1 package body pay_fr_court_orders as
2 /* $Header: pyfrcord.pkb 120.1 2006/02/02 04:35:17 aparkes noship $ */
3 g_package CONSTANT VARCHAR2(31):= 'pay_fr_court_orders.';
4 /*-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<**
5
6 Function : VALIDATION - This validates the current court orders run. It checks whether both P4 and P5
7 court orders exist. If they do then return an error message.
8 Returns : Error message
9 Called By : fr_court_orders.process
10
11 **-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<**/
12
13 function validation return varchar2 is
14 --<<***BEGIN***>>--
15 begin
16 --
17 hr_utility.set_location('Entering:'||g_package||'validation', 10);
18 --
19 if total_order(40).number_of_orders > 0 and
20 total_order(50).number_of_orders > 0 then
21 hr_utility.set_location(g_package||'validation - FAILED', 15);
22 fnd_message.set_name('PAY', 'PAY_74893_CO_P4S_P5S');
23 return fnd_message.get;
24 else
25 hr_utility.set_location(g_package||'valid - PASSED', 20);
26 return null;
27 end if;
28 end validation;
29 --<<***END***>>--
30
31 /*-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<**
32
33 Function : VALIDATION - This validates the current court orders run. It checks whether both P4 and P5
34 court orders exist. If they do then return an error message.
35 Returns : Error message
36 Called By : fr_court_orders.process
37
38 **-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<**/
39
40 function net_pay_valid return varchar2 is
41 --<<***BEGIN***>>--
42 begin
43 --
44 hr_utility.set_location('Entering:'||g_package||'net_pay_valid', 10);
45 --
46 return g_net_pay_valid;
47 --
48 hr_utility.set_location(' Leaving:'||g_package||'net_pay_valid', 20);
49 --
50 end net_pay_valid;
51 --<<***END***>>--
52
53 /*-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<**
54 Function : PROCESSED - This function will check to see whether any court orders have already
55 been processed - determines if the current element being processed
56 is the first court order element for this payroll run.
57 Returns : 'Y' or 'N' indicating whether court orders have already been processed
58 Called By : Fast Fomula FR_COURT_ORDER_PAYMENTS
59 **-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<**/
60
61 function processed (p_assignment_action_id in number) return varchar2 is
62 --<<***BEGIN***>>--
63 begin
64 if g_assignment_action_id = p_assignment_action_id then
65 hr_utility.set_location(g_package||'processed = Y', 10);
66 return 'Y';
67 else
68 hr_utility.set_location(g_package||'processed = N', 10);
69 return 'N';
70 end if;
71 end processed;
72 --<<***END***>>--
73
74 /*-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<**
75 Function : CO_PAYMENT - This function simply returns the payment value for a given source id.
76 Returns : Court Order Payment
77 Called By : get_payment
78 **-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<**/
79
80 function co_payment (p_source_id in number) return number is
81 --<<***BEGIN***>>--
82 begin
83 return nvl(court_order(p_source_id).payment,0);
84 exception
85 when no_data_found then
86 return 0;
87 end co_payment;
88 --<<***END***>>--
89
90 /*-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<**
91 Function : GET_PAYMENT - Retrieves the payment for the current court order element being processed
92 and also produces any informational messages about the payment.
93 Returns : Payment
94 Any message relating to payment
95 Flag indicating whether a message is being returned
96 Called By : Fast Formula FR_COURT_ORDER_PAYMENTS
97 **-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<**/
98
99 function get_payment(p_source_id in number
100 ,p_payment_reference in varchar2
101 ,p_element_name in varchar2
102 ,p_message out nocopy varchar2
103 ,p_message_text out nocopy varchar2
104 ,p_stop out nocopy varchar2 ) return number is
105 --
106 l_priority number;
107 l_payment number := 0;
108 ---------------------------------------------------------------------------------------------------------
109 procedure write_message (p_monthly_payment in number
110 ,p_outstanding_balance in number
111 ,p_priority in number
112 ,p_element_name in varchar2
113 ,p_message_text out nocopy varchar2
114 ,p_payment out nocopy number
115 ,p_stop out nocopy varchar2) is
116 --
117 l_payment number(30,2) := co_payment(p_source_id);
118 l_paid_in_period number(30,3) := nvl(court_order(p_source_id).balance_ptd,0);
119 l_value number(30,2);
120 --
121 begin
122 --
123 hr_utility.set_location('Entering:Get_Payment - write_message', 16);
124 --
125 p_payment := l_payment - l_paid_in_period;
126 --
127 if p_priority in (20,30,40) then
128 l_value := p_outstanding_balance;
129 else
130 l_value := p_monthly_payment;
131 end if;
132 --
133 if l_payment < l_value then
134 if p_payment > 0 then
135 fnd_message.set_name('PAY', 'PAY_74889_CO_SHORTFALL');
136 fnd_message.set_token('ELEMENT', p_element_name);
137 fnd_message.set_token('REFERENCE', p_payment_reference);
138 fnd_message.set_token('PAYMENT', l_payment);
139 fnd_message.set_token('OWED', l_value);
140 fnd_message.set_token('SHORTFALL', to_char(l_value - l_payment));
141 p_message_text := fnd_message.get;
142 else
143 fnd_message.set_name('PAY', 'PAY_74890_CO_NO_PAYMENT');
144 fnd_message.set_token('ELEMENT', p_element_name);
145 fnd_message.set_token('REFERENCE', p_payment_reference);
146 p_message_text := fnd_message.get;
147 end if;
148 else
149 if l_payment >= p_outstanding_balance then
150 fnd_message.set_name('PAY', 'PAY_74891_CO_PAID_OFF');
151 fnd_message.set_token('ELEMENT', p_element_name);
152 fnd_message.set_token('REFERENCE', p_payment_reference);
153 p_message_text := fnd_message.get;
154 p_stop := 'Y';
155 end if;
156 end if;
157 --
158 hr_utility.set_location('Entering:Get_Payment - write_message', 17);
159 --
160 end write_message;
161 ---------------------------------------------------------------------------------------------------------
162 --<<***BEGIN***>>--
163 begin
164 --
165 hr_utility.set_location('Entering:'||g_package||'get_payment element'||substr(p_element_name,1,20), 10);
166 --
167 if not court_order.exists(p_source_id) then
168 hr_utility.set_location('Element Source Id: '||substr(p_source_id,1,25)||' Not processed', 15);
169 p_message := 'Y';
170 fnd_message.set_name('PAY', 'PAY_74892_CO_NOT_PROCESSED');
171 fnd_message.set_token('ELEMENT', p_element_name);
172 fnd_message.set_token('REFERENCE', p_payment_reference);
173 p_message_text := fnd_message.get;
174 return 0;
175 end if;
176 --
177 p_message_text := ' ';
178 p_message := 'N';
179 p_stop := 'N';
180 l_priority := court_order(p_source_id).priority;
181 --
182 write_message(p_monthly_payment => court_order(p_source_id).monthly_payment
183 ,p_outstanding_balance => court_order(p_source_id).outstanding_balance
184 ,p_priority => l_priority
185 ,p_message_text => p_message_text
186 ,p_element_name => indirect_to_direct(p_element_name)
187 ,p_payment => l_payment
188 ,p_stop => p_stop);
189 --
190 if p_message_text <> ' ' then
191 hr_utility.set_location('Message = Y', 20);
192 p_message := 'Y';
193 end if;
194 --
195 hr_utility.set_location(' Leaving:'||g_package||'get_payment', 30);
196 --
197 return l_payment;
198 --
199 end get_payment;
200 --<<***END***>>--
201
202 /*-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<**
203 Function : MAP_NAMES - Matches direct -> indirect or indirect -> direct, depending on the calling
204 function. It loops through pl/sql table map_element - which has been set up
205 by sub-procedure initialise within procedure process. Once
206 a matching value has been found then the corresponding indirect or direct
207 element name is returned.
208 Returns : Indirect or Direct Element Name
209 Called By : direct_to_indirect
210 indirect_to_direct
211 **-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<**/
212
213 function map_names (p_direct_name in varchar2
214 ,p_indirect_name in varchar2 ) return varchar2 is
215 --
216 l_name varchar2(80) := null;
217 i number := 0;
218 --
219 --<<***BEGIN***>>--
220 begin
221 --
222 hr_utility.set_location('Entering:'||g_package||'map_names', 10);
223 --
224 i := map_element.first;
225 while i <= map_element.last loop
226 if p_direct_name is not null then
227 if map_element(i).direct_name = p_direct_name then
228 l_name := map_element(i).indirect_name;
229 exit;
230 end if;
231 elsif p_indirect_name is not null then
232 if map_element(i).indirect_name = p_indirect_name then
233 l_name := map_element(i).direct_name;
234 exit;
235 end if;
236 end if;
237 i := map_element.next(i);
238 end loop;
239 --
240 hr_utility.set_location(' Leaving:'||g_package||'map_names', 20);
241 --
242 return l_name;
243 --
244 end map_names;
245 --<<***END***>>--
246
247 /*-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<**
248 Function : DIRECT_TO_INDIRECT - Returns the name of the indirect element for a given direct element.
249 Returns : Indirect Element Name
250 Called By : get_balance_value
251 **-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<**/
252
253 function direct_to_indirect (p_direct_name in varchar2) return varchar2 is
254 --<<***BEGIN***>>--
255 begin
256 --
257 hr_utility.set_location('Entering:'||g_package||'direct_to_indirect', 10);
258 --
259 return map_names(p_direct_name => p_direct_name);
260 --
261 end direct_to_indirect;
262 --<<***END***>>--
263
264 /*-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<**
265 Function : INDIRECT_TO_DIRECT - Returns the name of the direct element for a given indirect element.
266 Returns : Direct Element Name
267 Called By : get_payment
268 **-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<**/
269
270 function indirect_to_direct (p_indirect_name in varchar2) return varchar2 is
271 --<<***BEGIN***>>--
272 begin
273 --
274 hr_utility.set_location('Entering:'||g_package||'indirect_to_direct', 10);
275 --
276 return map_names(p_indirect_name => p_indirect_name);
277 --
278 end indirect_to_direct;
279 --<<***END***>>--
280
281 /*-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<**
282 Function : GET_BALANCE_VALUE - This function returns the balance value of an indirect court orders
283 element given the source id and name of the corresponding direct
284 element and the name of the dimension.
285 N.B. The balance has the same name as the indirect element.
286 Returns : Balance Value for a given defined balance
287 Called By : process
288 **-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<**/
289
290 function get_balance_value(p_element_name in varchar2
291 ,p_dimension_name in varchar2
292 ,p_source_id in number
293 ,p_assignment_action_id in number) return number is
294 --
295 cursor csr_get_dimension_id (p_balance_name in varchar2) is
296 select db.defined_balance_id
297 from pay_defined_balances db,
298 pay_balance_types bt,
299 pay_balance_dimensions bdim
300 where bt.balance_name = p_balance_name
301 and bt.legislation_code = 'FR'
302 and bdim.dimension_name = p_dimension_name
303 and bdim.legislation_code = 'FR'
304 and db.balance_type_id = bt.balance_type_id
305 and db.balance_dimension_id = bdim.balance_dimension_id;
306 --
307 l_defined_balance_id number;
308 l_result number := null;
309 l_balance_name varchar2(100);
310 --
311 --<<***BEGIN***>>--
312 begin
313 --
314 hr_utility.set_location('Entering:'||g_package||'get_balance_value', 10);
315 --
316 l_balance_name := direct_to_indirect(p_element_name);
317 --
318 open csr_get_dimension_id(l_balance_name);
319 fetch csr_get_dimension_id into l_defined_balance_id;
320 close csr_get_dimension_id;
321 --
322 pay_balance_pkg.set_context ('ORIGINAL_ENTRY_ID',p_source_id);
323 l_result := pay_balance_pkg.get_value (l_defined_balance_id, p_assignment_action_id);
324 --
325 hr_utility.set_location(' Leaving:'||g_package||'get_balance_value', 20);
326 --
327 return l_result;
328 --
329 end get_balance_value;
330 --<<***END***>>--
331
332 /*-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<**
333 Function : PROCESS - This function will process all the court order elements and store the results
334 in two pl/sql tables. These tables can then be read by subsequent functions
335 to retrieve payments for a particular court order element type.
336 Returns : A return value - 0 if there is no error
337 1 if there is an error
338 Called By : Fast Formula FR_COURT_ORDERS_PAYMENTS
339 **-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<**/
340
341 function process (p_assignment_action_id in number
342 ,p_date_earned in date
343 ,p_source_id in number
344 ,p_net_payment_ptd in number
345 ,p_rmi in number
346 ,p_addl_threshold_per_dpndt in number
347 ,p_addl_seizable in number
348 ,p_error_msg out nocopy varchar2) return number is
349 --
350 i number := 0;
351 l_old_proc_order number := 0;
355 l_funds number := 0;
352 l_old_priority number := 0;
353 l_last_priority number := 0;
354 l_protected_amt number := 0;
356 l_max_seizure number := 0;
357 l_no_of_dpndts number := 0;
358 l_max_shortfall number := 0;
359 l_max_shortfall_order number := 0;
360 l_shortfall number := 0;
361 l_index number := 0;
362 l_rem_seizable number := 0;
363 l_payment number := 0;
364 l_payment_pro number := 0;
365 l_actual_total_payment number := 0;
366 l_remainder number := 0;
367 l_total_remainder number := 0;
368 l_total_payment number := 0;
369 l_total_amount number := 0;
370 l_all_payments number := 0;
371 l_balance_itd number;
372 l_balance_ptd number;
373 co_paid_off exception;
374 TYPE COCurTyp IS REF CURSOR;
375 cv COCurTyp;
376 cv_outstanding_balance number := 0;
377 cv_index number := 0;
378 --
379 cursor csr_get_court_orders is
380 select decode(et.element_name
381 ,'FR_FAMILY_MAINTENANCE',10
382 ,'FR_FAMILY_MAINTENANCE_ARREARS',15
383 ,'FR_TAX',20
384 ,'FR_FINE',30
385 ,'FR_MISCELLANEOUS',40
386 ,'FR_SEIZURE',50) PRIORITY
387 ,max(decode(iv.name
388 ,'Monthly Payment',rrv.result_value)) MONTHLY_PAYMENT
389 ,max(decode(iv.name,'Amount',rrv.result_value)) AMOUNT
390 ,max(decode(iv.name
391 ,'Processing Order',rrv.result_value)) PROCESSING_ORDER
392 ,et.element_name ELEMENT_NAME
393 ,rr.source_id SOURCE_ID
394 ,rr.run_result_id RUN_RESULT_ID
395 from pay_element_types_f et
396 ,pay_run_results rr
397 ,pay_run_result_values rrv
398 ,pay_input_values_f iv
399 where et.element_name in ('FR_FAMILY_MAINTENANCE'
400 ,'FR_FAMILY_MAINTENANCE_ARREARS'
401 ,'FR_TAX'
402 ,'FR_FINE'
403 ,'FR_MISCELLANEOUS'
404 ,'FR_SEIZURE')
405 and et.legislation_code = 'FR'
406 and et.business_group_id is null
407 and p_date_earned between et.effective_start_date
408 and et.effective_end_date
409 and et.element_type_id = rr.element_type_id
410 and rr.assignment_action_id = p_assignment_action_id
411 and rr.run_result_id = rrv.run_result_id
412 and rrv.input_value_id = iv.input_value_id
413 and iv.element_type_id = et.element_type_id
414 and p_date_earned between iv.effective_start_date
415 and iv.effective_end_date
416 and iv.name in ('Monthly Payment'
417 ,'Amount'
418 ,'Processing Order')
419 and rr.status in ('P','PA')
420 group by decode(et.element_name
421 ,'FR_FAMILY_MAINTENANCE',10
422 ,'FR_FAMILY_MAINTENANCE_ARREARS',15
423 ,'FR_TAX',20
424 ,'FR_FINE',30
425 ,'FR_MISCELLANEOUS',40
426 ,'FR_SEIZURE',50)
427 ,et.element_name
428 ,rr.source_id
429 ,rr.run_result_id
430 order by 1, 4;
431 --
432 /* BUG 2245520 Changed cursor to reflect PQH dynamic dependant calc */
433 /* BUG 2481752 Cursor updated to reflect a fix by the PHQ team */
434 Cursor csr_no_of_dpndts is
435 select count(*)
436 from PER_CONTACT_RELATIONSHIPS PCR
437 , per_all_assignments_f a
438 , pay_assignment_actions aa
439 where PCR.person_id = a.person_id
440 and a.assignment_id = aa.assignment_id
441 and aa.assignment_action_id = p_assignment_action_id
442 and p_date_earned
443 between a.effective_start_date and a.effective_end_date
444 and PCR.DEPENDENT_FLAG = 'Y'
445 and ( pcr.date_start is NULL OR p_date_earned BETWEEN
446 pcr.date_start AND NVL(pcr.date_end, p_date_earned) )
447 and (pcr.date_start IS NOT NULL OR
448 EXISTS (SELECT person_id
449 FROM per_all_people_f
450 WHERE person_id = pcr.contact_person_id
451 AND p_date_earned BETWEEN
452 effective_start_date AND effective_end_date));
453
454 ---------------------------------------------------------------------------------------------------------
455 function get_maximum_allowable_seizure (p_net_payment in number
456 ,p_no_of_dependants in number
457 ,p_addl_threshold_per_dpndt in number) return number is
458 --
459 l_seize_amount number := 0;
460 l_addl_threshold number := 0;
461 l_seize_basis number := 0;
462 l_low_value number := 0;
463 l_high_value number := 0;
464 l_effective_date date;
465 --
466 cursor csr_get_band_values is
467 select fnd_number.canonical_to_number(cinst.value) rate,
468 fnd_number.canonical_to_number(cinst2.value) high_value,
469 fnd_number.canonical_to_number(cinst3.value) low_value
470 from pay_user_column_instances_f cinst
471 ,pay_user_columns c
472 ,pay_user_column_instances_f cinst2
473 ,pay_user_columns c2
477 ,pay_user_rows_f r
474 ,pay_user_column_instances_f cinst3
475 ,pay_user_columns c3
476 ,pay_user_tables tab
478 where tab.user_table_name = 'FR_COURT_ORDER_BANDS'
479 and tab.user_key_units = 'N'
480 and c.user_table_id = tab.user_table_id
481 and c.legislation_code = 'FR'
482 and c.user_column_name = 'DEDUCTION_RATE'
483 and cinst.user_column_id = c.user_column_id
484 and l_effective_date between cinst.effective_start_date and cinst.effective_end_date
485 and cinst.legislation_code = 'FR'
486 and c2.user_table_id = tab.user_table_id
487 and c2.legislation_code = 'FR'
488 and c2.user_column_name = 'Upper Bound'
489 and cinst2.user_column_id = c2.user_column_id
490 and l_effective_date between cinst2.effective_start_date and cinst2.effective_end_date
491 and cinst2.legislation_code = 'FR'
492 and c3.user_table_id = tab.user_table_id
493 and c3.legislation_code = 'FR'
494 and c3.user_column_name = 'Lower Bound'
495 and cinst3.user_column_id = c3.user_column_id
496 and l_effective_date between cinst3.effective_start_date and cinst3.effective_end_date
497 and cinst3.legislation_code = 'FR'
498 and r.user_table_id = tab.user_table_id
499 and r.user_row_id = cinst.user_row_id
500 and r.user_row_id = cinst2.user_row_id
501 and r.user_row_id = cinst3.user_row_id;
502 /*
503 --
504 -- Table structures changed - select below is if everything is in one row.
505 select fnd_number.canonical_to_number(R.row_low_range_or_name) low_value
506 ,fnd_number.canonical_to_number(R.row_high_range) high_value
507 ,cinst.value rate
508 from pay_user_column_instances_f cinst
509 ,pay_user_columns c
510 ,pay_user_rows_f r
511 ,pay_user_tables tab
512 where tab.user_table_name = 'FR_COURT_ORDER_BANDS'
513 and c.user_table_id = tab.user_table_id
514 and c.legislation_code = 'FR'
515 and c.user_column_name = 'DEDUCTION_RATE'
516 and cinst.user_column_id = c.user_column_id
517 and r.user_table_id = tab.user_table_id
518 and l_effective_date between r.effective_start_date and r.effective_end_date
519 and r.legislation_code = 'FR'
520 and tab.user_key_units = 'N'
521 and cinst.user_row_id = r.user_row_id
522 and l_effective_date between cinst.effective_start_date and cinst.effective_end_date
523 and cinst.legislation_code = 'FR';
524 */
525 --
526 begin
527 --
528 hr_utility.set_location('Entering:Process - get_maximum_allowable_seizure',10);
529 --
530 select effective_date
531 into l_effective_date
532 from fnd_sessions
533 where session_id = userenv('SESSIONID');
534 --
535 /*=============================================================================
536 Work out the additional threshold - this is added to each band range
537 value, increasing the band range. This is determined by the no. of dependants
538 =============================================================================*/
539 l_addl_threshold := p_addl_threshold_per_dpndt * p_no_of_dependants;
540 --
541 hr_utility.set_location('Addl threshold is '||substr(l_addl_threshold,1,5), 15);
542 --
543 /*=============================================================================
544 Use a cursor to retreive all relevant values from User Defind Tables
545 The function hruserdt is not used because we want to loop thro' all values
546 and not just one - we don't know what the value actually is
547 =============================================================================*/
548 for r in csr_get_band_values loop
549 /*=============================================================================
550 Get the low and high value for each band and add additional threshold
551 This gives an annual figure which is divided by 12 to get monthly amount
552 Then determine how much of the net pay fits into each band selected but don't
553 mess the bottom value of zero
554 =============================================================================*/
555 if r.low_value = 0 then
556 l_low_value := 0;
557 else
558 l_low_value := (r.low_value + l_addl_threshold)/12;
559 end if;
560 l_high_value := (r.high_value + l_addl_threshold)/12;
561 l_seize_basis := least(greatest((p_net_payment - l_low_value),0),(l_high_value - l_low_value));
562 l_seize_amount := l_seize_amount + (l_seize_basis * r.rate);
563 end loop;
564 hr_utility.set_location('Seizable is '||substr(l_seize_amount,1,5),15);
565 --
566 hr_utility.set_location(' Leaving:Process - get_maximum_allowable_seizure',20);
567 return l_seize_amount;
568 --
569 end get_maximum_allowable_seizure;
570 ---------------------------------------------------------------------------------------------------------
571 procedure process_remainders (p_owed in number
572 ,p_max_shortfall_order in number
573 ,p_total_remainder in number
574 ,p_actual_total_payment in out nocopy number) is
578 --
575 begin
576 --
577 hr_utility.set_location('Entering:Process - process_remainders',10);
579 if court_order(p_max_shortfall_order).payment + p_total_remainder
580 <= p_owed then
581 --
582 hr_utility.set_location('Adding remainder:'||substr(p_total_remainder,1,5),15);
583 court_order(p_max_shortfall_order).payment := court_order(p_max_shortfall_order).payment + p_total_remainder;
584 p_actual_total_payment := p_actual_total_payment + p_total_remainder;
585 --
586 end if;
587 --
588 hr_utility.set_location(' Leaving:Process - process_remainders',20);
589 --
590 end process_remainders;
591 ---------------------------------------------------------------------------------------------------------
592 procedure init_totals (p_priority in number) is
593 begin
594 total_order(p_priority).monthly_payment := 0;
595 total_order(p_priority).amount := 0;
596 total_order(p_priority).outstanding_balance := 0;
597 total_order(p_priority).payment := 0;
598 total_order(p_priority).number_of_orders := 0;
599 total_order(p_priority).start_pos := 0;
600 total_order(p_priority).end_pos := 0;
601 end init_totals;
602 ---------------------------------------------------------------------------------------------------------
603 procedure initialise is
604 begin
605 hr_utility.set_location('Entering:Process - initialise' , 10);
606 --
607 -- Initialise the package global variables.
608 g_funds := 0;
609 g_net_pay_valid := 'Y';
610 --
611 total_order := total_order_null;
612 court_order := court_order_null;
613 court_order_index := court_order_index_null;
614 init_totals(10);
615 init_totals(15);
616 init_totals(20);
617 init_totals(30);
618 init_totals(40);
619 init_totals(50);
620 /*==========================================================================
621 Map direct element names to indirect element names. This mapping is
622 used by messages and when getting balance values for indirects.
623 ==========================================================================*/
624 map_element(10).direct_name := 'FR_FAMILY_MAINTENANCE';
625 map_element(10).indirect_name := 'FR_FAMILY_MAINTENANCE_PAYMENT';
626 map_element(15).direct_name := 'FR_FAMILY_MAINTENANCE_ARREARS';
627 map_element(15).indirect_name := 'FR_FAMILY_MAINTENANCE_ARREARS_PAYMENT';
628 map_element(20).direct_name := 'FR_FINE';
629 map_element(20).indirect_name := 'FR_FINE_PAYMENT';
630 map_element(30).direct_name := 'FR_TAX';
631 map_element(30).indirect_name := 'FR_TAX_PAYMENT';
632 map_element(40).direct_name := 'FR_MISCELLANEOUS';
633 map_element(40).indirect_name := 'FR_MISCELLANEOUS_PAYMENT';
634 map_element(50).direct_name := 'FR_SEIZURE';
635 map_element(50).indirect_name := 'FR_SEIZURE_PAYMENT';
636 hr_utility.set_location(' Leaving:Process - initialise' , 20);
637 end initialise;
638 ---------------------------------------------------------------------------------------------------------
639 procedure process_values (p_priority in number
640 ,p_funds in number
641 ,p_actual_total_payment out nocopy number) is
642 --
643 l_payment number;
644 l_prorate varchar2(1) := 'N';
645 l_sql_string varchar2(32000);
646 l_index number;
647 l_total_amount number := 0;
648 l_total_payment number := 0;
649 l_max_shortfall number := 0;
650 l_shortfall number := 0;
651 l_max_shortfall_order number := 0;
652 l_payment_pro number;
653 l_total_remainder number;
654 --
655 begin
656 --
657 p_actual_total_payment := 0;
658 --
659 hr_utility.set_location('Entering:Process - process_values' , 10);
660 --
661 if total_order(p_priority).number_of_orders > 0 then
662 --
663 total_order(p_priority).payment := least(p_funds,total_order(p_priority).outstanding_balance);
664 hr_utility.set_location('Payment is '||substr(total_order(p_priority).payment,1,5), 10);
665 --
666 if total_order(p_priority).payment < total_order(p_priority).outstanding_balance then
667 l_prorate := 'Y';
668 hr_utility.set_location('Prorate = Y', 10);
669 end if;
670 --
671 l_sql_string := null;
672 --
673 hr_utility.set_location('Building Dynamic SQL',10);
674 for i in total_order(p_priority).start_pos..total_order(p_priority).end_pos loop
675 --
676 l_index := court_order_index(i).source_id;
677 --
678 l_sql_string := l_sql_string||' select '||court_order(l_index).outstanding_balance||' balance'
679 ||','||l_index ||' l_index'
680 ||' from dual';
681 --
682 if i < total_order(p_priority).end_pos then
683 l_sql_string := l_sql_string||' union all ';
684 end if;
685 --
686 end loop;
687 hr_utility.set_location('Built Dynamic stmt',10);
688 --
689 if l_sql_string is not null then
690 l_sql_string := l_sql_string||' order by balance';
691 --
692 if l_prorate = 'Y' then
693 l_total_payment := total_order(p_priority).payment;
694 l_total_amount := total_order(p_priority).amount;
695 end if;
696 --
697 hr_utility.set_location('Opening Dynamic cursor',10);
698 open cv for l_sql_string;
702 if l_prorate = 'Y' then
699 loop
700 fetch cv into cv_outstanding_balance, cv_index;
701 exit when cv%notfound;
703 --
704 if l_total_amount = 0 then
705 hr_utility.set_location('Divide by zero error l_total_amount (1)', 15);
706 end if;
707 l_payment_pro := l_total_payment/l_total_amount;
708 l_payment := least(floor(l_payment_pro * 100 * court_order(cv_index).amount)/100, court_order(cv_index).outstanding_balance);
709 --
710 l_total_amount := l_total_amount - court_order(cv_index).amount;
711 l_total_payment := l_total_payment - l_payment;
712 --
713 else
714 l_payment := court_order(cv_index).outstanding_balance;
715 end if;
716 --
717 l_shortfall := court_order(cv_index).outstanding_balance - l_payment;
718 if l_max_shortfall < l_shortfall then
719 l_max_shortfall := l_shortfall;
720 l_max_shortfall_order := cv_index;
721 end if;
722 --
723 p_actual_total_payment := p_actual_total_payment + l_payment;
724 court_order(cv_index).payment := l_payment;
725 --
726 end loop;
727 close cv;
728 hr_utility.set_location('Closing dynamic cursor',10);
729 --
730 end if; -- if l_sql_string not null
731 --
732 l_total_remainder := total_order(p_priority).payment - p_actual_total_payment;
733 if l_total_remainder > 0 then
734 process_remainders(court_order(l_max_shortfall_order).outstanding_balance
735 ,l_max_shortfall_order
736 ,l_total_remainder
737 ,p_actual_total_payment);
738 end if;
739 --
740 end if; -- no of Arrears > 0
741 --
742 hr_utility.set_location(' Leaving:Process - process_values' , 20);
743 --
744 end process_values;
745 ---------------------------------------------------------------------------------------------------------
746 --<<***BEGIN***>>--
747 begin
748 --
749 hr_utility.set_location('Entering:function Process' , 10);
750 --
751 initialise;
752 /*=============================================================================
753 Get the number of dependants. This is used in max seizable calculation.
754 =============================================================================*/
755 open csr_no_of_dpndts;
756 fetch csr_no_of_dpndts into l_no_of_dpndts;
757 close csr_no_of_dpndts;
758 --
759 hr_utility.set_location('. Dependants Found='||l_no_of_dpndts,15);
760 g_assignment_action_id := p_assignment_action_id;
761 l_protected_amt := p_rmi;
762 --
763 if (p_net_payment_ptd + p_addl_seizable) <= l_protected_amt then
764 hr_utility.set_location('Net Pay Not Valid return = 0' , 10);
765 g_net_pay_valid := 'N';
766 return 0;
767 end if;
768 --
769 g_funds := (p_net_payment_ptd + p_addl_seizable) - l_protected_amt;
770 l_max_seizure := get_maximum_allowable_seizure(p_net_payment_ptd,nvl(l_no_of_dpndts,0),p_addl_threshold_per_dpndt) + p_addl_seizable;
771 l_remainder := g_funds - l_max_seizure;
772 --
773 /*=============================================================================
774 These are both set to 10 as I know that the first priority to be returned
775 by the cursor will be a P10 - and there will only be one of these
776 =============================================================================*/
777 l_old_priority := 10;
778 l_last_priority := 10;
779 -------------------------------------------------------------------------------------------------------
780 --<*><*><*><*><*><*><*><*><*><*><*> Populate PL/SQL Tables <*><*><*><*><*><*><*><*><*><*><*><*><*><*>--
781 -------------------------------------------------------------------------------------------------------
782 hr_utility.set_location('Populating PL/SQL tables',15);
783 i := 1;
784 for o in csr_get_court_orders loop
785 l_balance_itd := nvl(get_balance_value
786 (o.element_name
787 ,'FR Element-level ELE_ITD'
788 ,o.source_id,p_assignment_action_id),0);
789 l_balance_ptd := nvl(get_balance_value
790 (o.element_name
791 ,'FR Element-level ELE_PTD'
792 ,o.source_id,p_assignment_action_id),0);
793 --
794 begin
795 /*================================================================================================
796 If the current element being processed has an amount input value (ie not type P1) then check if
797 the total amount due has already been paid off. If it has then do not store this row and carry on.
798 Also check on Arrears and Seizure payments whether the total amount due would be exceeded by the
799 monthly payment value - if it would then set the monthly amount to the remaining that is owed,
800 as at the start of the period.
801 ================================================================================================*/
802 if o.priority <> 10 then
803 if l_balance_itd < fnd_number.canonical_to_number(o.amount) then
804 if o.priority in (15,50) and
805 ((l_balance_itd +
806 nvl(fnd_number.canonical_to_number(o.monthly_payment),0)) >
807 fnd_number.canonical_to_number(o.amount))
808 then
809 court_order(o.source_id).monthly_payment :=
813 else
810 fnd_number.canonical_to_number(o.amount) -
811 (l_balance_itd - l_balance_ptd);
812 end if;
814 raise co_paid_off;
815 end if;
816 end if;
817 /*==============================================================================
818 If the monthly payment has not already been assigned above then assign it now.
819 ==============================================================================*/
820 if not court_order.exists(o.source_id) then
821 court_order(o.source_id).monthly_payment :=
822 fnd_number.canonical_to_number(o.monthly_payment);
823 end if;
824 court_order(o.source_id).amount :=
825 fnd_number.canonical_to_number(o.amount);
826 court_order(o.source_id).outstanding_balance :=
827 court_order(o.source_id).amount - (l_balance_itd - l_balance_ptd);
828 court_order(o.source_id).balance_ptd := l_balance_ptd;
829 court_order(o.source_id).priority := o.priority;
830 --
831 court_order_index(i).source_id := o.source_id;
832 /*==============================================================================
833 Work out whether the priority should be incremented by 10 - this is only
834 applicable to P5's with a different/new processing sequence.
835 ==============================================================================*/
836 if o.priority = 50 then
837 if l_index < 50 then
838 l_index := 50;
839 l_old_proc_order :=
840 fnd_number.canonical_to_number(o.processing_order);
841 end if;
842 if l_old_proc_order <>
843 fnd_number.canonical_to_number(o.processing_order)
844 then
845 l_index := l_index + 10;
846 end if;
847 else
848 l_index := o.priority;
849 end if;
850 --
851 if l_old_priority < l_index then
852 l_last_priority := l_old_priority;
853 end if;
854 --
855 if total_order.exists(l_index) then
856 total_order(l_index).monthly_payment := total_order(l_index).monthly_payment + court_order(o.source_id).monthly_payment;
857 total_order(l_index).amount := total_order(l_index).amount + fnd_number.canonical_to_number(o.amount);
858 total_order(l_index).number_of_orders := total_order(l_index).number_of_orders+1;
859 total_order(l_index).outstanding_balance := total_order(l_index).outstanding_balance + court_order(o.source_id).outstanding_balance;
860 total_order(l_index).start_pos := total_order(l_last_priority).end_pos + 1;
861 total_order(l_index).end_pos := i;
862 else
863 /*=========================================================================================
864 This should only happen to priorities above 50 (ie for processing orders) as all values
865 below 50 have been initialised by procedure initialise. Cannot initialise for any value
866 above 50 because this depends upon the number of unique processing orders - which we
867 don't know as they are defined by the user.
868 =========================================================================================*/
869 if o.priority > 40 then
870 total_order(l_index).monthly_payment :=
871 fnd_number.canonical_to_number(o.monthly_payment);
872 total_order(l_index).payment := 0;
873 total_order(l_index).amount :=
874 fnd_number.canonical_to_number(o.amount);
875 total_order(l_index).number_of_orders := 1;
876 total_order(l_index).outstanding_balance := court_order(o.source_id).outstanding_balance;
877 total_order(l_index).start_pos := total_order(l_last_priority).end_pos + 1;
878 total_order(l_index).end_pos := i;
879 end if;
880 end if;
881 --
882 if o.priority = 50 then
883 l_old_proc_order :=
884 fnd_number.canonical_to_number(o.processing_order);
885 end if;
886 --
887 l_old_priority := l_index;
888 -- Row counter
889 i := i + 1;
890 --
891 exception
892 when co_paid_off then
893 null;
894 end;
895 --
896 end loop;
897 hr_utility.set_location('PL/SQL tables populated',15);
898 /*=====================================================================
899 Cannot have both P4's and P5's in same run - check if this is true.
900 =====================================================================*/
901 p_error_msg := validation;
902 if p_error_msg is not null then
903 hr_utility.set_location(' Leaving:function Process return = 1' , 20);
904 return 1;
905 end if;
906 hr_utility.set_location('Validation successful',15);
907 --------------------------------------------------------------------------------------------------------
908 --<*><*><*><*><*><*><*><*><*><*><*> Process Family Maintenance <*><*><*><*><*><*><*><*><*><*><*><*><*>--
909 --------------------------------------------------------------------------------------------------------
910 if total_order(10).number_of_orders > 0 then
911 --
912 l_index := court_order_index(total_order(10).start_pos).source_id;
913 --
914 total_order(10).payment := least(g_funds,total_order(10).monthly_payment);
915 court_order(l_index).payment := total_order(10).payment;
916 --
917 l_actual_total_payment := total_order(10).payment;
918 --
919 end if; -- if no of P1's > 0
920 --
924 l_actual_total_payment := 0;
921 hr_utility.set_location('P1 processing complete',15);
922 g_funds := g_funds - l_actual_total_payment;
923 l_all_payments := l_actual_total_payment;
925 --------------------------------------------------------------------------------------------------------
926 --<*><*><*><*><*><*><*><*><*> Process Family Maintenance Arrears <*><*><*><*><*><*><*><*><*><*><*><*>--
927 --------------------------------------------------------------------------------------------------------
928 if total_order(15).number_of_orders > 0 then
929 --
930 l_index := court_order_index(total_order(15).start_pos).source_id;
931 --
932 total_order(15).payment := least(g_funds,total_order(15).monthly_payment);
933 court_order(l_index).payment := total_order(15).payment;
934 --
935 l_actual_total_payment := total_order(15).payment;
936 --
937 end if; -- if no of P15's > 0
938 --
939 hr_utility.set_location('P15 processing complete',15);
940 --
941 g_funds := g_funds - l_actual_total_payment;
942 l_all_payments := l_all_payments + l_actual_total_payment;
943 --
944 l_actual_total_payment := 0;
945 l_max_shortfall := 0;
946 --------------------------------------------------------------------------------------------------------
947 --<*><*><*><*><*><*><*><*><*><*><*><*><*> Process Taxes <*><*><*><*><*><*><*><*><*><*><*><*><*><*><*>--
948 --------------------------------------------------------------------------------------------------------
949 if g_funds > 0 then
950 process_values(p_priority => 20
951 ,p_funds => g_funds
952 ,p_actual_total_payment => l_actual_total_payment);
953 end if;
954 hr_utility.set_location('P2 processing complete',15);
955 --
956 g_funds := g_funds - l_actual_total_payment;
957 l_all_payments := l_all_payments + l_actual_total_payment;
958 --------------------------------------------------------------------------------------------------------
959 --<*><*><*><*><*><*><*><*><*><*><*><*><*> Process Fines <*><*><*><*><*><*><*><*><*><*><*><*><*><*><*>--
960 --------------------------------------------------------------------------------------------------------
961 if g_funds > 0 then
962 if total_order(30).number_of_orders > 0 then
963 l_actual_total_payment := 0;
964 l_max_shortfall := 0;
965 /*=====================================================================================================
966 Calculate how much of the max seizable is left after P10, P15 and P20 payments have been calculated.
967 =====================================================================================================*/
968 l_rem_seizable := l_max_seizure - least(l_max_seizure,total_order(20).payment)
969 - greatest(((total_order(10).payment+total_order(15).payment) - l_remainder),0);
970 --
971 if l_rem_seizable > 0 then
972 g_funds := l_rem_seizable;
973 process_values(p_priority => 30
974 ,p_funds => g_funds
975 ,p_actual_total_payment => l_actual_total_payment);
976 g_funds := g_funds - l_actual_total_payment;
977 l_all_payments := l_all_payments + l_actual_total_payment;
978 end if;
979 end if;
980 end if;
981 hr_utility.set_location('P3 processing complete',15);
982 --------------------------------------------------------------------------------------------------------
983 --<*><*><*><*><*><*><*><*><*><*><*><*><*> Miscellaneous <*><*><*><*><*><*><*><*><*><*><*><*><*><*><*>--
984 --------------------------------------------------------------------------------------------------------
985 if g_funds > 0 then
986 if total_order(40).number_of_orders > 0 then
987 --
988 l_rem_seizable := l_max_seizure - least(l_max_seizure,total_order(20).payment)
989 - greatest(((total_order(10).payment+total_order(15).payment) - l_remainder),0)
990 - total_order(30).payment;
991 --
992 if l_rem_seizable > 0 then
993 --
994 g_funds := l_rem_seizable;
995 l_index := court_order_index(total_order(40).start_pos).source_id;
996 --
997 total_order(40).payment := least(g_funds,total_order(40).amount);
998 court_order(l_index).payment := total_order(40).payment;
999 --
1000 l_actual_total_payment := total_order(40).payment;
1001 --
1002 g_funds := g_funds - l_actual_total_payment;
1003 --
1004 end if;
1005 end if; -- if no of P40's > 0
1006 end if;
1007 hr_utility.set_location('P4 processing complete',15);
1008 /*=====================================================================
1009 Recalculate remaining seizable
1010 =====================================================================*/
1011 l_rem_seizable := l_max_seizure - least(l_max_seizure,total_order(20).payment)
1012 - greatest(((total_order(10).payment+total_order(15).payment) - l_remainder),0)
1013 - total_order(30).payment
1014 - total_order(40).payment;
1015 --------------------------------------------------------------------------------------------------------
1016 --<*><*><*><*><*><*><*><*><*><*><*><*><*> Process Seizure <*><*><*><*><*><*><*><*><*><*><*><*><*><*>---
1017 --------------------------------------------------------------------------------------------------------
1018 if l_rem_seizable > 0 then
1019 /*===============================================================================
1020 Set i to 50 as it is known that the first P50 element will have this priority
1021 ===============================================================================*/
1022 g_funds := l_rem_seizable;
1023 i := 50;
1024 l_max_shortfall := 0;
1025 l_shortfall := 0;
1026 --
1027 hr_utility.set_location('Processing P5 elements ..', 15);
1028 --
1029 while i <= total_order.last loop
1030 --
1031 l_actual_total_payment := 0;
1032 --
1033 if total_order(i).number_of_orders > 0 then
1034 --
1035 total_order(i).payment := least(g_funds,total_order(i).monthly_payment);
1036 l_total_payment := total_order(i).payment;
1037 l_total_amount := total_order(i).monthly_payment;
1038 --
1039 if l_total_amount = 0 then
1040 hr_utility.set_location('Divide by zero error l_total_amount (2)', 15);
1041 end if;
1042 --
1043 l_payment_pro := l_total_payment/l_total_amount;
1044 /*===============================================================================
1045 Go thro' each indivdual P50 element for the current processing order
1046 ===============================================================================*/
1047 for c in total_order(i).start_pos..total_order(i).end_pos loop
1048 --
1049 l_index := court_order_index(c).source_id;
1050 l_payment := floor(l_payment_pro * 100 * court_order(l_index).monthly_payment)/100;
1051 --
1052 l_shortfall := court_order(l_index).monthly_payment - l_payment;
1053 if l_max_shortfall < l_shortfall then
1054 l_max_shortfall := l_shortfall;
1055 l_max_shortfall_order := l_index;
1056 end if;
1057 --
1058 l_actual_total_payment := l_actual_total_payment + l_payment;
1059 court_order(l_index).payment := l_payment;
1060 --
1061 l_total_payment := l_total_payment - l_payment;
1062 l_total_amount := l_total_amount - court_order(l_index).monthly_payment;
1063 --
1064 end loop;
1065 --
1066 l_total_remainder := total_order(i).payment - l_actual_total_payment;
1067 if l_total_remainder > 0 then
1068 process_remainders(court_order(l_max_shortfall_order).outstanding_balance
1069 ,l_max_shortfall_order
1070 ,l_total_remainder
1071 ,l_actual_total_payment);
1072 end if;
1073 g_funds := g_funds - l_actual_total_payment;
1074 --
1075 end if;
1076 /*===============================================================================
1077 Go on to next processing order (sub-priority)
1078 ===============================================================================*/
1079 i := i + 10;
1080 --
1081 end loop;
1082 --
1083 hr_utility.set_location('P5 processing complete',15);
1084 --
1085 end if; -- If rem_seizable > 0
1086 --
1087 hr_utility.set_location(' Leaving:function Process return = 0' , 10);
1088 return 0;
1089 --
1090 end process;
1091 --<<***END***>>--
1092
1093 end pay_fr_court_orders;
[Home] [Help]