CREATE OR REPLACE VIEW V8_ED_CURRENCY_REV_INFO
AS 
SELECT 
  (select zfac.ZFAC_AUTONUMBER from zfac where zfac.ZFAC_ID = zpos.ZPOS_ZFAC_ID) AUTONUMBER, 
  zpos.ZPOS_POSTDATE DAT, 
  zpos.ZPOS_CDT CDT, 
  zpos.ZPOS_ID ZPOS_ID, 
  (case when ((zpos.ZPOS_DESCRIPT=zdco.ZDCO_LONGDESC) or zpos.ZPOS_DESCRIPT=(select yrch.YRCH_FOLIOTEXT from yrch,ydet where yrch.YRCH_ID=ydet.YDET_YRCH_ID and ydet.ydet_date=ZPOS.ZPOS_POSTDATE and ydet.YDET_YRES_ID=zpos.ZPOS_ORIGINATED_YRES_ID and rownum=1))  then (decode(zpos.ZPOS_DESCRIPT,(select yrch.YRCH_FOLIOTEXT from yrch,ydet where yrch.YRCH_ID=ydet.YDET_YRCH_ID and ydet.ydet_date=ZPOS.ZPOS_POSTDATE and ydet.YDET_YRES_ID=zpos.ZPOS_ORIGINATED_YRES_ID and rownum=1),WTRL_TRANSLATE('YRCH',(select yrch.YRCH_ID from yrch,ydet where yrch.YRCH_ID=ydet.YDET_YRCH_ID and ydet.ydet_date=ZPOS.ZPOS_POSTDATE and ydet.YDET_YRES_ID=zpos.ZPOS_ORIGINATED_YRES_ID and rownum=1),'YRCH_FOLIOTEXT',nvl((decode((select xcca.XCCA_XCMSTYPE from xcca,xcms where xcca.XCCA_ID=xcms.XCMS_XCCA_ID and xcms.XCMS_ID=zwin.ZWIN_INVOICE_TO_XCMS_ID),2,(select xcms.XCMS_WLAN_ID from xcms where xcms.XCMS_ID=zwin.ZWIN_INVOICE_TO_XCMS_ID),(select xcms.XCMS_WLAN_ID from xcms where xcms.XCMS_ID=zwin.ZWIN_INVOICE_TO_CONTACT_ID))),(select wlan.WLAN_ID from wlan where wlan.WLAN_DEFAULT=1))),(decode(nvl(zdco.ZDCO_FOLIOTEXT,' '),' ',WTRL_TRANSLATE('ZDCO',zdco.ZDCO_ID,'ZDCO_LONGDESC',nvl((decode((select xcca.XCCA_XCMSTYPE from xcca,xcms where xcca.XCCA_ID=xcms.XCMS_XCCA_ID and xcms.XCMS_ID=zwin.ZWIN_INVOICE_TO_XCMS_ID),2,(select xcms.XCMS_WLAN_ID from xcms where xcms.XCMS_ID=zwin.ZWIN_INVOICE_TO_XCMS_ID),(select xcms.XCMS_WLAN_ID from xcms where xcms.XCMS_ID=zwin.ZWIN_INVOICE_TO_CONTACT_ID))),(select wlan.WLAN_ID from wlan where wlan.WLAN_DEFAULT=1))),WTRL_TRANSLATE('ZDCO',zdco.ZDCO_ID,'ZDCO_FOLIOTEXT',nvl((decode((select xcca.XCCA_XCMSTYPE from xcca,xcms where xcca.XCCA_ID=xcms.XCMS_XCCA_ID and xcms.XCMS_ID=zwin.ZWIN_INVOICE_TO_XCMS_ID),2,(select xcms.XCMS_WLAN_ID from xcms where xcms.XCMS_ID=zwin.ZWIN_INVOICE_TO_XCMS_ID),(select xcms.XCMS_WLAN_ID from xcms where xcms.XCMS_ID=zwin.ZWIN_INVOICE_TO_CONTACT_ID))),(select wlan.WLAN_ID from wlan where wlan.WLAN_DEFAULT=1))))))) else zpos.ZPOS_DESCRIPT end) DDESC, 
  zdco.ZDCO_ID ZDCO_ID, 
  zdco.ZDCO_NUMERICDESC NDESC, 
  (decode(nvl(zdco.ZDCO_FOLIOTEXT,' '),' ',WTRL_TRANSLATE('ZDCO',zdco.ZDCO_ID,'ZDCO_LONGDESC',nvl(nvl((decode((select xcca.XCCA_XCMSTYPE from xcca,xcms where xcca.XCCA_ID=xcms.XCMS_XCCA_ID and xcms.XCMS_ID=zwin.ZWIN_INVOICE_TO_XCMS_ID),2,(select xcms.XCMS_WLAN_ID from xcms where xcms.XCMS_ID=zwin.ZWIN_INVOICE_TO_XCMS_ID),(select xcms.XCMS_WLAN_ID from xcms where xcms.XCMS_ID=zwin.ZWIN_INVOICE_TO_CONTACT_ID))),(select wlan.WLAN_ID from wlan where wlan.WLAN_DEFAULT=1)),(select wlan.WLAN_ID from wlan where wlan.WLAN_DEFAULT=1))),WTRL_TRANSLATE('ZDCO',zdco.ZDCO_ID,'ZDCO_FOLIOTEXT',nvl((decode((select xcca.XCCA_XCMSTYPE from xcca,xcms where xcca.XCCA_ID=xcms.XCMS_XCCA_ID and xcms.XCMS_ID=zwin.ZWIN_INVOICE_TO_XCMS_ID),2,(select xcms.XCMS_WLAN_ID from xcms where xcms.XCMS_ID=zwin.ZWIN_INVOICE_TO_XCMS_ID),(select xcms.XCMS_WLAN_ID from xcms where xcms.XCMS_ID=zwin.ZWIN_INVOICE_TO_CONTACT_ID))),(select wlan.WLAN_ID from wlan where wlan.WLAN_DEFAULT=1))))) LDESC, 
  (decode((select zpcf.ZPCF_CUSTOMORIGINATORNAME from zpcf where zpcf_zpos_id=zpos.ZPOS_ID),NULL,(SELECT v8_rep_name.NAME2 from v8_rep_name where v8_rep_name.XCMS_ID=zpos.ZPOS_ORIGINATED_XCMS_ID),(select zpcf.ZPCF_CUSTOMORIGINATORNAME from zpcf where zpcf_zpos_id=zpos.ZPOS_ID))) ORG_NAME, 
  (decode((select zpcf.ZPCF_CUSTOMORIGINATORNAME from zpcf where zpcf_zpos_id=zpos.ZPOS_ID),NULL,(SELECT v8_rep_name.NAME2 from v8_rep_name where v8_rep_name.XCMS_ID=zpos.ZPOS_ORIGINATED_XCMS_ID)||(DECODE((DECODE(nvl(zpos.ZPOS_ORIGINATED_ZFAC_ID,0),0,zpos.ZPOS_ORIGINATED_YRES_ID,zpos.ZPOS_ORIGINATED_ZFAC_ID)),(DECODE(nvl(zpos.ZPOS_ZFAC_ID,0),0,zpos.ZPOS_YRES_ID,zpos.ZPOS_ZFAC_ID)),'',(DECODE(nvl(zpos.ZPOS_ORIGINATED_ZFAC_ID,0),0,', '||(select yrms.YRMS_SHORTDESC from yrms,yres where yrms.YRMS_ID=yres.YRES_YRMS_ID and yres.YRES_ID=ZPOS.ZPOS_ORIGINATED_YRES_ID),', '||(select zfan.ZFAN_LONGDESC from zfan,zfac where zfan.ZFAN_ID=zfac.ZFAC_ZFAN_ID and zfac.ZFAC_ID=zpos.ZPOS_ORIGINATED_ZFAC_ID))))),(select zpcf.ZPCF_CUSTOMORIGINATORNAME from zpcf where zpcf_zpos_id=zpos.ZPOS_ID))) ORG_NAME_ROOM, 
  zwin.ZWIN_ID ZWIN_ID, 
  (DECODE(zpos.zpos_cdt,5,DECODE(zpos.zpos_paidout,1,to_number(NULL),zpos.zpos_unitprice*zpos.zpos_quantity),to_number(NULL))) LOCALAMOUNT, 
  (SELECT abs(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) COMMISSION, 
  zpos.ZPOS_GROSSFOREIGNPRICE FOREIGNAMOUNT, 
  (DECODE(zdco.ZDCO_XCHFEE,null,(DECODE(zpos.zpos_cdt,5,DECODE(zpos.zpos_paidout,1,to_number(NULL),(zpos.zpos_unitprice*zpos.zpos_quantity)),to_number(NULL))),(DECODE(zpos.zpos_cdt,5,DECODE(zpos.zpos_paidout,1,to_number(NULL),(zpos.zpos_unitprice*zpos.zpos_quantity)),to_number(NULL)))-(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))) PAIDAMOUNT, 
  zpos.ZPOS_REVERSEEXCHRATE	ZPOS_REVERSEEXCHRATE, 
  zpos.ZPOS_ZCUR_ID ZCUR_ID, 
  zcur.ZCUR_SHORTDESC CURRENCY_SHORT, 
  zcur.ZCUR_LONGDESC CURRENCY_LONG, 
  zdco.ZDCO_XCHFEE ZDCO_XCHFEE, 
  zdco.ZDCO_XCHFEETYPE ZDCO_XCHFEETYPE,
  zdco.ZDCO_XCHFEE_MULTIPLYBYCHEQUES ZDCO_XCHFEE_MULTIPLY, 
  zpos.ZPOS_COMMENT ZPOS_COMMENT, 
  ltrim((decode(zdco.ZDCO_XCHFEETYPE,1,to_char(zdco.ZDCO_XCHFEE,'99999990D99', 'NLS_NUMERIC_CHARACTERS = ''.,''')||' % ',2,(decode(zdco.ZDCO_XCHFEE_MULTIPLYBYCHEQUES,1,(select zz.ZPOS_QUANTITY from zpos zz where ZZ.ZPOS_ZWIN_ID=ZPOS.ZPOS_ZWIN_ID and ZZ.ZPOS_ZDCO_ID=ZDCO.ZDCO_XCHFEE_ZDCO_ID and rownum=1)||' x '||ltrim(to_char(zdco.ZDCO_XCHFEE,'99999990D99', 'NLS_NUMERIC_CHARACTERS = ''.,''')),to_char(zdco.ZDCO_XCHFEE,'99999990D99', 'NLS_NUMERIC_CHARACTERS = ''.,'''))),' % '))) EXCHANGEFEE, 
  ltrim((decode(zpos.ZPOS_REVERSEEXCHRATE,0,to_char(zpos.ZPOS_EXCHANGERATE,'99999990D999999', 'NLS_NUMERIC_CHARACTERS = ''.,'''),to_char((1/zpos.ZPOS_EXCHANGERATE),'99999990D999999', 'NLS_NUMERIC_CHARACTERS = ''.,''')))) EXCHANGERATE, 
  ltrim((decode(zpos.ZPOS_REVERSEEXCHRATE,1,to_char(zpos.ZPOS_EXCHANGERATE,'99999990D999999', 'NLS_NUMERIC_CHARACTERS = ''.,'''),to_char((1/zpos.ZPOS_EXCHANGERATE),'99999990D999999', 'NLS_NUMERIC_CHARACTERS = ''.,''')))) BACKRATE, 
  ltrim((decode(zdco.ZDCO_XCHFEETYPE,1,(decode(zpos.ZPOS_REVERSEEXCHRATE,0,to_char((zpos.ZPOS_EXCHANGERATE)*((100-zdco.ZDCO_XCHFEE)/100),'99999990D999999', 'NLS_NUMERIC_CHARACTERS = ''.,'''),to_char((1/zpos.ZPOS_EXCHANGERATE)*((100-zdco.ZDCO_XCHFEE)/100),'99999990D999999', 'NLS_NUMERIC_CHARACTERS = ''.,'''))),null))) COMM_EXCHANGERATE, 
  ltrim((decode(zdco.ZDCO_XCHFEETYPE,1,(decode(zpos.ZPOS_REVERSEEXCHRATE,1,to_char((zpos.ZPOS_EXCHANGERATE)*((100-zdco.ZDCO_XCHFEE)/100),'99999990D999999', 'NLS_NUMERIC_CHARACTERS = ''.,'''),to_char((1/zpos.ZPOS_EXCHANGERATE)*((100-zdco.ZDCO_XCHFEE)/100),'99999990D999999', 'NLS_NUMERIC_CHARACTERS = ''.,'''))),null))) COMM_BACKRATE, 
  (select zcur.ZCUR_SHORTDESC from ZCUR,ZPOS multi where multi.ZPOS_ZDCO_ID = zcur.ZCUR_ZDCO_ID and multi.zpos_zwin_id=zpos.zpos_zwin_id and multi.zpos_quantity<>1 and rownum=1) MULTI_CURRENCY_SHORT, 
  (select zcur.ZCUR_LONGDESC from ZCUR,ZPOS multi where multi.ZPOS_ZDCO_ID = zcur.ZCUR_ZDCO_ID and multi.zpos_zwin_id=zpos.zpos_zwin_id and multi.zpos_quantity<>1 and rownum=1) MULTI_CURRENCY_LONG, 
  ((select sum(multi.zpos_foreignprice) from zpos multi where multi.zpos_zwin_id=zpos.zpos_zwin_id and multi.zpos_quantity<>1)+nvl((select sum(multi.zpos_grossforeignprice*multi.zpos_quantity) from zpos multi where multi.zpos_zwin_id=zpos.zpos_zwin_id and multi.zpos_cdt=1),0)) MULIT_LOCALAMOUNT, 
  nvl((select sum(multi.zpos_grossforeignprice*multi.zpos_quantity) from zpos multi where multi.zpos_zwin_id=zpos.zpos_zwin_id and multi.zpos_cdt=1),0) MULTI_COMMISSION, 
  (select sum(multi.zpos_foreignprice) from zpos multi where multi.zpos_zwin_id=zpos.zpos_zwin_id and multi.zpos_quantity<>1) MULIT_PAIDAMOUNT, 
  ltrim(to_char((ltrim((decode(zpos.ZPOS_REVERSEEXCHRATE,1,zpos.ZPOS_EXCHANGERATE,(1/zpos.ZPOS_EXCHANGERATE)))))/(ltrim((decode((select sum(multi.ZPOS_REVERSEEXCHRATE) from zpos multi where multi.zpos_zwin_id=zpos.zpos_zwin_id and multi.zpos_quantity<>1),1,(select sum(multi.ZPOS_EXCHANGERATE) from zpos multi where multi.zpos_zwin_id=zpos.zpos_zwin_id and multi.zpos_quantity<>1),(1/(select sum(multi.ZPOS_EXCHANGERATE) from zpos multi where multi.zpos_zwin_id=zpos.zpos_zwin_id and multi.zpos_quantity<>1)))))),'99999990D999999', 'NLS_NUMERIC_CHARACTERS = ''.,''')) MULTI_BACKRATE 
FROM 
  ZPOS, 
  ZWIN, 
  ZDCO, 
  ZCUR 
WHERE 
  (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(zpos.ZPOS_HIDDEN,0)=0 AND 
  zpos.ZPOS_ZDCO_ID = zcur.ZCUR_ZDCO_ID(+) 
  order by zpos.ZPOS_ID asc;


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
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 V8_REP_RRES_INFOS
AS
SELECT 
  (SELECT RRST.RRST_NAME FROM RRST WHERE RRST.RRST_ID = RRES.RRES_RRST_ID) Restaurant,
  V8_Sys_Tr_Getrestables(RRES.RRES_ID) ALL_TABLES,
  V8_Sys_Tr_Getrestables(RRES.RRES_ID) TableNumber, 
  V8_Sys_Tr_GetrestableName(RRES.RRES_ID) TableName, 
  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 = V8_SYS_TR_GETFIRSTGUEST(rres_id) and rownum=1), RRES_DESCRIPTION)  GuestName, 
  (SELECT v8_rep_name.NAME3 FROM v8_rep_name WHERE v8_rep_name.XCMS_ID = V8_SYS_TR_GETFIRSTGUEST(rres_id) and rownum=1) GuestName_Greet,
  V8_SYS_TR_GETPROFILESINFO(rres_id, '; ') All_Names, 
  V8_Fnc_Xpda_Tableres_Infos(V8_SYS_TR_GETFIRSTGUEST(rres_id),1) GUEST_XPDA_SHORT,
  V8_Fnc_Xpda_Tableres_Infos(V8_SYS_TR_GETFIRSTGUEST(rres_id),2) GUEST_XPDA_LONG,  
  RRES.RRES_DESCRIPTION            Description,
  V8_SYS_TR_GETFIRSTGUEST(rres_id) RPRF_XCMS_ID,    
  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_LEFTTABLETIME,
  RRES.RRES_PLACEDTIME,
--  to_date(to_char(RRES_DATE, 'DD/MM/YYYY')   || ' ' || to_char(RRES_EndTIME, 'HH24:MI:SS'), 'DD/MM/YYYY HH24:MI:SS') as dt
  case 
    when  RRES_LEFTTABLETIME is not null and 
          to_date(to_char(RRES_DATE, 'DD/MM/YYYY')   || ' ' || 
          to_char(RRES_LEFTTABLETIME, 'HH24:MI:SS'), 'DD/MM/YYYY HH24:MI:SS') 
               < sysdate then 1 -- Already LEFT 
    when  RRES_LEFTTABLETIME is not null and 
          to_date(to_char(RRES_DATE, 'DD/MM/YYYY')   || ' ' || 
          to_char(RRES_LEFTTABLETIME, 'HH24:MI:SS'), 'DD/MM/YYYY HH24:MI:SS') 
               > sysdate then 2 -- LEFT IN THE FUTURE 
    when  RRES_PLACEDTIME is not null and 
          RRES_LEFTTABLETIME is  null and 
          RRES_DATE = trunc(sysdate) then 2  -- Reservation is SEATED now
    when  RRES_PLACEDTIME is null and 
          RRES_DATE = trunc(sysdate) then 3  -- Reservation is EXPECTED today
    when  RRES_DATE > trunc(sysdate) then 4  -- Future Reservation
    else  null
  end as SeatedStatus,
  V8_FNC_NUM_TO_CHAR_FORMATING(RRES.RRES_PRICE) PRICE_PERPAX,
  V8_FNC_NUM_TO_CHAR_FORMATING(RRES.RRES_PRICE*RRES.RRES_PAX) PRICE_TOTAL      
FROM 
  RRES
ORDER BY RRES.RRES_DATE, Restaurant, ALL_TABLES;


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


