View HLPDTEST.STATEGPS.T_PROJECT_PLAN_TAGS_V | Generated by SchemaSpy |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Analyzed at Mon Apr 12 01:05 IST 2021 |
View Definition:
SELECT
PROJECT_TASK_ID,
COALESCE(P1.START_DATE,CURRENT_DATE)START_DATE,
COALESCE(P1.FINISH_DATE,CURRENT_DATE)FINISH_DATE,
T_REPORTING_PLAN_TAGS.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,
T_PROJECT_PLAN_TAGS.TAG_ORDER,
T_REPORTING_PLAN_TAGS.TAG_ID
FROM
(
SELECT
PROJECT_TASK_ID,
START_DATE,
FINISH_DATE,
APPLICATION_MODULE,
WORKFLOW_TYPE_NAME,
APPLICATION_NAME,
DURATION,
DAYSREMAINING,
TASK_COUNT,
TASKS_COMPLETED,
DURATION_CHANGES,
USER_ID,
STATE_ID,
WORKFLOW_TYPE_ID,
APPLICATION_ID,
PROJECT_TAG_ID
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,
ISSUES.APPLICATION_MODULE,
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,
T_GPS_WORKFLOW_STATES.PROJECT_TAG_ID
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,
ISSUES.APPLICATION_MODULE,
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,
WS.PROJECT_TAG_ID
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
LEFT JOIN T_PROJECT_PLAN_TAGS T_PROJECT_PLAN_TAGS ON T_PROJECT_PLAN_TAGS.PROJECT_TAG_ID = P1.PROJECT_TAG_ID
LEFT JOIN T_REPORTING_PLAN_TAGS T_REPORTING_PLAN_TAGS ON T_REPORTING_PLAN_TAGS.TAG_ID = T_PROJECT_PLAN_TAGS.TAG_ID
--WHERE p1.application_id = 8522
UNION ALL
--Get TAG level estimates
SELECT DISTINCT
NULL TASK_ID,
T_PROJECT_PLAN_TAGS.START_DATE,
T_PROJECT_PLAN_TAGS.END_DATE,
T_REPORTING_PLAN_TAGS.TAG,
NULL MODULE,
NULL WORKFLOW_TYPE_NAME,
T_GPS_APPLICATIONS.APPLICATION_NAME,
T_PROJECT_PLAN_TAGS.ESTIMATED_DURATION DURATION,
NULL REMAININGDAYS,
0 PERCENT_COMPLETED,
0 TASKS_COUNT,
0 TASKS_COMPLETED,
0 DURATION_CHANGES,
NULL USER_ID,
NULL STATE_ID,
NULL WORKFLOW_TYPE_ID,
T_GPS_APPLICATIONS.APPLICATION_ID,
T_PROJECT_PLAN_TAGS.TAG_ORDER,
T_REPORTING_PLAN_TAGS.TAG_ID
FROM
T_PROJECT_PLAN_TAGS T_PROJECT_PLAN_TAGS,
T_REPORTING_PLAN_TAGS T_REPORTING_PLAN_TAGS,
T_GPS_APPLICATIONS T_GPS_APPLICATIONS,
T_GPS_WORKFLOW_TYPES T_GPS_WORKFLOW_TYPES
WHERE
T_PROJECT_PLAN_TAGS.TAG_ID NOT IN(
SELECT
P_TAGS.TAG_ID
FROM
T_INSTANCES T_INSTANCES,
T_GPS_WORKFLOW_STATES T_GPS_WORKFLOW_STATES,
T_PROJECT_TASK T_PROJECT_TASK,
T_PROJECT_PLAN_TAGS P_TAGS
WHERE
T_INSTANCES.ACTIVE = 1
AND T_GPS_WORKFLOW_STATES.PROJECT_TAG_ID IS NOT NULL
AND T_PROJECT_TASK.INSTANCE_ID IS NOT NULL
AND T_INSTANCES.INSTANCE_ID = T_PROJECT_TASK.INSTANCE_ID
AND T_GPS_WORKFLOW_STATES.STATE_ID = T_PROJECT_TASK.STATE_ID
AND P_TAGS.PROJECT_TAG_ID = T_GPS_WORKFLOW_STATES.PROJECT_TAG_ID
AND P_TAGS.PROJECT_ID = T_GPS_APPLICATIONS.APPLICATION_ID
)
AND T_PROJECT_PLAN_TAGS.TAG_ID = T_REPORTING_PLAN_TAGS.TAG_ID
AND T_GPS_APPLICATIONS.VISIBLE_FLAG = 'true'
AND T_GPS_WORKFLOW_TYPES.VISIBLE_FLAG = 'true'
AND T_GPS_APPLICATIONS.APPLICATION_ID = T_GPS_WORKFLOW_TYPES.APPLICATION_ID
AND T_GPS_APPLICATIONS.APPLICATION_ID = T_PROJECT_PLAN_TAGS.PROJECT_ID
AND T_PROJECT_PLAN_TAGS.ESTIMATED_DURATION IS NOT NULL AND T_PROJECT_PLAN_TAGS.ESTIMATED_DURATION > 0
Possibly Referenced Tables/Views:
![]() ![]() |