VIEW: APPS.OTFV_TRAINING_AMOUNT

Source

View Text - Preformatted

SELECT bgrT.name business_group_name ,tdt.name activity_type ,tvt.version_name activity_name ,ett.title event_title ,hr_bis.bis_decode_lookup('SCHEDULED_EVENT_STATUS',evt.event_status) event_status ,DECODE(bst.type,'A','Attended' ,'P','Placed' ,'W','Waitlisted' ,'C','Cancelled' ,'R','Restricted') booking_status ,oeh.flow_status_code order_header_status ,oel.flow_status_code order_line_status ,number_of_places places_booked ,NVL(tfl.money_amount,0) ota_amount ,tfl.currency_code ota_amount_currency ,NVL(oel.ordered_quantity * oel.unit_selling_price,0) oe_amount ,oeh.transactional_curr_code oe_amount_currency ,DECODE(tdb.internal_booking_flag,'Y','Yes','No') internal_booking ,DECODE(tdb.internal_booking_flag,'N','Yes','No') external_booking ,evt.event_id event_id ,tav.activity_version_id activity_version_id ,tad.activity_id activity_id ,tdb.booking_id enrollment_id ,tdb.business_group_id business_group_id ,tav.rco_id rco_id ,ofr.offering_id offering_id FROM ota_delegate_bookings tdb ,ota_booking_status_types bst ,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_finance_lines tfl ,oe_order_lines_all oel ,oe_order_headers_all oeh ,hr_all_organization_units_tl bgrT ,ota_offerings ofr WHERE tdb.event_id = evt.event_id AND evt.event_id = ett.event_id AND ett.language = userenv('LANG') AND bst.booking_status_type_id = tdb.booking_status_type_id AND bgrT.language = userenv('LANG') AND tdb.business_group_id = bgrT.organization_id AND evt.parent_offering_id = ofr.offering_id (+) AND ofr.activity_version_id = tav.activity_version_id (+) AND tav.activity_version_id = tvt.activity_version_id (+) AND tvt.language (+) = userenv('LANG') AND tav.activity_id = tad.activity_id (+) AND tad.activity_id = tdt.activity_id (+) AND tdt.language (+) = userenv('LANG') AND tdb.booking_id = tfl.booking_id (+) AND evt.line_id = oel.line_id (+) AND oel.header_id = oeh.header_id (+) AND tdb.business_group_id = NVL(ota_general.get_business_group_id,tdb.business_group_id) WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, TDT.NAME ACTIVITY_TYPE
, TVT.VERSION_NAME ACTIVITY_NAME
, ETT.TITLE EVENT_TITLE
, HR_BIS.BIS_DECODE_LOOKUP('SCHEDULED_EVENT_STATUS'
, EVT.EVENT_STATUS) EVENT_STATUS
, DECODE(BST.TYPE
, 'A'
, 'ATTENDED'
, 'P'
, 'PLACED'
, 'W'
, 'WAITLISTED'
, 'C'
, 'CANCELLED'
, 'R'
, 'RESTRICTED') BOOKING_STATUS
, OEH.FLOW_STATUS_CODE ORDER_HEADER_STATUS
, OEL.FLOW_STATUS_CODE ORDER_LINE_STATUS
, NUMBER_OF_PLACES PLACES_BOOKED
, NVL(TFL.MONEY_AMOUNT
, 0) OTA_AMOUNT
, TFL.CURRENCY_CODE OTA_AMOUNT_CURRENCY
, NVL(OEL.ORDERED_QUANTITY * OEL.UNIT_SELLING_PRICE
, 0) OE_AMOUNT
, OEH.TRANSACTIONAL_CURR_CODE OE_AMOUNT_CURRENCY
, DECODE(TDB.INTERNAL_BOOKING_FLAG
, 'Y'
, 'YES'
, 'NO') INTERNAL_BOOKING
, DECODE(TDB.INTERNAL_BOOKING_FLAG
, 'N'
, 'YES'
, 'NO') EXTERNAL_BOOKING
, EVT.EVENT_ID EVENT_ID
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TAD.ACTIVITY_ID ACTIVITY_ID
, TDB.BOOKING_ID ENROLLMENT_ID
, TDB.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TAV.RCO_ID RCO_ID
, OFR.OFFERING_ID OFFERING_ID
FROM OTA_DELEGATE_BOOKINGS TDB
, OTA_BOOKING_STATUS_TYPES BST
, 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_FINANCE_LINES TFL
, OE_ORDER_LINES_ALL OEL
, OE_ORDER_HEADERS_ALL OEH
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, OTA_OFFERINGS OFR
WHERE TDB.EVENT_ID = EVT.EVENT_ID
AND EVT.EVENT_ID = ETT.EVENT_ID
AND ETT.LANGUAGE = USERENV('LANG')
AND BST.BOOKING_STATUS_TYPE_ID = TDB.BOOKING_STATUS_TYPE_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND TDB.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND EVT.PARENT_OFFERING_ID = OFR.OFFERING_ID (+)
AND OFR.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID (+)
AND TAV.ACTIVITY_VERSION_ID = TVT.ACTIVITY_VERSION_ID (+)
AND TVT.LANGUAGE (+) = USERENV('LANG')
AND TAV.ACTIVITY_ID = TAD.ACTIVITY_ID (+)
AND TAD.ACTIVITY_ID = TDT.ACTIVITY_ID (+)
AND TDT.LANGUAGE (+) = USERENV('LANG')
AND TDB.BOOKING_ID = TFL.BOOKING_ID (+)
AND EVT.LINE_ID = OEL.LINE_ID (+)
AND OEL.HEADER_ID = OEH.HEADER_ID (+)
AND TDB.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, TDB.BUSINESS_GROUP_ID) WITH READ ONLY