Search Results booking_object_version_number

Overview

OTA_EVENT_ASSOCIAT_BILLING_V is an APPS-owned database view in the Oracle E-Business Suite OTA (Learning Management) product family. Its documented purpose is to expose the finance and billing information associated with an event association. In OTA, an event association links a delegate booking to an event offering, and the pricing and billing details for that booking are held in the OTA finance tables. This view consolidates the booking, delegate, pricing, customer, and finance header/line data into a single denormalized row suitable for reporting, inquiry screens, and integration extracts.

The view is defined with a WHERE predicate of EVT.PRICE_BASIS = 'C', which restricts the result set to events priced on a fixed or standard-price basis rather than per-attendee or other bases. It therefore reports only a subset of event associations: those whose associated event carries the price basis flag 'C'. Rows are further limited to non-cancelled finance lines (TFL.CANCELLED_FLAG = 'N'). The view is VALID in the APPS schema and is one of several OTA views that expose finance data for downstream reporting.

Underlying Base Objects

The view joins eleven base objects. The driving tables are OTA_EVENT_ASSOCIATIONS (aliased TEA) and OTA_DELEGATE_BOOKINGS (TDB), joined through the event and customer identifiers. OTA_EVENTS (EVT) supplies the event's standard price and currency. OTA_FINANCE_LINES (TFL) and OTA_FINANCE_HEADERS (TFH) provide the billing amounts, customer, invoice contact, and invoice address; the header join is an outer join, so finance lines without a header are retained. OTA_BOOKING_DEALS (TBD) and OTA_PRICE_LISTS (TPL) supply deal and price-list information.

Party and customer data comes from HZ_PARTIES (both the customer party CUS_PARTY and the person party PARTY), HZ_CUST_ACCOUNTS, HZ_CUST_ACCOUNT_ROLES, HZ_ORG_CONTACTS, and HZ_RELATIONSHIPS. Lookup translations are provided by HR_LOOKUPS (training unit type), AR_LOOKUPS (contact title), and OTA_BOOKING_STATUS_TYPES_TL (booking status name). The HR_API and HR_GENERAL packages are referenced for the DECODE_LOOKUP function used to translate booking source and other lookup values. Several of these objects are resolved through synonyms in the APPS schema.

Key Columns

• EVENT_ASSOCIATION_ID — Identifier of the parent event association.
• BOOKING_ID — Delegate booking identifier linking to OTA_DELEGATE_BOOKINGS.
• FINANCE_HEADER_ID, FINANCE_LINE_ID — Finance document identifiers for the billing header and line.
• CUSTOMER_ID — Customer account identifier on the finance header; the underlying account id used when reconciling billing to the customer.
• INVOICE_CONTACT, INVOICE_ADDRESS — Contact and address to which the invoice is directed.
• STANDARD_AMOUNT, MONEY_AMOUNT, UNITARY_AMOUNT — List, net, and per-unit amounts from the finance line.
• STANDARD_PRICE, CURRENCY_CODE — Event-level standard price and its currency.
• CONTACT_ID, PERSON_LAST_NAME, PERSON_FIRST_NAME, PERSON_PRE_NAME_ADJUNCT, MEANING — Delegate name components and title from AR_LOOKUPS.
• BOOKING_STATUS_TYPE_ID, NAME (status) — Booking status and its translated display name.
• TRAINING_UNIT_TYPE, TU_LOOK.MEANING — Price-list training unit and its lookup meaning.

Common Use Cases and Queries

The most frequent use is billing reconciliation: matching delegate bookings to the finance lines and headers that generate invoices. The view is also used to report revenue by customer, event, and currency, and to drive inquiries on event association finance data. A typical query filters on the customer identifier exposed by the view. Note that the searched term invoice_customer_id is not a column of this view; the customer identifier surfaced for billing purposes is CUSTOMER_ID (sourced from TFH.CUSTOMER_ID).

Sample query listing billing amounts for a customer:

• SELECT event_association_id, booking_id, finance_header_id, customer_id, money_amount, currency_code FROM ota_event_associat_billing_v WHERE customer_id = :p_customer_id;

Aggregating billed revenue by event and currency:

• SELECT event_association_id, currency_code, SUM(money_amount) billed FROM ota_event_associat_billing_v GROUP BY event_association_id, currency_code;

Because the view already restricts to non-cancelled lines and fixed-price events, consumers do not need to repeat those predicates; adding them is harmless but redundant.