OKS Service Contracts Billing History

Description

Categories
Enginatics
Repository
GitHub
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

SQL 110 lines

Parameters 8

ParameterTypeList of values SQL
Operating UnitLOV
haouv.name=:operating_unit
Contract NumberLOV
okhab.contract_number=:contract_nunber
ModifierLOV
okhab.contract_number_modifier=:modifier
Contract StatusLOV
osv0.meaning=:contract_status
Exclude Contract StatusLOV
osv0.meaning<>:exclude_contract_status
Billed FromDate
obsl.creation_date>=:billed_from
Billed ToDate
obsl.creation_date<:billed_to+1
Bill ActionLOV
obcl.bill_action=xxen_util.lookup_code(:bill_action,'OKS_BILL_ACTIONS',0)