View: PA_RESOURCE_AUTHORITY_V

Product:	PA - Projects
Description:
Implementation/DBA Data:	APPS.PA_RESOURCE_AUTHORITY_V

View Text

SELECT RES_DENORM.PERSON_ID
, RES_DENORM.RESOURCE_ID
, RES_DENORM.RESOURCE_NAME
, RES_DENORM.RESOURCE_EFFECTIVE_START_DATE
, RES_DENORM.RESOURCE_EFFECTIVE_END_DATE
, RES_DENORM.MANAGER_ID
, RES_DENORM.MANAGER_NAME RESOURCE_AUTHORITY_PERSON_NAME
, 'RESOURCE MANAGER'
FROM PA_RESOURCES_DENORM RES_DENORM
WHERE RES_DENORM.MANAGER_ID IS NOT NULL UNION SELECT RES_DENORM.PERSON_ID
, RES_DENORM.RESOURCE_ID
, RES_DENORM.RESOURCE_NAME
, RES_DENORM.RESOURCE_EFFECTIVE_START_DATE
, RES_DENORM.RESOURCE_EFFECTIVE_END_DATE
, PER.PERSON_ID
, PER.FULL_NAME
, 'STAFFING MANAGER (PRIMARY CONTACT)'
FROM PA_RESOURCES_DENORM RES_DENORM
, FND_GRANTS FG
, FND_OBJECTS FOB
, PER_PEOPLE_X PER
, (SELECT PA_SECURITY_PVT.GET_MENU_ID('PA_PRM_RES_PRMRY_CONTACT') MENU_ID
FROM DUAL) PRMRY_CONTACT_MENU
WHERE FOB.OBJ_NAME = 'ORGANIZATION'
AND FG.INSTANCE_PK1_VALUE = TO_CHAR(RES_DENORM.RESOURCE_ORGANIZATION_ID)
AND FG.INSTANCE_TYPE = 'INSTANCE'
AND FG.OBJECT_ID = FOB.OBJECT_ID
AND FG.GRANTEE_TYPE = 'USER'
AND FG.MENU_ID = PRMRY_CONTACT_MENU.MENU_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(FG.START_DATE)
AND TRUNC(NVL(FG.END_DATE
, SYSDATE+1))
AND TO_CHAR(PER.PERSON_ID) = SUBSTR(FG.GRANTEE_KEY
, 5
, 20) UNION SELECT RES_DENORM.PERSON_ID
, RES_DENORM.RESOURCE_ID
, RES_DENORM.RESOURCE_NAME
, RES_DENORM.RESOURCE_EFFECTIVE_START_DATE
, RES_DENORM.RESOURCE_EFFECTIVE_END_DATE
, PER.PERSON_ID
, PER.FULL_NAME
, 'STAFFING MANAGER'
FROM PA_RESOURCES_DENORM RES_DENORM
, FND_GRANTS FG
, FND_OBJECTS FOB
, PER_PEOPLE_X PER
, (SELECT PA_SECURITY_PVT.GET_MENU_ID('PA_PRM_RES_AUTH') MENU_ID
FROM DUAL) RES_AUTH_MENU
WHERE FOB.OBJ_NAME = 'ORGANIZATION'
AND FG.INSTANCE_PK1_VALUE = TO_CHAR(RES_DENORM.RESOURCE_ORGANIZATION_ID)
AND FG.INSTANCE_TYPE = 'INSTANCE'
AND FG.OBJECT_ID = FOB.OBJECT_ID
AND FG.GRANTEE_TYPE = 'USER'
AND FG.MENU_ID = RES_AUTH_MENU.MENU_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(FG.START_DATE)
AND TRUNC(NVL(FG.END_DATE
, SYSDATE+1))
AND TO_CHAR(PER.PERSON_ID) = SUBSTR(FG.GRANTEE_KEY
, 5
, 20)
AND PER.PERSON_ID <> RES_DENORM.MANAGER_ID
AND PER.PERSON_ID NOT IN ( SELECT PER2.PERSON_ID
FROM FND_GRANTS FG2
, FND_OBJECTS FOB2
, (SELECT PA_SECURITY_PVT.GET_MENU_ID('PA_PRM_RES_PRMRY_CONTACT') MENU_ID
FROM DUAL) PRMRY_CONTACT_MENU
, PER_PEOPLE_X PER2
WHERE FOB.OBJ_NAME = 'ORGANIZATION'
AND FG2.INSTANCE_PK1_VALUE = TO_CHAR(RES_DENORM.RESOURCE_ORGANIZATION_ID)
AND FG2.INSTANCE_TYPE = 'INSTANCE'
AND FG2.OBJECT_ID = FOB2.OBJECT_ID
AND FG2.GRANTEE_TYPE = 'USER'
AND FG2.MENU_ID = PRMRY_CONTACT_MENU.MENU_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(FG2.START_DATE)
AND TRUNC(NVL(FG2.END_DATE
, SYSDATE+1))
AND TO_CHAR(PER2.PERSON_ID) = SUBSTR(FG2.GRANTEE_KEY
, 5
, 20) )

Columns