Routines

Routines

Name Type Language Deterministic Return Type Security Restriction Comments
sp_get_rate_schedule_js FUNCTION SQL false json INVOKER
sp_register_card FUNCTION PLPGSQL false SETOF record INVOKER
sp_log_debug FUNCTION SQL false void INVOKER
sp_send_message_to_guest FUNCTION SQL false integer INVOKER
sp_get_folio_patterns_js FUNCTION SQL false json INVOKER
sp_move_transactions FUNCTION PLPGSQL false json INVOKER
sp_get_charge_schedule_v3 FUNCTION SQL false SETOF record INVOKER
sp_save_calendar_source FUNCTION PLPGSQL false json INVOKER
sp_tr_update_approved_flag FUNCTION PLPGSQL false trigger INVOKER
sp_tr_guest_request_delete_charges FUNCTION PLPGSQL false trigger INVOKER
sp_get_booking_rooms_js FUNCTION SQL false json INVOKER
sp_delete_precheckin_language FUNCTION PLPGSQL false json INVOKER
sp_check_endday_process PROCEDURE PLPGSQL false INVOKER
sp_lock_booking FUNCTION PLPGSQL false bigint INVOKER
sp_get_issue_card_log FUNCTION PLPGSQL false json INVOKER
sp_tr_rsv_pickup_room_revenue FUNCTION PLPGSQL false trigger INVOKER
sp_get_batch_reports_js FUNCTION SQL false json INVOKER
fn_generate_years FUNCTION SQL true SETOF integer INVOKER
fn_lookup FUNCTION SQL true text INVOKER
sp_get_booking_folio_balances_js FUNCTION SQL false json INVOKER
sp_get_group_inhouse_js FUNCTION PLPGSQL false json INVOKER
sp_selfcheckin_add_guests FUNCTION PLPGSQL false integer INVOKER
sp_calc_outstanding FUNCTION PLPGSQL false record INVOKER
sp_recapitulate FUNCTION PLPGSQL false SETOF record INVOKER
sp_rsv_pickup_by_periods_detail_js FUNCTION SQL false json INVOKER
sp_endday_check_auto_post PROCEDURE PLPGSQL false INVOKER
sp_check_booking_record_lock FUNCTION PLPGSQL false json INVOKER
sp_save_guest_document FUNCTION PLPGSQL false integer INVOKER
sp_unblock_rooms FUNCTION PLPGSQL false record INVOKER
sp_log_inhouse FUNCTION PLPGSQL false bigint INVOKER
sp_save_consent_master FUNCTION PLPGSQL false json INVOKER
sp_verify_rsv_pickup FUNCTION SQL false SETOF record INVOKER
fn_current_shift FUNCTION SQL false t_shift INVOKER
sp_search_guest_folio_js FUNCTION PLPGSQL false json INVOKER
sp_get_auto_post_rooms_js FUNCTION SQL false json INVOKER
sp_notify_from_system FUNCTION PLPGSQL false boolean INVOKER
sp_raise_error_if FUNCTION PLPGSQL false void INVOKER
sp_tr_folio_init FUNCTION PLPGSQL false trigger INVOKER
fn_normalize_guest_document_number FUNCTION SQL true text INVOKER
sp_trg_itemizer_before_delete FUNCTION PLPGSQL false trigger INVOKER
sp_get_keycard_issue_info FUNCTION PLPGSQL false SETOF record INVOKER
fn_precheckin_get_legal_document FUNCTION PLPGSQL false jsonb INVOKER
fn_get_inclusion_desc FUNCTION SQL true text INVOKER
sp_productivity_compare FUNCTION PLPGSQL false SETOF record INVOKER
sp_set_session FUNCTION PLPGSQL false void INVOKER
sp_get_sync_roomavail FUNCTION PLPGSQL false jsonb INVOKER
sp_tr_rsv_pickup_rate_revenue FUNCTION PLPGSQL false trigger INVOKER
sp_endday_update_outstanding PROCEDURE SQL false INVOKER
fn_path FUNCTION SQL true text INVOKER
sp_update_guest_inhouse FUNCTION PLPGSQL false record INVOKER
sp_deposit_noshow_charge FUNCTION SQL false json INVOKER
fn_doorlock_demo_mode FUNCTION PLPGSQL false boolean INVOKER
fn_age_range_id FUNCTION SQL false integer INVOKER
sp_run_endday_tasks_xxx PROCEDURE PLPGSQL false INVOKER
sp_replace_more_guest_inhouse_json FUNCTION PLPGSQL false json INVOKER
sp_save_inventory FUNCTION PLPGSQL false smallint INVOKER
sp_create_report_file FUNCTION PLPGSQL false record INVOKER
sp_get_folio_balances_js FUNCTION PLPGSQL false json INVOKER
sp_delete_notifications_user FUNCTION PLPGSQL false void INVOKER
sp_expected_arrival_js FUNCTION SQL false json INVOKER
sp_save_rate_schedule FUNCTION PLPGSQL false record INVOKER
sp_endday_export_register_cards PROCEDURE PLPGSQL false INVOKER
fn_calc_link_rate FUNCTION SQL false t_money INVOKER
sp_save_mealtype_json FUNCTION PLPGSQL false json INVOKER
sp_save_guest FUNCTION PLPGSQL false integer INVOKER
sp_delete_more_guest_inhouse_json FUNCTION PLPGSQL false json INVOKER
sp_delete_consent_details FUNCTION PLPGSQL false json INVOKER
fn_get_room_charge_dept FUNCTION SQL false integer INVOKER
fn_calendar_day_events FUNCTION SQL false json INVOKER
fn_percent FUNCTION SQL true t_percent INVOKER
sp_cancel_checkin FUNCTION PLPGSQL false record INVOKER
fn_get_consent_master_list FUNCTION SQL false json INVOKER
fn_get_mapped_roomtype FUNCTION PLPGSQL false integer INVOKER
fn_match_dows FUNCTION SQL true boolean INVOKER
fn_parse_ics FUNCTION PLPYTHON3U true jsonb INVOKER
sp_get_folio_history_js FUNCTION PLPGSQL false json INVOKER

Closed folio-item history + transactions/logs. i_source: any (default, public first then archive) | active | archive. Adds is_archived per row.
sp_rebuild_guest_history FUNCTION PLPGSQL false SETOF record INVOKER

Rebuild public.guest_history from registration UNION backup.registration (status O only) and recompute the guest roll-up. i_guest_id NULL = every guest. p_rebuild false = report only. Idempotent: re-running produces the same rows. Fills in MISSING stay rows only — existing rows are left untouched (write-once), so a rebuild can never replace real figures with values recomputed from sources that have already been purged. i_force = true overwrites existing rows and is the deliberate repair path. Note it can only recover stays that still exist in public.* or backup.* — stays already purged past ARCHIVE_PURGE_DAYS are gone for good.
sp_calc_requirement_charges FUNCTION SQL false SETOF record INVOKER
sp_raise_error FUNCTION PLPGSQL false void INVOKER
sp_check_shift PROCEDURE PLPGSQL false INVOKER
sp_get_rate_schedule FUNCTION PLPGSQL false SETOF record INVOKER
fn_json_diff FUNCTION SQL false jsonb INVOKER
sp_get_department_json FUNCTION PLPGSQL false json INVOKER
sp_db_change_owner FUNCTION PLPGSQL false void INVOKER
sp_tr_guest_messages_counter FUNCTION PLPGSQL false trigger INVOKER
sp_get_bedtype_js FUNCTION PLPGSQL false json INVOKER
fn_format_error FUNCTION SQL true text INVOKER
sp_trg_department_before_delete FUNCTION PLPGSQL false trigger INVOKER
sp_run_endday_task FUNCTION PLPGSQL false boolean INVOKER
sp_get_rateplan_link_details_js FUNCTION SQL false json INVOKER
sp_log_trans FUNCTION SQL false bigint INVOKER
sp_tr_transactions_folio_item_change FUNCTION PLPGSQL false trigger INVOKER
sp_get_available_rates_json FUNCTION PLPGSQL false json INVOKER
sp_tr_sales_target_user_log FUNCTION PLPGSQL false trigger INVOKER
sp_get_charge_schedule FUNCTION SQL false SETOF record INVOKER
sp_save_guest_document_data FUNCTION PLPGSQL false jsonb INVOKER
fn_result_success FUNCTION SQL false json INVOKER
sp_delete_mealtype_json FUNCTION PLPGSQL false json INVOKER
sp_save_draft FUNCTION SQL false integer INVOKER
fn_current_user FUNCTION PLPGSQL false citext INVOKER
sp_gen_request_charge_schedule FUNCTION PLPGSQL false integer INVOKER
sp_set_sys_param FUNCTION SQL false boolean INVOKER
fn_keycard_staykey FUNCTION SQL true text INVOKER
sp_post_transaction FUNCTION PLPGSQL false json INVOKER
fn_find_available_room FUNCTION SQL false SETOF integer INVOKER
sp_void_transaction FUNCTION SQL false json INVOKER
fn_get_record_lock FUNCTION PLPGSQL false json INVOKER
sp_get_notifications_user_js FUNCTION PLPGSQL false json INVOKER

