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

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 VIEW v8_rep_ThaiTaxSummary_1
(Zttl_id, Zttl_type, Zttl_form, ThaiTaxInvoiceNumber, PaymentAmount, VatableAmount, NonVatAmount, 
 AmountBeforeVat, VatPaid, ProvincialTaxPaid, PaymentType, PaymentTime, zwin_id)
AS
 SELECT 
    zttl_id,
    zttl_type,
    nvl(zttl_form,0),
    zttl_taxnumber ThaiTaxInvoiceNumber,
    (SELECT zpos_sumupamount FROM zpos WHERE zpos_id = zttl_paym_zpos_id) PaymentAmount,
    zttl_vatableamount VatableAmount,
    zttl_nonvatamount NonVatAmount,
    zttl_amountbeforevat AmountBeforeVat,
    ROUND(zttl_taxallocationvat, 2) VatPaid,
    ROUND(zttl_taxallocationprov, 2) ProvincialTaxPaid,
    (SELECT zdco_numericdesc||'-'||zdco_longdesc 
       FROM zpos, zdco 
      WHERE zpos_id = zttl_paym_zpos_id
        AND zpos_zdco_id = zdco_id) PaymentType,
    zttl_timestamp PaymentTime,
    (SELECT zpos_zwin_id FROM zpos WHERE zpos_id = zttl_paym_zpos_id) zwin_id
 FROM zttl outer_zttl
   ;
   
CREATE OR REPLACE VIEW v8_rep_ThaiTaxSummary_2
(Zttl_id, Zttl_type, Zttl_form, ThaiTaxInvoiceNumber, PaymentAmount, VatableAmount, NonVatAmount, 
 AmountBeforeVat, VatPaid, ProvincialTaxPaid, PaymentType, PaymentTime)
AS
SELECT 
    Zttl_id, Zttl_type, Zttl_form, ThaiTaxInvoiceNumber, PaymentAmount, VatableAmount, NonVatAmount, 
    AmountBeforeVat, VatPaid, ProvincialTaxPaid, PaymentType, PaymentTime  
FROM
    v8_rep_ThaiTaxSummary_1;   
   
CREATE OR REPLACE VIEW v8_rep_ThaiTaxSummary
(Zttl_id, Zttl_type, Zttl_form, ThaiTaxInvoiceNumber, PaymentAmount, VatableAmount, NonVatAmount, 
 AmountBeforeVat, VatPaid, ProvincialTaxPaid, PaymentType, PaymentTime, ReferenceNumber, 
 PaymentAmountText, VatableAmountText, NonVatAmountText, AmountBeforeVatText, VatPaidText, 
 ProvincialTaxPaidText)
AS
(
select 
    TTMain.Zttl_id,
    TTMain.Zttl_type,
    TTMain.Zttl_form,
    TTMain.ThaiTaxInvoiceNumber,
    TTMain.PaymentAmount,
    NVL(TTMain.VatableAmount, 0) VatableAmount,
    NVL(TTMain.NonVatAmount, 0) NonVatAmount,
    NVL(TTMain.AmountBeforeVat, 0) AmountBeforeVat,
    NVL(TTMain.VatPaid, 0) VatPaid,
    NVL(TTMain.ProvincialTaxPaid, 0) ProvincialTaxPaid,
    TTMain.PaymentType,
    TTMain.PaymentTime, 
    AGGREGATE_FIELD('SELECT ThaiTaxInvoiceNumber FROM v8_rep_ThaiTaxSummary_1, zttx WHERE zttx_cn_zttl_id = Zttl_id AND zttx_zttl_id =' || TO_CHAR(NVL(TTMain.Zttl_ID, 0)), ',' ) ReferenceNumber,
    TRIM(TO_CHAR(TTMain.PaymentAmount,'999G999G9990D99', 'NLS_NUMERIC_CHARACTERS = ''.,''')) PaymentAmountText,
    TRIM(TO_CHAR(
       NVL(TTMain.VatableAmount, 0)
       ,'999G999G9990D99', 'NLS_NUMERIC_CHARACTERS = ''.,''')) VatableAmountText,
    TRIM(TO_CHAR(
       NVL(TTMain.NonVatAmount, 0)
       ,'999G999G9990D99', 'NLS_NUMERIC_CHARACTERS = ''.,''')) NonVatAmountText,
    TRIM(TO_CHAR(
       NVL(TTMain.AmountBeforeVat, 0)
       ,'999G999G9990D99', 'NLS_NUMERIC_CHARACTERS = ''.,''')) AmountBeforeVatText,
    TRIM(TO_CHAR(
       NVL(TTMain.VatPaid, 0)
       ,'999G999G9990D99', 'NLS_NUMERIC_CHARACTERS = ''.,''')) VatPaidText,
    TRIM(TO_CHAR(
       NVL(TTMain.ProvincialTaxPaid, 0)
       ,'999G999G9990D99', 'NLS_NUMERIC_CHARACTERS = ''.,''')) ProvincialTaxPaidText
from v8_rep_ThaiTaxSummary_1 TTMain
); 

CREATE OR REPLACE FORCE VIEW V8_REP_YRMS_MOBILE_INFOS
AS
   SELECT YRMS_ID,
          YRMS_SHORTDESC,
          YRMS_LONGDESC,
          YRMS_YCAT_ID,
          YRMS_FLOOR,
          (SELECT ycat.YCAT_SHORTDESC
             FROM ycat
            WHERE ycat.YCAT_ID = yrms.YRMS_YCAT_ID)
             YCAT_SHORTDESC,
          (SELECT ycat.YCAT_LONGDESC
             FROM ycat
            WHERE ycat.YCAT_ID = yrms.YRMS_YCAT_ID)
             YCAT_LONGDESC,
          V8_sys_fideliodate FIDELIO_DATE,
          yrms.YRMS_YHKS_ID YRMS_YHKS_ID,
          (SELECT yhks.YHKS_SHORTDESC
             FROM yhks
            WHERE yhks.YHKS_ID = yrms.YRMS_YHKS_ID)
             YHKS_SHORTDESC,
          (SELECT yhks.YHKS_LONGDESC
             FROM yhks
            WHERE yhks.YHKS_ID = yrms.YRMS_YHKS_ID)
             YHKS_LONGDESC,
          DECODE (
             DECODE (
                (SELECT yrst.YRST_ID
                   FROM yrst
                  WHERE     V8_sys_fideliodate = yrst.YRST_FROMTIME
                        AND yrst.YRST_YRMS_ID = yrms.YRMS_ID),
                NULL, (SELECT yrst.YRST_ID
                         FROM yrst
                        WHERE     (V8_sys_fideliodate BETWEEN yrst.YRST_FROMTIME
                                                          AND   yrst.YRST_UNTILTIME
                                                              - 1)
                              AND yrst.YRST_YRMS_ID = yrms.YRMS_ID
                              AND ROWNUM = 1),
                ( (SELECT yrst.YRST_ID
                     FROM yrst
                    WHERE     V8_sys_fideliodate = yrst.YRST_FROMTIME
                          AND yrst.YRST_YRMS_ID = yrms.YRMS_ID))),
             NULL, 0,
             1)
             IS_OOO,
          DECODE (
             (SELECT yrst.YRST_ID
                FROM yrst
               WHERE     V8_sys_fideliodate = yrst.YRST_FROMTIME
                     AND yrst.YRST_YRMS_ID = yrms.YRMS_ID),
             NULL, (DECODE (
                       (SELECT yrst.YRST_ID
                          FROM yrst
                         WHERE     (V8_sys_fideliodate BETWEEN yrst.YRST_FROMTIME
                                                           AND   yrst.YRST_UNTILTIME
                                                               - 1)
                               AND yrst.YRST_YRMS_ID = yrms.YRMS_ID
                               AND ROWNUM = 1),
                       NULL, (SELECT yhks.YHKS_SHORTDESC
                                FROM yhks
                               WHERE yhks.YHKS_ID = yrms.YRMS_YHKS_ID),
                       (SELECT yrsc.YRSC_SHORTDESC
                          FROM yrsc, yrst
                         WHERE     yrsc.YRSC_ID = yrst.YRST_YRSC_ID
                               AND (V8_sys_fideliodate BETWEEN yrst.YRST_FROMTIME
                                                           AND   yrst.YRST_UNTILTIME
                                                               - 1)
                               AND yrst.YRST_YRMS_ID = yrms.YRMS_ID
                               AND ROWNUM = 1))),
             (SELECT yrsc.YRSC_SHORTDESC
                FROM yrsc, yrst
               WHERE     yrsc.YRSC_ID = yrst.YRST_YRSC_ID
                     AND V8_sys_fideliodate = yrst.YRST_FROMTIME
                     AND yrst.YRST_YRMS_ID = yrms.YRMS_ID))
             ROOM_STATUS,
          (SELECT yres.YRES_ID
             FROM yres
            WHERE     V8_sys_fideliodate BETWEEN (YRES.YRES_EXPARRTIME)
                                             AND (YRES.YRES_EXPDEPTIME)
                  AND yres.YRES_YRMS_ID = yrms.YRMS_ID
                  AND yres.YRES_RESSTATUS <> 3
                  AND TRUNC (yres.YRES_ACTARRTIME) <> V8_sys_fideliodate
                  AND ROWNUM = 1)
             STAY_OVER_YRES_ID,
          DECODE (
             (SELECT yres.YRES_ID
                FROM yres
               WHERE     V8_sys_fideliodate BETWEEN (YRES.YRES_EXPARRTIME)
                                                AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = yrms.YRMS_ID
                     AND yres.YRES_RESSTATUS <> 3
                     AND TRUNC (yres.YRES_ACTARRTIME) <> V8_sys_fideliodate
                     AND ROWNUM = 1),
             NULL, 0,
             1)
             STAY_OVER,
          (SELECT yres.YRES_ID
             FROM yres, ydet
            WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                  AND ydet.YDET_DATE + 1 = V8_sys_fideliodate
                  AND ydet.YDET_YRMS_ID = yrms.YRMS_ID
                  AND ROWNUM = 1)
             DEP_YRES_ID,
          DECODE (
             (SELECT yres.YRES_ID
                FROM yres, ydet
               WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                     AND ydet.YDET_DATE + 1 = V8_sys_fideliodate
                     AND ydet.YDET_YRMS_ID = yrms.YRMS_ID
                     AND ROWNUM = 1),
             NULL, 0,
             1)
             DEPARTURE,
          (SELECT yres.YRES_ACTDEPTIMENULL
             FROM yres, ydet
            WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                  AND ydet.YDET_DATE + 1 = V8_sys_fideliodate
                  AND ydet.YDET_YRMS_ID = yrms.YRMS_ID
                  AND ROWNUM = 1)
             DEP_YRES_ACTDEPTIMENULL,
          (SELECT yres.YRES_ID
             FROM yres, ydet
            WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                  AND ydet.YDET_DATE = V8_sys_fideliodate
                  AND ydet.YDET_YRMS_ID = yrms.YRMS_ID
                  AND ROWNUM = 1
                  AND (YRES.YRES_EXPARRTIME) = V8_sys_fideliodate)
             ARR_YRES_ID,
          DECODE (
             (SELECT yres.YRES_ID
                FROM yres, ydet
               WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                     AND ydet.YDET_DATE = V8_sys_fideliodate
                     AND ydet.YDET_YRMS_ID = yrms.YRMS_ID
                     AND ROWNUM = 1
                     AND (YRES.YRES_EXPARRTIME) = V8_sys_fideliodate),
             NULL, 0,
             1)
             ARRIVAL,
          (SELECT yres.YRES_ACTARRTIMENULL
             FROM yres, ydet
            WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                  AND ydet.YDET_DATE = V8_sys_fideliodate
                  AND ydet.YDET_YRMS_ID = yrms.YRMS_ID
                  AND ROWNUM = 1
                  AND (YRES.YRES_EXPARRTIME) = V8_sys_fideliodate)
             ARR_YRES_ACTARRTIMENULL,
          DECODE (
             (SELECT yres.YRES_ID
                FROM yres
               WHERE     V8_sys_fideliodate BETWEEN (YRES.YRES_EXPARRTIME)
                                                AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = yrms.YRMS_ID
                     AND yres.YRES_RESSTATUS <> 3
                     AND TRUNC (yres.YRES_ACTARRTIME) <> V8_sys_fideliodate
                     AND ROWNUM = 1),
             NULL, (SELECT yres.YRES_ID
                      FROM yres, ydet
                     WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                           AND ydet.YDET_DATE + 1 = V8_sys_fideliodate
                           AND ydet.YDET_YRMS_ID = yrms.YRMS_ID
                           AND ROWNUM = 1),
             (SELECT yres.YRES_ID
                FROM yres
               WHERE     V8_sys_fideliodate BETWEEN (YRES.YRES_EXPARRTIME)
                                                AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = yrms.YRMS_ID
                     AND yres.YRES_RESSTATUS <> 3
                     AND TRUNC (yres.YRES_ACTARRTIME) <> V8_sys_fideliodate
                     AND ROWNUM = 1))
             STAY_DEP_YRES_ID,
          DECODE (
             (SELECT yres.YRES_XCMS_ID
                FROM yres
               WHERE     V8_sys_fideliodate BETWEEN (YRES.YRES_EXPARRTIME)
                                                AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = yrms.YRMS_ID
                     AND yres.YRES_RESSTATUS <> 3
                     AND TRUNC (yres.YRES_ACTARRTIME) <> V8_sys_fideliodate
                     AND ROWNUM = 1),
             NULL, (SELECT yres.YRES_XCMS_ID
                      FROM yres, ydet
                     WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                           AND ydet.YDET_DATE + 1 = V8_sys_fideliodate
                           AND ydet.YDET_YRMS_ID = yrms.YRMS_ID
                           AND ROWNUM = 1),
             (SELECT yres.YRES_XCMS_ID
                FROM yres
               WHERE     V8_sys_fideliodate BETWEEN (YRES.YRES_EXPARRTIME)
                                                AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = yrms.YRMS_ID
                     AND yres.YRES_RESSTATUS <> 3
                     AND TRUNC (yres.YRES_ACTARRTIME) <> V8_sys_fideliodate
                     AND ROWNUM = 1))
             STAY_DEP_XCMS_ID,
          (SELECT v8_rep_name.NAME2
             FROM v8_rep_name
            WHERE v8_rep_name.XCMS_ID =
                     (DECODE (
                         (SELECT yres.YRES_XCMS_ID
                            FROM yres
                           WHERE     V8_sys_fideliodate BETWEEN (YRES.YRES_EXPARRTIME)
                                                            AND (YRES.YRES_EXPDEPTIME)
                                 AND yres.YRES_YRMS_ID = yrms.YRMS_ID
                                 AND yres.YRES_RESSTATUS <> 3
                                 AND TRUNC (yres.YRES_ACTARRTIME) <>
                                        V8_sys_fideliodate
                                 AND ROWNUM = 1),
                         NULL, (SELECT yres.YRES_XCMS_ID
                                  FROM yres, ydet
                                 WHERE     yres.YRES_CURRENT_YDET_ID =
                                              ydet.YDET_ID
                                       AND ydet.YDET_DATE + 1 =
                                              V8_sys_fideliodate
                                       AND ydet.YDET_YRMS_ID = yrms.YRMS_ID
                                       AND ROWNUM = 1),
                         (SELECT yres.YRES_XCMS_ID
                            FROM yres
                           WHERE     V8_sys_fideliodate BETWEEN (YRES.YRES_EXPARRTIME)
                                                            AND (YRES.YRES_EXPDEPTIME)
                                 AND yres.YRES_YRMS_ID = yrms.YRMS_ID
                                 AND yres.YRES_RESSTATUS <> 3
                                 AND TRUNC (yres.YRES_ACTARRTIME) <>
                                        V8_sys_fideliodate
                                 AND ROWNUM = 1))))
             STAY_DEP_NAME,
          DECODE (
             (SELECT yres.YRES_ID
                FROM yres
               WHERE     V8_sys_fideliodate BETWEEN (YRES.YRES_EXPARRTIME)
                                                AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = yrms.YRMS_ID
                     AND yres.YRES_RESSTATUS <> 3
                     AND TRUNC (yres.YRES_ACTARRTIME) <> V8_sys_fideliodate
                     AND ROWNUM = 1),
             NULL, (SELECT yres.YRES_EXPARRTIME
                      FROM yres, ydet
                     WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                           AND ydet.YDET_DATE + 1 = V8_sys_fideliodate
                           AND ydet.YDET_YRMS_ID = yrms.YRMS_ID
                           AND ROWNUM = 1),
             (SELECT yres.YRES_EXPARRTIME
                FROM yres
               WHERE     V8_sys_fideliodate BETWEEN (YRES.YRES_EXPARRTIME)
                                                AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = yrms.YRMS_ID
                     AND yres.YRES_RESSTATUS <> 3
                     AND TRUNC (yres.YRES_ACTARRTIME) <> V8_sys_fideliodate
                     AND ROWNUM = 1))
             STAY_DEP_YRES_EXPARRTIME,
          DECODE (
             (SELECT yres.YRES_ID
                FROM yres
               WHERE     V8_sys_fideliodate BETWEEN (YRES.YRES_EXPARRTIME)
                                                AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = yrms.YRMS_ID
                     AND yres.YRES_RESSTATUS <> 3
                     AND TRUNC (yres.YRES_ACTARRTIME) <> V8_sys_fideliodate
                     AND ROWNUM = 1),
             NULL, (SELECT yres.YRES_EXPDEPTIME
                      FROM yres, ydet
                     WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                           AND ydet.YDET_DATE + 1 = V8_sys_fideliodate
                           AND ydet.YDET_YRMS_ID = yrms.YRMS_ID
                           AND ROWNUM = 1),
             (SELECT yres.YRES_EXPDEPTIME
                FROM yres
               WHERE     V8_sys_fideliodate BETWEEN (YRES.YRES_EXPARRTIME)
                                                AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = yrms.YRMS_ID
                     AND yres.YRES_RESSTATUS <> 3
                     AND TRUNC (yres.YRES_ACTARRTIME) <> V8_sys_fideliodate
                     AND ROWNUM = 1))
             STAY_DEP_YRES_EXPDEPTIME,
          DECODE (
             (SELECT yres.YRES_ID
                FROM yres
               WHERE     V8_sys_fideliodate BETWEEN (YRES.YRES_EXPARRTIME)
                                                AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = yrms.YRMS_ID
                     AND yres.YRES_RESSTATUS <> 3
                     AND TRUNC (yres.YRES_ACTARRTIME) <> V8_sys_fideliodate
                     AND ROWNUM = 1),
             NULL, (SELECT yres.YRES_FREETEXT1
                      FROM yres, ydet
                     WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                           AND ydet.YDET_DATE + 1 = V8_sys_fideliodate
                           AND ydet.YDET_YRMS_ID = yrms.YRMS_ID
                           AND ROWNUM = 1),
             (SELECT yres.YRES_FREETEXT1
                FROM yres
               WHERE     V8_sys_fideliodate BETWEEN (YRES.YRES_EXPARRTIME)
                                                AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = yrms.YRMS_ID
                     AND yres.YRES_RESSTATUS <> 3
                     AND TRUNC (yres.YRES_ACTARRTIME) <> V8_sys_fideliodate
                     AND ROWNUM = 1))
             STAY_DEP_YRES_FREETEXT1,
          DECODE (
             (SELECT yres.YRES_ID
                FROM yres
               WHERE     V8_sys_fideliodate BETWEEN (YRES.YRES_EXPARRTIME)
                                                AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = yrms.YRMS_ID
                     AND yres.YRES_RESSTATUS <> 3
                     AND TRUNC (yres.YRES_ACTARRTIME) <> V8_sys_fideliodate
                     AND ROWNUM = 1),
             NULL, (SELECT yres.YRES_FREETEXT2
                      FROM yres, ydet
                     WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                           AND ydet.YDET_DATE + 1 = V8_sys_fideliodate
                           AND ydet.YDET_YRMS_ID = yrms.YRMS_ID
                           AND ROWNUM = 1),
             (SELECT yres.YRES_FREETEXT2
                FROM yres
               WHERE     V8_sys_fideliodate BETWEEN (YRES.YRES_EXPARRTIME)
                                                AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = yrms.YRMS_ID
                     AND yres.YRES_RESSTATUS <> 3
                     AND TRUNC (yres.YRES_ACTARRTIME) <> V8_sys_fideliodate
                     AND ROWNUM = 1))
             STAY_DEP_YRES_FREETEXT2,
          (SELECT xpda.XPDA_SHORTDESC
             FROM xpda, xpal
            WHERE     xpda.XPDA_ID = xpal.XPAL_XPDA_ID
                  AND xpal.XPAL_XPAC_ID = 11
                  AND xpal.XPAL_XCMS_ID =
                         (DECODE (
                             (SELECT yres.YRES_XCMS_ID
                                FROM yres
                               WHERE     V8_sys_fideliodate BETWEEN (YRES.YRES_EXPARRTIME)
                                                                AND (YRES.YRES_EXPDEPTIME)
                                     AND yres.YRES_YRMS_ID = yrms.YRMS_ID
                                     AND yres.YRES_RESSTATUS <> 3
                                     AND TRUNC (yres.YRES_ACTARRTIME) <>
                                            V8_sys_fideliodate
                                     AND ROWNUM = 1),
                             NULL, (SELECT yres.YRES_XCMS_ID
                                      FROM yres, ydet
                                     WHERE     yres.YRES_CURRENT_YDET_ID =
                                                  ydet.YDET_ID
                                           AND ydet.YDET_DATE + 1 =
                                                  V8_sys_fideliodate
                                           AND ydet.YDET_YRMS_ID =
                                                  yrms.YRMS_ID
                                           AND ROWNUM = 1),
                             (SELECT yres.YRES_XCMS_ID
                                FROM yres
                               WHERE     V8_sys_fideliodate BETWEEN (YRES.YRES_EXPARRTIME)
                                                                AND (YRES.YRES_EXPDEPTIME)
                                     AND yres.YRES_YRMS_ID = yrms.YRMS_ID
                                     AND yres.YRES_RESSTATUS <> 3
                                     AND TRUNC (yres.YRES_ACTARRTIME) <>
                                            V8_sys_fideliodate
                                     AND ROWNUM = 1))))
             STAY_DEP_VIP,
          (SELECT xpda.XPDA_LONGDESC
             FROM xpda, xpal
            WHERE     xpda.XPDA_ID = xpal.XPAL_XPDA_ID
                  AND xpal.XPAL_XPAC_ID = 11
                  AND xpal.XPAL_XCMS_ID =
                         (DECODE (
                             (SELECT yres.YRES_XCMS_ID
                                FROM yres
                               WHERE     V8_sys_fideliodate BETWEEN (YRES.YRES_EXPARRTIME)
                                                                AND (YRES.YRES_EXPDEPTIME)
                                     AND yres.YRES_YRMS_ID = yrms.YRMS_ID
                                     AND yres.YRES_RESSTATUS <> 3
                                     AND TRUNC (yres.YRES_ACTARRTIME) <>
                                            V8_sys_fideliodate
                                     AND ROWNUM = 1),
                             NULL, (SELECT yres.YRES_XCMS_ID
                                      FROM yres, ydet
                                     WHERE     yres.YRES_CURRENT_YDET_ID =
                                                  ydet.YDET_ID
                                           AND ydet.YDET_DATE + 1 =
                                                  V8_sys_fideliodate
                                           AND ydet.YDET_YRMS_ID =
                                                  yrms.YRMS_ID
                                           AND ROWNUM = 1),
                             (SELECT yres.YRES_XCMS_ID
                                FROM yres
                               WHERE     V8_sys_fideliodate BETWEEN (YRES.YRES_EXPARRTIME)
                                                                AND (YRES.YRES_EXPDEPTIME)
                                     AND yres.YRES_YRMS_ID = yrms.YRMS_ID
                                     AND yres.YRES_RESSTATUS <> 3
                                     AND TRUNC (yres.YRES_ACTARRTIME) <>
                                            V8_sys_fideliodate
                                     AND ROWNUM = 1))))
             STAY_DEP_VIP_DESC,
            DECODE (
               (SELECT ydet.YDET_ADULTNO
                  FROM yres, ydet
                 WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                       AND ydet.YDET_DATE + 1 =
                              (SELECT wgbs.wgbs_datevalue FROM wgbs)
                       AND ydet.YDET_YRMS_ID = yrms_id
                       AND ROWNUM = 1),
               NULL, (SELECT ydet.YDET_ADULTNO
                        FROM yres, ydet
                       WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                             AND ydet.YDET_DATE =
                                    (SELECT wgbs.wgbs_datevalue FROM wgbs)
                             AND ydet.YDET_YRMS_ID = yrms_id
                             AND ROWNUM = 1),
               (SELECT ydet.YDET_ADULTNO
                  FROM yres, ydet
                 WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                       AND ydet.YDET_DATE + 1 =
                              (SELECT wgbs.wgbs_datevalue FROM wgbs)
                       AND ydet.YDET_YRMS_ID = yrms_id
                       AND ROWNUM = 1))
               STAY_DEP_YDET_ADULTNO,
            NVL (
               (SELECT SUM (YRGL_NUMBER)
                  FROM YRGL, YDET, YRES
                 WHERE     YRGL.YRGL_YDET_ID = YDET.YDET_ID
                       AND ydet.YDET_DATE + 1 =
                              (SELECT wgbs.wgbs_datevalue FROM wgbs)
                       AND YDET.YDET_ID = YRES.YRES_CURRENT_YDET_ID
                       AND ydet.YDET_YRMS_ID = yrms_id),
                  (SELECT SUM (YRGL_NUMBER)
                     FROM YRGL, YDET, YRES
                    WHERE     YRGL.YRGL_YDET_ID = YDET.YDET_ID
                          AND ydet.YDET_DATE =
                                 (SELECT wgbs.wgbs_datevalue FROM wgbs)
                          AND YDET.YDET_ID = YRES.YRES_CURRENT_YDET_ID
                          AND ydet.YDET_YRMS_ID = yrms_id))
               STAY_DEP_KIDS,
           (SELECT yres.YRES_XCMS_ID
             FROM yres, ydet
            WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                  AND ydet.YDET_DATE = V8_sys_fideliodate
                  AND ydet.YDET_YRMS_ID = yrms.YRMS_ID
                  AND ROWNUM = 1
                  AND (YRES.YRES_EXPARRTIME) = V8_sys_fideliodate)
             ARR_XCMS_ID,
          (SELECT v8_rep_name.NAME2
             FROM v8_rep_name
            WHERE v8_rep_name.XCMS_ID =
                     (SELECT yres.YRES_XCMS_ID
                        FROM yres, ydet
                       WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                             AND ydet.YDET_DATE = V8_sys_fideliodate
                             AND ydet.YDET_YRMS_ID = yrms.YRMS_ID
                             AND ROWNUM = 1
                             AND (YRES.YRES_EXPARRTIME) = V8_sys_fideliodate))
             ARR_NAME,
          (SELECT yres.YRES_EXPARRTIME
             FROM yres, ydet
            WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                  AND ydet.YDET_DATE = V8_sys_fideliodate
                  AND ydet.YDET_YRMS_ID = yrms.YRMS_ID
                  AND ROWNUM = 1
                  AND (YRES.YRES_EXPARRTIME) = V8_sys_fideliodate)
             ARR_YRES_EXPARRTIME,
          (SELECT yres.YRES_EXPDEPTIME
             FROM yres, ydet
            WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                  AND ydet.YDET_DATE = V8_sys_fideliodate
                  AND ydet.YDET_YRMS_ID = yrms.YRMS_ID
                  AND ROWNUM = 1
                  AND (YRES.YRES_EXPARRTIME) = V8_sys_fideliodate)
             ARR_YRES_EXPDEPTIME,
          (SELECT yres.YRES_FREETEXT1
             FROM yres, ydet
            WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                  AND ydet.YDET_DATE = V8_sys_fideliodate
                  AND ydet.YDET_YRMS_ID = yrms.YRMS_ID
                  AND ROWNUM = 1
                  AND (YRES.YRES_EXPARRTIME) = V8_sys_fideliodate)
             ARR_YRES_FREETEXT1,
          (SELECT yres.YRES_FREETEXT2
             FROM yres, ydet
            WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                  AND ydet.YDET_DATE = V8_sys_fideliodate
                  AND ydet.YDET_YRMS_ID = yrms.YRMS_ID
                  AND ROWNUM = 1
                  AND (YRES.YRES_EXPARRTIME) = V8_sys_fideliodate)
             ARR_YRES_FREETEXT2,
          (SELECT xpda.XPDA_SHORTDESC
             FROM xpda, xpal
            WHERE     xpda.XPDA_ID = xpal.XPAL_XPDA_ID
                  AND xpal.XPAL_XPAC_ID = 11
                  AND xpal.XPAL_XCMS_ID =
                         (SELECT yres.YRES_XCMS_ID
                            FROM yres, ydet
                           WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                                 AND ydet.YDET_DATE = V8_sys_fideliodate
                                 AND ydet.YDET_YRMS_ID = yrms.YRMS_ID
                                 AND ROWNUM = 1
                                 AND (YRES.YRES_EXPARRTIME) =
                                        V8_sys_fideliodate))
             ARR_VIP,
          (SELECT xpda.XPDA_LONGDESC
             FROM xpda, xpal
            WHERE     xpda.XPDA_ID = xpal.XPAL_XPDA_ID
                  AND xpal.XPAL_XPAC_ID = 11
                  AND xpal.XPAL_XCMS_ID =
                         (SELECT yres.YRES_XCMS_ID
                            FROM yres, ydet
                           WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                                 AND ydet.YDET_DATE = V8_sys_fideliodate
                                 AND ydet.YDET_YRMS_ID = yrms.YRMS_ID
                                 AND ROWNUM = 1
                                 AND (YRES.YRES_EXPARRTIME) =
                                        V8_sys_fideliodate))
             ARR_VIP_DESC,
            (SELECT ydet.YDET_ADULTNO
               FROM yres, ydet
              WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                    AND ydet.YDET_DATE = (SELECT wgbs.wgbs_datevalue FROM wgbs)
                    AND ydet.YDET_YRMS_ID = yrms_id
                    AND ROWNUM = 1
                    AND (YRES.YRES_EXPARRTIME) =
                           (SELECT wgbs.wgbs_datevalue FROM wgbs))
               ARR_YDET_ADULTNO,
                (SELECT SUM (YRGL_NUMBER)
                   FROM YRGL, YDET, YRES
                  WHERE     YRGL.YRGL_YDET_ID = YDET.YDET_ID
                        AND ydet.YDET_DATE =
                               (SELECT wgbs.wgbs_datevalue FROM wgbs)
                        AND YDET.YDET_ID = YRES.YRES_CURRENT_YDET_ID
                        AND ydet.YDET_YRMS_ID = yrms_id
                        AND (YRES.YRES_EXPARRTIME =
                                (SELECT wgbs.wgbs_datevalue FROM wgbs)))
               ARR_KIDS,
          (CASE
              WHEN (   DECODE (
                          (SELECT yres.YRES_ID
                             FROM yres
                            WHERE     V8_sys_fideliodate BETWEEN (YRES.YRES_EXPARRTIME)
                                                             AND (YRES.YRES_EXPDEPTIME)
                                  AND yres.YRES_YRMS_ID = yrms.YRMS_ID
                                  AND yres.YRES_RESSTATUS <> 3
                                  AND TRUNC (yres.YRES_ACTARRTIME) <>
                                         V8_sys_fideliodate
                                  AND ROWNUM = 1),
                          NULL, 0,
                          1) = 1
                    OR DECODE (
                          (SELECT yres.YRES_ID
                             FROM yres, ydet
                            WHERE     yres.YRES_CURRENT_YDET_ID =
                                         ydet.YDET_ID
                                  AND ydet.YDET_DATE = V8_sys_fideliodate
                                  AND ydet.YDET_YRMS_ID = yrms.YRMS_ID
                                  AND YRES.YRES_ACTDEPTIMENULL = 1
                                  AND ROWNUM = 1
                                  AND (YRES.YRES_EXPARRTIME) =
                                         V8_sys_fideliodate),
                          NULL, 0,
                          1) = 1
                    OR DECODE (
                          DECODE (
                             (SELECT yrst.YRST_ID
                                FROM yrst
                               WHERE     V8_sys_fideliodate =
                                            yrst.YRST_FROMTIME
                                     AND yrst.YRST_YRMS_ID = yrms.YRMS_ID),
                             NULL, (SELECT yrst.YRST_ID
                                      FROM yrst
                                     WHERE     (V8_sys_fideliodate BETWEEN yrst.YRST_FROMTIME
                                                                       AND   yrst.YRST_UNTILTIME
                                                                           - 1)
                                           AND yrst.YRST_YRMS_ID =
                                                  yrms.YRMS_ID
                                           AND ROWNUM = 1),
                             ( (SELECT yrst.YRST_ID
                                  FROM yrst
                                 WHERE     V8_sys_fideliodate =
                                              yrst.YRST_FROMTIME
                                       AND yrst.YRST_YRMS_ID = yrms.YRMS_ID))),
                          NULL, 0,
                          1) = 1)
              THEN
                 0
              ELSE
                 1
           END)
             FREEROOM,            --room is occupied or arrival expected today
          (select count(1) from V8_REP_MODERN_MAINTENANCE a where A.WACT_PLACE_ID = YRMS_ID and a.wact_status <> 2) 
             OPENTASKS             

     FROM yrms
    WHERE (V8_sys_fideliodate BETWEEN yrms.YRMS_VALIDFROM
                                  AND yrms.YRMS_VALIDUNTIL);




Create or Replace Force View V8_REP_MODERN_MAINTENANCE As  
  Select WACT_ID,
  wact_description,
  wact_StartTime,
  to_char(WACT_STARTTIME,'dd.mm.yyyy') StartDate,
  to_char(WACT_STARTTIME,'hh24:mi') StartTime,
  wact_EndTime,
  to_char(WACT_ENDTIME,'dd.mm.yyyy') EndDate,
  to_char(WACT_ENDTIME,'hh24:mi') EndTime,
  wact_AllDay,
  wact_wasc_id,
  WASC_LONGDESC,
  wact_waty_id,
  WATY_LONGDESC,
  wact_wart_id,
  wact_private,
  wact_compltime,
  to_char(wact_compltime,'dd.mm.yyyy') CompletedDate,
  wact_RepeatTime,
  want_notes,
  WANT_ID,
  wact_place_id,
  wact_place_type,
  Case 
    When wact_place_type = 1 then (select YRMS_SHORTDESC from yrms where yrms_id = wact_place_id) 
    When wact_place_type = 2 then (select YFSC_LONGDESC from yfsc where yfsc_id = wact_place_id)
    When wact_place_type = 3 then (select YPUB_LONGDESC from ypub where ypub_id = wact_place_id)
    Else 'NN'
  End place_desc,
  Case 
    When wact_place_type = 1 then (DECODE (
         (SELECT yrst.YRST_ID
          FROM yrst
          WHERE     
            (SELECT wgbs.wgbs_datevalue FROM wgbs) =
                    yrst.YRST_FROMTIME
                AND yrst.YRST_YRMS_ID = wact_place_id),
             NULL, 
             (DECODE (
                 (SELECT yrst.YRST_ID
                    FROM yrst
                    WHERE     
                        ((SELECT wgbs.wgbs_datevalue FROM wgbs) BETWEEN yrst.YRST_FROMTIME
                                                                        AND   yrst.YRST_UNTILTIME - 1)
                          AND yrst.YRST_YRMS_ID = wact_place_id
                          AND ROWNUM = 1),
                       NULL, (SELECT yhks.YHKS_SHORTDESC
                                FROM yhks
                               WHERE yhks.YHKS_ID =
                                        (SELECT YRMS.YRMS_YHKS_ID
                                           FROM yrms
                                          WHERE YRMS.YRMS_ID =
                                                   wact_place_id)),
                       (SELECT yrsc.YRSC_SHORTDESC
                          FROM yrsc, yrst
                         WHERE     yrsc.YRSC_ID = yrst.YRST_YRSC_ID
                               AND ( (SELECT wgbs.wgbs_datevalue FROM wgbs) BETWEEN yrst.YRST_FROMTIME
                                                                                AND   yrst.YRST_UNTILTIME
                                                                                    - 1)
                               AND yrst.YRST_YRMS_ID = wact_place_id
                               AND ROWNUM = 1))),
             (SELECT yrsc.YRSC_SHORTDESC
                FROM yrsc, yrst
               WHERE     yrsc.YRSC_ID = yrst.YRST_YRSC_ID
                     AND (SELECT wgbs.wgbs_datevalue FROM wgbs) =
                            yrst.YRST_FROMTIME
                     AND yrst.YRST_YRMS_ID = wact_place_id)))
    Else ''
  End ROOM_STATUS,
(SELECT yres.YRES_ID
             FROM yres
            WHERE     (SELECT wgbs.wgbs_datevalue FROM wgbs) BETWEEN (YRES.YRES_EXPARRTIME)
                                                                 AND (YRES.YRES_EXPDEPTIME)
                  AND yres.YRES_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                  AND yres.YRES_RESSTATUS <> 3
                  AND TRUNC (yres.YRES_ACTARRTIME) <>
                         (SELECT wgbs.wgbs_datevalue FROM wgbs)
                  AND ROWNUM = 1)
             STAY_OVER_YRES_ID,
          DECODE (
             (SELECT yres.YRES_ID
                FROM yres
               WHERE     (SELECT wgbs.wgbs_datevalue FROM wgbs) BETWEEN (YRES.YRES_EXPARRTIME)
                                                                    AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                     AND yres.YRES_RESSTATUS <> 3
                     AND TRUNC (yres.YRES_ACTARRTIME) <>
                            (SELECT wgbs.wgbs_datevalue FROM wgbs)
                     AND ROWNUM = 1),
             NULL, 0,
             1)
             STAY_OVER,
          (SELECT yres.YRES_ID
             FROM yres, ydet
            WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                  AND ydet.YDET_DATE + 1 =
                         (SELECT wgbs.wgbs_datevalue FROM wgbs)
                  AND ydet.YDET_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                  AND yres.YRES_RESSTATUS <> 3
                  AND ROWNUM = 1)
             DEP_YRES_ID,
          DECODE (
             (SELECT yres.YRES_ID
                FROM yres, ydet
               WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                     AND ydet.YDET_DATE + 1 =
                            (SELECT wgbs.wgbs_datevalue FROM wgbs)
                     AND ydet.YDET_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                     AND yres.YRES_RESSTATUS <> 3
                     AND ROWNUM = 1),
             NULL, 0,
             1)
             DEPARTURE,
          (SELECT yres.YRES_ACTDEPTIMENULL
             FROM yres, ydet
            WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                  AND ydet.YDET_DATE + 1 =
                         (SELECT wgbs.wgbs_datevalue FROM wgbs)
                  AND ydet.YDET_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                  AND ROWNUM = 1)
             DEP_YRES_ACTDEPTIMENULL,
          (SELECT yres.YRES_ID
             FROM yres, ydet
            WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                  AND ydet.YDET_DATE = (SELECT wgbs.wgbs_datevalue FROM wgbs)
                  AND ydet.YDET_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                  AND ROWNUM = 1
                  AND (YRES.YRES_EXPARRTIME) =
                         (SELECT wgbs.wgbs_datevalue FROM wgbs))
             ARR_YRES_ID,
          DECODE (
             (SELECT yres.YRES_ID
                FROM yres, ydet
               WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                     AND ydet.YDET_DATE =
                            (SELECT wgbs.wgbs_datevalue FROM wgbs)
                     AND ydet.YDET_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                     AND ROWNUM = 1
                     AND (YRES.YRES_EXPARRTIME) =
                            (SELECT wgbs.wgbs_datevalue FROM wgbs)),
             NULL, 0,
             1)
             ARRIVAL,
          (SELECT yres.YRES_ACTARRTIMENULL
             FROM yres, ydet
            WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                  AND ydet.YDET_DATE = (SELECT wgbs.wgbs_datevalue FROM wgbs)
                  AND ydet.YDET_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                  AND ROWNUM = 1
                  AND (YRES.YRES_EXPARRTIME) =
                         (SELECT wgbs.wgbs_datevalue FROM wgbs))
             ARR_YRES_ACTARRTIMENULL,
          DECODE (
             (SELECT yres.YRES_ID
                FROM yres
               WHERE     (SELECT wgbs.wgbs_datevalue FROM wgbs) BETWEEN (YRES.YRES_EXPARRTIME)
                                                                    AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                     AND yres.YRES_RESSTATUS <> 3
                     AND TRUNC (yres.YRES_ACTARRTIME) <>
                            (SELECT wgbs.wgbs_datevalue FROM wgbs)
                     AND ROWNUM = 1),
             NULL, (SELECT yres.YRES_ID
                      FROM yres, ydet
                     WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                           AND ydet.YDET_DATE + 1 =
                                  (SELECT wgbs.wgbs_datevalue FROM wgbs)
                           AND ydet.YDET_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                           AND ROWNUM = 1),
             (SELECT yres.YRES_ID
                FROM yres
               WHERE     (SELECT wgbs.wgbs_datevalue FROM wgbs) BETWEEN (YRES.YRES_EXPARRTIME)
                                                                    AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                     AND yres.YRES_RESSTATUS <> 3
                     AND TRUNC (yres.YRES_ACTARRTIME) <>
                            (SELECT wgbs.wgbs_datevalue FROM wgbs)
                     AND ROWNUM = 1))
             STAY_DEP_YRES_ID,
            (SELECT(SELECT V8_FNC_XCNO_REP_INFOS(YRES_ID, 'YRES', 2, 1, null, null)
             FROM yres
               WHERE (SELECT wgbs.wgbs_datevalue FROM wgbs) BETWEEN (YRES.YRES_EXPARRTIME)
                                                                AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = wact_place_id
                     AND wact_place_type = 1
                     AND yres.YRES_RESSTATUS <> 3
                     AND TRUNC (yres.YRES_ACTARRTIME) <>
                            (SELECT wgbs.wgbs_datevalue FROM wgbs)
                     AND ROWNUM = 1)
            ||
            (SELECT V8_FNC_XCNO_REP_INFOS(YRES_ID, 'YRES', 2, 0, null, null)
             FROM yres
               WHERE (SELECT wgbs.wgbs_datevalue FROM wgbs) BETWEEN (YRES.YRES_EXPARRTIME)
                                                                AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = wact_place_id
                     AND wact_place_type = 1
                     AND yres.YRES_RESSTATUS <> 3
                     AND TRUNC (yres.YRES_ACTARRTIME) <>
                            (SELECT wgbs.wgbs_datevalue FROM wgbs)
                     AND ROWNUM = 1) from dual)
          STAY_DEP_HSK_NOTES,
          DECODE (
             (SELECT ydet.YDET_ADULTNO
                FROM yres, ydet
               WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                     AND ydet.YDET_DATE + 1 =
                            (SELECT wgbs.wgbs_datevalue FROM wgbs)
                     AND ydet.YDET_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                     AND ROWNUM = 1),
             NULL, (SELECT ydet.YDET_ADULTNO
                      FROM yres, ydet
                     WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                           AND ydet.YDET_DATE =
                                  (SELECT wgbs.wgbs_datevalue FROM wgbs)
                           AND ydet.YDET_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                           AND ROWNUM = 1),
             (SELECT ydet.YDET_ADULTNO
                FROM yres, ydet
               WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                     AND ydet.YDET_DATE + 1 =
                            (SELECT wgbs.wgbs_datevalue FROM wgbs)
                     AND ydet.YDET_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                     AND ROWNUM = 1))
             STAY_DEP_YDET_ADULTNO,
          NVL ((SELECT SUM (YRGL_NUMBER)
                 FROM YRGL, YDET, YRES
                 WHERE  YRGL.YRGL_YDET_ID = YDET.YDET_ID
                        AND ydet.YDET_DATE + 1 =
                                (SELECT wgbs.wgbs_datevalue FROM wgbs)
                        AND YDET.YDET_ID = YRES.YRES_CURRENT_YDET_ID
                        AND ydet.YDET_YRMS_ID = wact_place_id
                          AND wact_place_type = 1),
              NVL ((SELECT SUM (YRGL_NUMBER)
                 FROM YRGL, YDET, YRES
                 WHERE  YRGL.YRGL_YDET_ID = YDET.YDET_ID
                        AND ydet.YDET_DATE =
                                (SELECT wgbs.wgbs_datevalue FROM wgbs)
                        AND YDET.YDET_ID = YRES.YRES_CURRENT_YDET_ID
                        AND ydet.YDET_YRMS_ID = wact_place_id
                          AND wact_place_type = 1),0))
             STAY_DEP_KIDS,
          DECODE (
             (SELECT yres.YRES_XCMS_ID
                FROM yres
               WHERE     (SELECT wgbs.wgbs_datevalue FROM wgbs) BETWEEN (YRES.YRES_EXPARRTIME)
                                                                    AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                     AND yres.YRES_RESSTATUS <> 3
                     AND TRUNC (yres.YRES_ACTARRTIME) <>
                            (SELECT wgbs.wgbs_datevalue FROM wgbs)
                     AND ROWNUM = 1),
             NULL, (SELECT yres.YRES_XCMS_ID
                      FROM yres, ydet
                     WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                           AND ydet.YDET_DATE + 1 =
                                  (SELECT wgbs.wgbs_datevalue FROM wgbs)
                           AND ydet.YDET_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                           AND ROWNUM = 1),
             (SELECT yres.YRES_XCMS_ID
                FROM yres
               WHERE     (SELECT wgbs.wgbs_datevalue FROM wgbs) BETWEEN (YRES.YRES_EXPARRTIME)
                                                                    AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                     AND yres.YRES_RESSTATUS <> 3
                     AND TRUNC (yres.YRES_ACTARRTIME) <>
                            (SELECT wgbs.wgbs_datevalue FROM wgbs)
                     AND ROWNUM = 1))
             STAY_DEP_XCMS_ID,
          (NVL((Select V8_Fnc_Amenities_YDET_Infos (ydet_id, 1)
                  From ydet, yres
                  Where yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                  and rownum=1
                    AND ydet.YDET_DATE =
                    (SELECT wgbs.wgbs_datevalue FROM wgbs)
                AND ydet.YDET_YRMS_ID = wact_place_id
                AND wact_place_type = 1
                ),(Select V8_Fnc_Amenities_YDET_Infos (ydet_id, 1)
                  From ydet, yres
                  Where yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                  AND ydet.YDET_DATE + 1 =
                    (SELECT wgbs.wgbs_datevalue FROM wgbs)
                AND ydet.YDET_YRMS_ID = wact_place_id
                AND wact_place_type = 1)))
           STAY_DEP_AMENITY,
          (SELECT v8_rep_name.NAME2
             FROM v8_rep_name
            WHERE v8_rep_name.XCMS_ID =
                     (DECODE (
                         (SELECT yres.YRES_XCMS_ID
                            FROM yres
                           WHERE     (SELECT wgbs.wgbs_datevalue FROM wgbs) BETWEEN (YRES.YRES_EXPARRTIME)
                                                                                AND (YRES.YRES_EXPDEPTIME)
                                 AND yres.YRES_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                                 AND yres.YRES_RESSTATUS <> 3
                                 AND TRUNC (yres.YRES_ACTARRTIME) <>
                                        (SELECT wgbs.wgbs_datevalue FROM wgbs)
                                 AND ROWNUM = 1),
                         NULL, (SELECT yres.YRES_XCMS_ID
                                  FROM yres, ydet
                                 WHERE     yres.YRES_CURRENT_YDET_ID =
                                              ydet.YDET_ID
                                       AND ydet.YDET_DATE + 1 =
                                              (SELECT wgbs.wgbs_datevalue
                                                 FROM wgbs)
                                       AND ydet.YDET_YRMS_ID =
                                              wact_place_id
                  AND wact_place_type = 1
                                       AND ROWNUM = 1),
                         (SELECT yres.YRES_XCMS_ID
                            FROM yres
                           WHERE     (SELECT wgbs.wgbs_datevalue FROM wgbs) BETWEEN (YRES.YRES_EXPARRTIME)
                                                                                AND (YRES.YRES_EXPDEPTIME)
                                 AND yres.YRES_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                                 AND yres.YRES_RESSTATUS <> 3
                                 AND TRUNC (yres.YRES_ACTARRTIME) <>
                                        (SELECT wgbs.wgbs_datevalue FROM wgbs)
                                 AND ROWNUM = 1))))
             STAY_DEP_NAME,
          DECODE (
             (SELECT yres.YRES_ID
                FROM yres
               WHERE     (SELECT wgbs.wgbs_datevalue FROM wgbs) BETWEEN (YRES.YRES_EXPARRTIME)
                                                                    AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                     AND yres.YRES_RESSTATUS <> 3
                     AND TRUNC (yres.YRES_ACTARRTIME) <>
                            (SELECT wgbs.wgbs_datevalue FROM wgbs)
                     AND ROWNUM = 1),
             NULL, (SELECT yres.YRES_EXPARRTIME
                      FROM yres, ydet
                     WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                           AND ydet.YDET_DATE + 1 =
                                  (SELECT wgbs.wgbs_datevalue FROM wgbs)
                           AND ydet.YDET_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                           AND ROWNUM = 1),
             (SELECT yres.YRES_EXPARRTIME
                FROM yres
               WHERE     (SELECT wgbs.wgbs_datevalue FROM wgbs) BETWEEN (YRES.YRES_EXPARRTIME)
                                                                    AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                     AND yres.YRES_RESSTATUS <> 3
                     AND TRUNC (yres.YRES_ACTARRTIME) <>
                            (SELECT wgbs.wgbs_datevalue FROM wgbs)
                     AND ROWNUM = 1))
             STAY_DEP_YRES_EXPARRTIME,
          DECODE (
             (SELECT yres.YRES_ID
                FROM yres
               WHERE     (SELECT wgbs.wgbs_datevalue FROM wgbs) BETWEEN (YRES.YRES_EXPARRTIME)
                                                                    AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                     AND yres.YRES_RESSTATUS <> 3
                     AND TRUNC (yres.YRES_ACTARRTIME) <>
                            (SELECT wgbs.wgbs_datevalue FROM wgbs)
                     AND ROWNUM = 1),
             NULL, (SELECT yres.YRES_EXPDEPTIME
                      FROM yres, ydet
                     WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                           AND ydet.YDET_DATE + 1 =
                                  (SELECT wgbs.wgbs_datevalue FROM wgbs)
                           AND ydet.YDET_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                           AND ROWNUM = 1),
             (SELECT yres.YRES_EXPDEPTIME
                FROM yres
               WHERE     (SELECT wgbs.wgbs_datevalue FROM wgbs) BETWEEN (YRES.YRES_EXPARRTIME)
                                                                    AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                     AND yres.YRES_RESSTATUS <> 3
                     AND TRUNC (yres.YRES_ACTARRTIME) <>
                            (SELECT wgbs.wgbs_datevalue FROM wgbs)
                     AND ROWNUM = 1))
             STAY_DEP_YRES_EXPDEPTIME,
          DECODE (
             (SELECT yres.YRES_ID
                FROM yres
               WHERE     (SELECT wgbs.wgbs_datevalue FROM wgbs) BETWEEN (YRES.YRES_EXPARRTIME)
                                                                    AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                     AND yres.YRES_RESSTATUS <> 3
                     AND TRUNC (yres.YRES_ACTARRTIME) <>
                            (SELECT wgbs.wgbs_datevalue FROM wgbs)
                     AND ROWNUM = 1),
             NULL, (SELECT yres.YRES_FREETEXT1
                      FROM yres, ydet
                     WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                           AND ydet.YDET_DATE + 1 =
                                  (SELECT wgbs.wgbs_datevalue FROM wgbs)
                           AND ydet.YDET_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                           AND ROWNUM = 1),
             (SELECT yres.YRES_FREETEXT1
                FROM yres
               WHERE     (SELECT wgbs.wgbs_datevalue FROM wgbs) BETWEEN (YRES.YRES_EXPARRTIME)
                                                                    AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                     AND yres.YRES_RESSTATUS <> 3
                     AND TRUNC (yres.YRES_ACTARRTIME) <>
                            (SELECT wgbs.wgbs_datevalue FROM wgbs)
                     AND ROWNUM = 1))
             STAY_DEP_YRES_FREETEXT1,
          DECODE (
             (SELECT yres.YRES_ID
                FROM yres
               WHERE     (SELECT wgbs.wgbs_datevalue FROM wgbs) BETWEEN (YRES.YRES_EXPARRTIME)
                                                                    AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                     AND yres.YRES_RESSTATUS <> 3
                     AND TRUNC (yres.YRES_ACTARRTIME) <>
                            (SELECT wgbs.wgbs_datevalue FROM wgbs)
                     AND ROWNUM = 1),
             NULL, (SELECT yres.YRES_FREETEXT2
                      FROM yres, ydet
                     WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                           AND ydet.YDET_DATE + 1 =
                                  (SELECT wgbs.wgbs_datevalue FROM wgbs)
                           AND ydet.YDET_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                           AND ROWNUM = 1),
             (SELECT yres.YRES_FREETEXT2
                FROM yres
               WHERE     (SELECT wgbs.wgbs_datevalue FROM wgbs) BETWEEN (YRES.YRES_EXPARRTIME)
                                                                    AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                     AND yres.YRES_RESSTATUS <> 3
                     AND TRUNC (yres.YRES_ACTARRTIME) <>
                            (SELECT wgbs.wgbs_datevalue FROM wgbs)
                     AND ROWNUM = 1))
             STAY_DEP_YRES_FREETEXT2,
          (SELECT xpda.XPDA_SHORTDESC
             FROM xpda, xpal
            WHERE     xpda.XPDA_ID = xpal.XPAL_XPDA_ID
                  AND xpal.XPAL_XPAC_ID = 11
                  AND xpal.XPAL_XCMS_ID =
                         (DECODE (
                             (SELECT yres.YRES_XCMS_ID
                                FROM yres
                               WHERE     (SELECT wgbs.wgbs_datevalue
                                            FROM wgbs) BETWEEN (YRES.YRES_EXPARRTIME)
                                                           AND (YRES.YRES_EXPDEPTIME)
                                     AND yres.YRES_YRMS_ID =
                                            wact_place_id
                  AND wact_place_type = 1
                                     AND yres.YRES_RESSTATUS <> 3
                                     AND TRUNC (yres.YRES_ACTARRTIME) <>
                                            (SELECT wgbs.wgbs_datevalue
                                               FROM wgbs)
                                     AND ROWNUM = 1),
                             NULL, (SELECT yres.YRES_XCMS_ID
                                      FROM yres, ydet
                                     WHERE     yres.YRES_CURRENT_YDET_ID =
                                                  ydet.YDET_ID
                                           AND ydet.YDET_DATE + 1 =
                                                  (SELECT wgbs.wgbs_datevalue
                                                     FROM wgbs)
                                           AND ydet.YDET_YRMS_ID =
                                                  wact_place_id
                  AND wact_place_type = 1
                                           AND ROWNUM = 1),
                             (SELECT yres.YRES_XCMS_ID
                                FROM yres
                               WHERE     (SELECT wgbs.wgbs_datevalue
                                            FROM wgbs) BETWEEN (YRES.YRES_EXPARRTIME)
                                                           AND (YRES.YRES_EXPDEPTIME)
                                     AND yres.YRES_YRMS_ID =
                                            wact_place_id
                  AND wact_place_type = 1
                                     AND yres.YRES_RESSTATUS <> 3
                                     AND TRUNC (yres.YRES_ACTARRTIME) <>
                                            (SELECT wgbs.wgbs_datevalue
                                               FROM wgbs)
                                     AND ROWNUM = 1))))
             STAY_DEP_VIP,
          (SELECT xpda.XPDA_LONGDESC
             FROM xpda, xpal
            WHERE     xpda.XPDA_ID = xpal.XPAL_XPDA_ID
                  AND xpal.XPAL_XPAC_ID = 11
                  AND xpal.XPAL_XCMS_ID =
                         (DECODE (
                             (SELECT yres.YRES_XCMS_ID
                                FROM yres
                               WHERE     (SELECT wgbs.wgbs_datevalue
                                            FROM wgbs) BETWEEN (YRES.YRES_EXPARRTIME)
                                                           AND (YRES.YRES_EXPDEPTIME)
                                     AND yres.YRES_YRMS_ID =
                                            wact_place_id
                  AND wact_place_type = 1
                                     AND yres.YRES_RESSTATUS <> 3
                                     AND TRUNC (yres.YRES_ACTARRTIME) <>
                                            (SELECT wgbs.wgbs_datevalue
                                               FROM wgbs)
                                     AND ROWNUM = 1),
                             NULL, (SELECT yres.YRES_XCMS_ID
                                      FROM yres, ydet
                                     WHERE     yres.YRES_CURRENT_YDET_ID =
                                                  ydet.YDET_ID
                                           AND ydet.YDET_DATE + 1 =
                                                  (SELECT wgbs.wgbs_datevalue
                                                     FROM wgbs)
                                           AND ydet.YDET_YRMS_ID =
                                                  wact_place_id
                  AND wact_place_type = 1
                                           AND ROWNUM = 1),
                             (SELECT yres.YRES_XCMS_ID
                                FROM yres
                               WHERE     (SELECT wgbs.wgbs_datevalue
                                            FROM wgbs) BETWEEN (YRES.YRES_EXPARRTIME)
                                                           AND (YRES.YRES_EXPDEPTIME)
                                     AND yres.YRES_YRMS_ID =
                                            wact_place_id
                  AND wact_place_type = 1
                                     AND yres.YRES_RESSTATUS <> 3
                                     AND TRUNC (yres.YRES_ACTARRTIME) <>
                                            (SELECT wgbs.wgbs_datevalue
                                               FROM wgbs)
                                     AND ROWNUM = 1))))
             STAY_DEP_VIP_DESC,
          (SELECT yres.YRES_XCMS_ID
             FROM yres, ydet
            WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                  AND ydet.YDET_DATE = (SELECT wgbs.wgbs_datevalue FROM wgbs)
                  AND ydet.YDET_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                  AND ROWNUM = 1
                  AND (YRES.YRES_EXPARRTIME) =
                         (SELECT wgbs.wgbs_datevalue FROM wgbs))
             ARR_XCMS_ID,
          (SELECT v8_rep_name.NAME2
             FROM v8_rep_name
            WHERE v8_rep_name.XCMS_ID =
                     (SELECT yres.YRES_XCMS_ID
                        FROM yres, ydet
                       WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                             AND ydet.YDET_DATE =
                                    (SELECT wgbs.wgbs_datevalue FROM wgbs)
                             AND ydet.YDET_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                             AND ROWNUM = 1
                             AND (YRES.YRES_EXPARRTIME) =
                                    (SELECT wgbs.wgbs_datevalue FROM wgbs)))
             ARR_NAME,
          (SELECT yres.YRES_EXPARRTIME
             FROM yres, ydet
            WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                  AND ydet.YDET_DATE = (SELECT wgbs.wgbs_datevalue FROM wgbs)
                  AND ydet.YDET_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                  AND ROWNUM = 1
                  AND (YRES.YRES_EXPARRTIME) =
                         (SELECT wgbs.wgbs_datevalue FROM wgbs))
             ARR_YRES_EXPARRTIME,
          (SELECT yres.YRES_EXPDEPTIME
             FROM yres, ydet
            WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                  AND ydet.YDET_DATE = (SELECT wgbs.wgbs_datevalue FROM wgbs)
                  AND ydet.YDET_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                  AND ROWNUM = 1
                  AND (YRES.YRES_EXPARRTIME) =
                         (SELECT wgbs.wgbs_datevalue FROM wgbs))
             ARR_YRES_EXPDEPTIME,
          (SELECT yres.YRES_FREETEXT1
             FROM yres, ydet
            WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                  AND ydet.YDET_DATE = (SELECT wgbs.wgbs_datevalue FROM wgbs)
                  AND ydet.YDET_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                  AND ROWNUM = 1
                  AND (YRES.YRES_EXPARRTIME) =
                         (SELECT wgbs.wgbs_datevalue FROM wgbs))
             ARR_YRES_FREETEXT1,
          (SELECT yres.YRES_FREETEXT2
             FROM yres, ydet
            WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                  AND ydet.YDET_DATE = (SELECT wgbs.wgbs_datevalue FROM wgbs)
                  AND ydet.YDET_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                  AND ROWNUM = 1
                  AND (YRES.YRES_EXPARRTIME) =
                         (SELECT wgbs.wgbs_datevalue FROM wgbs))
             ARR_YRES_FREETEXT2,
          (SELECT ydet.YDET_ADULTNO
             FROM yres, ydet
            WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                  AND ydet.YDET_DATE = (SELECT wgbs.wgbs_datevalue FROM wgbs)
                  AND ydet.YDET_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                  AND ROWNUM = 1
                  AND (YRES.YRES_EXPARRTIME) =
                         (SELECT wgbs.wgbs_datevalue FROM wgbs))
             ARR_YDET_ADULTNO,
           (NVL ((SELECT SUM (YRGL_NUMBER)
                       FROM YRGL, YDET, YRES
                       WHERE  YRGL.YRGL_YDET_ID = YDET.YDET_ID
                              AND ydet.YDET_DATE =
                                      (SELECT wgbs.wgbs_datevalue FROM wgbs)
                              AND YDET.YDET_ID = YRES.YRES_CURRENT_YDET_ID
                              AND ydet.YDET_YRMS_ID = wact_place_id
                            AND (YRES.YRES_EXPARRTIME =
                                         (SELECT wgbs.wgbs_datevalue FROM wgbs))
                                AND wact_place_type = 1),0))
         ARR_KIDS,
          (Select V8_Fnc_Amenities_YDET_Infos (ydet_id, 1)
                  From ydet, yres
            WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                  AND ydet.YDET_DATE = (SELECT wgbs.wgbs_datevalue FROM wgbs)
                  AND ydet.YDET_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                  AND ROWNUM = 1
                  AND (YRES.YRES_EXPARRTIME) =
                         (SELECT wgbs.wgbs_datevalue FROM wgbs))
		 ARR_AMENITY,
            (SELECT(SELECT V8_FNC_XCNO_REP_INFOS(YRES_ID, 'YRES', 2, 1, null, null)
             FROM yres
               WHERE (SELECT wgbs.wgbs_datevalue FROM wgbs) BETWEEN (YRES.YRES_EXPARRTIME)
                                                                AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = wact_place_id
                     AND wact_place_type = 1
                     AND TRUNC (yres.YRES_EXPARRTIME) =
                            (SELECT wgbs.wgbs_datevalue FROM wgbs)
                     AND ROWNUM = 1)
            ||
            (SELECT V8_FNC_XCNO_REP_INFOS(YRES_ID, 'YRES', 2, 0, null, null)
             FROM yres
               WHERE (SELECT wgbs.wgbs_datevalue FROM wgbs) BETWEEN (YRES.YRES_EXPARRTIME)
                                                                AND (YRES.YRES_EXPDEPTIME)
                     AND yres.YRES_YRMS_ID = wact_place_id
                     AND wact_place_type = 1
                     AND TRUNC (yres.YRES_EXPARRTIME) =
                            (SELECT wgbs.wgbs_datevalue FROM wgbs)
                     AND ROWNUM = 1) from dual)
          ARR_HSK_NOTES,
          (SELECT xpda.XPDA_SHORTDESC
             FROM xpda, xpal
            WHERE     xpda.XPDA_ID = xpal.XPAL_XPDA_ID
                  AND xpal.XPAL_XPAC_ID = 11
                  AND xpal.XPAL_XCMS_ID =
                         (SELECT yres.YRES_XCMS_ID
                            FROM yres, ydet
                           WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                                 AND ydet.YDET_DATE =
                                        (SELECT wgbs.wgbs_datevalue FROM wgbs)
                                 AND ydet.YDET_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                                 AND ROWNUM = 1
                                 AND (YRES.YRES_EXPARRTIME) =
                                        (SELECT wgbs.wgbs_datevalue FROM wgbs)))
             ARR_VIP,
          (SELECT xpda.XPDA_LONGDESC
             FROM xpda, xpal
            WHERE     xpda.XPDA_ID = xpal.XPAL_XPDA_ID
                  AND xpal.XPAL_XPAC_ID = 11
                  AND xpal.XPAL_XCMS_ID =
                         (SELECT yres.YRES_XCMS_ID
                            FROM yres, ydet
                           WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                                 AND ydet.YDET_DATE =
                                        (SELECT wgbs.wgbs_datevalue FROM wgbs)
                                 AND ydet.YDET_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                                 AND ROWNUM = 1
                                 AND (YRES.YRES_EXPARRTIME) =
                                        (SELECT wgbs.wgbs_datevalue FROM wgbs)))
             ARR_VIP_DESC,
          (CASE
              WHEN (   DECODE (
                          (SELECT yres.YRES_ID
                             FROM yres
                            WHERE     (SELECT wgbs.wgbs_datevalue FROM wgbs) BETWEEN (YRES.YRES_EXPARRTIME)
                                                                                 AND (YRES.YRES_EXPDEPTIME)
                                  AND yres.YRES_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                                  AND yres.YRES_RESSTATUS <> 3
                                  AND TRUNC (yres.YRES_ACTARRTIME) <>
                                         (SELECT wgbs.wgbs_datevalue
                                            FROM wgbs)
                                  AND ROWNUM = 1),
                          NULL, 0,
                          1) = 1
                    OR DECODE (
                          (SELECT yres.YRES_ID
                             FROM yres, ydet
                            WHERE     yres.YRES_CURRENT_YDET_ID =
                                         ydet.YDET_ID
                                  AND ydet.YDET_DATE =
                                         (SELECT wgbs.wgbs_datevalue
                                            FROM wgbs)
                                  AND ydet.YDET_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                                  AND ROWNUM = 1
                                  AND (YRES.YRES_EXPARRTIME) =
                                         (SELECT wgbs.wgbs_datevalue
                                            FROM wgbs)),
                          NULL, 0,
                          1) = 1
                    OR DECODE (
                          DECODE (
                             (SELECT yrst.YRST_ID
                                FROM yrst
                               WHERE     (SELECT wgbs.wgbs_datevalue
                                            FROM wgbs) = yrst.YRST_FROMTIME
                                     AND yrst.YRST_YRMS_ID =
                                            wact_place_id
                  AND wact_place_type = 1),
                             NULL, (SELECT yrst.YRST_ID
                                      FROM yrst
                                     WHERE     ( (SELECT wgbs.wgbs_datevalue
                                                    FROM wgbs) BETWEEN yrst.YRST_FROMTIME
                                                                   AND   yrst.YRST_UNTILTIME
                                                                       - 1)
                                           AND yrst.YRST_YRMS_ID =
                                                  wact_place_id
                  AND wact_place_type = 1
                                           AND ROWNUM = 1),
                             ( (SELECT yrst.YRST_ID
                                  FROM yrst
                                 WHERE     (SELECT wgbs.wgbs_datevalue
                                              FROM wgbs) = yrst.YRST_FROMTIME
                                       AND yrst.YRST_YRMS_ID =
                                              wact_place_id
                  AND wact_place_type = 1))),
                          NULL, 0,
                          1) = 1)
              THEN
                 0
              ELSE
                 1
           END)
             FREEROOM,
          (SELECT MIN (ydet.YDET_DATE)
             FROM ydet, yres
            WHERE     ydet.YDET_YRES_ID = yres.YRES_ID
                  AND yres.YRES_RESSTATUS <> 3
                  AND yres.YRES_EXPARRTIME >
                         (SELECT wgbs.wgbs_datevalue FROM wgbs)
                  AND ydet.YDET_YRMS_ID = wact_place_id
                  AND wact_place_type = 1)
             NEXT_ARRIVAL,
  y1.wala_xcms_id CompanyId,
   (n1.XCMS_NAME1||lpad(n1.XCMS_NAME2,length(n1.XCMS_NAME2)+1,' ' )||' ' ||n1.XCMS_NAME3) CompanyName,
  y2.wala_xcms_id ContactId,
    (n2.XCMS_NAME1||decode(n2.XCMS_NAME3,'','',', '||n2.XCMS_NAME3)||decode(n2.XCMS_NAME2,'','',decode(n2.XCMS_NAME3,'',', ',' ')||n2.XCMS_NAME2)) ContactName,
  n3.xcms_id ManagerID,
  n3.xcms_name1 ManagerName,
  WACT_XCOM_ID,
  wact_requestlinked_id,
  wact_estimated_duration_min,
  wact_status,
  (SELECT count(*) FROM WIML where WIML_TABLENAME='WACT' and WIML_TABLEID = WACT_ID and WIML_IMAGETYPE = 5) picture_count,
  --yque start
--
            (SELECT trunc( mod( (sysdate - yque_q_date)*24*60, 60 ) )
             FROM yres, ydet, yque
            WHERE     yres.YRES_CURRENT_YDET_ID = ydet.YDET_ID
                  AND ydet.YDET_DATE = (SELECT wgbs.wgbs_datevalue FROM wgbs)
                  AND ydet.YDET_YRMS_ID = wact_place_id
                  AND wact_place_type = 1
                  AND ROWNUM = 1
                  and YRES.YRES_ID = YQUE.YQUE_YRES_ID
                  AND (YRES.YRES_EXPARRTIME) =
                         (SELECT wgbs.wgbs_datevalue FROM wgbs))
             YQUE_MINUTES
--
--yque end  
  from wact,watg,wasc,waty,want, xcms n1,xcms n2, xcms n3, wala y3, (select wala_wact_id, wala_xcms_id from wala where wala_xcms_type=1 ) y1,
    (select  wala_wact_id,wala_xcms_id from wala  where wala_xcms_type=2) y2
  where wact_id = y1.wala_wact_id(+)
    and WATY.WATY_WATG_ID = WATG.WATG_ID 
    and WATG.WATG_ROLE = 6
    and wact_id = y2.wala_wact_id(+)
    and wact_id = y3.wala_wact_id
    and y3.wala_xcms_id=n3.xcms_id
    and n3.xcms_type=3
    and wact_waty_id=waty_id(+)
    and nvl(wact_todo,0)=0
    and y1.wala_xcms_id=n1.xcms_id(+)
    and y2.wala_xcms_id=n2.xcms_id(+)
    and wact_want_id=want_id(+)
    and wact_wasc_id=wasc_id(+)
  Order By Wact_StartTime desc,Wact_id,wala_primary desc;
  
  Create or Replace Force View V8_REP_MAINTENANCE_PICTURES AS
  SELECT WIML_ID, WIML_DESCRIPTION, WIML_TABLEID, WIML_WIMG_ID, WIMG_IMPORTDATE
                 FROM WIML,WIMG
                WHERE     WIML_TABLENAME = 'WACT'
                      AND WIML_IMAGETYPE = 5
                    AND WIML_WIMG_ID = WIMG_ID;

CREATE OR REPLACE VIEW V8_REP_AR_AGING_DETAILED
AS 
select 
ACMP_ID, ACMP_NUMBER, MainAINV_ID Ainv_Id, PostDate, PCDate, trunc(TransferDate) TransferDate, ClosedDate, DebitAmount, CreditAmount, OpenBalance, AINV_CD, AACC_ID, AACC_NUMBER, AACC_ATYP_ID, AccountName, REMARKS, nvl(GuestName, AINV_DESCRIPT) GuestName, DepartmentId,  Ainv_Transfer_Info, Compression, ZPOS_ID 
,InvoiceNumber 
,0 InvoiceType 
,ainv_descript Description
,(select AACC.AACC_CREDITLIMIT from aacc where aacc_id=V8_AR_TRANS_COMPRESSED.AACC_ID) CREDITLIMIT
 from 
V8_AR_TRANS_COMPRESSED
 where 
 nvl(OpenBalance,0) <> 0
union
 select 
ACMP_ID, ACMP_NUMBER, MainAINV_ID Ainv_Id, PostDate, PCDate, trunc(TransferDate) TransferDate, ClosedDate, DebitAmount, CreditAmount, OpenBalance, AINV_CD, AACC_ID, AACC_NUMBER, AACC_ATYP_ID, AccountName, REMARKS, nvl(GuestName, AINV_DESCRIPT) GuestName, DepartmentId,  Ainv_Transfer_Info, Compression, ZPOS_ID 
,InvoiceNumber 
,0 InvoiceType 
,ainv_descript Description
,(select AACC.AACC_CREDITLIMIT from aacc where aacc_id=V8_AR_TRANS_NOT_COMPRESSED.AACC_ID) CREDITLIMIT
 from 
V8_AR_TRANS_NOT_COMPRESSED
 where 
 nvl(OpenBalance,0) <> 0;
			
CREATE OR REPLACE VIEW V8_BOF_OF_ADP
AS 
/*View for export advanced deposits Oracle Finance - Alshaya project
Luc Swankaert
09/06/2015 */
SELECT    COUNTRY
       || ','
       || LOCATION
       || ','
       || BUDGET_CCID
       || ','
       || BRAND
       || ','
       || TO_CHAR (TRANSACTION_DATE, 'DD/MM/YYYY')
       || ','
       || TO_CHAR (INVOICE_AMOUNT, 'fm9999999.009')
       || ','
       || CURRENCY_CODE
       || ','
       || CONVERSION_TYPE
       || ','
       || TO_CHAR (CONVERSION_RATE, 'fm9999990.00999')
       || ','
       || TO_CHAR (TRANSACTION_DATE, 'DD/MM/YYYY')
       || ','
       || PAYMENT_TYPE_SHORT
       || ','
       || ORACLE_CUST_NUMBER
       || ','
       || ORACLE_SITE_NUMBER
       || ','
       || SOURCE
       || ','
       || TO_CHAR (SYSDATE, 'DD/MM/YYYY')
       || ','
       || CREATED_BY
          CAPTURE,
          V.*
 from 
(SELECT NVL (
                  (SELECT wuss_value
                     FROM wuss
                    WHERE wuss_name = 'UDEF_OF_COUNTRY'),
                  'Please add user defined global setting: UDEF_OF_COUNTRY and fill out value for COUNTRY')
                  COUNTRY,
               NVL (
                  (SELECT wuss_value
                     FROM wuss
                    WHERE wuss_name = 'UDEF_OF_LOCATION'),
                  'Please add user defined global setting: UDEF_OF_LOCATION and fill out value for LOCATION')
                  LOCATION,
               NVL (
                  (SELECT wuss_value
                     FROM wuss
                    WHERE wuss_name = 'UDEF_OF_BUDGET_CCID'),
                  'Please add user defined global setting: UDEF_OF_BUDGET_CCID and fill out value for BUDGET_CCID')
                  BUDGET_CCID,
               NVL (
                  (SELECT wuss_value
                     FROM wuss
                    WHERE wuss_name = 'UDEF_OF_BRAND'),
                  'Please add user defined global setting: UDEF_OF_BRAND and fill out value for BRAND')
                  BRAND,
               NVL (
                  (SELECT wuss_value
                     FROM wuss
                    WHERE wuss_name = 'UDEF_OF_SOURCE'),
                  'Please add user defined global setting: UDEF_OF_SOURCE and fill out value for SOURCE')
                  SOURCE,
               z.zpos_id,
               z.zpos_cdt,
               z.zpos_zfac_id ZFAC_ID,
               z.zpos_zwin_id ZWIN_ID,
               Z.ZPOS_POSTDATE TRANSACTION_DATE,
               Z.ZPOS_GROSSFOREIGNPRICE INVOICE_AMOUNT,
               NVL (
                  (SELECT zcur.zcur_iso3
                     FROM zcur
                    WHERE z.ZPOS_ZCUR_ID = ZCUR.ZCUR_ID),
                  (SELECT ZCUR.ZCUR_ISO3
                     FROM zcur, wuss
                    WHERE     ZCUR.ZCUR_ID = WUSS.WUSS_VALUE
                          AND wuss_name = 'BaseCurrency'))
                  CURRENCY_CODE,
               (SELECT 'USER' FROM DUAL) CONVERSION_TYPE,
               Z.ZPOS_EXCHANGERATE CONVERSION_RATE,
               zdco.zdco_longdesc PAYMENT_TYPE_LONG,
               zdco.zdco_shortdesc PAYMENT_TYPE_SHORT,
               (SELECT AACC.AACC_NUMBER
                  FROM AACC
                 WHERE AACC.AACC_XCMS_ID = ZWIN_INVOICE_TO_XCMS_ID)
                  ORACLE_CUST_NUMBER,
               (SELECT AACC.AACC_SITE_NUMBER
                  FROM AACC
                 WHERE AACC.AACC_XCMS_ID = ZWIN_INVOICE_TO_XCMS_ID)
                  ORACLE_SITE_NUMBER,
               Z.ZPOS_PCDATE CREATION_DATE,
               (SELECT xced.xced_loginname
                  FROM xced
                 WHERE xced.xced_id = z.zpos_user_xcms_id)
                  CREATED_BY,
               (  SELECT MAX (zinv.ZINV_NUMBER)
                    FROM zinv, zpil
                   WHERE     zinv.ZINV_ID = zpil.ZPIL_ZINV_ID
                         AND zpil.ZPIL_ZPOS_ID = z.ZPOS_ID
                GROUP BY z.ZPOS_ID)
                  ZINV_NUMBER,
               zdco.zdco_id,
               ZWIN.ZWIN_INVOICE_TO_XCMS_ID,
               Z.ZPOS_SUMUPAMOUNT BASE_AMOUNT,
               (SELECT TRIM (xcms.XCMS_NAME3 || ' ' || xcms.XCMS_NAME1)
                  FROM xcms
                 WHERE xcms.XCMS_ID = ZWIN.ZWIN_INVOICE_TO_XCMS_ID)
                  AccountName
          FROM zpos z,
               zdco,
               zwin,
               ybom
         WHERE     z.ZPOS_ZWIN_ID = zwin.ZWIN_ID
               AND z.zpos_zdco_id = zdco.zdco_id
               AND z.zpos_zfac_id = ybom.ybom_zfac_id
               AND z.zpos_cdt = 5
               AND Z.ZPOS_PAIDOUT <> 1)v;

			   
CREATE OR REPLACE VIEW V8_BOF_OF_ADP_HEADER
AS 
SELECT    'COUNTRY'
       || ','
       || 'LOCATION'
       || ','
       || 'BUDGET CCID'
       || ','
       || 'BRAND'
       || ','
       || 'TRANSACTION_DATE'
       || ','
       || 'INVOICE_AMOUNT'
       || ','
       || 'CURRENCY_CODE'
       || ','
       || 'CONVERSION_TYPE'
       || ','
       || 'CONVERSION_RATE'
       || ','
       || 'CONVERSION_DATE'
       || ','
       || 'PAYMENT_TYPE'
       || ',' 
       || 'ORACLE_CUST_NUMBER'
       || ','
       || 'ORACLE_SITE_NUMBER'
       || ','
       || 'SOURCE'
       || ','
       || 'CREATION_DATE'
       || ','
       || 'CREATED_BY' CAPTURE                   
  FROM DUAL;

CREATE OR REPLACE FORCE VIEW V8_REP_ZPOS_SECURITY_DEPOSIT
AS 
SELECT
  z.ZPOS_ID ZPOS_ID,
  z.ZPOS_POSTDATE ZPOS_POSTDATE,
  z.ZPOS_PCDATE ZPOS_PCDATE,
  ZDCO.ZDCO_ID ZDCO_ID,
  ZDCO.ZDCO_NUMERICDESC ZDCO_NUMERICDESC,
  ZDCO.ZDCO_LONGDESC ZDCO_LONGDESC,
  z.ZPOS_DESCRIPT ZPOS_DESCRIPT,
  z.ZPOS_COMMENT ZPOS_COMMENT,
  z.ZPOS_PHONENR ZPOS_PHONENR,
  z.ZPOS_POSCHECK_NR ZPOS_POSCHECK_NR,
  z.ZPOS_PAIDOUT ZPOS_PAIDOUT,
  z.ZPOS_EXCHANGERATE ZPOS_EXCHANGERATE,
  ZDCO.ZDCO_NONHTLREV ZDCO_NONHTLREV,
  ZDCO.ZDCO_STATS_TYPE ZDCO_STATS_TYPE,
  ZDCO.ZDCO_PAYM_TYPE ZDCO_PAYM_TYPE,
  z.ZPOS_ZCAS_ID ZCAS_ID,
  ZDCO.ZDCO_ZDCG_ID ZDCG_ID,
  z.ZPOS_ZDC2_ID ZDC2_ID,
  (SELECT ZDCG.ZDCG_LONGDESC FROM ZDCG WHERE ZDCG.ZDCG_ID=ZDCO.ZDCO_ZDCG_ID) ZDCG_LONGDESC,   
  (SELECT ZCAS.ZCAS_NUMBER FROM ZCAS WHERE ZCAS.ZCAS_ID=z.ZPOS_ZCAS_ID) ZCAS_NUMBER,
  (DECODE(z.ZPOS_ZCAS_ID,(SELECT WUSS.WUSS_VALUE FROM WUSS WHERE WUSS.WUSS_XCMS_ID=0 AND WUSS.WUSS_NAME='NightAuditCashier'),1,0)) NA_POSTING,
  NVL((SELECT SUM(DECODE(ZPOS.ZPOS_CDT,5,(DECODE(ZPOS.ZPOS_PAIDOUT,1,(ZPOS.ZPOS_UNITPRICE*ZPOS.ZPOS_QUANTITY),(ZPOS.ZPOS_UNITPRICE*ZPOS.ZPOS_QUANTITY))),ZPOS.ZPOS_GROSSUNITPRICE*ZPOS.ZPOS_QUANTITY)) FROM ZPOS WHERE ZPOS.ZPOS_TAXLINK_ID=z.ZPOS_TAXLINK_ID GROUP BY ZPOS.ZPOS_TAXLINK_ID),0) GROSS_AMOUNT,
  NVL((SELECT SUM(DECODE(ZPOS.ZPOS_CDT,5,(DECODE(ZPOS.ZPOS_PAIDOUT,1,(ZPOS.ZPOS_UNITPRICE*ZPOS.ZPOS_QUANTITY),(ZPOS.ZPOS_UNITPRICE*ZPOS.ZPOS_QUANTITY))),ZPOS.ZPOS_UNITPRICE*ZPOS.ZPOS_QUANTITY)) FROM ZPOS WHERE ZPOS.ZPOS_CDT<>2 AND ZPOS.ZPOS_TAXLINK_ID=z.ZPOS_TAXLINK_ID GROUP BY ZPOS.ZPOS_TAXLINK_ID),0) NET_AMOUNT,
  NVL((SELECT SUM(DECODE(ZPOS.ZPOS_CDT,5,(DECODE(ZPOS.ZPOS_PAIDOUT,1,(ZPOS.ZPOS_UNITPRICE*ZPOS.ZPOS_QUANTITY),(ZPOS.ZPOS_UNITPRICE*ZPOS.ZPOS_QUANTITY))),ZPOS.ZPOS_UNITPRICE*ZPOS.ZPOS_QUANTITY)) FROM ZPOS WHERE ZPOS.ZPOS_CDT=2 AND ZPOS.ZPOS_TAXLINK_ID=z.ZPOS_TAXLINK_ID GROUP BY ZPOS.ZPOS_TAXLINK_ID),0) TAX_AMOUNT,
  NVL((SELECT SUM(DECODE(ZPOS.ZPOS_CDT,5,(DECODE(ZPOS.ZPOS_PAIDOUT,1,0,0)),(ZPOS.ZPOS_GROSSUNITPRICE*ZPOS.ZPOS_QUANTITY))) FROM ZPOS WHERE ZPOS.ZPOS_TAXLINK_ID=z.ZPOS_TAXLINK_ID GROUP BY ZPOS.ZPOS_TAXLINK_ID),0) REV_GROSS_AMOUNT,
  NVL((SELECT SUM(DECODE(ZPOS.ZPOS_CDT,5,(DECODE(ZPOS.ZPOS_PAIDOUT,1,0,0)),(ZPOS.ZPOS_UNITPRICE*ZPOS.ZPOS_QUANTITY))) FROM ZPOS WHERE ZPOS.ZPOS_CDT<>2 AND ZPOS.ZPOS_TAXLINK_ID=z.ZPOS_TAXLINK_ID GROUP BY ZPOS.ZPOS_TAXLINK_ID),0) REV_NET_AMOUNT,
  NVL((SELECT SUM(DECODE(ZPOS.ZPOS_CDT,5,(DECODE(ZPOS.ZPOS_PAIDOUT,1,(ZPOS.ZPOS_UNITPRICE*ZPOS.ZPOS_QUANTITY),(ZPOS.ZPOS_UNITPRICE*ZPOS.ZPOS_QUANTITY))),0)) FROM ZPOS WHERE ZPOS.ZPOS_TAXLINK_ID=z.ZPOS_TAXLINK_ID GROUP BY ZPOS.ZPOS_TAXLINK_ID),0) PAY_AMOUNT,
  NVL((SELECT SUM(DECODE(ZPOS.ZPOS_CDT,5,(DECODE(ZPOS.ZPOS_PAIDOUT,1,(ZPOS.ZPOS_FOREIGNPRICE*ZPOS.ZPOS_QUANTITY),(ZPOS.ZPOS_FOREIGNPRICE*ZPOS.ZPOS_QUANTITY))),0)) FROM ZPOS WHERE ZPOS.ZPOS_TAXLINK_ID=z.ZPOS_TAXLINK_ID AND ZDCO.ZDCO_PAYM_TYPE=4 GROUP BY ZPOS.ZPOS_TAXLINK_ID),0) FOREIGN_PAY_AMOUNT,
  NVL((SELECT SUM(DECODE(ZPOS.ZPOS_CDT,5,(DECODE(ZPOS.ZPOS_PAIDOUT,1,(-ZPOS.ZPOS_UNITPRICE*ZPOS.ZPOS_QUANTITY),(-ZPOS.ZPOS_UNITPRICE*ZPOS.ZPOS_QUANTITY))),ZPOS.ZPOS_GROSSUNITPRICE*ZPOS.ZPOS_QUANTITY)) FROM ZPOS WHERE ZPOS.ZPOS_TAXLINK_ID=z.ZPOS_TAXLINK_ID GROUP BY ZPOS.ZPOS_TAXLINK_ID),0) GROSS_AMOUNT_PAY_MINUS,
  NVL((SELECT SUM(DECODE(ZPOS.ZPOS_CDT,5,(DECODE(ZPOS.ZPOS_PAIDOUT,1,(-ZPOS.ZPOS_UNITPRICE*ZPOS.ZPOS_QUANTITY),(-ZPOS.ZPOS_UNITPRICE*ZPOS.ZPOS_QUANTITY))),ZPOS.ZPOS_UNITPRICE*ZPOS.ZPOS_QUANTITY)) FROM ZPOS WHERE ZPOS.ZPOS_CDT<>2 AND ZPOS.ZPOS_TAXLINK_ID=z.ZPOS_TAXLINK_ID GROUP BY ZPOS.ZPOS_TAXLINK_ID),0) NET_AMOUNT_PAY_MINUS,
  z.ZPOS_CDT ZPOS_CDT,
  NVL(z.zpos_ledgerstatus,0) ZPOS_LEDGERSTATUS,
  (CASE WHEN (SELECT ZLSM.zlsm_fromledger FROM ZLSM WHERE ZLSM.zlsm_zpos_id=z.zpos_id) IS NOT NULL THEN (SELECT ZLSM.zlsm_fromledger FROM ZLSM WHERE ZLSM.zlsm_zpos_id=z.zpos_id) ELSE NVL(z.zpos_ledgerstatus,0) END) org_zpos_ledgerstatus,
  z.ZPOS_ZWIN_ID ZPOS_ZWIN_ID,
  (SELECT ZWIN.ZWIN_WINDOW FROM ZWIN WHERE ZWIN.ZWIN_ID=z.ZPOS_ZWIN_ID) ZWIN_WINDOW,
  z.ZPOS_USER_XCMS_ID ZPOS_USER_XCMS_ID,
  (SELECT XCED.XCED_LOGINNAME FROM XCED WHERE XCED.XCED_ID=z.ZPOS_USER_XCMS_ID) USER_LOGINNAME,
  (SELECT v8_rep_name.NAME2 FROM v8_rep_name WHERE v8_rep_name.XCMS_ID=z.ZPOS_USER_XCMS_ID) USER_FULLNAME,
  z.ZPOS_XCMS_ID XCMS_ID,
  (SELECT v8_rep_name.NAME2 FROM v8_rep_name, zwin WHERE v8_rep_name.XCMS_ID=ZWIN.ZWIN_INVOICE_TO_XCMS_ID and ZWIN.ZWIN_ID=z.ZPOS_ZWIN_ID) GUESTNAME,
  (select ZWIN.ZWIN_INVOICE_TO_XCMS_ID from zwin where ZWIN.ZWIN_ID=Z.ZPOS_ZWIN_ID) INVOICE_TO_XCMS_ID,
  (DECODE(NVL(z.ZPOS_ZFAC_ID,0),0,z.ZPOS_YRES_ID,z.ZPOS_ZFAC_ID)) YRES_OR_ZFAC_ID,
  z.ZPOS_YRES_ID ZPOS_YRES_ID,
  z.ZPOS_ZFAC_ID ZPOS_ZFAC_ID,
  (DECODE((SELECT NVL(ZWIN.ZWIN_ZFAC_ID,0) FROM ZWIN WHERE ZWIN.ZWIN_ID=z.ZPOS_ZWIN_ID),0,(SELECT v8_rep_name.NAME2 FROM v8_rep_name,YRES,ZWIN WHERE v8_rep_name.XCMS_ID=YRES.YRES_XCMS_ID AND YRES.YRES_ID=ZWIN.ZWIN_YRES_ID AND ZWIN.ZWIN_ID=z.ZPOS_ZWIN_ID),(SELECT v8_rep_name.NAME2 FROM v8_rep_name,ZFAC,ZWIN WHERE v8_rep_name.XCMS_ID=ZFAC.ZFAC_XCMS_ID AND ZFAC.ZFAC_ID=ZWIN.ZWIN_ZFAC_ID AND ZWIN.ZWIN_ID=z.ZPOS_ZWIN_ID))) REAL_GUESTNAME,
  (SELECT v8_rep_name.NAME2 FROM v8_rep_name,YRES,ZWIN WHERE v8_rep_name.XCMS_ID=YRES.YRES_XCMS_ID AND YRES.YRES_ID=ZWIN.ZWIN_YRES_ID AND ZWIN.ZWIN_ID=z.ZPOS_ZWIN_ID) YRES_GUESTNAME,
  (SELECT v8_rep_name.NAME2 FROM v8_rep_name,ZFAC,ZWIN WHERE v8_rep_name.XCMS_ID=ZFAC.ZFAC_XCMS_ID AND ZFAC.ZFAC_ID=ZWIN.ZWIN_ZFAC_ID AND ZWIN.ZWIN_ID=z.ZPOS_ZWIN_ID) ZFAC_GUESTNAME,
  (DECODE(NVL(z.ZPOS_ZFAC_ID,0),
       0,(DECODE((SELECT YRMS.YRMS_SHORTDESC FROM YRMS,YDET WHERE YRMS.YRMS_ID=YDET.YDET_YRMS_ID AND YDET.YDET_DATE=z.ZPOS_POSTDATE AND YDET.YDET_YRES_ID=z.ZPOS_YRES_ID),
                   NULL,(SELECT YRMS.YRMS_SHORTDESC FROM YRMS,YDET WHERE YRMS.YRMS_ID=YDET.YDET_YRMS_ID AND YDET.YDET_DATE+1=z.ZPOS_POSTDATE AND YDET.YDET_YRES_ID=z.ZPOS_YRES_ID),
                             (SELECT YRMS.YRMS_SHORTDESC FROM YRMS,YDET WHERE YRMS.YRMS_ID=YDET.YDET_YRMS_ID AND YDET.YDET_DATE=z.ZPOS_POSTDATE AND YDET.YDET_YRES_ID=z.ZPOS_YRES_ID))),
           (SELECT DECODE(NVL(ZFAC.ZFAC_ZFAN_ID,0),
                                0,TO_CHAR(ZFAC.ZFAC_AUTONUMBER),
                                   (SELECT ZFAN.ZFAN_LONGDESC FROM ZFAN WHERE ZFAN.ZFAN_ID = ZFAC.ZFAC_ZFAN_ID )) 
            FROM ZFAC WHERE ZFAC.ZFAC_ID=z.ZPOS_ZFAC_ID ))) YRMS_OR_ZFAC_DESC,  
  (DECODE((SELECT YRMS.YRMS_SHORTDESC FROM YRMS,YDET WHERE YRMS.YRMS_ID=YDET.YDET_YRMS_ID AND YDET.YDET_DATE=z.ZPOS_POSTDATE AND YDET.YDET_YRES_ID=z.ZPOS_YRES_ID),NULL,(SELECT YRMS.YRMS_SHORTDESC FROM YRMS,YDET WHERE YRMS.YRMS_ID=YDET.YDET_YRMS_ID AND YDET.YDET_DATE+1=z.ZPOS_POSTDATE AND YDET.YDET_YRES_ID=z.ZPOS_YRES_ID),(SELECT YRMS.YRMS_SHORTDESC FROM YRMS,YDET WHERE YRMS.YRMS_ID=YDET.YDET_YRMS_ID AND YDET.YDET_DATE=z.ZPOS_POSTDATE AND YDET.YDET_YRES_ID=z.ZPOS_YRES_ID))) YRMS_SHORTDESC,
  (SELECT ZFAC.ZFAC_LONGDESC FROM ZFAC WHERE ZFAC.ZFAC_ID=z.ZPOS_ZFAC_ID) ZFAC_LONGDESC,
  (SELECT v8_rep_name.NAME2 FROM v8_rep_name WHERE v8_rep_name.XCMS_ID=z.ZPOS_ORIGINATED_XCMS_ID) ORI_GUESTNAME,
  (DECODE(NVL(z.ZPOS_ORIGINATED_ZFAC_ID,0),0,z.ZPOS_ORIGINATED_YRES_ID,z.ZPOS_ORIGINATED_ZFAC_ID)) ORI_YRES_OR_ZFAC_ID,
  z.ZPOS_ORIGINATED_YRES_ID ZPOS_ORIGINATED_YRES_ID,
  z.ZPOS_ORIGINATED_ZFAC_ID ZPOS_ORIGINATED_ZFAC_ID,
   (DECODE(NVL(z.ZPOS_ORIGINATED_ZFAC_ID,0),
        0,(DECODE((SELECT YRMS.YRMS_SHORTDESC FROM YRMS,YDET WHERE YRMS.YRMS_ID=YDET.YDET_YRMS_ID AND YDET.YDET_DATE=z.ZPOS_POSTDATE AND YDET.YDET_YRES_ID=z.ZPOS_ORIGINATED_YRES_ID),
                 NULL,(SELECT YRMS.YRMS_SHORTDESC FROM YRMS,YDET WHERE YRMS.YRMS_ID=YDET.YDET_YRMS_ID AND YDET.YDET_DATE+1=z.ZPOS_POSTDATE AND YDET.YDET_YRES_ID=z.ZPOS_ORIGINATED_YRES_ID),
                            (SELECT YRMS.YRMS_SHORTDESC FROM YRMS,YDET WHERE YRMS.YRMS_ID=YDET.YDET_YRMS_ID AND YDET.YDET_DATE=z.ZPOS_POSTDATE AND YDET.YDET_YRES_ID=z.ZPOS_ORIGINATED_YRES_ID))),
           (SELECT DECODE(NVL(ZFAC.ZFAC_ZFAN_ID,0),
                                0,TO_CHAR(ZFAC.ZFAC_AUTONUMBER),
                                   (SELECT ZFAN.ZFAN_LONGDESC FROM ZFAN WHERE ZFAN.ZFAN_ID = ZFAC.ZFAC_ZFAN_ID )) 
            FROM ZFAC WHERE ZFAC.ZFAC_ID=z.ZPOS_originated_ZFAC_ID ))) ORI_YRMS_OR_ZFAC_DESC, 
