aonestar.public

Parameters

Name	Type	Mode
i_start_date	date	IN
i_end_date	date	IN
i_roomtype_id	integer	IN (DEFAULT NULL)
i_roomgroup_id	integer	IN (DEFAULT NULL)
avail_date	date	OUT
roomtype_id	integer	OUT
roomtype_code	citext	OUT
roomtype_name	text	OUT
roomtype_seq	smallint	OUT
roomgroup_id	integer	OUT
rsv	integer	OUT
block	integer	OUT
inh	integer	OUT
ooo	integer	OUT
avail	integer	OUT
total	integer	OUT
stop_sell	boolean	OUT

Definition

WITH base AS (
        SELECT fn_date_range($1, $2) as avail_date, id AS roomtype_id, code, name,
            group_id AS roomgroup_id, order_seq, total_rooms
        FROM room_type
        WHERE (i_roomtype_id IS NULL OR id = i_roomtype_id) AND (i_roomgroup_id IS NULL OR group_id = i_roomgroup_id)
    ), av AS (
        SELECT base.avail_date, base.roomtype_id, roomgroup_id, order_seq, total_rooms, code, name,
            sum(iif(status='RSV', qty, 0)::int) AS rsv,
            sum(iif(status='BLK', qty, 0)::int) AS block,
            sum(iif(status='INH', qty, 0)::int) AS inh,
            sum(iif(status='OOO', qty, 0)::int) AS ooo,
            coalesce(rs.stop_sell, false) AS stop_sell
        FROM base
            LEFT JOIN room_usage ra ON ra.use_date = base.avail_date AND ra.roomtype_id = base.roomtype_id
            LEFT JOIN room_restriction rs ON rs.effect_date = base.avail_date AND rs.roomtype_id  = base.roomtype_id
        GROUP BY base.avail_date, base.roomtype_id, roomgroup_id, order_seq, total_rooms, code, name, stop_sell
    )
    SELECT avail_date, roomtype_id, code, name, order_seq, roomgroup_id, rsv, block, inh, ooo,
           (total_rooms + rsv + block + inh + ooo) AS avail, total_rooms::int, stop_sell
    FROM av
    ORDER BY avail_date, order_seq

sp_get_room_available

Parameters

Definition