OKS Service Contracts Billing Schedule

Description
Categories: Enginatics, Kcapps
Repository: Github
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/ ... 
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/
   more
select
haouv.name operating_unit,
ocv.meaning class,
osclv.meaning category,
okhab.contract_number,
okhab.contract_number_modifier modifier,
osv0.meaning contract_status,
osv1.meaning line_status,
osv2.meaning subline_status,
oklb1.line_number||nvl2(oklb2.line_number,'.'||oklb2.line_number,null) line_number,
xxen_util.meaning(nvl(olsb2.lty_code,olsb1.lty_code),'OKC_LINE_TYPE',0)||' '||xxen_util.meaning(okslb1.usage_type,'OKS_USAGE_TYPES',0) contract_line_type,
okslb2.base_reading,
okslb1.usage_period,
rr.name invoice_rule,
rr2.name accounting_rule,
oslb.sequence_no stream_level_sequence,
oslb.level_periods,
oslb.uom_per_period,
oslb.uom_code,
oslb.start_date stream_level_start_date,
oslb.end_date stream_level_end_date,
to_number(ole.sequence_number) level_sequence,
ole.date_start bill_from,
ole.date_end bill_to,
ole.date_to_interface interface_date,
ole.date_completed,
ole.amount,
okhab.scs_code,
okslb1.usage_type,
olsb1.lty_code,
xxen_util.user_name(ole.created_by) created_by,
xxen_util.client_time(ole.creation_date) creation_date,
xxen_util.user_name(ole.last_updated_by) last_updated_by,
xxen_util.client_time(ole.last_update_date) last_update_date
from
hr_all_organization_units_vl haouv,
okc_k_headers_all_b okhab,
okc_subclasses_v osclv,
okc_classes_v ocv,
okc_statuses_v osv0,
okc_statuses_v osv1,
okc_statuses_v osv2,
okc_k_lines_b oklb1,
(select oklb.* from okc_k_lines_b oklb where '&show_subline'='Y') oklb2,
oks_k_lines_b okslb1,
oks_k_lines_b okslb2,
okc_line_styles_b olsb1,
okc_line_styles_b olsb2,
oks_stream_levels_b oslb,
oks_level_elements ole,
ra_rules rr,
ra_rules rr2
where
1=1 and
okhab.authoring_org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) and
haouv.organization_id=okhab.authoring_org_id and
okhab.scs_code=osclv.code(+) and
osclv.cls_code=ocv.code(+) and
okhab.sts_code=osv0.code(+) and
oklb1.sts_code=osv1.code(+) and
oklb2.sts_code=osv2.code(+) and
okhab.id=oklb1.chr_id and
oklb1.id=oklb2.cle_id(+) and
oklb1.id=okslb1.cle_id and
oklb2.id=okslb2.cle_id(+) and
oklb1.lse_id=olsb1.id and
oklb2.lse_id=olsb2.id(+) and
oslb.id=ole.rul_id and
oklb1.inv_rule_id=rr.rule_id(+) and
okslb1.acct_rule_id=rr2.rule_id(+)
order by
haouv.name,
okhab.scs_code,
okhab.contract_number,
okhab.contract_number_modifier,
line_number,
oslb.sequence_no,
to_number(ole.sequence_number)
Parameter Name SQL text Validation
Operating Unit
haouv.name=:operating_unit
LOV
Contract Number
okhab.contract_number=:contract_nunber
LOV
Modifier
okhab.contract_number_modifier=:modifier
LOV
Contract Status
osv0.meaning=:contract_status
LOV
Exclude Contract Status
osv0.meaning<>:exclude_contract_status
LOV
Interface Date From
ole.date_to_interface>=:interface_date_from
Date
Interface Date To
ole.date_to_interface<:interface_date_to+1
Date
Show unbilled only
ole.date_completed is null
LOV
Level
oklb1.id=oslb.cle_id
LOV