aonestar.public

Parameters

Name	Type	Mode
i_roomtype_id	integer	IN
i_arrival	date	IN
i_departure	date	IN
i_qty	integer	IN
i_adult	integer	IN
i_child	integer	IN (DEFAULT 0)
i_infant	integer	IN (DEFAULT 0)
i_book_date	date	IN (DEFAULT NULL)
i_booking_item	integer	IN (DEFAULT NULL)
i_channel_id	integer	IN (DEFAULT NULL)
i_origin_id	integer	IN (DEFAULT NULL)
i_market_id	integer	IN (DEFAULT NULL)
i_rate_type	character	IN (DEFAULT 'D')
rate_id	integer	OUT
rateplan_id	integer	OUT
plan_type	smallint	OUT
name	text	OUT
description	text	OUT
mealtype_id	integer	OUT
mealtype_name	text	OUT
rate_amount	t_money	OUT
include_adult	smallint	OUT
include_child	smallint	OUT
include_infant	smallint	OUT
extra_adult	smallint	OUT
extra_child	smallint	OUT
extra_infant	smallint	OUT
no_amend	boolean	OUT
no_cancel	boolean	OUT
no_refund	boolean	OUT
origin_id	integer	OUT
market_id	integer	OUT
total_amount	t_money	OUT
is_open_rate	boolean	OUT
rate_multiple	boolean	OUT
is_invalid	boolean	OUT
rate_schedule	json	OUT

Definition

DECLARE 
    v_night int;
    v_adv_period int;
    sys_date date = fn_system_date();
    mapped_rateplans int[];
