View: PA_REP_ORG_COM_RES_LST_V

Product:	PA - Projects
Description:	PA_REP_ORG_COM_RES_LST_V is a view that shows resource listing for organization and competence
Implementation/DBA Data:	APPS.PA_REP_ORG_COM_RES_LST_V

View Text

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)

Columns