[sourcecode language="sql" padlinenumbers="true"]
SET SERVEROUTPUT ON SIZE UNLIMITED;
Declare
Cursor c1 is
Select distinct sold_from_org_id org_id, order_quantity_uom uom, inventory_item_id, 'O' line_source from oe_order_lines_all where sold_to_org_id = 3892
union all
Select distinct  odh.org_id,  odl.uom, odl.inventory_item_id, 'D' line_source from 
oms_dn_lines_all odl, oms_dn_headers_all odh
where 1=1
and odh.header_id = odl.header_id
and odh.customer_id = 3892 
and odl.inventory_item_id NOT IN (
Select inventory_item_id from oe_order_lines_all where sold_to_org_id = 3892)
and odl.inventory_item_id IS NOT NULL
union all
Select organization_id org_id, primary_uom_code uom, inventory_item_id, 'N' line_source from mtl_system_items where
1=1
and organization_id = 110
and inventory_item_id NOT IN (
Select inventory_item_id from oe_order_lines_all where sold_to_org_id = 3892
union all
Select inventory_item_id from oms_dn_lines_all where customer_id = 3892 and inventory_item_id IS NOT NULL)
order by inventory_item_id;
[/sourcecode]