IDeaS V5i Occupancy API

IDeaS, a third-party external interface used for Yield or Revenue Management, is connected to OPERA PMS through direct calls to OPERA's IDeaS API. The V5i Occupancy API allows the V5i client to collect certain occupancy data. To enable this functionality, IDeaS makes the V5i Occupancy API call providing this information. In response, OPERA creates a session specific temporary table containing the details.

Procedures

Procedure 1

This API will populate a temporary table (session specific) which must then be queried to fetch the required data.

temp_occupancy_data

(in_resort VARCHAR,
in_from_date DATE,
in_number_of_days NUMBER,
in_type VARCHAR,
out_msg VARCHAR,
in_res_type VARCHAR,
in_rate_category VARCHAR,
in_room_type VARCHAR,
in_populate_market_yn VARCHAR)

Example 1

Fetch future data, but will exclude rows from output when both market and rate category are populated.

DECLARE

v_message varchar2(2000);

BEGIN

occupancy.get_future_data(pms_p.resort,pms_p.business_date,1,'RATECATEGORY',v_message,null,'Y');

dbms_output.put_line(v_message);

END;

Example 2

Fetch past data, but will exclude rows from output when both market and rate category are populated.

DECLARE

v_message varchar2(2000);

BEGIN

occupancy.get_past_data(pms_p.resort,pms_p.business_date-1,1,'RATECATEGORY',v_message,null,'Y');

dbms_output.put_line(v_message);

END;

Note: When the IDeaS data extraction client passes a room type list using the in_room_type parameter, all requested data applicable to the specified room types will be sent, such as capacities, out of order counts, rooms sold counts, arrival and departure counts, associated revenues, no show and cancellation counts, etc.

Table Structure

temp_v5i_occupancy

Field	Data Type	Notes
RESORT	VARCHAR2(20)	Property this record belongs to.
OCCUPANCY_DATE	DATE	Considered occupancy date.
RATE_CATEGORY	VARCHAR2(20)	Rate Category.
PHYSICAL_ROOMS	NUMBER	Physical rooms of the Room Type for the date.
OO_ROOMS	NUMBER	Out of order room count for the date.
OS_ROOMS	NUMBER	Out of service room count for the date.
ROOMS_SOLD	NUMBER	Rooms sold for the date.
ROOM_REVENUE	NUMBER	Room revenue for the date.
ROOM_ARRIVALS	NUMBER	Arrival rooms count for the date.
ROOM_DEPARTURES	NUMBER	Departure rooms count for the date.
TOTAL_REVENUE	NUMBER	Total revenue for the date.
FOOD_REVENUE	NUMBER	Food revenue for the date.
CANCELLED_ROOMS	NUMBER	Cancelled rooms count for the date.
NO_SHOW_ROOMS	NUMBER	No show rooms count for the date.
RES_TYPE	VARCHAR2(20)	Reservation type.
MARKET_CODE	VARCHAR2(20)	Market Code.

Procedure 2

This API will populate a temporary table (session specific) which must then be queried to fetch the required data.

temp_v5i_occupancy

(in_resort VARCHAR,
in_from_date DATE,
in_number_of_days NUMBER,
in_type VARCHAR,
out_msg VARCHAR,
in_market_code VARCHAR,
in_room_type VARCHAR,
in_group_by_room_type VARCHAR)

Example 1

Fetch future occupancy data.

DECLARE

s varchar2(2000);

BEGIN

v5i_occupancy.get_future_occupancy( in_resort => pms_p.resort,
in_from_date => pms_p.business_date,
in_number_of_days => 1,
in_type => 'MARKET',
out_msg => s,
in_market_code => null,
in_room_type => '',
in_group_by_room_type_yn => 'Y');

IF s!='SUCCESS' THEN
dbms_output.put_line('ERROR: '||s);
END IF;

END;

Example 2

Fetch past occupancy data.

DECLARE

s varchar2(2000);

BEGIN

v5i_occupancy.get_future_occupancy( in_resort => pms_p.resort,
in_from_date => pms_p.business_date-1,
in_number_of_days => 1,
in_type => 'MARKET',
out_msg => s,
in_market_code => null,
in_room_type => '',
in_group_by_room_type_yn => 'Y');

IF s!='SUCCESS' THEN
dbms_output.put_line('ERROR: '||s);
END IF;

END;

Note: When the IDeaS data extraction client passes a room type list using the in_room_type parameter, all requested data applicable to the specified room types will be sent, such as capacities, out of order counts, rooms sold counts, arrival and departure counts, associated revenues, no show and cancellation counts, etc.

