| View HLPDTEST.STATEGPS.MIG_COMPLETED_VS_DUE_DATE_V | Generated by SchemaSpy |
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Analyzed at Thu Aug 13 01:05 IST 2020 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
View Definition:
SELECT T_GPS_APPLICATIONS.APPLICATION_ID,
T_GPS_APPLICATIONS.APPLICATION_NAME,
T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_NAME,
T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID,
T_GPS_WORKFLOW_STATES.STATE_DESCRIPTOR,
T_GPS_WORKFLOW_STATES.STATE_ID,
T_GPS_INSTANCES.INSTANCE_ID,
T_INSTANCES.PROMISED_DUE_DATE,
T_MIGRATION_REQUESTS.MIGRATION_STATUS,
T_MIGRATION_REQUESTS.MIGRATION_WF_STATE,
T_MIGRATION_REQUESTS.LAST_UPDATED_DATE,
T_MIGRATION_REQUESTS.MIGRATION_REQUEST_NUMBER,
T_MIGRATION_REQUESTS.MIGRATION_REQUEST_TITLE,
T_MIGRATION_REQUESTS.INSTANCE_NAME,
(SELECT T_GPS_INSTANCE_PROPERTIES.PROPERTY_VALUE
FROM T_GPS_INSTANCE_PROPERTIES
WHERE INSTANCE_ID = T_INSTANCES.INSTANCE_ID
AND T_GPS_INSTANCE_PROPERTIES.PROPERTY_NAME =
'tri2.SG_TRI2_ENTITY.IA_ISSUE_NUMBER') AS ISSUE_NUMBER,
(SELECT T_GPS_INSTANCE_PROPERTIES.PROPERTY_VALUE
FROM T_GPS_INSTANCE_PROPERTIES
WHERE INSTANCE_ID = T_INSTANCES.INSTANCE_ID
AND T_GPS_INSTANCE_PROPERTIES.PROPERTY_NAME =
'tri2.SG_TRI2_ENTITY.IA_ISSUE_TITLE') AS ISSUE_TITLE,
(CASE
WHEN TRUNC(T_INSTANCES.PROMISED_DUE_DATE) <
TRUNC(T_MIGRATION_REQUESTS.LAST_UPDATED_DATE) THEN
'After'
WHEN TRUNC(T_INSTANCES.PROMISED_DUE_DATE) >
TRUNC(T_MIGRATION_REQUESTS.LAST_UPDATED_DATE) THEN
'Before'
WHEN TRUNC(T_INSTANCES.PROMISED_DUE_DATE) =
TRUNC(T_MIGRATION_REQUESTS.LAST_UPDATED_DATE) THEN
'On'
ELSE
'Without'
END) STATUS,
1 AS COUNT
FROM STATEGPS.T_GPS_APPLICATIONS T_GPS_APPLICATIONS,
STATEGPS.T_GPS_WORKFLOW_TYPES T_GPS_WORKFLOW_TYPES,
STATEGPS.T_GPS_WORKFLOW_STATES T_GPS_WORKFLOW_STATES,
STATEGPS.T_GPS_INSTANCES T_GPS_INSTANCES,
STATEGPS.T_GPS_INSTANCE_PROPERTIES T_GPS_INSTANCE_PROPERTIES,
STATEGPS.T_INSTANCES T_INSTANCES,
TRI2GPS_DEV.T_MIGRATION_REQUESTS T_MIGRATION_REQUESTS
WHERE T_GPS_APPLICATIONS.APPLICATION_ID =
T_GPS_WORKFLOW_TYPES.APPLICATION_ID
AND T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID =
T_GPS_WORKFLOW_STATES.WORKFLOW_TYPE_ID
AND T_MIGRATION_REQUESTS.MIGRATION_STATUS in
('MIGRATION COMPLETED', 'Migration Moved All Objects Skipped')
AND T_GPS_WORKFLOW_STATES.STATE_ID = T_GPS_INSTANCES.CURRENT_STATE_ID
AND T_GPS_WORKFLOW_STATES.STATE_ORDER =
(SELECT MAX(STATE_ORDER)
FROM T_GPS_WORKFLOW_STATES
WHERE T_GPS_WORKFLOW_STATES.WORKFLOW_TYPE_ID =
T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID)
AND T_GPS_WORKFLOW_TYPES.WORKFLOW_TYPE_ID =
T_GPS_INSTANCES.WORKFLOW_TYPE_ID
AND T_GPS_INSTANCES.INSTANCE_ID = T_INSTANCES.INSTANCE_ID
AND T_INSTANCES.INSTANCE_ID = T_GPS_INSTANCE_PROPERTIES.INSTANCE_ID
AND T_MIGRATION_REQUESTS.LAST_UPDATED_DATE IS NOT NULL
AND T_GPS_WORKFLOW_TYPES.VISIBLE_FLAG = 'true'
AND T_MIGRATION_REQUESTS.MIGRATION_REQUEST_NUMBER =
T_GPS_INSTANCE_PROPERTIES.PROPERTY_VALUE
AND T_GPS_INSTANCE_PROPERTIES.PROPERTY_NAME =
'tri2.SG_TRI2_ENTITY.IA_ISSUE_NUMBER'
Possibly Referenced Tables/Views:
|