[Home] [Help]
PACKAGE BODY: APPS.PAY_STA_BUS
Source
1 Package Body pay_sta_bus as
2 /* $Header: pystarhi.pkb 120.2.12020000.2 2012/07/05 02:46:41 amnaraya ship $ */
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
143 select null
140 and asg.effective_end_date;
141 --
142 cursor c2 is
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
304 -- table. Existence of state tax record was checked in a
301 close csr_get_state_abbrev;
302 --
303 -- Raise error as FK does not relate to PK in pay_us_states
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) <>
460 pay_sta_shd.g_old_rec.additional_wa_amount)
461 or (p_emp_state_tax_rule_id is null) then
462 --
463 -- Check that the mandatory parameters have been set
464 --
465 if p_additional_wa_amount is null then
469 --
466 hr_utility.set_message(801, 'PAY_72805_STA_ADDL_WA_NOT_NULL');
467 hr_utility.raise_error;
468 end if;
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 >------------------------|
608 -- ----------------------------------------------------------------------------
609 --
610 -- Description
611 -- This procedure checks that sit_additional_tax >= 0
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
630 (p_emp_state_tax_rule_id in number
627 -- Internal table handler use only.
628 --
629 Procedure chk_sit_additional_tax
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 /* Commented the check for -ve amount to fix bug#13836628
656 --
657 -- check if sit_additional_tax value is in a valid range
658 --
659 if p_sit_additional_tax < 0 then
660 --
661 -- raise error as given value is invalid
662 --
663 hr_utility.set_message(801,'PAY_72802_STA_ADD_TAX_POSITIVE');
664 hr_utility.raise_error;
665 --
666 end if;
667 --
668 */
669 end if;
670 --
671 hr_utility.set_location('Leaving:'||l_proc,10);
672 --
673 End chk_sit_additional_tax;
674 --
675 -- ----------------------------------------------------------------------------
676 -- |------------------------< chk_sit_override_amount >-----------------------|
677 -- ----------------------------------------------------------------------------
678 --
679 -- Description
680 -- This procedure checks that sit_override_amount >= 0
681 --
682 -- Pre-Conditions
683 -- None.
684 --
685 -- In Parameters
686 -- p_emp_state_tax_rule_id PK
687 -- p_sit_override_amount
688 --
689 -- Post Success
690 -- Processing continues
691 --
692 -- Post Failure
693 -- Error raised.
694 --
695 -- Access Status
696 -- Internal table handler use only.
697 --
698 Procedure chk_sit_override_amount
699 (p_emp_state_tax_rule_id in number
700 ,p_sit_override_amount in
701 pay_us_emp_state_tax_rules_f.sit_override_amount%TYPE
702 ) is
703 --
704 l_proc varchar2(72) := g_package||'chk_sit_override_amount';
705 --
706 Begin
707 --
708 hr_utility.set_location('Entering:'||l_proc,5);
709 --
710 -- If the value is being inserted or updated...
711 --
712 if (p_emp_state_tax_rule_id is not null
713 and nvl(p_sit_override_amount,hr_api.g_number)
714 <> nvl(pay_sta_shd.g_old_rec.sit_override_amount,hr_api.g_number))
715 or (p_emp_state_tax_rule_id is null) then
716 --
717 -- Check that the mandatory parameters have been set
718 --
719 if p_sit_override_amount is null then
720 hr_utility.set_message(801, 'PAY_72814_STA_OVD_AMT_NOT_NULL');
721 hr_utility.raise_error;
722 end if;
723 --
724 -- check if sit_override_amount value is in a valid range
725 --
726 if p_sit_override_amount < 0 then
727 --
728 -- raise error as given value is invalid
729 --
730 hr_utility.set_message(801,'PAY_72813_STA_OVD_AMT_POSITIVE');
731 hr_utility.raise_error;
732 --
733 end if;
734 --
735 end if;
736 --
737 hr_utility.set_location('Leaving:'||l_proc,10);
738 --
739 End chk_sit_override_amount;
740 --
741 -- ----------------------------------------------------------------------------
742 -- |-----------------------< chk_sit_override_rate >--------------------------|
743 -- ----------------------------------------------------------------------------
744 --
745 -- Description
746 -- This procedure checks that sit_override_rate is between 0 and 100
747 --
748 -- Pre-Conditions
749 -- None.
750 --
751 -- In Parameters
752 -- p_emp_state_tax_rule_id PK
753 -- p_sit_override_rate
754 --
755 -- Post Success
756 -- Processing continues
757 --
758 -- Post Failure
759 -- Error raised.
760 --
761 -- Access Status
762 -- Internal table handler use only.
763 --
764 Procedure chk_sit_override_rate
765 (p_emp_state_tax_rule_id in number
766 ,p_sit_override_rate in
767 pay_us_emp_state_tax_rules_f.sit_override_rate%TYPE
768 ) is
769 --
770 l_proc varchar2(72) := g_package||'chk_sit_override_rate';
771 --
772 Begin
773 --
774 hr_utility.set_location('Entering:'||l_proc,5);
775 --
776 -- If the value is being inserted or updated...
777 --
778 if (p_emp_state_tax_rule_id is not null
779 and nvl(p_sit_override_rate,hr_api.g_number)
780 <> nvl(pay_sta_shd.g_old_rec.sit_override_rate,hr_api.g_number))
781 or (p_emp_state_tax_rule_id is null) then
782 --
783 -- Check that the mandatory parameters have been set
784 --
785 if p_sit_override_rate is null then
786 hr_utility.set_message(801, 'PAY_72816_STA_OVRD_RT_NOT_NULL');
790 -- check if sit_override_rate value is in a valid range
787 hr_utility.raise_error;
788 end if;
789 --
791 --
792 if p_sit_override_rate < 0 or p_sit_override_rate > 100 then
793 --
794 -- raise error as given value is invalid
795 --
796 hr_utility.set_message(801,'PAY_72815_STA_OVRD_RT_IN_RANGE');
797 hr_utility.raise_error;
798 --
799 end if;
800 --
801 end if;
802 --
803 hr_utility.set_location('Leaving:'||l_proc,10);
804 --
805 End chk_sit_override_rate;
806 --
807 -- ----------------------------------------------------------------------------
808 -- |------------------------< chk_remainder_percent >-------------------------|
809 -- ----------------------------------------------------------------------------
810 --
811 -- Description
812 -- This procedure checks that remainder_percent is between 0 and 100
813 --
814 -- Pre-Conditions
815 -- None.
816 --
817 -- In Parameters
818 -- p_emp_state_tax_rule_id PK
819 -- p_remainder_percent
820 --
821 -- Post Success
822 -- Processing continues
823 --
824 -- Post Failure
825 -- Error raised.
826 --
827 -- Access Status
828 -- Internal table handler use only.
829 --
830 Procedure chk_remainder_percent
831 (p_emp_state_tax_rule_id in number
832 ,p_remainder_percent in
833 pay_us_emp_state_tax_rules_f.remainder_percent%TYPE
834 ) is
835 --
836 l_proc varchar2(72) := g_package||'chk_remainder_percent';
837 --
838 Begin
839 --
840 hr_utility.set_location('Entering:'||l_proc,5);
841 --
842 -- If the value is being inserted or updated...
843 --
844 if (p_emp_state_tax_rule_id is not null
845 and nvl(p_remainder_percent,hr_api.g_number)
846 <> nvl(pay_sta_shd.g_old_rec.remainder_percent,hr_api.g_number))
847 or (p_emp_state_tax_rule_id is null) then
848 --
849 -- Check that the mandatory parameters have been set
850 --
851 if p_remainder_percent is null then
852 hr_utility.set_message(801, 'PAY_72818_STA_REM_PCT_NOT_NULL');
853 hr_utility.raise_error;
854 end if;
855 --
856 -- check if remainder_percent value is in a valid range
857 --
858 if p_remainder_percent < 0 or p_remainder_percent > 100 then
859 --
860 -- raise error as given value is invalid
861 --
862 hr_utility.set_message(801,'PAY_72817_STA_REM_PCT_IN_RANGE');
863 hr_utility.raise_error;
864 --
865 end if;
866 --
867 end if;
868 --
869 hr_utility.set_location('Leaving:'||l_proc,10);
870 --
871 End chk_remainder_percent;
872 --
873 -- ----------------------------------------------------------------------------
874 -- |---------------------------< chk_secondary_wa >---------------------------|
875 -- ----------------------------------------------------------------------------
876 --
877 -- Description
878 -- This procedure checks that secondary_wa >= 0
879 --
880 -- Pre-Conditions
881 -- None.
882 --
883 -- In Parameters
884 -- p_emp_state_tax_rule_id PK
885 -- p_secondary_wa
886 --
887 -- Post Success
888 -- Processing continues
889 --
890 -- Post Failure
891 -- Error raised.
892 --
893 -- Access Status
894 -- Internal table handler use only.
895 --
896 Procedure chk_secondary_wa
897 (p_emp_state_tax_rule_id in number
898 ,p_secondary_wa in pay_us_emp_state_tax_rules_f.secondary_wa%TYPE
899 ) is
900 --
901 l_proc varchar2(72) := g_package||'chk_secondary_wa';
902 --
903 Begin
904 --
905 hr_utility.set_location('Entering:'||l_proc,5);
906 --
907 -- If the value is being inserted or updated...
908 --
909 if (p_emp_state_tax_rule_id is not null
910 and nvl(p_secondary_wa,hr_api.g_number)
911 <> nvl(pay_sta_shd.g_old_rec.secondary_wa,hr_api.g_number))
912 or (p_emp_state_tax_rule_id is null) then
913 --
914 -- Check that the mandatory parameters have been set
915 --
916 if p_secondary_wa is null then
917 hr_utility.set_message(801, 'PAY_72821_STA_SECND_WA_NOT_NUL');
918 hr_utility.raise_error;
919 end if;
920 --
921 -- check if secondary_wa value is in a valid range
922 --
923 if p_secondary_wa < 0 then
924 --
925 -- raise error as given value is invalid
926 --
927 hr_utility.set_message(801,'PAY_72820_STA_SECOND_WA_POSTVE');
928 hr_utility.raise_error;
929 --
930 end if;
931 --
932 end if;
933 --
934 hr_utility.set_location('Leaving:'||l_proc,10);
935 --
936 End chk_secondary_wa;
937 --
938 -- ----------------------------------------------------------------------------
939 -- |----------------------< chk_withholding_allowances >----------------------|
940 -- ----------------------------------------------------------------------------
941 --
942 -- Description
943 -- This procedure checks that withholding_allowances >= 0
944 --
945 -- Pre-Conditions
946 -- None.
947 --
948 -- In Parameters
949 -- p_emp_state_tax_rule_id PK
950 -- p_withholding_allowances
954 --
951 --
952 -- Post Success
953 -- Processing continues
955 -- Post Failure
956 -- Error raised.
957 --
958 -- Access Status
959 -- Internal table handler use only.
960 --
961 Procedure chk_withholding_allowances
962 (p_emp_state_tax_rule_id in number
963 ,p_withholding_allowances in
964 pay_us_emp_state_tax_rules_f.withholding_allowances%TYPE
965 ) is
966 --
967 l_proc varchar2(72) := g_package||'chk_withholding_allowances';
968 --
969 Begin
970 --
971 hr_utility.set_location('Entering:'||l_proc,5);
972 --
973 -- If the value is being inserted or updated...
974 --
975 if (p_emp_state_tax_rule_id is not null
976 and nvl(p_withholding_allowances,hr_api.g_number)
977 <> nvl(pay_sta_shd.g_old_rec.withholding_allowances,hr_api.g_number))
978 or (p_emp_state_tax_rule_id is null) then
979 --
980 -- Check that the mandatory parameters have been set
981 --
982 if p_withholding_allowances is null then
983 hr_utility.set_message(801, 'PAY_72830_STA_WA_NOT_NULL');
984 hr_utility.raise_error;
985 end if;
986 --
987 -- check if withholding_allowances value is in a valid range
988 --
989 if p_withholding_allowances < 0 then
990 --
991 -- raise error as given value is invalid
992 --
993 hr_utility.set_message(801,'PAY_72829_STA_WA_POSITIVE');
994 hr_utility.raise_error;
995 --
996 end if;
997 --
998 end if;
999 --
1000 hr_utility.set_location('Leaving:'||l_proc,10);
1001 --
1002 End chk_withholding_allowances;
1003 --
1004 -- ----------------------------------------------------------------------------
1005 -- |-------------------< chk_sui_wage_base_override_amo >---------------------|
1006 -- ----------------------------------------------------------------------------
1007 --
1008 -- Description
1009 -- This procedure checks that sui_wage_base_override_amount >= 0
1010 --
1011 -- Pre-Conditions
1012 -- None.
1013 --
1014 -- In Parameters
1015 -- p_emp_state_tax_rule_id PK
1016 -- p_sui_wage_base_override_amo
1017 --
1018 -- Post Success
1019 -- Processing continues
1020 --
1021 -- Post Failure
1022 -- Error raised.
1023 --
1024 -- Access Status
1025 -- Internal table handler use only.
1026 --
1027 Procedure chk_sui_wage_base_override_amo
1028 (p_emp_state_tax_rule_id in number
1029 ,p_sui_wage_base_override_amo in
1030 pay_us_emp_state_tax_rules_f.sui_wage_base_override_amount%TYPE
1031 ) is
1032 --
1033 l_proc varchar2(72) := g_package||'chk_sui_wage_base_override_amo';
1034 --
1035 Begin
1036 --
1037 hr_utility.set_location('Entering:'||l_proc,5);
1038 --
1039 -- If the value is being inserted or updated...
1040 --
1041 if ((p_emp_state_tax_rule_id is not null
1042 and nvl(p_sui_wage_base_override_amo,hr_api.g_number) <>
1043 nvl(pay_sta_shd.g_old_rec.sui_wage_base_override_amount,hr_api.g_number))
1044 or
1045 (p_emp_state_tax_rule_id is null)) then
1046 --
1047 -- Validate only if attribute is not null
1048 --
1049 if p_sui_wage_base_override_amo is not null then
1050 --
1051 -- check if sui_wage_base_override_amo value is in a valid range
1052 --
1053 if p_sui_wage_base_override_amo < 0 then
1054 --
1055 -- raise error as given value is invalid
1056 --
1057 hr_utility.set_message(801,'PAY_72826_STA_SUI_OVD_POSITIVE');
1058 hr_utility.raise_error;
1059 --
1060 end if;
1061 --
1062 end if;
1063 --
1064 end if;
1065 --
1066 hr_utility.set_location('Leaving:'||l_proc,10);
1067 --
1068 End chk_sui_wage_base_override_amo;
1069 --
1070 -- ----------------------------------------------------------------------------
1071 -- |----------------------< chk_supp_tax_override_rate >----------------------|
1072 -- ----------------------------------------------------------------------------
1073 --
1074 -- Description
1075 -- This procedure checks that supp_tax_override_rate between 0 and 100
1076 --
1077 -- Pre-Conditions
1078 -- None.
1079 --
1080 -- In Parameters
1081 -- p_emp_state_tax_rule_id PK
1082 -- p_supp_tax_override_rate
1083 --
1084 -- Post Success
1085 -- Processing continues
1086 --
1087 -- Post Failure
1088 -- Error raised.
1089 --
1090 -- Access Status
1091 -- Internal table handler use only.
1092 --
1093 Procedure chk_supp_tax_override_rate
1094 (p_emp_state_tax_rule_id in number
1095 ,p_supp_tax_override_rate in
1096 pay_us_emp_state_tax_rules_f.supp_tax_override_rate%TYPE
1097 ) is
1098 --
1099 l_proc varchar2(72) := g_package||'chk_supp_tax_override_rate';
1100 --
1101 Begin
1102 --
1103 hr_utility.set_location('Entering:'||l_proc,5);
1104 --
1105 -- If the value is being inserted or updated...
1106 --
1107 if ((p_emp_state_tax_rule_id is not null
1108 and nvl(p_supp_tax_override_rate,hr_api.g_number)
1109 <> nvl(pay_sta_shd.g_old_rec.supp_tax_override_rate,hr_api.g_number))
1110 or
1111 (p_emp_state_tax_rule_id is null)) then
1112 --
1113 -- Validate only if attribute is not null
1114 --
1115 if p_supp_tax_override_rate is not null then
1116 --
1117 -- check if supp_tax_override_rate value is in a valid range
1121 -- raise error as given value is invalid
1118 --
1119 if p_supp_tax_override_rate < 0 or p_supp_tax_override_rate > 100 then
1120 --
1122 --
1123 hr_utility.set_message(801,'PAY_72827_STA_SUPP_RT_IN_RANGE');
1124 hr_utility.raise_error;
1125 --
1126 end if;
1127 --
1128 end if;
1129 --
1130 end if;
1131 --
1132 hr_utility.set_location('Leaving:'||l_proc,10);
1133 --
1134 End chk_supp_tax_override_rate;
1135 --
1136 -- ----------------------------------------------------------------------------
1137 -- |----------------------< chk_non_updateable_args >-------------------------|
1138 -- ----------------------------------------------------------------------------
1139 -- {Start Of Comments}
1140 --
1141 -- Description:
1142 -- This procedure checks that columns where updates are not allowed, have not
1143 -- been changed from their original value.
1144 --
1145 -- Prerequisites:
1146 -- None.
1147 --
1148 -- In Parameters
1149 -- p_rec record structure of row being updated
1150 -- effective_date Effective Date of session
1151 --
1152 -- Post Success:
1153 -- Processing continues.
1154 --
1155 -- Post Failure:
1156 -- Error raised.
1157 --
1158 -- Developer Implementation Notes:
1159 -- None.
1160 --
1161 -- Access Status:
1162 -- Internal Row Handler Use Only.
1163 --
1164 -- {End Of Comments}
1165 -- ----------------------------------------------------------------------------
1166 Procedure chk_non_updateable_args
1167 (p_rec in pay_sta_shd.g_rec_type
1168 ,p_effective_date in date
1169 ) is
1170 --
1171 l_proc varchar2(72) := g_package||'chk_non_updateable_args';
1172 l_error exception;
1173 l_argument varchar2(30);
1174 --
1175 Begin
1176 hr_utility.set_location('Entering:'||l_proc, 10);
1177 --
1178 -- Only proceed with validation if a row exists for
1179 -- the current record in the HR schema
1180 --
1181 if not pay_sta_shd.api_updating
1182 (p_emp_state_tax_rule_id => p_rec.emp_state_tax_rule_id
1183 ,p_object_version_number => p_rec.object_version_number
1184 ,p_effective_date => p_effective_date
1185 ) then
1186 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1187 hr_utility.set_message_token('PROCEDURE', l_proc);
1188 hr_utility.set_message_token('STEP', '20');
1189 end if;
1190 hr_utility.set_location(l_proc, 30);
1191 --
1192 if (nvl(p_rec.assignment_id, hr_api.g_number) <>
1193 pay_sta_shd.g_old_rec.assignment_id) then
1194 l_argument := 'assignment_id';
1195 raise l_error;
1196 end if;
1197 hr_utility.set_location(l_proc, 40);
1198 --
1199 if (nvl(p_rec.business_group_id, hr_api.g_number) <>
1200 pay_sta_shd.g_old_rec.business_group_id) then
1201 l_argument := 'business_group_id';
1202 raise l_error;
1203 end if;
1204 hr_utility.set_location(l_proc, 50);
1205 --
1206 if nvl(p_rec.state_code, hr_api.g_varchar2) <>
1207 pay_sta_shd.g_old_rec.state_code then
1208 l_argument := 'state_code';
1209 raise l_error;
1210 end if;
1211 hr_utility.set_location(l_proc, 60);
1212 --
1213 if nvl(p_rec.jurisdiction_code, hr_api.g_varchar2) <>
1214 pay_sta_shd.g_old_rec.jurisdiction_code then
1215 l_argument := 'jurisdiction_code';
1216 raise l_error;
1217 end if;
1218 hr_utility.set_location(l_proc, 70);
1219 exception
1220 when l_error then
1221 hr_api.argument_changed_error
1222 (p_api_name => l_proc
1223 ,p_argument => l_argument
1224 );
1225 when others then
1226 raise;
1227 hr_utility.set_location(' Leaving:'||l_proc, 80);
1228 end chk_non_updateable_args;
1229 --
1230 -- ----------------------------------------------------------------------------
1231 -- |------------------------------< chk_delete >------------------------------|
1232 -- ----------------------------------------------------------------------------
1233 --
1234 -- Description
1235 -- Tax rules may be deleted from pay_us_emp_state_tax_rules_f when the
1236 -- following conditions are met:
1237 -- - no payroll has been run for this state
1238 -- - the state is not assigned to a work location
1239 -- - the state is not assigned to a primary resident address
1240 --
1241 --
1242 -- Pre-Conditions
1243 -- None.
1244 --
1245 -- In Parameters
1246 -- p_emp_state_tax_rule_id PK
1247 -- p_assignment_id assignment id
1248 -- p_effective_date session date
1249 -- p_object_version_number object version number
1250 -- p_validation_start_date date
1251 -- p_validation_end_date date
1252 -- p_delete_routine varchar2 default null
1253 --
1254 -- Post Success
1255 -- Processing continues
1256 --
1257 -- Post Failure
1258 -- Error raised.
1259 --
1260 -- Access Status
1261 -- Internal table handler use only.
1262 --
1263 procedure chk_delete
1264 (p_emp_state_tax_rule_id in number
1265 ,p_assignment_id in number
1266 ,p_effective_date in date
1267 ,p_datetrack_mode in varchar2
1268 ,p_validation_start_date in date
1269 ,p_validation_end_date in date
1270 ,p_delete_routine in varchar2 default null
1271 ) is
1272 --
1273 l_effective_date date;
1274 l_exists varchar2(1);
1275 l_proc varchar2(72) := g_package||'chk_delete';
1276 l_county_rule_exists varchar2(1);
1277 --
1278 cursor csr_check_payroll(p_csr_tmp_date in date) is
1279 select null
1280 from pay_run_results prr,
1281 pay_assignment_actions paa
1282 where substr(prr.jurisdiction_code,1,2)=pay_sta_shd.g_old_rec.state_code
1283 and paa.assignment_action_id = prr.assignment_action_id
1284 and paa.assignment_id = pay_sta_shd.g_old_rec.assignment_id
1285 and exists (select null
1286 from pay_payroll_actions ppa
1287 where ppa.payroll_action_id = paa.payroll_action_id
1288 and ppa.action_type in ('Q','R')
1289 and ppa.date_earned > p_csr_tmp_date
1290 );
1291 --
1292 cursor csr_check_work_loc(p_csr_tmp_date in date) is
1293 select null
1294 from per_assignments_f asg,
1295 hr_locations hrl
1296 where asg.assignment_id = pay_sta_shd.g_old_rec.assignment_id
1297 and hrl.location_id = asg.location_id
1298 and asg.effective_end_date > p_csr_tmp_date
1299 and exists (select null
1300 from pay_us_states pus
1301 where pus.state_abbrev = hrl.region_2
1302 and pus.state_code = pay_sta_shd.g_old_rec.state_code);
1303 --
1304 cursor csr_check_residence_loc(p_csr_tmp_date in date) is
1305 select null
1306 from per_assignments_f asg,
1307 per_addresses pad
1308 where asg.assignment_id = pay_sta_shd.g_old_rec.assignment_id
1309 and pad.person_id = asg.person_id
1310 and pad.primary_flag = 'Y'
1311 and nvl(pad.date_to, hr_api.g_eot) > p_csr_tmp_date
1312 and exists (select null
1313 from pay_us_states pus
1314 where pus.state_abbrev = pad.region_2
1315 and pus.state_code = pay_sta_shd.g_old_rec.state_code);
1316 --
1317 -- Cursor to check for existing county tax rules
1318 --
1319 cursor chk_county_tax_rules
1320 is
1321 select null
1322 from pay_us_emp_county_tax_rules_f cnt
1323 where cnt.assignment_id = p_assignment_id
1324 and cnt.state_code = pay_sta_shd.g_old_rec.state_code
1325 and cnt.effective_end_date > p_effective_date;
1326 --
1327 begin
1328 hr_utility.set_location('Entering:'|| l_proc, 1);
1329 --
1330 -- Check mandatory parameters have been set
1331 --
1332 hr_api.mandatory_arg_error
1333 (p_api_name => l_proc
1334 ,p_argument => 'effective_date'
1335 ,p_argument_value => p_effective_date
1336 );
1337 --
1338 hr_api.mandatory_arg_error
1339 (p_api_name => l_proc
1340 ,p_argument => 'datetrack_mode'
1341 ,p_argument_value => p_datetrack_mode
1342 );
1343 --
1344 hr_utility.set_location(l_proc, 2);
1345 --
1346 -- Validate that this routine is called from Assignment code
1347 --
1348 if nvl(p_delete_routine,'X') = 'ASSIGNMENT' then
1349 --
1350 -- Perform validation for valid datetrack delete modes.
1351 --
1352 If p_datetrack_mode in(hr_api.g_zap, hr_api.g_delete) then
1353 --
1354 hr_utility.set_location(l_proc,20);
1355 --
1356 if p_datetrack_mode = hr_api.g_zap then
1357 l_effective_date := trunc(hr_api.g_sot);
1358 else
1359 l_effective_date := trunc(p_effective_date);
1360 end if;
1361 --
1362 -- Check if payroll has been run for this state
1363 --
1364 open csr_check_payroll(l_effective_date);
1365 fetch csr_check_payroll into l_exists;
1366 if csr_check_payroll%FOUND then
1367 hr_utility.set_location(l_proc,15);
1368 close csr_check_payroll;
1369 hr_utility.set_message(801, 'PAY_52235_TAX_RULE_DELETE');
1370 hr_utility.raise_error;
1371 end if;
1372 close csr_check_payroll;
1373 end if;
1374 else -- p_delete_routine <> 'ASSIGNMENT'
1375 --
1376 hr_utility.set_location(l_proc,20);
1377 --
1378 if p_datetrack_mode = hr_api.g_zap then
1379 --
1380 l_effective_date := trunc(hr_api.g_sot);
1381 --
1382 -- Check if payroll has been run for this state
1383 --
1384 open csr_check_payroll(l_effective_date);
1385 fetch csr_check_payroll into l_exists;
1386 if csr_check_payroll%FOUND then
1387 hr_utility.set_location(l_proc,15);
1388 close csr_check_payroll;
1389 hr_utility.set_message(801, 'PAY_52235_TAX_RULE_DELETE');
1390 hr_utility.raise_error;
1391 end if;
1392 close csr_check_payroll;
1393 --
1394 -- Check if state has been assigned to a work location
1395 --
1396 open csr_check_work_loc(l_effective_date);
1397 fetch csr_check_work_loc into l_exists;
1398 if csr_check_work_loc%FOUND then
1399 hr_utility.set_location(l_proc,25);
1400 close csr_check_work_loc;
1401 hr_utility.set_message(801, 'PAY_52293_TAX_STDEL_LOC');
1402 hr_utility.raise_error;
1403 end if;
1404 close csr_check_work_loc;
1405 --
1406 hr_utility.set_location(l_proc,30);
1407 --
1408 -- Check if state has been assigned to a primary residence
1409 --
1410 open csr_check_residence_loc(l_effective_date);
1411 fetch csr_check_residence_loc into l_exists;
1412 if csr_check_residence_loc%FOUND then
1413 hr_utility.set_location(l_proc,35);
1414 close csr_check_residence_loc;
1415 hr_utility.set_message(801, 'PAY_52296_TAX_STDEL_RES');
1416 hr_utility.raise_error;
1417 end if;
1418 close csr_check_residence_loc;
1419 --
1420 hr_utility.set_location(l_proc,40);
1421 --
1422 else
1423 --
1424 -- Delete not allowed for this datetrack mode
1425 --
1426 hr_utility.set_message(801, 'PAY_52971_TAX_ZAP_ONLY');
1427 hr_utility.raise_error;
1428 --
1429 end if;
1430 --
1431 end if;
1432 --
1433 -- If any county tax rules exist for this assignment, raise an error.
1434 --
1435 open chk_county_tax_rules;
1436 fetch chk_county_tax_rules into l_county_rule_exists;
1437 if chk_county_tax_rules%found then
1438 close chk_county_tax_rules;
1439 hr_utility.set_message(801,'HR_7215_DT_CHILD_EXISTS');
1440 hr_utility.set_message_token('TABLE_NAME',
1441 'PAY_US_EMP_COUNTY_TAX_RULES_F');
1442 hr_utility.raise_error;
1443 end if;
1444 close chk_county_tax_rules;
1445 --
1446 end chk_delete;
1447 --
1448 -- ----------------------------------------------------------------------------
1449 -- |--------------------------< dt_update_validate >--------------------------|
1450 -- ----------------------------------------------------------------------------
1451 -- {Start Of Comments}
1452 --
1453 -- Description:
1454 -- This procedure is used for referential integrity of datetracked
1455 -- parent entities when a datetrack update operation is taking place
1456 -- and where there is no cascading of update defined for this entity.
1457 --
1458 -- Prerequisites:
1459 -- This procedure is called from the update_validate.
1460 --
1461 -- In Parameters:
1462 --
1463 -- Post Success:
1464 -- Processing continues.
1465 --
1466 -- Post Failure:
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_update_validate
1478 (
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_update_validate';
1484 l_integrity_error 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 update mode is valid
1498 --
1499 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
1500 --
1501 --
1502 -- Ensure the arguments are not null
1503 --
1504 hr_api.mandatory_arg_error
1505 (p_api_name => l_proc,
1506 p_argument => 'validation_start_date',
1507 p_argument_value => p_validation_start_date);
1508 --
1509 hr_api.mandatory_arg_error
1510 (p_api_name => l_proc,
1511 p_argument => 'validation_end_date',
1512 p_argument_value => p_validation_end_date);
1513 --
1514 --
1515 --
1516 End If;
1517 --
1518 hr_utility.set_location(' Leaving:'||l_proc, 10);
1519 Exception
1520 When l_integrity_error Then
1521 --
1522 -- A referential integrity check was violated therefore
1523 -- we must error
1524 --
1525 hr_utility.set_message(801, 'HR_7216_DT_UPD_INTEGRITY_ERR');
1526 hr_utility.set_message_token('TABLE_NAME', l_table_name);
1527 hr_utility.raise_error;
1528 When Others Then
1529 --
1530 -- An unhandled or unexpected error has occurred which
1531 -- we must report
1532 --
1533 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1534 hr_utility.set_message_token('PROCEDURE', l_proc);
1535 hr_utility.set_message_token('STEP','15');
1536 hr_utility.raise_error;
1537 End dt_update_validate;
1538 --
1539 -- ----------------------------------------------------------------------------
1540 -- |--------------------------< dt_delete_validate >--------------------------|
1541 -- ----------------------------------------------------------------------------
1542 -- {Start Of Comments}
1543 --
1544 -- Description:
1545 -- This procedure is used for referential integrity of datetracked
1546 -- child entities when either a datetrack DELETE or ZAP is in operation
1547 -- and where there is no cascading of delete defined for this entity.
1548 -- For the datetrack mode of DELETE or ZAP we must ensure that no
1549 -- datetracked child rows exist between the validation start and end
1550 -- dates.
1551 --
1552 -- Prerequisites:
1553 -- This procedure is called from the delete_validate.
1554 --
1555 -- In Parameters:
1556 --
1557 -- Post Success:
1558 -- Processing continues.
1559 --
1560 -- Post Failure:
1561 -- If a row exists by determining the returning Boolean value from the
1562 -- generic dt_api.rows_exist function then we must supply an error via
1563 -- the use of the local exception handler l_rows_exist.
1564 --
1565 -- Developer Implementation Notes:
1566 -- This procedure should not need maintenance unless the HR Schema model
1567 -- changes.
1568 --
1569 -- Access Status:
1570 -- Internal Row Handler Use Only.
1571 --
1572 -- {End Of Comments}
1573 -- ----------------------------------------------------------------------------
1574 Procedure dt_delete_validate
1575 (p_emp_state_tax_rule_id in number,
1576 p_datetrack_mode in varchar2,
1577 p_validation_start_date in date,
1578 p_validation_end_date in date) Is
1579 --
1580 l_proc varchar2(72) := g_package||'dt_delete_validate';
1581 l_rows_exist Exception;
1582 l_table_name all_tables.table_name%TYPE;
1583 --
1584 Begin
1585 hr_utility.set_location('Entering:'||l_proc, 5);
1586 --
1587 -- Ensure that the p_datetrack_mode argument is not null
1588 --
1589 hr_api.mandatory_arg_error
1590 (p_api_name => l_proc,
1591 p_argument => 'datetrack_mode',
1592 p_argument_value => p_datetrack_mode);
1593 --
1594 -- Only perform the validation if the datetrack mode is either
1595 -- DELETE or ZAP
1596 --
1597 If (p_datetrack_mode = 'DELETE' or
1598 p_datetrack_mode = 'ZAP') then
1599 --
1600 --
1601 -- Ensure the arguments are not null
1602 --
1603 hr_api.mandatory_arg_error
1604 (p_api_name => l_proc,
1605 p_argument => 'validation_start_date',
1606 p_argument_value => p_validation_start_date);
1607 --
1608 hr_api.mandatory_arg_error
1609 (p_api_name => l_proc,
1610 p_argument => 'validation_end_date',
1611 p_argument_value => p_validation_end_date);
1612 --
1613 hr_api.mandatory_arg_error
1614 (p_api_name => l_proc,
1615 p_argument => 'emp_state_tax_rule_id',
1616 p_argument_value => p_emp_state_tax_rule_id);
1617 --
1618 --
1619 --
1620 End If;
1621 --
1622 hr_utility.set_location(' Leaving:'||l_proc, 10);
1623 Exception
1624 When l_rows_exist Then
1625 --
1626 -- A referential integrity check was violated therefore
1627 -- we must error
1628 --
1629 hr_utility.set_message(801, 'HR_7215_DT_CHILD_EXISTS');
1630 hr_utility.set_message_token('TABLE_NAME', l_table_name);
1631 hr_utility.raise_error;
1632 When Others Then
1633 --
1634 -- An unhandled or unexpected error has occurred which
1635 -- we must report
1636 --
1637 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1638 hr_utility.set_message_token('PROCEDURE', l_proc);
1639 hr_utility.set_message_token('STEP','15');
1640 hr_utility.raise_error;
1641 End dt_delete_validate;
1642 --
1643 -- ----------------------------------------------------------------------------
1644 -- |---------------------------< insert_validate >----------------------------|
1645 -- ----------------------------------------------------------------------------
1646 Procedure insert_validate
1647 (p_rec in pay_sta_shd.g_rec_type,
1648 p_effective_date in date,
1649 p_datetrack_mode in varchar2,
1650 p_validation_start_date in date,
1651 p_validation_end_date in date) is
1652 --
1653 l_proc varchar2(72) := g_package||'insert_validate';
1654 --
1655 Begin
1656 hr_utility.set_location('Entering:'||l_proc, 5);
1657 --
1658 -- Call all supporting business operations
1659 --
1660 chk_state_code
1661 (p_emp_state_tax_rule_id => p_rec.emp_state_tax_rule_id,
1662 p_state_code => p_rec.state_code);
1663 --
1664 chk_jurisdiction_code
1665 (p_emp_state_tax_rule_id => p_rec.emp_state_tax_rule_id
1666 ,p_jurisdiction_code => p_rec.jurisdiction_code
1667 ,p_state_code => p_rec.state_code
1668 );
1669 --
1670 chk_sit_optional_calc_ind
1671 (p_emp_state_tax_rule_id => p_rec.emp_state_tax_rule_id,
1672 p_sit_optional_calc_ind => p_rec.sit_optional_calc_ind,
1673 p_effective_date => p_effective_date,
1674 p_object_version_number => p_rec.object_version_number);
1675 --
1676 chk_assignment_id
1677 (p_emp_state_tax_rule_id => p_rec.emp_state_tax_rule_id
1678 ,p_assignment_id => p_rec.assignment_id
1679 ,p_business_group_id => p_rec.business_group_id
1680 ,p_effective_date => p_effective_date
1681 ,p_object_version_number => p_rec.object_version_number
1682 );
1683 --
1684 chk_additional_wa_amount
1685 (p_emp_state_tax_rule_id => p_rec.emp_state_tax_rule_id
1686 ,p_additional_wa_amount => p_rec.additional_wa_amount
1687 );
1688 --
1689 chk_filing_status_code
1690 (p_emp_state_tax_rule_id => p_rec.emp_state_tax_rule_id
1691 ,p_state_code => p_rec.state_code
1692 ,p_filing_status_code => p_rec.filing_status_code
1693 ,p_effective_date => p_effective_date
1694 ,p_validation_start_date => p_validation_start_date
1695 ,p_validation_end_date => p_validation_end_date
1696 );
1697 --
1698 chk_sit_additional_tax
1699 (p_emp_state_tax_rule_id => p_rec.emp_state_tax_rule_id
1700 ,p_sit_additional_tax => p_rec.sit_additional_tax
1701 );
1702 --
1703 chk_sit_override_amount
1704 (p_emp_state_tax_rule_id => p_rec.emp_state_tax_rule_id
1705 ,p_sit_override_amount => p_rec.sit_override_amount
1706 );
1707 --
1708 chk_sit_override_rate
1709 (p_emp_state_tax_rule_id => p_rec.emp_state_tax_rule_id
1710 ,p_sit_override_rate => p_rec.sit_override_rate
1711 );
1712 --
1713 chk_remainder_percent
1714 (p_emp_state_tax_rule_id => p_rec.emp_state_tax_rule_id
1715 ,p_remainder_percent => p_rec.remainder_percent
1716 );
1717 --
1718 chk_secondary_wa
1719 (p_emp_state_tax_rule_id => p_rec.emp_state_tax_rule_id
1720 ,p_secondary_wa => p_rec.secondary_wa
1721 );
1722 --
1723 chk_withholding_allowances
1724 (p_emp_state_tax_rule_id => p_rec.emp_state_tax_rule_id
1725 ,p_withholding_allowances => p_rec.withholding_allowances
1726 );
1727 --
1728 chk_sui_wage_base_override_amo
1729 (p_emp_state_tax_rule_id => p_rec.emp_state_tax_rule_id
1730 ,p_sui_wage_base_override_amo => p_rec.sui_wage_base_override_amount
1731 );
1732 --
1733 chk_supp_tax_override_rate
1734 (p_emp_state_tax_rule_id => p_rec.emp_state_tax_rule_id
1735 ,p_supp_tax_override_rate => p_rec.supp_tax_override_rate
1736 );
1737 --
1738 hr_utility.set_location(' Leaving:'||l_proc, 10);
1739 End insert_validate;
1740 --
1741 -- ----------------------------------------------------------------------------
1742 -- |---------------------------< update_validate >----------------------------|
1743 -- ----------------------------------------------------------------------------
1744 Procedure update_validate
1745 (p_rec in pay_sta_shd.g_rec_type,
1746 p_effective_date in date,
1747 p_datetrack_mode in varchar2,
1748 p_validation_start_date in date,
1749 p_validation_end_date in date) is
1750 --
1751 l_proc varchar2(72) := g_package||'update_validate';
1752 --
1753 Begin
1754 hr_utility.set_location('Entering:'||l_proc, 5);
1755 --
1756 -- Call all supporting business operations
1757 --
1758 chk_non_updateable_args
1759 (p_rec => p_rec
1760 ,p_effective_date => p_effective_date
1761 );
1762 --
1763 chk_sit_optional_calc_ind
1764 (p_emp_state_tax_rule_id => p_rec.emp_state_tax_rule_id,
1765 p_sit_optional_calc_ind => p_rec.sit_optional_calc_ind,
1766 p_effective_date => p_effective_date,
1767 p_object_version_number => p_rec.object_version_number);
1768 --
1769 chk_additional_wa_amount
1770 (p_emp_state_tax_rule_id => p_rec.emp_state_tax_rule_id
1771 ,p_additional_wa_amount => p_rec.additional_wa_amount
1772 );
1773 --
1774 chk_filing_status_code
1775 (p_emp_state_tax_rule_id => p_rec.emp_state_tax_rule_id
1776 ,p_state_code => p_rec.state_code
1777 ,p_filing_status_code => p_rec.filing_status_code
1778 ,p_effective_date => p_effective_date
1779 ,p_validation_start_date => p_validation_start_date
1780 ,p_validation_end_date => p_validation_end_date
1781 );
1782 --
1783 chk_sit_additional_tax
1784 (p_emp_state_tax_rule_id => p_rec.emp_state_tax_rule_id
1785 ,p_sit_additional_tax => p_rec.sit_additional_tax
1786 );
1787 --
1788 chk_sit_override_amount
1789 (p_emp_state_tax_rule_id => p_rec.emp_state_tax_rule_id
1790 ,p_sit_override_amount => p_rec.sit_override_amount
1791 );
1792 --
1793 chk_sit_override_rate
1794 (p_emp_state_tax_rule_id => p_rec.emp_state_tax_rule_id
1795 ,p_sit_override_rate => p_rec.sit_override_rate
1796 );
1797 --
1798 chk_remainder_percent
1799 (p_emp_state_tax_rule_id => p_rec.emp_state_tax_rule_id
1800 ,p_remainder_percent => p_rec.remainder_percent
1801 );
1802 --
1803 chk_secondary_wa
1804 (p_emp_state_tax_rule_id => p_rec.emp_state_tax_rule_id
1805 ,p_secondary_wa => p_rec.secondary_wa
1806 );
1807 --
1808 chk_withholding_allowances
1809 (p_emp_state_tax_rule_id => p_rec.emp_state_tax_rule_id
1810 ,p_withholding_allowances => p_rec.withholding_allowances
1811 );
1812 --
1813 chk_sui_wage_base_override_amo
1814 (p_emp_state_tax_rule_id => p_rec.emp_state_tax_rule_id
1815 ,p_sui_wage_base_override_amo => p_rec.sui_wage_base_override_amount
1816 );
1817 --
1818 chk_supp_tax_override_rate
1819 (p_emp_state_tax_rule_id => p_rec.emp_state_tax_rule_id
1820 ,p_supp_tax_override_rate => p_rec.supp_tax_override_rate
1821 );
1822 --
1823 -- Call the datetrack update integrity operation
1824 --
1825 dt_update_validate
1826 (
1827 p_datetrack_mode => p_datetrack_mode,
1828 p_validation_start_date => p_validation_start_date,
1829 p_validation_end_date => p_validation_end_date);
1830 --
1831 hr_utility.set_location(' Leaving:'||l_proc, 10);
1832 End update_validate;
1833 --
1834 -- ----------------------------------------------------------------------------
1835 -- |---------------------------< delete_validate >----------------------------|
1836 -- ----------------------------------------------------------------------------
1837 Procedure delete_validate
1838 (p_rec in pay_sta_shd.g_rec_type,
1839 p_effective_date in date,
1840 p_datetrack_mode in varchar2,
1841 p_validation_start_date in date,
1842 p_validation_end_date in date,
1843 p_delete_routine in varchar2
1844 ) is
1845 --
1846 l_proc varchar2(72) := g_package||'delete_validate';
1847 --
1848 Begin
1849 hr_utility.set_location('Entering:'||l_proc, 5);
1850 --
1851 -- Call all supporting business operations
1852 --
1853 chk_delete
1854 (p_emp_state_tax_rule_id => p_rec.emp_state_tax_rule_id
1855 ,p_assignment_id => pay_sta_shd.g_old_rec.assignment_id
1856 ,p_effective_date => p_effective_date
1857 ,p_datetrack_mode => p_datetrack_mode
1858 ,p_validation_start_date => p_validation_start_date
1859 ,p_validation_end_date => p_validation_end_date
1860 ,p_delete_routine => p_delete_routine
1861 );
1862 --
1863 dt_delete_validate
1864 (p_datetrack_mode => p_datetrack_mode,
1865 p_validation_start_date => p_validation_start_date,
1866 p_validation_end_date => p_validation_end_date,
1867 p_emp_state_tax_rule_id => p_rec.emp_state_tax_rule_id);
1868 --
1869 hr_utility.set_location(' Leaving:'||l_proc, 10);
1870 End delete_validate;
1871 --
1872 --
1873 -- ---------------------------------------------------------------------------
1874 -- |---------------------< return_legislation_code >-------------------------|
1875 -- ---------------------------------------------------------------------------
1876 --
1877 function return_legislation_code
1878 (p_emp_state_tax_rule_id in number) return varchar2 is
1879 --
1880 -- Declare cursor
1881 --
1882 cursor csr_leg_code is
1883 select a.legislation_code
1884 from per_business_groups a,
1885 pay_us_emp_state_tax_rules_f b
1886 where b.emp_state_tax_rule_id = p_emp_state_tax_rule_id
1887 and a.business_group_id = b.business_group_id;
1888 --
1889 -- Declare local variables
1890 --
1891 l_legislation_code varchar2(150);
1892 l_proc varchar2(72) := g_package||'return_legislation_code';
1893 --
1894 begin
1895 --
1896 hr_utility.set_location('Entering:'|| l_proc, 10);
1897 --
1898 -- Ensure that all the mandatory parameter are not null
1899 --
1900 hr_api.mandatory_arg_error(p_api_name => l_proc,
1901 p_argument => 'emp_state_tax_rule_id',
1902 p_argument_value => p_emp_state_tax_rule_id);
1903 --
1904 if nvl(g_sta_tax_rule_id, hr_api.g_number) = p_emp_state_tax_rule_id then
1905 --
1906 -- The legislation code has already been found with a previous
1907 -- call to this function. Just return the value in the global
1908 -- variable.
1909 --
1910 l_legislation_code := g_legislation_code;
1911 hr_utility.set_location(l_proc, 20);
1912 --
1913 else
1914 --
1915 -- The ID is different to the last call to this function
1916 -- or this is the first call to this function.
1917 --
1918 open csr_leg_code;
1919 --
1920 fetch csr_leg_code into l_legislation_code;
1921 --
1922 if csr_leg_code%notfound then
1923 --
1924 close csr_leg_code;
1925 --
1926 -- The primary key is invalid therefore we must error
1927 --
1928 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
1929 hr_utility.raise_error;
1930 --
1931 end if;
1932 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1933 --
1934 -- Set the global variables to the values are
1935 -- available for the next call to this function
1936 --
1937 close csr_leg_code;
1938 g_sta_tax_rule_id := p_emp_state_tax_rule_id;
1939 g_legislation_code := l_legislation_code;
1940 end if;
1941 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1942 --
1943 return l_legislation_code;
1944 --
1945 end return_legislation_code;
1946 --
1947 end pay_sta_bus;