VIEW: APPS.PA_REP_ORG_COM_RES_LST_V

Source

View Text - Preformatted

SELECT org.organization_id ,org.name ,comp.competence_id ,comp.name ,res.resource_name ,res.person_id ,rating_lvl.step_value ,res.manager_name ,res.manager_id ,job.name ,job.job_id ,res.resource_job_level ,job_info.jei_information5 ,DECODE(res.schedulable_flag,null,'N',res.schedulable_flag) ,res.resource_city || ', ' || res.resource_region || ', ' || res.resource_country_code ,res.resource_person_type FROM per_competences comp ,per_competence_elements comp_ele ,pa_resources_denorm res ,hr_organization_units org ,per_jobs job ,per_job_extra_info job_info ,per_rating_levels rating_lvl WHERE comp.competence_id = comp_ele.competence_id AND comp_ele.person_id = res.person_id AND res.resource_organization_id = org.organization_id AND 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.person_id in (select res.person_id from per_people_f per_pl where per_pl.person_id = res.person_id and rownum = 1) AND res.utilization_flag = 'Y' AND res.job_id = job.job_id AND res.job_id = job_info.job_id (+) AND 'Job Category' = job_info.jei_information_category (+) AND comp_ele.proficiency_level_id = rating_lvl.rating_level_id (+) AND trunc(SYSDATE) BETWEEN trunc(comp_ele.EFFECTIVE_DATE_FROM) AND NVL(trunc(comp_ele.EFFECTIVE_DATE_TO),SYSDATE+1)

View Text - HTML Formatted

SELECT ORG.ORGANIZATION_ID
, ORG.NAME
, COMP.COMPETENCE_ID
, COMP.NAME
, RES.RESOURCE_NAME
, RES.PERSON_ID
, RATING_LVL.STEP_VALUE
, RES.MANAGER_NAME
, RES.MANAGER_ID
, JOB.NAME
, JOB.JOB_ID
, RES.RESOURCE_JOB_LEVEL
, JOB_INFO.JEI_INFORMATION5
, DECODE(RES.SCHEDULABLE_FLAG
, NULL
, 'N'
, RES.SCHEDULABLE_FLAG)
, RES.RESOURCE_CITY || '
, ' || RES.RESOURCE_REGION || '
, ' || RES.RESOURCE_COUNTRY_CODE
, RES.RESOURCE_PERSON_TYPE
FROM PER_COMPETENCES COMP
, PER_COMPETENCE_ELEMENTS COMP_ELE
, PA_RESOURCES_DENORM RES
, HR_ORGANIZATION_UNITS ORG
, PER_JOBS JOB
, PER_JOB_EXTRA_INFO JOB_INFO
, PER_RATING_LEVELS RATING_LVL
WHERE COMP.COMPETENCE_ID = COMP_ELE.COMPETENCE_ID
AND COMP_ELE.PERSON_ID = RES.PERSON_ID
AND RES.RESOURCE_ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND 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.PERSON_ID IN (SELECT RES.PERSON_ID
FROM PER_PEOPLE_F PER_PL
WHERE PER_PL.PERSON_ID = RES.PERSON_ID
AND ROWNUM = 1)
AND RES.UTILIZATION_FLAG = 'Y'
AND RES.JOB_ID = JOB.JOB_ID
AND RES.JOB_ID = JOB_INFO.JOB_ID (+)
AND 'JOB CATEGORY' = JOB_INFO.JEI_INFORMATION_CATEGORY (+)
AND COMP_ELE.PROFICIENCY_LEVEL_ID = RATING_LVL.RATING_LEVEL_ID (+)
AND TRUNC(SYSDATE) BETWEEN TRUNC(COMP_ELE.EFFECTIVE_DATE_FROM)
AND NVL(TRUNC(COMP_ELE.EFFECTIVE_DATE_TO)
, SYSDATE+1)