Search Results oks_bill_actions




Overview
Explore in AI Chat

Overview

APPS.OKS_BILLING_HIST_V is a service contracts reporting view in Oracle E-Business Suite Release 12.1.1 and 12.2.2 that consolidates billing history records originating from the Oracle Service Contracts (OKS) billing schema. It presents a unified, query-friendly projection of billing transactions, transaction lines, contract lines, and sub-lines, translating internal lookup codes into user-facing meanings. The view is defined as a UNION ALL of three component queries, each of which joins a different combination of billing base objects against FND_LOOKUPS to resolve the bill_action code.

Because the view joins the billing header context (contract line, billing period dates, currency) with the transaction detail (transaction number, tax amount, line amount), it is a practical source for reporting on what was billed, when it was billed, and under what action. The view is exposed in the APPS schema and is typically consumed by reports, concurrent programs, and custom integrations that need a denormalized read model of service contract billing activity.

Underlying Base Objects

The documented base objects underlying the view are OKS_BILL_CONT_LINES, OKS_BILL_TXN_LINES, OKS_BILL_SUB_LINES, OKS_BILL_TRANSACTIONS, FND_LOOKUPS, and FND_GLOBAL. The first four are synonyms pointing to the corresponding service contracts billing tables.

  • OKS_BILL_CONT_LINES (bcl) — the driving billing contract line table; supplies row_id, id, cle_id, bill_action, creation_date, billing period dates (DATE_BILLED_FROM, DATE_BILLED_TO), and currency_code.
  • OKS_BILL_TRANSACTIONS (btr) — the billing transaction header; joined to bcl on BTN_ID and used to supply trx_class and TRX_NUMBER when a transaction line does not provide them.
  • OKS_BILL_TXN_LINES (btl) — the billing transaction line; supplies TRX_LINE_TAX_AMOUNT, TRX_LINE_AMOUNT, and, where present, trx_class and TRX_NUMBER.
  • OKS_BILL_SUB_LINES (bsl) — the billing sub-line; used in the second and third UNION branches to capture sub-line-level amounts where btl.bsl_id is not null.
  • FND_LOOKUPS (fnd) — joined on lookup_type = 'OKS_BILL_ACTIONS' and bcl.bill_action = fnd.lookup_code to translate the stored bill_action code into its MEANING.

The join structure differs by branch: the first branch filters btl.bsl_id IS NULL; the second joins through bsl where btl.bsl_id IS NOT NULL; the third selects sub-line amounts directly from bcl and bsl, returning null trx_class and trx_number.

Key Columns

  • ROW_ID — the rowid of the underlying OKS_BILL_CONT_LINES record, useful for locating the source row.
  • ID — the identifier of the billing contract line.
  • BILL_CLE_ID — the contract line (cle_id) to which the billing line is attached.
  • TRX_CLASS — the transaction class; derived via NVL from the transaction line or, failing that, the transaction header.
  • BILL_ACTION — the FND_LOOKUPS meaning for the OKS_BILL_ACTIONS lookup code; the human-readable billing action.
  • BILL_ON_DATE — the creation date of the billing contract line.
  • BILL_FROM_DATE / BILL_TO_DATE — the billed period boundaries (DATE_BILLED_FROM, DATE_BILLED_TO).
  • CURRENCY_CODE — the currency of the billing line.
  • TRX_NUMBER — the transaction number; a stored value of -99 is decoded to NULL via DECODE.
  • TAX_AMOUNT / TOTAL_AMOUNT — the transaction line tax amount and line amount; in the sub-line branch, TOTAL_AMOUNT is derived from bsl.amount as a character value, and TAX_AMOUNT is null.

Common Use Cases and Queries

Typical uses include billing history reports for a contract, reconciliation of billed periods against contract terms, and integration extracts that feed downstream finance or analytics systems. Because bill_action is resolved to a meaning, the view supports filtering by action description rather than code.

  • Billing history for a specific contract line: 
    SELECT bill_action, bill_from_date, bill_to_date, trx_number, total_amount
FROM   apps.oks_billing_hist_v
WHERE  bill_cle_id = :cle_id
ORDER  BY bill_on_date;
  • Billing activity by action within a date range: 
    SELECT bill_action, COUNT(*) cnt, SUM(total_amount) total
FROM   apps.oks_billing_hist_v
WHERE  bill_on_date BETWEEN :start_date AND :end_date
GROUP  BY bill_action;
  • Tax and amount detail for a currency: 
    SELECT trx_number, currency_code, tax_amount, total_amount
FROM   apps.oks_billing_hist_v
WHERE  currency_code = :currency AND tax_amount IS NOT NULL;

When querying, note the UNION ALL semantics: a given billing line may appear in more than one branch depending on the presence of transaction lines and sub-lines, so aggregation queries should consider whether duplicates are meaningful for the intended result. The view is read-only and cannot be updated or locked.