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

CREATE OR REPLACE FORCE VIEW V8_REP_QUEST_REP_SUM AS
SELECT
decode(nvl(SUM(AnswerValue)+SUM(MaxAnswerValue),0),0,0,ROUND(NVL(SUM(AnswerValue),1)/NVL(SUM(MaxAnswerValue),1)*100,2)) AVGReached,
NVL(ROUND(SUM(MaxAnswerValue),2),0) MaxAnswerSum,
NVL(ROUND(SUM(AnswerValue),2),0) AnswerSum,
qnrf_id,
QNRF_GUEST_XCMS_ID XCMS_ID,
QNRF_YRES_ID YRES_ID
FROM (
SELECT qnrf_id,
WQAN.WQAN_VALUE AnswerValue,
(SELECT MAX(WQAN.WQAN_VALUE) FROM wqan WHERE WQAN.WQAN_WQUE_ID = wque_id) MaxAnswerValue,
((WQAN.WQAN_VALUE * 100) / decode((SELECT max(WQAN.WQAN_VALUE)
FROM wqan WHERE WQAN.WQAN_WQUE_ID = wque_id),0,1,(SELECT max(WQAN.WQAN_VALUE)
FROM wqan WHERE WQAN.WQAN_WQUE_ID = wque_id))) PercAnswerValue,
QNRF.QNRF_ENTERED EnteredDate,
QNRF.QNRF_GUEST_XCMS_ID,
QNRF.QNRF_YRES_ID
--,qnra.*, wqan.*, wque.*, wqnl.*, wqug.*, wqnr.*, qnrf.*
FROM qnra, qnrf, wqan, wque, wqnl, wqug, wqnr
WHERE WQAN.WQAN_ID(+) = QNRA.QNRA_WQAN_ID
AND WQUE.WQUE_ID = WQNL.WQNL_WQUE_ID
AND WQNL.WQNL_ID = QNRA.QNRA_WQNL_ID
AND WQUG.WQUG_ID(+) = WQNL.WQNL_WQUG_ID
AND WQNR.WQNR_ID = WQNL.WQNL_WQNR_ID
AND QNRF.QNRF_ID = QNRA.QNRA_QNRF_ID
UNION
SELECT qnrf_id,
0 AnswerValue,
0 MaxAnswerValue,
0 PercAnswerValue,
QNRF_ENTERED EnteredDate,
QNRF_GUEST_XCMS_ID,
QNRF_YRES_ID FROM qnrf qnrf_1
WHERE NOT EXISTS (SELECT 1 FROM qnra WHERE qnra_qnrf_id = qnrf_1.qnrf_id)
)
GROUP BY qnrf_id,QNRF_GUEST_XCMS_ID,QNRF_YRES_ID;

CREATE OR REPLACE VIEW V8_REP_QUEST_GUEST_COMMENTS
AS 
SELECT  
     QNRF_ID, 
     WQNR_ID,
         (   x1.xcms_name1
          || DECODE (x1.xcms_name3, '', '', ', ' || x1.xcms_name3)
          || DECODE (x1.xcms_name2,
                     '', '',
                     DECODE (x1.xcms_name3, '', ', ', ' ') || x1.xcms_name2
                    )
         ) RESERVATIONNAME,
         (   x2.xcms_name1
          || DECODE (x2.xcms_name3, '', '', ', ' || x2.xcms_name3)
          || DECODE (x2.xcms_name2,
                     '', '',
                     DECODE (x2.xcms_name3, '', ', ', ' ') || x2.xcms_name2
                    )
         ) PROFILENAME,
    QNRF_FILLOUTDATE, 
    qnrf_entered, 
    qnrf_lastmodified, 
    qnrf_yres_id yres_id,
    YRES.YRES_EXPARRTIME ARRIVAL, 
    YRES.YRES_EXPDEPTIME DEPARTURE, 
    decode(nvl(YRES.YRES_YRMS_ID,0),0,(select YRMS.YRMS_SHORTDESC from ydet, yrms where YDET.YDET_YRMS_ID=YRMS.YRMS_ID and YDET.YDET_ID=YRES.YRES_CURRENT_YDET_ID),(select YRMS.YRMS_SHORTDESC from yrms where YRMS.YRMS_ID=YRES.YRES_YRMS_ID))YRMS_SHORTDESC, 
    decode(QNRF.QNRF_INPUTSOURCE,2,'Web entered',1,'Manual entered') INPUTSOURCE,
    QNRF.QNRF_COMMENT, 
    wqnr_shortdesc, 
    wqnr_longdesc,
    (select WQUG.WQUG_LONGDESC from wqug where WQUG.WQUG_ID=(select WQNL.WQNL_WQUG_ID from wqnl where WQNL.WQNL_WQNR_ID= QNRF.QNRF_WQNR_ID and rownum=1)) DEPARTMENT_GROUP
FROM qnrf,wqnr, yres, xcms x1, xcms x2
WHERE qnrf_wqnr_id = wqnr_id
     AND qnrf_yres_id = yres_id(+)
     AND yres_xcms_id = x1.xcms_id(+)
     AND qnrf_guest_xcms_id = x2.xcms_id(+)
     AND QNRF.QNRF_COMMENT is not null
ORDER BY qnrf_entered, qnrf_yres_id, qnrf_xcms_id;