Table Structure

temp_v5i_occupancy

Field	Data Type	Notes
RESORT	VARCHAR2(20)	Property this record belongs to.
OCCUPANCY_DATE	DATE	Considered occupancy date.
MARKET_CODE	VARCHAR2(20)	Market Segment Code.
PHYSICAL_ROOMS	NUMBER	Physical rooms of the hotel for the date.
OO_ROOMS	NUMBER	Out of order room count for the date.
OS_ROOMS	NUMBER	Out of service room count for the date.
ROOMS_SOLD	NUMBER	Rooms sold for the date.
ROOM_REVENUE	NUMBER	Room revenue for the date.
ROOM_ARRIVALS	NUMBER	Arrival rooms count for the date.
ROOM_DEPARTURES	NUMBER	Departure rooms count for the date
TOTAL_REVENUE	NUMBER	Total revenue for the date.
FOOD_REVENUE	NUMBER	Food revenue for the date.
CANCELLED_ROOMS	NUMBER	Cancelled rooms count for the date.
NO_SHOW_ROOMS	NUMBER	No show rooms count for the date.
RES_TYPE	VARCHAR2(20)	Reservation type for the record
ROOM_TYPE	VARCHAR2(20)	Stores the room type associated with the combination of market code, reservation type, etc. Note: Room Types that are configured as Can Be Meeting and are non-yieldable, will not be included.

Procedure 3

This API will populate a temporary table (session specific) which must then be queried to fetch the required data.

temp_v5i_resv

(in_resort VARCHAR,
in_from_date DATE,
in_past_days NUMBER,
out_msg VARCHAR)

Example 1

Fetch future reservations data.

DECLARE

s varchar2(100);

BEGIN

v5i_occupancy.get_future_resv(pms_p.resort,pms_p.business_date,1,s);

IF s!='SUCCESS' THEN

dbms_output.put_line('ERROR: '||s);

END IF;

END;

Example 2

Fetch past reservations data.

DECLARE

s varchar2(100);

BEGIN

v5i_occupancy.get_past_resv(pms_p.resort,pms_p.business_date-1,1,s);

IF s!='SUCCESS' THEN

dbms_output.put_line('ERROR: '||s);

END IF;

END;

Table Structure

temp_v5i_resv

Field	Data Type	Notes
CONFIRMATION_NO	NUMBER	Reservation Confirmation Number.
RESV_STATUS	VARCHAR2(20)	Reservation Status.
SHARED_YN	VARCHAR2(1)	Share indicator.
SHARERS_LIST	VARCHAR2(2000)	Confirmation Number List of the sharers.
TRX_DATE	DATE	Transaction date of the reservation.
ARRIVAL	DATE	Reservation arrival date.
DEPARTURE	DATE	Reservation departure date.
CHECKED_OUT_DATE	DATE	Reservation checked out date.
CANCELLATION_DATE	DATE	Reservation cancellation date.
BOOKING_DATE	DATE	Reservation booking date.
RATE_CODE	VARCHAR2(20)	Rate code of the reservation.
RATE_AMOUNT	NUMBER	Rate amount of the reservation.
MARKET_CODE	VARCHAR2(20)	Market code of the reservation.
ROOM	VARCHAR2(20)	Room Number. Note: Rooms that have been configured as Can Be Units and are non-yieldable will not be included.
ROOM_REVENUE	NUMBER	Room Revenue.
FB_REVENUE	NUMBER	Food and Beverage Revenue.
OTHER_REVENUE	NUMBER	Other Revenue.
TOTAL_REVENUE	NUMBER	Total revenue for the reservation.
ROOM_TYPE	VARCHAR2(20)	Stores the room type associated with the reservation at the current moment. Note: Room Types that are configured as Can Be Meeting and are non-yieldable, will not be included.
SOURCE_CODE	VARCHAR2(20)	Reservation source code.
CHANNEL	VARCHAR2(40)	Reservation channel code.
BOOKED_ROOM_TYPE	VARCHAR2(20)	Stores the room type associated with the reservation at the moment of booking.
NATIONALITY	VARCHAR2(20)	Nationality code.
RESV_TYPE	VARCHAR2(20)	Reservation Type (Booking Type).
CHILDREN	NUMBER	Number of children.
ADULTS	NUMBER	Number of adults.
RESV_NAME_ID	NUMBER	Indicates Reservation Unique ID.