aonestar.public

Parameters

Name	Type	Mode
start_date	date	IN
end_date	date	IN
i_roomtype_id	integer	IN (DEFAULT NULL)
i_rateplan_id	integer	IN (DEFAULT NULL)
i_rate_id	integer	IN (DEFAULT NULL)
allow_inactive_rates	boolean	IN (DEFAULT false)
allow_special_rates	boolean	IN (DEFAULT true)
rate_id	integer	OUT
rate_date	date	OUT
roomtype_id	integer	OUT
rateplan_id	integer	OUT
rateplan_seq	integer	OUT
rateplan_name	text	OUT
description	text	OUT
link_plan_id	integer	OUT
link_description	text	OUT
editable	boolean	OUT
available	boolean	OUT
enabled	boolean	OUT
rate_amount	t_money	OUT
stop_sell	boolean	OUT
cta	boolean	OUT
ctd	boolean	OUT
min_stay	smallint	OUT
max_stay	smallint	OUT
extra_adult	t_money	OUT
extra_child	t_money	OUT
extra_infant	t_money	OUT
comp	boolean	OUT
comp_abf	boolean	OUT
comp_lunch	boolean	OUT
comp_dinner	boolean	OUT
room_charge_dept	integer	OUT
plan_type	smallint	OUT
has_active_child	boolean	OUT
min_rate	t_money	OUT

Definition

DECLARE 
    def_rate_amount t_money;
    def_extra_adult t_money;
    def_extra_child t_money;
    def_extra_infant t_money;
