aonestar.public

Parameters

Name	Type	Mode
adate	date	IN (DEFAULT NULL)
user_name	text	IN (DEFAULT NULL)
p_booking_id	integer	IN (DEFAULT NULL)
status	text	OUT
msg	text	OUT

Definition

-- OUT param 'status' would conflict with rsv_pickup.status in ON CONFLICT predicate
#variable_conflict use_column
DECLARE rec_count int = 0;
BEGIN
    IF p_booking_id IS NOT NULL THEN
        SELECT CASE WHEN b.status = 'X' THEN b.cancel_date
                    WHEN b.status = 'N' THEN b.arrival
                    ELSE COALESCE(b.rebook_date, b.book_date)
               END INTO adate
        FROM booking b WHERE b.id = p_booking_id;

DELETE FROM rsv_pickup
        WHERE booking_id = p_booking_id
          AND rsv_pickup.status IN ('NEW', 'RBK');

WITH pickup AS (
            SELECT rp.status, 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 => p_booking_id) rp
        )
        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 adate, * FROM pickup
        ON CONFLICT (date_ref, booking_item, status) WHERE status <> 'AMD' DO NOTHING;

ELSE
        adate = COALESCE(adate, fn_system_date());

WITH pickup AS (
            SELECT rp.status, 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(adate, true) rp
        )
        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 adate, * FROM pickup
        ON CONFLICT (date_ref, booking_item, status) WHERE status <> 'AMD' DO NOTHING;
    END IF;

GET DIAGNOSTICS rec_count = ROW_COUNT;
    status = 'success';
    msg    = rec_count::TEXT || ' record generated';
END

sp_update_rsv_pickup

Parameters

Definition