room_summary

227907 rows


Columns

Column Type Size Nulls Auto Default Children Parents Comments
id int4 10 √ null
date_ref date 13 null
room_id int4 10 √ null
room.id fk_room_summary_room_id N
room_number t_room_number 2147483647 √ null
room_status bpchar 3 null
hk_status bpchar 2 √ null
roomtype_id int4 10 √ null
room_type.id fk_room_summary_roomtype_id N
bedtype_id int4 10 √ null
bed_type.id fk_room_summary_bedtype_id N
location_id int4 10 √ null
room_location.id fk_room_summary_location_id N
building_id int4 10 √ null
building.id fk_room_summary_building_id N
exposure_id int4 10 √ null
room_exposure.id fk_room_summary_exposure_id N
register_id int4 10 √ null
booking_id int4 10 √ null
book_type bpchar 1 √ null
share_room bool 1 √ false
house_used bool 1 √ false
walk_in bool 1 √ false
comp bool 1 √ false
day_used bool 1 √ false
rateplan_id int4 10 √ null
rate_plan.id fk_room_summary_rateplan_id N
stay_type bpchar 1 √ null
start_date date 13 √ null
end_date date 13 √ null
in_house_qty int2 5 √ 0
day_used_qty int2 5 √ 0
adult int2 5 √ 0
child int2 5 √ 0
infant int2 5 √ 0
extra_adult int2 5 √ 0
extra_child int2 5 √ 0
extra_infant int2 5 √ 0
room_rate t_money 15,2 √ null

REVENUE_CALC_TYPE = 0 basis. Full nightly rate, GROSS (includes priced-in meals, service charge and tax). Source: registration_rates.room_rate.
extra_adult_rate t_money 15,2 √ null
extra_child_rate t_money 15,2 √ null
extra_infant_rate t_money 15,2 √ null
promotion_id int4 10 √ null
promotion.id fk_room_summary_promotion_id N
channel_id int4 10 √ null
channel.id fk_room_summary_channel_id N
origin_id int4 10 √ null
origin.id fk_room_summary_origin_id N
sales_id int4 10 √ null
sales_staff.id fk_room_summary_sales_id N
market_id int4 10 √ null
market.id fk_room_summary_market_id N
rsvtype_id int4 10 √ null
reservation_type.id fk_room_summary_rsvtype_id N
advance_book_period int4 10 √ 0
return_guest bool 1 √ false
guest_id int4 10 √ null
guest_sex bpchar 1 √ null
guest_age int4 10 √ null
guest_nation_id int4 10 √ null
nationality.id fk_room_summary_guest_nation_id N
guest_lang_id int4 10 √ null
language.id fk_room_summary_guest_lang_id N
guest_country_id int4 10 √ null
country.id fk_room_summary_guest_country_id N
guest_country_res_id int4 10 √ null
country.id fk_room_summary_guest_country_res_id N
guest_city_id int4 10 √ null
city.id fk_room_summary_guest_city_id N
guest_occupation_id int4 10 √ null
occupation.id fk_room_summary_guest_occupation_id N
guest_vip_id int4 10 √ null
vip.id fk_room_summary_guest_vip_id N
guest_name text 2147483647 √ null
guest_member_code text 2147483647 √ null
accno varchar 2147483647 √ null
mealtype_id int4 10 √ null
room_charge t_money 15,2 √ null

REVENUE_CALC_TYPE = 1 basis, and the room charge that is (or will be) posted to the folio. Room only but still GROSS of service charge and tax. Source: sp_rate_schedule_breakdown.room. NULL on historical rows, which is why readers use COALESCE(room_charge, room_rate).
rate_breakdown jsonb 2147483647 √ null

How the nightly RATE splits across components, from sp_rate_schedule_breakdown: {room, abf, lunch, dinner, inclusive, extrabed, abf_extra, lunch_extra, dinner_extra, other}. All GROSS of service/tax. This is what the guest was quoted – not what was posted. Compare revenue_breakdown, which is the opposite direction.
revenue_breakdown jsonb 2147483647 √ null

Actual POSTED revenue to date for this registration, from fn_get_guest_revenues_js: SUM(transactions.acc_amount) grouped by department_group.category (room/fb/other), cumulative up to this night. Driven by folio postings, not by the rate. Compare rate_breakdown, which is the quoted rate split instead.
revenue_room_net t_money 15,2 √ null

REVENUE_CALC_TYPE = 2 basis. Room only and NET of every itemizer on the room-charge department (= BASE_PRICE). Source: sp_rate_schedule_breakdown.room_net. NULL = not backfilled; readers use COALESCE(revenue_room_net, room_charge, room_rate). “NET” = net of service/tax, not net of void.

Indexes

Constraint Name Type Sort Column(s)
pk_room_summary Primary key Asc id
idx_room_summary_bedtype_id Performance Asc bedtype_id
idx_room_summary_building_id Performance Asc building_id
idx_room_summary_channel_id Performance Asc channel_id
idx_room_summary_date_ref Performance Asc date_ref
idx_room_summary_exposure_id Performance Asc exposure_id
idx_room_summary_guest_city_id Performance Asc guest_city_id
idx_room_summary_guest_country_id Performance Asc guest_country_id
idx_room_summary_guest_country_res_id Performance Asc guest_country_res_id
idx_room_summary_guest_lang_id Performance Asc guest_lang_id
idx_room_summary_guest_nation_id Performance Asc guest_nation_id
idx_room_summary_guest_occupation_id Performance Asc guest_occupation_id
idx_room_summary_guest_vip_id Performance Asc guest_vip_id
idx_room_summary_location_id Performance Asc location_id
idx_room_summary_market_id Performance Asc market_id
idx_room_summary_origin_id Performance Asc origin_id
idx_room_summary_promotion_id Performance Asc promotion_id
idx_room_summary_rateplan_id Performance Asc rateplan_id
idx_room_summary_room_id Performance Asc room_id
idx_room_summary_room_staus Performance Asc room_status
idx_room_summary_roomtype_id Performance Asc roomtype_id
idx_room_summary_rsvtype_id Performance Asc rsvtype_id
idx_room_summary_sales_id Performance Asc sales_id
unq_room_summary Must be unique Asc/Asc/Asc date_ref + register_id + guest_id

Relationships

Close relationships within degrees of separation