Modify by KenG (2020/05/19)
sp_log_booking FUNCTION PLPGSQL false void INVOKER
fn_get_precheckin_legal_document_list FUNCTION SQL false json INVOKER
sp_get_rate_breakdown_js FUNCTION SQL false json INVOKER
sp_search_booking_js FUNCTION PLPGSQL false json INVOKER
sp_gen_door_qr FUNCTION PLPGSQL false jsonb INVOKER

ออก QR credential ของ registration (payload = roomno|effect|expire|register_id) render PNG ขึ้น S3 แบบ public แล้วบันทึกแถว credential_type=QR – คืน NULL เมื่อปิด DOORLOCK_QR หรือออกไม่สำเร็จ (fail-soft)
sp_booking_rates_breakdown FUNCTION SQL false SETOF record INVOKER

Booking-side twin of sp_rate_schedule_breakdown (pre-check-in nights).

room = GROSS room-only amount. room_net = same amount with every itemizer on room_net_dept removed (= BASE_PRICE), the basis for REVENUE_CALC_TYPE = 2. room_service / room_tax are the is_service / is_tax slices.

Note rsv_pickup.room_charge is NOT this figure: it comes from booking_items.room_charge, which is the sum of nightly rates and still includes meals.
sp_commit_rate_state FUNCTION PLPGSQL false integer INVOKER
sp_save_guest_js_ex FUNCTION PLPGSQL false integer INVOKER
sp_get_posting_folio_patterns_js FUNCTION SQL false json INVOKER

Get guest’s folio pattern
sp_change_room_status2 FUNCTION PLPGSQL false json INVOKER
fn_prop_name FUNCTION SQL false text INVOKER
sp_get_rate_plans_js FUNCTION SQL false json INVOKER
sp_meal_charges FUNCTION PLPGSQL false SETOF record INVOKER
sp_update_room_charge_schedule FUNCTION PLPGSQL false json INVOKER
fn_plantype_name FUNCTION SQL false text INVOKER
sp_cancel_auto_post FUNCTION PLPGSQL false json INVOKER
sp_tr_registration_doorlock_revoke FUNCTION PLPGSQL false trigger INVOKER
sp_check_out FUNCTION PLPGSQL false json INVOKER

Check out single/multiple rooms by specify either register_id/register_ids[] or folio_id/folio_ids[]

[]: ./null []: ./null
sp_delete_roominfo FUNCTION PLPGSQL false json INVOKER
sp_tr_room_type_init FUNCTION PLPGSQL false trigger INVOKER
sp_log_warning FUNCTION SQL false void INVOKER
sp_recalc_booking_rooms FUNCTION SQL false void INVOKER
sp_save_yield_override FUNCTION PLPGSQL false void INVOKER
sp_auto_post_group_rooms FUNCTION PLPGSQL false record INVOKER
sp_get_available_rates FUNCTION PLPGSQL false SETOF record INVOKER
sp_s3_cleanup_orphans FUNCTION PLPGSQL false jsonb INVOKER
fn_booking_rate_revenue FUNCTION SQL false t_money INVOKER
sp_tr_channel_log FUNCTION PLPGSQL false trigger INVOKER
sp_create_booking FUNCTION PLPGSQL false record INVOKER
sp_unlock_folio FUNCTION PLPGSQL false json INVOKER
sp_get_booking_js FUNCTION PLPGSQL false json INVOKER
fn_endday_register_card_exports FUNCTION SQL false SETOF record INVOKER

List non-cancelled registrations with an arrival date and build deterministic RegisterCard PDF filenames.
fn_test FUNCTION PLPGSQL false text INVOKER
fn_get_block_rooms FUNCTION SQL false text INVOKER
sp_init_room_available FUNCTION PLPGSQL false void INVOKER
fn_revenue_calc_type FUNCTION SQL false smallint INVOKER
sp_room_avail_by_roomtype FUNCTION PLPGSQL false SETOF record INVOKER
sp_delete_consent_master FUNCTION PLPGSQL false json INVOKER
fn_result_error FUNCTION SQL false json INVOKER
fn_presign_or_cached FUNCTION PLPGSQL false text INVOKER
sp_get_guest_document_json FUNCTION PLPGSQL false json INVOKER
sp_execute_endday PROCEDURE PLPGSQL false INVOKER
fn_calc_inclusion_charge FUNCTION SQL true t_money INVOKER
fn_conn_realtime FUNCTION PLPGSQL false citext INVOKER
sp_get_propertyinfo_json FUNCTION PLPGSQL false json INVOKER
fn_get_calendar_events_js FUNCTION PLPGSQL false json INVOKER
sp_tr_booking_items_save_log FUNCTION PLPGSQL false trigger INVOKER
sp_tr_guest_request_gen_charges FUNCTION PLPGSQL false trigger INVOKER
sp_get_room_avail_js FUNCTION SQL false jsonb INVOKER
fn_get_rateplan_children FUNCTION SQL false ARRAY INVOKER
sp_trg_masterfile_before_delete FUNCTION PLPGSQL false trigger INVOKER
sp_tr_room_restriction_touch FUNCTION PLPGSQL false trigger INVOKER
sp_assign_guest_document FUNCTION PLPGSQL false json INVOKER
sp_approve_rates FUNCTION PLPGSQL false record INVOKER
sp_tr_room_init FUNCTION PLPGSQL false trigger INVOKER
sp_change_maid_status FUNCTION PLPGSQL false record INVOKER
sp_update_rsv_pickup FUNCTION PLPGSQL false record INVOKER
sp_user_log FUNCTION SQL false void INVOKER
sp_get_keycard_issue_info_json FUNCTION PLPGSQL false json INVOKER
sp_tr_guest_init_name FUNCTION PLPGSQL false trigger INVOKER
sp_issue_credit_note FUNCTION PLPGSQL false json INVOKER
sp_prop_query FUNCTION PLPGSQL false SETOF record INVOKER
sp_recapitulate_by_date FUNCTION PLPGSQL false SETOF record INVOKER
sp_save_batch_report_options FUNCTION PLPGSQL false json INVOKER
sp_get_scanned_documents_json FUNCTION PLPGSQL false json INVOKER
sp_endday_mark_noshow PROCEDURE PLPGSQL false INVOKER
fn_current_propcode FUNCTION SQL false text INVOKER
fn_guest_document_holders FUNCTION SQL false SETOF integer INVOKER

Guest ids holding a document identity, best-surviving-profile first. Single source of the merge ranking.
sp_change_door_credential FUNCTION PLPGSQL false jsonb INVOKER
sp_save_issued_keycard FUNCTION PLPGSQL false jsonb INVOKER
sp_get_rsv_pickup FUNCTION PLPGSQL false SETOF record INVOKER
sp_unlock_booking_record_lock FUNCTION PLPGSQL false boolean INVOKER
sp_tr_booking_items_gen_item_id FUNCTION PLPGSQL false trigger INVOKER
sp_endday_clear_data PROCEDURE PLPGSQL false INVOKER

Archives completed business data and preserves transaction audit/VAT fields.
sp_void_transactions FUNCTION PLPGSQL false json INVOKER
sp_tr_room_block_status_changed FUNCTION PLPGSQL false trigger INVOKER
sp_cancel_booking FUNCTION PLPGSQL false record INVOKER
sp_group_summary FUNCTION SQL false json INVOKER

