 select  item_code,due_date, round(sum(demand),0) demand, sum(supply) supply, sum(stock_qty) stock_qty, sum(oth_supply) oth_supply, sum(pending_po) pending_po ,sum(pending_do) pending_do, sum(pending_indent) pending_indent, sum(pending_dr) pending_dr from 
 (
  
         select item_code,due_date,sum(demand) demand,sum(supply) supply,sum(stock_qty) stock_qty,
sum(oth_supply) oth_supply,sum(pending_po) pending_po,sum(pending_do) pending_do,sum(pending_indent) pending_indent,
sum(pending_dr) pending_dr from 
(

 SELECT 
d.item_code__plan  as item_code,FN_MRP_DUEDATE( timestamp('@fromdate@-00.00.00')) as due_date,0 as demand,      
 (a.quantity-a.alloc_qty) as supply,(a.quantity-a.alloc_qty) as stock_qty,
 0 as oth_supply, 0 as pending_po, 0 as pending_do, 0 as pending_indent, 0 as pending_dr 		   
 FROM   STOCK a,  LOCATION c,item d 
 WHERE  a.loc_code = c.loc_code
 AND    a.site_code = '@sitecode@'
 AND    d.item_code__plan = '@itemcode@'
 AND     a.item_code=d.item_code 

UNION ALL

	SELECT  item.item_code__plan as item_code, mps_order.due_date as due_date, 
		sum(mps_order.quantity) as demand, 0 as supply , 0 as stock_qty, 0 as oth_supply, 0 as pending_po, 0 as pending_do, 0 as pending_indent, 0 as pending_dr 
  FROM   mps_order, item
  WHERE ( mps_order.item_code = item.item_code ) and
  			(mps_order.site_code in ('@sitecode@') ) AND  
		  (item.item_code__plan  = '@itemcode@' ) AND  
        (mps_order.due_date >=  timestamp('@fromdate@-00.00.00')) AND  
        (mps_order.due_date <=  timestamp('@todate@-00.00.00')) AND
		  ( case when mps_order.status is null then 'P' else mps_order.status end in ('P','T','M'))
Group By item.item_code__plan, mps_order.due_date
 
  )
 group by item_code, due_date 

)

 group by item_code,due_date 
 having sum(demand) <> 0 or sum(supply) <> 0