BEGIN
    i_child  := coalesce(i_child, 0);
    i_infant := coalesce(i_infant, 0);

    IF (i_booking_item < 1) THEN
        i_booking_item := null; 
    END IF;

    IF i_arrival < sys_date THEN 
        i_arrival := sys_date;
    END IF; 

    IF (i_book_date IS NULL) AND (i_booking_item IS NOT NULL) THEN
        i_book_date := (
                        SELECT b.book_date 
                        FROM booking_items bi LEFT join booking b ON b.id = bi.booking_id
                        WHERE bi.item_id = i_booking_item
                      );
    END IF;

    IF i_channel_id is not null THEN
        mapped_rateplans := ARRAY(select rc.rateplan_id from rate_plan_channels rc where rc.channel_id = i_channel_id);
    END IF;

    i_book_date := coalesce(i_book_date, sys_date);
    v_night       := i_departure - i_arrival;
    v_adv_period  := i_arrival - i_book_date;
    v_night       := IIF(v_night = 0, 1, v_night);  

    RETURN QUERY
    WITH inv AS (
        SELECT iv.rate_id, iv.rate_date, iv.rate_amount, iv.stop_sell, iv.cta, iv.ctd, iv.min_stay, iv.max_stay, iv.available, iv.enabled
        FROM sp_inventory_rates(
                i_arrival, 
                IIF(i_arrival = i_departure, i_departure, i_departure-1), 
                i_roomtype_id, 
                allow_inactive_rates => false
             ) AS iv
        WHERE available = TRUE
    ), Rates AS (
        SELECT rd.rate_id, rd.rateplan_id, rd.name, rd.description, 
            ex.include_adult, ex.include_child, ex.include_infant,    
            ex.extra_adult, ex.extra_child, ex.extra_infant,    
            rd.no_amend, rd.no_cancel, rd.no_refund, rd.origin_id, rd.market_id,
            rd.rate_extra_adult, rd.rate_extra_child, rd.rate_extra_infant, rp.comp, rp.comp_abf,
            rp.mealtype_id, m.name as mealtype_name,
            rp.plan_type, rp.order_seq   
        FROM v_rate_details rd 
            LEFT JOIN rate_plan rp ON rp.id = rd.rateplan_id 
            LEFT JOIN meal_type m ON m.id = rp.mealtype_id,
            LATERAL sp_calc_extra(rd.rateplan_id, rd.roomtype_id, i_adult, i_child, i_infant) ex
        WHERE rd.enabled 
            AND (rp.rate_type = i_rate_type)
            AND (rd.roomtype_id = i_roomtype_id)
            AND (rd.book_start_date IS NULL OR i_book_date >= rd.book_start_date) 
            AND (rd.book_end_date IS NULL   OR i_book_date <= rd.book_end_date) 
            AND (rd.stay_start_date IS NULL OR i_arrival >= rd.stay_start_date) 
            AND (rd.stay_end_date IS NULL   OR i_departure <= rd.stay_end_date) 
            --AND night BETWEEN COALESCE(rd.min_stay,0) AND COALESCE(rd.max_stay,9999999) 
            -- Add by T.Supol 2025-07-30
            and (rp.origin_id is null or rp.origin_id = i_origin_id)
            and (rp.market_id is null or rp.market_id = i_market_id)
            -----------------------------
            AND v_adv_period BETWEEN COALESCE(rd.min_adv_book, 0) AND COALESCE(rd.max_adv_book, 9999999)  
            AND i_adult  <= coalesce(rd.max_adult, 9999)
            AND i_child  <= coalesce(rd.max_child, 9999)
            AND i_infant <= coalesce(rd.max_infant, 9999)
            AND (i_adult + i_child) <= coalesce(rd.max_pax, 9999)
            AND (coalesce(rp.mapped_channels,0) = 0 
                 or (i_channel_id is null and rp.mapped_channels = 0)
                 or (i_channel_id is not null and rp.id = any(mapped_rateplans))
                )
        --ORDER BY rd.order_seq, rd.rate_id   
    ), Schedule AS (
        SELECT R.rate_id, 
            sum(coalesce(p.rate_amount, 0)
                + coalesce(r.extra_adult * r.rate_extra_adult, 0) 
                + coalesce(r.extra_child * r.rate_extra_child, 0)
            ) as total_amount, 
            json_agg(json_build_object(
                'date',              p.rate_date, 
                'amount',            p.rate_amount,
                'extra_adult_rate',  r.rate_extra_adult,
                'extra_child_rate',  r.rate_extra_child,
                'extra_infant_rate', r.rate_extra_infant,
                'comp',              r.comp,
                'comp_abf',          r.comp_abf,
                'mealtype_id' ,      null,
                'mealtype_name' ,    null,
                'total',      coalesce(p.rate_amount, 0) 
                                     + coalesce(r.extra_adult * r.rate_extra_adult, 0) 
                                     + coalesce(r.extra_child * r.rate_extra_child, 0)
             )               
            ) AS rate_schedule,
            bool_or(
                 NOT available OR NOT enabled OR p.stop_sell
                 OR NOT (v_night BETWEEN COALESCE(p.min_stay, 0) AND COALESCE(p.max_stay, 9999999)) 
                 OR (COALESCE(p.cta, FALSE) AND p.rate_date = i_arrival)
                 OR (COALESCE(p.ctd, FALSE) AND p.rate_date = i_departure)
            ) AS restricted,
            bool_or(r.plan_type in (1,2) and (p.rate_amount is null or p.rate_amount < 0)) as is_invalid
        FROM Rates R LEFT JOIN inv AS P ON p.rate_id = R.rate_id --, LATERAL sp_get_rate_schedule(R.rate_id, $2, $3, false) S
        GROUP BY R.rate_id
    )
    SELECT 
          R.rate_id
        , R.rateplan_id
        , R.plan_type
        , R.name
        , R.description
        , R.mealtype_id
        , R.mealtype_name
        , (S.rate_schedule->0->>'amount')::t_money AS rate_amount
        , R.include_adult, R.include_child, R.include_infant
        , R.extra_adult, R.extra_child, R.extra_infant
        , R.no_amend
        , R.no_cancel
        , R.no_refund
        , R.origin_id
        , R.market_id
        , IIF(R.plan_type = 9, null, S.total_amount)::t_money
        , R.plan_type = 9
        , (Select count(distinct amount) 
           from json_to_recordset(S.rate_schedule) as rs(amount t_money)
          ) > 1 AS rate_multiple  
        , S.is_invalid
        , S.rate_schedule
    FROM Rates R INNER JOIN Schedule S ON S.rate_id = R.rate_id
    WHERE NOT S.restricted
    ORDER BY IIF(R.plan_type < 5, 1, R.plan_type), order_seq;
END

sp_get_available_rates

Parameters

Definition