aonestar.public

Parameters

Name	Type	Mode
rateplan_id	integer	IN
roomtype_id	integer	IN
adult	integer	IN
child	integer	IN
infant	integer	IN
include_adult	smallint	OUT
include_child	smallint	OUT
include_infant	smallint	OUT
extra_adult	smallint	OUT
extra_child	smallint	OUT
extra_infant	smallint	OUT
extra_adult_rate	t_money	OUT
extra_child_rate	t_money	OUT
extra_infant_rate	t_money	OUT
mealtype_id	integer	OUT

Definition

DECLARE
    allowed_adult integer;
    allowed_child integer;
    allowed_infant integer;
BEGIN
    SELECT
      COALESCE(rd.rate_extrabed, rp.rate_extrabed, rt.extrabed_rate, fn_sys_param('ROOM_RATE', 'EXTRA_ADULT', 0.0)),
      COALESCE(rd.rate_child, rp.rate_child, rt.child_rate, fn_sys_param('ROOM_RATE', 'EXTRA_CHILD', 0.0)),
      COALESCE(rd.rate_infant, rp.rate_infant, rt.infant_rate, fn_sys_param('ROOM_RATE', 'EXTRA_INFANT', null::t_money)),
      COALESCE(rd.include_adult, rt.include_adult, fn_default('INCLUDE_ADULT', 2), ADULT),
      COALESCE(rd.include_child, rt.include_child),
      COALESCE(rd.include_infant, rt.include_infant),
      rp.mealtype_id
    FROM rate_details rd 
        LEFT JOIN rate_plan rp ON rp.id = rd.rateplan_id
        LEFT JOIN room_type rt on rt.id = rd.roomtype_id
    WHERE (rd.rateplan_id = $1) and (rd.roomtype_id = $2)
    INTO extra_adult_rate, extra_child_rate, extra_infant_rate, allowed_adult, allowed_child, allowed_infant, mealtype_id;

    include_adult := least(adult, allowed_adult);

    allowed_child := allowed_child + (allowed_adult - include_adult);
    include_child = least(child, allowed_child);

    allowed_infant := allowed_infant + (allowed_adult + allowed_child - include_adult - include_child);
    include_infant = least(infant, allowed_infant);

    extra_adult  := IIF(adult  > include_adult,  adult  - include_adult,  0);
    extra_child  := IIF(child  > include_child,  child  - include_child,  0);
    extra_infant := IIF(infant > include_infant, infant - include_infant, 0);
    
    RETURN;
END;

sp_calc_extra

Parameters

Definition