aonestar.public

Parameters

Name	Type	Mode
folio_id	integer	IN
user_name	text	IN (DEFAULT NULL)
include_members	boolean	IN (DEFAULT false)
status	text	OUT
msg	text	OUT

Definition

DECLARE
    _folio_id       int;
    f               record;
    m               record;
    trans_ids       int[];
    void_result     json;
    ctb_reg_ids     int[];
    room_reg_ids    int[];
    SYS_DATE        date := fn_system_date();
    shift           text;
    v_member_status text;
    v_member_msg    text;
    member_msgs     text[] := '{}';
    _sqlstate TEXT; _detail TEXT; _hint TEXT; _context TEXT; _msg_text TEXT;
    v_result json;
BEGIN
    _folio_id := folio_id;
    user_name := COALESCE(user_name, fn_current_user());
    shift     := COALESCE(fn_work_shift(user_name), fn_online_shift());
    status    := 'success';

SELECT f2.folio_type, f2.register_id, f2.status AS folio_status
    FROM folio f2
    WHERE f2.id = _folio_id
    INTO f;

IF NOT FOUND OR f.folio_status <> 'A' THEN
        status := 'error';
        msg    := format('Folio %s not found or not active', _folio_id);
        RETURN;
    END IF;

/* ═══ folio_type = 'R' : delegate to existing sp_cancel_auto_post ═══ */
    IF f.folio_type = 'R' THEN
        void_result := sp_cancel_auto_post(f.register_id, user_name);
        msg := void_result->>'result_msg';
        IF void_result->>'result_code' = '4' THEN
            -- 30502 = nothing to cancel: not an error
            status := IIF(void_result->>'msg_code' = '30502', 'success', 'error');
        ELSE
            status := 'success';
        END IF;
        RETURN;
    END IF;

/* ═══ folio_type = 'G' : void AUTO transactions in group folio + reset flags ═══ */
    IF f.folio_type <> 'G' THEN
        status := 'error';
        msg    := format('Folio %s: unsupported folio_type ''%s''', _folio_id, f.folio_type);
        RETURN;
    END IF;

trans_ids := ARRAY(
        SELECT t.id FROM transactions t
        WHERE t.folio_id = _folio_id AND t.post_date = SYS_DATE
          AND t.post_flag IN ('R','A') AND t.active
    );

IF fn_length(trans_ids) > 0 THEN
        void_result := sp_void_transactions(trans_ids, 'Cancel auto-post', user_name, shift, force_void := true);

IF void_result->>'result_code' = '4' THEN
            status := 'error';
            msg    := void_result->>'result_msg';
            RETURN;
        END IF;

msg := format('%s transactions voided', fn_length(trans_ids));
    ELSE
        msg := sys_msg('30502', 'No auto-post transaction');
    END IF;

/* Always reset CTB flags regardless of whether there were transactions to
       void. This preserves complimentary-room behavior while leaving member
       own-account posts untouched unless include_members is requested. */
    ctb_reg_ids := ARRAY(
        SELECT rg.id
        FROM registration rg
            JOIN booking bk ON bk.id = rg.booking_id AND bk.folio_id = _folio_id
        WHERE rg.status = 'I'
          AND rg.charge_to_booking
    );

/* Reset registration_rates for CTB members */
    UPDATE registration_rates rr
    SET posted = FALSE, inclusive_posted = FALSE, tran_id = NULL
    WHERE rr.register_id = ANY(ctb_reg_ids) AND rr.charge_date = SYS_DATE;

/* Reset room_posted for CTB members */
    UPDATE registration rg
    SET room_posted = FALSE
    WHERE rg.id = ANY(ctb_reg_ids);

/* Reset charge_schedule: CTB requirements + per-booking requirements */
    UPDATE charge_schedule cs
    SET posted = FALSE, tran_id = NULL
    WHERE cs.charge_date = SYS_DATE AND cs.posted = TRUE
      AND (
          (cs.register_id = ANY(ctb_reg_ids) AND cs.charge_to_booking)
          OR (cs.booking_id IN (
                  SELECT bk.id FROM booking bk WHERE bk.folio_id = _folio_id
              ) AND cs.register_id IS NULL)
      );

/* ── include_members: cancel each in-house member's own-account folio ── */
    IF include_members THEN
        FOR m IN
            SELECT DISTINCT rg.folio_id AS member_folio_id
            FROM registration rg
                JOIN booking bk ON bk.id = rg.booking_id AND bk.folio_id = _folio_id
            WHERE rg.status = 'I'
            ORDER BY rg.folio_id
        LOOP
            SELECT s.status, s.msg
            FROM sp_cancel_auto_post_folio(m.member_folio_id, user_name, false) s
            INTO v_member_status, v_member_msg;

IF v_member_status = 'error' THEN
                member_msgs := array_append(member_msgs,
                    format('Member folio %s: %s', m.member_folio_id, v_member_msg));
            END IF;
        END LOOP;

IF array_length(member_msgs, 1) > 0 THEN
            status := 'error';
            msg    := concat_ws(E'\n', msg,
                'Member errors: '||array_to_string(member_msgs, '; '));
        END IF;
    END IF;

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;
    v_result := fn_handle_error(_sqlstate, _msg_text, _detail, _hint, _context, 'sp_cancel_auto_post_folio',
                    jsonb_build_object('folio_id', folio_id));
    status := 'error';
    msg    := v_result->>'result_msg';
END;