Search Results ar_summary_transactions

Overview

OKS.OKS_BTL_PR is a transient staging table within the Oracle E-Business Suite Service Contracts (OKS) schema that stores billing transactions produced during a preview run of the Service Contracts Main Billing program. When that concurrent program is executed in preview mode, it writes summarized or detailed billing lines into this table so that users can inspect the anticipated billing output before committing it to Oracle Receivables. The granularity of the rows — contract line versus contract sub line — is governed by the profile option AR_SUMMARY_TRANSACTIONS.

The table resides in the APPS_TS_TX_DATA tablespace with a PCTFREE of 10, and its indexes are held in APPS_TS_TX_IDX. Because it functions as a preview buffer rather than a permanent transactional store, its contents are typically purged or regenerated on each preview execution. Based on the foreign key topology — OKS_BTL_PR references OKS_BCL_PR, OKS_BSL_PR, and OKS_BTN_PR, which are themselves distinct billing records — the object exhibits the characteristics of a link table under a heuristic Data Vault classification. This classification should be treated as a modeling suggestion rather than a documented designation.

Key Information Stored

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

• ID — the surrogate primary key (index OKS_BTL_PR_U1), uniquely identifying each preview billing transaction row.
• BSL_ID and BCL_ID — foreign keys to OKS_BSL_PR and OKS_BCL_PR respectively. Together they form the unique business key enforced by index OKS_BTL_PR_U2. BSL_ID is populated only for detail (sub line level) transactions.
• BTN_ID — foreign key to OKS_BTN_PR, the parent bill transaction record.
• BILL_INSTANCE_NUMBER — a numeric unique identifier generated by sequence and indexed non-uniquely by OKS_BTL_PR_N3.
• OBJECT_VERSION_NUMBER — optimistic locking counter, set to 1 on insert and incremented on update by APIs.
• TRX_LINE_AMOUNT — documented as obsolete; TRX_LINE_TAX_AMOUNT is documented as not used. Both are retained for structural compatibility.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, supporting multi-tenant data isolation.
• MANUAL_CREDIT — indicates manually applied credit adjustments.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard WHO audit columns present on nearly all EBS transactional tables.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 — the descriptive flexfield segment columns, with ATTRIBUTE1 and ATTRIBUTE2 sized at 450 characters.

Common Use Cases and Queries

The primary use case is validating billing output prior to an actual Revenue Recognition or Receivables interface run. Consultants and support analysts query the table to confirm amounts, contract line attribution, and bill instance numbering before the preview is finalized. A typical diagnostic query joins the preview rows to their parent billing records:

• SELECT b.ID, b.BILL_INSTANCE_NUMBER, b.BCL_ID, b.BSL_ID, c.AMOUNT FROM OKS.OKS_BTL_PR b, OKS.OKS_BCL_PR c WHERE b.BCL_ID = c.ID AND b.BTN_ID = :p_btn_id;
• Filtering by BCL_ID via index OKS_BTL_PR_N2 to isolate a single contract line's preview transactions.
• Filtering by BILL_INSTANCE_NUMBER via index OKS_BTL_PR_N3 when reconciling a specific billing run.
• Detecting orphaned or duplicate rows by inspecting for null BSL_ID on detail-level runs or by grouping on BCL_ID and BSL_ID to catch violations of the U2 unique constraint.

Because the table is a preview artifact, it is not a reliable source for historical financial reporting; Receivables and the contract billing line tables should be used for those purposes.

Related Objects

• OKS.OKS_BCL_PR — contract line billing records; joined on BCL_ID.
• OKS.OKS_BSL_PR — sub line billing records; joined on BSL_ID for detail-level transactions.
• OKS.OKS_BTN_PR — parent bill transaction records; joined on BTN_ID.
• FND.FND_SECURITY_GROUPS — security group definitions; joined on SECURITY_GROUP_ID.
• Service Contracts Main Billing concurrent program — the process that populates this table in preview mode.
• OKS_BCL_PR, OKS_BSL_PR, OKS_BTN_PR — the transactional tables whose committed output supersedes this staging data.