View: PA_NOMINATE_RESOURCE_LOV_V

Product:	PA - Projects
Description:	This view is for Project Super User. It stores information of all candidates.
Implementation/DBA Data:	APPS.PA_NOMINATE_RESOURCE_LOV_V

View Text

SELECT PER.PERSON_ID RESOURCE_SOURCE_ID
, PER.FULL_NAME RESOURCE_NAME
, PER.EMPLOYEE_NUMBER RESOURCE_NUMBER
, ASSIGN.ORGANIZATION_ID RESOURCE_ORGANIZATION_ID
, HOU.NAME RESOURCE_ORGANIZATION_NAME
, ASSIGN.EFFECTIVE_START_DATE ASSIGNMENT_START_DATE
, ASSIGN.EFFECTIVE_END_DATE ASSIGNMENT_END_DATE
, ASSIGN.SUPERVISOR_ID SUPERVISOR_ID
, PA_LOOKUPS.MEANING
, PER.EMAIL_ADDRESS
FROM PER_PEOPLE_F PER
, HR_ALL_ORGANIZATION_UNITS HOU
, PER_ASSIGNMENTS_F ASSIGN
, PER_JOB_EXTRA_INFO JEI
, PER_ASSIGNMENT_STATUS_TYPES AST
, PA_LOOKUPS
WHERE PER.PERSON_ID = ASSIGN.PERSON_ID
AND ASSIGN.ORGANIZATION_ID IN (SELECT DISTINCT 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 SYSDATE <= ASSIGN.EFFECTIVE_END_DATE
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASSIGN.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN'
, 'ACTIVE_CWK')
AND ASSIGN.EFFECTIVE_START_DATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND ASSIGN.ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND ASSIGN.JOB_ID = JEI.JOB_ID
AND INFORMATION_TYPE = 'JOB CATEGORY'
AND JEI.JEI_INFORMATION6 = 'Y'
AND LOOKUP_TYPE = 'PA_PERSON_TYPE'
AND ((LOOKUP_CODE = 'EMP'
AND PER.CURRENT_EMPLOYEE_FLAG = 'Y') OR (LOOKUP_CODE = 'CWK'
AND PER.CURRENT_EMPLOYEE_FLAG <> 'Y'))

Columns