Group booking financial summary — returns a flat JSON array of rows. Each row: {row_id, row_type, section, description, booking, checked_in, total, in_house, checked_out}, where each of the five status buckets = {bookacc_qty, bookacc_amt, ownacc_qty, ownacc_amt, total_qty, total_amt}. Bucket derivation: booking=not-yet-checked-in; in_house=registration.status=I; checked_out=registration.status=O; checked_in=in_house+checked_out; total=booking+in_house+checked_out. Sections: inclusive | extra | requirement | grand_total. Detail row_types: room_charge, comp_room, total_room (amt=NULL), abf, lunch, dinner, inclusion (dynamic), extra_bed, extra_abf, extra_lunch, extra_dinner, requirement (dynamic). Subtotal row_types: inclusive_total, extra_total, requirement_total, grand_total. bookacc = charge_to_booking=TRUE, ownacc = charge_to_booking=FALSE. Two optional date axes, aligned with public.transactions: i_post_date = filter by POSTING date (transactions.post_date; the stay night / *_rates.charge_date row); i_charge_date = filter by SERVICE date (transactions.charge_date; ABF posted tonight charges tomorrow, etc.). Both NULL ⇒ whole stay. item_id narrows to one booking_items.item_id (or registration.booking_item).
sp_guest_inhouse_get_available_rate_json FUNCTION PLPGSQL false json INVOKER
sp_get_room_details_js FUNCTION SQL false json INVOKER
sp_center_search_json FUNCTION PLPGSQL false json INVOKER
sp_set_queue_realtime_notify FUNCTION PLPGSQL false boolean INVOKER
sp_revoke_register_credentials FUNCTION PLPGSQL false integer INVOKER
sp_reset_roomavail FUNCTION PLPGSQL false void INVOKER
fn_prop_key FUNCTION SQL false text INVOKER
sp_prop_exec FUNCTION SQL false text INVOKER
fn_selfcheckin_validate_guests FUNCTION PLPGSQL false jsonb INVOKER
sp_get_rate_approval FUNCTION SQL false SETOF record INVOKER
fn_check_range_segments FUNCTION PLPGSQL true jsonb INVOKER
sp_get_block_room_js FUNCTION PLPGSQL false json INVOKER
sp_update_transaction FUNCTION PLPGSQL false json INVOKER
fn_nbis_clean_codes FUNCTION SQL true ARRAY INVOKER
sp_sync_split_folio_departure PROCEDURE PLPGSQL false INVOKER
sp_db_search_ex FUNCTION SQL false SETOF record INVOKER
fn_date_range FUNCTION SQL true SETOF date INVOKER
sp_post_deposit_clearing FUNCTION PLPGSQL false json INVOKER
sp_gen_test_data FUNCTION PLPGSQL false json INVOKER
fn_leadtime_range_json FUNCTION SQL false jsonb INVOKER
sp_recalc_folio_balance FUNCTION SQL false t_money INVOKER
sp_tr_user_logs_init FUNCTION PLPGSQL false trigger INVOKER
sp_check_record_lock PROCEDURE PLPGSQL false INVOKER
sp_lock_folio FUNCTION PLPGSQL false json INVOKER
sp_get_transaction_summary FUNCTION SQL false SETOF record INVOKER
sp_set_current_user FUNCTION PLPGSQL false boolean INVOKER
sp_test_proc PROCEDURE PLPGSQL false INVOKER
sp_endday_gen_batch_report PROCEDURE PLPGSQL false INVOKER
fn_get_target_folio_seq FUNCTION SQL false integer INVOKER
sp_get_photo_user_guest_document FUNCTION PLPGSQL false record INVOKER
sp_tr_department_init FUNCTION PLPGSQL false trigger INVOKER
sp_bulk_update_inventory FUNCTION PLPGSQL false json INVOKER
sp_tr_room_update_room_count FUNCTION PLPGSQL false trigger INVOKER
sp_gen_door_credential FUNCTION PLPGSQL false jsonb INVOKER
fn_charge_object FUNCTION SQL true json INVOKER
fn_weekends FUNCTION SQL false json INVOKER
fn_prop_code FUNCTION SQL false text INVOKER
fn_get_channel_list FUNCTION PLPGSQL false json INVOKER
sp_tr_rate_daily_save_log FUNCTION PLPGSQL false trigger INVOKER

Audit trigger ของ rate_daily → public.rate_logs. ลำดับการหา actor: GUC rate_log.user_name → current.username → session.user_name → ‘System’ จงใจไม่เรียก fn_current_user() เพื่อเลี่ยง fallback fn_get_param(‘DEBUG’,‘USER_NAME’) ที่ seed เป็น ‘@UNKNOWN’ (ClickUp 86d42y50e)
sp_log_cashier FUNCTION SQL false bigint INVOKER
fn_result_warning FUNCTION SQL false json INVOKER
sp_delete_precheckin_legal_document FUNCTION PLPGSQL false json INVOKER
fn_get_bed_image_url FUNCTION SQL true text INVOKER
sp_send_precheckin_invite FUNCTION PLPGSQL false integer INVOKER

ส่งอีเมล template pre-check-in ให้แขกหลักของ booking (individual เท่านั้น) คืนจำนวนฉบับที่ส่งได้ (0 = ไม่ได้ส่ง) — ใช้ร่วมกันโดย sp_amend_booking และ sp_send_precheckin_reminder
fn_match_day FUNCTION SQL true boolean INVOKER
sp_get_booking_rooms_and_guests_js FUNCTION PLPGSQL false json INVOKER
sp_trial_balance FUNCTION PLPGSQL false SETOF record INVOKER
sp_get_chart_data FUNCTION PLPGSQL false jsonb INVOKER
sp_save_department_json FUNCTION PLPGSQL false json INVOKER
fn_qty FUNCTION SQL true text INVOKER
sp_reopen_folio FUNCTION PLPGSQL false json INVOKER
sp_change_room_status FUNCTION PLPGSQL false json INVOKER
sp_get_rate_plans FUNCTION PLPGSQL false json INVOKER
sp_cancel_auto_post_folio FUNCTION PLPGSQL false record INVOKER
sp_more_guest_assign_passport_json FUNCTION PLPGSQL false json INVOKER
sp_unlock_all_register_record_lock_by_user_name FUNCTION PLPGSQL false boolean INVOKER
sp_rsv_booking_pace_daily_js FUNCTION SQL false json INVOKER
fn_mask_credit_card FUNCTION PLPGSQL false text INVOKER
sp_get_endday_process_list_js FUNCTION PLPGSQL false jsonb INVOKER
sp_get_transaction_codes_js FUNCTION SQL false json INVOKER

List all transaction codes according to guest’s folio pattern
sp_get_guest_inhouse_js FUNCTION PLPGSQL false json INVOKER
sp_save_guest_document_id_card_json FUNCTION PLPGSQL false json INVOKER
sp_tr_booking_ensure_group_folio FUNCTION PLPGSQL false trigger INVOKER
sp_tr_register_guests_update_registered_flag FUNCTION PLPGSQL false trigger INVOKER
sp_get_sync_room_restrictions FUNCTION PLPGSQL false jsonb INVOKER
sp_deposit_cancellation_charge FUNCTION SQL false json INVOKER
fn_sellable_statuses FUNCTION SQL false ARRAY INVOKER
sp_save_batch_report FUNCTION SQL false json INVOKER
sp_save_signature FUNCTION PLPGSQL false bigint INVOKER
sp_tr_followup_counter FUNCTION PLPGSQL false trigger INVOKER
sp_inventory_rates FUNCTION PLPGSQL false SETOF record INVOKER
fn_lock_timeout FUNCTION SQL false interval INVOKER
sp_get_rate_comparison_js FUNCTION SQL false json INVOKER
sp_tr_rate_plan_update_parent FUNCTION PLPGSQL false trigger INVOKER
sp_search_transaction_json FUNCTION PLPGSQL false json INVOKER
sp_delete_draft FUNCTION SQL false void INVOKER
sp_send_confirm_email FUNCTION PLPGSQL false boolean INVOKER
sp_tr_files_init FUNCTION PLPGSQL false trigger INVOKER
sp_endday_increment_system_date PROCEDURE PLPGSQL false INVOKER
sp_print_form_header FUNCTION SQL false record INVOKER
sp_rsv_booking_pace_stat FUNCTION PLPGSQL false SETOF record INVOKER
sp_get_roomplan_resources_js FUNCTION SQL false json INVOKER
fn_is_holiday FUNCTION SQL false boolean INVOKER
fn_get_session FUNCTION PLPGSQL false text INVOKER
sp_send_message_to_guests FUNCTION PLPGSQL false integer INVOKER

ส่งอีเมล template ให้แขก – ครบทุกอีเมลใน guest.emails แยกฉบับต่ออีเมล (dedupe case-insensitive); บน dev บังคับ allowlist (@newsoft.co.th หรือ EMAIL_TEST_RECIPIENTS) เหนือ skip_test_mode, EMAIL_TEST_MODE=true redirect ทั้งก้อน / =false ปิดทุกกลไก
sp_get_folio_print_details FUNCTION SQL false SETOF record INVOKER
fn_get_linked_rateplans FUNCTION SQL false ARRAY INVOKER
sp_get_itemizer_amounts FUNCTION SQL false SETOF record INVOKER
fn_leadtime_range FUNCTION SQL false text INVOKER
sp_tr_credit_card_mask FUNCTION PLPGSQL false trigger INVOKER
fn_find_target_folio_item FUNCTION SQL false integer INVOKER

