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.
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, please 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.
In order 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. In the event that one or more 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. In the event that 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.
This API will populate a temporary table (session specific) which must then be queried to fetch the required data.
occupancy.get_past_data
Parameter |
Data Format |
in_resort |
VARCHAR2 |
in_from_date |
Date |
in_past_days |
Number |
in_type |
VARCHAR2 |
out_msg |
OUT VARCHAR2 |
in_room_type |
VARCHAR2 |
IMPORTANT: In the event that 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.
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;
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.