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

create or replace function GetTranslatedString(strtotranslate in varchar2, wlanid in number)
return varchar2
as
begin
  return strtotranslate;
end;
/

create or replace function TableToDescription(tablename in varchar2, wlanid in integer)
return varchar2
as
description varchar2(2000);
begin
  description := tablename;
  select 
    case tablename 
      when 'YRES' then GetTranslatedString('Reservation', wlanid)
      when 'YRMS' then GetTranslatedString('Room Status', wlanid)
      when 'YBLH' then GetTranslatedString('Blocks', wlanid)
      when 'YBLD' then GetTranslatedString('Blocks', wlanid)
      when 'YBOM' then GetTranslatedString('Booking Master', wlanid)
      when 'YPKD' then GetTranslatedString('Conference Packages', wlanid)
      when 'ZFAC' then GetTranslatedString('Financial Accounts', wlanid)
      when 'YBIS' then GetTranslatedString('Billing Instructions', wlanid)
      when 'ZPOS' then GetTranslatedString('Postings', wlanid)
      when 'IRMS' then GetTranslatedString('Interface', wlanid)
      when 'NA' then GetTranslatedString('Night Audit', wlanid)
      when 'YEVM' then GetTranslatedString('Booking Events', wlanid)
      when 'YEBS' then GetTranslatedString('Resources - Function Spaces', wlanid)
      when 'YEBR' then GetTranslatedString('Resources - Catering Items', wlanid)
      when 'YEMN' then GetTranslatedString('Resources - Standard Menus '||chr(38)||' Menu Items', wlanid)
      when 'YEMI' then GetTranslatedString('Resources - Standard Menus '||chr(38)||' Menu Items', wlanid)
      when 'WACT_ACT' then GetTranslatedString('Activities', wlanid)
      when 'WACT_TASK' then GetTranslatedString('Tasks', wlanid)
      when 'WLIC' then GetTranslatedString('License', wlanid)
      when 'YOVB' then GetTranslatedString('Overbooking', wlanid)
      when 'XCMS' then GetTranslatedString('Profile', wlanid)
      when 'WACT_MAIN' then GetTranslatedString('Hotel Maintenance', wlanid) --detailed with Guest Service
      when 'WACT_MAIN' then GetTranslatedString('Hotel Maintenance', wlanid) --detailed with Guest Service
      when 'RRES' then GetTranslatedString('Table Reservation', wlanid) --should detail with Resource
      when 'XCCS' then GetTranslatedString('Credit card', wlanid)
      when 'YRST' then GetTranslatedString('Room Status (OOO/OOS)', wlanid)
      when 'SEC' then GetTranslatedString('Security', wlanid)
      when 'MATERIALCONTROL' then GetTranslatedString('Material Control', wlanid)
      when 'v8' then GetTranslatedString('Application and users', wlanid)
      when 'COUP' then GetTranslatedString('Voucher Management', wlanid)
      when 'warn' then GetTranslatedString('Warning', wlanid)
      when 'ZXCH' then GetTranslatedString('Exchange Rate', wlanid)
      when 'WUSR' then GetTranslatedString('User Rights Settings', wlanid)
      when 'WLCK' then GetTranslatedString('Lock', wlanid)
      when 'OP' then GetTranslatedString('Online Payments', wlanid)
      when 'TI' then GetTranslatedString('Travel Insurance', wlanid)
      when 'ZBDP' then GetTranslatedString('Bank Debit Payments', wlanid)
      when 'WUSS' then GetTranslatedString('Global Settings', wlanid)
      when 'YRCH' then GetTranslatedString('Rate Code Details', wlanid)
      when 'XCED' then GetTranslatedString('User Details', wlanid)
      when 'YSLM' then GetTranslatedString('Selling Limit', wlanid)
      when 'YCEV' then GetTranslatedString('Calendar Events', wlanid)
      when 'WLAF' then GetTranslatedString('Lost and Found Item', wlanid)
      when 'WLFA' then GetTranslatedString('Lost and Found Action', wlanid)
      when 'AACC' then GetTranslatedString('Accounts Receivable', wlanid)
      else tablename
    end 
  into description
  from dual;
  
  if description is not null then
    description := GetTranslatedString(description, wlanid);
  end if;
  return description;
end;
/

create or replace function GetLogDescriptionFromLogType(logtype in number, tablename in varchar2, wlanid in number)
return varchar2
as
  description varchar2(2000);
begin
  description := '';
  
  if        (logtype in (90, 91, 92, 93)) then description := TableToDescription('IRMS', wlanid);
  else if (logtype in (47, 48, 49, 50, 51, 52, 144,145, 146, 147, 148, 149)) then description := TableToDescription('YBIS', wlanid);
        else if (logtype >=164) and (logtype <=184) then description := GetTranslatedString('Activity', wlanid);
              else if (logtype >=185) and (logtype <=201) then description := GetTranslatedString('Task', wlanid);
                    else if (logtype >=206) and (logtype <=207) then description := GetTranslatedString('Reservation', wlanid);
                          else if (logtype >=70) and (logtype <=74) or  (logtype >=211) and (logtype <=213) then description := GetTranslatedString('Hotel Maintenance', wlanid);
                                else if (logtype >=644) and (logtype <=648) then description := TableToDescription('WLAF', wlanid);
                                      else if (logtype >=649) and (logtype <=651) then description := TableToDescription('WLFA', wlanid);
                                            else description := TableToDescription(trim(tablename), wlanid);
                                            end if;
                                      end if;      
                                end if;
                          end if;      
                    end if;
              end if;
        end if;
   end if;
  return description;
end;
/

CREATE OR REPLACE function SearchMissingNames(tablename in varchar2, wlogtableid in number, NewLogKind in number, NameChangeLogKind in number, DeleteLogKind in number)
return varchar2
as
begin
  return 'SearchMissingNames not yet implemented.';
end;
/

CREATE OR REPLACE function GetNameIdRoomFromWlog(wlogid in number, tablename in varchar2, wlanid in number)
return varchar2
as
  description varchar2(2000);
  colvalue varchar2(2000);
  origcolvalue varchar2(2000);
  TYPE wlogcursortype IS REF CURSOR;
  wlogcursor wlogcursortype;
  wlogtableid number;
  wlogtype number;
  yslmtype number;
begin
  description := '';
  if tablename = 'YRES' then 
    OPEN wlogcursor for select wlog_table_id, ( select xcms_name1 || RTRIM( ' '  || xcms_name2 ) || RTRIM( ' ' || xcms_name3 ) from xcms, yres where yres_id = wlog_table_id and yres_xcms_id = xcms_id(+) ) yresname from wlog where wlog_id=wlogid;
    LOOP 
      FETCH wlogcursor into wlogtableid, colvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    if colvalue is null then --nvl(colvalue, ' ') = ' ' 
      colvalue := GetTranslatedString('Reservation Number : ', wlanid) ||to_char(wlogtableid);
    end if;
    description := colvalue;
  else
  if tablename = 'YRMS' then
    OPEN wlogcursor for select wlog_table_id, ( select yrms_shortdesc from yrms where yrms_id = wlog_table_id ) yrmsname 
                                  from wlog 
                                  where wlog_id=wlogid;
    LOOP 
      FETCH wlogcursor into wlogtableid, colvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    description := GetTranslatedString('Room No : ', wlanid) ||colvalue;
  else
  if tablename = 'ZPOS' then

    OPEN wlogcursor for select wlog_table_id, wlog_type, 
                                            ( select xcms_name1 || RTRIM( ' '  || xcms_name2 ) || RTRIM( ' ' || xcms_name3 ) from zpos, xcms where zpos_id = wlog_table_id and zpos_xcms_id = xcms_id(+) ) zposname,
                                            ( select xcms_name1 || RTRIM( ' '  || xcms_name2 ) || RTRIM( ' ' || xcms_name3 ) from zpos, xcms where zpos_id = wlog_table_id and ZPOS_ORIGINATED_XCMS_ID = xcms_id(+) ) zposorigname 
                                  from wlog 
                                  where wlog_id=wlogid;
    LOOP 
      FETCH wlogcursor into wlogtableid, wlogtype, colvalue, origcolvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    if wlogtype = 439 then
      if origcolvalue is not null then
        description := origcolvalue||', '||to_char(wlogtableid);
      else
        description := GetTranslatedString('Zpos ID : ', wlanid)||to_char(wlogtableid);
      end if;
    else
      if colvalue is not null then
        description := origcolvalue||', '||to_char(wlogtableid);
      else
        description := GetTranslatedString('Zpos ID : ', wlanid)||to_char(wlogtableid);
      end if;
    end if;
  else if tablename = 'XCCS' then
    OPEN wlogcursor for select wlog_table_id, ( select xccs_holdername from xccs where xccs_id = wlog_table_id) xccsholdername 
                                  from wlog 
                                  where wlog_id=wlogid;
    LOOP 
      FETCH wlogcursor into wlogtableid, colvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    if colvalue is null then
      description := SearchMissingNames(tablename, wlogtableid, 375,376,377); 
    else
      description := colvalue;
    end if;
  else if tablename = 'YBOM' then
    OPEN wlogcursor for select wlog_table_id, ( select ybom_name from ybom where ybom_id = wlog_table_id ) ybomname 
                                  from wlog 
                                  where wlog_id=wlogid;
    LOOP 
      FETCH wlogcursor into wlogtableid, colvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    if colvalue is null then
      description := SearchMissingNames(tablename, wlogtableid, 60,64,61); 
    else
      description := colvalue||', '||to_char(wlogtableid);
    end if;
  else if tablename = 'YEVM' then
    OPEN wlogcursor for select wlog_table_id, ( select yevm_name from yevm where yevm_id = wlog_table_id ) yevmname 
                                  from wlog 
                                  where wlog_id=wlogid;
    LOOP 
      FETCH wlogcursor into wlogtableid, colvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    if colvalue is null then
      description := SearchMissingNames(tablename, wlogtableid, 113,161,114); 
    else
      description := colvalue||', '||to_char(wlogtableid);
    end if;
  else if tablename = 'YEBS' then
    OPEN wlogcursor for select wlog_table_id, ( select yfsc_longdesc from yfsc, yebs where yebs_yfsc_id=yfsc_id and yebs_id = wlog_table_id ) yebsname 
                                  from wlog 
                                  where wlog_id=wlogid;
    LOOP 
      FETCH wlogcursor into wlogtableid, colvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    if colvalue is null then
      description := SearchMissingNames(tablename, wlogtableid, 128,0,129); 
    else
      description := colvalue||', '||to_char(wlogtableid);
    end if;
  else if tablename = 'YEMN' then
    OPEN wlogcursor for select wlog_table_id, 
                                  ( select yemn_menuname from yemn where yemn_id = wlog_table_id  ) yemnname, 
                                  ( select yemi_name from yemi where yemi_id = wlog_table_id  ) yeminame 
                                  from wlog 
                                  where wlog_id=wlogid;
    LOOP 
      FETCH wlogcursor into wlogtableid, colvalue, origcolvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    if colvalue is null then
      description := SearchMissingNames(tablename, wlogtableid, 230,234,231); 
    else
      description := colvalue||', '||to_char(wlogtableid);
    end if;
  else if tablename = 'YPKD' then
    OPEN wlogcursor for select wlog_table_id, 
                                  ( select ypkd_name from ypkd where ypkd_id=wlog_table_id) ypkdname 
                                  from wlog 
                                  where wlog_id=wlogid;
    LOOP 
      FETCH wlogcursor into wlogtableid, colvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    if colvalue is null then
      description := SearchMissingNames(tablename, wlogtableid, 317,324,318); 
    else
      description := colvalue||', '||to_char(wlogtableid);
    end if;
  else if tablename = 'YEMI' then
    OPEN wlogcursor for select wlog_table_id, 
                                  ( select yemi_name from yemi where yemi_id = wlog_table_id  ) yeminame 
                                  from wlog 
                                  where wlog_id=wlogid;
    LOOP 
      FETCH wlogcursor into wlogtableid, colvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    if colvalue is null then
      description := SearchMissingNames(tablename, wlogtableid, 274,296,275); 
    else
      description := colvalue||', '||to_char(wlogtableid);
    end if;
  else if tablename = 'YEBR' then
    OPEN wlogcursor for select wlog_table_id, 
                                  ( select ycit_longdesc from ycit, yebr where yebr_ycit_id=ycit_id and yebr_id = wlog_table_id ) yebrname 
                                  from wlog 
                                  where wlog_id=wlogid;
    LOOP 
      FETCH wlogcursor into wlogtableid, colvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    if colvalue is null then
      description := SearchMissingNames(tablename, wlogtableid, 150,338,151); 
    else
      description := colvalue||', '||to_char(wlogtableid);
    end if;
  else if tablename = 'WACT' then --code shows similarity for activities/tasks/mainteenances
    OPEN wlogcursor for select wlog_table_id, 
                                  ( select wact_description from wact where wact_id = wlog_table_id and wact_todo=0) acttaskmain 
                                  from wlog 
                                  where wlog_id=wlogid;
    LOOP 
      FETCH wlogcursor into wlogtableid, colvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    if colvalue is null then
      description := to_char(wlogtableid); 
    else
      description := colvalue||', '||to_char(wlogtableid);
    end if;
  else if tablename = 'ZBDP' then 
    OPEN wlogcursor for select wlog_table_id, 
                                  '' zbdpname 
                                  from wlog 
                                  where wlog_id=wlogid;
    LOOP 
      FETCH wlogcursor into wlogtableid, colvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    description := 'ZBDP #'||to_char(wlogtableid);
  else if tablename = 'YSLM' then 
    OPEN wlogcursor for select wlog_table_id, 
                                  yslm_type,
                                  (case when yslm_type = 1 then (select yrch_longdesc from yrch where yrch_id = yslm_recid) when yslm_type = 2 then (select ypac_longdesc from ypac where ypac_id = yslm_recid  ) else ' ' end) yslmDesc 
                                  from wlog, yslm 
                                  where wlog_id=wlogid and wlog_table_id=yslm_id(+);
    LOOP 
      FETCH wlogcursor into wlogtableid, yslmtype, colvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    description := colvalue;
  else if tablename = 'WLCK' then 
    OPEN wlogcursor for select wlog_table_id,
                                  ''  wlckDesc 
                                  from wlog 
                                  where wlog_id=wlogid;
    LOOP 
      FETCH wlogcursor into wlogtableid, colvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    description := to_char(wlogtableid);
  else if tablename = 'WLAF' then 
    OPEN wlogcursor for select wlog_table_id,
                                  (select wlaf_longdesc from wlaf where wlaf_id = wlog_table_id) wlaflongdesc 
                                  from wlog 
                                  where wlog_id=wlogid;
    LOOP 
      FETCH wlogcursor into wlogtableid, colvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    description := 'Id# '||to_char(wlogtableid)||', Item: '||colvalue;
  else if tablename = 'WLFA' then 
    OPEN wlogcursor for select wlog_table_id,
                                 (select wlfa_freetext from wlfa where wlfa_id = wlog_table_id) wlfadesc 
                                  from wlog 
                                  where wlog_id=wlogid;
    LOOP 
      FETCH wlogcursor into wlogtableid, colvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    description := colvalue;
  else if tablename = 'AACC' then 
    OPEN wlogcursor for select wlog_table_id,
                                 (select aacc_number from aacc where aacc_id = wlog_table_id) aaccnumber 
                                  from wlog 
                                  where wlog_id=wlogid;
    LOOP 
      FETCH wlogcursor into wlogtableid, colvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    description := colvalue;
  else if tablename = 'YBLH' then 
    OPEN wlogcursor for select wlog_table_id,
                                 ( select yblh_shortdesc from yblh where yblh_id = wlog_table_id ) yblhname 
                                  from wlog 
                                  where wlog_id=wlogid;
    LOOP 
      FETCH wlogcursor into wlogtableid, colvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    description := colvalue;
  else if tablename = 'YBLD' then 
    OPEN wlogcursor for select wlog_table_id,
                                ( select yblh_shortdesc from ybld, yblh where ybld_id = wlog_table_id and ybld_yblh_id = yblh_id ) ybldname 
                                  from wlog 
                                  where wlog_id=wlogid;
    LOOP 
      FETCH wlogcursor into wlogtableid, colvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    description := colvalue;
  else if tablename = 'ZFAC' then 
    OPEN wlogcursor for select wlog_table_id,
                               ( select xcms_name1 || RTRIM( ' '  || xcms_name2 ) || RTRIM( ' ' || xcms_name3 ) from xcms, zfac where zfac_id = wlog_table_id and zfac_xcms_id = xcms_id(+) ) zfacname 
                                  from wlog 
                                  where wlog_id=wlogid;
    LOOP 
      FETCH wlogcursor into wlogtableid, colvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    description := colvalue;
  else if tablename = 'YBIS' then 
    OPEN wlogcursor for select wlog_table_id,
                                           (select (select ' From: ' || xcms_name1 || RTRIM( ' '  || xcms_name2 ) || RTRIM( ' ' || xcms_name3 ) from ybis, yres, xcms where ybis_id = wlog_table_id and ybis_from_yres_id = yres_id and yres_xcms_id = xcms_id(+)) ||
                                           (select ' From: ' || xcms_name1 || RTRIM( ' '  || xcms_name2 ) || RTRIM( ' ' || xcms_name3 )                               from ybis, zfac, xcms where ybis_id = wlog_table_id and ybis_from_zfac_id = zfac_id and zfac_xcms_id = xcms_id(+)) ||
                                           (select ' To: ' || xcms_name1 || RTRIM( ' '  || xcms_name2 ) || RTRIM( ' ' || xcms_name3 ) from ybis, zwin, xcms where ybis_id = wlog_table_id and ybis_zwin_id = zwin_id and zwin_invoice_to_xcms_id = xcms_id(+)) from ybis where ybis_id = wlog_table_id 
                                           ) ybisname
                                  from wlog 
                                  where wlog_id=wlogid;
    LOOP 
      FETCH wlogcursor into wlogtableid, colvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    description := colvalue;
  else if tablename = 'YRMS' then 
    OPEN wlogcursor for select wlog_table_id,
                                           ( select icfg_longdesc from irms, icfg where irms_id = wlog_table_id and irms_icfg_id = icfg_id(+) ) irmsname
                                  from wlog 
                                  where wlog_id=wlogid;
    LOOP 
      FETCH wlogcursor into wlogtableid, colvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    description := colvalue;
  else if (tablename = 'NA') or (tablename = 'WUSR') then 
    description := ' ';
  else if tablename = 'YOVB' then 
    OPEN wlogcursor for select wlog_table_id,
                                          ( select decode (ycat_shortdesc,null,'House' , ycat_shortdesc) from yovb, ycat  where yovb_ycat_id=ycat_id(+) and yovb_id = wlog_table_id ) yovbname
                                  from wlog 
                                  where wlog_id=wlogid;
    LOOP 
      FETCH wlogcursor into wlogtableid, colvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    description := colvalue;
  else if tablename = 'WLIC' then 
    OPEN wlogcursor for select wlog_table_id,
                                         ( select wlic_longdesc from wlic where wlic_id = wlog_table_id ) wlicname
                                  from wlog 
                                  where wlog_id=wlogid;
    LOOP 
      FETCH wlogcursor into wlogtableid, colvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    description := colvalue;
  else if tablename = 'XCMS' then 
    OPEN wlogcursor for select wlog_table_id,
                                        ( select xcms_name1 || RTRIM( ' '  || xcms_name2 ) || RTRIM( ' ' || xcms_name3 ) from xcms where xcms_id = wlog_table_id ) xcmsname
                                  from wlog 
                                  where wlog_id=wlogid;
    LOOP 
      FETCH wlogcursor into wlogtableid, colvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    description := colvalue;
  else if tablename = 'YRST' then 
    OPEN wlogcursor for select wlog_table_id,
                                        ( select yrms_shortdesc from yrms, yrst where yrst_id = wlog_table_id and yrms_id=yrst_yrms_id) yrstname
                                  from wlog 
                                  where wlog_id=wlogid;
    LOOP 
      FETCH wlogcursor into wlogtableid, colvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    description := colvalue;
  else if tablename = 'YRCH' then 
    OPEN wlogcursor for select wlog_table_id,
                                        ( select yrch_shortdesc from yrch where yrch_id = wlog_table_id) yrchname
                                  from wlog 
                                  where wlog_id=wlogid;
    LOOP 
      FETCH wlogcursor into wlogtableid, colvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    description := colvalue;
  else if tablename = 'XCED' then 
    OPEN wlogcursor for select wlog_table_id,
                                       ( select xcms_name3 || RTRIM( ' '  || xcms_name2 ) || RTRIM( ' ' || xcms_name1 ) from xcms where xcms_id = wlog_table_id) xcedusername
                                  from wlog 
                                  where wlog_id=wlogid;
    LOOP 
      FETCH wlogcursor into wlogtableid, colvalue; 
      EXIT WHEN wlogcursor%NOTFOUND;
    END LOOP; 
    close wlogcursor;
    description := colvalue;
  else
    description := '';  
  end if;
  end if; 
  end if;
  end if;
  end if;
  end if;
  end if;
  end if;
  end if;
  end if;
  end if;
  end if;
  end if;
  end if;
  end if;
  end if;
  end if;
  end if;
  end if; --YBLH
  end if; --YBLD
  end if; --ZFAC
  end if; --YBIS
  end if; --IRMS
  end if; --NA + WUSR
  end if; --YOVB
  end if; --WLIC
  end if; --XCMS
  end if; --YRST
  end if; --YRCH
  end if; --XCED
   
  return description;
