aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_generate_recapitulate
Parameters
Name
Type
Mode
adate
date
IN (DEFAULT NULL)
user_name
text
IN (DEFAULT NULL)
status
text
OUT
msg
text
OUT
Definition
DECLARE rec_count int = 0; BEGIN adate = coalesce(adate, fn_system_date()); INSERT INTO recapitulate( date_ref, 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, individual_breakdown, group_rooms, group_adult, group_child, group_infant, group_revenue, group_breakdown, arrival_rooms, arrival_adult, arrival_child, arrival_infant, departure_rooms, departure_adult, departure_child, departure_infant, day_used_qty, day_used_adult, day_used_child, day_used_infant, day_used_revenue, booking_rooms, booking_adult, booking_child, booking_infant, walk_in_rooms, walk_in_adult, walk_in_child, walk_in_infant, early_checkout, extended_stay, canceled_rooms, canceled_adult, canceled_child, canceled_infant, no_show_rooms, no_show_adult, no_show_child, no_show_infant, tomorrow_expected_arrival, tomorrow_expected_departure, individual_revenue_total, group_revenue_total, day_used_revenue_total) SELECT $1, 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, individual_breakdown, group_rooms, group_adult, group_child, group_infant, group_revenue, group_breakdown, arrival_rooms, arrival_adult, arrival_child, arrival_infant, departure_rooms, departure_adult, departure_child, departure_infant, day_used_qty, day_used_adult, day_used_child, day_used_infant, day_used_revenue, booking_rooms, booking_adult, booking_child, booking_infant, walk_in_rooms, walk_in_adult, walk_in_child, walk_in_infant, early_checkout, extended_stay, canceled_rooms, canceled_adult, canceled_child, canceled_infant, no_show_rooms, no_show_adult, no_show_child, no_show_infant, tomorrow_expected_arrival, tomorrow_expected_departure, individual_revenue_total, group_revenue_total, day_used_revenue_total FROM sp_get_recapitulate($1, true) ON CONFLICT (date_ref) DO NOTHING; GET DIAGNOSTICS rec_count = ROW_COUNT; status = 'success'; msg = rec_count::TEXT||' record generated'; END