 select  item_code,due_date 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,site_code,sum(qtstk) qtstk   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,site_code,sum(qtstk) qtstk  from 
(


SELECT d.item_code__plan  as item_code,FN_MRP_DUEDATE(to_date('@fromdate@')) as due_date,0 as demand,
 0 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,a.site_code as site_code,(a.quantity-a.alloc_qty) as qtstk   		   
 FROM   STOCK a,INVSTAT b,  LOCATION c,item d 
 WHERE  a.loc_code = c.loc_code 
 AND    b.inv_stat = c.inv_stat 
AND 	b.usable = 'N'
 AND    NVL(b.stat_type, ' ') <> 'S' 
 AND    a.site_code = '@sitecode@' 
 AND    d.item_code__plan = '@itemcode@' 
 AND    NVL(b.plan_prod,'N') = 'Y' 
 AND     a.item_code=d.item_code  
UNION ALL 
 SELECT d.item_code__plan  as item_code,FN_MRP_DUEDATE(to_date('@fromdate@')) as due_date,0 as demand,
 0 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,a.site_code as site_code,(a.quantity-a.alloc_qty) as qtstk   		   
 FROM   STOCK a,INVSTAT b,  LOCATION c,item d 
 WHERE  a.loc_code = c.loc_code 
 AND    b.inv_stat = c.inv_stat 
 AND    NVL(b.stat_type, ' ') <> 'S' 
 AND    a.site_code = '@sitecode@' 
 AND    d.item_code__plan = '@itemcode@' 
 AND    NVL(b.plan_prod,'N') = 'Y' 
 AND     a.item_code=d.item_code  
UNION ALL
 SELECT d.item_code__plan  as item_code,FN_MRP_DUEDATE(to_date('@fromdate@')) as due_date,0 as demand,
 0 as supply,NVL((eff_qty * -1),0) as stock_qty,
 0 as oth_supply, 0 as pending_po, 0 as pending_do, 0 as pending_indent, 0 as pending_dr,a.site_code as site_code ,NVL((eff_qty * -1),0) as qtstk    
  FROM   INVTRACE a, LOCATION b, INVSTAT c,item d
 WHERE  a.loc_code  = b.loc_code
 AND   b.inv_stat  = c.inv_stat
 AND    NVL(c.stat_type, ' ') <> 'S'
 AND   a.item_code = '@itemcode@'
 AND   a.site_code = '@sitecode@'
   AND    NVL(c.plan_prod,'N') = 'Y'
AND     a.item_code=d.item_code 
 AND   a.EFF_date > to_date(fn_sysdate()) 
  UNION ALL 
SELECT   item.item_code__plan  as item_code,indent.req_date as due_date, 
           0 as demand , 
           sum(case when (case when indent.quantity is null then 0 else 
indent.quantity end - case when             
indent.ord_qty is null then 0 else indent.ord_qty end) < 0 then 0 else case 
when indent.quantity is null then 0 else 
indent.quantity end - case when indent.ord_qty is null then 0 else 
indent.ord_qty  end end  ) as supply,   
0 as stock_qty, sum(case when (case when indent.quantity is null then 0 else 
indent.quantity end - case when             
indent.ord_qty is null then 0 else indent.ord_qty end) < 0 then 0 else case 
when indent.quantity is null then 0 else 
indent.quantity end - case when indent.ord_qty is null then 0 else 
indent.ord_qty  end end  ) as oth_supply, 0 as pending_po, 0 as pending_do, 
sum(case when (case when indent.quantity is null then 0 else 
indent.quantity end - case when             
indent.ord_qty is null then 0 else indent.ord_qty end) < 0 then 0 else case 
when indent.quantity is null then 0 else 
indent.quantity end - case when indent.ord_qty is null then 0 else 
indent.ord_qty  end end  ) as pending_indent, 0 as pending_dr ,indent.site_code as site_code, 0 as qtstk    
FROM indent,item 
     WHERE (  indent.item_code = item.item_code ) and 
                (indent.status in ('O','A')) and 
        (indent.site_code in ('@sitecode@'))  AND 
          ( item.item_code__plan = '@itemcode@') AND 
          (  indent.req_date         <= to_date('@todate@'))   
         Group By item.item_code__plan,indent.req_date,indent.site_code   
  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,   
			0 as stock_qty, 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 oth_supply, 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 pending_po, 0 as pending_do, 0 as pending_indent, 0 as pending_dr,porddet.site_code as site_code  ,0 as qtstk      		   
      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 
          ( item.item_code__plan = '@itemcode@') AND 
        (  porder.pord_type not in (@ordtypes@) ) and 
        (  porddet.dlv_date         <= to_date('@todate@'))   
         Group By item.item_code__plan,porddet.dlv_date,porddet.site_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,mps_order.site_code as site_code, 0 as qtstk    
  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 >= to_date('@fromdate@')) AND  
        (mps_order.due_date <= to_date('@todate@')) 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,mps_order.site_code
 
  )
 group by item_code, due_date,site_code 

)

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