Meaning of LSE_ID in Oracle Service contract

LSE_ID	Usage (Meaning )
1	Service
2	Coverage
3	Business Process for Service
4	Reaction Time
5	Billing Type
6	Billing Rate
7	Covered Item
8	Covered Party
9	Covered Product
10	Covered Site
11	Covered System
12	Usage
13	Installed Item
14	Warranty
15	Coverage 16 Business Process for Warranty
16	Business Process for Warranty
17	Reaction Time
18	Covered Product
19	Extended Warranty
20	Coverage
21	Business Process for Warranty
22	Reaction Time
23	Billing Type
24	Billing Rate
25	Covered Product
35	Covered Customer
59	Billing Type
66	Coverage

API to swap Service contract for Newly created Install Base with Old install base

DECLARE
   p_transaction_type            VARCHAR2(50);
   p_instance_id                 NUMBER;
   p_new_instance_id             NUMBER;
   p_vld_org_id                  NUMBER;
   p_quantity                    NUMBER;
   p_party_account_id1           NUMBER;
   p_party_account_id2           NUMBER;
   p_transaction_date            DATE;
   p_source_transaction_date     DATE;
   p_transaction_id              NUMBER;
   p_grp_call_contracts          VARCHAR2(1);
   p_txn_type_id                 NUMBER;
   p_system_id                   NUMBER;
   p_order_line_id               NUMBER;
   p_call_from_bom_expl          VARCHAR2(1);
   lp_oks_txn_inst_tbl           oks_ibint_pub.txn_instance_tbl;
   lx_return_status              VARCHAR2(1);
   lx_msg_count                  NUMBER;
   lx_msg_data                   VARCHAR2(500);
   l_msg_index_out               NUMBER;
   l_return_msg                  VARCHAR2(32600);
   k_rpl_tbl                     oks_extwarprgm_pvt.contract_tbl;

   CURSOR get_k_for_rpl_csr
   IS
        SELECT tmp.old_customer_product_id instance_id
             , tmp.termination_date
             , tmp.installation_date
             , tmp.transaction_date
             , tmp.old_customer_acct_id
             , tmp.new_customer_acct_id
             , tmp.system_id
             , tmp.old_quantity
             , tmp.new_quantity
             , tmp.new_customer_product_id
             , ki.cle_id subline_id
             , ki.dnz_chr_id
             , kh.start_date hdr_sdt
             , kh.end_date hdr_edt
             , kh.sts_code hdr_sts
             , kl.cle_id
             , kl.price_negotiated
             , kl.start_date
             , kl.end_date
             , kl.sts_code prod_sts
             , kl.cust_acct_id
             , tl.start_date srv_sdt
             , tl.end_date srv_edt
             , kh.sts_code
             , kh.contract_number
             , ki.number_of_items
             , tl.price_negotiated
             , kl.date_terminated
             , tmp.old_inventory_item_id
             , kh.authoring_org_id
             , kh.inv_organization_id
             , kl.lse_id
             , kh.scs_code
             , tmp.new_customer_product_id
             , kis.object1_id1
             , tl.currency_code
             , tmp.old_unit_of_measure
             , kl.line_renewal_type_code
             , tmp.raise_credit
             , NULL
             , okl.tax_amount
             , kl.price_unit
             , kl.name
             , kl.item_description
             , kl.upg_orig_system_ref
             , kl.upg_orig_system_ref_id
             , tmp.new_inventory_item_id
             , tmp.return_reason_code
             , tmp.order_line_id
             , okl.price_uom
             , okl.toplvl_uom_code
             , okl.toplvl_price_qty
          FROM okc_k_items ki
             , okc_k_headers_all_b kh
             , okc_k_lines_v kl
             , okc_statuses_b st
             , oks_instance_temp tmp
             , okc_k_lines_b tl
             , okc_k_items kis
             , oks_k_lines_b okl
         WHERE tmp.rpl = 'Y'
           AND ki.object1_id1 = TO_CHAR( tmp.old_customer_product_id)
           AND ki.jtot_object1_code = 'OKX_CUSTPROD'
           AND ki.dnz_chr_id = kh.id
           AND kh.scs_code IN ('WARRANTY', 'SERVICE', 'SUBSCRIPTION')
           AND ki.cle_id = kl.id
           AND tl.id = kl.cle_id
           AND kis.cle_id = tl.id
           AND kis.dnz_chr_id = tl.dnz_chr_id
           AND kl.sts_code = st.code
           AND st.ste_code NOT IN ('TERMINATED', 'CANCELLED', 'HOLD')
           AND kl.date_terminated IS NULL
           AND okl.cle_id = kl.id
           AND kh.template_yn = 'N'
           AND ((TRUNC( tmp.transaction_date) <= TRUNC( kl.end_date)
             AND TRUNC( tmp.transaction_date) >= TRUNC( kl.start_date))
             OR (TRUNC( tmp.transaction_date) <= TRUNC( kl.start_date)))
      ORDER BY tmp.old_customer_product_id
             , kh.creation_date;
