View: PA_RESOURCE_AVAILABILITY_V

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

View Text

SELECT RES.ROWID
, RES.RESOURCE_ID
, RES.PERSON_ID
, RES.RESOURCE_NAME FULL_NAME
, RES.RESOURCE_ORGANIZATION_ID
, ORG.NAME ORGANIZATION_NAME
, RES.MANAGER_ID
, PER1.FULL_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
, PER.EMAIL_ADDRESS
FROM PA_RESOURCES_DENORM RES
, PER_ALL_PEOPLE_F PER
, PER_ALL_PEOPLE_F PER1
, HR_ALL_ORGANIZATION_UNITS_TL ORG
WHERE 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.RESOURCE_EFFECTIVE_END_DATE >= TRUNC(SYSDATE))
AND RES.SCHEDULABLE_FLAG = 'Y'
AND RES.RESOURCE_ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND ORG.LANGUAGE = USERENV('LANG')
AND PER.PERSON_ID = RES.PERSON_ID
AND (RES.RESOURCE_EFFECTIVE_START_DATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE)
AND ( RES.MANAGER_ID IS NULL OR ( PER1.PERSON_ID = RES.MANAGER_ID
AND PER1.EFFECTIVE_START_DATE= (SELECT MIN (PER2.EFFECTIVE_START_DATE)
FROM PER_ALL_PEOPLE_F PER2
WHERE PER2.PERSON_ID = PER1.PERSON_ID
AND PER2.EFFECTIVE_END_DATE >= TRUNC(SYSDATE)) ) )
AND RES.MANAGER_ID = PER1.PERSON_ID(+)

Columns