1 Package Body per_esa_bus as
2 /* $Header: peesarhi.pkb 120.0.12010000.3 2008/08/06 09:08:49 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_esa_bus.'; -- Global package name
9 --
10 --
11 -- The following two global variables are only to be used by the
12 -- return_legislation_code function.
13 --
14 g_attendance_id number default null;
15 g_legislation_code varchar2(150) default null;
16 -- ----------------------------------------------------------------------------
17 -- |-----------------------< chk_attendance_id >------------------------------|
18 -- ----------------------------------------------------------------------------
19 --
20 -- Description
21 -- This procedure is used to check that the primary key for the establishment
22 -- attendance table is created properly. It should be null on insert and
23 -- should not be able to be updated.
24 --
25 -- Pre Conditions
26 -- None.
27 --
28 -- In Parameters
29 -- attendance_id PK of record being inserted or updated.
30 -- object_version_number Object version number of record being
31 -- inserted or updated.
32 --
33 -- Post Success
34 -- Processing continues
35 --
36 -- Post Failure
37 -- Errors handled by the procedure
38 --
39 -- Access Status
40 -- Internal table handler use only.
41 --
42 Procedure chk_attendance_id(p_attendance_id in number,
43 p_object_version_number in number) is
44 --
45 l_proc varchar2(72) := g_package||'chk_attendance_id';
46 l_api_updating boolean;
47 --
48 Begin
49 --
50 hr_utility.set_location('Entering:'||l_proc, 5);
51 --
52 l_api_updating := per_esa_shd.api_updating
53 (p_attendance_id => p_attendance_id,
54 p_object_version_number => p_object_version_number);
55 --
56 if (l_api_updating
57 and nvl(p_attendance_id,hr_api.g_number)
58 <> per_esa_shd.g_old_rec.attendance_id) then
59 --
60 -- raise error as PK has changed
61 --
62 per_esa_shd.constraint_error('PER_ESTAB_ATTENDANCES_PK');
63 --
64 elsif not l_api_updating then
65 --
66 -- check if PK is null
67 --
68 if p_attendance_id is not null then
69 --
70 -- raise error as PK is not null
71 --
72 per_esa_shd.constraint_error('PER_ESTAB_ATTENDANCES_PK');
73 --
74 end if;
75 --
76 end if;
77 --
78 hr_utility.set_location(' Leaving:'||l_proc, 10);
79 --
80 End chk_attendance_id;
81 --
82 -- ----------------------------------------------------------------------------
83 -- |----------------------------< chk_person_id >-----------------------------|
84 -- ----------------------------------------------------------------------------
85 --
86 -- Description
87 -- This procedure is used to check that the person_id has not been inserted
88 -- into the establishment attendance table already.
89 --
90 -- Pre Conditions
91 -- None.
92 --
93 -- In Parameters
94 -- attendance_id PK of record being inserted or updated.
95 -- person_id id of person being inserted.
96 -- attended_start_date date of attendance at the establishment
97 -- establishment_id id of the establishment
98 -- establishment name of establishment (created on fly)
99 -- business_group_id id of business group
100 -- object_version_number Object version number of record being
101 -- inserted or updated.
102 -- party_id id of party being inserted. -- HR/TCA merge
103 --
104 -- Post Success
105 -- Processing continues
106 --
107 -- Post Failure
108 -- Errors handled by the procedure
109 --
110 -- Access Status
111 -- Internal table handler use only.
112 Procedure chk_person_id(p_attendance_id in number,
113 p_person_id in number,
114 p_attended_start_date in date,
115 p_establishment_id in number,
116 p_establishment in varchar2,
117 p_business_group_id in number,
118 p_object_version_number in number,
119 p_party_id in number -- HR/TCA merge
120 ) is
121 --
122 l_proc varchar2(72) := g_package||'chk_person_id';
123 l_api_updating boolean;
124 l_dummy varchar2(1);
125 --
126 cursor c1 is
127 select null
128 from per_establishment_attendances per
129 where nvl(per.person_id,-1) = nvl(p_person_id,-1)
130 and per.attended_start_date = nvl(p_attended_start_date,hr_api.g_sot)
131 and nvl(per.establishment_id,-1) = nvl(p_establishment_id,-1)
132 and nvl(per.establishment,-1) = nvl(p_establishment,-1)
133 and nvl(per.business_group_id,-1) = nvl(p_business_group_id,-1);
134 --
135 cursor c2 is
136 select null
137 from per_establishment_attendances per
138 where per.party_id = p_party_id
139 and per.attended_start_date = nvl(p_attended_start_date,hr_api.g_sot)
140 and nvl(per.establishment_id,-1) = nvl(p_establishment_id,-1)
141 and nvl(per.establishment,-1) = nvl(p_establishment,-1);
142 --
143 Begin
144 --
145 hr_utility.set_location('Entering:'||l_proc, 5);
146 --
147 l_api_updating := per_esa_shd.api_updating
148 (p_attendance_id => p_attendance_id,
149 p_object_version_number => p_object_version_number);
150 --
151 if (l_api_updating
152 and (nvl(p_person_id,hr_api.g_number)
153 <> per_esa_shd.g_old_rec.person_id
154 or nvl(p_party_id,hr_api.g_number) -- HR/TCA merge
155 <> per_esa_shd.g_old_rec.party_id
156 or nvl(p_attended_start_date,hr_api.g_date)
157 <> per_esa_shd.g_old_rec.attended_start_date
158 or nvl(p_establishment_id,hr_api.g_number)
159 <> per_esa_shd.g_old_rec.establishment_id
160 or nvl(p_establishment,hr_api.g_varchar2)
161 <> per_esa_shd.g_old_rec.establishment
162 or nvl(p_business_group_id,hr_api.g_number)
163 <> per_esa_shd.g_old_rec.business_group_id)
164 or not l_api_updating) then
165 --
166 -- check if person_id is null
167 --
168 if p_person_id is null then
169 -- HR/TCA merge
170 -- if person_id is null and party_id is null, raise error.
171 --
172 if p_party_id is null then
173 --
174 -- raise error as this a mandatory requirement
175 --
176 hr_utility.set_message(801,'HR_51494_ESA_CHK_PERSON_ID');
177 hr_utility.raise_error;
178 --
179 else
180 --
181 -- check if the changes made to the above parameters result in a unique
182 -- record existing in the per_establishment_attendances table, if not raise an
183 -- error as someone is trying to enter the same person twice.
184 --
185 open c2;
186 --
187 fetch c2 into l_dummy;
188 --
189 if c2%found then
190 --
191 close c2;
192 --
193 -- raise error as record alreasy exists
194 --
195 per_esa_shd.constraint_error('PER_ESTAB_ATTENDANCES_UK');
196 --
197 end if;
198 --
199 close c2;
200 --
201 end if;
202 else
203 --
204 -- check if the changes made to the above parameters result in a unique
205 -- record existing in the per_establishment_attendances table, if not raise an
206 -- error as someone is trying to enter the same person twice.
207 --
208 open c1;
209 --
210 fetch c1 into l_dummy;
211 --
212 if c1%found then
213 --
214 close c1;
215 --
216 -- raise error as record alreasy exists
217 --
218 per_esa_shd.constraint_error('PER_ESTAB_ATTENDANCES_UK');
219 --
220 end if;
221 --
222 close c1;
223 --
224 end if;
225 end if;
226 --
227 hr_utility.set_location(l_proc, 9);
228 --
229 --UPDATE of person_id not allowed unless currently null(U)
230 --
231 if (l_api_updating
232 and nvl(per_esa_shd.g_old_rec.person_id,hr_api.g_number) <> hr_api.g_number
233 and per_esa_shd.g_old_rec.person_id <> p_person_id
234 ) then
235 --
236 hr_utility.set_message(800, 'HR_289948_INV_UPD_PERSON_ID');
237 hr_utility.raise_error;
238 --
239 end if;
240 --
241 hr_utility.set_location(' Leaving:'||l_proc, 10);
242 --
243 End chk_person_id;
244 --
245 -- ----------------------------------------------------------------------------
246 -- |--------------------------< chk_party_id >--------------------------------|
247 -- ----------------------------------------------------------------------------
248 --
249 --
250 -- Description:
251 -- - Validates that the person_id and the party_id are matched in
252 -- per_all_people_f
253 -- and if person_id is not null and party_id is null, derive party_id
254 -- from per_all_people_f from person_id
255 --
256 -- Pre_conditions:
257 -- A valid business_group_id
258 --
259 -- In Arguments:
260 -- A Pl/Sql record structre.
261 -- effective_date
262
263 --
264 -- Post Success:
265 -- Process continues if :
266 --
267 -- Post Failure:
268 -- An application error is raised and processing is terminated if any of
269
270 -- Access Status:
271 -- Internal Table Handler Use Only.
272 --
273 --
274 Procedure chk_party_id(
275 p_rec in out nocopy per_esa_shd.g_rec_type
276 ,p_effective_date in date
277 )is
278 --
279 l_proc varchar2(72) := g_package||'chk_party_id';
280 l_party_id per_establishment_attendances.party_id%TYPE;
281 l_party_id2 per_establishment_attendances.party_id%TYPE;
282 --
283 --
284 -- cursor to check that the party_id maches person_id
285 --
286 cursor csr_get_party_id is
287 select party_id
288 from per_all_people_f per
289 where per.person_id = p_rec.person_id
290 and p_effective_date
291 between per.effective_start_date
292 and nvl(per.effective_end_date,hr_api.g_eot);
293 --
294 cursor csr_valid_party_id is
295 select party_id
296 from hz_parties hzp
297 where hzp.party_id = p_rec.party_id;
298 --
299 begin
300 hr_utility.set_location('Entering:'|| l_proc, 1);
301 --
302 --
303 if p_rec.person_id is not null then
304 open csr_get_party_id;
305 fetch csr_get_party_id into l_party_id;
306 close csr_get_party_id;
307 hr_utility.set_location(l_proc,20);
308 if p_rec.party_id is not null then
309 if p_rec.party_id <> nvl(l_party_id,-1) then
310 hr_utility.set_message(800, 'HR_289343_PERSONPARTY_MISMATCH');
311 hr_utility.set_location(l_proc,30);
312 hr_utility.raise_error;
313 end if;
314 else
315 --
316 -- derive party_id from per_all_people_f using person_id
317 --
318 hr_utility.set_location(l_proc,50);
319 p_rec.party_id := l_party_id;
320 end if;
321 else
322 if p_rec.party_id is null then
323 hr_utility.set_message(800, 'HR_289341_CHK_PERSON_OR_PARTY');
324 hr_utility.set_location(l_proc,60);
325 hr_utility.raise_error;
326 else
327 open csr_valid_party_id;
328 fetch csr_valid_party_id into l_party_id2;
329 if csr_valid_party_id%notfound then
330 close csr_valid_party_id;
331 hr_utility.set_message(800, 'PER_289342_PARTY_ID_INVALID');
332 hr_utility.set_location(l_proc,70);
333 hr_utility.raise_error;
334 end if;
335 close csr_valid_party_id;
336 end if;
337 end if;
338 --
339 hr_utility.set_location(' Leaving:'||l_proc,100);
340 End chk_party_id;
341 -- ----------------------------------------------------------------------------
342 -- |------------------------< chk_address_constraints >-----------------------|
343 -- ----------------------------------------------------------------------------
344 --
345 -- Description
346 -- This procedure is used to check that the establishment_id is not being
347 -- updated at the same time as the address is being changed.
348 --
349 -- Pre Conditions
350 -- None.
351 --
352 -- In Parameters
353 -- address establishment address
354 -- attendance_id PK of record being inserted or updated.
355 -- effective_date effective date
356 -- object_version_number Object version number of record being
357 -- inserted or updated.
358 --
359 -- Post Success
360 -- Processing continues
361 --
362 -- Post Failure
363 -- Error handled by procedure
364 --
365 -- Access Status
366 -- Internal table handler use only.
367 --
368 Procedure chk_address_constraints(p_address in varchar2
369 ,p_establishment_id in number
370 ,p_object_version_number in number
371 ,p_attendance_id in number) is
372 --
373 l_proc varchar2(72) := g_package||'chk_address_constraints';
374 l_api_updating boolean;
375
376 l_temp varchar2(4000) := per_esa_shd.g_old_rec.address;
377 --
378 Begin
379 --
380 hr_utility.set_location('Entering:'||l_proc, 5);
381 --
382 l_api_updating := per_esa_shd.api_updating
383 (p_attendance_id => p_attendance_id,
384 p_object_version_number => p_object_version_number);
385
386 hr_utility.set_location(p_address,6);
387 hr_utility.set_location(l_temp,6);
388 --hr_utility.set_location(l_api_updating,6);
389 --
390 if (l_api_updating
391 and nvl(p_address,hr_api.g_varchar2)
392 <> nvl(per_esa_shd.g_old_rec.address,hr_api.g_varchar2)
393 or not l_api_updating) then
394 --
395 -- check if establishment_id is changeing at the same time as the address
396 --
397 hr_utility.set_location(p_address,10);
398 hr_utility.set_location(p_establishment_id,10);
399 --
400 if nvl(p_establishment_id,hr_api.g_number) <> nvl(per_esa_shd.g_old_rec.establishment_id,hr_api.g_number) and p_attendance_id is not null then
401 --
402 -- raise error as does not exist as lookup
403 --
404 fnd_message.set_name('PER', 'HR_289587_ESA_ADDRESS_UPDATE');
405 hr_utility.raise_error;
406 --
407 end if;
408
409 elsif (l_api_updating
410 and nvl(p_establishment_id,hr_api.g_number)
411 <> per_esa_shd.g_old_rec.establishment_id
412 or not l_api_updating) then
413
414 hr_utility.set_location('Establishment Updating',15);
415 hr_utility.set_location(p_address,15);
416 --
417 -- check if establishment_id is being changed when the address is not null
418 --
419
420 if p_address is not null then
421
422 --
423 -- Raise error to get user to delete the address of old establishment before
424 -- updating the establishment_id
425 --
426 fnd_message.set_name('PER', 'HR_289586_ESA_ADDRESS_NULL');
427 hr_utility.raise_error;
428
429 end if;
430 --
431 end if;
432 --
433 hr_utility.set_location('Leaving:'||l_proc,20);
434 --
438 -- |-----------------------------< chk_full_time >----------------------------|
435 end chk_address_constraints;
436 --
437 -- ----------------------------------------------------------------------------
439 -- ----------------------------------------------------------------------------
440 --
441 -- Description
442 -- This procedure is used to check that the full_time lookup has a value of
443 -- Y/N, i.e. falls within the YES_NO lookup.
444 --
445 -- Pre Conditions
446 -- None.
447 --
448 -- In Parameters
449 -- attendance_id PK of record being inserted or updated.
450 -- full_time whether the attendance is full or
451 -- part time uses YES_NO lookup.
452 -- effective_date effective date
453 -- object_version_number Object version number of record being
454 -- inserted or updated.
455 --
456 -- Post Success
457 -- Processing continues
458 --
459 -- Post Failure
460 -- Error handled by procedure
461 --
462 -- Access Status
463 -- Internal table handler use only.
464 --
465 Procedure chk_full_time(p_attendance_id in number,
466 p_full_time in varchar2,
467 p_effective_date in date,
468 p_object_version_number in number) is
469 --
470 l_proc varchar2(72) := g_package||'chk_full_time';
471 l_api_updating boolean;
472 --
473 Begin
474 --
475 hr_utility.set_location('Entering:'||l_proc, 5);
476 --
477 l_api_updating := per_esa_shd.api_updating
478 (p_attendance_id => p_attendance_id,
479 p_object_version_number => p_object_version_number);
480 --
481 if (l_api_updating
482 and nvl(p_full_time,hr_api.g_varchar2)
483 <> per_esa_shd.g_old_rec.full_time
484 or not l_api_updating) then
485 --
486 -- check if value of full time falls within Full or Part time
487 --
488 hr_utility.set_location(p_full_time,100);
489 --
490 if hr_api.not_exists_in_hr_lookups(p_lookup_type => 'YES_NO',
491 p_lookup_code => p_full_time,
492 p_effective_date => p_effective_date) then
493 --
494 -- raise error as does not exist as lookup
495 --
496 hr_utility.set_message(801,'HR_51511_ESA_FULL_TIME_LKP');
497 hr_utility.raise_error;
498 --
499 end if;
500 --
501 end if;
502 --
503 hr_utility.set_location('Leaving:'||l_proc,10);
504 --
505 end chk_full_time;
506 -- ----------------------------------------------------------------------------
507 -- |--------------------------< chk_estab_location >--------------------------|
508 -- ----------------------------------------------------------------------------
509 --
510 -- Description
511 -- This procedure is used to check that either the establishment_id or the
512 -- establishment is populated. If the establishment id is populated then it
513 -- must exist in the per_establishments table.
514 --
515 -- Pre Conditions
516 -- None.
517 --
518 -- In Parameters
519 -- attendance_id PK of record being inserted or updated.
520 -- establishment_id id of establishment
521 -- establishment establishment location
522 -- object_version_number Object version number of record being
523 -- inserted or updated.
524 --
525 -- Post Success
526 -- Processing continues
527 --
528 -- Post Failure
529 -- Error handled by procedure
530 --
531 -- Access Status
532 -- Internal table handler use only.
533 --
534 Procedure chk_estab_location(p_attendance_id in number,
535 p_establishment_id in number,
536 p_establishment in varchar2,
537 p_object_version_number in number) is
538 --
539 l_proc varchar2(72) := g_package||'chk_estab_location';
540 l_api_updating boolean;
541 l_dummy varchar2(1);
542 --
543 cursor c1 is
544 select null
545 from per_establishments per
546 where per.establishment_id = p_establishment_id;
547 --
548 Begin
549 --
550 hr_utility.set_location('Entering:'||l_proc, 5);
551 --
552 l_api_updating := per_esa_shd.api_updating
553 (p_attendance_id => p_attendance_id,
554 p_object_version_number => p_object_version_number);
555 --
556 if (l_api_updating
557 or not l_api_updating) then
558 --
559 -- check if establishment and establishment attendance id are populated
560 -- only one should be populated.
561 --
562 if (p_establishment_id is null
563 and p_establishment is null
564 or (p_establishment is not null
565 and p_establishment_id is not null)) then
566 --
567 -- raise error as either establishmnet or establishment_id must be
568 -- populated but not both.
569 --
570 hr_utility.set_message(801,'HR_51495_ESA_ESTAB_ID_NULL');
571 hr_utility.raise_error;
572 --
573 end if;
577 --
574 --
575 -- check if establishment id has changed and if so does the establishment
576 -- exist in per_establishments table.
578 if p_establishment_id is not null
579 and (not l_api_updating
580 or (l_api_updating
581 and nvl(p_establishment_id,hr_api.g_number)
582 <> per_esa_shd.g_old_rec.establishment_id)) then
583 --
584 -- check if establishment id exists in per_establishments table
585 --
586 open c1;
587 --
588 fetch c1 into l_dummy;
589 --
590 if c1%notfound then
591 --
592 close c1;
593 --
594 -- raise error as establishment_id doesn't exist
595 --
596 per_esa_shd.constraint_error('PER_ESTAB_ATTENDANCES_FK1');
597 --
598 end if;
599 --
600 close c1;
601 --
602 end if;
603 --
604 end if;
605 --
606 hr_utility.set_location('Leaving:'||l_proc, 5);
607 --
608 end chk_estab_location;
609 --
610 -- ----------------------------------------------------------------------------
611 -- |----------------------------< chk_att_overlap >---------------------------|
612 -- ----------------------------------------------------------------------------
613 --
614 -- Description
615 -- This procedure is used to check if an establishment attendance overlap
616 -- occurs at the same establishment for the same person. It is also used
617 -- to check that any qualification records that use the establishment
618 -- attendance id do not have a qualification start date that falls
619 -- outside of the attendance start dates.
620 --
621 -- Bug: 1664075 Starts here.
622 --
623 -- This procedure also checks if the start date of the school attended
624 -- entered is less than date of birth of the person if exists. Raises error
625 -- message if there is no date of birth provided or if start date is less
626 -- than the date of birth of the person.
627 --
628 -- Bug: 1664075 Ends here.
629 --
630 -- Pre Conditions
631 -- None.
632 --
633 -- In Parameters
634 -- attendance_id PK of record being inserted or updated.
635 -- establishment_id id of establishment
636 -- person_id id of person being inserted
637 -- attended_start_date date attendance started.
638 -- attended_end_date date attendance finished.
639 -- establishment establishment location
640 -- object_version_number Object version number of record being
641 -- inserted or updated.
642 -- party_id id of party -- HR/TCA merge
643 --
644 -- Post Success
645 -- Processing continues
646 --
647 -- Post Failure
648 -- Error handled by procedure
649 --
650 -- Access Status
651 -- Internal table handler use only.
652 --
653 Procedure chk_att_overlap(p_attendance_id in number,
654 p_establishment_id in number,
655 p_person_id in number,
656 p_attended_start_date in date,
657 p_attended_end_date in date,
658 p_object_version_number in number,
659 -- Bug: 1664075 Starts here.
660 p_effective_date in date,
661 -- Bug: 1664075 Ends here.
662 p_party_id in number -- HR/TCA merge
663 ) is
664 --
665 l_proc varchar2(72) := g_package||'chk_att_overlap';
666 l_api_updating boolean;
667 l_dummy varchar2(1);
668 l_dob date;
669 --
670 cursor c1 is
671 select null
672 from per_establishment_attendances per
673 where per.person_id = p_person_id
674 and per.establishment_id = p_establishment_id
675 and per.attendance_id <> nvl(p_attendance_id,-1)
676 and (nvl(p_attended_start_date,hr_api.g_sot)
677 between per.attended_start_date
678 and nvl(per.attended_end_date,hr_api.g_eot)
679 or nvl(p_attended_end_date,hr_api.g_eot)
680 between per.attended_start_date
681 and nvl(per.attended_end_date,hr_api.g_eot));
682 --
683 cursor c2 is
684 select null
685 from per_qualifications per
686 where per.attendance_id = p_attendance_id
687 and (per.start_date
688 not between nvl(p_attended_start_date,hr_api.g_sot)
689 and nvl(p_attended_end_date,per.start_date)
690 or per.end_date
691 not between nvl(p_attended_start_date,hr_api.g_sot)
692 and nvl(p_attended_end_date,per.end_date));
693 -- HR/TCA merge
694 -- For party_id
695 cursor c3 is
696 select null
697 from per_establishment_attendances per
698 where per.party_id = p_party_id
699 and per.establishment_id = p_establishment_id
700 and per.attendance_id <> nvl(p_attendance_id,-1)
701 and (nvl(p_attended_start_date,hr_api.g_sot)
702 between per.attended_start_date
703 and nvl(per.attended_end_date,hr_api.g_eot)
704 or nvl(p_attended_end_date,hr_api.g_eot)
705 between per.attended_start_date
709 --
706 and nvl(per.attended_end_date,hr_api.g_eot));
707 --
708 -- Bug: 1664075 Starts here
710 cursor c4 is
711 select DATE_OF_BIRTH
712 from per_all_people_f per
713 where per.person_id = p_person_id
714 and p_effective_date
715 between per.effective_start_date
716 and nvl(per.effective_end_date,hr_api.g_eot)
717 and date_of_birth is not null;
718 --
719 -- Bug: 1664075 Ends here.
720 --
721 Begin
722 --
723 hr_utility.set_location('Entering:'||l_proc, 5);
724 --
725 l_api_updating := per_esa_shd.api_updating
726 (p_attendance_id => p_attendance_id,
727 p_object_version_number => p_object_version_number);
728 --
729 if l_api_updating then
730 hr_utility.set_location('Entering:chk_start'||p_attended_start_date,121);
731 hr_utility.set_location('Entering:chk_start'||per_esa_shd.g_old_rec.attended_start_date,121);
732 hr_utility.set_location('Entering:chk_end'||p_attended_end_date,212);
733 hr_utility.set_location('Entering:chk_start'||per_esa_shd.g_old_rec.attended_end_date,121);
734 end if;
735 if (l_api_updating
736 and (nvl(p_attended_start_date,hr_api.g_date)
737 <> nvl(per_esa_shd.g_old_rec.attended_start_date,hr_api.g_date)
738 or nvl(p_attended_end_date,hr_api.g_date)
739 <> nvl(per_esa_shd.g_old_rec.attended_end_date,hr_api.g_date))
740 or not l_api_updating) then
741 --
742 -- check if attended start date is null
743 --
744 --
745 -- Bug: 1664075 Starts here.
746 --
747 -- Check if the date of birth of the person is null or start date is less
748 -- than the date of birth of the person.
749 --
750 hr_utility.set_location('Entering:'||l_proc, 15);
751 open c4;
752 fetch c4 into l_dob;
753 if (c4%found) then
754 /* if (l_dob is null) then
755 close c4;
756 hr_utility.set_message(800,'HR_289737_SCH_ATTND_NULL_DOB');
757 hr_utility.raise_error;
758 elsif (p_attended_start_date is not null) and (l_dob > nvl(p_attended_start_date,hr_api.g_sot) ) then
759 close c4;
760 hr_utility.set_message(800,'HR_289384_SCH_ATND_START_DATE');
761 hr_utility.raise_error;
762 end if;
763 */
764
765 --
766 -- Bug# 2968084 Start Here
767 --
768 -- Description : Added extra validation to check for start and end date. This is due to change that start date is not mandatory
769 --
770 -- Validate date of birth, start date and end date for the schools and colleges attended
771 --
772 --
773 if (p_attended_start_date is not null) and (p_attended_end_date is null) and l_dob > nvl(p_attended_start_date,hr_api.g_sot) then
774 close c4;
775 hr_utility.set_message(800,'HR_289384_SCH_ATND_START_DATE');
776 hr_utility.raise_error;
777 elsif (p_attended_start_date is null) and (p_attended_end_date is not null) and (l_dob > nvl(p_attended_end_date,hr_api.g_sot)) then
778 close c4;
779 hr_utility.set_message(800,'PER_289498_ATT_END_DATE');
780 hr_utility.raise_error;
781 elsif (p_attended_start_date is not null) and (p_attended_end_date is not null) then
782 if (l_dob > p_attended_start_date) and (l_dob > p_attended_end_date) and (p_attended_start_date > p_attended_end_date) then
783 close c4;
784 hr_utility.set_message(800,'PER_289497_ATT_ST_END_DATE');
785 hr_utility.raise_error;
786 elsif (l_dob > p_attended_start_date) and (l_dob > p_attended_end_date) then
787 close c4;
788 hr_utility.set_message(800,'PER_289497_ATT_ST_END_DATE');
789 hr_utility.raise_error;
790 elsif (l_dob > p_attended_start_date) and (p_attended_start_date > p_attended_end_date) then
791 close c4;
792 elsif (l_dob > p_attended_end_date) and (p_attended_start_date > p_attended_end_date) then
793 close c4;
794 hr_utility.set_message(800,'PER_289499_ATT_END_DOB_DATE');
795 hr_utility.raise_error;
796 elsif (l_dob > p_attended_start_date) then
797 close c4;
798 hr_utility.set_message(800,'HR_289384_SCH_ATND_START_DATE');
799 hr_utility.raise_error;
800 elsif (l_dob > p_attended_end_date) then
801 close c4;
802 elsif (p_attended_start_date > p_attended_end_date) then
803 close c4;
804 hr_utility.set_message(800,'HR_51496_ESA_ATT_END_DATE');
805 hr_utility.raise_error;
806 end if;
807
808 end if;
809 end if;
810 close c4;
811
812 -- Bug# 2968084 Ends Here
813 --
814 -- Bug: 1664075 Ends here.
815 --
816 -- check if the attended start date is later than the attended end date
817 --
818 hr_utility.set_location('Entering:'||l_proc, 20);
819 if nvl(p_attended_start_date,hr_api.g_sot) > nvl(p_attended_end_date,nvl(p_attended_start_date,hr_api.g_sot))
820 then
821 --
822 -- raise error
823 --
824 hr_utility.set_message(800,'HR_51496_ESA_ATT_END_DATE'); -- Bug 3487909
825 hr_utility.raise_error;
826 --
827 end if;
828 --
829 -- check if qualification dates are within the range of the attendance
830 -- start dates that they reference
834 --
831 -- Fix for WWBUG 2502284.
832 /*
833 open c2;
835 fetch c2 into l_dummy;
836 --
837 if c2%found then
838 --
839 close c2;
840 --
841 -- raise error as qualification dates outside attendance dates
842 --
843 hr_utility.set_message(801,'HR_51596_ESA_QUAL_DATE_INV');
844 hr_utility.raise_error;
845 --
846 end if;
847 --
848 close c2;
849 --
850 */
851 -- check if a date overlap occurs for the same person at the same
852 -- establishment.
853 --
854 /* if p_person_id is not null then -- HR/TCA merge
855 open c1;
856 --
857 fetch c1 into l_dummy;
858 --
859 if c1%found then
860 --
861 close c1;
862 --
863 -- raise error as establishment attendance dates overlap
864 --
865 hr_utility.set_message(801,'HR_51497_ESA_CHK_ATT_OVERLAP');
866 hr_utility.raise_error;
867 --
868 end if;
869 --
870 close c1;
871 --
872 els */
873 /* if p_party_id is not null then
874 open c3;
875 --
876 fetch c3 into l_dummy;
877 --
878 if c3%found then
879 --
880 close c3;
881 --
882 -- raise error as establishment attendance dates overlap
883 --
884 hr_utility.set_message(801,'HR_51497_ESA_CHK_ATT_OVERLAP');
885 hr_utility.raise_error;
886 --
887 end if;
888 --
889 close c3;
890 --
891 end if;*/
892 end if;
893 --
894 hr_utility.set_location('Leaving:'||l_proc, 5);
895 --
896 end chk_att_overlap;
897 --
898 -- ----------------------------------------------------------------------------
899 -- |-----------------------------< chk_estab_bg >-----------------------------|
900 -- ----------------------------------------------------------------------------
901 --
902 -- Description
903 -- This procedure is used to check if an establishment attendance is in the
904 -- same business group as the person.
905 --
906 -- Pre Conditions
907 -- None.
908 --
909 -- In Parameters
910 -- attendance_id PK of record being inserted or updated.
911 -- business_group_id id of business group
912 -- person_id id of person being inserted
913 -- object_version_number Object version number of record being
914 -- inserted or updated.
915 -- effective_date Effective date
916 --
917 -- Post Success
918 -- Processing continues
919 --
920 -- Post Failure
921 -- Error handled by procedure
922 --
923 -- Access Status
924 -- Internal table handler use only.
925 --
926 Procedure chk_estab_bg(p_attendance_id in number,
927 p_business_group_id in number,
928 p_person_id in number,
929 p_object_version_number in number,
930 p_effective_date in date) is
931 --
932 l_proc varchar2(72) := g_package||'chk_estab_bg';
933 l_api_updating boolean;
934 l_dummy varchar2(1);
935 --
936 -- Bug fix 3648650.
937 -- cursor modified. nvl(person_id) is removed. This procedure
938 -- is called only if person id is not null. This will improve
939 -- performance.
940
941 cursor c1 is
942 select null
943 from per_people_f per
944 where per.person_id = p_person_id
945 and per.business_group_id =nvl(p_business_group_id,per.business_group_id)
946 and trunc(p_effective_date)
947 between trunc(per.effective_start_date)
948 and nvl(trunc(per.effective_end_date),trunc(per.effective_end_date));
949 --
950 Begin
951 --
952 hr_utility.set_location('Entering:'||l_proc, 5);
953 --
954 l_api_updating := per_esa_shd.api_updating
955 (p_attendance_id => p_attendance_id,
956 p_object_version_number => p_object_version_number);
957 --
958 if (l_api_updating
959 and nvl(p_person_id,hr_api.g_number) <> per_esa_shd.g_old_rec.person_id
960 or not l_api_updating) then
961 --
962 -- check if the person exists in the per_people_f table for the same
963 -- business group
964 --
965 open c1;
966 --
967 fetch c1 into l_dummy;
968 --
969 if c1%notfound then
970 --
971 close c1;
972 --
973 -- raise error as person does not exist for this business group
974 --
975 hr_utility.set_message(801,'HR_51503_ESA_ESTAB_BG');
976 hr_utility.raise_error;
977 --
978 end if;
979 --
980 close c1;
981 --
982 end if;
983 --
984 hr_utility.set_location(l_proc, 4);
985 --
986 --UPDATE of Business_group_id is not allowed unless currently null(U)
987 --
988 if (l_api_updating
989 and nvl(per_esa_shd.g_old_rec.business_group_id,hr_api.g_number)
990 <> hr_api.g_number
994 hr_utility.raise_error;
991 and per_esa_shd.g_old_rec.business_group_id <> p_business_group_id ) then
992 --
993 hr_utility.set_message(800, 'HR_289947_INV_UPD_BG_ID');
995 --
996 end if;
997 --
998 hr_utility.set_location('Leaving:'||l_proc, 5);
999 --
1000 end chk_estab_bg;
1001 --
1002 -- ----------------------------------------------------------------------------
1003 -- |-----------------------------< chk_estab_att_delete >---------------------|
1004 -- ----------------------------------------------------------------------------
1005 --
1006 -- Description
1007 -- This procedure is used to check if an establishment attendance can be
1008 -- deleted.
1009 --
1010 -- Pre Conditions
1011 -- None.
1012 --
1013 -- In Parameters
1014 -- attendance_id PK of record being inserted or updated.
1015 -- object_version_number Object version number of record being
1016 -- inserted or updated.
1017 --
1018 -- Post Success
1019 -- Processing continues
1020 --
1021 -- Post Failure
1022 -- Error handled by procedure
1023 --
1024 -- Access Status
1025 -- Internal table handler use only.
1026 --
1027 Procedure chk_estab_att_delete(p_attendance_id in number,
1028 p_object_version_number in number) is
1029 --
1030 l_proc varchar2(72) := g_package||'chk_estab_att_delete';
1031 l_api_updating boolean;
1032 l_dummy varchar2(1);
1033 --
1034 cursor c1 is
1035 select null
1036 from per_qualifications per
1037 where per.attendance_id = p_attendance_id;
1038 --
1039 Begin
1040 --
1041 hr_utility.set_location('Entering:'||l_proc,10);
1042 --
1043 open c1;
1044 --
1045 fetch c1 into l_dummy;
1046 --
1047 if c1%found then
1048 --
1049 close c1;
1050 --
1051 -- raise error as attendance_id is referenced.
1052 --
1053 hr_utility.set_message(801,'HR_51580_ESA_ESTAB_ATT_DEL');
1054 hr_utility.raise_error;
1055 --
1056 end if;
1057 --
1058 close c1;
1059 --
1060 hr_utility.set_location('Leaving:'||l_proc,10);
1061 --
1062 end chk_estab_att_delete;
1063 --
1064 -- -----------------------------------------------------------------------
1065 -- |------------------------------< chk_df >-----------------------------|
1066 -- -----------------------------------------------------------------------
1067 --
1068 -- Description:
1069 -- Validates the all Descriptive Flexfield values.
1070 --
1071 -- Pre-conditions:
1072 -- All other columns have been validated. Must be called as the
1073 -- last step from insert_validate and update_validate.
1074 --
1075 -- In Arguments:
1076 -- p_rec
1077 --
1078 -- Post Success:
1079 -- If the Descriptive Flexfield structure column and data values are
1080 -- all valid this procedure will end normally and processing will
1081 -- continue.
1082 --
1083 -- Post Failure:
1084 -- If the Descriptive Flexfield structure column value or any of
1085 -- the data values are invalid then an application error is raised as
1086 -- a PL/SQL exception.
1087 --
1088 -- Access Status:
1089 -- Internal Row Handler Use Only.
1090 --
1091 procedure chk_df
1092 (p_rec in per_esa_shd.g_rec_type) is
1093 --
1094 l_proc varchar2(72) := g_package||'chk_df';
1095 --
1096 begin
1097 hr_utility.set_location('Entering:'||l_proc, 10);
1098 --
1099 if ((p_rec.attendance_id is not null) and (
1100 nvl(per_esa_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
1101 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
1102 nvl(per_esa_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
1103 nvl(p_rec.attribute1, hr_api.g_varchar2) or
1104 nvl(per_esa_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
1105 nvl(p_rec.attribute2, hr_api.g_varchar2) or
1106 nvl(per_esa_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
1107 nvl(p_rec.attribute3, hr_api.g_varchar2) or
1108 nvl(per_esa_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
1109 nvl(p_rec.attribute4, hr_api.g_varchar2) or
1110 nvl(per_esa_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
1111 nvl(p_rec.attribute5, hr_api.g_varchar2) or
1112 nvl(per_esa_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
1113 nvl(p_rec.attribute6, hr_api.g_varchar2) or
1114 nvl(per_esa_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
1115 nvl(p_rec.attribute7, hr_api.g_varchar2) or
1116 nvl(per_esa_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
1117 nvl(p_rec.attribute8, hr_api.g_varchar2) or
1118 nvl(per_esa_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
1119 nvl(p_rec.attribute9, hr_api.g_varchar2) or
1120 nvl(per_esa_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
1121 nvl(p_rec.attribute10, hr_api.g_varchar2) or
1122 nvl(per_esa_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
1123 nvl(p_rec.attribute11, hr_api.g_varchar2) or
1124 nvl(per_esa_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
1125 nvl(p_rec.attribute12, hr_api.g_varchar2) or
1126 nvl(per_esa_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
1127 nvl(p_rec.attribute13, hr_api.g_varchar2) or
1128 nvl(per_esa_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
1132 nvl(per_esa_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
1129 nvl(p_rec.attribute14, hr_api.g_varchar2) or
1130 nvl(per_esa_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
1131 nvl(p_rec.attribute15, hr_api.g_varchar2) or
1133 nvl(p_rec.attribute16, hr_api.g_varchar2) or
1134 nvl(per_esa_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
1135 nvl(p_rec.attribute17, hr_api.g_varchar2) or
1136 nvl(per_esa_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
1137 nvl(p_rec.attribute18, hr_api.g_varchar2) or
1138 nvl(per_esa_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
1139 nvl(p_rec.attribute19, hr_api.g_varchar2) or
1140 nvl(per_esa_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
1141 nvl(p_rec.attribute20, hr_api.g_varchar2)))
1142 or
1143 (p_rec.attendance_id is null) then
1144 --
1145 -- Only execute the validation if absolutely necessary:
1146 -- a) During update, the structure column value or any
1147 -- of the attribute values have actually changed.
1148 -- b) During insert.
1149 --
1150 hr_dflex_utility.ins_or_upd_descflex_attribs
1151 (p_appl_short_name => 'PER'
1152 ,p_descflex_name => 'PER_ESTABLISHMENT_ATTENDANCES'
1153 ,p_attribute_category => p_rec.attribute_category
1154 ,p_attribute1_name => 'ATTRIBUTE1'
1155 ,p_attribute1_value => p_rec.attribute1
1156 ,p_attribute2_name => 'ATTRIBUTE2'
1157 ,p_attribute2_value => p_rec.attribute2
1158 ,p_attribute3_name => 'ATTRIBUTE3'
1159 ,p_attribute3_value => p_rec.attribute3
1160 ,p_attribute4_name => 'ATTRIBUTE4'
1161 ,p_attribute4_value => p_rec.attribute4
1162 ,p_attribute5_name => 'ATTRIBUTE5'
1163 ,p_attribute5_value => p_rec.attribute5
1164 ,p_attribute6_name => 'ATTRIBUTE6'
1165 ,p_attribute6_value => p_rec.attribute6
1166 ,p_attribute7_name => 'ATTRIBUTE7'
1167 ,p_attribute7_value => p_rec.attribute7
1168 ,p_attribute8_name => 'ATTRIBUTE8'
1169 ,p_attribute8_value => p_rec.attribute8
1170 ,p_attribute9_name => 'ATTRIBUTE9'
1171 ,p_attribute9_value => p_rec.attribute9
1172 ,p_attribute10_name => 'ATTRIBUTE10'
1173 ,p_attribute10_value => p_rec.attribute10
1174 ,p_attribute11_name => 'ATTRIBUTE11'
1175 ,p_attribute11_value => p_rec.attribute11
1176 ,p_attribute12_name => 'ATTRIBUTE12'
1177 ,p_attribute12_value => p_rec.attribute12
1178 ,p_attribute13_name => 'ATTRIBUTE13'
1179 ,p_attribute13_value => p_rec.attribute13
1180 ,p_attribute14_name => 'ATTRIBUTE14'
1181 ,p_attribute14_value => p_rec.attribute14
1182 ,p_attribute15_name => 'ATTRIBUTE15'
1183 ,p_attribute15_value => p_rec.attribute15
1184 ,p_attribute16_name => 'ATTRIBUTE16'
1185 ,p_attribute16_value => p_rec.attribute16
1186 ,p_attribute17_name => 'ATTRIBUTE17'
1187 ,p_attribute17_value => p_rec.attribute17
1188 ,p_attribute18_name => 'ATTRIBUTE18'
1189 ,p_attribute18_value => p_rec.attribute18
1190 ,p_attribute19_name => 'ATTRIBUTE19'
1191 ,p_attribute19_value => p_rec.attribute19
1192 ,p_attribute20_name => 'ATTRIBUTE20'
1193 ,p_attribute20_value => p_rec.attribute20
1194 );
1195 end if;
1196 --
1197 hr_utility.set_location(' Leaving:'||l_proc, 20);
1198
1199 end chk_df;
1200 -- ----------------------------------------------------------------------------
1201 -- |---------------------------< insert_validate >----------------------------|
1202 -- ----------------------------------------------------------------------------
1203 Procedure insert_validate(p_rec in out nocopy per_esa_shd.g_rec_type,
1204 p_effective_date in date) is
1205 --
1206 l_proc varchar2(72) := g_package||'insert_validate';
1207 --
1208 Begin
1209 hr_utility.set_location('Entering:'||l_proc, 5);
1210 --
1211 -- only party_id is specified, validate business_group_id
1212 -- for HR/TCA merge
1213 --
1214 if p_rec.person_id is not null then
1215 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1216 end if;
1217 --
1218 -- Call all supporting business operations
1219 --
1220 -- Business Rule Mapping
1221 -- =====================
1222 -- CHK_ATTENDANCE_ID
1223 --
1224 chk_attendance_id(p_rec.attendance_id,
1225 p_rec.object_version_number);
1226 --
1227 -- Business Rule Mapping
1228 -- =====================
1229 -- CHK_PERSON_ID
1230 --
1231 chk_person_id(p_rec.attendance_id,
1232 p_rec.person_id,
1233 p_rec.attended_start_date,
1234 p_rec.establishment_id,
1235 p_rec.establishment,
1236 p_rec.business_group_id,
1237 p_rec.object_version_number,
1238 p_rec.party_id); -- HR/TCA merge
1239 --
1240 -- Business Rule Mapping
1241 -- =====================
1242 -- CHK_PARTY_ID
1243 --
1244 chk_party_id
1245 (p_rec
1246 ,p_effective_date
1247 );
1248
1249 --
1250
1251 --
1252 -- Business Rule Mapping
1253 -- =====================
1254 -- CHK_ESTAB_LOCATION
1255 -- CHK_ESTABLISHMENT_ID
1256 --
1257 chk_estab_location(p_rec.attendance_id,
1258 p_rec.establishment_id,
1262 -- Business Rule Mapping
1259 p_rec.establishment,
1260 p_rec.object_version_number);
1261 --
1263 -- =====================
1264 -- CHK_ATT_OVERLAP
1265 -- CHK_ATTENDED_START_DATE
1266 -- CHK_ATTENDED_END_DATE
1267 -- CHK_ATT_TO_DATE
1268 --
1269 chk_att_overlap (p_rec.attendance_id,
1270 p_rec.establishment_id,
1271 p_rec.person_id,
1272 p_rec.attended_start_date,
1273 p_rec.attended_end_date,
1274 p_rec.object_version_number,
1275 -- Bug: 1664075 Starts here.
1276 p_effective_date,
1277 -- Bug: 1664075 Ends here.
1278 p_rec.party_id); -- HR/TCA merge
1279 --
1280 -- Business Rule Mapping
1281 -- =====================
1282 -- CHK_ESTAB_BG
1283 --
1284 -- Validate bg ifperson_id isspecified.
1285 -- for HR/TCA merge
1286 if p_rec.person_id is not null then
1287 chk_estab_bg(p_rec.attendance_id,
1288 p_rec.business_group_id,
1289 p_rec.person_id,
1290 p_rec.object_version_number,
1291 p_effective_date);
1292 end if;
1293 --
1294 -- Business Rule Mapping
1295 -- =====================
1296 -- CHK_FULL_TIME
1297 --
1298 chk_full_time(p_rec.attendance_id,
1299 p_rec.full_time,
1300 p_effective_date,
1301 p_rec.object_version_number);
1302 --
1303 -- Check Address parameter in the instance if record being passed establishment_id
1304 -- is linked to the attendance record
1305 chk_address_constraints(p_rec.address,
1306 p_rec.establishment_id,
1307 p_rec.object_version_number,
1308 p_rec.attendance_id);
1309 --
1310 -- Descriptive flex check
1311 -- ======================
1312 --
1313 per_esa_bus.chk_df(p_rec => p_rec);
1314 --
1315 hr_utility.set_location(' Leaving:'||l_proc, 10);
1316 End insert_validate;
1317 --
1318 -- ----------------------------------------------------------------------------
1319 -- |---------------------------< update_validate >----------------------------|
1320 -- ----------------------------------------------------------------------------
1321 Procedure update_validate(p_rec in per_esa_shd.g_rec_type,
1322 p_effective_date in date) is
1323 --
1324 l_proc varchar2(72) := g_package||'update_validate';
1325 --
1326 Begin
1327 hr_utility.set_location('Entering:'||l_proc, 5);
1328 --
1329 --
1330 -- if person_id is specified, validate business_group_id
1331 -- for HR/TCA merge
1332 --
1333 if p_rec.person_id is not null then
1334 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1335 end if;
1336 --
1337 -- Call all supporting business operations
1338 --
1339 -- Business Rule Mapping
1340 -- =====================
1341 -- CHK_ATTENDANCE_ID
1342 --
1343 chk_attendance_id(p_rec.attendance_id,
1344 p_rec.object_version_number);
1345 --
1346 -- Business Rule Mapping
1347 -- =====================
1348 -- CHK_PERSON_ID
1349 --
1350 chk_person_id(p_rec.attendance_id,
1351 p_rec.person_id,
1352 p_rec.attended_start_date,
1353 p_rec.establishment_id,
1354 p_rec.establishment,
1355 p_rec.business_group_id,
1356 p_rec.object_version_number,
1357 p_rec.party_id); -- HR/TCA merge
1358 --
1359 -- Business Rule Mapping
1360 -- =====================
1361 -- CHK_ESTAB_LOCATION
1362 -- CHK_ESTABLISHMENT_ID
1363 --
1364 chk_estab_location(p_rec.attendance_id,
1365 p_rec.establishment_id,
1366 p_rec.establishment,
1367 p_rec.object_version_number);
1368 --
1369 -- Business Rule Mapping
1370 -- =====================
1371 -- CHK_ATT_OVERLAP
1372 -- CHK_ATTENDED_START_DATE
1373 -- CHK_ATTENDED_END_DATE
1374 -- CHK_ATT_TO_DATE
1375 --
1376 chk_att_overlap (p_rec.attendance_id,
1377 p_rec.establishment_id,
1378 p_rec.person_id,
1379 p_rec.attended_start_date,
1380 p_rec.attended_end_date,
1381 p_rec.object_version_number,
1382 -- Bug: 1664075 Starts here.
1383 p_effective_date,
1384 -- Bug: 1664075 Ends here.
1385 p_rec.party_id); -- HR/TCA merge
1386 --
1387 -- Business Rule Mapping
1388 -- =====================
1389 -- CHK_ESTAB_BG
1390 --
1391 -- Validate bg only if person_id is specified.
1392 -- for HR/TCA merge
1393 if p_rec.person_id is not null then
1394 chk_estab_bg(p_rec.attendance_id,
1395 p_rec.business_group_id,
1396 p_rec.person_id,
1397 p_rec.object_version_number,
1398 p_effective_date);
1399 end if;
1400 --
1401 -- Business Rule Mapping
1402 -- =====================
1403 -- CHK_FULL_TIME
1404 --
1405 chk_full_time(p_rec.attendance_id,
1406 p_rec.full_time,
1407 p_effective_date,
1408 p_rec.object_version_number);
1409 --
1410 -- Check Address parameter in the instance if record being passed establishment_id
1411 -- is linked to the attendance record
1412 --
1413 chk_address_constraints(p_rec.address,
1414 p_rec.establishment_id,
1415 p_rec.object_version_number,
1416 p_rec.attendance_id);
1417 --
1418 -- Descriptive Flex Check
1419 -- ======================
1420 --
1421 per_esa_bus.chk_df(p_rec => p_rec);
1422 --
1423 hr_utility.set_location(' Leaving:'||l_proc, 10);
1424 End update_validate;
1425 --
1426 -- ----------------------------------------------------------------------------
1427 -- |---------------------------< delete_validate >----------------------------|
1428 -- ----------------------------------------------------------------------------
1429 Procedure delete_validate(p_rec in per_esa_shd.g_rec_type) is
1430 --
1431 l_proc varchar2(72) := g_package||'delete_validate';
1432 --
1433 Begin
1434 hr_utility.set_location('Entering:'||l_proc, 5);
1435 --
1436 -- Call all supporting business operations
1437 --
1438 -- Business Rule Mapping
1439 -- =====================
1440 -- CHK_ESTAB_ATT_DELETE
1441 chk_estab_att_delete(p_rec.attendance_id,
1442 p_rec.object_version_number);
1443 --
1444 hr_utility.set_location(' Leaving:'||l_proc, 10);
1445 End delete_validate;
1446 --
1447 -- ---------------------------------------------------------------------------
1448 -- |---------------------< return_legislation_code >-------------------------|
1449 -- ---------------------------------------------------------------------------
1450 --
1451 function return_legislation_code
1452 (p_attendance_id in per_establishment_attendances.attendance_id%TYPE
1453 ) return varchar2 is
1454 --
1455 -- Cursor to find legislation code
1456 --
1457 cursor csr_leg_code is
1458 select pbg.legislation_code
1459 from per_business_groups pbg
1460 , per_establishment_attendances esa
1461 where esa.attendance_id = p_attendance_id
1462 and pbg.business_group_id = esa.business_group_id;
1463 --
1464 -- Cursor to find if the business group exists
1465 --
1466 cursor csr_no_bg is
1467 select 'Y'
1468 from per_establishment_attendances
1469 where attendance_id = p_attendance_id
1470 and business_group_id is null;
1471 --
1472 -- Declare local variables
1473 --
1474 l_no_business_group varchar2(1);
1475 l_legislation_code varchar2(150);
1476 l_proc varchar2(72) := g_package||'return_legislation_code';
1477 begin
1478 hr_utility.set_location('Entering:'|| l_proc, 10);
1479 --
1480 -- Ensure that all the mandatory parameter are not null
1481 --
1482 hr_api.mandatory_arg_error(p_api_name => l_proc,
1483 p_argument => 'attendance_id',
1484 p_argument_value => p_attendance_id);
1485 --
1486 open csr_no_bg;
1487 fetch csr_no_bg into l_no_business_group;
1488 if csr_no_bg%found then
1489 l_no_business_group := 'N';
1490 end if;
1491 close csr_no_bg;
1492 --
1493 if l_no_business_group = 'Y' then
1494 return null;
1495 end if;
1496 --
1497 if nvl(g_attendance_id, hr_api.g_number) = p_attendance_id then
1498 --
1499 -- The legislation has already been found with a previous
1500 -- call to this function. Just return the value in the global
1501 -- variable.
1502 --
1503 l_legislation_code := g_legislation_code;
1504 hr_utility.set_location(l_proc, 20);
1505 else
1506 --
1507 -- The ID is different to the last call to this function
1508 -- or this is the first call to this function.
1509 --
1510 open csr_leg_code;
1511 fetch csr_leg_code into l_legislation_code;
1512 if csr_leg_code%notfound then
1513 --
1514 -- The primary key is invalid therefore we must error
1515 --
1516 close csr_leg_code;
1517 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
1518 fnd_message.raise_error;
1519 end if;
1520 --
1521 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1522 --
1523 -- Set the global variables so the vlaues are
1524 -- available for the next call to this function
1525 --
1526 close csr_leg_code;
1527 g_attendance_id := p_attendance_id;
1528 g_legislation_code := l_legislation_code;
1529 end if;
1530 hr_utility.set_location('Entering:'|| l_proc, 40);
1531 --
1532 return l_legislation_code;
1533 end return_legislation_code;
1534 --
1535 end per_esa_bus;
[Home] [Help]