Find target folio item in folio pattern using specfied department id
fn_get_request_end FUNCTION SQL true date INVOKER
fn_period_end FUNCTION SQL true date INVOKER
sp_get_checkin_details_js FUNCTION PLPGSQL false json INVOKER
sp_lock_room FUNCTION PLPGSQL false void INVOKER
sp_tr_booking_after_checkin FUNCTION PLPGSQL false trigger INVOKER
fn_age_range_json FUNCTION SQL false jsonb INVOKER
sp_productivity_stat_js FUNCTION SQL false json INVOKER
fn_get_params FUNCTION SQL false ARRAY INVOKER
sp_get_room_charge_schedule FUNCTION SQL false json INVOKER
sp_map_channels_to_rateplan FUNCTION SQL false json INVOKER
sp_endday_post_room_charges PROCEDURE SQL false INVOKER
sp_send_precheckin_reminder FUNCTION PLPGSQL false jsonb INVOKER

ส่งอีเมล template pre-check-in ซ้ำเป็น reminder ให้ booking individual ที่ arrival = fn_system_date()+1 ต้องเปิดทั้ง PRE-CHECKIN และ SELF-CHECKIN เรียกโดย authen sys.run_property_jobs (job_type precheckin_reminder)
fn_doorlock_validity FUNCTION PLPGSQL false record INVOKER
sp_save_issued_keycard_json FUNCTION PLPGSQL false jsonb INVOKER
sp_tr_registration_deleted FUNCTION PLPGSQL false trigger INVOKER
fn_format_address FUNCTION SQL true text INVOKER
fn_calc_inclusion_qty FUNCTION SQL true integer INVOKER
sp_update_rsv_pickup_amend FUNCTION PLPGSQL false void INVOKER
fn_yield_effective_rate FUNCTION PLPGSQL false t_money INVOKER
sp_save_reservation_more_guest_json FUNCTION PLPGSQL false json INVOKER
sp_authen_logout_all FUNCTION SQL false integer INVOKER
sp_get_reports_list_js FUNCTION PLPGSQL false json INVOKER
sp_merge_inhouse_guest_folio FUNCTION PLPGSQL false jsonb INVOKER

ยุบ registration ที่แยกออกมาด้วย sp_split_inhouse_guest_folio กลับเป็น more guest ของห้องต้นทาง ทำได้เฉพาะ primary-only follower ที่ยังไม่มี transaction ในโฟลิโอ (ไม่นับรายการ void) registration ที่ยุบถูกตั้ง status = X เพื่อคง audit trail คืน jsonb {register_id, merged_register_id, guest_id}
sp_auto_post_rooms PROCEDURE PLPGSQL false INVOKER
sp_generate_recapitulate FUNCTION PLPGSQL false record INVOKER
sp_expected_departure_js FUNCTION SQL false json INVOKER
fn_get_calendars_js FUNCTION PLPGSQL false json INVOKER
sp_rate_discrepancy FUNCTION SQL false SETOF record INVOKER
sp_seven_days_forecast_js FUNCTION SQL false json INVOKER
sp_get_keycard_issue_logs_json FUNCTION PLPGSQL false json INVOKER
sp_lock_group_exclusive FUNCTION PLPGSQL false void INVOKER
sp_delete_channel FUNCTION PLPGSQL false json INVOKER
sp_tr_guest_fullname_changed FUNCTION PLPGSQL false trigger INVOKER
sp_tr_deposit_status_changed FUNCTION PLPGSQL false trigger INVOKER
sp_group_check_out_old FUNCTION PLPGSQL false json INVOKER
fn_result FUNCTION SQL false json INVOKER
sp_save_folio_data FUNCTION PLPGSQL false jsonb INVOKER
sp_tr_bed_type_fix_image_url FUNCTION PLPGSQL false trigger INVOKER
sp_check_charge_schedule PROCEDURE PLPGSQL false INVOKER
sp_salestax_save_hotel_profile FUNCTION PLPGSQL false json INVOKER
fn_get_request_nights FUNCTION SQL true integer INVOKER
sp_get_folio_print_header FUNCTION SQL false SETOF record INVOKER
sp_endday_post_requirement_charges PROCEDURE SQL false INVOKER
sp_get_address_lines FUNCTION PLPGSQL true record INVOKER
sp_selfcheckin_release_room FUNCTION PLPGSQL false boolean INVOKER
sp_tr_booking_avail_change FUNCTION PLPGSQL false trigger INVOKER
sp_meal_plan FUNCTION PLPGSQL false SETOF record INVOKER
sp_get_recapitulatexxx FUNCTION PLPGSQL false SETOF record INVOKER
fn_split_period FUNCTION SQL true record INVOKER
sp_check_permission FUNCTION SQL false boolean INVOKER
sp_update_transaction_summary FUNCTION PLPGSQL false record INVOKER
sp_today_stat_js FUNCTION SQL false json INVOKER
sp_save_department_group_json FUNCTION PLPGSQL false json INVOKER
fn_sys_param FUNCTION PLPGSQL false time without time zone INVOKER
sp_get_vat_info FUNCTION PLPGSQL false SETOF record INVOKER
sp_lock_group_envelope FUNCTION PLPGSQL false void INVOKER
sp_split_inhouse_guest_folio FUNCTION PLPGSQL false jsonb INVOKER

แยก more guest (register_guests) ของ registration ที่ in-house ออกเป็น registration + folio ของตัวเอง แบบ share room primary-only (share_rate = false): ตัวใหม่ได้ adult = 0 และ registration_rates = 0 ทุกคอลัมน์ ค่าห้องยังอยู่ที่ห้องต้นทางทั้งหมด คืน jsonb {register_id, folio_id, shared_register_id, room_id, guest_id}
fn_selected_items FUNCTION PLPGSQL false text INVOKER
sp_log_roomstatus FUNCTION PLPGSQL false void INVOKER
sp_change_block_room FUNCTION PLPGSQL false json INVOKER
fn_user_attribute FUNCTION SQL false text INVOKER
fn_online_shift FUNCTION SQL false t_shift INVOKER
sp_check_avail_diff FUNCTION PLPGSQL false SETOF record INVOKER
sp_recalc_yield_rates FUNCTION PLPGSQL false integer INVOKER
sp_regenerate_rsv_pickup FUNCTION PLPGSQL false SETOF record INVOKER
fn_default FUNCTION SQL false time without time zone INVOKER
sp_append_log FUNCTION PLPGSQL false bigint INVOKER
sp_change_booking_status FUNCTION PLPGSQL false record INVOKER
sp_selfcheckin_release_expired FUNCTION PLPGSQL false integer INVOKER
sp_get_inventory_by_channel_js FUNCTION PLPGSQL false json INVOKER
sp_save_keycard_log FUNCTION PLPGSQL false jsonb INVOKER
sp_calc_rsv_pickup FUNCTION SQL false SETOF record INVOKER
sp_template_function FUNCTION PLPGSQL false json INVOKER
sp_endday_clear_guest_data FUNCTION PLPGSQL false integer INVOKER

Purges expired scan-pool rows and unreferenced guests using configurable retention.
sp_auto_post FUNCTION PLPGSQL false json INVOKER
fn_itemizer_breakdown FUNCTION SQL false SETOF record INVOKER

Splits a tax/service-INCLUSIVE amount into base (= BASE_PRICE) + service + tax + other using the itemizer codes given (normally department.itemizers).

base is the same number sp_post_transaction stores as the BASE_PRICE entry of transactions.itemizers, so callers reconcile with itemizer_summary exactly.

Two behaviours worth knowing: * sp_get_itemizer_amounts returns BASE_PRICE = NULL when the code array is empty or NULL (its residual is sum() over an empty set), so base coalesces back to p_amount. A department with no itemizers therefore yields base = gross, never NULL. * The arithmetic is NOT linear in p_amount: flat-amount itemizers (itemizer.rate IS NULL) are charged per unit, so base(2x) <> 2*base(x). Never precompute a per-department ratio and multiply – always call with the real amount.

Do not reimplement the arithmetic here; sp_calc_itemizers / fn_calc_itemizers / fn_itemizer use a naive per-itemizer model that ignores is_service/is_tax ordering and calc_on_baseprice and disagrees with what is stored on transactions.
fn_s3_referenced_paths FUNCTION SQL false SETOF text INVOKER
fn_array_item FUNCTION SQL true text INVOKER
fn_get_request_start FUNCTION SQL true date INVOKER
fn_booking_room_net_revenue FUNCTION SQL false t_money INVOKER