CREATE OR REPLACE VIEW V8_REP_REVENUE_HISTFOR_CAD
AS 
select "WDAT_DATE","WDAT_DATE2","WDAT_DATE3","DAYS","MONTHYEAR","FUTURE","YCAD_ID","NOOFROOMS","OOO","NOOFROOMS_TO_SELL","NOOFBEDS","OCC_ROOMS_DEF","OCC_ROOMS_TENT","ARR_ROOMS_DEF_TENT","ARR_ROOMS_DEF","DEP_ROOMS_DEF_TENT","DEP_ROOMS_DEF","OCC_ADULTS_DEF","OCC_ADULTS_TENT","OCC_CHILDS_DEF","OCC_CHILDS_TENT","DEV_ROOM_REV_GROSS","TENT_ROOM_REV_GROSS","DEV_ROOM_REV_NET","TENT_ROOM_REV_NET","DEV_FB_REV_GROSS","TENT_FB_REV_GROSS","DEV_FB_REV_NET","TENT_FB_REV_NET","DEV_OTHER_REV_GROSS","TENT_OTHER_REV_GROSS","DEV_OTHER_REV_NET","TENT_OTHER_REV_NET" from
(
select 
WDAT.WDAT_DATE WDAT_DATE, 
WDAT.WDAT_DATE WDAT_DATE2,
WDAT.WDAT_DATE WDAT_DATE3,
to_char(WDAT.WDAT_DATE,'DD')DAYS, -- New Field
TO_CHAR(WDAT.WDAT_DATE,'yyyy_mm') MonthYear, 
WDAT.FUTURE FUTURE,
WDAT.YCAD_ID,
SMAD.SMAD_ROOMSINHOTEL_D NOOFROOMS, 
SMAD.SMAD_ROOMS_OOO_D OOO,
SMAD.SMAD_ROOMSINHOTEL_D-SMAD.SMAD_ROOMS_OOO_D NOOFROOMS_TO_SELL, 
SMAD.SMAD_BEDSINHOTEL_D NOOFBEDS, -- CVZ New field
SMAD.SMAD_ROOMS_OCC_D OCC_ROOMS_DEF, 
0 OCC_ROOMS_TENT,
SMAD.SMAD_ARR_ROOMS_D ARR_ROOMS_DEF_TENT, 
SMAD.SMAD_ARR_ROOMS_D ARR_ROOMS_DEF, 
SMAD.SMAD_DEP_ROOMS_D DEP_ROOMS_DEF_TENT, 
SMAD.SMAD_DEP_ROOMS_D DEP_ROOMS_DEF, 
SMAD.SMAD_ADULTS_D OCC_ADULTS_DEF, 
0  OCC_ADULTS_TENT,
SMAD.SMAD_CHILDS_D OCC_CHILDS_DEF, 
0 OCC_CHILDS_TENT, 
NVL(SMAD.SMAD_ROOMREVGROSS_D,0) DEV_ROOM_REV_GROSS,
0 TENT_ROOM_REV_GROSS,
NVL(SMAD.SMAD_ROOMREVNET_D,0) DEV_ROOM_REV_NET,
0 TENT_ROOM_REV_NET,
NVL(SMAD.SMAD_FBREVGROSS_D,0) DEV_FB_REV_GROSS,
0 TENT_FB_REV_GROSS,
NVL(SMAD.SMAD_FBREVNET_D,0) DEV_FB_REV_NET,
0 TENT_FB_REV_NET,
NVL(SMAD.SMAD_MINIREVGROSS_D,0)+NVL(SMAD.SMAD_TELEREVGROSS_D,0)+NVL(SMAD.SMAD_MISCREVGROSS_D,0) DEV_OTHER_REV_GROSS,
0 TENT_OTHER_REV_GROSS,
NVL(SMAD.SMAD_MINIREVNET_D,0)+NVL(SMAD.SMAD_TELEREVNET_D,0)+NVL(SMAD.SMAD_MISCREVNET_D,0) DEV_OTHER_REV_NET,
0 TENT_OTHER_REV_NET
 from
V8_REP_WDAT_DATE_CAD WDAT,SMAD
 where
 WDAT.WDAT_DATE=SMAD.SMAD_DATE
 and
 wdat.YCAD_ID=SMAD.SMAD_YCAD_ID
)
union all
(
select
S_POSDATE                                                                     WDAT_DATE, 
S_POSDATE                                                                     WDAT_DATE2, 
S_POSDATE                                                                     WDAT_DATE3, 
to_char(S_POSDATE,'DD')                                               DAYS, -- New Field
TO_CHAR(S_POSDATE,'yyyy_mm')                                     MONTHYEAR,
1                                                                                     FUTURE, 
S_YCAD_ID                                                                      YCAD_ID,
sum(S_NOOFROOMS)                                                       NOOFROOMS, 
sum(S_OOO)                                                                    OOO,
sum(S_NOOFROOMS_TO_SELL)                                                      NOOFROOMS_TO_SELL,
sum(S_NOOFBEDS)                                                           NOOFBEDS, --CVZ New field
sum(S_OCC_ROOMS_DEF)                                                          OCC_ROOMS_DEF,
sum(S_OCC_ROOMS_TENT)                                                         OCC_ROOMS_TENT,  
sum(S_ARR_ROOMS_DEF_TENT)                                                     ARR_ROOMS_DEF_TENT,
sum(S_ARR_ROOMS_DEF)                                                          ARR_ROOMS_DEF,
--sum(S_ARR_ADULT_DEF_TENT)                                                     ARR_ADULT_DEF_TENT,
sum(S_DEP_ROOMS_DEF_TENT)                                                     DEP_ROOMS_DEF_TENT,
--sum(S_DEP_ADULTS_DEF_TENT)                                                    DEP_ADULTS_DEF_TENT,
sum(S_DEP_ROOMS_DEF)                                                          DEP_ROOMS_DEF,
sum(S_OCC_ADULTS_DEF)                                                         OCC_ADULTS_DEF,
sum(S_OCC_ADULTS_TENT)                                                        OCC_ADULTS_TENT,
sum(S_OCC_CHILDS_DEF)                                                         OCC_CHILDS_DEF,
sum(S_OCC_CHILDS_TENT)                                                        OCC_CHILDS_TENT,
sum(S_DEV_ROOM_REV_GROSS)                                                     DEV_ROOM_REV_GROSS,
sum(S_TENT_ROOM_REV_GROSS)                                                    TENT_ROOM_REV_GROSS,
sum(S_DEV_ROOM_REV_NET)                                                       DEV_ROOM_REV_NET,
sum(S_TENT_ROOM_REV_NET)                                                      TENT_ROOM_REV_NET,
sum(S_DEV_FB_REV_GROSS)                                                       DEV_FB_REV_GROSS,
sum(S_TENT_FB_REV_GROSS)                                                      TENT_FB_REV_GROSS,
sum(S_DEV_FB_REV_NET)                                                         DEV_FB_REV_NET,
sum(S_TENT_FB_REV_NET)                                                        TENT_FB_REV_NET,
sum(S_DEV_OTHER_REV_GROSS)                                                    DEV_OTHER_REV_GROSS,
sum(S_TENT_OTHER_REV_GROSS)                                                   TENT_OTHER_REV_GROSS,
sum(S_DEV_OTHER_REV_NET)                                                      DEV_OTHER_REV_NET,
sum(S_TENT_OTHER_REV_NET)                                                     TENT_OTHER_REV_NET
from
(
select
V8_SYS_NOOFROOMS_COMBINED_CAD.WDAT_DATE                                      S_POSDATE,
V8_SYS_NOOFROOMS_COMBINED_CAD.YCAD_ID                                        S_YCAD_ID,                                       
V8_SYS_NOOFROOMS_COMBINED_CAD.NOOFROOMS                                      S_NOOFROOMS,
nvl(V8_SYS_NOOFROOMS_COMBINED_CAD.OOO,0)                                     S_OOO,
V8_SYS_NOOFROOMS_COMBINED_CAD.NOOFROOMS-nvl(V8_SYS_NOOFROOMS_COMBINED_CAD.OOO,0)     S_NOOFROOMS_TO_SELL, 
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_NOT_USED
from
V8_SYS_NOOFROOMS_COMBINED_CAD
union all
select
V8_REP_NOOFROOMS_COMBINED_CAD.WDAT_DATE         S_POSDATE,
V8_REP_NOOFROOMS_COMBINED_CAD.YCAD_ID       S_YCAD_ID,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
V8_REP_NOOFROOMS_COMBINED_CAD.NOOFBEDS            S_NOOFBEDS, -- CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_NOT_USED
from
V8_REP_NOOFROOMS_COMBINED_CAD
union all
select
WDAT.WDAT_DATE                                                               S_POSDATE,
WDAT.YCAD_ID                                                       S_YCAD_ID,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
nvl(S_SYS_YBLD_OCC_CAD.S_DEFNOOFROOMS,0)+nvl(S_SYS_YDET_OCC_WB_CAD.S_DEFNOOFROOMS,0) S_OCC_ROOMS_DEF, 
nvl(S_SYS_YBLD_OCC_CAD.S_TENTNOOFROOMS,0)+nvl(S_SYS_YDET_OCC_WB_CAD.S_TENTNOOFROOMS,0)-nvl(S_SYS_YDET_OCC_WB_CAD.S_TENTBLOCKDEFRESROOM,0) S_OCC_ROOMS_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_NOT_USED
 from 
V8_REP_WDAT_DATE_CAD wdat,
( select 
V8_SYS_YBLD_OCC_CAD.YBLD_DATE S_YBLD_DATE,
V8_SYS_YBLD_OCC_CAD.YCAD_ID S_YCAD_ID,
sum(V8_SYS_YBLD_OCC_CAD.DEFNOOFROOMS) S_DEFNOOFROOMS, 
sum(V8_SYS_YBLD_OCC_CAD.TENTNOOFROOMS) S_TENTNOOFROOMS
 from V8_SYS_YBLD_OCC_CAD
 group by V8_SYS_YBLD_OCC_CAD.YBLD_DATE,V8_SYS_YBLD_OCC_CAD.YCAD_ID) S_SYS_YBLD_OCC_CAD,
 ( select 
V8_SYS_YDET_OCC_WB_CAD.YDET_DATE S_YDET_DATE,
V8_SYS_YDET_OCC_WB_CAD.YCAD_ID S_YCAD_ID,
sum(V8_SYS_YDET_OCC_WB_CAD.DEFNOOFROOMS) S_DEFNOOFROOMS, 
sum(V8_SYS_YDET_OCC_WB_CAD.TENTNOOFROOMS) S_TENTNOOFROOMS,
sum(V8_SYS_YDET_OCC_WB_CAD.TENTBLOCKDEFRESROOM) S_TENTBLOCKDEFRESROOM
 from V8_SYS_YDET_OCC_WB_CAD
 group by V8_SYS_YDET_OCC_WB_CAD.YDET_DATE,V8_SYS_YDET_OCC_WB_CAD.YCAD_ID ) S_SYS_YDET_OCC_WB_CAD
 where
 WDAT.WDAT_DATE=S_SYS_YBLD_OCC_CAD.S_YBLD_DATE(+) and
 WDAT.WDAT_DATE=S_SYS_YDET_OCC_WB_CAD.S_YDET_DATE(+) and
 wdat.YCAD_ID=S_SYS_YBLD_OCC_CAD.S_YCAD_ID (+) and
 wdat.ycad_id=S_SYS_YDET_OCC_WB_CAD.S_YCAD_ID (+)
 union all
select 
V8_SYS_HS_ARR_DEP_COMB_SMA_CAD.WDAT_DATE                                       S_POSDATE,
V8_SYS_HS_ARR_DEP_COMB_SMA_CAD.YCAD_ID      S_YCAD_ID,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
V8_SYS_HS_ARR_DEP_COMB_SMA_CAD.ARREXPROOM                                      S_ARR_ROOMS_DEF_TENT,
V8_SYS_HS_ARR_DEP_COMB_SMA_CAD.ARREXPADULT                                     S_ARR_ADULT_DEF_TENT,
V8_SYS_HS_ARR_DEP_COMB_SMA_CAD.DEPEXPROOM                                      S_DEP_ROOMS_DEF_TENT, 
V8_SYS_HS_ARR_DEP_COMB_SMA_CAD.DEPEXPADULT                                     S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_NOT_USED
 from V8_SYS_HS_ARR_DEP_COMB_SMA_CAD
 union all
select 
V8_SYS_HS_ARR_ALLROOM_CAD.WDAT_DATE                                              S_POSDATE,
V8_SYS_HS_ARR_ALLROOM_CAD.YCAD_ID                S_YCAD_ID,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
V8_SYS_HS_ARR_ALLROOM_CAD.DEFEXPROOM                                             S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_NOT_USED
 from V8_SYS_HS_ARR_ALLROOM_CAD
union all
select 
V8_SYS_HS_DEP_ALLROOM_CAD.WDAT_DATE                                              S_POSDATE,
V8_SYS_HS_DEP_ALLROOM_CAD.YCAD_ID                 S_YCAD_ID,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
V8_SYS_HS_DEP_ALLROOM_CAD.DEFDEPROOM                                             S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_NOT_USED
 from V8_SYS_HS_DEP_ALLROOM_CAD
union all
select
WDAT.WDAT_DATE                                                               S_POSDATE,
WDAT.YCAD_ID                                                       S_YCAD_ID,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
nvl(S_SYS_YDET_ADULT_WB_CAD. S_DEFADULTNO,0)-nvl(S_SYS_YDET_ADULT_WB_CAD.S_DEFBLOCKSUBSTRACT,0)+nvl(S_SYS_YBLD_OCC_CAD.S_DEFNOOFADULTS,0)    S_OCC_ADULTS_DEF, 
nvl(S_SYS_YDET_ADULT_WB_CAD. S_TENTADULTNO,0)-nvl(S_SYS_YDET_ADULT_WB_CAD.S_TENTBLOCKSUBSTRACT,0)+nvl(S_SYS_YBLD_OCC_CAD.S_TENTNOOFADULTS,0) S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_NOT_USED
 from 
V8_REP_WDAT_DATE_CAD wdat,
( select 
V8_SYS_YBLD_OCC_CAD.YBLD_DATE S_YBLD_DATE,
V8_SYS_YBLD_OCC_CAD.YCAD_ID S_YCAD_ID,
sum(V8_SYS_YBLD_OCC_CAD.DEFNOOFADULTS) S_DEFNOOFADULTS, 
sum(V8_SYS_YBLD_OCC_CAD.TENTNOOFADULTS) S_TENTNOOFADULTS
 from V8_SYS_YBLD_OCC_CAD
 group by V8_SYS_YBLD_OCC_CAD.YBLD_DATE,V8_SYS_YBLD_OCC_CAD.YCAD_ID) S_SYS_YBLD_OCC_CAD,
 ( select 
V8_SYS_YDET_ADULT_WB_CAD.YDET_DATE S_YDET_DATE,
V8_SYS_YDET_ADULT_WB_CAD.YCAD_ID S_YCAD_ID,
sum(V8_SYS_YDET_ADULT_WB_CAD.DEFADULTNO) S_DEFADULTNO, 
sum(V8_SYS_YDET_ADULT_WB_CAD.TENTADULTNO) S_TENTADULTNO,
sum(V8_SYS_YDET_ADULT_WB_CAD.DEFBLOCKSUBSTRACT) S_DEFBLOCKSUBSTRACT,
sum(V8_SYS_YDET_ADULT_WB_CAD.TENTBLOCKSUBSTRACT) S_TENTBLOCKSUBSTRACT
 from V8_SYS_YDET_ADULT_WB_CAD
 group by V8_SYS_YDET_ADULT_WB_CAD.YDET_DATE,V8_SYS_YDET_ADULT_WB_CAD.YCAD_ID ) S_SYS_YDET_ADULT_WB_CAD
 where
 WDAT.WDAT_DATE=S_SYS_YBLD_OCC_CAD.S_YBLD_DATE(+) and
 WDAT.WDAT_DATE=S_SYS_YDET_ADULT_WB_CAD.S_YDET_DATE(+) and
 wdat.ycad_id=S_SYS_YBLD_OCC_CAD.S_YCAD_ID (+) and
 WDAT.YCAD_ID=S_SYS_YDET_ADULT_WB_CAD.S_YCAD_ID (+)
union all
select 
YDET.YDET_DATE                                                               S_POSDATE,
YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID                                               S_YCAD_ID,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
sum(decode(YRES.YRES_RESSTATUS,1,YRGL.YRGL_NUMBER*YDET.YDET_NOOFROOMS,0))    S_OCC_CHILDS_DEF,
sum(decode(YRES.YRES_RESSTATUS,0,YRGL.YRGL_NUMBER*YDET.YDET_NOOFROOMS,0))    S_OCC_CHILDS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_NOT_USED
from YDET,YRGL,YRES,XCAC,YCAT 
where 
YRGL.YRGL_YDET_ID=YDET.YDET_ID  and 
YRES.YRES_ID=YDET.YDET_YRES_ID and 
XCAC.XCAC_ID=YRGL.YRGL_XCAC_ID and
YDET.YDET_YCAT_ID = YCAT.YCAT_ID
GROUP BY YDET.YDET_DATE, YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID
union all
select 
V.YPOS_DATE                                                                  S_POSDATE,
V.YCAD_ID                                                              S_YCAD_ID,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
V.DEF_REV_GROSS                                                              S_DEV_ROOM_REV_GROSS,
V.TENT_REV_GROSS                                                             S_TENT_ROOM_REV_GROSS,
V.DEF_REV_NET                                                                S_DEV_ROOM_REV_NET,
V.TENT_REV_NET                                                               S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_NOT_USED
from
V8_REP_YPOS_FC_STATS1_CAD V 
union all
select 
V.YPOS_DATE                                                                  S_POSDATE,
V.YCAD_ID                                                              S_YCAD_ID,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
V.DEF_REV_GROSS                                                              S_DEV_FB_REV_GROSS,
V.TENT_REV_GROSS                                                             S_TENT_FB_REV_GROSS,
V.DEF_REV_NET                                                                S_DEV_FB_REV_NET,
V.TENT_REV_NET                                                               S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_NOT_USED
from
V8_REP_YPOS_FC_STATS2_CAD V
union all
select 
V.YPOS_DATE                                                                  S_POSDATE,
V.YCAD_ID                                                              S_YCAD_ID,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
V.DEF_REV_GROSS                                                              S_DEV_OTHER_REV_GROSS,
V.TENT_REV_GROSS                                                             S_TENT_OTHER_REV_GROSS,
V.DEF_REV_NET                                                                S_DEV_OTHER_REV_NET,
V.TENT_REV_NET                                                               S_TENT_OTHER_REV_NET,
0                                                                            S_NOT_USED
from
V8_REP_YPOS_FC_STATS345_CAD V 
union all
select 
WDAT_DATE                                                                    S_POSDATE,
YCAD_ID                                                                 S_YCAD_ID,
0                                                                            S_NOOFROOMS,
0                                                                            S_OOO,
0                                                                            S_NOOFROOMS_TO_SELL,
0                                                                            S_NOOFBEDS, --CVZ New field
0                                                                            S_OCC_ROOMS_DEF,
0                                                                            S_OCC_ROOMS_TENT,
0                                                                            S_ARR_ROOMS_DEF_TENT,
0                                                                            S_ARR_ADULT_DEF_TENT,
0                                                                            S_DEP_ROOMS_DEF_TENT, 
0                                                                            S_DEP_ADULTS_DEF_TENT,
0                                                                            S_ARR_ROOMS_DEF,
0                                                                            S_DEP_ROOMS_DEF,
0                                                                            S_OCC_ADULTS_DEF, 
0                                                                            S_OCC_ADULTS_TENT,
0                                                                            S_OCC_CHILDS_DEF,
0                                                                            S_OCC_CHILDS_TENT,
0                                                                            S_DEV_ROOM_REV_GROSS,
0                                                                            S_TENT_ROOM_REV_GROSS,
0                                                                            S_DEV_ROOM_REV_NET,
0                                                                            S_TENT_ROOM_REV_NET,
0                                                                            S_DEV_FB_REV_GROSS,
0                                                                            S_TENT_FB_REV_GROSS,
0                                                                            S_DEV_FB_REV_NET,
0                                                                            S_TENT_FB_REV_NET,
0                                                                            S_DEV_OTHER_REV_GROSS,
0                                                                            S_TENT_OTHER_REV_GROSS,
0                                                                            S_DEV_OTHER_REV_NET,
0                                                                            S_TENT_OTHER_REV_NET,
0                                                                            S_NOT_USED
from
V8_REP_WDAT_DATE_CAD WDAT
) where not exists (select 1 from SMAD where SMAD.SMAD_DATE=S_POSDATE) 
group by S_POSDATE, S_YCAD_ID
) order by wdat_date;


CREATE OR REPLACE VIEW V8_SYS_BV_BASIC_PACKAGE AS
SELECT ypac_id id, ypac_shortdesc, ypac_longdesc name, ypac_invalid
FROM ypac
WHERE UPPER(ypac_longdesc) LIKE 'BV %'
AND (ypac_invalid IS NULL OR ypac_invalid = 0);


CREATE OR REPLACE FORCE VIEW V8_SYS_BV_PACKAGE AS
SELECT   ypac_id basicpack_id, ypac_shortdesc, ypac_longdesc, ypal_id custpack_id,
    DECODE (ypap.ypap_type,1,(ypal.ypal_multiplier*ydet.ydet_adultno),ypal.ypal_multiplier) packages_multiplier,
    yres.yres_id reservation_id,
    ydet_date valid_day,
    ydet.ydet_adultno nb_adults_res, ypap_type Packagestype, ypap_value,
    ypat_from package_valid_from, ypat_until package_valid_until,
    yres.yres_exparrtime arrival,
    yres.yres_expdeptime departure,
    xcms.xcms_id foreign_id, ypap_id, yres.yres_resstatus resstatus
     FROM ypal, ypac, ydet, yres, xcms, ypat, ypap
     WHERE ypac_longdesc LIKE 'BV %'
     AND (ypac_invalid IS NULL OR ypac_invalid = 0)
     AND ypal_ypac_id = ypac_id
     AND ypal_ydet_id = ydet_id
     AND ydet_yres_id = yres_id
     AND yres.yres_resstatus IN (0,1)
     AND yres.yres_xcms_id = xcms.xcms_id
     AND ypat.ypat_ypac_id=ypac.ypac_id
     AND YPAT.YPAT_YPAC_ID=YPAC.YPAC_ID
     AND YPAP.YPAP_YPAT_id= ypat.ypat_ID
     AND YPAP.YPAP_TYPE IN (1,2)
     AND ypat.ypat_invalid IS NULL
     AND ypap.ypap_invalid IS NULL
     AND ypap.ypap_value <> 0
     ORDER BY custpack_id, ydet_date;


