VIEW: APPS.OTFV_TRAINING_EVENT_CATEGORIES

Source

View Text - Preformatted

SELECT bgrT.name business_group_name ,evt.title event_name ,tct.category category_name ,aci.comments inclusion_comments ,aci.start_date_active start_date ,aci.end_date_active end_date ,DECODE(aci.primary_flag,NULL,NULL, hr_bis.bis_decode_lookup('YES_NO',aci.primary_flag)) primary_flag ,DECODE(tcu.type,NULL,NULL, hr_bis.bis_decode_lookup('CATEGORY_TYPE',tcu.type)) category_type ,tad.name activity_type ,tav.version_name activity_name ,tav.description activity_description , '_DF:OTA:OTA_ACT_CAT_INCLUSIONS:aci' ,evt.business_group_id business_group_id ,tcu.category_usage_id category_usage_id ,tav.activity_id activity_id ,tav.activity_version_id activity_version_id ,evt.event_id event_id ,tav.rco_id rco_id ,ofr.offering_id offering_id FROM hr_all_organization_units_tl bgrT ,ota_act_cat_inclusions aci ,ota_category_usages tcu ,ota_category_usages_tl tct ,ota_events evt ,ota_events_tl ett ,ota_activity_versions tav ,ota_activity_versions_tl tvt ,ota_activity_definitions tad ,ota_activity_definitions_tl tdt ,ota_offerings ofr WHERE evt.event_type = 'SCHEDULED' AND tav.activity_version_id = ofr.activity_version_id AND tav.activity_version_id = tvt.activity_version_id AND tvt.language = USERENV('LANG') AND ofr.offering_id = evt.parent_offering_id AND evt.event_id = ett.event_id AND ett.language = USERENV('LANG') AND tav.activity_version_id = aci.activity_version_id 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 aci.category_usage_id = tcu.category_usage_id(+) AND aci.category_usage_id = tct.category_usage_id (+) AND tct.language (+) = USERENV('LANG') 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
, EVT.TITLE EVENT_NAME
, TCT.CATEGORY CATEGORY_NAME
, ACI.COMMENTS INCLUSION_COMMENTS
, ACI.START_DATE_ACTIVE START_DATE
, ACI.END_DATE_ACTIVE END_DATE
, DECODE(ACI.PRIMARY_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, ACI.PRIMARY_FLAG)) PRIMARY_FLAG
, DECODE(TCU.TYPE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('CATEGORY_TYPE'
, TCU.TYPE)) CATEGORY_TYPE
, TAD.NAME ACTIVITY_TYPE
, TAV.VERSION_NAME ACTIVITY_NAME
, TAV.DESCRIPTION ACTIVITY_DESCRIPTION
, '_DF:OTA:OTA_ACT_CAT_INCLUSIONS:ACI'
, EVT.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TCU.CATEGORY_USAGE_ID CATEGORY_USAGE_ID
, TAV.ACTIVITY_ID ACTIVITY_ID
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, EVT.EVENT_ID EVENT_ID
, TAV.RCO_ID RCO_ID
, OFR.OFFERING_ID OFFERING_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL BGRT
, OTA_ACT_CAT_INCLUSIONS ACI
, OTA_CATEGORY_USAGES TCU
, OTA_CATEGORY_USAGES_TL TCT
, OTA_EVENTS EVT
, OTA_EVENTS_TL ETT
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_VERSIONS_TL TVT
, OTA_ACTIVITY_DEFINITIONS TAD
, OTA_ACTIVITY_DEFINITIONS_TL TDT
, OTA_OFFERINGS OFR
WHERE EVT.EVENT_TYPE = 'SCHEDULED'
AND TAV.ACTIVITY_VERSION_ID = OFR.ACTIVITY_VERSION_ID
AND TAV.ACTIVITY_VERSION_ID = TVT.ACTIVITY_VERSION_ID
AND TVT.LANGUAGE = USERENV('LANG')
AND OFR.OFFERING_ID = EVT.PARENT_OFFERING_ID
AND EVT.EVENT_ID = ETT.EVENT_ID
AND ETT.LANGUAGE = USERENV('LANG')
AND TAV.ACTIVITY_VERSION_ID = ACI.ACTIVITY_VERSION_ID
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 ACI.CATEGORY_USAGE_ID = TCU.CATEGORY_USAGE_ID(+)
AND ACI.CATEGORY_USAGE_ID = TCT.CATEGORY_USAGE_ID (+)
AND TCT.LANGUAGE (+) = USERENV('LANG')
AND BGRT.LANGUAGE = USERENV('LANG')
AND EVT.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, EVT.BUSINESS_GROUP_ID) WITH READ ONLY