View HLPDTEST.STATEGPS.MYVIEW 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_ID number 22  √  null
T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID Implied Constraint R
WORKFLOW_TYPE_NAME varchar2 255  √  null
START_DATE date 7  √  null
FINISH_DATE date 7  √  null
DURATION number 22  √  null
DAYSREMAINING number 22  √  null
RESOURCE_NAME varchar2 2000  √  null
USER_NAME varchar2 2000  √  null
ACTUALSTART_DATE date 7  √  null
ACTUALFINISH_DATE date 7  √  null
STATE_ID number 22  √  null
T_GPS_WORKFLOW_STATES.STATE_ID Implied Constraint R
IA_ISSUE_NUMBER varchar2 4000  √  null
IA_ISSUE_TITLE varchar2 4000  √  null
PROPERTY1 varchar2 0  √  null
PROPERTY2 varchar2 0  √  null
PROPERTY3 number 22  √  null
PROPERTY4 number 22  √  null
PROJECT_TASK_ID number 22  √  null
T_PROJECT_TASK.PROJECT_TASK_ID Implied Constraint R
COMMENTS varchar2 0  √  null
USER_ID varchar2 0  √  null
REASSIGN_STATE varchar2 0  √  null
PRIORITY number 22  √  null
OWNER varchar2 11  √  null
STATE_NAME varchar2 255  √  null
DOCUMENT_ID number 22  √  null
T_INSTANCE_DOCUMENTS.DOCUMENT_ID Implied Constraint R
DOCUMENT_PATH varchar2 200  √  null
CREATED_DATE date 7  √  null
DUE_DATE date 7  √  null
STATUS varchar2 11  √  null
MANAGER_COMMENTS varchar2 0  √  null
CURRENT_DUE_DATE date 7  √  null
PREDECESSOR varchar2 4000  √  null
PROMISED_DUE_DATE date 7  √  null
APPLICATION_MODULE varchar2 50  √  null
STATE_ORDER number 22  √  null
MIGRATION_NUMBER varchar2 0  √  null
MIGRATION_REQUEST_ID varchar2 0  √  null
MIG_ISSUE_LINK_ID varchar2 0  √  null
ISSUE_ID number 22  √  null
CRITICAL number 22  √  null

Analyzed at Sun Nov 29 01:06 IST 2020

