Search Results oks_bsl_pr_u1

Overview

OKS.OKS_BSL_PR is a transactional table in the Service Contracts (OKS) schema that stores billing information for contract sub lines. It is populated by the Service Contracts Main Billing concurrent program when that program is executed in preview mode. Each row captures the start date and end date of a billing period along with the amount charged for that period. The records produced in this table are primarily consumed to generate custom reports, making OKS_BSL_PR an intermediate staging and reporting artifact rather than a live operational ledger.

The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10. It is classified as VALID, carries FND Design Data under OKS.OKS_BSL_PR, and is registered against the standard EBS audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, OBJECT_VERSION_NUMBER). Its Data Vault classification, derived heuristically from the foreign key structure, is hub-leaning: the table centers on a stable surrogate identifier (ID) that business entities reference, which suggests modeling it as a hub with associated referencing satellites or links in a Data Vault representation.

Key Information Stored

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

• ID — Surrogate primary key (OKS_BSL_PR_PK) and the column behind the unique index OKS_BSL_PR_U1. It represents the bill sub line identifier.
• BCL_ID — Identifier of the contract line billing record; references OKS_BILL_CONT_LINES.ID.
• CLE_ID — Contract line identifier; references OKC_K_LINES_B.ID. Indexed non-uniquely via OKS_BSL_PR_N1.
• AMOUNT — Billing amount for the period associated with the sub line. For most sub lines it is populated from OKS_LEVEL_ELEMENTS.AMOUNT; for usage lines of type 'Fixed per period', 'Actual by quantity', and 'Actual per period' it is derived from the pricing engine.
• DATE_BILLED_FROM — Start date of the billing period; for regular invoices populated from OKS_LEVEL_ELEMENTS.DATE_START.
• DATE_BILLED_TO — End date of the billing period.
• OBJECT_VERSION_NUMBER — Sequential number set to 1 on insert and incremented on update, used by APIs for optimistic concurrency control.
• CREATED_BY / CREATION_DATE / LAST_UPDATED_BY / LAST_UPDATE_DATE / LAST_UPDATE_LOGIN — Standard EBS audit trail columns.
• SECURITY_GROUP_ID — References FND_SECURITY_GROUPS, supporting multi-tenant data isolation via the security group model.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — DFF-enabled descriptive flexfield columns for extensibility.
• DATE_TO_INTERFACE — Date indicating when the record was made available to downstream interface processing.
• MANUAL_CREDIT — Flag recording manual credit adjustments applied to the sub line billing record.
• AVERAGE — Documented as not used.

The surrogate primary key is ID; the only unique index (OKS_BSL_PR_U1) is on ID, so no separate business-key candidate is documented. BCL_ID and CLE_ID are non-unique foreign key indexes (OKS_BSL_PR_N2 and OKS_BSL_PR_N1 respectively).

Common Use Cases and Queries

Typical scenarios include reconciling preview-mode billing output against actual invoiced amounts, validating period coverage, and driving custom billing reports.

• List all billing sub lines for a contract line, ordered by billing period:

  SELECT id, bcl_id, cle_id, amount,
         date_billed_from, date_billed_to
  FROM   oks.oks_bsl_pr
  WHERE  cle_id = :p_cle_id
  ORDER  BY date_billed_from;

• Aggregate billed amounts per period:

  SELECT date_billed_from, date_billed_to,
         SUM(amount) total_billed
  FROM   oks.oks_bsl_pr
  GROUP  BY date_billed_from, date_billed_to;

• Join to contract lines to display line-level detail:

  SELECT b.id, b.amount, l.line_number
  FROM   oks.oks_bsl_pr b,
         okc.okc_k_lines_b l
  WHERE  b.cle_id = l.id;

Record-level lookups by the unique key should use ID, which is served by OKS_BSL_PR_U1. Filtering by BCL_ID or CLE_ID benefits from the non-unique indexes. Because the table is populated only in preview mode, queries should be scoped to a preview run window (for example, using CREATION_DATE) to avoid mixing results across runs.

Related Objects

• OKS.OKS_BCL_PR — Referenced by BCL_ID; holds contract line billing records.
• OKC.OKC_K_LINES_B — Referenced by CLE_ID; the base contract lines table.
• FND_SECURITY_GROUPS — Referenced by SECURITY_GROUP_ID; governs security group access.
• OKS.OKS_BSD_PR — References this table through BSL_ID and BSL_ID_AVERAGED; holds billing sub line distribution/detail records.
• OKS.OKS_BTL_PR — References this table through BSL_ID; holds billing transaction line records.
• OKS.OKS_LEVEL_ELEMENTS — Source of AMOUNT, DATE_START, and related period values during main billing processing.

These relationships confirm that OKS_BSL_PR functions as a parent hub for billing sub line detail (BSD) and transaction line (BTL) children, while itself depending on contract line billing (BCL) and contract line (CLE) parents.