CREATE OR REPLACE VIEW V8_SYS_BV_GUESTINRES AS
   SELECT yres.yres_id reservation_id, 
             acctype, 
             resinfostring,
          NVL (checked_in, 0) is_checked_in, 
          xcms.xcms_name1 lastname,
          UPPER(xcms.xcms_name1) slastname,
          xcms.xcms_name3 firstname, 
          UPPER(xcms.xcms_name3) sfirstname,
          yres.yres_exparrtime arrivaldate,
          yres.yres_expdeptime departuredate, 
          yrms_shortdesc roomnumber,
          ygrp_id GROUP_ID, 
          ygrp_name group_name,
          xcid.xcid_birthtime dayofbirth, 
          xcms.xcms_id foreignid,
          (SELECT MAX (xpdc.xpdc_value)
             FROM xpdc, xpdg
            WHERE xpdc.xpdc_xcms_id = xcms.xcms_id
                  AND xpdc.xpdc_xpdg_id = xpdg.xpdg_id
                  AND xpdg.xpdg_role = 99) 
                  membernumber,
          xadr.xadr_street1 street1, 
          xadr.xadr_street2 street2,
          xadr.xadr_city city, 
          xadr.xadr_zip zip,
          xcou_address.xcou_iso2 country_iso2,
          xcou_address.xcou_longdesc country,
          (SELECT MAX (xcom.xcom_value)
             FROM xcom, xcmt
            WHERE xcom.xcom_xcms_id = xcms.xcms_id
              AND xcom.xcom_xcmt_id = xcmt.xcmt_id
              AND xcmt.xcmt_type = 4
              AND NVL (xcom.xcom_primary, 0) = 1) telephone,
          (SELECT MAX (xcom.xcom_value)
             FROM xcom, xcmt
            WHERE xcom.xcom_xcms_id = xcms.xcms_id
              AND xcom.xcom_xcmt_id = xcmt.xcmt_id
              AND xcmt.xcmt_type = 3
              AND NVL (xcom.xcom_primary, 0) = 1) telefax,
          (SELECT MAX (xcom.xcom_value)
             FROM xcom, xcmt
            WHERE xcom.xcom_xcms_id = xcms.xcms_id
              AND xcom.xcom_xcmt_id = xcmt.xcmt_id
              AND xcmt.xcmt_type = 9
              AND NVL (xcom.xcom_primary, 0) = 1) mobile,
          (SELECT MAX (xcom.xcom_value)
             FROM xcom, xcmt
            WHERE xcom.xcom_xcms_id = xcms.xcms_id
              AND xcom.xcom_xcmt_id = xcmt.xcmt_id
              AND xcmt.xcmt_type = 1
              AND NVL (xcom.xcom_primary, 0) = 1) email,
          xcid.xcid_addrgreet title, 
          wlan.wlan_shortdesc LANGUAGE,
          xcou_nationality.xcou_iso2 nationality_iso2,
          xcou_nationality.xcou_longdesc nationality, 
          xcid.xcid_sex sex,
          xcid.xcid_lettgreet salutation,
          (SELECT MAX (xpdg.xpdg_longdesc)
             FROM xpdc, xpdg
            WHERE xpdc.xpdc_xcms_id = xcms.xcms_id
              AND xpdc.xpdc_xpdg_id = xpdg.xpdg_id
              AND xpdg.xpdg_role = 99) membercard,
          (SELECT MAX (xpdc.xpdc_validfrom)
             FROM xpdc, xpdg
            WHERE xpdc.xpdc_xcms_id = xcms.xcms_id
              AND xpdc.xpdc_xpdg_id = xpdg.xpdg_id
              AND xpdg.xpdg_role = 99) membersince,
          (SELECT MAX (xpdc.xpdc_expiry)
             FROM xpdc, xpdg
            WHERE xpdc.xpdc_xcms_id = xcms.xcms_id
              AND xpdc.xpdc_xpdg_id = xpdg.xpdg_id
              AND xpdg.xpdg_role = 99) membercardvalidity,
          xcca.xcca_longdesc guesttype, 
          xcno.xcno_notes remark,
          DECODE (xcms.xcms_type, 2, 1, 0) isguest,
          NVL ((SELECT MAX (xcil.xcil_company_xcms_id)
                  FROM xcil
                 WHERE xcil.xcil_individual_xcms_id = xcms.xcms_id),
               0
              ) company_foreignid,
          NVL ((SELECT MAX (xmnr.xmnr_value)
                  FROM xcil INNER JOIN xmnr ON xcil.xcil_company_xcms_id =
                                                             xmnr.xmnr_xcms_id
                       INNER JOIN xmty ON xmnr.xmnr_xmty_id = xmty.xmty_id
                 WHERE xcil.xcil_individual_xcms_id = xcms.xcms_id
                   AND xmty.xmty_role = 3),
               ''
              ) company_vatcode,
          wphs.wphs_name AS resort, 
          '' AS vat_id, 
          yres_vacant,
          YRES.WEIGHT AS mainguest,
          YRES.NOPOST as noposting
     FROM ((SELECT YRES_VACANT, 'RES' acctype, yres.yres_id, yrpl_xcms_id yres_xcms_id,
                  (SELECT yrms.yrms_shortdesc
                     FROM yrms, ydet
                    WHERE yrms.yrms_id = ydet.ydet_yrms_id
                      AND ydet.ydet_id = yres.yres_current_ydet_id)
                                                               yrms_shortdesc,
                  (DECODE (yres.yres_actarrtime, NULL, 0, 1)) checked_in,
                  (   'Res. #: '
                   || yres.yres_id
                   || ', From '
                   || yres.yres_exparrtime
                   || ' to '
                   || yres.yres_expdeptime
                  ) resinfostring,
                  yres.yres_current_ydet_id ydet_id,
                  yres.yres_exparrtime yres_exparrtime,
                  yres.yres_actarrtime yres_actarrtime,
                  yres.yres_expdeptime yres_expdeptime,
                  yres.yres_actdeptime yres_actdeptime,
                  (DECODE ((yres.yres_exparrtime),
                           (SELECT wgbs.wgbs_datevalue
                              FROM wgbs), 1,
                           0
                          )
                  ) arrival_today,
                  (DECODE ((yres.yres_exparrtime),
                           (SELECT wgbs.wgbs_datevalue
                              FROM wgbs), (DECODE (yres.yres_actarrtime,
                                                   NULL, 0,
                                                   1
                                                  )),
                           0
                          )
                  ) checked_in_today,
                  (DECODE ((yres.yres_expdeptime),
                           (SELECT wgbs.wgbs_datevalue
                              FROM wgbs), 1,
                           0
                          )
                  ) departure_today,
                  (DECODE ((yres.yres_expdeptime),
                           (SELECT wgbs.wgbs_datevalue
                              FROM wgbs), (DECODE (yres.yres_actdeptime,
                                                   NULL, 0,
                                                   1
                                                  )),
                           0
                          )
                  ) checked_out_today,
                  (DECODE (yres.yres_actdeptime, NULL, 0, 1)) checked_out,
                  (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 ygrp.ygrp_id
                     FROM ygrp, ydet
                    WHERE ygrp.ygrp_id = ydet.ydet_ygrp_id
                      AND ydet.ydet_id = yres.yres_current_ydet_id) ygrp_id,
                  (SELECT ygrp.ygrp_name
                     FROM ygrp, ydet
                    WHERE ygrp.ygrp_id = ydet.ydet_ygrp_id
                      AND ydet.ydet_id = yres.yres_current_ydet_id) ygrp_name,
                      (CASE YRPL.YRPL_XCMS_ID WHEN YRES_XCMS_ID THEN 1 ELSE 0 END) AS WEIGHT,
                      YRES.YRES_NOPOST AS NOPOST
             FROM yres, yrpl
            WHERE yres.yres_resstatus <> 3
            ---- selects only reservations with departure date 
            ---- later than now
              AND (SELECT wgbs.wgbs_datevalue
                    FROM wgbs
                 WHERE wgbs.wgbs_name = 'system date') <= yres.yres_expdeptime
              AND yrpl.yrpl_yres_id = yres.yres_id
           UNION ALL
           SELECT 0 as YRES_VACANT, 'FA' acctype, zfac_id, zfac_xcms_id yres_xcms_id,
                  NVL (zfan_longdesc, zfac_autonumber) yrms_shortdesc,
                  (DECODE (zfac_actvalidfrom, NULL, 0, 1)) checked_in,
                  (   'FA. #: '
                   || zfac_id
                   || ', From '
                   || zfac_expvalidfrom
                   || ' to '
                   || zfac_expvaliduntil
                  ) resinfostring,
                  0 ydet_id, zfac_expvalidfrom, zfac_actvalidfrom actarrtime,
                  NVL (zfac_expvaliduntil, zfac_expvalidfrom + 365),
                  zfac_actvaliduntil actdeptime,
                  (DECODE ((zfac_expvalidfrom),
                           (SELECT wgbs.wgbs_datevalue
                              FROM wgbs), 1,
                           0
                          )
                  ) arrival_today,
                  (DECODE ((zfac_expvalidfrom),
                           (SELECT wgbs.wgbs_datevalue
                              FROM wgbs), (DECODE (zfac_actvalidfrom,
                                                   NULL, 0,
                                                   1
                                                  )),
                           0
                          )
                  ) checked_in_today,
                  (DECODE ((zfac_expvalidfrom),
                           (SELECT wgbs.wgbs_datevalue
                              FROM wgbs), 1,
                           0
                          )
                  ) departure_today,
                  (DECODE ((zfac_expvaliduntil),
                           (SELECT wgbs.wgbs_datevalue
                              FROM wgbs), (DECODE (zfac_actvaliduntil,
                                                   NULL, 0,
                                                   1
                                                  )),
                           0
                          )
                  ) checked_out_today,
                  (DECODE (zfac_actvaliduntil, NULL, 0, 1)) checked_out,
                  (SELECT zfag_shortdesc
                     FROM zfag
                    WHERE zfac_zfag_id = zfag_id) ycat_shortdesc, 0 ygrp_id,
                  '' ygrp_name,
                  0 AS WEIGHT,
                  0 AS NOPOST
             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)
              AND (SELECT wgbs.wgbs_datevalue
                     FROM wgbs
                    WHERE wgbs.wgbs_name = 'system date') <=
                             NVL (zfac_expvaliduntil, zfac_expvalidfrom + 365)) ORDER BY YRES_ID, WEIGHT DESC) yres,
          xcms,
          wphs,
          xcid,
          wlan,
          xcou xcou_address,
          xcou xcou_nationality,
          xcca,
          (SELECT xcno_table_id xcno_xcms_id, xcno_notes
             FROM xcno, xcnc
            WHERE xcno.xcno_tablelinkedto = 'XCMS'
              AND xcno.xcno_xcnc_id = xcnc.xcnc_id
              AND UPPER (xcnc.xcnc_shortdesc) LIKE 'BV%') xcno,
          (SELECT *
             FROM xadr
            WHERE NVL (xadr.xadr_primary, 0) = 1) xadr
    WHERE xcms.xcms_id = xcid.xcid_id(+)
      AND xcms.xcms_org_wphs_id = wphs.wphs_id(+)
      AND yres_xcms_id(+) = xcms.xcms_id
      AND xcms.xcms_id = xadr.xadr_xcms_id(+)
      AND xadr.xadr_xcou_id = xcou_address.xcou_id(+)
      AND xcms.xcms_wlan_id = wlan.wlan_id(+)
      AND xcms.xcms_xcou_id_nation = xcou_nationality.xcou_id(+)
      AND xcms.xcms_xcca_id = xcca.xcca_id(+)
      AND xcms.xcms_id = xcno_xcms_id(+)
      AND xcms.xcms_type IN (1, 2)
      AND xcms.xcms_active <> 0 ---- 0: inactive
      AND xcms.xcms_active <> 4; ---- 4: merged to another profile     


CREATE OR REPLACE VIEW V8_PPR_RTRCDATA_FC1 as
select 
'"'||col01||'";'||
'"'||col02||'";'
||col03||';'
||col04||';'
||col05||';'
||col06||';'
||col07||';'
||col08||';'
||col09||';'
||col10||';'
||col11||';'
||col12||';'
||col13||';'
||col14||';'
||col15||';'
||col16||';'
||col17||';'
||col18||';'
||col19||';'
||'"'||col20||'";'
||col21||';'
||'"'||col22||'";'
||col23||';'
||'"'||col24||'";'
||col25||';' DATA,
2 Exp_order,
Exportdate
from V8_PPR_RTRCDATA_FC
where   (to_number(col05) + 
        to_number(col06) + 
        to_number(col06) + 
        to_number (col07)+ 
        to_number (col08) + 
        to_number (col09) + 
        to_number (col10) + 
        to_number (col11) + 
        to_number (col12) + 
        to_number (col13) + 
        to_number (COL21, '999999990D09', 'NLS_NUMERIC_CHARACTERS=.,') + 
        to_number (COL23, '999999990D09', 'NLS_NUMERIC_CHARACTERS=.,') + 
        to_number (COL25, '999999990D09', 'NLS_NUMERIC_CHARACTERS=.,')) 
        > 0
order by V8_PPR_RTRCDATA_FC.COL02, V8_PPR_RTRCDATA_FC.COL03, V8_PPR_RTRCDATA_FC.COL04 asc;





CREATE OR REPLACE VIEW v8_stat_ccm_res_forrev_fs
as
   SELECT YEBS_STARTDATE wdat_date,
          ybom_id,
          yevm_id,
          zdco_id,
          zdco_stats_type,
          1 ccm_type,
          yevm_noofattendees,
            DECODE (NVL (yebs_perhour, 0),
                    0, 1,
                    24 * (yebs_endtime - yebs_starttime))
          * yebs_specialrate
             grossrev,             
         (SELECT SUM (zpos_quantity * zpos_grossunitprice)
             FROM zpos,
                  (SELECT yblp_id,
                          yblp_resource_id,
                          yblp_resource_type,
                          zpos_id
                     FROM zpos, yblp
                    WHERE zpos_id = yblp_zpos_id) v
            WHERE     zpos.zpos_id = v.zpos_id
                  AND v.yblp_resource_id = yebs.yebs_id
                  AND v.yblp_resource_type = 2)
             posted_grossrev,
          (SELECT SUM (zpos_quantity * zpos_unitprice)
             FROM zpos,
                  (SELECT yblp_id,
                          yblp_resource_id,
                          yblp_resource_type,
                          zpos_id
                     FROM zpos, yblp
                    WHERE zpos_id = yblp_zpos_id) v
            WHERE     zpos.zpos_id = v.zpos_id
                  AND v.yblp_resource_id = yebs.yebs_id
                  AND v.yblp_resource_type = 2)
             posted_netrev,
          (SELECT SUM (zpos_quantity * zpos_unitprice)
             FROM zpos,
                  (SELECT yblp_id,
                          yblp_resource_id,
                          yblp_resource_type,
                          z2.zpos_id
                     FROM zpos z1, zpos z2, yblp
                    WHERE     z1.zpos_taxlink_id = z2.zpos_taxlink_id
                          AND z1.zpos_id = yblp_zpos_id
                          AND z2.zpos_cdt = 2) v
            WHERE     zpos.zpos_id = v.zpos_id
                  AND v.yblp_resource_id = yebs.yebs_id
                  AND v.yblp_resource_type = 2)
             posted_tax,            
          ybomyest.yest_cancelstatus ybom_yest_cancelstatus,
         yest.yest_cancelstatus yevm_yest_cancelstatus,
          YBOM.YBOM_CON_YEST_ID,
          YEVM.YEVM_YEST_ID,
          (SELECT TRUNC (wmlg_time_creation)
             FROM wmlg
            WHERE     wmlg_tablename = 'YEBS'
                  AND wmlg_table_id = yebs_id
                  AND ROWNUM = 1)
             production_date,
          yebs_alternative alternative,
            DECODE (NVL (yebs_perhour, 0),
                    0, 1,
                    24 * (yebs_endtime - yebs_starttime))
          * yebs_price_net
             Net,
            DECODE (NVL (yebs_perhour, 0),
                    0, 1,
                    24 * (yebs_endtime - yebs_starttime))
          * yebs_price_vat
             Vat
     FROM yebs,
          zdco,
          yevm,
          yest,
          ybom,
          yest ybomyest
    WHERE     yebs_zdco_id = zdco_id
          AND NVL (yebs_pkgincl, 0) = 0
          AND NVL (yebs_specialrate, 0) <> 0
          AND yebs_yevm_id = yevm_id
          AND yevm_yest_id = yest.yest_id
          AND yest.yest_postrevenue = 1
          AND yevm_ybom_id = ybom_id
          AND ybom_con_yest_id = ybomyest.yest_id
          AND (       NVL (ybomyest.yest_cancelstatus, 0) = 0
                  AND NVL (yest.yest_cancelstatus, 0) <> 1
               OR NVL (ybomyest.yest_cancelstatus, 0) = 1);


CREATE OR REPLACE VIEW v8_stat_ccm_res_forrev_ci
as
   SELECT TRUNC (yebr_starttime) wdat_date,
          ybom_id,
          yevm_id,
          zdco_id,
          zdco_stats_type,
          2 ccm_type,
          yevm_noofattendees,
            DECODE (NVL (yebr_perhour, 0),
                    0, 1,
                    24 * (yebr_endtime - yebr_starttime))
          * yebr_manualprice
          * yebr_quantity
             grossrev,            
          (SELECT SUM (zpos_quantity * zpos_grossunitprice)
             FROM zpos,
                  (SELECT yblp_id,
                          yblp_resource_id,
                          yblp_resource_type,
                          zpos_id
                     FROM zpos, yblp
                    WHERE zpos_id = yblp_zpos_id) v
            WHERE     zpos.zpos_id = v.zpos_id
                  AND v.yblp_resource_id = yebr.yebr_id
                  AND v.yblp_resource_type = 3)
             posted_grossrev,
          (SELECT SUM (zpos_quantity * zpos_unitprice)
             FROM zpos,
                  (SELECT yblp_id,
                          yblp_resource_id,
                          yblp_resource_type,
                          zpos_id
                     FROM zpos, yblp
                    WHERE zpos_id = yblp_zpos_id) v
            WHERE     zpos.zpos_id = v.zpos_id
                  AND v.yblp_resource_id = yebr.yebr_id
                  AND v.yblp_resource_type = 3)
             posted_netrev,
          (SELECT SUM (zpos_quantity * zpos_unitprice)
             FROM zpos,
                  (SELECT yblp_id,
                          yblp_resource_id,
                          yblp_resource_type,
                          z2.zpos_id
                     FROM zpos z1, zpos z2, yblp
                    WHERE     z1.zpos_taxlink_id = z2.zpos_taxlink_id
                          AND z1.zpos_id = yblp_zpos_id
                          AND z2.zpos_cdt = 2) v
            WHERE     zpos.zpos_id = v.zpos_id
                  AND v.yblp_resource_id = yebr.yebr_id
                  AND v.yblp_resource_type = 3)
             posted_tax,
          ybomyest.yest_cancelstatus ybom_yest_cancelstatus,
          yest.yest_cancelstatus yevm_yest_cancelstatus,
          YBOM.YBOM_CON_YEST_ID,
          YEVM.YEVM_YEST_ID,
          (SELECT TRUNC (wmlg_time_creation)
             FROM wmlg
            WHERE     wmlg_tablename = 'YEBR'
                  AND wmlg_table_id = yebr_id
                  AND ROWNUM = 1)
             production_date,
          yebr_alternative alternative,
            DECODE (NVL (yebr_perhour, 0),
                    0, 1,
                    24 * (yebr_endtime - yebr_starttime))
          * yebr_price_net
          * yebr_quantity
             Net,
            DECODE (NVL (yebr_perhour, 0),
                    0, 1,
                    24 * (yebr_endtime - yebr_starttime))
          * yebr_price_vat
          * yebr_quantity
             Vat
     FROM yebr,
          zdco,
          yevm,
          yest,
          ybom,
          yest ybomyest
    WHERE     yebr_zdco_id = zdco_id
          AND NVL (yebr_pkgincl, 0) = 0
          AND NVL (yebr_manualprice, 0) <> 0
          AND yebr_yevm_id = yevm_id
          AND yevm_yest_id = yest.yest_id
          AND yest.yest_postrevenue = 1
          AND yevm_ybom_id = ybom_id
          AND ybom_con_yest_id = ybomyest.yest_id
          AND (       NVL (ybomyest.yest_cancelstatus, 0) = 0
                  AND NVL (yest.yest_cancelstatus, 0) <> 1
               OR NVL (ybomyest.yest_cancelstatus, 0) = 1);