SUM(sp_booking_rates_breakdown.room_net) for one booking item = the REVENUE_CALC_TYPE = 2 basis at pickup time. Summed per night without multiplying room_qty, matching fn_booking_rate_revenue.

Takes only booking_item. It still passes i_booking_id down to the breakdown – leaving that NULL makes the nested sp_meal_plan run unfiltered across every booking (254 ms per item versus 55 ms on aonestar) – but it reads the id from booking_items instead of trusting the caller, because rsv_pickup.booking_id drifts from the item’s real booking after a rebook.

Returns NULL when the item no longer exists or has no rate rows; callers fall back to the gross figure.
sp_save_precheckin_language FUNCTION PLPGSQL false json INVOKER
sp_calc_extra FUNCTION PLPGSQL false record INVOKER
sp_save_rate_calendar FUNCTION PLPGSQL false SETOF record INVOKER
sp_get_user_logs_js FUNCTION PLPGSQL false json INVOKER
sp_get_folio_details_js FUNCTION PLPGSQL false json INVOKER
sp_rsv_pickup_stat FUNCTION PLPGSQL false SETOF record INVOKER
fn_money_div FUNCTION SQL true t_money INVOKER
sp_get_department_group_json FUNCTION PLPGSQL false json INVOKER
sp_trg_department_before_update FUNCTION PLPGSQL false trigger INVOKER
sp_unblock_all FUNCTION PLPGSQL false record INVOKER
sp_get_room_activity FUNCTION PLPGSQL false SETOF record INVOKER
fn_leadtime_range_id FUNCTION SQL false integer INVOKER
fn_doorlock_credential FUNCTION SQL false jsonb INVOKER

ประกอบ credential ที่ใช้งานได้ (status=P) ของ registration จากแถว PIN + แถว QR ให้เป็น jsonb ก้อนเดียวสำหรับส่งอีเมล/ตอบ RPC – คืน NULL เมื่อไม่มีเลย
sp_tr_guest_identity_guard FUNCTION PLPGSQL false trigger INVOKER
sp_tr_deposit_init FUNCTION PLPGSQL false trigger INVOKER
sp_lock_registration FUNCTION PLPGSQL false bigint INVOKER
fn_period FUNCTION PLPGSQL true record INVOKER
fn_get_room_activity FUNCTION SQL false integer INVOKER
fn_round_rate FUNCTION SQL true t_money INVOKER
fn_age_range FUNCTION SQL false text INVOKER
sp_tr_bind_registration_signature FUNCTION PLPGSQL false trigger INVOKER
fn_get_ooo_desc FUNCTION SQL false text INVOKER
sp_deposit_outstanding FUNCTION PLPGSQL false record INVOKER
fn_find_guest_by_document FUNCTION SQL false integer INVOKER
sp_get_meal_qty FUNCTION PLPGSQL false record INVOKER
sp_handle_error FUNCTION PLPGSQL false text INVOKER
sp_error_if FUNCTION PLPGSQL false void INVOKER
fn_attachment_cleanup_orphans FUNCTION SQL false jsonb INVOKER
sp_get_room_avail_detail_js FUNCTION PLPGSQL false jsonb INVOKER
sp_get_followup_js FUNCTION PLPGSQL false json INVOKER

Create by KenG (2020/03/12) # Return json # Condition return - In case 1: all value null will returnn all followup data - In case 2: if have followup_id only -> will return followup and comments where id of followup table - In case 3: if have booking_id only -> will return followup and comments where ref_type = booking and ref_id = booking_id - In case 4: if have register_id only -> will return followup and comments where ref_type = register and ref_id = register_id - In case 5: if have assign_user only -> will return followup and comments where assign_users that have param assign_user (get all followup that assign that user)
sp_get_sys_params_js FUNCTION SQL false json INVOKER
sp_save_rates_and_restrictions FUNCTION PLPGSQL false smallint INVOKER
sp_get_booking_charges FUNCTION SQL false SETOF record INVOKER
sp_update_folio_note FUNCTION PLPGSQL false json INVOKER
sp_occupancy_forecast FUNCTION PLPGSQL false SETOF record INVOKER
fn_get_precheckin_consent_logs_list FUNCTION SQL false json INVOKER
sp_create_booking_folio FUNCTION PLPGSQL false integer INVOKER
sp_tr_room_block_avail_change FUNCTION PLPGSQL false trigger INVOKER
sp_tr_guest_request_save_log FUNCTION PLPGSQL false trigger INVOKER
sp_trg_itemizer_before_update FUNCTION PLPGSQL false trigger INVOKER
sp_save_registration_rates FUNCTION PLPGSQL false record INVOKER
sp_tr_registration_regenerate_charges FUNCTION PLPGSQL false trigger INVOKER
sp_verify_folio FUNCTION PLPGSQL false json INVOKER
sp_productivity FUNCTION PLPGSQL false SETOF record INVOKER
sp_get_users FUNCTION PLPGSQL false SETOF record INVOKER
sp_update_registration_summary FUNCTION PLPGSQL false void INVOKER
fn_guest_document_country FUNCTION SQL true integer INVOKER
fn_match_value FUNCTION SQL true boolean INVOKER
sp_seven_days_forecast FUNCTION SQL false SETOF record INVOKER
fn_nbis_sync_json FUNCTION PLPGSQL false jsonb INVOKER
sp_update_avail FUNCTION SQL false void INVOKER
fn_intf_param FUNCTION SQL false text INVOKER
sp_cancel_checkout FUNCTION PLPGSQL false json INVOKER
sp_rsv_pickup_stat_today_js FUNCTION SQL false json INVOKER
sp_check_transaction PROCEDURE PLPGSQL false INVOKER
fn_get_mapped_rateplan FUNCTION PLPGSQL false integer INVOKER
sp_log_rate FUNCTION PLPGSQL false bigint INVOKER
sp_unblock_rooms_old FUNCTION PLPGSQL false record INVOKER
sp_create_folio_item FUNCTION PLPGSQL false integer INVOKER
sp_get_outstanding FUNCTION PLPGSQL false record INVOKER
fn_inhouse_summary_js FUNCTION PLPGSQL false json INVOKER
sp_log_room FUNCTION SQL false bigint INVOKER
sp_occupancy_calendar_js FUNCTION PLPGSQL false json INVOKER
fn_nbis_ota_mapping_json FUNCTION SQL false jsonb INVOKER
fn_result_confirm FUNCTION SQL false json INVOKER
sp_try_lock_room FUNCTION PLPGSQL false boolean INVOKER
fn_open_cashsale_folio FUNCTION PLPGSQL false integer INVOKER
sp_delete_batch_report FUNCTION PLPGSQL false json INVOKER
sp_tr_booking_items_avail_change FUNCTION PLPGSQL false trigger INVOKER
sp_get_guest_list_js FUNCTION SQL false json INVOKER
fn_length FUNCTION SQL true integer INVOKER
sp_get_draft FUNCTION SQL false json INVOKER
sp_get_roomplan_events_js FUNCTION PLPGSQL false json INVOKER
sp_log_info FUNCTION SQL false void INVOKER
sp_trg_vip_before_delete FUNCTION PLPGSQL false trigger INVOKER
sp_send_mail FUNCTION PLPGSQL false integer INVOKER

Send email via sp_send_message (centralized messaging system). Original hardcoded SMTP credentials removed for security.
sp_tr_registration_avail_change FUNCTION PLPGSQL false trigger INVOKER
sp_get_rate_summary_js FUNCTION SQL false json INVOKER
fn_doorlock_pin_block FUNCTION PLPGSQL false text INVOKER

ประกอบ HTML fragment ของ PIN / ประตูส่วนกลาง / ประตูห้องเชื่อม / QR สำหรับ token {{pin_block}} ในอีเมล guest-welcome คืน '' เมื่อไม่มีทั้ง pin_code และ qr_url (ห้ามคืน NULL)
sp_gen_namelist_guests FUNCTION PLPGSQL false integer INVOKER
sp_save_guest_js FUNCTION PLPGSQL false integer INVOKER
sp_check_out_old FUNCTION PLPGSQL false json INVOKER
sp_get_guest_folio_pattern_json FUNCTION PLPGSQL false jsonb INVOKER
sp_tr_department_group_init FUNCTION PLPGSQL false trigger INVOKER
sp_endday_recalc_yield_rates PROCEDURE PLPGSQL false INVOKER
sp_check_in_group FUNCTION PLPGSQL false json INVOKER
fn_map_guest_document_fields FUNCTION PLPGSQL false jsonb INVOKER
fn_check_endday_processing FUNCTION SQL false boolean INVOKER
fn_quantity FUNCTION SQL true text INVOKER
sp_search_guest_in_house_js FUNCTION PLPGSQL false json INVOKER
fn_get_channel_detail FUNCTION PLPGSQL false json INVOKER
sp_check_register_record_lock FUNCTION PLPGSQL false json INVOKER
sp_verify_rate_and_available FUNCTION PLPGSQL false json INVOKER
fn_doorlock_api_url FUNCTION SQL false text INVOKER

