aonestar.public

Parameters

Name	Type	Mode
update_data	jsonb	IN
booking_id	integer	IN
booking_item	integer	IN (DEFAULT NULL)
dates	date[]	IN (DEFAULT NULL)
room_rate	t_money	IN (DEFAULT NULL)
extra_adult	t_money	IN (DEFAULT NULL)
extra_child	t_money	IN (DEFAULT NULL)
extra_infant	t_money	IN (DEFAULT NULL)
mealtype_id	integer	IN (DEFAULT NULL)
rateplan_id	integer	IN (DEFAULT NULL)
p_validate	boolean	IN (DEFAULT true)
register_id	integer	IN
dates	date[]	IN (DEFAULT NULL)
room_rate	t_money	IN (DEFAULT NULL)
extra_adult	t_money	IN (DEFAULT NULL)
extra_child	t_money	IN (DEFAULT NULL)
extra_infant	t_money	IN (DEFAULT NULL)
mealtype_id	integer	IN (DEFAULT NULL)
rateplan_id	integer	IN (DEFAULT NULL)
p_validate	boolean	IN (DEFAULT true)

Definition

DECLARE
    item record;
    vchk record;
    _sqlstate TEXT;
    _detail   TEXT;
    _hint     TEXT;
    _context  TEXT;
    _msg_text TEXT;
    _def_mealtype int;
    _def_rateplan int;
BEGIN
    /*
     *  if "dates" is null, all dates in stay period will be updated
     *  Column room_rate, extra_adult, extra_child, extra_infant, comp, comp_abf will be skipped if their values are nulls
     */

-- Effective defaults of the registration (same fallback the readers use).
    -- Per-date values equal to these defaults are not overrides and must be
    -- stored as NULL (NULL = inherit from registration).
    SELECT COALESCE(rg.mealtype_id, rp.mealtype_id), rg.rateplan_id
    INTO _def_mealtype, _def_rateplan
    FROM registration rg
        LEFT JOIN rate_plan rp ON rp.id = rg.rateplan_id
    WHERE rg.id = $1;

-- Guard: primary-only followers have rates permanently zeroed by sp_checkin_split_folio
    -- Distribute followers may have their own rate schedule (share_rate=true)
    IF EXISTS(
        SELECT 1 FROM registration
        WHERE id = $1
          AND shared_register_id IS NOT NULL
          AND NOT share_rate
    ) THEN
        PERFORM sp_raise_error('20103', 'Cannot update room charge schedule for primary-only split-folio follower');
    END IF;

FOR item IN
        -- Query old rates by date
        WITH rg AS (
            SELECT fn_date_range(arrival, IIF(rg.arrival=rg.departure, rg.departure, rg.departure-1)) AS charge_date
            FROM registration rg
            WHERE (rg.id = $1)
        )
        SELECT rg.charge_date, /*to_char(rg.charge_date, 'Dy') AS dow,*/ rr.room_rate, rr.extrabed_rate, rr.child_rate, rr.infant_rate,
            rr.rateplan_id, rr.mealtype_id --rr.comp, rr.comp_abf
        FROM rg LEFT JOIN registration_rates rr ON rr.register_id = $1 AND rr.charge_date = rg.charge_date
        WHERE (dates IS NULL OR rg.charge_date = ANY(dates)) AND NOT rr.posted
    LOOP
        INSERT INTO registration_rates AS rr (register_id, charge_date, room_rate, extrabed_rate, child_rate, infant_rate, mealtype_id, rateplan_id)
        VALUES($1, item.charge_date,
               COALESCE($3, item.room_rate), COALESCE($4, item.extrabed_rate), COALESCE($5, item.child_rate),
               COALESCE($6, item.infant_rate),
			   iif($7 is distinct from -1, NULLIF($7, _def_mealtype), item.mealtype_id), --COALESCE($7, item.mealtype_id),
			   iif($8 is not null, NULLIF($8, _def_rateplan), item.rateplan_id))
        ON CONFLICT ON CONSTRAINT pk_registration_rates --(register_id, charge_date)
        DO UPDATE
           SET room_rate     = excluded.room_rate,
               extrabed_rate = excluded.extrabed_rate,
               child_rate    = excluded.child_rate,
               infant_rate   = excluded.infant_rate,
               mealtype_id   = excluded.mealtype_id,
               rateplan_id   = excluded.rateplan_id;
    END LOOP;

-- Validate the room-rate amounts actually saved (skipped for channel/system imports)
    IF p_validate THEN
        FOR vchk IN
            SELECT rr.charge_date, rr.room_rate,
                   COALESCE(rr.rateplan_id, rg.rateplan_id) AS rateplan_id,
                   rm.roomtype_id,
                   COALESCE(rr.mealtype_id, rg.mealtype_id) AS mealtype_id,
                   rg.adult, rg.child
            FROM registration_rates rr
                JOIN registration rg ON rg.id = rr.register_id
                LEFT JOIN room rm ON rm.id = rg.room_id
            WHERE rr.register_id = $1
              AND NOT rr.posted
              AND (dates IS NULL OR rr.charge_date = ANY(dates))
        LOOP
            PERFORM sp_validate_rate_amount(
                vchk.rateplan_id, vchk.roomtype_id, vchk.room_rate,
                mealtype_id := vchk.mealtype_id, adult := vchk.adult, child := vchk.child,
                charge_date := vchk.charge_date);
        END LOOP;
    END IF;

RETURN fn_result_success();
EXCEPTION
    WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        _sqlstate = RETURNED_SQLSTATE,
        _msg_text = MESSAGE_TEXT,
        _detail   = PG_EXCEPTION_DETAIL,
        _hint     = PG_EXCEPTION_HINT,
        _context  = PG_EXCEPTION_CONTEXT;
    RETURN fn_handle_error(_sqlstate, _msg_text, _detail, _hint, _context, 'sp_update_room_charge_schedule',
                jsonb_build_object(
                    'register_id', register_id,
                    'dates', dates,
                    'room_rate', room_rate,
                    'extra_adult', extra_adult,
                    'extra_child', extra_child,
                    'extra_infant', extra_infant,
                    'mealtype_id', mealtype_id,
                    'rateplan_id', rateplan_id)
           );
END