[Home] [Help]
PACKAGE BODY: APPS.PAY_US_YEAR_BEGIN_PROCESS
Source
1 PACKAGE BODY pay_us_year_begin_process AS
2 /* $Header: payusyearbegin.pkb 120.0 2005/05/29 11:26:41 appldev noship $ */
3 --
4 /*
5 ******************************************************************
6 * *
7 * Copyright (C) 1996 Oracle Corporation. *
8 * All rights reserved. *
9 * *
10 * This material has been provided pursuant to an agreement *
11 * containing restrictions on its use. The material is also *
12 * protected by copyright law. No part of this material may *
13 * be copied or distributed, transmitted or transcribed, in *
14 * any form or by any means, electronic, mechanical, magnetic, *
15 * manual, or otherwise, or disclosed to third parties without *
16 * the express written permission of Oracle Corporation, *
17 * 500 Oracle Parkway, Redwood City, CA, 94065. *
18 * *
19 ******************************************************************
20
21 Name : pay_us_year_begin_process
22
23
24 Change List
25 -----------
26 Date Name Vers Bug No Description
27 ---- ---- ------ ------- -----------
28 09-Nov-04 schauhan 115.0 3625425 Intial Version
29 19-Dec-04 meshah 115.1 4132366 added a additional
30 condition in updates
31 of state and city tax
32 records.
33 */
34
35 PROCEDURE sui_wb_ovr
36 (p_business_group in varchar2
37 ,p_curr_year in varchar2
38 )
39 IS
40
41 cursor csr_get_asg(p_start_day DATE,
42 p_business_group varchar2) is
43 select *
44 from pay_us_emp_state_tax_rules_f pst
45 where pst.sui_wage_base_override_amount is not null
46 and p_start_day between (pst.effective_start_date+1) and pst.effective_end_date
47 and pst.business_group_id = to_number(p_business_group) ;
48
49 l_state_rec PAY_US_EMP_STATE_TAX_RULES_F%rowtype;
50 l_last_day DATE;
51 l_start_day DATE;
52 l_last_year VARCHAR2(4);
53 l_curr_year VARCHAR2(4);
54
55 BEGIN
56 --hr_utility.trace_on(null,'oracle');
57 /* Get the assignments which have non zero SUI WAGE BASE OVERRIDE AMOUNT */
58 l_curr_year := p_curr_year;
59 hr_utility.trace('l_curr_year '||l_curr_year);
60
61 l_last_year := to_number(l_curr_year) - 1;
62
63 hr_utility.trace('l_last_year '||l_last_year);
64
65 l_last_day := to_date('12/31/'||l_last_year,'MM/DD/YYYY');
66 l_start_day := to_date('01/01/'||l_curr_year,'MM/DD/YYYY');
67
68
69 hr_utility.trace('l_last_day '||to_char(l_last_day));
70 hr_utility.trace('l_start_day '||to_char(l_start_day));
71
72 open csr_get_asg(l_start_day , p_business_group);
73
74 hr_utility.trace('Updating the state tax records ...');
75
76 loop
77
78 fetch csr_get_asg into l_state_rec;
79
80 exit when csr_get_asg%NOTFOUND;
81
82 hr_utility.trace('Updating Assignment : ' ||
83 to_char(l_state_rec.assignment_id));
84 hr_utility.trace(to_char(l_state_rec.emp_state_tax_rule_id));
85 hr_utility.trace(to_char(l_state_rec.effective_end_date));
86 hr_utility.trace(to_char( l_state_rec.assignment_id));
87 hr_utility.trace(l_state_rec.state_code);
88 hr_utility.trace(l_state_rec.jurisdiction_code);
89 hr_utility.trace(to_char(l_state_rec.business_group_id));
90 hr_utility.trace(to_char( l_state_rec.additional_wa_amount));
91 hr_utility.trace( l_state_rec.filing_status_code);
92 hr_utility.trace(to_char( l_state_rec.remainder_percent));
93 hr_utility.trace(to_char( l_state_rec.secondary_wa));
94 hr_utility.trace(to_char( l_state_rec.sit_additional_tax));
95 hr_utility.trace(to_char( l_state_rec.sit_override_amount));
96 hr_utility.trace(to_char( l_state_rec.sit_override_rate));
97 hr_utility.trace(to_char( l_state_rec.withholding_allowances));
98
99 /* End date the state tax record as of /12/31/(input year-1) */
100
101
102 update PAY_US_EMP_STATE_TAX_RULES_F
103 set effective_end_date = l_last_day --to_date('12/31/'||end_year,'MM/DD/YYYY')
104 where emp_state_tax_rule_id = l_state_rec.emp_state_tax_rule_id
105 and assignment_id = l_state_rec.assignment_id
106 and effective_start_date = l_state_rec.effective_start_date
107 and effective_end_date = l_state_rec.effective_end_date
108 and sui_wage_base_override_amount is not null;
109
110 /* Null out the SUI WAGE BASE OVERRIDE AMOUNT as of 01/01/1999 */
111 hr_utility.trace('Inserting Assignment : ' ||to_char(l_state_rec.assignment_id));
112
113 hr_utility.trace('Inserting Assignment Start Date : ' ||to_char(l_start_day));
114
115 hr_utility.trace('Inserting Assignment End Date : ' ||to_char(l_state_rec.effective_end_date));
116
117 /*We need to reset INDIANA EIC Also */
118 /* IF (l_state_rec.sta_information_category <> 'IN' AND l_state_rec.state_code <> '15') THEN
119 l_ind_eic := l_state_rec.sta_information1;
120 END IF; will write a new procedure for this too*/
121
122
123 insert into PAY_US_EMP_STATE_TAX_RULES_F
124 (emp_state_tax_rule_id,
125 effective_start_date,
126 effective_end_date,
127 assignment_id,
128 state_code,
129 jurisdiction_code,
130 business_group_id,
131 additional_wa_amount,
132 filing_status_code,
133 remainder_percent,
134 secondary_wa,
135 sit_additional_tax,
136 sit_override_amount,
137 sit_override_rate,
138 withholding_allowances,
139 excessive_wa_reject_date,
140 sdi_exempt,
141 sit_exempt,
142 sit_optional_calc_ind,
143 state_non_resident_cert,
144 sui_exempt,
145 wc_exempt,
146 sui_wage_base_override_amount,
147 supp_tax_override_rate,
148 object_version_number)
149 values
150 (l_state_rec.emp_state_tax_rule_id,
151 l_start_day,
152 l_state_rec.effective_end_date,
153 l_state_rec.assignment_id,
154 l_state_rec.state_code,
155 l_state_rec.jurisdiction_code,
156 l_state_rec.business_group_id,
157 l_state_rec.additional_wa_amount,
158 l_state_rec.filing_status_code,
159 l_state_rec.remainder_percent,
160 l_state_rec.secondary_wa,
161 l_state_rec.sit_additional_tax,
162 l_state_rec.sit_override_amount,
163 l_state_rec.sit_override_rate,
164 l_state_rec.withholding_allowances,
165 l_state_rec.excessive_wa_reject_date,
166 l_state_rec.sdi_exempt,
167 l_state_rec.sit_exempt,
168 l_state_rec.sit_optional_calc_ind,
169 l_state_rec.state_non_resident_cert,
170 l_state_rec.sui_exempt,
171 l_state_rec.wc_exempt,
172 null,
173 l_state_rec.supp_tax_override_rate,
174 l_state_rec.object_version_number) ;
175
176 hr_utility.trace('Updated Assignment : ' ||
177 to_char(l_state_rec.assignment_id));
178 commit;
179 end loop;
180 close csr_get_asg;
181
182 END;
183
184
185 PROCEDURE ind_eic_ovr
186 (p_business_group in varchar2
187 ,p_curr_year in varchar2
188 )
189 IS
190
191 cursor csr_get_asg(p_start_day DATE,
192 p_business_group varchar2) is
193 select *
194 from pay_us_emp_state_tax_rules_f pst
195 where pst.sta_information1 is not null
196 and p_start_day between (pst.effective_start_date+1) and pst.effective_end_date
197 and sta_information_category = 'IN'
198 and state_code = '15'
199 and pst.business_group_id = to_number(p_business_group);
200
201 l_state_rec PAY_US_EMP_STATE_TAX_RULES_F%rowtype;
202 l_last_day DATE;
203 l_start_day DATE;
204 l_last_year VARCHAR2(4);
205 l_curr_year VARCHAR2(4);
206
207 BEGIN
208 --hr_utility.trace_on(null,'oracle');
209 /* Get the assignments which have non zero SUI WAGE BASE OVERRIDE AMOUNT */
210 l_curr_year := p_curr_year;
211 hr_utility.trace('l_curr_year '||l_curr_year);
212
213 l_last_year := to_number(l_curr_year) - 1;
214
215 hr_utility.trace('l_last_year '||l_last_year);
216
217 l_last_day := to_date('12/31/'||l_last_year,'MM/DD/YYYY');
218 l_start_day := to_date('01/01/'||l_curr_year,'MM/DD/YYYY');
219
220
221 hr_utility.trace('l_last_day '||to_char(l_last_day));
222 hr_utility.trace('l_start_day '||to_char(l_start_day));
223
224 open csr_get_asg(l_start_day , p_business_group);
225
226 hr_utility.trace('Updating the state tax records ...');
227
228 loop
229
230 fetch csr_get_asg into l_state_rec;
231
232 exit when csr_get_asg%NOTFOUND;
233
234 hr_utility.trace('Updating Assignment : ' ||
235 to_char(l_state_rec.assignment_id));
236 hr_utility.trace(to_char(l_state_rec.emp_state_tax_rule_id));
237 hr_utility.trace(to_char(l_state_rec.effective_end_date));
238 hr_utility.trace(to_char( l_state_rec.assignment_id));
239 hr_utility.trace(l_state_rec.state_code);
240 hr_utility.trace(l_state_rec.jurisdiction_code);
241 hr_utility.trace(to_char(l_state_rec.business_group_id));
242 hr_utility.trace(to_char( l_state_rec.additional_wa_amount));
243 hr_utility.trace( l_state_rec.filing_status_code);
244 hr_utility.trace(to_char( l_state_rec.remainder_percent));
245 hr_utility.trace(to_char( l_state_rec.secondary_wa));
246 hr_utility.trace(to_char( l_state_rec.sit_additional_tax));
247 hr_utility.trace(to_char( l_state_rec.sit_override_amount));
248 hr_utility.trace(to_char( l_state_rec.sit_override_rate));
249 hr_utility.trace(to_char( l_state_rec.withholding_allowances));
250
251 /* End date the state tax record as of /12/31/(input year-1) */
252
253
254 update PAY_US_EMP_STATE_TAX_RULES_F
255 set effective_end_date = l_last_day --to_date('12/31/'||end_year,'MM/DD/YYYY')
256 where emp_state_tax_rule_id = l_state_rec.emp_state_tax_rule_id
257 and assignment_id = l_state_rec.assignment_id
258 and effective_start_date = l_state_rec.effective_start_date
259 and effective_end_date = l_state_rec.effective_end_date
260 and sta_information1 is not null;
261
262 /* Null out the Indiana EIC as of 01/01/1999 */
263 hr_utility.trace('Inserting Assignment : ' ||to_char(l_state_rec.assignment_id));
264
265 hr_utility.trace('Inserting Assignment Start Date : ' ||to_char(l_start_day));
266
267 hr_utility.trace('Inserting Assignment End Date : ' ||to_char(l_state_rec.effective_end_date));
268
269 insert into PAY_US_EMP_STATE_TAX_RULES_F
270 (
271 EMP_STATE_TAX_RULE_ID,
272 EFFECTIVE_START_DATE ,
273 EFFECTIVE_END_DATE ,
274 ASSIGNMENT_ID ,
275 STATE_CODE ,
276 JURISDICTION_CODE ,
277 BUSINESS_GROUP_ID ,
278 ADDITIONAL_WA_AMOUNT ,
279 FILING_STATUS_CODE ,
280 REMAINDER_PERCENT ,
281 SECONDARY_WA ,
282 SIT_ADDITIONAL_TAX ,
283 SIT_OVERRIDE_AMOUNT ,
284 SIT_OVERRIDE_RATE ,
285 WITHHOLDING_ALLOWANCES ,
286 EXCESSIVE_WA_REJECT_DATE ,
287 SDI_EXEMPT ,
288 SIT_EXEMPT ,
289 SIT_OPTIONAL_CALC_IND ,
290 STATE_NON_RESIDENT_CERT ,
291 SUI_EXEMPT ,
292 WC_EXEMPT ,
293 SUI_WAGE_BASE_OVERRIDE_AMOUNT ,
294 SUPP_TAX_OVERRIDE_RATE ,
295 LAST_UPDATE_DATE ,
296 LAST_UPDATED_BY ,
297 LAST_UPDATE_LOGIN ,
298 CREATED_BY ,
299 CREATION_DATE ,
300 OBJECT_VERSION_NUMBER ,
301 ATTRIBUTE_CATEGORY ,
302 ATTRIBUTE1 ,
303 ATTRIBUTE2 ,
304 ATTRIBUTE3 ,
305 ATTRIBUTE4 ,
306 ATTRIBUTE5 ,
307 ATTRIBUTE6 ,
308 ATTRIBUTE7 ,
309 ATTRIBUTE8 ,
310 ATTRIBUTE9 ,
311 ATTRIBUTE10 ,
312 ATTRIBUTE11 ,
313 ATTRIBUTE12 ,
314 ATTRIBUTE13 ,
315 ATTRIBUTE14 ,
316 ATTRIBUTE15 ,
317 ATTRIBUTE16 ,
318 ATTRIBUTE17 ,
319 ATTRIBUTE18 ,
320 ATTRIBUTE19 ,
321 ATTRIBUTE20 ,
322 ATTRIBUTE21 ,
323 ATTRIBUTE22 ,
324 ATTRIBUTE23 ,
325 ATTRIBUTE24 ,
326 ATTRIBUTE25 ,
327 ATTRIBUTE26 ,
328 ATTRIBUTE27 ,
329 ATTRIBUTE28 ,
330 ATTRIBUTE29 ,
331 ATTRIBUTE30 ,
332 STA_INFORMATION_CATEGORY ,
333 STA_INFORMATION1 ,
334 STA_INFORMATION2 ,
335 STA_INFORMATION3 ,
336 STA_INFORMATION4 ,
337 STA_INFORMATION5 ,
338 STA_INFORMATION6 ,
339 STA_INFORMATION7 ,
340 STA_INFORMATION8 ,
341 STA_INFORMATION9 ,
342 STA_INFORMATION10 ,
343 STA_INFORMATION11 ,
344 STA_INFORMATION12 ,
345 STA_INFORMATION13 ,
346 STA_INFORMATION14 ,
347 STA_INFORMATION15 ,
348 STA_INFORMATION16 ,
349 STA_INFORMATION17 ,
350 STA_INFORMATION18 ,
351 STA_INFORMATION19 ,
352 STA_INFORMATION20 ,
353 STA_INFORMATION21 ,
354 STA_INFORMATION22 ,
355 STA_INFORMATION23 ,
356 STA_INFORMATION24 ,
357 STA_INFORMATION25 ,
358 STA_INFORMATION26 ,
359 STA_INFORMATION27 ,
360 STA_INFORMATION28 ,
361 STA_INFORMATION29 ,
362 STA_INFORMATION30 )
363 values
364 ( l_state_rec.EMP_STATE_TAX_RULE_ID,
365 l_start_day,
366 l_state_rec.EFFECTIVE_END_DATE,
367 l_state_rec.ASSIGNMENT_ID,
368 l_state_rec.STATE_CODE ,
369 l_state_rec.JURISDICTION_CODE ,
370 l_state_rec.BUSINESS_GROUP_ID ,
371 l_state_rec.ADDITIONAL_WA_AMOUNT ,
372 l_state_rec.FILING_STATUS_CODE ,
373 l_state_rec.REMAINDER_PERCENT ,
374 l_state_rec.SECONDARY_WA ,
375 l_state_rec.SIT_ADDITIONAL_TAX ,
376 l_state_rec.SIT_OVERRIDE_AMOUNT ,
377 l_state_rec.SIT_OVERRIDE_RATE ,
378 l_state_rec.WITHHOLDING_ALLOWANCES ,
379 l_state_rec.EXCESSIVE_WA_REJECT_DATE ,
380 l_state_rec.SDI_EXEMPT ,
381 l_state_rec.SIT_EXEMPT ,
382 l_state_rec.SIT_OPTIONAL_CALC_IND ,
383 l_state_rec.STATE_NON_RESIDENT_CERT ,
384 l_state_rec.SUI_EXEMPT ,
385 l_state_rec.WC_EXEMPT ,
386 l_state_rec.SUI_WAGE_BASE_OVERRIDE_AMOUNT ,
387 l_state_rec.SUPP_TAX_OVERRIDE_RATE ,
388 l_state_rec.LAST_UPDATE_DATE ,
389 l_state_rec.LAST_UPDATED_BY ,
390 l_state_rec.LAST_UPDATE_LOGIN ,
391 l_state_rec.CREATED_BY ,
392 l_state_rec.CREATION_DATE ,
393 l_state_rec.OBJECT_VERSION_NUMBER ,
394 l_state_rec.ATTRIBUTE_CATEGORY ,
395 l_state_rec.ATTRIBUTE1 ,
396 l_state_rec.ATTRIBUTE2 ,
397 l_state_rec.ATTRIBUTE3 ,
398 l_state_rec.ATTRIBUTE4 ,
399 l_state_rec.ATTRIBUTE5 ,
400 l_state_rec.ATTRIBUTE6 ,
401 l_state_rec.ATTRIBUTE7 ,
402 l_state_rec.ATTRIBUTE8 ,
403 l_state_rec.ATTRIBUTE9 ,
404 l_state_rec.ATTRIBUTE10 ,
405 l_state_rec.ATTRIBUTE11 ,
406 l_state_rec.ATTRIBUTE12 ,
407 l_state_rec.ATTRIBUTE13 ,
408 l_state_rec.ATTRIBUTE14 ,
409 l_state_rec.ATTRIBUTE15 ,
410 l_state_rec.ATTRIBUTE16 ,
411 l_state_rec.ATTRIBUTE17 ,
412 l_state_rec.ATTRIBUTE18 ,
413 l_state_rec.ATTRIBUTE19 ,
414 l_state_rec.ATTRIBUTE20 ,
415 l_state_rec.ATTRIBUTE21 ,
416 l_state_rec.ATTRIBUTE22 ,
417 l_state_rec.ATTRIBUTE23 ,
418 l_state_rec.ATTRIBUTE24 ,
419 l_state_rec.ATTRIBUTE25 ,
420 l_state_rec.ATTRIBUTE26 ,
421 l_state_rec.ATTRIBUTE27 ,
422 l_state_rec.ATTRIBUTE28 ,
423 l_state_rec.ATTRIBUTE29 ,
424 l_state_rec.ATTRIBUTE30 ,
425 l_state_rec.STA_INFORMATION_CATEGORY ,
426 NULL ,
427 l_state_rec.STA_INFORMATION2 ,
428 l_state_rec.STA_INFORMATION3 ,
429 l_state_rec.STA_INFORMATION4 ,
430 l_state_rec.STA_INFORMATION5 ,
431 l_state_rec.STA_INFORMATION6 ,
432 l_state_rec.STA_INFORMATION7 ,
433 l_state_rec.STA_INFORMATION8 ,
434 l_state_rec.STA_INFORMATION9 ,
435 l_state_rec.STA_INFORMATION10 ,
436 l_state_rec.STA_INFORMATION11 ,
437 l_state_rec.STA_INFORMATION12 ,
438 l_state_rec.STA_INFORMATION13 ,
439 l_state_rec.STA_INFORMATION14 ,
440 l_state_rec.STA_INFORMATION15 ,
441 l_state_rec.STA_INFORMATION16 ,
442 l_state_rec.STA_INFORMATION17 ,
443 l_state_rec.STA_INFORMATION18 ,
444 l_state_rec.STA_INFORMATION19 ,
445 l_state_rec.STA_INFORMATION20 ,
446 l_state_rec.STA_INFORMATION21 ,
447 l_state_rec.STA_INFORMATION22 ,
448 l_state_rec.STA_INFORMATION23 ,
449 l_state_rec.STA_INFORMATION24 ,
450 l_state_rec.STA_INFORMATION25 ,
451 l_state_rec.STA_INFORMATION26 ,
452 l_state_rec.STA_INFORMATION27 ,
453 l_state_rec.STA_INFORMATION28 ,
454 l_state_rec.STA_INFORMATION29 ,
455 l_state_rec.STA_INFORMATION30 ) ;
456
457 hr_utility.trace('Updated Assignment : ' ||
458 to_char(l_state_rec.assignment_id));
459 commit;
460 end loop;
461 close csr_get_asg;
462
463 END;
464
465 PROCEDURE pa_head_tx_ovr
466 (p_business_group in varchar2
467 ,p_curr_year in varchar2
468 )
469 IS
470
471 cursor csr_get_asg(p_start_day DATE,
472 p_business_group varchar2) is
473 select pct.*
474 from pay_us_emp_city_tax_rules_f pct,
475 pay_state_rules psr,
476 pay_us_states pus
477 where psr.head_tax_period = 'A'
478 and psr.state_code = pus.state_abbrev
479 and pus.state_code = pct.state_code
480 and pct.ht_exempt is not null
481 and p_start_day between (pct.effective_start_date+1) and pct.effective_end_date
482 and pct.business_group_id = to_number(p_business_group) ;
483
484
485 l_city_rec PAY_US_EMP_CITY_TAX_RULES_F%rowtype;
486 l_last_day DATE;
487 l_start_day DATE;
488 l_last_year VARCHAR2(4);
489 l_curr_year VARCHAR2(4);
490
491 BEGIN
492 --hr_utility.trace_on(null,'oracle');
493 /* Get the assignments which have PA head Tax Exemption */
494 l_curr_year := p_curr_year;
495 hr_utility.trace('l_curr_year '||l_curr_year);
496
497 l_last_year := to_number(l_curr_year) - 1;
498
499 hr_utility.trace('l_last_year '||l_last_year);
500
501 l_last_day := to_date('12/31/'||l_last_year,'MM/DD/YYYY');
502 l_start_day := to_date('01/01/'||l_curr_year,'MM/DD/YYYY');
503
504
505 hr_utility.trace('l_last_day '||to_char(l_last_day));
506 hr_utility.trace('l_start_day '||to_char(l_start_day));
507
508 open csr_get_asg(l_start_day , p_business_group);
509
510 hr_utility.trace('Updating the city tax records ...');
511
512 loop
513
514 fetch csr_get_asg into l_city_rec;
515
516 exit when csr_get_asg%NOTFOUND;
517
518 /* End date the city tax record as of /12/31/(input year-1) */
519
520
521 update PAY_US_EMP_CITY_TAX_RULES_F
522 set effective_end_date = l_last_day --to_date('12/31/'||end_year,'MM/DD/YYYY')
523 where emp_city_tax_rule_id = l_city_rec.emp_city_tax_rule_id
524 and assignment_id = l_city_rec.assignment_id
525 and effective_start_date = l_city_rec.effective_start_date
526 and effective_end_date = l_city_rec.effective_end_date
527 and ht_exempt is not null;
528
529 /* Null out the PA Head Tax exemption as of 01/01/1999 */
530 hr_utility.trace('Inserting Assignment : ' ||to_char(l_city_rec.assignment_id));
531
532 hr_utility.trace('Inserting Assignment Start Date : ' ||to_char(l_start_day));
533
534 hr_utility.trace('Inserting Assignment End Date : ' ||to_char(l_city_rec.effective_end_date));
535
536
537
538 insert into PAY_US_EMP_CITY_TAX_RULES_F
539 (
540 EMP_CITY_TAX_RULE_ID,
541 EFFECTIVE_START_DATE,
542 EFFECTIVE_END_DATE ,
543 ASSIGNMENT_ID ,
544 STATE_CODE ,
545 COUNTY_CODE ,
546 CITY_CODE ,
547 BUSINESS_GROUP_ID ,
548 ADDITIONAL_WA_RATE ,
549 FILING_STATUS_CODE ,
550 JURISDICTION_CODE ,
551 LIT_ADDITIONAL_TAX ,
552 LIT_OVERRIDE_AMOUNT ,
553 LIT_OVERRIDE_RATE ,
554 WITHHOLDING_ALLOWANCES ,
555 LIT_EXEMPT ,
556 SD_EXEMPT ,
557 HT_EXEMPT ,
558 SCHOOL_DISTRICT_CODE ,
559 LAST_UPDATE_DATE ,
560 LAST_UPDATED_BY ,
561 LAST_UPDATE_LOGIN ,
562 CREATED_BY ,
563 CREATION_DATE ,
564 OBJECT_VERSION_NUMBER ,
565 ATTRIBUTE_CATEGORY ,
566 ATTRIBUTE1 ,
567 ATTRIBUTE2 ,
568 ATTRIBUTE3 ,
569 ATTRIBUTE4 ,
570 ATTRIBUTE5 ,
571 ATTRIBUTE6 ,
572 ATTRIBUTE7 ,
573 ATTRIBUTE8 ,
574 ATTRIBUTE9 ,
575 ATTRIBUTE10 ,
576 ATTRIBUTE11 ,
577 ATTRIBUTE12 ,
578 ATTRIBUTE13 ,
579 ATTRIBUTE14 ,
580 ATTRIBUTE15 ,
581 ATTRIBUTE16 ,
582 ATTRIBUTE17 ,
583 ATTRIBUTE18 ,
584 ATTRIBUTE19 ,
585 ATTRIBUTE20 ,
586 ATTRIBUTE21 ,
587 ATTRIBUTE22 ,
588 ATTRIBUTE23 ,
589 ATTRIBUTE24 ,
590 ATTRIBUTE25 ,
591 ATTRIBUTE26 ,
592 ATTRIBUTE27 ,
593 ATTRIBUTE28 ,
594 ATTRIBUTE29 ,
595 ATTRIBUTE30 ,
596 CTY_INFORMATION_CATEGORY ,
597 CTY_INFORMATION1 ,
598 CTY_INFORMATION2 ,
599 CTY_INFORMATION3 ,
600 CTY_INFORMATION4 ,
601 CTY_INFORMATION5 ,
602 CTY_INFORMATION6 ,
603 CTY_INFORMATION7 ,
604 CTY_INFORMATION8 ,
605 CTY_INFORMATION9 ,
606 CTY_INFORMATION10 ,
607 CTY_INFORMATION11 ,
608 CTY_INFORMATION12 ,
609 CTY_INFORMATION13 ,
610 CTY_INFORMATION14 ,
611 CTY_INFORMATION15 ,
612 CTY_INFORMATION16 ,
613 CTY_INFORMATION17 ,
614 CTY_INFORMATION18 ,
615 CTY_INFORMATION19 ,
616 CTY_INFORMATION20 ,
617 CTY_INFORMATION21 ,
618 CTY_INFORMATION22 ,
619 CTY_INFORMATION23 ,
620 CTY_INFORMATION24 ,
621 CTY_INFORMATION25 ,
622 CTY_INFORMATION26 ,
623 CTY_INFORMATION27 ,
624 CTY_INFORMATION28 ,
625 CTY_INFORMATION29 ,
626 CTY_INFORMATION30
627 )
628 values
629 (
630 l_city_rec.emp_city_tax_rule_id,
631 l_start_day,
632 l_city_rec.effective_end_date,
633 l_city_rec.ASSIGNMENT_ID ,
634 l_city_rec.STATE_CODE ,
635 l_city_rec.COUNTY_CODE ,
636 l_city_rec.CITY_CODE ,
637 l_city_rec.BUSINESS_GROUP_ID ,
638 l_city_rec.ADDITIONAL_WA_RATE ,
639 l_city_rec.FILING_STATUS_CODE ,
640 l_city_rec.JURISDICTION_CODE ,
641 l_city_rec.LIT_ADDITIONAL_TAX ,
642 l_city_rec.LIT_OVERRIDE_AMOUNT ,
643 l_city_rec.LIT_OVERRIDE_RATE ,
644 l_city_rec.WITHHOLDING_ALLOWANCES ,
645 l_city_rec.LIT_EXEMPT ,
646 l_city_rec.SD_EXEMPT ,
647 NULL ,
648 l_city_rec.SCHOOL_DISTRICT_CODE ,
649 l_city_rec.LAST_UPDATE_DATE ,
650 l_city_rec.LAST_UPDATED_BY ,
651 l_city_rec.LAST_UPDATE_LOGIN ,
652 l_city_rec.CREATED_BY ,
653 l_city_rec.CREATION_DATE ,
654 l_city_rec.OBJECT_VERSION_NUMBER ,
655 l_city_rec.ATTRIBUTE_CATEGORY ,
656 l_city_rec.ATTRIBUTE1 ,
657 l_city_rec.ATTRIBUTE2 ,
658 l_city_rec.ATTRIBUTE3 ,
659 l_city_rec.ATTRIBUTE4 ,
660 l_city_rec.ATTRIBUTE5 ,
661 l_city_rec.ATTRIBUTE6 ,
662 l_city_rec.ATTRIBUTE7 ,
663 l_city_rec.ATTRIBUTE8 ,
664 l_city_rec.ATTRIBUTE9 ,
665 l_city_rec.ATTRIBUTE10 ,
666 l_city_rec.ATTRIBUTE11 ,
667 l_city_rec.ATTRIBUTE12 ,
668 l_city_rec.ATTRIBUTE13 ,
669 l_city_rec.ATTRIBUTE14 ,
670 l_city_rec.ATTRIBUTE15 ,
671 l_city_rec.ATTRIBUTE16 ,
672 l_city_rec.ATTRIBUTE17 ,
673 l_city_rec.ATTRIBUTE18 ,
674 l_city_rec.ATTRIBUTE19 ,
675 l_city_rec.ATTRIBUTE20 ,
676 l_city_rec.ATTRIBUTE21 ,
677 l_city_rec.ATTRIBUTE22 ,
678 l_city_rec.ATTRIBUTE23 ,
679 l_city_rec.ATTRIBUTE24 ,
680 l_city_rec.ATTRIBUTE25 ,
681 l_city_rec.ATTRIBUTE26 ,
682 l_city_rec.ATTRIBUTE27 ,
683 l_city_rec.ATTRIBUTE28 ,
684 l_city_rec.ATTRIBUTE29 ,
685 l_city_rec.ATTRIBUTE30 ,
686 l_city_rec.CTY_INFORMATION_CATEGORY ,
687 l_city_rec.CTY_INFORMATION1 ,
688 l_city_rec.CTY_INFORMATION2 ,
689 l_city_rec.CTY_INFORMATION3 ,
690 l_city_rec.CTY_INFORMATION4 ,
691 l_city_rec.CTY_INFORMATION5 ,
692 l_city_rec.CTY_INFORMATION6 ,
693 l_city_rec.CTY_INFORMATION7 ,
694 l_city_rec.CTY_INFORMATION8 ,
695 l_city_rec.CTY_INFORMATION9 ,
696 l_city_rec.CTY_INFORMATION10 ,
697 l_city_rec.CTY_INFORMATION11 ,
698 l_city_rec.CTY_INFORMATION12 ,
699 l_city_rec.CTY_INFORMATION13 ,
700 l_city_rec.CTY_INFORMATION14 ,
701 l_city_rec.CTY_INFORMATION15 ,
702 l_city_rec.CTY_INFORMATION16 ,
703 l_city_rec.CTY_INFORMATION17 ,
704 l_city_rec.CTY_INFORMATION18 ,
705 l_city_rec.CTY_INFORMATION19 ,
706 l_city_rec.CTY_INFORMATION20 ,
707 l_city_rec.CTY_INFORMATION21 ,
708 l_city_rec.CTY_INFORMATION22 ,
709 l_city_rec.CTY_INFORMATION23 ,
710 l_city_rec.CTY_INFORMATION24 ,
711 l_city_rec.CTY_INFORMATION25 ,
712 l_city_rec.CTY_INFORMATION26 ,
713 l_city_rec.CTY_INFORMATION27 ,
714 l_city_rec.CTY_INFORMATION28 ,
715 l_city_rec.CTY_INFORMATION29 ,
716 l_city_rec.CTY_INFORMATION30
717 );
718
719
720 hr_utility.trace('Updated Assignment : ' ||
721 to_char(l_city_rec.assignment_id));
722 commit;
723 end loop;
724 close csr_get_asg;
725
726 END;
727
728
729
730 PROCEDURE fed_eic_filing_status_ovr
731 (p_business_group in varchar2
732 ,p_curr_year in varchar2
733 )
734 IS
735
736 cursor csr_get_asg(p_start_day DATE,
737 p_business_group varchar2) is
738 select *
739 from pay_us_emp_fed_tax_rules_f pft
740 where pft.eic_filing_status_code <> 0
741 and p_start_day between (pft.effective_start_date+1) and pft.effective_end_date
742 and pft.business_group_id = to_number(p_business_group);
743
744
745 l_fed_rec PAY_US_EMP_FED_TAX_RULES_F%rowtype;
746 l_last_day DATE;
747 l_start_day DATE;
748 l_last_year VARCHAR2(4);
749 l_curr_year VARCHAR2(4);
750
751 BEGIN
752 --hr_utility.trace_on(null,'oracle');
753 /* Get the assignments which have Fed EIC */
754 l_curr_year := p_curr_year;
755 hr_utility.trace('l_curr_year '||l_curr_year);
756
757 l_last_year := to_number(l_curr_year) - 1;
758
759 hr_utility.trace('l_last_year '||l_last_year);
760
761 l_last_day := to_date('12/31/'||l_last_year,'MM/DD/YYYY');
762 l_start_day := to_date('01/01/'||l_curr_year,'MM/DD/YYYY');
763
764
765 hr_utility.trace('l_last_day '||to_char(l_last_day));
766 hr_utility.trace('l_start_day '||to_char(l_start_day));
767
768 open csr_get_asg(l_start_day , p_business_group);
769
770 hr_utility.trace('Updating the fed tax records ...');
771
772 loop
773
774 fetch csr_get_asg into l_fed_rec;
775
776 exit when csr_get_asg%NOTFOUND;
777
778 /* End date the fed tax record as of /12/31/(input year-1) */
779
780
781 update PAY_US_EMP_FED_TAX_RULES_F
782 set effective_end_date = l_last_day --to_date('12/31/'||end_year,'MM/DD/YYYY')
783 where assignment_id = l_fed_rec.assignment_id
784 and effective_start_date = l_fed_rec.effective_start_date
785 and effective_end_date = l_fed_rec.effective_end_date
786 and eic_filing_status_code <> 0;
787
788 /* Null out the FED EIC of 01/01/1999 */
789 hr_utility.trace('Inserting Assignment : ' ||to_char(l_fed_rec.assignment_id));
790
791 hr_utility.trace('Inserting Assignment Start Date : ' ||to_char(l_start_day));
792
793 hr_utility.trace('Inserting Assignment End Date : ' ||to_char(l_fed_rec.effective_end_date));
794
795
796
797 /* Insert Stmt for FED Tax Rules will come here */
798 insert into pay_us_emp_fed_tax_rules_f
799 (
800 EMP_FED_TAX_RULE_ID ,
801 EFFECTIVE_START_DATE ,
802 EFFECTIVE_END_DATE ,
803 ASSIGNMENT_ID ,
804 SUI_STATE_CODE ,
805 SUI_JURISDICTION_CODE ,
806 BUSINESS_GROUP_ID ,
807 ADDITIONAL_WA_AMOUNT ,
808 FILING_STATUS_CODE ,
809 FIT_OVERRIDE_AMOUNT ,
810 FIT_OVERRIDE_RATE ,
811 WITHHOLDING_ALLOWANCES ,
812 CUMULATIVE_TAXATION ,
813 EIC_FILING_STATUS_CODE ,
814 FIT_ADDITIONAL_TAX ,
815 FIT_EXEMPT ,
816 FUTA_TAX_EXEMPT ,
817 MEDICARE_TAX_EXEMPT ,
818 SS_TAX_EXEMPT ,
819 STATUTORY_EMPLOYEE ,
820 W2_FILED_YEAR ,
821 SUPP_TAX_OVERRIDE_RATE ,
822 EXCESSIVE_WA_REJECT_DATE ,
823 LAST_UPDATE_DATE ,
824 LAST_UPDATED_BY ,
825 LAST_UPDATE_LOGIN ,
826 CREATED_BY ,
827 CREATION_DATE ,
828 OBJECT_VERSION_NUMBER ,
829 ATTRIBUTE_CATEGORY ,
830 ATTRIBUTE1 ,
831 ATTRIBUTE2 ,
832 ATTRIBUTE3 ,
833 ATTRIBUTE4 ,
834 ATTRIBUTE5 ,
835 ATTRIBUTE6 ,
836 ATTRIBUTE7 ,
837 ATTRIBUTE8 ,
838 ATTRIBUTE9 ,
839 ATTRIBUTE10 ,
840 ATTRIBUTE11 ,
841 ATTRIBUTE12 ,
842 ATTRIBUTE13 ,
843 ATTRIBUTE14 ,
844 ATTRIBUTE15 ,
845 ATTRIBUTE16 ,
846 ATTRIBUTE17 ,
847 ATTRIBUTE18 ,
848 ATTRIBUTE19 ,
849 ATTRIBUTE20 ,
850 ATTRIBUTE21 ,
851 ATTRIBUTE22 ,
852 ATTRIBUTE23 ,
853 ATTRIBUTE24 ,
854 ATTRIBUTE25 ,
855 ATTRIBUTE26 ,
856 ATTRIBUTE27 ,
857 ATTRIBUTE28 ,
858 ATTRIBUTE29 ,
859 ATTRIBUTE30 ,
860 FED_INFORMATION_CATEGORY ,
861 FED_INFORMATION1 ,
862 FED_INFORMATION2 ,
863 FED_INFORMATION3 ,
864 FED_INFORMATION4 ,
865 FED_INFORMATION5 ,
866 FED_INFORMATION6 ,
867 FED_INFORMATION7 ,
868 FED_INFORMATION8 ,
869 FED_INFORMATION9 ,
870 FED_INFORMATION10 ,
871 FED_INFORMATION11 ,
872 FED_INFORMATION12 ,
873 FED_INFORMATION13 ,
874 FED_INFORMATION14 ,
875 FED_INFORMATION15 ,
876 FED_INFORMATION16 ,
877 FED_INFORMATION17 ,
878 FED_INFORMATION18 ,
879 FED_INFORMATION19 ,
880 FED_INFORMATION20 ,
881 FED_INFORMATION21 ,
882 FED_INFORMATION22 ,
883 FED_INFORMATION23 ,
884 FED_INFORMATION24 ,
885 FED_INFORMATION25 ,
886 FED_INFORMATION26 ,
887 FED_INFORMATION27 ,
888 FED_INFORMATION28 ,
889 FED_INFORMATION29 ,
890 FED_INFORMATION30
891 )
892 values
893 (
894 l_fed_rec.EMP_FED_TAX_RULE_ID ,
895 l_start_day,
896 l_fed_rec.effective_end_date,
897 l_fed_rec.ASSIGNMENT_ID ,
898 l_fed_rec.SUI_STATE_CODE ,
899 l_fed_rec.SUI_JURISDICTION_CODE ,
900 l_fed_rec.BUSINESS_GROUP_ID ,
901 l_fed_rec.ADDITIONAL_WA_AMOUNT ,
902 l_fed_rec.FILING_STATUS_CODE ,
903 l_fed_rec.FIT_OVERRIDE_AMOUNT ,
904 l_fed_rec.FIT_OVERRIDE_RATE ,
905 l_fed_rec.WITHHOLDING_ALLOWANCES ,
906 l_fed_rec.CUMULATIVE_TAXATION ,
907 0 ,
908 l_fed_rec.FIT_ADDITIONAL_TAX ,
909 l_fed_rec.FIT_EXEMPT ,
910 l_fed_rec.FUTA_TAX_EXEMPT ,
911 l_fed_rec.MEDICARE_TAX_EXEMPT ,
912 l_fed_rec.SS_TAX_EXEMPT ,
913 l_fed_rec.STATUTORY_EMPLOYEE ,
914 l_fed_rec.W2_FILED_YEAR ,
915 l_fed_rec.SUPP_TAX_OVERRIDE_RATE ,
916 l_fed_rec.EXCESSIVE_WA_REJECT_DATE ,
917 l_fed_rec.LAST_UPDATE_DATE ,
918 l_fed_rec.LAST_UPDATED_BY ,
919 l_fed_rec.LAST_UPDATE_LOGIN ,
920 l_fed_rec.CREATED_BY ,
921 l_fed_rec.CREATION_DATE ,
922 l_fed_rec.OBJECT_VERSION_NUMBER ,
923 l_fed_rec.ATTRIBUTE_CATEGORY ,
924 l_fed_rec.ATTRIBUTE1 ,
925 l_fed_rec.ATTRIBUTE2 ,
926 l_fed_rec.ATTRIBUTE3 ,
927 l_fed_rec.ATTRIBUTE4 ,
928 l_fed_rec.ATTRIBUTE5 ,
929 l_fed_rec.ATTRIBUTE6 ,
930 l_fed_rec.ATTRIBUTE7 ,
931 l_fed_rec.ATTRIBUTE8 ,
932 l_fed_rec.ATTRIBUTE9 ,
933 l_fed_rec.ATTRIBUTE10 ,
934 l_fed_rec.ATTRIBUTE11 ,
935 l_fed_rec.ATTRIBUTE12 ,
936 l_fed_rec.ATTRIBUTE13 ,
937 l_fed_rec.ATTRIBUTE14 ,
938 l_fed_rec.ATTRIBUTE15 ,
939 l_fed_rec.ATTRIBUTE16 ,
940 l_fed_rec.ATTRIBUTE17 ,
941 l_fed_rec.ATTRIBUTE18 ,
942 l_fed_rec.ATTRIBUTE19 ,
943 l_fed_rec.ATTRIBUTE20 ,
944 l_fed_rec.ATTRIBUTE21 ,
945 l_fed_rec.ATTRIBUTE22 ,
946 l_fed_rec.ATTRIBUTE23 ,
947 l_fed_rec.ATTRIBUTE24 ,
948 l_fed_rec.ATTRIBUTE25 ,
949 l_fed_rec.ATTRIBUTE26 ,
950 l_fed_rec.ATTRIBUTE27 ,
951 l_fed_rec.ATTRIBUTE28 ,
952 l_fed_rec.ATTRIBUTE29 ,
953 l_fed_rec.ATTRIBUTE30 ,
954 l_fed_rec.FED_INFORMATION_CATEGORY ,
955 l_fed_rec.FED_INFORMATION1 ,
956 l_fed_rec.FED_INFORMATION2 ,
957 l_fed_rec.FED_INFORMATION3 ,
958 l_fed_rec.FED_INFORMATION4 ,
959 l_fed_rec.FED_INFORMATION5 ,
960 l_fed_rec.FED_INFORMATION6 ,
961 l_fed_rec.FED_INFORMATION7 ,
962 l_fed_rec.FED_INFORMATION8 ,
963 l_fed_rec.FED_INFORMATION9 ,
964 l_fed_rec.FED_INFORMATION10 ,
965 l_fed_rec.FED_INFORMATION11 ,
966 l_fed_rec.FED_INFORMATION12 ,
967 l_fed_rec.FED_INFORMATION13 ,
968 l_fed_rec.FED_INFORMATION14 ,
969 l_fed_rec.FED_INFORMATION15 ,
970 l_fed_rec.FED_INFORMATION16 ,
971 l_fed_rec.FED_INFORMATION17 ,
972 l_fed_rec.FED_INFORMATION18 ,
973 l_fed_rec.FED_INFORMATION19 ,
974 l_fed_rec.FED_INFORMATION20 ,
975 l_fed_rec.FED_INFORMATION21 ,
976 l_fed_rec.FED_INFORMATION22 ,
977 l_fed_rec.FED_INFORMATION23 ,
978 l_fed_rec.FED_INFORMATION24 ,
979 l_fed_rec.FED_INFORMATION25 ,
980 l_fed_rec.FED_INFORMATION26 ,
981 l_fed_rec.FED_INFORMATION27 ,
982 l_fed_rec.FED_INFORMATION28 ,
983 l_fed_rec.FED_INFORMATION29 ,
984 l_fed_rec.FED_INFORMATION30
985 );
986
987
988 hr_utility.trace('Updated Assignment : ' ||
989 to_char(l_fed_rec.assignment_id));
990 commit;
991 end loop;
992 close csr_get_asg;
993
994 END;
995
996 PROCEDURE reset_overrides
997 (errbuf out nocopy varchar2
998 ,retcode out nocopy number
999 ,p_business_group in varchar2
1000 ,p_curr_year in varchar2
1001 ,p_clr_ind_add_ovr in varchar2
1002 ,p_clr_ind_eic in varchar2
1003 ,p_clr_sui_wb_ovr in varchar2
1004 ,p_clr_pa_head_tax in varchar2
1005 ,p_clr_fed_eic_filing_status in varchar2
1006 )
1007 IS
1008
1009 BEGIN
1010 --hr_utility.trace_on(null,'ORACLE');
1011 hr_utility.trace(p_business_group||p_curr_year||p_clr_ind_add_ovr||p_clr_ind_eic||p_clr_sui_wb_ovr||p_clr_pa_head_tax||p_clr_fed_eic_filing_status);
1012
1013
1014 /* Clear the Indiana Override Adderess/Location */
1015 IF p_clr_ind_add_ovr = 'Y' THEN
1016 pay_us_indiana.update_address(errbuf,retcode,p_business_group,p_curr_year);
1017 END IF;
1018 /* End Indiana Override Address /Location */
1019
1020 /* Clear Indiana EIC */
1021 IF p_clr_ind_eic = 'Y' THEN
1022 hr_utility.trace('Procedure ind_eic_ovr');
1023 ind_eic_ovr(p_business_group,p_curr_year);
1024 END IF;
1025 /* End Indiana EIC */
1026
1027
1028 /* Clear SUI Wage Base Override */
1029 IF p_clr_sui_wb_ovr = 'Y' THEN
1030 hr_utility.trace('Procedure sui_wb_ovr');
1031 sui_wb_ovr(p_business_group,p_curr_year);
1032 END IF;
1033 /* End SUI Wage Base Override */
1034
1035
1036 /*Clear PA Head tax exempt */
1037 IF p_clr_pa_head_tax ='Y' THEN
1038 hr_utility.trace('Procedure pa_head_tx_ovr');
1039 pa_head_tx_ovr(p_business_group,p_curr_year);
1040 END IF;
1041 /* End PS head tax exempt */
1042
1043
1044 /* Clear Federal EIC Filing status */
1045 IF p_clr_fed_eic_filing_status = 'Y' THEN
1046 hr_utility.trace('Procedure fed_eic_filing_status_ovr');
1047 fed_eic_filing_status_ovr(p_business_group,p_curr_year);
1048 END IF;
1049 /* End EIC filing status*/
1050
1051 END reset_overrides;
1052
1053 end pay_us_year_begin_process;