CREATE OR REPLACE VIEW v8_stat_ccm_res_forrev_sm
as
   SELECT TRUNC (yemn_servestart) wdat_date,
          ybom_id,
          yevm_id,
          zdco_id,
          zdco_stats_type,
          3 ccm_type,
          yevm_noofattendees,
            ymrd_yfca_amount
          * DECODE (
               NVL (YEMN_POSTGUARANTEED, 0),
               0, YEMN_QUANTITY,
               DECODE (SIGN (YEMN_GUARANTEEDNOOFPEOPLE - YEMN_QUANTITY),
                       1, YEMN_GUARANTEEDNOOFPEOPLE,
                       YEMN_QUANTITY))
             grossrev,           
          (SELECT SUM (zpos_quantity * zpos_grossunitprice)
             FROM zpos,
                  (SELECT yblp_id,
                          yblp_resource_id,
                          yblp_resource_type,
                          zpos_id
                     FROM zpos, yblp
                    WHERE zpos_id = yblp_zpos_id) v
            WHERE     zpos.zpos_id = v.zpos_id
                  AND v.yblp_resource_id = ymrd.ymrd_id
                  AND v.yblp_resource_type = 8)
             posted_grossrev,
          (SELECT SUM (zpos_quantity * zpos_unitprice)
             FROM zpos,
                  (SELECT yblp_id,
                          yblp_resource_id,
                          yblp_resource_type,
                          zpos_id
                     FROM zpos, yblp
                    WHERE zpos_id = yblp_zpos_id) v
            WHERE     zpos.zpos_id = v.zpos_id
                  AND v.yblp_resource_id = ymrd.ymrd_id
                  AND v.yblp_resource_type = 8)
             posted_netrev,
          (SELECT SUM (zpos_quantity * zpos_unitprice)
             FROM zpos,
                  (SELECT yblp_id,
                          yblp_resource_id,
                          yblp_resource_type,
                          z2.zpos_id
                     FROM zpos z1, zpos z2, yblp
                    WHERE     z1.zpos_taxlink_id = z2.zpos_taxlink_id
                          AND z1.zpos_id = yblp_zpos_id
                          AND z2.zpos_cdt = 2) v
            WHERE     zpos.zpos_id = v.zpos_id
                  AND v.yblp_resource_id = ymrd.ymrd_id
                  AND v.yblp_resource_type = 8)
             posted_tax,
          ybomyest.yest_cancelstatus ybom_yest_cancelstatus,
          yest.yest_cancelstatus yevm_yest_cancelstatus,
          YBOM.YBOM_CON_YEST_ID,
          YEVM.YEVM_YEST_ID,
          (SELECT TRUNC (wmlg_time_creation)
             FROM wmlg
            WHERE     wmlg_tablename = 'YEMN'
                  AND wmlg_table_id = yemn_id
                  AND ROWNUM = 1)
             production_date,
          yemn_alternative alternative,
            ymrd_price_net
          * DECODE (
               NVL (YEMN_POSTGUARANTEED, 0),
               0, YEMN_QUANTITY,
               DECODE (SIGN (YEMN_GUARANTEEDNOOFPEOPLE - YEMN_QUANTITY),
                       1, YEMN_GUARANTEEDNOOFPEOPLE,
                       YEMN_QUANTITY))
             Net,
            ymrd_price_vat
          * DECODE (
               NVL (YEMN_POSTGUARANTEED, 0),
               0, YEMN_QUANTITY,
               DECODE (SIGN (YEMN_GUARANTEEDNOOFPEOPLE - YEMN_QUANTITY),
                       1, YEMN_GUARANTEEDNOOFPEOPLE,
                       YEMN_QUANTITY))
             Vat
     FROM yemn,
          zdco,
          ymrd,
          yevm,
          yest,
          ybom,
          yest ybomyest
    WHERE     ymrd_zdco_id = zdco_id
          AND yemn_id = ymrd_yemn_id
          AND NVL (yemn_pkgincl, 0) = 0
          AND NVL (yemn_chargedonconsumption, 0) = 0
          AND yemn_yevm_id = yevm_id
          AND yevm_yest_id = yest.yest_id
          AND yest.yest_postrevenue = 1
          AND yevm_ybom_id = ybom_id
          AND ybom_con_yest_id = ybomyest.yest_id
          AND (       NVL (ybomyest.yest_cancelstatus, 0) = 0
                  AND NVL (yest.yest_cancelstatus, 0) <> 1
               OR NVL (ybomyest.yest_cancelstatus, 0) = 1);


CREATE OR REPLACE FORCE VIEW V8_STAT_CCM_RES_FORREV_MI
AS
    SELECT TRUNC (yemn_servestart) wdat_date,
          ybom_id,
          yevm_id,
          zdco_id,
          zdco_stats_type,
          4 ccm_type,
          yevm_noofattendees,
          yemi_price * yemi_quantity grossrev,
          (SELECT SUM (zpos_quantity * zpos_grossunitprice)
             FROM zpos,
                  (SELECT yblp_id,
                          yblp_resource_id,
                          yblp_resource_type,
                          zpos_id
                     FROM zpos, yblp
                    WHERE zpos_id = yblp_zpos_id) v
            WHERE     zpos.zpos_id = v.zpos_id
                  AND v.yblp_resource_id = yemi.yemi_id
                  AND v.yblp_resource_type = 5)
             posted_grossrev,
          (SELECT SUM (zpos_quantity * zpos_unitprice)
             FROM zpos,
                  (SELECT yblp_id,
                          yblp_resource_id,
                          yblp_resource_type,
                          zpos_id
                     FROM zpos, yblp
                    WHERE zpos_id = yblp_zpos_id) v
            WHERE     zpos.zpos_id = v.zpos_id
                  AND v.yblp_resource_id = yemi.yemi_id
                  AND v.yblp_resource_type = 5)
             posted_netrev,
          (SELECT SUM (zpos_quantity * zpos_unitprice)
             FROM zpos,
                  (SELECT yblp_id,
                          yblp_resource_id,
                          yblp_resource_type,
                          z2.zpos_id
                     FROM zpos z1, zpos z2, yblp
                    WHERE     z1.zpos_taxlink_id = z2.zpos_taxlink_id
                          AND z1.zpos_id = yblp_zpos_id
                          AND z2.zpos_cdt = 2) v
            WHERE     zpos.zpos_id = v.zpos_id
                  AND v.yblp_resource_id = yemi.yemi_id
                  AND v.yblp_resource_type = 5)
             posted_tax,
          ybomyest.yest_cancelstatus ybom_yest_cancelstatus,
          yest.yest_cancelstatus yevm_yest_cancelstatus,
          YBOM.YBOM_CON_YEST_ID,
          YEVM.YEVM_YEST_ID,
          (SELECT TRUNC (wmlg_time_creation)
             FROM wmlg
            WHERE     wmlg_tablename = 'YEMI'
                  AND wmlg_table_id = yemi_id
                  AND ROWNUM = 1)
             production_date,
          yemi_alternative alternative,
          yemi_price_net * yemi_quantity Net,
          yemi_price_vat * yemi_quantity Vat
     FROM yemn,
          yemi,
          zdco,
          yevm,
          yest,
          ybom,
          yest ybomyest
    WHERE     NVL (yemn_chargedonconsumption, 0) = 0
          AND yemn_id = yemi_yemn_id
          AND yemi_zdco_id = zdco_id
          AND NVL (yemi_includedinthemenu, 0) = 0
          AND NVL (yemi_price, 0) <> 0
          AND yemn_yevm_id = yevm_id
          AND yevm_yest_id = yest.yest_id
          AND yest.yest_postrevenue = 1
          AND yevm_ybom_id = ybom_id
          AND ybom_con_yest_id = ybomyest.yest_id
          AND (       NVL (ybomyest.yest_cancelstatus, 0) = 0
                  AND NVL (yest.yest_cancelstatus, 0) <> 1
               OR NVL (ybomyest.yest_cancelstatus, 0) = 1);


CREATE OR REPLACE VIEW v8_stat_ccm_res_forrev_pk
as
   SELECT TRUNC (yevm_starttime) wdat_date,
          ybom_id,
          yevm.yevm_id,
          zdco_id,
          zdco_stats_type,
          5 ccm_type,
          yevm_noofattendees - NVL (ym.merged_yevm_noofattendees, 0)
             yevm_noofattendees,
            (yevm_noofattendees - NVL (ym.merged_yevm_noofattendees, 0))
          * ybef_rev_amount
             grossrev,             
         (SELECT SUM (zpos_quantity * zpos_grossunitprice)
             FROM zpos,
                  (SELECT yblp_id,
                          yblp_resource_id,
                          yblp_resource_type,
                          zpos_id
                     FROM zpos, yblp
                    WHERE zpos_id = yblp_zpos_id) v
            WHERE     zpos.zpos_id = v.zpos_id
                  AND v.yblp_resource_id = ybef.ybef_id
                  AND v.yblp_resource_type = 1)
             posted_grossrev,
          (SELECT SUM (zpos_quantity * zpos_unitprice)
             FROM zpos,
                  (SELECT yblp_id,
                          yblp_resource_id,
                          yblp_resource_type,
                          zpos_id
                     FROM zpos, yblp
                    WHERE zpos_id = yblp_zpos_id) v
            WHERE     zpos.zpos_id = v.zpos_id
                  AND v.yblp_resource_id = ybef.ybef_id
                  AND v.yblp_resource_type = 1)
             posted_netrev,
          (SELECT SUM (zpos_quantity * zpos_unitprice)
             FROM zpos,
                  (SELECT yblp_id,
                          yblp_resource_id,
                          yblp_resource_type,
                          z2.zpos_id
                     FROM zpos z1, zpos z2, yblp
                    WHERE     z1.zpos_taxlink_id = z2.zpos_taxlink_id
                          AND z1.zpos_id = yblp_zpos_id
                          AND z2.zpos_cdt = 2) v
            WHERE     zpos.zpos_id = v.zpos_id
                  AND v.yblp_resource_id = ybef.ybef_id
                  AND v.yblp_resource_type = 1)
             posted_tax,
          ybomyest.yest_cancelstatus ybom_yest_cancelstatus,
          yest.yest_cancelstatus yevm_yest_cancelstatus,
          YBOM.YBOM_CON_YEST_ID,
          YEVM.YEVM_YEST_ID,
          (SELECT TRUNC (wmlg_time_creation)
             FROM wmlg
            WHERE     wmlg_tablename = 'YBEF'
                  AND wmlg_table_id = ybef_id
                  AND ROWNUM = 1)
             production_date,
          ybef_alternative alternative,
            (yevm_noofattendees - NVL (ym.merged_yevm_noofattendees, 0))
          * ybef_price_net
             Net,
            (yevm_noofattendees - NVL (ym.merged_yevm_noofattendees, 0))
          * ybef_price_Vat
             Vat
     FROM yevm,
          ybef,
          zdco,
          yest,
          ybom,
          yest ybomyest,
          (SELECT ym.yevm_id,
                  (SELECT SUM (yevm_noofattendees) merged_yevm_noofattendees
                     FROM yevm
                    WHERE     NVL (yevm_mergedto_yevm_id, 0) <> 0
                          AND yevm_mergedto_yevm_id = ym.yevm_id)
                     merged_yevm_noofattendees
             FROM yevm ym--where nvl(ym.yevm_mergedto_yevm_id, 0) = 0
          ) ym
    WHERE     NVL (yevm_pkgincl, 0) = 1
          AND ybef_yevm_id = yevm.yevm_id
          AND NVL (ybef_ypkd_id, 0) > 0
          AND NVL (ybef_rev_amount, 0) <> 0
          AND ybef_zdco_id = zdco_id
          AND yevm_yest_id = yest.yest_id
          AND yest.yest_postrevenue = 1
          AND yevm_ybom_id = ybom_id
          AND ybom_con_yest_id = ybomyest.yest_id
          AND (       NVL (ybomyest.yest_cancelstatus, 0) = 0
                  AND NVL (yest.yest_cancelstatus, 0) <> 1
               OR NVL (ybomyest.yest_cancelstatus, 0) = 1)
               AND yevm.yevm_id = ym.yevm_id;


CREATE OR REPLACE VIEW v8_stat_ccm_res_forrev_fb
AS
  select * from v8_stat_ccm_res_forrev_sm
   UNION ALL
  select * from v8_stat_ccm_res_forrev_mi;


CREATE OR REPLACE VIEW v8_stat_ccm_res_forrev_exp
as
   SELECT TRUNC (yevm_starttime) wdat_date,
          ybom_id,
          yevm.yevm_id,
          zdco_id,
          0 zdco_stats_type,
          6 ccm_type,
          yevm_noofattendees - NVL (ym.merged_yevm_noofattendees, 0)
             yevm_noofattendees,
              (yevm_noofattendees - NVL (ym.merged_yevm_noofattendees, 0))
            * ybef_per_person
          + ybef_flat_amount
             grossrev,            
          0 posted_grossrev,
          0 posted_netrev,
          0 posted_tax,
          ybomyest.yest_cancelstatus ybom_yest_cancelstatus,
          yest.yest_cancelstatus yevm_yest_cancelstatus,
          YBOM.YBOM_CON_YEST_ID,
          YEVM.YEVM_YEST_ID,
          (SELECT TRUNC (wmlg_time_creation)
             FROM wmlg
            WHERE     wmlg_tablename = 'YBEF'
                  AND wmlg_table_id = ybef_id
                  AND ROWNUM = 1)
             production_date,
          ybef_alternative alternative,
            (yevm_noofattendees - NVL (ym.merged_yevm_noofattendees, 0))
            * ybef_per_person
          + ybef_flat_amount
          - evaluateVAT (
               NVL (zdco_id, 0),
               TRUNC (yevm_starttime),
                   (  yevm_noofattendees
                    - NVL (ym.merged_yevm_noofattendees, 0))
                 * ybef_per_person
               + ybef_flat_amount)
          Net,
          0 Vat
     FROM yevm,
          ybef,
          zdco,
          yest,
          ybom,
          yest ybomyest,
          (SELECT ym.yevm_id,
                  (SELECT SUM (yevm_noofattendees) merged_yevm_noofattendees
                     FROM yevm
                    WHERE     NVL (yevm_mergedto_yevm_id, 0) <> 0
                          AND yevm_mergedto_yevm_id = ym.yevm_id)
                     merged_yevm_noofattendees
             FROM yevm ym--where nvl(ym.yevm_mergedto_yevm_id, 0) = 0
          ) ym
    WHERE     ybef_yevm_id = YEVM.yevm_id
          AND (   NVL (ybef_per_person, 0) <> 0
               OR NVL (ybef_flat_amount, 0) <> 0)
          AND ybef_zdco_id = zdco_id
          AND yevm_yest_id = yest.yest_id
          AND yest.yest_postrevenue = 1
          AND yevm_ybom_id = ybom_id
          AND ybom_con_yest_id = ybomyest.yest_id
          AND (       NVL (ybomyest.yest_cancelstatus, 0) = 0
                  AND NVL (yest.yest_cancelstatus, 0) <> 1
               OR NVL (ybomyest.yest_cancelstatus, 0) = 1)
          --and nvl(yevm_mergedto_yevm_id, 0)=0;
          AND yevm.yevm_id = ym.yevm_id;


CREATE OR REPLACE VIEW v8_stat_ccm_res_forrev_tot
as
  select * from v8_stat_ccm_res_forrev_fs   --1
   UNION ALL --2
  select * from v8_stat_ccm_res_forrev_ci
   UNION ALL --3
  select * from v8_stat_ccm_res_forrev_sm
   UNION ALL --4
  select * from v8_stat_ccm_res_forrev_mi 
   UNION ALL --5
  select * from v8_stat_ccm_res_forrev_pk;


CREATE OR REPLACE FORCE VIEW V8_STAT_CCM_RES_FORREV_TOTALS
AS
   SELECT ybom.ybom_id,
          NVL (
             (SELECT SUM (v.GROSSREV)
                FROM v8_stat_ccm_res_forrev_fs v
               WHERE v.YBOM_ID = ybom.YBOM_ID AND NVL (v.ALTERNATIVE, 0) = 0),
             0)
             FS_REV,
          NVL (
             (SELECT SUM (v.NET)
                FROM v8_stat_ccm_res_forrev_fs v
               WHERE v.YBOM_ID = ybom.YBOM_ID AND NVL (v.ALTERNATIVE, 0) = 0),
             0)
             FS_NET_REV,
          NVL (
             (SELECT SUM (v.VAT)
                FROM v8_stat_ccm_res_forrev_fs v
               WHERE v.YBOM_ID = ybom.YBOM_ID AND NVL (v.ALTERNATIVE, 0) = 0),
             0)
             FS_VAT,
          NVL (
             (SELECT SUM (v.GROSSREV)
                FROM v8_stat_ccm_res_forrev_ci v
               WHERE v.YBOM_ID = ybom.YBOM_ID AND NVL (v.ALTERNATIVE, 0) = 0),
             0)
             ITEMS_REV,
          NVL (
             (SELECT SUM (v.NET)
                FROM v8_stat_ccm_res_forrev_ci v
               WHERE v.YBOM_ID = ybom.YBOM_ID AND NVL (v.ALTERNATIVE, 0) = 0),
             0)
             ITEMS_NET_REV,
          NVL (
             (SELECT SUM (v.VAT)
                FROM v8_stat_ccm_res_forrev_ci v
               WHERE v.YBOM_ID = ybom.YBOM_ID AND NVL (v.ALTERNATIVE, 0) = 0),
             0)
             ITEMS_VAT,
          NVL (
             (SELECT SUM (v.GROSSREV)
                FROM v8_stat_ccm_res_forrev_sm v
               WHERE v.YBOM_ID = ybom.YBOM_ID AND NVL (v.ALTERNATIVE, 0) = 0),
             0)
             BOOKED_MENU_REV,
          NVL (
             (SELECT SUM (v.NET)
                FROM v8_stat_ccm_res_forrev_sm v
               WHERE v.YBOM_ID = ybom.YBOM_ID AND NVL (v.ALTERNATIVE, 0) = 0),
             0)
             BOOKED_MENU_NET_REV,
          NVL (
             (SELECT SUM (v.VAT)
                FROM v8_stat_ccm_res_forrev_sm v
               WHERE v.YBOM_ID = ybom.YBOM_ID AND NVL (v.ALTERNATIVE, 0) = 0),
             0)
             BOOKED_MENU_VAT,
          NVL (
             (SELECT SUM (v.GROSSREV)
                FROM v8_stat_ccm_res_forrev_mi v
               WHERE v.YBOM_ID = ybom.YBOM_ID AND NVL (v.ALTERNATIVE, 0) = 0),
             0)
             MI_REV,
          NVL (
             (SELECT SUM (v.NET)
                FROM v8_stat_ccm_res_forrev_mi v
               WHERE v.YBOM_ID = ybom.YBOM_ID AND NVL (v.ALTERNATIVE, 0) = 0),
             0)
             MI_NET_REV,
          NVL (
             (SELECT SUM (v.VAT)
                FROM v8_stat_ccm_res_forrev_mi v
               WHERE v.YBOM_ID = ybom.YBOM_ID AND NVL (v.ALTERNATIVE, 0) = 0),
             0)
             MI_VAT,
          NVL (
             ROUND (
                (SELECT SUM (v.GROSSREV)
                   FROM v8_stat_ccm_res_forrev_pk v
                  WHERE     v.YBOM_ID = ybom.YBOM_ID
                        AND NVL (v.ALTERNATIVE, 0) = 0),
                2),
             0)
             PACKAGE_REV,
          NVL (
             ROUND (
                (SELECT SUM (v.NET)
                   FROM v8_stat_ccm_res_forrev_pk v
                  WHERE     v.YBOM_ID = ybom.YBOM_ID
                        AND NVL (v.ALTERNATIVE, 0) = 0),
                2),
             0)
             PACKAGE_NET_REV,
          NVL (
             ROUND (
                (SELECT SUM (v.VAT)
                   FROM v8_stat_ccm_res_forrev_pk v
                  WHERE     v.YBOM_ID = ybom.YBOM_ID
                        AND NVL (v.ALTERNATIVE, 0) = 0),
                2),
             0)
             PACKAGE_VAT,
          NVL (
             (SELECT SUM (v.GROSSREV)
                FROM v8_stat_ccm_res_forrev_fb v
               WHERE v.YBOM_ID = ybom.YBOM_ID AND NVL (v.ALTERNATIVE, 0) = 0),
             0)
             FB_REV,
          NVL (
             (SELECT SUM (v.NET)
                FROM v8_stat_ccm_res_forrev_fb v
               WHERE v.YBOM_ID = ybom.YBOM_ID AND NVL (v.ALTERNATIVE, 0) = 0),
             0)
             FB_NET_REV,
          NVL (
             (SELECT SUM (v.VAT)
                FROM v8_stat_ccm_res_forrev_fb v
               WHERE v.YBOM_ID = ybom.YBOM_ID AND NVL (v.ALTERNATIVE, 0) = 0),
             0)
             FB_VAT,
          NVL (
             (SELECT SUM (v.GROSSREV)
                FROM v8_stat_ccm_res_forrev_tot v
               WHERE v.YBOM_ID = ybom.YBOM_ID AND NVL (v.ALTERNATIVE, 0) = 0),
             0)
             TOT_REV,
          NVL (
             (SELECT SUM (v.NET)
                FROM v8_stat_ccm_res_forrev_tot v
               WHERE v.YBOM_ID = ybom.YBOM_ID AND NVL (v.ALTERNATIVE, 0) = 0),
             0)
             TOT_NET_REV,
          NVL (
             (SELECT SUM (v.VAT)
                FROM v8_stat_ccm_res_forrev_tot v
               WHERE v.YBOM_ID = ybom.YBOM_ID AND NVL (v.ALTERNATIVE, 0) = 0),
             0)
             TOT_VAT
     FROM ybom;


