VIEW: APPS.HR_DM_MIGRATION_SUMMARY_V

Source

View Text - Preformatted

SELECT phase_id, (select count(*) from hr_dm_phase_items pi WHERE pi.status ='C' and pi.phase_id = ph.phase_id) PI_C, (select count(*) from hr_dm_phase_items pi where pi.status ='NS' and pi.phase_id = ph.phase_id) PI_NS, (select count(*) from hr_dm_phase_items pi where pi.status ='S' and pi.phase_id = ph.phase_id) PI_S, (select count(*) from hr_dm_phase_items pi where pi.status ='E' and pi.phase_id = ph.phase_id) PI_E, (select count(*) from hr_dm_migration_ranges mr, hr_dm_phase_items pi2 where mr.status ='C' and mr.phase_item_id = pi2.phase_item_id and pi2.phase_id = (select ph2.phase_id from hr_dm_phases ph2 where ph2.phase_name = 'R' and ph2.migration_id = ph.migration_id) and ph.phase_name in ('DP', 'UP', 'D')) RNG_C, (select count(*) from hr_dm_migration_ranges mr, hr_dm_phase_items pi2 where mr.status ='NS' and mr.phase_item_id = pi2.phase_item_id and pi2.phase_id = (select ph2.phase_id from hr_dm_phases ph2 where ph2.phase_name = 'R' and ph2.migration_id = ph.migration_id) and ph.phase_name in ('DP', 'UP', 'D')) RNG_NS, (select count(*) from hr_dm_migration_ranges mr, hr_dm_phase_items pi2 where mr.status ='S' and mr.phase_item_id = pi2.phase_item_id and pi2.phase_id = (select ph2.phase_id from hr_dm_phases ph2 where ph2.phase_name = 'R' and ph2.migration_id = ph.migration_id) and ph.phase_name in ('DP', 'UP', 'D')) RNG_S, (select count(*) from hr_dm_migration_ranges mr, hr_dm_phase_items pi2 where mr.status ='E' and mr.phase_item_id = pi2.phase_item_id and pi2.phase_id = (select ph2.phase_id from hr_dm_phases ph2 where ph2.phase_name = 'R' and ph2.migration_id = ph.migration_id) and ph.phase_name in ('DP', 'UP', 'D')) RNG_E, (select count(*) from hr_dm_migration_requests req, fnd_concurrent_requests cr where cr.status_code ='C' and cr.request_id = req.request_id and req.phase_id = ph.phase_id and req.enabled_flag = 'Y' and req.phase_id IS NOT NULL) REQ_C, (select count(*) from hr_dm_migration_requests req, fnd_concurrent_requests cr where cr.status_code in ('P', 'Q') and cr.request_id = req.request_id and req.phase_id = ph.phase_id and req.enabled_flag = 'Y' and req.phase_id IS NOT NULL) REQ_NS, (select count(*) from hr_dm_migration_requests req, fnd_concurrent_requests cr where cr.status_code in ('R', 'I') and cr.request_id = req.request_id and req.phase_id = ph.phase_id and req.enabled_flag = 'Y' and req.phase_id IS NOT NULL) REQ_S, (select count(*) from hr_dm_migration_requests req, fnd_concurrent_requests cr where cr.status_code in ('E', 'D', 'U', 'M', 'X', 'T') and cr.request_id = req.request_id and req.phase_id = ph.phase_id and req.enabled_flag = 'Y' and req.phase_id IS NOT NULL) REQ_E from hr_dm_phases ph

View Text - HTML Formatted

