Renewal: The renewal information given in the renewal tab is stored in the OKC_RULES_B table where rule_information_category=’REN’.
Rule_information1 stores the Renewal Type.
Rule_information3 stores the Renewal duration.
Billing schedules: Billing schedule information is stored in OKS_LEVEL_ELEMENTS table.
For Billing Schedule records. The rule_information_category of OKC_RULES_B is ‘SLL’.
Sample Program: This program will give the billing schedule information for the contracts.
SELECT okch.contract_number Contract_Number,
okcl.id,
hzp.party_name Customer_Name,
rul_inv.rule_information1 Invoice_Text,
TO_DATE(TO_CHAR(bill.date_transaction,'DD-MON-YYYY')) Invoice_Date,
okcl.currency_code Currency,
okch.attribute1 Project_Number,
bill.date_start Bill_From,
DECODE(upper(rul.object1_id1),'DAY',(bill.date_start+TO_NUMBER(rul.rule_information4)-1)
,'WK',(bill.date_start+TO_NUMBER(rul.rule_information4)*7-1)
,'MTH',(ADD_MONTHS(bill.date_start,TO_NUMBER(rul.rule_information4))-1)
,'BA',(ADD_MONTHS(bill.date_start,6*TO_NUMBER(rul.rule_information4))-1)
,'QTR',(ADD_MONTHS(bill.date_start,3*TO_NUMBER(rul.rule_information4))-1)
,'YR',(ADD_MONTHS(bill.date_start,12*TO_NUMBER(rul.rule_information4))-1)
) Bill_To,
bill.amount Amount
FROM okc.okc_k_party_roles_b rol,
ar.hz_parties hzp,
okc.okc_rules_b rul,
okc.okc_rules_b rul_inv,
okc.okc_rule_groups_b rlgp,
okc.okc_statuses_b okcs,
okc.okc_k_lines_b okcl,
okc.okc_k_headers_b okch,
apps.oks_level_elements bill
WHERE 1=1
AND hzp.party_id = rol.object1_id1
AND rol.chr_id = okch.id
AND rol.rle_code = 'CUSTOMER'
AND okcs.ste_code != 'ENTERED'
AND okcs.code = okch.sts_code
AND okch.attribute2 != 'Warranty'
AND okcl.chr_id = okch.id
AND rlgp.cle_id = okcl.id
AND rul.rgp_id = rlgp.id
AND rul.rule_information_category = 'SLL'
AND rul_inv.rule_information_category = 'IRT'
AND rul_inv.rgp_id = rlgp.id
AND bill.rul_id = rul.id
SELECT
okch.contract_number,
okch.contract_number_modifier Modifier,
okch.id,
okch.attribute1 Project_Number,
okch.attribute2 Contract_Type,
okch.scs_code,
okch.sts_code,
hzp.party_name Customer_Name,
hzp.party_id,
okcl1.lse_id,
--decode(okcl1.lse_id,1,'Maintanance',12,'Usage',14,'Warranty',19,'Extended') Contract_Type,
mtl.segment1 Product_Number,
mtl.description Product_Name,
item.number_of_items Qty,
okcl_cov.start_Date Product_Start_Date,
okcl_cov.end_date Product_End_Date,
okcl_cov.price_negotiated Amount,
okcl_cov.currency_code Currency,
okch.start_date Contract_Start_date,
okch.end_date Contract_end_date,
csi.serial_number Serial_Number,
okctl.name Coverage_Name,
bus.bus_process_name Business_process_name,
time_v.day_of_week,
time_start.hour||':'||time_start.minute||' - '||time_end.hour||':'||time_end.minute Coverage_time,
ori.duration Reaction_Time
FROM okc.okc_k_headers_b okch,
okc.okc_k_lines_b okcl_cov, -- For Covered products
okc.okc_k_lines_b okcl1, -- For Contract Types
okc.okc_k_lines_b okcl2,
okc.okc_k_lines_b okcl3,
okc.okc_k_lines_b okcl4,
okc.okc_k_lines_tl okctl, -- Coverage Nmae.
apps.oks_bus_processes_v bus, -- Business process Names.
okc.okc_react_intervals ori, -- Reaction times Link table
okc.okc_timevalues_b time_v, -- Reaction Times.
okc.okc_timevalues_b time_cov, -- Coverage Times
okc.okc_timevalues_b time_start,
okc.okc_timevalues_b time_end,
okc.okc_rules_b rul, -- rule group for reaction times
okc.okc_rules_b rul_cov, -- rule group for coverage times
apps.okx_incident_severits_v okx,
okc.okc_rule_groups_b rlgp, -- rule group for reaction times
okc.okc_rule_groups_b rlgp_cov, -- rule group for coverage times
okc.okc_k_items item,
inv.mtl_system_items_b mtl,
csi.csi_item_instances csi,
okc.okc_cover_times cvr,
okc.okc_k_party_roles_b rol,
ar.hz_parties hzp
WHERE 1=1
and okx.id1 = rul.object1_id1
and okx.name = 'High'
and (time_v.day_of_week is null or time_start.day_of_week = time_v.day_of_week)
and time_v.id = ori.tve_id
and time_v.dnz_chr_id = okch.id -- time_v : For reaction times.
and ori.rul_id = rul.id
and rul.rule_information_category = 'RCN'
and rul.rgp_id = rlgp.id
and rlgp.rgd_code = 'SVC_K'
and rlgp.cle_id = okcl4.id
and okcl4.cle_id(+) = okcl3.id
and okcl4.dnz_chr_id = okch.id -- okcl4 : For Reaction times
and time_start.day_of_week = time_end.day_of_week
and time_end.id = time_cov.tve_id_ended
and time_end.dnz_chr_id = okch.id
and time_start.id = time_cov.tve_id_started
and time_start.dnz_chr_id = okch.id
and time_cov.id = cvr.tve_id
and time_cov.dnz_chr_id = okch.id -- time_cov : Coverage Times
and cvr.rul_id = rul_cov.id -- cvr : Link Table for coverage times.
and rul_cov.rgp_id = rlgp_cov.id
and rlgp_cov.rgd_code = 'SVC_K'
and rlgp_cov.cle_id = okcl3.id
and bus.id = okcl3.id
and okcl3.cle_id = okcl2.id -- okcl3 : For Business Process
and okcl3.dnz_chr_id = okch.id
and okctl.id = okcl2.id
and okctl.language = userenv('LANG')
and okcl2.lse_id in (2,15,20) -- okcl2 : For Coverages
and okcl2.cle_id = okcl1.id
and okcl2.dnz_chr_id = okch.id
and mtl.inventory_item_id = csi.inventory_item_id
and mtl.organization_id = csi.inv_master_organization_id
and csi.instance_id = item.object1_id1
and item.jtot_object1_code = 'OKX_CUSTPROD'
and item.cle_id = okcl_cov.id
and okcl_cov.cle_id = okcl1.id -- okcl_cov : For Cover Products
and okcl1.chr_id = okch.id -- okcl1 : For Contract Types
and hzp.party_id = rol.object1_id1
and rol.rle_code='CUSTOMER'
and rol.chr_id = okch.id
No comments:
Post a Comment