View: PA_C_RES_DENORM_V

Product:	PA - Projects
Description:	PA_C_RES_DENORM_V is a view that identifies all resources that are available for use at this time or in the future based on resources available in Projects
Implementation/DBA Data:	APPS.PA_C_RES_DENORM_V

View Text

SELECT PER.PERSON_ID RESOURCE_SOURCE_ID
, TXN.RESOURCE_ID
, RESDENORM.RESOURCE_NAME RESOURCE_NAME
, NULL RESOURCE_FIRST_NAME
, NULL RESOURCE_LAST_NAME
, NULL RESOURCE_MIDDLE_NAMES
, NULL RESOURCE_TYPE
, NULL RESOURCE_TYPE_ID
, DECODE(PER.CURRENT_EMPLOYEE_FLAG
, 'Y'
, PER.EMPLOYEE_NUMBER
, PER.NPW_NUMBER) RESOURCE_NUMBER
, RESDENORM.RESOURCE_ORGANIZATION_ID RESOURCE_ORGANIZATION_ID
, ORG.NAME RESOURCE_ORGANIZATION_NAME
, RESDENORM.RESOURCE_EFFECTIVE_START_DATE ASSIGNMENT_START_DATE
, RESDENORM.RESOURCE_EFFECTIVE_END_DATE ASSIGNMENT_END_DATE
, PER.EMAIL_ADDRESS RESOURCE_EMAIL_ADDRESS
, FLV.MEANING RESOURCE_PERSON_TYPE
FROM PER_PEOPLE_F PER
, PA_RESOURCES_DENORM RESDENORM
, PA_RESOURCE_TXN_ATTRIBUTES TXN
, HR_ALL_ORGANIZATION_UNITS_TL ORG
, FND_LOOKUP_VALUES FLV
WHERE PER.PERSON_ID = TXN.PERSON_ID(+)
AND (PER.EMPLOYEE_NUMBER IS NOT NULL OR PER.NPW_NUMBER IS NOT NULL)
AND (PER.CURRENT_EMPLOYEE_FLAG = 'Y' OR PER.CURRENT_NPW_FLAG = 'Y') AND TXN.PERSON_ID = RESDENORM.PERSON_ID
AND RESDENORM.RESOURCE_EFFECTIVE_START_DATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND RESDENORM.RESOURCE_EFFECTIVE_START_DATE = (SELECT MIN(RESDENORM1.RESOURCE_EFFECTIVE_START_DATE) FROM PA_RESOURCES_DENORM RESDENORM1
WHERE RESDENORM1.PERSON_ID = PER.PERSON_ID AND RESDENORM1.RESOURCE_EFFECTIVE_END_DATE >= TRUNC(SYSDATE))
AND ORG.ORGANIZATION_ID=RESDENORM.RESOURCE_ORGANIZATION_ID
AND ORG.LANGUAGE = USERENV ('LANG')
AND FLV.VIEW_APPLICATION_ID=3
AND FLV.LOOKUP_TYPE = 'PERSON_TYPE'
AND FLV.LANGUAGE = USERENV('LANG')
AND FLV.LOOKUP_CODE=DECODE(PER.CURRENT_EMPLOYEE_FLAG
, 'Y'
, 'EMP'
, 'CWK')

Columns