derive URL ของ endpoint อื่นจาก DOORLOCK_PIN.api_url (…/addpasscode -> …/staychange) คืน NULL เมื่อยังไม่ตั้ง api_url
sp_save_rateplan_channel_mapping FUNCTION PLPGSQL false json INVOKER
fn_period_start FUNCTION SQL true date INVOKER
sp_import_ics FUNCTION PLPGSQL false json INVOKER
sp_get_checkin_summary_js FUNCTION SQL false json INVOKER
sp_get_rateplan_mapping_channel_json FUNCTION PLPGSQL false json INVOKER
sp_trg_room_before_update_connecting_room FUNCTION PLPGSQL false trigger INVOKER
sp_update_outstanding FUNCTION PLPGSQL false record INVOKER
fn_sales_target_segment_name FUNCTION SQL false text INVOKER
fn_random_range FUNCTION PLPGSQL false integer INVOKER
sp_daily_clear_all_intf_state PROCEDURE PLPGSQL false INVOKER
sp_rsv_booking_pace_compare_js FUNCTION PLPGSQL false json INVOKER
fn_format_rate_schedule FUNCTION SQL false text INVOKER
sp_tr_avail_reset FUNCTION PLPGSQL false trigger INVOKER
sp_change_bedtype FUNCTION PLPGSQL false record INVOKER
sp_delete_calendar_event FUNCTION PLPGSQL false json INVOKER
sp_check_room_available FUNCTION PLPGSQL false record INVOKER
fn_handle_error FUNCTION PLPGSQL false json INVOKER
sp_get_rate_plans_js_old FUNCTION PLPGSQL false json INVOKER
sp_guest_merge_many FUNCTION PLPGSQL false integer INVOKER

Collapse several guest profiles into one destination. All-or-nothing: raises on the first failure.
sp_trg_room_after_update_connecting_room FUNCTION PLPGSQL false trigger INVOKER
sp_get_posting_departments_js FUNCTION SQL false json INVOKER

List all departments according to guest’s folio pattern
fn_prop_config FUNCTION SQL false text INVOKER
sp_save_guest_folio_pattern FUNCTION PLPGSQL false jsonb INVOKER
sp_change_room_status_old FUNCTION PLPGSQL false record INVOKER
sp_tr_guest_doorlock_email_changed FUNCTION PLPGSQL false trigger INVOKER

เซ็ตอีเมลของแขกเปลี่ยน -> ส่ง guest-welcome พร้อม PIN/QR ที่มีอยู่ให้ทุก registration ที่แขกเป็นแขกหลักและยัง in-house (ไม่ออก credential ใบใหม่, fail-soft)
fn_nbis_settings_json FUNCTION PLPGSQL false jsonb INVOKER
fn_itemizer_base_amount FUNCTION SQL false numeric INVOKER

Convenience wrapper over fn_itemizer_breakdown for callers that only hold a dept_id and only want the net (BASE_PRICE) figure. Returns p_amount unchanged when the department has no itemizers or does not exist. Returns raw numeric (not t_money) so callers can sum first and cast once – casting per night loses satang against itemizer_summary.
sp_get_rate_approval_js FUNCTION SQL false json INVOKER
fn_doorlock_roomno FUNCTION SQL false text INVOKER
sp_get_inventory_logs FUNCTION SQL false SETOF record INVOKER
fn_room_number FUNCTION SQL false text INVOKER
fn_calc_itemizers FUNCTION SQL false record INVOKER
fn_get_itemizer_amounts FUNCTION SQL false jsonb INVOKER
sp_get_booking_js_ex FUNCTION PLPGSQL false json INVOKER
sp_property_daily_cleanup FUNCTION PLPGSQL false jsonb INVOKER
fn_map FUNCTION SQL false anyelement INVOKER
sp_update_out_of_order FUNCTION PLPGSQL false record INVOKER
sp_clear_intf_rate_state PROCEDURE PLPGSQL false INVOKER
sp_close_cashsale_folio FUNCTION PLPGSQL false json INVOKER
sp_replace_guest_assign_passport_json FUNCTION SQL false json INVOKER
sp_close_folio FUNCTION PLPGSQL false json INVOKER
sp_get_user_logs FUNCTION SQL false SETOF record INVOKER
sp_get_charge_schedule_js FUNCTION SQL false json INVOKER
sp_parse_report_param FUNCTION PLPGSQL false jsonb INVOKER
sp_tr_registration_doorlock_change FUNCTION PLPGSQL false trigger INVOKER
sp_get_reports_option_js FUNCTION PLPGSQL false jsonb INVOKER
sp_save_precheckin_legal_document FUNCTION PLPGSQL false json INVOKER
sp_tr_booking_departure_change FUNCTION PLPGSQL false trigger INVOKER
sp_mealplan_forecast FUNCTION SQL false SETOF record INVOKER
fn_work_shift FUNCTION PLPGSQL false t_shift INVOKER

คืน work shift ปัจจุบันของ user จาก authen.user_property.work_shift (ผ่าน dblink). Memoize ผลลัพธ์ระดับ transaction ผ่าน GUC cache.work_shift (is_local) และคืน NULL พร้อม WARNING เมื่อต่อ authen ไม่ได้ แทนการโยน exception
sp_merge_guest_by_document FUNCTION PLPGSQL false integer INVOKER

Resolve a scanned document to one guest profile, collapsing duplicates. Fail-soft: falls back to redirect.
sp_get_inventory_js FUNCTION PLPGSQL false json INVOKER
sp_get_room_info_js FUNCTION SQL false json INVOKER
sp_get_booking_rooms_and_guests_js2 FUNCTION SQL false json INVOKER
sp_tr_out_of_order_avail_change FUNCTION PLPGSQL false trigger INVOKER
sp_run_batch_report FUNCTION SQL false integer INVOKER
sp_get_batch_report_details_js FUNCTION PLPGSQL false json INVOKER
fn_system_date FUNCTION SQL false date INVOKER
sp_endday_update_room_status PROCEDURE PLPGSQL false INVOKER
sp_endday_gen_transaction_summary PROCEDURE SQL false INVOKER
sp_verify_rate_and_available2 FUNCTION PLPGSQL false record INVOKER
sp_save_calendar_event FUNCTION PLPGSQL false json INVOKER
sp_update_endday_task FUNCTION PLPGSQL false boolean INVOKER
fn_get_vat_info_js FUNCTION PLPGSQL false jsonb INVOKER
sp_update_propertyinfo_json FUNCTION PLPGSQL false json INVOKER
sp_search_guest_js FUNCTION PLPGSQL false json INVOKER
sp_tr_room_activity_update_usage FUNCTION PLPGSQL false trigger INVOKER
sp_check_tax_invoice_issued PROCEDURE PLPGSQL false INVOKER
sp_export_register_cards PROCEDURE PLPGSQL false INVOKER

Export Register Card PDFs for non-cancelled registrations with the specified arrival date.
sp_get_booking_summary_js FUNCTION PLPGSQL false jsonb INVOKER
fn_prop_id FUNCTION SQL false integer INVOKER
fn_guest_document_path_from_url FUNCTION PLPGSQL false text INVOKER
sp_guest_merge FUNCTION PLPGSQL false void INVOKER

Merge one guest profile into another, re-pointing every referencing table. Raises on error.
sp_endday_gen_recapitulate PROCEDURE SQL false INVOKER
sp_generate_itemizer_summary FUNCTION PLPGSQL false record INVOKER
sp_db_reset_data PROCEDURE PLPGSQL false INVOKER
fn_query_to_json FUNCTION PLPGSQL false json INVOKER
sp_finish_log FUNCTION SQL false void INVOKER
sp_unconfirm_booking FUNCTION PLPGSQL false record INVOKER
sp_update_rategroup_by_id_json FUNCTION PLPGSQL false json INVOKER
sp_call_rpc FUNCTION SQL false json INVOKER

