aonestar.public

Parameters

Name	Type	Mode
register_id	integer	OUT
booking_id	integer	OUT
room_number	text	OUT
room_type	text	OUT
arrival	date	OUT
departure	date	OUT
guest_name	text	OUT
group_name	text	OUT
channel_name	text	OUT
rate_plan	text	OUT
actual_rate_total	t_money	OUT
booking_rate_total	t_money	OUT
standard_rate_total	t_money	OUT
booking_rate_diff	boolean	OUT
standard__rate_diff	boolean	OUT
checkin_user	text	OUT
checkin_time	timestamp with time zone	OUT
approved	boolean	OUT
approve_user	text	OUT
approve_time	timestamp with time zone	OUT

Definition

WITH SYS AS (
        SELECT fn_system_date() AS system_date
    ), RG AS (
        SELECT rg.id AS register_id, rg.booking_id, rm.room_number, rm.roomtype_id, rt.name AS roomtype, 
           rg.arrival, rg.departure, COALESCE(rg.rate_id, rd.rate_id) AS rate_id, guest_id, channel_id, rg.group_id, rg.rateplan_id, 
           rg.approved, rg.approve_user, rg.approve_time, rg.checkin_user, rg.checkin_time, 
           sp_get_registration_summary_js(rg.id) AS actual_rate_details,
           sp_get_checkin_summary_js(rg.booking_item, rg.room_id) AS booking_rate_details,
           0.0 AS standard_rate_total
--           (SELECT sum(rate_amount) 
--            FROM sp_inventory_rates(start_date => rg.arrival, end_date => rg.departure-1, i_roomtype_id => rm.roomtype_id, i_rateplan_id => rg.rateplan_id)
--           ) AS  standard_rate_total
        FROM registration AS RG
            LEFT join room rm ON rm.id = rg.room_id 
            LEFT join room_type rt ON rt.id = rm.roomtype_id
            LEFT join rate_details rd ON rd.rateplan_id = rg.rateplan_id AND rd.roomtype_id = rm.roomtype_id , sys 
        WHERE rg.status = 'I' AND (arrival = sys.system_date OR NOT rg.approved)  
    )
    SELECT rg.register_id, rg.booking_id, rg.room_number, rg.roomtype, rg.arrival, rg.departure, g.full_name, grp."name", cn."name", rp.name, 
        (rg.actual_rate_details->>'total_room_charge')::t_money AS actual_rate_total,
        (rg.booking_rate_details->>'total_room_charge')::t_money AS booking_rate_total, 
        standard_rate_total::t_money,      
        (rg.actual_rate_details->>'total_room_charge')::t_money <> (rg.booking_rate_details->>'total_room_charge')::t_money AS booking_rate_diff,  
        (rg.actual_rate_details->>'total_room_charge')::t_money <> standard_rate_total AS standard_rate_diff, 
        rg.checkin_user, rg.checkin_time, rg.approved, rg.approve_user, rg.approve_time--,
    FROM RG 
        LEFT JOIN guest g ON g.id = rg.guest_id
        LEFT JOIN channel cn ON cn.id = rg.channel_id 
        LEFT JOIN group_info grp ON rg.group_id = grp.id 
        LEFT JOIN rate_plan rp ON rp.id = rg.rateplan_id

sp_rate_discrepancy

Parameters

Definition