VIEW: APPS.OTFV_FINANCE_LINES

Source

View Text - Preformatted

SELECT tfl.finance_line_id line_id ,hr_bis.bis_decode_lookup('FINANCE_LINE_TYPE',tfl.line_type) line_type ,tfl.sequence_number sequence ,hr_bis.bis_decode_lookup('YES_NO',tfl.cancelled_flag) line_cancelled ,ota_general.fnd_currency_name(tfl.currency_code) currency ,tfl.standard_amount standard_amount ,tfl.money_amount money_amount ,tfl.unitary_amount unitary_amount ,tfl.date_raised date_raised ,tfl.comments comments ,hr_bis.bis_decode_lookup('GL_TRANSFER_STATUS',tfl.transfer_status) transfer_status ,tfl.transfer_date transfer_date ,tfl.transfer_message transfer_message ,hr_bis.bis_decode_lookup('FINANCE_HEADER_TYPE',tfh.type) header_type ,tfh.receivable_type header_sub_type ,tfh.date_raised date_header_raised ,tbd.name enrollment_agreement ,DECODE(tbd.type,NULL,NULL, hr_bis.bis_decode_lookup('BOOKING_DEAL_TYPE',tbd.type)) agreement_type ,tbd.discount_percentage agreement_discount ,DECODE(tpl.training_unit_type,NULL,NULL, hr_bis.bis_decode_lookup('TRAINING_UNIT',tpl.training_unit_type)) training_unit ,DECODE(tbd.customer_id,NULL,'I','E') enrollment_type ,DECODE(tbd.customer_id, NULL, per.full_name, ota_tdb_bus.get_full_name(stu_party.person_last_name , ttl.meaning, stu_party.person_first_name, tdb.business_group_id,to_char(NULL), to_char(NULL)) ) student_name ,orgT.name organization_name ,substrb(CUST_PARTY.PARTY_NAME,1,50) customer_name ,DECODE(tdb.customer_id, NULL, spn.full_name, ota_tdb_bus.get_full_name(party.person_last_name , cti_look.meaning, party.person_first_name, tdb.business_group_id, TO_CHAR(NULL), TO_CHAR(NULL))) contact_name ,ett.title event_title ,tvt.version_name activity_name ,evt.course_start_date event_start_date ,TO_CHAR(NULL) resource_name ,TO_CHAR(NULL) resource_type ,TO_DATE(NULL) resource_booking_from ,TO_DATE(NULL) resource_booking_to ,TO_CHAR(NULL) supplier_name ,'_DF:OTA:OTA_FINANCE_LINES:tfl' ,'_DF:OTA:OTA_TFL_TRANSFER:tfl' ,tfl.finance_header_id header_id ,tfl.booking_deal_id enrollment_agreement_id ,tfl.booking_id enrollment_id ,TO_NUMBER(NULL) vendor_id ,DECODE(tdb.customer_id,NULL,tdb.delegate_person_id,tdb.delegate_contact_id) student_id ,TDB.ORGANIZATION_ID organization_id ,TDB.CUSTOMER_ID customer_id ,evt.event_id event_id ,tav.activity_version_id activity_version_id ,TO_NUMBER(NULL) resource_booking_id ,tfl.resource_allocation_id resource_allocation_id ,tav.rco_id rco_id ,ofr.offering_id offering_id FROM per_all_people_f per ,per_all_people_f spn ,hr_all_organization_units_tl orgT /* Start replacement of RA_CUSTOMERS view with table access */ ,HZ_CUST_ACCOUNTS CUST_ACCT ,HZ_PARTIES CUST_PARTY /* Start replacement of RA_CONTACTS view with table access */ ,HZ_CUST_ACCOUNT_ROLES ACCT_ROLE ,HZ_PARTIES PARTY ,HZ_RELATIONSHIPS REL ,HZ_ORG_CONTACTS ORG_CONT ,ar_lookups cti_look ,ar_lookups ttl /* Start replacement of RA_CONTACTS view with table access */ ,HZ_CUST_ACCOUNT_ROLES STU_ACCT_ROLE ,HZ_PARTIES STU_PARTY ,HZ_RELATIONSHIPS STU_REL ,HZ_ORG_CONTACTS STU_ORG_CONT ,ota_activity_versions tav ,ota_events evt ,ota_activity_versions_tl tvt ,ota_events_tl ett ,ota_offerings ofr ,ota_delegate_bookings tdb ,ota_price_lists tpl ,ota_booking_deals tbd ,ota_finance_lines tfl ,ota_finance_headers tfh WHERE tfl.line_type = 'E' AND tdb.booking_id = tfl.booking_id AND tbd.booking_deal_id (+) = tfl.booking_deal_id AND tpl.price_list_id (+) = tbd.price_list_id AND tdb.event_id = evt.event_id AND evt.event_id = ett.event_id AND ett.language = userenv('LANG') /* replacement of RA_CONTACTS view with table access */ AND tdb.delegate_contact_id = STU_ACCT_ROLE.cust_account_role_id (+) AND STU_ACCT_ROLE.PARTY_ID = STU_REL.PARTY_ID (+) AND STU_ACCT_ROLE.ROLE_TYPE (+) = 'CONTACT' AND STU_ORG_CONT.PARTY_RELATIONSHIP_ID (+) = STU_REL.RELATIONSHIP_ID AND STU_REL.SUBJECT_ID = STU_PARTY.PARTY_ID (+) AND STU_REL.RELATIONSHIP_CODE (+) = 'CONTACT_OF' /* replacement of RA_CUSTOMERS view with table access */ AND tdb.customer_id = CUST_ACCT.cust_account_id (+) AND CUST_ACCT.PARTY_ID = CUST_PARTY.PARTY_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') /* replacement of RA_CONTACTS view with table access */ AND tdb.contact_id = ACCT_ROLE.cust_account_role_id (+) AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID (+) AND ACCT_ROLE.ROLE_TYPE (+) = 'CONTACT' AND ORG_CONT.PARTY_RELATIONSHIP_ID (+) = REL.RELATIONSHIP_ID AND REL.SUBJECT_ID = PARTY.PARTY_ID (+) AND REL.RELATIONSHIP_CODE (+) = 'CONTACT_OF' AND cti_look.lookup_type (+) = 'CONTACT_TITLE' AND cti_look.lookup_code (+) = org_cont.title AND ttl.lookup_code (+) = STU_ORG_CONT.title AND ttl.lookup_type (+) = 'CONTACT_TITLE' AND tdb.organization_id = orgT.organization_id (+) AND orgT.language (+) = userenv('LANG') AND tdb.sponsor_person_id = spn.person_id (+) AND tdb.date_booking_placed BETWEEN spn.effective_start_date (+) AND spn.effective_end_date (+) AND tdb.delegate_person_id = per.person_id (+) AND NVL(evt.course_start_date,tdb.date_booking_placed) BETWEEN NVL(per.effective_start_date, NVL(evt.course_start_date, tdb.date_booking_placed)) AND NVL(per.effective_end_date, NVL(evt.course_start_date,tdb.date_booking_placed)) AND tfl.finance_header_id = tfh.finance_header_id UNION SELECT tfl.finance_line_id line_id ,hr_bis.bis_decode_lookup('FINANCE_LINE_TYPE',tfl.line_type) line_type ,tfl.sequence_number sequence ,hr_bis.bis_decode_lookup('YES_NO',tfl.cancelled_flag) line_cancelled ,ota_general.fnd_currency_name(tfl.currency_code) currency ,tfl.standard_amount standard_amount ,tfl.money_amount money_amount ,tfl.unitary_amount unitary_amount ,tfl.date_raised date_raised ,tfl.comments comments ,hr_bis.bis_decode_lookup('GL_TRANSFER_STATUS',tfl.transfer_status) transfer_status ,tfl.transfer_date transfer_date ,tfl.transfer_message transfer_message ,hr_bis.bis_decode_lookup('FINANCE_HEADER_TYPE',tfh.type) header_type ,tfh.receivable_type header_sub_type ,tfh.date_raised date_header_raised ,tdb.name enrollment_agreement ,DECODE(tdb.type,NULL,NULL, hr_bis.bis_decode_lookup('BOOKING_DEAL_TYPE',tdb.type)) agreement_type ,tdb.discount_percentage agreement_discount ,DECODE(tpl.training_unit_type,NULL,NULL, hr_bis.bis_decode_lookup('TRAINING_UNIT',tpl.training_unit_type)) training_unit ,TO_CHAR(NULL) enrollment_type ,TO_CHAR(NULL) student_name ,TO_CHAR(NULL) organization_name ,substrb(CUST_PARTY.PARTY_NAME,1,50) customer_name ,TO_CHAR(NULL) contact_name ,TO_CHAR(NULL) event_title ,TO_CHAR(NULL) activity_name ,TO_DATE(NULL) event_start_date ,TO_CHAR(NULL) resource_name ,TO_CHAR(NULL) resource_type ,TO_DATE(NULL) resource_booking_from ,TO_DATE(NULL) resource_booking_to ,TO_CHAR(NULL) supplier_name ,'_DF:OTA:OTA_FINANCE_LINES:tfl' ,'_DF:OTA:OTA_TFL_TRANSFER:tfl' ,tfl.finance_header_id header_id ,tfl.booking_deal_id enrollment_agreeement_id ,tfl.booking_id enrollment_id ,TO_NUMBER(NULL) vendor_id ,TO_NUMBER(NULL) student_id ,TO_NUMBER(NULL) organization_id ,tdb.customer_id customer_id ,TO_NUMBER(NULL) event_id ,TO_NUMBER(NULL) activity_version_id ,TO_NUMBER(NULL) resource_booking_id ,tfl.resource_allocation_id resource_allocation_id ,TO_NUMBER(NULL) rco_id ,TO_NUMBER(NULL) offering_id FROM ota_finance_lines tfl /* Start replacement of RA_CUSTOMERS view with table access */ ,HZ_CUST_ACCOUNTS CUST_ACCT ,HZ_PARTIES CUST_PARTY ,ota_booking_deals_v tdb ,ota_price_lists tpl ,ota_finance_headers tfh WHERE tfl.line_type = 'P' /* replacement of RA_CUSTOMERS view with table access */ AND tdb.customer_id = CUST_ACCT.cust_account_id AND CUST_ACCT.PARTY_ID = CUST_PARTY.PARTY_ID AND tdb.booking_deal_id (+) = tfl.booking_deal_id AND tpl.price_list_id (+) = tdb.price_list_id AND tfl.finance_header_id = tfh.finance_header_id UNION SELECT tfl.finance_line_id line_id ,hr_bis.bis_decode_lookup('FINANCE_LINE_TYPE',tfl.line_type) line_type ,tfl.sequence_number sequence ,hr_bis.bis_decode_lookup('YES_NO',tfl.cancelled_flag) line_cancelled ,ota_general.fnd_currency_name(tfl.currency_code) currency ,tfl.standard_amount standard_amount ,tfl.money_amount money_amount ,tfl.unitary_amount unitary_amount ,tfl.date_raised date_raised ,tfl.comments comments ,hr_bis.bis_decode_lookup('GL_TRANSFER_STATUS',tfl.transfer_status) transfer_status ,tfl.transfer_date transfer_date ,tfl.transfer_message transfer_message ,hr_bis.bis_decode_lookup('FINANCE_HEADER_TYPE',tfh.type) header_type ,tfh.receivable_type header_sub_type ,tfh.date_raised date_header_raised ,TO_CHAR(NULL) enrollment_agreement ,TO_CHAR(NULL) agreement_type ,TO_NUMBER(NULL) agreement_discount ,TO_CHAR(NULL) training_unit ,TO_CHAR(NULL) enrollment_type ,TO_CHAR(NULL) student_name ,TO_CHAR(NULL) organization_id ,TO_CHAR(NULL) customer_name ,TO_CHAR(NULL) contact_name ,ett.title event_title ,tvt.version_name activity_name ,evt.course_start_date course_start_date ,tsr.name resource_name ,DECODE(tsr.resource_type,NULL,NULL, hr_bis.bis_decode_lookup('RESOURCE_TYPE',tsr.resource_type)) resource_type ,trb.required_date_from resource_booking_from ,trb.required_date_to resource_booking_to ,ven.vendor_name supplier_name ,'_DF:OTA:OTA_FINANCE_LINES:tfl' ,'_DF:OTA:OTA_TFL_TRANSFER:tfl' ,tfl.finance_header_id header_id ,tfl.booking_deal_id enrollment_agreement_id ,TO_NUMBER(NULL) enrollment_id ,tsr.vendor_id vendor_id ,TO_NUMBER(NULL) student_id ,TO_NUMBER(NULL) organization_id ,TO_NUMBER(NULL) customer_id ,evt.event_id event_id ,tav.activity_version_id activity_version_id ,tfl.resource_booking_id resource_booking_id ,tfl.resource_allocation_id resource_allocation_id ,tav.rco_id rco_id ,ofr.offering_id offering_id FROM ota_finance_lines tfl ,ota_resource_bookings trb ,ota_events evt ,ota_activity_versions tav ,ota_events_tl ett ,ota_offerings ofr ,ota_activity_versions_tl tvt ,ota_suppliable_resources tsr ,ota_suppliable_resources_tl tst ,ota_finance_headers tfh ,po_vendors ven WHERE tfl.finance_header_id = tfh.finance_header_id AND tfl.line_type = 'V' AND evt.event_id (+) = trb.event_id AND evt.event_id = ett.event_id AND ett.language = userenv('LANG') AND trb.resource_booking_id = tfl.resource_booking_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 tsr.supplied_resource_id (+) = trb.supplied_resource_id AND tsr.supplied_resource_id = tst.supplied_resource_id (+) AND tst.language (+) = userenv('LANG') AND tsr.vendor_id = ven.vendor_id (+) UNION SELECT tfl.finance_line_id line_id ,hr_bis.bis_decode_lookup('FINANCE_LINE_TYPE',tfl.line_type) line_type ,tfl.sequence_number sequence ,hr_bis.bis_decode_lookup('YES_NO',tfl.cancelled_flag) line_cancelled ,ota_general.fnd_currency_name(tfl.currency_code) currency ,tfl.standard_amount standard_amount ,tfl.money_amount money_amount ,tfl.unitary_amount unitary_amount ,tfl.date_raised date_raised ,tfl.comments comments ,hr_bis.bis_decode_lookup('GL_TRANSFER_STATUS',tfl.transfer_status) transfer_status ,tfl.transfer_date transfer_date ,tfl.transfer_message transfer_message ,hr_bis.bis_decode_lookup('FINANCE_HEADER_TYPE',tfh.type) header_type ,tfh.receivable_type header_sub_type ,tfh.date_raised date_header_raised ,TO_CHAR(NULL) enrollment_agreement ,TO_CHAR(NULL) agreement_type ,TO_NUMBER(NULL) agreement_discount ,TO_CHAR(NULL) training_unit ,TO_CHAR(NULL) enrollment_type ,TO_CHAR(NULL) student_name ,TO_CHAR(NULL) organization_name ,TO_CHAR(NULL) customer_name ,TO_CHAR(NULL) contact_name ,TO_CHAR(NULL) event_title ,TO_CHAR(NULL) activity_name ,TO_DATE(NULL) event_start_date ,TO_CHAR(NULL) resource_name ,TO_CHAR(NULL) resource_type ,TO_DATE(NULL) resource_required_from ,TO_DATE(NULL) resource_required_to ,TO_CHAR(NULL) supplier_name ,'_DF:OTA:OTA_FINANCE_LINES:tfl' ,'_DF:OTA:OTA_TFL_TRANSFER:tfl' ,tfl.finance_header_id header_id ,tfl.booking_deal_id enrollment_agreement_id ,TO_NUMBER(NULL) enrollment_id ,TO_NUMBER(NULL) vendor_id ,TO_NUMBER(NULL) student_id ,TO_NUMBER(NULL) organization_id ,TO_NUMBER(NULL) customer_id ,TO_NUMBER(NULL) event_id ,TO_NUMBER(NULL) activity_version_id ,tfl.resource_booking_id resource_booking_id ,tfl.resource_allocation_id resource_allocation_id ,TO_NUMBER(NULL) rco_id ,TO_NUMBER(NULL) offering_id FROM ota_finance_lines tfl ,ota_finance_headers tfh WHERE tfl.finance_header_id = tfh.finance_header_id AND tfl.line_type NOT IN ('E','R','V','P') WITH READ ONLY