View Definition:
SELECT APPLICATIONS1.APPLICATION_ID APPLICATION_ID, APPLICATIONS1.APPLICATION_NAME, WORKFLOWS.WORKFLOW_TYPE_ID, WORKFLOWS.WORKFLOW_TYPE_NAME, PROJECTTASKS.START_DATE, PROJECTTASKS.FINISH_DATE, PROJECTTASKS.DURATION, PROJECTTASKS.DAYSREMAINING, PROJECTTASKS.RESOURCE_NAME, PROJECTTASKS.USER_NAME, PROJECTTASKS.ACTUALSTART_DATE, PROJECTTASKS.ACTUALFINISH_DATE, STATES.State_Id, instance_props.IA_ISSUE_NUMBER, instance_props.IA_ISSUE_TITLE, NULL PROPERTY1, NULL PROPERTY2, PROJECTTASKS.INSTANCE_ID PROPERTY3, 22.2 PROPERTY4, PROJECTTASKS.PROJECT_TASK_ID, NULL COMMENTS, NULL USER_ID, NULL REASSIGN_STATE, decode(insts.priority, NULL, PROJECTTASKS.PRIORITY, insts.priority) PRIORITY, NULL OWNER, STATES.State_Name, (select DOCUMENT_ID from t_instance_documents where instance_id = PROJECTTASKS.Instance_Id and rownum = 1) DOCUMENT_ID, (select DOCUMENT from t_instance_documents where instance_id = PROJECTTASKS.Instance_Id and rownum = 1) DOCUMENT_PATH, ISSUES.CREATED_DATE, ISSUES.DUE_DATE, decode(PROJECTTASKS.Duration, 0, 'SKIPPED', decode(gps_insts.instance_present, 1, decode(insts.PREDECESSOR,NULL,'STARTED', --The following decode will show the status as PENDING, if the predecessor ISSUE Tasks are still STARTED -- Otherwise, it will show the status as STARTED DECODE((SELECT distinct gi.instance_present from t_gps_instances gi, t_project_task pt where gi.instance_id = pt.instance_id and gi.current_state_id = pt.state_id and gi.instance_id in (select ip.PRED_INSTANCE_ID from t_instance_predecessor ip where ip.instance_id = PROJECTTASKS.Instance_Id -- If the ip.pred_state_id is null, then use gi.current_state_id and gi.current_state_id = nvl(ip.pred_state_id,gi.current_state_id)) -- The predecessor instance should be active=1 and instance_present=1 and gi.instance_present = 1 and gi.active = 1 ), 1, 'PENDING', 'STARTED')), NULL, 'NOT STARTED', 'COMPLETED')) STATUS, NULL MANAGER_COMMENTS, insts.due_date CURRENT_DUE_DATE, insts.PREDECESSOR, PROJECTTASKS.PROMISED_DUE_DATE, ---added application module from tri2 issues table by kchinta on 27/5/2009 ISSUES.Application_Module, ---added STATE_ORDER from T_GPS_WORKFLOW_STATES table by kchinta on 8/6/2009 STATES.STATE_ORDER ---added Migration Number ,Migration request id,MIG_ISSUE_LINK_ID , NULL MIGRATION_NUMBER, NULL MIGRATION_REQUEST_ID, NULL MIG_ISSUE_LINK_ID --- ADDED ISSUES ID IN TRI2GPS_DEV.ISSUES TABLE , ISSUES.ISSUE_ID ISSUE_ID, PROJECTTASKS.Critical FROM T_GPS_APPLICATIONS APPLICATIONS1, T_GPS_WORKFLOW_TYPES WORKFLOWS, T_GPS_WORKFLOW_STATES STATES, T_PROJECT_TASK PROJECTTASKS, t_workflow_state_task_v t_workflow_state_task_v, t_instances insts, t_gps_instances gps_insts, tri2gps_dev.issues ISSUES, v_instance_props instance_props WHERE PROJECTTASKS.STATE_ID = STATES.STATE_ID AND STATES.WORKFLOW_TYPE_ID = WORKFLOWS.WORKFLOW_TYPE_ID AND WORKFLOWS.APPLICATION_ID = APPLICATIONS1.APPLICATION_ID and PROJECTTASKS.state_id = t_workflow_state_task_v.state_id and PROJECTTASKS.STATE_ID = gps_insts.current_state_id and (APPLICATIONS1.visible_flag is null or APPLICATIONS1.visible_flag = 'true') and WORKFLOWS.visible_flag = 'true' and insts.instance_id = PROJECTTASKS.Instance_Id and PROJECTTASKS.Instance_Id = gps_insts.instance_id and gps_insts.active = 1 AND instance_props.IA_ISSUE_NUMBER = ISSUES.ISSUE_NUMBER AND instance_props.Instance_Id = PROJECTTASKS.Instance_Id union -- ISSUE's state is not available t_gps_instances table -- NOT STARTED tasks SELECT distinct APPLICATIONS1.APPLICATION_ID APPLICATION_ID, APPLICATIONS1.APPLICATION_NAME, WORKFLOWS.WORKFLOW_TYPE_ID, WORKFLOWS.WORKFLOW_TYPE_NAME, PROJECTTASKS.START_DATE, PROJECTTASKS.FINISH_DATE, PROJECTTASKS.DURATION, PROJECTTASKS.DAYSREMAINING, PROJECTTASKS.RESOURCE_NAME, PROJECTTASKS.USER_NAME, PROJECTTASKS.ACTUALSTART_DATE, PROJECTTASKS.ACTUALFINISH_DATE, STATES.State_Id, instance_props.IA_ISSUE_NUMBER, instance_props.IA_ISSUE_TITLE, NULL PROPERTY1, NULL PROPERTY2, PROJECTTASKS.INSTANCE_ID PROPERTY3, 22.2 PROPERTY4, PROJECTTASKS.PROJECT_TASK_ID, NULL COMMENTS, NULL USER_ID, NULL REASSIGN_STATE, -- priorty from project tasks if instance doesn't have priority decode(insts.priority, NULL, PROJECTTASKS.PRIORITY, insts.priority) PRIORITY, null owner, STATES.State_Name, (select DOCUMENT_ID from t_instance_documents where instance_id = PROJECTTASKS.Instance_Id and rownum = 1) DOCUMENT_ID, (select DOCUMENT from t_instance_documents where instance_id = PROJECTTASKS.Instance_Id and rownum = 1) DOCUMENT_PATH, ISSUES.CREATED_DATE, ISSUES.DUE_DATE, decode(PROJECTTASKS.DAYSREMAINING, 0, 'COMPLETED', 'NOT STARTED') STATUS, NULL MANAGER_COMMENTS, insts.due_date CURRENT_DUE_DATE, insts.PREDECESSOR, PROJECTTASKS.PROMISED_DUE_DATE, ---added application module from tri2 issues table by kchinta on 27/5/2009 ISSUES.Application_Module, ---added STATE_ORDER from T_GPS_WORKFLOW_STATES table by kchinta on 8/6/2009 STATES.STATE_ORDER ---added Migration Number and Migration Request id,, MIG_ISSUE_LINK_ID , NULL MIGRATION_NUMBER, NULL MIGRATION_REQUEST_ID, NULL MIG_ISSUE_LINK_ID --- ADDED ISSUES ID IN TRI2GPS_DEV.ISSUES TABLE , ISSUES.ISSUE_ID ISSUE_ID, PROJECTTASKS.Critical FROM T_GPS_APPLICATIONS APPLICATIONS1, T_GPS_WORKFLOW_TYPES WORKFLOWS, T_GPS_WORKFLOW_STATES STATES, T_PROJECT_TASK PROJECTTASKS, t_workflow_state_task_v t_workflow_state_task_v, t_instances insts, t_gps_instances gps_insts, tri2gps_dev.issues ISSUES, v_instance_props instance_props WHERE PROJECTTASKS.STATE_ID = STATES.STATE_ID AND STATES.WORKFLOW_TYPE_ID = WORKFLOWS.WORKFLOW_TYPE_ID AND WORKFLOWS.APPLICATION_ID = APPLICATIONS1.APPLICATION_ID and PROJECTTASKS.state_id = t_workflow_state_task_v.state_id and (APPLICATIONS1.visible_flag is null or APPLICATIONS1.visible_flag = 'true') and WORKFLOWS.visible_flag = 'true' and insts.instance_id = PROJECTTASKS.Instance_Id and PROJECTTASKS.Instance_Id = gps_insts.instance_id and gps_insts.active = 1 AND instance_props.IA_ISSUE_NUMBER = ISSUES.ISSUE_NUMBER AND instance_props.Instance_Id = PROJECTTASKS.Instance_Id and not exists (select current_state_id from t_gps_instances where PROJECTTASKS.state_id = current_state_id and PROJECTTASKS.instance_id = instance_id) union -- This section retrieves all the non-workflow native tasks, which are assigned role/user -- No link to t_gps_instanceS is provided here, where these tasks are not workflow instances -- SKIPPED, STARTED, NOT STARTED and COMPLETED tasks SELECT APPLICATIONS.APPLICATION_ID APPLICATION_ID, APPLICATIONS.APPLICATION_NAME, WORKFLOWS.WORKFLOW_TYPE_ID, WORKFLOWS.WORKFLOW_TYPE_NAME, PROJECTTASKS.START_DATE, PROJECTTASKS.FINISH_DATE, PROJECTTASKS.DURATION, PROJECTTASKS.DAYSREMAINING, PROJECTTASKS.RESOURCE_NAME, PROJECTTASKS.USER_NAME, PROJECTTASKS.ACTUALSTART_DATE, PROJECTTASKS.ACTUALFINISH_DATE, STATES.STATE_ID, NULL IA_ISSUE_NUMBER, STATES.STATE_NAME IA_ISSUE_TITLE, NULL PROPERTY1, NULL PROPERTY2, NULL PROPERTY3, 22.2 PROPERTY4, PROJECTTASKS.PROJECT_TASK_ID, NULL COMMENTS, NULL USER_ID, NULL REASSIGN_STATE, PROJECTTASKS.PRIORITY PRIORITY, 'tri2support' owner, NULL STATE_NAME, NULL DOCUMENT_ID, NULL DOCUMENT_PATH, NULL CREATED_DATE, PROJECTTASKS.FINISH_DATE DUE_DATE, decode(daysremaining, 0, 'COMPLETED', --'STARTED' DECODE((SELECT DAYSREMAINING FROM T_PROJECT_TASK WHERE STATE_ID = PROJECTTASKS.STATE_ID - 1 and rownum = 1), 0, 'STARTED', NULL, 'STARTED', 'NOT STARTED')) STATUS, NULL MANAGER_COMMENTS, NULL CURRENT_DUE_DATE, NULL PREDECESSOR, PROJECTTASKS.PROMISED_DUE_DATE, -- added NULL for application module by kchinta on 27/5/2009 NULL, ---added STATE_ORDER from T_GPS_WORKFLOW_STATES table by kchinta on 8/6/2009 STATES.STATE_ORDER ---added Migration Number and Migration Request id,,MIG_ISSUE_LINK_ID , NULL MIGRATION_NUMBER, NULL MIGRATION_REQUEST_ID, NULL MIG_ISSUE_LINK_ID --- ADDED ISSUES ID IN TRI2GPS_DEV.ISSUES TABLE , NULL ISSUE_ID, PROJECTTASKS.Critical FROM T_GPS_APPLICATIONS APPLICATIONS, T_GPS_WORKFLOW_TYPES WORKFLOWS, T_GPS_WORKFLOW_STATES STATES, T_PROJECT_TASK PROJECTTASKS WHERE PROJECTTASKS.STATE_ID = STATES.STATE_ID AND STATES.WORKFLOW_TYPE_ID = WORKFLOWS.WORKFLOW_TYPE_ID AND WORKFLOWS.APPLICATION_ID = APPLICATIONS.APPLICATION_ID and (APPLICATIONS.visible_flag is null or APPLICATIONS.visible_flag = 'true') and WORKFLOWS.visible_flag = 'true' and not exists (select * from t_workflow_state_task_v x where x.state_id = PROJECTTASKS.state_id) -- LCHINTA COMMENTS FOR SQL OBJECTS MAPPING TESTING IN MIGRATION TOOL ORDER BY 24, 30, 14, 37
 
Possibly Referenced Tables/Views:


Close relationships: