This discussion is archived
0 Replies Latest reply: Nov 1, 2012 4:54 AM by 870586 RSS

Problem in displaying service contract details

870586 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points