aonestar.public

Parameters

Name	Type	Mode
src_booking_id	integer	IN
arrival	date	IN
departure	date	IN
new_guest	jsonb	IN (DEFAULT NULL)
user_name	text	IN (DEFAULT NULL)
result_code	text	OUT
result_msg	text	OUT
msg_code	text	OUT
out_booking_id	integer	OUT

Definition

DECLARE
    booking_data jsonb;
    new_details  jsonb;
    v_state      TEXT;
    v_detail     TEXT;
    v_hint       TEXT;
    v_context    TEXT;
    v_result     json;
    -- availability check
    _detail_row  record;
    v_avail_code text;
    v_avail_msg  text;
    v_avail_mc   text;
BEGIN
    -- Load source booking (with requests, without logs/summary/deposits)
    booking_data := sp_get_booking_js(
        src_booking_id,
        include_logs     := false,
        include_requests := true,
        include_summary  := false,
        include_deposit  := false
    )::jsonb;

IF booking_data IS NULL THEN
        result_code := '4';
        result_msg  := sys_msg('10003', 'Booking not found');
        msg_code    := '10003';
        RETURN;
    END IF;

-- Replace primary guest if provided
    IF new_guest IS NOT NULL THEN
        booking_data := booking_data || jsonb_build_object(
            'guest_id', new_guest->'id',
            'guests',   jsonb_build_array(new_guest)
        );
    END IF;

-- Update each detail: new arrival/departure, clear rate_schedule (force re-fetch from inventory)
    -- Also strip requirement IDs so sp_save_guest_requests INSERTs fresh rows
    SELECT jsonb_agg(d || jsonb_build_object(
        'arrival',       arrival,
        'departure',     departure,
        'rate_schedule', null,
        'requirements',  COALESCE(
            (SELECT jsonb_agg(req - 'id') FROM jsonb_array_elements(d->'requirements') AS req),
            '[]'::jsonb
        )
    ))
    FROM jsonb_array_elements(booking_data->'details') d
    INTO new_details;

-- Check room availability for each detail before creating booking
    FOR _detail_row IN SELECT val FROM jsonb_array_elements(new_details) AS val LOOP
        SELECT avail.result_code, avail.result_msg, avail.msg_code
        INTO v_avail_code, v_avail_msg, v_avail_mc
        FROM sp_check_room_available(
            (_detail_row.val->>'roomtype_id')::int,
            arrival,
            departure,
            coalesce((_detail_row.val->>'qty')::int, 1)
        ) avail;

IF v_avail_code = '4' THEN
            result_code := '4';
            result_msg  := v_avail_msg;
            msg_code    := v_avail_mc;
            RETURN;
        END IF;
    END LOOP;

-- Reset booking-level fields, set new dates
    -- Also strip requirement IDs so sp_save_guest_requests INSERTs fresh rows
    booking_data := booking_data || jsonb_build_object(
        'booking_id',         null,
        'confirm_no',         null,
        'folio_id',           null,
        'voucher_no',         null,
        'arrival',            arrival,
        'departure',          departure,
        'book_date',          null,
        'channel_booking_id', null,
        'cancel_user',        null,
        'cancel_time',        null,
        'confirm_user',       null,
        'confirm_time',       null,
        'confirm_remark',     null,
        'create_user',        null,
        'create_time',        null,
        'modify_user',        null,
        'modify_time',        null,
        'deposits',           '[]'::jsonb,
        'details',            new_details,
        'requirements',       COALESCE(
            (SELECT jsonb_agg(req - 'id') FROM jsonb_array_elements(booking_data->'requirements') AS req),
            '[]'::jsonb
        )
    );

SELECT * FROM sp_create_booking(booking_data, user_name)
    INTO result_code, result_msg, msg_code, out_booking_id;

-- Update booking log: prefix "New booking" → "New booking (duplicate from RSV# X)"
    -- Use regexp_replace to preserve any text appended after "New booking"
    IF result_code = '0' THEN
        UPDATE booking_log
        SET log_text = regexp_replace(
            log_text,
            '^New booking',
            format('New booking (duplicated from RSV# %s)', src_booking_id)
        )
        WHERE id = (
            SELECT id FROM booking_log
            WHERE booking_id = out_booking_id AND log_type = 'New Booking'
            ORDER BY id DESC LIMIT 1
        );
    END IF;

EXCEPTION
    WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        v_state    = RETURNED_SQLSTATE,
        result_msg = MESSAGE_TEXT,
        v_detail   = PG_EXCEPTION_DETAIL,
        v_hint     = PG_EXCEPTION_HINT,
        v_context  = PG_EXCEPTION_CONTEXT;
    v_result    := fn_handle_error(v_state, result_msg, v_detail, v_hint, v_context, 'sp_duplicate_booking',
                       jsonb_build_object('src_booking_id', src_booking_id));
    result_code := v_result->>'result_code';
    result_msg  := v_result->>'result_msg';
    msg_code    := v_result->>'msg_code';
END