CREATE OR REPLACE VIEW V8_ED_FOLIO_REV_ORI_ZDCO_PS
as select 
DAT,
DAT_J,
CDT,
min(ZPOS_ID) ZPOS_ID,
min(ZPOS_INTERNALGROUP_ID) ZPOS_INTERNALGROUP_ID,
minusi,
SUM(QTY) QTY,
GROSS_QTY,
SUM(GROSS) GROSS,
SUM(PAY) PAY,
SUM(REV) REV,
IS_RES,
IS_FA,
ORI_RES_FA_ID,
ARRIVAL,
DEPARTURE,
ZFAC_OR_YRES,
min(ZDCO_NUMERICDESC) ZDCO_NUMERICDESC,
SPAY,
SPAY_CL,
SPAY_NOCL,
SREV,
OPENA,
REAL_ORIGINATOR_NAME,
ORIGINATOR_NAME,
ORI_RES_FA_DESC,
FSPAY_CL,
DDESC,
ZDCO_OR_ZPOS,
INDICATE_ZPOS_ID,
TAXCODE_DESC,
MULTI_TAXCODE_EXISTS,
ZWIN_ID
from(
SELECT
  ZPOS_POSTDATE_S DAT,
  to_number(to_char(ZPOS_POSTDATE_S,'J')) DAT_J,
  (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,
nvl2((to_char( DEPARTURE,'J')),'-',NULL) MINUSI,
QTY,
GROSS_QTY,
  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=ZDCO_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=ZDCO_OR_ZPOS_S)),0),'0',decode((select ZPOS.ZPOS_ID from ZPOS where ZPOS.ZPOS_ID=ZDCO_OR_ZPOS_S),(select yrch_LONGDESC from yrch where yrch_id=(SELECT S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZDCO_OR_ZPOS_S)),
                                                         wtrl_translate('YRCH',(select S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZDCO_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=ZDCO_OR_ZPOS_S)),decode((select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZDCO_OR_ZPOS_S),(select yrch_foliotext from yrch where YRCH_ID=(select S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZDCO_OR_ZPOS_S)),wtrl_translate('YRCH',(SELECT S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZDCO_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=ZDCO_OR_ZPOS_S))),
                                                         2,decode((select ZPOS.zpos_descript from ZPOS where ZPOS_ID=ZDCO_OR_ZPOS_S),(select ZART.ZART_LONGDESC from ZART where ZART.ZART_ID=(select ZORG_TABLE_ID from ZORG where ZORG.ZORG_ZPOS_ID=ZDCO_OR_ZPOS_S)),wtrl_translate('ZART',(SELECT ZORG.ZORG_TABLE_ID from ZORG where ZORG.ZORG_ZPOS_ID=ZDCO_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=ZDCO_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=ZDCO_OR_ZPOS_S)),0),'0',decode((select ZPOS.ZPOS_ID from ZPOS where ZPOS.ZPOS_ID=ZDCO_OR_ZPOS_S),(select YPAC_LONGDESC from YPAC where YPAC_ID=(SELECT S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZDCO_OR_ZPOS_S)),
                                                         wtrl_translate('YPAC',(select S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZDCO_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=ZDCO_OR_ZPOS_S)),decode((select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZDCO_OR_ZPOS_S),(select YPAC_FOLIOTEXT from YPAC where YPAC_ID=(select S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZDCO_OR_ZPOS_S)),wtrl_translate('YPAC',(SELECT S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZDCO_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=ZDCO_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=ZPOS_ID_S)),0),'0',decode((select ZPOS.ZPOS_ID from ZPOS where ZPOS.ZPOS_ID=ZPOS_ID_S),(select ZDCO_LONGDESC from ZDCO where ZDCO_ID=(SELECT S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZPOS_ID_S)),
                                                         wtrl_translate('ZDCO',(select S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZPOS_ID_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=ZPOS_ID_S)),decode((select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=ZPOS_ID_S),(select ZDCO_LONGDESC from ZDCO where ZDCO_ID=(select S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=ZPOS_ID_S)),wtrl_translate('ZDCO',(SELECT S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=ZPOS_ID_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=ZPOS_ID_S))))
                                   ) DDESC,
   ZDCO_OR_ZPOS_S ZDCO_OR_ZPOS, 
  INDICATE_ZPOS_ID, 
    --decode(INDICATE_ZPOS_ID,1,(case when (select count( distinct Z2.ZPOS_ZTCD_ID) from zpos Z2 where Z2.ZPOS_INTERNALSPLIT_ZPOS_ID=ZDCO_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=ZDCO_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_ZDCO_ID=ZDCO_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_ZDCO_ID=ZDCO_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=ZDCO_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=ZDCO_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_ZDCO_ID=ZDCO_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_ZDCO_ID=ZDCO_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=ZDCO_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_ZDCO_ID=ZDCO_OR_ZPOS_S and Z3.ZPOS_ZWIN_ID=ZWIN_ID_S and Z5.ZPOS_INTERNALGROUP_ID = Z3.ZPOS_INTERNALGROUP_ID)>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,
sum(NTD.ZPOS_QUANTITY) QTY,
  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)))/(sum(NTD.ZPOS_QUANTITY)) GROSS_QTY, 
  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_ZDCO_ID,0),0,1,0) INDICATE_ZPOS_ID,
  DECODE(NVL(ntd.ZPOS_ZDCO_ID,0),0,ntd.ZPOS_ID,ntd.ZPOS_ZDCO_ID) ZDCO_OR_ZPOS_S,
  DECODE(NVL(ntd.ZPOS_ORIGINATED_YRES_ID,0),0,1,0) ZFAC_OR_YRES,
  ntd.ZPOS_ZDCO_ID ZDCO_ID_S
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, ntd.ZPOS_ZDCO_ID, NTD.ZPOS_SUMUPAMOUNT,
DECODE(NVL(ntd.ZPOS_ZDCO_ID,0),0,ntd.ZPOS_ID,ntd.ZPOS_ZDCO_ID), 
DECODE(NVL(ntd.ZPOS_ORIGINATED_YRES_ID,0),0,1,0), DECODE(NVL(ntd.ZPOS_ZDCO_ID,0),0,1,0)
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,
sum(NTD.ZPOS_QUANTITY) QTY,
  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)))/(sum(NTD.ZPOS_QUANTITY)) GROSS_QTY, 
  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_ZDCO_ID,0),0,1,0) INDICATE_ZPOS_ID,
  DECODE(NVL(ntd.ZPOS_ZDCO_ID,0),0,ntd.ZPOS_ID,ntd.ZPOS_ZDCO_ID) ZDCO_OR_ZPOS_S,
  DECODE(NVL(ntd.ZPOS_ORIGINATED_YRES_ID,0),0,1,0) ZFAC_OR_YRES,
  ntd.ZPOS_ZDCO_ID ZDCO_ID_S
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, ntd.ZPOS_ZDCO_ID, NTD.ZPOS_SUMUPAMOUNT,
DECODE(NVL(ntd.ZPOS_ZDCO_ID,0),0,ntd.ZPOS_ID,ntd.ZPOS_ZDCO_ID),
DECODE(NVL(ntd.ZPOS_ZDCO_ID,0),0,ntd.ZPOS_ID,ntd.ZPOS_ZDCO_ID), 
DECODE(NVL(ntd.ZPOS_ORIGINATED_YRES_ID,0),0,1,0), DECODE(NVL(ntd.ZPOS_ZDCO_ID,0),0,1,0))
WHERE ABS(nvl(REV_S,PAY_S))<>(DECODE((SELECT WUSS.WUSS_VALUE FROM WUSS WHERE WUSS.WUSS_XCMS_ID=0 AND WUSS.WUSS_NAME='Show 0 Postings on Billing Screen'),1,-1,0)) ORDER BY CDT ASC)
group by
DAT,DAT_J,CDT,minusi,GROSS_QTY,IS_RES,IS_FA,ORI_RES_FA_ID,ARRIVAL,DEPARTURE,ZFAC_OR_YRES,SPAY,SPAY_CL,SPAY_NOCL,SREV,OPENA,REAL_ORIGINATOR_NAME,ORIGINATOR_NAME,ORI_RES_FA_DESC,FSPAY_CL,DDESC,ZDCO_OR_ZPOS,INDICATE_ZPOS_ID,TAXCODE_DESC,MULTI_TAXCODE_EXISTS,ZWIN_ID;


