0 Replies Latest reply: Nov 1, 2012 6:54 AM by 870586 RSS

    Problem in displaying service contract details

    870586
      Hi

      I have to create one report which will show all contracts ,its line and sub line details.
      I am new to service contracts and dont know about its tables.
      I have written one query but it is showing only service line details not the usage line details .
      Please let me know what am I doing wrong in the below query.Which join am I missing?

      select okhb.contract_number,
      okl.ID,
      oki.object1_id1,
      sub_items.object1_id1,
      okl.line_number,
      okl.line_number||'.'||okl1.line_number sub_line_no,
      msi.segment1 line_item
      ,msi.description line_item_description
      ,msi_sub.segment1 sub_line_item
      ,msi_sub.description sub_line_item_description
      ,cii.serial_number
      from OKC_K_LINES_B okl,
      OKC_K_LINES_B okl1,
      OKC_K_HEADERS_ALL_B okhb,
      okc_k_items oki,
      okc_k_items sub_items,
      mtl_system_items_b msi,
      mtl_system_items_b msi_sub,
      csi_item_instances CII
      where okhb.id=okl.Chr_Id
      and okl.id=okl1.cle_id
      and okl.id = oki.cle_id

      --and okhb.contract_number='66'
      and oki.object1_id1 = msi.inventory_item_id
      and oki.object1_id2 = msi.organization_id
      and sub_items.object1_id1 = cii.instance_id

      and cii.inventory_item_id = msi_sub.inventory_item_id
      and cii.inv_master_organization_id = msi_sub.organization_id
      and okl1.id= sub_items.cle_id