OM Query

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