[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.5 2012/01/19 08:25:16 rpahune ship $ */
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 06-Oct-09 emunisek 115.2 8985595 Added new parameter p_clr_wis_eic
34 to the procedure reset_overrides
35 to enable the "Clear Wisconsin EIC"
36 in "Year Begin Process".An overloaded
37 function was created with new parameter
38 to maintain integrity of any other
39 reference to this procedure.
40 Added procedure wis_eic_ovr to clear
41 the Wisconsin EIC through the Year
42 Begin Process.
43 The wis_eic_ovr is similar to wis_ind_ovr
44 except that this will clear "EIC Filing Status"
45 and "EIC Qualifying Children" for Wisconsin
46 where as only "EIC Filing Status" was cleared
47 for Indiana.
48 8-Dec-09 parusia 115.3 9157658 Updated the last_update_date of the newly created
49 record to represent the sysdate.
50 8-Jan-10 parusia 115.4 9157658 Added WHO columns for sui_wb_ovr procedure
51 5-Oct-11 nvelaga 115.5 11056284 Modified the sui_wb_ovr procedure.
52 Included the missing Attribute and Information
53 columns in the insert statement.
54 */
55
56 PROCEDURE sui_wb_ovr
57 (p_business_group in varchar2
58 ,p_curr_year in varchar2
59 )
60 IS
61
62 cursor csr_get_asg(p_start_day DATE,
63 p_business_group varchar2) is
64 select *
65 from pay_us_emp_state_tax_rules_f pst
66 where pst.sui_wage_base_override_amount is not null
67 and p_start_day between (pst.effective_start_date+1) and pst.effective_end_date
68 and pst.business_group_id = to_number(p_business_group) ;
69
70 l_state_rec PAY_US_EMP_STATE_TAX_RULES_F%rowtype;
71 l_last_day DATE;
72 l_start_day DATE;
73 l_last_year VARCHAR2(4);
74 l_curr_year VARCHAR2(4);
75
76 BEGIN
77 --hr_utility.trace_on(null,'oracle');
78 /* Get the assignments which have non zero SUI WAGE BASE OVERRIDE AMOUNT */
79 l_curr_year := p_curr_year;
80 hr_utility.trace('l_curr_year '||l_curr_year);
81
82 l_last_year := to_number(l_curr_year) - 1;
83
84 hr_utility.trace('l_last_year '||l_last_year);
85
86 l_last_day := to_date('12/31/'||l_last_year,'MM/DD/YYYY');
87 l_start_day := to_date('01/01/'||l_curr_year,'MM/DD/YYYY');
88
89
90 hr_utility.trace('l_last_day '||to_char(l_last_day));
91 hr_utility.trace('l_start_day '||to_char(l_start_day));
92
93 open csr_get_asg(l_start_day , p_business_group);
94
95 hr_utility.trace('Updating the state tax records ...');
96
97 loop
98
99 fetch csr_get_asg into l_state_rec;
100
101 exit when csr_get_asg%NOTFOUND;
102
103 hr_utility.trace('Updating Assignment : ' ||
104 to_char(l_state_rec.assignment_id));
105 hr_utility.trace(to_char(l_state_rec.emp_state_tax_rule_id));
106 hr_utility.trace(to_char(l_state_rec.effective_end_date));
107 hr_utility.trace(to_char( l_state_rec.assignment_id));
108 hr_utility.trace(l_state_rec.state_code);
109 hr_utility.trace(l_state_rec.jurisdiction_code);
110 hr_utility.trace(to_char(l_state_rec.business_group_id));
111 hr_utility.trace(to_char( l_state_rec.additional_wa_amount));
112 hr_utility.trace( l_state_rec.filing_status_code);
113 hr_utility.trace(to_char( l_state_rec.remainder_percent));
114 hr_utility.trace(to_char( l_state_rec.secondary_wa));
115 hr_utility.trace(to_char( l_state_rec.sit_additional_tax));
116 hr_utility.trace(to_char( l_state_rec.sit_override_amount));
117 hr_utility.trace(to_char( l_state_rec.sit_override_rate));
118 hr_utility.trace(to_char( l_state_rec.withholding_allowances));
119
120 /* End date the state tax record as of /12/31/(input year-1) */
121
122
123 update PAY_US_EMP_STATE_TAX_RULES_F
124 set effective_end_date = l_last_day --to_date('12/31/'||end_year,'MM/DD/YYYY')
125 where emp_state_tax_rule_id = l_state_rec.emp_state_tax_rule_id
126 and assignment_id = l_state_rec.assignment_id
127 and effective_start_date = l_state_rec.effective_start_date
128 and effective_end_date = l_state_rec.effective_end_date
129 and sui_wage_base_override_amount is not null;
130
131 /* Null out the SUI WAGE BASE OVERRIDE AMOUNT as of 01/01/1999 */
132 hr_utility.trace('Inserting Assignment : ' ||to_char(l_state_rec.assignment_id));
133
134 hr_utility.trace('Inserting Assignment Start Date : ' ||to_char(l_start_day));
135
136 hr_utility.trace('Inserting Assignment End Date : ' ||to_char(l_state_rec.effective_end_date));
137
138 /*We need to reset INDIANA EIC Also */
139 /* IF (l_state_rec.sta_information_category <> 'IN' AND l_state_rec.state_code <> '15') THEN
140 l_ind_eic := l_state_rec.sta_information1;
141 END IF; will write a new procedure for this too*/
142
143 /* Added Attribute and Information columns in insert statement for bug#11056284 */
144 insert into PAY_US_EMP_STATE_TAX_RULES_F
145 (emp_state_tax_rule_id,
146 effective_start_date,
147 effective_end_date,
148 assignment_id,
149 state_code,
150 jurisdiction_code,
151 business_group_id,
152 additional_wa_amount,
153 filing_status_code,
154 remainder_percent,
155 secondary_wa,
156 sit_additional_tax,
157 sit_override_amount,
158 sit_override_rate,
159 withholding_allowances,
160 excessive_wa_reject_date,
161 sdi_exempt,
162 sit_exempt,
163 sit_optional_calc_ind,
164 state_non_resident_cert,
165 sui_exempt,
166 wc_exempt,
167 sui_wage_base_override_amount,
168 supp_tax_override_rate,
169 last_update_date,
170 last_updated_by,
171 last_update_login,
172 created_by,
173 creation_date,
174 object_version_number,
175 attribute_category,
176 attribute1,
177 attribute2,
178 attribute3,
179 attribute4,
180 attribute5,
181 attribute6,
182 attribute7,
183 attribute8,
184 attribute9,
185 attribute10,
186 attribute11,
187 attribute12,
188 attribute13,
189 attribute14,
190 attribute15,
191 attribute16,
192 attribute17,
193 attribute18,
194 attribute19,
195 attribute20,
196 attribute21,
197 attribute22,
198 attribute23,
199 attribute24,
200 attribute25,
201 attribute26,
202 attribute27,
203 attribute28,
204 attribute29,
205 attribute30,
206 sta_information_category,
207 sta_information1,
208 sta_information2,
209 sta_information3,
210 sta_information4,
211 sta_information5,
212 sta_information6,
213 sta_information7,
214 sta_information8,
215 sta_information9,
216 sta_information10,
217 sta_information11,
218 sta_information12,
219 sta_information13,
220 sta_information14,
221 sta_information15,
222 sta_information16,
223 sta_information17,
224 sta_information18,
225 sta_information19,
226 sta_information20,
227 sta_information21,
228 sta_information22,
229 sta_information23,
230 sta_information24,
231 sta_information25,
232 sta_information26,
233 sta_information27,
234 sta_information28,
235 sta_information29,
236 sta_information30)
237 values
238 (l_state_rec.emp_state_tax_rule_id,
239 l_start_day,
240 l_state_rec.effective_end_date,
241 l_state_rec.assignment_id,
242 l_state_rec.state_code,
243 l_state_rec.jurisdiction_code,
244 l_state_rec.business_group_id,
245 l_state_rec.additional_wa_amount,
246 l_state_rec.filing_status_code,
247 l_state_rec.remainder_percent,
248 l_state_rec.secondary_wa,
249 l_state_rec.sit_additional_tax,
250 l_state_rec.sit_override_amount,
251 l_state_rec.sit_override_rate,
252 l_state_rec.withholding_allowances,
253 l_state_rec.excessive_wa_reject_date,
254 l_state_rec.sdi_exempt,
255 l_state_rec.sit_exempt,
256 l_state_rec.sit_optional_calc_ind,
257 l_state_rec.state_non_resident_cert,
258 l_state_rec.sui_exempt,
259 l_state_rec.wc_exempt,
260 null,
261 l_state_rec.supp_tax_override_rate,
262 null , -- bug 9157658
263 l_state_rec.last_updated_by,
264 l_state_rec.last_update_login,
265 l_state_rec.created_by,
266 l_state_rec.creation_date,
267 l_state_rec.object_version_number,
268 l_state_rec.attribute_category,
269 l_state_rec.attribute1,
270 l_state_rec.attribute2,
271 l_state_rec.attribute3,
272 l_state_rec.attribute4,
273 l_state_rec.attribute5,
274 l_state_rec.attribute6,
275 l_state_rec.attribute7,
276 l_state_rec.attribute8,
277 l_state_rec.attribute9,
278 l_state_rec.attribute10,
279 l_state_rec.attribute11,
280 l_state_rec.attribute12,
281 l_state_rec.attribute13,
282 l_state_rec.attribute14,
283 l_state_rec.attribute15,
284 l_state_rec.attribute16,
285 l_state_rec.attribute17,
286 l_state_rec.attribute18,
287 l_state_rec.attribute19,
288 l_state_rec.attribute20,
289 l_state_rec.attribute21,
290 l_state_rec.attribute22,
291 l_state_rec.attribute23,
292 l_state_rec.attribute24,
293 l_state_rec.attribute25,
294 l_state_rec.attribute26,
295 l_state_rec.attribute27,
296 l_state_rec.attribute28,
297 l_state_rec.attribute29,
298 l_state_rec.attribute30,
299 l_state_rec.sta_information_category,
300 l_state_rec.sta_information1,
301 l_state_rec.sta_information2,
302 l_state_rec.sta_information3,
303 l_state_rec.sta_information4,
304 l_state_rec.sta_information5,
305 l_state_rec.sta_information6,
306 l_state_rec.sta_information7,
307 l_state_rec.sta_information8,
308 l_state_rec.sta_information9,
309 l_state_rec.sta_information10,
310 l_state_rec.sta_information11,
311 l_state_rec.sta_information12,
312 l_state_rec.sta_information13,
313 l_state_rec.sta_information14,
314 l_state_rec.sta_information15,
315 l_state_rec.sta_information16,
316 l_state_rec.sta_information17,
317 l_state_rec.sta_information18,
318 l_state_rec.sta_information19,
319 l_state_rec.sta_information20,
320 l_state_rec.sta_information21,
321 l_state_rec.sta_information22,
322 l_state_rec.sta_information23,
323 l_state_rec.sta_information24,
324 l_state_rec.sta_information25,
325 l_state_rec.sta_information26,
326 l_state_rec.sta_information27,
327 l_state_rec.sta_information28,
328 l_state_rec.sta_information29,
329 l_state_rec.sta_information30) ;
330
331 hr_utility.trace('Updated Assignment : ' ||
332 to_char(l_state_rec.assignment_id));
333 commit;
334 end loop;
335 close csr_get_asg;
336
337 END;
338
339
340 PROCEDURE ind_eic_ovr
341 (p_business_group in varchar2
342 ,p_curr_year in varchar2
343 )
344 IS
345
346 cursor csr_get_asg(p_start_day DATE,
347 p_business_group varchar2) is
348 select *
349 from pay_us_emp_state_tax_rules_f pst
350 where pst.sta_information1 is not null
351 and p_start_day between (pst.effective_start_date+1) and pst.effective_end_date
352 and sta_information_category = 'IN'
353 and state_code = '15'
354 and pst.business_group_id = to_number(p_business_group);
355
356 l_state_rec PAY_US_EMP_STATE_TAX_RULES_F%rowtype;
357 l_last_day DATE;
358 l_start_day DATE;
359 l_last_year VARCHAR2(4);
360 l_curr_year VARCHAR2(4);
361
362 BEGIN
363 --hr_utility.trace_on(null,'oracle');
364 /* Get the assignments which have non zero SUI WAGE BASE OVERRIDE AMOUNT */
365 l_curr_year := p_curr_year;
366 hr_utility.trace('l_curr_year '||l_curr_year);
367
368 l_last_year := to_number(l_curr_year) - 1;
369
370 hr_utility.trace('l_last_year '||l_last_year);
371
372 l_last_day := to_date('12/31/'||l_last_year,'MM/DD/YYYY');
373 l_start_day := to_date('01/01/'||l_curr_year,'MM/DD/YYYY');
374
375
376 hr_utility.trace('l_last_day '||to_char(l_last_day));
377 hr_utility.trace('l_start_day '||to_char(l_start_day));
378
379 open csr_get_asg(l_start_day , p_business_group);
380
381 hr_utility.trace('Updating the state tax records ...');
382
383 loop
384
385 fetch csr_get_asg into l_state_rec;
386
387 exit when csr_get_asg%NOTFOUND;
388
389 hr_utility.trace('Updating Assignment : ' ||
390 to_char(l_state_rec.assignment_id));
391 hr_utility.trace(to_char(l_state_rec.emp_state_tax_rule_id));
392 hr_utility.trace(to_char(l_state_rec.effective_end_date));
393 hr_utility.trace(to_char( l_state_rec.assignment_id));
394 hr_utility.trace(l_state_rec.state_code);
395 hr_utility.trace(l_state_rec.jurisdiction_code);
396 hr_utility.trace(to_char(l_state_rec.business_group_id));
397 hr_utility.trace(to_char( l_state_rec.additional_wa_amount));
398 hr_utility.trace( l_state_rec.filing_status_code);
399 hr_utility.trace(to_char( l_state_rec.remainder_percent));
400 hr_utility.trace(to_char( l_state_rec.secondary_wa));
401 hr_utility.trace(to_char( l_state_rec.sit_additional_tax));
402 hr_utility.trace(to_char( l_state_rec.sit_override_amount));
403 hr_utility.trace(to_char( l_state_rec.sit_override_rate));
404 hr_utility.trace(to_char( l_state_rec.withholding_allowances));
405
406 /* End date the state tax record as of /12/31/(input year-1) */
407
408
409 update PAY_US_EMP_STATE_TAX_RULES_F
410 set effective_end_date = l_last_day --to_date('12/31/'||end_year,'MM/DD/YYYY')
411 where emp_state_tax_rule_id = l_state_rec.emp_state_tax_rule_id
412 and assignment_id = l_state_rec.assignment_id
413 and effective_start_date = l_state_rec.effective_start_date
414 and effective_end_date = l_state_rec.effective_end_date
415 and sta_information1 is not null;
416
417 /* Null out the Indiana EIC as of 01/01/1999 */
418 hr_utility.trace('Inserting Assignment : ' ||to_char(l_state_rec.assignment_id));
419
420 hr_utility.trace('Inserting Assignment Start Date : ' ||to_char(l_start_day));
421
422 hr_utility.trace('Inserting Assignment End Date : ' ||to_char(l_state_rec.effective_end_date));
423
424 insert into PAY_US_EMP_STATE_TAX_RULES_F
425 (
426 EMP_STATE_TAX_RULE_ID,
427 EFFECTIVE_START_DATE ,
428 EFFECTIVE_END_DATE ,
429 ASSIGNMENT_ID ,
430 STATE_CODE ,
431 JURISDICTION_CODE ,
432 BUSINESS_GROUP_ID ,
433 ADDITIONAL_WA_AMOUNT ,
434 FILING_STATUS_CODE ,
435 REMAINDER_PERCENT ,
436 SECONDARY_WA ,
437 SIT_ADDITIONAL_TAX ,
438 SIT_OVERRIDE_AMOUNT ,
439 SIT_OVERRIDE_RATE ,
440 WITHHOLDING_ALLOWANCES ,
441 EXCESSIVE_WA_REJECT_DATE ,
442 SDI_EXEMPT ,
443 SIT_EXEMPT ,
444 SIT_OPTIONAL_CALC_IND ,
445 STATE_NON_RESIDENT_CERT ,
446 SUI_EXEMPT ,
447 WC_EXEMPT ,
448 SUI_WAGE_BASE_OVERRIDE_AMOUNT ,
449 SUPP_TAX_OVERRIDE_RATE ,
450 LAST_UPDATE_DATE ,
451 LAST_UPDATED_BY ,
452 LAST_UPDATE_LOGIN ,
453 CREATED_BY ,
454 CREATION_DATE ,
455 OBJECT_VERSION_NUMBER ,
456 ATTRIBUTE_CATEGORY ,
457 ATTRIBUTE1 ,
458 ATTRIBUTE2 ,
459 ATTRIBUTE3 ,
460 ATTRIBUTE4 ,
461 ATTRIBUTE5 ,
462 ATTRIBUTE6 ,
463 ATTRIBUTE7 ,
464 ATTRIBUTE8 ,
465 ATTRIBUTE9 ,
466 ATTRIBUTE10 ,
467 ATTRIBUTE11 ,
468 ATTRIBUTE12 ,
469 ATTRIBUTE13 ,
470 ATTRIBUTE14 ,
471 ATTRIBUTE15 ,
472 ATTRIBUTE16 ,
473 ATTRIBUTE17 ,
474 ATTRIBUTE18 ,
475 ATTRIBUTE19 ,
476 ATTRIBUTE20 ,
477 ATTRIBUTE21 ,
478 ATTRIBUTE22 ,
479 ATTRIBUTE23 ,
480 ATTRIBUTE24 ,
481 ATTRIBUTE25 ,
482 ATTRIBUTE26 ,
483 ATTRIBUTE27 ,
484 ATTRIBUTE28 ,
485 ATTRIBUTE29 ,
486 ATTRIBUTE30 ,
487 STA_INFORMATION_CATEGORY ,
488 STA_INFORMATION1 ,
489 STA_INFORMATION2 ,
490 STA_INFORMATION3 ,
491 STA_INFORMATION4 ,
492 STA_INFORMATION5 ,
493 STA_INFORMATION6 ,
494 STA_INFORMATION7 ,
495 STA_INFORMATION8 ,
496 STA_INFORMATION9 ,
497 STA_INFORMATION10 ,
498 STA_INFORMATION11 ,
499 STA_INFORMATION12 ,
500 STA_INFORMATION13 ,
501 STA_INFORMATION14 ,
502 STA_INFORMATION15 ,
503 STA_INFORMATION16 ,
504 STA_INFORMATION17 ,
505 STA_INFORMATION18 ,
506 STA_INFORMATION19 ,
507 STA_INFORMATION20 ,
508 STA_INFORMATION21 ,
509 STA_INFORMATION22 ,
510 STA_INFORMATION23 ,
511 STA_INFORMATION24 ,
512 STA_INFORMATION25 ,
513 STA_INFORMATION26 ,
514 STA_INFORMATION27 ,
515 STA_INFORMATION28 ,
516 STA_INFORMATION29 ,
517 STA_INFORMATION30 )
518 values
519 ( l_state_rec.EMP_STATE_TAX_RULE_ID,
520 l_start_day,
521 l_state_rec.EFFECTIVE_END_DATE,
522 l_state_rec.ASSIGNMENT_ID,
523 l_state_rec.STATE_CODE ,
524 l_state_rec.JURISDICTION_CODE ,
525 l_state_rec.BUSINESS_GROUP_ID ,
526 l_state_rec.ADDITIONAL_WA_AMOUNT ,
527 l_state_rec.FILING_STATUS_CODE ,
528 l_state_rec.REMAINDER_PERCENT ,
529 l_state_rec.SECONDARY_WA ,
530 l_state_rec.SIT_ADDITIONAL_TAX ,
531 l_state_rec.SIT_OVERRIDE_AMOUNT ,
532 l_state_rec.SIT_OVERRIDE_RATE ,
533 l_state_rec.WITHHOLDING_ALLOWANCES ,
534 l_state_rec.EXCESSIVE_WA_REJECT_DATE ,
535 l_state_rec.SDI_EXEMPT ,
536 l_state_rec.SIT_EXEMPT ,
537 l_state_rec.SIT_OPTIONAL_CALC_IND ,
538 l_state_rec.STATE_NON_RESIDENT_CERT ,
539 l_state_rec.SUI_EXEMPT ,
540 l_state_rec.WC_EXEMPT ,
541 l_state_rec.SUI_WAGE_BASE_OVERRIDE_AMOUNT ,
542 l_state_rec.SUPP_TAX_OVERRIDE_RATE ,
543 NULL , -- Bug 9157658
544 l_state_rec.LAST_UPDATED_BY ,
545 l_state_rec.LAST_UPDATE_LOGIN ,
546 l_state_rec.CREATED_BY ,
547 l_state_rec.CREATION_DATE ,
548 l_state_rec.OBJECT_VERSION_NUMBER ,
549 l_state_rec.ATTRIBUTE_CATEGORY ,
550 l_state_rec.ATTRIBUTE1 ,
551 l_state_rec.ATTRIBUTE2 ,
552 l_state_rec.ATTRIBUTE3 ,
553 l_state_rec.ATTRIBUTE4 ,
554 l_state_rec.ATTRIBUTE5 ,
555 l_state_rec.ATTRIBUTE6 ,
556 l_state_rec.ATTRIBUTE7 ,
557 l_state_rec.ATTRIBUTE8 ,
558 l_state_rec.ATTRIBUTE9 ,
559 l_state_rec.ATTRIBUTE10 ,
560 l_state_rec.ATTRIBUTE11 ,
561 l_state_rec.ATTRIBUTE12 ,
562 l_state_rec.ATTRIBUTE13 ,
563 l_state_rec.ATTRIBUTE14 ,
564 l_state_rec.ATTRIBUTE15 ,
565 l_state_rec.ATTRIBUTE16 ,
566 l_state_rec.ATTRIBUTE17 ,
567 l_state_rec.ATTRIBUTE18 ,
568 l_state_rec.ATTRIBUTE19 ,
569 l_state_rec.ATTRIBUTE20 ,
570 l_state_rec.ATTRIBUTE21 ,
571 l_state_rec.ATTRIBUTE22 ,
572 l_state_rec.ATTRIBUTE23 ,
573 l_state_rec.ATTRIBUTE24 ,
574 l_state_rec.ATTRIBUTE25 ,
575 l_state_rec.ATTRIBUTE26 ,
576 l_state_rec.ATTRIBUTE27 ,
577 l_state_rec.ATTRIBUTE28 ,
578 l_state_rec.ATTRIBUTE29 ,
579 l_state_rec.ATTRIBUTE30 ,
580 l_state_rec.STA_INFORMATION_CATEGORY ,
581 NULL ,
582 l_state_rec.STA_INFORMATION2 ,
583 l_state_rec.STA_INFORMATION3 ,
584 l_state_rec.STA_INFORMATION4 ,
585 l_state_rec.STA_INFORMATION5 ,
586 l_state_rec.STA_INFORMATION6 ,
587 l_state_rec.STA_INFORMATION7 ,
588 l_state_rec.STA_INFORMATION8 ,
589 l_state_rec.STA_INFORMATION9 ,
590 l_state_rec.STA_INFORMATION10 ,
591 l_state_rec.STA_INFORMATION11 ,
592 l_state_rec.STA_INFORMATION12 ,
593 l_state_rec.STA_INFORMATION13 ,
594 l_state_rec.STA_INFORMATION14 ,
595 l_state_rec.STA_INFORMATION15 ,
596 l_state_rec.STA_INFORMATION16 ,
597 l_state_rec.STA_INFORMATION17 ,
598 l_state_rec.STA_INFORMATION18 ,
599 l_state_rec.STA_INFORMATION19 ,
600 l_state_rec.STA_INFORMATION20 ,
601 l_state_rec.STA_INFORMATION21 ,
602 l_state_rec.STA_INFORMATION22 ,
603 l_state_rec.STA_INFORMATION23 ,
604 l_state_rec.STA_INFORMATION24 ,
605 l_state_rec.STA_INFORMATION25 ,
606 l_state_rec.STA_INFORMATION26 ,
607 l_state_rec.STA_INFORMATION27 ,
608 l_state_rec.STA_INFORMATION28 ,
609 l_state_rec.STA_INFORMATION29 ,
610 l_state_rec.STA_INFORMATION30 ) ;
611
612 hr_utility.trace('Updated Assignment : ' ||
613 to_char(l_state_rec.assignment_id));
614 commit;
615 end loop;
616 close csr_get_asg;
617
618 END;
619
620 PROCEDURE wis_eic_ovr
621 (p_business_group in varchar2
622 ,p_curr_year in varchar2
623 )
624 IS
625
626 cursor csr_get_asg(p_start_day DATE,
627 p_business_group varchar2) is
628 select *
629 from pay_us_emp_state_tax_rules_f pst
630 where (pst.sta_information1 is not null
631 or pst.sta_information6 is not null)
632 and p_start_day between (pst.effective_start_date+1) and pst.effective_end_date
633 and sta_information_category = 'WI'
634 and state_code = '50'
635 and pst.business_group_id = to_number(p_business_group);
636
637 l_state_rec PAY_US_EMP_STATE_TAX_RULES_F%rowtype;
638 l_last_day DATE;
639 l_start_day DATE;
640 l_last_year VARCHAR2(4);
641 l_curr_year VARCHAR2(4);
642
643 BEGIN
644 --hr_utility.trace_on(null,'oracle');
645 /* Get the assignments which have State EIC as not null */
646 l_curr_year := p_curr_year;
647 hr_utility.trace('l_curr_year '||l_curr_year);
648
649 l_last_year := to_number(l_curr_year) - 1;
650
651 hr_utility.trace('l_last_year '||l_last_year);
652
653 l_last_day := to_date('12/31/'||l_last_year,'MM/DD/YYYY');
654 l_start_day := to_date('01/01/'||l_curr_year,'MM/DD/YYYY');
655
656
657 hr_utility.trace('l_last_day '||to_char(l_last_day));
658 hr_utility.trace('l_start_day '||to_char(l_start_day));
659
660 open csr_get_asg(l_start_day , p_business_group);
661
662 hr_utility.trace('Updating the state tax records ...');
663
664 loop
665
666 fetch csr_get_asg into l_state_rec;
667
668 exit when csr_get_asg%NOTFOUND;
669
670 hr_utility.trace('Updating Assignment : ' ||
671 to_char(l_state_rec.assignment_id));
672 hr_utility.trace(to_char(l_state_rec.emp_state_tax_rule_id));
673 hr_utility.trace(to_char(l_state_rec.effective_end_date));
674 hr_utility.trace(to_char( l_state_rec.assignment_id));
675 hr_utility.trace(l_state_rec.state_code);
676 hr_utility.trace(l_state_rec.jurisdiction_code);
677 hr_utility.trace(to_char(l_state_rec.business_group_id));
678 hr_utility.trace(to_char( l_state_rec.additional_wa_amount));
679 hr_utility.trace( l_state_rec.filing_status_code);
680 hr_utility.trace(to_char( l_state_rec.remainder_percent));
681 hr_utility.trace(to_char( l_state_rec.secondary_wa));
682 hr_utility.trace(to_char( l_state_rec.sit_additional_tax));
683 hr_utility.trace(to_char( l_state_rec.sit_override_amount));
684 hr_utility.trace(to_char( l_state_rec.sit_override_rate));
685 hr_utility.trace(to_char( l_state_rec.withholding_allowances));
686
687 /* End date the state tax record as of /12/31/(input year-1) */
688
689
690 update PAY_US_EMP_STATE_TAX_RULES_F
691 set effective_end_date = l_last_day --to_date('12/31/'||end_year,'MM/DD/YYYY')
692 where emp_state_tax_rule_id = l_state_rec.emp_state_tax_rule_id
693 and assignment_id = l_state_rec.assignment_id
694 and effective_start_date = l_state_rec.effective_start_date
695 and effective_end_date = l_state_rec.effective_end_date
696 and sta_information1 is not null;
697
698 /* Null out the Wisconsin EIC as of 01/01/1999 */
699 hr_utility.trace('Inserting Assignment : ' ||to_char(l_state_rec.assignment_id));
700
701 hr_utility.trace('Inserting Assignment Start Date : ' ||to_char(l_start_day));
702
703 hr_utility.trace('Inserting Assignment End Date : ' ||to_char(l_state_rec.effective_end_date));
704
705 insert into PAY_US_EMP_STATE_TAX_RULES_F
706 (
707 EMP_STATE_TAX_RULE_ID,
708 EFFECTIVE_START_DATE ,
709 EFFECTIVE_END_DATE ,
710 ASSIGNMENT_ID ,
711 STATE_CODE ,
712 JURISDICTION_CODE ,
713 BUSINESS_GROUP_ID ,
714 ADDITIONAL_WA_AMOUNT ,
715 FILING_STATUS_CODE ,
716 REMAINDER_PERCENT ,
717 SECONDARY_WA ,
718 SIT_ADDITIONAL_TAX ,
719 SIT_OVERRIDE_AMOUNT ,
720 SIT_OVERRIDE_RATE ,
721 WITHHOLDING_ALLOWANCES ,
722 EXCESSIVE_WA_REJECT_DATE ,
723 SDI_EXEMPT ,
724 SIT_EXEMPT ,
725 SIT_OPTIONAL_CALC_IND ,
726 STATE_NON_RESIDENT_CERT ,
727 SUI_EXEMPT ,
728 WC_EXEMPT ,
729 SUI_WAGE_BASE_OVERRIDE_AMOUNT ,
730 SUPP_TAX_OVERRIDE_RATE ,
731 LAST_UPDATE_DATE ,
732 LAST_UPDATED_BY ,
733 LAST_UPDATE_LOGIN ,
734 CREATED_BY ,
735 CREATION_DATE ,
736 OBJECT_VERSION_NUMBER ,
737 ATTRIBUTE_CATEGORY ,
738 ATTRIBUTE1 ,
739 ATTRIBUTE2 ,
740 ATTRIBUTE3 ,
741 ATTRIBUTE4 ,
742 ATTRIBUTE5 ,
743 ATTRIBUTE6 ,
744 ATTRIBUTE7 ,
745 ATTRIBUTE8 ,
746 ATTRIBUTE9 ,
747 ATTRIBUTE10 ,
748 ATTRIBUTE11 ,
749 ATTRIBUTE12 ,
750 ATTRIBUTE13 ,
751 ATTRIBUTE14 ,
752 ATTRIBUTE15 ,
753 ATTRIBUTE16 ,
754 ATTRIBUTE17 ,
755 ATTRIBUTE18 ,
756 ATTRIBUTE19 ,
757 ATTRIBUTE20 ,
758 ATTRIBUTE21 ,
759 ATTRIBUTE22 ,
760 ATTRIBUTE23 ,
761 ATTRIBUTE24 ,
762 ATTRIBUTE25 ,
763 ATTRIBUTE26 ,
764 ATTRIBUTE27 ,
765 ATTRIBUTE28 ,
766 ATTRIBUTE29 ,
767 ATTRIBUTE30 ,
768 STA_INFORMATION_CATEGORY ,
769 STA_INFORMATION1 ,
770 STA_INFORMATION2 ,
771 STA_INFORMATION3 ,
772 STA_INFORMATION4 ,
773 STA_INFORMATION5 ,
774 STA_INFORMATION6 ,
775 STA_INFORMATION7 ,
776 STA_INFORMATION8 ,
777 STA_INFORMATION9 ,
778 STA_INFORMATION10 ,
779 STA_INFORMATION11 ,
780 STA_INFORMATION12 ,
781 STA_INFORMATION13 ,
782 STA_INFORMATION14 ,
783 STA_INFORMATION15 ,
784 STA_INFORMATION16 ,
785 STA_INFORMATION17 ,
786 STA_INFORMATION18 ,
787 STA_INFORMATION19 ,
788 STA_INFORMATION20 ,
789 STA_INFORMATION21 ,
790 STA_INFORMATION22 ,
791 STA_INFORMATION23 ,
792 STA_INFORMATION24 ,
793 STA_INFORMATION25 ,
794 STA_INFORMATION26 ,
795 STA_INFORMATION27 ,
796 STA_INFORMATION28 ,
797 STA_INFORMATION29 ,
798 STA_INFORMATION30 )
799 values
800 ( l_state_rec.EMP_STATE_TAX_RULE_ID,
801 l_start_day,
802 l_state_rec.EFFECTIVE_END_DATE,
803 l_state_rec.ASSIGNMENT_ID,
804 l_state_rec.STATE_CODE ,
805 l_state_rec.JURISDICTION_CODE ,
806 l_state_rec.BUSINESS_GROUP_ID ,
807 l_state_rec.ADDITIONAL_WA_AMOUNT ,
808 l_state_rec.FILING_STATUS_CODE ,
809 l_state_rec.REMAINDER_PERCENT ,
810 l_state_rec.SECONDARY_WA ,
811 l_state_rec.SIT_ADDITIONAL_TAX ,
812 l_state_rec.SIT_OVERRIDE_AMOUNT ,
813 l_state_rec.SIT_OVERRIDE_RATE ,
814 l_state_rec.WITHHOLDING_ALLOWANCES ,
815 l_state_rec.EXCESSIVE_WA_REJECT_DATE ,
816 l_state_rec.SDI_EXEMPT ,
817 l_state_rec.SIT_EXEMPT ,
818 l_state_rec.SIT_OPTIONAL_CALC_IND ,
819 l_state_rec.STATE_NON_RESIDENT_CERT ,
820 l_state_rec.SUI_EXEMPT ,
821 l_state_rec.WC_EXEMPT ,
822 l_state_rec.SUI_WAGE_BASE_OVERRIDE_AMOUNT ,
823 l_state_rec.SUPP_TAX_OVERRIDE_RATE ,
824 NULL , -- Bug 9157658
825 l_state_rec.LAST_UPDATED_BY ,
826 l_state_rec.LAST_UPDATE_LOGIN ,
827 l_state_rec.CREATED_BY ,
828 l_state_rec.CREATION_DATE ,
829 l_state_rec.OBJECT_VERSION_NUMBER ,
830 l_state_rec.ATTRIBUTE_CATEGORY ,
831 l_state_rec.ATTRIBUTE1 ,
832 l_state_rec.ATTRIBUTE2 ,
833 l_state_rec.ATTRIBUTE3 ,
834 l_state_rec.ATTRIBUTE4 ,
835 l_state_rec.ATTRIBUTE5 ,
836 l_state_rec.ATTRIBUTE6 ,
837 l_state_rec.ATTRIBUTE7 ,
838 l_state_rec.ATTRIBUTE8 ,
839 l_state_rec.ATTRIBUTE9 ,
840 l_state_rec.ATTRIBUTE10 ,
841 l_state_rec.ATTRIBUTE11 ,
842 l_state_rec.ATTRIBUTE12 ,
843 l_state_rec.ATTRIBUTE13 ,
844 l_state_rec.ATTRIBUTE14 ,
845 l_state_rec.ATTRIBUTE15 ,
846 l_state_rec.ATTRIBUTE16 ,
847 l_state_rec.ATTRIBUTE17 ,
848 l_state_rec.ATTRIBUTE18 ,
849 l_state_rec.ATTRIBUTE19 ,
850 l_state_rec.ATTRIBUTE20 ,
851 l_state_rec.ATTRIBUTE21 ,
852 l_state_rec.ATTRIBUTE22 ,
853 l_state_rec.ATTRIBUTE23 ,
854 l_state_rec.ATTRIBUTE24 ,
855 l_state_rec.ATTRIBUTE25 ,
856 l_state_rec.ATTRIBUTE26 ,
857 l_state_rec.ATTRIBUTE27 ,
858 l_state_rec.ATTRIBUTE28 ,
859 l_state_rec.ATTRIBUTE29 ,
860 l_state_rec.ATTRIBUTE30 ,
861 l_state_rec.STA_INFORMATION_CATEGORY ,
862 NULL ,
863 l_state_rec.STA_INFORMATION2 ,
864 l_state_rec.STA_INFORMATION3 ,
865 l_state_rec.STA_INFORMATION4 ,
866 l_state_rec.STA_INFORMATION5 ,
867 NULL ,
868 l_state_rec.STA_INFORMATION7 ,
869 l_state_rec.STA_INFORMATION8 ,
870 l_state_rec.STA_INFORMATION9 ,
871 l_state_rec.STA_INFORMATION10 ,
872 l_state_rec.STA_INFORMATION11 ,
873 l_state_rec.STA_INFORMATION12 ,
874 l_state_rec.STA_INFORMATION13 ,
875 l_state_rec.STA_INFORMATION14 ,
876 l_state_rec.STA_INFORMATION15 ,
877 l_state_rec.STA_INFORMATION16 ,
878 l_state_rec.STA_INFORMATION17 ,
879 l_state_rec.STA_INFORMATION18 ,
880 l_state_rec.STA_INFORMATION19 ,
881 l_state_rec.STA_INFORMATION20 ,
882 l_state_rec.STA_INFORMATION21 ,
883 l_state_rec.STA_INFORMATION22 ,
884 l_state_rec.STA_INFORMATION23 ,
885 l_state_rec.STA_INFORMATION24 ,
886 l_state_rec.STA_INFORMATION25 ,
887 l_state_rec.STA_INFORMATION26 ,
888 l_state_rec.STA_INFORMATION27 ,
889 l_state_rec.STA_INFORMATION28 ,
890 l_state_rec.STA_INFORMATION29 ,
891 l_state_rec.STA_INFORMATION30 ) ;
892
893 hr_utility.trace('Updated Assignment : ' ||
894 to_char(l_state_rec.assignment_id));
895 commit;
896 end loop;
897 close csr_get_asg;
898
899 END;
900
901 PROCEDURE pa_head_tx_ovr
902 (p_business_group in varchar2
903 ,p_curr_year in varchar2
904 )
905 IS
906
907 cursor csr_get_asg(p_start_day DATE,
908 p_business_group varchar2) is
909 select pct.*
910 from pay_us_emp_city_tax_rules_f pct,
911 pay_state_rules psr,
912 pay_us_states pus
913 where psr.head_tax_period = 'A'
914 and psr.state_code = pus.state_abbrev
915 and pus.state_code = pct.state_code
916 and pct.ht_exempt is not null
917 and p_start_day between (pct.effective_start_date+1) and pct.effective_end_date
918 and pct.business_group_id = to_number(p_business_group) ;
919
920
921 l_city_rec PAY_US_EMP_CITY_TAX_RULES_F%rowtype;
922 l_last_day DATE;
923 l_start_day DATE;
924 l_last_year VARCHAR2(4);
925 l_curr_year VARCHAR2(4);
926
927 BEGIN
928 --hr_utility.trace_on(null,'oracle');
929 /* Get the assignments which have PA head Tax Exemption */
930 l_curr_year := p_curr_year;
931 hr_utility.trace('l_curr_year '||l_curr_year);
932
933 l_last_year := to_number(l_curr_year) - 1;
934
935 hr_utility.trace('l_last_year '||l_last_year);
936
937 l_last_day := to_date('12/31/'||l_last_year,'MM/DD/YYYY');
938 l_start_day := to_date('01/01/'||l_curr_year,'MM/DD/YYYY');
939
940
941 hr_utility.trace('l_last_day '||to_char(l_last_day));
942 hr_utility.trace('l_start_day '||to_char(l_start_day));
943
944 open csr_get_asg(l_start_day , p_business_group);
945
946 hr_utility.trace('Updating the city tax records ...');
947
948 loop
949
950 fetch csr_get_asg into l_city_rec;
951
952 exit when csr_get_asg%NOTFOUND;
953
954 /* End date the city tax record as of /12/31/(input year-1) */
955
956
957 update PAY_US_EMP_CITY_TAX_RULES_F
958 set effective_end_date = l_last_day --to_date('12/31/'||end_year,'MM/DD/YYYY')
959 where emp_city_tax_rule_id = l_city_rec.emp_city_tax_rule_id
960 and assignment_id = l_city_rec.assignment_id
961 and effective_start_date = l_city_rec.effective_start_date
962 and effective_end_date = l_city_rec.effective_end_date
963 and ht_exempt is not null;
964
965 /* Null out the PA Head Tax exemption as of 01/01/1999 */
966 hr_utility.trace('Inserting Assignment : ' ||to_char(l_city_rec.assignment_id));
967
968 hr_utility.trace('Inserting Assignment Start Date : ' ||to_char(l_start_day));
969
970 hr_utility.trace('Inserting Assignment End Date : ' ||to_char(l_city_rec.effective_end_date));
971
972
973
974 insert into PAY_US_EMP_CITY_TAX_RULES_F
975 (
976 EMP_CITY_TAX_RULE_ID,
977 EFFECTIVE_START_DATE,
978 EFFECTIVE_END_DATE ,
979 ASSIGNMENT_ID ,
980 STATE_CODE ,
981 COUNTY_CODE ,
982 CITY_CODE ,
983 BUSINESS_GROUP_ID ,
984 ADDITIONAL_WA_RATE ,
985 FILING_STATUS_CODE ,
986 JURISDICTION_CODE ,
987 LIT_ADDITIONAL_TAX ,
988 LIT_OVERRIDE_AMOUNT ,
989 LIT_OVERRIDE_RATE ,
990 WITHHOLDING_ALLOWANCES ,
991 LIT_EXEMPT ,
992 SD_EXEMPT ,
993 HT_EXEMPT ,
994 SCHOOL_DISTRICT_CODE ,
995 LAST_UPDATE_DATE ,
996 LAST_UPDATED_BY ,
997 LAST_UPDATE_LOGIN ,
998 CREATED_BY ,
999 CREATION_DATE ,
1000 OBJECT_VERSION_NUMBER ,
1001 ATTRIBUTE_CATEGORY ,
1002 ATTRIBUTE1 ,
1003 ATTRIBUTE2 ,
1004 ATTRIBUTE3 ,
1005 ATTRIBUTE4 ,
1006 ATTRIBUTE5 ,
1007 ATTRIBUTE6 ,
1008 ATTRIBUTE7 ,
1009 ATTRIBUTE8 ,
1010 ATTRIBUTE9 ,
1011 ATTRIBUTE10 ,
1012 ATTRIBUTE11 ,
1013 ATTRIBUTE12 ,
1014 ATTRIBUTE13 ,
1015 ATTRIBUTE14 ,
1016 ATTRIBUTE15 ,
1017 ATTRIBUTE16 ,
1018 ATTRIBUTE17 ,
1019 ATTRIBUTE18 ,
1020 ATTRIBUTE19 ,
1021 ATTRIBUTE20 ,
1022 ATTRIBUTE21 ,
1023 ATTRIBUTE22 ,
1024 ATTRIBUTE23 ,
1025 ATTRIBUTE24 ,
1026 ATTRIBUTE25 ,
1027 ATTRIBUTE26 ,
1028 ATTRIBUTE27 ,
1029 ATTRIBUTE28 ,
1030 ATTRIBUTE29 ,
1031 ATTRIBUTE30 ,
1032 CTY_INFORMATION_CATEGORY ,
1033 CTY_INFORMATION1 ,
1034 CTY_INFORMATION2 ,
1035 CTY_INFORMATION3 ,
1036 CTY_INFORMATION4 ,
1037 CTY_INFORMATION5 ,
1038 CTY_INFORMATION6 ,
1039 CTY_INFORMATION7 ,
1040 CTY_INFORMATION8 ,
1041 CTY_INFORMATION9 ,
1042 CTY_INFORMATION10 ,
1043 CTY_INFORMATION11 ,
1044 CTY_INFORMATION12 ,
1045 CTY_INFORMATION13 ,
1046 CTY_INFORMATION14 ,
1047 CTY_INFORMATION15 ,
1048 CTY_INFORMATION16 ,
1049 CTY_INFORMATION17 ,
1050 CTY_INFORMATION18 ,
1051 CTY_INFORMATION19 ,
1052 CTY_INFORMATION20 ,
1053 CTY_INFORMATION21 ,
1054 CTY_INFORMATION22 ,
1055 CTY_INFORMATION23 ,
1056 CTY_INFORMATION24 ,
1057 CTY_INFORMATION25 ,
1058 CTY_INFORMATION26 ,
1059 CTY_INFORMATION27 ,
1060 CTY_INFORMATION28 ,
1061 CTY_INFORMATION29 ,
1062 CTY_INFORMATION30
1063 )
1064 values
1065 (
1066 l_city_rec.emp_city_tax_rule_id,
1067 l_start_day,
1068 l_city_rec.effective_end_date,
1069 l_city_rec.ASSIGNMENT_ID ,
1070 l_city_rec.STATE_CODE ,
1071 l_city_rec.COUNTY_CODE ,
1072 l_city_rec.CITY_CODE ,
1073 l_city_rec.BUSINESS_GROUP_ID ,
1074 l_city_rec.ADDITIONAL_WA_RATE ,
1075 l_city_rec.FILING_STATUS_CODE ,
1076 l_city_rec.JURISDICTION_CODE ,
1077 l_city_rec.LIT_ADDITIONAL_TAX ,
1078 l_city_rec.LIT_OVERRIDE_AMOUNT ,
1079 l_city_rec.LIT_OVERRIDE_RATE ,
1080 l_city_rec.WITHHOLDING_ALLOWANCES ,
1081 l_city_rec.LIT_EXEMPT ,
1082 l_city_rec.SD_EXEMPT ,
1083 NULL ,
1084 l_city_rec.SCHOOL_DISTRICT_CODE ,
1085 NULL , -- Bug 9157658
1086 l_city_rec.LAST_UPDATED_BY ,
1087 l_city_rec.LAST_UPDATE_LOGIN ,
1088 l_city_rec.CREATED_BY ,
1089 l_city_rec.CREATION_DATE ,
1090 l_city_rec.OBJECT_VERSION_NUMBER ,
1091 l_city_rec.ATTRIBUTE_CATEGORY ,
1092 l_city_rec.ATTRIBUTE1 ,
1093 l_city_rec.ATTRIBUTE2 ,
1094 l_city_rec.ATTRIBUTE3 ,
1095 l_city_rec.ATTRIBUTE4 ,
1096 l_city_rec.ATTRIBUTE5 ,
1097 l_city_rec.ATTRIBUTE6 ,
1098 l_city_rec.ATTRIBUTE7 ,
1099 l_city_rec.ATTRIBUTE8 ,
1100 l_city_rec.ATTRIBUTE9 ,
1101 l_city_rec.ATTRIBUTE10 ,
1102 l_city_rec.ATTRIBUTE11 ,
1103 l_city_rec.ATTRIBUTE12 ,
1104 l_city_rec.ATTRIBUTE13 ,
1105 l_city_rec.ATTRIBUTE14 ,
1106 l_city_rec.ATTRIBUTE15 ,
1107 l_city_rec.ATTRIBUTE16 ,
1108 l_city_rec.ATTRIBUTE17 ,
1109 l_city_rec.ATTRIBUTE18 ,
1110 l_city_rec.ATTRIBUTE19 ,
1111 l_city_rec.ATTRIBUTE20 ,
1112 l_city_rec.ATTRIBUTE21 ,
1113 l_city_rec.ATTRIBUTE22 ,
1114 l_city_rec.ATTRIBUTE23 ,
1115 l_city_rec.ATTRIBUTE24 ,
1116 l_city_rec.ATTRIBUTE25 ,
1117 l_city_rec.ATTRIBUTE26 ,
1118 l_city_rec.ATTRIBUTE27 ,
1119 l_city_rec.ATTRIBUTE28 ,
1120 l_city_rec.ATTRIBUTE29 ,
1121 l_city_rec.ATTRIBUTE30 ,
1122 l_city_rec.CTY_INFORMATION_CATEGORY ,
1123 l_city_rec.CTY_INFORMATION1 ,
1124 l_city_rec.CTY_INFORMATION2 ,
1125 l_city_rec.CTY_INFORMATION3 ,
1126 l_city_rec.CTY_INFORMATION4 ,
1127 l_city_rec.CTY_INFORMATION5 ,
1128 l_city_rec.CTY_INFORMATION6 ,
1129 l_city_rec.CTY_INFORMATION7 ,
1130 l_city_rec.CTY_INFORMATION8 ,
1131 l_city_rec.CTY_INFORMATION9 ,
1132 l_city_rec.CTY_INFORMATION10 ,
1133 l_city_rec.CTY_INFORMATION11 ,
1134 l_city_rec.CTY_INFORMATION12 ,
1135 l_city_rec.CTY_INFORMATION13 ,
1136 l_city_rec.CTY_INFORMATION14 ,
1137 l_city_rec.CTY_INFORMATION15 ,
1138 l_city_rec.CTY_INFORMATION16 ,
1139 l_city_rec.CTY_INFORMATION17 ,
1140 l_city_rec.CTY_INFORMATION18 ,
1141 l_city_rec.CTY_INFORMATION19 ,
1142 l_city_rec.CTY_INFORMATION20 ,
1143 l_city_rec.CTY_INFORMATION21 ,
1144 l_city_rec.CTY_INFORMATION22 ,
1145 l_city_rec.CTY_INFORMATION23 ,
1146 l_city_rec.CTY_INFORMATION24 ,
1147 l_city_rec.CTY_INFORMATION25 ,
1148 l_city_rec.CTY_INFORMATION26 ,
1149 l_city_rec.CTY_INFORMATION27 ,
1150 l_city_rec.CTY_INFORMATION28 ,
1151 l_city_rec.CTY_INFORMATION29 ,
1152 l_city_rec.CTY_INFORMATION30
1153 );
1154
1155
1156 hr_utility.trace('Updated Assignment : ' ||
1157 to_char(l_city_rec.assignment_id));
1158 commit;
1159 end loop;
1160 close csr_get_asg;
1161
1162 END;
1163
1164
1165
1166 PROCEDURE fed_eic_filing_status_ovr
1167 (p_business_group in varchar2
1168 ,p_curr_year in varchar2
1169 )
1170 IS
1171
1172 cursor csr_get_asg(p_start_day DATE,
1173 p_business_group varchar2) is
1174 select *
1175 from pay_us_emp_fed_tax_rules_f pft
1176 where pft.eic_filing_status_code <> 0
1177 and p_start_day between (pft.effective_start_date+1) and pft.effective_end_date
1178 and pft.business_group_id = to_number(p_business_group);
1179
1180
1181 l_fed_rec PAY_US_EMP_FED_TAX_RULES_F%rowtype;
1182 l_last_day DATE;
1183 l_start_day DATE;
1184 l_last_year VARCHAR2(4);
1185 l_curr_year VARCHAR2(4);
1186
1187 BEGIN
1188 --hr_utility.trace_on(null,'oracle');
1189 /* Get the assignments which have Fed EIC */
1190 l_curr_year := p_curr_year;
1191 hr_utility.trace('l_curr_year '||l_curr_year);
1192
1193 l_last_year := to_number(l_curr_year) - 1;
1194
1195 hr_utility.trace('l_last_year '||l_last_year);
1196
1197 l_last_day := to_date('12/31/'||l_last_year,'MM/DD/YYYY');
1198 l_start_day := to_date('01/01/'||l_curr_year,'MM/DD/YYYY');
1199
1200
1201 hr_utility.trace('l_last_day '||to_char(l_last_day));
1202 hr_utility.trace('l_start_day '||to_char(l_start_day));
1203
1204 open csr_get_asg(l_start_day , p_business_group);
1205
1206 hr_utility.trace('Updating the fed tax records ...');
1207
1208 loop
1209
1210 fetch csr_get_asg into l_fed_rec;
1211
1212 exit when csr_get_asg%NOTFOUND;
1213
1214 /* End date the fed tax record as of /12/31/(input year-1) */
1215
1216
1217 update PAY_US_EMP_FED_TAX_RULES_F
1218 set effective_end_date = l_last_day --to_date('12/31/'||end_year,'MM/DD/YYYY')
1219 where assignment_id = l_fed_rec.assignment_id
1220 and effective_start_date = l_fed_rec.effective_start_date
1221 and effective_end_date = l_fed_rec.effective_end_date
1222 and eic_filing_status_code <> 0;
1223
1224 /* Null out the FED EIC of 01/01/1999 */
1225 hr_utility.trace('Inserting Assignment : ' ||to_char(l_fed_rec.assignment_id));
1226
1227 hr_utility.trace('Inserting Assignment Start Date : ' ||to_char(l_start_day));
1228
1229 hr_utility.trace('Inserting Assignment End Date : ' ||to_char(l_fed_rec.effective_end_date));
1230
1231
1232
1233 /* Insert Stmt for FED Tax Rules will come here */
1234 insert into pay_us_emp_fed_tax_rules_f
1235 (
1236 EMP_FED_TAX_RULE_ID ,
1237 EFFECTIVE_START_DATE ,
1238 EFFECTIVE_END_DATE ,
1239 ASSIGNMENT_ID ,
1240 SUI_STATE_CODE ,
1241 SUI_JURISDICTION_CODE ,
1242 BUSINESS_GROUP_ID ,
1243 ADDITIONAL_WA_AMOUNT ,
1244 FILING_STATUS_CODE ,
1245 FIT_OVERRIDE_AMOUNT ,
1246 FIT_OVERRIDE_RATE ,
1247 WITHHOLDING_ALLOWANCES ,
1248 CUMULATIVE_TAXATION ,
1249 EIC_FILING_STATUS_CODE ,
1250 FIT_ADDITIONAL_TAX ,
1251 FIT_EXEMPT ,
1252 FUTA_TAX_EXEMPT ,
1253 MEDICARE_TAX_EXEMPT ,
1254 SS_TAX_EXEMPT ,
1255 STATUTORY_EMPLOYEE ,
1256 W2_FILED_YEAR ,
1257 SUPP_TAX_OVERRIDE_RATE ,
1258 EXCESSIVE_WA_REJECT_DATE ,
1259 LAST_UPDATE_DATE ,
1260 LAST_UPDATED_BY ,
1261 LAST_UPDATE_LOGIN ,
1262 CREATED_BY ,
1263 CREATION_DATE ,
1264 OBJECT_VERSION_NUMBER ,
1265 ATTRIBUTE_CATEGORY ,
1266 ATTRIBUTE1 ,
1267 ATTRIBUTE2 ,
1268 ATTRIBUTE3 ,
1269 ATTRIBUTE4 ,
1270 ATTRIBUTE5 ,
1271 ATTRIBUTE6 ,
1272 ATTRIBUTE7 ,
1273 ATTRIBUTE8 ,
1274 ATTRIBUTE9 ,
1275 ATTRIBUTE10 ,
1276 ATTRIBUTE11 ,
1277 ATTRIBUTE12 ,
1278 ATTRIBUTE13 ,
1279 ATTRIBUTE14 ,
1280 ATTRIBUTE15 ,
1281 ATTRIBUTE16 ,
1282 ATTRIBUTE17 ,
1283 ATTRIBUTE18 ,
1284 ATTRIBUTE19 ,
1285 ATTRIBUTE20 ,
1286 ATTRIBUTE21 ,
1287 ATTRIBUTE22 ,
1288 ATTRIBUTE23 ,
1289 ATTRIBUTE24 ,
1290 ATTRIBUTE25 ,
1291 ATTRIBUTE26 ,
1292 ATTRIBUTE27 ,
1293 ATTRIBUTE28 ,
1294 ATTRIBUTE29 ,
1295 ATTRIBUTE30 ,
1296 FED_INFORMATION_CATEGORY ,
1297 FED_INFORMATION1 ,
1298 FED_INFORMATION2 ,
1299 FED_INFORMATION3 ,
1300 FED_INFORMATION4 ,
1301 FED_INFORMATION5 ,
1302 FED_INFORMATION6 ,
1303 FED_INFORMATION7 ,
1304 FED_INFORMATION8 ,
1305 FED_INFORMATION9 ,
1306 FED_INFORMATION10 ,
1307 FED_INFORMATION11 ,
1308 FED_INFORMATION12 ,
1309 FED_INFORMATION13 ,
1310 FED_INFORMATION14 ,
1311 FED_INFORMATION15 ,
1312 FED_INFORMATION16 ,
1313 FED_INFORMATION17 ,
1314 FED_INFORMATION18 ,
1315 FED_INFORMATION19 ,
1316 FED_INFORMATION20 ,
1317 FED_INFORMATION21 ,
1318 FED_INFORMATION22 ,
1319 FED_INFORMATION23 ,
1320 FED_INFORMATION24 ,
1321 FED_INFORMATION25 ,
1322 FED_INFORMATION26 ,
1323 FED_INFORMATION27 ,
1324 FED_INFORMATION28 ,
1325 FED_INFORMATION29 ,
1326 FED_INFORMATION30
1327 )
1328 values
1329 (
1330 l_fed_rec.EMP_FED_TAX_RULE_ID ,
1331 l_start_day,
1332 l_fed_rec.effective_end_date,
1333 l_fed_rec.ASSIGNMENT_ID ,
1334 l_fed_rec.SUI_STATE_CODE ,
1335 l_fed_rec.SUI_JURISDICTION_CODE ,
1336 l_fed_rec.BUSINESS_GROUP_ID ,
1337 l_fed_rec.ADDITIONAL_WA_AMOUNT ,
1338 l_fed_rec.FILING_STATUS_CODE ,
1339 l_fed_rec.FIT_OVERRIDE_AMOUNT ,
1340 l_fed_rec.FIT_OVERRIDE_RATE ,
1341 l_fed_rec.WITHHOLDING_ALLOWANCES ,
1342 l_fed_rec.CUMULATIVE_TAXATION ,
1343 0 ,
1344 l_fed_rec.FIT_ADDITIONAL_TAX ,
1345 l_fed_rec.FIT_EXEMPT ,
1346 l_fed_rec.FUTA_TAX_EXEMPT ,
1347 l_fed_rec.MEDICARE_TAX_EXEMPT ,
1348 l_fed_rec.SS_TAX_EXEMPT ,
1349 l_fed_rec.STATUTORY_EMPLOYEE ,
1350 l_fed_rec.W2_FILED_YEAR ,
1351 l_fed_rec.SUPP_TAX_OVERRIDE_RATE ,
1352 l_fed_rec.EXCESSIVE_WA_REJECT_DATE ,
1353 NULL , -- Bug 9157658
1354 l_fed_rec.LAST_UPDATED_BY ,
1355 l_fed_rec.LAST_UPDATE_LOGIN ,
1356 l_fed_rec.CREATED_BY ,
1357 l_fed_rec.CREATION_DATE ,
1358 l_fed_rec.OBJECT_VERSION_NUMBER ,
1359 l_fed_rec.ATTRIBUTE_CATEGORY ,
1360 l_fed_rec.ATTRIBUTE1 ,
1361 l_fed_rec.ATTRIBUTE2 ,
1362 l_fed_rec.ATTRIBUTE3 ,
1363 l_fed_rec.ATTRIBUTE4 ,
1364 l_fed_rec.ATTRIBUTE5 ,
1365 l_fed_rec.ATTRIBUTE6 ,
1366 l_fed_rec.ATTRIBUTE7 ,
1367 l_fed_rec.ATTRIBUTE8 ,
1368 l_fed_rec.ATTRIBUTE9 ,
1369 l_fed_rec.ATTRIBUTE10 ,
1370 l_fed_rec.ATTRIBUTE11 ,
1371 l_fed_rec.ATTRIBUTE12 ,
1372 l_fed_rec.ATTRIBUTE13 ,
1373 l_fed_rec.ATTRIBUTE14 ,
1374 l_fed_rec.ATTRIBUTE15 ,
1375 l_fed_rec.ATTRIBUTE16 ,
1376 l_fed_rec.ATTRIBUTE17 ,
1377 l_fed_rec.ATTRIBUTE18 ,
1378 l_fed_rec.ATTRIBUTE19 ,
1379 l_fed_rec.ATTRIBUTE20 ,
1380 l_fed_rec.ATTRIBUTE21 ,
1381 l_fed_rec.ATTRIBUTE22 ,
1382 l_fed_rec.ATTRIBUTE23 ,
1383 l_fed_rec.ATTRIBUTE24 ,
1384 l_fed_rec.ATTRIBUTE25 ,
1385 l_fed_rec.ATTRIBUTE26 ,
1386 l_fed_rec.ATTRIBUTE27 ,
1387 l_fed_rec.ATTRIBUTE28 ,
1388 l_fed_rec.ATTRIBUTE29 ,
1389 l_fed_rec.ATTRIBUTE30 ,
1390 l_fed_rec.FED_INFORMATION_CATEGORY ,
1391 l_fed_rec.FED_INFORMATION1 ,
1392 l_fed_rec.FED_INFORMATION2 ,
1393 l_fed_rec.FED_INFORMATION3 ,
1394 l_fed_rec.FED_INFORMATION4 ,
1395 l_fed_rec.FED_INFORMATION5 ,
1396 l_fed_rec.FED_INFORMATION6 ,
1397 l_fed_rec.FED_INFORMATION7 ,
1398 l_fed_rec.FED_INFORMATION8 ,
1399 l_fed_rec.FED_INFORMATION9 ,
1400 l_fed_rec.FED_INFORMATION10 ,
1401 l_fed_rec.FED_INFORMATION11 ,
1402 l_fed_rec.FED_INFORMATION12 ,
1403 l_fed_rec.FED_INFORMATION13 ,
1404 l_fed_rec.FED_INFORMATION14 ,
1405 l_fed_rec.FED_INFORMATION15 ,
1406 l_fed_rec.FED_INFORMATION16 ,
1407 l_fed_rec.FED_INFORMATION17 ,
1408 l_fed_rec.FED_INFORMATION18 ,
1409 l_fed_rec.FED_INFORMATION19 ,
1410 l_fed_rec.FED_INFORMATION20 ,
1411 l_fed_rec.FED_INFORMATION21 ,
1412 l_fed_rec.FED_INFORMATION22 ,
1413 l_fed_rec.FED_INFORMATION23 ,
1414 l_fed_rec.FED_INFORMATION24 ,
1415 l_fed_rec.FED_INFORMATION25 ,
1416 l_fed_rec.FED_INFORMATION26 ,
1417 l_fed_rec.FED_INFORMATION27 ,
1418 l_fed_rec.FED_INFORMATION28 ,
1419 l_fed_rec.FED_INFORMATION29 ,
1420 l_fed_rec.FED_INFORMATION30
1421 );
1422
1423
1424 hr_utility.trace('Updated Assignment : ' ||
1425 to_char(l_fed_rec.assignment_id));
1426 commit;
1427 end loop;
1428 close csr_get_asg;
1429
1430 END;
1431
1432 /*Created for Bug8985595 to allow the clearing of Wisconsin EIC through Year Begin
1433 Process.This is an overloaded function created to maintain integrity of any other
1434 reference to this procedure. */
1435
1436 PROCEDURE reset_overrides
1437 (errbuf out nocopy varchar2
1438 ,retcode out nocopy number
1439 ,p_business_group in varchar2
1440 ,p_curr_year in varchar2
1441 ,p_clr_ind_add_ovr in varchar2
1442 ,p_clr_ind_eic in varchar2
1443 ,p_clr_sui_wb_ovr in varchar2
1444 ,p_clr_pa_head_tax in varchar2
1445 ,p_clr_fed_eic_filing_status in varchar2
1446 )
1447 IS
1448
1449 l_clr_wis_eic varchar2(3);
1450
1451 BEGIN
1452
1453 l_clr_wis_eic := 'N';
1454
1455 /*If a call to reset_overrides happens without the p_clr_wis_eic,
1456 then we make p_clr_wis_eic as No and pass it to the procedure*/
1457
1458 reset_overrides
1459 (errbuf
1460 ,retcode
1461 ,p_business_group
1462 ,p_curr_year
1463 ,p_clr_ind_add_ovr
1464 ,p_clr_ind_eic
1465 ,p_clr_sui_wb_ovr
1466 ,p_clr_pa_head_tax
1467 ,p_clr_fed_eic_filing_status
1468 ,l_clr_wis_eic
1469 );
1470
1471 END;
1472
1473 /*Added Parameter p_clr_wis_eic for Bug8985595 to allow the clearing of Wisconsin EIC through Year Begin Process */
1474
1475 PROCEDURE reset_overrides
1476 (errbuf out nocopy varchar2
1477 ,retcode out nocopy number
1478 ,p_business_group in varchar2
1479 ,p_curr_year in varchar2
1480 ,p_clr_ind_add_ovr in varchar2
1481 ,p_clr_ind_eic in varchar2
1482 ,p_clr_sui_wb_ovr in varchar2
1483 ,p_clr_pa_head_tax in varchar2
1484 ,p_clr_fed_eic_filing_status in varchar2
1485 ,p_clr_wis_eic in varchar2
1486 )
1487 IS
1488
1489 BEGIN
1490 --hr_utility.trace_on(null,'ORACLE');
1491 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);
1492
1493
1494 /* Clear the Indiana Override Adderess/Location */
1495 IF p_clr_ind_add_ovr = 'Y' THEN
1496 pay_us_indiana.update_address(errbuf,retcode,p_business_group,p_curr_year);
1497 END IF;
1498 /* End Indiana Override Address /Location */
1499
1500 /* Clear Indiana EIC */
1501 IF p_clr_ind_eic = 'Y' THEN
1502 hr_utility.trace('Procedure ind_eic_ovr');
1503 ind_eic_ovr(p_business_group,p_curr_year);
1504 END IF;
1505 /* End Indiana EIC */
1506
1507 /*Added for Bug8985595 to allow the clearing of Wisconsin EIC through Year Begin Process */
1508
1509 /* Clear Wisconsin EIC */
1510 IF p_clr_wis_eic = 'Y' THEN
1511 hr_utility.trace('Procedure wis_eic_ovr');
1512 wis_eic_ovr(p_business_group,p_curr_year);
1513 END IF;
1514 /* End Wisconsin EIC */
1515
1516 /*Bug8985595 ends*/
1517
1518 /* Clear SUI Wage Base Override */
1519 IF p_clr_sui_wb_ovr = 'Y' THEN
1520 hr_utility.trace('Procedure sui_wb_ovr');
1521 sui_wb_ovr(p_business_group,p_curr_year);
1522 END IF;
1523 /* End SUI Wage Base Override */
1524
1525
1526 /*Clear PA Head tax exempt */
1527 IF p_clr_pa_head_tax ='Y' THEN
1528 hr_utility.trace('Procedure pa_head_tx_ovr');
1529 pa_head_tx_ovr(p_business_group,p_curr_year);
1530 END IF;
1531 /* End PS head tax exempt */
1532
1533
1534 /* Clear Federal EIC Filing status */
1535 IF p_clr_fed_eic_filing_status = 'Y' THEN
1536 hr_utility.trace('Procedure fed_eic_filing_status_ovr');
1537 fed_eic_filing_status_ovr(p_business_group,p_curr_year);
1538 END IF;
1539 /* End EIC filing status*/
1540
1541 END reset_overrides;
1542
1543 end pay_us_year_begin_process;