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

CREATE OR REPLACE function evaluatemath(formula varchar2) return number
is
  res number;
begin
  res := 0;
  if formula <> ' ' then
    execute immediate 'select '||formula||' from dual' into res;
  end if;
  return res;
end;
/

CREATE OR REPLACE function evaluateVAT(zdcoid number, ondate Date, grossamount number) return varchar2 is
  TYPE refCursor IS REF CURSOR;
  net number;
  totaltax number;
  perc number;
  formula varchar2(2000);
  rcursor refCursor;
  
  cursor deptcodetaxes is
    select ztcd_udf taxformula
    from zdco, zdtl, ztxc, ztcd
    where zdco_id=zdtl_zdco_id and zdtl_ztxc_id=ztxc_id and ztxc_id=ztcd_ztxc_id
          and zdco_id=zdcoid
          and zdtl_fromtime<=ondate and ondate<=zdtl_totime
    order by ztcd_order;      
  rowdeptcodetaxes deptcodetaxes%rowtype;          
begin
  formula := '';
  open deptcodetaxes;
  loop
    fetch deptcodetaxes into rowdeptcodetaxes;
    exit when deptcodetaxes%notfound;
    
    if formula <> ' ' then
      formula := formula||' + ';
    end if;
      
    formula := formula||rowdeptcodetaxes.taxformula; 
    
  end loop;  
  close deptcodetaxes;
  
  totaltax :=0;
  
  if formula <> ' ' then
    formula:=replace(formula, 'x', to_char(grossamount));
    formula:=replace(formula, ';', ',');
    --get the vat        
    select evaluatemath(formula) into totaltax from dual;
  end if;
  return totaltax;
end;
/

CREATE OR REPLACE FUNCTION V8_SYS_TR_GETRESERVATIONSINFO(resid in number, currDate in date, subdelimiter in varchar := '|', delimiter in varchar := ';' ) RETURN VARCHAR2 IS
cursor c is
SELECT rrer.rrer_yres_id,
       ydet.ydet_yrms_id,
       yrms.yrms_shortdesc,
       V8_REP_YRES_INFOS.GUEST_NAME
  FROM rrer,
       ydet,
       yrms,
       v8_rep_yres_infos
 WHERE     rrer.rrer_rres_id = resid
       AND ydet.ydet_yres_id = rrer.rrer_yres_id
       AND ydet.ydet_yrms_id = yrms.yrms_id
       AND V8_REP_YRES_INFOS.YRES_ID = rrer.rrer_yres_id
       AND ydet.ydet_date = currDate;
r c%rowtype;
res VARCHAR2(2000);
BEGIN
   res := ' ';
   for r in c loop
     if res != ' ' then
       res := r.rrer_yres_id || subdelimiter || r.GUEST_NAME || subdelimiter || r.yrms_shortdesc || delimiter || res;
     else
       res := r.rrer_yres_id || subdelimiter || r.GUEST_NAME || subdelimiter || r.yrms_shortdesc;
     end if;
   end loop;
   if res = ' ' then
     res := null;
   end if;  
   RETURN res;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       RAISE;
END V8_SYS_TR_GETRESERVATIONSINFO;
/

CREATE OR REPLACE FUNCTION V8_FNC_ResultsetasStringlist (SqlStatement string, LinkId_or_Null integer, ResultFieldName string) 
RETURN  string  
IS
   TYPE dynamiccur IS REF CURSOR;
   cur dynamiccur;
  
   fieldvalue varchar2 (60);
   result varchar2      (2000);
   
BEGIN
   result :=  null;
   
   if (LinkId_or_null is null) then
     open cur for SqlStatement;
   else
     open cur for SqlStatement using LinkId_or_null;
   end if;
   
   loop
     fetch cur into fieldvalue;
     
     exit when cur%notfound;

     if (result  is not null )  then
         result:=result ||  ', ';
      end if;
      
     result:=result || fieldvalue;               
       
   end loop; 
   
   close cur;

   RETURN result;

   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END V8_FNC_ResultsetasStringlist;
/

CREATE OR REPLACE FUNCTION V8_FNC_YCRN_CRSRESNR(yresid in number) RETURN VARCHAR2 IS
cursor c is 
  select YCRN.YCRN_CRSSYSTEM, YCRN.YCRN_CRSRESNR from ycrn where YCRN.YCRN_YRES_ID=yresid order by YCRN.YCRN_CRSSYSTEM;   
r c%rowtype;
zdc VARCHAR2(2000);
BEGIN
   zdc := '';
   for r in c loop
     if zdc is not null then
    zdc:= zdc || ', ';
  end if;
  zdc:= zdc || r.YCRN_CRSSYSTEM ||' ' || r.YCRN_CRSRESNR ;
   end loop;
   RETURN zdc;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       RAISE;
END V8_FNC_YCRN_CRSRESNR;
/

CREATE OR REPLACE function V8_FNC_CCM_STD_MENU
(
/* LS 28/11/2012
   FC = Format with currency. Currency of main booking. Position of currency specified in global settings. eg 9,999.99 Euro
   F  = Format without currency as specified in global settings. eg. 9,999.99
   All output of function is translated according to provided wlanid, if translation available.
STDMENUINFONR NUMBER:
1   STDMENUNAME: Returns Standard Menu Name.
2   FCRATE: Returns Standard  Menu Rate. If Menu included in package returns "Incl. in package".  If On Consumption returns "on consumption" If no rate amount or amount is 0 returns "".
3   FCTOT_RATE: Returns Total Standard  Menu Rate. If Menu included in package returns "Incl. in package".  If On Consumption returns "on consumption" If no rate amount or amount is 0 returns "".
4   FCORG_RATE: Returns Standard Menu Original Rate.
5   FCTOT_ORG_RATE: Returns Total Standard Menu Original Rate.
6   FCYEMN_COSTPRICE: Returns Standard Menu Cost Price.
7   RATE: Returns Standard  Menu Rate. If Menu included in package returns "Incl. in package".  If On Consumption returns "on consumption" If no rate amount or amount is 0 returns "".
8   FRATE: Returns Standard  Menu Rate. If Menu included in package returns "Incl. in package".  If On Consumption returns "on consumption" If no rate amount or amount is 0 returns "".
9   RATE_NET: Returns Standard Menu Net Rate. If Menu included in package returns "Incl. in package".  If On Consumption returns "on consumption" If no rate amount or amount is 0 returns "".   
10  FRATE_NET: Returns Standard  Menu Net Rate. If Menu included in package returns "Incl. in package".  If On Consumption returns "on consumption" If no rate amount or amount is 0 returns "".
11  FCRATE_NET: Returns Standard  Menu Net Rate. If Menu included in package returns "Incl. in package".  If On Consumption returns "on consumption" If no rate amount or amount is 0 returns "".
12  RATE_VAT: Returns Standard Menu Rate Vat. If Menu included in package or if On Consumption or no rate amount or rate amount is 0 returns "".   
13  FRATE_VAT: Returns Standard  Menu Rate Vat. If Menu included in package or if On Consumption or no rate amount or rate amount is 0 returns "".
14  FCRATE_VAT: Returns Standard  Menu Rate Vat. If Menu included in package or if On Consumption or no rate amount or rate amount is 0 returns "".
15  TOT_RATE: Returns Total Standard Menu Rate. If Menu included in package returns "Incl. in package".  If On Consumption returns "on consumption" If no rate amount or amount is 0 returns "".
16  FTOT_RATE: Returns Total Standard Menu Rate. If Menu included in package returns "Incl. in package".  If On Consumption returns "on consumption" If no rate amount or amount is 0 returns "".
17  TOT_RATE_NET: Returns Total Standard Menu Net Rate. If Menu included in package returns "Incl. in package".  If On Consumption returns "on consumption" If no rate amount or amount is 0 returns "".
18  FTOT_RATE_NET: Returns Total Standard Menu Net Rate. If Menu included in package returns "Incl. in package".  If On Consumption returns "on consumption" If no rate amount or amount is 0 returns "".
19  FCTOT_RATE_NET: Returns Total Standard  Menu Net Rate. If Menu included in package returns "Incl. in package".  If On Consumption returns "on consumption" If no rate amount or amount is 0 returns "".
20  TOT_RATE_VAT: Returns Total Standard Menu Rate Vat. If Menu included in package or if On Consumption or no rate amount or rate amount is 0 returns "".
21  FTOT_RATE_VAT: Returns Total Standard Menu Rate Vat. If Menu included in package or if On Consumption or no rate amount or rate amount is 0 returns "".
22  FCTOT_RATE_VAT: Returns Total Standard Menu Rate Vat. If Menu included in package or if On Consumption or no rate amount or rate amount is 0 returns "".
23  ORG_RATE: Returns Standard Menu Original Rate.
24  FORG_RATE: Returns Standard Menu Original Rate.
25  TOT_ORG_RATE: Returns Total Standard Menu Original Rate.
26  FTOT_ORG_RATE: Returns Total Standard Menu Original Rate.
27  YEMN_COSTPRICE: Returns Standard Menu Cost Price.
28  FYEMN_COSTPRICE: Returns Standard Menu Cost Price.
*/
  YEMNID IN NUMBER,    
  STDMENUINFONR NUMBER,              
  WLANID IN NUMBER:=null                  
)
  return varchar2 as
  STDMENUINFO varchar2(2000);
  
  CURSOR STDMENUINFOCURSOR is
  select V8_FNC_WTRL_TRANSLATE('YEMN', 'YEMN_MENUNAME', YEMN_ID, WLANID,0) STDMENUNAME,
  lpad(rtrim(decode(yemn.YEMN_PKGINCL,1,V8_FNC_WRTR_Translate('incl. in package', WLANID),decode(yemn.YEMN_CHARGEDONCONSUMPTION,1,V8_FNC_WRTR_Translate('on consumption', WLANID), decode(nvl(yemn.YEMN_MENUPRICE,0),0,to_char(null),yemn.YEMN_MENUPRICE)))),17,' ') RATE,
  lpad(rtrim(decode(yemn.YEMN_PKGINCL,1,V8_FNC_WRTR_Translate('incl. in package', WLANID),decode(yemn.YEMN_CHARGEDONCONSUMPTION,1,V8_FNC_WRTR_Translate('on consumption', WLANID), decode(nvl(yemn.YEMN_MENUPRICE,0),0,to_char(null),V8_FNC_NUM_TO_CHAR_FORMATING(yemn.YEMN_MENUPRICE))))),17,' ') FRATE,
  lpad(rtrim(decode(yemn.YEMN_PKGINCL,1,V8_FNC_WRTR_Translate('incl. in package', WLANID),decode(yemn.YEMN_CHARGEDONCONSUMPTION,1,V8_FNC_WRTR_Translate('on consumption', WLANID), decode(nvl(yemn.YEMN_MENUPRICE,0),0,to_char(null),V8_FNC_VALCURR_FORMATING(yemn.YEMN_MENUPRICE, (select zcur.ZCUR_SHORTDESC from yevm,ybom,zcur where yevm.YEVM_ID=yemn.YEMN_YEVM_ID and yevm.YEVM_YBOM_ID=ybom.YBOM_ID and zcur.ZCUR_ID=ybom.YBOM_BASE_ZCUR_ID and rownum=1)))))),17,' ') FCRATE,
  lpad(rtrim(decode(yemn.YEMN_PKGINCL,1,V8_FNC_WRTR_Translate('incl. in package', WLANID),decode(yemn.YEMN_CHARGEDONCONSUMPTION,1,V8_FNC_WRTR_Translate('on consumption', WLANID), decode(nvl(yemn.YEMN_MENUPRICE,0),0,to_char(null),(select sum(ymrd.ymrd_price_net) from ymrd where ymrd_yemn_id=yemn.yemn_id))))),17,' ') RATE_NET,
  lpad(rtrim(decode(yemn.YEMN_PKGINCL,1,V8_FNC_WRTR_Translate('incl. in package', WLANID),decode(yemn.YEMN_CHARGEDONCONSUMPTION,1,V8_FNC_WRTR_Translate('on consumption', WLANID), decode(nvl(yemn.YEMN_MENUPRICE,0),0,to_char(null),V8_FNC_NUM_TO_CHAR_FORMATING((select sum(ymrd.ymrd_price_net) from ymrd where ymrd_yemn_id=yemn.yemn_id)))))),17,' ') FRATE_NET,
  lpad(rtrim(decode(yemn.YEMN_PKGINCL,1,V8_FNC_WRTR_Translate('incl. in package', WLANID),decode(yemn.YEMN_CHARGEDONCONSUMPTION,1,V8_FNC_WRTR_Translate('on consumption', WLANID), decode(nvl(yemn.YEMN_MENUPRICE,0),0,to_char(null),V8_FNC_VALCURR_FORMATING((select sum(ymrd.ymrd_price_net) from ymrd where ymrd_yemn_id=yemn.yemn_id),(select zcur.ZCUR_SHORTDESC from yevm,ybom,zcur where yevm.YEVM_ID=yemn.YEMN_YEVM_ID and yevm.YEVM_YBOM_ID=ybom.YBOM_ID and zcur.ZCUR_ID=ybom.YBOM_BASE_ZCUR_ID and rownum=1)))))),17,' ') FCRATE_NET,
  lpad(rtrim(decode(yemn.YEMN_PKGINCL,1,'',decode(yemn.YEMN_CHARGEDONCONSUMPTION,1,'', decode(nvl(yemn.YEMN_MENUPRICE,0),0,to_char(null),(select sum(ymrd.ymrd_price_vat) from ymrd where ymrd_yemn_id=yemn.yemn_id))))),17,' ') RATE_VAT,
  lpad(rtrim(decode(yemn.YEMN_PKGINCL,1,'',decode(yemn.YEMN_CHARGEDONCONSUMPTION,1,'', decode(nvl(yemn.YEMN_MENUPRICE,0),0,to_char(null),V8_FNC_NUM_TO_CHAR_FORMATING((select sum(ymrd.ymrd_price_vat) from ymrd where ymrd_yemn_id=yemn.yemn_id)))))),17,' ') FRATE_VAT,
  lpad(rtrim(decode(yemn.YEMN_PKGINCL,1,'',decode(yemn.YEMN_CHARGEDONCONSUMPTION,1,'', decode(nvl(yemn.YEMN_MENUPRICE,0),0,to_char(null),V8_FNC_VALCURR_FORMATING((select sum(ymrd.ymrd_price_vat) from ymrd where ymrd_yemn_id=yemn.yemn_id),(select zcur.ZCUR_SHORTDESC from yevm,ybom,zcur where yevm.YEVM_ID=yemn.YEMN_YEVM_ID and yevm.YEVM_YBOM_ID=ybom.YBOM_ID and zcur.ZCUR_ID=ybom.YBOM_BASE_ZCUR_ID and rownum=1)))))),17,' ') FCRATE_VAT,
  lpad(rtrim(decode(yemn.YEMN_PKGINCL,1,V8_FNC_WRTR_Translate('incl. in package', WLANID),decode(yemn.YEMN_CHARGEDONCONSUMPTION,1,V8_FNC_WRTR_Translate('on consumption', WLANID), decode(nvl(yemn.YEMN_MENUPRICE,0),0,to_char(null),yemn.YEMN_QUANTITY*yemn.YEMN_MENUPRICE)))),17,' ') TOT_RATE,
  lpad(rtrim(decode(yemn.YEMN_PKGINCL,1,V8_FNC_WRTR_Translate('incl. in package', WLANID),decode(yemn.YEMN_CHARGEDONCONSUMPTION,1,V8_FNC_WRTR_Translate('on consumption', WLANID), decode(nvl(yemn.YEMN_MENUPRICE,0),0,to_char(null),V8_FNC_NUM_TO_CHAR_FORMATING(yemn.YEMN_QUANTITY*yemn.YEMN_MENUPRICE))))),17,' ') FTOT_RATE,
  lpad(rtrim(decode(yemn.YEMN_PKGINCL,1,V8_FNC_WRTR_Translate('incl. in package', WLANID),decode(yemn.YEMN_CHARGEDONCONSUMPTION,1,V8_FNC_WRTR_Translate('on consumption', WLANID), decode(nvl(yemn.YEMN_MENUPRICE,0),0,to_char(null),V8_FNC_VALCURR_FORMATING(yemn.YEMN_QUANTITY*yemn.YEMN_MENUPRICE, (select zcur.ZCUR_SHORTDESC from yevm,ybom,zcur where yevm.YEVM_ID=yemn.YEMN_YEVM_ID and yevm.YEVM_YBOM_ID=ybom.YBOM_ID and zcur.ZCUR_ID=ybom.YBOM_BASE_ZCUR_ID and rownum=1)))))),17,' ') FCTOT_RATE,
  lpad(rtrim(decode(yemn.YEMN_PKGINCL,1,V8_FNC_WRTR_Translate('incl. in package', WLANID),decode(yemn.YEMN_CHARGEDONCONSUMPTION,1,V8_FNC_WRTR_Translate('on consumption', WLANID), decode(nvl(yemn.YEMN_MENUPRICE,0),0,to_char(null),yemn.yemn_quantity*(select sum(ymrd.ymrd_price_net) from ymrd where ymrd_yemn_id=yemn.yemn_id))))),17,' ') TOT_RATE_NET,
  lpad(rtrim(decode(yemn.YEMN_PKGINCL,1,V8_FNC_WRTR_Translate('incl. in package', WLANID),decode(yemn.YEMN_CHARGEDONCONSUMPTION,1,V8_FNC_WRTR_Translate('on consumption', WLANID), decode(nvl(yemn.YEMN_MENUPRICE,0),0,to_char(null),V8_FNC_NUM_TO_CHAR_FORMATING(yemn.yemn_quantity*(select sum(ymrd.ymrd_price_net) from ymrd where ymrd_yemn_id=yemn.yemn_id)))))),17,' ') FTOT_RATE_NET,
  lpad(rtrim(decode(yemn.YEMN_PKGINCL,1,V8_FNC_WRTR_Translate('incl. in package', WLANID),decode(yemn.YEMN_CHARGEDONCONSUMPTION,1,V8_FNC_WRTR_Translate('on consumption', WLANID), decode(nvl(yemn.YEMN_MENUPRICE,0),0,to_char(null),V8_FNC_VALCURR_FORMATING(yemn.yemn_quantity*(select sum(ymrd.ymrd_price_net) from ymrd where ymrd_yemn_id=yemn.yemn_id),(select zcur.ZCUR_SHORTDESC from yevm,ybom,zcur where yevm.YEVM_ID=yemn.YEMN_YEVM_ID and yevm.YEVM_YBOM_ID=ybom.YBOM_ID and zcur.ZCUR_ID=ybom.YBOM_BASE_ZCUR_ID and rownum=1)))))),17,' ') FCTOT_RATE_NET,
  lpad(rtrim(decode(yemn.YEMN_PKGINCL,1,'',decode(yemn.YEMN_CHARGEDONCONSUMPTION,1,'', decode(nvl(yemn.YEMN_MENUPRICE,0),0,to_char(null),yemn.yemn_quantity*(select sum(ymrd.ymrd_price_vat) from ymrd where ymrd_yemn_id=yemn.yemn_id))))),17,' ') TOT_RATE_VAT,
  lpad(rtrim(decode(yemn.YEMN_PKGINCL,1,'',decode(yemn.YEMN_CHARGEDONCONSUMPTION,1,'', decode(nvl(yemn.YEMN_MENUPRICE,0),0,to_char(null),V8_FNC_NUM_TO_CHAR_FORMATING(yemn.yemn_quantity*(select sum(ymrd.ymrd_price_vat) from ymrd where ymrd_yemn_id=yemn.yemn_id)))))),17,' ') FTOT_RATE_VAT,
  lpad(rtrim(decode(yemn.YEMN_PKGINCL,1,'',decode(yemn.YEMN_CHARGEDONCONSUMPTION,1,'', decode(nvl(yemn.YEMN_MENUPRICE,0),0,to_char(null),V8_FNC_VALCURR_FORMATING(yemn.yemn_quantity*(select sum(ymrd.ymrd_price_vat) from ymrd where ymrd_yemn_id=yemn.yemn_id),(select zcur.ZCUR_SHORTDESC from yevm,ybom,zcur where yevm.YEVM_ID=yemn.YEMN_YEVM_ID and yevm.YEVM_YBOM_ID=ybom.YBOM_ID and zcur.ZCUR_ID=ybom.YBOM_BASE_ZCUR_ID and rownum=1)))))),17,' ') FCTOT_RATE_VAT, 
  (select sum(ymrd_org_amount) from ymrd where ymrd_yemn_id=yemn.yemn_id) ORG_RATE,
  V8_FNC_NUM_TO_CHAR_FORMATING((select sum(ymrd_org_amount) from ymrd where ymrd_yemn_id=yemn.yemn_id)) FORG_RATE,
  V8_FNC_VALCURR_FORMATING( 
    (select sum(ymrd_org_amount) from ymrd where ymrd_yemn_id=yemn.yemn_id), 
    (select zcur.ZCUR_SHORTDESC from yevm,ybom,zcur where yevm.YEVM_ID=yemn.YEMN_YEVM_ID and yevm.YEVM_YBOM_ID=ybom.YBOM_ID and zcur.ZCUR_ID=ybom.YBOM_BASE_ZCUR_ID and rownum=1
     )  ) FCORG_RATE,
    yemn.yemn_quantity*(select sum(ymrd_org_amount) from ymrd where ymrd_yemn_id=yemn.yemn_id) TOT_ORG_RATE,
 V8_FNC_NUM_TO_CHAR_FORMATING( 
    yemn.yemn_quantity*(select sum(ymrd_org_amount) from ymrd where ymrd_yemn_id=yemn.yemn_id)) FTOT_ORG_RATE,
  V8_FNC_VALCURR_FORMATING( 
    yemn.yemn_quantity*(select sum(ymrd_org_amount) from ymrd where ymrd_yemn_id=yemn.yemn_id), 
    (select zcur.ZCUR_SHORTDESC from yevm,ybom,zcur where yevm.YEVM_ID=yemn.YEMN_YEVM_ID and yevm.YEVM_YBOM_ID=ybom.YBOM_ID and zcur.ZCUR_ID=ybom.YBOM_BASE_ZCUR_ID and rownum=1
     )  ) FCTOT_ORG_RATE,
  yemn.yemn_costprice YEMN_COSTPRICE,
  V8_FNC_NUM_TO_CHAR_FORMATING(yemn.yemn_costprice) FYEMN_COSTPRICE,
  V8_FNC_VALCURR_FORMATING(yemn.yemn_costprice, (select zcur.ZCUR_SHORTDESC from yevm,ybom,zcur where yevm.YEVM_ID=yemn.YEMN_YEVM_ID and yevm.YEVM_YBOM_ID=ybom.YBOM_ID and zcur.ZCUR_ID=ybom.YBOM_BASE_ZCUR_ID and rownum=1)) FCYEMN_COSTPRICE   
  from yemn, ysme  
  where yemn_ysme_id=ysme_id and yemn_id=YEMNID;
  
  STDMENUINFOVALUE STDMENUINFOCURSOR%rowtype;
begin
  begin
    open STDMENUINFOCURSOR;
    loop
    fetch STDMENUINFOCURSOR
    into STDMENUINFOVALUE;
    exit when STDMENUINFOCURSOR%notfound;
    
    if STDMENUINFONR = 1 then
      STDMENUINFO := STDMENUINFOVALUE.STDMENUNAME;
    end if;
    if STDMENUINFONR = 2 then
      STDMENUINFO := STDMENUINFOVALUE.FCRATE;
    end if;
    if STDMENUINFONR = 3 then
      STDMENUINFO := STDMENUINFOVALUE.FCTOT_RATE;
    end if;
    if STDMENUINFONR = 4 then
      STDMENUINFO := STDMENUINFOVALUE.FCORG_RATE;
    end if;
    if STDMENUINFONR = 5 then
      STDMENUINFO := STDMENUINFOVALUE.FCTOT_ORG_RATE;
    end if;
    if STDMENUINFONR = 6 then
      STDMENUINFO := STDMENUINFOVALUE.FCYEMN_COSTPRICE;
    end if;
    if STDMENUINFONR = 7 then
      STDMENUINFO := STDMENUINFOVALUE.RATE;
    end if;
    if STDMENUINFONR = 8 then
      STDMENUINFO := STDMENUINFOVALUE.FRATE;
    end if;
        if STDMENUINFONR = 9 then
      STDMENUINFO := STDMENUINFOVALUE.RATE_NET;
    end if;
    if STDMENUINFONR = 10 then
      STDMENUINFO := STDMENUINFOVALUE.FRATE_NET;
    end if;
    if STDMENUINFONR = 11 then
      STDMENUINFO := STDMENUINFOVALUE.FCRATE_NET;
    end if;
    if STDMENUINFONR = 12 then
      STDMENUINFO := STDMENUINFOVALUE.RATE_VAT;
    end if;
    if STDMENUINFONR = 13 then
      STDMENUINFO := STDMENUINFOVALUE.FRATE_VAT;
    end if;
    if STDMENUINFONR = 14 then
      STDMENUINFO := STDMENUINFOVALUE.FCRATE_VAT;
    end if;
    if STDMENUINFONR = 15 then
      STDMENUINFO := STDMENUINFOVALUE.TOT_RATE;
    end if;
    if STDMENUINFONR = 16 then
      STDMENUINFO := STDMENUINFOVALUE.FTOT_RATE;
    end if;
    if STDMENUINFONR = 17 then
      STDMENUINFO := STDMENUINFOVALUE.TOT_RATE_NET;
    end if;
    if STDMENUINFONR = 18 then
      STDMENUINFO := STDMENUINFOVALUE.FTOT_RATE_NET;
    end if;
    if STDMENUINFONR = 19 then
      STDMENUINFO := STDMENUINFOVALUE.FCTOT_RATE_NET;
    end if;
    if STDMENUINFONR = 20 then
      STDMENUINFO := STDMENUINFOVALUE.TOT_RATE_VAT;
    end if;
    if STDMENUINFONR = 21 then
      STDMENUINFO := STDMENUINFOVALUE.FTOT_RATE_VAT;
    end if;
    if STDMENUINFONR = 22 then
      STDMENUINFO := STDMENUINFOVALUE.FCTOT_RATE_VAT;
    end if;
    if STDMENUINFONR = 23 then
      STDMENUINFO := STDMENUINFOVALUE.ORG_RATE;
    end if;
    if STDMENUINFONR = 24 then
      STDMENUINFO := STDMENUINFOVALUE.FORG_RATE;
    end if;
    if STDMENUINFONR = 25 then
      STDMENUINFO := STDMENUINFOVALUE.TOT_ORG_RATE;
    end if;
    if STDMENUINFONR = 26 then
      STDMENUINFO := STDMENUINFOVALUE.FTOT_ORG_RATE;
    end if;
     if STDMENUINFONR = 27 then
      STDMENUINFO := STDMENUINFOVALUE.YEMN_COSTPRICE;
    end if;
    if STDMENUINFONR = 28 then
      STDMENUINFO := STDMENUINFOVALUE.FYEMN_COSTPRICE;
    end if;
    
    end loop;
    exception
      when no_data_found then null;
      when others then null;
    close STDMENUINFOCURSOR;
  end;
  
  return STDMENUINFO;
end V8_FNC_CCM_STD_MENU;
/

