View HLPDTEST.STATEGPS.T_PRODUCTS_SUMMARY_V | Generated by SchemaSpy |
View Definition:
SELECT APPS.APPLICATION_NAME,NULL PROCESS,PLANNED.START_DATE,PLANNED.END_DATE,ACTUAL.ACTUAL_START_DATE,ACTUAL.ACTUAL_END_DATE,STATUS.PROJECT_SUMMARY FROM
(SELECT APPLICATION_ID, MIN(START_DATE) START_DATE,MAX(FINISH_DATE) END_DATE
FROM T_PROJECT_TASK_V WHERE APPLICATION_ID = 2818 GROUP BY APPLICATION_ID) PLANNED,
(SELECT APPLICATION_ID, LISTAGG(STATUS_COUNT,', ') WITHIN GROUP(ORDER BY STATUS_COUNT) PROJECT_SUMMARY
FROM (SELECT APPLICATION_ID,STATUS||':'||COUNT(DISTINCT PROPERTY3) AS STATUS_COUNT
FROM T_PROJECT_TASK_V WHERE APPLICATION_ID = 2818 GROUP BY STATUS,APPLICATION_ID) GROUP BY APPLICATION_ID) STATUS,
(SELECT INST.APPLICATION_ID,MIN(ACTIVITY.CREATED_DATE) ACTUAL_START_DATE,MAX(ACTIVITY.CREATED_DATE) ACTUAL_END_DATE
FROM T_PROJECT_ACTIVITY ACTIVITY , T_PROJECT_TASK TASK, T_GPS_INSTANCES INST
WHERE ACTIVITY.PROJECT_TASK_ID = TASK.PROJECT_TASK_ID
AND TASK.INSTANCE_ID = INST.INSTANCE_ID
AND INST.APPLICATION_ID = 2818 GROUP BY INST.APPLICATION_ID) ACTUAL,
T_GPS_APPLICATIONS APPS
WHERE PLANNED.APPLICATION_ID = ACTUAL.APPLICATION_ID
AND ACTUAL.APPLICATION_ID = STATUS.APPLICATION_ID
AND STATUS.APPLICATION_ID = APPS.APPLICATION_ID
UNION
SELECT NULL APPNAME,WORKFLOWS.WORKFLOW_TYPE_NAME,PLANNED.START_DATE,PLANNED.END_DATE,ACTUAL.ACTUAL_START_DATE,ACTUAL.ACTUAL_END_DATE,STATUS.PROJECT_SUMMARY FROM
(SELECT WORKFLOW_TYPE_ID, MIN(START_DATE) START_DATE,MAX(FINISH_DATE) END_DATE
FROM T_PROJECT_TASK_V WHERE APPLICATION_ID = 2818 GROUP BY WORKFLOW_TYPE_ID) PLANNED,
(SELECT WORKFLOW_TYPE_ID, LISTAGG(STATUS_COUNT,', ') WITHIN GROUP(ORDER BY STATUS_COUNT) PROJECT_SUMMARY
FROM (SELECT WORKFLOW_TYPE_ID,STATUS||':'||COUNT(DISTINCT PROPERTY3) AS STATUS_COUNT
FROM T_PROJECT_TASK_V WHERE APPLICATION_ID = 2818 GROUP BY STATUS,WORKFLOW_TYPE_ID) GROUP BY WORKFLOW_TYPE_ID) STATUS,
(SELECT INST.WORKFLOW_TYPE_ID,MIN(ACTIVITY.CREATED_DATE) ACTUAL_START_DATE,MAX(ACTIVITY.CREATED_DATE) ACTUAL_END_DATE
FROM T_PROJECT_ACTIVITY ACTIVITY , T_PROJECT_TASK TASK, T_GPS_INSTANCES INST
WHERE ACTIVITY.PROJECT_TASK_ID = TASK.PROJECT_TASK_ID
AND TASK.INSTANCE_ID = INST.INSTANCE_ID
AND INST.APPLICATION_ID = 2818 GROUP BY INST.WORKFLOW_TYPE_ID) ACTUAL,
T_GPS_WORKFLOW_TYPES WORKFLOWS
WHERE PLANNED.WORKFLOW_TYPE_ID = ACTUAL.WORKFLOW_TYPE_ID
AND ACTUAL.WORKFLOW_TYPE_ID = STATUS.WORKFLOW_TYPE_ID
AND STATUS.WORKFLOW_TYPE_ID = WORKFLOWS.WORKFLOW_TYPE_ID
Possibly Referenced Tables/Views: