eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PA_REP_ORG_COMP_SUM_V

Source

View Text - Preformatted


SELECT org.organization_id ,org.name ,comp.competence_id ,comp.name ,comp.competence_alias ,comp.description ,count(comp_ele.person_id) ,bus.name ,DECODE(res.schedulable_flag,null,'N',res.schedulable_flag) schedulable_flag ,comp_def.segment1 ,comp_def.segment2 ,comp_def.segment3 ,comp_def.segment4 ,comp_def.segment5 ,comp_def.segment6 ,comp_def.segment7 ,comp_def.segment8 ,comp_def.segment9 ,comp_def.segment10 ,comp_def.segment11 ,comp_def.segment12 ,comp_def.segment13 ,comp_def.segment14 ,comp_def.segment15 ,comp_def.segment16 ,comp_def.segment17 ,comp_def.segment18 ,comp_def.segment19 ,comp_def.segment20 ,comp_def.segment21 ,comp_def.segment22 ,comp_def.segment23 ,comp_def.segment24 ,comp_def.segment25 ,comp_def.segment26 ,comp_def.segment27 ,comp_def.segment28 ,comp_def.segment29 ,comp_def.segment30 ,res.resource_person_type FROM per_competences comp ,per_competence_elements comp_ele ,pa_resources_denorm res ,hr_organization_units org ,per_business_groups bus ,per_competence_definitions comp_def WHERE comp.competence_id = comp_ele.competence_id AND comp_ele.person_id = res.person_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.resource_organization_id = org.organization_id AND comp.business_group_id = bus.business_group_id (+) AND comp.competence_definition_id = comp_def.competence_definition_id (+) group by org.organization_id ,org.name ,comp.competence_id ,comp.name ,comp.competence_alias ,comp.description ,bus.name ,schedulable_flag ,comp_def.segment1 ,comp_def.segment2 ,comp_def.segment3 ,comp_def.segment4 ,comp_def.segment5 ,comp_def.segment6 ,comp_def.segment7 ,comp_def.segment8 ,comp_def.segment9 ,comp_def.segment10 ,comp_def.segment11 ,comp_def.segment12 ,comp_def.segment13 ,comp_def.segment14 ,comp_def.segment15 ,comp_def.segment16 ,comp_def.segment17 ,comp_def.segment18 ,comp_def.segment19 ,comp_def.segment20 ,comp_def.segment21 ,comp_def.segment22 ,comp_def.segment23 ,comp_def.segment24 ,comp_def.segment25 ,comp_def.segment26 ,comp_def.segment27 ,comp_def.segment28 ,comp_def.segment29 ,comp_def.segment30 ,res.resource_person_type

View Text - HTML Formatted

SELECT ORG.ORGANIZATION_ID
, ORG.NAME
, COMP.COMPETENCE_ID
, COMP.NAME
, COMP.COMPETENCE_ALIAS
, COMP.DESCRIPTION
, COUNT(COMP_ELE.PERSON_ID)
, BUS.NAME
, DECODE(RES.SCHEDULABLE_FLAG
, NULL
, 'N'
, RES.SCHEDULABLE_FLAG) SCHEDULABLE_FLAG
, COMP_DEF.SEGMENT1
, COMP_DEF.SEGMENT2
, COMP_DEF.SEGMENT3
, COMP_DEF.SEGMENT4
, COMP_DEF.SEGMENT5
, COMP_DEF.SEGMENT6
, COMP_DEF.SEGMENT7
, COMP_DEF.SEGMENT8
, COMP_DEF.SEGMENT9
, COMP_DEF.SEGMENT10
, COMP_DEF.SEGMENT11
, COMP_DEF.SEGMENT12
, COMP_DEF.SEGMENT13
, COMP_DEF.SEGMENT14
, COMP_DEF.SEGMENT15
, COMP_DEF.SEGMENT16
, COMP_DEF.SEGMENT17
, COMP_DEF.SEGMENT18
, COMP_DEF.SEGMENT19
, COMP_DEF.SEGMENT20
, COMP_DEF.SEGMENT21
, COMP_DEF.SEGMENT22
, COMP_DEF.SEGMENT23
, COMP_DEF.SEGMENT24
, COMP_DEF.SEGMENT25
, COMP_DEF.SEGMENT26
, COMP_DEF.SEGMENT27
, COMP_DEF.SEGMENT28
, COMP_DEF.SEGMENT29
, COMP_DEF.SEGMENT30
, RES.RESOURCE_PERSON_TYPE
FROM PER_COMPETENCES COMP
, PER_COMPETENCE_ELEMENTS COMP_ELE
, PA_RESOURCES_DENORM RES
, HR_ORGANIZATION_UNITS ORG
, PER_BUSINESS_GROUPS BUS
, PER_COMPETENCE_DEFINITIONS COMP_DEF
WHERE COMP.COMPETENCE_ID = COMP_ELE.COMPETENCE_ID
AND COMP_ELE.PERSON_ID = RES.PERSON_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.RESOURCE_ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND COMP.BUSINESS_GROUP_ID = BUS.BUSINESS_GROUP_ID (+)
AND COMP.COMPETENCE_DEFINITION_ID = COMP_DEF.COMPETENCE_DEFINITION_ID (+) GROUP BY ORG.ORGANIZATION_ID
, ORG.NAME
, COMP.COMPETENCE_ID
, COMP.NAME
, COMP.COMPETENCE_ALIAS
, COMP.DESCRIPTION
, BUS.NAME
, SCHEDULABLE_FLAG
, COMP_DEF.SEGMENT1
, COMP_DEF.SEGMENT2
, COMP_DEF.SEGMENT3
, COMP_DEF.SEGMENT4
, COMP_DEF.SEGMENT5
, COMP_DEF.SEGMENT6
, COMP_DEF.SEGMENT7
, COMP_DEF.SEGMENT8
, COMP_DEF.SEGMENT9
, COMP_DEF.SEGMENT10
, COMP_DEF.SEGMENT11
, COMP_DEF.SEGMENT12
, COMP_DEF.SEGMENT13
, COMP_DEF.SEGMENT14
, COMP_DEF.SEGMENT15
, COMP_DEF.SEGMENT16
, COMP_DEF.SEGMENT17
, COMP_DEF.SEGMENT18
, COMP_DEF.SEGMENT19
, COMP_DEF.SEGMENT20
, COMP_DEF.SEGMENT21
, COMP_DEF.SEGMENT22
, COMP_DEF.SEGMENT23
, COMP_DEF.SEGMENT24
, COMP_DEF.SEGMENT25
, COMP_DEF.SEGMENT26
, COMP_DEF.SEGMENT27
, COMP_DEF.SEGMENT28
, COMP_DEF.SEGMENT29
, COMP_DEF.SEGMENT30
, RES.RESOURCE_PERSON_TYPE