[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.3 2005/09/26 23:59:57 rdhingra noship $ */
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
111 */
112
113 -- Global declarations
114 type num_array is table of number(15) index by binary_integer;
115 type char80_array is table of varchar2(80) index by binary_integer;
116 type char_array is table of varchar2(1) index by binary_integer;
117 type char2000_array is table of varchar2(2000) index by binary_integer;
118 --
119 -- Assignment Id Cache
120 g_asgid_tbl_id num_array;
121 g_asgid_tbl_bgid num_array;
122 g_nxt_free_asgid binary_integer := 0;
123 --
124 -- Group Dimension Cache.
125 g_dim_tbl_grp char80_array;
126 g_dim_tbl_asg char80_array;
127 g_dim_tbl_crs char2000_array;
128 g_dim_tbl_vtd char2000_array;
129 g_dim_tbl_jdr char_array;
130 g_dim_tbl_btt char_array;
131 g_nxt_free_dim binary_integer;
132 --
133 -------------------------------------------------------------------------------
134 --
135 -- Quick procedure to raise an error
136 --
137 -------------------------------------------------------------------------------
138 PROCEDURE local_error(p_procedure varchar2,
139 p_step number) IS
140 BEGIN
141 --
142 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
143 hr_utility.set_message_token('PROCEDURE',
144 'pay_us_tax_bals_pkg.'||p_procedure);
145 hr_utility.set_message_token('STEP',p_step);
146 hr_utility.raise_error;
147 --
148 END local_error;
149 --
150 --
151 ----------------------------------------------------------------------------
152 -- Get the assignment level equivilent of the group balance, plus a cursor
153 -- that returns all the assignments contributing to the group level balance.
154 ----------------------------------------------------------------------------
155 procedure get_asg_for_grp_lvl(p_grp_dvl_dimension in varchar2,
156 p_asg_lvl_dimension out nocopy varchar2,
157 p_asg_cursor out nocopy varchar2,
158 p_asg_jd_required out nocopy boolean,
159 p_asg_vdate_cursor out nocopy varchar2,
160 p_asg_balance_time out nocopy varchar2,
161 p_found out nocopy boolean)
162 is
163 l_count number;
164 l_found boolean;
165 begin
166 -- Look to see if the group level balance is in our cache.
167 --
168 hr_utility.set_location('pay_us_tax_bals_pkg.get_asg_for_grp_lvl', 10);
169 --
170 l_count := 0;
171 l_found := FALSE;
172 while ((l_count < g_nxt_free_dim) AND (l_found = FALSE)) loop
173 if (p_grp_dvl_dimension = g_dim_tbl_grp(l_count)) then
174 hr_utility.set_location('pay_us_tax_bals_pkg.get_asg_for_grp_lvl', 20);
175 --
176 p_asg_lvl_dimension := g_dim_tbl_asg(l_count);
177 p_asg_cursor := g_dim_tbl_crs(l_count);
178 p_asg_vdate_cursor := g_dim_tbl_vtd(l_count);
179 p_asg_balance_time := g_dim_tbl_btt(l_count);
180 --
181 -- Does the cursor require the jurisdiction_code.
182 --
183 if g_dim_tbl_jdr(l_count) = 'Y' then
184 p_asg_jd_required := TRUE;
185 else
186 p_asg_jd_required := FALSE;
187 end if;
188 l_found := TRUE;
189 end if;
190 l_count := l_count + 1;
191 end loop;
192 --
193 hr_utility.set_location('pay_us_tax_bals_pkg.get_asg_for_grp_lvl', 30);
194 p_found := l_found;
195 --
196 end;
197 --
198 ----------------------------------------------------------------------------
199 -- Get the defined balance id given the balance name and database item
200 -- suffix.
201 ----------------------------------------------------------------------------
202 function get_defined_balance (p_balance_name varchar2,
203 p_dimension_suffix varchar2) return number is
204 l_defined_balance_id number;
205 --
206 begin
207 hr_utility.set_location('pay_us_tax_bals_pkg.get_defined_balance', 10);
208 --
209
210 SELECT creator_id
211 INTO l_defined_balance_id
212 FROM ff_user_entities
213 WHERE user_entity_name like
214 translate(p_balance_name||'_'||p_dimension_suffix,' ','_')
215 AND legislation_code = 'US';
216 --
217 hr_utility.set_location('pay_us_tax_bals_pkg.get_defined_balance', 20);
218 return l_defined_balance_id;
219 end;
220 --
221 ------------------------------------------------------------------------------
222 -- This ensures that the assignment is on a payroll on the effective date,
223 -- if not a valid date is found. If no valid date can be found an error is
224 -- raised.
225 ------------------------------------------------------------------------------
226 function get_virtual_date (p_assignment_id number,
227 p_virtual_date date,
228 p_balance_time varchar2,
229 p_asg_vdate_cursor varchar2) return date is
230 l_dummy varchar2(1);
231 l_virtual_date date;
232 l_virtual_date2 date;
233 l_res_date date;
234 begin
235 begin
236 --
237 -- Is the assignment on a payroll.
238 --
239 hr_utility.set_location('pay_us_tax_bals_pkg.get_virtual_date', 10);
240 select ''
241 into l_dummy
242 from per_assignments_f paf
243 where paf.assignment_id = p_assignment_id
244 and p_virtual_date between paf.effective_start_date
245 and paf.effective_end_date
246 and paf.payroll_id is not null;
247
248 --
249 hr_utility.set_location('pay_us_tax_bals_pkg.get_virtual_date', 20);
250 return p_virtual_date;
251 exception
252 when no_data_found then
253 --
254 -- Find a valid date for the assignment.
255 --
256 declare
257 sql_cursor number;
258 l_rows number;
259 begin
260 hr_utility.set_location('pay_us_tax_bals_pkg.get_virtual_date',
261 30);
262 sql_cursor := dbms_sql.open_cursor;
263 dbms_sql.parse(sql_cursor, p_asg_vdate_cursor, dbms_sql.v7);
264 dbms_sql.bind_variable (sql_cursor, 'ASSIGNMENT_ID',
265 p_assignment_id);
266 dbms_sql.bind_variable (sql_cursor, 'DATE_EARNED',
267 p_virtual_date);
268 dbms_sql.bind_variable (sql_cursor, 'DATE2_EARNED',
269 p_virtual_date);
270 dbms_sql.define_column (sql_cursor, 1, l_virtual_date);
271 l_rows := dbms_sql.execute(sql_cursor);
272 l_rows := dbms_sql.fetch_rows (sql_cursor);
273 if l_rows > 0 then
274 hr_utility.set_location(
275 'pay_us_tax_bals_pkg.get_virtual_date', 40);
276 dbms_sql.column_value (sql_cursor, 1, l_virtual_date);
277 --
278 select max(ppf.effective_end_date)
279 into l_virtual_date2
280 from per_assignments_f paf,
281 pay_payrolls_f ppf
282 where paf.assignment_id = p_assignment_id
283 and paf.payroll_id = ppf.payroll_id
284 and ppf.effective_end_date between
285 trunc(p_virtual_date, p_balance_time)
286 and p_virtual_date;
287 --
288 -- Now work out which date is needed
289 --
290 if l_virtual_date is null then
291 if l_virtual_date2 is null then
292 hr_utility.set_location(
293 'pay_us_tax_bals_pkg.get_virtual_date', 60);
294 local_error('get_virtual_date', 2);
295 else
296 hr_utility.set_location(
297 'pay_us_tax_bals_pkg.get_virtual_date', 70);
298 l_res_date := l_virtual_date2;
299 end if;
300 else
301 if l_virtual_date2 is null then
302 hr_utility.set_location(
303 'pay_us_tax_bals_pkg.get_virtual_date', 80);
304 l_res_date := l_virtual_date;
305 else
306 hr_utility.set_location(
307 'pay_us_tax_bals_pkg.get_virtual_date', 90);
308 l_res_date := least(l_virtual_date, l_virtual_date2);
309 end if;
310 end if;
311 --
312 else
313 hr_utility.set_location(
314 'pay_us_tax_bals_pkg.get_virtual_date', 50);
315 local_error('get_virtual_date', 1);
316 end if;
317 --
318 dbms_sql.close_cursor(sql_cursor);
319 end;
320 --
321 return l_res_date;
322 end;
323 end;
324 --
325 --
326 ------------------------------------------------------------------------------
330 p_virtual_date date,
327 -- Get the balance value of a group level balance given the assignment id.
328 ------------------------------------------------------------------------------
329 function get_grp_asg_value (p_assignment_id number,
331 p_balance_name varchar2,
332 p_database_suffix varchar2,
333 p_gre_id number,
334 p_jurisdiction_code varchar2,
335 p_asg_lock varchar2)
336 return number is
337 l_dummy varchar2(5);
338 l_lat_balances boolean;
339 l_asg_data_suffix varchar2(80);
340 l_asg_data_cursor varchar2(2000);
341 l_asg_vdate_cursor varchar2(2000);
342 l_asg_balance_time varchar2(10);
343 l_asg_jd_required boolean;
344 l_grp_lat_exist boolean;
345 l_defined_balance_id number;
346 sql_cursor number;
347 l_rows number;
348 l_asg_id number;
349 l_balance_value number;
350 cnt number;
351 l_virtual_date date;
352 l_balance_type_id number;
353 l_run_route varchar2(5);
354 l_run_route_bool boolean;
355
356 begin
357
358 -- check for the 'RUN_ROUTE' parameter_name in the pay_action_parameters
359 -- table to determine if we want to call the run_result route instead of
360 -- the run_balance route.
361 begin
362
363 select parameter_value
364 into l_run_route
365 from PAY_ACTION_PARAMETERS
366 where parameter_name = 'RUN_ROUTE';
367
368 exception
369 WHEN others then
370 l_run_route := 'FALSE';
371 end;
372
373 IF l_run_route <> 'TRUE' THEN
374 l_run_route_bool := false;
375 ELSE
376 l_run_route_bool := true;
377 END IF;
378
379 l_balance_value := 0;
380 hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_asg_value', 10);
381 --
382 -- Get the assignment level version.
383 --
384 get_asg_for_grp_lvl(p_database_suffix,
385 l_asg_data_suffix,
386 l_asg_data_cursor,
387 l_asg_jd_required,
388 l_asg_vdate_cursor,
389 l_asg_balance_time,
390 l_grp_lat_exist);
391 --
392 if l_grp_lat_exist then
393 --
394 -- Are there latest balances available.
395 --
396 hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_asg_value', 20);
397 l_defined_balance_id := get_defined_balance(p_balance_name,
398 l_asg_data_suffix);
399 --
400 begin
401 select ''
402 into l_dummy
403 from dual
404 where exists (
405 select ''
406 from pay_payroll_actions ppa,
407 pay_assignment_actions paa,
408 pay_assignment_latest_balances palb
409 where palb.assignment_id = p_assignment_id
410 and palb.defined_balance_id = l_defined_balance_id
411 and palb.assignment_action_id =
412 paa.assignment_action_id
413 and paa.payroll_action_id = ppa.payroll_action_id
414 and ppa.action_type in ('R','Q','I','B','V')
415 and p_virtual_date >= ppa.effective_date);
416 --
417 l_lat_balances := TRUE;
418 --
419 exception
420 when no_data_found then
421 l_lat_balances := FALSE;
422 end;
423 --
424 if (l_lat_balances = TRUE) then
425 --
426 -- OK, we can sum the values of the assignment balances to get the
427 -- group balance.
428 --
429 hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_asg_value', 30);
430 begin
431 --
432 sql_cursor := dbms_sql.open_cursor;
433 dbms_sql.parse(sql_cursor, l_asg_data_cursor, dbms_sql.v7);
434 dbms_sql.bind_variable (sql_cursor, 'TAX_UNIT_ID', p_gre_id);
435 dbms_sql.bind_variable (sql_cursor, 'DATE_EARNED', p_virtual_date);
436 dbms_sql.bind_variable (sql_cursor, 'DATE2_EARNED', p_virtual_date);
437 dbms_sql.define_column (sql_cursor, 1, l_asg_id);
438 --
439 -- Does the cursor require the jurisdiction code. Hence balance
440 -- type.
441 --
442 if l_asg_jd_required then
443 select balance_type_id
444 into l_balance_type_id
445 from pay_defined_balances
446 where defined_balance_id = l_defined_balance_id;
447 --
448 dbms_sql.bind_variable (sql_cursor, 'BALANCE_TYPE_ID',
449 l_balance_type_id);
450 dbms_sql.bind_variable (sql_cursor, 'JURISDICTION_CODE',
451 p_jurisdiction_code);
452 end if;
453 l_rows := dbms_sql.execute(sql_cursor);
454 l_rows := 1;
455 cnt := 0;
456 --
460 while (l_rows <> 0) loop
457 -- Loop through all the contributing assignments, go get there
458 -- balance value and add onto the running total.
459 --
461 l_rows := dbms_sql.fetch_rows (sql_cursor);
462 cnt := cnt + 1;
463 if l_rows > 0 then
464 hr_utility.set_location(
465 'pay_us_tax_bals_pkg.get_grp_asg_value', 40);
466 dbms_sql.column_value (sql_cursor, 1, l_asg_id);
467 --
468 l_virtual_date := get_virtual_date(l_asg_id, p_virtual_date,
469 l_asg_balance_time,
470 l_asg_vdate_cursor);
471 --
472 l_balance_value := l_balance_value +
473 pay_balance_pkg.get_value_lock
474 (l_defined_balance_id,
475 l_asg_id,
476 l_virtual_date,
477 l_run_route_bool,
478 p_asg_lock
479 );
480 end if;
481 end loop;
482 --
483 dbms_sql.close_cursor(sql_cursor);
484 end;
485 else
486 --
487 -- No latets balances available. Run the route.
488 --
489 hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_asg_value', 50);
490 l_defined_balance_id := get_defined_balance(p_balance_name,
491 p_database_suffix);
492 l_balance_value := pay_balance_pkg.get_value_lock
493 (l_defined_balance_id,
494 p_assignment_id,
495 p_virtual_date,
496 l_run_route_bool,
497 p_asg_lock
498 );
499 end if;
500 else
501 --
502 -- Can not sum the assignment level balances, thus run group
503 -- level route.
504 --
505 hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_asg_value', 60);
506 l_defined_balance_id := get_defined_balance(p_balance_name,
507 p_database_suffix);
508 l_balance_value := pay_balance_pkg.get_value_lock
509 (l_defined_balance_id,
510 p_assignment_id,
511 p_virtual_date,
512 l_run_route_bool,
513 p_asg_lock
514 );
515 end if;
516 --
517 hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_asg_value', 70);
518 return l_balance_value;
519 --
520 end;
521 --
522 ------------------------------------------------------------------------------
523 -- Get the balance value of a group level balance given the assignment action
524 -- id.
525 ------------------------------------------------------------------------------
526 function get_grp_act_value (p_assignment_action_id number,
527 p_virtual_date date,
528 p_balance_name varchar2,
529 p_database_suffix varchar2,
530 p_gre_id number)
531 return number is
532 l_defined_balance_id number;
533 l_balance_value number;
534 l_run_route varchar2(5);
535 l_run_route_bool boolean;
536
537 begin
538
539 -- check for the 'RUN_ROUTE' parameter_name in the pay_action_parameters
540 -- table to determine if we want to call the run_result route instead of
541 -- the run_balance route.
542 begin
543
544 select parameter_value
545 into l_run_route
546 from PAY_ACTION_PARAMETERS
547 where parameter_name = 'RUN_ROUTE';
548
549 exception
550 WHEN others then
551 l_run_route := 'FALSE';
552 end;
553
554 IF l_run_route <> 'TRUE' THEN
555 l_run_route_bool := false;
556 ELSE
557 l_run_route_bool := true;
558 END IF;
559
560 l_balance_value := 0;
561 hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_act_value', 10);
562 --
563 l_defined_balance_id := get_defined_balance(p_balance_name,
564 p_database_suffix);
565 l_balance_value := pay_balance_pkg.get_value (l_defined_balance_id,
566 p_assignment_action_id,
567 l_run_route_bool,
568 FALSE
569 );
570 --
571 hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_act_value', 20);
572 return l_balance_value;
573 --
574 end;
575 --
576 -------------------------------------------------------------------------------
580 p_virtual_date date,
577 -- Get the value of the group level balance.
578 -------------------------------------------------------------------------------
579 function get_grp_value (p_assignment_id number,
581 p_balance_name varchar2,
582 p_database_suffix varchar2,
583 p_gre_id number,
584 p_jurisdiction_code varchar2,
585 p_assignment_action_id number default null,
586 p_asg_lock varchar2)
587 return number is
588 begin
589 hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_value', 10);
590 if p_assignment_action_id is null then
591 hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_value', 20);
592 return get_grp_asg_value(p_assignment_id,
593 p_virtual_date,
594 p_balance_name,
595 p_database_suffix,
596 p_gre_id,
597 p_jurisdiction_code,
598 p_asg_lock);
599 else
600 hr_utility.set_location('pay_us_tax_bals_pkg.get_grp_value', 30);
601 return get_grp_act_value(p_assignment_action_id,
602 p_virtual_date,
603 p_balance_name,
604 p_database_suffix,
605 p_gre_id);
606 end if;
607 end;
608 -------------------------------------------------------------------------------
609 --
610 -- Wrapper around the core bal user exit
611 --
612 -------------------------------------------------------------------------------
613 FUNCTION call_balance_user_exit
614 (p_balance_name varchar2,
615 p_dimension_suffix varchar2,
616 p_assignment_action_id number DEFAULT NULL,
617 p_assignment_id number DEFAULT NULL,
618 p_virtual_date date DEFAULT NULL,
619 p_asg_type varchar2 DEFAULT NULL,
620 p_gre_id number DEFAULT NULL,
621 p_jurisdiction_code varchar2 DEFAULT NULL,
622 p_asg_lock varchar2 DEFAULT 'Y')
623 RETURN number IS
624 --
625 l_defined_balance_id number;
626 l_balance_type_id number;
627 l_dimension_id number;
628 l_session VARCHAR2(15);
629 l_run_route varchar2(5);
630 l_run_route_bool boolean;
631 --
632 BEGIN
633 --
634
635 hr_utility.set_location('pay_us_tax_bals_pkg.balance_name'||p_balance_name, 9);
636 hr_utility.trace('p_dimension_suffix = '||p_dimension_suffix);
637 hr_utility.trace('p_balance_name = '||p_balance_name);
638 hr_utility.trace('p_asg_type = '||p_asg_type);
639 hr_utility.set_location('pay_us_tax_bals_pkg.call_balance_user_exit', 10);
640
641 -- check for the 'RUN_ROUTE' parameter_name in the pay_action_parameters
642 -- table to determine if we want to call the run_result route instead of
643 -- the run_balance route.
644 begin
645
646 select parameter_value
647 into l_run_route
648 from PAY_ACTION_PARAMETERS
649 where parameter_name = 'RUN_ROUTE';
650
651 exception
652 WHEN others then
653 l_run_route := 'FALSE';
654 end;
655
656 IF l_run_route <> 'TRUE' THEN
657 l_run_route_bool := false;
658 ELSE
659 l_run_route_bool := true;
660 END IF;
661
662 IF p_assignment_action_id IS NOT NULL THEN
663 -- If group level balance, call the group level balance code.
664
665 /* commenting of the following code. From now on we will be using
666 the Balance Reporting Arch */
667 /*
668 if p_asg_type = 'GRE' then
669 hr_utility.set_location('pay_us_tax_bals_pkg.call_balance_user_exit',
670 20);
671 return get_grp_value(p_assignment_id,
672 p_virtual_date,
673 p_balance_name,
674 p_dimension_suffix,
675 p_gre_id,
676 p_jurisdiction_code,
677 p_assignment_action_id,
678 p_asg_lock
679 );
680 else
681 */
682 l_defined_balance_id := get_defined_balance(p_balance_name,
683 p_dimension_suffix);
684 IF p_dimension_suffix not like '%PAY%' THEN
685 hr_utility.set_location('pay_us_tax_bals_pkg.call_balance_user_exit', 30);
686 return pay_balance_pkg.get_value (l_defined_balance_id,
687 p_assignment_action_id,
688 l_run_route_bool,
689 FALSE
690 );
691 ELSE /* If payments dimension then must execute DB item 395029 */
692 hr_utility.set_location('pay_us_tax_bals_pkg.call_balance_user_exit', 40);
693 return pay_balance_pkg.get_value (l_defined_balance_id,
694 p_assignment_action_id,
698 --
695 true );
696 -- END IF;
697 end if;
699 ELSE
700 -- If group level balance, call the group level balance code.
701 /* commenting of the following code. From now on we will be using
702 the Balance Reporting Arch */
703 /*
704 if p_asg_type = 'GRE' then
705 hr_utility.set_location('pay_us_tax_bals_pkg.call_balance_user_exit',
706 50);
707 return get_grp_value(p_assignment_id,
708 p_virtual_date,
709 p_balance_name,
710 p_dimension_suffix,
711 p_gre_id,
712 p_jurisdiction_code,
713 null,
714 p_asg_lock
715 );
716 else
717 */
718 l_defined_balance_id := get_defined_balance(p_balance_name,
719 p_dimension_suffix);
720 hr_utility.set_location('pay_us_tax_bals_pkg.call_balance_user_exit',
721 60);
722 return pay_balance_pkg.get_value_lock
723 (l_defined_balance_id,
724 p_assignment_id,
725 p_virtual_date,
726 l_run_route_bool,
727 p_asg_lock
728 );
729 -- end if;
730 END IF;
731 --
732 END call_balance_user_exit;
733 --
734 -------------------------------------------------------------------------------
735 --
736 -- An overloaded version without the payroll_action_id param to prevent calls
737 -- from forms from breaking
738 --
739 -------------------------------------------------------------------------------
740 FUNCTION us_tax_balance_rep (p_asg_lock in boolean DEFAULT TRUE,
741 p_tax_balance_category in varchar2,
742 p_tax_type in varchar2,
743 p_ee_or_er in varchar2,
744 p_time_type in varchar2,
745 p_asg_type in varchar2,
746 p_gre_id_context in number,
747 p_jd_context in varchar2 DEFAULT NULL,
748 p_assignment_action_id in number DEFAULT NULL,
749 p_assignment_id in number DEFAULT NULL,
750 p_virtual_date in date DEFAULT NULL,
751 p_payroll_action_id in number)
752 RETURN number IS
753 --
754 BEGIN
755 --
756 return us_tax_balance(p_tax_balance_category => p_tax_balance_category,
757 p_tax_type => p_tax_type,
758 p_ee_or_er => p_ee_or_er,
759 p_time_type => p_time_type,
760 p_asg_type => p_asg_type,
761 p_gre_id_context => p_gre_id_context,
762 p_jd_context => p_jd_context,
763 p_assignment_action_id => p_assignment_action_id,
764 p_assignment_id => p_assignment_id,
765 p_virtual_date => p_virtual_date,
766 p_payroll_action_id => p_payroll_action_id,
767 p_asg_lock => p_asg_lock);
768 --
769 END us_tax_balance_rep;
770 --
771 FUNCTION us_tax_balance_rep (p_asg_lock in boolean DEFAULT TRUE,
772 p_tax_balance_category in varchar2,
773 p_tax_type in varchar2,
774 p_ee_or_er in varchar2,
775 p_time_type in varchar2,
776 p_asg_type in varchar2,
777 p_gre_id_context in number,
778 p_jd_context in varchar2 DEFAULT NULL,
779 p_assignment_action_id in number DEFAULT NULL,
780 p_assignment_id in number DEFAULT NULL,
781 p_virtual_date in date DEFAULT NULL
782 )
783 RETURN number IS
784 --
785 BEGIN
786 --
787 return us_tax_balance(p_tax_balance_category => p_tax_balance_category,
788 p_tax_type => p_tax_type,
789 p_ee_or_er => p_ee_or_er,
790 p_time_type => p_time_type,
791 p_asg_type => p_asg_type,
792 p_gre_id_context => p_gre_id_context,
793 p_jd_context => p_jd_context,
794 p_assignment_action_id => p_assignment_action_id,
795 p_assignment_id => p_assignment_id,
796 p_virtual_date => p_virtual_date,
797 p_payroll_action_id => NULL,
798 p_asg_lock => p_asg_lock);
799 --
800 END us_tax_balance_rep;
801 --
802 FUNCTION us_tax_balance (p_tax_balance_category in varchar2,
803 p_tax_type in varchar2,
804 p_ee_or_er in varchar2,
808 p_jd_context in varchar2 DEFAULT NULL,
805 p_time_type in varchar2,
806 p_asg_type in varchar2,
807 p_gre_id_context in number,
809 p_assignment_action_id in number DEFAULT NULL,
810 p_assignment_id in number DEFAULT NULL,
811 p_virtual_date in date DEFAULT NULL)
812 RETURN number IS
813 --
814 BEGIN
815 --
816 return us_tax_balance(p_tax_balance_category => p_tax_balance_category,
817 p_tax_type => p_tax_type,
818 p_ee_or_er => p_ee_or_er,
819 p_time_type => p_time_type,
820 p_asg_type => p_asg_type,
821 p_gre_id_context => p_gre_id_context,
822 p_jd_context => p_jd_context,
823 p_assignment_action_id => p_assignment_action_id,
824 p_assignment_id => p_assignment_id,
825 p_virtual_date => p_virtual_date,
826 p_payroll_action_id => NULL,
827 p_asg_lock => TRUE);
828 --
829 END us_tax_balance;
830 --
831 FUNCTION us_tax_balance (p_tax_balance_category in varchar2,
832 p_tax_type in varchar2,
833 p_ee_or_er in varchar2,
834 p_time_type in varchar2,
835 p_asg_type in varchar2,
836 p_gre_id_context in number,
837 p_jd_context in varchar2 DEFAULT NULL,
838 p_assignment_action_id in number DEFAULT NULL,
839 p_assignment_id in number DEFAULT NULL,
840 p_virtual_date in date DEFAULT NULL,
841 p_payroll_action_id in number)
842 RETURN number IS
843 --
844 BEGIN
845 --
846 return us_tax_balance(p_tax_balance_category => p_tax_balance_category,
847 p_tax_type => p_tax_type,
848 p_ee_or_er => p_ee_or_er,
849 p_time_type => p_time_type,
850 p_asg_type => p_asg_type,
851 p_gre_id_context => p_gre_id_context,
852 p_jd_context => p_jd_context,
853 p_assignment_action_id => p_assignment_action_id,
854 p_assignment_id => p_assignment_id,
855 p_virtual_date => p_virtual_date,
856 p_payroll_action_id => p_payroll_action_id,
857 p_asg_lock => TRUE);
858 --
859 END us_tax_balance;
860 --
861 -------------------------------------------------------------------------------
862 --
863 --
864 --
865 --
866 -------------------------------------------------------------------------------
867 FUNCTION us_tax_balance (p_tax_balance_category in varchar2,
868 p_tax_type in varchar2,
869 p_ee_or_er in varchar2,
870 p_time_type in varchar2,
871 p_asg_type in varchar2,
872 p_gre_id_context in number,
873 p_jd_context in varchar2 DEFAULT NULL,
874 p_assignment_action_id in number DEFAULT NULL,
875 p_assignment_id in number DEFAULT NULL,
876 p_virtual_date in date DEFAULT NULL,
877 p_payroll_action_id in number,
878 p_asg_lock in boolean)
879 RETURN number IS
880 --
881 -- 337641 - cursor rather than ful blown select
882 -- doing group function (min)
883 --
884 CURSOR get_pay_action_id IS
885 select assignment_action_id
886 from pay_assignment_actions
887 where payroll_action_id = p_payroll_action_id;
888
889 /* we need to get the max assignment_action_id for the core
890 balance package. from the max aaid they find the business group
891 id to see if the balances are valid for that business group only. */
892
893 /*
894 CURSOR c_get_min_aaid(p_start_date date , p_end_date date) is
895 select nvl(min(assignment_action_id),-1)
896 from pay_assignment_actions paa,pay_payroll_actions ppa
897 where paa.tax_unit_id = p_gre_id_context
898 and ppa.payroll_action_id = paa.payroll_action_id
899 and ppa.effective_date between p_start_date and p_end_date
900 and ppa.action_type in ('R','Q','I','B','V') ;
901 */
902
903 CURSOR c_get_min_aaid(p_start_date date , p_end_date date,p_bg_id number) is
904 select nvl(min(assignment_action_id),-1)
905 from pay_assignment_actions paa,pay_payroll_actions ppa,pay_payrolls_f ppf
906 where ppa.business_group_id +0 = p_bg_id
907 and ppa.payroll_action_id = paa.payroll_action_id
908 and ppa.effective_date between p_start_date and p_end_date
909 and ppa.action_type in ('R','Q','I','B','V')
910 and ppf.payroll_id = ppa.payroll_id
911 and ppa.business_group_id +0 = ppf.business_group_id;
912
913 CURSOR c_get_bg_id is
917
914 select business_group_id
915 from hr_organization_units
916 where organization_id = p_gre_id_context;
918 --
919 l_return_value number;
920 l_test number;
921 l_tax_balance_category varchar2(30);
922 l_tax_type varchar2(30);
923 l_ee_or_er varchar2(5);
924 l_dimension_string varchar2(80);
925 l_jd_dimension_string varchar2(80);
926 l_assignment_id number;
927 l_assignment_action_id number;
928 l_asg_exists number;
929 l_max_date date;
930 l_bal_start_date date;
931 l_virtual_date date;
932 l_valid number;
933 l_asg_lock varchar2(2);
934 l_non_w2_cat varchar2(60);
935
936 l_group_rb_report varchar2(50);
937 l_grp_aaid varchar2(50);
938 l_session_aaid number;
939 l_group_rb_sdate date;
940 l_group_rb_edate date;
941 l_temp_bg_id number;
942
943 l_end_of_time date default to_date('31-12-4712','DD-MM-YYYY');
944 --
945 BEGIN
946 --
947 -- Set the locking flag.
948 --
949 l_asg_lock := 'N';
950 if (p_asg_lock) then
951 l_asg_lock := 'Y';
952 end if;
953 --
954 -- Check that inputs based on lookups are valid
955 --
956 if p_tax_balance_category <> 'NONE' then
957
958 SELECT count(0)
959 INTO l_valid
960 FROM hr_lookups
961 WHERE lookup_type = 'US_TAX_BALANCE_CATEGORY'
962 AND lookup_code = p_tax_balance_category;
963 --
964 IF l_valid = 0 THEN
965 hr_utility.trace('Error: Invalid tax balance category');
966 local_error('us_tax_balance',1);
967 END IF;
968 --
969 SELECT count(0)
970 INTO l_valid
971 FROM hr_lookups
972 WHERE lookup_type = 'US_TAX_TYPE'
973 AND lookup_code = p_tax_type;
974 --
975 IF l_valid = 0 THEN
976 hr_utility.trace('Error: Invalid tax type');
977 local_error('us_tax_balance',2);
978 END IF;
979 --
980 end if; /* p_tax_balance_category is NONE */
981
982 SELECT count(0)
983 INTO l_valid
984 FROM dual
985 WHERE p_asg_type in ('ASG','PER','GRE');
986 --
987 IF l_valid = 0 THEN
988 hr_utility.trace('Error: Invalid asg_type parameter');
989 local_error('us_tax_balance',3);
990 END IF;
991 --
992 SELECT count(0)
993 INTO l_valid
994 FROM dual
995 WHERE p_time_type in ('RUN','PTD','MONTH','QTD','YTD', 'PAYMENTS', 'PYDATE');
996 --
997 IF l_valid = 0 THEN
998 hr_utility.trace('Error: Invalid time_type parameter');
999 local_error('us_tax_balance',4);
1000 END IF;
1001 --
1002 -- Set the contexts used in the bal user exit. Same throughout, so set
1003 -- them up front
1004 --
1005 hr_utility.set_location('pay_tax_bals_pkg',30);
1006 --
1007 pay_balance_pkg.set_context('TAX_UNIT_ID',p_gre_id_context);
1008 IF p_jd_context IS NOT NULL THEN
1009 IF (p_tax_type = 'SCHOOL' and length(p_jd_context) > 11) THEN
1010 pay_balance_pkg.set_context('JURISDICTION_CODE',substr(p_jd_context,1,2)||
1011 '-'||substr(p_jd_context,13,5));
1012 ELSE
1013 pay_balance_pkg.set_context('JURISDICTION_CODE',p_jd_context);
1014 END IF;
1015 END IF;
1016 --
1017 hr_utility.set_location('pay_tax_bals_pkg',40);
1018 --
1019 l_assignment_id := p_assignment_id;
1020
1021 l_group_rb_report := NVL(pay_us_balance_view_pkg.get_session_var('GROUP_RB_REPORT'),'NA');
1022
1023 If l_group_rb_report <> 'NA' then
1024
1025 l_grp_aaid := nvl(pay_us_balance_view_pkg.get_session_var('GRP_AAID'),'NA');
1026
1027 l_group_rb_sdate :=
1028 nvl(pay_us_balance_view_pkg.get_session_var('GROUP_RB_SDATE'),sysdate);
1029 l_group_rb_edate :=
1030 nvl(pay_us_balance_view_pkg.get_session_var('GROUP_RB_EDATE'),l_end_of_time);
1031
1032
1033
1034 if l_grp_aaid = 'NA' then
1035
1036 open c_get_bg_id;
1037 fetch c_get_bg_id into l_temp_bg_id;
1038 close c_get_bg_id;
1039
1040 open c_get_min_aaid(l_group_rb_sdate,l_group_rb_edate,l_temp_bg_id);
1041 fetch c_get_min_aaid into l_session_aaid;
1042 close c_get_min_aaid;
1043
1044 pay_us_balance_view_pkg.set_session_var('GRP_AAID',to_char(l_session_aaid));
1045
1046 l_grp_aaid := to_char(l_session_aaid);
1047
1048 end if;
1049
1050 l_assignment_action_id := to_number(l_grp_aaid);
1051
1052 else
1053 l_assignment_action_id := p_assignment_action_id;
1054
1055 end if;
1056
1057 l_tax_type := p_tax_type;
1058 l_tax_balance_category := p_tax_balance_category;
1059 --
1060 -- Check if assignment exists at l_virtual_date, if using date mode
1061 -- Changed date format to DD-MON-YYYY, bug 612696
1062 l_virtual_date :=fnd_date.canonical_to_date(fnd_date.date_to_canonical(p_virtual_date));
1063 --
1064 hr_utility.set_location('pay_tax_bals_pkg',50);
1065 --
1066 IF (l_assignment_id is not null and l_virtual_date is not null) THEN
1067 --
1068 select count(0)
1069 into l_asg_exists
1070 from per_assignments_f
1071 where assignment_id = l_assignment_id
1072 and l_virtual_date between effective_start_date and effective_end_date;
1073 --
1074 -- if assignment doesn't exist ...
1075 --
1079 --
1076 hr_utility.set_location('pay_tax_bals_pkg',60);
1077 --
1078 IF l_asg_exists = 0 THEN
1080 -- get the termination date ...
1081 --
1082 select max(effective_end_date)
1083 into l_max_date
1084 from per_assignments_f
1085 where assignment_id = l_assignment_id;
1086 --
1087 -- get the date of the start of the time period in question
1088 --
1089 hr_utility.set_location('pay_tax_bals_pkg',70);
1090 --
1091 IF p_time_type = 'QTD' THEN
1092 l_bal_start_date := trunc(l_virtual_date,'Q');
1093 ELSIF p_time_type = 'MONTH' THEN
1094 l_bal_start_date := trunc(l_virtual_date,'MM');
1095 ELSIF p_time_type = 'YTD' THEN
1096 l_bal_start_date := trunc(l_virtual_date,'Y');
1097 ELSIF p_time_type = 'PTD' THEN
1098 select tp.start_date
1099 into l_bal_start_date
1100 from per_time_periods tp,
1101 per_assignments_f asg
1102 where asg.assignment_id = l_assignment_id
1103 and l_max_date between asg.effective_start_date and effective_end_date
1104 and asg.payroll_id = tp.payroll_id
1105 and l_virtual_date between tp.start_date and tp.end_date;
1106 END IF;
1107 --
1108 -- set the virtual date to termination date, or return 0 if terminated
1109 -- before the time period.
1110 --
1111 hr_utility.trace('Assignment was terminated on : ' || l_max_date);
1112 hr_utility.trace('Time period in question begins on : ' ||
1113 l_bal_start_date);
1114 --
1115 IF l_max_date < l_bal_start_date THEN
1116 return 0;
1117 ELSE
1118 l_virtual_date := l_max_date;
1119 END IF;
1120 --
1121 hr_utility.trace('Using new virtual date : ' || l_virtual_date);
1122 --
1123 END IF;
1124 END IF;
1125 --
1126 -- Convert "WITHHELD" to proper balance categories;
1127 --
1128 hr_utility.set_location('pay_tax_bals_pkg',80);
1129 --
1130 IF l_tax_balance_category = 'WITHHELD' THEN
1131 IF p_ee_or_er = 'ER' or l_tax_type = 'FUTA' THEN
1132 l_tax_balance_category := 'LIABILITY';
1133 ELSIF (l_tax_type = 'EIC'
1134 OR l_tax_type = 'STEIC') THEN
1135 l_tax_balance_category := 'ADVANCE';
1136 END IF;
1137 END IF;
1138 IF l_tax_balance_category = 'ADVANCED' THEN
1139 l_tax_balance_category := 'ADVANCE';
1140 END IF;
1141 --
1142 -- Check if illegal tax combo (FIT and TAXABLE, FUTA and SUBJ_NWHABLE, etc.)
1143 --
1144 hr_utility.set_location('pay_tax_bals_pkg',90);
1145 --
1146 IF (l_tax_type = 'FIT' or l_tax_type = 'SIT' or l_tax_type = 'COUNTY' or
1147 l_tax_type = 'CITY' or l_tax_type = 'EIC' or l_tax_type = 'HT' or
1148 l_tax_type = 'SCHOOL' or l_tax_type = 'STEIC' ) THEN -- income tax
1149 IF (l_tax_balance_category = 'TAXABLE' or
1150 l_tax_balance_category = 'EXCESS') THEN
1151 hr_utility.trace('Error: Illegal tax category for tax type');
1152 local_error('us_tax_balance',5);
1153 END IF;
1154 --
1155 -- return 0 for currently unsupported EIC balances.
1156 --
1157 -- 403b, 457 and Pre_Tax was added by skutteti for the pre-tax enhancements
1158 --
1159 IF l_tax_type = 'EIC' and (l_tax_balance_category = 'SUBJ_NWHABLE' -- or
1160 --l_tax_balance_category = '401_REDNS' or
1161 --l_tax_balance_category = '125_REDNS' or
1162 --l_tax_balance_category = '403_REDNS' or
1163 --l_tax_balance_category = '457_REDNS' or
1164 --l_tax_balance_category = 'PRE_TAX_REDNS' or
1165 --l_tax_balance_category = 'DEP_CARE_REDNS'
1166 ) THEN
1167 return 0;
1168 END IF;
1169 ELSE -- limit tax
1170 IF l_tax_balance_category = 'SUBJ_NWHABLE' THEN
1171 return 0;
1172 END IF;
1173 END IF;
1174 --
1175 hr_utility.set_location('pay_tax_bals_pkg',100);
1176 --
1177 l_ee_or_er := ltrim(rtrim(p_ee_or_er));
1178 --
1179 --------------- Some Error Checking -------------
1180 --
1181 --
1182 if (l_tax_type = 'FIT' or l_tax_type = 'SIT' or l_tax_type = 'CITY' or
1183 l_tax_type = 'COUNTY' or l_tax_type = 'EIC' or l_tax_type = 'SCHOOL'
1184 or l_tax_type = 'HT' or l_tax_type = 'WC' or l_tax_type = 'WC2' or
1185 l_tax_type = 'STEIC' ) THEN
1186 if l_ee_or_er = 'ER' THEN
1187 hr_utility.trace('Error: ER not allowed for tax type');
1188 local_error('us_tax_balance',6);
1189 else
1190 l_ee_or_er := NULL;
1191 end if;
1192 elsif (l_tax_type = 'FUTA') THEN
1193 if l_ee_or_er = 'EE' THEN
1194 hr_utility.trace('Error: EE not allowed for tax type');
1195 local_error('us_tax_balance',7);
1196 else
1197 l_ee_or_er := NULL;
1198 end if;
1199 elsif (l_tax_type = 'SS' or l_tax_type = 'MEDICARE' or l_tax_type = 'SDI' or
1200 l_tax_type = 'SUI') THEN
1201 if (l_ee_or_er <> 'EE' and l_ee_or_er <> 'ER') THEN
1202 hr_utility.trace('Error: EE or ER required for tax type');
1203 local_error('us_tax_balance',8);
1204 end if;
1205 end if;
1206 --
1207 hr_utility.set_location('pay_tax_bals_pkg',110);
1208 --
1209 -- Force space at end of this parameter if necessary
1210 --
1211 hr_utility.set_location('pay_tax_bals_pkg',120);
1212 --
1216 --
1213 IF l_ee_or_er IS NOT NULL THEN
1214 l_ee_or_er := rtrim(l_ee_or_er)||' ';
1215 END IF;
1217 -- Set up dimension strings
1218 --
1219 IF p_asg_type <> 'GRE' THEN
1220 l_dimension_string := p_asg_type||'_GRE_'||p_time_type;
1221 l_jd_dimension_string := p_asg_type||'_JD_GRE_'||p_time_type;
1222 ELSE
1223 --
1224 l_dimension_string := 'GRE_'||p_time_type;
1225 l_jd_dimension_string := 'GRE_JD_'||p_time_type;
1226 --
1227 --
1228 --
1229 -- If given payroll action id, get an asg action id from it to use. Else
1230 -- use the assignment_id and virtual date, since the get balance routine
1231 -- will be called in date mode.
1232 --
1233 --
1234 -- bug # gaz
1235 --
1236 IF (p_payroll_action_id is not null) THEN
1237 begin
1238 OPEN get_pay_action_id;
1239 FETCH get_pay_action_id INTO l_assignment_action_id;
1240 CLOSE get_pay_action_id;
1241 end;
1242 else
1243 if (p_assignment_action_id is null) then
1244 --
1245 -- Get a dummy assignment id to call the balance user exit in date mode.
1246 --
1247 declare
1248 l_bg_id number;
1249 l_count number;
1250 l_found boolean;
1251 check_asg number;
1252 begin
1253 pay_balance_pkg.set_context('DATE_EARNED',
1254 fnd_date.date_to_canonical(l_virtual_date));
1255 pay_balance_pkg.set_context('BALANCE_DATE',
1256 fnd_date.date_to_canonical(l_virtual_date));
1257 select business_group_id
1258 into l_bg_id
1259 from hr_organization_units
1260 where organization_id = p_gre_id_context;
1261 --
1262 -- Look to see if theres an assignment in the cache for
1263 -- this business group
1264 --
1265 l_count := 0;
1266 l_found := FALSE;
1267 while ((l_count < g_nxt_free_asgid) AND (l_found = FALSE)) loop
1268 if (l_bg_id = g_asgid_tbl_bgid(l_count)) then
1269 --
1270 -- OK, now check that the assignment is valid as of the
1271 -- virtual date.
1272 --
1273 begin
1274 select 1
1275 into check_asg
1276 from per_assignments_f paf
1277 where paf.assignment_id = g_asgid_tbl_id(l_count)
1278 and l_virtual_date between paf.effective_start_date
1279 and paf.effective_end_date;
1280 --
1281 l_assignment_id := g_asgid_tbl_id(l_count);
1282 l_found := TRUE;
1283 --
1284 exception
1285 when no_data_found then null;
1286 end;
1287 end if; ---- (l_bg_id = g_asgid_tbl_bgid(l_count))
1288 l_count := l_count + 1;
1289 end loop;
1290 --
1291 if (l_found = FALSE) then
1292 --
1293 -- OK, need to get an assignment from the database.
1294 --
1295 begin
1296 /* Modified query for performance enhancement (Bug 3343974). */
1297 select min(paa.assignment_id)
1298 into l_assignment_id
1299 from pay_assignment_actions paa,
1300 pay_payroll_actions pact,
1301 pay_payrolls_f ppf
1302 where pact.effective_date <= l_virtual_date
1303 and pact.payroll_action_id=paa.payroll_action_id
1304 and pact.action_type in ('R', 'Q', 'I', 'V', 'B')
1305 and paa.tax_unit_id = p_gre_id_context
1306 and ppf.payroll_id = pact.payroll_id
1307 and ppf.business_group_id = l_bg_id;
1308
1309 --
1310 -- Place the defined balance in cache.
1311 --
1312 g_asgid_tbl_bgid(g_nxt_free_asgid) := ltrim(rtrim(l_bg_id));
1313 g_asgid_tbl_id (g_nxt_free_asgid) :=
1314 ltrim(rtrim(l_assignment_id));
1315 g_nxt_free_asgid := g_nxt_free_asgid + 1;
1316 --
1317 exception when no_data_found then
1318 begin
1319 hr_utility.trace('Error: Failure to find defined balance');
1320 local_error('us_tax_balance',1);
1321 --
1322 end;
1323 end;
1324 end if; ---- (l_found = FALSE)
1325 end;
1326 end if; ---- (p_assignment_action_id is null)
1327 END IF; ---- (p_payroll_action_id is not null)
1328 END IF;
1329 --
1330 IF p_time_type = 'PAYMENTS' THEN
1331 --
1332 -- 360669 put PAYMENTS_JD back
1333 --
1334 l_jd_dimension_string := p_time_type||'_JD';
1335 l_dimension_string := p_time_type;
1336 --
1337 END IF;
1338 --
1339 --
1340 -- Check if the tax is federal or not.
1341 --
1342 SELECT count(0)
1343 INTO l_test
1344 FROM sys.dual
1345 WHERE l_tax_type in ('FIT','FUTA','MEDICARE','SS','EIC');
1346
1347
1348 IF ((p_jd_context IS NOT NULL) and
1349 (substr(p_jd_context,1,2) <> '00')) THEN
1350
1351 l_test := 0;
1352
1353 END IF;
1354
1355 --
1359 l_return_value := call_balance_user_exit ('GROSS_EARNINGS',
1356 IF l_test <> 0 THEN -- yes, the tax is federal
1357 --
1358 IF l_tax_balance_category = 'GROSS' THEN
1360 l_dimension_string,
1361 l_assignment_action_id,
1362 l_assignment_id,
1363 l_virtual_date,
1364 p_asg_type,
1365 p_gre_id_context,
1366 p_jd_context,
1367 l_asg_lock);
1368 --
1369 -- The if condition was added by subbu on 15-sep-2000
1370 --
1371 IF l_tax_type = 'FIT' AND l_return_value > 0 THEN
1372 l_return_value := l_return_value -
1373 call_balance_user_exit ('ALIEN_EXPAT_EARNINGS',
1374 l_dimension_string,
1375 l_assignment_action_id,
1376 l_assignment_id,
1377 l_virtual_date,
1378 p_asg_type,
1379 p_gre_id_context,
1380 p_jd_context,
1381 l_asg_lock) ;
1382 END IF;
1383 --
1384 ELSIF l_tax_balance_category = 'SUBJ_WHABLE' THEN
1385 l_return_value := call_balance_user_exit ('REGULAR_EARNINGS',
1386 l_dimension_string,
1387 l_assignment_action_id,
1388 l_assignment_id,
1389 l_virtual_date,
1390 p_asg_type,
1391 p_gre_id_context,
1392 p_jd_context,
1393 l_asg_lock)
1394 + call_balance_user_exit (
1395 'SUPPLEMENTAL_EARNINGS_FOR_'||l_tax_type,
1396 'SUBJECT_TO_TAX_'||l_dimension_string,
1397 l_assignment_action_id,
1398 l_assignment_id,
1399 l_virtual_date,
1400 p_asg_type,
1401 p_gre_id_context,
1402 p_jd_context,
1403 l_asg_lock);
1404 --
1405 ELSIF l_tax_balance_category = 'SUBJ_NWHABLE' THEN
1406 l_return_value := call_balance_user_exit (
1407 'SUPPLEMENTAL_EARNINGS_FOR_NW'||l_tax_type,
1408 'SUBJECT_TO_TAX_'||l_dimension_string,
1409 l_assignment_action_id,
1410 l_assignment_id,
1411 l_virtual_date,
1412 p_asg_type,
1413 p_gre_id_context,
1414 p_jd_context,
1415 l_asg_lock);
1416 --
1417 ELSIF l_tax_balance_category = '401_REDNS' THEN
1418 l_return_value := call_balance_user_exit ('DEF_COMP_401K',
1419 l_dimension_string,
1420 l_assignment_action_id,
1421 l_assignment_id,
1422 l_virtual_date,
1423 p_asg_type,
1424 p_gre_id_context,
1425 p_jd_context,
1426 l_asg_lock);
1427 --
1428 -- 337641
1429 -- check if balance 0 therefore no need to
1430 -- subtract subsequent balance
1431 --
1432 IF ( l_return_value <> 0 )
1433 THEN
1434 l_return_value := l_return_value
1435 - call_balance_user_exit ('DEF_COMP_401K_FOR_'||l_tax_type,
1436 'SUBJECT_TO_TAX_'||l_dimension_string,
1437 l_assignment_action_id,
1438 l_assignment_id,
1439 l_virtual_date,
1440 p_asg_type,
1441 p_gre_id_context,
1442 p_jd_context,
1443 l_asg_lock);
1444 --
1445 -- added by skutteti in Nov 2000, to remove the Non W2 portion
1446 --
1447 IF l_tax_type = 'FIT' THEN
1448 l_return_value := l_return_value -
1449 call_balance_user_exit(
1450 'FIT_NON_W2_DEF_COMP_401',
1451 l_dimension_string,
1452 l_assignment_action_id,
1453 l_assignment_id,
1454 l_virtual_date,
1455 p_asg_type,
1456 p_gre_id_context,
1460 END IF;
1457 p_jd_context,
1458 l_asg_lock);
1459 END IF;
1461 --
1462 -- 403b, 457 and Pre_Tax was added by skutteti for the pre-tax enhancements
1463 --
1464 ELSIF l_tax_balance_category = '403_REDNS' THEN
1465 l_return_value := call_balance_user_exit (
1466 'DEF_COMP_403B',
1467 l_dimension_string,
1468 l_assignment_action_id,
1469 l_assignment_id,
1470 l_virtual_date,
1471 p_asg_type,
1472 p_gre_id_context,
1473 p_jd_context,
1474 l_asg_lock);
1475 IF ( l_return_value <> 0 )
1476 THEN
1477 l_return_value := l_return_value - call_balance_user_exit (
1478 'DEF_COMP_403B_FOR_'||l_tax_type,
1479 'SUBJECT_TO_TAX_'||l_dimension_string,
1480 l_assignment_action_id,
1481 l_assignment_id,
1482 l_virtual_date,
1483 p_asg_type,
1484 p_gre_id_context,
1485 p_jd_context,
1486 l_asg_lock);
1487 --
1488 -- added by skutteti in Nov 2000, to remove the Non W2 portion
1489 --
1490 IF l_tax_type = 'FIT' THEN
1491 l_return_value := l_return_value -
1492 call_balance_user_exit(
1493 'FIT_NON_W2_DEF_COMP_403',
1494 l_dimension_string,
1495 l_assignment_action_id,
1496 l_assignment_id,
1497 l_virtual_date,
1498 p_asg_type,
1499 p_gre_id_context,
1500 p_jd_context,
1501 l_asg_lock);
1502 END IF;
1503 END IF;
1504 --
1505 -- Other Pretax was added by kthirmiy for the pre-tax enhancements
1506 --
1507 ELSIF l_tax_balance_category = 'OTHER_PRETAX_REDNS' THEN
1508 l_return_value := call_balance_user_exit (
1509 'OTHER_PRETAX',
1510 l_dimension_string,
1511 l_assignment_action_id,
1512 l_assignment_id,
1513 l_virtual_date,
1514 p_asg_type,
1515 p_gre_id_context,
1516 p_jd_context,
1517 l_asg_lock);
1518 IF ( l_return_value <> 0 )
1519 THEN
1520 l_return_value := l_return_value - call_balance_user_exit (
1521 'OTHER_PRETAX_FOR_'||l_tax_type,
1522 'SUBJECT_TO_TAX_'||l_dimension_string,
1523 l_assignment_action_id,
1524 l_assignment_id,
1525 l_virtual_date,
1526 p_asg_type,
1527 p_gre_id_context,
1528 p_jd_context,
1529 l_asg_lock);
1530 --
1531 -- added by skutteti in Nov 2000, to remove the Non W2 portion
1532 --
1533 IF l_tax_type = 'FIT' THEN
1534 l_return_value := l_return_value -
1535 call_balance_user_exit(
1536 'FIT_NON_W2_OTHER_PRETAX',
1537 l_dimension_string,
1538 l_assignment_action_id,
1539 l_assignment_id,
1540 l_virtual_date,
1541 p_asg_type,
1542 p_gre_id_context,
1543 p_jd_context,
1544 l_asg_lock);
1545 END IF;
1546 END IF;
1547
1548 ELSIF l_tax_balance_category = '457_REDNS' THEN
1549 l_return_value := call_balance_user_exit (
1550 'DEF_COMP_457',
1551 l_dimension_string,
1552 l_assignment_action_id,
1553 l_assignment_id,
1554 l_virtual_date,
1555 p_asg_type,
1556 p_gre_id_context,
1557 p_jd_context,
1558 l_asg_lock);
1562 'DEF_COMP_457_FOR_'||l_tax_type,
1559 IF ( l_return_value <> 0 )
1560 THEN
1561 l_return_value := l_return_value - call_balance_user_exit (
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 -- added by skutteti in Nov 2000, to remove the Non W2 portion
1573 --
1574 IF l_tax_type = 'FIT' THEN
1575 l_return_value := l_return_value -
1576 call_balance_user_exit(
1577 'FIT_NON_W2_DEF_COMP_457',
1578 l_dimension_string,
1579 l_assignment_action_id,
1580 l_assignment_id,
1581 l_virtual_date,
1582 p_asg_type,
1583 p_gre_id_context,
1584 p_jd_context,
1585 l_asg_lock);
1586 END IF;
1587 END IF;
1588 ELSIF l_tax_balance_category = 'PRE_TAX_REDNS' THEN
1589 l_return_value := call_balance_user_exit (
1590 'PRE_TAX_DEDUCTIONS',
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 IF ( l_return_value <> 0 )
1600 THEN
1601 l_return_value := l_return_value - call_balance_user_exit (
1602 'PRE_TAX_DEDUCTIONS_FOR_'||l_tax_type,
1603 'SUBJECT_TO_TAX_'||l_dimension_string,
1604 l_assignment_action_id,
1605 l_assignment_id,
1606 l_virtual_date,
1607 p_asg_type,
1608 p_gre_id_context,
1609 p_jd_context,
1610 l_asg_lock);
1611 --
1612 -- added by skutteti in Nov 2000, to remove the Non W2 portion
1613 --
1614 IF l_tax_type = 'FIT' THEN
1615 l_return_value := l_return_value -
1616 call_balance_user_exit(
1617 'FIT_NON_W2_PRE_TAX_DEDNS',
1618 l_dimension_string,
1619 l_assignment_action_id,
1620 l_assignment_id,
1621 l_virtual_date,
1622 p_asg_type,
1623 p_gre_id_context,
1624 p_jd_context,
1625 l_asg_lock);
1626 END IF;
1627 END IF;
1628 --
1629 ELSIF l_tax_balance_category = '125_REDNS' THEN
1630 l_return_value := call_balance_user_exit ('SECTION_125',
1631 l_dimension_string,
1632 l_assignment_action_id,
1633 l_assignment_id,
1634 l_virtual_date,
1635 p_asg_type,
1636 p_gre_id_context,
1637 p_jd_context,
1638 l_asg_lock);
1639 --
1640 -- 337641
1641 -- check if balance 0 therefore no need to
1642 -- subtract subsequent balance
1643 --
1644 IF ( l_return_value <> 0 )
1645 THEN
1646 l_return_value := l_return_value
1647 - call_balance_user_exit ('SECTION_125_FOR_'||l_tax_type,
1648 'SUBJECT_TO_TAX_'||l_dimension_string,
1649 l_assignment_action_id,
1650 l_assignment_id,
1651 l_virtual_date,
1652 p_asg_type,
1653 p_gre_id_context,
1654 p_jd_context,
1655 l_asg_lock);
1656 --
1657 -- added by skutteti in Nov 2000, to remove the Non W2 portion
1658 --
1659 IF l_tax_type = 'FIT' THEN
1660 l_return_value := l_return_value - call_balance_user_exit(
1664 l_assignment_id,
1661 'FIT_NON_W2_SECTION_125',
1662 l_dimension_string,
1663 l_assignment_action_id,
1665 l_virtual_date,
1666 p_asg_type,
1667 p_gre_id_context,
1668 p_jd_context,
1669 l_asg_lock);
1670 END IF;
1671 END IF;
1672 --
1673 ELSIF l_tax_balance_category = 'DEP_CARE_REDNS' THEN
1674 l_return_value := call_balance_user_exit ('DEPENDENT_CARE',
1675 l_dimension_string,
1676 l_assignment_action_id,
1677 l_assignment_id,
1678 l_virtual_date,
1679 p_asg_type,
1680 p_gre_id_context,
1681 p_jd_context,
1682 l_asg_lock);
1683 --
1684 -- 337641
1685 -- check if balance 0 therefore no need to
1686 -- subtract subsequent balance
1687 --
1688 IF ( l_return_value <> 0 )
1689 THEN
1690 l_return_value := l_return_value
1691 - call_balance_user_exit ('DEPENDENT_CARE_FOR_'||l_tax_type,
1692 'SUBJECT_TO_TAX_'||l_dimension_string,
1693 l_assignment_action_id,
1694 l_assignment_id,
1695 l_virtual_date,
1696 p_asg_type,
1697 p_gre_id_context,
1698 p_jd_context,
1699 l_asg_lock);
1700 --
1701 -- added by skutteti in Nov 2000, to remove the Non W2 portion
1702 --
1703 IF l_tax_type = 'FIT' THEN
1704 l_return_value := l_return_value -
1705 call_balance_user_exit(
1706 'FIT_NON_W2_DEPENDENT_CARE',
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 END IF;
1716 END IF;
1717 --
1718 ELSIF l_tax_balance_category = 'TAXABLE' THEN
1719
1720 hr_utility.trace('balance name sent = '||l_tax_type||'_'||
1721 l_ee_or_er||'TAXABLE');
1722 hr_utility.trace(' l_dimension_string = '||l_dimension_string);
1723
1724 l_return_value := call_balance_user_exit (l_tax_type||'_'||
1725 l_ee_or_er||'TAXABLE',
1726 l_dimension_string,
1727 l_assignment_action_id,
1728 l_assignment_id,
1729 l_virtual_date,
1730 p_asg_type,
1731 p_gre_id_context,
1732 p_jd_context,
1733 l_asg_lock);
1734 --
1735 ELSIF (l_tax_balance_category = 'WITHHELD' or
1736 l_tax_balance_category = 'LIABILITY' or
1737 l_tax_balance_category = 'ADVANCE') THEN
1738 l_return_value := call_balance_user_exit (
1739 l_tax_type||'_'||l_ee_or_er||l_tax_balance_category,
1740 l_dimension_string,
1741 l_assignment_action_id,
1742 l_assignment_id,
1743 l_virtual_date,
1744 p_asg_type,
1745 p_gre_id_context,
1746 p_jd_context,
1747 l_asg_lock);
1748 END IF;
1749 ELSE -- the tax is non-federal
1750 --
1751 -- if the tax balance is not derived, get it here.
1752 IF (l_tax_balance_category <> 'SUBJECT' and
1753 l_tax_balance_category <> 'EXEMPT' and
1754 l_tax_balance_category <> 'EXCESS' and
1755 l_tax_balance_category <> 'REDUCED_SUBJ_WHABLE') THEN
1756 --
1757 -- Use the CITY balances for HT if we don't want to see LIABILITY
1758 --
1759 IF (l_tax_type = 'HT') THEN
1760 IF (l_tax_balance_category <> 'WITHHELD') THEN
1761 l_tax_type := 'CITY';
1762 ELSE
1763 l_tax_type := 'HEAD TAX';
1764 END IF;
1765 END IF;
1766 --
1767 -- Added for workers comp
1768 If (l_tax_type = 'WC' ) THEN
1769 l_tax_type := 'WORKERS COMP';
1770 END IF;
1771 If (l_tax_type = 'WC2') THEN
1775
1772 l_tax_type := 'WORKERS COMP2';
1773 END IF;
1774 --
1776 hr_utility.trace ('The category is : '|| l_tax_balance_category);
1777 if l_tax_balance_category = 'NONE' then
1778
1779 l_return_value := call_balance_user_exit (
1780 l_tax_type,
1781 l_dimension_string,
1782 l_assignment_action_id,
1783 l_assignment_id,
1784 l_virtual_date,
1785 p_asg_type,
1786 p_gre_id_context,
1787 p_jd_context,
1788 l_asg_lock);
1789 else
1790 l_return_value := call_balance_user_exit (
1791 l_tax_type||'_'||l_ee_or_er||l_tax_balance_category,
1792 l_jd_dimension_string,
1793 l_assignment_action_id,
1794 l_assignment_id,
1795 l_virtual_date,
1796 p_asg_type,
1797 p_gre_id_context,
1798 p_jd_context,
1799 l_asg_lock);
1800
1801 end if;
1802
1803 --
1804 -- added by skutteti to remove the non w2 portion for pre tax REDNS
1805 --
1806 /*
1807 IF (l_return_value <> 0 AND
1808 l_tax_type = 'SIT' AND
1809 l_tax_balance_category like '%REDNS' ) THEN
1810 IF l_tax_balance_category = 'PRE_TAX_REDNS' THEN
1811 l_non_w2_cat := 'NON_W2_PRE_TAX_DEDNS';
1812 ELSIF l_tax_balance_category = '401_REDNS' THEN
1813 l_non_w2_cat := 'NON_W2_DEF_COMP_401';
1814 ELSIF l_tax_balance_category = '403_REDNS' THEN
1815 l_non_w2_cat := 'NON_W2_DEF_COMP_403';
1816 ELSIF l_tax_balance_category = '457_REDNS' THEN
1817 l_non_w2_cat := 'NON_W2_DEF_COMP_457';
1818 ELSIF l_tax_balance_category = '125_REDNS' THEN
1819 l_non_w2_cat := 'NON_W2_SECTION_125';
1820 ELSIF l_tax_balance_category = 'DEP_CARE_REDNS' THEN
1821 l_non_w2_cat := 'NON_W2_DEPENDENT_CARE';
1822 END IF;
1823 l_return_value := l_return_value - call_balance_user_exit (
1824 'SIT_'||l_non_w2_cat,
1825 l_jd_dimension_string,
1826 l_assignment_action_id,
1827 l_assignment_id,
1828 l_virtual_date,
1829 p_asg_type,
1830 p_gre_id_context,
1831 p_jd_context,
1832 l_asg_lock);
1833 END IF; -- end of Non W2 portion
1834 */
1835 -- tmehra 10-AUG-2001
1836 -- Above code has been commented out
1837 -- as it has become redundant due to
1838 -- the addition of the new -ve feeds
1839 -- to the SIT Redns
1840 -- Balances.
1841
1842 END IF;
1843 END IF;
1844 --
1845 IF l_tax_balance_category = 'SUBJECT' THEN
1846 l_return_value := us_tax_balance_rep(p_asg_lock,
1847 'SUBJ_WHABLE',
1848 l_tax_type,
1849 p_ee_or_er,
1850 p_time_type,
1851 p_asg_type,
1852 p_gre_id_context,
1853 p_jd_context,
1854 l_assignment_action_id,
1855 l_assignment_id,
1856 l_virtual_date)
1857 + us_tax_balance_rep(p_asg_lock,
1858 'SUBJ_NWHABLE',
1859 l_tax_type,
1860 p_ee_or_er,
1861 p_time_type,
1862 p_asg_type,
1863 p_gre_id_context,
1864 p_jd_context,
1865 l_assignment_action_id,
1866 l_assignment_id,
1867 l_virtual_date);
1868 --
1869 ELSIF l_tax_balance_category = 'EXEMPT' THEN
1870 l_return_value := us_tax_balance_rep(p_asg_lock,
1871 'GROSS',
1872 l_tax_type,
1873 p_ee_or_er,
1874 p_time_type,
1875 p_asg_type,
1876 p_gre_id_context,
1877 p_jd_context,
1878 l_assignment_action_id,
1879 l_assignment_id,
1880 l_virtual_date);
1881 --
1882 -- 337641
1883 -- check if balance 0 therefore no need to
1884 -- subtract subsequent balance
1885 --
1886 IF ( l_return_value <> 0 )
1887 THEN
1888 l_return_value := l_return_value
1892 p_ee_or_er,
1889 - us_tax_balance_rep(p_asg_lock,
1890 'SUBJECT',
1891 l_tax_type,
1893 p_time_type,
1894 p_asg_type,
1895 p_gre_id_context,
1896 p_jd_context,
1897 l_assignment_action_id,
1898 l_assignment_id,
1899 l_virtual_date);
1900 END IF;
1901 --
1902 -- Adding the following code for the NEW TRR which goes of the
1903 -- Balance Reporting Arch.
1904
1905 ELSIF (l_tax_balance_category = 'REDUCED_SUBJ_WHABLE') AND
1906 NVL(pay_us_balance_view_pkg.get_session_var('REPORT_TYPE'),'NOT_DEFINED') = 'W2' THEN
1907
1908 l_return_value := us_tax_balance_rep(p_asg_lock,
1909 'SUBJ_WHABLE',
1910 l_tax_type,
1911 p_ee_or_er,
1912 p_time_type,
1913 p_asg_type,
1914 p_gre_id_context,
1915 p_jd_context,
1916 l_assignment_action_id,
1917 l_assignment_id,
1918 l_virtual_date)
1919 + us_tax_balance_rep(p_asg_lock,
1920 'SUBJ_NWHABLE',
1921 l_tax_type,
1922 p_ee_or_er,
1923 p_time_type,
1924 p_asg_type,
1925 p_gre_id_context,
1926 p_jd_context,
1927 l_assignment_action_id,
1928 l_assignment_id,
1929 l_virtual_date);
1930 --
1931 IF ( l_return_value <> 0 ) THEN
1932
1933 l_return_value := l_return_value
1934 - us_tax_balance_rep(p_asg_lock,
1935 'PRE_TAX_REDNS',
1936 l_tax_type,
1937 p_ee_or_er,
1938 p_time_type,
1939 p_asg_type,
1940 p_gre_id_context,
1941 p_jd_context,
1942 l_assignment_action_id,
1943 l_assignment_id,
1944 l_virtual_date);
1945
1946 END IF;
1947 --
1948
1949 ELSIF l_tax_balance_category = 'REDUCED_SUBJ_WHABLE' THEN
1950 l_return_value := us_tax_balance_rep(p_asg_lock,
1951 'SUBJ_WHABLE',
1952 l_tax_type,
1953 p_ee_or_er,
1954 p_time_type,
1955 p_asg_type,
1956 p_gre_id_context,
1957 p_jd_context,
1958 l_assignment_action_id,
1959 l_assignment_id,
1960 l_virtual_date);
1961 --
1962 -- 337641
1963 -- check if balance 0 therefore no need to
1964 -- subtract subsequent balance
1965 --
1966 IF ( l_return_value <> 0 )
1967 THEN
1968 l_return_value := l_return_value
1969 --
1970 -- skutteti commented the following and added a new pre tax redns
1971 --
1972 --
1973 -- - us_tax_balance_rep(p_asg_lock,
1974 -- '401_REDNS',
1975 -- l_tax_type,
1976 -- p_ee_or_er,
1977 -- p_time_type,
1978 -- p_asg_type,
1979 -- p_gre_id_context,
1980 -- p_jd_context,
1981 -- l_assignment_action_id,
1982 -- l_assignment_id,
1983 -- l_virtual_date)
1984 -- - us_tax_balance_rep(p_asg_lock,
1985 -- '125_REDNS',
1986 -- l_tax_type,
1987 -- p_ee_or_er,
1988 -- p_time_type,
1989 -- p_asg_type,
1990 -- p_gre_id_context,
1991 -- p_jd_context,
1992 -- l_assignment_action_id,
1993 -- l_assignment_id,
1994 -- l_virtual_date)
1995 -- - us_tax_balance_rep(p_asg_lock,
1996 -- 'DEP_CARE_REDNS',
1997 -- l_tax_type,
1998 -- p_ee_or_er,
1999 -- p_time_type,
2000 -- p_asg_type,
2001 -- p_gre_id_context,
2002 -- p_jd_context,
2003 -- l_assignment_action_id,
2004 -- l_assignment_id,
2008 ---------------------------------
2005 -- l_virtual_date)
2006 ---------------------------------
2007 -- skutteti added the following
2009 - us_tax_balance_rep(p_asg_lock,
2010 'PRE_TAX_REDNS',
2011 l_tax_type,
2012 p_ee_or_er,
2013 p_time_type,
2014 p_asg_type,
2015 p_gre_id_context,
2016 p_jd_context,
2017 l_assignment_action_id,
2018 l_assignment_id,
2019 l_virtual_date);
2020 END IF;
2021 --
2022 ELSIF l_tax_balance_category = 'EXCESS' THEN
2023 l_return_value := us_tax_balance_rep(p_asg_lock,
2024 'REDUCED_SUBJ_WHABLE',
2025 l_tax_type,
2026 p_ee_or_er,
2027 p_time_type,
2028 p_asg_type,
2029 p_gre_id_context,
2030 p_jd_context,
2031 l_assignment_action_id,
2032 l_assignment_id,
2033 l_virtual_date);
2034 --
2035 -- 337641
2036 -- check if balance 0 therefore no need to
2037 -- subtract subsequent balance
2038 --
2039 IF ( l_return_value <> 0 )
2040 THEN
2041 l_return_value := l_return_value
2042 - us_tax_balance_rep(p_asg_lock,
2043 'TAXABLE',
2044 l_tax_type,
2045 p_ee_or_er,
2046 p_time_type,
2047 p_asg_type,
2048 p_gre_id_context,
2049 p_jd_context,
2050 l_assignment_action_id,
2051 l_assignment_id,
2052 l_virtual_date);
2053 END IF;
2054 END IF;
2055 --
2056 hr_utility.trace('Returning : ' || l_return_value);
2057 --
2058 return l_return_value;
2059 --
2060 END us_tax_balance;
2061 --
2062 BEGIN
2063 --
2064 -- Setup the Quarter To Date dimensions in the Cache.
2065 --
2066 g_dim_tbl_grp(0) := 'GRE_QTD';
2067 g_dim_tbl_asg(0) := 'ASG_GRE_QTD';
2068 g_dim_tbl_jdr(0) := 'N';
2069 g_dim_tbl_crs(0) := 'select distinct PAA.assignment_id ' ||
2070 'from pay_assignment_actions PAA, ' ||
2071 ' pay_payroll_actions PPA ' ||
2072 'where PAA.tax_unit_id = :TAX_UNIT_ID ' ||
2073 'and PPA.payroll_action_id = ' ||
2074 ' PAA.payroll_action_id ' ||
2075 'and PPA.effective_date >= ' ||
2076 'trunc(:DATE_EARNED,''Q'') ' ||
2077 'and PPA.effective_date <= ' ||
2078 ':DATE2_EARNED ' ||
2079 'and PPA.action_type in (''R'',''Q'',''I'',''B'',''V'') ';
2080 g_dim_tbl_vtd(0) := 'select max(PAF.effective_end_date) ' ||
2081 'from per_assignments_f PAF ' ||
2082 'where PAF.assignment_id = :ASSIGNMENT_ID ' ||
2083 'and PAF.payroll_id is not null ' ||
2084 'and PAF.effective_end_date between ' ||
2085 ' trunc(:DATE_EARNED,''Q'') and ' ||
2086 ' :DATE2_EARNED';
2087 g_dim_tbl_btt(0) := 'Q';
2088 --
2089 -- Setup the Year To Date dimensions in the Cache.
2090 --
2091 g_dim_tbl_grp(1) := 'GRE_YTD';
2092 g_dim_tbl_asg(1) := 'ASG_GRE_YTD';
2093 g_dim_tbl_jdr(1) := 'N';
2094 g_dim_tbl_crs(1) := 'select distinct PAA.assignment_id ' ||
2095 'from pay_assignment_actions PAA, ' ||
2096 ' pay_payroll_actions PPA ' ||
2097 'where PAA.tax_unit_id = :TAX_UNIT_ID ' ||
2098 'and PPA.payroll_action_id = ' ||
2099 ' PAA.payroll_action_id ' ||
2100 'and PPA.effective_date >= ' ||
2101 'trunc(:DATE_EARNED,''Y'') ' ||
2102 'and PPA.effective_date <= ' ||
2103 ':DATE2_EARNED ' ||
2104 'and PPA.action_type in (''R'',''Q'',''I'',''B'',''V'') ';
2105 g_dim_tbl_vtd(1) := 'select max(PAF.effective_end_date) ' ||
2106 'from per_assignments_f PAF ' ||
2107 'where PAF.assignment_id = :ASSIGNMENT_ID ' ||
2108 'and PAF.payroll_id is not null ' ||
2109 'and PAF.effective_end_date between ' ||
2110 ' trunc(:DATE_EARNED,''Y'') and ' ||
2111 ' :DATE2_EARNED';
2112 g_dim_tbl_btt(1) := 'Y';
2113 --
2114 -- Setup the Subject to Tax Year To Date dimensions in the Cache.
2115 --
2116 g_dim_tbl_grp(2) := 'SUBJECT_TO_TAX_GRE_YTD';
2117 g_dim_tbl_asg(2) := 'SUBJECT_TO_TAX_ASG_GRE_YTD';
2118 g_dim_tbl_jdr(2) := 'N';
2119 g_dim_tbl_crs(2) := 'select distinct PAA.assignment_id ' ||
2120 'from pay_assignment_actions PAA, ' ||
2121 ' pay_payroll_actions PPA ' ||
2122 'where PAA.tax_unit_id = :TAX_UNIT_ID ' ||
2123 'and PPA.payroll_action_id = ' ||
2124 ' PAA.payroll_action_id ' ||
2125 'and PPA.effective_date >= ' ||
2126 'trunc(:DATE_EARNED,''Y'') ' ||
2127 'and PPA.effective_date <= ' ||
2128 ':DATE2_EARNED ' ||
2129 'and PPA.action_type in (''R'',''Q'',''I'',''B'',''V'') ';
2133 'and PAF.payroll_id is not null ' ||
2130 g_dim_tbl_vtd(2) := 'select max(PAF.effective_end_date) ' ||
2131 'from per_assignments_f PAF ' ||
2132 'where PAF.assignment_id = :ASSIGNMENT_ID ' ||
2134 'and PAF.effective_end_date between ' ||
2135 ' trunc(:DATE_EARNED,''Y'') and ' ||
2136 ' :DATE2_EARNED';
2137 g_dim_tbl_btt(2) := 'Y';
2138 --
2139 -- Setup the Year To Date in Jurisdiction dimensions in the Cache.
2140 --
2141 g_dim_tbl_grp(3) := 'GRE_JD_YTD';
2142 g_dim_tbl_asg(3) := 'ASG_JD_GRE_YTD';
2143 g_dim_tbl_jdr(3) := 'Y';
2144 g_dim_tbl_crs(3) := 'select distinct PAR.assignment_id ' ||
2145 'from pay_balance_types PBT, ' ||
2146 ' pay_us_asg_reporting PAR ' ||
2147 'where PAR.tax_unit_id = :TAX_UNIT_ID ' ||
2148 'and PBT.balance_type_id = :BALANCE_TYPE_ID ' ||
2149 'and PBT.jurisdiction_level <> 0 ' ||
2150 'and substr(PAR.jurisdiction_code, 1, ' ||
2151 'PBT.jurisdiction_level) = ' ||
2152 'substr(:JURISDICTION_CODE, 1, ' ||
2153 'PBT.jurisdiction_level) ' ||
2154 'and exists (select 1 ' ||
2155 ' from pay_payroll_actions PPA, ' ||
2156 ' pay_assignment_actions PAA ' ||
2157 ' where PAA.assignment_id = ' ||
2158 'PAR.assignment_id ' ||
2159 ' and PAA.tax_unit_id = ' ||
2160 'PAR.tax_unit_id ' ||
2161 ' and PPA.payroll_action_id = ' ||
2162 'PAA.payroll_action_id ' ||
2163 ' and PPA.effective_date >= ' ||
2164 'trunc(:DATE_EARNED,''Y'') ' ||
2165 ' and PPA.effective_date <= ' ||
2166 ':DATE2_EARNED ' ||
2167 ' and PPA.action_type in (''R'',''Q'',''I'',''B'',''V'') )';
2168 g_dim_tbl_vtd(3) := 'select max(PAF.effective_end_date) ' ||
2169 'from per_assignments_f PAF ' ||
2170 'where PAF.assignment_id = :ASSIGNMENT_ID ' ||
2171 'and PAF.payroll_id is not null ' ||
2172 'and PAF.effective_end_date between ' ||
2173 ' trunc(:DATE_EARNED,''Y'') and ' ||
2174 ' :DATE2_EARNED';
2175 g_dim_tbl_btt(3) := 'Y';
2176 -- Set the next free cache space.
2177 g_nxt_free_dim := 4;
2178
2179
2180 -- hr_utility.trace_on(null,'tx');
2181 end pay_us_tax_bals_pkg;