VIEW: APPS.OTFV_EVENT_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 ,ett.title event_name ,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 ,evt.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 ,evt.event_id event_id ,ele.proficiency_level_id proficiency_level_id ,tav.rco_id rco_id ,ofr.offering_id offering_id FROM ota_events evt ,ota_events_tl ett ,per_rating_levels ral ,hr_all_organization_units_tl bgrT ,per_competence_elements ele ,ota_activity_versions tav ,ota_activity_versions_tl tvt ,ota_activity_definitions tad ,ota_activity_definitions_tl tdt ,ota_offerings ofr ,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.activity_version_id = ofr.activity_version_id AND ofr.offering_id = evt.parent_offering_id AND evt.event_id = ett.event_id AND ett.language = userenv('LANG') AND tav.activity_id = tad.activity_id AND tad.activity_id = tdt.activity_id AND tdt.language = userenv('LANG') AND evt.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND evt.business_group_id = NVL(ota_general.get_business_group_id, evt.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
, ETT.TITLE EVENT_NAME
, 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
, EVT.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
, EVT.EVENT_ID EVENT_ID
, ELE.PROFICIENCY_LEVEL_ID PROFICIENCY_LEVEL_ID
, TAV.RCO_ID RCO_ID
, OFR.OFFERING_ID OFFERING_ID
FROM OTA_EVENTS EVT
, OTA_EVENTS_TL ETT
, PER_RATING_LEVELS RAL
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_COMPETENCE_ELEMENTS ELE
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_VERSIONS_TL TVT
, OTA_ACTIVITY_DEFINITIONS TAD
, OTA_ACTIVITY_DEFINITIONS_TL TDT
, OTA_OFFERINGS OFR
, 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.ACTIVITY_VERSION_ID = OFR.ACTIVITY_VERSION_ID
AND OFR.OFFERING_ID = EVT.PARENT_OFFERING_ID
AND EVT.EVENT_ID = ETT.EVENT_ID
AND ETT.LANGUAGE = USERENV('LANG')
AND TAV.ACTIVITY_ID = TAD.ACTIVITY_ID
AND TAD.ACTIVITY_ID = TDT.ACTIVITY_ID
AND TDT.LANGUAGE = USERENV('LANG')
AND EVT.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND EVT.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, EVT.BUSINESS_GROUP_ID) WITH READ ONLY