Query to get Service Contracts Header, Line, Subline in Oracle Apps

Run initialization with below command.

exec mo_global.set_policy_context('S', 103);
 
Contract Header Data:-

 SELECT CONTRACT_NUMBER "Contract",
       TO_CHAR (id) "Id",
       SHORT_DESCRIPTION "Description",
       INV_ORGANIZATION_ID "Organization Id",
       STS_CODE "Status",
       scs_code,
       CURRENCY_CODE "Currency",
       TO_CHAR (DATE_TERMINATED, 'DD-MON-YYYY') "Date Terminated",
       TRN_CODE,
       TO_CHAR (START_DATE, 'DD-MON-YYYY') "Start Date",
       TO_CHAR (END_DATE, 'DD-MON-YYYY') " End Date",
       UPG_ORIG_SYSTEM_REF,
       PRICE_LIST_ID,
       BILL_TO_SITE_USE_ID,
       SHIP_TO_SITE_USE_ID,
       PAYMENT_TERM_ID,
       ACCT_RULE_ID,
       INV_RULE_ID,
       AR_INTERFACE_YN,
       CLASS_MEANING,
       TEMPLATE_YN,
       HOLD_BILLING
  FROM oks_auth_headers_v
 WHERE CONTRACT_NUMBER ='123456';


Contract Line 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 = '1112730666' --Pass value id from above query
ORDER BY TO_NUMBER (line_number);

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.

Contract Subline Data:-
 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_b
                                                     WHERE contract_number = '123456'--Pass Contract Number
                                                  )
       AND lse_id IN (8,
                      7,
                      9,
                      10,
                      11,
                      35);

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.