OKS Service Contracts Billing Schedule – Oracle EBS SQL Report
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Service Contracts · all Service Contracts reports
Overview
Service Contracts billing schedule with invoicing and accounting rules, and detailed to be billed period dates and amounts from the stream level elements table oks_level_elements.
Column date_competed is used to identify open or already billed records and date_to_interface is used by the service contracts billing program to identify the records to be billed at any given date. For advance billing, date_to_interface is set to the beginning of the billing period and for arrears, it is set to the end. When creating new billing schedule record for past periods (that should have been billed already), date_to_interface is set to the current date.
An overview of oracle service contracts and other line types can be found here: https://www.enginatics.com/reports/okc-contract-lines-summary/?utm_source=oraclereporting.com&utm_medium=referral&utm_campaign=oracle-ebs-sql&utm_content=oks-service-contracts-billing-schedule
Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.
Report Parameters
Operating Unit, Contract Number, Modifier, Contract Status, Exclude Contract Status, Interface Date From, Interface Date To, Show unbilled only, Level
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, oks_stream_levels_b, oks_level_elements, ra_rules, xxen_report_org_access
Report Categories
Related Reports
OKS Service Contracts Billing History, OKC Contract Lines Summary, OKS Service Contract Line Upload, AR Transactions and Lines 11i, OKL Termination Quotes, OKS Contract Termination Upload, GL Account Distribution Analysis
Running This SQL Without Blitz Report
The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.
Download & Import Options
| Resource | Link |
|---|---|
| Excel Example Output | OKS Service Contracts Billing Schedule 07-Jun-2020 014328.xlsx |
| Blitz Report™ XML Import | OKS_Service_Contracts_Billing_Schedule.xml |
| SQL Source | OKS Service Contracts Billing Schedule.sql |
| Full Report on Enginatics | www.enginatics.com/reports/oks-service-contracts-billing-schedule/ |
OKS Service Contracts Billing Schedule - Case Study & Technical Analysis
Executive Summary
The OKS Service Contracts Billing Schedule report looks forward (and backward) at the billing schedule defined for a contract. Unlike the “Billing History” report which shows what was billed, this report shows what is scheduled to be billed. It is the primary tool for forecasting future service revenue.
Business Challenge
Finance needs to know future cash flow from service contracts.
- Forecasting: “What is our projected billing for the ‘Maintenance’ category next quarter?”
- Validation: “We just booked a 3-year contract. Did the system generate the correct quarterly billing schedule?”
- Unbilled Revenue: “Which periods have passed but haven’t been billed yet?”
Solution
The OKS Service Contracts Billing Schedule report exposes the OKS_LEVEL_ELEMENTS table.
Key Features:
- Schedule Visibility: Lists every future billing event (Date, Amount) for the life of the contract.
- Status: Shows whether a scheduled event has been “Interfaced” (billed) or is still “Open”.
- Rules: Displays the Invoicing Rule (e.g., Advance/Arrears) and Accounting Rule.
Technical Architecture
The report queries the stream level elements in OKS.
Key Tables and Views
OKS_LEVEL_ELEMENTS: The core table storing the individual billing installments.OKS_STREAM_LEVELS_B: Defines the billing frequency and rules.OKS_K_LINES_B: The contract line details.
Core Logic
- Schedule Generation: When a contract is authorized, OKS generates the billing schedule in
OKS_LEVEL_ELEMENTS. - Reporting: The report queries this table.
- Status Check: It checks the
DATE_COMPLETEDcolumn to determine if a specific installment has been processed.
Business Impact
- Cash Flow Management: Provides accurate data for cash flow forecasting.
- Operational Control: Helps identify “stuck” billing schedules that are not being picked up by the billing program.
- Customer Transparency: Allows providing customers with a schedule of future payments.
Useful Links
- Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
- Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
- Oracle EBS Reporting Toolkits by Blitz Report™
- Blitz Report™ FAQ & Community Q&A
- Supply Chain Hub by Blitz Report™
- Blitz Report™ Customer Case Studies
- Oracle EBS Reporting Blog
- Oracle EBS Reporting Resource Centre
© Enginatics GmbH