View: OTV_TRAINING_COMPETENCE

Product:	OTA - Learning Management
Description:	View to list student's competence for an Activity version.
Implementation/DBA Data:	APPS.OTV_TRAINING_COMPETENCE

View Text

SELECT COMP.NAME
, HR_GENERAL.DECODE_LOOKUP(ELE.COMPETENCE_TYPE
, 'COMPETENCE_TYPE')
, ELE.PROFICIENCY_LEVEL_ID
, RAL.STEP_VALUE || DECODE(RAL.NAME
, ''
, ''
, ' - ' || RAL.NAME)
, OAV.VERSION_NAME
, OAV.DESCRIPTION
, LNG.DESCRIPTION
, HR_GENERAL.DECODE_LOOKUP('ACTIVITY_USER_STATUS'
, OAV.USER_STATUS)
, PEO.FULL_NAME
, VEN.VENDOR_NAME
, OAV.CONTROLLING_PERSON_ID
, OAV.ACTIVITY_ID
, ELE.COMPETENCE_ELEMENT_ID
, COMP.COMPETENCE_ID
, OAV.ACTIVITY_VERSION_ID
, PEO.BUSINESS_GROUP_ID
, BGR.NAME
FROM PER_COMPETENCES COMP
, PER_COMPETENCE_ELEMENTS ELE
, OTA_ACTIVITY_VERSIONS OAV
, FND_LANGUAGES_VL LNG
, PER_PEOPLE_F PEO
, PER_RATING_LEVELS RAL
, PO_VENDORS VEN
, HR_ALL_ORGANIZATION_UNITS BGR
WHERE COMP.COMPETENCE_ID = ELE.COMPETENCE_ID
AND ELE.TYPE = 'DELIVERY'
AND OAV.ACTIVITY_VERSION_ID = ELE.ACTIVITY_VERSION_ID
AND OAV.LANGUAGE_ID = LNG.LANGUAGE_ID(+)
AND OAV.CONTROLLING_PERSON_ID = PEO.PERSON_ID(+)
AND ELE.PROFICIENCY_LEVEL_ID = RAL.RATING_LEVEL_ID(+)
AND TRUNC(SYSDATE) BETWEEN NVL(OAV.START_DATE
, TRUNC(SYSDATE))
AND NVL(OAV.END_DATE
, TRUNC(SYSDATE))
AND OAV.START_DATE BETWEEN PEO.EFFECTIVE_START_DATE(+)
AND PEO.EFFECTIVE_END_DATE(+)
AND ELE.BUSINESS_GROUP_ID = COMP.BUSINESS_GROUP_ID+0
AND OAV.VENDOR_ID = VEN.VENDOR_ID(+)
AND PEO.BUSINESS_GROUP_ID=BGR.ORGANIZATION_ID
AND PEO.BUSINESS_GROUP_ID=NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, PEO.BUSINESS_GROUP_ID)

Columns