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