BEGIN
   --   lp_oks_txn_inst_tbl( 1).old_customer_product_id          := 4367640;
   apps.csi_item_instance_pvt.call_to_contracts(
  p_transaction_type                        => 'RPL'
, p_instance_id                             => 4367640
, p_new_instance_id                         => 5302910
, p_vld_org_id                              => 103
, p_quantity                                => NULL
, p_party_account_id1                       => NULL
, p_party_account_id2                       => NULL
, p_transaction_date                        => SYSDATE
, p_source_transaction_date                 => SYSDATE
, p_grp_call_contracts                      => fnd_api.g_false
, p_oks_txn_inst_tbl                        => lp_oks_txn_inst_tbl
, x_return_status                           => lx_return_status
, x_msg_count                               => lx_msg_count
, x_msg_data                                => lx_msg_data);

   IF lx_return_status = fnd_api.g_ret_sts_success
   THEN
      FORALL i IN lp_oks_txn_inst_tbl.FIRST .. lp_oks_txn_inst_tbl.LAST
         INSERT INTO oks_instance_temp
              VALUES lp_oks_txn_inst_tbl( i);

      fnd_global.apps_initialize(12247
                               , 50305
                               , 515);

      OPEN get_k_for_rpl_csr;

      FETCH get_k_for_rpl_csr BULK COLLECT INTO k_rpl_tbl;

      CLOSE get_k_for_rpl_csr;

      apps.oks_extwarprgm_pvt.update_contract_ibreplace(
           k_rpl_tbl
         , lx_return_status
         , lx_msg_count
         , lx_msg_data);

      IF lx_return_status = fnd_api.g_ret_sts_success
      THEN
         DBMS_OUTPUT.put_line( 'Swapped syccessfully.');
         COMMIT;
      ELSE
         IF lx_msg_count > 0
         THEN
            FOR i IN 1 .. lx_msg_count
            LOOP
               fnd_msg_pub.get(p_msg_index    => i
                             , p_encoded      => fnd_api.g_false
                             , p_data         => lx_msg_data
                             , p_msg_index_out=> l_msg_index_out);

               IF l_return_msg IS NULL
               THEN
                  l_return_msg                :=
                        l_msg_index_out
                     || ':'
                     || lx_msg_data;
               ELSE
                  l_return_msg                :=
                        l_return_msg
                     || '/'
                     || l_msg_index_out
                     || ':'
                     || lx_msg_data;
               END IF;
            END LOOP;
         END IF;

         DBMS_OUTPUT.put_line(   'Swaping of Service contract for IB failed : '
                              || NVL(lx_msg_data, l_return_msg));
      END IF;
   ELSE
      IF lx_msg_count > 0
      THEN
         FOR i IN 1 .. lx_msg_count
         LOOP
            fnd_msg_pub.get(p_msg_index    => i
                          , p_encoded      => fnd_api.g_false
                          , p_data         => lx_msg_data
                          , p_msg_index_out=> l_msg_index_out);

            IF l_return_msg IS NULL
            THEN
               l_return_msg                :=
                     l_msg_index_out
                  || ':'
                  || lx_msg_data;
            ELSE
               l_return_msg                :=
                     l_return_msg
                  || '/'
                  || l_msg_index_out
                  || ':'
                  || lx_msg_data;
            END IF;
         END LOOP;
      END IF;

      DBMS_OUTPUT.put_line(   'call to contract failure : '
                           || NVL(lx_msg_data, l_return_msg));
   END IF;
