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

--Function to get ZDCO_ID's of department codes which are able to post against package allowance. Will be used in POS8 view(s).

CREATE OR REPLACE FUNCTION V8_FNC_YPAZ_ZDCO_ID(ypacid in number) RETURN VARCHAR2 IS
cursor c is 
  select YPAZ.YPAZ_ZDCO_ID ALLOWANCE_ZDCO_ID from ypaz where YPAZ.YPAZ_YPAC_ID=ypacid;   
r c%rowtype;
zdc VARCHAR2(2000);
BEGIN
   zdc := '';
   for r in c loop
     if zdc is not null then
    zdc:= zdc || ', ';
  end if;
  zdc:= zdc || to_char(r.ALLOWANCE_ZDCO_ID);
   end loop;
   RETURN zdc;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       RAISE;
END V8_FNC_YPAZ_ZDCO_ID;
/

--select V8_FNC_YPAZ_ZDCO_ID(ypac_id) ALLOWANCE_ZDCO_ID from ypac where ypac_id=1017 

CREATE OR REPLACE Function getDeptCodesForZall
   ( ZALLID_IN IN number )
   RETURN varchar2
IS
   DeptCodes varchar2(1000);
   
   CURSOR c1 IS
      SELECT zdco_id 
      FROM   zald, zdco 
      WHERE  zald_zall_id = ZALLID_IN 
        AND  zald_zdco_id = zdco_id 
      UNION 
      SELECT zdco_id 
       FROM  zall, ypac, zdco 
      WHERE  ypac_id = zall_ypac_id 
        AND  zall_id = ZALLID_IN 
        AND  ypac_zdco_id = zdco_id;

BEGIN
   DeptCodes := 'a';
    
   FOR DeptRec IN  c1
   LOOP
     IF DeptCodes = 'a' THEN
        DeptCodes:= DeptRec.zdco_id; 
     ELSE 
        DeptCodes:= DeptCodes || ', ' || DeptRec.zdco_id; 
     END IF;   
   END LOOP;


   RETURN DeptCodes;

EXCEPTION
WHEN OTHERS THEN
   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
/

CREATE OR REPLACE VIEW V8_REP_NAME
AS 
select
xcms_id XCMS_ID,
xcms_type XCMS_TYPE,
xcms_active XCMS_ACTIVE,
XCID.XCID_ADDRGREET XCID_ADDRGREET,
decode(xcms_type,1,xcms_name1||decode(nvl(xcms_name2,''),'','',' ')||xcms_name2||decode(nvl(xcms_name3,''),'','',' ')||xcms_name3,trim(decode(nvl(xcms_name3,''),'','',xcms_name3||' ')||decode(nvl(xcms_name2,''),'','',xcms_name2||' ')||xcms_name1)) NAME,
decode(xcms_type,1,xcms_name1||decode(nvl(xcms_name2,''),'','',' ')||xcms_name2||decode(nvl(xcms_name3,''),'','',' ')||xcms_name3,trim(decode(nvl(xcms_name1,''),'','',xcms_name1||decode(nvl(xcms_name3,''),'','',',')||' ')||decode(nvl(xcms_name3,''),'','',xcms_name3||' ')||xcms_name2)) NAME2,
(decode(xcms_type,1,xcms_name1||decode(nvl(xcms_name2,''),'','',' ')||xcms_name2||decode(nvl(xcms_name3,''),'','',' ')||xcms_name3,decode(nvl(xcid_addrgreet,' '),' ','',xcid_addrgreet||' ')||trim(decode(nvl(xcms_name3,''),'','',xcms_name3||' ')||decode(nvl(xcms_name2,''),'','',xcms_name2||' ')||xcms_name1))) NAME3,
(decode(xcms_type,1,xcms_name1||decode(nvl(xcms_name2,''),'','',' ')||xcms_name2||decode(nvl(xcms_name3,''),'','',' ')||xcms_name3,decode(nvl(xcid_addrgreet,' '),' ','',xcid_addrgreet||chr(13)||chr(10))||trim(decode(nvl(xcms_name3,''),'','',xcms_name3||' ')||decode(nvl(xcms_name2,''),'','',xcms_name2||' ')||xcms_name1))) NAME4,
(decode(xcms_type,1,xcms_name1||decode(xcms_name2,null,decode(xcms_name3,null,'',chr(13)||chr(10)||xcms_name3),decode(xcms_name3,null,'',chr(13)||chr(10)||xcms_name2||chr(13)||chr(10)||xcms_name3)),decode(nvl(xcid_addrgreet,' '),' ','',xcid_addrgreet||chr(13)||chr(10))||trim(decode(nvl(xcms_name3,''),'','',xcms_name3||' ')||decode(nvl(xcms_name2,''),'','',xcms_name2||' ')||xcms_name1))) NAME5,
decode(xcms_type,1,xcms_name1||decode(nvl(xcms_name2,''),'','',' ')||xcms_name2||decode(nvl(xcms_name3,''),'','',' ')||xcms_name3,trim(decode(nvl(xcms_name1,''),'','',xcms_name1||decode(nvl(xcms_name3,''),'','',',')||' ')||decode(nvl(xcms_name3,''),'','',xcms_name3||' ')||xcms_name2)||decode(nvl(xcid_addrgreet,''),'','',', '||xcid_addrgreet)||' '||xcid_title) NAME6,
decode(xcms_type,1,xcms_name1||decode(nvl(xcms_name2,''),'','',' ')||xcms_name2||decode(nvl(xcms_name3,''),'','',' ')||xcms_name3,trim(decode(nvl(xcms_name1,''),'','',xcms_name1||decode(nvl(xcms_name3,''),'','',',')||' ')||decode(nvl(xcms_name3,''),'','',xcms_name3||' ')||xcms_name2)||decode(nvl(xcid_addrgreet,''),'','',' '||xcid_addrgreet)||' '||xcid_title) NAME7,
XCID.XCID_ADDRGREET||'+'||xcms.xcms_name1||'+'||xcms.xcms_name3 NAME_MyCentral,
decode(xcms_type,1,'',decode(nvl(xcid_lettgreet,' '),' ','',xcid_lettgreet||' ')||trim(decode(nvl(xcms.xcms_name3,''),'','',xcms.xcms_name3||' ')||decode(nvl(xcms_name2,''),'','',xcms_name2||' ')||xcms_name1)) NAME_GREET,
decode(xcms_type,1,'',decode(nvl(xcid_lettgreet,' '),' ','',xcid_lettgreet||' ')||trim((xcms_name1)))||',' NAME_GREET2,
xcid_position                                       XCID_POSITION,
xcid_department                                   XCID_DEPARTMENT,
XCID_TITLE                                           XCID_TITLE,
(select XCCD.XCCD_DIVISION from xccd where xccd.XCCD_ID=xcms.XCMS_ID) XCCD_DIVISION
from xcms,xcid
where xcms.xcms_id=xcid.xcid_id(+);

create or replace view v8_sys_allroomspercategory as
select wdat_date,yrms_ycat_id,count(yrms_id) noofrooms
from yrms,wdat
where
      yrms_validfrom<= wdat_date
  and yrms_validuntil>= wdat_date
group by wdat_date,yrms_ycat_id;

create or replace view v8_sys_noofrooms_all as
select wdat_date,count(yrms_id) noofrooms
from yrms,wdat,ycat
where
      yrms_validfrom<= wdat_date
  and yrms_validuntil>= wdat_date
  and nvl(yrms_suite,0)=0
  and ycat_id=yrms_ycat_id
  and ycat_notinavl=0
  and not exists (select YRST_YRMS_ID
                  from YRST,YRSC
                  where YRST_FROMTIME<=WDAT_DATE
                    and trunc(YRST_UNTILTIME)>WDAT_DATE
                    and YRST.YRST_YRSC_ID=YRSC_ID
                    and YRSC.YRSC_AFFECTAILABILITY=1
                    and YRST_YRMS_ID=YRMS_ID)
group by wdat_date;

