VIEW: APPS.OTFV_HR_RESTRICTED_EVENTS

Source

View Text - Preformatted

SELECT bgrT.name business_group_name ,ett.title event_title ,evt.comments event_comments ,evt.course_start_date event_start_date ,evt.course_end_date event_end_date ,evt.course_start_time event_start_time ,evt.course_end_time event_end_time ,evt.duration event_duration ,DECODE(evt.duration_units,NULL,NULL, hr_bis.bis_decode_lookup('OTA_DURATION_UNITS',evt.duration_units)) event_duration_units ,DECODE(evt.secure_event_flag,NULL,NULL, hr_bis.bis_decode_lookup('YES_NO',evt.secure_event_flag)) secure_event ,hr_bis.bis_decode_lookup('YES_NO', substr(ota_evt_shd.resource_booking_flag(evt.event_id),1,1)) resource_bookings ,orgT1.name training_center ,ven.vendor_name supplier_name ,evt.enrolment_start_date enrollment_start_date ,evt.enrolment_end_date enrollment_end_date ,ota_utility.get_lang_name(ofv.language_code) LANGUAGE ,DECODE(evt.event_status,NULL,NULL, hr_bis.bis_decode_lookup('SCHEDULED_EVENT_STATUS',evt.event_status)) event_status ,DECODE(evt.user_status,NULL,NULL, hr_bis.bis_decode_lookup('EVENT_USER_STATUS',evt.user_status)) event_user_status ,evt.minimum_attendees minimum_attendees ,evt.maximum_attendees maximum_attendees ,evt.standard_price standard_price ,cur.name currency ,orgT.name organization_name ,job.name job_name ,pos.name position_name ,tea.comments event_association_comments ,evt.timezone time_zone ,'_DF:OTA:OTA_EVENTS:evt' ,'_DF:OTA:OTA_EVENT_ASSOCIATIONS:tea' ,evt.business_group_id business_group_id ,evt.event_id event_id ,tea.organization_id organization_id ,tea.job_id job_id ,tea.position_id position_id ,tea.event_association_id event_association_id ,evt.training_center_id training_center_id ,evt.parent_offering_id offering_id FROM hr_all_organization_units_tl bgrT ,ota_events evt ,ota_events_tl ett ,hr_all_organization_units_tl orgT ,per_all_positions pos ,per_jobs job ,ota_event_associations tea ,po_vendors ven ,fnd_currencies_vl cur ,ota_offerings_vl ofv ,hr_all_organization_units_tl orgT1 WHERE evt.public_event_flag = 'N' AND evt.event_id = ett.event_id AND ett.language = userenv('LANG') AND evt.training_center_id = orgT1.organization_id (+) AND DECODE(orgT1.language,NULL,'X',orgT1.language)= DECODE(orgT1.language,NULL,'X',userenv('LANG')) AND evt.event_id = tea.event_id AND evt.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND tea.organization_id = orgT.organization_id (+) AND tea.customer_id IS NULL AND decode(orgT.organization_id,null,'X',orgT.language) = decode(orgT.organization_id, null,'X', userenv('LANG')) AND tea.job_id = job.job_id (+) AND tea.position_id = pos.position_id (+) AND evt.vendor_id = ven.vendor_id (+) AND evt.currency_code = cur.currency_code (+) AND evt.parent_offering_id = ofv.offering_id(+) 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
, ETT.TITLE EVENT_TITLE
, EVT.COMMENTS EVENT_COMMENTS
, EVT.COURSE_START_DATE EVENT_START_DATE
, EVT.COURSE_END_DATE EVENT_END_DATE
, EVT.COURSE_START_TIME EVENT_START_TIME
, EVT.COURSE_END_TIME EVENT_END_TIME
, EVT.DURATION EVENT_DURATION
, DECODE(EVT.DURATION_UNITS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('OTA_DURATION_UNITS'
, EVT.DURATION_UNITS)) EVENT_DURATION_UNITS
, DECODE(EVT.SECURE_EVENT_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EVT.SECURE_EVENT_FLAG)) SECURE_EVENT
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, SUBSTR(OTA_EVT_SHD.RESOURCE_BOOKING_FLAG(EVT.EVENT_ID)
, 1
, 1)) RESOURCE_BOOKINGS
, ORGT1.NAME TRAINING_CENTER
, VEN.VENDOR_NAME SUPPLIER_NAME
, EVT.ENROLMENT_START_DATE ENROLLMENT_START_DATE
, EVT.ENROLMENT_END_DATE ENROLLMENT_END_DATE
, OTA_UTILITY.GET_LANG_NAME(OFV.LANGUAGE_CODE) LANGUAGE
, DECODE(EVT.EVENT_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('SCHEDULED_EVENT_STATUS'
, EVT.EVENT_STATUS)) EVENT_STATUS
, DECODE(EVT.USER_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('EVENT_USER_STATUS'
, EVT.USER_STATUS)) EVENT_USER_STATUS
, EVT.MINIMUM_ATTENDEES MINIMUM_ATTENDEES
, EVT.MAXIMUM_ATTENDEES MAXIMUM_ATTENDEES
, EVT.STANDARD_PRICE STANDARD_PRICE
, CUR.NAME CURRENCY
, ORGT.NAME ORGANIZATION_NAME
, JOB.NAME JOB_NAME
, POS.NAME POSITION_NAME
, TEA.COMMENTS EVENT_ASSOCIATION_COMMENTS
, EVT.TIMEZONE TIME_ZONE
, '_DF:OTA:OTA_EVENTS:EVT'
, '_DF:OTA:OTA_EVENT_ASSOCIATIONS:TEA'
, EVT.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, EVT.EVENT_ID EVENT_ID
, TEA.ORGANIZATION_ID ORGANIZATION_ID
, TEA.JOB_ID JOB_ID
, TEA.POSITION_ID POSITION_ID
, TEA.EVENT_ASSOCIATION_ID EVENT_ASSOCIATION_ID
, EVT.TRAINING_CENTER_ID TRAINING_CENTER_ID
, EVT.PARENT_OFFERING_ID OFFERING_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL BGRT
, OTA_EVENTS EVT
, OTA_EVENTS_TL ETT
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, PER_ALL_POSITIONS POS
, PER_JOBS JOB
, OTA_EVENT_ASSOCIATIONS TEA
, PO_VENDORS VEN
, FND_CURRENCIES_VL CUR
, OTA_OFFERINGS_VL OFV
, HR_ALL_ORGANIZATION_UNITS_TL ORGT1
WHERE EVT.PUBLIC_EVENT_FLAG = 'N'
AND EVT.EVENT_ID = ETT.EVENT_ID
AND ETT.LANGUAGE = USERENV('LANG')
AND EVT.TRAINING_CENTER_ID = ORGT1.ORGANIZATION_ID (+)
AND DECODE(ORGT1.LANGUAGE
, NULL
, 'X'
, ORGT1.LANGUAGE)= DECODE(ORGT1.LANGUAGE
, NULL
, 'X'
, USERENV('LANG'))
AND EVT.EVENT_ID = TEA.EVENT_ID
AND EVT.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND TEA.ORGANIZATION_ID = ORGT.ORGANIZATION_ID (+)
AND TEA.CUSTOMER_ID IS NULL
AND DECODE(ORGT.ORGANIZATION_ID
, NULL
, 'X'
, ORGT.LANGUAGE) = DECODE(ORGT.ORGANIZATION_ID
, NULL
, 'X'
, USERENV('LANG'))
AND TEA.JOB_ID = JOB.JOB_ID (+)
AND TEA.POSITION_ID = POS.POSITION_ID (+)
AND EVT.VENDOR_ID = VEN.VENDOR_ID (+)
AND EVT.CURRENCY_CODE = CUR.CURRENCY_CODE (+)
AND EVT.PARENT_OFFERING_ID = OFV.OFFERING_ID(+)
AND EVT.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, EVT.BUSINESS_GROUP_ID) WITH READ ONLY