--
--In House
--
select inh.* from
(SELECT 
  YRES.YRES_ACTARRTIME,
  YRES.YRES_ACTDEPTIME,
  YRES.YRES_expARRTIME,
  YRES.YRES_expDEPTIME,
  substr(decode(XCMS.XCMS_NAME3,null,XCMS.XCMS_NAME1,XCMS.XCMS_NAME1||', '||XCMS.XCMS_NAME3),1,19) 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 yhks.YHKS_SHORTDESC from yhks,yrms,ydet where yhks.yhks_id = yrms.yrms_yhks_id and 
     yrms.yrms_id=ydet.ydet_yrms_id and ydet.ydet_id = yres.YRES_CURRENT_YDET_ID) HSK_STATUS,
     (select V8_SYS_S8HP_SETTINGS.S8HP_MYSTAYCHECKPLUSPROFILEID||XCMS.XCMS_ID from V8_sys_S8HP_Settings)  MyStayCheckURL
FROM 
  YRES,
  YDET,
  yrms,
  XCMS
WHERE 
  ydet_yrms_id=yrms_id(+)        and
  yres_expdeptime>= V8_sys_Fideliodate and
  yres_exparrtime<=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_ACTARRTIMENULL = 0
order by xcms_name1, xcms_name3 ) inh;