END;

R12: How to Query the Service Contracts Tables for Header, Line, Subline and Billing Information -- 816374.1

A. Contract Header Data

select * from OKC_K_HEADERS_ALL_B 
where contract_number like :p_contract_number;

B. Contract Line Data

Note: in OKC_K_LINES_B the chr_id field is only populated with the contract header id for contract lines. For contract sublines, this value is NULL.  Dnz_chr_id is populated with the contract header id for both lines and sublines. 

B1. This SQL takes data from views rather than from actual contracts tables and is useful for reviewing data but not ideal for verifying if base tables hold correct data.

  SELECT DISTINCT oal.line_number
                , oll.lse_name
                , oal.sts_code "Status"
                , oal.trn_code
                , oal.lse_id
                , old.service_name
                , oal.currency_code "Currency|Code"
                , TO_CHAR( oal.start_date
                         , 'DD-MON-YYYY' )
                     "Start Date"
                , TO_CHAR( oal.end_date
                         , 'DD-MON-YYYY' )
                     "End Date"
                , qpl.name "Price List Name"
                , cust_acct_id
                , bill_to_site_use_id
                , inv_rule_id
                , ship_to_site_use_id
                , ship_to_site_use_id
                , acct_rule_id
                , usage_period
                , usage_type
                , uom_quantified
                , billing_schedule_type
                , invoice_text
    FROM oks_auth_lines_v oal
       , okc_launch_lgrid_v oll
       , qp_pricelists_lov_v qpl
       , oks_line_details_v old
   WHERE oal.id = oll.id
     AND cle_id IS NULL
     AND qpl.price_list_id = oal.price_list_id
     AND old.contract_id = oll.chr_id
     AND oll.chr_id = '<value of id taken from query A>'

ORDER BY TO_NUMBER( line_number );


B2. Data taken directly from contract table. (Note that this query may appear to return duplicate lines, as the query on okc_k_headers_all_b will return more than one contract if the contract has been renewed).

SELECT *
  FROM okc_k_lines_b
 WHERE chr_id IN (SELECT id
                    FROM okc_k_headers_all_b

                   WHERE contract_number = '<contract number>');

C. Contract Subline Data

Note: When you add a subline to a contract OKC_K_LINES_B is populated with data, some of the data created there for each subline is internal data. Use the LSE_ID to restrict the data returned when querying. 

C1. Query for all the sublines on a contract with a Level type that can be seen when authoring the contract (i.e. restricts to lines which have Level of Product, Site, Item, System, Customer or Site). Note that this query may appear to return duplicate lines, as the query on okc_k_headers_all_b will return more than one contract if the contract has been renewed.

SELECT id
     , line_number
     , cle_id
     , sts_code
     , hidden_ind
     , DECODE( lse_id,  8, 'Party',  7, 'Item',  9, 'Product',  10, 'Site',  11, 'System',  35, 'Customer' ) "Level"
     , object_version_number
     , price_negotiated
     , price_level_ind
     , price_unit
     , price_unit_percent
     , price_type
     , currency_code
     , price_list_id
     , price_list_line_id
     , item_to_price_yn
     , pricing_date
     , date_terminated
     , start_date
     , end_date
  FROM okc_k_lines_b
 WHERE dnz_chr_id IN (SELECT id
                        FROM okc_k_headers_all_b
                       WHERE contract_number = '<contract number>')

   AND lse_id IN (8, 7, 9, 10, 11, 35);


C2. Query for contract sublines for a given contract line only. Replace <parent line number> with the line number of the required contract line (e.g. 1, 2. 3), taken either from the contract form, or from query B2. (Note that this query may appear to return duplicate lines, as the query on okc_k_headers_all_b will return more than one contract if the contract has been renewed).