CREATE OR REPLACE VIEW v8_rep_ThaiTaxReport
AS
SELECT 
    zttl_id,
    zttl_type,
    decode(zttl_type,'DP','Deposit Payment',decode(zttl_type,'FP','FO Payment',decode(zttl_type,'CN','Credit Note',decode(zttl_type,'NV','Non-Vatable',decode(zttl_type,'CL','City Ledger',decode(zttl_type,'AR','A/R Payment')))))) PaymentType,
    zttl_taxnumber ThaiTaxInvoiceNumber,
    (select V8_REP_NAME.NAME2 from v8_rep_name, zinv where V8_REP_NAME.XCMS_ID=ZINV.ZINV_XCMS_ID and ZINV.ZINV_ID=zttl_zinv_id) GuestName,
    (select ZINV.ZINV_NUMBER from zinv where ZINV.ZINV_ID=zttl_zinv_id) InvoiceNumber,
    (case when (select ZWIN.ZWIN_YRES_ID from zwin, zinv where ZWIN.ZWIN_ID=ZINV.ZINV_ZWIN_ID and ZINV.ZINV_ID=zttl_zinv_id) is not null then
      (select YRMS.YRMS_SHORTDESC from yrms, ydet, yres, zwin, zinv where YRMS.YRMS_ID=YDET.YDET_YRMS_ID and YDET.YDET_YRES_ID=YRES.YRES_ID and YRES.YRES_ID=ZWIN.ZWIN_YRES_ID and ZWIN.ZWIN_ID=ZINV.ZINV_ZWIN_ID and ZINV.ZINV_ID=zttl_zinv_id and YDET.YDET_DATE=zttl_taxinvoicedate and rownum=1) else
      (case when (select ZFAC.ZFAC_ZFAN_ID from zfac, zwin, zinv where ZFAC.ZFAC_ID=ZWIN.ZWIN_ZFAC_ID and ZWIN.ZWIN_ID=ZINV.ZINV_ZWIN_ID and ZINV.ZINV_ID=zttl_zinv_id and rownum=1) is not null then
      (select ZFAN.ZFAN_LONGDESC from zfan, zfac, zwin, zinv where ZFAN.ZFAN_ID=ZFAC.ZFAC_ZFAN_ID and ZFAC.ZFAC_ID=ZWIN.ZWIN_ZFAC_ID and ZWIN.ZWIN_ID=ZINV.ZINV_ZWIN_ID and ZINV.ZINV_ID=zttl_zinv_id and rownum=1)
      else to_char((select ZFAC.ZFAC_AUTONUMBER from zfac, zwin, zinv where ZFAC.ZFAC_ID=ZWIN.ZWIN_ZFAC_ID and ZWIN.ZWIN_ID=ZINV.ZINV_ZWIN_ID and ZINV.ZINV_ID=zttl_zinv_id and rownum=1)) end) end) RoomFA,
    (select V8_REP_NAME.NAME2 from v8_rep_name, zpos where V8_REP_NAME.XCMS_ID=ZPOS.ZPOS_USER_XCMS_ID and ZPOS.ZPOS_ID=zttl_paym_zpos_id) Cashier,
    (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) PaymentDescription,
    zttl_timestamp PaymentTime,
    (select ZINV.ZINV_DATE from zinv where ZINV.ZINV_ID=zttl_zinv_id) InvoiceDate,
    AGGREGATE_FIELD('SELECT ThaiTaxInvoiceNumber FROM v8_rep_ThaiTaxSummary_1, zttx WHERE zttx_cn_zttl_id = Zttl_id AND zttx_zttl_id =' || TO_CHAR(NVL(Zttl_ID, 0)), ',' ) ReferenceNumber,
    (SELECT zpos_zwin_id FROM zpos WHERE zpos_id = zttl_paym_zpos_id) zwin_id
 FROM zttl outer_zttl
 ORDER BY zttl_timestamp asc;
   
create or replace view V8_SYS_OPENBALANCE_PER_WINDOW as
 select
    yres.yres_id,
    yres.departure_today,
    zwin.zwin_id,
    zwin.zwin_window,
    nvl(zwin.zwin_billview,0) billview,
    nvl(ZWIN.ZWIN_VIDEO_CO,0) video_co,
    nvl(min((select XCMS.XCMS_TYPE from xcms where XCMS.XCMS_ID=ZWIN.ZWIN_INVOICE_TO_XCMS_ID and XCMS.XCMS_TYPE=1)),0) COMPANY_PROFILE,
    nvl((select sum(decode(zpos.zpos_cdt,5,(decode(zpos.zpos_paidout,1,0,0)),(zpos.zpos_sumupamount))) from zpos where zpos_zwin_id=zwin.zwin_id and zpos_proforma is null and zpos_noreprint <> 1),0) revenue,
    nvl((select sum(decode(zpos.zpos_cdt,5,(decode(zpos.zpos_paidout,1,-(zpos.zpos_sumupamount),-(zpos.zpos_sumupamount))),0)) from zpos where zpos_zwin_id=zwin.zwin_id and zpos_proforma is null  and zpos_noreprint <> 1),0) payment,
    (nvl((select sum(decode(zpos.zpos_cdt,5,(decode(zpos.zpos_paidout,1,0,0)),(zpos.zpos_sumupamount))) from zpos where zpos_zwin_id=zwin.zwin_id and zpos_proforma is null and zpos_noreprint <> 1),0) + 
    nvl((select sum(decode(zpos.zpos_cdt,5,(decode(zpos.zpos_paidout,1,-(zpos.zpos_sumupamount),-(zpos.zpos_sumupamount))),0)) from zpos where zpos_zwin_id=zwin.zwin_id and zpos_proforma is null and zpos_noreprint <> 1),0)) balance
 from zwin, v8_rep_yres_infos yres
 where
 yres.yres_id = zwin.zwin_yres_id 
 and YRES.CHECKED_IN = 1 
 and yres.checked_out <> 1 
group by yres_id, departure_today, zwin_id, zwin_window, zwin_billview, zwin_video_co
order by yres_id, zwin_window asc;

 CREATE OR REPLACE VIEW V8_REP_PSTAT_STAY_COMP_PMSCCM
AS 
select
1 TYP,
'ALL' TYT_TXT,
       guest.XCMS_ID GUESTID, 
       cmp.XCMS_ID COMPANYID, 
       s.SHIS_YRES_ID, 
       S.SHIS_YBOM_ID,
       s.SHIS_YRMS_SHORTDESC, 
       s.SHIS_BOOKERNAME,
       s.SHIS_XCMA_SHORTDESC,
       s.SHIS_YBLH_SHORTDESC,
       s.SHIS_YCAT_SHORTDESC,
       s.SHIS_YRCH_SHORTDESC,
       sum(s.SHIS_GROSSMISCREV) GROSSMISCBARREV,
       sum(s.SHIS_GROSSTELEREV) GROSSTELEREV,
       sum(s.SHIS_GROSSMINIBARREV) GROSSMINIBARREV,
       sum(s.SHIS_GROSSFBREV) GROSSFBREV,
       sum(s.SHIS_GROSSROOMREV) GROSSROOMREV, 
       sum(s.SHIS_GROSSREV) GROSSREV,       
       s.SHIS_YRES_EXPARRTIME, 
       s.SHIS_YRES_EXPDEPTIME, 
       sum(decode(nvl(shis_dep_id,0),1,0,nvl(shis_ydet_noofrooms,0)))  nights, 
       cmp.xcms_name1 companyName, 
       (select v8_rep_name.NAME2 from v8_rep_name where v8_rep_name.XCMS_ID = guest.XCMS_ID) GuestName,
       decode(nvl(shis_ARR_ID,0),1,1,0) stays
from shis s, xcms cmp, xcms guest
where 
    (cmp.xcms_id in (SHIS_YCLN_1_XCMS_ID, SHIS_YCLN_2_XCMS_ID, SHIS_YCLN_3_XCMS_ID, SHIS_YCLN_4_XCMS_ID, SHIS_YCLN_5_XCMS_ID, SHIS_YCLN_6_XCMS_ID))
     and guest.XCMS_ID = s.shis_xcms_id 
     group by 
     s.SHIS_YRES_ID, s.SHIS_YRMS_SHORTDESC,s.shis_ybom_id, s.shis_arr_id,s.SHIS_YRES_EXPARRTIME, s.SHIS_YRES_EXPDEPTIME, cmp.xcms_name1, guest.xcms_name1 , guest.xcms_name2, guest.xcms_name3,guest.XCMS_ID,cmp.XCMS_ID,       s.SHIS_BOOKERNAME,
       s.SHIS_XCMA_SHORTDESC,
       s.SHIS_YBLH_SHORTDESC,
       s.SHIS_YCAT_SHORTDESC,
       s.SHIS_YRCH_SHORTDESC
union all
select
2 TYP,
'PMS' TYT_TXT,
       guest.XCMS_ID GUESTID, 
       cmp.XCMS_ID COMPANYID, 
       s.SHIS_YRES_ID, 
       S.SHIS_YBOM_ID,
       s.SHIS_YRMS_SHORTDESC, 
       s.SHIS_BOOKERNAME,
       s.SHIS_XCMA_SHORTDESC,
       s.SHIS_YBLH_SHORTDESC,
       s.SHIS_YCAT_SHORTDESC,
       s.SHIS_YRCH_SHORTDESC,
       sum(s.SHIS_GROSSMISCREV) GROSSMISCBARREV,
       sum(s.SHIS_GROSSTELEREV) GROSSTELEREV,
       sum(s.SHIS_GROSSMINIBARREV) GROSSMINIBARREV,
       sum(s.SHIS_GROSSFBREV) GROSSFBREV,
       sum(s.SHIS_GROSSROOMREV) GROSSROOMREV, 
       sum(s.SHIS_GROSSREV) GROSSREV,       
       s.SHIS_YRES_EXPARRTIME, 
       s.SHIS_YRES_EXPDEPTIME, 
       sum(decode(nvl(shis_dep_id,0),1,0,nvl(shis_ydet_noofrooms,0)))  nights, 
       cmp.xcms_name1 companyName, 
       (select v8_rep_name.NAME2 from v8_rep_name where v8_rep_name.XCMS_ID = guest.XCMS_ID) GuestName,
       decode(nvl(shis_ARR_ID,0),1,1,0) stays
from shis s, xcms cmp, xcms guest
where 
    (cmp.xcms_id in (SHIS_YCLN_1_XCMS_ID, SHIS_YCLN_2_XCMS_ID, SHIS_YCLN_3_XCMS_ID, SHIS_YCLN_4_XCMS_ID, SHIS_YCLN_5_XCMS_ID, SHIS_YCLN_6_XCMS_ID))
     and guest.XCMS_ID = s.shis_xcms_id
     and nvl(s.shis_ybom_id,0)=0 
     and S.SHIS_YRES_RESSTATUS<>3
     group by 
     s.SHIS_YRES_ID, s.SHIS_YRMS_SHORTDESC,s.shis_ybom_id, s.shis_arr_id,s.SHIS_YRES_EXPARRTIME, s.SHIS_YRES_EXPDEPTIME, cmp.xcms_name1, guest.xcms_name1 , guest.xcms_name2, guest.xcms_name3,guest.XCMS_ID,cmp.XCMS_ID,       s.SHIS_BOOKERNAME,
       s.SHIS_XCMA_SHORTDESC,
       s.SHIS_YBLH_SHORTDESC,
       s.SHIS_YCAT_SHORTDESC,
       s.SHIS_YRCH_SHORTDESC
union all
select
3 TYP,
'CCM' TYT_TXT,
       guest.XCMS_ID GUESTID, 
       cmp.XCMS_ID COMPANYID, 
       s.SHIS_YRES_ID, 
       S.SHIS_YBOM_ID,
       s.SHIS_YRMS_SHORTDESC, 
       s.SHIS_BOOKERNAME,
       s.SHIS_XCMA_SHORTDESC,
       s.SHIS_YBLH_SHORTDESC,
       s.SHIS_YCAT_SHORTDESC,
       s.SHIS_YRCH_SHORTDESC,
       sum(s.SHIS_GROSSMISCREV) GROSSMISCBARREV,
       sum(s.SHIS_GROSSTELEREV) GROSSTELEREV,
       sum(s.SHIS_GROSSMINIBARREV) GROSSMINIBARREV,
       sum(s.SHIS_GROSSFBREV) GROSSFBREV,
       sum(s.SHIS_GROSSROOMREV) GROSSROOMREV, 
       sum(s.SHIS_GROSSREV) GROSSREV,       
       s.SHIS_YRES_EXPARRTIME, 
       s.SHIS_YRES_EXPDEPTIME, 
       sum(decode(nvl(shis_dep_id,0),1,0,nvl(shis_ydet_noofrooms,0)))  nights, 
       cmp.xcms_name1 companyName, 
       (select v8_rep_name.NAME2 from v8_rep_name where v8_rep_name.XCMS_ID = guest.XCMS_ID) GuestName,
       decode(nvl(shis_ARR_ID,0),1,1,0) stays
from shis s, xcms cmp, xcms guest
where 
    (cmp.xcms_id in (SHIS_YCLN_1_XCMS_ID, SHIS_YCLN_2_XCMS_ID, SHIS_YCLN_3_XCMS_ID, SHIS_YCLN_4_XCMS_ID, SHIS_YCLN_5_XCMS_ID, SHIS_YCLN_6_XCMS_ID))
     and guest.XCMS_ID = s.shis_xcms_id
     and nvl(s.shis_ybom_id,0)<>0 
     group by 
     s.SHIS_YRES_ID, s.SHIS_YRMS_SHORTDESC,s.shis_ybom_id, s.shis_arr_id,s.SHIS_YRES_EXPARRTIME, s.SHIS_YRES_EXPDEPTIME, cmp.xcms_name1, guest.xcms_name1 , guest.xcms_name2, guest.xcms_name3,guest.XCMS_ID,cmp.XCMS_ID,       s.SHIS_BOOKERNAME,
       s.SHIS_XCMA_SHORTDESC,
       s.SHIS_YBLH_SHORTDESC,
       s.SHIS_YCAT_SHORTDESC,
       s.SHIS_YRCH_SHORTDESC;

