View: PA_REP_ORG_COMP_SUM_V

Product:	PA - Projects
Description:	A view that shows organization competence summary
Implementation/DBA Data:	APPS.PA_REP_ORG_COMP_SUM_V

View Text

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

Columns