Search Results roc_id

Overview

APPS.OTFV_FINANCE_LINES is an Oracle E-Business Suite 12.1.1 and 12.2.2 reporting view that consolidates the financial transaction lines generated within the Oracle Training Administration (OTA) module of Oracle EBS. It denormalizes the core finance line table OTA_FINANCE_LINES by joining it to its parent header, booking deal, enrollment, student, customer, organization, contact, and activity context, so that a single flat row describes both the monetary movement and the training or enrollment event that produced it.

The view is not a base table; it is a read-only presentation layer used for reporting, extracts, and downstream integration. Lookup values such as line_type, transfer_status, header_type, agreement_type, and training_unit are translated through the HR_BIS packaging using hr_bis.bis_decode_lookup, converting internal codes into the user-facing meanings stored in AR_LOOKUPS. Currency codes are similarly rendered through ota_general.fnd_currency_name. Because the view embeds several OTA packages for name formatting and decoding, it inherits those packages' logic and returns fully resolved, presentation-ready data rather than raw identifiers.

This makes OTFV_FINANCE_LINES particularly relevant in the context of the header_sub_type attribute, which it exposes directly from the parent finance header, allowing a report to classify finance lines by both the top-level header type and the more granular receivable type.

Underlying Base Objects

The view's FROM clause is anchored on OTA_FINANCE_LINES (synonym aliased as tfl) joined to OTA_FINANCE_HEADERS (tfh) on finance_header_id, supplying the header_type and header_sub_type values. From this core it fans out to OTA_BOOKING_DEALS (tbd) for agreement data, OTA_BOOKING_DEALS_V and OTA_DELEGATE_BOOKINGS (tdb) for enrollment and student identification, PER_ALL_PEOPLE_F (per, spn) for internal student and contact names, HZ_PARTIES, HZ_CUST_ACCOUNTS, HZ_CUST_ACCOUNT_ROLES, HZ_ORG_CONTACTS, and HZ_RELATIONSHIPS for the external customer and contact hierarchy, HR_ALL_ORGANIZATION_UNITS_TL (orgT) for organization names, and OTA_EVENTS/OTA_EVENTS_TL (ett) and OTA_ACTIVITY_VERSIONS/OTA_ACTIVITY_VERSIONS_TL (tvt) for event and activity titles. Suppliable resource and supplier context is drawn from OTA_SUPPLIABLE_RESOURCES, OTA_SUPPLIABLE_RESOURCES_TL, OTA_OFFERINGS, OTA_PRICE_LISTS, OTA_RESOURCE_BOOKINGS, and PO_VENDORS.

Decoding and name assembly are delegated to HR_BIS, HR_GENERAL, OTA_GENERAL, OTA_TBD_API, and OTA_TDB_BUS. Lookup meanings are resolved from AR_LOOKUPS. Two hidden descriptor columns (_DF:OTA:OTA_FINANCE_LINES:tfl and _DF:OTA:OTA_TFL_TRANSFER:tfl) are exposed to support Oracle's flexfield/descriptive-flex reporting conventions.

Key Columns

• line_id — the finance_line_id, primary identifier of each finance line.
• line_type, line_cancelled, transfer_status, transfer_date, transfer_message — decoded financial attributes describing the nature, cancellation state, and GL transfer status of the line.
• currency, standard_amount, money_amount, unitary_amount, date_raised, comments — the monetary content of the line.
• header_type and header_sub_type — the decoded FINANCE_HEADER_TYPE and the raw receivable_type of the parent header; header_sub_type is the column most directly sought when classifying finance lines by header subtype.
• header_id, booking_deal_id — foreign keys to OTA_FINANCE_HEADERS and the associated booking deal.
• enrollment_agreement, agreement_type, agreement_discount, training_unit, enrollment_type, student_name, organization_name, customer_name, contact_name — the participant and agreement context.
• event_title, activity_name, event_start_date — training event scheduling context.
• resource_name, resource_type, resource_booking_from, resource_booking_to, supplier_name — placeholders in the documented text, present for integration symmetry.

Common Use Cases and Queries

Typical uses include finance reconciliation reports, revenue recognition extracts, and feed programs that export OTA transactions to external financial systems. The view is also used to analyze receivables by header subtype and to reconcile transfers to General Ledger. A representative query grouping amounts by header subtype is:

SELECT header_type, header_sub_type, currency, SUM(money_amount) total_amount
FROM apps.otfv_finance_lines
WHERE line_cancelled = 'No'
GROUP BY header_type, header_sub_type, currency;

A second pattern lists uncancelled lines for a given booking deal with student and event detail:

SELECT line_id, line_type, header_sub_type, student_name, event_title, money_amount
FROM apps.otfv_finance_lines
WHERE booking_deal_id = :p_deal_id
AND line_cancelled = 'No';

Because the view resolves lookups and names at query time, filter predicates should generally be applied to the underlying codes or to the already-decoded columns with the same values the lookup returns, and joins to OTA_FINANCE_LINES or OTA_FINANCE_HEADERS remain the most reliable way to constrain by header_sub_type.