aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_get_sync_roomrate
Parameters
Name
Type
Mode
metadata
jsonb
IN
params
jsonb
IN
Definition
DECLARE v_service_code text; v_interface_code text; v_trace_id uuid := COALESCE((metadata->>'trace_id')::uuid, gen_random_uuid()); v_sync_ts timestamptz := COALESCE((metadata->>'sync_times')::timestamptz, now()); v_start_ts timestamptz := clock_timestamp(); v_end_ts timestamptz; v_date_from date; v_date_to date; v_sync_all boolean := COALESCE((params->>'sync_all')::boolean, true); v_property_id uuid := COALESCE((params->>'property_id')::uuid, 'cf2a2d1b-692f-4140-b150-8542653bd4f6'::uuid); v_sync_restrictions boolean := COALESCE(fn_intf_param('NBIS','INTERFACE_RESTRICTION_ENABLED','false')::boolean, false); v_allow_special boolean := COALESCE((params->>'allow_special_rates')::boolean, true); v_result jsonb := '[]'::jsonb; v_rec_count integer := 0; v_anomalies jsonb := '[]'::jsonb; v_chg jsonb := '[]'::jsonb; v_prv_plan text; v_prv_date date; v_prv_rate int; v_prv_ss boolean; v_prv_cta boolean; v_prv_ctd boolean; v_prv_min smallint; v_prv_max smallint; v_cur_plan text; v_cur_date date; v_cur_rate int; v_cur_ss boolean; v_cur_cta boolean; v_cur_ctd boolean; v_cur_min smallint; v_cur_max smallint; v_start_date date; BEGIN v_interface_code := fn_intf_param('NBIS','INTERFACE_CODE','')::text; v_service_code := 'NBIS_' || v_interface_code || '_RATE'; v_date_from := COALESCE((params->>'date_from')::date, v_sync_ts::date, current_date); v_date_to := v_date_from + COALESCE(NULLIF(params->>'max_days','')::int, 0); WITH rd AS ( SELECT DISTINCT ON (rate_id) rate_id, channel_codes FROM rate_details WHERE channel_linked = true ORDER BY rate_id ), src AS ( SELECT rd.channel_codes[1]::text AS rate_plan_id, r.rate_date::date AS rate_date, (r.rate_amount::numeric * 100)::int AS rate, COALESCE(r.stop_sell, false) AS stop_sell, COALESCE(r.cta, false) AS cta, COALESCE(r.ctd, false) AS ctd, COALESCE(r.min_stay, 0)::smallint AS min_stay, COALESCE(r.max_stay, 1)::smallint AS max_stay FROM public.sp_inventory_rates( v_date_from, v_date_to, NULL, NULL, NULL, false, v_allow_special ) r JOIN rd ON rd.rate_id = r.rate_id WHERE rd.channel_codes[1] IS NOT NULL AND r.rate_amount IS NOT NULL ), chg AS ( SELECT s.* FROM src s LEFT JOIN public.intf_rate_state st ON st.interface_code = v_service_code AND st.property_id = v_property_id AND st.rate_plan_id = s.rate_plan_id AND st.rate_date = s.rate_date WHERE v_sync_all OR st.rate IS NULL OR st.rate IS DISTINCT FROM s.rate OR ( v_sync_restrictions AND ( st.stop_sell IS DISTINCT FROM s.stop_sell OR st.cta IS DISTINCT FROM s.cta OR st.ctd IS DISTINCT FROM s.ctd OR st.min_stay IS DISTINCT FROM s.min_stay OR st.max_stay IS DISTINCT FROM s.max_stay ) ) ) SELECT COALESCE( jsonb_agg( jsonb_build_object( 'rate_plan_id', rate_plan_id, 'rate_date', rate_date, 'rate', rate, 'stop_sell', stop_sell, 'closed_to_arrival', cta, 'closed_to_departure', ctd, 'min_stay', min_stay, 'max_stay', max_stay ) ORDER BY rate_plan_id, rate_date ), '[]'::jsonb ) INTO v_chg FROM chg; v_result := '[]'::jsonb; v_rec_count := 0; v_prv_plan := NULL; v_prv_date := NULL; v_prv_rate := NULL; v_prv_ss := NULL; v_prv_cta := NULL; v_prv_ctd := NULL; v_prv_min := NULL; v_prv_max := NULL; v_start_date := NULL; FOR v_cur_plan, v_cur_date, v_cur_rate, v_cur_ss, v_cur_cta, v_cur_ctd, v_cur_min, v_cur_max IN SELECT x.rate_plan_id, x.rate_date, x.rate, x.stop_sell, x.cta, x.ctd, x.min_stay, x.max_stay FROM jsonb_to_recordset(v_chg) AS x( rate_plan_id text, rate_date date, rate int, stop_sell boolean, cta boolean, ctd boolean, min_stay smallint, max_stay smallint ) ORDER BY x.rate_plan_id, x.rate_date LOOP IF (v_prv_plan IS NOT NULL) AND NOT ( v_prv_plan = v_cur_plan AND v_prv_date + 1 = v_cur_date AND v_prv_rate = v_cur_rate AND ( NOT v_sync_restrictions OR ( v_prv_ss IS NOT DISTINCT FROM v_cur_ss AND v_prv_cta IS NOT DISTINCT FROM v_cur_cta AND v_prv_ctd IS NOT DISTINCT FROM v_cur_ctd AND v_prv_min IS NOT DISTINCT FROM v_cur_min AND v_prv_max IS NOT DISTINCT FROM v_cur_max ) ) ) THEN v_result := v_result || jsonb_build_array( jsonb_build_object( 'property_id', v_property_id::text, 'rate_plan_id', v_prv_plan, 'date_from', v_start_date, 'date_to', v_prv_date, 'rate', v_prv_rate ) || CASE WHEN v_sync_restrictions THEN jsonb_build_object( 'stop_sell', v_prv_ss, 'closed_to_arrival', v_prv_cta, 'closed_to_departure', v_prv_ctd, 'min_stay', v_prv_min, 'max_stay', v_prv_max ) ELSE '{}'::jsonb END ); v_rec_count := v_rec_count + 1; v_start_date := v_cur_date; ELSIF v_start_date IS NULL THEN v_start_date := v_cur_date; END IF; v_prv_plan := v_cur_plan; v_prv_date := v_cur_date; v_prv_rate := v_cur_rate; v_prv_ss := v_cur_ss; v_prv_cta := v_cur_cta; v_prv_ctd := v_cur_ctd; v_prv_min := v_cur_min; v_prv_max := v_cur_max; END LOOP; IF v_prv_plan IS NOT NULL THEN v_result := v_result || jsonb_build_array( jsonb_build_object( 'property_id', v_property_id::text, 'rate_plan_id', v_prv_plan, 'date_from', v_start_date, 'date_to', v_prv_date, 'rate', v_prv_rate ) || CASE WHEN v_sync_restrictions THEN jsonb_build_object( 'stop_sell', v_prv_ss, 'closed_to_arrival', v_prv_cta, 'closed_to_departure', v_prv_ctd, 'min_stay', v_prv_min, 'max_stay', v_prv_max ) ELSE '{}'::jsonb END ); v_rec_count := v_rec_count + 1; END IF; v_end_ts := clock_timestamp(); INSERT INTO intf_sync_channel_logs( interface_code, sync_type, trace_id, start_time, end_time, rows_returned, anomalies, metadata, params, return_payload, created_at ) VALUES ( v_service_code, CASE WHEN v_sync_all THEN 'FULL' ELSE 'ONCHANGE' END, v_trace_id, v_start_ts, v_end_ts, v_rec_count, v_anomalies, COALESCE(metadata, '{}'::jsonb) || jsonb_build_object( 'state_rows', v_chg ), params, v_result, now() ); RETURN jsonb_build_object( 'result_data', jsonb_build_object( 'metadata', jsonb_build_object( 'trace_id', v_trace_id, 'sync_times', v_sync_ts, 'date_from', v_date_from, 'date_to', v_date_to, 'rows', v_rec_count ), 'result', v_result ) ); END;