aonestar.public

Parameters

Name	Type	Mode
date_ref	date	IN (DEFAULT NULL)
recalculate	boolean	IN (DEFAULT false)
status	text	OUT
booking_id	integer	OUT
booking_item	integer	OUT
roomtype_id	integer	OUT
bedtype_id	integer	OUT
book_type	character	OUT
house_used	boolean	OUT
comp	boolean	OUT
day_used	boolean	OUT
rateplan_id	integer	OUT
mealtype_id	integer	OUT
stay_type	character	OUT
arrival	date	OUT
departure	date	OUT
adult	smallint	OUT
child	smallint	OUT
infant	smallint	OUT
extra_adult	smallint	OUT
extra_child	smallint	OUT
extra_infant	smallint	OUT
room_rate	t_money	OUT
extra_adult_rate	t_money	OUT
extra_child_rate	t_money	OUT
extra_infant_rate	t_money	OUT
room_charge	t_money	OUT
extra_charge	t_money	OUT
other_charge	t_money	OUT
total_charge	t_money	OUT
promotion_id	integer	OUT
channel_id	integer	OUT
origin_id	integer	OUT
sales_id	integer	OUT
market_id	integer	OUT
rsvtype_id	integer	OUT
lead_time	integer	OUT
return_guest	boolean	OUT
guest_id	integer	OUT
guest_sex	character	OUT
guest_age	integer	OUT
guest_nation_id	integer	OUT
guest_lang_id	integer	OUT
guest_country_id	integer	OUT
guest_country_res_id	integer	OUT
guest_city_id	integer	OUT
guest_occupation_id	integer	OUT
guest_vip_id	integer	OUT
guest_name	text	OUT
guest_member_code	text	OUT

Definition

DECLARE 
    system_date DATE = fn_system_date();
    def_extra_adult t_money;
    def_extra_child t_money;
    def_extra_infant t_money;

BEGIN
    date_ref = COALESCE(date_ref, system_date);
    def_extra_adult := fn_sys_param('ROOM_RATE', 'EXTRA_ADULT', 0.0);
    def_extra_child := fn_sys_param('ROOM_RATE', 'EXTRA_CHILD', 0.0);
    def_extra_infant:= fn_sys_param('ROOM_RATE', 'EXTRA_INFANT', 0.0);

    IF (date_ref = system_date) OR recalculate THEN 
        RETURN query
        SELECT IIF(b.status = 'X', 'CXL', 'NEW') as status,
            bi.booking_id,
            bi.item_id,
            bi.roomtype_id,
            bi.bedtype_id,
            b.book_type,
            (rp.plan_type = 6) as house_used,
            (rp.plan_type = 5) as comp,
            (bi.arrival = bi.departure) as day_used,
            bi.rateplan_id,  --10
            bi.mealtype_id,
            b.stay_type,
            bi.arrival,
            bi.departure,
            bi.adult,
            bi.child,
            bi.infant,
            bi.extra_adult,
            bi.extra_child,
            bi.extra_infant, --20
            br.room_rate,
            coalesce(br.extrabed_rate, rd.rate_extrabed, rp.rate_extrabed, def_extra_adult) as extra_adult_rate,
            coalesce(br.child_rate, rd.rate_child, rp.rate_child, def_extra_child) as extra_child_rate,
            coalesce(br.infant_rate, rd.rate_infant, rp.rate_infant, def_extra_infant) as extra_infant_rate,
            bi.room_charge,
            bi.extra_charge,
            bi.other_charge,
            bi.total_charge,
            bi.promotion_id,
            b.channel_id, --30
            b.origin_id,
            b.sales_id,
            b.market_id,
            b.rsvtype_id,
            b.arrival-b.book_date as lead_time,
            (g.last_departure NOTNULL AND g.last_departure < b.arrival) AS return_guest,
            b.guest_id,
            g.sex as guest_sex,
            EXTRACT(YEAR FROM age($1, g.birthdate))::int as guest_age,
            g.nation_id as guest_nation_id, --40
            g.lang_id as guest_lang_id,
            g.country_id as guest_country_id,
            g.country_res_id as guest_country_res_id,
            g.city_id as guest_city_id,
            g.occupation_id as guest_occupation_id,
            g.vip_id as guest_vip_id,
            g.full_name::text as guest_name,
            g.member_code::text as guest_member_code
        FROM booking_items bi 
            left join booking b on b.id = bi.booking_id
            left join rate_plan rp on rp.id = bi.rateplan_id
            left join rate_details rd on rd.rate_id = bi.rate_id 
            left join guest g on g.id = b.guest_id
            left join booking_rates br on br.booking_item = bi.item_id and br.charge_date = bi.arrival
        WHERE (b.status in ('R','C') and b.book_date = $1) 
               or (b.status = 'X' and b.cancel_date = $1 and b.book_date <> b.cancel_date);

    ELSE
        RETURN query
        SELECT p.status, p.booking_id, p.booking_item, p.roomtype_id, p.bedtype_id, p.book_type, p.house_used, p.comp, p.day_used, 
            p.rateplan_id, p.mealtype_id, p.stay_type, p.arrival, p.departure, p.adult, p.child, p.infant, p.extra_adult, p.extra_child, 
            p.extra_infant, p.room_rate, p.extra_adult_rate, p.extra_child_rate, p.extra_infant_rate, p.room_charge, p.extra_charge, 
            p.other_charge, p.total_charge, p.promotion_id, p.channel_id, p.origin_id, p.sales_id, p.market_id, p.rsvtype_id, 
            p.lead_time, p.return_guest, p.guest_id, p.guest_sex, p.guest_age, p.guest_nation_id, p.guest_lang_id, p.guest_country_id, 
            p.guest_country_res_id, p.guest_city_id, p.guest_occupation_id, p.guest_vip_id, p.guest_name, p.guest_member_code
        FROM rsv_pickup p
        WHERE p.date_ref = $1;
    END IF;
END

sp_get_rsv_pickup

Parameters

Definition