View Text - HTML Formatted

SELECT TFL.FINANCE_LINE_ID LINE_ID
, HR_BIS.BIS_DECODE_LOOKUP('FINANCE_LINE_TYPE'
, TFL.LINE_TYPE) LINE_TYPE
, TFL.SEQUENCE_NUMBER SEQUENCE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, TFL.CANCELLED_FLAG) LINE_CANCELLED
, OTA_GENERAL.FND_CURRENCY_NAME(TFL.CURRENCY_CODE) CURRENCY
, TFL.STANDARD_AMOUNT STANDARD_AMOUNT
, TFL.MONEY_AMOUNT MONEY_AMOUNT
, TFL.UNITARY_AMOUNT UNITARY_AMOUNT
, TFL.DATE_RAISED DATE_RAISED
, TFL.COMMENTS COMMENTS
, HR_BIS.BIS_DECODE_LOOKUP('GL_TRANSFER_STATUS'
, TFL.TRANSFER_STATUS) TRANSFER_STATUS
, TFL.TRANSFER_DATE TRANSFER_DATE
, TFL.TRANSFER_MESSAGE TRANSFER_MESSAGE
, HR_BIS.BIS_DECODE_LOOKUP('FINANCE_HEADER_TYPE'
, TFH.TYPE) HEADER_TYPE
, TFH.RECEIVABLE_TYPE HEADER_SUB_TYPE
, TFH.DATE_RAISED DATE_HEADER_RAISED
, TBD.NAME ENROLLMENT_AGREEMENT
, DECODE(TBD.TYPE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('BOOKING_DEAL_TYPE'
, TBD.TYPE)) AGREEMENT_TYPE
, TBD.DISCOUNT_PERCENTAGE AGREEMENT_DISCOUNT
, DECODE(TPL.TRAINING_UNIT_TYPE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('TRAINING_UNIT'
, TPL.TRAINING_UNIT_TYPE)) TRAINING_UNIT
, DECODE(TBD.CUSTOMER_ID
, NULL
, 'I'
, 'E') ENROLLMENT_TYPE
, DECODE(TBD.CUSTOMER_ID
, NULL
, PER.FULL_NAME
, OTA_TDB_BUS.GET_FULL_NAME(STU_PARTY.PERSON_LAST_NAME
, TTL.MEANING
, STU_PARTY.PERSON_FIRST_NAME
, TDB.BUSINESS_GROUP_ID
, TO_CHAR(NULL)
, TO_CHAR(NULL)) ) STUDENT_NAME
, ORGT.NAME ORGANIZATION_NAME
, SUBSTRB(CUST_PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, DECODE(TDB.CUSTOMER_ID
, NULL
, SPN.FULL_NAME
, OTA_TDB_BUS.GET_FULL_NAME(PARTY.PERSON_LAST_NAME
, CTI_LOOK.MEANING
, PARTY.PERSON_FIRST_NAME
, TDB.BUSINESS_GROUP_ID
, TO_CHAR(NULL)
, TO_CHAR(NULL))) CONTACT_NAME
, ETT.TITLE EVENT_TITLE
, TVT.VERSION_NAME ACTIVITY_NAME
, EVT.COURSE_START_DATE EVENT_START_DATE
, TO_CHAR(NULL) RESOURCE_NAME
, TO_CHAR(NULL) RESOURCE_TYPE
, TO_DATE(NULL) RESOURCE_BOOKING_FROM
, TO_DATE(NULL) RESOURCE_BOOKING_TO
, TO_CHAR(NULL) SUPPLIER_NAME
, '_DF:OTA:OTA_FINANCE_LINES:TFL'
, '_DF:OTA:OTA_TFL_TRANSFER:TFL'
, TFL.FINANCE_HEADER_ID HEADER_ID
, TFL.BOOKING_DEAL_ID ENROLLMENT_AGREEMENT_ID
, TFL.BOOKING_ID ENROLLMENT_ID
, TO_NUMBER(NULL) VENDOR_ID
, DECODE(TDB.CUSTOMER_ID
, NULL
, TDB.DELEGATE_PERSON_ID
, TDB.DELEGATE_CONTACT_ID) STUDENT_ID
, TDB.ORGANIZATION_ID ORGANIZATION_ID
, TDB.CUSTOMER_ID CUSTOMER_ID
, EVT.EVENT_ID EVENT_ID
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TO_NUMBER(NULL) RESOURCE_BOOKING_ID
, TFL.RESOURCE_ALLOCATION_ID RESOURCE_ALLOCATION_ID
, TAV.RCO_ID RCO_ID
, OFR.OFFERING_ID OFFERING_ID
FROM PER_ALL_PEOPLE_F PER
, PER_ALL_PEOPLE_F SPN
, HR_ALL_ORGANIZATION_UNITS_TL ORGT /* START REPLACEMENT OF RA_CUSTOMERS VIEW WITH TABLE ACCESS */
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES CUST_PARTY /* START REPLACEMENT OF RA_CONTACTS VIEW WITH TABLE ACCESS */
, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES PARTY
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, AR_LOOKUPS CTI_LOOK
, AR_LOOKUPS TTL /* START REPLACEMENT OF RA_CONTACTS VIEW WITH TABLE ACCESS */
, HZ_CUST_ACCOUNT_ROLES STU_ACCT_ROLE
, HZ_PARTIES STU_PARTY
, HZ_RELATIONSHIPS STU_REL
, HZ_ORG_CONTACTS STU_ORG_CONT
, OTA_ACTIVITY_VERSIONS TAV
, OTA_EVENTS EVT
, OTA_ACTIVITY_VERSIONS_TL TVT
, OTA_EVENTS_TL ETT
, OTA_OFFERINGS OFR
, OTA_DELEGATE_BOOKINGS TDB
, OTA_PRICE_LISTS TPL
, OTA_BOOKING_DEALS TBD
, OTA_FINANCE_LINES TFL
, OTA_FINANCE_HEADERS TFH
WHERE TFL.LINE_TYPE = 'E'
AND TDB.BOOKING_ID = TFL.BOOKING_ID
AND TBD.BOOKING_DEAL_ID (+) = TFL.BOOKING_DEAL_ID
AND TPL.PRICE_LIST_ID (+) = TBD.PRICE_LIST_ID
AND TDB.EVENT_ID = EVT.EVENT_ID
AND EVT.EVENT_ID = ETT.EVENT_ID
AND ETT.LANGUAGE = USERENV('LANG') /* REPLACEMENT OF RA_CONTACTS VIEW WITH TABLE ACCESS */
AND TDB.DELEGATE_CONTACT_ID = STU_ACCT_ROLE.CUST_ACCOUNT_ROLE_ID (+)
AND STU_ACCT_ROLE.PARTY_ID = STU_REL.PARTY_ID (+)
AND STU_ACCT_ROLE.ROLE_TYPE (+) = 'CONTACT'
AND STU_ORG_CONT.PARTY_RELATIONSHIP_ID (+) = STU_REL.RELATIONSHIP_ID
AND STU_REL.SUBJECT_ID = STU_PARTY.PARTY_ID (+)
AND STU_REL.RELATIONSHIP_CODE (+) = 'CONTACT_OF' /* REPLACEMENT OF RA_CUSTOMERS VIEW WITH TABLE ACCESS */
AND TDB.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID (+)
AND CUST_ACCT.PARTY_ID = CUST_PARTY.PARTY_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') /* REPLACEMENT OF RA_CONTACTS VIEW WITH TABLE ACCESS */
AND TDB.CONTACT_ID = ACCT_ROLE.CUST_ACCOUNT_ROLE_ID (+)
AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID (+)
AND ACCT_ROLE.ROLE_TYPE (+) = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID (+) = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID (+)
AND REL.RELATIONSHIP_CODE (+) = 'CONTACT_OF'
AND CTI_LOOK.LOOKUP_TYPE (+) = 'CONTACT_TITLE'
AND CTI_LOOK.LOOKUP_CODE (+) = ORG_CONT.TITLE
AND TTL.LOOKUP_CODE (+) = STU_ORG_CONT.TITLE
AND TTL.LOOKUP_TYPE (+) = 'CONTACT_TITLE'
AND TDB.ORGANIZATION_ID = ORGT.ORGANIZATION_ID (+)
AND ORGT.LANGUAGE (+) = USERENV('LANG')
AND TDB.SPONSOR_PERSON_ID = SPN.PERSON_ID (+)
AND TDB.DATE_BOOKING_PLACED BETWEEN SPN.EFFECTIVE_START_DATE (+)
AND SPN.EFFECTIVE_END_DATE (+)
AND TDB.DELEGATE_PERSON_ID = PER.PERSON_ID (+)
AND NVL(EVT.COURSE_START_DATE
, TDB.DATE_BOOKING_PLACED) BETWEEN NVL(PER.EFFECTIVE_START_DATE
, NVL(EVT.COURSE_START_DATE
, TDB.DATE_BOOKING_PLACED))
AND NVL(PER.EFFECTIVE_END_DATE
, NVL(EVT.COURSE_START_DATE
, TDB.DATE_BOOKING_PLACED))
AND TFL.FINANCE_HEADER_ID = TFH.FINANCE_HEADER_ID UNION SELECT TFL.FINANCE_LINE_ID LINE_ID
, HR_BIS.BIS_DECODE_LOOKUP('FINANCE_LINE_TYPE'
, TFL.LINE_TYPE) LINE_TYPE
, TFL.SEQUENCE_NUMBER SEQUENCE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, TFL.CANCELLED_FLAG) LINE_CANCELLED
, OTA_GENERAL.FND_CURRENCY_NAME(TFL.CURRENCY_CODE) CURRENCY
, TFL.STANDARD_AMOUNT STANDARD_AMOUNT
, TFL.MONEY_AMOUNT MONEY_AMOUNT
, TFL.UNITARY_AMOUNT UNITARY_AMOUNT
, TFL.DATE_RAISED DATE_RAISED
, TFL.COMMENTS COMMENTS
, HR_BIS.BIS_DECODE_LOOKUP('GL_TRANSFER_STATUS'
, TFL.TRANSFER_STATUS) TRANSFER_STATUS
, TFL.TRANSFER_DATE TRANSFER_DATE
, TFL.TRANSFER_MESSAGE TRANSFER_MESSAGE
, HR_BIS.BIS_DECODE_LOOKUP('FINANCE_HEADER_TYPE'
, TFH.TYPE) HEADER_TYPE
, TFH.RECEIVABLE_TYPE HEADER_SUB_TYPE
, TFH.DATE_RAISED DATE_HEADER_RAISED
, TDB.NAME ENROLLMENT_AGREEMENT
, DECODE(TDB.TYPE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('BOOKING_DEAL_TYPE'
, TDB.TYPE)) AGREEMENT_TYPE
, TDB.DISCOUNT_PERCENTAGE AGREEMENT_DISCOUNT
, DECODE(TPL.TRAINING_UNIT_TYPE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('TRAINING_UNIT'
, TPL.TRAINING_UNIT_TYPE)) TRAINING_UNIT
, TO_CHAR(NULL) ENROLLMENT_TYPE
, TO_CHAR(NULL) STUDENT_NAME
, TO_CHAR(NULL) ORGANIZATION_NAME
, SUBSTRB(CUST_PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, TO_CHAR(NULL) CONTACT_NAME
, TO_CHAR(NULL) EVENT_TITLE
, TO_CHAR(NULL) ACTIVITY_NAME
, TO_DATE(NULL) EVENT_START_DATE
, TO_CHAR(NULL) RESOURCE_NAME
, TO_CHAR(NULL) RESOURCE_TYPE
, TO_DATE(NULL) RESOURCE_BOOKING_FROM
, TO_DATE(NULL) RESOURCE_BOOKING_TO
, TO_CHAR(NULL) SUPPLIER_NAME
, '_DF:OTA:OTA_FINANCE_LINES:TFL'
, '_DF:OTA:OTA_TFL_TRANSFER:TFL'
, TFL.FINANCE_HEADER_ID HEADER_ID
, TFL.BOOKING_DEAL_ID ENROLLMENT_AGREEEMENT_ID
, TFL.BOOKING_ID ENROLLMENT_ID
, TO_NUMBER(NULL) VENDOR_ID
, TO_NUMBER(NULL) STUDENT_ID
, TO_NUMBER(NULL) ORGANIZATION_ID
, TDB.CUSTOMER_ID CUSTOMER_ID
, TO_NUMBER(NULL) EVENT_ID
, TO_NUMBER(NULL) ACTIVITY_VERSION_ID
, TO_NUMBER(NULL) RESOURCE_BOOKING_ID
, TFL.RESOURCE_ALLOCATION_ID RESOURCE_ALLOCATION_ID
, TO_NUMBER(NULL) RCO_ID
, TO_NUMBER(NULL) OFFERING_ID
FROM OTA_FINANCE_LINES TFL /* START REPLACEMENT OF RA_CUSTOMERS VIEW WITH TABLE ACCESS */
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES CUST_PARTY
, OTA_BOOKING_DEALS_V TDB
, OTA_PRICE_LISTS TPL
, OTA_FINANCE_HEADERS TFH
WHERE TFL.LINE_TYPE = 'P' /* REPLACEMENT OF RA_CUSTOMERS VIEW WITH TABLE ACCESS */
AND TDB.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = CUST_PARTY.PARTY_ID
AND TDB.BOOKING_DEAL_ID (+) = TFL.BOOKING_DEAL_ID
AND TPL.PRICE_LIST_ID (+) = TDB.PRICE_LIST_ID
AND TFL.FINANCE_HEADER_ID = TFH.FINANCE_HEADER_ID UNION SELECT TFL.FINANCE_LINE_ID LINE_ID
, HR_BIS.BIS_DECODE_LOOKUP('FINANCE_LINE_TYPE'
, TFL.LINE_TYPE) LINE_TYPE
, TFL.SEQUENCE_NUMBER SEQUENCE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, TFL.CANCELLED_FLAG) LINE_CANCELLED
, OTA_GENERAL.FND_CURRENCY_NAME(TFL.CURRENCY_CODE) CURRENCY
, TFL.STANDARD_AMOUNT STANDARD_AMOUNT
, TFL.MONEY_AMOUNT MONEY_AMOUNT
, TFL.UNITARY_AMOUNT UNITARY_AMOUNT
, TFL.DATE_RAISED DATE_RAISED
, TFL.COMMENTS COMMENTS
, HR_BIS.BIS_DECODE_LOOKUP('GL_TRANSFER_STATUS'
, TFL.TRANSFER_STATUS) TRANSFER_STATUS
, TFL.TRANSFER_DATE TRANSFER_DATE
, TFL.TRANSFER_MESSAGE TRANSFER_MESSAGE
, HR_BIS.BIS_DECODE_LOOKUP('FINANCE_HEADER_TYPE'
, TFH.TYPE) HEADER_TYPE
, TFH.RECEIVABLE_TYPE HEADER_SUB_TYPE
, TFH.DATE_RAISED DATE_HEADER_RAISED
, TO_CHAR(NULL) ENROLLMENT_AGREEMENT
, TO_CHAR(NULL) AGREEMENT_TYPE
, TO_NUMBER(NULL) AGREEMENT_DISCOUNT
, TO_CHAR(NULL) TRAINING_UNIT
, TO_CHAR(NULL) ENROLLMENT_TYPE
, TO_CHAR(NULL) STUDENT_NAME
, TO_CHAR(NULL) ORGANIZATION_ID
, TO_CHAR(NULL) CUSTOMER_NAME
, TO_CHAR(NULL) CONTACT_NAME
, ETT.TITLE EVENT_TITLE
, TVT.VERSION_NAME ACTIVITY_NAME
, EVT.COURSE_START_DATE COURSE_START_DATE
, TSR.NAME RESOURCE_NAME
, DECODE(TSR.RESOURCE_TYPE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('RESOURCE_TYPE'
, TSR.RESOURCE_TYPE)) RESOURCE_TYPE
, TRB.REQUIRED_DATE_FROM RESOURCE_BOOKING_FROM
, TRB.REQUIRED_DATE_TO RESOURCE_BOOKING_TO
, VEN.VENDOR_NAME SUPPLIER_NAME
, '_DF:OTA:OTA_FINANCE_LINES:TFL'
, '_DF:OTA:OTA_TFL_TRANSFER:TFL'
, TFL.FINANCE_HEADER_ID HEADER_ID
, TFL.BOOKING_DEAL_ID ENROLLMENT_AGREEMENT_ID
, TO_NUMBER(NULL) ENROLLMENT_ID
, TSR.VENDOR_ID VENDOR_ID
, TO_NUMBER(NULL) STUDENT_ID
, TO_NUMBER(NULL) ORGANIZATION_ID
, TO_NUMBER(NULL) CUSTOMER_ID
, EVT.EVENT_ID EVENT_ID
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TFL.RESOURCE_BOOKING_ID RESOURCE_BOOKING_ID
, TFL.RESOURCE_ALLOCATION_ID RESOURCE_ALLOCATION_ID
, TAV.RCO_ID RCO_ID
, OFR.OFFERING_ID OFFERING_ID
FROM OTA_FINANCE_LINES TFL
, OTA_RESOURCE_BOOKINGS TRB
, OTA_EVENTS EVT
, OTA_ACTIVITY_VERSIONS TAV
, OTA_EVENTS_TL ETT
, OTA_OFFERINGS OFR
, OTA_ACTIVITY_VERSIONS_TL TVT
, OTA_SUPPLIABLE_RESOURCES TSR
, OTA_SUPPLIABLE_RESOURCES_TL TST
, OTA_FINANCE_HEADERS TFH
, PO_VENDORS VEN
WHERE TFL.FINANCE_HEADER_ID = TFH.FINANCE_HEADER_ID
AND TFL.LINE_TYPE = 'V'
AND EVT.EVENT_ID (+) = TRB.EVENT_ID
AND EVT.EVENT_ID = ETT.EVENT_ID
AND ETT.LANGUAGE = USERENV('LANG')
AND TRB.RESOURCE_BOOKING_ID = TFL.RESOURCE_BOOKING_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 TSR.SUPPLIED_RESOURCE_ID (+) = TRB.SUPPLIED_RESOURCE_ID
AND TSR.SUPPLIED_RESOURCE_ID = TST.SUPPLIED_RESOURCE_ID (+)
AND TST.LANGUAGE (+) = USERENV('LANG')
AND TSR.VENDOR_ID = VEN.VENDOR_ID (+) UNION SELECT TFL.FINANCE_LINE_ID LINE_ID
, HR_BIS.BIS_DECODE_LOOKUP('FINANCE_LINE_TYPE'
, TFL.LINE_TYPE) LINE_TYPE
, TFL.SEQUENCE_NUMBER SEQUENCE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, TFL.CANCELLED_FLAG) LINE_CANCELLED
, OTA_GENERAL.FND_CURRENCY_NAME(TFL.CURRENCY_CODE) CURRENCY
, TFL.STANDARD_AMOUNT STANDARD_AMOUNT
, TFL.MONEY_AMOUNT MONEY_AMOUNT
, TFL.UNITARY_AMOUNT UNITARY_AMOUNT
, TFL.DATE_RAISED DATE_RAISED
, TFL.COMMENTS COMMENTS
, HR_BIS.BIS_DECODE_LOOKUP('GL_TRANSFER_STATUS'
, TFL.TRANSFER_STATUS) TRANSFER_STATUS
, TFL.TRANSFER_DATE TRANSFER_DATE
, TFL.TRANSFER_MESSAGE TRANSFER_MESSAGE
, HR_BIS.BIS_DECODE_LOOKUP('FINANCE_HEADER_TYPE'
, TFH.TYPE) HEADER_TYPE
, TFH.RECEIVABLE_TYPE HEADER_SUB_TYPE
, TFH.DATE_RAISED DATE_HEADER_RAISED
, TO_CHAR(NULL) ENROLLMENT_AGREEMENT
, TO_CHAR(NULL) AGREEMENT_TYPE
, TO_NUMBER(NULL) AGREEMENT_DISCOUNT
, TO_CHAR(NULL) TRAINING_UNIT
, TO_CHAR(NULL) ENROLLMENT_TYPE
, TO_CHAR(NULL) STUDENT_NAME
, TO_CHAR(NULL) ORGANIZATION_NAME
, TO_CHAR(NULL) CUSTOMER_NAME
, TO_CHAR(NULL) CONTACT_NAME
, TO_CHAR(NULL) EVENT_TITLE
, TO_CHAR(NULL) ACTIVITY_NAME
, TO_DATE(NULL) EVENT_START_DATE
, TO_CHAR(NULL) RESOURCE_NAME
, TO_CHAR(NULL) RESOURCE_TYPE
, TO_DATE(NULL) RESOURCE_REQUIRED_FROM
, TO_DATE(NULL) RESOURCE_REQUIRED_TO
, TO_CHAR(NULL) SUPPLIER_NAME
, '_DF:OTA:OTA_FINANCE_LINES:TFL'
, '_DF:OTA:OTA_TFL_TRANSFER:TFL'
, TFL.FINANCE_HEADER_ID HEADER_ID
, TFL.BOOKING_DEAL_ID ENROLLMENT_AGREEMENT_ID
, TO_NUMBER(NULL) ENROLLMENT_ID
, TO_NUMBER(NULL) VENDOR_ID
, TO_NUMBER(NULL) STUDENT_ID
, TO_NUMBER(NULL) ORGANIZATION_ID
, TO_NUMBER(NULL) CUSTOMER_ID
, TO_NUMBER(NULL) EVENT_ID
, TO_NUMBER(NULL) ACTIVITY_VERSION_ID
, TFL.RESOURCE_BOOKING_ID RESOURCE_BOOKING_ID
, TFL.RESOURCE_ALLOCATION_ID RESOURCE_ALLOCATION_ID
, TO_NUMBER(NULL) RCO_ID
, TO_NUMBER(NULL) OFFERING_ID
FROM OTA_FINANCE_LINES TFL
, OTA_FINANCE_HEADERS TFH
WHERE TFL.FINANCE_HEADER_ID = TFH.FINANCE_HEADER_ID
AND TFL.LINE_TYPE NOT IN ('E'
, 'R'
, 'V'
, 'P') WITH READ ONLY