 select item_code, due_date, round(sum(demand),0) demand, sum(supply) supply from  ( 
SELECT item.item_code__plan as item_code,independent_demand.due_date as due_date, sum(case when independent_demand.quantity__std_uom is null then 0 else independent_demand.quantity__std_uom end)
         as demand, 0 as supply 
   FROM independent_demand, item  
   WHERE ( independent_demand.item_code = item.item_code ) and 
         ( independent_demand.site_code in ('@sitecode@') ) AND  
         ( independent_demand.item_code = '@itemcode@') AND 
         ( independent_demand.due_date >= timestamp('@fromdate@-00.00.00')) AND 
         ( independent_demand.due_date <= timestamp('@todate@-00.00.00'))  and 
         ( case when independent_demand.status is null then 'C' else independent_demand.status end <> 'P')  
  Group By item.item_code__plan,independent_demand.due_date 
UNION ALL
 SELECT  item.item_code__plan as item_code, sales_demand.due_date as due_date, 
             sum(case when sales_demand.quantity__std_uom is null then 0 else sales_demand.quantity__std_uom end  - case 
              when sales_demand.consumed_qty is null then 0 else sales_demand.consumed_qty end ) as demand, 0 as supply      
         FROM sales_demand, item 
         WHERE ( sales_demand.item_code =  item.item_code ) and 
               ( sales_demand.site_code in ('@sitecode@') ) AND 
               ( sales_demand.item_code = '@itemcode@' ) AND 
               ( sales_demand.due_date >= timestamp('@fromdate@-00.00.00')) AND 
               ( sales_demand.due_date <= timestamp('@todate@-00.00.00')) AND ( 0 = @madetostock@ ) 
               Group By item.item_code__plan,sales_demand.due_date  
UNION ALL
 SELECT item.item_code__plan as item_code, distorder_det.due_date as due_date,
         sum(case when qty_confirm is null then 0 else qty_confirm end - case when qty_shipped is null then 0 else qty_shipped end) as demand,           0 as supply      
         FROM distorder,distorder_det, item 
         WHERE ( distorder_det.item_code = item.item_code ) and 
               ( distorder.dist_order = distorder_det.dist_order  ) and 
               ( distorder.confirmed = 'Y'  ) and 
               ( distorder.site_code__ship in ('@sitecode@') ) AND 
               ( distorder_det.item_code = '@itemcode@') AND 
               ( distorder_det.due_date <= timestamp('@todate@-00.00.00'))   and 
               ( distorder.order_type not in (@ordtypes@) ) and 
               ( case when distorder.status is null then 'P' else distorder.status end = 'P') AND 
               ( case when qty_confirm is null then 0 else qty_confirm end - case when qty_shipped is null then 0 else qty_shipped end) > 0    
         Group By item.item_code__plan,distorder_det.due_date           
UNION ALL
 SELECT  item.item_code__plan as item_code, distorder_det.due_date as due_date, 
		0 as demand, 
		sum(case when qty_confirm is null then 0 else qty_confirm end - case when qty_received is null then 0 else qty_received end ) as supply 
FROM distorder,distorder_det, item       
WHERE ( distorder_det.item_code = item.item_code ) 
		and ( distorder.dist_order = distorder_det.dist_order  ) 
		and ( distorder.confirmed = 'Y'  ) 
		and ( distorder.site_code__dlv in ('@sitecode@') ) 
		AND ( distorder_det.item_code = '@itemcode@') 
		AND ( distorder_det.due_date <= timestamp('@todate@-00.00.00')) 
		and ( distorder.order_type not in (@ordtypes@) ) 
		and ( case when distorder.status is null then 'P' else distorder.status end = 'P')   
		AND (case when qty_confirm is null then 0 else qty_confirm end - case when qty_received is null then 0 else qty_received end ) > 0 
Group By  item.item_code__plan,distorder_det.due_date  
UNION ALL
 SELECT  	 item.item_code__plan as item_code, porddet.dlv_date as due_date, 
		  	0 as demand ,            
			sum(case when (case when porddet.quantity__stduom is null then 0 else porddet.quantity__stduom end - case when porddet.dlv_qty is null then 0 
				else porddet.dlv_qty end) < 0 then 0 else case when porddet.quantity__stduom is null then 0 else porddet.quantity__stduom end - case when porddet.dlv_qty is null then 0 
				else porddet.dlv_qty end end  ) as supply         
FROM porddet,porder, item      
WHERE (  porddet.item_code = item.item_code ) 
		and         (porddet.purc_order = porder.purc_order ) 
		and          (porder.confirmed = 'Y') 
		and         (porder.status not in('C','X')) 
		and         (porddet.status not in('C','X')) 
		and         (porddet.site_code in ('@sitecode@') ) 
		AND         (  porddet.item_code = '@itemcode@' ) 
		and         (  porder.pord_type not in (@ordtypes@) ) 
		and         (  porddet.dlv_date         <= timestamp('@todate@-00.00.00'))            
Group By  item.item_code__plan,porddet.dlv_date  
UNION ALL
 SELECT  item.item_code__plan as item_code, FN_MRP_DUEDATE(timestamp('@fromdate@-00.00.00')) as due_date,          
		0 as demand ,          
		DDF_INV_CLSTK_MRP('@sitecode@',
		item.item_code,
		timestamp(fn_sysdate())) as supply           
FROM  item          
WHERE item.item_code  = '@itemcode@'          
		and   DDF_INV_CLSTK_MRP('@sitecode@',item.item_code,timestamp(fn_sysdate())) > 0  
   ) alias 
group by item_code,due_date 
having sum(demand) <> 0 or sum(supply) <> 0