[Home] [Help]
PACKAGE BODY: APPS.PAY_US_TAX_API
Source
1 PACKAGE BODY pay_us_tax_api AS
2 /* $Header: pytaxapi.pkb 115.8 2003/06/03 17:39:29 tclewis ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' pay_us_tax_api.';
7 -- ----------------------------------------------------------------------------
8 -- |---------------------< correct_tax_percentage >---------------------------|
9 -- ----------------------------------------------------------------------------
10 --
11 procedure correct_tax_percentage
12 (
13 p_validate boolean default false
14 ,p_assignment_id number
15 ,p_effective_date date
16 ,p_state_code varchar2
17 ,p_county_code varchar2
18 ,p_city_code varchar2
19 ,p_percentage number
20 ) is
21 l_effective_start_date date := p_effective_date;
22 l_effective_end_date date;
23 l_proc varchar2(72) := g_package||'correct_tax_percentage';
24 begin
25 hr_utility.set_location('Entering:'||l_proc, 10);
26 pay_us_tax_internal.maintain_tax_percentage
27 (
28 p_assignment_id => p_assignment_id,
29 p_effective_date => p_effective_date,
30 p_state_code => p_state_code,
31 p_county_code => p_county_code,
32 p_city_code => p_city_code,
33 p_percentage => p_percentage,
34 p_datetrack_mode => 'CORRECTION',
35 p_calculate_pct => TRUE,
36 p_effective_start_date => l_effective_start_date,
37 p_effective_end_date => l_effective_end_date
38 );
39 hr_utility.set_location('Leaving:'||l_proc, 20);
40 end correct_tax_percentage;
41 --
42 -- ----------------------------------------------------------------------------
43 -- |----------------------------< delete_tax_rule >---------------------------|
44 -- ----------------------------------------------------------------------------
45 -- Deletes the tax rule(s) and percentages for a given jurisdiction as well as
46 -- deleting all subordinate jurisdictions and their respective tax percentages.
47 --
48 procedure delete_tax_rule
49 (p_validate in boolean default false
50 ,p_assignment_id in number
51 ,p_state_code in varchar2
52 ,p_county_code in varchar2 default '000'
53 ,p_city_code in varchar2 default '0000'
54 ,p_effective_start_date out nocopy date
55 ,p_effective_end_date out nocopy date
56 ,p_object_version_number in out nocopy number
57 ,p_effective_date in date
58 ,p_datetrack_mode in varchar2 default 'ZAP'
59 ,p_delete_routine in varchar2 default null
60 ) is
61 --
62 -- Declare types, cursors and local variables
63 --
64 TYPE gen_rec is RECORD
65 (
66 state_code pay_us_emp_city_tax_rules_f.state_code%TYPE,
67 county_code pay_us_emp_city_tax_rules_f.state_code%TYPE,
68 city_code pay_us_emp_city_tax_rules_f.state_code%TYPE,
69 object_version_number number
70 );
71 --
72 --
73 -- Find the city_tax_rule_id, object_version_number,
74 -- and effective_start and end dates for the current jurisdiction.
75 --
76 Cursor get_city_tax_rule_id(l_jurisdiction varchar2) is
77 select emp_city_tax_rule_id, object_version_number,
78 effective_start_date, effective_end_date
79 from pay_us_emp_city_tax_rules_f
80 where assignment_id = p_assignment_id
81 and jurisdiction_code = l_jurisdiction
82 and p_effective_date between effective_start_date
83 and effective_end_date;
84 --
85 -- Find the county_tax_rule_id, object_version_number,
86 -- and effective_start and end dates for the current jurisdiction.
87 --
88 Cursor get_county_tax_rule_id(l_jurisdiction varchar2) is
89 select emp_county_tax_rule_id, object_version_number,
90 effective_start_date, effective_end_date
91 from pay_us_emp_county_tax_rules_f
92 where assignment_id = p_assignment_id
93 and jurisdiction_code = l_jurisdiction
94 and p_effective_date between effective_start_date
95 and effective_end_date;
96 --
97 -- Find the state_tax_rule_id, object_version_number,
98 -- and effective_start and end dates for the current jurisdiction.
99 --
100 Cursor get_state_tax_rule_id(l_jurisdiction varchar2) is
101 select emp_state_tax_rule_id, object_version_number,
102 effective_start_date, effective_end_date
103 from pay_us_emp_state_tax_rules_f
104 where assignment_id = p_assignment_id
105 and jurisdiction_code = l_jurisdiction
106 and p_effective_date between effective_start_date
107 and effective_end_date;
108 --
109 -- Find all cities under county jurisdiction
110 --
111 cursor csr_city_parm_sel is
112 select state_code, county_code,
113 city_code, object_version_number
114 from pay_us_emp_city_tax_rules_f
115 where assignment_id = p_assignment_id
116 and state_code = p_state_code
117 and county_code = p_county_code
118 and (city_code <> '0000'
119 and city_code is not null)
120 and p_effective_date
121 between effective_start_date
122 and effective_end_date;
123 --
124 -- Find all counties under state jurisdiction
125 --
126 cursor csr_county_parm_sel is
127 select state_code, county_code,
128 '0000' city_code, object_version_number
129 from pay_us_emp_county_tax_rules_f
130 where assignment_id = p_assignment_id
131 and state_code = p_state_code
132 and (county_code <> '000'
133 and county_code is not null)
134 and p_effective_date
135 between effective_start_date
136 and effective_end_date;
137 --
138 --
139 --
140 cursor csr_chk_vertex_exist(
141 p_jurisdiction varchar2,
142 p_effective_date date
143 ) is
144 select count(*)
145 from pay_element_entries_f peef,
146 pay_element_entry_values_f peevf,
147 pay_element_types_f petf,
148 pay_element_links_f pelf
149 where peef.assignment_id=p_assignment_id
150 and p_effective_date < peef.effective_end_date
151 and petf.element_name='VERTEX'
152 and pelf.element_type_id=petf.element_type_id
153 and peef.element_link_id=pelf.element_link_id
154 and peevf.screen_entry_value = p_jurisdiction
155 and p_effective_date < peevf.effective_end_date
156 and peevf.element_entry_id = peef.element_entry_id;
157
158 /*
159 Constants
160 */
161 CITY_JURISDICTION constant number := 1;
162 COUNTY_JURISDICTION constant number := 2;
163 STATE_JURISDICTION constant number := 3;
164
165 l_proc varchar2(72) := g_package||'delete_tax_rule';
166 l_jurisdiction_code pay_us_emp_state_tax_rules.jurisdiction_code%TYPE;
167 l_object_version_number
168 pay_us_emp_city_tax_rules_f.object_version_number%TYPE;
169 l_effective_start_date pay_us_emp_city_tax_rules_f.effective_start_date%TYPE;
170 l_effective_end_date pay_us_emp_city_tax_rules_f.effective_end_date%TYPE;
171 l_emp_city_tax_rule_id pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE;
172 l_emp_county_tax_rule_id
173 pay_us_emp_county_tax_rules_f.emp_county_tax_rule_id%TYPE;
174 l_emp_state_tax_rule_id
175 pay_us_emp_state_tax_rules_f.emp_state_tax_rule_id%TYPE;
176 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
177 t_effective_start_date pay_us_emp_city_tax_rules_f.effective_start_date%TYPE;
178 l_effective_date pay_us_emp_city_tax_rules_f.effective_start_date%TYPE;
179 l_vertex_exist number;
180 l_delete_jurisdiction number;
181 l_parm_sel varchar2(2000);
182 l_jurisdiction_rec gen_rec;
183 --
184 begin
185 --
186 hr_utility.set_location('Entering:'|| l_proc, 10);
187 --
188 -- Issue a savepoint
189 --
190 savepoint delete_tax_rule;
191
192 --
193 hr_utility.set_location(l_proc, 15);
194 --
195 -- Process Logic
196 --
197 l_jurisdiction_code := p_state_code||'-'
198 ||p_county_code||'-'
199 ||p_city_code;
200 --
201 if p_city_code <> '0000' then
202 hr_utility.set_location(l_proc, 20);
203 l_delete_jurisdiction := CITY_JURISDICTION;
204 -- just delete the city.
205 elsif p_county_code <> '000' and p_city_code = '0000' then
206 hr_utility.set_location(l_proc, 25);
207 l_delete_jurisdiction := COUNTY_JURISDICTION;
208 -- find all of the cities, then delete them
209 for l_jurisdiction_rec in csr_city_parm_sel loop
210 delete_tax_rule
211 (
212 p_validate => FALSE
213 ,p_assignment_id => p_assignment_id
214 ,p_state_code => l_jurisdiction_rec.state_code
215 ,p_county_code => l_jurisdiction_rec.county_code
216 ,p_city_code => l_jurisdiction_rec.city_code
217 ,p_effective_start_date => l_effective_start_date
218 ,p_effective_end_date => l_effective_end_date
219 ,p_object_version_number => l_jurisdiction_rec.object_version_number
220 ,p_effective_date => p_effective_date
221 ,p_datetrack_mode => p_datetrack_mode
222 ,p_delete_routine => p_delete_routine
223 );
224 end loop;
225 elsif p_state_code is not null and p_county_code = '000'
226 and p_city_code = '0000' then
227 hr_utility.set_location(l_proc, 30);
228 l_delete_jurisdiction := STATE_JURISDICTION;
229 -- find all of the counties then delete them
230 for l_jurisdiction_rec in csr_county_parm_sel loop
231 delete_tax_rule
232 (
233 p_validate => FALSE
234 ,p_assignment_id => p_assignment_id
235 ,p_state_code => l_jurisdiction_rec.state_code
236 ,p_county_code => l_jurisdiction_rec.county_code
237 ,p_city_code => l_jurisdiction_rec.city_code
238 ,p_effective_start_date => l_effective_start_date
239 ,p_effective_end_date => l_effective_end_date
240 ,p_object_version_number => l_jurisdiction_rec.object_version_number
241 ,p_effective_date => p_effective_date
242 ,p_datetrack_mode => p_datetrack_mode
243 ,p_delete_routine => p_delete_routine
244 );
245 end loop;
246 end if;
247 --
248 --
249 l_object_version_number := p_object_version_number;
250 --
251 if l_delete_jurisdiction = CITY_JURISDICTION then
252 --
253 --
254 --
255 open get_city_tax_rule_id(l_jurisdiction_code);
256 fetch get_city_tax_rule_id into l_emp_city_tax_rule_id,
257 l_object_version_number,
258 l_effective_start_date,
259 l_effective_end_date;
260 if get_city_tax_rule_id%FOUND then
261 close get_city_tax_rule_id;
262 --
263 hr_utility.set_location(l_proc, 40);
264 --
265 pay_cty_del.del
266 (
267 p_emp_city_tax_rule_id => l_emp_city_tax_rule_id
268 ,p_effective_start_date => l_effective_start_date
269 ,p_effective_end_date => l_effective_end_date
270 ,p_object_version_number => l_object_version_number
271 ,p_effective_date => p_effective_date
272 ,p_datetrack_mode => p_datetrack_mode
273 ,p_delete_routine => p_delete_routine
274 );
275 else
276 close get_city_tax_rule_id;
277 hr_utility.set_message(801, 'HR_7182_DT_NO_MIN_MAX_ROWS');
278 hr_utility.set_message_token('table', 'pay_us_emp_city_tax_rules_f');
279 hr_utility.set_message_token('step','45');
280 hr_utility.raise_error;
281 end if;
282 elsif l_delete_jurisdiction = COUNTY_JURISDICTION then
283 --
284 --
285 --
286 open get_county_tax_rule_id(l_jurisdiction_code);
287 fetch get_county_tax_rule_id into l_emp_county_tax_rule_id,
288 l_object_version_number,
289 l_effective_start_date,
290 l_effective_end_date;
291 if get_county_tax_rule_id%FOUND then
292 close get_county_tax_rule_id;
293 --
294 hr_utility.set_location(l_proc, 50);
295 --
296 pay_cnt_del.del
297 (
298 p_emp_county_tax_rule_id => l_emp_county_tax_rule_id
299 ,p_effective_start_date => l_effective_start_date
300 ,p_effective_end_date => l_effective_end_date
301 ,p_object_version_number => l_object_version_number
302 ,p_effective_date => p_effective_date
303 ,p_datetrack_mode => p_datetrack_mode
304 ,p_delete_routine => p_delete_routine
305 );
306 else
307 close get_county_tax_rule_id;
308 hr_utility.set_message(801, 'HR_7182_DT_NO_MIN_MAX_ROWS');
309 hr_utility.set_message_token('table', 'pay_us_emp_county_tax_rules_f');
310 hr_utility.set_message_token('step','55');
311 hr_utility.raise_error;
312 end if;
313 elsif l_delete_jurisdiction = STATE_JURISDICTION then
314 --
315 --
316 --
317 open get_state_tax_rule_id(l_jurisdiction_code);
318 fetch get_state_tax_rule_id into l_emp_state_tax_rule_id,
319 l_object_version_number,
320 l_effective_start_date,
321 l_effective_end_date;
322 if get_state_tax_rule_id%FOUND then
323 close get_state_tax_rule_id;
324 --
325 hr_utility.set_location(l_proc, 60);
326 --
327 pay_sta_del.del
328 (
329 p_emp_state_tax_rule_id => l_emp_state_tax_rule_id
330 ,p_effective_start_date => l_effective_start_date
331 ,p_effective_end_date => l_effective_end_date
332 ,p_object_version_number => l_object_version_number
333 ,p_effective_date => p_effective_date
334 ,p_datetrack_mode => p_datetrack_mode
335 ,p_delete_routine => p_delete_routine
336 );
337 else
338 close get_state_tax_rule_id;
339 hr_utility.set_message(801, 'HR_7182_DT_NO_MIN_MAX_ROWS');
340 hr_utility.set_message_token('table', 'pay_us_emp_state_tax_rules_f');
341 hr_utility.set_message_token('step','65');
342 hr_utility.raise_error;
343 end if;
344 end if;
345 --
346 if p_datetrack_mode = 'DELETE' then
347 l_effective_date := p_effective_date;
348 else
349 l_effective_date := hr_api.g_date;
350 end if;
351 open csr_chk_vertex_exist(l_jurisdiction_code, l_effective_date);
352 fetch csr_chk_vertex_exist into l_vertex_exist;
353 t_effective_start_date := p_effective_date;
354 if csr_chk_vertex_exist%FOUND then
355 --
356 hr_utility.set_location(l_proc, 35);
357 --
358 pay_us_tax_internal.maintain_tax_percentage
359 (
360 p_assignment_id => p_assignment_id,
361 p_effective_date => p_effective_date,
362 p_state_code => p_state_code,
363 p_county_code => p_county_code,
364 p_city_code => p_city_code,
365 p_datetrack_mode => p_datetrack_mode,
366 p_calculate_pct => FALSE,
367 p_effective_start_date => t_effective_start_date,
368 p_effective_end_date => l_effective_end_date
369 );
370 end if;
371 --
372 hr_utility.set_location(l_proc, 70);
373 --
374 -- When in validation only mode raise the Validate_Enabled exception
375 --
376 if p_validate then
377 raise hr_api.validate_enabled;
378 end if;
379 --
380 hr_utility.set_location(' Leaving:'||l_proc, 80);
381 --
382 exception
383 --
384 when hr_api.validate_enabled then
385 --
386 -- As the Validate_Enabled exception has been raised
387 -- we must rollback to the savepoint
388 --
389 ROLLBACK TO delete_tax_rule;
390 --
391 -- Only set output warning arguments
392 -- (Any key or derived arguments must be set to null
393 -- when validation only mode is being used.)
394 --
395 when others then
396 --
397 -- A validation or unexpected error has occurred
398 --
399 ROLLBACK TO delete_tax_rule;
400 raise;
401 --
402 end delete_tax_rule;
403 --
404 -- ----------------------------------------------------------------------------
405 -- |-------------------------< submit_fed_w4 >--------------------------------|
406 -- ----------------------------------------------------------------------------
407 procedure submit_fed_w4
408 (
409 p_validate IN boolean default false
410 ,p_person_id IN number
411 ,p_effective_date IN date
412 ,p_source_name IN varchar2
413 ,p_filing_status_code IN varchar2
414 ,p_withholding_allowances IN number
415 ,p_fit_additional_tax IN number
416 ,p_fit_exempt IN varchar2
417 ,p_stat_trans_audit_id OUT nocopy pay_stat_trans_audit.stat_trans_audit_id%TYPE
418 )
419 AS
420 l_proc VARCHAR2(80) := g_package || 'submit_fed_w4';
421 lv_trans_type VARCHAR2(30) := 'US_TAX_FORMS';
422 lv_trans_subtype VARCHAR2(30) := 'W4';
423 ln_business_group_id per_people_f.business_group_id%TYPE;
424 ln_parent_audit_id pay_stat_trans_audit.stat_trans_audit_id%TYPE;
425 ln_assignment_id per_assignments_f.assignment_id%TYPE;
426 ln_gre_id hr_organization_units.organization_id%TYPE;
427 ln_fed_tax_rule_id pay_us_emp_fed_tax_rules_f.emp_fed_tax_rule_id%TYPE;
428 ln_ovn pay_us_emp_fed_tax_rules_f.object_version_number%TYPE;
429 ld_old_start_date pay_us_emp_fed_tax_rules_f.effective_start_date%TYPE;
430 ld_start_date pay_us_emp_fed_tax_rules_f.effective_start_date%TYPE;
431 ld_end_date pay_us_emp_fed_tax_rules_f.effective_end_date%TYPE;
432 lv_filing_status_code pay_us_emp_fed_tax_rules_f.filing_status_code%TYPE;
433 lv_state_filing_status_code pay_us_emp_state_tax_rules_f.filing_status_code%TYPE;
434 lv_context pay_stat_trans_audit.audit_information_category%TYPE;
435 ln_dummy NUMBER(15);
436 lv_datetrack_mode VARCHAR2(30);
437 lv_update_method VARCHAR2(30);
438 l_primary_only VARCHAR2(1);
439 lv_update_error_msg VARCHAR2(10000);
440
441 CURSOR c_get_bg_id(p_person_id number) IS
442 select business_group_id
443 from per_people_f
444 where person_id = p_person_id;
445
446 CURSOR c_fed_tax_rows IS
447 select ftr.emp_fed_tax_rule_id,
448 ftr.object_version_number,
449 ftr.effective_start_date,
450 paf.assignment_id,
451 hsck.segment1
452 from pay_us_emp_fed_tax_rules_f ftr, per_assignments_f paf,
453 hr_soft_coding_keyflex hsck
454 where paf.person_id = p_person_id
455 and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
456 and paf.assignment_id = ftr.assignment_id
457 and paf.assignment_type = 'E'
458 and decode(l_primary_only,'Y',paf.primary_flag,'Y') = 'Y'
459 and p_effective_date between paf.effective_start_date and paf.effective_end_date
460 and p_effective_date between ftr.effective_start_date and ftr.effective_end_date
461 and not exists( select 'x'
462 from hr_organization_information hoi,
463 hr_soft_coding_keyflex sck
464 where paf.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
465 and sck.segment1 = hoi.organization_id
466 and hoi.org_information_context = '1099R Magnetic Report Rules')
467 for update nowait;
468
469 CURSOR c_state_tax_rows IS
470 select str.emp_state_tax_rule_id,
471 str.object_version_number,
472 str.effective_start_date,
473 pus.state_abbrev,
474 pus.state_code,
475 paf.assignment_id,
476 stif.sta_information7,
477 hsck.segment1
478 from pay_us_emp_state_tax_rules_f str, per_assignments_f paf,
479 pay_us_state_tax_info_f stif, pay_us_states pus,
480 hr_soft_coding_keyflex hsck
481 where paf.person_id = p_person_id
482 and paf.assignment_id = str.assignment_id
483 and paf.assignment_type = 'E'
484 and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
485 and decode(l_primary_only,'Y',paf.primary_flag,'Y') = 'Y'
486 and str.state_code = stif.state_code
487 and str.state_code = pus.state_code
488 and stif.sta_information7 like 'Y%'
489 and p_effective_date between stif.effective_start_date and stif.effective_end_date
490 and p_effective_date between paf.effective_start_date and paf.effective_end_date
491 and p_effective_date between str.effective_start_date and str.effective_end_date
492 and not exists( select 'x'
493 from hr_organization_information hoi,
494 hr_soft_coding_keyflex sck
495 where paf.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
496 and sck.segment1 = hoi.organization_id
497 and hoi.org_information_context = '1099R Magnetic Report Rules')
498 for update nowait;
499
500 c_state_rec c_state_tax_rows%ROWTYPE;
501
502 BEGIN
503
504 hr_utility.set_location('Entering :' || l_proc, 10);
505 -- set a savepoint before we do anything
506 SAVEPOINT submit_fed_w4;
507
508 -- get the update method
509 lv_update_method := 'PRIMARY';
510 if lv_update_method = 'PRIMARY' then
511 l_primary_only := 'Y';
512 else
513 l_primary_only := 'N';
514 end if;
515
516 -- lock records
517 open c_state_tax_rows;
518 open c_fed_tax_rows;
519
520 -- get the bg of the person
521 open c_get_bg_id(p_person_id);
522 fetch c_get_bg_id into ln_business_group_id;
523 if c_get_bg_id%NOTFOUND then
524 close c_get_bg_id;
525 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
526 hr_utility.set_message_token('PROCEDURE',l_proc);
527 hr_utility.set_message_token('STEP','1');
528 hr_utility.raise_error;
529 end if;
530 close c_get_bg_id;
531
532 -- make sure we can update
533 lv_update_error_msg := chk_w4_allowed(p_person_id => p_person_id,
534 p_effective_date => p_effective_date,
535 p_source_name => p_source_name
536 );
537 if lv_update_error_msg is not null then
538 hr_utility.set_message(801, lv_update_error_msg);
539 hr_utility.raise_error;
540 end if;
541 /*
542 begin
543 --
544 -- Start of API User Hook for the before hook of create_state_tax_rule
545 --
546 pay_tax_bk1.submit_fed_w4_b
547 (
548 p_source_name => p_source_name
549 ,p_person_id => p_person_id
550 ,p_business_group_id => ln_business_group_id
551 ,p_filing_status_code => p_filing_status_code
552 ,p_withholding_allowances => p_withholding_allowances
553 ,p_fit_additional_tax => p_fit_additional_tax
554 ,p_fit_exempt => p_fit_exempt
555 );
556
557 exception
558 when hr_api.cannot_find_prog_unit then
559 hr_api.cannot_find_prog_unit_error
560 (
561 p_module_name => 'submit_fed_w4'
562 ,p_hook_type => 'BP'
563 );
564 --
565 -- End of API User Hook for the before hook of create_state_tax_rule
566 --
567 end;
568 */
569 hr_utility.set_location(l_proc, 20);
570 -- start by putting a master w-4 transaction event which will be the
571 -- parent for the rest of these transactions
572 pay_aud_ins.ins(
573 p_effective_date => p_effective_date
574 ,p_transaction_type => lv_trans_type
575 ,p_transaction_subtype => lv_trans_subtype
576 ,p_transaction_date => trunc(sysdate)
577 ,p_transaction_effective_date => p_effective_date
578 ,p_business_group_id => ln_business_group_id
579 ,p_person_id => p_person_id
580 ,p_source1 => '00-000-0000'
581 ,p_source1_type => 'JURISDICTION'
582 ,p_source3 => p_source_name
583 ,p_source3_type => 'SOURCE_NAME'
584 ,p_audit_information_category => 'W4 FED'
585 ,p_audit_information1 => p_filing_status_code
586 ,p_audit_information2 => fnd_number.number_to_canonical(p_withholding_allowances)
587 ,p_audit_information3 => fnd_number.number_to_canonical(p_fit_additional_tax)
588 ,p_audit_information4 => p_fit_exempt
589 ,p_stat_trans_audit_id => ln_parent_audit_id
590 ,p_object_version_number => ln_ovn
591 );
592
593 -- start by updating the fed tax records
594 FETCH c_fed_tax_rows INTO ln_fed_tax_rule_id,
595 ln_ovn,
596 ld_old_start_date,
597 ln_assignment_id,
598 ln_gre_id;
599
600 if c_fed_tax_rows%NOTFOUND then
601 close c_fed_tax_rows;
602 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
603 hr_utility.set_message_token('PROCEDURE',l_proc);
604 hr_utility.set_message_token('STEP','2');
605 hr_utility.raise_error;
606 end if;
607
608 hr_utility.set_location(l_proc, 30);
609 -- We loop on the cursor
610 WHILE c_fed_tax_rows%FOUND LOOP
611 -- We insert using datetrack mode of UPDATE
612 -- future dated records will cause an error
613 -- if the old start date = p_ef_date, we perform a correction instead
614 if ld_old_start_date = p_effective_date then
615 lv_datetrack_mode := 'CORRECTION';
616 else
617 lv_datetrack_mode := 'UPDATE';
618 end if;
619
620 hr_utility.trace('filing stat ' || ln_fed_tax_rule_id);
621 pay_federal_tax_rule_api.update_fed_tax_rule
622 (p_emp_fed_tax_rule_id => ln_fed_tax_rule_id
623 ,p_withholding_allowances => p_withholding_allowances
624 ,p_fit_additional_tax => p_fit_additional_tax
625 ,p_filing_status_code => p_filing_status_code
626 ,p_fit_exempt => p_fit_exempt
627 ,p_object_version_number => ln_ovn
628 ,p_effective_start_date => ld_start_date
629 ,p_effective_end_date => ld_end_date
630 ,p_effective_date => p_effective_date
631 ,p_datetrack_update_mode => lv_datetrack_mode
632 );
633
634 -- we insert a row into the transaction table to show the change to this assignment
635 pay_aud_ins.ins(
636 p_effective_date => p_effective_date
637 ,p_transaction_type => lv_trans_type
638 ,p_transaction_date => trunc(sysdate)
639 ,p_transaction_effective_date => p_effective_date
640 ,p_business_group_id => ln_business_group_id
641 ,p_transaction_subtype => lv_trans_subtype
642 ,p_person_id => p_person_id
643 ,p_assignment_id => ln_assignment_id
644 ,p_source1 => '00-000-0000'
645 ,p_source1_type => 'JURISDICTION'
646 ,p_source2 => fnd_number.number_to_canonical(ln_gre_id)
647 ,p_source2_type => 'GRE'
648 ,p_source3 => p_source_name
649 ,p_source3_type => 'SOURCE_NAME'
650 ,p_transaction_parent_id => ln_parent_audit_id
651 ,p_stat_trans_audit_id => ln_dummy
652 ,p_object_version_number => ln_ovn
653 );
654
655 -- as a sanity check we make sure that the dates are right
656 if (ld_start_date <> p_effective_date) or
657 (ld_end_date <> hr_api.g_eot) then
658 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
659 hr_utility.set_message_token('PROCEDURE',l_proc);
660 hr_utility.set_message_token('STEP','2');
661 hr_utility.raise_error;
662 end if;
663
664 -- we fetch the next row
665 FETCH c_fed_tax_rows INTO ln_fed_tax_rule_id,ln_ovn,ld_old_start_date,
666 ln_assignment_id, ln_gre_id;
667 end LOOP;
668
669 -- next we update state tax records
670 -- we don't update the amount withheld, because it is probably of a different magnitude
671 -- then the state taxes.
672
673 hr_utility.set_location(l_proc, 40);
674 fetch c_state_tax_rows into c_state_rec;
675
676 WHILE c_state_tax_rows%FOUND LOOP
677 if c_state_rec.effective_start_date = p_effective_date then
678 lv_datetrack_mode := 'CORRECTION';
679 else
680 lv_datetrack_mode := 'UPDATE';
681 end if;
682
683 -- We need to test whether or not the state being updated has a filing status
684 -- that we are filing. We do this by validating it in the state api. If it fails
685 -- validation, we default to single.
686
687 -- Also, if the fed type is '03', we change it to '04' for the states
688 if p_filing_status_code = '03' then
689 lv_state_filing_status_code := '04';
690 else
691 lv_state_filing_status_code := p_filing_status_code;
692 end if;
693
694 BEGIN
695
696 lv_state_filing_status_code := '04'; -- fed '03' maps to state '04'
697 pay_sta_bus.chk_filing_status_code(
698 p_emp_state_tax_rule_id => null
699 ,p_state_code => c_state_rec.state_code
700 ,p_filing_status_code => lv_state_filing_status_code
701 ,p_effective_date => p_effective_date
702 ,p_validation_start_date => p_effective_date
703 ,p_validation_end_date => hr_api.g_eot
704 );
705 EXCEPTION
706 WHEN OTHERS THEN
707 lv_state_filing_status_code := '01';
708 END;
709
710 pay_state_tax_rule_api.update_state_tax_rule
711 (p_emp_state_tax_rule_id => c_state_rec.emp_state_tax_rule_id
712 ,p_withholding_allowances => p_withholding_allowances
713 ,p_sit_additional_tax => 0
714 ,p_filing_status_code => lv_state_filing_status_code
715 ,p_sit_exempt => p_fit_exempt
716 ,p_object_version_number => c_state_rec.object_version_number
717 ,p_effective_start_date => ld_start_date
718 ,p_effective_end_date => ld_end_date
719 ,p_effective_date => p_effective_date
720 ,p_datetrack_update_mode => lv_datetrack_mode
721 );
722
723 -- when we insert into the transaction audit table, we only show
724 -- where the child record is different from the parent record
725 -- therefore, if state filing status <> fed filing status we
726 -- store it, otherwise there is nothing stored except the child
727 -- record info
728
729 if p_filing_status_code <> lv_state_filing_status_code then
730 lv_context := 'W4 FED';
731 else
732 lv_context := null;
733 lv_state_filing_status_code := null;
734 end if;
735
736
737 -- insert a row in the transaction table
738 pay_aud_ins.ins(
739 p_effective_date => p_effective_date
740 ,p_transaction_type => lv_trans_type
741 ,p_transaction_date => trunc(sysdate)
742 ,p_transaction_effective_date => p_effective_date
743 ,p_business_group_id => ln_business_group_id
744 ,p_transaction_subtype => lv_trans_subtype
745 ,p_person_id => p_person_id
746 ,p_assignment_id => c_state_rec.assignment_id
747 ,p_source1 => c_state_rec.state_code || '-000-0000'
748 ,p_source1_type => 'JURISDICTION'
749 ,p_source2 => fnd_number.number_to_canonical(c_state_rec.segment1) --gre
750 ,p_source2_type => 'GRE'
751 ,p_source3 => p_source_name
752 ,p_source3_type => 'SOURCE_NAME'
753 ,p_audit_information_category => lv_context
754 ,p_audit_information1 => lv_state_filing_status_code
755 ,p_transaction_parent_id => ln_parent_audit_id
756 ,p_stat_trans_audit_id => ln_dummy
757 ,p_object_version_number => ln_ovn
758 );
759
760 -- as a sanity check we make sure that the dates are right
761 if (ld_start_date <> p_effective_date) or
762 (ld_end_date <> hr_api.g_eot) then
763 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
764 hr_utility.set_message_token('PROCEDURE',l_proc);
765 hr_utility.set_message_token('STEP','3');
766 hr_utility.raise_error;
767 end if;
768
769 -- fetch the next row
770 fetch c_state_tax_rows into c_state_rec;
771
772 end LOOP;
773
774 -- Fire off the workflow to handle notifications
775
776 hr_utility.set_location(l_proc, 50);
777
778 pay_us_tax_wf.start_wf(p_transaction_id => ln_parent_audit_id
779 ,p_process => 'FED_W4_NOTIFICATION'
780 );
781
782 /*
783 begin
784 --
785 -- Start of API User Hook for the after hook of submit_fed_w4
786 --
787 pay_tax_bk1.submit_fed_w4_a
788 (
789 p_source_name => p_source_name
790 ,p_person_id => p_person_id
791 ,p_business_group_id => ln_business_group_id
792 ,p_filing_status_code => p_filing_status_code
793 ,p_withholding_allowances => p_withholding_allowances
794 ,p_fit_additional_tax => p_fit_additional_tax
795 ,p_fit_exempt => p_fit_exempt
796 ,p_stat_trans_audit_id => ln_parent_audit_id
797 );
798
799 exception
800 when hr_api.cannot_find_prog_unit then
801 hr_api.cannot_find_prog_unit_error
802 (
803 p_module_name => 'submit_fed_w4'
804 ,p_hook_type => 'AP'
805 );
806 --
807 -- End of API User Hook for the before hook of create_state_tax_rule
808 --
809 end;
810 */
811 if p_validate then
812 raise hr_api.validate_enabled;
813 end if;
814
815 -- Set the output variable
816 p_stat_trans_audit_id := ln_parent_audit_id;
817
818 hr_utility.trace(' Leaving: ' || l_proc);
819 EXCEPTION
820 when hr_api.validate_enabled then
821 --
822 -- As the Validate_Enabled exception has been raised
823 -- we must rollback to the savepoint
824 --
825 ROLLBACK TO submit_fed_w4;
826 p_stat_trans_audit_id := null;
827
828 when others then
829 -- A validation or unexpected error has occurred
830 --
831 ROLLBACK TO submit_fed_w4;
832 p_stat_trans_audit_id := null;
833 raise;
834
835 END submit_fed_w4;
836
837
838 -- ----------------------------------------------------------------------------
839 -- |-------------------------< chk_w4_allowed >-------------------------------|
840 -- ----------------------------------------------------------------------------
841 function chk_w4_allowed
842 (
843 p_person_id IN number
844 ,p_effective_date IN date
845 ,p_source_name IN varchar2
846 ,p_state_code IN varchar2 DEFAULT null
847 ) return fnd_new_messages.message_name%TYPE IS
848
849 l_primary_only VARCHAR2(1);
850 l_proc VARCHAR2(80) := g_package || 'chk_w4_allowed';
851
852 CURSOR c_tax_defaulting IS
853 select 'x'
854 from pay_us_emp_fed_tax_rules_f prtf,
855 per_assignments_f paf
856 where paf.person_id = p_person_id
857 and decode(l_primary_only,'Y',paf.primary_flag,'Y') = 'Y'
858 and prtf.assignment_id = paf.assignment_id
859 and prtf.effective_start_date <= p_effective_date;
860
861 CURSOR c_primary_retiree_asg IS
862 select 'x'
863 from per_assignments_f paf,
864 hr_organization_information hoi,
865 hr_soft_coding_keyflex sck
866 where paf.person_id = p_person_id
867 and paf.primary_flag = 'Y'
868 and paf.effective_end_date >= p_effective_date
869 and paf.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
870 and paf.assignment_type = 'E'
871 and sck.segment1 = hoi.organization_id
872 and hoi.org_information_context = '1099R Magnetic Report Rules';
873
874 CURSOR c_excess_over_fed IS
875 select 'x'
876 from per_assignments_f paf,
877 pay_us_emp_fed_tax_rules_f ftr
878 where paf.person_id = p_person_id
879 and ftr.assignment_id = paf.assignment_id
880 and paf.assignment_type = 'E'
881 and decode(l_primary_only,'Y',paf.primary_flag,'Y') = 'Y'
882 and p_effective_date between ftr.effective_start_date and ftr.effective_end_date
883 and p_effective_date between paf.effective_start_date and paf.effective_end_date
884 and (ftr.excessive_wa_reject_date is not null
885 or nvl(ftr.fit_override_rate,0) <> 0
886 or nvl(ftr.supp_tax_override_rate,0) <> 0
887 or nvl(ftr.fit_override_amount,0) <> 0);
888
889 CURSOR c_excess_over_state IS
890 select 'x'
891 from per_assignments_f paf,
892 pay_us_emp_state_tax_rules_f str
893 where paf.person_id = p_person_id
894 and paf.assignment_type = 'E'
895 and str.assignment_id = paf.assignment_id
896 and str.state_code = p_state_code
897 and decode(l_primary_only,'Y',paf.primary_flag,'Y') = 'Y'
898 and p_effective_date between str.effective_start_date and str.effective_end_date
899 and p_effective_date between paf.effective_start_date and paf.effective_end_date
900 and (str.excessive_wa_reject_date is not null
901 or nvl(str.sit_override_amount,0) <> 0
902 or nvl(str.sit_override_rate,0) <> 0
903 or nvl(str.sui_wage_base_override_amount,0) <> 0
904 or nvl(str.supp_tax_override_rate,0) <> 0);
905
906 CURSOR c_excess_over_state_for_fed IS
907 select 'x'
908 from per_assignments_f paf,
909 pay_us_emp_state_tax_rules_f str,
910 pay_us_state_tax_info_f stif
911 where paf.person_id = p_person_id
912 and paf.assignment_type = 'E'
913 and str.assignment_id = paf.assignment_id
914 and stif.state_code = str.state_code
915 and stif.sta_information7 like 'Y%'
916 and decode(l_primary_only,'Y',paf.primary_flag,'Y') = 'Y'
917 and p_effective_date between str.effective_start_date and str.effective_end_date
918 and p_effective_date between paf.effective_start_date and paf.effective_end_date
919 and (str.excessive_wa_reject_date is not null
920 or nvl(str.sit_override_amount,0) <> 0
921 or nvl(str.sit_override_rate,0) <> 0
922 or nvl(str.sui_wage_base_override_amount,0) <> 0
923 or nvl(str.supp_tax_override_rate,0) <> 0);
924
925 CURSOR c_future_fed_recs IS
926 select 'x'
927 from per_assignments_f paf,
928 pay_us_emp_fed_tax_rules_f ftr
929 where paf.person_id = p_person_id
930 and paf.assignment_type = 'E'
931 and ftr.assignment_id = paf.assignment_id
932 and decode(l_primary_only,'Y',paf.primary_flag,'Y') = 'Y'
933 and ftr.effective_start_date > p_effective_date
934 and p_effective_date between paf.effective_start_date and paf.effective_end_date;
935
936 CURSOR c_future_state_recs IS
937 select 'x'
938 from per_assignments_f paf,
939 pay_us_emp_state_tax_rules_f str
940 where paf.person_id = p_person_id
941 and str.assignment_id = paf.assignment_id
942 and paf.assignment_type = 'E'
943 and decode(l_primary_only,'Y',paf.primary_flag,'Y') = 'Y'
944 and str.state_code = p_state_code
945 and str.effective_start_date > p_effective_date
946 and p_effective_date between paf.effective_start_date and paf.effective_end_date;
947
948
949 CURSOR c_future_state_recs_for_fed IS
950 select 'x'
951 from per_assignments_f paf,
952 pay_us_emp_state_tax_rules_f str,
953 pay_us_state_tax_info_f stif
954 where paf.person_id = p_person_id
955 and str.assignment_id = paf.assignment_id
956 and paf.assignment_type = 'E'
957 and decode(l_primary_only,'Y',paf.primary_flag,'Y') = 'Y'
958 and stif.state_code = str.state_code
959 and stif.sta_information7 like 'Y%'
960 and str.effective_start_date > p_effective_date
961 and p_effective_date between paf.effective_start_date and paf.effective_end_date
962 and p_effective_date between stif.effective_start_date and stif.effective_end_date;
963
964 curs_dummy VARCHAR2(1);
965 lv_update_method VARCHAR2(30);
966
967 BEGIN
968 hr_utility.trace('Entering: ' || l_proc);
969
970 -- NOTE: need to replace this call with a check of the bg org flex
971 lv_update_method := 'PRIMARY';
972
973 -- check for update method set to NONE
974 hr_utility.trace(l_proc || ' - Testing W4_UPDATE_METHOD');
975
976 if lv_update_method = 'PRIMARY' then
977 l_primary_only := 'Y';
978 elsif lv_update_method = 'ALL' then
979 l_primary_only := 'N';
980 else -- update_method = NONE
981 hr_utility.trace(' Leaving: ' || l_proc || ' - Failed - Method = None');
982 return 'PAY_US_OTF_NO_UPDATE_ALLOWED';
983 end if;
984
985 -- we make sure tax records have been defaulted by the effective date
986 hr_utility.trace(l_proc || ' - Testing DEFAULT_TAX_RECORDS_CREATED');
987 open c_tax_defaulting;
988 fetch c_tax_defaulting into curs_dummy;
989
990 if c_tax_defaulting%NOTFOUND then
991 hr_utility.trace(' Leaving: ' || l_proc || ' - Failed - Defaulting has not taken place');
992 close c_tax_defaulting;
993 return 'PAY_US_OTF_NO_TAX_DEFAULTING';
994 end if;
995
996 close c_tax_defaulting;
997
998 -- check for primary assignment being a retiree assignment
999 hr_utility.trace(l_proc || ' - Testing PRIMARY_ASG = RETIREE ASG');
1000 open c_primary_retiree_asg;
1001 fetch c_primary_retiree_asg into curs_dummy;
1002
1003 if c_primary_retiree_asg%FOUND then
1004 hr_utility.trace(' Leaving: ' || l_proc || ' - Failed - Primary Assignment is a Retiree');
1005 close c_primary_retiree_asg;
1006 return 'PAY_US_OTF_RETIREE_PRIMARY_ASG';
1007 end if;
1008 close c_primary_retiree_asg;
1009
1010 -- if p_state code is null, check the federal cursors
1011 if p_state_code is null then
1012
1013 -- check for excessive wa reject date or override amounts
1014 -- Note: we don't actually check the date of the reject, just
1015 -- it's existence shuts the employee out
1016
1017 hr_utility.trace(l_proc || ' - Testing FED_EXCESSIVE_WA_REJECT_DATE/OVERRIDES');
1018 open c_excess_over_fed;
1019 fetch c_excess_over_fed into curs_dummy;
1020
1021 if c_excess_over_fed%FOUND then
1022 hr_utility.trace(' Leaving: ' || l_proc || ' - Failed - Fed Reject Date or Overrides');
1023 close c_excess_over_fed;
1024 return 'PAY_US_OTF_REJECT_DATE_OR_OVER';
1025 end if;
1026
1027 close c_excess_over_fed;
1028
1029 hr_utility.trace(l_proc || ' - Testing STATE_EXCESSIVE_WA_REJECT_DATE/OVERRIDES');
1030 open c_excess_over_state_for_fed;
1031 fetch c_excess_over_state_for_fed into curs_dummy;
1032
1033 if c_excess_over_state_for_fed%FOUND then
1034 hr_utility.trace(' Leaving: ' || l_proc || ' - Failed - State Reject Date or Overrides');
1035 close c_excess_over_state_for_fed;
1036 return 'PAY_US_OTF_REJECT_DATE_OR_OVER';
1037 end if;
1038 close c_excess_over_state_for_fed;
1039
1040 -- check for any future dated changes in non-retiree asgs for both state and fed
1041
1042 hr_utility.trace(l_proc || ' - Testing FED_FUTURE_DATED_CHANGES');
1043 open c_future_fed_recs;
1044 fetch c_future_fed_recs into curs_dummy;
1045 if c_future_fed_recs%FOUND then
1046 close c_future_fed_recs;
1047 hr_utility.trace(' Leaving: ' || l_proc || ' - Failed - Future Dated Federal Tax Records');
1048 return 'PAY_US_OTF_FUTURE_RECORDS';
1049 end if;
1050 close c_future_fed_recs;
1051
1052 hr_utility.trace(l_proc || ' - Testing STATE_FUTURE_DATED_CHANGES');
1053 open c_future_state_recs_for_fed;
1054 fetch c_future_state_recs_for_fed into curs_dummy;
1055 if c_future_state_recs_for_fed%FOUND then
1056 close c_future_state_recs_for_fed;
1057 hr_utility.trace(' Leaving: ' || l_proc || ' - Failed - Future Dated State Tax Records');
1058 return 'PAY_US_OTF_FUTURE_RECORDS';
1059 end if;
1060
1061 close c_future_state_recs_for_fed;
1062
1063 else -- check the state cursors only
1064
1065 hr_utility.trace(l_proc || ' - Testing STATE_EXCESSIVE_WA_REJECT_DATE/OVERRIDES');
1066 open c_excess_over_state;
1067 fetch c_excess_over_state into curs_dummy;
1068
1069 if c_excess_over_state%FOUND then
1070 hr_utility.trace(' Leaving: ' || l_proc || ' - Failed - State Reject Date or Overrides');
1071 close c_excess_over_state;
1072 return 'PAY_US_OTF_REJECT_DATE_OR_OVER';
1073 end if;
1074 close c_excess_over_state;
1075
1076 hr_utility.trace(l_proc || ' - Testing STATE_FUTURE_DATED_CHANGES');
1077 open c_future_state_recs;
1078 fetch c_future_state_recs into curs_dummy;
1079 if c_future_state_recs_for_fed%FOUND then
1080 close c_future_state_recs;
1081 hr_utility.trace(' Leaving: ' || l_proc || ' - Failed - Future Dated State Tax Records');
1082 return 'PAY_US_OTF_FUTURE_RECORDS';
1083 end if;
1084 close c_future_state_recs;
1085 end if;
1086
1087 -- if we've reached this point, then allow update
1088 hr_utility.trace(' Leaving: ' || l_proc || ' - Passed ');
1089 return null;
1090
1091 end chk_w4_allowed;
1092
1093 end pay_us_tax_api;