|
|
|
IDeaS V5i Past Occupancy and Future Occupancy API
This document describes the two APIs that meet the requirements of Sections 2.1 through 2.4 of the document titled IDeaS V5i Integration Interface Data Requirements. Specifically, these APIs can be used to create the following extracts as described in the IDeaS requirements.
IDeaS Requirement |
Description |
API Variable |
Current Total Extract (Sec. 2.1) |
Future Occupancy by Property |
TOTAL |
Current Segment Extract (Sec. 2.2) |
Future Occupancy by Market |
MARKET |
Current Res Type Extract |
Future Occupancy by Res Type |
RESTYPE |
Current Res Type by Segment Extract |
Future Occupancy by Res Type by Market |
RESTYPEMARKET |
Current Res Type by Rate Category Extract |
Future Occupancy by Res Type by Rate Category |
RESTYPE_RTCAT |
Past Total Extract (Sec. 2.3) |
Past Occupancy by Property |
TOTAL |
Past Segment Extract (Sec. 2.4) |
Past Occupancy by Market |
MARKET |
Note: Because of memory limitations, the API will not populate PL/SQL tables. Instead, a global temp table (temp_v5i_occupancy) 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, do not use any transaction specific command (rollback/commit) between the calling of API, querying of table, and fetching the data.
With the possibility of an extract being created based on Res Type and the fact that these res types will be of both a Deduct and a Non Deduct nature, we are expecting that IDeaS would be ignoring those res types which although sent as part of the extract are of no purpose in the IDeaS calibration of data.
When input data includes component-room room types, the values of affected variables are based on physical room counts except when the results provide a breakdown by room types, in which case each individual room type (including component-room room types) is counted separately. For example, assume room type COMP2 is a component-room room type consisting of 1 TK type physical room and 1 TD type physical room.
If the extract covers one reservation that is for 1 COMP2 room type, the following are the resulting variable values:
v5i_occupancy.get_future_occupancy
in_type TOTAL |
Resulting Values |
Room Type = NULL |
Rooms Sold 2, Arrival 1, Rev 700 |
Room Type = COMP2 |
Rooms Sold 1, Arrival 1, Rev 700 |
Room Type = TD |
Rooms Sold 1, Arrival 0, Rev 0 |
Room Type = TK |
Rooms Sold 1, Arrival 0, Rev 0 |
Room Type = COMP2, TK, TD |
Rooms Sold 3, Arrival 1, Rev 700 |
in_type MARKET |
Resulting Values |
Room Type = NULL |
Rooms Sold 2, Arrival 1, Rev 700 |
Room Type = COMP2 |
Rooms Sold 1, Arrival 1, Rev 700 |
Room Type = TD |
Rooms Sold 1, Arrival 0, Rev 0 |
Room Type = TK |
Rooms Sold 1, Arrival 0, Rev 0 |
Room Type = COMP2, TK, TD |
Rooms Sold 3, Arrival 1, Rev 700 |
in_type RESTYPEMARKET |
Resulting Values |
Room Type = NULL |
Rooms Sold 2, Arrival 1, Rev 700 |
Room Type = COMP2 |
Rooms Sold 1, Arrival 1, Rev 700 |
Room Type = TD |
Rooms Sold 1, Arrival 0, Rev 0 |
Room Type = TK |
Rooms Sold 1, Arrival 0, Rev 0 |
Room Type = COMP2, TK, TD |
Rooms Sold 3, Arrival 1, Rev 700 |
v5i_occupancy.get_past_occupancy
in_type TOTAL |
Resulting Values |
Room Type = NULL |
Rooms Sold 2, Arrival 2, Rev 575 |
Room Type = COMP2 |
Rooms Sold 1, Arrival 2, Rev 575 |
Room Type = TD |
Rooms Sold 1, Arrival 0, Rev 0 |
Room Type = TK |
Rooms Sold 1, Arrival 0, Rev 0 |
Room Type = COMP2, TK, TD |
Rooms Sold 3, Arrival 2, Rev 575 |
in_type MARKET |
Resulting Values |
Room Type = NULL |
Rooms Sold 2, Arrival 2, Rev 575 |
Room Type = COMP2 |
Rooms Sold 1, Arrival 2, Rev 575 |
Room Type = TD |
Rooms Sold 1, Arrival 0, Rev 0 |
Room Type = TK |
Rooms Sold 1, Arrival 0, Rev 0 |
Room Type = COMP2, TK, TD |
Rooms Sold 3, Arrival 2, Rev 575 |
Oracle does not recommend calling the Future Occupancy API for a number of days into the future (in_number_of_days) that is greater than 365. Specifying a number of days greater than 365 is likely to compromise system performance and have adverse effects. Additionally with the new possibilities of RESTYPE and RESTYPEMARKET the amount of row generated will be significantly increased and therefore strong recommendations would be to call smaller windows of occupancy multiple times, i.e., a max of 180 days twice versus 365 days once.
Oracle does not recommend calling the Past Occupancy API for a number of days into the past (in_past_days) that is greater than 45 when performing extracts for a PMS environment. Specifying a number of days greater than 45 is likely to compromise system performance and have adverse effects. (However, it is recognized that for the initial one-time extract, 365 days can be specified.)
When performing extracts for ORS environments, specifying 1 day in the past would suffice as data earlier than this will never change.
Following are descriptions of the APIs developed for IDeaS, along with examples of their use.
v5i_occupancy.get_future_occupancy
(in_resort,
in_from_date,
in_number_of_days,
in_type,
out_msg,
in_res_type default null,
in_rate_category default null,
in_market_code default null,
in_room_type default null,
in_group_by_room_type_yn default 'N');
Parameter |
Data Format |
in_resort |
VARCHAR2 |
in_from_date |
DATE |
in_number_of_days |
NUMBER |
in_type |
VARCHAR2 Default = ‘TOTAL’ Valid values = ‘TOTAL’,‘MARKET’,’RESTYPE’, ’RESTYPEMARKET’,'RESTYPE_RTCAT' |
out_msg |
OUT VARCHAR2 |
in_res_type |
VARCHAR2 --Comma separated list of valid Opera reservation types --Applicable only to in_type = ‘RESTYPE’ or ‘RESTYPEMARKET’ or 'RESTYPE_RTCAT' --If ‘TOTAL’ or ‘MARKET’ value sent to be NULL |
in_rate_category |
VARCHAR2 --Comma separated list of valid Opera rate categories --Applicable only to in_type = ‘RATECATEGORY’ or 'RESTYPE_RTCAT' --If ‘TOTAL’ or ‘RESTYPE’ value sent to be NULL |
in_market_code |
VARCHAR2 --Comma separated list of valid Opera market segments --Applicable only to in_type = ‘MARKET’ or ‘RESTYPEMARKET’ --If ‘TOTAL’ or ‘MARKET’ value sent to be NULL |
in_room_type |
VARCHAR2 --Comma separated list of valid Opera room types --Null value will be treated as all room types |
in_populate_market_yn |
VARCHAR2 -- Default = N |
in_group_by_room_type_yn |
VARCHAR2(20) Group data by room type; group data by room class and room type if the General>Room Class application function is set to Y. Default = 'N' Valid values = 'Y', 'N' |
The use of TOTAL or MARKET in the in_type parameter will result in the API only returning the rooms sold figures for DEDUCT statuses, not including those of a NON DEDUCT status.
Out of Order and Out of Service counts are only included when the TOTAL variable is sent. Physical Rooms and OO Room counts (OO reflected with TOTAL only) are reflecting based on the Room Types defined. Should no room types be defined, i.e., null, the Physical Rooms and OO Rooms counts are based on the property level.
Note: When using the Rate Category filter, the quality of the resulting data 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 one or more nights.
4. Reservation is pushed down from external system without rate code.
v5i_occupancy.get_past_occupancy
(in_resort,
in_from_date,
in_past_days,
in_type,
out_msg,
in_room_type default null,
in_group_by_room_type_yn default 'N');
Parameter |
Data Format |
in_resort |
VARCHAR2 |
in_from_date |
DATE |
in_past_days |
NUMBER |
in_type |
VARCHAR2 Default = 'TOTAL' Valid values = 'TOTAL','MARKET','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 |
in_populate_market_yn |
VARCHAR2 -- Default = N |
in_group_by_room_type_yn |
VARCHAR2(20) Group data by room type; group data by room class and room type if the General>Room Class application function is set to Y. Default = 'N' Valid values = 'Y', 'N' |
The APIs will populate a global temp table (session specific) called “temp_v5i_occupancy” which must then be queried to fetch the required data.
No changes would be made to the past occupancy variable as these are always a deduct status.
The Past Occupancy API will only return values for the Physical Rooms when the in_type = ‘TOTAL’.
The temp_v5i_occupancy table has the following structure.
Field |
Data Format |
Notes |
RESORT |
VARCHAR2(20) NOT NULL |
Property this record belongs to. |
OCCUPANCY_DATE |
DATE NOT NULL |
Considered occupancy date. |
RATE_CATEGORY |
VARCHAR(20) |
Stores the rate category associated with the combination of market code, reservation type, etc., for transient reservations. Null for block reservations. |
MARKET_CODE |
VARCHAR2(20) |
Market code for block reservations is taken from the market code on the block header (BOOKING screen). Null for transient reservations. |
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. See Notes 1 and 2. |
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. See Notes 1 and 2. |
FOOD_REVENUE |
NUMBER |
Food revenue for the date. See Notes 1 and 2. |
CANCELLED_ROOMS |
NUMBER |
Cancelled rooms count for the date. |
NO_SHOW_ROOMS |
NUMBER |
No show rooms count for the date. |
RES_TYPE |
VARCHAR2 |
Reservation type. |
ROOM_TYPE |
VARCHAR2(20) |
The ROOM_TYPE column can contain a room class value when the General>Room Class application function is set to Y. |
1. 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.
2. 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.
3. Where the in_type variable ‘RESTYPEMARKET’ has been called, multiple rows for the same date will exist for the various Market Types and Res Types; therefore, if the property was configured with 24 Market codes and 10 Res Types there could potentially be 240 rows for the specified date. If called for 180 days this could amount to 43,200 rows.
Example 1 - Future Occupancy by Property
The following call will fetch the future data for property ‘ODH’ from business date for 365 days.
declare vmsg varchar2(200);begin v5i_occupancy.get_future_occupancy ('ODH', pms_p.business_date, 365, 'TOTAL', vmsg); If vmsg = ‘SUCCESS’ then ---- Query the temp_v5i_occupancy table. End if;end;Example 2 - Future Occupancy by Market
The following call will fetch the future data for property ‘ODH’ by market segment from business date for 365 days.
declare vmsg varchar2(200);begin v5i_occupancy.get_future_occupancy ('ODH', pms_p.business_date, 365, 'MARKET', vmsg); If vmsg = ‘SUCCESS’ then ---- Query the temp_v5i_occupancy table. End if;end;
Example 3 - Future Occupancy by Market Filtered for Room Types DLX and SUP
The following call will fetch the future data for property ‘ODH’ by market segment from business date for 365 days for the room types DLX and SUP.
declare vmsg varchar2(200);begin v5i_occupancy.get_future_occupancy ('ODH', pms_p.business_date, 365, 'MARKET', Vmsg, Null, Null, Null, ‘DLX,SUP’ 'N'); If vmsg = ‘SUCCESS’ then ---- Query the temp_v5i_occupancy table. End if;end;Note: Results will be filtered by the DLX and SUP room types and summarized. If the in_group_by_room_type_yn parameter was set to 'Y', results would be grouped by room type.
Example 4 - Future Occupancy by Reservation Type
The following call will fetch the future data for property ‘ODH’ by reservation type from business date for 365 days.
declare vmsg varchar2(200);begin v5i_occupancy.get_future_occupancy ('ODH', pms_p.business_date, 365, 'RESTYPE', Vmsg,‘4PM,6PM, NON GTD, DEP REQ’ -- Comma separated list of valid Opera reservation types, if ‘’ is sent then it is assumed all RESTYPES Null); If vmsg = ‘SUCCESS’ then ---- Query the temp_v5i_occupancy table. End if;end;Example 5 - Future Occupancy by Market Segment by Reservation Type
The following call will fetch the future data for property ‘ODH’ by market segment by reservation type from business date for 180 days.
declare vmsg varchar2(200);begin v5i_occupancy.get_future_occupancy ('ODH', pms_p.business_date, 180, 'RESTYPEMARKET', Vmsg,‘4PM,6PM, NON GTD, DEP REQ’, -- Comma separated list of valid Opera reservation types, if ‘’ is sent then it is assumed all RESTYPES’RACK,GROUP,CORP’ -- Comma separated list of valid Opera market segments, if ‘’ is sent then it is assumed all MARKET SEGMENTS); If vmsg = ‘SUCCESS’ then ---- Query the temp_v5i_occupancy table. End if;end;Due to the possible large amount of data that can be returned by the API for Market by Reservation Type per Day, it is a suggestion that no more than 180 days be used.
Example 6 - Future Occupancy by Rate Category by Reservation Type
The following call will fetch the future data for property ‘ODH’ by rate category by reservation type from business date for 180 days.
Note: It is suggested that no more than 180 days be used.
declare vmsg varchar2(200);begin v5i_occupancy.get_future_occupancy ('ODH', pms_p.business_date, 180, 'RESTYPE_RTCAT', Vmsg,‘NON GTD, DEP REQ’, -- Comma separated list of valid Opera reservation types, if ‘’ is sent then it is assumed all RESTYPES’RACK,GROUP,CORP’ -- Comma separated list of valid Opera rate categories, if ‘’ is sent then it is assumed all RATE CATEGORIES); If vmsg = ‘SUCCESS’ then ---- Query the temp_v5i_occupancy table. End if;end;Example 7 - Past Occupancy by Property
The following call will fetch the past data for property ‘ODH’ from business date for past 45 days.
declare vmsg varchar2(200);begin v5i_occupancy.get_past_occupancy ('ODH', pms_p.business_date -1, 45, 'TOTAL', vmsg); If vmsg = ‘SUCCESS’ then ---- Query the temp_v5i_occupancy table. End if;end;Example 8 - Past Occupancy by Property for Room Types DLX and KNG
The following call will fetch the past data for property ‘ODH’ from business date for past 45 days for room types DLX and KNG.
declare vmsg varchar2(200);begin v5i_occupancy.get_past_occupancy ('ODH', pms_p.business_date -1, 45, 'TOTAL', Vmsg, ‘DLX,KNG’, 'N'); If vmsg = ‘SUCCESS’ then ---- Query the temp_v5i_occupancy table. End if;end;Note: Results will be filtered by the DLX and KNG room types and summarized. If the in_group_by_room_type_yn parameter was set to 'Y', results would be grouped by room type.
Example 9 - Past Occupancy by Market
The following call will fetch the past data for property ‘ODH’ by market segment from business date for past 45 days.
declare vmsg varchar2(200);begin v5i_occupancy.get_past_occupancy ('ODH', pms_p.business_date -1, 45, 'MARKET', vmsg); If vmsg = ‘SUCCESS’ then ---- Query the temp_v5i_occupancy table. End if;end;Example 10 - Past Occupancy by Rate Category
The following call will fetch the past data for property ‘ODH’ by rate category from business date for the past 45 days.
declare vmsg varchar2(200);begin v5i_occupancy.get_past_occupancy ('ODH', pms_p.business_date -1, 45, 'RATECATEGORY', vmsg); If vmsg = ‘SUCCESS’ then ---- Query the temp_v5i_occupancy table. End if;end;Example 11 - Future Occupancy with In Type Parameter Variables and Resulting Calculation Statistic
ABC and XYZ represent market codes using the below.
Date |
Res Type |
Totals |
Rooms Sold |
Cancels |
20 |
D - Deduct |
10 |
8 (6 ABC, 2 XYZ) |
2 (1 ABC, 1 XYZ) |
ND Non Deduct |
15 |
12 (3 ABC, 9 XYZ) |
3 (3 XYZ) |
Variable TOTAL
Date |
Rooms Sold |
Cancels |
20 |
8 |
5 |
Variable MARKET
Date |
Market |
Rooms Sold |
Cancels |
20 |
ABC |
6 |
1 |
20 |
XYZ |
2 |
4 |
Variable RESTYPE
Date |
ResType |
Rooms Sold |
Cancels |
20 |
D |
8 |
2 |
20 |
ND |
12 |
3 |