end;
/

create or replace view v8_sys_roomUsage as
select 1 value,ydet_yrms_id yrms_id 
from ydet,yres
where yres_id=ydet_yres_id 
and yres_resstatus<>3
and ydet_date-2<(select * from fideliodate)
and ydet_date+nvl((select wuss_value from wuss where wuss_name='UsageStatsDaysOnRoomSearch'),30)
    >(select * from fideliodate)
;


CREATE OR REPLACE VIEW V8_REP_XCMS_WMLG_INFO_NS
AS 
SELECT A.XCMS_ID,
            A.CREATION_XCED_ID,
            A.CREATION_XCED_INITIALS,
            A.UPDAT_XCED_ID,
            A.UPDAT_XCED_INITIALS,
            A.WMLG_TIME_CREATION,
            A.WMLG_TIME_UPDATE,
            A.XCCA_ID,
            A.XCCA_LONGDESC,
            A.XCMS_TYPE
       FROM (SELECT va.* FROM V8_REP_XCMS_WMLG_INFO VA, XCMS XC WHERE VA.XCMS_ID = XC.XCMS_ID AND XC.XCMS_ACTIVE = 1) A,
            (SELECT B.XCED_ID,
                    B.XCED_title,
                    B.XCED_LOGINNAME,
                    C.XECL_ID,
                    C.XECL_EMP_XCMS_ID,
                    C.XECL_CUST_XCMS_ID AS XCMS_ID
               FROM XCED B, XECL C
              WHERE B.XCED_ID = C.XECL_EMP_XCMS_ID AND B.XCED_SALESMGR = 1) D
      WHERE A.XCMS_ID = D.XCMS_ID(+) AND D.XCMS_ID IS NULL
   ORDER BY A.XCMS_ID;


CREATE OR REPLACE VIEW V8_REP_GROSS_NET_TAXLINK
AS 
select
nvl(ZPOS_ZFAC_ID,0) ZPOS_ZFAC_ID,
nvl(ZPOS_TAXLINK_ID,0) ZPOS_TAXLINK_ID,
sum(ZPOS_ZDCO_ID) ZPOS_ZDCO_ID,
sum(GROSSREV) GROSSREV,
SUM(NETREV) NETREV,
SUM(ZPOS_ZTCD_ID) ZPOS_ZTCD_ID,
sum(ZTXC_ID) ZTXC_ID
from
(
-- Gross and Net 
select
zpos.zpos_zfac_id ZPOS_ZFAC_ID,
(select ZFAC.ZFAC_ZFAG_ID from zfac where zfac.zfac_id=ZPOS.ZPOS_ZFAC_ID) ZFAG_ID,
zpos.zpos_taxlink_id ZPOS_TAXLINK_ID,
ZPOS.ZPOS_ZDCO_ID ZPOS_ZDCO_ID,
(ZPOS.ZPOS_QUANTITY*ZPOS.ZPOS_GROSSUNITPRICE)  GROSSREV,
(ZPOS.ZPOS_QUANTITY*ZPOS.ZPOS_UNITPRICE) NETREV,
0  ZPOS_ZTCD_ID,
0 ZTXC_ID
from
zpos
where
ZPOS.ZPOS_CDT=1
union all
-- VAT ID
select
zpos.zpos_zfac_id ZPOS_ZFAC_ID,
(select ZFAC.ZFAC_ZFAG_ID from zfac where zfac.zfac_id=ZPOS.ZPOS_ZFAC_ID) ZFAG_ID,
zpos.zpos_taxlink_id ZPOS_TAXLINK_ID,
0 ZPOS_ZDCO_ID,
0 GROSSREV,
0 NETREV,
ZPOS.ZPOS_ZTCD_ID ZPOS_ZTCD_ID,
nvl((select ZTCD.ZTCD_ZTXC_ID from ztcd where ZTCD.ZTCD_ID=ZPOS.ZPOS_ZTCD_ID),0) ZTXC_ID
from
zpos
where
ZPOS.ZPOS_CDT=2
)
where
zfag_id in (select zfag_id from zfag where zfag_type=12)
group by 
ZPOS_ZFAC_ID, ZPOS_TAXLINK_ID;

CREATE OR REPLACE VIEW V8_REP_STAT_TURNAWAY
AS 
SELECT
XCMA.XCMA_ID,
XCMA.XCMA_SHORTDESC,
XCMA.XCMA_LONGDESC,
YTAC.YTAC_ID,
YTAC.YTAC_SHORTDESC,
YTAC.YTAC_LONGDESC,
YTAW.YTAW_ID,
YTAW.YTAW_NOOFADULTS,
YTAW.YTAW_NOOFNIGHTS,
YTAW.YTAW_NOOFROOMS,
YTAW.YTAW_RESDATE
FROM
xcma,
ytac,
ytaw
WHERE
YTAW.YTAW_XCMA_ID = XCMA.XCMA_ID AND
YTAW.YTAW_YTAC_ID = YTAC.YTAC_ID;


create or replace view V8_REP_USERRIGHTS as  
SELECT 
  WUSR_ID,
  WUSR_XCMS_ID,
  (select v8_rep_name.NAME2 from v8_rep_name where v8_rep_name.XCMS_ID = wusr.WUSR_XCMS_ID) USERNAME,
  (select xced_status from xced where xced_id=WUSR.WUSR_XCMS_ID) XCED_STATUS, --TT24482
  WUSR_WEGR_ID,
  WUSR_NAME,
  WUSR_VALUE,
  WEGR_ID,
  WEGR_SHORTDESC,
  WEGR_LONGDESC,
  WEGR_DISPLORDER,
  wurs.*
FROM 
  wusr,
  wegr,
  wurs
WHERE 
  wusr_wegr_id = wegr.WEGR_ID(+) and
  wusr.WUSR_NAME = wurs.WURS_DBNAME;


CREATE OR REPLACE VIEW V8_REP_HS_ARR_ALLCILD_YCATYRCH
AS 
select wdat_date, ydet_ycat_id ycat_ID,ydet_yrch_id yrch_id, nvl(sum(allarrchild),0) allarrchild,nvl(sum(defarrchild),0) defarrchild,nvl(sum(tentarrchild),0) tentarrchild 
   ,nvl(sum(allarrdomesticchild),0) allarrdomesticchild,nvl(sum(defarrdomesticchild),0) defarrdomesticchild,nvl(sum(tentarrdomesticchild),0) tentarrdomesticchild           
from (
select 
   wdat_date
   ,ydet_ycat_id
   ,ydet_yrch_id
   ,sum(yrgl_number*ydet_noofrooms) allarrchild
   ,sum(decode(xcac_domestic,1,yrgl_number*ydet_noofrooms,0)) allarrdomesticchild
   ,sum(decode(yres_resstatus,1,decode(xcac_domestic,1,0,yrgl_number*ydet_noofrooms))) defarrchild
   ,sum(decode(yres_resstatus,0,decode(xcac_domestic,1,0,yrgl_number*ydet_noofrooms))) tentarrchild
   ,sum(decode(yres_resstatus,1,decode(xcac_domestic,1,yrgl_number*ydet_noofrooms,0))) defarrdomesticchild
   ,sum(decode(yres_resstatus,0,decode(xcac_domestic,1,yrgl_number*ydet_noofrooms,0))) tentarrdomesticchild
from wdat,ydet,yres,yrgl,xcac,ycat
where
    ydet_date=wdat_date 
and yres_exparrtime=wdat_date
and ydet_yres_id=yres_id
and yres_resstatus<>3
and yrgl_ydet_id=ydet_id
and yrgl_xcac_id=xcac_id
and ycat_id=ydet_ycat_id
and ycat_notinavl=0
group by wdat_date,yres_resstatus,ydet_ycat_id,ydet_yrch_id
) group by wdat_date,ydet_ycat_id,ydet_yrch_id;


CREATE OR REPLACE VIEW V8_REP_HS_ARR_ALLDULT_YCATYRCH
AS 
select 
wdat_date,
YCAT_id,
yrch_id yrch_id,
sum(allexpadult) allexpadult,
sum(defexpadult) defexpadult,
sum(tentexpadult) tentexpadult  
from
(
select 
   wdat_date
   ,ydet_YCAT_id YCAT_id
   ,ydet_yrch_id YRCH_ID
   ,sum(ydet_adultno*ydet_noofrooms) allexpadult
   ,decode(yres_resstatus,1,sum(ydet_adultno*ydet_noofrooms)) defexpadult
   ,decode(yres_resstatus,0,sum(ydet_adultno*ydet_noofrooms)) tentexpadult
from wdat,ydet,yres,ycat
where
    ydet_date=wdat_date 
and yres_exparrtime=wdat_date
and ydet_yres_id=yres_id
and yres_resstatus<>3
and ycat_id=ydet_ycat_id
and ycat_notinavl=0
group by wdat_date,yres_resstatus,ydet_YCAT_id,ydet_yrch_id
) group by wdat_date,YCAT_id,yrch_id;


CREATE OR REPLACE VIEW V8_REP_HS_ARR_ALLROOM_YCATYRCH
AS 
select 
wdat_date,
YCAT_id,
YRCH_ID YRCH_ID,
sum(allexproom) allexproom,
sum(defexproom) defexproom,
sum(tentexproom) tentexproom
from
(
select 
   wdat_date
   ,ydet_YCAT_id YCAT_id
   ,ydet_yrch_id YRCH_ID
   ,decode(nvl(ydet_sharenum,0),0,sum(ydet_noofrooms),avg(ydet_noofrooms)) allexproom
   ,decode(nvl(ydet_sharenum,0),0,sum(decode(yres_resstatus,1,ydet_noofrooms,0)),decode(max(yres_resstatus),1,avg(ydet_noofrooms),0)) defexproom
   ,decode(nvl(ydet_sharenum,0),0,sum(decode(yres_resstatus,0,ydet_noofrooms,0)),decode(max(yres_resstatus),0,avg(ydet_noofrooms),0)) tentexproom
from wdat,ydet yd,yres y,ycat
where
    ydet_date=wdat_date 
and yres_exparrtime=wdat_date
and ydet_yres_id=yres_id
and yres_resstatus<>3
and ycat_id=ydet_ycat_id
and ycat_notinavl=0
and (nvl(ydet_sharenum,0)=0
     or
     not exists (select d.ydet_sharenum 
                           from ydet d,yres r 
                           where d.ydet_yres_id=r.yres_id
                             and d.ydet_id<>yd.ydet_id 
                             and (y.yres_exparrtime<>r.yres_exparrtime)
                             and d.ydet_sharenum=yd.ydet_sharenum
                           )
    )
group by wdat_date,ydet_sharenum,ydet_YCAT_id,ydet_yrch_id
) group by wdat_date,YCAT_id,YRCH_ID;


CREATE OR REPLACE VIEW V8_REP_HS_DEP_ALLROOM_YCATYRCH
AS 
select wdat_date,ycat_id,yrch_id yrch_id,sum(alldeproom) alldeproom,sum(defdeproom) defdeproom ,sum(tentdeproom) tentdeproom  from (
select 
   wdat_date
   ,ydet_ycat_id ycat_id
   ,ydet_yrch_id yrch_id
   ,ydet_sharenum
   ,decode(nvl(ydet_sharenum,0),0,sum(ydet_noofrooms),avg(ydet_noofrooms)) alldeproom
   ,decode(nvl(ydet_sharenum,0),0,sum(decode(yres_resstatus,1,ydet_noofrooms,0)),decode(max(yres_resstatus),1,avg(ydet_noofrooms),0)) defdeproom
   ,decode(nvl(ydet_sharenum,0),0,sum(decode(yres_resstatus,0,ydet_noofrooms,0)),decode(max(yres_resstatus),0,avg(ydet_noofrooms),0)) tentdeproom
from wdat,ydet yd,yres y,ycat
where
ydet_id=yres_last_ydet_id
and yres_expdeptime=wdat_date     
and yres_resstatus<>3
and ycat_id=ydet_ycat_id
and ycat_notinavl=0
and (nvl(ydet_sharenum,0)= 0
     or
     not exists (select d.ydet_sharenum 
                           from ydet d,yres r 
                           where d.ydet_yres_id=r.yres_id
                             and d.ydet_id<>yd.ydet_id 
                             and y.yres_expdeptime<>r.yres_expdeptime 
                             and d.ydet_sharenum=yd.ydet_sharenum
                           )
    )
group by wdat_date,ydet_sharenum,ydet_ycat_id,ydet_yrch_id)
group by wdat_date,ycat_id,yrch_id;


