VIEW: APPS.PER_COMPETENCE_ELEMENTS_V7

Source

View Text - Preformatted

SELECT CEL1.ASSESSMENT_TYPE_ID , CEL1.GROUP_COMPETENCE_TYPE , CEL2.COMPETENCE_ID , CPL.NAME , COMP.DATE_FROM , COMP.DATE_TO , CEL2.SEQUENCE_NUMBER , 'Y' , CEL2.COMPETENCE_ELEMENT_ID , CEL2.BUSINESS_GROUP_ID , CEL2.OBJECT_VERSION_NUMBER , CEL2.ATTRIBUTE_CATEGORY , CEL2.ATTRIBUTE1 , CEL2.ATTRIBUTE2 , CEL2.ATTRIBUTE3 , CEL2.ATTRIBUTE4 , CEL2.ATTRIBUTE5 , CEL2.ATTRIBUTE6 , CEL2.ATTRIBUTE7 , CEL2.ATTRIBUTE8 , CEL2.ATTRIBUTE9 , CEL2.ATTRIBUTE10 , CEL2.ATTRIBUTE11 , CEL2.ATTRIBUTE12 , CEL2.ATTRIBUTE13 , CEL2.ATTRIBUTE14 , CEL2.ATTRIBUTE15 , CEL2.ATTRIBUTE16 , CEL2.ATTRIBUTE17 , CEL2.ATTRIBUTE18 , CEL2.ATTRIBUTE19 , CEL2.ATTRIBUTE20 , CEL2.LAST_UPDATE_DATE , CEL2.LAST_UPDATED_BY , CEL2.LAST_UPDATE_LOGIN , CEL2.CREATED_BY , CEL2.CREATION_DATE , CEL2.PARTY_ID , CEL2.QUALIFICATION_TYPE_ID , CEL2.UNIT_STANDARD_TYPE , CEL2.STATUS , CEL2.ACHIEVED_DATE , CEL2.INFORMATION_CATEGORY , CEL2.INFORMATION1 , CEL2.INFORMATION2 , CEL2.INFORMATION3 , CEL2.INFORMATION4 , CEL2.INFORMATION5 , CEL2.INFORMATION6 , CEL2.INFORMATION7 , CEL2.INFORMATION8 , CEL2.INFORMATION9 , CEL2.INFORMATION10 , CEL2.INFORMATION11 , CEL2.INFORMATION12 , CEL2.INFORMATION13 , CEL2.INFORMATION14 , CEL2.INFORMATION15 , CEL2.INFORMATION16 , CEL2.INFORMATION17 , CEL2.INFORMATION18 , CEL2.INFORMATION19 , CEL2.INFORMATION20 FROM PER_COMPETENCE_ELEMENTS CEL1, PER_COMPETENCE_ELEMENTS CEL2, PER_COMPETENCES COMP, PER_COMPETENCES_TL CPL WHERE CEL1.TYPE = 'ASSESSMENT_GROUP' AND CEL2.TYPE = 'ASSESSMENT_COMPETENCE' AND CEL2.COMPETENCE_ID = COMP.COMPETENCE_ID AND CPL.COMPETENCE_ID = COMP.COMPETENCE_ID AND CPL.LANGUAGE = userenv('LANG') AND (CEL2.BUSINESS_GROUP_ID = COMP.BUSINESS_GROUP_ID OR COMP.BUSINESS_GROUP_ID IS NULL) AND CEL2.PARENT_COMPETENCE_ELEMENT_ID = CEL1.COMPETENCE_ELEMENT_ID UNION SELECT TO_NUMBER(NULL) ,CEL_V2.COMPETENCE_TYPE ,COMP_V2.COMPETENCE_ID ,CPL_V2.NAME ,COMP_V2.DATE_FROM ,COMP_V2.DATE_TO ,TO_NUMBER(NULL) ,'N' ,TO_NUMBER(NULL) ,CEL_V2.BUSINESS_GROUP_ID ,TO_NUMBER(NULL) ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,TO_DATE(NULL) ,TO_NUMBER(NULL) ,TO_NUMBER(NULL) ,TO_NUMBER(NULL) ,TO_DATE(NULL) ,TO_NUMBER(NULL) ,CEL_V3.QUALIFICATION_TYPE_ID ,NULL ,NULL ,TO_DATE(NULL) ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL FROM PER_COMPETENCE_ELEMENTS CEL_V2 ,PER_COMPETENCES COMP_V2 ,PER_COMPETENCES_TL CPL_V2 ,PER_COMPETENCE_ELEMENTS CEL_V3 WHERE CEL_V2.TYPE = 'COMPETENCE_USAGE' AND CEL_V2.COMPETENCE_ID = COMP_V2.COMPETENCE_ID AND CPL_V2.COMPETENCE_ID = COMP_V2.COMPETENCE_ID AND CPL_V2.LANGUAGE = userenv('LANG') AND (CEL_V2.BUSINESS_GROUP_ID = COMP_V2.BUSINESS_GROUP_ID OR COMP_V2.BUSINESS_GROUP_ID IS NULL) AND CEL_V2.COMPETENCE_ID = CEL_V3.COMPETENCE_ID(+) AND CEL_V3.TYPE(+) = 'QUALIFICATION' UNION SELECT TO_NUMBER(NULL) ,'ALL' ,COMP_V3.COMPETENCE_ID ,CPL_V3.NAME ,COMP_V3.DATE_FROM ,COMP_V3.DATE_TO ,TO_NUMBER(NULL) ,'N' ,TO_NUMBER(NULL) ,COMP_V3.BUSINESS_GROUP_ID ,TO_NUMBER(NULL) ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,TO_DATE(NULL) ,TO_NUMBER(NULL) ,TO_NUMBER(NULL) ,TO_NUMBER(NULL) ,TO_DATE(NULL) ,TO_NUMBER(NULL) , CEL_V3.QUALIFICATION_TYPE_ID ,NULL ,NULL ,TO_DATE(NULL) ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL FROM PER_COMPETENCES COMP_V3, PER_COMPETENCES_TL CPL_V3, PER_COMPETENCE_ELEMENTS CEL_V3 WHERE COMP_V3.COMPETENCE_ID NOT IN (SELECT DISTINCT COMPETENCE_ID FROM PER_COMPETENCE_ELEMENTS COMP_VI WHERE TYPE = 'COMPETENCE_USAGE' AND (COMP_VI.BUSINESS_GROUP_ID = COMP_V3.BUSINESS_GROUP_ID OR COMP_V3.BUSINESS_GROUP_ID IS NULL)) AND CPL_V3.COMPETENCE_ID = COMP_V3.COMPETENCE_ID AND CPL_V3.LANGUAGE = userenv('LANG') AND COMP_V3.COMPETENCE_ID = CEL_V3.COMPETENCE_ID(+) AND CEL_V3.TYPE(+) = 'QUALIFICATION'

