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

CREATE OR REPLACE VIEW V8_REP_YHRL_INFOS
AS 
SELECT
  (select wgbs.wgbs_datevalue from wgbs) FIDELIODATE,
  nvl(YHRL.YHRL_YRMS_ID,0) YRMS_ID,   
  (select yrms.YRMS_SHORTDESC from yrms where YRMS.YRMS_ID=YHRL.YHRL_YRMS_ID) YRMS_SHORTDESC,
  (select yrms.YRMS_LONGDESC from yrms where YRMS.YRMS_ID=YHRL.YHRL_YRMS_ID) YRMS_LONGDESC,
  (select yrms.YRMS_YCAT_ID from yrms where YRMS.YRMS_ID=YHRL.YHRL_YRMS_ID) YRMS_YCAT_ID,
  nvl(YHRL.YHRL_YPUB_ID,0) YPUB_ID,
  nvl(YHRL.YHRL_YFSC_ID,0) YFSC_ID,
  decode(nvl(YHRL.YHRL_YRMS_ID,0),0,decode(nvl(YHRL.YHRL_YPUB_ID,0),0,(select YFSC.YFSC_LONGDESC from yfsc where YFSC.YFSC_ID=YHRL.YHRL_YFSC_ID),(select YPUB.YPUB_LONGDESC from ypub where YPUB.YPUB_ID=YHRL.YHRL_YPUB_ID)),(select YCAT.YCAT_SHORTDESC from ycat where YCAT.YCAT_ID=(select yrms.YRMS_YCAT_ID from yrms where YRMS.YRMS_ID=YHRL.YHRL_YRMS_ID)) ) PLACES_FS,
  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, yrms,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 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=(select yrms.YRMS_YCAT_ID from yrms where YRMS.YRMS_ID=YHRL.YHRL_YRMS_ID)) YCAT_SHORTDESC,
  (select YCAT.YCAT_LONGDESC from ycat where YCAT.YCAT_ID=(select yrms.YRMS_YCAT_ID from yrms where YRMS.YRMS_ID=YHRL.YHRL_YRMS_ID)) YCAT_LONGDESC,
  (select YRMS.YRMS_YHKS_ID from yrms where YRMS.YRMS_ID=YHRL.YHRL_YRMS_ID) YRMS_YHKS_ID,
  (select yhks.YHKS_SHORTDESC from yhks where YHKS.YHKS_ID=(select YRMS.YRMS_YHKS_ID from yrms where YRMS.YRMS_ID=YHRL.YHRL_YRMS_ID)) YHKS_SHORTDESC,
  (select yhks.YHKS_LONGDESC from yhks where YHKS.YHKS_ID=(select YRMS.YRMS_YHKS_ID from yrms where YRMS.YRMS_ID=YHRL.YHRL_YRMS_ID)) YHKS_LONGDESC,
  (select yrst.YRST_ID from yrst where ((select wgbs.wgbs_datevalue from wgbs) between yrst.YRST_FROMTIME and yrst.YRST_UNTILTIME-1) and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1) YRST_ID,
  (select yrst.YRST_YRSC_ID from yrst where ((select wgbs.wgbs_datevalue from wgbs) between yrst.YRST_FROMTIME and yrst.YRST_UNTILTIME-1) and yrst.YRST_YRMS_ID=yhrl.YHRL_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.wgbs_datevalue from wgbs) between yrst.YRST_FROMTIME and yrst.YRST_UNTILTIME-1) and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1) YRSC_SHORTDESC,
  (select yrst.YRST_FROMTIME from yrst where ((select wgbs.wgbs_datevalue from wgbs) between yrst.YRST_FROMTIME and yrst.YRST_UNTILTIME-1) and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1) YRST_FROMTIME,
  (select yrst.YRST_UNTILTIME from yrst where ((select wgbs.wgbs_datevalue from wgbs) between yrst.YRST_FROMTIME and yrst.YRST_UNTILTIME-1) and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1) YRST_UNTILTIME,
  (select yrst.YRST_ID from yrst where (select wgbs.wgbs_datevalue from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID) NEXT_YRST_ID,
  (select yrst.YRST_YRSC_ID from yrst where (select wgbs.wgbs_datevalue from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID) NEXT_YRST_YRSC_ID,
  (select yrsc.YRSC_SHORTDESC from yrsc,yrst where yrsc.YRSC_ID=yrst.YRST_YRSC_ID and (select wgbs.wgbs_datevalue from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID) NEXT_YRSC_SHORTDESC,
  (select yrst.YRST_FROMTIME from yrst where (select wgbs.wgbs_datevalue from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID) NEXT_YRST_FROMTIME,
  (select yrst.YRST_UNTILTIME from yrst where (select wgbs.wgbs_datevalue from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID) NEXT_YRST_UNTILTIME,
  decode((select yrst.YRST_ID from yrst where (select wgbs.wgbs_datevalue from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID),NULL,(select yrst.YRST_ID from yrst where ((select wgbs.wgbs_datevalue from wgbs) between yrst.YRST_FROMTIME and yrst.YRST_UNTILTIME-1) and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1),((select yrst.YRST_ID from yrst where (select wgbs.wgbs_datevalue from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID))) BOTH_YRST_ID,
  decode((select yrst.YRST_ID from yrst where (select wgbs.wgbs_datevalue from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID),NULL,(select yrst.YRST_ID from yrst where ((select wgbs.wgbs_datevalue from wgbs) between yrst.YRST_FROMTIME and yrst.YRST_UNTILTIME-1) and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1),((select yrst.YRST_ID from yrst where (select wgbs.wgbs_datevalue from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID)),NULL,0,1) IS_OOO,
  decode((select yrst.YRST_ID from yrst where (select wgbs.wgbs_datevalue from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID),NULL,(select yrsc.YRSC_SHORTDESC from yrsc,yrst where yrsc.YRSC_ID=yrst.YRST_YRSC_ID and ((select wgbs.wgbs_datevalue from wgbs) between yrst.YRST_FROMTIME and yrst.YRST_UNTILTIME-1) and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1),(select yrsc.YRSC_SHORTDESC from yrsc,yrst where yrsc.YRSC_ID=yrst.YRST_YRSC_ID and (select wgbs.wgbs_datevalue from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID)) BOTH_YRSC_SHORTDESC,
  decode((select yrst.YRST_ID from yrst where (select wgbs.wgbs_datevalue from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID),NULL,(select yrst.YRST_FROMTIME from yrst where ((select wgbs.wgbs_datevalue from wgbs) between yrst.YRST_FROMTIME and yrst.YRST_UNTILTIME-1) and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1),((select yrst.YRST_FROMTIME from yrst where (select wgbs.wgbs_datevalue from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID))) BOTH_YRST_FROMTIME,
  decode((select yrst.YRST_ID from yrst where (select wgbs.wgbs_datevalue from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID),NULL,(select yrst.YRST_UNTILTIME from yrst where ((select wgbs.wgbs_datevalue from wgbs) between yrst.YRST_FROMTIME and yrst.YRST_UNTILTIME-1) and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1),((select yrst.YRST_UNTILTIME from yrst where (select wgbs.wgbs_datevalue from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID))) BOTH_YRST_UNTILTIME,
  decode((select yrst.YRST_ID from yrst where (select wgbs.wgbs_datevalue from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID),NULL,(decode((select yrst.YRST_ID from yrst where ((select wgbs.wgbs_datevalue from wgbs) between yrst.YRST_FROMTIME and yrst.YRST_UNTILTIME-1) and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1),NULL,(select yhks.YHKS_SHORTDESC from yhks where yhks.YHKS_ID=(select YRMS.YRMS_YHKS_ID from yrms where YRMS.YRMS_ID=YHRL.YHRL_YRMS_ID)),(select yrsc.YRSC_SHORTDESC from yrsc,yrst where yrsc.YRSC_ID=yrst.YRST_YRSC_ID and ((select wgbs.wgbs_datevalue from wgbs) between yrst.YRST_FROMTIME and yrst.YRST_UNTILTIME-1) and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1))),(select yrsc.YRSC_SHORTDESC from yrsc,yrst where yrsc.YRSC_ID=yrst.YRST_YRSC_ID and (select wgbs.wgbs_datevalue from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID)) ROOM_STATUS,
  (select yres.YRES_ID from yres where (select wgbs.wgbs_datevalue from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yhrl.YHRL_YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select wgbs.wgbs_datevalue from wgbs) and rownum=1) STAY_OVER_YRES_ID,
  decode((select yres.YRES_ID from yres where (select wgbs.wgbs_datevalue from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yhrl.YHRL_YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select wgbs.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.wgbs_datevalue from wgbs) and ydet.YDET_YRMS_ID=yhrl.YHRL_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.wgbs_datevalue from wgbs) and ydet.YDET_YRMS_ID=yhrl.YHRL_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.wgbs_datevalue from wgbs) and ydet.YDET_YRMS_ID=yhrl.YHRL_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.wgbs_datevalue from wgbs) and ydet.YDET_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1 and (YRES.YRES_EXPARRTIME)=(select wgbs.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.wgbs_datevalue from wgbs) and ydet.YDET_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1 and (YRES.YRES_EXPARRTIME)=(select wgbs.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.wgbs_datevalue from wgbs) and ydet.YDET_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1 and (YRES.YRES_EXPARRTIME)=(select wgbs.wgbs_datevalue from wgbs)) ARR_YRES_ACTARRTIMENULL,
  decode((select yres.YRES_ID from yres where (select wgbs.wgbs_datevalue from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yhrl.YHRL_YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select wgbs.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.wgbs_datevalue from wgbs) and ydet.YDET_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1),(select yres.YRES_ID from yres where (select wgbs.wgbs_datevalue from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yhrl.YHRL_YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select wgbs.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.wgbs_datevalue from wgbs) and ydet.YDET_YRMS_ID=yhrl.YHRL_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.wgbs_datevalue from wgbs) and ydet.YDET_YRMS_ID=yhrl.YHRL_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.wgbs_datevalue from wgbs) and ydet.YDET_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1)) STAY_DEP_YDET_ADULTNO,
  decode((select yres.YRES_XCMS_ID from yres where (select wgbs.wgbs_datevalue from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yhrl.YHRL_YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select wgbs.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.wgbs_datevalue from wgbs) and ydet.YDET_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1),(select yres.YRES_XCMS_ID from yres where (select wgbs.wgbs_datevalue from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yhrl.YHRL_YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select wgbs.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.wgbs_datevalue from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yhrl.YHRL_YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select wgbs.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.wgbs_datevalue from wgbs) and ydet.YDET_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1),(select yres.YRES_XCMS_ID from yres where (select wgbs.wgbs_datevalue from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yhrl.YHRL_YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select wgbs.wgbs_datevalue from wgbs) and rownum=1)))) STAY_DEP_NAME,
  decode((select yres.YRES_ID from yres where (select wgbs.wgbs_datevalue from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yhrl.YHRL_YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select wgbs.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.wgbs_datevalue from wgbs) and ydet.YDET_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1),(select yres.YRES_EXPARRTIME from yres where (select wgbs.wgbs_datevalue from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yhrl.YHRL_YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select wgbs.wgbs_datevalue from wgbs) and rownum=1)) STAY_DEP_YRES_EXPARRTIME,
  decode((select yres.YRES_ID from yres where (select wgbs.wgbs_datevalue from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yhrl.YHRL_YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select wgbs.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.wgbs_datevalue from wgbs) and ydet.YDET_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1),(select yres.YRES_EXPDEPTIME from yres where (select wgbs.wgbs_datevalue from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yhrl.YHRL_YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select wgbs.wgbs_datevalue from wgbs) and rownum=1)) STAY_DEP_YRES_EXPDEPTIME,
  decode((select yres.YRES_ID from yres where (select wgbs.wgbs_datevalue from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yhrl.YHRL_YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select wgbs.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.wgbs_datevalue from wgbs) and ydet.YDET_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1),(select yres.YRES_FREETEXT1 from yres where (select wgbs.wgbs_datevalue from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yhrl.YHRL_YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select wgbs.wgbs_datevalue from wgbs) and rownum=1)) STAY_DEP_YRES_FREETEXT1,
  decode((select yres.YRES_ID from yres where (select wgbs.wgbs_datevalue from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yhrl.YHRL_YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select wgbs.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.wgbs_datevalue from wgbs) and ydet.YDET_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1),(select yres.YRES_FREETEXT2 from yres where (select wgbs.wgbs_datevalue from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yhrl.YHRL_YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select wgbs.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.wgbs_datevalue from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yhrl.YHRL_YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select wgbs.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.wgbs_datevalue from wgbs) and ydet.YDET_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1),(select yres.YRES_XCMS_ID from yres where (select wgbs.wgbs_datevalue from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yhrl.YHRL_YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select wgbs.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.wgbs_datevalue from wgbs) and ydet.YDET_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1 and (YRES.YRES_EXPARRTIME)=(select wgbs.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.wgbs_datevalue from wgbs) and ydet.YDET_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1 and (YRES.YRES_EXPARRTIME)=(select wgbs.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.wgbs_datevalue from wgbs) and ydet.YDET_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1 and (YRES.YRES_EXPARRTIME)=(select wgbs.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.wgbs_datevalue from wgbs) and ydet.YDET_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1 and (YRES.YRES_EXPARRTIME)=(select wgbs.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.wgbs_datevalue from wgbs) and ydet.YDET_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1 and (YRES.YRES_EXPARRTIME)=(select wgbs.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.wgbs_datevalue from wgbs) and ydet.YDET_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1 and (YRES.YRES_EXPARRTIME)=(select wgbs.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.wgbs_datevalue from wgbs) and ydet.YDET_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1 and (YRES.YRES_EXPARRTIME)=(select wgbs.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.wgbs_datevalue from wgbs) and ydet.YDET_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1 and (YRES.YRES_EXPARRTIME)=(select wgbs.wgbs_datevalue from wgbs))) ARR_VIP,
  (case when (decode((select yres.YRES_ID from yres where (select wgbs.wgbs_datevalue from wgbs) between (YRES.YRES_EXPARRTIME) and (YRES.YRES_EXPDEPTIME) and yres.YRES_YRMS_ID=yhrl.YHRL_YRMS_ID and yres.YRES_RESSTATUS<>3 and trunc(yres.YRES_ACTARRTIME)<>(select wgbs.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.wgbs_datevalue from wgbs) and ydet.YDET_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1 and (YRES.YRES_EXPARRTIME)=(select wgbs.wgbs_datevalue from wgbs)),null,0,1)=1 or decode(decode((select yrst.YRST_ID from yrst where (select wgbs.wgbs_datevalue from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID),NULL,(select yrst.YRST_ID from yrst where ((select wgbs.wgbs_datevalue from wgbs) between yrst.YRST_FROMTIME and yrst.YRST_UNTILTIME-1) and yrst.YRST_YRMS_ID=yhrl.YHRL_YRMS_ID and rownum=1),((select yrst.YRST_ID from yrst where (select wgbs.wgbs_datevalue from wgbs)=yrst.YRST_FROMTIME and yrst.YRST_YRMS_ID=yhrl.YHRL_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.wgbs_datevalue from wgbs) and ydet.YDET_YRMS_ID=yhrl.YHRL_YRMS_ID) NEXT_ARRIVAL  
From
  yhrl
where
    yhrl.YHRL_DATE=(select wgbs.wgbs_datevalue from wgbs); 

CREATE OR REPLACE VIEW V8_IFC_PROFILESEARCH AS
SELECT
  DECODE(XCMS_TYPE,1,XCMS_NAME1||' '||XCMS_NAME2||' '||XCMS_NAME3,TRIM(DECODE(NVL(XCMS_NAME3,''),'','',XCMS_NAME3||' ')||DECODE(NVL(XCMS_NAME2,''),'','',XCMS_NAME2||' ')||XCMS_NAME1)) GUEST_FORMATTEDNAME,
  XCMS.XCMS_NAME1 GUEST_NAME,
  XCMS.XCMS_NAME3 GUEST_FIRSTNAME,
  (select XCID.XCID_LETTGREET from xcid where XCID.XCID_ID=XCMS.XCMS_ID) LETTERGREETING,
  (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=XCMS.XCMS_ID and rownum = 1) GUEST_VIP,
  (SELECT WLAN_SHORTDESC FROM WLAN WHERE XCMS.XCMS_WLAN_ID=WLAN.WLAN_ID) GUEST_LANGUAGE,
  (SELECT XCID_ADDRGREET FROM XCID WHERE XCID.XCID_ID=XCMS.XCMS_ID) GUEST_TITLE,      
  XCMS.XCMS_ID GUEST_XCMS_ID,
  XADR_STREET1 GUEST_ADDRESS1,
  XADR_STREET2 GUEST_ADDRESS2,
  XADR_ZIP GUEST_ZIP,
  XADR_CITY GUEST_CITY,
  XSTA_LONGDESC GUEST_STATE,
  XCOU_LONGDESC GUEST_COUNTRY,
  XCOU_ISO2 GUEST_COUNTRY_ISO,
  (SELECT XCOU.XCOU_ISO2 from XCOU WHERE XCOU.XCOU_ID=XCMS.XCMS_XCOU_ID_NATION) GUEST_NATIONALITY,
  (SELECT XCOM_VALUE FROM XCOM, XCMT WHERE  XCMT.XCMT_ID = XCOM.XCOM_XCMT_ID AND XCOM.XCOM_PRIMARY(+)=1 AND XCMT.XCMT_TYPE(+)=4 AND  XCOM.XCOM_XCMS_ID=XCMS.XCMS_ID AND ROWNUM=1 ) GUEST_PHONE,
  (SELECT XCOM_VALUE FROM XCOM, XCMT WHERE XCMT.XCMT_ID = XCOM.XCOM_XCMT_ID AND   XCOM.XCOM_PRIMARY(+)=1 AND   XCMT.XCMT_TYPE(+)=1 AND  XCOM.XCOM_XCMS_ID=XCMS.XCMS_ID AND ROWNUM=1 ) GUEST_EMAIL,
  (SELECT XCOM_VALUE FROM XCOM, XCMT WHERE XCMT.XCMT_ID = XCOM.XCOM_XCMT_ID AND   XCOM.XCOM_PRIMARY(+)=1 AND   XCMT.XCMT_TYPE(+)=3 AND  XCOM.XCOM_XCMS_ID=XCMS.XCMS_ID AND ROWNUM=1 ) GUEST_FAX,
  (SELECT XCID.XCID_BIRTHTIME FROM XCID WHERE XCID.XCID_ID = XCMS.XCMS_ID) GUEST_BIRTHDAY,
  (SELECT XMNR.XMNR_VALUE FROM XMNR, XMTY WHERE  XMTY.XMTY_ROLE=6 AND XMTY.XMTY_ID=XMNR.XMNR_XMTY_ID AND XMNR.XMNR_XCMS_ID=XCMS.XCMS_ID AND ROWNUM=1) GUEST_MEMBER_ID_6,
  (SELECT DECODE(A.XCMS_TYPE,1,A.XCMS_NAME1||' '||A.XCMS_NAME2||' '||A.XCMS_NAME3,TRIM(DECODE(NVL(A.XCMS_NAME3,''),'','',A.XCMS_NAME3||' ')||DECODE(NVL(A.XCMS_NAME2,''),'','',A.XCMS_NAME2||' ')||A.XCMS_NAME1))  FROM XCMS A, XCIL, XCCA WHERE XCCA.XCCA_ID=A.XCMS_XCCA_ID AND XCIL.XCIL_PRIMARY(+)=1 AND ROWNUM = 1 AND XCCA.XCCA_INTERNALCATEGORY=1 AND XCIL.XCIL_COMPANY_XCMS_ID=A.XCMS_ID AND XCIL.XCIL_INDIVIDUAL_XCMS_ID=XCMS.XCMS_ID) GUEST_COMPANY,
  (SELECT DECODE(NVL(XCID.XCID_SEX,0),0,' ',1,'M',2,'F',3,' ') FROM XCID WHERE XCID.XCID_ID=XCMS.XCMS_ID) GUEST_GENDER,
  (SELECT 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=1 AND XPDC.XPDC_PRIMARY=1 AND ROWNUM=1) GUEST_PASSPORT
FROM XADR,XSTA,XCOU, XCMS
  WHERE XADR_PRIMARY(+)=1 AND
  XCMS.XCMS_ID=XADR.XADR_XCMS_ID(+) AND
  XADR_XSTA_ID=XSTA_ID(+) AND
  XADR_XCOU_ID=XCOU_ID(+);

  