SELECT id
     , line_number
     , cle_id
     , sts_code
     , DECODE( lse_id,  8, 'Party',  7, 'Item',  9, 'Product',  10, 'Site',  11, 'System',  35, 'Customer' ) "Level"
     , object_version_number
     , price_negotiated
     , price_level_ind
     , price_unit
     , price_unit_percent
     , price_type
     , currency_code
     , price_list_id
     , price_list_line_id
     , item_to_price_yn
     , pricing_date
     , date_terminated
     , start_date
     , end_date
  FROM okc_k_lines_b
 WHERE dnz_chr_id IN (SELECT id
                        FROM okc_k_headers_all_b
                       WHERE contract_number = '<contract number>')
   AND cle_id IN (SELECT id
                    FROM okc_k_lines_b
                   WHERE chr_id IN (SELECT id
                                      FROM okc_k_headers_all_b
                                     WHERE contract_number = '<contract number>')
                     AND line_number = '<parent line number>')
   AND lse_id IN (8, 7, 9, 10, 11, 35);

C3. This query returns the inventory item for a given contract subline where the Level = Product (i.e. the subline is for a particular install base instance).

SELECT kl.line_number
     , ks.name
     , i.segment1
  FROM okc_k_headers_all_b kh
     , okc_k_lines_b kl
     , okc_k_items ki
     , okc_line_styles_v ks
     , csi_item_instances c
     , mtl_system_items_b i
 WHERE kh.contract_number = '<contract number>'
   AND kh.contract_number_modifier IS NULL                                                                 --can be populated
   AND kh.id = kl.dnz_chr_id
   AND kh.id = ki.dnz_chr_id
   AND kl.id = ki.cle_id
   AND kl.lse_id = ks.id
   AND ki.jtot_object1_code IN ('OKX_CUSTPROD')
   AND c.last_vld_organization_id = i.organization_id
   AND TO_NUMBER( ki.object1_id1 ) = c.instance_id
   AND c.inventory_item_id = i.inventory_item_id;

D. Contract Billing Data

D1. This query shows the billing invoice details. Note that -99 will be shown for invoice number if the 'Service Contracts Fetch Receivables Info For Billing' concurrent program has not been run after Autoinvoice has been run.

SELECT DISTINCT d.contract_number
              , a.trx_number "Invoice Number"
              , TO_CHAR( b.date_billed_from
                       , 'DD-MON-YYYY HH24-MI' )
                   "Bill From"
              , TO_CHAR( b.date_billed_to
                       , 'DD-MON-YYYY HH24-MI' )
                   "Bill To"
              , b.amount
  FROM oks_bill_transactions a
     , oks_bill_txn_lines aa
     , oks_bill_cont_lines b
     , okc_k_lines_b c
     , okc_k_headers_all_b d
 WHERE a.id = aa.btn_id
   AND aa.bcl_id = b.id
   AND b.cle_id = c.cle_id
   AND c.dnz_chr_id = d.id
   AND d.id = '<contract id from query A>';

D2. This query shows the billing transaction details. The data in this table is shown in the History tab of the Billing Schedule form in the contract. 

For the bill_action, the codes have the following meanings: 
Regular Invoice -RI, 
Termination Credit - TR, 
Averaging - AV, 
Settlement Invoice - SRI, 
Settlement Credit - STR.

  SELECT hdr.contract_number "Contract"
       , hdr.contract_number_modifier "Modifier"
       , hdr.id
       , TO_CHAR( cont.creation_date
                , 'DD-MON-YYYY HH24:MI' )
            "Creation Date"
       , bill_action
       , btn_id "Billing Transaction ID"
       , amount
       , TO_CHAR( date_billed_from
                , 'DD-MON-YYYY' )
            "Date Billed From"
       , TO_CHAR( date_billed_to
                , 'DD-MON-YYYY' )
            "Date Billed To"
    FROM oks_bill_cont_lines cont
       , okc_k_lines_b line
       , okc_k_headers_all_b hdr
   WHERE hdr.id = line.dnz_chr_id
     AND cont.cle_id = line.id
     AND hdr.id = '<contract id from query A>'

ORDER BY cont.creation_date;

D3. This query returns data about the contract Line ids corresponding to each invoice as well as invoice details.