LEGACY — เรียก sys.call_rpc ต่อ; โค้ดใหม่ให้ใช้ sys.call_rpc / sys.call_rpc_async โดยตรง
fn_get_mapped_channel FUNCTION PLPGSQL false integer INVOKER
sp_endday_update_room_summary PROCEDURE SQL false INVOKER
sp_get_occupancy_calendar FUNCTION PLPGSQL false SETOF record INVOKER
sp_get_params FUNCTION PLPGSQL false SETOF record INVOKER
sp_test_endday_task PROCEDURE PLPGSQL false INVOKER
sp_refresh_guest_stat FUNCTION PLPGSQL false integer INVOKER

Recompute guest.visit_count / visit_nights / last_arrival / last_departure / total_revenue / revenues from public.guest_history (assignment, not +=). i_guest_id NULL = every guest. Zeroes out guests that have no stay left, and only writes rows whose values actually change. guest.revenues keeps its existing {category, amount} shape so the JSON_VALUE jsonpath readers stay valid. Callers: sp_update_guest_stat, sp_rebuild_guest_history, rpc.guest_merge.
sp_upsert_guest_history FUNCTION PLPGSQL false integer INVOKER

Hot-path writer for public.guest_history — reads public.* ONLY, never backup.* (a registration still present in public.registration always has its folio, transactions, rates and requests in public too: sp_endday_clear_data archives a registration folio together with its registration and its atomic-set guard drops any registration whose transactions reference outside the batch). Returns > 0 when a stay row was written, < 0 when one was removed because the registration is no longer checked out (cancel-checkout), 0 when nothing to do. Write-once: an existing row is never overwritten — the deliberate repair path is sp_rebuild_guest_history(i_force := true).
fn_get_rateplan_parents FUNCTION SQL false ARRAY INVOKER
sp_tr_group_info_name_changed FUNCTION PLPGSQL false trigger INVOKER
sp_tr_booking_update_booking_name FUNCTION PLPGSQL false trigger INVOKER
sp_tr_sales_target_protect_master FUNCTION PLPGSQL false trigger INVOKER
fn_validate_shift FUNCTION SQL false t_shift INVOKER
sp_change_ooo_period FUNCTION PLPGSQL false json INVOKER
sp_rsv_pickup_compare FUNCTION PLPGSQL false SETOF record INVOKER
sp_checkin_split_folio FUNCTION PLPGSQL false jsonb INVOKER
sp_search_room_js FUNCTION PLPGSQL false json INVOKER
sp_call_accsys_api FUNCTION PLPGSQL false json INVOKER
sp_tr_notify_minibar_status_change FUNCTION PLPGSQL false trigger INVOKER
fn_get_guest_revenues_js FUNCTION SQL false jsonb INVOKER
sp_get_room_available FUNCTION SQL false SETOF record INVOKER
sp_rate_calendar_js FUNCTION PLPGSQL false json INVOKER
sp_parse_guest_name FUNCTION PLPGSQL false record INVOKER
fn_generate_dropdown_items FUNCTION PLPGSQL false json INVOKER
sp_tr_sales_target_validate_segment FUNCTION PLPGSQL false trigger INVOKER
sp_get_room_summary FUNCTION PLPGSQL false SETOF record INVOKER
sp_delete_calendar_source FUNCTION PLPGSQL false json INVOKER
sp_auto_post_folio FUNCTION PLPGSQL false record INVOKER
sp_update_guest_stat FUNCTION PLPGSQL false record INVOKER

Sync one registration into public.guest_history then recompute that guest’s roll-up columns. Idempotent — safe to call repeatedly for the same register_id (the old additive version double-counted). Called from sp_check_out and sp_cancel_checkout; when the registration is no longer checked out the stay row is removed and the roll-up shrinks accordingly.
fn_period_to_str FUNCTION SQL true text INVOKER
sp_tr_followup_alert FUNCTION PLPGSQL false trigger INVOKER
sp_get_checkout_balances_js FUNCTION PLPGSQL false json INVOKER
fn_booking_summary_js FUNCTION PLPGSQL false json INVOKER
sp_check_folio PROCEDURE PLPGSQL false INVOKER
sp_change_departure FUNCTION PLPGSQL false json INVOKER
sp_system_notify FUNCTION SQL false bigint INVOKER
sp_get_registration_summary_js FUNCTION SQL false json INVOKER
sp_endday_check_non_extensions PROCEDURE PLPGSQL false INVOKER
fn_age_str FUNCTION SQL false text INVOKER
fn_get_param FUNCTION SQL false text INVOKER
fn_guest_document_guest_js FUNCTION PLPGSQL false jsonb INVOKER
fn_filter_reports_by_enabled_interfaces FUNCTION SQL false jsonb INVOKER
sp_monthly_revenue FUNCTION SQL false SETOF record INVOKER
sp_tr_booking_save_log FUNCTION PLPGSQL false trigger INVOKER
sp_change_online_shift FUNCTION SQL false boolean INVOKER
sp_delete_rategroup_by_id_json FUNCTION PLPGSQL false json INVOKER
sp_tr_registration_request_gen_charges FUNCTION PLPGSQL false trigger INVOKER
sp_change_stay_room FUNCTION PLPGSQL false json INVOKER
fn_user_email FUNCTION SQL false text INVOKER
sp_trg_vip_before_update FUNCTION PLPGSQL false trigger INVOKER
sp_open_cashsale_folio FUNCTION PLPGSQL false json INVOKER
sp_endday_gen_itemizer_summary PROCEDURE SQL false INVOKER
sp_save_guests_js FUNCTION SQL false ARRAY INVOKER
sp_convert_supper_block FUNCTION PLPGSQL false record INVOKER
sp_check_out_booking_old FUNCTION PLPGSQL false json INVOKER
fn_get_transaction_js FUNCTION SQL false json INVOKER
fn_nbis_cm_config FUNCTION SQL false jsonb INVOKER
fn_room_layout_json FUNCTION SQL false jsonb INVOKER
sp_save_guest_document_passport_json FUNCTION PLPGSQL false json INVOKER
sp_add_out_of_order FUNCTION PLPGSQL false record INVOKER
sp_split_transaction FUNCTION PLPGSQL false json INVOKER
sp_update_booking_summary FUNCTION PLPGSQL false void INVOKER
fn_get_precheckin_legal_acceptance_list FUNCTION SQL false json INVOKER
fn_get_calendar_sources_js FUNCTION PLPGSQL false json INVOKER
sp_log_guest FUNCTION SQL false bigint INVOKER
sp_tr_update_system_params FUNCTION PLPGSQL false trigger INVOKER
sp_map_rateplans_to_channel FUNCTION PLPGSQL false json INVOKER
sp_auto_post_requirement_charges FUNCTION PLPGSQL false record INVOKER
fn_get_consent_details_list FUNCTION SQL false json INVOKER
sp_gen_booking_rates PROCEDURE PLPGSQL false INVOKER
sp_selfcheckin_block_room FUNCTION PLPGSQL false integer INVOKER
fn_fullname FUNCTION PLPGSQL false text INVOKER
sp_addnew_guest_assign_passport_json FUNCTION SQL false json INVOKER
fn_conn_authen FUNCTION PLPGSQL false citext INVOKER
sp_unlock_register_record_lock FUNCTION PLPGSQL false boolean INVOKER
sp_update_cashsale_folio FUNCTION PLPGSQL false json INVOKER
sp_block_rooms FUNCTION PLPGSQL false record INVOKER
sp_save_rategroup_json FUNCTION PLPGSQL false json INVOKER
sp_auto_post_room_charges FUNCTION PLPGSQL false record INVOKER
sp_db_import_roomrecap PROCEDURE PLPGSQL false INVOKER
sp_get_guest_js FUNCTION SQL false json INVOKER
sp_tr_followup_notificattion FUNCTION PLPGSQL false trigger INVOKER
sp_get_guest_stat FUNCTION SQL false SETOF record INVOKER
sp_auto_block_rooms FUNCTION PLPGSQL false json INVOKER
sp_calc_meal FUNCTION PLPGSQL false numeric INVOKER
sp_check_folio_item PROCEDURE PLPGSQL false INVOKER
fn_pax FUNCTION SQL true text INVOKER
sp_get_rate_inclusions FUNCTION SQL false SETOF record INVOKER
sp_get_folio_printing_items FUNCTION SQL false SETOF record INVOKER
sp_get_rateplan_amount FUNCTION PLPGSQL false t_money INVOKER
sp_transfer_transactions FUNCTION PLPGSQL false json INVOKER

Transfer transactions to another folio
sp_endday_check_city_ledger PROCEDURE PLPGSQL false INVOKER