CREATE OR REPLACE VIEW v8_stat_ccm_res_forrev
AS
  select * from v8_stat_ccm_res_forrev_tot
   UNION ALL --6
  select * from v8_stat_ccm_res_forrev_exp;   


CREATE OR REPLACE VIEW V8_CCM_FS_PACKAGE
(YPKD_ID, YPKD_YBOM_ID, YPKD_NAME, YPKD_NOOFATTENDEES, YPKD_STARTTIME, 
 YPKD_ENDTIME, YPKD_ALTERNATIVE, YBOM_BASE_ZCUR_ID, ZCUR_SHORTDESC, YPKG_ID, 
 YPKG_NAME, YPKD_DISPLORDER, YEVM_ID, YEVM_NAME, YEVM_STARTTIME, 
 YEVM_ENDTIME, YEVM_NOOFATTENDEES, YBEF_ZDCO_ID, ZDCO_LONGDESC, YBEF_REV_AMOUNT, 
 YPKD_PRICE)
AS 
SELECT ypkd.YPKD_ID YPKD_ID,
          ypkd.ypkd_ybom_id YPKD_YBOM_ID,
          ypkd.ypkd_name YPKD_NAME,
          ypkd.ypkd_noofattendees YPKD_NOOFATTENDEES,
          ypkd.ypkd_starttime YPKD_STARTTIME,
          ypkd.YPKD_ENDTIME YPKD_ENDTIME,
          NVL (ypkd.YPKD_ALTERNATIVE, 0) YPKD_ALTERNATIVE,
          (SELECT ybom.YBOM_BASE_ZCUR_ID
             FROM ybom
            WHERE ybom.ybom_id = ypkd.YPKD_YBOM_ID)
             ybom_base_zcur_id,
          (SELECT zcur.zcur_shortdesc
             FROM ybom, zcur
            WHERE     ybom.ybom_id = ypkd.YPKD_YBOM_ID
                  AND ybom.ybom_base_zcur_id = zcur.zcur_id)
             ZCUR_SHORTDESC,
          ypkd.YPKD_YPKG_ID YPKG_ID,
          (SELECT ypkg.YPKG_NAME
             FROM ypkg
            WHERE ypkg.ypkg_id = ypkd.YPKD_YPKG_ID)
             YPKG_NAME,
          ypkd_displorder YPKD_DISPLORDER,
          ybef.ybef_yevm_id YEVM_ID,
          (SELECT yevm.yevm_name
             FROM yevm
            WHERE yevm.yevm_id = ybef.ybef_yevm_id)
             yevm_name,
          (SELECT yevm.YEVM_STARTTIME
             FROM yevm
            WHERE yevm.YEVM_ID = ybef.YBEF_YEVM_ID)
             YEVM_STARTTIME,
          (SELECT yevm.YEVM_ENDTIME
             FROM yevm
            WHERE yevm.YEVM_ID = ybef.YBEF_YEVM_ID)
             YEVM_ENDTIME,
          (SELECT yevm.YEVM_NOOFATTENDEES - NVL (ym.merged_yevm_noofattendees, 0)
             FROM yevm,
              (SELECT ym.yevm_id,
                  (SELECT SUM (yevm_noofattendees) merged_yevm_noofattendees
                     FROM yevm
                    WHERE     NVL (yevm_mergedto_yevm_id, 0) <> 0
                          AND yevm_mergedto_yevm_id = ym.yevm_id)
                     merged_yevm_noofattendees
             FROM yevm ym    
                         ) ym
            WHERE yevm.YEVM_ID = ybef.YBEF_YEVM_ID and yevm.YEVM_ID=ym.yevm_id)
             YEVM_NOOFATTENDEES,
          ybef.YBEF_ZDCO_ID YBEF_ZDCO_ID,
          (SELECT zdco.ZDCO_LONGDESC
             FROM zdco
            WHERE zdco.zdco_id = ybef.YBEF_ZDCO_ID)
             ZDCO_LONGDESC,
          ybef.YBEF_REV_AMOUNT YBEF_REV_AMOUNT,
          ypkd.ypkd_price YPKD_PRICE
     FROM ypkd, ybef      
    WHERE ypkd.YPKD_ID = ybef.YBEF_YPKD_ID;


CREATE OR REPLACE VIEW V8_CCM_FS_MISC
AS 
select
yevm.yevm_ybom_id yevm_ybom_id,
yevm.YEVM_ID YEVM_ID,
yevm.YEVM_ALTERNATIVE YEVM_ALTERNATIVE,
TO_NUMBER(TO_CHAR(TRUNC(yevm.YEVM_STARTTIME),'J')) DATE_NUMBER,
yevm.YEVM_STARTTIME yevm_starttime,
to_char(yevm.YEVM_STARTTIME,'HH24:MI') starttime_time,
yevm.YEVM_ENDTIME yevm_endtime,
to_char(yevm.YEVM_ENDTIME,'HH24:MI') endtime_time,
yevm.yevm_name yevm_name,
yevm.yevm_noofattendees  yevm_noofattendees,
yevm.yevm_displorder yevm_displorder,
V8_FNC_Multiple_FS_INFO(yebr.yebr_yevm_id,0)yfsc_longdesc_all,
(select yfsc.YFSC_ID from yfsc,yebs where yfsc.YFSC_ID=yebs.YEBS_YFSC_ID and yebs.YEBS_YEVM_ID=yebr.yebr_yevm_id and rownum=1) yfsc_id,
(select yfsc.YFSC_LONGDESC from yfsc,yebs where yfsc.YFSC_ID=yebs.YEBS_YFSC_ID and yebs.YEBS_YEVM_ID=yebr.yebr_yevm_id and rownum=1) yfsc_longdesc,
(select yfsc.YFSC_GENERIC_NAME from yfsc,yebs where yfsc.YFSC_ID=yebs.YEBS_YFSC_ID and yebs.YEBS_YEVM_ID=yebr.yebr_yevm_id and rownum=1) yfsc_generic_name,
(select count(yebs.yebs_id) from yebs where yebs.YEBS_YEVM_ID=yebr.yebr_yevm_id) count_yfsc,
yebr.yebr_id yebr_id,
yebr.YEBR_ALTERNATIVE YEBR_ALTERNATIVE,
nvl(yebr.yebr_yevm_id,0) yebr_yevm_id,
yebr.YEBR_QUANTITY yebr_quantity,
yebr.YEBR_NAME yebr_name,
yebr.YEBR_STARTTIME yebr_starttime,
to_char(yebr.YEBR_STARTTIME,'HH24:MI') yebr_starttime_time,
yebr.YEBR_ENDTIME yebr_endtime,
to_char(yebr.YEBR_ENDTIME,'HH24:MI') yebr_endtime_time,
yebr.YEBR_MANUALPRICE yebr_manualprice,
round(YEBR_MANUALPRICE*100/(100+
  (select (to_number(substr(ztcd.ZTCD_UDF,5,length(ztcd.ZTCD_UDF)-10))) from ztcd,ztxc,zdtl where ztcd.ZTCD_ZTXC_ID=ztxc.ZTXC_ID
  and ztxc.ZTXC_ID=zdtl.ZDTL_ZTXC_ID and zdtl.ZDTL_ZDCO_ID=YEBR_ZDCO_ID and zdtl.ZDTL_FROMTIME<=trunc(yebr.YEBR_STARTTIME) and zdtl.ZDTL_TOTIME>=trunc(YEBR.YEBR_ENDTIME))),2) YEBR_MANUALPRICE_NET,
yebr.YEBR_QUANTITY*decode(nvl(yebr.yebr_perhour,0), 0, yebr.yebr_manualprice, 1, (yebr_endtime-yebr_starttime)*24*yebr.yebr_manualprice) YEBR_TOT_RATE, 
round((yebr.YEBR_QUANTITY*decode(nvl(yebr.yebr_perhour,0), 0, yebr.yebr_manualprice, 1, (yebr_endtime-yebr_starttime)*24*yebr.yebr_manualprice))*100/(100+
  (select (to_number(substr(ztcd.ZTCD_UDF,5,length(ztcd.ZTCD_UDF)-10))) from ztcd,ztxc,zdtl where ztcd.ZTCD_ZTXC_ID=ztxc.ZTXC_ID
  and ztxc.ZTXC_ID=zdtl.ZDTL_ZTXC_ID and zdtl.ZDTL_ZDCO_ID=YEBR_ZDCO_ID and zdtl.ZDTL_FROMTIME<=trunc(yebr.YEBR_STARTTIME) and zdtl.ZDTL_TOTIME>=trunc(YEBR.YEBR_ENDTIME))),2) YEBR_TOT_RATE_NET, 
yebr.YEBR_COSTPRICE yebr_costprice,
yebr.YEBR_PKGINCL yebr_pkgincl,
yebr.YEBR_SETUPTIME yebr_setuptime,
yebr.YEBR_SETDOWNTIME yebr_setdowntime,
yebr.YEBR_DISPLORDER yebr_displorder,
yebr.YEBR_DESCRIPTION yebr_description,
yebr.YEBR_EXTERNALORDER yebr_externalorder,
(select ycit.YCIT_SOURCE_XCMS_ID from ycit where ycit.ycit_id=yebr.YEBR_YCIT_ID) ycit_source_xcms_id,
(select xcms_name1||' '||xcms_name2||' '||xcms_name3 from xcms, ycit where xcms.xcms_id=ycit.YCIT_SOURCE_XCMS_ID and ycit.YCIT_ID=yebr.YEBR_YCIT_ID) Vendor,
(select ycir.YCIR_PERHOUR from ycir where yebr.YEBR_YCIR_ID=ycir.ycir_id) ycir_perhour,
yebr.YEBR_YCIA_ID ycia_id,
(select ycia.YCIA_LONGDESC from ycia where ycia.YCIA_ID=yebr.YEBR_YCIA_ID) ycia_longdesc,
yebr.YEBR_YCIT_ID ycit_id,
(select ycit.YCIT_LONGDESC from ycit where ycit.ycit_id=yebr.YEBR_YCIT_ID) ycit_longdesc,
(select ycir.YCIR_YCRD_ID from ycir where ycir.YCIR_ID=yebr.YEBR_YCIR_ID) yrcd_id,
(select ycrd.ycrd_longdesc from ycrd,ycir where ycrd.ycrd_id=ycir.ycir_ycrd_id and ycir.YCIR_ID=yebr.YEBR_YCIR_ID) ycrd_longdesc,
v8_fnc_ccm_notes(yebr.yebr_id,'YEBR',1,1) yebr_notes_viewable,
v8_fnc_ccm_notes(yebr.yebr_id,'YEBR',0,0) yebr_notes_notviewable,
v8_fnc_ccm_notes(yebr.yebr_id,'YEBR',1,0) yebr_notes_all,
nvl((select 1 from xcno where xcno.XCNO_TABLE_ID=yebr.yebr_id and xcno.XCNO_TABLELINKEDTO='YEBR'and rownum=1),0) yebr_notes_check,
(select yevm.YEVM_YETY_ID from yevm where yevm.YEVM_ID=yebr.YEBR_YEVM_ID) yety_id,
(select yety_longdesc from yevm,yety where yevm.yevm_yety_id=yety.yety_id and yevm.YEVM_ID=yebr.YEBR_YEVM_ID) yety_longdesc,
(select ybom.YBOM_BASE_ZCUR_ID from ybom,yevm where ybom.ybom_id=yevm.yevm_ybom_id and yevm.yevm_id=yebr.yebr_yevm_id) zcur_id,
(select zcur.zcur_shortdesc from zcur,ybom,yevm where ybom.ybom_base_zcur_id=zcur.zcur_id and ybom.ybom_id=yevm.yevm_ybom_id and yevm.yevm_id=yebr.yebr_yevm_id) zcur_shortdesc,
(select yest.YEST_CANCELSTATUS from yest,yevm where yest.YEST_ID=yevm.YEVM_YEST_ID and yevm.YEVM_ID=yebr.yebr_yevm_id) yest_cancelstatus,
(select YCIT.YCIT_YCIC_ID from ycit where YCIT.YCIT_ID=YEBR.YEBR_YCIT_ID) YCIT_YCIC_ID,
(select YCIC_LONGDESC from ycit,ycic where ycic.ycic_id=YCIT.YCIT_YCIC_ID and YCIT.YCIT_ID=YEBR.YEBR_YCIT_ID) YCIC_LONGDESC
from
yebr, yevm
where 
yevm.YEVM_ID=yebr.YEBR_YEVM_ID (+)and
nvl(yevm.yevm_mergedto_yevm_id, 0)=0;


CREATE OR REPLACE VIEW V8_CCM_FS_FB
AS 
select
yevm.YEVM_YBOM_ID yevm_ybom_id,
yemn.YEMN_YEVM_ID YEVM_ID,
yevm.YEVM_ALTERNATIVE YEVM_ALTERNATIVE,
TO_NUMBER(TO_CHAR(TRUNC(YEVM.YEVM_STARTTIME),'J')) DATE_NUMBER,
yevm.YEVM_STARTTIME yevm_starttime,
to_char(yevm.YEVM_STARTTIME,'HH24:MI')starttime_time,
yevm.YEVM_ENDTIME  yevm_endtime,
to_char(yevm.YEVM_ENDTIME,'HH24:MI') endtime_time,
yevm.yevm_name  yevm_name,
yevm.yevm_noofattendees  yevm_noofattendees,
yevm.yevm_displorder  yevm_displorder,
V8_FNC_Multiple_FS_INFO(yevm.YEVM_ID,0)yfsc_longdesc_all,
(select yfsc.YFSC_ID from yfsc,yebs where yfsc.YFSC_ID=yebs.YEBS_YFSC_ID and yebs.YEBS_YEVM_ID=yevm.yevm_id and rownum=1) yfsc_id,
(select yfsc.YFSC_LONGDESC from yfsc,yebs where yfsc.YFSC_ID=yebs.YEBS_YFSC_ID and yebs.YEBS_YEVM_ID=yevm.yevm_id and rownum=1) yfsc_longdesc,
(select yfsc.YFSC_GENERIC_NAME from yfsc,yebs where yfsc.YFSC_ID=yebs.YEBS_YFSC_ID and yebs.YEBS_YEVM_ID=yevm.yevm_id and rownum=1) yfsc_generic_name,
(select count(yebs.yebs_id) from yebs where yebs.YEBS_YEVM_ID=yevm.yevm_id) count_yfsc,
yemn.YEMN_ID yemn_id,
yemn.YEMN_ALTERNATIVE YEMN_ALTERNATIVE,
nvl(yemn.YEMN_YEVM_ID,0) yemn_yevm_id,
yemn.YEMN_MENUNAME yemn_menuname,
yemn.YEMN_CHARGEDONCONSUMPTION yemn_chargedonconsumption,
yemn.YEMN_MENUPRICE yemn_menuprice,
(select sum(round(YMRD_ORG_AMOUNT*100/(100+
  (select (to_number(substr(ztcd.ZTCD_UDF,5,length(ztcd.ZTCD_UDF)-10))) from ztcd,ztxc,zdtl,yemn where ymrd.ymrd_yemn_id=yemn.yemn_id and ztcd.ZTCD_ZTXC_ID=ztxc.ZTXC_ID 
  and ztxc.ZTXC_ID=zdtl.ZDTL_ZTXC_ID and ymrd.ymrd_YEMN_ID=YEMN.YEMN_ID and zdtl.ZDTL_ZDCO_ID=YMRD_ZDCO_ID and zdtl.ZDTL_FROMTIME<=trunc(yemn.YEMN_SERVESTART) and zdtl.ZDTL_TOTIME>=trunc(yemn.YEMN_SERVEEND ))),2))
  from ymrd where ymrd_yemn_id=yemn.yemn_id) yemn_menuprice_net,
yemn.YEMN_COSTPRICE yemn_costprice,
yemn.YEMN_QUANTITY yemn_quantity,
yemn.YEMN_COMPLIMENTARY yemn_complimentary,
yemn.YEMN_NOOFSETS yemn_noofsets,
yemn.YEMN_EXPECTEDNOOFPEOPLE yemn_expectednoofpeople,
yemn.YEMN_GUARANTEEDNOOFPEOPLE YEMN_GUARANTEEDNOOFPEOPLE,
yemn.YEMN_PKGINCL yemn_pkgincl,
yemn.YEMN_SERVESTART yemn_servestart,
yemn.YEMN_SERVEEND yemn_serveend,
yemn.YEMN_DISPLORDER yemn_displorder,
yemn.YEMN_DESCRIPTION yemn_description,
yemn.YEMN_YSME_ID ysme_id,
v8_fnc_ccm_notes(yemn.yemn_id,'YEMN',1,1) yemn_notes_viewable,
v8_fnc_ccm_notes(yemn.yemn_id,'YEMN',0,0) yemn_notes_notviewable,
v8_fnc_ccm_notes(yemn.yemn_id,'YEMN',1,0) yemn_notes_all,
nvl((select 1 from xcno where xcno.XCNO_TABLE_ID=yemn.yemn_id and xcno.XCNO_TABLELINKEDTO='YEMN'and rownum=1),0) yemn_notes_check,
(select ysme.YSME_SERVEDPERPERSON from ysme where ysme.ysme_id=yemn.yemn_ysme_id) ysme_servedperperson,
(select ysme.ysme_longdesc from ysme where ysme.ysme_id=yemn.yemn_ysme_id) ysme_longdesc,
(select ybom.YBOM_BASE_ZCUR_ID from ybom where ybom.ybom_id=yevm.yevm_ybom_id ) zcur_id,
(select zcur.zcur_shortdesc from zcur,ybom where ybom.ybom_base_zcur_id=zcur.zcur_id and ybom.ybom_id=yevm.yevm_ybom_id ) zcur_shortdesc,
yemi.YEMI_ID yemi_id,
yemi.YEMI_ALTERNATIVE YEMI_ALTERNATIVE,
yemi.YEMI_YEMN_ID yemi_yemn_id,
yemi.YEMI_YCMI_ID yemi_ycmi_id,
yemi.yemi_price yemi_price,
round(YEMI_PRICE*100/(100+
  (select (to_number(substr(ztcd.ZTCD_UDF,5,length(ztcd.ZTCD_UDF)-10))) from ztcd,ztxc,zdtl,yemn where ztcd.ZTCD_ZTXC_ID=ztxc.ZTXC_ID 
  and ztxc.ZTXC_ID=zdtl.ZDTL_ZTXC_ID and yemn.YEMN_ID=yemi.YEMI_YEMN_ID and zdtl.ZDTL_ZDCO_ID=YEMI_ZDCO_ID and zdtl.ZDTL_FROMTIME<=trunc(yemn.YEMN_SERVESTART) and zdtl.ZDTL_TOTIME>=trunc(yemn.YEMN_SERVEEND ))),2) YEMI_PRICE_NET,