CREATE OR REPLACE VIEW V8_ED_FOL_GRP_AMOUNT_ROOMREV
AS 
SELECT 
MIN_DAT_S DAT, 
1 CDT, 
MIN_ZPOS_ID_S ZPOS_ID, 
COUNTER_S QTY, 
GROSS_S GROSS, 
SUM_GROSS_S SUM_GROSS, 
NVL(PAY_S,0) PAY, 
(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 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 (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 (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 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 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 ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) OPENA, 
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 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 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 (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 (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 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 ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) END)) FSPAY_CL, 
ZWIN_ID_S ZWIN_ID, 
(decode(nvl((select ZORG.ZORG_ROLE from zorg where ZORG.ZORG_ZPOS_ID=MIN_ZPOS_ID_S),0),
                                                         0,(select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS.ZPOS_ID=MIN_ZPOS_ID_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=MIN_ZPOS_ID_S)),0),'0',decode((select ZPOS.ZPOS_DESCRIPT from zpos where ZPOS.ZPOS_ID=MIN_ZPOS_ID_S) ,(select zdco_longdesc from zdco where ZDCO_ID=(select ZPOS_ZDCO_ID from zpos where ZPOS.ZPOS_ID=MIN_ZPOS_ID_S)  and MIN_ZPOS_ID_S=(select ZORG.ZORG_ZPOS_ID from zorg where ZORG.ZORG_ZPOS_ID=MIN_ZPOS_ID_S)),
                                                         wtrl_translate('ZDCO', (select ZDCO_ID from zdco where zdco_id=(select zpos_zdco_id from zpos where zpos_id=MIN_ZPOS_ID_S)),'ZDCO_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=(select zpos.zpos_zwin_id from zpos where zpos_id=MIN_ZPOS_ID_S)))),(select ZPOS.ZPOS_DESCRIPT from zpos where zpos.zpos_id=MIN_ZPOS_ID_S)),decode((select ZPOS.ZPOS_DESCRIPT from zpos where zpos_id=MIN_ZPOS_ID_S),(select yrch_foliotext from yrch where YRCH_ID=(select zorg.zorg_table_id from zorg where ZORG.ZORG_ZPOS_ID=MIN_ZPOS_ID_S)),wtrl_translate('YRCH',(select ZORG.ZORG_TABLE_ID from zorg where ZORG.ZORG_ZPOS_ID=MIN_ZPOS_ID_S) ,'YRCH_FOLIOTEXT',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=(select ZWIN.ZWIN_INVOICE_TO_XCMS_ID from zwin where zwin.zwin_id=(select zpos_zwin_id from zpos where zpos_id=MIN_ZPOS_ID_S)))),(select ZPOS.ZPOS_DESCRIPT from zpos where zpos_id=MIN_ZPOS_ID_S))),
                                                         2,decode((select ZPOS.zpos_descript from ZPOS where ZPOS_ID=MIN_ZPOS_ID_S),(select ZART.ZART_LONGDESC from ZART where ZART.ZART_ID=(select ZORG_TABLE_ID from ZORG where ZORG.ZORG_ZPOS_ID=MIN_ZPOS_ID_S)),wtrl_translate('ZART',(SELECT ZORG.ZORG_TABLE_ID from ZORG where ZORG.ZORG_ZPOS_ID=MIN_ZPOS_ID_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=MIN_ZPOS_ID_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=MIN_ZPOS_ID_S)),0),'0',decode((select ZPOS.ZPOS_DESCRIPT from zpos where ZPOS.ZPOS_ID=MIN_ZPOS_ID_S) ,(select zdco_longdesc from zdco where ZDCO_ID=(select ZPOS_ZDCO_ID from zpos where ZPOS.ZPOS_ID=MIN_ZPOS_ID_S)  and MIN_ZPOS_ID_S=(select ZORG.ZORG_ZPOS_ID from zorg where ZORG.ZORG_ZPOS_ID=MIN_ZPOS_ID_S)),
                                                          wtrl_translate('ZDCO', (select ZDCO_ID from zdco where zdco_id=(select zpos_zdco_id from zpos where zpos_id=MIN_ZPOS_ID_S)),'ZDCO_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=(select zpos.zpos_zwin_id from zpos where zpos_id=MIN_ZPOS_ID_S)))),(select ZPOS.ZPOS_DESCRIPT from zpos where zpos.zpos_id=MIN_ZPOS_ID_S)),decode((select ZPOS.ZPOS_DESCRIPT from zpos where zpos_id=MIN_ZPOS_ID_S),(select yPAC_foliotext from YPAC where YPAC_ID=(select zorg.zorg_table_id from zorg where ZORG.ZORG_ZPOS_ID=MIN_ZPOS_ID_S)),wtrl_translate('YPAC',(select ZORG.ZORG_TABLE_ID from zorg where ZORG.ZORG_ZPOS_ID=MIN_ZPOS_ID_S) ,'YPAC_FOLIOTEXT',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=(select ZWIN.ZWIN_INVOICE_TO_XCMS_ID from zwin where zwin.zwin_id=(select zpos_zwin_id from zpos where zpos_id=MIN_ZPOS_ID_S)))),(select ZPOS.ZPOS_DESCRIPT from zpos where zpos_id=MIN_ZPOS_ID_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=MIN_ZPOS_ID_S)),0),'0',decode((select ZPOS.ZPOS_ID from ZPOS where ZPOS.ZPOS_ID=MIN_ZPOS_ID_S),(select ZDCO_LONGDESC from ZDCO where ZDCO_ID=(SELECT S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=MIN_ZPOS_ID_S)),
                                                         wtrl_translate('ZDCO',(select S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=MIN_ZPOS_ID_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=MIN_ZPOS_ID_S)),decode((select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=MIN_ZPOS_ID_S),(select ZDCO_LONGDESC from ZDCO where ZDCO_ID=(select S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=MIN_ZPOS_ID_S)),wtrl_translate('ZDCO',(SELECT S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=MIN_ZPOS_ID_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=MIN_ZPOS_ID_S))))
                                   ) DDESC, 
(SELECT ZPOS.ZPOS_COMMENT FROM ZPOS WHERE ZPOS.ZPOS_ID=MIN_ZPOS_ID_S) ZPOS_COMMENT, 
(SELECT ZPOS.ZPOS_DESCRIPT FROM ZPOS WHERE ZPOS.ZPOS_ID=MIN_ZPOS_ID_S) ZPOS_DESCRIPT 
FROM 
( 
SELECT 
  MIN(ZPOS.ZPOS_POSTDATE) MIN_DAT_S, 
  MAX(ZPOS.ZPOS_POSTDATE) MAX_DAT_S, 
  MIN(ZPOS.ZPOS_ID) MIN_ZPOS_ID_S, 
  COUNT(*) COUNTER_S, 
  (DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,(-ZPOS.zpos_unitprice*ZPOS.zpos_quantity),-(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),DECODE(ZPOS.ZPOS_CDT,5,0,ZPOS.zpos_sumupamount))) GROSS_S, 
  COUNT(*)*(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,(-ZPOS.zpos_unitprice*ZPOS.zpos_quantity),-(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),DECODE(ZPOS.ZPOS_CDT,5,0,ZPOS.zpos_sumupamount))) SUM_GROSS_S, 
  SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,NULL,(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)), NULL)) PAY_S, 
  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))) REV_S, 
  ZWIN.ZWIN_ID ZWIN_ID_S,
  ZWIN.ZWIN_INVOICE_TO_XCMS_ID INVOICE_S
