aonestar.public

Parameters

Name	Type	Mode
i_rate_date	date	IN (DEFAULT NULL)
i_booking_id	integer	IN (DEFAULT NULL)
i_booking_item	integer	IN (DEFAULT NULL)
i_charge_to_booking	boolean	IN (DEFAULT NULL)
only_not_checked_in	boolean	IN (DEFAULT true)
booking_id	integer	OUT
booking_item	integer	OUT
charge_date	date	OUT
arrival	date	OUT
departure	date	OUT
room_qty	smallint	OUT
adult	smallint	OUT
child	smallint	OUT
infant	smallint	OUT
extra_adult	smallint	OUT
extra_child	smallint	OUT
rateplan_id	integer	OUT
mealtype_id	integer	OUT
rate_amount	t_money	OUT
rate_extra_adult	t_money	OUT
room	t_money	OUT
extrabed	t_money	OUT
abf	t_money	OUT
lunch	t_money	OUT
dinner	t_money	OUT
inclusive	t_money	OUT
other	t_money	OUT
inclusive_items	json	OUT
other_items	json	OUT
charge_to_booking	boolean	OUT
abf_included	t_money	OUT
lunch_included	t_money	OUT
dinner_included	t_money	OUT
abf_extra	t_money	OUT
lunch_extra	t_money	OUT
dinner_extra	t_money	OUT
room_net_dept	integer	OUT
room_net	numeric	OUT
room_service	numeric	OUT
room_tax	numeric	OUT

Definition

