View: PA_RESOURCE_AVAIL_STAFF_V

Product:	PA - Projects
Description:	PA_RESOURCE_AVAIL_STAFF_V is a view that shows all resources with their resource manager AND availability.
Implementation/DBA Data:	APPS.PA_RESOURCE_AVAIL_STAFF_V

View Text

SELECT RES.ROWID
, PER.PERSON_ID RESOURCE_ANALYST_ID
, RES.RESOURCE_ID
, RES.PERSON_ID
, RES.RESOURCE_NAME
, RES.RESOURCE_ORGANIZATION_ID
, ORG.NAME ORGANIZATION_NAME
, RES.MANAGER_ID
, RES.MANAGER_NAME
, RES.RESOURCE_JOB_LEVEL
, RES.RESOURCE_EFFECTIVE_START_DATE
, RES.RESOURCE_EFFECTIVE_END_DATE
, RES.RESOURCE_CITY
, RES.RESOURCE_REGION
, RES.RESOURCE_COUNTRY_CODE
, RES.RESOURCE_COUNTRY
, PA_RESOURCE_UTILS.GET_CURRENT_PROJECT_NAMENUMBER(RES.RESOURCE_ID)
, PA_RESOURCE_UTILS.GET_CURRENT_PROJECT_ID(RES.RESOURCE_ID)
, RES.RESOURCE_PERSON_TYPE
, PER1.EMAIL_ADDRESS
FROM PA_RESOURCES_DENORM RES
, PER_ALL_PEOPLE_F PER
, PER_ALL_PEOPLE_F PER1
, WF_ROLES WFR
, FND_GRANTS FG
, FND_OBJECTS FOB
, FND_MENUS TEMP
, HR_ALL_ORGANIZATION_UNITS_TL ORG
WHERE TO_NUMBER(FG.INSTANCE_PK1_VALUE) = RES.RESOURCE_ORGANIZATION_ID
AND FG.INSTANCE_SET_ID IS NULL
AND FG.INSTANCE_TYPE = 'INSTANCE'
AND FG.OBJECT_ID = FOB.OBJECT_ID
AND FOB.OBJ_NAME = 'ORGANIZATION'
AND TEMP.MENU_NAME = 'PA_PRM_RES_AUTH'
AND FG.MENU_ID = TEMP.MENU_ID
AND RES.RESOURCE_ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND ORG.LANGUAGE = USERENV('LANG')
AND FG.GRANTEE_TYPE = 'USER'
AND FG.GRANTEE_KEY = WFR.NAME
AND WFR.ORIG_SYSTEM = 'HZ_PARTY'
AND PER.PARTY_ID = WFR.ORIG_SYSTEM_ID
AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN TRUNC(FG.START_DATE)
AND TRUNC(NVL(FG.END_DATE
, SYSDATE+1))
AND RES.SCHEDULABLE_FLAG = 'Y'
AND RES.RESOURCE_EFFECTIVE_START_DATE = ( SELECT MIN (RES1.RESOURCE_EFFECTIVE_START_DATE)
FROM PA_RESOURCES_DENORM RES1
WHERE RES1.RESOURCE_ID = RES.RESOURCE_ID
AND RES1.PERSON_ID = RES.PERSON_ID
AND RES1.RESOURCE_EFFECTIVE_END_DATE >= TRUNC(SYSDATE))
AND RES.PERSON_ID = PER1.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PER1.EFFECTIVE_START_DATE
AND PER1.EFFECTIVE_END_DATE

Columns