VIEW: APPS.PER_COMPETENCE_ELEMENTS_V

Source

View Text - Preformatted

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') , COMP.QUALIFICATION_TYPE_ID,COMP.UNIT_STANDARD_TYPE ,COMP.STATUS,HR_GENERAL.DECODE_LOOKUP('PER_QUAL_FWK_COMP_STATUS',COMP.STATUS) ,COMP.ACHIEVED_DATE ,COMP.INFORMATION_CATEGORY,COMP.INFORMATION1 ,COMP.INFORMATION2,COMP.INFORMATION3,COMP.INFORMATION4 ,COMP.INFORMATION5,COMP.INFORMATION6,COMP.INFORMATION7 ,COMP.INFORMATION8,COMP.INFORMATION9,COMP.INFORMATION10 ,COMP.INFORMATION11,COMP.INFORMATION12,COMP.INFORMATION13 ,COMP.INFORMATION14,COMP.INFORMATION15,COMP.INFORMATION16 ,COMP.INFORMATION17,COMP.INFORMATION18,COMP.INFORMATION19 ,COMP.INFORMATION20 ,CPN.UNIT_STANDARD_ID,CPN.CREDIT_TYPE ,HR_GENERAL.DECODE_LOOKUP('PER_QUAL_FWK_CREDIT_TYPE',CPN.CREDIT_TYPE) ,CPN.CREDITS ,CPN.LEVEL_TYPE ,HR_GENERAL.DECODE_LOOKUP('PER_QUAL_FWK_LEVEL_TYPE',CPN.LEVEL_TYPE) ,CPN.LEVEL_NUMBER ,HR_GENERAL.DECODE_LOOKUP('PER_QUAL_FWK_LEVEL',CPN.LEVEL_NUMBER) ,CPN.FIELD ,HR_GENERAL.DECODE_LOOKUP('PER_QUAL_FWK_FIELD',CPN.FIELD) ,CPN.SUB_FIELD ,HR_GENERAL.DECODE_LOOKUP('PER_QUAL_FWK_SUB_FIELD',CPN.SUB_FIELD) ,CPN.COMPETENCE_CLUSTER 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'))

View Text - HTML Formatted

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')
, COMP.QUALIFICATION_TYPE_ID
, COMP.UNIT_STANDARD_TYPE
, COMP.STATUS
, HR_GENERAL.DECODE_LOOKUP('PER_QUAL_FWK_COMP_STATUS'
, COMP.STATUS)
, COMP.ACHIEVED_DATE
, COMP.INFORMATION_CATEGORY
, COMP.INFORMATION1
, COMP.INFORMATION2
, COMP.INFORMATION3
, COMP.INFORMATION4
, COMP.INFORMATION5
, COMP.INFORMATION6
, COMP.INFORMATION7
, COMP.INFORMATION8
, COMP.INFORMATION9
, COMP.INFORMATION10
, COMP.INFORMATION11
, COMP.INFORMATION12
, COMP.INFORMATION13
, COMP.INFORMATION14
, COMP.INFORMATION15
, COMP.INFORMATION16
, COMP.INFORMATION17
, COMP.INFORMATION18
, COMP.INFORMATION19
, COMP.INFORMATION20
, CPN.UNIT_STANDARD_ID
, CPN.CREDIT_TYPE
, HR_GENERAL.DECODE_LOOKUP('PER_QUAL_FWK_CREDIT_TYPE'
, CPN.CREDIT_TYPE)
, CPN.CREDITS
, CPN.LEVEL_TYPE
, HR_GENERAL.DECODE_LOOKUP('PER_QUAL_FWK_LEVEL_TYPE'
, CPN.LEVEL_TYPE)
, CPN.LEVEL_NUMBER
, HR_GENERAL.DECODE_LOOKUP('PER_QUAL_FWK_LEVEL'
, CPN.LEVEL_NUMBER)
, CPN.FIELD
, HR_GENERAL.DECODE_LOOKUP('PER_QUAL_FWK_FIELD'
, CPN.FIELD)
, CPN.SUB_FIELD
, HR_GENERAL.DECODE_LOOKUP('PER_QUAL_FWK_SUB_FIELD'
, CPN.SUB_FIELD)
, CPN.COMPETENCE_CLUSTER
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'))