View HLPDTEST.STATEGPS.T_PROJECT_CRITICAL_TASKS_V | Generated by SchemaSpy |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Analyzed at Mon Mar 01 01:05 IST 2021 |
View Definition:
SELECT T_GPS_APPLICATIONS.APPLICATION_ID,
T_GPS_APPLICATIONS.APPLICATION_NAME,
T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID,
T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_NAME,
T_INSTANCES.INSTANCE_ID,
T_INSTANCES.PRIORITY,
SUBSTR((SELECT IP.PROPERTY_VALUE
FROM T_GPS_INSTANCE_PROPERTIES IP,
T_GPS_WORKFLOW_INSTANCES WI,
T_GPS_WF_INSTANCE_CLASSES WIC,
T_GPS_WF_INST_CLASS_PROPS WICP
WHERE T_INSTANCES.INSTANCE_ID = IP.INSTANCE_ID
AND (T_GPS_APPLICATIONS.APPLICATION_ID = WI.APPLICATION_ID OR
T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID =
WI.WORKFLOW_TYPE_ID)
AND WI.INSTANCE_ID = WIC.INSTANCE_ID
AND WIC.CLASS_ID = WICP.CLASS_ID
AND WICP.REGISTER_AS = 'TASK_NUMBER'
AND IP.PROPERTY_NAME LIKE '%.' || WICP.PROPERTY_NAME) ||
' - ' ||
(SELECT IP.PROPERTY_VALUE
FROM T_GPS_INSTANCE_PROPERTIES IP,
T_GPS_WORKFLOW_INSTANCES WI,
T_GPS_WF_INSTANCE_CLASSES WIC,
T_GPS_WF_INST_CLASS_PROPS WICP
WHERE T_INSTANCES.INSTANCE_ID = IP.INSTANCE_ID
AND (T_GPS_APPLICATIONS.APPLICATION_ID = WI.APPLICATION_ID OR
T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID =
WI.WORKFLOW_TYPE_ID)
AND WI.INSTANCE_ID = WIC.INSTANCE_ID
AND WIC.CLASS_ID = WICP.CLASS_ID
AND WICP.REGISTER_AS = 'TASK_NAME'
AND IP.PROPERTY_NAME LIKE '%.' || WICP.PROPERTY_NAME),
0,
20) || '...',
T_PROJECT_TASK.PROJECT_TASK_ID,
SUBSTR(T_GPS_WORKFLOW_STATES.STATE_NAME, 0, 20) || '...',
T_PROJECT_TASK.START_DATE,
T_PROJECT_TASK.FINISH_DATE,
T_PROJECT_TASK.DURATION,
T_PROJECT_TASK.DAYSREMAINING,
((T_PROJECT_TASK.DURATION -
NVL(T_PROJECT_TASK.DAYSREMAINING, T_PROJECT_TASK.DURATION)) * 100 /
T_PROJECT_TASK.DURATION),
T_PROJECT_TASK.USER_NAME,DUE_DATE, STATE_ORDER,WORKFLOW_ORDER,
(SELECT MAX(PROPERTY_VALUE) FROM T_GPS_WFT_PROPERTIES WFTP WHERE WFTP.WORKFLOW_TYPE_ID=
T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID AND PROPERTY_NAME='wfPredecessors') PREDECESSORS
FROM STATEGPS.T_INSTANCES T_INSTANCES,
STATEGPS.T_PROJECT_TASK T_PROJECT_TASK,
STATEGPS.T_GPS_WORKFLOW_STATES T_GPS_WORKFLOW_STATES,
STATEGPS.T_GPS_WORKFLOW_TYPES T_GPS_WORKFLOW_TYPES,
STATEGPS.T_GPS_APPLICATIONS T_GPS_APPLICATIONS
WHERE T_PROJECT_TASK.STATE_ID = T_GPS_WORKFLOW_STATES.STATE_ID
AND T_INSTANCES.WORKFLOW_TYPE_ID = T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID
AND T_INSTANCES.APPLICATION_ID = T_GPS_APPLICATIONS.APPLICATION_ID
AND T_INSTANCES.INSTANCE_ID = T_PROJECT_TASK.INSTANCE_ID
AND T_PROJECT_TASK.CRITICAL = 1
AND T_PROJECT_TASK.DURATION > 0
AND (T_PROJECT_TASK.DAYSREMAINING > 0 OR
T_PROJECT_TASK.DAYSREMAINING IS NULL)
AND T_PROJECT_TASK.START_DATE IS NOT NULL
AND T_PROJECT_TASK.FINISH_DATE IS NOT NULL
AND T_INSTANCES.ACTIVE = 1
--AND T_GPS_APPLICATIONS.APPLICATION_ID=8722
UNION
SELECT
T_GPS_APPLICATIONS.APPLICATION_ID,
T_GPS_APPLICATIONS.APPLICATION_NAME,
T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID,
T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_NAME,
NULL INSTANCE_ID,
NULL PRIORITY,
NULL TASK,
NULL PROJECT_TASK_ID,
NULL STATE_NAME,
NVL(TO_DATE(TGWP.WFSTARTDATE,'DD/MM/YYYY'),SYSDATE) START_DATE,
NVL(TO_DATE(TGWP.WFFINISHDATE,'DD/MM/YYYY'),SYSDATE) FINISH_DATE,
NVL(TO_NUMBER(TGWP.WFDURATION),0) DURATION,
NULL DAYSREMAINING,
0 PERCENT_COMPLETED,
'' USER_NAME,NULL DUE_DATE, NULL STATE_ORDER,WORKFLOW_ORDER,
WFPREDECESSORS PREDECESSORS
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,
MAX(
CASE
WHEN PROPERTY_NAME = 'critical' THEN PROPERTY_VALUE
END
) CRITICAL
FROM
T_GPS_WFT_PROPERTIES
GROUP BY
WORKFLOW_TYPE_ID
) AK
WHERE
AK.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.CRITICAL = 1
--AND T_GPS_APPLICATIONS.APPLICATION_ID=8722
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
)
) ORDER BY DUE_DATE, STATE_ORDER,WORKFLOW_ORDER ASC
Possibly Referenced Tables/Views:
![]() ![]() |