View HLPDTEST.STATEGPS.TICKET_TASKS_V | Generated by SchemaSpy |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Analyzed at Mon May 23 01:05 IST 2022 |
View Definition:
SELECT "STATE_NAME",
null "PROJECT_TASK_ID",
STATE_NAME "ACTUAL_STATE_NAME",
STATE_NAME "ACTION_ITEM_DESCRIPTION",
'COMPLETED' "STATUS",
null "RESOURCE_NAME",
t_users.FIRST_NAME || ' ' || t_users.MIDDLE_NAME || ' ' ||
t_users.LAST_NAME USER_NAME,
t_instances.creation_date "START_DATE",
t_instances.creation_date "FINISH_DATE",
null "DURATION",
null "DAYSREMAINING",
issues.notes "COMMENTS",
null "MANAGER_COMMENTS",
issues.instance_id "INSTANCE_ID",
"STATE_ORDER",
"DISPLAY_ORDER",
issue_number "PROPERTY_VALUE",
"WORKFLOW_TYPE_NAME",
'' for_order
from t_instances t_instances,
tri2gps_dev.issues issues,
t_gps_workflow_states t_gps_workflow_states,
t_gps_workflow_types t_gps_workflow_types,
tas.t_users t_users
where t_instances.instance_id = issues.instance_id
and t_instances.workflow_type_id =
t_gps_workflow_states.workflow_type_id
and t_gps_workflow_states.workflow_type_id =
t_gps_workflow_types.workflow_type_id
and issues.created_by = t_users.user_id
and t_gps_workflow_states.startnode = 1
and t_instances.active=1
--and t_gps_workflow_states.state_descriptor <> 'Workflow Move'
--AND issues.issue_number in ('CC/ISOE/19103')
UNION ALL
SELECT nvl((select k.action_item_name
from t_task_action_items k
where k.project_task_id = T_PROJECT_TASK.Project_Task_Id),
T_GPS_WORKFLOW_STATES.STATE_NAME) STATE_NAME,
project_task_id,
T_GPS_WORKFLOW_STATES.STATE_NAME ACTUAL_STATE_NAME,
(select description
from t_task_action_items
where project_task_id = T_PROJECT_TASK.Project_Task_Id) action_item_description,
DECODE(t_gps_instances.instance_present,
1,
DECODE(T_PROJECT_TASK.DAYSREMAINING,
0,
decode((select count(state_id) count1
from t_project_task a
where a.instance_id = T_PROJECT_TASK.Instance_Id
and a.state_id = T_PROJECT_TASK.State_Id
group by instance_id, state_id),
1,
'STARTED',
'COMPLETED'),
DECODE(t_instances.PREDECESSOR,
NULL,
'STARTED',
DECODE((SELECT DISTINCT gi.instance_present
FROM STATEGPS.t_gps_instances gi,
STATEGPS.t_project_task pt,
STATEGPS.t_gps_workflow_states ws
WHERE gi.instance_id = pt.instance_id
AND gi.current_state_id = ws.state_id
AND gi.instance_id IN
(SELECT ip.PRED_INSTANCE_ID
FROM STATEGPS.t_instance_predecessor ip,
STATEGPS.t_gps_workflow_states ws1
WHERE ip.instance_id =
T_PROJECT_TASK.Instance_Id
AND NVL(ip.pred_state_id,
ws.state_id) =
ws1.state_id
AND ws.state_order <=
ws1.state_order)
AND gi.instance_present = 1
AND gi.active = 1),
1,
'PENDING',
DECODE(T_PROJECT_TASK.DURATION,
0,
'SKIPPED',
'STARTED')))),
0,
DECODE(T_PROJECT_TASK.DAYSREMAINING,
0,
DECODE(T_PROJECT_TASK.DURATION,
0,
'SKIPPED',
'COMPLETED'),
DECODE(t_gps_instances.instance_present,
0,
DECODE(T_PROJECT_TASK.DURATION,
0,
'SKIPPED',
'COMPLETED')))) STATUS,
T_PROJECT_TASK.RESOURCE_NAME,
decode(T_GPS_APPLICATIONS.Application_Name,
'Application Object Migrator',
DECODE(t_gps_instances.instance_present,
0,
T_USERS1.FIRST_NAME || ' ' || T_USERS1.MIDDLE_NAME || ' ' ||
T_USERS1.LAST_NAME,
stategps.getApproverName(T_GPS_WORKFLOW_STATES.STATE_ID)),
DECODE(T_PROJECT_TASK.DAYSREMAINING,null,T_USERS.FIRST_NAME || ' ' ||
T_USERS.MIDDLE_NAME || ' ' ||
T_USERS.LAST_NAME,
0,
decode((select count(state_id) count1
from t_project_task a
where a.instance_id = T_PROJECT_TASK.Instance_Id
and a.state_id = T_PROJECT_TASK.State_Id
group by instance_id, state_id),
1,T_USERS1.FIRST_NAME || ' ' ||
T_USERS1.MIDDLE_NAME || ' ' ||
T_USERS1.LAST_NAME, T_USERS.FIRST_NAME || ' ' ||
T_USERS.MIDDLE_NAME || ' ' ||
T_USERS.LAST_NAME))) USER_NAME,
T_GPS_INSTANCES.CREATED_DATE START_DATE,
DECODE(T_PROJECT_TASK.DAYSREMAINING,
0,
decode((select count(state_id) count1
from t_project_task a
where a.instance_id = T_PROJECT_TASK.Instance_Id
and a.state_id = T_PROJECT_TASK.State_Id
group by instance_id, state_id),
1,
T_GPS_INSTANCES.MOVED_OUT_DATE,
decode(sign(T_PROJECT_TASK.FINISH_DATE -
T_GPS_INSTANCES.CREATED_DATE),
-1,
T_GPS_INSTANCES.MOVED_OUT_DATE,
null,
T_GPS_INSTANCES.MOVED_OUT_DATE,
T_PROJECT_TASK.FINISH_DATE)),
T_GPS_INSTANCES.MOVED_OUT_DATE) FINISH_DATE,
T_PROJECT_TASK.DURATION,
T_PROJECT_TASK.DAYSREMAINING,
--T_GPS_INSTANCES.STATE_COMMENTS COMMENTS,
DECODE(T_PROJECT_TASK.DAYSREMAINING,
0,
decode((select count(state_id) count1
from t_project_task a
where a.instance_id = T_PROJECT_TASK.Instance_Id
and a.state_id = T_PROJECT_TASK.State_Id
group by instance_id, state_id),
1,
T_GPS_INSTANCES.STATE_COMMENTS,
decode(sign(T_PROJECT_TASK.FINISH_DATE -
T_GPS_INSTANCES.CREATED_DATE),
-1,
T_GPS_INSTANCES.STATE_COMMENTS,
T_PROJECT_TASK.COMMENTS)),
T_GPS_INSTANCES.STATE_COMMENTS) COMMENTS,
T_PROJECT_TASK.MANAGER_COMMENTS,
T_PROJECT_TASK.INSTANCE_ID,
T_GPS_WORKFLOW_STATES.STATE_ORDER,
T_GPS_WORKFLOW_STATES.Display_Order,
T_GPS_INSTANCE_PROPERTIES.PROPERTY_VALUE,
T_GPS_WORKFLOW_TYPES.Workflow_Type_Name,
'' for_order
FROM STATEGPS.T_GPS_INSTANCES T_GPS_INSTANCES
LEFT OUTER JOIN TAS.T_USERS T_USERS1
on T_USERS1.USER_NAME = T_GPS_INSTANCES.MOVED_OUT_BY,
STATEGPS.T_GPS_WORKFLOW_STATES T_GPS_WORKFLOW_STATES,
STATEGPS.T_PROJECT_TASK T_PROJECT_TASK
LEFT OUTER JOIN TAS.T_USERS T_USERS
on T_USERS.USER_NAME = T_PROJECT_TASK.USER_NAME, STATEGPS.T_INSTANCES
T_INSTANCES, STATEGPS.T_GPS_APPLICATIONS T_GPS_APPLICATIONS,
STATEGPS.T_GPS_WORKFLOW_TYPES T_GPS_WORKFLOW_TYPES,
STATEGPS.T_WORKFLOW_STATE_TASK_V T_WORKFLOW_STATE_TASK_V,
STATEGPS.T_GPS_INSTANCE_PROPERTIES T_GPS_INSTANCE_PROPERTIES
WHERE T_PROJECT_TASK.STATE_ID = T_GPS_WORKFLOW_STATES.STATE_ID
AND T_GPS_WORKFLOW_STATES.WORKFLOW_TYPE_ID =
T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID
AND T_GPS_WORKFLOW_TYPES.APPLICATION_ID =
T_GPS_APPLICATIONS.APPLICATION_ID
AND (T_PROJECT_TASK.STATE_ID = T_WORKFLOW_STATE_TASK_V.STATE_ID AND
T_PROJECT_TASK.STATE_ID = T_GPS_INSTANCES.CURRENT_STATE_ID)
AND (T_GPS_APPLICATIONS.VISIBLE_FLAG IS NULL OR
T_GPS_APPLICATIONS.VISIBLE_FLAG = 'true')
AND T_GPS_WORKFLOW_TYPES.VISIBLE_FLAG = 'true'
AND T_INSTANCES.INSTANCE_ID = T_PROJECT_TASK.INSTANCE_ID
AND T_PROJECT_TASK.INSTANCE_ID = T_GPS_INSTANCES.INSTANCE_ID
AND T_GPS_INSTANCES.ACTIVE = 1
AND T_GPS_INSTANCE_PROPERTIES.INSTANCE_ID = T_PROJECT_TASK.INSTANCE_ID
AND (T_GPS_INSTANCES.VISIBLE_FLAG IS NULL OR
T_GPS_INSTANCES.VISIBLE_FLAG = 'true')
--AND T_GPS_WORKFLOW_STATES.STATE_NAME ='Verification on change initiation'
--AND T_GPS_INSTANCE_PROPERTIES.PROPERTY_VALUE in ('CC/ISOE/19103')
AND T_GPS_INSTANCE_PROPERTIES.PROPERTY_NAME IN
('tri2.SG_TRI2_ENTITY.IA_ISSUE_NUMBER',
'tri2.SG_TRI2_ENTITY.MIG_REQUEST_NUMBER')
UNION ALL
SELECT DISTINCT nvl((select k.action_item_name
from t_task_action_items k
where k.project_task_id =
T_PROJECT_TASK.Project_Task_Id),
T_GPS_WORKFLOW_STATES.STATE_NAME) STATE_NAME,
project_task_id,
T_GPS_WORKFLOW_STATES.STATE_NAME ACTUAL_STATE_NAME,
(select description
from t_task_action_items
where project_task_id = T_PROJECT_TASK.Project_Task_Id) action_item_description,
DECODE(T_PROJECT_TASK.DAYSREMAINING,
0,
DECODE(T_PROJECT_TASK.DURATION,
0,
'SKIPPED',
'COMPLETED'),
DECODE((select instance_present
from t_gps_instances a,
T_GPS_WORKFLOW_STATES b
where a.instance_id =
T_PROJECT_TASK.INSTANCE_ID
and a.current_state_id = b.state_id
and ( /*(b.state_descriptor = 'Workflow End'
and a.instance_present = 0)or */
b.state_order in
(select max(state_order)
from t_gps_workflow_states a,
t_instances y
where a.workflow_type_id =
y.workflow_type_id
and y.instance_id =
T_PROJECT_TASK.INSTANCE_ID) and
a.instance_present = 1)),
0,
'PATH NOT TRAVERSED',
1,
'PATH NOT TRAVERSED',
'NOT STARTED')) STATUS,
T_PROJECT_TASK.RESOURCE_NAME,
DECODE(T_GPS_APPLICATIONS.Application_Name,
'Application Object Migrator',
getApproverName(T_GPS_WORKFLOW_STATES.STATE_ID),
T_USERS.FIRST_NAME || ' ' || T_USERS.MIDDLE_NAME || ' ' ||
T_USERS.LAST_NAME) USER_NAME,
NULL START_DATE,
NULL FINISH_DATE,
T_PROJECT_TASK.DURATION,
T_PROJECT_TASK.DAYSREMAINING,
NULL COMMENTS,
NULL MANAGER_COMMENTS,
T_PROJECT_TASK.INSTANCE_ID,
T_GPS_WORKFLOW_STATES.STATE_ORDER,
T_GPS_WORKFLOW_STATES.Display_Order,
T_GPS_INSTANCE_PROPERTIES.PROPERTY_VALUE,
T_GPS_WORKFLOW_TYPES.Workflow_Type_Name,
'NOT STARTED' for_order
FROM STATEGPS.T_GPS_INSTANCES T_GPS_INSTANCES,
STATEGPS.T_GPS_WORKFLOW_STATES T_GPS_WORKFLOW_STATES,
--TAS.T_USERS T_USERS1,
STATEGPS.T_PROJECT_TASK T_PROJECT_TASK
LEFT OUTER JOIN TAS.T_USERS T_USERS
on T_USERS.USER_NAME = T_PROJECT_TASK.USER_NAME, STATEGPS.T_INSTANCES
T_INSTANCES, STATEGPS.T_GPS_APPLICATIONS T_GPS_APPLICATIONS,
STATEGPS.T_GPS_WORKFLOW_TYPES T_GPS_WORKFLOW_TYPES,
STATEGPS.T_WORKFLOW_STATE_TASK_V T_WORKFLOW_STATE_TASK_V,
STATEGPS.T_GPS_INSTANCE_PROPERTIES T_GPS_INSTANCE_PROPERTIES
WHERE T_PROJECT_TASK.STATE_ID = T_GPS_WORKFLOW_STATES.STATE_ID
--AND T_USERS1.USER_NAME = T_GPS_INSTANCES.MOVED_OUT_BY
AND T_GPS_WORKFLOW_STATES.WORKFLOW_TYPE_ID =
T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID
AND T_GPS_WORKFLOW_TYPES.APPLICATION_ID =
T_GPS_APPLICATIONS.APPLICATION_ID
AND T_PROJECT_TASK.STATE_ID = T_WORKFLOW_STATE_TASK_V.STATE_ID
AND (T_GPS_APPLICATIONS.VISIBLE_FLAG IS NULL OR
T_GPS_APPLICATIONS.VISIBLE_FLAG = 'true')
AND T_GPS_WORKFLOW_TYPES.VISIBLE_FLAG = 'true'
AND T_INSTANCES.INSTANCE_ID = T_PROJECT_TASK.INSTANCE_ID
AND T_PROJECT_TASK.INSTANCE_ID = T_GPS_INSTANCES.INSTANCE_ID
AND T_GPS_INSTANCES.ACTIVE = 1
AND T_GPS_INSTANCE_PROPERTIES.Instance_Id = T_PROJECT_TASK.INSTANCE_ID
--AND T_GPS_INSTANCE_PROPERTIES.PROPERTY_VALUE in ('CC/ISOE/19103')
AND NOT EXISTS
(SELECT T_GPS_INSTANCES$1.CURRENT_STATE_ID
FROM STATEGPS.T_GPS_INSTANCES T_GPS_INSTANCES$1
WHERE T_PROJECT_TASK.STATE_ID = T_GPS_INSTANCES$1.CURRENT_STATE_ID
AND T_PROJECT_TASK.INSTANCE_ID = T_GPS_INSTANCES$1.INSTANCE_ID
AND (T_GPS_INSTANCES$1.VISIBLE_FLAG IS NULL OR
T_GPS_INSTANCES$1.VISIBLE_FLAG = 'true'))
--AND T_GPS_WORKFLOW_STATES.STATE_NAME ='Verification on change initiation'
--AND T_GPS_INSTANCE_PROPERTIES.PROPERTY_VALUE in ('CC/ISOE/19103')
AND T_GPS_INSTANCE_PROPERTIES.PROPERTY_NAME IN
('tri2.SG_TRI2_ENTITY.IA_ISSUE_NUMBER',
'tri2.SG_TRI2_ENTITY.MIG_REQUEST_NUMBER')
union all
SELECT "STATE_NAME",
null "PROJECT_TASK_ID",
STATE_NAME "ACTUAL_STATE_NAME",
null "ACTION_ITEM_DESCRIPTION",
decode(moved_out_date, null, 'STARTED', 'COMPLETED') "STATUS",
null "RESOURCE_NAME",
'AOM System' USER_NAME,
t_instances.created_date "START_DATE",
t_instances.moved_out_date "FINISH_DATE",
null "DURATION",
null "DAYSREMAINING",
decode(moved_out_date, null, '', 'Moved by System') "COMMENTS",
null "MANAGER_COMMENTS",
t_migration_requests.instance_id "INSTANCE_ID",
"STATE_ORDER",
"DISPLAY_ORDER",
migration_request_number "PROPERTY_VALUE",
"WORKFLOW_TYPE_NAME",
'' for_order
from t_gps_instances t_instances,
tri2gps_dev.t_migration_requests t_migration_requests,
t_gps_workflow_states t_gps_workflow_states,
t_gps_workflow_types t_gps_workflow_types
where t_instances.instance_id = t_migration_requests.instance_id
and t_instances.workflow_type_id =
t_gps_workflow_states.workflow_type_id
and t_gps_workflow_states.workflow_type_id =
t_gps_workflow_types.workflow_type_id
and t_instances.current_state_id = t_gps_workflow_states.state_id
and t_gps_workflow_states.state_descriptor = 'Self Move State'
and t_instances.active=1
--and t_migration_requests.migration_request_number = 'CC/ISOE/19103' --1512
ORDER BY start_date, STATE_ORDER, FINISH_DATE
Possibly Referenced Tables/Views:
![]() ![]() |