ONT Unbooked Orders

select  h.order_number, 
         h.ordered_date,
         l.line_category_code category,
        fu.user_name,
l.item_identifier_type,
l.inventory_item_id,
l.ordered_item_id,
l.ordered_item,
DECODE(l.line_category_code,'RETURN',nvl(l.unit_selling_price,0) * nvl(l.ordered_quantity,0) * -1,
               nvl(l.unit_selling_price,0) * nvl(l.ordered_quantity,0))  line_amt ,
         h.transactional_curr_code,
l.charge_periodicity_code,
&rp_item_flex_all_seg item_flex, 
	ONT_OEXOEUBK_XMLP_PKG.c_periodicity_dspformula(l.charge_periodicity_code) C_periodicity_dsp,
ONT_OEXOEUBK_XMLP_PKG.Item_dspFormula(l.inventory_item_id,l.item_identifier_type,l.ordered_item_id,MSI.ORGANIZATION_ID,l.ordered_item) Item_dsp 
from    fnd_user fu, 
        oe_order_lines_all l, 
        oe_order_headers_all h,
        mtl_system_items_vl msi 
where   h.header_id = l.header_id
and l.inventory_item_id  = msi.inventory_item_id
and msi.organization_id = nvl(l.ship_from_org_id,:c_master_org)
and     h.created_by = fu.user_id
and     l.booked_flag ='N' 
and     (l.cancelled_flag ='N' or l.cancelled_flag is null)
and     l.open_flag ='Y'   
--and nvl(h.org_id,0) = nvl(:p_organization_id,0)
and nvl(h.org_id,0) = nvl(:p_organization_id1,0)
&lp_created_by
&lp_order_date
&lp_order_category
&lp_line_category
UNION ALL
select   h.order_number,
              h.ordered_date,
              h.order_category_code category,
              fu.user_name,
              null,
              null,
              null,
              null,
              0 line_amt,
              h.transactional_curr_code,
              null,
              null, 
	ONT_OEXOEUBK_XMLP_PKG.c_periodicity_dspformula(null) C_periodicity_dsp,
	null Item_dsp
from oe_order_headers_all h,
        fnd_user fu
where  h.created_by = fu.user_id 
 and   h.booked_flag  ='N' 
 and  (h.cancelled_flag ='N' or h.cancelled_flag is null)
 and h.open_flag = 'Y' 
--and nvl(h.org_id,0) = nvl(:p_organization_id,0)
and nvl(h.org_id,0) = nvl(:p_organization_id1,0)
 and not exists (select null from oe_order_lines_all  l1  where  l1.header_id = h.header_id )
 &lp_created_by 
&lp_order_date
&lp_order_category
&lp_order_by