aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_checkin_split_folio
Parameters
Name
Type
Mode
i_booking_item
integer
IN
i_room_id
integer
IN
i_checkin_user
text
IN
Definition
DECLARE v_row record; v_result json; v_register_ids integer[] := '{}'; v_errors jsonb := '[]'; v_n integer; is_first boolean := true; BEGIN FOR v_row IN SELECT bg.guest_id FROM booking_guests bg WHERE bg.booking_item = i_booking_item ORDER BY bg.room_seq NULLS LAST, bg.guest_id LOOP v_result := sp_check_in( i_booking_item, i_room_id, v_row.guest_id, i_checkin_user, NULL, FALSE, NOT is_first -- FALSE for primary, TRUE for followers ); IF (v_result::jsonb->>'result_code') = '0' THEN v_register_ids := v_register_ids || (v_result::jsonb->'result_data'->>'register_id')::int; is_first := false; ELSE v_errors := v_errors || jsonb_build_array(jsonb_build_object( 'item_id', i_booking_item, 'guest_id', v_row.guest_id, 'message', v_result::jsonb->>'result_msg')); END IF; END LOOP; v_n := array_length(v_register_ids, 1); -- Split room_rate evenly across all registrations; remainder goes to last guest IF coalesce(v_n, 0) > 1 THEN WITH indexed AS ( SELECT t.register_id, t.idx FROM unnest(v_register_ids) WITH ORDINALITY AS t(register_id, idx) ) UPDATE registration_rates rr SET room_rate = CASE WHEN ir.idx < v_n THEN FLOOR(br.room_rate::numeric / v_n)::public.t_money ELSE (br.room_rate::numeric - FLOOR(br.room_rate::numeric / v_n) * (v_n - 1))::public.t_money END, extrabed_rate = CASE WHEN ir.idx = 1 THEN rr.extrabed_rate ELSE 0::public.t_money END, child_rate = CASE WHEN ir.idx = 1 THEN rr.child_rate ELSE 0::public.t_money END, infant_rate = CASE WHEN ir.idx = 1 THEN rr.infant_rate ELSE 0::public.t_money END FROM indexed ir JOIN booking_rates br ON br.booking_item = i_booking_item AND br.charge_date = rr.charge_date WHERE rr.register_id = ir.register_id AND NOT rr.posted; END IF; RETURN jsonb_build_object( 'checked_in', coalesce(v_n, 0), 'register_ids', to_jsonb(v_register_ids), 'errors', v_errors ); END