VIEW: APPS.OTFV_COST_TRANSFER

Source

View Text - Preformatted

SELECT bgrT.name business_group_name ,tfh.paying_cost_center paying_cost_center ,ett.title class_title ,evt.event_type class_type ,dmt.category category_name ,tvt.version_name course_name ,evt.course_start_date class_start_date ,(evt.course_end_date-evt.course_start_date+1) training_duration_days ,orgT.name class_center ,del.full_name student_name ,del.employee_number employee_number ,btt.name booking_status ,tdb.date_booking_placed booking_date ,tfl.date_raised billing_date ,tfl.currency_code currency ,tfl.money_amount enrollment_amount ,tfl.transfer_date transfer_date ,DECODE(tfl.transfer_status,NULL,NULL, hr_bis.bis_decode_lookup('GL_TRANSFER_STATUS',tfl.transfer_status)) transfer_status ,'_KF:SQLGL:GL#:gcc' ,tdb.delegate_person_id person_id ,tdb.booking_id booking_id ,tdb.booking_status_type_id booking_status_type_id ,tfl.finance_line_id finance_line_id ,tfh.finance_header_id financde_header_id ,tdb.business_group_id business_group_id ,evt.training_center_id training_center_id ,ofr.offering_id offering_id ,tav.rco_id rco_id FROM ota_delegate_bookings tdb ,ota_finance_lines tfl ,ota_finance_headers tfh ,gl_code_combinations gcc ,ota_booking_status_types bst ,ota_booking_status_types_TL btt ,per_all_people_f del ,ota_events evt ,ota_events_tl ett ,ota_offerings ofr ,ota_activity_versions tav ,ota_activity_versions_tl tvt ,hr_all_organization_units_tl bgrT ,hr_all_organization_units_tl orgT ,ota_category_usages dmd ,ota_category_usages_tl dmt WHERE tfh.type = 'CT' AND evt.training_center_id = orgT.organization_id(+) AND DECODE(orgT.language,NULL,'X',orgT.language) = DECODE(orgT.language,NULL,'X',userenv('LANG')) AND tfh.finance_header_id = tfl.finance_header_id AND tfh.transfer_from_cc_id = gcc.code_combination_id(+) AND tfl.booking_id = tdb.booking_id(+) AND tdb.booking_status_type_id = bst.booking_status_type_id (+) AND bst.booking_status_type_id = btt.booking_status_type_id (+) AND btt.language (+) = userenv('LANG') AND tdb.delegate_person_id = del.person_id(+) AND tdb.date_booking_placed BETWEEN del.effective_start_date(+) AND del.effective_end_date(+) AND tdb.event_id = evt.event_id (+) AND evt.event_id = ett.event_id (+) AND ett.language (+) = userenv('LANG') AND evt.parent_offering_id = ofr.offering_id (+) AND ofr.delivery_mode_id = dmd.category_usage_id (+) AND dmd.category_usage_id = dmt.category_usage_id (+) AND dmt.language (+) = userenv('LANG') AND ofr.activity_version_id = tav.activity_version_id (+) AND tav.activity_version_id = tvt.activity_version_id (+) AND tvt.language (+) = userenv('LANG') AND (dmd.type = 'DM' OR dmd.type is null) AND tdb.business_group_id = bgrT.organization_id(+) AND DECODE(bgrT.language,NULL,'X',bgrT.language) = DECODE(bgrT.language,NULL,'X',userenv('LANG')) 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
, TFH.PAYING_COST_CENTER PAYING_COST_CENTER
, ETT.TITLE CLASS_TITLE
, EVT.EVENT_TYPE CLASS_TYPE
, DMT.CATEGORY CATEGORY_NAME
, TVT.VERSION_NAME COURSE_NAME
, EVT.COURSE_START_DATE CLASS_START_DATE
, (EVT.COURSE_END_DATE-EVT.COURSE_START_DATE+1) TRAINING_DURATION_DAYS
, ORGT.NAME CLASS_CENTER
, DEL.FULL_NAME STUDENT_NAME
, DEL.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, BTT.NAME BOOKING_STATUS
, TDB.DATE_BOOKING_PLACED BOOKING_DATE
, TFL.DATE_RAISED BILLING_DATE
, TFL.CURRENCY_CODE CURRENCY
, TFL.MONEY_AMOUNT ENROLLMENT_AMOUNT
, TFL.TRANSFER_DATE TRANSFER_DATE
, DECODE(TFL.TRANSFER_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('GL_TRANSFER_STATUS'
, TFL.TRANSFER_STATUS)) TRANSFER_STATUS
, '_KF:SQLGL:GL#:GCC'
, TDB.DELEGATE_PERSON_ID PERSON_ID
, TDB.BOOKING_ID BOOKING_ID
, TDB.BOOKING_STATUS_TYPE_ID BOOKING_STATUS_TYPE_ID
, TFL.FINANCE_LINE_ID FINANCE_LINE_ID
, TFH.FINANCE_HEADER_ID FINANCDE_HEADER_ID
, TDB.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, EVT.TRAINING_CENTER_ID TRAINING_CENTER_ID
, OFR.OFFERING_ID OFFERING_ID
, TAV.RCO_ID RCO_ID
FROM OTA_DELEGATE_BOOKINGS TDB
, OTA_FINANCE_LINES TFL
, OTA_FINANCE_HEADERS TFH
, GL_CODE_COMBINATIONS GCC
, OTA_BOOKING_STATUS_TYPES BST
, OTA_BOOKING_STATUS_TYPES_TL BTT
, PER_ALL_PEOPLE_F DEL
, OTA_EVENTS EVT
, OTA_EVENTS_TL ETT
, OTA_OFFERINGS OFR
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_VERSIONS_TL TVT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, OTA_CATEGORY_USAGES DMD
, OTA_CATEGORY_USAGES_TL DMT
WHERE TFH.TYPE = 'CT'
AND EVT.TRAINING_CENTER_ID = ORGT.ORGANIZATION_ID(+)
AND DECODE(ORGT.LANGUAGE
, NULL
, 'X'
, ORGT.LANGUAGE) = DECODE(ORGT.LANGUAGE
, NULL
, 'X'
, USERENV('LANG'))
AND TFH.FINANCE_HEADER_ID = TFL.FINANCE_HEADER_ID
AND TFH.TRANSFER_FROM_CC_ID = GCC.CODE_COMBINATION_ID(+)
AND TFL.BOOKING_ID = TDB.BOOKING_ID(+)
AND TDB.BOOKING_STATUS_TYPE_ID = BST.BOOKING_STATUS_TYPE_ID (+)
AND BST.BOOKING_STATUS_TYPE_ID = BTT.BOOKING_STATUS_TYPE_ID (+)
AND BTT.LANGUAGE (+) = USERENV('LANG')
AND TDB.DELEGATE_PERSON_ID = DEL.PERSON_ID(+)
AND TDB.DATE_BOOKING_PLACED BETWEEN DEL.EFFECTIVE_START_DATE(+)
AND DEL.EFFECTIVE_END_DATE(+)
AND TDB.EVENT_ID = EVT.EVENT_ID (+)
AND EVT.EVENT_ID = ETT.EVENT_ID (+)
AND ETT.LANGUAGE (+) = USERENV('LANG')
AND EVT.PARENT_OFFERING_ID = OFR.OFFERING_ID (+)
AND OFR.DELIVERY_MODE_ID = DMD.CATEGORY_USAGE_ID (+)
AND DMD.CATEGORY_USAGE_ID = DMT.CATEGORY_USAGE_ID (+)
AND DMT.LANGUAGE (+) = USERENV('LANG')
AND OFR.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID (+)
AND TAV.ACTIVITY_VERSION_ID = TVT.ACTIVITY_VERSION_ID (+)
AND TVT.LANGUAGE (+) = USERENV('LANG')
AND (DMD.TYPE = 'DM' OR DMD.TYPE IS NULL)
AND TDB.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID(+)
AND DECODE(BGRT.LANGUAGE
, NULL
, 'X'
, BGRT.LANGUAGE) = DECODE(BGRT.LANGUAGE
, NULL
, 'X'
, USERENV('LANG'))
AND TDB.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, TDB.BUSINESS_GROUP_ID) WITH READ ONLY