1 PACKAGE BODY pay_kr_wg_pkg AS
2 /* $Header: pykrffwg.pkb 120.16.12000000.1 2007/01/17 22:04:46 appldev noship $ */
3
4 --Global var introduced for bug 3223825
5 g_wg_deduction_bal_id NUMBER;
6 g_wg_interest_bal_id NUMBER;
7 --Global var introduced for bug 4498363
8 g_min_undeduct_amt NUMBER := 0;
9 g_base_undeduct_amt NUMBER := 0;
10 g_max_attachable_earnings NUMBER := 0;
11 g_change_effective_date DATE := to_date('2005/07/28', 'YYYY/MM/DD');
12 --Global variable added for the enhancement 4680413
13 g_change_eff_date_NTLA DATE := to_date('2006/04/28', 'YYYY/MM/DD');
14 g_excpn_court_order_flag VARCHAR2(1) := 'N';
15 --
16 cursor csr_get_global_values(p_global_name IN varchar2) is
17 select global_value from ff_globals_f where global_name = p_global_name;
18 --
19 g_debug boolean := hr_utility.debug_enabled;
20 -----------------------------------------------------------------------------------------------
21 -- Function calc_wage_garnishment. Function with added parameter o_adjusted_amount.
22 -- This function calls another version with the parameter p_attachment_sequence_no
23 -----------------------------------------------------------------------------------------------
24 FUNCTION calc_wage_garnishment( p_assignment_id IN NUMBER,
25 p_assignment_action_id IN NUMBER,
26 p_date_earned IN DATE,
27 p_element_entry_id IN NUMBER,
28 p_net_earnings IN NUMBER,
29 p_run_type IN VARCHAR2,
30 p_attachment_amount OUT NOCOPY NUMBER,
31 p_adjusted_amount OUT NOCOPY NUMBER,
32 p_attach_total_base OUT NOCOPY NUMBER,
33 p_real_attach_total OUT NOCOPY NUMBER,
34 p_emp_attach_total OUT NOCOPY NUMBER,
35 p_interest_amount OUT NOCOPY NUMBER,
36 p_adjustment_amount OUT NOCOPY NUMBER,
37 p_unadjusted_amount OUT NOCOPY NUMBER,
38 p_stop_flag OUT NOCOPY VARCHAR2,
39 p_message OUT NOCOPY VARCHAR2,
40 p_curr_attach_seq_no OUT NOCOPY VARCHAR2,
41 p_curr_case_number OUT NOCOPY VARCHAR2,
42 p_payout_date OUT NOCOPY DATE,
43 p_date_paid IN DATE,
44 p_wg_attach_earnings_mtd IN NUMBER,
45 p_wg_deductions_mtd IN NUMBER
46
47 ) RETURN NUMBER
48 IS
49
50 l_attachment_seq_no VARCHAR2(100);
51 l_return NUMBER := 0;
52 BEGIN
53 --
54 --
55 l_attachment_seq_no := pay_kr_wg_report_pkg.get_attach_seq_no(p_element_entry_id);
56
57
58 if g_debug then
59 hr_utility.set_location('Got the attachment seq no : '||l_attachment_seq_no, 10);
60 end if;
61
62 l_return := calc_wage_garnishment( p_assignment_id => p_assignment_id ,
63 p_assignment_action_id => p_assignment_action_id ,
64 p_date_earned => p_date_earned ,
65 p_attachment_seq_no => l_attachment_seq_no ,
66 p_net_earnings => p_net_earnings ,
67 p_run_type => p_run_type ,
68 p_attachment_amount => p_attachment_amount ,
69 p_adjusted_amount => p_adjusted_amount ,
70 p_attach_total_base => p_attach_total_base ,
71 p_real_attach_total => p_real_attach_total ,
72 p_emp_attach_total => p_emp_attach_total ,
73 p_interest_amount => p_interest_amount ,
74 p_adjustment_amount => p_adjustment_amount ,
75 p_unadjusted_amount => p_unadjusted_amount ,
76 p_stop_flag => p_stop_flag ,
77 p_message => p_message ,
78 p_curr_attach_seq_no => p_curr_attach_seq_no ,
79 p_curr_case_number => p_curr_case_number ,
80 p_payout_date => p_payout_date ,
81 p_date_paid => p_date_paid ,
82 p_wg_attach_earnings_mtd => p_wg_attach_earnings_mtd ,
83 p_wg_deductions_mtd => p_wg_deductions_mtd );
84
85
86 RETURN l_return;
87
88 END calc_wage_garnishment;
89 --==================================================================================================
90
91 ----------------------------------------------------------------------------------------------------
92 -- Function calc_wage_garnishments.
93 ----------------------------------------------------------------------------------------------------
94 FUNCTION calc_wage_garnishment( p_assignment_id IN NUMBER,
95 p_assignment_action_id IN NUMBER,
96 p_date_earned IN DATE,
97 p_attachment_seq_no IN VARCHAR2,
98 p_net_earnings IN NUMBER,
99 p_run_type IN VARCHAR2,
100 p_attachment_amount OUT NOCOPY NUMBER,
101 p_adjusted_amount OUT NOCOPY NUMBER,
102 p_attach_total_base OUT NOCOPY NUMBER,
103 p_real_attach_total OUT NOCOPY NUMBER,
104 p_emp_attach_total OUT NOCOPY NUMBER,
105 p_interest_amount OUT NOCOPY NUMBER,
106 p_adjustment_amount OUT NOCOPY NUMBER,
107 p_unadjusted_amount OUT NOCOPY NUMBER,
108 p_stop_flag OUT NOCOPY VARCHAR2,
109 p_message OUT NOCOPY VARCHAR2,
110 p_curr_attach_seq_no OUT NOCOPY VARCHAR2,
111 p_curr_case_number OUT NOCOPY VARCHAR2,
112 p_payout_date OUT NOCOPY DATE,
113 p_date_paid IN DATE,
114 p_wg_attach_earnings_mtd IN NUMBER,
115 p_wg_deductions_mtd IN NUMBER
116 ) RETURN NUMBER
117 IS
118 c_all_attachment CHAR(1) DEFAULT 'N';
119 d_all_attach_date DATE ;
120 c_actual_attachment CHAR(1) DEFAULT 'N';
121 c_obligation_release CHAR(1) DEFAULT 'N';
122 c_all_attach_full_paid CHAR(1) DEFAULT 'N';
123 c_waiting_for_all_attach CHAR(1) DEFAULT 'N';
124 c_redistribution_required CHAR(1) DEFAULT 'N'; --4866417
125
126 g_emp_total t_emp_total ;
127 g_actual_attach tab_actual_attach;
128
129 l_emp_attach_total NUMBER := 0;
130 l_correction_amount NUMBER := 0;
131 l_period_start_date DATE ;
132 l_period_end_date DATE ;
133 l_payout_date DATE ;
134 l_payroll_action_id pay_assignment_actions.payroll_action_id%TYPE;
135
136 --Local variables introduced for Bug :4498363--
137 l_count_court_orders NUMBER := 0;
138 l_count_actual_attach NUMBER := 0;
139 l_earnings NUMBER := 0;
140 l_count_oblig_rel_before NUMBER := 0;
141 l_count_oblig_rel_after NUMBER := 0;
142
143 e_duplicate_attach_exception EXCEPTION;
144 e_prev_case_notfound_exception EXCEPTION;
145 e_all_attachment_exception EXCEPTION;
146
147 -- Bug 2715365 Cursors for Payroll Period dates
148
149 Cursor csr_pay_period(p_asg_action_id NUMBER)
150 IS
151 select TPERIOD.start_date
152 ,TPERIOD.end_date
153 ,PACTION.payroll_action_id
154 from pay_payroll_actions PACTION
155 ,pay_assignment_actions ASSACTION
156 ,per_time_periods TPERIOD
157 ,per_time_period_types TPTYPE
158 where ASSACTION.assignment_action_id = p_asg_action_id
159 and PACTION.payroll_action_id = ASSACTION.payroll_action_id
160 and TPERIOD.time_period_id = PACTION.time_period_id
161 and TPTYPE.period_type = TPERIOD.period_type;
162 --
163 -- Local Procedures
164 --
165 -- 1. load_court_orders
166 -- 2. calc_net_earnings -- Bug : 4498363
167 -- 3. calc_real_attachment_total
168 -- 4. calc_emp_attachment_total
169 -- 5. distribute_paid_amt
170 -- 6. get_court_order_start_date.
171 -- 7. is_oblig_release_processed
172 --
173 ----------------------------------------------------------------
174 -- Procedure to get all current court orders
175 ----------------------------------------------------------------
176 PROCEDURE load_court_orders
177 IS
178 CURSOR csr_etype(p_element_name VARCHAR2) IS
179 SELECT element_type_id
180 from pay_element_types_f
181 where element_name = p_element_name
182 and legislation_code = 'KR'
183 and business_group_id is null
184 and p_date_earned between effective_start_date and effective_end_date
185 group by element_type_id;
186
187 CURSOR csr_wg(p_element_type_id number) IS
188 SELECT pee.assignment_id,
189 pee.element_entry_id,
190 peev.input_value_id,
191 pee.effective_start_date court_order_start_date,
192 pee.entry_information1 interest_from_date1,
193 pee.entry_information2 interest_to_date1,
194 pee.entry_information3 interest_base1,
195 pee.entry_information4 interest_rate1,
196 pee.entry_information5 interest_from_date2,
197 pee.entry_information6 interest_to_date2,
198 pee.entry_information7 interest_base2,
199 pee.entry_information8 interest_rate2,
200 pee.entry_information9 interest_from_date3,
201 pee.entry_information10 interest_to_date3,
202 pee.entry_information11 interest_base3,
203 pee.entry_information12 interest_rate3,
204 pee.entry_information13 interest_from_date4,
205 pee.entry_information14 interest_to_date4,
206 pee.entry_information15 interest_base4,
207 pee.entry_information16 interest_rate4,
208 pee.entry_information17 interest_from_date5,
209 pee.entry_information18 interest_to_date5,
210 pee.entry_information19 interest_base5,
211 pee.entry_information20 interest_rate5,
212 pee.entry_information21 previous_case_number,
213 pee.entry_information23 payout_date,
214 pee.entry_information24 court_order_origin,
215 name,
216 peev.screen_entry_value
217 from pay_element_entry_values_f peev,
218 pay_element_entries_f pee,
219 pay_element_links_f pel,
220 pay_input_values_f piv
221 where pel.element_type_id = p_element_type_id
222 and piv.element_type_id = p_element_type_id
223 and piv.element_type_id = pel.element_type_id
224 and p_date_earned between pel.effective_start_date and pel.effective_end_date
225 and p_date_earned between piv.effective_start_date and piv.effective_end_date
226 and peev.input_value_id = piv.input_value_id
227 and pee.element_link_id = pel.element_link_id
228 and pee.assignment_id = p_assignment_id
229 and nvl(pee.entry_type, 'E') = 'E'
230 and p_date_earned between pee.effective_start_date and pee.effective_end_date
231 and peev.element_entry_id = pee.element_entry_id
232 and peev.effective_start_date = pee.effective_start_date
233 and peev.effective_end_date = pee.effective_end_date
234 -- Bug 2786290
235 -- Bug 4866417 -- removed the not exists clause introduced for bug 2786290
236 -- Order by clause changes for bug 268885
237 order by 1,2,3 desc;
238
239
240 l_element_type_id NUMBER := 0;
241 l_element_entry_id NUMBER := 0;
242 l_court_order_no NUMBER := 0;
243 l_valid_attach_seq VARCHAR2(1) := 'Y';
244 l_interest_loaded BOOLEAN := FALSE;
245 l_check_condition VARCHAR2(1) := 'N'; --4498363
246 ---------------------------------------------------------------------
247 -- Bug : 4533467
248 ---------------------------------------------------------------------
249 FUNCTION get_court_order_start_date(p_element_entry_id IN NUMBER)
250 RETURN DATE
251 IS
252 --
253 Cursor csr_get_court_order_start_date(p_element_entry_id IN NUMBER) IS
254 select min(effective_start_date)
255 from pay_element_entries_f pee
256 where pee.element_entry_id = p_element_entry_id;
257 --
258 l_court_order_start_date DATE;
259 --
260 BEGIN
261 OPEN csr_get_court_order_start_date(p_element_entry_id);
262 FETCH csr_get_court_order_start_date into l_court_order_start_date;
263 CLOSE csr_get_court_order_start_date;
264 --
265 RETURN l_court_order_start_date;
266 END get_court_order_start_date;
267
268 ---------------------------------------------------------------------
269 -- Bug : 4866417
270 ---------------------------------------------------------------------
271
272 FUNCTION is_oblig_release_processed(p_element_entry_id IN NUMBER,
273 p_element_type_id IN NUMBER)
274 RETURN VARCHAR2
275 IS
276 l_oblig_release_processed VARCHAR2(1) := 'N';
277 --
278 Cursor csr_oblig_release_processed IS
279 SELECT 'Y'
280 from pay_run_results prr
281 ,pay_run_result_values prrv
282 ,pay_input_values_f piv
283 where prr.source_id = p_element_entry_id
284 and prr.entry_type IN ('I','E')
285 and prr.status = 'P'
286 and prrv.run_result_id = prr.run_result_id
287 and piv.input_value_id = prrv.input_value_id
288 and piv.element_type_id = p_element_type_id
289 and piv.name = 'Obligation Release'
290 and prrv.result_value = 'Y'
291 and piv.legislation_code = 'KR'
292 and p_date_earned between piv.effective_start_date and piv.effective_end_date;
293 --
294 BEGIN
295 OPEN csr_oblig_release_processed;
296 FETCH csr_oblig_release_processed into l_oblig_release_processed;
297 CLOSE csr_oblig_release_processed;
298
299 return l_oblig_release_processed;
300
301 END is_oblig_release_processed;
302
303 ---------------------------------------------------------------------
304 -- Bug 2893245
305 -- Function get_previous_payout_date
306 ---------------------------------------------------------------------
307 FUNCTION get_previous_payout_date (p_element_entry_id IN NUMBER, p_attachment_seq_no IN VARCHAR2) RETURN DATE
308 IS
309
310 CURSOR csr_prev_payout IS
311 SELECT fnd_date.canonical_to_date(prrv.result_value)
312 from pay_run_results prr
313 ,pay_run_result_values prrv
314 ,pay_element_types_f pet
315 ,pay_input_values_f piv
316 where prr.source_id = p_element_entry_id
317 and prr.entry_type IN ('I','E')
318 and prr.status IN ('P')
319 and prrv.run_result_id = prr.run_result_id
320 and piv.input_value_id = prrv.input_value_id
321 and pet.element_name = 'WG Results'
322 and pet.legislation_code = 'KR'
323 and piv.element_type_id = pet.element_type_id
324 and piv.name = 'Payout Date'
325 and piv.legislation_code = 'KR'
326 and piv.business_group_id IS NULL
327 and p_date_earned between piv.effective_start_date and piv.effective_end_date
328 order by prr.assignment_action_id desc;
329 --
330 -- Bug : 4859775
331 -- Removed join with pay_element_types_f and used the cursor csr_etype
332 -- to get the element_type_id of 'WG Results'
333 --
334 CURSOR csr_prev_payout_bal_adj(p_element_type_id IN NUMBER) IS
335 SELECT fnd_date.canonical_to_date(prrv.result_value)
336 from pay_payroll_actions ppa
337 ,pay_assignment_actions paa
338 ,pay_run_results prr
339 ,pay_run_result_values prrv
340 ,pay_run_result_values prrv1
341 ,pay_input_values_f piv
342 ,pay_input_values_f piv1
343 where paa.assignment_id = p_assignment_id
344 and prr.assignment_action_id = paa.assignment_action_id
345 and paa.action_status = 'C'
346 and ppa.payroll_action_id = paa.payroll_action_id
347 and ppa.action_type = 'B'
348 and ppa.effective_date <= p_date_earned
349 -- following condition is put for performance reasons
350 and paa.assignment_action_id <= p_assignment_action_id
351 and prr.status = 'P'
352 and prr.entry_type = 'B'
353 and prr.element_type_id = p_element_type_id
354 and prrv1.run_result_id = prr.run_result_id
355 and piv1.name = 'Attachment Seq No'
356 and piv1.legislation_code = 'KR'
357 and piv1.business_group_id IS NULL
358 and piv1.element_type_id = p_element_type_id
359 and piv1.input_value_id = prrv1.input_value_id
360 and prrv1.result_value = p_attachment_seq_no
361 and piv.name = 'Payout Date'
362 and piv.legislation_code = 'KR'
363 and piv.business_group_id IS NULL
364 and prrv.run_result_id = prr.run_result_id
365 and piv.input_value_id = prrv.input_value_id
366 and piv.element_type_id = p_element_type_id
367 and p_date_earned between piv.effective_start_date and piv.effective_end_date
368 order by ppa.effective_date desc;
369
370 l_prev_payout_date DATE;
371 l_prev_payout_date_bal_adj DATE;
372 l_elem_type_id NUMBER;
373
374 BEGIN
375
376 OPEN csr_prev_payout;
377 FETCH csr_prev_payout INTO l_prev_payout_date;
378 CLOSE csr_prev_payout;
379
380 --
381 -- Bug : 4859775
382 -- Added code to get element_type_id for 'WG Results'
383 --
384 OPEN csr_etype('WG Results');
385 FETCH csr_etype into l_elem_type_id;
386 CLOSE csr_etype;
387
388 OPEN csr_prev_payout_bal_adj(l_elem_type_id);
389 FETCH csr_prev_payout_bal_adj INTO l_prev_payout_date_bal_adj;
390 CLOSE csr_prev_payout_bal_adj;
391
392 -------------------------------------------------------------------------
393 -- If both are not null then return the higher date
394 -- If one of them is not null then return the date which is not null
395 -- If both of them are null then return NULL
396 -------------------------------------------------------------------------
397
398 IF l_prev_payout_date IS NULL OR l_prev_payout_date_bal_adj IS NULL THEN
399
400 IF l_prev_payout_date_bal_adj IS NOT NULL THEN
401 l_prev_payout_date := l_prev_payout_date_bal_adj;
402 END IF;
403
404 ELSIF l_prev_payout_date_bal_adj > l_prev_payout_date THEN
405 l_prev_payout_date := l_prev_payout_date_bal_adj;
406
407 END IF;
408 if g_debug then
409 hr_utility.set_location('Previous Payout Date is : '||to_char(l_prev_payout_date), 1);
410 end if;
411
412 RETURN l_prev_payout_date;
413
414 EXCEPTION
415 WHEN OTHERS THEN
416 if g_debug then
417 hr_utility.set_location('Error in get_previous_payout_date. Message : '||substr(sqlerrm,1,200), -10);
418 end if;
419 RAISE;
420
421 END get_previous_payout_date;
422 -----------------------------------------------------------------
423
424 ---------------------------------------------------------------------
425 -- Function processing_first_time
426 ---------------------------------------------------------------------
427 FUNCTION processing_first_time (p_attach_seq_no IN VARCHAR2) RETURN BOOLEAN
428 IS
429
430 CURSOR csr_defined_bal_id(p_balance_name IN VARCHAR2, p_dim_name IN VARCHAR2)
431 IS
432 SELECT pdb.defined_balance_id
433 from pay_balance_types pbt
434 ,pay_balance_dimensions pbd
435 ,pay_defined_balances pdb
436 where pbt.balance_name = p_balance_name
437 and pbt.legislation_code ='KR'
438 and pbd.database_item_suffix = p_dim_name
439 and pbd.legislation_code ='KR'
440 and pdb.balance_type_id = pbt.balance_type_id
441 and pdb.balance_dimension_id = pbd.balance_dimension_id
442 and pdb.legislation_code ='KR';
443
444 l_value NUMBER DEFAULT 0 ;
445 -- Bug 3223825
446 l_defined_balance_id NUMBER DEFAULT 0;
447
448 BEGIN
449 -- Bug 2762478
450 pay_balance_pkg.set_context('SOURCE_TEXT', p_attach_seq_no);
451
452 -- Bug 3223825
453 IF g_wg_deduction_bal_id IS NULL THEN
454 IF g_debug THEN
455 hr_utility.trace('first attempt');
456 END IF;
457
458 OPEN csr_defined_bal_id('WG Deductions', '_ASG_WG_ITD');
459 FETCH csr_defined_bal_id INTO l_defined_balance_id;
460 CLOSE csr_defined_bal_id;
461
462 g_wg_deduction_bal_id:=l_defined_balance_id;
463 ELSE
464
465 IF g_debug THEN
466 hr_utility.trace('sec attempt');
467 END IF;
468
469 l_defined_balance_id := g_wg_deduction_bal_id;
470
471 END IF;
472 -- Bug 3435686
473 l_value:=pay_balance_pkg.get_value (l_defined_balance_id, p_assignment_action_id);
474
475 if g_debug then
476 hr_utility.trace('l_value wg deduction ' || l_value) ;
477 end if;
478
479 IF l_value > 0 THEN
480
481 RETURN FALSE;
482
483 ELSE
484
485 IF g_wg_interest_bal_id is NULL THEN
486 IF g_debug THEN
487 hr_utility.trace('first attempt');
488 END IF;
489
490 OPEN csr_defined_bal_id('WG Paid Interest', '_ASG_WG_ITD');
491 FETCH csr_defined_bal_id INTO l_defined_balance_id;
492 CLOSE csr_defined_bal_id;
493
494 g_wg_interest_bal_id := l_defined_balance_id;
495
496 ELSE
497 IF g_debug THEN
498 hr_utility.trace('sec attempt');
499 end if;
500
501 l_defined_balance_id:=g_wg_interest_bal_id;
502
503 END IF;
504 -- Bug 3435686
505 l_value:=pay_balance_pkg.get_value (l_defined_balance_id, p_assignment_action_id);
506
507 IF g_debug THEN
508 hr_utility.trace('l_value wg paid interest ' || l_value);
509 END IF;
510
511 IF l_value > 0 THEN
512 RETURN FALSE;
513 END IF;
514
515 END IF;
516
517 RETURN TRUE;
518
519 EXCEPTION
520 WHEN OTHERS THEN
521 if g_debug then
522 hr_utility.set_location('Error in Processing First Time. Message : '||substr(sqlerrm,1,200), -10);
523 end if;
524 RAISE;
525
526 END processing_first_time;
527 -----------------------------------------------------------------
528
529 -----------------------------------------------------------------
530 -- procedure validate_interest_bands
531 ------------------------------------------------------------------------------
532 -- Bug 2762097. Introduced this procedure to make interest calculation modular
533 -- Bug 2822757. check for previous payout date included.
534 -- Bug 2860586. check for Bug 2822757 is modified. >= is used instead of >
535 -- Bug 2893245. NVL used for previous_payout_date if court order is not processing first time
536 -- Payroll Period Start Date is used if previous payout date is null.
537 -- Bug 3062873. Outer Period Interest Calculation.
538 -- Check for interest_to_date5 >= l_period_start_date excluded.
539 ---------------------------------------------------------------------------------------------
540 PROCEDURE validate_interest_bands (i IN PLS_INTEGER)
541 IS
542 l_processing_first_time BOOLEAN;
543
544 BEGIN
545 -----------------------------------------------------------------
546 -- User must enter the payout date while running the payroll run.
547 -- However this is not a mandatory parameter.
548 -- Payout Date is defaulted to Date Paid.
549 -----------------------------------------------------------------
550 IF l_payout_date IS NULL THEN
551 l_payout_date := l_period_end_date;
552 END IF;
553
554 ---------------------------------------------------------------------------------------------
555 -- If current payout date is less than previous payout date, then interest band is not valid.
556 -- Set interest_calc_to_date to null.
557 ----------------------------------------------------------------------------------------------
558 -- Otherwise, take the least of user entered value of interest_to_date and payout date - 1
559 -- because if interest band is effetive then interest should be calculated upto payout date - 1.
560 -------------------------------------------------------------------------------------------------
561 IF l_payout_date <= g_court_orders(i).previous_payout_date THEN
562 g_court_orders(i).interest_to_date1 := NULL;
563 g_court_orders(i).interest_to_date2 := NULL;
564 g_court_orders(i).interest_to_date3 := NULL;
565 g_court_orders(i).interest_to_date4 := NULL;
566 g_court_orders(i).interest_to_date5 := NULL;
567 ELSE
568 g_court_orders(i).interest_to_date1 := least(g_court_orders(i).interest_to_date1, l_payout_date-1);
569 g_court_orders(i).interest_to_date2 := least(g_court_orders(i).interest_to_date2, l_payout_date-1);
570 g_court_orders(i).interest_to_date3 := least(g_court_orders(i).interest_to_date3, l_payout_date-1);
571 g_court_orders(i).interest_to_date4 := least(g_court_orders(i).interest_to_date4, l_payout_date-1);
572 g_court_orders(i).interest_to_date5 := least(g_court_orders(i).interest_to_date5, l_payout_date-1);
573 END IF;
574
575 l_processing_first_time := processing_first_time(g_court_orders(l_court_order_no).attachment_sequence_no);
576
577 ----------------------------------------------------------------------------------------------------
578 -- Interest From Date will be populated only if interest period is valid for the current payroll run
579 -- At all later stages Interest Rate is used to identify interest rate based calculations
580 -- If interest from date is not null then apply interest calculation.
581 ----------------------------------------------------------------------------------------------
582 -- If Interest Rate or Interest From Date is not specified for an interest band, it is invalid
583 -------------------------------------------------------------------------------------------------------------
584 -- If Interest To Date for an interest band in set to null because of the above condition, it becomes invalid
585 -------------------------------------------------------------------------------------------------------------
586 -- If user has entered interest from date and if interest from date is for a future date after payroll period
587 -- Then this interest band will not be considered in this payroll run.
588 ----------------------------------------------------------------------------------------------------------------
589 -- Interest_calc_from_date must be less than or equal to interest_calc_to_date for an interest band to be valid.
590 ----------------------------------------------------------------------------------------------------------------
591 -- For the first payroll run, interest period is derived from user entries in Element Entry DDF.
592 -- For all payroll runs thereafter, interest period is derived from previous payout date.
593 ------------------------------------------------------------------------------------------------
594 -- Interest Band 1
595 ------------------
596 IF NOT (g_court_orders(i).interest_rate1 > 0 AND
597 g_court_orders(i).interest_to_date1 IS NOT NULL AND
598 g_court_orders(i).interest_from_date1 IS NOT NULL AND
599 g_court_orders(i).interest_to_date1 >= nvl(g_court_orders(i).previous_payout_date, g_court_orders(i).interest_to_date1) AND
600 g_court_orders(i).interest_from_date1 <= g_court_orders(i).interest_to_date1)
601 THEN
602 g_court_orders(i).interest_from_date1 := NULL;
603
604 ELSIF g_court_orders(i).interest_to_date1 < l_period_start_date THEN
605 IF NOT l_processing_first_time THEN
606
607 IF NOT g_court_orders(i).previous_payout_date < g_court_orders(i).interest_to_date1 THEN
608 g_court_orders(i).interest_from_date1 := NULL;
609 ELSE
610 g_court_orders(i).interest_from_date1 := greatest(g_court_orders(i).previous_payout_date, g_court_orders(i).interest_from_date1);
611 END IF;
612
613 END IF;
614
615 ELSIF NOT l_processing_first_time THEN
616 g_court_orders(i).interest_from_date1 := greatest(g_court_orders(i).interest_from_date1, nvl(g_court_orders(i).previous_payout_date, l_period_start_date));
617 END IF;
618 ------------------
619 -- Interest Band 2
620 ------------------
621 IF NOT (g_court_orders(i).interest_rate2 > 0 AND
622 g_court_orders(i).interest_to_date2 IS NOT NULL AND
623 g_court_orders(i).interest_from_date2 IS NOT NULL AND
624 g_court_orders(i).interest_to_date2 >= nvl(g_court_orders(i).previous_payout_date, g_court_orders(i).interest_to_date2) AND
625 g_court_orders(i).interest_from_date2 <= g_court_orders(i).interest_to_date2)
626 THEN
627 g_court_orders(i).interest_from_date2 := NULL;
628
629 ELSIF g_court_orders(i).interest_to_date2 < l_period_start_date THEN
630 IF NOT l_processing_first_time THEN
631
632 IF NOT g_court_orders(i).previous_payout_date < g_court_orders(i).interest_to_date2 THEN
633 g_court_orders(i).interest_from_date2 := NULL;
634 ELSE
635 g_court_orders(i).interest_from_date2 := greatest(g_court_orders(i).previous_payout_date, g_court_orders(i).interest_from_date2);
636 END IF;
637
638 END IF;
639
640 ELSIF NOT l_processing_first_time THEN
641 g_court_orders(i).interest_from_date2 := greatest(g_court_orders(i).interest_from_date2, nvl(g_court_orders(i).previous_payout_date, l_period_start_date));
642 END IF;
643 ------------------
644 -- Interest Band 3
645 ------------------
646 IF NOT (g_court_orders(i).interest_rate3 > 0 AND
647 g_court_orders(i).interest_to_date3 IS NOT NULL AND
648 g_court_orders(i).interest_from_date3 IS NOT NULL AND
649 g_court_orders(i).interest_to_date3 >= nvl(g_court_orders(i).previous_payout_date, g_court_orders(i).interest_to_date3) AND
650 g_court_orders(i).interest_from_date3 <= g_court_orders(i).interest_to_date3)
651 THEN
652 g_court_orders(i).interest_from_date3 := NULL;
653
654 ELSIF g_court_orders(i).interest_to_date3 < l_period_start_date THEN
655 IF NOT l_processing_first_time THEN
656
657 IF NOT g_court_orders(i).previous_payout_date < g_court_orders(i).interest_to_date3 THEN
658 g_court_orders(i).interest_from_date3 := NULL;
659 ELSE
660 g_court_orders(i).interest_from_date3 := greatest(g_court_orders(i).previous_payout_date, g_court_orders(i).interest_from_date3);
661 END IF;
662
663 END IF;
664
665 ELSIF NOT l_processing_first_time THEN
666 g_court_orders(i).interest_from_date3 := greatest(g_court_orders(i).interest_from_date3, nvl(g_court_orders(i).previous_payout_date, l_period_start_date));
667 END IF;
668 ------------------
669 -- Interest Band 4
670 ------------------
671 IF NOT (g_court_orders(i).interest_rate4 > 0 AND
672 g_court_orders(i).interest_to_date4 IS NOT NULL AND
673 g_court_orders(i).interest_from_date4 IS NOT NULL AND
674 g_court_orders(i).interest_to_date4 >= nvl(g_court_orders(i).previous_payout_date, g_court_orders(i).interest_to_date4) AND
675 g_court_orders(i).interest_from_date4 <= g_court_orders(i).interest_to_date4)
676 THEN
677 g_court_orders(i).interest_from_date4 := NULL;
678
679 ELSIF g_court_orders(i).interest_to_date4 < l_period_start_date THEN
680 IF NOT l_processing_first_time THEN
681
682 IF NOT g_court_orders(i).previous_payout_date < g_court_orders(i).interest_to_date4 THEN
683 g_court_orders(i).interest_from_date4 := NULL;
684 ELSE
685 g_court_orders(i).interest_from_date4 := greatest(g_court_orders(i).previous_payout_date, g_court_orders(i).interest_from_date4);
686 END IF;
687
688 END IF;
689
690 ELSIF NOT l_processing_first_time THEN
691 g_court_orders(i).interest_from_date4 := greatest(g_court_orders(i).interest_from_date4, nvl(g_court_orders(i).previous_payout_date, l_period_start_date));
692 END IF;
693 ------------------
694 -- Interest Band 5
695 ------------------
696 IF NOT (g_court_orders(i).interest_rate5 > 0 AND
697 g_court_orders(i).interest_to_date5 IS NOT NULL AND
698 g_court_orders(i).interest_from_date5 IS NOT NULL AND
699 g_court_orders(i).interest_to_date5 >= nvl(g_court_orders(i).previous_payout_date, g_court_orders(i).interest_to_date5) AND
700 g_court_orders(i).interest_from_date5 <= g_court_orders(i).interest_to_date5)
701 THEN
702 g_court_orders(i).interest_from_date5 := NULL;
703
704 ELSIF g_court_orders(i).interest_to_date5 < l_period_start_date THEN
705 IF NOT l_processing_first_time THEN
706
707 IF NOT g_court_orders(i).previous_payout_date < g_court_orders(i).interest_to_date5 THEN
708 g_court_orders(i).interest_from_date5 := NULL;
709 ELSE
710 g_court_orders(i).interest_from_date5 := greatest(g_court_orders(i).previous_payout_date, g_court_orders(i).interest_from_date5);
711 END IF;
712
713 END IF;
714
715 ELSIF NOT l_processing_first_time THEN
716 g_court_orders(i).interest_from_date5 := greatest(g_court_orders(i).interest_from_date5, nvl(g_court_orders(i).previous_payout_date, l_period_start_date));
717 END IF;
718
719 EXCEPTION
720 WHEN OTHERS THEN
721 if g_debug then
722 hr_utility.set_location('Error in Interest Validation. Message : '||substr(sqlerrm,1,200), -20);
723 end if;
724 RAISE;
725
726 END validate_interest_bands;
727 -----------------------------------------------------------------
728
729 --------------------------------------------------------------------------------------
730 --Bug : 4498363
731 -- Function returns 'Y' if the corresponding Provisional court order's start date of an
732 -- 'Actual Seizure and Collection' court received after 28-JUL-2005 is greater than
733 -- 28-JUL-2005
734 --------------------------------------------------------------------------------------
735 FUNCTION check_exception_case (p_prev_case_num IN VARCHAR2)
736 RETURN VARCHAR2
737 IS
738 l_provisional_date DATE;
739 l_court_order_origin VARCHAR2(30);
740 --
741 cursor csr_get_provisional_date(p_prev_case_num IN VARCHAR2) is
742 SELECT pee.effective_start_date,pee.entry_information24
743 from pay_element_entry_values_f peev,
744 pay_element_entries_f pee,
745 pay_element_links_f pel,
746 pay_input_values_f piv
747 where pel.element_type_id = l_element_type_id
748 and piv.element_type_id = l_element_type_id
749 and piv.element_type_id = pel.element_type_id
750 and p_date_earned between pel.effective_start_date and pel.effective_end_date
751 and p_date_earned between piv.effective_start_date and piv.effective_end_date
752 and peev.input_value_id = piv.input_value_id
753 and pee.element_link_id = pel.element_link_id
754 and pee.assignment_id = p_assignment_id
755 and nvl(pee.entry_type, 'E') = 'E'
756 and p_date_earned between pee.effective_start_date and pee.effective_end_date
757 and peev.element_entry_id = pee.element_entry_id
758 and peev.effective_start_date = pee.effective_start_date
759 and peev.effective_end_date = pee.effective_end_date
760 and piv.name = 'Case Number'
761 and peev.screen_entry_value = p_prev_case_num
762 order by pee.effective_start_date;
763
764 BEGIN
765 OPEN csr_get_provisional_date(p_prev_case_num);
766 FETCH csr_get_provisional_date into l_provisional_date,l_court_order_origin;
767 CLOSE csr_get_provisional_date;
768 --
769 IF l_provisional_date >= g_change_effective_date and l_court_order_origin = '01' THEN
770 RETURN 'Y';
771 -- Bug 4680413
772 -- If the date recieved for Provisional Court Order is after 28-APR-2006,then
773 -- Court Order will follow new rule.
774 ELSIF l_provisional_date >= g_change_eff_date_NTLA THEN
775 RETURN 'Y';
776 ELSE
777 RETURN 'N';
778 END IF;
779 if g_debug then
780 hr_utility.trace('l_count_court_orders '||l_count_court_orders);
781 hr_utility.trace('p_assignment_id '||p_assignment_id);
782 hr_utility.trace('l_provisional_date '||l_provisional_date);
783 hr_utility.trace('p_provisional_case_num '||p_prev_case_num);
784 end if;
785 END check_exception_case;
786
787 --------------------------------------------------------------------------------------
788 -- Procedure load_court_orders begins here
789 --------------------------------------------------------------------------------------
790 BEGIN
791
792 OPEN csr_etype('Wage Garnishments');
793 FETCH csr_etype into l_element_type_id;
794 CLOSE csr_etype;
795
796 FOR i IN csr_wg(l_element_type_id)
797 LOOP
798
799 -- Element entry is a new court order if it has not already been loaded
800 -- Load values in the same court order if element entry id is same as previously loaded
801
802 IF (l_element_entry_id = 0) OR (l_element_entry_id <> i.element_entry_id) THEN
803
804 l_court_order_no := g_court_orders.count + 1;
805 l_interest_loaded := FALSE;
806 l_element_entry_id := i.element_entry_id;
807
808 g_court_orders(l_court_order_no).court_order_start_date := get_court_order_start_date(i.element_entry_id); --Bug : 4533467
809 g_court_orders(l_court_order_no).previous_case_number := ltrim(rtrim(i.previous_case_number));
810 g_court_orders(l_court_order_no).interest_rate1 := to_number(nvl(i.interest_rate1,'0'));
811 g_court_orders(l_court_order_no).interest_calculation_base1 := to_number(nvl(i.interest_base1,'0'));
812 g_court_orders(l_court_order_no).interest_rate2 := to_number(nvl(i.interest_rate2,'0'));
813 g_court_orders(l_court_order_no).interest_calculation_base2 := to_number(nvl(i.interest_base2,'0'));
814 g_court_orders(l_court_order_no).interest_rate3 := to_number(nvl(i.interest_rate3,'0'));
815 g_court_orders(l_court_order_no).interest_calculation_base3 := to_number(nvl(i.interest_base3,'0'));
816 g_court_orders(l_court_order_no).interest_rate4 := to_number(nvl(i.interest_rate4,'0'));
817 g_court_orders(l_court_order_no).interest_calculation_base4 := to_number(nvl(i.interest_base4,'0'));
818 g_court_orders(l_court_order_no).interest_rate5 := to_number(nvl(i.interest_rate5,'0'));
819 g_court_orders(l_court_order_no).interest_calculation_base5 := to_number(nvl(i.interest_base5,'0'));
820 g_court_orders(l_court_order_no).court_order_origin := nvl(i.court_order_origin,'01');
821
822 ELSE
823 l_court_order_no := g_court_orders.count;
824
825 END IF;
826
827 g_court_orders(l_court_order_no).element_entry_id := i.element_entry_id;
828
829 IF upper(i.name) = 'ATTACHMENT SEQ NO' THEN
830 g_court_orders(l_court_order_no).attachment_sequence_no := i.screen_entry_value;
831 if g_debug then
832 hr_utility.trace('attachment_sequence_no'||g_court_orders(l_court_order_no).attachment_sequence_no);
833 end if;
834 -------------------------------------------------------------------
835 -- RAISE error in attachment sequence number has already been used.
836 -- This check will be run only once for a court order.
837 -- Bug 2856663 : procedure call modified to include new parameter p_assignment_id
838 -------------------------------------------------------------------
839 l_valid_attach_seq := attachment_seq_no_is_valid( p_assignment_id,
840 l_element_entry_id,
841 g_court_orders(l_court_order_no).attachment_sequence_no);
842 --
843 IF l_valid_attach_seq <> 'Y' THEN
844 p_curr_attach_seq_no := g_court_orders(l_court_order_no).attachment_sequence_no;
845 p_curr_case_number := g_court_orders(l_court_order_no).case_number;
846 RAISE e_duplicate_attach_exception;
847 END IF;
848
849 ELSIF upper(i.name) = 'PROCESSING TYPE' THEN
850 g_court_orders(l_court_order_no).processing_type := i.screen_entry_value;
851 --------------------------------------------------------------------------------------
852 --Bug : 4498363 Logic to check if a Provisional court order with start date before
853 --28-JUL-2005 has received an Actual Seizure and Collection after 28-JUL-2005.
854 --------------------------------------------------------------------------------------
855 IF g_court_orders(l_court_order_no).processing_type in ('AS', 'AA') AND g_court_orders(l_court_order_no).court_order_start_date >= g_change_effective_date THEN
856 l_check_condition := check_exception_case(g_court_orders(l_court_order_no).previous_case_number);
857 if l_check_condition = 'Y' then
858 l_count_court_orders := l_count_court_orders + 1;
859 end if;
860 --------------------------------------------------------------------------------------
861 -- Bug 4498363
862 --
863 --Logic to calculate the no. of court orders received after 28-JUL-2005
864 --and from Individual/Organization.
865 --------------------------------------------------------------------------------------
866 ELSIF g_court_orders(l_court_order_no).court_order_start_date >= g_change_effective_date THEN
867 -- Bug 4680413
868 --Added condition to check for court orders after the effective Date 28-APR-2006
869 IF g_court_orders(l_court_order_no).court_order_start_date >= g_change_eff_date_NTLA THEN
870 l_count_court_orders := l_count_court_orders + 1;
871 ELSE
872 IF nvl(g_court_orders(l_court_order_no).court_order_origin,'01') = '01' then
873 l_count_court_orders := l_count_court_orders + 1;
874 END IF;
875 END IF;
876 END IF;
877
878 ELSIF upper(i.name) = 'PRINCIPAL BASE' THEN
879 g_court_orders(l_court_order_no).principal_base := to_number(nvl(i.screen_entry_value,'0'));
880
881 ELSIF upper(i.name) = 'COURT FEE BASE' THEN
882 g_court_orders(l_court_order_no).court_fee_base := to_number(nvl(i.screen_entry_value,'0'));
883
884 ELSIF upper(i.name) = 'INTEREST BASE' THEN
885 g_court_orders(l_court_order_no).interest_base := to_number(nvl(i.screen_entry_value,'0'));
886
887 ELSIF upper(i.name) = 'CASE NUMBER' THEN
888 g_court_orders(l_court_order_no).case_number := i.screen_entry_value;
889
890 ELSIF upper(i.name) = 'OBLIGATION RELEASE' THEN
891 g_court_orders(l_court_order_no).obligation_release := nvl(i.screen_entry_value,'N');
892 --
893 -- Bug 4866417
894 -- An obligation release is considered to be processed if a run result with the value
895 -- 'Y' is found for the Obligation release for this Court Order
896 --
897 g_court_orders(l_court_order_no).obligation_release_processed := nvl(is_oblig_release_processed(l_element_entry_id,l_element_type_id),'N');
898 --
899 -- This flag c_redistribution_required will be used by the main procedure
900 -- to redistribute the Obligation Release amount only if at least one unprocessed
901 -- Obligation Released court order is found.
902 --
903 if g_debug then
904 hr_utility.trace('obligation_release_processed '||g_court_orders(l_court_order_no).obligation_release_processed);
905 hr_utility.trace('attachment_sequence_no'||g_court_orders(l_court_order_no).attachment_sequence_no);
906 end if;
907 --
908 IF g_court_orders(l_court_order_no).obligation_release_processed = 'N' THEN
909 c_redistribution_required := 'Y';
910 END IF;
911 --------------------------------------------------------------------------------------
912 -- Changes for Bug 4498363
913 -- Logic to check whether an Obligation release is received for Court Orders
914 -- with start date less than 28-JUL-2005.
915 --------------------------------------------------------------------------------------
916 IF g_court_orders(l_court_order_no).obligation_release = 'Y' THEN
917 --
918 IF (g_court_orders(l_court_order_no).court_order_start_date >= g_change_effective_date) THEN
919 -- Bug 4680413
920 -- Added logic to increment l_count_oblig_rel_after if the Court Order is from NTLA
921 -- after 28-APR-2006
922 IF (g_court_orders(l_court_order_no).court_order_start_date >= g_change_eff_date_NTLA) THEN
923 --
924 l_count_oblig_rel_after := l_count_oblig_rel_after + 1;
925 ELSE
926 IF nvl(g_court_orders(l_court_order_no).court_order_origin,'01') = '01' THEN
927 l_count_oblig_rel_after := l_count_oblig_rel_after + 1;
928 ELSE
929 l_count_oblig_rel_before := l_count_oblig_rel_before + 1;
930 END IF;
931 END IF;
932 ELSE
933 l_count_oblig_rel_before := l_count_oblig_rel_before + 1;
934 END IF;
935 if g_debug then
936 hr_utility.set_location('num of court orders which received obligation_release'||l_count_oblig_rel_before,310);
937 end if;
938 END IF;
939 --
940 -- Changes for Bug 2708036
941 --
942 ELSIF upper(i.name) = 'RECEPTION TIME' THEN
943 g_court_orders(l_court_order_no).reception_time := nvl(hr_chkfmt.changeformat(i.screen_entry_value, 'H_HHMM', null),'00:00');
944 g_court_orders(l_court_order_no).court_order_start_date := to_date( ( to_char( g_court_orders(l_court_order_no).court_order_start_date, 'YYYY/MM/DD')
945 ||' '||g_court_orders(l_court_order_no).reception_time), 'YYYY/MM/DD HH24:MI');
946 --
947 END IF;
948
949 ------------------------------------------------------------------------------------------
950 -- Load Interest only after attachment seq no has been populated as attachment seq no
951 -- is used while calling procedure processing_first_time (it sets the context for balance)
952 ------------------------------------------------------------------------------------------
953 -- Load interest calculation information only if interest base is not specified.
954 -- This check is placed for performance reasons.
955 -------------------------------------------------------------------------------
956 -- Interest needs to be leaded only once for each court order
957 -------------------------------------------------------------
958
959 IF g_court_orders(l_court_order_no).attachment_sequence_no IS NOT NULL AND
960 g_court_orders(l_court_order_no).interest_base = 0 AND
961 NOT l_interest_loaded
962 THEN
963
964 g_court_orders(l_court_order_no).interest_from_date1 := fnd_date.canonical_to_date(i.interest_from_date1);
965 g_court_orders(l_court_order_no).interest_from_date2 := fnd_date.canonical_to_date(i.interest_from_date2);
966 g_court_orders(l_court_order_no).interest_from_date3 := fnd_date.canonical_to_date(i.interest_from_date3);
967 g_court_orders(l_court_order_no).interest_from_date4 := fnd_date.canonical_to_date(i.interest_from_date4);
968 g_court_orders(l_court_order_no).interest_from_date5 := fnd_date.canonical_to_date(i.interest_from_date5);
969 -------------------------------------------------------------------------------
970 -- If interest_to_date is null, then populate payout_date-1 in interest_to_date
971 -------------------------------------------------------------------------------
972 g_court_orders(l_court_order_no).interest_to_date1 := nvl(fnd_date.canonical_to_date(i.interest_to_date1), l_payout_date-1);
973 g_court_orders(l_court_order_no).interest_to_date2 := nvl(fnd_date.canonical_to_date(i.interest_to_date2), l_payout_date-1);
974 g_court_orders(l_court_order_no).interest_to_date3 := nvl(fnd_date.canonical_to_date(i.interest_to_date3), l_payout_date-1);
975 g_court_orders(l_court_order_no).interest_to_date4 := nvl(fnd_date.canonical_to_date(i.interest_to_date4), l_payout_date-1);
976 g_court_orders(l_court_order_no).interest_to_date5 := nvl(fnd_date.canonical_to_date(i.interest_to_date5), l_payout_date-1);
977 --------------------------------------------------------------------------------------
978 -- Bug 2893245
979 -- Previous Payout Date is now computed only for the interest rate based court orders.
980 --------------------------------------------------------------------------------------
981 g_court_orders(l_court_order_no).previous_payout_date := get_previous_payout_date(g_court_orders(l_court_order_no).element_entry_id,
982 g_court_orders(l_court_order_no).attachment_sequence_no);
983
984 validate_interest_bands(l_court_order_no);
985
986 l_interest_loaded := TRUE;
987 if g_debug then
988 hr_utility.set_location('Attachment Sequence Number : '||g_court_orders(l_court_order_no).attachment_sequence_no||
989
990 ' Band 1 : '||
991 ' '|| to_char(g_court_orders(l_court_order_no).interest_calculation_base1)||
992 ' '|| to_char(g_court_orders(l_court_order_no).interest_rate1)||
993 ' '|| to_char(g_court_orders(l_court_order_no).interest_from_date1)||
994 ' '|| to_char(g_court_orders(l_court_order_no).interest_to_date1),11);
995
996
997 hr_utility.set_location('Attachment Sequence Number : '||g_court_orders(l_court_order_no).attachment_sequence_no||
998
999 ' Band 2 : '||
1000 ' '|| to_char(g_court_orders(l_court_order_no).interest_calculation_base2)||
1001 ' '|| to_char(g_court_orders(l_court_order_no).interest_rate2)||
1002 ' '|| to_char(g_court_orders(l_court_order_no).interest_from_date2)||
1003 ' '|| to_char(g_court_orders(l_court_order_no).interest_to_date2),21);
1004
1005 hr_utility.set_location('Attachment Sequence Number : '||g_court_orders(l_court_order_no).attachment_sequence_no||
1006 ' Band 3 : '||
1007 ' '|| to_char(g_court_orders(l_court_order_no).interest_calculation_base3)||
1008 ' '|| to_char(g_court_orders(l_court_order_no).interest_rate3)||
1009 ' '|| to_char(g_court_orders(l_court_order_no).interest_from_date3)||
1010 ' '|| to_char(g_court_orders(l_court_order_no).interest_to_date3),31);
1011
1012 hr_utility.set_location('Attachment Sequence Number : '||g_court_orders(l_court_order_no).attachment_sequence_no||
1013 ' Band 4 : '||
1014 ' '|| to_char(g_court_orders(l_court_order_no).interest_calculation_base4)||
1015 ' '|| to_char(g_court_orders(l_court_order_no).interest_rate4)||
1016 ' '|| to_char(g_court_orders(l_court_order_no).interest_from_date4)||
1017 ' '|| to_char(g_court_orders(l_court_order_no).interest_to_date4),41);
1018
1019 hr_utility.set_location('Attachment Sequence Number : '||g_court_orders(l_court_order_no).attachment_sequence_no||
1020 ' Band 5 : '||
1021 ' '|| to_char(g_court_orders(l_court_order_no).interest_calculation_base5)||
1022 ' '|| to_char(g_court_orders(l_court_order_no).interest_rate5)||
1023 ' '|| to_char(g_court_orders(l_court_order_no).interest_from_date5)||
1024 ' '|| to_char(g_court_orders(l_court_order_no).interest_to_date5),51);
1025 end if;
1026
1027 END IF; -- interest loaded
1028
1029 END LOOP;
1030 --------------------------------------------------------------------------------------
1031 -- Bug : 4498363
1032 -- Logic to check if an exceptional condition of court orders has been met
1033 -- and if whether in such a condition obligation release has been received by
1034 -- court orders with start date before 28-JUL-2005.
1035 --------------------------------------------------------------------------------------
1036 IF (l_count_court_orders = 0) OR (l_count_court_orders = g_court_orders.count) then
1037 g_excpn_court_order_flag :='N';
1038 ELSE
1039 IF ((l_count_oblig_rel_before >= 1) AND (l_count_oblig_rel_before = (g_court_orders.count-l_count_court_orders)))
1040 OR ((l_count_oblig_rel_after >= 1) AND (l_count_oblig_rel_after = l_count_court_orders)) then
1041 g_excpn_court_order_flag :='N';
1042 ELSE
1043 g_excpn_court_order_flag :='Y';
1044 END IF;
1045 END IF;
1046 if g_debug then
1047 hr_utility.trace('l_count_oblig_rel_before '||l_count_oblig_rel_before);
1048 hr_utility.trace('l_count_oblig_rel_after '||l_count_oblig_rel_after);
1049 end if;
1050 --
1051 EXCEPTION
1052 WHEN OTHERS THEN
1053 if g_debug then
1054 hr_utility.set_location('Error in loading court orders. Message : '||substr(sqlerrm,1,200), -30);
1055 end if;
1056 RAISE;
1057
1058 END load_court_orders;
1059
1060 ------------------------------------------------------------------------------------------------------------
1061 -- Function to calculate net earnings (BUG : 4498363)
1062 ------------------------------------------------------------------------------------------------------------
1063 FUNCTION calc_net_earnings(p_net_earnings NUMBER,
1064 p_wg_attach_earnings_mtd NUMBER,
1065 p_wg_deductions_mtd NUMBER,
1066 p_date_paid DATE)
1067 RETURN NUMBER
1068 IS
1069 l_net_earnings_mtd NUMBER := 0;
1070 l_deductable_earnings_mtd NUMBER := 0;
1071 l_new_rule_earnings NUMBER := 0;
1072 l_old_rule_earnings NUMBER := 0;
1073 l_deductable_amount NUMBER := 0;
1074 BEGIN
1075 if g_debug then
1076 hr_utility.trace('calc_net_earnings : p_net_earnings'||to_char(p_net_earnings) );
1077 hr_utility.trace('calc_net_earnings : p_date_paid'||to_char(p_date_paid) );
1078 hr_utility.trace('calc_net_earnings : l_count_court_orders'||to_char(l_count_court_orders) );
1079 hr_utility.trace('calc_net_earnings : p_wg_attach_earnings_mtd'||to_char(p_wg_attach_earnings_mtd) );
1080 hr_utility.trace('calc_net_earnings : p_wg_deductions_mtd'||to_char(p_wg_deductions_mtd) );
1081 end if;
1082 l_old_rule_earnings := p_net_earnings * g_max_attachable_earnings;
1083 --
1084 IF p_date_paid >= g_change_effective_date and l_count_court_orders > 0 then --After 28-JUL-2005
1085 --
1086 l_net_earnings_mtd := p_wg_attach_earnings_mtd;
1087 l_deductable_earnings_mtd := l_net_earnings_mtd * g_max_attachable_earnings;
1088 --
1089 IF l_deductable_earnings_mtd < g_min_undeduct_amt then
1090 l_new_rule_earnings := greatest(l_net_earnings_mtd - g_min_undeduct_amt, 0);
1091 ELSIF l_deductable_earnings_mtd >= g_base_undeduct_amt then
1092 l_new_rule_earnings := l_net_earnings_mtd - (greatest(g_base_undeduct_amt, (g_base_undeduct_amt + (l_deductable_earnings_mtd - g_base_undeduct_amt)/2)));
1093 ELSE
1094 l_new_rule_earnings := l_deductable_earnings_mtd;
1095 END IF;
1096 --
1097 IF g_excpn_court_order_flag = 'Y' THEN
1098 l_deductable_amount := greatest(l_new_rule_earnings, l_old_rule_earnings, 0);
1099 ELSE
1100 l_deductable_amount := greatest(l_new_rule_earnings, 0);
1101 END IF;
1102 --
1103 IF p_wg_attach_earnings_mtd > p_net_earnings then
1104 l_deductable_amount := greatest(l_deductable_amount - p_wg_deductions_mtd, 0);
1105 END IF;
1106 --
1107 if g_debug then
1108 hr_utility.trace('calc_net_earnings : l_deductable_amount'||to_char(l_deductable_amount) );
1109 end if;
1110 return l_deductable_amount;
1111 ELSE --Before 28-JUL-2005
1112 if g_debug then
1113 hr_utility.trace('calc_net_earnings : l_old_rule_earnings'||to_char(l_old_rule_earnings) );
1114 end if;
1115 return l_old_rule_earnings;
1116 END IF;
1117 END calc_net_earnings;
1118
1119 ------------------------------------------------------------------------------------------------------------
1120 -- Procedure to calculate real attachment total
1121 ------------------------------------------------------------------------------------------------------------
1122 -- This procedure is called from the main procedure.
1123 -- In normal processing, this procedure will be called only once.
1124 -- But in case when an All Attachment was in process and some amount is available in wg_attachable_earnings
1125 -- after fully paying All Attachment, in such cases the unused amount will be distributed among the court
1126 -- orders suspended because of the All Attachment.
1127 -- For such court orders this procedure is again called from after_all_attachment
1128 --
1129 -- Care must be taken in this procedure with flags c_all_attach_full_paid and c_waiting_for_all_attach which
1130 -- are used to signal such events.
1131 ------------------------------------------------------------------------------------------------------------
1132 PROCEDURE calc_real_attachment_total
1133 IS
1134 l_interest NUMBER := 0;
1135 l_interest_to_date DATE;
1136 l_no_of_actual_attachments NUMBER := 0;
1137
1138 BEGIN
1139
1140
1141 IF g_court_orders.count > 0 THEN
1142
1143 FOR i in 1..g_court_orders.last
1144 LOOP
1145 if g_debug then
1146 hr_utility.set_location('Attachment Sequence : '||g_court_orders(i).attachment_sequence_no,12);
1147 end if;
1148 --
1149 IF nvl(g_court_orders(i).stop_flag, 'N') <> 'Y' THEN
1150
1151 -----------------------------------------------------------------------------------------------
1152 -- Setting flags for Actual Attachment and All Attachment
1153 -----------------------------------------------------------------------------------------------
1154
1155 -- This check is placed to avoid setting the flag during after all attachment run
1156
1157 IF NOT c_all_attach_full_paid ='Y' THEN
1158
1159 -- Set flags for All attachment.
1160 -- Two types of All Attachment are possible. 1. All Attachment 2. Actual All Attachment
1161
1162 IF g_court_orders(i).processing_type IN ('A','AA') THEN
1163
1164 -- RAISE error if an All Attachment is already processing.
1165
1166 IF c_all_attachment = 'Y' THEN
1167 p_curr_attach_seq_no := g_court_orders(i).attachment_sequence_no;
1168 p_curr_case_number := g_court_orders(i).case_number;
1169 RAISE e_all_attachment_exception;
1170 END IF;
1171
1172 c_all_attachment := 'Y';
1173
1174 d_all_attach_date := g_court_orders(i).court_order_start_date;
1175
1176 END IF;
1177
1178 -- Set flags for actual attachment.
1179 -- Two types of actual Attachment are possible. 1. Actual Seizure and Collection 2. Actual All Attachment
1180
1181 IF g_court_orders(i).processing_type IN ('AS','AA') THEN
1182
1183 l_no_of_actual_attachments := g_actual_attach.count + 1;
1184
1185 c_actual_attachment := 'Y';
1186 g_actual_attach(l_no_of_actual_attachments).d_actual_attach_date := g_court_orders(i).court_order_start_date;
1187
1188 -- If previous case number is not specified then RAISE error
1189
1190 IF g_court_orders(i).previous_case_number IS NULL THEN
1191 p_curr_attach_seq_no := g_court_orders(i).attachment_sequence_no;
1192 p_curr_case_number := g_court_orders(i).case_number;
1193 RAISE e_prev_case_notfound_exception;
1194 END IF;
1195
1196 g_actual_attach(l_no_of_actual_attachments).c_actual_attach_prev_case := g_court_orders(i).previous_case_number;
1197
1198 END IF;
1199 END IF; -- c_all_attach_full_paid
1200
1201 -----------------------------------------------------------------------------------------------
1202 -- End of setting flags
1203 -----------------------------------------------------------------------------------------------
1204
1205 -- Now calculating Real Attachment Total
1206
1207 -----------------------------------------------------------------------------------------------
1208 -- Obligation Release has the highest priority for all court orders
1209 -- Do not calculate real attachment total for those court orders for which obligation release has come
1210 -- For all other court orders calculate real attachment total
1211 -----------------------------------------------------------------------------------------------
1212
1213 IF (g_court_orders(i).obligation_release = 'N') OR (g_court_orders(i).obligation_release IS NULL) THEN
1214
1215 --------------------------------------------------------------------------------------------
1216 -- For court orders for which are not Obligation Released, All Attachment has the highest priority.
1217 --
1218 -- If an All Attachment has come then court orders with court_order_start_date later than All Attachment
1219 -- will not be processed in this run.
1220 -- If there is any such court order, flag c_waiting_for_all_attach will be set.
1221 -- Such court orders are processed only after All Attachment is fully paid.
1222 --
1223 -- And if no All Attachment has come then, process all court orders.
1224 --------------------------------------------------------------------------------------------
1225
1226 IF c_all_attachment = 'N' OR
1227 (c_all_attachment = 'Y' AND g_court_orders(i).court_order_start_date <= d_all_attach_date) OR
1228 g_court_orders(i).processing_type IN ('A' , 'AA') OR
1229 c_all_attach_full_paid = 'Y'
1230 THEN
1231 ------------------------------------------------------------------
1232 -- A court order specifies either interest rate or interest base
1233 --
1234 -- Bug 2715287
1235 -- However if neither interest base nor interest rate is specified
1236 -- then court order will follow the interest base processing.
1237 -- Bug 2860586
1238 -- interest_rate is used to identify interest rate based court orders.
1239 ----------------------------------------------------------------------
1240
1241 IF (g_court_orders(i).interest_rate1 > 0 OR
1242 g_court_orders(i).interest_rate2 > 0 OR
1243 g_court_orders(i).interest_rate3 > 0 OR
1244 g_court_orders(i).interest_rate4 > 0 OR
1245 g_court_orders(i).interest_rate5 > 0 )
1246 THEN -- Interest Rate case
1247
1248 -- Attachment Total Base = Principal Base + Court Fee Base
1249
1250 g_court_orders(i).attachment_total_base := g_court_orders(i).principal_base +
1251 g_court_orders(i).court_fee_base ;
1252 ---------------------------------------------------------------------------------
1253 -- Calculating Interest this period
1254 ---------------------------------------------------------------------------------
1255
1256 IF g_court_orders(i).interest_from_date1 is not null AND
1257 g_court_orders(i).interest_calculation_base1 > 0
1258 THEN
1259 l_interest := l_interest + round(g_court_orders(i).interest_calculation_base1 * g_court_orders(i).interest_rate1 *
1260 ((g_court_orders(i).interest_to_date1 - g_court_orders(i).interest_from_date1 + 1)/365) / 100);
1261 END IF;
1262
1263 IF g_court_orders(i).interest_from_date2 is not null AND
1264 g_court_orders(i).interest_calculation_base2 > 0
1265 THEN
1266 l_interest := l_interest + round(g_court_orders(i).interest_calculation_base2 * g_court_orders(i).interest_rate2 *
1267 ((g_court_orders(i).interest_to_date2 - g_court_orders(i).interest_from_date2 + 1)/365) / 100);
1268 END IF;
1269
1270 IF g_court_orders(i).interest_from_date3 is not null AND
1271 g_court_orders(i).interest_calculation_base3 > 0
1272 THEN
1273 l_interest := l_interest + round(g_court_orders(i).interest_calculation_base3 * g_court_orders(i).interest_rate3 *
1274 ((g_court_orders(i).interest_to_date3 - g_court_orders(i).interest_from_date3 + 1)/365) / 100);
1275 END IF;
1276
1277 IF g_court_orders(i).interest_from_date4 is not null AND
1278 g_court_orders(i).interest_calculation_base4 > 0
1279 THEN
1280 l_interest := l_interest + round(g_court_orders(i).interest_calculation_base4 * g_court_orders(i).interest_rate4 *
1281 ((g_court_orders(i).interest_to_date4 - g_court_orders(i).interest_from_date4 + 1)/365) / 100);
1282 END IF;
1283
1284 IF g_court_orders(i).interest_from_date5 is not null AND
1285 g_court_orders(i).interest_calculation_base5 > 0
1286 THEN
1287 l_interest := l_interest + round(g_court_orders(i).interest_calculation_base5 * g_court_orders(i).interest_rate5 *
1288 ((g_court_orders(i).interest_to_date5 - g_court_orders(i).interest_from_date5 + 1)/365) / 100);
1289 END IF;
1290 g_court_orders(i).interest_amount := l_interest;
1291
1292 l_interest := 0;
1293 --------------------------------------------------------------------------
1294 -- End of interest calculation
1295 --------------------------------------------------------------------------
1296
1297 -- Real Attachment Total = Attachment Total Base + Total Prepaid Interest + Interest this period
1298
1299 g_court_orders(i).real_attach_total_by_creditor := g_court_orders(i).attachment_total_base + g_court_orders(i).interest_amount +
1300 nvl(pay_kr_wg_report_pkg.paid_interest(p_assignment_id , g_court_orders(i).element_entry_id , p_date_earned ),0) ;
1301
1302 ELSE -- Interest Base Case
1303 --------------------------------------------------------------------------
1304 -- Attachment Total Base = Principal Base + Court Fee Base + Interest Base
1305 -- Real Attachment Total = Attachment Total Base -- Bug 2713144
1306 --------------------------------------------------------------------------
1307
1308 g_court_orders(i).attachment_total_base := g_court_orders(i).principal_base +
1309 g_court_orders(i).court_fee_base +
1310 g_court_orders(i).interest_base ;
1311
1312 g_court_orders(i).real_attach_total_by_creditor := g_court_orders(i).attachment_total_base;
1313
1314 END IF;
1315
1316 END IF; -- All Attachment
1317
1318 END IF; -- Obligation Release
1319 if g_debug then
1320 hr_utility.set_location('Real Attachment Total By Creditor : '||to_CHAR(g_court_orders(i).real_attach_total_by_creditor),22);
1321 hr_utility.set_location('Interest Amount for this Creditor : '||to_CHAR(g_court_orders(i).interest_amount),32);
1322 hr_utility.set_location('Attachment Total Base : '||to_CHAR(g_court_orders(i).attachment_total_base),42);
1323 end if;
1324 END IF; -- Stop Flag
1325
1326 END LOOP;
1327
1328 END IF; -- Count
1329
1330 EXCEPTION
1331 WHEN OTHERS THEN
1332 if g_debug then
1333 hr_utility.set_location('Error in calculating Real Attachment Total. '||substr(sqlerrm,1,200), -40);
1334 end if;
1335 RAISE;
1336
1337 END calc_real_attachment_total;
1338
1339 ------------------------------------------------------------------------------------------------------------
1340 -- Procedure to calculate employee attachment total
1341 ------------------------------------------------------------------------------------------------------------
1342 -- This procedure is called from the main procedure.
1343 -- In normal processing, this procedure will be called only once.
1344 -- But in case when an All Attachment was in process and some amount is available in wg_attachable_earnings
1345 -- after fully paying All Attachment, in such cases the unused amount will be distributed among the court
1346 -- orders suspended because of the All Attachment.
1347 -- For such court orders this procedure is again called from after_all_attachment
1348 --
1349 -- Care must be taken in this procedure with flags c_all_attach_full_paid and c_waiting_for_all_attach which
1350 -- are used to signal such events.
1351 -------------------------------------------------------------------------------------------------------------
1352 PROCEDURE calc_emp_attachment_total
1353 IS
1354
1355 -------------------------------------------------------------------------
1356 -- Function to compare previous case numbers in case of Actual Attachment
1357 -------------------------------------------------------------------------
1358 FUNCTION case_number_matches(p_case_number IN VARCHAR2,
1359 p_court_order_type IN VARCHAR2
1360 ) RETURN BOOLEAN
1361 IS
1362 b_case_number_matches BOOLEAN := FALSE;
1363
1364 BEGIN
1365
1366 IF p_court_order_type = 'ACT' THEN
1367 IF g_actual_attach.count > 0 THEN
1368 FOR i in 1..g_actual_attach.last LOOP
1369 IF p_case_number = g_actual_attach(i).c_actual_attach_prev_case THEN
1370 g_actual_attach(i).c_actual_attach_case_found := 'Y';
1371 b_case_number_matches := TRUE;
1372 EXIT;
1373 END IF;
1374 END LOOP;
1375 END IF;
1376 END IF; -- Court Order Type = 'ACT'
1377
1378 RETURN b_case_number_matches;
1379
1380 END case_number_matches;
1381 --------------------------------------------------------------------
1382 BEGIN
1383 IF g_court_orders.count > 0 THEN
1384
1385 FOR i in 1..g_court_orders.last
1386 LOOP
1387 if g_debug then
1388 hr_utility.set_location('Attachment Sequence : '||g_court_orders(i).attachment_sequence_no,13);
1389 end if;
1390 --
1391 IF nvl(g_court_orders(i).stop_flag, 'N') <> 'Y' THEN
1392
1393 -- Calculate Employee Attachment total for attachments not affected by obligation release
1394
1395 IF (g_court_orders(i).obligation_release = 'N') OR (g_court_orders(i).obligation_release IS NULL) THEN
1396
1397 -----------------------------------------------------------------
1398 -- If no all attachment has come.
1399 -- Or this is the all attachment
1400 -- Or this attachment came before all attachment
1401 -- Or All Attachment is fully paid and still money is avilable
1402 -----------------------------------------------------------------
1403
1404 IF c_all_attachment = 'N' OR
1405 (c_all_attachment = 'Y' AND g_court_orders(i).court_order_start_date <= d_all_attach_date) OR
1406 g_court_orders(i).processing_type IN ('A' ,'AA') OR
1407 c_all_attach_full_paid ='Y'
1408 THEN
1409
1410 ---------------------------------------------------------------------------------
1411 -- For those attachments not affected by obligation release or All Attachment,
1412 -- Actual Attachment has the highest priority.
1413 -- If an Actual Attachment comes, then it specifies the previous case number.
1414 -- The court order with case number same as previous case number stops processing.
1415 --
1416 -- For all other court orders processing is as normal.
1417 ---------------------------------------------------------------------------------
1418
1419 IF (c_actual_attachment = 'Y') AND
1420 case_number_matches(g_court_orders(i).case_number, 'ACT')
1421 THEN
1422
1423 --
1424 g_court_orders(i).emp_attach_total_by_creditor := 0;
1425 g_court_orders(i).stop_flag := 'Y';
1426 g_court_orders(i).out_message := 'PAY_KR_WG_ACTUAL_ATTACH_MSG';
1427
1428 ELSE -- Court orders not affected by actual attachment.
1429
1430 ------------------------------------------------------------------------------
1431 -- Bug 2860586
1432 -- interest_rate is used to identify interest rate based court orders.
1433 ----------------------------------------------------------------------
1434
1435 IF (g_court_orders(i).interest_rate1 is not null OR
1436 g_court_orders(i).interest_rate2 is not null OR
1437 g_court_orders(i).interest_rate3 is not null OR
1438 g_court_orders(i).interest_rate4 is not null OR
1439 g_court_orders(i).interest_rate5 is not null )
1440 THEN -- Interest Rate case.
1441
1442 -- Employee attachment total = Real Attachment Total - Prepaid Debt
1443
1444 g_court_orders(i).emp_attach_total_by_creditor := g_court_orders(i).real_attach_total_by_creditor -
1445 nvl(pay_kr_wg_report_pkg.paid_amount(p_assignment_id , g_court_orders(i).element_entry_id, p_date_earned ),0);
1446
1447 -- Bug 2713144 Distribution Base = Real Attachment Total By Creditor
1448
1449 g_court_orders(i).distribution_base := g_court_orders(i).real_attach_total_by_creditor;
1450
1451 ELSE -- Interest Base case.
1452
1453 -- Employee attachment total = Real Attachment Total - Prepaid Debt -- Bug 2713144
1454
1455 g_court_orders(i).emp_attach_total_by_creditor := g_court_orders(i).real_attach_total_by_creditor -
1456 nvl(pay_kr_wg_report_pkg.paid_amount(p_assignment_id , g_court_orders(i).element_entry_id, p_date_earned ),0);
1457
1458 -- Bug 2713144 Distribution Base = Real Attachment Total
1459
1460 g_court_orders(i).distribution_base := g_court_orders(i).real_attach_total_by_creditor;
1461
1462 END IF;
1463
1464 END IF; -- Actual Attachment
1465 -----------------------------------------------------------------------------------------------
1466 -- Resetting PAY_KR_WG_ALL_ATTACH_MSG message because earnings are available in the current run
1467 -- to process attachments after All Attachment
1468 -----------------------------------------------------------------------------------------------
1469
1470 IF c_all_attach_full_paid = 'Y' THEN
1471 g_court_orders(i).out_message := 'XYZ';
1472 END IF;
1473
1474 ELSE -- All Attachment
1475 -- If all attachment is processing then do not process any new attachment
1476
1477 g_court_orders(i).real_attach_total_by_creditor := 0 ;
1478 g_court_orders(i).interest_amount := 0 ;
1479 g_court_orders(i).attachment_total_base := 0 ;
1480 g_court_orders(i).emp_attach_total_by_creditor := 0;
1481 c_waiting_for_all_attach := 'Y';
1482 g_court_orders(i).out_message := 'PAY_KR_WG_ALL_ATTACH_MSG';
1483
1484 END IF; -- All Attachment
1485
1486 -- Processing for the attachment for which obligation release has come.
1487
1488 ELSE -- Obligation Release
1489
1490 g_court_orders(i).real_attach_total_by_creditor := 0 ;
1491 g_court_orders(i).interest_amount := 0 ;
1492 g_court_orders(i).attachment_total_base := 0 ;
1493 c_obligation_release := 'Y';
1494 g_court_orders(i).emp_attach_total_by_creditor := 0;
1495 g_court_orders(i).out_message := 'PAY_KR_WG_OBLIG_RELEASE_MSG';
1496 g_court_orders(i).stop_flag := 'Y'; -- Bug : 4498363
1497
1498 -- Caclulate wage garnishment adjustment.
1499 -- Adjustment is applicable only in case of Provisional Attachment
1500
1501 IF g_court_orders(i).processing_type = 'P' THEN
1502
1503 -- Calculate wage garnishment adjustment amount for this obligation release
1504 -- And calculate total wage garnishment adjustment amount for all obligation releases
1505 -- Bug 4866417
1506 -- Redistrubution amount should be calcualted only if obligation release
1507 -- has not earlier been processed.
1508 --
1509 IF g_court_orders(i).obligation_release_processed = 'N' THEN -- 4866417
1510 g_court_orders(i).wg_adjustment_amount := nvl(pay_kr_wg_report_pkg.paid_amount(p_assignment_id, g_court_orders(i).element_entry_id, p_date_earned ),0);
1511 g_emp_total.wg_adjustment := g_emp_total.wg_adjustment + g_court_orders(i).wg_adjustment_amount;
1512 END IF;
1513 --
1514 if g_debug then
1515 hr_utility.set_location('WG Adjustment amount ' || to_char(g_emp_total.wg_adjustment),23);
1516 end if;
1517
1518 END IF;
1519
1520 END IF; -- Obligation Release
1521
1522 g_emp_total.emp_attach_total := g_emp_total.emp_attach_total + g_court_orders(i).emp_attach_total_by_creditor ;
1523 -- Bug 2713144
1524 g_emp_total.distribution_base := g_emp_total.distribution_base + g_court_orders(i).distribution_base;
1525
1526 END IF;
1527 if g_debug then
1528 hr_utility.set_location('Employee Attachment Total : '|| to_char(g_court_orders(i).emp_attach_total_by_creditor),33);
1529 end if;
1530 END LOOP;
1531 --
1532 if g_debug then
1533 hr_utility.set_location('Employee Attachment Total for all creditors: '|| to_char(g_emp_total.emp_attach_total),43);
1534 end if;
1535 END IF; -- g_court_orders.count > 0
1536
1537 EXCEPTION
1538 WHEN OTHERS THEN
1539 if g_debug then
1540 hr_utility.set_location('Error in calculating Employee Attachment Total. '||substr(sqlerrm,1,200), -50);
1541 end if;
1542 RAISE;
1543 END calc_emp_attachment_total;
1544
1545 -----------------------------------------------------------
1546 -- Procedure to distribute the current employee paid amount
1547 -----------------------------------------------------------
1548 PROCEDURE distribute_paid_amt(p_distribution_amount number, p_distribute_actual_flag char, p_adj_pay_flag char)
1549 IS
1550 -- Bug 2926020
1551
1552 l_unpaid_amount NUMBER := 0;
1553 l_leftover_amount NUMBER := 0;
1554
1555 l_temp NUMBER := 0;
1556 l_temp_total NUMBER := 0;
1557 l_correction NUMBER := 0;
1558
1559 BEGIN
1560 if g_debug then
1561 hr_utility.set_location('Inside distribute_paid_amt',14);
1562 end if;
1563 IF g_court_orders.count > 0 THEN
1564
1565 FOR i in 1..g_court_orders.last
1566 LOOP
1567 if g_debug then
1568 hr_utility.set_location('Attachment Sequence '||g_court_orders(i).attachment_sequence_no,24);
1569 end if;
1570
1571 IF nvl(g_court_orders(i).stop_flag, 'N') <> 'Y' THEN
1572
1573 -----------------------------------------------------------------------------------------------
1574 -- Distribute amount to all attachments not affected by obligation release and All Attachment
1575 -- Court Orders affected by Actual Attachment have already been stopped in emp_attachment_total
1576 -----------------------------------------------------------------------------------------------
1577
1578 IF (g_court_orders(i).obligation_release = 'N') OR (g_court_orders(i).obligation_release IS NULL) THEN
1579
1580 IF c_all_attachment = 'N' OR
1581 (c_all_attachment = 'Y' AND g_court_orders(i).court_order_start_date <= d_all_attach_date) OR
1582 g_court_orders(i).processing_type IN ('A' , 'AA') OR
1583 c_all_attach_full_paid = 'Y'
1584 THEN
1585
1586 -----------------------------------------------------------------------------------------------
1587 -- Distribute Logic Begins here
1588 -- Distribution will take place only if available amount is less than employee attachment total
1589 -- Otherwise actual amounts will be paid.
1590 -----------------------------------------------------------------------------------------------
1591
1592 IF g_court_orders(i).emp_attach_total_by_creditor > 0 THEN
1593
1594 IF p_distribute_actual_flag = 'D' THEN
1595
1596 -- This check is placed to avoid divide by zero error in case of wg adjustment distribution
1597
1598 IF g_emp_total.emp_attach_total > 0 THEN
1599 ----------------------------------------------------------------------------------
1600 -- Bug 2713144 : In case of redistribution distribution rate should be calculated
1601 -- on the basis of Employee Attachment Total
1602 -- Bug 2926020 : trunc is used instead of round in calculating distribution rate
1603 -- and curr_emp_paid_amt_by_creditor
1604 ----------------------------------------------------------------------------------
1605
1606 IF p_adj_pay_flag = 'A' THEN
1607 g_court_orders(i).distribution_rate := trunc(( g_court_orders(i).emp_attach_total_by_creditor / ( g_emp_total.emp_attach_total - l_correction_amount ) ), 15);
1608 ELSE
1609 g_court_orders(i).distribution_rate := trunc(( g_court_orders(i).distribution_base / ( g_emp_total.distribution_base - l_correction_amount ) ), 15);
1610 END IF;
1611
1612 -------------------------------------------------------------------------------
1613 -- If calculated distribution amount is more than the outstanding amount then
1614 -- Pay only the outstanding amount and redistribute the difference amount among
1615 -- other outstanding court orders.
1616 --------------------------------------------------------------------------------
1617 -- While redistributing the excess amount emp_attach_total for such court orders
1618 -- must be excluded from distribution rate calculations.
1619 -------------------------------------------------------------------------------
1620 -- l_temp = Excess Paid Amount for a court order.
1621 -- l_temp_total = Total Excess Paid Amount for all court orders.
1622 -- l_correction = Sum of employee attachment totals for all such court orders.
1623 -- Bug 3048774 = curr_emp_paid_amt_by_creditor should be deducted while calculating
1624 -- Outstanding amount in the recursive runs.
1625 -------------------------------------------------------------------------------
1626
1627 l_temp := trunc(g_court_orders(i).distribution_rate * p_distribution_amount) - (g_court_orders(i).emp_attach_total_by_creditor - g_court_orders(i).wg_adjusted_amount - g_court_orders(i).curr_emp_paid_amt_by_creditor);
1628
1629 IF l_temp > 0 THEN
1630 -- Pay Only the outstanding amount.
1631 g_court_orders(i).curr_emp_paid_amt_by_creditor := g_court_orders(i).emp_attach_total_by_creditor - g_court_orders(i).wg_adjusted_amount;
1632
1633 l_temp_total := l_temp_total + l_temp;
1634
1635 IF p_adj_pay_flag = 'A' THEN
1636 l_correction := l_correction + g_court_orders(i).emp_attach_total_by_creditor;
1637 ELSE
1638 l_correction := l_correction + g_court_orders(i).distribution_base;
1639 END IF;
1640
1641 ELSE
1642 ------------------------------------------------------------------------------------
1643 -- curr_emp_paid_amt_by_creditor = curr_emp_paid_amt_by_creditor + correction amount
1644 -- (correction amount resulted from excess distribution for some other court order)
1645 ------------------------------------------------------------------------------------
1646 g_court_orders(i).curr_emp_paid_amt_by_creditor := g_court_orders(i).curr_emp_paid_amt_by_creditor + trunc(g_court_orders(i).distribution_rate * p_distribution_amount);
1647
1648 END IF;
1649
1650 -- Bug 2926020
1651 l_leftover_amount := l_leftover_amount + trunc(g_court_orders(i).distribution_rate * p_distribution_amount);
1652
1653 ELSE
1654 g_court_orders(i).distribution_rate := 0;
1655 END IF;
1656 if g_debug then
1657 hr_utility.set_location('Distribution Rate : ' || to_char(g_court_orders(i).distribution_rate),34);
1658 hr_utility.set_location('p_distribution_amount : '|| to_char(p_distribution_amount),44);
1659 end if;
1660 ELSE
1661 g_court_orders(i).curr_emp_paid_amt_by_creditor := g_court_orders(i).emp_attach_total_by_creditor - g_court_orders(i).wg_adjusted_amount;
1662
1663 END IF;
1664
1665 END IF;
1666
1667 ----------------------
1668 -- End of Distribution
1669 ----------------------
1670 if g_debug then
1671 hr_utility.set_location('g_court_orders(i).curr_emp_paid_amt_by_creditor : '||to_char(g_court_orders(i).curr_emp_paid_amt_by_creditor),54);
1672 hr_utility.set_location('g_court_orders(i).emp_attach_total_by_creditor : '||to_char(g_court_orders(i).emp_attach_total_by_creditor),64);
1673 hr_utility.set_location('g_court_orders(i).wg_adjusted_amount : '||to_char(g_court_orders(i).wg_adjusted_amount),74);
1674 end if;
1675 ---------------------------------------------------------------
1676 -- Setting flags if full amount has been paid for a court order
1677 ---------------------------------------------------------------
1678
1679 IF g_court_orders(i).curr_emp_paid_amt_by_creditor = g_court_orders(i).emp_attach_total_by_creditor - g_court_orders(i).wg_adjusted_amount THEN
1680
1681 --------------------------------------------------------------------------------------------
1682 -- This check is placed to avoid setting stop_flag for attachments came after All Attachment
1683 -- Since such attachments are not prcessed, they should not be stopped.
1684 --------------------------------------------------------------------------------------------
1685
1686 IF g_court_orders(i).emp_attach_total_by_creditor > 0 THEN
1687 if g_debug then
1688 hr_utility.set_location('Setting stop flag...................',84);
1689 end if;
1690 g_court_orders(i).stop_flag := 'Y';
1691 END IF;
1692
1693 --------------------------------------------------------------------------------------------
1694 -- Check placed to set flag if All Attachment is fully paid
1695 -- If this flag is set, Attachments coming after All Attachment will be processed if
1696 -- earnings are available after paying All Attachment
1697 --------------------------------------------------------------------------------------------
1698
1699 IF g_court_orders(i).processing_type IN ('A' , 'AA') THEN
1700 c_all_attach_full_paid := 'Y';
1701 END IF;
1702
1703 --------------------------------------------------------------------------------------------
1704 -- This check is placed to avoid changing the message in case of provisional attachment
1705 -- overridden by actual attachment
1706 --------------------------------------------------------------------------------------------
1707
1708 IF g_court_orders(i).curr_emp_paid_amt_by_creditor > 0 THEN
1709 g_court_orders(i).out_message := 'PAY_KR_WG_FUL_DEBT_PAID_MSG';
1710 END IF;
1711
1712 END IF;
1713 -----------------------
1714 -- End of Setting flags
1715 -----------------------
1716
1717 -----------------------------------------------------------------------------
1718 -- Set the value of wage garnishment adjustment in case of obligation release
1719 -----------------------------------------------------------------------------
1720 IF p_adj_pay_flag = 'A' THEN
1721
1722 g_court_orders(i).wg_adjusted_amount := g_court_orders(i).wg_adjusted_amount + g_court_orders(i).curr_emp_paid_amt_by_creditor;
1723
1724 g_emp_total.wg_adjusted := g_emp_total.wg_adjusted + g_court_orders(i).wg_adjusted_amount;
1725
1726 ----------------------------------------------------------------------------------------------
1727 -- Either curr_emp_paid_amt_by_creditor or wg_adjusted_amount should hold value for a creditor
1728 ----------------------------------------------------------------------------------------------
1729 g_court_orders(i).curr_emp_paid_amt_by_creditor := 0;
1730
1731 END IF;
1732
1733 if g_debug then
1734 hr_utility.set_location('g_court_orders(i).curr_emp_paid_amt_by_creditor : '||to_char(g_court_orders(i).curr_emp_paid_amt_by_creditor),94);
1735 hr_utility.set_location('g_court_orders(i).emp_attach_total_by_creditor : '||to_char(g_court_orders(i).emp_attach_total_by_creditor),104);
1736 hr_utility.set_location('g_court_orders(i).wg_adjusted_amount : '||to_char(g_court_orders(i).wg_adjusted_amount),114);
1737 end if;
1738 END IF; -- All Attachment
1739
1740 ELSE -- Obligation Release
1741
1742 g_court_orders(i).curr_emp_paid_amt_by_creditor := 0;
1743
1744 --Bug : 4498363
1745 --Removed stop_flag and placed in calc_emp_attachment_total since it is required
1746 --prior to distribution
1747
1748 END IF; -- Obligation Release
1749 --------------------------------------------------------------------------
1750 -- Set the stop flag for separation pay run
1751 -- Bug 2657588 - Court Order should not be stopped for interim separation.
1752 -- Bug 4866417 - Below logic has been moved to the end of this procedure
1753 --------------------------------------------------------------------------
1754 END IF; -- stop flag
1755 END LOOP;
1756
1757 ----------------------------------------------------------------------------------------------------
1758 -- Correction Logic for cases when calculated distribution amount is greater than outstanding amount
1759 -- If the correction is required because of excess paid amounts then while doing corrections]
1760 -- leftover amount should also be added.
1761 ----------------------------------------------------------------------------------------------------
1762
1763 l_leftover_amount := p_distribution_amount - l_leftover_amount;
1764
1765 IF l_temp_total > 0 THEN
1766 -----------------------------------------------------------------------
1767 -- Bug 3048774
1768 -- l_correction_amount should hold cumulative value for all recursions.
1769 -----------------------------------------------------------------------
1770
1771 l_correction_amount := l_correction_amount + l_correction;
1772 if g_debug then
1773 hr_utility.trace('Going to call distribute_paid_amount recursively for the correction logic');
1774 end if;
1775 distribute_paid_amt(l_temp_total + l_leftover_amount, p_distribute_actual_flag, p_adj_pay_flag);
1776 l_leftover_amount := 0;
1777
1778 ELSE
1779 l_correction_amount := 0;
1780 END IF;
1781
1782 -------------------------
1783 -- Bug 2926020
1784 -- Left-over Amount Logic
1785 -------------------------
1786 if g_debug then
1787 hr_utility.trace('Going to adjust the leftover amount among outstanding court orders');
1788 end if;
1789 --
1790 IF p_distribute_actual_flag = 'D' and l_leftover_amount > 0 THEN
1791
1792 FOR i in 1..g_court_orders.count LOOP
1793
1794 EXIT WHEN l_leftover_amount = 0;
1795
1796 IF g_court_orders(i).emp_attach_total_by_creditor > 0 AND nvl(g_court_orders(i).stop_flag, 'N') <> 'Y' THEN
1797
1798 l_unpaid_amount := g_court_orders(i).emp_attach_total_by_creditor
1799 - g_court_orders(i).curr_emp_paid_amt_by_creditor
1800 - g_court_orders(i).wg_adjusted_amount;
1801
1802 IF l_unpaid_amount > l_leftover_amount THEN
1803
1804 IF p_adj_pay_flag = 'A' THEN
1805 g_court_orders(i).wg_adjusted_amount := g_court_orders(i).wg_adjusted_amount + l_leftover_amount;
1806 g_emp_total.wg_adjusted := g_emp_total.wg_adjusted + l_leftover_amount;
1807 ELSE
1808 g_court_orders(i).curr_emp_paid_amt_by_creditor := g_court_orders(i).curr_emp_paid_amt_by_creditor + l_leftover_amount;
1809
1810 END IF;
1811
1812 l_leftover_amount := 0;
1813
1814 ELSE
1815
1816 l_leftover_amount := l_leftover_amount - l_unpaid_amount;
1817
1818 IF p_adj_pay_flag = 'A' THEN
1819 g_court_orders(i).wg_adjusted_amount := g_court_orders(i).wg_adjusted_amount + l_unpaid_amount;
1820 g_emp_total.wg_adjusted := g_emp_total.wg_adjusted + l_unpaid_amount;
1821 ELSE
1822 g_court_orders(i).curr_emp_paid_amt_by_creditor := g_court_orders(i).curr_emp_paid_amt_by_creditor + l_unpaid_amount;
1823
1824 END IF;
1825
1826 g_court_orders(i).stop_flag := 'Y';
1827 g_court_orders(i).out_message := 'PAY_KR_WG_FUL_DEBT_PAID_MSG';
1828
1829 IF g_court_orders(i).processing_type IN ('A' , 'AA') THEN
1830 c_all_attach_full_paid := 'Y';
1831 END IF;
1832 END IF;
1833 END IF;
1834 END LOOP;
1835 END IF;
1836 --------------------------------
1837 -- End of Left-over amount logic
1838 --------------------------------
1839 ----------------------------------------------------------------------------------------------------
1840 -- Set the stop flag for separation pay run
1841 -- Bug 2657588 - Court Order should not be stopped for interim separation.
1842 -- Bug : 4866417
1843 -- This logic has been moved after the leftover amount logic because in case of separation pay stop
1844 -- flag was being set before the leftover logic, because of which leftover amount didn't get
1845 -- Adjusted in outstanding court orders in separation pay run.
1846 ----------------------------------------------------------------------------------------------------
1847 FOR i in 1..g_court_orders.count LOOP
1848 IF p_run_type = 'SEP' THEN
1849 IF p_adj_pay_flag = 'A' THEN
1850 IF p_distribute_actual_flag <> 'D' THEN
1851 g_court_orders(i).stop_flag := 'Y';
1852 END IF;
1853 ELSE
1854 g_court_orders(i).stop_flag := 'Y';
1855 END IF;
1856 END IF;
1857 END LOOP;
1858 END IF; -- count > 0
1859
1860 EXCEPTION
1861 WHEN OTHERS THEN
1862 if g_debug then
1863 hr_utility.set_location('Error in Distributing Paid Amount. '||substr(sqlerrm,1,200), -60);
1864 end if;
1865 RAISE;
1866
1867 END distribute_paid_amt;
1868
1869 --------------------------------------------------------------------------------------------
1870 -- Procedure to process attachments came after All Attachment if All Attachment has stopped.
1871 --------------------------------------------------------------------------------------------
1872 PROCEDURE after_all_attachment (p_earnings IN NUMBER, p_adj_pay IN CHAR)
1873 IS
1874
1875 BEGIN
1876 if g_debug then
1877 hr_utility.set_location('Earnings for after_all_attachment : '||to_char(p_earnings),15);
1878 end if;
1879 g_emp_total.emp_attach_total := 0;
1880
1881 -- Bug 2713144 Resetting Distribution Base
1882
1883 g_emp_total.distribution_base := 0;
1884 if g_debug then
1885 hr_utility.set_location('Calculating Real Attachment Total for After All Attachment processing',25);
1886 end if;
1887
1888 calc_real_attachment_total;
1889 if g_debug then
1890 hr_utility.set_location('Calculating employee attachment total for After All Attachment processing',35);
1891 end if;
1892 calc_emp_attachment_total;
1893
1894 g_emp_total.curr_emp_paid_amt := least(g_emp_total.emp_attach_total, p_earnings);
1895 if g_debug then
1896 hr_utility.set_location('Distributing paid amount for After All Attachment processing ',45);
1897
1898 end if;
1899 IF p_earnings >= g_emp_total.emp_attach_total THEN
1900 distribute_paid_amt(0, 'A', p_adj_pay);
1901 ELSE
1902 distribute_paid_amt(p_earnings, 'D', p_adj_pay);
1903 END IF;
1904 if g_debug then
1905 hr_utility.set_location('Employee Attachment total for After All Attachment processing : '||to_char(g_emp_total.emp_attach_total),55);
1906 end if;
1907 IF p_adj_pay = 'A' AND g_emp_total.emp_attach_total > p_earnings THEN
1908 if g_debug then
1909 hr_utility.set_location('p_net_earnings '||to_char(p_net_earnings),65);
1910 end if;
1911 g_emp_total.curr_emp_paid_amt := least(g_emp_total.emp_attach_total - p_earnings, p_net_earnings);
1912
1913 IF p_net_earnings >= g_emp_total.emp_attach_total - p_earnings THEN
1914 distribute_paid_amt(0, 'A', 'P');
1915 ELSE
1916 distribute_paid_amt(p_net_earnings, 'D', 'P');
1917 END IF;
1918
1919 END IF;
1920
1921 EXCEPTION
1922 WHEN OTHERS THEN
1923 if g_debug then
1924 hr_utility.set_location('Error in After All Attachment Processing. '||substr(sqlerrm,1,200), -70);
1925 end if;
1926 RAISE;
1927
1928 END;
1929
1930 ----------------------------------------
1931 -- Main proceudre body begins here
1932 ----------------------------------------
1933 BEGIN
1934 if g_debug then
1935 hr_utility.set_location('---------Entering pay_kr_wg_pkg.calc_wage_garnishment--------',10);
1936 end if;
1937 -- Bug 2715365 Fetch Payroll period start date and end date from per_time_periods table
1938
1939 OPEN csr_pay_period(p_assignment_action_id);
1940 FETCH csr_pay_period INTO l_period_start_date, l_period_end_date, l_payroll_action_id;
1941 CLOSE csr_pay_period;
1942
1943 -- Bug 2762097 Fetch payoutdate from legislative_parameters in pay_payroll_actions table
1944 -- Bug 3021794 payout date is now stored in canonical format in legislative parameter.
1945 -- Thus modified the function call to get_legislative_parameter
1946
1947 l_payout_date := fnd_date.canonical_to_date (pay_kr_ff_functions_pkg.get_legislative_parameter(
1948 P_PAYROLL_ACTION_ID => l_payroll_action_id,
1949 P_PARAMETER_NAME => 'PAYOUTDATE',
1950 P_DEFAULT_VALUE => fnd_date.date_to_canonical(l_period_end_date)));
1951
1952 -- Load court orders only if this is the first assignment or is not previously processed
1953
1954 IF (g_last_assignment_processed IS NULL) OR (p_assignment_id <> g_last_assignment_processed) THEN
1955
1956 -- Delete court orders of previous assignment
1957
1958 g_court_orders.delete;
1959
1960 -- Set the global variable to skip processing this assignment for next entry
1961
1962 g_last_assignment_processed := p_assignment_id;
1963
1964 -- Load all court orders for this assignment
1965 if g_debug then
1966 hr_utility.set_location('Loading court orders',30);
1967 end if;
1968 load_court_orders;
1969
1970 -- Calculate attachment total base and real attachment total for each creditor
1971 if g_debug then
1972 hr_utility.set_location('Calculating Real Attachment Total',40);
1973 end if;
1974 calc_real_attachment_total;
1975
1976 -- calculate employee attachment total for each creditor and total of employee attahcment total
1977 if g_debug then
1978 hr_utility.set_location('Calculating Employee Attachment Total',50);
1979 end if;
1980 calc_emp_attachment_total;
1981
1982 ------------------------------------------------------------------------------------
1983 -- Distribute Wage Garnishment Adjustment in case any obligation release has come
1984 -- Distribute_paid_amt procedure must be run at lease once because all output values
1985 -- are returned from this procedure
1986 ------------------------------------------------------------------------------------
1987
1988 l_emp_attach_total := g_emp_total.emp_attach_total;
1989 if g_debug then
1990 hr_utility.set_location('Adjusting WG Adjustment',60);
1991 end if;
1992 ------------------------------------------------------------------------------------
1993 -- Bug : 4498363
1994 -- Check for exception condition and distribution of earnings among court orders
1995 -- is done only if exceptional condition does not exist.
1996 ------------------------------------------------------------------------------------
1997 if p_run_type = 'SEP' or p_run_type = 'SEP_I' then -- Bug 4737220
1998 l_earnings := p_net_earnings * g_max_attachable_earnings;
1999 else
2000 l_earnings := calc_net_earnings(p_net_earnings, p_wg_attach_earnings_mtd, p_wg_deductions_mtd, p_date_paid);
2001 end if;
2002 --
2003 if g_debug then
2004 hr_utility.set_location('Net earnings '||l_earnings,350);
2005 hr_utility.trace('c_redistribution_required '||c_redistribution_required);
2006 end if;
2007 --
2008 IF c_obligation_release = 'Y' and c_redistribution_required = 'Y' THEN --4866417
2009 if g_debug then
2010 hr_utility.set_location('g_emp_total.emp_attach_total : '||to_char(g_emp_total.emp_attach_total),70);
2011 hr_utility.set_location('g_emp_total.wg_adjustment : '|| to_char(g_emp_total.wg_adjustment),80);
2012 end if;
2013
2014 g_emp_total.curr_emp_paid_amt := least(g_emp_total.emp_attach_total, g_emp_total.wg_adjustment);
2015
2016 IF g_emp_total.curr_emp_paid_amt > 0 THEN
2017
2018 IF g_emp_total.emp_attach_total <= g_emp_total.wg_adjustment THEN
2019
2020 distribute_paid_amt(0, 'A', 'A');
2021 -----------------------------------------------------------------------------------------------
2022 -- Since WG_Adjustment amount is more than employee attachment total, some amount
2023 -- will be available in WG_Adjustment after redistribution.
2024 --
2025 -- If this is the case then, we need to check if some court orders have been suspended
2026 -- because of a court order of processing priority All Attachment.
2027 --
2028 -- And if a court order is suspended because of All Attachment then it should be processed now.
2029 ------------------------------------------------------------------------------------------------
2030
2031 IF c_all_attachment = 'Y' AND c_all_attach_full_paid = 'Y' AND c_waiting_for_all_attach = 'Y' THEN
2032 after_all_attachment(g_emp_total.wg_adjustment - g_emp_total.curr_emp_paid_amt, 'A');
2033 END IF;
2034
2035 ELSE
2036 distribute_paid_amt(g_emp_total.wg_adjustment,'D', 'A');
2037 END IF;
2038 END IF;
2039 END IF;
2040 -----------------------------------------------------------------------------------------------------------
2041 -- Do this processing only if employee attachment total is greater than wage garnishment adjustment amount.
2042 -- This means if either WG_Adjustment amount is 0, Or
2043 -- WG_Adjustment is not sufficient to fully pay all court orders. Then net earnings will be used to pay the
2044 -- balance amount.
2045 -----------------------------------------------------------------------------------------------------------
2046 if g_debug then
2047 hr_utility.set_location('Distributing Amount',90);
2048 end if;
2049
2050 IF l_emp_attach_total > g_emp_total.wg_adjustment THEN
2051 ---------------------------------------------------------------------------------------------
2052 -- current employee paid amount = minimum of net earnings and employee attachment total
2053 --
2054 -- WG_Adjustment amount should be subtracted from employee attachment total when calculating
2055 -- Current Employee Paid Amount
2056 ---------------------------------------------------------------------------------------------
2057
2058 g_emp_total.curr_emp_paid_amt := least(l_emp_attach_total - g_emp_total.wg_adjusted, l_earnings);
2059
2060 ----------------------------------------------------------------------------------------------------------------
2061 -- Calculate the distribution percent of current paid amount for each creditor
2062 --
2063 -- If employee attachment total is less than attachable earnings then do not distribute the amount, pay actuals
2064 -- This is identfied with a flag distribute/actual, whose value will be 'D'-distribute and 'A'-actual
2065 ----------------------------------------------------------------------------------------------------------------
2066 if g_debug then
2067 hr_utility.set_location('Net Earnings : ' || to_char(l_earnings),100);
2068 end if;
2069 IF l_emp_attach_total - g_emp_total.wg_adjusted <= l_earnings THEN
2070
2071 distribute_paid_amt(0, 'A', 'P');
2072
2073 ----------------------------------------------------------------------------------------------------
2074 -- Since net earnings is higher than balance employee attachment total, amount is still avaialable.
2075 --
2076 -- If this is the case, we need to check if any court order was suspended because of All Attachment.
2077 -- Now, since All Attachment has been fully paid, balance amount in net earnings should be used to
2078 -- to pay the debt of all suspended court orders.
2079 ----------------------------------------------------------------------------------------------------
2080
2081 IF c_all_attachment = 'Y' AND c_all_attach_full_paid = 'Y' AND c_waiting_for_all_attach = 'Y' THEN
2082 after_all_attachment(l_earnings - g_emp_total.curr_emp_paid_amt, 'P');
2083 END IF;
2084
2085 ELSE
2086 distribute_paid_amt(g_emp_total.curr_emp_paid_amt, 'D', 'P');
2087 END IF;
2088
2089 END IF;
2090 --
2091 if g_debug then
2092 hr_utility.set_location('p_adjustment_amount := '||to_char(g_emp_total.wg_adjustment),110);
2093 hr_utility.set_location('p_unadjusted_amount := '||to_char(g_emp_total.wg_adjustment - g_emp_total.wg_adjusted),120);
2094 end if;
2095 p_adjustment_amount := g_emp_total.wg_adjustment;
2096 p_unadjusted_amount := g_emp_total.wg_adjustment - g_emp_total.wg_adjusted;
2097
2098 ELSE
2099 p_adjustment_amount := 0;
2100 p_unadjusted_amount := 0;
2101 END IF;
2102
2103 -- RETURN output parameters
2104
2105 IF g_court_orders.count > 0 THEN
2106
2107 FOR i in 1..g_court_orders.last
2108 LOOP
2109 IF p_attachment_seq_no = g_court_orders(i).attachment_sequence_no THEN
2110 if g_debug then
2111 hr_utility.set_location('p_attachment_amount := '||to_char(g_court_orders(i).curr_emp_paid_amt_by_creditor),130);
2112 hr_utility.set_location('p_adjusted_amount := '||to_char(g_court_orders(i).wg_adjusted_amount),140);
2113 hr_utility.set_location('p_interest_amount := '||to_char(g_court_orders(i).interest_amount),150);
2114 hr_utility.set_location('p_stop_flag := '||g_court_orders(i).stop_flag,160);
2115 hr_utility.set_location('p_real_attach_total := '||to_char(g_court_orders(i).real_attach_total_by_creditor),170);
2116 hr_utility.set_location('p_emp_attach_total := '||to_char(g_court_orders(i).emp_attach_total_by_creditor),180);
2117 hr_utility.set_location('p_attach_total_base := '||to_char(g_court_orders(i).attachment_total_base),190);
2118 hr_utility.set_location('p_message := '||g_court_orders(i).out_message,200);
2119 hr_utility.set_location('p_curr_attach_seq_no := '||g_court_orders(i).attachment_sequence_no,210);
2120 hr_utility.set_location('p_curr_case_number := '||g_court_orders(i).case_number,220);
2121 end if;
2122 p_curr_attach_seq_no := g_court_orders(i).attachment_sequence_no;
2123 p_curr_case_number := g_court_orders(i).case_number;
2124 p_attachment_amount := g_court_orders(i).curr_emp_paid_amt_by_creditor;
2125 p_adjusted_amount := g_court_orders(i).wg_adjusted_amount;
2126 p_interest_amount := g_court_orders(i).interest_amount;
2127 p_stop_flag := g_court_orders(i).stop_flag;
2128 p_real_attach_total := g_court_orders(i).real_attach_total_by_creditor;
2129 p_emp_attach_total := g_court_orders(i).emp_attach_total_by_creditor;
2130 p_attach_total_base := g_court_orders(i).attachment_total_base;
2131 p_message := g_court_orders(i).out_message;
2132
2133 -- Bug 2860586 IF condition added to return previous payout date if user has entered a
2134 -- lesser payout date than previous payout date.
2135
2136 IF l_payout_date < g_court_orders(i).previous_payout_date THEN
2137 p_payout_date := g_court_orders(i).previous_payout_date;
2138 ELSE
2139 p_payout_date := l_payout_date;
2140 END IF;
2141
2142 EXIT;
2143
2144 END IF;
2145 END LOOP;
2146
2147 END IF;
2148 --
2149 if g_debug then
2150 hr_utility.set_location('---------Leaving pay_kr_wg_pkg.calc_wage_garnishment--------',230);
2151 end if;
2152 RETURN 0;
2153
2154 EXCEPTION
2155
2156 WHEN e_duplicate_attach_exception THEN
2157 RETURN 1;
2158
2159 WHEN e_prev_case_notfound_exception THEN
2160 RETURN 2;
2161
2162 WHEN e_all_attachment_exception THEN
2163 RETURN 4;
2164
2165 WHEN OTHERS THEN
2166 if g_debug then
2167 hr_utility.set_location(substr(sqlerrm,1,200),-80);
2168 end if;
2169 RAISE;
2170
2171 END calc_wage_garnishment;
2172
2173 ----------------------------------------------------------------------------------
2174 -- Function <-------- ATTACHMENT_SEQ_NO_IS_VALID ------>
2175 ----------------------------------------------------------------------------------
2176 -- Bug 2856663 : check for assignment_id included in the where clause.
2177 ----------------------------------------------------------------------------------
2178 FUNCTION attachment_seq_no_is_valid (p_assignment_id IN NUMBER,
2179 p_element_entry_id IN NUMBER,
2180 p_attachment_seq_no IN VARCHAR2)
2181 RETURN VARCHAR2
2182 IS
2183 Cursor csr_attach IS
2184 SELECT 'Y'
2185 from pay_element_entry_values_f peev,
2186 pay_element_entries_f pee,
2187 pay_element_links_f pel,
2188 pay_input_values_f piv,
2189 pay_element_types_f pet,
2190 fnd_sessions ses
2191 where pet.element_name = 'Wage Garnishments'
2192 and pet.legislation_code = 'KR'
2193 and pet.business_group_id IS NULL
2194 and ses.session_id = userenv('sessionid')
2195 and pel.element_type_id = pet.element_type_id
2196 and piv.element_type_id = pel.element_type_id
2197 and piv.name = 'Attachment Seq No'
2198 and pee.element_link_id = pel.element_link_id
2199 and nvl(pee.entry_type, 'E') = 'E'
2200 and pee.assignment_id = p_assignment_id
2201 and peev.input_value_id = piv.input_value_id
2202 and peev.element_entry_id = pee.element_entry_id
2203 and peev.element_entry_id <> p_element_entry_id
2204 and ses.effective_date between pel.effective_start_date and pel.effective_end_date
2205 and ses.effective_date between piv.effective_start_date and piv.effective_end_date
2206 and ses.effective_date between pet.effective_start_date and pet.effective_end_date
2207 and pee.element_entry_id <> p_element_entry_id
2208 and peev.effective_start_date = pee.effective_start_date
2209 and peev.effective_end_date = pee.effective_end_date
2210 and peev.screen_entry_value = p_attachment_seq_no;
2211
2212 l_exists VARCHAR2(1) := 'N';
2213
2214 BEGIN
2215
2216 OPEN csr_attach;
2217 FETCH csr_attach INTO l_exists;
2218 CLOSE csr_attach;
2219
2220 IF l_exists = 'Y' THEN
2221 RETURN 'N';
2222 ELSE
2223 RETURN 'Y';
2224 END IF;
2225
2226 END attachment_seq_no_is_valid;
2227
2228 ---------------------------------
2229 ----Package pay_kr_wg_pkg--------
2230 ---------------------------------
2231 BEGIN
2232 -- Bug : 4498363
2233 OPEN csr_get_global_values('KR_WG_MIN_UNDEDUCTABLE_AMOUNT');
2234 FETCH csr_get_global_values into g_min_undeduct_amt;
2235 CLOSE csr_get_global_values;
2236
2237 OPEN csr_get_global_values('KR_WG_BASE_UNDEDUCTABLE_AMOUNT');
2238 FETCH csr_get_global_values into g_base_undeduct_amt;
2239 CLOSE csr_get_global_values;
2240
2241 OPEN csr_get_global_values('KR_WG_MAX_ATTACHABLE_EARNINGS');
2242 FETCH csr_get_global_values into g_max_attachable_earnings;
2243 CLOSE csr_get_global_values;
2244
2245 END pay_kr_wg_pkg;
[Home] [Help]