aonestar.public

Parameters

Name	Type	Mode
p_booking_item	integer	IN

Definition

WITH params AS (
        SELECT COALESCE(extra_adult, 0) > 0 AS has_extra_adult,
               COALESCE(extra_child,  0) > 0 AS has_extra_child,
               mealtype_id                   AS bi_mealtype_id
        FROM booking_items WHERE item_id = p_booking_item
    ),
    rng AS (
        SELECT charge_date, room_rate, extrabed_rate, child_rate, infant_rate,
               COALESCE(br.mealtype_id, p.bi_mealtype_id) AS eff_mealtype_id,
               row_number() OVER (ORDER BY charge_date)
               - row_number() OVER (
                   PARTITION BY room_rate, extrabed_rate, child_rate, infant_rate,
                                COALESCE(br.mealtype_id, p.bi_mealtype_id)
                   ORDER BY charge_date
               ) AS grp
        FROM booking_rates br, params p
        WHERE booking_item = p_booking_item
    ),
    ranges AS (
        SELECT min(charge_date) AS from_date, max(charge_date) AS to_date,
               room_rate, extrabed_rate, child_rate,
               (SELECT name FROM meal_type WHERE id = min(eff_mealtype_id)) AS mt_name
        FROM rng
        GROUP BY grp, room_rate, extrabed_rate, child_rate, infant_rate, eff_mealtype_id
    )
    SELECT string_agg(
        format('      %s - %s: %s%s%s, %s',
            to_char(from_date, 'DD Mon YY'),
            to_char(to_date,   'DD Mon YY'),
            room_rate,
            CASE WHEN p.has_extra_adult THEN ', ExtraAdult: ' || extrabed_rate ELSE '' END,
            CASE WHEN p.has_extra_child  THEN ', ExtraChild: ' || child_rate   ELSE '' END,
            COALESCE(mt_name, 'No ABF')
        ),
        E'\n' ORDER BY from_date
    )
    FROM ranges, params p

fn_format_rate_schedule

Parameters

Definition