View: PA_PROJ_MANAGER_V

Product:	PA - Projects
Description:	PA_PROJ_MANAGER_V is a view that shows the list of all resources who are project managers
Implementation/DBA Data:	APPS.PA_PROJ_MANAGER_V

View Text

SELECT PPF.FULL_NAME
, PPP.RESOURCE_SOURCE_ID
FROM PER_PEOPLE_X PPF
, PA_PROJECT_PARTIES PPP
, (SELECT NVL(FND_PROFILE.VALUE('PA_SUPER_PROJECT_VIEW')
, 'N') VAL
FROM DUAL) PROF
WHERE PROF.VAL='Y'
AND PPP.PROJECT_ROLE_ID = 1
AND PPP.RESOURCE_TYPE_ID = 101
AND PPF.PERSON_ID = PPP.RESOURCE_SOURCE_ID UNION SELECT PPF.FULL_NAME
, PPP.RESOURCE_SOURCE_ID
FROM PER_PEOPLE_X PPF
, PA_PROJECT_PARTIES PPP
, (SELECT NVL(FND_PROFILE.VALUE('PA_SUPER_PROJECT_VIEW')
, 'N') VAL
FROM DUAL) PROF
WHERE PROF.VAL='N'
AND PPF.PERSON_ID = FND_GLOBAL.EMPLOYEE_ID
AND PPP.RESOURCE_SOURCE_ID = PPF.PERSON_ID
AND PPP.PROJECT_ROLE_ID = 1
AND PPP.RESOURCE_TYPE_ID = 101 UNION SELECT RES.RESOURCE_NAME
, RES.PERSON_ID
FROM PA_RESOURCES_DENORM RES
, PA_PROJECT_PARTIES PPP
, (SELECT NVL(FND_PROFILE.VALUE('PA_SUPER_PROJECT_VIEW')
, 'N') VAL
FROM DUAL) PROF
, (SELECT INSTANCE_PK1_VALUE
FROM FND_GRANTS FG
, FND_USER FU
, FND_OBJECTS FOB
, (SELECT NVL(PA_SECURITY_PVT.GET_MENU_ID('PA_PRM_PROJ_AUTH')
, -1) MENU_ID
FROM DUAL) PRJ_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 = PRJ_AUTH_MENU.MENU_ID
AND FG.GRANTEE_KEY = 'PER:' || FU.EMPLOYEE_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))) AUTH_ORG
WHERE PROF.VAL='N'
AND PPP.PROJECT_ROLE_ID = 1
AND PPP.RESOURCE_TYPE_ID = 101
AND RES.PERSON_ID = PPP.RESOURCE_SOURCE_ID
AND RES.RESOURCE_ORGANIZATION_ID IN AUTH_ORG.INSTANCE_PK1_VALUE

Columns