FROM 
  ZPOS, 
  ZWIN, 
  ZDCO,
  ZORG 
WHERE 
ZORG.ZORG_ZPOS_ID(+)=ZPOS.ZPOS_ID AND
  (zpos_zall_id IS NULL OR zpos_zall_id=0) AND 
  ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND 
  zdco_id=ZPOS.zpos_zdco_id AND 
  ZPOS.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID(+) AND 
  NVL(ZDCO.ZDCO_SUBFUNCTION,0)=1 AND 
  NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND 
  ZPOS.ZPOS_CDT=1 
--AND ZWIN.ZWIN_ID=:paraw 
  AND 
  ABS((DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,(-ZPOS.zpos_unitprice*ZPOS.zpos_quantity),-(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),DECODE(ZPOS.ZPOS_CDT,5,0,ZPOS.zpos_sumupamount))))<>(DECODE((SELECT WUSS.WUSS_VALUE FROM WUSS WHERE WUSS.WUSS_XCMS_ID=0 AND WUSS.WUSS_NAME='Show 0 Postings on Billing Screen'),1,-1,0)) 
  GROUP BY 
  ZWIN.ZWIN_ID, ZWIN.ZWIN_INVOICE_TO_XCMS_ID,
  (DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,(-ZPOS.zpos_unitprice*ZPOS.zpos_quantity),-(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),DECODE(ZPOS.ZPOS_CDT,5,0,ZPOS.zpos_sumupamount))) 
 ) 