--
  (DECODE(NVL(z.ZPOS_ORIGINATED_ZFAC_ID,0),
        0,(DECODE((SELECT YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID FROM YCAT,YDET WHERE YCAT.YCAT_ID=YDET.YDET_YCAT_ID AND YDET.YDET_DATE=z.ZPOS_POSTDATE AND YDET.YDET_YRES_ID=z.ZPOS_ORIGINATED_YRES_ID),
                 NULL,(SELECT YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID FROM YCAT,YDET WHERE YCAT.YCAT_ID=YDET.YDET_YCAT_ID AND YDET.YDET_DATE+1=z.ZPOS_POSTDATE AND YDET.YDET_YRES_ID=z.ZPOS_ORIGINATED_YRES_ID),
                            (SELECT YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID FROM YCAT,YDET WHERE YCAT.YCAT_ID=YDET.YDET_YCAT_ID AND YDET.YDET_DATE=z.ZPOS_POSTDATE AND YDET.YDET_YRES_ID=z.ZPOS_ORIGINATED_YRES_ID))),
           (SELECT ZFAG.ZFAG_YCAD_ID FROM ZFAG,ZFAC 
            WHERE ZFAG.ZFAG_ID=ZFAC.ZFAC_ZFAG_ID AND ZFAC.ZFAC_ID=z.ZPOS_originated_ZFAC_ID ))) ORI_YCAD_ID,            
--             
  (DECODE((SELECT YRMS.YRMS_SHORTDESC FROM YRMS,YDET WHERE YRMS.YRMS_ID=YDET.YDET_YRMS_ID AND YDET.YDET_DATE=z.ZPOS_POSTDATE AND YDET.YDET_YRES_ID=z.ZPOS_ORIGINATED_YRES_ID),NULL,(SELECT YRMS.YRMS_SHORTDESC FROM YRMS,YDET WHERE YRMS.YRMS_ID=YDET.YDET_YRMS_ID AND YDET.YDET_DATE+1=z.ZPOS_POSTDATE AND YDET.YDET_YRES_ID=z.ZPOS_ORIGINATED_YRES_ID),(SELECT YRMS.YRMS_SHORTDESC FROM YRMS,YDET WHERE YRMS.YRMS_ID=YDET.YDET_YRMS_ID AND YDET.YDET_DATE=z.ZPOS_POSTDATE AND YDET.YDET_YRES_ID=z.ZPOS_ORIGINATED_YRES_ID))) ORI_YRMS_SHORTDESC,
  (SELECT ZFAC.ZFAC_LONGDESC FROM ZFAC WHERE ZFAC.ZFAC_ID=z.ZPOS_ORIGINATED_ZFAC_ID) ORI_ZFAC_LONGDESC,
  (SELECT MAX(ZINV.ZINV_NUMBER) FROM ZINV,ZPIL WHERE ZINV.ZINV_ID=ZPIL.ZPIL_ZINV_ID AND ZPIL.ZPIL_ZPOS_ID=z.ZPOS_ID GROUP BY z.ZPOS_ID) MAX_ZINV_NUMBER,
  (SELECT MIN(ZINV.ZINV_NUMBER) FROM ZINV,ZPIL WHERE ZINV.ZINV_ID=ZPIL.ZPIL_ZINV_ID AND ZPIL.ZPIL_ZPOS_ID=z.ZPOS_ID GROUP BY z.ZPOS_ID) MIN_ZINV_NUMBER, 
   z.ZPOS_INTERNALSPLIT_ZPOS_ID ZPOS_INTERNALSPLIT_ZPOS_ID,   
  (DECODE(z.ZPOS_ID,z.ZPOS_INTERNALSPLIT_ZPOS_ID,1,0)) MASTER_SPLIT_POST,
  (DECODE(z.ZPOS_ID,z.ZPOS_INTERNALSPLIT_ZPOS_ID,NULL,(SELECT zdco_s.ZDCO_NUMERICDESC FROM ZDCO zdco_s,ZPOS WHERE zdco_s.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID AND ZPOS.ZPOS_ID=z.ZPOS_INTERNALSPLIT_ZPOS_ID))) MASTER_POST_ZDCO_NUMERICDESC,
  (DECODE(z.ZPOS_ID,z.ZPOS_INTERNALSPLIT_ZPOS_ID,NULL,(SELECT zdco_s.ZDCO_LONGDESC FROM ZDCO zdco_s,ZPOS WHERE zdco_s.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID AND ZPOS.ZPOS_ID=z.ZPOS_INTERNALSPLIT_ZPOS_ID))) MASTER_POST_ZDCO_LONGDESC,
  (DECODE(SIGN(z.ZPOS_UNITPRICE*z.ZPOS_QUANTITY),-1,DECODE(z.ZPOS_PAIDOUT,0,1,0),1,DECODE(z.ZPOS_PAIDOUT,1,1,0))) INVERS_POSTING,
  z.zpos_zcur_id,
  NVL((SELECT ZFAG.ZFAG_TYPE FROM ZFAC,ZFAG WHERE ZFAG.ZFAG_ID=ZFAC.ZFAC_ZFAG_ID AND ZFAC.ZFAC_ID=z.ZPOS_ZFAC_ID),0) ZFAG_TYPE,
  (case when NVL((SELECT ZFAG.ZFAG_TYPE FROM ZFAC,ZFAG WHERE ZFAG.ZFAG_ID=ZFAC.ZFAC_ZFAG_ID AND ZFAC.ZFAC_ID=z.ZPOS_ORIGINATED_ZFAC_ID),0)=12 then 3 when ZDCO.ZDCO_DEPOSIT=1 then 2 else 1 end) POSTING_TYPE,
  (case when NVL((SELECT ZFAG.ZFAG_TYPE FROM ZFAC,ZFAG WHERE ZFAG.ZFAG_ID=ZFAC.ZFAC_ZFAG_ID AND ZFAC.ZFAC_ID=z.ZPOS_ORIGINATED_ZFAC_ID),0)=12 then 'Voucher' when ZDCO.ZDCO_DEPOSIT=1 then 'Deposit' else 'Hotel' end) POSTING_TYPE_DESC
FROM
  ZPOS z,WDAT,ZDCO
WHERE
  z.ZPOS_CDT IN (1,5) AND
  z.ZPOS_POSTDATE=WDAT.WDAT_DATE(+) AND
  z.ZPOS_ZDCO_ID=ZDCO.ZDCO_ID AND
  ZDCO.ZDCO_SECURITYDEPOSIT=1;

CREATE OR REPLACE VIEW V8_REP_COVERS
AS 
select 
zpos_postdate ZPOS_POSTDATE, 
zpos_zcas_id ZCAS_ID, 
zdmg_id ZDMG_ID, 
zdcg_id ZDCG_ID, 
zdcg_longdesc, --CvZ new field 20.05.2015
zpos_poscheck_nr ZPOS_CHECK_NR, 
sum(zpos_sumupamount) AMOUNT, 
avg(zpos_covers) COVERS
 from zpos, zdco, zdcg, zdmg
 where zpos_zdco_id = zdco_id and
         zdco_zdcg_id = zdcg_id and
         zdcg_zdmg_id = zdmg_id and
         zpos_internalsplit_zpos_id = zpos_id and
         zpos_cdt = 1
         group by zpos_poscheck_nr, zpos_postdate, zpos_zcas_id, zdmg_id, zdcg_id, zdcg_longdesc;

create or replace view V8_Rep_TrendperMonth as
select 
  TO_CHAR(YTRD_CALCFORDATE,'yyyy_mm') MonthYear,
  sum(a.NOOFROMS)        NOOFROOMS,
  sum(a.DEFNOOFROOMS)    DEFNOFROOMS,
  sum(a.DEFNOOFROOMS7 )  DEFNOFROOMS7,
  sum(a.DEFNOOFROOMS14 ) DEFNOFROOMS14,
  sum(a.DEFNOOFROOMS21 ) DEFNOFROOMS21,
  sum(a.DEFNOOFROOMS28 ) DEFNOFROOMS28,
  sum(a.DefNoOfRooms1Months ) DefNoOfRooms1Months,
  sum(a.DefNoOfRooms3Months ) DefNoOfRooms3Months,
  sum(a.DefNoOfRooms6Months ) DefNoOfRooms6Months,
  sum(a.DefNoOfRooms12Months ) DefNoOfRooms12Months,        
  round((sum(a.DEFNOOFROOMS) * 100)/sum(a.NOOFROMS),2) FullPERC_DEFNOFROOMS,
  round((sum(a.DEFNOOFROOMS7) * 100)/sum(a.NOOFROMS),2) FullPERC_DEFNOFROOMS7,  
  round((sum(a.DEFNOOFROOMS14) * 100)/sum(a.NOOFROMS),2) FullPERC_DEFNOFROOMS14,
  round((sum(a.DEFNOOFROOMS21) * 100)/sum(a.NOOFROMS),2) FullPERC_DEFNOFROOMS21,
  round((sum(a.DEFNOOFROOMS28) * 100)/sum(a.NOOFROMS),2) FullPERC_DEFNOFROOMS28,
  round((sum(a.DefNoOfRooms1Months) * 100)/sum(a.NOOFROMS),2) FullPERC_DefNoOfRooms1Months,
  round((sum(a.DefNoOfRooms3Months) * 100)/sum(a.NOOFROMS),2) FullPERC_DefNoOfRooms3Months,
  round((sum(a.DefNoOfRooms6Months) * 100)/sum(a.NOOFROMS),2) FullPERC_DefNoOfRooms6Months,
  round((sum(a.DefNoOfRooms12Months) * 100)/sum(a.NOOFROMS),2) FullPERC_DefNoOfRooms12Months,       
--Tent 
  sum(a.TENTNOOFROOMS)    TENTNOFROOMS,
  sum(a.TENTNOOFROOMS7 )  TENTNOFROOMS7,
  sum(a.TENTNOOFROOMS14 ) TENTNOFROOMS14,
  sum(a.TENTNOOFROOMS21 ) TENTNOFROOMS21,
  sum(a.TENTNOOFROOMS28 ) TENTNOFROOMS28,
  sum(a.TENTNoOfRooms1Months ) TENTNoOfRooms1Months,
  sum(a.TENTNoOfRooms3Months ) TENTNoOfRooms3Months,
  sum(a.TENTNoOfRooms6Months ) TENTNoOfRooms6Months,
  sum(a.TENTNoOfRooms12Months ) TENTNoOfRooms12Months,        
  round((sum(a.TENTNOOFROOMS) * 100)/sum(a.NOOFROMS),2) FullPERC_TENTNOFROOMS,
  round((sum(a.TENTNOOFROOMS7) * 100)/sum(a.NOOFROMS),2) FullPERC_TENTNOFROOMS7,  
  round((sum(a.TENTNOOFROOMS14) * 100)/sum(a.NOOFROMS),2) FullPERC_TENTNOFROOMS14,
  round((sum(a.TENTNOOFROOMS21) * 100)/sum(a.NOOFROMS),2) FullPERC_TENTNOFROOMS21,
  round((sum(a.TENTNOOFROOMS28) * 100)/sum(a.NOOFROMS),2) FullPERC_TENTNOFROOMS28,
  round((sum(a.TENTNoOfRooms1Months) * 100)/sum(a.NOOFROMS),2) FullPERC_TENTNoOfRooms1Months,
  round((sum(a.TENTNoOfRooms3Months) * 100)/sum(a.NOOFROMS),2) FullPERC_TENTNoOfRooms3Months,
  round((sum(a.TENTNoOfRooms6Months) * 100)/sum(a.NOOFROMS),2) FullPERC_TENTNoOfRooms6Months,
  round((sum(a.TENTNoOfRooms12Months) * 100)/sum(a.NOOFROMS),2) FullPERC_TENTNoOfRooms12Months,      
-- Def + Tent 
  sum(a.DTNOOFROOMS)    DTNOFROOMS,
  sum(a.DTNOOFROOMS7 )  DTNOFROOMS7,
  sum(a.DTNOOFROOMS14 ) DTNOFROOMS14,
  sum(a.DTNOOFROOMS21 ) DTNOFROOMS21,
  sum(a.DTNOOFROOMS28 ) DTNOFROOMS28,
  sum(a.DTNoOfRooms1Months ) DTNoOfRooms1Months,
  sum(a.DTNoOfRooms3Months ) DTNoOfRooms3Months,
  sum(a.DTNoOfRooms6Months ) DTNoOfRooms6Months,
  sum(a.DTNoOfRooms12Months ) DTNoOfRooms12Months,        
  round((sum(a.DTNOOFROOMS) * 100)/sum(a.NOOFROMS),2) FullPERC_DTNOFROOMS,
  round((sum(a.DTNOOFROOMS7) * 100)/sum(a.NOOFROMS),2) FullPERC_DTNOFROOMS7,  
  round((sum(a.DTNOOFROOMS14) * 100)/sum(a.NOOFROMS),2) FullPERC_DTNOFROOMS14,
  round((sum(a.DTNOOFROOMS21) * 100)/sum(a.NOOFROMS),2) FullPERC_DTNOFROOMS21,
  round((sum(a.DTNOOFROOMS28) * 100)/sum(a.NOOFROMS),2) FullPERC_DTNOFROOMS28,
  round((sum(a.DTNoOfRooms1Months) * 100)/sum(a.NOOFROMS),2) FullPERC_DTNoOfRooms1Months,
  round((sum(a.DTNoOfRooms3Months) * 100)/sum(a.NOOFROMS),2) FullPERC_DTNoOfRooms3Months,
  round((sum(a.DTNoOfRooms6Months) * 100)/sum(a.NOOFROMS),2) FullPERC_DTNoOfRooms6Months,
  round((sum(a.DTNoOfRooms12Months) * 100)/sum(a.NOOFROMS),2) FullPERC_DTNoOfRooms12Months  
from 
V8_Rep_TrendperDay7 a
group by 
  TO_CHAR(YTRD_CALCFORDATE,'yyyy_mm')
  order by TO_CHAR(YTRD_CALCFORDATE,'yyyy_mm')
;  

CREATE OR REPLACE VIEW V8_STAT_SHIS_CHAN_DAY_LY_CAD
AS 
SELECT 
  WDAT_DATE WDAT_DATE, 
  CHANNEL_ID CHANNEL_ID, 
  YCAD_ID YCAD_ID,
  sum(NOOFROOMS) NOOFROOMS, 
  sum(ADULTNO) ADULTNO, 
  sum(KIDS) KIDS, 
  sum(ARRIVALS) ARRIVALS, 
  sum(DEPARTURES) DEPARTURES, 
  sum(ARR_ADULTNO)ARR_ADULTNO, 
  sum(DEP_ADULTNO) DEP_ADULTNO, 
  sum(ARR_KIDS)ARR_KIDS, 
  sum(DEP_KIDS)DEP_KIDS, 
  sum(NOSHOW_ROOM)NOSHOW_ROOM, 
  sum(CANCEL_ROOM)CANCEL_ROOM, 
  sum(NETROOMREV)NETROOMREV, 
  sum(NETFBREV)NETFBREV, 
  sum(NETMINIBARREV)NETMINIBARREV, 
  sum(NETTELEREV)NETTELEREV, 
  sum(NETMISCREV)NETMISCREV, 
  sum(NETREV)NETREV, 
  sum(GROSSROOMREV)GROSSROOMREV, 
  sum(GROSSFBREV)GROSSFBREV, 
  sum(GROSSMINIBARREV)GROSSMINIBARREV, 
  sum(GROSSTELEREV)GROSSTELEREV, 
  sum(GROSSMISCREV)GROSSMISCREV, 
  sum(GROSSREV)GROSSREV 
