1 PACKAGE BODY pay_us_payroll_utils AS
2 /* $Header: pyusutil.pkb 120.1.12000000.1 2007/01/18 03:09:22 appldev 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_payroll_utils
21
22 Description : The package has all the common packages used in
23 US Payroll.
24
25 Change List
26 -----------
27 Date Name Vers Bug No Description
28 ----------- ---------- ------ ------- ------------------------------------
29 24-APR-2003 ahanda 115.0 Created.
30 30-MAY-2003 vnatari 115.1 2938540 modified check_balance_status to avoid
31 reports erroring out
32 30-MAY-2003 vinaraya 115.2 2973119 Added code for city tax information
33 in populate_jit_information and
34 check for county and city tax in
35 get_tax_exists.
36 05-JUN-2003 vinaraya 115.3 2973119 Changed the code as per review comments
37 for the bug.Modified the return
38 statments in get_tax_exists function.
39 05-JUN-2003 vnatari 115.4 2938540 overloaded function get_tax_exists and
40 added code for WC
41 05-JUN-2003 ahanda 115.5 3012587 Changed code for WC.
42 23-JUN-2003 djoshi 115.6 Changed the code in
43 check_balance_status
44 to make sure that we compare bg
45 for pba also.
46 07-AUG-2003 sshetty 115.8 Value for DCP limit is derived.
47 21-AUG-2003 meshah 115.9 removed the call to
48 pay_emp_action_arch.set_error_message
49 21-AUG-2003 meshah 115.10 Uncommented commit and exit.
50 29-AUG-2003 rsethupa 115.11 2527077 Added functions formated_header_string
51 and formated_data_string that will be
52 used by reports displaying in HTML and
53 CSV formats.
54 29-AUG-2003 rsethupa 115.12 2527077 Added local variables and comments.
55 11-SEP-2003 meshah 115.13 3136815 changed check_balance_status
56 function. created two cursors
57 c_get_valid_count and
58 c_get_attribute_count. A balance is
59 valid only if the counts returned
60 from both the cursors are same.
61 13-NOV-2003 tclewis 115.14 Added code to STEIC.
62 18-DEC-2003 saurgupt 115.17 3312482 Remove the call to
63 pay_core_utils.push_message and
64 pay_core_utils.push_tokens
65 29-DEC-2003 saurgupt 115.18 3340952 Calls to pay_core_utils.push_message
66 and pay_core_utils.push_tokens are
67 added again to show warning messages
68 in the log file of Unacceptable Tax
69 Balances report.
70 06-JAN-2004 meshah 115.19 3349198 Now when getting city taxes we check
71 if there is a user defined city. If
72 it is then we return N.
73 Get_tax_exists has been changed for
74 this.
75 13-JAN-2004 meshah 115.21 3349198 For user defined cities now checking
76 for just the first char instead of
77 the whole string.
78 30-JAN-2004 rmonge 115.22 3358113 Modified cursor 'c_get_states_jit'
79 to use a decode on
80 sdi_ee_wage_limit. This will
81 return STA_INFORMATION1 instead
82 in case sdi_ee_wage_limit is null
83 or 0. STA_INFORMATION1 stores
84 the sdi_ee_wage_limit per week.
85 18-MAR-2004 sdahiya 115.23 3258868 Modified check_balance_status, now
86 truncating the date passed to year.
87
88 3179050 Modified function formated_data_string
89 to display nothing for NULL values
90 for HTML format.
91
92 Both these changes were already done in
93 ver 115.14 and 115.15 respectively. But
94 ver 115.16 was modified over 115.13. So
95 doing required changes again.
96
97 Added p_legislation_code parameter to
98 check_balance_status to allow CA package
99 to act as wrapper and call this package
100 for actual results. This parameter is
101 defaulted to 'US' to avoid breaking of
102 existing calls.
103 26-APR-2004 ahanda 115.24 Added function get_parameter.
104 20-SEP-2004 tmehra 115.25 Changed the limit calculation for
105 403b Catchup and 457 Catchup Limits.
106 20-DEC-2004 schauhan 115.26 3892148 Added function ssn_reporting_preferences
107 for Check Writer and Deposit Advice Reports.
108 25-DEC-2004 schauhan 115.27 3892148 Made changes to function
109 ssn_reporting_preferences.
110 12-JAN-2005 ahanda 115.28 3980866 Added check for FUTA at state level for
111 the mentioned bug.
112 13-JAN-2005 schauhan 115.29 3892148 Added comments to the funtion
113 ssn_reporting_preferences
114 28-jan-2005 djoshi 115.30 Check_balance_status should return 'N' if
115 the no balance is associated with attribute
116 currently we dont have any zero check
117 19-APR-2005 ahanda 115.31 Added a new function get_min_action
118 to get the min assignment_action_id
119 for a given business_group, GRE, payroll
120 and dates.
121 21-APR-2005 schauhan 115.32 Bug 3969061. Added a check for 'FUTA ' to
122 get_tax_exists in the state section.
123 25-MAY-2005 ahanda 115.33 Changed function get_parameter to check if
124 exact param exists i.e. ' ' || name || '='
125 24-AUG-2005 sackumar 115.34 4518409 Changed function get_parameter to check if
126 exact param exists i.e. ' ' || name || '='
127 except for the first token in the
128 legislative_parameters field.
129 *****************************************************************************/
130
131 /*****************************************************************************
132 ** Package Local Variables
133 *****************************************************************************/
134 gv_package VARCHAR2(100) := 'pay_us_payroll_utils';
135 gc_csv_delimiter VARCHAR2(1) := ',';
136 gc_csv_data_delimiter VARCHAR2(1) := '"';
137
138 gv_html_start_data VARCHAR2(5) := '<td>' ;
139 gv_html_end_data VARCHAR2(5) := '</td>' ;
140
141
142 /*****************************************************************************
143 Name : populate_jit_information
144 Purpose : This procedure populates a PL/SQL table with JIT information
145 Arguments :
146 Notes :
147 *****************************************************************************/
148 PROCEDURE populate_jit_information(
149 p_effective_date in date default sysdate
150 ,p_get_federal in varchar2 default 'N'
151 ,p_get_state in varchar2 default 'N'
152 ,p_get_county in varchar2 default 'N'
153 ,p_get_city in varchar2 default 'N'
154 ,p_jurisdiction_code in varchar2 default NULL) is
155
156 lv_state_code VARCHAR2(2);
157 lv_sit_exists VARCHAR2(1);
158 ln_sdi_ee_limit NUMBER;
159 ln_sdi_er_limit NUMBER;
160 ln_sui_ee_limit NUMBER;
161 ln_sui_er_limit NUMBER;
162 lv_steic_exists VARCHAR2(1);
163 ln_state_futa_rate NUMBER;
164
165 lv_jurisdiction_code VARCHAR2(11);
166 lv_county_tax_exists VARCHAR2(1);
167 lv_county_sd_tax_exists VARCHAR2(1);
168 lv_county_head_tax_exists VARCHAR2(1);
169
170 lv_temp_state_code VARCHAR2(3);
171 lv_county_code VARCHAR2(4);
172 lv_city_code VARCHAR2(5);
173 lv_temp_code VARCHAR2(11);
174 ln_index NUMBER;
175
176 lv_city_jurisdiction_code VARCHAR2(11);
177 lv_city_tax_exists VARCHAR2(1);
178 lv_city_head_tax_exists VARCHAR2(1);
179 lv_city_sd_tax_exists VARCHAR2(1);
180
181 ln_fed_count NUMBER := 0;
182 ln_state_count NUMBER := 0;
183 ln_county_count NUMBER := 0;
184 ln_city_count NUMBER := 0;
185 ln_schdist_count NUMBER := 0;
186
187 ln_futa_wage NUMBER;
188 ln_futa_rate NUMBER;
189 ln_ss_ee_wage NUMBER;
190 ln_ss_ee_rate NUMBER;
191 ln_ss_er_wage NUMBER;
192 ln_ss_er_rate NUMBER;
193 ln_medi_ee_rate NUMBER;
194 ln_medi_er_rate NUMBER;
195 ln_401k NUMBER;
196 ln_403b NUMBER;
197 ln_457 NUMBER;
198 ln_401k_catchup NUMBER;
199 ln_403_catchup NUMBER;
200 ln_457_catchup NUMBER;
201 ln_dcp_limit NUMBER;
202
203 lv_error_message VARCHAR2(500);
204 lv_procedure_name VARCHAR2(100) := '.populate_jit_information';
205 ln_step NUMBER;
206
207 cursor c_get_federal_jit (cp_effective_date in date
208 ,cp_fed_info_category in varchar2) is
209 select futa_wage_limit, futa_rate,
210 ss_ee_wage_limit, ss_ee_rate,
211 ss_er_wage_limit, ss_er_rate,
212 medi_ee_rate, medi_er_rate,
213 fed_information1, fed_information2
214 from pay_us_federal_tax_info_f
215 where cp_effective_date between effective_start_date
216 and effective_end_date
217 and fed_information_category = cp_fed_info_category;
218
219 /* Rosie monge chaning the cursor to fix bug 3358113 */
220 /* Added decode statement to sdi_ee_wage_limit */
221 cursor c_get_states_jit (cp_effective_date in date) is
222 select state_code,
223 sit_exists,
224 sui_ee_wage_limit,
225 sui_er_wage_limit,
226 decode(sdi_ee_wage_limit,
227 NULL, STA_INFORMATION1,
228 0, STA_INFORMATION1,
229 sdi_ee_wage_limit) sdi_ee_wage_limit,
230 sdi_er_wage_limit,
231 nvl(sta_information17,'N'),
232 sta_information19 futa_rate
233 from pay_us_state_tax_info_f
234 where cp_effective_date between effective_start_date
235 and effective_end_date
236 and sta_information_category = 'State tax limit rate info'
237 order by 1 ;
238
239 cursor c_get_county_jit (cp_effective_date in date) is
240 select jurisdiction_code,
241 county_tax,
242 head_tax,
243 school_tax
244 from pay_us_county_tax_info_f
245 where cp_effective_date between effective_start_date
246 and effective_end_date
247 and cnty_information_category = 'County tax status info'
248 order by 1 ;
249
250 /*******************************************************************
251 ** Cursor to populate ltr_city_info_tax pl/sql table **
252 ** Bug Number: 2973119 Changes start **
253 ********************************************************************/
254
255 cursor c_get_city_jit ( cp_effective_date in date
256 , cp_jurisdiction_code in varchar2) is
257 select jurisdiction_code,
258 city_tax,
259 head_tax,
260 school_tax
261 from pay_us_city_tax_info_f
262 where cp_effective_date between effective_start_date
263 and effective_end_date
264 and jurisdiction_code = cp_jurisdiction_code
265 and city_information_category = 'City tax status info';
266
267 /********** Bug Number:2973119 End ***************************/
268
269 BEGIN
270 ln_step := 1;
271 hr_utility.set_location(gv_package || lv_procedure_name, 1);
272 /***************************************************************
273 ** Build a PL/SQL table which has federal tax info
274 ***************************************************************/
275 if p_get_federal = 'Y' and
276 pay_us_payroll_utils.ltr_fed_tax_info.count < 1 then
277 ln_step := 5;
278 open c_get_federal_jit (p_effective_date, '401K LIMITS');
279 fetch c_get_federal_jit into ln_futa_wage, ln_futa_rate,
280 ln_ss_ee_wage, ln_ss_ee_rate,
281 ln_ss_er_wage, ln_ss_er_rate,
282 ln_medi_ee_rate, ln_medi_er_rate,
283 ln_401k, ln_401k_catchup;
284 close c_get_federal_jit;
285 ln_403b := pay_ff_functions.get_pqp_limit(
286 p_effective_date => p_effective_date,
287 p_limit => 'ELECTIVE_DEFERRAL_LIMIT');
288 ln_403_catchup := pay_ff_functions.get_pqp_limit (
289 p_effective_date => p_effective_date,
290 p_limit => 'GENERAL_CATCHUP_LIMIT');
291 ln_457 := pay_ff_functions.get_457_annual_limit(
292 p_effective_date => p_effective_date,
293 p_limit => '457 LIMIT');
294 ln_457_catchup := pay_ff_functions.get_457_annual_limit(
295 p_effective_date => p_effective_date,
296 p_limit => '457 ADDITIONAL CATCHUP');
297 ln_dcp_limit := pqp_us_srs_extracts.get_dcp_limit(p_effective_date);
298
299 pay_us_payroll_utils.ltr_fed_tax_info(1).futa_wage := ln_futa_wage;
300 pay_us_payroll_utils.ltr_fed_tax_info(1).futa_rate := ln_futa_rate;
301 pay_us_payroll_utils.ltr_fed_tax_info(1).ss_ee_wage := ln_ss_ee_wage;
302 pay_us_payroll_utils.ltr_fed_tax_info(1).ss_ee_rate := ln_ss_ee_rate;
303 pay_us_payroll_utils.ltr_fed_tax_info(1).ss_er_wage := ln_ss_er_wage;
304 pay_us_payroll_utils.ltr_fed_tax_info(1).ss_er_rate := ln_ss_er_rate;
305 pay_us_payroll_utils.ltr_fed_tax_info(1).med_ee_rate := ln_medi_ee_rate;
306 pay_us_payroll_utils.ltr_fed_tax_info(1).med_er_rate := ln_medi_er_rate;
307 pay_us_payroll_utils.ltr_fed_tax_info(1).p401_limit := ln_401k;
308 pay_us_payroll_utils.ltr_fed_tax_info(1).p403_limit := ln_403b;
309 pay_us_payroll_utils.ltr_fed_tax_info(1).p457_limit := ln_457;
310 pay_us_payroll_utils.ltr_fed_tax_info(1).catchup_401k := ln_401k_catchup;
311 pay_us_payroll_utils.ltr_fed_tax_info(1).catchup_403b := ln_403_catchup;
312 pay_us_payroll_utils.ltr_fed_tax_info(1).catchup_457 := ln_457_catchup;
313 pay_us_payroll_utils.ltr_fed_tax_info(1).dcp_limit := ln_dcp_limit;
314 end if;
315
316 /***************************************************************
317 ** Build a PL/SQL table which has state tax info for all states
318 ***************************************************************/
319 hr_utility.set_location(gv_package || lv_procedure_name, 300);
320 ln_step := 10;
321 hr_utility.set_location(p_get_state,310);
322 hr_utility.set_location(to_char( pay_us_payroll_utils.ltr_state_tax_info.count),320);
323 if p_get_state = 'Y' and
324 pay_us_payroll_utils.ltr_state_tax_info.count < 1 then
325 open c_get_states_jit(p_effective_date);
326 loop
327 fetch c_get_states_jit into lv_state_code, lv_sit_exists,
328 ln_sui_ee_limit, ln_sui_er_limit ,
329 ln_sdi_ee_limit, ln_sdi_er_limit,
330 lv_steic_exists, ln_state_futa_rate ;
331 if c_get_states_jit%notfound then
332 hr_utility.set_location(gv_package || lv_procedure_name, 310);
333 exit;
334 end if;
335 hr_utility.set_location(gv_package || lv_procedure_name, 320);
336 hr_utility.trace('lv_state_code = ' || lv_state_code);
337 hr_utility.trace('lv_sit_exists = ' || lv_sit_exists);
338 hr_utility.trace('ln_sui_ee_limit = ' || ln_sui_ee_limit);
339 hr_utility.trace('ln_sui_er_limit = ' || ln_sui_er_limit);
340 hr_utility.trace('ln_sdi_ee_limit = ' || ln_sdi_ee_limit);
341 hr_utility.trace('ln_sdi_er_limit = ' || ln_sdi_er_limit);
342 hr_utility.trace('lv_steic_exists = ' || lv_steic_exists);
343 hr_utility.trace('ln_state_futa_rate= '|| ln_state_futa_rate);
344
345 pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sit_exists
346 := lv_sit_exists;
347 pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sui_ee_limit
348 := ln_sui_ee_limit;
349 pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sui_er_limit
350 := ln_sui_er_limit;
351 pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sdi_ee_limit
352 := ln_sdi_ee_limit;
353 pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sdi_er_limit
354 := ln_sdi_er_limit;
355 pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).steic_exists
356 := lv_steic_exists;
357 pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).futa_rate
358 := ln_state_futa_rate;
359
360 end loop;
361 close c_get_states_jit;
362 end if;
363 hr_utility.set_location(gv_package || lv_procedure_name, 350);
364
365 ln_step := 55;
366 if p_get_county = 'Y' and
367 pay_us_payroll_utils.ltr_county_tax_info.count < 1 then
368 open c_get_county_jit(p_effective_date);
369 loop
370 fetch c_get_county_jit into lv_jurisdiction_code,
371 lv_county_tax_exists,
372 lv_county_head_tax_exists,
373 lv_county_sd_tax_exists;
374 if c_get_county_jit%notfound then
375 hr_utility.set_location(gv_package || lv_procedure_name, 360);
376 exit;
377 end if;
378 hr_utility.set_location(gv_package || lv_procedure_name, 370);
379 hr_utility.trace('lv_jurisdiction_code = ' || lv_jurisdiction_code);
380
381 /****************** Start ****************************************************/
382 lv_temp_state_code := substr(lv_jurisdiction_code,1,2);
383 lv_county_code := substr(lv_jurisdiction_code,4,3);
384 lv_temp_code := lv_temp_state_code||lv_county_code;
385
386 ln_index := to_number(lv_temp_code);
387
388 /********************* End ****************************************************/
389
390 pay_us_payroll_utils.ltr_county_tax_info(ln_index).jurisdiction_code
391 := lv_jurisdiction_code;
392 pay_us_payroll_utils.ltr_county_tax_info(ln_index).cnty_tax_exists
393 := lv_county_tax_exists;
394 pay_us_payroll_utils.ltr_county_tax_info(ln_index).cnty_head_tax_exists
395 := lv_county_head_tax_exists;
396 pay_us_payroll_utils.ltr_county_tax_info(ln_index).cnty_sd_tax_exists
397 := lv_county_sd_tax_exists;
398
399 end loop;
400 close c_get_county_jit;
401 end if;
402
403 hr_utility.set_location(gv_package || lv_procedure_name, 400);
404 ln_step := 60;
405
406 /*********************** Bug Number:2973119 Changes Start *********************/
407
408 ln_step := 65;
409 if p_get_city = 'Y' then
410
411 ln_step := 70 ;
412 hr_utility.set_location(gv_package || lv_procedure_name, 450);
413
414 lv_temp_state_code := substr(p_jurisdiction_code,1,2);
415 lv_county_code := substr(p_jurisdiction_code,4,3);
416 lv_city_code := substr(p_jurisdiction_code,8,4);
417 lv_temp_code := lv_temp_state_code||lv_county_code||lv_city_code;
418 ln_index := to_number(lv_temp_code);
419
420 open c_get_city_jit(p_effective_date,p_jurisdiction_code);
421 fetch c_get_city_jit into lv_city_jurisdiction_code,
422 lv_city_tax_exists,
423 lv_city_head_tax_exists,
424 lv_city_sd_tax_exists;
425 if c_get_city_jit%notfound then
426 hr_utility.set_location(gv_package || lv_procedure_name, 460);
427 pay_us_payroll_utils.ltr_city_tax_info(ln_index).jurisdiction_code
428 := p_jurisdiction_code;
429 pay_us_payroll_utils.ltr_city_tax_info(ln_index).city_tax_exists
430 := NULL;
431 pay_us_payroll_utils.ltr_city_tax_info(ln_index).city_head_tax_exists
432 := NULL;
433 pay_us_payroll_utils.ltr_city_tax_info(ln_index).city_sd_tax_exists
434 := NULL;
435 else
436 hr_utility.set_location(gv_package || lv_procedure_name, 470);
437 hr_utility.trace('lv_jurisdiction_code = ' || lv_city_jurisdiction_code);
438
439 pay_us_payroll_utils.ltr_city_tax_info(ln_index).jurisdiction_code
440 := lv_city_jurisdiction_code;
441 pay_us_payroll_utils.ltr_city_tax_info(ln_index).city_tax_exists
442 := lv_city_tax_exists;
443 pay_us_payroll_utils.ltr_city_tax_info(ln_index).city_head_tax_exists
444 := lv_city_head_tax_exists;
445 pay_us_payroll_utils.ltr_city_tax_info(ln_index).city_sd_tax_exists
446 := lv_city_sd_tax_exists;
447 end if;
448
449 close c_get_city_jit;
450 end if;
451
452 hr_utility.set_location(gv_package || lv_procedure_name, 480);
453 ln_step := 75;
454
455 /*********************** Bug Number:2973119 Changes End **********************/
456
457 exception
458 when others then
459 hr_utility.set_location(gv_package || lv_procedure_name, 500);
460 lv_error_message := 'Error at step ' || ln_step ||
461 ' in ' || gv_package || lv_procedure_name;
462 hr_utility.trace(lv_error_message || '-' || sqlerrm);
463
464 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
465 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
466 hr_utility.raise_error;
467
468 END populate_jit_information;
469
470 /********************************************************************
471 ** Function : get_tax_exists
472 ** Arguments: p_jurisdiction_code
473 ** p_tax_type
474 ** Returns : Y/N
475 ** Purpose : This function has 2 parameters as input. The function
476 ** gets the effective_date from fnd_sessions. If the date
477 ** in fnd_sessions is not found, get the data as of sysdate.
478 *********************************************************************/
479 FUNCTION get_tax_exists (p_jurisdiction_code in varchar2
480 ,p_tax_type in varchar2)
481 RETURN varchar2
482 IS
483
484 cursor c_sessions is
485 select effective_date from fnd_sessions fs
486 where session_id = userenv('sessionid');
487
488 ld_effective_date DATE;
489
490 BEGIN
491 open c_sessions;
492 fetch c_sessions into ld_effective_date;
493 if c_sessions%notfound then
494 ld_effective_date := sysdate;
495 end if;
496 close c_sessions;
497
498 return (get_tax_exists (p_jurisdiction_code => p_jurisdiction_code
499 ,p_tax_type => p_tax_type
500 ,p_effective_date => ld_effective_date)
501 );
502 END get_tax_exists;
503
504 /********************************************************************
505 ** Function : get_tax_exists
506 ** Arguments: p_jurisdiction_code
507 ** p_tax_type
508 ** p_effective_date
509 ** Returns : Y/N
510 ** Purpose : This function has 3 parameters as input. The function
511 ** gets the data as of the effective_date passed to it.
512 *********************************************************************/
513 FUNCTION get_tax_exists(p_jurisdiction_code in varchar2
514 ,p_tax_type in varchar2
515 ,p_effective_date in date )
516 RETURN varchar2
517
518 IS
519
520 /*********************** Bug Number:2973119 Start ****************/
521
522 lv_county_code VARCHAR2(20);
523 lv_city_code VARCHAR2(20);
524 lv_temp_code VARCHAR2(20);
525 ln_index_code NUMBER;
526
527 /*********************** Bug Number:2973119 End ******************/
528
529 lv_state_code VARCHAR2(20);
530 lv_value VARCHAR2(20);
531 lv_return_value VARCHAR2(1);
532
533 BEGIN
534
535 hr_utility.trace('p_jurisdiction_code is : '|| p_jurisdiction_code);
536 hr_utility.trace('p_tax_type is : '|| p_tax_type);
537 hr_utility.trace('p_effective_date is : '|| p_effective_date);
538
539 lv_state_code := substr(p_jurisdiction_code,1,2);
540
541 /********************* Bug Number:2973119 Start *****************/
542
543 lv_county_code := substr(p_jurisdiction_code,4,3);
544 lv_city_code := substr(p_jurisdiction_code,8,4);
545
546 /********************* Bug Number:2973119 End *****************/
547
548 --federal
549 if p_jurisdiction_code = '00-000-0000' then
550
551 if pay_us_payroll_utils.ltr_fed_tax_info.count < 1 then
552 populate_jit_information( p_effective_date => p_effective_date
553 , p_get_federal => 'Y');
554 end if;
555
556 if p_tax_type = 'FUTA WAGE' then
557 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).futa_wage;
558 elsif p_tax_type = 'FUTA RATE' then
559 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).futa_rate;
560 elsif p_tax_type = 'SS EE' then
561 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).ss_ee_wage;
562 elsif p_tax_type = 'SS EE RATE' then
563 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).ss_ee_rate;
564 elsif p_tax_type = 'SS ER' then
565 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).ss_er_wage;
566 elsif p_tax_type = 'SS ER RATE' then
567 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).ss_er_rate;
568 elsif p_tax_type = 'MED EE RATE' then
569 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).med_ee_rate;
570 elsif p_tax_type = 'MED ER RATE' then
571 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).med_er_rate;
572 elsif p_tax_type = '401K' then
573 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).p401_limit;
574 elsif p_tax_type = '403B' then
575 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).p403_limit;
576 elsif p_tax_type = '457' then
577 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).p457_limit;
578 elsif p_tax_type = '401K CATCHUP' then
579 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).catchup_401k;
580 elsif p_tax_type = '403B CATCHUP' then
581 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).catchup_403b;
582 elsif p_tax_type = '457 CATCHUP' then
583 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).catchup_457;
584 elsif p_tax_type = 'DCP' then
585 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).dcp_limit;
586 end if;
587
588 --state
589 elsif lv_state_code <> '00' and
590 lv_county_code = '000' and
591 lv_city_code = '0000' then
592
593 if pay_us_payroll_utils.ltr_state_tax_info.count < 1 then
594 populate_jit_information(p_effective_date => p_effective_date
595 ,p_get_state => 'Y');
596 end if;
597
598 if p_tax_type = 'SUI EE' then
599 lv_value := pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sui_ee_limit;
600 elsif p_tax_type = 'SUI ER' then
601 lv_value := pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sui_er_limit;
602 hr_utility.set_location(lv_value,230);
603 elsif p_tax_type = 'SDI EE' then
604 lv_value := pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sdi_ee_limit;
605 elsif p_tax_type = 'SDI ER' then
606 lv_value := pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sdi_er_limit;
607 elsif p_tax_type = 'SIT EE' then
608 lv_value := pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sit_exists;
609 elsif p_tax_type = 'STEIC EE' then
610 lv_value := pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).steic_exists;
611 elsif p_tax_type = 'FUTA ER' then
612 lv_value := pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).futa_rate;
613 elsif p_tax_type = 'FUTA ' then
614 lv_value := 'Y'; -- Bug3969061
615 elsif p_tax_type in ('WC EE', 'WC2 EE') then
616 begin
617 select 'Y' into lv_value from dual
618 where exists (
619 select 'x'
620 from pay_wc_funds wcf, pay_us_states uss
621 where uss.state_code = lv_state_code
622 and uss.state_abbrev = wcf.state_code
623 and wcf.business_group_id =
624 nvl(hr_general.get_business_group_id, wcf.business_group_id));
625 exception
626 when no_data_found then
627 lv_value := 'N';
628 end;
629 end if;
630
631 /***************** Bug Number:2973119 Start **************************************/
632
633 --county
634 elsif lv_state_code <> '00' and
635 lv_county_code <> '000' and
636 lv_city_code = '0000' then
637
638 if pay_us_payroll_utils.ltr_county_tax_info.count<1 then
639 populate_jit_information(p_effective_date => p_effective_date
640 ,p_get_county => 'Y');
641 end if;
642
643 lv_temp_code := lv_state_code||lv_county_code;
644 ln_index_code := to_number(lv_temp_code);
645
646 if pay_us_payroll_utils.ltr_county_tax_info.exists(ln_index_code) then
647 lv_value := pay_us_payroll_utils.ltr_county_tax_info(ln_index_code).cnty_tax_exists;
648 end if;
649
650 --city
651 elsif lv_state_code <> '00' and
652 lv_county_code <> '000' and
653 lv_city_code <> '0000' then
654
655 if substr(lv_city_code,1,1) = 'U' then
656 /* for user defined cities we should return N because they are not
657 primary cities and will never have tax */
658 lv_value := 'N';
659 else
660 if pay_us_payroll_utils.ltr_city_tax_info.count < 1 then
661 populate_jit_information(p_effective_date => p_effective_date
662 ,p_get_city => 'Y'
663 ,p_jurisdiction_code => p_jurisdiction_code);
664 end if;
665
666 lv_temp_code := lv_state_code||lv_county_code||lv_city_code;
667 hr_utility.trace('lv_temp_code : '|| lv_temp_code);
668 hr_utility.trace(' B4 ln_index_code');
669 ln_index_code := to_number(lv_temp_code);
670 hr_utility.trace(' A4 ln_index_code');
671
672 if p_tax_type = 'CITY' then
673 if pay_us_payroll_utils.ltr_city_tax_info.exists(ln_index_code) then
674
675 hr_utility.trace(' CITY found in PLSQL table');
676 null;
677 else
678 hr_utility.trace(' CITY NOT found in PLSQL table');
679
680 populate_jit_information(p_effective_date => p_effective_date
681 ,p_get_city => 'Y'
682 ,p_jurisdiction_code => p_jurisdiction_code);
683 end if;
684 lv_value
685 := pay_us_payroll_utils.ltr_city_tax_info(ln_index_code).city_tax_exists;
686 end if;
687
688 if p_tax_type = 'HT' then
689 if pay_us_payroll_utils.ltr_city_tax_info.exists(ln_index_code) then
690 hr_utility.trace(' HT found in PLSQL table');
691 null;
692 else
693 hr_utility.trace(' HT NOT found in PLSQL table');
694 populate_jit_information(p_effective_date => p_effective_date
695 ,p_get_city => 'Y'
696 ,p_jurisdiction_code => p_jurisdiction_code);
697 end if;
698 lv_value := pay_us_payroll_utils.ltr_city_tax_info(ln_index_code).city_head_tax_exists;
699 end if;
700
701 /********************* Bug Number:2973119 End ****************************/
702
703 end if; /* substr(lv_city_code,1,1) = 'U' */
704
705 end if;
706
707 if lv_value = 'Y' then
708 lv_return_value := 'Y';
709 elsif nvl(lv_value,'0') = '0' or lv_value = 'N' then
710 lv_return_value := 'N';
711 elsif nvl(lv_value,'0') <> '0' then
712 lv_return_value := 'Y';
713 end if;
714
715 return(lv_return_value);
716 END get_tax_exists;
717
718
719 /*****************************************************************************
720 Name : check_balance_status
721 Purpose : Function should be used to identify whether the balances relevant
722 to partcular attribute are valid for use of BRA.
723 Arguments : 1. Start Date
724 2. Business Group Id
725 3. Atttribute Name
726 4. Legislation Code
727 Return : 'Y' for valid status and 'N' for invalid status of balance
728 Notes : It will used by group level reports (940,941,GRE Totals) to find
729 if all the balances related to a report are valid or not
730 *****************************************************************************/
731
732 FUNCTION check_balance_status(
733 p_start_date in date,
734 p_business_group_id in hr_organization_units.organization_id%type,
735 p_attribute_name in varchar2,
736 p_legislation_code in varchar2 default 'US')
737 RETURN VARCHAR2
738 IS
739
740 /*************************************************************
741 ** Cursor to check if the attribute_name passed as parameter
742 ** exists or not.
743 **************************************************************/
744 CURSOR c_attribute_exists(
745 c_attribute_name in pay_bal_attribute_definitions.attribute_name%type)
746 is
747 select 1
748 from pay_bal_attribute_definitions
749 where attribute_name = c_attribute_name
750 and legislation_code = p_legislation_code;
751
752 CURSOR c_get_valid_count(cp_start_date in date,
753 cp_business_group_id in per_business_groups.business_group_id%type,
754 cp_attribute_name in varchar2) IS
755 select /*+ ORDERED */ count(*)
756 from pay_bal_attribute_definitions pbad,
757 pay_balance_attributes pba,
758 pay_balance_validation pbv
759 where pbad.attribute_name = cp_attribute_name
760 and pbad.attribute_id = pba.attribute_id
761 and (pba.business_group_id = cp_business_group_id
762 OR
763 pba.legislation_code = p_legislation_code)
764 and pba.defined_balance_id = pbv.defined_balance_id
765 and pbv.business_group_id = cp_business_group_id
766 and NVL(pbv.balance_load_date, cp_start_date) <= cp_start_date
767 and nvl(pbv.run_balance_status, 'I') = 'V';
768
769 CURSOR c_get_attribute_count(
770 cp_business_group_id in per_business_groups.business_group_id%type,
771 cp_attribute_name in varchar2) IS
772
773 select count(*)
774 from pay_bal_attribute_definitions pbad,
775 pay_balance_attributes pba
776 where pbad.attribute_name = cp_attribute_name
777 and pbad.attribute_id = pba.attribute_id
778 and (pba.business_group_id = cp_business_group_id
779 OR
780 pba.legislation_code = p_legislation_code );
781
782 ln_attribute_exists NUMBER(1);
783 ln_valid_bal_exists NUMBER(1);
784 lv_return_status VARCHAR2(1) := 'N';
785 lv_package_stage VARCHAR2(50) := 'pay_us_payroll_utils.check_balance_status';
786
787 l_attribute_count number;
788 l_valid_count number;
789 l_trunc_date date; /* Bug 3258868 */
790
791 BEGIN
792 hr_utility.trace('Start of Procedure '||lv_package_stage);
793 hr_utility.set_location(lv_package_stage,10);
794
795 l_trunc_date := trunc(p_start_date,'Y'); -- Bug 3258868
796
797 -- Validate if the attribute passed as parameter exists
798 open c_attribute_exists(p_attribute_name);
799 fetch c_attribute_exists INTO ln_attribute_exists;
800 if c_attribute_exists%notfound then
801 hr_utility.set_location(lv_package_stage,20);
802 lv_return_status := 'N';
803 hr_utility.trace('Invalid Attribute Name');
804 raise_application_error(-20101, 'Error in pay_us_.check_balance_status');
805 end if;
806 close c_attribute_exists ;
807
808 hr_utility.set_location(lv_package_stage,30);
809
810 open c_get_valid_count(l_trunc_date, -- Bug 3258868
811 p_business_group_id,
812 p_attribute_name );
813 fetch c_get_valid_count into l_valid_count;
814 close c_get_valid_count;
815
816 hr_utility.trace('Valid Count for '||p_attribute_name||' is '||to_char(l_valid_count));
817
818 /* Do following check only if the attribute count > zero */
819
820 IF l_valid_count > 0 THEN
821
822 open c_get_attribute_count(
823 p_business_group_id,
824 p_attribute_name );
825 fetch c_get_attribute_count into l_attribute_count;
826 close c_get_attribute_count;
827
828 hr_utility.trace('Attribute Count for '||p_attribute_name||' is '||to_char(l_attribute_count));
829
830 if l_valid_count = l_attribute_count then
831
832 hr_utility.set_location(lv_package_stage,40);
833 lv_return_status := 'Y';
834 else
835
836 -- Bug 3312482 Push statements are deleted.
837 hr_utility.set_location(lv_package_stage,50);
838
839 -- Bug 3340952 Push statements are added again.
840 pay_core_utils.push_message(801,'PAY_EXCEPTION','A');
841 pay_core_utils.push_token('description','Warning Invalid Balance Status . ,In Attribute -> ' ||p_attribute_name);
842
843 hr_utility.trace('Balance Status is Invalid for Attribute -> ' ||p_attribute_name);
844
845 lv_return_status := 'N';
846 end if;
847 end if; /* IF l_valid_count > 0 */
848 hr_utility.trace('End of Procedure ' || lv_package_stage);
849 return(lv_return_status);
850
851
852 EXCEPTION
853 WHEN others THEN
854 hr_utility.set_location(lv_package_stage,60);
855 hr_utility.trace('Invalid Attribute Name');
856 raise_application_error(-20101, 'Error in pay_us_.check_balance_status');
857 raise;
858 END check_balance_status;
859
860 /************************************************************
861 ** Function : formated_header_string
862 ** Arguments: p_input_string
863 ** p_output_file_type
864 ** Returns : input string with the HTML Header tags
865 ** Purpose : This Function will be used by the reports that are
866 ** displaying in HTML format. It returns the input
867 ** string with the HTML Header tags
868 ************************************************************/
869
870 FUNCTION formated_header_string
871 (p_input_string in VARCHAR2
872 ,p_output_file_type in VARCHAR2
873 )
874 RETURN VARCHAR2
875 IS
876
877 lv_format VARCHAR2(1000);
878
879 BEGIN
880 hr_utility.set_location(gv_package || '.formated_header_string', 10);
881 IF p_output_file_type = 'CSV' THEN
882 hr_utility.set_location(gv_package || '.formated_header_string', 20);
883 lv_format := p_input_string;
884 ELSIF p_output_file_type = 'HTML' THEN
885 hr_utility.set_location(gv_package || '.formated_header_string', 30);
886 lv_format := '<HTML><HEAD> <CENTER> <H1> <B>' || p_input_string ||
887 '</B></H1></CENTER></HEAD>';
888 END IF;
889
890 hr_utility.set_location(gv_package || '.formated_header_string', 40);
891 return lv_format;
892
893 END formated_header_string;
894
895
896 /************************************************************
897 ** Function : formated_data_string
898 ** Arguments: p_input_string
899 ** p_output_file_type
900 ** p_bold
901 ** Returns : the formated input string based on the Output
902 ** format. If the format is CSV then the values are
903 ** returned seperated by comma (,). If the format is
904 ** HTML then the returned string has the HTML tags.
905 ** The parameter p_bold only works for the HTML
906 ** format.
907 ** Purpose : This Function will be used by reports that are
908 ** displaying in HTML/CSV format.
909 ************************************************************/
910 FUNCTION formated_data_string
911 (p_input_string in VARCHAR2
912 ,p_output_file_type in VARCHAR2
913 ,p_bold in VARCHAR2
914 )
915 RETURN VARCHAR2
916 IS
917
918 lv_format VARCHAR2(1000);
919 lv_bold VARCHAR2(10);
920 BEGIN
921 lv_bold := nvl(p_bold,'N');
922 hr_utility.set_location(gv_package || '.formated_data_string', 10);
923 IF p_output_file_type = 'CSV' THEN
924 hr_utility.set_location(gv_package || '.formated_data_string', 20);
925 lv_format := gc_csv_data_delimiter || p_input_string ||
926 gc_csv_data_delimiter || gc_csv_delimiter;
927 ELSIF p_output_file_type = 'HTML' THEN
928 IF p_input_string is null THEN
929 hr_utility.set_location(gv_package || '.formated_data_string', 30);
930 lv_format := gv_html_start_data || ' ' || gv_html_end_data; -- Bug 3179050
931 ELSE
932 IF lv_bold = 'Y' THEN
933 hr_utility.set_location(gv_package || '.formated_data_string', 40);
934 lv_format := gv_html_start_data || '<b> ' || p_input_string
935 || '</b>' || gv_html_end_data;
936 ELSE
937 hr_utility.set_location(gv_package || '.formated_data_string', 50);
938 lv_format := gv_html_start_data || p_input_string || gv_html_end_data;
939 END IF;
940 END IF;
941 END IF;
942
943 hr_utility.set_location(gv_package || '.formated_data_string', 60);
944 return lv_format;
945
946 END formated_data_string;
947
948
949 /**************************************************************************
950 ** Function : get_parameter
951 ** Arguments: p_param_name
952 ** p_parameter_list
953 ** Returns : the the value for the parameter p_param_name
954 ** from the p_parameter_list
955 ** This function is called to get the value entered
956 ** by the user which is stored in legislative
957 ** parameters. Both the name and list is passed to
958 ** the function.
959 **************************************************************************/
960 FUNCTION get_parameter(p_parameter_name in varchar2,
961 p_parameter_list in varchar2)
962 RETURN VARCHAR2
963 IS
964 ln_start_ptr NUMBER;
965 ln_end_ptr NUMBER;
966
967 lv_token_value pay_payroll_actions.legislative_parameters%type;
968 lv_par_value pay_payroll_actions.legislative_parameters%type;
969 BEGIN
970
971 --4518409 lv_token_value := ' ' || p_parameter_name||'=';
972
973 if substr(p_parameter_list,1,length(p_parameter_name)) <> p_parameter_name then
974 lv_token_value := ' ' || p_parameter_name||'=';
975 else
976 lv_token_value := p_parameter_name||'=';
977 end if;
978
979 ln_start_ptr := instr(p_parameter_list, lv_token_value) + length(lv_token_value);
980 ln_end_ptr := instr(p_parameter_list, ' ', ln_start_ptr);
981
982 /* if there is no spaces use then length of the string */
983 if ln_end_ptr = 0 then
984 ln_end_ptr := length(p_parameter_list)+1;
985 end if;
986
987 /* Did we find the token */
988 if instr(p_parameter_list, lv_token_value) = 0 then
989 lv_par_value := NULL;
990 else
991 lv_par_value := substr(p_parameter_list, ln_start_ptr, ln_end_ptr - ln_start_ptr);
992 end if;
993
994 return lv_par_value;
995
996 END get_parameter;
997
998
999
1000
1001 /**************************************************************************
1002 ** Function : ssn_reporting_preferences
1003 ** Arguments: p_loc_id location Id,
1004 p_org_id organization Id,
1005 p_bg_id business group Id
1006 ** Returns : The value for the parameter lv_display_ssn
1007 ** This function is called is called from Check Writer,Deposit Advice
1008 ** and Archive Check WRiter and Deposit Advice Reports.It is supposed to
1009 ** return if we want to show SSN on the output of these reoprts or not.
1010 ** The Function checks the value set by the user at location then organization and
1011 ** finally at BG level.It was added for Bug 3892148.
1012 **************************************************************************/
1013 FUNCTION ssn_reporting_preferences(p_loc_id in number,
1014 p_org_id in number,
1015 p_bg_id in number)
1016 RETURN VARCHAR2
1017 IS
1018 lv_display_ssn varchar2(100);
1019
1020 cursor c_loc_pref(cp_location_id in number) is
1021 select lei_information1
1022 from hr_location_extra_info hlei
1023 where hlei.location_id = cp_location_id
1024 and information_type = 'US_LOC_REP_PREFERENCES';
1025
1026 cursor c_org_pref(cp_organization_id in number
1027 ,cp_org_information_context in varchar2) is
1028 select org_information1
1029 from hr_organization_information hoi
1030 where organization_id = cp_organization_id
1031 and org_information_context = cp_org_information_context;
1032
1033 BEGIN
1034 open c_loc_pref(p_loc_id);
1035 fetch c_loc_pref into lv_display_ssn;
1036 if c_loc_pref%notfound or lv_display_ssn is null then
1037 open c_org_pref(p_org_id, 'US_ORG_REP_PREFERENCES');
1038 fetch c_org_pref into lv_display_ssn;
1039 if c_org_pref%notfound or lv_display_ssn is null then
1040 close c_org_pref;
1041
1042 open c_org_pref(p_org_id, 'US_BG_REP_PREFERENCES');
1043 fetch c_org_pref into lv_display_ssn;
1044 if c_org_pref%notfound or lv_display_ssn is null then
1045 lv_display_ssn := 'Y';
1046 end if;
1047 end if;
1048 close c_org_pref;
1049 end if;
1050 close c_loc_pref;
1051
1052 if nvl(lv_display_ssn,'Y') = 'Y' then
1053 return 'Y';
1054 else
1055 return 'N';
1056 end if;
1057
1058 END ssn_reporting_preferences;
1059
1060
1061 FUNCTION get_min_action(p_business_group_id in number
1062 ,p_start_date in date
1063 ,p_end_date in date
1064 ,p_tax_unit_id in number default null
1065 ,p_payroll_id in number default null)
1066 RETURN NUMBER
1067 IS
1068 cursor c_get_min_action(cp_business_group_id in number
1069 ,cp_start_date in date
1070 ,cp_end_date in date
1071 ,cp_tax_unit_id in number
1072 ,cp_payroll_id in number) is
1073 select nvl(min(assignment_action_id),-1)
1074 from pay_assignment_actions paa,
1075 pay_payroll_actions ppa,
1076 pay_payrolls_f ppf
1077 where ppa.business_group_id +0 = cp_business_group_id
1078 and ppa.payroll_action_id = paa.payroll_action_id
1079 and ppa.effective_date between cp_start_date and cp_end_date
1080 and ppa.action_type in ('R','Q','I','B','V')
1081 and ppf.payroll_id = ppa.payroll_id
1082 and ppa.business_group_id +0 = ppf.business_group_id
1083 and paa.tax_unit_id = nvl(cp_tax_unit_id, paa.tax_unit_id)
1084 and ppf.payroll_id = nvl(cp_payroll_id, ppf.payroll_id);
1085
1086 ln_min_action NUMBER;
1087
1088 BEGIN
1089 open c_get_min_action(p_business_group_id
1090 ,p_start_date
1091 ,p_end_date
1092 ,p_tax_unit_id
1093 ,p_payroll_id);
1094 fetch c_get_min_action into ln_min_action;
1095 close c_get_min_action;
1096
1097 pay_us_balance_view_pkg.set_session_var('GRP_AAID',to_char(ln_min_action));
1098
1099 return(ln_min_action);
1100
1101 END get_min_action;
1102
1103 END pay_us_payroll_utils;
[Home] [Help]