Product: | PA - Projects |
---|---|
Description: | PA_REP_ORG_RES_COMP_V is a view that lists all resource competence details by organizations |
Implementation/DBA Data: | APPS.PA_REP_ORG_RES_COMP_V |
SELECT RES.RESOURCE_ORGANIZATION_ID
, PA_EXPENDITURES_UTILS.GETORGTLNAME(RES.RESOURCE_ORGANIZATION_ID)
, RES.RESOURCE_ID
, RES.RESOURCE_NAME
, PA_HR_COMPETENCE_UTILS.GET_RES_COMPETENCES(RES.PERSON_ID)
, PA_HR_COMPETENCE_UTILS.GET_RES_COMPETENCES_COUNT(RES.PERSON_ID)
, PA_HR_COMPETENCE_UTILS.GET_RES_COMP_LAST_UPDATED(RES.PERSON_ID)
, RES.MANAGER_ID
, RES.MANAGER_NAME
, PJ.JOB_ID
, PJ.NAME
, INFO.JEI_INFORMATION4
, INFO.JEI_INFORMATION5
, DECODE(RES.SCHEDULABLE_FLAG
, NULL
, 'N'
, RES.SCHEDULABLE_FLAG)
, RES.BILLABLE_FLAG
, RES.RESOURCE_PERSON_TYPE
FROM PA_RESOURCES_DENORM RES
, PER_JOBS PJ
, PER_JOB_EXTRA_INFO INFO
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 >= SYSDATE)
AND RES.UTILIZATION_FLAG = 'Y'
AND RES.JOB_ID = PJ.JOB_ID
AND PJ.JOB_ID = INFO.JOB_ID (+)
AND 'JOB CATEGORY' = INFO.JEI_INFORMATION_CATEGORY (+)
AND RES.PERSON_ID = (SELECT PER.PERSON_ID
FROM PER_PEOPLE_F PER
WHERE RES.PERSON_ID = PER.PERSON_ID
AND ROWNUM = 1)