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


CREATE OR REPLACE VIEW v8_sys_ar_comp_roomno (ACC_ID, ROOMNUMBER) AS
SELECT YRES_ID, RESROOM
  FROM v8_sys_bgs_roomno
UNION  
SELECT ZFAC_ID, to_char(decode(nvl(zfac_autonumber,0),0,(select zfan_longdesc from zfan where zfan_id=zfac.zfac_zfan_id),zfac_autonumber)) ROOMNUMBER
  FROM ZFAC;


CREATE OR REPLACE VIEW V8_SYS_LEISURE_YRES
AS 
SELECT 
 'RES' acctype, 
  YRES.YRES_ID, 
  YRES.YRES_XCMS_ID YRES_XCMS_ID, 
  (select name2 from v8_rep_name where xcms_id=YRES.YRES_XCMS_ID) NAME_RES,
  (SELECT YRMS.YRMS_SHORTDESC FROM YRMS,YDET WHERE YRMS.YRMS_ID=YDET.YDET_YRMS_ID AND YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) YRMS_SHORTDESC, 
  (DECODE(YRES.YRES_ACTARRTIME,NULL,0,1)) CHECKED_IN, 
  YRES.yres_calcstatus, 
  ('Res. #: '||yres.YRES_ID ||', From '|| YRES.YRES_EXPARRTIME||' to '||YRES.YRES_EXPDEPTIME) ResInfoString, 
  YRES.YRES_CURRENT_YDET_ID YDET_ID, 
  YRES.YRES_CRSRESNR YRES_CRSRESNR, 
  V8_sys_fideliodate FIDELIODATUM, 
  YRES.YRES_EXPARRTIME YRES_EXPARRTIME, 
  YRES.YRES_ACTARRTIME YRES_ACTARRTIME, 
  YRES.YRES_EXPDEPTIME YRES_EXPDEPTIME, 
  YRES.YRES_ACTDEPTIME YRES_ACTDEPTIME, 
  (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_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,'HH24:MI') YRES_EXPARRHOUR, 
  TO_CHAR(YRES.YRES_EXPDEPHOUR,'HH24:MI') YRES_EXPDEPHOUR, 
  YRES.YRES_RESSTATUS YRES_RESSTATUS, 
  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_ADULTNO FROM YDET WHERE YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID) YDET_ADULTNO, 
  (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 
FROM 
YRES 
union all 
select 
 'FA' acctype, 
  zfac_id, 
  zfac_xcms_id YRES_XCMS_ID, 
  (select name2 from v8_rep_name where xcms_id=zfac_xcms_id) NAME_RES,
  nvl(zfan_longdesc, zfac_autonumber) YRMS_SHORTDESC, 
  (DECODE(zfac_actvalidfrom,NULL,0,1)) CHECKED_IN, 
  0, 
  ('FA. #: '||zfac_id ||', From '|| zfac_expvalidfrom ||' to '|| zfac_expvaliduntil) ResInfoString, 
  0 YDET_ID, 
  '' YRES_CRSRESNR, 
  V8_sys_fideliodate FIDELIODATUM, 
   zfac_expvalidfrom , 
   zfac_actvalidfrom actarrtime, 
   nvl(zfac_expvaliduntil, zfac_expvalidfrom+365), 
   zfac_actvaliduntil actdeptime, 
  '' YGCO_SHORTDESC, 
  '' YGCO_LONGDESC, 
  (zfac_expvaliduntil) - (zfac_expvalidfrom) Nights, 
  (DECODE((zfac_expvalidfrom),V8_sys_fideliodate,1,0)) ARRIVAL_TODAY, 
  (DECODE((zfac_expvalidfrom),V8_sys_fideliodate,(DECODE(zfac_actvalidfrom,NULL,0,1)),0)) CHECKED_IN_TODAY, 
  (DECODE((zfac_expvalidfrom),V8_sys_fideliodate,1,0)) DEPARTURE_TODAY, 
  (DECODE((zfac_expvaliduntil),V8_sys_fideliodate,(DECODE(zfac_actvaliduntil,NULL,0,1)),0)) CHECKED_OUT_TODAY, 
  (DECODE(zfac_actvaliduntil,NULL,0,1)) CHECKED_OUT, 
  (CASE WHEN (zfac_expvalidfrom IS NOT NULL AND V8_sys_fideliodate BETWEEN zfac_expvalidfrom AND zfac_expvaliduntil AND zfac_actvaliduntil IS NULL) THEN 1 ELSE 0 END) INHOUSE, 
  '00:00' YRES_EXPARRHOUR, 
  '00:00' YRES_EXPDEPHOUR, 
  1 YRES_RESSTAUS, 
  0 YRES_WALKIN, 
  '' YRES_FREETEXT1, 
  '' YRES_FREETEXT2, 
  0 YRES_HASSHARE, 
  0 YRES_SHARETYPE, 
  1 YDET_ADULTNO, 
  (select zfag_shortdesc from zfag where zfac_zfag_id=zfag_id) YCAT_SHORTDESC 
from   zfac, zfan, zfag, fideliodate d 
where  zfac_zfan_id=zfan_id(+) and 
       zfac_zfag_id=zfag_id and 
       zfag_type not in (1, 2);


CREATE OR REPLACE VIEW POS8_REP_GUESTCHECKS
AS   
   SELECT  TO_NUMBER (DECODE (MFORDER.ORDERTYPE || MFORDER.ADDONCHARGE,
                   'SF', '0',
                   ROUND (MFORDER.TOTAL, 3)
                  )
          ) MFORDER_TOTAL,
          MFORDER.ORDERTYPE MFORDER_ORDERTYPE,
          MFORDER.BOOKINGDATE MFORDER_BOOKINGDATE,
          MFORDER.BOOKINGDATEV8 MFORDER_BOOKINGDATEV8,
             TO_CHAR
                (TO_NUMBER (SUBSTR ((SELECT MFCHECK.CHECKNO
                                       FROM MFCHECK
                                      WHERE MFCHECK.CHECKID =
                                                             MFPAYMENT.CHECKID),
                                    1,
                                    3
                                   )
                           )
                )
          || '/'
          || TO_CHAR (TO_NUMBER (SUBSTR ((SELECT MFCHECK.CHECKNO
                                            FROM MFCHECK
                                           WHERE MFCHECK.CHECKID =
                                                             MFPAYMENT.CHECKID),
                                         4,
                                         3
                                        )
                                )
                     )
          || '/'
          || TO_CHAR (TO_NUMBER (SUBSTR ((SELECT MFCHECK.CHECKNO
                                            FROM MFCHECK
                                           WHERE MFCHECK.CHECKID =
                                                             MFPAYMENT.CHECKID),
                                         7
                                        )
                                )
                     ) MFCHECK_CHECKNO,
          MFORDER.ARTNAME MFORDER_ARTNAME, MFORDER.ORDERID MFORDER_ORDERID,
          ROUND (MFORDER.COUNT, 3) MFORDER_COUNT,
          MFORDER.STORNOCOUNT MFORDER_STORNOCOUNT,
          MFPAYMENT.CANCELEDPAYMENTID MFPAYMENT_CANCELEDPAYMENTID,
          MFORDER.STORNO MFORDER_STORNO,
          MFORDER.TAXPERCENTAGE MFORDER_TAXPERCENTAGE,
          MFPAYMENT.PAYMENTID MFPAYMENT_PAYMENTID,
          (SELECT MFCHECK.CHECKEND
             FROM MFCHECK
            WHERE MFCHECK.CHECKID = MFPAYMENT.CHECKID) MFCHECK_CHECKEND,
          (SELECT MFCHECK.PERSONCNT
             FROM MFCHECK
            WHERE MFCHECK.CHECKID = MFPAYMENT.CHECKID) MFCHECK_PERSONCNT,
          (SELECT MFTABLE.TABLENO
             FROM MFTABLE, MFCHECK
            WHERE MFCHECK.CHECKID = MFPAYMENT.CHECKID
              AND MFTABLE.TABLEID = MFCHECK.USEDTABLEID) MFTABLE_TABLENO,
          (SELECT MFCHECK.CHECKDATE
             FROM MFCHECK
            WHERE MFCHECK.CHECKID = MFPAYMENT.CHECKID) MFCHECK_CHECKDATE,
          MFPAYMENT.WAITERNAME MFPAYMENT_WAITERNAME,
          MFPAYMENT.CURRENCY MFPAYMENT_CURRENCY,
          MFPAYMENT.XNGRATE MFPAYMENT_XNGRATE,
          MFPAYMENT.AMOUNT MFPAYMENT_AMOUNT,
          (SELECT MFCHECK.TOTAL
             FROM MFCHECK
            WHERE MFCHECK.CHECKID = MFPAYMENT.CHECKID) MFCHECK_TOTAL,
          MFPAYMENT.PAYTYPENAME MFPAYMENT_PAYTYPENAME,
          MFPAYMENT.ROOMNUM MFPAYMENT_ROOMNUM,
          MFPAYMENT.GUESTNAME MFPAYMENT_GUESTNAME,
          (SELECT MFOUTLET.NAME
             FROM MFOUTLET
            WHERE MFOUTLET.OUTLETID = MFPAYMENT.OUTLETID) MFOUTLET_NAME,
          (SELECT MFPAYTYPE.PAYGROUP
             FROM MFPAYTYPE
            WHERE MFPAYTYPE.PAYTYPEID =
                                       MFPAYMENT.PAYTYPEID)
                                                           MFPAYTYPE_PAYGROUP,
          MFORDER.DISCOUNTNAME MFORDER_DISCOUNTNAME,
          MFORDER.DISCOUNTBOOKINGID MFORDER_DISCOUNTBOOKINGID,
          MFORDER.DISCOUNTPERCENTAGE MFORDER_DISCOUNTPERCENTAGE,
          MFORDER.HARD MFORDER_HARD, MFPAYMENT.WAITERID MFPAYMENT_WAITERID,
          (SELECT MFWAITER.LASTNAME
             FROM MFWAITER
            WHERE MFWAITER.WAITERID = MFPAYMENT.WAITERID) MFWAITER_LASTNAME,
          MFPAYMENT.CHECKID MFPAYMENT_CHECKID,
          MFORDER.DISCOUNTED MFORDER_DISCOUNTED,
          (SELECT MFWAITER.GREETING
             FROM MFWAITER
            WHERE MFWAITER.WAITERID = MFPAYMENT.WAITERID) MFWAITER_GREETING,
          (SELECT MFCHECK.CHECKID
             FROM MFCHECK
            WHERE MFCHECK.CHECKID = MFPAYMENT.CHECKID) MFCHECK_CHECKID,
          (SELECT COUNT (MFPAYMENT.CHECKID)
             FROM MFPAYMENT, MFCHECK
            WHERE (MFCHECK.CHECKID = MFPAYMENT.CHECKID)
              AND (MFPAYMENT.CANCELEDPAYMENTID IS NULL)) MFPAYMENT_COUNT,
          ROUND (DECODE (MFORDER.ORDERTYPE,
                         'G', '0',
                           (  MFORDER.TOTAL
                            - MFORDER.TOTAL * MFORDER.DISCOUNTPERCENTAGE / 100
                           )
                         * 100
                         / (100 + MFORDER.TAXPERCENTAGE)
                        ),
                 6
                ) NET,
          ROUND (DECODE (MFORDER.ORDERTYPE,
                         'G', '0',
                           (  MFORDER.TOTAL
                            - MFORDER.TOTAL * MFORDER.DISCOUNTPERCENTAGE / 100
                           )
                         * MFORDER.TAXPERCENTAGE
                         / (100 + MFORDER.TAXPERCENTAGE)
                        ),
                 6
                ) TAX,
          MFORDER.TOTAL / 100 * MFORDER.DISCOUNTPERCENTAGE DIS,
          MFORDER.SINGLEPRICE MFORDER_SINGLEPRICE,
          MFORDER.ADDONCHARGE MFORDER_ADDONCHARGE,
          MFORDER.ORDDATETIME MFORDER_ORDDATETIME,
          MFORDER.OVERGROUPID MFORDER_OVERGROUPID,
          MFORDER.OVERGROUPABBR MFORDER_OVERGROUPABBR,
          (SELECT MFCHECK.CHECKTYPE
             FROM MFCHECK
            WHERE MFCHECK.CHECKID = MFPAYMENT.CHECKID) MFCHECK_CHECKTYPE,
          (SELECT DECODE (MFCHECK.CHECKTYPE,
                          'NORM', 1,
                          'VOID', 2,
                          1
                         )
             FROM MFCHECK
            WHERE MFCHECK.CHECKID = MFPAYMENT.CHECKID) MFCHECK_CHECKTYPE_NUM,
          TO_NUMBER (SUBSTR (MFPAYMENT.WAITERID, 4)) WAITERID,
          TO_NUMBER (SUBSTR (MFPAYMENT.OUTLETID, 4)) OUTLETID,
          NVL ((SELECT 1
                  FROM MFORDER MFORDER_S
                 WHERE MFORDER_S.CHECKID = MFORDER.CHECKID
                   AND MFORDER_S.DISCOUNTPERCENTAGE > 0
                   AND ROWNUM = 1),
               0
              ) CHECK_DIS
     FROM MFORDER, MFPAYMENT
    WHERE MFPAYMENT.CHECKID = MFORDER.CHECKID
      AND (MFORDER.BOOKINGDATE = MFPAYMENT.BOOKINGDATE)
      AND (MFPAYMENT.CANCELEDPAYMENTID IS NULL)
      AND MFPAYMENT.PAYMENTID =
                              (SELECT MIN (MFPAYMENT_S.PAYMENTID)
                                 FROM MFPAYMENT MFPAYMENT_S
                                WHERE MFPAYMENT_S.CHECKID = MFPAYMENT.CHECKID);

CREATE OR REPLACE VIEW POS8_REP_REV_ARTICLE_ALL
AS
   SELECT mforder.overgroupid mforder_overgroupid,
          mforder.orderid mforder_orderid,
          ROUND (  mforder.total
                 - mforder.total * mforder.discountpercentage / 100,
                 5
                ) mforder_total,
          ROUND (  (  mforder.total
                    - mforder.total * mforder.discountpercentage / 100
                   )
                 * mforder.taxpercentage
                 / (100 + mforder.taxpercentage),
                 10
                ) mforder_taxamount,
          mfcheck.checktype mfcheck_checktype,
          mfcheck.reopenstat mfcheck_reopenstat,
          mforder.overgroupabbr mfovergroup_abbreviation,
          mforder.bookingdate mforder_bookingdate,
          mforder.discounted mforder_discounted,
          (SELECT mfovergroup.NAME
             FROM mfovergroup
            WHERE mforder.overgroupid =
                                     mfovergroup.overgroupid)
                                                             mfovergroup_name,
          mforder.ordertype mforder_ordertype,
          (SELECT mfoutlet.NAME
             FROM mfoutlet
            WHERE mforder.outletid = mfoutlet.outletid) mfoutlet_name,
          mforder.checkid mforder_checkid,
          pkg_kar.get_cover (mfcheck.checkid,
                             mfcheck.personcnt
                            ) mfcheck_personcnt,
          DECODE (mforder.discountpercentage,
                  100, 0,
                  ROUND (mforder.COUNT, 3)
                 ) mforder_count_sale,
          DECODE (mforder.discountpercentage,
                  100, ROUND (mforder.COUNT, 3),
                  0
                 ) mforder_count_hb,
          ROUND (mforder.COUNT, 3) mforder_count_all,
          mforder.discountpercentage mfoder_discountpercentage,
          mfcheck.checkid mfcheck_checkid, mforder.outletid mforder_outletid,
          mforder.artname mforder_artname,
          mforder.singleprice mforder_singleprice,
          mforder.orddatetime orddatetime, mforder.waiterid mforder_waiterid,
          mforder.waiterid mfwaiter_waiterid,
          SUBSTR (mforder.waitername,
                  1,
                  (INSTR (mforder.waitername, ',') - 1)
                 ) mfwaiter_lastname,
          mfcheck.outletid mfcheck_outletid,
          (SELECT mfoutlet.NAME
             FROM mfoutlet
            WHERE mfcheck.outletid = mfoutlet.outletid) mfcheck_outlet_name,
          mfcheck.checkdate mfcheck_checkdate,
          mforder.mealperiodid mfmealperiod_mealperiodid,
          (SELECT mfmealperiod.mpname
             FROM mfmealperiod
            WHERE mfmealperiod.mealperiodid =
                                     mforder.mealperiodid)
                                                          mfmealperiod_mpname,
          mforder.mealperiodid mforder_mealperiodid,
          mforder.maingroupid mfmaingroup_maingroupid,
          (SELECT mfmaingroup.NAME
             FROM mfmaingroup
            WHERE mforder.maingroupid =
                                     mfmaingroup.maingroupid)
                                                             mfmaingroup_name,
          (SELECT NAME
             FROM mfsubgroup
            WHERE mfsubgroup.subgroupid = mforder.subgroupid) mfsubgroup_name,
          NVL ((SELECT ACCOUNT
                  FROM mfsubgroup
                 WHERE mfsubgroup.subgroupid = mforder.subgroupid),
               '0'
              ) mfsubgroup_account,
          mforder.udarticleno mforder_udarticleno, mforder.COST mforder_cost,
          mforder.bookingdatev8 mforder_bookingdatev8,
          TO_NUMBER (SUBSTR (mforder.overgroupid, 4)) overgroupid,
          TO_NUMBER (SUBSTR (mforder.maingroupid, 4)) maingroupid,
          TO_NUMBER (SUBSTR (mforder.subgroupid, 4)) subgroupid,
          TO_NUMBER (SUBSTR (mforder.outletid, 4)) outletid,
          TO_NUMBER (SUBSTR (mfcheck.outletid, 4)) mfcheckoutletid,
          TO_NUMBER (SUBSTR (mforder.mealperiodid, 4)) mealperiodid,
          TO_NUMBER (SUBSTR (mforder.waiterid, 4)) waiterid
     FROM mforder, mfcheck
    WHERE (mforder.checkid = mfcheck.checkid) AND (mforder.ordertype <> 'G');

CREATE OR REPLACE VIEW POS8_REP_REV_ARTICLE_OVERGROUP
AS
   SELECT mforder.overgroupid mforder_overgroupid,
          mforder.orderid mforder_orderid,
          ROUND (  mforder.total
                 - mforder.total * mforder.discountpercentage / 100,
                 5
                ) mforder_total,
          ROUND (  (  mforder.total
                    - mforder.total * mforder.discountpercentage / 100
                   )
                 * mforder.taxpercentage
                 / (100 + mforder.taxpercentage),
                 10
                ) mforder_taxamount,
          mfcheck.checktype mfcheck_checktype,
          mfcheck.reopenstat mfcheck_reopenstat,
          mforder.overgroupabbr mfovergroup_abbreviation,
          mforder.bookingdate mforder_bookingdate,
          mforder.discounted mforder_discounted,
          (SELECT mfovergroup.NAME
             FROM mfovergroup
            WHERE mforder.overgroupid =
                                     mfovergroup.overgroupid)
                                                             mfovergroup_name,
          mforder.ordertype mforder_ordertype,
          (SELECT mfoutlet.NAME
             FROM mfoutlet
            WHERE mforder.outletid = mfoutlet.outletid) mfoutlet_name,
          mforder.checkid mforder_checkid,
          pkg_kar.get_cover (mfcheck.checkid,
                             mfcheck.personcnt
                            ) mfcheck_personcnt,
          (SELECT COUNT (checkid)
             FROM mfcheck
            WHERE (mfcheck.checkid = mforder.checkid)) COUNT,
          mfcheck.checkid mfcheck_checkid, mforder.outletid mforder_outletid,
          mforder.artname mforder_artname,
          ROUND (mforder.COUNT, 3) mforder_count,
          mforder.singleprice mforder_singleprice,
          mforder.orddatetime orddatetime, mforder.waiterid mforder_waiterid,
          mforder.waiterid mfwaiter_waiterid,
          SUBSTR (mforder.waitername,
                  1,
                  (INSTR (mforder.waitername, ',') - 1)
                 ) mfwaiter_lastname,
          mfcheck.outletid mfcheck_outletid,
          (SELECT mfoutlet.NAME
             FROM mfoutlet
            WHERE mfcheck.outletid = mfoutlet.outletid) mfcheck_outlet_name,
          mfcheck.checkdate mfcheck_checkdate,
          mforder.mealperiodid mfmealperiod_mealperiodid,
          (SELECT mfmealperiod.mpname
             FROM mfmealperiod
            WHERE mfmealperiod.mealperiodid =
                                     mforder.mealperiodid)
                                                          mfmealperiod_mpname,
          mforder.mealperiodid mforder_mealperiodid,
          mforder.maingroupid mfmaingroup_maingroupid,
          (SELECT mfmaingroup.NAME
             FROM mfmaingroup
            WHERE mforder.maingroupid =
                                     mfmaingroup.maingroupid)
                                                             mfmaingroup_name,
          (SELECT NAME
             FROM mfsubgroup
            WHERE mfsubgroup.subgroupid = mforder.subgroupid) mfsubgroup_name,
          NVL ((SELECT ACCOUNT
                  FROM mfsubgroup
                 WHERE mfsubgroup.subgroupid = mforder.subgroupid),
               '0'
              ) mfsubgroup_account,
          mforder.udarticleno mforder_udarticleno, mforder.COST mforder_cost,
          mforder.bookingdatev8 mforder_bookingdatev8,
          TO_NUMBER (SUBSTR (mforder.overgroupid, 4)) overgroupid,
          TO_NUMBER (SUBSTR (mforder.maingroupid, 4)) maingroupid,
          TO_NUMBER (SUBSTR (mforder.subgroupid, 4)) subgroupid,
          TO_NUMBER (SUBSTR (mforder.outletid, 4)) outletid,
          TO_NUMBER (SUBSTR (mfcheck.outletid, 4)) mfcheckoutletid,
          TO_NUMBER (SUBSTR (mforder.mealperiodid, 4)) mealperiodid,
          TO_NUMBER (SUBSTR (mforder.waiterid, 4)) waiterid
     FROM mforder, mfcheck
    WHERE (mforder.checkid = mfcheck.checkid)
      AND (mforder.discountpercentage < 100)
      AND (mforder.ordertype <> 'G');

CREATE OR REPLACE VIEW POS8_REP_REV_GROUPARTICLE
AS
   SELECT mforder.overgroupid mforder_overgroupid,
          mforder.orderid mforder_orderid,
          ROUND (  mforder.total
                 - mforder.total * mforder.discountpercentage / 100,
                 5
                ) mforder_total,
          ROUND (  (  mforder.total
                    - mforder.total * mforder.discountpercentage / 100
                   )
                 * mforder.taxpercentage
                 / (100 + mforder.taxpercentage),
                 10
                ) mforder_taxamount,
          mfcheck.checktype mfcheck_checktype,
          mfcheck.reopenstat mfcheck_reopenstat,
          mfovergroup.abbreviation mfovergroup_abbreviation,
          mforder.bookingdate mforder_bookingdate,
          mforder.discounted mforder_discounted,
          mfovergroup.NAME mfovergroup_name,
          mforder.ordertype mforder_ordertype, mfoutlet.NAME mfoutlet_name,
          mfparam.propertyname mfparam_propertyname,
          mforder.checkid mforder_checkid,
          pkg_kar.get_cover (mfcheck.checkid,
                             mfcheck.personcnt
                            ) mfcheck_personcnt,
          (SELECT COUNT (checkid)
             FROM mfcheck
            WHERE (mfcheck.checkid = mforder.checkid)) COUNT,
          mfcheck.checkid mfcheck_checkid, mforder.outletid mforder_outletid,
          mforder.artname mforder_artname,
          ROUND (mforder.COUNT, 3) mforder_count,
          mforder.singleprice mforder_singleprice,
          mforder.waiterid mforder_waiterid,
          SUBSTR (mforder.waitername,
                  1,
                  (INSTR (mforder.waitername, ',') - 1)
                 ) mfwaiter_lastname,
          mfcheck.outletid mfcheck_outletid,
          mfcheck.checkdate mfcheck_checkdate,
          mfmealperiod.mealperiodid mfmealperiod_mealperiodid,
          mfmealperiod.mpname mfmealperiod_mpname,
          mforder.mealperiodid mforder_mealperiodid,
          mfmaingroup.maingroupid mfmaingroup_maingroupid,
          mfmaingroup.NAME mfmaingroup_name,
          mforder.udarticleno mforder_udarticleno, mforder.COST mforder_cost,
          mforder.bookingdatev8 mforder_bookingdatev8,
          TO_NUMBER (SUBSTR (mfovergroup.overgroupid, 4)) overgroupid,
          TO_NUMBER (SUBSTR (mforder.outletid, 4)) outletid
     FROM mforder,
          mfcheck,
          mfovergroup,
          mfoutlet,
          mfparam,
          mfmaingroup,
          mfmealperiod
    WHERE (mforder.overgroupid = mfovergroup.overgroupid)
      AND (mforder.outletid = mfoutlet.outletid)
      AND (mfmealperiod.mealperiodid = mforder.mealperiodid)
      AND (mforder.checkid = mfcheck.checkid)
      AND (mforder.maingroupid = mfmaingroup.maingroupid)
      AND (mforder.discountpercentage < 100)
      AND (mforder.ordertype = 'G');


CREATE OR REPLACE VIEW POS8_REP_REV_TOTAL_DAY
AS
   SELECT mfcheck.checkid checkid,
          ROUND (  mforder.total
                 - mforder.total * mforder.discountpercentage / 100,
                 5
                ) mforder_total,
          ROUND (  (  mforder.total
                    - mforder.total * mforder.discountpercentage / 100
                   )
                 * mforder.taxpercentage
                 / (100 + mforder.taxpercentage),
                 10
                ) mforder_taxamount,
          (SELECT abbreviation
             FROM mfcurrency
            WHERE currencyid IN (SELECT defaultcurrencyid
                                   FROM mfparam)) currency,
          mforder.bookingdate bookingdate,
          mforder.bookingdatev8 bookingdatev8, mforder.outletabbr outletname,
          NVL (pkg_kar.get_cover (mfcheck.checkid, mfcheck.personcnt),
               0
              ) per_cnt,
          mforder.outletid mforder_outletid,
          TO_NUMBER (TO_CHAR (mforder.bookingdatev8, 'YYYY')) YEAR,
          TO_NUMBER (TO_CHAR (mforder.bookingdatev8, 'IW')) cweek,
          TO_CHAR (mforder.bookingdatev8, 'Day') weekday,
          TO_NUMBER (SUBSTR (mforder.outletid, 4)) outletid
     FROM mforder, mfcheck, mfovergroup
    WHERE (mforder.checkid = mfcheck.checkid)
      AND (mforder.overgroupid = mfovergroup.overgroupid)
      AND (mforder.discountpercentage < 100)
      AND (mforder.ordertype <> 'G')
      AND (mfovergroup.ispaidout = 'F');


CREATE OR REPLACE VIEW V8_REP_THAITAXREPORT
AS 
SELECT 
    zttl_id,
    zttl_type,
    zttl_wpca_id WPCA_ID,
    (select WPCA.WPCA_THAIRDNUMBER from wpca where WPCA.WPCA_ID=ZTTL_WPCA_ID) THAIRDNUMBER,
    (select WPCA.WPCA_THAIWSNUMBER from wpca where WPCA.WPCA_ID=ZTTL_WPCA_ID) THAIWSNUMBER,
    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;
