Creando una herramienta History Forecast desde Opera PMS Online con Java y HTML Responsive Web Design (parte 1)

Revenue Management es una técnica que consiste en vender el producto adecuado, al cliente adecuado, en el momento adecuado y al precio adecuado, se trata de predecir el comportamiento del consumidor con el objetivo de maximizar los ingresos y minimizar los costos.

El forecasting consiste en la estimación y el análisis de la demanda futura para un producto o servicio utilizando diferentes variables como datos históricos.

Una de las principales variables en las que se fundamenta el forecasting hotelero es el análisis de datos estadísticos de la propiedad en periodos anteriores, con esto podemos prever el los ingresos futuros y tomar las decisiones comerciales oportunas para corregir desviaciones con el propósito de maximizar los ingresos y minimizar los costos.

Teniendo claro la importancia de una herramienta forecast en especial online, en tiempo real y desde un smartphone que consulte datos directamente desde el Opera PMS sería una herramienta de gran utilidad para la gestión eficiente de la Propiedad.

Es importante considerar que la herramienta forecast online deba contemplar los siguientes datos cuyas descripciones se mantienen de la documentación original de Opera:

Con este propósito se construye el siguiente Query SQL para Oracle que obtiene el forecast con los ingresos del el mes corriente (HISTORY) hacia el estimado de ingresos anual (FORECAST).
Date. Date to which the statistics apply.

Total Occupancy. Total occupied rooms (non-deduct, deduct, individual and group).

Arrival Rooms. Rooms with guests arriving this date. For arrivals, component rooms count as 1 and not the number of physical rooms making up the component room.

Comp Rooms. Complementary rooms (rooms attached to reservations having a rate code that is flagged Complimentary).

House Use. House use rooms (rooms attached to reservations having a rate code that is flagged House Use).

Deduct Individual. Individual reservations having a reservation type that is flagged Deduct from inventory.

Non-Deduct Individual. Individual reservations having a reservation type that is not flagged Deduct from inventory.

Deduct Group. Group reservations having a reservation type that is flagged Deduct from inventory.

Non-Deduct Group. Group reservations having a reservation type that is not flagged Deduct from inventory.

Occupancy %. (Total Occupied Rooms / Number of Rooms in Property) x 100).

Room Revenue. Total revenue for this date associated with Lodging transaction codes for deduct, non-deduct, individual, and group reservations.

Average Rate. Total Room Revenue / Number of Occupied rooms.

Departure Rooms. Rooms due out this date. For departures, component rooms count as 1 and not the number of physical rooms making up the component room.

Day Use Rooms. Day Use Rooms for the date.No Show Rooms. No Show Rooms for the date.

Con este propósito se construye el siguiente Query SQL para Oracle que obtiene el forecast con los ingresos del el mes corriente (HISTORY) hacia el estimado de ingresos anual (FORECAST).