BEGIN
    def_extra_adult  := fn_sys_param('ROOM_RATE', 'EXTRA_ADULT', 0.0)::t_money;
    def_extra_child  := fn_sys_param('ROOM_RATE', 'EXTRA_CHILD', 0.0)::t_money;
    def_extra_infant := fn_sys_param('ROOM_RATE', 'EXTRA_INFANT', 0.0)::t_money;  

    RETURN query 
    WITH RECURSIVE plan AS (
        WITH rates AS (
            SELECT d.rate_id, d.rate_date,
                d.roomtype_id, d.rateplan_id, p.plan_type, p.order_seq AS rateplan_seq,
                p.link_plan_id IS NULL AS editable, p.enabled, 
                p.link_plan_id, p.link_adjust_type, p.link_adjust_percent, p.link_adjust_amt,
                p.name, p.description,
                COALESCE(d.rate_extrabed, p.rate_extrabed, def_extra_adult) AS extra_adult,
                COALESCE(d.rate_child, p.rate_child, def_extra_child) AS extra_child,
                COALESCE(d.rate_infant, p.rate_infant, def_extra_infant) AS extra_infant,
                p.days,
                p.origin_id,
                p.market_id,
                p.no_amend, p.no_cancel,  p.no_refund,
                --d.include_adult, d.include_child, d.include_infant,
                d.max_adult, d.max_child, d.max_infant, d.max_pax,
                p.min_stay, p.max_stay, p.min_adv_book, p.max_adv_book,
                p.book_start_date, p.book_end_date,
                p.stay_start_date, p.stay_end_date,
                p.room_charge_dept, 
                d.rate_amount AS original_rate, 
                rt.room_rate AS default_rate,
                p.enabled AND (p.stay_start_date IS NULL OR d.rate_date >= p.stay_start_date) AND (p.stay_end_date IS NULL OR d.rate_date <= p.stay_end_date) AS available,
                FALSE AS comp, FALSE AS comp_abf, FALSE AS comp_lunch, FALSE AS comp_dinner,
                p.has_active_child, coalesce(p.min_rate, rt.min_rate) as min_rate 
            FROM (SELECT fn_date_range($1, $2) AS rate_date, dtl.* 
                  FROM rate_details dtl) AS d 
                LEFT JOIN rate_plan p ON p.id = d.rateplan_id 
                LEFT JOIN room_type rt ON rt.id = d.roomtype_id
            WHERE allow_special_rates OR p.plan_type in (1,2)
        )
        SELECT r.rate_id, r.rate_date, r.roomtype_id, r.rateplan_id, r.rateplan_seq, r.name, r.description, 
            r.link_plan_id, ''::text AS link_description, r.editable, r.available, r.enabled, 
            CASE 
                WHEN R.plan_type = 9 then NULL
                WHEN R.plan_type <> 1 then 0.0
                WHEN R.available AND (r.days[extract(dow from r.rate_date)+1] = false) THEN 
                    coalesce(daily.rate_amount, R.default_rate)
                WHEN R.available THEN 
                    coalesce(daily.rate_amount, R.original_rate, R.default_rate)
                ELSE R.default_rate 
            END::t_money AS rate_amount, 
            COALESCE(daily.stop_sell, FALSE) AS stop_sell, 
            COALESCE(daily.cta, FALSE) AS cta,
            COALESCE(daily.ctd, FALSE) AS ctd,
            COALESCE(daily.min_stay, r.min_stay) AS min_stay, 
            COALESCE(daily.max_stay, r.max_stay) AS max_stay,
            R.extra_adult, R.extra_child, R.extra_infant, R.comp, R.comp_abf, R.comp_lunch, 
            R.comp_dinner, R.room_charge_dept, R.plan_type, R.has_active_child, R.min_rate 
        FROM rates R 
            LEFT JOIN rate_daily daily ON daily.rate_id = R.rate_id AND daily.rate_date = R.rate_date
        WHERE R.link_plan_id IS NULL
        UNION
        SELECT r.rate_id, r.rate_date, r.roomtype_id, r.rateplan_id, r.rateplan_seq, r.name, r.description, r.link_plan_id, 
            fn_rate_link_description(link.name, R.link_adjust_type, R.link_adjust_percent, R.link_adjust_amt) AS link_description,
            r.editable, r.available, r.enabled, 
--            CASE WHEN R.available 
--                THEN fn_calc_link_rate(link.rate_amount, R.link_adjust_type::int, R.link_adjust_percent::t_percent, R.link_adjust_amt, r.rate_date, r.days) 
--                ELSE NULL 
--            END::t_money AS rate_amount,
            CASE WHEN R.available 
                THEN fn_calc_link_rate(link.rate_amount, R.link_adjust_type::int, R.link_adjust_percent::t_percent, R.link_adjust_amt, r.rate_date, r.days) 
                ELSE R.default_rate  
            END::t_money AS rate_amount,
            COALESCE(daily.stop_sell, FALSE) AS stop_sell, 
            COALESCE(daily.cta, FALSE) AS cta,
            COALESCE(daily.ctd, FALSE) AS ctd,
            COALESCE(daily.min_stay, r.min_stay) AS min_stay, 
            COALESCE(daily.max_stay, r.max_stay) AS max_stay,
            R.extra_adult, R.extra_child, R.extra_infant, R.comp, R.comp_abf, R.comp_lunch, 
            R.comp_dinner, R.room_charge_dept, R.plan_type, R.has_active_child, R.min_rate   
--            (CASE WHEN R.available THEN COALESCE(daily.stop_sell, FALSE) ELSE NULL END) AS stop_sell, 
--            (CASE WHEN R.available THEN COALESCE(daily.cta, FALSE) ELSE NULL END) AS cta, 
--            (CASE WHEN R.available THEN COALESCE(daily.ctd, FALSE) ELSE NULL END)  AS ctd, 
--            (CASE WHEN R.available THEN COALESCE(daily.min_stay, r.min_stay) ELSE NULL END) AS min_stay, 
--            (CASE WHEN R.available THEN COALESCE(daily.max_stay, r.max_stay) ELSE NULL END) AS max_stay
        FROM rates R 
            LEFT JOIN rate_daily daily ON daily.rate_id = R.rate_id AND daily.rate_date = R.rate_date
            JOIN plan AS link ON link.rateplan_id = R.link_plan_id AND link.roomtype_id = R.roomtype_id AND link.rate_date = R.rate_date
        WHERE R.link_plan_id IS NOT NULL
    )
    SELECT * 
    FROM plan p
    WHERE (allow_inactive_rates OR p.enabled) 
        AND ($3 IS NULL OR p.roomtype_id = $3) 
        AND ($4 IS NULL OR p.rateplan_id = $4) 
        AND ($5 IS NULL OR p.rate_id = $5);
    --ORDER BY roomtype_id, rateplan_seq, rateplan_id, rate_date
END

sp_inventory_rates

Parameters

Definition