VIEW: APPS.OTFV_DELIVERED_COMPETENCES

Source

View Text - Preformatted

SELECT bgrT.name business_group_name ,comp.name competence_name ,DECODE(ele.competence_type,NULL,NULL, hr_bis.bis_decode_lookup('COMPETENCE_TYPE',ele.competence_type)) competence_type ,tdt.name activity_type ,tvt.version_name activity_name ,tvt.description description ,ral.step_value || DECODE(ral.name,'','',' - ' || ral.name) proficiency ,'_DF:PER:PER_COMPETENCE_ELEMENTS:comp' ,tav.activity_id activity_id ,tav.activity_version_id activity_version_id ,tad.business_group_id business_group_id ,comp.competence_id competence_id ,ele.competence_element_id competence_element_id ,tav.controlling_person_id controlling_person_id ,ele.proficiency_level_id proficiency_level_id ,tav.rco_id rco_id FROM fnd_languages_vl lng ,ota_activity_definitions tad ,ota_activity_definitions_tl tdt ,per_rating_levels ral ,po_vendors ven ,hr_all_organization_units bgr ,hr_all_organization_units_tl bgrT ,per_competence_elements ele ,ota_activity_versions tav ,ota_activity_versions_tl tvt ,per_competences comp WHERE comp.competence_id = ele.competence_id AND ele.type = 'DELIVERY' AND ele.proficiency_level_id = ral.rating_level_id(+) AND tav.activity_version_id = ele.activity_version_id AND tav.activity_version_id = tvt.activity_version_id AND tvt.language = userenv('LANG') AND tav.language_id = lng.language_id(+) AND tav.activity_id = tad.activity_id AND tad.activity_id = tdt.activity_id AND tdt.language = userenv('LANG') AND tav.vendor_id = ven.vendor_id(+) AND tad.business_group_id = bgr.organization_id AND bgr.organization_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND tad.business_group_id = NVL(ota_general.get_business_group_id,tad.business_group_id) WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, COMP.NAME COMPETENCE_NAME
, DECODE(ELE.COMPETENCE_TYPE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('COMPETENCE_TYPE'
, ELE.COMPETENCE_TYPE)) COMPETENCE_TYPE
, TDT.NAME ACTIVITY_TYPE
, TVT.VERSION_NAME ACTIVITY_NAME
, TVT.DESCRIPTION DESCRIPTION
, RAL.STEP_VALUE || DECODE(RAL.NAME
, ''
, ''
, ' - ' || RAL.NAME) PROFICIENCY
, '_DF:PER:PER_COMPETENCE_ELEMENTS:COMP'
, TAV.ACTIVITY_ID ACTIVITY_ID
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TAD.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, COMP.COMPETENCE_ID COMPETENCE_ID
, ELE.COMPETENCE_ELEMENT_ID COMPETENCE_ELEMENT_ID
, TAV.CONTROLLING_PERSON_ID CONTROLLING_PERSON_ID
, ELE.PROFICIENCY_LEVEL_ID PROFICIENCY_LEVEL_ID
, TAV.RCO_ID RCO_ID
FROM FND_LANGUAGES_VL LNG
, OTA_ACTIVITY_DEFINITIONS TAD
, OTA_ACTIVITY_DEFINITIONS_TL TDT
, PER_RATING_LEVELS RAL
, PO_VENDORS VEN
, HR_ALL_ORGANIZATION_UNITS BGR
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_COMPETENCE_ELEMENTS ELE
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_VERSIONS_TL TVT
, PER_COMPETENCES COMP
WHERE COMP.COMPETENCE_ID = ELE.COMPETENCE_ID
AND ELE.TYPE = 'DELIVERY'
AND ELE.PROFICIENCY_LEVEL_ID = RAL.RATING_LEVEL_ID(+)
AND TAV.ACTIVITY_VERSION_ID = ELE.ACTIVITY_VERSION_ID
AND TAV.ACTIVITY_VERSION_ID = TVT.ACTIVITY_VERSION_ID
AND TVT.LANGUAGE = USERENV('LANG')
AND TAV.LANGUAGE_ID = LNG.LANGUAGE_ID(+)
AND TAV.ACTIVITY_ID = TAD.ACTIVITY_ID
AND TAD.ACTIVITY_ID = TDT.ACTIVITY_ID
AND TDT.LANGUAGE = USERENV('LANG')
AND TAV.VENDOR_ID = VEN.VENDOR_ID(+)
AND TAD.BUSINESS_GROUP_ID = BGR.ORGANIZATION_ID
AND BGR.ORGANIZATION_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND TAD.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, TAD.BUSINESS_GROUP_ID) WITH READ ONLY