UNION ALL 
(select
DAT,
CDT,
min(ZPOS_ID) ZPOS_ID,
SUM(QTY) QTY,
GROSS,
SUM(SUM_GROSS) SUM_GROSS,
SUM(PAY) PAY,
SPAY,
SPAY_CL,
SPAY_NOCL,
SREV,
OPENA,
FSPAY_CL,
ZWIN_ID,
DDESC,
ZPOS_COMMENT,
ZPOS_DESCRIPT
from
(SELECT 
  ZPOS.ZPOS_POSTDATE DAT, 
  ZPOS.ZPOS_CDT CDT, 
  ZPOS.ZPOS_ID ZPOS_ID, 
  (DECODE(ZPOS.ZPOS_PAIDOUT,1,-1,1)*(ZPOS.ZPOS_QUANTITY))                                                                     QTY, 
  ((DECODE(ZPOS.zpos_cdt,5,0,DECODE(ZPOS.ZPOS_CDT,5,0,ZPOS.zpos_sumupamount)))/(DECODE(ZPOS.ZPOS_PAIDOUT,1,-1,1)*(ZPOS.ZPOS_QUANTITY)))              GROSS, 
  (DECODE(ZPOS.zpos_cdt,5,0,DECODE(ZPOS.ZPOS_CDT,5,0,ZPOS.zpos_sumupamount)))               SUM_GROSS, 
  (DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL)))                          PAY, 
  (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 ZPOS.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID)             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 (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)=2  AND ZPOS.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID)  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 (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)<>2  AND ZPOS.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID)  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 ZPOS.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID)      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 ZPOS.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID)-(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 ZPOS.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID) OPENA, 
   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 ZPOS.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID)-(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 ZPOS.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID)=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 (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)=2  AND ZPOS.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID),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 (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)=2  AND ZPOS.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID),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 ZPOS.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID)-(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 ZPOS.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID) END)) FSPAY_CL, 
   ZWIN.ZWIN_ID ZWIN_ID, 
    decode(nvl(ZORG.ZORG_ROLE,0),0, ZPOS_DESCRIPT,
                                     --  1,decode(nvl((select YRCH.YRCH_FOLIOTEXT from YRCH where YRCH.YRCH_ID=ZORG.ZORG_TABLE_ID),0),'0',decode(ZPOS.ZPOS_DESCRIPT,(select yrch_LONGDESC from yrch where yrch_id=zorg_table_id),wtrl_translate('YRCH',ZORG.ZORG_TABLE_ID,'YRCH_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=ZWIN.ZWIN_INVOICE_TO_XCMS_ID)),ZPOS.ZPOS_DESCRIPT),decode(ZPOS.ZPOS_DESCRIPT,(select yrch_foliotext from yrch where YRCH_ID=zorg_table_id),wtrl_translate('YRCH',ZORG.ZORG_TABLE_ID,'YRCH_FOLIOTEXT',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=ZWIN.ZWIN_INVOICE_TO_XCMS_ID)),ZPOS_DESCRIPT)),
                                       1,decode(nvl((select YRCH.YRCH_FOLIOTEXT from YRCH where YRCH.YRCH_ID=ZORG.ZORG_TABLE_ID),0),'0',decode(ZPOS.ZPOS_DESCRIPT,(select zdco_longdesc from zdco where ZDCO_ID=ZPOS_ZDCO_ID and ZPOS_ID=ZORG.ZORG_ZPOS_ID),wtrl_translate('ZDCO', ZDCO_ID,'ZDCO_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=ZWIN.ZWIN_INVOICE_TO_XCMS_ID)),ZPOS.ZPOS_DESCRIPT),decode(ZPOS.ZPOS_DESCRIPT,(select yrch_foliotext from yrch where YRCH_ID=zorg_table_id),wtrl_translate('YRCH',ZORG.ZORG_TABLE_ID,'YRCH_FOLIOTEXT',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=ZWIN.ZWIN_INVOICE_TO_XCMS_ID)),ZPOS_DESCRIPT)),
                                       2,decode(zpos_descript,(select ZART.ZART_LONGDESC from ZART where ZART.ZART_ID=ZORG_TABLE_ID),wtrl_translate('ZART',ZORG.ZORG_TABLE_ID,'ZART_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=ZWIN.ZWIN_INVOICE_TO_XCMS_ID)),ZPOS_DESCRIPT),
                                       3,decode(nvl((select YPAC.YPAC_FOLIOTEXT from YPAC where YPAC.YPAC_ID=ZORG.ZORG_TABLE_ID),0),'0',decode(ZPOS.ZPOS_DESCRIPT,(select zdco_longdesc from zdco where ZDCO_ID=ZPOS_ZDCO_ID and ZPOS_ID=ZORG.ZORG_ZPOS_ID),wtrl_translate('ZDCO', ZDCO_ID,'ZDCO_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=ZWIN.ZWIN_INVOICE_TO_XCMS_ID)),ZPOS.ZPOS_DESCRIPT),decode(ZPOS.ZPOS_DESCRIPT,(select YPAC_foliotext from YPAC where YPAC_ID=zorg_table_id),wtrl_translate('YPAC',ZORG.ZORG_TABLE_ID,'YPAC_FOLIOTEXT',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=ZWIN.ZWIN_INVOICE_TO_XCMS_ID)),ZPOS_DESCRIPT)),
                                       4,decode(nvl((select ZDCO.ZDCO_FOLIOTEXT from ZDCO where ZDCO.ZDCO_ID=ZORG.ZORG_TABLE_ID),0),'0',decode(ZPOS.ZPOS_DESCRIPT,(select ZDCO_LONGDESC from ZDCO where ZDCO_id=zorg_table_id),wtrl_translate('ZDCO',ZORG.ZORG_TABLE_ID,'ZDCO_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=ZWIN.ZWIN_INVOICE_TO_XCMS_ID)),ZPOS.ZPOS_DESCRIPT),decode(ZPOS.ZPOS_DESCRIPT,(select ZDCO_longdesc from ZDCO where ZDCO_ID=zorg_table_id),wtrl_translate('ZDCO',ZORG.ZORG_TABLE_ID,'ZDCO_FOLIOTEXT',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=ZWIN.ZWIN_INVOICE_TO_XCMS_ID)),ZPOS_DESCRIPT))
                                       )DDESC,  
   ZPOS.ZPOS_COMMENT ZPOS_COMMENT, 
   ZPOS.ZPOS_DESCRIPT ZPOS_DESCRIPT 
FROM 
  ZPOS, 
  ZWIN, 
  ZDCO,
  ZORG
WHERE 
ZORG.ZORG_ZPOS_ID(+)=ZPOS.ZPOS_ID AND
  (zpos_zall_id IS NULL OR zpos_zall_id=0) AND 
  ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND 
  zdco_id=ZPOS.zpos_zdco_id AND 
  ZPOS.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID(+) AND 
  NVL(ZDCO.ZDCO_SUBFUNCTION,0)<>1 AND 
  NVL(ZPOS.ZPOS_HIDDEN,0)=0 
--AND ZWIN.ZWIN_ID=:paraw 
  AND 
  ABS((DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,(-ZPOS.zpos_unitprice*ZPOS.zpos_quantity),-(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),DECODE(ZPOS.ZPOS_CDT,5,0,ZPOS.zpos_sumupamount))))<>(DECODE((SELECT WUSS.WUSS_VALUE FROM WUSS WHERE WUSS.WUSS_XCMS_ID=0 AND WUSS.WUSS_NAME='Show 0 Postings on Billing Screen'),1,-1,0))
)
GROUP by
DAT, CDT, GROSS, SPAY,SPAY_CL,SPAY_NOCL,SREV,PAY, OPENA,FSPAY_CL,ZWIN_ID,DDESC,ZPOS_COMMENT,ZPOS_DESCRIPT);

CREATE OR REPLACE VIEW V8_ED_FOL_GRP_AMT_ROOMREV_DAY
AS
(SELECT 
MIN_DAT_S DAT, 
1 CDT, 
MIN_ZPOS_ID_S ZPOS_ID, 
COUNTER_S QTY, 
GROSS_S GROSS, 
SUM_GROSS_S SUM_GROSS, 
NVL(PAY_S,0) PAY, 
(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 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 (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 (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 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 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 ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) OPENA, 
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 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 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 (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 (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 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 ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) END)) FSPAY_CL, 
ZWIN_ID_S ZWIN_ID, 
(decode(nvl((select ZORG.ZORG_ROLE from zorg where ZORG.ZORG_ZPOS_ID=MIN_ZPOS_ID_S),0),
                                                         0,(select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS.ZPOS_ID=MIN_ZPOS_ID_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=MIN_ZPOS_ID_S)),0),'0',decode((select ZPOS.ZPOS_ID from ZPOS where ZPOS.ZPOS_ID=MIN_ZPOS_ID_S),(select yrch_LONGDESC from yrch where yrch_id=(SELECT S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=MIN_ZPOS_ID_S)),
                                                         wtrl_translate('YRCH',(select S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=MIN_ZPOS_ID_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=MIN_ZPOS_ID_S)),decode((select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=MIN_ZPOS_ID_S),(select yrch_foliotext from yrch where YRCH_ID=(select S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=MIN_ZPOS_ID_S)),wtrl_translate('YRCH',(SELECT S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=MIN_ZPOS_ID_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=MIN_ZPOS_ID_S))),
                                                         2,decode((select ZPOS.zpos_descript from ZPOS where ZPOS_ID=MIN_ZPOS_ID_S),(select ZART.ZART_LONGDESC from ZART where ZART.ZART_ID=(select ZORG_TABLE_ID from ZORG where ZORG.ZORG_ZPOS_ID=MIN_ZPOS_ID_S)),wtrl_translate('ZART',(SELECT ZORG.ZORG_TABLE_ID from ZORG where ZORG.ZORG_ZPOS_ID=MIN_ZPOS_ID_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=MIN_ZPOS_ID_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=MIN_ZPOS_ID_S)),0),'0',decode((select ZPOS.ZPOS_ID from ZPOS where ZPOS.ZPOS_ID=MIN_ZPOS_ID_S),(select YPAC_LONGDESC from YPAC where YPAC_ID=(SELECT S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=MIN_ZPOS_ID_S)),
                                                         wtrl_translate('YPAC',(select S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=MIN_ZPOS_ID_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=MIN_ZPOS_ID_S)),decode((select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=MIN_ZPOS_ID_S),(select YPAC_FOLIOTEXT from YPAC where YPAC_ID=(select S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=MIN_ZPOS_ID_S)),wtrl_translate('YPAC',(SELECT S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=MIN_ZPOS_ID_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=MIN_ZPOS_ID_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=MIN_ZPOS_ID_S)),0),'0',decode((select ZPOS.ZPOS_ID from ZPOS where ZPOS.ZPOS_ID=MIN_ZPOS_ID_S),(select ZDCO_LONGDESC from ZDCO where ZDCO_ID=(SELECT S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=MIN_ZPOS_ID_S)),
                                                         wtrl_translate('ZDCO',(select S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=MIN_ZPOS_ID_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=MIN_ZPOS_ID_S)),decode((select ZPOS.ZPOS_DESCRIPT from ZPOS where ZPOS_ID=MIN_ZPOS_ID_S),(select ZDCO_LONGDESC from ZDCO where ZDCO_ID=(select S.zorg_table_id from ZORG S where S.ZORG_ZPOS_ID=MIN_ZPOS_ID_S)),wtrl_translate('ZDCO',(SELECT S.ZORG_TABLE_ID from ZORG S where S.ZORG_ZPOS_ID=MIN_ZPOS_ID_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=MIN_ZPOS_ID_S))))
                                   ) DDESC, 
(SELECT ZPOS.ZPOS_COMMENT FROM ZPOS WHERE ZPOS.ZPOS_ID=MIN_ZPOS_ID_S) ZPOS_COMMENT, 
(SELECT ZPOS.ZPOS_DESCRIPT FROM ZPOS WHERE ZPOS.ZPOS_ID=MIN_ZPOS_ID_S) ZPOS_DESCRIPT 
FROM 
( 
SELECT 
  MIN(ZPOS.ZPOS_POSTDATE) MIN_DAT_S, 
  MAX(ZPOS.ZPOS_POSTDATE) MAX_DAT_S, 
  MIN(ZPOS.ZPOS_ID) MIN_ZPOS_ID_S, 
  COUNT(*) COUNTER_S, 
  (DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,(-ZPOS.zpos_unitprice*ZPOS.zpos_quantity),-(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),DECODE(ZPOS.ZPOS_CDT,5,0,ZPOS.zpos_sumupamount))) GROSS_S, 
  COUNT(*)*(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,(-ZPOS.zpos_unitprice*ZPOS.zpos_quantity),-(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),DECODE(ZPOS.ZPOS_CDT,5,0,ZPOS.zpos_sumupamount))) SUM_GROSS_S, 
  SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,NULL,(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)), NULL)) PAY_S, 
  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))) REV_S, 
  ZWIN.ZWIN_ID ZWIN_ID_S ,
  ZWIN.ZWIN_INVOICE_TO_XCMS_ID INVOICE_S
FROM 
  ZPOS, 
  ZWIN, 
  ZDCO,
  ZORG 
WHERE 
ZORG.ZORG_ZPOS_ID(+)=ZPOS.ZPOS_ID AND
  (zpos_zall_id IS NULL OR zpos_zall_id=0) AND 
  ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND 
  zdco_id=ZPOS.zpos_zdco_id AND 
  ZPOS.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID(+) AND 
  NVL(ZDCO.ZDCO_SUBFUNCTION,0)=1 AND 
  NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND 
  ZPOS.ZPOS_CDT=1 
--AND ZWIN.ZWIN_ID=:paraw 
  AND 
  ABS((DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,(-ZPOS.zpos_unitprice*ZPOS.zpos_quantity),-(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),DECODE(ZPOS.ZPOS_CDT,5,0,ZPOS.zpos_sumupamount))))<>(DECODE((SELECT WUSS.WUSS_VALUE FROM WUSS WHERE WUSS.WUSS_XCMS_ID=0 AND WUSS.WUSS_NAME='Show 0 Postings on Billing Screen'),1,-1,0)) 
  GROUP BY 
  ZWIN.ZWIN_ID,ZWIN.ZWIN_INVOICE_TO_XCMS_ID, ZPOS.ZPOS_POSTDATE,
  (DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,(-ZPOS.zpos_unitprice*ZPOS.zpos_quantity),-(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),DECODE(ZPOS.ZPOS_CDT,5,0,ZPOS.zpos_sumupamount))) 
 ) 
 UNION ALL 
(select
DAT,
CDT,
min(ZPOS_ID) ZPOS_ID,
SUM(QTY) QTY,
GROSS,
SUM(SUM_GROSS) SUM_GROSS,
SUM(PAY) PAY,
SPAY,
SPAY_CL,
SPAY_NOCL,
SREV,
OPENA,
FSPAY_CL,
ZWIN_ID,
DDESC,
ZPOS_COMMENT,
ZPOS_DESCRIPT
from
(SELECT 
  ZPOS.ZPOS_POSTDATE DAT, 
  ZPOS.ZPOS_CDT CDT, 
  ZPOS.ZPOS_ID ZPOS_ID, 
  (DECODE(ZPOS.ZPOS_PAIDOUT,1,-1,1)*(ZPOS.ZPOS_QUANTITY))                                                                     QTY, 
 ((DECODE(ZPOS.zpos_cdt,5,0,DECODE(ZPOS.ZPOS_CDT,5,0,ZPOS.zpos_sumupamount)))/(DECODE(ZPOS.ZPOS_PAIDOUT,1,-1,1)*(ZPOS.ZPOS_QUANTITY)))             GROSS,  
  (DECODE(ZPOS.zpos_cdt,5,0,DECODE(ZPOS.ZPOS_CDT,5,0,ZPOS.zpos_sumupamount)))               SUM_GROSS, 
  (DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL)))                          PAY, 
  (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 ZPOS.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID)             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 (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)=2  AND ZPOS.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID)  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 (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)<>2  AND ZPOS.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID)  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 ZPOS.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID)      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 ZPOS.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID)-(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 ZPOS.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID) OPENA, 
   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 ZPOS.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID)-(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 ZPOS.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID)=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 (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)=2  AND ZPOS.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID),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 (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)=2  AND ZPOS.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID),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 ZPOS.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID)-(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 ZPOS.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID) END)) FSPAY_CL, 
   ZWIN.ZWIN_ID ZWIN_ID, 
  decode(nvl(ZORG.ZORG_ROLE,0),0, ZPOS_DESCRIPT,
                                       1,decode(nvl((select YRCH.YRCH_FOLIOTEXT from YRCH where YRCH.YRCH_ID=ZORG.ZORG_TABLE_ID),0),'0',decode(ZPOS.ZPOS_DESCRIPT,(select yrch_LONGDESC from yrch where yrch_id=zorg_table_id),wtrl_translate('YRCH',ZORG.ZORG_TABLE_ID,'YRCH_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=ZWIN.ZWIN_INVOICE_TO_XCMS_ID)),ZPOS.ZPOS_DESCRIPT),decode(ZPOS.ZPOS_DESCRIPT,(select yrch_foliotext from yrch where YRCH_ID=zorg_table_id),wtrl_translate('YRCH',ZORG.ZORG_TABLE_ID,'YRCH_FOLIOTEXT',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=ZWIN.ZWIN_INVOICE_TO_XCMS_ID)),ZPOS_DESCRIPT)),
                                       2,decode(zpos_descript,(select ZART.ZART_LONGDESC from ZART where ZART.ZART_ID=ZORG_TABLE_ID),wtrl_translate('ZART',ZORG.ZORG_TABLE_ID,'ZART_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=ZWIN.ZWIN_INVOICE_TO_XCMS_ID)),ZPOS_DESCRIPT),
                                       3,decode(nvl((select YPAC.YPAC_FOLIOTEXT from YPAC where YPAC.YPAC_ID=ZORG.ZORG_TABLE_ID),0),'0',decode(ZPOS.ZPOS_DESCRIPT,(select YPAC_LONGDESC from YPAC where YPAC_id=zorg_table_id),wtrl_translate('YPAC',ZORG.ZORG_TABLE_ID,'YPAC_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=ZWIN.ZWIN_INVOICE_TO_XCMS_ID)),ZPOS.ZPOS_DESCRIPT),decode(ZPOS.ZPOS_DESCRIPT,(select YPAC_foliotext from YPAC where YPAC_ID=zorg_table_id),wtrl_translate('YPAC',ZORG.ZORG_TABLE_ID,'YPAC_FOLIOTEXT',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=ZWIN.ZWIN_INVOICE_TO_XCMS_ID)),ZPOS_DESCRIPT)),
                                       4,decode(nvl((select ZDCO.ZDCO_FOLIOTEXT from ZDCO where ZDCO.ZDCO_ID=ZORG.ZORG_TABLE_ID),0),'0',decode(ZPOS.ZPOS_DESCRIPT,(select ZDCO_LONGDESC from ZDCO where ZDCO_id=zorg_table_id),wtrl_translate('ZDCO',ZORG.ZORG_TABLE_ID,'ZDCO_LONGDESC',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=ZWIN.ZWIN_INVOICE_TO_XCMS_ID)),ZPOS.ZPOS_DESCRIPT),decode(ZPOS.ZPOS_DESCRIPT,(select ZDCO_longdesc from ZDCO where ZDCO_ID=zorg_table_id),wtrl_translate('ZDCO',ZORG.ZORG_TABLE_ID,'ZDCO_FOLIOTEXT',(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=ZWIN.ZWIN_INVOICE_TO_XCMS_ID)),ZPOS_DESCRIPT))
                                       )DDESC, 
   ZPOS.ZPOS_COMMENT ZPOS_COMMENT, 
   ZPOS.ZPOS_DESCRIPT ZPOS_DESCRIPT 
FROM 
  ZPOS, 
  ZWIN, 
  ZDCO,
  ZORG
WHERE 
ZORG.ZORG_ZPOS_ID(+)=ZPOS.ZPOS_ID AND
  (zpos_zall_id IS NULL OR zpos_zall_id=0) AND 
  ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND 
  zdco_id=ZPOS.zpos_zdco_id AND 
  ZPOS.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID(+) AND 
  NVL(ZDCO.ZDCO_SUBFUNCTION,0)<>1 AND 
  NVL(ZPOS.ZPOS_HIDDEN,0)=0 
--AND ZWIN.ZWIN_ID=:paraw 
  AND 
  ABS((DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,(-ZPOS.zpos_unitprice*ZPOS.zpos_quantity),-(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),DECODE(ZPOS.ZPOS_CDT,5,0,ZPOS.zpos_sumupamount))))<>(DECODE((SELECT WUSS.WUSS_VALUE FROM WUSS WHERE WUSS.WUSS_XCMS_ID=0 AND WUSS.WUSS_NAME='Show 0 Postings on Billing Screen'),1,-1,0))
)
GROUP by
DAT, CDT, PAY, GROSS, SPAY,SPAY_CL,SPAY_NOCL,SREV, OPENA,FSPAY_CL,ZWIN_ID,DDESC,ZPOS_COMMENT,ZPOS_DESCRIPT));

CREATE OR REPLACE VIEW V8_REP_TABLERES1
AS 
SELECT 
  (SELECT RRST.RRST_NAME FROM RRST WHERE RRST.RRST_ID = RRES.RRES_RRST_ID) Restaurant, 
  RTAB.RTAB_NUMBER                 TableNumber, 
  RTAB.RTAB_DESCRIPTION      TableName, 
  V8_Sys_Tr_Getrestables(RRES.RRES_ID) ALL_TABLES, 
  TO_CHAR(RRES.RRES_STARTTIME,'hh24:mi')     StartTime, 
  TO_CHAR(RRES.RRES_EndTIME,'hh24:mi')       EndTime, 
  nvl((SELECT v8_rep_name.NAME2 FROM v8_rep_name WHERE v8_rep_name.XCMS_ID = RPRF.RPRF_XCMS_ID and rownum=1), RRES_DESCRIPTION)  GuestName, 
  (SELECT v8_rep_name.NAME3 FROM v8_rep_name WHERE v8_rep_name.XCMS_ID = RPRF.RPRF_XCMS_ID and rownum=1) GuestName_Greet, 
  V8_Fnc_Xpda_Tableres_Infos(RPRF.RPRF_XCMS_ID,1) GUEST_XPDA_SHORT, 
  V8_Fnc_Xpda_Tableres_Infos(RPRF.RPRF_XCMS_ID,2) GUEST_XPDA_LONG, 
  RPRF.RPRF_XCMS_ID, 
  RRES.RRES_DESCRIPTION            Description, 
  RRES.RRES_ID, 
  RRES.RRES_DATE, 
  RRES.RRES_STARTTIME, 
  RRES.RRES_ENDTIME, 
  RRES.RRES_PAX,
  NVL((SELECT SUM(rrgl_number) FROM RRGL,XCAC WHERE RRGL.RRGL_XCAC_ID=XCAC.XCAC_ID AND RRGL.RRGL_RRES_ID = RRES.RRES_ID AND XCAC.XCAC_REPORT_GROUP=1 ),0) TOTALKIDS_G1,
  NVL((SELECT SUM(rrgl_number) FROM RRGL,XCAC WHERE RRGL.RRGL_XCAC_ID=XCAC.XCAC_ID AND RRGL.RRGL_RRES_ID = RRES.RRES_ID AND XCAC.XCAC_REPORT_GROUP=2 ),0) TOTALKIDS_G2,
  NVL((SELECT SUM(rrgl_number) FROM RRGL,XCAC WHERE RRGL.RRGL_XCAC_ID=XCAC.XCAC_ID AND RRGL.RRGL_RRES_ID = RRES.RRES_ID AND XCAC.XCAC_REPORT_GROUP=3 ),0) TOTALKIDS_G3,
  NVL((SELECT SUM(RRGL_NUMBER) FROM RRGL WHERE RRGL_RRES_ID=RRES_ID),0) AS TOTALKIDS,
  RRES.RRES_RRST_ID, 
  RRES.RRES_DESCRIPTION, 
  RRES.RRES_TABLECARD, 
  RRES.RRES_WCOL_ID, 
  RRES.RRES_CANCELED,
  RRES.RRES_CRSSYSTEM, 
  (select yres_id from yres where yres.YRES_XCMS_ID = rprf.Rprf_XCMS_ID and rres.RRES_DATE between yres.YRES_EXPARRTIME and yres.YRES_EXPDEPTIME and rownum=1) YRES_ID, 
  (select yrms.YRMS_SHORTDESC from yres, yrms where yres.YRES_YRMS_ID = yrms.YRMS_ID and yres.YRES_XCMS_ID = rprf.RPRF_XCMS_ID and rres.RRES_DATE between yres.YRES_EXPARRTIME and yres.YRES_EXPDEPTIME and rownum=1) YRMS_SHORTDESC,
  RRES.RRES_NOAVAILREASON,
  nvl((select 1 from rdet rdet_s where rdet_s.rdet_rres_id=RRES.RRES_ID group by rdet_s.rdet_rres_id having count(rdet_s.rdet_id)>1),0) CHECK_MULTI_TABLES -- MH new field 12.11.2014,
  FROM 
  RRES, 
  RDET, 
  RPRF, 
  RTAB 
WHERE 
  RDET.RDET_RRES_ID(+) = RRES.RRES_ID AND 
  RDET.RDET_RTAB_ID = RTAB.RTAB_ID(+) AND 
  RPRF.RPRF_RRES_ID(+) = RRES.RRES_ID
ORDER BY RRES.RRES_DATE, Restaurant, RTAB.RTAB_NUMBER;

CREATE OR REPLACE VIEW POS8_REP_MASTERDATA_WAITER
AS 
SELECT MFWAITER.LASTNAME MFWAITER_LASTNAME,
            MFWAITER.FIRSTNAME MFWAITER_FIRSTNAME,
            MFWAITER.GREETING MFWAITER_GREETING,
            MFWAITER.WAITERNO MFWAITER_WAITERNO,
            MFWAITER.CODE MFWAITER_CODE,
            MFWAITER.MAXREPRINTCOUNT MFWAITER_MAXREPRINTCOUNT,
            MFWAITER.DDWAITER MFWAITER_DDWAITER,
            MFWAITER.MULTILOGON MFWAITER_MULTILOGON,
            MFWAITER.DEFAULTPERMISSION MFWAITER_DEFAULTPERMISSION,
            (select MFOUTLET.NAME from mfoutlet where MFOUTLET.OUTLETID = MFWAITER.DEFAULTOUTLETID)  MFOUTLET_NAME
       FROM MFWAITER, MFPARAM
      ORDER BY MFWAITER.LASTNAME;

