aonestar.public

Parameters

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

Definition

DECLARE rec_count int = 0;
BEGIN
    adate = coalesce(adate, fn_system_date());

    WITH pickup AS (
        SELECT rp.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
        FROM sp_get_rsv_pickup($1, 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)
    select $1, * FROM pickup
    ON CONFLICT (date_ref, booking_item) DO NOTHING;

    GET DIAGNOSTICS rec_count = ROW_COUNT;  
    status = 'success';
    msg = rec_count::TEXT||' record generated';
END

sp_update_rsv_pickup

Parameters

Definition