View: PA_C_ELIG_RESOURCE_V

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

View Text

SELECT PER.PERSON_ID RESOURCE_SOURCE_ID
, RES.RESOURCE_ID
, PER.FULL_NAME RESOURCE_NAME
, PER.FIRST_NAME RESOURCE_FIRST_NAME
, PER.LAST_NAME RESOURCE_LAST_NAME
, PER.MIDDLE_NAMES RESOURCE_MIDDLE_NAMES
, TYPE.RESOURCE_TYPE_CODE RESOURCE_TYPE
, RES.RESOURCE_TYPE_ID RESOURCE_TYPE_ID
, DECODE(PER.CURRENT_EMPLOYEE_FLAG
, 'Y'
, PER.EMPLOYEE_NUMBER
, PER.NPW_NUMBER) RESOURCE_NUMBER
, ASSIGN.ORGANIZATION_ID RESOURCE_ORGANIZATION_ID
, ORG.NAME
, ASSIGN.EFFECTIVE_START_DATE ASSIGNMENT_START_DATE
, ASSIGN.EFFECTIVE_END_DATE ASSIGNMENT_END_DATE
, NULL
, NULL
, DECODE(PER.CURRENT_EMPLOYEE_FLAG
, 'Y'
, 'EMP'
, 'CWK') PERSON_TYPE
, PER.EMAIL_ADDRESS RESOURCE_EMAIL_ADDRESS
FROM PER_PEOPLE_F PER
, PER_ASSIGNMENTS_F ASSIGN
, PER_ASSIGNMENT_STATUS_TYPES AST
, PA_RESOURCES RES
, PA_RESOURCE_TYPES TYPE
, PA_RESOURCE_TXN_ATTRIBUTES TXN
, PER_JOB_EXTRA_INFO JEI
, HR_ALL_ORGANIZATION_UNITS_TL ORG
WHERE PER.PERSON_ID = ASSIGN.PERSON_ID
AND (PER.EMPLOYEE_NUMBER IS NOT NULL OR PER.NPW_NUMBER IS NOT NULL)
AND LEAST(GREATEST(ASSIGN.EFFECTIVE_START_DATE
, SYSDATE)
, ASSIGN.EFFECTIVE_END_DATE) BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND EXISTS (SELECT ORGANIZATION_ID
FROM PA_ALL_ORGANIZATIONS
WHERE PA_ORG_USE_TYPE = 'EXPENDITURES'
AND INACTIVE_DATE IS NULL
AND ORGANIZATION_ID = ASSIGN.ORGANIZATION_ID)
AND ASSIGN.PRIMARY_FLAG = 'Y'
AND ASSIGN.ASSIGNMENT_TYPE IN ('E'
, 'C')
AND ASSIGN.JOB_ID IS NOT NULL
AND ASSIGN.JOB_ID = JEI.JOB_ID
AND JEI.JEI_INFORMATION_CATEGORY = 'JOB CATEGORY'
AND JEI.JEI_INFORMATION6 = 'Y'
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASSIGN.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN'
, 'ACTIVE_CWK')
AND PER.PERSON_ID = TXN.PERSON_ID(+)
AND TXN.RESOURCE_ID = RES.RESOURCE_ID(+)
AND RES.RESOURCE_TYPE_ID = TYPE.RESOURCE_TYPE_ID(+)
AND ORG.ORGANIZATION_ID=ASSIGN.ORGANIZATION_ID
AND ORG.LANGUAGE = USERENV ('LANG') UNION SELECT PER.PERSON_ID RESOURCE_SOURCE_ID
, RES.RESOURCE_ID
, PER.FULL_NAME RESOURCE_NAME
, PER.FIRST_NAME RESOURCE_FIRST_NAME
, PER.LAST_NAME RESOURCE_LAST_NAME
, PER.MIDDLE_NAMES RESOURCE_MIDDLE_NAMES
, TYPE.RESOURCE_TYPE_CODE RESOURCE_TYPE
, RES.RESOURCE_TYPE_ID RESOURCE_TYPE_ID
, DECODE(PER.CURRENT_EMPLOYEE_FLAG
, 'Y'
, PER.EMPLOYEE_NUMBER
, PER.NPW_NUMBER) RESOURCE_NUMBER
, ASSIGN.ORGANIZATION_ID RESOURCE_ORGANIZATION_ID
, ORG.NAME
, ASSIGN.EFFECTIVE_START_DATE ASSIGNMENT_START_DATE
, ASSIGN.EFFECTIVE_END_DATE ASSIGNMENT_END_DATE
, NULL
, NULL
, DECODE(PER.CURRENT_EMPLOYEE_FLAG
, 'Y'
, 'EMP'
, 'CWK') PERSON_TYPE
, PER.EMAIL_ADDRESS RESOURCE_EMAIL_ADDRESS
FROM PER_PEOPLE_F PER
, PER_ASSIGNMENTS_F ASSIGN
, PER_ASSIGNMENT_STATUS_TYPES AST
, PA_RESOURCES RES
, PA_RESOURCE_TYPES TYPE
, PA_RESOURCE_TXN_ATTRIBUTES TXN
, PER_JOB_EXTRA_INFO JEI
, HR_ALL_ORGANIZATION_UNITS_TL ORG
WHERE PER.PERSON_ID = ASSIGN.PERSON_ID AND(PER.EMPLOYEE_NUMBER IS NOT NULL OR PER.NPW_NUMBER IS NOT NULL)
AND (ASSIGN.EFFECTIVE_START_DATE-1) BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND EXISTS (SELECT ORGANIZATION_ID
FROM PA_ALL_ORGANIZATIONS
WHERE PA_ORG_USE_TYPE = 'EXPENDITURES'
AND INACTIVE_DATE IS NULL
AND ORGANIZATION_ID = ASSIGN.ORGANIZATION_ID)
AND ASSIGN.PRIMARY_FLAG = 'Y'
AND ASSIGN.ASSIGNMENT_TYPE IN ('E'
, 'C')
AND RES.FUTURE_TERM_WF_FLAG = 'Y'
AND ASSIGN.JOB_ID IS NOT NULL
AND ASSIGN.JOB_ID = JEI.JOB_ID
AND JEI.JEI_INFORMATION_CATEGORY = 'JOB CATEGORY'
AND JEI.JEI_INFORMATION6 = 'Y'
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASSIGN.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS IN ('TERM_ASSIGN')
AND PER.PERSON_ID = TXN.PERSON_ID
AND TXN.RESOURCE_ID = RES.RESOURCE_ID
AND RES.RESOURCE_TYPE_ID = TYPE.RESOURCE_TYPE_ID
AND ORG.ORGANIZATION_ID = ASSIGN.ORGANIZATION_ID
AND ORG.LANGUAGE = USERENV('LANG')

Columns