--LAST UPDATE SCRIPT CONTAINING ONLY VIEWS THAT HAVE CHANGED SINCE PREVIOUS RELEASE

CREATE OR REPLACE VIEW V8_BOF_PREPOST
AS 
select 
   s.s_postdate Postdate, 
   sum(s.s_netamount) netamount, 
   sum(s.s_grossamount) grossamount, 
   s.s_type post_type,
   s.s_numbertype numbertype
from 
( 
select 
  p.ZPOS_POSTDATE s_date, 
  p.ZPOS_POSTDATE s_postdate, 
  p.ZPOS_ZDCO_ID s_ZDCO_ID, 
  round(decode(p.ZPOS_CDT,5,-p.zpos_unitprice*p.zpos_quantity,p.zpos_unitprice*p.zpos_quantity),2) s_netamount, 
  round(decode(p.ZPOS_CDT,5,-p.zpos_unitprice*p.zpos_quantity+nvl(SUM(-t.zpos_unitprice*t.zpos_quantity),0),p.zpos_unitprice*p.zpos_quantity+nvl(SUM(t.zpos_unitprice*t.zpos_quantity),0)),2) s_grossamount, 
  decode(p.ZPOS_CDT,5,'ADV_PAY','ADV_POST') s_Type,
  decode(p.ZPOS_CDT,5,5,1) s_numbertype
FROM 
  ZPOS p, 
  ZPOS t 
WHERE 
       p.zpos_cdt IN (1,5) 
   and t.zpos_cdt(+)=2 
   and p.zpos_taxlink_id=t.zpos_taxlink_id(+) 
   and p.ZPOS_LEDGERSTATUS in (1,2) 
group by 
   p.zpos_id, 
   p.zpos_unitprice, 
   p.zpos_quantity, 
   p.zpos_postdate, 
   p.zpos_zdco_id, 
   p.zpos_cdt, 
   p.ZPOS_PAIDOUT 
union all
select 
  l.ZLSM_DATE s_date, 
  l.ZLSM_DATE s_postdate, 
  p.ZPOS_ZDCO_ID s_ZDCO_ID, 
  round(decode(p.ZPOS_CDT,5,p.zpos_unitprice*p.zpos_quantity,-p.zpos_unitprice*p.zpos_quantity),2) s_netamount, 
  round(decode(p.ZPOS_CDT,5,p.zpos_unitprice*p.zpos_quantity+nvl(SUM(t.zpos_unitprice*t.zpos_quantity),0),-p.zpos_unitprice*p.zpos_quantity+nvl(SUM(-t.zpos_unitprice*t.zpos_quantity),0)),2) s_grossamount, 
  decode(p.ZPOS_CDT,5,'CI_PAY ','CI_POST') s_type,
  decode(p.ZPOS_CDT,5,6,2) s_numbertype 
FROM 
  ZPOS p, 
  ZPOS t, 
  zlsm l 
WHERE 
       p.zpos_cdt IN (1,5) 
   and t.zpos_cdt(+)= 2 
   and p.zpos_taxlink_id=t.zpos_taxlink_id(+) 
   and p.zpos_id=l.zlsm_zpos_id 
   and zlsm_fromledger=1 
   and zlsm_toledger=2 
group by 
   p.zpos_id, 
   p.zpos_unitprice, 
   p.zpos_quantity, 
   p.zpos_postdate, 
   p.zpos_zdco_id, 
   p.zpos_cdt, 
   p.ZPOS_PAIDOUT, 
   l.ZLSM_DATE 
) s 
group by 
   s.s_Type, 
   s.s_postdate,
   s.s_numbertype;

