Query to find Service Contract, Line, Subline, Item, Order Information

The following query lists all the service contract related information.

-------------------------------------------------------------------------------

-- Query to find Service Contract, Line, Subline, Item, Order Information

-------------------------------------------------------------------------------

SELECT okh.contract_number         "Contract Number",

       okh.scs_code                "Code",

       ooh.order_number            "Order Number",

       TO_NUMBER(sl.line_number)   "SL Line",

       sl.lse_id                   "SL LSE ID",

       TO_NUMBER(cl.line_number)   "CL Line",

       cl.lse_id                   "CL LSE ID",

       TO_CHAR(cl.id)              "CL ID",

       msi.inventory_item_id,

       msi.segment1                "Item Number",

       cii.serial_number           "Serial Number",

       okh.start_date              "Contract Start Date",

       sl.start_date               "Line Start Date",

       cl.start_date               "Subline Start Date"

  FROM okc_k_headers_all_b   okh,

       okc_k_lines_b         sl,

       okc_k_lines_b         cl,

       okc_k_items           oki,

       okc_k_rel_objs        rel,

       csi_item_instances    cii,

       mtl_system_items_b    msi,

       oe_order_headers_all  ooh

 WHERE 1=1

   AND okh.id                   =  sl.chr_id

   AND cl.cle_id                =  sl.id

   AND sl.cle_id IS NULL

   AND cl.id                    =  oki.cle_id(+)

   AND oki.object1_id1          =  cii.instance_id

   AND cii.inventory_item_id    =  msi.inventory_item_id

   AND okh.inv_organization_id  =  msi.organization_id

   AND rel.chr_id               =  okh.id

   AND rel.jtot_object1_code    =  'OKX_ORDERHEAD'

   AND rel.object1_id1          =  ooh.header_id

   AND oki.object1_id2          =  '#'

   ----

   -- AND okh.cust_po_number       =  ooh.cust_po_number

   -- AND okh.ship_to_site_use_id  =  ooh.ship_to_org_id

   --

   /* if subline start date is earlier than line start date */

   -- AND sl.start_date > cl.start_date

   ----

   -- following conditions are for test purpose only

   ----

   -- AND okh.contract_number = '12796'

   -- AND sl.line_number      = 1

 ORDER BY okh.contract_number,

       TO_NUMBER(sl.line_number),

       TO_NUMBER(cl.line_number);