 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.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
     FROM independent_demand, item 
    WHERE ( independent_demand.item_code = item.item_code ) and 
          ( independent_demand.site_code in ('@sitecode@') ) AND   
          ( item.item_code__plan = '@itemcode@') AND 
          ( independent_demand.due_date >= to_date('@fromdate@')) AND   
          ( independent_demand.due_date <= to_date('@todate@'))  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,dist_demand.due_date as due_date, 
         sum(case when dist_demand.qty_required is null then 0 else dist_demand.qty_required 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  
     FROM dist_demand, item 
    WHERE ( dist_demand.item_code =  item.item_code ) and 
          ( dist_demand.site_code__source in ('@sitecode@') ) AND  
          ( item.item_code__plan = '@itemcode@') AND 
          ( dist_demand.due_date >= to_date('@fromdate@')) AND   
          ( dist_demand.due_date <= to_date('@todate@')) AND 
          ( 0 = @madetostock@ ) 
         Group By item.item_code__plan,dist_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, 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 sales_demand, item 
    WHERE ( sales_demand.item_code =  item.item_code ) and 
          ( sales_demand.site_code in ('@sitecode@') ) AND  
          ( item.item_code__plan = '@itemcode@') AND 
          ( sales_demand.due_date >= to_date('@fromdate@')) AND   
          ( sales_demand.due_date <= to_date('@todate@')) AND 
          ( 0 = @madetostock@ ) 
         Group By item.item_code__plan,sales_demand.due_date 
UNION ALL
   SELECT  item.item_code__plan  as item_code,invtrace.tran_date as due_date, 
          sum(invtrace.eff_qty) 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 invtrace, item, location, invstat 
     WHERE ( invtrace.item_code = item.item_code ) and 
			( location.loc_code = invtrace.loc_code)  and
			( invstat.inv_stat = location.inv_stat) and
			( invstat.stat_type <> 'S' ) and
          ( invtrace.site_code in ('@sitecode@') ) AND   
          ( item.item_code__plan = '@itemcode@') AND 
          ( invtrace.tran_date >= to_date('@fromdate@'))   and 
          ( invtrace.tran_date <= to_date('@rundate@'))   and 
          ( invtrace.ref_ser in ('D-ISS','S-DSP') )
         Group By item.item_code__plan,invtrace.tran_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, 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   
     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   
          ( item.item_code__plan = '@itemcode@') AND 
          ( distorder_det.due_date <= to_date('@todate@'))   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,
			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_dr, 0 as pending_indent, 0 as pending_dr 	   
          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 
          ( item.item_code__plan = '@itemcode@') AND 
           ( distorder_det.due_date <= to_date('@todate@')) 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,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 
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   
  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   		   
      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 
 
UNION ALL 
      
 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(to_date('@fromdate@')) 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, 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   a.item_code = '@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,
 NVL((eff_qty * -1),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 
  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())) group by item_code,due_date

 UNION ALL 
       SELECT  item.item_code__plan  as item_code,to_date('@fromdate@') 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 		   
   FROM   workorder, item 
   WHERE ( workorder.item_code = item.item_code ) and 
         ( workorder.site_code in ('@sitecode@') ) AND 
          ( item.item_code__plan = '@itemcode@') AND 
         ( workorder.due_date <= to_date('@todate@')) 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  item.item_code__plan ,to_date('@fromdate@') 
 UNION ALL 
         SELECT workorder_bill.item_code as item_code,to_date('@fromdate@') 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 		   
 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, item item1 
                   WHERE item1.item_code = workorder.item_code and
				(workorder.site_code in ('@sitecode@') ) AND  
                         ( item1.item_code__plan = '@itemcode@' ) AND  
                         (workorder.due_date <= to_date('@todate@')) 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.item_code,to_date('@fromdate@') 


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