VIEW: APPS.PA_REP_PROJ_RES_COMP_V

Source

View Text - Preformatted

SELECT org.organization_id, org.name, proj.project_id, proj.name, proj.segment1, 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, hr_organization_units org, PER_JOBS pj, PER_JOB_EXTRA_INFO info, PA_PROJECTS_ALL proj, (SELECT DISTINCT asmt.resource_id, asmt.project_id FROM PA_PROJECT_ASSIGNMENTS asmt) proj_res_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.resource_organization_id = org.organization_id AND res.resource_id = proj_res_info.resource_id AND proj_res_info.project_id = proj.project_id AND res.job_id = pj.job_id AND pj.job_id = info.job_id (+) AND 'Job Category' = info.jei_information_category (+)

View Text - HTML Formatted

SELECT ORG.ORGANIZATION_ID
, ORG.NAME
, PROJ.PROJECT_ID
, PROJ.NAME
, PROJ.SEGMENT1
, 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
, HR_ORGANIZATION_UNITS ORG
, PER_JOBS PJ
, PER_JOB_EXTRA_INFO INFO
, PA_PROJECTS_ALL PROJ
, (SELECT DISTINCT ASMT.RESOURCE_ID
, ASMT.PROJECT_ID
FROM PA_PROJECT_ASSIGNMENTS ASMT) PROJ_RES_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.RESOURCE_ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND RES.RESOURCE_ID = PROJ_RES_INFO.RESOURCE_ID
AND PROJ_RES_INFO.PROJECT_ID = PROJ.PROJECT_ID
AND RES.JOB_ID = PJ.JOB_ID
AND PJ.JOB_ID = INFO.JOB_ID (+)
AND 'JOB CATEGORY' = INFO.JEI_INFORMATION_CATEGORY (+)