CREATE OR REPLACE VIEW V8_REP_HSK_INFOS_ALLDAY
AS 
SELECT 
'FREE ROOM' STATUS, 
1 V_STATUS, 
-1 YDET_ID, 
-1 YRES_ID, 
-1 YRES_CALCSTATUS, 
WDAT.WDAT_DATE, 
V8_sys_fideliodate FIDELIO_DATE, 
YRMS.YRMS_ID, 
YRMS.YRMS_YCAT_ID, 
YRMS.YRMS_SHORTDESC, 
YRMS.YRMS_LONGDESC, 
YRMS.YRMS_DISPLORDER, 
YRMS.YRMS_VALIDFROM, 
YRMS.YRMS_VALIDUNTIL, 
YRMS.YRMS_YHKS_ID, 
(SELECT YHKS.YHKS_SHORTDESC FROM YHKS WHERE YHKS.YHKS_ID=YRMS.YRMS_YHKS_ID) YHKS_SHORTDESC, 
(SELECT YHKS.YHKS_LONGDESC FROM YHKS WHERE YHKS.YHKS_ID=YRMS.YRMS_YHKS_ID) YHKS_LONGDESC, 
(SELECT YHKS.YHKS_CLEAN FROM YHKS WHERE YHKS.YHKS_ID=YRMS.YRMS_YHKS_ID) YHKS_CLEAN, 
DECODE(NVL((SELECT YHKS.YHKS_CLEAN FROM YHKS WHERE YHKS.YHKS_ID=YRMS.YRMS_YHKS_ID),0),0,1) IS_DIRTY,
DECODE((SELECT COUNT(YRST_ID) FROM YRST WHERE YRMS.YRMS_ID = YRST.YRST_YRMS_ID AND WDAT.WDAT_DATE >= YRST.YRST_FROMTIME AND WDAT.WDAT_DATE < YRST.YRST_UNTILTIME and YRST.YRST_YRSC_ID=1),1,1,0) IS_OOO,
DECODE((SELECT COUNT(YRST_ID) FROM YRST WHERE YRMS.YRMS_ID = YRST.YRST_YRMS_ID AND WDAT.WDAT_DATE >= YRST.YRST_FROMTIME AND WDAT.WDAT_DATE <= YRST.YRST_UNTILTIME and YRST.YRST_YRSC_ID=1),1,1,0) IS_OOO_HSK,
DECODE((SELECT COUNT(YRST_ID) FROM YRST WHERE YRMS.YRMS_ID = YRST.YRST_YRMS_ID AND WDAT.WDAT_DATE >= YRST.YRST_FROMTIME AND WDAT.WDAT_DATE < trunc(YRST.YRST_UNTILTIME) and YRST.YRST_YRSC_ID=2),1,1,0) IS_OOS,
DECODE((SELECT COUNT(YRST_ID) FROM YRST WHERE YRMS.YRMS_ID = YRST.YRST_YRMS_ID AND WDAT.WDAT_DATE >= YRST.YRST_FROMTIME AND WDAT.WDAT_DATE <= trunc(YRST.YRST_UNTILTIME) and YRST.YRST_YRSC_ID=2),1,1,0) IS_OOS_HSK,
YRMS.YRMS_NOOFPERSONS, 
YRMS.YRMS_FLOOR, 
YRMS.YRMS_HSK_OCCUPIED, 
YRMS.YRMS_MAX_BEDS, 
YRMS.YRMS_FREETEXT, 
YRMS.YRMS_L_STATUS, 
YRMS.YRMS_HSK_NOOFPERSONS, 
YRMS.YRMS_DEFAULTCREDITS, 
(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, 
(SELECT YCAT.YCAT_DISPLORDER FROM YCAT WHERE YCAT.YCAT_ID=YRMS.YRMS_YCAT_ID) YCAT_DISPLORDER, 
(SELECT YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID FROM YCAT WHERE YCAT.YCAT_ID=YRMS.YRMS_YCAT_ID) YCAD_ID,
(SELECT YCAT.YCAT_SUITE FROM YCAT WHERE YCAT.YCAT_ID=YRMS.YRMS_YCAT_ID) YCAT_SUITE,
TO_CHAR(NULL) NAME, 
TO_DATE(NULL) YRES_EXPARRTIME, 
TO_DATE(NULL) YRES_EXPDEPTIME, 
TO_CHAR(NULL) YRES_EXPARRHOUR, 
TO_CHAR(NULL) YRES_EXPDEPHOUR, 
to_number(null) CHECKED_OUT,
TO_NUMBER(NULL) YRES_RESSTATUS, 
TO_CHAR(NULL) YRES_FREETEXT1, 
TO_CHAR(NULL) YRES_FREETEXT2, 
0 YDET_NOOFROOMS, 
0 YDET_ADULTNO, 
0 TOTALKIDS, 
0 TOTALKIDS_G1, 
0 TOTALKIDS_G2, 
0 TOTALKIDS_G3, 
TO_NUMBER(NULL) YBLH_ID, 
TO_CHAR(NULL) YBLH_SHORTDESC, 
TO_NUMBER(NULL) CONF_RATE, 
TO_NUMBER(NULL) CALC_PRICE, 
TO_NUMBER(NULL) YRCH_ID, 
TO_CHAR(NULL) YRCH_SHORTDESC, 
TO_NUMBER(NULL) YRCH_COMPSTATUS, 
TO_CHAR(NULL) ALL_YCLN_NAMES, 
TO_NUMBER(NULL) YDET_XCMA_ID, 
TO_CHAR(NULL) XCMA_SHORTDESC, 
TO_NUMBER(NULL) IS_RES_SPECIALS, 
TO_CHAR(NULL) RES_SPECIALS_SHORT, 
TO_CHAR(NULL) RES_SPECIALS_LONG, 
TO_NUMBER(NULL) IS_XCMS_SPECIAL, 
TO_CHAR(NULL) XCMS_SPECIALS_SHORT, 
TO_CHAR(NULL) XCMS_SPECIALS_LONG, 
TO_CHAR(NULL) PACKAGES_SHORT, 
TO_CHAR(NULL) PACKAGES_LONG, 
TO_CHAR(NULL) ALL_PACKAGES_SHORT, 
TO_CHAR(NULL) ALL_PACKAGES_LONG, 
TO_NUMBER(NULL) IS_YRES_NOTES_NON_VABLE, 
TO_CHAR(NULL) YRES_NOTES_NON_VABLE_SHORT, 
TO_CHAR(NULL) YRES_NOTES_NON_VABLE_LONG, 
TO_NUMBER(NULL) IS_YRES_NOTES_VABLE, 
TO_CHAR(NULL) YRES_NOTES_VABLE_SHORT, 
TO_CHAR(NULL) YRES_NOTES_VABLE_LONG, 
TO_NUMBER(NULL) IS_XCMS_NOTES_NON_VABLE, 
TO_CHAR(NULL) XCMS_NOTES_NON_VABLE_SHORT, 
TO_CHAR(NULL) XCMS_NOTES_NON_VABLE_LONG, 
TO_NUMBER(NULL) IS_XCMS_NOTES_VABLE, 
TO_CHAR(NULL) XCMS_NOTES_VABLE_SHORT, 
TO_CHAR(NULL) XCMS_NOTES_VABLE_LONG, 
TO_CHAR(NULL) SHARER_NAME, 
TO_CHAR(NULL) MULTI_GUEST_NAMES, 
TO_CHAR(NULL) ALL_AMENITIES, 
TO_CHAR(NULL) AMENITIES, 
TO_CHAR(NULL) YDET_AMENITIES_SHORT, 
TO_CHAR(NULL) YDET_AMENITIES_LONG, 
TO_CHAR(NULL) CHILDREN_SHORT, 
TO_CHAR(NULL) CHILDREN_LONG,
TO_CHAR(NULL) VIP_SHORTDESC,
to_date(NULL) ARRIVAL_ROOM,
to_char(null) Inventory_Day,
0 EXP_ROOM_MOVE_TODAY,
0 ROOM_MOVE_DONE_TODAY,
to_char(null) TODAY_ROOM_MOVE_FROM,
to_char(null) TODAY_ROOM_MOVE_TO
FROM 
WDAT,YRMS--,YDET 
WHERE WDAT.WDAT_DATE BETWEEN YRMS.YRMS_VALIDFROM AND YRMS.YRMS_VALIDUNTIL 
--AND WDAT.WDAT_DATE BETWEEN V8_sys_fideliodate AND V8_sys_fideliodate 
AND (NOT EXISTS (SELECT 1 FROM YDET YDET_S1,YRES WHERE YDET_S1.YDET_YRES_ID=YRES.YRES_ID AND YRES.YRES_RESSTATUS<>3 AND YRES.YRES_CALCSTATUS IN (0,1,2,3,4,5,6,7,8,9) AND (decode(nvl(YRES.YRES_YRMS_ID,0),0,(select YDET_S2.YDET_YRMS_ID from ydet YDET_S2 where YDET_S2.YDET_ID=YRES.YRES_CURRENT_YDET_ID),YRES.YRES_YRMS_ID))=YRMS.YRMS_ID AND YDET_S1.YDET_DATE=WDAT.WDAT_DATE)
and not exists (select 1 from Ydet,yres where yres.yres_expdeptime=ydet.ydet_date+1 and ydet.ydet_date=wdat.wdat_date and (decode(nvl(YRES.YRES_YRMS_ID,0),0,(select YDET_S2.YDET_YRMS_ID from ydet YDET_S2 where YDET_S2.YDET_ID=YRES.YRES_CURRENT_YDET_ID),YRES.YRES_YRMS_ID))=yrms.yrms_id and yres.yres_calcstatus in (0,1,2,3,4,5,6,7,8,9))
and not exists (select 1 from yres,ydet where yres.yres_expdeptime=wdat.wdat_date and ydet.ydet_date+1=yres.yres_expdeptime and yres.yres_resstatus<>3 and (decode(nvl(YRES.YRES_YRMS_ID,0),0,(select YDET_S2.YDET_YRMS_ID from ydet YDET_S2 where YDET_S2.YDET_ID=YRES.YRES_CURRENT_YDET_ID),YRES.YRES_YRMS_ID))=yrms.yrms_id and ydet.ydet_yres_id=yres.yres_id and yres.yres_calcstatus in (0,1,2,3,4,5,6,7,8,9)))
-- Row for NoShow Rooms
OR EXISTS (SELECT 1 FROM YDET YDET_S1,YRES WHERE YDET_S1.YDET_YRES_ID=YRES.YRES_ID AND YRES.YRES_RESSTATUS=3 AND YRES.YRES_CALCSTATUS =13 AND (decode(nvl(YRES.YRES_YRMS_ID,0),0,(select YDET_S2.YDET_YRMS_ID from ydet YDET_S2 where YDET_S2.YDET_ID=YRES.YRES_CURRENT_YDET_ID),YRES.YRES_YRMS_ID))=YRMS.YRMS_ID AND YDET_S1.YDET_DATE=WDAT.WDAT_DATE)
UNION ALL 
SELECT 
'OCC ROOM' STATUS, 
2 V_STATUS, 
YDET.YDET_ID, 
YDET.YDET_YRES_ID YRES_ID, 
RES.YRES_CALCSTATUS YRES_CALCSTATUS, 
WDAT.WDAT_DATE, 
V8_sys_fideliodate FIDELIO_DATE, 
YRMS.YRMS_ID, 
YRMS.YRMS_YCAT_ID, 
YRMS.YRMS_SHORTDESC, 
YRMS.YRMS_LONGDESC, 
YRMS.YRMS_DISPLORDER, 
YRMS.YRMS_VALIDFROM, 
YRMS.YRMS_VALIDUNTIL, 
YRMS.YRMS_YHKS_ID, 
(SELECT YHKS.YHKS_SHORTDESC FROM YHKS WHERE YHKS.YHKS_ID=YRMS.YRMS_YHKS_ID) YHKS_SHORTDESC, 
(SELECT YHKS.YHKS_LONGDESC FROM YHKS WHERE YHKS.YHKS_ID=YRMS.YRMS_YHKS_ID) YHKS_LONGDESC, 
(SELECT YHKS.YHKS_CLEAN FROM YHKS WHERE YHKS.YHKS_ID=YRMS.YRMS_YHKS_ID) YHKS_CLEAN, 
DECODE(NVL((SELECT YHKS.YHKS_CLEAN FROM YHKS WHERE YHKS.YHKS_ID=YRMS.YRMS_YHKS_ID),0),0,1) IS_DIRTY,
DECODE((SELECT COUNT(YRST_ID) FROM YRST WHERE YRMS.YRMS_ID = YRST.YRST_YRMS_ID AND WDAT.WDAT_DATE >= YRST.YRST_FROMTIME AND WDAT.WDAT_DATE < YRST.YRST_UNTILTIME and YRST.YRST_YRSC_ID=1),1,1,0) IS_OOO,
DECODE((SELECT COUNT(YRST_ID) FROM YRST WHERE YRMS.YRMS_ID = YRST.YRST_YRMS_ID AND WDAT.WDAT_DATE >= YRST.YRST_FROMTIME AND WDAT.WDAT_DATE <= YRST.YRST_UNTILTIME and YRST.YRST_YRSC_ID=1),1,1,0) IS_OOO_HSK,
DECODE((SELECT COUNT(YRST_ID) FROM YRST WHERE YRMS.YRMS_ID = YRST.YRST_YRMS_ID AND WDAT.WDAT_DATE >= YRST.YRST_FROMTIME AND WDAT.WDAT_DATE < trunc(YRST.YRST_UNTILTIME) and YRST.YRST_YRSC_ID=2),1,1,0) IS_OOS,
DECODE((SELECT COUNT(YRST_ID) FROM YRST WHERE YRMS.YRMS_ID = YRST.YRST_YRMS_ID AND WDAT.WDAT_DATE >= YRST.YRST_FROMTIME AND WDAT.WDAT_DATE <= trunc(YRST.YRST_UNTILTIME) and YRST.YRST_YRSC_ID=2),1,1,0) IS_OOS_HSK,
YRMS.YRMS_NOOFPERSONS, 
YRMS.YRMS_FLOOR, 
YRMS.YRMS_HSK_OCCUPIED, 
YRMS.YRMS_MAX_BEDS, 
YRMS.YRMS_FREETEXT, 
YRMS.YRMS_L_STATUS, 
YRMS.YRMS_HSK_NOOFPERSONS, 
YRMS.YRMS_DEFAULTCREDITS, 
(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, 
(SELECT YCAT.YCAT_DISPLORDER FROM YCAT WHERE YCAT.YCAT_ID=YRMS.YRMS_YCAT_ID) YCAT_DISPLORDER, 
(SELECT YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID FROM YCAT WHERE YCAT.YCAT_ID=YRMS.YRMS_YCAT_ID) YCAD_ID,
(SELECT YCAT.YCAT_SUITE FROM YCAT WHERE YCAT.YCAT_ID=YRMS.YRMS_YCAT_ID) YCAT_SUITE,
RES.NAME, 
RES.YRES_EXPARRTIME, 
RES.YRES_EXPDEPTIME, 
RES.YRES_EXPARRHOUR, 
RES.YRES_EXPDEPHOUR, 
nvl2(res.yres_actdeptime,1,0) CHECKED_OUT,
RES.YRES_RESSTATUS, 
RES.YRES_FREETEXT1, 
RES.YRES_FREETEXT2, 
RES.YDET_NOOFROOMS, 
RES.YDET_ADULTNO, 
RES.TOTALKIDS, 
RES.TOTALKIDS_G1, 
RES.TOTALKIDS_G2, 
RES.TOTALKIDS_G3, 
RES.YBLH_ID, 
RES.YBLH_SHORTDESC, 
RES.CONF_RATE, 
RES.CALC_PRICE, 
RES.YRCH_ID, 
RES.YRCH_SHORTDESC, 
RES.YRCH_COMPSTATUS, 
RES.ALL_YCLN_NAMES, 
RES.YDET_XCMA_ID, 
RES.XCMA_SHORTDESC, 
RES.IS_RES_SPECIALS, 
RES.RES_SPECIALS_SHORT, 
RES.RES_SPECIALS_LONG, 
RES.IS_XCMS_SPECIAL, 
RES.XCMS_SPECIALS_SHORT, 
RES.XCMS_SPECIALS_LONG, 
RES.PACKAGES_SHORT, 
RES.PACKAGES_LONG, 
RES.ALL_PACKAGES_SHORT, 
RES.ALL_PACKAGES_LONG, 
RES.IS_YRES_NOTES_NON_VABLE, 
RES.YRES_NOTES_NON_VABLE_SHORT, 
RES.YRES_NOTES_NON_VABLE_LONG, 
RES.IS_YRES_NOTES_VABLE, 
RES.YRES_NOTES_VABLE_SHORT, 
RES.YRES_NOTES_VABLE_LONG, 
RES.IS_XCMS_NOTES_NON_VABLE, 
RES.XCMS_NOTES_NON_VABLE_SHORT, 
RES.XCMS_NOTES_NON_VABLE_LONG, 
RES.IS_XCMS_NOTES_VABLE, 
RES.XCMS_NOTES_VABLE_SHORT, 
RES.XCMS_NOTES_VABLE_LONG, 
RES.SHARER_NAME, 
RES.MULTI_GUEST_NAMES, 
RES.ALL_AMENITIES, 
RES.AMENITIES, 
RES.YDET_AMENITIES_SHORT, 
RES.YDET_AMENITIES_LONG, 
RES.CHILDREN_SHORT, 
RES.CHILDREN_LONG,
RES.VIP_SHORTDESC ,
(select min(ydet_s.ydet_date) from ydet ydet_s where ydet_s.ydet_yres_id=Res.yres_id and ydet_s.ydet_yrms_id=yrms.yrms_id) ARRIVAL_ROOM,
ydet_inventory(ydet.ydet_id,1) inventory_day,
RES.EXP_ROOM_MOVE_TODAY EXP_ROOM_MOVE_TODAY,
RES.ROOM_MOVE_DONE_TODAY ROOM_MOVE_DONE_TODAY,
RES.TODAY_ROOM_MOVE_FROM TODAY_ROOM_MOVE_FROM,
RES.TODAY_ROOM_MOVE_TO TODAY_ROOM_MOVE_TO
FROM 
WDAT,YRMS,YDET,V8_REP_YRES_INFOS RES 
WHERE WDAT.WDAT_DATE BETWEEN YRMS.YRMS_VALIDFROM AND YRMS.YRMS_VALIDUNTIL 
AND WDAT.WDAT_DATE=YDET.YDET_DATE 
AND YDET.YDET_YRES_ID=RES.YRES_ID 
--AND WDAT.WDAT_DATE BETWEEN V8_sys_fideliodate AND V8_sys_fideliodate 
AND YRMS.YRMS_ID=RES.YDET_YRMS_ID 
and ydet.ydet_date<>RES.YRES_EXPARRTIME
AND (SELECT YRES.YRES_RESSTATUS FROM YRES WHERE YRES.YRES_ID=YDET.YDET_YRES_ID)<>3 
and EXISTS (SELECT 1 FROM YDET YDET_2,YRES WHERE YDET_2.YDET_YRES_ID=YRES.YRES_ID AND YRES.YRES_RESSTATUS<>3 AND YRES.YRES_CALCSTATUS IN (0,1,2,3) AND YDET_2.YDET_ID=YDET.YDET_ID)
UNION ALL 
SELECT 
'DEP EXP' STATUS, 
4 V_STATUS, 
YDET.YDET_ID, 
YDET.YDET_YRES_ID YRES_ID, 
RES.YRES_CALCSTATUS YRES_CALCSTATUS, 
WDAT.WDAT_DATE, 
V8_sys_fideliodate FIDELIO_DATE, 
YRMS.YRMS_ID, 
YRMS.YRMS_YCAT_ID,
YRMS.YRMS_SHORTDESC, 
YRMS.YRMS_LONGDESC, 
YRMS.YRMS_DISPLORDER, 
YRMS.YRMS_VALIDFROM, 
YRMS.YRMS_VALIDUNTIL, 
YRMS.YRMS_YHKS_ID, 
(SELECT YHKS.YHKS_SHORTDESC FROM YHKS WHERE YHKS.YHKS_ID=YRMS.YRMS_YHKS_ID) YHKS_SHORTDESC, 
(SELECT YHKS.YHKS_LONGDESC FROM YHKS WHERE YHKS.YHKS_ID=YRMS.YRMS_YHKS_ID) YHKS_LONGDESC, 
(SELECT YHKS.YHKS_CLEAN FROM YHKS WHERE YHKS.YHKS_ID=YRMS.YRMS_YHKS_ID) YHKS_CLEAN, 
DECODE(NVL((SELECT YHKS.YHKS_CLEAN FROM YHKS WHERE YHKS.YHKS_ID=YRMS.YRMS_YHKS_ID),0),0,1) IS_DIRTY, 
DECODE((SELECT COUNT(YRST_ID) FROM YRST WHERE YRMS.YRMS_ID = YRST.YRST_YRMS_ID AND WDAT.WDAT_DATE >= YRST.YRST_FROMTIME AND WDAT.WDAT_DATE < YRST.YRST_UNTILTIME and YRST.YRST_YRSC_ID=1),1,1,0) IS_OOO,
DECODE((SELECT COUNT(YRST_ID) FROM YRST WHERE YRMS.YRMS_ID = YRST.YRST_YRMS_ID AND WDAT.WDAT_DATE >= YRST.YRST_FROMTIME AND WDAT.WDAT_DATE <= YRST.YRST_UNTILTIME and YRST.YRST_YRSC_ID=1),1,1,0) IS_OOO_HSK,
DECODE((SELECT COUNT(YRST_ID) FROM YRST WHERE YRMS.YRMS_ID = YRST.YRST_YRMS_ID AND WDAT.WDAT_DATE >= YRST.YRST_FROMTIME AND WDAT.WDAT_DATE < trunc(YRST.YRST_UNTILTIME) and YRST.YRST_YRSC_ID=2),1,1,0) IS_OOS,
DECODE((SELECT COUNT(YRST_ID) FROM YRST WHERE YRMS.YRMS_ID = YRST.YRST_YRMS_ID AND WDAT.WDAT_DATE >= YRST.YRST_FROMTIME AND WDAT.WDAT_DATE <=trunc(YRST.YRST_UNTILTIME) and YRST.YRST_YRSC_ID=2),1,1,0) IS_OOS_HSK,
YRMS.YRMS_NOOFPERSONS, 
YRMS.YRMS_FLOOR, 
YRMS.YRMS_HSK_OCCUPIED, 
YRMS.YRMS_MAX_BEDS, 
YRMS.YRMS_FREETEXT, 
YRMS.YRMS_L_STATUS, 
YRMS.YRMS_HSK_NOOFPERSONS, 
YRMS.YRMS_DEFAULTCREDITS, 
(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, 
(SELECT YCAT.YCAT_DISPLORDER FROM YCAT WHERE YCAT.YCAT_ID=YRMS.YRMS_YCAT_ID) YCAT_DISPLORDER, 
(SELECT YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID FROM YCAT WHERE YCAT.YCAT_ID=YRMS.YRMS_YCAT_ID) YCAD_ID,
(SELECT YCAT.YCAT_SUITE FROM YCAT WHERE YCAT.YCAT_ID=YRMS.YRMS_YCAT_ID) YCAT_SUITE,
RES.NAME, 
RES.YRES_EXPARRTIME, 
RES.YRES_EXPDEPTIME, 
RES.YRES_EXPARRHOUR, 
RES.YRES_EXPDEPHOUR, 
nvl2(res.yres_actdeptime,1,0) CHECKED_OUT,
RES.YRES_RESSTATUS, 
RES.YRES_FREETEXT1, 
RES.YRES_FREETEXT2, 
RES.YDET_NOOFROOMS, 
RES.YDET_ADULTNO, 
RES.TOTALKIDS, 
RES.TOTALKIDS_G1, 
RES.TOTALKIDS_G2, 
RES.TOTALKIDS_G3, 
RES.YBLH_ID, 
RES.YBLH_SHORTDESC, 
RES.CONF_RATE, 
RES.CALC_PRICE, 
RES.YRCH_ID, 
RES.YRCH_SHORTDESC, 
RES.YRCH_COMPSTATUS, 
RES.ALL_YCLN_NAMES, 
RES.YDET_XCMA_ID, 
RES.XCMA_SHORTDESC, 
RES.IS_RES_SPECIALS, 
RES.RES_SPECIALS_SHORT, 
RES.RES_SPECIALS_LONG, 
RES.IS_XCMS_SPECIAL, 
RES.XCMS_SPECIALS_SHORT, 
RES.XCMS_SPECIALS_LONG, 
RES.PACKAGES_SHORT, 
RES.PACKAGES_LONG, 
RES.ALL_PACKAGES_SHORT, 
RES.ALL_PACKAGES_LONG, 
RES.IS_YRES_NOTES_NON_VABLE, 
RES.YRES_NOTES_NON_VABLE_SHORT, 
RES.YRES_NOTES_NON_VABLE_LONG, 
RES.IS_YRES_NOTES_VABLE, 
RES.YRES_NOTES_VABLE_SHORT, 
RES.YRES_NOTES_VABLE_LONG, 
RES.IS_XCMS_NOTES_NON_VABLE, 
RES.XCMS_NOTES_NON_VABLE_SHORT, 
RES.XCMS_NOTES_NON_VABLE_LONG, 
RES.IS_XCMS_NOTES_VABLE, 
RES.XCMS_NOTES_VABLE_SHORT, 
RES.XCMS_NOTES_VABLE_LONG, 
RES.SHARER_NAME, 
RES.MULTI_GUEST_NAMES, 
RES.ALL_AMENITIES, 
RES.AMENITIES, 
RES.YDET_AMENITIES_SHORT, 
RES.YDET_AMENITIES_LONG, 
RES.CHILDREN_SHORT, 
RES.CHILDREN_LONG,
RES.VIP_SHORTDESC ,
(select min(ydet_s.ydet_date) from ydet ydet_s where ydet_s.ydet_yres_id=Res.yres_id and ydet_s.ydet_yrms_id=yrms.yrms_id) ARRIVAL_ROOM,
ydet_inventory(ydet.ydet_id,1) inventory_day,
RES.EXP_ROOM_MOVE_TODAY EXP_ROOM_MOVE_TODAY,
RES.ROOM_MOVE_DONE_TODAY ROOM_MOVE_DONE_TODAY,
RES.TODAY_ROOM_MOVE_FROM TODAY_ROOM_MOVE_FROM,
RES.TODAY_ROOM_MOVE_TO TODAY_ROOM_MOVE_TO
FROM 
WDAT,YRMS,YDET,V8_REP_YRES_INFOS RES 
WHERE WDAT.WDAT_DATE BETWEEN YRMS.YRMS_VALIDFROM AND YRMS.YRMS_VALIDUNTIL 
AND WDAT.WDAT_DATE=YDET.YDET_DATE+1 
AND YDET.YDET_YRES_ID=RES.YRES_ID 
AND (RES.YRES_EXPDEPTIME)=WDAT.WDAT_DATE 
--AND WDAT.WDAT_DATE BETWEEN V8_sys_fideliodate AND V8_sys_fideliodate 
AND YRMS.YRMS_ID=RES.YDET_YRMS_ID 
AND (SELECT YRES.YRES_RESSTATUS FROM YRES WHERE YRES.YRES_ID=YDET.YDET_YRES_ID)<>3 
AND EXISTS (SELECT 1 FROM YDET YDET_2,YRES WHERE YDET_2.YDET_YRES_ID=YRES.YRES_ID AND YRES.YRES_RESSTATUS<>3 AND YRES.YRES_CALCSTATUS IN (0,1,2,3,4,5,6) AND YDET_2.YDET_ID=YDET.YDET_ID) 
UNION ALL
SELECT 
'ARR EXP' STATUS, 
5 V_STATUS, 
YDET.YDET_ID, 
YDET.YDET_YRES_ID YRES_ID, 
RES.YRES_CALCSTATUS YRES_CALCSTATUS, 
WDAT.WDAT_DATE, 
V8_sys_fideliodate FIDELIO_DATE, 
YRMS.YRMS_ID, 
YRMS.YRMS_YCAT_ID, 
YRMS.YRMS_SHORTDESC, 
YRMS.YRMS_LONGDESC, 
YRMS.YRMS_DISPLORDER, 
YRMS.YRMS_VALIDFROM, 
YRMS.YRMS_VALIDUNTIL, 
YRMS.YRMS_YHKS_ID, 
(SELECT YHKS.YHKS_SHORTDESC FROM YHKS WHERE YHKS.YHKS_ID=YRMS.YRMS_YHKS_ID) YHKS_SHORTDESC, 
(SELECT YHKS.YHKS_LONGDESC FROM YHKS WHERE YHKS.YHKS_ID=YRMS.YRMS_YHKS_ID) YHKS_LONGDESC, 
(SELECT YHKS.YHKS_CLEAN FROM YHKS WHERE YHKS.YHKS_ID=YRMS.YRMS_YHKS_ID) YHKS_CLEAN, 
DECODE(NVL((SELECT YHKS.YHKS_CLEAN FROM YHKS WHERE YHKS.YHKS_ID=YRMS.YRMS_YHKS_ID),0),0,1) IS_DIRTY, 
DECODE((SELECT COUNT(YRST_ID) FROM YRST WHERE YRMS.YRMS_ID = YRST.YRST_YRMS_ID AND WDAT.WDAT_DATE >= YRST.YRST_FROMTIME AND WDAT.WDAT_DATE < YRST.YRST_UNTILTIME and YRST.YRST_YRSC_ID=1),1,1,0) IS_OOO,
DECODE((SELECT COUNT(YRST_ID) FROM YRST WHERE YRMS.YRMS_ID = YRST.YRST_YRMS_ID AND WDAT.WDAT_DATE >= YRST.YRST_FROMTIME AND WDAT.WDAT_DATE <= YRST.YRST_UNTILTIME and YRST.YRST_YRSC_ID=1),1,1,0) IS_OOO_HSK,
DECODE((SELECT COUNT(YRST_ID) FROM YRST WHERE YRMS.YRMS_ID = YRST.YRST_YRMS_ID AND WDAT.WDAT_DATE >= YRST.YRST_FROMTIME AND WDAT.WDAT_DATE < trunc(YRST.YRST_UNTILTIME) and YRST.YRST_YRSC_ID=2),1,1,0) IS_OOS,
DECODE((SELECT COUNT(YRST_ID) FROM YRST WHERE YRMS.YRMS_ID = YRST.YRST_YRMS_ID AND WDAT.WDAT_DATE >= YRST.YRST_FROMTIME AND WDAT.WDAT_DATE <=trunc(YRST.YRST_UNTILTIME) and YRST.YRST_YRSC_ID=2),1,1,0) IS_OOS_HSK,
YRMS.YRMS_NOOFPERSONS, 
YRMS.YRMS_FLOOR, 
YRMS.YRMS_HSK_OCCUPIED, 
YRMS.YRMS_MAX_BEDS, 
YRMS.YRMS_FREETEXT, 
YRMS.YRMS_L_STATUS, 
YRMS.YRMS_HSK_NOOFPERSONS, 
YRMS.YRMS_DEFAULTCREDITS, 
(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, 
(SELECT YCAT.YCAT_DISPLORDER FROM YCAT WHERE YCAT.YCAT_ID=YRMS.YRMS_YCAT_ID) YCAT_DISPLORDER, 
(SELECT YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID FROM YCAT WHERE YCAT.YCAT_ID=YRMS.YRMS_YCAT_ID) YCAD_ID,
(SELECT YCAT.YCAT_SUITE FROM YCAT WHERE YCAT.YCAT_ID=YRMS.YRMS_YCAT_ID) YCAT_SUITE,
RES.NAME, 
RES.YRES_EXPARRTIME, 
RES.YRES_EXPDEPTIME, 
RES.YRES_EXPARRHOUR, 
RES.YRES_EXPDEPHOUR, 
nvl2(res.yres_actdeptime,1,0) CHECKED_OUT,
RES.YRES_RESSTATUS, 
RES.YRES_FREETEXT1, 
RES.YRES_FREETEXT2, 
RES.YDET_NOOFROOMS, 
RES.YDET_ADULTNO, 
RES.TOTALKIDS, 
RES.TOTALKIDS_G1, 
RES.TOTALKIDS_G2, 
RES.TOTALKIDS_G3, 
RES.YBLH_ID, 
RES.YBLH_SHORTDESC, 
RES.CONF_RATE, 
RES.CALC_PRICE, 
RES.YRCH_ID, 
RES.YRCH_SHORTDESC, 
RES.YRCH_COMPSTATUS, 
RES.ALL_YCLN_NAMES, 
RES.YDET_XCMA_ID, 
RES.XCMA_SHORTDESC, 
RES.IS_RES_SPECIALS, 
RES.RES_SPECIALS_SHORT, 
RES.RES_SPECIALS_LONG, 
RES.IS_XCMS_SPECIAL, 
RES.XCMS_SPECIALS_SHORT, 
RES.XCMS_SPECIALS_LONG, 
RES.PACKAGES_SHORT, 
RES.PACKAGES_LONG, 
RES.ALL_PACKAGES_SHORT, 
RES.ALL_PACKAGES_LONG, 
RES.IS_YRES_NOTES_NON_VABLE, 
RES.YRES_NOTES_NON_VABLE_SHORT, 
RES.YRES_NOTES_NON_VABLE_LONG, 
RES.IS_YRES_NOTES_VABLE, 
RES.YRES_NOTES_VABLE_SHORT, 
RES.YRES_NOTES_VABLE_LONG, 
RES.IS_XCMS_NOTES_NON_VABLE, 
RES.XCMS_NOTES_NON_VABLE_SHORT, 
RES.XCMS_NOTES_NON_VABLE_LONG, 
RES.IS_XCMS_NOTES_VABLE, 
RES.XCMS_NOTES_VABLE_SHORT, 
RES.XCMS_NOTES_VABLE_LONG, 
RES.SHARER_NAME, 
RES.MULTI_GUEST_NAMES, 
RES.ALL_AMENITIES, 
RES.AMENITIES, 
RES.YDET_AMENITIES_SHORT, 
RES.YDET_AMENITIES_LONG, 
RES.CHILDREN_SHORT, 
RES.CHILDREN_LONG,
RES.VIP_SHORTDESC ,
(select min(ydet_s.ydet_date) from ydet ydet_s where ydet_s.ydet_yres_id=Res.yres_id and ydet_s.ydet_yrms_id=yrms.yrms_id) ARRIVAL_ROOM,
ydet_inventory(ydet.ydet_id,1) inventory_day,
RES.EXP_ROOM_MOVE_TODAY EXP_ROOM_MOVE_TODAY,
RES.ROOM_MOVE_DONE_TODAY ROOM_MOVE_DONE_TODAY,
RES.TODAY_ROOM_MOVE_FROM TODAY_ROOM_MOVE_FROM,
RES.TODAY_ROOM_MOVE_TO TODAY_ROOM_MOVE_TO
FROM 
WDAT,YRMS,YDET,V8_REP_YRES_INFOS RES 
WHERE WDAT.WDAT_DATE BETWEEN YRMS.YRMS_VALIDFROM AND YRMS.YRMS_VALIDUNTIL 
AND WDAT.WDAT_DATE=YDET.YDET_DATE
and wdat.wdat_date=RES.YRES_EXPARRTIME
AND YDET.YDET_YRES_ID=RES.YRES_ID 
--AND WDAT.WDAT_DATE BETWEEN V8_sys_fideliodate AND V8_sys_fideliodate 
AND YRMS.YRMS_ID=RES.YDET_YRMS_ID 
AND (SELECT YRES.YRES_RESSTATUS FROM YRES WHERE YRES.YRES_ID=YDET.YDET_YRES_ID)<>3 
AND EXISTS (SELECT 1 FROM YDET YDET_2,YRES WHERE YDET_2.YDET_YRES_ID=YRES.YRES_ID AND YRES.YRES_RESSTATUS<>3 AND YRES.YRES_CALCSTATUS IN (0,1,2) AND YDET.YDET_ID=YDET_2.YDET_ID)
AND NOT EXISTS (SELECT 1 FROM YDET YDET_2,YRES WHERE YDET_2.YDET_YRES_ID=YRES.YRES_ID AND YRES.YRES_RESSTATUS<>3 AND YRES.YRES_CALCSTATUS IN (7,8,9) AND YDET_2.YDET_ID=YDET.YDET_ID)
UNION ALL
SELECT 
'DAY USE' STATUS, 
7 V_STATUS, 
YDET.YDET_ID, 
YDET.YDET_YRES_ID YRES_ID, 
RES.YRES_CALCSTATUS YRES_CALCSTATUS, 
WDAT.WDAT_DATE, 
V8_sys_fideliodate FIDELIO_DATE, 
YRMS.YRMS_ID, 
YRMS.YRMS_YCAT_ID, 
YRMS.YRMS_SHORTDESC, 
YRMS.YRMS_LONGDESC, 
YRMS.YRMS_DISPLORDER, 
YRMS.YRMS_VALIDFROM, 
YRMS.YRMS_VALIDUNTIL, 
YRMS.YRMS_YHKS_ID, 
(SELECT YHKS.YHKS_SHORTDESC FROM YHKS WHERE YHKS.YHKS_ID=YRMS.YRMS_YHKS_ID) YHKS_SHORTDESC, 
(SELECT YHKS.YHKS_LONGDESC FROM YHKS WHERE YHKS.YHKS_ID=YRMS.YRMS_YHKS_ID) YHKS_LONGDESC, 
(SELECT YHKS.YHKS_CLEAN FROM YHKS WHERE YHKS.YHKS_ID=YRMS.YRMS_YHKS_ID) YHKS_CLEAN, 
DECODE(NVL((SELECT YHKS.YHKS_CLEAN FROM YHKS WHERE YHKS.YHKS_ID=YRMS.YRMS_YHKS_ID),0),0,1) IS_DIRTY, 
DECODE((SELECT COUNT(YRST_ID) FROM YRST WHERE YRMS.YRMS_ID = YRST.YRST_YRMS_ID AND WDAT.WDAT_DATE >= YRST.YRST_FROMTIME AND WDAT.WDAT_DATE < YRST.YRST_UNTILTIME and YRST.YRST_YRSC_ID=1),1,1,0) IS_OOO,
DECODE((SELECT COUNT(YRST_ID) FROM YRST WHERE YRMS.YRMS_ID = YRST.YRST_YRMS_ID AND WDAT.WDAT_DATE >= YRST.YRST_FROMTIME AND WDAT.WDAT_DATE <= YRST.YRST_UNTILTIME and YRST.YRST_YRSC_ID=1),1,1,0) IS_OOO_HSK,
DECODE((SELECT COUNT(YRST_ID) FROM YRST WHERE YRMS.YRMS_ID = YRST.YRST_YRMS_ID AND WDAT.WDAT_DATE >= YRST.YRST_FROMTIME AND WDAT.WDAT_DATE <trunc(YRST.YRST_UNTILTIME) and YRST.YRST_YRSC_ID=2),1,1,0) IS_OOS,
DECODE((SELECT COUNT(YRST_ID) FROM YRST WHERE YRMS.YRMS_ID = YRST.YRST_YRMS_ID AND WDAT.WDAT_DATE >= YRST.YRST_FROMTIME AND WDAT.WDAT_DATE <= trunc(YRST.YRST_UNTILTIME) and YRST.YRST_YRSC_ID=2),1,1,0) IS_OOS_HSK,
YRMS.YRMS_NOOFPERSONS, 
YRMS.YRMS_FLOOR, 
YRMS.YRMS_HSK_OCCUPIED, 
YRMS.YRMS_MAX_BEDS, 
YRMS.YRMS_FREETEXT, 
YRMS.YRMS_L_STATUS, 
YRMS.YRMS_HSK_NOOFPERSONS, 
YRMS.YRMS_DEFAULTCREDITS, 
(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, 
(SELECT YCAT.YCAT_DISPLORDER FROM YCAT WHERE YCAT.YCAT_ID=YRMS.YRMS_YCAT_ID) YCAT_DISPLORDER, 
(SELECT YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID FROM YCAT WHERE YCAT.YCAT_ID=YRMS.YRMS_YCAT_ID) YCAD_ID,
(SELECT YCAT.YCAT_SUITE FROM YCAT WHERE YCAT.YCAT_ID=YRMS.YRMS_YCAT_ID) YCAT_SUITE,
RES.NAME, 
RES.YRES_EXPARRTIME, 
RES.YRES_EXPDEPTIME, 
RES.YRES_EXPARRHOUR, 
RES.YRES_EXPDEPHOUR, 
nvl2(res.yres_actdeptime,1,0) CHECKED_OUT,
RES.YRES_RESSTATUS, 
RES.YRES_FREETEXT1, 
RES.YRES_FREETEXT2, 
RES.YDET_NOOFROOMS, 
RES.YDET_ADULTNO, 
RES.TOTALKIDS, 
RES.TOTALKIDS_G1, 
RES.TOTALKIDS_G2, 
RES.TOTALKIDS_G3, 
RES.YBLH_ID, 
RES.YBLH_SHORTDESC, 
RES.CONF_RATE, 
RES.CALC_PRICE, 
RES.YRCH_ID, 
RES.YRCH_SHORTDESC, 
RES.YRCH_COMPSTATUS, 
RES.ALL_YCLN_NAMES, 
RES.YDET_XCMA_ID, 
RES.XCMA_SHORTDESC, 
RES.IS_RES_SPECIALS, 
RES.RES_SPECIALS_SHORT, 
RES.RES_SPECIALS_LONG, 
RES.IS_XCMS_SPECIAL, 
RES.XCMS_SPECIALS_SHORT, 
RES.XCMS_SPECIALS_LONG, 
RES.PACKAGES_SHORT, 
RES.PACKAGES_LONG, 
RES.ALL_PACKAGES_SHORT, 
RES.ALL_PACKAGES_LONG, 
RES.IS_YRES_NOTES_NON_VABLE, 
RES.YRES_NOTES_NON_VABLE_SHORT, 
RES.YRES_NOTES_NON_VABLE_LONG, 
RES.IS_YRES_NOTES_VABLE, 
RES.YRES_NOTES_VABLE_SHORT, 
RES.YRES_NOTES_VABLE_LONG, 
RES.IS_XCMS_NOTES_NON_VABLE, 
RES.XCMS_NOTES_NON_VABLE_SHORT, 
RES.XCMS_NOTES_NON_VABLE_LONG, 
RES.IS_XCMS_NOTES_VABLE, 
RES.XCMS_NOTES_VABLE_SHORT, 
RES.XCMS_NOTES_VABLE_LONG, 
RES.SHARER_NAME, 
RES.MULTI_GUEST_NAMES, 
RES.ALL_AMENITIES, 
RES.AMENITIES, 
RES.YDET_AMENITIES_SHORT, 
RES.YDET_AMENITIES_LONG, 
RES.CHILDREN_SHORT, 
RES.CHILDREN_LONG,
RES.VIP_SHORTDESC ,
(select min(ydet_s.ydet_date) from ydet ydet_s where ydet_s.ydet_yres_id=Res.yres_id and ydet_s.ydet_yrms_id=yrms.yrms_id) ARRIVAL_ROOM,
ydet_inventory(ydet.ydet_id,1) inventory_day,
RES.EXP_ROOM_MOVE_TODAY EXP_ROOM_MOVE_TODAY,
RES.ROOM_MOVE_DONE_TODAY ROOM_MOVE_DONE_TODAY,
RES.TODAY_ROOM_MOVE_FROM TODAY_ROOM_MOVE_FROM,
RES.TODAY_ROOM_MOVE_TO TODAY_ROOM_MOVE_TO
FROM 
WDAT,YRMS,YDET,V8_REP_YRES_INFOS RES 
WHERE WDAT.WDAT_DATE BETWEEN YRMS.YRMS_VALIDFROM AND YRMS.YRMS_VALIDUNTIL 
AND WDAT.WDAT_DATE=YDET.YDET_DATE
AND YDET.YDET_YRES_ID=RES.YRES_ID 
--AND WDAT.WDAT_DATE BETWEEN V8_sys_fideliodate AND V8_sys_fideliodate 
AND YRMS.YRMS_ID=RES.YDET_YRMS_ID 
AND (SELECT YRES.YRES_RESSTATUS FROM YRES WHERE YRES.YRES_ID=YDET.YDET_YRES_ID)<>3 
AND NOT EXISTS (SELECT 1 FROM YDET YDET_2,YRES WHERE YDET_2.YDET_YRES_ID=YRES.YRES_ID AND YRES.YRES_RESSTATUS<>3 AND YRES.YRES_CALCSTATUS IN (1,2) AND YDET_2.YDET_ID=YDET.YDET_ID)
AND EXISTS (SELECT 1 FROM YDET YDET_2,YRES WHERE YDET_2.YDET_YRES_ID=YRES.YRES_ID AND YRES.YRES_RESSTATUS<>3 AND YRES.YRES_CALCSTATUS IN (7,8,9) AND YDET_2.YDET_ID=YDET.YDET_ID);

CREATE OR REPLACE VIEW V8_REP_INVOICENO
AS 
select/*+rule*/ 
distinct 
zz.zinv_id,  
zz.zinv_correcting_zinv_id, 
zz.zinv_void_zinv_id, 
zinn_type, 
zz.ZINV_NUMBER,  
zz.ZINV_ROLE,
zz.ZINV_DATE, 
ZIPL_ID, 
Zwin_id, 
zwin_yres_id, 
zwin_zfac_id, 
zfag_type,  
(case when (select max(zinv_id)from zinv voider where voider.zinv_void_zinv_id=zz.zinv_id) is not null then 1 else 0 end) VOID_IS_INVALID,
(select zinv_id from zinv voider where voider.zinv_void_zinv_id=zz.zinv_id) voider_zinv_id,  
(select zinv_id from zinv voider where voider.zinv_correcting_zinv_id=zz.zinv_id) corrector_zinv_id,  
(select ZNCR_ID from zncr where zncr_zinb_id = revi.zinb_id) NotaCorrection,  
Z.ZINB_ID, 
Z.ZINB_TYPE, 
Z.zinb_parent_zinb_id, 
Z.zinb_editor_xcms_id,  
Z.zinb_editor_datetime, 
Z.zinb_revision_number, 
Z.zinb_note,  
revi.ZINB_ID revi_id, 
revi.ZINB_TYPE revi_type, 
revi.zinb_parent_zinb_id revi_parent_zinb_id,  
revi.zinb_editor_xcms_id revi_editor_xcms_id, 
revi.zinb_editor_datetime revi_editor_datetime,  
revi.zinb_revision_number revi_revision_number, 
revi.zinb_note revi_note,  
x1.XCMS_ID, 
x1.XCMS_NAME1, 
x1.XCMS_NAME2, 
x1.XCMS_NAME3,  
Decode(nvl(ZFAC_ID, 0), 0, Decode(x2.XCMS_TYPE, 1, x2.XCMS_NAME1||' '||x2.XCMS_NAME2||' '||x2.XCMS_NAME3,
Trim(Decode(nvl(x2.XCMS_NAME1,''),'','',x2.XCMS_NAME1||Decode(nvl(x2.XCMS_NAME3,''),'','',',')||' ')||Decode(nvl(x2.XCMS_NAME3,
''),'','',x2.XCMS_NAME3||' ')||x2.XCMS_NAME2)),   Decode(x3.XCMS_TYPE, 1, x3.XCMS_NAME1||' '||x3.XCMS_NAME2||' '||x3.XCMS_NAME3,
Trim(Decode(nvl(x3.XCMS_NAME1,''),'','',x3.XCMS_NAME1||Decode(nvl(x3.XCMS_NAME3,''),'','',',')||' ')||Decode(nvl(x3.XCMS_NAME3,
''),'','',x3.XCMS_NAME3||' ')||x2.XCMS_NAME2 ))) ReservationName,  Decode(x1.XCMS_TYPE, 1, 
x1.XCMS_NAME1||' '||x1.XCMS_NAME2||' '||x1.XCMS_NAME3,Trim(Decode(nvl(x1.XCMS_NAME1,''),'','',
x1.XCMS_NAME1||Decode(nvl(x1.XCMS_NAME3,''),'','',',')||' ')||Decode(nvl(x1.XCMS_NAME3,''),'','',
x1.XCMS_NAME3||' ')||x1.XCMS_NAME2 )) Invoicename, 
decode(nvl(zfac_id,0),0,yrms_shortdesc,zfac_longdesc) roomzfac_longdesc,  
YRMS_SHORTDESC, 
YRMS_ID, 
ZFST_LONGDESC, 
ZFST_WRPT_ID   
from ZINV zz, 
ZWIN, 
ZFAN, 
YDET, 
YRMS, 
ZINB Z, 
ZIPL, 
XCMS x1,  
XCMS x2, 
XCMS x3, 
ZFAC, 
YRES, 
zinn, 
ZINB REVI, 
ZFST, 
zfag  
where ZINV_ZWIN_ID=ZWIN_ID(+) 
and ZWIN_YRES_ID=YDET_YRES_ID(+) 
and YDET_YRMS_ID=YRMS_ID(+) 
and zz.ZINV_ID=ZIPL_ZINV_ID(+) 
and ZIPL_ZINB_ID=Z.ZINB_ID(+) 
and ZINV_XCMS_ID=x1.XCMS_ID(+) 
and ZFAC_ID(+) = ZWIN_ZFAC_ID 
and YRES_ID(+) = ZWIN_YRES_ID 
and x2.XCMS_ID(+)=YRES_XCMS_ID 
and x3.xcms_id(+)=zfac_xcms_id 
and NVL(Z.ZINB_PARENT_ZINB_ID,0)=0 
and REVI.ZINB_PARENT_ZINB_ID(+)=Z.ZINB_ID 
and zinn_id=zz.zinv_zinn_id 
and zz.zinv_zfst_id=zfst_id(+) 
and ZFAC_ZFAN_ID=ZFAN_ID(+) 
and zz.ZINV_ROLE <> 10 
and zfac_zfag_id=zfag_id(+)
union all 
select distinct 
zz.ZINV_ID, 
zz.zinv_correcting_zinv_id, 
zz.zinv_void_zinv_id, zinn_type, 
zz.ZINV_NUMBER, 
zz.ZINV_ROLE, 
zz.ZINV_DATE, 
ZIPL_ID, 
zwin_id, 
zwin_yres_id, 
zwin_zfac_id, 
zfag_type,  
(case when zz.zinv_void_zinv_id is not null OR exists (select 1 from zinv voider where voider.zinv_void_zinv_id=zz.zinv_id) then 1 else 0 end) VOID_IS_INVALID,
(select zinv_id from zinv voider where voider.zinv_void_zinv_id=zz.zinv_id) voider_zinv_id, 
(select zinv_id from zinv voider where voider.zinv_correcting_zinv_id=zz.zinv_id) corrector_zinv_id,  
(select ZNCR_ID from zncr where zncr_zinb_id = revi.zinb_id) NotaCorrection, 
Z.ZINB_ID, 
Z.ZINB_TYPE, 
Z.zinb_parent_zinb_id, 
Z.zinb_editor_xcms_id, 
Z.zinb_editor_datetime, 
Z.zinb_revision_number, 
Z.zinb_note, 
revi.ZINB_ID revi_id, 
revi.ZINB_TYPE 
revi_type, 
revi.zinb_parent_zinb_id revi_parent_zinb_id, 
revi.zinb_editor_xcms_id revi_editor_xcms_id, 
revi.zinb_editor_datetime revi_editor_datetime,
revi.zinb_revision_number revi_revision_number, 
revi.zinb_note revi_note, 
x1.XCMS_ID, 
x1.XCMS_NAME1, 
x1.XCMS_NAME2, 
x1.XCMS_NAME3, 
Decode(nvl(x3.XCMS_ID, 0), 0, Decode(x2.XCMS_TYPE, 1, x2.XCMS_NAME1||' '||x2.XCMS_NAME2||' '||x2.XCMS_NAME3,Trim(Decode(nvl(x2.XCMS_NAME1,''),'','',
x2.XCMS_NAME1||Decode(nvl(x2.XCMS_NAME3,''),'','',',')||' ')||Decode(nvl(x2.XCMS_NAME3,''),'','',
x2.XCMS_NAME3||' ')||x2.XCMS_NAME2)), Decode(x3.XCMS_TYPE, 1, x3.XCMS_NAME1||' '||x3.XCMS_NAME2||' '||x3.XCMS_NAME3,
Trim(Decode(nvl(x3.XCMS_NAME1,''),'','',x3.XCMS_NAME1||Decode(nvl(x3.XCMS_NAME3,''),'','',',')||' ')||Decode(nvl(x3.XCMS_NAME3,
''),'','',x3.XCMS_NAME3||' ')||x2.XCMS_NAME2 ))) ReservationName, Decode(x1.XCMS_TYPE, 1, 
x1.XCMS_NAME1||' '||x1.XCMS_NAME2||' '||x1.XCMS_NAME3,Trim(Decode(nvl(x1.XCMS_NAME1,''),'','',
x1.XCMS_NAME1||Decode(nvl(x1.XCMS_NAME3,''),'','',',')||' ')||Decode(nvl(x1.XCMS_NAME3,''),'','',
x1.XCMS_NAME3||' ')||x1.XCMS_NAME2 )) Invoicename, 
'AR / '||nvl(AACC_NUMBER, '') roomzfac_longdesc, 
'' YRMS_SHORTDESC, 0 
YRMS_ID, 
ZFST_LONGDESC, 
ZFST_WRPT_ID 
from ZINV zz, 
ZWIN, 
ZINB Z , 
YDET, 
YRMS, 
ZIPL, 
XCMS x1, 
XCMS x2, 
XCMS x3, 
ZINN, 
ZINB REVI, 
ZFST, 
AACC, 
ZFRL, 
ACPL, 
AINV, 
zfac, 
zfag 
where
zinn_id = zinv_zinn_id 
and zz.ZINV_ZWIN_ID=ZWIN_ID(+)
and ZWIN_YRES_ID=YDET_YRES_ID(+) 
and YDET_YRMS_ID=YRMS_ID(+) 
and ZIPL_ZINV_ID(+) = zz.ZINV_ID 
and Z.ZINB_ID(+) = ZIPL_ZINB_ID 
and NVL(Z.ZINB_PARENT_ZINB_ID,0)=0 
and REVI.ZINB_PARENT_ZINB_ID(+)=Z.ZINB_ID 
and zfst_id(+) = zinv_zfst_id 
and AACC_ID(+) = AINV_AACC_ID 
and ZFRL_ZINV_ID(+) = zz.ZINV_ID
and ACPL_ACMP_ID(+) = ZFRL_ACMP_ID 
and AINV_ID(+) = ACPL_AINV_ID 
and x1.XCMS_ID(+) = ZINV_XCMS_ID 
and x2.xcms_id(+) = AACC_XCMS_ID 
and x3.XCMS_ID(+) = AACC_CONTACT_XCMS_ID 
and ZINV_ROLE = 10 
and zwin_zfac_id=zfac_id(+) 
and zfac_zfag_id=zfag_id(+) 
order by zinv_id, zinb_id, yrms_id, revi_id;

CREATE OR REPLACE VIEW V8_REP_ARR_DEP_IN_MEALPLAN
AS 
select
YDET_DATE,
YDET_ID,
(case when(YDET_DATE=YRES_EXPARRTIME) then 1 else 0 END) ARRIVAL,
(case when(YDET_DATE=YRES_EXPDEPTIME) then 1 else 0 END) DEPARTURE,
(case when(YDET_DATE<>YRES_EXPARRTIME and YDET_DATE<>YRES_EXPDEPTIME) then 1 else 0 END) INHOUSE,
FIDELIODATUM,
YDET_YRES_ID,
YRES_EXPARRTIME,
YRES_EXPDEPTIME,
YRES_XCMS_ID,
YDET_NOOFROOMS,
YDET_ADULTNO YDET_ADULTNO,
TOTALKIDS TOTALKIDS,
PAX PAX,
YRES_RESSTATUS,
NO_BREAKFAST,
NO_LUNCH,
NO_DINNER
from
(
SELECT 
ydet.YDET_DATE YDET_DATE,
YDET.YDET_ID,
V8_sys_fideliodate FIDELIODATUM,
ydet.YDET_YRES_ID YDET_YRES_ID,
(SELECT yres.YRES_EXPARRTIME FROM yres WHERE yres.YRES_ID = ydet.YDET_YRES_ID) YRES_EXPARRTIME,
(SELECT yres.YRES_EXPDEPTIME FROM yres WHERE yres.YRES_ID = ydet.YDET_YRES_ID) YRES_EXPDEPTIME,
(SELECT yres.yres_xcms_id FROM yres WHERE yres.YRES_ID = ydet.YDET_YRES_ID) YRES_XCMS_ID,
  NVL ((DECODE (NVL (ydet.YDET_SHARENUM, 0), 0, ydet.YDET_NOOFROOMS,(CASE WHEN ydet.YDET_ID =(SELECT MIN (ydet_2.YDET_ID) FROM ydet ydet_2 WHERE ydet_2.YDET_SHARENUM = ydet.YDET_SHARENUM) THEN (SELECT AVG (ydet_2.YDET_NOOFROOMS) FROM ydet ydet_2 WHERE ydet_2.YDET_SHARENUM = ydet.YDET_SHARENUM) ELSE 0 END))), 0) YDET_NOOFROOMS,
 --NVL (ydet.YDET_ADULTNO, 0) YDET_ADULTNO,
 decode(nvl(YDET.YDET_SHARENUM,0),0, NVL (ydet.YDET_ADULTNO, 0)*nvl(YDET.YDET_NOOFROOMS,0),nvl(ydet.ydet_adultno,0)) YDET_ADULTNO,
 --NVL ( (SELECT SUM (yrgl_number) FROM yrgl WHERE yrgl.YRGL_YDET_ID = ydet.YDET_ID), 0) TOTALKIDS,
  decode(nvl(YDET.YDET_SHARENUM,0),0, NVL ( (SELECT SUM (yrgl_number) FROM yrgl WHERE yrgl.YRGL_YDET_ID = ydet.YDET_ID), 0)*nvl(YDET.YDET_NOOFROOMS,0),NVL ( (SELECT SUM (yrgl_number) FROM yrgl WHERE yrgl.YRGL_YDET_ID = ydet.YDET_ID), 0)) TOTALKIDS,
 --( NVL (ydet.YDET_ADULTNO, 0) + NVL ( (SELECT SUM (yrgl_number) FROM yrgl WHERE yrgl.YRGL_YDET_ID = ydet.YDET_ID), 0)) PAX,
 decode(nvl(YDET.YDET_SHARENUM,0),0, ( NVL (ydet.YDET_ADULTNO, 0) + NVL ( (SELECT SUM (yrgl_number) FROM yrgl WHERE yrgl.YRGL_YDET_ID = ydet.YDET_ID), 0))*nvl(YDET.YDET_NOOFROOMS,0),( NVL (ydet.YDET_ADULTNO, 0) + NVL ( (SELECT SUM (yrgl_number) FROM yrgl WHERE yrgl.YRGL_YDET_ID = ydet.YDET_ID), 0))) PAX,
 (SELECT YRES.yres_RESSTATUS FROM yres WHERE yres.Yres_ID = ydet_Yres_id) yres_RESSTATUS,
 NVL((SELECT ybfo.YBFO_B FROM ybfo WHERE ybfo.YBFO_YRES_ID = YDET_YRES_ID AND ybfo.YBFO_DATE = YDET_DATE), 0)  NO_BREAKFAST,
NVL((SELECT ybfo.YBFO_L FROM ybfo WHERE ybfo.YBFO_YRES_ID = YDET_YRES_ID AND ybfo.YBFO_DATE = YDET_DATE), 0)  NO_LUNCH,
NVL ((SELECT ybfo.YBFO_D FROM ybfo WHERE     ybfo.YBFO_YRES_ID = YDET_YRES_ID AND ybfo.YBFO_DATE = YDET_DATE), 0) NO_DINNER
 FROM wdat, ydet
 where  wdat.WDAT_DATE = ydet.YDET_DATE(+)
 and (SELECT YRES.yres_RESSTATUS FROM yres WHERE yres.Yres_ID = ydet_Yres_id) <>3
-- and ydet_yres_id=4287
union all
(select
YDET_DATE,
YDET_ID,
FIDELIODATUM,
YDET_YRES_ID,
YRES_EXPARRTIME,
YRES_EXPDEPTIME,
YRES_XCMS_ID,
YDET_NOOFROOMS,
YDET_ADULTNO,
TOTALKIDS,
PAX,
YRES_RESSTATUS,
NVL((SELECT ybfo.YBFO_B FROM ybfo WHERE ybfo.YBFO_YRES_ID = YDET_YRES_ID AND ybfo.YBFO_DATE = YDET_DATE), 0)  NO_BREAKFAST,
NVL((SELECT ybfo.YBFO_L FROM ybfo WHERE ybfo.YBFO_YRES_ID = YDET_YRES_ID AND ybfo.YBFO_DATE = YDET_DATE), 0)  NO_LUNCH,
NVL ((SELECT ybfo.YBFO_D FROM ybfo WHERE     ybfo.YBFO_YRES_ID = YDET_YRES_ID AND ybfo.YBFO_DATE = YDET_DATE), 0) NO_DINNER
from
(select
ydet_date ydet_Date,
max(YDET_ID) YDET_ID,
MAX( FIDELIODATUM) FIDELIODATUM,
YDET_YRES_ID,
MAX(YRES_EXPARRTIME)YRES_EXPARRTIME,
MAX(YRES_EXPDEPTIME) YRES_EXPDEPTIME,
MAX(YRES_XCMS_ID) YRES_XCMS_ID,
YDET_NOOFROOMS,
YDET_ADULTNO,
TOTALKIDS,
PAX,
yres_RESSTATUS from
(SELECT 
(SELECT yres.YRES_EXPDEPTIME FROM yres WHERE yres.YRES_ID = ydet.YDET_YRES_ID) YDET_DATE,
YDET_ID,
V8_sys_fideliodate FIDELIODATUM,
ydet.YDET_YRES_ID YDET_YRES_ID,
(SELECT yres.YRES_EXPARRTIME FROM yres WHERE yres.YRES_ID = ydet.YDET_YRES_ID) YRES_EXPARRTIME,
(SELECT yres.YRES_EXPDEPTIME FROM yres WHERE yres.YRES_ID = ydet.YDET_YRES_ID) YRES_EXPDEPTIME,
(SELECT yres.yres_xcms_id FROM yres WHERE yres.YRES_ID = ydet.YDET_YRES_ID) YRES_XCMS_ID,
 NVL ((DECODE (NVL (ydet.YDET_SHARENUM, 0), 0, ydet.YDET_NOOFROOMS,(CASE WHEN ydet.YDET_ID =(SELECT MIN (ydet_2.YDET_ID) FROM ydet ydet_2 WHERE ydet_2.YDET_SHARENUM = ydet.YDET_SHARENUM) THEN (SELECT AVG (ydet_2.YDET_NOOFROOMS) FROM ydet ydet_2 WHERE ydet_2.YDET_SHARENUM = ydet.YDET_SHARENUM) ELSE 0 END))), 0) YDET_NOOFROOMS,
 NVL (ydet.YDET_ADULTNO, 0) YDET_ADULTNO,
 NVL ( (SELECT SUM (yrgl_number) FROM yrgl WHERE yrgl.YRGL_YDET_ID = ydet.YDET_ID), 0) TOTALKIDS,
 ( NVL (ydet.YDET_ADULTNO, 0) + NVL ( (SELECT SUM (yrgl_number) FROM yrgl WHERE yrgl.YRGL_YDET_ID = ydet.YDET_ID), 0)) PAX,
 (SELECT YRES.yres_RESSTATUS FROM yres WHERE yres.Yres_ID = ydet_Yres_id) yres_RESSTATUS
 FROM wdat, ydet
 where  wdat.WDAT_DATE = ydet.YDET_DATE(+) 
 and (SELECT YRES.yres_RESSTATUS FROM yres WHERE yres.Yres_ID = ydet_Yres_id)<>3
 --and ydet_yres_id=4100
 )
 group by YDET_DATE, YDET_YRES_ID, yres_RESSTATUS, YDET_NOOFROOMS,
YDET_ADULTNO,
TOTALKIDS,
PAX
)
)
order by ydet_date, ydet_yres_id);

CREATE OR REPLACE VIEW V8_REP_BLOCKS
AS 
SELECT
        YBLH.YBLH_ID YBLH_ID,
        YBLH.YBLH_SHORTDESC YBLH_SHORTDESC,
        YBLH.YBLH_LONGDESC YBLH_LONGDESC,
        YBLH.YBLH_STARTDATE YBLH_STARTDATE,
    YBLH.YBLH_ENDDATE YBLH_ENDDATE,
    YBLH.YBLH_CUTOFFDATE YBLH_CUTOFFDATE,
    YBLH.YBLH_ELASTIC YBLH_ELASTIC,
    YBLH.YBLH_BACKTOHOUSE YBLH_BACKTOHOUSE,
    trunc(YBLH.YBLH_ROOMINGLISTDATE) YBLH_ROOMINGLISTDATE,
    YBST.YBST_SHORTDESC YBST_SHORTDESC,
    YBLH.YBLH_YBOM_ID YBLH_YBOM_ID,
    YBST.YBST_RESSTATUS YBST_RESSTATUS,
     (SELECT YRCH.YRCH_SHORTDESC FROM YRCH WHERE YBLH.YBLH_YRCH_ID = YRCH_ID) YRCH_SHORTDESC,
     (SELECT XCMA.XCMA_SHORTDESC FROM XCMA WHERE YBLH.YBLH_XCMA_ID = XCMA_ID) XCMA_SHORTDESC,
         (select yras.yras_ID from ybal,YRAS, yrac  where ybal.YBAL_YRAS_ID = YRAS.YRAS_ID AND YRAS.YRAS_YRAC_ID = yrac.yrac_id
                              AND yrac.yrac_ID = 1 and rownum = 1) SOURCE_ID,
         (select yras.YRAS_SHORTDESC from ybal,YRAS, yrac  where ybal.YBAL_YRAS_ID = YRAS.YRAS_ID AND YRAS.YRAS_YRAC_ID = yrac.yrac_id
                              AND yrac.yrac_ID = 1 and rownum = 1) SOURCE,
         (select yras.yras_ID from ybal,YRAS, yrac  where ybal.YBAL_YRAS_ID = YRAS.YRAS_ID AND YRAS.YRAS_YRAC_ID = yrac.yrac_id
                              AND yrac.yrac_ID = 2 and rownum = 1) CHANNEL_ID,
         (select yras.YRAS_SHORTDESC from ybal,YRAS, yrac  where ybal.YBAL_YRAS_ID = YRAS.YRAS_ID AND YRAS.YRAS_YRAC_ID = yrac.yrac_id
                              AND yrac.yrac_ID = 2 and rownum = 1) CHANNEL,
    YBLD.YBLD_ID YBLD_ID,
     YBLD.YBLD_DATE YBLD_DATE,
     YBLD.YBLD_INITIALNOOFROOMS YBLD_INITIALNOOFROOMS,
     YBLD.YBLD_ACTUALNOOFROOMS YBLD_ACTUALNOOFROOMS,
     YBLD.YBLD_CUTOFFDATE YBLD_CUTOFFDATE,
         (select v8_rep_name.NAME2 from v8_rep_name,ybcl where v8_rep_name.XCMS_ID=ybcl.YBCL_XCMS_ID and ybcl.YBCL_YBLH_ID=yblh.YBLH_ID and ybcl.YBCL_INTERNALCATEGORY=1 and ybcl.YBCL_PRIMARY=0 and rownum = 1) COMPANY_NAME,
     (select v8_rep_name.NAME2 from v8_rep_name,ybcl where v8_rep_name.XCMS_ID=ybcl.YBCL_XCMS_ID and ybcl.YBCL_YBLH_ID=yblh.YBLH_ID and ybcl.YBCL_INTERNALCATEGORY=2 and ybcl.YBCL_PRIMARY=0 and rownum = 1) TRAVELAGENT_NAME,
     (select v8_rep_name.NAME2 from v8_rep_name,ybcl where v8_rep_name.XCMS_ID=ybcl.YBCL_XCMS_ID and ybcl.YBCL_YBLH_ID=yblh.YBLH_ID and ybcl.YBCL_INTERNALCATEGORY=3 and ybcl.YBCL_PRIMARY=0 and rownum = 1) SOURCE_NAME,
     (select v8_rep_name.NAME2 from v8_rep_name,ybcl where v8_rep_name.XCMS_ID=ybcl.YBCL_XCMS_ID and ybcl.YBCL_YBLH_ID=yblh.YBLH_ID and ybcl.YBCL_INTERNALCATEGORY=4 and ybcl.YBCL_PRIMARY=0 and rownum = 1) INDIVIDUAL_NAME,
     (select v8_rep_name.NAME2 from v8_rep_name,ybcl where v8_rep_name.XCMS_ID=ybcl.YBCL_XCMS_ID and ybcl.YBCL_YBLH_ID=yblh.YBLH_ID and ybcl.YBCL_INTERNALCATEGORY=5 and ybcl.YBCL_PRIMARY=0 and rownum = 1) VENDOR_NAME,
     (select v8_rep_name.NAME2 from v8_rep_name,ybcl where v8_rep_name.XCMS_ID=ybcl.YBCL_XCMS_ID and ybcl.YBCL_YBLH_ID=yblh.YBLH_ID and ybcl.YBCL_INTERNALCATEGORY=6 and ybcl.YBCL_PRIMARY=0 and rownum = 1) MPLANER_NAME,
     (select v8_rep_name.NAME2 from v8_rep_name,ybcl where v8_rep_name.XCMS_ID=ybcl.YBCL_XCMS_ID and ybcl.YBCL_YBLH_ID=yblh.YBLH_ID and ybcl.YBCL_PRIMARY=1 and rownum = 1) BOOKER,
        YCAT.YCAT_ID,
     YCAT.YCAT_SHORTDESC YCAT_SHORTDESC,
     YCAT.YCAT_LONGDESC YCAT_LONGDESC,
     (select yrcv.YRCV_VALUE from yrcv,yrcd,yrch where yrcv.YRCV_YRCD_ID = yrcd.YRCD_ID and yrcd.YRCD_YRCH_ID = yrch.YRCH_ID and YBLH_YRCH_ID = yrch.YRCH_ID and yrcv.YRCV_NOOFADULTS=1 and yrcv.YRCV_DEFAULT=1 and (yrcd.YRCD_STARTDATE<=ybld.YBLD_DATE and yrcd.YRCD_ENDDATE>=ybld.YBLD_DATE) and rownum=1) PAX_DEF,
    V8_FNC_NUM_TO_CHAR_FORMATING(decode(nvl((SELECT 1 FROM YBLR WHERE YBLR.YBLR_YBLD_ID=YBLD.YBLD_ID and YBLR.YBLR_ADULTNO=1),0),0,
  (select yrcv.YRCV_VALUE from yrcv,yrcd,yrch where yrcv.YRCV_YRCD_ID = yrcd.YRCD_ID and yrcd.YRCD_YRCH_ID = yrch.YRCH_ID and YBLH_YRCH_ID = yrch.YRCH_ID and yrcv.YRCV_NOOFADULTS=1 and yrcv.YRCV_DEFAULT<>1 and (yrcd.YRCD_STARTDATE<=ybld.YBLD_DATE and yrcd.YRCD_ENDDATE>=ybld.YBLD_DATE) and rownum=1),
    (select YBLR.YBLR_RATEAMOUNT from yblr where YBLR.YBLR_ADULTNO=1 and YBLR.YBLR_YBLD_ID=YBLD.YBLD_ID))) PAX1,
      V8_FNC_NUM_TO_CHAR_FORMATING(decode(nvl((SELECT 1 FROM YBLR WHERE YBLR.YBLR_YBLD_ID=YBLD.YBLD_ID and YBLR.YBLR_ADULTNO=2),0),0,
      (select yrcv.YRCV_VALUE from yrcv,yrcd,yrch where yrcv.YRCV_YRCD_ID = yrcd.YRCD_ID and yrcd.YRCD_YRCH_ID = yrch.YRCH_ID and YBLH_YRCH_ID = yrch.YRCH_ID and yrcv.YRCV_NOOFADULTS=2 and yrcv.YRCV_DEFAULT<>1 and (yrcd.YRCD_STARTDATE<=ybld.YBLD_DATE and yrcd.YRCD_ENDDATE>=ybld.YBLD_DATE) and rownum=1),
    (select YBLR.YBLR_RATEAMOUNT from yblr where YBLR.YBLR_ADULTNO=1 and YBLR.YBLR_YBLD_ID=YBLD.YBLD_ID))) PAX2,
        V8_FNC_NUM_TO_CHAR_FORMATING(decode(nvl((SELECT 1 FROM YBLR WHERE YBLR.YBLR_YBLD_ID=YBLD.YBLD_ID and YBLR.YBLR_ADULTNO=3),0),0,
      (select yrcv.YRCV_VALUE from yrcv,yrcd,yrch where yrcv.YRCV_YRCD_ID = yrcd.YRCD_ID and yrcd.YRCD_YRCH_ID = yrch.YRCH_ID and YBLH_YRCH_ID = yrch.YRCH_ID and yrcv.YRCV_NOOFADULTS=3 and yrcv.YRCV_DEFAULT<>1 and (yrcd.YRCD_STARTDATE<=ybld.YBLD_DATE and yrcd.YRCD_ENDDATE>=ybld.YBLD_DATE) and rownum=1),
    (select YBLR.YBLR_RATEAMOUNT from yblr where YBLR.YBLR_ADULTNO=1 and YBLR.YBLR_YBLD_ID=YBLD.YBLD_ID))) PAX3,
        V8_FNC_NUM_TO_CHAR_FORMATING(decode(nvl((SELECT 1 FROM YBLR WHERE YBLR.YBLR_YBLD_ID=YBLD.YBLD_ID and YBLR.YBLR_ADULTNO=4),0),0,
      (select yrcv.YRCV_VALUE from yrcv,yrcd,yrch where yrcv.YRCV_YRCD_ID = yrcd.YRCD_ID and yrcd.YRCD_YRCH_ID = yrch.YRCH_ID and YBLH_YRCH_ID = yrch.YRCH_ID and yrcv.YRCV_NOOFADULTS=4 and yrcv.YRCV_DEFAULT<>1 and (yrcd.YRCD_STARTDATE<=ybld.YBLD_DATE and yrcd.YRCD_ENDDATE>=ybld.YBLD_DATE) and rownum=1),
    (select YBLR.YBLR_RATEAMOUNT from yblr where YBLR.YBLR_ADULTNO=1 and YBLR.YBLR_YBLD_ID=YBLD.YBLD_ID))) PAX4,
    (select sum(YDET.YDET_NOOFROOMS) from ydet where YBLD.YBLD_ID = YDET.YDET_YBLD_ID) YDET_NOOFROOMS ,
     (select sum(YDET.YDET_NOOFROOMS) from ydet,yres r where YBLD.YBLD_ID = YDET.YDET_YBLD_ID and ydet.ydet_yres_id=r.yres_id and r.YRES_NOAVAILREASON=3) CANCELROOM,
     (select sum(YDET.YDET_NOOFROOMS) from ydet,yres r where YBLD.YBLD_ID = YDET.YDET_YBLD_ID and ydet.ydet_yres_id=r.yres_id and r.YRES_NOAVAILREASON=4) NOSHOWROOM,
     (select sum(YDET.YDET_NOOFROOMS) from ydet,yres r where YBLD.YBLD_ID = YDET.YDET_YBLD_ID and ydet.ydet_yres_id=r.yres_id and r.YRES_ORGDEPTIME>r.YRES_ACTDEPTIME) EARLYDEPROOM,
     (SELECT 1 FROM YRCH WHERE YBLH.YBLH_YRCH_ID = YRCH_ID and yrch.YRCH_COMPSTATUS=1) COMPLIROOMS,
     (SELECT 1 FROM YRCH WHERE YBLH.YBLH_YRCH_ID = YRCH_ID and yrch.YRCH_COMPSTATUS=2) HOUSEUSEROOMS,
      decode(nvl((SELECT 1 FROM YBLR WHERE YBLR.YBLR_YBLD_ID=YBLD.YBLD_ID and YBLR.YBLR_ADULTNO=1),0),0,
      V8_FNC_VALCURR_FORMATING((select yrcv.YRCV_VALUE from yrcv,yrcd,yrch where yrcv.YRCV_YRCD_ID = yrcd.YRCD_ID and yrcd.YRCD_YRCH_ID = yrch.YRCH_ID and YBLH_YRCH_ID = yrch.YRCH_ID and yrcv.YRCV_NOOFADULTS=1 and yrcv.YRCV_DEFAULT<>1 and (yrcd.YRCD_STARTDATE<=ybld.YBLD_DATE and yrcd.YRCD_ENDDATE>=ybld.YBLD_DATE) and rownum=1),
        (select zcur.zcur_shortdesc from yrcv,yrcd,yrch,zcur where yrcv.YRCV_YRCD_ID = yrcd.YRCD_ID and yrcd.YRCD_YRCH_ID = yrch.YRCH_ID and YBLH_YRCH_ID = yrch.YRCH_ID and yrcv.YRCV_NOOFADULTS=1 and yrcv.YRCV_DEFAULT<>1 and (yrcd.YRCD_STARTDATE<=ybld.YBLD_DATE and yrcd.YRCD_ENDDATE>=ybld.YBLD_DATE) and yrch.yrch_zcur_id=zcur.zcur_id and rownum=1)),
      V8_FNC_VALCURR_FORMATING((select YBLR.YBLR_RATEAMOUNT from yblr where YBLR.YBLR_ADULTNO=1 and YBLR.YBLR_YBLD_ID=YBLD.YBLD_ID),(select ZCUR.ZCUR_SHORTDESC from zcur,yrch where ZCUR.ZCUR_ID=YRCH.YRCH_ZCUR_ID and YRCH.YRCH_ID=YBLH.YBLH_YRCH_ID and rownum=1))) 
      FPAX1,
      decode(nvl((SELECT 1 FROM YBLR WHERE YBLR.YBLR_YBLD_ID=YBLD.YBLD_ID and YBLR.YBLR_ADULTNO=2),0),0,
      V8_FNC_VALCURR_FORMATING((select yrcv.YRCV_VALUE from yrcv,yrcd,yrch where yrcv.YRCV_YRCD_ID = yrcd.YRCD_ID and yrcd.YRCD_YRCH_ID = yrch.YRCH_ID and YBLH_YRCH_ID = yrch.YRCH_ID and yrcv.YRCV_NOOFADULTS=2 and yrcv.YRCV_DEFAULT<>1 and (yrcd.YRCD_STARTDATE<=ybld.YBLD_DATE and yrcd.YRCD_ENDDATE>=ybld.YBLD_DATE) and rownum=1),
        (select zcur.zcur_shortdesc from yrcv,yrcd,yrch,zcur where yrcv.YRCV_YRCD_ID = yrcd.YRCD_ID and yrcd.YRCD_YRCH_ID = yrch.YRCH_ID and YBLH_YRCH_ID = yrch.YRCH_ID and yrcv.YRCV_NOOFADULTS=2 and yrcv.YRCV_DEFAULT<>1 and (yrcd.YRCD_STARTDATE<=ybld.YBLD_DATE and yrcd.YRCD_ENDDATE>=ybld.YBLD_DATE) and yrch.yrch_zcur_id=zcur.zcur_id and rownum=1)),
      V8_FNC_VALCURR_FORMATING((select YBLR.YBLR_RATEAMOUNT from yblr where YBLR.YBLR_ADULTNO=2 and YBLR.YBLR_YBLD_ID=YBLD.YBLD_ID),(select ZCUR.ZCUR_SHORTDESC from zcur,yrch where ZCUR.ZCUR_ID=YRCH.YRCH_ZCUR_ID and YRCH.YRCH_ID=YBLH.YBLH_YRCH_ID and rownum=1))) 
      FPAX2,
      decode(nvl((SELECT 1 FROM YBLR WHERE YBLR.YBLR_YBLD_ID=YBLD.YBLD_ID and YBLR.YBLR_ADULTNO=3),0),0,
      V8_FNC_VALCURR_FORMATING((select yrcv.YRCV_VALUE from yrcv,yrcd,yrch where yrcv.YRCV_YRCD_ID = yrcd.YRCD_ID and yrcd.YRCD_YRCH_ID = yrch.YRCH_ID and YBLH_YRCH_ID = yrch.YRCH_ID and yrcv.YRCV_NOOFADULTS=3 and yrcv.YRCV_DEFAULT<>1 and (yrcd.YRCD_STARTDATE<=ybld.YBLD_DATE and yrcd.YRCD_ENDDATE>=ybld.YBLD_DATE) and rownum=1),
        (select zcur.zcur_shortdesc from yrcv,yrcd,yrch,zcur where yrcv.YRCV_YRCD_ID = yrcd.YRCD_ID and yrcd.YRCD_YRCH_ID = yrch.YRCH_ID and YBLH_YRCH_ID = yrch.YRCH_ID and yrcv.YRCV_NOOFADULTS=3 and yrcv.YRCV_DEFAULT<>1 and (yrcd.YRCD_STARTDATE<=ybld.YBLD_DATE and yrcd.YRCD_ENDDATE>=ybld.YBLD_DATE) and yrch.yrch_zcur_id=zcur.zcur_id and rownum=1)),
      V8_FNC_VALCURR_FORMATING((select YBLR.YBLR_RATEAMOUNT from yblr where YBLR.YBLR_ADULTNO=3 and YBLR.YBLR_YBLD_ID=YBLD.YBLD_ID),(select ZCUR.ZCUR_SHORTDESC from zcur,yrch where ZCUR.ZCUR_ID=YRCH.YRCH_ZCUR_ID and YRCH.YRCH_ID=YBLH.YBLH_YRCH_ID and rownum=1))) 
      FPAX3,
      decode(nvl((SELECT 1 FROM YBLR WHERE YBLR.YBLR_YBLD_ID=YBLD.YBLD_ID and YBLR.YBLR_ADULTNO=4),0),0,
      V8_FNC_VALCURR_FORMATING((select yrcv.YRCV_VALUE from yrcv,yrcd,yrch where yrcv.YRCV_YRCD_ID = yrcd.YRCD_ID and yrcd.YRCD_YRCH_ID = yrch.YRCH_ID and YBLH_YRCH_ID = yrch.YRCH_ID and yrcv.YRCV_NOOFADULTS=4 and yrcv.YRCV_DEFAULT<>1 and (yrcd.YRCD_STARTDATE<=ybld.YBLD_DATE and yrcd.YRCD_ENDDATE>=ybld.YBLD_DATE) and rownum=1),
        (select zcur.zcur_shortdesc from yrcv,yrcd,yrch,zcur where yrcv.YRCV_YRCD_ID = yrcd.YRCD_ID and yrcd.YRCD_YRCH_ID = yrch.YRCH_ID and YBLH_YRCH_ID = yrch.YRCH_ID and yrcv.YRCV_NOOFADULTS=4 and yrcv.YRCV_DEFAULT<>1 and (yrcd.YRCD_STARTDATE<=ybld.YBLD_DATE and yrcd.YRCD_ENDDATE>=ybld.YBLD_DATE) and yrch.yrch_zcur_id=zcur.zcur_id and rownum=1)),
      V8_FNC_VALCURR_FORMATING((select YBLR.YBLR_RATEAMOUNT from yblr where YBLR.YBLR_ADULTNO=4 and YBLR.YBLR_YBLD_ID=YBLD.YBLD_ID),(select ZCUR.ZCUR_SHORTDESC from zcur,yrch where ZCUR.ZCUR_ID=YRCH.YRCH_ZCUR_ID and YRCH.YRCH_ID=YBLH.YBLH_YRCH_ID and rownum=1))) 
      FPAX4,
     (select sum(YBOS.YBOS_QUANTITY*YBOS.YBOS_UNITPRICE) from ybos where YBOS.YBOS_YBLD_ID=ybld.ybld_id) RATE,
     V8_FNC_VALCURR_FORMATING((select sum(YBOS.YBOS_QUANTITY*YBOS.YBOS_UNITPRICE) from ybos where YBOS.YBOS_YBLD_ID=ybld.ybld_id),(select ZCUR.ZCUR_SHORTDESC from zcur,yrch where zcur.zcur_id=YRCH.YRCH_ZCUR_ID and YBLH.YBLH_YRCH_ID=YRCH.YRCH_ID and rownum=1)) FRATE,
     V8_FNC_VALCURR_FORMATING((select sum(YBOS.YBOS_UNITPRICE) from ybos where YBOS.YBOS_YBLD_ID=ybld.ybld_id),(select ZCUR.ZCUR_SHORTDESC from zcur,yrch where zcur.zcur_id=YRCH.YRCH_ZCUR_ID and YBLH.YBLH_YRCH_ID=YRCH.YRCH_ID and rownum=1)) RATE_GROSS,
     v8_fnc_valcurr_formating((select sum(YBOS.YBOS_UNITPRICE) from ybos where ybos.ybos_dt=1 and YBOS.YBOS_YBLD_ID=ybld.ybld_id),(select ZCUR.ZCUR_SHORTDESC from zcur,yrch where zcur.zcur_id=YRCH.YRCH_ZCUR_ID and YBLH.YBLH_YRCH_ID=YRCH.YRCH_ID and rownum=1)) RATE_NET,
     YBLD.YBLD_NOOFADULTS YBLD_NOOFADULTS -- MH 23.09.2014 new fileld     
FROM
    YBLH YBLH,
    YBLD YBLD,
    YBST YBST,
    YCAT YCAT
WHERE
    YBLH.YBLH_ID = YBLD.YBLD_YBLH_ID(+) AND
    YBLH.YBLH_YBST_ID = YBST.YBST_ID AND
    YBLD.YBLD_YCAT_ID = YCAT.YCAT_ID(+)
ORDER BY
    YBLH.YBLH_LONGDESC ASC,
    YBLD.YBLD_ID ASC;

CREATE OR REPLACE VIEW V8_REP_MATRIX_WDAT_YCIT
AS 
SELECT
    wdat.wdat_date wdat_date,
    ycit.ycit_id ycit_id,
    ycit.ycit_ycic_id ycic_id,
    ycit.ycit_quantity ycit_quantity,
    NVL((SELECT SUM(yebr.yebr_quantity*YDET.YDET_NOOFROOMS) FROM yebr,ydet,yres WHERE yebr.yebr_ydet_id = ydet.ydet_id AND ydet.ydet_yres_id = yres.yres_id AND yres.yres_resstatus = 1 AND ydet.ydet_date = wdat.wdat_date AND yebr.yebr_ycit_id = ycit.ycit_id),0) quantity_def,
    NVL((SELECT SUM(yebr.yebr_quantity*YDET.YDET_NOOFROOMS ) FROM yebr,ydet,yres WHERE yebr.yebr_ydet_id = ydet.ydet_id AND ydet.ydet_yres_id = yres.yres_id AND yres.yres_resstatus = 0 AND ydet.ydet_date = wdat.wdat_date AND yebr.yebr_ycit_id = ycit.ycit_id),0) quantity_tent    
FROM
    wdat, ycit;

CREATE OR REPLACE VIEW V8_MAILING_RESERVATION_INFOS
AS 
select 
  yres.YRES_ID, 
  yres.YRES_EXPARRTIME Arrival, 
  yres.YRES_EXPDEPTIME Departure, 
  y1.Rate, 
  (decode(yres.YRES_HASSHARE,1,(case when (yres.YRES_HASSHARE=1 and yres.YRES_ID=(select min(ydet_2.YDET_YRES_ID) from ydet,ydet ydet_2 where ydet.YDET_ID=yres.YRES_CURRENT_YDET_ID and ydet.YDET_SHARENUM=ydet_2.YDET_SHARENUM group by ydet_2.YDET_SHARENUM)) then (select avg(ydet_2.YDET_NOOFROOMS) from ydet,ydet ydet_2 where ydet.YDET_ID=yres.YRES_CURRENT_YDET_ID and ydet.YDET_SHARENUM=ydet_2.YDET_SHARENUM group by ydet_2.YDET_SHARENUM) else 0 end),(select ydet.YDET_NOOFROOMS from ydet where ydet.YDET_ID=yres.YRES_CURRENT_YDET_ID))) NOOFROOMS, 
  (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 ycat.YCAT_SHORTDESC from ycat,ydet where ycat.YCAT_ID=ydet.YDET_YCAT_ID and ydet.YDET_ID=yres.YRES_CURRENT_YDET_ID) YCAT_SHORTDESC, 
  (select ycat.YCAT_LONGDESC from ycat,ydet where ycat.YCAT_ID=ydet.YDET_YCAT_ID and ydet.YDET_ID=yres.YRES_CURRENT_YDET_ID) YCAT_LONGDESC, 
  yres.YRES_OPTION YRES_OPTION, 
  (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, 
  yres.YRES_YGCO_ID YRES_YGCO_ID, 
  (select ygco.YGCO_SHORTDESC from ygco where ygco.YGCO_ID=yres.YRES_YGCO_ID) YGCO_SHORTDESC, 
  (select ygco.YGCO_LONGDESC from ygco where ygco.YGCO_ID=yres.YRES_YGCO_ID) YGCO_LONGDESC, 
  (YRES.YRES_EXPDEPTIME) - (YRES.YRES_EXPARRTIME) Nights, 
  (decode((YRES.YRES_EXPARRTIME),V8_sys_fideliodate,1,0)) ARRIVAL_TODAY, 
  (decode((YRES.YRES_EXPARRTIME),V8_sys_fideliodate,(decode(yres.YRES_ACTARRTIME,NULL,0,1)),0)) CHECKED_IN_TODAY, 
  (decode(yres.YRES_ACTARRTIME,NULL,0,1)) CHECKED_IN, 
  (decode((YRES.YRES_EXPDEPTIME),V8_sys_fideliodate,1,0)) DEPARTURE_TODAY, 
  (decode((YRES.YRES_EXPDEPTIME),V8_sys_fideliodate,(decode(yres.YRES_ACTDEPTIME,NULL,0,1)),0)) CHECKED_OUT_TODAY, 
  (decode(yres.YRES_ACTDEPTIME,NULL,0,1)) CHECKED_OUT, 
  (case when (yres.YRES_ACTARRTIME IS NOT NULL and V8_sys_fideliodate between yres.YRES_EXPARRTIME and yres.YRES_EXPDEPTIME and yres.YRES_ACTDEPTIME IS NULL) then 1 else 0 end) INHOUSE, 
  to_char(yres.YRES_EXPARRHOUR,'HH:MI') YRES_EXPARRHOUR, 
  to_char(yres.YRES_EXPDEPHOUR,'HH:MI') YRES_EXPDEPHOUR, 
  yres.YRES_RESSTATUS YRES_RESSTATUS, 
  yres.YRES_NOAVAILREASON YRES_NOAVAILREASON, 
  yres.YRES_WALKIN YRES_WALKIN, 
  yres.YRES_FREETEXT1 YRES_FREETEXT1, 
  yres.YRES_FREETEXT2 YRES_FREETEXT2, 
  nvl(yres.YRES_HASSHARE,0) YRES_HASSHARE, 
  yres.YRES_SHARETYPE YRES_SHARETYPE, 
  (select ydet.YDET_SHARENUM from ydet where ydet.YDET_ID=yres.YRES_CURRENT_YDET_ID) YDET_SHARENUM, 
  (select ydet.YDET_YRMS_ID from ydet where ydet.YDET_ID=yres.YRES_CURRENT_YDET_ID) YDET_YRMS_ID, 
  (select ydet.YDET_YCAT_ID from ydet where ydet.YDET_ID=yres.YRES_CURRENT_YDET_ID) YDET_YCAT_ID, 
  (select ydet.YDET_YCAT_ID_RATE from ydet where ydet.YDET_ID=yres.YRES_CURRENT_YDET_ID) YDET_YCAT_ID_RATE, 
  (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) YCAT_SHORTDESC_RATE, 
  (select yblh.YBLH_ID from yblh,ybld,ydet where yblh.YBLH_ID=ybld.YBLD_ID and ybld.YBLD_ID=ydet.YDET_YBLD_ID and ydet.YDET_ID=yres.YRES_CURRENT_YDET_ID) YBLH_ID, 
  (select yblh.YBLH_SHORTDESC from yblh,ybld,ydet where yblh.YBLH_ID=ybld.YBLD_ID and ybld.YBLD_ID=ydet.YDET_YBLD_ID and ydet.YDET_ID=yres.YRES_CURRENT_YDET_ID) YBLH_SHORTDESC, 
  nvl((select ydet.YDET_RATEAMOUNT from ydet where ydet.YDET_ID=yres.YRES_CURRENT_YDET_ID),0) YDET_RATEAMOUNT, --Daily rate per room + all packages-- 
  nvl((select ydet.YDET_RATEDIFF from ydet where ydet.YDET_ID=yres.YRES_CURRENT_YDET_ID),0) YDET_RATEDIFF, 
  nvl((select ydet.YDET_RATEDISCOUNTPERC from ydet where ydet.YDET_ID=yres.YRES_CURRENT_YDET_ID),0) YDET_RATEDISCOUNTPERC, 
  (select nvl(ydet.YDET_RATEMANUAL,0) from ydet where ydet.YDET_ID=yres.YRES_CURRENT_YDET_ID) YDET_RATEMANUAL, --Manual flagged rate. If add on package is flagged it is automatically added--
  (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) CONF_RATE, --Daily rate per room + all packages--
  (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-- 
  (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  getYDetDiscountedAmount(ydet_rateamount,ydet_ratemanual,ydet_ratediff,ydet_ratediscountperc,ydet_nodiscountpackage,ydet_finalAmount)
          -nvl((SELECT sum(decode(ypal.YPAL_INCLUDED,0,ypal.YPAL_VALUE*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) CONF_RATE_NOADDON, -- Conf_rate excluding add on package--
  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 fixed charged for the first night--
  (select ydet.YDET_YRCH_ID from ydet where ydet.YDET_ID=yres.YRES_CURRENT_YDET_ID) YRCH_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) YRCH_SHORTDESC, 
  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) YRCH_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 YRES_XCMS_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 ygrp.YGRP_NAME from ygrp where ygrp.YGRP_ID=yres.YRES_YGRP_ID) YRES_GROUP_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) YDET_GROUP_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 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) YDET_XCMA_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) XCMA_SHORTDESC, 
  (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) SOURCE_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) SOURCE_SHORTDESC, 
  (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) CHANNEL_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) CHANNEL_SHORTDESC, 
  (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) VIP_SHORTDESC, 
  (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_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, 
  (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_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, 
  (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 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, 
  yres.YRES_VACANT YRES_VACANT, 
  (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) MAX_YCXL_ID, 
  (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)) YCXL_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)) YCXL_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)) YCXL_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)) YCXL_PCTIME 
