aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_commit_rate_state
Parameters
Name
Type
Mode
i_trace_id
uuid
IN
Definition
DECLARE v_interface_code text; v_property_id uuid; v_state_rows jsonb; v_rows int := 0; BEGIN SELECT l.interface_code, (l.params->>'property_id')::uuid, (l.metadata->'state_rows') INTO v_interface_code, v_property_id, v_state_rows FROM public.intf_sync_channel_logs l WHERE l.trace_id = i_trace_id ORDER BY l.id DESC LIMIT 1; IF v_interface_code IS NULL OR v_property_id IS NULL OR v_state_rows IS NULL OR jsonb_typeof(v_state_rows) <> 'array' OR jsonb_array_length(v_state_rows) = 0 THEN RETURN 0; END IF; INSERT INTO public.intf_rate_state( interface_code, property_id, rate_plan_id, rate_date, rate, stop_sell, cta, ctd, min_stay, max_stay, updated_at ) SELECT v_interface_code, v_property_id, x.rate_plan_id, x.rate_date, x.rate, x.stop_sell, x.cta, x.ctd, x.min_stay, x.max_stay, now() FROM jsonb_to_recordset(v_state_rows) AS x( rate_plan_id text, rate_date date, rate int, stop_sell boolean, cta boolean, ctd boolean, min_stay smallint, max_stay smallint ) ON CONFLICT (interface_code, property_id, rate_plan_id, rate_date) DO UPDATE SET rate = EXCLUDED.rate, stop_sell = EXCLUDED.stop_sell, cta = EXCLUDED.cta, ctd = EXCLUDED.ctd, min_stay = EXCLUDED.min_stay, max_stay = EXCLUDED.max_stay, updated_at = now(); GET DIAGNOSTICS v_rows = ROW_COUNT; RETURN v_rows; END;