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 independent_demand.tran_id,'I-REQ' as tran_ser,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 , 0 as stock_qty, 0 as oth_supply, 0 as pending_po, 0 as pending_do, 0 as pending_indent, 0 as pending_dr ,  independent_demand.site_code  
   FROM independent_demand, item  
   WHERE ( independent_demand.item_code = item.item_code ) and 
        ( independent_demand.site_code || item.item_code__plan ) IN ( '@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 independent_demand.tran_id,'I-REQ',item.item_code__plan,independent_demand.due_date ,  independent_demand.site_code   

UNION ALL 
 SELECT  sales_demand.tran_id,'S-FCT' as tran_ser,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  , 
              0 as stock_qty, 0 as oth_supply, 0 as pending_po, 0 as pending_do, 0 as pending_indent, 0 as pending_dr ,  sales_demand.site_code        
         FROM sales_demand, item 
         WHERE ( sales_demand.item_code =  item.item_code ) and 
        ( sales_demand.site_code || sales_demand.item_code ) IN ( '@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 sales_demand.tran_id,'S-FCT',item.item_code__plan,sales_demand.due_date ,  sales_demand.site_code   

UNION ALL 
 SELECT   distorder.dist_order||'@'||distorder_det.line_no tran_id,'Dist Order' as tran_ser, 
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  , 0 as stock_qty, 0 as oth_supply, 0 as pending_po, 
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 pending_do, 
0 as pending_indent, 0 as pending_dr ,  distorder.site_code__ship  as site_code     
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 || distorder_det.item_code ) IN ( '@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 distorder.dist_order||'@'||distorder_det.line_no,'Dist Order',item.item_code__plan,distorder_det.due_date , distorder.site_code__ship   
         
UNION ALL

 SELECT sorder.sale_order||'@'||sorditem.line_no tran_id,'S-ITEM' as tran_ser,
item.item_code__plan as item_code, sorditem.due_date as due_date, 
sum((case when sorditem.quantity is null then 0 else sorditem.quantity end - case when sorditem.qty_alloc is null then 0 		else sorditem.qty_alloc end - case when sorditem.qty_desp is null then 0 		else sorditem.qty_desp end )  * case when sorditem.conv__qty_stdqty is null then 1 else sorditem.conv__qty_stdqty end ) 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 , sorditem.site_code        
	FROM sorder,sorditem, item     
	WHERE
      ( sorditem.item_code = item.item_code )  and
      ( sorder.sale_order = sorditem.sale_order ) and 
		( case when sorder.confirmed is null then 'N' else sorder.confirmed end         = 'Y' ) and 
		( case when sorditem.consume_fc is null then 'Y' else sorditem.consume_fc end = 'N' ) and 
	( sorditem.site_code || sorditem.item_code ) IN ( '@itemcode@' ) AND  
      ( sorditem.due_date <= timestamp('@todate@-00.00.00')) and 
		( case when sorder.status is null then 'P' else sorder.status end = 'P') 	and 
		( sorder.order_type not in (@ordtypes@) ) and ( 0 = @madetostock@)  
Group By  sorder.sale_order||'@'||sorditem.line_no ,'S-ITEM', 
     item.item_code__plan,sorditem.due_date , sorditem.site_code        
UNION ALL    
 SELECT  distorder.dist_order||'@'||distorder_det.line_no tran_id,'Dist Order' as tran_ser,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,
		0 as stock_qty, 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 oth_supply, 
      0 as pending_po, 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 pending_do,
      0 as pending_indent, 0 as pending_dr ,  distorder.site_code__dlv  as site_code  	    
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 || distorder_det.item_code ) IN ( '@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  distorder.dist_order||'@'||distorder_det.line_no,'Dist Order',item.item_code__plan,distorder_det.due_date ,  distorder.site_code__dlv   

UNION ALL    
 SELECT  porder.purc_order||'@'||porddet.line_no as tran_id,'Purchase Order' as tran_ser,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     
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 || porddet.item_code ) IN ( '@itemcode@' ) 
		and         (  porder.pord_type not in (@ordtypes@) ) 
		and         (  porddet.dlv_date         <= timestamp('@todate@-00.00.00'))            
Group By  porder.purc_order||'@'||porddet.line_no,'Purchase Order',item.item_code__plan,porddet.dlv_date ,  porddet.site_code   

UNION ALL   
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 
UNION ALL  
 SELECT   workorder.work_order||'@'||'1' as tran_id,'Work Order' as tran_ser,item.item_code__plan as item_code,
 timestamp('@fromdate@-00.00.00') as due_date,            
		 0 as demand,sum((case when workorder.current_batch_qty is null then 0 else workorder.current_batch_qty end * case when workorder.yield_perc is null then 100 else workorder.yield_perc end / 100 ) - case when workorder.quantity__rcvd is null then 0 else workorder.quantity__rcvd end ) as supply,
0 as stock_qty, sum((case when workorder.current_batch_qty is null then 0 else workorder.current_batch_qty end * case when workorder.yield_perc is null then 100 else workorder.yield_perc end / 100 ) - case when workorder.quantity__rcvd is null then 0 else workorder.quantity__rcvd end ) as oth_supply, 0 as pending_po, 0 as pending_do, 0 as pending_indent, 0 as pending_dr ,  workorder.site_code        
FROM   workorder, item    
WHERE ( workorder.item_code = item.item_code ) 
 and   ( workorder.site_code ||  workorder.item_code ) IN ( '@itemcode@' ) 
		AND          ( workorder.due_date <= timestamp('@todate@-00.00.00')) 
		AND workorder.SALE_ORDER IS  NULL
		AND          (case when workorder.status is null then 'P' else workorder.status end in ('R','F')) 
		and         (case when workorder.consider_wip is null then 'O' else workorder.consider_wip end = 'O')          
Group By  workorder.work_order||'@'||'1','Work Order',item.item_code__plan, timestamp('@fromdate@-00.00.00') ,  workorder.site_code       

UNION ALL   
 SELECT workorder_bill.work_order||'@'||'1' as tran_id,case when rtrim(siteitem.supp_sour) = 'M' then 'Work Order' when rtrim(siteitem.supp_sour) = 'P' then 'Indent' else '???' end  as tran_ser, workorder_bill.item_code as item_code, timestamp('@fromdate@-00.00.00') as due_date, 
		sum(  case when workorder_bill.QUANTITY is null then 0 else workorder_bill.QUANTITY end  - case when (case when workorder_bill.ALLOC_QTY is null then 0 else workorder_bill.ALLOC_QTY end - case when workorder_bill.ISS_QTY is null then 0 
				else workorder_bill.ISS_QTY end) < 0 then case when workorder_bill.ISS_QTY is null then 0 else workorder_bill.ISS_QTY end  else case when workorder_bill.ALLOC_QTY is null then 0 else workorder_bill.ALLOC_QTY end  end  ) 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 , workorder.site_code  		   
from workorder_bill, workorder, item, siteitem  
where workorder_bill.work_order = workorder.work_order  
		and workorder_bill.item_code = item.item_code  
		and workorder_bill.line_type <> 'B'  
		and workorder_bill.item_code = siteitem.item_code  
		and workorder.site_code = siteitem.site_code  
		and case when workorder_bill.QUANTITY is null then 0 
			 else workorder_bill.QUANTITY end - case when (case when workorder_bill.ALLOC_QTY is null then 0 
			 else workorder_bill.ALLOC_QTY end  - case when workorder_bill.ISS_QTY is null then 0 else workorder_bill.ISS_QTY end)<0  
			 then ( case when workorder_bill.ISS_QTY is null then 0 else workorder_bill.ISS_QTY end) 
			 else (case when workorder_bill.ALLOC_QTY is null then 0 else workorder_bill.ALLOC_QTY end) end  > 0  
		and workorder.work_order in ( SELECT   workorder.work_order FROM   workorder WHERE 
		   ( workorder.site_code ||  workorder.item_code ) IN ( '@itemcode@' ) 		
		AND (workorder.due_date <= timestamp('@todate@-00.00.00')) 
		AND workorder.SALE_ORDER IS  NULL
		AND (case when workorder.status is null then 'P' else workorder.status end  in ('R','F')) 
		and (case when workorder.consider_wip is null then 'O' else workorder.consider_wip end = 'O')  )  
Group By  workorder_bill.work_order,case when rtrim(siteitem.supp_sour) = 'M' then 'Work Order' when rtrim(siteitem.supp_sour) = 'P' then 'Indent' else '???'  end,workorder_bill.item_code, timestamp('@fromdate@-00.00.00') , workorder.site_code  
   ) alias    

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