--001

select dep.* from
(SELECT 
  YRES.YRES_ACTARRTIME,
  YRES.YRES_ACTDEPTIME,
  YRES.YRES_expARRTIME,
  YRES.YRES_expDEPTIME,
  decode(XCMS.XCMS_NAME3,null,XCMS.XCMS_NAME1,XCMS.XCMS_NAME1||', '||XCMS.XCMS_NAME3) NAME,  
  yres.yres_xcms_id,
  YDET.YDET_ADULTNO,
  YDET.YDET_NOOFROOMS,
  ydet_yrms_id,
  yrms_shortdesc,
  (select ycat.YCAT_SHORTDESC from ycat where ycat.YCAT_ID = ydet_Ycat_id) YCAT_SHORTDESC,
  yres_id,
  To_Char(yres_exparrtime,'DD.MM.YYYY') as Arrivaldate,
  To_Char(yres_expdeptime,'DD.MM.YYYY') as Departuredate,
  round (trunc(yres_expdeptime-yres_exparrtime)) as nights,
  TO_CHAR(getYDetDiscountedAmount(ydet_rateamount,ydet_ratemanual,ydet_ratediff,ydet_ratediscountperc,ydet_nodiscountpackage,ydet_finalAmount),'999990.99') as rate,
  (select to_Char((bgs.revenue + bgs.Payment),'999990.99') from v8_sys_billingguestsearch bgs where BGS.ACC_ID  = YRES.YRES_ID) Balance
FROM 
  YRES,
  YDET,
  yrms,
  XCMS
WHERE 
  ydet_yrms_id=yrms_id(+)        and
  yres_expdeptime< = V8_sys_FidelioDate and
  yres_current_ydet_id=ydet_id and
  yres.YRES_RESSTATUS<>3 and
  yres.yres_xcms_id=xcms.xcms_id and
  yres.yres_actdeptimenull = 1 and
  yres.yres_actarrtimenull = 0
order by xcms_name1, xcms_name3 ) dep;


--
-- 002 Balance per Reservation
--
SELECT 
  zwin.ZWIN_WINDOW    ZWIN_WINDOW,
  zwin.ZWIN_ID,
  (select to_char(nvl(sum(decode(zpos_cdt, 1, zpos_sumupamount, 2, zpos_sumupamount, 5, -(zpos_sumupamount))),0),'999990.99') summ from ZPOS where (zpos_zall_id=0 or zpos_zall_id is null) and zpos_proforma is null and zpos_zwin_id=zwin_id) WinAmount,
  (select to_char(nvl(sum(decode(zpos_cdt, 1, zpos_sumupamount, 2, zpos_sumupamount, 5, -(zpos_sumupamount))),0),'999990.99') summ from ZPOS where (zpos_zall_id=0 or zpos_zall_id is null) and zpos_proforma is null and zpos.ZPOS_YRES_ID =:Parameter) TotAmount   
FROM 
  zwin
WHERE 
  zwin_yres_id=:parameter;