yemi.YEMI_INCLUDEDINTHEMENU YEMI_INCLUDEDINTHEMENU,
yemi.YEMI_NAME yemi_name,
yemi.YEMI_QUANTITY YEMI_QUANTITY,
yemi.YEMI_COURSE yemi_course,
yemi.YEMI_COURSEORDER yemi_courseorder,
yemi.YEMI_ORIGIN yemi_origin,
yemi.YEMI_VINTAGE yemi_vintage,
yemi.YEMI_PORTION yemi_portion,
yemi.YEMI_LONGDESC2 YEMI_LONGDESC2,
yemi.YEMI_LONGDESC3 YEMI_LONGDESC3,
yemi.YEMI_LONGDESC4 YEMI_LONGDESC4,
yemi.YEMI_ORIGIN2 YEMI_ORIGIN2,
yemi.YEMI_ORIGIN3 YEMI_ORIGIN3,
yemi.YEMI_DESCRIPTION yemi_description,
yemi.YEMI_YCMI_ID ycmi_id,
(select ycmi.YCMI_ARTICLENUMBER from ycmi where ycmi.ycmi_id=yemi.yemi_ycmi_id) ycmi_articlenumber,
(select ycmi.YCMI_LONGDESC from ycmi where ycmi.ycmi_id=yemi.yemi_ycmi_id) ycmi_longdesc,
(select ycmi.YCMI_LONGDESC2 from ycmi where ycmi.ycmi_id=yemi.yemi_ycmi_id) ycmi_longdesc2,
(select ycmi.YCMI_LONGDESC3 from ycmi where ycmi.ycmi_id=yemi.yemi_ycmi_id) ycmi_longdesc3,
(select ycmi.YCMI_LONGDESC4 from ycmi where ycmi.ycmi_id=yemi.yemi_ycmi_id) ycmi_longdesc4,
(select ycmi.YCMI_ORIGIN from ycmi where ycmi.ycmi_id=yemi.yemi_ycmi_id) ycmi_origin,
(select ycmi.YCMI_ORIGIN2 from ycmi where ycmi.ycmi_id=yemi.yemi_ycmi_id) ycmi_origin2,
(select ycmi.YCMI_ORIGIN3 from ycmi where ycmi.ycmi_id=yemi.yemi_ycmi_id) ycmi_origin3,
(select ycmi.YCMI_PORTION from ycmi where ycmi.ycmi_id=yemi.yemi_ycmi_id) ycmi_portion,
(select yevm.YEVM_YETY_ID from yevm where yevm.yevm_id=yemn.yemn_yevm_id) YETY_ID,
(select yety.YETY_LONGDESC from yety,yevm where yety.YETY_ID=yevm.YEVM_YETY_ID and yevm.YEVM_ID=yemn.YEMN_YEVM_ID) yety_longdesc,
(select ysmc.YSMC_FOODORBEVERAGE from ysmc,ysme where ysmc.YSMC_ID=ysme.YSME_YSMC_ID and ysme.YSME_ID=yemn.YEMN_YSME_ID) ysmc_foodorbeverage, /* Global Settings: Standard Menu Setting*/
(select ycmc.YCMC_FOODORBEVERAGE from ycmc,ycmi where ycmi.YCMI_YCMC_ID=ycmc.YCMC_ID and yemi.YEMI_YCMI_ID=ycmi.YCMI_ID) ycmc_foodorbeverage, /* Global Settings: Menuartikel Setting*/
yemi.YEMI_FOODORBEVERAGE yemi_foodorbeverage, /* gebuchter MenuItem 0=Beverage 1=Food*/
decode(nvl(yemi_foodorbeverage,2),2,(select ysmc.YSMC_FOODORBEVERAGE from ysmc,ysme where ysmc.YSMC_ID=ysme.YSME_YSMC_ID and ysme.YSME_ID=yemn.YEMN_YSME_ID),yemi_foodorbeverage) food_or_bev,
(select yest.YEST_CANCELSTATUS from yest where yest.YEST_ID=yevm.YEVM_YEST_ID ) yest_cancelstatus
from
yevm,yemn, yemi
where
yevm.yevm_id = yemn.yemn_yevm_id (+) and
yemn.YEMN_ID=yemi.YEMI_YEMN_ID (+) and
nvl(yevm.yevm_mergedto_yevm_id, 0)=0;


CREATE OR REPLACE VIEW V8_CCM_REV_FC_ZPOS
(YBOM_ID, TYP, TYP_TXT, YBOM_ARRIVAL, ZFAC_ID, 
 YEVM_ZPOS_YBOS_YPOS_DATE, ZDCO_ID, ZDCO_LONGDESC, FORECAST, REVENUE, 
 REVENUE_PM)
AS 
select
ybom_id_s ybom_id,
TYP TYP,
TYP_TXT TYP_TXT,
ybom_arrival_s ybom_arrival,
zfac_id_s zfac_id,
Date_s yevm_zpos_ybos_ypos_date,
zdco_id_s zdco_id,
zdco_longdesc_s zdco_longdesc,
sum(Forecast_s) forecast,
sum(Revenue_s) revenue,
sum(revenue_pm_s) revenue_pm
from
-- CCM Forecast Revenue per Date and Departmentcode
(select
v8_ccm_forecast_revenue.yevm_ybom_id ybom_id_s,
1 TYP,
'CCM Revenue' TYP_TXT,
(select ybom.ybom_arrival from YBOM where ybom.ybom_id=V8_CCM_FORECAST_REVENUE.yevm_ybom_id) ybom_arrival_s,
(select ybom.ybom_zfac_id from ybom where ybom.ybom_id=v8_ccm_forecast_revenue.yevm_ybom_id) Zfac_id_s,
trunc((select YEVM.YEVM_STARTTIME from yevm where yevm.yevm_id=V8_CCM_FORECAST_REVENUE.YBEF_YEVM_ID)) Date_s,
v8_ccm_forecast_revenue.zdco_id zdco_id_s,
(select zdco.zdco_longdesc from zdco where zdco.zdco_id=v8_ccm_forecast_revenue.zdco_id) zdco_longdesc_s,
v8_ccm_forecast_revenue.totalforecast forecast_s,
v8_ccm_forecast_revenue.totalrevenue revenue_s,
0 revenue_pm_s
from
v8_ccm_forecast_revenue
where
nvl(v8_ccm_forecast_revenue.alternativeflag,0)=0
and
v8_ccm_forecast_revenue.yest_cancelstatus=0
union ALL
-- PM Account Revenue per Date and Departmentcode
select
(select zfac.zfac_ybom_id from zfac where zfac.zfac_id=zpos.zpos_zfac_id) YBOM_ID_s,
2 TYP,
'PM Revenue' TYP_TXT,
(select ybom.ybom_arrival from zfac,ybom where ybom.ybom_id=zfac.zfac_ybom_id and zfac.zfac_id=zpos.zpos_zfac_id) YBOM_Arrival_s,
zpos.zpos_zfac_id zfac_id_s,
zpos.zpos_postdate Date_s,
zpos.zpos_zdco_id zdco_id_s,
(select zdco.zdco_longdesc from zdco where zdco.zdco_id=zpos.zpos_zdco_id) zdco_longdesc_s,
0 forecast_s,
0 revenue_s,
(zpos.zpos_grossunitprice*zpos.zpos_quantity) revenue_pm_s
from
zpos
where
zpos_cdt=1
UNION ALL
select
(select YBLH.YBLH_YBOM_ID from yblh where YBLH.YBLH_ID=YBOS.YBOS_YBLH_ID) YBOM_ID_s,
3 TYP,
'Blockrevenue' TYP_TXT,
(select YBOM.YBOM_ARRIVAL from yblh,ybom where YBLH.YBLH_YBOM_ID=YBOM.YBOM_ID and YBLH.YBLH_ID=YBOS.YBOS_YBLH_ID) YBOM_ARRIVAL_s,
(select YBOM.YBOM_ZFAC_ID from ybom,yblh where YBLH.YBLH_YBOM_ID=YBOM.YBOM_ID and YBLH.YBLH_ID=YBOS.YBOS_YBLH_ID) ZFAC_ID_s,
YBOS.YBOS_DATE Date_s,
9999999999 zdco_id_s,
'Blocks' zdco_longdesc_s,
0 forecast_s,
(ybos_unitprice*decode(ybld_actualnoofrooms, 0, ybld_initialnoofrooms, ybld_actualnoofrooms)) revenue_s,
0 revenue_pm_s
from
yblh, ybos, ybst, ybld
where
yblh_id=ybos_yblh_id and 
yblh_ybst_id=ybst_id and 
nvl(ybst_cancelstatus, 0)<>1 and 
yblh_id=ybld_yblh_id and 
ybld_ycat_id=ybos_ycat_id 
and ybld_date=ybos_date
UNION ALL
select
yres_ybom_id YBOM_ID_s,
4 TYP,
'Reservationrevenue' TYP_TXT,
(select YBOM.YBOM_ARRIVAL from ybom where yres_ybom_id=ybom.ybom_id) YBOM_ARRIVAL_s,
(select YBOM.YBOM_ZFAC_ID from ybom where yres_ybom_id=ybom.ybom_id) ZFAC_ID_s,
YPOS.YPOS_DATE Date_s,
8888888888 zdco_id_s,
'Reservations' zdco_longdesc_s,
0 forecast_s,
(ypos_unitprice*ypos_quantity) revenue_s,
0 revenue_pm_s
from
(select yres_id, yres_ybom_id,(select count(ydet_id) from ydet where ydet_yres_id=yres_id and nvl(ydet_ybld_id, 0)>0 ) linkedblocks,(select count(ydet_id) from ydet where ydet_yres_id=yres_id and nvl(ydet_ygrp_id, 0)>0 ) linkedgroups from yres    where yres_ybom_id>0 and nvl(yres_resstatus, 0)<>3   ) yresindividual, ypos 
where 
yresindividual.linkedblocks=0 and 
yresindividual.yres_id=ypos.ypos_yres_id
)
group by
ybom_id_s,TYP,TYP_TXT,ybom_arrival_s,zfac_id_s,Date_s,zdco_id_s,zdco_longdesc_s;


CREATE OR REPLACE FORCE VIEW V8_SYS_CHECK AS
SELECT name, RPAD(NVL(recommended_value,' '),29) recommended_value, current_value, status, category, headline
FROM
(
  SELECT RPAD('View version', 39) name, ' ' current_value, ' ' recommended_value, ' ' status, 'view parameter' category, 1 headline FROM dual
UNION ALL
  SELECT RPAD(' ', 39) name, ' ' current_value, '1.8 (2012/11/26)' recommended_value, ' ' status, 'view parameter' category, 0 headline FROM dual
UNION ALL
  SELECT RPAD('Oracle DB version', 39) name, ' ' current_value, ' ' recommended_value, ' ' status, 'oracle parameter' category, 1 headline FROM v$instance
UNION ALL
  SELECT RPAD(' ', 39) name, ' ' current_value, (SELECT version FROM v$instance) recommended_value, ' ' status, 'oracle parameter' category, 0 headline FROM DUAL
UNION ALL
  SELECT RPAD('Oracle parameter ('||(SELECT SUBSTR(version,1,4) FROM v$instance)||')', 39) name, ' ' current_value, ' ' recommended_value, ' ' status, 'oracle parameter' category, 1 headline FROM dual
UNION ALL
  SELECT LPAD(LOWER(param.name), 39) name, param.value current_value, '{TRUE}' recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN NVL(param.value,'NULL') = 'TRUE' THEN 'green' ELSE 'orange' END WHEN param2.version = '11.1' THEN CASE WHEN NVL(param.value,'NULL') = 'TRUE' THEN 'green' ELSE 'orange' END WHEN param2.version = '10.2' THEN CASE WHEN NVL(param.value,'NULL') = 'TRUE' THEN 'green' ELSE 'orange' END END value, 'oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = '_ash_enable') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT LPAD(LOWER(param.name), 39) name, param.value current_value, CASE WHEN param2.version = '11.2' THEN '{FALSE}' WHEN param2.version = '11.1' THEN '{FALSE}' WHEN param2.version = '10.2' THEN '{FALSE}' END recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN NVL(param.value,'NULL') = 'FALSE' THEN 'green' ELSE 'red' END WHEN param2.version = '11.1' THEN CASE WHEN NVL(param.value,'NULL') = 'FALSE' THEN 'green' ELSE 'red' END WHEN param2.version = '10.2' THEN CASE WHEN NVL(param.value,'NULL') = 'FALSE' THEN 'green' ELSE 'red' END END value, 'oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = '_b_tree_bitmap_plans') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT CASE WHEN param2.version = '11.2' THEN LPAD(LOWER(param.name), 39) WHEN param2.version = '11.1' THEN LPAD(LOWER(param.name), 39) WHEN param2.version = '10.2' THEN NULL END name, param.value current_value, CASE WHEN param2.version = '11.2' THEN '{1}' WHEN param2.version = '11.1' THEN '{1}' WHEN param2.version = '10.2' THEN NULL END recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN NVL(param.value,0) = 1 THEN 'green' ELSE 'orange' END WHEN param2.version = '11.1' THEN CASE WHEN NVL(param.value,0) = 1 THEN 'green' ELSE 'orange' END WHEN param2.version = '10.2' THEN NULL END value, 'oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = '_disable_flashback_archiver') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT LPAD(LOWER(param.name), 39) name, param.value current_value, CASE WHEN param2.version = '11.2' THEN '{TRUE}' WHEN param2.version = '11.1' THEN '{FALSE}' WHEN param2.version = '10.2' THEN '{FALSE}' END recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN NVL(param.value,'NULL') = 'TRUE' THEN 'green' ELSE 'red' END WHEN param2.version = '11.1' THEN CASE WHEN NVL(param.value,'NULL') = 'FALSE' THEN 'green' ELSE 'red' END WHEN param2.version = '10.2' THEN CASE WHEN NVL(param.value,'NULL') = 'FALSE' THEN 'green' ELSE 'red' END END value, 'oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = '_index_join_enabled') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT LPAD(LOWER(param.name), 39) name, param.value current_value, CASE WHEN param2.version = '11.2' THEN '{TRUE}' WHEN param2.version = '11.1' THEN '{TRUE}' WHEN param2.version = '10.2' THEN '{TRUE}' END recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN NVL(param.value,'NULL') = 'TRUE' THEN 'green' ELSE 'red' END WHEN param2.version = '11.1' THEN CASE WHEN NVL(param.value,'NULL') = 'TRUE' THEN 'green' ELSE 'red' END WHEN param2.version = '10.2' THEN CASE WHEN NVL(param.value,'NULL') = 'TRUE' THEN 'green' ELSE 'red' END END value, 'oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = '_gby_hash_aggregation_enabled') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT LPAD(LOWER(param.name), 39) name, param.value current_value, CASE WHEN param2.version = '11.2' THEN '{TRUE}' WHEN param2.version = '11.1' THEN '{FALSE}' WHEN param2.version = '10.2' THEN '{FALSE}' END recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN NVL(param.value,'NULL') = 'TRUE' THEN 'green' ELSE 'red' END WHEN param2.version = '11.1' THEN CASE WHEN NVL(param.value,'NULL') = 'FALSE' THEN 'green' ELSE 'red' END WHEN param2.version = '10.2' THEN CASE WHEN NVL(param.value,'NULL') = 'FALSE' THEN 'green' ELSE 'red' END END value, 'oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = '_new_initial_join_orders') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT CASE WHEN param2.version = '11.2.0.3' THEN LPAD(LOWER(param.name), 39) WHEN param2.version = '11.2.0.2' THEN LPAD(LOWER(param.name), 39) WHEN param2.version = '11.2.0.1' THEN LPAD(LOWER(param.name), 39) WHEN SUBSTR(param2.version,1,4) = '11.1' THEN NULL WHEN SUBSTR(param2.version,1,4) = '10.2' THEN NULL END name, param.value current_value, CASE WHEN param2.version = '11.2.0.3' THEN '{TRUE}' WHEN param2.version = '11.2.0.2' THEN '{TRUE}' WHEN param2.version = '11.2.0.1' THEN '{FALSE}' WHEN SUBSTR(param2.version,1,4) = '11.1' THEN NULL WHEN SUBSTR(param2.version,1,4) = '10.2' THEN NULL END recommended_value, CASE WHEN param2.version = '11.2.0.3' THEN CASE WHEN NVL(param.value,'NULL') = 'TRUE' THEN 'green' ELSE 'red' END WHEN param2.version = '11.2.0.2' THEN CASE WHEN NVL(param.value,'NULL') = 'TRUE' THEN 'green' ELSE 'red' END WHEN param2.version = '11.2.0.1' THEN CASE WHEN NVL(param.value,'NULL') = 'FALSE' THEN 'green' ELSE 'red' END WHEN SUBSTR(param2.version,1,4) = '11.1' THEN NULL WHEN SUBSTR(param2.version,1,4) = '10.2' THEN NULL END value, 'oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = '_optim_peek_user_binds') param, (SELECT SUBSTR(version,1,8) version FROM v$instance) param2
UNION ALL
  SELECT LPAD(LOWER(param.name), 39) name, param.value current_value, CASE WHEN param2.version = '11.2' THEN '{TRUE}' WHEN param2.version = '11.1' THEN '{FALSE}' WHEN param2.version = '10.2' THEN '{FALSE}' END recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN NVL(param.value,'NULL') = 'TRUE' THEN 'green' ELSE 'red' END WHEN param2.version = '11.1' THEN CASE WHEN NVL(param.value,'NULL') = 'FALSE' THEN 'green' ELSE 'red' END WHEN param2.version = '10.2' THEN CASE WHEN NVL(param.value,'NULL') = 'FALSE' THEN 'green' ELSE 'red' END END value, 'oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = '_optimizer_connect_by_cost_based') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT LPAD(LOWER(param.name), 39) name, param.value current_value, CASE WHEN param2.version = '11.2' THEN '{TRUE}' WHEN param2.version = '11.1' THEN '{TRUE}' WHEN param2.version = '10.2' THEN '{TRUE}' END recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN NVL(param.value,'NULL') = 'TRUE' THEN 'green' ELSE 'red' END WHEN param2.version = '11.1' THEN CASE WHEN NVL(param.value,'NULL') = 'TRUE' THEN 'green' ELSE 'red' END WHEN param2.version = '10.2' THEN CASE WHEN NVL(param.value,'NULL') = 'TRUE' THEN 'green' ELSE 'red' END END value, 'oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = '_optimizer_join_elimination_enabled') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT LPAD(LOWER(param.name), 39) name, param.value current_value, CASE WHEN param2.version = '11.2' THEN '{LINEAR}' WHEN param2.version = '11.1' THEN '{LINEAR}' WHEN param2.version = '10.2' THEN '{LINEAR}' END recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN NVL(param.value,'NULL') = 'LINEAR' THEN 'green' ELSE 'red' END WHEN param2.version = '11.1' THEN CASE WHEN NVL(param.value,'NULL') = 'LINEAR' THEN 'green' ELSE 'red' END WHEN param2.version = '10.2' THEN CASE WHEN NVL(param.value,'NULL') = 'LINEAR' THEN 'green' ELSE 'red' END END value, 'oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = '_optimizer_cost_based_transformation') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT LPAD(LOWER(param.name), 39) name, param.value current_value, CASE WHEN SUBSTR(param2.version,1,8) = '11.2.0.3' THEN '{FALSE}' WHEN SUBSTR(param2.version,1,8) = '11.2.0.2' THEN '{TRUE}' WHEN SUBSTR(param2.version,1,8) = '11.2.0.1' THEN '{TRUE}' WHEN SUBSTR(param2.version,1,4) = '11.1' THEN '{TRUE}' WHEN SUBSTR(param2.version,1,4) = '10.2' THEN '{FALSE}' END recommended_value, CASE WHEN SUBSTR(param2.version,1,8) = '11.2.0.3' THEN CASE WHEN NVL(param.value,'NULL') = 'FALSE' THEN 'green' ELSE 'red' END WHEN SUBSTR(param2.version,1,8) = '11.2.0.2' THEN CASE WHEN NVL(param.value,'NULL') = 'TRUE' THEN 'green' ELSE 'red' END WHEN SUBSTR(param2.version,1,8) = '11.2.0.1' THEN CASE WHEN NVL(param.value,'NULL') = 'TRUE' THEN 'green' ELSE 'red' END WHEN SUBSTR(param2.version,1,4) = '11.1' THEN CASE WHEN NVL(param.value,'NULL') = 'TRUE' THEN 'green' ELSE 'red' END WHEN SUBSTR(param2.version,1,4) = '10.2' THEN CASE WHEN NVL(param.value,'NULL') = 'FALSE' THEN 'green' ELSE 'red' END END value, 'oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = '_projection_pushdown') param, (SELECT SUBSTR(version,1,8) version FROM v$instance) param2