from 
(select yres.yres_id
       , getYDetDiscountedAmount(ydet_rateamount,ydet_ratemanual,ydet_ratediff,ydet_ratediscountperc,ydet_nodiscountpackage,ydet_finalAmount) 
        -y3.Rate1 Rate 
 from yres, ydet, (select y32.ydet_id, sum(decode(y31.YPAL_INCLUDED,0, nvl(y31.YPAL_VALUE,0),0  )) rate1 from ypal y31, ydet y32 where y32.YDET_ID=y31.YPAL_YDET_ID(+) group by y32.ydet_id) y3 
 where ydet.YDET_ID=yres.YRES_CURRENT_YDET_ID and ydet.ydet_id=y3.ydet_id) y1, 
yres 
where yres.yres_id=y1.yres_id;     

create or replace view v8_mailing_reservation_prof
AS
select yres_id, yres_xcms_id xcms_id
from
  yres
union
select yres_id, ycln_xcms_id
from
  yres, ycln, ydet, xcms
where
  yres_id=ydet_yres_id and ydet_id=ycln_ydet_id and ycln_xcms_id=xcms_id and xcms_type <> 2
union
select 
  yrpl_yres_id, yrpl_xcms_id
from 
  yres, yrpl
where 
  yres_id=yrpl_yres_id;

