View: PER_COMPETENCE_ELEMENTS_V3

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

View Text

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
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)
FROM PER_COMPETENCE_ELEMENTS CEL_V2
, PER_COMPETENCES COMP_V2
, PER_COMPETENCES_TL CPL_V2
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) 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)
FROM PER_COMPETENCES COMP_V3
, PER_COMPETENCES_TL CPL_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')

Columns