aonestar.public

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;

sp_commit_rate_state

Parameters

Definition