UNION ALL
  SELECT LPAD(LOWER(param.name), 39) name, param.value current_value, CASE WHEN param2.version = '11.2' THEN '{TRUE}' WHEN param2.version = '11.1' THEN '{TRUE}' WHEN param2.version = '10.2' THEN NULL END recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN NVL(param.value,'NULL') = 'TRUE' THEN 'green' ELSE 'red' END WHEN param2.version = '11.1' THEN CASE WHEN NVL(param.value,'NULL') = 'TRUE' THEN 'green' ELSE 'red' END WHEN param2.version = '10.2' THEN NULL END value, 'oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = '_replace_virtual_columns') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT LPAD(LOWER(param.name), 39) name, param.value current_value, CASE WHEN param2.version = '11.2' THEN '{10.2.0.4}' WHEN param2.version = '11.1' THEN '{10.2.0.4}' WHEN param2.version = '10.2' THEN '{10.2.0.4}' END recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN SUBSTR(param.value,1,8) = '10.2.0.4' THEN 'green' ELSE 'red' END WHEN param2.version = '11.1' THEN CASE WHEN SUBSTR(param.value,1,8) = '10.2.0.4' THEN 'green' WHEN SUBSTR(param.value,1,8) = '10.2.0.4' THEN 'green' ELSE 'red' END WHEN param2.version = '10.2' THEN CASE WHEN SUBSTR(param.value,1,8) = '10.2.0.4' THEN 'green' WHEN SUBSTR(param.value,1,8) = '10.2.0.4' THEN 'green' ELSE 'red' END END value, 'oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'compatible') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT CASE WHEN param2.version = '11.2' THEN LPAD(LOWER(param.name), 39) WHEN param2.version = '11.1' THEN LPAD(LOWER(param.name), 39) WHEN param2.version = '10.2' THEN NULL END name, param.value current_value, CASE WHEN param2.version = '11.2' THEN '{NONE}' WHEN param2.version = '11.1' THEN '{NONE}' WHEN param2.version = '10.2' THEN NULL END recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN SUBSTR(param.value,1,6) = 'NONE' THEN 'green' ELSE 'red' END WHEN param2.version = '11.1' THEN CASE WHEN SUBSTR(param.value,1,6) = 'NONE' THEN 'green' ELSE 'red' END WHEN param2.version = '10.2' THEN NULL END value, 'oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'control_management_pack_access') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT CASE WHEN param2.version = '11.2' THEN LPAD(LOWER(param.name), 39) WHEN param2.version = '11.1' THEN NULL WHEN param2.version = '10.2' THEN NULL END name, param.value current_value, CASE WHEN param2.version = '11.2' THEN '{FALSE}' WHEN param2.version = '11.1' THEN NULL WHEN param2.version = '10.2' THEN NULL END recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN param.value = 'FALSE' THEN 'green' ELSE 'red' END WHEN param2.version = '11.1' THEN NULL WHEN param2.version = '10.2' THEN NULL END value, 'oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'deferred_segment_creation') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT CASE WHEN param2.version = '11.2' THEN LPAD(LOWER(param.name), 39) WHEN param2.version = '11.1' THEN LPAD(LOWER(param.name), 39) WHEN param2.version = '10.2' THEN NULL END name, param.value current_value, CASE WHEN param2.version = '11.2' AND (SELECT SUBSTR(UPPER(PLATFORM_name),1,5) FROM V$DATABASE) = 'MICRO' THEN '{d:\oracle}' WHEN param2.version = '11.2' AND (SELECT SUBSTR(UPPER(PLATFORM_name),1,5) FROM V$DATABASE) = 'LINUX' THEN '{/u01/app/oracle}' WHEN param2.version = '11.1' AND (SELECT SUBSTR(UPPER(PLATFORM_name),1,5) FROM V$DATABASE) = 'MICRO' THEN '{d:\oracle}' WHEN param2.version = '11.1' AND (SELECT SUBSTR(UPPER(PLATFORM_name),1,5) FROM V$DATABASE) = 'LINUX' THEN '{/u01/app/oracle}' WHEN param2.version = '10.2' THEN NULL END recommended_value, CASE WHEN param2.version = '11.2' AND (SELECT SUBSTR(UPPER(PLATFORM_name),1,5) FROM V$DATABASE) = 'MICRO' THEN CASE WHEN INSTR(UPPER(param.value),'D:\ORACLE', 1) =0 OR LENGTH(param.value) <> 9 THEN 'orange' ELSE 'green' END WHEN param2.version = '11.2' AND (SELECT SUBSTR(UPPER(PLATFORM_name),1,5) FROM V$DATABASE) = 'LINUX' THEN CASE WHEN INSTR(UPPER(param.value),'/U01/APP/ORACLE', 1) =0 OR LENGTH(param.value) <> 15 THEN 'orange' ELSE 'green' END WHEN param2.version = '11.1' AND (SELECT SUBSTR(UPPER(PLATFORM_name),1,5) FROM V$DATABASE) = 'MICRO' THEN CASE WHEN INSTR(UPPER(param.value),'D:\ORACLE', 1) =0 OR LENGTH(param.value) <> 9 THEN 'orange' ELSE 'green' END WHEN param2.version = '11.1' AND (SELECT SUBSTR(UPPER(PLATFORM_name),1,5) FROM V$DATABASE) = 'LINUX' THEN CASE WHEN INSTR(UPPER(param.VALUE),'/U01/APP/ORACLE', 1) =0 OR LENGTH(param.VALUE) <> 15 THEN 'orange' ELSE 'green' END WHEN param2.version = '10.2' THEN NULL END value, 'oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'diagnostic_dest') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT CASE WHEN param2.version = '11.2.0.3' THEN LPAD(LOWER(param.name), 39) WHEN param2.version = '11.2.0.2' THEN LPAD(LOWER(param.name), 39) WHEN param2.version = '11.2.0.1' THEN LPAD(LOWER(param.name), 39) WHEN SUBSTR(param2.version,1,4) = '11.1' THEN NULL WHEN SUBSTR(param2.version,1,4) = '10.2' THEN NULL END name, param.value current_value, CASE WHEN param2.version = '11.2.0.3' THEN '{10.2.0.4}'  WHEN param2.version = '11.2.0.2' THEN '{10.2.0.4}' WHEN param2.version = '11.2.0.1' THEN '{10.2.0.4}' WHEN SUBSTR(param2.version,1,4) = '11.1' THEN '{10.2.0.4}' WHEN SUBSTR(param2.version,1,4) = '10.2' THEN NULL END recommended_value, CASE WHEN SUBSTR(param2.version,1,4) = '11.2' THEN CASE WHEN param.value = '10.2.0.4' THEN 'green' ELSE 'red' END WHEN param2.version = '10.2.0.4' THEN CASE WHEN param.value = '10.2.0.4' THEN 'green' ELSE 'red' END WHEN SUBSTR(param2.version,1,4) = '11.1' THEN /* NULL */ CASE WHEN param.value = '10.2.0.4' THEN 'green' ELSE 'red' END WHEN SUBSTR(param2.version,1,4) = '10.2' THEN NULL WHEN param2.version = '10.2.0.4' THEN NULL END value, 'oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'optimizer_features_enable') param, (SELECT SUBSTR(version,1,8) version FROM v$instance) param2
UNION ALL
  SELECT CASE WHEN param2.version = '11.2' THEN LPAD(LOWER(param.name), 39) WHEN param2.version = '11.1' THEN NULL WHEN param2.version = '10.2' THEN NULL END name, param.value current_value, CASE WHEN param2.version = '11.2' THEN '{FALSE}' WHEN param2.version = '11.1' THEN NULL WHEN param2.version = '10.2' THEN NULL END recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN param.value = 'FALSE' THEN 'green' ELSE 'red' END WHEN param2.version = '11.1' THEN NULL WHEN param2.version = '10.2' THEN NULL END value, 'oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'optimizer_secure_view_merging') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT CASE WHEN param2.version = '11.2' THEN LPAD(LOWER(param.name), 39) WHEN param2.version = '11.1' THEN NULL WHEN param2.version = '10.2' THEN NULL END name, param.value current_value, CASE WHEN param2.version = '11.2' THEN '{FALSE}' WHEN param2.version = '11.1' THEN NULL WHEN param2.version = '10.2' THEN NULL END recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN param.value = 'FALSE' THEN 'green' ELSE 'orange' END WHEN param2.version = '11.1' THEN NULL WHEN param2.version = '10.2' THEN NULL END value, 'oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'optimizer_use_invisible_indexes') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT LPAD(LOWER(param.name), 39) name, param.value current_value, '{FALSE}' recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN NVL(param.value,'NULL') = 'FALSE' THEN 'green' ELSE 'red' END WHEN param2.version = '11.1' THEN CASE WHEN NVL(param.value,'NULL') = 'FALSE' THEN 'green' ELSE 'red' END WHEN param2.version = '10.2' THEN CASE WHEN NVL(param.value,'NULL') = 'FALSE' THEN 'green' ELSE 'red' END END value, 'oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'query_rewrite_enabled') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT CASE WHEN param2.version = '11.2' THEN LPAD(LOWER(param.name), 39) WHEN param2.version = '11.1' THEN LPAD(LOWER(param.name), 39) WHEN param2.version = '10.2' THEN NULL END name, param.value current_value, CASE WHEN param2.version = '11.2' THEN '{FALSE}' WHEN param2.version = '11.1' THEN '{FALSE}' WHEN param2.version = '10.2' THEN NULL END recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN param.value = 'FALSE' THEN 'green' ELSE 'red' END WHEN param2.version = '11.1' THEN CASE WHEN param.value = 'FALSE' THEN 'green' ELSE 'red' END WHEN param2.version = '10.2' THEN NULL END value, 'oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'sec_case_sensitive_logon') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT RPAD('    SQL Plan Management (SPM)', 39) name, ' ' current_value, ' ' recommended_value, ' ' status, 'plan management parameter' category, 1 headline FROM dual
UNION ALL
  SELECT CASE WHEN param2.version = '11.2' THEN LPAD(LOWER(param.name), 39) WHEN param2.version = '11.1' THEN LPAD(LOWER(param.name), 39) WHEN param2.version = '10.2' THEN NULL END name, param.value current_value, CASE WHEN param2.version = '11.2' THEN '{FALSE}' WHEN param2.version = '11.1' THEN '{FALSE}' WHEN param2.version = '10.2' THEN NULL END recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN param.value = 'FALSE' THEN 'green' ELSE 'red' END WHEN param2.version = '11.1' THEN CASE WHEN param.value = 'FALSE' THEN 'green' ELSE 'red' END WHEN param2.version = '10.2' THEN NULL END value, 'plan management parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'optimizer_capture_sql_plan_baselines') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT CASE WHEN param2.version = '11.2' THEN LPAD(LOWER(param.name), 39) WHEN param2.version = '11.1' THEN LPAD(LOWER(param.name), 39) WHEN param2.version = '10.2' THEN NULL END name, param.value current_value, CASE WHEN param2.version = '11.2' THEN '{FALSE}' WHEN param2.version = '11.1' THEN '{FALSE}' WHEN param2.version = '10.2' THEN NULL END recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN param.value = 'FALSE' THEN 'green' ELSE 'red' END WHEN param2.version = '11.1' THEN CASE WHEN param.value = 'FALSE' THEN 'green' ELSE 'red' END WHEN param2.version = '10.2' THEN NULL END value, 'plan management parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'optimizer_use_sql_plan_baselines') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT RPAD('    Adative Cursor Sharing (ACS)', 39) name, ' ' current_value, ' ' recommended_value, ' ' status, 'cursor sharing parameter' category, 1 headline FROM dual
UNION ALL
  SELECT CASE WHEN param2.version = '11.2' THEN LPAD(LOWER(param.name), 39) WHEN param2.version = '11.1' THEN LPAD(LOWER(param.name), 39) WHEN param2.version = '10.2' THEN NULL END name, param.value current_value, CASE WHEN param2.version = '11.2' THEN '{FALSE}' WHEN param2.version = '11.1' THEN '{FALSE}' WHEN param2.version = '10.2' THEN NULL END recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN param.value = 'FALSE' THEN 'green' ELSE 'red' END WHEN param2.version = '11.1' THEN CASE WHEN param.value = 'FALSE' THEN 'green' ELSE 'red' END WHEN param2.version = '10.2' THEN NULL END value, 'cursor sharing parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = '_optimizer_adaptive_cursor_sharing') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT CASE WHEN param2.version = '11.2' THEN LPAD(LOWER(param.name), 39) WHEN param2.version = '11.1' THEN LPAD(LOWER(param.name), 39) WHEN param2.version = '10.2' THEN NULL END name, param.value current_value, CASE WHEN param2.version = '11.2' THEN '{NONE}' WHEN param2.version = '11.1' THEN '{NONE}' WHEN param2.version = '10.2' THEN NULL END recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN param.value = 'NONE' THEN 'green' ELSE 'red' END WHEN param2.version = '11.1' THEN CASE WHEN param.value = 'NONE' THEN 'green' ELSE 'red' END WHEN param2.version = '10.2' THEN NULL END value, 'cursor sharing parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = '_optimizer_extended_cursor_sharing') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT CASE WHEN param2.version = '11.2' THEN LPAD(LOWER(param.name), 39) WHEN param2.version = '11.1' THEN LPAD(LOWER(param.name), 39) WHEN param2.version = '10.2' THEN NULL END name, param.value current_value, CASE WHEN param2.version = '11.2' THEN '{NONE}' WHEN param2.version = '11.1' THEN '{NONE}' WHEN param2.version = '10.2' THEN NULL END recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN param.value = 'NONE' THEN 'green' ELSE 'red' END WHEN param2.version = '11.1' THEN CASE WHEN param.value = 'NONE' THEN 'green' ELSE 'red' END WHEN param2.version = '10.2' THEN NULL END value, 'cursor sharing parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = '_optimizer_extended_cursor_sharing_rel') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT LPAD(LOWER(param.name), 39) name, param.value current_value, CASE WHEN param2.version = '11.2' THEN '{EXACT}' WHEN param2.version = '11.1' THEN '{EXACT}' WHEN param2.version = '10.2' THEN '{EXACT}' END recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN SUBSTR(param.value,1,8) = 'EXACT' THEN 'green' ELSE 'red' END WHEN param2.version = '11.1' THEN CASE WHEN SUBSTR(param.value,1,8) = 'EXACT' THEN 'green' WHEN SUBSTR(param.value,1,8) = 'EXACT' THEN 'green' ELSE 'red' END WHEN param2.version = '10.2' THEN CASE WHEN SUBSTR(param.value,1,8) = 'EXACT' THEN 'green' WHEN SUBSTR(param.value,1,8) = 'EXACT' THEN 'green' ELSE 'red' END END value, 'cursor sharing parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'cursor_sharing') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT RPAD(' ', 39) name, ' ' current_value, ' ' recommended_value, ' ' status, 'global oracle parameter' category, 1 headline FROM v$instance
UNION ALL
  SELECT RPAD('Oracle parameter (global)', 39) name, ' ' current_value, ' ' recommended_value, ' ' status, 'global oracle parameter' category, 1 headline FROM dual
UNION ALL
  SELECT LPAD(LOWER(param.name), 39) name, param.value current_value, '{FALSE}' recommended_value, CASE WHEN param.value = 'FALSE' THEN 'green' WHEN param.value = 'TRUE' THEN 'red' END value, 'global oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'O7_DICTIONARY_ACCESSIBILITY') param
UNION ALL
  SELECT LPAD(LOWER(param.name), 39) name, param.value current_value, CASE WHEN param2.version = '11.2' THEN '{NONE/OS/DB}' WHEN param2.version = '11.1' THEN '{NONE/OS/DB}' WHEN param2.version = '10.2' THEN '{NONE}' END recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN SUBSTR(param.value,1,6) = 'NONE' THEN 'green' WHEN SUBSTR(param.value,1,6) = 'OS' THEN 'green' WHEN SUBSTR(param.value,1,6) = 'DB' THEN 'green' ELSE 'orange' END WHEN param2.version = '11.1' THEN CASE WHEN SUBSTR(param.value,1,6) = 'NONE' THEN 'green' WHEN SUBSTR(param.value,1,6) = 'OS' THEN 'green' WHEN SUBSTR(param.value,1,6) = 'DB' THEN 'green' ELSE 'orange' END WHEN param2.version = '10.2' THEN CASE WHEN SUBSTR(param.value,1,6) = 'NONE' THEN 'green' ELSE 'orange' END END value, 'global oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'audit_trail') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT LPAD(param.name, 39) name, param.value current_value, '{16, 32}' recommended_value, CASE WHEN TRUNC(param.value/16) = 1 THEN 'green' WHEN TRUNC(param.value/16) = 2 THEN 'green' WHEN TRUNC(param.value/16) = 0 THEN 'red' ELSE 'red' END value, 'global oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'db_file_multiblock_read_count' -- BUG:4648736 - TO_NUMBER ON VARCHAR2 FILED FAILS WITH ORA-01722 ON 10GR2
         AND trim('#' FROM TRANSLATE(ksppstvl,'1234567890','##########')) is null) param
UNION ALL
  SELECT LPAD(param.name, 39) name, TO_CHAR(param.value/1024/1024/1024)||' GB' current_value, '{>=20 GB}' recommended_value, CASE WHEN TRUNC(param.value/1024/1024/1024/20) = 1 THEN 'green' WHEN TRUNC(param.value/1024/1024/1024/20) = 0 THEN 'red' ELSE 'green' END value, 'global oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'db_recovery_file_dest_size' AND trim('#' FROM TRANSLATE(ksppstvl,'1234567890','##########')) is null) param
UNION ALL
  SELECT LPAD(param.name, 39) name, param.value current_value, '{8192}' recommended_value, CASE WHEN TRUNC(param.value/8192) = 1 THEN 'green' WHEN TRUNC(param.value/8192) = 0 THEN 'red' ELSE 'red' END value, 'global oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'db_block_size' AND trim('#' FROM TRANSLATE(ksppstvl,'1234567890','##########')) is null) param
UNION ALL
  SELECT LPAD(param.name, 39) name, param.value current_value, '{300}' recommended_value, CASE WHEN TRUNC(param.value/300) = 1 THEN 'green' WHEN TRUNC(param.value/300) = 0 THEN 'orange' ELSE 'orange' END value, 'global oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'fast_start_mttr_target' AND trim('#' FROM TRANSLATE(ksppstvl,'1234567890','##########')) is null) param
UNION ALL
  SELECT LPAD(param.name, 39) name, param.value current_value, '{TRUE}' recommended_value, CASE WHEN param.value = 'TRUE' THEN 'green' WHEN param.value = 'FALSE' THEN 'orange' END value, 'global oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'log_checkpoints_to_alert') param
UNION ALL
  SELECT LPAD(param.name, 39) name, param.value current_value, '{0}' recommended_value, CASE WHEN param.value = 0 THEN 'green' WHEN param.value = 0 THEN 'orange' END value, 'global oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'log_checkpoint_interval' AND trim('#' FROM TRANSLATE(ksppstvl,'1234567890','##########')) is null) param
UNION ALL
  SELECT LPAD(param.name, 39) name, param.value current_value, '{0}' recommended_value, CASE WHEN param.value = 0 THEN 'green' ELSE 'orange' END value, 'global oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'log_checkpoint_timeout' AND trim('#' FROM TRANSLATE(ksppstvl,'1234567890','##########')) is null) param
UNION ALL
  SELECT LPAD(param.name, 39) name, param.value current_value, '{2048}' recommended_value, CASE WHEN TRUNC(param.value/2048) = 1 THEN 'green' WHEN TRUNC(param.value/2048) = 0 THEN 'red' ELSE 'red' END value, 'global oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'open_cursors' AND trim('#' FROM TRANSLATE(ksppstvl,'1234567890','##########')) is null) param
UNION ALL
  SELECT LPAD(param.name, 39) name, param.value current_value, '{90}' recommended_value, CASE WHEN TRUNC(param.value/90) = 1 THEN 'green' WHEN TRUNC(param.value/90) = 0 THEN 'red' ELSE 'red' END value, 'global oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'optimizer_index_caching' AND trim('#' FROM TRANSLATE(ksppstvl,'1234567890','##########')) is null) param
UNION ALL
  SELECT LPAD(param.name, 39) name, param.value current_value, '{10}' recommended_value, CASE WHEN TRUNC(param.value/10) = 1 THEN 'green' WHEN TRUNC(param.value/10) = 0 THEN 'red' ELSE 'red' END value, 'global oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'optimizer_index_cost_adj' AND trim('#' FROM TRANSLATE(ksppstvl,'1234567890','##########')) is null) param
UNION ALL
  SELECT LPAD(param.name, 39) name, param.value current_value, '{FIRST_ROWS_100}' recommended_value, CASE WHEN param.value = 'FIRST_ROWS_100' THEN 'green' ELSE 'red' END value, 'global oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'optimizer_mode') param
UNION ALL
  SELECT LPAD(param.name, 39) name, param.value current_value, CASE WHEN param2.version = '11.2' THEN '{>=1000}' WHEN param2.version = '11.1' THEN '{>=500}' WHEN param2.version = '10.2' THEN '{>=500}' END recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN TRUNC(param.value/1000) = 1 THEN 'green' ELSE 'red' END WHEN param2.version = '11.1' THEN CASE WHEN TRUNC(param.value/500) = 1 THEN 'green' WHEN TRUNC(param.value/500) = 0 THEN 'red' ELSE 'green' END WHEN param2.version = '10.2' THEN CASE WHEN TRUNC(param.value/500) = 1 THEN 'green' WHEN TRUNC(param.value/500) = 0 THEN 'red' ELSE 'green' END END value, 'global oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'processes' AND trim('#' FROM TRANSLATE(ksppstvl,'1234567890','##########')) is null) param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT LPAD(param.name, 39) name, param.value current_value, '{OFF}' recommended_value, CASE WHEN param.value = 'OFF' THEN 'green' ELSE 'red' END value, 'global oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'recyclebin') param
UNION ALL
  SELECT LPAD(param.name, 39) name, param.value current_value, '{2048}' recommended_value, CASE WHEN TRUNC(param.value/2048) = 1 THEN 'green' WHEN TRUNC(param.value/2048) = 0 THEN 'red' ELSE 'red' END value, 'global oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'session_cached_cursors' AND trim('#' FROM TRANSLATE(ksppstvl,'1234567890','##########')) is null) param
UNION ALL
  SELECT LPAD(param.name, 39) name, param.value current_value, '{FALSE}' recommended_value, CASE WHEN param.value = 'FALSE' THEN 'green' WHEN param.value = 'TRUE' THEN 'red' END value, 'global oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'star_transformation_enabled') param
UNION ALL
  SELECT LPAD(param.name, 39) name, param.value current_value, '{TYPICAL}' recommended_value, CASE WHEN param.value = 'TYPICAL' THEN 'green' ELSE 'red' END value, 'global oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'statistics_level') param
UNION ALL
  SELECT LPAD(param.name, 39) name, param.value current_value, '{TRUE}' recommended_value, CASE WHEN param.value = 'TRUE' THEN 'green' WHEN param.value = 'FALSE' THEN 'red' END value, 'global oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'timed_statistics') param
