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

Definition

--declare SYS_DATE date = fn_system_date();
--BEGIN
    --RETURN QUERY
    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,
           -- bool_and(s.inclusive_posted),
            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, --false as posted, --s.inclusive_posted as posted, 
                 rsv.charge_to_booking,
                 iif(inc.name <> dp.name, inc.name, null) AS remark --inc.name 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 ($2 is null or s.inclusive_posted = $2)  
                  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 --AND ($2 is null or s.posted = $2)  
        group by 1,2,3
--    ),
--    REQUESTS as (
--        SELECT rq.*
--        FROM (Select distinct ) guest_request rq --ON rq.booking_id = rsv.booking_id and rq.booking_item is null
--        WHERE rq.booking_item is null and rq.booking_id is not null and rq.booking_id in (select booking_id from RSV)
--        UNION ALL
--        SELECT rq.*
--        FROM RSV LEFT JOIN guest_request rq ON rq.booking_id = rsv.booking_id and rq.booking_item = rsv.booking_item
--    ), other as (
--        SELECT booking_id, booking_item, post_date,
--            coalesce(sum(total_amount),0) as charge_amt,
--            bool_and(posted) as posted,
--            json_agg(json_build_object(
--                'charge_id',    charge_id, 
--                'inclusion_id', inclusion_id, 
--                'name',         req.name,
--                'charge_date',  charge_date, 
--                'charge_dept',  charge_dept, 
--                'frequency',    frequency,
--                'qty',          qty, 
--                'charge_rate',  charge_rate, 
--                'remark',       remark,
--                'charge_to_booking', 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(rsv.room_qty, adult, child, infant, 1, inc.charge_rate, 
--                                       inc.multiplier, inc.frequency, d.post_date, d.post_date+1) AS qty,
--                 inc.charge_rate, --false as posted, --s.inclusive_posted as posted, 
--                 rsv.charge_to_booking,
--                 iif(inc.name <> dp.name, inc.name, null) AS remark --inc.name AS remark,        
--            FROM guest_request rq
--                LEFT JOIN inclusion inc ON inc.id = rq.inclusion_id 
--                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 ($2 is null or s.inclusive_posted = $2)  
--                  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 REQ
--        WHERE ($1 is null or req.post_date = $1)
--        GROUP BY 1,2
--        ORDER BY register_id desc, post_date    
    )
    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, --s.extra_child_rate,
        (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)) as room, 
        (s.extra_amt 
         - coalesce(m.abf_extra_amt,0) 
         - coalesce(m.lunch_extra_amt,0) 
         - coalesce(m.dinner_extra_amt,0)) as extra_bed, 
        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, --oth.items as other_items,
        s.charge_to_booking, 
        coalesce(m.abf_included_amt,0), coalesce(m.lunch_included_amt,0), coalesce(m.dinner_included_amt,0),
        coalesce(m.abf_extra_amt,0), coalesce(m.lunch_extra_amt,0), coalesce(m.dinner_extra_amt,0)
    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 other oth 
--            on oth.register_id = s.register_id and oth.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
    --where  (inc.items is not null) --or (oth.items is not null)
    order by register_id desc, charge_date--;
--END

sp_booking_rates_breakdown

Parameters

Definition