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