View HLPDTEST.STATEGPS.T_STARTED_TASKS_V Generated by
SchemaSpy
Legend: SourceForge.net
Primary key columns
Columns with indexes
Implied relationships
Excluded column relationships
< n > number of related tables
 
Column Type Size Nulls Auto Default Children Parents Comments
APPLICATION_ID number 22  √  null
T_GPS_APPLICATIONS.APPLICATION_ID Implied Constraint R
APPLICATION_NAME varchar2 255  √  null
WORKFLOW_TYPE_NAME varchar2 255  √  null
IA_ISSUE_NUMBER varchar2 100  √  null
IA_ISSUE_TITLE varchar2 4000  √  null
STATE_NAME varchar2 255  √  null
RESOURCE_NAME varchar2 2000  √  null
USER_NAME varchar2 2000  √  null
TOTAL_ACTIVITY_TIME number 22  √  null
LAST_ACTIVITY_TIME date 7  √  null
LAST_ACTIVITY_ENTERED varchar2 4000  √  null
LAST_ACTIVITY_ENTERED_HOURS number 22  √  null
MANAGER varchar2 4000  √  null
DEVELOPER varchar2 4000  √  null
LEADD varchar2 4000  √  null
USERR varchar2 4000  √  null
WORKFLOW_TYPE_ID number 22  √  null
T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID Implied Constraint R
START_DATE date 7  √  null
FINISH_DATE date 7  √  null
DURATION number 22  √  null
DAYSREMAINING number 22  √  null
STATE_ID number 22  √  null
T_GPS_WORKFLOW_STATES.STATE_ID Implied Constraint R
PROPERTY1 varchar2 0  √  null
PROPERTY2 varchar2 0  √  null
PROPERTY4 number 22  √  null
PROJECT_TASK_ID number 22  √  null
T_PROJECT_TASK.PROJECT_TASK_ID Implied Constraint R
COMMENTS varchar2 4000  √  null
USER_ID number 22  √  null
T_GPS_USERS.USER_ID Implied Constraint R
REASSIGN_STATE varchar2 0  √  null
PRIORITY number 22  √  null
OWNER varchar2 500  √  null
DOCUMENT_PATH varchar2 200  √  null
DUE_DATE date 7  √  null
CREATED_DATE date 8  √  null
STATUS varchar2 11  √  null
PROPERTY3 number 22  √  null
DOCUMENT_ID number 22  √  null
T_INSTANCE_DOCUMENTS.DOCUMENT_ID Implied Constraint R
MANAGER_COMMENTS varchar2 4000  √  null
CURRENT_DUE_DATE date 7  √  null
PREDECESSOR varchar2 4000  √  null
APPLICATION_MODULE varchar2 50  √  null
STATE_ORDER number 22  √  null
PROMISED_DUE_DATE date 7  √  null
CUSTOMER_TICKET_NUMBER varchar2 255  √  null
MIGRATION_NUMBER varchar2 30  √  null
ISSUE_ID number 20  √  null
MIGRATION_REQUEST_ID number 22  √  null
MIG_ISSUE_LINK_ID number 22  √  null
CRITICAL number 22  √  null
OPERATION_COUNT number 22  √  null
TASK_COMMENTS varchar2 4000  √  null
OLD_DURATION number 22  √  null
OLD_DAYSREMAINING number 22  √  null
OLD_TASK_COMMENTS varchar2 4000  √  null
OLD_MANAGER_COMMENTS varchar2 0  √  null
TAG varchar2 1000  √  null
OLD_USER_NAME varchar2 2000  √  null
ISSUE_TYPE varchar2 100  √  null
COLL_USERNAME varchar2 2000  √  null
STATE_TYPE varchar2 50  √  null
MIGRATION_ACTIVE varchar2 5  √  null
ACTIVITY_DATE date 8  √  null

Analyzed at Fri Sep 25 01:05 IST 2020

