Search Results oks_billing_hist_v




Overview
Explore in AI Chat

Overview

OKS_BILLING_HIST_V is a Service Contracts (OKS) reporting view owned by the APPS schema and delivered as a valid database object in Oracle EBS 12.1.1 and 12.2.2. It consolidates the billing history of service contracts into a single, denormalized read-only interface. The view joins billing transaction headers, billing transaction lines, contract billing lines, sub-line detail, and FND lookup translations, then unions the results across three distinct processing paths: standard billing lines, sub-line driven billing lines, and billing rows that carry no line-level amount or transaction number. This union-based design allows the view to present a complete billing picture regardless of whether a given charge was billed directly, through a sub-line hierarchy, or recorded only at the contract-billing-line level. Because the view exposes transaction numbers, transaction classes, currency codes, billing date ranges, and monetary amounts, it is a natural source for operational reporting, reconciliation, and integrations that need Service Contracts billing history without navigating the underlying normalized tables directly.

Underlying Base Objects

The documented base objects referenced by OKS_BILLING_HIST_V are FND_GLOBAL (PACKAGE), FND_LOOKUPS (VIEW), OKS_BILL_CONT_LINES (SYNONYM), OKS_BILL_SUB_LINES (SYNONYM), OKS_BILL_TRANSACTIONS (SYNONYM), and OKS_BILL_TXN_LINES (SYNONYM). OKS_BILL_TRANSACTIONS (aliased BTR) holds the billing transaction header, supplying the transaction class and transaction number when line-level values are absent. OKS_BILL_TXN_LINES (aliased BTL) holds the billing transaction line detail, supplying TRX_LINE_AMOUNT, TRX_LINE_TAX_AMOUNT, TRX_NUMBER, and TRX_CLASS. OKS_BILL_CONT_LINES (aliased BCL) is the contract billing line, providing the row identifier, line ID, contract line element ID, billing action, creation date, billed-from and billed-to dates, and currency code. OKS_BILL_SUB_LINES (aliased BSL) links sub-lines to their parent contract billing lines and drives the second branch of the union. FND_LOOKUPS translates BILL_ACTION codes using lookup type OKS_BILL_ACTIONS, and FND_GLOBAL supplies session context used by the view's security and lookup logic. The joins key on BTR.ID = BTL.BTN_ID, BTL.BCL_ID = BCL.ID, and BCL.BTN_ID = BTR.ID, with lookup translation applied through BILL_ACTION = FND.LOOKUP_CODE.

Key Columns

  • ROW_ID — the ROWID of the underlying OKS_BILL_CONT_LINES row; useful for uniquely identifying a billing line.
  • ID / BILL_CLE_ID — the billing line identifier and the contract line element identifier, linking the row back to the service contract structure.
  • TRX_CLASS — transaction class, resolved via NVL from the line then the header.
  • BILL_ACTION — the FND lookup meaning for the billing action code.
  • BILL_ON_DATE, BILL_FROM_DATE, BILL_TO_DATE — creation date and the billed service period boundaries.
  • CURRENCY_CODE — the currency of the billing line.
  • TRX_NUMBER — the transaction number; a value of -99 is converted to NULL by DECODE.
  • TAX_AMOUNT — TRX_LINE_TAX_AMOUNT from the billing transaction line.
  • TOTAL_AMOUNT — TRX_LINE_AMOUNT, the line-level billed amount that is the primary target of "total_amount" searches.

Common Use Cases and Queries

The most frequent requirement is to retrieve the billed total for a contract or period. A straightforward query filters by date range and contract line:

  • SELECT id, bill_cle_id, trx_number, currency_code, total_amount, tax_amount FROM apps.oks_billing_hist_v WHERE bill_on_date BETWEEN :p_from AND :p_to;
  • Aggregation by contract and currency: SELECT bill_cle_id, currency_code, SUM(total_amount) FROM apps.oks_billing_hist_v GROUP BY bill_cle_id, currency_code;
  • Reconciliation of taxed versus untaxed totals: SELECT trx_number, total_amount, tax_amount, (total_amount - NVL(tax_amount,0)) net_amount FROM apps.oks_billing_hist_v ORDER BY bill_on_date DESC;

Because the view unions billing rows that lack line-level amounts, consumers should use NVL or outer-style handling when summing TOTAL_AMOUNT across all rows, ensuring that contract-level-only billing entries are not silently excluded from totals.