Results for “oks_bill_transactions”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The OKS_BILL_TRANSACTIONS table is a core billing data structure in the Oracle Service Contracts (OKS) module. It stores the financial transaction — invoice, credit memo, debit memo, or similar receivable document — that is eventually received back from Oracle Receivables (AR) after a service contract is billed. In effect, it acts as the OKS-side landing table that captures the AR-originated billing document so that Service Contracts can reconcile the contract line it billed against the actual receivable document that AR generated.

The table resides in the OKS schema and is documented as VALID in ETRM for both Oracle EBS 12.1.1 and 12.2.2. Its primary key is OKS_BILL_TRANSACTIONS_PK, defined on the ID column. A unique index, OKS_BILL_TRANSACTIONS_U1, is also defined on ID, marking ID as the sole documented business-key candidate (in practice it is a surrogate/identity key rather than a natural business key).

Based on the foreign key structure, the ETRM metadata classifies this object as hub-leaning in Data Vault terms — a heuristic modeling suggestion that positions OKS_BILL_TRANSACTIONS as a central hub entity around which transaction-line and contract-line satellites are attached. This reflects its role as the parent record for billing transaction detail and its single outward reference to FND_SECURITY_GROUPS.

Key Information Stored

Of the 13 documented columns, the most operationally significant are:

• ID — Surrogate primary key (OKS_BILL_TRANSACTIONS_PK) and the value propagated to child tables.
• TRX_NUMBER — The AR transaction number (invoice/credit document number) returned from Receivables.
• TRX_DATE — The transaction date of the AR document.
• TRX_AMOUNT — The monetary amount of the billing transaction.
• TRX_CLASS — The AR transaction class (e.g., invoice, credit memo, debit memo) distinguishing the nature of the document.
• CURRENCY_CODE — The currency in which the receivable transaction was recorded.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, supporting multi-tenant / security-group data isolation.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by the OA Framework.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard Oracle who-columns providing audit lineage.

The surrogate key (ID) should be distinguished from business identifiers such as TRX_NUMBER, which carries the externally meaningful AR document reference. The documented schema does not include a contract or contract-line identifier directly in this table; linkage to contract lines is achieved through the child tables described below.

Common Use Cases and Queries

Typical reporting scenarios include reconciling Service Contracts billing events to AR invoices, auditing credit memos issued against service contracts, and tracing a transaction number back to its contract lines.

• Retrieve all transactions for a given AR document: SELECT id, trx_number, trx_date, trx_amount, trx_class FROM oks_bill_transactions WHERE trx_number = :trx_number;
• Aggregate amounts by transaction class: SELECT trx_class, SUM(trx_amount) FROM oks_bill_transactions GROUP BY trx_class;
• Join to detail lines: SELECT bt.trx_number, tl.* FROM oks_bill_transactions bt, oks_bill_txn_lines tl WHERE bt.id = tl.btn_id;
• Join to contract lines: SELECT bt.trx_number, cl.* FROM oks_bill_transactions bt, oks_bill_cont_lines cl WHERE bt.id = cl.btn_id;
• Filter by date range for period-end reconciliation using TRX_DATE.

These queries support both operational troubleshooting (why did an AR document not match a contract?) and periodic financial reconciliation between OKS and AR.

Related Objects

The following objects are most directly related, based on documented FK and PK relationships:

• OKS_BILL_TXN_LINES — Child table; joins via OKS_BILL_TXN_LINES.BTN_ID → OKS_BILL_TRANSACTIONS.ID.
• OKS_BILL_CONT_LINES — Child table; joins via OKS_BILL_CONT_LINES.BTN_ID → OKS_BILL_TRANSACTIONS.ID.
• FND_SECURITY_GROUPS — Referenced through OKS_BILL_TRANSACTIONS.SECURITY_GROUP_ID.
• OKS_BILL_TRANSACTIONS_PK / OKS_BILL_TRANSACTIONS_U1 — Primary key constraint and unique index on ID.

Together these relationships establish OKS_BILL_TRANSACTIONS as the hub that unifies AR transaction headers with their Service Contracts detail and contract-line contexts.