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 summary AS (
        SELECT room_id, room_number, room_status, hk_status, roomtype_id, bedtype_id, location_id, 
            building_id, exposure_id, register_id, booking_id, book_type, share_room, house_used, walk_in, comp, day_used, 
            rateplan_id, stay_type, start_date, end_date, in_house_qty, day_used_qty, adult, child, infant, extra_adult, extra_child, 
            extra_infant, room_rate, extra_adult_rate, extra_child_rate, extra_infant_rate, promotion_id, channel_id, origin_id, 
            sales_id, market_id, rsvtype_id, advance_book_period, 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, mealtype_id, room_charge, rate_breakdown, fn_get_guest_revenues_js(register_id) as revenue_breakdown
        FROM sp_get_room_summary($1, true)
    )
    INSERT INTO room_summary(date_ref, room_id, room_number, room_status, hk_status, roomtype_id, bedtype_id, location_id, 
        building_id, exposure_id, register_id, booking_id, book_type, share_room, house_used, walk_in, comp, day_used, 
        rateplan_id, stay_type, start_date, end_date, in_house_qty, day_used_qty, adult, child, infant, extra_adult, extra_child, 
        extra_infant, room_rate, extra_adult_rate, extra_child_rate, extra_infant_rate, promotion_id, channel_id, origin_id, 
        sales_id, market_id, rsvtype_id, advance_book_period, 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, mealtype_id, room_charge, rate_breakdown, revenue_breakdown)
    select $1, * FROM summary
    ON CONFLICT (date_ref, register_id, guest_id) DO NOTHING;

    GET DIAGNOSTICS rec_count = ROW_COUNT;  
    status = 'success';
    msg = rec_count::TEXT||' record generated';
END

sp_update_room_summary

Parameters

Definition