View Definition:
SELECT T_PROJECT_TASK_V.APPLICATION_ID, T_PROJECT_TASK_V.APPLICATION_NAME, T_PROJECT_TASK_V.WORKFLOW_TYPE_NAME, T_PROJECT_TASK_V.IA_ISSUE_NUMBER, T_PROJECT_TASK_V.IA_ISSUE_TITLE, T_PROJECT_TASK_V.STATE_NAME, T_PROJECT_TASK_V.RESOURCE_NAME, T_PROJECT_TASK_V.USER_NAME, (SELECT SUM(non_billable_hours) FROM T_PROJECT_ACTIVITY T_PROJECT_ACTIVITY WHERE T_PROJECT_ACTIVITY.project_Task_id = --196190 T_PROJECT_TASK_V.PROJECT_TASK_ID AND T_PROJECT_ACTIVITY.user_name = -- 'rdeepti' T_PROJECT_TASK.USER_NAME) total_activity_time, (SELECT MAX(T_PROJECT_ACTIVITY.CREATED_DATE) FROM T_PROJECT_ACTIVITY T_PROJECT_ACTIVITY WHERE T_PROJECT_ACTIVITY.project_Task_id = T_PROJECT_TASK_V.PROJECT_TASK_ID AND T_PROJECT_ACTIVITY.user_name = T_PROJECT_TASK.USER_NAME) last_Activity_time, (SELECT GET_LAST_ACTIVITY(T_PROJECT_TASK_V.PROJECT_TASK_ID, T_PROJECT_TASK.USER_NAME) from dual) last_Activity_Entered, (SELECT sum(T_PROJECT_ACTIVITY.non_billable_hours) FROM T_PROJECT_ACTIVITY T_PROJECT_ACTIVITY WHERE T_PROJECT_ACTIVITY.project_Task_id = T_PROJECT_TASK_V.PROJECT_TASK_ID AND T_PROJECT_ACTIVITY.user_name = T_PROJECT_TASK.USER_NAME AND T_PROJECT_ACTIVITY.CREATED_DATE = (Select MAX(a.CREATED_DATE) FROM T_PROJECT_ACTIVITY a WHERE a.project_Task_id = T_PROJECT_TASK_V.PROJECT_TASK_ID AND a.user_name = T_PROJECT_TASK.USER_NAME)) last_Activity_Entered_Hours, /*(SELECT distinct --a.state_id, tgws.state_name,a.resource_name, tgws.state_order, a.user_name FROM t_project_task a, t_gps_workflow_states tgws WHERE tgws.state_id = a.state_id AND a.instance_id = T_PROJECT_TASK_V.property3 and state_name in ('Assign Priority & Resources') --and state_name = 'Provide Testcase' 364010 ) Manager,*/ GET_RESOURCE(T_PROJECT_TASK_V.property3,'Manager') Manager, GET_RESOURCE(T_PROJECT_TASK_V.property3,'Developer') Developer, GET_RESOURCE(T_PROJECT_TASK_V.property3,'Lead') Leadd, GET_RESOURCE(T_PROJECT_TASK_V.property3,'User') Userr, /* (SELECT user_name FROM (SELECT a.user_name,a.instance_id,a.project_task_id FROM t_project_task a, t_gps_workflow_states tgws WHERE tgws.state_id = a.state_id AND (state_name = 'Develop and Unit Test' OR state_name = 'Configure and Unit Test' OR state_name = 'Provide Support') order by a.project_task_id asc) WHERE instance_id = T_PROJECT_TASK_V.property3 and rownum = 1) Developer,*/ /*(SELECT --a.state_id, tgws.state_name,a.resource_name, tgws.state_order, a.user_name FROM t_project_task a, t_gps_workflow_states tgws WHERE tgws.state_id = a.state_id AND a.instance_id = T_PROJECT_TASK_V.property3 --1232588 AND (state_name = 'Review Code & Backward Compatibality' OR state_name = 'Review Configurations')) Leadd,*/ /*(SELECT user_name from ( SELECT a.user_name,a.instance_id,a.project_task_id FROM t_project_task a, t_gps_workflow_states tgws WHERE tgws.state_id = a.state_id AND (state_name = 'Review Code & Backward Compatibality' OR state_name = 'Review Configurations') order by a.project_task_id asc ) WHERE instance_id = T_PROJECT_TASK_V.property3 and rownum = 1) Leadd,*/ /* (SELECT a.user_name FROM t_project_task a, t_gps_workflow_states tgws WHERE tgws.state_id = a.state_id AND a.instance_id = T_PROJECT_TASK_V.property3 --316226 AND state_name = 'UAT') Userr,*/ /*(SELECT user_name FROM (SELECT a.user_name,a.instance_id,a.project_task_id FROM t_project_task a, t_gps_workflow_states tgws WHERE tgws.state_id = a.state_id AND state_name = 'UAT' order by a.project_task_id asc ) WHERE instance_id = T_PROJECT_TASK_V.property3 and rownum = 1) Userr,*/ T_PROJECT_TASK_V.WORKFLOW_TYPE_ID, T_PROJECT_TASK_V.START_DATE, T_PROJECT_TASK_V.FINISH_DATE, T_PROJECT_TASK_V.DURATION, T_PROJECT_TASK_V.DAYSREMAINING, T_PROJECT_TASK_V.STATE_ID, T_PROJECT_TASK_V.PROPERTY1, T_PROJECT_TASK_V.PROPERTY2, T_PROJECT_TASK_V.PROPERTY4, T_PROJECT_TASK_V.PROJECT_TASK_ID, DECODE(T_PROJECT_TASK_V.property3, NULL, (SELECT comments FROM v_native_task_comments WHERE project_task_id = T_PROJECT_TASK_V.project_task_id), DECODE((SELECT comments FROM v_comments WHERE instance_id = T_PROJECT_TASK_V.property3), NULL, (SELECT comments FROM T_PROJECT_TASK WHERE project_task_id = T_PROJECT_TASK_V.PROJECT_TASK_ID), (SELECT comments FROM v_comments WHERE instance_id = T_PROJECT_TASK_V.property3))) Comments, (SELECT USER_ID FROM TAS.t_users WHERE USER_NAME = T_PROJECT_TASK_V.USER_NAME) User_Id, T_PROJECT_TASK_V.REASSIGN_STATE, T_PROJECT_TASK_V.PRIORITY, DECODE(T_PROJECT_TASK_V.property3, NULL, 'tri2support', (SELECT owner FROM t_instances WHERE T_PROJECT_TASK_V.property3 = instance_id)) Owner, T_PROJECT_TASK_V.DOCUMENT_PATH, T_PROJECT_TASK_V.DUE_DATE, TRUNC(T_PROJECT_TASK_V.CREATED_DATE) CREATED_DATE, T_PROJECT_TASK_V.STATUS, T_PROJECT_TASK_V.PROPERTY3, T_PROJECT_TASK_V.DOCUMENT_ID, DECODE(T_PROJECT_TASK_V.property3, NULL, (SELECT manager_comments FROM v_native_task_manager_comments WHERE project_task_id = T_PROJECT_TASK_V.project_task_id), DECODE((SELECT manager_comments FROM v_manager_comments WHERE instance_id = T_PROJECT_TASK_V.property3), NULL, (SELECT manager_comments FROM T_PROJECT_TASK WHERE project_task_id = T_PROJECT_TASK_V.PROJECT_TASK_ID), (SELECT manager_comments FROM v_manager_comments WHERE instance_id = T_PROJECT_TASK_V.property3))) Manager_Comments, T_PROJECT_TASK_V.CURRENT_DUE_DATE, T_PROJECT_TASK_V.PREDECESSOR, T_PROJECT_TASK_V.APPLICATION_MODULE, T_PROJECT_TASK_V.STATE_ORDER, T_PROJECT_TASK_V.PROMISED_DUE_DATE, (SELECT CUST_TICKET_NUMBER FROM T_CUSTOMER_REF WHERE INSTANCE_ID = T_PROJECT_TASK_V.PROPERTY3) Customer_Ticket_number, (SELECT mr.migration_request_number FROM tri2gps_dev.t_migration_requests mr WHERE mr.migration_request_id = (SELECT MAX(tm.migration_request_id) FROM tri2gps_dev.t_migration_issues_links tm WHERE TM.ISSUE_ID = T_PROJECT_TASK_V.ISSUE_ID AND tm.migration_request_id IS NOT NULL)) MIGRATION_NUMBER, T_PROJECT_TASK_V.ISSUE_ID, (SELECT MAX(tm.migration_request_id) FROM tri2gps_dev.t_migration_issues_links tm WHERE TM.ISSUE_ID = T_PROJECT_TASK_V.ISSUE_ID AND tm.migration_request_id IS NOT NULL) MIGRATION_REQUEST_ID, (SELECT MAX(tm.mig_issue_link_id) FROM tri2gps_dev.t_migration_issues_links tm WHERE TM.ISSUE_ID = T_PROJECT_TASK_V.ISSUE_ID AND tm.migration_request_id IS NOT NULL) MIG_ISSUE_LINK_ID, T_PROJECT_TASK_V.CRITICAL, (SELECT COUNT(1) FROM t_gps_state_operations WHERE from_state_id = T_PROJECT_TASK_V.STATE_ID) Operation_count, T_PROJECT_TASK.COMMENTS task_comments, T_PROJECT_TASK.DURATION OLD_DURATION, T_PROJECT_TASK.DAYSREMAINING OLD_DAYSREMAINING, T_PROJECT_TASK.COMMENTS OLD_TASK_COMMENTS, T_PROJECT_TASK_V.MANAGER_COMMENTS OLD_MANAGER_COMMENTS, --T_PROJECT_TASK_V.DUE_DATE, --T_PROJECT_TASK_V.PRIORITY, T_PROJECT_TASK_V.TAG, T_PROJECT_TASK.USER_NAME OLD_USER_NAME, (SELECT issue_type FROM tri2gps_dev.issues s WHERE s.issue_id = T_PROJECT_TASK_V.ISSUE_ID) ISSUE_TYPE, (SUBSTR(T_PROJECT_TASK_V.USER_NAME, 1, CASE WHEN (instr(T_PROJECT_TASK_V.USER_NAME, ',', 1, 1) - 1) = -1 THEN LENGTH(T_PROJECT_TASK_V.USER_NAME) ELSE (instr(T_PROJECT_TASK_V.USER_NAME, ',', 1, 1) - 1) END)) COLL_USERNAME, T_PROJECT_TASK_V.STATE_TYPE, (SELECT DECODE(MAX(active), '1', 'True', '0', 'False') a FROM t_gps_instances tg WHERE instance_id IN (SELECT instance_id FROM t_gps_instance_properties WHERE property_name LIKE 'tri2.SG_TRI2_ENTITY.MIG_REQUEST_NUMBER' AND property_value = T_PROJECT_TASK_V.IA_ISSUE_NUMBER)) Migration_Active, (SELECT TRUNC(SYSDATE) FROM DUAL) ACTIVITY_DATE FROM T_PROJECT_TASK_V T_PROJECT_TASK_V, T_PROJECT_TASK T_PROJECT_TASK WHERE T_PROJECT_TASK.PROJECT_TASK_ID = T_PROJECT_TASK_V.PROJECT_TASK_ID AND T_PROJECT_TASK_V.STATUS IN ('STARTED') /* AND T_PROJECT_TASK_V.DAYSREMAINING IS NOT NULL AND T_PROJECT_TASK_V.DAYSREMAINING != 0;*/ AND (T_PROJECT_TASK_V.DAYSREMAINING IS NULL OR T_PROJECT_TASK_V.DAYSREMAINING > 0)
 
Possibly Referenced Tables/Views:


Close relationships: