1 PACKAGE BODY pay_us_payroll_utils AS
2 /* $Header: pyusutil.pkb 120.2.12020000.2 2012/07/05 03:56:05 amnaraya ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_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 03-MAR-2009 tclewis 115.35 Added SDI1 EE to populate_jit_information
130 and get_tax_exists.
131 29-FEB-2012 ybudamal 115.36 13715397 Added procedure update_state_or_local_wh_link
132 to update the state W4 withholding link and local
133 tax form link for the State if local link exists.
134 Otherwise creates a new local tax form link.
135 *****************************************************************************/
136
137 /*****************************************************************************
138 ** Package Local Variables
139 *****************************************************************************/
140 gv_package VARCHAR2(100) := 'pay_us_payroll_utils';
141 gc_csv_delimiter VARCHAR2(1) := ',';
142 gc_csv_data_delimiter VARCHAR2(1) := '"';
143
144 gv_html_start_data VARCHAR2(5) := '<td>' ;
145 gv_html_end_data VARCHAR2(5) := '</td>' ;
146
147
148 /*****************************************************************************
149 Name : populate_jit_information
150 Purpose : This procedure populates a PL/SQL table with JIT information
151 Arguments :
152 Notes :
153 *****************************************************************************/
154 PROCEDURE populate_jit_information(
155 p_effective_date in date default sysdate
156 ,p_get_federal in varchar2 default 'N'
157 ,p_get_state in varchar2 default 'N'
158 ,p_get_county in varchar2 default 'N'
159 ,p_get_city in varchar2 default 'N'
160 ,p_jurisdiction_code in varchar2 default NULL) is
161
162 lv_state_code VARCHAR2(2);
163 lv_sit_exists VARCHAR2(1);
164 ln_sdi_ee_limit NUMBER;
165 ln_sdi1_ee_limit NUMBER;
166 ln_sdi_er_limit NUMBER;
167 ln_sui_ee_limit NUMBER;
168 ln_sui_er_limit NUMBER;
169 lv_steic_exists VARCHAR2(1);
170 ln_state_futa_rate NUMBER;
171
172 lv_jurisdiction_code VARCHAR2(11);
173 lv_county_tax_exists VARCHAR2(1);
174 lv_county_sd_tax_exists VARCHAR2(1);
175 lv_county_head_tax_exists VARCHAR2(1);
176
177 lv_temp_state_code VARCHAR2(3);
178 lv_county_code VARCHAR2(4);
179 lv_city_code VARCHAR2(5);
180 lv_temp_code VARCHAR2(11);
181 ln_index NUMBER;
182
183 lv_city_jurisdiction_code VARCHAR2(11);
184 lv_city_tax_exists VARCHAR2(1);
185 lv_city_head_tax_exists VARCHAR2(1);
186 lv_city_sd_tax_exists VARCHAR2(1);
187
188 ln_fed_count NUMBER := 0;
189 ln_state_count NUMBER := 0;
190 ln_county_count NUMBER := 0;
191 ln_city_count NUMBER := 0;
192 ln_schdist_count NUMBER := 0;
193
194 ln_futa_wage NUMBER;
195 ln_futa_rate NUMBER;
196 ln_ss_ee_wage NUMBER;
197 ln_ss_ee_rate NUMBER;
198 ln_ss_er_wage NUMBER;
199 ln_ss_er_rate NUMBER;
200 ln_medi_ee_rate NUMBER;
201 ln_medi_er_rate NUMBER;
202 ln_401k NUMBER;
203 ln_403b NUMBER;
204 ln_457 NUMBER;
205 ln_401k_catchup NUMBER;
206 ln_403_catchup NUMBER;
207 ln_457_catchup NUMBER;
208 ln_dcp_limit NUMBER;
209
210 lv_error_message VARCHAR2(500);
211 lv_procedure_name VARCHAR2(100) := '.populate_jit_information';
212 ln_step NUMBER;
213
214 cursor c_get_federal_jit (cp_effective_date in date
215 ,cp_fed_info_category in varchar2) is
216 select futa_wage_limit, futa_rate,
217 ss_ee_wage_limit, ss_ee_rate,
218 ss_er_wage_limit, ss_er_rate,
219 medi_ee_rate, medi_er_rate,
220 fed_information1, fed_information2
221 from pay_us_federal_tax_info_f
222 where cp_effective_date between effective_start_date
223 and effective_end_date
224 and fed_information_category = cp_fed_info_category;
225
226 /* Rosie monge chaning the cursor to fix bug 3358113 */
227 /* Added decode statement to sdi_ee_wage_limit */
228 cursor c_get_states_jit (cp_effective_date in date) is
229 select state_code,
230 sit_exists,
231 sui_ee_wage_limit,
232 sui_er_wage_limit,
233 decode(sdi_ee_wage_limit,
234 NULL, STA_INFORMATION1,
235 0, STA_INFORMATION1,
236 sdi_ee_wage_limit) sdi_ee_wage_limit,
237 sdi_er_wage_limit,
238 nvl(sta_information17,'N'),
239 sta_information19 futa_rate,
240 sta_information21 sdi1_ee_wage_limit
241 from pay_us_state_tax_info_f
242 where cp_effective_date between effective_start_date
243 and effective_end_date
244 and sta_information_category = 'State tax limit rate info'
245 order by 1 ;
246
247 cursor c_get_county_jit (cp_effective_date in date) is
248 select jurisdiction_code,
249 county_tax,
250 head_tax,
251 school_tax
252 from pay_us_county_tax_info_f
253 where cp_effective_date between effective_start_date
254 and effective_end_date
255 and cnty_information_category = 'County tax status info'
256 order by 1 ;
257
258 /*******************************************************************
259 ** Cursor to populate ltr_city_info_tax pl/sql table **
260 ** Bug Number: 2973119 Changes start **
261 ********************************************************************/
262
263 cursor c_get_city_jit ( cp_effective_date in date
264 , cp_jurisdiction_code in varchar2) is
265 select jurisdiction_code,
266 city_tax,
267 head_tax,
268 school_tax
269 from pay_us_city_tax_info_f
270 where cp_effective_date between effective_start_date
271 and effective_end_date
272 and jurisdiction_code = cp_jurisdiction_code
273 and city_information_category = 'City tax status info';
274
275 /********** Bug Number:2973119 End ***************************/
276
277 BEGIN
278 ln_step := 1;
279 hr_utility.set_location(gv_package || lv_procedure_name, 1);
280 /***************************************************************
281 ** Build a PL/SQL table which has federal tax info
282 ***************************************************************/
283 if p_get_federal = 'Y' and
284 pay_us_payroll_utils.ltr_fed_tax_info.count < 1 then
285 ln_step := 5;
286 open c_get_federal_jit (p_effective_date, '401K LIMITS');
287 fetch c_get_federal_jit into ln_futa_wage, ln_futa_rate,
288 ln_ss_ee_wage, ln_ss_ee_rate,
289 ln_ss_er_wage, ln_ss_er_rate,
290 ln_medi_ee_rate, ln_medi_er_rate,
291 ln_401k, ln_401k_catchup;
292 close c_get_federal_jit;
293 ln_403b := pay_ff_functions.get_pqp_limit(
294 p_effective_date => p_effective_date,
295 p_limit => 'ELECTIVE_DEFERRAL_LIMIT');
296 ln_403_catchup := pay_ff_functions.get_pqp_limit (
297 p_effective_date => p_effective_date,
298 p_limit => 'GENERAL_CATCHUP_LIMIT');
299 ln_457 := pay_ff_functions.get_457_annual_limit(
300 p_effective_date => p_effective_date,
301 p_limit => '457 LIMIT');
302 ln_457_catchup := pay_ff_functions.get_457_annual_limit(
303 p_effective_date => p_effective_date,
304 p_limit => '457 ADDITIONAL CATCHUP');
305 ln_dcp_limit := pqp_us_srs_extracts.get_dcp_limit(p_effective_date);
306
307 pay_us_payroll_utils.ltr_fed_tax_info(1).futa_wage := ln_futa_wage;
308 pay_us_payroll_utils.ltr_fed_tax_info(1).futa_rate := ln_futa_rate;
309 pay_us_payroll_utils.ltr_fed_tax_info(1).ss_ee_wage := ln_ss_ee_wage;
310 pay_us_payroll_utils.ltr_fed_tax_info(1).ss_ee_rate := ln_ss_ee_rate;
311 pay_us_payroll_utils.ltr_fed_tax_info(1).ss_er_wage := ln_ss_er_wage;
312 pay_us_payroll_utils.ltr_fed_tax_info(1).ss_er_rate := ln_ss_er_rate;
313 pay_us_payroll_utils.ltr_fed_tax_info(1).med_ee_rate := ln_medi_ee_rate;
314 pay_us_payroll_utils.ltr_fed_tax_info(1).med_er_rate := ln_medi_er_rate;
315 pay_us_payroll_utils.ltr_fed_tax_info(1).p401_limit := ln_401k;
316 pay_us_payroll_utils.ltr_fed_tax_info(1).p403_limit := ln_403b;
317 pay_us_payroll_utils.ltr_fed_tax_info(1).p457_limit := ln_457;
318 pay_us_payroll_utils.ltr_fed_tax_info(1).catchup_401k := ln_401k_catchup;
319 pay_us_payroll_utils.ltr_fed_tax_info(1).catchup_403b := ln_403_catchup;
320 pay_us_payroll_utils.ltr_fed_tax_info(1).catchup_457 := ln_457_catchup;
321 pay_us_payroll_utils.ltr_fed_tax_info(1).dcp_limit := ln_dcp_limit;
322 end if;
323
324 /***************************************************************
325 ** Build a PL/SQL table which has state tax info for all states
326 ***************************************************************/
327 hr_utility.set_location(gv_package || lv_procedure_name, 300);
328 ln_step := 10;
329 hr_utility.set_location(p_get_state,310);
330 hr_utility.set_location(to_char( pay_us_payroll_utils.ltr_state_tax_info.count),320);
331 if p_get_state = 'Y' and
332 pay_us_payroll_utils.ltr_state_tax_info.count < 1 then
333 open c_get_states_jit(p_effective_date);
334 loop
335 fetch c_get_states_jit into lv_state_code, lv_sit_exists,
336 ln_sui_ee_limit, ln_sui_er_limit ,
337 ln_sdi_ee_limit, ln_sdi_er_limit,
338 lv_steic_exists, ln_state_futa_rate,
339 ln_sdi1_ee_limit;
340 if c_get_states_jit%notfound then
341 hr_utility.set_location(gv_package || lv_procedure_name, 310);
342 exit;
343 end if;
344 hr_utility.set_location(gv_package || lv_procedure_name, 320);
345 hr_utility.trace('lv_state_code = ' || lv_state_code);
346 hr_utility.trace('lv_sit_exists = ' || lv_sit_exists);
347 hr_utility.trace('ln_sui_ee_limit = ' || ln_sui_ee_limit);
348 hr_utility.trace('ln_sui_er_limit = ' || ln_sui_er_limit);
349 hr_utility.trace('ln_sdi_ee_limit = ' || ln_sdi_ee_limit);
350 hr_utility.trace('ln_sdi1_ee_limit = ' || ln_sdi1_ee_limit);
351 hr_utility.trace('ln_sdi_er_limit = ' || ln_sdi_er_limit);
352 hr_utility.trace('lv_steic_exists = ' || lv_steic_exists);
353 hr_utility.trace('ln_state_futa_rate= '|| ln_state_futa_rate);
354
355 pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sit_exists
356 := lv_sit_exists;
357 pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sui_ee_limit
358 := ln_sui_ee_limit;
359 pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sui_er_limit
360 := ln_sui_er_limit;
361 pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sdi_ee_limit
362 := ln_sdi_ee_limit;
363 pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sdi_er_limit
364 := ln_sdi_er_limit;
365 pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).steic_exists
366 := lv_steic_exists;
367 pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).futa_rate
368 := ln_state_futa_rate;
369 pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sdi1_ee_limit
370 := ln_sdi1_ee_limit;
371
372 end loop;
373 close c_get_states_jit;
374 end if;
375 hr_utility.set_location(gv_package || lv_procedure_name, 350);
376
377 ln_step := 55;
378 if p_get_county = 'Y' and
379 pay_us_payroll_utils.ltr_county_tax_info.count < 1 then
380 open c_get_county_jit(p_effective_date);
381 loop
382 fetch c_get_county_jit into lv_jurisdiction_code,
383 lv_county_tax_exists,
384 lv_county_head_tax_exists,
385 lv_county_sd_tax_exists;
386 if c_get_county_jit%notfound then
387 hr_utility.set_location(gv_package || lv_procedure_name, 360);
388 exit;
389 end if;
390 hr_utility.set_location(gv_package || lv_procedure_name, 370);
391 hr_utility.trace('lv_jurisdiction_code = ' || lv_jurisdiction_code);
392
393 /****************** Start ****************************************************/
394 lv_temp_state_code := substr(lv_jurisdiction_code,1,2);
395 lv_county_code := substr(lv_jurisdiction_code,4,3);
396 lv_temp_code := lv_temp_state_code||lv_county_code;
397
398 ln_index := to_number(lv_temp_code);
399
400 /********************* End ****************************************************/
401
402 pay_us_payroll_utils.ltr_county_tax_info(ln_index).jurisdiction_code
403 := lv_jurisdiction_code;
404 pay_us_payroll_utils.ltr_county_tax_info(ln_index).cnty_tax_exists
405 := lv_county_tax_exists;
406 pay_us_payroll_utils.ltr_county_tax_info(ln_index).cnty_head_tax_exists
407 := lv_county_head_tax_exists;
408 pay_us_payroll_utils.ltr_county_tax_info(ln_index).cnty_sd_tax_exists
409 := lv_county_sd_tax_exists;
410
411 end loop;
412 close c_get_county_jit;
413 end if;
414
415 hr_utility.set_location(gv_package || lv_procedure_name, 400);
416 ln_step := 60;
417
418 /*********************** Bug Number:2973119 Changes Start *********************/
419
420 ln_step := 65;
421 if p_get_city = 'Y' then
422
423 ln_step := 70 ;
424 hr_utility.set_location(gv_package || lv_procedure_name, 450);
425
426 lv_temp_state_code := substr(p_jurisdiction_code,1,2);
427 lv_county_code := substr(p_jurisdiction_code,4,3);
428 lv_city_code := substr(p_jurisdiction_code,8,4);
429 lv_temp_code := lv_temp_state_code||lv_county_code||lv_city_code;
430 ln_index := to_number(lv_temp_code);
431
432 open c_get_city_jit(p_effective_date,p_jurisdiction_code);
433 fetch c_get_city_jit into lv_city_jurisdiction_code,
434 lv_city_tax_exists,
435 lv_city_head_tax_exists,
436 lv_city_sd_tax_exists;
437 if c_get_city_jit%notfound then
438 hr_utility.set_location(gv_package || lv_procedure_name, 460);
439 pay_us_payroll_utils.ltr_city_tax_info(ln_index).jurisdiction_code
440 := p_jurisdiction_code;
441 pay_us_payroll_utils.ltr_city_tax_info(ln_index).city_tax_exists
442 := NULL;
443 pay_us_payroll_utils.ltr_city_tax_info(ln_index).city_head_tax_exists
444 := NULL;
445 pay_us_payroll_utils.ltr_city_tax_info(ln_index).city_sd_tax_exists
446 := NULL;
447 else
448 hr_utility.set_location(gv_package || lv_procedure_name, 470);
449 hr_utility.trace('lv_jurisdiction_code = ' || lv_city_jurisdiction_code);
450
451 pay_us_payroll_utils.ltr_city_tax_info(ln_index).jurisdiction_code
452 := lv_city_jurisdiction_code;
453 pay_us_payroll_utils.ltr_city_tax_info(ln_index).city_tax_exists
454 := lv_city_tax_exists;
455 pay_us_payroll_utils.ltr_city_tax_info(ln_index).city_head_tax_exists
456 := lv_city_head_tax_exists;
457 pay_us_payroll_utils.ltr_city_tax_info(ln_index).city_sd_tax_exists
458 := lv_city_sd_tax_exists;
459 end if;
460
461 close c_get_city_jit;
462 end if;
463
464 hr_utility.set_location(gv_package || lv_procedure_name, 480);
465 ln_step := 75;
466
467 /*********************** Bug Number:2973119 Changes End **********************/
468
469 exception
470 when others then
471 hr_utility.set_location(gv_package || lv_procedure_name, 500);
472 lv_error_message := 'Error at step ' || ln_step ||
473 ' in ' || gv_package || lv_procedure_name;
474 hr_utility.trace(lv_error_message || '-' || sqlerrm);
475
476 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
477 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
478 hr_utility.raise_error;
479
480 END populate_jit_information;
481
482 /********************************************************************
483 ** Function : get_tax_exists
484 ** Arguments: p_jurisdiction_code
485 ** p_tax_type
486 ** Returns : Y/N
487 ** Purpose : This function has 2 parameters as input. The function
488 ** gets the effective_date from fnd_sessions. If the date
489 ** in fnd_sessions is not found, get the data as of sysdate.
490 *********************************************************************/
491 FUNCTION get_tax_exists (p_jurisdiction_code in varchar2
492 ,p_tax_type in varchar2)
493 RETURN varchar2
494 IS
495
496 cursor c_sessions is
497 select effective_date from fnd_sessions fs
498 where session_id = userenv('sessionid');
499
500 ld_effective_date DATE;
501
502 BEGIN
503 open c_sessions;
504 fetch c_sessions into ld_effective_date;
505 if c_sessions%notfound then
506 ld_effective_date := sysdate;
507 end if;
508 close c_sessions;
509
510 return (get_tax_exists (p_jurisdiction_code => p_jurisdiction_code
511 ,p_tax_type => p_tax_type
512 ,p_effective_date => ld_effective_date)
513 );
514 END get_tax_exists;
515
516 /********************************************************************
517 ** Function : get_tax_exists
518 ** Arguments: p_jurisdiction_code
519 ** p_tax_type
520 ** p_effective_date
521 ** Returns : Y/N
522 ** Purpose : This function has 3 parameters as input. The function
523 ** gets the data as of the effective_date passed to it.
524 *********************************************************************/
525 FUNCTION get_tax_exists(p_jurisdiction_code in varchar2
526 ,p_tax_type in varchar2
527 ,p_effective_date in date )
528 RETURN varchar2
529
530 IS
531
532 /*********************** Bug Number:2973119 Start ****************/
533
534 lv_county_code VARCHAR2(20);
535 lv_city_code VARCHAR2(20);
536 lv_temp_code VARCHAR2(20);
537 ln_index_code NUMBER;
538
539 /*********************** Bug Number:2973119 End ******************/
540
541 lv_state_code VARCHAR2(20);
542 lv_value VARCHAR2(20);
543 lv_return_value VARCHAR2(1);
544
545 BEGIN
546
547 hr_utility.trace('p_jurisdiction_code is : '|| p_jurisdiction_code);
548 hr_utility.trace('p_tax_type is : '|| p_tax_type);
549 hr_utility.trace('p_effective_date is : '|| p_effective_date);
550
551 lv_state_code := substr(p_jurisdiction_code,1,2);
552
553 /********************* Bug Number:2973119 Start *****************/
554
555 lv_county_code := substr(p_jurisdiction_code,4,3);
556 lv_city_code := substr(p_jurisdiction_code,8,4);
557
558 /********************* Bug Number:2973119 End *****************/
559
560 --federal
561 if p_jurisdiction_code = '00-000-0000' then
562
563 if pay_us_payroll_utils.ltr_fed_tax_info.count < 1 then
564 populate_jit_information( p_effective_date => p_effective_date
565 , p_get_federal => 'Y');
566 end if;
567
568 if p_tax_type = 'FUTA WAGE' then
569 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).futa_wage;
570 elsif p_tax_type = 'FUTA RATE' then
571 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).futa_rate;
572 elsif p_tax_type = 'SS EE' then
573 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).ss_ee_wage;
574 elsif p_tax_type = 'SS EE RATE' then
575 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).ss_ee_rate;
576 elsif p_tax_type = 'SS ER' then
577 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).ss_er_wage;
578 elsif p_tax_type = 'SS ER RATE' then
579 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).ss_er_rate;
580 elsif p_tax_type = 'MED EE RATE' then
581 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).med_ee_rate;
582 elsif p_tax_type = 'MED ER RATE' then
583 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).med_er_rate;
584 elsif p_tax_type = '401K' then
585 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).p401_limit;
586 elsif p_tax_type = '403B' then
587 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).p403_limit;
588 elsif p_tax_type = '457' then
589 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).p457_limit;
590 elsif p_tax_type = '401K CATCHUP' then
591 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).catchup_401k;
592 elsif p_tax_type = '403B CATCHUP' then
593 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).catchup_403b;
594 elsif p_tax_type = '457 CATCHUP' then
595 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).catchup_457;
596 elsif p_tax_type = 'DCP' then
597 lv_value := pay_us_payroll_utils.ltr_fed_tax_info(1).dcp_limit;
598 end if;
599
600 --state
601 elsif lv_state_code <> '00' and
602 lv_county_code = '000' and
603 lv_city_code = '0000' then
604
605 if pay_us_payroll_utils.ltr_state_tax_info.count < 1 then
606 populate_jit_information(p_effective_date => p_effective_date
607 ,p_get_state => 'Y');
608 end if;
609
610 if p_tax_type = 'SUI EE' then
611 lv_value := pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sui_ee_limit;
612 elsif p_tax_type = 'SUI ER' then
613 lv_value := pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sui_er_limit;
614 hr_utility.set_location(lv_value,230);
615 elsif p_tax_type = 'SDI EE' then
616 lv_value := pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sdi_ee_limit;
617 elsif p_tax_type = 'SDI1 EE' then
618 lv_value := pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sdi1_ee_limit;
619 elsif p_tax_type = 'SDI ER' then
620 lv_value := pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sdi_er_limit;
621 elsif p_tax_type = 'SIT EE' then
622 lv_value := pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).sit_exists;
623 elsif p_tax_type = 'STEIC EE' then
624 lv_value := pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).steic_exists;
625 elsif p_tax_type = 'FUTA ER' then
626 lv_value := pay_us_payroll_utils.ltr_state_tax_info(lv_state_code).futa_rate;
627 elsif p_tax_type = 'FUTA ' then
628 lv_value := 'Y'; -- Bug3969061
629 elsif p_tax_type in ('WC EE', 'WC2 EE') then
630 begin
631 select 'Y' into lv_value from dual
632 where exists (
633 select 'x'
634 from pay_wc_funds wcf, pay_us_states uss
635 where uss.state_code = lv_state_code
636 and uss.state_abbrev = wcf.state_code
637 and wcf.business_group_id =
638 nvl(hr_general.get_business_group_id, wcf.business_group_id));
639 exception
640 when no_data_found then
641 lv_value := 'N';
642 end;
643 end if;
644
645 /***************** Bug Number:2973119 Start **************************************/
646
647 --county
648 elsif lv_state_code <> '00' and
649 lv_county_code <> '000' and
650 lv_city_code = '0000' then
651
652 if pay_us_payroll_utils.ltr_county_tax_info.count<1 then
653 populate_jit_information(p_effective_date => p_effective_date
654 ,p_get_county => 'Y');
655 end if;
656
657 lv_temp_code := lv_state_code||lv_county_code;
658 ln_index_code := to_number(lv_temp_code);
659
660 if pay_us_payroll_utils.ltr_county_tax_info.exists(ln_index_code) then
661 lv_value := pay_us_payroll_utils.ltr_county_tax_info(ln_index_code).cnty_tax_exists;
662 end if;
663
664 --city
665 elsif lv_state_code <> '00' and
666 lv_county_code <> '000' and
667 lv_city_code <> '0000' then
668
669 if substr(lv_city_code,1,1) = 'U' then
670 /* for user defined cities we should return N because they are not
671 primary cities and will never have tax */
672 lv_value := 'N';
673 else
674 if pay_us_payroll_utils.ltr_city_tax_info.count < 1 then
675 populate_jit_information(p_effective_date => p_effective_date
676 ,p_get_city => 'Y'
677 ,p_jurisdiction_code => p_jurisdiction_code);
678 end if;
679
680 lv_temp_code := lv_state_code||lv_county_code||lv_city_code;
681 hr_utility.trace('lv_temp_code : '|| lv_temp_code);
682 hr_utility.trace(' B4 ln_index_code');
683 ln_index_code := to_number(lv_temp_code);
684 hr_utility.trace(' A4 ln_index_code');
685
686 if p_tax_type = 'CITY' then
687 if pay_us_payroll_utils.ltr_city_tax_info.exists(ln_index_code) then
688
689 hr_utility.trace(' CITY found in PLSQL table');
690 null;
691 else
692 hr_utility.trace(' CITY NOT found in PLSQL table');
693
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
699 := pay_us_payroll_utils.ltr_city_tax_info(ln_index_code).city_tax_exists;
700 end if;
701
702 if p_tax_type = 'HT' then
703 if pay_us_payroll_utils.ltr_city_tax_info.exists(ln_index_code) then
704 hr_utility.trace(' HT found in PLSQL table');
705 null;
706 else
707 hr_utility.trace(' HT NOT found in PLSQL table');
708 populate_jit_information(p_effective_date => p_effective_date
709 ,p_get_city => 'Y'
710 ,p_jurisdiction_code => p_jurisdiction_code);
711 end if;
712 lv_value := pay_us_payroll_utils.ltr_city_tax_info(ln_index_code).city_head_tax_exists;
713 end if;
714
715 /********************* Bug Number:2973119 End ****************************/
716
717 end if; /* substr(lv_city_code,1,1) = 'U' */
718
719 end if;
720
721 if lv_value = 'Y' then
722 lv_return_value := 'Y';
723 elsif nvl(lv_value,'0') = '0' or lv_value = 'N' then
724 lv_return_value := 'N';
725 elsif nvl(lv_value,'0') <> '0' then
726 lv_return_value := 'Y';
727 end if;
728
729 return(lv_return_value);
730 END get_tax_exists;
731
732
733 /*****************************************************************************
734 Name : check_balance_status
735 Purpose : Function should be used to identify whether the balances relevant
736 to partcular attribute are valid for use of BRA.
737 Arguments : 1. Start Date
738 2. Business Group Id
739 3. Atttribute Name
740 4. Legislation Code
741 Return : 'Y' for valid status and 'N' for invalid status of balance
742 Notes : It will used by group level reports (940,941,GRE Totals) to find
743 if all the balances related to a report are valid or not
744 *****************************************************************************/
745
746 FUNCTION check_balance_status(
747 p_start_date in date,
748 p_business_group_id in hr_organization_units.organization_id%type,
749 p_attribute_name in varchar2,
750 p_legislation_code in varchar2 default 'US')
751 RETURN VARCHAR2
752 IS
753
754 /*************************************************************
755 ** Cursor to check if the attribute_name passed as parameter
756 ** exists or not.
757 **************************************************************/
758 CURSOR c_attribute_exists(
759 c_attribute_name in pay_bal_attribute_definitions.attribute_name%type)
760 is
761 select 1
762 from pay_bal_attribute_definitions
763 where attribute_name = c_attribute_name
764 and legislation_code = p_legislation_code;
765
766 CURSOR c_get_valid_count(cp_start_date in date,
767 cp_business_group_id in per_business_groups.business_group_id%type,
768 cp_attribute_name in varchar2) IS
769 select /*+ ORDERED */ count(*)
770 from pay_bal_attribute_definitions pbad,
771 pay_balance_attributes pba,
772 pay_balance_validation pbv
773 where pbad.attribute_name = cp_attribute_name
774 and pbad.attribute_id = pba.attribute_id
775 and (pba.business_group_id = cp_business_group_id
776 OR
777 pba.legislation_code = p_legislation_code)
778 and pba.defined_balance_id = pbv.defined_balance_id
779 and pbv.business_group_id = cp_business_group_id
780 and NVL(pbv.balance_load_date, cp_start_date) <= cp_start_date
781 and nvl(pbv.run_balance_status, 'I') = 'V';
782
783 CURSOR c_get_attribute_count(
784 cp_business_group_id in per_business_groups.business_group_id%type,
785 cp_attribute_name in varchar2) IS
786
787 select count(*)
788 from pay_bal_attribute_definitions pbad,
789 pay_balance_attributes pba
790 where pbad.attribute_name = cp_attribute_name
791 and pbad.attribute_id = pba.attribute_id
792 and (pba.business_group_id = cp_business_group_id
793 OR
794 pba.legislation_code = p_legislation_code );
795
796 ln_attribute_exists NUMBER(1);
797 ln_valid_bal_exists NUMBER(1);
798 lv_return_status VARCHAR2(1) := 'N';
799 lv_package_stage VARCHAR2(50) := 'pay_us_payroll_utils.check_balance_status';
800
801 l_attribute_count number;
802 l_valid_count number;
803 l_trunc_date date; /* Bug 3258868 */
804
805 BEGIN
806 hr_utility.trace('Start of Procedure '||lv_package_stage);
807 hr_utility.set_location(lv_package_stage,10);
808
809 l_trunc_date := trunc(p_start_date,'Y'); -- Bug 3258868
810
811 -- Validate if the attribute passed as parameter exists
812 open c_attribute_exists(p_attribute_name);
813 fetch c_attribute_exists INTO ln_attribute_exists;
814 if c_attribute_exists%notfound then
815 hr_utility.set_location(lv_package_stage,20);
816 lv_return_status := 'N';
817 hr_utility.trace('Invalid Attribute Name');
818 raise_application_error(-20101, 'Error in pay_us_.check_balance_status');
819 end if;
820 close c_attribute_exists ;
821
822 hr_utility.set_location(lv_package_stage,30);
823
824 open c_get_valid_count(l_trunc_date, -- Bug 3258868
825 p_business_group_id,
826 p_attribute_name );
827 fetch c_get_valid_count into l_valid_count;
828 close c_get_valid_count;
829
830 hr_utility.trace('Valid Count for '||p_attribute_name||' is '||to_char(l_valid_count));
831
832 /* Do following check only if the attribute count > zero */
833
834 IF l_valid_count > 0 THEN
835
836 open c_get_attribute_count(
837 p_business_group_id,
838 p_attribute_name );
839 fetch c_get_attribute_count into l_attribute_count;
840 close c_get_attribute_count;
841
842 hr_utility.trace('Attribute Count for '||p_attribute_name||' is '||to_char(l_attribute_count));
843
844 if l_valid_count = l_attribute_count then
845
846 hr_utility.set_location(lv_package_stage,40);
847 lv_return_status := 'Y';
848 else
849
850 -- Bug 3312482 Push statements are deleted.
851 hr_utility.set_location(lv_package_stage,50);
852
853 -- Bug 3340952 Push statements are added again.
854 pay_core_utils.push_message(801,'PAY_EXCEPTION','A');
855 pay_core_utils.push_token('description','Warning Invalid Balance Status . ,In Attribute -> ' ||p_attribute_name);
856
857 hr_utility.trace('Balance Status is Invalid for Attribute -> ' ||p_attribute_name);
858
859 lv_return_status := 'N';
860 end if;
861 end if; /* IF l_valid_count > 0 */
862 hr_utility.trace('End of Procedure ' || lv_package_stage);
863 return(lv_return_status);
864
865
866 EXCEPTION
867 WHEN others THEN
868 hr_utility.set_location(lv_package_stage,60);
869 hr_utility.trace('Invalid Attribute Name');
870 raise_application_error(-20101, 'Error in pay_us_.check_balance_status');
871 raise;
872 END check_balance_status;
873
874 /************************************************************
875 ** Function : formated_header_string
876 ** Arguments: p_input_string
877 ** p_output_file_type
878 ** Returns : input string with the HTML Header tags
879 ** Purpose : This Function will be used by the reports that are
880 ** displaying in HTML format. It returns the input
881 ** string with the HTML Header tags
882 ************************************************************/
883
884 FUNCTION formated_header_string
885 (p_input_string in VARCHAR2
886 ,p_output_file_type in VARCHAR2
887 )
888 RETURN VARCHAR2
889 IS
890
891 lv_format VARCHAR2(1000);
892
893 BEGIN
894 hr_utility.set_location(gv_package || '.formated_header_string', 10);
895 IF p_output_file_type = 'CSV' THEN
896 hr_utility.set_location(gv_package || '.formated_header_string', 20);
897 lv_format := p_input_string;
898 ELSIF p_output_file_type = 'HTML' THEN
899 hr_utility.set_location(gv_package || '.formated_header_string', 30);
900 lv_format := '<HTML><HEAD> <CENTER> <H1> <B>' || p_input_string ||
901 '</B></H1></CENTER></HEAD>';
902 END IF;
903
904 hr_utility.set_location(gv_package || '.formated_header_string', 40);
905 return lv_format;
906
907 END formated_header_string;
908
909
910 /************************************************************
911 ** Function : formated_data_string
912 ** Arguments: p_input_string
913 ** p_output_file_type
914 ** p_bold
915 ** Returns : the formated input string based on the Output
916 ** format. If the format is CSV then the values are
917 ** returned seperated by comma (,). If the format is
918 ** HTML then the returned string has the HTML tags.
919 ** The parameter p_bold only works for the HTML
920 ** format.
921 ** Purpose : This Function will be used by reports that are
922 ** displaying in HTML/CSV format.
923 ************************************************************/
924 FUNCTION formated_data_string
925 (p_input_string in VARCHAR2
926 ,p_output_file_type in VARCHAR2
927 ,p_bold in VARCHAR2
928 )
929 RETURN VARCHAR2
930 IS
931
932 lv_format VARCHAR2(1000);
933 lv_bold VARCHAR2(10);
934 BEGIN
935 lv_bold := nvl(p_bold,'N');
936 hr_utility.set_location(gv_package || '.formated_data_string', 10);
937 IF p_output_file_type = 'CSV' THEN
938 hr_utility.set_location(gv_package || '.formated_data_string', 20);
939 lv_format := gc_csv_data_delimiter || p_input_string ||
940 gc_csv_data_delimiter || gc_csv_delimiter;
941 ELSIF p_output_file_type = 'HTML' THEN
942 IF p_input_string is null THEN
943 hr_utility.set_location(gv_package || '.formated_data_string', 30);
944 lv_format := gv_html_start_data || ' ' || gv_html_end_data; -- Bug 3179050
945 ELSE
946 IF lv_bold = 'Y' THEN
947 hr_utility.set_location(gv_package || '.formated_data_string', 40);
948 lv_format := gv_html_start_data || '<b> ' || p_input_string
949 || '</b>' || gv_html_end_data;
950 ELSE
951 hr_utility.set_location(gv_package || '.formated_data_string', 50);
952 lv_format := gv_html_start_data || p_input_string || gv_html_end_data;
953 END IF;
954 END IF;
955 END IF;
956
957 hr_utility.set_location(gv_package || '.formated_data_string', 60);
958 return lv_format;
959
960 END formated_data_string;
961
962
963 /**************************************************************************
964 ** Function : get_parameter
965 ** Arguments: p_param_name
966 ** p_parameter_list
967 ** Returns : the the value for the parameter p_param_name
968 ** from the p_parameter_list
969 ** This function is called to get the value entered
970 ** by the user which is stored in legislative
971 ** parameters. Both the name and list is passed to
972 ** the function.
973 **************************************************************************/
974 FUNCTION get_parameter(p_parameter_name in varchar2,
975 p_parameter_list in varchar2)
976 RETURN VARCHAR2
977 IS
978 ln_start_ptr NUMBER;
979 ln_end_ptr NUMBER;
980
981 lv_token_value pay_payroll_actions.legislative_parameters%type;
982 lv_par_value pay_payroll_actions.legislative_parameters%type;
983 BEGIN
984
985 --4518409 lv_token_value := ' ' || p_parameter_name||'=';
986
987 if substr(p_parameter_list,1,length(p_parameter_name)) <> p_parameter_name then
988 lv_token_value := ' ' || p_parameter_name||'=';
989 else
990 lv_token_value := p_parameter_name||'=';
991 end if;
992
993 ln_start_ptr := instr(p_parameter_list, lv_token_value) + length(lv_token_value);
994 ln_end_ptr := instr(p_parameter_list, ' ', ln_start_ptr);
995
996 /* if there is no spaces use then length of the string */
997 if ln_end_ptr = 0 then
998 ln_end_ptr := length(p_parameter_list)+1;
999 end if;
1000
1001 /* Did we find the token */
1002 if instr(p_parameter_list, lv_token_value) = 0 then
1003 lv_par_value := NULL;
1004 else
1005 lv_par_value := substr(p_parameter_list, ln_start_ptr, ln_end_ptr - ln_start_ptr);
1006 end if;
1007
1008 return lv_par_value;
1009
1010 END get_parameter;
1011
1012
1013
1014
1015 /**************************************************************************
1016 ** Function : ssn_reporting_preferences
1017 ** Arguments: p_loc_id location Id,
1018 p_org_id organization Id,
1019 p_bg_id business group Id
1020 ** Returns : The value for the parameter lv_display_ssn
1021 ** This function is called is called from Check Writer,Deposit Advice
1022 ** and Archive Check WRiter and Deposit Advice Reports.It is supposed to
1023 ** return if we want to show SSN on the output of these reoprts or not.
1024 ** The Function checks the value set by the user at location then organization and
1025 ** finally at BG level.It was added for Bug 3892148.
1026 **************************************************************************/
1027 FUNCTION ssn_reporting_preferences(p_loc_id in number,
1028 p_org_id in number,
1029 p_bg_id in number)
1030 RETURN VARCHAR2
1031 IS
1032 lv_display_ssn varchar2(100);
1033
1034 cursor c_loc_pref(cp_location_id in number) is
1035 select lei_information1
1036 from hr_location_extra_info hlei
1037 where hlei.location_id = cp_location_id
1038 and information_type = 'US_LOC_REP_PREFERENCES';
1039
1040 cursor c_org_pref(cp_organization_id in number
1041 ,cp_org_information_context in varchar2) is
1042 select org_information1
1043 from hr_organization_information hoi
1044 where organization_id = cp_organization_id
1045 and org_information_context = cp_org_information_context;
1046
1047 BEGIN
1048 open c_loc_pref(p_loc_id);
1049 fetch c_loc_pref into lv_display_ssn;
1050 if c_loc_pref%notfound or lv_display_ssn is null then
1051 open c_org_pref(p_org_id, 'US_ORG_REP_PREFERENCES');
1052 fetch c_org_pref into lv_display_ssn;
1053 if c_org_pref%notfound or lv_display_ssn is null then
1054 close c_org_pref;
1055
1056 open c_org_pref(p_org_id, 'US_BG_REP_PREFERENCES');
1057 fetch c_org_pref into lv_display_ssn;
1058 if c_org_pref%notfound or lv_display_ssn is null then
1059 lv_display_ssn := 'Y';
1060 end if;
1061 end if;
1062 close c_org_pref;
1063 end if;
1064 close c_loc_pref;
1065
1066 if nvl(lv_display_ssn,'Y') = 'Y' then
1067 return 'Y';
1068 else
1069 return 'N';
1070 end if;
1071
1072 END ssn_reporting_preferences;
1073
1074
1075 FUNCTION get_min_action(p_business_group_id in number
1076 ,p_start_date in date
1077 ,p_end_date in date
1078 ,p_tax_unit_id in number default null
1079 ,p_payroll_id in number default null)
1080 RETURN NUMBER
1081 IS
1082 cursor c_get_min_action(cp_business_group_id in number
1083 ,cp_start_date in date
1084 ,cp_end_date in date
1085 ,cp_tax_unit_id in number
1086 ,cp_payroll_id in number) is
1087 select nvl(min(assignment_action_id),-1)
1088 from pay_assignment_actions paa,
1089 pay_payroll_actions ppa,
1090 pay_payrolls_f ppf
1091 where ppa.business_group_id +0 = cp_business_group_id
1092 and ppa.payroll_action_id = paa.payroll_action_id
1093 and ppa.effective_date between cp_start_date and cp_end_date
1094 and ppa.action_type in ('R','Q','I','B','V')
1095 and ppf.payroll_id = ppa.payroll_id
1096 and ppa.business_group_id +0 = ppf.business_group_id
1097 and paa.tax_unit_id = nvl(cp_tax_unit_id, paa.tax_unit_id)
1098 and ppf.payroll_id = nvl(cp_payroll_id, ppf.payroll_id);
1099
1100 ln_min_action NUMBER;
1101
1102 BEGIN
1103 open c_get_min_action(p_business_group_id
1104 ,p_start_date
1105 ,p_end_date
1106 ,p_tax_unit_id
1107 ,p_payroll_id);
1108 fetch c_get_min_action into ln_min_action;
1109 close c_get_min_action;
1110
1111 pay_us_balance_view_pkg.set_session_var('GRP_AAID',to_char(ln_min_action));
1112
1113 return(ln_min_action);
1114
1115 END get_min_action;
1116
1117 /*****************************************************************************
1118 Name : update_state_or_local_wh_link
1119 Purpose : This procedure updates the state W4 withholding link and local
1120 tax form link for the State if local link exists. Otherwise
1121 creates a new local tax form link.
1122 Arguments : p_state - Name of the State
1123 p_state_or_local_tax_form - State/Local form to update
1124 p_link - New Link to be used
1125 Notes :
1126 *****************************************************************************/
1127 PROCEDURE update_state_or_local_wh_link(errbuf OUT NOCOPY VARCHAR2,
1128 retcode OUT NOCOPY NUMBER,
1129 p_state_or_local_tax_form IN VARCHAR2,
1130 p_state IN VARCHAR2,
1131 p_link IN VARCHAR2) IS
1132
1133 /* Declaration of local Variables */
1134 l_sysdate DATE := TRUNC(SYSDATE);
1135 l_app_user fnd_user.user_name%TYPE;
1136 l_lookup_type fnd_lookup_values.lookup_type%TYPE;
1137 l_view_application_id fnd_lookup_values.view_application_id%TYPE;
1138 l_lookup_code fnd_lookup_values.lookup_code%TYPE;
1139 l_security_group_id fnd_lookup_values.security_group_id%TYPE;
1140 l_language fnd_lookup_values.language%TYPE;
1141 l_state_name pay_us_states.state_name%TYPE;
1142 l_application_id fnd_application.application_id%TYPE;
1143 l_security_group_id1 fnd_security_groups.security_group_id%TYPE;
1144 l_procedure_name VARCHAR2(50) := 'update_state_or_local_wh_link';
1145
1146 /* Cursor to fetch the lookup details */
1147 CURSOR get_lookup_value_cur(v_lookup_code IN VARCHAR2,
1148 v_lookup_type IN VARCHAR2) IS
1149 SELECT lookup_type,
1150 view_application_id,
1151 lookup_code,
1152 security_group_id,
1153 language
1154 FROM fnd_lookup_values
1155 WHERE lookup_type = v_lookup_type
1156 AND lookup_code = v_lookup_code
1157 AND language = userenv ('LANG');
1158
1159 /* Cursor to fetch the state name */
1160 CURSOR get_state_name_cur(v_state IN VARCHAR2) IS
1161 SELECT state_name
1162 FROM pay_us_states
1163 WHERE state_abbrev = v_state;
1164
1165 /* Cursor to find the Security_Group_Id */
1166 CURSOR get_security_group_id_cur(v_application_id Number ) IS
1167 SELECT sg.security_group_id
1168 FROM fnd_lookup_types lt, fnd_security_groups sg
1169 WHERE lt.lookup_type = 'PAY_US_LOCAL_PDF_LINK_W4'
1170 AND lt.view_application_id = v_application_id
1171 AND lt.security_group_id = sg.security_group_id
1172 AND sg.security_group_key ='STANDARD';
1173
1174 /* Cursor to find the Application_Id */
1175 CURSOR get_application_id_cur IS
1176 SELECT application_id
1177 FROM fnd_application
1178 WHERE application_short_name = 'AU';
1179
1180 BEGIN
1181 hr_utility.set_location(gv_package || '.' || l_procedure_name, 10);
1182
1183 /* Derive Owner_Id for Owner Name ORACLE */
1184 l_app_user := fnd_load_util.owner_id('ORACLE');
1185
1186 hr_utility.set_location(gv_package || '.' || l_procedure_name, 20);
1187
1188 IF p_state_or_local_tax_form = 'STATE' THEN
1189
1190 hr_utility.set_location(gv_package || '.' || l_procedure_name, 30);
1191
1192 /* Get the Lookup Details */
1193 OPEN get_lookup_value_cur(p_state, 'PAY_US_OTF_PDF_LINK_W4');
1194 FETCH get_lookup_value_cur INTO l_lookup_type, l_view_application_id,
1195 l_lookup_code, l_security_group_id,
1196 l_language;
1197
1198 IF get_lookup_value_cur%FOUND THEN
1199 hr_utility.set_location(gv_package || '.' || l_procedure_name, 40);
1200
1201 /* Update the State Link for the Lookup */
1202 UPDATE fnd_lookup_values
1203 SET description = p_link,
1204 meaning = 'User updated: ' || LTRIM(meaning, 'User updated: '),
1205 last_updated_by = l_app_user,
1206 last_update_date = l_sysdate
1207 WHERE lookup_type = l_lookup_type
1208 AND view_application_id = l_view_application_id
1209 AND lookup_code = l_lookup_code
1210 AND security_group_id = l_security_group_id
1211 AND language = l_language;
1212 END IF;
1213
1214 CLOSE get_lookup_value_cur;
1215
1216 hr_utility.set_location(gv_package || '.' || l_procedure_name, 50);
1217
1218 ELSIF p_state_or_local_tax_form = 'LOCAL' THEN
1219
1220 hr_utility.set_location(gv_package || '.' || l_procedure_name, 60);
1221
1222 /* Get the Lookup Details */
1223 OPEN get_lookup_value_cur(p_state, 'PAY_US_LOCAL_PDF_LINK_W4');
1224 FETCH get_lookup_value_cur INTO l_lookup_type, l_view_application_id,
1225 l_lookup_code, l_security_group_id,
1226 l_language;
1227
1228 IF get_lookup_value_cur%NOTFOUND THEN
1229
1230 hr_utility.set_location(gv_package || '.' || l_procedure_name, 70);
1231
1232 /* Get the State Name */
1233 OPEN get_state_name_cur(p_state);
1234 FETCH get_state_name_cur into l_state_name;
1235 CLOSE get_state_name_cur;
1236
1237 hr_utility.set_location(gv_package || '.' || l_procedure_name, 80);
1238
1239 /* Get the Application Id */
1240 OPEN get_application_id_cur;
1241 FETCH get_application_id_cur into l_application_id;
1242 CLOSE get_application_id_cur;
1243
1244 hr_utility.set_location(gv_package || '.' || l_procedure_name, 90);
1245
1246 /* Get the Security Group Id */
1247 OPEN get_security_group_id_cur(l_application_id);
1248 FETCH get_security_group_id_cur into l_security_group_id1;
1249 CLOSE get_security_group_id_cur;
1250
1251 /* Insert the new Lookup Code */
1252 INSERT INTO fnd_lookup_values
1253 ( lookup_type
1254 ,language
1255 ,lookup_code
1256 ,meaning
1257 ,description
1258 ,enabled_flag
1259 ,created_by
1260 ,creation_date
1261 ,last_updated_by
1262 ,last_update_login
1263 ,last_update_date
1264 ,source_lang
1265 ,security_group_id
1266 ,view_application_id )
1267 VALUES
1268 ( 'PAY_US_LOCAL_PDF_LINK_W4'
1269 ,'US'
1270 ,p_state
1271 ,'HTTP hyperlink to '|| l_state_name || ' local pdf form'
1272 ,p_link
1273 ,'Y'
1274 ,l_app_user
1275 ,l_sysdate
1276 ,l_app_user
1277 ,0
1278 ,l_sysdate
1279 ,'US'
1280 ,l_security_group_id1
1281 ,l_application_id );
1282
1283 hr_utility.set_location(gv_package || '.' || l_procedure_name, 100);
1284
1285 ELSE
1286 hr_utility.set_location(gv_package || '.' || l_procedure_name, 110);
1287
1288 /* Update the Local Link for the Lookup */
1289 UPDATE fnd_lookup_values
1290 SET description = p_link,
1291 last_updated_by = l_app_user,
1292 last_update_date = l_sysdate
1293 WHERE lookup_type = l_lookup_type
1294 AND view_application_id = l_view_application_id
1295 AND lookup_code = l_lookup_code
1296 AND security_group_id = l_security_group_id
1297 AND language = l_language;
1298
1299 END IF;
1300
1301 CLOSE get_lookup_value_cur;
1302 hr_utility.set_location(gv_package || '.' || l_procedure_name, 120);
1303
1304 END IF;
1305 COMMIT;
1306 hr_utility.set_location(gv_package || '.' || l_procedure_name, 130);
1307
1308
1309 EXCEPTION
1310 when others then
1311 hr_utility.set_location(gv_package || '.' || l_procedure_name, 130);
1312 raise_application_error(-20101, 'Error while updating the Link');
1313 raise;
1314 END update_state_or_local_wh_link;
1315
1316 END pay_us_payroll_utils;
[Home] [Help]