[Home] [Help]
PACKAGE BODY: APPS.PAY_US_ITERATIVE_VALUES
Source
1 PACKAGE BODY pay_us_iterative_values AS
2 /* $Header: pyusifun.pkb 120.0 2005/05/29 09:35:53 appldev noship $ */
3
4 PROCEDURE get_table_position (p_entry_id in number,
5 p_found out nocopy boolean,
6 p_pos_index out nocopy number,
7 p_called_from VARCHAR2 default null,
8 p_clear_asg VARCHAR2 default null
9 ) is
10 p_count number;
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
141 hr_utility.trace('Iter Val Value EXISTS');
142 plsql_tab_entry_id := iter_val(i).asg_id;
143 else
144 hr_utility.trace('Iter Val Value Does Not EXISTS');
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,
297 p_desired_amt number,
298 p_calc_method varchar2,
299 p_to_within number,
300 p_clr_add_amt 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
419
416 end if; /* g_aaid is null or <> p_aaid */
417
418 return 1;
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
455
456 p_count number;
457
458 l_pos_no number;
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_partial_flag IN VARCHAR2 ,
484 p_net_asg_run IN NUMBER,
485 p_arrears_itd IN NUMBER,
486 p_guaranteed_net IN NUMBER,
487 p_dedn_amt IN NUMBER,
488 p_amount IN NUMBER,
489 p_iter_count IN NUMBER,
490 p_to_arrears IN OUT nocopy NUMBER,
491 p_not_taken IN OUT nocopy NUMBER,
492 p_ins_flag IN VARCHAR2)
493 RETURN NUMBER IS
494
495 l_dedn_amt NUMBER(27,7); -- local var
496 v_arrears_flag VARCHAR2(1);
497
498
499 Begin
500
501 hr_utility.trace('p_iter_count= '|| to_char(p_iter_count));
502 hr_utility.trace('p_amount= '|| to_char(p_amount));
503 hr_utility.trace('p_dedn_amt= '|| to_char(p_dedn_amt));
504 hr_utility.trace('p_arrears_itd= '|| to_char(p_arrears_itd));
505 hr_utility.trace('p_ins_flag= '|| p_ins_flag);
506
507 /* call the arrearage function if this the first call from the
508 formula */
509
510 if p_iter_count <= 1 and p_ins_flag = 'N' then /* main */
511
512 hr_utility.trace('Calling Arrearage');
513 l_dedn_amt := hr_us_ff_udfs.Arrearage (
514 p_eletype_id => p_eletype_id,
515 p_date_earned => p_date_earned,
516 p_partial_flag => p_partial_flag,
517 p_net_asg_run => p_net_asg_run,
518 p_arrears_itd => p_arrears_itd,
519 p_guaranteed_net => p_guaranteed_net,
520 p_dedn_amt => p_dedn_amt,
521 p_to_arrears => p_to_arrears,
522 p_not_taken => p_not_taken );
523
524 else /* main */
525
526 p_to_arrears := 0;
527 p_not_taken := 0;
528
529 -- Determine if Arrears = 'Y' for this dedn
530 -- Can do this by checking for "Clear Arrears" input value on base ele.
531 -- This input value is only created when Arrears is marked Yes on Deductions
532 -- screen.
533
534 begin
535
536 select 'Y' into v_arrears_flag
537 from pay_input_values_f ipv
538 where ipv.name = 'Clear Arrears'
539 and p_date_earned BETWEEN ipv.effective_start_date
540 AND ipv.effective_end_date
541 and ipv.element_type_id = p_eletype_id;
542
543 exception
544
545 WHEN NO_DATA_FOUND THEN
546 hr_utility.set_location('Arrearage is NOT ON for this ele.', 99);
547 v_arrears_flag := 'N';
548
549 WHEN TOO_MANY_ROWS THEN
550 hr_utility.set_location('Too many rows returned for Clear Arrears inpval.', 99);
551 v_arrears_flag := 'N';
552
553 end;
554
555 hr_utility.trace('value of arrear flag : '|| v_arrears_flag);
556 hr_utility.trace('Partial Flag= '|| p_partial_flag);
557
558 IF v_arrears_flag = 'N' THEN
559
560 if p_partial_flag = 'N' then
561
562 p_to_arrears := 0;
563 if p_dedn_amt <> p_amount then
567 p_not_taken := 0;
564 p_not_taken := p_amount;
565 l_dedn_amt := 0;
566 else
568 l_dedn_amt := p_amount;
569 end if;
570
571 else /* p_partial_flag = Y */
572
573 p_to_arrears := 0;
574 p_not_taken := p_amount - p_dedn_amt;
575 l_dedn_amt := p_dedn_amt;
576 end if;
577
578 else /* clear_arrear = Y */
579
580 if p_partial_flag = 'N' then
581
582 if p_dedn_amt < p_amount then
583 p_to_arrears := p_amount;
584 p_not_taken := p_amount;
585 l_dedn_amt := 0;
586 else
587 p_to_arrears := 0;
588 p_not_taken := 0;
589 l_dedn_amt := p_dedn_amt;
590 end if;
591
592 else /* p_partial_flag = Y */
593
594 p_to_arrears := p_amount - p_dedn_amt;
595 if p_dedn_amt > p_amount then
596 p_not_taken := 0;
597 else
598 p_not_taken := p_amount - p_dedn_amt;
599 end if;
600 l_dedn_amt := p_dedn_amt;
601
602 end if; /* p_partial_flag */
603 end if; /* clear_arrear = Y */
604
605 end if; /* main */
606
607 return l_dedn_amt;
608
609 END Iterative_Arrearage;
610
611 FUNCTION reduces_disposable_income (
612 p_assignment_id IN NUMBER,
613 p_date_earned IN DATE,
614 p_element_type_id IN NUMBER,
615 p_tax_type IN pay_balance_types.tax_type%TYPE)
616 RETURN VARCHAR2 IS
617
618 cursor c_garn_ele_exists is
619
620 select
621 pet.element_name
622 ,piv.name
623 ,peev.screen_entry_value
624 from pay_element_entries_f peef,
625 pay_element_entry_values_f peev,
626 pay_input_values_f piv,
627 pay_element_links_f pel,
628 pay_element_types_f pet,
629 pay_element_classifications pec
630 where peef.assignment_id = p_assignment_id
631 and peef.creator_type <> 'UT'
632 and p_date_earned between peef.effective_start_date
633 and peef.effective_end_date
634 and peef.element_entry_id = peev.element_entry_id
635 and p_date_earned between peev.effective_start_date
636 and peev.effective_end_date
637 and peev.input_value_id = piv.input_value_id
638 and piv.element_type_id = pet.element_type_id
639 and piv.name = 'Jurisdiction'
640 and p_date_earned between piv.effective_start_date
641 and piv.effective_end_date
642 and peef.element_link_id = pel.element_link_id
643 and p_date_earned between pel.effective_start_date
644 and pel.effective_end_date
645 and pel.element_type_id = pet.element_type_id
646 and p_date_earned between pet.effective_start_date
647 and pet.effective_end_date
648 and pec.classification_id = pet.classification_id
649 and pec.classification_name = 'Involuntary Deductions';
650
651 CURSOR csr_get_info is
652 select taxability_rules_date_id
653 from pay_taxability_rules_dates
654 where p_date_earned between valid_date_from and
655 valid_date_to
656 and legislation_code = 'US';
657
658
659 CURSOR csr_tax_rules_exists(
660 p_juri_code VARCHAR2,
661 p_tax_cat pay_taxability_rules.tax_category%TYPE,
662 p_classification_id pay_element_classifications.classification_id%TYPE,
663 p_tax_rules_date_id pay_taxability_rules.taxability_rules_date_id%TYPE
664 ) is
665 select 'Y'
666 from pay_taxability_rules
667 where jurisdiction_code = p_juri_code
668 and tax_type = p_tax_type
669 and tax_category = p_tax_cat
670 and classification_id = p_classification_id
671 and taxability_rules_date_id = p_tax_rules_date_id
672 and legislation_code = 'US'
673 and nvl(status,'VALID') <> 'D';
674
675
676 CURSOR csr_work_location is
677 select ps.state_code
678 from hr_locations hrl
679 , hr_soft_coding_keyflex hrsckf
680 , per_all_assignments_f paf
681 , pay_us_states ps
682 where p_date_earned BETWEEN paf.effective_start_date
683 and paf.effective_end_date
684 and paf.assignment_id = p_assignment_id
685 and paf.soft_coding_keyflex_id = hrsckf.soft_coding_keyflex_id
686 and nvl(hrsckf.segment18,paf.location_id) = hrl.location_id
687 and ps.state_abbrev = nvl(hrl.loc_information17,hrl.region_2);
688
689 CURSOR c_get_tax_cat is
690 select pet.element_information1, pet.classification_id
691 from pay_element_types_f pet
695
692 where pet.element_type_id = p_element_type_id
693 and p_date_earned between pet.effective_start_date
694 and pet.effective_end_date;
696 l_cur_ele_tax_cat pay_element_types_f.element_information1%TYPE;
697 l_fed VARCHAR2(5);
698 l_state VARCHAR2(5);
699
700 l_tax_rules_date_id pay_taxability_rules_dates.taxability_rules_date_id%TYPE;
701 l_classification_id pay_element_classifications.classification_id%TYPE;
702 l_element_name pay_element_types_f.element_name%TYPE;
703 l_ip_val_name pay_input_values_f.name%TYPE;
704 l_value pay_element_entry_values_f.screen_entry_value%TYPE;
705
706 l_other varchar2(5);
707
708 BEGIN
709
710 hr_utility.trace('In reduces_disposable_income ');
711 hr_utility.trace('Input assignment id is : '|| to_char(p_assignment_id));
712 hr_utility.trace('Input Date earned is : '|| p_date_earned);
713 hr_utility.trace('Input Tax Type is : '|| p_tax_type);
714
715 open csr_get_info;
716 fetch csr_get_info INTO l_tax_rules_date_id;
717 close csr_get_info;
718
719 hr_utility.trace('Tax Rule Date Id is : '|| to_char(l_tax_rules_date_id));
720
721 open c_get_tax_cat;
722 fetch c_get_tax_cat into l_cur_ele_tax_cat,l_classification_id;
723 close c_get_tax_cat;
724
725 l_other := 'N';
726
727 open c_garn_ele_exists;
728 loop
729 fetch c_garn_ele_exists into l_element_name,
730 l_ip_val_name,l_value;
731
732 exit when c_garn_ele_exists%NOTFOUND;
733
734 hr_utility.trace('Garnishment Element exists ');
735 hr_utility.trace('Classification Id : '||to_char(l_classification_id));
736 hr_utility.trace('Element Name is : '|| l_element_name);
737 hr_utility.trace('Tax Category is : '|| l_cur_ele_tax_cat);
738 hr_utility.trace('Input Value Name is : '|| l_ip_val_name);
739 hr_utility.trace('Value is : '|| l_value);
740
741 open csr_tax_rules_exists('00-000-0000',l_cur_ele_tax_cat,
742 l_classification_id,l_tax_rules_date_id);
743 fetch csr_tax_rules_exists into l_fed;
744 close csr_tax_rules_exists;
745
746 hr_utility.trace('Federal Taxability Rule is : '|| l_fed);
747
748 if l_fed = 'Y' then
749 -- Addded code check for DCIA as DCIA has Earning rules
750 -- defined only at Federal level.
751 if p_tax_type <> 'DCIA' then
752
753 if l_value is null then
754 open csr_work_location;
755 fetch csr_work_location into l_value;
756 close csr_work_location;
757 hr_utility.trace('Work Location is : '|| l_value);
758 end if; /* l_value is null */
759
760 l_value := l_value || '-000-0000';
761
762 open csr_tax_rules_exists(l_value,l_cur_ele_tax_cat,
763 l_classification_id,l_tax_rules_date_id);
764 fetch csr_tax_rules_exists into l_state;
765 if csr_tax_rules_exists%FOUND then
766 l_other := l_state;
767 else
768 l_other := 'N';
769 end if;
770 close csr_tax_rules_exists;
771
772 hr_utility.trace('State Taxability Rule is : '|| l_state);
773 else
774 l_other := 'Y';
775 end if; /* p_tax_type != 'DCIA' */
776
777 end if; /* l_fed = 'Y' */
778
779 end loop;
780 close c_garn_ele_exists;
781
782 return l_other;
783
784 END reduces_disposable_income;
785
786 FUNCTION partial_deduction_allowed (
787 p_element_type_id IN NUMBER,
788 p_date_earned IN DATE )
789 RETURN VARCHAR2 IS
790
791 cursor c_get_partial_info is
792
793 select pet.element_information2
794 from pay_element_types_f pet
795 where pet.element_type_id = p_element_type_id
796 and p_date_earned BETWEEN pet.effective_start_date
797 AND pet.effective_end_date;
798
799 l_partial_deduction pay_element_types_f.element_information2%TYPE;
800
801 Begin
802 hr_utility.trace('In partial_deduction_allowed function');
803 hr_utility.trace('Element Type Id is : '|| to_char(p_element_type_id));
804
805 open c_get_partial_info;
806 fetch c_get_partial_info into l_partial_deduction;
807 close c_get_partial_info;
808
809 hr_utility.trace('l_partial_deduction is : '|| l_partial_deduction);
810 if l_partial_deduction is null then
811 return 'N';
812 else
813 return l_partial_deduction;
814 end if;
815
816 END partial_deduction_allowed;
817
818 FUNCTION set_processing_element(p_asg_id in number,
819 p_ele_type in varchar2)
820 RETURN NUMBER IS
821
822 p_found boolean;
823 p_cnt number;
824
825 BEGIN /* set_processing_element */
829 p_cnt := iter_ele_type.COUNT + 1;
826 hr_utility.trace('In set_processing_element');
827
828 if iter_ele_type.COUNT = 0 then
830 iter_ele_type(p_cnt).ele_type := p_ele_type;
831 iter_ele_type(p_cnt).asg_id := p_asg_id;
832 return 2;
833 end if;
834
835 for i in iter_ele_type.FIRST .. iter_ele_type.LAST loop
836
837 if (p_ele_type = iter_ele_type(i).ele_type and (p_found = FALSE))then
838
842
839 p_found := TRUE;
840 return 1;
841 end if;
843 end loop;
844
845 if not p_found then
846
847 hr_utility.trace('Inserting ');
848 p_cnt := iter_ele_type.COUNT + 1;
849 iter_ele_type(p_cnt).ele_type := p_ele_type;
850 iter_ele_type(p_cnt).asg_id := p_asg_id;
851 end if;
852
853 return 1;
854
855 END; /* set_processing_element */
856
857 FUNCTION get_processing_element(p_ele_type IN varchar2)
858 RETURN VARCHAR2 IS
859
860 p_found boolean;
861 p_out_val varchar2(50);
862
863 BEGIN /* get_processing_element */
864
865 hr_utility.trace('In get_processing_element');
866
867 if iter_ele_type.COUNT = 0 then
868 p_out_val := 'Not Found';
869 return p_out_val;
870
871 end if;
872
873 for i in iter_ele_type.FIRST .. iter_ele_type.LAST loop
874
875 if (p_ele_type = iter_ele_type(i).ele_type and (p_found = FALSE))then
876
877 p_found := TRUE;
878 p_out_val := p_ele_type;
879 end if;
880
881 end loop;
882
883 if not p_found then
884
885 hr_utility.trace('Not Found');
886 p_out_val := 'Not Found';
887 end if;
888
889 return p_out_val;
890
891 END; /* get_processing_element */
892
893 FUNCTION set_inserted_flag (p_entry_id in number,
894 p_asg_id in number,
895 p_ins_flag in varchar2 )
896 RETURN VARCHAR2 IS
897
898 l_pos_no number;
899 l_found_flag boolean;
900 cnt number;
901
902 BEGIN /* set_inserted_flag */
903
904 hr_utility.trace('In set_inserted_flag');
905
906 get_table_position(p_entry_id,l_found_flag, l_pos_no,'INS_FLAG');
907
908 hr_utility.trace('l_pos_no is '|| to_char(l_pos_no));
909
910 if l_found_flag = FALSE then
911 cnt := iter_ins.COUNT + 1;
912 iter_ins(cnt).entry_id := p_entry_id;
913 iter_ins(cnt).asg_id := p_asg_id;
914 iter_ins(cnt).ins_flag := p_ins_flag;
915 end if;
916
917 return p_ins_flag;
918
919 END; /* set_inserted_flag */
920
921
922 FUNCTION get_inserted_flag (p_entry_id in number)
923 RETURN VARCHAR2 IS
924
925 l_found_flag boolean;
926 l_pos_no number;
927
928 p_ins_flag varchar2(5);
929
930 BEGIN /* get_inserted_flag */
931
932 hr_utility.trace('In get_inserted_flag');
933
934 get_table_position(p_entry_id,l_found_flag, l_pos_no,'INS_FLAG');
935
936 hr_utility.trace('l_pos_no is '|| to_char(l_pos_no));
937
938 if l_found_flag = FALSE then
939 p_ins_flag := 'N';
940 else
941 p_ins_flag := 'Y';
942 end if;
943
944 return p_ins_flag;
945
946 END; /* get_inserted_flag */
947
948
949 FUNCTION get_iter_amt (p_entry_id in number,
950 p_passed_amt in out nocopy number)
951 RETURN NUMBER IS
952
953 l_pos_no number;
954 l_found_flag boolean;
955
956 p_calc_amt number;
957
958 BEGIN /* get_iter_amt */
959
960 hr_utility.trace('In get_iter_amt');
961
962 get_table_position(p_entry_id,l_found_flag, l_pos_no,'ITER_AMT');
963
964 hr_utility.trace('Value of l_pos_no is : '||to_char(l_pos_no));
965
966 if l_found_flag = FALSE then
967
968 p_calc_amt := 0;
969 p_passed_amt := 0;
970 else
971 p_calc_amt := iter_amt(l_pos_no).calc_amt;
972 p_passed_amt := iter_amt(l_pos_no).passed_amt;
973 end if;
974
975 return p_calc_amt;
976
977 END; /* get_iter_amt */
978
979 FUNCTION set_iter_amt (p_entry_id in number,
980 p_asg_id in number,
981 p_calc_amt in number,
982 p_passed_amt in number)
983 RETURN NUMBER IS
984
985 l_pos_no number;
986 l_found_flag boolean;
987
988 BEGIN /* set_iter_amt */
989
990 hr_utility.trace('In set_iter_amt');
991 get_table_position(p_entry_id,l_found_flag, l_pos_no,'ITER_AMT');
992
993 hr_utility.trace('Value of l_pos_no is : '||to_char(l_pos_no));
994
995 if l_found_flag = FALSE then
996
997 l_pos_no := iter_amt.COUNT + 1;
998 iter_amt(l_pos_no).entry_id := p_entry_id ;
999 iter_amt(l_pos_no).asg_id := p_asg_id ;
1000
1001 end if;
1002
1003 iter_amt(l_pos_no).calc_amt := p_calc_amt ;
1004 iter_amt(l_pos_no).passed_amt := p_passed_amt;
1005
1006 return 1;
1007
1008 END; /* set_iter_amt */
1009
1010 FUNCTION clear_iter_ins
1011 RETURN NUMBER IS
1012
1013 l_count NUMBER;
1014
1015 BEGIN /* clear_iter_ins */
1016 hr_utility.trace('In clear_iter_ins ');
1017
1018 l_count := iter_ins.count;
1019 if l_count > 0 then
1020 iter_ins.DELETE;
1021 end if;
1022 return 1;
1023 END; /* clear_iter_ins */
1024
1025 END pay_us_iterative_values;
1026