OKS Service Contracts Billing History – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

Service Contracts billing history with invoicing and accounting rules, bill action, billed period dates, amounts and counter reading details for usage billing. When running service contracts billing, there is always a full set of records created in the following billing history tables:

oks_bill_cont_lines obcl oks_bill_sub_lines obsl oks_bill_sub_line_dtls obsld oks_bill_transactions obt oks_bill_txn_lines obtl

This set of records is complete down to subline level obsl/obsld, regardless if the billed contract line type has a subline or not. For subscription lines (lse_id=46, lty_code=’SUBSCRIPTION’) without a subline, for example, both obcl and obsl point their cle_id to the same line in okc_k_lines_b instead of different line and subline.

Unique identifier for the billing entry is obtl.bill_instance_number, which links to receivables line rctla.interface_line_attribute3. When driving queries from the OKS side, make sure to include a to_char() conversion for the numeric obtl.bill_instance_number, to enable index use on character type rctla.interface_line_attribute3.

The OKS billing history does not have a link to the originating billing schedule record in oks_level_elements (see https://www.enginatics.com/reports/oks-service-contracts-billing-schedule/)

An overview of oracle service contracts and other line types can be found here: https://www.enginatics.com/reports/okc-contract-lines-summary/

oks_billing_history_v

Report Parameters

Operating Unit, Contract Number, Modifier, Contract Status, Exclude Contract Status, Billed From, Billed To, Bill Action

Oracle EBS Tables Used

hr_all_organization_units_vl, okc_k_headers_all_b, okc_subclasses_v, okc_classes_v, okc_statuses_v, okc_k_lines_b, oks_k_lines_b, okc_line_styles_b, ra_rules, oks_bill_cont_lines, oks_bill_sub_lines, oks_bill_sub_line_dtls, &counter_tbls, oks_bill_txn_lines, ra_customer_trx_lines_all, ra_customer_trx_all, ra_cust_trx_types_all, mo_glob_org_access_tmp, dual, okc_subclasses_b

Report Categories

Enginatics

OKS Service Contracts Billing Schedule, AR Transactions and Lines 11i, OKC Contract Lines Summary, GL Account Distribution Analysis, OKL Termination Quotes, GL Account Analysis (Distributions)

Running This SQL Without Blitz Report

Some Oracle EBS SQL reports in this library require functions from the utility package xxen_util. Install it before running the SQL directly against your Oracle EBS database.

Download & Import Options

Resource Link
Excel Example Output OKS Service Contracts Billing History 14-Oct-2020 080331.xlsx
Blitz Report™ XML Import OKS_Service_Contracts_Billing_History.xml
Full SQL on Enginatics www.enginatics.com/reports/oks-service-contracts-billing-history/

OKS Service Contracts Billing History - Case Study & Technical Analysis

Executive Summary

The OKS Service Contracts Billing History report provides a detailed audit trail of all billing transactions generated by the Service Contracts module. It links the contract lines to the specific AR Invoices created, allowing for full reconciliation between the Service and Finance departments.

Business Challenge

Discrepancies between what was promised (Contract) and what was billed (Invoice) are common sources of customer disputes.

Solution

The OKS Service Contracts Billing History report joins OKS billing tables with AR tables.

Key Features:

Technical Architecture

The report queries the OKS billing history tables and links them to AR.

Key Tables and Views

Core Logic

  1. History Retrieval: Queries the OKS_BILL_% tables which store the history of the “Service Contracts Main Billing” concurrent program.
  2. Linking: Uses the BILL_INSTANCE_NUMBER (in OKS) to match with INTERFACE_LINE_ATTRIBUTE3 (in AR) to find the corresponding invoice.
  3. Detailing: Joins to counter tables if the line type is Usage.

Business Impact

© 2026 Enginatics