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,tran_id,tran_ser  ,site_code    from 
 ( 
  
SELECT a.site_code||'@'||d.item_code||'@'||a.lot_no||'@'||a.lot_sl||'@'||a.loc_code||'@'||(case when c.alloc_pref is null then 'X' else c.alloc_pref end)  as tran_id,
  'Stock' as tran_ser,  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 , a.site_code  		   
 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, ' ') <> @ordtypes@
 and   (  a.site_code || d.item_code__plan ) IN ( '@itemcode@' ) 
 AND    NVL(b.plan_prod,'N') = 'Y'
 AND     a.item_code=d.item_code 
 ) alias    

group by item_code,due_date ,tran_id,tran_ser,site_code      
having  sum(supply) <> 0 
