VIEW: APPS.OTV_TRAINING_COMPETENCE

Source

View Text - Preformatted

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)

View Text - HTML Formatted

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)