View HLPDTEST.STATEGPS.MY_FOLLOW_UP_V | Generated by SchemaSpy |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Analyzed at Wed May 25 01:05 IST 2022 |
View Definition:
SELECT
FOLLOW_UP_INFO.APPLICATION_NAME,
FOLLOW_UP_INFO.WORKFLOW_TYPE_NAME,
FOLLOW_UP_INFO.Application_Module,
FOLLOW_UP_INFO.IA_ISSUE_NUMBER,
FOLLOW_UP_INFO.IA_ISSUE_TITLE,
FOLLOW_UP_INFO.State_Name,
FOLLOW_UP_INFO.Originator,
FOLLOW_UP_INFO.Assigned_Resource,
FOLLOW_UP_INFO.Lead_Resource,
FOLLOW_UP_INFO.Manager_Resource,
FOLLOW_UP_INFO.STATUS,
FOLLOW_UP_INFO.PROMISSED_DUE_DATE,
FOLLOW_UP_INFO.CURRENT_DUE_DATE,
FOLLOW_UP_INFO.USER_NAME FOLLOWUP_USER_NAME,
FOLLOW_UP_INFO.TASK_PROMISSED_DUE_DATE,
FOLLOW_UP_INFO.CONFIG_ID,
FOLLOW_UP_INFO.Ticket_Number,
FOLLOW_UP_INFO.Priority,
FOLLOW_UP_INFO.INSTANCE_ID
FROM (SELECT APPLICATIONS1.APPLICATION_NAME,
WORKFLOWS.WORKFLOW_TYPE_NAME,
ISSUES.Application_Module,
instance_props.IA_ISSUE_NUMBER,
instance_props.IA_ISSUE_TITLE,
STATES.State_Name,
decode(PROJECTTASKS.Instance_Id,
NULL,
'tri2support',
(select owner
from stategps.t_instances
where PROJECTTASKS.Instance_Id = instance_id)) Originator,
PROJECTTASKS.USER_NAME Assigned_Resource,
(Select DISTINCT tpt.USER_NAME
from stategps.T_PROJECT_TASK tpt
where UPPER(tpt.RESOURCE_NAME) like UPPER('%Lead%')
and tpt.Instance_Id = PROJECTTASKS.INSTANCE_ID
AND rownum = 1) Lead_Resource,
(Select DISTINCT tpt.USER_NAME
from stategps.T_PROJECT_TASK tpt
where UPPER(tpt.RESOURCE_NAME) like UPPER('%Manager%')
and tpt.INSTANCE_ID = PROJECTTASKS.INSTANCE_ID
AND rownum = 1) Manager_Resource,
DECODE(gps_insts.instance_present,
1,
DECODE(insts.PREDECESSOR,
NULL,
'STARTED',
DECODE((SELECT DISTINCT gi.instance_present
FROM t_gps_instances gi,
t_project_task pt,
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 t_instance_predecessor ip,
t_gps_workflow_states ws1
WHERE ip.instance_id =
PROJECTTASKS.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(PROJECTTASKS.DURATION,
0,
'SKIPPED',
'STARTED'))),
0,
DECODE(PROJECTTASKS.DAYSREMAINING,
0,
DECODE(PROJECTTASKS.DURATION,
0,
'SKIPPED',
'COMPLETED'),
'SKIPPED')) STATUS,
ISSUES.DUE_DATE PROMISSED_DUE_DATE,
insts.due_date CURRENT_DUE_DATE,
ISSUES.USER_NAME,
PROJECTTASKS.PROMISED_DUE_DATE TASK_PROMISSED_DUE_DATE,
PROJECTTASKS.Priority,
PROJECTTASKS.INSTANCE_ID,
ISSUES.Ticket_Number,
Issues.Config_Id
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,
(select *
from tri2gps_dev.issues ISSUES1,
tri2gps_dev.mail_list_config MAIL_LIST_CONFIG
where ISSUES1.Issue_Number = MAIL_LIST_CONFIG.Ticket_Number) 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
AND (gps_insts.visible_flag is null OR
gps_insts.visible_flag = 'true')) FOLLOW_UP_INFO
where FOLLOW_UP_INFO.status = 'STARTED'
/*and FOLLOW_UP_INFO.USER_NAME = 'skolla'*/
Possibly Referenced Tables/Views:
![]() ![]() |