[Home] [Help]
PACKAGE BODY: APPS.PAY_US_OVER_LIMIT_TAX_RPT_PKG
Source
1 PACKAGE BODY pay_us_over_limit_tax_rpt_pkg
2 /* $Header: pyusoltx.pkb 120.1 2006/11/17 07:09:10 ckesanap noship $ */
3 /* ******************************************************************
4 * *
5 * Copyright (C) 1993 Oracle Corporation. *
6 * All rights reserved. *
7 * *
8 * This material has been provided pursuant to an agreement *
9 * containing restrictions on its use. The material is also *
10 * protected by copyright law. No part of this material may *
11 * be copied or distributed, transmitted or transcribed, in *
12 * any form or by any means, electronic, mechanical, magnetic, *
13 * manual, or otherwise, or disclosed to third parties without *
14 * the express written permission of Oracle Corporation, *
15 * 500 Oracle Parkway, Redwood City, CA, 94065. *
16 * *
17 ******************************************************************
18
19 Name : pyusoltx.pkb
20
21 Description : This script is used by the Over Limit Report
22 for populating limits and retreining it from
23 the PL/SQL Table
24
25 Name :This package defines the cursors needed for OLT to run Multi-Threaded
26 This loads all the records that will appear in the report. This
27 data is being stored in pay_us_rpt_totals table.
28
29 Uses :
30
31 Change List
32 -----------
33 Date Name Vers Bug No Description
34 ----------- -------- ------ -------- -----------
35 01-NOV-1999 hzhao 110.0 Initial Version.
36 22-NOV-1999 hzhao 110.1 Moved Header below Create stmt.
37 15-AUG-2001 tmehra 110.2 Added support for 403b and 457
38 15-AUG-2001 tmehra 110.3 Updated the above remark
39
40 07-DEC-2001 irgonzal 115.4 Same as 115.2
41 115.3 changes are not required
42 due to performance issues.
43 04-FEB-2002 meshah 115.5 2166701 added procedure load_data,
44 load_state_taxes and
45 load_federal_taxes. Also changed
46 some cursors for performance.
47 05-FEB-2002 meshah 115.6 Added checkfile entry to the file.
48 05-MAR-2002 meshah 115.7 removed to_char from 401, 403 and
49 457 in hr_utility.
50 06-MAR-2002 meshah 115.8 cursor c_federal_taxes was refered
51 at a wrong place in load_federal_balance.
52 this was causing report to error out
53 with invalid cursor error.
54 20-MAR-2002 meshah 115.9 changed the date checking in load_data
55 and removed per_assignments_f table
56 from load_state_data.
57 27-MAR-2002 meshah 115.10 2280318 the call to get_tax_balances has been
58 changed. Instead of calling it in the
59 sql statement we are calling it in a
60 loop. Also the get_value function has
61 been changed from pay_us_balance_view_pkg
62 to pay_balance_pkg for Tax Group balances
63 and setting the TAX GROUP context instead
64 of the TAX UNIT ID.
65 30-APR-2002 meshah 115.11 2345031 cursor sel_aaid has been changed in load_data
66 we are now selecting effective_end_date for
67 the assignment. This date is then compared
68 with as_of_date and the lowest date is then
69 passed to the other procedures/functions.
70 This is required to handle terminated employees.
71 07-JUN-2002 sshetty 115.12 Added qualifier to func get_pqp_limit
72 and get_457_annual_limit.
73 25-NOV-2002 irgonzal 115.13 2664340 Added logic to handle Catchup balances.
74 03-DEC-2002 irgonzal 115.14 2664340 Modified Load_Fed_Catchup_Balance
75 procedure and ensure the balance_name
76 does not concatenate "_".
77 03-DEC-2002 irgonzal 115.15 2664340 Modified Load_Fed_Catchup_Balance
78 procedure and added upper function
79 when getting balance name.
80 17-DEC-2002 irgonzal 115.16 2714501 Modified load_data procedure: initialized
81 l_as_of_date within the "sel_aaid" loop.
82 19-DEC-2002 irgonzal 115.17 2693022 Added logic to handle USERRA balances.
83 18-MAY-2003 vgunasek 115.18 2938556 report rewrite including support for
84 new balance reporting architecture (run
85 balances) and multi threading.
86 06-JUN-2003 vgunasek 115.19 2938556 Changed code to check changes in Tax group
87 removed chnkno = 1 check to insert dummy
88 assignment action. Some Spell changes in
89 comments.
90 06-JUN-2003 vgunasek 115.20 2938556 Changed comments and fixed gscc errors.
91 12-JUN-2003 vgunasek 115.21 3002767 Initialised g_inserted_asg_action_id_flag
92 for all assignments. Made state query as
93 rule based.
94 19-JUN-2003 kaverma 115.22 3015312 Corrected the declaration of l_leg_param in
95 load_data
96 24-JUN-2003 kaverma 115.23 3018606 Corrected call to load_federal_taxes and load_state_taxes
97 in load_data procedure.
98 07-AUG-2003 sshetty 115.24 Added a check for Defined
99 Contrib Plan over limit.
100
101 05-SEP-2003 sdahiya 115.25 3118107 Added code in load_data procedure for insertion
102 of assignment action id.
103 02-JAN-2004 sshetty 115.26 3349624 Changed the Dimension name referenced
104 for DCP from PER_YTD to PER_GRE_YTD.
105 02-SEP-2004 tmehra 115.27 3770316 Removed the 403b and 457 Catchup
106 limit checking for tax_type = null
107 option.
108 16-NOV-2006 ckesanap 115.28 4521358 Added the 'Roth 401k' and 'Roth 403b' over limit
109 check. The balance values of both deferred 401k and Roth 401k
110 are combined and then checked for exceeding the annual limit.
111 Similarly for 403b.
112 ***************************************************************************/
113 AS
114
115 --------------------- GLOBAL variables ----------------------------------
116 l_start_date pay_payroll_actions.start_date%type;
117 l_end_date pay_payroll_actions.effective_date%type;
118 l_business_group_id pay_payroll_actions.business_group_id%type;
119 l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
120 l_effective_date pay_payroll_actions.effective_date%type;
121 l_action_type pay_payroll_actions.action_type%type;
122 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
123 l_assignment_id pay_assignment_actions.assignment_id%type;
124 l_tax_unit_id hr_organization_units.organization_id%type;
125 l_gre_name hr_organization_units.name%type;
126 l_organization_id hr_organization_units.organization_id%type;
127 l_org_name hr_organization_units.name%type;
128 l_location_id hr_locations.location_id%type;
129 l_location_code hr_locations.location_code%type;
130 l_leg_param pay_payroll_actions.legislative_parameters%type;--Bug3015312
131 l_leg_start_date date;
132 l_leg_end_date date;
133 t_gre_id number(15);
134 t_payroll_action_id pay_payroll_actions.payroll_action_id%type; --:PACTID
135 l_row_count number :=0;
136 l_tax_type varchar2(240);
137 l_tax_group hr_organization_information.org_information5%type;
138 l_date_prm varchar2(20);
139 l_as_of_date date;
140
141 -- bug # 2938556
142 l_prev_tg hr_organization_information.org_information5%type;
143 l_tg_changed varchar2(1):= 'Y';
144 g_get_param varchar2(1):= 'Y';
145 g_inserted_asg_action_id_flag varchar2(1):= 'N';
146 g_inserted_asg_action_id number;
147 l_ppa_finder varchar2(240);
148 p_insert_done_flag varchar2(1):= 'N';
149 TYPE t_rec_bal IS RECORD
150 (balance_id ff_user_entities.creator_id%TYPE,
151 balance_name varchar2(1000),
152 tax_type varchar2(50)
153 );
154
155 TYPE t_balance IS TABLE OF t_rec_bal INDEX BY BINARY_INTEGER;
156
157 t_fed_balance_list t_balance;
158 t_state_balance_list t_balance;
159
160
161 ----------------------- END global variables ------------------------------+
162 --
163
164 --------------- START tax group info --------------------------------------+
165 --
166 function tax_group(p_tax_unit_id number) return VARCHAR2
167 is
168 cursor c_tax_group(cp_tax_unit_id in number) is
169 select org_information5
170 from hr_organization_information
171 where organization_id = cp_tax_unit_id
172 and org_information_context = 'Federal Tax Rules';
173
174 lv_tax_group varchar2(240);
175
176 begin
177 open c_tax_group(p_tax_unit_id);
178 fetch c_tax_group into lv_tax_group;
179 close c_tax_group;
180
181 if ltrim(rtrim(lv_tax_group)) is null then
182 return('xXx');
183 else
184 return(lv_tax_group);
185 end if;
186 end Tax_group;
187 --
188
189 --Removed procedure poplulate_state_limits_table as part of bug # 2938556
190 --
191 --
192 -- Function to get the balances
193 --
194 FUNCTION get_taxable_balance (
195 p_assignment_id IN NUMBER
196 ,p_effective_date IN DATE
197 ,p_assignment_action_id IN NUMBER
198 ,p_tax_unit_id IN NUMBER
199 ,p_tax_group IN VARCHAR2
200 ,p_jurisdiction_code IN VARCHAR2
201 ,p_tax_type IN VARCHAR2
202 ,p_balance_id IN NUMBER
203 )
204 RETURN NUMBER IS
205 ln_balance_value number := 0;
206 ln_catchup_balance_value number := 0;
207 -- lv_tax_group hr_organization_information.org_information5%TYPE ;
208 lv_tg_balance_name varchar2(1000);
209 ln_defined_bal_id ff_user_entities.creator_id%TYPE;
210 lv_bal_flag varchar2(30) := ' ';
211 --
212 -- #2664340
213 lv_balance_name varchar2(1000);
214
215 l_catchup_bal_name varchar2(50);
216 l_catchup_bal_id number;
217 l_catchup_count number :=1;
218 ln_catchup_defined_bal_id ff_user_entities.creator_id%TYPE;
219
220 --
221
222 cursor c_def_bal_id(cp_balance_name in varchar2) is
223 select creator_id
224 from ff_database_items fdi,
225 ff_user_entities fue
226 where fue.user_entity_id = fdi.user_entity_id
227 and fue.creator_type='B'
228 and fdi.user_name = cp_balance_name;
229 --
230 -- -----------------------------------------------------------------------+
231 -- # 2664340 Catchup Balance Processing +
232 -- -----------------------------------------------------------------------+
233 -- This procdure was removed as it is no longer required due to
234 -- report rewrite bug # 2938556
235
236 -- -----------------------------------------------------------------------+
237 -- Main get_taxable_balance +
238 -- -----------------------------------------------------------------------+
239 BEGIN
240 --lv_tax_group := null;
241 ln_defined_bal_id := p_balance_id;
242 if p_tax_type in ('FUTA', 'SS ER', 'SS EE') and p_tax_group <> 'xXx' then
243
244 pay_balance_pkg.set_context('TAX_GROUP', p_tax_group);
245
246 ln_balance_value := pay_balance_pkg.get_value(
247 p_assignment_id => p_assignment_id
248 ,p_defined_balance_id => ln_defined_bal_id
249 ,p_virtual_date => p_effective_date
250 );
251
252 elsif p_tax_type in ('FUTA', 'SS ER', 'SS EE') and p_tax_group = 'xXx'then
253 hr_utility.trace('in to get_taxable_balance defined_balance_id : ' || to_char(ln_defined_bal_id));
254
255 ln_balance_value := pay_balance_pkg.get_value(
256 p_defined_balance_id => ln_defined_bal_id
257 ,p_assignment_action_id => p_assignment_action_id
258 ,p_tax_unit_id => p_tax_unit_id
259 ,p_jurisdiction_code => p_jurisdiction_code
260 ,p_source_id => null
261 ,p_tax_group => null
262 ,p_date_earned =>null
263 );
264
265 elsif p_tax_type in ('401K') then
266 hr_utility.trace('in to get_taxable_balance defined_balance_id : ' || to_char(ln_defined_bal_id));
267 ln_balance_value := pay_balance_pkg.get_value(
268 p_defined_balance_id => ln_defined_bal_id
269 ,p_assignment_action_id => p_assignment_action_id
270 ,p_tax_unit_id => p_tax_unit_id
271 ,p_jurisdiction_code => p_jurisdiction_code
272 ,p_source_id => null
273 ,p_tax_group => null
274 ,p_date_earned =>null
275 );
276
277 l_catchup_bal_name := 'DEF_COMP_401K_CATCHUP_PER_GRE_YTD';
278 open c_def_bal_id(l_catchup_bal_name);
279 fetch c_def_bal_id into ln_catchup_defined_bal_id;
280 close c_def_bal_id;
281
282 hr_utility.trace('in to get_taxable_balance defined_balance_id : ' || to_char(ln_catchup_defined_bal_id));
283
284 ln_catchup_balance_value := pay_balance_pkg.get_value(
285 p_defined_balance_id => ln_catchup_defined_bal_id
286 ,p_assignment_action_id => p_assignment_action_id
287 ,p_tax_unit_id => p_tax_unit_id
288 ,p_jurisdiction_code => p_jurisdiction_code
289 ,p_source_id => null
290 ,p_tax_group => null
291 ,p_date_earned =>null
292 );
293
294 ln_balance_value := nvl(ln_balance_value,0) -
295 nvl(ln_catchup_balance_value,0);
296
297 ---- Added for bug 4521358
298
299 elsif p_tax_type in ('401K ROTH') then
300 hr_utility.trace('in to get_taxable_balance defined_balance_id : ' || to_char(ln_defined_bal_id));
301 ln_balance_value := pay_balance_pkg.get_value(
302 p_defined_balance_id => ln_defined_bal_id
303 ,p_assignment_action_id => p_assignment_action_id
304 ,p_tax_unit_id => p_tax_unit_id
305 ,p_jurisdiction_code => p_jurisdiction_code
306 ,p_source_id => null
307 ,p_tax_group => null
308 ,p_date_earned =>null
309 );
310 ------
311
312 elsif p_tax_type in ('403B') then
313 hr_utility.trace('in to get_taxable_balance defined_balance_id : ' || to_char(ln_defined_bal_id));
317 ,p_tax_unit_id => p_tax_unit_id
314 ln_balance_value := pay_balance_pkg.get_value(
315 p_defined_balance_id => ln_defined_bal_id
316 ,p_assignment_action_id => p_assignment_action_id
318 ,p_jurisdiction_code => p_jurisdiction_code
319 ,p_source_id => null
320 ,p_tax_group => null
321 ,p_date_earned =>null
322 );
323
324 l_catchup_bal_name := 'DEF_COMP_403B_CATCHUP_PER_GRE_YTD';
325 open c_def_bal_id(l_catchup_bal_name);
326 fetch c_def_bal_id into ln_catchup_defined_bal_id;
327 close c_def_bal_id;
328
329 hr_utility.trace('in to get_taxable_balance defined_balance_id : ' || to_char(ln_catchup_defined_bal_id));
330
331 ln_catchup_balance_value := pay_balance_pkg.get_value(
332 p_defined_balance_id => ln_catchup_defined_bal_id
333 ,p_assignment_action_id => p_assignment_action_id
334 ,p_tax_unit_id => p_tax_unit_id
335 ,p_jurisdiction_code => p_jurisdiction_code
336 ,p_source_id => null
337 ,p_tax_group => null
338 ,p_date_earned =>null
339 );
340
341 ln_balance_value := nvl(ln_balance_value,0) -
342 nvl(ln_catchup_balance_value,0);
343
344 ---- Added for bug 4521358
345
346 elsif p_tax_type in ('403B ROTH') then
347 hr_utility.trace('in to get_taxable_balance defined_balance_id : ' || to_char(ln_defined_bal_id));
348 ln_balance_value := pay_balance_pkg.get_value(
349 p_defined_balance_id => ln_defined_bal_id
350 ,p_assignment_action_id => p_assignment_action_id
351 ,p_tax_unit_id => p_tax_unit_id
352 ,p_jurisdiction_code => p_jurisdiction_code
353 ,p_source_id => null
354 ,p_tax_group => null
355 ,p_date_earned =>null
356 );
357
358 -----------
359
360 elsif p_tax_type in ('457') then
361 hr_utility.trace('in to get_taxable_balance defined_balance_id : ' || to_char(ln_defined_bal_id));
362 ln_balance_value := pay_balance_pkg.get_value(
363 p_defined_balance_id => ln_defined_bal_id
364 ,p_assignment_action_id => p_assignment_action_id
365 ,p_tax_unit_id => p_tax_unit_id
366 ,p_jurisdiction_code => p_jurisdiction_code
367 ,p_source_id => null
368 ,p_tax_group => null
369 ,p_date_earned =>null
370 );
371
372 l_catchup_bal_name := 'DEF_COMP_457_CATCHUP_PER_GRE_YTD';
373 open c_def_bal_id(l_catchup_bal_name);
374 fetch c_def_bal_id into ln_catchup_defined_bal_id;
375 close c_def_bal_id;
376
377 hr_utility.trace('in to get_taxable_balance defined_balance_id : ' || to_char(ln_catchup_defined_bal_id));
378
379 ln_catchup_balance_value := pay_balance_pkg.get_value(
380 p_defined_balance_id => ln_catchup_defined_bal_id
381 ,p_assignment_action_id => p_assignment_action_id
382 ,p_tax_unit_id => p_tax_unit_id
383 ,p_jurisdiction_code => p_jurisdiction_code
384 ,p_source_id => null
385 ,p_tax_group => null
386 ,p_date_earned =>null
387 );
388
389 ln_balance_value := nvl(ln_balance_value,0) -
390 nvl(ln_catchup_balance_value,0);
391
392 elsif p_tax_type in ('SDI ER', 'SDI EE', 'SUI ER', 'SUI EE') then
393 hr_utility.trace('in to get_taxable_balance defined_balance_id : ' || to_char(ln_defined_bal_id));
394 ln_balance_value := pay_balance_pkg.get_value(
395 p_defined_balance_id => ln_defined_bal_id
396 ,p_assignment_action_id => p_assignment_action_id
397 ,p_tax_unit_id => p_tax_unit_id
398 ,p_jurisdiction_code => p_jurisdiction_code
399 ,p_source_id => null
400 ,p_tax_group => null
401 ,p_date_earned =>null
402 );
403
404 elsif p_tax_type in ( '401K CATCHUP', '403B CATCHUP', '457 CATCHUP','DCP') then -- #2664340
405
406 hr_utility.trace('in to get_taxable_balance defined_balance_id : ' || to_char(ln_defined_bal_id));
407
408 ln_balance_value := pay_balance_pkg.get_value(
409 p_defined_balance_id => ln_defined_bal_id
410 ,p_assignment_action_id => p_assignment_action_id
411 ,p_tax_unit_id => p_tax_unit_id
412 ,p_jurisdiction_code => p_jurisdiction_code
413 ,p_source_id => null
414 ,p_tax_group => null
415 ,p_date_earned =>null
416 );
417 end if;
418 if ln_balance_value is null then
419 ln_balance_value := 0;
420 end if;
421 return (ln_balance_value);
422
423 END get_taxable_balance;
424
425 -- End of get_taxable_balance_procedure
426
427
428 --
429 -- Added for bug 4521358.
430 -- Function to get the p_balance_id of Roth 401k, Roth 403b, 401k and 403b
434 --
431 -- to combine Deferred and Roth balances and check for Over Limit Report.
432 -- If the tax type is of deferred deduction, it returns the balance_id of Roth deduction.
433 -- Similarly if the tax type is Roth deduction, it return the balance_id of deferred.
435 FUNCTION get_roth_balance_id ( p_tax_type IN VARCHAR2 )
436
437 RETURN NUMBER IS
438
439 ln_balance_id number := 0 ;
440
441 def_401k_balance_name varchar2(40) := 'DEF_COMP_401K_PER_GRE_YTD';
442 def_403b_balance_name varchar2(40) := 'DEF_COMP_403B_PER_GRE_YTD';
443 roth_401k_balance_name varchar2(40) := 'ROTH_401K_AMOUNT_PER_GRE_YTD';
444 roth_403b_balance_name varchar2(40) := 'ROTH_403B_AMOUNT_PER_GRE_YTD';
445
446 cursor c_def_bal_id(cp_balance_name in varchar2) is
447 select creator_id
448 from ff_database_items fdi,
449 ff_user_entities fue
450 where fue.user_entity_id = fdi.user_entity_id
451 and fue.creator_type='B'
452 and fdi.user_name = cp_balance_name;
453
454 BEGIN
455
456 hr_utility.trace('In get_roth_balance_id, p_tax_type: '|| p_tax_type);
457
458 if p_tax_type = '401K' then
459 open c_def_bal_id (roth_401k_balance_name);
460 fetch c_def_bal_id into ln_balance_id;
461 close c_def_bal_id;
462
463 hr_utility.trace('Balance name: '||roth_401k_balance_name||', balance id: '||ln_balance_id);
464
465 elsif p_tax_type = '403B' then
466 open c_def_bal_id (roth_403b_balance_name);
467 fetch c_def_bal_id into ln_balance_id;
468 close c_def_bal_id;
469
470 hr_utility.trace('Balance name: '||roth_401k_balance_name||', balance id: '||ln_balance_id);
471
472 elsif p_tax_type = '401K ROTH' then
473 open c_def_bal_id (def_401k_balance_name);
474 fetch c_def_bal_id into ln_balance_id;
475 close c_def_bal_id;
476
477 hr_utility.trace('Balance name: '||roth_401k_balance_name||', balance id: '||ln_balance_id);
478
479 elsif p_tax_type = '403B ROTH' then
480 open c_def_bal_id (def_403b_balance_name);
481 fetch c_def_bal_id into ln_balance_id;
482 close c_def_bal_id;
483
484 hr_utility.trace('Balance name: '||roth_401k_balance_name||', balance id: '||ln_balance_id);
485
486 end if;
487
488 return (ln_balance_id) ;
489
490 END get_roth_balance_id ;
491
492 -- End of get_roth_balance_id function.
493
494
495 -- Procedure to populate federal balances table.
496 -- This is included as part of bug # 2938556. The plsql table will be populated with
497 -- values required depending on the tax type and tax group. these values will be used
498 -- to calculate over limit values
499
500 procedure populate_fed_balance_list (p_tax_type IN VARCHAR2,
501 p_tax_group IN VARCHAR2) is
502
503 cursor c_def_bal_id(cp_balance_name in varchar2) is
504 select creator_id
505 from ff_database_items fdi,
506 ff_user_entities fue
507 where fue.user_entity_id = fdi.user_entity_id
508 and fue.creator_type='B'
509 and fdi.user_name = cp_balance_name;
510
511 l_count number := 1;
512
513 begin
514
515 hr_utility.set_location('IN populate_fed_balance_list ',350);
516
517 if p_tax_type is null then
518 if p_tax_group = 'xXx' then
519 t_fed_balance_list(1).balance_name := 'FUTA_TAXABLE_PER_GRE_YTD';
520 t_fed_balance_list(1).tax_type := 'FUTA';
521 t_fed_balance_list(2).balance_name := 'SS_ER_TAXABLE_PER_GRE_YTD';
522 t_fed_balance_list(2).tax_type := 'SS ER';
523 t_fed_balance_list(3).balance_name := 'SS_EE_TAXABLE_PER_GRE_YTD';
524 t_fed_balance_list(3).tax_type := 'SS EE';
525 t_fed_balance_list(4).balance_name := 'DEF_COMP_401K_PER_GRE_YTD';
526 t_fed_balance_list(4).tax_type := '401K';
527 t_fed_balance_list(5).balance_name := 'DEF_COMP_403B_PER_GRE_YTD';
528 t_fed_balance_list(5).tax_type := '403B';
529 t_fed_balance_list(6).balance_name := 'DEF_COMP_457_PER_GRE_YTD';
530 t_fed_balance_list(6).tax_type := '457';
531 t_fed_balance_list(7).balance_name := 'DEF_COMP_401K_CATCHUP_PER_GRE_YTD';
532 t_fed_balance_list(7).tax_type := '401K CATCHUP';
533 /* -- part of the bug #3770316 Fix. tmehra
534 t_fed_balance_list(8).balance_name := 'DEF_COMP_403B_CATCHUP_PER_GRE_YTD';
535 t_fed_balance_list(8).tax_type := '403B CATCHUP';
536 t_fed_balance_list(9).balance_name := 'DEF_COMP_457_CATCHUP_PER_GRE_YTD';
537 t_fed_balance_list(9).tax_type := '457 CATCHUP';
538 */
539 t_fed_balance_list(8).balance_name := 'EE_SRS_DCP_CONTRIBUTION_PER_GRE_YTD';
540 t_fed_balance_list(8).tax_type := 'DCP';
541
542 -- Added for bug 4521358
543
544 t_fed_balance_list(9).tax_type := '401K ROTH';
545 t_fed_balance_list(9).balance_name := 'ROTH_401K_AMOUNT_PER_GRE_YTD';
546 t_fed_balance_list(10).tax_type := '403B ROTH';
547 t_fed_balance_list(10).balance_name := 'ROTH_403B_AMOUNT_PER_GRE_YTD';
548
549 else
550 t_fed_balance_list(1).balance_name := 'FUTA_TAXABLE_PER_TG_YTD';
551 t_fed_balance_list(1).tax_type := 'FUTA';
552 t_fed_balance_list(2).balance_name := 'SS_ER_TAXABLE_PER_TG_YTD';
553 t_fed_balance_list(2).tax_type := 'SS ER';
557 t_fed_balance_list(4).tax_type := '401K';
554 t_fed_balance_list(3).balance_name := 'SS_EE_TAXABLE_PER_TG_YTD';
555 t_fed_balance_list(3).tax_type := 'SS EE';
556 t_fed_balance_list(4).balance_name := 'DEF_COMP_401K_PER_GRE_YTD';
558 t_fed_balance_list(5).balance_name := 'DEF_COMP_403B_PER_GRE_YTD';
559 t_fed_balance_list(5).tax_type := '403B';
560 t_fed_balance_list(6).balance_name := 'DEF_COMP_457_PER_GRE_YTD';
561 t_fed_balance_list(6).tax_type := '457';
562 t_fed_balance_list(7).balance_name := 'DEF_COMP_401K_CATCHUP_PER_GRE_YTD';
563 t_fed_balance_list(7).tax_type := '401K CATCHUP';
564 /* -- part of the bug #3770316 Fix. tmehra
565 t_fed_balance_list(8).balance_name := 'DEF_COMP_403B_CATCHUP_PER_GRE_YTD';
566 t_fed_balance_list(8).tax_type := '403B CATCHUP';
567 t_fed_balance_list(9).balance_name := 'DEF_COMP_457_CATCHUP_PER_GRE_YTD';
568 t_fed_balance_list(9).tax_type := '457 CATCHUP';
569 */
570 t_fed_balance_list(8).balance_name := 'EE_SRS_DCP_CONTRIBUTION_PER_GRE_YTD';
571 t_fed_balance_list(8).tax_type := 'DCP';
572
573 -- Added for bug 4521358
574
575 t_fed_balance_list(9).tax_type := '401K ROTH';
576 t_fed_balance_list(9).balance_name := 'ROTH_401K_AMOUNT_PER_GRE_YTD';
577 t_fed_balance_list(10).tax_type := '403B ROTH';
578 t_fed_balance_list(10).balance_name := 'ROTH_403B_AMOUNT_PER_GRE_YTD';
579
580 end if;
581 elsif p_tax_type = 'FUTA' and p_tax_group = 'xXx' then
582 t_fed_balance_list(1).balance_name := 'FUTA_TAXABLE_PER_GRE_YTD';
583 t_fed_balance_list(1).tax_type := p_tax_type;
584 elsif p_tax_type = 'SS ER' and p_tax_group = 'xXx' then
585 t_fed_balance_list(1).balance_name := 'SS_ER_TAXABLE_PER_GRE_YTD';
586 t_fed_balance_list(1).tax_type := p_tax_type;
587 elsif p_tax_type = 'SS EE' and p_tax_group = 'xXx' then
588 t_fed_balance_list(1).balance_name := 'SS_EE_TAXABLE_PER_GRE_YTD';
589 t_fed_balance_list(1).tax_type := p_tax_type;
590 elsif p_tax_type = 'FUTA' and p_tax_group <> 'xXx' then
591 t_fed_balance_list(1).balance_name := 'FUTA_TAXABLE_PER_TG_YTD';
592 t_fed_balance_list(1).tax_type := p_tax_type;
593 elsif p_tax_type = 'SS ER' and p_tax_group <> 'xXx' then
594 t_fed_balance_list(1).balance_name := 'SS_ER_TAXABLE_PER_TG_YTD';
595 t_fed_balance_list(1).tax_type := p_tax_type;
596 elsif p_tax_type = 'SS EE' and p_tax_group <> 'xXx' then
597 t_fed_balance_list(1).balance_name := 'SS_EE_TAXABLE_PER_TG_YTD';
598 t_fed_balance_list(1).tax_type := p_tax_type;
599 elsif p_tax_type = '401K' then
600 t_fed_balance_list(1).balance_name := 'DEF_COMP_401K_PER_GRE_YTD';
601 t_fed_balance_list(1).tax_type := p_tax_type;
602 elsif p_tax_type = '403B' then
603 t_fed_balance_list(1).balance_name := 'DEF_COMP_403B_PER_GRE_YTD';
604 t_fed_balance_list(1).tax_type := p_tax_type;
605 elsif p_tax_type = '457' then
606 t_fed_balance_list(1).balance_name := 'DEF_COMP_457_PER_GRE_YTD';
607 t_fed_balance_list(1).tax_type := p_tax_type;
608 elsif p_tax_type = '401K CATCHUP' then
609 t_fed_balance_list(1).balance_name := 'DEF_COMP_401K_CATCHUP_PER_GRE_YTD';
610 t_fed_balance_list(1).tax_type := p_tax_type;
611 elsif p_tax_type = '403B CATCHUP' then
612 t_fed_balance_list(1).balance_name := 'DEF_COMP_403B_CATCHUP_PER_GRE_YTD';
613 t_fed_balance_list(1).tax_type := p_tax_type;
614
615 --- Added for bug 4521358
616
617 elsif p_tax_type = '401K ROTH' then
618 t_fed_balance_list(1).balance_name := 'ROTH_401K_AMOUNT_PER_GRE_YTD';
619 t_fed_balance_list(1).tax_type := p_tax_type;
620 elsif p_tax_type = '403B ROTH' then
621 t_fed_balance_list(1).balance_name := 'ROTH_403B_AMOUNT_PER_GRE_YTD';
622 t_fed_balance_list(1).tax_type := p_tax_type;
623 -----
624 elsif p_tax_type = '457 CATCHUP' then
625 t_fed_balance_list(1).balance_name := 'DEF_COMP_457_CATCHUP_PER_GRE_YTD';
626 t_fed_balance_list(1).tax_type := p_tax_type;
627 elsif p_tax_type = 'DCP' then
628 t_fed_balance_list(1).balance_name := 'EE_SRS_DCP_CONTRIBUTION_PER_GRE_YTD';
629 t_fed_balance_list(1).tax_type := p_tax_type;
630 else
631 null; -- Not a Federal Tax
632 end if;
633
634 l_count := t_fed_balance_list.count;
635 for i in 1 .. l_count loop
636 open c_def_bal_id (t_fed_balance_list(i).balance_name);
637 fetch c_def_bal_id into t_fed_balance_list(i).balance_id;
638 close c_def_bal_id;
639 hr_utility.trace('Inserting fed_balance_list table with balance name ' || t_fed_balance_list(i).balance_name );
640 hr_utility.trace('Inserting fed_balance_list table with balance id ' || t_fed_balance_list(i).balance_id );
641 end loop;
642 hr_utility.set_location('OUT populate_fed_balance_list ',360);
643 end populate_fed_balance_list;
644
645 -- end of procedure populate_fed_balance_list
646
647
648 -- This is included as part of bug # 2938556. The plsql table will be populated with
649 -- values required depending on the tax type. These values will be used
650 -- to calculate over limit values
651
652 procedure populate_state_balance_list (p_tax_type IN VARCHAR2) is
653
654 cursor c_def_bal_id(cp_balance_name in varchar2) is
655 select creator_id
656 from ff_database_items fdi,
657 ff_user_entities fue
658 where fue.user_entity_id = fdi.user_entity_id
662 l_count number := 1;
659 and fue.creator_type='B'
660 and fdi.user_name = cp_balance_name;
661
663
664 begin
665
666 hr_utility.set_location('IN populate_state_balance_list ',370);
667 if p_tax_type is null then
668 t_state_balance_list(1).balance_name := 'SDI_ER_TAXABLE_PER_JD_GRE_YTD';
669 t_state_balance_list(1).tax_type := 'SDI ER';
670 t_state_balance_list(2).balance_name := 'SDI_EE_TAXABLE_PER_JD_GRE_YTD';
671 t_state_balance_list(2).tax_type := 'SDI EE';
672 t_state_balance_list(3).balance_name := 'SUI_ER_TAXABLE_PER_JD_GRE_YTD';
673 t_state_balance_list(3).tax_type := 'SUI ER';
674 t_state_balance_list(4).balance_name := 'SUI_EE_TAXABLE_PER_JD_GRE_YTD';
675 t_state_balance_list(4).tax_type := 'SUI EE';
676 elsif p_tax_type = 'SDI ER' then
677 t_state_balance_list(1).balance_name := 'SDI_ER_TAXABLE_PER_JD_GRE_YTD';
678 t_state_balance_list(1).tax_type := p_tax_type;
679 elsif p_tax_type = 'SDI EE' then
680 t_state_balance_list(1).balance_name := 'SDI_EE_TAXABLE_PER_JD_GRE_YTD';
681 t_state_balance_list(1).tax_type := p_tax_type;
682 elsif p_tax_type = 'SUI ER' then
683 t_state_balance_list(1).balance_name := 'SUI_ER_TAXABLE_PER_JD_GRE_YTD';
684 t_state_balance_list(1).tax_type := p_tax_type;
685 elsif p_tax_type = 'SUI EE' then
686 t_state_balance_list(1).balance_name := 'SUI_EE_TAXABLE_PER_JD_GRE_YTD';
687 t_state_balance_list(1).tax_type := p_tax_type;
688 else
689 null; -- Not a State Tax
690 end if;
691
692 l_count := t_state_balance_list.count;
693 for i in 1 .. l_count loop
694 open c_def_bal_id (t_state_balance_list(i).balance_name);
695 fetch c_def_bal_id into t_state_balance_list(i).balance_id;
696 close c_def_bal_id;
697 hr_utility.trace('Inserting state_balance_list table with balance name ' || t_state_balance_list(i).balance_name );
698 hr_utility.trace('Inserting state_balance_list table with balance id ' || t_state_balance_list(i).balance_id );
699 end loop;
700 hr_utility.set_location('OUT populate_state_balance_list ',380);
701 end populate_state_balance_list;
702
703 -- End of the procedure populate_state_balance_list
704
705
706 -- -----------------------------------------------------------------------+
707 -- END of function get_taxable_balance +
708 -- -----------------------------------------------------------------------+
709
710 FUNCTION get_state_limit(
711 p_state_code IN NUMBER
712 ,p_tax_type IN VARCHAR2 )
713 RETURN NUMBER is
714 ln_state_tax_limit NUMBER;
715
716 BEGIN
717 if p_tax_type = 'SDI EE' then
718 ln_state_tax_limit := pay_us_payroll_utils.ltr_state_tax_info(p_state_code).sdi_ee_limit;
719 elsif p_tax_type = 'SDI ER' then
720 ln_state_tax_limit := pay_us_payroll_utils.ltr_state_tax_info(p_state_code).sdi_er_limit;
721 elsif p_tax_type = 'SUI EE' then
722 ln_state_tax_limit := pay_us_payroll_utils.ltr_state_tax_info(p_state_code).sui_ee_limit;
723 elsif p_tax_type = 'SUI ER' then
724 ln_state_tax_limit := pay_us_payroll_utils.ltr_state_tax_info(p_state_code).sui_er_limit;
725 end if;
726
727 if ln_state_tax_limit is null then
728 ln_state_tax_limit := 0;
729 end if;
730 hr_utility.trace('Returning tax limit ' || p_tax_type || ' for the state code ' || to_char (p_state_code) ||' with
731 the value as ' ||to_char(ln_state_tax_limit));
732 return (ln_state_tax_limit);
733 EXCEPTION
734 when others then
735 return 0;
736 END get_state_limit;
737
738 -------------------- START STATE TAXES ---------------------------------
739 --
740 procedure load_state_taxes (p_asg_id number,
741 p_ppa_finder varchar2,
742 p_asg_action_id number,
743 p_as_of_date date,
744 p_tax_unit_id varchar2,
745 p_tax_type varchar2,
746 p_tax_group varchar2,
747 p_chnkno number) is
748
749 cursor C_state_taxes is
750 select /*+RULE */
751 pus.state_abbrev,
752 pest.state_code,
753 pest.jurisdiction_code
754 from
755 pay_us_emp_state_tax_rules_f pest,
756 pay_us_states pus
757 where
758 pest.assignment_id = p_asg_id
759 and p_as_of_date between pest.effective_start_date
760 and pest.effective_end_date
761 and pus.state_code = pest.state_code
762 order by state_abbrev;
763 --
764 -- local variables
765 l_over_limit number;
766 l_tax_person_id number;
767 l_state_abbrev varchar2(240);
768 l_state_code varchar2(10);
769 l_state_tax_type varchar2(240);
770 l_state_tax_limit number;
771 l_state_taxable_value number;
772
773 l_jurisdiction_code pay_us_emp_state_tax_rules_f.jurisdiction_code%type;
774
775 -- #2938556
776 l_assignment_number per_assignments_f.assignment_number%type;
777 l_person_id per_assignments_f.person_id%type;
778 l_first_name per_people_f.first_name%type;
779 l_middle_name per_people_f.middle_names%type;
780 l_last_name per_people_f.last_name%type;
781 l_ssn per_people_f.national_identifier%type;
785 l_bal_id number;
782 l_prev_asg_act_id pay_assignment_actions.assignment_action_id%type;
783 new_asg_act_id pay_assignment_actions.assignment_action_id%type;
784 l_count number := 1;
786
787
788 --
789 --
790 begin
791 hr_utility.set_location('IN load_state_taxes ',300);
792
793 /* do not execute this procedure if the tax type is for federal */
794
795 if (( p_tax_type is null ) or
796 ( p_tax_type not in ('FUTA', 'SS ER', 'SS EE', '401K','403B','457','401K CATCHUP','403B CATCHUP','457 CATCHUP'))
797 ) then
798
799 open c_state_taxes;
800 loop
801 fetch c_state_taxes into
802 l_state_abbrev,
803 l_state_code,
804 l_jurisdiction_code;
805
806 hr_utility.trace('Number of STATE TAXES Records fetched = '||to_char(c_state_taxes%ROWCOUNT));
807 exit when c_state_taxes%notfound;
808
809 l_count := t_state_balance_list.count;
810
811
812 for i in 1 .. l_count loop
813
814 l_state_tax_type := t_state_balance_list(i).tax_type;
815 l_bal_id := t_state_balance_list(i).balance_id;
816
817 if (l_state_abbrev = 'NY' or l_state_abbrev = 'HI') then
818 if l_state_tax_type in('SDI EE','SDI ER') then
819 l_state_tax_type := 'xx';
820 end if;
821 end if;
822 hr_utility.trace('STATE = '|| l_state_abbrev);
823 hr_utility.trace('Tax Type : ' || l_state_tax_type);
824 hr_utility.trace('Balance Id : ' || l_bal_id);
825
826 l_state_taxable_value := get_taxable_balance(
827 p_asg_id,
828 p_as_of_date,
829 p_asg_action_id,
830 p_tax_unit_id,
831 p_tax_group,
832 l_jurisdiction_code,
833 l_state_tax_type,
834 l_bal_id);
835 l_state_tax_limit := get_state_limit( l_state_code,
836 l_state_tax_type);
837
838 -- calculate the over limit amount
839 hr_utility.trace('State Taxable Value is : '|| to_char(l_state_taxable_value));
840 hr_utility.trace('State Tax Limit is : ' ||to_char(l_state_tax_limit));
841
842 l_over_limit := nvl(l_state_taxable_value,0) - nvl(l_state_tax_limit,0);
843
844 -- need to insert those rows that have over limit > 0
845
846 if nvl(l_over_limit,0) > 0 then
847 if (l_prev_asg_act_id = p_asg_action_id) then
848
849 null;
850
851 else
852
853 l_prev_asg_act_id := p_asg_action_id;
854 if (g_inserted_asg_action_id_flag = 'N') then
855 select pay_assignment_actions_s.nextval
856 into new_asg_act_id
857 from dual;
858
859 -- insert the action record.
860 hr_utility.trace('New Assignment action id: '||to_char(new_asg_act_id));
861 hr_utility.trace('Assignment id: '||to_char(p_asg_id));
862
863 hr_nonrun_asact.insact(new_asg_act_id,p_asg_id,t_payroll_action_id,p_chnkno,p_tax_unit_id);
864 g_inserted_asg_action_id_flag := 'Y';
865 else
866 g_inserted_asg_action_id_flag := 'N';
867 new_asg_act_id := g_inserted_asg_action_id;
868 hr_utility.trace('Inserted Assignment action id: '||to_char(new_asg_act_id));
869 end if;
870 select paf.assignment_number
871 ,paf.person_id
872 ,ppf.first_name
873 ,ppf.middle_names
874 ,ppf.last_name
875 ,ppf.national_identifier
876 into l_assignment_number
877 ,l_person_id
878 ,l_first_name
879 ,l_middle_name
880 ,l_last_name
881 ,l_ssn
882 from per_assignments_f paf
883 ,per_people_f ppf
884 where paf.assignment_id = p_asg_id
885 and paf.effective_end_date = ( select max(paf1.effective_end_date)
886 from per_assignments_f paf1
887 where paf1.assignment_id = paf.assignment_id
888 and paf1.effective_start_date <= p_as_of_date)
889 and ppf.person_id = paf.person_id
890 and p_as_of_date between ppf.effective_start_date and ppf.effective_end_date;
891 end if;
892
893 insert into pay_us_rpt_totals
894 ( attribute2, -- :PACTID
895 session_id,
896 tax_unit_id,
897 gre_name, organization_name,
898 location_name, state_code, state_abbrev
899 ,value7 -- asg. id
900 ,attribute7 -- tax type
901 ,value1 -- state_taxable_value
902 ,value2 -- state_over_limit
903 ,value3 -- state_tax_limit
904 ,attribute5 -- New Assignment Action id
908 ,attribute13 -- Middle name
905 ,attribute10 -- Assignment number
906 ,attribute11 -- Person id
907 ,attribute12 -- First name
909 ,attribute14 -- Last name
910 ,attribute15 -- SSN
911 )
912 values
913 (t_payroll_action_id
914 ,p_ppa_finder
915 ,p_tax_unit_id
916 ,l_gre_name, l_org_name
917 ,l_location_code, l_state_code, l_state_abbrev
918 ,p_asg_id
919 ,l_state_tax_type
920 ,nvl(l_state_taxable_value,0)
921 ,l_over_limit
922 ,nvl(l_state_tax_limit,0)
923 ,new_asg_act_id
924 ,l_assignment_number
925 ,l_person_id
926 ,l_first_name
927 ,l_middle_name
928 ,l_last_name
929 ,l_ssn
930 );
931
932 hr_utility.trace('Inserting a record in pay_us_rpt_totals for the employee ' ||l_last_name || ' ' ||
933 l_first_name );
934 hr_utility.trace('Inserted chunk number ' ||p_chnkno || ' for tax type ' || l_state_tax_type );
935
936 end if;
937 end loop;
938
939 end loop;
940 close c_state_taxes;
941
942 end if;
943
944 hr_utility.set_location('OUT load_state_taxes ',350);
945
946 exception
947 when others then
948 hr_utility.trace('Error occurred load_state_taxes ...' ||SQLERRM);
949 raise;
950 end load_state_taxes;
951 -- -----------------------------------------------------------------------+
952 -- START FEDERAL TAXES +
953 -- -----------------------------------------------------------------------+
954 --
955 -- Get USERRA balance and checks whether person is over the limit
956 -- Returns new over limit balance (2693022)
957 --
958 procedure Process_USERRA_balance(p_tax_type varchar2,
959 p_over_limit IN OUT nocopy number,
960 p_business_group_id number,
961 p_as_of_date date,
962 p_asg_action_id number,
963 p_tax_unit_id number) is
964 --
965 l_temp_over_limit number;
966 l_bal_feed_exists varchar2(1);
967 l_bal_name pay_balance_types.balance_name%TYPE;
968 l_bal_type_id pay_balance_types.balance_type_id%TYPE;
969 l_bal_dimension varchar2(150) := 'Person within Government Reporting Entity Year to Date';
970 l_def_balance_id ff_user_entities.creator_id%TYPE;
971 l_value number;
972
973 --
974 cursor csr_balance_type(cp_tax_type varchar2
975 , cp_bg_id number
976 ) is
977 select bal.balance_type_id, bal.balance_name
978 from pay_balance_types bal
979 where bal.balance_name like cp_tax_type
980 and bal.business_group_id = cp_bg_id
981 order by bal.balance_name DESC;
982 --
983 cursor csr_balance_feed(cp_balance_type_id number, cp_bg_id number) is
984 select 'Y'
985 from pay_balance_feeds_f
986 where balance_type_id = cp_balance_type_id
987 and business_group_id = cp_bg_id;
988 --
989 cursor csr_def_balance(cp_balance_type_id number
990 ,cp_bg_id number
991 ,cp_dimension varchar2) is
992 select def.defined_balance_id
993 from pay_defined_balances def
994 ,pay_balance_dimensions dim
995 where def.balance_type_id = cp_balance_type_id
996 and def.business_group_id = cp_bg_id
997 and def.balance_dimension_id = dim.balance_dimension_id
998 and dim.legislation_code = 'US'
999 and dim.dimension_name = cp_dimension;
1000 --
1001 begin
1002 l_temp_over_limit := p_over_limit;
1003 -- Check whether balances exist
1004 open csr_balance_type('W2 USERRA '||p_tax_type||'%'
1005 ,p_business_group_id);
1006 fetch csr_balance_type into l_bal_type_id, l_bal_name;
1007 --
1008 Loop
1009
1010 exit when (csr_balance_type%NOTFOUND)
1011 or (l_temp_over_limit <= 0);
1012 --
1013 -- Check whether the balance is fed
1014 open csr_balance_feed(l_bal_type_id, p_business_group_id);
1015 fetch csr_balance_feed into l_bal_feed_exists;
1016 --
1017 if csr_balance_feed%FOUND then
1018 --
1019 -- Get the balance and update the over limit amount
1020 --
1021 open csr_def_balance(l_bal_type_id
1022 , p_business_group_id
1023 , l_bal_dimension);
1024 fetch csr_def_balance into l_def_balance_id;
1025 --
1026 if csr_def_balance%FOUND then
1027 -- Get balance
1028 pay_us_balance_view_pkg.set_context('TAX_UNIT_ID', p_tax_unit_id);
1029 l_value := nvl(pay_us_balance_view_pkg.get_value
1030 (
1031 p_defined_balance_id => l_def_balance_id
1032 ,p_assignment_action_id => p_asg_action_id
1033 ),0);
1034 -- Update over limit amount
1035 l_temp_over_limit := l_temp_over_limit - l_value;
1036 end if;
1040 --
1037 close csr_def_balance;
1038 end if;
1039 close csr_balance_feed;
1041 fetch csr_balance_type into l_bal_type_id, l_bal_name;
1042 end loop;
1043 close csr_balance_type;
1044 p_over_limit := l_temp_over_limit;
1045
1046 end Process_USERRA_balance;
1047 -- -----------------------------------------------------------------------+
1048 -- load_federal_taxes +
1049 -- -----------------------------------------------------------------------+
1050 procedure load_federal_taxes (p_asg_id number,
1051 p_ppa_finder varchar2,
1052 p_asg_action_id number,
1053 p_as_of_date date,
1054 p_tax_unit_id number,
1055 p_tax_type varchar2,
1056 p_business_group_id number, -- #2693022
1057 p_tax_group varchar2,
1058 p_chnkno number) is
1059
1060 --
1061 -- local variables
1062 --
1063 l_fed_taxable_value number;
1064 l_futa_wage_limit number;
1065 l_ss_ee_wage_limit number;
1066 l_ss_er_wage_limit number;
1067 l_fed_401k_limit varchar2(240);
1068 l_fed_403b_limit varchar2(240);
1069 l_fed_457_limit varchar2(240);
1070 l_over_limit number := 0;
1071 l_fed_tax_limit number;
1072 l_fed_tax_type varchar2(240);
1073 l_fed_roth_taxable_value number; --- Added the last 2 for bug 4521358
1074 l_fed_roth_balance_id number;
1075
1076
1077
1078 -- #2938556
1079 l_assignment_number per_assignments_f.assignment_number%type;
1080 l_person_id per_assignments_f.person_id%type;
1081 l_first_name per_people_f.first_name%type;
1082 l_middle_name per_people_f.middle_names%type;
1083 l_last_name per_people_f.last_name%type;
1084 l_ssn per_people_f.national_identifier%type;
1085 l_fed_401k_catchup_limit number;
1086 l_fed_403b_catchup_limit number;
1087 l_fed_457_catchup_limit number;
1088 l_dcp_limit number;
1089 l_prev_asg_act_id pay_assignment_actions.assignment_action_id%type;
1090 new_asg_act_id pay_assignment_actions.assignment_action_id%type;
1091 l_count number := 1;
1092 l_bal_id number;
1093 --
1094 --
1095 begin
1096 hr_utility.set_location('IN load_federal_taxes ',400);
1097
1098 /* do not execute this procedure if the tax type is for state */
1099
1100 if (( p_tax_type is null ) or
1101 ( p_tax_type in ('FUTA', 'SS ER', 'SS EE', '401K','403B','401K ROTH','403B ROTH','457','401K CATCHUP','403B CATCHUP','457 CATCHUP','DCP'))
1102 ) then
1103
1104
1105 /* We should consider getting the federal taxes along with 403 and 457 limits
1106 once and put them in PL/SQL table for performance */
1107
1108 -- get 401K, SS and FUTA limits.
1109 -- #2938556
1110 l_futa_wage_limit := pay_us_payroll_utils.ltr_fed_tax_info(1).futa_wage;
1111 l_ss_ee_wage_limit := pay_us_payroll_utils.ltr_fed_tax_info(1).ss_ee_wage;
1112 l_ss_er_wage_limit := pay_us_payroll_utils.ltr_fed_tax_info(1).ss_er_wage;
1113 l_fed_401k_limit := pay_us_payroll_utils.ltr_fed_tax_info(1).p401_limit;
1114 l_fed_403b_limit := pay_us_payroll_utils.ltr_fed_tax_info(1).p403_limit;
1115 l_fed_457_limit := pay_us_payroll_utils.ltr_fed_tax_info(1).p457_limit;
1116 l_fed_401k_catchup_limit := pay_us_payroll_utils.ltr_fed_tax_info(1).catchup_401k;
1117 l_fed_403b_catchup_limit := pay_us_payroll_utils.ltr_fed_tax_info(1).catchup_403b;
1118 l_fed_457_catchup_limit := pay_us_payroll_utils.ltr_fed_tax_info(1).catchup_457;
1119 l_dcp_limit := pay_us_payroll_utils.ltr_fed_tax_info(1).dcp_limit;
1120
1121 hr_utility.trace('FUTA : ' || to_char(l_futa_wage_limit));
1122 hr_utility.trace('SS EE : ' || to_char(l_ss_ee_wage_limit));
1123 hr_utility.trace('SS ER : ' || to_char(l_ss_er_wage_limit));
1124 hr_utility.trace('401K : ' || l_fed_401k_limit);
1125 hr_utility.trace('403B : ' || l_fed_403B_limit);
1126 hr_utility.trace('457 : ' || l_fed_457_limit);
1127 hr_utility.trace('Tax Type : ' || p_tax_type);
1128 --
1129
1130 l_count := t_fed_balance_list.count;
1131
1132 for i in 1 .. l_count loop
1133 l_fed_tax_type := t_fed_balance_list(i).tax_type;
1134 hr_utility.trace('Tax Type : ' || l_fed_tax_type);
1135 l_bal_id := t_fed_balance_list(i).balance_id;
1136
1137 l_fed_taxable_value := get_taxable_balance(
1138 p_asg_id,
1139 p_as_of_date,
1140 p_asg_action_id,
1141 p_tax_unit_id,
1142 p_tax_group,
1143 null,
1144 l_fed_tax_type,
1145 l_bal_id);
1146
1147 -- calculate the over limit amount
1148 if l_fed_tax_type = 'FUTA' then
1149 l_over_limit := nvl(l_fed_taxable_value,0) - nvl(l_futa_wage_limit,0);
1150 l_fed_tax_limit := nvl(l_futa_wage_limit,0);
1151 elsif l_fed_tax_type = 'SS ER' then
1152 l_over_limit := nvl(l_fed_taxable_value,0) - nvl(l_ss_er_wage_limit,0);
1153 l_fed_tax_limit := nvl(l_ss_er_wage_limit,0);
1154 elsif l_fed_tax_type = 'SS EE' then
1155 l_over_limit := nvl(l_fed_taxable_value,0) - nvl(l_ss_ee_wage_limit,0);
1156 l_fed_tax_limit := nvl(l_ss_ee_wage_limit,0);
1157 elsif l_fed_tax_type = '401K' then
1158 l_fed_tax_limit := nvl(to_number(l_fed_401k_limit),0);
1159
1160 -- Added for bug 4521358
1161 -- We get the balance_id for the 401k Roth deduction
1162 -- and pass that to get_taxable_balance. Then, we add the
1163 -- balance values of both deferred and Roth deductions to
1164 -- check for the over limit report.
1165
1166 l_fed_roth_balance_id := get_roth_balance_id(l_fed_tax_type);
1167 l_fed_roth_taxable_value := get_taxable_balance(
1168 p_asg_id,
1169 p_as_of_date,
1170 p_asg_action_id,
1171 p_tax_unit_id,
1172 p_tax_group,
1173 null,
1174 '401K ROTH',
1175 l_fed_roth_balance_id);
1176 l_fed_taxable_value := l_fed_taxable_value + l_fed_roth_taxable_value;
1177
1178 -----
1179 l_over_limit := nvl(l_fed_taxable_value,0) - l_fed_tax_limit;
1180 if l_over_limit > 0 then -- #2693022
1181 Process_USERRA_balance(l_fed_tax_type,
1182 l_over_limit,
1183 p_business_group_id,
1184 p_as_of_date,
1185 p_asg_action_id,
1186 p_tax_unit_id
1187 );
1188 end if;
1189
1190 elsif l_fed_tax_type = '403B' then
1191 l_fed_tax_limit := nvl(to_number(l_fed_403b_limit),0);
1192
1193 -- Added for bug 4521358
1194
1195 l_fed_roth_balance_id := get_roth_balance_id(l_fed_tax_type);
1196 l_fed_roth_taxable_value := get_taxable_balance(
1197 p_asg_id,
1198 p_as_of_date,
1199 p_asg_action_id,
1200 p_tax_unit_id,
1201 p_tax_group,
1202 null,
1203 '403B ROTH',
1204 l_fed_roth_balance_id);
1205 l_fed_taxable_value := l_fed_taxable_value + l_fed_roth_taxable_value;
1206
1207 -----
1208
1209 l_over_limit := nvl(l_fed_taxable_value,0) - l_fed_tax_limit;
1210 if l_over_limit > 0 then -- #2693022
1211 Process_USERRA_balance(l_fed_tax_type,
1212 l_over_limit,
1213 p_business_group_id,
1214 p_as_of_date,
1215 p_asg_action_id,
1216 p_tax_unit_id
1217 );
1218 end if;
1219
1220 -- Added for bug 4521358. Here the l_count is being checked because
1221 -- when no tax_type is selected while passing it as a parameter, the
1222 -- employees will appear twice, once in the '401K' tax_type and again
1223 -- in '401K Roth' tax_type.
1224
1225 elsif ( l_fed_tax_type = '401K ROTH' and l_count = 1 ) then
1226 l_fed_tax_limit := nvl(to_number(l_fed_401k_limit),0);
1227 l_fed_roth_balance_id := get_roth_balance_id(l_fed_tax_type);
1228 l_fed_roth_taxable_value := get_taxable_balance(
1229 p_asg_id,
1230 p_as_of_date,
1231 p_asg_action_id,
1232 p_tax_unit_id,
1233 p_tax_group,
1234 null,
1235 '401K',
1236 l_fed_roth_balance_id);
1237 l_fed_taxable_value := l_fed_taxable_value + l_fed_roth_taxable_value;
1238 l_over_limit := nvl(l_fed_taxable_value,0) - l_fed_tax_limit;
1239 if l_over_limit > 0 then -- #2693022
1240 Process_USERRA_balance(l_fed_tax_type,
1241 l_over_limit,
1242 p_business_group_id,
1243 p_as_of_date,
1244 p_asg_action_id,
1245 p_tax_unit_id
1246 );
1247 end if;
1248
1249 elsif ( l_fed_tax_type = '403B ROTH' and l_count = 1 ) then
1250 l_fed_tax_limit := nvl(to_number(l_fed_403b_limit),0);
1251 l_fed_roth_balance_id := get_roth_balance_id(l_fed_tax_type);
1252 l_fed_roth_taxable_value := get_taxable_balance(
1253 p_asg_id,
1257 p_tax_group,
1254 p_as_of_date,
1255 p_asg_action_id,
1256 p_tax_unit_id,
1258 null,
1259 '403B',
1260 l_fed_roth_balance_id);
1261 l_fed_taxable_value := l_fed_taxable_value + l_fed_roth_taxable_value;
1262 l_over_limit := nvl(l_fed_taxable_value,0) - l_fed_tax_limit;
1263 if l_over_limit > 0 then -- #2693022
1264 Process_USERRA_balance(l_fed_tax_type,
1265 l_over_limit,
1266 p_business_group_id,
1267 p_as_of_date,
1268 p_asg_action_id,
1269 p_tax_unit_id
1270 );
1271 end if;
1272 -------------------------
1273
1274 elsif l_fed_tax_type = '457' then
1275 l_fed_tax_limit := nvl(to_number(l_fed_457_limit),0);
1276 l_over_limit := nvl(l_fed_taxable_value,0) - l_fed_tax_limit;
1277 if l_over_limit > 0 then -- #2693022
1278 Process_USERRA_balance(l_fed_tax_type,
1279 l_over_limit,
1280 p_business_group_id,
1281 p_as_of_date,
1282 p_asg_action_id,
1283 p_tax_unit_id
1284 );
1285 end if;
1286 --
1287 -- # 2664340: Process Catchup balances
1288 elsif l_fed_tax_type = '401K CATCHUP' then
1289
1290 l_fed_tax_limit := nvl(l_fed_401k_catchup_limit,0);
1291 l_over_limit := nvl(l_fed_taxable_value,0) - l_fed_tax_limit;
1292
1293 elsif l_fed_tax_type = '403B CATCHUP' then
1294
1295 l_fed_tax_limit := nvl(l_fed_403b_catchup_limit,0);
1296 l_over_limit := nvl(l_fed_taxable_value,0) - l_fed_tax_limit;
1297
1298 elsif l_fed_tax_type = '457 CATCHUP' then
1299
1300 l_fed_tax_limit := nvl(l_fed_457_catchup_limit,0);
1301 l_over_limit := nvl(l_fed_taxable_value,0) - l_fed_tax_limit;
1302
1303 elsif l_fed_tax_type = 'DCP' then
1304 l_fed_tax_limit := NVL(l_dcp_limit,0);
1305 l_over_limit := nvl(l_fed_taxable_value,0) - l_fed_tax_limit;
1306 end if;
1307
1308 hr_utility.trace('Federal Taxable Value is : '|| to_char(l_fed_taxable_value));
1309 hr_utility.trace('Federal Tax Limit is : ' ||to_char(l_fed_tax_limit));
1310
1311 -- need to insert those rows that have over limit > 0
1312 if nvl(l_over_limit,0) > 0 then
1313 if (l_prev_asg_act_id = p_asg_action_id) then
1314
1315 null;
1316
1317 else
1318
1319 l_prev_asg_act_id := p_asg_action_id;
1320
1321 select pay_assignment_actions_s.nextval
1322 into new_asg_act_id
1323 from dual;
1324
1325 -- insert the action record.
1326 hr_utility.trace('New Assignment action id: '||to_char(new_asg_act_id));
1327 hr_utility.trace('Assignment id: '||to_char(p_asg_id));
1328
1329 hr_nonrun_asact.insact(new_asg_act_id,p_asg_id,t_payroll_action_id,p_chnkno,p_tax_unit_id);
1330 g_inserted_asg_action_id := new_asg_act_id;
1331 g_inserted_asg_action_id_flag := 'Y';
1332 select paf.assignment_number
1333 ,paf.person_id
1334 ,ppf.first_name
1335 ,ppf.middle_names
1336 ,ppf.last_name
1337 ,ppf.national_identifier
1338 into l_assignment_number
1339 ,l_person_id
1340 ,l_first_name
1341 ,l_middle_name
1342 ,l_last_name
1343 ,l_ssn
1344 from per_assignments_f paf
1345 ,per_people_f ppf
1346 where paf.assignment_id = p_asg_id
1347 and paf.effective_end_date = ( select max(paf1.effective_end_date)
1348 from per_assignments_f paf1
1349 where paf1.assignment_id = paf.assignment_id
1350 and paf1.effective_start_date <= p_as_of_date)
1351 and ppf.person_id = paf.person_id
1352 and p_as_of_date between ppf.effective_start_date and ppf.effective_end_date;
1353
1354 end if;
1355
1356 insert into pay_us_rpt_totals
1357 ( attribute2 -- :PACTID
1358 ,session_id
1359 ,tax_unit_id
1360 ,gre_name, organization_name, location_name
1361 ,value7 -- asg. id
1362 ,attribute7 -- tax type
1363 ,value1 -- fed_taxable_value
1364 ,value2 -- fed_over_limit
1365 ,value3 -- fed_tax_limit
1366 ,attribute5 -- New Assignment Action id
1367 ,attribute10 -- Assignment number
1368 ,attribute11 -- Person id
1369 ,attribute12 -- First name
1370 ,attribute13 -- Middle name
1371 ,attribute14 -- Last name
1372 ,attribute15 -- SSN
1373 )
1374 values
1378 ,l_gre_name, l_org_name, l_location_code
1375 (t_payroll_action_id
1376 ,p_ppa_finder
1377 ,p_tax_unit_id
1379 ,p_asg_id
1380 ,l_fed_tax_type
1381 ,l_fed_taxable_value
1382 ,l_over_limit
1383 ,l_fed_tax_limit
1384 ,new_asg_act_id
1385 ,l_assignment_number
1386 ,l_person_id
1387 ,l_first_name
1388 ,l_middle_name
1389 ,l_last_name
1390 ,l_ssn
1391 );
1392 hr_utility.trace('Inserting a record in pay_us_rpt_totals for the employee ' ||l_last_name || ' ' || l_first_name
1393 );
1394 hr_utility.trace('Inserted chunk number ' ||p_chnkno || ' for tax type ' || l_fed_tax_type );
1395 end if; -- over limit > 0
1396
1397 end loop;
1398 -- close c_balance_sets;
1399 end if; /* tax type is null or one of the federal limit tax */
1400
1401 hr_utility.set_location('OUT load_federal_taxes ',450);
1402
1403 exception
1404 when others then
1405 hr_utility.trace('Error occurred load_federal_taxes ...' ||SQLERRM);
1406 raise;
1407 end load_federal_taxes;
1408 --
1409 --------------------- END FEDERAL TAXES -----------------------------------
1410 --
1411 procedure load_data
1412 (
1413 pactid in number, /* payroll action id */
1414 chnkno in number,
1415 p_assignment_id IN NUMBER,
1416 p_assignment_action_id IN NUMBER,
1417 p_tax_unit_id IN NUMBER
1418 ) is
1419
1420 cursor sel_aaid (l_aaid number)
1421 is
1422 select
1423 ppa_arch.start_date start_date,
1424 ppa_arch.effective_date end_date,
1425 ppa_arch.business_group_id business_group_id,
1426 ppa_arch.payroll_action_id payroll_action_id,
1427 -- to_number(paa.serial_number) assignment_action_id, -- max assignment_action_id
1428 paa.assignment_id assignment_id,
1429 paa.tax_unit_id tax_unit_id,
1430 hou.name gre_name,
1431 paf.organization_id organization_id,
1432 hou1.name organization_name,
1433 paf.location_id location_id,
1434 hrl.location_code location_code,
1435 paf.effective_end_date max_end_date
1436 from hr_locations_all hrl,
1437 hr_all_organization_units hou1,
1438 hr_all_organization_units hou,
1439 per_assignments_f paf,
1440 pay_assignment_actions paa, -- PYUGEN
1441 pay_payroll_actions ppa_arch -- PYUGEN
1442 where
1443 -- ppa_arch.payroll_action_id = l_pactid
1444 paa.assignment_action_id = l_aaid
1445 and paa.payroll_action_id = ppa_arch.payroll_action_id
1446 -- and paa.chunk_number = l_chnkno
1447 and paf.assignment_id = paa.assignment_id
1448 and paf.effective_end_date = ( select max(effective_end_date)
1449 from per_assignments_f paf1
1450 where paf1.assignment_id = paf.assignment_id
1451 and paf1.effective_start_date <=
1452 ppa_arch.effective_date
1453 )
1454 and hrl.location_id = NVL(paf.location_id,hou.location_id)
1455 and hou1.organization_id = nvl(paf.organization_id,paf.business_group_id)
1456 and hou.organization_id = paa.tax_unit_id;
1457
1458 l_bal_date per_assignments_f.effective_end_date%TYPE;
1459 new_asg_act_id pay_assignment_actions.assignment_action_id%type;
1460 --
1461 --
1462 --------------------------- M A I N -------------------------------------
1463 begin
1464 --hr_utility.trace_on(null,'oracle');
1465
1466 hr_utility.set_location('IN load data',500);
1467
1468 hr_utility.trace('PACTID = '||pactid);
1469 hr_utility.trace('CHNKNO = '||to_char(chnkno));
1470 begin
1471 if g_get_param = 'Y' then
1472 g_get_param := 'N';
1473 select ppa.legislative_parameters,
1474 ppa.business_group_id,
1475 ppa.start_date,
1476 ppa.effective_date,
1477 pay_us_over_limit_pkg.get_parameter('GRE',ppa.legislative_parameters),
1478 pay_us_over_limit_pkg.get_parameter('AS_OF_DATE',ppa.legislative_parameters),
1479 pay_us_over_limit_pkg.get_parameter('TAX_TYPE',ppa.legislative_parameters),
1480 pay_us_over_limit_pkg.get_parameter('PPA_FINDER',ppa.legislative_parameters),
1481 ppa.payroll_action_id
1482 into l_leg_param,
1483 l_business_group_id,
1484 l_leg_start_date,
1485 l_leg_end_date,
1486 t_gre_id,
1487 l_date_prm,
1488 l_tax_type,
1489 l_ppa_finder,
1490 t_payroll_action_id
1491 from pay_payroll_actions ppa
1492 where ppa.payroll_action_id = pactid;
1493
1494
1495 /* the tax type returned is like SDI_EE whereas the value stored
1496 in the table is SDI EE hence we need to replace '_' with null */
1497
1498 l_tax_type := replace(l_tax_type,'_',' ');
1499
1500 end if;
1501 exception when no_data_found then
1502 hr_utility.trace('Legislative Details not found...');
1503 raise;
1504 end;
1505
1506 g_inserted_asg_action_id_flag := 'N';
1507 l_as_of_date := to_date(l_date_prm,'YYYY/MM/DD');
1508
1509 if (pay_us_payroll_utils.ltr_fed_tax_info.count < 1 or pay_us_payroll_utils.ltr_state_tax_info.count < 1 ) THEN
1510 hr_utility.trace('Inserting Limit Values using utilities package');
1511 pay_us_payroll_utils.populate_jit_information(p_effective_date => l_as_of_date
1512 ,p_get_federal => 'Y'
1513 ,p_get_state => 'Y');
1514 end if;
1515
1516
1517
1518 -- removed the select statement to fetch parameters as part of bug # 2938556
1519
1520 -- Removed call to populate_state_limits_table as part of bug # 2938556
1521 --
1522 open sel_aaid (p_assignment_action_id);
1523 fetch sel_aaid into l_start_date,
1524 l_end_date,
1525 l_business_group_id,
1526 l_payroll_action_id,
1527 -- l_assignment_action_id,
1528 l_assignment_id,
1529 l_tax_unit_id,
1530 l_gre_name,
1531 l_organization_id,
1532 l_org_name,
1533 l_location_id,
1534 l_location_code,
1535 l_bal_date;
1536
1537
1538
1539 hr_utility.trace('Chunk No = '||to_char(chnkno));
1540 hr_utility.trace('Start Date = '||to_char(l_start_date));
1541 hr_utility.trace('End Date = '||to_char(l_end_date));
1542 hr_utility.trace('BG ID = '||to_char(l_business_group_id));
1543 hr_utility.trace('Payroll Action ID = '||to_char(l_payroll_action_id));
1544 hr_utility.trace('Action Type = '||l_action_type);
1545 hr_utility.trace('Asg Act ID = '||to_char(p_assignment_action_id));
1546 hr_utility.trace('Asg ID = '||to_char(l_assignment_id));
1547 hr_utility.trace('Tax Unit ID = '||to_char(l_tax_unit_id));
1548 hr_utility.trace('GRE Name = '||l_gre_name);
1549
1550 l_tax_group := Tax_group(l_tax_unit_id);
1551
1552 hr_utility.trace('Tax Group = '||l_tax_group);
1553
1554 -- Insert a dummy assignment action to enable the report to be fired in case of
1555 -- no employee is found.
1556
1557 -- Bug No 3118107 removed insertion of dummy assignment action
1558 if p_insert_done_flag = 'N' then
1559 l_prev_tg := l_tax_group;
1560 end if;
1561
1562 -- # 2664340
1563 -- Clear the catchup balances table
1564 -- commented for # 2938556
1565 --
1566 -- t_fed_catchup_balance.DELETE;
1567 --
1568 -- We have to check the as_of_date with the effective_end_date of the assignment
1569 -- and pass in the date that is less. This is required for terminated employees
1570 -- Employees who have been terminated before the as_of_date.
1571
1572 if l_bal_date < l_as_of_date then
1573
1574 l_as_of_date := l_bal_date;
1575
1576 end if;
1577
1578 -- Check if the tax group changes
1579
1580 if ( l_prev_tg <> l_tax_group) then
1581 l_prev_tg := l_tax_group;
1582 l_tg_changed := 'Y';
1583 end if;
1584
1585 -- Populate the federal balances table only if that is empty
1586 -- or if the tax group changes.
1587
1588 if (t_fed_balance_list.count < 1 or l_tg_changed = 'Y' ) THEN
1589 populate_fed_balance_list(l_tax_type,l_tax_group);
1590 l_tg_changed := 'N';
1591 end if;
1592
1593 -- Populate the state balances table only if that is empty.
1594
1595 if (t_state_balance_list.count < 1 ) THEN
1596 populate_state_balance_list(l_tax_type);
1597 end if;
1598
1599
1600 -- Call load_federal_taxes to process federal balances
1601
1602 load_federal_taxes(l_assignment_id -- Bug3018606
1603 ,l_ppa_finder
1604 ,p_assignment_action_id
1605 ,l_as_of_date
1606 ,p_tax_unit_id
1607 ,l_tax_type
1608 ,l_business_group_id
1609 ,l_tax_group
1610 ,chnkno);
1611
1612 -- Call load_state_taxes to process state balances
1613
1614 load_state_taxes(l_assignment_id --Bug3018606
1615 ,l_ppa_finder
1616 ,p_assignment_action_id
1617 ,l_as_of_date
1618 ,p_tax_unit_id
1619 ,l_tax_type
1620 ,l_tax_group
1621 ,chnkno);
1622
1623 hr_utility.trace('End of LOAD DATA');
1624 hr_utility.set_location('OUT load data',550);
1625
1626 -----------------------------------------------
1627 --Bug # 3118107
1628 -----------------------------------------------
1629
1630 if p_insert_done_flag = 'N' then
1631 select pay_assignment_actions_s.nextval
1632 into new_asg_act_id
1633 from dual;
1634
1635 --insert the action record
1636 hr_nonrun_asact.insact(new_asg_act_id,p_assignment_id,pactid,chnkno,p_tax_unit_id);
1637 p_insert_done_flag := 'Y';
1638 end if;
1639 close sel_aaid;
1640 exception
1641 when others then
1642 hr_utility.trace('Error occurred load_data ...' ||SQLERRM);
1643 raise;
1644
1645 end load_data;
1646 --------------------------end load data-----------------------------
1647
1648 END pay_us_over_limit_tax_rpt_pkg;