aonestar.public

Parameters

Name	Type	Mode
period_type	text	IN (DEFAULT NULL)
from_date	date	IN (DEFAULT NULL)
to_date	date	IN (DEFAULT NULL)
recalculate	boolean	IN (DEFAULT false)
revenue_calc_type	integer	IN (DEFAULT fn_revenue_calc_type())
start_date	date	OUT
end_date	date	OUT
total_rooms	integer	OUT
inventory_rooms	integer	OUT
sellable_rooms	integer	OUT
ooo_rooms	integer	OUT
oos_rooms	integer	OUT
ooi_rooms	integer	OUT
occ_rooms	integer	OUT
occ_pax	text	OUT
occ_adult	integer	OUT
occ_child	integer	OUT
occ_infant	integer	OUT
comp_rooms	integer	OUT
comp_pax	text	OUT
comp_adult	integer	OUT
comp_child	integer	OUT
comp_infant	integer	OUT
hsu_rooms	integer	OUT
hsu_pax	text	OUT
hsu_adult	integer	OUT
hsu_child	integer	OUT
hsu_infant	integer	OUT
adr_actual	t_money	OUT
adr_per_guest_actual	t_money	OUT
revpar_actual	t_money	OUT
adr_overall	t_money	OUT
adr_per_guest_overall	t_money	OUT
revpar_overall	t_money	OUT
pay_rooms	integer	OUT
without_pay_rooms	integer	OUT
available_rooms	integer	OUT
individual_rooms	integer	OUT
individual_pax	text	OUT
individual_adult	integer	OUT
individual_child	integer	OUT
individual_infant	integer	OUT
individual_revenue	t_money	OUT
group_rooms	integer	OUT
group_pax	text	OUT
group_adult	integer	OUT
group_child	integer	OUT
group_infant	integer	OUT
group_revenue	t_money	OUT
total_revenue	t_money	OUT
arrival_rooms	integer	OUT
arrival_pax	text	OUT
arrival_adult	integer	OUT
arrival_child	integer	OUT
arrival_infant	integer	OUT
departure_rooms	integer	OUT
departure_pax	text	OUT
departure_adult	integer	OUT
departure_child	integer	OUT
departure_infant	integer	OUT
walkin_rooms	integer	OUT
walkin_pax	text	OUT
walkin_adult	integer	OUT
walkin_child	integer	OUT
walkin_infant	integer	OUT
booking_rooms	integer	OUT
booking_pax	text	OUT
booking_adult	integer	OUT
booking_child	integer	OUT
booking_infant	integer	OUT
dayuse_qty	integer	OUT
dayuse_pax	text	OUT
dayuse_adult	integer	OUT
dayuse_child	integer	OUT
dayuse_infant	integer	OUT
dayuse_revenue	t_money	OUT
early_checkout_rooms	integer	OUT
extend_stay_rooms	integer	OUT
cancel_rooms	integer	OUT
cancel_pax	text	OUT
cancel_adult	integer	OUT
cancel_child	integer	OUT
cancel_infant	integer	OUT
noshow_rooms	integer	OUT
noshow_pax	text	OUT
noshow_adult	integer	OUT
noshow_child	integer	OUT
noshow_infant	integer	OUT
tomorrow_arrival	integer	OUT
tomorrow_departure	integer	OUT
tomorrow_occupancy	integer	OUT
percent_inventory	t_percent	OUT
percent_sellable	t_percent	OUT
percent_ooo	t_percent	OUT
percent_oos	t_percent	OUT
percent_ooi	t_percent	OUT
percent_occ_actual	t_percent	OUT
percent_payroom_actual	t_percent	OUT
percent_without_pay_actual	t_percent	OUT
percent_available_actual	t_percent	OUT
percent_comp_actual	t_percent	OUT
percent_hsu_actual	t_percent	OUT
percent_arrival_actual	t_percent	OUT
percent_departure_actual	t_percent	OUT
percent_walkin_actual	t_percent	OUT
percent_booking_actual	t_percent	OUT
percent_dayuse_actual	t_percent	OUT
percent_early_checkout_actual	t_percent	OUT
percent_extend_stay_actual	t_percent	OUT
percent_cancel_actual	t_percent	OUT
percent_noshow_actual	t_percent	OUT
percent_tomorrow_occ_actual	t_percent	OUT
percent_occ_overall	t_percent	OUT
percent_payroom_overall	t_percent	OUT
percent_without_pay_overall	t_percent	OUT
percent_available_overall	t_percent	OUT
percent_comp_overall	t_percent	OUT
percent_hsu_overall	t_percent	OUT
percent_arrival_overall	t_percent	OUT
percent_departure_overall	t_percent	OUT
percent_walkin_overall	t_percent	OUT
percent_booking_overall	t_percent	OUT
percent_dayuse_overall	t_percent	OUT
percent_early_checkout_overall	t_percent	OUT
percent_extend_stay_overall	t_percent	OUT
percent_cancel_overall	t_percent	OUT
percent_noshow_overall	t_percent	OUT
percent_tomorrow_occ_overall	t_percent	OUT

