Product: | PA - Projects |
---|---|
Description: | PA_REP_RES_COMP_DTS_V is a view that lists all resources and their organization, competencies details and job details |
Implementation/DBA Data: | APPS.PA_REP_RES_COMP_DTS_V |
SELECT RES.PERSON_ID
, RES.RESOURCE_ID
, RES.RESOURCE_NAME
, COMP.COMPETENCE_ID
, COMP.NAME
, COMP.COMPETENCE_ALIAS
, B_GROUP.NAME
, RL.STEP_VALUE
, COMP_ELE.LAST_UPDATE_DATE
, DECODE(RES.SCHEDULABLE_FLAG
, NULL
, 'N'
, RES.SCHEDULABLE_FLAG)
, DEF.SEGMENT1
, DEF.SEGMENT2
, DEF.SEGMENT3
, DEF.SEGMENT4
, DEF.SEGMENT5
, DEF.SEGMENT6
, DEF.SEGMENT7
, DEF.SEGMENT8
, DEF.SEGMENT9
, DEF.SEGMENT10
, DEF.SEGMENT11
, DEF.SEGMENT12
, DEF.SEGMENT13
, DEF.SEGMENT14
, DEF.SEGMENT15
, DEF.SEGMENT16
, DEF.SEGMENT17
, DEF.SEGMENT18
, DEF.SEGMENT19
, DEF.SEGMENT20
, DEF.SEGMENT21
, DEF.SEGMENT22
, DEF.SEGMENT23
, DEF.SEGMENT24
, DEF.SEGMENT25
, DEF.SEGMENT26
, DEF.SEGMENT27
, DEF.SEGMENT28
, DEF.SEGMENT29
, DEF.SEGMENT30
, RES.RESOURCE_PERSON_TYPE
FROM PA_RESOURCES_DENORM RES
, PER_COMPETENCE_ELEMENTS COMP_ELE
, PER_BUSINESS_GROUPS B_GROUP
, PER_COMPETENCES COMP
, PER_RATING_LEVELS RL
, PER_COMPETENCE_DEFINITIONS DEF
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.PERSON_ID = COMP_ELE.PERSON_ID
AND COMP_ELE.COMPETENCE_ID = COMP.COMPETENCE_ID
AND COMP_ELE.BUSINESS_GROUP_ID = B_GROUP.BUSINESS_GROUP_ID (+)
AND COMP_ELE.PROFICIENCY_LEVEL_ID = RL.RATING_LEVEL_ID (+)
AND COMP.COMPETENCE_DEFINITION_ID = DEF.COMPETENCE_DEFINITION_ID (+)
AND RES.PERSON_ID = (SELECT PER.PERSON_ID
FROM PER_PEOPLE_F PER
WHERE RES.PERSON_ID = PER.PERSON_ID
AND ROWNUM = 1)
AND RES.UTILIZATION_FLAG = 'Y'