[Home] [Help]
PACKAGE BODY: APPS.PAY_CTY_BUS
Source
1 Package Body pay_cty_bus as
2 /* $Header: pyctyrhi.pkb 120.0.12000000.2 2007/05/01 22:44:28 ahanda noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_cty_bus.'; -- Global package name
9 --
10 --
11 -- ---------------------------------------------------------------------------
12 -- |------------------------< chk_assignment_id >----------------------------|
13 -- ---------------------------------------------------------------------------
14 --
15 --
16 -- Description:
17 -- - Determines if the current assignment has:
18 -- a valid assignment id as of the defaulting date.
19 --
20 -- Pre_conditions:
21 -- A valid business_group_id
22 --
23 -- In Arguments:
24 -- p_emp_county_tax_rule_id
25 -- p_business_group_id
26 -- p_assignment_id
27 -- p_effective_date
28 -- p_object_version_number
29 --
30 -- Post Success:
31 -- Process continues if :
32 -- The assignment_id is valid
33 --
34 -- Post Failure:
35 -- An application error is raised and processing is terminated
36 -- if the assignment_id is invalid.
37 --
38 -- Access Status
39 -- Internal Row Handler Use Only.
40 --
41 --
42 procedure chk_assignment_id
43 (p_emp_city_tax_rule_id in
44 pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE
45 ,p_assignment_id in
46 pay_us_emp_city_tax_rules_f.assignment_id%TYPE
47 ,p_business_group_id in
48 pay_us_emp_city_tax_rules_f.business_group_id%TYPE
49 ,p_effective_date in date
50 ,p_object_version_number in number
51 )
52 is
53 --
54 l_proc varchar2(72) := g_package||'chk_assignment_id';
55 l_tmp varchar2(2);
56 l_api_updating boolean;
57 l_assignment_id per_assignments_f.assignment_id%TYPE;
58 l_business_group_id per_assignments_f.business_group_id%TYPE;
59 --
60 --
61 cursor csr_business_grp is
62 select business_group_id
63 from per_assignments_f
64 where assignment_id = p_assignment_id
65 and p_effective_date between effective_start_date and
66 effective_end_date;
67 --
68 cursor csr_defaulting is
69 select null
70 from pay_us_emp_fed_tax_rules_f
71 where assignment_id = p_assignment_id
72 and p_effective_date between effective_start_date and
73 effective_end_date;
74 --
75 begin
76 hr_utility.set_location('Entering: '|| l_proc, 5);
77 --
78 -- Check to see if mandatory parameters have been set.
79 --
80 if p_assignment_id is null then
81 hr_utility.set_message(801,'PAY_72736_CTY_ASG_NOT_NULL');
82 hr_utility.raise_error;
83 elsif p_business_group_id is null then
84 hr_utility.set_message(801,'PAY_72738_CTY_BG_NOT_NULL');
85 hr_utility.raise_error;
86 end if;
87 --
88 hr_api.mandatory_arg_error
89 (p_api_name => l_proc
90 ,p_argument => 'effective_date'
91 ,p_argument_value => p_effective_date
92 );
93 --
94 l_api_updating := pay_cty_shd.api_updating
95 (
96 p_emp_city_tax_rule_id => p_emp_city_tax_rule_id,
97 p_effective_date => p_effective_date,
98 p_object_version_number => p_object_version_number
99 );
100 if (not l_api_updating) then
101 open csr_business_grp;
102 fetch csr_business_grp into l_business_group_id;
103 if csr_business_grp%NOTFOUND then
104 close csr_business_grp;
105 hr_utility.set_message(801, 'HR_51746_ASG_INV_ASG_ID');
106 hr_utility.raise_error;
107 else
108 --
109 close csr_business_grp;
110 --
111 if nvl(p_business_group_id, hr_api.g_number) <> l_business_group_id
112 then
113 hr_utility.set_message(801, 'PAY_72737_CTY_BG_MATCH_ASG');
114 hr_utility.raise_error;
115 else
116 open csr_defaulting;
117 fetch csr_defaulting into l_tmp;
118 if csr_defaulting%NOTFOUND then
119 close csr_defaulting;
120 hr_utility.set_message(801, 'PAY_72731_CTY_NO_FED_RULE');
121 hr_utility.raise_error;
122 end if;
123 close csr_defaulting;
124 end if;
125 end if;
126 end if;
127 hr_utility.set_location(' Leaving: '||l_proc, 65);
128 end chk_assignment_id;
129 --
130 -- ---------------------------------------------------------------------------
131 -- |---------------------------< chk_state_code >----------------------------|
132 -- ---------------------------------------------------------------------------
133 --
134 --
135 -- Description:
136 -- - Determines if the current state code has:
137 -- 1. an entry in the pay_us_states table.
138 --
139 -- Pre_conditions:
140 --
141 -- In Arguments:
142 -- p_state_code
143 --
144 -- Post Success:
145 -- Process continues if :
146 -- The state_code is valid
147 --
148 -- Post Failure:
149 -- An application error is raised and processing is terminated
150 -- if the state_code is invalid.
151 --
152 -- Access Status
153 -- Internal Row Handler Use Only.
154 --
155 --
156 procedure chk_state_code
157 (p_state_code in pay_us_emp_city_tax_rules_f.state_code%TYPE
158 )
159 is
160 --
161 l_proc varchar2(72) := g_package||'chk_state_code';
162 l_state_code pay_us_emp_city_tax_rules_f.state_code%TYPE;
163 --
164 --
165 cursor csr_valid_state_code is
166 select state_code
167 from pay_us_states
168 where state_code = p_state_code;
169 --
170 begin
171 hr_utility.set_location('Entering: '|| l_proc, 5);
172 --
173 -- Check state_code is not null.
174 --
175 --
176 if (p_state_code IS NOT NULL) then
177 hr_utility.set_location(l_proc, 10);
178 --
179 -- Check state code is valid.
180 --
181 open csr_valid_state_code;
182 fetch csr_valid_state_code into l_state_code;
183 if csr_valid_state_code%notfound then
184 close csr_valid_state_code;
185 pay_cty_shd.constraint_error
186 ('PAY_US_EMP_CITY_TAX_RULES_FK1');
187 end if;
188 close csr_valid_state_code;
189 else
190 hr_utility.set_message(801,'PAY_72752_CTY_STA_NOT_NULL');
191 hr_utility.raise_error;
192 end if;
193 hr_utility.set_location(' Leaving: '||l_proc, 20);
194 end chk_state_code;
195 --
196 --
197 -- ---------------------------------------------------------------------------
198 -- |------------------------< chk_county_code >------------------------------|
199 -- ---------------------------------------------------------------------------
200 --
201 --
202 -- Description:
203 -- - Determines if the current county_code has:
204 -- - 1. an entry in the pay_us_emp_counties table, where the state_code
205 -- matches the tax record state_code.
206 --
207 --
208 -- Pre_conditions:
209 -- A valid state_code
210 --
211 -- In Arguments:
212 -- p_state_code
213 -- p_county_code
214 --
215 -- Post Success:
216 -- Process continues if :
217 -- The county_code is valid
218 --
219 -- Post Failure:
220 -- An application error is raised and processing is terminated
221 -- if the county_code is invalid.
222 --
223 -- Access Status
224 -- Internal Row Handler Use Only.
225 --
226 --
227 procedure chk_county_code
228 (p_county_code in pay_us_emp_city_tax_rules_f.county_code%TYPE
229 ,p_state_code in pay_us_emp_city_tax_rules_f.state_code%TYPE
230 )
231 is
232 --
233 l_proc varchar2(72) := g_package||'chk_county_code';
234 l_county_code pay_us_emp_city_tax_rules_f.county_code%TYPE;
235 l_state_code pay_us_emp_city_tax_rules_f.state_code%TYPE;
236 --
237 --
238 cursor csr_valid_county_code is
239 select county_code
240 from pay_us_counties
241 where state_code = p_state_code
242 and county_code = p_county_code;
243 --
244 begin
245 hr_utility.set_location('Entering: '|| l_proc, 5);
246 --
247 -- Check to see if mandatory parameters have been set.
248 --
249 hr_api.mandatory_arg_error
250 (p_api_name => l_proc
251 ,p_argument => 'state_code'
252 ,p_argument_value => p_state_code
253 );
254 --
255 if (p_county_code IS NOT NULL) then
256 hr_utility.set_location(l_proc, 10);
257 --
258 -- Check for valid county code.
259 --
260 open csr_valid_county_code;
261 fetch csr_valid_county_code into l_county_code;
262 if csr_valid_county_code%notfound then
263 close csr_valid_county_code;
264 hr_utility.set_message(801,'HR_51279_ADD_INV_CTY_CO_ST_CMB');
265 hr_utility.raise_error;
266 end if;
267 close csr_valid_county_code;
268 hr_utility.set_location(l_proc, 20);
269 else
270 hr_utility.set_message(801,'PAY_72740_CTY_CNT_NOT_NULL');
271 hr_utility.raise_error;
272 end if;
273 hr_utility.set_location(' Leaving: '||l_proc, 25);
274 end chk_county_code;
275 --
276 -- ---------------------------------------------------------------------------
277 -- |----------------------------< chk_city_code >----------------------------|
278 -- ---------------------------------------------------------------------------
279 --
280 -- Description:
281 -- - Determines if the current city code has:
282 -- - 1. an entry in the pay_us_emp_city_geocodes table.
283 --
284 -- Pre_conditions:
285 --
286 -- In Arguments:
287 -- p_state_code
288 -- p_county_code
289 -- p_city_code
290 --
291 -- Post Success:
292 -- Process continues if :
293 -- The city_code is valid
294 --
295 -- Post Failure:
296 -- An application error is raised and processing is terminated
297 -- if the city_code is invalid.
298 --
299 -- Access Status
300 -- Internal Row Handler Use Only.
301 --
302 --
303 procedure chk_city_code
304 (p_state_code in pay_us_emp_city_tax_rules_f.state_code%TYPE
305 ,p_county_code in pay_us_emp_city_tax_rules_f.county_code%TYPE
306 ,p_city_code in pay_us_emp_city_tax_rules_f.city_code%TYPE
307 )
308 is
309 --
310 l_proc varchar2(72) := g_package||'chk_city_code';
311 l_city_code pay_us_emp_city_tax_rules_f.city_code%TYPE;
312 --
313 --
314 cursor csr_valid_city_code is
315 select city_code
316 from pay_us_city_geocodes
317 where state_code = p_state_code
318 and county_code = p_county_code
319 and city_code = p_city_code;
320 --
321 begin
322 hr_utility.set_location('Entering: '|| l_proc, 5);
323 --
324 -- Check city_code is not null.
325 --
326 if (p_city_code IS NOT NULL) then
327 hr_utility.set_location(l_proc, 10);
328 --
329 -- Check city code is valid.
330 --
331 open csr_valid_city_code;
332 fetch csr_valid_city_code into l_city_code;
333 if csr_valid_city_code%notfound then
334 close csr_valid_city_code;
335 pay_cty_shd.constraint_error
336 (p_constraint_name => 'PAY_US_EMP_CITY_TAX_RULES_FK3');
337 end if;
338 close csr_valid_city_code;
339 else
340 hr_utility.set_message(801,'PAY_72739_CTY_CTY_NOT_NULL');
341 hr_utility.raise_error;
342 end if;
343 hr_utility.set_location(' Leaving: '||l_proc, 15);
344 end chk_city_code;
345 --
346 --
347 -- ---------------------------------------------------------------------------
348 -- |---------------------------< chk_jurisdiction_code >---------------------|
349 -- ---------------------------------------------------------------------------
350 --
351 -- Description:
352 -- - Determines if the current jurisdiction_code has:
353 -- - 1. an entry in the pay_state_rules table, where the state_code
354 -- matches the tax record state_code.
355 --
356 --
357 -- Pre_conditions:
358 -- A valid state_code.
359 -- A valid county_code.
360 -- A valid city_code.
361 --
362 -- In Arguments:
363 -- p_jurisdiction_code
364 -- p_state_code
365 -- p_county_code
366 -- p_city_code
367 --
368 -- Post Success:
369 -- Process continues if :
370 -- The jurisdiction_code is valid
371 --
372 -- Post Failure:
373 -- An application error is raised and processing is terminated
374 -- if the jurisdiction_code is invalid.
375 --
376 -- Access Status
377 -- Internal Row Handler Use Only.
378 --
379 --
380 procedure chk_jurisdiction_code
381 (p_jurisdiction_code in pay_us_emp_city_tax_rules_f.jurisdiction_code%TYPE
382 ,p_city_code in pay_us_emp_city_tax_rules_f.city_code%TYPE
383 ,p_county_code in pay_us_emp_city_tax_rules_f.county_code%TYPE
384 ,p_state_code in pay_us_emp_city_tax_rules_f.state_code%TYPE)
385 is
386 --
387 l_proc varchar2(72) := g_package||'chk_jurisdiction_code';
388 l_jurisdiction number;
389 --
390 --
391 cursor csr_valid_jurisdiction_code is
392 select count(*)
393 from pay_us_city_names
394 where state_code = p_state_code
395 and county_code = p_county_code
396 and city_code = p_city_code;
397 --
398 --
399 begin
400 hr_utility.set_location('Entering: '|| l_proc, 5);
401 --
402 if p_jurisdiction_code is null then
403 hr_utility.set_message(801,'PAY_72744_CTY_JD_NOT_NULL');
404 hr_utility.raise_error;
405 end if;
406 --
407 hr_api.mandatory_arg_error
408 (p_api_name => l_proc
409 ,p_argument => 'state_code'
410 ,p_argument_value => p_state_code
411 );
412 --
413 hr_api.mandatory_arg_error
414 (p_api_name => l_proc
415 ,p_argument => 'county_code'
416 ,p_argument_value => p_county_code
417 );
418 --
419 hr_api.mandatory_arg_error
420 (p_api_name => l_proc
421 ,p_argument => 'city_code'
422 ,p_argument_value => p_city_code
423 );
424 --
425 hr_utility.set_location(l_proc, 10);
426 --
427 --
428 -- Check for valid jurisdiction code
429 --
430 open csr_valid_jurisdiction_code;
431 fetch csr_valid_jurisdiction_code into l_jurisdiction;
432 if l_jurisdiction = 0 then
433 close csr_valid_jurisdiction_code;
434 hr_utility.set_message(801,'PAY_8003_1099R_JU_CODE');
435 hr_utility.raise_error;
436 end if;
437 close csr_valid_jurisdiction_code;
438 hr_utility.set_location(l_proc, 20);
439 end chk_jurisdiction_code;
440 --
441 --
442 -- ---------------------------------------------------------------------------
443 -- |-------------------------< chk_additional_wa_rate >----------------------|
444 -- ---------------------------------------------------------------------------
445 --
446 --
447 -- Description:
448 -- - Determines if the current additional_wa_rate has:
449 -- - 1. a value between 0 and 100.
450 --
451 -- Pre_conditions: none.
452 --
453 -- In Arguments:
454 -- p_emp_city_tax_rule_id
455 -- p_additional_wa_rate
456 --
457 -- Post Success:
458 -- Process continues if :
459 --
460 -- Post Failure:
461 -- An application error is raised and processing is terminated
462 -- if the additional_wa_rate is invalid.
463 --
464 -- Access Status
465 -- Internal Row Handler Use Only.
466 --
467 procedure chk_additional_wa_rate
468 (p_emp_city_tax_rule_id in
469 pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE
470 ,p_additional_wa_rate in
471 pay_us_emp_city_tax_rules_f.additional_wa_rate%TYPE
472 )
473 is
474 --
475 l_proc varchar2(72) := g_package||'chk_additional_wa_rate';
476 l_additional_wa_rate pay_us_emp_city_tax_rules_f.additional_wa_rate%TYPE;
477 --
478 begin
479 hr_utility.set_location('Entering: '|| l_proc, 5);
480 --
481 -- If value is being inserted or updated ...
482 --
483 if (p_emp_city_tax_rule_id is not null
484 and nvl(p_additional_wa_rate,hr_api.g_number) <>
485 pay_cty_shd.g_old_rec.additional_wa_rate)
486 or (p_emp_city_tax_rule_id is null) then
487 --
488 -- Check that additional_wa_rate is not null.
489 --
490 if p_additional_wa_rate is null then
491 hr_utility.set_message(801,'PAY_72735_CTY_ADDL_WA_NOT_NULL');
492 hr_utility.raise_error;
493 else
494 --
495 -- Check that additional_wa_rate is in a valid range.
496 --
497 if p_additional_wa_rate < 0 or p_additional_wa_rate > 100 then
498 --
499 hr_utility.set_message(801,'PAY_72734_CTY_ADDL_WA_IN_RANGE');
500 hr_utility.raise_error;
501 end if;
502 end if;
503 end if;
504 hr_utility.set_location(' Leaving: '||l_proc, 15);
505 end chk_additional_wa_rate;
506 --
507 -- ---------------------------------------------------------------------------
508 -- |------------------------< chk_filing_status_code >-----------------------|
509 -- ---------------------------------------------------------------------------
510 --
511 --
512 -- Description:
513 -- - Determines if the current filing_status_code has:
514 -- - 1. an entry (lookup_code) in HR_LOOKUPS, where
515 -- lookup_type = 'US_LIT_FILING_STATUS'
516 --
517 --
518 -- Pre_conditions:
519 --
520 -- In Arguments:
521 -- p_emp_city_tax_rule_id
522 -- p_filing_status_code
523 -- p_state_code
524 -- p_effective_date
525 -- p_validation_start_date
526 -- p_validation_end_date
527 --
528 -- Post Success:
529 -- Process continues if :
530 --
531 -- Post Failure:
532 -- An application error is raised and processing is terminated
533 -- if the filing_status_code is invalid.
534 --
535 -- Access Status
536 -- Internal Row Handler Use Only.
537 --
538 procedure chk_filing_status_code
539 (p_emp_city_tax_rule_id in
540 pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE,
541 p_filing_status_code in
542 pay_us_emp_city_tax_rules_f.filing_status_code%TYPE,
543 p_state_code in pay_us_emp_city_tax_rules_f.state_code%TYPE,
544 p_effective_date in date,
545 p_validation_start_date in date,
546 p_validation_end_date in date
547 )
548 is
549 --
550 l_proc varchar2(72) := g_package||
551 'chk_filing_status_code';
552 --
553 begin
554 --
555 hr_utility.set_location('Entering: '|| l_proc, 5);
556 --
557 hr_api.mandatory_arg_error
558 (p_api_name => l_proc
559 ,p_argument => 'effective_date'
560 ,p_argument_value => p_effective_date
561 );
562 --
563 hr_api.mandatory_arg_error
564 (p_api_name => l_proc
565 ,p_argument => 'state_code'
566 ,p_argument_value => p_state_code
567 );
568 --
569 hr_api.mandatory_arg_error
570 (p_api_name => l_proc
571 ,p_argument => 'validation_start_date'
572 ,p_argument_value => p_validation_start_date
573 );
574 --
575 hr_api.mandatory_arg_error
576 (p_api_name => l_proc
577 ,p_argument => 'validation_end_date'
578 ,p_argument_value => p_validation_end_date
579 );
580 --
581 -- If value is being inserted or updated ...
582 --
583 if (p_emp_city_tax_rule_id is not null
584 and nvl(p_filing_status_code,hr_api.g_varchar2) <>
585 pay_cty_shd.g_old_rec.filing_status_code)
586 or (p_emp_city_tax_rule_id is null) then
587 --
588 -- Check that filing_status_code is not null.
589 --
590 if p_filing_status_code is null then
591 hr_utility.set_message(801,'PAY_72742_CTY_FIL_STA_NOT_NULL');
592 hr_utility.raise_error;
593 else
594 --
595 -- Check that filing_status_code is in a valid range.
596 --
597 if hr_api.not_exists_in_dt_hr_lookups
598 (p_effective_date => p_effective_date
599 ,p_validation_start_date => p_validation_start_date
600 ,p_validation_end_date => p_validation_end_date
601 ,p_lookup_type => 'US_LIT_FILING_STATUS'
602 ,p_lookup_code => p_filing_status_code
603 ) then
604 --
605 hr_utility.set_message(801,'PAY_72741_CTY_FIL_STAT_INVALID');
606 hr_utility.raise_error;
607 end if;
608 end if;
609 end if;
610
611 hr_utility.set_location(' Leaving: '||l_proc, 15);
612 end chk_filing_status_code;
613 --
614 --
615 -- ---------------------------------------------------------------------------
616 -- |------------------------< chk_lit_additional_tax >-----------------------|
617 -- ---------------------------------------------------------------------------
618 --
619 -- Description:
620 -- - Determines if the current lit_additional_tax has:
621 -- - 1. a valid value >= 0.
622 --
623 -- Pre_conditions:
624 --
625 -- In Arguments:
626 -- p_emp_city_tax_rule_id
627 -- p_lit_additional_tax
628 --
629 -- Post Success:
630 -- Process continues if :
631 -- The lit_additional_tax is valid.
632 --
633 -- Post Failure:
634 -- An application error is raised and processing is terminated
635 -- if the lit_additional_tax is invalid.
636 --
637 -- Access Status
638 -- Internal Row Handler Use Only.
639 --
640 --
641 procedure chk_lit_additional_tax
642 (p_emp_city_tax_rule_id in
643 pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE
644 ,p_lit_additional_tax in
645 pay_us_emp_city_tax_rules_f.lit_additional_tax%TYPE
646 )
647 is
648 --
649 l_proc varchar2(72) := g_package||'chk_lit_additional_tax';
650 --
651 begin
652 hr_utility.set_location('Entering: '|| l_proc, 5);
653 --
654 -- If value is being inserted or updated ...
655 --
656 if (p_emp_city_tax_rule_id is not null
657 and nvl(p_lit_additional_tax,hr_api.g_number) <>
658 pay_cty_shd.g_old_rec.lit_additional_tax)
659 or (p_emp_city_tax_rule_id is null) then
660 --
661 -- Check that lit_additional_tax is not null.
662 --
663 if p_lit_additional_tax is null then
664 hr_utility.set_message(801,'PAY_72733_CTY_ADL_TAX_NOT_NULL');
665 hr_utility.raise_error;
666 else
667 --
668 -- Check that lit_additional_tax is in a valid range.
669 --
670 if p_lit_additional_tax < 0 then
671 hr_utility.set_message(801,'PAY_72732_CTY_ADL_TAX_POSITIVE');
672 hr_utility.raise_error;
673 end if;
674 end if;
675 end if;
676
677 hr_utility.set_location(' Leaving: '||l_proc, 15);
678 end chk_lit_additional_tax;
679 --
680 -- ---------------------------------------------------------------------------
681 -- |------------------------< chk_lit_override_amount >----------------------|
682 -- ---------------------------------------------------------------------------
683 --
684 -- Description:
685 -- - Determines if the current lit_override_amount has:
686 -- - 1. a valid value >= 0.
687 --
688 -- Pre_conditions:
689 --
690 -- In Arguments:
691 -- p_emp_city_tax_rule_id
692 -- p_lit_override_amount
693 --
694 -- Post Success:
695 -- Process continues if :
696 -- The lit_override_amount is valid.
697 --
698 -- Post Failure:
699 -- An application error is raised and processing is terminated
700 -- if the lit_override_amount is invalid.
701 --
702 -- Access Status
703 -- Internal Row Handler Use Only.
704 --
705 procedure chk_lit_override_amount
706 (p_emp_city_tax_rule_id in
707 pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE
708 ,p_lit_override_amount in
709 pay_us_emp_city_tax_rules_f.lit_override_amount%TYPE
710 )
711 is
712 --
713 l_proc varchar2(72) := g_package||
714 'chk_lit_override_amount';
715 --
716 begin
717 hr_utility.set_location('Entering: '|| l_proc, 5);
718 --
719 -- If value is being inserted or updated ...
720 --
721 if (p_emp_city_tax_rule_id is not null
722 and nvl(p_lit_override_amount,hr_api.g_number) <>
723 pay_cty_shd.g_old_rec.lit_override_amount)
724 or (p_emp_city_tax_rule_id is null) then
725 --
726 -- Check that lit_override_amount is not null.
727 --
728 if p_lit_override_amount is null then
729 hr_utility.set_message(801,'PAY_72747_CTY_OVRD_AMT_NOT_NUL');
730 hr_utility.raise_error;
731 else
732 --
733 -- Check that lit_override_amount is in a valid range.
734 --
735 if p_lit_override_amount < 0 then
736 hr_utility.set_message(801,'PAY_72746_CTY_OVRD_AMT_POSTVE');
737 hr_utility.raise_error;
738 end if;
739 end if;
740 end if;
741
742 hr_utility.set_location(' Leaving: '||l_proc, 15);
743 end chk_lit_override_amount;
744 --
745 -- ---------------------------------------------------------------------------
746 -- |------------------------< chk_lit_override_rate >------------------------|
747 -- ---------------------------------------------------------------------------
748 --
749 -- Description:
750 -- - Determines if the current lit_override_rate has:
751 -- - 1. a valid value between 0 and 100.
752 --
753 -- Pre_conditions:
754 --
755 -- In Arguments:
756 -- p_emp_city_tax_rule_id
757 -- p_lit_override_rate
758 --
759 -- Post Success:
760 -- Process continues if :
761 -- The lit_override_rate is valid
762 --
763 -- Post Failure:
764 -- An application error is raised and processing is terminated
765 -- if the lit_override_rate is invalid.
766 --
767 -- Access Status
768 -- Internal Row Handler Use Only.
769 --
770 procedure chk_lit_override_rate
771 (p_emp_city_tax_rule_id in
772 pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE
773 ,p_lit_override_rate in
774 pay_us_emp_city_tax_rules_f.lit_override_rate%TYPE
775 )
776 is
777 --
778 l_proc varchar2(72) := g_package||
779 'chk_lit_override_rate';
780 --
781 begin
782 hr_utility.set_location('Entering: '|| l_proc, 5);
783 --
784 -- If value is being inserted or updated ...
785 --
786 if (p_emp_city_tax_rule_id is not null
787 and nvl(p_lit_override_rate,hr_api.g_number) <>
788 pay_cty_shd.g_old_rec.lit_override_rate)
789 or (p_emp_city_tax_rule_id is null) then
790 --
791 -- Check that lit_override_rate is not null.
792 --
793 if p_lit_override_rate is null then
794 hr_utility.set_message(801,'PAY_72749_CTY_OVRD_RT_NOT_NULL');
795 hr_utility.raise_error;
796 else
797 --
798 -- Check that lit_override_rate is in a valid range.
799 --
800 if p_lit_override_rate < 0 or p_lit_override_rate > 100 then
801 hr_utility.set_message(801,'PAY_72748_CTY_OVRD_RT_IN_RANG');
802 hr_utility.raise_error;
803 end if;
804 end if;
805 end if;
806 hr_utility.set_location(' Leaving: '||l_proc, 15);
807 end chk_lit_override_rate;
808 --
809 -- ---------------------------------------------------------------------------
810 -- |----------------------< chk_withholding_allowances >---------------------|
811 -- ---------------------------------------------------------------------------
812 --
813 -- Description:
814 -- - Determines if the current withholding_allowances has:
815 -- - 1. a valid value >= 0.
816 --
817 -- Pre_conditions:
818 --
819 -- In Arguments:
820 -- p_emp_city_tax_rule_id
821 -- p_withholding_allowances
822 --
823 -- Post Success:
824 -- Process continues if :
825 -- The withholding_allowances is valid
826 --
827 -- Post Failure:
828 -- An application error is raised and processing is terminated
829 -- if the withholding_allowances is invalid.
830 --
831 -- Access Status
832 -- Internal Row Handler Use Only.
833 --
834 procedure chk_withholding_allowances
835 (p_emp_city_tax_rule_id in
836 pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE
837 ,p_withholding_allowances in
838 pay_us_emp_city_tax_rules_f.withholding_allowances%TYPE
839 )
840 is
841 --
842 l_proc varchar2(72) := g_package||
843 'chk_withholding_allowances';
844 --
845 begin
846 hr_utility.set_location('Entering: '|| l_proc, 5);
847 --
848 -- If value is being inserted or updated ...
849 --
850 if (p_emp_city_tax_rule_id is not null
851 and nvl(p_withholding_allowances,hr_api.g_number) <>
852 pay_cty_shd.g_old_rec.withholding_allowances)
853 or (p_emp_city_tax_rule_id is null) then
854 --
855 -- Check that withholding_allowances is not null.
856 --
857 if p_withholding_allowances is null then
858 hr_utility.set_message(801,'PAY_72754_CTY_WA_NOT_NULL');
859 hr_utility.raise_error;
860 else
861 --
862 -- Check that withholding_allowances is in a valid range.
863 --
864 if p_withholding_allowances < 0 then
865 hr_utility.set_message(801,'PAY_72753_CTY_WA_POSITIVE');
866 hr_utility.raise_error;
867 end if;
868 end if;
869 end if;
870 hr_utility.set_location(' Leaving: '||l_proc, 15);
871 end chk_withholding_allowances;
872 --
873 -- ---------------------------------------------------------------------------
874 -- |------------------------< chk_school_district_code >---------------------|
875 -- ---------------------------------------------------------------------------
876 --
877 -- Description:
878 -- -This routine performs the following validation on the school district code
879 -- - a. the school district code exists in PAY_US_SCHOOL_DSTS for the
880 -- current state, county and city.
881 -- - b. the current county does not contain any specified school districts
882 -- for the given assignment, within the specified time period
883 -- - c. the current city does not contain any school districts for the
884 -- given assignment, within the specified time period, except for the
885 -- one specified in this routine
886 --
887 -- Pre_conditions:
888 --
889 -- In Arguments:
890 -- p_emp_city_tax_rule_id
891 -- p_assignment_id
892 -- p_school_district_code
893 -- p_state_code
894 -- p_county_code
895 -- p_city_code
896 -- p_effective_date
897 -- p_validation_start_date
898 -- p_validation_end_date
899 -- p_object_version_number
900 --
901 -- Post Success:
902 -- Process continues if :
903 -- The school district code is valid
904 --
905 -- Post Failure:
906 -- An application error is raised and processing is terminated
907 -- if the school district code is invalid.
908 --
909 -- Access Status
910 -- Internal Row Handler Use Only.
911 --
912 procedure chk_school_district_code
913 (p_emp_city_tax_rule_id in
914 pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%TYPE,
915 p_assignment_id in
916 pay_us_emp_city_tax_rules_f.assignment_id%TYPE,
917 p_school_district_code in
918 pay_us_emp_city_tax_rules_f.school_district_code%TYPE,
919 p_state_code in
920 pay_us_emp_city_tax_rules_f.state_code%TYPE,
921 p_county_code in
922 pay_us_emp_city_tax_rules_f.county_code%TYPE,
923 p_city_code in
924 pay_us_emp_city_tax_rules_f.city_code%TYPE,
925 p_effective_date in date,
926 p_validation_start_date in date,
927 p_validation_end_date in date,
928 p_object_version_number in
929 pay_us_emp_city_tax_rules_f.object_version_number%TYPE
930 )
931 is
932 --
933 l_proc varchar2(72) := g_package||'chk_school_district_code';
934 l_school_district_code
935 pay_us_emp_city_tax_rules_f.school_district_code%TYPE;
936 l_cty_schl_dist_code
937 pay_us_emp_city_tax_rules_f.school_district_code%TYPE;
938 l_cnt_schl_dist_code
939 pay_us_emp_county_tax_rules_f.school_district_code%TYPE;
940 l_api_updating boolean;
941 --
942 cursor csr_school_district_code (p_sta_code varchar2,
943 p_cnt_code varchar2,
944 p_cty_code varchar2,
945 p_sch_dst_code varchar2) is
946 select school_dst_code from pay_us_city_school_dsts
947 where state_code = p_sta_code
948 and county_code = p_cnt_code
949 and city_code = p_cty_code
950 and school_dst_code = p_sch_dst_code;
951 --
952 cursor csr_cty_school_district_code (p_val_start_date date,
953 p_val_end_date date,
954 p_asg_id number,
955 p_sta_code varchar2,
956 p_cnt_code varchar2,
957 p_cty_code varchar2,
958 p_tax_rule_id number) is
959 select school_district_code
960 from pay_us_emp_city_tax_rules_f ctr
961 where ctr.assignment_id = p_asg_id
962 and ctr.effective_end_date >= p_validation_start_date
963 and ctr.effective_start_date <= p_validation_end_date
964 and ctr.school_district_code is not null
965 and ctr.state_code = p_sta_code
966 and ctr.county_code = p_cnt_code
967 and ctr.city_code = p_cty_code
968 and ctr.emp_city_tax_rule_id
969 <> NVL(p_tax_rule_id, ctr.emp_city_tax_rule_id);
970 --
971 cursor csr_cnt_school_district_code (p_val_start_date date,
972 p_val_end_date date,
973 p_asg_id number,
974 p_sta_code varchar2,
975 p_cnt_code varchar2) is
976 select school_district_code
977 from pay_us_emp_county_tax_rules_f ctr
978 where ctr.assignment_id = p_asg_id
979 and ctr.effective_end_date >= p_validation_start_date
980 and ctr.effective_start_date <= p_validation_end_date
981 and ctr.school_district_code is not null
982 and ctr.state_code = p_sta_code
983 and ctr.county_code = p_cnt_code;
984 --
985 begin
986 hr_utility.set_location('Entering: '|| l_proc, 5);
987 --
988 hr_api.mandatory_arg_error
989 (p_api_name => l_proc
990 ,p_argument => 'state_code'
991 ,p_argument_value => p_state_code
992 );
993 --
994 hr_api.mandatory_arg_error
995 (p_api_name => l_proc
996 ,p_argument => 'county_code'
997 ,p_argument_value => p_county_code
998 );
999 --
1000 hr_api.mandatory_arg_error
1001 (p_api_name => l_proc
1002 ,p_argument => 'city_code'
1003 ,p_argument_value => p_city_code
1004 );
1005 --
1006 l_api_updating := pay_cty_shd.api_updating
1007 (
1008 p_effective_date => p_effective_date,
1009 p_emp_city_tax_rule_id => p_emp_city_tax_rule_id,
1010 p_object_version_number => p_object_version_number
1011 );
1012 --
1013 -- If the value is being inserted or updated...
1014 --
1015 if (l_api_updating
1016 and nvl(p_school_district_code,hr_api.g_number) <>
1017 pay_cty_shd.g_old_rec.school_district_code)
1018 or (not l_api_updating) then
1019 --
1020 -- Check school_district_code.
1021 --
1022 if p_school_district_code is not null then
1023 hr_utility.set_location(l_proc, 10);
1024 --
1025 open csr_school_district_code (p_state_code,
1026 p_county_code,
1027 p_city_code,
1028 p_school_district_code);
1029 fetch csr_school_district_code into l_school_district_code;
1030 if csr_school_district_code%found then
1031 close csr_school_district_code;
1032 open csr_cnt_school_district_code (p_validation_start_date,
1033 p_validation_end_date,
1034 p_assignment_id,
1035 p_state_code,
1036 p_county_code);
1037 fetch csr_cnt_school_district_code into l_cnt_schl_dist_code;
1038 open csr_cty_school_district_code (p_validation_start_date,
1039 p_validation_end_date,
1040 p_assignment_id,
1041 p_state_code,
1042 p_county_code,
1043 p_city_code,
1044 p_emp_city_tax_rule_id);
1045 fetch csr_cty_school_district_code into l_cty_schl_dist_code;
1046 if (csr_cty_school_district_code%found)
1047 or (csr_cnt_school_district_code%found) then
1048 close csr_cty_school_district_code;
1049 close csr_cnt_school_district_code;
1050 --
1051 hr_utility.set_message(801,'PAY_52240_TAX_SD_CHK');
1052 hr_utility.raise_error;
1053 --
1054 end if;
1055 close csr_cty_school_district_code;
1056 close csr_cnt_school_district_code;
1057 else
1058 --
1059 close csr_school_district_code;
1060 hr_utility.set_message(801,'PAY_72750_CTY_SCHL_DST_INVALID');
1061 hr_utility.raise_error;
1062 end if;
1063 end if;
1064 end if;
1065 hr_utility.set_location(' Leaving: '||l_proc, 15);
1066 end chk_school_district_code;
1067 --
1068 -- ---------------------------------------------------------------------------
1069 -- |-------------------------------< chk_delete >----------------------------|
1070 -- ---------------------------------------------------------------------------
1071 --
1072 --
1073 -- Description:
1074 -- Tax rules may be deleted from pay_us_emp_city_tax_rules_f when the
1075 -- following conditions are met:
1076 -- - no payroll has been run for this city
1077 -- - the city is not assigned to a work location
1078 -- - the city is not assigned to a primary resident address
1079 --
1080 -- Pre_conditions: None.
1081 --
1082 -- In Arguments:
1083 -- p_effective_date
1084 -- p_assignment_id
1085 -- p_city_code
1086 -- p_county_code
1087 -- p_state_code
1088 -- p_delete_mode
1089 -- p_delete_routine
1090 --
1091 -- Post Success:
1092 -- Processing continues.
1093 --
1094 --
1095 -- Post Failure:
1096 -- An application error is raised and processing is terminated.
1097 --
1098 -- Access Status
1099 -- Internal Row Handler Use Only.
1100 --
1101 procedure chk_delete
1102 (p_effective_date in date,
1103 p_assignment_id in per_assignments_f.assignment_id%TYPE,
1104 p_city_code in pay_us_emp_city_tax_rules_f.city_code%TYPE,
1105 p_county_code in pay_us_emp_city_tax_rules_f.county_code%TYPE,
1106 p_state_code in pay_us_emp_city_tax_rules_f.state_code%TYPE,
1107 p_delete_mode in varchar2,
1108 p_delete_routine in varchar2
1109 )
1110 is
1111 --
1112 l_proc varchar2(72) := g_package||'chk_delete';
1113 l_payroll_exists number;
1114 l_city_work_exists number;
1115 l_city_residence number;
1116 l_effective_date date;
1117 --
1118 cursor chk_city_payroll(p_csr_tmp_date in date)
1119 is
1120 select prr.run_result_id from
1121 pay_run_results prr,
1122 pay_assignment_actions paa,
1123 per_assignments_f paf -- Bug 3419786
1124 where paf.assignment_id = p_assignment_id -- Bug 3419786
1125 and paf.assignment_id = paa.assignment_id
1126 and prr.assignment_action_id = paa.assignment_action_id
1127 and prr.jurisdiction_code =
1128 p_state_code||'-'||p_county_code||'-'||p_city_code
1129 and exists (select null
1130 from pay_payroll_actions ppa,
1131 pay_payrolls_f ppf -- Bug 3419786
1132 where ppa.payroll_action_id = paa.payroll_action_id
1133 and ppa.action_type in ('Q','R')
1134 and ppa.date_earned > p_csr_tmp_date
1135 and ppa.payroll_id = ppf.payroll_id -- Bug 3419786
1136 and ppa.effective_date between ppf.effective_start_date
1137 and ppf.effective_end_date
1138 and ppf.payroll_id >= 0
1139 and ppf.payroll_id = paf.payroll_id
1140 );
1141 --
1142 -- Cursor to check if the city has been assigned as the state of a work
1143 -- location of the assignment.
1144 --
1145 cursor chk_city_work(p_csr_tmp_date in date)
1146 is
1147 select hrl.location_id
1148 from per_assignments_f paf,
1149 hr_locations hrl
1150 where paf.assignment_id = p_assignment_id
1151 and hrl.location_id = paf.location_id
1152 and paf.effective_end_date > p_csr_tmp_date
1153 and exists (select null
1154 from pay_us_states pus,
1155 pay_us_counties puc,
1156 pay_us_city_names pucn
1157 where pus.state_abbrev = hrl.region_2
1158 and pus.state_code = p_state_code
1159 and puc.state_code = pus.state_code
1160 and puc.county_name = hrl.region_1
1161 and puc.county_code = p_county_code
1162 and pucn.state_code = pus.state_code
1163 and pucn.county_code = puc.county_code
1164 and pucn.city_name = hrl.town_or_city
1165 and pucn.city_code = p_city_code);
1166 --
1167 --
1168 -- Cursor to check if the city has been assigned as the city of a
1169 -- resident address of the assignment.
1170 --
1171 --
1172 cursor chk_city_residence(p_csr_tmp_date in date)
1173 is
1174 select 1
1175 from per_assignments_f paf,
1176 per_addresses pa
1177 where paf.assignment_id = p_assignment_id
1178 and pa.person_id = paf.person_id
1179 and pa.primary_flag = 'Y'
1180 and nvl(pa.date_to, hr_api.g_eot) > p_csr_tmp_date
1181 and exists (select null
1182 from pay_us_states pus,
1183 pay_us_counties puco,
1184 pay_us_city_names puci
1185 where pus.state_abbrev = pa.region_2
1186 and pus.state_code = p_state_code
1187 and puco.state_code = pus.state_code
1188 and puco.county_name = pa.region_1
1189 and puco.county_code = p_county_code
1190 and puci.state_code = pus.state_code
1191 and puci.county_code = puco.county_code
1192 and puci.city_name = pa.town_or_city
1193 and puci.city_code = p_city_code);
1194 --
1195 --
1196 --
1197 begin
1198 hr_utility.set_location('Entering: '|| l_proc, 5);
1199 --
1200 if p_delete_mode in ('ZAP','DELETE') then
1201 hr_utility.set_location(l_proc, 10);
1202 if p_delete_mode = hr_api.g_zap then
1203 l_effective_date := trunc(hr_api.g_sot);
1204 else
1205 l_effective_date := trunc(p_effective_date);
1206 end if;
1207 if p_delete_mode = 'DELETE' and p_delete_routine = 'ASSIGNMENT' then
1208 open chk_city_payroll(l_effective_date);
1209 fetch chk_city_payroll into l_payroll_exists;
1210 if chk_city_payroll%found then
1211 close chk_city_payroll;
1212 hr_utility.set_message(801,'PAY_52235_TAX_RULE_DELETE');
1213 hr_utility.raise_error;
1214 end if;
1215 close chk_city_payroll;
1216 end if;
1217 end if;
1218 --
1219 if p_delete_mode = 'ZAP' and p_delete_routine <> 'ASSIGNMENT' then
1220 hr_utility.set_location(l_proc, 15);
1221 open chk_city_work(l_effective_date);
1222 fetch chk_city_work into l_city_work_exists;
1223 if chk_city_work%found then
1224 close chk_city_work;
1225 hr_utility.set_message(801,'PAY_52295_TAX_CIDEL_LOC');
1226 hr_utility.raise_error;
1227 end if;
1228 close chk_city_work;
1229 --
1230 hr_utility.set_location(l_proc, 20);
1231 open chk_city_residence(l_effective_date);
1232 fetch chk_city_residence into l_city_residence;
1233 if chk_city_residence%found then
1234 close chk_city_residence;
1235 hr_utility.set_message(801,'PAY_52298_TAX_CIDEL_RES');
1236 hr_utility.raise_error;
1237 end if;
1238 close chk_city_residence;
1239 end if;
1240 --
1241 if p_delete_mode <> 'ZAP' and p_delete_routine <> 'ASSIGNMENT' then
1242 hr_utility.set_message(801,'PAY_52971_TAX_ZAP_ONLY');
1243 hr_utility.raise_error;
1244 end if;
1245 hr_utility.set_location(' Leaving: '||l_proc, 45);
1246 --
1247 end chk_delete;
1248 --
1249 --
1250 -- ---------------------------------------------------------------------------
1251 -- |-----------------------< chk_non_updateable_args >-----------------------|
1252 -- ---------------------------------------------------------------------------
1253 --
1254 --
1255 -- Description: This procedure checks that columns where updates are not
1256 -- allowed, have not been changed from their original value.
1257 --
1258 -- Pre_conditions: None
1259 --
1260 -- In Arguments:
1261 -- p_rec
1262 -- p_effective_date
1263 --
1264 -- Post Success:
1265 -- Processing continues.
1266 --
1267 -- Post Failure:
1268 -- An application error is raised and processing is terminated.
1269 --
1270 -- Access Status
1271 -- Internal Row Handler Use Only.
1272 --
1273 --
1274 Procedure chk_non_updateable_args
1275 (p_rec in pay_cty_shd.g_rec_type,
1276 p_effective_date in date
1277 ) is
1278 --
1279 l_proc varchar2(72) := g_package||'chk_non_updateable_args';
1280 l_error exception;
1281 l_argument varchar2(30);
1282 --
1283 Begin
1284 hr_utility.set_location('Entering: '||l_proc, 10);
1285 --
1286 --
1287 if not pay_cty_shd.api_updating
1288 (p_emp_city_tax_rule_id => p_rec.emp_city_tax_rule_id,
1289 p_object_version_number => p_rec.object_version_number,
1290 p_effective_date => p_effective_date
1291 ) then
1292 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1293 hr_utility.set_message_token('PROCEDURE', l_proc);
1294 hr_utility.set_message_token('STEP',20);
1295 end if;
1296 hr_utility.set_location(l_proc, 30);
1297 --
1298 if (nvl(p_rec.business_group_id, hr_api.g_number) <>
1299 pay_cty_shd.g_old_rec.business_group_id) then
1300 l_argument := 'business_group_id';
1301 raise l_error;
1302 end if;
1303 hr_utility.set_location(l_proc, 40);
1304 --
1305 if (nvl(p_rec.assignment_id, hr_api.g_number) <>
1306 pay_cty_shd.g_old_rec.assignment_id) then
1307 l_argument := 'assignment_id';
1308 raise l_error;
1309 end if;
1310 hr_utility.set_location(l_proc, 50);
1311 --
1312 if (nvl(p_rec.state_code, hr_api.g_varchar2) <>
1313 pay_cty_shd.g_old_rec.state_code) then
1314 l_argument := 'state_code';
1315 raise l_error;
1316 end if;
1317 hr_utility.set_location(l_proc, 60);
1318 --
1319 if (nvl(p_rec.county_code, hr_api.g_varchar2) <>
1320 pay_cty_shd.g_old_rec.county_code) then
1321 l_argument := 'county_code';
1322 raise l_error;
1323 end if;
1324 hr_utility.set_location(l_proc, 70);
1325 --
1326 if (nvl(p_rec.city_code, hr_api.g_varchar2) <>
1327 pay_cty_shd.g_old_rec.city_code) then
1328 l_argument := 'city_code';
1329 raise l_error;
1330 end if;
1331 hr_utility.set_location(l_proc, 80);
1332 --
1333 if (nvl(p_rec.jurisdiction_code, hr_api.g_varchar2) <>
1334 pay_cty_shd.g_old_rec.jurisdiction_code) then
1335 l_argument := 'jurisdiction_code';
1336 raise l_error;
1337 end if;
1338 hr_utility.set_location('Leaving: '||l_proc, 90);
1339 --
1340 exception
1341 when l_error then
1342 hr_api.argument_changed_error
1343 (p_api_name => l_proc,
1344 p_argument => l_argument
1345 );
1346 when others then
1347 raise;
1348 end chk_non_updateable_args;
1349 --
1350 --
1351 -- ----------------------------------------------------------------------------
1352 -- |--------------------------< dt_update_validate >--------------------------|
1353 -- ----------------------------------------------------------------------------
1354 -- {Start Of Comments}
1355 --
1356 -- Description:
1357 -- This procedure is used for referential integrity of datetracked
1358 -- parent entities when a datetrack update operation is taking place
1359 -- and where there is no cascading of update defined for this entity.
1360 --
1361 -- Prerequisites:
1362 -- This procedure is called from the update_validate.
1363 --
1364 -- In Parameters:
1365 --
1366 -- Post Success:
1367 -- Processing continues.
1368 --
1369 -- Post Failure:
1370 --
1371 -- Developer Implementation Notes:
1372 -- This procedure should not need maintenance unless the HR Schema model
1373 -- changes.
1374 --
1375 -- Access Status:
1376 -- Internal Row Handler Use Only.
1377 --
1378 -- {End Of Comments}
1379 -- ----------------------------------------------------------------------------
1380 Procedure dt_update_validate
1381 (
1382 p_datetrack_mode in varchar2,
1383 p_validation_start_date in date,
1384 p_validation_end_date in date) Is
1385 --
1386 l_proc varchar2(72) := g_package||'dt_update_validate';
1387 l_integrity_error Exception;
1388 l_table_name all_tables.table_name%TYPE;
1389 --
1390 Begin
1391 hr_utility.set_location('Entering:'||l_proc, 5);
1392 --
1393 -- Ensure that the p_datetrack_mode argument is not null
1394 --
1395 hr_api.mandatory_arg_error
1396 (p_api_name => l_proc,
1397 p_argument => 'datetrack_mode',
1398 p_argument_value => p_datetrack_mode);
1399 --
1400 -- Only perform the validation if the datetrack update mode is valid
1401 --
1402 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
1403 --
1404 --
1405 -- Ensure the arguments are not null
1406 --
1407 hr_api.mandatory_arg_error
1408 (p_api_name => l_proc,
1409 p_argument => 'validation_start_date',
1410 p_argument_value => p_validation_start_date);
1411 --
1412 hr_api.mandatory_arg_error
1413 (p_api_name => l_proc,
1414 p_argument => 'validation_end_date',
1415 p_argument_value => p_validation_end_date);
1416 --
1417 --
1418 --
1419 End If;
1420 --
1421 hr_utility.set_location(' Leaving:'||l_proc, 10);
1422 Exception
1423 When l_integrity_error Then
1424 --
1425 -- A referential integrity check was violated therefore
1426 -- we must error
1427 --
1428 hr_utility.set_message(801, 'HR_7216_DT_UPD_INTEGRITY_ERR');
1429 hr_utility.set_message_token('TABLE_NAME', l_table_name);
1430 hr_utility.raise_error;
1431 When Others Then
1432 --
1433 -- An unhandled or unexpected error has occurred which
1434 -- we must report
1435 --
1436 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1437 hr_utility.set_message_token('PROCEDURE', l_proc);
1438 hr_utility.set_message_token('STEP','15');
1439 hr_utility.raise_error;
1440 End dt_update_validate;
1441 --
1442 -- ----------------------------------------------------------------------------
1443 -- |--------------------------< dt_delete_validate >--------------------------|
1444 -- ----------------------------------------------------------------------------
1445 -- {Start Of Comments}
1446 --
1447 -- Description:
1448 -- This procedure is used for referential integrity of datetracked
1449 -- child entities when either a datetrack DELETE or ZAP is in operation
1450 -- and where there is no cascading of delete defined for this entity.
1451 -- For the datetrack mode of DELETE or ZAP we must ensure that no
1452 -- datetracked child rows exist between the validation start and end
1453 -- dates.
1454 --
1455 -- Prerequisites:
1456 -- This procedure is called from the delete_validate.
1457 --
1458 -- In Parameters:
1459 --
1460 -- Post Success:
1461 -- Processing continues.
1462 --
1463 -- Post Failure:
1464 -- If a row exists by determining the returning Boolean value from the
1465 -- generic dt_api.rows_exist function then we must supply an error via
1466 -- the use of the local exception handler l_rows_exist.
1467 --
1468 -- Developer Implementation Notes:
1469 -- This procedure should not need maintenance unless the HR Schema model
1470 -- changes.
1471 --
1472 -- Access Status:
1473 -- Internal Row Handler Use Only.
1474 --
1475 -- {End Of Comments}
1476 -- ----------------------------------------------------------------------------
1477 Procedure dt_delete_validate
1478 (p_emp_city_tax_rule_id in number,
1479 p_datetrack_mode in varchar2,
1480 p_validation_start_date in date,
1481 p_validation_end_date in date) Is
1482 --
1483 l_proc varchar2(72) := g_package||'dt_delete_validate';
1484 l_rows_exist Exception;
1485 l_table_name all_tables.table_name%TYPE;
1486 --
1487 Begin
1488 hr_utility.set_location('Entering:'||l_proc, 5);
1489 --
1490 -- Ensure that the p_datetrack_mode argument is not null
1491 --
1492 hr_api.mandatory_arg_error
1493 (p_api_name => l_proc,
1494 p_argument => 'datetrack_mode',
1495 p_argument_value => p_datetrack_mode);
1496 --
1497 -- Only perform the validation if the datetrack mode is either
1498 -- DELETE or ZAP
1499 --
1500 If (p_datetrack_mode = 'DELETE' or
1501 p_datetrack_mode = 'ZAP') then
1502 --
1503 --
1504 -- Ensure the arguments are not null
1505 --
1506 hr_api.mandatory_arg_error
1507 (p_api_name => l_proc,
1508 p_argument => 'validation_start_date',
1509 p_argument_value => p_validation_start_date);
1510 --
1511 hr_api.mandatory_arg_error
1512 (p_api_name => l_proc,
1513 p_argument => 'validation_end_date',
1514 p_argument_value => p_validation_end_date);
1515 --
1516 hr_api.mandatory_arg_error
1517 (p_api_name => l_proc,
1518 p_argument => 'emp_city_tax_rule_id',
1519 p_argument_value => p_emp_city_tax_rule_id);
1520 --
1521 --
1522 --
1523 End If;
1524 --
1525 hr_utility.set_location(' Leaving:'||l_proc, 10);
1526 Exception
1527 When l_rows_exist Then
1528 --
1529 -- A referential integrity check was violated therefore
1530 -- we must error
1531 --
1532 hr_utility.set_message(801, 'HR_7215_DT_CHILD_EXISTS');
1533 hr_utility.set_message_token('TABLE_NAME', l_table_name);
1534 hr_utility.raise_error;
1535 When Others Then
1536 --
1537 -- An unhandled or unexpected error has occurred which
1538 -- we must report
1539 --
1540 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1541 hr_utility.set_message_token('PROCEDURE', l_proc);
1542 hr_utility.set_message_token('STEP','15');
1543 hr_utility.raise_error;
1544 End dt_delete_validate;
1545 --
1546 -- ----------------------------------------------------------------------------
1547 -- |---------------------------< insert_validate >----------------------------|
1548 -- ----------------------------------------------------------------------------
1549 Procedure insert_validate
1550 (p_rec in pay_cty_shd.g_rec_type,
1551 p_effective_date in date,
1552 p_datetrack_mode in varchar2,
1553 p_validation_start_date in date,
1554 p_validation_end_date in date) is
1555 --
1556 l_proc varchar2(72) := g_package||'insert_validate';
1557 --
1558 Begin
1559 hr_utility.set_location('Entering:'||l_proc, 5);
1560 --
1561 -- Call all supporting business operations
1562 --
1563 --
1564 chk_assignment_id
1565 (
1566 p_emp_city_tax_rule_id => p_rec.emp_city_tax_rule_id,
1567 p_assignment_id => p_rec.assignment_id,
1568 p_business_group_id => p_rec.business_group_id,
1569 p_effective_date => p_effective_date,
1570 p_object_version_number => p_rec.object_version_number
1571 );
1572 --
1573 chk_state_code
1574 (p_state_code => p_rec.state_code
1575 );
1576 --
1577 chk_county_code
1578 (p_county_code => p_rec.county_code,
1579 p_state_code => p_rec.state_code
1580 );
1581 --
1582 chk_city_code
1583 (p_city_code => p_rec.city_code,
1584 p_county_code => p_rec.county_code,
1585 p_state_code => p_rec.state_code
1586 );
1587 --
1588 chk_jurisdiction_code
1589 (p_jurisdiction_code => p_rec.jurisdiction_code,
1590 p_city_code => p_rec.city_code,
1591 p_county_code => p_rec.county_code,
1592 p_state_code => p_rec.state_code
1593 );
1594 --
1595 chk_additional_wa_rate
1596 (p_emp_city_tax_rule_id => p_rec.emp_city_tax_rule_id,
1597 p_additional_wa_rate => p_rec.additional_wa_rate
1598 );
1599 --
1600 chk_filing_status_code
1601 (p_emp_city_tax_rule_id => p_rec.emp_city_tax_rule_id,
1602 p_filing_status_code => p_rec.filing_status_code,
1603 p_state_code => p_rec.state_code,
1604 p_effective_date => p_effective_date,
1605 p_validation_start_date => p_validation_start_date,
1606 p_validation_end_date => p_validation_end_date
1607 );
1608 --
1609 chk_lit_additional_tax
1610 (p_emp_city_tax_rule_id => p_rec.emp_city_tax_rule_id,
1611 p_lit_additional_tax => p_rec.lit_additional_tax
1612 );
1613 --
1614 chk_lit_override_amount
1615 (p_emp_city_tax_rule_id => p_rec.emp_city_tax_rule_id,
1616 p_lit_override_amount => p_rec.lit_override_amount
1617 );
1618 --
1619 chk_lit_override_rate
1620 (p_emp_city_tax_rule_id => p_rec.emp_city_tax_rule_id,
1621 p_lit_override_rate => p_rec.lit_override_rate
1622 );
1623 --
1624 chk_withholding_allowances
1625 (p_emp_city_tax_rule_id => p_rec.emp_city_tax_rule_id,
1626 p_withholding_allowances => p_rec.withholding_allowances
1627 );
1628 --
1629 chk_school_district_code
1630 (p_emp_city_tax_rule_id => p_rec.emp_city_tax_rule_id,
1631 p_assignment_id => p_rec.assignment_id,
1632 p_school_district_code => p_rec.school_district_code,
1633 p_state_code => p_rec.state_code,
1634 p_county_code => p_rec.county_code,
1635 p_city_code => p_rec.city_code,
1636 p_effective_date => p_effective_date,
1637 p_validation_start_date => p_validation_start_date,
1638 p_validation_end_date => p_validation_end_date,
1639 p_object_version_number => p_rec.object_version_number
1640 );
1641 --
1642 --
1643 hr_utility.set_location(' Leaving:'||l_proc, 10);
1644 End insert_validate;
1645 --
1646 -- ----------------------------------------------------------------------------
1647 -- |---------------------------< update_validate >----------------------------|
1648 -- ----------------------------------------------------------------------------
1649 Procedure update_validate
1650 (p_rec in pay_cty_shd.g_rec_type,
1651 p_effective_date in date,
1652 p_datetrack_mode in varchar2,
1653 p_validation_start_date in date,
1654 p_validation_end_date in date) is
1655 --
1656 l_proc varchar2(72) := g_package||'update_validate';
1657 --
1658 Begin
1659 hr_utility.set_location('Entering:'||l_proc, 5);
1660 --
1661 -- Call all supporting business operations
1662 --
1663 chk_non_updateable_args
1664 (p_rec => p_rec,
1665 p_effective_date => p_effective_date
1666 );
1667 --
1668 chk_additional_wa_rate
1669 (p_emp_city_tax_rule_id => p_rec.emp_city_tax_rule_id,
1670 p_additional_wa_rate => p_rec.additional_wa_rate
1671 );
1672 --
1673 chk_filing_status_code
1674 (p_emp_city_tax_rule_id => p_rec.emp_city_tax_rule_id,
1675 p_filing_status_code => p_rec.filing_status_code,
1676 p_state_code => p_rec.state_code,
1677 p_effective_date => p_effective_date,
1678 p_validation_start_date => p_validation_start_date,
1679 p_validation_end_date => p_validation_end_date
1680 );
1681 --
1682 chk_lit_additional_tax
1683 (p_emp_city_tax_rule_id => p_rec.emp_city_tax_rule_id,
1684 p_lit_additional_tax => p_rec.lit_additional_tax
1685 );
1686 --
1687 chk_lit_override_amount
1688 (p_emp_city_tax_rule_id => p_rec.emp_city_tax_rule_id,
1689 p_lit_override_amount => p_rec.lit_override_amount
1690 );
1691 --
1692 chk_lit_override_rate
1693 (p_emp_city_tax_rule_id => p_rec.emp_city_tax_rule_id,
1694 p_lit_override_rate => p_rec.lit_override_rate
1695 );
1696 --
1697 chk_withholding_allowances
1698 (p_emp_city_tax_rule_id => p_rec.emp_city_tax_rule_id,
1699 p_withholding_allowances => p_rec.withholding_allowances
1700 );
1701 --
1702 chk_school_district_code
1703 (p_emp_city_tax_rule_id => p_rec.emp_city_tax_rule_id,
1704 p_assignment_id => p_rec.assignment_id,
1705 p_school_district_code => p_rec.school_district_code,
1706 p_state_code => p_rec.state_code,
1707 p_county_code => p_rec.county_code,
1708 p_city_code => p_rec.city_code,
1709 p_effective_date => p_effective_date,
1710 p_validation_start_date => p_validation_start_date,
1711 p_validation_end_date => p_validation_end_date,
1712 p_object_version_number => p_rec.object_version_number
1713 );
1714 --
1715 -- Call the datetrack update integrity operation
1716 --
1717 dt_update_validate
1718 (
1719 p_datetrack_mode => p_datetrack_mode,
1720 p_validation_start_date => p_validation_start_date,
1721 p_validation_end_date => p_validation_end_date);
1722 --
1723 hr_utility.set_location(' Leaving:'||l_proc, 10);
1724 End update_validate;
1725 --
1726 -- ----------------------------------------------------------------------------
1727 -- |---------------------------< delete_validate >----------------------------|
1728 -- ----------------------------------------------------------------------------
1729 Procedure delete_validate
1730 (p_rec in pay_cty_shd.g_rec_type,
1731 p_effective_date in date,
1732 p_datetrack_mode in varchar2,
1733 p_delete_routine in varchar2,
1734 p_validation_start_date in date,
1735 p_validation_end_date in date) is
1736 --
1737 l_proc varchar2(72) := g_package||'delete_validate';
1738 --
1739 Begin
1740 hr_utility.set_location('Entering:'||l_proc, 5);
1741 --
1742 -- Call all supporting business operations
1743 --
1744 chk_delete
1745 (p_effective_date => p_effective_date,
1746 p_assignment_id => pay_cty_shd.g_old_rec.assignment_id,
1747 p_city_code => pay_cty_shd.g_old_rec.city_code,
1748 p_county_code => pay_cty_shd.g_old_rec.county_code,
1749 p_state_code => pay_cty_shd.g_old_rec.state_code,
1750 p_delete_mode => p_datetrack_mode,
1751 p_delete_routine => p_delete_routine
1752 );
1753 --
1754 hr_utility.set_location(l_proc, 10);
1755 --
1756 dt_delete_validate
1757 (p_datetrack_mode => p_datetrack_mode,
1758 p_validation_start_date => p_validation_start_date,
1759 p_validation_end_date => p_validation_end_date,
1760 p_emp_city_tax_rule_id => p_rec.emp_city_tax_rule_id);
1761 --
1762 hr_utility.set_location(' Leaving:'||l_proc, 15);
1763 End delete_validate;
1764 --
1765 --
1766 -- ---------------------------------------------------------------------------
1767 -- |---------------------< return_legislation_code >-------------------------|
1768 -- ---------------------------------------------------------------------------
1769 --
1770 function return_legislation_code
1771 (p_emp_city_tax_rule_id in number) return varchar2 is
1772 --
1773 -- Declare cursor
1774 --
1775 cursor csr_leg_code is
1776 select a.legislation_code
1777 from per_business_groups a,
1778 pay_us_emp_city_tax_rules_f b
1779 where b.emp_city_tax_rule_id = p_emp_city_tax_rule_id
1780 and a.business_group_id = b.business_group_id;
1781 --
1782 -- Declare local variables
1783 --
1784 l_legislation_code varchar2(150);
1785 l_proc varchar2(72) := g_package||'return_legislation_code';
1786 --
1787 begin
1788 --
1789 hr_utility.set_location('Entering:'|| l_proc, 10);
1790 --
1791 -- Ensure that all the mandatory parameter are not null
1792 --
1793 hr_api.mandatory_arg_error(p_api_name => l_proc,
1794 p_argument => 'emp_city_tax_rule_id',
1795 p_argument_value => p_emp_city_tax_rule_id);
1796 --
1797 if nvl(g_cty_tax_rule_id, hr_api.g_number) = p_emp_city_tax_rule_id then
1798 --
1799 -- The legislation code has already been found with a previous
1800 -- call to this function. Just return the value in the global
1801 -- variable.
1802 --
1803 l_legislation_code := g_legislation_code;
1804 hr_utility.set_location(l_proc, 20);
1805 --
1806 else
1807 --
1808 -- The ID is different to the last call to this function
1809 -- or this is the first call to this function.
1810 --
1811 open csr_leg_code;
1812 --
1813 fetch csr_leg_code into l_legislation_code;
1814 --
1815 if csr_leg_code%notfound then
1816 --
1817 close csr_leg_code;
1818 --
1819 -- The primary key is invalid therefore we must error
1820 --
1821 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
1822 hr_utility.raise_error;
1823 --
1824 end if;
1825 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1826 --
1827 -- Set the global variables to the values are
1828 -- available for the next call to this function
1829 --
1830 close csr_leg_code;
1831 g_cty_tax_rule_id := p_emp_city_tax_rule_id;
1832 g_legislation_code := l_legislation_code;
1833 end if;
1834 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1835 --
1836 return l_legislation_code;
1837 --
1838 end return_legislation_code;
1839 end pay_cty_bus;