CREATE OR REPLACE VIEW V8_REP_YBLD_OCC_YCATYRCH
AS 
select
   ybld_ycat_id,
   YBLH.YBLH_YRCH_ID yrch_id,
   ybld_date,
   sum(decode(ybst_resstatus,1,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms),0)) defnoofrooms,
   sum(decode(ybst_resstatus,1,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms*ybld_noofadults),0)) defnoofadults,
   sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms),0)) tentnoofrooms,
   sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms*ybld_noofadults),0)) tentnoofadults,
   sum(decode(ybst_resstatus,1,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms),nvl(ybld_expectednoofrooms,0))) defnoofroomsbookingpos,
   sum(decode(ybst_resstatus,1,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms*nvl(ybld_noofadults,0)),nvl(ybld_expectednoofrooms,0)*nvl(ybld_noofadults,0))) defnoofadultsBookinPos,
   nvl(sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,case when nvl((select sum(YDET.YDET_NOOFROOMS) from ydet,yres where YDET.YDET_YBLD_ID=ybld.ybld_id and YDET.YDET_YRES_ID=yres.yres_id and yres.yres_resstatus=1),0)>=YBLD.YBLD_EXPECTEDNOOFROOMS then 0 else  YBLD.YBLD_EXPECTEDNOOFROOMS-nvl((select sum(YDET.YDET_NOOFROOMS) from ydet,yres where YDET.YDET_YBLD_ID=ybld.ybld_id and YDET.YDET_YRES_ID=yres.yres_id and yres.yres_resstatus=1),0)end))),0) defnoofroomsbookingposavail,
   nvl(sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,case when nvl((select sum(YDET.YDET_NOOFROOMS) from ydet,yres where YDET.YDET_YBLD_ID=ybld.ybld_id and YDET.YDET_YRES_ID=yres.yres_id and yres.yres_resstatus=1),0)>=YBLD.YBLD_EXPECTEDNOOFROOMS then 0 else  YBLD.YBLD_EXPECTEDNOOFROOMS-nvl((select sum(YDET.YDET_NOOFROOMS) from ydet,yres where YDET.YDET_YBLD_ID=ybld.ybld_id and YDET.YDET_YRES_ID=yres.yres_id and yres.yres_resstatus=1),0)end * YBLD.YBLD_NOOFADULTS ))),0) defnoofadultsbookingposavail,
   sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms-nvl(ybld_expectednoofrooms,0)),0)) tentnoofroomsbookingpos,
   sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,(ybld_actualnoofrooms-nvl(ybld_expectednoofrooms,0))*nvl(ybld_noofadults,0)),0)) tentnoofadultsBookingPos,
   nvl(sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,case when nvl((select sum(YDET.YDET_NOOFROOMS) from ydet,yres where YDET.YDET_YBLD_ID=ybld.ybld_id and YDET.YDET_YRES_ID=yres.yres_id and yres.yres_resstatus=1),0)>=YBLD.YBLD_EXPECTEDNOOFROOMS then YBLD.YBLD_ACTUALNOOFROOMS-nvl((select sum(YDET.YDET_NOOFROOMS) from ydet,yres where YDET.YDET_YBLD_ID=ybld.ybld_id and YDET.YDET_YRES_ID=yres.yres_id and yres.yres_resstatus=1),0) else YBLD.YBLD_ACTUALNOOFROOMS-YBLD.YBLD_EXPECTEDNOOFROOMS  end))),0) tentnoofroomsbookingposavail,
   nvl(sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,case when nvl((select sum(YDET.YDET_NOOFROOMS) from ydet,yres where YDET.YDET_YBLD_ID=ybld.ybld_id and YDET.YDET_YRES_ID=yres.yres_id and yres.yres_resstatus=1),0)>=YBLD.YBLD_EXPECTEDNOOFROOMS then YBLD.YBLD_ACTUALNOOFROOMS-nvl((select sum(YDET.YDET_NOOFROOMS) from ydet,yres where YDET.YDET_YBLD_ID=ybld.ybld_id and YDET.YDET_YRES_ID=yres.yres_id and yres.yres_resstatus=1),0) else YBLD.YBLD_ACTUALNOOFROOMS-YBLD.YBLD_EXPECTEDNOOFROOMS  end * YBLD.YBLD_NOOFADULTS))),0) tentnoofadultsbookingposavail
from ybld,yblh,ybst,fideliodate d
where
    ybld_yblh_id=yblh_id
and YBLH_YBST_ID=ybst_id
and YBST_RESSTATUS<>3
group by ybld_ycat_id,yblh_yrch_id,ybld_date;



