aonestar.public

Parameters

Name	Type	Mode
i_register_id	integer	IN
i_user	text	IN (DEFAULT NULL)

Definition

DECLARE
    v_user        text := coalesce(i_user, fn_current_user());
    v_reg         record;
    v_target      integer;
    v_target_stat char(1);
    v_room_no     text;
    v_guest_name  text;
    v_ipu_payload jsonb;
BEGIN
    CALL sp_check_endday_process();
    CALL sp_check_record_lock(v_user, register_id := i_register_id);

SELECT coalesce(r.shared_register_id, r.id) INTO v_target
    FROM registration r WHERE r.id = i_register_id;

PERFORM sp_error_if(v_target IS NULL, '20227', 'Registration not found or not in-house.');

PERFORM 1 FROM registration r WHERE r.id = v_target FOR UPDATE;

SELECT r.* INTO v_reg FROM registration r WHERE r.id = i_register_id;

PERFORM sp_error_if(v_reg.status <> 'I', '20227', 'Registration not found or not in-house.');

-- ยุบได้เฉพาะ primary-only follower เท่านั้น: ตัว distribute มีค่าห้องของตัวเอง
    -- การยุบจะทำให้ยอดค่าห้องรวมของห้องหาย
    PERFORM sp_error_if(v_reg.shared_register_id IS NULL OR v_reg.share_rate,
        '20232', 'This registration is not a split-folio guest of another room.');

SELECT r.status INTO v_target_stat FROM registration r WHERE r.id = v_target;
    PERFORM sp_error_if(coalesce(v_target_stat, 'X') <> 'I',
        '20227', 'Registration not found or not in-house.');

-- folio ตัวเองต้องยังว่าง (void แล้วไม่นับ เหมือนเงื่อนไขใน sp_cancel_checkin)
    PERFORM sp_error_if(EXISTS (
        SELECT 1 FROM transactions t
        WHERE t.folio_id = v_reg.folio_id
          AND coalesce(t.post_flag, '') <> 'D'
    ), '20231', 'Cannot merge: transactions have been posted to this folio.');

IF ipu.is_active() THEN
        v_ipu_payload := ipu.build_guest_payload(i_register_id);
    END IF;

-- คืนแขกกลับเป็น more guest ของห้องต้นทาง (ทั้งตัวเองและ more guest ที่ติดมาด้วย)
    INSERT INTO register_guests (register_id, guest_id)
    VALUES (v_target, v_reg.guest_id)
    ON CONFLICT DO NOTHING;

INSERT INTO register_guests (register_id, guest_id)
    SELECT v_target, rgs.guest_id
    FROM register_guests rgs
    WHERE rgs.register_id = i_register_id
    ON CONFLICT DO NOTHING;

UPDATE booking_guests bg
    SET register_id = v_target
    WHERE bg.register_id = i_register_id;

DELETE FROM deposit            WHERE register_id = i_register_id;
    DELETE FROM guest_request      WHERE register_id = i_register_id;
    DELETE FROM registration_rates WHERE register_id = i_register_id;
    -- folio_items / folio_details ตามไปด้วย FK ON DELETE CASCADE
    DELETE FROM folio              WHERE register_id = i_register_id;
    -- registration row ยังอยู่ (status = X) จึงต้องเก็บกวาด register_guests เอง
    DELETE FROM register_guests    WHERE register_id = i_register_id;

-- แยกสอง statement ตาม sp_cancel_checkin: ต้องคง booking_item ไว้ตอน I->X
    -- ให้ avail trigger เห็นก่อน แล้วค่อยตัด FK ที่ค้าง
    UPDATE registration SET status = 'X', folio_id = NULL WHERE id = i_register_id;
    UPDATE registration SET booking_item = NULL WHERE id = i_register_id;

-- ไม่เหลือ follower แล้ว ห้องนี้กลับเป็นห้องปกติ
    IF NOT EXISTS (
        SELECT 1 FROM registration r
        WHERE r.shared_register_id = v_target AND r.status = 'I'
    ) THEN
        UPDATE registration SET split_folio = FALSE WHERE id = v_target;
    END IF;

PERFORM sp_update_registration_summary(v_target);

v_room_no    := fn_room_number(v_reg.room_id);
    v_guest_name := (SELECT g.full_name::text FROM guest g WHERE g.id = v_reg.guest_id);

PERFORM sp_log_inhouse(v_target, 'Merge folio',
        format('Merge register#%s (%s) back to this room',
               i_register_id, coalesce(v_guest_name, v_reg.guest_id::text)), v_user);
    PERFORM sp_log_inhouse(i_register_id, 'Merge folio',
        format('Merged back to register#%s, room %s', v_target, v_room_no), v_user);

IF v_ipu_payload IS NOT NULL AND ipu.is_enabled('guest.check_out') THEN
        PERFORM ipu.post_queue('guest.check_out', v_room_no, v_ipu_payload);
    END IF;

RETURN jsonb_build_object(
        'register_id'       , v_target,
        'merged_register_id', i_register_id,
        'guest_id'          , v_reg.guest_id
    );
END

sp_merge_inhouse_guest_folio

Description

Parameters

Definition