VIEW: APPS.PA_REP_ORG_RES_COMP_V

Source

View Text - Preformatted

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)

View Text - HTML Formatted

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)