Definition

DECLARE 
    pay_guests int;
    without_pay_guests int;
BEGIN                                  
    SELECT min(r.date_ref), max(r.date_ref), SUM(r.total_rooms) AS total_rooms, SUM(r.ooo_rooms), SUM(r.oos_rooms), 
          SUM(r.ooi_rooms), SUM(r.occ_rooms) AS occ_rooms, SUM(r.occ_adult), SUM(r.occ_child), SUM(r.occ_infant), 
          SUM(r.hsu_rooms), SUM(r.hsu_adult), SUM(r.hsu_child), SUM(r.hsu_infant), SUM(r.comp_rooms), SUM(r.comp_adult), 
          SUM(r.comp_child), SUM(r.comp_infant), SUM(r.individual_rooms), SUM(r.individual_adult), SUM(r.individual_child), 
          SUM(r.individual_infant), SUM(r.individual_revenue)::t_money, SUM(r.group_rooms), SUM(r.group_adult), SUM(r.group_child), 
          SUM(r.group_infant), SUM(r.group_revenue)::t_money, sum(r.total_revenue)::t_money, SUM(r.arrival_rooms), 
          SUM(r.arrival_adult), SUM(r.arrival_child), SUM(r.arrival_infant), SUM(r.departure_rooms), SUM(r.departure_adult), 
          SUM(r.departure_child), SUM(r.departure_infant), SUM(r.day_used_qty), SUM(r.day_used_adult), SUM(r.day_used_child), 
          SUM(r.day_used_infant), SUM(r.day_used_revenue)::t_money, SUM(r.booking_rooms), SUM(r.booking_adult), 
          SUM(r.booking_child), SUM(r.booking_infant), SUM(r.walk_in_rooms), SUM(r.walk_in_adult), SUM(r.walk_in_child), 
          SUM(r.walk_in_infant), SUM(r.early_checkout), SUM(r.extended_stay), SUM(r.canceled_rooms), SUM(r.canceled_adult), 
          SUM(r.canceled_child), SUM(r.canceled_infant), SUM(r.no_show_rooms), SUM(r.no_show_adult), 
          SUM(r.no_show_child), SUM(r.no_show_infant), 
          IIF(period_type='TODAY' OR from_date = to_date, sum(r.tomorrow_expected_arrival), NULL)::int, 
          IIF(period_type='TODAY' OR from_date = to_date, sum(r.tomorrow_expected_departure), NULL)::int
    FROM sp_recapitulate_by_date(period_type, from_date, to_date, recalculate, revenue_calc_type) AS R
    INTO start_date, end_date, total_rooms, ooo_rooms, oos_rooms, ooi_rooms, occ_rooms, occ_adult, occ_child, occ_infant, hsu_rooms, 
         hsu_adult, hsu_child, hsu_infant, comp_rooms, comp_adult, comp_child, comp_infant, individual_rooms, individual_adult, individual_child, 
         individual_infant, individual_revenue, group_rooms, group_adult, group_child, group_infant, group_revenue,  
         total_revenue, arrival_rooms, arrival_adult, arrival_child, arrival_infant, departure_rooms, departure_adult, departure_child, departure_infant, 
         dayuse_qty, dayuse_adult, dayuse_child, dayuse_infant, dayuse_revenue, booking_rooms, booking_adult, booking_child, booking_infant, 
         walkin_rooms, walkin_adult, walkin_child, walkin_infant, early_checkout_rooms, extend_stay_rooms, cancel_rooms, cancel_adult, cancel_child, 
         cancel_infant, noshow_rooms, noshow_adult, noshow_child, noshow_infant, tomorrow_arrival, tomorrow_departure; 
    
    inventory_rooms     := total_rooms - ooi_rooms; 
    sellable_rooms      := inventory_rooms - ooo_rooms;

    pay_rooms           := occ_rooms - comp_rooms - hsu_rooms; 
    without_pay_rooms   := comp_rooms + hsu_rooms;                                  -- Rooms without pay
    pay_guests          := occ_adult - comp_adult - hsu_adult;                      -- Paid guests
    without_pay_guests  := comp_adult + hsu_adult;                                  -- Guests without pay
    available_rooms     := total_rooms - occ_rooms - ooo_rooms;                     -- Available rooms 
    tomorrow_occupancy  := occ_rooms + tomorrow_arrival - tomorrow_departure; 

    adr_overall           := fn_money_div(total_revenue, occ_rooms); 
    adr_per_guest_overall := fn_money_div(total_revenue, occ_adult); 
    revpar_overall        := fn_money_div(total_revenue, inventory_rooms);                     

    adr_actual            := fn_money_div(total_revenue, pay_rooms); 
    adr_per_guest_actual  := fn_money_div(total_revenue, pay_guests); 
    revpar_actual         := fn_money_div(total_revenue, sellable_rooms); 

    percent_ooo         := fn_percent(ooo_rooms, total_rooms);   
    percent_oos         := fn_percent(oos_rooms, total_rooms); 
    percent_ooi         := fn_percent(ooi_rooms, total_rooms); 
    percent_inventory   := fn_percent(inventory_rooms, total_rooms); 
    percent_sellable    := fn_percent(sellable_rooms, total_rooms); 
    
    percent_occ_overall            := fn_percent(occ_rooms, inventory_rooms);           -- Overall, Occupancy percent
    percent_payroom_overall        := fn_percent(pay_rooms, inventory_rooms);           -- Overall, Pay rooms percent
    percent_without_pay_overall    := fn_percent(without_pay_rooms, inventory_rooms);   -- Overall, without pay percent
    percent_available_overall      := fn_percent(available_rooms, inventory_rooms);     -- Overall, available_percent
    percent_comp_overall           := fn_percent(comp_rooms, inventory_rooms);          -- Overall, comp_percent
    percent_hsu_overall            := fn_percent(hsu_rooms, inventory_rooms);           -- Overall, hsu_percent
    percent_arrival_overall        := fn_percent(arrival_rooms, inventory_rooms);   
    percent_departure_overall      := fn_percent(departure_rooms, inventory_rooms);   
    percent_walkin_overall         := fn_percent(walkin_rooms, inventory_rooms);   
    percent_booking_overall        := fn_percent(booking_rooms, inventory_rooms);   
    percent_dayuse_overall         := fn_percent(dayuse_qty, inventory_rooms);   
    percent_early_checkout_overall := fn_percent(early_checkout_rooms, inventory_rooms);   
    percent_extend_stay_overall    := fn_percent(extend_stay_rooms, inventory_rooms);   
    percent_cancel_overall         := fn_percent(cancel_rooms, inventory_rooms);   
    percent_noshow_overall         := fn_percent(noshow_rooms, inventory_rooms);   
    percent_tomorrow_occ_overall   := fn_percent(tomorrow_occupancy, inventory_rooms);   
    
    percent_occ_actual             := fn_percent(occ_rooms, sellable_rooms);           -- actual, Occupancy percent
    percent_payroom_actual         := fn_percent(pay_rooms, sellable_rooms);           -- actual, Pay rooms percent
    percent_without_pay_actual     := fn_percent(without_pay_rooms, sellable_rooms);   -- actual, without pay percent
    percent_available_actual       := fn_percent(available_rooms, sellable_rooms);     -- actual, available_percent
    percent_comp_actual            := fn_percent(comp_rooms, sellable_rooms);          -- actual, comp_percent
    percent_hsu_actual             := fn_percent(hsu_rooms, sellable_rooms);           -- actual, hsu_percent
    percent_arrival_actual         := fn_percent(arrival_rooms, sellable_rooms);   
    percent_departure_actual       := fn_percent(departure_rooms, sellable_rooms);   
    percent_walkin_actual          := fn_percent(walkin_rooms, sellable_rooms);   
    percent_booking_actual         := fn_percent(booking_rooms, sellable_rooms);   
    percent_dayuse_actual          := fn_percent(dayuse_qty, sellable_rooms);   
    percent_early_checkout_actual  := fn_percent(early_checkout_rooms, sellable_rooms);   
    percent_extend_stay_actual     := fn_percent(extend_stay_rooms, sellable_rooms);   
    percent_cancel_actual          := fn_percent(cancel_rooms, sellable_rooms);   
    percent_noshow_actual          := fn_percent(noshow_rooms, sellable_rooms);   
    percent_tomorrow_occ_actual    := fn_percent(tomorrow_occupancy, sellable_rooms);   

    occ_pax        := fn_pax(occ_adult, occ_child, occ_infant);
    hsu_pax        := fn_pax(hsu_adult, hsu_child, hsu_infant);
    comp_pax       := fn_pax(comp_adult, comp_child, comp_infant);
    individual_pax := fn_pax(individual_adult, individual_child, individual_infant);
    group_pax      := fn_pax(group_adult, group_child, group_infant);
    arrival_pax    := fn_pax(arrival_adult, arrival_child, arrival_infant);
    departure_pax  := fn_pax(departure_adult, departure_child, departure_infant);
    dayuse_pax     := fn_pax(dayuse_adult, dayuse_child, dayuse_infant);
    booking_pax    := fn_pax(booking_adult, booking_child, booking_infant);
    walkin_pax     := fn_pax(walkin_adult, walkin_child, walkin_infant);
    cancel_pax     := fn_pax(cancel_adult, cancel_child, cancel_infant);
    noshow_pax     := fn_pax(noshow_adult, noshow_child, noshow_infant);

    RETURN NEXT;  
END