create or replace view v8_mailing_shis_only_yres
as
  select * from shis
  where shis_yres_id is not null;

create or replace view v8_mailing_reservation_prof_s
as
select shis_id, shis_xcms_id from v8_mailing_shis_only_yres 
union 
select v.shis_id, v.shis_xcms_id from
(        select shis_id, shis_ycln_1_xcms_id shis_xcms_id from v8_mailing_shis_only_yres where nvl(shis_ycln_1_xcms_id, 0) > 0
union select shis_id, shis_ycln_2_xcms_id from v8_mailing_shis_only_yres where nvl(shis_ycln_2_xcms_id, 0) >0
union select shis_id, shis_ycln_3_xcms_id from v8_mailing_shis_only_yres where nvl(shis_ycln_3_xcms_id, 0) >0
union select shis_id, shis_ycln_4_xcms_id from v8_mailing_shis_only_yres where nvl(shis_ycln_4_xcms_id, 0) >0
union select shis_id, shis_ycln_5_xcms_id from v8_mailing_shis_only_yres where nvl(shis_ycln_5_xcms_id, 0) >0
union select shis_id, shis_ycln_6_xcms_id from v8_mailing_shis_only_yres where nvl(shis_ycln_6_xcms_id, 0) >0
union select shis_id, shis_ycln_7_xcms_id from v8_mailing_shis_only_yres where nvl(shis_ycln_7_xcms_id, 0) >0) v,
xcms
where v.shis_xcms_id=xcms.xcms_id and xcms_type <> 2;

CREATE OR REPLACE VIEW V8_MAILING_RESERVATION
AS 
select v.yres_id, v.yres_actarrtime, v.yres_actdeptime, v.yres_exparrtime, v.yres_expdeptime,  v.yres_resstatus, 
       v.Nights, v.FutureNights, v.Stays, v.FutureStays, v.xcms_id yres_xcms_id,
       (select SUM(zpos_grossunitprice*zpos_quantity) Payment from zpos where zpos_yres_id=v.yres_id and zpos_cdt<>5 ) Payment, 
       (select SUM(getYDetDiscountedAmount(ydet_rateamount,ydet_ratemanual,ydet_ratediff,ydet_ratediscountperc,ydet_nodiscountpackage,ydet_finalAmount)) 
	    from ydet where ydet.ydet_yres_id=v.yres_id) rate,
       yras_id, 
       ydet_yrch_id, 
       ydet_xcma_id,
       wphs_id,
       v.firstarrival,
       v.lastarrival,
       v.lastdeparture,
       ydet_ycat_id
from	   
  (select shis.shis_yres_id yres_id, shis.shis_yres_actarrtime yres_actarrtime, shis.shis_yres_actdeptime yres_actdeptime, 
     shis.shis_yres_exparrtime yres_exparrtime, shis.shis_yres_expdeptime yres_expdeptime,  shis.shis_yres_resstatus yres_resstatus, 
     shis.Nights, shis.FutureNights, shis.Stays, shis.FutureStays,
     shisp.shis_xcms_id xcms_id,
     shis.shis_wphs_id wphs_id,
     shis.firstarrival,
     shis.lastarrival,
     shis.lastdeparture,
     shis.shis_ydet_xcma_id ydet_xcma_id,
     shis.shis_ydet_yrch_id ydet_yrch_id,
     shis.shis_source_id yras_id,
     shis.shis_ydet_ycat_id ydet_ycat_id
  from 
    v8_mailing_reservation_shis shis,
    v8_mailing_reservation_prof_s shisp 
  where 
      shis.shis_id=shis.max_shis_id  
      and shis.shis_id=shisp.shis_id
  union all
  SELECT v.yres_id, v.yres_actarrtime, v.yres_actdeptime, v.yres_exparrtime, v.yres_expdeptime, v.yres_resstatus, 
	     decode(nvl(v.yres_actarrtime, NULL), NULL, 0, TRUNC(decode(nvl(v.yres_actdeptime, NULL), NULL, v.yres_expdeptime, v.yres_actdeptime )) - TRUNC(v.yres_actarrtime) ) Nights, 
		 decode(nvl(v.yres_actarrtime, NULL), NULL, (V.YRES_EXPDEPTIME) - TRUNC(v.yres_exparrtime), 0) FutureNights, 
		 decode(nvl(v.yres_actarrtime, NULL), NULL, 0, 1) Stays, 
		 decode(nvl(v.yres_actarrtime, NULL), NULL, 1, 0) FutureStays,
		 pv1.xcms_id,
		 0,
         (select trunc(min(a.yres_actarrtime)) from yres a where a.yres_xcms_id=v.yres_xcms_id) firstarrival,
         (select trunc(max(a.yres_actarrtime)) from yres a where a.yres_xcms_id=v.yres_xcms_id) lastarrival,
         (select trunc(max(a.yres_actdeptime)) from yres a where a.yres_xcms_id=v.yres_xcms_id) lastdeparture,
         (select y.ydet_xcma_id from ydet y where y.ydet_id=v.yres_last_ydet_id) ydet_xcma_id,
         (select y.ydet_yrch_id from ydet y where y.ydet_id=v.yres_last_ydet_id) ydet_yrch_id,
         (select y.yral_yras_id from yres r, ydet rd, yral y where r.yres_last_ydet_id=rd.ydet_id and y.yral_ydet_id=rd.ydet_id and r.yres_id=v.yres_id and rownum=1) yras_id,
         (select y.ydet_ycat_id from ydet y where y.ydet_id=v.yres_last_ydet_id) ydet_ycat_id
  from yres v, wgbs, v8_mailing_reservation_prof pv1
  where	   	    
    (decode(nvl(v.yres_actarrtime, NULL), NULL, v.yres_exparrtime, trunc(v.yres_actarrtime))<=wgbs_datevalue and 
	decode(nvl(yres_actdeptime, NULL), NULL, yres_expdeptime, trunc(yres_actdeptime))>=wgbs_datevalue or
	YRES_EXPARRTIME>wgbs_datevalue) and
	v.yres_id=pv1.yres_id ) v;