SELECT PHASE_ID
, (SELECT COUNT(*)
FROM HR_DM_PHASE_ITEMS PI
WHERE PI.STATUS ='C'
AND PI.PHASE_ID = PH.PHASE_ID) PI_C
, (SELECT COUNT(*)
FROM HR_DM_PHASE_ITEMS PI
WHERE PI.STATUS ='NS'
AND PI.PHASE_ID = PH.PHASE_ID) PI_NS
, (SELECT COUNT(*)
FROM HR_DM_PHASE_ITEMS PI
WHERE PI.STATUS ='S'
AND PI.PHASE_ID = PH.PHASE_ID) PI_S
, (SELECT COUNT(*)
FROM HR_DM_PHASE_ITEMS PI
WHERE PI.STATUS ='E'
AND PI.PHASE_ID = PH.PHASE_ID) PI_E
, (SELECT COUNT(*)
FROM HR_DM_MIGRATION_RANGES MR
, HR_DM_PHASE_ITEMS PI2
WHERE MR.STATUS ='C'
AND MR.PHASE_ITEM_ID = PI2.PHASE_ITEM_ID
AND PI2.PHASE_ID = (SELECT PH2.PHASE_ID
FROM HR_DM_PHASES PH2
WHERE PH2.PHASE_NAME = 'R'
AND PH2.MIGRATION_ID = PH.MIGRATION_ID)
AND PH.PHASE_NAME IN ('DP'
, 'UP'
, 'D')) RNG_C
, (SELECT COUNT(*)
FROM HR_DM_MIGRATION_RANGES MR
, HR_DM_PHASE_ITEMS PI2
WHERE MR.STATUS ='NS'
AND MR.PHASE_ITEM_ID = PI2.PHASE_ITEM_ID
AND PI2.PHASE_ID = (SELECT PH2.PHASE_ID
FROM HR_DM_PHASES PH2
WHERE PH2.PHASE_NAME = 'R'
AND PH2.MIGRATION_ID = PH.MIGRATION_ID)
AND PH.PHASE_NAME IN ('DP'
, 'UP'
, 'D')) RNG_NS
, (SELECT COUNT(*)
FROM HR_DM_MIGRATION_RANGES MR
, HR_DM_PHASE_ITEMS PI2
WHERE MR.STATUS ='S'
AND MR.PHASE_ITEM_ID = PI2.PHASE_ITEM_ID
AND PI2.PHASE_ID = (SELECT PH2.PHASE_ID
FROM HR_DM_PHASES PH2
WHERE PH2.PHASE_NAME = 'R'
AND PH2.MIGRATION_ID = PH.MIGRATION_ID)
AND PH.PHASE_NAME IN ('DP'
, 'UP'
, 'D')) RNG_S
, (SELECT COUNT(*)
FROM HR_DM_MIGRATION_RANGES MR
, HR_DM_PHASE_ITEMS PI2
WHERE MR.STATUS ='E'
AND MR.PHASE_ITEM_ID = PI2.PHASE_ITEM_ID
AND PI2.PHASE_ID = (SELECT PH2.PHASE_ID
FROM HR_DM_PHASES PH2
WHERE PH2.PHASE_NAME = 'R'
AND PH2.MIGRATION_ID = PH.MIGRATION_ID)
AND PH.PHASE_NAME IN ('DP'
, 'UP'
, 'D')) RNG_E
, (SELECT COUNT(*)
FROM HR_DM_MIGRATION_REQUESTS REQ
, FND_CONCURRENT_REQUESTS CR
WHERE CR.STATUS_CODE ='C'
AND CR.REQUEST_ID = REQ.REQUEST_ID
AND REQ.PHASE_ID = PH.PHASE_ID
AND REQ.ENABLED_FLAG = 'Y'
AND REQ.PHASE_ID IS NOT NULL) REQ_C
, (SELECT COUNT(*)
FROM HR_DM_MIGRATION_REQUESTS REQ
, FND_CONCURRENT_REQUESTS CR
WHERE CR.STATUS_CODE IN ('P'
, 'Q')
AND CR.REQUEST_ID = REQ.REQUEST_ID
AND REQ.PHASE_ID = PH.PHASE_ID
AND REQ.ENABLED_FLAG = 'Y'
AND REQ.PHASE_ID IS NOT NULL) REQ_NS
, (SELECT COUNT(*)
FROM HR_DM_MIGRATION_REQUESTS REQ
, FND_CONCURRENT_REQUESTS CR
WHERE CR.STATUS_CODE IN ('R'
, 'I')
AND CR.REQUEST_ID = REQ.REQUEST_ID
AND REQ.PHASE_ID = PH.PHASE_ID
AND REQ.ENABLED_FLAG = 'Y'
AND REQ.PHASE_ID IS NOT NULL) REQ_S
, (SELECT COUNT(*)
FROM HR_DM_MIGRATION_REQUESTS REQ
, FND_CONCURRENT_REQUESTS CR
WHERE CR.STATUS_CODE IN ('E'
, 'D'
, 'U'
, 'M'
, 'X'
, 'T')
AND CR.REQUEST_ID = REQ.REQUEST_ID
AND REQ.PHASE_ID = PH.PHASE_ID
AND REQ.ENABLED_FLAG = 'Y'
AND REQ.PHASE_ID IS NOT NULL) REQ_E
FROM HR_DM_PHASES PH