View: PA_REP_MANAGERS_V

Product:	PA - Projects
Description:	PA_REP_RESOURCES_V is a secured view that lists all managers in the system
Implementation/DBA Data:	APPS.PA_REP_MANAGERS_V

View Text

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)

Columns