aonestar.public

Parameters

Name	Type	Mode
register_id	integer	OUT
room_number	t_room_number	OUT
roomtype_id	integer	OUT
room_type	text	OUT
roomtype_code	text	OUT
arrival	date	OUT
departure	date	OUT
guest_name	text	OUT
group_name	text	OUT
channel_name	text	OUT
booking_id	integer	OUT
booking_item	integer	OUT
booking_rate_code	text	OUT
booking_rate_plan	text	OUT
booking_rate_total	t_money	OUT
actual_rate_code	text	OUT
actual_rate_plan	text	OUT
actual_rate_total	t_money	OUT
rate_diff	t_money	OUT
approved	boolean	OUT
approve_user	text	OUT
approve_time	timestamp with time zone	OUT
checkin_user	text	OUT
checkin_time	timestamp with time zone	OUT
upgrade_type	text	OUT

Definition

SELECT rg.id AS register_id, rm.room_number, rm.roomtype_id, 
           rt.name AS room_type, rt.code as roomtype_code,
           rg.arrival, rg.departure, g.full_name AS guest_name, 
           grp."name" AS group_name, cn."name" AS channel_name, 
           rg.booking_id, rg.booking_item,
           bp.code as booking_rate_code, 
           bp.name as booking_rate_plan, 
           bi.total_charge as booking_rate_total,
           rp.code as actual_rate_code, 
           rp.name as actual_rate_plan, 
           rg.total_charge as actual_rate_total,  
           rg.total_charge-bi.total_charge as rate_diff,
           rg.approved, rg.approve_user, rg.approve_time, rg.checkin_user, rg.checkin_time,
           (select upgrade_type from room_upgrade ru where ru.register_id = rg.id order by ru.id desc limit 1) as upgrade_type             
    FROM registration AS RG
        LEFT JOIN booking bk on bk.id = rg.booking_id
        LEFT JOIN booking_items bi on bi.item_id = rg.booking_item
        LEFT JOIN room rm ON rm.id = rg.room_id 
        LEFT JOIN room_type rt ON rt.id = rm.roomtype_id
        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   
        LEFT JOIN rate_plan bp ON bp.id = bi.rateplan_id    
    WHERE rg.status = 'I' AND (rg.arrival = fn_system_date() OR NOT rg.approved)
    ORDER BY rm.room_number, rg.id

sp_get_rate_approval

Parameters

Definition