UNION ALL
  SELECT LPAD(param.name, 39) name, param.value current_value, '{AUTO}' recommended_value, CASE WHEN param.value = 'AUTO' THEN 'green' ELSE 'orange' END value, 'global oracle parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'undo_management') param
UNION ALL
  SELECT RPAD('    Result Cache', 39) name, ' ' current_value, ' ' recommended_value, ' ' status, 'result cache parameter' category, 1 headline FROM dual
UNION ALL
  SELECT LPAD(param.name, 39) name, param.value current_value, CASE WHEN param2.version = '11.2' THEN '{0}' WHEN param2.version = '11.1' THEN '{0}' END recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN TRUNC(param.value/1024) = 0 THEN 'green' ELSE 'red' END WHEN param2.version = '11.1' THEN CASE WHEN TRUNC(param.value/1024) = 0 THEN 'green' ELSE 'red' END WHEN param2.version = '10.2' THEN NULL END value, 'result cache parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'client_result_cache_size') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT LPAD(param.name, 39) name, param.value current_value, CASE WHEN param2.version = '11.2' THEN '{10}' WHEN param2.version = '11.1' THEN '{10}' END recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN TRUNC(param.value/10) = 1 THEN 'green' ELSE 'red' END WHEN param2.version = '11.1' THEN CASE WHEN TRUNC(param.value/10) = 1 THEN 'green' ELSE 'red' END WHEN param2.version = '10.2' THEN NULL END value, 'result cache parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'result_cache_max_result') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT LPAD(param.name, 39) name, param.value current_value, CASE WHEN param2.version = '11.2' THEN '{12M}' WHEN param2.version = '11.1' THEN '{12M}' END recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN TRUNC(param.value/1024/1024) = 12 THEN 'green' ELSE 'red' END WHEN param2.version = '11.1' THEN CASE WHEN TRUNC(param.value/1024/1024) = 12 THEN 'green' ELSE 'red' END WHEN param2.version = '10.2' THEN NULL END value, 'result cache parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'result_cache_max_size') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT LPAD(param.name, 39) name, param.value current_value, CASE WHEN param2.version = '11.2' THEN '{FORCE}' WHEN param2.version = '11.1' THEN '{FORCE}' END recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN SUBSTR(param.value,1,5) = 'FORCE' THEN 'green' ELSE 'red' END WHEN param2.version = '11.1' THEN CASE WHEN SUBSTR(param.value,1,5) = 'FORCE' THEN 'green' ELSE 'red' END WHEN param2.version = '10.2' THEN NULL END value, 'result cache parameter' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'result_cache_mode') param, (SELECT SUBSTR(version,1,4) version FROM v$instance) param2
UNION ALL
  SELECT RPAD('Log Archive Mode', 39) name, ' ' current_value, '{ARCHIVELOG}' recommended_value, ' ' status, 'database' category, 1 headline FROM dual
UNION ALL
  SELECT LPAD(' ', 39) name, param.value current_value, ' ' recommended_value, CASE WHEN param.value = 'ARCHIVELOG' THEN 'green' WHEN param.value = 'NOARCHIVELOG' THEN 'red' ELSE 'red' END value, 'database' category, 0 headline FROM (SELECT log_mode value FROM v$database) param
UNION ALL
  SELECT RPAD('Memory settings (-1%/+1%)', 39) name, ' ' current_value, ' ' recommended_value, ' ' status, 'memory' category, 1 headline FROM dual
UNION ALL
 SELECT LPAD(param.name, 39) name, param.value current_value, CASE WHEN param.name = 'memory_max_target' AND param.value > (2619930052*0.01) THEN '{'||LTRIM(TO_CHAR(param.value,'999999999990'))||'}'
                                                                   WHEN param.name = 'memory_max_target' AND param.value < 2619930052 THEN '2619930052'
                                                                   WHEN param.name = 'sga_max_size' AND param.value > (2147483648*0.01) THEN '{'||LTRIM(TO_CHAR(param.value,'99999999990'))||'}'
                                                                   WHEN param.name = 'sga_max_size' AND param.value < 2147483648 THEN '2147483648'
                                                              ELSE '2147483648'
                                                               END recommended_value,
                                                              CASE WHEN param.name = 'memory_max_target' AND param.value > (2619930052*0.01) THEN 'green'
                                                                   WHEN param.name = 'sga_max_size' AND param.value > (2147483648*0.01) THEN 'green'
                                                              ELSE 'red'
                                                               END value,
   'memory' category, 0 headline
  FROM
    (SELECT ksp.ksppinm name, ksp.ksppstvl value FROM (SELECT * FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm IN ('sga_max_size','memory_max_target') AND TRUNC(ksppstvl/1) <> 0 ORDER BY ksppinm) ksp WHERE trim('#' FROM TRANSLATE(ksp.ksppstvl,'1234567890','##########')) is null AND rownum=1) param
UNION ALL
 SELECT LPAD(param.name, 39) name, param.value current_value, CASE WHEN param2.name = 'memory_max_target' AND param2.value BETWEEN 1 AND 2583691265 THEN '{'||LTRIM(TO_CHAR(TRUNC(param2.value*0.305195,0),'99999999990'))||'}'
                                                                   WHEN param2.name = 'memory_max_target' AND param2.value > 2583691264 THEN '{'||LTRIM(TO_CHAR(TRUNC(param2.value*0.415584,0),'99999999990'))||'}'
                                                                   WHEN param2.name = 'sga_max_size' AND param2.name <> 'memory_max_target' AND param2.value BETWEEN 1 AND 2113929217 THEN '{'||LTRIM(TO_CHAR(TRUNC(param2.value*0.373016,0),'99999999990'))||'}'
                                                                   WHEN param2.name = 'sga_max_size' AND param2.name <> 'memory_max_target' AND param2.value > 2113929216 THEN '{'||LTRIM(TO_CHAR(TRUNC(param2.value*0.512,0),'99999999990'))||'}'
                                                             ELSE '{'||LTRIM('786432000') 
                                                              END recommended_value,
                                                             CASE WHEN param2.name = 'memory_max_target' AND param2.value BETWEEN 1 AND 2583691265 AND TRUNC(param2.value*0.305195,0) BETWEEN (param.value-param.value*0.01) AND param.value+(param.value*0.01)  THEN 'green'
                                                                  WHEN param2.name = 'memory_max_target' AND param2.value > 2583691264 AND TRUNC(param2.value*0.415584,0) BETWEEN (param.value-param.value*0.01) AND param.value+(param.value*0.01)  THEN 'green'
                                                                  WHEN param2.name = 'sga_max_size' AND param2.value BETWEEN 1 AND 2113929217  AND TRUNC(param2.value*0.373016,0) BETWEEN (param.value-param.value*0.01) AND param.value+(param.value*0.01)  THEN 'green'
                                                                  WHEN param2.name = 'sga_max_size' AND param2.value > 2113929216 AND TRUNC(param2.value*0.512,0) BETWEEN (param.value-param.value*0.01) AND param.value+(param.value*0.01)  THEN 'green'
                                                             ELSE 'red'
                                                              END value,
    'memory' category, 0 headline
  FROM
    (SELECT ksp.ksppinm name, ksp.ksppstvl value FROM (SELECT * FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm IN ('sga_max_size','memory_max_target') AND TRUNC(ksppstvl/1) <> 0 ORDER BY ksppinm) ksp WHERE trim('#' FROM TRANSLATE(ksp.ksppstvl,'1234567890','##########')) is null AND rownum=1) param2,
    (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'db_cache_size' AND trim('#' FROM TRANSLATE(ksppstvl,'1234567890','##########')) is null) param
UNION ALL
  SELECT LPAD(param.name, 39) name, TO_CHAR(param.value/1024/1024)||' MB' CURRENT_VALUE, '{16-64 MB}' recommended_value, CASE WHEN TRUNC(param.value/1024/1024/16) = 1 THEN 'green' WHEN TRUNC(PARAM.VALUE/1024/1024/16) = 2 THEN 'green' WHEN TRUNC(param.value/1024/1024/16) = 3 THEN 'green' WHEN TRUNC(param.value/1024/1024/16) = 4 THEN 'green' ELSE 'red' END value, 'memory' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'large_pool_size' AND trim('#' FROM TRANSLATE(ksppstvl,'1234567890','##########')) is null) param
UNION ALL
  SELECT LPAD(param.name, 39) name, TO_CHAR(param.value/1024/1024)||' MB' CURRENT_VALUE, '{0 MB}' recommended_value, CASE WHEN param.value = 0 THEN 'green' ELSE 'red' END value, 'memory' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'java_pool_size' AND trim('#' FROM TRANSLATE(ksppstvl,'1234567890','##########')) is null) param
UNION ALL 
SELECT LPAD(param.name, 39) name, param.value current_value, CASE WHEN param2.name = 'memory_max_target' AND param2.value BETWEEN 1 AND 2583691265 THEN '{'||LTRIM(TO_CHAR(TRUNC(param2.value*0.182630,0),'99999999990'))||'}'
                                                                   WHEN param2.name = 'memory_max_target' AND param2.value > 2583691264 THEN '{'||LTRIM(TO_CHAR(TRUNC(param2.value*0.178571,0),'99999999990'))||'}'
                                                                   WHEN param2.name = 'sga_max_size' AND param2.name <> 'memory_max_target' AND param2.value BETWEEN 1 AND 2113929217 THEN '{'||LTRIM(TO_CHAR(TRUNC(param2.value*0.223214,0),'99999999990'))||'}'
                                                                   WHEN param2.name = 'sga_max_size' AND param2.name <> 'memory_max_target' AND param2.value > 2113929216 THEN '{'||LTRIM(TO_CHAR(TRUNC(param2.value*0.22,0),'99999999990'))||'}'
                                                             ELSE '{'||LTRIM('471859200') 
                                                              END recommended_value,
                                                             CASE WHEN param2.name = 'memory_max_target' AND param2.value BETWEEN 1 AND 2583691265 AND TRUNC(param2.value*0.182630,0) BETWEEN (param.value-param.value*0.01) AND param.value+(param.value*0.01)  THEN 'green'
                                                                  WHEN param2.name = 'memory_max_target' AND param2.value > 2583691264 AND TRUNC(param2.value*0.178571,0) BETWEEN (param.value-param.value*0.01) AND param.value+(param.value*0.01)  THEN 'green'
                                                                  WHEN param2.name = 'sga_max_size' AND param2.value BETWEEN 1 AND 2113929217  AND TRUNC(param2.value*0.223214,0) BETWEEN (param.value-param.value*0.01) AND param.value+(param.value*0.01)  THEN 'green'
                                                                  WHEN param2.name = 'sga_max_size' AND param2.value > 2113929216 AND TRUNC(param2.value*0.22,0) BETWEEN (param.value-param.value*0.01) AND param.value+(param.value*0.01)  THEN 'green'
                                                             ELSE 'red'
                                                              END value,
    'memory' category, 0 headline
  FROM
    (SELECT ksp.ksppinm name, ksp.ksppstvl value FROM (SELECT * FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm IN ('sga_max_size','memory_max_target') AND TRUNC(ksppstvl/1) <> 0 ORDER BY ksppinm) ksp WHERE trim('#' FROM TRANSLATE(ksp.ksppstvl,'1234567890','##########')) is null AND rownum=1) param2,
    (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'pga_aggregate_target' AND trim('#' FROM TRANSLATE(ksppstvl,'1234567890','##########')) is null) param
UNION ALL
SELECT LPAD(param.name, 39) name, param.value current_value, CASE WHEN param2.name = 'memory_max_target' AND param2.value BETWEEN 1 AND 2583691265 THEN '{'||'{'||LTRIM(TO_CHAR(TRUNC(param2.value*0.305195,0),'99999999990'))||'}'
                                                                   WHEN param2.name = 'memory_max_target' AND param2.value > 2583691264 THEN '{'||LTRIM(TO_CHAR(TRUNC(param2.value*0.311688,0),'99999999990'))||'}'
                                                                   WHEN param2.name = 'sga_max_size' AND param2.name <> 'memory_max_target' AND param2.value BETWEEN 1 AND 2113929217 THEN '{'||LTRIM(TO_CHAR(TRUNC(param2.value*0.373016,0),'99999999990'))||'}'
                                                                   WHEN param2.name = 'sga_max_size' AND param2.name <> 'memory_max_target' AND param2.value > 2113929216 THEN '{'||LTRIM(TO_CHAR(TRUNC(param2.value*0.384,0),'99999999990'))||'}'
                                                             ELSE '{'||LTRIM('788529152') 
                                                              END recommended_value,
                                                             CASE WHEN param2.name = 'memory_max_target' AND param2.value BETWEEN 1 AND 2583691265 AND TRUNC(param2.value*0.305195,0) BETWEEN (param.value-param.value*0.01) AND param.value+(param.value*0.01)  THEN 'green'
                                                                  WHEN param2.name = 'memory_max_target' AND param2.value > 2583691264 AND TRUNC(param2.value*0.311688,0) BETWEEN (param.value-param.value*0.01) AND param.value+(param.value*0.01)  THEN 'green'
                                                                  WHEN param2.name = 'sga_max_size' AND param2.value BETWEEN 1 AND 2113929217  AND TRUNC(param2.value*0.373016,0) BETWEEN (param.value-param.value*0.01) AND param.value+(param.value*0.01)  THEN 'green'
                                                                  WHEN param2.name = 'sga_max_size' AND param2.value > 2113929216 AND TRUNC(param2.value*0.384,0) BETWEEN (param.value-param.value*0.01) AND param.value+(param.value*0.01)  THEN 'green'
                                                             ELSE 'red'
                                                              END value,
    'memory' category, 0 headline
  FROM
    (SELECT ksp.ksppinm name, ksp.ksppstvl value FROM (SELECT * FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm IN ('sga_max_size','memory_max_target') AND TRUNC(ksppstvl/1) <> 0 ORDER BY ksppinm) ksp WHERE trim('#' FROM TRANSLATE(ksp.ksppstvl,'1234567890','##########')) is null AND rownum=1) param2,
    (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'shared_pool_size' AND trim('#' FROM TRANSLATE(ksppstvl,'1234567890','##########')) is null) param
UNION ALL
  SELECT LPAD(param.name, 39) name, TO_CHAR(param.value/1024/1024)||' MB' CURRENT_VALUE, '{16-32 MB}' recommended_value, CASE WHEN TRUNC(param.value/1024/1024/16) = 1 THEN 'green' WHEN TRUNC(PARAM.VALUE/1024/1024/16) = 2 THEN 'green' ELSE 'red' END value, 'memory' category, 0 headline FROM (SELECT ksppinm name, ksppstvl value FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'streams_pool_size' AND trim('#' FROM TRANSLATE(ksppstvl,'1234567890','##########')) is null) param
UNION ALL
  SELECT RPAD('location of table: DUAL', 39) name, ' ' current_value, '{SYSTEM}' recommended_value, ' ' status, 'database' category, 1 headline FROM dual
UNION ALL
  SELECT LPAD(' ', 39) name, param.value current_value, ' ' recommended_value, CASE WHEN param.value = 'SYSTEM' THEN 'green' ELSE 'orange' END value, 'database' category, 0 headline FROM (SELECT tablespace_name value FROM dba_tables WHERE table_name = 'DUAL') param
UNION ALL
  SELECT RPAD('Using SPFILE', 39) name, ' ' current_value, (SELECT '{spfile'||(SELECT UPPER(instance_name) FROM v$instance)||'.ora}' FROM dual) recommended_value, ' ' status, 'parameter file' category, 1 headline FROM dual
UNION ALL
  SELECT RPAD(' ', 39) name, CASE WHEN TRUNC(LENGTH(param.name)/29) = 0 THEN LPAD(param.name,29) WHEN TRUNC(LENGTH(param.name)/29) > 0 THEN LPAD(SUBSTR(param.name,1,10)||'...'||SUBSTR(param.name,-13,13),29) ELSE LPAD('{not used}',29) END current_value, ' ' recommended_value, CASE WHEN INSTR(UPPER(param.name),'SPFILE') = 0 THEN 'red' WHEN param.name IS NULL THEN 'red' ELSE 'green' END value, 'parameter file' category, 0 headline FROM (SELECT LOWER(ksppstvl) name FROM sys.x_$ksppi JOIN sys.x_$ksppsv ON (sys.x_$ksppi.indx=sys.x_$ksppsv.indx) WHERE ksppinm = 'spfile') param
UNION ALL
  SELECT RPAD('Redo Log Groups#', 39) name, ' ' current_value, '{5-10}' recommended_value, ' ' status, 'redo log groups' category, 1 headline FROM dual
UNION ALL
  SELECT LPAD(' ', 39) name, TO_CHAR(param.value) current_value, ' ' recommended_value, CASE WHEN TRUNC(param.value/5) = 1 THEN 'orange' WHEN TRUNC(param.value/5) = 2 THEN 'green' ELSE 'red' END value, 'redo log groups' category, 0 headline FROM (SELECT COUNT(1) value FROM v$log) param
UNION ALL
  SELECT RPAD('Redo Log sizes', 39) name, ' ' current_value, '{25-100 MB}' recommended_value, ' ' status, 'redo log groups' category, 1 headline FROM dual
UNION ALL
  SELECT LPAD(' ', 39) name, TO_CHAR(param.value) current_value, ' ' recommended_value, CASE WHEN TRUNC(param.value/25) = 1 THEN 'orange' WHEN TRUNC(param.value/25) = 2 THEN 'orange' WHEN TRUNC(param.value/25) = 3 THEN 'green' WHEN TRUNC(param.value/25) = 4 THEN 'green' ELSE 'red' END value, 'redo log groups' category, 0 headline FROM (SELECT MAX(bytes/1024/1024) value FROM v$log) param
UNION ALL
  SELECT RPAD('Missing Tablespaces', 39) name, ' ' current_value, '{none}' recommended_value, ' ' status, 'tablespaces' category, 1 headline FROM dual
UNION ALL
  SELECT LPAD(V$TABLESPACE.name, 39) name, ' ' current_value, ' ' recommended_value, 'red' status, 'tablespaces' category, 0 headline FROM v$tablespace WHERE v$tablespace.name NOT IN ('V8ARCHIVE', 'V8CONF', 'V8INDEX', 'V8LARGE', 'V8MAIN', 'V8TEMP', 'MFCONF', 'MFINDEX', 'MFMAIN', 'MFLARGE', 'MFTEMP') AND (v$tablespace.name LIKE 'V8%' OR v$tablespace.name LIKE 'MF%')
UNION ALL
  SELECT RPAD('Tablespace Size', 39) name, ' ' current_value, '{overall full <75%}' recommended_value, ' ' status, 'tablespaces' category, 1 headline FROM DUAL
UNION ALL
  SELECT LPAD(TBS.TBS_name, 39) name, TO_CHAR(tbs."Allocated GB",'9999990.90')||' GB ('||TO_CHAR(tbs.overall_free_pct,'90.90')||'%)' current_value, ' ' recommended_value, CASE WHEN TRUNC(tbs.overall_free_pct/75) = 0 THEN 'green' ELSE 'red' END value, 'tablespaces' category, 0 headline FROM (SELECT NVL(tbs_file.tablespace_name, NVL(tbs_free.tablespace_name,'UNKNOWN')) tbs_name, kbytes_alloc "Allocated GB", kbytes_alloc-NVL(kbytes_free,0) "Used GB", NVL(kbytes_free,0) "Free GB", ROUND(((kbytes_alloc-NVL(kbytes_free,0))/kbytes_alloc)*100,2) overall_free_pct, data_files "Data Files" FROM(SELECT SUM(bytes)/1024/1024/1024 Kbytes_free, MAX(bytes)/1024/1024/1024 largest, tablespace_name FROM dba_free_space GROUP BY tablespace_name) tbs_free, (SELECT SUM(bytes)/1024/1024/1024 Kbytes_alloc, tablespace_name, COUNT(*) data_files FROM dba_data_files GROUP BY tablespace_name) tbs_file WHERE tbs_free.tablespace_name (+) = tbs_file.tablespace_name AND (tbs_free.tablespace_name LIKE 'V8%' OR tbs_free.tablespace_name LIKE 'MF%') ORDER BY tbs_free.tablespace_name) TBS
UNION ALL
  SELECT RPAD('Oracle Text Search', 39) name, ' ' current_value, '{INSTALLED}' recommended_value, ' ' status, 'oracle text' category, 1 headline FROM dual
UNION ALL
  SELECT LPAD(' ', 39) name, CASE WHEN param.value = 1 THEN 'INSTALLED' ELSE 'NOT INSTALLED' END current_value, ' ' recommended_value, CASE WHEN param.value = 1 THEN 'green' ELSE 'red' END value, 'oracle text' category, 0 headline FROM (SELECT COUNT(username) value FROM all_users WHERE UPPER(username) LIKE 'CTXSYS') param
)
WHERE name IS NOT NULL;
/
