aonestar.public

Parameters

Name	Type	Mode
p_system_date	date	IN
p_user_name	text	IN (DEFAULT NULL)

Definition

DECLARE
    v_booking_id    int;
    v_book_status   char(1);
    v_noshow_date   date;
    v_source_id     bigint;
    v_source_status text;
    v_source_date   date;
BEGIN
    FOR v_booking_id IN (
        SELECT DISTINCT booking_id FROM booking_log
        WHERE log_date = p_system_date AND log_type = 'Amend Booking'
    )
    LOOP
        SELECT status, noshow_date INTO v_book_status, v_noshow_date FROM booking WHERE id = v_booking_id;
        IF v_book_status IS NULL OR v_book_status NOT IN ('R', 'C') THEN
            CONTINUE;
        END IF;

-- booking ที่เคยผ่าน no-show ไม่เก็บ AMD stat อีก
        IF v_noshow_date IS NOT NULL THEN
            CONTINUE;
        END IF;

-- New Booking / Rebook / No Show today means those rows are already authoritative
        IF EXISTS (
            SELECT 1 FROM booking_log
            WHERE booking_id = v_booking_id
              AND log_date   = p_system_date
              AND log_type   IN ('New Booking', 'Rebook', 'No Show')
        ) THEN
            CONTINUE;
        END IF;

-- Find source: most recently inserted positive row (highest id)
        -- room_qty > 0 excludes AMD OLD rows (negative) inserted in earlier amend passes
        SELECT id, status, date_ref
        INTO v_source_id, v_source_status, v_source_date
        FROM rsv_pickup
        WHERE booking_id = v_booking_id
          AND status IN ('NEW', 'AMD', 'RBK', 'RBN')
          AND room_qty > 0
        ORDER BY id DESC
        LIMIT 1;

-- Warn when booking was amended but has no prior rsv_pickup rows (unusual state)
        IF v_source_id IS NULL THEN
            PERFORM sp_log_warning(
                'sp_update_rsv_pickup_amend',
                format('booking_id=%s amended on %s but has no prior rsv_pickup rows (no AMD OLD will be inserted)',
                       v_booking_id, p_system_date),
                notify_admin => true
            );
        END IF;

-- AMD OLD: negate all positive rows of the same source batch (status + date_ref)
        -- v_source_id IS NULL skips only this block; AMD NEW still runs below
        IF v_source_id IS NOT NULL THEN
            INSERT INTO rsv_pickup(
                date_ref, status, booking_id, booking_item,
                roomtype_id, bedtype_id, book_type, house_used, comp, day_used,
                rateplan_id, mealtype_id, stay_type, arrival, departure,
                adult, child, infant, extra_adult, extra_child, extra_infant,
                room_rate, extra_adult_rate, extra_child_rate, extra_infant_rate,
                room_charge, extra_charge, other_charge, total_charge,
                promotion_id, channel_id, origin_id, sales_id, market_id, rsvtype_id,
                lead_time, return_guest, guest_id, guest_sex, guest_age,
                guest_nation_id, guest_lang_id, guest_country_id, guest_country_res_id,
                guest_city_id, guest_occupation_id, guest_vip_id,
                guest_name, guest_member_code, room_qty)
            SELECT
                p_system_date, 'AMD', rp.booking_id, rp.booking_item,
                rp.roomtype_id, rp.bedtype_id, rp.book_type, rp.house_used, rp.comp, rp.day_used,
                rp.rateplan_id, rp.mealtype_id, rp.stay_type, rp.arrival, rp.departure,
                -rp.adult, -rp.child, -rp.infant, -rp.extra_adult, -rp.extra_child, -rp.extra_infant,
                rp.room_rate, rp.extra_adult_rate, rp.extra_child_rate, rp.extra_infant_rate,
                -rp.room_charge, -rp.extra_charge, -rp.other_charge, -rp.total_charge,
                rp.promotion_id, rp.channel_id, rp.origin_id, rp.sales_id, rp.market_id, rp.rsvtype_id,
                rp.lead_time, rp.return_guest, rp.guest_id, rp.guest_sex, rp.guest_age,
                rp.guest_nation_id, rp.guest_lang_id, rp.guest_country_id, rp.guest_country_res_id,
                rp.guest_city_id, rp.guest_occupation_id, rp.guest_vip_id,
                rp.guest_name, rp.guest_member_code, -rp.room_qty
            FROM rsv_pickup rp
            WHERE rp.booking_id = v_booking_id
            AND rp.status     = v_source_status
            AND rp.date_ref   = v_source_date
            AND rp.room_qty   > 0;
        END IF;  -- v_source_id IS NOT NULL

-- AMD NEW: current booking state
        INSERT INTO rsv_pickup(
            date_ref, status, booking_id, booking_item,
            roomtype_id, bedtype_id, book_type, house_used, comp, day_used,
            rateplan_id, mealtype_id, stay_type, arrival, departure,
            adult, child, infant, extra_adult, extra_child, extra_infant,
            room_rate, extra_adult_rate, extra_child_rate, extra_infant_rate,
            room_charge, extra_charge, other_charge, total_charge,
            promotion_id, channel_id, origin_id, sales_id, market_id, rsvtype_id,
            lead_time, return_guest, guest_id, guest_sex, guest_age,
            guest_nation_id, guest_lang_id, guest_country_id, guest_country_res_id,
            guest_city_id, guest_occupation_id, guest_vip_id,
            guest_name, guest_member_code, room_qty)
        SELECT
            p_system_date, 'AMD',
            rp.booking_id, rp.booking_item,
            rp.roomtype_id, rp.bedtype_id, rp.book_type, rp.house_used, rp.comp, rp.day_used,
            rp.rateplan_id, rp.mealtype_id, rp.stay_type, rp.arrival, rp.departure,
            rp.adult, rp.child, rp.infant, rp.extra_adult, rp.extra_child, rp.extra_infant,
            rp.room_rate, rp.extra_adult_rate, rp.extra_child_rate, rp.extra_infant_rate,
            rp.room_charge, rp.extra_charge, rp.other_charge, rp.total_charge,
            rp.promotion_id, rp.channel_id, rp.origin_id, rp.sales_id, rp.market_id, rp.rsvtype_id,
            rp.lead_time, rp.return_guest, rp.guest_id, rp.guest_sex, rp.guest_age,
            rp.guest_nation_id, rp.guest_lang_id, rp.guest_country_id, rp.guest_country_res_id,
            rp.guest_city_id, rp.guest_occupation_id, rp.guest_vip_id,
            rp.guest_name, rp.guest_member_code, rp.room_qty
        FROM sp_get_rsv_pickup(p_booking_id => v_booking_id) rp
        WHERE rp.status IN ('NEW', 'RBK', 'RBN');

END LOOP;
END