View: PA_REP_ALL_MGR_V

Product:	PA - Projects
Description:	PA_REP_ALL_MGR_V is a view that list all direct and indirect managers in HR for a given application user has a Resource Authority
Implementation/DBA Data:	APPS.PA_REP_ALL_MGR_V

View Text

SELECT RES_MGR.MANAGER_ID
, RES_MGR.MANAGER_NAME
, RES.MANAGER_ID
, DECODE(RES_MGR.MANAGER_ID
, RES.MANAGER_ID
, 'DIRECT_REPORTS'
, 'ALL_RESOURCES')
FROM PA_REP_RES_MGR_V RES_MGR
, PA_RESOURCES_DENORM RES
, (SELECT NVL(FND_PROFILE.VALUE('PA_SUPER_RESOURCE')
, 'N') VAL
FROM DUAL) PROF
WHERE PROF.VAL='N'
AND SYSDATE BETWEEN RES.RESOURCE_EFFECTIVE_START_DATE
AND RES.RESOURCE_EFFECTIVE_END_DATE
AND RES.MANAGER_ID IN (SELECT RES2.MANAGER_ID
FROM PA_RESOURCES_DENORM RES2 CONNECT BY PRIOR RES2.PERSON_ID = RES2.MANAGER_ID
AND SYSDATE BETWEEN RES2.RESOURCE_EFFECTIVE_START_DATE
AND RES2.RESOURCE_EFFECTIVE_END_DATE
AND RES2.PERSON_ID <> PRIOR RES2.MANAGER_ID START WITH RES2.MANAGER_ID = RES_MGR.MANAGER_ID
AND SYSDATE BETWEEN RES2.RESOURCE_EFFECTIVE_START_DATE
AND RES2.RESOURCE_EFFECTIVE_END_DATE) UNION SELECT RES.MANAGER_ID
, RES.MANAGER_NAME
, RES3.MANAGER_ID
, DECODE(RES3.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
, PA_RESOURCES_DENORM RES2
, (SELECT DISTINCT MANAGER_ID
FROM PA_RESOURCES_DENORM
WHERE SYSDATE BETWEEN RESOURCE_EFFECTIVE_START_DATE
AND RESOURCE_EFFECTIVE_END_DATE) RES3
, (SELECT INSTANCE_PK1_VALUE
FROM FND_GRANTS FG
, FND_USER FU
, FND_OBJECTS FOB
, (SELECT NVL(PA_SECURITY_PVT.GET_MENU_ID('PA_PRM_RES_AUTH')
, -1) MENU_ID
FROM DUAL) RES_AUTH_MENU
WHERE FG.INSTANCE_TYPE = 'INSTANCE'
AND FG.GRANTEE_TYPE = 'USER'
AND FG.OBJECT_ID = FOB.OBJECT_ID
AND FOB.OBJ_NAME = 'ORGANIZATION'
AND FG.MENU_ID = RES_AUTH_MENU.MENU_ID
AND FG.GRANTEE_KEY = 'HZ_PARTY:' || FU.PERSON_PARTY_ID
AND FU.USER_ID = FND_GLOBAL.USER_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(FG.START_DATE)
AND TRUNC(NVL(FG.END_DATE
, SYSDATE+1)) ) RES_AUTH
, (SELECT NVL(FND_PROFILE.VALUE('PA_SUPER_RESOURCE')
, 'N') VAL
FROM DUAL) PROF
WHERE PROF.VAL='N'
AND RES.MANAGER_ID = RES2.PERSON_ID
AND RES2.RESOURCE_ORGANIZATION_ID IN RES_AUTH.INSTANCE_PK1_VALUE
AND RES3.MANAGER_ID IN (SELECT RES4.MANAGER_ID
FROM PA_RESOURCES_DENORM RES4 CONNECT BY PRIOR RES4.PERSON_ID = RES4.MANAGER_ID
AND SYSDATE BETWEEN RES4.RESOURCE_EFFECTIVE_START_DATE
AND RES4.RESOURCE_EFFECTIVE_END_DATE
AND RES4.PERSON_ID <> PRIOR RES4.MANAGER_ID START WITH RES4.MANAGER_ID = RES.MANAGER_ID
AND SYSDATE BETWEEN RES4.RESOURCE_EFFECTIVE_START_DATE
AND RES4.RESOURCE_EFFECTIVE_END_DATE) UNION ALL 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
, (SELECT NVL(FND_PROFILE.VALUE('PA_SUPER_RESOURCE')
, 'N') VAL
FROM DUAL) PROF
WHERE PROF.VAL='Y'
AND 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