View Text - HTML Formatted

SELECT CEL1.ASSESSMENT_TYPE_ID
, CEL1.GROUP_COMPETENCE_TYPE
, CEL2.COMPETENCE_ID
, CPL.NAME
, COMP.DATE_FROM
, COMP.DATE_TO
, CEL2.SEQUENCE_NUMBER
, 'Y'
, CEL2.COMPETENCE_ELEMENT_ID
, CEL2.BUSINESS_GROUP_ID
, CEL2.OBJECT_VERSION_NUMBER
, CEL2.ATTRIBUTE_CATEGORY
, CEL2.ATTRIBUTE1
, CEL2.ATTRIBUTE2
, CEL2.ATTRIBUTE3
, CEL2.ATTRIBUTE4
, CEL2.ATTRIBUTE5
, CEL2.ATTRIBUTE6
, CEL2.ATTRIBUTE7
, CEL2.ATTRIBUTE8
, CEL2.ATTRIBUTE9
, CEL2.ATTRIBUTE10
, CEL2.ATTRIBUTE11
, CEL2.ATTRIBUTE12
, CEL2.ATTRIBUTE13
, CEL2.ATTRIBUTE14
, CEL2.ATTRIBUTE15
, CEL2.ATTRIBUTE16
, CEL2.ATTRIBUTE17
, CEL2.ATTRIBUTE18
, CEL2.ATTRIBUTE19
, CEL2.ATTRIBUTE20
, CEL2.LAST_UPDATE_DATE
, CEL2.LAST_UPDATED_BY
, CEL2.LAST_UPDATE_LOGIN
, CEL2.CREATED_BY
, CEL2.CREATION_DATE
, CEL2.PARTY_ID
, CEL2.QUALIFICATION_TYPE_ID
, CEL2.UNIT_STANDARD_TYPE
, CEL2.STATUS
, CEL2.ACHIEVED_DATE
, CEL2.INFORMATION_CATEGORY
, CEL2.INFORMATION1
, CEL2.INFORMATION2
, CEL2.INFORMATION3
, CEL2.INFORMATION4
, CEL2.INFORMATION5
, CEL2.INFORMATION6
, CEL2.INFORMATION7
, CEL2.INFORMATION8
, CEL2.INFORMATION9
, CEL2.INFORMATION10
, CEL2.INFORMATION11
, CEL2.INFORMATION12
, CEL2.INFORMATION13
, CEL2.INFORMATION14
, CEL2.INFORMATION15
, CEL2.INFORMATION16
, CEL2.INFORMATION17
, CEL2.INFORMATION18
, CEL2.INFORMATION19
, CEL2.INFORMATION20
FROM PER_COMPETENCE_ELEMENTS CEL1
, PER_COMPETENCE_ELEMENTS CEL2
, PER_COMPETENCES COMP
, PER_COMPETENCES_TL CPL
WHERE CEL1.TYPE = 'ASSESSMENT_GROUP'
AND CEL2.TYPE = 'ASSESSMENT_COMPETENCE'
AND CEL2.COMPETENCE_ID = COMP.COMPETENCE_ID
AND CPL.COMPETENCE_ID = COMP.COMPETENCE_ID
AND CPL.LANGUAGE = USERENV('LANG')
AND (CEL2.BUSINESS_GROUP_ID = COMP.BUSINESS_GROUP_ID OR COMP.BUSINESS_GROUP_ID IS NULL)
AND CEL2.PARENT_COMPETENCE_ELEMENT_ID = CEL1.COMPETENCE_ELEMENT_ID UNION SELECT TO_NUMBER(NULL)
, CEL_V2.COMPETENCE_TYPE
, COMP_V2.COMPETENCE_ID
, CPL_V2.NAME
, COMP_V2.DATE_FROM
, COMP_V2.DATE_TO
, TO_NUMBER(NULL)
, 'N'
, TO_NUMBER(NULL)
, CEL_V2.BUSINESS_GROUP_ID
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, CEL_V3.QUALIFICATION_TYPE_ID
, NULL
, NULL
, TO_DATE(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM PER_COMPETENCE_ELEMENTS CEL_V2
, PER_COMPETENCES COMP_V2
, PER_COMPETENCES_TL CPL_V2
, PER_COMPETENCE_ELEMENTS CEL_V3
WHERE CEL_V2.TYPE = 'COMPETENCE_USAGE'
AND CEL_V2.COMPETENCE_ID = COMP_V2.COMPETENCE_ID
AND CPL_V2.COMPETENCE_ID = COMP_V2.COMPETENCE_ID
AND CPL_V2.LANGUAGE = USERENV('LANG')
AND (CEL_V2.BUSINESS_GROUP_ID = COMP_V2.BUSINESS_GROUP_ID OR COMP_V2.BUSINESS_GROUP_ID IS NULL)
AND CEL_V2.COMPETENCE_ID = CEL_V3.COMPETENCE_ID(+)
AND CEL_V3.TYPE(+) = 'QUALIFICATION' UNION SELECT TO_NUMBER(NULL)
, 'ALL'
, COMP_V3.COMPETENCE_ID
, CPL_V3.NAME
, COMP_V3.DATE_FROM
, COMP_V3.DATE_TO
, TO_NUMBER(NULL)
, 'N'
, TO_NUMBER(NULL)
, COMP_V3.BUSINESS_GROUP_ID
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, CEL_V3.QUALIFICATION_TYPE_ID
, NULL
, NULL
, TO_DATE(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM PER_COMPETENCES COMP_V3
, PER_COMPETENCES_TL CPL_V3
, PER_COMPETENCE_ELEMENTS CEL_V3
WHERE COMP_V3.COMPETENCE_ID NOT IN (SELECT DISTINCT COMPETENCE_ID
FROM PER_COMPETENCE_ELEMENTS COMP_VI
WHERE TYPE = 'COMPETENCE_USAGE'
AND (COMP_VI.BUSINESS_GROUP_ID = COMP_V3.BUSINESS_GROUP_ID OR COMP_V3.BUSINESS_GROUP_ID IS NULL))
AND CPL_V3.COMPETENCE_ID = COMP_V3.COMPETENCE_ID
AND CPL_V3.LANGUAGE = USERENV('LANG')
AND COMP_V3.COMPETENCE_ID = CEL_V3.COMPETENCE_ID(+)
AND CEL_V3.TYPE(+) = 'QUALIFICATION'