aonestar.public

Parameters

Name	Type	Mode
channel_id	integer	IN
arrival	date	IN
departure	date	IN
adult	integer	IN
child	integer	IN
qty	integer	IN (DEFAULT 1)
allow_extrabed	boolean	IN (DEFAULT true)
rate_id	integer	OUT
roomtype_id	integer	OUT
roomtype_code	text	OUT
roomtype_name	text	OUT
roomtype_seq	integer	OUT
rateplan_id	integer	OUT
rateplan_code	text	OUT
rateplan_name	text	OUT
rateplan_seq	integer	OUT
include_adult	smallint	OUT
include_child	smallint	OUT
extra_adult	smallint	OUT
extra_child	smallint	OUT
room_rate	t_money	OUT
extra_adult_rate	t_money	OUT
extra_child_rate	t_money	OUT
total_amount	t_money	OUT
mealtype_id	integer	OUT
mealtype_name	text	OUT
abf_included	boolean	OUT
lunch_included	boolean	OUT
dinner_included	boolean	OUT
no_amend	boolean	OUT
no_cancel	boolean	OUT
no_refund	boolean	OUT
room_avail	integer	OUT
rate_description	text	OUT
room_description	text	OUT
bedtypes	json	OUT
rate_schedule	json	OUT

Definition

declare
    v_nights int;
BEGIN
  arrival  := coalesce(arrival, fn_system_date());
  v_nights := IIF(departure = arrival, 1, departure - arrival);

RETURN QUERY
  with avail as (
    select a.roomtype_id, a.roomtype_code, a.roomtype_name, a.roomtype_seq, min(a.avail) as avail
    from sp_get_room_available($2, $3, null) A
    where a.avail >= qty and not a.stop_sell
    group by 1,2,3,4
  ),
  room_beds as (
    select b.roomtype_id,
        json_agg(json_build_object(
          'id', b.bedtype_id,
          'code', b.code,
          'name', b.name)
        ) as bedtypes
    from (select distinct rm.roomtype_id, rm.bedtype_id, b.code, b.name
          from room rm left join bed_type b on b.id = rm.bedtype_id) B
    group by 1
  ),
  room_avl as (
    select a.roomtype_id, a.roomtype_code, a.avail,
        a.roomtype_name,
        a.roomtype_seq,
        rt.description as room_description,
        b.bedtypes
    from avail a
        left join room_type rt on rt.id = a.roomtype_id
        left join room_beds b on b.roomtype_id = a.roomtype_id
  ),
  av AS (
      select av.roomtype_id,
            av.roomtype_code::text as roomtype_code,
            rd.rate_id, rd.rateplan_id, rp.mealtype_id,
            av.roomtype_seq,
            rp.order_seq as rateplan_seq,
            av.roomtype_name,
            rp.code::text as rateplan_code,
            rp.name as rateplan_name,
            mt.name as mealtype_name,
            mt.abf IS NOT NULL AS abf_included,
            mt.lunch IS NOT NULL AS lunch_included,
            mt.dinner IS NOT NULL AS dinner_included,
            av.room_description,
            rp.description as rate_description,
            av.avail, av.bedtypes
        from rate_plan_channels a
            inner join rate_details rd on rd.rateplan_id = a.rateplan_id
            inner join room_avl av on av.roomtype_id = rd.roomtype_id
            left join rate_plan rp on rp.id = a.rateplan_id
            left join meal_type mt on mt.id = rp.mealtype_id
        where a.channel_id = $1 and a.enabled and rd.active and rp.enabled --and av.avail > 0
        order by av.roomtype_seq, rp.order_seq
  )
  SELECT av.rate_id,
    av.roomtype_id, av.roomtype_code, av.roomtype_name, av.roomtype_seq::int,
    av.rateplan_id, av.rateplan_code, av.rateplan_name, av.rateplan_seq,
    ar.include_adult, ar.include_child, ar.extra_adult, ar.extra_child,
    (ar.total_amount / v_nights)::t_money as rate_amount,
    (ar.rate_schedule->0->>'extra_adult_rate')::t_money,
    (ar.rate_schedule->0->>'extra_child_rate')::t_money,
    ar.total_amount,
    av.mealtype_id, av.mealtype_name, av.abf_included, av.lunch_included, av.dinner_included,
    ar.no_amend, ar.no_cancel, ar.no_refund, av.avail, av.rate_description,
    av.room_description, av.bedtypes, ar.rate_schedule
  FROM av INNER JOIN
    sp_get_available_rates(null, $2, $3, $6, $4, $5, i_channel_id := $1) ar
    ON av.rate_id = ar.rate_id
  WHERE not ar.is_invalid and (allow_extrabed or ar.extra_adult = 0);
END

sp_get_available_rooms_and_rates

Parameters

Definition