View: PA_NOMINATE_RES_LOV_STAFF_V

Product:	PA - Projects
Description:	This view is for Staffing Manager. It stores information of all candidates that can be nominated by each staffing manager.
Implementation/DBA Data:	APPS.PA_NOMINATE_RES_LOV_STAFF_V

View Text

SELECT ASSIGN.PERSON_ID RESOURCE_SOURCE_ID
, PER.FULL_NAME RESOURCE_NAME
, DECODE(PER.CURRENT_EMPLOYEE_FLAG
, 'Y'
, PER.EMPLOYEE_NUMBER
, PER.NPW_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
, SUP.PERSON_ID STAFFING_ANALYST_ID
, DECODE(PER.CURRENT_EMPLOYEE_FLAG
, 'Y'
, 'EMP'
, 'CWK') PERSON_TYPE
FROM PER_ALL_PEOPLE_F PER
, PER_ALL_PEOPLE_F SUP
, HR_ALL_ORGANIZATION_UNITS HOU
, PER_ASSIGNMENTS_F ASSIGN
, PER_JOB_EXTRA_INFO JEI
, PER_ASSIGNMENT_STATUS_TYPES AST
, FND_GRANTS FG
, FND_OBJECTS FOB
, WF_ROLES WFR
, (SELECT PA_SECURITY_PVT.GET_MENU_ID('PA_PRM_RES_AUTH') MENU_ID
FROM DUAL) TEMP
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 FG.INSTANCE_PK1_VALUE = TO_CHAR(ASSIGN.ORGANIZATION_ID)
AND FG.INSTANCE_TYPE = 'INSTANCE'
AND FG.OBJECT_ID = FOB.OBJECT_ID
AND FOB.OBJ_NAME = 'ORGANIZATION'
AND FG.GRANTEE_TYPE = 'USER'
AND FG.GRANTEE_KEY = WFR.NAME
AND WFR.ORIG_SYSTEM = 'HZ_PARTY'
AND SUP.PARTY_ID = WFR.ORIG_SYSTEM_ID
AND SYSDATE BETWEEN SUP.EFFECTIVE_START_DATE
AND SUP.EFFECTIVE_END_DATE
AND FG.MENU_ID = TEMP.MENU_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(FG.START_DATE)
AND TRUNC(NVL(FG.END_DATE
, SYSDATE+1))
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'

Columns