View HLPDTEST.STATEGPS.T_PROJECT_PLAN_TASKS_V | Generated by SchemaSpy |
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Analyzed at Sun Jan 24 01:06 IST 2021 |
View Definition:
SELECT project_task_id,
COALESCE(start_date, current_date) start_date,
COALESCE(finish_date, current_date) finish_date,
COALESCE(description, state_name) TAG,
application_module,
workflow_type_name,
application_name,
DURATION,
daysremaining,
COALESCE(decode(daysremaining, 0, DURATION, NULL, 0) /
decode(daysremaining,
0,
DURATION,
COALESCE(daysremaining, DURATION)) * 100,
0) percent_completed,
task_count,
tasks_completed,
round(DURATION - duration_changes, 1) duration_changes,
user_id,
state_id,
workflow_type_id,
application_id,
workflow_order
FROM (SELECT project_task_id,
start_date,
finish_date,
DESCRIPTION,
application_module,
state_name,
workflow_type_name,
application_name,
DURATION,
daysremaining,
task_count,
tasks_completed,
duration_changes,
user_id,
state_id,
workflow_type_id,
application_id,
workflow_order
FROM (SELECT t_project_task.project_task_id,
decode(gi.instance_present,
0,
gi.created_date,
t_project_task.start_date) start_date,
decode(gi.instance_present,
0,
gi.moved_out_date,
t_project_task.finish_date) finish_date,
t_gps_workflow_states.DESCRIPTION,
issues.application_module,
t_gps_workflow_states.state_name,
wt.workflow_type_name,
A.application_name,
t_project_task.DURATION,
t_project_task.daysremaining,
decode((SELECT E.history_id
FROM t_reporting_plan_history E
WHERE E.history_id IN
(get_previous_history_id(A.application_id))
AND E.date_time < issues.created_date),
NULL,
0,
decode((SELECT DISTINCT (project_task_id)
FROM t_project_task_history H
WHERE H.project_task_id =
t_project_task.project_task_id
AND H.execution_id IN
(get_previous_history_id(A.application_id))),
NULL,
1,
0)) task_count,
decode(daysremaining,
0,
decode((SELECT count(*)
FROM t_project_task_history H
WHERE H.project_task_id =
t_project_task.project_task_id
AND H.execution_id IN
(get_previous_history_id(A.application_id))),
0,
decode((SELECT E.history_id
FROM t_reporting_plan_history E
WHERE E.history_id IN
(get_previous_history_id(A.application_id))
AND E.date_time <
issues.created_date),
NULL,
0,
1),
1,
1),
0) tasks_completed,
decode(daysremaining,
0,
t_project_task.DURATION,
nvl((SELECT MAX(DURATION)
FROM t_project_task_history H
WHERE H.project_task_id =
t_project_task.project_task_id
AND H.execution_id IN
(get_previous_history_id(A.application_id))),
0)) duration_changes,
t_project_task.user_id,
t_gps_workflow_states.state_id,
wt.workflow_type_id,
A.application_id,
wt.workflow_order
FROM stategps.t_project_task t_project_task,
stategps.t_gps_workflow_states t_gps_workflow_states,
tri2gps_dev.issues issues,
t_gps_workflow_types wt,
t_gps_applications A,
t_gps_instances gi
WHERE t_gps_workflow_states.state_id =
t_project_task.state_id
AND t_gps_workflow_states.workflow_type_id =
wt.workflow_type_id
AND wt.application_id = A.application_id
AND t_project_task.instance_id = gi.instance_id
AND t_project_task.state_id = gi.current_state_id
AND active = 1
AND A.visible_flag = 'true'
AND wt.visible_flag = 'true'
--AND A.application_id = 8522
--and gi.instance_id = 888314
AND t_project_task.DURATION > 0
AND issues.instance_id = t_project_task.instance_id) P
--This Union is to get the NOT STARTED Tasks
UNION ALL
SELECT pt.project_task_id,
pt.start_date,
pt.finish_date,
ws.DESCRIPTION,
issues.application_module,
ws.state_name,
wt.workflow_type_name,
A.application_name,
pt.DURATION,
pt.daysremaining,
decode((SELECT E.history_id
FROM t_reporting_plan_history E
WHERE E.history_id IN
(get_previous_history_id(A.application_id))
AND E.date_time < issues.created_date),
NULL,
0,
decode((SELECT DISTINCT (project_task_id)
FROM t_project_task_history H
WHERE H.project_task_id = pt.project_task_id
AND H.execution_id IN
(get_previous_history_id(A.application_id))),
NULL,
1,
0)) task_count,
0 tasks_completed,
decode(daysremaining,
0,
pt.DURATION,
nvl((SELECT MAX(DURATION)
FROM t_project_task_history H
WHERE H.project_task_id = pt.project_task_id
AND H.execution_id IN
(get_previous_history_id(A.application_id))),
0)) duration_changes,
pt.user_id,
ws.state_id,
wt.workflow_type_id,
A.application_id,
wt.workflow_order
FROM t_gps_workflow_states ws,
t_gps_workflow_types wt,
t_gps_applications A,
t_project_task pt,
t_instances TI,
tri2gps_dev.issues issues
WHERE A.application_id = wt.application_id
--AND A.application_id = 8522
AND wt.workflow_type_id = ws.workflow_type_id
AND A.visible_flag = 'true'
AND wt.visible_flag = 'true'
AND NOT EXISTS
(SELECT current_state_id
FROM t_gps_instances
WHERE pt.state_id = current_state_id
AND pt.instance_id = instance_id
AND (visible_flag IS NULL OR visible_flag = 'true'))
AND ws.state_descriptor = 'Default State'
AND pt.state_id = ws.state_id
AND pt.DURATION > 0
--and ti.instance_id = 888314
AND pt.instance_id = TI.instance_id
AND TI.application_id = A.application_id
AND TI.workflow_type_id = ws.workflow_type_id
AND TI.active = 1
AND issues.instance_id = TI.instance_id) p1
--WHERE p1.application_id = 8522
UNION ALL
--This Union is to get workflow(s) where tasks are not available
SELECT null task_id,
COALESCE(TO_DATE(tgwp.wfstartdate, 'dd/mm/yyyy'), current_date) start_date,
COALESCE(TO_DATE(tgwp.wffinishdate, 'dd/mm/yyyy'), current_date) finish_date,
NULL tag,
NULL module,
t_gps_workflow_types.workflow_type_name,
t_gps_applications.application_name,
COALESCE(to_number(tgwp.wfduration), 0) DURATION,
0 remainingdays,
0 percent_completed,
0 tasks_count,
0 tasks_completed,
0 duration_changes,
null user_id,
null state_id,
t_gps_workflow_types.workflow_type_id,
t_gps_applications.application_id,
t_gps_workflow_types.workflow_order
FROM t_gps_applications t_gps_applications,
t_gps_workflow_types t_gps_workflow_types,
(SELECT *
FROM (SELECT workflow_type_id,
MAX(CASE
WHEN property_name = 'wfPredecessors' THEN
property_value
END) wfpredecessors,
MAX(CASE
WHEN property_name = 'wfStartDate' THEN
property_value
END) wfstartdate,
MAX(CASE
WHEN property_name = 'wfFinishDate' THEN
property_value
END) wffinishdate,
MAX(CASE
WHEN property_name = 'wfDuration' THEN
property_value
END) wfduration
FROM t_gps_wft_properties
GROUP BY workflow_type_id) A
WHERE A.wfduration <> 0) tgwp
WHERE t_gps_applications.application_id =
t_gps_workflow_types.application_id
AND t_gps_workflow_types.workflow_type_id = tgwp.workflow_type_id
AND t_gps_applications.visible_flag = 'true'
AND t_gps_workflow_types.visible_flag = 'true'
AND tgwp.workflow_type_id IN
(SELECT workflow_type_id
FROM (SELECT tgwt.workflow_type_id
FROM t_gps_workflow_types tgwt
MINUS
SELECT t_instances.workflow_type_id
FROM t_instances t_instances
WHERE t_instances.active = 1))
--AND t_gps_applications.application_id = 7111
Possibly Referenced Tables/Views:
![]() ![]() |