aonestar.public

Parameters

Name	Type	Mode
rateplan_id	integer	IN
roomtype_id	integer	IN
rate_amount	t_money	IN
mealtype_id	integer	IN (DEFAULT NULL)
adult	smallint	IN (DEFAULT NULL)
child	smallint	IN (DEFAULT NULL)
arrival	date	IN (DEFAULT NULL)
departure	date	IN (DEFAULT NULL)
charge_date	date	IN (DEFAULT NULL)
min_rate	t_money	IN (DEFAULT NULL)
rate_code	text	IN (DEFAULT NULL)
room_code	text	IN (DEFAULT NULL)

Definition

DECLARE
    v_rateplan_id integer := rateplan_id;
    v_roomtype_id integer := roomtype_id;
    v_rate_amount t_money := rate_amount;
    v_mealtype_id integer := mealtype_id;
    v_adult smallint := adult;
    v_child smallint := child;
    v_arrival date := arrival;
    v_departure date := departure;
    v_charge_date date := charge_date;
    v_daily_mode boolean := arrival IS NULL OR departure IS NULL OR charge_date IS NULL;
    v_room_code text := room_code;
    v_rate_code text := rate_code;
    v_min_rate t_money := min_rate;
    meal_amount numeric;
    inclusive_amount numeric;
    net_room_rate numeric;
BEGIN
    IF v_rate_amount IS NULL THEN
        RETURN;
    END IF;

IF v_room_code IS NULL
       OR v_rate_code IS NULL
       OR v_min_rate IS NULL
       OR v_mealtype_id IS NULL
       OR v_adult IS NULL
       OR v_child IS NULL THEN
        SELECT COALESCE(v_room_code, rt.code),
               COALESCE(v_rate_code, rp.code),
               COALESCE(v_min_rate, rp.min_rate, rt.min_rate),
               COALESCE(v_mealtype_id, rp.mealtype_id),
               COALESCE(v_adult, rd.include_adult, rt.include_adult, 2),
               COALESCE(v_child, rd.include_child, rt.include_child, 0)
        INTO v_room_code,
             v_rate_code,
             v_min_rate,
             v_mealtype_id,
             v_adult,
             v_child
        FROM rate_plan rp
            JOIN room_type rt ON rt.id = v_roomtype_id
            LEFT JOIN rate_details rd ON rd.rateplan_id = rp.id AND rd.roomtype_id = v_roomtype_id
        WHERE rp.id = v_rateplan_id;
    END IF;

IF v_daily_mode THEN
        v_charge_date := CURRENT_DATE;
        v_arrival := CURRENT_DATE - 1;
        v_departure := CURRENT_DATE + 1;
    END IF;

SELECT COALESCE(meal.inc_abf::numeric, 0)
           + COALESCE(meal.inc_lunch::numeric, 0)
           + COALESCE(meal.inc_dinner::numeric, 0)
    INTO meal_amount
    FROM sp_calc_meal_prices(v_mealtype_id, v_adult, v_child, 0, 0) meal;

meal_amount := COALESCE(meal_amount, 0);

SELECT COALESCE(SUM(
        fn_calc_inclusion_qty(
            1,
            COALESCE(v_adult, 0),
            COALESCE(v_child, 0),
            0,
            1,
            inc.charge_rate,
            inc.multiplier,
            inc.frequency,
            v_charge_date,
            v_charge_date + 1
        ) * inc.charge_rate::numeric
    ), 0)
    INTO inclusive_amount
    FROM sp_get_rate_inclusions(v_rateplan_id) inc
    WHERE inc.charge_rate > 0
      AND (
          v_daily_mode
          OR (NOT v_daily_mode AND inc.frequency = 1 AND v_charge_date <> v_arrival)
          OR (NOT v_daily_mode AND inc.frequency = 2 AND v_charge_date <> v_departure)
      );

net_room_rate := v_rate_amount::numeric - meal_amount - inclusive_amount;

IF v_min_rate IS NOT NULL AND v_rate_amount < v_min_rate THEN
        PERFORM sp_raise_error('10801',
            'Room rate lower than the minimum rate are not allowed (%s - %s, Amount = %s, Minimum = %s)',
            v_room_code,
            v_rate_code,
            v_rate_amount::text,
            v_min_rate::text
        );
    END IF;

IF net_room_rate < 0 THEN
        PERFORM sp_raise_error('10802',
            'Rate amount is too small for meals and inclusions (%s - %s, Rate = %s, Meal = %s, Inclusion = %s, Net = %s)',
            v_room_code,
            v_rate_code,
            v_rate_amount::text,
            meal_amount::text,
            inclusive_amount::text,
            net_room_rate::text
        );
    END IF;
END

sp_validate_rate_amount

Parameters

Definition