View: PER_COMPETENCE_ELEMENTS_V

Product:	PER - Human Resources
Description:	Used to support user interface
Implementation/DBA Data:	APPS.PER_COMPETENCE_ELEMENTS_V

View Text

SELECT /* $HEADER: $ */ COMP.ROWID
, 1
, COMP.COMPETENCE_ELEMENT_ID
, COMP.BUSINESS_GROUP_ID
, COMP.ENTERPRISE_ID
, COMP.OBJECT_VERSION_NUMBER
, COMP.TYPE
, COMP.COMPETENCE_ID
, CPL.NAME
, COMP.PROFICIENCY_LEVEL_ID
, RATL1.STEP_VALUE || DECODE(RTL1.NAME
, ''
, ''
, ' - ' || RTL1.NAME)
, COMP.HIGH_PROFICIENCY_LEVEL_ID
, RATL2.STEP_VALUE || DECODE(RTL2.NAME
, ''
, ''
, ' - ' || RTL2.NAME)
, COMP.PERSON_ID
, PER.FULL_NAME
, COMP.JOB_ID
, JBT.NAME
, COMP.VALID_GRADE_ID
, GDT.NAME
, COMP.POSITION_ID
, HR_GENERAL.DECODE_POSITION_LATEST_NAME(COMP.POSITION_ID)
, COMP.ORGANIZATION_ID
, ORGTL.NAME
, COMP.ACTIVITY_VERSION_ID
, OTAV.VERSION_NAME
, COMP.MANDATORY
, COMP.EFFECTIVE_DATE_FROM
, COMP.EFFECTIVE_DATE_TO
, COMP.COMPETENCE_TYPE
, COMP.CERTIFICATION_DATE
, COMP.CERTIFICATION_METHOD
, HR_GENERAL.DECODE_LOOKUP('CERTIFICATION_METHOD'
, COMP.CERTIFICATION_METHOD)
, COMP.NEXT_CERTIFICATION_DATE
, COMP.SOURCE_OF_PROFICIENCY_LEVEL
, HR_GENERAL.DECODE_LOOKUP('PROFICIENCY_SOURCE'
, COMP.SOURCE_OF_PROFICIENCY_LEVEL)
, COMP.COMMENTS
, COMP.ATTRIBUTE_CATEGORY
, COMP.ATTRIBUTE1
, COMP.ATTRIBUTE2
, COMP.ATTRIBUTE3
, COMP.ATTRIBUTE4
, COMP.ATTRIBUTE5
, COMP.ATTRIBUTE6
, COMP.ATTRIBUTE7
, COMP.ATTRIBUTE8
, COMP.ATTRIBUTE9
, COMP.ATTRIBUTE10
, COMP.ATTRIBUTE11
, COMP.ATTRIBUTE12
, COMP.ATTRIBUTE13
, COMP.ATTRIBUTE14
, COMP.ATTRIBUTE15
, COMP.ATTRIBUTE16
, COMP.ATTRIBUTE17
, COMP.ATTRIBUTE18
, COMP.ATTRIBUTE19
, COMP.ATTRIBUTE20
, COMP.LAST_UPDATE_DATE
, COMP.LAST_UPDATED_BY
, COMP.LAST_UPDATE_LOGIN
, COMP.CREATED_BY
, COMP.CREATION_DATE
, COMP.OBJECT_ID
, COMP.OBJECT_NAME
, COMP.PARTY_ID
, DECODE(CPN.BUSINESS_GROUP_ID
, NULL
, 'Y'
, 'N')
FROM OTA_ACTIVITY_VERSIONS OTAV
, PER_RATING_LEVELS RATL1
, PER_RATING_LEVELS RATL2
, PER_RATING_LEVELS_TL RTL1
, PER_RATING_LEVELS_TL RTL2
, PER_VALID_GRADES VGRD
, PER_GRADES_TL GDT
, PER_JOBS_TL JBT
, HR_ORG_UNITS_NO_JOIN ORGS
, HR_ALL_ORGANIZATION_UNITS_TL ORGTL
, PER_COMPETENCES CPN
, PER_COMPETENCES_TL CPL
, PER_PEOPLE_F PER
, PER_COMPETENCE_ELEMENTS COMP
, FND_SESSIONS FND
WHERE COMP.COMPETENCE_ID = CPN.COMPETENCE_ID
AND CPL.COMPETENCE_ID = CPN.COMPETENCE_ID
AND CPL.LANGUAGE = USERENV('LANG')
AND COMP.JOB_ID = JBT.JOB_ID(+)
AND JBT.LANGUAGE(+) = USERENV('LANG')
AND COMP.VALID_GRADE_ID = VGRD.VALID_GRADE_ID (+)
AND GDT.GRADE_ID(+) = VGRD.GRADE_ID
AND GDT.LANGUAGE(+) = USERENV('LANG')
AND COMP.ORGANIZATION_ID = ORGS.ORGANIZATION_ID (+)
AND COMP.PROFICIENCY_LEVEL_ID = RATL1.RATING_LEVEL_ID (+)
AND COMP.PROFICIENCY_LEVEL_ID = RTL1.RATING_LEVEL_ID (+)
AND RTL1.LANGUAGE(+) = USERENV('LANG')
AND COMP.HIGH_PROFICIENCY_LEVEL_ID = RATL2.RATING_LEVEL_ID (+)
AND COMP.HIGH_PROFICIENCY_LEVEL_ID = RTL2.RATING_LEVEL_ID (+)
AND RTL2.LANGUAGE(+) = USERENV('LANG')
AND COMP.PERSON_ID = PER.PERSON_ID (+)
AND COMP.ACTIVITY_VERSION_ID = OTAV.ACTIVITY_VERSION_ID (+)
AND FND.SESSION_ID = USERENV('SESSIONID')
AND FND.EFFECTIVE_DATE BETWEEN NVL(PER.EFFECTIVE_START_DATE
, FND.EFFECTIVE_DATE)
AND NVL(PER.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE)
AND ORGS.ORGANIZATION_ID = ORGTL.ORGANIZATION_ID (+)
AND DECODE(ORGTL.ORGANIZATION_ID
, NULL
, '1'
, ORGTL.LANGUAGE) = DECODE(ORGTL.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))

Columns