WITH RSV AS (
        SELECT bi.booking_id, bi.item_id as booking_item,
            bi.arrival, bi.departure, bi.rateplan_id, bi.mealtype_id,
            (bk.book_type = 'G') as is_group,
            (bi.charge_to = 'B' and bk.book_type = 'G') as charge_to_booking,
            false as split_room_charge,
            bi.adult, bi.child, bi.infant, bi.extra_adult, bi.extra_child, bi.extra_infant,
            bi.included_adult, bi.included_child,
            IIF(only_not_checked_in, bi.qty-bi.inhouse_qty, bi.qty) as room_qty
        FROM booking bk
            INNER JOIN booking_items bi ON bi.booking_id = bk.id
        WHERE (i_rate_date is null or i_rate_date between bi.arrival and bi.departure)
            AND (i_booking_id is not null or i_booking_item is not null or bk.status in ('R','C','I'))
            AND (not only_not_checked_in or bi.qty-bi.inhouse_qty > 0)
            AND (i_booking_id is NULL OR bi.booking_id = i_booking_id)
            AND (i_booking_item is NULL OR bi.item_id  = i_booking_item)
            AND (i_charge_to_booking is null OR (i_charge_to_booking = (bi.charge_to = 'B' and bk.book_type = 'G')))
    ),
    Schedule as (
        SELECT rsv.booking_id, rsv.booking_item, rsv.arrival, rsv.departure, rsv.rateplan_id,
            coalesce(rs.mealtype_id, rsv.mealtype_id) as mealtype_id,
            rsv.charge_to_booking, rsv.adult, rsv.child, rsv.infant, rsv.room_qty,
            rsv.extra_adult, rsv.extra_child, rsv.included_adult, rsv.included_child,
            rs.charge_date, rs.room_rate, rs.extrabed_rate as extra_adult_rate, rs.child_rate as extra_child_rate,
            (rsv.extra_adult * rs.extrabed_rate) + (rsv.extra_child * rs.child_rate) as extra_amt,
            rs.room_charge_dept
        FROM RSV inner join booking_rates rs on rsv.booking_item = rs.booking_item
        WHERE (i_rate_date is null or rs.charge_date = i_rate_date)
    ),
    inclusive as (
        select inc.booking_id, inc.booking_item, post_date,
            coalesce(sum(qty * charge_rate),0) as charge_amt,
            json_agg(json_build_object(
                'inclusion_id', inclusion_id,
                'name', inc.name,
                'charge_date', inc.charge_date,
                'charge_dept', charge_dept,
                'frequency', frequency,
                'qty', qty,
                'charge_rate', charge_rate,
                'remark', remark,
                'charge_to_booking', inc.charge_to_booking,
                'posted', false
            )) as items
        from (
            SELECT rsv.booking_id, rsv.booking_item, d.post_date, inc.name,
                 CASE frequency
                    WHEN 1 THEN d.post_date +1
                    WHEN 2 THEN d.post_date
                    WHEN 3 THEN rsv.arrival
                    WHEN 4 THEN rsv.departure
                 END AS charge_date,
                 inc.inclusion_id, inc.charge_dept, inc.frequency,
                 fn_calc_inclusion_qty(1, adult, child, infant, 1, inc.charge_rate,
                                       inc.multiplier, inc.frequency, d.post_date, d.post_date+1) AS qty,
                 inc.charge_rate,
                 rsv.charge_to_booking,
                 iif(inc.name <> dp.name, inc.name, null) AS remark
            FROM RSV,
                LATERAL sp_get_rate_inclusions(rsv.rateplan_id) AS inc
                LEFT JOIN department dp ON dp.id = inc.charge_dept,
                LATERAL fn_date_range(rsv.arrival, rsv.departure) AS d(post_date)
            WHERE (i_rate_date is null or d.post_date = i_rate_date)
                  AND ((frequency = 1 AND d.post_date <> rsv.arrival)       -- Daily except arrival
                      OR (frequency = 2 AND d.post_date <> rsv.departure)   -- Daily except departure
                      OR (frequency = 3 AND d.post_date = rsv.arrival)      -- Once on arrival
                      OR (frequency = 4 AND d.post_date = rsv.departure))   -- Once on departure
        ) as INC
          LEFT JOIN schedule s
                on s.booking_id = inc.booking_id
                    and s.booking_item = inc.booking_item
                    and s.charge_date = inc.post_date
        where qty > 0 and charge_rate > 0
        group by 1,2,3
    ), rate_rows as (
        select s.booking_id, s.booking_item, s.charge_date, s.arrival, s.departure,
            s.room_qty, s.adult, s.child, s.infant, s.extra_adult, s.extra_child,
            s.rateplan_id, s.mealtype_id, s.room_rate as rate_amount, s.extra_adult_rate as rate_extra_adult,
            CASE WHEN s.room_rate::numeric = 0 THEN 0::t_money ELSE
                (s.room_rate
                 - coalesce(m.abf_included_amt,0)
                 - coalesce(m.lunch_included_amt,0)
                 - coalesce(m.dinner_included_amt,0)
                 - coalesce(inc.charge_amt,0))
            END as room,
            (s.extra_amt
             - coalesce(m.abf_extra_amt,0)
             - coalesce(m.lunch_extra_amt,0)
             - coalesce(m.dinner_extra_amt,0)) as extrabed,
            coalesce(m.abf_amt,0) as abf,
            coalesce(m.lunch_amt,0) as lunch,
            coalesce(m.dinner_amt,0) as dinner,
            coalesce(inc.charge_amt,0) as inclusive,
            0.0 as other, --coalesce(oth.charge_amt,0) as other,
            inc.items as inclusive_items,
            null::json as other_items, --oth.items as other_items,
            s.charge_to_booking,
            coalesce(m.abf_included_amt,0) as abf_included,
            coalesce(m.lunch_included_amt,0) as lunch_included,
            coalesce(m.dinner_included_amt,0) as dinner_included,
            coalesce(m.abf_extra_amt,0) as abf_extra,
            coalesce(m.lunch_extra_amt,0) as lunch_extra,
            coalesce(m.dinner_extra_amt,0) as dinner_extra,
            s.room_charge_dept as room_charge_dept_guard,
            m.register_id as ord_register_id
        from schedule s
            left join inclusive inc
                on inc.booking_id = s.booking_id and inc.booking_item = s.booking_item and inc.post_date = s.charge_date
            left join sp_meal_plan(
                            i_charge_date := $1,
                            i_booking_id := i_booking_id,
                            status := 'RSV',
                            single_room := true
                      ) m
                on m.booking_id = s.booking_id and m.booking_item = s.booking_item and m.charge_date = s.charge_date
    )
    select r.booking_id, r.booking_item, r.charge_date, r.arrival, r.departure,
        r.room_qty, r.adult, r.child, r.infant, r.extra_adult, r.extra_child,
        r.rateplan_id, r.mealtype_id, r.rate_amount, r.rate_extra_adult,
        r.room, r.extrabed, r.abf, r.lunch, r.dinner, r.inclusive, r.other,
        r.inclusive_items, r.other_items,
        r.charge_to_booking,
        r.abf_included, r.lunch_included, r.dinner_included,
        r.abf_extra, r.lunch_extra, r.dinner_extra,
        /* Same dept resolution as sp_rate_schedule_breakdown / sp_auto_post:
           rate_plan first, then the booking_rates guard column, then AUTO_POST.ROOM. */
        coalesce(rp.room_charge_dept, r.room_charge_dept_guard, fn_sys_param('AUTO_POST', 'ROOM', null::int)) as room_net_dept,
        ib.base    as room_net,
        ib.service as room_service,
        ib.tax     as room_tax
    from rate_rows r
        left join rate_plan rp on rp.id = r.rateplan_id
        left join department d
            on d.id = coalesce(rp.room_charge_dept, r.room_charge_dept_guard, fn_sys_param('AUTO_POST', 'ROOM', null::int))
        left join lateral fn_itemizer_breakdown(r.room, coalesce(d.itemizers, '{}'::text[])) ib on true
    order by r.ord_register_id desc, r.charge_date;

sp_booking_rates_breakdown

Description

Parameters

Definition