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