CREATE OR REPLACE VIEW V8_CCM_FS_ROOMS_DET
AS 
select
-- Single Reservations 
1 TYP,
v8_editor_res.bookingmaster_id ybom_id,
v8_editor_res.RESERVATION_ID yres_id,
v8_editor_res.Arrival Arrival,
v8_editor_res.departure Departure,
v8_editor_res.NOOFROOMS noofrooms,
v8_editor_res.ADULTNO adultno,
v8_editor_res.ROOMTYPE_ID roomtype_id,
v8_editor_res.ROOMTYPE_DESC roomtype_desc,
v8_editor_res.RESSTATUS resstatus,
v8_editor_res.CONF_RATE conf_rate,
v8_editor_res.CONF_RATE_STAY conf_rate_stay,
v8_editor_res.RATE_NET Conf_Rate_Net,
v8_editor_res.RATE_NET_STAY Conf_Rate_Net_stay,
V8_EDITOR_RES.CALC_PRICE calc_price
from
v8_editor_res
where 
v8_editor_res.resstatus<>'NOT' and
--nvl(v8_editor_res.BLOCK_id,0)=0 and --supress block condistion to show splitted block reservation also on functionsheet 
nvl(v8_editor_res.GROUP_id,0)=0 
--and v8_editor_res.bookingmaster_id=:ybom_id
union all
select
-- Group not splitted  
2 TYP,
v8_editor_gpu.bookingmaster_id ybom_id,
v8_editor_gpu.RESERVATION_ID yres_YBLH_id,
v8_editor_gpu.Arrival Arrival,
v8_editor_gpu.departure Departure,
v8_editor_gpu.NOOFROOMS noofrooms,
v8_editor_gpu.ADULTNO adultno,
v8_editor_gpu.ROOMTYPE_ID roomtype_id,
v8_editor_gpu.ROOMTYPE_DESC roomtype_desc,
v8_editor_gpu.RESSTATUS resstatus,
v8_editor_gpu.CONF_RATE conf_rate,
v8_editor_gpu.CONF_RATE_STAY conf_rate_stay,
v8_editor_gpu.RATE_NET Conf_Rate_Net,
v8_editor_gpu.RATE_NET_STAY Conf_Rate_Net_stay,
V8_EDITOR_GPU.CALC_PRICE calc_price
from
v8_editor_gpu
where 
v8_editor_gpu.resstatus<>'NOT' 
--and v8_editor_gpu.bookingmaster_id=:ybom_id
union all
select
-- Group splitted  
3 TYP,
v8_editor_gps.bookingmaster_id ybom_id,
v8_editor_gps.RESERVATION_ID yres_YBLH_id,
v8_editor_gps.Arrival Arrival,
v8_editor_gps.departure Departure,
v8_editor_gps.NOOFROOMS noofrooms,
v8_editor_gps.ADULTNO adultno,
v8_editor_gps.ROOMTYPE_ID roomtype_id,
v8_editor_gps.ROOMTYPE_DESC roomtype_desc,
v8_editor_gps.RESSTATUS resstatus,
v8_editor_gps.CONF_RATE conf_rate,
v8_editor_gps.CONF_RATE_STAY conf_rate_stay,
v8_editor_gps.RATE_NET Conf_Rate_Net,
v8_editor_gps.RATE_NET_STAY Conf_Rate_Net_stay,
V8_EDITOR_GPS.CALC_PRICE calc_price
from
v8_editor_gps
where 
v8_editor_gps.resstatus<>'NOT';


CREATE OR REPLACE VIEW V8_CCM_FS_BLOCK_DET
AS 
select
-- Blockreservation 
4 TYP,
v8_rep_blocks.yblh_ybom_id ybom_id,
v8_rep_blocks.yblh_startdate Arrival,
v8_rep_blocks.yblh_enddate departure,
v8_rep_blocks.YBLD_ID ybld_id,
v8_rep_blocks.YBLD_DATE YBLD_DATE,
to_char(v8_rep_blocks.YBLD_DATE,'dd.mm.yyyy') F_ybld_date,
nvl(v8_rep_blocks.ybld_actualnoofrooms,0) noofrooms, 
nvl((select v8_ed_block_details.AVAILABLE from v8_ed_block_details where v8_ed_Block_details.YBLD_ID=v8_rep_blocks.YBLD_ID),0) noofrooms_avail,
nvl((select v8_ed_block_details.pickup  from v8_ed_block_details where v8_ed_Block_details.YBLD_ID=v8_rep_blocks.YBLD_ID),0) noofrooms_pickup,
ybld_noofadults adultno,  
v8_rep_blocks.ycat_ID roomtype_id,
v8_rep_blocks.ycat_longdesc roomtype_desc,
V8_REP_BLOCKS.YRCH_SHORTDESC YRCH_SHORTDESC,
v8_rep_blocks.ybst_shortdesc resstatus,
(select sum(YBSS.YBSS_QUANTITY*YBSS.YBSS_UNITPRICE) from YBSS where YBSS.YBSS_YBLD_ID=v8_rep_blocks.YBLD_ID) Conf_rate,
(select sum(YBSS.YBSS_QUANTITY*YBSS.YBSS_UNITPRICE) from YBSS where YBSS.YBSS_YBLD_ID=v8_rep_blocks.YBLD_ID and YBSS.YBSS_dt=1) Conf_rate_net
from
v8_rep_blocks
where 
v8_rep_blocks.YBST_RESSTATUS<>3 and
nvl((select v8_ed_block_details.available  from v8_ed_block_details where v8_ed_Block_details.YBLD_ID=v8_rep_blocks.YBLD_ID),0)<>0 and
nvl(v8_rep_blocks.yblh_ybom_id,0)<>0
union all
select
-- Blockreservation Pickup
5 TYP,
V8_EDITOR_RES.BOOKINGMASTER_ID YBOM_ID,
V8_EDITOR_RES.Arrival Arrival,
V8_EDITOR_RES.Departure departure,
null ybld_id,
Arrival YBLD_DATE,
to_char(Arrival,'dd.mm.yyyy')||' - '||to_char(departure,'dd.mm.yyyy') F_ybld_date,
sum(V8_EDITOR_RES.noofrooms) noofrooms, 
null noofrooms_avail,
null noofrooms_pickup,
V8_EDITOR_RES.adultno adultno,
V8_EDITOR_RES.roomtype_id roomtype_id,
V8_EDITOR_RES.roomtype_desc roomtype_desc,
V8_EDITOR_RES.RATECODE_DESC YRCH_SHORTDESC,
V8_EDITOR_RES.resstatus resstatus,
v8_editor_res.conf_rate Conf_rate,
null Conf_rate_net
from
v8_editor_res
where
V8_EDITOR_RES.RESSTATUS<>'NOT' and V8_EDITOR_RES.BLOCK_ID>0
group by
bookingmaster_id,arrival, departure,adultno,roomtype_id, roomtype_desc,ratecode_desc, resstatus, v8_editor_res.conf_rate;

CREATE OR REPLACE VIEW V8_ED_FOL_ORI_GCHK_ARR_PD
AS 
(SELECT
ZPOS_POSTDATE_S DAT,
to_number(to_char(ZPOS_POSTDATE_S,'J')) DAT_J,
  nvl2((to_char( DEPARTURE,'J')),'-',NULL) MINUSI,
(SELECT ZPOS.ZPOS_CDT FROM ZPOS WHERE ZPOS.ZPOS_ID=ZPOS_ID_S) CDT,
  ZPOS_ID_S ZPOS_ID,
  ZPOS_INTERNALGROUP_ID_S ZPOS_INTERNALGROUP_ID,
  GROSS_S GROSS,
  PAY_S PAY,
  REV_S REV,
  IS_RES,
  IS_FA,
  ORI_RES_FA_ID,
  ARRIVAL,
  DEPARTURE,
  ZFAC_OR_YRES,
  (SELECT ZDCO.ZDCO_NUMERICDESC FROM ZDCO,ZPOS WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID AND ZPOS.ZPOS_ID=ZPOS_ID_S) ZDCO_NUMERICDESC,
  (SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) SPAY,
  (SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)=2 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) SPAY_CL,
  (SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)<>2 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) SPAY_NOCL,
  (SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1, -ZPOS.zpos_unitprice*ZPOS.zpos_quantity, NULL),DECODE(ZPOS.ZPOS_CDT,5,0, ZPOS.zpos_sumupamount))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) SREV,
  (SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1, -ZPOS.zpos_unitprice*ZPOS.zpos_quantity, NULL),DECODE(ZPOS.ZPOS_CDT,5,0, ZPOS.zpos_sumupamount))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S)-(SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) OPENA,
  (DECODE(IS_FA,0,(SELECT V8_REP_NAME.NAME6 FROM YRES,V8_REP_NAME WHERE YRES.YRES_ID=ORI_RES_FA_ID AND YRES.YRES_XCMS_ID=V8_REP_NAME.XCMS_ID),(SELECT V8_REP_NAME.NAME6 FROM ZFAC,V8_REP_NAME WHERE ZFAC.ZFAC_ID=ORI_RES_FA_ID AND ZFAC.ZFAC_XCMS_ID=V8_REP_NAME.XCMS_ID))) REAL_ORIGINATOR_NAME,
  (SELECT ZPCF.ZPCF_CUSTOMORIGINATORNAME FROM ZPCF WHERE ZPCF.ZPCF_ZPOS_ID=ZPOS_ID_S) ORIGINATOR_NAME,
  (DECODE(IS_FA,0,(NVL((SELECT YRMS.YRMS_SHORTDESC FROM YRMS,YDET WHERE YRMS.YRMS_ID=YDET.YDET_YRMS_ID AND YDET.YDET_DATE=ZPOS_POSTDATE_S AND YDET.YDET_YRES_ID=ORI_RES_FA_ID),(SELECT YRMS.YRMS_SHORTDESC FROM YRMS,YDET WHERE YRMS.YRMS_ID=YDET.YDET_YRMS_ID AND YDET.YDET_DATE+1=ZPOS_POSTDATE_S AND YDET.YDET_YRES_ID=ORI_RES_FA_ID))),(NVL((SELECT ZFAN.ZFAN_LONGDESC FROM ZFAN,ZFAC WHERE ZFAN.ZFAN_ID=ZFAC.ZFAC_ZFAN_ID AND ZFAC.ZFAC_ID=ORI_RES_FA_ID),(SELECT ZFAC.ZFAC_AUTONUMBER FROM ZFAC WHERE ZFAC.ZFAC_ID=ORI_RES_FA_ID))))) ORI_RES_FA_DESC,
  TO_CHAR((CASE WHEN (SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1, -ZPOS.zpos_unitprice*ZPOS.zpos_quantity, NULL),DECODE(ZPOS.ZPOS_CDT,5,0, ZPOS.zpos_sumupamount))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S)-(SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S)=0 AND NVL((SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)=2  
  AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S),0)<>0 THEN NVL((SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)=2  AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S),0) ELSE (SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1, -ZPOS.zpos_unitprice*ZPOS.zpos_quantity, NULL),DECODE(ZPOS.ZPOS_CDT,5,0, ZPOS.zpos_sumupamount))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S)-(SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0)
  AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) END)*100,'999999990,09') FSPAY_CL,
decode(INDICATE_ZPOS_ID,
                                   0, NVL( WTRL_TRANSLATE('ZARR',(select ZARR_ID from ZARR where ZARR_ID=(select ZPOS_ZARR_ID from ZPOS where ZPOS.ZPOS_ZARR_ID=ZARR_or_zpos_S and ZPOS.ZPOS_ID=ZPOS_ID_S)),'ZARR_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(select ZPOS_DESCRIPT from zpos where ZPOS.ZPOS_ID=ZPOS_id_S)),
                                   1, decode(nvl((select ZORG.ZORG_ROLE from zorg where ZORG.ZORG_ZPOS_ID=ZPOS_ID_S),0),
                                                         0,(select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS.ZPOS_ID=ZARR_OR_ZPOS_S),
                                                         1,decode(nvl((select YRCH.YRCH_FOLIOTEXT from YRCH where YRCH.YRCH_ID=(select S.ZORG_TABLE_ID from ZORG s where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S)),0),'0',decode((select ZPOS.ZPOS_ID from ZPOS where ZPOS.ZPOS_ID=ZARR_OR_ZPOS_S),(select yrch_LONGDESC from yrch where yrch_id=(SELECT S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S)),
                                                         wtrl_translate('YRCH',(select S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S),'YRCH_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZARR_OR_ZPOS_S)),decode((select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZARR_OR_ZPOS_S),(select yrch_foliotext from yrch where YRCH_ID=(select S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S)),wtrl_translate('YRCH',(SELECT S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S),'YRCH_FOLIOTEXT',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(SELECT ZPOS.ZPOS_DESCRIPT from zpos where ZPOS.ZPOS_ID=ZARR_OR_ZPOS_S))),
                                                         2,decode((select ZPOS.zpos_descript from ZPOS where ZPOS_ID=ZARR_OR_ZPOS_S),(select ZART.ZART_LONGDESC from ZART where ZART.ZART_ID=(select ZORG_TABLE_ID from ZORG where ZORG.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S)),wtrl_translate('ZART',(SELECT ZORG.ZORG_TABLE_ID from ZORG where ZORG.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S),'ZART_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(SELECT ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZARR_OR_ZPOS_S)),
                                                         3,decode(nvl((select YPAC.YPAC_FOLIOTEXT from YPAC where YPAC.YPAC_ID=(select S.ZORG_TABLE_ID from ZORG s where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S)),0),'0',decode((select ZPOS.ZPOS_ID from ZPOS where ZPOS.ZPOS_ID=ZARR_OR_ZPOS_S),(select YPAC_LONGDESC from YPAC where YPAC_ID=(SELECT S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S)),
                                                         wtrl_translate('YPAC',(select S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S),'YPAC_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZARR_OR_ZPOS_S)),decode((select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZARR_OR_ZPOS_S),(select YPAC_FOLIOTEXT from YPAC where YPAC_ID=(select S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S)),wtrl_translate('YPAC',(SELECT S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S),'YPAC_FOLIOTEXT',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(SELECT ZPOS.ZPOS_DESCRIPT from zpos where ZPOS.ZPOS_ID=ZARR_OR_ZPOS_S))),
                                                        4,decode(nvl((select ZDCO.ZDCO_FOLIOTEXT from ZDCO where ZDCO.ZDCO_ID=(select S.ZORG_TABLE_ID from ZORG s where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S)),0),'0',decode((select ZPOS.ZPOS_ID from ZPOS where ZPOS.ZPOS_ID=ZARR_OR_ZPOS_S),(select ZDCO_LONGDESC from ZDCO where ZDCO_ID=(SELECT S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S)),
                                                         wtrl_translate('ZDCO',(select S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S),'ZDCO_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZARR_OR_ZPOS_S)),decode((select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZARR_OR_ZPOS_S),(select ZDCO_LONGDESC from ZDCO where ZDCO_ID=(select S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S)),wtrl_translate('ZDCO',(SELECT S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S),'ZDCO_FOLIOTEXT',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(SELECT ZPOS.ZPOS_DESCRIPT from zpos where ZPOS.ZPOS_ID=ZARR_OR_ZPOS_S))))
                                   ) DDESC,
  ZCHK_OR_ZPOS,
  ZARR_OR_ZPOS_S ZARR_OR_ZPOS, 
  INDICATE_ZPOS_ID,
  GUEST_CHECK_NUM, 
  decode(INDICATE_ZPOS_ID,1,(case when (select count( distinct Z2.ZPOS_ZTCD_ID) from zpos Z2 where Z2.ZPOS_INTERNALSPLIT_ZPOS_ID=ZARR_OR_ZPOS_S and Z2.ZPOS_ZTCD_ID is not null)>1 then ' ' else (wtrl_translate('ZTXC',(select ZTXC.ZTXC_ID from ZTXC, ZTCD where ZTXC_ID=ZTCD.ZTCD_ZTXC_ID and ZTCD.ZTCD_ID=(select Z2.ZPOS_ZTCD_ID from zpos Z2 where Z2.ZPOS_INTERNALSPLIT_ZPOS_ID=ZARR_OR_ZPOS_S and Z2.ZPOS_ZTCD_ID is not null and rownum=1)), 'ZTXC_LONGDESC', (SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=(SELECT ZWIN.ZWIN_INVOICE_TO_XCMS_ID FROM ZWIN WHERE ZWIN.ZWIN_ID=ZWIN_ID_S)))) end),(case when (select count(distinct Z3.ZPOS_ZTCD_ID) from zpos Z3 where Z3.ZPOS_ZARR_ID=ZARR_OR_ZPOS_S and Z3.ZPOS_POSTDATE=ZPOS_POSTDATE_S and Z3.ZPOS_ZWIN_ID=ZWIN_ID_S)>1 then ' '  else (wtrl_translate('ZTXC',(select ZTXC.ZTXC_ID from ztxc, ztcd where ZTXC.ZTXC_ID=ZTCD.ZTCD_ZTXC_ID and ZTCD.ZTCD_ID=(select Z4.ZPOS_ZTCD_ID from zpos Z3, zpos Z4 where Z3.ZPOS_ZARR_ID=ZARR_OR_ZPOS_S and Z3.ZPOS_TAXLINK_ID=Z4.ZPOS_TAXLINK_ID and Z3.ZPOS_POSTDATE=zpos_postdate_s and Z3.ZPOS_ZWIN_ID=Zwin_id_s and Z4.ZPOS_ZTCD_ID is not null and rownum=1)), 'ZTXC_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=(SELECT ZWIN.ZWIN_INVOICE_TO_XCMS_ID FROM ZWIN WHERE ZWIN.ZWIN_ID=ZWIN_ID_S)))) end)) TAXCODE_DESC, 
  decode(INDICATE_ZPOS_ID,1,(case when (select count( distinct Z2.ZPOS_ZTCD_ID) from zpos Z2 where Z2.ZPOS_INTERNALSPLIT_ZPOS_ID=ZARR_OR_ZPOS_S and Z2.ZPOS_ZTCD_ID is not null)>1 then 1 else 0 end ),(case when (select count( distinct Z3.ZPOS_ZTCD_ID) from zpos Z3 where Z3.ZPOS_ZARR_ID=ZARR_OR_ZPOS_S and Z3.ZPOS_POSTDATE=ZPOS_POSTDATE_S and Z3.ZPOS_ZWIN_ID=ZWIN_ID_S)>1 then 1 else 0 end)) MULTI_TAXCODE_EXISTS, 
  ZWIN_ID_S ZWIN_ID
FROM
(
SELECT
  ntd.ZPOS_POSTDATE ZPOS_POSTDATE_S,
  MIN(ntd.ZPOS_ID) ZPOS_ID_S,
  MIN(NTD.ZPOS_INTERNALGROUP_ID) ZPOS_INTERNALGROUP_ID_S,
  1 IS_RES,
  0 IS_FA,
  ntd.ZPOS_ORIGINATED_YRES_ID ORI_RES_FA_ID,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1,(-ntd.zpos_unitprice*ntd.zpos_quantity),-(ntd.zpos_unitprice*ntd.zpos_quantity)),DECODE(ntd.ZPOS_CDT,5,0,ntd.zpos_sumupamount))) GROSS_S,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1,NULL,(ntd.zpos_unitprice*ntd.zpos_quantity)), NULL)) PAY_S,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1, -ntd.zpos_unitprice*ntd.zpos_quantity, NULL),DECODE(ntd.ZPOS_CDT,5,0, ntd.zpos_sumupamount))) REV_S,
  ZWIN.ZWIN_ID ZWIN_ID_S,
  ZWIN.ZWIN_INVOICE_TO_XCMS_ID INVOICE_S,
  (SELECT YRES.YRES_EXPARRTIME FROM YRES WHERE YRES.YRES_ID=ntd.ZPOS_ORIGINATED_YRES_ID) ARRIVAL,
  (SELECT YRES.YRES_EXPDEPTIME FROM YRES WHERE YRES.YRES_ID=ntd.ZPOS_ORIGINATED_YRES_ID) DEPARTURE,
  DECODE(NVL(ntd.ZPOS_ZARR_ID,0),0,1,0) INDICATE_ZPOS_ID,
  ntd.ZPOS_POSCHECK_NR GUEST_CHECK_NUM,
  null ZCHK_OR_ZPOS,
  DECODE(NVL(ntd.ZPOS_ZARR_ID,0),0,ntd.ZPOS_ID,ntd.ZPOS_ZARR_ID) ZARR_OR_ZPOS_S,
  DECODE(NVL(ntd.ZPOS_ORIGINATED_YRES_ID,0),0,1,0) ZFAC_OR_YRES
FROM
  ZPOS ntd,
  ZWIN ZWIN,
  ZDCO,
  ZORG
WHERE
ZORG.ZORG_ZPOS_ID(+)=NTD.ZPOS_ID and
  (zpos_zall_id IS NULL OR zpos_zall_id=0) AND
  ntd.zpos_id=ntd.zpos_internalsplit_zpos_id AND
  zdco_id=ntd.zpos_zdco_id AND
  ntd.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID(+) AND
  NVL(ntd.ZPOS_HIDDEN,0)=0
  AND NVL(ntd.ZPOS_NOREPRINT,0)<>1
  AND nvl(NTD.ZPOS_ORIGINATED_ZFAC_ID,0)=0
--AND ZWIN.ZWIN_ID=:WINID
GROUP BY ZWIN.ZWIN_ID,   ZWIN.ZWIN_INVOICE_TO_XCMS_ID, (ntd.ZPOS_POSCHECK_NR,' '), ntd.ZPOS_POSTDATE,ntd.ZPOS_ORIGINATED_YRES_ID, DECODE(NVL(ntd.ZPOS_ZARR_ID,0),0,ntd.ZPOS_ID,ntd.ZPOS_ZARR_ID), DECODE(NVL(ntd.ZPOS_ORIGINATED_YRES_ID,0),0,1,0), DECODE(NVL(ntd.ZPOS_ZARR_ID,0),0,1,0)--,decode(nvl(ntd.ZPOS_ZARR_ID,0),0,ntd.ZPOS_ID,ntd.ZPOS_ID)-- ntd.ZPOS_ID--,zdco.ZDCO_ID--, ntd.ZPOS_ID, ntd.ZPOS_ZARR_ID)
UNION ALL
SELECT
  ntd.ZPOS_POSTDATE ZPOS_POSTDATE_S,
  MIN(ntd.ZPOS_ID) ZPOS_ID_S,
  MIN(NTD.ZPOS_INTERNALGROUP_ID) ZPOS_INTERNALGROUP_ID_S,
  0 IS_RES,
  1 IS_FA,
  ntd.ZPOS_ORIGINATED_ZFAC_ID ORI_RES_FA_ID,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1,(-ntd.zpos_unitprice*ntd.zpos_quantity),-(ntd.zpos_unitprice*ntd.zpos_quantity)),DECODE(ntd.ZPOS_CDT,5,0,ntd.zpos_sumupamount))) GROSS_S,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1,NULL,(ntd.zpos_unitprice*ntd.zpos_quantity)), NULL)) PAY_S,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1, -ntd.zpos_unitprice*ntd.zpos_quantity, NULL),DECODE(ntd.ZPOS_CDT,5,0, ntd.zpos_sumupamount))) REV_S,
  ZWIN.ZWIN_ID ZWIN_ID_S,
    ZWIN.ZWIN_INVOICE_TO_XCMS_ID INVOICE_S,
  (SELECT ZFAC.ZFAC_EXPVALIDFROM FROM ZFAC WHERE ZFAC.ZFAC_ID=ntd.ZPOS_ORIGINATED_ZFAC_ID) ARRIVAL,
  (SELECT ZFAC.ZFAC_EXPVALIDUNTIL FROM ZFAC WHERE ZFAC.ZFAC_ID=ntd.ZPOS_ORIGINATED_ZFAC_ID) DEPARTURE,
  DECODE(NVL(ntd.ZPOS_ZARR_ID,0),0,1,0) INDICATE_ZPOS_ID,
  ntd.ZPOS_POSCHECK_NR GUEST_CHECK_NUM,
  null ZCHK_OR_ZPOS,
  DECODE(NVL(ntd.ZPOS_ZARR_ID,0),0,ntd.ZPOS_ID,ntd.ZPOS_ZARR_ID) ZARR_OR_ZPOS_S,
  DECODE(NVL(ntd.ZPOS_ORIGINATED_YRES_ID,0),0,1,0) ZFAC_OR_YRES
FROM
  ZPOS ntd,
  ZWIN ZWIN,
  ZDCO
WHERE
  (zpos_zall_id IS NULL OR zpos_zall_id=0) AND
  ntd.zpos_id=ntd.zpos_internalsplit_zpos_id AND
  zdco_id=ntd.zpos_zdco_id AND
  ntd.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID(+) AND
  NVL(ntd.ZPOS_HIDDEN,0)=0
  AND NVL(ntd.ZPOS_NOREPRINT,0)<>1
  AND nvl(NTD.ZPOS_ORIGINATED_ZFAC_ID,0)<>0
  --AND ZWIN.ZWIN_ID=:WINID
