-- Corrections of Forecast (related to TT 13600)


-- view for resources forecast and ybef values

create or replace view v8_ybef_forecast_check2
as
select c.yevm_ybom_id, b.yevm_id, a.ybef_id, d.zdco_longdesc, b.Forecast, a.ybef_rev_amount, d.zdco_id, b.Posted 
from
  ybef a,
(select v.yevm_id, v.ypkd_id, v.zdco_id, sum(v.Forecast) Forecast, sum(v.Posted) Posted
from (
select yemn_yevm_id yevm_id, 0 ypkd_id, ymrd_zdco_id zdco_id, 
       decode(nvl(yemn_chargedonconsumption, 0)+nvl(yemn_pkgincl, 0), 0, (yemn_quantity*ymrd_yfca_amount) ,1, 0, 2, 0) Forecast,
	   (select sum(yblp_amount) Posted from yblp where yblp_resource_id=yemn_id and yblp_resource_type=8 ) Posted
from yemn, ymrd
where yemn_id=ymrd_yemn_id 
union all
select yemi_yevm_id, 0 ypkd_id, yemi_zdco_id, 
       decode(nvl(yemn_chargedonconsumption, 0)+nvl(yemn_pkgincl, 0), 0, (yemi_price*yemi_quantity), 1, 0, 2, 0),
	   (select sum(yblp_amount) Posted from yblp where yblp_resource_id=yemi_id and yblp_resource_type=5 ) Posted
from yemn, yemi
where yemn_id=yemi_yemn_id 
) v
group by v.yevm_id, v.ypkd_id, v.zdco_id
order by v.yevm_id desc) b,
  yevm c,
  zdco d
where a.ybef_yevm_id=b.yevm_id 
  and a.ybef_ypkd_id=b.ypkd_id
  and a.ybef_zdco_id=b.zdco_id
  --and abs(a.ybef_rev_amount-b.forecast)>0.0001
  and a.ybef_yevm_id=c.yevm_id
  and a.ybef_zdco_id=d.zdco_id
order by b.yevm_id desc;


-- update rev amount values

update ybef 
set ybef_rev_amount=(select w.forecast from v8_ybef_forecast_check2 w where w.ybef_id=ybef.ybef_id)
where ybef_id in 
  (select w.ybef_id 
   from v8_ybef_forecast_check2 w 
   where abs(w.ybef_rev_amount-w.forecast)>0.0001
	 );

	 
-- drop the view 
drop view v8_ybef_forecast_check2;

commit;