CREATE OR REPLACE VIEW V8_REP_YDET_ADULT_WB_YCATYRCH
AS 
select ydet_ycat_id,ydet_yrch_id ydet_yrch_id,ydet_date,sum(defadultno) defadultno,sum(tentadultno) tentadultno,sum(defblocksubstract) defblocksubstract,sum(tentblocksubstract) tentblocksubstract from
 (
  select
   ydet_ycat_id,
   ydet_yrch_id,
   ydet_date
         ,sum(decode(yres_resstatus,1,ydet_noofrooms*ydet_adultno,0)) defadultno
         ,sum(decode(yres_resstatus,0,ydet_noofrooms*ydet_adultno,0)) tentadultno
         ,decode(sign(nvl(min(ybld_cutoffdate),d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,0
                  ,avg(ydet_noofrooms)*(avg(decode(ybst_resstatus,1,nvl(ybld_noofadults,0),0)))) defblocksubstract 
         ,decode(sign(nvl(min(ybld_cutoffdate),d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,0
                  ,avg(ydet_noofrooms)*(avg(decode(ybst_resstatus,0,nvl(ybld_noofadults,0),0)))) tentblocksubstract 
 from ydet,yres,ybld,yblh,ybst,fideliodate d
 where
     ydet_yres_id=yres_id(+)
 and ydet_ybld_id=ybld_id(+)
 and ybld_yblh_id=yblh_id(+)
 and yblh_ybst_id=ybst_id(+)
 and yres_resstatus<>3
 and yres_exparrtime<>yres_expdeptime
 and nvl(ydet_sharenum,0)<>0
 group by ydet_sharenum,ydet_ycat_id,ydet_yrch_id,ydet_date,WGBS_DATEVALUE
 union all
  select
   ydet_ycat_id,
   ydet_yrch_id,
   ydet_date
         ,sum(decode(yres_resstatus,1,ydet_noofrooms*ydet_adultno,0)) defadultno
         ,sum(decode(yres_resstatus,0,ydet_noofrooms*ydet_adultno,0)) tentadultno
         ,decode(sign(nvl(ybld_cutoffdate,d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,0
                  ,sum(decode(nvl(ybst_resstatus,0),1,ydet_noofrooms*nvl(ybld_noofadults,0),0))) defblocksubstract 
         ,decode(sign(nvl(ybld_cutoffdate,d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,0
                  ,sum(decode(nvl(ybst_resstatus,0),0,ydet_noofrooms*nvl(ybld_noofadults,0),0))) tentblocksubstract 
 from ydet,yres,ybld,yblh,ybst,fideliodate d
 where
     ydet_yres_id=yres_id(+)
 and ydet_ybld_id=ybld_id(+)
 and ybld_yblh_id=yblh_id(+)
 and yblh_ybst_id=ybst_id(+)
 and yres_resstatus<>3
 and yres_exparrtime<>yres_expdeptime
 and nvl(ydet_sharenum,0)=0
 group by ydet_ycat_id,ydet_yrch_id,ydet_date,WGBS_DATEVALUE,ybld_cutoffdate
)
group by ydet_ycat_id,ydet_yrch_id,ydet_date;


CREATE OR REPLACE VIEW V8_REP_YDET_OCC_WB_YCATYRCH
AS 
select ydet_ycat_id,ydet_yrch_id ydet_yrch_id,ydet_date,sum(defnoofrooms) defnoofrooms,sum(tentnoofrooms) tentnoofrooms,sum(tentblockdefresroom) tentblockdefresroom from
 (
  select
   ydet_ycat_id,
   ydet_yrch_id,
   ydet_date,
          decode(sign(nvl(ybld_cutoffdate,d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,sum(decode(yres_resstatus,1,ydet_noofrooms,0))
                  ,sum(decode(ybst_resstatus,0,decode(yres_resstatus,1,ydet_noofrooms,0),0))
                  )
          defnoofrooms,
          decode(sign(nvl(ybld_cutoffdate,d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,sum(decode(yres_resstatus,0,ydet_noofrooms,0))
                  ,0
                  )
          tentnoofrooms,
          decode(sign(nvl(ybld_cutoffdate,d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,0
                  ,sum(decode(ybst_resstatus,0,decode(yres_resstatus,1,ydet_noofrooms,0),0))
                  )
          tentblockdefresroom
 from ydet,yres,ybld,yblh,ybst,fideliodate d
 where
     ydet_yres_id=yres_id(+)
 and ydet_ybld_id=ybld_id(+)
 and ybld_yblh_id=yblh_id(+)
 and yblh_ybst_id=ybst_id(+)
 and yres_resstatus<>3
 and yres_exparrtime<>yres_expdeptime
 and nvl(ydet_sharenum,0)=0
 group by ydet_ycat_id,ydet_yrch_id,ydet_date,ydet_sharenum,ybld_cutoffdate,d.WGBS_DATEVALUE
 union all
 select
   ydet_ycat_id,
   ydet_yrch_id,
   ydet_date,
          decode(sign(nvl(min(ybld_cutoffdate),d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,sum(decode(yres_resstatus,1,ydet_noofrooms,0))/greatest(1,sum(decode(yres_resstatus,1,1,0)))
                  ,avg(decode(nvl(ybst_resstatus,0),0,decode(yres_resstatus,1,ydet_noofrooms,0),0))
                  )
          defnoofrooms,
          decode(sign(nvl(min(ybld_cutoffdate),d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,decode(sum(yres_resstatus),0,avg(decode(yres_resstatus,0,ydet_noofrooms,0)),0)
                  ,0
                  )
          tentnoofrooms,
          decode(sign(nvl(min(ybld_cutoffdate),d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,0
                  ,avg(decode(ybst_resstatus,0,decode(yres_resstatus,1,ydet_noofrooms,0),0))
                  )
          tentblockdefresroom
 from ydet,yres,ybld,yblh,ybst,fideliodate d
 where
     ydet_yres_id=yres_id(+)
 and ydet_ybld_id=ybld_id(+)
 and ybld_yblh_id=yblh_id(+)
 and yblh_ybst_id=ybst_id(+)
 and yres_resstatus<>3
 and yres_exparrtime<>yres_expdeptime
 and nvl(ydet_sharenum,0)<>0
 group by ydet_ycat_id,ydet_yrch_id,ydet_date,ydet_sharenum,d.WGBS_DATEVALUE
 ) group by ydet_ycat_id,ydet_yrch_id,ydet_date;


CREATE OR REPLACE VIEW V8_REP_YCATYRCH_HISTFOR 
AS 
SELECT 
wd.WDAT_DATE WDAT_DATE, 
wd.WDAT_DATE WDAT_DATE2, 
wd.WDAT_DATE WDAT_DATE3, 
wd.YCAT_ID YCAT_ID,
wd.yrch_id yrch_id, 
(SELECT YRCH.YRCH_DISPLORDER FROM YRCH WHERE YRCH.YRCH_ID=wd.YRCH_ID) YRCH_DISPLORDER,
(SELECT YRCH.YRCH_YRCC_ID FROM YRCH WHERE YRCH.YRCH_ID=wd.YRCH_ID) YRCH_YRCC_ID,
(SELECT YRCC.YRCC_DISPLORDER FROM YRCH,yrcc WHERE  yrch.yrch_yrcc_id=yrcc.yrcc_id and YRCH.YRCH_ID=wd.YRCH_ID) YRCC_DISPLORDER,
(SELECT YCAT.YCAT_DISPLORDER FROM YCAT WHERE YCAT.YCAT_ID=wd.YCAT_ID) YCAT_DISPLORDER,  
wd.YCAT_LONGDESC YCAT_LONGDESC, 
--wd.YCAT_INACTIVE YCAT_INACTIVE,  
(decode(wd.FUTURE,1, 
  (nvl((select V8_REP_YBLD_OCC_YCATYRCH.DEFNOOFROOMS from V8_REP_YBLD_OCC_YCATYRCH where V8_REP_YBLD_OCC_YCATYRCH.YBLD_YCAT_ID=wd.YCAT_ID and V8_REP_YBLD_OCC_YCATYRCH.yrch_id=wd.yrch_id and  V8_REP_YBLD_OCC_YCATYRCH.YBLD_DATE=wd.WDAT_DATE),0)+nvl((select V8_REP_YDET_OCC_WB_YCATYRCH.DEFNOOFROOMS from V8_REP_YDET_OCC_WB_YCATYRCH where V8_REP_YDET_OCC_WB_YCATYRCH.YDET_YCAT_ID=wd.YCAT_ID and V8_REP_YDET_OCC_WB_YCATYRCH.ydet_yrch_id=wd.yrch_id and V8_REP_YDET_OCC_WB_YCATYRCH.YDET_DATE=wd.WDAT_DATE),0)), 
  (nvl((select sum(decode(shis.SHIS_YRES_RESSTATUS,3,0,decode(shis.SHIS_DEP_ID,1,0,shis.SHIS_YDET_NOOFROOMS))) from shis where shis.SHIS_YDET_YCAT_ID=wd.YCAT_ID and SHIS.SHIS_YDET_YRCH_ID=WD.YRCH_ID and shis.SHIS_WDAT_DATE=wd.WDAT_DATE),0)))) 
  DEF_NOOFRROMS, 
(decode(wd.FUTURE,1, 
  (nvl((select V8_REP_YBLD_OCC_YCATYRCH.DEFNOOFROOMS from V8_REP_YBLD_OCC_YCATYRCH where V8_REP_YBLD_OCC_YCATYRCH.YBLD_YCAT_ID=wd.YCAT_ID and V8_REP_YBLD_OCC_YCATYRCH.yrch_id=wd.yrch_id and V8_REP_YBLD_OCC_YCATYRCH.YBLD_DATE=wd.WDAT_DATE),0)+nvl((select V8_REP_YBLD_OCC_YCATYRCH.DEFNOOFROOMSBOOKINGPOSAVAIL from V8_REP_YBLD_OCC_YCATYRCH where V8_REP_YBLD_OCC_YCATYRCH.YBLD_YCAT_ID=wd.YCAT_ID and V8_REP_YBLD_OCC_YCATYRCH.yrch_id=wd.yrch_id and V8_REP_YBLD_OCC_YCATYRCH.YBLD_DATE=wd.WDAT_DATE),0)+nvl((select V8_REP_YDET_OCC_WB_YCATYRCH.DEFNOOFROOMS from V8_REP_YDET_OCC_WB_YCATYRCH where V8_REP_YDET_OCC_WB_YCATYRCH.YDET_YCAT_ID=wd.YCAT_ID and V8_REP_YDET_OCC_WB_YCATYRCH.ydet_yrch_id=wd.yrch_id and V8_REP_YDET_OCC_WB_YCATYRCH.YDET_DATE=wd.WDAT_DATE),0)), 
  (nvl((select sum(decode(shis.SHIS_YRES_RESSTATUS,3,0,decode(shis.SHIS_DEP_ID,1,0,shis.SHIS_YDET_NOOFROOMS))) from shis where shis.SHIS_YDET_YCAT_ID=wd.YCAT_ID and SHIS.SHIS_YDET_YRCH_ID=WD.YRCH_ID and shis.SHIS_WDAT_DATE=wd.WDAT_DATE),0)))) 
  DEF_NOOFRROMS_BOOKINGPOS, 
(decode(wd.FUTURE,1, 
  (nvl((select allexproom from V8_REP_HS_ARR_ALLROOM_YCATYRCH v where v.YCAT_id= wd.YCAT_ID and v.yrch_id=wd.yrch_id and v.wdat_date=wd.WDAT_DATE),0)), 
  (nvl((select sum(decode(shis.SHIS_YRES_RESSTATUS,3,0,decode(shis.SHIS_ARR_ID,1,shis.SHIS_YDET_NOOFROOMS,0))) from shis where shis.SHIS_YDET_YCAT_ID=wd.YCAT_ID and SHIS.SHIS_YDET_YRCH_ID=WD.YRCH_ID and shis.SHIS_WDAT_DATE=wd.WDAT_DATE),0)))) 
  ARRIVAL_NOOFROOMS, 
(decode(wd.FUTURE,1, 
  (nvl((select defexproom from V8_REP_HS_ARR_ALLROOM_YCATYRCH v where v.YCAT_id= wd.YCAT_ID and v.yrch_id=wd.yrch_id and v.wdat_date=wd.WDAT_DATE),0)), 
  (nvl((select sum(decode(shis.SHIS_YRES_RESSTATUS,1,decode(shis.SHIS_ARR_ID,1,shis.SHIS_YDET_NOOFROOMS,0),0)) from shis where shis.SHIS_YDET_YCAT_ID=wd.YCAT_ID and SHIS.SHIS_YDET_YRCH_ID=WD.YRCH_ID and shis.SHIS_WDAT_DATE=wd.WDAT_DATE),0)))) 
  ARRIVAL_NOOFROOMS_def, 
(decode(wd.FUTURE,
        1,(nvl((select alldeproom 
                from V8_REP_HS_DEP_ALLROOM_YCATYRCH v 
                where v.YCAT_id= wd.YCAT_ID 
                and v.yrch_id=wd.yrch_id
                and v.wdat_date=wd.WDAT_DATE
               ),0)
          )
         ,(nvl((select sum(det.YDET_NOOFROOMS) 
                from v8_rep_ydet_infos det, yres 
                where det.YDET_YCAT_ID = wd.YCAT_ID 
                AND det.YDET_ID = yres.YRES_LAST_YDET_ID 
                and DET.YDET_YRCH_ID=wd.yrch_id
                and yres.YRES_EXPDEPTIME=wd.WDAT_DATE 
                and yres.YRES_RESSTATUS <> 3 
                and nvl(trunc(yres.YRES_ACTDEPTIME), yres.YRES_EXPDEPTIME) = wd.WDAT_DATE 
               ),0)
          ) 
         /*,(nvl((select sum(decode(shis.SHIS_YRES_RESSTATUS
                                  ,3,0
                                  ,decode(shis.SHIS_DEP_ID,1,shis.SHIS_YDET_NOOFROOMS,0))
                          ) 
                from shis 
                where shis.SHIS_YDET_YCAT_ID=wd.YCAT_ID 
                and shis.SHIS_WDAT_DATE=wd.WDAT_DATE
               ),0)
          )*/
      )
) DEPARTURE_NOOFROOMS,
(decode(wd.FUTURE,
        1,(nvl((select defdeproom 
                from V8_REP_HS_DEP_ALLROOM_YCATYRCH v 
                where v.YCAT_id= wd.YCAT_ID 
                and v.yrch_id=wd.yrch_id
                and v.wdat_date=wd.WDAT_DATE
               ),0)
          )
         ,(nvl((select sum(det.YDET_NOOFROOMS) 
                from v8_rep_ydet_infos det, yres 
                where det.YDET_YCAT_ID = wd.YCAT_ID 
                and DET.YDET_YRCH_ID=wd.yrch_id
                AND det.YDET_ID = yres.YRES_LAST_YDET_ID 
                and yres.YRES_EXPDEPTIME=wd.WDAT_DATE 
                and yres.YRES_RESSTATUS=1 
                and nvl(trunc(yres.YRES_ACTDEPTIME), yres.YRES_EXPDEPTIME) = wd.WDAT_DATE 
               ),0)
          ) 
         /*,(nvl((select sum(decode(shis.SHIS_YRES_RESSTATUS
                                  ,1,decode(shis.SHIS_DEP_ID,1,shis.SHIS_YDET_NOOFROOMS,0)
                                  ,0)
                          ) 
                from shis 
                where shis.SHIS_YDET_YCAT_ID=wd.YCAT_ID 
                and shis.SHIS_WDAT_DATE=wd.WDAT_DATE
               ),0)
          )*/
      )
) DEPARTURE_NOOFROOMS_def,
(decode(wd.FUTURE,1, 
(nvl((select V8_REP_YBLD_OCC_YCATYRCH.TENTNOOFROOMS from V8_REP_YBLD_OCC_YCATYRCH where V8_REP_YBLD_OCC_YCATYRCH.YBLD_YCAT_ID=wd.YCAT_ID and V8_REP_YBLD_OCC_YCATYRCH.yrch_id=wd.yrch_id and V8_REP_YBLD_OCC_YCATYRCH.YBLD_DATE=wd.WDAT_DATE),0)+(nvl((select V8_REP_YDET_OCC_WB_YCATYRCH.TENTNOOFROOMS from V8_REP_YDET_OCC_WB_YCATYRCH where V8_REP_YDET_OCC_WB_YCATYRCH.YDET_YCAT_ID=wd.YCAT_ID and V8_REP_YDET_OCC_WB_YCATYRCH.ydet_yrch_id=wd.yrch_id and V8_REP_YDET_OCC_WB_YCATYRCH.YDET_DATE=wd.WDAT_DATE),0)-nvl((select V8_REP_YDET_OCC_WB_YCATYRCH.TENTBLOCKDEFRESROOM from V8_REP_YDET_OCC_WB_YCATYRCH where V8_REP_YDET_OCC_WB_YCATYRCH.YDET_YCAT_ID=wd.YCAT_ID and V8_REP_YDET_OCC_WB_YCATYRCH.ydet_yrch_id=wd.yrch_id and V8_REP_YDET_OCC_WB_YCATYRCH.YDET_DATE=wd.WDAT_DATE),0))), 
0)) TENT_NOOFRROMS,  
(decode(wd.FUTURE,1, 
(nvl((select V8_REP_YBLD_OCC_YCATYRCH.TENTNOOFROOMSBOOKINGPOSAVAIL from V8_REP_YBLD_OCC_YCATYRCH where V8_REP_YBLD_OCC_YCATYRCH.YBLD_YCAT_ID=wd.YCAT_ID and V8_REP_YBLD_OCC_YCATYRCH.yrch_id=wd.yrch_id  and V8_REP_YBLD_OCC_YCATYRCH.YBLD_DATE=wd.WDAT_DATE),0)+(nvl((select V8_REP_YDET_OCC_WB_YCATYRCH.TENTNOOFROOMS from V8_REP_YDET_OCC_WB_YCATYRCH where V8_REP_YDET_OCC_WB_YCATYRCH.YDET_YCAT_ID=wd.YCAT_ID and V8_REP_YDET_OCC_WB_YCATYRCH.ydet_yrch_id=wd.yrch_id and V8_REP_YDET_OCC_WB_YCATYRCH.YDET_DATE=wd.WDAT_DATE),0))), 
0)) TENT_NOOFRROMS_BOOKINGPOS, 
(decode(wd.FUTURE,1, 
(nvl((select V8_REP_YBLD_OCC_YCATYRCH.DEFNOOFADULTS from V8_REP_YBLD_OCC_YCATYRCH where V8_REP_YBLD_OCC_YCATYRCH.YBLD_YCAT_ID=wd.YCAT_ID and V8_REP_YBLD_OCC_YCATYRCH.yrch_id=wd.yrch_id and V8_REP_YBLD_OCC_YCATYRCH.YBLD_DATE=wd.WDAT_DATE),0)+nvl((select V8_REP_YDET_ADULT_WB_YCATYRCH.DEFADULTNO from V8_REP_YDET_ADULT_WB_YCATYRCH where V8_REP_YDET_ADULT_WB_YCATYRCH.YDET_YCAT_ID=wd.YCAT_ID and V8_REP_YDET_ADULT_WB_YCATYRCH.ydet_yrch_id=wd.yrch_id and V8_REP_YDET_ADULT_WB_YCATYRCH.YDET_DATE=wd.WDAT_DATE),0)), 
(nvl((select sum(decode(shis.SHIS_YRES_RESSTATUS,3,0,decode(shis.SHIS_DEP_ID,1,0,shis.SHIS_YDET_ADULTNO))) from shis where shis.SHIS_YDET_YCAT_ID=wd.YCAT_ID and SHIS.SHIS_YDET_YRCH_ID=wd.yrch_id and shis.SHIS_WDAT_DATE=wd.WDAT_DATE),0)))) 
DEF_NOOFADULTS, 
(decode(wd.FUTURE,1, 
(nvl((select V8_REP_YBLD_OCC_YCATYRCH.DEFNOOFADULTSBOOKINGPOSAVAIL from V8_REP_YBLD_OCC_YCATYRCH where V8_REP_YBLD_OCC_YCATYRCH.YBLD_YCAT_ID=wd.YCAT_ID and V8_REP_YBLD_OCC_YCATYRCH.yrch_id=wd.yrch_id and  V8_REP_YBLD_OCC_YCATYRCH.YBLD_DATE=wd.WDAT_DATE),0)+nvl((select V8_REP_YDET_ADULT_WB_YCATYRCH.DEFADULTNO from V8_REP_YDET_ADULT_WB_YCATYRCH where V8_REP_YDET_ADULT_WB_YCATYRCH.YDET_YCAT_ID=wd.YCAT_ID and V8_REP_YDET_ADULT_WB_YCATYRCH.ydet_yrch_id=wd.yrch_id and V8_REP_YDET_ADULT_WB_YCATYRCH.YDET_DATE=wd.WDAT_DATE),0)), 
(nvl((select sum(decode(shis.SHIS_YRES_RESSTATUS,3,0,decode(shis.SHIS_DEP_ID,1,0,shis.SHIS_YDET_ADULTNO))) from shis where shis.SHIS_YDET_YCAT_ID=wd.YCAT_ID and SHIS.SHIS_YDET_YRCH_ID=wd.yrch_id and shis.SHIS_WDAT_DATE=wd.WDAT_DATE),0))))
DEF_NOOFADULTS_BOOKINGPOS, 
(decode(wd.FUTURE,1, 
(nvl((select V8_REP_YBLD_OCC_YCATYRCH.TENTNOOFADULTS from V8_REP_YBLD_OCC_YCATYRCH where V8_REP_YBLD_OCC_YCATYRCH.YBLD_YCAT_ID=wd.YCAT_ID and V8_REP_YBLD_OCC_YCATYRCH.yrch_id=wd.yrch_id and V8_REP_YBLD_OCC_YCATYRCH.YBLD_DATE=wd.WDAT_DATE),0)+nvl((select V8_REP_YDET_ADULT_WB_YCATYRCH.TENTADULTNO from V8_REP_YDET_ADULT_WB_YCATYRCH where V8_REP_YDET_ADULT_WB_YCATYRCH.YDET_YCAT_ID=wd.YCAT_ID and V8_REP_YDET_ADULT_WB_YCATYRCH.ydet_yrch_id=wd.yrch_id  and V8_REP_YDET_ADULT_WB_YCATYRCH.YDET_DATE=wd.WDAT_DATE),0)), 
0)) TENT_NOOFADULTS,  
(decode(wd.FUTURE,1, 
(nvl((select V8_REP_YBLD_OCC_YCATYRCH.TENTNOOFADULTSBOOKINGPOSAVAIL from V8_REP_YBLD_OCC_YCATYRCH where V8_REP_YBLD_OCC_YCATYRCH.YBLD_YCAT_ID=wd.YCAT_ID and V8_REP_YBLD_OCC_YCATYRCH.yrch_id=wd.yrch_id and V8_REP_YBLD_OCC_YCATYRCH.YBLD_DATE=wd.WDAT_DATE),0)+nvl((select V8_REP_YDET_ADULT_WB_YCATYRCH.TENTADULTNO from V8_REP_YDET_ADULT_WB_YCATYRCH where V8_REP_YDET_ADULT_WB_YCATYRCH.YDET_YCAT_ID=wd.YCAT_ID and V8_REP_YDET_ADULT_WB_YCATYRCH.ydet_yrch_id=wd.yrch_id and V8_REP_YDET_ADULT_WB_YCATYRCH.YDET_DATE=wd.WDAT_DATE),0)), 
0)) TENT_NOOFADULTS_BOOKINGPOS,
--
NVL((DECODE(wd.FUTURE,1, 
(SELECT SUM(yrgl_number*ydet_noofrooms) 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_xcac_id AND YRES.YRES_RESSTATUS=1 AND YDET.YDET_DATE=wd.WDAT_DATE AND YDET.YDET_YCAT_ID=wd.YCAT_ID and ydet.ydet_yrch_id=wd.yrch_id ), 
(NVL((SELECT SUM(DECODE(SHIS.SHIS_YRES_RESSTATUS,3,0,DECODE(SHIS.SHIS_DEP_ID,1,0,SHIS.SHIS_TOTALYRGL_NUMBER ))) FROM SHIS WHERE SHIS.SHIS_YDET_YCAT_ID=wd.YCAT_ID and SHIS.SHIS_YDET_YRCH_ID=wd.yrch_id AND SHIS.SHIS_WDAT_DATE=wd.WDAT_DATE),0)))),0) 
DEF_NOOFKIDS, 
NVL((DECODE(wd.FUTURE,1, 
(SELECT SUM(yrgl_number*ydet_noofrooms) 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_xcac_id AND YRES.YRES_RESSTATUS=0 AND YDET.YDET_DATE=wd.WDAT_DATE AND YDET.YDET_YCAT_ID=wd.YCAT_ID and ydet.ydet_yrch_id=wd.yrch_id ), 
0)),0) TENT_NOOFKIDS, 
--
NVL((SELECT SUM(V8_REP_HS_ARR_ALLDULT_YCATYRCH.DEFEXPADULT) FROM V8_REP_HS_ARR_ALLDULT_YCATYRCH WHERE V8_REP_HS_ARR_ALLDULT_YCATYRCH.YCAT_ID=wd.YCAT_ID and V8_REP_HS_ARR_ALLDULT_YCATYRCH.YRCH_ID=wd.yrch_id and V8_REP_HS_ARR_ALLDULT_YCATYRCH.WDAT_DATE=wd.WDAT_DATE),0) DEF_ARR_ADULTS, 
NVL((SELECT SUM(V8_REP_HS_ARR_ALLDULT_YCATYRCH.TENTEXPADULT) FROM V8_REP_HS_ARR_ALLDULT_YCATYRCH WHERE V8_REP_HS_ARR_ALLDULT_YCATYRCH.YCAT_ID=wd.YCAT_ID AND V8_REP_HS_ARR_ALLDULT_YCATYRCH.YRCH_ID=wd.yrch_id and  V8_REP_HS_ARR_ALLDULT_YCATYRCH.WDAT_DATE=wd.WDAT_DATE),0) TENT_ARR_ADULTS,
NVL((SELECT SUM(V8_REP_HS_ARR_ALLCILD_YCATYRCH.DEFARRCHILD) FROM V8_REP_HS_ARR_ALLCILD_YCATYRCH WHERE V8_REP_HS_ARR_ALLCILD_YCATYRCH.YCAT_ID=wd.YCAT_ID and V8_REP_HS_ARR_ALLCILD_YCATYRCH.yrch_id=wd.yrch_id AND V8_REP_HS_ARR_ALLCILD_YCATYRCH.WDAT_DATE=wd.WDAT_DATE),0) DEF_ARR_KIDS, 
NVL((SELECT SUM(V8_REP_HS_ARR_ALLCILD_YCATYRCH.TENTARRCHILD) FROM V8_REP_HS_ARR_ALLCILD_YCATYRCH WHERE V8_REP_HS_ARR_ALLCILD_YCATYRCH.YCAT_ID=wd.YCAT_ID AND V8_REP_HS_ARR_ALLCILD_YCATYRCH.yrch_id=wd.yrch_id and V8_REP_HS_ARR_ALLCILD_YCATYRCH.WDAT_DATE=wd.WDAT_DATE),0) TENT_ARR_KIDS, 
-- 
(decode(wd.FUTURE,1, 
nvl(V8_REP_YPOS_FC_CATRCH_STATYPE1.DEF_REV_GROSS,0), 
(nvl((select sum(shis.SHIS_GROSSROOMREV) from shis where shis.SHIS_YDET_YCAT_ID=wd.YCAT_ID and SHIS.SHIS_YDET_YRCH_ID=wd.yrch_id and shis.SHIS_WDAT_DATE=wd.WDAT_DATE),0)))) DEF_RM_REV_GROSS, 
(decode(wd.FUTURE,1, 
nvl(V8_REP_YPOS_FC_CATRCH_STATYPE1.DEF_REV_GROSS_BOOKINGPOS ,0), 
(nvl((select sum(shis.SHIS_GROSSROOMREV) from shis where shis.SHIS_YDET_YCAT_ID=wd.YCAT_ID and SHIS.SHIS_YDET_YRCH_ID=wd.yrch_id and shis.SHIS_WDAT_DATE=wd.WDAT_DATE),0)))) DEF_RM_REV_GROSS_BOOKINGPOS,
-- 
(decode(wd.FUTURE,1,nvl(V8_REP_YPOS_FC_CATRCH_STATYPE1.TENT_REV_GROSS,0),0)) TENT_RM_REV_GROSS, 
(decode(wd.FUTURE,1,nvl(V8_REP_YPOS_FC_CATRCH_STATYPE1.TENT_REV_GROSS_BOOKINGPOS,0),0)) TENT_RM_REV_GROSS_BOOKINGPOS, 
-- 
(decode(wd.FUTURE,1, 
nvl(V8_REP_YPOS_FC_CATRCH_STATYPE2.DEF_REV_GROSS,0), 
(nvl((select sum(shis.SHIS_GROSSFBREV) from shis where shis.SHIS_YDET_YCAT_ID=wd.YCAT_ID and SHIS.SHIS_YDET_YRCH_ID=wd.yrch_id and shis.SHIS_WDAT_DATE=wd.WDAT_DATE),0)))) DEF_FB_REV_GROSS, 
(decode(wd.FUTURE,1, 
nvl(V8_REP_YPOS_FC_CATRCH_STATYPE2.DEF_REV_GROSS_BOOKINGPOS,0), 
(nvl((select sum(shis.SHIS_GROSSFBREV) from shis where shis.SHIS_YDET_YCAT_ID=wd.YCAT_ID and SHIS.SHIS_YDET_YRCH_ID=wd.yrch_id and shis.SHIS_WDAT_DATE=wd.WDAT_DATE),0)))) DEF_FB_REV_GROSS_BOOKINGPOS, 
-- 
(decode(wd.FUTURE,1,nvl(V8_REP_YPOS_FC_CATRCH_STATYPE2.TENT_REV_GROSS,0),0)) TENT_FB_REV_GROSS, 
(decode(wd.FUTURE,1,nvl(V8_REP_YPOS_FC_CATRCH_STATYPE2.TENT_REV_GROSS_BOOKINGPOS,0),0)) TENT_FB_REV_GROSS_BOOKINGPOS, 
-- 
(decode(wd.FUTURE,1, 
nvl(V8_REP_YPOS_FC_CATRCH_STATY345.DEF_REV_GROSS,0), 
(nvl((select sum(shis.SHIS_GROSSMINIBARREV+shis.SHIS_GROSSMISCREV+shis.SHIS_GROSSTELEREV ) from shis where shis.SHIS_YDET_YCAT_ID=wd.YCAT_ID and SHIS.SHIS_YDET_YRCH_ID=wd.yrch_id and shis.SHIS_WDAT_DATE=wd.WDAT_DATE),0)))) DEF_OTH_REV_GROSS, 
(decode(wd.FUTURE,1, 
nvl(V8_REP_YPOS_FC_CATRCH_STATY345.DEF_REV_GROSS_BOOKINGPOS,0), 
(nvl((select sum(shis.SHIS_GROSSMINIBARREV+shis.SHIS_GROSSMISCREV+shis.SHIS_GROSSTELEREV ) from shis where shis.SHIS_YDET_YCAT_ID=wd.YCAT_ID and SHIS.SHIS_YDET_YRCH_ID=wd.yrch_id and shis.SHIS_WDAT_DATE=wd.WDAT_DATE),0)))) DEF_OTH_REV_GROSS_BOOKINGPOS,
-- 
(decode(wd.FUTURE,1,nvl(V8_REP_YPOS_FC_CATRCH_STATY345.TENT_REV_GROSS,0),0)) TENT_OTH_REV_GROSS, 
(decode(wd.FUTURE,1,nvl(V8_REP_YPOS_FC_CATRCH_STATY345.TENT_REV_GROSS_BOOKINGPOS,0),0)) TENT_OTH_REV_GROSS_BOOKINGPOS, 
-- 
(decode(wd.FUTURE,1, 
nvl(V8_REP_YPOS_FC_YCATYRCH.DEF_REV_GROSS,0), 
(nvl((select sum(shis.SHIS_GROSSREV) from shis where shis.SHIS_YDET_YCAT_ID=wd.YCAT_ID and SHIS.SHIS_YDET_YRCH_ID=wd.yrch_id and shis.SHIS_WDAT_DATE=wd.WDAT_DATE),0)))) DEF_REV_GROSS, 
(decode(wd.FUTURE,1, 
nvl(V8_REP_YPOS_FC_YCATYRCH.DEF_REV_GROSS_BOOKINGPOS,0), 
(nvl((select sum(shis.SHIS_GROSSREV) from shis where shis.SHIS_YDET_YCAT_ID=wd.YCAT_ID and SHIS.SHIS_YDET_YRCH_ID=wd.yrch_id  and shis.SHIS_WDAT_DATE=wd.WDAT_DATE),0)))) DEF_REV_GROSS_BOOKINGPOS, 
-- 
(decode(wd.FUTURE,1, 
nvl(V8_REP_YPOS_FC_YCATYRCH.TENT_REV_GROSS,0), 
0)) TENT_REV_GROSS,  
(decode(wd.FUTURE,1, 
nvl(V8_REP_YPOS_FC_YCATYRCH.TENT_REV_GROSS_BOOKINGPOS,0), 
0)) TENT_REV_GROSS_BOOKINGPOS,
-- 
(decode(wd.FUTURE,1, 
nvl(V8_REP_YPOS_FC_CATRCH_STATYPE1.DEF_REV_NET,0), 
(nvl((select sum(shis.SHIS_NETROOMREV) from shis where shis.SHIS_YDET_YCAT_ID=wd.YCAT_ID and SHIS.SHIS_YDET_YRCH_ID=wd.yrch_id and shis.SHIS_WDAT_DATE=wd.WDAT_DATE),0)))) DEF_RM_REV_NET, 
(decode(wd.FUTURE,1, 
nvl(V8_REP_YPOS_FC_CATRCH_STATYPE1.DEF_REV_NET_BOOKINGPOS,0), 
(nvl((select sum(shis.SHIS_NETROOMREV) from shis where shis.SHIS_YDET_YCAT_ID=wd.YCAT_ID and SHIS.SHIS_YDET_YRCH_ID=wd.yrch_id and shis.SHIS_WDAT_DATE=wd.WDAT_DATE),0)))) DEF_RM_REV_NET_BOOKINGPOS,
-- 
(decode(wd.FUTURE,1,nvl(V8_REP_YPOS_FC_CATRCH_STATYPE1.TENT_REV_NET,0), 0)) TENT_RM_REV_NET, 
(decode(wd.FUTURE,1,nvl(V8_REP_YPOS_FC_CATRCH_STATYPE1.TENT_REV_NET_BOOKINGPOS,0), 0)) TENT_RM_REV_NET_BOOKINGPOS,
-- 
(decode(wd.FUTURE,1, 
nvl(V8_REP_YPOS_FC_CATRCH_STATYPE2.DEF_REV_NET,0), 
(nvl((select sum(shis.SHIS_NETFBREV) from shis where shis.SHIS_YDET_YCAT_ID=wd.YCAT_ID and SHIS.SHIS_YDET_YRCH_ID=wd.yrch_id and shis.SHIS_WDAT_DATE=wd.WDAT_DATE),0)))) DEF_FB_REV_NET, 
(decode(wd.FUTURE,1, 
nvl(V8_REP_YPOS_FC_CATRCH_STATYPE2.DEF_REV_NET_BOOKINGPOS,0), 
(nvl((select sum(shis.SHIS_NETFBREV) from shis where shis.SHIS_YDET_YCAT_ID=wd.YCAT_ID and SHIS.SHIS_YDET_YRCH_ID=wd.yrch_id and shis.SHIS_WDAT_DATE=wd.WDAT_DATE),0)))) DEF_FB_REV_NET_BOOKINGPOS,
-- 
(decode(wd.FUTURE,1,nvl(V8_REP_YPOS_FC_CATRCH_STATYPE2.TENT_REV_NET,0), 0)) TENT_FB_REV_NET, 
(decode(wd.FUTURE,1,nvl(V8_REP_YPOS_FC_CATRCH_STATYPE2.TENT_REV_NET_BOOKINGPOS,0), 0)) TENT_FB_REV_NET_BOOKINGPOS, 
-- 
(decode(wd.FUTURE,1, 
nvl(V8_REP_YPOS_FC_CATRCH_STATY345.DEF_REV_NET,0), 
(nvl((select sum(shis.SHIS_NETMINIBARREV+shis.SHIS_NETMISCREV+shis.SHIS_NETTELEREV) from shis where shis.SHIS_YDET_YCAT_ID=wd.YCAT_ID and SHIS.SHIS_YDET_YRCH_ID=wd.yrch_id and shis.SHIS_WDAT_DATE=wd.WDAT_DATE),0)))) DEF_OTH_REV_NET, 
(decode(wd.FUTURE,1, 
nvl(V8_REP_YPOS_FC_CATRCH_STATY345.DEF_REV_NET_BOOKINGPOS,0), 
(nvl((select sum(shis.SHIS_NETMINIBARREV+shis.SHIS_NETMISCREV+shis.SHIS_NETTELEREV) from shis where shis.SHIS_YDET_YCAT_ID=wd.YCAT_ID and SHIS.SHIS_YDET_YRCH_ID=wd.yrch_id and shis.SHIS_WDAT_DATE=wd.WDAT_DATE),0)))) DEF_OTH_REV_NET_BOOKINGPOS,
-- 
(decode(wd.FUTURE,1,nvl(V8_REP_YPOS_FC_CATRCH_STATY345.TENT_REV_NET,0), 0)) TENT_OTH_REV_NET, 
(decode(wd.FUTURE,1,nvl(V8_REP_YPOS_FC_CATRCH_STATY345.TENT_REV_NET_BOOKINGPOS,0), 0)) TENT_OTH_REV_NET_BOOKINGPOS, 
-- 
(decode(wd.FUTURE,1, 
nvl(V8_REP_YPOS_FC_YCATYRCH.DEF_REV_NET,0), 
(nvl((select sum(shis.SHIS_NETREV) from shis where shis.SHIS_YDET_YCAT_ID=wd.YCAT_ID and SHIS.SHIS_YDET_YRCH_ID=wd.yrch_id and shis.SHIS_WDAT_DATE=wd.WDAT_DATE),0)))) DEF_REV_NET, 
(decode(wd.FUTURE,1, 
nvl(V8_REP_YPOS_FC_YCATYRCH.DEF_REV_NET_BOOKINGPOS,0), 
(nvl((select sum(shis.SHIS_NETREV) from shis where shis.SHIS_YDET_YCAT_ID=wd.YCAT_ID and SHIS.SHIS_YDET_YRCH_ID=wd.yrch_id and shis.SHIS_WDAT_DATE=wd.WDAT_DATE),0)))) DEF_REV_NET_BOOKINGPOS, 
-- 
(decode(wd.FUTURE,1,nvl(V8_REP_YPOS_FC_YCATYRCH.TENT_REV_NET,0), 0)) TENT_REV_NET, 
(decode(wd.FUTURE,1,nvl(V8_REP_YPOS_FC_YCATYRCH.TENT_REV_NET_BOOKINGPOS,0), 0)) TENT_REV_NET_BOOKINGPOS, 
--  
wd.FUTURE FUTURE 
 FROM 
V8_REP_WDAT_DATE_YCATYRCH wd, 
V8_REP_YPOS_FC_YCATYRCH, 
V8_REP_YPOS_FC_CATRCH_STATYPE1, 
V8_REP_YPOS_FC_CATRCH_STATYPE2, 
V8_REP_YPOS_FC_CATRCH_STATY345 
where 
wd.WDAT_DATE=V8_REP_YPOS_FC_YCATYRCH.YPOS_DATE(+) and 
wd.YCAT_ID  =V8_REP_YPOS_FC_YCATYRCH.YDET_YCAT_ID(+) and 
wd.yrch_ID  =V8_REP_YPOS_FC_YCATYRCH.yrch_ID(+) and 
wd.WDAT_DATE=V8_REP_YPOS_FC_CATRCH_STATYPE1.YPOS_DATE(+) and 
wd.YCAT_ID  =V8_REP_YPOS_FC_CATRCH_STATYPE1.YDET_YCAT_ID(+) and 
wd.yrch_id  =V8_REP_YPOS_FC_CATRCH_STATYPE1.yrch_id(+) and 
wd.WDAT_DATE=V8_REP_YPOS_FC_CATRCH_STATYPE2.YPOS_DATE(+) and 
wd.YCAT_ID  =V8_REP_YPOS_FC_CATRCH_STATYPE2.YDET_YCAT_ID(+) and 
wd.yrch_ID  =V8_REP_YPOS_FC_CATRCH_STATYPE2.yrch_ID(+) and 
wd.WDAT_DATE=V8_REP_YPOS_FC_CATRCH_STATY345.YPOS_DATE(+) and 
wd.YCAT_ID  =V8_REP_YPOS_FC_CATRCH_STATY345.YDET_YCAT_ID(+) and 
wd.yrch_ID  =V8_REP_YPOS_FC_CATRCH_STATY345.yrch_ID(+)
--and wdat_date=to_date('17.11.2014','dd.mm.yyyy')
--and wd.yrch_id=1003 
order by wd.WDAT_DATE;


CREATE OR REPLACE VIEW V8_STAT_SHIS_DAY_COMBI_CAD
AS 
SELECT 
    WDAT_DATE WDAT_DATE,
    SHIS_WPHS_ID,
    REP_XCMS_ID XCMS_ID,
    YCAD_ID,
    (SELECT V8_REP_NAME.NAME FROM V8_REP_NAME WHERE V8_REP_NAME.XCMS_ID = REP_XCMS_ID AND ROWNUM = 1) NAME,
    (SELECT XCCA.XCCA_ID FROM XCCA,XCMS WHERE XCCA.XCCA_ID = XCMS.XCMS_XCCA_ID AND XCMS.XCMS_ID = REP_XCMS_ID AND ROWNUM = 1) XCCA_ID,
    (SELECT XCCA.XCCA_INTERNALCATEGORY FROM XCCA,XCMS WHERE XCCA.XCCA_ID = XCMS.XCMS_XCCA_ID AND XCMS.XCMS_ID = REP_XCMS_ID AND ROWNUM = 1) XCCA_INTERNALCATEGORY,
    (SELECT XCCA.XCCA_SHORTDESC FROM XCCA,XCMS WHERE XCCA.XCCA_ID = XCMS.XCMS_XCCA_ID AND XCMS.XCMS_ID = REP_XCMS_ID AND ROWNUM = 1) XCCA_SHORTDESC,
    (SELECT XCCA.XCCA_LONGDESC FROM XCCA,XCMS WHERE XCCA.XCCA_ID = XCMS.XCMS_XCCA_ID AND XCMS.XCMS_ID = REP_XCMS_ID AND ROWNUM = 1) XCCA_LONGDESC,
    (SELECT XADR.XADR_XCOU_ID FROM XADR WHERE XADR.XADR_PRIMARY = 1 AND XADR.XADR_XCMS_ID = REP_XCMS_ID AND ROWNUM = 1) XADR_XCOU_ID,
    (SELECT XADR.XADR_STREET1 FROM XADR WHERE XADR.XADR_PRIMARY = 1 AND XADR.XADR_XCMS_ID = REP_XCMS_ID AND ROWNUM = 1) XADR_STREET1,
    (SELECT XADR.XADR_STREET2 FROM XADR WHERE XADR.XADR_PRIMARY = 1 AND XADR.XADR_XCMS_ID = REP_XCMS_ID AND ROWNUM = 1) XADR_STREET2,
    (SELECT XADR.XADR_STREET3 FROM XADR WHERE XADR.XADR_PRIMARY = 1 AND XADR.XADR_XCMS_ID = REP_XCMS_ID AND ROWNUM = 1) XADR_STREET3,
    (SELECT XADR.XADR_CITY FROM XADR WHERE XADR.XADR_PRIMARY = 1 AND XADR.XADR_XCMS_ID = REP_XCMS_ID AND ROWNUM = 1) XADR_CITY,
    (SELECT XADR.XADR_ZIP FROM XADR WHERE XADR.XADR_PRIMARY = 1 AND XADR.XADR_XCMS_ID = REP_XCMS_ID AND ROWNUM = 1) XADR_ZIP,
    (SELECT XCOU.XCOU_ISO2 FROM XCOU WHERE XCOU.XCOU_ID = (SELECT XADR.XADR_XCOU_ID FROM XADR WHERE XADR.XADR_PRIMARY = 1 AND XADR.XADR_XCMS_ID = REP_XCMS_ID AND ROWNUM = 1)) XCOU_ISO2,
    (SELECT XCOU.XCOU_LONGDESC FROM XCOU WHERE XCOU.XCOU_ID = (SELECT XADR.XADR_XCOU_ID FROM XADR WHERE XADR.XADR_PRIMARY = 1 AND XADR.XADR_XCMS_ID = REP_XCMS_ID AND ROWNUM = 1)) XCOU_LONGDESC,
    NOOFROOMS NOOFROOMS,
    ADULTNO ADULTNO,
    KIDS KIDS,
    ARRIVALS ARRIVALS,
    DEPARTURES DEPARTURES,
    ARR_ADULTNO ARR_ADULTNO,
    DEP_ADULTNO DEP_ADULTNO,
    ARR_KIDS ARR_KIDS,
    DEP_KIDS DEP_KIDS,
    NOSHOW_ROOM NOSHOW_ROOM,
    CANCEL_ROOM CANCEL_ROOM,
    NETROOMREV NETROOMREV,
    NETFBREV NETFBREV,
    NETMINIBARREV NETMINIBARREV,
    NETTELEREV NETTELEREV,
    NETMISCREV NETMISCREV,
    NETREV NETREV,
    GROSSROOMREV GROSSROOMREV,
    GROSSFBREV GROSSFBREV,
    GROSSMINIBARREV GROSSMINIBARREV,
    GROSSTELEREV GROSSTELEREV,
    GROSSMISCREV GROSSMISCREV,
    GROSSREV GROSSREV 
FROM 
(
    SELECT 
        WDAT_DATE WDAT_DATE,
        SHIS_WPHS_ID,
        XCMS_ID REP_XCMS_ID,
        YCAD_ID,
        nvl(sum(noofrooms),0) noofrooms,
        nvl(sum(adultno),0) adultno,
        nvl(sum(kids),0) kids,
        nvl(sum(arrivals),0) arrivals,
        nvl(sum(departures),0) departures,
        nvl(sum(arr_adultno),0) arr_adultno,
        nvl(sum(dep_adultno),0) dep_adultno,
        nvl(sum(arr_kids),0) arr_kids,
        nvl(sum(dep_kids),0) dep_kids,
        nvl(sum(noshow_room),0) noshow_room,
        nvl(sum(cancel_room),0) cancel_room,
        nvl(sum(netroomrev),0) netroomrev,
        nvl(sum(netfbrev),0) netfbrev,
        nvl(sum(netminibarrev),0) netminibarrev,
        nvl(sum(nettelerev),0) nettelerev,
        nvl(sum(netmiscrev),0) netmiscrev,
        nvl(sum(netrev),0) netrev,
        nvl(sum(grossroomrev),0) grossroomrev,
        nvl(sum(grossfbrev),0) grossfbrev,
        nvl(sum(grossminibarrev),0) grossminibarrev,
        nvl(sum(grosstelerev),0) grosstelerev,
        nvl(sum(grossmiscrev),0) grossmiscrev,
        nvl(sum(grossrev),0) grossrev 
    FROM 
    ( 
        SELECT 
            WDAT.WDAT_DATE WDAT_DATE,
            S.SHIS_WPHS_ID SHIS_WPHS_ID,   
            S.SHIS_YCLN_1_XCMS_ID XCMS_ID,
            YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID YCAD_ID,
            sum(CASE WHEN s.shis_yres_resstatus = 3 OR s.shis_dep_id = 1 AND s.shis_arr_id = 0 THEN 0 ELSE s.shis_ydet_noofrooms END) noofrooms,
            sum(CASE WHEN s.shis_yres_resstatus = 3 OR s.shis_dep_id = 1 AND s.shis_arr_id = 0 THEN 0 ELSE s.shis_ydet_adultno END) adultno,
            sum(CASE WHEN s.shis_yres_resstatus = 3 OR s.shis_dep_id = 1 AND s.shis_arr_id = 0 THEN 0 ELSE nvl(s.shis_totalyrgl_number, 0) END) kids,
            sum(decode(s.shis_arr_id, 1, s.shis_ydet_noofrooms, 0)) arrivals,
            sum(decode(s.shis_dep_id, 1, s.shis_ydet_noofrooms, 0)) departures,
            sum(decode(s.shis_arr_id, 1, s.shis_ydet_adultno, 0)) arr_adultno,
            sum(decode(s.shis_dep_id, 1, s.shis_ydet_adultno, 0)) dep_adultno,
            sum(decode(s.shis_arr_id, 1, nvl(s.shis_totalyrgl_number, 0), 0)) arr_kids,
            sum(decode(s.shis_dep_id, 1, nvl(s.shis_totalyrgl_number, 0), 0)) dep_kids,
            sum(CASE WHEN s.shis_yres_resstatus = 3 AND s.shis_yres_noavailreason = 4 AND trunc(s.shis_yres_exparrtime) = trunc(s.shis_wdat_date) THEN s.shis_ydet_noofrooms ELSE 0 END) noshow_room,
            sum(CASE WHEN s.shis_yres_resstatus = 3 AND s.shis_yres_noavailreason = 3 AND trunc(s.shis_yres_exparrtime) = trunc(s.shis_wdat_date) THEN s.shis_ydet_noofrooms ELSE 0 END) cancel_room,
            sum(s.shis_netroomrev) netroomrev,
            sum(s.shis_netfbrev) netfbrev,
            sum(s.shis_netminibarrev) netminibarrev,
            sum(s.shis_nettelerev) nettelerev,
            sum(s.shis_netmiscrev) netmiscrev,
            sum(s.shis_netrev) netrev,
            sum(s.shis_grossroomrev) grossroomrev,
            sum(s.shis_grossfbrev) grossfbrev,
            sum(s.shis_grossminibarrev) grossminibarrev,
            sum(s.shis_grosstelerev) grosstelerev,
            sum(s.shis_grossmiscrev) grossmiscrev,
            sum(s.shis_grossrev) grossrev
        FROM 
            wdat,
            shis s,
            ycat 
        WHERE 
            wdat.wdat_date = s.shis_wdat_date AND
            nvl(s.shis_ycln_1_xcms_id, 0) <> s.shis_xcms_id AND
            nvl(nvl(S.SHIS_YDET_YCAT_ID,(select ZFAG.ZFAG_YCAD_ID from zfag,zfac where ZFAG.ZFAG_ID=ZFAC.ZFAC_ZFAG_ID and ZFAC.ZFAC_ID=S.SHIS_ZFAC_ID)),(SELECT YCAD.YCAD_ID FROM YCAD WHERE YCAD.YCAD_DEFAULT=1))=YCAT.YCAT_ID AND
            s.shis_ycln_1_xcms_id IS NOT NULL
        GROUP BY
            WDAT.WDAT_DATE, S.SHIS_YCLN_1_XCMS_ID,S.SHIS_WPHS_ID,YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID 
        --
        UNION ALL 
        --
        SELECT 
            WDAT.WDAT_DATE WDAT_DATE,
            S.SHIS_WPHS_ID SHIS_WPHS_ID,  
            S.SHIS_YCLN_2_XCMS_ID XCMS_ID,
            YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID YCAD_ID,
            sum(CASE WHEN s.shis_yres_resstatus = 3 OR s.shis_dep_id = 1 AND s.shis_arr_id = 0 THEN 0 ELSE s.shis_ydet_noofrooms END) noofrooms,
            sum(CASE WHEN s.shis_yres_resstatus = 3 OR s.shis_dep_id = 1 AND s.shis_arr_id = 0 THEN 0 ELSE s.shis_ydet_adultno END) adultno,
            sum(CASE WHEN s.shis_yres_resstatus = 3 OR s.shis_dep_id = 1 AND s.shis_arr_id = 0 THEN 0 ELSE nvl(s.shis_totalyrgl_number, 0) END) kids,
            sum(decode(s.shis_arr_id, 1, s.shis_ydet_noofrooms, 0)) arrivals,
            sum(decode(s.shis_dep_id, 1, s.shis_ydet_noofrooms, 0)) departures,
            sum(decode(s.shis_arr_id, 1, s.shis_ydet_adultno, 0)) arr_adultno,
            sum(decode(s.shis_dep_id, 1, s.shis_ydet_adultno, 0)) dep_adultno,
            sum(decode(s.shis_arr_id, 1, nvl(s.shis_totalyrgl_number, 0), 0)) arr_kids,
            sum(decode(s.shis_dep_id, 1, nvl(s.shis_totalyrgl_number, 0), 0)) dep_kids,
            sum(CASE WHEN s.shis_yres_resstatus = 3 AND s.shis_yres_noavailreason = 4 AND trunc(s.shis_yres_exparrtime) = trunc(s.shis_wdat_date) THEN s.shis_ydet_noofrooms ELSE 0 END) noshow_room,
            sum(CASE WHEN s.shis_yres_resstatus = 3 AND s.shis_yres_noavailreason = 3 AND trunc(s.shis_yres_exparrtime) = trunc(s.shis_wdat_date) THEN s.shis_ydet_noofrooms ELSE 0 END) cancel_room,
            sum(s.shis_netroomrev) netroomrev,
            sum(s.shis_netfbrev) netfbrev,
            sum(s.shis_netminibarrev) netminibarrev,
            sum(s.shis_nettelerev) nettelerev,
            sum(s.shis_netmiscrev) netmiscrev,
            sum(s.shis_netrev) netrev,
            sum(s.shis_grossroomrev) grossroomrev,
            sum(s.shis_grossfbrev) grossfbrev,
            sum(s.shis_grossminibarrev) grossminibarrev,
            sum(s.shis_grosstelerev) grosstelerev,
            sum(s.shis_grossmiscrev) grossmiscrev,
            sum(s.shis_grossrev) grossrev
        FROM 
            wdat,
            shis s,
            ycat 
        WHERE 
            wdat.wdat_date = s.shis_wdat_date AND
            nvl(s.shis_ycln_2_xcms_id, 0) <> s.shis_xcms_id AND
            nvl(nvl(S.SHIS_YDET_YCAT_ID,(select ZFAG.ZFAG_YCAD_ID from zfag,zfac where ZFAG.ZFAG_ID=ZFAC.ZFAC_ZFAG_ID and ZFAC.ZFAC_ID=S.SHIS_ZFAC_ID)),(SELECT YCAD.YCAD_ID FROM YCAD WHERE YCAD.YCAD_DEFAULT=1))=YCAT.YCAT_ID AND
            s.shis_ycln_2_xcms_id IS NOT NULL
        GROUP BY WDAT.WDAT_DATE, S.SHIS_YCLN_2_XCMS_ID ,S.SHIS_WPHS_ID,YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID 
        UNION ALL 
        SELECT 
            WDAT.WDAT_DATE WDAT_DATE,
            S.SHIS_WPHS_ID SHIS_WPHS_ID,
            S.SHIS_YCLN_3_XCMS_ID XCMS_ID,
            YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID YCAD_ID,
            sum(CASE WHEN s.shis_yres_resstatus = 3 OR s.shis_dep_id = 1 AND s.shis_arr_id = 0 THEN 0 ELSE s.shis_ydet_noofrooms END) noofrooms,
            sum(CASE WHEN s.shis_yres_resstatus = 3 OR s.shis_dep_id = 1 AND s.shis_arr_id = 0 THEN 0 ELSE s.shis_ydet_adultno END) adultno,
            sum(CASE WHEN s.shis_yres_resstatus = 3 OR s.shis_dep_id = 1 AND s.shis_arr_id = 0 THEN 0 ELSE nvl(s.shis_totalyrgl_number, 0) END) kids,
            sum(decode(s.shis_arr_id, 1, s.shis_ydet_noofrooms, 0)) arrivals,
            sum(decode(s.shis_dep_id, 1, s.shis_ydet_noofrooms, 0)) departures,
            sum(decode(s.shis_arr_id, 1, s.shis_ydet_adultno, 0)) arr_adultno,
            sum(decode(s.shis_dep_id, 1, s.shis_ydet_adultno, 0)) dep_adultno,
            sum(decode(s.shis_arr_id, 1, nvl(s.shis_totalyrgl_number, 0), 0)) arr_kids,
            sum(decode(s.shis_dep_id, 1, nvl(s.shis_totalyrgl_number, 0), 0)) dep_kids,
            sum(CASE WHEN s.shis_yres_resstatus = 3 AND s.shis_yres_noavailreason = 4 AND trunc(s.shis_yres_exparrtime) = trunc(s.shis_wdat_date) THEN s.shis_ydet_noofrooms ELSE 0 END) noshow_room,
            sum(CASE WHEN s.shis_yres_resstatus = 3 AND s.shis_yres_noavailreason = 3 AND trunc(s.shis_yres_exparrtime) = trunc(s.shis_wdat_date) THEN s.shis_ydet_noofrooms ELSE 0 END) cancel_room,
            sum(s.shis_netroomrev) netroomrev,
            sum(s.shis_netfbrev) netfbrev,
            sum(s.shis_netminibarrev) netminibarrev,
            sum(s.shis_nettelerev) nettelerev,
            sum(s.shis_netmiscrev) netmiscrev,
            sum(s.shis_netrev) netrev,
            sum(s.shis_grossroomrev) grossroomrev,
            sum(s.shis_grossfbrev) grossfbrev,
            sum(s.shis_grossminibarrev) grossminibarrev,
            sum(s.shis_grosstelerev) grosstelerev,
            sum(s.shis_grossmiscrev) grossmiscrev,
            sum(s.shis_grossrev) grossrev
        FROM 
            wdat,
            shis s,
            ycat 
        WHERE 
            wdat.wdat_date = s.shis_wdat_date AND
            nvl(s.shis_ycln_3_xcms_id, 0) <> s.shis_xcms_id AND
            nvl(nvl(S.SHIS_YDET_YCAT_ID,(select ZFAG.ZFAG_YCAD_ID from zfag,zfac where ZFAG.ZFAG_ID=ZFAC.ZFAC_ZFAG_ID and ZFAC.ZFAC_ID=S.SHIS_ZFAC_ID)),(SELECT YCAD.YCAD_ID FROM YCAD WHERE YCAD.YCAD_DEFAULT=1))=YCAT.YCAT_ID AND
            s.shis_ycln_3_xcms_id IS NOT NULL
        GROUP BY WDAT.WDAT_DATE,S.SHIS_YCLN_3_XCMS_ID,S.SHIS_WPHS_ID,YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID  
        UNION ALL 
        SELECT 
            WDAT.WDAT_DATE WDAT_DATE,
            s.SHIS_WPHS_ID SHIS_WPHS_ID,
            S.SHIS_YCLN_4_XCMS_ID XCMS_ID, 
            YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID YCAD_ID,
            sum(CASE WHEN s.shis_yres_resstatus = 3 OR s.shis_dep_id = 1 AND s.shis_arr_id = 0 THEN 0 ELSE s.shis_ydet_noofrooms END) noofrooms,
            sum(CASE WHEN s.shis_yres_resstatus = 3 OR s.shis_dep_id = 1 AND s.shis_arr_id = 0 THEN 0 ELSE s.shis_ydet_adultno END) adultno,
            sum(CASE WHEN s.shis_yres_resstatus = 3 OR s.shis_dep_id = 1 AND s.shis_arr_id = 0 THEN 0 ELSE nvl(s.shis_totalyrgl_number, 0) END) kids,
            sum(decode(s.shis_arr_id, 1, s.shis_ydet_noofrooms, 0)) arrivals,
            sum(decode(s.shis_dep_id, 1, s.shis_ydet_noofrooms, 0)) departures,
            sum(decode(s.shis_arr_id, 1, s.shis_ydet_adultno, 0)) arr_adultno,
            sum(decode(s.shis_dep_id, 1, s.shis_ydet_adultno, 0)) dep_adultno,
            sum(decode(s.shis_arr_id, 1, nvl(s.shis_totalyrgl_number, 0), 0)) arr_kids,
            sum(decode(s.shis_dep_id, 1, nvl(s.shis_totalyrgl_number, 0), 0)) dep_kids,
            sum(CASE WHEN s.shis_yres_resstatus = 3 AND s.shis_yres_noavailreason = 4 AND trunc(s.shis_yres_exparrtime) = trunc(s.shis_wdat_date) THEN s.shis_ydet_noofrooms ELSE 0 END) noshow_room,
            sum(CASE WHEN s.shis_yres_resstatus = 3 AND s.shis_yres_noavailreason = 3 AND trunc(s.shis_yres_exparrtime) = trunc(s.shis_wdat_date) THEN s.shis_ydet_noofrooms ELSE 0 END) cancel_room,
            sum(s.shis_netroomrev) netroomrev,
            sum(s.shis_netfbrev) netfbrev,
            sum(s.shis_netminibarrev) netminibarrev,
            sum(s.shis_nettelerev) nettelerev,
            sum(s.shis_netmiscrev) netmiscrev,
            sum(s.shis_netrev) netrev,
            sum(s.shis_grossroomrev) grossroomrev,
            sum(s.shis_grossfbrev) grossfbrev,
            sum(s.shis_grossminibarrev) grossminibarrev,
            sum(s.shis_grosstelerev) grosstelerev,
            sum(s.shis_grossmiscrev) grossmiscrev,
            sum(s.shis_grossrev) grossrev
        FROM 
            wdat,
            shis s,
            ycat 
        WHERE 
            wdat.wdat_date=s.shis_wdat_date AND
            nvl(s.shis_ycln_4_xcms_id, 0) <> s.shis_xcms_id AND
            nvl(nvl(S.SHIS_YDET_YCAT_ID,(select ZFAG.ZFAG_YCAD_ID from zfag,zfac where ZFAG.ZFAG_ID=ZFAC.ZFAC_ZFAG_ID and ZFAC.ZFAC_ID=S.SHIS_ZFAC_ID)),(SELECT YCAD.YCAD_ID FROM YCAD WHERE YCAD.YCAD_DEFAULT=1))=YCAT.YCAT_ID AND 
            s.shis_ycln_4_xcms_id IS NOT NULL
        GROUP BY WDAT.WDAT_DATE, S.SHIS_YCLN_4_XCMS_ID,S.SHIS_WPHS_ID,YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID  
        UNION ALL 
        SELECT 
            WDAT.WDAT_DATE WDAT_DATE,
            S.SHIS_WPHS_ID SHIS_WPHS_ID,
            S.SHIS_YCLN_5_XCMS_ID XCMS_ID,
            YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID YCAD_ID,
            sum(CASE WHEN s.shis_yres_resstatus = 3 OR s.shis_dep_id = 1 AND s.shis_arr_id = 0 THEN 0 ELSE s.shis_ydet_noofrooms END) noofrooms,
            sum(CASE WHEN s.shis_yres_resstatus = 3 OR s.shis_dep_id = 1 AND s.shis_arr_id = 0 THEN 0 ELSE s.shis_ydet_adultno END) adultno,
            sum(CASE WHEN s.shis_yres_resstatus = 3 OR s.shis_dep_id = 1 AND s.shis_arr_id = 0 THEN 0 ELSE nvl(s.shis_totalyrgl_number, 0) END) kids,
            sum(decode(s.shis_arr_id, 1, s.shis_ydet_noofrooms, 0)) arrivals,
            sum(decode(s.shis_dep_id, 1, s.shis_ydet_noofrooms, 0)) departures,
            sum(decode(s.shis_arr_id, 1, s.shis_ydet_adultno, 0)) arr_adultno,
            sum(decode(s.shis_dep_id, 1, s.shis_ydet_adultno, 0)) dep_adultno,
            sum(decode(s.shis_arr_id, 1, nvl(s.shis_totalyrgl_number, 0), 0)) arr_kids,
            sum(decode(s.shis_dep_id, 1, nvl(s.shis_totalyrgl_number, 0), 0)) dep_kids,
            sum(CASE WHEN s.shis_yres_resstatus = 3 AND s.shis_yres_noavailreason = 4 AND trunc(s.shis_yres_exparrtime) = trunc(s.shis_wdat_date) THEN s.shis_ydet_noofrooms ELSE 0 END) noshow_room,
            sum(CASE WHEN s.shis_yres_resstatus = 3 AND s.shis_yres_noavailreason = 3 AND trunc(s.shis_yres_exparrtime) = trunc(s.shis_wdat_date) THEN s.shis_ydet_noofrooms ELSE 0 END) cancel_room,
            sum(s.shis_netroomrev) netroomrev,
            sum(s.shis_netfbrev) netfbrev,
            sum(s.shis_netminibarrev) netminibarrev,
            sum(s.shis_nettelerev) nettelerev,
            sum(s.shis_netmiscrev) netmiscrev,
            sum(s.shis_netrev) netrev,
            sum(s.shis_grossroomrev) grossroomrev,
            sum(s.shis_grossfbrev) grossfbrev,
            sum(s.shis_grossminibarrev) grossminibarrev,
            sum(s.shis_grosstelerev) grosstelerev,
            sum(s.shis_grossmiscrev) grossmiscrev,
            sum(s.shis_grossrev) grossrev
        FROM 
            wdat,
            shis s,
            ycat 
        WHERE 
            wdat.wdat_date = s.shis_wdat_date AND 
            nvl(s.shis_ycln_5_xcms_id, 0) <> s.shis_xcms_id AND
            nvl(nvl(S.SHIS_YDET_YCAT_ID,(select ZFAG.ZFAG_YCAD_ID from zfag,zfac where ZFAG.ZFAG_ID=ZFAC.ZFAC_ZFAG_ID and ZFAC.ZFAC_ID=S.SHIS_ZFAC_ID)),(SELECT YCAD.YCAD_ID FROM YCAD WHERE YCAD.YCAD_DEFAULT=1))=YCAT.YCAT_ID AND
            s.shis_ycln_5_xcms_id IS NOT NULL 
        GROUP BY WDAT.WDAT_DATE,S.SHIS_YCLN_5_XCMS_ID,S.SHIS_WPHS_ID,YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID  
        UNION ALL 
        SELECT 
            WDAT.WDAT_DATE WDAT_DATE,
            S.SHIS_WPHS_ID SHIS_WPHS_ID,  
            S.SHIS_YCLN_6_XCMS_ID XCMS_ID,
            YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID YCAD_ID,
            sum(CASE WHEN s.shis_yres_resstatus = 3 OR s.shis_dep_id = 1 AND s.shis_arr_id = 0 THEN 0 ELSE s.shis_ydet_noofrooms END) noofrooms,
            sum(CASE WHEN s.shis_yres_resstatus = 3 OR s.shis_dep_id = 1 AND s.shis_arr_id = 0 THEN 0 ELSE s.shis_ydet_adultno END) adultno,
            sum(CASE WHEN s.shis_yres_resstatus = 3 OR s.shis_dep_id = 1 AND s.shis_arr_id = 0 THEN 0 ELSE nvl(s.shis_totalyrgl_number, 0) END) kids,
            sum(decode(s.shis_arr_id, 1, s.shis_ydet_noofrooms, 0)) arrivals,
            sum(decode(s.shis_dep_id, 1, s.shis_ydet_noofrooms, 0)) departures,
            sum(decode(s.shis_arr_id, 1, s.shis_ydet_adultno, 0)) arr_adultno,
            sum(decode(s.shis_dep_id, 1, s.shis_ydet_adultno, 0)) dep_adultno,
            sum(decode(s.shis_arr_id, 1, nvl(s.shis_totalyrgl_number, 0), 0)) arr_kids,
            sum(decode(s.shis_dep_id, 1, nvl(s.shis_totalyrgl_number, 0), 0)) dep_kids,
            sum(CASE WHEN s.shis_yres_resstatus = 3 AND s.shis_yres_noavailreason = 4 AND trunc(s.shis_yres_exparrtime) = trunc(s.shis_wdat_date) THEN s.shis_ydet_noofrooms ELSE 0 END) noshow_room,
            sum(CASE WHEN s.shis_yres_resstatus = 3 AND s.shis_yres_noavailreason = 3 AND trunc(s.shis_yres_exparrtime) = trunc(s.shis_wdat_date) THEN s.shis_ydet_noofrooms ELSE 0 END) cancel_room,
            sum(s.shis_netroomrev) netroomrev,
            sum(s.shis_netfbrev) netfbrev,
            sum(s.shis_netminibarrev) netminibarrev,
            sum(s.shis_nettelerev) nettelerev,
            sum(s.shis_netmiscrev) netmiscrev,
            sum(s.shis_netrev) netrev,
            sum(s.shis_grossroomrev) grossroomrev,
            sum(s.shis_grossfbrev) grossfbrev,
            sum(s.shis_grossminibarrev) grossminibarrev,
            sum(s.shis_grosstelerev) grosstelerev,
            sum(s.shis_grossmiscrev) grossmiscrev,
            sum(s.shis_grossrev) grossrev
        FROM 
            wdat,
            shis s,
            ycat 
        WHERE 
            wdat.wdat_date = s.shis_wdat_date AND
            nvl(s.shis_ycln_6_xcms_id, 0) <> s.shis_xcms_id AND
            nvl(nvl(S.SHIS_YDET_YCAT_ID,(select ZFAG.ZFAG_YCAD_ID from zfag,zfac where ZFAG.ZFAG_ID=ZFAC.ZFAC_ZFAG_ID and ZFAC.ZFAC_ID=S.SHIS_ZFAC_ID)),(SELECT YCAD.YCAD_ID FROM YCAD WHERE YCAD.YCAD_DEFAULT=1))=YCAT.YCAT_ID AND
            s.shis_ycln_6_xcms_id IS NOT NULL
        GROUP BY WDAT.WDAT_DATE,S.SHIS_YCLN_6_XCMS_ID,S.SHIS_WPHS_ID,YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID  
        UNION ALL 
        SELECT 
            WDAT.WDAT_DATE WDAT_DATE,
            S.SHIS_WPHS_ID SHIS_WPHS_ID,  
            S.SHIS_YCLN_7_XCMS_ID XCMS_ID,
            YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID YCAD_ID,
            sum(CASE WHEN s.shis_yres_resstatus = 3 OR s.shis_dep_id = 1 AND s.shis_arr_id = 0 THEN 0 ELSE s.shis_ydet_noofrooms END) noofrooms,
            sum(CASE WHEN s.shis_yres_resstatus = 3 OR s.shis_dep_id = 1 AND s.shis_arr_id = 0 THEN 0 ELSE s.shis_ydet_adultno END) adultno,
            sum(CASE WHEN s.shis_yres_resstatus = 3 OR s.shis_dep_id = 1 AND s.shis_arr_id = 0 THEN 0 ELSE nvl(s.shis_totalyrgl_number, 0) END) kids,
            sum(decode(s.shis_arr_id, 1, s.shis_ydet_noofrooms, 0)) arrivals,
            sum(decode(s.shis_dep_id, 1, s.shis_ydet_noofrooms, 0)) departures,
            sum(decode(s.shis_arr_id, 1, s.shis_ydet_adultno, 0)) arr_adultno,
            sum(decode(s.shis_dep_id, 1, s.shis_ydet_adultno, 0)) dep_adultno,
            sum(decode(s.shis_arr_id, 1, nvl(s.shis_totalyrgl_number, 0), 0)) arr_kids,
            sum(decode(s.shis_dep_id, 1, nvl(s.shis_totalyrgl_number, 0), 0)) dep_kids,
            sum(CASE WHEN s.shis_yres_resstatus = 3 AND s.shis_yres_noavailreason = 4 AND trunc(s.shis_yres_exparrtime) = trunc(s.shis_wdat_date) THEN s.shis_ydet_noofrooms ELSE 0 END) noshow_room,
            sum(CASE WHEN s.shis_yres_resstatus = 3 AND s.shis_yres_noavailreason = 3 AND trunc(s.shis_yres_exparrtime) = trunc(s.shis_wdat_date) THEN s.shis_ydet_noofrooms ELSE 0 END) cancel_room,
            sum(s.shis_netroomrev) netroomrev,
            sum(s.shis_netfbrev) netfbrev,
            sum(s.shis_netminibarrev) netminibarrev,
            sum(s.shis_nettelerev) nettelerev,
            sum(s.shis_netmiscrev) netmiscrev,
            sum(s.shis_netrev) netrev,
            sum(s.shis_grossroomrev) grossroomrev,
            sum(s.shis_grossfbrev) grossfbrev,
            sum(s.shis_grossminibarrev) grossminibarrev,
            sum(s.shis_grosstelerev) grosstelerev,
            sum(s.shis_grossmiscrev) grossmiscrev,
            sum(s.shis_grossrev) grossrev
        FROM 
            wdat,
            shis s,
            ycat 
        WHERE 
            wdat.wdat_date = s.shis_wdat_date AND
            nvl(s.shis_ycln_7_xcms_id, 0) <> s.shis_xcms_id AND
            nvl(nvl(S.SHIS_YDET_YCAT_ID,(select ZFAG.ZFAG_YCAD_ID from zfag,zfac where ZFAG.ZFAG_ID=ZFAC.ZFAC_ZFAG_ID and ZFAC.ZFAC_ID=S.SHIS_ZFAC_ID)),(SELECT YCAD.YCAD_ID FROM YCAD WHERE YCAD.YCAD_DEFAULT=1))=YCAT.YCAT_ID AND
            s.shis_ycln_7_xcms_id IS NOT NULL
        GROUP BY WDAT.WDAT_DATE,S.SHIS_YCLN_7_XCMS_ID,S.SHIS_WPHS_ID,YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID  
        UNION ALL 
 SELECT 
            WDAT.WDAT_DATE WDAT_DATE,
            S.SHIS_WPHS_ID SHIS_WPHS_ID,  
            S.SHIS_XCMS_ID XCMS_ID,
            YCAD_ID YCAD_ID,
            sum(CASE WHEN s.shis_yres_resstatus = 3 OR s.shis_dep_id = 1 AND s.shis_arr_id = 0 THEN 0 ELSE s.shis_ydet_noofrooms END) noofrooms,
            sum(CASE WHEN s.shis_yres_resstatus = 3 OR s.shis_dep_id = 1 AND s.shis_arr_id = 0 THEN 0 ELSE s.shis_ydet_adultno END) adultno,
            sum(CASE WHEN s.shis_yres_resstatus = 3 OR s.shis_dep_id = 1 AND s.shis_arr_id = 0 THEN 0 ELSE nvl(s.shis_totalyrgl_number, 0) END) kids,
            sum(decode(s.shis_arr_id, 1, s.shis_ydet_noofrooms, 0)) arrivals,
            sum(decode(s.shis_dep_id, 1, s.shis_ydet_noofrooms, 0)) departures,
            sum(decode(s.shis_arr_id, 1, s.shis_ydet_adultno, 0)) arr_adultno,
            sum(decode(s.shis_dep_id, 1, s.shis_ydet_adultno, 0)) dep_adultno,
            sum(decode(s.shis_arr_id, 1, nvl(s.shis_totalyrgl_number, 0), 0)) arr_kids,
            sum(decode(s.shis_dep_id, 1, nvl(s.shis_totalyrgl_number, 0), 0)) dep_kids,
            sum(CASE WHEN s.shis_yres_resstatus = 3 AND s.shis_yres_noavailreason = 4 AND trunc(s.shis_yres_exparrtime) = trunc(s.shis_wdat_date) THEN s.shis_ydet_noofrooms ELSE 0 END) noshow_room,
            sum(CASE WHEN s.shis_yres_resstatus = 3 AND s.shis_yres_noavailreason = 3 AND trunc(s.shis_yres_exparrtime) = trunc(s.shis_wdat_date) THEN s.shis_ydet_noofrooms ELSE 0 END) cancel_room,
            sum(s.shis_netroomrev) netroomrev,
            sum(s.shis_netfbrev) netfbrev,
            sum(s.shis_netminibarrev) netminibarrev,
            sum(s.shis_nettelerev) nettelerev,
            sum(s.shis_netmiscrev) netmiscrev,
            sum(s.shis_netrev) netrev,
            sum(s.shis_grossroomrev) grossroomrev,
            sum(s.shis_grossfbrev) grossfbrev,
            sum(s.shis_grossminibarrev) grossminibarrev,
            sum(s.shis_grosstelerev) grosstelerev,
            sum(s.shis_grossmiscrev) grossmiscrev,
            sum(s.shis_grossrev) grossrev
        FROM 
            wdat,
            shis s,
            ycad
        WHERE 
            WDAT.WDAT_DATE = S.SHIS_WDAT_DATE AND 
            nvl2(S.SHIS_YDET_YCAT_ID,(select YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID from ycat where YCAT.YCAT_ID=S.SHIS_YDET_YCAT_ID),nvl((select ZFAG.ZFAG_YCAD_ID from zfag,zfac where ZFAG.ZFAG_ID=ZFAC.ZFAC_ZFAG_ID and ZFAC.ZFAC_ID=S.SHIS_ZFAC_ID),(SELECT YCAD.YCAD_ID FROM YCAD WHERE YCAD.YCAD_DEFAULT=1)))=ycad.ycad_id   AND
            S.SHIS_XCMS_ID IS NOT NULL 
        group by WDAT.WDAT_DATE,S.SHIS_XCMS_ID,S.SHIS_WPHS_ID,YCAD.YCAD_ID
    ) 
    group by
        WDAT_DATE,XCMS_ID,SHIS_WPHS_ID,YCAD_ID 
);

CREATE OR REPLACE FORCE VIEW V8_IFC_ACCOUNTSEARCH 
AS
SELECT 
3 DEST_TYPE,
AACC.AACC_ID DEST_ID,
AACC.AACC_ID RESERVATIONNUMBER,
1 PRIMARY_GUEST,
AACC.AACC_XCMS_ID GUESTNUMBER,
(SELECT v8_rep_name.NAME2 FROM v8_rep_name WHERE v8_rep_name.XCMS_ID = AACC.AACC_XCMS_ID) FORMATTEDNAME,
(SELECT XCMS.XCMS_NAME1 FROM XCMS WHERE XCMS.XCMS_ID = AACC.AACC_XCMS_ID) LASTNAME,
(SELECT UPPER (XCMS.XCMS_NAME1) FROM XCMS WHERE XCMS.XCMS_ID = AACC.AACC_XCMS_ID) UPLASTNAME,
(SELECT XCMS.XCMS_NAME3 FROM XCMS WHERE XCMS.XCMS_ID = AACC.AACC_XCMS_ID) FIRSTNAME,
(SELECT XCID.XCID_ADDRGREET FROM XCID WHERE XCID.XCID_ID = AACC.AACC_XCMS_ID) TITLE,
(SELECT XCID.XCID_TITLE FROM XCID WHERE XCID.XCID_ID = AACC.AACC_XCMS_ID) FREETITLE,
NVL(AACC.AACC_NUMBER, AACC.AACC_ID) ROOMNUMBER,
(SELECT ATYP.ATYP_SHORTDESC FROM ATYP WHERE ATYP.ATYP_ID = AACC.AACC_ATYP_ID) ROOMTYPE,
NULL ycatid,
TO_DATE (NULL) ARRIVALDATE,
TO_DATE (NULL) DEPARTUREDATE,
TO_DATE (NULL) ARRIVALTIME,
TO_DATE (NULL) DEPARTURETIME,
TO_NUMBER (NULL) SHARENUMBER,
0 NOPOSTFLAG,
0 ADULTS,
0 KIDS,
(SELECT WLAN.WLAN_SHORTDESC FROM WLAN WHERE WLAN.WLAN_ID = (SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID = AACC.AACC_XCMS_ID)) LANGUAGE,
NULL GROUPID,
'' GROUPNAME,
1 GUESTRESSTATUS,
NVL((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 = AACC.AACC_XCMS_ID AND ROWNUM = 1),'')
VIP,
'' MARKETCODE,
'' REMARK1,
'' REMARK2,
'' PAYMETHOD,
0 MESSAGES,
'' RATECODE,
'' COMPANY,
'' TRAVELAGENTNAME,
'' CRS_SYSTEM,
'' CRS_NUMBER,
AACC.AACC_CREDITLIMIT CREDITLIMIT,
'' ZTRA_NUMBER
FROM AACC
WHERE AACC.AACC_OBSOLETE = 0;

CREATE OR REPLACE VIEW V8_CCM_FS_PACKAGE
AS 
SELECT ypkd.YPKD_ID YPKD_ID,
          ypkd.ypkd_ybom_id YPKD_YBOM_ID,
          ypkd.ypkd_name YPKD_NAME,
          ypkd.ypkd_noofattendees YPKD_NOOFATTENDEES,
          ypkd.ypkd_starttime YPKD_STARTTIME,
          ypkd.YPKD_ENDTIME YPKD_ENDTIME,
          NVL (ypkd.YPKD_ALTERNATIVE, 0) YPKD_ALTERNATIVE,
          (SELECT ybom.YBOM_BASE_ZCUR_ID
             FROM ybom
            WHERE ybom.ybom_id = ypkd.YPKD_YBOM_ID)
             ybom_base_zcur_id,
          (SELECT zcur.zcur_shortdesc
             FROM ybom, zcur
            WHERE     ybom.ybom_id = ypkd.YPKD_YBOM_ID
                  AND ybom.ybom_base_zcur_id = zcur.zcur_id)
             ZCUR_SHORTDESC,
          ypkd.YPKD_YPKG_ID YPKG_ID,
          (SELECT ypkg.YPKG_NAME
             FROM ypkg
            WHERE ypkg.ypkg_id = ypkd.YPKD_YPKG_ID)
             YPKG_NAME,
          ypkd_displorder YPKD_DISPLORDER,
          yevm.
          YEVM_ID,
          yevm.yevm_name,
          yevm.YEVM_STARTTIME,
          yevm.YEVM_ENDTIME,
          yevm.YEVM_NOOFATTENDEES - NVL((select ym.merged_yevm_noofattendees from v8_ccm_yevm_mergedattendees ym where YM.YEVM_ID=yevm.yevm_id) , 0)  YEVM_NOOFATTENDEES,
          ybef.YBEF_ZDCO_ID YBEF_ZDCO_ID,
          (SELECT zdco.ZDCO_LONGDESC
             FROM zdco
            WHERE zdco.zdco_id = ybef.YBEF_ZDCO_ID)
             ZDCO_LONGDESC,
          nvl(ybef.YBEF_REV_AMOUNT,0) YBEF_REV_AMOUNT,
          nvl(ypkd.ypkd_price,0) YPKD_PRICE
     FROM ypkd, YEVM, YBEF    
    WHERE ypkd.YPKD_ID = yevm.yevm_YPKD_ID and
          yevm.yevm_id=ybef.ybef_yevm_id(+);

-- View used in Booking Forecast. Forecast and posted for individual and group linked reservations. (No blocks).
CREATE OR REPLACE VIEW V8_STAT_FO_FORREV
AS 
select 0 typ, yres_id, yres_ybom_id ybom_id, ydet_date ypos_date,
  (select sum(ypos_unitprice*ypos_quantity) from ypos where ypos_yres_id=yresindividual.yres_id and ypos_date=ydet_date) rate,
  (select sum(ypos_unitprice*ypos_quantity) from ypos where ypos_yres_id=yresindividual.yres_id and ypos_date=ydet_date and ypos_dt=1) ratenet,
  (select sum(ypos_unitprice*ypos_quantity) from ypos where ypos_yres_id=yresindividual.yres_id and ypos_date=ydet_date and ypos_dt=2) ratetax,
  0 posted, 0 postednet, 0 postedtax
from 
  (  
  select yres_id, yres_ybom_id
  from yres
  where yres_ybom_id>0 and nvl(yres_resstatus, 0)<>3 and nvl((select count(1) from ydet where ydet_yres_id=yres_id and  nvl(ydet_ybld_id,0)>0 ), 0)=0
  ) yresindividual, ydet
where yresindividual.yres_id = ydet.ydet_yres_id
union all
-- Posted Revenue.(from history shis)
select 1, shis_yres_id, shis_ybom_id, shis_wdat_date, 
  0 rate, 
  0 ratenet, 
  0 ratetax, 
  shis_grossrev posted, 
  shis.shis_netrev, 
  shis_grossrev - shis_netrev 
from shis, wgbs 
where shis_yres_id>0 and shis_wdat_date<wgbs.wgbs_datevalue and nvl(shis_yblh_id, 0)=0 and nvl(shis_yres_resstatus, 0)<>3 
union all
-- Today's Posted revenue from linked reservation
select distinct 2, yres_id, yres_ybom_id, z.zpos_postdate, 
  0 rate, 
  0 ratenet,
  0 ratetax,
  (select sum(zpos_unitprice*zpos_quantity) from zpos where zpos.zpos_yres_id=yresindividual.yres_id and zpos.zpos_postdate=z.zpos_postdate) posted,
  (select sum(zpos_unitprice*zpos_quantity) from zpos where zpos.zpos_yres_id=yresindividual.yres_id and zpos.zpos_postdate=z.zpos_postdate and zpos.zpos_cdt=1) postednet,
  (select sum(zpos_unitprice*zpos_quantity) from zpos where zpos.zpos_yres_id=yresindividual.yres_id and zpos.zpos_postdate=z.zpos_postdate and zpos.zpos_cdt=2) postedtax
from zpos z,
    (
  -- All linked reservations to booking excempt blocks.
  select yres_id, yres_ybom_id
  from yres
  where yres_ybom_id>0 and nvl(yres_resstatus, 0)<>3 and nvl((select count(1) from ydet where ydet_yres_id=yres_id and  nvl(ydet_ybld_id,0)>0 ), 0)=0
   ) yresindividual,wgbs     
where z.zpos_yres_id=yresindividual.yres_id and z.zpos_cdt<>5 and z.zpos_postdate = wgbs.wgbs_datevalue
union all
-- Today's Posted revenue from linked reservations transferred to FA.
select distinct 3, yres_id, yres_ybom_id, z.zpos_postdate, 
  0 rate, 
  0 ratenet,
  0 ratetax,
  (select sum(zpos_unitprice*zpos_quantity) from zpos where ZPOS.ZPOS_ORIGINATED_YRES_ID=yresindividual.yres_id and zpos.zpos_postdate=z.zpos_postdate) posted,
  (select sum(zpos_unitprice*zpos_quantity) from zpos where zpos.zpos_originated_yres_id=yresindividual.yres_id and zpos.zpos_postdate=z.zpos_postdate and zpos.zpos_cdt=1) postednet,
  (select sum(zpos_unitprice*zpos_quantity) from zpos where zpos.zpos_originated_yres_id=yresindividual.yres_id and zpos.zpos_postdate=z.zpos_postdate and zpos.zpos_cdt=2) postedtax
from zpos z,
    (
 --All linked reservations to booking excempt blocks.
  select yres_id, yres_ybom_id
  from yres
  where yres_ybom_id>0 and nvl(yres_resstatus, 0)<>3 and nvl((select count(1) from ydet where ydet_yres_id=yres_id and  nvl(ydet_ybld_id,0)>0 ), 0)=0
   ) yresindividual,wgbs 
where z.zpos_originated_yres_id=yresindividual.yres_id and z.zpos_cdt<>5 and z.zpos_zfac_id <>0 and z.zpos_postdate = wgbs.wgbs_datevalue ;

create or replace view V8_CCM_ROOMS_IND
as
SELECT v8_editor_res.bookingmaster_id ybom_id,
          v8_editor_res.RESERVATION_ID yres_id,
          v8_editor_res.Arrival Arrival,
          v8_editor_res.departure Departure,
          v8_editor_res.NOOFROOMS noofrooms,
          v8_editor_res.ADULTNO adultno,
          v8_editor_res.ROOMTYPE_ID roomtype_id,
          v8_editor_res.ROOMTYPE_DESC roomtype_desc,
          v8_editor_res.RESSTATUS resstatus,
          v8_editor_res.CONF_RATE conf_rate,
          v8_editor_res.CONF_RATE_STAY conf_rate_stay,
          v8_editor_res.RATE_NET Conf_Rate_Net,
          v8_editor_res.RATE_NET_STAY Conf_Rate_Net_stay,
          V8_EDITOR_RES.CALC_PRICE calc_price,
          V8_EDITOR_RES.CURRENCY,
		  v8_editor_res.YRES_CONFIRMATIONNO YRES_CONFIRMATIONNO
     FROM v8_editor_res
    WHERE     v8_editor_res.resstatus <> 'NOT'
          AND NVL (v8_editor_res.BLOCK_id, 0) = 0
          AND NVL (v8_editor_res.GROUP_ID, 0) = 0;

-- View used in Booking Forecast. Forecast and posted for block reservations.
CREATE OR REPLACE VIEW V8_CCM_ROOMS_BLOCK_PERDAY
AS
   -- Forecast.
   SELECT typ,
          ybom_id,
          ybld_date,
          conf_rate * noofrooms rate,
          conf_rate_net * noofrooms ratenet,
          noofrooms * (conf_rate - conf_rate_net) ratetax,
          0 posted,
          0 postednet,
          0 postedtax
     FROM v8_ccm_rooms_block_npu
   UNION ALL
   SELECT typ,
          ybom_id,
          ybld_date,
          conf_rate * noofrooms rate,
          conf_rate_net * noofrooms ratenet,
          noofrooms * (conf_rate - conf_rate_net) ratetax,
          0 posted,
          0 postednet,
          0 postedtax
     FROM v8_ccm_rooms_block_pu_det
   UNION ALL
-- Posted revenue.
   -- From History 
   SELECT 3 typ,
          shis_ybom_id,
          shis_wdat_date,
          0 rate,
          0 ratenet,
          0 ratetax,
          shis_grossrev posted,
          shis_netrev postednet,
          shis_grossrev - shis_netrev postedtax
     FROM shis, wgbs
    WHERE     shis_yres_id > 0
          AND shis_wdat_date < wgbs.wgbs_datevalue
          AND NVL (shis_yblh_id, 0) > 0
          AND NVL (shis_yres_resstatus, 0) <> 3
   UNION ALL
   -- Revenue posted on linked block reservations.        
   SELECT 4 typ,
          yres_ybom_id,
          zpos_postdate,
          0 rate,
          0 ratenet,
          0 ratetax,
          zpos_grossunitprice * zpos_quantity posted,
          zpos_unitprice * zpos_quantity PostedNet,
          (SELECT SUM (z.zpos_unitprice * z.zpos_quantity)
             FROM zpos z
            WHERE z.zpos_taxlink_id = zpos.zpos_taxlink_id AND z.zpos_cdt = 2)
             PostedTax
     FROM zpos,
          (SELECT yres_id,
                  yres_ybom_id,
                  (SELECT COUNT (ydet_id)
                     FROM ydet
                    WHERE     ydet_yres_id = yres_id
                          AND NVL (ydet_ybld_id, 0) > 0)
                     linkedblocks
             FROM yres
            WHERE yres_ybom_id > 0 AND NVL (yres_resstatus, 0) <> 3)
          yresindividual,
          wgbs
    WHERE     zpos.zpos_postdate = wgbs.wgbs_datevalue
          AND zpos.zpos_yres_id = yresindividual.yres_id
          AND yresindividual.linkedblocks > 0
          AND zpos.zpos_cdt <> 5
   UNION ALL
   -- Revenue from linked block reservations but transfered to FA
   SELECT 4 typ,
          yres_ybom_id,
          zpos_postdate,
          0 rate,
          0 ratenet,
          0 ratetax,
          zpos_grossunitprice * zpos_quantity posted,
          zpos_unitprice * zpos_quantity PostedNet,
          (SELECT SUM (z.zpos_unitprice * z.zpos_quantity)
             FROM zpos z
            WHERE z.zpos_taxlink_id = zpos.zpos_taxlink_id AND z.zpos_cdt = 2)
             PostedTax
     FROM zpos,
          (SELECT yres_id,
                  yres_ybom_id,
                  ybom_zfac_id,
                  (SELECT COUNT (ydet_id)
                     FROM ydet
                    WHERE     ydet_yres_id = yres_id
                          AND NVL (ydet_ybld_id, 0) > 0)
                     linkedblocks
             FROM yres, ybom
            WHERE yres_ybom_id = ybom_id AND NVL (yres_resstatus, 0) <> 3)
          yresindividual,
          wgbs
    WHERE     zpos.zpos_postdate = wgbs.wgbs_datevalue
          AND zpos.zpos_originated_yres_id = yresindividual.yres_id
          AND zpos.zpos_zfac_id <>0
          AND yresindividual.linkedblocks > 0
          AND zpos.zpos_cdt <> 5;

---- View used in Booking Forecast. Manual postings on linked financial account
create or replace view v8_stat_ccm_manualpostings as
select ybom_id, zdco_id, zdco_numericdesc, zdco_longdesc,  
  zpos_grossunitprice*zpos_quantity Posted, 
  zpos_unitprice*zpos_quantity PostedNet, 
  (select sum(z.zpos_unitprice*z.zpos_quantity) from zpos z where z.zpos_taxlink_id=zpos.zpos_taxlink_id and z.zpos_cdt=2 ) PostedTax
from ybom, zpos, zdco 
where ybom_zfac_id=zpos_zfac_id and zpos_cdt<>5 and nvl(ybom_zfac_id, 0)>0 and zpos_zdco_id=zdco_id 
  and zpos_id not in
  (
    select zpos_id from zpos
    where zpos_originated_yres_id in (select yres_id from yres where yres_ybom_id=ybom_id)
  ) 
and nvl(zpos_ztcd_id, 0)=0;
---- View used in Booking Forecast. Manual postings on linked financial account
create or replace view v8_stat_ccm_manualpostings as
select ybom_id, zdco_id, zdco_numericdesc, zdco_longdesc,  
  zpos_grossunitprice*zpos_quantity Posted, 
  zpos_unitprice*zpos_quantity PostedNet, 
  (select sum(z.zpos_unitprice*z.zpos_quantity) from zpos z where z.zpos_taxlink_id=zpos.zpos_taxlink_id and z.zpos_cdt=2 ) PostedTax
from ybom, zpos, zdco 
where ybom_zfac_id=zpos_zfac_id and zpos_cdt<>5 and nvl(ybom_zfac_id, 0)>0 and zpos_zdco_id=zdco_id 
  and zpos_id not in
  (
    select zpos_id from zpos
    where zpos_originated_yres_id in (select yres_id from yres where yres_ybom_id=ybom_id)
  ) 
and nvl(zpos_ztcd_id, 0)=0;

create or replace view v8_ccm_web_translations as
select 0 ResourceType, 'Event Type' ResourceTypeName, yety_ycwi_id ycwi_id, yety_longdesc ResourceName from yety where yety_ycwi_id > 0
union all
select 1 ResourceType, 'Function Spaces', yfsc_ycwi_id ycwi_id, yfsc_longdesc ResourceName from yfsc where yfsc_ycwi_id > 0
union all
select 2 ResourceType, 'Function Space | Rates', yfsr_ycwi_id ycwi_id, (select yfsc_longdesc from yfsc where yfsc_id=yfsr_yfsc_id)||' | '||yfrd_longdesc ResourceName from yfsr, yfrd where yfsr_yfrd_id=yfrd_id and yfsr_ycwi_id > 0
union all
select 3 ResourceType, 'Function Space | Seattings', yssl_ycwi_id ycwi_id, (select yfsc_longdesc from yfsc where yfsc_id=yssl_yfsc_id)||' | '||yssc_longdesc ResourceName from yssl, yssc where yssl_yssc_id=yssc_id and yssl_ycwi_id > 0
union all
select 4 ResourceType, 'Catering Items', ycit_ycwi_id ycwi_id, ycit_longdesc ResourceName from ycit where ycit_ycwi_id > 0
union all
select 5 ResourceType, 'Catering Items | Rates', ycir_ycwi_id ycwi_id, (select ycit_longdesc from ycit where ycit_id=ycir_ycit)||' | '||ycrd_longdesc ResourceName from ycir, ycrd where ycir_ycrd_id=ycrd_id and ycir_ycwi_id > 0
union all
select 6 ResourceType, 'Standard Menu', ysme_ycwi_id ycwi_id, ysme_longdesc ResourceName from ysme where ysme_ycwi_id > 0
union all
select 7 ResourceType, 'Standard Menu | Outlets', ysmr_ycwi_id ycwi_id, (select ysme_longdesc from ysme where ysme_id=ysmr_ysme_id)||' | '||decode(yout_longdesc, null, '<General>', yout_longdesc) ResourceName from ysmr, yout where ysmr_yout_id=yout_id(+) and ysmr_ycwi_id > 0
union all
select 8 ResourceType, 'Standard Menu Items', ycmi_ycwi_id ycwi_id, ycmi_longdesc ResourceName from ycmi where ycmi_ycwi_id > 0
union all
select 9 ResourceType, 'Standard Menu Items | Rates', ycmr_ycwi_id ycwi_id, (select ycmi_longdesc from ycmi where ycmi_id=ycmr_ycmi_id)||' | '||decode(yout_longdesc, null, '<General>', yout_longdesc) ResourceName from ycmr, yout where ycmr_yout_id=yout_id(+) and ycmr_ycwi_id > 0 
union all
select 10 ResourceType, 'Packages', ypkg_ycwi_id ycwi_id, ypkg_name ResourceName from ypkg where ypkg_ycwi_id > 0
union all
select 11 ResourceType, 'Function Type', yfty_ycwi_id ycwi_id, yfty_longdesc ResourceName from yfty where yfty_ycwi_id > 0;  


