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);