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