aonestar.public

Parameters

Name	Type	Mode
p_start_date	date	IN
p_end_date	date	IN
p_revenue_calc_type	integer	IN
date_ref	date	OUT
source	text	OUT
record_id	integer	OUT
booking_id	integer	OUT
booking_item	integer	OUT
register_id	integer	OUT
display_name	text	OUT
room_number	text	OUT
roomtype_id	integer	OUT
rateplan_id	integer	OUT
origin_id	integer	OUT
sales_id	integer	OUT
market_id	integer	OUT
channel_id	integer	OUT
status	text	OUT
arrival	date	OUT
departure	date	OUT
room_night	integer	OUT
revenue	numeric	OUT

Definition

WITH historical AS (
        SELECT
            rs.date_ref,
            'registration'::text AS source,
            rs.register_id AS record_id,
            rs.booking_id,
            NULL::integer AS booking_item,
            rs.register_id,
            rs.guest_name AS display_name,
            rs.room_number::text,
            rs.roomtype_id,
            rs.rateplan_id,
            rs.origin_id,
            rs.sales_id,
            rs.market_id,
            rs.channel_id,
            'O'::text AS status,
            rs.start_date AS arrival,
            rs.end_date AS departure,
            rs.in_house_qty::integer AS room_night,
            CASE p_revenue_calc_type
                WHEN 1 THEN (rs.in_house_qty + rs.day_used_qty) * COALESCE(rs.room_charge, rs.room_rate, 0)
                WHEN 2 THEN (rs.in_house_qty + rs.day_used_qty) * COALESCE(rs.revenue_room_net, rs.room_charge, rs.room_rate, 0)
                ELSE (rs.in_house_qty + rs.day_used_qty) * COALESCE(rs.room_rate, 0)
            END::numeric AS revenue
        FROM public.room_summary rs
        WHERE rs.date_ref BETWEEN p_start_date AND LEAST(p_end_date, fn_system_date() - 1)
          AND (rs.room_status = 'OCC' OR rs.day_used)
          AND (rs.in_house_qty > 0 OR rs.day_used_qty > 0)
    ), booking_days AS (
        SELECT
            d.date_ref,
            b.id AS booking_id,
            bi.item_id AS booking_item,
            bi.roomtype_id,
            bi.rateplan_id,
            b.origin_id,
            b.sales_id,
            b.market_id,
            b.channel_id,
            b.status::text,
            bi.arrival,
            bi.departure,
            (bi.qty - bi.inhouse_qty)::integer AS room_night,
            b.booking_name::text AS display_name
        FROM public.booking b
        JOIN public.booking_items bi ON bi.booking_id = b.id
        CROSS JOIN LATERAL (
            SELECT generate_series(
                GREATEST(p_start_date, fn_system_date(), bi.arrival),
                LEAST(p_end_date, bi.departure - 1), interval '1 day'
            )::date AS date_ref
        ) d
        WHERE b.status IN ('R', 'C', 'I')
          AND bi.qty - bi.inhouse_qty > 0
          AND GREATEST(p_start_date, fn_system_date(), bi.arrival)
              <= LEAST(p_end_date, bi.departure - 1)
    ), future_booking AS (
        SELECT
            bd.date_ref,
            'booking'::text AS source,
            bd.booking_item AS record_id,
            bd.booking_id,
            bd.booking_item,
            NULL::integer AS register_id,
            bd.display_name,
            NULL::text AS room_number,
            bd.roomtype_id,
            bd.rateplan_id,
            bd.origin_id,
            bd.sales_id,
            bd.market_id,
            bd.channel_id,
            bd.status,
            bd.arrival,
            bd.departure,
            bd.room_night,
            CASE p_revenue_calc_type
                WHEN 1 THEN bd.room_night * COALESCE(rb.room, 0)
                WHEN 2 THEN bd.room_night * COALESCE(rb.room_net, 0)
                ELSE bd.room_night * COALESCE(br.room_rate, 0)
            END::numeric AS revenue
        FROM booking_days bd
        LEFT JOIN public.booking_rates br
          ON br.booking_item = bd.booking_item AND br.charge_date = bd.date_ref
        LEFT JOIN LATERAL public.sp_booking_rates_breakdown(
            bd.date_ref, bd.booking_id, bd.booking_item, NULL, true
        ) rb ON true
    ), registration_days AS (
        SELECT
            d.date_ref,
            rg.id AS register_id,
            rg.booking_id,
            rg.booking_item,
            rg.guest_id,
            g.full_name::text AS display_name,
            rm.room_number::text,
            rm.roomtype_id,
            rg.rateplan_id,
            rg.origin_id,
            rg.sales_id,
            rg.market_id,
            rg.channel_id,
            rg.status::text,
            rg.arrival,
            rg.departure,
            rg.room_id,
            rg.day_used,
            rg.is_join_room,
            row_number() OVER (
                PARTITION BY d.date_ref, rg.room_id
                ORDER BY rg.is_join_room, rg.shared_register_id NULLS FIRST, rg.id
            ) AS room_seq
        FROM public.registration rg
        LEFT JOIN public.guest g ON g.id = rg.guest_id
        LEFT JOIN public.room rm ON rm.id = rg.room_id
        CROSS JOIN LATERAL (
            SELECT generate_series(
                GREATEST(p_start_date, fn_system_date(), rg.arrival),
                LEAST(p_end_date, rg.departure - 1), interval '1 day'
            )::date AS date_ref
        ) d
        WHERE rg.status IN ('I', 'O')
          AND NOT rg.day_used
          AND GREATEST(p_start_date, fn_system_date(), rg.arrival)
              <= LEAST(p_end_date, rg.departure - 1)
    ), future_registration AS (
        SELECT
            rd.date_ref,
            'registration'::text AS source,
            rd.register_id AS record_id,
            rd.booking_id,
            rd.booking_item,
            rd.register_id,
            rd.display_name,
            rd.room_number,
            rd.roomtype_id,
            rd.rateplan_id,
            rd.origin_id,
            rd.sales_id,
            rd.market_id,
            rd.channel_id,
            rd.status,
            rd.arrival,
            rd.departure,
            CASE WHEN rd.room_seq = 1 AND NOT rd.is_join_room THEN 1 ELSE 0 END AS room_night,
            CASE p_revenue_calc_type
                WHEN 1 THEN COALESCE(rb.room, 0)
                WHEN 2 THEN COALESCE(rb.room_net, 0)
                ELSE COALESCE(rr.room_rate, 0)
            END::numeric AS revenue
        FROM registration_days rd
        LEFT JOIN public.registration_rates rr
          ON rr.register_id = rd.register_id AND rr.charge_date = rd.date_ref
        LEFT JOIN LATERAL public.sp_rate_schedule_breakdown(
            rd.date_ref, NULL, rd.register_id, NULL, false
        ) rb ON true
    )
    SELECT * FROM historical
    UNION ALL SELECT * FROM future_booking
    UNION ALL SELECT * FROM future_registration

fn_occupancy_calendar_details

Parameters

Definition