Search Results oks_btl_pr_pk

Overview

OKS_BTL_PR is a table in the OKS schema (Service Contracts module) of Oracle E-Business Suite, documented as VALID in ETRM for releases 12.1.1 and 12.2.2. Its stated purpose is to hold the actual transaction amount and tax returned from Oracle Receivables (AR). In the Service Contracts billing flow, contract lines are priced and forwarded to AR for invoicing; OKS_BTL_PR captures the confirmed financial outcome of that round trip — the billed line amount and the associated tax amount — keyed back to the originating contract and billing structures. This makes it the transactional "results" table that bridges Service Contracts billing data with Receivables-returned figures, supporting reconciliation and reporting of billed versus expected amounts.

Under the heuristic Data Vault classification mined from its foreign-key structure, OKS_BTL_PR is modeled as a link. This is consistent with its design: it sits between parent billing entities and records the intersection of transaction identifiers plus the returned financial measures, rather than acting as an independent hub of business keys or a pure descriptive satellite.

Key Information Stored

The table contains 31 documented columns. The most significant are:

• ID — the surrogate primary key, enforced by OKS_BTL_PR_PK and also by the unique index OKS_BTL_PR_U1.
• BTN_ID, BSL_ID, BCL_ID — foreign keys to OKS_BTN_PR, OKS_BSL_PR, and OKS_BCL_PR respectively, anchoring each row to its billing transaction, billing schedule line, and billing contract line context.
• TRX_LINE_AMOUNT — the actual transaction line amount returned from AR.
• TRX_LINE_TAX_AMOUNT — the tax amount returned from AR for the corresponding line.
• BILL_INSTANCE_NUMBER — the billing instance identifier associating the row with a specific billing event.
• MANUAL_CREDIT — flag indicating whether the transaction resulted from a manual credit action.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, supporting multi-tenant/data-security partitioning.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the EBS framework.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO audit columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — the standard DFF (descriptive flexfield) extension columns.

The second unique index, OKS_BTL_PR_U2 on (BSL_ID, BCL_ID), is a business-key candidate, indicating that a billing schedule line / contract line pairing is expected to be unique within the table.

Common Use Cases and Queries

Typical uses include reconciling billed amounts and tax against contract expectations, reporting actual AR-returned revenue by contract line, and diagnosing billing discrepancies. A representative query joining to the parent structures:

• SELECT b.ID, b.BILL_INSTANCE_NUMBER, b.TRX_LINE_AMOUNT, b.TRX_LINE_TAX_AMOUNT, b.MANUAL_CREDIT FROM OKS_BTL_PR b WHERE b.BSL_ID = :bsl_id ORDER BY b.CREATION_DATE DESC;
• Aggregate actual billed amount and tax per billing transaction: SELECT BTN_ID, SUM(TRX_LINE_AMOUNT), SUM(TRX_LINE_TAX_AMOUNT) FROM OKS_BTL_PR GROUP BY BTN_ID;
• Join to OKS_BCL_PR and OKS_BSL_PR to compare billed versus scheduled amounts, and filter MANUAL_CREDIT = 'Y' to isolate manually credited transactions.

Related Objects

The FK structure identifies the principal related objects:

• OKS_BTN_PR — joined via OKS_BTL_PR.BTN_ID; parent billing transaction.
• OKS_BSL_PR — joined via OKS_BTL_PR.BSL_ID; billing schedule line.
• OKS_BCL_PR — joined via OKS_BTL_PR.BCL_ID; billing contract line.
• FND_SECURITY_GROUPS — joined via OKS_BTL_PR.SECURITY_GROUP_ID; security partitioning.
• OKS_BTL_PR_PK / OKS_BTL_PR_U1 / OKS_BTL_PR_U2 — primary and unique indexes governing row identity and the (BSL_ID, BCL_ID) business key.

These relationships position OKS_BTL_PR as the AR-returned financial detail layer beneath the Service Contracts billing hierarchy.