· 9 years ago · Nov 17, 2016, 07:28 PM
1BEGIN;
2
3ALTER TABLE client_settings ADD COLUMN claim_tolerance_ind boolean not null DEFAULT false;
4
5UPDATE client_settings
6SET claim_tolerance_ind = CASE WHEN claim_tolerance_type = 'NO_TOLERANCE'::enum.claim_tolerance_type THEN false ELSE true END;
7
8SELECT gosimple.maint_execute_for_all_clients($$
9ALTER TABLE client_settings ALTER COLUMN claim_tolerance_amount DROP DEFAULT;
10
11ALTER TABLE client_settings ALTER COLUMN claim_tolerance_amount DROP NOT NULL;
12$$);
13
14ALTER TABLE client_settings ALTER COLUMN claim_tolerance_amount DROP DEFAULT;
15
16ALTER TABLE client_settings ALTER COLUMN claim_tolerance_amount DROP NOT NULL;
17
18ALTER TABLE client_settings DROP COLUMN claim_tolerance_type CASCADE;
19
20
21-- Function: gosimple.calculate_claim_tolerance()
22
23-- DROP FUNCTION gosimple.calculate_claim_tolerance();
24
25CREATE OR REPLACE FUNCTION gosimple.calculate_claim_tolerance()
26 RETURNS void AS
27$BODY$
28DECLARE
29 _claim_tolerance_ind enum.claim_tolerance_type;
30 _claim_tolerance_amount numeric;
31BEGIN
32
33SELECT claim_tolerance_ind, claim_tolerance_amount FROM client_settings
34INTO _claim_tolerance_ind, _claim_tolerance_amount;
35
36IF _claim_tolerance_ind = false THEN
37 RETURN;
38END IF; --exit function if client doesn't have tolerances enabled
39
40
41--TEMP: Remove soon
42DROP TABLE IF EXISTS _claims_to_apply_tolerance;
43DROP TABLE IF EXISTS _tolerances;
44
45
46CREATE TEMPORARY TABLE _tolerances AS
47SELECT claim_reason_code, tolerance_amount, out_of_tolerance_action
48FROM client_claim_reason_code_tolerance;
49
50--Any claims that have at least one pending line and no pend or reject at header are eligible to apply tolerance
51CREATE TEMPORARY TABLE _claims_to_apply_tolerance AS
52SELECT claim_id FROM _claims_to_process c
53--Claim header not on hold, pended, or rejected
54WHERE NOT EXISTS (
55 SELECT 1
56 FROM claim_reason_code crc
57 WHERE crc.claim_id = c.claim_id
58 AND crc.claim_reason_type IN ('HOLD'::enum.claim_reason_type, 'PEND'::enum.claim_reason_type, 'REJECT'::enum.claim_reason_type)
59 AND upper_inf(crc.active_range)
60)
61--At least one line pending
62AND EXISTS (
63 SELECT 1
64 FROM claim_product cp
65 INNER JOIN claim_product_reason_code cprc ON cprc.claim_product_id = cp.claim_product_id
66 AND cprc.claim_reason_type = 'PEND'::enum.claim_reason_type
67 AND upper_inf(cprc.active_range)
68 WHERE cp.claim_id = c.claim_id
69 UNION ALL
70 SELECT 1
71 FROM claim_adhoc_product cap
72 INNER JOIN claim_adhoc_product_reason_code caprc ON caprc.claim_adhoc_product_id = cap.claim_adhoc_product_id
73 AND caprc.claim_reason_type = 'PEND'::enum.claim_reason_type
74 AND upper_inf(caprc.active_range)
75 WHERE cap.claim_id = c.claim_id
76 UNION ALL
77 SELECT 1
78 FROM claim_lumpsum cl
79 INNER JOIN claim_lumpsum_reason_code clrc ON clrc.claim_lumpsum_id = cl.claim_lumpsum_id
80 AND clrc.claim_reason_type = 'PEND'::enum.claim_reason_type
81 AND upper_inf(clrc.active_range)
82 WHERE cl.claim_id = c.claim_id
83)
84--Claim has no pend reasons that aren't specified with a tolerance (even $0)
85AND NOT EXISTS (
86 SELECT 1
87 FROM claim_product_reason_code xx
88 INNER JOIN claim_product cpx ON cpx.claim_product_id = xx.claim_product_id
89 WHERE cpx.claim_id = c.claim_id
90 AND xx.claim_reason_type = 'PEND'::enum.claim_reason_type
91 AND upper_inf(xx.active_range)
92 AND NOT EXISTS (
93 SELECT 1
94 FROM _tolerances t
95 WHERE t.claim_reason_code = xx.claim_reason_code)
96)
97AND NOT EXISTS (
98 SELECT 1
99 FROM claim_adhoc_product_reason_code axx
100 INNER JOIN claim_adhoc_product capx ON capx.claim_adhoc_product_id = axx.claim_adhoc_product_id
101 WHERE capx.claim_id = c.claim_id
102 AND axx.claim_reason_type = 'PEND'::enum.claim_reason_type
103 AND upper_inf(axx.active_range)
104 AND NOT EXISTS (
105 SELECT 1
106 FROM _tolerances t
107 WHERE t.claim_reason_code = axx.claim_reason_code)
108
109)
110AND NOT EXISTS (
111 SELECT 1
112 FROM claim_lumpsum_reason_code clrcx
113 INNER JOIN claim_lumpsum clx
114 ON clrcx.claim_lumpsum_id = clx.claim_lumpsum_id
115 WHERE clx.claim_id = c.claim_id
116 AND clrcx.claim_reason_type = 'PEND'::enum.claim_reason_type
117 AND upper_inf(clrcx.active_range)
118 AND NOT EXISTS (
119 SELECT 1
120 FROM _tolerances t
121 WHERE t.claim_reason_code = clrcx.claim_reason_code)
122
123);
124
125--If no claims to apply tolerances for, exit
126IF NOT EXISTS (SELECT 1 FROM _claims_to_apply_tolerance) THEN
127 DROP TABLE _claims_to_apply_tolerance;
128 DROP TABLE _tolerances;
129 RETURN;
130END IF;
131
132ANALYZE _claims_to_apply_tolerance;
133
134CREATE TEMPORARY TABLE _reasons_to_clear (
135 claim_id uuid,
136 claim_product_id uuid,
137 claim_adhoc_product_id uuid,
138 claim_lumpsum_id uuid,
139 claim_reason_code enum.claim_reason_code,
140 claim_reason_type enum.claim_reason_type,
141 claim_reason_note character varying
142);
143
144
145
146CREATE TEMPORARY TABLE _tolerance_by_line
147(
148 claim_id uuid,
149 claim_product_id uuid,
150 claim_adhoc_product_id uuid,
151 claim_lumpsum_id uuid,
152 claim_reason_code enum.claim_reason_code,
153 outstanding numeric
154);
155
156--generate tolerances by
157INSERT INTO _tolerance_by_line ( claim_id, claim_product_id, claim_adhoc_product_id, claim_lumpsum_id, claim_reason_code, outstanding)
158 SELECT cp.claim_id,
159 cp.claim_product_id,
160 null::uuid as claim_adhoc_product_id,
161 null::uuid as claim_lumpsum_id,
162 cprc.claim_reason_code,
163 CASE WHEN ((cp.rebate_requested_quantity * cp.rebate_requested_rate) -
164 (cp.rebate_allowed_quantity * cp.rebate_allowed_rate))::numeric < 0
165 THEN 0
166 ELSE ((cp.rebate_requested_quantity * cp.rebate_requested_rate) -
167 (cp.rebate_allowed_quantity * cp.rebate_allowed_rate))::numeric
168 END AS outstanding
169 FROM _claims_to_apply_tolerance ctat
170 INNER JOIN claim_product cp ON cp.claim_id = ctat.claim_id
171 INNER JOIN claim_product_reason_code cprc ON cprc.claim_product_id = cp.claim_product_id
172 AND cprc.claim_reason_type = 'PEND'::enum.claim_reason_type
173 AND upper_inf(cprc.active_range);
174
175 INSERT INTO _tolerance_by_line ( claim_id, claim_product_id, claim_adhoc_product_id, claim_lumpsum_id, claim_reason_code, outstanding )
176 SELECT cp.claim_id,
177 null::uuid as claim_product_id,
178 cp.claim_adhoc_product_id as claim_adhoc_product_id,
179 null::uuid as claim_lumpsum_id,
180 cprc.claim_reason_code,
181 CASE WHEN ((cp.rebate_requested_quantity * cp.rebate_requested_rate) -
182 (cp.rebate_allowed_quantity * cp.rebate_allowed_rate))::numeric < 0
183 THEN 0
184 ELSE ((cp.rebate_requested_quantity * cp.rebate_requested_rate) -
185 (cp.rebate_allowed_quantity * cp.rebate_allowed_rate))::numeric
186 END AS outstanding
187 FROM _claims_to_apply_tolerance ctat
188 INNER JOIN claim_adhoc_product cp ON cp.claim_id = ctat.claim_id
189 INNER JOIN claim_adhoc_product_reason_code cprc ON cprc.claim_adhoc_product_id = cp.claim_adhoc_product_id
190 AND cprc.claim_reason_type = 'PEND'::enum.claim_reason_type
191 AND upper_inf(cprc.active_range);
192
193 INSERT INTO _tolerance_by_line ( claim_id, claim_product_id, claim_adhoc_product_id, claim_lumpsum_id, claim_reason_code, outstanding )
194 SELECT cl.claim_id,
195 null::uuid as claim_product_id,
196 null::uuid as claim_adhoc_product_id,
197 cl.claim_lumpsum_id as claim_lumpsum_id,
198 clrc.claim_reason_code,
199 CASE WHEN (cl.requested_amount - cl.allowed_amount) < 0
200 THEN 0
201 ELSE cl.requested_amount - cl.allowed_amount
202 END AS outstanding
203 FROM _claims_to_apply_tolerance ctat
204 INNER JOIN claim_lumpsum cl ON cl.claim_id = ctat.claim_id
205 INNER JOIN claim_lumpsum_reason_code clrc ON clrc.claim_lumpsum_id = cl.claim_lumpsum_id
206 AND clrc.claim_reason_type = 'PEND'::enum.claim_reason_type
207 AND upper_inf(clrc.active_range);
208
209 CREATE TEMPORARY TABLE _tolerance_by_reason AS
210 SELECT CT.claim_id,
211 CT.claim_reason_code,
212 SUM(outstanding) as outstanding,
213 COALESCE(T.tolerance_amount,0) as tolerance,
214 T.out_of_tolerance_action,
215 CASE WHEN SUM(outstanding) > COALESCE(T.tolerance_amount,0) AND T.out_of_tolerance_action = 'PEND' THEN SUM(outstanding)
216 WHEN SUM(outstanding) > COALESCE(T.tolerance_amount,0) AND T.out_of_tolerance_action = 'RECALC' THEN 0
217 WHEN SUM(outstanding) > COALESCE(T.tolerance_amount,0) AND T.out_of_tolerance_action = 'REJECT' THEN 0
218 ELSE SUM(outstanding) END as post_tolerance_action_outstanding
219 FROM _tolerance_by_line CT
220 LEFT OUTER JOIN _tolerances T ON T.claim_reason_code = CT.claim_reason_code
221 GROUP BY CT.claim_id, CT.claim_reason_code, COALESCE(T.tolerance_amount,0), T.out_of_tolerance_action;
222
223 --If there is a max tolerance by claim in addition to the pendreason tolerances, we need to check the total of everything after out of tolerance actions
224 -- have been applied. If it's within the overall tolerance, then we'll allow all tolerance actions to be applied.
225 CREATE TEMPORARY TABLE _overall_claim_tolerance AS
226 SELECT CT.claim_id,
227 SUM(outstanding) as total_claim_outstanding,
228 CASE
229 WHEN _claim_tolerance_amount IS NOT NULL AND SUM(outstanding) <= _claim_tolerance_amount
230 THEN true
231 WHEN _claim_tolerance_amount IS NULL
232 THEN true
233 ELSE false
234
235 END as pass_claim
236 FROM _tolerance_by_reason CT
237 GROUP BY CT.claim_id;
238
239 /* THESE TWO STATEMENTS ARE TEMPORARY FOR UNIT TESTING/DEBUGGING/LOGGING AND CAN BE REMOVED IF DISK SPACE BECOMES AN ISSUE */
240 --Log any reasons that weren't cleared by process
241 INSERT INTO claim_pend_reason_tolerance_failed_check_log (claim_id, claim_reason_code, outstanding, tolerance, date_checked, out_of_tolerance_action)
242 SELECT T.claim_id, T.claim_reason_code, T.outstanding, T.tolerance, now(), out_of_tolerance_action
243 FROM _tolerance_by_reason T
244 INNER JOIN _overall_claim_tolerance O ON O.claim_id = T.claim_id
245 WHERE (T.outstanding > T.tolerance OR O.pass_claim = false);
246
247
248 --Log all reasons on claims that will clear by tolerance
249 INSERT INTO claim_pend_reason_tolerance_passed_check_log (claim_id, claim_reason_code, outstanding, tolerance, date_checked)
250 SELECT T.claim_id, T.claim_reason_code, T.outstanding, T.tolerance, now()
251 FROM _tolerance_by_reason T
252 INNER JOIN _overall_claim_tolerance O ON O.claim_id = T.claim_id
253 WHERE T.tolerance >= T.outstanding
254 AND O.pass_claim = true
255 AND NOT EXISTS (
256 SELECT 1
257 FROM _tolerance_by_reason X
258 WHERE X.claim_id = T.claim_id
259 AND X.outstanding > X.tolerance
260 );
261 /* END LOGGING CODE */
262
263 --Build a list of all claim product records and their reason codes thate will clear from tolerance
264 -- ALL reasons on that claim must be within tolerance
265 -- We will use this list to clear the pends and create overrides
266 INSERT INTO _reasons_to_clear (claim_id, claim_product_id, claim_adhoc_product_id, claim_lumpsum_id, claim_reason_code, claim_reason_type, claim_reason_note )
267 SELECT DISTINCT T.claim_id, T.claim_product_id, T.claim_adhoc_product_id, T.claim_lumpsum_id, T.claim_reason_code, 'PEND'::enum.claim_reason_type,
268 'This line remained pended due to out of tolerance action.'
269 FROM _tolerance_by_line T
270 INNER JOIN _tolerance_by_reason R
271 ON T.claim_id = R.claim_id
272 AND T.claim_reason_code = R.claim_reason_code
273 AND R.out_of_tolerance_action = 'PEND'::enum.out_of_tolerance_action_type
274 INNER JOIN _overall_claim_tolerance O on O.claim_id = T.claim_id
275 WHERE true
276 AND ( R.outstanding > R.tolerance OR O.pass_claim = FALSE );
277
278 INSERT INTO _reasons_to_clear (claim_id, claim_product_id, claim_adhoc_product_id, claim_lumpsum_id, claim_reason_code, claim_reason_type, claim_reason_note )
279 SELECT DISTINCT T.claim_id, T.claim_product_id, T.claim_adhoc_product_id, T.claim_lumpsum_id, T.claim_reason_code, 'RECALC'::enum.claim_reason_type,
280 'Recalculated to short pay due to pending $$ out of tolerance'
281 FROM _tolerance_by_line T
282 INNER JOIN _tolerance_by_reason R
283 ON T.claim_id = R.claim_id
284 AND T.claim_reason_code = R.claim_reason_code
285 AND R.out_of_tolerance_action = 'RECALC'::enum.out_of_tolerance_action_type
286 INNER JOIN _overall_claim_tolerance O on O.claim_id = T.claim_id
287 WHERE true
288 AND ( R.outstanding > R.tolerance OR O.pass_claim = FALSE )
289 AND NOT EXISTS ( --no other pending reason on this product which should be remain pending
290 SELECT 1
291 FROM _reasons_to_clear X
292 WHERE ( X.claim_product_id = T.claim_product_id
293 OR X.claim_adhoc_product_id = T.claim_adhoc_product_id
294 OR X.claim_lumpsum_id = T.claim_lumpsum_id)
295 --AND X.claim_reason_type = 'PEND'::enum.claim_reason_type
296 );
297
298 INSERT INTO _reasons_to_clear (claim_id, claim_product_id, claim_adhoc_product_id, claim_lumpsum_id, claim_reason_code, claim_reason_type, claim_reason_note )
299 SELECT DISTINCT T.claim_id, T.claim_product_id, T.claim_adhoc_product_id, T.claim_lumpsum_id, T.claim_reason_code, 'REJECT'::enum.claim_reason_type,
300 'Auto-rejected for pending $$ out of tolerance'
301 FROM _tolerance_by_line T
302 INNER JOIN _tolerance_by_reason R
303 ON T.claim_id = R.claim_id
304 AND T.claim_reason_code = R.claim_reason_code
305 AND R.out_of_tolerance_action = 'REJECT'::enum.out_of_tolerance_action_type
306 INNER JOIN _overall_claim_tolerance O on O.claim_id = T.claim_id
307 WHERE true
308 AND ( R.outstanding > R.tolerance OR O.pass_claim = FALSE )
309 --AND O.pass_claim = true
310 AND NOT EXISTS ( --no other pending reason on this product which should remain pending
311 SELECT 1
312 FROM _reasons_to_clear X
313 WHERE ( X.claim_product_id = T.claim_product_id
314 OR X.claim_adhoc_product_id = T.claim_adhoc_product_id
315 OR X.claim_lumpsum_id = T.claim_lumpsum_id)
316 --AND X.claim_reason_type = 'PEND'::enum.claim_reason_type
317 );
318
319 INSERT INTO _reasons_to_clear (claim_id, claim_product_id, claim_adhoc_product_id, claim_lumpsum_id, claim_reason_code, claim_reason_type, claim_reason_note )
320 SELECT DISTINCT T.claim_id, T.claim_product_id, T.claim_adhoc_product_id, T.claim_lumpsum_id, T.claim_reason_code, 'OVERRIDE'::enum.claim_reason_type,
321 'Auto-overridden by tolerance'
322 FROM _tolerance_by_line T
323 INNER JOIN _tolerance_by_reason R
324 ON R.claim_id = T.claim_id
325 AND R.claim_reason_code = T.claim_reason_code
326 AND R.tolerance >= R.outstanding
327 INNER JOIN _overall_claim_tolerance O on O.claim_id = T.claim_id
328 WHERE O.pass_claim = true
329 AND NOT EXISTS ( --no reasons out of tolerance on the claim that will remain pending
330 SELECT 1
331 FROM _reasons_to_clear X
332 WHERE ( X.claim_product_id = T.claim_product_id
333 OR X.claim_adhoc_product_id = T.claim_adhoc_product_id
334 OR X.claim_lumpsum_id = T.claim_lumpsum_id)
335 --AND X.claim_reason_type = 'PEND'::enum.claim_reason_type
336 );
337
338 DROP TABLE _tolerance_by_reason;
339 DROP TABLE _tolerance_by_line;
340 DROP TABLE _overall_claim_tolerance;
341 DROP TABLE _tolerances;
342
343 --Clear pends for claim products which are changing due to tolerance actions using _reasons_to_clear
344 UPDATE claim_product_reason_code
345 SET active_range = tstzrange(lower(cprc.active_range), now(), '[]') --need parm for exclusive or inclusive?
346 FROM claim_product_reason_code cprc
347 INNER JOIN _reasons_to_clear c
348 ON c.claim_product_id = cprc.claim_product_id
349 -- We will clear other reasons on a product other than the one in the _reasons_to_clear table now.
350 --AND c.claim_reason_code = cprc.claim_reason_code
351 AND cprc.claim_reason_type = 'PEND'
352 AND upper_inf(cprc.active_range)
353 WHERE claim_product_reason_code.claim_product_reason_code_id = cprc.claim_product_reason_code_id
354 AND c.claim_reason_type <> 'PEND';
355
356 UPDATE claim_adhoc_product_reason_code
357 SET active_range = tstzrange(lower(claim_adhoc_product_reason_code.active_range), now(), '[]') --need parm for exclusive or inclusive?
358 FROM claim_adhoc_product_reason_code cprc
359 INNER JOIN _reasons_to_clear c
360 ON c.claim_adhoc_product_id = cprc.claim_adhoc_product_id
361 -- We will clear other reasons on a product other than the one in the _reasons_to_clear table now.
362 -- AND c.claim_reason_code = cprc.claim_reason_code
363 AND cprc.claim_reason_type = 'PEND'
364 AND upper_inf(cprc.active_range)
365 WHERE claim_adhoc_product_reason_code.claim_adhoc_product_reason_code_id = cprc.claim_adhoc_product_reason_code_id
366 AND c.claim_reason_type <> 'PEND';
367
368 UPDATE claim_lumpsum_reason_code
369 SET active_range = tstzrange(lower(claim_lumpsum_reason_code.active_range), now(), '[]') --need parm for exclusive or inclusive?
370 FROM claim_lumpsum_reason_code cprc
371 INNER JOIN _reasons_to_clear c
372 ON c.claim_lumpsum_id = cprc.claim_lumpsum_id
373 -- We will clear other reasons on a product other than the one in the _reasons_to_clear table now.
374 -- AND c.claim_reason_code = cprc.claim_reason_code
375 AND cprc.claim_reason_type = 'PEND'
376 AND upper_inf(cprc.active_range)
377 WHERE claim_lumpsum_reason_code.claim_lumpsum_reason_code_id = cprc.claim_lumpsum_reason_code_id
378 AND c.claim_reason_type <> 'PEND';
379
380 --Set allowed columns equal to the requested columns
381 UPDATE claim_product
382 SET rebate_allowed_quantity = cp.rebate_requested_quantity,
383 rebate_allowed_rate = cp.rebate_requested_rate
384 FROM claim_product cp
385 INNER JOIN _claims_to_process c
386 ON c.claim_id = cp.claim_id
387 INNER JOIN _reasons_to_clear r
388 ON cp.claim_product_id = r.claim_product_id
389 AND r.claim_reason_type = 'OVERRIDE'::enum.claim_reason_type
390 WHERE claim_product.claim_product_id = cp.claim_product_id;
391
392 UPDATE claim_adhoc_product
393 SET rebate_allowed_quantity = cp.rebate_requested_quantity,
394 rebate_allowed_rate = cp.rebate_requested_rate
395 FROM claim_adhoc_product cp
396 INNER JOIN _claims_to_process c
397 ON c.claim_id = cp.claim_id
398 INNER JOIN _reasons_to_clear r
399 ON cp.claim_adhoc_product_id = r.claim_adhoc_product_id
400 AND r.claim_reason_type = 'OVERRIDE'::enum.claim_reason_type
401 WHERE claim_adhoc_product.claim_adhoc_product_id = cp.claim_adhoc_product_id;
402
403 UPDATE claim_lumpsum
404 SET allowed_amount = cl.requested_amount
405 FROM claim_lumpsum cl
406 INNER JOIN _claims_to_process c
407 ON c.claim_id = cl.claim_id
408 INNER JOIN _reasons_to_clear r
409 ON cl.claim_lumpsum_id = r.claim_lumpsum_id
410 AND r.claim_reason_type = 'OVERRIDE'::enum.claim_reason_type
411 WHERE claim_lumpsum.claim_lumpsum_id = cl.claim_lumpsum_id;
412
413
414 --Set allowed = to calculated for lines to be recalc'd
415 UPDATE claim_product
416 SET rebate_allowed_quantity = cp.rebate_requested_quantity,
417 rebate_allowed_rate = cp.rebate_calculated_rate
418 FROM claim_product cp
419 INNER JOIN _claims_to_process c
420 ON c.claim_id = cp.claim_id
421 INNER JOIN _reasons_to_clear r
422 ON cp.claim_product_id = r.claim_product_id
423 AND r.claim_reason_type = 'RECALC'::enum.claim_reason_type
424 WHERE claim_product.claim_product_id = cp.claim_product_id;
425
426 --Set allowed = 0 for lines to be rejected
427 UPDATE claim_product
428 SET rebate_allowed_quantity = 0,
429 rebate_allowed_rate = 0
430 FROM claim_product cp
431 INNER JOIN _claims_to_process c
432 ON c.claim_id = cp.claim_id
433 INNER JOIN _reasons_to_clear r
434 ON cp.claim_product_id = r.claim_product_id
435 AND r.claim_reason_type = 'REJECT'::enum.claim_reason_type
436 WHERE claim_product.claim_product_id = cp.claim_product_id;
437
438 UPDATE claim_adhoc_product
439 SET rebate_allowed_quantity = 0,
440 rebate_allowed_rate = 0
441 FROM claim_adhoc_product cp
442 INNER JOIN _claims_to_process c
443 ON c.claim_id = cp.claim_id
444 INNER JOIN _reasons_to_clear r
445 ON cp.claim_adhoc_product_id = r.claim_adhoc_product_id
446 AND r.claim_reason_type = 'REJECT'::enum.claim_reason_type
447 WHERE claim_adhoc_product.claim_adhoc_product_id = cp.claim_adhoc_product_id;
448
449 --No update needed for lumpsum recalcs - the allowed amount remains the allowed amount
450
451 UPDATE claim_lumpsum
452 SET allowed_amount = 0
453 FROM claim_lumpsum cl
454 INNER JOIN _claims_to_process c
455 ON c.claim_id = cl.claim_id
456 INNER JOIN _reasons_to_clear r
457 ON cl.claim_lumpsum_id = r.claim_lumpsum_id
458 AND r.claim_reason_type = 'REJECT'::enum.claim_reason_type
459 WHERE claim_lumpsum.claim_lumpsum_id = cl.claim_lumpsum_id;
460
461
462 INSERT INTO claim_product_reason_code (claim_product_id, claim_reason_code, claim_reason_type, claim_reason_note, active_range)
463 SELECT claim_product_id, MIN(claim_reason_code), claim_reason_type, claim_reason_note, tstzrange(now() + '1 microsecond', null, '[]')
464 FROM _reasons_to_clear
465 WHERE claim_product_id IS NOT NULL
466 AND claim_reason_type <> 'PEND'
467 GROUP BY claim_product_id, claim_reason_type, claim_reason_note;
468
469 INSERT INTO claim_adhoc_product_reason_code (claim_adhoc_product_id, claim_reason_code, claim_reason_type, claim_reason_note, active_range)
470 SELECT claim_adhoc_product_id, MIN(claim_reason_code), claim_reason_type, claim_reason_note, tstzrange(now() + '1 microsecond', null, '[]')
471 FROM _reasons_to_clear
472 WHERE claim_adhoc_product_id IS NOT NULL
473 AND claim_reason_type <> 'PEND'
474 GROUP BY claim_adhoc_product_id, claim_reason_type, claim_reason_note;
475
476 INSERT INTO claim_lumpsum_reason_code (claim_lumpsum_id, claim_reason_code, claim_reason_type, claim_reason_note, active_range)
477 SELECT claim_lumpsum_id, MIN(claim_reason_code), claim_reason_type, claim_reason_note, tstzrange(now() + '1 microsecond', null, '[]')
478 FROM _reasons_to_clear
479 WHERE claim_lumpsum_id IS NOT NULL
480 AND claim_reason_type <> 'PEND'
481 GROUP BY claim_lumpsum_id, claim_reason_type, claim_reason_note;
482
483 DROP TABLE _reasons_to_clear;
484 DROP TABLE _claims_to_apply_tolerance;
485END;
486
487$BODY$
488 LANGUAGE plpgsql VOLATILE
489 COST 100;
490ALTER FUNCTION gosimple.calculate_claim_tolerance()
491 OWNER TO root;
492
493
494DROP TYPE enum.claim_tolerance_type;