[Home] [Help]
PACKAGE BODY: APPS.PAY_US_ITERATIVE_VALUES
Source
1 PACKAGE BODY pay_us_iterative_values AS
2 /* $Header: pyusifun.pkb 120.0.12010000.2 2009/04/03 09:19:07 svannian ship $ */
3
4 PROCEDURE get_table_position (p_entry_id in number,
5 p_found out nocopy boolean,
6 p_pos_index out nocopy number,
10 p_count number;
7 p_called_from VARCHAR2 default null,
8 p_clear_asg VARCHAR2 default null
9 ) is
11 plsql_tab_entry_id number;
12 start_cnt number;
13 end_cnt number;
14
15 begin
16
17 hr_utility.trace('get_table_position');
18 hr_utility.trace('EEID is : ' || to_char(p_entry_id));
19 hr_utility.trace('p_called_from is : ' || p_called_from);
20
21 p_found := FALSE;
22 p_pos_index := 0;
23
24 if p_called_from = 'STOPPER' then
25 p_count := iter_stop.COUNT;
26 start_cnt := iter_stop.FIRST;
27 end_cnt := iter_stop.LAST;
28 elsif p_called_from = 'INS_FLAG' then
29 p_count := iter_ins.COUNT;
30 start_cnt := iter_ins.FIRST;
31 end_cnt := iter_ins.LAST;
32 elsif p_called_from = 'ITER_AMT' then
33 p_count := iter_amt.COUNT;
34 start_cnt := iter_amt.FIRST;
35 end_cnt := iter_amt.LAST;
36 elsif p_called_from = 'ITER_ELE' then
37 p_count := iter_ele_type.COUNT;
38 start_cnt := iter_ele_type.FIRST;
39 end_cnt := iter_ele_type.LAST;
40 else
41 p_count := iter_val.COUNT;
42 start_cnt := iter_val.FIRST;
43 end_cnt := iter_val.LAST;
44 end if;
45
46 if p_count = 0 then
47
48 p_found := FALSE;
49 p_pos_index := 0;
50 return;
51
52 else
53
54 hr_utility.trace('Value of COUNT is : ' || to_char(p_count));
55 hr_utility.trace('Value of FIRST is : ' || to_char(start_cnt));
56 hr_utility.trace('Value of LAST is : ' || to_char(end_cnt));
57 hr_utility.trace('Value of p_clear_asg is : ' || p_clear_asg);
58
59 for i in start_cnt .. end_cnt loop
60
61 if p_clear_asg is null then
62
63 if p_called_from = 'STOPPER' then
64 if iter_stop.EXISTS(i) then
65 hr_utility.trace('Iter Stop Value EXISTS');
66 plsql_tab_entry_id := iter_stop(i).entry_id;
67 else
68 hr_utility.trace('Iter Stop Value Does Not EXISTS');
69 plsql_tab_entry_id := 0;
70 end if;
71
72 elsif p_called_from = 'INS_FLAG' then
73 if iter_ins.EXISTS(i) then
74 hr_utility.trace('Iter Ins Value EXISTS');
75 plsql_tab_entry_id := iter_ins(i).entry_id;
76 else
77 hr_utility.trace('Iter Ins Value Does Not EXISTS');
78 plsql_tab_entry_id := 0;
79 end if;
80
81 elsif p_called_from = 'ITER_AMT' then
82 if iter_amt.EXISTS(i) then
83 hr_utility.trace('Iter Amt Value EXISTS');
84 plsql_tab_entry_id := iter_amt(i).entry_id;
85 else
86 hr_utility.trace('Iter Amt Value Does Not EXISTS');
87 plsql_tab_entry_id := 0;
88 end if;
89
90 else
91 if iter_val.EXISTS(i) then
92 hr_utility.trace('Iter Val Value EXISTS');
93 plsql_tab_entry_id := iter_val(i).entry_id;
94 else
95 hr_utility.trace('Iter Val Value Does Not EXISTS');
96 plsql_tab_entry_id := 0;
97 end if;
98
99 end if; /* p_called_from */
100
101 else /* p_clear_asg is null */
102
103 if p_called_from = 'STOPPER' then
104 if iter_stop.EXISTS(i) then
105 hr_utility.trace('Iter Stop Value EXISTS');
106 plsql_tab_entry_id := iter_stop(i).asg_id;
107 else
108 hr_utility.trace('Iter Stop Value Does Not EXISTS');
109 plsql_tab_entry_id := 0;
110 end if;
111
112 elsif p_called_from = 'INS_FLAG' then
113 if iter_ins.EXISTS(i) then
114 hr_utility.trace('Iter Ins Value EXISTS');
115 plsql_tab_entry_id := iter_ins(i).asg_id;
116 else
117 hr_utility.trace('Iter Ins Value Does Not EXISTS');
118 plsql_tab_entry_id := 0;
119 end if;
120
121 elsif p_called_from = 'ITER_AMT' then
122 if iter_amt.EXISTS(i) then
123 hr_utility.trace('Iter Amt Value EXISTS');
124 plsql_tab_entry_id := iter_amt(i).asg_id;
125 else
126 hr_utility.trace('Iter Amt Value Does Not EXISTS');
127 plsql_tab_entry_id := 0;
128 end if;
129
130 elsif p_called_from = 'ITER_ELE' then
131 if iter_ele_type.EXISTS(i) then
132 hr_utility.trace('Iter Ele Value EXISTS');
133 plsql_tab_entry_id := iter_ele_type(i).asg_id;
134 else
135 hr_utility.trace('Iter Ele Value Does Not EXISTS');
136 plsql_tab_entry_id := 0;
137 end if;
138
139 else
140 if iter_val.EXISTS(i) then
144 hr_utility.trace('Iter Val Value Does Not EXISTS');
141 hr_utility.trace('Iter Val Value EXISTS');
142 plsql_tab_entry_id := iter_val(i).asg_id;
143 else
145 plsql_tab_entry_id := 0;
146 end if;
147 end if; /* p_called_from */
148
149 end if; /* p_clear_asg is null */
150
151 hr_utility.trace('PLSQL EEID is : ' || to_char(plsql_tab_entry_id));
152 if ((p_entry_id = plsql_tab_entry_id) and (p_found = FALSE)) then
153
154 p_found := TRUE;
155 p_pos_index := i;
156 return;
157
158 end if;
159
160 end loop;
161
162 end if;
163
164 hr_utility.trace('Value of p_pos_index is : ' || to_char(p_pos_index));
165 return;
166
167 end; /* get_table_position */
168
169
170 FUNCTION get_stopper_flag ( p_entry_id in number)
171 RETURN VARCHAR2 IS
172
173 l_found_flag boolean;
174 l_pos_no number;
175
176 p_stopper_flag varchar2(5);
177
178 BEGIN /* get_stopper_flag */
179
180 hr_utility.trace('get_stopper_flag');
181
182 get_table_position(p_entry_id,l_found_flag, l_pos_no,'STOPPER');
183
184 if l_found_flag = FALSE then
185
186 p_stopper_flag := 'N';
187 else
188
189 p_stopper_flag := 'Y';
190 end if;
191
192 hr_utility.trace('Value of p_stopper_flag is : '|| p_stopper_flag);
193 return p_stopper_flag;
194
195 end; /* get_stopper_flag */
196
197 FUNCTION set_stopper_flag(p_entry_id number,
198 p_asg_id number,
199 p_stopper_flag VARCHAR2)
200 RETURN NUMBER IS
201
202 l_pos_no number;
203 l_found_flag boolean;
204
205 BEGIN /* set_stopper_flag */
206
207 hr_utility.trace('set_stopper_flag');
208
209 get_table_position(p_entry_id,l_found_flag, l_pos_no,'STOPPER');
210
211 hr_utility.trace('l_pos_no = '|| to_char(l_pos_no));
212
213 if l_found_flag = FALSE then
214
215 hr_utility.trace('Found Flag is FALSE ');
216 l_pos_no := iter_stop.COUNT + 1;
217 hr_utility.trace('increasing l_pos_no = '|| to_char(l_pos_no));
218
219 iter_stop(l_pos_no).entry_id := p_entry_id;
220 iter_stop(l_pos_no).asg_id := p_asg_id;
221 iter_stop(l_pos_no).stop_flag := 'Y';
222
223 end if;
224
225 return 1;
226
227 end; /* set_stopper_flag */
228
229
230 FUNCTION get_iterative_value(
231 p_entry_id in number,
232 iteration_number in number,
233 max_deduction out nocopy number,
234 min_deduction out nocopy number,
235 p_desired_amt out nocopy number,
236 p_calc_method out nocopy varchar2,
237 p_to_within out nocopy number,
238 p_clr_add_amt out nocopy number,
239 p_clr_rep_amt out nocopy number )
240 RETURN NUMBER IS
241
242 new_deduction number;
243 p_count number;
244
245 l_found_flag boolean;
246 l_pos_no number;
247
248 BEGIN /* get_iterative_value */
249
250 hr_utility.trace('get_iterative_value');
251
252 get_table_position(p_entry_id,l_found_flag, l_pos_no);
253
254 if l_found_flag = FALSE then
255
256 max_deduction := 0;
257 min_deduction := 0;
258 new_deduction := 0;
259 p_desired_amt := 0;
260 p_calc_method := 'Interpolation';
261 p_to_within := 1;
262 p_clr_add_amt := 0;
263 p_clr_rep_amt := 0;
264 else
265
266 max_deduction := iter_val(l_pos_no).max_dedn;
267 min_deduction := iter_val(l_pos_no).min_dedn;
268 new_deduction := iter_val(l_pos_no).new_dedn;
269 p_desired_amt := iter_val(l_pos_no).des_amt;
270 p_calc_method := iter_val(l_pos_no).calc_method;
271 p_to_within := iter_val(l_pos_no).to_within;
272 p_clr_add_amt := iter_val(l_pos_no).clr_add_amt;
273 p_clr_rep_amt := iter_val(l_pos_no).clr_rep_amt;
274
275 end if;
276
277 hr_utility.trace('Value of max is : ' || to_char(max_deduction));
278 hr_utility.trace('Value of min is : ' || to_char(min_deduction));
279 hr_utility.trace('Value of new is : ' || to_char(new_deduction));
280 hr_utility.trace('Value of Desired Amt is : ' || to_char(p_desired_amt));
281 hr_utility.trace('Value of Calc Method is : ' || p_calc_method);
282 hr_utility.trace('Value of To Within is : ' || to_char(p_to_within));
283 hr_utility.trace('Value of Clr Add Amt is : ' || to_char(p_clr_add_amt));
284 hr_utility.trace('Value of Clr Rep Amt is : ' || to_char(p_clr_rep_amt));
285
286 return new_deduction;
287
288 END; /* get_iterative_value */
289
290 FUNCTION set_iterative_value(
291 p_entry_id number,
292 p_asg_id number,
293 iteration_number number,
294 max_deduction number,
295 min_deduction number,
296 new_deduction number,
300 p_clr_add_amt number,
297 p_desired_amt number,
298 p_calc_method varchar2,
299 p_to_within number,
301 p_clr_rep_amt number )
302 RETURN NUMBER IS
303
304 l_pos_no number;
305 l_found_flag boolean;
306
307 BEGIN /* set_iterative_value */
308 hr_utility.trace('set_iterative_value');
309
310 get_table_position(p_entry_id,l_found_flag, l_pos_no);
311
312 hr_utility.trace('l_pos_no is '|| to_char(l_pos_no));
313
314 if l_found_flag = FALSE then
315 l_pos_no := iter_val.COUNT + 1;
316 iter_val(l_pos_no).entry_id := p_entry_id;
317 iter_val(l_pos_no).asg_id := p_asg_id;
318 end if;
319
320 iter_val(l_pos_no).iter_no := iteration_number;
321 iter_val(l_pos_no).max_dedn := max_deduction;
322 iter_val(l_pos_no).min_dedn := min_deduction;
323 iter_val(l_pos_no).new_dedn := new_deduction;
324 iter_val(l_pos_no).des_amt := p_desired_amt;
325 iter_val(l_pos_no).calc_method := p_calc_method;
326 iter_val(l_pos_no).to_within := p_to_within;
327 iter_val(l_pos_no).clr_add_amt := p_clr_add_amt;
328 iter_val(l_pos_no).clr_rep_amt := p_clr_rep_amt;
329
330
331 hr_utility.trace('Iter No is :' || to_char(iter_val(l_pos_no).iter_no));
332 hr_utility.trace('Max is :' || to_char(iter_val(l_pos_no).max_dedn));
333 hr_utility.trace('Min is :' || to_char(iter_val(l_pos_no).min_dedn));
334 hr_utility.trace('New is :' || to_char(iter_val(l_pos_no).new_dedn));
335 hr_utility.trace('Desired Amt is :' || to_char(iter_val(l_pos_no).des_amt));
336 hr_utility.trace('Calc Method is :' || iter_val(l_pos_no).calc_method);
337 hr_utility.trace('To Within is :' || to_char(iter_val(l_pos_no).to_within));
338 hr_utility.trace('Clr Add Amt is :' || to_char(iter_val(l_pos_no).clr_add_amt));
339 hr_utility.trace('Clr Rep Amt is :' || to_char(iter_val(l_pos_no).clr_rep_amt));
340
341 return new_deduction;
342
343 END; /* set_iterative_value */
344
345 FUNCTION clear_iterative_value(p_entry_id in number)
346 RETURN NUMBER IS
347
348 p_count number;
349 l_pos_no number;
350 l_found_flag boolean;
351
352 BEGIN /* clear_iterative_value */
353
354 hr_utility.trace('In clear_iterative_value ');
355
356 get_table_position(p_entry_id,l_found_flag, l_pos_no);
357
358 hr_utility.trace('Position = ' || to_char(l_pos_no));
359
360 if l_found_flag then
361 hr_utility.trace('Found flag is true');
362 iter_val.DELETE(l_pos_no) ;
363
364 end if;
365
366 /* clear the iter_amt plsql table also. This is used by 401,403 and 457
367 elements. */
368
369 get_table_position(p_entry_id,l_found_flag, l_pos_no,'ITER_AMT');
370 hr_utility.trace('Position = ' || to_char(l_pos_no));
371
372 if l_found_flag then
373 hr_utility.trace('Found flag is true');
374 iter_amt.DELETE(l_pos_no) ;
375
376 end if;
377
378 return 1;
379
380 END; /* clear_iterative_value */
381
382 FUNCTION clear_on_asg(p_asg_id in number,
383 p_aaid in number)
384 RETURN NUMBER IS
385
386 p_count number;
387 l_pos_no number;
388 l_found_flag boolean;
389
390 BEGIN /* clear_on_asg */
391
392 /* we need to check the assignment_action_id for a seperate check run.
393 if the AAID is different then we clear the tables for the assignment.
394 for seperate check runs the Assignment Id and element entry id would
395 be same but AAID would be different.
396
397 So if we get a different AAID we see if we have a record saved for that
398 assignment id, if yes then delete it else do nothing. */
399
400 hr_utility.trace('In clear_on_asg ');
401 hr_utility.trace('g_aaid = ' || to_char(g_aaid));
402 hr_utility.trace('p_aaid = ' || to_char(p_aaid));
403 hr_utility.trace('p_asg_id = ' || to_char(p_asg_id));
404
405 if ((g_aaid is null) OR
406 (g_aaid <> p_aaid )) then
407
408 iter_stop.DELETE ;
409 iter_amt.DELETE ;
410 iter_val.DELETE ;
411 iter_ele_type.DELETE ;
412 iter_ins.DELETE ;
413
414 g_aaid := p_aaid;
415
416 end if; /* g_aaid is null or <> p_aaid */
417
418 return 1;
419
420 END; /* clear_on_asg */
421
422
423 FUNCTION get_iter_count(p_entry_id in number )
424 RETURN NUMBER IS
425
426 iter_count number := 0;
427 p_flag varchar2(5);
428 p_count number ;
429
430 l_pos_no number;
431 l_found_flag boolean;
432
433 BEGIN /* get_iter_count */
434
435 hr_utility.trace('In get_iter_count');
436
437 get_table_position(p_entry_id,l_found_flag, l_pos_no);
438
439 hr_utility.trace('Value of l_pos_no is : '||to_char(l_pos_no));
440
441 if l_found_flag = FALSE then
442
443 iter_count := l_pos_no ;
444 else
445 iter_count := iter_val(l_pos_no).iter_no;
446 end if;
447
448 hr_utility.trace('Value of iter count is : '||to_char(iter_count));
449 return iter_count;
450
451 END; /* get_iter_count */
452
453 FUNCTION inc_iter_count(p_entry_id in number)
454 RETURN NUMBER IS
458 l_pos_no number;
455
456 p_count number;
457
459 l_found_flag boolean;
460
461 BEGIN /* inc_iter_count */
462
463 hr_utility.trace('In inc_iter_count');
464
465 get_table_position(p_entry_id,l_found_flag, l_pos_no);
466
467 hr_utility.trace('l_pos_no is : '|| to_char(l_pos_no));
468 if l_found_flag = FALSE then
469
470 raise NO_DATA_FOUND;
471 else
472
473 iter_val(l_pos_no).iter_no := iter_val(l_pos_no).iter_no + 1;
474 end if;
475
476 return iter_val(l_pos_no).iter_no;
477
478 END; /* inc_iter_count */
479
480 FUNCTION Iterative_Arrearage (
481 p_eletype_id IN NUMBER,
482 p_date_earned IN DATE,
483 p_assignment_id IN NUMBER ,
484 p_ele_entry_id IN NUMBER,
485 p_partial_flag IN VARCHAR2 ,
486 p_net_asg_run IN NUMBER,
487 p_arrears_itd IN NUMBER,
488 p_guaranteed_net IN NUMBER,
489 p_dedn_amt IN NUMBER,
490 p_amount IN NUMBER,
491 p_iter_count IN NUMBER,
492 p_to_arrears IN OUT nocopy NUMBER,
493 p_not_taken IN OUT nocopy NUMBER,
494 p_ins_flag IN VARCHAR2)
495 RETURN NUMBER IS
496
497 l_dedn_amt NUMBER(27,7); -- local var
498 v_arrears_flag VARCHAR2(1);
499
500
501 Begin
502
503 hr_utility.trace('p_iter_count= '|| to_char(p_iter_count));
504 hr_utility.trace('p_amount= '|| to_char(p_amount));
505 hr_utility.trace('p_dedn_amt= '|| to_char(p_dedn_amt));
506 hr_utility.trace('p_arrears_itd= '|| to_char(p_arrears_itd));
507 hr_utility.trace('p_ins_flag= '|| p_ins_flag);
508
509 /* call the arrearage function if this the first call from the
510 formula */
511
512 if p_iter_count <= 1 and p_ins_flag = 'N' then /* main */
513
514 hr_utility.trace('Calling Arrearage');
515 l_dedn_amt := hr_us_ff_udfs.Arrearage (
516 p_eletype_id => p_eletype_id,
517 p_date_earned => p_date_earned,
518 p_assignment_id => p_assignment_id , /* 6970340 */
519 p_ele_entry_id => p_ele_entry_id ,
520 p_partial_flag => p_partial_flag,
521 p_net_asg_run => p_net_asg_run,
522 p_arrears_itd => p_arrears_itd,
523 p_guaranteed_net => p_guaranteed_net,
524 p_dedn_amt => p_dedn_amt,
525 p_to_arrears => p_to_arrears,
526 p_not_taken => p_not_taken );
527
528 else /* main */
529
530 p_to_arrears := 0;
531 p_not_taken := 0;
532
533 -- Determine if Arrears = 'Y' for this dedn
534 -- Can do this by checking for "Clear Arrears" input value on base ele.
535 -- This input value is only created when Arrears is marked Yes on Deductions
536 -- screen.
537
538 begin
539
540 select 'Y' into v_arrears_flag
541 from pay_input_values_f ipv
542 where ipv.name = 'Clear Arrears'
543 and p_date_earned BETWEEN ipv.effective_start_date
544 AND ipv.effective_end_date
545 and ipv.element_type_id = p_eletype_id;
546
547 exception
548
549 WHEN NO_DATA_FOUND THEN
550 hr_utility.set_location('Arrearage is NOT ON for this ele.', 99);
551 v_arrears_flag := 'N';
552
553 WHEN TOO_MANY_ROWS THEN
554 hr_utility.set_location('Too many rows returned for Clear Arrears inpval.', 99);
555 v_arrears_flag := 'N';
556
557 end;
558
559 hr_utility.trace('value of arrear flag : '|| v_arrears_flag);
560 hr_utility.trace('Partial Flag= '|| p_partial_flag);
561
562 IF v_arrears_flag = 'N' THEN
563
564 if p_partial_flag = 'N' then
565
566 p_to_arrears := 0;
567 if p_dedn_amt <> p_amount then
568 p_not_taken := p_amount;
569 l_dedn_amt := 0;
570 else
571 p_not_taken := 0;
572 l_dedn_amt := p_amount;
573 end if;
574
575 else /* p_partial_flag = Y */
576
577 p_to_arrears := 0;
578 p_not_taken := p_amount - p_dedn_amt;
579 l_dedn_amt := p_dedn_amt;
580 end if;
581
582 else /* clear_arrear = Y */
583
584 if p_partial_flag = 'N' then
585
586 if p_dedn_amt < p_amount then
587 p_to_arrears := p_amount;
588 p_not_taken := p_amount;
589 l_dedn_amt := 0;
590 else
591 p_to_arrears := 0;
592 p_not_taken := 0;
593 l_dedn_amt := p_dedn_amt;
594 end if;
595
596 else /* p_partial_flag = Y */
597
598 p_to_arrears := p_amount - p_dedn_amt;
599 if p_dedn_amt > p_amount then
600 p_not_taken := 0;
601 else
605
602 p_not_taken := p_amount - p_dedn_amt;
603 end if;
604 l_dedn_amt := p_dedn_amt;
606 end if; /* p_partial_flag */
607 end if; /* clear_arrear = Y */
608
609 end if; /* main */
610
611 return l_dedn_amt;
612
613 END Iterative_Arrearage;
614
615 FUNCTION reduces_disposable_income (
616 p_assignment_id IN NUMBER,
617 p_date_earned IN DATE,
618 p_element_type_id IN NUMBER,
619 p_tax_type IN pay_balance_types.tax_type%TYPE)
620 RETURN VARCHAR2 IS
621
622 cursor c_garn_ele_exists is
623
624 select
625 pet.element_name
626 ,piv.name
627 ,peev.screen_entry_value
628 from pay_element_entries_f peef,
629 pay_element_entry_values_f peev,
630 pay_input_values_f piv,
631 pay_element_links_f pel,
632 pay_element_types_f pet,
633 pay_element_classifications pec
634 where peef.assignment_id = p_assignment_id
635 and peef.creator_type <> 'UT'
636 and p_date_earned between peef.effective_start_date
637 and peef.effective_end_date
638 and peef.element_entry_id = peev.element_entry_id
639 and p_date_earned between peev.effective_start_date
640 and peev.effective_end_date
641 and peev.input_value_id = piv.input_value_id
642 and piv.element_type_id = pet.element_type_id
643 and piv.name = 'Jurisdiction'
644 and p_date_earned between piv.effective_start_date
645 and piv.effective_end_date
646 and peef.element_link_id = pel.element_link_id
647 and p_date_earned between pel.effective_start_date
648 and pel.effective_end_date
649 and pel.element_type_id = pet.element_type_id
650 and p_date_earned between pet.effective_start_date
651 and pet.effective_end_date
652 and pec.classification_id = pet.classification_id
653 and pec.classification_name = 'Involuntary Deductions';
654
655 CURSOR csr_get_info is
656 select taxability_rules_date_id
657 from pay_taxability_rules_dates
658 where p_date_earned between valid_date_from and
659 valid_date_to
660 and legislation_code = 'US';
661
662
663 CURSOR csr_tax_rules_exists(
664 p_juri_code VARCHAR2,
665 p_tax_cat pay_taxability_rules.tax_category%TYPE,
666 p_classification_id pay_element_classifications.classification_id%TYPE,
667 p_tax_rules_date_id pay_taxability_rules.taxability_rules_date_id%TYPE
668 ) is
669 select 'Y'
670 from pay_taxability_rules
671 where jurisdiction_code = p_juri_code
672 and tax_type = p_tax_type
673 and tax_category = p_tax_cat
674 and classification_id = p_classification_id
675 and taxability_rules_date_id = p_tax_rules_date_id
676 and legislation_code = 'US'
677 and nvl(status,'VALID') <> 'D';
678
679
680 CURSOR csr_work_location is
681 select ps.state_code
682 from hr_locations hrl
683 , hr_soft_coding_keyflex hrsckf
684 , per_all_assignments_f paf
685 , pay_us_states ps
686 where p_date_earned BETWEEN paf.effective_start_date
687 and paf.effective_end_date
688 and paf.assignment_id = p_assignment_id
689 and paf.soft_coding_keyflex_id = hrsckf.soft_coding_keyflex_id
690 and nvl(hrsckf.segment18,paf.location_id) = hrl.location_id
691 and ps.state_abbrev = nvl(hrl.loc_information17,hrl.region_2);
692
693 CURSOR c_get_tax_cat is
694 select pet.element_information1, pet.classification_id
695 from pay_element_types_f pet
696 where pet.element_type_id = p_element_type_id
697 and p_date_earned between pet.effective_start_date
698 and pet.effective_end_date;
699
700 l_cur_ele_tax_cat pay_element_types_f.element_information1%TYPE;
701 l_fed VARCHAR2(5);
702 l_state VARCHAR2(5);
703
704 l_tax_rules_date_id pay_taxability_rules_dates.taxability_rules_date_id%TYPE;
705 l_classification_id pay_element_classifications.classification_id%TYPE;
706 l_element_name pay_element_types_f.element_name%TYPE;
707 l_ip_val_name pay_input_values_f.name%TYPE;
708 l_value pay_element_entry_values_f.screen_entry_value%TYPE;
709
710 l_other varchar2(5);
711
712 BEGIN
713
714 hr_utility.trace('In reduces_disposable_income ');
715 hr_utility.trace('Input assignment id is : '|| to_char(p_assignment_id));
716 hr_utility.trace('Input Date earned is : '|| p_date_earned);
717 hr_utility.trace('Input Tax Type is : '|| p_tax_type);
718
719 open csr_get_info;
720 fetch csr_get_info INTO l_tax_rules_date_id;
721 close csr_get_info;
722
723 hr_utility.trace('Tax Rule Date Id is : '|| to_char(l_tax_rules_date_id));
724
725 open c_get_tax_cat;
726 fetch c_get_tax_cat into l_cur_ele_tax_cat,l_classification_id;
727 close c_get_tax_cat;
728
729 l_other := 'N';
730
731 open c_garn_ele_exists;
732 loop
733 fetch c_garn_ele_exists into l_element_name,
737
734 l_ip_val_name,l_value;
735
736 exit when c_garn_ele_exists%NOTFOUND;
738 hr_utility.trace('Garnishment Element exists ');
739 hr_utility.trace('Classification Id : '||to_char(l_classification_id));
740 hr_utility.trace('Element Name is : '|| l_element_name);
741 hr_utility.trace('Tax Category is : '|| l_cur_ele_tax_cat);
742 hr_utility.trace('Input Value Name is : '|| l_ip_val_name);
743 hr_utility.trace('Value is : '|| l_value);
744
745 open csr_tax_rules_exists('00-000-0000',l_cur_ele_tax_cat,
746 l_classification_id,l_tax_rules_date_id);
747 fetch csr_tax_rules_exists into l_fed;
748 close csr_tax_rules_exists;
749
750 hr_utility.trace('Federal Taxability Rule is : '|| l_fed);
751
752 if l_fed = 'Y' then
753 -- Addded code check for DCIA as DCIA has Earning rules
754 -- defined only at Federal level.
755 if p_tax_type <> 'DCIA' then
756
757 if l_value is null then
758 open csr_work_location;
759 fetch csr_work_location into l_value;
760 close csr_work_location;
761 hr_utility.trace('Work Location is : '|| l_value);
762 end if; /* l_value is null */
763
764 l_value := l_value || '-000-0000';
765
766 open csr_tax_rules_exists(l_value,l_cur_ele_tax_cat,
767 l_classification_id,l_tax_rules_date_id);
768 fetch csr_tax_rules_exists into l_state;
769 if csr_tax_rules_exists%FOUND then
770 l_other := l_state;
771 else
772 l_other := 'N';
773 end if;
774 close csr_tax_rules_exists;
775
776 hr_utility.trace('State Taxability Rule is : '|| l_state);
777 else
778 l_other := 'Y';
779 end if; /* p_tax_type != 'DCIA' */
780
781 end if; /* l_fed = 'Y' */
782
783 end loop;
784 close c_garn_ele_exists;
785
786 return l_other;
787
788 END reduces_disposable_income;
789
790 FUNCTION partial_deduction_allowed (
791 p_element_type_id IN NUMBER,
792 p_date_earned IN DATE )
793 RETURN VARCHAR2 IS
794
795 cursor c_get_partial_info is
796
797 select pet.element_information2
798 from pay_element_types_f pet
799 where pet.element_type_id = p_element_type_id
800 and p_date_earned BETWEEN pet.effective_start_date
801 AND pet.effective_end_date;
802
803 l_partial_deduction pay_element_types_f.element_information2%TYPE;
804
805 Begin
806 hr_utility.trace('In partial_deduction_allowed function');
807 hr_utility.trace('Element Type Id is : '|| to_char(p_element_type_id));
808
809 open c_get_partial_info;
810 fetch c_get_partial_info into l_partial_deduction;
811 close c_get_partial_info;
812
813 hr_utility.trace('l_partial_deduction is : '|| l_partial_deduction);
814 if l_partial_deduction is null then
815 return 'N';
816 else
817 return l_partial_deduction;
818 end if;
819
820 END partial_deduction_allowed;
821
822 FUNCTION set_processing_element(p_asg_id in number,
823 p_ele_type in varchar2)
824 RETURN NUMBER IS
825
826 p_found boolean;
827 p_cnt number;
828
829 BEGIN /* set_processing_element */
830 hr_utility.trace('In set_processing_element');
831
832 if iter_ele_type.COUNT = 0 then
833 p_cnt := iter_ele_type.COUNT + 1;
834 iter_ele_type(p_cnt).ele_type := p_ele_type;
835 iter_ele_type(p_cnt).asg_id := p_asg_id;
836 return 2;
837 end if;
838
839 for i in iter_ele_type.FIRST .. iter_ele_type.LAST loop
840
841 if (p_ele_type = iter_ele_type(i).ele_type and (p_found = FALSE))then
842
843 p_found := TRUE;
844 return 1;
845 end if;
846
847 end loop;
848
849 if not p_found then
850
851 hr_utility.trace('Inserting ');
852 p_cnt := iter_ele_type.COUNT + 1;
853 iter_ele_type(p_cnt).ele_type := p_ele_type;
854 iter_ele_type(p_cnt).asg_id := p_asg_id;
855 end if;
856
857 return 1;
858
859 END; /* set_processing_element */
860
861 FUNCTION get_processing_element(p_ele_type IN varchar2)
862 RETURN VARCHAR2 IS
863
864 p_found boolean;
865 p_out_val varchar2(50);
866
867 BEGIN /* get_processing_element */
868
869 hr_utility.trace('In get_processing_element');
870
871 if iter_ele_type.COUNT = 0 then
872 p_out_val := 'Not Found';
873 return p_out_val;
874
875 end if;
876
877 for i in iter_ele_type.FIRST .. iter_ele_type.LAST loop
878
879 if (p_ele_type = iter_ele_type(i).ele_type and (p_found = FALSE))then
880
881 p_found := TRUE;
882 p_out_val := p_ele_type;
883 end if;
884
885 end loop;
886
887 if not p_found then
888
889 hr_utility.trace('Not Found');
890 p_out_val := 'Not Found';
891 end if;
892
893 return p_out_val;
894
895 END; /* get_processing_element */
896
897 FUNCTION set_inserted_flag (p_entry_id in number,
898 p_asg_id in number,
899 p_ins_flag in varchar2 )
900 RETURN VARCHAR2 IS
901
902 l_pos_no number;
903 l_found_flag boolean;
904 cnt number;
905
906 BEGIN /* set_inserted_flag */
907
908 hr_utility.trace('In set_inserted_flag');
909
910 get_table_position(p_entry_id,l_found_flag, l_pos_no,'INS_FLAG');
911
912 hr_utility.trace('l_pos_no is '|| to_char(l_pos_no));
913
914 if l_found_flag = FALSE then
915 cnt := iter_ins.COUNT + 1;
916 iter_ins(cnt).entry_id := p_entry_id;
917 iter_ins(cnt).asg_id := p_asg_id;
918 iter_ins(cnt).ins_flag := p_ins_flag;
919 end if;
920
921 return p_ins_flag;
922
923 END; /* set_inserted_flag */
924
925
926 FUNCTION get_inserted_flag (p_entry_id in number)
927 RETURN VARCHAR2 IS
928
929 l_found_flag boolean;
930 l_pos_no number;
931
932 p_ins_flag varchar2(5);
933
934 BEGIN /* get_inserted_flag */
935
936 hr_utility.trace('In get_inserted_flag');
937
938 get_table_position(p_entry_id,l_found_flag, l_pos_no,'INS_FLAG');
939
940 hr_utility.trace('l_pos_no is '|| to_char(l_pos_no));
941
942 if l_found_flag = FALSE then
943 p_ins_flag := 'N';
944 else
945 p_ins_flag := 'Y';
946 end if;
947
948 return p_ins_flag;
949
950 END; /* get_inserted_flag */
951
952
953 FUNCTION get_iter_amt (p_entry_id in number,
954 p_passed_amt in out nocopy number)
955 RETURN NUMBER IS
956
957 l_pos_no number;
958 l_found_flag boolean;
959
960 p_calc_amt number;
961
962 BEGIN /* get_iter_amt */
963
964 hr_utility.trace('In get_iter_amt');
965
966 get_table_position(p_entry_id,l_found_flag, l_pos_no,'ITER_AMT');
967
968 hr_utility.trace('Value of l_pos_no is : '||to_char(l_pos_no));
969
970 if l_found_flag = FALSE then
971
972 p_calc_amt := 0;
973 p_passed_amt := 0;
974 else
975 p_calc_amt := iter_amt(l_pos_no).calc_amt;
976 p_passed_amt := iter_amt(l_pos_no).passed_amt;
977 end if;
978
979 return p_calc_amt;
980
981 END; /* get_iter_amt */
982
983 FUNCTION set_iter_amt (p_entry_id in number,
984 p_asg_id in number,
985 p_calc_amt in number,
986 p_passed_amt in number)
987 RETURN NUMBER IS
988
989 l_pos_no number;
990 l_found_flag boolean;
991
992 BEGIN /* set_iter_amt */
993
994 hr_utility.trace('In set_iter_amt');
995 get_table_position(p_entry_id,l_found_flag, l_pos_no,'ITER_AMT');
996
997 hr_utility.trace('Value of l_pos_no is : '||to_char(l_pos_no));
998
999 if l_found_flag = FALSE then
1000
1001 l_pos_no := iter_amt.COUNT + 1;
1002 iter_amt(l_pos_no).entry_id := p_entry_id ;
1003 iter_amt(l_pos_no).asg_id := p_asg_id ;
1004
1005 end if;
1006
1007 iter_amt(l_pos_no).calc_amt := p_calc_amt ;
1008 iter_amt(l_pos_no).passed_amt := p_passed_amt;
1009
1010 return 1;
1011
1012 END; /* set_iter_amt */
1013
1014 FUNCTION clear_iter_ins
1015 RETURN NUMBER IS
1016
1017 l_count NUMBER;
1018
1019 BEGIN /* clear_iter_ins */
1020 hr_utility.trace('In clear_iter_ins ');
1021
1022 l_count := iter_ins.count;
1023 if l_count > 0 then
1024 iter_ins.DELETE;
1025 end if;
1026 return 1;
1027 END; /* clear_iter_ins */
1028
1029 END pay_us_iterative_values;
1030