[Home] [Help]
PACKAGE BODY: APPS.PAY_US_TAX_BALANCE_PERF
Source
1 package body pay_us_tax_balance_perf as
2 /* $Header: pyustxpl.pkb 120.4.12020000.2 2012/11/01 17:25:14 emunisek ship $ */
3 --
4 -- Copyright (c) Oracle Corporation 1991, 1992, 1993. All rights reserved.
5 --
6 /*
7 NAME
8 pyustxpl.pkb
9 --
10 DESCRIPTION
11 API to get US tax balance figures performance version.
12 --
13 MODIFIED (DD-MON-YYYY)
14 N Bristow 21-MAY-1996 Changed package name to comply with standards.
15 N Bristow 30-APR-1996 Created from a copy of pay_us_tax_bals_pkg,
16 altered package for chequewriter performance
17 reasons.
18 19-mar-98 McVeagh Change create or replace 'as' not 'is'
19 08-apr-99 djoshi Verfied and converted for Canonical
20 Complience of Date
21 14-Sep-1999 skutteti 115.4 Pre-tax enhancements
22 15-SEP-2000 skutteti 115.5 Currently there is no balance for FIT gross,
23 instead 'Gross Earnings' is used. Changed code
24 to subtract Alien earnings from FIT Gross.
25 23-NOV-2000 skutteti 115.6 Pre tax for Alien expat earnings has to be
26 reported in 1042s. Added code to subtract the
27 Alien portion of Pre-tax for SIT/FIT purposes.
28 15-AUG-2000 tmehra 115.7 Reverted the above changes for SIT Redns as
29 it is now directly being reduced by feeds.
30 04-DEC-2008 tclewis 115.8 Added validaton for SUI1 EE AND SDI1 EE.
31 03-JUN-2010 emunisek 115.9 Modified US_TAX_BALANCE function to change the way Federal Tax
32 Balances are fetched.Replaced the derived approach with estimated
33 values from Earnings with Run Results.The new changes are dependant
34 on a profile value set at site level.If the profile value is not
35 set or set as No, the balances will be fetched as it was before.
36 Only when Profile value is made Yes, the new changes will be effective.
37 03-JUN-2010 emunisek 115.10 Added dbdrv hint to the file to make it GSCC compliant
38 24-NOV-2011 nkjaladi 115.11 Bug #11926304 Modified US_TAX_BALANCE function
39 to support the value fetching for PSD taxes.
40 01-NOV-2012 emunisek 115.12 Bug#14385437 Added changes to check the value set for Profile
41 Option 'PAY_US_DIRECT_BALANCE_START_YEAR' to use
42 the Direct US Federal Balances approach.
43
44 */
45 -- Global declarations
46 type char_array is table of varchar(81) index by binary_integer;
47 type num_array is table of number(16) index by binary_integer;
48 --
49 g_defbal_tbl_id num_array;
50 g_defbal_tbl_name char_array;
51 g_nxt_free_defbal number;
52 -------------------------------------------------------------------------------
53 --
54 -- Quick procedure to raise an error
55 --
56 -------------------------------------------------------------------------------
57 PROCEDURE local_error(p_procedure varchar2,
58 p_step number) IS
59 BEGIN
60 --
61 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
62 hr_utility.set_message_token('PROCEDURE',
63 'pay_us_tax_balance_perf.'||p_procedure);
64 hr_utility.set_message_token('STEP',p_step);
65 hr_utility.raise_error;
66 --
67 END local_error;
68 -------------------------------------------------------------------------------
69 --
70 -- Wrapper around the core bal user exit
71 --
72 -------------------------------------------------------------------------------
73 FUNCTION call_balance_user_exit
74 (p_balance_name varchar2,
75 p_dimension_suffix varchar2,
76 p_assignment_action_id number,
77 p_business_group_id number)
78 RETURN number IS
79 --
80 l_defined_balance_id number;
81 l_balance_type_id number;
82 l_dimension_id number;
83 l_defbal_name char(81);
84 l_count number;
85 l_found boolean;
86 --
87 BEGIN
88 --
89 -- Search for the defined balance in the Cache.
90 --
91 l_defbal_name := p_balance_name||p_dimension_suffix||p_business_group_id;
92 l_count := 1;
93 l_found := FALSE;
94 while (l_count < g_nxt_free_defbal and l_found = FALSE) loop
95 if (l_defbal_name = g_defbal_tbl_name(l_count)) then
96 l_defined_balance_id := g_defbal_tbl_id(l_count);
97 l_found := TRUE;
98 end if;
99 l_count := l_count + 1;
100 end loop;
101 --
102 --
103 -- If the balance is not in the Cache get it from the database.
104 --
105 if (l_found = FALSE) then
106 BEGIN
107 --
108 hr_utility.trace('Looking for def_bal: ' || p_balance_name ||
109 ' : ' || p_dimension_suffix);
110 --
111 SELECT creator_id
112 INTO l_defined_balance_id
113 FROM ff_user_entities
114 WHERE user_entity_name like
115 translate(p_balance_name||'_'||p_dimension_suffix,' ','_');
116 --
117 --
118 -- Place the defined balance in cache.
119 --
120 g_defbal_tbl_name(g_nxt_free_defbal) := l_defbal_name;
121 g_defbal_tbl_id(g_nxt_free_defbal) := l_defined_balance_id;
122 g_nxt_free_defbal := g_nxt_free_defbal + 1;
123 hr_utility.trace('Calling core balance user exit');
124 --
125 EXCEPTION WHEN no_data_found THEN
126 hr_utility.trace('Error: Failure to find defined balance');
127 local_error('call_balance_user_exit',1);
128 --
129 END;
130 --
131 end if;
132 --
133 return pay_balance_pkg.get_value (l_defined_balance_id,
134 p_assignment_action_id);
135 --
136 END call_balance_user_exit;
137 -------------------------------------------------------------------------------
138 --
139 --
140 --
141 --
142 -------------------------------------------------------------------------------
143 FUNCTION us_tax_balance (p_tax_balance_category in varchar2,
144 p_tax_type in varchar2,
145 p_ee_or_er in varchar2,
146 p_time_type in varchar2,
147 p_asg_type in varchar2,
148 p_gre_id_context in number,
149 p_jd_context in varchar2 DEFAULT NULL,
150 p_assignment_action_id in number DEFAULT NULL,
151 p_assignment_id in number DEFAULT NULL,
152 p_virtual_date in date DEFAULT NULL,
153 p_business_group_id in number)
154 RETURN number IS
155
156 CURSOR get_year (p_assign_action_id NUMBER) IS
157 SELECT TO_CHAR(effective_date,'YYYY')
158 FROM pay_assignment_actions paa,pay_payroll_actions ppa
159 WHERE ppa.payroll_action_id = paa.payroll_action_id
160 AND paa.assignment_action_id = p_assign_action_id;
161
162 --
163 l_return_value number;
164 l_test number;
165 l_tax_balance_category varchar2(30);
166 l_tax_type varchar2(15);
167 l_ee_or_er varchar2(5);
168 l_dimension_string varchar2(80);
169 l_jd_dimension_string varchar2(80);
170 l_assignment_id number;
171 l_assignment_action_id number;
172 l_asg_exists number;
173 l_max_date date;
174 l_bal_start_date date;
175 l_virtual_date date;
176 l_valid number;
177 l_non_w2_cat varchar2(60);
178 l_direct_fed_bal_call varchar2(2) := 'N'; /*Added for Bug#6696348*/
179 /* Added for Bug#14385437 */
180 l_year varchar2(4);
181 l_direct_bal_year varchar2(4);
182 /* End of changes for Bug#14385437 */
183 l_tax_tag varchar2(20);/*Added for Bug#6696348*/
184 --
185 BEGIN
186 /*Changes for Bug#6696348*/
187 --
188 --Check if Direct Balances needs to be used for Federal Balances
189 --
190 l_direct_fed_bal_call := nvl(fnd_profile.value('PAY_DIRECT_US_FEDERAL_BALANCES'),'N');
191
192 /* Added for Bug#14385437 */
193
194 IF l_direct_fed_bal_call = 'Y' THEN
195
196 IF p_assignment_action_id IS NOT NULL THEN
197
198 OPEN get_year(p_assignment_action_id);
199 FETCH get_year INTO l_year;
200 CLOSE get_year;
201
202 hr_utility.trace('Year from Assignment Action ID: '||l_year);
203
204 ELSE
205
206 l_year := TO_CHAR(p_virtual_date,'YYYY');
207
208 hr_utility.trace('Year from Virtual Date: '||l_year);
209
210 END IF;
211
212 l_direct_bal_year := NVL(fnd_profile.value('PAY_US_DIRECT_BALANCE_START_YEAR'),'0001');
213
214 IF l_direct_bal_year > l_year THEN
215
216 l_direct_fed_bal_call := 'N';
217
218 ELSE
219
220 hr_utility.trace('US Federal Balances will be fetched from Direct Values');
221
222 END IF;
223
224 END IF;
225 /* End of changes for Bug#14385437 */
226 /*End Bug#6696348*/
227 --
228 -- Check that inputs based on lookups are valid
229 --
230 SELECT count(*)
231 INTO l_valid
232 FROM hr_lookups
233 WHERE lookup_type = 'US_TAX_BALANCE_CATEGORY'
234 AND lookup_code = p_tax_balance_category;
235 --
236 IF l_valid = 0 THEN
237 hr_utility.trace('Error: Invalid tax balance category');
238 local_error('us_tax_balance',1);
239 END IF;
240 --
241 SELECT count(*)
242 INTO l_valid
243 FROM hr_lookups
244 WHERE lookup_type = 'US_TAX_TYPE'
245 AND lookup_code = p_tax_type;
246 --
247 IF l_valid = 0 THEN
248 IF instr(l_tax_type,'PSD') = 0 THEN -- #11926304 Added If to support PSD tax
249 hr_utility.trace('Error: Invalid tax type');
250 local_error('us_tax_balance',2);
251 END IF; -- #11926304
252 END IF;
253 --
254 SELECT count(*)
255 INTO l_valid
256 FROM dual
257 WHERE p_asg_type in ('ASG','PER','GRE');
258 --
259 IF l_valid = 0 THEN
260 hr_utility.trace('Error: Invalid asg_type parameter');
261 local_error('us_tax_balance',3);
262 END IF;
263 --
264 SELECT count(*)
265 INTO l_valid
266 FROM dual
267 WHERE p_time_type in ('RUN','PTD','MONTH','QTD','YTD', 'PAYMENTS', 'PYDATE');
268 --
269 IF l_valid = 0 THEN
270 hr_utility.trace('Error: Invalid time_type parameter');
271 local_error('us_tax_balance',4);
272 END IF;
273 --
274 -- Set the contexts used in the bal user exit. Same throughout, so set
275 -- them up front
276 --
277 hr_utility.set_location('pay_tax_bals_pkg',30);
278 --
279 IF p_jd_context IS NOT NULL THEN
280 IF (p_tax_type = 'SCHOOL' and length(p_jd_context) > 11) THEN
281 pay_balance_pkg.set_context('JURISDICTION_CODE',substr(p_jd_context,1,2)||
282 '-'||substr(p_jd_context,13,5));
283 ELSE
284 pay_balance_pkg.set_context('JURISDICTION_CODE',p_jd_context);
285 END IF;
286 END IF;
287 --
288 hr_utility.set_location('pay_tax_bals_pkg',40);
289 --
290 l_assignment_id := p_assignment_id;
291 l_assignment_action_id := p_assignment_action_id;
292 l_tax_type := p_tax_type;
293 l_tax_balance_category := p_tax_balance_category;
294 l_virtual_date := p_virtual_date;
295 --
296 -- Check if assignment exists at l_virtual_date, if using date mode
297 --
298 hr_utility.set_location('pay_tax_bals_pkg',50);
299 --
300 --
301 -- Convert "WITHHELD" to proper balance categories;
302 --
303 hr_utility.set_location('pay_tax_bals_pkg',80);
304 --
305 IF l_tax_balance_category = 'WITHHELD' THEN
306 IF p_ee_or_er = 'ER' or l_tax_type = 'FUTA' or l_tax_type = 'HT' THEN
307 l_tax_balance_category := 'LIABILITY';
308 ELSIF l_tax_type = 'EIC' THEN
309 l_tax_balance_category := 'ADVANCE';
310 END IF;
311 END IF;
312 IF l_tax_balance_category = 'ADVANCED' THEN
313 l_tax_balance_category := 'ADVANCE';
314 END IF;
315 --
316 -- Check if illegal tax combo (FIT and TAXABLE, FUTA and SUBJ_NWHABLE, etc.)
317 --
318 hr_utility.set_location('pay_tax_bals_pkg',90);
319 --
320 IF (l_tax_type = 'FIT' or l_tax_type = 'SIT' or l_tax_type = 'COUNTY' or
321 l_tax_type = 'CITY' or l_tax_type = 'EIC' or l_tax_type = 'HT' or
322 l_tax_type = 'SCHOOL') THEN -- income tax
323 IF (l_tax_balance_category = 'TAXABLE' or
324 l_tax_balance_category = 'EXCESS') THEN
325 hr_utility.trace('Error: Illegal tax category for tax type');
326 local_error('us_tax_balance',5);
327 END IF;
328 --
329 -- return 0 for currently unsupported EIC balances.
330 -- skutteti added 403,457 and Pre tax REDNS
331 --
332 IF l_tax_type = 'EIC' and (l_tax_balance_category = 'SUBJ_NWHABLE' or
333 l_tax_balance_category = '401_REDNS' or
334 l_tax_balance_category = '125_REDNS' or
335 l_tax_balance_category = 'DEP_CARE_REDNS' or
336 l_tax_balance_category = '403_REDNS' or
337 l_tax_balance_category = '457_REDNS' or
338 l_tax_balance_category = 'PRE_TAX_REDNS' ) THEN
339 return 0;
340 END IF;
341 ELSE -- limit tax
342 IF l_tax_balance_category = 'SUBJ_NWHABLE' THEN
343 return 0;
344 END IF;
345 END IF;
346 --
347 hr_utility.set_location('pay_tax_bals_pkg',100);
348 --
349 l_ee_or_er := ltrim(rtrim(p_ee_or_er));
350 --
351 --------------- Some Error Checking -------------
352 --
353 --
354 if (l_tax_type = 'FIT' or l_tax_type = 'SIT' or l_tax_type = 'CITY' or
355 l_tax_type = 'COUNTY' or l_tax_type = 'EIC' or l_tax_type = 'SCHOOL'
356 or instr(l_tax_type,'PSD') <> 0 ) THEN --#11926304
357 if l_ee_or_er = 'ER' THEN
358 hr_utility.trace('Error: ER not allowed for tax type');
359 local_error('us_tax_balance',6);
360 else
361 l_ee_or_er := NULL;
362 end if;
363 elsif (l_tax_type = 'FUTA' or l_tax_type = 'HT') THEN
364 if l_ee_or_er = 'EE' THEN
365 hr_utility.trace('Error: EE not allowed for tax type');
366 local_error('us_tax_balance',7);
367 else
368 l_ee_or_er := NULL;
369 end if;
370 elsif (l_tax_type = 'SS' or l_tax_type = 'MEDICARE' or l_tax_type = 'SDI' or
371 l_tax_type = 'SUI') THEN
372 if (l_ee_or_er <> 'EE' and l_ee_or_er <> 'ER') THEN
373 hr_utility.trace('Error: EE or ER required for tax type');
374 local_error('us_tax_balance',8);
375 end if;
376 elsif (l_tax_type = 'SUI1') OR (l_tax_type = 'SDI1')THEN
377 if (l_ee_or_er <> 'EE' ) THEN
378 hr_utility.trace('Error: EE required for tax type');
379 local_error('us_tax_balance',9);
380 end if;
381 end if;
382 --
383 hr_utility.set_location('pay_tax_bals_pkg',110);
384
385 -- As of implementation of the SUI1 EE Tax, we only maintain
386 -- a WIthheld balance. As the SUI1 tax type should match
387 -- balances with SUI We will return the SUI balances.
388
389 IF l_tax_type = 'SUI1' and (l_tax_balance_category <> 'WITHHELD'
390 ) THEN
391 l_tax_type := 'SUI';
392 END IF;
393
394 IF l_tax_type = 'SDI1' and (l_tax_balance_category <> 'WITHHELD'
395 ) THEN
396 l_tax_type := 'SDI';
397 END IF;
398 --
399 -- Force space at end of this parameter if necessary
400 --
401 hr_utility.set_location('pay_tax_bals_pkg',120);
402 --
403 IF l_ee_or_er IS NOT NULL THEN
404 l_ee_or_er := rtrim(l_ee_or_er)||' ';
405 END IF;
406 --
407 -- Set up dimension strings
408 --
409 IF p_asg_type <> 'GRE' THEN
410 l_dimension_string := p_asg_type||'_GRE_'||p_time_type;
411 l_jd_dimension_string := p_asg_type||'_JD_GRE_'||p_time_type;
412 ELSE
413 --
414 l_dimension_string := 'GRE_'||p_time_type;
415 l_jd_dimension_string := 'GRE_JD_'||p_time_type;
416 --
417 l_assignment_id := p_assignment_id;
418 --
419 END IF;
420
421 IF p_time_type = 'PAYMENTS' THEN
422 --
423 -- 333594 payments_jd dimension is defunct
424 -- removed following line
425 -- l_jd_dimension_string := p_time_type||'_JD';
426 --
427 l_dimension_string := p_time_type;
428 l_jd_dimension_string := p_time_type;
429
430 END IF;
431 --
432 --
433 -- Check if the tax is federal or not.
434 --
435 SELECT count(*)
436 INTO l_test
437 FROM sys.dual
438 WHERE l_tax_type in ('FIT','FUTA','MEDICARE','SS','EIC');
439 --
440 IF l_test <> 0 THEN -- yes, the tax is federal
441 --
442
443 IF l_direct_fed_bal_call = 'Y' THEN
444
445 IF l_tax_type IN ('MEDICARE','SS') THEN
446
447 l_tax_tag := l_tax_type||'_'||rtrim(l_ee_or_er);
448
449 ELSE
450
451 l_tax_tag := l_tax_type;
452
453 END IF;
454
455 END IF;
456
457 IF l_tax_balance_category = 'GROSS' THEN
458
459 IF l_direct_fed_bal_call = 'Y' THEN
460
461 l_return_value := call_balance_user_exit (l_tax_tag||'_'||l_tax_balance_category,
462 l_dimension_string,
463 l_assignment_action_id,
464 p_business_group_id);
465
466 ELSE
467
468 l_return_value := call_balance_user_exit ('GROSS_EARNINGS',
469 l_dimension_string,
470 l_assignment_action_id,
471 p_business_group_id);
472 --
473 -- The if condition was added by subbu on 15-sep-2000
474 --
475 IF l_tax_type = 'FIT' AND l_return_value > 0 THEN
476 l_return_value := l_return_value -
477 call_balance_user_exit ('ALIEN_EXPAT_EARNINGS',
478 l_dimension_string,
479 l_assignment_action_id,
480 p_business_group_id);
481 END IF;
482
483 END IF;
484 --
485 ELSIF l_tax_balance_category = 'SUBJ_WHABLE' THEN
486
487 IF l_direct_fed_bal_call = 'Y' THEN
488
489 l_return_value := call_balance_user_exit (l_tax_tag||'_'||l_tax_balance_category,
490 l_dimension_string,
491 l_assignment_action_id,
492 p_business_group_id);
493
494 ELSE
495
496 l_return_value := call_balance_user_exit ('REGULAR_EARNINGS',
497 l_dimension_string,
498 l_assignment_action_id,
499 p_business_group_id)
500 + call_balance_user_exit (
501 'SUPPLEMENTAL_EARNINGS_FOR_'||l_tax_type,
502 'SUBJECT_TO_TAX_'||l_dimension_string,
503 l_assignment_action_id,
504 p_business_group_id);
505
506 END IF;
507 --
508 ELSIF l_tax_balance_category = 'SUBJ_NWHABLE' THEN
509
510 IF l_direct_fed_bal_call = 'Y' THEN
511
512 l_return_value := call_balance_user_exit (l_tax_tag||'_'||l_tax_balance_category,
513 l_dimension_string,
514 l_assignment_action_id,
515 p_business_group_id);
516
517 ELSE
518
519 l_return_value := call_balance_user_exit (
520 'SUPPLEMENTAL_EARNINGS_FOR_NW'||l_tax_type,
521 'SUBJECT_TO_TAX_'||l_dimension_string,
522 l_assignment_action_id,
523 p_business_group_id);
524
525 END IF;
526 --
527 ELSIF l_tax_balance_category = '401_REDNS' THEN
528
529 IF l_direct_fed_bal_call = 'Y' THEN
530
531 l_return_value := call_balance_user_exit (l_tax_tag||'_'||l_tax_balance_category,
532 l_dimension_string,
533 l_assignment_action_id,
534 p_business_group_id);
535
536 ELSE
537
538 l_return_value := call_balance_user_exit ('DEF_COMP_401K',
539 l_dimension_string,
540 l_assignment_action_id,
541 p_business_group_id);
542 --
543 -- 337641
544 -- check if balance 0 therefore no need to
545 -- subtract subsequent balance
546 --
547 IF ( l_return_value <> 0 )
548 THEN
549 l_return_value := l_return_value
550 - call_balance_user_exit ('DEF_COMP_401K_FOR_'||l_tax_type,
551 'SUBJECT_TO_TAX_'||l_dimension_string,
552 l_assignment_action_id,
553 p_business_group_id);
554 --
555 -- added by skutteti in Nov 2000, to remove the Non W2 portion
556 --
557 IF l_tax_type = 'FIT' THEN
558 l_return_value := l_return_value - call_balance_user_exit
559 ('FIT_NON_W2_DEF_COMP_401',
560 l_dimension_string,
561 l_assignment_action_id,
562 p_business_group_id);
563 END IF;
564 END IF;
565
566 END IF;
567 --
568 ELSIF l_tax_balance_category = '125_REDNS' THEN
569
570 IF l_direct_fed_bal_call = 'Y' THEN
571
572 l_return_value := call_balance_user_exit (l_tax_tag||'_'||l_tax_balance_category,
573 l_dimension_string,
574 l_assignment_action_id,
575 p_business_group_id);
576
577 ELSE
578
579 l_return_value := call_balance_user_exit ('SECTION_125',
580 l_dimension_string,
581 l_assignment_action_id,
582 p_business_group_id);
583 --
584 -- 337641
585 -- check if balance 0 therefore no need to
586 -- subtract subsequent balance
587 --
588 IF ( l_return_value <> 0 )
589 THEN
590 l_return_value := l_return_value
591 - call_balance_user_exit ('SECTION_125_FOR_'||l_tax_type,
592 'SUBJECT_TO_TAX_'||l_dimension_string,
593 l_assignment_action_id,
594 p_business_group_id);
595 --
596 -- added by skutteti in Nov 2000, to remove the Non W2 portion
597 --
598 IF l_tax_type = 'FIT' THEN
599 l_return_value := l_return_value - call_balance_user_exit
600 ('FIT_NON_W2_SECTION_125',
601 l_dimension_string,
602 l_assignment_action_id,
603 p_business_group_id);
604 END IF;
605 END IF;
606
607 END IF;
608 --
609 ELSIF l_tax_balance_category = 'DEP_CARE_REDNS' THEN
610
611 IF l_direct_fed_bal_call = 'Y' THEN
612
613 l_return_value := call_balance_user_exit (l_tax_tag||'_'||l_tax_balance_category,
614 l_dimension_string,
615 l_assignment_action_id,
616 p_business_group_id);
617
618 ELSE
619
620 l_return_value := call_balance_user_exit ('DEPENDENT_CARE',
621 l_dimension_string,
622 l_assignment_action_id,
623 p_business_group_id);
624 --
625 -- 337641
626 -- check if balance 0 therefore no need to
627 -- subtract subsequent balance
628 --
629 IF ( l_return_value <> 0 )
630 THEN
631 l_return_value := l_return_value
632 - call_balance_user_exit ('DEPENDENT_CARE_FOR_'||l_tax_type,
633 'SUBJECT_TO_TAX_'||l_dimension_string,
634 l_assignment_action_id,
635 p_business_group_id);
636 --
637 -- added by skutteti in Nov 2000, to remove the Non W2 portion
638 --
639 IF l_tax_type = 'FIT' THEN
640 l_return_value := l_return_value - call_balance_user_exit
641 ('FIT_NON_W2_DEPENDENT_CARE',
642 l_dimension_string,
643 l_assignment_action_id,
644 p_business_group_id);
645 END IF;
646 END IF;
647
648 END IF;
649 --
650 -------------------------------------------------------------------------------
651 -- skutteti added the following: 403,457 and PRE_TAX as part of the pre-tax
652 -- enhancements
653 -------------------------------------------------------------------------------
654 ELSIF l_tax_balance_category = 'PRE_TAX_REDNS' THEN
655
656 IF l_direct_fed_bal_call = 'Y' THEN
657
658 l_return_value := call_balance_user_exit (l_tax_tag||'_'||l_tax_balance_category,
659 l_dimension_string,
660 l_assignment_action_id,
661 p_business_group_id);
662
663 ELSE
664
665 l_return_value := call_balance_user_exit ('PRE_TAX_DEDUCTIONS',
666 l_dimension_string,
667 l_assignment_action_id,
668 p_business_group_id);
669 IF ( l_return_value <> 0 )
670 THEN
671 l_return_value := l_return_value - call_balance_user_exit (
672 'PRE_TAX_DEDUCTIONS_FOR_'||l_tax_type,
673 'SUBJECT_TO_TAX_'||l_dimension_string,
674 l_assignment_action_id,
675 p_business_group_id);
676 --
677 -- added by skutteti in Nov 2000, to remove the Non W2 portion
678 --
679 IF l_tax_type = 'FIT' THEN
680 l_return_value := l_return_value - call_balance_user_exit
681 ('FIT_NON_W2_PRE_TAX_DEDNS',
682 l_dimension_string,
683 l_assignment_action_id,
684 p_business_group_id);
685 END IF;
686 --
687 END IF;
688
689 END IF;
690 --
691 ELSIF l_tax_balance_category = '403_REDNS' THEN
692
693 IF l_direct_fed_bal_call = 'Y' THEN
694
695 l_return_value := call_balance_user_exit (l_tax_tag||'_'||l_tax_balance_category,
696 l_dimension_string,
697 l_assignment_action_id,
698 p_business_group_id);
699
700 ELSE
701
702 l_return_value := call_balance_user_exit ('DEF_COMP_403B',
703 l_dimension_string,
704 l_assignment_action_id,
705 p_business_group_id);
706 IF ( l_return_value <> 0 ) THEN
707 l_return_value := l_return_value - call_balance_user_exit (
708 'DEF_COMP_403B_FOR_'||l_tax_type,
709 'SUBJECT_TO_TAX_'||l_dimension_string,
710 l_assignment_action_id,
711 p_business_group_id);
712 --
713 -- added by skutteti in Nov 2000, to remove the Non W2 portion
714 --
715 IF l_tax_type = 'FIT' THEN
716 l_return_value := l_return_value - call_balance_user_exit
717 ('FIT_NON_W2_DEF_COMP_403',
718 l_dimension_string,
719 l_assignment_action_id,
720 p_business_group_id);
721 END IF;
722 END IF;
723
724 END IF;
725 --
726 ELSIF l_tax_balance_category = '457_REDNS' THEN
727
728 IF l_direct_fed_bal_call = 'Y' THEN
729
730 l_return_value := call_balance_user_exit (l_tax_tag||'_'||l_tax_balance_category,
731 l_dimension_string,
732 l_assignment_action_id,
733 p_business_group_id);
734
735 ELSE
736
737 l_return_value := call_balance_user_exit ('DEF_COMP_457',
738 l_dimension_string,
739 l_assignment_action_id,
740 p_business_group_id);
741 IF ( l_return_value <> 0 ) THEN
742 l_return_value := l_return_value - call_balance_user_exit (
743 'DEF_COMP_457_FOR_'||l_tax_type,
744 'SUBJECT_TO_TAX_'||l_dimension_string,
745 l_assignment_action_id,
746 p_business_group_id);
747 --
748 -- added by skutteti in Nov 2000, to remove the Non W2 portion
749 --
750 IF l_tax_type = 'FIT' THEN
751 l_return_value := l_return_value - call_balance_user_exit
752 ('FIT_NON_W2_DEF_COMP_457',
753 l_dimension_string,
754 l_assignment_action_id,
755 p_business_group_id);
756 END IF;
757 END IF;
758
759 END IF;
760 --
761 ELSIF l_tax_balance_category = 'TAXABLE' THEN
762
763 l_return_value := call_balance_user_exit (l_tax_type||'_'||
764 l_ee_or_er||'TAXABLE',
765 l_dimension_string,
766 l_assignment_action_id,
767 p_business_group_id);
768 --
769 ELSIF (l_tax_balance_category = 'WITHHELD' or
770 l_tax_balance_category = 'LIABILITY' or
771 l_tax_balance_category = 'ADVANCE') THEN
772 l_return_value := call_balance_user_exit (
773 l_tax_type||'_'||l_ee_or_er||l_tax_balance_category,
774 l_dimension_string,
775 l_assignment_action_id,
776 p_business_group_id);
777 END IF;
778 ELSE -- the tax is non-federal
779 --
780 -- if the tax balance is not derived, get it here.
781 IF (l_tax_balance_category <> 'SUBJECT' and
782 l_tax_balance_category <> 'EXEMPT' and
783 l_tax_balance_category <> 'EXCESS' and
784 l_tax_balance_category <> 'REDUCED_SUBJ_WHABLE') THEN
785 --
786 -- Use the CITY balances for HT if we don't want to see LIABILITY
787 --
788 IF (l_tax_type = 'HT') THEN
789 IF (l_tax_balance_category <> 'LIABILITY') THEN
790 l_tax_type := 'CITY';
791 ELSE
792 l_tax_type := 'HEAD TAX';
793 END IF;
794 END IF;
795 --
796 l_return_value := call_balance_user_exit (
797 l_tax_type||'_'||l_ee_or_er||l_tax_balance_category,
798 l_jd_dimension_string,
799 l_assignment_action_id,
800 p_business_group_id);
801 /* The following code was commented by tmehra as SIT Redns is now
802 directly being reduced by the feeds.
803 --
804 -- added by skutteti to remove the non w2 portion for pre tax REDNS
805 --
806 IF (l_return_value <> 0 AND
807 l_tax_type = 'SIT' AND
808 l_tax_balance_category like '%REDNS' ) THEN
809 IF l_tax_balance_category = 'PRE_TAX_REDNS' THEN
810 l_non_w2_cat := 'NON_W2_PRE_TAX_DEDNS';
811 ELSIF l_tax_balance_category = '401_REDNS' THEN
812 l_non_w2_cat := 'NON_W2_DEF_COMP_401';
813 ELSIF l_tax_balance_category = '403_REDNS' THEN
814 l_non_w2_cat := 'NON_W2_DEF_COMP_403';
815 ELSIF l_tax_balance_category = '457_REDNS' THEN
816 l_non_w2_cat := 'NON_W2_DEF_COMP_457';
817 ELSIF l_tax_balance_category = '125_REDNS' THEN
818 l_non_w2_cat := 'NON_W2_SECTION_125';
819 ELSIF l_tax_balance_category = 'DEP_CARE_REDNS' THEN
820 l_non_w2_cat := 'NON_W2_DEPENDENT_CARE';
821 END IF;
822 l_return_value := l_return_value - call_balance_user_exit (
823 'SIT_'||l_non_w2_cat,
824 l_jd_dimension_string,
825 l_assignment_action_id,
826 p_business_group_id);
827 END IF;
828
829 */
830
831 END IF;
832 END IF;
833 --
834 IF l_tax_balance_category = 'SUBJECT' THEN
835 l_return_value := us_tax_balance('SUBJ_WHABLE',
836 l_tax_type,
837 p_ee_or_er,
838 p_time_type,
839 p_asg_type,
840 p_gre_id_context,
841 p_jd_context,
842 l_assignment_action_id,
843 l_assignment_id,
844 l_virtual_date,
845 p_business_group_id)
846 + us_tax_balance('SUBJ_NWHABLE',
847 l_tax_type,
848 p_ee_or_er,
849 p_time_type,
850 p_asg_type,
851 p_gre_id_context,
852 p_jd_context,
853 l_assignment_action_id,
854 l_assignment_id,
855 l_virtual_date,
856 p_business_group_id);
857 --
858 ELSIF l_tax_balance_category = 'EXEMPT' THEN
859 l_return_value := us_tax_balance('GROSS',
860 l_tax_type,
861 p_ee_or_er,
862 p_time_type,
863 p_asg_type,
864 p_gre_id_context,
865 p_jd_context,
866 l_assignment_action_id,
867 l_assignment_id,
868 l_virtual_date,
869 p_business_group_id);
870 --
871 -- 337641
872 -- check if balance 0 therefore no need to
873 -- subtract subsequent balance
874 --
875 IF ( l_return_value <> 0 )
876 THEN
877 l_return_value := l_return_value
878 - us_tax_balance('SUBJECT',
879 l_tax_type,
880 p_ee_or_er,
881 p_time_type,
882 p_asg_type,
883 p_gre_id_context,
884 p_jd_context,
885 l_assignment_action_id,
886 l_assignment_id,
887 l_virtual_date,
888 p_business_group_id);
889 END IF;
890 --
891 ELSIF l_tax_balance_category = 'REDUCED_SUBJ_WHABLE' THEN
892 l_return_value := us_tax_balance('SUBJ_WHABLE',
893 l_tax_type,
894 p_ee_or_er,
895 p_time_type,
896 p_asg_type,
897 p_gre_id_context,
898 p_jd_context,
899 l_assignment_action_id,
900 l_assignment_id,
901 l_virtual_date,
902 p_business_group_id);
903 --
904 -- 337641
905 -- check if balance 0 therefore no need to
906 -- subtract subsequent balance
907 --
908 IF ( l_return_value <> 0 )
909 THEN
910 /***************************************************************
911 * skutteti commented all the individual pre-tax categories and
912 * replaced it by PRE_TAX_REDNS
913 ***************************************************************/
914 -- l_return_value := l_return_value
915 -- - us_tax_balance('401_REDNS',
916 -- l_tax_type,
917 -- p_ee_or_er,
918 -- p_time_type,
919 -- p_asg_type,
920 -- p_gre_id_context,
921 -- p_jd_context,
922 -- l_assignment_action_id,
923 -- l_assignment_id,
924 -- l_virtual_date,
925 -- p_business_group_id)
926 -- - us_tax_balance('125_REDNS',
927 -- l_tax_type,
928 -- p_ee_or_er,
929 -- p_time_type,
930 -- p_asg_type,
931 -- p_gre_id_context,
932 -- p_jd_context,
933 -- l_assignment_action_id,
934 -- l_assignment_id,
935 -- l_virtual_date,
936 -- p_business_group_id)
937 -- - us_tax_balance('DEP_CARE_REDNS',
938 -- l_tax_type,
939 -- p_ee_or_er,
940 -- p_time_type,
941 -- p_asg_type,
942 -- p_gre_id_context,
943 -- p_jd_context,
944 -- l_assignment_action_id,
945 -- l_assignment_id,
946 -- l_virtual_date,
947 -- p_business_group_id);
948 /**********************************************************
949 * skutteti added the following part
950 **********************************************************/
951 l_return_value := l_return_value - us_tax_balance(
952 'PRE_TAX_REDNS',
953 l_tax_type,
954 p_ee_or_er,
955 p_time_type,
956 p_asg_type,
957 p_gre_id_context,
958 p_jd_context,
959 l_assignment_action_id,
960 l_assignment_id,
961 l_virtual_date,
962 p_business_group_id);
963 END IF;
964 --
965 ELSIF l_tax_balance_category = 'EXCESS' THEN
966 l_return_value := us_tax_balance('REDUCED_SUBJ_WHABLE',
967 l_tax_type,
968 p_ee_or_er,
969 p_time_type,
970 p_asg_type,
971 p_gre_id_context,
972 p_jd_context,
973 l_assignment_action_id,
974 l_assignment_id,
975 l_virtual_date,
976 p_business_group_id);
977 --
978 -- 337641
979 -- check if balance 0 therefore no need to
980 -- subtract subsequent balance
981 --
982 IF ( l_return_value <> 0 )
983 THEN
984 l_return_value := l_return_value
985 - us_tax_balance('TAXABLE',
986 l_tax_type,
987 p_ee_or_er,
988 p_time_type,
989 p_asg_type,
990 p_gre_id_context,
991 p_jd_context,
992 l_assignment_action_id,
993 l_assignment_id,
994 l_virtual_date,
995 p_business_group_id);
996 END IF;
997 END IF;
998 --
999 hr_utility.trace('Returning : ' || l_return_value);
1000 --
1001 return l_return_value;
1002 --
1003 END us_tax_balance;
1004 --
1005 BEGIN
1006 g_nxt_free_defbal := 1;
1007 END pay_us_tax_balance_perf;