GROUP BY ZWIN.ZWIN_ID,  ZWIN.ZWIN_INVOICE_TO_XCMS_ID, ntd.ZPOS_POSCHECK_NR,  ntd.ZPOS_POSTDATE,ntd.ZPOS_ORIGINATED_ZFAC_ID, DECODE(NVL(ntd.ZPOS_ZARR_ID,0),0,ntd.ZPOS_ID,ntd.ZPOS_ZARR_ID),DECODE(NVL(ntd.ZPOS_ORIGINATED_YRES_ID,0),0,1,0), DECODE(NVL(ntd.ZPOS_ZARR_ID,0),0,1,0)--,decode(nvl(ntd.ZPOS_ZARR_ID,0),0,ntd.ZPOS_ID,ntd.ZPOS_ID)-- ntd.ZPOS_ID--,zdco.ZDCO_ID--, ntd.ZPOS_ID, ntd.ZPOS_ZARR_ID
) WHERE  GUEST_CHECK_NUM is null
union all
SELECT
  ZPOS_POSTDATE_S DAT,
  to_number(to_char(ZPOS_POSTDATE_S,'J')) DAT_J,
  nvl2((to_char( DEPARTURE,'J')),'-',NULL) MINUSI,
  (SELECT ZPOS.ZPOS_CDT FROM ZPOS WHERE ZPOS.ZPOS_ID=ZPOS_ID_S) CDT,
  ZPOS_ID_S ZPOS_ID,
  ZPOS_INTERNALGROUP_ID_S ZPOS_INTERNALGROUP_ID,
  GROSS_S GROSS,
  PAY_S PAY,
  REV_S REV,
  IS_RES,
  IS_FA,
  ORI_RES_FA_ID,
  ARRIVAL,
  DEPARTURE,
  ZFAC_OR_YRES,
  (SELECT ZDCO.ZDCO_NUMERICDESC FROM ZDCO,ZPOS WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID AND ZPOS.ZPOS_ID=ZPOS_ID_S) ZDCO_NUMERICDESC,
  (SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) SPAY,
  (SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)=2 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) SPAY_CL,
  (SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)<>2 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) SPAY_NOCL,
  (SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1, -ZPOS.zpos_unitprice*ZPOS.zpos_quantity, NULL),DECODE(ZPOS.ZPOS_CDT,5,0, ZPOS.zpos_sumupamount))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) SREV,
  (SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1, -ZPOS.zpos_unitprice*ZPOS.zpos_quantity, NULL),DECODE(ZPOS.ZPOS_CDT,5,0, ZPOS.zpos_sumupamount))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S)-(SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) OPENA,
    (DECODE(IS_FA,0,(SELECT V8_REP_NAME.NAME6 FROM YRES,V8_REP_NAME WHERE YRES.YRES_ID=ORI_RES_FA_ID AND YRES.YRES_XCMS_ID=V8_REP_NAME.XCMS_ID),(SELECT V8_REP_NAME.NAME6 FROM ZFAC,V8_REP_NAME WHERE ZFAC.ZFAC_ID=ORI_RES_FA_ID AND ZFAC.ZFAC_XCMS_ID=V8_REP_NAME.XCMS_ID))) REAL_ORIGINATOR_NAME,
  (SELECT ZPCF.ZPCF_CUSTOMORIGINATORNAME FROM ZPCF WHERE ZPCF.ZPCF_ZPOS_ID=ZPOS_ID_S) ORIGINATOR_NAME,
  (DECODE(IS_FA,0,(NVL((SELECT YRMS.YRMS_SHORTDESC FROM YRMS,YDET WHERE YRMS.YRMS_ID=YDET.YDET_YRMS_ID AND YDET.YDET_DATE=ZPOS_POSTDATE_S AND YDET.YDET_YRES_ID=ORI_RES_FA_ID),(SELECT YRMS.YRMS_SHORTDESC FROM YRMS,YDET WHERE YRMS.YRMS_ID=YDET.YDET_YRMS_ID AND YDET.YDET_DATE+1=ZPOS_POSTDATE_S AND YDET.YDET_YRES_ID=ORI_RES_FA_ID))),(NVL((SELECT ZFAN.ZFAN_LONGDESC FROM ZFAN,ZFAC WHERE ZFAN.ZFAN_ID=ZFAC.ZFAC_ZFAN_ID AND ZFAC.ZFAC_ID=ORI_RES_FA_ID),(SELECT ZFAC.ZFAC_AUTONUMBER FROM ZFAC WHERE ZFAC.ZFAC_ID=ORI_RES_FA_ID))))) ORI_RES_FA_DESC,
  TO_CHAR((CASE WHEN (SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1, -ZPOS.zpos_unitprice*ZPOS.zpos_quantity, NULL),DECODE(ZPOS.ZPOS_CDT,5,0, ZPOS.zpos_sumupamount))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S)-(SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S)=0 AND NVL((SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)=2  
  AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S),0)<>0 THEN NVL((SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)=2  AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S),0) ELSE (SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1, -ZPOS.zpos_unitprice*ZPOS.zpos_quantity, NULL),DECODE(ZPOS.ZPOS_CDT,5,0, ZPOS.zpos_sumupamount))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S)-(SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0)
  AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) END)*100,'999999990,09') FSPAY_CL,
  decode(INDICATE_ZPOS_ID,
                                   0, NVL( WTRL_TRANSLATE('ZDCO',(select ZDCO_ID from ZDCO where ZDCO_ID=(select ZPOS_ZDCO_ID from ZPOS where ZPOS_ID=ZPOS_ID_S)),'ZDCO_FOLIOTEXT',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(select ZPOS_DESCRIPT from zpos where ZPOS.ZPOS_ID=ZPOS_id_S)),
                                   1, decode(nvl((select ZORG.ZORG_ROLE from zorg where ZORG.ZORG_ZPOS_ID=ZPOS_ID_S),0),
                                                         0,(select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS.ZPOS_ID=ZCHK_OR_ZPOS_S),
                                                         1,decode(nvl((select YRCH.YRCH_FOLIOTEXT from YRCH where YRCH.YRCH_ID=(select S.ZORG_TABLE_ID from ZORG s where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S)),0),'0',decode((select ZPOS.ZPOS_ID from ZPOS where ZPOS.ZPOS_ID=ZCHK_OR_ZPOS_S),(select yrch_LONGDESC from yrch where yrch_id=(SELECT S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S)),
                                                         wtrl_translate('YRCH',(select S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S),'YRCH_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZCHK_OR_ZPOS_S)),decode((select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZCHK_OR_ZPOS_S),(select yrch_foliotext from yrch where YRCH_ID=(select S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S)),wtrl_translate('YRCH',(SELECT S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S),'YRCH_FOLIOTEXT',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(SELECT ZPOS.ZPOS_DESCRIPT from zpos where ZPOS.ZPOS_ID=ZCHK_OR_ZPOS_S))),
                                                         2,decode((select ZPOS.zpos_descript from ZPOS where ZPOS_ID=ZCHK_OR_ZPOS_S),(select ZART.ZART_LONGDESC from ZART where ZART.ZART_ID=(select ZORG_TABLE_ID from ZORG where ZORG.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S)),wtrl_translate('ZART',(SELECT ZORG.ZORG_TABLE_ID from ZORG where ZORG.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S),'ZART_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(SELECT ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZCHK_OR_ZPOS_S)),
                                                         3,decode(nvl((select YPAC.YPAC_FOLIOTEXT from YPAC where YPAC.YPAC_ID=(select S.ZORG_TABLE_ID from ZORG s where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S)),0),'0',decode((select ZPOS.ZPOS_ID from ZPOS where ZPOS.ZPOS_ID=ZCHK_OR_ZPOS_S),(select YPAC_LONGDESC from YPAC where YPAC_ID=(SELECT S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S)),
                                                         wtrl_translate('YPAC',(select S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S),'YPAC_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZCHK_OR_ZPOS_S)),decode((select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZCHK_OR_ZPOS_S),(select YPAC_FOLIOTEXT from YPAC where YPAC_ID=(select S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S)),wtrl_translate('YPAC',(SELECT S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S),'YPAC_FOLIOTEXT',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(SELECT ZPOS.ZPOS_DESCRIPT from zpos where ZPOS.ZPOS_ID=ZCHK_OR_ZPOS_S))),
                                                         4,decode(nvl((select ZDCO.ZDCO_FOLIOTEXT from ZDCO where ZDCO.ZDCO_ID=(select S.ZORG_TABLE_ID from ZORG s where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S)),0),'0',decode((select ZPOS.ZPOS_ID from ZPOS where ZPOS.ZPOS_ID=ZCHK_OR_ZPOS_S),(select ZDCO_LONGDESC from ZDCO where ZDCO_ID=(SELECT S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S)),
                                                         wtrl_translate('ZDCO',(select S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S),'ZDCO_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZCHK_OR_ZPOS_S)),decode((select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZCHK_OR_ZPOS_S),(select ZDCO_LONGDESC from ZDCO where ZDCO_ID=(select S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S)),wtrl_translate('ZDCO',(SELECT S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S),'ZDCO_FOLIOTEXT',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(SELECT ZPOS.ZPOS_DESCRIPT from zpos where ZPOS.ZPOS_ID=ZCHK_OR_ZPOS_S))))
          ) DDESC,
ZCHK_OR_ZPOS_S ZCHK_OR_ZPOS, 
  ZARR_OR_ZPOS,
  INDICATE_ZPOS_ID, 
  (DECODE(INDICATE_ZPOS_ID,0,ZCHK_OR_ZPOS_S,to_char(null))) GUEST_CHECK_NUM,
    decode(INDICATE_ZPOS_ID,1,(case when (select count( distinct Z2.ZPOS_ZTCD_ID) from zpos Z2 where Z2.ZPOS_INTERNALSPLIT_ZPOS_ID=ZCHK_OR_ZPOS_S and Z2.ZPOS_ZTCD_ID is not null)>1 then ' ' else (wtrl_translate('ZTXC',(select ZTXC.ZTXC_ID from ZTXC, ZTCD where ZTXC_ID=ZTCD.ZTCD_ZTXC_ID and ZTCD.ZTCD_ID=(select Z2.ZPOS_ZTCD_ID from zpos Z2 where Z2.ZPOS_INTERNALSPLIT_ZPOS_ID=ZCHK_OR_ZPOS_S and Z2.ZPOS_ZTCD_ID is not null)), 'ZTXC_LONGDESC', (SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=(SELECT ZWIN.ZWIN_INVOICE_TO_XCMS_ID FROM ZWIN WHERE ZWIN.ZWIN_ID=ZWIN_ID_S)))) end),(case when (select count(distinct Z3.ZPOS_ZTCD_ID) from zpos Z3 where Z3.ZPOS_POSCHECK_NR=ZCHK_OR_ZPOS_S and Z3.ZPOS_POSTDATE=ZPOS_POSTDATE_S and Z3.ZPOS_ZWIN_ID=ZWIN_ID_S)>1 then ' '  else (wtrl_translate('ZTXC',(select ZTXC.ZTXC_ID from ztxc, ztcd where ZTXC.ZTXC_ID=ZTCD.ZTCD_ZTXC_ID and ZTCD.ZTCD_ID=(select Z4.ZPOS_ZTCD_ID from zpos Z3, zpos Z4 where Z3.ZPOS_POSCHECK_NR=ZCHK_OR_ZPOS_S and Z3.ZPOS_TAXLINK_ID=Z4.ZPOS_TAXLINK_ID and Z3.ZPOS_POSTDATE=zpos_postdate_s and Z3.ZPOS_ZWIN_ID=Zwin_id_s and Z4.ZPOS_ZTCD_ID is not null and rownum=1)), 'ZTXC_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=(SELECT ZWIN.ZWIN_INVOICE_TO_XCMS_ID FROM ZWIN WHERE ZWIN.ZWIN_ID=ZWIN_ID_S)))) end)) TAXCODE_DESC, 
decode(INDICATE_ZPOS_ID,1,(case when (select count( distinct Z2.ZPOS_ZTCD_ID) from zpos Z2 where Z2.ZPOS_INTERNALSPLIT_ZPOS_ID=ZCHK_OR_ZPOS_S and Z2.ZPOS_ZTCD_ID is not null)>1 then 1 else 0 end ),(case when (select count( distinct Z3.ZPOS_ZTCD_ID) from zpos Z3, ZPOS Z5 where Z5.ZPOS_POSCHECK_NR=ZCHK_OR_ZPOS_S and Z3.ZPOS_ZWIN_ID=ZWIN_ID_S )>1 then 1 else 0 end)) MULTI_TAXCODE_EXISTS,
  ZWIN_ID_S ZWIN_ID
FROM
(
SELECT
  MIN(ntd.ZPOS_POSTDATE) ZPOS_POSTDATE_S,
  MIN(ntd.ZPOS_ID) ZPOS_ID_S,
  MIN(NTD.ZPOS_INTERNALGROUP_ID) ZPOS_INTERNALGROUP_ID_S,
  1 IS_RES,
  0 IS_FA,
  ntd.ZPOS_ORIGINATED_YRES_ID ORI_RES_FA_ID,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1,(-ntd.zpos_unitprice*ntd.zpos_quantity),-(ntd.zpos_unitprice*ntd.zpos_quantity)),DECODE(ntd.ZPOS_CDT,5,0,ntd.zpos_sumupamount))) GROSS_S,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1,NULL,(ntd.zpos_unitprice*ntd.zpos_quantity)), NULL)) PAY_S,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1, -ntd.zpos_unitprice*ntd.zpos_quantity, NULL),DECODE(ntd.ZPOS_CDT,5,0, ntd.zpos_sumupamount))) REV_S,
  ZWIN.ZWIN_ID ZWIN_ID_S,
  ZWIN.ZWIN_INVOICE_TO_XCMS_ID INVOICE_S,
  (SELECT TRUNC(YRES.YRES_EXPARRTIME) FROM YRES WHERE YRES.YRES_ID=NTD.ZPOS_ORIGINATED_YRES_ID) ARRIVAL,
(SELECT trunc(YRES.YRES_EXPDEPTIME) FROM YRES WHERE YRES.YRES_ID=NTD.ZPOS_ORIGINATED_YRES_ID) DEPARTURE,
    DECODE(NVL(ntd.ZPOS_POSCHECK_NR,' '),' ',1,0) INDICATE_ZPOS_ID,
  to_char(DECODE(NVL(ntd.ZPOS_POSCHECK_NR,' '),' ',to_char(ntd.ZPOS_ID),ntd.ZPOS_POSCHECK_NR)) ZCHK_OR_ZPOS_S,
to_number(null) ZARR_OR_ZPOS,
  DECODE(NVL(ntd.ZPOS_ORIGINATED_YRES_ID,0),0,1,0) ZFAC_OR_YRES
FROM
  ZPOS ntd,
  ZWIN ZWIN,
  ZDCO,
  ZORG
WHERE
ZORG.ZORG_ZPOS_ID(+)=NTD.ZPOS_ID and
  (zpos_zall_id IS NULL OR zpos_zall_id=0) AND
  ntd.zpos_id=ntd.zpos_internalsplit_zpos_id AND
  zdco_id=ntd.zpos_zdco_id AND
  ntd.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID(+) AND
  NVL(ntd.ZPOS_HIDDEN,0)=0
  AND NVL(ntd.ZPOS_NOREPRINT,0)<>1
  AND nvl(NTD.ZPOS_ORIGINATED_ZFAC_ID,0)=0
--AND ZWIN.ZWIN_ID=:WINID
GROUP BY ZWIN.ZWIN_ID,  ZWIN.ZWIN_INVOICE_TO_XCMS_ID, ntd.ZPOS_ORIGINATED_YRES_ID, to_char(DECODE(NVL(ntd.ZPOS_POSCHECK_NR,' '),' ',to_char(ntd.ZPOS_ID),ntd.ZPOS_POSCHECK_NR)), DECODE(NVL(ntd.ZPOS_ORIGINATED_YRES_ID,0),0,1,0),DECODE(NVL(ntd.ZPOS_POSCHECK_NR,' '),' ',1,0) --,decode(nvl(ntd.ZPOS_ZARR_ID,0),0,ntd.ZPOS_ID,ntd.ZPOS_ID)-- ntd.ZPOS_ID--,zdco.ZDCO_ID--, ntd.ZPOS_ID, ntd.ZPOS_ZARR_ID
UNION ALL
SELECT
 MIN(ntd.ZPOS_POSTDATE) ZPOS_POSTDATE_S,
  MIN(ntd.ZPOS_ID) ZPOS_ID_S,
MIN(NTD.ZPOS_INTERNALGROUP_ID) ZPOS_INTERNALGROUP_ID_S,
  0 IS_RES,
  1 IS_FA,
  ntd.ZPOS_ORIGINATED_ZFAC_ID ORI_RES_FA_ID,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1,(-ntd.zpos_unitprice*ntd.zpos_quantity),-(ntd.zpos_unitprice*ntd.zpos_quantity)),DECODE(ntd.ZPOS_CDT,5,0,ntd.zpos_sumupamount))) GROSS_S,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1,NULL,(ntd.zpos_unitprice*ntd.zpos_quantity)), NULL)) PAY_S,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1, -ntd.zpos_unitprice*ntd.zpos_quantity, NULL),DECODE(ntd.ZPOS_CDT,5,0, ntd.zpos_sumupamount))) REV_S,
  ZWIN.ZWIN_ID ZWIN_ID_S,
  ZWIN.ZWIN_INVOICE_TO_XCMS_ID INVOICE_S,
  NULL ARRIVAL,
  NULL DEPARTURE,
  DECODE(NVL(ntd.ZPOS_POSCHECK_NR,' '),' ',1,0) INDICATE_ZPOS_ID,
  to_char(DECODE(NVL(ntd.ZPOS_POSCHECK_NR,' '),' ',to_char(ntd.ZPOS_ID),ntd.ZPOS_POSCHECK_NR)) ZCHK_OR_ZPOS_S,
to_number(null) ZARR_OR_ZPOS,
  DECODE(NVL(ntd.ZPOS_ORIGINATED_YRES_ID,0),0,1,0) ZFAC_OR_YRES
FROM
  ZPOS ntd,
  ZWIN ZWIN,
  ZDCO
WHERE
  (zpos_zall_id IS NULL OR zpos_zall_id=0) AND
  ntd.zpos_id=ntd.zpos_internalsplit_zpos_id AND
  zdco_id=ntd.zpos_zdco_id AND
  ntd.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID(+) AND
  NVL(ntd.ZPOS_HIDDEN,0)=0
  AND NVL(ntd.ZPOS_NOREPRINT,0)<>1
  AND nvl(NTD.ZPOS_ORIGINATED_ZFAC_ID,0)<>0
--AND ZWIN.ZWIN_ID=:WINID
GROUP BY ZWIN.ZWIN_ID,   ZWIN.ZWIN_INVOICE_TO_XCMS_ID, ntd.ZPOS_ORIGINATED_ZFAC_ID, to_char(DECODE(NVL(ntd.ZPOS_POSCHECK_NR,' '),' ',to_char(ntd.ZPOS_ID),ntd.ZPOS_POSCHECK_NR)), DECODE(NVL(ntd.ZPOS_ORIGINATED_YRES_ID,0),0,1,0),DECODE(NVL(ntd.ZPOS_POSCHECK_NR,' '),' ',1,0) --,decode(nvl(ntd.ZPOS_ZARR_ID,0),0,ntd.ZPOS_ID,ntd.ZPOS_ID)-- ntd.ZPOS_ID--,zdco.ZDCO_ID--, ntd.ZPOS_ID, ntd.ZPOS_ZARR_ID
)
where 
(DECODE(INDICATE_ZPOS_ID,0,ZCHK_OR_ZPOS_S,to_char(null))) is not null
)order by CDT ASC, DAT;

CREATE OR REPLACE VIEW V8_ED_FOL_ORI_GCHK_ARR_PS
AS 
(SELECT
  ZPOS_POSTDATE_S DAT,
  to_number(to_char(ZPOS_POSTDATE_S,'J')) DAT_J,
  nvl2((to_char( DEPARTURE,'J')),'-',NULL) MINUSI,
  (SELECT ZPOS.ZPOS_CDT FROM ZPOS WHERE ZPOS.ZPOS_ID=ZPOS_ID_S) CDT,
  ZPOS_ID_S ZPOS_ID,
  ZPOS_INTERNALGROUP_ID_S ZPOS_INTERNALGROUP_ID,
   GROSS_S GROSS,
  PAY_S PAY,
  REV_S REV,
  IS_RES,
  IS_FA,
  ORI_RES_FA_ID,
  ARRIVAL,
  DEPARTURE,
  ZFAC_OR_YRES,
  (SELECT ZDCO.ZDCO_NUMERICDESC FROM ZDCO,ZPOS WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID AND ZPOS.ZPOS_ID=ZPOS_ID_S) ZDCO_NUMERICDESC,
  (SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) SPAY,
  (SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)=2 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) SPAY_CL,
  (SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)<>2 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) SPAY_NOCL,
  (SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1, -ZPOS.zpos_unitprice*ZPOS.zpos_quantity, NULL),DECODE(ZPOS.ZPOS_CDT,5,0, ZPOS.zpos_sumupamount))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) SREV,
  (SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1, -ZPOS.zpos_unitprice*ZPOS.zpos_quantity, NULL),DECODE(ZPOS.ZPOS_CDT,5,0, ZPOS.zpos_sumupamount))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S)-(SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) OPENA,
    (DECODE(IS_FA,0,(SELECT V8_REP_NAME.NAME6 FROM YRES,V8_REP_NAME WHERE YRES.YRES_ID=ORI_RES_FA_ID AND YRES.YRES_XCMS_ID=V8_REP_NAME.XCMS_ID),(SELECT V8_REP_NAME.NAME6 FROM ZFAC,V8_REP_NAME WHERE ZFAC.ZFAC_ID=ORI_RES_FA_ID AND ZFAC.ZFAC_XCMS_ID=V8_REP_NAME.XCMS_ID))) REAL_ORIGINATOR_NAME,
  (SELECT ZPCF.ZPCF_CUSTOMORIGINATORNAME FROM ZPCF WHERE ZPCF.ZPCF_ZPOS_ID=ZPOS_ID_S) ORIGINATOR_NAME,
  (DECODE(IS_FA,0,(NVL((SELECT YRMS.YRMS_SHORTDESC FROM YRMS,YDET WHERE YRMS.YRMS_ID=YDET.YDET_YRMS_ID AND YDET.YDET_DATE=ZPOS_POSTDATE_S AND YDET.YDET_YRES_ID=ORI_RES_FA_ID),(SELECT YRMS.YRMS_SHORTDESC FROM YRMS,YDET WHERE YRMS.YRMS_ID=YDET.YDET_YRMS_ID AND YDET.YDET_DATE+1=ZPOS_POSTDATE_S AND YDET.YDET_YRES_ID=ORI_RES_FA_ID))),(NVL((SELECT ZFAN.ZFAN_LONGDESC FROM ZFAN,ZFAC WHERE ZFAN.ZFAN_ID=ZFAC.ZFAC_ZFAN_ID AND ZFAC.ZFAC_ID=ORI_RES_FA_ID),(SELECT ZFAC.ZFAC_AUTONUMBER FROM ZFAC WHERE ZFAC.ZFAC_ID=ORI_RES_FA_ID))))) ORI_RES_FA_DESC,
  TO_CHAR((CASE WHEN (SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1, -ZPOS.zpos_unitprice*ZPOS.zpos_quantity, NULL),DECODE(ZPOS.ZPOS_CDT,5,0, ZPOS.zpos_sumupamount))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S)-(SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S)=0 AND NVL((SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)=2  
  AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S),0)<>0 THEN NVL((SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)=2  AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S),0) ELSE (SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1, -ZPOS.zpos_unitprice*ZPOS.zpos_quantity, NULL),DECODE(ZPOS.ZPOS_CDT,5,0, ZPOS.zpos_sumupamount))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S)-(SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0)
  AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) END)*100,'999999990,09') FSPAY_CL,
