VIEW: APPS.PA_REP_MANAGERS_V

Source

View Text - Preformatted

SELECT res.manager_id ,res.manager_name ,res2.manager_id ,decode(res2.manager_id, res.manager_id, 'DIRECT_REPORTS', 'ALL_RESOURCES') FROM (select distinct manager_id, manager_name from pa_resources_denorm WHERE sysdate between resource_effective_start_date and resource_effective_end_date and manager_name is not null) res ,(select distinct manager_id from pa_resources_denorm where sysdate between resource_effective_start_date and resource_effective_end_date) res2 WHERE res2.manager_id in (select distinct res3.manager_id from pa_resources_denorm res3 connect by prior res3.person_id = res3.manager_id and sysdate between res3.resource_effective_start_date and res3.resource_effective_end_date and res3.person_id <> prior res3.manager_id start with res3.manager_id = res.manager_id and sysdate between res3.resource_effective_start_date and res3.resource_effective_end_date)

View Text - HTML Formatted

SELECT RES.MANAGER_ID
, RES.MANAGER_NAME
, RES2.MANAGER_ID
, DECODE(RES2.MANAGER_ID
, RES.MANAGER_ID
, 'DIRECT_REPORTS'
, 'ALL_RESOURCES')
FROM (SELECT DISTINCT MANAGER_ID
, MANAGER_NAME
FROM PA_RESOURCES_DENORM
WHERE SYSDATE BETWEEN RESOURCE_EFFECTIVE_START_DATE
AND RESOURCE_EFFECTIVE_END_DATE
AND MANAGER_NAME IS NOT NULL) RES
, (SELECT DISTINCT MANAGER_ID
FROM PA_RESOURCES_DENORM
WHERE SYSDATE BETWEEN RESOURCE_EFFECTIVE_START_DATE
AND RESOURCE_EFFECTIVE_END_DATE) RES2
WHERE RES2.MANAGER_ID IN (SELECT DISTINCT RES3.MANAGER_ID
FROM PA_RESOURCES_DENORM RES3 CONNECT BY PRIOR RES3.PERSON_ID = RES3.MANAGER_ID
AND SYSDATE BETWEEN RES3.RESOURCE_EFFECTIVE_START_DATE
AND RES3.RESOURCE_EFFECTIVE_END_DATE
AND RES3.PERSON_ID <> PRIOR RES3.MANAGER_ID START WITH RES3.MANAGER_ID = RES.MANAGER_ID
AND SYSDATE BETWEEN RES3.RESOURCE_EFFECTIVE_START_DATE
AND RES3.RESOURCE_EFFECTIVE_END_DATE)