/*---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

create or replace view v8_sys_avl_def_combined as
select tot.YRMS_YCAT_ID ycat_id,tot.WDAT_DATE wdat_date,tot.NOOFROOMS noofrooms,'tot' type from v8_sys_noofroomspercategory tot
union
select yocc.YDET_YCAT_ID ycat_id,yocc.YDET_DATE wdat_date,-yocc.defNOOFROOMS noofrooms,'yocc' type from v8_sys_ydet_occ_wb yocc
union
select bocc.YBLD_YCAT_ID ycat_id,bocc.YBLD_DATE wdat_date,-bocc.defNOOFROOMS noofrooms,'bocc' type from v8_sys_ybld_occ bocc;

create or replace view v8_sys_avl_def_combined_all as
select tot.WDAT_DATE wdat_date,tot.NOOFROOMS noofrooms,'tot' type from v8_sys_noofrooms_all tot
union
select yocc.YDET_DATE wdat_date,-yocc.defNOOFROOMS noofrooms,'yocc' type from v8_sys_ydet_occ_wb_all yocc
union
select bocc.YBLD_DATE wdat_date,-bocc.defNOOFROOMS noofrooms,'bocc' type from v8_sys_ybld_occ_all bocc;

create or replace view v8_sys_avl_tent_combined as
select tot.YRMS_YCAT_ID ycat_id,tot.WDAT_DATE wdat_date,tot.NOOFROOMS noofrooms,'tot' type from v8_sys_noofroomspercategory tot
union
select yocc.YDET_YCAT_ID ycat_id,yocc.YDET_DATE wdat_date,-((yocc.tentNOOFROOMS+yocc.defNOOFROOMS)-yocc.tentBLOCKdefRESROOM) noofrooms,'yocc' type from v8_sys_ydet_occ_wb yocc
union
select bocc.YBLD_YCAT_ID ycat_id,bocc.YBLD_DATE wdat_date,-(bocc.tentNOOFROOMS+bocc.defNOOFROOMS) noofrooms,'bocc' type from v8_sys_ybld_occ bocc;

create or replace view v8_sys_avl_tent_combined_all as
select tot.WDAT_DATE wdat_date,tot.NOOFROOMS noofrooms,'tot' type from v8_sys_noofrooms_all tot
union
select yocc.YDET_DATE wdat_date,-((yocc.tentNOOFROOMS+yocc.defNOOFROOMS)-yocc.tentBLOCKdefRESROOM) noofrooms,'yocc' type from v8_sys_ydet_occ_wb_all yocc
union
select bocc.YBLD_DATE wdat_date,-(bocc.tentNOOFROOMS+bocc.defNOOFROOMS) noofrooms,'bocc' type from v8_sys_ybld_occ_all bocc;

create or replace view v8_sys_avl_def_sumup as
select ycat_id,wdat_date,sum(noofrooms) noofrooms
from v8_sys_avl_def_combined
group by ycat_id,wdat_date;

create or replace view v8_sys_avl_def_sumup_all as
select wdat_date,sum(noofrooms) noofrooms
from v8_sys_avl_def_combined_all
group by wdat_date;

create or replace view v8_sys_avl_tent_sumup as
select ycat_id,wdat_date,sum(noofrooms) noofrooms
from v8_sys_avl_tent_combined
group by ycat_id,wdat_date;

create or replace view v8_sys_avl_tent_sumup_all as
select wdat_date,sum(noofrooms) noofrooms
from v8_sys_avl_tent_combined_all
group by wdat_date;

create or replace view v8_rep_avlcombined as 
select day,ycat_id,sum(maxrooms) maxrooms,sum(defrooms) defrooms,sum(tentrooms) tentrooms,sum(tbdrrooms) tbdrrooms,sum(defblock) defblock,sum(tentblock) tentblock,sum(ooo) ooo,sum(overbooking) overgbooking
 from (
select wdat_date day,yrms_ycat_id ycat_id,noofrooms  maxrooms,0 defrooms,0 tentrooms,0 tbdrrooms,0 defblock,0 tentblock,0 ooo,0 overbooking from v8_sys_noofroomspercategory
union all
select ydet_date,ydet_ycat_id,0 maxrooms,defnoofrooms defrooms,tentnoofrooms tentrooms,tentblockdefresroom tbdrrooms,0 defblock,0 tentblock,0 ooo,0 overbooking from v8_sys_ydet_occ_wb
union all 
select ybld_date day,ybld_ycat_id,0 maxrooms,0 defrooms,0 tentrooms,0 tbdrrooms,defnoofrooms defblock,tentnoofrooms tentblock,0 ooo,0 overbooking from v8_sys_ybld_occ  
union all
select wdat_date,yrms_ycat_id,0 maxrooms,0 defrooms,0 tentrooms,0 tbdrrooms,0 defblock,0 tentblock,count(yrms_id) ooo,0 overbooking
from yrms,wdat
where
      yrms_validfrom<= wdat_date
  and yrms_validuntil>= wdat_date
  and exists (select YRST_YRMS_ID
                  from YRST,YRSC
				  where YRST_FROMTIME<=WDAT_DATE
				    and trunc(YRST_UNTILTIME)>WDAT_DATE
					and YRST.YRST_YRSC_ID=YRSC_ID
					and YRSC.YRSC_AFFECTAILABILITY=1
                    and YRST_YRMS_ID=YRMS_ID)
group by wdat_date,yrms_ycat_id
union all
select yovb_date day,yovb_ycat_id,0 maxrooms,0 defrooms,0 tentrooms,0 tbdrrooms,0 defblock,0 tentblock,0 ooo,yovb_value overbooking from yovb where yovb_type=2 
) group by day,ycat_id; 

/*---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

create or replace view v8_sys_avl_def_arrival_room as
select wdat_Date,sum(noofrooms) noofrooms from (
select wdat_date
      ,decode(nvl(ydet_sharenum,0),0,sum(case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end)
                                    ,avg(case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end)
             ) noofrooms
      ,decode(nvl(ydet_sharenum,0),0,sum(case when yres_exparrtime=yres_expdeptime then ydet_noofrooms else 0 end)
                                    ,avg(case when yres_exparrtime=yres_expdeptime then ydet_noofrooms else 0 end)
             ) noofroomsDayUse
from ydet yd,yres,wdat,ycat
where 
    ydet_yres_id=yres_id
and ydet_date=wdat_date
and yres.YRES_EXPDEPTIME>  V8_sys_fideliohistorydate 
and ydet_date=yres_exparrtime
and nvl(yres_resstatus,0)= 1
and ycat_id=ydet_ycat_id
and ycat_notinavl=0
and (nvl(ydet_sharenum,0)= 0
     or
	 not exists (select d.ydet_sharenum 
	                       from ydet d,yres r 
						   where d.ydet_yres_id=r.yres_id 
						     and d.ydet_sharenum=yd.ydet_sharenum 
							 and d.ydet_id<>yd.ydet_id 
							 and d.ydet_date>r.yres_exparrtime
							 and nvl(r.yres_resstatus,0)=1
						   )
	)
group by wdat_date,ydet_sharenum)
group by wdat_date;

create or replace view v8_sys_avl_tent_arrival_room as
select wdat_Date,sum(noofrooms) noofrooms from (
select wdat_date
      ,decode(nvl(ydet_sharenum,0),0,sum(case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end)
                                    ,avg(case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end)
             ) noofrooms
      ,decode(nvl(ydet_sharenum,0),0,sum(case when yres_exparrtime=yres_expdeptime then ydet_noofrooms else 0 end)
                                    ,avg(case when yres_exparrtime=yres_expdeptime then ydet_noofrooms else 0 end)
             ) noofroomsDayUse
from ydet yd,yres,wdat,ycat
where 
    ydet_yres_id=yres_id
and ydet_date=wdat_date
and yres.YRES_EXPDEPTIME>  V8_sys_fideliohistorydate 
and ydet_date=yres_exparrtime
and nvl(yres_resstatus,0)<> 3
and ycat_id=ydet_ycat_id
and ycat_notinavl=0
and (nvl(ydet_sharenum,0)= 0
     or
	 not exists (select d.ydet_sharenum 
	                       from ydet d,yres r 
						   where d.ydet_yres_id=r.yres_id 
						     and d.ydet_sharenum=yd.ydet_sharenum 
							 and d.ydet_id<>yd.ydet_id 
							 and d.ydet_date>r.yres_exparrtime
							 and nvl(r.yres_resstatus,0)<>3
						   )
	)
group by wdat_date,ydet_sharenum)
group by wdat_date;

create or replace view v8_sys_avl_def_arrival_adult as
select wdat_date
      ,sum(case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end*ydet_noofrooms) noofadults 
      ,sum(case when yres_exparrtime= yres_expdeptime then ydet_noofrooms else 0 end*ydet_noofrooms) noofadultsDayUse 
from ydet,yres,wdat,ycat
where 
    ydet_yres_id=yres_id
and ydet_date=wdat_date
and yres.YRES_EXPDEPTIME>  V8_sys_fideliohistorydate 
and ydet_date=yres_exparrtime
and nvl(yres_resstatus,0)= 1
and ycat_id=ydet_ycat_id
and ycat_notinavl=0
group by wdat_date;

create or replace view v8_sys_avl_tent_arrival_adult as
select wdat_date 
      ,sum(case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end*ydet_noofrooms) noofadults 
      ,sum(case when yres_exparrtime= yres_expdeptime then ydet_noofrooms else 0 end*ydet_noofrooms) noofadultsDayUse 
from ydet,yres,wdat,ycat
where 
    ydet_yres_id=yres_id
and ydet_date=wdat_date
and yres.YRES_EXPDEPTIME>  V8_sys_fideliohistorydate 
and ydet_date=yres_exparrtime
and nvl(yres_resstatus,0)<> 3
and ycat_id=ydet_ycat_id
and ycat_notinavl=0
group by wdat_date;

create or replace view v8_sys_avl_combined_arrival as
select
   wdat_date
   ,sum(decode(type,1,num,0)) defroom
   ,sum(decode(type,2,num,0)) tentroom
   ,sum(decode(type,3,num,0)) defadult
   ,sum(decode(type,4,num,0)) tentadult
from
(
select wdat_date,1 type,noofrooms num from v8_sys_avl_def_arrival_room a 
union 
select wdat_date,2 type,noofrooms num from v8_sys_avl_tent_arrival_room a 
union 
select wdat_date,3 type,noofadults num from v8_sys_avl_def_arrival_adult a 
union 
select wdat_date,4 type,noofadults num from v8_sys_avl_tent_arrival_adult a 
union 
select wdat_date,0 type,0 num from wdat
)
group by wdat_date;

/*---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

create or replace view v8_sys_avl_def_arrival_xcac as
select wdat_date,yrgl_xcac_id xcac_id,xcac_shortdesc, xcac_Longdesc,sum(yrgl_number*ydet_noofrooms) num 
from ydet,yres,wdat,yrgl, xcac,ycat
where 
    ydet_yres_id=yres_id
and ydet_date=wdat_date
and ydet_date=yres_exparrtime
and nvl(yres_resstatus,0)= 1
and yres.YRES_EXPDEPTIME>  V8_sys_fideliohistorydate 
and yrgl_ydet_id=ydet_id
and xcac_id=yrgl_xcac_id
and ycat_id=ydet_ycat_id
and ycat_notinavl=0
group by wdat_date,yrgl_xcac_id,xcac_shortdesc,xcac_Longdesc;

--for compatibility reasons i keep old views (ie for reports or 3rd party products

create or replace view v8_sys_ydet_def_occ_wb as select ydet_ycat_id,ydet_date,defnoofrooms noofrooms from v8_sys_ydet_occ_wb;

create or replace view v8_sys_ydet_def_occ_wb_all as select ydet_date,defnoofrooms noofrooms from v8_sys_ydet_occ_wb_all;

create or replace view v8_sys_ydet_tent_occ_wb as select ydet_ycat_id,ydet_date,((tentnoofrooms+defnoofrooms)-tentblockdefresroom) noofrooms from v8_sys_ydet_occ_wb;

create or replace view v8_sys_ydet_tent_occ_wb_all as select ydet_date,((tentnoofrooms+defnoofrooms)-tentblockdefresroom) noofrooms from v8_sys_ydet_occ_wb_all;

create or replace view v8_sys_ybld_def_occ as select ybld_ycat_id,ybld_date,defnoofrooms noofrooms from v8_sys_ybld_occ;

create or replace view v8_sys_ybld_def_occ_all as select ybld_date,defnoofrooms noofrooms from v8_sys_ybld_occ_all;

create or replace view v8_sys_ybld_tent_occ as select ybld_ycat_id,ybld_date,tentnoofrooms+defnoofrooms noofrooms from v8_sys_ybld_occ;

create or replace view v8_sys_ybld_tent_occ_all as select ybld_date,tentnoofrooms+defnoofrooms noofrooms from v8_sys_ybld_occ_all;

/*---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
create or replace view V8_SYS_BLOCK_SUMUP as
select
  YBLD_DATE, 
  nvl(sum(BlockTent),0) BlockTent, 
  nvl(sum(BlockDef),0) BlockDef, 
  nvl(sum(TentPickedUp),0) TentPickedUp, 
  nvl(sum(ActPickedUp),0) ActPickedUp,
  nvl(sum(YBLD_NOOFADULTS),0) NoOfAdults
  from (  
SELECT 
  YBLD_DATE, 
  decode(YBST_RESSTATUS, 0, YBLD_ACTUALNOOFROOMS, 0) BlockTent, 
  decode(YBST_RESSTATUS, 1, YBLD_ACTUALNOOFROOMS, 0) BlockDef,
  (select sum(YDET_NOOFROOMS) from ydet, yres where yres_id=ydet_yres_id and ydet_ybld_id=ybld_id and YRES_RESSTATUS=0) TentPickedUp, 
  (select count(YDET_NOOFROOMS) from ydet, yres where yres_id=ydet_yres_id and ydet_ybld_id=ybld_id and YRES_RESSTATUS=1) ActPickedUp, 
  YBLD_NOOFADULTS 
FROM YBLD, yblh, ybst
where 
     ybld_yblh_id=yblh_id
and  yblh_ybst_id=ybst_id     
and  YBST_RESSTATUS<>3
)
group by 
  ybld_date
order by 
  ybld_date;
  
-- tt 19504 
-- Occupany for exact 1 pax

CREATE OR REPLACE VIEW V8_REP_YBLD_OCC_E1P
AS 
select
   ybld_ycat_id,
   ybld_date,
   sum(decode(ybst_resstatus,1,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms),0)) defnoofrooms,
   sum(decode(ybst_resstatus,1,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms),nvl(ybld_expectednoofrooms,0))) defnoofroomsbookingpos,
   sum(decode(ybst_resstatus,1,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms*nvl(ybld_noofadults,0)),0)) defnoofadults,
   sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms),0)) tentnoofrooms,
   sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms-nvl(ybld_expectednoofrooms,0)),0)) tentnoofroomsbookingpos,
   sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms*nvl(ybld_noofadults,0)),0)) tentnoofadults
from ybld,yblh,ybst,fideliodate d
where
    ybld_yblh_id=yblh_id
and YBLH_YBST_ID=ybst_id
and YBST_RESSTATUS<>3
and nvl(YBLD.YBLD_NOOFADULTS,0)=1
group by ybld_ycat_id,ybld_date;

create or replace view v8_rep_ydet_occ_wb_e1p as
select ydet_ycat_id,ydet_date
      ,sum(defnoofrooms) defnoofrooms
      ,sum(defnoofroomsDayUse) defnoofroomsDayUse
      ,sum(tentnoofrooms) tentnoofrooms
      ,sum(tentnoofroomsDayUse) tentnoofroomsDayUse
      ,sum(tentblockdefresroom) tentblockdefresroom
      ,sum(tentblockdefresroomDayUse) tentblockdefresroomDayUse
from 
 (
  select
   ydet_ycat_id,
   ydet_date,
          decode(sign(nvl(ybld_cutoffdate,d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,sum(decode(yres_resstatus,1,case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end,0))
                  , sum(decode(ybst_resstatus,0,decode(yres_resstatus,1,case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end,0),0))
                   -sum(decode(ybst_resstatus,1,decode(yres_resstatus,1,case when yres_exparrtime= yres_expdeptime then ydet_noofrooms else 0 end,0),0))
          ) defnoofrooms,
          sum(decode(yres_resstatus,1,case when yres_exparrtime=yres_expdeptime then ydet_noofrooms else 0 end,0))
          defnoofroomsDayUse,
          decode(sign(nvl(ybld_cutoffdate,d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  , sum(decode(yres_resstatus,0,case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end,0))          
                  ,-sum(decode(ybst_resstatus,0,decode(yres_resstatus,0,case when yres_exparrtime= yres_expdeptime then ydet_noofrooms else 0 end,0),0)) --substract tentative res only because definitive day use (and non day use) will be substrated by tentblockdefresroom
          ) tentnoofrooms,
          decode(sign(nvl(ybld_cutoffdate,d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,sum(decode(yres_resstatus,0,case when yres_exparrtime=yres_expdeptime then ydet_noofrooms else 0 end,0))          
                  ,sum(decode(ybst_resstatus,0,decode(yres_resstatus,0,case when yres_exparrtime= yres_expdeptime then ydet_noofrooms else 0 end,0),0))
                  )
          tentnoofroomsDayUse,
          decode(sign(nvl(ybld_cutoffdate,d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,0
                  ,sum(decode(ybst_resstatus,0,decode(yres_resstatus,1,case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end,0),0))
                  )
          tentblockdefresroom,
          decode(sign(nvl(ybld_cutoffdate,d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,0
                  ,sum(decode(ybst_resstatus,0,decode(yres_resstatus,1,case when yres_exparrtime =yres_expdeptime then ydet_noofrooms else 0 end,0),0))
                  )
          tentblockdefresroomDayUse
 from ydet,yres,ybld,yblh,ybst,fideliodate d
 where
     ydet_yres_id=yres_id(+)
 and ydet_ybld_id=ybld_id(+)
 and ybld_yblh_id=yblh_id(+)
 and yblh_ybst_id=ybst_id(+)
 and yres_resstatus<>3
 --and yres_exparrtime<>yres_expdeptime
 and nvl(ydet_sharenum,0)=0
 and ((ydet_adultno=1 and not exists (select 1 from yrgl where yrgl_ydet_id=ydet_id and yrgl_number>0)) or
(ydet_adultno=0 and exists (select 1 from yrgl where yrgl_ydet_id=ydet_id and yrgl_number=1)))
 group by ydet_ycat_id,ydet_date,ybld_cutoffdate,d.WGBS_DATEVALUE
 union all
--1 
 select
   ydet_ycat_id,
   ydet_date,
          decode(sign(nvl(min(ybld_cutoffdate),d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,sum(decode(yres_resstatus,1,case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end,0))/greatest(1,sum(decode(yres_resstatus,1,case when yres_exparrtime<>yres_expdeptime then 1 else 0 end,0)))
                  ,decode(max(nvl(ybst_resstatus,0)),0,sum(decode(yres_resstatus,1,case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end,0))/greatest(1,sum(decode(yres_resstatus,1,case when yres_exparrtime<>yres_expdeptime then 1 else 0 end,0))),0)
                  )
          defnoofrooms,
          case when max(decode(yres_resstatus,1,yres_expdeptime-yres_exparrtime,0))=0 then 
          decode(sign(nvl(min(ybld_cutoffdate),d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,sum(decode(yres_resstatus,1,case when yres_exparrtime =yres_expdeptime then ydet_noofrooms else 0 end,0))/greatest(1,sum(decode(yres_resstatus,1,case when yres_exparrtime=yres_expdeptime then 1 else 0 end,0)))
                  ,decode(max(nvl(ybst_resstatus,0)),0,sum(decode(yres_resstatus,1,case when yres_exparrtime =yres_expdeptime then ydet_noofrooms else 0 end,0))/greatest(1,sum(decode(yres_resstatus,1,case when yres_exparrtime =yres_expdeptime then 1 else 0 end,0))),0)
                  ) else 0 end
           defnoofroomsDayUse,
          decode(sign(nvl(min(ybld_cutoffdate),d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,decode(sum(yres_resstatus),0,sum(decode(yres_resstatus,0,case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end,0))/greatest(1,sum(decode(yres_resstatus,0,case when yres_exparrtime<>yres_expdeptime then 1 else 0 end,0))),0)
                  ,0                              
                  )
          tentnoofrooms,
          case when max(yres_expdeptime-yres_exparrtime)=0
          then 
          decode(sign(nvl(min(ybld_cutoffdate),d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,decode(sum(yres_resstatus),0,sum(decode(yres_resstatus,0,case when yres_exparrtime =yres_expdeptime then ydet_noofrooms else 0 end,0))/greatest(1,sum(decode(yres_resstatus,0,case when yres_exparrtime =yres_expdeptime then 1 else 0 end,0))),0)
                  ,0
                  )
          else 0 end
          tentnoofroomsDayUse,
          decode(sign(nvl(min(ybld_cutoffdate),d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,0
                  ,decode(max(nvl(ybst_resstatus,0)),0,sum(decode(yres_resstatus,1,case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end,0))/greatest(1,sum(decode(yres_resstatus,1,case when yres_exparrtime<>yres_expdeptime then 1 else 0 end,0))),0)
                  )
          tentblockdefresroom,
          case when max(decode(yres_resstatus,1,yres_expdeptime-yres_exparrtime,0))=0 then 
          decode(sign(nvl(min(ybld_cutoffdate),d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,0
                  ,decode(max(nvl(ybst_resstatus,0)),0,sum(decode(yres_resstatus,1,case when yres_exparrtime =yres_expdeptime then ydet_noofrooms else 0 end,0))/greatest(1,sum(decode(yres_resstatus,1,case when yres_exparrtime =yres_expdeptime then 1 else 0 end,0))),0)
                  ) else 0 end
          tentBlockDefResroomDayUse
 from ydet,yres,ybld,yblh,ybst,fideliodate d
 where
     ydet_yres_id=yres_id(+)
 and ydet_ybld_id=ybld_id(+)
 and ybld_yblh_id=yblh_id(+)
 and yblh_ybst_id=ybst_id(+)
 and yres_resstatus<>3
 --and yres_exparrtime<>yres_expdeptime
 and nvl(ydet_sharenum,0)<>0
 and ((ydet_adultno=1 and not exists (select 1 from yrgl where yrgl_ydet_id=ydet_id and yrgl_number>0)) or
(ydet_adultno=0 and exists (select 1 from yrgl where yrgl_ydet_id=ydet_id and yrgl_number=1)))
 group by ydet_ycat_id,ydet_date,ydet_sharenum,d.WGBS_DATEVALUE
--1 
 ) group by ydet_ycat_id,ydet_date;

-- tt 19504 
-- Occupany for more than 1 pax

CREATE OR REPLACE VIEW V8_REP_YBLD_OCC_M1P
AS 
select
   ybld_ycat_id,
   ybld_date,
   sum(decode(ybst_resstatus,1,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms),0)) defnoofrooms,
   sum(decode(ybst_resstatus,1,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms),nvl(ybld_expectednoofrooms,0))) defnoofroomsbookingpos,
   sum(decode(ybst_resstatus,1,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms*nvl(ybld_noofadults,0)),0)) defnoofadults,
   sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms),0)) tentnoofrooms,
   sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms-nvl(ybld_expectednoofrooms,0)),0)) tentnoofroomsbookingpos,
   sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms*nvl(ybld_noofadults,0)),0)) tentnoofadults
from ybld,yblh,ybst,fideliodate d
where
    ybld_yblh_id=yblh_id
and YBLH_YBST_ID=ybst_id
and YBST_RESSTATUS<>3
and nvl(YBLD.YBLD_NOOFADULTS,0)<>1
group by ybld_ycat_id,ybld_date;

create or replace view v8_rep_ydet_occ_wb_m1p as
select ydet_ycat_id,ydet_date
      ,sum(defnoofrooms) defnoofrooms
      ,sum(defnoofroomsDayUse) defnoofroomsDayUse
      ,sum(tentnoofrooms) tentnoofrooms
      ,sum(tentnoofroomsDayUse) tentnoofroomsDayUse
      ,sum(tentblockdefresroom) tentblockdefresroom
      ,sum(tentblockdefresroomDayUse) tentblockdefresroomDayUse
from 
 (
  select
   ydet_ycat_id,
   ydet_date,
          decode(sign(nvl(ybld_cutoffdate,d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,sum(decode(yres_resstatus,1,case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end,0))
                  , sum(decode(ybst_resstatus,0,decode(yres_resstatus,1,case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end,0),0))
                   -sum(decode(ybst_resstatus,1,decode(yres_resstatus,1,case when yres_exparrtime= yres_expdeptime then ydet_noofrooms else 0 end,0),0))
          ) defnoofrooms,
          sum(decode(yres_resstatus,1,case when yres_exparrtime=yres_expdeptime then ydet_noofrooms else 0 end,0))
          defnoofroomsDayUse,
          decode(sign(nvl(ybld_cutoffdate,d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  , sum(decode(yres_resstatus,0,case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end,0))          
                  ,-sum(decode(ybst_resstatus,0,decode(yres_resstatus,0,case when yres_exparrtime= yres_expdeptime then ydet_noofrooms else 0 end,0),0)) --substract tentative res only because definitive day use (and non day use) will be substrated by tentblockdefresroom
          ) tentnoofrooms,
          decode(sign(nvl(ybld_cutoffdate,d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,sum(decode(yres_resstatus,0,case when yres_exparrtime=yres_expdeptime then ydet_noofrooms else 0 end,0))          
                  ,sum(decode(ybst_resstatus,0,decode(yres_resstatus,0,case when yres_exparrtime= yres_expdeptime then ydet_noofrooms else 0 end,0),0))
                  )
          tentnoofroomsDayUse,
          decode(sign(nvl(ybld_cutoffdate,d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,0
                  ,sum(decode(ybst_resstatus,0,decode(yres_resstatus,1,case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end,0),0))
                  )
          tentblockdefresroom,
          decode(sign(nvl(ybld_cutoffdate,d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,0
                  ,sum(decode(ybst_resstatus,0,decode(yres_resstatus,1,case when yres_exparrtime =yres_expdeptime then ydet_noofrooms else 0 end,0),0))
                  )
          tentblockdefresroomDayUse
 from ydet,yres,ybld,yblh,ybst,fideliodate d
 where
     ydet_yres_id=yres_id(+)
 and ydet_ybld_id=ybld_id(+)
 and ybld_yblh_id=yblh_id(+)
 and yblh_ybst_id=ybst_id(+)
 and yres_resstatus<>3
 --and yres_exparrtime<>yres_expdeptime
 and nvl(ydet_sharenum,0)=0
 and (ydet_adultno>1 or (ydet_adultno=0 and exists (select 1 from yrgl where yrgl_ydet_id=ydet_id and yrgl_number>0) ) or (ydet_adultno=1 and exists (select 1 from yrgl where yrgl_ydet_id=ydet_id and yrgl_number>0) ) )
 group by ydet_ycat_id,ydet_date,ybld_cutoffdate,d.WGBS_DATEVALUE
 union all
--1 
 select
   ydet_ycat_id,
   ydet_date,
          decode(sign(nvl(min(ybld_cutoffdate),d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,sum(decode(yres_resstatus,1,case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end,0))/greatest(1,sum(decode(yres_resstatus,1,case when yres_exparrtime<>yres_expdeptime then 1 else 0 end,0)))
                  ,decode(max(nvl(ybst_resstatus,0)),0,sum(decode(yres_resstatus,1,case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end,0))/greatest(1,sum(decode(yres_resstatus,1,case when yres_exparrtime<>yres_expdeptime then 1 else 0 end,0))),0)
                  )
          defnoofrooms,
          case when max(decode(yres_resstatus,1,yres_expdeptime-yres_exparrtime,0))=0 then 
          decode(sign(nvl(min(ybld_cutoffdate),d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,sum(decode(yres_resstatus,1,case when yres_exparrtime =yres_expdeptime then ydet_noofrooms else 0 end,0))/greatest(1,sum(decode(yres_resstatus,1,case when yres_exparrtime=yres_expdeptime then 1 else 0 end,0)))
                  ,decode(max(nvl(ybst_resstatus,0)),0,sum(decode(yres_resstatus,1,case when yres_exparrtime =yres_expdeptime then ydet_noofrooms else 0 end,0))/greatest(1,sum(decode(yres_resstatus,1,case when yres_exparrtime =yres_expdeptime then 1 else 0 end,0))),0)
                  ) else 0 end
           defnoofroomsDayUse,
          decode(sign(nvl(min(ybld_cutoffdate),d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,decode(sum(yres_resstatus),0,sum(decode(yres_resstatus,0,case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end,0))/greatest(1,sum(decode(yres_resstatus,0,case when yres_exparrtime<>yres_expdeptime then 1 else 0 end,0))),0)
                  ,0                              
                  )
          tentnoofrooms,
          case when max(yres_expdeptime-yres_exparrtime)=0
          then 
          decode(sign(nvl(min(ybld_cutoffdate),d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,decode(sum(yres_resstatus),0,sum(decode(yres_resstatus,0,case when yres_exparrtime =yres_expdeptime then ydet_noofrooms else 0 end,0))/greatest(1,sum(decode(yres_resstatus,0,case when yres_exparrtime =yres_expdeptime then 1 else 0 end,0))),0)
                  ,0
                  )
          else 0 end
          tentnoofroomsDayUse,
          decode(sign(nvl(min(ybld_cutoffdate),d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,0
                  ,decode(max(nvl(ybst_resstatus,0)),0,sum(decode(yres_resstatus,1,case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end,0))/greatest(1,sum(decode(yres_resstatus,1,case when yres_exparrtime<>yres_expdeptime then 1 else 0 end,0))),0)
                  )
          tentblockdefresroom,
          case when max(decode(yres_resstatus,1,yres_expdeptime-yres_exparrtime,0))=0 then 
          decode(sign(nvl(min(ybld_cutoffdate),d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,0
                  ,decode(max(nvl(ybst_resstatus,0)),0,sum(decode(yres_resstatus,1,case when yres_exparrtime =yres_expdeptime then ydet_noofrooms else 0 end,0))/greatest(1,sum(decode(yres_resstatus,1,case when yres_exparrtime =yres_expdeptime then 1 else 0 end,0))),0)
                  ) else 0 end
          tentBlockDefResroomDayUse
 from ydet,yres,ybld,yblh,ybst,fideliodate d
 where
     ydet_yres_id=yres_id(+)
 and ydet_ybld_id=ybld_id(+)
 and ybld_yblh_id=yblh_id(+)
 and yblh_ybst_id=ybst_id(+)
 and yres_resstatus<>3
 --and yres_exparrtime<>yres_expdeptime
 and nvl(ydet_sharenum,0)<>0
and (ydet_adultno>1 or (ydet_adultno=0 and exists (select 1 from yrgl where yrgl_ydet_id=ydet_id and yrgl_number>0) ) or (ydet_adultno=1 and exists (select 1 from yrgl where yrgl_ydet_id=ydet_id and yrgl_number>0) ) )
 group by ydet_ycat_id,ydet_date,ydet_sharenum,d.WGBS_DATEVALUE
--1 
 ) group by ydet_ycat_id,ydet_date;

-- tt 19504
-- VIP Guests

CREATE OR REPLACE VIEW V8_REP_STAT_VIPADULT  AS   
SELECT 
  SUM(decode(yres_resstatus,1,ydet_adultno,0)) VIP_ADULTS_DEF,
  SUM(decode(yres_resstatus,0,ydet_adultno,0)) VIP_ADULTS_TENT,
  WDAT.WDAT_DATE WDAT_DATE
FROM
  WDAT, 
  YDET,
  YRES,
  XPAL,ycat
WHERE 
  ydet_yres_id=yres_id  AND
  yres_resstatus<>3  AND
  ydet_date=WDAT.WDAT_DATE  AND
  XPAL.XPAL_XCMS_ID=YRES.YRES_XCMS_ID  AND
  xpal_xpac_id=11
  and ycat_id=ydet_ycat_id
  and ycat_notinavl=0
  group by WDAT.WDAT_DATE;

-- SP, all res Pickup from block

CREATE OR REPLACE VIEW V8_SYS_YBLD_PICKUP_ALL
AS 
select
   ybld_date,
   sum(def_pickupnoofrooms) defnoofrooms,
   sum(def_picknoofadults) defnoofadults,
   sum(tent_pickupnoofrooms) tentnoofrooms,
   sum( tent_picknoofadults) tentnoofadults
from 
(select 
 d.ybld_id,
 nvl((select sum(ydet_noofrooms) from ydet,yres where ydet_yres_id=yres_id and yres_resstatus=0 and ydet_ybld_id=d.ybld_id and  nvl(ydet_sharenum,0)=0),0)+nvl((select count( distinct ydet_sharenum) from ydet,yres where ydet_yres_id=yres_id and yres_resstatus=0 and ydet_ybld_id=d.ybld_id and  nvl(ydet_sharenum,0)<>0),0) tent_pickupnoofrooms, 
 nvl((select sum(ydet_adultno) from ydet,yres where ydet_yres_id=yres_id and yres_resstatus=0 and ydet_ybld_id=d.ybld_id),0) tent_picknoofadults,
 nvl((select sum(ydet_noofrooms) from ydet,yres where ydet_yres_id=yres_id and yres_resstatus=1 and ydet_ybld_id=d.ybld_id and  nvl(ydet_sharenum,0)=0),0)+nvl((select count( distinct ydet_sharenum) from ydet,yres where ydet_yres_id=yres_id and yres_resstatus=1 and ydet_ybld_id=d.ybld_id and  nvl(ydet_sharenum,0)<>0),0) def_pickupnoofrooms, 
 nvl((select sum(ydet_adultno) from ydet,yres where ydet_yres_id=yres_id and yres_resstatus=1 and ydet_ybld_id=d.ybld_id),0) def_picknoofadults,
 ybld_actualnoofrooms,
 ybld_cutoffdate,
 ybld_date,
 ybld_yblh_id,
 ybld_expectednoofrooms
  from ybld d) ybld,yblh,ybst,fideliodate d
where
    ybld_yblh_id=yblh_id
and YBLH_YBST_ID=ybst_id
and YBST_RESSTATUS<>3
group by ybld_date;

-- SP, all Rooms left in the block

CREATE OR REPLACE VIEW V8_SYS_YBLD_NOT_PICKUP_ALL
AS 
select
   ybld_date,
   sum(decode(ybst_resstatus,1,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms),0)) defnoofrooms,
   sum(decode(ybst_resstatus,1,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms),nvl(ybld_expectednoofrooms,0))) defnoofroomsbookingpos,
   sum(decode(ybst_resstatus,1,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms*nvl(ybld_noofadults,0)),0)) defnoofadults,
   sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms),0)) tentnoofrooms,
   sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms-nvl(ybld_expectednoofrooms,0)),0)) tentnoofroomsbookingpos,
   sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms*nvl(ybld_noofadults,0)),0)) tentnoofadults
from
(select 
 d.ybld_id,
 ybld_actualnoofrooms-(nvl((select sum(ydet_noofrooms) from ydet,yres where ydet_yres_id=yres_id and yres_resstatus<>3 and ydet_ybld_id=d.ybld_id and  nvl(ydet_sharenum,0)=0),0)+nvl((select count( distinct ydet_sharenum) from ydet,yres where ydet_yres_id=yres_id and yres_resstatus<>3 and ydet_ybld_id=d.ybld_id and  nvl(ydet_sharenum,0)<>0),0)) ybld_actualnoofrooms, 
 ybld_noofadults-(nvl((select avg(ydet_adultno) from ydet,yres where ydet_yres_id=yres_id and yres_resstatus<>3 and ydet_ybld_id=d.ybld_id),0)) ybld_noofadults,
 --ybld_actualnoofrooms,
 ybld_cutoffdate,
 ybld_date,
 ybld_yblh_id,
 ybld_expectednoofrooms
  from ybld d) ybld,yblh,ybst,fideliodate d
where
    ybld_yblh_id=yblh_id
and YBLH_YBST_ID=ybst_id
and YBST_RESSTATUS<>3
group by ybld_date;

--SG, views needed to show sum of future reservations on Company Profile HTML

CREATE OR REPLACE FORCE VIEW V8_SYS_YDET_OCC_WB_YCLN
AS 
select ydet_date, xcms_id, internalcat
      ,sum(defnoofrooms) defnoofrooms
      ,sum(defnoofroomsDayUse) defnoofroomsDayUse
      ,sum(tentnoofrooms) tentnoofrooms
      ,sum(tentnoofroomsDayUse) tentnoofroomsDayUse
      ,sum(tentblockdefresroom) tentblockdefresroom
      ,sum(tentblockdefresroomDayUse) tentblockdefresroomDayUse
from
 (
  select
   ydet_date,
   ycln_xcms_id xcms_id,
   YCLN.YCLN_XCCA_INTERNALCATEGORY internalcat,
          decode(sign(nvl(ybld_cutoffdate,d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,sum(decode(yres_resstatus,1,case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end,0))
                  , sum(decode(ybst_resstatus,0,decode(yres_resstatus,1,case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end,0),0))
                   -sum(decode(ybst_resstatus,1,decode(yres_resstatus,1,case when yres_exparrtime= yres_expdeptime then ydet_noofrooms else 0 end,0),0))
          ) defnoofrooms,
          sum(decode(yres_resstatus,1,case when yres_exparrtime=yres_expdeptime then ydet_noofrooms else 0 end,0))
          defnoofroomsDayUse,
          decode(sign(nvl(ybld_cutoffdate,d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  , sum(decode(yres_resstatus,0,case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end,0))
                  ,-sum(decode(ybst_resstatus,0,decode(yres_resstatus,0,case when yres_exparrtime= yres_expdeptime then ydet_noofrooms else 0 end,0),0)) --substract tentative res only because definitive day use (and non day use) will be substrated by tentblockdefresroom
          ) tentnoofrooms,
          decode(sign(nvl(ybld_cutoffdate,d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,sum(decode(yres_resstatus,0,case when yres_exparrtime=yres_expdeptime then ydet_noofrooms else 0 end,0))
                  ,sum(decode(ybst_resstatus,0,decode(yres_resstatus,0,case when yres_exparrtime= yres_expdeptime then ydet_noofrooms else 0 end,0),0))
                  )
          tentnoofroomsDayUse,
          decode(sign(nvl(ybld_cutoffdate,d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,0
                  ,sum(decode(ybst_resstatus,0,decode(yres_resstatus,1,case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end,0),0))
                  )
          tentblockdefresroom,
          decode(sign(nvl(ybld_cutoffdate,d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,0
                  ,sum(decode(ybst_resstatus,0,decode(yres_resstatus,1,case when yres_exparrtime =yres_expdeptime then ydet_noofrooms else 0 end,0),0))
                  )
          tentblockdefresroomDayUse
 from ydet,yres,ybld,yblh,ybst,fideliodate d,ycln
 where
     ydet_yres_id=yres_id(+)
 and ydet_ybld_id=ybld_id(+)
 and ybld_yblh_id=yblh_id(+)
 and yblh_ybst_id=ybst_id(+)
 and yres_resstatus<>3
 --and yres_exparrtime<>yres_expdeptime
 and nvl(ydet_sharenum,0)=0
 and YCLN.YCLN_YDET_ID=YDET.YDET_ID
 and YCLN.YCLN_XCCA_INTERNALCATEGORY in (1,2,3)
 group by ydet_date,ybld_cutoffdate,d.WGBS_DATEVALUE,ycln_xcms_id,YCLN_XCCA_INTERNALCATEGORY
 union all
--1
 select
   ydet_date,
   ycln_xcms_id xcms_id,
   YCLN.YCLN_XCCA_INTERNALCATEGORY internalcat,
          decode(sign(nvl(min(ybld_cutoffdate),d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,sum(decode(yres_resstatus,1,case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end,0))/greatest(1,sum(decode(yres_resstatus,1,case when yres_exparrtime<>yres_expdeptime then 1 else 0 end,0)))
                  ,decode(max(nvl(ybst_resstatus,0)),0,sum(decode(yres_resstatus,1,case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end,0))/greatest(1,sum(decode(yres_resstatus,1,case when yres_exparrtime<>yres_expdeptime then 1 else 0 end,0))),0)
                  )
          defnoofrooms,
          case when max(decode(yres_resstatus,1,yres_expdeptime-yres_exparrtime,0))=0 then
          decode(sign(nvl(min(ybld_cutoffdate),d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,sum(decode(yres_resstatus,1,case when yres_exparrtime =yres_expdeptime then ydet_noofrooms else 0 end,0))/greatest(1,sum(decode(yres_resstatus,1,case when yres_exparrtime=yres_expdeptime then 1 else 0 end,0)))
                  ,decode(max(nvl(ybst_resstatus,0)),0,sum(decode(yres_resstatus,1,case when yres_exparrtime =yres_expdeptime then ydet_noofrooms else 0 end,0))/greatest(1,sum(decode(yres_resstatus,1,case when yres_exparrtime =yres_expdeptime then 1 else 0 end,0))),0)
                  ) else 0 end
           defnoofroomsDayUse,
          decode(sign(nvl(min(ybld_cutoffdate),d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,decode(sum(yres_resstatus),0,sum(decode(yres_resstatus,0,case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end,0))/greatest(1,sum(decode(yres_resstatus,0,case when yres_exparrtime<>yres_expdeptime then 1 else 0 end,0))),0)
                  ,0
                  )
          tentnoofrooms,
          case when max(yres_expdeptime-yres_exparrtime)=0
          then
          decode(sign(nvl(min(ybld_cutoffdate),d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,decode(sum(yres_resstatus),0,sum(decode(yres_resstatus,0,case when yres_exparrtime =yres_expdeptime then ydet_noofrooms else 0 end,0))/greatest(1,sum(decode(yres_resstatus,0,case when yres_exparrtime =yres_expdeptime then 1 else 0 end,0))),0)
                  ,0
                  )
          else 0 end
          tentnoofroomsDayUse,
          decode(sign(nvl(min(ybld_cutoffdate),d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,0
                  ,decode(max(nvl(ybst_resstatus,0)),0,sum(decode(yres_resstatus,1,case when yres_exparrtime<>yres_expdeptime then ydet_noofrooms else 0 end,0))/greatest(1,sum(decode(yres_resstatus,1,case when yres_exparrtime<>yres_expdeptime then 1 else 0 end,0))),0)
                  )
          tentblockdefresroom,
          case when max(decode(yres_resstatus,1,yres_expdeptime-yres_exparrtime,0))=0 then
          decode(sign(nvl(min(ybld_cutoffdate),d.WGBS_DATEVALUE-1)-d.WGBS_DATEVALUE),-1
                  ,0
                  ,decode(max(nvl(ybst_resstatus,0)),0,sum(decode(yres_resstatus,1,case when yres_exparrtime =yres_expdeptime then ydet_noofrooms else 0 end,0))/greatest(1,sum(decode(yres_resstatus,1,case when yres_exparrtime =yres_expdeptime then 1 else 0 end,0))),0)
                  ) else 0 end
          tentBlockDefResroomDayUse
 from ydet,yres,ybld,yblh,ybst,fideliodate d,ycln
 where
     ydet_yres_id=yres_id(+)
 and ydet_ybld_id=ybld_id(+)
 and ybld_yblh_id=yblh_id(+)
 and yblh_ybst_id=ybst_id(+)
 and yres_resstatus<>3
 --and yres_exparrtime<>yres_expdeptime
 and nvl(ydet_sharenum,0)<>0
 and YCLN.YCLN_YDET_ID=YDET.YDET_ID
 and YCLN.YCLN_XCCA_INTERNALCATEGORY in (1,2,3)
 group by ydet_ycat_id,ydet_date,ydet_sharenum,d.WGBS_DATEVALUE,YCLN.YCLN_XCMS_ID,YCLN_XCCA_INTERNALCATEGORY
--1
 ) group by ydet_date,xcms_id,internalcat;

CREATE OR REPLACE FORCE VIEW V8_SYS_YBLD_OCC_YBCL
AS 
select
   ybld_date,
   ybcl.YBCL_XCMS_ID xcms_id,
   YBCL.YBCL_INTERNALCATEGORY internalcat,
   sum(decode(ybst_resstatus,1,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms),0)) defnoofrooms,
   sum(decode(ybst_resstatus,1,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms),nvl(ybld_expectednoofrooms,0))) defnoofroomsbookingpos,
   sum(decode(ybst_resstatus,1,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms*nvl(ybld_noofadults,0)),0)) defnoofadults,
   sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms),0)) tentnoofrooms,
   sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms-nvl(ybld_expectednoofrooms,0)),0)) tentnoofroomsbookingpos,
   sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms*nvl(ybld_noofadults,0)),0)) tentnoofadults
from ybld,yblh,ybst,fideliodate d,ybcl
where
    ybld_yblh_id=yblh_id
and YBLH_YBST_ID=ybst_id
and YBST_RESSTATUS<>3
and YBCL.YBCL_YBLH_ID=YBLH.YBLH_ID
AND YBCL.YBCL_INTERNALCATEGORY in (1,2,3)
group by ybld_date,ybcl_xcms_id,YBCL.YBCL_INTERNALCATEGORY;

CREATE OR REPLACE VIEW V8_SYS_YBLD_PICKUP_YBCL
AS 
select
   ybld_date,
   YBCL.YBCL_XCMS_ID xcms_id,
   YBCL.YBCL_INTERNALCATEGORY internalcat,
   sum(def_pickupnoofrooms) defnoofrooms,
   sum(def_picknoofadults) defnoofadults,
   sum(tent_pickupnoofrooms) tentnoofrooms,
   sum( tent_picknoofadults) tentnoofadults
from 
(select 
 d.ybld_id,
 nvl((select sum(ydet_noofrooms) from ydet,yres where ydet_yres_id=yres_id and yres_resstatus=0 and ydet_ybld_id=d.ybld_id and  nvl(ydet_sharenum,0)=0),0)+nvl((select count( distinct ydet_sharenum) from ydet,yres where ydet_yres_id=yres_id and yres_resstatus=0 and ydet_ybld_id=d.ybld_id and  nvl(ydet_sharenum,0)<>0),0) tent_pickupnoofrooms, 
 nvl((select sum(ydet_adultno) from ydet,yres where ydet_yres_id=yres_id and yres_resstatus=0 and ydet_ybld_id=d.ybld_id),0) tent_picknoofadults,
 nvl((select sum(ydet_noofrooms) from ydet,yres where ydet_yres_id=yres_id and yres_resstatus=1 and ydet_ybld_id=d.ybld_id and  nvl(ydet_sharenum,0)=0),0)+nvl((select count( distinct ydet_sharenum) from ydet,yres where ydet_yres_id=yres_id and yres_resstatus=1 and ydet_ybld_id=d.ybld_id and  nvl(ydet_sharenum,0)<>0),0) def_pickupnoofrooms, 
 nvl((select sum(ydet_adultno) from ydet,yres where ydet_yres_id=yres_id and yres_resstatus=1 and ydet_ybld_id=d.ybld_id),0) def_picknoofadults,
 ybld_actualnoofrooms,
 ybld_cutoffdate,
 ybld_date,
 ybld_yblh_id,
 ybld_expectednoofrooms
  from ybld d
  ) ybld,yblh,ybst,fideliodate d,ybcl
where
    ybld_yblh_id=yblh_id
and YBLH_YBST_ID=ybst_id
and YBST_RESSTATUS<>3
and YBCL.YBCL_YBLH_ID=YBLH.YBLH_ID
AND YBCL.YBCL_INTERNALCATEGORY in (1,2,3)
group by ybld_date, YBCL.YBCL_XCMS_ID, YBCL.YBCL_INTERNALCATEGORY;

CREATE OR REPLACE VIEW V8_SYS_YBLD_NOT_PICKUP_YBCL
AS 
select
   ybld_date,
   YBCL.YBCL_XCMS_ID xcms_id,
   YBCL.YBCL_INTERNALCATEGORY internalcat,
   sum(decode(ybst_resstatus,1,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms),0)) defnoofrooms,
   sum(decode(ybst_resstatus,1,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms),nvl(ybld_expectednoofrooms,0))) defnoofroomsbookingpos,
   sum(decode(ybst_resstatus,1,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms*nvl(ybld_noofadults,0)),0)) defnoofadults,
   sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms),0)) tentnoofrooms,
   sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms-nvl(ybld_expectednoofrooms,0)),0)) tentnoofroomsbookingpos,
   sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms*nvl(ybld_noofadults,0)),0)) tentnoofadults
from
(select 
 d.ybld_id,
 ybld_actualnoofrooms-(nvl((select sum(ydet_noofrooms) from ydet,yres where ydet_yres_id=yres_id and yres_resstatus<>3 and ydet_ybld_id=d.ybld_id and  nvl(ydet_sharenum,0)=0),0)+nvl((select count( distinct ydet_sharenum) from ydet,yres where ydet_yres_id=yres_id and yres_resstatus<>3 and ydet_ybld_id=d.ybld_id and  nvl(ydet_sharenum,0)<>0),0)) ybld_actualnoofrooms, 
 ybld_noofadults-(nvl((select avg(ydet_adultno) from ydet,yres where ydet_yres_id=yres_id and yres_resstatus<>3 and ydet_ybld_id=d.ybld_id),0)) ybld_noofadults,
 --ybld_actualnoofrooms,
 ybld_cutoffdate,
 ybld_date,
 ybld_yblh_id,
 ybld_expectednoofrooms
  from ybld d) ybld,yblh,ybst,fideliodate d,ybcl
where
    ybld_yblh_id=yblh_id
and YBLH_YBST_ID=ybst_id
and YBST_RESSTATUS<>3
and YBCL.YBCL_YBLH_ID=YBLH.YBLH_ID
AND YBCL.YBCL_INTERNALCATEGORY in (1,2,3)
group by ybld_date,YBCL.YBCL_XCMS_ID,YBCL.YBCL_INTERNALCATEGORY;

--CZ New views for occupancy forecast report handling for hotel segments

CREATE OR REPLACE VIEW V8_SYS_YBLD_DEF_OCC_CAD
AS 
select ycad_id, ybld_date,defnoofrooms noofrooms from v8_sys_ybld_occ_cad;

CREATE OR REPLACE VIEW V8_SYS_YBLD_TENT_OCC_CAD
AS 
select ycad_id, ybld_date,tentnoofrooms+defnoofrooms noofrooms from v8_sys_ybld_occ_cad;

CREATE OR REPLACE VIEW V8_SYS_YBLD_OCC_CAD
AS 
select
   YCAT_SUM_ROOMTYPE_YCAD_ID ycad_id,
   ybld_date,
   sum(decode(ybst_resstatus,1,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms),0)) defnoofrooms,
   sum(decode(ybst_resstatus,1,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms),nvl(ybld_expectednoofrooms,0))) defnoofroomsbookingpos,
   sum(decode(ybst_resstatus,1,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms*nvl(ybld_noofadults,0)),0)) defnoofadults,
   sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms),0)) tentnoofrooms,
   sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms-nvl(ybld_expectednoofrooms,0)),0)) tentnoofroomsbookingpos,
   sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms*nvl(ybld_noofadults,0)),0)) tentnoofadults
from ybld,yblh,ybst,fideliodate d,ycat
where
    ybld_yblh_id=yblh_id
and YBLH_YBST_ID=ybst_id
and YBST_RESSTATUS<>3
and ybld_ycat_id=ycat_id
group by YCAT_SUM_ROOMTYPE_YCAD_ID,ybld_date;

CREATE OR REPLACE VIEW V8_SYS_YBLD_NOT_PICKUP_CAD
AS 
select
   ybld_date,
   YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID YCAD_ID,
   sum(decode(ybst_resstatus,1,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms),0)) defnoofrooms,
   sum(decode(ybst_resstatus,1,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms),nvl(ybld_expectednoofrooms,0))) defnoofroomsbookingpos,
   sum(decode(ybst_resstatus,1,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms*nvl(ybld_noofadults,0)),0)) defnoofadults,
   sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms),0)) tentnoofrooms,
   sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms-nvl(ybld_expectednoofrooms,0)),0)) tentnoofroomsbookingpos,
   sum(decode(ybst_resstatus,0,decode(sign(ybld_cutoffdate-d.WGBS_DATEVALUE),-1,0,ybld_actualnoofrooms*nvl(ybld_noofadults,0)),0)) tentnoofadults
from
(select 
 d.ybld_id,
 D.ybld_ycat_id,
 ybld_actualnoofrooms-(nvl((select sum(ydet_noofrooms) from ydet,yres where ydet_yres_id=yres_id and yres_resstatus<>3 and ydet_ybld_id=d.ybld_id and  nvl(ydet_sharenum,0)=0),0)+nvl((select count( distinct ydet_sharenum) from ydet,yres where ydet_yres_id=yres_id and yres_resstatus<>3 and ydet_ybld_id=d.ybld_id and  nvl(ydet_sharenum,0)<>0),0)) ybld_actualnoofrooms, 
 ybld_noofadults-(nvl((select avg(ydet_adultno) from ydet,yres where ydet_yres_id=yres_id and yres_resstatus<>3 and ydet_ybld_id=d.ybld_id),0)) ybld_noofadults,
 --ybld_actualnoofrooms,
 ybld_cutoffdate,
 ybld_date,
 ybld_yblh_id,
 ybld_expectednoofrooms
  from ybld d) ybld,yblh,ybst,fideliodate d, ycat
where
    ybld_yblh_id=yblh_id
and YBLH_YBST_ID=ybst_id
and YBST_RESSTATUS<>3
and YBLD_YCAT_ID = ycat_id
group by ybld_date, YCAT_SUM_ROOMTYPE_YCAD_ID;

CREATE OR REPLACE VIEW V8_SYS_YBLD_PICKUP_CAD
AS 
select
   ybld_date,
   YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID YCAD_ID,
   sum(def_pickupnoofrooms) defnoofrooms,
   sum(def_picknoofadults) defnoofadults,
   sum(tent_pickupnoofrooms) tentnoofrooms,
   sum( tent_picknoofadults) tentnoofadults
from 
(select 
 d.ybld_id,
 d.ybld_ycat_id,
 nvl((select sum(ydet_noofrooms) from ydet,yres where ydet_yres_id=yres_id and yres_resstatus=0 and ydet_ybld_id=d.ybld_id and  nvl(ydet_sharenum,0)=0),0)+nvl((select count( distinct ydet_sharenum) from ydet,yres where ydet_yres_id=yres_id and yres_resstatus=0 and ydet_ybld_id=d.ybld_id and  nvl(ydet_sharenum,0)<>0),0) tent_pickupnoofrooms, 
 nvl((select sum(ydet_adultno) from ydet,yres where ydet_yres_id=yres_id and yres_resstatus=0 and ydet_ybld_id=d.ybld_id),0) tent_picknoofadults,
 nvl((select sum(ydet_noofrooms) from ydet,yres where ydet_yres_id=yres_id and yres_resstatus=1 and ydet_ybld_id=d.ybld_id and  nvl(ydet_sharenum,0)=0),0)+nvl((select count( distinct ydet_sharenum) from ydet,yres where ydet_yres_id=yres_id and yres_resstatus=1 and ydet_ybld_id=d.ybld_id and  nvl(ydet_sharenum,0)<>0),0) def_pickupnoofrooms, 
 nvl((select sum(ydet_adultno) from ydet,yres where ydet_yres_id=yres_id and yres_resstatus=1 and ydet_ybld_id=d.ybld_id),0) def_picknoofadults,
 ybld_actualnoofrooms,
 ybld_cutoffdate,
 ybld_date,
 ybld_yblh_id,
 ybld_expectednoofrooms
  from ybld d) ybld,yblh,ybst,fideliodate d, ycat
where
    ybld_yblh_id=yblh_id
and YBLH_YBST_ID=ybst_id
and YBST_RESSTATUS<>3
and ybld_ycat_id = ycat.ycat_id
group by ybld_date, YCAT_SUM_ROOMTYPE_YCAD_ID;

CREATE OR REPLACE VIEW V8_REP_STAT_VIPADULT_CAD
AS 
SELECT 
  SUM(decode(yres_resstatus,1,ydet_adultno,0)) VIP_ADULTS_DEF,
  SUM(decode(yres_resstatus,0,ydet_adultno,0)) VIP_ADULTS_TENT,
  YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID YCAD_ID,
  WDAT.WDAT_DATE WDAT_DATE
FROM
  WDAT, 
  YDET,
  YRES,
  XPAL,ycat
WHERE 
  ydet_yres_id=yres_id  AND
  yres_resstatus<>3  AND
  ydet_date=WDAT.WDAT_DATE  AND
  XPAL.XPAL_XCMS_ID=YRES.YRES_XCMS_ID  AND
  xpal_xpac_id=11
  and ycat_id=ydet_ycat_id
  and ycat_notinavl=0
  group by YCAT.YCAT_SUM_ROOMTYPE_YCAD_ID,  WDAT.WDAT_DATE;

/*---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

CREATE OR REPLACE FORCE VIEW V8_ED_FOLIO_REV_ORI_ARR_PS
AS 
SELECT
  ZPOS_POSTDATE_S DAT,
  to_number(to_char(ZPOS_POSTDATE_S,'J')) DAT_J,
  (SELECT ZPOS.ZPOS_CDT FROM ZPOS WHERE ZPOS.ZPOS_ID=ZPOS_ID_S) CDT,
  ZPOS_ID_S ZPOS_ID,
  ZPOS_INTERNALGROUP_ID_S ZPOS_INTERNALGROUP_ID,
  -- Quantity makes no sense in this Style decode((select zpos.ZPOS_PAIDOUT from zpos where zpos.ZPOS_ID=ZPOS_ID_S),1,-1,1)*(select zpos.ZPOS_QUANTITY from zpos where zpos.ZPOS_ID=ZPOS_ID_S) QTY,
  GROSS_S GROSS,
  PAY_S PAY,
  REV_S REV,
  IS_RES,
  IS_FA,
  ORI_RES_FA_ID,
  ARRIVAL,
  DEPARTURE,
  ZFAC_OR_YRES,
  (SELECT ZDCO.ZDCO_NUMERICDESC FROM ZDCO,ZPOS WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID AND ZPOS.ZPOS_ID=ZPOS_ID_S) ZDCO_NUMERICDESC,
  (SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) SPAY,
  (SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)=2 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) SPAY_CL,
  (SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)<>2 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) SPAY_NOCL,
  (SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1, -ZPOS.zpos_unitprice*ZPOS.zpos_quantity, NULL),DECODE(ZPOS.ZPOS_CDT,5,0, ZPOS.zpos_sumupamount))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) SREV,
  (SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1, -ZPOS.zpos_unitprice*ZPOS.zpos_quantity, NULL),DECODE(ZPOS.ZPOS_CDT,5,0, ZPOS.zpos_sumupamount))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S)-(SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) OPENA,
  --(SELECT V8_REP_NAME.NAME6 FROM YRES,V8_REP_NAME WHERE YRES.YRES_ID=ORI_RES_FA_ID AND YRES.YRES_XCMS_ID=V8_REP_NAME.XCMS_ID) RES_GUEST_NAME,
  --(SELECT V8_REP_NAME.NAME6 FROM ZFAC,V8_REP_NAME WHERE ZFAC.ZFAC_ID=ORI_RES_FA_ID AND ZFAC.ZFAC_XCMS_ID=V8_REP_NAME.XCMS_ID) FA_GUEST_NAME,
  (DECODE(IS_FA,0,(SELECT V8_REP_NAME.NAME6 FROM YRES,V8_REP_NAME WHERE YRES.YRES_ID=ORI_RES_FA_ID AND YRES.YRES_XCMS_ID=V8_REP_NAME.XCMS_ID),(SELECT V8_REP_NAME.NAME6 FROM ZFAC,V8_REP_NAME WHERE ZFAC.ZFAC_ID=ORI_RES_FA_ID AND ZFAC.ZFAC_XCMS_ID=V8_REP_NAME.XCMS_ID))) REAL_ORIGINATOR_NAME,
  (SELECT ZPCF.ZPCF_CUSTOMORIGINATORNAME FROM ZPCF WHERE ZPCF.ZPCF_ZPOS_ID=ZPOS_ID_S) ORIGINATOR_NAME,
  (DECODE(IS_FA,0,(NVL((SELECT YRMS.YRMS_SHORTDESC FROM YRMS,YDET WHERE YRMS.YRMS_ID=YDET.YDET_YRMS_ID AND YDET.YDET_DATE=ZPOS_POSTDATE_S AND YDET.YDET_YRES_ID=ORI_RES_FA_ID),(SELECT YRMS.YRMS_SHORTDESC FROM YRMS,YDET WHERE YRMS.YRMS_ID=YDET.YDET_YRMS_ID AND YDET.YDET_DATE+1=ZPOS_POSTDATE_S AND YDET.YDET_YRES_ID=ORI_RES_FA_ID))),(NVL((SELECT ZFAN.ZFAN_LONGDESC FROM ZFAN,ZFAC WHERE ZFAN.ZFAN_ID=ZFAC.ZFAC_ZFAN_ID AND ZFAC.ZFAC_ID=ORI_RES_FA_ID),(SELECT ZFAC.ZFAC_AUTONUMBER FROM ZFAC WHERE ZFAC.ZFAC_ID=ORI_RES_FA_ID))))) ORI_RES_FA_DESC,
  TO_CHAR((CASE WHEN (SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1, -ZPOS.zpos_unitprice*ZPOS.zpos_quantity, NULL),DECODE(ZPOS.ZPOS_CDT,5,0, ZPOS.zpos_sumupamount))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S)-(SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S)=0 AND NVL((SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)=2  
  AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S),0)<>0 THEN NVL((SELECT SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND (SELECT ZDCO.ZDCO_PAYM_TYPE FROM ZDCO WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID)=2  AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S),0) ELSE (SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1, -ZPOS.zpos_unitprice*ZPOS.zpos_quantity, NULL),DECODE(ZPOS.ZPOS_CDT,5,0, ZPOS.zpos_sumupamount))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0) AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S)-(SELECT NVL(SUM(DECODE(ZPOS.zpos_cdt,5,DECODE(ZPOS.zpos_paidout,1,TO_NUMBER(NULL),(ZPOS.zpos_unitprice*ZPOS.zpos_quantity)),TO_NUMBER(NULL))),0) FROM ZPOS WHERE (zpos_zall_id IS NULL OR zpos_zall_id=0)
  AND ZPOS.zpos_id=ZPOS.zpos_internalsplit_zpos_id AND NVL(ZPOS.ZPOS_HIDDEN,0)=0 AND NVL(ZPOS.ZPOS_NOREPRINT,0)<>1 AND ZPOS.ZPOS_ZWIN_ID = ZWIN_ID_S) END)*100,'999999990,09') FSPAY_CL,
  (DECODE(INDICATE_ZPOS_ID,0,(Wtrl_Translate('ZARR',(DECODE(INDICATE_ZPOS_ID,0,(SELECT ZARR.ZARR_ID  FROM ZARR WHERE ZARR.ZARR_ID=ZARR_OR_ZPOS_S),0)),'ZARR_LONGDESC',NVL((DECODE((SELECT XCCA.XCCA_XCMSTYPE FROM XCCA,XCMS WHERE XCCA.XCCA_ID=XCMS.XCMS_XCCA_ID AND XCMS.XCMS_ID=(SELECT ZWIN.ZWIN_INVOICE_TO_XCMS_ID FROM ZWIN WHERE ZWIN.ZWIN_ID=ZWIN_ID_S)),2,(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=(SELECT ZWIN.ZWIN_INVOICE_TO_XCMS_ID FROM ZWIN WHERE ZWIN.ZWIN_ID=ZWIN_ID_S)),(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=(SELECT ZWIN.ZWIN_INVOICE_TO_CONTACT_ID FROM ZWIN WHERE ZWIN.ZWIN_ID=ZWIN_ID_S)))),(SELECT WLAN.WLAN_ID FROM WLAN WHERE WLAN.WLAN_DEFAULT=1)))),
  (CASE WHEN (((SELECT ZPOS.ZPOS_DESCRIPT FROM ZPOS WHERE ZPOS.ZPOS_ID=(DECODE(INDICATE_ZPOS_ID,0,0,(SELECT ZPOS.ZPOS_ID FROM ZPOS WHERE ZPOS.ZPOS_ID=ZARR_OR_ZPOS_S)))))=(SELECT ZDCO.ZDCO_LONGDESC FROM ZDCO,ZPOS WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID AND ZPOS.ZPOS_ID=(DECODE(INDICATE_ZPOS_ID,0,0,(SELECT ZPOS.ZPOS_ID FROM ZPOS WHERE ZPOS.ZPOS_ID=ZARR_OR_ZPOS_S)))))
  OR (SELECT ZPOS.ZPOS_DESCRIPT FROM ZPOS WHERE ZPOS.ZPOS_ID=(DECODE(INDICATE_ZPOS_ID,0,0,(SELECT ZPOS.ZPOS_ID FROM ZPOS WHERE ZPOS.ZPOS_ID=ZARR_OR_ZPOS_S))))=(SELECT YRCH.YRCH_FOLIOTEXT FROM YRCH,YDET WHERE YRCH.YRCH_ID=YDET.YDET_YRCH_ID AND YDET.ydet_date=TRUNC(ZPOS_POSTDATE_S) AND YDET.YDET_YRES_ID=(SELECT ZPOS.ZPOS_ORIGINATED_YRES_ID FROM ZPOS WHERE ZPOS.ZPOS_ID=(DECODE(INDICATE_ZPOS_ID,0,0,(SELECT ZPOS.ZPOS_ID FROM ZPOS WHERE ZPOS.ZPOS_ID=ZARR_OR_ZPOS_S))) AND ROWNUM=1))
  THEN
  (DECODE(
  (SELECT ZPOS.ZPOS_DESCRIPT FROM ZPOS WHERE ZPOS.ZPOS_ID=(DECODE(INDICATE_ZPOS_ID,0,0,(SELECT ZPOS.ZPOS_ID FROM ZPOS WHERE ZPOS.ZPOS_ID=ZARR_OR_ZPOS_S)))),
  (SELECT YRCH.YRCH_FOLIOTEXT FROM YRCH,YDET WHERE YRCH.YRCH_ID=YDET.YDET_YRCH_ID AND YDET.ydet_date=TRUNC(ZPOS_POSTDATE_S) AND YDET.YDET_YRES_ID=(SELECT ZPOS.ZPOS_ORIGINATED_YRES_ID FROM ZPOS WHERE ZPOS.ZPOS_ID=(DECODE(INDICATE_ZPOS_ID,0,0,(SELECT ZPOS.ZPOS_ID FROM ZPOS WHERE ZPOS.ZPOS_ID=ZARR_OR_ZPOS_S))) AND ROWNUM=1)),
  DECODE(NVL((SELECT YRCH.YRCH_ID FROM YRCH,YDET WHERE YRCH.YRCH_ID=YDET.YDET_YRCH_ID AND YDET.ydet_date=TRUNC(ZPOS_POSTDATE_S) AND YDET.YDET_YRES_ID=(SELECT ZPOS.ZPOS_ORIGINATED_YRES_ID FROM ZPOS WHERE ZPOS.ZPOS_ID=(DECODE(INDICATE_ZPOS_ID,0,0,(SELECT ZPOS.ZPOS_ID FROM ZPOS WHERE ZPOS.ZPOS_ID=ZARR_OR_ZPOS_S))) AND ROWNUM=1)),0),0,'',Wtrl_Translate('YRCH',(SELECT YRCH.YRCH_ID FROM YRCH,YDET WHERE YRCH.YRCH_ID=YDET.YDET_YRCH_ID AND YDET.ydet_date=TRUNC(ZPOS_POSTDATE_S) AND YDET.YDET_YRES_ID=(SELECT ZPOS.ZPOS_ORIGINATED_YRES_ID FROM ZPOS WHERE ZPOS.ZPOS_ID=(DECODE(INDICATE_ZPOS_ID,0,0,(SELECT ZPOS.ZPOS_ID FROM ZPOS WHERE ZPOS.ZPOS_ID=ZARR_OR_ZPOS_S))) 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=(SELECT ZWIN.ZWIN_INVOICE_TO_XCMS_ID FROM ZWIN WHERE ZWIN.ZWIN_ID=ZWIN_ID_S)),2,(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=(SELECT ZWIN.ZWIN_INVOICE_TO_XCMS_ID FROM ZWIN WHERE ZWIN.ZWIN_ID=ZWIN_ID_S)),(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=(SELECT ZWIN.ZWIN_INVOICE_TO_CONTACT_ID FROM ZWIN WHERE ZWIN.ZWIN_ID=ZWIN_ID_S)))),(SELECT WLAN.WLAN_ID FROM WLAN WHERE WLAN.WLAN_DEFAULT=1)))),
  (DECODE(NVL((SELECT ZDCO.ZDCO_FOLIOTEXT FROM ZDCO WHERE ZDCO.ZDCO_ID=(SELECT ZPOS.ZPOS_ZDCO_ID FROM ZPOS WHERE ZPOS.ZPOS_ID=(DECODE(INDICATE_ZPOS_ID,0,0,(SELECT ZPOS.ZPOS_ID FROM ZPOS WHERE ZPOS.ZPOS_ID=ZARR_OR_ZPOS_S))))),' '),' ',
  DECODE(NVL((SELECT ZDCO.ZDCO_ID FROM ZDCO,ZPOS WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID AND ZPOS.ZPOS_ID=(DECODE(INDICATE_ZPOS_ID,0,0,(SELECT ZPOS.ZPOS_ID FROM ZPOS WHERE ZPOS.ZPOS_ID=ZARR_OR_ZPOS_S)))),0),0,' ',(Wtrl_Translate('ZDCO',(SELECT ZDCO.ZDCO_ID FROM ZDCO,ZPOS WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID AND ZPOS.ZPOS_ID=(DECODE(INDICATE_ZPOS_ID,0,0,(SELECT ZPOS.ZPOS_ID FROM ZPOS WHERE ZPOS.ZPOS_ID=ZARR_OR_ZPOS_S)))),'ZDCO_LONGDESC',NVL((DECODE((SELECT XCCA.XCCA_XCMSTYPE FROM XCCA,XCMS WHERE XCCA.XCCA_ID=XCMS.XCMS_XCCA_ID AND XCMS.XCMS_ID=(SELECT ZWIN.ZWIN_INVOICE_TO_XCMS_ID FROM ZWIN WHERE ZWIN.ZWIN_ID=ZWIN_ID_S)),2,(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=(SELECT ZWIN.ZWIN_INVOICE_TO_XCMS_ID FROM ZWIN WHERE ZWIN.ZWIN_ID=ZWIN_ID_S)),(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=(SELECT ZWIN.ZWIN_INVOICE_TO_CONTACT_ID FROM ZWIN WHERE ZWIN.ZWIN_ID=ZWIN_ID_S)))),(SELECT WLAN.WLAN_ID FROM WLAN WHERE WLAN.WLAN_DEFAULT=1))))),
  DECODE(NVL((SELECT ZDCO.ZDCO_ID FROM ZDCO,ZPOS WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID AND ZPOS.ZPOS_ID=(DECODE(INDICATE_ZPOS_ID,0,0,(SELECT ZPOS.ZPOS_ID FROM ZPOS WHERE ZPOS.ZPOS_ID=ZARR_OR_ZPOS_S)))),0),0,' ',(Wtrl_Translate('ZDCO',(SELECT ZDCO.ZDCO_ID FROM ZDCO,ZPOS WHERE ZDCO.ZDCO_ID=ZPOS.ZPOS_ZDCO_ID AND ZPOS.ZPOS_ID=(DECODE(INDICATE_ZPOS_ID,0,0,(SELECT ZPOS.ZPOS_ID FROM ZPOS WHERE ZPOS.ZPOS_ID=ZARR_OR_ZPOS_S)))),'ZDCO_FOLIOTEXT',NVL((DECODE((SELECT XCCA.XCCA_XCMSTYPE FROM XCCA,XCMS WHERE XCCA.XCCA_ID=XCMS.XCMS_XCCA_ID AND XCMS.XCMS_ID=(SELECT ZWIN.ZWIN_INVOICE_TO_XCMS_ID FROM ZWIN WHERE ZWIN.ZWIN_ID=ZWIN_ID_S)),2,(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=(SELECT ZWIN.ZWIN_INVOICE_TO_XCMS_ID FROM ZWIN WHERE ZWIN.ZWIN_ID=ZWIN_ID_S)),(SELECT XCMS.XCMS_WLAN_ID FROM XCMS WHERE XCMS.XCMS_ID=(SELECT ZWIN.ZWIN_INVOICE_TO_CONTACT_ID FROM ZWIN WHERE ZWIN.ZWIN_ID=ZWIN_ID_S)))),(SELECT WLAN.WLAN_ID FROM WLAN WHERE WLAN.WLAN_DEFAULT=1)))))
  ))))
  ELSE (SELECT ZPOS.ZPOS_DESCRIPT FROM ZPOS WHERE ZPOS.ZPOS_ID=(DECODE(INDICATE_ZPOS_ID,0,0,(SELECT ZPOS.ZPOS_ID FROM ZPOS WHERE ZPOS.ZPOS_ID=ZARR_OR_ZPOS_S)))) END)
  )) DDESC,
  ZARR_OR_ZPOS_S ZARR_OR_ZPOS, 
  INDICATE_ZPOS_ID, 
  decode(INDICATE_ZPOS_ID,1,(case when (select count( distinct Z2.ZPOS_ZTCD_ID) from zpos Z2 where Z2.ZPOS_INTERNALSPLIT_ZPOS_ID=ZARR_OR_ZPOS_S and Z2.ZPOS_ZTCD_ID is not null)>1 then ' ' else (select ZTXC.ZTXC_LONGDESC from ztxc, ztcd where ZTXC.ZTXC_ID=ZTCD.ZTCD_ZTXC_ID and ZTCD.ZTCD_ID=(select distinct Z2.ZPOS_ZTCD_ID from zpos Z2 where Z2.ZPOS_INTERNALSPLIT_ZPOS_ID=ZARR_OR_ZPOS_S and Z2.ZPOS_ZTCD_ID is not null)) end ),(case when (select count( distinct Z3.ZPOS_ZTCD_ID) from zpos Z3 where Z3.ZPOS_ZARR_ID=ZARR_OR_ZPOS_S and Z3.ZPOS_POSTDATE=ZPOS_POSTDATE_S and Z3.ZPOS_ZWIN_ID=ZWIN_ID_S)>1 then ' ' else (select ZTXC.ZTXC_LONGDESC from ztxc, ztcd where ZTXC.ZTXC_ID=ZTCD.ZTCD_ZTXC_ID and ZTCD.ZTCD_ID=(select distinct Z4.ZPOS_ZTCD_ID from zpos Z3, zpos Z4 where Z3.ZPOS_ZARR_ID=ZARR_OR_ZPOS_S  and Z3.ZPOS_TAXLINK_ID=Z4.ZPOS_TAXLINK_ID and Z3.ZPOS_POSTDATE=ZPOS_POSTDATE_S and Z3.ZPOS_ZWIN_ID=ZWIN_ID_S and Z4.ZPOS_ZTCD_ID is not null and rownum=1)) end)) TAXCODE_DESC,
  decode(INDICATE_ZPOS_ID,1,(case when (select count( distinct Z2.ZPOS_ZTCD_ID) from zpos Z2 where Z2.ZPOS_INTERNALSPLIT_ZPOS_ID=ZARR_OR_ZPOS_S and Z2.ZPOS_ZTCD_ID is not null)>1 then 1 else 0 end ),(case when (select count( distinct Z3.ZPOS_ZTCD_ID) from zpos Z3 where Z3.ZPOS_ZARR_ID=ZARR_OR_ZPOS_S and Z3.ZPOS_ZWIN_ID=ZWIN_ID_S)>1 then 1 else 0 end)) MULTI_TAXCODE_EXISTS, 
  ZWIN_ID_S ZWIN_ID
FROM
(
SELECT
--  distinct
  MIN(ntd.ZPOS_POSTDATE) ZPOS_POSTDATE_S,
  MIN(ntd.ZPOS_ID) ZPOS_ID_S,
  MIN(NTD.ZPOS_INTERNALGROUP_ID) ZPOS_INTERNALGROUP_ID_S,
  1 IS_RES,
  0 IS_FA,
  ntd.ZPOS_ORIGINATED_YRES_ID ORI_RES_FA_ID,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1,(-ntd.zpos_unitprice*ntd.zpos_quantity),-(ntd.zpos_unitprice*ntd.zpos_quantity)),DECODE(ntd.ZPOS_CDT,5,0,ntd.zpos_sumupamount))) GROSS_S,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1,NULL,(ntd.zpos_unitprice*ntd.zpos_quantity)), NULL)) PAY_S,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1, -ntd.zpos_unitprice*ntd.zpos_quantity, NULL),DECODE(ntd.ZPOS_CDT,5,0, ntd.zpos_sumupamount))) REV_S,
  ZWIN.ZWIN_ID ZWIN_ID_S,
  (SELECT YRES.YRES_EXPARRTIME FROM YRES WHERE YRES.YRES_ID=NTD.ZPOS_ORIGINATED_YRES_ID) ARRIVAL,
  (SELECT YRES.YRES_EXPDEPTIME FROM YRES WHERE YRES.YRES_ID=NTD.ZPOS_ORIGINATED_YRES_ID) DEPARTURE,
  DECODE(NVL(ntd.ZPOS_ZARR_ID,0),0,1,0) INDICATE_ZPOS_ID,
  DECODE(NVL(ntd.ZPOS_ZARR_ID,0),0,ntd.ZPOS_ID,ntd.ZPOS_ZARR_ID) ZARR_OR_ZPOS_S,
  DECODE(NVL(ntd.ZPOS_ORIGINATED_YRES_ID,0),0,1,0) ZFAC_OR_YRES
FROM
  ZPOS ntd,
  ZWIN ZWIN,
  ZDCO
WHERE
  (zpos_zall_id IS NULL OR zpos_zall_id=0) AND
  ntd.zpos_id=ntd.zpos_internalsplit_zpos_id AND
  zdco_id=ntd.zpos_zdco_id AND
  ntd.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID(+) AND
  NVL(ntd.ZPOS_HIDDEN,0)=0
  AND NVL(ntd.ZPOS_NOREPRINT,0)<>1
  AND nvl(NTD.ZPOS_ORIGINATED_ZFAC_ID,0)=0
  --AND ZWIN.ZWIN_ID=:WINID
GROUP BY ZWIN.ZWIN_ID, ntd.ZPOS_ORIGINATED_YRES_ID, DECODE(NVL(ntd.ZPOS_ZARR_ID,0),0,ntd.ZPOS_ID,ntd.ZPOS_ZARR_ID), DECODE(NVL(ntd.ZPOS_ORIGINATED_YRES_ID,0),0,1,0), DECODE(NVL(ntd.ZPOS_ZARR_ID,0),0,1,0)--,decode(nvl(ntd.ZPOS_ZARR_ID,0),0,ntd.ZPOS_ID,ntd.ZPOS_ID)-- ntd.ZPOS_ID--,zdco.ZDCO_ID--, ntd.ZPOS_ID, ntd.ZPOS_ZARR_ID
UNION ALL
SELECT
--  distinct
  MIN(ntd.ZPOS_POSTDATE) ZPOS_POSTDATE_S,
  MIN(ntd.ZPOS_ID) ZPOS_ID_S,
 MIN(NTD.ZPOS_INTERNALGROUP_ID) ZPOS_INTERNALGROUP_ID_S,
  0 IS_RES,
  1 IS_FA,
  ntd.ZPOS_ORIGINATED_ZFAC_ID ORI_RES_FA_ID,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1,(-ntd.zpos_unitprice*ntd.zpos_quantity),-(ntd.zpos_unitprice*ntd.zpos_quantity)),DECODE(ntd.ZPOS_CDT,5,0,ntd.zpos_sumupamount))) GROSS_S,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1,NULL,(ntd.zpos_unitprice*ntd.zpos_quantity)), NULL)) PAY_S,
  SUM(DECODE(ntd.zpos_cdt,5,DECODE(ntd.zpos_paidout,1, -ntd.zpos_unitprice*ntd.zpos_quantity, NULL),DECODE(ntd.ZPOS_CDT,5,0, ntd.zpos_sumupamount))) REV_S,
  ZWIN.ZWIN_ID ZWIN_ID_S,
  (SELECT ZFAC.ZFAC_EXPVALIDFROM FROM ZFAC WHERE ZFAC.ZFAC_ID=ntd.ZPOS_ORIGINATED_ZFAC_ID) ARRIVAL,
  (SELECT ZFAC.ZFAC_EXPVALIDUNTIL FROM ZFAC WHERE ZFAC.ZFAC_ID=ntd.ZPOS_ORIGINATED_ZFAC_ID) DEPARTURE,
  DECODE(NVL(ntd.ZPOS_ZARR_ID,0),0,1,0) INDICATE_ZPOS_ID,
  DECODE(NVL(ntd.ZPOS_ZARR_ID,0),0,ntd.ZPOS_ID,ntd.ZPOS_ZARR_ID) ZARR_OR_ZPOS_S,
  DECODE(NVL(ntd.ZPOS_ORIGINATED_YRES_ID,0),0,1,0) ZFAC_OR_YRES
FROM
  ZPOS ntd,
  ZWIN ZWIN,
  ZDCO
WHERE
  (zpos_zall_id IS NULL OR zpos_zall_id=0) AND
  ntd.zpos_id=ntd.zpos_internalsplit_zpos_id AND
  zdco_id=ntd.zpos_zdco_id AND
  ntd.ZPOS_ZWIN_ID = ZWIN.ZWIN_ID(+) AND
  NVL(ntd.ZPOS_HIDDEN,0)=0
  AND NVL(ntd.ZPOS_NOREPRINT,0)<>1
  AND nvl(NTD.ZPOS_ORIGINATED_ZFAC_ID,0)<>0
  --AND ZWIN.ZWIN_ID=:WINID
GROUP BY ZWIN.ZWIN_ID, ntd.ZPOS_ORIGINATED_ZFAC_ID, DECODE(NVL(ntd.ZPOS_ZARR_ID,0),0,ntd.ZPOS_ID,ntd.ZPOS_ZARR_ID),DECODE(NVL(ntd.ZPOS_ORIGINATED_YRES_ID,0),0,1,0), DECODE(NVL(ntd.ZPOS_ZARR_ID,0),0,1,0)--,decode(nvl(ntd.ZPOS_ZARR_ID,0),0,ntd.ZPOS_ID,ntd.ZPOS_ID)-- ntd.ZPOS_ID--,zdco.ZDCO_ID--, ntd.ZPOS_ID, ntd.ZPOS_ZARR_ID
) WHERE ABS(nvl(REV_S,PAY_S))<>(DECODE((SELECT WUSS.WUSS_VALUE FROM WUSS WHERE WUSS.WUSS_XCMS_ID=0 AND WUSS.WUSS_NAME='Show 0 Postings on Billing Screen'),1,-1,0)) ORDER BY CDT ASC;

 
CREATE OR REPLACE VIEW V8_REP_BV_BookingDetails_FUT AS
SELECT 
DECODE(a.CREATEDFROM,1,'SPA', 2, 'PMS') OriginatedSystem,
TRUNC(a.BOOKINGSTARTDATETIME) DayofSpa,
a.PARTGLASTNAME||', '||a.PARTGFIRSTNAME GuestName,
(SELECT n.NAME2 FROM v8_rep_name n WHERE n.XCMS_ID = a.PARTICIPATINGGUESTKEY) GuestName_Suite8,
a.BOOKINGDESCRIPTION_en BOOKINGDESCRIPTION,
a.BOOKINGSTARTDATETIME Starts,
a.BOOKINGENDDATETIME Ends,
a.BOOKINGDURATION Duration,
a.PRICE,
a.REMARK_en Remark,
a.CREATED,
a.Facility_en Facility,
a.Facility_Count,
a.Employee,
a.Employee_Count
FROM BV_VIEW_BookingDetailList_FUT a
WHERE 
 a.BOOKINGACTIVE = 1;
 
 
--former maintained by BV team as CID_BASIC_PACKAGE
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);

--ypdp_type: 0=per YPAP_XCAC_ID  1=per adult  2=flat

create or replace view v8_sys_res_package_price_conf as 
select ypac_id,ydet_id,sum(flat) flat,sum(adults) adults,sum(children) children
from ( 
select ypac_id,ydet_id,1 flat,0 adults,0 children
from ydet,ypal,ypac,ypat,ypap
where ypap_ypat_id=ypat_id
  and ypat_ypac_id=ypac_id
  and ypal_ypac_id=ypac_id
  and ydet_id=ypal_ydet_id
  and ydet_date>=ypat_from
  and ydet_date<=ypat_until
  and (ypat_invalid is null or ypat_invalid=0)
  and ypap_type=2
  and ypap_value>0  
union all
select ypac_id,ydet_id,0 flat,ydet_adultno adults,0 children
from ydet,ypal,ypac,ypat,ypap
where ypap_ypat_id=ypat_id
  and ypat_ypac_id=ypac_id
  and ypal_ypac_id=ypac_id
  and ydet_id=ypal_ydet_id
  and ydet_date>=ypat_from
  and ydet_date<=ypat_until
  and (ypat_invalid is null or ypat_invalid=0)
  and ydet_adultno>0
  and ypap_type=1
  and ypap_value>0  
union all 
select ypac_id,ydet_id,0 flat,0 adults,yrgl_number children
from ydet,ypal,ypac,ypat,ypap,yrgl
where ypap_ypat_id=ypat_id
  and ypat_ypac_id=ypac_id
  and ypal_ypac_id=ypac_id
  and ydet_id=ypal_ydet_id
  and ydet_date>=ypat_from
  and ydet_date<=ypat_until
  and (ypat_invalid is null or ypat_invalid=0)
  and ydet_id=yrgl_ydet_id
  and yrgl_xcac_id=ypap_xcac_id
  and ypap_type=0
  and ypap_value>0
union all    
select ypac_id,ydet_id,1 flat,0 adults,0 children
from ydet,ypal,ypac
where ypal_ypac_id=ypac_id
  and ydet_id=ypal_ydet_id
  and not exists (
      select 1 
      from ypat,ypap
      where ypap_ypat_id=ypat_id
        and ypat_ypac_id=ypac_id
        and ydet_date>=ypat_from
        and ydet_date<=ypat_until
        and (ypat_invalid is null or ypat_invalid=0)
        and ypap_value>0
      )    
) group by ypac_id,ydet_id;

--former maintained by BV team as CID_PACKAGE
CREATE OR REPLACE FORCE VIEW V8_SYS_BV_PACKAGE AS
SELECT   
    ypac.ypac_id basicpack_id
   ,ypac_shortdesc
   ,ypac_longdesc
   ,ypal_id custpack_id
   ,v.flat*ypal_multiplier flat
   ,v.adults*ypal_multiplier adults
   ,v.children*ypal_multiplier children
   ,v.flat*ypal_multiplier+v.adults*ypal_multiplier+v.children*ypal_multiplier packages_multiplier
   ,yres_id reservation_id
   ,ydet_date valid_day
   ,ydet_adultno nb_adults_res
   ,yres_exparrtime arrival
   ,yres_expdeptime departure
   ,xcms_id foreign_id
   ,yres_resstatus resstatus
FROM ypal, ypac, ydet, yres, xcms, v8_sys_res_package_price_conf v
WHERE ypac_longdesc LIKE 'BV%'
     AND (ypac_invalid IS NULL OR ypac_invalid = 0)
     AND ypal_ypac_id = ypac.ypac_id
     AND ypal_ydet_id = ydet.ydet_id
     AND ydet_yres_id = yres_id
     AND yres_resstatus IN (0,1)
     AND yres_xcms_id = xcms.xcms_id
     and v.ypac_id=ypac.ypac_id
     and v.ydet_id=ydet.ydet_id  
ORDER BY custpack_id, ydet_date;

--former maintained by BV team as cid_bof_bellavita_customers    
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,
          CreditLimit
     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,
                      YRES.YRES_CREDITLIMIT -nvl((select sum(V8_REP_CREDITLIMIT.BALANCE) from V8_REP_CREDITLIMIT where ZPOS_YRES_ID  = YRES_ID),0)  CreditLimit 
             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,
                  ZFAC.ZFAC_CREDITLIMIT -nvl((select sum(V8_REP_CREDITLIMIT.BALANCE) from V8_REP_CREDITLIMIT where V8_REP_CREDITLIMIT.ZFAC_ID  = zfac.ZFAC_ID),0) CreditLimit
             FROM zfac, zfan, zfag, fideliodate d
            WHERE zfac_zfan_id = zfan_id(+)
              AND zfac_zfag_id = zfag_id
              AND zfag_type IN (0, 5, 7, 9, 10, 11)
              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;     

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 LPAD('view version', 39) name, '2.0 (2013/02/13)' current_value, ' ' recommended_value, 'green' status, 'view parameter' category, 0 headline FROM dual
union all
  SELECT LPAD('oracle DB version', 39) name, (SELECT version FROM v$instance) current_value, ' ' recommended_value, 'green' status, 'database' 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, 'database 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
  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 LPAD(LOWER(param.name), 39) WHEN SUBSTR(param2.version,1,4) = '10.2' THEN LPAD(LOWER(param.name), 39) END name, param.value current_value, CASE WHEN param2.version = '11.2.0.3' THEN '{10.2.0.4 | [11.2.0.1]}' WHEN param2.version = '11.2.0.2' THEN '{10.2.0.4 | [11.2.0.1]}' WHEN param2.version = '11.2.0.1' THEN '{10.2.0.4 | [11.2.0.1]}' WHEN SUBSTR(param2.version,1,4) = '11.1' THEN '{10.2.0.4}' WHEN SUBSTR(param2.version,1,4) = '10.2' THEN '{10.2.0.4}' END recommended_value, CASE WHEN SUBSTR(param2.version,1,4) = '11.2' THEN CASE WHEN param.value = '11.2.0.1' THEN 'orange' 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 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
  SELECT LPAD(LOWER(param.name), 39) name, param.value current_value, CASE WHEN param2.version = '11.2' THEN '{NONE | [DB_EXTENDED]}' WHEN param2.version = '11.1' THEN '{NONE | [DB_EXTENDED]}' WHEN param2.version = '10.2' THEN '{NONE | [DB_EXTENDED]}' END recommended_value, CASE WHEN param2.version = '11.2' THEN CASE WHEN param.value = 'NONE' THEN 'green' WHEN param.value = 'DB_EXTENDED' THEN 'green' ELSE 'red' END WHEN param2.version = '11.1' THEN CASE WHEN param.value = 'NONE' THEN 'green' WHEN param.value = 'DB_EXTENDED' THEN 'green' ELSE 'red' END WHEN param2.version = '10.2' THEN CASE WHEN param.value = 'NONE' THEN 'green' WHEN param.value = 'DB_EXTENDED' THEN 'green' ELSE 'red' 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('Database Log Mode', 39) name, ' ' current_value, ' ' recommended_value, ' ' status, 'database' category, 1 headline FROM dual
UNION ALL
  SELECT LPAD('archive mode', 39) name, CASE WHEN param.value = 'ARCHIVELOG' THEN 'ENABLED' WHEN param.value = 'NOARCHIVELOG' THEN 'DISABLED' END current_value, '{ENABLED}' 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', 39) name, ' ' current_value, LPAD('{-1%/+1%}', 29) 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('tablespace of table: DUAL', 39) name, param.value current_value, '{SYSTEM}' 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 Parameter File', 39) name, ' ' current_value, ' ' recommended_value, ' ' status, 'parameter file' category, 1 headline FROM dual
UNION ALL
  SELECT LPAD('server parameter file', 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, (SELECT '{spfile'||(SELECT UPPER(instance_name) FROM v$instance)||'.ora}' FROM dual) 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 Files', 39) name, ' ' current_value, ' ' recommended_value, ' ' status, 'redo log files' category, 1 headline FROM dual
UNION ALL
  SELECT LPAD('redo log groups #', 39) name, TO_CHAR(param.value) current_value, '{5-10}' 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 files' category, 0 headline FROM (SELECT COUNT(1) value FROM v$log) param
UNION ALL
  SELECT LPAD('redo log size', 39) name, TO_CHAR(param.value) current_value, '{25-100 MB}' 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 files' category, 0 headline FROM (SELECT MAX(bytes/1024/1024) value FROM v$log) param
UNION ALL
  SELECT RPAD('Missing Tablespaces', 39) name, ' ' current_value, ' ' 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, '{x => 5%}' current_value, ' ' 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
--SELECT * FROM (SELECT LPAD(tablespace_name, 39) name, SUBSTR(graph,1,14)||'!'||SUBSTR(graph,16,7)||', '||RPAD(TO_CHAR(total_gb,'9990.90'),8)||' GB ' current_value, CASE WHEN TRUNC(pct_used/75) <> 0 THEN '{overall full<75%}' END recommended_value, CASE WHEN TRUNC(pct_used/75) = 0 THEN 'green' ELSE 'red' END value, 'tablespaces' category, 0 headline FROM (SELECT total.ts tablespace_name, total.gb total_gb, NVL(total.gb-free.gb,total.gb) used_gb, NVL(free.gb,0) free_gb, DECODE(total.gb,null,0,NVL(ROUND((total.gb-free.gb)/(total.gb)*100,4),100)) pct_used, 100-DECODE(total.gb,null,0,NVL(ROUND((total.gb-free.gb)/(total.gb)*100,4),100)) pct_free, CASE WHEN (total.gb IS NULL) THEN '['||RPAD(LPAD('offline',13,'-'),20,'-')||']' ELSE '['|| DECODE(free.gb,null,'xxxxxxxxxxxxxxxxxxxx',NVL(RPAD(LPAD('X',TRUNC((100-ROUND((free.gb)/(total.gb)*100, 4))/5),'X'),20,'_'), '____________________'))||']' END graph FROM (SELECT tablespace_name ts, ROUND(SUM(CASE WHEN TRUNC(maxbytes) = 0 THEN bytes ELSE maxbytes END)/1024/1024/1024,4) gb FROM dba_data_files GROUP BY tablespace_name) total, (SELECT tablespace_name ts, ROUND(SUM(bytes)/1024/1024/1024,4) gb FROM dba_free_space GROUP BY tablespace_name) free, v$tablespace dbat WHERE total.ts=free.ts(+) AND total.ts=dbat.name ORDER BY 7 DESC) tsinfo WHERE (tablespace_name LIKE 'V8%' OR tablespace_name LIKE 'MF%' OR tablespace_name LIKE 'PERFSTAT' OR tablespace_name LIKE '%AUDIT%') ORDER BY tsinfo.tablespace_name)
   SELECT LPAD(name, 39) name, SUBSTR(graph,1,14)||'!'||SUBSTR(graph,16,7)||', '||CASE WHEN alloc_size <=1024 THEN RPAD(TO_CHAR(alloc_size,'9990.0'),8)||' MB ' ELSE RPAD(TO_CHAR(alloc_size/1024,'9990.0'),8)||' GB ' END current_value, CASE WHEN alloc_space_used_pct > 75 THEN '{overall full<75%}' END recommended_value, CASE WHEN TRUNC(alloc_space_used_pct/75) = 0 THEN 'green' ELSE 'red' END value, 'tablespaces' category, 0 headline FROM ( 
               SELECT tbs.*, CASE WHEN (alloc_space_used_pct IS NULL) THEN '['||RPAD(LPAD('offline',13,'_'),20,'_')||']' ELSE '['|| DECODE(alloc_space_used_pct,null,'XXXXXXXXXXXXXXXXXXXX',NVL(RPAD(LPAD('X',TRUNC((alloc_space_used_pct)/5),'X'),20,'_'), '____________________'))||']' END graph FROM (
               WITH df AS (SELECT tablespace_name, SUM(bytes) bytes, SUM(CASE WHEN maxbytes = 0 THEN bytes ELSE maxbytes END) maxbytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext FROM dba_data_files GROUP BY tablespace_name), tf AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext FROM dba_temp_files GROUP BY tablespace_name)
                                SELECT d.tablespace_name NAME, ROUND(NVL(a.maxbytes/1024/1024, 0), 1) MAX_SIZE, ROUND(NVL(a.bytes/1024/1024, 0), 1) ALLOC_SIZE, ROUND(NVL(a.bytes-NVL(f.bytes, 0), 0)/1024/1024, 1) SPACE_USED, ROUND(NVL((a.bytes-NVL(f.bytes, 0))/a.bytes * 100, 0), 1) ALLOC_SPACE_USED_PCT, a.autoext, ROUND(NVL(f.bytes, 0)/1024/1024, 1) ALLOC_FREE_SPACE, d.status, a.cnt DATAFILES#, d.contents TYPE, d.extent_management EXTENT_MGMT, d.segment_space_management SEGMENT_MGMT FROM dba_tablespaces d, df a, (SELECT tablespace_name, SUM(bytes) BYTES FROM dba_free_space GROUP BY tablespace_name) f WHERE d.tablespace_name=a.tablespace_name(+) AND d.tablespace_name=f.tablespace_name(+) AND NOT d.contents='UNDO' AND NOT (d.extent_management='LOCAL' AND d.contents='TEMPORARY')
                              UNION ALL
                                SELECT d.tablespace_name, NVL(a.bytes/1024/1024, 0), NVL(a.bytes/1024/1024, 0), NVL(t.ub*d.block_size, 0)/1024/1024, NVL((t.ub*d.block_size)/a.bytes*100, 0), a.autoext, (NVL(a.bytes, 0)/1024/1024-NVL((t.ub*d.block_size), 0)/1024/1024), d.status, a.cnt, d.contents, d.extent_management, d.segment_space_management FROM dba_tablespaces d, tf a, (SELECT ss.tablespace_name, SUM(ss.used_blocks) ub FROM gv$sort_segment ss GROUP BY ss.tablespace_name) t WHERE  d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY' ORDER BY 1 ) tbs) WHERE (name  LIKE 'V8%' OR name LIKE 'MF%' OR name LIKE 'PERFSTAT' OR name LIKE '%AUDIT%')
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;