decode(INDICATE_ZPOS_ID,
                                   0, NVL( WTRL_TRANSLATE('ZARR',(select ZARR_ID from ZARR where ZARR_ID=(select ZPOS_ZARR_ID from ZPOS where ZPOS.ZPOS_ZARR_ID=ZARR_or_zpos_S and ZPOS.ZPOS_ID=ZPOS_ID_S)),'ZARR_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(select ZPOS_DESCRIPT from zpos where ZPOS.ZPOS_ID=ZPOS_id_S)),
                                   1, decode(nvl((select ZORG.ZORG_ROLE from zorg where ZORG.ZORG_ZPOS_ID=ZPOS_ID_S),0),
                                                         0,(select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS.ZPOS_ID=ZARR_OR_ZPOS_S),
                                                         1,decode(nvl((select YRCH.YRCH_FOLIOTEXT from YRCH where YRCH.YRCH_ID=(select S.ZORG_TABLE_ID from ZORG s where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S)),0),'0',decode((select ZPOS.ZPOS_ID from ZPOS where ZPOS.ZPOS_ID=ZARR_OR_ZPOS_S),(select yrch_LONGDESC from yrch where yrch_id=(SELECT S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S)),
                                                         wtrl_translate('YRCH',(select S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S),'YRCH_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZARR_OR_ZPOS_S)),decode((select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZARR_OR_ZPOS_S),(select yrch_foliotext from yrch where YRCH_ID=(select S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S)),wtrl_translate('YRCH',(SELECT S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S),'YRCH_FOLIOTEXT',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(SELECT ZPOS.ZPOS_DESCRIPT from zpos where ZPOS.ZPOS_ID=ZARR_OR_ZPOS_S))),
                                                         2,decode((select ZPOS.zpos_descript from ZPOS where ZPOS_ID=ZARR_OR_ZPOS_S),(select ZART.ZART_LONGDESC from ZART where ZART.ZART_ID=(select ZORG_TABLE_ID from ZORG where ZORG.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S)),wtrl_translate('ZART',(SELECT ZORG.ZORG_TABLE_ID from ZORG where ZORG.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S),'ZART_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(SELECT ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZARR_OR_ZPOS_S)),
                                                         3,decode(nvl((select YPAC.YPAC_FOLIOTEXT from YPAC where YPAC.YPAC_ID=(select S.ZORG_TABLE_ID from ZORG s where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S)),0),'0',decode((select ZPOS.ZPOS_ID from ZPOS where ZPOS.ZPOS_ID=ZARR_OR_ZPOS_S),(select YPAC_LONGDESC from YPAC where YPAC_ID=(SELECT S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S)),
                                                         wtrl_translate('YPAC',(select S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S),'YPAC_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZARR_OR_ZPOS_S)),decode((select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZARR_OR_ZPOS_S),(select YPAC_FOLIOTEXT from YPAC where YPAC_ID=(select S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S)),wtrl_translate('YPAC',(SELECT S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S),'YPAC_FOLIOTEXT',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(SELECT ZPOS.ZPOS_DESCRIPT from zpos where ZPOS.ZPOS_ID=ZARR_OR_ZPOS_S))),
                                                        4,decode(nvl((select ZDCO.ZDCO_FOLIOTEXT from ZDCO where ZDCO.ZDCO_ID=(select S.ZORG_TABLE_ID from ZORG s where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S)),0),'0',decode((select ZPOS.ZPOS_ID from ZPOS where ZPOS.ZPOS_ID=ZARR_OR_ZPOS_S),(select ZDCO_LONGDESC from ZDCO where ZDCO_ID=(SELECT S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S)),
                                                         wtrl_translate('ZDCO',(select S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S),'ZDCO_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZARR_OR_ZPOS_S)),decode((select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZARR_OR_ZPOS_S),(select ZDCO_LONGDESC from ZDCO where ZDCO_ID=(select S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S)),wtrl_translate('ZDCO',(SELECT S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZARR_OR_ZPOS_S),'ZDCO_FOLIOTEXT',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(SELECT ZPOS.ZPOS_DESCRIPT from zpos where ZPOS.ZPOS_ID=ZARR_OR_ZPOS_S))))
                                   ) DDESC,
  ZCHK_OR_ZPOS,
  ZARR_OR_ZPOS_S ZARR_OR_ZPOS, 
  INDICATE_ZPOS_ID,
  GUEST_CHECK_NUM, 
 -- decode(INDICATE_ZPOS_ID,1,(case when (select count( distinct Z2.ZPOS_ZTCD_ID) from zpos Z2 where Z2.ZPOS_INTERNALSPLIT_ZPOS_ID=ZARR_OR_ZPOS_S and Z2.ZPOS_ZTCD_ID is not null)>1 then ' ' else (select ZTXC.ZTXC_LONGDESC from ztxc, ztcd where ZTXC.ZTXC_ID=ZTCD.ZTCD_ZTXC_ID and ZTCD.ZTCD_ID=(select distinct Z2.ZPOS_ZTCD_ID from zpos Z2 where Z2.ZPOS_INTERNALSPLIT_ZPOS_ID=ZARR_OR_ZPOS_S and Z2.ZPOS_ZTCD_ID is not null)) end ),(case when (select count( distinct Z3.ZPOS_ZTCD_ID) from zpos Z3 where Z3.ZPOS_ZARR_ID=ZARR_OR_ZPOS_S and Z3.ZPOS_POSTDATE=ZPOS_POSTDATE_S and Z3.ZPOS_ZWIN_ID=ZWIN_ID_S)>1 then ' ' else (select ZTXC.ZTXC_LONGDESC from ztxc, ztcd where ZTXC.ZTXC_ID=ZTCD.ZTCD_ZTXC_ID and ZTCD.ZTCD_ID=(select distinct Z4.ZPOS_ZTCD_ID from zpos Z3, zpos Z4 where Z3.ZPOS_ZARR_ID=ZARR_OR_ZPOS_S  and Z3.ZPOS_TAXLINK_ID=Z4.ZPOS_TAXLINK_ID and Z3.ZPOS_POSTDATE=ZPOS_POSTDATE_S and Z3.ZPOS_ZWIN_ID=ZWIN_ID_S and Z4.ZPOS_ZTCD_ID is not null and rownum=1)) end)) TAXCODE_DESC,
  decode(INDICATE_ZPOS_ID,1,(case when (select count( distinct Z2.ZPOS_ZTCD_ID) from zpos Z2 where Z2.ZPOS_INTERNALSPLIT_ZPOS_ID=ZARR_OR_ZPOS_S and Z2.ZPOS_ZTCD_ID is not null)>1 then ' ' else (wtrl_translate('ZTXC',(select ZTXC.ZTXC_ID from ZTXC, ZTCD where ZTXC_ID=ZTCD.ZTCD_ZTXC_ID and ZTCD.ZTCD_ID=(select Z2.ZPOS_ZTCD_ID from zpos Z2 where Z2.ZPOS_INTERNALSPLIT_ZPOS_ID=ZARR_OR_ZPOS_S and Z2.ZPOS_ZTCD_ID is not null and rownum=1)), 'ZTXC_LONGDESC', (SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=(SELECT ZWIN.ZWIN_INVOICE_TO_XCMS_ID FROM ZWIN WHERE ZWIN.ZWIN_ID=ZWIN_ID_S)))) end),(case when (select count(distinct Z3.ZPOS_ZTCD_ID) from zpos Z3 where Z3.ZPOS_ZARR_ID=ZARR_OR_ZPOS_S and Z3.ZPOS_POSTDATE=ZPOS_POSTDATE_S and Z3.ZPOS_ZWIN_ID=ZWIN_ID_S)>1 then ' '  else (wtrl_translate('ZTXC',(select ZTXC.ZTXC_ID from ztxc, ztcd where ZTXC.ZTXC_ID=ZTCD.ZTCD_ZTXC_ID and ZTCD.ZTCD_ID=(select Z4.ZPOS_ZTCD_ID from zpos Z3, zpos Z4 where Z3.ZPOS_ZARR_ID=ZARR_OR_ZPOS_S and Z3.ZPOS_TAXLINK_ID=Z4.ZPOS_TAXLINK_ID and Z3.ZPOS_POSTDATE=zpos_postdate_s and Z3.ZPOS_ZWIN_ID=Zwin_id_s and Z4.ZPOS_ZTCD_ID is not null and rownum=1)), 'ZTXC_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=(SELECT ZWIN.ZWIN_INVOICE_TO_XCMS_ID FROM ZWIN WHERE ZWIN.ZWIN_ID=ZWIN_ID_S)))) end)) TAXCODE_DESC, 
  decode(INDICATE_ZPOS_ID,1,(case when (select count( distinct Z2.ZPOS_ZTCD_ID) from zpos Z2 where Z2.ZPOS_INTERNALSPLIT_ZPOS_ID=ZARR_OR_ZPOS_S and Z2.ZPOS_ZTCD_ID is not null)>1 then 1 else 0 end ),(case when (select count( distinct Z3.ZPOS_ZTCD_ID) from zpos Z3 where Z3.ZPOS_ZARR_ID=ZARR_OR_ZPOS_S and Z3.ZPOS_POSTDATE=ZPOS_POSTDATE_S and Z3.ZPOS_ZWIN_ID=ZWIN_ID_S)>1 then 1 else 0 end)) MULTI_TAXCODE_EXISTS, 
  ZWIN_ID_S ZWIN_ID
FROM
(
SELECT
--  distinct
  MIN(ntd.ZPOS_POSTDATE) ZPOS_POSTDATE_S,
  MIN(ntd.ZPOS_ID) ZPOS_ID_S,
  MIN(NTD.ZPOS_INTERNALGROUP_ID) ZPOS_INTERNALGROUP_ID_S,
  1 IS_RES,
  0 IS_FA,
  ntd.ZPOS_ORIGINATED_YRES_ID ORI_RES_FA_ID,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1,(-ntd.zpos_unitprice*ntd.zpos_quantity),-(ntd.zpos_unitprice*ntd.zpos_quantity)),DECODE(ntd.ZPOS_CDT,5,0,ntd.zpos_sumupamount))) GROSS_S,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1,NULL,(ntd.zpos_unitprice*ntd.zpos_quantity)), NULL)) PAY_S,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1, -ntd.zpos_unitprice*ntd.zpos_quantity, NULL),DECODE(ntd.ZPOS_CDT,5,0, ntd.zpos_sumupamount))) REV_S,
  ZWIN.ZWIN_ID ZWIN_ID_S,
  ZWIN.ZWIN_INVOICE_TO_XCMS_ID INVOICE_S,
  (SELECT YRES.YRES_EXPARRTIME FROM YRES WHERE YRES.YRES_ID=NTD.ZPOS_ORIGINATED_YRES_ID) ARRIVAL,
  (SELECT YRES.YRES_EXPDEPTIME FROM YRES WHERE YRES.YRES_ID=NTD.ZPOS_ORIGINATED_YRES_ID) DEPARTURE,
  DECODE(NVL(ntd.ZPOS_ZARR_ID,0),0,1,0) INDICATE_ZPOS_ID,
   ntd.ZPOS_POSCHECK_NR GUEST_CHECK_NUM,
  null ZCHK_OR_ZPOS,
  DECODE(NVL(ntd.ZPOS_ZARR_ID,0),0,ntd.ZPOS_ID,ntd.ZPOS_ZARR_ID) ZARR_OR_ZPOS_S,
  DECODE(NVL(ntd.ZPOS_ORIGINATED_YRES_ID,0),0,1,0) ZFAC_OR_YRES
FROM
  ZPOS ntd,
  ZWIN ZWIN,
  ZDCO,
  ZORG
WHERE
ZORG.ZORG_ZPOS_ID(+)=NTD.ZPOS_ID AND
  (zpos_zall_id IS NULL OR zpos_zall_id=0) AND
  ntd.zpos_id=ntd.zpos_internalsplit_zpos_id AND
  zdco_id=ntd.zpos_zdco_id AND
  ntd.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID(+) AND
  NVL(ntd.ZPOS_HIDDEN,0)=0
  AND NVL(ntd.ZPOS_NOREPRINT,0)<>1
  AND nvl(NTD.ZPOS_ORIGINATED_ZFAC_ID,0)=0
--AND ZWIN.ZWIN_ID=:WINID
GROUP BY ZWIN.ZWIN_ID,ZWIN.ZWIN_INVOICE_TO_XCMS_ID, ntd.ZPOS_POSCHECK_NR,  ntd.ZPOS_ORIGINATED_YRES_ID, DECODE(NVL(ntd.ZPOS_ZARR_ID,0),0,ntd.ZPOS_ID,ntd.ZPOS_ZARR_ID), DECODE(NVL(ntd.ZPOS_ORIGINATED_YRES_ID,0),0,1,0), DECODE(NVL(ntd.ZPOS_ZARR_ID,0),0,1,0)--,decode(nvl(ntd.ZPOS_ZARR_ID,0),0,ntd.ZPOS_ID,ntd.ZPOS_ID)-- ntd.ZPOS_ID--,zdco.ZDCO_ID--, ntd.ZPOS_ID, ntd.ZPOS_ZARR_ID
UNION ALL
SELECT
  MIN(ntd.ZPOS_POSTDATE) ZPOS_POSTDATE_S,
  MIN(ntd.ZPOS_ID) ZPOS_ID_S,
  MIN(NTD.ZPOS_INTERNALGROUP_ID) ZPOS_INTERNALGROUP_ID_S,
  0 IS_RES,
  1 IS_FA,
  ntd.ZPOS_ORIGINATED_ZFAC_ID ORI_RES_FA_ID,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1,(-ntd.zpos_unitprice*ntd.zpos_quantity),-(ntd.zpos_unitprice*ntd.zpos_quantity)),DECODE(ntd.ZPOS_CDT,5,0,ntd.zpos_sumupamount))) GROSS_S,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1,NULL,(ntd.zpos_unitprice*ntd.zpos_quantity)), NULL)) PAY_S,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1, -ntd.zpos_unitprice*ntd.zpos_quantity, NULL),DECODE(ntd.ZPOS_CDT,5,0, ntd.zpos_sumupamount))) REV_S,
  ZWIN.ZWIN_ID ZWIN_ID_S,
  ZWIN.ZWIN_INVOICE_TO_XCMS_ID INVOICE_S,
  (SELECT ZFAC.ZFAC_EXPVALIDFROM FROM ZFAC WHERE ZFAC.ZFAC_ID=ntd.ZPOS_ORIGINATED_ZFAC_ID) ARRIVAL,
  (SELECT ZFAC.ZFAC_EXPVALIDUNTIL FROM ZFAC WHERE ZFAC.ZFAC_ID=ntd.ZPOS_ORIGINATED_ZFAC_ID) DEPARTURE,
  DECODE(NVL(ntd.ZPOS_ZARR_ID,0),0,1,0) INDICATE_ZPOS_ID,
  ntd.ZPOS_POSCHECK_NR GUEST_CHECK_NUM,
  null ZCHK_OR_ZPOS,
  DECODE(NVL(ntd.ZPOS_ZARR_ID,0),0,ntd.ZPOS_ID,ntd.ZPOS_ZARR_ID) ZARR_OR_ZPOS_S,
  DECODE(NVL(ntd.ZPOS_ORIGINATED_YRES_ID,0),0,1,0) ZFAC_OR_YRES
FROM
  ZPOS ntd,
  ZWIN ZWIN,
  ZDCO
WHERE
  (zpos_zall_id IS NULL OR zpos_zall_id=0) AND
  ntd.zpos_id=ntd.zpos_internalsplit_zpos_id AND
  zdco_id=ntd.zpos_zdco_id AND
  ntd.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID(+) AND
  NVL(ntd.ZPOS_HIDDEN,0)=0
  AND NVL(ntd.ZPOS_NOREPRINT,0)<>1
  AND nvl(NTD.ZPOS_ORIGINATED_ZFAC_ID,0)<>0
--AND ZWIN.ZWIN_ID=:WINID
GROUP BY ZWIN.ZWIN_ID,ZWIN.ZWIN_INVOICE_TO_XCMS_ID, ntd.ZPOS_POSCHECK_NR,  ntd.ZPOS_ORIGINATED_ZFAC_ID, DECODE(NVL(ntd.ZPOS_ZARR_ID,0),0,ntd.ZPOS_ID,ntd.ZPOS_ZARR_ID),DECODE(NVL(ntd.ZPOS_ORIGINATED_YRES_ID,0),0,1,0), DECODE(NVL(ntd.ZPOS_ZARR_ID,0),0,1,0)--,decode(nvl(ntd.ZPOS_ZARR_ID,0),0,ntd.ZPOS_ID,ntd.ZPOS_ID)-- ntd.ZPOS_ID--,zdco.ZDCO_ID--, ntd.ZPOS_ID, ntd.ZPOS_ZARR_ID
) WHERE  GUEST_CHECK_NUM is null
union all
SELECT
  ZPOS_POSTDATE_S DAT,
  to_number(to_char(ZPOS_POSTDATE_S,'J')) DAT_J,
  nvl2((to_char( DEPARTURE,'J')),'-',NULL) MINUSI,
  (SELECT ZPOS.ZPOS_CDT FROM ZPOS WHERE ZPOS.ZPOS_ID=ZPOS_ID_S) CDT,
  ZPOS_ID_S ZPOS_ID,
  ZPOS_INTERNALGROUP_ID_S ZPOS_INTERNALGROUP_ID,
  GROSS_S GROSS,
  PAY_S PAY,
  REV_S REV,
  IS_RES,
  IS_FA,
  ORI_RES_FA_ID,
  ARRIVAL,
  DEPARTURE,
  ZFAC_OR_YRES,
  (SELECT ZDCO.ZDCO_NUMERICDESC FROM ZDCO,ZPOS WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID AND ZPOS.ZPOS_ID=ZPOS_ID_S) ZDCO_NUMERICDESC,
  (SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) SPAY,
  (SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)=2 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) SPAY_CL,
  (SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)<>2 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) SPAY_NOCL,
  (SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1, -ZPOS.zpos_unitprice*ZPOS.zpos_quantity, NULL),DECODE(ZPOS.ZPOS_CDT,5,0, ZPOS.zpos_sumupamount))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) SREV,
  (SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1, -ZPOS.zpos_unitprice*ZPOS.zpos_quantity, NULL),DECODE(ZPOS.ZPOS_CDT,5,0, ZPOS.zpos_sumupamount))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S)-(SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) OPENA,
    (DECODE(IS_FA,0,(SELECT V8_REP_NAME.NAME6 FROM YRES,V8_REP_NAME WHERE YRES.YRES_ID=ORI_RES_FA_ID AND YRES.YRES_XCMS_ID=V8_REP_NAME.XCMS_ID),(SELECT V8_REP_NAME.NAME6 FROM ZFAC,V8_REP_NAME WHERE ZFAC.ZFAC_ID=ORI_RES_FA_ID AND ZFAC.ZFAC_XCMS_ID=V8_REP_NAME.XCMS_ID))) REAL_ORIGINATOR_NAME,
  (SELECT ZPCF.ZPCF_CUSTOMORIGINATORNAME FROM ZPCF WHERE ZPCF.ZPCF_ZPOS_ID=ZPOS_ID_S) ORIGINATOR_NAME,
  (DECODE(IS_FA,0,(NVL((SELECT YRMS.YRMS_SHORTDESC FROM YRMS,YDET WHERE YRMS.YRMS_ID=YDET.YDET_YRMS_ID AND YDET.YDET_DATE=ZPOS_POSTDATE_S AND YDET.YDET_YRES_ID=ORI_RES_FA_ID),(SELECT YRMS.YRMS_SHORTDESC FROM YRMS,YDET WHERE YRMS.YRMS_ID=YDET.YDET_YRMS_ID AND YDET.YDET_DATE+1=ZPOS_POSTDATE_S AND YDET.YDET_YRES_ID=ORI_RES_FA_ID))),(NVL((SELECT ZFAN.ZFAN_LONGDESC FROM ZFAN,ZFAC WHERE ZFAN.ZFAN_ID=ZFAC.ZFAC_ZFAN_ID AND ZFAC.ZFAC_ID=ORI_RES_FA_ID),(SELECT ZFAC.ZFAC_AUTONUMBER FROM ZFAC WHERE ZFAC.ZFAC_ID=ORI_RES_FA_ID))))) ORI_RES_FA_DESC,
  TO_CHAR((CASE WHEN (SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1, -ZPOS.zpos_unitprice*ZPOS.zpos_quantity, NULL),DECODE(ZPOS.ZPOS_CDT,5,0, ZPOS.zpos_sumupamount))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S)-(SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S)=0 AND NVL((SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)=2  
  AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S),0)<>0 THEN NVL((SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)=2  AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S),0) ELSE (SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1, -ZPOS.zpos_unitprice*ZPOS.zpos_quantity, NULL),DECODE(ZPOS.ZPOS_CDT,5,0, ZPOS.zpos_sumupamount))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S)-(SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0)
  AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) END)*100,'999999990,09') FSPAY_CL,
  decode(INDICATE_ZPOS_ID,
                                   0, NVL( WTRL_TRANSLATE('ZDCO',(select ZDCO_ID from ZDCO where ZDCO_ID=(select ZPOS_ZDCO_ID from ZPOS where ZPOS_ID=ZPOS_ID_S)),'ZDCO_FOLIOTEXT',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(select ZPOS_DESCRIPT from zpos where ZPOS.ZPOS_ID=ZPOS_id_S)),
                                   1, decode(nvl((select ZORG.ZORG_ROLE from zorg where ZORG.ZORG_ZPOS_ID=ZPOS_ID_S),0),
                                                         0,(select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS.ZPOS_ID=ZCHK_OR_ZPOS_S),
                                                         1,decode(nvl((select YRCH.YRCH_FOLIOTEXT from YRCH where YRCH.YRCH_ID=(select S.ZORG_TABLE_ID from ZORG s where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S)),0),'0',decode((select ZPOS.ZPOS_ID from ZPOS where ZPOS.ZPOS_ID=ZCHK_OR_ZPOS_S),(select yrch_LONGDESC from yrch where yrch_id=(SELECT S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S)),
                                                         wtrl_translate('YRCH',(select S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S),'YRCH_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZCHK_OR_ZPOS_S)),decode((select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZCHK_OR_ZPOS_S),(select yrch_foliotext from yrch where YRCH_ID=(select S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S)),wtrl_translate('YRCH',(SELECT S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S),'YRCH_FOLIOTEXT',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(SELECT ZPOS.ZPOS_DESCRIPT from zpos where ZPOS.ZPOS_ID=ZCHK_OR_ZPOS_S))),
                                                         2,decode((select ZPOS.zpos_descript from ZPOS where ZPOS_ID=ZCHK_OR_ZPOS_S),(select ZART.ZART_LONGDESC from ZART where ZART.ZART_ID=(select ZORG_TABLE_ID from ZORG where ZORG.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S)),wtrl_translate('ZART',(SELECT ZORG.ZORG_TABLE_ID from ZORG where ZORG.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S),'ZART_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(SELECT ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZCHK_OR_ZPOS_S)),
                                                         3,decode(nvl((select YPAC.YPAC_FOLIOTEXT from YPAC where YPAC.YPAC_ID=(select S.ZORG_TABLE_ID from ZORG s where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S)),0),'0',decode((select ZPOS.ZPOS_ID from ZPOS where ZPOS.ZPOS_ID=ZCHK_OR_ZPOS_S),(select YPAC_LONGDESC from YPAC where YPAC_ID=(SELECT S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S)),
                                                         wtrl_translate('YPAC',(select S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S),'YPAC_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZCHK_OR_ZPOS_S)),decode((select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZCHK_OR_ZPOS_S),(select YPAC_FOLIOTEXT from YPAC where YPAC_ID=(select S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S)),wtrl_translate('YPAC',(SELECT S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S),'YPAC_FOLIOTEXT',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(SELECT ZPOS.ZPOS_DESCRIPT from zpos where ZPOS.ZPOS_ID=ZCHK_OR_ZPOS_S))),
                                                        4,decode(nvl((select ZDCO.ZDCO_FOLIOTEXT from ZDCO where ZDCO.ZDCO_ID=(select S.ZORG_TABLE_ID from ZORG s where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S)),0),'0',decode((select ZPOS.ZPOS_ID from ZPOS where ZPOS.ZPOS_ID=ZCHK_OR_ZPOS_S),(select ZDCO_LONGDESC from ZDCO where ZDCO_ID=(SELECT S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S)),
                                                         wtrl_translate('ZDCO',(select S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S),'ZDCO_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZCHK_OR_ZPOS_S)),decode((select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZCHK_OR_ZPOS_S),(select ZDCO_LONGDESC from ZDCO where ZDCO_ID=(select S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S)),wtrl_translate('ZDCO',(SELECT S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZCHK_OR_ZPOS_S),'ZDCO_FOLIOTEXT',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=INVOICE_S)),(SELECT ZPOS.ZPOS_DESCRIPT from zpos where ZPOS.ZPOS_ID=ZCHK_OR_ZPOS_S))))
          ) DDESC,
ZCHK_OR_ZPOS_S ZCHK_OR_ZPOS, 
  ZARR_OR_ZPOS,
  INDICATE_ZPOS_ID, 
  (DECODE(INDICATE_ZPOS_ID,0,ZCHK_OR_ZPOS_S,to_char(null))) GUEST_CHECK_NUM,
   --decode(INDICATE_ZPOS_ID,1,(case when (select count( distinct Z2.ZPOS_ZTCD_ID) from zpos Z2 where Z2.ZPOS_INTERNALSPLIT_ZPOS_ID=ZCHK_OR_ZPOS_S and Z2.ZPOS_ZTCD_ID is not null)>1 then ' ' else (select ZTXC.ZTXC_LONGDESC from ztxc, ztcd where ZTXC.ZTXC_ID=ZTCD.ZTCD_ZTXC_ID and ZTCD.ZTCD_ID=(select distinct Z2.ZPOS_ZTCD_ID from zpos Z2 where Z2.ZPOS_INTERNALSPLIT_ZPOS_ID=ZCHK_OR_ZPOS_S and Z2.ZPOS_ZTCD_ID is not null)) end ),(case when (select count( distinct Z3.ZPOS_ZTCD_ID) from zpos Z3 where Z3.ZPOS_POSCHECK_NR=ZCHK_OR_ZPOS_S and Z3.ZPOS_POSTDATE=ZPOS_POSTDATE_S and Z3.ZPOS_ZWIN_ID=ZWIN_ID_S)>1 then ' ' else (select ZTXC.ZTXC_LONGDESC from ztxc, ztcd where ZTXC.ZTXC_ID=ZTCD.ZTCD_ZTXC_ID and ZTCD.ZTCD_ID=(select distinct Z4.ZPOS_ZTCD_ID from zpos Z3, zpos Z4 where Z3.ZPOS_POSCHECK_NR=ZCHK_OR_ZPOS_S  and Z3.ZPOS_TAXLINK_ID=Z4.ZPOS_TAXLINK_ID and Z3.ZPOS_POSTDATE=ZPOS_POSTDATE_S and Z3.ZPOS_ZWIN_ID=ZWIN_ID_S and Z4.ZPOS_ZTCD_ID is not null and rownum=1)) end)) TAXCODE_DESC,
   decode(INDICATE_ZPOS_ID,1,(case when (select count( distinct Z2.ZPOS_ZTCD_ID) from zpos Z2 where Z2.ZPOS_INTERNALSPLIT_ZPOS_ID=ZCHK_OR_ZPOS_S and Z2.ZPOS_ZTCD_ID is not null)>1 then ' ' else (wtrl_translate('ZTXC',(select ZTXC.ZTXC_ID from ZTXC, ZTCD where ZTXC_ID=ZTCD.ZTCD_ZTXC_ID and ZTCD.ZTCD_ID=(select Z2.ZPOS_ZTCD_ID from zpos Z2 where Z2.ZPOS_INTERNALSPLIT_ZPOS_ID=ZCHK_OR_ZPOS_S and Z2.ZPOS_ZTCD_ID is not null)), 'ZTXC_LONGDESC', (SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=(SELECT ZWIN.ZWIN_INVOICE_TO_XCMS_ID FROM ZWIN WHERE ZWIN.ZWIN_ID=ZWIN_ID_S)))) end),(case when (select count(distinct Z3.ZPOS_ZTCD_ID) from zpos Z3 where Z3.ZPOS_POSCHECK_NR=ZCHK_OR_ZPOS_S and Z3.ZPOS_POSTDATE=ZPOS_POSTDATE_S and Z3.ZPOS_ZWIN_ID=ZWIN_ID_S)>1 then ' '  else (wtrl_translate('ZTXC',(select ZTXC.ZTXC_ID from ztxc, ztcd where ZTXC.ZTXC_ID=ZTCD.ZTCD_ZTXC_ID and ZTCD.ZTCD_ID=(select Z4.ZPOS_ZTCD_ID from zpos Z3, zpos Z4 where Z3.ZPOS_POSCHECK_NR=ZCHK_OR_ZPOS_S and Z3.ZPOS_TAXLINK_ID=Z4.ZPOS_TAXLINK_ID and Z3.ZPOS_POSTDATE=zpos_postdate_s and Z3.ZPOS_ZWIN_ID=Zwin_id_s and Z4.ZPOS_ZTCD_ID is not null and rownum=1)), 'ZTXC_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=(SELECT ZWIN.ZWIN_INVOICE_TO_XCMS_ID FROM ZWIN WHERE ZWIN.ZWIN_ID=ZWIN_ID_S)))) end)) TAXCODE_DESC, 
   decode(INDICATE_ZPOS_ID,1,(case when (select count( distinct Z2.ZPOS_ZTCD_ID) from zpos Z2 where Z2.ZPOS_INTERNALSPLIT_ZPOS_ID=ZCHK_OR_ZPOS_S and Z2.ZPOS_ZTCD_ID is not null)>1 then 1 else 0 end ),(case when (select count( distinct Z3.ZPOS_ZTCD_ID) from zpos Z3, ZPOS Z5 where Z5.ZPOS_POSCHECK_NR=ZCHK_OR_ZPOS_S and Z3.ZPOS_ZWIN_ID=ZWIN_ID_S )>1 then 1 else 0 end)) MULTI_TAXCODE_EXISTS,
  ZWIN_ID_S ZWIN_ID
FROM
(
SELECT
  MIN(ntd.ZPOS_POSTDATE) ZPOS_POSTDATE_S,
  MIN(ntd.ZPOS_ID) ZPOS_ID_S,
  MIN(NTD.ZPOS_INTERNALGROUP_ID) ZPOS_INTERNALGROUP_ID_S,
  1 IS_RES,
  0 IS_FA,
  ntd.ZPOS_ORIGINATED_YRES_ID ORI_RES_FA_ID,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1,(-ntd.zpos_unitprice*ntd.zpos_quantity),-(ntd.zpos_unitprice*ntd.zpos_quantity)),DECODE(ntd.ZPOS_CDT,5,0,ntd.zpos_sumupamount))) GROSS_S,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1,NULL,(ntd.zpos_unitprice*ntd.zpos_quantity)), NULL)) PAY_S,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1, -ntd.zpos_unitprice*ntd.zpos_quantity, NULL),DECODE(ntd.ZPOS_CDT,5,0, ntd.zpos_sumupamount))) REV_S,
  ZWIN.ZWIN_ID ZWIN_ID_S,
  ZWIN.ZWIN_INVOICE_TO_XCMS_ID INVOICE_S,
  (SELECT TRUNC(YRES.YRES_EXPARRTIME) FROM YRES WHERE YRES.YRES_ID=NTD.ZPOS_ORIGINATED_YRES_ID) ARRIVAL,
(SELECT trunc(YRES.YRES_EXPDEPTIME) FROM YRES WHERE YRES.YRES_ID=NTD.ZPOS_ORIGINATED_YRES_ID) DEPARTURE,
    DECODE(NVL(ntd.ZPOS_POSCHECK_NR,' '),' ',1,0) INDICATE_ZPOS_ID,
  to_char(DECODE(NVL(ntd.ZPOS_POSCHECK_NR,' '),' ',to_char(ntd.ZPOS_ID),ntd.ZPOS_POSCHECK_NR)) ZCHK_OR_ZPOS_S,
to_number(null) ZARR_OR_ZPOS,
  DECODE(NVL(ntd.ZPOS_ORIGINATED_YRES_ID,0),0,1,0) ZFAC_OR_YRES
FROM
  ZPOS ntd,
  ZWIN ZWIN,
  ZDCO,
  ZORG
WHERE
ZORG.ZORG_ZPOS_ID(+)=NTD.ZPOS_ID AND
  (zpos_zall_id IS NULL OR zpos_zall_id=0) AND
  ntd.zpos_id=ntd.zpos_internalsplit_zpos_id AND
  zdco_id=ntd.zpos_zdco_id AND
  ntd.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID(+) AND
  NVL(ntd.ZPOS_HIDDEN,0)=0
  AND NVL(ntd.ZPOS_NOREPRINT,0)<>1
  AND nvl(NTD.ZPOS_ORIGINATED_ZFAC_ID,0)=0
--AND ZWIN.ZWIN_ID=:WINID
GROUP BY ZWIN.ZWIN_ID, ZWIN.ZWIN_INVOICE_TO_XCMS_ID, ntd.ZPOS_ORIGINATED_YRES_ID, to_char(DECODE(NVL(ntd.ZPOS_POSCHECK_NR,' '),' ',to_char(ntd.ZPOS_ID),ntd.ZPOS_POSCHECK_NR)), DECODE(NVL(ntd.ZPOS_ORIGINATED_YRES_ID,0),0,1,0),DECODE(NVL(ntd.ZPOS_POSCHECK_NR,' '),' ',1,0) --,decode(nvl(ntd.ZPOS_ZARR_ID,0),0,ntd.ZPOS_ID,ntd.ZPOS_ID)-- ntd.ZPOS_ID--,zdco.ZDCO_ID--, ntd.ZPOS_ID, ntd.ZPOS_ZARR_ID
UNION ALL
SELECT
  MIN(ntd.ZPOS_POSTDATE) ZPOS_POSTDATE_S,
  MIN(ntd.ZPOS_ID) ZPOS_ID_S,
MIN(NTD.ZPOS_INTERNALGROUP_ID) ZPOS_INTERNALGROUP_ID_S,
  0 IS_RES,
  1 IS_FA,
  ntd.ZPOS_ORIGINATED_ZFAC_ID ORI_RES_FA_ID,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1,(-ntd.zpos_unitprice*ntd.zpos_quantity),-(ntd.zpos_unitprice*ntd.zpos_quantity)),DECODE(ntd.ZPOS_CDT,5,0,ntd.zpos_sumupamount))) GROSS_S,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1,NULL,(ntd.zpos_unitprice*ntd.zpos_quantity)), NULL)) PAY_S,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1, -ntd.zpos_unitprice*ntd.zpos_quantity, NULL),DECODE(ntd.ZPOS_CDT,5,0, ntd.zpos_sumupamount))) REV_S,
  ZWIN.ZWIN_ID ZWIN_ID_S,
  ZWIN.ZWIN_INVOICE_TO_XCMS_ID INVOICE_S,
  NULL ARRIVAL,
  NULL DEPARTURE,
  DECODE(NVL(ntd.ZPOS_POSCHECK_NR,' '),' ',1,0) INDICATE_ZPOS_ID,
  to_char(DECODE(NVL(ntd.ZPOS_POSCHECK_NR,' '),' ',to_char(ntd.ZPOS_ID),ntd.ZPOS_POSCHECK_NR)) ZCHK_OR_ZPOS_S,
to_number(null) ZARR_OR_ZPOS,
  DECODE(NVL(ntd.ZPOS_ORIGINATED_YRES_ID,0),0,1,0) ZFAC_OR_YRES
FROM
  ZPOS ntd,
  ZWIN ZWIN,
  ZDCO
WHERE
  (zpos_zall_id IS NULL OR zpos_zall_id=0) AND
  ntd.zpos_id=ntd.zpos_internalsplit_zpos_id AND
  zdco_id=ntd.zpos_zdco_id AND
  ntd.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID(+) AND
  NVL(ntd.ZPOS_HIDDEN,0)=0
  AND NVL(ntd.ZPOS_NOREPRINT,0)<>1
  AND nvl(NTD.ZPOS_ORIGINATED_ZFAC_ID,0)<>0
--AND ZWIN.ZWIN_ID=:WINID
GROUP BY ZWIN.ZWIN_ID, ZWIN.ZWIN_INVOICE_TO_XCMS_ID, ntd.ZPOS_ORIGINATED_ZFAC_ID, to_char(DECODE(NVL(ntd.ZPOS_POSCHECK_NR,' '),' ',to_char(ntd.ZPOS_ID),ntd.ZPOS_POSCHECK_NR)), DECODE(NVL(ntd.ZPOS_ORIGINATED_YRES_ID,0),0,1,0),DECODE(NVL(ntd.ZPOS_POSCHECK_NR,' '),' ',1,0) --,decode(nvl(ntd.ZPOS_ZARR_ID,0),0,ntd.ZPOS_ID,ntd.ZPOS_ID)-- ntd.ZPOS_ID--,zdco.ZDCO_ID--, ntd.ZPOS_ID, ntd.ZPOS_ZARR_ID
)
where 
(DECODE(INDICATE_ZPOS_ID,0,ZCHK_OR_ZPOS_S,to_char(null))) is not null
)
order by CDT ASC, DAT;
