[Home] [Help]
PACKAGE BODY: APPS.PAY_SID_BUS
Source
1 Package Body pay_sid_bus as
2 /* $Header: pysidrhi.pkb 120.1 2005/07/05 06:26:10 vikgupta noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_sid_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_prsi_details_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_prsi_details_id in number
22 ) is
23 --
24 -- Declare cursor
25 --
26 cursor csr_sec_grp is
27 select pbg.security_group_id
28 from per_business_groups pbg
29 , pay_ie_prsi_details_f sid
30 , per_all_assignments_f paa
31 where sid.prsi_details_id = p_prsi_details_id
32 and sid.assignment_id = paa.assignment_id
33 and pbg.business_group_id = paa.business_group_id;
34 --
35 -- Declare local variables
36 --
37 l_security_group_id number;
38 l_proc varchar2(72) := g_package||'set_security_group_id';
39 --
40 begin
41 --
42 hr_utility.set_location('Entering:'|| l_proc, 10);
43 --
44 -- Ensure that all the mandatory parameter are not null
45 --
46 hr_api.mandatory_arg_error
47 (p_api_name => l_proc
48 ,p_argument => 'prsi_details_id'
49 ,p_argument_value => p_prsi_details_id
50 );
51 --
52 open csr_sec_grp;
53 fetch csr_sec_grp into l_security_group_id;
54 --
55 if csr_sec_grp%notfound then
56 --
57 close csr_sec_grp;
58 --
59 -- The primary key is invalid therefore we must error
60 --
61 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
62 fnd_message.raise_error;
63 --
64 end if;
65 close csr_sec_grp;
66 --
67 -- Set the security_group_id in CLIENT_INFO
68 --
69 hr_api.set_security_group_id
70 (p_security_group_id => l_security_group_id
71 );
72 --
73 hr_utility.set_location(' Leaving:'|| l_proc, 20);
74 --
75 end set_security_group_id;
76 --
77 -- ---------------------------------------------------------------------------
78 -- |---------------------< return_legislation_code >-------------------------|
79 -- ---------------------------------------------------------------------------
80 --
81 Function return_legislation_code
82 (p_prsi_details_id in number
83 )
84 Return Varchar2 Is
85 --
86 -- Declare cursor
87 --
88 cursor csr_leg_code is
89 select pbg.legislation_code
90 from per_business_groups pbg
91 , pay_ie_prsi_details_f sid
92 , per_all_assignments_f paa
93 where sid.prsi_details_id = p_prsi_details_id
94 and sid.assignment_id = paa.assignment_id
95 and pbg.business_group_id = paa.business_group_id;
96 --
97 -- Declare local variables
98 --
99 l_legislation_code varchar2(150);
100 l_proc varchar2(72) := g_package||'return_legislation_code';
101 --
102 Begin
103 --
104 hr_utility.set_location('Entering:'|| l_proc, 10);
105 --
106 -- Ensure that all the mandatory parameter are not null
107 --
108 hr_api.mandatory_arg_error
109 (p_api_name => l_proc
110 ,p_argument => 'prsi_details_id'
111 ,p_argument_value => p_prsi_details_id
112 );
113 --
114 if ( nvl(pay_sid_bus.g_prsi_details_id, hr_api.g_number)
115 = p_prsi_details_id) then
116 --
117 -- The legislation code has already been found with a previous
118 -- call to this function. Just return the value in the global
119 -- variable.
120 --
121 l_legislation_code := pay_sid_bus.g_legislation_code;
122 hr_utility.set_location(l_proc, 20);
123 else
124 --
125 -- The ID is different to the last call to this function
126 -- or this is the first call to this function.
127 --
128 open csr_leg_code;
129 fetch csr_leg_code into l_legislation_code;
130 --
131 if csr_leg_code%notfound then
132 --
133 -- The primary key is invalid therefore we must error
134 --
135 close csr_leg_code;
136 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
137 fnd_message.raise_error;
138 end if;
139 hr_utility.set_location(l_proc,30);
140 --
141 -- Set the global variables so the values are
142 -- available for the next call to this function.
143 --
144 close csr_leg_code;
145 pay_sid_bus.g_prsi_details_id := p_prsi_details_id;
146 pay_sid_bus.g_legislation_code := l_legislation_code;
147 end if;
148 hr_utility.set_location(' Leaving:'|| l_proc, 40);
149 return l_legislation_code;
150 end return_legislation_code;
151 --
152 -- ----------------------------------------------------------------------------
153 -- |-----------------------< chk_non_updateable_args >------------------------|
154 -- ----------------------------------------------------------------------------
155 -- {Start Of Comments}
156 --
157 -- Description:
158 -- This procedure is used to ensure that non updateable attributes have
159 -- not been updated. If an attribute has been updated an error is generated.
160 --
161 -- Pre Conditions:
162 -- g_old_rec has been populated with details of the values currently in
163 -- the database.
164 --
165 -- In Arguments:
166 -- p_rec has been populated with the updated values the user would like the
167 -- record set to.
168 --
169 -- Post Success:
170 -- Processing continues if all the non updateable attributes have not
171 -- changed.
172 --
173 -- Post Failure:
174 -- An application error is raised if any of the non updatable attributes
175 -- have been altered.
176 --
177 -- {End Of Comments}
178 -- ----------------------------------------------------------------------------
179 Procedure chk_non_updateable_args
180 (p_effective_date in date
181 ,p_rec in pay_sid_shd.g_rec_type
182 ) IS
183 --
184 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
185 l_error EXCEPTION;
186 l_argument varchar2(30);
187 --
188 Begin
189 --
190 -- Only proceed with the validation if a row exists for the current
191 -- record in the HR Schema.
192 --
193 IF NOT pay_sid_shd.api_updating
194 (p_prsi_details_id => p_rec.prsi_details_id
195 ,p_effective_date => p_effective_date
196 ,p_object_version_number => p_rec.object_version_number
197 ) THEN
198 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
199 fnd_message.set_token('PROCEDURE ', l_proc);
200 fnd_message.set_token('STEP ', '5');
201 fnd_message.raise_error;
202 END IF;
203 --
204 --
205 IF nvl(p_rec.assignment_id, hr_api.g_number) <>
206 nvl(pay_ipd_shd.g_old_rec.assignment_id, hr_api.g_number) THEN
207 l_argument := 'assignment_id';
208 END IF;
209 --
210 EXCEPTION
211 WHEN l_error THEN
212 hr_api.argument_changed_error
213 (p_api_name => l_proc
214 ,p_argument => l_argument);
215 WHEN OTHERS THEN
216 RAISE;
217 End chk_non_updateable_args;
218 --
219 -- ---------------------------------------------------------------------------
220 -- |---------------------< chk_assignment_id >-------------------------|
221 -- ---------------------------------------------------------------------------
222 -- {Start Of Comments}
223 --
224 -- Description:
225 -- check if assignment already exists and valid as of the effectuve date
226 --
227 -- Prerequisites:
228 --
229 -- In Arguments:
230 -- p_effective_date
231 -- p_assignment_id
232 --
233 --
234 -- Post Success:
235 -- processing continues as no error is raised.
236 --
237 -- Post Failure:
238 -- An error is raised if the assignment does not exist.
239 --
240 -- Access Status:
241 -- Internal Development Use Only.
242 --
243 -- {End Of Comments}
244 -- ---------------------------------------------------------------------------
245 PROCEDURE chk_assignment_id
246 (p_effective_date IN DATE
247 , p_assignment_id IN NUMBER
248 ) IS
249 -- select payroll_id if assignment id exists
250 CURSOR csr_assignment IS
251 SELECT payroll_id
252 FROM per_all_assignments_f
253 WHERE assignment_id = p_assignment_id
254 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
255 --
256 l_payroll_id NUMBER;
257 BEGIN
258 OPEN csr_assignment;
259 FETCH csr_assignment INTO l_payroll_id;
260 IF csr_assignment%NOTFOUND THEN
261 CLOSE csr_assignment;
262 fnd_message.set_name('PAY','HR_IE_ASG_NOT_EXISTS');
263 fnd_message.raise_error;
264 END IF;
265 --
266 IF l_payroll_id IS NULL THEN
267 CLOSE csr_assignment;
268 fnd_message.set_name('PAY','HR_IE_ASG_NOT_IN_PAYROLL');
269 fnd_message.raise_error;
270 END IF;
271 CLOSE csr_assignment;
272 END chk_assignment_id;
273 --
274 --
275 -- ---------------------------------------------------------------------------
276 -- |------------------------< chk_director_flag >----------------------------|
277 -- ---------------------------------------------------------------------------
278 -- {Start Of Comments}
279 --
280 -- Description:
281 -- check if director_flag already exists in lookup_type YES_NO
282 --
283 -- Prerequisites:
284 --
285 -- In Arguments:
286 -- p_effective_date
287 -- p_director_flag
288 --
289 --
290 -- Post Success:
291 -- processing continues as no error is raised.
292 --
293 -- Post Failure:
294 -- An error is raised if the director_flag does not exist.
295 --
296 -- Access Status:
297 -- Internal Development Use Only.
298 --
299 -- {End Of Comments}
300 -- ---------------------------------------------------------------------------
301 PROCEDURE chk_director_flag
302 (p_effective_date IN DATE
303 , p_director_flag IN VARCHAR2
304 ) IS
305 -- select wehre director_flag exists
306 CURSOR csr_director_flag IS
307 SELECT NULL
308 FROM hr_lookups
309 WHERE lookup_code = p_director_flag
310 AND lookup_type = 'YES_NO'
311 AND enabled_flag = 'Y'
312 AND p_effective_date BETWEEN nvl(start_date_active, p_effective_date) AND nvl(end_date_active, p_effective_Date);
313 --
314 l_exists VARCHAR2(1);
315 BEGIN
316 OPEN csr_director_flag;
317 FETCH csr_director_flag INTO l_exists;
318 IF csr_director_flag%NOTFOUND THEN
319 CLOSE csr_director_flag;
320 fnd_message.set_name('PAY','HR_IE_DIRECTOR_FLAG_INVALID');
321 fnd_message.raise_error;
322 END IF;
323 --
324 CLOSE csr_director_flag;
325 END chk_director_flag;
326 --
327 --
328 -- ---------------------------------------------------------------------------
329 -- |---------------------< chk_contribution_class >-------------------------|
330 -- ---------------------------------------------------------------------------
331 -- {Start Of Comments}
332 --
333 -- Description:
334 -- check if contribution_class already exists in lookup_type
335 -- IE_PRSI_CONT_CLASS
336 --
337 -- Prerequisites:
338 --
339 -- In Arguments:
340 -- p_effective_date
341 -- p_contribution_class
342 --
343 --
344 -- Post Success:
345 -- processing continues as no error is raised.
346 --
347 -- Post Failure:
348 -- An error is raised if the contribution_class does not exist.
349 --
350 -- Access Status:
351 -- Internal Development Use Only.
352 --
353 -- {End Of Comments}
354 -- ---------------------------------------------------------------------------
355 PROCEDURE chk_contribution_class
356 (p_effective_date IN DATE
357 , p_contribution_class IN VARCHAR2
358 ) IS
359 -- select wehre contribution_class exists
360 CURSOR csr_contribution_class IS
361 SELECT NULL
362 FROM hr_lookups
363 WHERE lookup_code = p_contribution_class
364 AND lookup_type = 'IE_PRSI_CONT_CLASS'
365 AND enabled_flag = 'Y'
366 AND p_effective_date BETWEEN nvl(start_date_active, p_effective_date) AND nvl(end_date_active, p_effective_Date);
367 --
368 l_exists VARCHAR2(1);
369 BEGIN
370 OPEN csr_contribution_class;
371 FETCH csr_contribution_class INTO l_exists;
372 IF csr_contribution_class%NOTFOUND THEN
373 CLOSE csr_contribution_class;
374 fnd_message.set_name('PAY','HR_IE_CONT_CLASS_INVALID');
375 fnd_message.raise_error;
376 END IF;
377 --
378 CLOSE csr_contribution_class;
379 END chk_contribution_class;
380 --
381 --
382 -- ---------------------------------------------------------------------------
383 -- |---------------------< chk_overridden_subclass >-------------------------|
384 -- ---------------------------------------------------------------------------
385 -- {Start Of Comments}
386 --
387 -- Description:
388 -- check if overridden_subclass already exists in lookup_type
389 -- IE_PRSI_CONT_SUBCLASS
390 --
391 -- Prerequisites:
392 --
393 -- In Arguments:
394 -- p_effective_date
395 -- p_overridden_subclass
396 --
397 --
398 -- Post Success:
399 -- processing continues as no error is raised.
400 --
401 -- Post Failure:
402 -- An error is raised if the overridden_subclass does not exist.
403 --
404 -- Access Status:
405 -- Internal Development Use Only.
406 --
407 -- {End Of Comments}
408 -- ---------------------------------------------------------------------------
409 PROCEDURE chk_overridden_subclass
410 (p_effective_date IN DATE
411 , p_overridden_subclass IN VARCHAR2
412 ) IS
413 -- select wehre overridden_subclass exists
414 CURSOR csr_overridden_subclass IS
415 SELECT NULL
416 FROM hr_lookups
417 WHERE lookup_code = p_overridden_subclass
418 AND lookup_type = 'IE_PRSI_CONT_SUBCLASS'
419 AND enabled_flag = 'Y'
420 AND p_effective_date BETWEEN nvl(start_date_active, p_effective_date) AND nvl(end_date_active, p_effective_Date);
421 --
422 l_exists VARCHAR2(1);
423 BEGIN
424 OPEN csr_overridden_subclass;
425 FETCH csr_overridden_subclass INTO l_exists;
426 IF p_overridden_subclass IS NOT NULL AND csr_overridden_subclass%NOTFOUND THEN
427 CLOSE csr_overridden_subclass;
428 fnd_message.set_name('PAY','HR_IE_CONT_SUBCLASS_INVALID');
429 fnd_message.raise_error;
430 END IF;
431 --
432 CLOSE csr_overridden_subclass;
433 END chk_overridden_subclass;
434 --
435 --
436 -- ---------------------------------------------------------------------------
437 -- |------------------------< chk_soc_ben_flag >----------------------------|
438 -- ---------------------------------------------------------------------------
439 -- {Start Of Comments}
440 --
441 -- Description:
442 -- check if soc_ben_flag already exists in lookup_type YES_NO
443 --
444 -- Prerequisites:
445 --
446 -- In Arguments:
447 -- p_effective_date
448 -- p_soc_ben_flag
449 --
450 --
451 -- Post Success:
452 -- processing continues as no error is raised.
453 --
454 -- Post Failure:
455 -- An error is raised if the soc_ben_flag does not exist.
456 --
457 -- Access Status:
458 -- Internal Development Use Only.
459 --
460 -- {End Of Comments}
461 -- ---------------------------------------------------------------------------
462 PROCEDURE chk_soc_ben_flag
463 (p_effective_date IN DATE
464 , p_soc_ben_flag IN VARCHAR2
465 ) IS
466 -- select wehre soc_ben_flag exists
467 CURSOR csr_soc_ben_flag IS
468 SELECT NULL
469 FROM hr_lookups
470 WHERE lookup_code = p_soc_ben_flag
471 AND lookup_type = 'YES_NO'
472 AND enabled_flag = 'Y'
473 AND p_effective_date BETWEEN nvl(start_date_active, p_effective_date) AND nvl(end_date_active, p_effective_Date);
474 --
475 l_exists VARCHAR2(1);
476 BEGIN
477 OPEN csr_soc_ben_flag;
478 FETCH csr_soc_ben_flag INTO l_exists;
479 IF csr_soc_ben_flag%NOTFOUND THEN
480 CLOSE csr_soc_ben_flag;
481 fnd_message.set_name('PAY','HR_IE_SOC_BEN_FLAG_INVALID');
482 fnd_message.raise_error;
483 END IF;
484 --
485 CLOSE csr_soc_ben_flag;
486 END chk_soc_ben_flag;
487 --
488 --
489 -- ---------------------------------------------------------------------------
490 -- |---------------------< chk_overlapping_record >-------------------------|
491 -- ---------------------------------------------------------------------------
492 -- {Start Of Comments}
493 --
494 -- Description:
495 -- check if PRSI record already exists for the assignment
496 --
497 -- Prerequisites:
498 --
499 -- In Arguments:
500 -- p_assignment_id
501 -- p_validation_start_date
502 -- p_validation_end_date
503 --
504 -- Post Success:
505 -- processing continues as no error is raised.
506 --
507 -- Post Failure:
508 -- An error is raised if PRSI record already exists for the assignment
509 --
510 -- Access Status:
511 -- Internal Development Use Only.
512 --
513 -- {End Of Comments}
514 -- ---------------------------------------------------------------------------
515 PROCEDURE chk_overlapping_record
516 ( p_assignment_id IN NUMBER
517 , p_validation_start_date DATE
518 , p_validation_end_date DATE
519 ) IS
520 -- Select existing PRSI details
521 CURSOR csr_prsi_details IS
522 SELECT min(ipd.effective_start_date) min_start_date
523 FROM pay_ie_prsi_details_f ipd
524 WHERE ipd.assignment_id = p_assignment_id
525 and (ipd.effective_start_date between p_validation_start_date and p_validation_end_date
526 OR ipd.effective_end_date between p_validation_start_date and p_validation_end_date
527 OR p_validation_start_date BETWEEN ipd.effective_start_date and ipd.effective_end_date
528 OR p_validation_end_date BETWEEN ipd.effective_start_date and ipd.effective_end_date);
529
530
531 --
532 prsi_details_rec csr_prsi_details%ROWTYPE;
533 BEGIN
534 hr_utility.set_location('In Overlapping Record',103);
535 OPEN csr_prsi_details;
536 FETCH csr_prsi_details INTO prsi_details_rec;
537 --
538 hr_utility.set_location('prsi_details_rec'||prsi_details_rec.min_start_date,104);
539 IF prsi_details_rec.min_start_date is not null THEN
540 CLOSE csr_prsi_details;
541 fnd_message.set_name('PAY', 'HR_IE_PRSI_DETAILS_EXIST');
542 fnd_message.set_token('START_DATE', to_char(prsi_details_rec.min_start_date, 'DD-MON-YYYY'));
543 fnd_message.raise_error;
544 END IF;
545 --
546 CLOSE csr_prsi_details;
547 END chk_overlapping_record;
548 --
549 --
550 -- ---------------------------------------------------------------------------
551 -- |---------------------< chk_soc_ben_start_date >--------------------------|
552 -- ---------------------------------------------------------------------------
553 -- {Start Of Comments}
554 --
555 -- Description:
556 -- check if soc_ben_start_date is not null when soc_ben_flag is 'N'
557 --
558 -- Prerequisites:
559 --
560 -- In Arguments:
561 -- p_effective_date
562 -- p_soc_ben_flag
563 -- p_soc_ben_start_date
564 --
565 --
566 -- Post Success:
567 -- processing continues as no error is raised.
568 --
569 -- Post Failure:
570 -- An error is raised if the soc_ben_start_date is not null and
571 -- soc_ben_flag is 'N'
572 --
573 -- Access Status:
574 -- Internal Development Use Only.
575 --
576 -- {End Of Comments}
577 -- ---------------------------------------------------------------------------
578 PROCEDURE chk_soc_ben_start_date
579 (p_effective_date IN DATE
580 , p_soc_ben_flag IN VARCHAR2
581 , p_soc_ben_start_date IN DATE
582 ) IS
583 --
584 BEGIN
585 IF p_soc_ben_flag = 'N' and p_soc_ben_start_date IS NOT NULL THEN
586 fnd_message.set_name('PAY','HR_IE_SOC_BEN_START_DATE');
587 fnd_message.raise_error;
588 END IF;
589 --
590 END chk_soc_ben_start_date;
591 --
592 --
593 -- ---------------------------------------------------------------------------
594 -- |------------------------< get_std_ins_weeks >----------------------------|
595 -- ---------------------------------------------------------------------------
596 -- {Start Of Comments}
597 --
598 -- Description:
599 -- Get standard default number of insurable weeks in current pay period
600 --
601 -- Prerequisites:
602 --
603 -- In Arguments:
604 -- p_effective_date
605 -- p_assignment_id
606 --
607 --
608 -- Post Success:
609 -- returns standard default number of insurable weeks in current pay period
610 --
611 -- Post Failure:
612 --
613 -- Access Status:
614 -- Internal Development Use Only.
615 --
616 -- {End Of Comments}
617 -- ---------------------------------------------------------------------------
618 FUNCTION get_std_ins_weeks
619 (p_effective_date IN DATE
620 , p_assignment_id IN NUMBER
621 ) RETURN NUMBER IS
622 --
623 CURSOR get_period_info IS
624 SELECT ptp.time_period_id, ptp.start_Date, ptp.end_date
625 FROM per_time_periods ptp, pay_all_payrolls_f pap, per_all_assignments_f paa
626 WHERE ptp.payroll_id = pap.payroll_id
627 AND p_effective_date BETWEEN ptp.start_date AND ptp.end_date
628 AND pap.payroll_id = paa.payroll_id
629 AND p_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date
630 AND paa.assignment_id = p_assignment_id
631 AND p_effective_date BETWEEN paa.effective_start_date AND paa.effective_end_date;
632 --
633 period_info_rec get_period_info%ROWTYPE;
634 --
635 l_first_day VARCHAR2(10);
636 l_count NUMBER;
637 l_date DATE;
638 BEGIN
639 OPEN get_period_info;
640 FETCH get_period_info INTO period_info_rec;
641 CLOSE get_period_info;
642 --
643 l_first_day := to_char(to_date('01-01-'||to_char(p_effective_date,'YYYY'),'DD-MM-YYYY'),'DAY');
644 --
645 l_date := period_info_rec.start_date;
646 --
647 IF to_char(l_date, 'DAY') = l_first_day THEN
648 l_count := 1;
649 ELSE
650 l_count := 0;
651 END IF;
652 --
653 LOOP
654 l_date := next_day(l_date, l_first_day);
655 --
656 IF l_date <= period_info_rec.end_date THEN
657 l_count := l_count + 1;
658 ELSE
659 EXIT;
660 END IF;
661 END LOOP;
662 --
663 RETURN l_count;
664 END get_std_ins_weeks;
665 --
666 --
667 -- ---------------------------------------------------------------------------
668 -- |---------------------< chk_overridden_ins_weeks >------------------------|
669 -- ---------------------------------------------------------------------------
670 -- {Start Of Comments}
671 --
672 -- Description:
673 -- check that overridden insurable weeks are not more than standard default
674 -- number of insurable weeks in current pay period.
675 --
676 -- Prerequisites:
677 --
678 -- In Arguments:
679 -- p_effective_date
680 -- p_assignment_id
681 -- p_overridden_ins_weeks
682 --
683 --
684 -- Post Success:
685 -- Process continues
686 --
687 -- Post Failure:
688 --
689 -- Access Status:
690 -- Internal Development Use Only.
691 --
692 -- {End Of Comments}
693 -- ---------------------------------------------------------------------------
694 PROCEDURE chk_overridden_ins_weeks
695 (p_effective_date IN DATE
696 , p_assignment_id IN NUMBER
697 , p_overridden_ins_weeks IN NUMBER
698 ) IS
699 --
700 l_std_ins_weeks NUMBER;
701 --
702 BEGIN
703 -- Get std default number of insurable weeks
704 l_std_ins_weeks := get_std_ins_weeks( p_effective_date => p_effective_date
705 , p_assignment_id => p_assignment_id);
706 -- if overridden number of insurable weeks are more than standard
707 -- default number in current pay period then raie error
708 IF nvl(p_overridden_ins_weeks, 0) > nvl(l_std_ins_weeks, 0) THEN
709 null;
710 -- fnd_message.set_name('PAY','HR_IE_OVERRIDDEN_INS_WEEKS');
711 -- fnd_message.raise_error;
712 END IF;
713 --
714 END chk_overridden_ins_weeks;
715 --
716 --
717 -- ---------------------------------------------------------------------------
718 -- |--------------------< chk_exemption_start_end_dates >--------------------|
719 -- ---------------------------------------------------------------------------
720 -- {Start Of Comments}
721 --
722 -- Description:
723 -- check if exemption start date is before or equal to certificate end date
724 --
725 -- Prerequisites:
726 --
727 -- In Arguments:
728 -- p_exemption_start_date
729 -- p_exemption_end_date
730 --
731 --
732 -- Post Success:
733 -- processing continues as no error is raised.
734 --
735 -- Post Failure:
736 -- An error is raised if the exemption_start_date is after
737 -- exemption_end_Date
738 --
739 -- Access Status:
740 -- Internal Development Use Only.
741 --
742 -- {End Of Comments}
743 -- ---------------------------------------------------------------------------
744 PROCEDURE chk_exemption_start_end_dates
745 (p_exemption_start_Date IN DATE
746 , p_exemption_end_date IN DATE
747 ) IS
748 BEGIN
749 IF p_exemption_start_date > nvl(p_exemption_end_date, p_exemption_start_date+1) THEN
750 fnd_message.set_name('PAY', 'HR_IE_START_END_DATES');
751 fnd_message.set_token('START_DATE', to_char(p_exemption_start_date, 'DD-MON-YYYY'));
752 fnd_message.set_token('END_DATE', to_char(p_exemption_end_date, 'DD-MON-YYYY'));
753 fnd_message.raise_error;
754 END IF;
755 END chk_exemption_start_end_Dates;
756 --
757 --
758 -- ---------------------------------------------------------------------------
759 -- |------------------------< chk_community_flag >----------------------------|
760 -- ---------------------------------------------------------------------------
761 -- {Start Of Comments}
762 --
763 -- Description:
764 -- check if community_flag already exists in lookup_type YES_NO
765 --
766 -- Prerequisites:
767 --
768 -- In Arguments:
769 -- p_effective_date
770 -- p_community_flag
771 --
772 --
773 -- Post Success:
774 -- processing continues as no error is raised.
775 --
776 -- Post Failure:
777 -- An error is raised if the community_flag does not exist.
778 --
779 -- Access Status:
780 -- Internal Development Use Only.
781 --
782 -- {End Of Comments}
783 -- ---------------------------------------------------------------------------
784 PROCEDURE chk_community_flag
785 (p_effective_date IN DATE
786 , p_community_flag IN VARCHAR2
787 ) IS
788 -- select wehre community_flag exists
789 CURSOR csr_community_flag IS
790 SELECT NULL
791 FROM hr_lookups
792 WHERE lookup_code = p_community_flag
793 AND lookup_type = 'YES_NO'
794 AND enabled_flag = 'Y'
795 AND p_effective_date BETWEEN nvl(start_date_active, p_effective_date) AND nvl(end_date_active, p_effective_Date);
796 --
797 l_exists VARCHAR2(1);
798 BEGIN
799 OPEN csr_community_flag;
800 FETCH csr_community_flag INTO l_exists;
801 IF csr_community_flag%NOTFOUND THEN
802 CLOSE csr_community_flag;
803 fnd_message.set_name('PAY','HR_IE_COMMUNITY_FLAG_INVALID');
804 fnd_message.raise_error;
805 END IF;
806 --
807 CLOSE csr_community_flag;
808 END chk_community_flag;
809 --
810 --
811 -- ----------------------------------------------------------------------------
812 -- |--------------------------< dt_update_validate >--------------------------|
813 -- ----------------------------------------------------------------------------
814 -- {Start Of Comments}
815 --
816 -- Description:
817 -- This procedure is used for referential integrity of datetracked
818 -- parent entities when a datetrack update operation is taking place
819 -- and where there is no cascading of update defined for this entity.
820 --
821 -- Prerequisites:
822 -- This procedure is called from the update_validate.
823 --
824 -- In Parameters:
825 --
826 -- Post Success:
827 -- Processing continues.
828 --
829 -- Post Failure:
830 --
831 -- Developer Implementation Notes:
832 -- This procedure should not need maintenance unless the HR Schema model
833 -- changes.
834 --
835 -- Access Status:
836 -- Internal Row Handler Use Only.
837 --
838 -- {End Of Comments}
839 -- ----------------------------------------------------------------------------
840 Procedure dt_update_validate
841 (p_assignment_id in number default hr_api.g_number
842 ,p_datetrack_mode in varchar2
843 ,p_validation_start_date in date
844 ,p_validation_end_date in date
845 ) Is
846 --
847 l_proc varchar2(72) := g_package||'dt_update_validate';
848 l_integrity_error Exception;
849 l_table_name all_tables.table_name%TYPE;
850 --
851 Begin
852 --
853 -- Ensure that the p_datetrack_mode argument is not null
854 --
855 hr_api.mandatory_arg_error
856 (p_api_name => l_proc
857 ,p_argument => 'datetrack_mode'
858 ,p_argument_value => p_datetrack_mode
859 );
860 --
861 -- Mode will be valid, as this is checked at the start of the upd.
862 --
863 -- Ensure the arguments are not null
864 --
865 hr_api.mandatory_arg_error
866 (p_api_name => l_proc
867 ,p_argument => 'validation_start_date'
868 ,p_argument_value => p_validation_start_date
869 );
870 --
871 hr_api.mandatory_arg_error
872 (p_api_name => l_proc
873 ,p_argument => 'validation_end_date'
874 ,p_argument_value => p_validation_end_date
875 );
876 --
877 If ((nvl(p_assignment_id, hr_api.g_number) <> hr_api.g_number) and
878 NOT (dt_api.check_min_max_dates
879 (p_base_table_name => 'per_all_assignments_f'
880 ,p_base_key_column => 'ASSIGNMENT_ID'
881 ,p_base_key_value => p_assignment_id
882 ,p_from_date => p_validation_start_date
883 ,p_to_date => p_validation_end_date))) Then
884 l_table_name := 'all assignments';
885 raise l_integrity_error;
886 End If;
887 --
888 Exception
889 When l_integrity_error Then
890 --
891 -- A referential integrity check was violated therefore
892 -- we must error
893 --
894 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
895 fnd_message.set_token('TABLE_NAME', l_table_name);
896 fnd_message.raise_error;
897 When Others Then
898 --
899 -- An unhandled or unexpected error has occurred which
900 -- we must report
901 --
902 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
903 fnd_message.set_token('PROCEDURE', l_proc);
904 fnd_message.set_token('STEP','15');
905 fnd_message.raise_error;
906 End dt_update_validate;
907 --
908 -- ----------------------------------------------------------------------------
909 -- |--------------------------< dt_delete_validate >--------------------------|
910 -- ----------------------------------------------------------------------------
911 -- {Start Of Comments}
912 --
913 -- Description:
914 -- This procedure is used for referential integrity of datetracked
915 -- child entities when either a datetrack DELETE or ZAP is in operation
916 -- and where there is no cascading of delete defined for this entity.
917 -- For the datetrack mode of DELETE or ZAP we must ensure that no
918 -- datetracked child rows exist between the validation start and end
919 -- dates.
920 --
921 -- Prerequisites:
922 -- This procedure is called from the delete_validate.
923 --
924 -- In Parameters:
925 --
926 -- Post Success:
927 -- Processing continues.
928 --
929 -- Post Failure:
930 -- If a row exists by determining the returning Boolean value from the
931 -- generic dt_api.rows_exist function then we must supply an error via
932 -- the use of the local exception handler l_rows_exist.
933 --
934 -- Developer Implementation Notes:
935 -- This procedure should not need maintenance unless the HR Schema model
936 -- changes.
937 --
938 -- Access Status:
939 -- Internal Row Handler Use Only.
940 --
941 -- {End Of Comments}
942 -- ----------------------------------------------------------------------------
943 Procedure dt_delete_validate
944 (p_prsi_details_id in number
945 ,p_datetrack_mode in varchar2
946 ,p_validation_start_date in date
947 ,p_validation_end_date in date
948 ) Is
949 --
950 l_proc varchar2(72) := g_package||'dt_delete_validate';
951 l_rows_exist Exception;
952 l_table_name all_tables.table_name%TYPE;
953 --
954 Begin
955 --
956 -- Ensure that the p_datetrack_mode argument is not null
957 --
958 hr_api.mandatory_arg_error
959 (p_api_name => l_proc
960 ,p_argument => 'datetrack_mode'
961 ,p_argument_value => p_datetrack_mode
962 );
963 --
964 -- Only perform the validation if the datetrack mode is either
965 -- DELETE or ZAP
966 --
967 If (p_datetrack_mode = hr_api.g_delete or
968 p_datetrack_mode = hr_api.g_zap) then
969 --
970 --
971 -- Ensure the arguments are not null
972 --
973 hr_api.mandatory_arg_error
974 (p_api_name => l_proc
975 ,p_argument => 'validation_start_date'
976 ,p_argument_value => p_validation_start_date
977 );
978 --
979 hr_api.mandatory_arg_error
980 (p_api_name => l_proc
981 ,p_argument => 'validation_end_date'
982 ,p_argument_value => p_validation_end_date
983 );
984 --
985 hr_api.mandatory_arg_error
986 (p_api_name => l_proc
987 ,p_argument => 'prsi_details_id'
988 ,p_argument_value => p_prsi_details_id
989 );
990 --
991 --
992 --
993 End If;
994 --
995 Exception
996 When l_rows_exist Then
997 --
998 -- A referential integrity check was violated therefore
999 -- we must error
1000 --
1001 fnd_message.set_name('PAY', 'HR_7215_DT_CHILD_EXISTS');
1002 fnd_message.set_token('TABLE_NAME', l_table_name);
1003 fnd_message.raise_error;
1004 When Others Then
1005 --
1006 -- An unhandled or unexpected error has occurred which
1007 -- we must report
1008 --
1009 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1010 fnd_message.set_token('PROCEDURE', l_proc);
1011 fnd_message.set_token('STEP','15');
1012 fnd_message.raise_error;
1013 --
1014 End dt_delete_validate;
1015 --
1016 -- ----------------------------------------------------------------------------
1017 -- |---------------------------< insert_validate >----------------------------|
1018 -- ----------------------------------------------------------------------------
1019 Procedure insert_validate
1020 (p_rec in pay_sid_shd.g_rec_type
1021 ,p_effective_date in date
1022 ,p_datetrack_mode in varchar2
1023 ,p_validation_start_date in date
1024 ,p_validation_end_date in date
1025 ) is
1026 --
1027 l_proc varchar2(72) := g_package||'insert_validate';
1028 --
1029 Begin
1030 -- hr_utility.trace_on(null,'VIKPRSI');
1031 hr_utility.set_location('Entering:'||l_proc, 5);
1032 --
1033 -- Call all supporting business operations
1034 --
1035 per_asg_bus1.set_security_group_id(p_rec.assignment_id);
1036 --
1037 pay_sid_bus.chk_assignment_id(p_effective_date => p_effective_date
1038 , p_assignment_id => p_rec.assignment_id);
1039 --
1040 pay_sid_bus.chk_director_flag(p_effective_date => p_effective_date
1041 , p_director_flag => p_rec.director_flag);
1042 --
1043 pay_sid_bus.chk_contribution_class(p_effective_date => p_effective_date
1044 , p_contribution_class => p_rec.contribution_class);
1045 --
1046 pay_sid_bus.chk_overridden_subclass(p_effective_date => p_effective_date
1047 , p_overridden_subclass => p_rec.overridden_subclass );
1048 --
1049 pay_sid_bus.chk_soc_ben_flag(p_effective_date => p_effective_date
1050 , p_soc_ben_flag => p_rec.soc_ben_flag );
1051 --
1052 pay_sid_bus.chk_soc_ben_start_date(p_effective_date => p_effective_date
1053 , p_soc_ben_flag => p_rec.soc_ben_flag
1054 , p_soc_ben_start_date => p_rec.soc_ben_start_date);
1055 --
1056 pay_sid_bus.chk_overridden_ins_weeks(p_effective_date => p_effective_date
1057 , p_assignment_id => p_rec.assignment_id
1058 , p_overridden_ins_weeks => p_rec.overridden_ins_weeks);
1059 --
1060 pay_sid_bus.chk_exemption_start_end_dates( p_exemption_start_Date => p_rec.exemption_start_date
1061 , p_exemption_end_date => p_rec.exemption_end_date) ;
1062 --
1063 pay_sid_bus.chk_community_flag(p_effective_date => p_effective_date
1064 , p_community_flag => p_rec.community_flag);
1065 --
1066 hr_utility.set_location('p_assignment_id...'||p_rec.assignment_id,100);
1067 hr_utility.set_location('p_validation_start_date...'||p_validation_start_date,101);
1068 hr_utility.set_location('p_validation_end_date...'||p_validation_end_date,102);
1069
1070 pay_sid_bus.chk_overlapping_record(p_assignment_id => p_rec.assignment_id
1071 ,p_validation_start_date => p_validation_start_date
1072 ,p_validation_end_date => p_validation_end_date);
1073
1074 hr_utility.set_location(' Leaving:'||l_proc, 10);
1075 End insert_validate;
1076 --
1077 -- ----------------------------------------------------------------------------
1078 -- |---------------------------< update_validate >----------------------------|
1079 -- ----------------------------------------------------------------------------
1080 Procedure update_validate
1081 (p_rec in pay_sid_shd.g_rec_type
1082 ,p_effective_date in date
1083 ,p_datetrack_mode in varchar2
1084 ,p_validation_start_date in date
1085 ,p_validation_end_date in date
1086 ) is
1087 --
1088 l_proc varchar2(72) := g_package||'update_validate';
1089 --
1090 Begin
1091 hr_utility.set_location('Entering:'||l_proc, 5);
1092 --
1093 -- Call all supporting business operations
1094 --
1095 per_asg_bus1.set_security_group_id(p_rec.assignment_id);
1096 --
1097 --
1098 pay_sid_bus.chk_director_flag(p_effective_date => p_effective_date
1099 , p_director_flag => p_rec.director_flag);
1100 --
1101 pay_sid_bus.chk_contribution_class(p_effective_date => p_effective_date
1102 , p_contribution_class => p_rec.contribution_class);
1103 --
1104 pay_sid_bus.chk_overridden_subclass(p_effective_date => p_effective_date
1105 , p_overridden_subclass => p_rec.overridden_subclass );
1106 --
1107 pay_sid_bus.chk_soc_ben_flag(p_effective_date => p_effective_date
1108 , p_soc_ben_flag => p_rec.soc_ben_flag );
1109 --
1110 pay_sid_bus.chk_soc_ben_start_date(p_effective_date => p_effective_date
1111 , p_soc_ben_flag => p_rec.soc_ben_flag
1112 , p_soc_ben_start_date => p_rec.soc_ben_start_date);
1113 --
1114 pay_sid_bus.chk_overridden_ins_weeks(p_effective_date => p_effective_date
1115 , p_assignment_id => p_rec.assignment_id
1116 , p_overridden_ins_weeks => p_rec.overridden_ins_weeks);
1117 --
1118 pay_sid_bus.chk_exemption_start_end_dates( p_exemption_start_Date => p_rec.exemption_start_date
1119 , p_exemption_end_date => p_rec.exemption_end_date) ;
1120 --
1121 pay_sid_bus.chk_community_flag(p_effective_date => p_effective_date
1122 , p_community_flag => p_rec.community_flag);
1123 --
1124 -- Call the datetrack update integrity operation
1125 --
1126 dt_update_validate
1127 (p_assignment_id => p_rec.assignment_id
1128 ,p_datetrack_mode => p_datetrack_mode
1129 ,p_validation_start_date => p_validation_start_date
1130 ,p_validation_end_date => p_validation_end_date
1131 );
1132 --
1133 chk_non_updateable_args
1134 (p_effective_date => p_effective_date
1135 ,p_rec => p_rec
1136 );
1137 --
1138 --
1139 hr_utility.set_location(' Leaving:'||l_proc, 10);
1140 End update_validate;
1141 --
1142 -- ----------------------------------------------------------------------------
1143 -- |---------------------------< delete_validate >----------------------------|
1144 -- ----------------------------------------------------------------------------
1145 Procedure delete_validate
1146 (p_rec in pay_sid_shd.g_rec_type
1147 ,p_effective_date in date
1148 ,p_datetrack_mode in varchar2
1149 ,p_validation_start_date in date
1150 ,p_validation_end_date in date
1151 ) is
1152 --
1153 l_proc varchar2(72) := g_package||'delete_validate';
1154 --
1155 Begin
1156 hr_utility.set_location('Entering:'||l_proc, 5);
1157 --
1158 -- Call all supporting business operations
1159 --
1160 dt_delete_validate
1161 (p_datetrack_mode => p_datetrack_mode
1162 ,p_validation_start_date => p_validation_start_date
1163 ,p_validation_end_date => p_validation_end_date
1164 ,p_prsi_details_id => p_rec.prsi_details_id
1165 );
1166 --
1167 hr_utility.set_location(' Leaving:'||l_proc, 10);
1168 End delete_validate;
1169 --
1170 end pay_sid_bus;