Past Occupancy by Rate Category API

The Past Occupancy by Rate Category API allows the external vendor to collect past occupancy statistics by rate category. To enable this functionality, the third-party vendor makes the Past Occupancy by Rate Category API call providing basic information. In response, OPERA creates a session specific temporary table, temp_occupancy_data. The third-party vendor then fetches records from this table.

Important!

To prevent a compromise of system performance, it is not recommended to call the Past Occupancy API for a number of days into the past (in_past_days) that is greater than 45 within a PMS environment. (However, it is recognized that for the initial one-time extract, 365 days may be specified.) Specifying 1 day in the past for ORS environments would suffice as data earlier than this will never change.

Because of memory limitations, the APIs will not populate PL/SQL tables. Instead, a global temp table (temp_occupancy_data) is populated which needs to be queried to collect the data after the API call. See Table Structure for details.

Since the session specific global temp table will be populated, inform any other external vendors to not use any transaction specific command (rollback/commit) between the calling of API, querying of table, and fetching the data.

To ensure accuracy of the data and irrespective of the source of the reservation (e.g., manually input, sent from CRS, Web/Internet, etc.) it is imperative that all reservations/blocks have a rate code (for all nights of the stay), as unlike market code, rate code is not a compulsory column on the reservation or block tables. If reservations exist in the system without a rate code, OPERA will pass the extracted data via the API which may then contain records with null rate category values. It is therefore important to recognize the possibility of compromised data under such conditions.

Quality of data resulting from this API could be compromised if:

1. Rate codes are not mandatory for individual reservations.

2. Rate codes are not mandatory for group/block reservations.

3. User error when modifying a multi night stay reservation accidentally removes the rate code for a night or more.

4. Reservation pushed down from external system without rate code.

All dates will return a row for each configured rate category and a null/blank row. If a reservation does not have a rate code, thereby also not having an associated rate category, the statistics for this reservation will fall into the null/blank rate category row returned.

Procedure

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

occupancy.get_past_data

(in_resort,
in_from_date,
in_past_days,
in_type,
out_msg,
in_room_type default null);

Parameters

Parameter	Data Format
in_resort	VARCHAR2
in_from_date	Date
in_past_days	Number
in_type	VARCHAR2 default = 'TOTAL' valid values = 'TOTAL','RATECATEGORY'.
out_msg	OUT VARCHAR2
in_room_type	VARCHAR2 Comma separated list of valid OPERA room types. Null value will be treated as all room types.

IMPORTANT: If a reservation does not have a rate code, and therefor has no rate category associated with it, the API will return occupancy with a blank (null) rate category.

When the table has been created and populated, the out_msg is returned; if the table cannot be created and populated, a relevant error message is returned.

Example - Past Occupancy by Rate Category:

Fetch past data for property ODH by rate category from the business date for the past 45 days.

declare

vmsg VARCHAR2(200);

begin

occupancy.get_past_data ('ODH',
pms_p.business_date -1,
45,
'RATECATEGORY',
vmsg);

If vmsg = 'SUCCESS' then

--- query the temp_occupancy_data table.

end if;

end;

Table Structure

temp_occupancy_data

Field	Data Type	Notes
RESORT	VARCHAR2(20) NOT NULL	Property code
OCCUPANCY_DATE	Date NOT NULL	Date
PHYSICAL_ROOMS	Number	Physical rooms
OO_ROOMS	Number	Out of order rooms
OS_ROOMS	Number	Out of service rooms
ROOMS_SOLD	Number	Rooms sold
ROOM_REVENUE	Number	Room revenue
ROOM_ARRIVALS	Number	Arrival rooms
ROOM_DEPARTURES	Number	Departure rooms
TOTAL_REVENUE	Number	Total revenue
FOOD_REVENUE	Number	Food and beverage revenue
CANCELLED_ROOMS	Number	Cancelled rooms
NO_SHOW_ROOMS	Number	No show rooms
RES_TYPE	VARCHAR2	Reservation type
RATE_CATEGORY	VARCHAR2(20)	Rate category

Revenue values will be prepared and stored based on the number of decimals internally set. This may mean, for example, that in a 2-decimal environment, 12 decimal places might be exposed.

Revenue values extracted will be net revenues. The interpretation of "net" depends on whether the environment is PMS, ORS, or integrated PMS/ORS; and on the configuration of the environment.