SELECT bcl.id
     , bcl.cle_id
     , bcl.btn_id
     , bcl.bill_action
     , okl.id "Line id"
     , okh.id "Contract id"
     , btn.trx_number "Invoice"
     , bcl.date_billed_from
     , bcl.date_billed_to
  FROM oks_bill_cont_lines bcl
     , okc_k_lines_b okl
     , okc_k_headers_all_b okh
     , oks_bill_transactions btn
     , oks_bill_txn_lines btl
 WHERE okh.contract_number = '<contract number>'
   AND okh.id = okl.dnz_chr_id
   AND okl.cle_id IS NULL
   AND okl.id = bcl.cle_id
   AND btn.id = bcl.btn_id
   AND btl.btn_id = btn.id

   AND btl.bill_instance_number IS NOT NULL;

D4. This query returns information about what the contract billing schedule for a contract and can be used to investigate amounts expected to be billed in a billing period.

SELECT TO_CHAR( bcl.id )
     , TO_CHAR( bsl.id )
     , TO_CHAR( lvl.id )
     , lvl.date_start
     , bsl.date_billed_from
     , lvl.date_end
     , bsl.date_billed_to
     , bcl.date_next_invoice
     , lvl.date_transaction
     , lvl.date_to_interface
     , lvl.date_completed
     , TO_CHAR( rul_id )
     , TO_CHAR( lvl.parent_cle_id )
     , bsl.amount
  FROM oks_bill_sub_lines bsl
     , oks_bill_cont_lines bcl
     , oks_level_elements lvl
     , okc_k_lines_b kl
     , okc_k_headers_all_b kh
 WHERE kh.contract_number = '<contract number>'
   AND kl.dnz_chr_id = kh.id
   AND lvl.dnz_chr_id = kh.id
   AND bcl.cle_id = kl.id
   AND bcl.id = bsl.bcl_id
   AND lvl.cle_id = bsl.cle_id;

E. Receivables Interface Data

E1. Query to return all the data in the RA interface table for a given service contract. This will return the data populated into the table by Service Contracts Main Billing. Note that this query will not return any data if Autoinvoice has been run since the records are deleted from this table once they have been successfully processed by Autoinvoice.

SELECT *
  FROM ra_interface_lines_all
 WHERE sales_order = '<contract number>';

F. Subscription Contracts

F1. How to find the install base instance created for the subscription line item. (Note that when you enter a subscription line, the application automatically creates an Oracle Install Base item instance. This is what this query is retrieving).
Note: the last line in the query can be commented out if your contract has no modifier.

SELECT osh.instance_id
     , okh.contract_number
     , okh.contract_number_modifier
     , okl.line_number
  FROM oks_subscr_header_b osh
     , okc_k_headers_all_b okh
     , okc_k_lines_b okl
 WHERE osh.dnz_chr_id = okh.id
   AND osh.cle_id = okl.id
   AND okl.chr_id = okh.id
   AND okh.contract_number = '<contract number>'
   AND NVL( okh.contract_number_modifier, '-' ) = NVL( '<contract_modifier>', '-' );

F2. Query to find the install base instances created by a Subscription Contract as a result of subscription fulfillment.

SELECT csi.instance_number
  FROM oks_subscr_elements ose
     , csi_item_instances csi
 WHERE ose.dnz_chr_id IN (SELECT id
                            FROM okc_k_headers_all_b
                           WHERE contract_number = '<contract number>'
                             AND NVL( contract_number_modifier, '-' ) = NVL( '<contract modifier>', '-' ))
   AND ose.order_line_id = csi.last_oe_order_line_id;

f3. QUERY TO find which subscription contract line created THE install base INSTANCE, FOR A s/ubscription fulfillment.

SELECT okh.contract_number
     , okh.contract_number_modifier
     , okl.line_number
  FROM oks_subscr_elements ose
     , csi_item_instances csi
     , okc_k_headers_all_b okh
     , okc_k_lines_b okl
 WHERE csi.instance_number = '<Instance Number>'
   AND ose.order_line_id = csi.last_oe_order_line_id
   AND okh.id = ose.dnz_chr_id
   AND okl.chr_id = okh.id
   AND okl.id = ose.dnz_cle_id;