[Home] [Help]
PACKAGE BODY: APPS.PAY_US_TAX_BALS_PKG
Source
1 package body pay_us_tax_bals_pkg as
2 /* $Header: pyustxbl.pkb 120.9.12020000.2 2012/11/01 17:21:44 emunisek ship $ */
3 --
4 -- Copyright (c) Oracle Corporation 1991, 1992, 1993. All rights reserved.
5 --
6 /*
7 NAME
8 pyustxbl.pkb
9 --
10 DESCRIPTION
11 API to get US tax balance figures.
12 --
13 MODIFIED (DD-MON-YYYY)
14 S Panwar 1-FEB-1995 Created
15 S Panwar ?-???-???? Various changes
16 S Panwar 13-JUL-1995 Improved error handling/reporting, added code
17 to catch unsupported EIC balances.
18 H Parichabutr 24-JUL-1995 Updated to handle "PAYMENTS" and "PAYMENTS_JD"
19 time types - required for displaying tax bals
20 on SOE.
21 S Desai 27-Nov-1995 Added PYDATE time dimension.
22 gpaytonm 09-JAN-1995 333594 payments_jd dimension is defunct
23 gpaytonm 01-FEB-1996 337641 performance fixes to SQL statements (two)
24 and more control over calling bal user exit.
25 S Desai 18-Mar-1996 Ensure that get_dummy_asg_id returns an assignment
26 with a payroll_id and is effective on the virtual
27 date in order to pass the core.
28 T Grisco 23-Apr-1996 360669 put PAYMENTS_JD back.
29 S Desai 20-Aug-1996 371351: Head Tax mis-classified as an employer
30 liability.
31 L Thompson30-SEP-1996 40.15 395029: Must execute db item if dimension is
32 _PAYMENTS
33 L Thompson03-NOV-1996 40.16 378594: WC_EE previously not accessible.
34
35 nbristow 08-NOV-1996 40.17 420465: Added several performance fixes.
36 Major changes to improve
37 the handling of latest balances.
38 arcsed in by lwthomps.
39 nbristow 14-NOV-1996 40.18 Removed hard coded path, no longer forced
40 to use latest balances.
41 nbristow 20-NOV-1996 40.19 Created overload functions and
42 us_tax_balance_rep functions.
43 nbristow 06-DEC-1996 40.20 Fixed get_virtual_date, now also checks
44 the payroll as well as the assignment.
45 nbristow 18-DEC-1996 40.21 Changed calls to get_value (date mode) to
46 get_value_lock.
47 lwthomps 27-May-1997 40.22 489769, WC2_EE previously not accessible.
48 Similiar to change for 40.16.
49 tbattoo 16-JAN-1998 40.23(110.0) changed date format to DD-MON-YYYY -
50 bug 612696.
51 tbattoo 11-MAY-1998 40.24(110.1) dual mantained changes in view so
52 GRE PYDATE routes work over a range
53 djoshi 08-APR-1999 Verfied and converted for Canonical
54 Complience of Date
55 skutteti 14-SEP-1999 115.4 Pre-tax enhancements. Added categories 403B
56 and 457 wherever required.
57 hzhao 10-DEC-1999 115.5 Added support of pre-tax for EIC
58 JARTHURT 24-JUL-2000 115.6 Added legislation_code check in
59 get_defined_balance
60 skutteti 15-SEP-2000 115.8 Currently there is no balance for FIT gross,
61 instead 'Gross Earnings' is used. Changed code
62 to subtract Alien earnings from FIT Gross.
63 skutteti 23-NOV-2000 115.9 Pre tax for Alien expat earnings has to be
64 reported in 1042s. Added code to subtract the
65 Alien portion of Pre-tax for SIT/FIT purposes.
66 tmehra 16-AUG-2001 115.10 Removed above code to subtract Non W2 protion
67 of Pre-Tax for SIT as new balance feeds have
68 been added to achive this.
69 kthirmiy 01-OCT-2001 115.11 Added code for balance extract with the
70 tax_balance_category of 'OTHER_PRETAX_REDNS'
71 to show in the Pretax Details block for
72 other pre-tax enhancements
73 meshah 13-JUN-2002 115.18 changed the function call_balance_user_exit
74 to remove the call to get_grp_value because
75 from July 2002 we should be using the Balance
76 Reporting Arch. and that does not require the
77 call.
78 for new TRR checking REDUCED_SUBJ_WHABLE and
79 session_var of W2.
80 meshah 11-FEB-2003 115.21 Now checking for a session var of PAYUSNFR
81 to set the assignment_action_id before making
82 the balance call.
83 meshah 13-FEB-2003 115.22 nocopy.
84 meshah 17-APR-2003 115.23 changed the name of the session var from
85 PAYUSNFR to GROUP_RB_REPORT and added a new
86 cursor c_get_max_aaid.
87 meshah 29-MAY-2003 115.24 changed cursor c_get_max_aaid to c_get_min_aaid
88 GRE Totals, 940 and 941 reports are now
89 setting GROUP_RB_SDATE and GROUP_RB_EDATE
90 session variables to get the minimum
91 assignment_action_id. In c_get_min_aaid we
92 are using nvl in the select to return a -1
93 for cases where there are no runs.
94 meshah 04-JUN-2003 115.25 changed cursor c_get_min_aaid to work with
95 business_group_id and added a new cursor
96 c_get_bg_id.
97 sdahiya 12-JAN-2004 115.26 Modified query for performance enhancement
98 (Bug 3343974).
99 kvsankar 16-JAN-2004 115.27 Modified query for performance enhancement
100 (Bug 3290396).
101 tlcewis 17-MAR-2004 115.28 added coding for STEIC.
102 fusman 10-JAN-2005 115.29 Added JD_dimension String for NY FUTA Taxable.
103 fusman 12-JAN-2005 115.30 Changed the l_test value to 0 to make FUTA a state tax.
104 pragupta 14-APR-2005 115.31 Increased the size of l_tax_type
105 sackumar 13-SEP-2005 115.32 (Bug 4347453) Modified the g_dim_tbl_crs(3) query.
106 Introduced Index Hint in the query.
107 rdhingra 23-SEP-2005 115.33 Bug 4583560: Performance changes done
108 rdhingra 27-SEP-2005 115.34 Bug 4583560: Performance changes done
109 Reverting changes of ver 32 as it was putting a full index scan
110 tclewis 04-DEC-2008 115.35 Added validaton for SUI1 EE and SDI1 EE
111 emunisek 03-JUN-2010 115.38 Modified US_TAX_BALANCE function to change the way Federal Tax
112 Balances are fetched.Replaced the derived approach with estimated
113 values from Earnings with Run Results.The new changes are dependant
114 on a profile value set at site level.If the profile value is not
115 set or set as No, the balances will be fetched as it was before.
116 Only when Profile value is made Yes, the new changes will be effective.
117 emunisek 18-OCT-2011 115.39 Modified US_TAX_BALANCE function to support the value fetching for
118 FUTA CREDIT Balance
119 nkjaladi 23-NOV-2011 115.40 Bug #11926304 Modified US_TAX_BALANCE function
120 to support the value fetching for PSD taxes.
121 emunisek 01-NOV-2012 115.41 Bug#14385437 Added changes to check the value set for Profile
122 Option 'PAY_US_DIRECT_BALANCE_START_YEAR' to use
123 the Direct US Federal Balances approach.
124 */
125
126 -- Global declarations
127 type num_array is table of number(15) index by binary_integer;
128 type char80_array is table of varchar2(80) index by binary_integer;
129 type char_array is table of varchar2(1) index by binary_integer;
130 type char2000_array is table of varchar2(2000) index by binary_integer;
131 --
132 -- Assignment Id Cache
133 g_asgid_tbl_id num_array;
134 g_asgid_tbl_bgid num_array;
135 g_nxt_free_asgid binary_integer := 0;
136 --
137 -- Group Dimension Cache.
138 g_dim_tbl_grp char80_array;
139 g_dim_tbl_asg char80_array;
140 g_dim_tbl_crs char2000_array;
141 g_dim_tbl_vtd char2000_array;
142 g_dim_tbl_jdr char_array;
143 g_dim_tbl_btt char_array;
144 g_nxt_free_dim binary_integer;
145 --
146 -------------------------------------------------------------------------------
147 --
148 -- Quick procedure to raise an error
149 --
150 -------------------------------------------------------------------------------
151 PROCEDURE local_error(p_procedure varchar2,
152 p_step number) IS
153 BEGIN
154 --
155 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
156 hr_utility.set_message_token('PROCEDURE',
157 'pay_us_tax_bals_pkg.'||p_procedure);
158 hr_utility.set_message_token('STEP',p_step);
159 hr_utility.raise_error;
160 --
161 END local_error;
162 --
163 --
164 ----------------------------------------------------------------------------
165 -- Get the assignment level equivilent of the group balance, plus a cursor
166 -- that returns all the assignments contributing to the group level balance.
167 ----------------------------------------------------------------------------
168 procedure get_asg_for_grp_lvl(p_grp_dvl_dimension in varchar2,
169 p_asg_lvl_dimension out nocopy varchar2,
170 p_asg_cursor out nocopy varchar2,
171 p_asg_jd_required out nocopy boolean,
172 p_asg_vdate_cursor out nocopy varchar2,
173 p_asg_balance_time out nocopy varchar2,
174 p_found out nocopy boolean)
175 is
176 l_count number;
177 l_found boolean;
178 begin
179 -- Look to see if the group level balance is in our cache.
180 --
181 hr_utility.set_location('pay_us_tax_bals_pkg.get_asg_for_grp_lvl', 10);
182 --
183 l_count := 0;
184 l_found := FALSE;
185 while ((l_count < g_nxt_free_dim) AND (l_found = FALSE)) loop
186 if (p_grp_dvl_dimension = g_dim_tbl_grp(l_count)) then
187 hr_utility.set_location('pay_us_tax_bals_pkg.get_asg_for_grp_lvl', 20);
188 --
189 p_asg_lvl_dimension := g_dim_tbl_asg(l_count);
190 p_asg_cursor := g_dim_tbl_crs(l_count);
191 p_asg_vdate_cursor := g_dim_tbl_vtd(l_count);
192 p_asg_balance_time := g_dim_tbl_btt(l_count);
193 --
194 -- Does the cursor require the jurisdiction_code.
195 --
196 if g_dim_tbl_jdr(l_count) = 'Y' then
197 p_asg_jd_required := TRUE;
198 else
199 p_asg_jd_required := FALSE;
200 end if;
201 l_found := TRUE;
202 end if;
203 l_count := l_count + 1;
204 end loop;
205 --
206 hr_utility.set_location('pay_us_tax_bals_pkg.get_asg_for_grp_lvl', 30);
207 p_found := l_found;
208 --
209 end;
210 --
211 ----------------------------------------------------------------------------
212 -- Get the defined balance id given the balance name and database item
213 -- suffix.
214 ----------------------------------------------------------------------------
215 function get_defined_balance (p_balance_name varchar2,
216 p_dimension_suffix varchar2) return number is
217 l_defined_balance_id number;
218 --
219 begin
220 hr_utility.set_location('pay_us_tax_bals_pkg.get_defined_balance', 10);
221 --
222
223 SELECT creator_id
224 INTO l_defined_balance_id
225 FROM ff_user_entities
226 WHERE user_entity_name like
227 translate(p_balance_name||'_'||p_dimension_suffix,' ','_')
228 AND legislation_code = 'US';
229 --
230 hr_utility.set_location('pay_us_tax_bals_pkg.get_defined_balance', 20);
231 return l_defined_balance_id;
232 end;
233 --
234 ------------------------------------------------------------------------------
235 -- This ensures that the assignment is on a payroll on the effective date,
236 -- if not a valid date is found. If no valid date can be found an error is
237 -- raised.
238 ------------------------------------------------------------------------------
239 function get_virtual_date (p_assignment_id number,
240 p_virtual_date date,
241 p_balance_time varchar2,
242 p_asg_vdate_cursor varchar2) return date is
243 l_dummy varchar2(1);
244 l_virtual_date date;
245 l_virtual_date2 date;
246 l_res_date date;
247 begin
248 begin
249 --
250 -- Is the assignment on a payroll.
251 --
252 hr_utility.set_location('pay_us_tax_bals_pkg.get_virtual_date', 10);
253 select ''
254 into l_dummy
255 from per_assignments_f paf
256 where paf.assignment_id = p_assignment_id
257 and p_virtual_date between paf.effective_start_date
258 and paf.effective_end_date
259 and paf.payroll_id is not null;
260
261 --
262 hr_utility.set_location('pay_us_tax_bals_pkg.get_virtual_date', 20);
263 return p_virtual_date;
264 exception
265 when no_data_found then
266 --
267 -- Find a valid date for the assignment.
268 --
269 declare
270 sql_cursor number;
271 l_rows number;
272 begin
273 hr_utility.set_location('pay_us_tax_bals_pkg.get_virtual_date',
274 30);
275 sql_cursor := dbms_sql.open_cursor;
276 dbms_sql.parse(sql_cursor, p_asg_vdate_cursor, dbms_sql.v7);
277 dbms_sql.bind_variable (sql_cursor, 'ASSIGNMENT_ID',
278 p_assignment_id);
279 dbms_sql.bind_variable (sql_cursor, 'DATE_EARNED',
280 p_virtual_date);
281 dbms_sql.bind_variable (sql_cursor, 'DATE2_EARNED',
282 p_virtual_date);
283 dbms_sql.define_column (sql_cursor, 1, l_virtual_date);
284 l_rows := dbms_sql.execute(sql_cursor);
285 l_rows := dbms_sql.fetch_rows (sql_cursor);
286 if l_rows > 0 then
287 hr_utility.set_location(
288 'pay_us_tax_bals_pkg.get_virtual_date', 40);
289 dbms_sql.column_value (sql_cursor, 1, l_virtual_date);
290 --
291 select max(ppf.effective_end_date)
292 into l_virtual_date2
293 from per_assignments_f paf,
294 pay_payrolls_f ppf
295 where paf.assignment_id = p_assignment_id
296 and paf.payroll_id = ppf.payroll_id
297 and ppf.effective_end_date between
298 trunc(p_virtual_date, p_balance_time)
299 and p_virtual_date;
300 --
301 -- Now work out which date is needed
302 --
303 if l_virtual_date is null then
304 if l_virtual_date2 is null then
305 hr_utility.set_location(
306 'pay_us_tax_bals_pkg.get_virtual_date', 60);
307 local_error('get_virtual_date', 2);
308 else
309 hr_utility.set_location(
310 'pay_us_tax_bals_pkg.get_virtual_date', 70);
311 l_res_date := l_virtual_date2;
312 end if;
313 else
314 if l_virtual_date2 is null then
315 hr_utility.set_location(
316 'pay_us_tax_bals_pkg.get_virtual_date', 80);
317 l_res_date := l_virtual_date;
318 else
319 hr_utility.set_location(
320 'pay_us_tax_bals_pkg.get_virtual_date', 90);
321 l_res_date := least(l_virtual_date, l_virtual_date2);
322 end if;
323 end if;
324 --
325 else
326 hr_utility.set_location(
327 'pay_us_tax_bals_pkg.get_virtual_date', 50);
328 local_error('get_virtual_date', 1);
329 end if;
330 --
331 dbms_sql.close_cursor(sql_cursor);
332 end;
333 --
334 return l_res_date;
335 end;
336 end;
337 --
338 --
339 ------------------------------------------------------------------------------
340 -- Get the balance value of a group level balance given the assignment id.
341 ------------------------------------------------------------------------------
342 function get_grp_asg_value (p_assignment_id number,
343 p_virtual_date date,
344 p_balance_name varchar2,
345 p_database_suffix varchar2,
346 p_gre_id number,
347 p_jurisdiction_code varchar2,
348 p_asg_lock varchar2)
349 return number is
350 l_dummy varchar2(5);
351 l_lat_balances boolean;
352 l_asg_data_suffix varchar2(80);
353 l_asg_data_cursor varchar2(2000);
354 l_asg_vdate_cursor varchar2(2000);
355 l_asg_balance_time varchar2(10);
356 l_asg_jd_required boolean;
357 l_grp_lat_exist boolean;
358 l_defined_balance_id number;
359 sql_cursor number;
360 l_rows number;
361 l_asg_id number;
362 l_balance_value number;
363 cnt number;
364 l_virtual_date date;
365 l_balance_type_id number;
366 l_run_route varchar2(5);
367 l_run_route_bool boolean;
368
369 begin
370
371 -- check for the 'RUN_ROUTE' parameter_name in the pay_action_parameters
372 -- table to determine if we want to call the run_result route instead of
373 -- the run_balance route.
374 begin
375
376 select parameter_value
377 into l_run_route
378 from PAY_ACTION_PARAMETERS
379 where parameter_name = 'RUN_ROUTE';
380
381 exception
382 WHEN others then
383 l_run_route := 'FALSE';
384 end;
385
386 IF l_run_route <> 'TRUE' THEN
387 l_run_route_bool := false;
388 ELSE
389 l_run_route_bool := true;
390 END IF;
391
392 l_balance_value := 0;
393 hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_asg_value', 10);
394 --
395 -- Get the assignment level version.
396 --
397 get_asg_for_grp_lvl(p_database_suffix,
398 l_asg_data_suffix,
399 l_asg_data_cursor,
400 l_asg_jd_required,
401 l_asg_vdate_cursor,
402 l_asg_balance_time,
403 l_grp_lat_exist);
404 --
405 if l_grp_lat_exist then
406 --
407 -- Are there latest balances available.
408 --
409 hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_asg_value', 20);
410 l_defined_balance_id := get_defined_balance(p_balance_name,
411 l_asg_data_suffix);
412 --
413 begin
414 select ''
415 into l_dummy
416 from dual
417 where exists (
418 select ''
419 from pay_payroll_actions ppa,
420 pay_assignment_actions paa,
421 pay_assignment_latest_balances palb
422 where palb.assignment_id = p_assignment_id
423 and palb.defined_balance_id = l_defined_balance_id
424 and palb.assignment_action_id =
425 paa.assignment_action_id
426 and paa.payroll_action_id = ppa.payroll_action_id
427 and ppa.action_type in ('R','Q','I','B','V')
428 and p_virtual_date >= ppa.effective_date);
429 --
430 l_lat_balances := TRUE;
431 --
432 exception
433 when no_data_found then
434 l_lat_balances := FALSE;
435 end;
436 --
437 if (l_lat_balances = TRUE) then
438 --
439 -- OK, we can sum the values of the assignment balances to get the
440 -- group balance.
441 --
442 hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_asg_value', 30);
443 begin
444 --
445 sql_cursor := dbms_sql.open_cursor;
446 dbms_sql.parse(sql_cursor, l_asg_data_cursor, dbms_sql.v7);
447 dbms_sql.bind_variable (sql_cursor, 'TAX_UNIT_ID', p_gre_id);
448 dbms_sql.bind_variable (sql_cursor, 'DATE_EARNED', p_virtual_date);
449 dbms_sql.bind_variable (sql_cursor, 'DATE2_EARNED', p_virtual_date);
450 dbms_sql.define_column (sql_cursor, 1, l_asg_id);
451 --
452 -- Does the cursor require the jurisdiction code. Hence balance
453 -- type.
454 --
455 if l_asg_jd_required then
456 select balance_type_id
457 into l_balance_type_id
458 from pay_defined_balances
459 where defined_balance_id = l_defined_balance_id;
460 --
461 dbms_sql.bind_variable (sql_cursor, 'BALANCE_TYPE_ID',
462 l_balance_type_id);
463 dbms_sql.bind_variable (sql_cursor, 'JURISDICTION_CODE',
464 p_jurisdiction_code);
465 end if;
466 l_rows := dbms_sql.execute(sql_cursor);
467 l_rows := 1;
468 cnt := 0;
469 --
470 -- Loop through all the contributing assignments, go get there
471 -- balance value and add onto the running total.
472 --
473 while (l_rows <> 0) loop
474 l_rows := dbms_sql.fetch_rows (sql_cursor);
475 cnt := cnt + 1;
476 if l_rows > 0 then
477 hr_utility.set_location(
478 'pay_us_tax_bals_pkg.get_grp_asg_value', 40);
479 dbms_sql.column_value (sql_cursor, 1, l_asg_id);
480 --
481 l_virtual_date := get_virtual_date(l_asg_id, p_virtual_date,
482 l_asg_balance_time,
483 l_asg_vdate_cursor);
484 --
485 l_balance_value := l_balance_value +
486 pay_balance_pkg.get_value_lock
487 (l_defined_balance_id,
488 l_asg_id,
489 l_virtual_date,
490 l_run_route_bool,
491 p_asg_lock
492 );
493 end if;
494 end loop;
495 --
496 dbms_sql.close_cursor(sql_cursor);
497 end;
498 else
499 --
500 -- No latets balances available. Run the route.
501 --
502 hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_asg_value', 50);
503 l_defined_balance_id := get_defined_balance(p_balance_name,
504 p_database_suffix);
505 l_balance_value := pay_balance_pkg.get_value_lock
506 (l_defined_balance_id,
507 p_assignment_id,
508 p_virtual_date,
509 l_run_route_bool,
510 p_asg_lock
511 );
512 end if;
513 else
514 --
515 -- Can not sum the assignment level balances, thus run group
516 -- level route.
517 --
518 hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_asg_value', 60);
519 l_defined_balance_id := get_defined_balance(p_balance_name,
520 p_database_suffix);
521 l_balance_value := pay_balance_pkg.get_value_lock
522 (l_defined_balance_id,
523 p_assignment_id,
524 p_virtual_date,
525 l_run_route_bool,
526 p_asg_lock
527 );
528 end if;
529 --
530 hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_asg_value', 70);
531 return l_balance_value;
532 --
533 end;
534 --
535 ------------------------------------------------------------------------------
536 -- Get the balance value of a group level balance given the assignment action
537 -- id.
538 ------------------------------------------------------------------------------
539 function get_grp_act_value (p_assignment_action_id number,
540 p_virtual_date date,
541 p_balance_name varchar2,
542 p_database_suffix varchar2,
543 p_gre_id number)
544 return number is
545 l_defined_balance_id number;
546 l_balance_value number;
547 l_run_route varchar2(5);
548 l_run_route_bool boolean;
549
550 begin
551
552 -- check for the 'RUN_ROUTE' parameter_name in the pay_action_parameters
553 -- table to determine if we want to call the run_result route instead of
554 -- the run_balance route.
555 begin
556
557 select parameter_value
558 into l_run_route
559 from PAY_ACTION_PARAMETERS
560 where parameter_name = 'RUN_ROUTE';
561
562 exception
563 WHEN others then
564 l_run_route := 'FALSE';
565 end;
566
567 IF l_run_route <> 'TRUE' THEN
568 l_run_route_bool := false;
569 ELSE
570 l_run_route_bool := true;
571 END IF;
572
573 l_balance_value := 0;
574 hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_act_value', 10);
575 --
576 l_defined_balance_id := get_defined_balance(p_balance_name,
577 p_database_suffix);
578 l_balance_value := pay_balance_pkg.get_value (l_defined_balance_id,
579 p_assignment_action_id,
580 l_run_route_bool,
581 FALSE
582 );
583 --
584 hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_act_value', 20);
585 return l_balance_value;
586 --
587 end;
588 --
589 -------------------------------------------------------------------------------
590 -- Get the value of the group level balance.
591 -------------------------------------------------------------------------------
592 function get_grp_value (p_assignment_id number,
593 p_virtual_date date,
594 p_balance_name varchar2,
595 p_database_suffix varchar2,
596 p_gre_id number,
597 p_jurisdiction_code varchar2,
598 p_assignment_action_id number default null,
599 p_asg_lock varchar2)
600 return number is
601 begin
602 hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_value', 10);
603 if p_assignment_action_id is null then
604 hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_value', 20);
605 return get_grp_asg_value(p_assignment_id,
606 p_virtual_date,
607 p_balance_name,
608 p_database_suffix,
609 p_gre_id,
610 p_jurisdiction_code,
611 p_asg_lock);
612 else
613 hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_value', 30);
614 return get_grp_act_value(p_assignment_action_id,
615 p_virtual_date,
616 p_balance_name,
617 p_database_suffix,
618 p_gre_id);
619 end if;
620 end;
621 -------------------------------------------------------------------------------
622 --
623 -- Wrapper around the core bal user exit
624 --
625 -------------------------------------------------------------------------------
626 FUNCTION call_balance_user_exit
627 (p_balance_name varchar2,
628 p_dimension_suffix varchar2,
629 p_assignment_action_id number DEFAULT NULL,
630 p_assignment_id number DEFAULT NULL,
631 p_virtual_date date DEFAULT NULL,
632 p_asg_type varchar2 DEFAULT NULL,
633 p_gre_id number DEFAULT NULL,
634 p_jurisdiction_code varchar2 DEFAULT NULL,
635 p_asg_lock varchar2 DEFAULT 'Y')
636 RETURN number IS
637 --
638 l_defined_balance_id number;
639 l_balance_type_id number;
640 l_dimension_id number;
641 l_session VARCHAR2(15);
642 l_run_route varchar2(5);
643 l_run_route_bool boolean;
644 --
645 BEGIN
646 --
647
648 hr_utility.set_location('pay_us_tax_bals_pkg.balance_name'||p_balance_name, 9);
649 hr_utility.trace('p_dimension_suffix = '||p_dimension_suffix);
650 hr_utility.trace('p_balance_name = '||p_balance_name);
651 hr_utility.trace('p_asg_type = '||p_asg_type);
652 hr_utility.set_location('pay_us_tax_bals_pkg.call_balance_user_exit', 10);
653
654 -- check for the 'RUN_ROUTE' parameter_name in the pay_action_parameters
655 -- table to determine if we want to call the run_result route instead of
656 -- the run_balance route.
657 begin
658
659 select parameter_value
660 into l_run_route
661 from PAY_ACTION_PARAMETERS
662 where parameter_name = 'RUN_ROUTE';
663
664 exception
665 WHEN others then
666 l_run_route := 'FALSE';
667 end;
668
669 IF l_run_route <> 'TRUE' THEN
670 l_run_route_bool := false;
671 ELSE
672 l_run_route_bool := true;
673 END IF;
674
675 IF p_assignment_action_id IS NOT NULL THEN
676 -- If group level balance, call the group level balance code.
677
678 /* commenting of the following code. From now on we will be using
679 the Balance Reporting Arch */
680 /*
681 if p_asg_type = 'GRE' then
682 hr_utility.set_location('pay_us_tax_bals_pkg.call_balance_user_exit',
683 20);
684 return get_grp_value(p_assignment_id,
685 p_virtual_date,
686 p_balance_name,
687 p_dimension_suffix,
688 p_gre_id,
689 p_jurisdiction_code,
690 p_assignment_action_id,
691 p_asg_lock
692 );
693 else
694 */
695 l_defined_balance_id := get_defined_balance(p_balance_name,
696 p_dimension_suffix);
697 IF p_dimension_suffix not like '%PAY%' THEN
698 hr_utility.set_location('pay_us_tax_bals_pkg.call_balance_user_exit', 30);
699 return pay_balance_pkg.get_value (l_defined_balance_id,
700 p_assignment_action_id,
701 l_run_route_bool,
702 FALSE
703 );
704 ELSE /* If payments dimension then must execute DB item 395029 */
705 hr_utility.set_location('pay_us_tax_bals_pkg.call_balance_user_exit', 40);
706 return pay_balance_pkg.get_value (l_defined_balance_id,
707 p_assignment_action_id,
708 true );
709 -- END IF;
710 end if;
711 --
712 ELSE
713 -- If group level balance, call the group level balance code.
714 /* commenting of the following code. From now on we will be using
715 the Balance Reporting Arch */
716 /*
717 if p_asg_type = 'GRE' then
718 hr_utility.set_location('pay_us_tax_bals_pkg.call_balance_user_exit',
719 50);
720 return get_grp_value(p_assignment_id,
721 p_virtual_date,
722 p_balance_name,
723 p_dimension_suffix,
724 p_gre_id,
725 p_jurisdiction_code,
726 null,
727 p_asg_lock
728 );
729 else
730 */
731 l_defined_balance_id := get_defined_balance(p_balance_name,
732 p_dimension_suffix);
733 hr_utility.set_location('pay_us_tax_bals_pkg.call_balance_user_exit',
734 60);
735 return pay_balance_pkg.get_value_lock
736 (l_defined_balance_id,
737 p_assignment_id,
738 p_virtual_date,
739 l_run_route_bool,
740 p_asg_lock
741 );
742 -- end if;
743 END IF;
744 --
745 END call_balance_user_exit;
746 --
747 -------------------------------------------------------------------------------
748 --
749 -- An overloaded version without the payroll_action_id param to prevent calls
750 -- from forms from breaking
751 --
752 -------------------------------------------------------------------------------
753 FUNCTION us_tax_balance_rep (p_asg_lock in boolean DEFAULT TRUE,
754 p_tax_balance_category in varchar2,
755 p_tax_type in varchar2,
756 p_ee_or_er in varchar2,
757 p_time_type in varchar2,
758 p_asg_type in varchar2,
759 p_gre_id_context in number,
760 p_jd_context in varchar2 DEFAULT NULL,
761 p_assignment_action_id in number DEFAULT NULL,
762 p_assignment_id in number DEFAULT NULL,
763 p_virtual_date in date DEFAULT NULL,
764 p_payroll_action_id in number)
765 RETURN number IS
766 --
767 BEGIN
768 --
769 return us_tax_balance(p_tax_balance_category => p_tax_balance_category,
770 p_tax_type => p_tax_type,
771 p_ee_or_er => p_ee_or_er,
772 p_time_type => p_time_type,
773 p_asg_type => p_asg_type,
774 p_gre_id_context => p_gre_id_context,
775 p_jd_context => p_jd_context,
776 p_assignment_action_id => p_assignment_action_id,
777 p_assignment_id => p_assignment_id,
778 p_virtual_date => p_virtual_date,
779 p_payroll_action_id => p_payroll_action_id,
780 p_asg_lock => p_asg_lock);
781 --
782 END us_tax_balance_rep;
783 --
784 FUNCTION us_tax_balance_rep (p_asg_lock in boolean DEFAULT TRUE,
785 p_tax_balance_category in varchar2,
786 p_tax_type in varchar2,
787 p_ee_or_er in varchar2,
788 p_time_type in varchar2,
789 p_asg_type in varchar2,
790 p_gre_id_context in number,
791 p_jd_context in varchar2 DEFAULT NULL,
792 p_assignment_action_id in number DEFAULT NULL,
793 p_assignment_id in number DEFAULT NULL,
794 p_virtual_date in date DEFAULT NULL
795 )
796 RETURN number IS
797 --
798 BEGIN
799 --
800 return us_tax_balance(p_tax_balance_category => p_tax_balance_category,
801 p_tax_type => p_tax_type,
802 p_ee_or_er => p_ee_or_er,
803 p_time_type => p_time_type,
804 p_asg_type => p_asg_type,
805 p_gre_id_context => p_gre_id_context,
806 p_jd_context => p_jd_context,
807 p_assignment_action_id => p_assignment_action_id,
808 p_assignment_id => p_assignment_id,
809 p_virtual_date => p_virtual_date,
810 p_payroll_action_id => NULL,
811 p_asg_lock => p_asg_lock);
812 --
813 END us_tax_balance_rep;
814 --
815 FUNCTION us_tax_balance (p_tax_balance_category in varchar2,
816 p_tax_type in varchar2,
817 p_ee_or_er in varchar2,
818 p_time_type in varchar2,
819 p_asg_type in varchar2,
820 p_gre_id_context in number,
821 p_jd_context in varchar2 DEFAULT NULL,
822 p_assignment_action_id in number DEFAULT NULL,
823 p_assignment_id in number DEFAULT NULL,
824 p_virtual_date in date DEFAULT NULL)
825 RETURN number IS
826 --
827 BEGIN
828 --
829 return us_tax_balance(p_tax_balance_category => p_tax_balance_category,
830 p_tax_type => p_tax_type,
831 p_ee_or_er => p_ee_or_er,
832 p_time_type => p_time_type,
833 p_asg_type => p_asg_type,
834 p_gre_id_context => p_gre_id_context,
835 p_jd_context => p_jd_context,
836 p_assignment_action_id => p_assignment_action_id,
837 p_assignment_id => p_assignment_id,
838 p_virtual_date => p_virtual_date,
839 p_payroll_action_id => NULL,
840 p_asg_lock => TRUE);
841 --
842 END us_tax_balance;
843 --
844 FUNCTION us_tax_balance (p_tax_balance_category in varchar2,
845 p_tax_type in varchar2,
846 p_ee_or_er in varchar2,
847 p_time_type in varchar2,
848 p_asg_type in varchar2,
849 p_gre_id_context in number,
850 p_jd_context in varchar2 DEFAULT NULL,
851 p_assignment_action_id in number DEFAULT NULL,
852 p_assignment_id in number DEFAULT NULL,
853 p_virtual_date in date DEFAULT NULL,
854 p_payroll_action_id in number)
855 RETURN number IS
856 --
857 BEGIN
858 --
859 return us_tax_balance(p_tax_balance_category => p_tax_balance_category,
860 p_tax_type => p_tax_type,
861 p_ee_or_er => p_ee_or_er,
862 p_time_type => p_time_type,
863 p_asg_type => p_asg_type,
864 p_gre_id_context => p_gre_id_context,
865 p_jd_context => p_jd_context,
866 p_assignment_action_id => p_assignment_action_id,
867 p_assignment_id => p_assignment_id,
868 p_virtual_date => p_virtual_date,
869 p_payroll_action_id => p_payroll_action_id,
870 p_asg_lock => TRUE);
871 --
872 END us_tax_balance;
873 --
874 -------------------------------------------------------------------------------
875 --
876 --
877 --
878 --
879 -------------------------------------------------------------------------------
880 FUNCTION us_tax_balance (p_tax_balance_category in varchar2,
881 p_tax_type in varchar2,
882 p_ee_or_er in varchar2,
883 p_time_type in varchar2,
884 p_asg_type in varchar2,
885 p_gre_id_context in number,
886 p_jd_context in varchar2 DEFAULT NULL,
887 p_assignment_action_id in number DEFAULT NULL,
888 p_assignment_id in number DEFAULT NULL,
889 p_virtual_date in date DEFAULT NULL,
890 p_payroll_action_id in number,
891 p_asg_lock in boolean)
892 RETURN number IS
893 --
894 -- 337641 - cursor rather than ful blown select
895 -- doing group function (min)
896 --
897 CURSOR get_pay_action_id IS
898 select assignment_action_id
899 from pay_assignment_actions
900 where payroll_action_id = p_payroll_action_id;
901
902 /* we need to get the max assignment_action_id for the core
903 balance package. from the max aaid they find the business group
904 id to see if the balances are valid for that business group only. */
905
906 /*
907 CURSOR c_get_min_aaid(p_start_date date , p_end_date date) is
908 select nvl(min(assignment_action_id),-1)
909 from pay_assignment_actions paa,pay_payroll_actions ppa
910 where paa.tax_unit_id = p_gre_id_context
911 and ppa.payroll_action_id = paa.payroll_action_id
912 and ppa.effective_date between p_start_date and p_end_date
913 and ppa.action_type in ('R','Q','I','B','V') ;
914 */
915
916 CURSOR c_get_min_aaid(p_start_date date , p_end_date date,p_bg_id number) is
917 select nvl(min(assignment_action_id),-1)
918 from pay_assignment_actions paa,pay_payroll_actions ppa,pay_payrolls_f ppf
919 where ppa.business_group_id +0 = p_bg_id
920 and ppa.payroll_action_id = paa.payroll_action_id
921 and ppa.effective_date between p_start_date and p_end_date
922 and ppa.action_type in ('R','Q','I','B','V')
923 and ppf.payroll_id = ppa.payroll_id
924 and ppa.business_group_id +0 = ppf.business_group_id;
925
926 CURSOR c_get_bg_id is
927 select business_group_id
928 from hr_organization_units
929 where organization_id = p_gre_id_context;
930
931 CURSOR get_year (p_assign_action_id NUMBER) IS
932 SELECT TO_CHAR(effective_date,'YYYY')
933 FROM pay_assignment_actions paa,pay_payroll_actions ppa
934 WHERE ppa.payroll_action_id = paa.payroll_action_id
935 AND paa.assignment_action_id = p_assign_action_id;
936
937 --
938 l_return_value number;
939 l_test number;
940 l_tax_balance_category varchar2(30);
941 l_tax_type varchar2(30);
942 l_ee_or_er varchar2(5);
943 l_dimension_string varchar2(80);
944 l_jd_dimension_string varchar2(80);
945 l_assignment_id number;
946 l_assignment_action_id number;
947 l_asg_exists number;
948 l_max_date date;
949 l_bal_start_date date;
950 l_virtual_date date;
951 l_valid number;
952 l_asg_lock varchar2(2);
953 l_non_w2_cat varchar2(60);
954
955 l_group_rb_report varchar2(50);
956 l_grp_aaid varchar2(50);
957 l_session_aaid number;
958 l_group_rb_sdate date;
959 l_group_rb_edate date;
960 l_temp_bg_id number;
961 l_direct_fed_bal_call varchar2(2) := 'N'; /*Added for Bug#6696348*/
962 /* Added for Bug#14385437 */
963 l_year varchar2(4);
964 l_direct_bal_year varchar2(4);
965 /* End of changes for Bug#14385437 */
966 l_tax_tag varchar2(20);/*Added for Bug#6696348*/
967
968 l_end_of_time date default to_date('31-12-4712','DD-MM-YYYY');
969 --
970 BEGIN
971 --
972 -- Set the locking flag.
973 --
974 l_asg_lock := 'N';
975 if (p_asg_lock) then
976 l_asg_lock := 'Y';
977 end if;
978 /*Changes for Bug#6696348*/
979 --
980 --Check if Direct Balances needs to be used for Federal Balances
981 --
982
983 /* Added for Bug#14385437 */
984
985 l_direct_fed_bal_call := nvl(fnd_profile.value('PAY_DIRECT_US_FEDERAL_BALANCES'),'N');
986
987 IF l_direct_fed_bal_call = 'Y' THEN
988
989 IF p_assignment_action_id IS NOT NULL THEN
990
991 OPEN get_year(p_assignment_action_id);
992 FETCH get_year INTO l_year;
993 CLOSE get_year;
994
995 hr_utility.trace('Year from Assignment Action ID: '||l_year);
996
997 ELSE
998
999 l_year := TO_CHAR(p_virtual_date,'YYYY');
1000
1001 hr_utility.trace('Year from Virtual Date: '||l_year);
1002
1003 END IF;
1004
1005 l_direct_bal_year := NVL(fnd_profile.value('PAY_US_DIRECT_BALANCE_START_YEAR'),'0001');
1006
1007 IF l_direct_bal_year > l_year THEN
1008
1009 l_direct_fed_bal_call := 'N';
1010
1011 ELSE
1012
1013 hr_utility.trace('US Federal Balances will be fetched from Direct Values');
1014
1015 END IF;
1016
1017 END IF;
1018 /* End of changes for Bug#14385437 */
1019 /*End Bug#6696348*/
1020 --
1021 -- Check that inputs based on lookups are valid
1022 --
1023 if p_tax_balance_category <> 'NONE' then
1024
1025 SELECT count(0)
1026 INTO l_valid
1027 FROM hr_lookups
1028 WHERE lookup_type = 'US_TAX_BALANCE_CATEGORY'
1029 AND lookup_code = p_tax_balance_category;
1030 --
1031 IF l_valid = 0 THEN
1032 hr_utility.trace('Error: Invalid tax balance category');
1033 local_error('us_tax_balance',1);
1034 END IF;
1035 --
1036 SELECT count(0)
1037 INTO l_valid
1038 FROM hr_lookups
1039 WHERE lookup_type = 'US_TAX_TYPE'
1040 AND lookup_code = p_tax_type;
1041 --
1042 IF l_valid = 0 THEN
1043 IF instr(l_tax_type,'PSD') = 0 THEN -- #11926304 Added If to support PSD tax
1044 hr_utility.trace('Error: Invalid tax type');
1045 local_error('us_tax_balance',2);
1046 END IF; -- #11926304
1047 END IF;
1048 --
1049 end if; /* p_tax_balance_category is NONE */
1050
1051 SELECT count(0)
1052 INTO l_valid
1053 FROM dual
1054 WHERE p_asg_type in ('ASG','PER','GRE');
1055 --
1056 IF l_valid = 0 THEN
1057 hr_utility.trace('Error: Invalid asg_type parameter');
1058 local_error('us_tax_balance',3);
1059 END IF;
1060 --
1061 SELECT count(0)
1062 INTO l_valid
1063 FROM dual
1064 WHERE p_time_type in ('RUN','PTD','MONTH','QTD','YTD', 'PAYMENTS', 'PYDATE');
1065 --
1066 IF l_valid = 0 THEN
1067 hr_utility.trace('Error: Invalid time_type parameter');
1068 local_error('us_tax_balance',4);
1069 END IF;
1070 --
1071 -- Set the contexts used in the bal user exit. Same throughout, so set
1072 -- them up front
1073 --
1074 hr_utility.set_location('pay_tax_bals_pkg',30);
1075 --
1076 pay_balance_pkg.set_context('TAX_UNIT_ID',p_gre_id_context);
1077 IF p_jd_context IS NOT NULL THEN
1078 IF (p_tax_type = 'SCHOOL' and length(p_jd_context) > 11) THEN
1079 pay_balance_pkg.set_context('JURISDICTION_CODE',substr(p_jd_context,1,2)||
1080 '-'||substr(p_jd_context,13,5));
1081 ELSE
1082 pay_balance_pkg.set_context('JURISDICTION_CODE',p_jd_context);
1083 END IF;
1084 END IF;
1085 --
1086 hr_utility.set_location('pay_tax_bals_pkg',40);
1087 --
1088 l_assignment_id := p_assignment_id;
1089
1090 l_group_rb_report := NVL(pay_us_balance_view_pkg.get_session_var('GROUP_RB_REPORT'),'NA');
1091
1092 If l_group_rb_report <> 'NA' then
1093
1094 l_grp_aaid := nvl(pay_us_balance_view_pkg.get_session_var('GRP_AAID'),'NA');
1095
1096 l_group_rb_sdate :=
1097 nvl(pay_us_balance_view_pkg.get_session_var('GROUP_RB_SDATE'),sysdate);
1098 l_group_rb_edate :=
1099 nvl(pay_us_balance_view_pkg.get_session_var('GROUP_RB_EDATE'),l_end_of_time);
1100
1101
1102
1103 if l_grp_aaid = 'NA' then
1104
1105 open c_get_bg_id;
1106 fetch c_get_bg_id into l_temp_bg_id;
1107 close c_get_bg_id;
1108
1109 open c_get_min_aaid(l_group_rb_sdate,l_group_rb_edate,l_temp_bg_id);
1110 fetch c_get_min_aaid into l_session_aaid;
1111 close c_get_min_aaid;
1112
1113 pay_us_balance_view_pkg.set_session_var('GRP_AAID',to_char(l_session_aaid));
1114
1115 l_grp_aaid := to_char(l_session_aaid);
1116
1117 end if;
1118
1119 l_assignment_action_id := to_number(l_grp_aaid);
1120
1121 else
1122 l_assignment_action_id := p_assignment_action_id;
1123
1124 end if;
1125
1126 l_tax_type := p_tax_type;
1127 l_tax_balance_category := p_tax_balance_category;
1128 --
1129 -- Check if assignment exists at l_virtual_date, if using date mode
1130 -- Changed date format to DD-MON-YYYY, bug 612696
1131 l_virtual_date :=fnd_date.canonical_to_date(fnd_date.date_to_canonical(p_virtual_date));
1132 --
1133 hr_utility.set_location('pay_tax_bals_pkg',50);
1134 --
1135 IF (l_assignment_id is not null and l_virtual_date is not null) THEN
1136 --
1137 select count(0)
1138 into l_asg_exists
1139 from per_assignments_f
1140 where assignment_id = l_assignment_id
1141 and l_virtual_date between effective_start_date and effective_end_date;
1142 --
1143 -- if assignment doesn't exist ...
1144 --
1145 hr_utility.set_location('pay_tax_bals_pkg',60);
1146 --
1147 IF l_asg_exists = 0 THEN
1148 --
1149 -- get the termination date ...
1150 --
1151 select max(effective_end_date)
1152 into l_max_date
1153 from per_assignments_f
1154 where assignment_id = l_assignment_id;
1155 --
1156 -- get the date of the start of the time period in question
1157 --
1158 hr_utility.set_location('pay_tax_bals_pkg',70);
1159 --
1160 IF p_time_type = 'QTD' THEN
1161 l_bal_start_date := trunc(l_virtual_date,'Q');
1162 ELSIF p_time_type = 'MONTH' THEN
1163 l_bal_start_date := trunc(l_virtual_date,'MM');
1164 ELSIF p_time_type = 'YTD' THEN
1165 l_bal_start_date := trunc(l_virtual_date,'Y');
1166 ELSIF p_time_type = 'PTD' THEN
1167 select tp.start_date
1168 into l_bal_start_date
1169 from per_time_periods tp,
1170 per_assignments_f asg
1171 where asg.assignment_id = l_assignment_id
1172 and l_max_date between asg.effective_start_date and effective_end_date
1173 and asg.payroll_id = tp.payroll_id
1174 and l_virtual_date between tp.start_date and tp.end_date;
1175 END IF;
1176 --
1177 -- set the virtual date to termination date, or return 0 if terminated
1178 -- before the time period.
1179 --
1180 hr_utility.trace('Assignment was terminated on : ' || l_max_date);
1181 hr_utility.trace('Time period in question begins on : ' ||
1182 l_bal_start_date);
1183 --
1184 IF l_max_date < l_bal_start_date THEN
1185 return 0;
1186 ELSE
1187 l_virtual_date := l_max_date;
1188 END IF;
1189 --
1190 hr_utility.trace('Using new virtual date : ' || l_virtual_date);
1191 --
1192 END IF;
1193 END IF;
1194 --
1195 -- Convert "WITHHELD" to proper balance categories;
1196 --
1197 hr_utility.set_location('pay_tax_bals_pkg',80);
1198 --
1199 IF l_tax_balance_category = 'WITHHELD' THEN
1200 IF p_ee_or_er = 'ER' or l_tax_type = 'FUTA' THEN
1201 l_tax_balance_category := 'LIABILITY';
1202 ELSIF (l_tax_type = 'EIC'
1203 OR l_tax_type = 'STEIC') THEN
1204 l_tax_balance_category := 'ADVANCE';
1205 END IF;
1206 END IF;
1207 IF l_tax_balance_category = 'ADVANCED' THEN
1208 l_tax_balance_category := 'ADVANCE';
1209 END IF;
1210 --
1211 -- Check if illegal tax combo (FIT and TAXABLE, FUTA and SUBJ_NWHABLE, etc.)
1212 --
1213 hr_utility.set_location('pay_tax_bals_pkg',90);
1214 --
1215 IF (l_tax_type = 'FIT' or l_tax_type = 'SIT' or l_tax_type = 'COUNTY' or
1216 l_tax_type = 'CITY' or l_tax_type = 'EIC' or l_tax_type = 'HT' or
1217 l_tax_type = 'SCHOOL' or l_tax_type = 'STEIC' ) THEN -- income tax
1218 IF (l_tax_balance_category = 'TAXABLE' or
1219 l_tax_balance_category = 'EXCESS') THEN
1220 hr_utility.trace('Error: Illegal tax category for tax type');
1221 local_error('us_tax_balance',5);
1222 END IF;
1223 --
1224 -- return 0 for currently unsupported EIC balances.
1225 --
1226 -- 403b, 457 and Pre_Tax was added by skutteti for the pre-tax enhancements
1227 --
1228 IF l_tax_type = 'EIC' and (l_tax_balance_category = 'SUBJ_NWHABLE' -- or
1229 --l_tax_balance_category = '401_REDNS' or
1230 --l_tax_balance_category = '125_REDNS' or
1231 --l_tax_balance_category = '403_REDNS' or
1232 --l_tax_balance_category = '457_REDNS' or
1233 --l_tax_balance_category = 'PRE_TAX_REDNS' or
1234 --l_tax_balance_category = 'DEP_CARE_REDNS'
1235 ) THEN
1236 return 0;
1237 END IF;
1238 ELSE -- limit tax
1239 IF l_tax_balance_category = 'SUBJ_NWHABLE' THEN
1240 return 0;
1241 END IF;
1242 END IF;
1243 --
1244 hr_utility.set_location('pay_tax_bals_pkg',100);
1245 --
1246 l_ee_or_er := ltrim(rtrim(p_ee_or_er));
1247 --
1248 --------------- Some Error Checking -------------
1249 --
1250 --
1251 if (l_tax_type = 'FIT' or l_tax_type = 'SIT' or l_tax_type = 'CITY' or
1252 l_tax_type = 'COUNTY' or l_tax_type = 'EIC' or l_tax_type = 'SCHOOL'
1253 or l_tax_type = 'HT' or l_tax_type = 'WC' or l_tax_type = 'WC2' or
1254 l_tax_type = 'STEIC' or instr(l_tax_type,'PSD') <> 0 ) THEN --#11926304
1255 if l_ee_or_er = 'ER' THEN
1256 hr_utility.trace('Error: ER not allowed for tax type');
1257 local_error('us_tax_balance',6);
1258 else
1259 l_ee_or_er := NULL;
1260 end if;
1261 elsif (l_tax_type = 'FUTA') THEN
1262 if l_ee_or_er = 'EE' THEN
1263 hr_utility.trace('Error: EE not allowed for tax type');
1264 local_error('us_tax_balance',7);
1265 else
1266 l_ee_or_er := NULL;
1267 end if;
1268 elsif (l_tax_type = 'SS' or l_tax_type = 'MEDICARE' or l_tax_type = 'SDI' or
1269 l_tax_type = 'SUI' ) THEN
1270 if (l_ee_or_er <> 'EE' and l_ee_or_er <> 'ER') THEN
1271 hr_utility.trace('Error: EE or ER required for tax type');
1272 local_error('us_tax_balance',8);
1273 end if;
1274 elsif (l_tax_type = 'SUI1') or (l_tax_type = 'SDI1')THEN
1275 if (l_ee_or_er <> 'EE' ) THEN
1276 hr_utility.trace('Error: EE required for tax type');
1277 local_error('us_tax_balance',9);
1278 end if;
1279 end if;
1280
1281 -- As of implementation of the SUI1 EE Tax, we only maintain
1282 -- a WIthheld balance. As the SUI1 tax type should match
1283 -- balances with SUI We will return the SUI balances.
1284
1285 IF (l_tax_type = 'SUI1') and (l_tax_balance_category <> 'WITHHELD'
1286 ) THEN
1287 l_tax_type := 'SUI';
1288 END IF;
1289
1290 IF l_tax_type = 'SDI1' and
1291 (l_tax_balance_category <> 'WITHHELD' AND
1292 l_tax_balance_category <> 'TAXABLE'
1293 ) THEN
1294 return 0;
1295 END IF;
1296
1297 --
1298 hr_utility.set_location('pay_tax_bals_pkg',110);
1299 --
1300 -- Force space at end of this parameter if necessary
1301 --
1302 hr_utility.set_location('pay_tax_bals_pkg',120);
1303 --
1304 IF l_ee_or_er IS NOT NULL THEN
1305 l_ee_or_er := rtrim(l_ee_or_er)||' ';
1306 END IF;
1307 --
1308 -- Set up dimension strings
1309 --
1310 IF p_asg_type <> 'GRE' THEN
1311 l_dimension_string := p_asg_type||'_GRE_'||p_time_type;
1312 l_jd_dimension_string := p_asg_type||'_JD_GRE_'||p_time_type;
1313 ELSE
1314 --
1315 l_dimension_string := 'GRE_'||p_time_type;
1316 l_jd_dimension_string := 'GRE_JD_'||p_time_type;
1317 --
1318 --
1319 --
1320 -- If given payroll action id, get an asg action id from it to use. Else
1321 -- use the assignment_id and virtual date, since the get balance routine
1322 -- will be called in date mode.
1323 --
1324 --
1325 -- bug # gaz
1326 --
1327 IF (p_payroll_action_id is not null) THEN
1328 begin
1329 OPEN get_pay_action_id;
1330 FETCH get_pay_action_id INTO l_assignment_action_id;
1331 CLOSE get_pay_action_id;
1332 end;
1333 else
1334 if (p_assignment_action_id is null) then
1335 --
1336 -- Get a dummy assignment id to call the balance user exit in date mode.
1337 --
1338 declare
1339 l_bg_id number;
1340 l_count number;
1341 l_found boolean;
1342 check_asg number;
1343 begin
1344 pay_balance_pkg.set_context('DATE_EARNED',
1345 fnd_date.date_to_canonical(l_virtual_date));
1346 pay_balance_pkg.set_context('BALANCE_DATE',
1347 fnd_date.date_to_canonical(l_virtual_date));
1348 select business_group_id
1349 into l_bg_id
1350 from hr_organization_units
1351 where organization_id = p_gre_id_context;
1352 --
1353 -- Look to see if theres an assignment in the cache for
1354 -- this business group
1355 --
1356 l_count := 0;
1357 l_found := FALSE;
1358 while ((l_count < g_nxt_free_asgid) AND (l_found = FALSE)) loop
1359 if (l_bg_id = g_asgid_tbl_bgid(l_count)) then
1360 --
1361 -- OK, now check that the assignment is valid as of the
1362 -- virtual date.
1363 --
1364 begin
1365 select 1
1366 into check_asg
1367 from per_assignments_f paf
1368 where paf.assignment_id = g_asgid_tbl_id(l_count)
1369 and l_virtual_date between paf.effective_start_date
1370 and paf.effective_end_date;
1371 --
1372 l_assignment_id := g_asgid_tbl_id(l_count);
1373 l_found := TRUE;
1374 --
1375 exception
1376 when no_data_found then null;
1377 end;
1378 end if; ---- (l_bg_id = g_asgid_tbl_bgid(l_count))
1379 l_count := l_count + 1;
1380 end loop;
1381 --
1382 if (l_found = FALSE) then
1383 --
1384 -- OK, need to get an assignment from the database.
1385 --
1386 begin
1387 /* Modified query for performance enhancement (Bug 3343974). */
1388 select min(paa.assignment_id)
1389 into l_assignment_id
1390 from pay_assignment_actions paa,
1391 pay_payroll_actions pact,
1392 pay_payrolls_f ppf
1393 where pact.effective_date <= l_virtual_date
1394 and pact.payroll_action_id=paa.payroll_action_id
1395 and pact.action_type in ('R', 'Q', 'I', 'V', 'B')
1396 and paa.tax_unit_id = p_gre_id_context
1397 and ppf.payroll_id = pact.payroll_id
1398 and ppf.business_group_id = l_bg_id;
1399
1400 --
1401 -- Place the defined balance in cache.
1402 --
1403 g_asgid_tbl_bgid(g_nxt_free_asgid) := ltrim(rtrim(l_bg_id));
1404 g_asgid_tbl_id (g_nxt_free_asgid) :=
1405 ltrim(rtrim(l_assignment_id));
1406 g_nxt_free_asgid := g_nxt_free_asgid + 1;
1407 --
1408 exception when no_data_found then
1409 begin
1410 hr_utility.trace('Error: Failure to find defined balance');
1411 local_error('us_tax_balance',1);
1412 --
1413 end;
1414 end;
1415 end if; ---- (l_found = FALSE)
1416 end;
1417 end if; ---- (p_assignment_action_id is null)
1418 END IF; ---- (p_payroll_action_id is not null)
1419 END IF;
1420 --
1421 IF p_time_type = 'PAYMENTS' THEN
1422 --
1423 -- 360669 put PAYMENTS_JD back
1424 --
1425 l_jd_dimension_string := p_time_type||'_JD';
1426 l_dimension_string := p_time_type;
1427 --
1428 END IF;
1429 --
1430 --
1431 -- Check if the tax is federal or not.
1432 --
1433 SELECT count(0)
1434 INTO l_test
1435 FROM sys.dual
1436 WHERE l_tax_type in ('FIT','FUTA','MEDICARE','SS','EIC');
1437
1438
1439 IF ((p_jd_context IS NOT NULL) and
1440 (substr(p_jd_context,1,2) <> '00')) THEN
1441
1442 l_test := 0;
1443
1444 END IF;
1445
1446 --
1447 IF l_test <> 0 THEN -- yes, the tax is federal
1448 --
1449
1450 IF l_direct_fed_bal_call = 'Y' THEN
1451
1452 IF l_tax_type IN ('MEDICARE','SS') THEN
1453
1454 l_tax_tag := l_tax_type||'_'||rtrim(l_ee_or_er);
1455
1456 ELSE
1457
1458 l_tax_tag := l_tax_type;
1459
1460 END IF;
1461
1462 END IF;
1463
1464 IF l_tax_balance_category = 'GROSS' THEN
1465
1466 IF l_direct_fed_bal_call = 'Y' THEN
1467
1468 l_return_value := call_balance_user_exit (l_tax_tag||'_'||l_tax_balance_category,
1469 l_dimension_string,
1470 l_assignment_action_id,
1471 l_assignment_id,
1472 l_virtual_date,
1473 p_asg_type,
1474 p_gre_id_context,
1475 p_jd_context,
1476 l_asg_lock);
1477
1478 ELSE
1479
1480 l_return_value := call_balance_user_exit ('GROSS_EARNINGS',
1481 l_dimension_string,
1482 l_assignment_action_id,
1483 l_assignment_id,
1484 l_virtual_date,
1485 p_asg_type,
1486 p_gre_id_context,
1487 p_jd_context,
1488 l_asg_lock);
1489 --
1490 -- The if condition was added by subbu on 15-sep-2000
1491 --
1492 IF l_tax_type = 'FIT' AND l_return_value > 0 THEN
1493 l_return_value := l_return_value -
1494 call_balance_user_exit ('ALIEN_EXPAT_EARNINGS',
1495 l_dimension_string,
1496 l_assignment_action_id,
1497 l_assignment_id,
1498 l_virtual_date,
1499 p_asg_type,
1500 p_gre_id_context,
1501 p_jd_context,
1502 l_asg_lock) ;
1503 END IF;
1504
1505 END IF;
1506 --
1507 ELSIF l_tax_balance_category = 'SUBJ_WHABLE' THEN
1508
1509 IF l_direct_fed_bal_call = 'Y' THEN
1510
1511 l_return_value := call_balance_user_exit (l_tax_tag||'_'||l_tax_balance_category,
1512 l_dimension_string,
1513 l_assignment_action_id,
1514 l_assignment_id,
1515 l_virtual_date,
1516 p_asg_type,
1517 p_gre_id_context,
1518 p_jd_context,
1519 l_asg_lock);
1520
1521 ELSE
1522
1523 l_return_value := call_balance_user_exit ('REGULAR_EARNINGS',
1524 l_dimension_string,
1525 l_assignment_action_id,
1526 l_assignment_id,
1527 l_virtual_date,
1528 p_asg_type,
1529 p_gre_id_context,
1530 p_jd_context,
1531 l_asg_lock)
1532 + call_balance_user_exit (
1533 'SUPPLEMENTAL_EARNINGS_FOR_'||l_tax_type,
1534 'SUBJECT_TO_TAX_'||l_dimension_string,
1535 l_assignment_action_id,
1536 l_assignment_id,
1537 l_virtual_date,
1538 p_asg_type,
1539 p_gre_id_context,
1540 p_jd_context,
1541 l_asg_lock);
1542
1543 END IF;
1544 --
1545 ELSIF l_tax_balance_category = 'SUBJ_NWHABLE' THEN
1546
1547 IF l_direct_fed_bal_call = 'Y' THEN
1548
1549 l_return_value := call_balance_user_exit (l_tax_tag||'_'||l_tax_balance_category,
1550 l_dimension_string,
1551 l_assignment_action_id,
1552 l_assignment_id,
1553 l_virtual_date,
1554 p_asg_type,
1555 p_gre_id_context,
1556 p_jd_context,
1557 l_asg_lock);
1558
1559 ELSE
1560
1561 l_return_value := call_balance_user_exit (
1562 'SUPPLEMENTAL_EARNINGS_FOR_NW'||l_tax_type,
1563 'SUBJECT_TO_TAX_'||l_dimension_string,
1564 l_assignment_action_id,
1565 l_assignment_id,
1566 l_virtual_date,
1567 p_asg_type,
1568 p_gre_id_context,
1569 p_jd_context,
1570 l_asg_lock);
1571
1572 END IF;
1573 --
1574 ELSIF l_tax_balance_category = '401_REDNS' THEN
1575
1576 IF l_direct_fed_bal_call = 'Y' THEN
1577
1578 l_return_value := call_balance_user_exit (l_tax_tag||'_'||l_tax_balance_category,
1579 l_dimension_string,
1580 l_assignment_action_id,
1581 l_assignment_id,
1582 l_virtual_date,
1583 p_asg_type,
1584 p_gre_id_context,
1585 p_jd_context,
1586 l_asg_lock);
1587
1588 ELSE
1589
1590 l_return_value := call_balance_user_exit ('DEF_COMP_401K',
1591 l_dimension_string,
1592 l_assignment_action_id,
1593 l_assignment_id,
1594 l_virtual_date,
1595 p_asg_type,
1596 p_gre_id_context,
1597 p_jd_context,
1598 l_asg_lock);
1599
1600
1601 --
1602 -- 337641
1603 -- check if balance 0 therefore no need to
1604 -- subtract subsequent balance
1605 --
1606 IF ( l_return_value <> 0 )
1607 THEN
1608 l_return_value := l_return_value
1609 - call_balance_user_exit ('DEF_COMP_401K_FOR_'||l_tax_type,
1610 'SUBJECT_TO_TAX_'||l_dimension_string,
1611 l_assignment_action_id,
1612 l_assignment_id,
1613 l_virtual_date,
1614 p_asg_type,
1615 p_gre_id_context,
1616 p_jd_context,
1617 l_asg_lock);
1618 --
1619 -- added by skutteti in Nov 2000, to remove the Non W2 portion
1620 --
1621 IF l_tax_type = 'FIT' THEN
1622 l_return_value := l_return_value -
1623 call_balance_user_exit(
1624 'FIT_NON_W2_DEF_COMP_401',
1625 l_dimension_string,
1626 l_assignment_action_id,
1627 l_assignment_id,
1628 l_virtual_date,
1629 p_asg_type,
1630 p_gre_id_context,
1631 p_jd_context,
1632 l_asg_lock);
1633 END IF;
1634 END IF;
1635
1636 END IF;
1637 --
1638 -- 403b, 457 and Pre_Tax was added by skutteti for the pre-tax enhancements
1639 --
1640 ELSIF l_tax_balance_category = '403_REDNS' THEN
1641
1642 IF l_direct_fed_bal_call = 'Y' THEN
1643
1644 l_return_value := call_balance_user_exit (
1645 l_tax_tag||'_'||l_tax_balance_category,
1646 l_dimension_string,
1647 l_assignment_action_id,
1648 l_assignment_id,
1649 l_virtual_date,
1650 p_asg_type,
1651 p_gre_id_context,
1652 p_jd_context,
1653 l_asg_lock);
1654
1655
1656 ELSE
1657 l_return_value := call_balance_user_exit (
1658 'DEF_COMP_403B',
1659 l_dimension_string,
1660 l_assignment_action_id,
1661 l_assignment_id,
1662 l_virtual_date,
1663 p_asg_type,
1664 p_gre_id_context,
1665 p_jd_context,
1666 l_asg_lock);
1667 IF ( l_return_value <> 0 )
1668 THEN
1669 l_return_value := l_return_value - call_balance_user_exit (
1670 'DEF_COMP_403B_FOR_'||l_tax_type,
1671 'SUBJECT_TO_TAX_'||l_dimension_string,
1672 l_assignment_action_id,
1673 l_assignment_id,
1674 l_virtual_date,
1675 p_asg_type,
1676 p_gre_id_context,
1677 p_jd_context,
1678 l_asg_lock);
1679 --
1680 -- added by skutteti in Nov 2000, to remove the Non W2 portion
1681 --
1682 IF l_tax_type = 'FIT' THEN
1683 l_return_value := l_return_value -
1684 call_balance_user_exit(
1685 'FIT_NON_W2_DEF_COMP_403',
1686 l_dimension_string,
1687 l_assignment_action_id,
1688 l_assignment_id,
1689 l_virtual_date,
1690 p_asg_type,
1691 p_gre_id_context,
1692 p_jd_context,
1693 l_asg_lock);
1694 END IF;
1695 END IF;
1696
1697 END IF;
1698 --
1699 -- Other Pretax was added by kthirmiy for the pre-tax enhancements
1700 --
1701 ELSIF l_tax_balance_category = 'OTHER_PRETAX_REDNS' THEN
1702
1703 IF l_direct_fed_bal_call = 'Y' THEN
1704
1705 l_return_value := call_balance_user_exit (
1706 l_tax_tag||'_'||l_tax_balance_category,
1707 l_dimension_string,
1708 l_assignment_action_id,
1709 l_assignment_id,
1710 l_virtual_date,
1711 p_asg_type,
1712 p_gre_id_context,
1713 p_jd_context,
1714 l_asg_lock);
1715
1716 ELSE
1717 l_return_value := call_balance_user_exit (
1718 'OTHER_PRETAX',
1719 l_dimension_string,
1720 l_assignment_action_id,
1721 l_assignment_id,
1722 l_virtual_date,
1723 p_asg_type,
1724 p_gre_id_context,
1725 p_jd_context,
1726 l_asg_lock);
1727 IF ( l_return_value <> 0 )
1728 THEN
1729 l_return_value := l_return_value - call_balance_user_exit (
1730 'OTHER_PRETAX_FOR_'||l_tax_type,
1731 'SUBJECT_TO_TAX_'||l_dimension_string,
1732 l_assignment_action_id,
1733 l_assignment_id,
1734 l_virtual_date,
1735 p_asg_type,
1736 p_gre_id_context,
1737 p_jd_context,
1738 l_asg_lock);
1739 --
1740 -- added by skutteti in Nov 2000, to remove the Non W2 portion
1741 --
1742 IF l_tax_type = 'FIT' THEN
1743 l_return_value := l_return_value -
1744 call_balance_user_exit(
1745 'FIT_NON_W2_OTHER_PRETAX',
1746 l_dimension_string,
1747 l_assignment_action_id,
1748 l_assignment_id,
1749 l_virtual_date,
1750 p_asg_type,
1751 p_gre_id_context,
1752 p_jd_context,
1753 l_asg_lock);
1754 END IF;
1755 END IF;
1756
1757 END IF;
1758
1759 ELSIF l_tax_balance_category = '457_REDNS' THEN
1760
1761 IF l_direct_fed_bal_call = 'Y' THEN
1762
1763 l_return_value := call_balance_user_exit (
1764 l_tax_tag||'_'||l_tax_balance_category,
1765 l_dimension_string,
1766 l_assignment_action_id,
1767 l_assignment_id,
1768 l_virtual_date,
1769 p_asg_type,
1770 p_gre_id_context,
1771 p_jd_context,
1772 l_asg_lock);
1773 ELSE
1774
1775 l_return_value := call_balance_user_exit (
1776 'DEF_COMP_457',
1777 l_dimension_string,
1778 l_assignment_action_id,
1779 l_assignment_id,
1780 l_virtual_date,
1781 p_asg_type,
1782 p_gre_id_context,
1783 p_jd_context,
1784 l_asg_lock);
1785
1786 IF ( l_return_value <> 0 )
1787 THEN
1788 l_return_value := l_return_value - call_balance_user_exit (
1789 'DEF_COMP_457_FOR_'||l_tax_type,
1790 'SUBJECT_TO_TAX_'||l_dimension_string,
1791 l_assignment_action_id,
1792 l_assignment_id,
1793 l_virtual_date,
1794 p_asg_type,
1795 p_gre_id_context,
1796 p_jd_context,
1797 l_asg_lock);
1798 --
1799 -- added by skutteti in Nov 2000, to remove the Non W2 portion
1800 --
1801 IF l_tax_type = 'FIT' THEN
1802 l_return_value := l_return_value -
1803 call_balance_user_exit(
1804 'FIT_NON_W2_DEF_COMP_457',
1805 l_dimension_string,
1806 l_assignment_action_id,
1807 l_assignment_id,
1808 l_virtual_date,
1809 p_asg_type,
1810 p_gre_id_context,
1811 p_jd_context,
1812 l_asg_lock);
1813 END IF;
1814 END IF;
1815
1816 END IF;
1817
1818 ELSIF l_tax_balance_category = 'PRE_TAX_REDNS' THEN
1819
1820 IF l_direct_fed_bal_call = 'Y' THEN
1821
1822 l_return_value := call_balance_user_exit (
1823 l_tax_tag||'_'||l_tax_balance_category,
1824 l_dimension_string,
1825 l_assignment_action_id,
1826 l_assignment_id,
1827 l_virtual_date,
1828 p_asg_type,
1829 p_gre_id_context,
1830 p_jd_context,
1831 l_asg_lock);
1832
1833
1834 ELSE
1835
1836 l_return_value := call_balance_user_exit (
1837 'PRE_TAX_DEDUCTIONS',
1838 l_dimension_string,
1839 l_assignment_action_id,
1840 l_assignment_id,
1841 l_virtual_date,
1842 p_asg_type,
1843 p_gre_id_context,
1844 p_jd_context,
1845 l_asg_lock);
1846 IF ( l_return_value <> 0 )
1847 THEN
1848 l_return_value := l_return_value - call_balance_user_exit (
1849 'PRE_TAX_DEDUCTIONS_FOR_'||l_tax_type,
1850 'SUBJECT_TO_TAX_'||l_dimension_string,
1851 l_assignment_action_id,
1852 l_assignment_id,
1853 l_virtual_date,
1854 p_asg_type,
1855 p_gre_id_context,
1856 p_jd_context,
1857 l_asg_lock);
1858 --
1859 -- added by skutteti in Nov 2000, to remove the Non W2 portion
1860 --
1861 IF l_tax_type = 'FIT' THEN
1862 l_return_value := l_return_value -
1863 call_balance_user_exit(
1864 'FIT_NON_W2_PRE_TAX_DEDNS',
1865 l_dimension_string,
1866 l_assignment_action_id,
1867 l_assignment_id,
1868 l_virtual_date,
1869 p_asg_type,
1870 p_gre_id_context,
1871 p_jd_context,
1872 l_asg_lock);
1873 END IF;
1874 END IF;
1875
1876 END IF;
1877 --
1878 ELSIF l_tax_balance_category = '125_REDNS' THEN
1879
1880 IF l_direct_fed_bal_call = 'Y' THEN
1881
1882 l_return_value := call_balance_user_exit (l_tax_tag||'_'||l_tax_balance_category,
1883 l_dimension_string,
1884 l_assignment_action_id,
1885 l_assignment_id,
1886 l_virtual_date,
1887 p_asg_type,
1888 p_gre_id_context,
1889 p_jd_context,
1890 l_asg_lock);
1891
1892 ELSE
1893
1894 l_return_value := call_balance_user_exit ('SECTION_125',
1895 l_dimension_string,
1896 l_assignment_action_id,
1897 l_assignment_id,
1898 l_virtual_date,
1899 p_asg_type,
1900 p_gre_id_context,
1901 p_jd_context,
1902 l_asg_lock);
1903 --
1904 -- 337641
1905 -- check if balance 0 therefore no need to
1906 -- subtract subsequent balance
1907 --
1908 IF ( l_return_value <> 0 )
1909 THEN
1910 l_return_value := l_return_value
1911 - call_balance_user_exit ('SECTION_125_FOR_'||l_tax_type,
1912 'SUBJECT_TO_TAX_'||l_dimension_string,
1913 l_assignment_action_id,
1914 l_assignment_id,
1915 l_virtual_date,
1916 p_asg_type,
1917 p_gre_id_context,
1918 p_jd_context,
1919 l_asg_lock);
1920 --
1921 -- added by skutteti in Nov 2000, to remove the Non W2 portion
1922 --
1923 IF l_tax_type = 'FIT' THEN
1924 l_return_value := l_return_value - call_balance_user_exit(
1925 'FIT_NON_W2_SECTION_125',
1926 l_dimension_string,
1927 l_assignment_action_id,
1928 l_assignment_id,
1929 l_virtual_date,
1930 p_asg_type,
1931 p_gre_id_context,
1932 p_jd_context,
1933 l_asg_lock);
1934 END IF;
1935 END IF;
1936
1937 END IF;
1938 --
1939 ELSIF l_tax_balance_category = 'DEP_CARE_REDNS' THEN
1940
1941 IF l_direct_fed_bal_call = 'Y' THEN
1942
1943 l_return_value := call_balance_user_exit (l_tax_tag||'_'||l_tax_balance_category,
1944 l_dimension_string,
1945 l_assignment_action_id,
1946 l_assignment_id,
1947 l_virtual_date,
1948 p_asg_type,
1949 p_gre_id_context,
1950 p_jd_context,
1951 l_asg_lock);
1952
1953 ELSE
1954
1955 l_return_value := call_balance_user_exit ('DEPENDENT_CARE',
1956 l_dimension_string,
1957 l_assignment_action_id,
1958 l_assignment_id,
1959 l_virtual_date,
1960 p_asg_type,
1961 p_gre_id_context,
1962 p_jd_context,
1963 l_asg_lock);
1964 --
1965 -- 337641
1966 -- check if balance 0 therefore no need to
1967 -- subtract subsequent balance
1968 --
1969 IF ( l_return_value <> 0 )
1970 THEN
1971 l_return_value := l_return_value
1972 - call_balance_user_exit ('DEPENDENT_CARE_FOR_'||l_tax_type,
1973 'SUBJECT_TO_TAX_'||l_dimension_string,
1974 l_assignment_action_id,
1975 l_assignment_id,
1976 l_virtual_date,
1977 p_asg_type,
1978 p_gre_id_context,
1979 p_jd_context,
1980 l_asg_lock);
1981 --
1982 -- added by skutteti in Nov 2000, to remove the Non W2 portion
1983 --
1984 IF l_tax_type = 'FIT' THEN
1985 l_return_value := l_return_value -
1986 call_balance_user_exit(
1987 'FIT_NON_W2_DEPENDENT_CARE',
1988 l_dimension_string,
1989 l_assignment_action_id,
1990 l_assignment_id,
1991 l_virtual_date,
1992 p_asg_type,
1993 p_gre_id_context,
1994 p_jd_context,
1995 l_asg_lock);
1996 END IF;
1997 END IF;
1998
1999 END IF;
2000 --
2001 ELSIF l_tax_balance_category = 'TAXABLE' THEN
2002
2003 hr_utility.trace('balance name sent = '||l_tax_type||'_'||
2004 l_ee_or_er||'TAXABLE');
2005 hr_utility.trace(' l_dimension_string = '||l_dimension_string);
2006
2007 l_return_value := call_balance_user_exit (l_tax_type||'_'||
2008 l_ee_or_er||'TAXABLE',
2009 l_dimension_string,
2010 l_assignment_action_id,
2011 l_assignment_id,
2012 l_virtual_date,
2013 p_asg_type,
2014 p_gre_id_context,
2015 p_jd_context,
2016 l_asg_lock);
2017 --
2018 ELSIF (l_tax_balance_category = 'WITHHELD' or
2019 l_tax_balance_category = 'LIABILITY' or
2020 l_tax_balance_category = 'ADVANCE') THEN
2021 l_return_value := call_balance_user_exit (
2022 l_tax_type||'_'||l_ee_or_er||l_tax_balance_category,
2023 l_dimension_string,
2024 l_assignment_action_id,
2025 l_assignment_id,
2026 l_virtual_date,
2027 p_asg_type,
2028 p_gre_id_context,
2029 p_jd_context,
2030 l_asg_lock);
2031 /*Added for Bug#13025209*/
2032 ELSIF (l_tax_balance_category = 'CREDIT') THEN
2033
2034 IF (l_tax_type = 'FUTA') THEN
2035 l_return_value := call_balance_user_exit (
2036 l_tax_type||'_'||l_tax_balance_category,
2037 l_dimension_string,
2038 l_assignment_action_id,
2039 l_assignment_id,
2040 l_virtual_date,
2041 p_asg_type,
2042 p_gre_id_context,
2043 p_jd_context,
2044 l_asg_lock);
2045 END IF;
2046 /*End of changes for Bug#13025209*/
2047 END IF;
2048 ELSE -- the tax is non-federal
2049 --
2050 -- if the tax balance is not derived, get it here.
2051 IF (l_tax_balance_category <> 'SUBJECT' and
2052 l_tax_balance_category <> 'EXEMPT' and
2053 l_tax_balance_category <> 'EXCESS' and
2054 l_tax_balance_category <> 'REDUCED_SUBJ_WHABLE') THEN
2055 --
2056 -- Use the CITY balances for HT if we don't want to see LIABILITY
2057 --
2058 IF (l_tax_type = 'HT') THEN
2059 IF (l_tax_balance_category <> 'WITHHELD') THEN
2060 l_tax_type := 'CITY';
2061 ELSE
2062 l_tax_type := 'HEAD TAX';
2063 END IF;
2064 END IF;
2065 --
2066 -- Added for workers comp
2067 If (l_tax_type = 'WC' ) THEN
2068 l_tax_type := 'WORKERS COMP';
2069 END IF;
2070 If (l_tax_type = 'WC2') THEN
2071 l_tax_type := 'WORKERS COMP2';
2072 END IF;
2073 --
2074
2075 hr_utility.trace ('The category is : '|| l_tax_balance_category);
2076 if l_tax_balance_category = 'NONE' then
2077
2078 l_return_value := call_balance_user_exit (
2079 l_tax_type,
2080 l_dimension_string,
2081 l_assignment_action_id,
2082 l_assignment_id,
2083 l_virtual_date,
2084 p_asg_type,
2085 p_gre_id_context,
2086 p_jd_context,
2087 l_asg_lock);
2088 else
2089 l_return_value := call_balance_user_exit (
2090 l_tax_type||'_'||l_ee_or_er||l_tax_balance_category,
2091 l_jd_dimension_string,
2092 l_assignment_action_id,
2093 l_assignment_id,
2094 l_virtual_date,
2095 p_asg_type,
2096 p_gre_id_context,
2097 p_jd_context,
2098 l_asg_lock);
2099
2100 end if;
2101
2102 --
2103 -- added by skutteti to remove the non w2 portion for pre tax REDNS
2104 --
2105 /*
2106 IF (l_return_value <> 0 AND
2107 l_tax_type = 'SIT' AND
2108 l_tax_balance_category like '%REDNS' ) THEN
2109 IF l_tax_balance_category = 'PRE_TAX_REDNS' THEN
2110 l_non_w2_cat := 'NON_W2_PRE_TAX_DEDNS';
2111 ELSIF l_tax_balance_category = '401_REDNS' THEN
2112 l_non_w2_cat := 'NON_W2_DEF_COMP_401';
2113 ELSIF l_tax_balance_category = '403_REDNS' THEN
2114 l_non_w2_cat := 'NON_W2_DEF_COMP_403';
2115 ELSIF l_tax_balance_category = '457_REDNS' THEN
2116 l_non_w2_cat := 'NON_W2_DEF_COMP_457';
2117 ELSIF l_tax_balance_category = '125_REDNS' THEN
2118 l_non_w2_cat := 'NON_W2_SECTION_125';
2119 ELSIF l_tax_balance_category = 'DEP_CARE_REDNS' THEN
2120 l_non_w2_cat := 'NON_W2_DEPENDENT_CARE';
2121 END IF;
2122 l_return_value := l_return_value - call_balance_user_exit (
2123 'SIT_'||l_non_w2_cat,
2124 l_jd_dimension_string,
2125 l_assignment_action_id,
2126 l_assignment_id,
2127 l_virtual_date,
2128 p_asg_type,
2129 p_gre_id_context,
2130 p_jd_context,
2131 l_asg_lock);
2132 END IF; -- end of Non W2 portion
2133 */
2134 -- tmehra 10-AUG-2001
2135 -- Above code has been commented out
2136 -- as it has become redundant due to
2137 -- the addition of the new -ve feeds
2138 -- to the SIT Redns
2139 -- Balances.
2140
2141 END IF;
2142 END IF;
2143 --
2144 IF l_tax_balance_category = 'SUBJECT' THEN
2145 l_return_value := us_tax_balance_rep(p_asg_lock,
2146 'SUBJ_WHABLE',
2147 l_tax_type,
2148 p_ee_or_er,
2149 p_time_type,
2150 p_asg_type,
2151 p_gre_id_context,
2152 p_jd_context,
2153 l_assignment_action_id,
2154 l_assignment_id,
2155 l_virtual_date)
2156 + us_tax_balance_rep(p_asg_lock,
2157 'SUBJ_NWHABLE',
2158 l_tax_type,
2159 p_ee_or_er,
2160 p_time_type,
2161 p_asg_type,
2162 p_gre_id_context,
2163 p_jd_context,
2164 l_assignment_action_id,
2165 l_assignment_id,
2166 l_virtual_date);
2167 --
2168 ELSIF l_tax_balance_category = 'EXEMPT' THEN
2169 l_return_value := us_tax_balance_rep(p_asg_lock,
2170 'GROSS',
2171 l_tax_type,
2172 p_ee_or_er,
2173 p_time_type,
2174 p_asg_type,
2175 p_gre_id_context,
2176 p_jd_context,
2177 l_assignment_action_id,
2178 l_assignment_id,
2179 l_virtual_date);
2180 --
2181 -- 337641
2182 -- check if balance 0 therefore no need to
2183 -- subtract subsequent balance
2184 --
2185 IF ( l_return_value <> 0 )
2186 THEN
2187 l_return_value := l_return_value
2188 - us_tax_balance_rep(p_asg_lock,
2189 'SUBJECT',
2190 l_tax_type,
2191 p_ee_or_er,
2192 p_time_type,
2193 p_asg_type,
2194 p_gre_id_context,
2195 p_jd_context,
2196 l_assignment_action_id,
2197 l_assignment_id,
2198 l_virtual_date);
2199 END IF;
2200 --
2201 -- Adding the following code for the NEW TRR which goes of the
2202 -- Balance Reporting Arch.
2203
2204 ELSIF (l_tax_balance_category = 'REDUCED_SUBJ_WHABLE') AND
2205 NVL(pay_us_balance_view_pkg.get_session_var('REPORT_TYPE'),'NOT_DEFINED') = 'W2' THEN
2206
2207 l_return_value := us_tax_balance_rep(p_asg_lock,
2208 'SUBJ_WHABLE',
2209 l_tax_type,
2210 p_ee_or_er,
2211 p_time_type,
2212 p_asg_type,
2213 p_gre_id_context,
2214 p_jd_context,
2215 l_assignment_action_id,
2216 l_assignment_id,
2217 l_virtual_date)
2218 + us_tax_balance_rep(p_asg_lock,
2219 'SUBJ_NWHABLE',
2220 l_tax_type,
2221 p_ee_or_er,
2222 p_time_type,
2223 p_asg_type,
2224 p_gre_id_context,
2225 p_jd_context,
2226 l_assignment_action_id,
2227 l_assignment_id,
2228 l_virtual_date);
2229 --
2230 IF ( l_return_value <> 0 ) THEN
2231
2232 l_return_value := l_return_value
2233 - us_tax_balance_rep(p_asg_lock,
2234 'PRE_TAX_REDNS',
2235 l_tax_type,
2236 p_ee_or_er,
2237 p_time_type,
2238 p_asg_type,
2239 p_gre_id_context,
2240 p_jd_context,
2241 l_assignment_action_id,
2242 l_assignment_id,
2243 l_virtual_date);
2244
2245 END IF;
2246 --
2247
2248 ELSIF l_tax_balance_category = 'REDUCED_SUBJ_WHABLE' THEN
2249 l_return_value := us_tax_balance_rep(p_asg_lock,
2250 'SUBJ_WHABLE',
2251 l_tax_type,
2252 p_ee_or_er,
2253 p_time_type,
2254 p_asg_type,
2255 p_gre_id_context,
2256 p_jd_context,
2257 l_assignment_action_id,
2258 l_assignment_id,
2259 l_virtual_date);
2260 --
2261 -- 337641
2262 -- check if balance 0 therefore no need to
2263 -- subtract subsequent balance
2264 --
2265 IF ( l_return_value <> 0 )
2266 THEN
2267 l_return_value := l_return_value
2268 --
2269 -- skutteti commented the following and added a new pre tax redns
2270 --
2271 --
2272 -- - us_tax_balance_rep(p_asg_lock,
2273 -- '401_REDNS',
2274 -- l_tax_type,
2275 -- p_ee_or_er,
2276 -- p_time_type,
2277 -- p_asg_type,
2278 -- p_gre_id_context,
2279 -- p_jd_context,
2280 -- l_assignment_action_id,
2281 -- l_assignment_id,
2282 -- l_virtual_date)
2283 -- - us_tax_balance_rep(p_asg_lock,
2284 -- '125_REDNS',
2285 -- l_tax_type,
2286 -- p_ee_or_er,
2287 -- p_time_type,
2288 -- p_asg_type,
2289 -- p_gre_id_context,
2290 -- p_jd_context,
2291 -- l_assignment_action_id,
2292 -- l_assignment_id,
2293 -- l_virtual_date)
2294 -- - us_tax_balance_rep(p_asg_lock,
2295 -- 'DEP_CARE_REDNS',
2296 -- l_tax_type,
2297 -- p_ee_or_er,
2298 -- p_time_type,
2299 -- p_asg_type,
2300 -- p_gre_id_context,
2301 -- p_jd_context,
2302 -- l_assignment_action_id,
2303 -- l_assignment_id,
2304 -- l_virtual_date)
2305 ---------------------------------
2306 -- skutteti added the following
2307 ---------------------------------
2308 - us_tax_balance_rep(p_asg_lock,
2309 'PRE_TAX_REDNS',
2310 l_tax_type,
2311 p_ee_or_er,
2312 p_time_type,
2313 p_asg_type,
2314 p_gre_id_context,
2315 p_jd_context,
2316 l_assignment_action_id,
2317 l_assignment_id,
2318 l_virtual_date);
2319 END IF;
2320 --
2321 ELSIF l_tax_balance_category = 'EXCESS' THEN
2322 l_return_value := us_tax_balance_rep(p_asg_lock,
2323 'REDUCED_SUBJ_WHABLE',
2324 l_tax_type,
2325 p_ee_or_er,
2326 p_time_type,
2327 p_asg_type,
2328 p_gre_id_context,
2329 p_jd_context,
2330 l_assignment_action_id,
2331 l_assignment_id,
2332 l_virtual_date);
2333 --
2334 -- 337641
2335 -- check if balance 0 therefore no need to
2336 -- subtract subsequent balance
2337 --
2338 IF ( l_return_value <> 0 )
2339 THEN
2340 l_return_value := l_return_value
2341 - us_tax_balance_rep(p_asg_lock,
2342 'TAXABLE',
2343 l_tax_type,
2344 p_ee_or_er,
2345 p_time_type,
2346 p_asg_type,
2347 p_gre_id_context,
2348 p_jd_context,
2349 l_assignment_action_id,
2350 l_assignment_id,
2351 l_virtual_date);
2352 END IF;
2353 END IF;
2354 --
2355 hr_utility.trace('Returning : ' || l_return_value);
2356 --
2357 return l_return_value;
2358 --
2359 END us_tax_balance;
2360 --
2361 BEGIN
2362 --
2363 -- Setup the Quarter To Date dimensions in the Cache.
2364 --
2365 g_dim_tbl_grp(0) := 'GRE_QTD';
2366 g_dim_tbl_asg(0) := 'ASG_GRE_QTD';
2367 g_dim_tbl_jdr(0) := 'N';
2368 g_dim_tbl_crs(0) := 'select distinct PAA.assignment_id ' ||
2369 'from pay_assignment_actions PAA, ' ||
2370 ' pay_payroll_actions PPA ' ||
2371 'where PAA.tax_unit_id = :TAX_UNIT_ID ' ||
2372 'and PPA.payroll_action_id = ' ||
2373 ' PAA.payroll_action_id ' ||
2374 'and PPA.effective_date >= ' ||
2375 'trunc(:DATE_EARNED,''Q'') ' ||
2376 'and PPA.effective_date <= ' ||
2377 ':DATE2_EARNED ' ||
2378 'and PPA.action_type in (''R'',''Q'',''I'',''B'',''V'') ';
2379 g_dim_tbl_vtd(0) := 'select max(PAF.effective_end_date) ' ||
2380 'from per_assignments_f PAF ' ||
2381 'where PAF.assignment_id = :ASSIGNMENT_ID ' ||
2382 'and PAF.payroll_id is not null ' ||
2383 'and PAF.effective_end_date between ' ||
2384 ' trunc(:DATE_EARNED,''Q'') and ' ||
2385 ' :DATE2_EARNED';
2386 g_dim_tbl_btt(0) := 'Q';
2387 --
2388 -- Setup the Year To Date dimensions in the Cache.
2389 --
2390 g_dim_tbl_grp(1) := 'GRE_YTD';
2391 g_dim_tbl_asg(1) := 'ASG_GRE_YTD';
2392 g_dim_tbl_jdr(1) := 'N';
2393 g_dim_tbl_crs(1) := 'select distinct PAA.assignment_id ' ||
2394 'from pay_assignment_actions PAA, ' ||
2395 ' pay_payroll_actions PPA ' ||
2396 'where PAA.tax_unit_id = :TAX_UNIT_ID ' ||
2397 'and PPA.payroll_action_id = ' ||
2398 ' PAA.payroll_action_id ' ||
2399 'and PPA.effective_date >= ' ||
2400 'trunc(:DATE_EARNED,''Y'') ' ||
2401 'and PPA.effective_date <= ' ||
2402 ':DATE2_EARNED ' ||
2403 'and PPA.action_type in (''R'',''Q'',''I'',''B'',''V'') ';
2404 g_dim_tbl_vtd(1) := 'select max(PAF.effective_end_date) ' ||
2405 'from per_assignments_f PAF ' ||
2406 'where PAF.assignment_id = :ASSIGNMENT_ID ' ||
2407 'and PAF.payroll_id is not null ' ||
2408 'and PAF.effective_end_date between ' ||
2409 ' trunc(:DATE_EARNED,''Y'') and ' ||
2410 ' :DATE2_EARNED';
2411 g_dim_tbl_btt(1) := 'Y';
2412 --
2413 -- Setup the Subject to Tax Year To Date dimensions in the Cache.
2414 --
2415 g_dim_tbl_grp(2) := 'SUBJECT_TO_TAX_GRE_YTD';
2416 g_dim_tbl_asg(2) := 'SUBJECT_TO_TAX_ASG_GRE_YTD';
2417 g_dim_tbl_jdr(2) := 'N';
2418 g_dim_tbl_crs(2) := 'select distinct PAA.assignment_id ' ||
2419 'from pay_assignment_actions PAA, ' ||
2420 ' pay_payroll_actions PPA ' ||
2421 'where PAA.tax_unit_id = :TAX_UNIT_ID ' ||
2422 'and PPA.payroll_action_id = ' ||
2423 ' PAA.payroll_action_id ' ||
2424 'and PPA.effective_date >= ' ||
2425 'trunc(:DATE_EARNED,''Y'') ' ||
2426 'and PPA.effective_date <= ' ||
2427 ':DATE2_EARNED ' ||
2428 'and PPA.action_type in (''R'',''Q'',''I'',''B'',''V'') ';
2429 g_dim_tbl_vtd(2) := 'select max(PAF.effective_end_date) ' ||
2430 'from per_assignments_f PAF ' ||
2431 'where PAF.assignment_id = :ASSIGNMENT_ID ' ||
2432 'and PAF.payroll_id is not null ' ||
2433 'and PAF.effective_end_date between ' ||
2434 ' trunc(:DATE_EARNED,''Y'') and ' ||
2435 ' :DATE2_EARNED';
2436 g_dim_tbl_btt(2) := 'Y';
2437 --
2438 -- Setup the Year To Date in Jurisdiction dimensions in the Cache.
2439 --
2440 g_dim_tbl_grp(3) := 'GRE_JD_YTD';
2441 g_dim_tbl_asg(3) := 'ASG_JD_GRE_YTD';
2442 g_dim_tbl_jdr(3) := 'Y';
2443 g_dim_tbl_crs(3) := 'select distinct PAR.assignment_id ' ||
2444 'from pay_balance_types PBT, ' ||
2445 ' pay_us_asg_reporting PAR ' ||
2446 'where PAR.tax_unit_id = :TAX_UNIT_ID ' ||
2447 'and PBT.balance_type_id = :BALANCE_TYPE_ID ' ||
2448 'and PBT.jurisdiction_level <> 0 ' ||
2449 'and substr(PAR.jurisdiction_code, 1, ' ||
2450 'PBT.jurisdiction_level) = ' ||
2451 'substr(:JURISDICTION_CODE, 1, ' ||
2452 'PBT.jurisdiction_level) ' ||
2453 'and exists (select 1 ' ||
2454 ' from pay_payroll_actions PPA, ' ||
2455 ' pay_assignment_actions PAA ' ||
2456 ' where PAA.assignment_id = ' ||
2457 'PAR.assignment_id ' ||
2458 ' and PAA.tax_unit_id = ' ||
2459 'PAR.tax_unit_id ' ||
2460 ' and PPA.payroll_action_id = ' ||
2461 'PAA.payroll_action_id ' ||
2462 ' and PPA.effective_date >= ' ||
2463 'trunc(:DATE_EARNED,''Y'') ' ||
2464 ' and PPA.effective_date <= ' ||
2465 ':DATE2_EARNED ' ||
2466 ' and PPA.action_type in (''R'',''Q'',''I'',''B'',''V'') )';
2467 g_dim_tbl_vtd(3) := 'select max(PAF.effective_end_date) ' ||
2468 'from per_assignments_f PAF ' ||
2469 'where PAF.assignment_id = :ASSIGNMENT_ID ' ||
2470 'and PAF.payroll_id is not null ' ||
2471 'and PAF.effective_end_date between ' ||
2472 ' trunc(:DATE_EARNED,''Y'') and ' ||
2473 ' :DATE2_EARNED';
2474 g_dim_tbl_btt(3) := 'Y';
2475 -- Set the next free cache space.
2476 g_nxt_free_dim := 4;
2477
2478
2479 -- hr_utility.trace_on(null,'tx');
2480 end pay_us_tax_bals_pkg;