CREATE OR REPLACE FORCE VIEW V8_REP_YHRL_INFOS
AS 
SELECT
  (select WGBS_DATEVALUE from wgbs) FIDELIODATE,
  yrms.YRMS_ID YRMS_ID,
  yrms.YRMS_SHORTDESC YRMS_SHORTDESC,
  yrms.YRMS_LONGDESC YRMS_LONGDESC,
  yrms.YRMS_YCAT_ID YRMS_YCAT_ID,
  yhrl.YHRL_AUTOMAID_NR YHRL_AUTOMAID_NR,
  yhrl.YHRL_CREDIT YHRL_CREDIT,
  yhrl.YHRL_AUTOMAID_MAXCREDITS YHRL_AUTOMAID_MAXCREDITS,
  yhrl.YHRL_YHAT_ID YHRL_YHAT_ID,
  yhrl.YHRL_DATE YHRL_DATE,
  (select yhac.YHAC_COMMENT from yhac where yhac.YHAC_ID=yhrl.YHRL_YHAC_ID) ATTENDANT_NOTES,
  (select yhat.YHAT_NAME from yhat where yhat.YHAT_ID=yhrl.YHRL_YHAT_ID) YHAT_NAME,
  (select yhat.YHAT_NAME from yhat, yhhc, yhrl maid_yhrl where yhhc.YHHC_ID = yhat.YHAT_YHHC_ID and maid_yhrl.YHRL_YHAT_ID = yhat.YHAT_ID and yhhc.YHHC_ROLE = 0 and maid_yhrl.YHRL_DATE = yhrl.YHRL_DATE and maid_Yhrl.YHRL_YRMS_ID = yrms.yrms_id and rownum=1) YHAT_MAID_NAME,
  (select yhhc.YHHC_SHORTDESC from yhat, YHHC where yhhc.YHHC_ID = yhat.YHAT_YHHC_ID and  yhat.YHAT_ID=yhrl.YHRL_YHAT_ID) YHHC_SHORTDESC,
  (select yhhc.YHHC_LONGDESC from yhat, YHHC where yhhc.YHHC_ID = yhat.YHAT_YHHC_ID and  yhat.YHAT_ID=yhrl.YHRL_YHAT_ID) YHHC_LONGDESC,
  (select yhhc.YHHC_ROLE from yhat, YHHC where yhhc.YHHC_ID = yhat.YHAT_YHHC_ID and  yhat.YHAT_ID=yhrl.YHRL_YHAT_ID) YHHC_ROLE,    
  (select decode(yhhc.YHHC_ROLE,0,'Maid',1,'Housekeeper','UNK') from yhat, YHHC where yhhc.YHHC_ID = yhat.YHAT_YHHC_ID and  yhat.YHAT_ID=yhrl.YHRL_YHAT_ID) YHHC_TYPE,  
  (select ycat.YCAT_SHORTDESC from ycat where ycat.YCAT_ID=yrms.YRMS_YCAT_ID) YCAT_SHORTDESC,
  (select ycat.YCAT_LONGDESC from ycat where ycat.YCAT_ID=yrms.YRMS_YCAT_ID) YCAT_LONGDESC,
  yrms.YRMS_YHKS_ID YRMS_YHKS_ID,
  (select yhks.YHKS_SHORTDESC from yhks where yhks.YHKS_ID=yrms.YRMS_YHKS_ID) YHKS_SHORTDESC,
  (select yhks.YHKS_LONGDESC from yhks where yhks.YHKS_ID=yrms.YRMS_YHKS_ID) YHKS_LONGDESC,
  (select yrst.YRST_ID from yrst where ((select WGBS_DATEVALUE from wgbs) between yrst.YRST_FROMTIME and yrst.YRST_UNTILTIME-1) and yrst.YRST_YRMS_ID=yrms.YRMS_ID and rownum=1) YRST_ID,
  (select yrst.YRST_YRSC_ID from yrst where ((select WGBS_DATEVALUE from wgbs) between yrst.YRST_FROMTIME and yrst.YRST_UNTILTIME-1) and yrst.YRST_YRMS_ID=yrms.YRMS_ID and rownum=1) YRST_YRSC_ID,
  (select yrsc.YRSC_SHORTDESC from yrsc,yrst where yrsc.YRSC_ID=yrst.YRST_YRSC_ID and ((select WGBS_DATEVALUE from wgbs) between yrst.YRST_FROMTIME and yrst.YRST_UNTILTIME-1) and yrst.YRST_YRMS_ID=yrms.YRMS_ID and rownum=1) YRSC_SHORTDESC,
  (select yrst.YRST_FROMTIME from yrst where ((select WGBS_DATEVALUE from wgbs) between yrst.YRST_FROMTIME and yrst.YRST_UNTILTIME-1) and yrst.YRST_YRMS_ID=yrms.YRMS_ID and rownum=1) YRST_FROMTIME,
  (select yrst.YRST_UNTILTIME from yrst where ((select WGBS_DATEVALUE from wgbs) between yrst.YRST_FROMTIME and yrst.YRST_UNTILTIME-1) and yrst.YRST_YRMS_ID=yrms.YRMS_ID and rownum=1) YRST_UNTILTIME,
  (select yrst.YRST_ID from yrst where (select WGBS_DATEVALUE from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yrms.YRMS_ID) NEXT_YRST_ID,
  (select yrst.YRST_YRSC_ID from yrst where (select WGBS_DATEVALUE from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yrms.YRMS_ID) NEXT_YRST_YRSC_ID,
  (select yrsc.YRSC_SHORTDESC from yrsc,yrst where yrsc.YRSC_ID=yrst.YRST_YRSC_ID and (select WGBS_DATEVALUE from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yrms.YRMS_ID) NEXT_YRSC_SHORTDESC,
  (select yrst.YRST_FROMTIME from yrst where (select WGBS_DATEVALUE from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yrms.YRMS_ID) NEXT_YRST_FROMTIME,
  (select yrst.YRST_UNTILTIME from yrst where (select WGBS_DATEVALUE from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yrms.YRMS_ID) NEXT_YRST_UNTILTIME,
  decode((select yrst.YRST_ID from yrst where (select WGBS_DATEVALUE from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yrms.YRMS_ID),NULL,(select yrst.YRST_ID from yrst where ((select WGBS_DATEVALUE from wgbs) between yrst.YRST_FROMTIME and yrst.YRST_UNTILTIME-1) and yrst.YRST_YRMS_ID=yrms.YRMS_ID and rownum=1),((select yrst.YRST_ID from yrst where (select WGBS_DATEVALUE from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yrms.YRMS_ID))) BOTH_YRST_ID,
  decode(decode((select yrst.YRST_ID from yrst where (select WGBS_DATEVALUE from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yrms.YRMS_ID),NULL,(select yrst.YRST_ID from yrst where ((select WGBS_DATEVALUE from wgbs) between yrst.YRST_FROMTIME and yrst.YRST_UNTILTIME-1) and yrst.YRST_YRMS_ID=yrms.YRMS_ID and rownum=1),((select yrst.YRST_ID from yrst where (select WGBS_DATEVALUE from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yrms.YRMS_ID))),NULL,0,1) IS_OOO,
  decode((select yrst.YRST_ID from yrst where (select WGBS_DATEVALUE from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yrms.YRMS_ID),NULL,(select yrsc.YRSC_SHORTDESC from yrsc,yrst where yrsc.YRSC_ID=yrst.YRST_YRSC_ID and ((select WGBS_DATEVALUE from wgbs) between yrst.YRST_FROMTIME and yrst.YRST_UNTILTIME-1) and yrst.YRST_YRMS_ID=yrms.YRMS_ID and rownum=1),(select yrsc.YRSC_SHORTDESC from yrsc,yrst where yrsc.YRSC_ID=yrst.YRST_YRSC_ID and (select WGBS_DATEVALUE from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yrms.YRMS_ID)) BOTH_YRSC_SHORTDESC,
  decode((select yrst.YRST_ID from yrst where (select WGBS_DATEVALUE from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yrms.YRMS_ID),NULL,(select yrst.YRST_FROMTIME from yrst where ((select WGBS_DATEVALUE from wgbs) between yrst.YRST_FROMTIME and yrst.YRST_UNTILTIME-1) and yrst.YRST_YRMS_ID=yrms.YRMS_ID and rownum=1),((select yrst.YRST_FROMTIME from yrst where (select WGBS_DATEVALUE from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yrms.YRMS_ID))) BOTH_YRST_FROMTIME,
  decode((select yrst.YRST_ID from yrst where (select WGBS_DATEVALUE from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yrms.YRMS_ID),NULL,(select yrst.YRST_UNTILTIME from yrst where ((select WGBS_DATEVALUE from wgbs) between yrst.YRST_FROMTIME and yrst.YRST_UNTILTIME-1) and yrst.YRST_YRMS_ID=yrms.YRMS_ID and rownum=1),((select yrst.YRST_UNTILTIME from yrst where (select WGBS_DATEVALUE from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yrms.YRMS_ID))) BOTH_YRST_UNTILTIME,
  decode((select yrst.YRST_ID from yrst where (select WGBS_DATEVALUE from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yrms.YRMS_ID),NULL,(decode((select yrst.YRST_ID from yrst where ((select WGBS_DATEVALUE from wgbs) between yrst.YRST_FROMTIME and yrst.YRST_UNTILTIME-1) and yrst.YRST_YRMS_ID=yrms.YRMS_ID and rownum=1),NULL,(select yhks.YHKS_SHORTDESC from yhks where yhks.YHKS_ID=yrms.YRMS_YHKS_ID),(select yrsc.YRSC_SHORTDESC from yrsc,yrst where yrsc.YRSC_ID=yrst.YRST_YRSC_ID and ((select WGBS_DATEVALUE from wgbs) between yrst.YRST_FROMTIME and yrst.YRST_UNTILTIME-1) and yrst.YRST_YRMS_ID=yrms.YRMS_ID and rownum=1))),(select yrsc.YRSC_SHORTDESC from yrsc,yrst where yrsc.YRSC_ID=yrst.YRST_YRSC_ID and (select WGBS_DATEVALUE from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yrms.YRMS_ID)) ROOM_STATUS,
  (select yres.YRES_ID from yres where (select WGBS_DATEVALUE from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yrms.YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select WGBS_DATEVALUE from wgbs) and rownum=1) STAY_OVER_YRES_ID,
  decode((select yres.YRES_ID from yres where (select WGBS_DATEVALUE from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yrms.YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select WGBS_DATEVALUE from wgbs) and rownum=1),NULL,0,1) STAY_OVER,
  (select yres.YRES_ID from yres,ydet where yres.YRES_CURRENT_YDET_ID=ydet.YDET_ID and ydet.YDET_DATE+1=(select WGBS_DATEVALUE from wgbs) and ydet.YDET_YRMS_ID=yrms.YRMS_ID and yres.YRES_RESSTATUS<>3 and rownum=1) DEP_YRES_ID,
  decode((select yres.YRES_ID from yres,ydet where yres.YRES_CURRENT_YDET_ID=ydet.YDET_ID and ydet.YDET_DATE+1=(select WGBS_DATEVALUE from wgbs) and ydet.YDET_YRMS_ID=yrms.YRMS_ID and yres.YRES_RESSTATUS<>3 and rownum=1),NULL,0,1) DEPARTURE,
  (select yres.YRES_ACTDEPTIMENULL from yres,ydet where yres.YRES_CURRENT_YDET_ID=ydet.YDET_ID and ydet.YDET_DATE+1=(select WGBS_DATEVALUE from wgbs) and ydet.YDET_YRMS_ID=yrms.YRMS_ID and rownum=1) DEP_YRES_ACTDEPTIMENULL,
  (select yres.YRES_ID from yres,ydet where yres.YRES_CURRENT_YDET_ID=ydet.YDET_ID and ydet.YDET_DATE=(select WGBS_DATEVALUE from wgbs) and ydet.YDET_YRMS_ID=yrms.YRMS_ID and rownum=1 and (YRES.YRES_EXPARRTIME)=(select WGBS_DATEVALUE from wgbs)) ARR_YRES_ID,
  decode((select yres.YRES_ID from yres,ydet where yres.YRES_CURRENT_YDET_ID=ydet.YDET_ID and ydet.YDET_DATE=(select WGBS_DATEVALUE from wgbs) and ydet.YDET_YRMS_ID=yrms.YRMS_ID and rownum=1 and (YRES.YRES_EXPARRTIME)=(select WGBS_DATEVALUE from wgbs)),null,0,1) ARRIVAL,
  (select yres.YRES_ACTARRTIMENULL from yres,ydet where yres.YRES_CURRENT_YDET_ID=ydet.YDET_ID and ydet.YDET_DATE=(select WGBS_DATEVALUE from wgbs) and ydet.YDET_YRMS_ID=yrms.YRMS_ID and rownum=1 and (YRES.YRES_EXPARRTIME)=(select WGBS_DATEVALUE from wgbs)) ARR_YRES_ACTARRTIMENULL,
  decode((select yres.YRES_ID from yres where (select WGBS_DATEVALUE from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yrms.YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select WGBS_DATEVALUE from wgbs) and rownum=1),NULL,(select yres.YRES_ID from yres,ydet where yres.YRES_CURRENT_YDET_ID=ydet.YDET_ID and ydet.YDET_DATE+1=(select WGBS_DATEVALUE from wgbs) and ydet.YDET_YRMS_ID=yrms.YRMS_ID and rownum=1),(select yres.YRES_ID from yres where (select WGBS_DATEVALUE from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yrms.YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select WGBS_DATEVALUE from wgbs) and rownum=1)) STAY_DEP_YRES_ID,
  decode((select ydet.YDET_ADULTNO from yres,ydet where yres.YRES_CURRENT_YDET_ID=ydet.YDET_ID and ydet.YDET_DATE+1=(select WGBS_DATEVALUE from wgbs) and ydet.YDET_YRMS_ID=yrms.YRMS_ID and rownum=1),NULL,(select ydet.YDET_ADULTNO from yres,ydet where yres.YRES_CURRENT_YDET_ID=ydet.YDET_ID and ydet.YDET_DATE=(select WGBS_DATEVALUE from wgbs) and ydet.YDET_YRMS_ID=yrms.YRMS_ID and rownum=1),(select ydet.YDET_ADULTNO from yres,ydet where yres.YRES_CURRENT_YDET_ID=ydet.YDET_ID and ydet.YDET_DATE+1=(select WGBS_DATEVALUE from wgbs) and ydet.YDET_YRMS_ID=yrms.YRMS_ID and rownum=1)) STAY_DEP_YDET_ADULTNO,
  decode((select yres.YRES_XCMS_ID from yres where (select WGBS_DATEVALUE from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yrms.YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select WGBS_DATEVALUE from wgbs) and rownum=1),NULL,(select yres.YRES_XCMS_ID from yres,ydet where yres.YRES_CURRENT_YDET_ID=ydet.YDET_ID and ydet.YDET_DATE+1=(select WGBS_DATEVALUE from wgbs) and ydet.YDET_YRMS_ID=yrms.YRMS_ID and rownum=1),(select yres.YRES_XCMS_ID from yres where (select WGBS_DATEVALUE from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yrms.YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select WGBS_DATEVALUE from wgbs) and rownum=1)) STAY_DEP_XCMS_ID,
  (select v8_rep_name.NAME2 from v8_rep_name where v8_rep_name.XCMS_ID=(decode((select yres.YRES_XCMS_ID from yres where (select WGBS_DATEVALUE from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yrms.YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select WGBS_DATEVALUE from wgbs) and rownum=1),NULL,(select yres.YRES_XCMS_ID from yres,ydet where yres.YRES_CURRENT_YDET_ID=ydet.YDET_ID and ydet.YDET_DATE+1=(select WGBS_DATEVALUE from wgbs) and ydet.YDET_YRMS_ID=yrms.YRMS_ID and rownum=1),(select yres.YRES_XCMS_ID from yres where (select WGBS_DATEVALUE from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yrms.YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select WGBS_DATEVALUE from wgbs) and rownum=1)))) STAY_DEP_NAME,
  decode((select yres.YRES_ID from yres where (select WGBS_DATEVALUE from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yrms.YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select WGBS_DATEVALUE from wgbs) and rownum=1),NULL,(select yres.YRES_EXPARRTIME from yres,ydet where yres.YRES_CURRENT_YDET_ID=ydet.YDET_ID and ydet.YDET_DATE+1=(select WGBS_DATEVALUE from wgbs) and ydet.YDET_YRMS_ID=yrms.YRMS_ID and rownum=1),(select yres.YRES_EXPARRTIME from yres where (select WGBS_DATEVALUE from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yrms.YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select WGBS_DATEVALUE from wgbs) and rownum=1)) STAY_DEP_YRES_EXPARRTIME,
  decode((select yres.YRES_ID from yres where (select WGBS_DATEVALUE from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yrms.YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select WGBS_DATEVALUE from wgbs) and rownum=1),NULL,(select yres.YRES_EXPDEPTIME from yres,ydet where yres.YRES_CURRENT_YDET_ID=ydet.YDET_ID and ydet.YDET_DATE+1=(select WGBS_DATEVALUE from wgbs) and ydet.YDET_YRMS_ID=yrms.YRMS_ID and rownum=1),(select yres.YRES_EXPDEPTIME from yres where (select WGBS_DATEVALUE from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yrms.YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select WGBS_DATEVALUE from wgbs) and rownum=1)) STAY_DEP_YRES_EXPDEPTIME,
  decode((select yres.YRES_ID from yres where (select WGBS_DATEVALUE from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yrms.YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select WGBS_DATEVALUE from wgbs) and rownum=1),NULL,(select yres.YRES_FREETEXT1 from yres,ydet where yres.YRES_CURRENT_YDET_ID=ydet.YDET_ID and ydet.YDET_DATE+1=(select WGBS_DATEVALUE from wgbs) and ydet.YDET_YRMS_ID=yrms.YRMS_ID and rownum=1),(select yres.YRES_FREETEXT1 from yres where (select WGBS_DATEVALUE from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yrms.YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select WGBS_DATEVALUE from wgbs) and rownum=1)) STAY_DEP_YRES_FREETEXT1,
  decode((select yres.YRES_ID from yres where (select WGBS_DATEVALUE from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yrms.YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select WGBS_DATEVALUE from wgbs) and rownum=1),NULL,(select yres.YRES_FREETEXT2 from yres,ydet where yres.YRES_CURRENT_YDET_ID=ydet.YDET_ID and ydet.YDET_DATE+1=(select WGBS_DATEVALUE from wgbs) and ydet.YDET_YRMS_ID=yrms.YRMS_ID and rownum=1),(select yres.YRES_FREETEXT2 from yres where (select WGBS_DATEVALUE from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yrms.YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select WGBS_DATEVALUE from wgbs) and rownum=1)) STAY_DEP_YRES_FREETEXT2,
  (select xpda.XPDA_SHORTDESC from xpda,xpal where xpda.XPDA_ID=xpal.XPAL_XPDA_ID and xpal.XPAL_XPAC_ID=11 and xpal.XPAL_XCMS_ID=(decode((select yres.YRES_XCMS_ID from yres where (select WGBS_DATEVALUE from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yrms.YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select WGBS_DATEVALUE from wgbs) and rownum=1),NULL,(select yres.YRES_XCMS_ID from yres,ydet where yres.YRES_CURRENT_YDET_ID=ydet.YDET_ID and ydet.YDET_DATE+1=(select WGBS_DATEVALUE from wgbs) and ydet.YDET_YRMS_ID=yrms.YRMS_ID and rownum=1),(select yres.YRES_XCMS_ID from yres where (select WGBS_DATEVALUE from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yrms.YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select WGBS_DATEVALUE from wgbs) and rownum=1)))) STAY_DEP_VIP,
  (select yres.YRES_XCMS_ID from yres,ydet where yres.YRES_CURRENT_YDET_ID=ydet.YDET_ID and ydet.YDET_DATE=(select WGBS_DATEVALUE from wgbs) and ydet.YDET_YRMS_ID=yrms.YRMS_ID and rownum=1 and (YRES.YRES_EXPARRTIME)=(select WGBS_DATEVALUE from wgbs)) ARR_XCMS_ID,
  (select v8_rep_name.NAME2 from v8_rep_name where v8_rep_name.XCMS_ID=(select yres.YRES_XCMS_ID from yres,ydet where yres.YRES_CURRENT_YDET_ID=ydet.YDET_ID and ydet.YDET_DATE=(select WGBS_DATEVALUE from wgbs) and ydet.YDET_YRMS_ID=yrms.YRMS_ID and rownum=1 and (YRES.YRES_EXPARRTIME)=(select WGBS_DATEVALUE from wgbs))) ARR_NAME,
  (select yres.YRES_EXPARRTIME from yres,ydet where yres.YRES_CURRENT_YDET_ID=ydet.YDET_ID and ydet.YDET_DATE=(select WGBS_DATEVALUE from wgbs) and ydet.YDET_YRMS_ID=yrms.YRMS_ID and rownum=1 and (YRES.YRES_EXPARRTIME)=(select WGBS_DATEVALUE from wgbs)) ARR_YRES_EXPARRTIME,
  (select yres.YRES_EXPDEPTIME from yres,ydet where yres.YRES_CURRENT_YDET_ID=ydet.YDET_ID and ydet.YDET_DATE=(select WGBS_DATEVALUE from wgbs) and ydet.YDET_YRMS_ID=yrms.YRMS_ID and rownum=1 and (YRES.YRES_EXPARRTIME)=(select WGBS_DATEVALUE from wgbs)) ARR_YRES_EXPDEPTIME,
  (select yres.YRES_FREETEXT1 from yres,ydet where yres.YRES_CURRENT_YDET_ID=ydet.YDET_ID and ydet.YDET_DATE=(select WGBS_DATEVALUE from wgbs) and ydet.YDET_YRMS_ID=yrms.YRMS_ID and rownum=1 and (YRES.YRES_EXPARRTIME)=(select WGBS_DATEVALUE from wgbs)) ARR_YRES_FREETEXT1,
  (select yres.YRES_FREETEXT2 from yres,ydet where yres.YRES_CURRENT_YDET_ID=ydet.YDET_ID and ydet.YDET_DATE=(select WGBS_DATEVALUE from wgbs) and ydet.YDET_YRMS_ID=yrms.YRMS_ID and rownum=1 and (YRES.YRES_EXPARRTIME)=(select WGBS_DATEVALUE from wgbs)) ARR_YRES_FREETEXT2,
  (select ydet.YDET_ADULTNO from yres,ydet where yres.YRES_CURRENT_YDET_ID=ydet.YDET_ID and ydet.YDET_DATE=(select WGBS_DATEVALUE from wgbs) and ydet.YDET_YRMS_ID=yrms.YRMS_ID and rownum=1 and (YRES.YRES_EXPARRTIME)=(select WGBS_DATEVALUE from wgbs)) ARR_YDET_ADULTNO,
  (select xpda.XPDA_SHORTDESC from xpda,xpal where xpda.XPDA_ID=xpal.XPAL_XPDA_ID and xpal.XPAL_XPAC_ID=11 and xpal.XPAL_XCMS_ID=(select yres.YRES_XCMS_ID from yres,ydet where yres.YRES_CURRENT_YDET_ID=ydet.YDET_ID and ydet.YDET_DATE=(select WGBS_DATEVALUE from wgbs) and ydet.YDET_YRMS_ID=yrms.YRMS_ID and rownum=1 and (YRES.YRES_EXPARRTIME)=(select WGBS_DATEVALUE from wgbs))) ARR_VIP,
  (case when (decode((select yres.YRES_ID from yres where (select WGBS_DATEVALUE from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yrms.YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select WGBS_DATEVALUE from wgbs) and rownum=1),NULL,0,1)=1 or decode((select yres.YRES_ID from yres,ydet where yres.YRES_CURRENT_YDET_ID=ydet.YDET_ID and ydet.YDET_DATE=(select WGBS_DATEVALUE from wgbs) and ydet.YDET_YRMS_ID=yrms.YRMS_ID and rownum=1 and (YRES.YRES_EXPARRTIME)=(select WGBS_DATEVALUE from wgbs)),null,0,1)=1 or decode(decode((select yrst.YRST_ID from yrst where (select WGBS_DATEVALUE from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yrms.YRMS_ID),NULL,(select yrst.YRST_ID from yrst where ((select WGBS_DATEVALUE from wgbs) between yrst.YRST_FROMTIME and yrst.YRST_UNTILTIME-1) and yrst.YRST_YRMS_ID=yrms.YRMS_ID and rownum=1),((select yrst.YRST_ID from yrst where (select WGBS_DATEVALUE from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yrms.YRMS_ID))),NULL,0,1)=1) then 0 else 1 end) FREEROOM,
  (select min(ydet.YDET_DATE) from ydet,yres where ydet.YDET_YRES_ID=yres.YRES_ID and yres.YRES_RESSTATUS<>3 and yres.YRES_EXPARRTIME>(select WGBS_DATEVALUE from wgbs) and ydet.YDET_YRMS_ID=yrms.YRMS_ID) NEXT_ARRIVAL
FROM
  yrms,yhrl
where
  yrms.YRMS_ID=yhrl.YHRL_YRMS_ID and
  yhrl.YHRL_DATE=(select WGBS_DATEVALUE from wgbs); 

CREATE OR REPLACE VIEW V8_REP_NOOF_C_H_SHARE_D_USE 
AS
select wdat.WDAT_DATE WDAT_DATE,
nvl(ydet.YDET_SHARENUM,0) SHARE_NUM,
decode(nvl(ydet_sharenum,0),0,sum(ydet_noofrooms),avg(ydet_noofrooms)) C_H_ROOMS,
yrch.YRCH_COMPSTATUS YRCH_COMPSTATUS
from yres y,ydet,yrch,wdat
where
   yres_id=ydet_yres_id
and yres_resstatus<> 3
and ydet_date=wdat.WDAT_DATE(+)
and yres_expdeptime=yres_exparrtime
and yrch_id=ydet_yrch_id
group by wdat.WDAT_DATE,ydet_sharenum,yrch.YRCH_COMPSTATUS;

CREATE OR REPLACE VIEW V8_REP_NOOF_C_H_PER_DAY_D_USE
AS
select V8_REP_NOOF_C_H_SHARE_D_USE.WDAT_DATE WDAT_DATE,
sum(decode(V8_REP_NOOF_C_H_SHARE_D_USE.YRCH_COMPSTATUS,1,V8_REP_NOOF_C_H_SHARE_D_USE.C_H_ROOMS,0)) COMP_ROOMS,
sum(decode(V8_REP_NOOF_C_H_SHARE_D_USE.YRCH_COMPSTATUS,2,V8_REP_NOOF_C_H_SHARE_D_USE.C_H_ROOMS,0)) HOUSE_ROOMS
from V8_REP_NOOF_C_H_SHARE_D_USE
group by V8_REP_NOOF_C_H_SHARE_D_USE.WDAT_DATE;

CREATE OR REPLACE VIEW V8_REP_SALES_SHEET_REV_YCAD 
AS
SELECT
    SRDS.SRDS_DATE,
    SRDS.SRDS_YCAD_ID,
    (select YCAD.YCAD_SHORTDESC from YCAD where YCAD.YCAD_ID = SRDS.SRDS_YCAD_ID) YCAD_SHORTDESC,
    (select YCAD.YCAD_LONGDESC from YCAD where YCAD.YCAD_ID = SRDS.SRDS_YCAD_ID) YCAD_LONGDESC,
    ADD_MONTHS(SRDS.SRDS_DATE,-12) LY_SRDS_DATE,
    SRDS.SRDS_ZDCO_ID,
    SRDS.SRDS_ZDCO_NUMERICDESC,
    SRDS.SRDS_ZDCO_CDT,
    NVL((SELECT ZDCO.ZDCO_INACTIVE FROM ZDCO WHERE ZDCO.ZDCO_ID=SRDS.SRDS_ZDCO_ID),0) ZDCO_INACTIVE,
    NVL((SELECT ZDCO.ZDCO_STATS_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=SRDS.SRDS_ZDCO_ID),0) ZDCO_STATS_TYPE,
    (SELECT ZDCO.ZDCO_ZDCG_ID FROM ZDCO WHERE ZDCO.ZDCO_ID=SRDS.SRDS_ZDCO_ID) ZDCO_ZDCG_ID,
    (SELECT ZDCG.ZDCG_SHORTDESC FROM ZDCG WHERE ZDCG.ZDCG_ID=(SELECT ZDCO.ZDCO_ZDCG_ID FROM ZDCO WHERE ZDCO.ZDCO_ID=SRDS.SRDS_ZDCO_ID)) ZDCG_SHORTDESC,
    SRDS.SRDS_ZDCG_LONGDESC,
    SRDS.SRDS_NETREV_D,
    NVL((SELECT srds_s.SRDS_NETREV_D FROM SRDS srds_s WHERE srds_s.SRDS_DATE=ADD_MONTHS(SRDS.SRDS_DATE,-12) AND SRDS_s.SRDS_ZDCO_ID=SRDS.SRDS_ZDCO_ID and srds_s.SRDS_YCAD_ID = srds.SRDS_YCAD_ID ),0) SRDS_NETREV_DLY,
    NVL((SELECT SBUD.SBUD_VALUE FROM SBUD WHERE SBUD.SBUD_DATE=SRDS.SRDS_DATE AND SBUD.SBUD_TABLE_ID=SRDS.SRDS_ZDCO_ID AND UPPER(SBUD.SBUD_TABLENAME)='ZDCO' AND SBUD.SBUD_TYPE=1),0) SBUD_NETREV_D,
    NVL((SELECT SBUD.SBUD_VALUE FROM SBUD WHERE SBUD.SBUD_DATE=ADD_MONTHS(SRDS.SRDS_DATE,-12) AND SBUD.SBUD_TABLE_ID=SRDS.SRDS_ZDCO_ID AND UPPER(SBUD.SBUD_TABLENAME)='ZDCO' AND SBUD.SBUD_TYPE=1),0) SBUD_NETREV_DLY,
    SRDS.SRDS_NETREV_M SRDS_NETREV_M,
    NVL((SELECT SRDS_s.SRDS_NETREV_M FROM SRDS SRDS_s WHERE SRDS_s.SRDS_DATE=ADD_MONTHS(SRDS.SRDS_DATE,-12) AND SRDS_s.SRDS_ZDCO_ID=SRDS.SRDS_ZDCO_ID and srds_s.SRDS_YCAD_ID = srds.SRDS_YCAD_ID),0) SRDS_NETREV_MLY,
    NVL((SELECT SBUD.SBUD_MONTH_VALUE FROM SBUD WHERE SBUD.SBUD_DATE=SRDS.SRDS_DATE AND SBUD.SBUD_TABLE_ID=SRDS.SRDS_ZDCO_ID AND UPPER(SBUD.SBUD_TABLENAME)='ZDCO' AND SBUD.SBUD_TYPE=1),0) SBUD_NETREV_M,
    NVL((SELECT SBUD.SBUD_MONTH_VALUE FROM SBUD WHERE SBUD.SBUD_DATE=ADD_MONTHS(SRDS.SRDS_DATE,-12) AND SBUD.SBUD_TABLE_ID=SRDS.SRDS_ZDCO_ID AND UPPER(SBUD.SBUD_TABLENAME)='ZDCO' AND SBUD.SBUD_TYPE=1),0) SBUD_NETREV_MLY,
    NVL((SELECT SUM(SBUD.SBUD_VALUE) FROM SBUD WHERE SBUD.SBUD_DATE BETWEEN (SELECT v8_rep_dates.FI_FIRST_OF_MONTH FROM v8_rep_dates WHERE v8_rep_dates.FIDELIODATE=SRDS.SRDS_DATE) AND SRDS.SRDS_DATE AND SBUD.SBUD_TABLE_ID=SRDS.SRDS_ZDCO_ID AND UPPER(SBUD.SBUD_TABLENAME)='ZDCO' AND SBUD.SBUD_TYPE=1),0) SBUD_NETREV_M_COM,
    NVL((SELECT SUM(SBUD.SBUD_VALUE) FROM SBUD WHERE SBUD.SBUD_DATE BETWEEN ADD_MONTHS((SELECT v8_rep_dates.FI_FIRST_OF_MONTH FROM v8_rep_dates WHERE v8_rep_dates.FIDELIODATE=SRDS.SRDS_DATE),-12) AND ADD_MONTHS(SRDS.SRDS_DATE,-12) AND SBUD.SBUD_TABLE_ID=SRDS.SRDS_ZDCO_ID AND UPPER(SBUD.SBUD_TABLENAME)='ZDCO' AND SBUD.SBUD_TYPE=1),0) SBUD_NETREV_MLY_COM,
    SRDS.SRDS_NETREV_Y SRDS_NETREV_Y,
    --
    NVL((SELECT SRDS_s.SRDS_NETREV_Y FROM SRDS SRDS_s WHERE SRDS_s.SRDS_DATE=ADD_MONTHS(SRDS.SRDS_DATE,-12) AND SRDS_s.SRDS_ZDCO_ID=SRDS.SRDS_ZDCO_ID and srds_s.SRDS_YCAD_ID = srds.SRDS_YCAD_ID),0) SRDS_NETREV_YLY,
    NVL((SELECT SUM(SBUD.SBUD_VALUE) FROM SBUD WHERE TO_CHAR(SBUD.SBUD_DATE,'YYYY')=TO_CHAR(SRDS.SRDS_DATE,'YYYY') AND SBUD.SBUD_TABLE_ID=SRDS.SRDS_ZDCO_ID AND UPPER(SBUD.SBUD_TABLENAME)='ZDCO' AND SBUD.SBUD_TYPE=1),0) SBUD_NETREV_Y,
    NVL((SELECT SUM(SBUD.SBUD_VALUE) FROM SBUD WHERE TO_CHAR(SBUD.SBUD_DATE,'YYYY')=TO_CHAR(ADD_MONTHS(SRDS.SRDS_DATE,-12),'YYYY') AND SBUD.SBUD_TABLE_ID=SRDS.SRDS_ZDCO_ID AND UPPER(SBUD.SBUD_TABLENAME)='ZDCO' AND SBUD.SBUD_TYPE=1),0) SBUD_NETREV_YLY,
    NVL((SELECT SUM(SBUD.SBUD_VALUE) FROM SBUD WHERE SBUD.SBUD_DATE BETWEEN (SELECT v8_rep_dates.FI_FIRST_OF_YEAR FROM v8_rep_dates WHERE v8_rep_dates.FIDELIODATE=SRDS.SRDS_DATE) AND SRDS.SRDS_DATE AND SBUD.SBUD_TABLE_ID=SRDS.SRDS_ZDCO_ID AND UPPER(SBUD.SBUD_TABLENAME)='ZDCO' AND SBUD.SBUD_TYPE=1),0) SBUD_NETREV_Y_COM,
    NVL((SELECT SUM(SBUD.SBUD_VALUE) FROM SBUD WHERE SBUD.SBUD_DATE BETWEEN ADD_MONTHS((SELECT v8_rep_dates.FI_FIRST_OF_YEAR FROM v8_rep_dates WHERE v8_rep_dates.FIDELIODATE=SRDS.SRDS_DATE),-12) AND ADD_MONTHS(SRDS.SRDS_DATE,-12) AND SBUD.SBUD_TABLE_ID=SRDS.SRDS_ZDCO_ID AND UPPER(SBUD.SBUD_TABLENAME)='ZDCO' AND SBUD.SBUD_TYPE=1),0) SBUD_NETREV_YLY_COM,
    SRDS.SRDS_GROSSREV_D SRDS_GROSSREV_D,
    NVL((SELECT SRDS_s.SRDS_GROSSREV_D FROM SRDS SRDS_s WHERE SRDS_s.SRDS_DATE=ADD_MONTHS(SRDS.SRDS_DATE,-12) AND SRDS_s.SRDS_ZDCO_ID=SRDS.SRDS_ZDCO_ID and srds_s.SRDS_YCAD_ID = srds.SRDS_YCAD_ID),0) SRDS_GROSSREV_DLY,
    NVL((SELECT SBUD.SBUD_VALUE FROM SBUD WHERE SBUD.SBUD_DATE=SRDS.SRDS_DATE AND SBUD.SBUD_TABLE_ID=SRDS.SRDS_ZDCO_ID AND UPPER(SBUD.SBUD_TABLENAME)='ZDCO' AND SBUD.SBUD_TYPE=2),0) SBUD_GROSSREV_D,
    NVL((SELECT SBUD.SBUD_VALUE FROM SBUD WHERE SBUD.SBUD_DATE=ADD_MONTHS(SRDS.SRDS_DATE,-12) AND SBUD.SBUD_TABLE_ID=SRDS.SRDS_ZDCO_ID AND UPPER(SBUD.SBUD_TABLENAME)='ZDCO' AND SBUD.SBUD_TYPE=2),0) SBUD_GROSSREV_DLY,
    SRDS.SRDS_GROSSREV_M SRDS_GROSSREV_M,
    NVL((SELECT SRDS_s.SRDS_GROSSREV_M FROM SRDS SRDS_s WHERE SRDS_s.SRDS_DATE=ADD_MONTHS(SRDS.SRDS_DATE,-12) AND SRDS_s.SRDS_ZDCO_ID=SRDS.SRDS_ZDCO_ID and srds_s.SRDS_YCAD_ID = srds.SRDS_YCAD_ID),0) SRDS_GROSSREV_MLY,
    NVL((SELECT SBUD.SBUD_MONTH_VALUE FROM SBUD WHERE SBUD.SBUD_DATE=SRDS.SRDS_DATE AND SBUD.SBUD_TABLE_ID=SRDS.SRDS_ZDCO_ID AND UPPER(SBUD.SBUD_TABLENAME)='ZDCO' AND SBUD.SBUD_TYPE=2),0) SBUD_GROSSREV_M,
    NVL((SELECT SBUD.SBUD_MONTH_VALUE FROM SBUD WHERE SBUD.SBUD_DATE=ADD_MONTHS(SRDS.SRDS_DATE,-12) AND SBUD.SBUD_TABLE_ID=SRDS.SRDS_ZDCO_ID AND UPPER(SBUD.SBUD_TABLENAME)='ZDCO' AND SBUD.SBUD_TYPE=2),0) SBUD_GROSSREV_MLY,
    NVL((SELECT SUM(SBUD.SBUD_VALUE) FROM SBUD WHERE SBUD.SBUD_DATE BETWEEN (SELECT v8_rep_dates.FI_FIRST_OF_MONTH FROM v8_rep_dates WHERE v8_rep_dates.FIDELIODATE=SRDS.SRDS_DATE) AND SRDS.SRDS_DATE AND SBUD.SBUD_TABLE_ID=SRDS.SRDS_ZDCO_ID AND UPPER(SBUD.SBUD_TABLENAME)='ZDCO' AND SBUD.SBUD_TYPE=2),0) SBUD_GROSSREV_M_COM,
    NVL((SELECT SUM(SBUD.SBUD_VALUE) FROM SBUD WHERE SBUD.SBUD_DATE BETWEEN ADD_MONTHS((SELECT v8_rep_dates.FI_FIRST_OF_MONTH FROM v8_rep_dates WHERE v8_rep_dates.FIDELIODATE=SRDS.SRDS_DATE),-12) AND ADD_MONTHS(SRDS.SRDS_DATE,-12) AND SBUD.SBUD_TABLE_ID=SRDS.SRDS_ZDCO_ID AND UPPER(SBUD.SBUD_TABLENAME)='ZDCO' AND SBUD.SBUD_TYPE=2),0) SBUD_GROSSREV_MLY_COM,
    SRDS.SRDS_GROSSREV_Y SRDS_GROSSREV_Y,
    NVL((SELECT SRDS_s.SRDS_GROSSREV_Y FROM SRDS SRDS_s WHERE SRDS_s.SRDS_DATE=ADD_MONTHS(SRDS.SRDS_DATE,-12) AND SRDS_s.SRDS_ZDCO_ID=SRDS.SRDS_ZDCO_ID and srds_s.SRDS_YCAD_ID = srds.SRDS_YCAD_ID),0) SRDS_GROSSREV_YLY,
    NVL((SELECT SUM(SBUD.SBUD_VALUE) FROM SBUD WHERE TO_CHAR(SBUD.SBUD_DATE,'YYYY')=TO_CHAR(SRDS.SRDS_DATE,'YYYY') AND SBUD.SBUD_TABLE_ID=SRDS.SRDS_ZDCO_ID AND UPPER(SBUD.SBUD_TABLENAME)='ZDCO' AND SBUD.SBUD_TYPE=2),0) SBUD_GROSSREV_Y,
    NVL((SELECT SUM(SBUD.SBUD_VALUE) FROM SBUD WHERE TO_CHAR(SBUD.SBUD_DATE,'YYYY')=TO_CHAR(ADD_MONTHS(SRDS.SRDS_DATE,-12),'YYYY') AND SBUD.SBUD_TABLE_ID=SRDS.SRDS_ZDCO_ID AND UPPER(SBUD.SBUD_TABLENAME)='ZDCO' AND SBUD.SBUD_TYPE=2),0) SBUD_GROSSREV_YLY,
    NVL((SELECT SUM(SBUD.SBUD_VALUE) FROM SBUD WHERE SBUD.SBUD_DATE BETWEEN (SELECT v8_rep_dates.FI_FIRST_OF_YEAR FROM v8_rep_dates WHERE v8_rep_dates.FIDELIODATE=SRDS.SRDS_DATE) AND SRDS.SRDS_DATE AND SBUD.SBUD_TABLE_ID=SRDS.SRDS_ZDCO_ID AND UPPER(SBUD.SBUD_TABLENAME)='ZDCO' AND SBUD.SBUD_TYPE=2),0) SBUD_GROSSREV_Y_COM,
    NVL((SELECT SUM(SBUD.SBUD_VALUE) FROM SBUD WHERE SBUD.SBUD_DATE BETWEEN ADD_MONTHS((SELECT v8_rep_dates.FI_FIRST_OF_YEAR FROM v8_rep_dates WHERE v8_rep_dates.FIDELIODATE=SRDS.SRDS_DATE),-12) AND ADD_MONTHS(SRDS.SRDS_DATE,-12) AND SBUD.SBUD_TABLE_ID=SRDS.SRDS_ZDCO_ID AND UPPER(SBUD.SBUD_TABLENAME)='ZDCO' AND SBUD.SBUD_TYPE=2),0) SBUD_GROSSREV_YLY_COM
FROM
SRDS
WHERE SRDS.SRDS_TYPE = 0
order by
SRDS.SRDS_DATE, SRDS.SRDS_ZDCO_NUMERICDESC;

CREATE OR REPLACE FORCE VIEW V8_ED_CCM_PACKAGES
AS
   SELECT ypkd.YPKD_ID YPKD_ID,
          ypkd.YPKD_YBOM_ID YPKD_YBOM_ID,
          ypkd.YPKD_YPKG_ID YPKD_YPKG_ID,
          DECODE (ypkd.YPKD_STARTTIME, YEVM_STARTTIME_S, 1, 2) DAY_FOLLOW,
          ypkd.YPKD_NAME YPKD_NAME,
          ypkd.YPKD_PRICE YPKD_PRICE,
          V8_FNC_VALCURR_FORMATING (ypkd.YPKD_PRICE, ybom.zcur_shortdesc)
             FYPKD_PRICE,
          NVL ( (SELECT SUM (YBEF.YBEF_REV_AMOUNT)
                   FROM ybef
                  WHERE YBEF.YBEF_YPKD_ID = YPKD.YPKD_ID),
               0)
             YPKD_PRICE_GROSS,
          NVL ( (SELECT SUM (YBEF.YBEF_PRICE_NET)
                   FROM ybef
                  WHERE YBEF.YBEF_YPKD_ID = YPKD.YPKD_ID),
               0)
             YPKD_PRICE_NET,
          NVL ( (SELECT SUM (YBEF.YBEF_PRICE_VAT)
                   FROM ybef
                  WHERE YBEF.YBEF_YPKD_ID = YPKD.YPKD_ID),
               0)
             YPKD_PRICE_VAT,
          ypkd.YPKD_NOOFATTENDEES YPKD_NOOFATTENDEES,
          DECODE (NVL (ypkd.YPKD_NOOFATTENDEES, 0),
                  0, TO_CHAR (NULL),
                  TO_CHAR (ypkd.YPKD_NOOFATTENDEES) || ' x')
             FYPKD_NOOFATTENDEES,
          ypkd.YPKD_NOOFATTENDEES * ypkd.YPKD_PRICE TOT_PRICE,
          V8_FNC_VALCURR_FORMATING (
             ypkd.YPKD_NOOFATTENDEES * ypkd.YPKD_PRICE,
             ybom.zcur_shortdesc)
             FTOT_PRICE,
          NVL ( (SELECT SUM (YBEF.YBEF_REV_AMOUNT * ypkd.ypkd_noofattendees)
                   FROM ybef
                  WHERE YBEF.YBEF_YPKD_ID = YPKD.YPKD_ID),
               0)
             TOT_PRICE_GROSS,
          NVL ( (SELECT SUM (YBEF.YBEF_PRICE_NET * ypkd.ypkd_noofattendees)
                   FROM ybef
                  WHERE YBEF.YBEF_YPKD_ID = YPKD.YPKD_ID),
               0)
             TOT_PRICE_NET,
          NVL ( (SELECT SUM (YBEF.YBEF_PRICE_VAT * ypkd.ypkd_noofattendees)
                   FROM ybef
                  WHERE YBEF.YBEF_YPKD_ID = YPKD.YPKD_ID),
               0)
             TOT_PRICE_VAT,
          ypkd.YPKD_STARTTIME YPKD_STARTTIME,
          TRUNC (ypkd.YPKD_STARTTIME) TRUNC_YPKD_STARTTIME,
          TO_NUMBER (TO_CHAR (TRUNC (YEVM_STARTTIME_S), 'J')) DATE_NUMBER,
          ypkd.YPKD_ENDTIME YPKD_ENDTIME,
          YBOM.YBOM_BASE_ZCUR_ID ZCUR_ID,
          YBOM.ZCUR_SHORTDESC,
          (SELECT ypkg_name
             FROM ypkg
            WHERE ypkg_id = ypkd_ypkg_id)
             YPKG_NAME,
          1 CHECKVALUE,
          YPKD_DISPLORDER,
          ypkd_alternative AlternativeFlag,
          v8_fnc_ccm_alternatives ('YPKD',
                                   ypkd_id,
                                   0,
                                   1)
             Alternatives,
          v8_fnc_ccm_alternatives ('YPKD',
                                   ypkd_id,
                                   1,
                                   1)
             AlternativesFrom
     FROM ypkd,
          (SELECT DISTINCT
                  yevm.YEVM_YPKD_ID YEVM_YPKD_ID_S,
                  TRUNC (yevm.YEVM_STARTTIME) YEVM_STARTTIME_S
             FROM ypkd, yevm
            WHERE ypkd.YPKD_ID = yevm.YEVM_YPKD_ID),
          (SELECT ybom_id,
                  YBOM_BASE_ZCUR_ID,
                  (SELECT zcur_shortdesc
                     FROM zcur
                    WHERE zcur_id = ybom_base_zcur_id)
                     zcur_shortdesc
             FROM ybom) ybom
    WHERE     ypkd.YPKD_ID = YEVM_YPKD_ID_S
          AND YPKD.YPKD_YBOM_ID = YBOM.YBOM_ID
          AND YEVM_STARTTIME_S BETWEEN TRUNC (ypkd.YPKD_STARTTIME)
                                   AND TRUNC (ypkd.YPKD_ENDTIME);

CREATE OR REPLACE VIEW V8_REP_TABLERES1
AS 
SELECT 
  (SELECT RRST.RRST_NAME FROM RRST WHERE RRST.RRST_ID = RRES.RRES_RRST_ID) Restaurant, 
  RTAB.RTAB_NUMBER                 TableNumber, 
  RTAB.RTAB_DESCRIPTION      TableName, 
  V8_Sys_Tr_Getrestables(RRES.RRES_ID) ALL_TABLES, 
  TO_CHAR(RRES.RRES_STARTTIME,'hh24:mi')     StartTime, 
  TO_CHAR(RRES.RRES_EndTIME,'hh24:mi')       EndTime, 
  nvl((SELECT v8_rep_name.NAME2 FROM v8_rep_name WHERE v8_rep_name.XCMS_ID = RPRF.RPRF_XCMS_ID and rownum=1), RRES_DESCRIPTION)  GuestName, 
  (SELECT v8_rep_name.NAME3 FROM v8_rep_name WHERE v8_rep_name.XCMS_ID = RPRF.RPRF_XCMS_ID and rownum=1) GuestName_Greet, 
  V8_Fnc_Xpda_Tableres_Infos(RPRF.RPRF_XCMS_ID,1) GUEST_XPDA_SHORT, 
  V8_Fnc_Xpda_Tableres_Infos(RPRF.RPRF_XCMS_ID,2) GUEST_XPDA_LONG, 
  RPRF.RPRF_XCMS_ID, 
  RRES.RRES_DESCRIPTION            Description, 
  RRES.RRES_ID, 
  RRES.RRES_DATE, 
  RRES.RRES_STARTTIME, 
  RRES.RRES_ENDTIME, 
  RRES.RRES_PAX,
  NVL((SELECT SUM(rrgl_number) FROM RRGL,XCAC WHERE RRGL.RRGL_XCAC_ID=XCAC.XCAC_ID AND RRGL.RRGL_RRES_ID = RRES.RRES_ID AND XCAC.XCAC_REPORT_GROUP=1 ),0) TOTALKIDS_G1,
  NVL((SELECT SUM(rrgl_number) FROM RRGL,XCAC WHERE RRGL.RRGL_XCAC_ID=XCAC.XCAC_ID AND RRGL.RRGL_RRES_ID = RRES.RRES_ID AND XCAC.XCAC_REPORT_GROUP=2 ),0) TOTALKIDS_G2,
  NVL((SELECT SUM(rrgl_number) FROM RRGL,XCAC WHERE RRGL.RRGL_XCAC_ID=XCAC.XCAC_ID AND RRGL.RRGL_RRES_ID = RRES.RRES_ID AND XCAC.XCAC_REPORT_GROUP=3 ),0) TOTALKIDS_G3,
  NVL((SELECT SUM(RRGL_NUMBER) FROM RRGL WHERE RRGL_RRES_ID=RRES_ID),0) AS TOTALKIDS,
  RRES.RRES_RRST_ID, 
  RRES.RRES_DESCRIPTION, 
  RRES.RRES_TABLECARD, 
  RRES.RRES_WCOL_ID, 
  RRES.RRES_CANCELED,
  RRES.RRES_CRSSYSTEM, 
  (select yres_id from yres where yres.YRES_XCMS_ID = rprf.Rprf_XCMS_ID and rres.RRES_DATE between yres.YRES_EXPARRTIME and yres.YRES_EXPDEPTIME and rownum=1) YRES_ID, 
  (select yrms.YRMS_SHORTDESC from yres, yrms where yres.YRES_YRMS_ID = yrms.YRMS_ID and yres.YRES_XCMS_ID = rprf.RPRF_XCMS_ID and rres.RRES_DATE between yres.YRES_EXPARRTIME and yres.YRES_EXPDEPTIME and rownum=1) YRMS_SHORTDESC
  FROM 
  RRES, 
  RDET, 
  RPRF, 
  RTAB 
WHERE 
  RDET.RDET_RRES_ID(+) = RRES.RRES_ID AND 
  RDET.RDET_RTAB_ID = RTAB.RTAB_ID(+) AND 
  RPRF.RPRF_RRES_ID(+) = RRES.RRES_ID
ORDER BY RRES.RRES_DATE, Restaurant, RTAB.RTAB_NUMBER;

CREATE OR REPLACE VIEW V8_REP_RRES_INFOS
AS
SELECT 
  (SELECT RRST.RRST_NAME FROM RRST WHERE RRST.RRST_ID = RRES.RRES_RRST_ID) Restaurant,
  V8_Sys_Tr_Getrestables(RRES.RRES_ID) ALL_TABLES,
  V8_Sys_Tr_Getrestables(RRES.RRES_ID) TableNumber, 
  V8_Sys_Tr_GetrestableName(RRES.RRES_ID) TableName, 
  TO_CHAR(RRES.RRES_STARTTIME,'hh24:mi')     StartTime,
  TO_CHAR(RRES.RRES_EndTIME,'hh24:mi')       EndTime,
  nvl((SELECT v8_rep_name.NAME2 FROM v8_rep_name WHERE v8_rep_name.XCMS_ID = V8_SYS_TR_GETFIRSTGUEST(rres_id) and rownum=1), RRES_DESCRIPTION)  GuestName, 
  (SELECT v8_rep_name.NAME3 FROM v8_rep_name WHERE v8_rep_name.XCMS_ID = V8_SYS_TR_GETFIRSTGUEST(rres_id) and rownum=1) GuestName_Greet,
  V8_SYS_TR_GETPROFILESINFO(rres_id, '; ') All_Names, 
  V8_Fnc_Xpda_Tableres_Infos(V8_SYS_TR_GETFIRSTGUEST(rres_id),1) GUEST_XPDA_SHORT,
  V8_Fnc_Xpda_Tableres_Infos(V8_SYS_TR_GETFIRSTGUEST(rres_id),2) GUEST_XPDA_LONG,  
  RRES.RRES_DESCRIPTION            Description,
  V8_SYS_TR_GETFIRSTGUEST(rres_id) RPRF_XCMS_ID,    
  RRES.RRES_ID,
  RRES.RRES_DATE,
  RRES.RRES_STARTTIME,
  RRES.RRES_ENDTIME,
  RRES.RRES_PAX,
  NVL((SELECT SUM(rrgl_number) FROM RRGL,XCAC WHERE RRGL.RRGL_XCAC_ID=XCAC.XCAC_ID AND RRGL.RRGL_RRES_ID = RRES.RRES_ID AND XCAC.XCAC_REPORT_GROUP=1 ),0) TOTALKIDS_G1,
  NVL((SELECT SUM(rrgl_number) FROM RRGL,XCAC WHERE RRGL.RRGL_XCAC_ID=XCAC.XCAC_ID AND RRGL.RRGL_RRES_ID = RRES.RRES_ID AND XCAC.XCAC_REPORT_GROUP=2 ),0) TOTALKIDS_G2,
  NVL((SELECT SUM(rrgl_number) FROM RRGL,XCAC WHERE RRGL.RRGL_XCAC_ID=XCAC.XCAC_ID AND RRGL.RRGL_RRES_ID = RRES.RRES_ID AND XCAC.XCAC_REPORT_GROUP=3 ),0) TOTALKIDS_G3,
  NVL((SELECT SUM(RRGL_NUMBER) FROM RRGL WHERE RRGL_RRES_ID=RRES_ID),0) AS TOTALKIDS,
  RRES.RRES_RRST_ID,
  RRES.RRES_DESCRIPTION,
  RRES.RRES_TABLECARD,  
  RRES.RRES_WCOL_ID,
  RRES.RRES_CANCELED,
  RRES.RRES_LEFTTABLETIME,
  RRES.RRES_PLACEDTIME,
  --  to_date(to_char(RRES_DATE, 'DD/MM/YYYY')   || ' ' || to_char(RRES_EndTIME, 'HH24:MI:SS'), 'DD/MM/YYYY HH24:MI:SS') as dt
  case 
    when  RRES_LEFTTABLETIME is not null and 
          to_date(to_char(RRES_DATE, 'DD/MM/YYYY')   || ' ' || 
          to_char(RRES_LEFTTABLETIME, 'HH24:MI:SS'), 'DD/MM/YYYY HH24:MI:SS') 
               < sysdate then 1 -- Already LEFT 
    when  RRES_LEFTTABLETIME is not null and 
          to_date(to_char(RRES_DATE, 'DD/MM/YYYY')   || ' ' || 
          to_char(RRES_LEFTTABLETIME, 'HH24:MI:SS'), 'DD/MM/YYYY HH24:MI:SS') 
               > sysdate then 2 -- LEFT IN THE FUTURE 
    when  RRES_PLACEDTIME is not null and 
          RRES_LEFTTABLETIME is  null and 
          RRES_DATE = trunc(sysdate) then 2  -- Reservation is SEATED now
    when  RRES_PLACEDTIME is null and 
          RRES_DATE = trunc(sysdate) then 3  -- Reservation is EXPECTED today
    when  RRES_DATE > trunc(sysdate) then 4  -- Future Reservation
    else  null
  end as SeatedStatus,
  V8_FNC_NUM_TO_CHAR_FORMATING(RRES.RRES_PRICE) PRICE_PERPAX,
  V8_FNC_NUM_TO_CHAR_FORMATING(RRES.RRES_PRICE*RRES.RRES_PAX) PRICE_TOTAL      
FROM 
  RRES
ORDER BY RRES.RRES_DATE, Restaurant, ALL_TABLES;

CREATE OR REPLACE VIEW V8_REP_REVENUE_HISTFOR_HUGE
AS 
select "WDAT_DATE","WDAT_DATE2","WDAT_DATE3","DAYS","MONTHYEAR","FUTURE","NOOFROOMS","OOO","NOOFROOMS_TO_SELL","NOOFBEDS","OCC_ROOMS_DEF","OCC_ROOMS_TENT","OCC_ROOMS_E1P_DEF","OCC_ROOMS_E1P_TENT","OCC_ROOMS_M1P_DEF","OCC_ROOMS_M1P_TENT","ARR_ROOMS_DEF_TENT","ARR_ROOMS_DEF","DEP_ROOMS_DEF_TENT","DEP_ROOMS_DEF","OCC_ADULTS_DEF","OCC_ADULTS_TENT","OCC_CHILDS_DEF","OCC_CHILDS_TENT","VIP_ADULTS_DEF","VIP_ADULTS_TENT","DEV_ROOM_REV_GROSS","TENT_ROOM_REV_GROSS","DEV_ROOM_REV_NET","TENT_ROOM_REV_NET","DEV_FB_REV_GROSS","TENT_FB_REV_GROSS","DEV_FB_REV_NET","TENT_FB_REV_NET","DEV_OTHER_REV_GROSS","TENT_OTHER_REV_GROSS","DEV_OTHER_REV_NET","TENT_OTHER_REV_NET","COMP_ROOMS","HOUSEUSE_ROOMS","OOO_ROOMS","OOS_ROOMS","WALKIN_ROOMS","NOSHOW_ROOMS","DAYUSEROOMS_ROOMS","COUNT_RESMADE","COUNT_RESCANCELED","COUNT_CANCFOR_DAY","OCC_ROOMS_BUD","OCC_ADULTS_BUD","OCC_CHILDS_BUD","BUD_ROOM_REV_GROSS","BUD_ROOM_REV_NET","BUD_FB_REV_GROSS","BUD_FB_REV_NET","BUD_OTHER_REV_NET","BUD_OTHER_REV_GROSS","DAYUSE_RMS_DEF","DAYUSE_RMS_TENT","ARR_DAYUSE_RMS_DEF_TENT","ARR_DAYUSE_RMS_DEF","DEP_DAYUSE_RMS_DEF_TENT","DEP_DAYUSE_RMS_DEF","DAYUSE_ADLT_DEF","DAYUSE_ADLT_TENT","DAYUSE_CHILDS_DEF","DAYUSE_CHILDS_TENT","BLOCK_ALL_DEF","BLOCK_ALL_TENT","BLOCK_NOT_PICKUP_DEF","BLOCK_NOT_PICKUP_TENT","BLOCK_PICKUP_DEF","BLOCK_PICKUP_TENT","DAYUSE_COMP_ROOMS","DAYUSE_HOUSE_ROOMS" from
(
select 
WDAT.WDAT_DATE WDAT_DATE, 
WDAT.WDAT_DATE WDAT_DATE2,
WDAT.WDAT_DATE WDAT_DATE3,
to_char(WDAT.WDAT_DATE,'DD')DAYS, -- New Field
TO_CHAR(WDAT.WDAT_DATE,'yyyy_mm') MonthYear, 
WDAT.FUTURE FUTURE,
SMAN.SMAN_ROOMSINHOTEL_D NOOFROOMS, 
SMAN.SMAN_ROOMS_OOO_D OOO,
SMAN.SMAN_ROOMSINHOTEL_D-SMAN.SMAN_ROOMS_OOO_D NOOFROOMS_TO_SELL,
SMAN.SMAN_BEDSINHOTEL_D NOOFBEDS, --CVZ New field
SMAN.SMAN_ROOMS_OCC_D OCC_ROOMS_DEF, 
0 OCC_ROOMS_TENT,
SMAN.SMAN_ROOMS_OCC_E1P_D OCC_ROOMS_E1P_DEF,
0 OCC_ROOMS_E1P_TENT,
SMAN.SMAN_ROOMS_OCC_M1P_D OCC_ROOMS_M1P_DEF,  
0 OCC_ROOMS_M1P_TENT,
SMAN.SMAN_ARR_ROOMS_D ARR_ROOMS_DEF_TENT, 
SMAN.SMAN_ARR_ROOMS_D ARR_ROOMS_DEF, 
SMAN.SMAN_DEP_ROOMS_D DEP_ROOMS_DEF_TENT, 
SMAN.SMAN_DEP_ROOMS_D DEP_ROOMS_DEF, 
SMAN.SMAN_ADULTS_D OCC_ADULTS_DEF, 
0  OCC_ADULTS_TENT,
SMAN.SMAN_CHILDS_D OCC_CHILDS_DEF, 
0 OCC_CHILDS_TENT, 
SMAN.SMAN_VIPADULTS_D VIP_ADULTS_DEF,
0 VIP_ADULTS_TENT,  
NVL(SMAN.SMAN_ROOMREVGROSS_D,0) DEV_ROOM_REV_GROSS,
0 TENT_ROOM_REV_GROSS,
NVL(SMAN.SMAN_ROOMREVNET_D,0) DEV_ROOM_REV_NET,
0 TENT_ROOM_REV_NET,
NVL(SMAN.SMAN_FBREVGROSS_D,0) DEV_FB_REV_GROSS,
0 TENT_FB_REV_GROSS,
NVL(SMAN.SMAN_FBREVNET_D,0) DEV_FB_REV_NET,
0 TENT_FB_REV_NET,
NVL(SMAN.SMAN_MINIREVGROSS_D,0)+NVL(SMAN.SMAN_TELEREVGROSS_D,0)+NVL(SMAN.SMAN_MISCREVGROSS_D,0) DEV_OTHER_REV_GROSS,
0 TENT_OTHER_REV_GROSS,
NVL(SMAN.SMAN_MINIREVNET_D,0)+NVL(SMAN.SMAN_TELEREVNET_D,0)+NVL(SMAN.SMAN_MISCREVNET_D,0) DEV_OTHER_REV_NET,
0 TENT_OTHER_REV_NET,
nvl(SMAN.SMAN_COMPROOMS_D,0) COMP_ROOMS,
nvl(SMAN.SMAN_HOUSEUSEROOMS_D,0) HOUSEUSE_ROOMS,
nvl(SMAN.SMAN_ROOMS_OOO_D,0) OOO_ROOMS,
nvl(SMAN.SMAN_ROOMS_OOS_D,0) OOS_ROOMS,
nvl(SMAN.SMAN_WALKIN_D,0) WALKIN_ROOMS,
nvl(SMAN.SMAN_NOSHOWROOMS_D,0) NOSHOW_ROOMS,
nvl(SMAN.SMAN_DAYUSEROOMS_D,0) DAYUSEROOMS_ROOMS,
nvl(SMAN.SMAN_RESMADETODAY_D,0) COUNT_RESMADE,
nvl(SMAN.SMAN_RESCANCELEDTODAY_D,0) COUNT_RESCANCELED,
nvl(SMAN.SMAN_CANCFORTODAY_D,0) COUNT_CANCFOR_DAY,
NVL((SELECT SMBD.SMBD_ROOMS_OCC_D FROM SMBD WHERE SMBD.SMBD_DATE=WDAT.WDAT_DATE),0) OCC_ROOMS_BUD, 
NVL((SELECT SMBD.SMBD_ADULTS_D FROM SMBD WHERE SMBD.SMBD_DATE=WDAT.WDAT_DATE),0) OCC_ADULTS_BUD,
NVL((SELECT SMBD.SMBD_CHILDS_D FROM SMBD WHERE SMBD.SMBD_DATE=WDAT.WDAT_DATE),0) OCC_CHILDS_BUD,
NVL((SELECT SMBD.SMBD_ROOMREVGROSS_D FROM SMBD WHERE SMBD.SMBD_DATE=WDAT.WDAT_DATE),0)  BUD_ROOM_REV_GROSS,
NVL((SELECT SMBD.SMBD_ROOMREVNET_D FROM SMBD WHERE SMBD.SMBD_DATE=WDAT.WDAT_DATE),0)  BUD_ROOM_REV_NET,
NVL((SELECT SMBD.SMBD_FBREVGROSS_D FROM SMBD WHERE SMBD.SMBD_DATE=WDAT.WDAT_DATE),0)  BUD_FB_REV_GROSS,
NVL((SELECT SMBD.SMBD_FBREVNET_D FROM SMBD WHERE SMBD.SMBD_DATE=WDAT.WDAT_DATE),0)  BUD_FB_REV_NET,
NVL((SELECT SMBD.SMBD_MINIREVGROSS_D FROM SMBD WHERE SMBD.SMBD_DATE=WDAT.WDAT_DATE),0)+NVL((SELECT SMBD.SMBD_TELEREVGROSS_D FROM SMBD WHERE SMBD.SMBD_DATE=WDAT.WDAT_DATE),0)+NVL((SELECT SMBD.SMBD_MISCREVGROSS_D FROM SMBD WHERE SMBD.SMBD_DATE=WDAT.WDAT_DATE),0) BUD_OTHER_REV_NET,
NVL((SELECT SMBD.SMBD_MINIREVNET_D FROM SMBD WHERE SMBD.SMBD_DATE=WDAT.WDAT_DATE),0)+NVL((SELECT SMBD.SMBD_TELEREVNET_D FROM SMBD WHERE SMBD.SMBD_DATE=WDAT.WDAT_DATE),0)+NVL((SELECT SMBD.SMBD_MISCREVNET_D FROM SMBD WHERE SMBD.SMBD_DATE=WDAT.WDAT_DATE),0) BUD_OTHER_REV_GROSS,
SMAN.SMAN_DAYUSEROOMS_D DAYUSE_RMS_DEF, 
0 DAYUSE_RMS_TENT, 
0 ARR_DAYUSE_RMS_DEF_TENT, 
0 ARR_DAYUSE_RMS_DEF, 
0 DEP_DAYUSE_RMS_DEF_TENT, 
0 DEP_DAYUSE_RMS_DEF,
SMAN.SMAN_DAYUSE_ADULTS_D DAYUSE_ADLT_DEF, 
0 DAYUSE_ADLT_TENT, 
0 DAYUSE_CHILDS_DEF, 
0 DAYUSE_CHILDS_TENT, 
0 BLOCK_ALL_DEF, 
0 BLOCK_ALL_TENT,
0 BLOCK_NOT_PICKUP_DEF,
0 BLOCK_NOT_PICKUP_TENT,
0 BLOCK_PICKUP_DEF,
0 BLOCK_PICKUP_TENT,
0 DAYUSE_COMP_ROOMS,
0 DAYUSE_HOUSE_ROOMS
 from
V8_REP_WDAT_DATE WDAT,sman
 where
 WDAT.WDAT_DATE=SMAN.SMAN_DATE
)
union all
(
select
S_POSDATE                                                                     WDAT_DATE, 
S_POSDATE                                                                     WDAT_DATE2, 
S_POSDATE                                                                     WDAT_DATE3, 
to_char(S_POSDATE,'DD')                                                 DAYS, -- New Field
TO_CHAR(S_POSDATE,'yyyy_mm')                                    MONTHYEAR,
1                                                                                    FUTURE, 
sum(S_NOOFROOMS)                                                      NOOFROOMS, 
sum(S_OOO)                                                                    OOO,
sum(S_NOOFROOMS_TO_SELL)                                                      NOOFROOMS_TO_SELL,
sum(S_NOOFBEDS)                                                         NOOFBEDS, --CVZ New field
sum(S_OCC_ROOMS_DEF)                                                          OCC_ROOMS_DEF,
sum(S_OCC_ROOMS_TENT)                                                         OCC_ROOMS_TENT,  
sum(S_OCC_ROOMS_E1P_DEF)                                                      OCC_ROOMS_E1P_DEF,
sum(S_OCC_ROOMS_E1P_TENT)                                                     OCC_ROOMS_E1P_TENT,                         
sum(S_OCC_ROOMS_M1P_DEF)                                                      OCC_ROOMS_M1P_DEF,
sum(S_OCC_ROOMS_M1P_TENT)                                                     OCC_ROOMS_M1P_TENT,
sum(S_ARR_ROOMS_DEF_TENT)                                                     ARR_ROOMS_DEF_TENT,
sum(S_ARR_ROOMS_DEF)                                                          ARR_ROOMS_DEF,
--sum(S_ARR_ADULT_DEF_TENT)                                                     ARR_ADULT_DEF_TENT,
sum(S_DEP_ROOMS_DEF_TENT)                                                     DEP_ROOMS_DEF_TENT,
--sum(S_DEP_ADULTS_DEF_TENT)                                                    DEP_ADULTS_DEF_TENT,
sum(S_DEP_ROOMS_DEF)                                                          DEP_ROOMS_DEF,
sum(S_OCC_ADULTS_DEF)                                                         OCC_ADULTS_DEF,
sum(S_OCC_ADULTS_TENT)                                                        OCC_ADULTS_TENT,
sum(S_OCC_CHILDS_DEF)                                                         OCC_CHILDS_DEF,
sum(S_OCC_CHILDS_TENT)                                                        OCC_CHILDS_TENT,
sum(S_VIP_ADULTS_DEF)                                                         VIP_ADULTS_DEF,
sum(S_VIP_ADULTS_TENT)                                                        VIP_ADULTS_TENT,
sum(S_DEV_ROOM_REV_GROSS)                                                     DEV_ROOM_REV_GROSS,
sum(S_TENT_ROOM_REV_GROSS)                                                    TENT_ROOM_REV_GROSS,
sum(S_DEV_ROOM_REV_NET)                                                       DEV_ROOM_REV_NET,
sum(S_TENT_ROOM_REV_NET)                                                      TENT_ROOM_REV_NET,
sum(S_DEV_FB_REV_GROSS)                                                       DEV_FB_REV_GROSS,
sum(S_TENT_FB_REV_GROSS)                                                      TENT_FB_REV_GROSS,
sum(S_DEV_FB_REV_NET)                                                         DEV_FB_REV_NET,
sum(S_TENT_FB_REV_NET)                                                        TENT_FB_REV_NET,
sum(S_DEV_OTHER_REV_GROSS)                                                    DEV_OTHER_REV_GROSS,
sum(S_TENT_OTHER_REV_GROSS)                                                   TENT_OTHER_REV_GROSS,
sum(S_DEV_OTHER_REV_NET)                                                      DEV_OTHER_REV_NET,
sum(S_TENT_OTHER_REV_NET)                                                     TENT_OTHER_REV_NET,
sum(S_COMP_ROOMS)                                                             COMP_ROOMS,
sum(S_HOUSEUSE_ROOMS)                                                         HOUSEUSE_ROOMS,
sum(S_OOO_ROOMS)                                                              OOO_ROOMS,
sum(S_OOS_ROOMS)                                                              OOS_ROOMS,
sum(S_WALKIN_ROOMS)                                                           WALKIN_ROOMS,
sum(S_NOSHOW_ROOMS)                                                           NOSHOW_ROOMS,
sum(S_DAYUSEROOMS_ROOMS)                                                      DAYUSEROOMS_ROOMS,
sum(S_COUNT_RESMADE)                                                          COUNT_RESMADE,
sum(S_COUNT_RESCANCELED)                                                      COUNT_RESCANCELED,
sum(S_COUNT_CANCFOR_DAY)                                                      COUNT_CANCFOR_DAY,
sum(S_OCC_ROOMS_BUD)                                                          OCC_ROOMS_BUD,  
sum(S_OCC_ADULTS_BUD)                                                         OCC_ADULTS_BUD, 
sum(S_OCC_CHILDS_BUD)                                                         OCC_CHILDS_BUD,
sum(S_BUD_ROOM_REV_GROSS)                                                     BUD_ROOM_REV_GROSS,
sum(S_BUD_ROOM_REV_GROSS)                                                     BUD_ROOM_REV_NET,
sum(S_BUD_FB_REV_GROSS)                                                       BUD_FB_REV_GROSS,
sum(S_BUD_FB_REV_NET)                                                         BUD_FB_REV_NET,
sum(S_BUD_OTHER_REV_GROSS)                                                    BUD_OTHER_REV_GROSS,
sum(S_BUD_OTHER_REV_NET)                                                      BUD_OTHER_REV_NET,
sum(S_DAYUSE_RMS_DEF)                                                         DAYUSE_RMS_DEF, 
sum(S_DAYUSE_RMS_TENT)                                                        DAYUSE_RMS_TENT,  
sum(S_ARR_DAYUSE_RMS_DEF_TENT)                                                ARR_DAYUSE_RMS_DEF_TENT, 
sum(S_ARR_DAYUSE_RMS_DEF)                                                     ARR_DAYUSE_RMS_DEF,
sum(S_DEP_DAYUSE_RMS_DEF_TENT)                                                DEP_DAYUSE_RMS_DEF_TENT,  
sum(S_DEP_DAYUSE_RMS_DEF)                                                     DEP_DAYUSE_RMS_DEF,
sum(S_DAYUSE_ADLT_DEF)                                                        DAYUSE_ADLT_DEF, 
sum(S_DAYUSE_ADLT_TENT)                                                       DAYUSE_ADLT_TENT,
sum(S_DAYUSE_CHILDS_DEF)                                                       DAYUSE_CHILDS_DEF, 
sum(S_DAYUSE_CHILDS_TENT)                                                   DAYUSE_CHILDS_TENT,  
sum(S_BLOCK_ALL_DEF)                                                                BLOCK_ALL_DEF, 
sum(S_BLOCK_ALL_TENT)                                                               BLOCK_ALL_TENT,
sum(S_BLOCK_NOT_PICKUP_DEF)                                                 BLOCK_NOT_PICKUP_DEF,
sum(S_BLOCK_NOT_PICKUP_TENT)                                               BLOCK_NOT_PICKUP_TENT,
sum(S_BLOCK_PICKUP_DEF)                                                        BLOCK_PICKUP_DEF,
sum(S_BLOCK_PICKUP_TENT)                                                        BLOCK_PICKUP_TENT,
sum(S_DAYUSE_COMP_ROOMS)                                                    DAYUSE_COMP_ROOMS,
sum(S_DAYUSE_HOUSE_ROOMS)                                                   DAYUSE_HOUSE_ROOMS      
from
(
select
V8_SYS_NOOFROOMS_COMBINED.WDAT_DATE                                          S_POSDATE,
V8_SYS_NOOFROOMS_COMBINED.NOOFROOMS                                          S_NOOFROOMS,
nvl(V8_SYS_NOOFROOMS_COMBINED.OOO,0)                                         S_OOO,
V8_SYS_NOOFROOMS_COMBINED.NOOFROOMS-nvl(V8_SYS_NOOFROOMS_COMBINED.OOO,0)     S_NOOFROOMS_TO_SELL, 
0                                                                            S_NOOFBEDS,--CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_OCC_ROOMS_E1P_DEF,
0                                                                            S_OCC_ROOMS_E1P_TENT,
0                                                                            S_OCC_ROOMS_M1P_DEF,
0                                                                            S_OCC_ROOMS_M1P_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_VIP_ADULTS_DEF,
0                                                                            S_VIP_ADULTS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_COMP_ROOMS,
0                                                                            S_HOUSEUSE_ROOMS,
0                                                                            S_OOO_ROOMS,
0                                                                            S_OOS_ROOMS,
0                                                                            S_WALKIN_ROOMS,
0                                                                            S_NOSHOW_ROOMS,
0                                                                            S_DAYUSEROOMS_ROOMS,
0                                                                            S_COUNT_RESMADE,
0                                                                            S_COUNT_RESCANCELED,
0                                                                            S_COUNT_CANCFOR_DAY,
0                                                                            S_OCC_ROOMS_BUD, 
0                                                                            S_OCC_ADULTS_BUD, 
0                                                                            S_OCC_CHILDS_BUD,
0                                                                            S_BUD_ROOM_REV_GROSS,
0                                                                            S_BUD_ROOM_REV_NET,
0                                                                            S_BUD_FB_REV_GROSS,
0                                                                            S_BUD_FB_REV_NET,
0                                                                            S_BUD_OTHER_REV_GROSS,
0                                                                            S_BUD_OTHER_REV_NET,
0                                                                            S_NOT_USED,
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_DAYUSE_RMS_TENT, 
0                                                                            S_ARR_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_DEP_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_ARR_DAYUSE_RMS_DEF, 
0                                                                            S_DEP_DAYUSE_RMS_DEF,
0                                                                            S_DAYUSE_ADLT_DEF,  
0                                                                            S_DAYUSE_ADLT_TENT, 
0                                                                            S_DAYUSE_CHILDS_DEF,
0                                                                            S_DAYUSE_CHILDS_TENT,
0                                                                            S_BLOCK_ALL_DEF,
0                                                                            S_BLOCK_ALL_TENT,
0                                                                            S_BLOCK_NOT_PICKUP_DEF,
0                                                                            S_BLOCK_NOT_PICKUP_TENT,
0                                                                            S_BLOCK_PICKUP_DEF,
0                                                                            S_BLOCK_PICKUP_TENT,
0                                                                            S_DAYUSE_COMP_ROOMS,
0                                                                            S_DAYUSE_HOUSE_ROOMS
from
V8_SYS_NOOFROOMS_COMBINED
union all
select
V8_REP_NOOFROOMS_COMBINED.WDAT_DATE         S_POSDATE,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
V8_REP_NOOFROOMS_COMBINED.NOOFBEDS            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_OCC_ROOMS_E1P_DEF,
0                                                                            S_OCC_ROOMS_E1P_TENT,
0                                                                            S_OCC_ROOMS_M1P_DEF,
0                                                                            S_OCC_ROOMS_M1P_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_VIP_ADULTS_DEF,
0                                                                            S_VIP_ADULTS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_COMP_ROOMS,
0                                                                            S_HOUSEUSE_ROOMS,
0                                                                            S_OOO_ROOMS,
0                                                                            S_OOS_ROOMS,
0                                                                            S_WALKIN_ROOMS,
0                                                                            S_NOSHOW_ROOMS,
0                                                                            S_DAYUSEROOMS_ROOMS,
0                                                                            S_COUNT_RESMADE,
0                                                                            S_COUNT_RESCANCELED,
0                                                                            S_COUNT_CANCFOR_DAY,
0                                                                            S_OCC_ROOMS_BUD, 
0                                                                            S_OCC_ADULTS_BUD, 
0                                                                            S_OCC_CHILDS_BUD,
0                                                                            S_BUD_ROOM_REV_GROSS,
0                                                                            S_BUD_ROOM_REV_NET,
0                                                                            S_BUD_FB_REV_GROSS,
0                                                                            S_BUD_FB_REV_NET,
0                                                                            S_BUD_OTHER_REV_GROSS,
0                                                                            S_BUD_OTHER_REV_NET,
0                                                                            S_NOT_USED,
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_DAYUSE_RMS_TENT, 
0                                                                            S_ARR_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_DEP_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_ARR_DAYUSE_RMS_DEF, 
0                                                                            S_DEP_DAYUSE_RMS_DEF,
0                                                                            S_DAYUSE_ADLT_DEF,  
0                                                                            S_DAYUSE_ADLT_TENT,  
0                                                                            S_DAYUSE_CHILDS_DEF,
0                                                                            S_DAYUSE_CHILDS_TENT,
0                                                                            S_BLOCK_ALL_DEF,
0                                                                            S_BLOCK_ALL_TENT,
0                                                                            S_BLOCK_NOT_PICKUP_DEF,
0                                                                            S_BLOCK_NOT_PICKUP_TENT,
0                                                                            S_BLOCK_PICKUP_DEF,
0                                                                            S_BLOCK_PICKUP_TENT,
0                                                                            S_DAYUSE_COMP_ROOMS,
0                                                                            S_DAYUSE_HOUSE_ROOMS
from
V8_REP_NOOFROOMS_COMBINED
union all
select
WDAT.WDAT_DATE                                                               S_POSDATE,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
nvl(S_SYS_YBLD_OCC.S_DEFNOOFROOMS,0)+nvl(S_SYS_YDET_OCC_WB.S_DEFNOOFROOMS,0) S_OCC_ROOMS_DEF, 
nvl(S_SYS_YBLD_OCC.S_TENTNOOFROOMS,0)+nvl(S_SYS_YDET_OCC_WB.S_TENTNOOFROOMS,0)-nvl(S_SYS_YDET_OCC_WB.S_TENTBLOCKDEFRESROOM,0) S_OCC_ROOMS_TENT,
0                                                                            S_OCC_ROOMS_E1P_DEF,
0                                                                            S_OCC_ROOMS_E1P_TENT,
0                                                                            S_OCC_ROOMS_M1P_DEF,
0                                                                            S_OCC_ROOMS_M1P_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_VIP_ADULTS_DEF,
0                                                                            S_VIP_ADULTS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_COMP_ROOMS,
0                                                                            S_HOUSEUSE_ROOMS,
0                                                                            S_OOO_ROOMS,
0                                                                            S_OOS_ROOMS,
0                                                                            S_WALKIN_ROOMS,
0                                                                            S_NOSHOW_ROOMS,
0                                                                            S_DAYUSEROOMS_ROOMS,
0                                                                            S_COUNT_RESMADE,
0                                                                            S_COUNT_RESCANCELED,
0                                                                            S_COUNT_CANCFOR_DAY,
0                                                                            S_OCC_ROOMS_BUD, 
0                                                                            S_OCC_ADULTS_BUD, 
0                                                                            S_OCC_CHILDS_BUD,
0                                                                            S_BUD_ROOM_REV_GROSS,
0                                                                            S_BUD_ROOM_REV_NET,
0                                                                            S_BUD_FB_REV_GROSS,
0                                                                            S_BUD_FB_REV_NET,
0                                                                            S_BUD_OTHER_REV_GROSS,
0                                                                            S_BUD_OTHER_REV_NET,
0                                                                            S_NOT_USED,
nvl(S_SYS_YDET_OCC_WB.S_DEFNOOFROOMSDAYUSE,0)                                  S_DAYUSE_RMS_DEF,   
nvl(S_SYS_YDET_OCC_WB.S_TENTNOOFROOMSDAYUSE,0)                                 S_DAYUSE_RMS_TENT,   
0                                                                            S_ARR_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_DEP_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_ARR_DAYUSE_RMS_DEF, 
0                                                                            S_DEP_DAYUSE_RMS_DEF,
0                                                                            S_DAYUSE_ADLT_DEF,  
0                                                                            S_DAYUSE_ADLT_TENT,  
0                                                                            S_DAYUSE_CHILDS_DEF,
0                                                                            S_DAYUSE_CHILDS_TENT,
0                                                                            S_BLOCK_ALL_DEF,
0                                                                            S_BLOCK_ALL_TENT,
0                                                                            S_BLOCK_NOT_PICKUP_DEF,
0                                                                            S_BLOCK_NOT_PICKUP_TENT,
0                                                                            S_BLOCK_PICKUP_DEF,
0                                                                            S_BLOCK_PICKUP_TENT,
0                                                                            S_DAYUSE_COMP_ROOMS,
0                                                                            S_DAYUSE_HOUSE_ROOMS
 from 
 wdat,
( select 
V8_SYS_YBLD_OCC.YBLD_DATE S_YBLD_DATE,
sum(V8_SYS_YBLD_OCC.DEFNOOFROOMS) S_DEFNOOFROOMS, 
sum(V8_SYS_YBLD_OCC.TENTNOOFROOMS) S_TENTNOOFROOMS
 from V8_SYS_YBLD_OCC
 group by V8_SYS_YBLD_OCC.YBLD_DATE) S_SYS_YBLD_OCC,
 ( select 
V8_SYS_YDET_OCC_WB.YDET_DATE S_YDET_DATE,
sum(V8_SYS_YDET_OCC_WB.DEFNOOFROOMS) S_DEFNOOFROOMS, 
sum(V8_SYS_YDET_OCC_WB.TENTNOOFROOMS) S_TENTNOOFROOMS,
sum(V8_SYS_YDET_OCC_WB.TENTBLOCKDEFRESROOM) S_TENTBLOCKDEFRESROOM,
sum(V8_SYS_YDET_OCC_WB.DEFNOOFROOMSDAYUSE) S_DEFNOOFROOMSDAYUSE,
sum(V8_SYS_YDET_OCC_WB.TENTNOOFROOMSDAYUSE) S_TENTNOOFROOMSDAYUSE
 from V8_SYS_YDET_OCC_WB
 group by V8_SYS_YDET_OCC_WB.YDET_DATE) S_SYS_YDET_OCC_WB
 where
 WDAT.WDAT_DATE=S_SYS_YBLD_OCC.S_YBLD_DATE(+) and
 WDAT.WDAT_DATE=S_SYS_YDET_OCC_WB.S_YDET_DATE(+)
union all
select
WDAT.WDAT_DATE                                                               S_POSDATE,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF, 
0                                                                            S_OCC_ROOMS_TENT,
nvl(S_SYS_YBLD_OCC.S_DEFNOOFROOMS,0)+nvl(S_SYS_YDET_OCC_WB.S_DEFNOOFROOMS,0) S_OCC_ROOMS_E1P_DEF,
nvl(S_SYS_YBLD_OCC.S_TENTNOOFROOMS,0)+nvl(S_SYS_YDET_OCC_WB.S_TENTNOOFROOMS,0)-nvl(S_SYS_YDET_OCC_WB.S_TENTBLOCKDEFRESROOM,0) S_OCC_ROOMS_E1P_TENT,
0                                                                            S_OCC_ROOMS_M1P_DEF,
0                                                                            S_OCC_ROOMS_M1P_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_VIP_ADULTS_DEF,
0                                                                            S_VIP_ADULTS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_COMP_ROOMS,
0                                                                            S_HOUSEUSE_ROOMS,
0                                                                            S_OOO_ROOMS,
0                                                                            S_OOS_ROOMS,
0                                                                            S_WALKIN_ROOMS,
0                                                                            S_NOSHOW_ROOMS,
0                                                                            S_DAYUSEROOMS_ROOMS,
0                                                                            S_COUNT_RESMADE,
0                                                                            S_COUNT_RESCANCELED,
0                                                                            S_COUNT_CANCFOR_DAY,
0                                                                            S_OCC_ROOMS_BUD, 
0                                                                            S_OCC_ADULTS_BUD, 
0                                                                            S_OCC_CHILDS_BUD,
0                                                                            S_BUD_ROOM_REV_GROSS,
0                                                                            S_BUD_ROOM_REV_NET,
0                                                                            S_BUD_FB_REV_GROSS,
0                                                                            S_BUD_FB_REV_NET,
0                                                                            S_BUD_OTHER_REV_GROSS,
0                                                                            S_BUD_OTHER_REV_NET,
0                                                                            S_NOT_USED,
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_ARR_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_DEP_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_ARR_DAYUSE_RMS_DEF, 
0                                                                            S_DEP_DAYUSE_RMS_DEF,
0                                                                            S_DAYUSE_ADLT_DEF,  
0                                                                            S_DAYUSE_ADLT_TENT, 
0                                                                            S_DAYUSE_CHILDS_DEF,
0                                                                            S_DAYUSE_CHILDS_TENT,
0                                                                            S_BLOCK_ALL_DEF,
0                                                                            S_BLOCK_ALL_TENT,
0                                                                            S_BLOCK_NOT_PICKUP_DEF,
0                                                                            S_BLOCK_NOT_PICKUP_TENT,
0                                                                            S_BLOCK_PICKUP_DEF,
0                                                                            S_BLOCK_PICKUP_TENT,
0                                                                            S_DAYUSE_COMP_ROOMS,
0                                                                            S_DAYUSE_HOUSE_ROOMS
 from 
 wdat,
( select 
V8_REP_YBLD_OCC_E1P.YBLD_DATE S_YBLD_DATE,
sum(V8_REP_YBLD_OCC_E1P.DEFNOOFROOMS) S_DEFNOOFROOMS, 
sum(V8_REP_YBLD_OCC_E1P.TENTNOOFROOMS) S_TENTNOOFROOMS
 from V8_REP_YBLD_OCC_E1P
 group by V8_REP_YBLD_OCC_E1P.YBLD_DATE) S_SYS_YBLD_OCC,
 ( select 
V8_REP_YDET_OCC_WB_E1P.YDET_DATE S_YDET_DATE,
sum(V8_REP_YDET_OCC_WB_E1P.DEFNOOFROOMS) S_DEFNOOFROOMS, 
sum(V8_REP_YDET_OCC_WB_E1P.TENTNOOFROOMS) S_TENTNOOFROOMS,
sum(V8_REP_YDET_OCC_WB_E1P.TENTBLOCKDEFRESROOM) S_TENTBLOCKDEFRESROOM
 from V8_REP_YDET_OCC_WB_E1P
 group by V8_REP_YDET_OCC_WB_E1P.YDET_DATE) S_SYS_YDET_OCC_WB
 where
 WDAT.WDAT_DATE=S_SYS_YBLD_OCC.S_YBLD_DATE(+) and
 WDAT.WDAT_DATE=S_SYS_YDET_OCC_WB.S_YDET_DATE(+)
union all
select
WDAT.WDAT_DATE                                                               S_POSDATE,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF, 
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_OCC_ROOMS_E1P_DEF,
0                                                                            S_OCC_ROOMS_E1P_TENT,
nvl(S_SYS_YBLD_OCC.S_DEFNOOFROOMS,0)+nvl(S_SYS_YDET_OCC_WB.S_DEFNOOFROOMS,0) S_OCC_ROOMS_M1P_DEF,
nvl(S_SYS_YBLD_OCC.S_TENTNOOFROOMS,0)+nvl(S_SYS_YDET_OCC_WB.S_TENTNOOFROOMS,0)-nvl(S_SYS_YDET_OCC_WB.S_TENTBLOCKDEFRESROOM,0) S_OCC_ROOMS_M1P_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_VIP_ADULTS_DEF,
0                                                                            S_VIP_ADULTS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_COMP_ROOMS,
0                                                                            S_HOUSEUSE_ROOMS,
0                                                                            S_OOO_ROOMS,
0                                                                            S_OOS_ROOMS,
0                                                                            S_WALKIN_ROOMS,
0                                                                            S_NOSHOW_ROOMS,
0                                                                            S_DAYUSEROOMS_ROOMS,
0                                                                            S_COUNT_RESMADE,
0                                                                            S_COUNT_RESCANCELED,
0                                                                            S_COUNT_CANCFOR_DAY,
0                                                                            S_OCC_ROOMS_BUD, 
0                                                                            S_OCC_ADULTS_BUD, 
0                                                                            S_OCC_CHILDS_BUD,
0                                                                            S_BUD_ROOM_REV_GROSS,
0                                                                            S_BUD_ROOM_REV_NET,
0                                                                            S_BUD_FB_REV_GROSS,
0                                                                            S_BUD_FB_REV_NET,
0                                                                            S_BUD_OTHER_REV_GROSS,
0                                                                            S_BUD_OTHER_REV_NET,
0                                                                            S_NOT_USED,
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_ARR_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_DEP_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_ARR_DAYUSE_RMS_DEF, 
0                                                                            S_DEP_DAYUSE_RMS_DEF,
0                                                                            S_DAYUSE_ADLT_DEF,  
0                                                                            S_DAYUSE_ADLT_TENT, 
0                                                                            S_DAYUSE_CHILDS_DEF,
0                                                                            S_DAYUSE_CHILDS_TENT,
0                                                                            S_BLOCK_ALL_DEF,
0                                                                            S_BLOCK_ALL_TENT,
0                                                                            S_BLOCK_NOT_PICKUP_DEF,
0                                                                            S_BLOCK_NOT_PICKUP_TENT,
0                                                                            S_BLOCK_PICKUP_DEF,
0                                                                            S_BLOCK_PICKUP_TENT,
0                                                                            S_DAYUSE_COMP_ROOMS,
0                                                                            S_DAYUSE_HOUSE_ROOMS
 from 
 wdat,
( select 
V8_REP_YBLD_OCC_M1P.YBLD_DATE S_YBLD_DATE,
sum(V8_REP_YBLD_OCC_M1P.DEFNOOFROOMS) S_DEFNOOFROOMS, 
sum(V8_REP_YBLD_OCC_M1P.TENTNOOFROOMS) S_TENTNOOFROOMS
 from V8_REP_YBLD_OCC_M1P
 group by V8_REP_YBLD_OCC_M1P.YBLD_DATE) S_SYS_YBLD_OCC,
 ( select 
V8_REP_YDET_OCC_WB_M1P.YDET_DATE S_YDET_DATE,
sum(V8_REP_YDET_OCC_WB_M1P.DEFNOOFROOMS) S_DEFNOOFROOMS, 
sum(V8_REP_YDET_OCC_WB_M1P.TENTNOOFROOMS) S_TENTNOOFROOMS,
sum(V8_REP_YDET_OCC_WB_M1P.TENTBLOCKDEFRESROOM) S_TENTBLOCKDEFRESROOM
 from V8_REP_YDET_OCC_WB_M1P
 group by V8_REP_YDET_OCC_WB_M1P.YDET_DATE) S_SYS_YDET_OCC_WB
 where
 WDAT.WDAT_DATE=S_SYS_YBLD_OCC.S_YBLD_DATE(+) and
 WDAT.WDAT_DATE=S_SYS_YDET_OCC_WB.S_YDET_DATE(+)
union all
select 
V8_SYS_HS_ARR_DEP_COMB_SMALL.WDAT_DATE                                       S_POSDATE,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_OCC_ROOMS_E1P_DEF,
0                                                                            S_OCC_ROOMS_E1P_TENT,
0                                                                            S_OCC_ROOMS_M1P_DEF,
0                                                                            S_OCC_ROOMS_M1P_TENT,
V8_SYS_HS_ARR_DEP_COMB_SMALL.ARREXPROOM                                      S_ARR_ROOMS_DEF_TENT,
V8_SYS_HS_ARR_DEP_COMB_SMALL.ARREXPADULT                                     S_ARR_ADULT_DEF_TENT,
V8_SYS_HS_ARR_DEP_COMB_SMALL.DEPEXPROOM                                      S_DEP_ROOMS_DEF_TENT, 
V8_SYS_HS_ARR_DEP_COMB_SMALL.DEPEXPADULT                                     S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_VIP_ADULTS_DEF,
0                                                                            S_VIP_ADULTS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_COMP_ROOMS,
0                                                                            S_HOUSEUSE_ROOMS,
0                                                                            S_OOO_ROOMS,
0                                                                            S_OOS_ROOMS,
0                                                                            S_WALKIN_ROOMS,
0                                                                            S_NOSHOW_ROOMS,
0                                                                            S_DAYUSEROOMS_ROOMS,
0                                                                            S_COUNT_RESMADE,
0                                                                            S_COUNT_RESCANCELED,
0                                                                            S_COUNT_CANCFOR_DAY,
0                                                                            S_OCC_ROOMS_BUD, 
0                                                                            S_OCC_ADULTS_BUD, 
0                                                                            S_OCC_CHILDS_BUD,
0                                                                            S_BUD_ROOM_REV_GROSS,
0                                                                            S_BUD_ROOM_REV_NET,
0                                                                            S_BUD_FB_REV_GROSS,
0                                                                            S_BUD_FB_REV_NET,
0                                                                            S_BUD_OTHER_REV_GROSS,
0                                                                            S_BUD_OTHER_REV_NET,
0                                                                            S_NOT_USED,
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_DAYUSE_RMS_DEF, 
V8_SYS_HS_ARR_DEP_COMB_SMALL.ARREXPROOMDAYUSE                                S_S_DAYUSE_ARR_RMS_DEF_TENT,
V8_SYS_HS_ARR_DEP_COMB_SMALL.DEPEXPROOMDAYUSE                                S_DEP_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_ARR_DAYUSE_RMS_DEF, 
0                                                                            S_DEP_DAYUSE_RMS_DEF,
0                                                                            S_DAYUSE_ADLT_DEF,  
0                                                                            S_DAYUSE_ADLT_TENT, 
0                                                                            S_DAYUSE_CHILDS_DEF,
0                                                                            S_DAYUSE_CHILDS_TENT,
0                                                                            S_BLOCK_ALL_DEF,
0                                                                            S_BLOCK_ALL_TENT,
0                                                                            S_BLOCK_NOT_PICKUP_DEF,
0                                                                            S_BLOCK_NOT_PICKUP_TENT,
0                                                                            S_BLOCK_PICKUP_DEF,
0                                                                            S_BLOCK_PICKUP_TENT,
0                                                                            S_DAYUSE_COMP_ROOMS,
0                                                                            S_DAYUSE_HOUSE_ROOMS
 from V8_SYS_HS_ARR_DEP_COMB_SMALL
union all
select 
V8_SYS_HS_ARR_ALLROOM.WDAT_DATE                                              S_POSDATE,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_OCC_ROOMS_E1P_DEF,
0                                                                            S_OCC_ROOMS_E1P_TENT,
0                                                                            S_OCC_ROOMS_M1P_DEF,
0                                                                            S_OCC_ROOMS_M1P_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
V8_SYS_HS_ARR_ALLROOM.DEFEXPROOM                                             S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_VIP_ADULTS_DEF,
0                                                                            S_VIP_ADULTS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_COMP_ROOMS,
0                                                                            S_HOUSEUSE_ROOMS,
0                                                                            S_OOO_ROOMS,
0                                                                            S_OOS_ROOMS,
0                                                                            S_WALKIN_ROOMS,
0                                                                            S_NOSHOW_ROOMS,
0                                                                            S_DAYUSEROOMS_ROOMS,
0                                                                            S_COUNT_RESMADE,
0                                                                            S_COUNT_RESCANCELED,
0                                                                            S_COUNT_CANCFOR_DAY,
0                                                                            S_OCC_ROOMS_BUD, 
0                                                                            S_OCC_ADULTS_BUD, 
0                                                                            S_OCC_CHILDS_BUD,
0                                                                            S_BUD_ROOM_REV_GROSS,
0                                                                            S_BUD_ROOM_REV_NET,
0                                                                            S_BUD_FB_REV_GROSS,
0                                                                            S_BUD_FB_REV_NET,
0                                                                            S_BUD_OTHER_REV_GROSS,
0                                                                            S_BUD_OTHER_REV_NET,
0                                                                            S_NOT_USED,
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_ARR_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_DEP_DAYUSE_RMS_DEF_TENT, 
V8_SYS_HS_ARR_ALLROOM.DEFEXPROOMDAYUSE     S_ARR_DAYUSE_RMS_DEF, 
0                                                                            S_DEP_DAYUSE_RMS_DEF,
0                                                                            S_DAYUSE_ADLT_DEF,  
0                                                                            S_DAYUSE_ADLT_TENT, 
0                                                                            S_DAYUSE_CHILDS_DEF,
0                                                                            S_DAYUSE_CHILDS_TENT,
0                                                                            S_BLOCK_ALL_DEF,
0                                                                            S_BLOCK_ALL_TENT,
0                                                                            S_BLOCK_NOT_PICKUP_DEF,
0                                                                            S_BLOCK_NOT_PICKUP_TENT,
0                                                                            S_BLOCK_PICKUP_DEF,
0                                                                            S_BLOCK_PICKUP_TENT,
0                                                                            S_DAYUSE_COMP_ROOMS,
0                                                                            S_DAYUSE_HOUSE_ROOMS
 from V8_SYS_HS_ARR_ALLROOM
union all
select 
V8_SYS_HS_DEP_ALLROOM.WDAT_DATE                                              S_POSDATE,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_OCC_ROOMS_E1P_DEF,
0                                                                            S_OCC_ROOMS_E1P_TENT,
0                                                                            S_OCC_ROOMS_M1P_DEF,
0                                                                            S_OCC_ROOMS_M1P_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
V8_SYS_HS_DEP_ALLROOM.DEFDEPROOM                                             S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_VIP_ADULTS_DEF,
0                                                                            S_VIP_ADULTS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_COMP_ROOMS,
0                                                                            S_HOUSEUSE_ROOMS,
0                                                                            S_OOO_ROOMS,
0                                                                            S_OOS_ROOMS,
0                                                                            S_WALKIN_ROOMS,
0                                                                            S_NOSHOW_ROOMS,
0                                                                            S_DAYUSEROOMS_ROOMS,
0                                                                            S_COUNT_RESMADE,
0                                                                            S_COUNT_RESCANCELED,
0                                                                            S_COUNT_CANCFOR_DAY,
0                                                                            S_OCC_ROOMS_BUD, 
0                                                                            S_OCC_ADULTS_BUD, 
0                                                                            S_OCC_CHILDS_BUD,
0                                                                            S_BUD_ROOM_REV_GROSS,
0                                                                            S_BUD_ROOM_REV_NET,
0                                                                            S_BUD_FB_REV_GROSS,
0                                                                            S_BUD_FB_REV_NET,
0                                                                            S_BUD_OTHER_REV_GROSS,
0                                                                            S_BUD_OTHER_REV_NET,
0                                                                            S_NOT_USED,
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_ARR_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_DEP_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_ARR_DAYUSE_RMS_DEF, 
V8_SYS_HS_DEP_ALLROOM.DEFDEPROOMDAYUSE     S_DEP_DAYUSE_RMS_DEF,
0                                                                            S_DAYUSE_ADLT_DEF,  
0                                                                            S_DAYUSE_ADLT_TENT, 
0                                                                            S_DAYUSE_CHILDS_DEF,
0                                                                            S_DAYUSE_CHILDS_TENT,
0                                                                            S_BLOCK_ALL_DEF,
0                                                                            S_BLOCK_ALL_TENT,
0                                                                            S_BLOCK_NOT_PICKUP_DEF,
0                                                                            S_BLOCK_NOT_PICKUP_TENT,
0                                                                            S_BLOCK_PICKUP_DEF,
0                                                                            S_BLOCK_PICKUP_TENT,
0                                                                            S_DAYUSE_COMP_ROOMS,
0                                                                            S_DAYUSE_HOUSE_ROOMS
 from V8_SYS_HS_DEP_ALLROOM
union all
select
WDAT.WDAT_DATE                                                               S_POSDATE,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_OCC_ROOMS_E1P_DEF,
0                                                                            S_OCC_ROOMS_E1P_TENT,
0                                                                            S_OCC_ROOMS_M1P_DEF,
0                                                                            S_OCC_ROOMS_M1P_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
nvl(S_SYS_YDET_ADULT_WB. S_DEFADULTNO,0)-nvl(S_SYS_YDET_ADULT_WB.S_DEFBLOCKSUBSTRACT,0)+nvl(S_SYS_YBLD_OCC.S_DEFNOOFADULTS,0)    S_OCC_ADULTS_DEF, 
nvl(S_SYS_YDET_ADULT_WB. S_TENTADULTNO,0)-nvl(S_SYS_YDET_ADULT_WB.S_TENTBLOCKSUBSTRACT,0)+nvl(S_SYS_YBLD_OCC.S_TENTNOOFADULTS,0) S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_VIP_ADULTS_DEF,
0                                                                            S_VIP_ADULTS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_COMP_ROOMS,
0                                                                            S_HOUSEUSE_ROOMS,
0                                                                            S_OOO_ROOMS,
0                                                                            S_OOS_ROOMS,
0                                                                            S_WALKIN_ROOMS,
0                                                                            S_NOSHOW_ROOMS,
0                                                                            S_DAYUSEROOMS_ROOMS,
0                                                                            S_COUNT_RESMADE,
0                                                                            S_COUNT_RESCANCELED,
0                                                                            S_COUNT_CANCFOR_DAY,
0                                                                            S_OCC_ROOMS_BUD, 
0                                                                            S_OCC_ADULTS_BUD, 
0                                                                            S_OCC_CHILDS_BUD,
0                                                                            S_BUD_ROOM_REV_GROSS,
0                                                                            S_BUD_ROOM_REV_NET,
0                                                                            S_BUD_FB_REV_GROSS,
0                                                                            S_BUD_FB_REV_NET,
0                                                                            S_BUD_OTHER_REV_GROSS,
0                                                                            S_BUD_OTHER_REV_NET,
0                                                                            S_NOT_USED,
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_ARR_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_DEP_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_ARR_DAYUSE_RMS_DEF, 
0                                                                            S_DEP_DAYUSE_RMS_DEF,
nvl(S_SYS_YDET_ADULT_WB.S_DEFADULTNODAYUSE,0)                                  S_DAYUSE_ADLT_DEF, 
nvl(S_SYS_YDET_ADULT_WB.S_TENTADULTNODAYUSE,0)                                 S_DAYUSE_ADLT_TENT,
0                                                                            S_DAYUSE_CHILDS_DEF,
0                                                                            S_DAYUSE_CHILDS_TENT,
0                                                                            S_BLOCK_ALL_DEF,
0                                                                            S_BLOCK_ALL_TENT,
0                                                                            S_BLOCK_NOT_PICKUP_DEF,
0                                                                            S_BLOCK_NOT_PICKUP_TENT,
0                                                                            S_BLOCK_PICKUP_DEF,
0                                                                            S_BLOCK_PICKUP_TENT,
0                                                                            S_DAYUSE_COMP_ROOMS,
0                                                                            S_DAYUSE_HOUSE_ROOMS
 from 
 wdat,
( select 
V8_SYS_YBLD_OCC.YBLD_DATE S_YBLD_DATE,
sum(V8_SYS_YBLD_OCC.DEFNOOFADULTS) S_DEFNOOFADULTS, 
sum(V8_SYS_YBLD_OCC.TENTNOOFADULTS) S_TENTNOOFADULTS
 from V8_SYS_YBLD_OCC
 group by V8_SYS_YBLD_OCC.YBLD_DATE) S_SYS_YBLD_OCC,
 ( select 
V8_SYS_YDET_ADULT_WB.YDET_DATE S_YDET_DATE,
sum(V8_SYS_YDET_ADULT_WB.DEFADULTNO) S_DEFADULTNO, 
sum(V8_SYS_YDET_ADULT_WB.TENTADULTNO) S_TENTADULTNO,
sum(V8_SYS_YDET_ADULT_WB.DEFBLOCKSUBSTRACT) S_DEFBLOCKSUBSTRACT,
sum(V8_SYS_YDET_ADULT_WB.TENTBLOCKSUBSTRACT) S_TENTBLOCKSUBSTRACT,
sum(V8_SYS_YDET_ADULT_WB.DEFADULTNODAYUSE) S_DEFADULTNODAYUSE,
sum(V8_SYS_YDET_ADULT_WB.TENTADULTNODAYUSE) S_TENTADULTNODAYUSE
 from V8_SYS_YDET_ADULT_WB
 group by V8_SYS_YDET_ADULT_WB.YDET_DATE ) S_SYS_YDET_ADULT_WB
 where
 WDAT.WDAT_DATE=S_SYS_YBLD_OCC.S_YBLD_DATE(+) and
 WDAT.WDAT_DATE=S_SYS_YDET_ADULT_WB.S_YDET_DATE(+)
union all
select 
YDET.YDET_DATE                                                               S_POSDATE,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_OCC_ROOMS_E1P_DEF,
0                                                                            S_OCC_ROOMS_E1P_TENT,
0                                                                            S_OCC_ROOMS_M1P_DEF,
0                                                                            S_OCC_ROOMS_M1P_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
sum(decode(YRES.YRES_RESSTATUS,1,YRGL.YRGL_NUMBER*YDET.YDET_NOOFROOMS,0))    S_OCC_CHILDS_DEF,
sum(decode(YRES.YRES_RESSTATUS,0,YRGL.YRGL_NUMBER*YDET.YDET_NOOFROOMS,0))    S_OCC_CHILDS_TENT,
0                                                                            S_VIP_ADULTS_DEF,
0                                                                            S_VIP_ADULTS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_COMP_ROOMS,
0                                                                            S_HOUSEUSE_ROOMS,
0                                                                            S_OOO_ROOMS,
0                                                                            S_OOS_ROOMS,
0                                                                            S_WALKIN_ROOMS,
0                                                                            S_NOSHOW_ROOMS,
0                                                                            S_DAYUSEROOMS_ROOMS,
0                                                                            S_COUNT_RESMADE,
0                                                                            S_COUNT_RESCANCELED,
0                                                                            S_COUNT_CANCFOR_DAY,
0                                                                            S_OCC_ROOMS_BUD, 
0                                                                            S_OCC_ADULTS_BUD, 
0                                                                            S_OCC_CHILDS_BUD,
0                                                                            S_BUD_ROOM_REV_GROSS,
0                                                                            S_BUD_ROOM_REV_NET,
0                                                                            S_BUD_FB_REV_GROSS,
0                                                                            S_BUD_FB_REV_NET,
0                                                                            S_BUD_OTHER_REV_GROSS,
0                                                                            S_BUD_OTHER_REV_NET,
0                                                                            S_NOT_USED,
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_ARR_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_DEP_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_ARR_DAYUSE_RMS_DEF, 
0                                                                            S_DEP_DAYUSE_RMS_DEF,
0                                                                            S_DAYUSE_ADLT_DEF, 
0                                                                            S_DAYUSE_ADLT_TENT,
0                                                                            S_DAYUSE_CHILDS_DEF,
0                                                                            S_DAYUSE_CHILDS_TENT,
0                                                                            S_BLOCK_ALL_DEF,
0                                                                            S_BLOCK_ALL_TENT,
0                                                                            S_BLOCK_NOT_PICKUP_DEF,
0                                                                            S_BLOCK_NOT_PICKUP_TENT,
0                                                                            S_BLOCK_PICKUP_DEF,
0                                                                            S_BLOCK_PICKUP_TENT,
0                                                                            S_DAYUSE_COMP_ROOMS,
0                                                                            S_DAYUSE_HOUSE_ROOMS
from YDET,YRGL,YRES,XCAC 
where 
YRGL.YRGL_YDET_ID=YDET.YDET_ID  and 
YRES.YRES_ID=YDET.YDET_YRES_ID and 
XCAC.XCAC_ID=YRGL.YRGL_XCAC_ID 
GROUP BY YDET.YDET_DATE
union all
select 
YDET.YDET_DATE                                                 S_POSDATE,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_OCC_ROOMS_E1P_DEF,
0                                                                            S_OCC_ROOMS_E1P_TENT,
0                                                                            S_OCC_ROOMS_M1P_DEF,
0                                                                            S_OCC_ROOMS_M1P_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_VIP_ADULTS_DEF,
0                                                                            S_VIP_ADULTS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_COMP_ROOMS,
0                                                                            S_HOUSEUSE_ROOMS,
0                                                                            S_OOO_ROOMS,
0                                                                            S_OOS_ROOMS,
0                                                                            S_WALKIN_ROOMS,
0                                                                            S_NOSHOW_ROOMS,
0                                                                            S_DAYUSEROOMS_ROOMS,
0                                                                            S_COUNT_RESMADE,
0                                                                            S_COUNT_RESCANCELED,
0                                                                            S_COUNT_CANCFOR_DAY,
0                                                                            S_OCC_ROOMS_BUD, 
0                                                                            S_OCC_ADULTS_BUD, 
0                                                                            S_OCC_CHILDS_BUD,
0                                                                            S_BUD_ROOM_REV_GROSS,
0                                                                            S_BUD_ROOM_REV_NET,
0                                                                            S_BUD_FB_REV_GROSS,
0                                                                            S_BUD_FB_REV_NET,
0                                                                            S_BUD_OTHER_REV_GROSS,
0                                                                            S_BUD_OTHER_REV_NET,
0                                                                            S_NOT_USED,
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_ARR_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_DEP_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_ARR_DAYUSE_RMS_DEF, 
0                                                                            S_DEP_DAYUSE_RMS_DEF,
0                                                                            S_DAYUSE_ADLT_DEF,  
0                                                                            S_DAYUSE_ADLT_TENT, 
sum(decode(YRES.YRES_RESSTATUS,1,YRGL.YRGL_NUMBER*YDET.YDET_NOOFROOMS,0))              S_DAYUSE_CHILDS_DEF, 
sum(decode(YRES.YRES_RESSTATUS,0,YRGL.YRGL_NUMBER*YDET.YDET_NOOFROOMS,0))             S_DAYUSE_CHILDS_TENT,
0                                                                            S_BLOCK_ALL_DEF,
0                                                                            S_BLOCK_ALL_TENT,
0                                                                            S_BLOCK_NOT_PICKUP_DEF,
0                                                                            S_BLOCK_NOT_PICKUP_TENT,
0                                                                            S_BLOCK_PICKUP_DEF, 
0                                                                            S_BLOCK_PICKUP_TENT,
0                                                                            S_DAYUSE_COMP_ROOMS,
0                                                                            S_DAYUSE_HOUSE_ROOMS
from 
YDET,YRGL,YRES,XCAC 
where 
YRGL.YRGL_YDET_ID=YDET.YDET_ID  and 
YRES.YRES_ID=YDET.YDET_YRES_ID and 
XCAC.XCAC_ID=YRGL.YRGL_XCAC_ID and
YRES.YRES_EXPARRTIME = YRES.YRES_EXPDEPTIME
GROUP BY YDET.YDET_DATE
union all
select 
V8_REP_STAT_VIPADULT.WDAT_DATE                                               S_POSDATE,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_OCC_ROOMS_E1P_DEF,
0                                                                            S_OCC_ROOMS_E1P_TENT,
0                                                                            S_OCC_ROOMS_M1P_DEF,
0                                                                            S_OCC_ROOMS_M1P_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
V8_REP_STAT_VIPADULT.VIP_ADULTS_DEF                                          S_VIP_ADULTS_DEF,
V8_REP_STAT_VIPADULT.VIP_ADULTS_TENT                                         S_VIP_ADULTS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_COMP_ROOMS,
0                                                                            S_HOUSEUSE_ROOMS,
0                                                                            S_OOO_ROOMS,
0                                                                            S_OOS_ROOMS,
0                                                                            S_WALKIN_ROOMS,
0                                                                            S_NOSHOW_ROOMS,
0                                                                            S_DAYUSEROOMS_ROOMS,
0                                                                            S_COUNT_RESMADE,
0                                                                            S_COUNT_RESCANCELED,
0                                                                            S_COUNT_CANCFOR_DAY,
0                                                                            S_OCC_ROOMS_BUD, 
0                                                                            S_OCC_ADULTS_BUD, 
0                                                                            S_OCC_CHILDS_BUD,
0                                                                            S_BUD_ROOM_REV_GROSS,
0                                                                            S_BUD_ROOM_REV_NET,
0                                                                            S_BUD_FB_REV_GROSS,
0                                                                            S_BUD_FB_REV_NET,
0                                                                            S_BUD_OTHER_REV_GROSS,
0                                                                            S_BUD_OTHER_REV_NET,
0                                                                            S_NOT_USED,
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_ARR_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_DEP_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_ARR_DAYUSE_RMS_DEF, 
0                                                                            S_DEP_DAYUSE_RMS_DEF,
0                                                                            S_DAYUSE_ADLT_DEF, 
0                                                                            S_DAYUSE_ADLT_TENT,
0                                                                            S_DAYUSE_CHILDS_DEF,
0                                                                            S_DAYUSE_CHILDS_TENT,
0                                                                            S_BLOCK_ALL_DEF,
0                                                                            S_BLOCK_ALL_TENT,
0                                                                            S_BLOCK_NOT_PICKUP_DEF,
0                                                                            S_BLOCK_NOT_PICKUP_TENT,
0                                                                            S_BLOCK_PICKUP_DEF,
0                                                                            S_BLOCK_PICKUP_TENT,
0                                                                            S_DAYUSE_COMP_ROOMS,
0                                                                            S_DAYUSE_HOUSE_ROOMS
 from V8_REP_STAT_VIPADULT
union all
select 
V8_REP_STAT_NOOF_C_H_PER_DAY.WDAT_DATE                                       S_POSDATE,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_OCC_ROOMS_E1P_DEF,
0                                                                            S_OCC_ROOMS_E1P_TENT,
0                                                                            S_OCC_ROOMS_M1P_DEF,
0                                                                            S_OCC_ROOMS_M1P_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_VIP_ADULTS_DEF,
0                                                                            S_VIP_ADULTS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
V8_REP_STAT_NOOF_C_H_PER_DAY.COMP_ROOMS                                      S_COMP_ROOMS,
V8_REP_STAT_NOOF_C_H_PER_DAY.HOUSE_ROOMS                                     S_HOUSEUSE_ROOMS,
0                                                                            S_OOO_ROOMS,
0                                                                            S_OOS_ROOMS,
0                                                                            S_WALKIN_ROOMS,
0                                                                            S_NOSHOW_ROOMS,
0                                                                            S_DAYUSEROOMS_ROOMS,
0                                                                            S_COUNT_RESMADE,
0                                                                            S_COUNT_RESCANCELED,
0                                                                            S_COUNT_CANCFOR_DAY,
0                                                                            S_OCC_ROOMS_BUD, 
0                                                                            S_OCC_ADULTS_BUD, 
0                                                                            S_OCC_CHILDS_BUD,
0                                                                            S_BUD_ROOM_REV_GROSS,
0                                                                            S_BUD_ROOM_REV_NET,
0                                                                            S_BUD_FB_REV_GROSS,
0                                                                            S_BUD_FB_REV_NET,
0                                                                            S_BUD_OTHER_REV_GROSS,
0                                                                            S_BUD_OTHER_REV_NET,
0                                                                            S_NOT_USED,
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_ARR_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_DEP_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_ARR_DAYUSE_RMS_DEF, 
0                                                                            S_DEP_DAYUSE_RMS_DEF,
0                                                                            S_DAYUSE_ADLT_DEF, 
0                                                                            S_DAYUSE_ADLT_TENT,
0                                                                            S_DAYUSE_CHILDS_DEF,
0                                                                            S_DAYUSE_CHILDS_TENT,
0                                                                            S_BLOCK_ALL_DEF,
0                                                                            S_BLOCK_ALL_TENT,
0                                                                            S_BLOCK_NOT_PICKUP_DEF,
0                                                                            S_BLOCK_NOT_PICKUP_TENT,
0                                                                            S_BLOCK_PICKUP_DEF,
0                                                                            S_BLOCK_PICKUP_TENT,
0                                                                            S_DAYUSE_COMP_ROOMS,
0                                                                            S_DAYUSE_HOUSE_ROOMS
 from V8_REP_STAT_NOOF_C_H_PER_DAY
union all
select 
V8_SYS_NOOFOUTOFORDER.WDAT_DATE                                              S_POSDATE,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_OCC_ROOMS_E1P_DEF,
0                                                                            S_OCC_ROOMS_E1P_TENT,
0                                                                            S_OCC_ROOMS_M1P_DEF,
0                                                                            S_OCC_ROOMS_M1P_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_VIP_ADULTS_DEF,
0                                                                            S_VIP_ADULTS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_COMP_ROOMS,
0                                                                            S_HOUSEUSE_ROOMS,
V8_SYS_NOOFOUTOFORDER.OOO                                                    S_OOO_ROOMS,
V8_SYS_NOOFOUTOFORDER.OOS                                                    S_OOS_ROOMS,
0                                                                            S_WALKIN_ROOMS,
0                                                                            S_NOSHOW_ROOMS,
0                                                                            S_DAYUSEROOMS_ROOMS,
0                                                                            S_COUNT_RESMADE,
0                                                                            S_COUNT_RESCANCELED,
0                                                                            S_COUNT_CANCFOR_DAY,
0                                                                            S_OCC_ROOMS_BUD, 
0                                                                            S_OCC_ADULTS_BUD, 
0                                                                            S_OCC_CHILDS_BUD,
0                                                                            S_BUD_ROOM_REV_GROSS,
0                                                                            S_BUD_ROOM_REV_NET,
0                                                                            S_BUD_FB_REV_GROSS,
0                                                                            S_BUD_FB_REV_NET,
0                                                                            S_BUD_OTHER_REV_GROSS,
0                                                                            S_BUD_OTHER_REV_NET,
0                                                                            S_NOT_USED,
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_ARR_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_DEP_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_ARR_DAYUSE_RMS_DEF, 
0                                                                            S_DEP_DAYUSE_RMS_DEF,
0                                                                            S_DAYUSE_ADLT_DEF, 
0                                                                            S_DAYUSE_ADLT_TENT,
0                                                                            S_DAYUSE_CHILDS_DEF,
0                                                                            S_DAYUSE_CHILDS_TENT,
0                                                                            S_BLOCK_ALL_DEF,
0                                                                            S_BLOCK_ALL_TENT,
0                                                                            S_BLOCK_NOT_PICKUP_DEF,
0                                                                            S_BLOCK_NOT_PICKUP_TENT,
0                                                                            S_BLOCK_PICKUP_DEF,
0                                                                            S_BLOCK_PICKUP_TENT,
0                                                                            S_DAYUSE_COMP_ROOMS,
0                                                                            S_DAYUSE_HOUSE_ROOMS
 from 
V8_SYS_NOOFOUTOFORDER
union all
select 
V8_REP_STAT_RESCREATED_PERDAY.CREATE_DATE                                    S_POSDATE,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_OCC_ROOMS_E1P_DEF,
0                                                                            S_OCC_ROOMS_E1P_TENT,
0                                                                            S_OCC_ROOMS_M1P_DEF,
0                                                                            S_OCC_ROOMS_M1P_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_VIP_ADULTS_DEF,
0                                                                            S_VIP_ADULTS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_COMP_ROOMS,
0                                                                            S_HOUSEUSE_ROOMS,
0                                                                            S_OOO_ROOMS,
0                                                                            S_OOS_ROOMS,
0                                                                            S_WALKIN_ROOMS,
0                                                                            S_NOSHOW_ROOMS,
0                                                                            S_DAYUSEROOMS_ROOMS,
V8_REP_STAT_RESCREATED_PERDAY.RES_COUNT                                      S_COUNT_RESMADE,
0                                                                            S_COUNT_RESCANCELED,
0                                                                            S_COUNT_CANCFOR_DAY,
0                                                                            S_OCC_ROOMS_BUD, 
0                                                                            S_OCC_ADULTS_BUD, 
0                                                                            S_OCC_CHILDS_BUD,
0                                                                            S_BUD_ROOM_REV_GROSS,
0                                                                            S_BUD_ROOM_REV_NET,
0                                                                            S_BUD_FB_REV_GROSS,
0                                                                            S_BUD_FB_REV_NET,
0                                                                            S_BUD_OTHER_REV_GROSS,
0                                                                            S_BUD_OTHER_REV_NET,
0                                                                            S_NOT_USED,
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_ARR_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_DEP_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_ARR_DAYUSE_RMS_DEF, 
0                                                                            S_DEP_DAYUSE_RMS_DEF,
0                                                                            S_DAYUSE_ADLT_DEF, 
0                                                                            S_DAYUSE_ADLT_TENT,
0                                                                            S_DAYUSE_CHILDS_DEF,
0                                                                            S_DAYUSE_CHILDS_TENT,
0                                                                            S_BLOCK_ALL_DEF,
0                                                                            S_BLOCK_ALL_TENT,
0                                                                            S_BLOCK_NOT_PICKUP_DEF,
0                                                                            S_BLOCK_NOT_PICKUP_TENT,
0                                                                            S_BLOCK_PICKUP_DEF,
0                                                                            S_BLOCK_PICKUP_TENT,
0                                                                            S_DAYUSE_COMP_ROOMS,
0                                                                            S_DAYUSE_HOUSE_ROOMS
 from V8_REP_STAT_RESCREATED_PERDAY
union all
select 
V8_REP_STAT_RESCANCELLED.CANCEL_DATE                                         S_POSDATE,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_OCC_ROOMS_E1P_DEF,
0                                                                            S_OCC_ROOMS_E1P_TENT,
0                                                                            S_OCC_ROOMS_M1P_DEF,
0                                                                            S_OCC_ROOMS_M1P_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_VIP_ADULTS_DEF,
0                                                                            S_VIP_ADULTS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_COMP_ROOMS,
0                                                                            S_HOUSEUSE_ROOMS,
0                                                                            S_OOO_ROOMS,
0                                                                            S_OOS_ROOMS,
0                                                                            S_WALKIN_ROOMS,
0                                                                            S_NOSHOW_ROOMS,
0                                                                            S_DAYUSEROOMS_ROOMS,
0                                                                            S_COUNT_RESMADE,
V8_REP_STAT_RESCANCELLED.DEFINITE                                            S_COUNT_RESCANCELED,
0                                                                            S_COUNT_CANCFOR_DAY,
0                                                                            S_OCC_ROOMS_BUD, 
0                                                                            S_OCC_ADULTS_BUD, 
0                                                                            S_OCC_CHILDS_BUD,
0                                                                            S_BUD_ROOM_REV_GROSS,
0                                                                            S_BUD_ROOM_REV_NET,
0                                                                            S_BUD_FB_REV_GROSS,
0                                                                            S_BUD_FB_REV_NET,
0                                                                            S_BUD_OTHER_REV_GROSS,
0                                                                            S_BUD_OTHER_REV_NET,
0                                                                            S_NOT_USED,
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_ARR_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_DEP_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_ARR_DAYUSE_RMS_DEF, 
0                                                                            S_DEP_DAYUSE_RMS_DEF,
0                                                                            S_DAYUSE_ADLT_DEF, 
0                                                                            S_DAYUSE_ADLT_TENT,
0                                                                            S_DAYUSE_CHILDS_DEF,
0                                                                            S_DAYUSE_CHILDS_TENT,
0                                                                            S_BLOCK_ALL_DEF,
0                                                                            S_BLOCK_ALL_TENT,
0                                                                            S_BLOCK_NOT_PICKUP_DEF,
0                                                                            S_BLOCK_NOT_PICKUP_TENT,
0                                                                            S_BLOCK_PICKUP_DEF,
0                                                                            S_BLOCK_PICKUP_TENT,
0                                                                            S_DAYUSE_COMP_ROOMS,
0                                                                            S_DAYUSE_HOUSE_ROOMS
 from V8_REP_STAT_RESCANCELLED
union all
select 
V8_REP_STAT_RESCANCEL_FORDAY.RES_DATE                                        S_POSDATE,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_OCC_ROOMS_E1P_DEF,
0                                                                            S_OCC_ROOMS_E1P_TENT,
0                                                                            S_OCC_ROOMS_M1P_DEF,
0                                                                            S_OCC_ROOMS_M1P_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_VIP_ADULTS_DEF,
0                                                                            S_VIP_ADULTS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_COMP_ROOMS,
0                                                                            S_HOUSEUSE_ROOMS,
0                                                                            S_OOO_ROOMS,
0                                                                            S_OOS_ROOMS,
0                                                                            S_WALKIN_ROOMS,
0                                                                            S_NOSHOW_ROOMS,
0                                                                            S_DAYUSEROOMS_ROOMS,
0                                                                            S_COUNT_RESMADE,
0                                                                            S_COUNT_RESCANCELED,
V8_REP_STAT_RESCANCEL_FORDAY.DEFINITE                                        S_COUNT_CANCFOR_DAY,
0                                                                            S_OCC_ROOMS_BUD, 
0                                                                            S_OCC_ADULTS_BUD, 
0                                                                            S_OCC_CHILDS_BUD,
0                                                                            S_BUD_ROOM_REV_GROSS,
0                                                                            S_BUD_ROOM_REV_NET,
0                                                                            S_BUD_FB_REV_GROSS,
0                                                                            S_BUD_FB_REV_NET,
0                                                                            S_BUD_OTHER_REV_GROSS,
0                                                                            S_BUD_OTHER_REV_NET,
0                                                                            S_NOT_USED,
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_ARR_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_DEP_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_ARR_DAYUSE_RMS_DEF, 
0                                                                            S_DEP_DAYUSE_RMS_DEF,
0                                                                            S_DAYUSE_ADLT_DEF, 
0                                                                            S_DAYUSE_ADLT_TENT,
0                                                                            S_DAYUSE_CHILDS_DEF,
0                                                                            S_DAYUSE_CHILDS_TENT,
0                                                                            S_BLOCK_ALL_DEF,
0                                                                            S_BLOCK_ALL_TENT,
0                                                                            S_BLOCK_NOT_PICKUP_DEF,
0                                                                            S_BLOCK_NOT_PICKUP_TENT,
0                                                                            S_BLOCK_PICKUP_DEF,
0                                                                            S_BLOCK_PICKUP_TENT,
0                                                                            S_DAYUSE_COMP_ROOMS,
0                                                                            S_DAYUSE_HOUSE_ROOMS
 from 
V8_REP_STAT_RESCANCEL_FORDAY
union all 
select 
V.YPOS_DATE                                                                  S_POSDATE,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_OCC_ROOMS_E1P_DEF,
0                                                                            S_OCC_ROOMS_E1P_TENT,
0                                                                            S_OCC_ROOMS_M1P_DEF,
0                                                                            S_OCC_ROOMS_M1P_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_VIP_ADULTS_DEF,
0                                                                            S_VIP_ADULTS_TENT,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
V.DEF_REV_GROSS                                                              S_DEV_ROOM_REV_GROSS,
V.TENT_REV_GROSS                                                             S_TENT_ROOM_REV_GROSS,
V.DEF_REV_NET                                                                S_DEV_ROOM_REV_NET,
V.TENT_REV_NET                                                               S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_COMP_ROOMS,
0                                                                            S_HOUSEUSE_ROOMS,
0                                                                            S_OOO_ROOMS,
0                                                                            S_OOS_ROOMS,
0                                                                            S_WALKIN_ROOMS,
0                                                                            S_NOSHOW_ROOMS,
0                                                                            S_DAYUSEROOMS_ROOMS,
0                                                                            S_COUNT_RESMADE,
0                                                                            S_COUNT_RESCANCELED,
0                                                                            S_COUNT_CANCFOR_DAY,
0                                                                            S_OCC_ROOMS_BUD, 
0                                                                            S_OCC_ADULTS_BUD, 
0                                                                            S_OCC_CHILDS_BUD,
0                                                                            S_BUD_ROOM_REV_GROSS,
0                                                                            S_BUD_ROOM_REV_NET,
0                                                                            S_BUD_FB_REV_GROSS,
0                                                                            S_BUD_FB_REV_NET,
0                                                                            S_BUD_OTHER_REV_GROSS,
0                                                                            S_BUD_OTHER_REV_NET,
0                                                                            S_NOT_USED,
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_ARR_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_DEP_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_ARR_DAYUSE_RMS_DEF, 
0                                                                            S_DEP_DAYUSE_RMS_DEF,
0                                                                            S_DAYUSE_ADLT_DEF, 
0                                                                            S_DAYUSE_ADLT_TENT,
0                                                                            S_DAYUSE_CHILDS_DEF,
0                                                                            S_DAYUSE_CHILDS_TENT,
0                                                                            S_BLOCK_ALL_DEF,
0                                                                            S_BLOCK_ALL_TENT,
0                                                                            S_BLOCK_NOT_PICKUP_DEF,
0                                                                            S_BLOCK_NOT_PICKUP_TENT,
0                                                                            S_BLOCK_PICKUP_DEF,
0                                                                            S_BLOCK_PICKUP_TENT,
0                                                                            S_DAYUSE_COMP_ROOMS,
0                                                                            S_DAYUSE_HOUSE_ROOMS
from
V8_REP_YPOS_FC_STATSTYPE1 V 
union all
select 
V.YPOS_DATE                                                                  S_POSDATE,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_OCC_ROOMS_E1P_DEF,
0                                                                            S_OCC_ROOMS_E1P_TENT,
0                                                                            S_OCC_ROOMS_M1P_DEF,
0                                                                            S_OCC_ROOMS_M1P_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_VIP_ADULTS_DEF,
0                                                                            S_VIP_ADULTS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
V.DEF_REV_GROSS                                                              S_DEV_FB_REV_GROSS,
V.TENT_REV_GROSS                                                             S_TENT_FB_REV_GROSS,
V.DEF_REV_NET                                                                S_DEV_FB_REV_NET,
V.TENT_REV_NET                                                               S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_COMP_ROOMS,
0                                                                            S_HOUSEUSE_ROOMS,
0                                                                            S_OOO_ROOMS,
0                                                                            S_OOS_ROOMS,
0                                                                            S_WALKIN_ROOMS,
0                                                                            S_NOSHOW_ROOMS,
0                                                                            S_DAYUSEROOMS_ROOMS,
0                                                                            S_COUNT_RESMADE,
0                                                                            S_COUNT_RESCANCELED,
0                                                                            S_COUNT_CANCFOR_DAY,
0                                                                            S_OCC_ROOMS_BUD, 
0                                                                            S_OCC_ADULTS_BUD, 
0                                                                            S_OCC_CHILDS_BUD,
0                                                                            S_BUD_ROOM_REV_GROSS,
0                                                                            S_BUD_ROOM_REV_NET,
0                                                                            S_BUD_FB_REV_GROSS,
0                                                                            S_BUD_FB_REV_NET,
0                                                                            S_BUD_OTHER_REV_GROSS,
0                                                                            S_BUD_OTHER_REV_NET,
0                                                                            S_NOT_USED,
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_ARR_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_DEP_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_ARR_DAYUSE_RMS_DEF, 
0                                                                            S_DEP_DAYUSE_RMS_DEF,
0                                                                            S_DAYUSE_ADLT_DEF, 
0                                                                            S_DAYUSE_ADLT_TENT,
0                                                                            S_DAYUSE_CHILDS_DEF,
0                                                                            S_DAYUSE_CHILDS_TENT,
0                                                                            S_BLOCK_ALL_DEF,
0                                                                            S_BLOCK_ALL_TENT,
0                                                                            S_BLOCK_NOT_PICKUP_DEF,
0                                                                            S_BLOCK_NOT_PICKUP_TENT,
0                                                                            S_BLOCK_PICKUP_DEF,
0                                                                            S_BLOCK_PICKUP_TENT,
0                                                                            S_DAYUSE_COMP_ROOMS,
0                                                                            S_DAYUSE_HOUSE_ROOMS
from
V8_REP_YPOS_FC_STATSTYPE2 V 
union all
select 
V.YPOS_DATE                                                                  S_POSDATE,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_OCC_ROOMS_E1P_DEF,
0                                                                            S_OCC_ROOMS_E1P_TENT,
0                                                                            S_OCC_ROOMS_M1P_DEF,
0                                                                            S_OCC_ROOMS_M1P_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_VIP_ADULTS_DEF,
0                                                                            S_VIP_ADULTS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
V.DEF_REV_GROSS                                                              S_DEV_OTHER_REV_GROSS,
V.TENT_REV_GROSS                                                             S_TENT_OTHER_REV_GROSS,
V.DEF_REV_NET                                                                S_DEV_OTHER_REV_NET,
V.TENT_REV_NET                                                               S_TENT_OTHER_REV_NET,
0                                                                            S_COMP_ROOMS,
0                                                                            S_HOUSEUSE_ROOMS,
0                                                                            S_OOO_ROOMS,
0                                                                            S_OOS_ROOMS,
0                                                                            S_WALKIN_ROOMS,
0                                                                            S_NOSHOW_ROOMS,
0                                                                            S_DAYUSEROOMS_ROOMS,
0                                                                            S_COUNT_RESMADE,
0                                                                            S_COUNT_RESCANCELED,
0                                                                            S_COUNT_CANCFOR_DAY,
0                                                                            S_OCC_ROOMS_BUD, 
0                                                                            S_OCC_ADULTS_BUD, 
0                                                                            S_OCC_CHILDS_BUD,
0                                                                            S_BUD_ROOM_REV_GROSS,
0                                                                            S_BUD_ROOM_REV_NET,
0                                                                            S_BUD_FB_REV_GROSS,
0                                                                            S_BUD_FB_REV_NET,
0                                                                            S_BUD_OTHER_REV_GROSS,
0                                                                            S_BUD_OTHER_REV_NET,
0                                                                            S_NOT_USED,
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_ARR_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_DEP_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_ARR_DAYUSE_RMS_DEF, 
0                                                                            S_DEP_DAYUSE_RMS_DEF,
0                                                                            S_DAYUSE_ADLT_DEF, 
0                                                                            S_DAYUSE_ADLT_TENT,
0                                                                            S_DAYUSE_CHILDS_DEF,
0                                                                            S_DAYUSE_CHILDS_TENT,
0                                                                            S_BLOCK_ALL_DEF,
0                                                                            S_BLOCK_ALL_TENT,
0                                                                            S_BLOCK_NOT_PICKUP_DEF,
0                                                                            S_BLOCK_NOT_PICKUP_TENT,
0                                                                            S_BLOCK_PICKUP_DEF,
0                                                                            S_BLOCK_PICKUP_TENT,
0                                                                            S_DAYUSE_COMP_ROOMS,
0                                                                            S_DAYUSE_HOUSE_ROOMS
from
V8_REP_YPOS_FC_STATSTYPE345 V 
union all
select 
SMBD.SMBD_DATE                                                               S_POSDATE,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_OCC_ROOMS_E1P_DEF,
0                                                                            S_OCC_ROOMS_E1P_TENT,
0                                                                            S_OCC_ROOMS_M1P_DEF,
0                                                                            S_OCC_ROOMS_M1P_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_VIP_ADULTS_DEF,
0                                                                            S_VIP_ADULTS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_COMP_ROOMS,
0                                                                            S_HOUSEUSE_ROOMS,
0                                                                            S_OOO_ROOMS,
0                                                                            S_OOS_ROOMS,
0                                                                            S_WALKIN_ROOMS,
0                                                                            S_NOSHOW_ROOMS,
0                                                                            S_DAYUSEROOMS_ROOMS,
0                                                                            S_COUNT_RESMADE,
0                                                                            S_COUNT_RESCANCELED,
0                                                                            S_COUNT_CANCFOR_DAY,
SMBD.SMBD_ROOMS_OCC_D                                                        S_OCC_ROOMS_BUD, 
SMBD.SMBD_ADULTS_D                                                           S_OCC_ADULTS_BUD, 
SMBD.SMBD_CHILDS_D                                                           S_OCC_CHILDS_BUD,
SMBD.SMBD_ROOMREVGROSS_D                                                     S_BUD_ROOM_REV_GROSS,
SMBD.SMBD_ROOMREVNET_D                                                       S_BUD_ROOM_REV_NET,
SMBD.SMBD_FBREVGROSS_D                                                       S_BUD_FB_REV_GROSS,
SMBD.SMBD_FBREVNET_D                                                         S_BUD_FB_REV_NET,
nvl(SMBD.SMBD_MINIREVGROSS_D,0)+nvl( SMBD.SMBD_TELEREVGROSS_D,0)+nvl(SMBD.SMBD_MISCREVGROSS_D,0) S_BUD_OTHER_REV_GROSS,
nvl(SMBD.SMBD_MINIREVNET_D,0)+nvl(SMBD.SMBD_TELEREVNET_D,0)+nvl(SMBD.SMBD_MISCREVNET_D,0)        S_BUD_OTHER_REV_NET,
0                                                                            S_NOT_USED,
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_ARR_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_DEP_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_ARR_DAYUSE_RMS_DEF, 
0                                                                            S_DEP_DAYUSE_RMS_DEF,
0                                                                            S_DAYUSE_ADLT_DEF, 
0                                                                            S_DAYUSE_ADLT_TENT,
0                                                                            S_DAYUSE_CHILDS_DEF,
0                                                                            S_DAYUSE_CHILDS_TENT,
0                                                                            S_BLOCK_ALL_DEF,
0                                                                            S_BLOCK_ALL_TENT,
0                                                                            S_BLOCK_NOT_PICKUP_DEF,
0                                                                            S_BLOCK_NOT_PICKUP_TENT,
0                                                                            S_BLOCK_PICKUP_DEF,
0                                                                            S_BLOCK_PICKUP_TENT,
0                                                                            S_DAYUSE_COMP_ROOMS,
0                                                                            S_DAYUSE_HOUSE_ROOMS
from 
SMBD
union all
select 
WDAT.WDAT_DATE                                                 S_POSDATE,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_OCC_ROOMS_E1P_DEF,
0                                                                            S_OCC_ROOMS_E1P_TENT,
0                                                                            S_OCC_ROOMS_M1P_DEF,
0                                                                            S_OCC_ROOMS_M1P_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_VIP_ADULTS_DEF,
0                                                                            S_VIP_ADULTS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_COMP_ROOMS,
0                                                                            S_HOUSEUSE_ROOMS,
0                                                                            S_OOO_ROOMS,
0                                                                            S_OOS_ROOMS,
0                                                                            S_WALKIN_ROOMS,
0                                                                            S_NOSHOW_ROOMS,
0                                                                            S_DAYUSEROOMS_ROOMS,
0                                                                            S_COUNT_RESMADE,
0                                                                            S_COUNT_RESCANCELED,
0                                                                            S_COUNT_CANCFOR_DAY,
0                                                                            S_OCC_ROOMS_BUD, 
0                                                                            S_OCC_ADULTS_BUD, 
0                                                                            S_OCC_CHILDS_BUD,
0                                                                            S_BUD_ROOM_REV_GROSS,
0                                                                            S_BUD_ROOM_REV_NET,
0                                                                            S_BUD_FB_REV_GROSS,
0                                                                            S_BUD_FB_REV_NET,
0                                                                            S_BUD_OTHER_REV_GROSS,
0                                                                            S_BUD_OTHER_REV_NET,
0                                                                            S_NOT_USED,
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_ARR_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_DEP_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_ARR_DAYUSE_RMS_DEF, 
0                                                                            S_DEP_DAYUSE_RMS_DEF,
0                                                                            S_DAYUSE_ADLT_DEF, 
0                                                                            S_DAYUSE_ADLT_TENT,
0                                                                            S_DAYUSE_CHILDS_DEF,
0                                                                            S_DAYUSE_CHILDS_TENT,
S_SYS_YBLD_DEF_OCC_ALL.S_NOOFROOMS               S_BLOCK_ALL_DEF,
S_SYS_YBLD_TENT_OCC_ALL.S_NOOFROOMS             S_BLOCK_ALL_TENT,
0                                                                            S_BLOCK_NOT_PICKUP_DEF,
0                                                                            S_BLOCK_NOT_PICKUP_TENT,
0                                                                            S_BLOCK_PICKUP_DEF,
0                                                                            S_BLOCK_PICKUP_TENT,
0                                                                            S_DAYUSE_COMP_ROOMS,
0                                                                            S_DAYUSE_HOUSE_ROOMS
from
WDAT,
(select
V8_SYS_YBLD_DEF_OCC_ALL.YBLD_DATE S_YBLD_DATE,
sum(V8_SYS_YBLD_DEF_OCC_ALL.NOOFROOMS) S_NOOFROOMS
from V8_SYS_YBLD_DEF_OCC_ALL
group by V8_SYS_YBLD_DEF_OCC_ALL.YBLD_DATE) S_SYS_YBLD_DEF_OCC_ALL,
(select
V8_SYS_YBLD_TENT_OCC_ALL.YBLD_DATE S_YBLD_DATE,
sum(V8_SYS_YBLD_TENT_OCC_ALL.NOOFROOMS) S_NOOFROOMS
from V8_SYS_YBLD_TENT_OCC_ALL
group by V8_SYS_YBLD_TENT_OCC_ALL.YBLD_DATE) S_SYS_YBLD_TENT_OCC_ALL
where
wdat.wdat_date = S_SYS_YBLD_DEF_OCC_ALL.S_YBLD_DATE (+) and
wdat.wdat_date = S_SYS_YBLD_TENT_OCC_ALL.S_YBLD_DATE (+)
union all
select 
V8_SYS_YBLD_NOT_PICKUP_ALL.YBLD_DATE             S_POSDATE,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_OCC_ROOMS_E1P_DEF,
0                                                                            S_OCC_ROOMS_E1P_TENT,
0                                                                            S_OCC_ROOMS_M1P_DEF,
0                                                                            S_OCC_ROOMS_M1P_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_VIP_ADULTS_DEF,
0                                                                            S_VIP_ADULTS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_COMP_ROOMS,
0                                                                            S_HOUSEUSE_ROOMS,
0                                                                            S_OOO_ROOMS,
0                                                                            S_OOS_ROOMS,
0                                                                            S_WALKIN_ROOMS,
0                                                                            S_NOSHOW_ROOMS,
0                                                                            S_DAYUSEROOMS_ROOMS,
0                                                                            S_COUNT_RESMADE,
0                                                                            S_COUNT_RESCANCELED,
0                                                                            S_COUNT_CANCFOR_DAY,
0                                                                            S_OCC_ROOMS_BUD, 
0                                                                            S_OCC_ADULTS_BUD, 
0                                                                            S_OCC_CHILDS_BUD,
0                                                                            S_BUD_ROOM_REV_GROSS,
0                                                                            S_BUD_ROOM_REV_NET,
0                                                                            S_BUD_FB_REV_GROSS,
0                                                                            S_BUD_FB_REV_NET,
0                                                                            S_BUD_OTHER_REV_GROSS,
0                                                                            S_BUD_OTHER_REV_NET,
0                                                                            S_NOT_USED,
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_ARR_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_DEP_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_ARR_DAYUSE_RMS_DEF, 
0                                                                            S_DEP_DAYUSE_RMS_DEF,
0                                                                            S_DAYUSE_ADLT_DEF, 
0                                                                            S_DAYUSE_ADLT_TENT,
0                                                                            S_DAYUSE_CHILDS_DEF,
0                                                                            S_DAYUSE_CHILDS_TENT,
0                                                                            S_BLOCK_ALL_DEF,
0                                                                            S_BLOCK_ALL_TENT,
V8_SYS_YBLD_NOT_PICKUP_ALL.DEFNOOFROOMS     S_BLOCK_NOT_PICKUP_DEF,
V8_SYS_YBLD_NOT_PICKUP_ALL.TENTNOOFROOMS   S_BLOCK_NOT_PICKUP_TENT,
0                                                                            S_BLOCK_PICKUP_DEF,
0                                                                            S_BLOCK_PICKUP_TENT,
0                                                                            S_DAYUSE_COMP_ROOMS,
0                                                                            S_DAYUSE_HOUSE_ROOMS
from
V8_SYS_YBLD_NOT_PICKUP_ALL
union all
select 
V8_SYS_YBLD_PICKUP_ALL.YBLD_DATE                     S_POSDATE,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_OCC_ROOMS_E1P_DEF,
0                                                                            S_OCC_ROOMS_E1P_TENT,
0                                                                            S_OCC_ROOMS_M1P_DEF,
0                                                                            S_OCC_ROOMS_M1P_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_VIP_ADULTS_DEF,
0                                                                            S_VIP_ADULTS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_COMP_ROOMS,
0                                                                            S_HOUSEUSE_ROOMS,
0                                                                            S_OOO_ROOMS,
0                                                                            S_OOS_ROOMS,
0                                                                            S_WALKIN_ROOMS,
0                                                                            S_NOSHOW_ROOMS,
0                                                                            S_DAYUSEROOMS_ROOMS,
0                                                                            S_COUNT_RESMADE,
0                                                                            S_COUNT_RESCANCELED,
0                                                                            S_COUNT_CANCFOR_DAY,
0                                                                            S_OCC_ROOMS_BUD, 
0                                                                            S_OCC_ADULTS_BUD, 
0                                                                            S_OCC_CHILDS_BUD,
0                                                                            S_BUD_ROOM_REV_GROSS,
0                                                                            S_BUD_ROOM_REV_NET,
0                                                                            S_BUD_FB_REV_GROSS,
0                                                                            S_BUD_FB_REV_NET,
0                                                                            S_BUD_OTHER_REV_GROSS,
0                                                                            S_BUD_OTHER_REV_NET,
0                                                                            S_NOT_USED,
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_ARR_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_DEP_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_ARR_DAYUSE_RMS_DEF, 
0                                                                            S_DEP_DAYUSE_RMS_DEF,
0                                                                            S_DAYUSE_ADLT_DEF, 
0                                                                            S_DAYUSE_ADLT_TENT,
0                                                                            S_DAYUSE_CHILDS_DEF,
0                                                                            S_DAYUSE_CHILDS_TENT,
0                                                                            S_BLOCK_ALL_DEF,
0                                                                            S_BLOCK_ALL_TENT,
0                                                                            S_BLOCK_NOT_PICKUP_DEF,
0                                                                            S_BLOCK_NOT_PICKUP_TENT,
V8_SYS_YBLD_PICKUP_ALL.DEFNOOFROOMS             S_BLOCK_PICKUP_DEF, 
V8_SYS_YBLD_PICKUP_ALL.TENTNOOFROOMS           S_BLOCK_PICKUP_TENT,
0                                                                            S_DAYUSE_COMP_ROOMS,
0                                                                            S_DAYUSE_HOUSE_ROOMS
from
V8_SYS_YBLD_PICKUP_ALL
union all
select 
V8_REP_NOOF_C_H_PER_DAY_D_USE.WDAT_DATE                                       S_POSDATE,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_OCC_ROOMS_E1P_DEF,
0                                                                            S_OCC_ROOMS_E1P_TENT,
0                                                                            S_OCC_ROOMS_M1P_DEF,
0                                                                            S_OCC_ROOMS_M1P_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_VIP_ADULTS_DEF,
0                                                                            S_VIP_ADULTS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_COMP_ROOMS,
0                                                                            S_HOUSEUSE_ROOMS,
0                                                                            S_OOO_ROOMS,
0                                                                            S_OOS_ROOMS,
0                                                                            S_WALKIN_ROOMS,
0                                                                            S_NOSHOW_ROOMS,
0                                                                            S_DAYUSEROOMS_ROOMS,
0                                                                            S_COUNT_RESMADE,
0                                                                            S_COUNT_RESCANCELED,
0                                                                            S_COUNT_CANCFOR_DAY,
0                                                                            S_OCC_ROOMS_BUD, 
0                                                                            S_OCC_ADULTS_BUD, 
0                                                                            S_OCC_CHILDS_BUD,
0                                                                            S_BUD_ROOM_REV_GROSS,
0                                                                            S_BUD_ROOM_REV_NET,
0                                                                            S_BUD_FB_REV_GROSS,
0                                                                            S_BUD_FB_REV_NET,
0                                                                            S_BUD_OTHER_REV_GROSS,
0                                                                            S_BUD_OTHER_REV_NET,
0                                                                            S_NOT_USED,
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_ARR_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_DEP_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_ARR_DAYUSE_RMS_DEF, 
0                                                                            S_DEP_DAYUSE_RMS_DEF,
0                                                                            S_DAYUSE_ADLT_DEF, 
0                                                                            S_DAYUSE_ADLT_TENT,
0                                                                            S_DAYUSE_CHILDS_DEF,
0                                                                            S_DAYUSE_CHILDS_TENT,
0                                                                            S_BLOCK_ALL_DEF,
0                                                                            S_BLOCK_ALL_TENT,
0                                                                            S_BLOCK_NOT_PICKUP_DEF,
0                                                                            S_BLOCK_NOT_PICKUP_TENT,
0                                                                            S_BLOCK_PICKUP_DEF,
0                                                                            S_BLOCK_PICKUP_TENT,
V8_REP_NOOF_C_H_PER_DAY_D_USE.COMP_ROOMS            S_DAYUSE_COMP_ROOMS,
V8_REP_NOOF_C_H_PER_DAY_D_USE.HOUSE_ROOMS           S_DAYUSE_HOUSE_ROOMS
from V8_REP_NOOF_C_H_PER_DAY_D_USE
union all
select 
WDAT_DATE                                                                    S_POSDATE,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_OCC_ROOMS_E1P_DEF,
0                                                                            S_OCC_ROOMS_E1P_TENT,
0                                                                            S_OCC_ROOMS_M1P_DEF,
0                                                                            S_OCC_ROOMS_M1P_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_VIP_ADULTS_DEF,
0                                                                            S_VIP_ADULTS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_COMP_ROOMS,
0                                                                            S_HOUSEUSE_ROOMS,
0                                                                            S_OOO_ROOMS,
0                                                                            S_OOS_ROOMS,
0                                                                            S_WALKIN_ROOMS,
0                                                                            S_NOSHOW_ROOMS,
0                                                                            S_DAYUSEROOMS_ROOMS,
0                                                                            S_COUNT_RESMADE,
0                                                                            S_COUNT_RESCANCELED,
0                                                                            S_COUNT_CANCFOR_DAY,
0                                                                            S_OCC_ROOMS_BUD, 
0                                                                            S_OCC_ADULTS_BUD, 
0                                                                            S_OCC_CHILDS_BUD,
0                                                                            S_BUD_ROOM_REV_GROSS,
0                                                                            S_BUD_ROOM_REV_NET,
0                                                                            S_BUD_FB_REV_GROSS,
0                                                                            S_BUD_FB_REV_NET,
0                                                                            S_BUD_OTHER_REV_GROSS,
0                                                                            S_BUD_OTHER_REV_NET,
0                                                                            S_NOT_USED,
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_DAYUSE_RMS_DEF, 
0                                                                            S_ARR_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_DEP_DAYUSE_RMS_DEF_TENT, 
0                                                                            S_ARR_DAYUSE_RMS_DEF, 
0                                                                            S_DEP_DAYUSE_RMS_DEF,
0                                                                            S_DAYUSE_ADLT_DEF, 
0                                                                            S_DAYUSE_ADLT_TENT,
0                                                                            S_DAYUSE_CHILDS_DEF,
0                                                                            S_DAYUSE_CHILDS_TENT,
0                                                                            S_BLOCK_ALL_DEF,
0                                                                            S_BLOCK_ALL_TENT,
0                                                                            S_BLOCK_NOT_PICKUP_DEF,
0                                                                            S_BLOCK_NOT_PICKUP_TENT,
0                                                                            S_BLOCK_PICKUP_DEF,
0                                                                            S_BLOCK_PICKUP_TENT,
0                                                                            S_DAYUSE_COMP_ROOMS,
0                                                                            S_DAYUSE_HOUSE_ROOMS
from
WDAT
) where not exists (select 1 from sman where SMAN.SMAN_DATE=S_POSDATE) 
group by S_POSDATE
) order by wdat_date;

CREATE OR REPLACE VIEW V8_SYS_BV_GUESTINRES AS
   SELECT yres.yres_id reservation_id, 
             acctype, 
             resinfostring,
          NVL (checked_in, 0) is_checked_in, 
          xcms.xcms_name1 lastname,
          UPPER(xcms.xcms_name1) slastname,
          xcms.xcms_name3 firstname, 
          UPPER(xcms.xcms_name3) sfirstname,
          yres.yres_exparrtime arrivaldate,
          yres.yres_expdeptime departuredate, 
          yrms_shortdesc roomnumber,
          ygrp_id GROUP_ID, 
          ygrp_name group_name,
          xcid.xcid_birthtime dayofbirth, 
          xcms.xcms_id foreignid,
          (SELECT MAX (xpdc.xpdc_value)
             FROM xpdc, xpdg
            WHERE xpdc.xpdc_xcms_id = xcms.xcms_id
                  AND xpdc.xpdc_xpdg_id = xpdg.xpdg_id
                  AND xpdg.xpdg_role = 99) 
                  membernumber,
          xadr.xadr_street1 street1, 
          xadr.xadr_street2 street2,
          xadr.xadr_city city, 
          xadr.xadr_zip zip,
          xcou_address.xcou_iso2 country_iso2,
          xcou_address.xcou_longdesc country,
          (SELECT MAX (xcom.xcom_value)
             FROM xcom, xcmt
            WHERE xcom.xcom_xcms_id = xcms.xcms_id
              AND xcom.xcom_xcmt_id = xcmt.xcmt_id
              AND xcmt.xcmt_type = 4
              AND NVL (xcom.xcom_primary, 0) = 1) telephone,
          (SELECT MAX (xcom.xcom_value)
             FROM xcom, xcmt
            WHERE xcom.xcom_xcms_id = xcms.xcms_id
              AND xcom.xcom_xcmt_id = xcmt.xcmt_id
              AND xcmt.xcmt_type = 3
              AND NVL (xcom.xcom_primary, 0) = 1) telefax,
          (SELECT MAX (xcom.xcom_value)
             FROM xcom, xcmt
            WHERE xcom.xcom_xcms_id = xcms.xcms_id
              AND xcom.xcom_xcmt_id = xcmt.xcmt_id
              AND xcmt.xcmt_type = 9
              AND NVL (xcom.xcom_primary, 0) = 1) mobile,
          (SELECT MAX (xcom.xcom_value)
             FROM xcom, xcmt
            WHERE xcom.xcom_xcms_id = xcms.xcms_id
              AND xcom.xcom_xcmt_id = xcmt.xcmt_id
              AND xcmt.xcmt_type = 1
              AND NVL (xcom.xcom_primary, 0) = 1) email,
          xcid.xcid_addrgreet title, 
          wlan.wlan_shortdesc LANGUAGE,
          xcou_nationality.xcou_iso2 nationality_iso2,
          xcou_nationality.xcou_longdesc nationality, 
          xcid.xcid_sex sex,
          xcid.xcid_lettgreet salutation,
          (SELECT MAX (xpdg.xpdg_longdesc)
             FROM xpdc, xpdg
            WHERE xpdc.xpdc_xcms_id = xcms.xcms_id
              AND xpdc.xpdc_xpdg_id = xpdg.xpdg_id
              AND xpdg.xpdg_role = 99) membercard,
          (SELECT MAX (xpdc.xpdc_validfrom)
             FROM xpdc, xpdg
            WHERE xpdc.xpdc_xcms_id = xcms.xcms_id
              AND xpdc.xpdc_xpdg_id = xpdg.xpdg_id
              AND xpdg.xpdg_role = 99) membersince,
          (SELECT MAX (xpdc.xpdc_expiry)
             FROM xpdc, xpdg
            WHERE xpdc.xpdc_xcms_id = xcms.xcms_id
              AND xpdc.xpdc_xpdg_id = xpdg.xpdg_id
              AND xpdg.xpdg_role = 99) membercardvalidity,
          xcca.xcca_longdesc guesttype, 
          xcno.xcno_notes remark,
          DECODE (xcms.xcms_type, 2, 1, 0) isguest,
          NVL ((SELECT MAX (xcil.xcil_company_xcms_id)
                  FROM xcil
                 WHERE xcil.xcil_individual_xcms_id = xcms.xcms_id),
               0
              ) company_foreignid,
          NVL ((SELECT MAX (xmnr.xmnr_value)
                  FROM xcil INNER JOIN xmnr ON xcil.xcil_company_xcms_id =
                                                             xmnr.xmnr_xcms_id
                       INNER JOIN xmty ON xmnr.xmnr_xmty_id = xmty.xmty_id
                 WHERE xcil.xcil_individual_xcms_id = xcms.xcms_id
                   AND xmty.xmty_role = 3),
               ''
              ) company_vatcode,
          wphs.wphs_name AS resort, 
          '' AS vat_id, 
          yres_vacant,
          YRES.WEIGHT AS mainguest,
          YRES.NOPOST as noposting,
          CreditLimit
     FROM ((SELECT YRES_VACANT, 'RES' acctype, yres.yres_id, yrpl_xcms_id yres_xcms_id,
                  (SELECT yrms.yrms_shortdesc
                     FROM yrms, ydet
                    WHERE yrms.yrms_id = ydet.ydet_yrms_id
                      AND ydet.ydet_id = yres.yres_current_ydet_id)
                                                               yrms_shortdesc,
                  (DECODE (yres.yres_actarrtime, NULL, 0, 1)) checked_in,
                  (   'Res. #: '
                   || yres.yres_id
                   || ', From '
                   || yres.yres_exparrtime
                   || ' to '
                   || yres.yres_expdeptime
                  ) resinfostring,
                  yres.yres_current_ydet_id ydet_id,
                  yres.yres_exparrtime yres_exparrtime,
                  yres.yres_actarrtime yres_actarrtime,
                  yres.yres_expdeptime yres_expdeptime,
                  yres.yres_actdeptime yres_actdeptime,
                  (DECODE ((yres.yres_exparrtime),
                           (SELECT wgbs.wgbs_datevalue
                              FROM wgbs), 1,
                           0
                          )
                  ) arrival_today,
                  (DECODE ((yres.yres_exparrtime),
                           (SELECT wgbs.wgbs_datevalue
                              FROM wgbs), (DECODE (yres.yres_actarrtime,
                                                   NULL, 0,
                                                   1
                                                  )),
                           0
                          )
                  ) checked_in_today,
                  (DECODE ((yres.yres_expdeptime),
                           (SELECT wgbs.wgbs_datevalue
                              FROM wgbs), 1,
                           0
                          )
                  ) departure_today,
                  (DECODE ((yres.yres_expdeptime),
                           (SELECT wgbs.wgbs_datevalue
                              FROM wgbs), (DECODE (yres.yres_actdeptime,
                                                   NULL, 0,
                                                   1
                                                  )),
                           0
                          )
                  ) checked_out_today,
                  (DECODE (yres.yres_actdeptime, NULL, 0, 1)) checked_out,
                  (SELECT ycat.ycat_shortdesc
                     FROM ycat, ydet
                    WHERE ycat.ycat_id = ydet.ydet_ycat_id
                      AND ydet.ydet_id = yres.yres_current_ydet_id)
                                                               ycat_shortdesc,
                  (SELECT ygrp.ygrp_id
                     FROM ygrp, ydet
                    WHERE ygrp.ygrp_id = ydet.ydet_ygrp_id
                      AND ydet.ydet_id = yres.yres_current_ydet_id) ygrp_id,
                  (SELECT ygrp.ygrp_name
                     FROM ygrp, ydet
                    WHERE ygrp.ygrp_id = ydet.ydet_ygrp_id
                      AND ydet.ydet_id = yres.yres_current_ydet_id) ygrp_name,
                      (CASE YRPL.YRPL_XCMS_ID WHEN YRES_XCMS_ID THEN 1 ELSE 0 END) AS WEIGHT,
                      YRES.YRES_NOPOST AS NOPOST,
                      YRES.YRES_CREDITLIMIT -nvl((select sum(V8_REP_CREDITLIMIT.BALANCE) from V8_REP_CREDITLIMIT where ZPOS_YRES_ID  = YRES_ID),0)  CreditLimit 
             FROM yres, yrpl
            WHERE yres.yres_resstatus <> 3
            ---- selects only reservations with departure date 
            ---- later than now
              AND (SELECT wgbs.wgbs_datevalue
                    FROM wgbs
                 WHERE wgbs.wgbs_name = 'system date') <= yres.yres_expdeptime
              AND yrpl.yrpl_yres_id = yres.yres_id
           UNION ALL
           SELECT 0 as YRES_VACANT, 'FA' acctype, zfac_id, zfac_xcms_id yres_xcms_id,
                  NVL (zfan_longdesc, zfac_autonumber) yrms_shortdesc,
                  (DECODE (zfac_actvalidfrom, NULL, 0, 1)) checked_in,
                  (   'FA. #: '
                   || zfac_id
                   || ', From '
                   || zfac_expvalidfrom
                   || ' to '
                   || zfac_expvaliduntil
                  ) resinfostring,
                  0 ydet_id, zfac_expvalidfrom, zfac_actvalidfrom actarrtime,
                  NVL (zfac_expvaliduntil, zfac_expvalidfrom + 365),
                  zfac_actvaliduntil actdeptime,
                  (DECODE ((zfac_expvalidfrom),
                           (SELECT wgbs.wgbs_datevalue
                              FROM wgbs), 1,
                           0
                          )
                  ) arrival_today,
                  (DECODE ((zfac_expvalidfrom),
                           (SELECT wgbs.wgbs_datevalue
                              FROM wgbs), (DECODE (zfac_actvalidfrom,
                                                   NULL, 0,
                                                   1
                                                  )),
                           0
                          )
                  ) checked_in_today,
                  (DECODE ((zfac_expvalidfrom),
                           (SELECT wgbs.wgbs_datevalue
                              FROM wgbs), 1,
                           0
                          )
                  ) departure_today,
                  (DECODE ((zfac_expvaliduntil),
                           (SELECT wgbs.wgbs_datevalue
                              FROM wgbs), (DECODE (zfac_actvaliduntil,
                                                   NULL, 0,
                                                   1
                                                  )),
                           0
                          )
                  ) checked_out_today,
                  (DECODE (zfac_actvaliduntil, NULL, 0, 1)) checked_out,
                  (SELECT zfag_shortdesc
                     FROM zfag
                    WHERE zfac_zfag_id = zfag_id) ycat_shortdesc, 0 ygrp_id,
                  '' ygrp_name,
                  0 AS WEIGHT,
                  0 AS NOPOST,
                  ZFAC.ZFAC_CREDITLIMIT -nvl((select sum(V8_REP_CREDITLIMIT.BALANCE) from V8_REP_CREDITLIMIT where V8_REP_CREDITLIMIT.ZFAC_ID  = zfac.ZFAC_ID),0) CreditLimit
             FROM zfac, zfan, zfag, fideliodate d
            WHERE zfac_zfan_id = zfan_id(+)
              AND zfac_zfag_id = zfag_id
              AND zfag_type NOT IN (1, 2)
              AND (SELECT wgbs.wgbs_datevalue
                     FROM wgbs
                    WHERE wgbs.wgbs_name = 'system date') <=
                             NVL (zfac_expvaliduntil, zfac_expvalidfrom + 365)) ORDER BY YRES_ID, WEIGHT DESC) yres,
          xcms,
          wphs,
          xcid,
          wlan,
          xcou xcou_address,
          xcou xcou_nationality,
          xcca,
          (SELECT xcno_table_id xcno_xcms_id, xcno_notes
             FROM xcno, xcnc
            WHERE xcno.xcno_tablelinkedto = 'XCMS'
              AND xcno.xcno_xcnc_id = xcnc.xcnc_id
              AND UPPER (xcnc.xcnc_shortdesc) LIKE 'BV%') xcno,
          (SELECT *
             FROM xadr
            WHERE NVL (xadr.xadr_primary, 0) = 1) xadr
    WHERE xcms.xcms_id = xcid.xcid_id(+)
      AND xcms.xcms_org_wphs_id = wphs.wphs_id(+)
      AND yres_xcms_id(+) = xcms.xcms_id
      AND xcms.xcms_id = xadr.xadr_xcms_id(+)
      AND xadr.xadr_xcou_id = xcou_address.xcou_id(+)
      AND xcms.xcms_wlan_id = wlan.wlan_id(+)
      AND xcms.xcms_xcou_id_nation = xcou_nationality.xcou_id(+)
      AND xcms.xcms_xcca_id = xcca.xcca_id(+)
      AND xcms.xcms_id = xcno_xcms_id(+)
      AND xcms.xcms_type IN (1, 2)
      AND xcms.xcms_active <> 0 ---- 0: inactive
      AND xcms.xcms_active <> 4; ---- 4: merged to another profile     

	  