select john.organization_id,
john.top_item_id,
itema.segment1,
decode(ITEMA.ITEM_TYPE,
'AA','Asset Activity',
'AI','ATO Item',
'AOC','ATO Option Class',
'CI','Container Item',
'EP','Expendable Item',
'FG','Finished Good',
'FRT','Freight',
'IC','Interchangeable Item',
'IR','Internal Requisition',
'LC','Restricted Item',
'LV','Low Value Item',
'OP','Outside Processing Item',
'P','Purchased item',
'PH','Phantom item',
'PTO','PTO model',
'PUTO','Purchased To Order',
'SA','Subassembly',
'SI','Supply item',
null) ITEM_TYPE_CODE,
john.plan_level,
john.component_item_id,
itemb.segment1,
decode(itemb.PLANNING_MAKE_BUY_CODE,1,'Make',2,'Buy') PLANNING_MAKE_BUY_CODE,
john.assembly_item_id,
itemc.segment1,
john.component_quantity,
john.extended_quantity,
john.sort_order
from john_category_item_6 john,
mtl_system_items_b itema,
mtl_system_items_b itemb,
mtl_system_items_b itemc
where john.organization_id=itema.organization_id
and john.top_item_id=itema.inventory_item_id
and john.organization_id=itemb.organization_id
and john.component_item_id=itemb.inventory_item_id
and john.organization_id=itemc.organization_id(+)
and john.ASSEMBLY_ITEM_ID=itemc.inventory_item_id(+)
order by john.top_item_id,john.sort_order