aonestar.public

Parameters

Name	Type	Mode
booking_id	integer	IN (DEFAULT NULL)
booking_item	integer	IN (DEFAULT NULL)
reset_extra_qty	boolean	IN (DEFAULT false)

Definition

DECLARE 
    _other_charge t_money;
    _booking_id int;
    total record;
BEGIN

    if reset_extra_qty then
--        for total in (
--            with rg as (
--                select rg.id as register_id, ex.extra_adult, ex.extra_child, ex.extra_infant  
--                from registration rg 
--                    left join room rm on rm.id = rg.room_id,
--                    lateral sp_calc_extra(rg.rateplan_id, rm.roomtype_id, rg.adult, rg.child, rg.infant) ex
--                where $1 is null or rg.id = $1
--            )
--            select rg.register_id, rg.extra_adult, rg.extra_child, rg.extra_infant,  
--                   sum(rr.room_rate) as room_charge,  
--                     coalesce(sum(rg.extra_adult  * rr.extrabed_rate), 0.0)
--                   + coalesce(sum(rg.extra_child  * rr.child_rate), 0.0)
--                   + coalesce(sum(rg.extra_infant * rr.infant_rate), 0.0) as extra_charge
--            from  rg left join registration_rates rr on rr.register_id = rg.register_id
--            group by 1,2,3,4)
--        loop
--            _other_charge = (select sum(charge_qty * charge_amount) 
--                             from charge_schedule cs 
--                             where cs.register_id = total.register_id and cs.charge_type <> 'inclusion');
--    
--            update registration rg
--            set room_charge = total.room_charge, 
--                extra_charge = total.extra_charge,
--                other_charge = _other_charge,
--                extra_adult  = rg.extra_adult,
--                extra_child  = rg.extra_child,
--                extra_infant = rg.extra_infant
--            where rg.id = total.register_id;
--        end loop;
    else
        /* update summary of each booking item (total for single room)*/
        for total in (
            select bi.booking_id, bi.item_id, bi.adult, bi.child, bi.infant, bi.arrival, bi.departure, 
                   sum(IIF(br.comp, 0, br.room_rate)) as room_charge,  
                   coalesce(sum(bi.extra_adult  * br.extrabed_rate), 0.0)
                   + coalesce(sum(bi.extra_child  * br.child_rate), 0.0)
                   + coalesce(sum(bi.extra_infant * br.infant_rate), 0.0) as extra_charge
            from booking_items bi left join booking_rates br on br.booking_item = bi.item_id
            where ($1 is null or bi.booking_id = $1) and ($2 is null or bi.item_id = $2)
            group by 1,2,3,4,5,6,7)
        loop
            
--            _other_charge = (select sum(charge_qty * charge_amount) 
--                             from charge_schedule cs inner join guest_request rq on rq.id = cs.request_id 
--                             where rq.booking_item = total.item_id and not rq.inclusive);

            with req as (
                SELECT rq.charge_rate,
                   fn_get_request_qty(1, total.adult, total.child, total.infant, rq.qty, rq.multiplier) AS charge_qty,    
                   fn_get_request_nights(rq.frequency , total.arrival, total.departure, rq.start_date, rq.end_date) AS charge_nights 
                FROM guest_request rq
                WHERE (rq.register_id IS NULL) 
                    AND (total.booking_id = rq.booking_id) 
                    AND (rq.booking_item IS NULL OR rq.booking_item = total.item_id) 
                    AND (rq.multiplier <> 1)
                    AND NOT rq.inclusive
                    AND NOT coalesce(rq.charge_to_booking, false)
            )
            select sum(charge_rate * charge_qty * charge_nights)
            from req
            into _other_charge;
    
            update booking_items bi
            set room_charge = total.room_charge, 
                extra_charge = total.extra_charge,
                other_charge = _other_charge
            where bi.item_id = total.item_id;
        end loop;

        /* update summary of booking */
        for _booking_id in (select id from booking b where $1 is null or b.id = $1) 
        loop
            _other_charge = (select sum(charge_qty * charge_amount) 
                             from charge_schedule cs 
                             where cs.booking_id = _booking_id and cs.charge_type <> 'inclusion');

            with req as (
                SELECT rq.charge_rate,
                   fn_get_request_qty(1, total.adult, total.child, total.infant, rq.qty, rq.multiplier) AS charge_qty,    
                   fn_get_request_nights(rq.frequency , total.arrival, total.departure, rq.start_date, rq.end_date) AS charge_nights 
                FROM guest_request rq
                WHERE (rq.register_id IS NULL) 
                    AND (total.booking_id = rq.booking_id) 
                    AND NOT rq.inclusive
                    --AND (rq.booking_item IS NULL OR rq.booking_item = total.item_id) 
                    --AND (rq.multiplier <> 1)
                    --AND NOT coalesce(rq.charge_to_booking, false)
            )
            select sum(charge_rate * charge_qty * charge_nights)
            from req
            into _other_charge;
    
            with summ as ( 
                select 
                  min(arrival) as min_arrival, 
                  max(departure) as max_departure,  
                  sum(room_charge) as room_charge,
                  sum(extra_charge) as extra_charge 
                from booking_items bi
                where bi.booking_id = _booking_id    
            )
            update booking b
            set arrival       = min_arrival,
                departure     = max_departure,
                room_charge   = summ.room_charge, 
                extra_charge  = summ.extra_charge,
                other_charge  = _other_charge,
                total_deposit = (select sum(d.amount) from deposit d where d.booking_id = _booking_id and d.status <> 'X')
            from summ
            where b.id = _booking_id;
        end loop;
    end if;

END

sp_update_booking_summary

Parameters

Definition