Wednesday, August 22, 2012

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

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect