--UPDATE FROM 8.10.1.10 TO 8.10.1.11

CREATE OR REPLACE VIEW V8_WEBAPI_ROOMACTUALS_old
AS 
select
YRMS.YRMS_ID
,YRMS.YRMS_WALKORDER SORT_ORDER
,YRMS.YRMS_SHORTDESC
,YRMS.YRMS_FLOOR
,YCAT.YCAT_SHORTDESC
,YRMS.YRMS_NOOFPERSONS
,YHKS.YHKS_SHORTDESC
,YHKS.YHKS_CLEAN
,YRMS.YRMS_DEFAULTCREDITS
,YRMS.YRMS_DEPARTURECREDITS
,YRMS.YRMS_VALIDFROM
,YRMS.YRMS_VALIDUNTIL
,YCAT.YCAT_LONGDESC
,YRMS.YRMS_LONGDESC
,YHKS.YHKS_LONGDESC
,ARES.ARESID YRES_ID
,YRMS.YRMS_GUESTSERVICE_STATUS GUESTSTATUS
,TO_CHAR(YRMS.YRMS_HSK_OCCUPIED) HOUSEKEEPINGSTATUS
,DECODE(DECODE(ARES.ACALCSTATUS,3,1,4,1,8,1,0),1,1,DECODE(NRES.NCALCSTATUS,2,1,8,1,0)) FRONTOFFICESTATUS
,ARES.AGUESTNAME GUESTNAME
,ARES.ACMSID GUESTID
,NRES.NGUESTNAME NEXTGUESTNAME
,NRES.NCMSID NEXTGUESTID
,NRES.NRESID NEXTYRESID
,(SELECT yhac_comment FROM yhrl, yhac, fideliodate WHERE yhrl_date=wgbs_datevalue and yhrl_yrms_id=yrms.yrms_id and yhrl_yhac_id=yhac_id) MaidNotes
,(case when exists (select 1 from xpal where xpal_xpac_id=11 and xpal_xcms_id=DECODE(ARES.ACMSID,null,NRES.NCMSID)) then 1 else 0 end) IS_VIP
--,(case when exists (select 1 from yque where yque_yres_id=NRES.NRESID) then 1 else 0 end) IN_QUEUE
,(select YQUE_Q_DATE from yque where yque_yres_id=NRES.NRESID) QUEUE_date
,(select yrsc_id from yrst, yrsc, fideliodate f where  yrst_yrms_id=yrms.yrms_id and yrst_fromtime<=f.wgbs_datevalue and yrst_untiltime>f.wgbs_datevalue and yrst_yrsc_id=yrsc_id and rownum=1) SERVICESTATUS
,0 RESCOUNT
,ARES.AEXPARRTIME ARRIVAL
,ARES.AEXPDEPTIME DEPARTURE
,NRES.NEXPARRHOUR EXPARRHOURNEXTGUESTTODAY
,ARES.AEXPDEPHOUR EXPDEPHOURCURRENTGUEST
,DECODE(NRES.NRESID,null,0,1) IS_ARRIVAL
,NVL(DECODE(ARES.ACALCSTATUS,4,1,5.1,0),0) IS_DEPARTURE
,ARES.AADULTNO ADULTS
,ARES.AKIDS CHILDCOUNT
,(case when exists (select 1 from ydet,yres where nvl(yres.yres_current_ydet_id,yres.yres_last_ydet_id)=ydet.ydet_id and ydet.ydet_yrms_id=yrms.yrms_id and yres.yres_calcstatus=3) then 1 else 0 end) IS_STAYOVER
,(case when exists (select 1 from ydet,yres where nvl(yres_current_ydet_id,yres_last_ydet_id)=ydet_id and ydet_yrms_id=yrms.yrms_id and yres_calcstatus in (15,8,7)) then 1 else 0 end) IS_DAYUSE
,(select count(yamn_id) Linen  from yhra,yamn, yhrl, fideliodate where yhra_yamn_id=yamn_id and (yamn_role=1) and yhra_yhrl_id=yhrl_id and yhrl_yrms_id=yrms.yrms_id and yhrl_date=wgbs_datevalue) LINEN
,(select count(yamn_id) Towel from yhra,yamn, yhrl, fideliodate where yhra_yamn_id=yamn_id and (yamn_role=2) and yhra_yhrl_id=yhrl_id and yhrl_yrms_id=yrms.yrms_id and yhrl_date=wgbs_datevalue) TOWEL
,(SELECT count(wact_id) FROM wact, waty WHERE  wact_waty_id = waty_id AND WACT_COMPLTIME IS NULL  AND wact_place_type=1 and wact_place_id=YRMS.YRMS_id ) MAINTENANCE_CNT
,(select nvl(yhat_name,'')  from yhrl, yhat, yhhc where  yhrl_yhat_id=yhat_id and yhat_yhhc_id=yhhc_id and yhrl_date=(select wgbs_datevalue from fideliodate)  and yhrl_yrms_id=yrms.yrms_id and yhhc_role=0) Maid_Name 
,(select nvl(yhat_name,'')  from yhrl, yhat, yhhc where  yhrl_yhat_id=yhat_id and yhat_yhhc_id=yhhc_id and yhrl_date=(select wgbs_datevalue from fideliodate)  and yhrl_yrms_id=yrms.yrms_id and yhhc_role=1) Manager_Name 
, (select yhat_xced_id  from yhrl, yhat, yhhc where  yhrl_yhat_id=yhat_id and yhat_yhhc_id=yhhc_id and yhrl_date=(select wgbs_datevalue from fideliodate)  and yhrl_yrms_id=yrms.yrms_id and yhhc_role=0) Maid_EmployeeId 
, (select yhat_xced_id  from yhrl, yhat, yhhc where  yhrl_yhat_id=yhat_id and yhat_yhhc_id=yhhc_id and yhrl_date=(select wgbs_datevalue from fideliodate)  and yhrl_yrms_id=yrms.yrms_id and yhhc_role=1) Manager_EmployeeId
, (case when exists (select 1 from xcnc, xcno where xcnc_tablelinkedto=0 and xcnc_id=xcno_xcnc_id and xcno_tablelinkedto='XCMS' and xcno_table_id=ARES.ACMSID) then 1 else 0 end)   guestNotesExists 
from
yrms, ycat, yhks,
(--Current Guest
--no sharer
select 
RA.YRES_ID ARESID,
DA.YDET_ADULTNO AADULTNO,
YRMS.YRMS_ID ARMSID,
RA.YRES_CALCSTATUS ACALCSTATUS,
RA.YRES_EXPARRTIME AEXPARRTIME,
RA.YRES_ACTARRTIME AACTARRTIME,
RA.YRES_EXPDEPTIME AEXPDEPTIME,
RA.YRES_ACTDEPTIME AACTDEPTIME,
RA.YRES_EXPARRHOUR AEXPARRHOUR,
RA.YRES_EXPDEPHOUR AEXPDEPHOUR,
CA.XCMS_ID ACMSID,
DECODE (CA.xcms_type, 1, CA.xcms_name1 || ' ' || CA.xcms_name2 || ' ' || CA.xcms_name3,  TRIM( DECODE (NVL(CA.xcms_name1, ''), '', '',CA. xcms_name1 || DECODE (NVL( CA.xcms_name3, ''), '', '', ',') || ' ')  || DECODE (NVL (CA.xcms_name3, ''), '', '',CA.xcms_name3  || ' ') || CA.xcms_name2))  AGUESTNAME,
nvl((select sum(YRGL.YRGL_NUMBER) from yrgl where YRGL.YRGL_YDET_ID=DA.YDET_ID),0) AKIDS
from 
ydet DA,yres RA,yrms, xcms CA
where
    DA.ydet_id=nvl(RA.yres_current_ydet_id,RA.yres_last_ydet_id) 
and RA.yres_calcstatus in (3,4,8,2)
and (   (ydet_date<>(select * from fideliodate) and DA.ydet_yrms_id=yrms_id)
     or (ydet_date=(select * from fideliodate) and RA.yres_yrms_id=yrms_id))
and RA.yres_hasshare=0
and RA.YRES_XCMS_ID=CA.XCMS_ID
union all
-- share resevation, master share
select 
RA.YRES_ID ARESID,
DA.YDET_ADULTNO AADULTNO,
YRMS.YRMS_ID ARMSID,
RA.YRES_CALCSTATUS ACALCSTATUS,
RA.YRES_EXPARRTIME AEXPARRTIME,
RA.YRES_ACTARRTIME AACTARRTIME,
RA.YRES_EXPDEPTIME AEXPDEPTIME,
RA.YRES_ACTDEPTIME AACTDEPTIME,
RA.YRES_EXPARRHOUR AEXPARRHOUR,
RA.YRES_EXPDEPHOUR AEXPDEPHOUR,
CA.XCMS_ID ACMSID,
DECODE (CA.xcms_type, 1, CA.xcms_name1 || ' ' || CA.xcms_name2 || ' ' || CA.xcms_name3,  TRIM( DECODE (NVL(CA.xcms_name1, ''), '', '',CA. xcms_name1 || DECODE (NVL( CA.xcms_name3, ''), '', '', ',') || ' ')  || DECODE (NVL (CA.xcms_name3, ''), '', '',CA.xcms_name3  || ' ') || CA.xcms_name2))  AGUESTNAME,
nvl((select sum(YRGL.YRGL_NUMBER) from yrgl where YRGL.YRGL_YDET_ID=DA.YDET_ID),0) AKIDS
from
ydet DA,yres RA,yrms, xcms CA
where
    DA.ydet_id=nvl(RA.yres_current_ydet_id,RA.yres_last_ydet_id) 
and RA.yres_calcstatus in (3,4,8,2)
and (   (ydet_date<>(select * from fideliodate) and DA.ydet_yrms_id=yrms_id)
     or (ydet_date=(select * from fideliodate) and RA.yres_yrms_id=yrms_id))
and RA.yres_hasshare=1
and RA.yres_sharetype=1
and RA.YRES_XCMS_ID=CA.XCMS_ID
union all
--share Resevation no master, so max values are taken
select
RA.YRES_ID ARESID,
SR.YDET_ADULTNO_S AADULTNO,
SR.YRMS_ID_S ARMSID,
RA.YRES_CALCSTATUS ACALCSTATUS,
RA.YRES_EXPARRTIME AEXPARRTIME,
RA.YRES_ACTARRTIME AACTARRTIME,
RA.YRES_EXPDEPTIME AEXPDEPTIME,
RA.YRES_ACTDEPTIME AACTDEPTIME,
RA.YRES_EXPARRHOUR AEXPARRHOUR,
RA.YRES_EXPDEPHOUR AEXPDEPHOUR,
CA.XCMS_ID ACMSID,
DECODE (CA.xcms_type, 1, CA.xcms_name1 || ' ' || CA.xcms_name2 || ' ' || CA.xcms_name3,  TRIM( DECODE (NVL(CA.xcms_name1, ''), '', '',CA. xcms_name1 || DECODE (NVL( CA.xcms_name3, ''), '', '', ',') || ' ')  || DECODE (NVL (CA.xcms_name3, ''), '', '',CA.xcms_name3  || ' ') || CA.xcms_name2))  AGUESTNAME,
SR.KIDS_S AKIDS
from
(
 select 
 max(RAS.yres_id) YRES_ID_S,
 sum(DA.ydet_adultno) YDET_ADULTNO_S,
 yrms_id YRMS_ID_S,
 sum(nvl((select sum(YRGL.YRGL_NUMBER) from yrgl where YRGL.YRGL_YDET_ID=DA.YDET_ID),0)) KIDS_S
 from
 ydet DA,yres RAS,yrms
 where
     Da.ydet_id=nvl(RAS.yres_current_ydet_id,RAS.yres_last_ydet_id) 
 and RAS.yres_calcstatus in (3,4,8,2)
 and (   (ydet_date<>(select * from fideliodate) and DA.ydet_yrms_id=yrms_id)
      or (ydet_date=(select * from fideliodate) and RAS.yres_yrms_id=yrms_id))
 and RAS.yres_hasshare=1
 and RAS.yres_sharetype=0
 and DA.ydet_yrms_id is not null
 group by yrms_id
) SR ,yres RA, xcms CA
where SR.YRES_ID_S=RA.YRES_ID
and RA.YRES_XCMS_ID=CA.XCMS_ID
) ARES,
(--Next Res:
--no sharer
select 
RA.YRES_ID NRESID,
DA.YDET_ADULTNO NADULTNO,
YRMS.YRMS_ID NRMSID,
RA.YRES_CALCSTATUS NCALCSTATUS,
RA.YRES_EXPARRTIME NEXPARRTIME,
RA.YRES_ACTARRTIME NACTARRTIME,
RA.YRES_EXPDEPTIME NEXPDEPTIME,
RA.YRES_ACTDEPTIME NACTDEPTIME,
RA.YRES_EXPARRHOUR NEXPARRHOUR,
RA.YRES_EXPDEPHOUR NEXPDEPHOUR,
CA.XCMS_ID NCMSID,
DECODE (CA.xcms_type, 1, CA.xcms_name1 || ' ' || CA.xcms_name2 || ' ' || CA.xcms_name3,  TRIM( DECODE (NVL(CA.xcms_name1, ''), '', '',CA. xcms_name1 || DECODE (NVL( CA.xcms_name3, ''), '', '', ',') || ' ')  || DECODE (NVL (CA.xcms_name3, ''), '', '',CA.xcms_name3  || ' ') || CA.xcms_name2))  NGUESTNAME,
nvl((select sum(YRGL.YRGL_NUMBER) from yrgl where YRGL.YRGL_YDET_ID=DA.YDET_ID),0) NKIDS
 from
ydet DA,yres RA,yrms, xcms CA
where
    DA.ydet_id=nvl(RA.yres_current_ydet_id,RA.yres_last_ydet_id) 
and (   RA.yres_calcstatus in (1)
     or (RA.yres_calcstatus in (3) and ydet_yrms_id<>yres_yrms_id)) 
and DA.ydet_yrms_id=yrms_id
and RA.yres_hasshare=0
and RA.YRES_XCMS_ID=CA.XCMS_ID
union all
-- share resevation, master share
select 
RA.YRES_ID NRESID,
DA.YDET_ADULTNO NADULTNO,
YRMS.YRMS_ID NRMSID,
RA.YRES_CALCSTATUS NCALCSTATUS,
RA.YRES_EXPARRTIME NEXPARRTIME,
RA.YRES_ACTARRTIME NACTARRTIME,
RA.YRES_EXPDEPTIME NEXPDEPTIME,
RA.YRES_ACTDEPTIME NACTDEPTIME,
RA.YRES_EXPARRHOUR NEXPARRHOUR,
RA.YRES_EXPDEPHOUR NEXPDEPHOUR,
CA.XCMS_ID NCMSID,
DECODE (CA.xcms_type, 1, CA.xcms_name1 || ' ' || CA.xcms_name2 || ' ' || CA.xcms_name3,  TRIM( DECODE (NVL(CA.xcms_name1, ''), '', '',CA. xcms_name1 || DECODE (NVL( CA.xcms_name3, ''), '', '', ',') || ' ')  || DECODE (NVL (CA.xcms_name3, ''), '', '',CA.xcms_name3  || ' ') || CA.xcms_name2))  NGUESTNAME,
nvl((select sum(YRGL.YRGL_NUMBER) from yrgl where YRGL.YRGL_YDET_ID=DA.YDET_ID),0) NKIDS
from
ydet DA,yres RA,yrms, xcms CA
where
    DA.ydet_id=nvl(RA.yres_current_ydet_id,RA.yres_last_ydet_id) 
and (   RA.yres_calcstatus in (1)
     or (RA.yres_calcstatus in (3) and ydet_yrms_id<>yres_yrms_id)) 
and DA.ydet_yrms_id=yrms_id
and RA.yres_hasshare=1
and RA.yres_sharetype=1
and RA.YRES_XCMS_ID=CA.XCMS_ID
union all
--share Resevation no master, so max values are taken
select
RA.YRES_ID NRESID,
SR.YDET_ADULTNO_S NADULTNO,
SR.YRMS_ID_S NRMSID,
RA.YRES_CALCSTATUS NCALCSTATUS,
RA.YRES_EXPARRTIME NEXPARRTIME,
RA.YRES_ACTARRTIME NACTARRTIME,
RA.YRES_EXPDEPTIME NEXPDEPTIME,
RA.YRES_ACTDEPTIME NACTDEPTIME,
RA.YRES_EXPARRHOUR NEXPARRHOUR,
RA.YRES_EXPDEPHOUR NEXPDEPHOUR,
CA.XCMS_ID NCMSID,
DECODE (CA.xcms_type, 1, CA.xcms_name1 || ' ' || CA.xcms_name2 || ' ' || CA.xcms_name3,  TRIM( DECODE (NVL(CA.xcms_name1, ''), '', '',CA. xcms_name1 || DECODE (NVL( CA.xcms_name3, ''), '', '', ',') || ' ')  || DECODE (NVL (CA.xcms_name3, ''), '', '',CA.xcms_name3  || ' ') || CA.xcms_name2)) NGUESTNAME,
SR.KIDS_S NKIDS
from
(
 select 
 max(RAS.yres_id) YRES_ID_S,
 sum(DA.ydet_adultno) YDET_ADULTNO_S,
 DA.ydet_yrms_id YRMS_ID_S,
 sum(nvl((select sum(YRGL.YRGL_NUMBER) from yrgl where YRGL.YRGL_YDET_ID=DA.YDET_ID),0)) KIDS_S
 from
 ydet DA,yres RAS
 where
 Da.ydet_id=nvl(RAS.yres_current_ydet_id,RAS.yres_last_ydet_id) 
 and (   RAS.yres_calcstatus in (1)
      or (RAS.yres_calcstatus in (3) and ydet_yrms_id<>yres_yrms_id)) 
 and RAS.yres_hasshare=1
 and RAS.yres_sharetype=0
 and DA.ydet_yrms_id is not null
 group by DA.ydet_yrms_id) SR 
 ,yres RA, xcms CA
 where SR.YRES_ID_S=RA.YRES_ID
 and RA.YRES_XCMS_ID=CA.XCMS_ID
) NRES
where
YRMS.YRMS_YCAT_ID=YCAT.YCAT_ID
and YRMS.YRMS_YHKS_ID=YHKS.YHKS_ID
and YRMS.YRMS_ID=ARES.ARMSID(+)
and YRMS.YRMS_ID=NRES.NRMSID(+)
and V8_sys_fideliodate between YRMS.YRMS_VALIDFROM and YRMS.yrms_validuntil
order by YRMS.YRMS_WALKORDER;


--end old///////////////////////////////////////////////

create or replace view v8_webapi_RoomActuals_baseAct 
as
select 
 yres_id
,ydet_adultno
,yrms_id
,yres_calcstatus
,yres_exparrtime
,yres_actarrtime
,yres_expdeptime
,yres_actdeptime
,yres_exparrhour
,yres_expdephour
,xcms_id
,decode (xcms_type, 1, xcms_name1 || ' ' || xcms_name2 || ' ' || xcms_name3,  trim( decode (nvl(xcms_name1, ''), '', '',xcms_name1 || decode (nvl( xcms_name3, ''), '', '', ',') || ' ')  || decode (nvl (xcms_name3, ''), '', '',xcms_name3  || ' ') || xcms_name2)) guestname
,nvl((select sum(yrgl_number) from yrgl where yrgl_ydet_id=ydet_id),0) kids
,yres_hasshare
,yres_sharetype
from ydet,yres,yrms,xcms
where
    ydet_id=yres_current_ydet_id
and yres_calcstatus in (3,4,8,2)
and yrms_id=nvl(yres_yrms_id,ydet_yrms_id)
and yres_xcms_id=xcms_id;

create or replace view v8_webapi_RoomActuals_baseNext 
as
select 
 yres_id
,ydet_adultno
,yrms_id
,yres_calcstatus
,yres_exparrtime
,yres_actarrtime
,yres_expdeptime
,yres_actdeptime
,yres_exparrhour
,yres_expdephour
,xcms_id
,decode (xcms_type, 1, xcms_name1 || ' ' || xcms_name2 || ' ' || xcms_name3,  trim( decode (nvl(xcms_name1, ''), '', '',xcms_name1 || decode (nvl( xcms_name3, ''), '', '', ',') || ' ')  || decode (nvl (xcms_name3, ''), '', '',xcms_name3  || ' ') || xcms_name2)) guestname
,nvl((select sum(yrgl_number) from yrgl where yrgl_ydet_id=ydet_id),0) kids
,yres_hasshare
,yres_sharetype
from ydet,yres,yrms,xcms
where
    ydet_id=yres_current_ydet_id
and (   yres_calcstatus in ( 1, 7, 15)
     or (yres_calcstatus in (3) and ydet_yrms_id<>yres_yrms_id) --room move
    ) 
and yrms_id=ydet_yrms_id
and yres_xcms_id=xcms_id;

create or replace view v8_webapi_RoomActuals_basePrev 
as
select 
 yres_id
,ydet_adultno
,yrms_id
,yres_calcstatus
,yres_exparrtime
,yres_actarrtime
,yres_expdeptime
,yres_actdeptime
,yres_exparrhour
,yres_expdephour
,xcms_id
,decode (xcms_type, 1, xcms_name1 || ' ' || xcms_name2 || ' ' || xcms_name3,  trim( decode (nvl(xcms_name1, ''), '', '',xcms_name1 || decode (nvl( xcms_name3, ''), '', '', ',') || ' ')  || decode (nvl (xcms_name3, ''), '', '',xcms_name3  || ' ') || xcms_name2)) guestname
,nvl((select sum(yrgl_number) from yrgl where yrgl_ydet_id=ydet_id),0) kids
,yres_hasshare
,yres_sharetype
from ydet,yres,yrms,xcms
where
    ydet_id=yres_current_ydet_id
and trunc(yres_ActDepTime)=(select * from fideliodate)
and yres_calcstatus not in (12, 13) 
and yrms_id=ydet_yrms_id
and yres_xcms_id=xcms_id;

create or replace view v8_webapi_RoomActuals_baseActI 
as
select
 yres.yres_id
,adultno 
,yrms_id
,yres_calcstatus
,yres_exparrtime
,yres_actarrtime
,yres_expdeptime
,yres_actdeptime
,yres_exparrhour
,yres_expdephour
,xcms_id
,decode (xcms_type, 1, xcms_name1 || ' ' || xcms_name2 || ' ' || xcms_name3,  trim( decode (nvl(xcms_name1, ''), '', '',xcms_name1 || decode (nvl( xcms_name3, ''), '', '', ',') || ' ')  || decode (nvl (xcms_name3, ''), '', '',xcms_name3  || ' ') || xcms_name2)) guestname
,kids
,yres_hasshare
,yres_sharetype
from
(select
   max(yres_id) yres_id
  ,sum(ydet_adultno) adultno
  ,yrms_id
  ,sum(kids) kids
 from v8_webapi_RoomActuals_baseAct
 where
     yres_hasshare=1
 and yres_sharetype=0
 group by yrms_id
) SR ,yres, xcms
where SR.YRES_ID=yres.YRES_ID
and YRES_XCMS_ID=XCMS_ID;
  
create or replace view v8_webapi_RoomActuals_baseNexI 
as
select
 yres.yres_id
,adultno 
,yrms_id
,yres_calcstatus
,yres_exparrtime
,yres_actarrtime
,yres_expdeptime
,yres_actdeptime
,yres_exparrhour
,yres_expdephour
,xcms_id
,decode (xcms_type, 1, xcms_name1 || ' ' || xcms_name2 || ' ' || xcms_name3,  trim( decode (nvl(xcms_name1, ''), '', '',xcms_name1 || decode (nvl( xcms_name3, ''), '', '', ',') || ' ')  || decode (nvl (xcms_name3, ''), '', '',xcms_name3  || ' ') || xcms_name2)) guestname
,kids
,yres_hasshare
,yres_sharetype
from
(select
   max(yres_id) yres_id
  ,sum(ydet_adultno) adultno
  ,yrms_id
  ,sum(kids) kids
 from v8_webapi_RoomActuals_baseNext
 where
     yres_hasshare=1
 and yres_sharetype=0
 group by yrms_id
) SR ,yres, xcms
where SR.YRES_ID=yres.YRES_ID
and YRES_XCMS_ID=XCMS_ID;

create or replace view v8_webapi_RoomActuals_basePreI 
as
select
 yres.yres_id
,adultno 
,yrms_id
,yres_calcstatus
,yres_exparrtime
,yres_actarrtime
,yres_expdeptime
,yres_actdeptime
,yres_exparrhour
,yres_expdephour
,xcms_id
,decode (xcms_type, 1, xcms_name1 || ' ' || xcms_name2 || ' ' || xcms_name3,  trim( decode (nvl(xcms_name1, ''), '', '',xcms_name1 || decode (nvl( xcms_name3, ''), '', '', ',') || ' ')  || decode (nvl (xcms_name3, ''), '', '',xcms_name3  || ' ') || xcms_name2)) guestname
,kids
,yres_hasshare
,yres_sharetype
from
(select
   max(yres_id) yres_id
  ,sum(ydet_adultno) adultno
  ,yrms_id
  ,sum(kids) kids
 from v8_webapi_RoomActuals_basePrev
 --where 
 --    yres_hasshare=1 checked out reservations are not shared any more (they check out one by one and share is broken at that time)
 --and yres_sharetype=0
 group by yrms_id
) SR ,yres, xcms
where SR.YRES_ID=yres.YRES_ID
and YRES_XCMS_ID=XCMS_ID;

CREATE OR REPLACE VIEW V8_WEBAPI_ROOMACTUALS
AS 
select
 YRMS.YRMS_ID
,YRMS.YRMS_WALKORDER SORT_ORDER
,YRMS.YRMS_SHORTDESC
,YRMS.YRMS_FLOOR
,YCAT.YCAT_SHORTDESC
,YRMS.YRMS_NOOFPERSONS
,YHKS.YHKS_SHORTDESC
,YHKS.YHKS_CLEAN
,YRMS.YRMS_DEFAULTCREDITS
,YRMS.YRMS_DEPARTURECREDITS
,YRMS.YRMS_VALIDFROM
,YRMS.YRMS_VALIDUNTIL
,YCAT.YCAT_LONGDESC
,YRMS.YRMS_LONGDESC
,YHKS.YHKS_LONGDESC
,ARES.yres_id YRES_ID
,YRMS.YRMS_GUESTSERVICE_STATUS GUESTSTATUS
,TO_CHAR(YRMS.YRMS_HSK_OCCUPIED) HOUSEKEEPINGSTATUS
,DECODE(DECODE(ARES.yres_CALCSTATUS,3,1,4,1,8,1,0),1,1,DECODE(NRES.yres_CALCSTATUS,2,1,8,1,0)) FRONTOFFICESTATUS
,ARES.GUESTNAME GUESTNAME
,ARES.xcms_id GUESTID
,NRES.GUESTNAME NEXTGUESTNAME
,NRES.xcms_id NEXTGUESTID
,NRES.yres_id NEXTYRESID
,(SELECT yhac_comment FROM yhrl, yhac, fideliodate WHERE yhrl_date=wgbs_datevalue and yhrl_yrms_id=yrms.yrms_id and yhrl_yhac_id=yhac_id) MaidNotes
,(case when exists (select 1 from xpal where xpal_xpac_id=11 and xpal_xcms_id=DECODE(ARES.xcms_id,null,NRES.xcms_id)) then 1 else 0 end) IS_VIP
--,(case when exists (select 1 from yque where yque_yres_id=NRES.NRESID) then 1 else 0 end) IN_QUEUE
,(select YQUE_Q_DATE from yque where yque_yres_id=NRES.yres_id) QUEUE_date
,(select yrsc_id from yrst, yrsc, fideliodate f where  yrst_yrms_id=yrms.yrms_id and yrst_fromtime<=f.wgbs_datevalue and yrst_untiltime>f.wgbs_datevalue and yrst_yrsc_id=yrsc_id and rownum=1) SERVICESTATUS
,0 RESCOUNT
,ARES.yres_EXPARRTIME ARRIVAL
,ARES.yres_EXPDEPTIME DEPARTURE
,NRES.yres_EXPARRHOUR EXPARRHOURNEXTGUESTTODAY
,ARES.yres_EXPDEPHOUR EXPDEPHOURCURRENTGUEST
,DECODE(NRES.yres_id,null,0,1) IS_ARRIVAL
,DECODE(ARES.yres_CALCSTATUS,4,1,0) IS_DEPARTURE  -- expected departure today
,DECODE(ARES.yres_CALCSTATUS,2,1,8,1,0) IS_ARRIVED -- checked in today, day use inhouse
,DECODE(PRES.yres_calcstatus,5,1,16,1,0) IS_DEPARTED --departed today, day use departed today
,ARES.ydet_adultno ADULTS
,ARES.KIDS CHILDCOUNT
,(case when exists (select 1 from ydet,yres where nvl(yres.yres_current_ydet_id,yres.yres_last_ydet_id)=ydet.ydet_id and ydet.ydet_yrms_id=yrms.yrms_id and yres.yres_calcstatus=3) then 1 else 0 end) IS_STAYOVER
,(case when exists (select 1 from ydet,yres where nvl(yres_current_ydet_id,yres_last_ydet_id)=ydet_id and ydet_yrms_id=yrms.yrms_id and yres_calcstatus in (15,16, 8,7)) then 1 else 0 end) IS_DAYUSE
,(select count(yamn_id) Linen  from yhra,yamn, yhrl, fideliodate where yhra_yamn_id=yamn_id and (yamn_role=1) and yhra_yhrl_id=yhrl_id and yhrl_yrms_id=yrms.yrms_id and yhrl_date=wgbs_datevalue) LINEN
,(select count(yamn_id) Towel from yhra,yamn, yhrl, fideliodate where yhra_yamn_id=yamn_id and (yamn_role=2) and yhra_yhrl_id=yhrl_id and yhrl_yrms_id=yrms.yrms_id and yhrl_date=wgbs_datevalue) TOWEL
,(SELECT count(wact_id) FROM wact, waty WHERE  wact_waty_id = waty_id AND WACT_COMPLTIME IS NULL  AND wact_place_type=1 and wact_place_id=YRMS.YRMS_id ) MAINTENANCE_CNT
,(select nvl(yhat_name,'')  from yhrl, yhat, yhhc where  yhrl_yhat_id=yhat_id and yhat_yhhc_id=yhhc_id and yhrl_date=(select wgbs_datevalue from fideliodate)  and yhrl_yrms_id=yrms.yrms_id and yhhc_role=0) Maid_Name 
,(select nvl(yhat_name,'')  from yhrl, yhat, yhhc where  yhrl_yhat_id=yhat_id and yhat_yhhc_id=yhhc_id and yhrl_date=(select wgbs_datevalue from fideliodate)  and yhrl_yrms_id=yrms.yrms_id and yhhc_role=1) Manager_Name 
, (select yhat_xced_id  from yhrl, yhat, yhhc where  yhrl_yhat_id=yhat_id and yhat_yhhc_id=yhhc_id and yhrl_date=(select wgbs_datevalue from fideliodate)  and yhrl_yrms_id=yrms.yrms_id and yhhc_role=0) Maid_EmployeeId 
, (select yhat_xced_id  from yhrl, yhat, yhhc where  yhrl_yhat_id=yhat_id and yhat_yhhc_id=yhhc_id and yhrl_date=(select wgbs_datevalue from fideliodate)  and yhrl_yrms_id=yrms.yrms_id and yhhc_role=1) Manager_EmployeeId
, (case when exists (select 1 from xcnc, xcno where xcnc_tablelinkedto=0 and xcnc_id=xcno_xcnc_id and xcno_tablelinkedto='XCMS' and xcno_table_id=ARES.xcms_id) then 1 else 0 end) guestNotesExists 
,PRES.yres_id PrevYRESID
,PRES.GUESTNAME PrevGUESTNAME
,PRES.xcms_id PrevGUESTID
,PRES.yres_EXPARRTIME PrevARRIVAL
,PRES.yres_EXPDEPTIME PrevDEPARTURE
,PRES.adultno PrevADULTS
,PRES.KIDS PrevCHILDCOUNT
,PRES.yres_EXPDEPHOUR EXPDEPHOURPREVGUEST,
YHKS_id,
ycat_id
from yrms, ycat, yhks
,(select * from v8_webapi_RoomActuals_baseAct where yres_hasshare=0
  union all
  select * from v8_webapi_RoomActuals_baseAct where yres_hasshare=1 and yres_sharetype=1
  union all
  select * from v8_webapi_RoomActuals_baseActI
 ) ARES --Current Guest currentyly checked in
,(select * from v8_webapi_RoomActuals_baseNext where yres_hasshare=0
  union all
  select * from v8_webapi_RoomActuals_baseNext where yres_hasshare=1 and yres_sharetype=1
  union all
  select * from v8_webapi_RoomActuals_baseNexI 
 ) NRES --Next Guest not checked in yet
,(--select * from v8_webapi_RoomActuals_basePrev where yres_hasshare=0
  --union all
  --select * from v8_webapi_RoomActuals_basePrev where yres_hasshare=1 and yres_sharetype=1
  --union all
  select * from v8_webapi_RoomActuals_basePreI 
 ) PRES --Previous Guest checked out today
where
    YRMS_YCAT_ID=YCAT_ID
and YRMS_YHKS_ID=YHKS_ID
and YRMS.YRMS_ID=ARES.yrms_id(+)
and YRMS.YRMS_ID=NRES.yrms_id(+)
and YRMS.YRMS_ID=PRES.yrms_id(+)
and V8_sys_fideliodate between YRMS_VALIDFROM and yrms_validuntil
order by YRMS_WALKORDER;  
