VIEW: APPS.PA_REP_RES_COMP_DTS_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'