Skip to main content

How to query service contracts tables and tables from other schemas to find underlying data about contract headers, lines, sublines and contract billing in R12.


This article provides a variety of queries which can be used to find the required data for a particular contract.

In the SQL statements given, <xxx> represents text that needs to be replaced with the actual values indicated between the brackets. For example, if you want to execute a query for a contract with number Test1, then whenever you see <contract number> in the SQL below, replace it with Test1.

Sections in this article are:

A. Contract Header Data
B. Contract Line Data
C. Contract Subline Data
D. Contract Billing Data
E. Receivables Interface Data
F. Subscription Contracts


A. Contract Header Data

SELECT * 
FROM   okc_k_headers_all_b 
WHERE  contract_number LIKE '<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 Sub-line Data

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

C1. Query for all the sub-lines 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 subscription 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; 


Reference : 
R12: How to Query the Service Contracts Tables for Header, Line, Subline and Billing Information (Doc ID 816374.1)


Comments

  1. ShivaDecember 7, 2022 at 4:41 AM

    Just copy paste from doc id will not help How to Query the Service Contracts Tables for Header, Line, Subline and Billing Information in R12 (Doc ID 816374.1)

    some are not working at all

    ReplyDelete

Post a Comment

Popular posts from this blog

SQL Query to extract Oracle Purchase Order Information

SELECT   poh.po_header_id,    poh.type_lookup_code PO_TYPE,   poh.authorization_status PO_STATUS,   poh.segment1 PO_NUMBER,   pov.vendor_name SUPPLIER_NAME,   povs.vendor_site_code Location,   hrls.location_code Ship_To,   hrlb.location_code Bill_to,   pol.line_num ,   msib.segment1 Item,   pol.unit_price,   pol.quantity,   pod.amount_billed Amount,   pod.destination_subinventory,   ppf.full_name Buyer_Name,   poh.closed_Code  FROM   PO_HEADERS_ALL poh,   PO_LINES_ALL pol,   mtl_system_items_b msib,   PO_LINE_LOCATIONS_ALL poll,   PO_DISTRIBUTIONS_ALL pod,   po_vendors pov,   po_vendor_sites_All povs,   hr_locations_all hrls,   hr_locations_all hrlb,   per_all_people_f ppf,   po_line_types polt WHERE   1                         =1 AND polt.line...
Post a Comment
Read more

Query to find Operating Unit, Business Group and Legal Entity Information

SELECT   DISTINCT   hrl . country ,                  hroutl_bg . name              bg ,                  hroutl_bg . organization_id ,                  lep . legal_entity_id ,                  lep . name                    legal_entity ,                  hroutl_ou . name              ou_name ,               ...
Post a Comment
Read more

List of iExpenses Tables

List of iExpenses Tables  Table Name Description AP_EXPENSE_REPORT_HEADERS_ALL Expense report header information AP_EXPENSE_REPORT_LINES_ALL Expense report lines information AP_EXP_REPORT_DISTS_ALL Expense report distribution information. It contains the accounts against each expense report line. AP_CREDIT_CARD_TRXNS_ALL Table to store the corporate credit card transactions that are sent by the banks. These lines are saved as expense lines when the user creates the expense lines for credit cards AP_NOTES Table to store the comments entered by approvers and auditors     Setup tables   AP_EXPENSE_REPORTS_ALL This table contains the header level information about the expense templates AP_EXPENSE_REPORT_PARAMS_ALL This table contains the detail level information about the expense templates AP_POL_CAT_OPTIONS_ALL Table to store the policy options AP_POL_CONTEXT Table to store the policy context     ...
Post a Comment
Read more