[Home] [Help]
PACKAGE BODY: APPS.PAY_US_FLS_REPORTING_PKG
Source
1 PACKAGE BODY pay_us_fls_reporting_pkg AS
2 /* $Header: pyusflsp.pkb 120.10 2010/08/03 10:13:05 emunisek ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_fls_reporting_pkg
21
22 Description : Generate FLS periodic magnetic reports according to
23 FLS requirements.
24
25 Uses :
26
27 Change List
28 -----------
29 Date Name Vers Bug No Description
30 ---- ---- ------ ------- -----------
34 from Wage Accumulation.
31 29-JUL-2010 emunisek 115.29 9872952 Made changes such that the FIT related
32 wages are shown in accordance with
33 Direct US Federal Balances and Exempt
35 28-JAN-2010 svannian 115.28 9603852 Invalid number error resolved.
36 12-JAN-2010 nkjaladi 115.27 9164356 Made changes to functions get_tax_exists,
37 get_fls_jd_values and
38 get_fls_tax_type_values to display SDI1
39 tax values
40 17-JUL-2006 ahanda 115.26 5368410 Multiplied ln_r_ee_tax_amt by -1 for EIC
41 27-JAN-2006 asasthan 115.25 4969824 Removed unwanted text(typos)
42 27-JAN-2006 ahanda 115.24 4969824 added order by to the
43 range cursor
44 20-JAN-2006 asasthan 115.23 4969824 Changed order of table
45 20-JAN-2006 asasthan 115.22 4969824 Range cursor passes start_person
46 end_person instead of start_asg
47 20-JUL-2005 ahanda 115.21 4500097 Added Ordered hint for subquery
48 08-JUL-2005 pragupta 115.20 4335410 Changed function -
49 get_fls_tax_type_values
50 14-MAR-2005 sackumar 115.19 4222032 Change Range Cursor to remove
51 redundant use of bind Variable
52 :payroll_action_id
53 29-DEC-2004 ahanda 115.18 4092186 Changed function get_tax_exists
54 to return N for MA - SDI
55 02-SEP-2004 meshah 115.17 Fixed gscc error
56 18-AUG-2004 ahanda 115.16 3832605 Added new function
57 get_jurisdiction_name
58 18-FEB-2004 ssmukher 115.14 3343962 Performance Changes to cursor
59 c_action_info
60 19-FEB-2002 ahanda 115.13 2232320 Changed check_tax_unit_fein
61 06-FEB-2002 ahanda 115.12 Changed get_fls_jd_values to assign
62 each column values in PL/SQL table
63 instead of assgining the table.
64 (Workaround for bug 1822467)
65 15-AUG-2001 ahanda 115.11 Changed cursor c_action_info
66 in function get_fls_jd_values
67 for performance reasons.
68 20-JUN-2001 ahanda 115.10 Added check for category in
69 cursor check_tax_unit_fein.
70 1849359 Added to_number in select from
71 pay_action_information to
72 work around bug 1822467
73 22-APR-2001 ahanda 115.9 Changed range code to error out
74 if FEIN is not 9 chars.
75 17-APR-2001 ahanda 115.8 Getting value if SS EE Withhled
76 from action_information8 instead
77 of action_information9.
78 15-APR-2001 ahanda 115.7 Changed apps.package name to
79 package name.
80 13-APR-2001 ahanda 115.6 Modified functions
81 - get_tax_exists
82 to return N for FUTA EE
83 - get_fls_tax_type_values
84 to return formated
85 +ve and - ve values.
86 27-MAR-2001 ahanda 115.5 Modified functions
87 - get_tax_exists
88 - get_fls_agency_code
89 - get_fls_tax_type_values
90 Changed the above function as
91 agency code is now dependent
92 on Tax Types.
93 Also fixed bug 1680396.
94 12-MAR-2001 asasthan 115.4 Modified functions:
95 - get_fls_agency_code
96 - get_fls_tax_type_values.
97 02-MAR-2001 asasthan 115.3 Changed the function to get
98 the agency code from
99 sta_information9 of
100 'State tax limit rate info'
101 record.
102 22-FEB-2001 ahanda 115.3 Changes get_fls_tax_type_values
103 20-FEB-2001 ahanda 115.2 Removed comment in range
104 19-FEB-2001 ahanda 115.1 Removed comment in range
105 and action creation.
106 28-JAN-2001 ahanda 115.0 Created.
107
108 *******************************************************************/
109
110 /******************************************************************
111 ** Package Local Variables
112 ******************************************************************/
113 gv_package varchar2(50) := 'pay_us_fls_reporting_pkg';
114
115
116 /*******************************************************************
117 ** Procedure to return the values for the Payroll Action of
118 ** the Periodic Tax Filing Interface.
119 ** This is used in Range Code and Action Creation.
120 *******************************************************************/
121 PROCEDURE get_payroll_action_info (
122 p_payroll_action_id in number,
123 -- Bug 3343962 Performance changes
124 p_start_date out nocopy date,
125 p_end_date out nocopy date,
126 p_report_qualifier out nocopy varchar2,
127 p_report_type out nocopy varchar2,
128 p_report_category out nocopy varchar2,
129 p_business_group_id out nocopy number,
130 p_tax_unit_id out nocopy number,
131 p_payroll_id out nocopy varchar2,
132 p_consolidation_set_id out nocopy number)
133 IS
134
135 cursor c_payroll_action(cp_payroll_action_id in number) is
136 select ppa.start_date
137 ,ppa.effective_date
138 ,ppa.business_group_id
139 ,ppa.report_qualifier
140 ,ppa.report_type
141 ,ppa.report_category
142 ,ppa.legislative_parameters
143 from pay_payroll_actions ppa
144 where payroll_action_id = cp_payroll_action_id;
145
146 ld_start_date DATE;
147 ld_end_date DATE;
148 ln_business_group_id NUMBER;
149 lv_report_qualifier VARCHAR2(30);
150 lv_report_type VARCHAR2(30);
151 lv_report_category VARCHAR2(30);
152 lv_leg_parameter VARCHAR2(300);
153
154 ln_tax_unit_id NUMBER;
155 ln_payroll_id NUMBER;
156 ln_consolidation_set_id NUMBER;
157
158 BEGIN
159 hr_utility.set_location(gv_package || '.get_payroll_action_info', 10);
160
161 open c_payroll_action(p_payroll_action_id);
162 fetch c_payroll_action into
163 ld_start_date, ld_end_date, ln_business_group_id,
164 lv_report_qualifier, lv_report_type,
165 lv_report_category, lv_leg_parameter;
166 if c_payroll_action%notfound then
167 hr_utility.set_location( gv_package || '.get_payroll_action_info',20);
168 hr_utility.raise_error;
169 end if;
170 close c_payroll_action;
171 hr_utility.set_location(gv_package || '.get_payroll_action_info', 30);
172
173 ln_payroll_id := to_number(rtrim(Pay_Mag_Utils.get_parameter(
174 'TRANSFER_PAYROLL_ID'
175 ,'TRANSFER_CONSOLIDATION_SET_ID'
176 ,lv_leg_parameter)));
177 hr_utility.set_location(gv_package || '.get_payroll_action_info', 40);
178 ln_consolidation_set_id := to_number(rtrim(Pay_Mag_Utils.get_parameter(
179 'TRANSFER_CONSOLIDATION_SET_ID'
180 ,'TRANSFER_TAX_UNIT_ID'
181 ,lv_leg_parameter)));
182 hr_utility.set_location(gv_package || '.get_payroll_action_info', 50);
183 ln_tax_unit_id := to_number(rtrim(Pay_Mag_Utils.get_parameter(
184 'TRANSFER_TAX_UNIT_ID'
185 ,null
186 ,lv_leg_parameter)));
187
188 hr_utility.set_location(gv_package || '.get_payroll_action_info', 60);
189 p_start_date := ld_start_date;
190 p_end_date := ld_end_date;
191 p_report_qualifier := lv_report_qualifier;
192 p_report_type := lv_report_type;
193 p_report_category := lv_report_category;
194 p_business_group_id := ln_business_group_id;
195 p_tax_unit_id := ln_tax_unit_id;
196 p_payroll_id := ln_payroll_id;
197 p_consolidation_set_id := ln_consolidation_set_id;
198
199 hr_utility.set_location(gv_package || '.get_payroll_action_info', 100);
200
201 END get_payroll_action_info;
202
203 /*******************************************************************
207 *******************************************************************/
204 ** Procedure to return the values for the Payroll Action of
205 ** the Periodic Tax Filing Interface.
206 ** This is used in Range Code and Action Creation.
208 FUNCTION check_tax_unit_fein ( p_tax_unit_id in number
209 ,p_payroll_action_id in number
210 ,p_tax_unit_fein in varchar2 default null)
211 RETURN NUMBER IS
212
213 cursor c_get_fein (cp_tax_unit_id in number) is
214 select replace( replace(replace(hoi.org_information1,'-'),'/'),' ')
215 from hr_organization_information hoi
216 where hoi.organization_id = cp_tax_unit_id
217 and hoi.org_information_context = 'Employer Identification';
218
219 cursor c_get_gre_name (cp_tax_unit_id in number) is
220 select hou.name
221 from hr_all_organization_units hou
222 where hou.organization_id = cp_tax_unit_id;
223
224 lv_tax_unit_fein VARCHAR2(20);
225 lv_gre_name VARCHAR2(80);
226 ln_error_count NUMBER := 0;
227
228 BEGIN
229
230 lv_tax_unit_fein := p_tax_unit_fein;
231
232 if p_tax_unit_fein is null then
233 open c_get_fein(p_tax_unit_id);
234 fetch c_get_fein into lv_tax_unit_fein;
235 close c_get_fein;
236 end if;
237
238 if length(lv_tax_unit_fein) <> 9 then
239 ln_error_count := 1;
240 open c_get_gre_name(p_tax_unit_id);
241 fetch c_get_gre_name into lv_gre_name;
242 close c_get_gre_name;
243
244 insert into pay_message_lines
245 (line_sequence, payroll_id, message_level,
246 source_id, source_type, line_text) values
247 (pay_message_lines_s.nextval, NULL, 'F',
248 p_payroll_action_id, 'P',
249 'FEIN is not 9 charcters for GRE: ' || lv_gre_name);
250 end if;
251
252 return (ln_error_count);
253
254 END check_tax_unit_fein;
255
256
257 /********************************************************
258 ** Range Code: Multi Threading
259 ********************************************************/
263
260 PROCEDURE range_cursor ( p_payroll_action_id in number
261 ,p_sql_string out nocopy varchar2) -- Bug 3343962
262 IS
264 cursor c_arch_tax_unit ( cp_business_group_id in number
265 ,cp_start_date in date
266 ,cp_end_date in date
267 ) is
268 select organization_id,
269 replace( replace(replace(hoi.org_information1,'-'),'/'),' ')
270 from hr_organization_information hoi
271 where org_information_context = 'Employer Identification'
272 and exists (select 'x'
273 from pay_assignment_actions paa,
274 pay_payroll_actions ppa
275 where ppa.payroll_action_id = paa.payroll_action_id
276 and ppa.business_group_id = cp_business_group_id
277 and ppa.effective_date between cp_start_date
278 and cp_end_date
279 and ppa.action_type = 'X'
280 and ppa.report_type = 'XFR_INTERFACE'
281 and ppa.action_status = 'C'
282 and paa.action_status = 'C'
283 and paa.tax_unit_id = hoi.organization_id
284 );
285
286 lv_error_message VARCHAR2(1000);
287 ln_error_count NUMBER := 0;
288 ln_arch_tax_unit_id NUMBER;
289 lv_arch_tax_unit_fein VARCHAR2(80);
290
291 lv_sql_string varchar2(10000);
292
293 ld_start_date DATE;
294 ld_end_date DATE;
295 ln_business_group_id NUMBER;
296 lv_report_qualifier VARCHAR2(30);
297 lv_report_type VARCHAR2(30);
298 lv_report_category VARCHAR2(30);
299
300 ln_tax_unit_id NUMBER;
301 ln_payroll_id NUMBER;
302 ln_consolidation_set_id NUMBER;
303
304 BEGIN
305 hr_utility.set_location(gv_package || '.range_code', 10);
306 get_payroll_action_info (
307 p_payroll_action_id
308 ,ld_start_date
309 ,ld_end_date
310 ,lv_report_qualifier
311 ,lv_report_type
312 ,lv_report_category
313 ,ln_business_group_id
314 ,ln_tax_unit_id
315 ,ln_payroll_id
316 ,ln_consolidation_set_id);
317
318 hr_utility.trace('ld_start_date = ' || ld_start_date);
319 hr_utility.trace('ld_end_date = ' || ld_end_date);
320 hr_utility.trace('ln_business_group_id = ' || ln_business_group_id);
321 hr_utility.trace('ln_tax_unit_id = ' || ln_tax_unit_id);
322 hr_utility.trace('ln_payroll_id = ' || ln_payroll_id);
323 hr_utility.trace('lv_report_qualifier = ' || lv_report_qualifier);
324 hr_utility.trace('lv_report_type = ' || lv_report_type);
325 hr_utility.trace('lv_report_category = ' || lv_report_category);
326
327 hr_utility.set_location(gv_package || '.range_code', 20);
328
329 if ln_tax_unit_id is not null then
330 hr_utility.set_location(gv_package || '.range_code', 30);
331 ln_error_count := ln_error_count +
332 check_tax_unit_fein(
333 p_tax_unit_id => ln_tax_unit_id
334 ,p_payroll_action_id => p_payroll_action_id);
335 else
336 hr_utility.set_location(gv_package || '.range_code', 40);
337 open c_arch_tax_unit( ln_business_group_id
338 ,ld_start_date
339 ,ld_end_date);
340 loop
341 fetch c_arch_tax_unit into ln_arch_tax_unit_id,
342 lv_arch_tax_unit_fein;
343 if c_arch_tax_unit%notfound then
344 exit;
345 end if;
346
347 hr_utility.set_location(gv_package || '.range_code', 50);
348 hr_utility.trace('ln_arch_tax_unit_id = ' || ln_arch_tax_unit_id);
349 hr_utility.trace('lv_arch_tax_unit_fein = ' || lv_arch_tax_unit_fein);
350 hr_utility.set_location(gv_package || '.range_code', 60);
351
352 ln_error_count := ln_error_count +
353 check_tax_unit_fein(
354 p_tax_unit_id => ln_arch_tax_unit_id
355 ,p_payroll_action_id => p_payroll_action_id
356 ,p_tax_unit_fein => lv_arch_tax_unit_fein);
357 end loop;
358 close c_arch_tax_unit;
359 hr_utility.set_location(gv_package || '.range_code', 70);
360 end if;
361
362 hr_utility.trace('Error Count = ' || ln_error_count);
363 if ln_error_count > 0 then
364 lv_error_message := 'Please check the messages at the Payroll Action Level' ||
365 'to find out the GRE''s with invalid FEIN.';
366
367 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
368 hr_utility.set_message_token('FORMULA_TEXT',lv_error_message);
369 commit;
370 hr_utility.raise_error;
371 end if;
372
373 hr_utility.set_location(gv_package || '.range_code', 60);
374
375 lv_sql_string :=
376 'select distinct paf.person_id
377 from pay_payroll_actions ppa,
378 pay_assignment_actions paa,
379 per_assignments_f paf
383 || ''',''dd/mm/yyyy'')
380 where ppa.business_group_id = ' || ln_business_group_id || '
381 and ppa.effective_date
382 between to_date(''' || to_char(ld_start_date, 'dd/mm/yyyy')
384 and to_date(''' || to_char(ld_end_date, 'dd/mm/yyyy')
385 || ''',''dd/mm/yyyy'')
386 and ppa.action_type = ''X''
387 and ppa.report_type = ''XFR_INTERFACE''
388 and ppa.action_status =''C''
389 and ppa.payroll_action_id = paa.payroll_action_id
390 and paa.action_status = ''C''
391 and paa.tax_unit_id = nvl('''|| ln_tax_unit_id ||
392 ''', paa.tax_unit_id)
393 and paf.assignment_id = paa.assignment_id
394 and ppa.effective_date between paf.effective_start_date
395 and paf.effective_end_date
396 and not exists
397 (select /*+ ORDERED */
398 ''x''
399 from pay_action_interlocks pai,
400 pay_assignment_actions paa1,
401 pay_payroll_actions ppa1
402 where pai.locked_action_id = paa.assignment_action_id
403 and paa1.assignment_action_id = pai.locking_action_id
404 and ppa1.payroll_action_id = paa1.payroll_action_id
405 and ppa1.action_type =''X''
406 and ppa1.report_type = ''FLS''
407 and ppa1.report_qualifier = ''PERIODIC''
408 and ppa1.report_category = ''RT'')
409 and :payroll_action_id is not null
410 and rtrim(pay_mag_utils.get_parameter(
411 ''TRANSFER_PAYROLL_ID''
412 ,''TRANSFER_CONSOLIDATION_SET_ID''
413 ,ppa.legislative_parameters)) =
414 nvl('''||ln_payroll_id
415 ||''', rtrim(pay_mag_utils.get_parameter(
416 ''TRANSFER_PAYROLL_ID''
417 ,''TRANSFER_CONSOLIDATION_SET_ID''
418 ,ppa.legislative_parameters)))
419 and rtrim(pay_mag_utils.get_parameter(
420 ''TRANSFER_CONSOLIDATION_SET_ID''
424 ||''', rtrim(pay_mag_utils.get_parameter(
421 ,null
422 ,ppa.legislative_parameters)) =
423 nvl('''||ln_consolidation_set_id
425 ''TRANSFER_CONSOLIDATION_SET_ID''
426 ,null
427 ,ppa.legislative_parameters)))
428 order by paf.person_id';
429
430 p_sql_string := lv_sql_string;
431 hr_utility.set_location(gv_package || '.range_code', 50);
432
433 END range_cursor;
434
435 /********************************************************
436 ** Action Creation Code: Multi Threading
437 ********************************************************/
438 PROCEDURE action_creation( p_payroll_action_id in number
439 ,p_start_person in number
440 ,p_end_person in number
441 ,p_chunk in number)
442
443 IS
444
445 cursor c_get_fls_emp( cp_tax_unit_id in number
446 ,cp_payroll_id in number
447 ,cp_consolidation_set_id in number
448 ,cp_business_group_id in number
449 ,cp_start_date in date
450 ,cp_end_date in date
451 ,cp_start_person_id in number
452 ,cp_end_person_id in number
453 ) is
454 select paa.assignment_id,
455 ppa.effective_date,
456 paa.tax_unit_id,
457 paa.assignment_action_id
458 from pay_payroll_actions ppa,
459 pay_assignment_actions paa,
460 per_assignments_f paf
461 where ppa.business_group_id = cp_business_group_id
462 and ppa.effective_date between cp_start_date
463 and cp_end_date
464 and ppa.action_type = 'X'
465 and ppa.report_type = 'XFR_INTERFACE'
466 and ppa.action_status = 'C'
467 and ppa.payroll_action_id = paa.payroll_action_id
468 and paa.action_status = 'C'
469 and paa.tax_unit_id = nvl(to_char(cp_tax_unit_id), paa.tax_unit_id)
470 and paf.assignment_id = paa.assignment_id
471 and paf.person_id between cp_start_person_id
472 and cp_end_person_id
473 and ppa.effective_date between paf.effective_start_date
474 and paf.effective_end_date
475 and not exists
476 (select /*+ ORDERED */
477 'x'
478 from pay_action_interlocks pai,
479 pay_assignment_actions paa1,
480 pay_payroll_actions ppa1
481 where pai.locked_action_id = paa.assignment_action_id
482 and paa1.assignment_action_id = pai.locking_action_id
483 and ppa1.payroll_action_id = paa1.payroll_action_id
484 and ppa1.action_type = 'X'
485 and ppa1.report_type = 'FLS'
486 and ppa1.report_qualifier = 'PERIODIC'
487 and ppa1.report_category = 'RT')
488 and rtrim(pay_mag_utils.get_parameter(
489 'TRANSFER_PAYROLL_ID'
490 ,'TRANSFER_CONSOLIDATION_SET_ID'
491 ,ppa.legislative_parameters)) =
492 nvl(to_char(cp_payroll_id),
493 rtrim(pay_mag_utils.get_parameter(
494 'TRANSFER_PAYROLL_ID'
495 ,'TRANSFER_CONSOLIDATION_SET_ID'
496 ,ppa.legislative_parameters)))
497 and rtrim(pay_mag_utils.get_parameter(
498 'TRANSFER_CONSOLIDATION_SET_ID'
499 ,null
500 ,ppa.legislative_parameters)) =
501 nvl(to_char(cp_consolidation_set_id),
502 rtrim(pay_mag_utils.get_parameter(
503 'TRANSFER_CONSOLIDATION_SET_ID'
504 ,null
505 ,ppa.legislative_parameters))) ;
506
507 ld_start_date DATE;
508 ld_end_date DATE;
509 ln_business_group_id NUMBER;
510 lv_report_qualifier VARCHAR2(30);
511 lv_report_type VARCHAR2(30);
512 lv_report_category VARCHAR2(30);
513 ln_tax_unit_id NUMBER;
514 ln_payroll_id NUMBER;
515 ln_consolidation_set_id NUMBER;
516
517 /* Assignment Record Local Variables */
518 ln_assignment_id NUMBER;
519 ld_effective_date DATE;
520 ln_emp_tax_unit_id NUMBER;
521 ln_assignment_action_id NUMBER;
522
523 ln_locking_action_id NUMBER;
524
525 BEGIN
526 hr_utility.set_location(gv_package || '.action_creation', 10);
527 get_payroll_action_info (
528 p_payroll_action_id
529 ,ld_start_date
530 ,ld_end_date
531 ,lv_report_qualifier
532 ,lv_report_type
533 ,lv_report_category
534 ,ln_business_group_id
535 ,ln_tax_unit_id
536 ,ln_payroll_id
537 ,ln_consolidation_set_id);
538
539 hr_utility.set_location(gv_package || '.action_creation', 20);
540 open c_get_fls_emp( ln_tax_unit_id
541 ,ln_payroll_id
542 ,ln_consolidation_set_id
546 ,p_start_person
543 ,ln_business_group_id
544 ,ld_start_date
545 ,ld_end_date
547 ,p_end_person);
548 loop
549 hr_utility.set_location(gv_package || '.action_creation', 30);
550 fetch c_get_fls_emp into ln_assignment_id, ld_effective_date,
551 ln_emp_tax_unit_id, ln_assignment_action_id;
552 if c_get_fls_emp%notfound then
553 hr_utility.set_location(gv_package || '.action_creation', 40);
554 exit;
555 end if;
556
557 hr_utility.set_location(gv_package || '.action_creation', 50);
558 select pay_assignment_actions_s.nextval
559 into ln_locking_action_id
560 from dual;
561
562 -- insert into pay_assignment_actions.
563 hr_nonrun_asact.insact(ln_locking_action_id, ln_assignment_id,
564 p_payroll_action_id, p_chunk, ln_emp_tax_unit_id);
565 hr_utility.set_location(gv_package || '.action_creation', 60);
566
567 -- insert an interlock to this action
568 hr_nonrun_asact.insint(ln_locking_action_id, ln_assignment_action_id);
569
570 update pay_assignment_actions paa
571 set paa.serial_number = ln_assignment_action_id
572 where paa.assignment_action_id = ln_locking_action_id;
573
574 hr_utility.set_location(gv_package || '.action_creation', 60);
575 end loop;
576 close c_get_fls_emp;
577
578 hr_utility.set_location(gv_package || '.action_creation', 60);
579 END action_creation;
580
581
582 /*******************************************************************
583 ** Function called from the Fast Formula.
584 ** More detail in Header File.
585 *******************************************************************/
586 FUNCTION get_fls_org_information(
587 p_tax_unit_id in number
588 ,p_payroll_action_id in number
589 ,p_effective_date in varchar2
590 )
591 RETURN VARCHAR2
592 IS
593 lv_org4_short_name VARCHAR2(15);
594 lv_org5_short_name VARCHAR2(15);
595 BEGIN
596 lv_org4_short_name := rpad('002', 15, ' ');
597 lv_org5_short_name := rpad('Unit1', 15, ' ');
598
599 return(lv_org4_short_name || lv_org5_short_name);
600
601 END get_fls_org_information;
602
603
604 /*******************************************************************
605 ** Function called from the Fast Formula.
606 ** More detail in Header File.
607 *******************************************************************/
608 FUNCTION get_tax_exists( p_jurisdiction_code in varchar2
609 ,p_effective_date in varchar2
610 ,p_tax_type in varchar2
611 ,p_tax_type_resp in varchar2 default NULL
612 )
613 RETURN VARCHAR2
614 IS
615 cursor c_state_tax_exists( cp_jurisdiction_code in varchar2
616 ,cp_effective_date in date) is
617 select pust.sit_exists,
618 decode(pust.sdi_ee_wage_limit, null, 'N', 'Y'),
619 decode(pust.sdi_er_wage_limit, null, 'N', 'Y'),
620 decode(pust.sui_ee_wage_limit, null, 'N', 'Y'),
621 decode(pust.sui_er_wage_limit, null, 'N', 'Y')
622 from pay_us_state_tax_info_f pust
623 where cp_effective_date between pust.effective_start_date
624 and pust.effective_end_date
625 and pust.state_code = substr(cp_jurisdiction_code, 1,2)
626 and pust.sta_information_category = 'State tax limit rate info';
627
628 /* 9164356 start*/
629 cursor c_sdi1_state_tax_exists( cp_jurisdiction_code in varchar2
630 ,cp_effective_date in date) is
631 select 'Y'
632 from pay_us_state_tax_info_f pust
633 where cp_effective_date between pust.effective_start_date
634 and pust.effective_end_date
635 and pust.state_code = substr(cp_jurisdiction_code, 1,2)
636 and pust.sta_information20 IS NOT NULL
637 and pust.sta_information_category = 'State tax limit rate info';
638 /* 9164356 end*/
639
640 cursor c_county_tax_exists( cp_jurisdiction_code in varchar2
641 ,cp_effective_date in date) is
642 select puct.county_tax, puct.head_tax, puct.school_tax
643 from pay_us_county_tax_info_f puct
644 where cp_effective_date between puct.effective_start_date
645 and puct.effective_end_date
646 and puct.jurisdiction_code = cp_jurisdiction_code;
647
648 cursor c_city_tax_exists( cp_jurisdiction_code in varchar2
649 ,cp_effective_date in date) is
650 select city_tax, head_tax, school_tax
651 from pay_us_city_tax_info_f
652 where cp_effective_date between effective_start_date
653 and effective_end_date
654 and jurisdiction_code = cp_jurisdiction_code;
655
656 ld_effective_date DATE;
657
658 lv_it_exists VARCHAR2(1) := 'N';
659 lv_sdi_ee_exists VARCHAR2(1) := 'N';
660 lv_sdi_er_exists VARCHAR2(1) := 'N';
661 lv_sdi1_ee_exists VARCHAR2(1) := 'N'; -- #9164356
662 lv_sui_ee_exists VARCHAR2(1) := 'N';
666
663 lv_sui_er_exists VARCHAR2(1) := 'N';
664 lv_head_tax_exists VARCHAR2(1) := 'N';
665 lv_school_tax_exists VARCHAR2(1) := 'N';
667 lv_return VARCHAR2(1) := 'N';
668
669 BEGIN
670
671 hr_utility.set_location(gv_package || '.get_tax_exists', 10);
672 hr_utility.trace('Effective Date = ' || p_effective_date);
673 hr_utility.trace('Tax Type = ' || p_tax_type);
674 hr_utility.trace('JD = ' || p_jurisdiction_code);
675
676 ld_effective_date := to_date(p_effective_date, 'MM/DD/YYYY');
677 hr_utility.set_location(gv_package || '.get_tax_exists', 20);
678
679 /* If p_tax_type_resp is null, the function is called from
680 Wages Cursor so check if there is EE or ER taxes.
681 If p_tax_type_resp is not null, the function is called
682 from Taxes cursor so check if tax exists for the passed
686 -- Federal
683 EE or ER value
684 */
685
687 if p_jurisdiction_code = '00-000-0000' then
688 hr_utility.set_location(gv_package || '.get_tax_exists', 30);
689 if p_tax_type = 'UI' and nvl(p_tax_type_resp, 'ER') = 'ER' then
690 lv_return := 'Y';
691 elsif p_tax_type in ('EIC', 'HI', 'IT', 'OASDI') then
692 lv_return := 'Y';
693 end if;
694 -- State
695 elsif length(p_jurisdiction_code) = 11 and
696 substr(p_jurisdiction_code,3) = '-000-0000' then
697 hr_utility.set_location(gv_package || '.get_tax_exists', 40);
698 -- MA has Health Insurance report under SDI so if SDI
699 -- is passed return N and Y for HI
700 if p_jurisdiction_code = '22-000-0000' and
701 p_tax_type = 'HI' and p_tax_type_resp is null then
702 lv_return := 'Y';
703 elsif p_jurisdiction_code = '22-000-0000' and
704 p_tax_type = 'SDI' and p_tax_type_resp is null then
705 lv_return := 'N';
706 elsif ((p_tax_type not in ('SDI1','OASDI', 'HI', 'EIC', 'OPT')) or --Added SDI1 #9164356
707 (p_jurisdiction_code = '22-000-0000' and
708 p_tax_type = 'HI' and
709 p_tax_type_resp is not null)) then
710 open c_state_tax_exists(p_jurisdiction_code, ld_effective_date);
711 fetch c_state_tax_exists into lv_it_exists
712 ,lv_sdi_ee_exists
713 ,lv_sdi_er_exists
714 ,lv_sui_ee_exists
715 ,lv_sui_er_exists;
716 close c_state_tax_exists;
717 /* 9164356 start */
718 elsif p_tax_type = 'SDI1' then
719 hr_utility.set_location(gv_package || '.get_tax_exists ', 42);
720 open c_sdi1_state_tax_exists(p_jurisdiction_code, ld_effective_date);
721 fetch c_sdi1_state_tax_exists into lv_sdi1_ee_exists;
722 if c_sdi1_state_tax_exists%notfound then
723 lv_sdi1_ee_exists := 'N';
724 hr_utility.set_location(gv_package || '.get_tax_exists', 44);
725 end if;
726 hr_utility.set_location(gv_package || '.get_tax_exists | SDI1 flag:'|| lv_sdi1_ee_exists, 46);
727 close c_sdi1_state_tax_exists;
728 /* 9164356 end */
729 end if;
730 -- County
731 elsif substr(p_jurisdiction_code,7,5) = '-0000' and
732 substr(p_jurisdiction_code,3,4) <> '-000' then
733
734 hr_utility.set_location(gv_package || '.get_tax_exists', 50);
735 if p_tax_type in ('OPT', 'IT') then
736 open c_county_tax_exists(p_jurisdiction_code, ld_effective_date);
737 fetch c_county_tax_exists into lv_it_exists
738 ,lv_head_tax_exists
739 ,lv_school_tax_exists;
740 close c_county_tax_exists;
741 end if;
742 -- City
743 elsif length(p_jurisdiction_code) = 11 and
747 if p_tax_type in ('OPT', 'IT') then
744 substr(p_jurisdiction_code,3) <> '-000-0000' then
745
746 hr_utility.set_location(gv_package || '.get_tax_exists', 60);
748 open c_city_tax_exists(p_jurisdiction_code, ld_effective_date);
749 fetch c_city_tax_exists into lv_it_exists
750 ,lv_head_tax_exists
751 ,lv_school_tax_exists;
752 close c_city_tax_exists;
753 end if;
754 end if;
755
756 if length(p_jurisdiction_code) = 8 and
757 p_tax_type in ('IT') then
758 hr_utility.set_location(gv_package || '.get_tax_exists', 70);
759 lv_return := 'Y';
760 elsif p_jurisdiction_code <> '00-000-0000' then
761 hr_utility.set_location(gv_package || '.get_tax_exists', 80);
762 if p_tax_type = 'IT' then
763 lv_return := lv_it_exists;
764 elsif ((p_tax_type = 'SDI') or
765 (p_jurisdiction_code = '22-000-0000' and
766 p_tax_type = 'HI')) then
767 if p_tax_type_resp is null then
768 if lv_sdi_ee_exists = 'Y' or lv_sdi_er_exists = 'Y' then
769 lv_return := 'Y';
770 end if;
771 elsif p_tax_type_resp = 'EE' then
772 if lv_sdi_ee_exists = 'Y' then
773 lv_return := 'Y';
774 end if;
775 elsif p_tax_type_resp = 'ER' then
776 if lv_sdi_er_exists = 'Y' then
777 lv_return := 'Y';
778 end if;
779 end if;
780 elsif p_tax_type in ('UI', 'HI') then
781 if p_tax_type_resp is null then
782 if lv_sui_ee_exists = 'Y' or lv_sui_er_exists = 'Y' then
783 lv_return := 'Y';
784 end if;
785 elsif p_tax_type_resp = 'EE' then
786 if lv_sui_ee_exists = 'Y' then
787 lv_return := 'Y';
788 end if;
789 elsif p_tax_type_resp = 'ER' then
790 if lv_sui_er_exists = 'Y' then
791 lv_return := 'Y';
792 end if;
793 end if;
794 elsif p_tax_type = 'OPT' then
795 lv_return := lv_head_tax_exists;
796 /*9164356 start*/
797 elsif (p_tax_type = 'SDI1') then
798 hr_utility.set_location(gv_package || '.get_tax_exists', 82);
799 if (p_tax_type_resp is null or (p_tax_type_resp = 'EE')) then
800 if lv_sdi1_ee_exists = 'Y' then
801 hr_utility.set_location(gv_package || '.get_tax_exists', 84);
802 lv_return := 'Y';
803 end if;
804 end if;
805 /*9164356 end*/
806 end if;
807 end if;
808
809 hr_utility.set_location(gv_package || '.get_tax_exists', 90);
810 hr_utility.trace('Returned Value = ' || lv_return);
811 hr_utility.set_location(gv_package || '.get_tax_exists', 100);
812
813 return(lv_return);
814
815 END get_tax_exists;
816
817
818 /*******************************************************************
819 ** Function called from the Fast Formula.
820 ** Returns the Jurisdiction Name.
821 *******************************************************************/
822 FUNCTION get_jurisdiction_name(p_jurisdiction_code in varchar2
823 ,p_resident_jurisdiction in varchar2
824 )
825 RETURN VARCHAR2
826 IS
827
831
828 lv_jurisdiction_name VARCHAR2(200);
829
830 BEGIN
832 hr_utility.set_location(gv_package || '.get_jurisdiction_name', 10);
833 hr_utility.trace('JD =' || p_jurisdiction_code);
834
835 if p_jurisdiction_code = '00-000-0000' then
836 lv_jurisdiction_name := 'Federal';
837 else
838 -- if the JD passed is for County, City or School Dst
839 -- get the state abbrev and then use add it before the City Name
840 if substr(p_jurisdiction_code,4,3) <> '000' then
841 lv_jurisdiction_name
842 := pay_us_employee_payslip_web.get_jurisdiction_name(
843 substr(p_jurisdiction_code,1,2)||'-000-0000') || '-';
844 end if;
845
846 lv_jurisdiction_name
847 := lv_jurisdiction_name ||
848 pay_us_employee_payslip_web.get_jurisdiction_name(
849 p_jurisdiction_code);
850 end if;
851
852 if p_resident_jurisdiction is not null then
853 lv_jurisdiction_name
854 := lv_jurisdiction_name || '/' ||
855 pay_us_employee_payslip_web.get_jurisdiction_name(
856 p_jurisdiction_code);
857 end if;
858
859 return(lv_jurisdiction_name);
860
861 END get_jurisdiction_name;
862
863
864 /*******************************************************************
865 ** Function called from the Fast Formula.
866 ** More detail in Header File.
867 *******************************************************************/
868 FUNCTION get_fls_agency_code( p_jurisdiction_code in varchar2
869 ,p_effective_date in varchar2
873 RETURN VARCHAR2
870 ,p_resident_jurisdiction in varchar2
871 ,p_tax_type_code in varchar2
872 )
874 IS
875
876 cursor c_federal_agency_code( cp_effective_date in date) is
877 select puft.fed_information1,
878 nvl(puft.fed_information2, puft.fed_information1)
879 from pay_us_federal_tax_info_f puft
880 where cp_effective_date between puft.effective_start_date
881 and puft.effective_end_date
882 and puft.fed_information_category = 'FLS Interface Mapping';
883
884 cursor c_state_agency_code( cp_jurisdiction_code in varchar2
885 ,cp_effective_date in date) is
886 select pust.sta_information9
887 from pay_us_state_tax_info_f pust
888 where cp_effective_date between pust.effective_start_date
889 and pust.effective_end_date
890 and pust.state_code = substr(cp_jurisdiction_code, 1,2)
891 and pust.sta_information_category = 'State tax limit rate info';
892
893
894 /*******************************************************************
895 ** Cursor returns a not found if agency code is not defined.
896 *******************************************************************/
897 cursor c_county_agency_code( cp_jurisdiction_code in varchar2
898 ,cp_effective_date in date) is
899 select puct.cnty_attribute1,
900 nvl(puct.cnty_attribute2, puct.cnty_attribute1)
901 from pay_us_county_tax_info_f puct
902 where cp_effective_date between puct.effective_start_date
903 and puct.effective_end_date
904 and puct.jurisdiction_code = cp_jurisdiction_code
905 and (puct.cnty_attribute1 is not null or
906 puct.cnty_attribute2 is not null);
907
908 /*******************************************************************
909 ** Cursor returns a not found if agency code is not defined.
910 *******************************************************************/
911 cursor c_city_agency_code( cp_jurisdiction_code in varchar2
912 ,cp_effective_date in date) is
913 select city_attribute1,
914 nvl(city_attribute2, city_attribute1)
915 from pay_us_city_tax_info_f
916 where cp_effective_date between effective_start_date
917 and effective_end_date
918 and jurisdiction_code = cp_jurisdiction_code
919 and (city_attribute1 is not null or
920 city_attribute2 is not null);
921
922 /*******************************************************************
923 ** Cursor returns a not found if agency code is not defined.
924 *******************************************************************/
925 cursor c_school_agency_code( cp_jurisdiction_code in varchar2
926 ,cp_resident_jurisdiction in varchar2
927 ,cp_effective_date in date
928 ) is
929 select pusd.sch_information1
930 from pay_us_school_dsts_tax_info_f pusd
931 where cp_effective_date between pusd.effective_start_date
932 and pusd.effective_end_date
933 and pusd.state_code = substr(cp_jurisdiction_code, 1, 2)
934 and pusd.school_dsts_code = substr(cp_jurisdiction_code, 4)
935 and pusd.jurisdiction_code = cp_resident_jurisdiction
936 and pusd.sch_information_category = 'FLS Interface Mapping'
937 and pusd.sch_information1 is not null;
938
939 lv_agency_code VARCHAR2(30) := lpad(9, 17, 9);
940 lv_futa_agency_code VARCHAR2(30);
941 lv_opt_agency_code VARCHAR2(30);
942
943 ld_effective_date DATE;
944
945 BEGIN
946
947 hr_utility.set_location(gv_package || '.get_fls_agency_code', 10);
948 hr_utility.trace('JD =' || p_jurisdiction_code);
949 hr_utility.trace('Eff Date =' || p_effective_date);
950
951 ld_effective_date := to_date(p_effective_date, 'mm/dd/yyyy');
952
953 hr_utility.set_location(gv_package || '.get_fls_agency_code', 20);
954
955 -- Federal
956 if p_jurisdiction_code = '00-000-0000' then
957 hr_utility.set_location(gv_package || '.get_fls_agency_code', 30);
958 open c_federal_agency_code(ld_effective_date);
959 fetch c_federal_agency_code into lv_agency_code,
960 lv_futa_agency_code;
961 close c_federal_agency_code;
962 /* If Tax Type is UI get agency code from fed_information2 */
963 if p_tax_type_code = 'UI' then
964 lv_agency_code := lv_futa_agency_code;
965 end if;
966
967 -- State
968 elsif substr(p_jurisdiction_code,3) = '-000-0000' then
969 hr_utility.set_location(gv_package || '.get_fls_agency_code', 40);
970 open c_state_agency_code(p_jurisdiction_code, ld_effective_date);
971 fetch c_state_agency_code into lv_agency_code;
972 close c_state_agency_code;
973 -- County
974 elsif substr(p_jurisdiction_code,7,5) = '-0000' and
975 substr(p_jurisdiction_code,3,4) <> '-000' then
976 hr_utility.set_location(gv_package || '.get_fls_agency_code', 50);
977 open c_county_agency_code(p_jurisdiction_code, ld_effective_date);
978 fetch c_county_agency_code into lv_agency_code,
979 lv_opt_agency_code;
980 close c_county_agency_code;
981
982 /* If Tax Type is OPT get agency code from attribute2.
983 If value of attribute2 is null get value of attribute1 as
984 agency code is same.
985 */
989
986 if p_tax_type_code = 'OPT' then
987 lv_agency_code := lv_opt_agency_code;
988 end if;
990 -- City
991 elsif length(p_jurisdiction_code) = 11 and
992 substr(p_jurisdiction_code,3) <> '-000-0000' then
993 hr_utility.set_location(gv_package || '.get_fls_agency_code', 60);
994 open c_city_agency_code(p_jurisdiction_code, ld_effective_date);
995 fetch c_city_agency_code into lv_agency_code, lv_opt_agency_code;
996 close c_city_agency_code;
997
998 /* If Tax Type is OPT get agency code from attribute2.
999 If value of attribute2 is null get value of attribute1 as
1000 agency code is same.
1001 */
1002 if p_tax_type_code = 'OPT' then
1003 lv_agency_code := lv_opt_agency_code;
1004 end if;
1005
1006 -- School
1007 elsif length(p_jurisdiction_code) = 8 then
1008 hr_utility.set_location(gv_package || '.get_fls_agency_code', 70);
1009 open c_school_agency_code( p_jurisdiction_code
1010 ,p_resident_jurisdiction
1011 ,ld_effective_date);
1012 fetch c_school_agency_code into lv_agency_code;
1013 close c_school_agency_code;
1014 end if;
1015 hr_utility.set_location(gv_package || '.get_fls_agency_code', 100);
1016
1017 return (lv_agency_code);
1018
1019 END get_fls_agency_code;
1020
1021
1022 /*******************************************************************
1023 ** Function called from the Fast Formula.
1024 ** More detail in Header File.
1025 *******************************************************************/
1026 FUNCTION get_fls_jd_values( p_tax_unit_id in number
1027 ,p_payroll_action_id in number
1028 )
1029 RETURN NUMBER
1030 IS
1031 -- Bug : 3343962 Performance Changes
1032 -- Bug : 9164356 Commented the old definition and added the new definition
1033 cursor c_action_info( cp_tax_unit_id in number
1034 ,cp_payroll_action_id in number) is
1035 /*
1036 select jurisdiction_code
1037 ,nvl(sum(to_number(action_information1)),0) action_information1
1038 ,nvl(sum(to_number(action_information2)),0) action_information2
1039 ,nvl(sum(to_number(action_information3)),0) action_information3
1040 ,nvl(sum(to_number(action_information4)),0) action_information4
1041 ,nvl(sum(to_number(action_information5)),0) action_information5
1042 ,nvl(sum(to_number(action_information6)),0) action_information6
1043 ,nvl(sum(to_number(action_information7)),0) action_information7
1044 ,nvl(sum(to_number(action_information8)),0) action_information8
1045 ,nvl(sum(to_number(action_information9)),0) action_information9
1046 ,nvl(sum(to_number(action_information10)),0) action_information10
1047 ,nvl(sum(to_number(action_information11)),0) action_information11
1048 ,nvl(sum(to_number(action_information12)),0) action_information12
1049 ,nvl(sum(to_number(action_information13)),0) action_information13
1050 ,nvl(sum(to_number(action_information14)),0) action_information14
1051 ,nvl(sum(to_number(action_information15)),0) action_information15
1052 ,nvl(sum(to_number(action_information16)),0) action_information16
1053 ,nvl(sum(to_number(action_information17)),0) action_information17
1054 ,nvl(sum(to_number(action_information18)),0) action_information18
1055 ,nvl(sum(to_number(action_information19)),0) action_information19
1056 ,nvl(sum(to_number(action_information20)),0) action_information20
1057 ,nvl(sum(to_number(action_information21)),0) action_information21
1058 ,nvl(sum(to_number(action_information22)),0) action_information22
1059 ,nvl(sum(to_number(action_information23)),0) action_information23
1060 ,nvl(sum(to_number(action_information24)),0) action_information24
1061 ,nvl(sum(to_number(action_information25)),0) action_information25
1062 ,nvl(sum(to_number(action_information26)),0) action_information26
1063 ,nvl(sum(to_number(action_information27)),0) action_information27
1064 ,nvl(sum(to_number(action_information28)),0) action_information28
1065 ,nvl(sum(to_number(action_information29)),0) action_information29
1066 ,action_information30
1067 from pay_action_information pai,
1068 pay_assignment_actions paa,
1069 pay_payroll_actions ppa -- Bug 3343962
1070 where pai.tax_unit_id = cp_tax_unit_id
1071 and paa.payroll_action_id = cp_payroll_action_id
1072 and ppa.payroll_action_id = cp_payroll_action_id
1073 and ppa.payroll_action_id = paa.payroll_action_id
1074 and pai.action_context_id = paa.serial_number
1075 and pai.action_context_type = 'AAP'
1076 and pai.action_information_category in ('US FEDERAL',
1077 'US STATE',
1078 'US COUNTY',
1079 'US CITY',
1080 'US SCHOOL DISTRICT'
1081 )
1082 group by jurisdiction_code,
1083 action_information30;*/
1084 /* Modified below Query for Bug#9872952*/
1085 /* The values which will be used for FIT Wages calculation will be controlled by FIT Exempt from Wage
1086 Accumulation value stored in action_information27.The new columns action_information31, action_information32
1087 and action_information33 are for FIT purpose.*/
1088 /* Added for 9164356 start*/
1089 select jurisdiction_code
1090 ,nvl(sum(to_number(decode(pai.action_information_category,'US STATE2',0,'US FEDERAL',decode(action_information27,'Y',0,action_information1),action_information1))),0) action_information1 --Modified for Bug#9872952
1094 ,nvl(sum(to_number(action_information5)),0) action_information5
1091 ,nvl(sum(to_number(action_information2)),0) action_information2
1092 ,nvl(sum(to_number(decode(pai.action_information_category,'US FEDERAL',decode(action_information27,'Y',0,action_information3),action_information3))),0) action_information3 --Modified for Bug#9872952
1093 ,nvl(sum(to_number(decode(pai.action_information_category,'US FEDERAL',decode(action_information27,'Y',0,action_information4),action_information4))),0) action_information4 --Modified for Bug#9872952
1095 ,nvl(sum(to_number(decode(pai.action_information_category,'US FEDERAL',decode(action_information27,'Y',0,action_information6),action_information6))),0) action_information6 --Modified for Bug#9872952
1096 ,nvl(sum(to_number(action_information7)),0) action_information7
1097 ,nvl(sum(to_number(action_information8)),0) action_information8
1098 ,nvl(sum(to_number(action_information9)),0) action_information9
1099 ,nvl(sum(to_number(action_information10)),0) action_information10
1100 ,nvl(sum(to_number(action_information11)),0) action_information11
1101 ,nvl(sum(to_number(action_information12)),0) action_information12
1102 ,nvl(sum(to_number(action_information13)),0) action_information13
1103 ,nvl(sum(to_number(action_information14)),0) action_information14
1104 ,nvl(sum(to_number(action_information15)),0) action_information15
1105 ,nvl(sum(to_number(action_information16)),0) action_information16
1106 ,nvl(sum(to_number(action_information17)),0) action_information17
1107 ,nvl(sum(to_number(action_information18)),0) action_information18
1108 ,nvl(sum(to_number(action_information19)),0) action_information19
1109 ,nvl(sum(to_number(action_information20)),0) action_information20
1110 ,nvl(sum(to_number(action_information21)),0) action_information21
1111 ,nvl(sum(to_number(action_information22)),0) action_information22
1112 ,nvl(sum(to_number(action_information23)),0) action_information23
1113 ,nvl(sum(to_number(action_information24)),0) action_information24
1114 ,nvl(sum(to_number(action_information25)),0) action_information25
1115 ,nvl(sum(to_number(action_information26)),0) action_information26
1116 ,nvl(sum(to_number(decode(pai.action_information_category,'US FEDERAL',0,action_information27))),0) action_information27 --Modified for Bug#9872952
1117 ,nvl(sum(to_number(action_information28)),0) action_information28
1118 ,nvl(sum(to_number(action_information29)),0) action_information29
1119 ,decode(pai.action_information_category,'US STATE2','0',action_information30) action_information30 /* Bug # 9603852 */
1120 ,nvl(sum(to_number(decode(pai.action_information_category,'US STATE2',action_information1,0))),0) sdi1_ee
1121 ,nvl(sum(to_number(decode(pai.action_information_category,'US FEDERAL',decode(action_information27,'Y',0,action_information2),action_information2))),0) action_information31 --Added for Bug#9872952
1122 ,nvl(sum(to_number(decode(pai.action_information_category,'US FEDERAL',decode(action_information27,'Y',0,action_information5),action_information5))),0) action_information32 --Added for Bug#9872952
1123 ,nvl(sum(to_number(decode(pai.action_information_category,'US FEDERAL',decode(action_information27,'Y',0,action_information23),action_information23))),0) action_information33 --Added for Bug#9872952
1124 from pay_action_information pai,
1125 pay_assignment_actions paa,
1126 pay_payroll_actions ppa
1127 where pai.tax_unit_id = cp_tax_unit_id
1128 and paa.payroll_action_id = cp_payroll_action_id
1129 and ppa.payroll_action_id = cp_payroll_action_id
1130 and ppa.payroll_action_id = paa.payroll_action_id
1131 and pai.action_context_id = paa.serial_number
1132 and pai.action_context_type = 'AAP'
1133 and pai.action_information_category in ('US FEDERAL',
1134 'US STATE',
1135 'US STATE2',
1136 'US COUNTY',
1137 'US CITY',
1138 'US SCHOOL DISTRICT'
1139 )
1140 group by jurisdiction_code,
1141 decode(pai.action_information_category,'US STATE2','0',action_information30);
1142 /* Added for 9164356 end*/
1143
1144 i_count NUMBER := 0;
1145
1146 BEGIN
1147 hr_utility.set_location(gv_package || '.get_fls_jd_values', 10);
1148
1149 /* Reset the PL/SQL tables */
1150 if pay_us_fls_reporting_pkg.ltr_action_info.count > 0 then
1151 for i in pay_us_fls_reporting_pkg.ltr_action_info.first ..
1152 pay_us_fls_reporting_pkg.ltr_action_info.last loop
1153 pay_us_fls_reporting_pkg.ltr_action_info(i).jurisdiction_code := null;
1154 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information1 := null;
1155 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information2 := null;
1156 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information3 := null;
1157 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information4 := null;
1158 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information5 := null;
1159 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information6 := null;
1160 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information7 := null;
1161 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information8 := null;
1162 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information9 := null;
1163 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information10 := null;
1164 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information11 := null;
1165 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information12 := null;
1166 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information13 := null;
1167 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information14 := null;
1171 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information18 := null;
1168 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information15 := null;
1169 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information16 := null;
1170 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information17 := null;
1172 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information19 := null;
1173 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information20 := null;
1174 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information21 := null;
1175 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information22 := null;
1176 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information23 := null;
1177 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information24 := null;
1178 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information25 := null;
1179 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information26 := null;
1180 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information27 := null;
1181 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information28 := null;
1182 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information29 := null;
1183 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information30 := null;
1184 pay_us_fls_reporting_pkg.ltr_action_info(i).sdi1_ee := null; --9164356
1185 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information31 := null; --Added for Bug#9872952
1186 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information32 := null; --Added for Bug#9872952
1187 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information33 := null; --Added for Bug#9872952
1188 end loop;
1189 pay_us_fls_reporting_pkg.ltr_action_info.delete;
1190 end if;
1191
1192 hr_utility.trace('Payroll Action ID=' || p_payroll_action_id);
1193 hr_utility.trace('Tax Unit ID=' || p_tax_unit_id);
1194 for action_rec in c_action_info(p_tax_unit_id, p_payroll_action_id)
1195 loop
1196 hr_utility.set_location(gv_package || '.get_fls_jd_values', 20);
1197 /* Commented out because of DB issue. Bug 1822467 */
1198 --pay_us_fls_reporting_pkg.ltr_action_info(i_count) := action_rec;
1199 pay_us_fls_reporting_pkg.ltr_action_info(i_count).jurisdiction_code
1200 := action_rec.jurisdiction_code;
1201 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information1
1202 := action_rec.action_information1;
1203 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information2
1204 := action_rec.action_information2;
1205 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information3
1206 := action_rec.action_information3;
1207 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information4
1208 := action_rec.action_information4;
1209 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information5
1210 := action_rec.action_information5;
1211 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information6
1212 := action_rec.action_information6;
1213 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information7
1214 := action_rec.action_information7;
1215 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information8
1216 := action_rec.action_information8;
1217 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information9
1218 := action_rec.action_information9;
1219 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information10
1220 := action_rec.action_information10;
1221 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information11
1222 := action_rec.action_information11;
1223 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information12
1224 := action_rec.action_information12;
1225 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information13
1226 := action_rec.action_information13;
1227 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information14
1228 := action_rec.action_information14;
1229 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information15
1230 := action_rec.action_information15;
1231 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information16
1232 := action_rec.action_information16;
1233 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information17
1234 := action_rec.action_information17;
1235 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information18
1236 := action_rec.action_information18;
1237 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information19
1238 := action_rec.action_information19;
1239 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information20
1240 := action_rec.action_information20;
1241 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information21
1242 := action_rec.action_information21;
1243 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information22
1244 := action_rec.action_information22;
1245 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information23
1246 := action_rec.action_information23;
1247 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information24
1248 := action_rec.action_information24;
1249 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information25
1250 := action_rec.action_information25;
1251 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information26
1252 := action_rec.action_information26;
1256 := action_rec.action_information28;
1253 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information27
1254 := action_rec.action_information27;
1255 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information28
1257 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information29
1258 := action_rec.action_information29;
1259 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information30
1260 := action_rec.action_information30;
1261 pay_us_fls_reporting_pkg.ltr_action_info(i_count).sdi1_ee
1262 := action_rec.sdi1_ee; --#9164356
1263 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information31 --Added for Bug#9872952
1264 := action_rec.action_information31;
1265 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information32 --Added for Bug#9872952
1266 := action_rec.action_information32;
1267 pay_us_fls_reporting_pkg.ltr_action_info(i_count).action_information33 --Added for Bug#9872952
1268 := action_rec.action_information33;
1269
1270 i_count := i_count + 1;
1271 hr_utility.set_location(gv_package || '.get_fls_jd_values', 30);
1272 end loop;
1273
1274 hr_utility.set_location(gv_package || '.get_fls_jd_values', 40);
1275 if pay_us_fls_reporting_pkg.ltr_action_info.count > 0 then
1276 for i in pay_us_fls_reporting_pkg.ltr_action_info.first ..
1277 pay_us_fls_reporting_pkg.ltr_action_info.last loop
1278 hr_utility.trace('JD='||pay_us_fls_reporting_pkg.ltr_action_info(i).jurisdiction_code);
1279 end loop;
1280 end if;
1281 hr_utility.trace('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
1282
1283 hr_utility.set_location(gv_package || '.get_fls_jd_values', 100);
1284 return (1);
1285 END get_fls_jd_values;
1286
1287 /*******************************************************************
1288 ** Function called from the Fast Formula.
1289 ** More detail in Header File.
1290 ** Function returns:
1291 ** - Gross_amt
1292 ** - Subject_amt
1293 ** - Taxable_amt
1294 ** - Resident EE Tax Amt
1295 ** - Resident ER Tax Amt
1296 ** - Non-Resident EE Tax Amt
1297 *******************************************************************/
1298 FUNCTION get_fls_tax_type_values(
1299 p_tax_type in varchar2
1300 ,p_jurisdiction in varchar2
1301 ,p_resident_jurisdiction in varchar2
1302 )
1303 RETURN VARCHAR2
1304 IS
1305
1306 ln_gross_amt NUMBER(12,2) := 0;
1307 ln_subject_amt NUMBER(12,2) := 0;
1308 ln_amt1 NUMBER(12,2) := 0;
1309 ln_amt2 NUMBER(12,2) := 0;
1310 ln_amt3 NUMBER(12,2) := 0;
1311 ln_amt4 NUMBER(12,2) := 0;
1312 ln_amt5 NUMBER(12,2) := 0;
1313 ln_amt6 NUMBER(12,2) := 0;
1314 ln_amt7 NUMBER(12,2) := 0;
1315 ln_taxable_amt NUMBER(12,2) := 0;
1316 ln_r_ee_tax_amt NUMBER(12,2) := 0;
1317 ln_r_er_tax_amt NUMBER(12,2) := 0;
1318 ln_nr_ee_tax_amt NUMBER(12,2) := 0;
1319 ln_nr_er_tax_amt NUMBER(12,2) := 0;
1320
1321 ln_nr_flag VARCHAR2(1) := 'N';
1322 ln_r_flag VARCHAR2(1) := 'N';
1323
1324 lv_return VARCHAR2(200);
1325
1326 BEGIN
1327 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 10);
1328 hr_utility.trace('Tax Type Code=' || p_tax_type);
1329 hr_utility.trace('Jurisdiction Code=' || p_jurisdiction);
1330 hr_utility.trace('PL/SQL Count=' || pay_us_fls_reporting_pkg.ltr_action_info.count);
1331
1332 for i in pay_us_fls_reporting_pkg.ltr_action_info.first ..
1333 pay_us_fls_reporting_pkg.ltr_action_info.last loop
1334
1335 hr_utility.trace('PL/SQL Jurisdiction Code=' ||
1336 pay_us_fls_reporting_pkg.ltr_action_info(i).jurisdiction_code);
1337
1338 hr_utility.trace('PL/SQL Action30 is= ' ||
1339 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information30);
1340
1341 hr_utility.trace('PL/SQL p_jurisdiction is= ' ||
1342 p_jurisdiction);
1343
1344 hr_utility.trace('PL/SQL p_resident_jurisdiction is = ' ||
1345 nvl(p_resident_jurisdiction,'NOT ARCHIVED'));
1346
1347 if pay_us_fls_reporting_pkg.ltr_action_info(i).jurisdiction_code = p_jurisdiction then
1348
1349 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 20);
1350
1351 if p_jurisdiction = '00-000-0000' then
1352 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 30);
1353
1354 if p_tax_type <> 'IT' THEN -- If clause added for Bug#9872952
1355 -- Regular Earnings
1356 ln_amt1 := ln_amt1 +
1357 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information2;
1358 -- Gross Earnings
1359 ln_amt2 := ln_amt2 +
1360 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information23;
1361 -- Pre Tax Deduction
1362 ln_amt3 := ln_amt3 +
1363 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information5;
1364
1365 end if;
1366
1367 if p_tax_type = 'IT' then
1368 -- Added for Bug#9872952
1369 /*Brought in the Complete calculation for FIT so that the values are affected
1370 only for FIT Wages based on the FIT Exempt from Wage Accumulation Flag*/
1371 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 40);
1372 -- Regular Earnings
1373 ln_amt1 := ln_amt1 +
1374 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information31;
1375 -- Gross Earnings
1376 ln_amt2 := ln_amt2 +
1377 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information33;
1378 -- Pre Tax Deduction
1379 ln_amt3 := ln_amt3 +
1380 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information32;
1381 -- End Bug#9872952
1382 -- Supp Earning for FIT
1383 ln_amt4 := ln_amt4 +
1384 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information3;
1385 -- Supp Earning for NWFIT
1386 ln_amt5 := ln_amt5 +
1387 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information4;
1388 -- Pre Tax Deduction for FIT
1389 ln_amt6 := ln_amt6 +
1390 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information6;
1391 -- FIT Withheld
1392 ln_r_ee_tax_amt := ln_r_ee_tax_amt +
1393 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information1;
1394
1395 elsif p_tax_type = 'UI' then
1396 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 50);
1397 -- Supp Earning for FUTA
1398 ln_amt4 := ln_amt4 +
1399 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information19;
1400 -- Pre Tax Deduction for FUTA
1401 ln_amt6 := ln_amt6 +
1402 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information20;
1403 -- Taxable
1404 ln_amt7 := ln_amt7 +
1405 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information21;
1406 -- Liability
1407 ln_r_er_tax_amt := ln_r_er_tax_amt +
1408 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information22;
1409
1410 elsif p_tax_type = 'OASDI' then
1411 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 60);
1412 -- Supp Earning for SS
1413 ln_amt4 := ln_amt4 +
1414 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information11;
1415 -- Pre Tax Deduction for SS
1416 ln_amt6 := ln_amt6 +
1417 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information12;
1418 -- Taxable
1419 ln_amt7 := ln_amt7 +
1420 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information7;
1421 -- SS EE Withheld
1422 ln_r_ee_tax_amt := ln_r_ee_tax_amt +
1423 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information8;
1424 -- SS ER Liability
1425 ln_r_er_tax_amt := ln_r_er_tax_amt +
1426 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information9;
1427 elsif p_tax_type = 'HI' then
1428 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 70);
1429 -- Supp Earning for Medicare
1430 ln_amt4 := ln_amt4 +
1431 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information17;
1432 -- Pre Tax Deduction for Medicare
1433 ln_amt6 := ln_amt6 +
1434 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information18;
1435 -- Medicare EE Taxable
1436 ln_amt7 := ln_amt7 +
1437 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information13;
1438 -- Medicare Withheld
1439 ln_r_ee_tax_amt := ln_r_ee_tax_amt +
1440 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information14;
1441 -- Medicare ER Liability
1442 ln_r_er_tax_amt := ln_r_er_tax_amt +
1443 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information15;
1444 elsif p_tax_type = 'EIC' then
1445 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 80);
1446 ln_amt1 := 0;
1447 ln_amt2 := 0;
1448 ln_amt3 := 0;
1449 -- EIC Advance
1450 ln_r_ee_tax_amt := ln_r_ee_tax_amt +
1451 (-1 * pay_us_fls_reporting_pkg.ltr_action_info(i).action_information26);
1452
1453 end if;
1454 exit;
1455 -- State Jurisdiction code
1456 elsif substr(p_jurisdiction,3) = '-000-0000' and
1457 p_jurisdiction <> '00-000-0000' then
1458
1462 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 120);
1459 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 110);
1460
1461 if p_tax_type = 'IT' then
1463 --SIT Gross
1464 ln_amt2 := ln_amt2 +
1465 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information17;
1466 -- SIT Subj Whable
1467 ln_amt4 := ln_amt4 +
1468 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information2;
1469 -- SIT Subj NWhable
1470 ln_amt5 := ln_amt5 +
1471 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information3;
1472 -- SIT Pre Tax Redns
1473 ln_amt3 := ln_amt3 +
1474 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information4;
1475 -- SIT Withheld
1476 ln_r_ee_tax_amt := ln_r_ee_tax_amt +
1477 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information1;
1478
1479 elsif p_tax_type = 'UI' then
1480 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 130);
1481 /* Always report the ER component if it is there. -- 4335410
1482 In case ER is null report EE.
1483 SUI and SDI ER Liability is always passed as 0.
1484 Doing an if to get EE or ER Wages instead on NVL as the PL/SQL table will
1485 have a Zero value if there is a NULL in the table. */
1486
1487 -- SUI EE Gross, SUI ER Gross
1488 if nvl(pay_us_fls_reporting_pkg.ltr_action_info(i).action_information29,0) <> 0 then
1489 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 140);
1490 ln_amt2 := ln_amt2 +
1491 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information29;
1492 else
1493 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 150);
1494 ln_amt2 := ln_amt2 +
1495 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information28;
1496 end if;
1497
1498 -- nvl(SUI EE Subj Whable, SUI ER Subj Whable)
1499 if nvl(pay_us_fls_reporting_pkg.ltr_action_info(i).action_information19,0) <> 0 then
1500 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 160);
1501 ln_amt4 := ln_amt4 +
1502 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information19;
1503 else
1504 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 170);
1505 ln_amt4 := ln_amt4 +
1506 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information15;
1507 end if;
1508
1509 -- nvl(SUI EE Pre Tax Redns, SUI ER Pre Tax Redns)
1510 if nvl(pay_us_fls_reporting_pkg.ltr_action_info(i).action_information20,0) <> 0 then
1511 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 180);
1512 ln_amt3 := ln_amt3 +
1513 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information20;
1514 else
1515 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 190);
1516 ln_amt3 := ln_amt3 +
1517 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information16;
1518 end if;
1519
1520 -- nvl(SUI EE Taxable, SUI ER Taxable)
1521 if nvl(pay_us_fls_reporting_pkg.ltr_action_info(i).action_information18,0) <> 0 then
1522 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 200);
1523 ln_amt7 := ln_amt7 +
1524 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information18;
1525 else
1526 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 210);
1527 ln_amt7 := ln_amt7 +
1528 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information14;
1529 end if;
1530
1531 -- SUI EE Withheld
1532 ln_r_ee_tax_amt := ln_r_ee_tax_amt +
1533 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information13;
1534 -- SUI ER Liability
1535 ln_r_er_tax_amt := ln_r_er_tax_amt +
1536 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information21;
1537
1538 elsif p_tax_type in ('SDI','SDI1','HI') then -- Added 'SDI1' bug 9164356
1539 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 220);
1540 /******************************************************
1541 Checking for SDI and HI because for MA the tax_type
1542 will be HI. The HI balances are stored in the same SDI
1543 balances as all other states.
1544 For all other states the tax_type is SDI.
1545 ********************************************************
1546 Always report the ER component if it is there. -- 4335410
1547 In case ER is null report EE.
1548 SDI ER Liability is always passed as 0
1549 *******************************************************/
1550 --SDI EE Gross, SDI ER Gross
1551 if nvl(pay_us_fls_reporting_pkg.ltr_action_info(i).action_information27,0) <> 0 then
1552 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 230);
1553 ln_amt2 := ln_amt2 +
1554 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information27;
1555 else
1556 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 240);
1557 ln_amt2 := ln_amt2 +
1558 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information26;
1559 end if;
1560
1561 -- nvl(SDI EE Subj Whable, SDI ER Subj Whable)
1562 if nvl(pay_us_fls_reporting_pkg.ltr_action_info(i).action_information11,0) <> 0 then
1563 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 250);
1564 ln_amt4 := ln_amt4 +
1565 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information11;
1566 else
1567 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 260);
1568 ln_amt4 := ln_amt4 +
1569 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information7;
1570 end if;
1571
1572 -- nvl(SDI EE Pre Tax Redns, SDI ER Pre Tax Redns)
1573 if nvl(pay_us_fls_reporting_pkg.ltr_action_info(i).action_information12,0) <> 0 then
1574 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 270);
1575 ln_amt3 := ln_amt3 +
1576 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information12;
1577 else
1578 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 280);
1579 ln_amt3 := ln_amt3 +
1580 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information8;
1581 end if;
1582
1583 -- nvl(SDI EE Taxable, SDI ER Taxable)
1584 if nvl(pay_us_fls_reporting_pkg.ltr_action_info(i).action_information10,0) <> 0 then
1585 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 290);
1586 ln_amt7 := ln_amt7 +
1587 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information10;
1588 else
1589 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 300);
1590 ln_amt7 := ln_amt7 +
1591 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information6;
1592 end if;
1593
1594 /* Commented bug # 9164356 start
1595 -- SDI EE Withheld
1596 ln_r_ee_tax_amt := ln_r_ee_tax_amt +
1597 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information5;
1598 -- SDI ER Liability
1599 ln_r_er_tax_amt := ln_r_er_tax_amt +
1600 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information9;
1601 Commented bug # 9164356 end*/
1602
1603 if (p_tax_type = 'SDI1') then -- Added if else condition #9164356
1604 -- SDI1 EE Withheld
1605 ln_r_ee_tax_amt := ln_r_ee_tax_amt +
1606 pay_us_fls_reporting_pkg.ltr_action_info(i).sdi1_ee;
1607 -- SDI1 ER Liability
1608 ln_r_er_tax_amt := ln_r_er_tax_amt +
1609 0;
1610 else
1611 -- SDI EE Withheld
1612 ln_r_ee_tax_amt := ln_r_ee_tax_amt +
1613 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information5;
1614 -- SDI ER Liability
1615 ln_r_er_tax_amt := ln_r_er_tax_amt +
1616 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information9;
1617 end if;
1618 elsif p_tax_type = 'WC' then
1619 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 350);
1620 null;
1621 /*
1622 -- WC Withheld
1623 ln_r_ee_tax_amt := ln_r_ee_tax_amt +
1624 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information22;
1625 */
1626 end if;
1627
1628 exit;
1629 -- County Jurisdiction code
1630 elsif substr(p_jurisdiction,7) = '-0000' and
1631 substr(p_jurisdiction,3,4) <> '-000'then
1632 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 400);
1633 -- Gross Earnings
1634 ln_amt2 := ln_amt2 +
1635 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information7;
1636
1637 if p_tax_type = 'IT' then
1638 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 410);
1639 -- County Subj Whable
1640 ln_amt4 := ln_amt4 +
1641 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information2;
1642 -- County Subj NWhable
1643 ln_amt5 := ln_amt5 +
1644 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information3;
1645 -- County Pre Tax Redns
1646 ln_amt3 := ln_amt3 +
1647 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information4;
1648 -- County Withheld
1649 if nvl(pay_us_fls_reporting_pkg.ltr_action_info(i).action_information30, 'R')
1650 = 'R' then
1651 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 420);
1652 ln_r_ee_tax_amt := ln_r_ee_tax_amt +
1653 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information1;
1654 ln_r_flag := 'Y';
1655 elsif nvl(pay_us_fls_reporting_pkg.ltr_action_info(i).action_information30, 'R')
1656 = 'NR' then
1657 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 430);
1658 ln_nr_ee_tax_amt := ln_nr_ee_tax_amt +
1659 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information1;
1660 ln_nr_flag := 'Y';
1661 end if;
1662
1663 elsif p_tax_type = 'OPT' then
1664 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 440);
1665 -- Head Tax Subj Whable
1666 ln_amt4 := ln_amt4 +
1667 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information2;
1668 -- Head Tax Subj NWhable
1669 ln_amt5 := ln_amt5 +
1670 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information3;
1671 -- Head Tax Pre Tax Redns
1672 ln_amt3 := ln_amt3 +
1673 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information4;
1674
1675 if nvl(pay_us_fls_reporting_pkg.ltr_action_info(i).action_information30, 'R')
1676 = 'R' then
1677 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 450);
1678 -- Head Tax Withheld
1679 ln_r_ee_tax_amt := ln_r_ee_tax_amt +
1680 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information6;
1681 -- Head Tax Liability
1682 ln_r_er_tax_amt := ln_r_er_tax_amt +
1683 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information5;
1684 ln_r_flag := 'Y';
1685 elsif nvl(pay_us_fls_reporting_pkg.ltr_action_info(i).action_information30, 'R')
1686 = 'NR' then
1687 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 460);
1688 -- Head Tax Withheld
1689 ln_nr_ee_tax_amt := ln_nr_ee_tax_amt +
1690 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information6;
1691 -- Head Tax Liability
1692 ln_nr_er_tax_amt := ln_nr_er_tax_amt +
1693 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information5;
1694 ln_nr_flag := 'Y';
1695 end if;
1696 end if;
1697 if ln_nr_flag = 'Y' and ln_r_flag = 'Y' then
1698 exit;
1699 end if;
1700
1701 -- City Jurisdiction code
1702 elsif length(p_jurisdiction) = 11 and
1703 substr(p_jurisdiction,3) <> '-000-0000'then
1704 -- Gross Earnings
1705 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 500);
1706 ln_amt2 := ln_amt2 +
1707 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information7;
1708
1709 if p_tax_type = 'IT' then
1710 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 510);
1711 -- City Subj Whable
1712 ln_amt4 := ln_amt4 +
1713 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information2;
1714 -- City Subj NWhable
1715 ln_amt5 := ln_amt5 +
1716 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information3;
1717 -- City Pre Tax Redns
1718 ln_amt3 := ln_amt3 +
1719 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information4;
1720 -- City Withheld
1721 if nvl(pay_us_fls_reporting_pkg.ltr_action_info(i).action_information30, 'R') = 'R' then
1722 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 520);
1723 ln_r_ee_tax_amt := ln_r_ee_tax_amt +
1724 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information1;
1725 ln_r_flag := 'Y';
1726 elsif nvl(pay_us_fls_reporting_pkg.ltr_action_info(i).action_information30, 'R') = 'NR' then
1727 ln_nr_ee_tax_amt := ln_nr_ee_tax_amt +
1728 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information1;
1729 ln_nr_flag := 'Y';
1730 end if;
1731
1732 elsif p_tax_type = 'OPT' then
1733 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 530);
1734 -- Head Tax Subj Whable
1735 ln_amt4 := ln_amt4 +
1736 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information2;
1737 -- Head Tax Subj NWhable
1738 ln_amt5 := ln_amt5 +
1739 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information3;
1740 -- Head Tax Pre Tax Redns
1741 ln_amt3 := ln_amt3 +
1742 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information4;
1743 -- Head Tax Withheld
1744 if nvl(pay_us_fls_reporting_pkg.ltr_action_info(i).action_information30, 'R') = 'R' then
1745 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 540);
1746 ln_r_ee_tax_amt := ln_r_ee_tax_amt +
1747 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information6;
1748 -- Head Tax Liability
1749 ln_r_er_tax_amt := ln_r_er_tax_amt +
1750 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information5;
1751 ln_r_flag := 'Y';
1752 elsif nvl(pay_us_fls_reporting_pkg.ltr_action_info(i).action_information30, 'R') = 'NR' then
1753 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 550);
1754 -- Head Tax Withheld
1755 ln_nr_ee_tax_amt := ln_nr_ee_tax_amt +
1756 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information6;
1757 -- Head Tax Liability
1758 ln_nr_er_tax_amt := ln_nr_er_tax_amt +
1759 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information5;
1760 ln_nr_flag := 'Y';
1761 end if;
1762 end if;
1763 if ln_nr_flag = 'Y' and ln_r_flag = 'Y' then
1764 exit;
1765 end if;
1766
1767 -- School Jurisdiction code
1768 elsif length(p_jurisdiction) = 8 then
1769 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 600);
1770 if nvl(Pay_us_fls_reporting_pkg.ltr_action_info(i).action_information30, 'R')
1771 = nvl(p_resident_jurisdiction, 'R') then
1772
1773 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 600);
1774 hr_utility.trace('ANK Gross val = ' ||
1775 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information5);
1776 -- Gross Earnings
1777 ln_amt2 := ln_amt2 +
1778 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information5;
1779
1780 if p_tax_type = 'IT' then
1781 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 610);
1782 -- School Subj Whable
1783 ln_amt4 := ln_amt4 +
1784 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information2;
1785 -- School Subj NWhable
1786 ln_amt5 := ln_amt5 +
1787 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information3;
1788 -- School Pre Tax Redns
1789 ln_amt3 := ln_amt3 +
1790 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information4;
1791 -- School Withheld
1792 ln_r_ee_tax_amt := ln_r_ee_tax_amt +
1793 pay_us_fls_reporting_pkg.ltr_action_info(i).action_information1;
1794 end if;
1795 exit;
1796 end if;
1797
1798 end if; /* End of Jurisdiction Check */
1799 end if; /* End of PL/SQL Table and Parameter JD check */
1800 end loop; /* End of PL/SQL Table Loop */
1801
1802 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 700);
1803 hr_utility.trace('Amt1=' || ln_amt1);
1804 hr_utility.trace('Amt2=' || ln_amt2);
1805 hr_utility.trace('Amt3=' || ln_amt3);
1806 hr_utility.trace('Amt4=' || ln_amt4);
1807 hr_utility.trace('Amt5=' || ln_amt5);
1808 hr_utility.trace('Amt6=' || ln_amt6);
1809 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 710);
1810
1811 ln_gross_amt := ln_amt2;
1812 if p_tax_type in ('IT', 'OPT', 'EIC') then
1813 ln_subject_amt := ln_amt1 + ln_amt4 + ln_amt5;
1814 ln_taxable_amt := ln_amt1 + ln_amt4 + ln_amt5 - (ln_amt3 - ln_amt6);
1815 else
1816 ln_subject_amt := ln_amt1 + ln_amt4 - (ln_amt3 - ln_amt6);
1817 ln_taxable_amt := ln_amt7;
1818 end if;
1819
1820 if p_tax_type in ('SDI', 'UI') and
1821 substr(p_jurisdiction,3) = '-000-0000' and
1822 p_jurisdiction <> '00-000-0000' then
1823 ln_r_er_tax_amt := 0;
1824 end if;
1825
1826 hr_utility.trace('Gross=' || ln_gross_amt);
1827 hr_utility.trace('Subj=' || ln_subject_amt);
1828 hr_utility.trace('Taxable=' || ln_taxable_amt);
1829 hr_utility.trace('Withheld='|| ln_r_ee_tax_amt);
1830
1831 /* Return the formatted values */
1832 select ltrim(rtrim(to_char(ln_gross_amt, decode(sign(ln_gross_amt),
1833 -1, '0000000000.00',
1834 '00000000000.00')))) ||
1835 ltrim(rtrim(to_char(ln_subject_amt, decode(sign(ln_subject_amt),
1836 -1, '0000000000.00',
1837 '00000000000.00')))) ||
1838 ltrim(rtrim(to_char(ln_taxable_amt, decode(sign(ln_taxable_amt),
1839 -1, '0000000000.00',
1840 '00000000000.00')))) ||
1841 ltrim(rtrim(to_char(ln_r_ee_tax_amt, decode(sign(ln_r_ee_tax_amt),
1842 -1, '0000000000.00',
1843 '00000000000.00')))) ||
1844 ltrim(rtrim(to_char(ln_r_er_tax_amt, decode(sign(ln_r_er_tax_amt),
1845 -1, '0000000000.00',
1846 '00000000000.00')))) ||
1847 ltrim(rtrim(to_char(ln_nr_ee_tax_amt, decode(sign(ln_nr_ee_tax_amt),
1848 -1, '0000000000.00',
1849 '00000000000.00'))))
1850 into lv_return from dual;
1851
1852 hr_utility.trace('Return Value=' || lv_return);
1853 hr_utility.set_location(gv_package || '.get_fls_tax_type_values', 800);
1854 return(lv_return);
1855 END get_fls_tax_type_values;
1856
1857 --BEGIN
1858 -- hr_utility.trace_on(null, 'FLSP');
1859
1860 END pay_us_fls_reporting_pkg;