SELECT rec_type rec_type,
DECODE(rec_type,'A_STAT',f_message('REP_HISTORY','History'),f_message('REP_FORECAST','Forecast')) rec_type_desc,
TO_CHAR(considered_date,'DD/MM/RRRR') considered_date,
SUM(no_rooms * count_resv_01) no_rooms,
SUM(ind_deduct_rooms * count_resv_01) ind_deduct_rooms,
SUM(ind_non_deduct_rooms * count_resv_01) ind_non_deduct_rooms,
SUM(grp_deduct_rooms * count_resv_01) grp_deduct_rooms,
SUM(grp_non_deduct_rooms * count_resv_01) grp_non_deduct_rooms,
SUM(no_persons * count_resv_01) no_persons,
SUM(arrival_rooms * count_resv_01) arrival_rooms,
SUM(departure_rooms * count_resv_01) departure_rooms,
SUM(complimentary_rooms * count_resv_01) complimentary_rooms,
SUM(house_use_rooms * count_resv_01) house_use_rooms,
SUM(day_use_rooms * count_resv_01) day_use_rooms,
SUM(no_show_rooms * count_resv_01) no_show_rooms,
SUM(revenue * count_revenue_01) revenue,
SUM(DECODE(group_yn,'N',DECODE(deduct_yn,'Y',revenue,0),0) * count_revenue_01) ind_deduct_revenue,
SUM(DECODE(group_yn,'N',DECODE(deduct_yn,'N',revenue,0),0) * count_revenue_01) ind_non_deduct_revenue,
SUM(DECODE(group_yn,'Y',DECODE(deduct_yn,'Y',revenue,0),0) * count_revenue_01) grp_deduct_revenue,
SUM(DECODE(group_yn,'Y',DECODE(deduct_yn,'N',revenue,0),0) * count_revenue_01) grp_non_deduct_revenue
FROM (
SELECT SUBSTR(rsf.rec_type,1,6) rec_type,
rsf.considered_date considered_date,
DECODE(nvl(rsf.owner_rental_flag,'H'),'O',0,rsf.no_rooms) no_rooms,
DECODE(nvl(rsf.owner_rental_flag,'H'),'O',0,DECODE(rsf.group_yn,'N',DECODE(deduct_yn,'Y',rsf.no_rooms,0),0)) ind_deduct_rooms,
DECODE(nvl(rsf.owner_rental_flag,'H'),'O',0,DECODE(rsf.group_yn,'N',DECODE(deduct_yn,'N',rsf.no_rooms,0),0)) ind_non_deduct_rooms,
DECODE(nvl(rsf.owner_rental_flag,'H'),'O',0,DECODE(rsf.group_yn,'Y',DECODE(deduct_yn,'Y',rsf.no_rooms,0),0)) grp_deduct_rooms,
DECODE(nvl(rsf.owner_rental_flag,'H'),'O',0,DECODE(rsf.group_yn,'Y',DECODE(deduct_yn,'N',rsf.no_rooms,0),0)) grp_non_deduct_rooms,
DECODE(nvl(rsf.owner_rental_flag,'H'),'O',0,rsf.no_persons) no_persons,
DECODE(nvl(rsf.owner_rental_flag,'H'),'O',0,rsf.arrival_rooms) arrival_rooms,
DECODE(nvl(rsf.owner_rental_flag,'H'),'O',0,rsf.departure_rooms) departure_rooms,
DECODE(nvl(rsf.owner_rental_flag,'H'),'O',0,rsf.complimentary_rooms) complimentary_rooms,
DECODE(nvl(rsf.owner_rental_flag,'H'),'O',0,rsf.house_use_rooms) house_use_rooms,
DECODE(nvl(rsf.owner_rental_flag,'H'),'O',0,rsf.day_use_rooms) day_use_rooms,
DECODE(nvl(rsf.owner_rental_flag,'H'),'O',0,rsf.no_show_rooms) no_show_rooms,
DECODE(nvl(rsf.owner_rental_flag,'H'),'O',0,
CASE 'RN'
WHEN 'RN' THEN rsf.net_room_revenue -- net room revenue
WHEN 'RG' THEN rsf.net_room_revenue + rsf.room_revenue_tax -- net total revenue
WHEN 'TN' THEN rsf.total_revenue -- gross room revenue
WHEN 'TG' THEN rsf.total_revenue + rsf.total_revenue_tax -- gross total revenue
ELSE rsf.net_room_revenue
END
) revenue,
(CASE WHEN SUBSTR(rsf.room_category,1,1) <> '-' THEN 1 -- this is not a PM resv, count it
WHEN SUBSTR(rsf.rec_type,1,6) = 'A_STAT' THEN 0 -- for statistics we never count PM reservations (rooms)
WHEN NVL('N','N') = 'Y' THEN 1 -- for forecast look at parameter whether to count PM rooms
ELSE 0
END ) count_resv_01,
(CASE WHEN SUBSTR(rsf.room_category,1,1) <> '-' THEN 1 -- this is not a PM resv, count the revenue
WHEN SUBSTR(rsf.rec_type,1,6) = 'A_STAT' THEN 1 -- for statistics we count all revenue, even from PM resv
WHEN NVL('N','N') = 'Y' THEN 1 -- for forecast look at parameter whether to count PM rooms
ELSE 0
END ) count_revenue_01,
rsf.owner_rooms owner_rooms,
rsf.ff_rooms ff_rooms,
rsf.deduct_yn,
rsf.group_yn
FROM rep_res_stati_forecast_hld rsf
where rsf.considered_date between TO_DATE(('01'||to_char(sysdate,'MMRRRR')),'DDMMRRRR') and TO_DATE(TO_CHAR(LAST_DAY(to_date(to_char(ADD_MONTHS(SYSDATE, 11),'YYYYMM'),'YYYYMM')),'DDMMRRRR'),'DDMMRRRR')
and rsf.deduct_yn='Y'
-- make sure that we have at least one record per day
UNION ALL
SELECT (CASE WHEN (TO_DATE(('01'||to_char(sysdate,'MMRRRR')),'DDMMRRRR') + ns.n - 1) < pms_p.business_date THEN
'A_STAT'
ELSE 'B_FORE'
END) rec_type,
(TO_DATE(('01'||to_char(sysdate,'MMRRRR')),'DDMMRRRR') + ns.n - 1) considered_date,
0 no_rooms,
0 ind_deduct_rooms,
0 ind_non_deduct_rooms,
0 grp_deduct_rooms,
0 grp_non_deduct_rooms,
0 no_persons,
0 arrival_rooms,
0 departure_rooms,
0 complimentary_rooms,
0 house_use_rooms,
0 day_use_rooms,
CASE WHEN (TO_DATE(('01'||to_char(sysdate,'MMRRRR')),'DDMMRRRR') + ns.n - 1) < pms_p.business_date THEN
0
ELSE TO_NUMBER(NULL)
END no_show_rooms, -- for the forecasting section the No Show rooms should be NULL since we cannot predict how many rooms will be no-show
0 revenue,
0 count_resv_01,
0 count_revenue_01,
0 owner_rooms,
0 ff_rooms,
'N' deduct_yn,
'N' group_yn
FROM number_scale ns
WHERE ns.n <= TO_DATE(TO_CHAR(LAST_DAY(to_date(to_char(ADD_MONTHS(SYSDATE, 11),'YYYYMM'),'YYYYMM')),'DDMMRRRR'),'DDMMRRRR') - TO_DATE(('01'||to_char(sysdate,'MMRRRR')),'DDMMRRRR') + 1
)
group by rec_type,
considered_date
ORDER BY rec_type,
TO_DATE(considered_date,'DD/MM/RRRR')




Este Query será la base para comenzar a construir la herramienta forecast online para smartphone que se publicara en los siguientes Post de este Blog.