aonestar.public

Parameters

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

Definition

DECLARE 
    _other_charge t_money;
    total record;
    rateplan_id int;
    roomtype_id int;
BEGIN

    if reset_extra_qty then
        for total in (
            with rg as (
                select rg.id as register_id, 
                    coalesce(ex.extra_adult, rg.extra_adult) as extra_adult, 
                    coalesce(ex.extra_child, rg.extra_child) as extra_child, 
                    coalesce(ex.extra_infant, rg.extra_infant) as 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(IIF(rr.comp, 0, 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 r
            set room_charge = total.room_charge, 
                extra_charge = total.extra_charge,
                other_charge = _other_charge,
                extra_adult  = total.extra_adult,
                extra_child  = total.extra_child,
                extra_infant = total.extra_infant
            where r.id = total.register_id;
        end loop;
    else
        for total in (
            select rg.id as register_id, 
                   sum(IIF(rr.comp, 0, 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 registration rg left join registration_rates rr on rr.register_id = rg.id
            where $1 is null or rg.id = $1
            group by 1)
        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
            where rg.id = total.register_id;
        end loop;
    end if;

END

sp_update_registration_summary

Parameters

Definition