End day precheck C08: บล็อก end day เมื่อยังมี folio window ที่มีรายการ city ledger (department_group.category = ‘city’) แต่ยังไม่ถูกปิด ทำงานเมื่อ system param CASHIER.ALWAYS_CLOSE_CITY_LEDGER เปิดเท่านั้น
sp_save_guest_requests FUNCTION PLPGSQL false record INVOKER
fn_booking_room_gross_revenue FUNCTION SQL false t_money INVOKER

SUM(sp_booking_rates_breakdown.room) for one booking item = the REVENUE_CALC_TYPE = 1 basis (Gross Room) at pickup time. Summed per night without multiplying room_qty, matching fn_booking_rate_revenue and fn_booking_room_net_revenue.

Returns NULL when the item no longer exists or has no rate rows; callers fall back to rsv_pickup.room_charge.
fn_get_request_qty FUNCTION PLPGSQL true integer INVOKER
sp_process_booking FUNCTION PLPGSQL false SETOF record INVOKER
sp_search_transaction_js FUNCTION SQL false json INVOKER
sp_get_scan_inhouse_json FUNCTION SQL false json INVOKER
sp_save_channel FUNCTION PLPGSQL false json INVOKER
fn_group_name_in_use FUNCTION PLPGSQL false boolean INVOKER
fn_accommodation FUNCTION SQL true text INVOKER
sp_query_logs FUNCTION SQL false SETOF sys_log INVOKER
fn_lookup_values FUNCTION PLPGSQL false text INVOKER
sp_update_mealtype_by_id_json FUNCTION PLPGSQL false json INVOKER
sp_get_recapitulate FUNCTION PLPGSQL false SETOF record INVOKER
fn_month_end FUNCTION SQL false date INVOKER
fn_rate_link_description FUNCTION SQL false text INVOKER
sp_save_draft_booking_js FUNCTION PLPGSQL false jsonb INVOKER
fn_get_params_old FUNCTION SQL false ARRAY INVOKER
sp_save_calendar FUNCTION PLPGSQL false json INVOKER
fn_s3_prop_prefix FUNCTION SQL false text INVOKER
fn_nbis_mapping_json FUNCTION SQL false jsonb INVOKER
sp_tr_registration_save_log FUNCTION PLPGSQL false trigger INVOKER
sp_save_rate_plan FUNCTION PLPGSQL false json INVOKER
sp_delete_department_group_json FUNCTION PLPGSQL false json INVOKER
sp_log_error FUNCTION SQL false void INVOKER
sp_revoke_door_credential FUNCTION PLPGSQL false jsonb INVOKER
sp_tr_room_validate_roomno FUNCTION PLPGSQL false trigger INVOKER
sp_get_online_shift FUNCTION PLPGSQL false text INVOKER
fn_get_precheckin_language_list FUNCTION SQL false json INVOKER
fn_apply_yield FUNCTION SQL false t_money INVOKER
sp_log_endday PROCEDURE PLPGSQL false INVOKER
sp_trg_registration_before_update_extend_stay FUNCTION PLPGSQL false trigger INVOKER
sp_enqueue_realtime FUNCTION PLPGSQL false boolean INVOKER
sp_db_search FUNCTION SQL false SETOF record INVOKER
sp_set_param FUNCTION PLPGSQL false boolean INVOKER
fn_format_log FUNCTION PLPGSQL false text INVOKER
sp_trg_masterfile_before_update FUNCTION PLPGSQL false trigger INVOKER
sp_backfill_revenue_room_net PROCEDURE PLPGSQL false INVOKER

Fills revenue_room_net on room_summary, recapitulate, guest_history and rsv_pickup for any row that still has NULL, optionally limited to a date window. Idempotent – every statement filters on IS NULL, so re-running costs one empty pass.

V20260916.10.2 already backfills inline, so this is not needed for a normal upgrade. Reach for it when a property is restored from a pre-migration dump, or when a data fix means a date range should be recomputed – clear the columns for those dates first, then call it with the window.
sp_sys_log FUNCTION SQL false void INVOKER
sp_lock_group_shared FUNCTION PLPGSQL false void INVOKER
sp_calc_itemizers FUNCTION SQL false SETOF record INVOKER
sp_update_room_summary FUNCTION PLPGSQL false record INVOKER
sp_check_in FUNCTION PLPGSQL false json INVOKER
sp_saletax_report FUNCTION PLPGSQL false SETOF record INVOKER
sp_tr_deposit_save_log FUNCTION PLPGSQL false trigger INVOKER
sp_get_vat_address FUNCTION PLPGSQL false record INVOKER
sp_get_available_rooms_and_rates FUNCTION PLPGSQL false SETOF record INVOKER
sp_make_payment FUNCTION PLPGSQL false json INVOKER
sp_get_sync_roomrate FUNCTION PLPGSQL false jsonb INVOKER
sp_get_payment_codes_js FUNCTION SQL false json INVOKER

List all payment codes
sp_calc_meal_prices FUNCTION PLPGSQL false SETOF record INVOKER
sp_endday_update_rsv_pickup PROCEDURE PLPGSQL false INVOKER
sp_rate_schedule_breakdown FUNCTION SQL false SETOF record INVOKER

Splits each in-house stay night into room / extrabed / meal / inclusive / other components.

room = GROSS room-only amount (rate minus included meals minus rate inclusions). This is the amount sp_auto_post_folio actually posts to the folio – do not change it. room_net = room with every itemizer on room_net_dept removed (= BASE_PRICE). Basis for REVENUE_CALC_TYPE = 2. Equals the BASE_PRICE stored on the posted room-charge transaction, because room charges post with qty = 1. room_service / room_tax = the is_service and is_tax slices of room, for reporting. room_net_dept = coalesce(rate_plan.room_charge_dept, AUTO_POST.ROOM); it varies per rate plan and different departments carry different itemizer sets, so it is resolved per row.
sp_productivity_by_geocode FUNCTION SQL false SETOF record INVOKER
sp_endday_update_task PROCEDURE PLPGSQL false INVOKER
fn_doorlock_fmt_ts FUNCTION SQL true text INVOKER

format วันเวลาให้ DoorLock-service (M/D/YYYY HH:MM:SS) – staychange/getpasscodes ปฏิเสธรูปแบบ ISO
sp_delete_guest_document_json FUNCTION PLPGSQL false json INVOKER
sp_confirm_booking FUNCTION PLPGSQL false record INVOKER
sp_delete_out_of_order FUNCTION PLPGSQL false record INVOKER
sp_rsv_pickup_by_periods_js FUNCTION SQL false json INVOKER
sp_validate_rate_amount FUNCTION PLPGSQL false void INVOKER
sp_get_rategroup_by_id_json FUNCTION PLPGSQL false json INVOKER
sp_check_room_activity PROCEDURE PLPGSQL false INVOKER
fn_get_calendar_events_list_js FUNCTION PLPGSQL false json INVOKER
sp_reopen_cashsale_folio FUNCTION PLPGSQL false json INVOKER
sp_get_batch_report_options FUNCTION PLPGSQL false json INVOKER
sp_amend_booking FUNCTION PLPGSQL false record INVOKER
sp_change_folio_pattern FUNCTION PLPGSQL false json INVOKER
sp_save_consent_details FUNCTION PLPGSQL false json INVOKER
fn_itemizer FUNCTION PLPGSQL false t_money INVOKER
fn_occupancy_calendar_details FUNCTION SQL false SETOF record INVOKER
fn_qr_png FUNCTION PLPYTHON3U true bytea INVOKER

สร้างรูป QR เป็น PNG (bytea) จากข้อความ – byte mode, ECC level M, version 1-6 (payload ไม่เกิน 106 bytes) เขียนด้วย stdlib ล้วน ไม่พึ่ง library ภายนอก
sp_delete_calendar FUNCTION PLPGSQL false json INVOKER
fn_month_start FUNCTION SQL false date INVOKER
sp_send_message FUNCTION PLPGSQL false bigint INVOKER

สร้าง message ที่ authen ผ่าน msg.create_message – subject_suffix ต่อท้าย subject ของ template (NULL = ใช้ subject เดิม)
sp_get_available_rates_js FUNCTION PLPGSQL false record INVOKER
sp_ratecalendar_get_js FUNCTION PLPGSQL false json INVOKER
fn_attachment_check_limits FUNCTION PLPGSQL false json INVOKER
sp_select_room_id_by_roomnumber FUNCTION PLPGSQL false integer INVOKER
sp_db_create_backup_table PROCEDURE PLPGSQL false INVOKER
sp_duplicate_booking FUNCTION PLPGSQL false record INVOKER