Query to fetch list of Service Contracts linked to IB

For a covered product, one first relates the contract to the install base and then the install base to the item. In such a case, the column OBJECT1_ID1 will refer to the install base id.  The item id on the install base record then relates to the inventory record. Contract modifier can be used as you can have more than one contract with the same contract number. The combo of number/modifier has to be unique, so if you use the combo you know you get 1 contract. SELECT kl.line_number, ks.NAME, i.segment1 FROM okc_k_headers_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_modifier IS NULL 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