OM Renewal information 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.

/* Formatted on 7/8/2014 9:08:23 AM (QP5 v5.115.810.9015) */
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

===================    **********************    ================

/* Formatted on 7/8/2014 9:08:29 AM (QP5 v5.115.810.9015) */
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