FROM 
( 
SELECT 
  wdat.WDAT_DATE WDAT_DATE, 
  yras.YRAS_ID CHANNEL_ID, 
  sum(decode(S.SHIS_YRES_RESSTATUS,3,0,decode(S.SHIS_DEP_ID,1,0,S.SHIS_YDET_NOOFROOMS))) NOOFROOMS,
  SUM(DECODE(S.SHIS_YRES_RESSTATUS,3,0,CASE WHEN S.SHIS_DEP_ID=1 AND S.SHIS_ARR_ID=1 THEN S.SHIS_YDET_ADULTNO WHEN S.SHIS_DEP_ID=1 AND S.SHIS_ARR_ID=0 THEN 0 WHEN NVL(S.SHIS_YDET_ID,0)=0 THEN 0 ELSE S.SHIS_YDET_ADULTNO END )) ADULTNO, 
  SUM(DECODE(S.SHIS_YRES_RESSTATUS,3,0,CASE WHEN S.SHIS_DEP_ID=1 AND S.SHIS_ARR_ID=1 THEN NVL(S.SHIS_TOTALYRGL_NUMBER,0) WHEN S.SHIS_DEP_ID=1 AND S.SHIS_ARR_ID=0 THEN 0 WHEN NVL(S.SHIS_YDET_ID,0)=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(decode(S.SHIS_YRES_RESSTATUS,3,(decode(S.SHIS_YRES_NOAVAILREASON,4,(decode(trunc(S.SHIS_YRES_EXPARRTIME),trunc(S.SHIS_WDAT_DATE),S.SHIS_YDET_NOOFROOMS,0)))),0)) NOSHOW_ROOM,
  sum(decode(S.SHIS_YRES_RESSTATUS,3,(decode(S.SHIS_YRES_NOAVAILREASON,3,(decode(trunc(S.SHIS_YRES_EXPARRTIME),trunc(S.SHIS_WDAT_DATE),S.SHIS_YDET_NOOFROOMS,0)))),0)) 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 ,
 YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID YCAD_ID
FROM 
  wdat, 
  shis S, 
  yras ,
  ycat
WHERE 
ADD_MONTHS(WDAT.WDAT_DATE,-12)=S.SHIS_WDAT_DATE and 
yras.YRAS_ID(+)=S.SHIS_CHANNEL_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 yras.YRAS_YRAC_ID=2
group by wdat.WDAT_DATE,yras.YRAS_ID, YCAT_SUM_ROOMTYPE_YCAD_ID) 
group by WDAT_DATE,CHANNEL_ID,YCAD_ID;

CREATE OR REPLACE VIEW V8_STAT_SHIS_CHAN_MONTH_LY_CAD
AS 
SELECT 
  WDAT_DATE WDAT_DATE, 
  CHANNEL_ID CHANNEL_ID, 
  YCAD_ID YCAD_ID,
  sum(NOOFROOMS) NOOFROOMS, 
  sum(ADULTNO) ADULTNO, 
  sum(KIDS) KIDS, 
  sum(ARRIVALS) ARRIVALS, 
  sum(DEPARTURES) DEPARTURES, 
  sum(ARR_ADULTNO) ARR_ADULTNO, 
  sum(DEP_ADULTNO) DEP_ADULTNO, 
  sum(ARR_KIDS) ARR_KIDS, 
  sum(DEP_KIDS) DEP_KIDS, 
  sum(NOSHOW_ROOM) NOSHOW_ROOM, 
  sum(CANCEL_ROOM) CANCEL_ROOM, 
  sum(NETROOMREV) NETROOMREV, 
  sum(NETFBREV) NETFBREV, 
  sum(NETMINIBARREV) NETMINIBARREV, 
  sum(NETTELEREV) NETTELEREV, 
  sum(NETMISCREV) NETMISCREV, 
  sum(NETREV) NETREV, 
  sum(GROSSROOMREV) GROSSROOMREV, 
  sum(GROSSFBREV) GROSSFBREV, 
  sum(GROSSMINIBARREV) GROSSMINIBARREV, 
  sum(GROSSTELEREV) GROSSTELEREV, 
  sum(GROSSMISCREV) GROSSMISCREV, 
  sum(GROSSREV) GROSSREV
FROM 
( 
SELECT 
  wdat.WDAT_DATE WDAT_DATE, 
  yras.YRAS_ID CHANNEL_ID, 
  YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID YCAD_ID,
  sum(decode(S.SHIS_YRES_RESSTATUS,3,0,decode(S.SHIS_DEP_ID,1,0,S.SHIS_YDET_NOOFROOMS))) NOOFROOMS,
  SUM(DECODE(S.SHIS_YRES_RESSTATUS,3,0,CASE WHEN S.SHIS_DEP_ID=1 AND S.SHIS_ARR_ID=1 THEN S.SHIS_YDET_ADULTNO WHEN S.SHIS_DEP_ID=1 AND S.SHIS_ARR_ID=0 THEN 0 WHEN NVL(S.SHIS_YDET_ID,0)=0 THEN 0 ELSE S.SHIS_YDET_ADULTNO END )) ADULTNO, 
  SUM(DECODE(S.SHIS_YRES_RESSTATUS,3,0,CASE WHEN S.SHIS_DEP_ID=1 AND S.SHIS_ARR_ID=1 THEN NVL(S.SHIS_TOTALYRGL_NUMBER,0) WHEN S.SHIS_DEP_ID=1 AND S.SHIS_ARR_ID=0 THEN 0 WHEN NVL(S.SHIS_YDET_ID,0)=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(decode(S.SHIS_YRES_RESSTATUS,3,(decode(S.SHIS_YRES_NOAVAILREASON,4,(decode(trunc(S.SHIS_YRES_EXPARRTIME),trunc(S.SHIS_WDAT_DATE),S.SHIS_YDET_NOOFROOMS,0)))),0)) NOSHOW_ROOM,
  sum(decode(S.SHIS_YRES_RESSTATUS,3,(decode(S.SHIS_YRES_NOAVAILREASON,3,(decode(trunc(S.SHIS_YRES_EXPARRTIME),trunc(S.SHIS_WDAT_DATE),S.SHIS_YDET_NOOFROOMS,0)))),0)) 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, 
  yras,
  ycat
WHERE 
ADD_MONTHS(ADD_MONTHS(LAST_DAY(WDAT.WDAT_DATE),-1)+1,-12)<=S.SHIS_WDAT_DATE and 
S.SHIS_WDAT_DATE<=ADD_MONTHS(WDAT.WDAT_DATE,-12) and 
yras.YRAS_ID(+)=S.SHIS_CHANNEL_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
yras.YRAS_YRAC_ID=2
group by wdat.WDAT_DATE,yras.YRAS_ID, YCAT_SUM_ROOMTYPE_YCAD_ID) 
group by WDAT_DATE,CHANNEL_ID,YCAD_ID;

CREATE OR REPLACE VIEW V8_STAT_SHIS_CHAN_YEAR_LY_CAD
AS 
SELECT 
  WDAT_DATE WDAT_DATE, 
  CHANNEL_ID CHANNEL_ID, 
  YCAD_ID YCAD_ID,
  sum(NOOFROOMS) NOOFROOMS, 
  sum(ADULTNO) ADULTNO, 
  sum(KIDS) KIDS, 
  sum(ARRIVALS) ARRIVALS, 
  sum(DEPARTURES) DEPARTURES, 
  sum(ARR_ADULTNO) ARR_ADULTNO, 
  sum(DEP_ADULTNO) DEP_ADULTNO, 
  sum(ARR_KIDS) ARR_KIDS, 
  sum(DEP_KIDS) DEP_KIDS, 
  sum(NOSHOW_ROOM) NOSHOW_ROOM, 
  sum(CANCEL_ROOM) CANCEL_ROOM, 
  sum(NETROOMREV) NETROOMREV, 
  sum(NETFBREV) NETFBREV, 
  sum(NETMINIBARREV) NETMINIBARREV, 
  sum(NETTELEREV) NETTELEREV, 
  sum(NETMISCREV) NETMISCREV, 
  sum(NETREV) NETREV, 
  sum(GROSSROOMREV) GROSSROOMREV, 
  sum(GROSSFBREV) GROSSFBREV, 
  sum(GROSSMINIBARREV) GROSSMINIBARREV, 
  sum(GROSSTELEREV) GROSSTELEREV, 
  sum(GROSSMISCREV) GROSSMISCREV, 
  sum(GROSSREV) GROSSREV
FROM 
( 
SELECT 
  wdat.WDAT_DATE WDAT_DATE, 
  yras.YRAS_ID CHANNEL_ID, 
  YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID YCAD_ID,
  sum(decode(S.SHIS_YRES_RESSTATUS,3,0,decode(S.SHIS_DEP_ID,1,0,S.SHIS_YDET_NOOFROOMS))) NOOFROOMS,
  SUM(DECODE(S.SHIS_YRES_RESSTATUS,3,0,CASE WHEN S.SHIS_DEP_ID=1 AND S.SHIS_ARR_ID=1 THEN S.SHIS_YDET_ADULTNO WHEN S.SHIS_DEP_ID=1 AND S.SHIS_ARR_ID=0 THEN 0 WHEN NVL(S.SHIS_YDET_ID,0)=0 THEN 0 ELSE S.SHIS_YDET_ADULTNO END )) ADULTNO, 
  SUM(DECODE(S.SHIS_YRES_RESSTATUS,3,0,CASE WHEN S.SHIS_DEP_ID=1 AND S.SHIS_ARR_ID=1 THEN NVL(S.SHIS_TOTALYRGL_NUMBER,0) WHEN S.SHIS_DEP_ID=1 AND S.SHIS_ARR_ID=0 THEN 0 WHEN NVL(S.SHIS_YDET_ID,0)=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(decode(S.SHIS_YRES_RESSTATUS,3,(decode(S.SHIS_YRES_NOAVAILREASON,4,(decode(trunc(S.SHIS_YRES_EXPARRTIME),trunc(S.SHIS_WDAT_DATE),S.SHIS_YDET_NOOFROOMS,0)))),0)) NOSHOW_ROOM,
  sum(decode(S.SHIS_YRES_RESSTATUS,3,(decode(S.SHIS_YRES_NOAVAILREASON,3,(decode(trunc(S.SHIS_YRES_EXPARRTIME),trunc(S.SHIS_WDAT_DATE),S.SHIS_YDET_NOOFROOMS,0)))),0)) 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, 
  yras,
  ycat
WHERE 
ADD_MONTHS((select v8_rep_dates.FI_FIRST_OF_YEAR from v8_rep_dates where v8_rep_dates.FIDELIODATE=wdat.WDAT_DATE),-12)<=S.SHIS_WDAT_DATE and 
S.SHIS_WDAT_DATE<=ADD_MONTHS(WDAT.WDAT_DATE,-12)  and 
yras.YRAS_ID(+)=S.SHIS_CHANNEL_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
yras.YRAS_YRAC_ID=2
group by wdat.WDAT_DATE,yras.YRAS_ID, YCAT_SUM_ROOMTYPE_YCAD_ID) 
group by WDAT_DATE,CHANNEL_ID,YCAD_ID;

CREATE OR REPLACE VIEW V8_STAT_SHIS_CHAN_COMBI_LY_CAD
AS 
SELECT 
  WDAT_DATE WDAT_DATE,
  CHANNEL_ID CHANNEL_ID,
  YCAD_ID YCAD_ID,
  sum(NOOFROOMS_D) NOOFROOMS_D,
  sum(ADULTNO_D) ADULTNO_D,
  sum(KIDS_D) KIDS_D,
  sum(ARRIVALS_D) ARRIVALS_D,
  sum(DEPARTURES_D) DEPARTURES_D,
  sum(ARR_ADULTNO_D) ARR_ADULTNO_D,
  sum(DEP_ADULTNO_D) DEP_ADULTNO_D,
  sum(ARR_KIDS_D) ARR_KIDS_D,
  sum(DEP_KIDS_D) DEP_KIDS_D,
  sum(NOSHOW_ROOM_D) NOSHOW_ROOM_D,
  sum(CANCEL_ROOM_D) CANCEL_ROOM_D, 
  sum(NETROOMREV_D) NETROOMREV_D, 
  sum(NETFBREV_D) NETFBREV_D, 
  sum(NETMINIBARREV_D) NETMINIBARREV_D, 
  sum(NETTELEREV_D) NETTELEREV_D, 
  sum(NETMISCREV_D) NETMISCREV_D, 
  sum(NETREV_D) NETREV_D,
  sum(GROSSROOMREV_D) GROSSROOMREV_D,
  sum(GROSSFBREV_D) GROSSFBREV_D,
  sum(GROSSMINIBARREV_D) GROSSMINIBARREV_D,
  sum(GROSSTELEREV_D) GROSSTELEREV_D,
  sum(GROSSMISCREV_D) GROSSMISCREV_D,
  sum(GROSSREV_D) GROSSREV_D,
  sum(NOOFROOMS_M) NOOFROOMS_M,
  sum(ADULTNO_M) ADULTNO_M,
  sum(KIDS_M) KIDS_M,
  sum(ARRIVALS_M) ARRIVALS_M,
  sum(DEPARTURES_M) DEPARTURES_M,
  sum(ARR_ADULTNO_M) ARR_ADULTNO_M,
  sum(DEP_ADULTNO_M) DEP_ADULTNO_M,
  sum(ARR_KIDS_M) ARR_KIDS_M,
  sum(DEP_KIDS_M) DEP_KIDS_M,
  sum(NOSHOW_ROOM_M) NOSHOW_ROOM_M,
  sum(CANCEL_ROOM_M) CANCEL_ROOM_M, 
  sum(NETROOMREV_M) NETROOMREV_M, 
  sum(NETFBREV_M) NETFBREV_M, 
  sum(NETMINIBARREV_M) NETMINIBARREV_M, 
  sum(NETTELEREV_M) NETTELEREV_M, 
  sum(NETMISCREV_M) NETMISCREV_M, 
  sum(NETREV_M) NETREV_M,
  sum(GROSSROOMREV_M) GROSSROOMREV_M,
  sum(GROSSFBREV_M) GROSSFBREV_M,
  sum(GROSSMINIBARREV_M) GROSSMINIBARREV_M,
  sum(GROSSTELEREV_M) GROSSTELEREV_M,
  sum(GROSSMISCREV_M) GROSSMISCREV_M,
  sum(GROSSREV_M) GROSSREV_M,
  sum(NOOFROOMS_Y) NOOFROOMS_Y,
  sum(ADULTNO_Y) ADULTNO_Y,
  sum(KIDS_Y) KIDS_Y,
  sum(ARRIVALS_Y) ARRIVALS_Y,
  sum(DEPARTURES_Y) DEPARTURES_Y,
  sum(ARR_ADULTNO_Y) ARR_ADULTNO_Y,
  sum(DEP_ADULTNO_Y) DEP_ADULTNO_Y,
  sum(ARR_KIDS_Y) ARR_KIDS_Y,
  sum(DEP_KIDS_Y) DEP_KIDS_Y,
  sum(NOSHOW_ROOM_Y) NOSHOW_ROOM_Y,
  sum(CANCEL_ROOM_Y) CANCEL_ROOM_Y, 
  sum(NETROOMREV_Y) NETROOMREV_Y, 
  sum(NETFBREV_Y) NETFBREV_Y, 
  sum(NETMINIBARREV_Y) NETMINIBARREV_Y, 
  sum(NETTELEREV_Y) NETTELEREV_Y, 
  sum(NETMISCREV_Y) NETMISCREV_Y, 
  sum(NETREV_Y) NETREV_Y,
  sum(GROSSROOMREV_Y) GROSSROOMREV_Y,
  sum(GROSSFBREV_Y) GROSSFBREV_Y,
  sum(GROSSMINIBARREV_Y) GROSSMINIBARREV_Y,
  sum(GROSSTELEREV_Y) GROSSTELEREV_Y,
  sum(GROSSMISCREV_Y) GROSSMISCREV_Y,
  sum(GROSSREV_Y) GROSSREV_Y,
  SUM(NOOFROOMS_D_LY) NOOFROOMS_D_LY,
  SUM(ADULTNO_D_LY) ADULTNO_D_LY,
  SUM(KIDS_D_LY) KIDS_D_LY,
  SUM(ARRIVALS_D_LY) ARRIVALS_D_LY,
  SUM(DEPARTURES_D_LY) DEPARTURES_D_LY,
  SUM(ARR_ADULTNO_D_LY) ARR_ADULTNO_D_LY,
  SUM(DEP_ADULTNO_D_LY) DEP_ADULTNO_D_LY,
  SUM(ARR_KIDS_D_LY) ARR_KIDS_D_LY,
  SUM(DEP_KIDS_D_LY) DEP_KIDS_D_LY,
  SUM(NOSHOW_ROOM_D_LY) NOSHOW_ROOM_D_LY,
  SUM(CANCEL_ROOM_D_LY) CANCEL_ROOM_D_LY,
  SUM(NETROOMREV_D_LY) NETROOMREV_D_LY,
  SUM(NETFBREV_D_LY) NETFBREV_D_LY,
  SUM(NETMINIBARREV_D_LY) NETMINIBARREV_D_LY,
  SUM(NETTELEREV_D_LY) NETTELEREV_D_LY,
  SUM(NETMISCREV_D_LY) NETMISCREV_D_LY,
  SUM(NETREV_D_LY) NETREV_D_LY,
  SUM(GROSSROOMREV_D_LY) GROSSROOMREV_D_LY,
  SUM(GROSSFBREV_D_LY) GROSSFBREV_D_LY,
  SUM(GROSSMINIBARREV_D_LY) GROSSMINIBARREV_D_LY,
  SUM(GROSSTELEREV_D_LY) GROSSTELEREV_D_LY,
  SUM(GROSSMISCREV_D_LY) GROSSMISCREV_D_LY,
  SUM(GROSSREV_D_LY) GROSSREV_D_LY,
  SUM(NOOFROOMS_M_LY) NOOFROOMS_M_LY,
  SUM(ADULTNO_M_LY) ADULTNO_M_LY,
  SUM(KIDS_M_LY) KIDS_M_LY,
  SUM(ARRIVALS_M_LY) ARRIVALS_M_LY,
  SUM(DEPARTURES_M_LY) DEPARTURES_M_LY,
  SUM(ARR_ADULTNO_M_LY) ARR_ADULTNO_M_LY,
  SUM(DEP_ADULTNO_M_LY) DEP_ADULTNO_M_LY,
  SUM(ARR_KIDS_M_LY) ARR_KIDS_M_LY,
  SUM(DEP_KIDS_M_LY) DEP_KIDS_M_LY,
  SUM(NOSHOW_ROOM_M_LY) NOSHOW_ROOM_M_LY,
  SUM(CANCEL_ROOM_M_LY) CANCEL_ROOM_M_LY,
  SUM(NETROOMREV_M_LY) NETROOMREV_M_LY,
  SUM(NETFBREV_M_LY) NETFBREV_M_LY,
  SUM(NETMINIBARREV_M_LY) NETMINIBARREV_M_LY,
  SUM(NETTELEREV_M_LY) NETTELEREV_M_LY,
  SUM(NETMISCREV_M_LY) NETMISCREV_M_LY,
  SUM(NETREV_M_LY) NETREV_M_LY,
  SUM(GROSSROOMREV_M_LY) GROSSROOMREV_M_LY,
  SUM(GROSSFBREV_M_LY) GROSSFBREV_M_LY,
  SUM(GROSSMINIBARREV_M_LY) GROSSMINIBARREV_M_LY,
  SUM(GROSSTELEREV_M_LY) GROSSTELEREV_M_LY,
  SUM(GROSSMISCREV_M_LY) GROSSMISCREV_M_LY,
  SUM(GROSSREV_M_LY) GROSSREV_M_LY,
  SUM(NOOFROOMS_Y_LY) NOOFROOMS_Y_LY,
  SUM(ADULTNO_Y_LY) ADULTNO_Y_LY,
  SUM(KIDS_Y_LY) KIDS_Y_LY,
  SUM(ARRIVALS_Y_LY) ARRIVALS_Y_LY,
  SUM(DEPARTURES_Y_LY) DEPARTURES_Y_LY,
  SUM(ARR_ADULTNO_Y_LY) ARR_ADULTNO_Y_LY,
  SUM(DEP_ADULTNO_Y_LY) DEP_ADULTNO_Y_LY,
  SUM(ARR_KIDS_Y_LY) ARR_KIDS_Y_LY,
  SUM(DEP_KIDS_Y_LY) DEP_KIDS_Y_LY,
  SUM(NOSHOW_ROOM_Y_LY) NOSHOW_ROOM_Y_LY,
  SUM(CANCEL_ROOM_Y_LY) CANCEL_ROOM_Y_LY,
  SUM(NETROOMREV_Y_LY) NETROOMREV_Y_LY,
  SUM(NETFBREV_Y_LY) NETFBREV_Y_LY,
  SUM(NETMINIBARREV_Y_LY) NETMINIBARREV_Y_LY,
  SUM(NETTELEREV_Y_LY) NETTELEREV_Y_LY,
  SUM(NETMISCREV_Y_LY) NETMISCREV_Y_LY,
  SUM(NETREV_Y_LY) NETREV_Y_LY,
  SUM(GROSSROOMREV_Y_LY) GROSSROOMREV_Y_LY,
  SUM(GROSSFBREV_Y_LY) GROSSFBREV_Y_LY,
  SUM(GROSSMINIBARREV_Y_LY) GROSSMINIBARREV_Y_LY,
  SUM(GROSSTELEREV_Y_LY) GROSSTELEREV_Y_LY,
  SUM(GROSSMISCREV_Y_LY) GROSSMISCREV_Y_LY,
  SUM(GROSSREV_Y_LY) GROSSREV_Y_LY
FROM (
SELECT WDAT_DATE, CHANNEL_ID, YCAD_ID,
NOOFROOMS NOOFROOMS_D, ADULTNO ADULTNO_D, KIDS KIDS_D, ARRIVALS ARRIVALS_D, DEPARTURES DEPARTURES_D, ARR_ADULTNO ARR_ADULTNO_D, 
DEP_ADULTNO DEP_ADULTNO_D, ARR_KIDS ARR_KIDS_D, DEP_KIDS DEP_KIDS_D, NOSHOW_ROOM NOSHOW_ROOM_D, CANCEL_ROOM CANCEL_ROOM_D,
NETROOMREV NETROOMREV_D, NETFBREV NETFBREV_D, NETMINIBARREV NETMINIBARREV_D, NETTELEREV NETTELEREV_D, NETMISCREV NETMISCREV_D, 
NETREV NETREV_D, GROSSROOMREV GROSSROOMREV_D, GROSSFBREV GROSSFBREV_D, GROSSMINIBARREV GROSSMINIBARREV_D, 
GROSSTELEREV GROSSTELEREV_D, GROSSMISCREV GROSSMISCREV_D, GROSSREV GROSSREV_D,
0 NOOFROOMS_M, 0 ADULTNO_M, 0 KIDS_M, 0 ARRIVALS_M, 0 DEPARTURES_M, 0 ARR_ADULTNO_M, 
0 DEP_ADULTNO_M, 0 ARR_KIDS_M, 0 DEP_KIDS_M, 0 NOSHOW_ROOM_M, 0 CANCEL_ROOM_M,
0 NETROOMREV_M, 0 NETFBREV_M, 0 NETMINIBARREV_M, 0 NETTELEREV_M, 0 NETMISCREV_M, 
0 NETREV_M, 0 GROSSROOMREV_M, 0 GROSSFBREV_M, 0 GROSSMINIBARREV_M, 
0 GROSSTELEREV_M, 0 GROSSMISCREV_M, 0 GROSSREV_M,
0 NOOFROOMS_Y, 0 ADULTNO_Y, 0 KIDS_Y, 0 ARRIVALS_Y, 0 DEPARTURES_Y, 0 ARR_ADULTNO_Y, 
0 DEP_ADULTNO_Y, 0 ARR_KIDS_Y, 0 DEP_KIDS_Y, 0 NOSHOW_ROOM_Y, 0 CANCEL_ROOM_Y,
0 NETROOMREV_Y, 0 NETFBREV_Y, 0 NETMINIBARREV_Y, 0 NETTELEREV_Y, 0 NETMISCREV_Y, 
0 NETREV_Y, 0 GROSSROOMREV_Y, 0 GROSSFBREV_Y, 0 GROSSMINIBARREV_Y, 
0 GROSSTELEREV_Y, 0 GROSSMISCREV_Y, 0 GROSSREV_Y,
0 NOOFROOMS_D_LY, 0 ADULTNO_D_LY, 0 KIDS_D_LY, 0 ARRIVALS_D_LY, 0 DEPARTURES_D_LY, 0 ARR_ADULTNO_D_LY,
0 DEP_ADULTNO_D_LY, 0 ARR_KIDS_D_LY, 0 DEP_KIDS_D_LY, 0 NOSHOW_ROOM_D_LY, 0 CANCEL_ROOM_D_LY,
0 NETROOMREV_D_LY, 0 NETFBREV_D_LY, 0 NETMINIBARREV_D_LY, 0 NETTELEREV_D_LY, 0 NETMISCREV_D_LY,
0 NETREV_D_LY, 0 GROSSROOMREV_D_LY, 0 GROSSFBREV_D_LY, 0 GROSSMINIBARREV_D_LY,
0 GROSSTELEREV_D_LY, 0 GROSSMISCREV_D_LY, 0 GROSSREV_D_LY,
0 NOOFROOMS_M_LY, 0 ADULTNO_M_LY, 0 KIDS_M_LY, 0 ARRIVALS_M_LY, 0 DEPARTURES_M_LY, 0 ARR_ADULTNO_M_LY,
0 DEP_ADULTNO_M_LY, 0 ARR_KIDS_M_LY, 0 DEP_KIDS_M_LY, 0 NOSHOW_ROOM_M_LY, 0 CANCEL_ROOM_M_LY,
0 NETROOMREV_M_LY, 0 NETFBREV_M_LY, 0 NETMINIBARREV_M_LY, 0 NETTELEREV_M_LY, 0 NETMISCREV_M_LY,
0 NETREV_M_LY, 0 GROSSROOMREV_M_LY, 0 GROSSFBREV_M_LY, 0 GROSSMINIBARREV_M_LY,
0 GROSSTELEREV_M_LY, 0 GROSSMISCREV_M_LY, 0 GROSSREV_M_LY,
0 NOOFROOMS_Y_LY, 0 ADULTNO_Y_LY, 0 KIDS_Y_LY, 0 ARRIVALS_Y_LY, 0 DEPARTURES_Y_LY, 0 ARR_ADULTNO_Y_LY,
0 DEP_ADULTNO_Y_LY, 0 ARR_KIDS_Y_LY, 0 DEP_KIDS_Y_LY, 0 NOSHOW_ROOM_Y_LY, 0 CANCEL_ROOM_Y_LY,
0 NETROOMREV_Y_LY, 0 NETFBREV_Y_LY, 0 NETMINIBARREV_Y_LY, 0 NETTELEREV_Y_LY, 0 NETMISCREV_Y_LY,
0 NETREV_Y_LY, 0 GROSSROOMREV_Y_LY, 0 GROSSFBREV_Y_LY, 0 GROSSMINIBARREV_Y_LY,
0 GROSSTELEREV_Y_LY, 0 GROSSMISCREV_Y_LY, 0 GROSSREV_Y_LY
FROM V8_STAT_SHIS_CHANNEL_DAY_CAD
UNION ALL
SELECT WDAT_DATE, CHANNEL_ID, YCAD_ID,
0 NOOFROOMS_D, 0 ADULTNO_D, 0 KIDS_D, 0 ARRIVALS_D, 0 DEPARTURES_D, 0 ARR_ADULTNO_D, 
0 DEP_ADULTNO_D, 0 ARR_KIDS_D, 0 DEP_KIDS_D, 0 NOSHOW_ROOM_D, 0 CANCEL_ROOM_D,
0 NETROOMREV_D, 0 NETFBREV_D, 0 NETMINIBARREV_D, 0 NETTELEREV_D, 0 NETMISCREV_D, 
0 NETREV_D, 0 GROSSROOMREV_D, 0 GROSSFBREV_D, 0 GROSSMINIBARREV_D, 
0 GROSSTELEREV_D, 0 GROSSMISCREV_D, 0 GROSSREV_D,
NOOFROOMS NOOFROOMS_M, ADULTNO ADULTNO_M, KIDS KIDS_M, ARRIVALS ARRIVALS_M, DEPARTURES DEPARTURES_M, ARR_ADULTNO ARR_ADULTNO_M, 
DEP_ADULTNO DEP_ADULTNO_M, ARR_KIDS ARR_KIDS_M, DEP_KIDS DEP_KIDS_M, NOSHOW_ROOM NOSHOW_ROOM_M, CANCEL_ROOM CANCEL_ROOM_M,
NETROOMREV NETROOMREV_M, NETFBREV NETFBREV_M, NETMINIBARREV NETMINIBARREV_M, NETTELEREV NETTELEREV_M, NETMISCREV NETMISCREV_M, 
NETREV NETREV_M, GROSSROOMREV GROSSROOMREV_M, GROSSFBREV GROSSFBREV_M, GROSSMINIBARREV GROSSMINIBARREV_M, 
GROSSTELEREV GROSSTELEREV_M, GROSSMISCREV GROSSMISCREV_M, GROSSREV GROSSREV_M,
0 NOOFROOMS_Y, 0 ADULTNO_Y, 0 KIDS_Y, 0 ARRIVALS_Y, 0 DEPARTURES_Y, 0 ARR_ADULTNO_Y, 
0 DEP_ADULTNO_Y, 0 ARR_KIDS_Y, 0 DEP_KIDS_Y, 0 NOSHOW_ROOM_Y, 0 CANCEL_ROOM_Y,
0 NETROOMREV_Y, 0 NETFBREV_Y, 0 NETMINIBARREV_Y, 0 NETTELEREV_Y, 0 NETMISCREV_Y, 
0 NETREV_Y, 0 GROSSROOMREV_Y, 0 GROSSFBREV_Y, 0 GROSSMINIBARREV_Y, 
0 GROSSTELEREV_Y, 0 GROSSMISCREV_Y, 0 GROSSREV_Y,
0 NOOFROOMS_D_LY, 0 ADULTNO_D_LY, 0 KIDS_D_LY, 0 ARRIVALS_D_LY, 0 DEPARTURES_D_LY, 0 ARR_ADULTNO_D_LY,
0 DEP_ADULTNO_D_LY, 0 ARR_KIDS_D_LY, 0 DEP_KIDS_D_LY, 0 NOSHOW_ROOM_D_LY, 0 CANCEL_ROOM_D_LY,
0 NETROOMREV_D_LY, 0 NETFBREV_D_LY, 0 NETMINIBARREV_D_LY, 0 NETTELEREV_D_LY, 0 NETMISCREV_D_LY,
0 NETREV_D_LY, 0 GROSSROOMREV_D_LY, 0 GROSSFBREV_D_LY, 0 GROSSMINIBARREV_D_LY,
0 GROSSTELEREV_D_LY, 0 GROSSMISCREV_D_LY, 0 GROSSREV_D_LY,
0 NOOFROOMS_M_LY, 0 ADULTNO_M_LY, 0 KIDS_M_LY, 0 ARRIVALS_M_LY, 0 DEPARTURES_M_LY, 0 ARR_ADULTNO_M_LY,
0 DEP_ADULTNO_M_LY, 0 ARR_KIDS_M_LY, 0 DEP_KIDS_M_LY, 0 NOSHOW_ROOM_M_LY, 0 CANCEL_ROOM_M_LY,
0 NETROOMREV_M_LY, 0 NETFBREV_M_LY, 0 NETMINIBARREV_M_LY, 0 NETTELEREV_M_LY, 0 NETMISCREV_M_LY,
0 NETREV_M_LY, 0 GROSSROOMREV_M_LY, 0 GROSSFBREV_M_LY, 0 GROSSMINIBARREV_M_LY,
0 GROSSTELEREV_M_LY, 0 GROSSMISCREV_M_LY, 0 GROSSREV_M_LY,
0 NOOFROOMS_Y_LY, 0 ADULTNO_Y_LY, 0 KIDS_Y_LY, 0 ARRIVALS_Y_LY, 0 DEPARTURES_Y_LY, 0 ARR_ADULTNO_Y_LY,
0 DEP_ADULTNO_Y_LY, 0 ARR_KIDS_Y_LY, 0 DEP_KIDS_Y_LY, 0 NOSHOW_ROOM_Y_LY, 0 CANCEL_ROOM_Y_LY,
0 NETROOMREV_Y_LY, 0 NETFBREV_Y_LY, 0 NETMINIBARREV_Y_LY, 0 NETTELEREV_Y_LY, 0 NETMISCREV_Y_LY,
0 NETREV_Y_LY, 0 GROSSROOMREV_Y_LY, 0 GROSSFBREV_Y_LY, 0 GROSSMINIBARREV_Y_LY,
0 GROSSTELEREV_Y_LY, 0 GROSSMISCREV_Y_LY, 0 GROSSREV_Y_LY
FROM V8_STAT_SHIS_CHANNEL_MONTH_CAD
UNION ALL
SELECT WDAT_DATE, CHANNEL_ID, YCAD_ID,
0 NOOFROOMS_D, 0 ADULTNO_D, 0 KIDS_D, 0 ARRIVALS_D, 0 DEPARTURES_D, 0 ARR_ADULTNO_D, 
0 DEP_ADULTNO_D, 0 ARR_KIDS_D, 0 DEP_KIDS_D, 0 NOSHOW_ROOM_D, 0 CANCEL_ROOM_D,
0 NETROOMREV_D, 0 NETFBREV_D, 0 NETMINIBARREV_D, 0 NETTELEREV_D, 0 NETMISCREV_D, 
0 NETREV_D, 0 GROSSROOMREV_D, 0 GROSSFBREV_D, 0 GROSSMINIBARREV_D, 
0 GROSSTELEREV_D, 0 GROSSMISCREV_D, 0 GROSSREV_D,
0 NOOFROOMS_M, 0 ADULTNO_M, 0 KIDS_M, 0 ARRIVALS_M, 0 DEPARTURES_M, 0 ARR_ADULTNO_M, 
0 DEP_ADULTNO_M, 0 ARR_KIDS_M, 0 DEP_KIDS_M, 0 NOSHOW_ROOM_M, 0 CANCEL_ROOM_M,
0 NETROOMREV_M, 0 NETFBREV_M, 0 NETMINIBARREV_M, 0 NETTELEREV_M, 0 NETMISCREV_M, 
0 NETREV_M, 0 GROSSROOMREV_M, 0 GROSSFBREV_M, 0 GROSSMINIBARREV_M, 
0 GROSSTELEREV_M, 0 GROSSMISCREV_M, 0 GROSSREV_M,
NOOFROOMS NOOFROOMS_Y, ADULTNO ADULTNO_Y, KIDS KIDS_Y, ARRIVALS ARRIVALS_Y, DEPARTURES DEPARTURES_Y, ARR_ADULTNO ARR_ADULTNO_Y, 
DEP_ADULTNO DEP_ADULTNO_Y, ARR_KIDS ARR_KIDS_Y, DEP_KIDS DEP_KIDS_Y, NOSHOW_ROOM NOSHOW_ROOM_Y, CANCEL_ROOM CANCEL_ROOM_Y,
NETROOMREV NETROOMREV_Y, NETFBREV NETFBREV_Y, NETMINIBARREV NETMINIBARREV_Y, NETTELEREV NETTELEREV_Y, NETMISCREV NETMISCREV_Y, 
NETREV NETREV_Y, GROSSROOMREV GROSSROOMREV_Y, GROSSFBREV GROSSFBREV_Y, GROSSMINIBARREV GROSSMINIBARREV_Y, 
GROSSTELEREV GROSSTELEREV_Y, GROSSMISCREV GROSSMISCREV_Y, GROSSREV GROSSREV_Y,
0 NOOFROOMS_D_LY, 0 ADULTNO_D_LY, 0 KIDS_D_LY, 0 ARRIVALS_D_LY, 0 DEPARTURES_D_LY, 0 ARR_ADULTNO_D_LY,
0 DEP_ADULTNO_D_LY, 0 ARR_KIDS_D_LY, 0 DEP_KIDS_D_LY, 0 NOSHOW_ROOM_D_LY, 0 CANCEL_ROOM_D_LY,
0 NETROOMREV_D_LY, 0 NETFBREV_D_LY, 0 NETMINIBARREV_D_LY, 0 NETTELEREV_D_LY, 0 NETMISCREV_D_LY,
0 NETREV_D_LY, 0 GROSSROOMREV_D_LY, 0 GROSSFBREV_D_LY, 0 GROSSMINIBARREV_D_LY,
0 GROSSTELEREV_D_LY, 0 GROSSMISCREV_D_LY, 0 GROSSREV_D_LY,
0 NOOFROOMS_M_LY, 0 ADULTNO_M_LY, 0 KIDS_M_LY, 0 ARRIVALS_M_LY, 0 DEPARTURES_M_LY, 0 ARR_ADULTNO_M_LY,
0 DEP_ADULTNO_M_LY, 0 ARR_KIDS_M_LY, 0 DEP_KIDS_M_LY, 0 NOSHOW_ROOM_M_LY, 0 CANCEL_ROOM_M_LY,
0 NETROOMREV_M_LY, 0 NETFBREV_M_LY, 0 NETMINIBARREV_M_LY, 0 NETTELEREV_M_LY, 0 NETMISCREV_M_LY,
0 NETREV_M_LY, 0 GROSSROOMREV_M_LY, 0 GROSSFBREV_M_LY, 0 GROSSMINIBARREV_M_LY,
0 GROSSTELEREV_M_LY, 0 GROSSMISCREV_M_LY, 0 GROSSREV_M_LY,
0 NOOFROOMS_Y_LY, 0 ADULTNO_Y_LY, 0 KIDS_Y_LY, 0 ARRIVALS_Y_LY, 0 DEPARTURES_Y_LY, 0 ARR_ADULTNO_Y_LY,
0 DEP_ADULTNO_Y_LY, 0 ARR_KIDS_Y_LY, 0 DEP_KIDS_Y_LY, 0 NOSHOW_ROOM_Y_LY, 0 CANCEL_ROOM_Y_LY,
0 NETROOMREV_Y_LY, 0 NETFBREV_Y_LY, 0 NETMINIBARREV_Y_LY, 0 NETTELEREV_Y_LY, 0 NETMISCREV_Y_LY,
0 NETREV_Y_LY, 0 GROSSROOMREV_Y_LY, 0 GROSSFBREV_Y_LY, 0 GROSSMINIBARREV_Y_LY,
0 GROSSTELEREV_Y_LY, 0 GROSSMISCREV_Y_LY, 0 GROSSREV_Y_LY
FROM V8_STAT_SHIS_CHANNEL_YEAR_CAD
UNION ALL
SELECT WDAT_DATE, CHANNEL_ID,YCAD_ID,
0 NOOFROOMS_D, 0 ADULTNO_D, 0 KIDS_D, 0 ARRIVALS_D, 0 DEPARTURES_D, 0 ARR_ADULTNO_D,
0 DEP_ADULTNO_D, 0 ARR_KIDS_D, 0 DEP_KIDS_D, 0 NOSHOW_ROOM_D, 0 CANCEL_ROOM_D,
0 NETROOMREV_D, 0 NETFBREV_D, 0 NETMINIBARREV_D, 0 NETTELEREV_D, 0 NETMISCREV_D,
0 NETREV_D, 0 GROSSROOMREV_D, 0 GROSSFBREV_D, 0 GROSSMINIBARREV_D,
0 GROSSTELEREV_D, 0 GROSSMISCREV_D, 0 GROSSREV_D,
0 NOOFROOMS_M, 0 ADULTNO_M, 0 KIDS_M, 0 ARRIVALS_M, 0 DEPARTURES_M, 0 ARR_ADULTNO_M,
0 DEP_ADULTNO_M, 0 ARR_KIDS_M, 0 DEP_KIDS_M, 0 NOSHOW_ROOM_M, 0 CANCEL_ROOM_M,
0 NETROOMREV_M, 0 NETFBREV_M, 0 NETMINIBARREV_M, 0 NETTELEREV_M, 0 NETMISCREV_M,
0 NETREV_M, 0 GROSSROOMREV_M, 0 GROSSFBREV_M, 0 GROSSMINIBARREV_M,
0 GROSSTELEREV_M, 0 GROSSMISCREV_M, 0 GROSSREV_M,
0 NOOFROOMS_Y, 0 ADULTNO_Y, 0 KIDS_Y, 0 ARRIVALS_Y, 0 DEPARTURES_Y, 0 ARR_ADULTNO_Y,
0 DEP_ADULTNO_Y, 0 ARR_KIDS_Y, 0 DEP_KIDS_Y, 0 NOSHOW_ROOM_Y, 0 CANCEL_ROOM_Y,
0 NETROOMREV_Y, 0 NETFBREV_Y, 0 NETMINIBARREV_Y, 0 NETTELEREV_Y, 0 NETMISCREV_Y,
0 NETREV_Y, 0 GROSSROOMREV_Y, 0 GROSSFBREV_Y, 0 GROSSMINIBARREV_Y,
0 GROSSTELEREV_Y, 0 GROSSMISCREV_Y, 0 GROSSREV_Y,
NOOFROOMS NOOFROOMS_D_LY, ADULTNO ADULTNO_D_LY, KIDS KIDS_D_LY, ARRIVALS ARRIVALS_D_LY, DEPARTURES DEPARTURES_D_LY, ARR_ADULTNO ARR_ADULTNO_D_LY,
DEP_ADULTNO DEP_ADULTNO_D_LY, ARR_KIDS ARR_KIDS_D_LY, DEP_KIDS DEP_KIDS_D_LY, NOSHOW_ROOM NOSHOW_ROOM_D_LY, CANCEL_ROOM CANCEL_ROOM_D_LY,
NETROOMREV NETROOMREV_D_LY, NETFBREV NETFBREV_D_LY, NETMINIBARREV NETMINIBARREV_D_LY, NETTELEREV NETTELEREV_D_LY, NETMISCREV NETMISCREV_D_LY,
NETREV NETREV_D_LY, GROSSROOMREV GROSSROOMREV_D_LY, GROSSFBREV GROSSFBREV_D_LY, GROSSMINIBARREV GROSSMINIBARREV_D_LY,
GROSSTELEREV GROSSTELEREV_D_LY, GROSSMISCREV GROSSMISCREV_D_LY, GROSSREV GROSSREV_D_LY,
0 NOOFROOMS_M_LY, 0 ADULTNO_M_LY, 0 KIDS_M_LY, 0 ARRIVALS_M_LY, 0 DEPARTURES_M_LY, 0 ARR_ADULTNO_M_LY,
0 DEP_ADULTNO_M_LY, 0 ARR_KIDS_M_LY, 0 DEP_KIDS_M_LY, 0 NOSHOW_ROOM_M_LY, 0 CANCEL_ROOM_M_LY,
0 NETROOMREV_M_LY, 0 NETFBREV_M_LY, 0 NETMINIBARREV_M_LY, 0 NETTELEREV_M_LY, 0 NETMISCREV_M_LY,
0 NETREV_M_LY, 0 GROSSROOMREV_M_LY, 0 GROSSFBREV_M_LY, 0 GROSSMINIBARREV_M_LY,
0 GROSSTELEREV_M_LY, 0 GROSSMISCREV_M_LY, 0 GROSSREV_M_LY,
0 NOOFROOMS_Y_LY, 0 ADULTNO_Y_LY, 0 KIDS_Y_LY, 0 ARRIVALS_Y_LY, 0 DEPARTURES_Y_LY, 0 ARR_ADULTNO_Y_LY,
0 DEP_ADULTNO_Y_LY, 0 ARR_KIDS_Y_LY, 0 DEP_KIDS_Y_LY, 0 NOSHOW_ROOM_Y_LY, 0 CANCEL_ROOM_Y_LY,
0 NETROOMREV_Y_LY, 0 NETFBREV_Y_LY, 0 NETMINIBARREV_Y_LY, 0 NETTELEREV_Y_LY, 0 NETMISCREV_Y_LY,
0 NETREV_Y_LY, 0 GROSSROOMREV_Y_LY, 0 GROSSFBREV_Y_LY, 0 GROSSMINIBARREV_Y_LY,
0 GROSSTELEREV_Y_LY, 0 GROSSMISCREV_Y_LY, 0 GROSSREV_Y_LY
FROM V8_STAT_SHIS_CHAN_DAY_LY_CAD
UNION ALL
SELECT WDAT_DATE, CHANNEL_ID,YCAD_ID,
0 NOOFROOMS_D, 0 ADULTNO_D, 0 KIDS_D, 0 ARRIVALS_D, 0 DEPARTURES_D, 0 ARR_ADULTNO_D,
0 DEP_ADULTNO_D, 0 ARR_KIDS_D, 0 DEP_KIDS_D, 0 NOSHOW_ROOM_D, 0 CANCEL_ROOM_D,
0 NETROOMREV_D, 0 NETFBREV_D, 0 NETMINIBARREV_D, 0 NETTELEREV_D, 0 NETMISCREV_D,
0 NETREV_D, 0 GROSSROOMREV_D, 0 GROSSFBREV_D, 0 GROSSMINIBARREV_D,
0 GROSSTELEREV_D, 0 GROSSMISCREV_D, 0 GROSSREV_D,
0 NOOFROOMS_M, 0 ADULTNO_M, 0 KIDS_M, 0 ARRIVALS_M, 0 DEPARTURES_M, 0 ARR_ADULTNO_M,
0 DEP_ADULTNO_M, 0 ARR_KIDS_M, 0 DEP_KIDS_M, 0 NOSHOW_ROOM_M, 0 CANCEL_ROOM_M,
0 NETROOMREV_M, 0 NETFBREV_M, 0 NETMINIBARREV_M, 0 NETTELEREV_M, 0 NETMISCREV_M,
0 NETREV_M, 0 GROSSROOMREV_M, 0 GROSSFBREV_M, 0 GROSSMINIBARREV_M,
0 GROSSTELEREV_M, 0 GROSSMISCREV_M, 0 GROSSREV_M,
0 NOOFROOMS_Y, 0 ADULTNO_Y, 0 KIDS_Y, 0 ARRIVALS_Y, 0 DEPARTURES_Y, 0 ARR_ADULTNO_Y,
0 DEP_ADULTNO_Y, 0 ARR_KIDS_Y, 0 DEP_KIDS_Y, 0 NOSHOW_ROOM_Y, 0 CANCEL_ROOM_Y,
0 NETROOMREV_Y, 0 NETFBREV_Y, 0 NETMINIBARREV_Y, 0 NETTELEREV_Y, 0 NETMISCREV_Y,
0 NETREV_Y, 0 GROSSROOMREV_Y, 0 GROSSFBREV_Y, 0 GROSSMINIBARREV_Y,
0 GROSSTELEREV_Y, 0 GROSSMISCREV_Y, 0 GROSSREV_Y,
0 NOOFROOMS_D_LY, 0 ADULTNO_D_LY, 0 KIDS_D_LY, 0 ARRIVALS_D_LY, 0 DEPARTURES_D_LY, 0 ARR_ADULTNO_D_LY,
0 DEP_ADULTNO_D_LY, 0 ARR_KIDS_D_LY, 0 DEP_KIDS_D_LY, 0 NOSHOW_ROOM_D_LY, 0 CANCEL_ROOM_D_LY,
0 NETROOMREV_D_LY, 0 NETFBREV_D_LY, 0 NETMINIBARREV_D_LY, 0 NETTELEREV_D_LY, 0 NETMISCREV_D_LY,
0 NETREV_D_LY, 0 GROSSROOMREV_D_LY, 0 GROSSFBREV_D_LY, 0 GROSSMINIBARREV_D_LY,
0 GROSSTELEREV_D_LY, 0 GROSSMISCREV_D_LY, 0 GROSSREV_D_LY,
NOOFROOMS NOOFROOMS_M_LY, ADULTNO ADULTNO_M_LY, KIDS KIDS_M_LY, ARRIVALS ARRIVALS_M_LY, DEPARTURES DEPARTURES_M_LY, ARR_ADULTNO ARR_ADULTNO_M_LY,
DEP_ADULTNO DEP_ADULTNO_M_LY, ARR_KIDS ARR_KIDS_M_LY, DEP_KIDS DEP_KIDS_M_LY, NOSHOW_ROOM NOSHOW_ROOM_M_LY, CANCEL_ROOM CANCEL_ROOM_M_LY,
NETROOMREV NETROOMREV_M_LY, NETFBREV NETFBREV_M_LY, NETMINIBARREV NETMINIBARREV_M_LY, NETTELEREV NETTELEREV_M_LY, NETMISCREV NETMISCREV_M_LY,
NETREV NETREV_M_LY, GROSSROOMREV GROSSROOMREV_M_LY, GROSSFBREV GROSSFBREV_M_LY, GROSSMINIBARREV GROSSMINIBARREV_M_LY,
GROSSTELEREV GROSSTELEREV_M_LY, GROSSMISCREV GROSSMISCREV_M_LY, GROSSREV GROSSREV_M_LY,
0 NOOFROOMS_Y_LY, 0 ADULTNO_Y_LY, 0 KIDS_Y_LY, 0 ARRIVALS_Y_LY, 0 DEPARTURES_Y_LY, 0 ARR_ADULTNO_Y_LY,
0 DEP_ADULTNO_Y_LY, 0 ARR_KIDS_Y_LY, 0 DEP_KIDS_Y_LY, 0 NOSHOW_ROOM_Y_LY, 0 CANCEL_ROOM_Y_LY,
0 NETROOMREV_Y_LY, 0 NETFBREV_Y_LY, 0 NETMINIBARREV_Y_LY, 0 NETTELEREV_Y_LY, 0 NETMISCREV_Y_LY,
0 NETREV_Y_LY, 0 GROSSROOMREV_Y_LY, 0 GROSSFBREV_Y_LY, 0 GROSSMINIBARREV_Y_LY,
0 GROSSTELEREV_Y_LY, 0 GROSSMISCREV_Y_LY, 0 GROSSREV_Y_LY
FROM V8_STAT_SHIS_CHAN_MONTH_LY_CAD
UNION ALL
SELECT WDAT_DATE, CHANNEL_ID,YCAD_ID,
0 NOOFROOMS_D, 0 ADULTNO_D, 0 KIDS_D, 0 ARRIVALS_D, 0 DEPARTURES_D, 0 ARR_ADULTNO_D,
0 DEP_ADULTNO_D, 0 ARR_KIDS_D, 0 DEP_KIDS_D, 0 NOSHOW_ROOM_D, 0 CANCEL_ROOM_D,
0 NETROOMREV_D, 0 NETFBREV_D, 0 NETMINIBARREV_D, 0 NETTELEREV_D, 0 NETMISCREV_D,
0 NETREV_D, 0 GROSSROOMREV_D, 0 GROSSFBREV_D, 0 GROSSMINIBARREV_D,
0 GROSSTELEREV_D, 0 GROSSMISCREV_D, 0 GROSSREV_D,
0 NOOFROOMS_M, 0 ADULTNO_M, 0 KIDS_M, 0 ARRIVALS_M, 0 DEPARTURES_M, 0 ARR_ADULTNO_M,
0 DEP_ADULTNO_M, 0 ARR_KIDS_M, 0 DEP_KIDS_M, 0 NOSHOW_ROOM_M, 0 CANCEL_ROOM_M,
0 NETROOMREV_M, 0 NETFBREV_M, 0 NETMINIBARREV_M, 0 NETTELEREV_M, 0 NETMISCREV_M,
0 NETREV_M, 0 GROSSROOMREV_M, 0 GROSSFBREV_M, 0 GROSSMINIBARREV_M,
0 GROSSTELEREV_M, 0 GROSSMISCREV_M, 0 GROSSREV_M,
0 NOOFROOMS_Y, 0 ADULTNO_Y, 0 KIDS_Y, 0 ARRIVALS_Y, 0 DEPARTURES_Y, 0 ARR_ADULTNO_Y,
0 DEP_ADULTNO_Y, 0 ARR_KIDS_Y, 0 DEP_KIDS_Y, 0 NOSHOW_ROOM_Y, 0 CANCEL_ROOM_Y,
0 NETROOMREV_Y, 0 NETFBREV_Y, 0 NETMINIBARREV_Y, 0 NETTELEREV_Y, 0 NETMISCREV_Y,
0 NETREV_Y, 0 GROSSROOMREV_Y, 0 GROSSFBREV_Y, 0 GROSSMINIBARREV_Y,
0 GROSSTELEREV_Y, 0 GROSSMISCREV_Y, 0 GROSSREV_Y,
0 NOOFROOMS_D_LY, 0 ADULTNO_D_LY, 0 KIDS_D_LY, 0 ARRIVALS_D_LY, 0 DEPARTURES_D_LY, 0 ARR_ADULTNO_D_LY,
0 DEP_ADULTNO_D_LY, 0 ARR_KIDS_D_LY, 0 DEP_KIDS_D_LY, 0 NOSHOW_ROOM_D_LY, 0 CANCEL_ROOM_D_LY,
0 NETROOMREV_D_LY, 0 NETFBREV_D_LY, 0 NETMINIBARREV_D_LY, 0 NETTELEREV_D_LY, 0 NETMISCREV_D_LY,
0 NETREV_D_LY, 0 GROSSROOMREV_D_LY, 0 GROSSFBREV_D_LY, 0 GROSSMINIBARREV_D_LY,
0 GROSSTELEREV_D_LY, 0 GROSSMISCREV_D_LY, 0 GROSSREV_D_LY,
0 NOOFROOMS_M_LY, 0 ADULTNO_M_LY, 0 KIDS_M_LY, 0 ARRIVALS_M_LY, 0 DEPARTURES_M_LY, 0 ARR_ADULTNO_M_LY,
0 DEP_ADULTNO_M_LY, 0 ARR_KIDS_M_LY, 0 DEP_KIDS_M_LY, 0 NOSHOW_ROOM_M_LY, 0 CANCEL_ROOM_M_LY,
0 NETROOMREV_M_LY, 0 NETFBREV_M_LY, 0 NETMINIBARREV_M_LY, 0 NETTELEREV_M_LY, 0 NETMISCREV_M_LY,
0 NETREV_M_LY, 0 GROSSROOMREV_M_LY, 0 GROSSFBREV_M_LY, 0 GROSSMINIBARREV_M_LY,
0 GROSSTELEREV_M_LY, 0 GROSSMISCREV_M_LY, 0 GROSSREV_M_LY,
NOOFROOMS NOOFROOMS_Y_LY, ADULTNO ADULTNO_Y_LY, KIDS KIDS_Y_LY, ARRIVALS ARRIVALS_Y_LY, DEPARTURES DEPARTURES_Y_LY, ARR_ADULTNO ARR_ADULTNO_Y_LY,
DEP_ADULTNO DEP_ADULTNO_Y_LY, ARR_KIDS ARR_KIDS_Y_LY, DEP_KIDS DEP_KIDS_Y_LY, NOSHOW_ROOM NOSHOW_ROOM_Y_LY, CANCEL_ROOM CANCEL_ROOM_Y_LY,
NETROOMREV NETROOMREV_Y_LY, NETFBREV NETFBREV_Y_LY, NETMINIBARREV NETMINIBARREV_Y_LY, NETTELEREV NETTELEREV_Y_LY, NETMISCREV NETMISCREV_Y_LY,
NETREV NETREV_Y_LY, GROSSROOMREV GROSSROOMREV_Y_LY, GROSSFBREV GROSSFBREV_Y_LY, GROSSMINIBARREV GROSSMINIBARREV_Y_LY,
GROSSTELEREV GROSSTELEREV_Y_LY, GROSSMISCREV GROSSMISCREV_Y_LY, GROSSREV GROSSREV_Y_LY
FROM V8_STAT_SHIS_CHAN_YEAR_LY_CAD)
GROUP BY WDAT_DATE, CHANNEL_ID, YCAD_ID;

CREATE OR REPLACE VIEW V8_EDITOR_RES
AS
SELECT
  YRES.YRES_ID RESERVATION_ID,
  YRES.YRES_YBOM_ID BOOKINGMASTER_ID,
  YRES.YRES_CURRENT_YDET_ID RESERVATION_DETAIL_ID,
  YRES.YRES_EXPARRTIME ARRIVAL,
  YRES.YRES_EXPDEPTIME DEPARTURE,
  (YRES.YRES_EXPDEPTIME) - (YRES.YRES_EXPARRTIME) NIGHTS,
  TO_CHAR(YRES.YRES_EXPARRHOUR,'HH24:MI')                     ARRIVALTIME,
  TO_CHAR(YRES.YRES_EXPDEPHOUR,'HH24:MI')                     DEPARTURETIME,
  (select YDET.YDET_YCAT_ID from ydet where ydet.ydet_id=YRES.YRES_CURRENT_YDET_ID) YCAT_ID,
  (SELECT YDET.YDET_NOOFROOMS FROM YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID)                                               NOOFROOMS,
  (SELECT YCAT.YCAT_SHORTDESC FROM YCAT,YDET WHERE YCAT.YCAT_ID=YDET.YDET_YCAT_ID AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID)      ROOMTYPE,
  (SELECT YCAT.YCAT_LONGDESC FROM YCAT,YDET WHERE YCAT.YCAT_ID=YDET.YDET_YCAT_ID AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID)      ROOMTYPE_DESC,
  WTRL_TRANSLATE (
            'YCAT',
            (SELECT   YDET.YDET_YCAT_ID
               FROM   YDET
              WHERE   YDET.YDET_ID = YRES.YRES_CURRENT_YDET_ID),
            'YCAT_LONGDESC',
            NVL ( (SELECT   XCMS.XCMS_WLAN_ID
                     FROM   XCMS
                    WHERE   XCMS.XCMS_ID = YRES.YRES_XCMS_ID),
                 (SELECT   ygrp.YGRP_WLAN_ID
                    FROM   ygrp
                   WHERE   ygrp.YGRP_ID = yres.YRES_YGRP_ID))
         )
            TRANSLATED_TYPE_DESC,
  (SELECT YGCO.YGCO_SHORTDESC FROM YGCO WHERE YGCO.YGCO_ID=YRES.YRES_YGCO_ID)                                                        GUARANTEECODE,
  (SELECT YGCO.YGCO_LONGDESC FROM YGCO WHERE YGCO.YGCO_ID=YRES.YRES_YGCO_ID)                                                      GUARANTEECODE_DESC,
  DECODE(YRES.YRES_RESSTATUS,0,'TENTATIVE', 1,'DEFINITE',3,'NOT')                                                                 RESSTATUS,
  DECODE(YRES.YRES_NOAVAILREASON,1,'WAITLIST',2,'OFFER',3,'CANCELED',4,'NOSHOW')                                                  NOAVAILREASON,
  YRES.YRES_WALKIN                                                                                                                  WALKIN,
  YRES.YRES_FREETEXT1                                                                                                              FREETEXT1,
  YRES.YRES_FREETEXT2                                                                                                              FREETEXT2,
  NVL(YRES.YRES_HASSHARE,0)                                                                                                      HASSHARE,
  DECODE(YRES.YRES_HASSHARE,1,(DECODE(YRES.YRES_SHARETYPE,0,'INDEPENDANT',1,'MASTER',2,'SLAVE')),' ')                             SHARETYPE,
  (SELECT YDET.YDET_ADULTNO FROM YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID)                                              ADULTNO,
  NVL((SELECT SUM(YRGL_NUMBER) FROM YRGL,YDET WHERE YRGL.YRGL_YDET_ID=YDET.YDET_ID AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID ),0) CHILDNO,
  (SELECT YDET.YDET_YRMS_ID FROM YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID)                                                   ROOM_ID,
  (SELECT YRMS.YRMS_SHORTDESC FROM YRMS,YDET WHERE YRMS.YRMS_ID=YDET.YDET_YRMS_ID AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID)      ROOMNUMBER,
  (SELECT YRMS.YRMS_LONGDESC FROM YRMS,YDET WHERE YRMS.YRMS_ID=YDET.YDET_YRMS_ID AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID)      ROOMNUMBER_LDESC,  
  (SELECT YDET.YDET_YCAT_ID FROM YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID)                                                ROOMTYPE_ID,
  (SELECT YDET.YDET_YCAT_ID_RATE FROM YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID)                                          RATEROOMTYPE_ID,
  (SELECT YCAT.YCAT_SHORTDESC FROM YCAT,YDET WHERE YCAT.YCAT_ID=YDET.YDET_YCAT_ID_RATE AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) RATEROOMTYPE,
  (SELECT YCAT.YCAT_LONGDESC FROM YCAT,YDET WHERE YCAT.YCAT_ID=YDET.YDET_YCAT_ID_RATE AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) RATEROOMTYPE_DESC,
  (SELECT YBLH.YBLH_ID FROM YBLH,YBLD,YDET WHERE YBLH.YBLH_ID=YBLD.YBLD_YBLH_ID AND YBLD.YBLD_ID=YDET.YDET_YBLD_ID AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) BLOCK_ID,
  (SELECT YBLH.YBLH_SHORTDESC FROM YBLH,YBLD,YDET WHERE YBLH.YBLH_ID=YBLD.YBLD_YBLH_ID AND YBLD.YBLD_ID=YDET.YDET_YBLD_ID AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) BLOCK_DESC,
  NVL((SELECT YDET.YDET_RATEAMOUNT FROM YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID),0)                                          RATEAMOUNT, --Daily rate per room + all packages--
  NVL((SELECT YDET.YDET_RATEDIFF FROM YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID),0)                                      RATEDIFF,
  NVL((SELECT YDET.YDET_RATEDISCOUNTPERC FROM YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID),0)                              RATEDISCOUNTPERC,
  (SELECT NVL(YDET.YDET_RATEMANUAL,0) FROM YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID)                                      RATEMANUAL, --Manual flagged rate.If add on package is flagged it is automatically added--
  (SELECT getYDetDiscountedAmountForeign(ydet_rateamount,ydet_ratemanual,ydet_ratediff,ydet_ratediscountperc,ydet_nodiscountpackage,ydet_foreignrateamount,ydet_foreignratemanual,ydet_foreignratediff,ydet_foreignnodiscountpackage,ydet_finalAmount,ydet_finalForeignAmount)         
   FROM YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) CONF_RATE, --Daily rate per room + all packages--
  V8_FNC_NUM_TO_CHAR_FORMATING((SELECT getYDetDiscountedAmountForeign(ydet_rateamount,ydet_ratemanual,ydet_ratediff,ydet_ratediscountperc,ydet_nodiscountpackage,ydet_foreignrateamount,ydet_foreignratemanual,ydet_foreignratediff,ydet_foreignnodiscountpackage,ydet_finalAmount,ydet_finalForeignAmount)         
   FROM YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID)) CONF_RATE_GS,-- Formatted Daily rate per room + all packages--
  V8_FNC_VALCURR_FORMATING(
  (SELECT getYDetDiscountedAmountForeign(ydet_rateamount,ydet_ratemanual,ydet_ratediff,ydet_ratediscountperc,ydet_nodiscountpackage,ydet_foreignrateamount,ydet_foreignratemanual,ydet_foreignratediff,ydet_foreignnodiscountpackage,ydet_finalAmount,ydet_finalForeignAmount)         
   FROM YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID),(SELECT zcur.zcur_shortdesc FROM zcur WHERE zcur_id = yres_contract_zcur_id)) FCONF_RATE_GS, -- Formatted Daily rate per room + all packages with currency--
   (SELECT SUM(YPOS.YPOS_UNITPRICE) FROM YPOS WHERE YPOS.YPOS_YRES_ID = YRES.YRES_ID AND YPOS.YPOS_TYPE <> 1) CONF_RATE_TOTAL, --Rate + all packages per stay per room--
   (SELECT SUM(YPOS.YPOS_QUANTITY* YPOS.YPOS_UNITPRICE) FROM YPOS WHERE YPOS.YPOS_YRES_ID=YRES.YRES_ID AND YPOS.YPOS_TYPE<>1) CONF_RATE_STAY, --Total rate per stay + all packages--
   V8_FNC_NUM_TO_CHAR_FORMATING((SELECT SUM(YPOS.YPOS_QUANTITY* YPOS.YPOS_UNITPRICE) FROM YPOS WHERE YPOS.YPOS_YRES_ID=YRES.YRES_ID AND YPOS.YPOS_TYPE<>1)) CONF_RATE_STAY_GS, -- Formatted total rate per stay + all packages--
   V8_FNC_VALCURR_FORMATING((SELECT SUM(YPOS.YPOS_QUANTITY* YPOS.YPOS_UNITPRICE) FROM YPOS WHERE YPOS.YPOS_YRES_ID=YRES.YRES_ID AND YPOS.YPOS_TYPE<>1),
  (SELECT zcur.zcur_shortdesc FROM zcur WHERE zcur_id = yres_contract_zcur_id)) FCONF_RATE_STAY_GS, -- Formatted total rate per stay + all package with currency--
   (SELECT  getYDetDiscountedAmountForeign(ydet_rateamount,ydet_ratemanual,ydet_ratediff,ydet_ratediscountperc,ydet_nodiscountpackage,ydet_foreignrateamount,ydet_foreignratemanual,ydet_foreignratediff,ydet_foreignnodiscountpackage,ydet_finalAmount,ydet_finalForeignAmount)           
   FROM YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) 
   + NVL((SELECT SUM(decode(ydet_foreignrateamount,null,yfxc_amount,nvl(yfxc_foreignamount,yfxc_amount))*YFXC.YFXC_QUANTITY) FROM YFXC,YDET WHERE YFXC.YFXC_YDET_ID=YDET.YDET_ID AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID),0) CONF_RATE_FIXCHARGE, -- Conf_rate + daily fixed charges
  (SELECT SUM(YPOS.YPOS_QUANTITY* YPOS.YPOS_UNITPRICE) FROM YPOS WHERE YPOS.YPOS_YRES_ID=YRES.YRES_ID) CONF_RATE_FIXCHARGE_STAY, --Total rate per stay + all packages + fixed charges --
  (SELECT SUM (YPOS.YPOS_UNITPRICE) FROM YPOS,YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID AND YPOS.YPOS_YRES_ID = YRES.YRES_ID AND YDET.YDET_DATE=YPOS.YPOS_DATE AND YPOS.YPOS_DT <> 2 AND YPOS.YPOS_TYPE<>1) CONF_RATE_NET,--Daily rate per room + all packages Net --
  (SELECT SUM(YPOS.YPOS_UNITPRICE) FROM YPOS WHERE YPOS.YPOS_YRES_ID = YRES.YRES_ID AND YPOS.YPOS_TYPE <> 1 AND YPOS.YPOS_DT <> 2 ) CONF_RATE_NET_TOTAL, --Rate + all packages per stay per room Net-- 
  (SELECT SUM (YPOS.YPOS_QUANTITY * YPOS.YPOS_UNITPRICE) FROM YPOS WHERE YPOS.YPOS_YRES_ID = YRES.YRES_ID AND YPOS.YPOS_TYPE <> 1 AND YPOS.YPOS_DT <> 2 ) CONF_RATE_NET_STAY, --Total rate per stay + all packages Net--  
  (SELECT getYDetDiscountedAmountForeign(ydet_rateamount,ydet_ratemanual,ydet_ratediff,ydet_ratediscountperc,ydet_nodiscountpackage,ydet_foreignrateamount,ydet_foreignratemanual,ydet_foreignratediff,ydet_foreignnodiscountpackage,ydet_finalAmount,ydet_finalForeignAmount)
         -NVL((SELECT SUM(DECODE(YPAL.YPAL_INCLUDED, 0, decode(ydet_foreignrateamount,null,YPAL_VALUE,ypal_foreignvalue)*ypal_multiplier, 0)) FROM YPAL WHERE YPAL.YPAL_YDET_ID=YDET.YDET_ID),0) 
   FROM YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) CONF_RATE_NOADDON, -- Conf_rate excluding add on package--
  V8_FNC_NUM_TO_CHAR_FORMATING(
  (SELECT getYDetDiscountedAmountForeign(ydet_rateamount,ydet_ratemanual,ydet_ratediff,ydet_ratediscountperc,ydet_nodiscountpackage,ydet_foreignrateamount,ydet_foreignratemanual,ydet_foreignratediff,ydet_foreignnodiscountpackage,ydet_finalAmount,ydet_finalForeignAmount)
         -NVL((SELECT SUM(DECODE(YPAL.YPAL_INCLUDED, 0, decode(ydet_foreignrateamount,null,YPAL_VALUE,ypal_foreignvalue)*ypal_multiplier, 0)) FROM YPAL WHERE YPAL.YPAL_YDET_ID=YDET.YDET_ID),0) 
   FROM YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID)) CONF_RATE_NOADDON_GS, --Formatted Conf_rate excluding add on package--
  (SELECT  getYDetDiscountedAmount(ydet_rateamount,ydet_ratemanual,ydet_ratediff,ydet_ratediscountperc,ydet_nodiscountpackage,ydet_finalAmount)
          -NVL((SELECT SUM(DECODE(YPAL.YPAL_INCLUDED,0,decode(ydet_foreignrateamount,null,YPAL_VALUE,ypal_foreignvalue)*YPAL.YPAL_MULTIPLIER,0)) FROM YPAL WHERE YPAL.YPAL_YDET_ID=YDET.YDET_ID),0) 
   FROM YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) 
   + NVL((SELECT SUM(decode(ydet_foreignrateamount,null,yfxc_amount,nvl(yfxc_foreignamount,yfxc_amount))*YFXC.YFXC_QUANTITY) FROM YFXC,YDET WHERE YFXC.YFXC_YDET_ID=YDET.YDET_ID AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID),0) CONF_RATE_NOADDON_FIXCHARGE, --Conf_rate excluding add on  packages + all fixed charges--
  (SELECT zcur.zcur_shortdesc FROM zcur WHERE zcur_id = yres_contract_zcur_id) Currency,
  ((SELECT getYDetDiscountedAmount(ydet_rateamount,ydet_ratemanual,ydet_ratediff,ydet_ratediscountperc,ydet_nodiscountpackage,ydet_finalAmount)  
    FROM YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID)) CALC_PRICE, -- Same as conf_rate Depreciated, please use conf_rate.
  (SELECT YDET_FINALAMOUNT FROM YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) FINAL_AMOUNT, --added just for controlling purpose
  (SELECT (NVL((SELECT SUM(DECODE(YPAL.YPAL_INCLUDED,0,YPAL.YPAL_VALUE,0)) FROM YPAL WHERE YPAL.YPAL_YDET_ID=YDET.YDET_ID),0)) FROM YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID)                                         ADD_ON_PACKAGES, -- Total add on packages first night--
  NVL((SELECT SUM(YFXC.YFXC_AMOUNT*YFXC.YFXC_QUANTITY) FROM YFXC,YDET WHERE YFXC.YFXC_YDET_ID=YDET.YDET_ID AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID),0)                                                                     TOTAL_FIX_CHARGES, -- Total fidex charges first night--
  (SELECT SUM(YPOS.YPOS_QUANTITY* YPOS.YPOS_UNITPRICE) FROM YPOS WHERE YPOS.YPOS_YRES_ID=YRES.YRES_ID AND YPOS.YPOS_TYPE=1) FIXCHARGE_STAY, --Total fixed charges for the stay--
  (SELECT SUM(YPOS.YPOS_QUANTITY* YPOS.YPOS_UNITPRICE) FROM YPOS,YDET WHERE YPOS.YPOS_YRES_ID=YRES.YRES_ID and ydet_id=nvl(YRES.YRES_CURRENT_YDET_ID,YRES.YRES_LAST_YDET_ID) and YDET.YDET_DATE=YPOS.YPOS_DATE AND YPOS.YPOS_DT<>2 )        RATE_NET, -- Daily Rate + all packages + Fixed charges All rooms Net-- 
  (SELECT SUM(YPOS.YPOS_QUANTITY* YPOS.YPOS_UNITPRICE) FROM YPOS WHERE YPOS.YPOS_YRES_ID=YRES.YRES_ID  AND YPOS.YPOS_DT<>2 )                                                                                                                  RATE_NET_STAY,--Total rate per stay + all packages + fixed charges Net (CONF_RATE_FIXCHARGE_STAY NET)--
  (SELECT YDET.YDET_YRCH_ID FROM YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID)                                                                                                                                               RATECODE_ID,
  (SELECT YRCH.YRCH_SHORTDESC FROM YRCH,YDET WHERE YRCH.YRCH_ID=YDET.YDET_YRCH_ID AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID)                                                                                                 RATECODE,
  (SELECT YRCH.YRCH_LONGDESC FROM YRCH,YDET WHERE YRCH.YRCH_ID=YDET.YDET_YRCH_ID AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID)                                                                                                 RATECODE_DESC,
  (SELECT YRCH.YRCH_RATEINFO FROM YRCH,YDET WHERE YRCH.YRCH_ID=YDET.YDET_YRCH_ID AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID)                                                                                                 RATECODE_INFO,
DECODE ((SELECT YRCH.YRCH_BOARDTYPE FROM YRCH, YDET WHERE YRCH.YRCH_ID = YDET.YDET_YRCH_ID AND YDET.YDET_ID = YRES.YRES_CURRENT_YDET_ID),
               0,'No Board',1,'Breakfast',2,'Breakfast and Lunch',3,'Breakfast and Dinner',4,'Full Board',5,'All Inclusive')BOARDTYPE,
  (SELECT YRCH.YRCH_FOLIOTEXT FROM YRCH,YDET WHERE YRCH.YRCH_ID=YDET.YDET_YRCH_ID AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID)                                                                                                 RATECODE_TEXT,
  DECODE((NVL((SELECT YRCH.YRCH_COMPSTATUS FROM YRCH,YDET WHERE YRCH.YRCH_ID=YDET.YDET_YRCH_ID AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID),0)),1,'COMP',2,'HOUSE USE','')                                                     COMPSTATUS,
  (CASE WHEN (NVL(YRES.YRES_XCMS_ID,0)=0 AND NVL(YRES.YRES_YGRP_ID,0)<>0) THEN 1 ELSE 0 END)                                                                                                                                       UNSPLITTED_GROUP_RES,
  YRES.YRES_XCMS_ID                                                                                                                                                                                                                   GUEST_ID,
  (SELECT V8_REP_NAME.NAME2 FROM V8_REP_NAME WHERE V8_REP_NAME.XCMS_ID=YRES.YRES_XCMS_ID)                                                                                                                                     GUEST_NAME,
  (SELECT V8_REP_NAME.NAME3 FROM V8_REP_NAME WHERE V8_REP_NAME.XCMS_ID=YRES.YRES_XCMS_ID)                                                                                                                                     GUEST_GREET_NAME,
  (SELECT TO_NUMBER(TO_CHAR(XCID.XCID_BIRTHTIME, 'DD')) FROM XCID WHERE XCID.XCID_ID=YRES.YRES_XCMS_ID)                                                                                                                      GUEST_BIRTHDATE_DAY,
  (SELECT TO_NUMBER(TO_CHAR(XCID.XCID_BIRTHTIME, 'MM')) FROM XCID WHERE XCID.XCID_ID=YRES.YRES_XCMS_ID)                                                                                                                      GUEST_BIRTHDATE_MONTH,
  (SELECT TO_NUMBER(TO_CHAR(XCID.XCID_BIRTHTIME, 'YYYY')) FROM XCID WHERE XCID.XCID_ID=YRES.YRES_XCMS_ID)                                                                                                                      GUEST_BIRTHDATE_YEAR,
  (SELECT YGRP.YGRP_NAME FROM YGRP WHERE YGRP.YGRP_ID=YRES.YRES_YGRP_ID)                                                                                                                                                     GROUP_RESERVATION_NAME,
  (CASE WHEN (NVL(YRES.YRES_XCMS_ID,0)=0 AND NVL(YRES.YRES_YGRP_ID,0)<>0) THEN (SELECT YGRP.YGRP_NAME FROM YGRP WHERE YGRP.YGRP_ID=YRES.YRES_YGRP_ID) ELSE (SELECT V8_REP_NAME.NAME2 FROM V8_REP_NAME WHERE V8_REP_NAME.XCMS_ID=YRES.YRES_XCMS_ID) END) NAME,
  (SELECT YGRP.YGRP_NAME FROM YGRP,YDET WHERE YGRP.YGRP_ID=YDET.YDET_YGRP_ID AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID)                                                                                                                  GROUP_DETAIL_NAME,
  (CASE WHEN (NVL(YRES.YRES_XCMS_ID,0)=0 AND NVL(YRES.YRES_YGRP_ID,0)<>0) THEN (SELECT YGRP.YGRP_NAME FROM YGRP WHERE YGRP.YGRP_ID=YRES.YRES_YGRP_ID) ELSE (SELECT YGRP.YGRP_NAME FROM YGRP,YDET WHERE YGRP.YGRP_ID=YDET.YDET_YGRP_ID AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) END) GROUP_NAME,
  (CASE WHEN (NVL(YRES.YRES_XCMS_ID,0)=0 AND NVL(YRES.YRES_YGRP_ID,0)<>0) THEN (SELECT YGRP.YGRP_ID FROM YGRP WHERE YGRP.YGRP_ID=YRES.YRES_YGRP_ID) ELSE (SELECT YGRP.YGRP_ID FROM YGRP,YDET WHERE YGRP.YGRP_ID=YDET.YDET_YGRP_ID AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) END) GROUP_ID,
  (SELECT V8_REP_NAME.NAME2 FROM V8_REP_NAME,YCLN,YDET WHERE V8_REP_NAME.XCMS_ID=YCLN.YCLN_XCMS_ID AND YCLN.YCLN_YDET_ID=YDET.YDET_ID AND YCLN.YCLN_XCCA_INTERNALCATEGORY=1 AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) COMPANY_NAME,
  (SELECT V8_REP_NAME.NAME2 FROM V8_REP_NAME,YCLN,YDET WHERE V8_REP_NAME.XCMS_ID=YCLN.YCLN_XCMS_ID AND YCLN.YCLN_YDET_ID=YDET.YDET_ID AND YCLN.YCLN_XCCA_INTERNALCATEGORY=2 AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) TRAVELAGENT_NAME,
  (SELECT XCMS_ID FROM XCMS,YCLN,YDET WHERE XCMS.XCMS_ID=YCLN.YCLN_XCMS_ID AND YCLN.YCLN_YDET_ID=YDET.YDET_ID AND YCLN.YCLN_XCCA_INTERNALCATEGORY=2 AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID)                         TRAVELAGENT_ID,
  (SELECT V8_REP_NAME.NAME2 FROM V8_REP_NAME,YCLN,YDET WHERE V8_REP_NAME.XCMS_ID=YCLN.YCLN_XCMS_ID AND YCLN.YCLN_YDET_ID=YDET.YDET_ID AND YCLN.YCLN_XCCA_INTERNALCATEGORY=3 AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) SOURCE_NAME,
  (SELECT V8_REP_NAME.NAME2 FROM V8_REP_NAME,YCLN,YDET WHERE V8_REP_NAME.XCMS_ID=YCLN.YCLN_XCMS_ID AND YCLN.YCLN_YDET_ID=YDET.YDET_ID AND YCLN.YCLN_XCCA_INTERNALCATEGORY=4 AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) BOOKER_NAME,
  (SELECT V8_REP_NAME.NAME2 FROM V8_REP_NAME,YCLN,YDET WHERE V8_REP_NAME.XCMS_ID=YCLN.YCLN_XCMS_ID AND YCLN.YCLN_YDET_ID=YDET.YDET_ID AND YCLN.YCLN_XCCA_INTERNALCATEGORY=5 AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) VENDOR_NAME,
  (SELECT V8_REP_NAME.NAME2 FROM V8_REP_NAME,YCLN,YDET WHERE V8_REP_NAME.XCMS_ID=YCLN.YCLN_XCMS_ID AND YCLN.YCLN_YDET_ID=YDET.YDET_ID AND YCLN.YCLN_XCCA_INTERNALCATEGORY=6 AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) MPLANER_NAME,
  (SELECT YDET.YDET_XCMA_ID FROM YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID)                                                                                                                                               MARKETCODE_ID,
  (SELECT XCMA.XCMA_SHORTDESC FROM XCMA,YDET WHERE XCMA.XCMA_ID=YDET.YDET_XCMA_ID AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID)                                                                                             MARKETCODE_DESC,
  (SELECT YRAS.YRAS_ID FROM YRAL,YRAS,YRAC,YDET WHERE YRAL.YRAL_YDET_ID = YDET.YDET_ID AND YRAL.YRAL_YRAS_ID = YRAS.YRAS_ID AND YRAL.YRAL_YRAC_ID = YRAC.YRAC_ID AND YRAC.YRAC_ID = 1 AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID AND ROWNUM=1)                                   SOURCECODE_ID,
  (SELECT YRAS.YRAS_SHORTDESC FROM YRAL,YRAS,YRAC,YDET WHERE YRAL.YRAL_YDET_ID = YDET.YDET_ID AND YRAL.YRAL_YRAS_ID = YRAS.YRAS_ID AND YRAL.YRAL_YRAC_ID = YRAC.YRAC_ID AND YRAC.YRAC_ID = 1 AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID AND ROWNUM=1)                           SOURCECODE_DESC,
  (SELECT YRAS.YRAS_ID FROM YRAL,YRAS,YRAC,YDET WHERE YRAL.YRAL_YDET_ID = YDET.YDET_ID AND YRAL.YRAL_YRAS_ID = YRAS.YRAS_ID AND YRAL.YRAL_YRAC_ID = YRAC.YRAC_ID AND YRAC.YRAC_ID = 2 AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID AND ROWNUM=1)                                   CHANNELCODE_ID,
  (SELECT YRAS.YRAS_SHORTDESC FROM YRAL,YRAS,YRAC,YDET WHERE YRAL.YRAL_YDET_ID = YDET.YDET_ID AND YRAL.YRAL_YRAS_ID = YRAS.YRAS_ID AND YRAL.YRAL_YRAC_ID = YRAC.YRAC_ID AND YRAC.YRAC_ID = 2 AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID AND ROWNUM=1)                           CHANNELCODE_DESC,
  (SELECT COUNT(YRES_S.YRES_ID) FROM YRES YRES_S WHERE YRES_S.YRES_XCMS_ID=YRES.YRES_XCMS_ID AND YRES_S.YRES_EXPDEPTIME<YRES.YRES_EXPARRTIME AND YRES_S.YRES_RESSTATUS<>3)                     NO_OF_PREV_STAYS,
  (SELECT COUNT(YDET.YDET_ID) FROM YDET,YRES YRES_S WHERE YDET.YDET_YRES_ID=YRES_S.YRES_ID AND YRES_S.YRES_XCMS_ID=YRES.YRES_XCMS_ID AND YRES_S.YRES_EXPDEPTIME<YRES.YRES_EXPARRTIME AND YRES_S.YRES_RESSTATUS<>3)         NO_OF_PREV_ROOM_NIGHTS,
  (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=YRES.YRES_XCMS_ID)                                                                       VIPCODE,
  (CASE WHEN YRES.YRES_YRMS_ID<>(SELECT YDET.YDET_YRMS_ID FROM YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) THEN 1 ELSE 0 END)                                                                                     EXP_ROOM_MOVE_TODAY,
  (CASE WHEN YRES.YRES_YRMS_ID=(SELECT YDET.YDET_YRMS_ID FROM YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) AND YRES.YRES_YRMS_ID<>(SELECT YDET.YDET_YRMS_ID FROM YDET WHERE YDET.YDET_YRES_ID=YRES.YRES_ID AND YDET.YDET_DATE=V8_sys_fideliodate - 1) THEN 1 ELSE 0 END) ROOM_MOVE_DONE_TODAY,
  (SELECT YDET.YDET_YRMS_ID FROM YDET WHERE YDET.YDET_YRES_ID=YRES.YRES_ID AND YDET.YDET_DATE=V8_sys_fideliodate)                                                                                             TODAY_ROOM_MOVE_TO_ROOM_ID,
  (SELECT YRMS.YRMS_SHORTDESC FROM YRMS,YDET WHERE YRMS.YRMS_ID=YDET.YDET_YRMS_ID AND YDET.YDET_YRES_ID=YRES.YRES_ID AND YDET.YDET_DATE=V8_sys_fideliodate)                                         TODAY_ROOM_MOVE_TO_ROOM,
  (CASE WHEN YRES.YRES_YRMS_ID<>(SELECT YDET.YDET_YRMS_ID FROM YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) THEN YRES.YRES_YRMS_ID ELSE (SELECT YDET.YDET_YRMS_ID FROM YDET WHERE YDET.YDET_YRES_ID=YRES.YRES_ID AND YDET.YDET_DATE=V8_sys_fideliodate - 1) END) TODAY_ROOM_MOVE_FROM_ROOM_ID,
  (CASE WHEN YRES.YRES_YRMS_ID<>(SELECT YDET.YDET_YRMS_ID FROM YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) THEN (SELECT YRMS.YRMS_SHORTDESC FROM YRMS WHERE YRMS.YRMS_ID=YRES.YRES_YRMS_ID) ELSE (SELECT YRMS.YRMS_SHORTDESC FROM YRMS,YDET WHERE YRMS.YRMS_ID=YDET.YDET_YRMS_ID AND YDET.YDET_YRES_ID=YRES.YRES_ID AND YDET.YDET_DATE=V8_sys_fideliodate - 1) END) TODAY_ROOM_MOVE_FROM_ROOM,
  (SELECT MIN(TRUNC(YRES_S.YRES_ACTARRTIME)) FROM YRES YRES_S WHERE YRES_S.YRES_XCMS_ID=YRES.YRES_XCMS_ID AND YRES_S.YRES_EXPDEPTIME<YRES.YRES_EXPARRTIME AND YRES_S.YRES_RESSTATUS<>3)                                    FIRST_ARRIVAL,
  (SELECT MAX(TRUNC(YRES_S.YRES_ACTARRTIME)) FROM YRES YRES_S WHERE YRES_S.YRES_XCMS_ID=YRES.YRES_XCMS_ID AND YRES_S.YRES_EXPDEPTIME<YRES.YRES_EXPARRTIME AND YRES_S.YRES_RESSTATUS<>3)                                    LAST_ARRIVAL,
  (SELECT YRMS.YRMS_SHORTDESC FROM YRMS,YDET YDET_S WHERE YRMS.YRMS_ID=YDET_S.YDET_YRMS_ID AND YDET_S.YDET_ID=(SELECT MAX(YDET.YDET_ID) FROM YDET WHERE YDET.YDET_YRES_ID=(SELECT YRES_S2.YRES_ID FROM YRES YRES_S2 WHERE TRUNC(YRES_S2.YRES_ACTDEPTIME)=(SELECT MAX(TRUNC(YRES_S.YRES_ACTDEPTIME)) FROM YRES YRES_S WHERE YRES_S.YRES_XCMS_ID=YRES.YRES_XCMS_ID AND YRES_S.YRES_EXPDEPTIME<YRES.YRES_EXPARRTIME AND YRES_S.YRES_RESSTATUS<>3) AND ROWNUM=1))) LAST_ROOMNUMBER,
  (SELECT MIN(TRUNC(YRES_S.YRES_EXPARRTIME)) FROM YRES YRES_S WHERE YRES_S.YRES_XCMS_ID=YRES.YRES_XCMS_ID AND YRES_S.YRES_EXPARRTIME>YRES.YRES_EXPARRTIME AND YRES_S.YRES_RESSTATUS<>3)                                    NEXT_ARRIVAL,
  (SELECT MAX(TRUNC(YRES_S.YRES_ACTDEPTIME)) FROM YRES YRES_S WHERE YRES_S.YRES_XCMS_ID=YRES.YRES_XCMS_ID AND YRES_S.YRES_EXPDEPTIME<YRES.YRES_EXPARRTIME AND YRES_S.YRES_RESSTATUS<>3)                                 LAST_DEPARTURE,
  (SELECT  getYDetDiscountedAmountForeign(ydet_rateamount,ydet_ratemanual,ydet_ratediff,ydet_ratediscountperc,ydet_nodiscountpackage,ydet_foreignrateamount,ydet_foreignratemanual,ydet_foreignratediff,ydet_foreignnodiscountpackage,ydet_finalAmount,ydet_finalForeignAmount)           
   FROM YDET WHERE YDET.YDET_ID=(SELECT MAX(YDET.YDET_ID) FROM YDET WHERE YDET.YDET_YRES_ID=(SELECT YRES_S2.YRES_ID FROM YRES YRES_S2 WHERE TRUNC(YRES_S2.YRES_ACTDEPTIME)=(SELECT MAX(TRUNC(YRES_S.YRES_ACTDEPTIME)) FROM YRES YRES_S WHERE YRES_S.YRES_XCMS_ID=YRES.YRES_XCMS_ID AND YRES_S.YRES_EXPDEPTIME<YRES.YRES_EXPARRTIME AND YRES_S.YRES_RESSTATUS<>3) AND ROWNUM=1))) LAST_CONF_RATE,  YRES.YRES_OPTION                                                                                                                                                                                                                         OPTIONDATE,
  (SELECT YCXL_3.YCXL_NUMBER FROM YCXL YCXL_3 WHERE YCXL_3.YCXL_ID=(SELECT MAX(YCXL_2.YCXL_ID) FROM YCXL YCXL_2 WHERE YCXL_2.YCXL_YRES_ID=YRES.YRES_ID AND YCXL_2.YCXL_CANCELORREINSTATE=1))                             CANCELLATION_NUMBER,
  (SELECT YCXL.YCXL_DATE FROM YCXL WHERE YCXL.YCXL_ID=(SELECT MAX(YCXL_2.YCXL_ID) FROM YCXL YCXL_2 WHERE YCXL_2.YCXL_YRES_ID=YRES.YRES_ID AND YCXL_2.YCXL_CANCELORREINSTATE=1))                                          CANCELLATION_DATE,
  (SELECT YCXL.YCXL_REASON FROM YCXL WHERE YCXL.YCXL_ID=(SELECT MAX(YCXL_2.YCXL_ID) FROM YCXL YCXL_2 WHERE YCXL_2.YCXL_YRES_ID=YRES.YRES_ID AND YCXL_2.YCXL_CANCELORREINSTATE=1))                                          CANCELLATION_REASON,
  (SELECT TRUNC(YCXL.YCXL_PCDATE) FROM YCXL WHERE YCXL.YCXL_ID=(SELECT MAX(YCXL_2.YCXL_ID) FROM YCXL YCXL_2 WHERE YCXL_2.YCXL_YRES_ID=YRES.YRES_ID AND YCXL_2.YCXL_CANCELORREINSTATE=1))                                  CANCELLATION_PCDATE,
  (SELECT TO_CHAR(YCXL.YCXL_PCDATE,'HH24:MI') FROM YCXL WHERE YCXL.YCXL_ID=(SELECT MAX(YCXL_2.YCXL_ID) FROM YCXL YCXL_2 WHERE YCXL_2.YCXL_YRES_ID=YRES.YRES_ID AND YCXL_2.YCXL_CANCELORREINSTATE=1))                     CANCELLATION_PCTIME,
  DECODE(NVL((SELECT MAX(YWLT.YWLT_ID) FROM YWLT WHERE YWLT.YWLT_YRES_ID=YRES.YRES_ID AND YWLT.YWLT_ACCEPTEDDATE IS NULL),0),0,0,1)                                                                                           IS_WAITLIST,
  (SELECT YWLP.YWLP_LONGDESC FROM YWLP WHERE YWLP.YWLP_ID=(SELECT YWLT.YWLT_YWLP_ID FROM YWLT WHERE YWLT.YWLT_ID=(SELECT MAX(YWLT_2.YWLT_ID) FROM YWLT YWLT_2 WHERE YWLT_2.YWLT_YRES_ID=YRES.YRES_ID AND YWLT_2.YWLT_ACCEPTEDDATE IS NULL)))  WAITLIST_DESC,
  (SELECT YWLP.YWLP_PRIORITY FROM YWLP WHERE YWLP.YWLP_ID=(SELECT YWLT.YWLT_YWLP_ID FROM YWLT WHERE YWLT.YWLT_ID=(SELECT MAX(YWLT_2.YWLT_ID) FROM YWLT YWLT_2 WHERE YWLT_2.YWLT_YRES_ID=YRES.YRES_ID AND YWLT_2.YWLT_ACCEPTEDDATE IS NULL)))  WAITLIST_PRIORITY,
  (SELECT XCOM.XCOM_VALUE FROM XCOM WHERE XCOM.XCOM_ID =(SELECT YWLT.YWLT_XCOM_ID FROM YWLT WHERE YWLT.YWLT_ID=(SELECT MAX(YWLT_2.YWLT_ID) FROM YWLT YWLT_2 WHERE YWLT_2.YWLT_YRES_ID=YRES.YRES_ID AND YWLT_2.YWLT_ACCEPTEDDATE IS NULL)))    WAITLIST_COMM,
  (SELECT XCMT.XCMT_LONGDESC FROM XCMT WHERE XCMT.XCMT_ID=(SELECT XCOM.XCOM_XCMT_ID FROM XCOM WHERE XCOM.XCOM_ID =(SELECT YWLT.YWLT_XCOM_ID FROM YWLT WHERE YWLT.YWLT_ID=(SELECT MAX(YWLT_2.YWLT_ID) FROM YWLT YWLT_2 WHERE YWLT_2.YWLT_YRES_ID=YRES.YRES_ID AND YWLT_2.YWLT_ACCEPTEDDATE IS NULL)))) WAITLIST_COMM_TYPE,
  (CASE WHEN (NVL(YRES.YRES_XCMS_ID,0)=0 AND NVL(YRES.YRES_YGRP_ID,0)<>0) THEN (SELECT YGRP.YGRP_XCOU_ID FROM YGRP WHERE YGRP.YGRP_ID=YRES.YRES_YGRP_ID) ELSE (SELECT XADR.XADR_XCOU_ID FROM XADR WHERE XADR.XADR_XCMS_ID=YRES.YRES_XCMS_ID AND XADR.XADR_PRIMARY=1 AND ROWNUM=1) END)                                                                             COUNTRY_ID,
  (SELECT XCOU.XCOU_CUSTSHORTDESC FROM XCOU WHERE XCOU.XCOU_ID=(CASE WHEN (NVL(YRES.YRES_XCMS_ID,0)=0 AND NVL(YRES.YRES_YGRP_ID,0)<>0) THEN (SELECT YGRP.YGRP_XCOU_ID FROM YGRP WHERE YGRP.YGRP_ID=YRES.YRES_YGRP_ID) ELSE (SELECT XADR.XADR_XCOU_ID FROM XADR WHERE XADR.XADR_XCMS_ID=YRES.YRES_XCMS_ID AND XADR.XADR_PRIMARY=1 AND ROWNUM=1) END)) COUNTRY_SHORTDESC,
  (SELECT XCOU.XCOU_LONGDESC FROM XCOU WHERE XCOU.XCOU_ID=(CASE WHEN (NVL(YRES.YRES_XCMS_ID,0)=0 AND NVL(YRES.YRES_YGRP_ID,0)<>0) THEN (SELECT YGRP.YGRP_XCOU_ID FROM YGRP WHERE YGRP.YGRP_ID=YRES.YRES_YGRP_ID) ELSE (SELECT XADR.XADR_XCOU_ID FROM XADR WHERE XADR.XADR_XCMS_ID=YRES.YRES_XCMS_ID AND XADR.XADR_PRIMARY=1 AND ROWNUM=1) END))      COUNTRY_LONGDESC,
  (SELECT SUM(YDEP.YDEP_AMOUNT_RMS) FROM YDEP WHERE YDEP.YDEP_YRES_ID=YRES.YRES_ID)                  DEPOSIT_REQUESTED,
  (SELECT MAX(YDEP.YDEP_DUEDATE_RMS) FROM YDEP WHERE YDEP.YDEP_YRES_ID=YRES.YRES_ID)              DEPOSIT_LIMIT,
  (SELECT NVL((SUM(ZPOS.ZPOS_QUANTITY * ZPOS.ZPOS_UNITPRICE)),0) FROM ZPOS WHERE ZPOS.ZPOS_CDT= 5 AND ZPOS.ZPOS_LEDGERSTATUS=1 AND ZPOS.ZPOS_YRES_ID=YRES.YRES_ID) DEPOSIT_PAID,
  (SELECT MAX(ZPOS.ZPOS_POSTDATE) FROM ZPOS WHERE ZPOS.ZPOS_CDT= 5 AND ZPOS.ZPOS_LEDGERSTATUS=1 AND ZPOS.ZPOS_YRES_ID=YRES.YRES_ID)                                DEPOSIT_POSTDATE,
  (SELECT XCED.XCED_INITIALS FROM WMLG, XCED WHERE WMLG.WMLG_TABLENAME='YRES' AND WMLG.WMLG_XCMS_UPDATE=XCED_ID AND WMLG.WMLG_TABLE_ID=YRES_ID AND ROWNUM=1)                     ENTERED_BY_USER,
  (SELECT DECODE(XCMS_TYPE,1,XCMS_NAME1||' '||XCMS_NAME2||' '||XCMS_NAME3,TRIM(DECODE(NVL(XCMS_NAME3,''),'','',XCMS_NAME3||' ')||DECODE(NVL(XCMS_NAME2,''),'','',XCMS_NAME2||' ')||XCMS_NAME1)) FROM WMLG, XCED, XCMS WHERE XCMS.XCMS_ID=XCED.XCED_ID AND WMLG.WMLG_TABLENAME='YRES' AND WMLG.WMLG_XCMS_UPDATE=XCED_ID AND WMLG.WMLG_TABLE_ID=YRES_ID AND ROWNUM=1)                     ENTERED_BY_USERNAME,
  (SELECT TO_DATE(WMLG.WMLG_TIME_CREATION) FROM WMLG, XCED WHERE WMLG.WMLG_TABLENAME='YRES' AND WMLG.WMLG_XCMS_UPDATE=XCED_ID AND WMLG.WMLG_TABLE_ID=YRES_ID AND ROWNUM=1)                ENTERED_WHEN,
  (SELECT TO_CHAR(WMLG.WMLG_TIME_CREATION,'HH24:MI') FROM WMLG, XCED WHERE WMLG.WMLG_TABLENAME='YRES' AND WMLG.WMLG_XCMS_UPDATE=XCED_ID AND WMLG.WMLG_TABLE_ID=YRES_ID AND ROWNUM=1)                ENTERED_WHEN_TIME,
  (SELECT TRUNC(WMLG.WMLG_TIME_UPDATE) FROM WMLG, XCED WHERE WMLG.WMLG_TABLENAME='YRES' AND WMLG.WMLG_XCMS_UPDATE=XCED_ID AND WMLG.WMLG_TABLE_ID=YRES_ID AND ROWNUM=1)                UPDATED_WHEN_DATE,
  (SELECT XPDC.XPDC_VALUE FROM XPDC, XPDG WHERE XPDC.XPDC_XPDG_ID = XPDG.XPDG_ID AND XPDG.XPDG_ROLE=1 AND XPDC.XPDC_XCMS_ID = YRES.YRES_XCMS_ID AND ROWNUM=1)                    PASSPORT,
  (SELECT XCOU.XCOU_NATIONALITY FROM XCOU, XCMS WHERE XCOU.XCOU_ID = XCMS.XCMS_XCOU_ID_NATION AND XCMS.XCMS_ID = YRES.YRES_XCMS_ID)                                   NATIONALITY,
  (SELECT XCOU.XCOU_ISO2 FROM XCOU, XCMS WHERE XCOU.XCOU_ID = XCMS.XCMS_XCOU_ID_NATION AND XCMS.XCMS_ID = YRES.YRES_XCMS_ID)                                       NATIONALITY_ISO2,
  (SELECT XCOU.XCOU_ISO3 FROM XCOU, XCMS WHERE XCOU.XCOU_ID = XCMS.XCMS_XCOU_ID_NATION AND XCMS.XCMS_ID = YRES.YRES_XCMS_ID)                                       NATIONALITY_ISO3,
  (SELECT COUNT(YRES_ID) FROM YRES NS WHERE NS.YRES_NOAVAILREASON = 4 AND NS.YRES_XCMS_ID = YRES.YRES_XCMS_ID)                                                        NOSHOWS,
  (SELECT COUNT(YRES_ID) FROM YRES NS WHERE NS.YRES_NOAVAILREASON = 3 AND NS.YRES_XCMS_ID = YRES.YRES_XCMS_ID) 							   CANCELLATIONS, 
  YRES_YRFR_ID,
  (select ZDCO_LONGDESC from ZDCO where YRES.YRES_DEFPAYMETH_ZDCO_ID  = zdco_id) Payment_Method,
  V8_Fnc_Ypal_Rep_Infos(YRES.YRES_CURRENT_YDET_ID,1) PACKAGES_SHORT,
  V8_Fnc_Ypal_Rep_Infos(YRES.YRES_CURRENT_YDET_ID,2) PACKAGES_LONG,
  decode(yres_printrate,1,'Y','N') PRINT_RATE,
  V8_Fnc_Yrft_Rep_Infos(YRES.YRES_ID,1,1) YRES_YRFT_SHORT,
  V8_Fnc_Yrft_Rep_Infos(YRES.YRES_ID,1,2) YRES_YRFT_LONG,
  V8_Fnc_Xpda_Rep_Infos((DECODE((SELECT XPAC.XPAC_RESORDET FROM XPAC WHERE XPAC.XPAC_ID=18),0,YRES.YRES_XCMS_ID,1,YRES.YRES_ID,2,YRES.YRES_CURRENT_YDET_ID)),18,1,(SELECT XPAC.XPAC_RESORDET FROM XPAC WHERE XPAC.XPAC_ID=18)) RES_SPECIALS_SHORT,
  V8_Fnc_Xpda_Rep_Infos((DECODE((SELECT XPAC.XPAC_RESORDET FROM XPAC WHERE XPAC.XPAC_ID=18),0,YRES.YRES_XCMS_ID,1,YRES.YRES_ID,2,YRES.YRES_CURRENT_YDET_ID)),18,2,(SELECT XPAC.XPAC_RESORDET FROM XPAC WHERE XPAC.XPAC_ID=18)) RES_SPECIALS_LONG,
  (select WFLT_CODE from V8_REP_TRANSPORTATION where V8_REP_TRANSPORTATION.YRFL_YRES_ID = yres_id and V8_REP_TRANSPORTATION.YRFL_ARRDEP = 0) TRANSPORT_ARR_CODE,
  (select WFLT_CODE from V8_REP_TRANSPORTATION where V8_REP_TRANSPORTATION.YRFL_YRES_ID = yres_id and V8_REP_TRANSPORTATION.YRFL_ARRDEP = 1) TRANSPORT_DEP_CODE,
   YRES.YRES_YPRT_ID YPRT_ID,
  (SELECT YPRT.YPRT_LONGDESC FROM YPRT WHERE YPRT.YPRT_ID=YRES.YRES_YPRT_ID) YPRT_LONGDESC,
  (SELECT YPRT.YPRT_COMMENT FROM YPRT WHERE YPRT.YPRT_ID=YRES.YRES_YPRT_ID) YPRT_COMMENT,
  (SELECT COUNT(yres_2.YRES_ID) FROM YRES yres_2 WHERE yres_2.YRES_YPRT_ID=YRES.YRES_YPRT_ID AND yres_2.YRES_RESSTATUS<>3) TOTAL_PARTY_MEMBERS,
  V8_Fnc_Yprt_Rep_Infos(YRES.YRES_ID,YRES.YRES_YPRT_ID,3) PARTY_MEMBER,
  YRES.YRES_CONFIRMATIONNO YRES_CONFIRMATIONNO
FROM
YRES;

create or replace function local_date_to_gmt(inputdate DATE) return DATE as
  inputd DATE;
begin
  inputd := apply_custom_daylight_saving(inputdate);
  return CAST((FROM_TZ(CAST(inputd AS TIMESTAMP), SESSIONTIMEZONE) AT TIME ZONE 'GMT') AS DATE);
end;
/

create or replace view V8_ED_TA_ICS_WACT_INFOS as
select 
  wact_id,
  (select wmlg.wmlg_time_creation from wmlg where wmlg.wmlg_tablename = 'WACT' and wmlg.wmlg_table_id = wact.wact_id) wmlg_time_creation,
  local_date_to_gmt((select wmlg.wmlg_time_creation from wmlg where wmlg.wmlg_tablename = 'WACT' and wmlg.wmlg_table_id = wact.wact_id)) wmlg_time_creation_GMT,
  (select wmlg.wmlg_time_update from wmlg where wmlg.wmlg_tablename = 'WACT' and wmlg.wmlg_table_id = wact.wact_id) wmlg_time_update,
  local_date_to_gmt((select wmlg.wmlg_time_update from wmlg where wmlg.wmlg_tablename = 'WACT' and wmlg.wmlg_table_id = wact.wact_id)) wmlg_time_update_GMT,
  case when wact.wact_allday = 1 or to_char(wact.wact_starttime, 'HH24') = '00' then trunc(wact.wact_starttime) + 9 *60/1440 else wact.wact_starttime end  wact_starttime,
  local_date_to_gmt(case when wact.wact_allday = 1 or to_char(wact.wact_starttime, 'HH24') = '00' then trunc(wact.wact_starttime) + 9 *60/1440 else wact.wact_starttime end) wact_starttime_GMT,
  wact.wact_endtime,
  local_date_to_gmt(wact.wact_endtime) wact_endtime_GMT
from
  wact;  
/

CREATE OR REPLACE VIEW V8_IFC_RESERVATIONSEARCH
AS 
SELECT
  0 DEST_TYPE,
  (CASE WHEN YRES.YRES_VACANT = 1 or YRES.YRES_ACTARRTIMENULL = 1 or YRES.YRES_ACTDEPTIMENULL = 0 then (select YDET.YDET_YRMS_ID from YDET where YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID and nvl(YDET.YDET_YRMS_ID,0)<>0) else YRES.YRES_YRMS_ID end) DEST_ID,
  YRES.YRES_ID RESERVATIONNUMBER,
  DECODE(YRPL.YRPL_XCMS_ID,YRES.YRES_XCMS_ID,1,0) PRIMARY_GUEST,
  YRPL.YRPL_XCMS_ID GUESTNUMBER,
  (CASE WHEN (NVL(YRES.YRES_XCMS_ID,0)=0 AND NVL(YRES.YRES_YGRP_ID,0)<>0) THEN (SELECT YGRP.YGRP_NAME FROM YGRP WHERE YGRP.YGRP_ID=YRES.YRES_YGRP_ID) ELSE (SELECT V8_REP_NAME.NAME2 FROM V8_REP_NAME WHERE V8_REP_NAME.XCMS_ID=YRPL.YRPL_XCMS_ID) END) FORMATTEDNAME,
  (SELECT XCMS_NAME1 FROM XCMS WHERE XCMS.XCMS_ID=YRPL.YRPL_XCMS_ID) LASTNAME,
  (SELECT UPPER(XCMS_NAME1) FROM XCMS WHERE XCMS.XCMS_ID=YRPL.YRPL_XCMS_ID) UPLASTNAME,
  (SELECT XCMS_NAME3 FROM XCMS WHERE XCMS.XCMS_ID=YRPL.YRPL_XCMS_ID) FIRSTNAME,
  (SELECT XCID_ADDRGREET FROM XCID WHERE XCID.XCID_ID=YRPL.YRPL_XCMS_ID) TITLE,
  (SELECT XCID_TITLE FROM XCID WHERE XCID.XCID_ID=YRPL.YRPL_XCMS_ID) FREETITLE,
  (CASE WHEN YRES.YRES_VACANT = 1 or YRES.YRES_ACTARRTIMENULL = 1 or YRES.YRES_ACTDEPTIMENULL = 0 then (SELECT YRMS.YRMS_SHORTDESC FROM YRMS,YDET WHERE YRMS.YRMS_ID=YDET.YDET_YRMS_ID AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) else (SELECT YRMS.YRMS_SHORTDESC FROM YRMS WHERE YRMS.YRMS_ID=YRES.YRES_YRMS_ID) end) ROOMNUMBER,
  (CASE WHEN YRES.YRES_VACANT = 1 or YRES.YRES_ACTARRTIMENULL = 1 or YRES.YRES_ACTDEPTIMENULL = 0
         then (SELECT YCAT.YCAT_SHORTDESC FROM YCAT,YDET WHERE YCAT.YCAT_ID=YDET.YDET_YCAT_ID AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID)
         else (SELECT YCAT.YCAT_SHORTDESC FROM YCAT,YRMS WHERE YCAT.YCAT_ID=YRMS.YRMS_YCAT_ID AND YRMS.YRMS_ID=YRES.YRES_YRMS_ID) end) ROOMTYPE,
  (CASE WHEN YRES.YRES_VACANT = 1 or YRES.YRES_ACTARRTIMENULL = 1 or YRES.YRES_ACTDEPTIMENULL = 0
         then (SELECT ycat_id FROM YCAT,YDET WHERE YCAT.YCAT_ID=YDET.YDET_YCAT_ID AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID)
         else (SELECT ycat_id FROM YCAT,YRMS WHERE YCAT.YCAT_ID=YRMS.YRMS_YCAT_ID AND YRMS.YRMS_ID=YRES.YRES_YRMS_ID) end) YCATID,
  YRES.YRES_EXPARRTIME ARRIVALDATE,
  YRES.YRES_EXPDEPTIME DEPARTUREDATE,
  YRES.YRES_EXPARRHOUR ARRIVALTIME,
  YRES.YRES_EXPDEPHOUR DEPARTURETIME,
  (SELECT YDET.YDET_SHARENUM FROM YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) SHARENUMBER,
  YRES.YRES_NOPOST NOPOSTFLAG,
  (SELECT YDET.YDET_ADULTNO FROM YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) ADULTS,
  NVL((SELECT SUM(YRGL_NUMBER) FROM YRGL,YDET WHERE YRGL.YRGL_YDET_ID=YDET.YDET_ID AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID ),0) KIDS,
  (SELECT WLAN_SHORTDESC FROM WLAN, XCMS WHERE XCMS.XCMS_WLAN_ID=WLAN.WLAN_ID AND XCMS.XCMS_ID=YRES.YRES_XCMS_ID) LANGUAGE,
  (SELECT YGRP.YGRP_ID FROM YGRP, YDET WHERE YGRP.YGRP_ID=YDET.YDET_YGRP_ID AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) GROUPID,
  (SELECT YGRP.YGRP_NAME FROM YGRP, YDET WHERE YGRP.YGRP_ID=YDET.YDET_YGRP_ID AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) GROUPNAME,
  DECODE(YRES.YRES_VACANT,1,2,DECODE(YRES.YRES_ACTARRTIME,NULL,4,DECODE(YRES.YRES_ACTDEPTIME,NULL,1,0))) GUESTRESSTATUS,
  (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=YRES.YRES_XCMS_ID) VIP,
  (SELECT XCMA.XCMA_SHORTDESC FROM XCMA,YDET WHERE XCMA.XCMA_ID=YDET.YDET_XCMA_ID AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) MARKETCODE,
  YRES.YRES_FREETEXT1 REMARK1,
  YRES.YRES_FREETEXT2 REMARK2,
  (SELECT ZDCO.ZDCO_LONGDESC FROM ZDCO WHERE ZDCO.ZDCO_ID = YRES.YRES_DEFPAYMETH_ZDCO_ID) PAYMETHOD,
  (CASE WHEN YRES.YRES_ID IN (SELECT YMES_YRES_ID FROM YMES WHERE YMES.YMES_RECEIVEDTYPE NOT IN (1,4)) THEN 1 ELSE 0 END) MESSAGES,
  (SELECT YRCH.YRCH_SHORTDESC FROM YRCH,YDET WHERE YRCH.YRCH_ID=YDET.YDET_YRCH_ID AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) RATECODE,
  (SELECT XCMS.XCMS_NAME1||' '||XCMS.XCMS_NAME2||' '||XCMS.XCMS_NAME3 FROM XCMS, YCLN WHERE YCLN.YCLN_XCMS_ID = XCMS.XCMS_ID AND YCLN.YCLN_YDET_ID=YRES.YRES_CURRENT_YDET_ID AND YCLN.YCLN_XCCA_INTERNALCATEGORY=1 AND ROWNUM=1) COMPANY,
  (SELECT XCMS.XCMS_NAME1||' '||XCMS.XCMS_NAME2||' '||XCMS.XCMS_NAME3 FROM XCMS, YCLN WHERE YCLN.YCLN_XCMS_ID = XCMS.XCMS_ID AND YCLN.YCLN_YDET_ID=YRES.YRES_CURRENT_YDET_ID AND YCLN.YCLN_XCCA_INTERNALCATEGORY=2 AND ROWNUM=1) TRAVELAGENTNAME,
  YRES.YRES_CRSSYSTEM CRS_SYSTEM,
  YRES.YRES_CRSRESNR CRS_NUMBER,
  yres.YRES_CREDITLIMIT CREDITLIMIT,
  '' ZTRA_NUMBER
FROM
YRES , YRPL
WHERE
yres.YRES_EXPDEPTIME > sysdate - 100 and
(YRES.YRES_ACTDEPTIMENULL=1 OR TRUNC(YRES.YRES_ACTDEPTIME)=V8_sys_fideliodate)
AND
(YRES_ACTARRTIMENULL=0 OR YRES.YRES_EXPARRTIME < sysdate + 3)
AND
YRES.YRES_RESSTATUS<>3
AND
yres.YRES_ID = yrpl.YRPL_YRES_ID
UNION ALL
select distinct "DEST_TYPE","DEST_ID","RESERVATIONNUMBER","PRIMARY_GUEST","GUESTNUMBER","FORMATTEDNAME","LASTNAME","UPLASTNAME","FIRSTNAME","TITLE","FREETITLE","ROOMNUMBER","ROOMTYPE","YCATID","ARRIVALDATE","DEPARTUREDATE", "ARRIVALTIME","DEPARTURETIME","SHARENUMBER","NOPOSTFLAG","ADULTS","KIDS","LANGUAGE","GROUPID","GROUPNAME","GUESTRESSTATUS","VIP","MARKETCODE","REMARK1","REMARK2","PAYMETHOD","MESSAGES","RATECODE","COMPANY","TRAVELAGENTNAME","CRS_SYSTEM","CRS_NUMBER","CREDITLIMIT","ZTRA_NUMBER" from (
(SELECT /*+ RULE */
1 DEST_TYPE,
ZFAC.ZFAC_ID DEST_ID,
ZFAC.ZFAC_ID RESERVATIONNUMBER,
1 PRIMARY_GUEST,
ZFAC.ZFAC_XCMS_ID GUESTNUMBER,
(SELECT v8_rep_name.NAME2 from v8_rep_name where v8_rep_name.XCMS_ID=ZFAC.ZFAC_XCMS_ID) FORMATTEDNAME,
(SELECT XCMS.XCMS_NAME1 from XCMS where XCMS.XCMS_ID=ZFAC_XCMS_ID) LASTNAME,
(SELECT UPPER(XCMS.XCMS_NAME1) from XCMS where XCMS.XCMS_ID=ZFAC_XCMS_ID) UPLASTNAME,
(SELECT XCMS.XCMS_NAME3 from XCMS where XCMS.XCMS_ID=ZFAC_XCMS_ID) FIRSTNAME,
(SELECT XCID.XCID_ADDRGREET from XCID where XCID.XCID_ID=ZFAC_XCMS_ID) TITLE,
(SELECT XCID.XCID_TITLE from XCID where XCID.XCID_ID=ZFAC_XCMS_ID) FREETITLE,
nvl2((SELECT ZFAN.ZFAN_LONGDESC from ZFAN where ZFAN.ZFAN_ID=ZFAC.ZFAC_ZFAN_ID),(SELECT ZFAN.ZFAN_LONGDESC from ZFAN where ZFAN.ZFAN_ID=ZFAC.ZFAC_ZFAN_ID),ZFAC.ZFAC_AUTONUMBER) ROOMNUMBER,
(SELECT ZFAG.ZFAG_SHORTDESC from ZFAG where ZFAG.ZFAG_ID=ZFAC.ZFAC_ZFAG_ID) ROOMTYPE,
0 ycatid,
ZFAC.ZFAC_EXPVALIDFROM ARRIVALDATE,
ZFAC.ZFAC_EXPVALIDUNTIL DEPARTUREDATE,
ZFAC.ZFAC_EXPVALIDFROM ARRIVALTIME,
ZFAC.ZFAC_EXPVALIDUNTIL DEPARTURETIME,
TO_NUMBER(NULL) SHARENUMBER,
NVL(ZFAC.ZFAC_NOPOST,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=ZFAC.ZFAC_XCMS_ID)) LANGUAGE,
0 GROUPID,
'' GROUPNAME,
DECODE(ZFAC.ZFAC_EXPVALIDFROM,NULL,1,DECODE(ZFAC.ZFAC_ACTVALIDFROM,NULL,4,DECODE(ZFAC.ZFAC_ACTVALIDUNTIL,NULL,1,0))) 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=ZFAC.ZFAC_XCMS_ID AND ROWNUM = 1),'') VIP,
(SELECT XCMA.XCMA_SHORTDESC from XCMA where XCMA.XCMA_ID=ZFAC.ZFAC_XCMA_ID) MARKETCODE,
'' REMARK1,
'' REMARK2,
'' PAYMETHOD,
0 MESSAGES,
'' RATECODE,
'' COMPANY,
'' TRAVELAGENTNAME,
'' CRS_SYSTEM,
'' CRS_NUMBER,
nvl2(ZFAC.ZFAC_ZCRL_ID,ZFAC.ZFAC_CREDITLIMIT,999999) CREDITLIMIT,
ZTRA_NUMBER
 FROM
ZFAC , ZTRA
WHERE
((zfac.ZFAC_ACTVALIDUNTILNULL = 1 AND zfac.ZFAC_ACTVALIDFROMNULL <> 1))
and ZTRA.ZTRA_ID = ZFAC.ZFAC_ZTRA_ID
AND nvl((SELECT ZFAG.ZFAG_TYPE FROM ZFAG WHERE ZFAG.ZFAG_ID=ZFAC.ZFAC_ZFAG_ID),0) not in (1,2,12)
AND decode(zfac.ZFAC_EXPVALIDUNTIL,null,sysdate,ZFAC.ZFAC_EXPVALIDUNTIL) > sysdate - 100)
UNION ALL
(SELECT
1 DEST_TYPE,
ZFAC.ZFAC_ID DEST_ID,
ZFAC.ZFAC_ID RESERVATIONNUMBER,
1 PRIMARY_GUEST,
ZFAC.ZFAC_XCMS_ID GUESTNUMBER,
(SELECT v8_rep_name.NAME2 from v8_rep_name where v8_rep_name.XCMS_ID=ZFAC.ZFAC_XCMS_ID) FORMATTEDNAME,
(SELECT XCMS.XCMS_NAME1 from XCMS where XCMS.XCMS_ID=ZFAC_XCMS_ID) LASTNAME,
(SELECT UPPER(XCMS.XCMS_NAME1) from XCMS where XCMS.XCMS_ID=ZFAC_XCMS_ID) UPLASTNAME,
(SELECT XCMS.XCMS_NAME3 from XCMS where XCMS.XCMS_ID=ZFAC_XCMS_ID) FIRSTNAME,
(SELECT XCID.XCID_ADDRGREET from XCID where XCID.XCID_ID=ZFAC_XCMS_ID) TITLE,
(SELECT XCID.XCID_TITLE from XCID where XCID.XCID_ID=ZFAC_XCMS_ID) FREETITLE,
nvl2((SELECT ZFAN.ZFAN_LONGDESC from ZFAN where ZFAN.ZFAN_ID=ZFAC.ZFAC_ZFAN_ID),(SELECT ZFAN.ZFAN_LONGDESC from ZFAN where ZFAN.ZFAN_ID=ZFAC.ZFAC_ZFAN_ID),ZFAC.ZFAC_AUTONUMBER) ROOMNUMBER,
(SELECT ZFAG.ZFAG_SHORTDESC from ZFAG where ZFAG.ZFAG_ID=ZFAC.ZFAC_ZFAG_ID) ROOMTYPE,
null ycatid,
ZFAC.ZFAC_EXPVALIDFROM ARRIVALDATE,
ZFAC.ZFAC_EXPVALIDUNTIL DEPARTUREDATE,
ZFAC.ZFAC_EXPVALIDFROM ARRIVALTIME,
ZFAC.ZFAC_EXPVALIDUNTIL DEPARTURETIME,
TO_NUMBER(NULL) SHARENUMBER,
NVL(ZFAC.ZFAC_NOPOST,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=ZFAC.ZFAC_XCMS_ID)) LANGUAGE,
0 GROUPID,
'' GROUPNAME,
DECODE(ZFAC.ZFAC_EXPVALIDFROM,NULL,1,DECODE(ZFAC.ZFAC_ACTVALIDFROM,NULL,4,DECODE(ZFAC.ZFAC_ACTVALIDUNTIL,NULL,1,0))) 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=ZFAC.ZFAC_XCMS_ID AND ROWNUM = 1),'') VIP,
(SELECT XCMA.XCMA_SHORTDESC from XCMA where XCMA.XCMA_ID=ZFAC.ZFAC_XCMA_ID) MARKETCODE,
'' REMARK1,
'' REMARK2,
'' PAYMETHOD,
0 MESSAGES,
'' RATECODE,
'' COMPANY,
'' TRAVELAGENTNAME,
'' CRS_SYSTEM,
'' CRS_NUMBER,
nvl2(ZFAC.ZFAC_ZCRL_ID,ZFAC.ZFAC_CREDITLIMIT,999999) CREDITLIMIT,
''  ZTRA_NUMBER
 FROM
ZFAC
WHERE
((zfac.ZFAC_ACTVALIDUNTILNULL = 1 AND zfac.ZFAC_EXPVALIDFROM < sysdate + 3 ))
and zfac_ZTRA_ID is null
AND nvl((SELECT ZFAG.ZFAG_TYPE FROM ZFAG WHERE ZFAG.ZFAG_ID=ZFAC.ZFAC_ZFAG_ID),0) not in (1,2,12)
AND decode(zfac.ZFAC_EXPVALIDUNTIL,null,sysdate,ZFAC.ZFAC_EXPVALIDUNTIL) > sysdate - 100)
);


