[Home] [Help]
PACKAGE BODY: APPS.PER_QUA_BUS
Source
1 Package Body per_qua_bus as
2 /* $Header: pequarhi.pkb 120.0.12010000.2 2008/08/06 09:31:13 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_qua_bus.'; -- Global package name
9 -- The following two global variables are only to be
10 -- used by the return_legislation_code function.
11 --
12 g_legislation_code varchar2(150) default null;
13 g_qualification_id number default null;
14 --
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 --
21 procedure set_security_group_id
22 (
23 p_qualification_id in per_qualifications.qualification_id%TYPE
24 ,p_associated_column1 in varchar2 default null
25 ) is
26 --
27 -- Declare cursor
28 --
29 cursor csr_sec_grp is
30 select inf.org_information14
31 from hr_organization_information inf
32 , per_qualifications qua
33 where qua.qualification_id = p_qualification_id
34 and inf.organization_id = qua.business_group_id
35 and inf.org_information_context || '' = 'Business Group Information';
36 --
37 -- Local variables
38 --
39 l_security_group_id number;
40 l_proc varchar2(72) := g_package||'set_security_group_id';
41 --
42 begin
43 hr_utility.set_location('Entering:'|| l_proc, 10);
44 --
45 -- Ensure that all the mandatory parameter are not null
46 --
47 hr_api.mandatory_arg_error(p_api_name => l_proc,
48 p_argument => 'qualification_id',
49 p_argument_value => p_qualification_id);
50 --
51 open csr_sec_grp;
52 fetch csr_sec_grp into l_security_group_id;
53 if csr_sec_grp%notfound then
54 close csr_sec_grp;
55 --
56 -- The primary key is invalid therefore we must error
57 --
58 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
59 hr_multi_message.add
60 (p_associated_column1 => nvl(p_associated_column1, 'QUALIFICATION_ID')
61 );
62 else
63 close csr_sec_grp;
64 --
65 -- Set the security_group_id in CLIENT_INFO
66 --
67 hr_api.set_security_group_id
68 (p_security_group_id => l_security_group_id
69 );
70 end if;
71 --
72 hr_utility.set_location(' Leaving:'|| l_proc, 20);
73 --
74 end set_security_group_id;
75 --
76 --
77 -- ---------------------------------------------------------------------------
78 -- |---------------------< return_legislation_code >-------------------------|
79 -- ---------------------------------------------------------------------------
80 --
81 Function return_legislation_code
82 (p_qualification_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_perf pbg
91 , per_qualifications qua
92 where qua.qualification_id = p_qualification_id
93 and pbg.business_group_id (+) = qua.business_group_id;
94 --
95 -- Declare local variables
96 --
97 l_legislation_code varchar2(150);
98 l_proc varchar2(72) := g_package||'return_legislation_code';
99 Begin
100 --
101 hr_utility.set_location('Entering:'|| l_proc, 10);
102 --
103 -- Ensure that all the mandatory parameter are not null
104 --
105 hr_api.mandatory_arg_error
106 (p_api_name => l_proc
107 ,p_argument => 'qualification_id'
108 ,p_argument_value => p_qualification_id
109 );
110 --
111 if ( nvl(per_qua_bus.g_qualification_id, hr_api.g_number)
112 = p_qualification_id) then
113 --
114 -- The legislation code has already been found with a previous
115 -- call to this function. Just return the value in the global
116 -- variable.
117 --
118 l_legislation_code := per_qua_bus.g_legislation_code;
119 hr_utility.set_location(l_proc, 20);
120 else
121 --
122 -- The ID is different to the last call to this function
123 -- or this is the first call to this function.
124 --
125 open csr_leg_code;
126 fetch csr_leg_code into l_legislation_code;
127 --
128 if csr_leg_code%notfound then
129 --
130 -- The primary key is invalid therefore we must error
131 --
132 close csr_leg_code;
133 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
134 fnd_message.raise_error;
135 end if;
136 hr_utility.set_location(l_proc,30);
137 --
138 -- Set the global variables so the values are
139 -- available for the next call to this function.
140 --
141 close csr_leg_code;
142 per_qua_bus.g_qualification_id := p_qualification_id;
143 per_qua_bus.g_legislation_code := l_legislation_code;
144 end if;
145 hr_utility.set_location(' Leaving:'|| l_proc, 40);
146 return l_legislation_code;
147 end return_legislation_code;
148 --
149 -- ----------------------------------------------------------------------------
150 -- |---------------------------< chk_qualification_id >-----------------------|
151 -- ----------------------------------------------------------------------------
152 --
153 -- Description
154 -- This procedure checks that a qualification_id is unique. This column
155 -- is the primary key for the entity and so must be null on insert and
156 -- non-updateable on update.
157 --
158 -- Pre-Conditions
159 -- None.
160 --
161 -- In Parameters
162 -- p_qualification_id PK
163 -- p_object_version_number object version number
164 --
165 -- Post Success
166 -- Processing continues
167 --
168 -- Post Failure
169 -- Error raised.
170 --
171 -- Access Status
172 -- Internal table handler use only.
173 --
174 Procedure chk_qualification_id (p_qualification_id in number,
175 p_object_version_number in number) is
176 --
177 l_proc varchar2(72) := g_package||'chk_qualification_id';
178 l_api_updating boolean;
179 --
180 Begin
181 --
182 hr_utility.set_location('Entering:'||l_proc,5);
183 --
184 l_api_updating := per_qua_shd.api_updating
185 (p_qualification_id => p_qualification_id,
186 p_object_version_number => p_object_version_number);
187 --
188 if (l_api_updating
189 and nvl(p_qualification_id,hr_api.g_number)
190 <> nvl(per_qua_shd.g_old_rec.qualification_id,hr_api.g_number)) then
191 --
192 -- raise error as PK has changed
193 --
194 per_qua_shd.constraint_error('PER_QUALIFICATIONS_PK');
195 --
196 elsif not l_api_updating then
197 --
198 -- check if PK is null
199 --
200 if p_qualification_id is not null then
201 --
202 -- raise error as PK is not null
203 --
204 per_qua_shd.constraint_error('PER_QUALIFICATIONS_PK');
205 --
206 end if;
207 --
208 end if;
209 --
210 hr_utility.set_location('Leaving:'||l_proc,10);
211 --
212 End chk_qualification_id;
213 --
214 -- ----------------------------------------------------------------------------
215 -- |---------------------------< chk_qualification_type_id >------------------|
216 -- ----------------------------------------------------------------------------
217 --
218 -- Description
219 -- This procedure checks that a qualification_type_id is unique. This column
220 -- is the primary key for the entity and so must be null on insert and
221 -- non-updateable on update.
222 --
223 -- Pre-Conditions
224 -- None.
225 --
226 -- In Parameters
227 -- p_qualification_id PK
228 -- p_qualification_type_id ID of qualification type
229 -- p_object_version_number object version number
230 --
231 -- Post Success
232 -- Processing continues
233 --
234 -- Post Failure
235 -- Error raised.
236 --
237 -- Access Status
238 -- Internal table handler use only.
239 --
240 Procedure chk_qualification_type_id (p_qualification_id in number,
241 p_qualification_type_id in number,
242 p_object_version_number in number) is
243 --
244 l_proc varchar2(72) := g_package||'chk_qualification_type_id';
245 l_api_updating boolean;
246 l_dummy varchar2(1);
247 --
248 cursor c1 is
249 select null
250 from per_qualification_types per
251 where per.qualification_type_id = p_qualification_type_id;
252 --
253 Begin
254 --
255 hr_utility.set_location('Entering:'||l_proc,5);
256 --
257 l_api_updating := per_qua_shd.api_updating
258 (p_qualification_id => p_qualification_id,
259 p_object_version_number => p_object_version_number);
260 --
261 if (l_api_updating
262 and nvl(p_qualification_type_id,hr_api.g_number)
263 <> nvl(per_qua_shd.g_old_rec.qualification_type_id,hr_api.g_number)
264 or not l_api_updating) then
265 --
266 -- check if qualification type exist in per_qualification_types table
267 --
268 open c1;
269 --
270 fetch c1 into l_dummy;
271 if c1%notfound then
272 --
273 close c1;
274 --
275 -- raise error as FK does not relate to PK in per_qualification_types
276 -- table.
277 --
278 per_qua_shd.constraint_error('PER_QUALIFICATIONS_FK2');
279 --
280 end if;
281 --
282 close c1;
283 --
284 end if;
285 --
286 hr_utility.set_location('Leaving:'||l_proc,10);
287 --
288 End chk_qualification_type_id;
289 --
290 -- ----------------------------------------------------------------------------
291 -- |---------------------------< chk_person_id >------------------------------|
292 -- ----------------------------------------------------------------------------
293 --
294 -- Description
295 -- This procedure checks that a person_id or attendance_id are populated.
296 -- The person_id must exist in the per_people_f table as of the effective date
297 -- and the attendance_id must exist in the per_establishment_attendances table.
298 --
299 -- Pre-Conditions
300 -- None.
301 --
302 -- In Parameters
303 -- p_effective_date effective date
304 -- p_qualification_id PK
305 -- p_person_id ID of person_id.
306 -- p_attendance_id ID of attendance.
307 -- p_object_version_number object version number
308 -- p_party_id ID of party -- HR/TCA merge
309 --
310 -- Post Success
311 -- Processing continues
312 --
313 -- Post Failure
314 -- Error raised.
315 --
316 -- Access Status
317 -- Internal table handler use only.
318 --
319 Procedure chk_person_id (
320 p_effective_date in date,
321 p_qualification_id in number,
322 p_person_id in number,
323 p_attendance_id in number,
324 p_object_version_number in number,
325 p_party_id in number
326 ) is
327 --
328 l_proc varchar2(72) := g_package||'chk_person_id';
329 l_api_updating boolean;
330 l_dummy varchar2(1);
331 --
332 cursor c1 is
333 select null
334 from per_all_people_f per -- Bug 3148893. Replaced per_all_people_f with per_people_f
335 where per.person_id = p_person_id
336 and p_effective_date
337 between per.effective_start_date
338 and nvl(per.effective_end_date,hr_api.g_eot);
339 --
340 cursor c2 is
341 select null
342 from per_establishment_attendances per
343 where per.attendance_id = p_attendance_id;
344 --
345 Begin
346 --
347 hr_utility.set_location('Entering:'||l_proc,5);
348 --
349 l_api_updating := per_qua_shd.api_updating
350 (p_qualification_id => p_qualification_id,
351 p_object_version_number => p_object_version_number);
352 --
353 if (l_api_updating
354 and (nvl(p_person_id,hr_api.g_number)
355 <> nvl(per_qua_shd.g_old_rec.person_id,hr_api.g_number)
356 or nvl(p_attendance_id,hr_api.g_number)
357 <> nvl(per_qua_shd.g_old_rec.attendance_id,hr_api.g_number))
358 or not l_api_updating) then
359 --
360 -- check if attendance_id or person_id are populated and not both.
361 --
362 if (p_person_id is null
363 and p_party_id is null -- HR/TCA merge
364 and p_attendance_id is null) then
365 -- WWBUG 2658623 comment out the following 2 lines
366 --or ((p_person_id is not null or p_party_id is not null)
367 --and p_attendance_id is not null)) then
368 --
369 hr_utility.set_message(801,'HR_51833_QUA_PER_ATT_ID');
370 hr_multi_message.add
371 (p_associated_column1 => 'PER_QUALIFICATIONS.PERSON_ID'
372 ,p_associated_column2 => 'PER_QUALIFICATIONS.PARTY_ID'
373 ,p_associated_column3 => 'PER_QUALIFICATIONS.ATTENDANCE_ID'
374 );
375 --
376 end if;
377 --
378 -- Check that values exist in the relevant tables.
379 -- person_id must exist in PER_PEOPLE_F as of the effective date.
380 -- attendance_id must exist in PER_ESTABLISHMENT_ATTENDANCES table.
381 --
382 if p_person_id is not null then
383 --
384 open c1;
385 --
386 fetch c1 into l_dummy;
387 if c1%notfound then
388 --
389 close c1;
390 hr_utility.set_message(801,'HR_51834_QUA_PER_ID_INV');
391 hr_multi_message.add
392 (p_associated_column1 => 'PER_QUALIFICATIONS.PERSON_ID'
393 );
394 --
395 else
396 --
397 close c1;
398 --
399 end if;
400 --
401 end if;
402 --
403 if p_attendance_id is not null then
404 --
405 open c2;
406 --
407 fetch c2 into l_dummy;
408 if c2%notfound then
409 --
410 close c2;
411 per_qua_shd.constraint_error('PER_QUALIFICATIONS_FK1');
412 --
413 end if;
414 --
415 close c2;
416 --
417 end if;
418 --
419 end if;
420 --
421 --UPDATE of person id not allowed unless currently null(U)
422 --
423 if (l_api_updating
424 and nvl(per_qua_shd.g_old_rec.person_id,hr_api.g_number) <> hr_api.g_number
425 and per_qua_shd.g_old_rec.person_id <> p_person_id
426 ) then
427 --
428 hr_utility.set_message(800, 'HR_289948_INV_UPD_PERSON_ID');
429 hr_utility.raise_error;
430 --
431 end if;
432 --
436 --
433 hr_utility.set_location('Leaving:'||l_proc,10);
434 --
435 End chk_person_id;
437 --
438 -- ----------------------------------------------------------------------------
439 -- |--------------------------< chk_party_id >--------------------------------|
440 -- ----------------------------------------------------------------------------
441 --
442 --
443 -- Description:
444 -- - Validates that the person_id and the party_id are matched in
445 -- per_all_people_f
446 -- and if person_id is not null and party_id is null, derive party_id
447 -- from per_all_people_f from person_id
448 --
449 -- Pre_conditions:
450 -- A valid business_group_id
451 --
452 -- In Arguments:
453 -- A Pl/Sql record structre.
454 -- effective_date
455
456 --
457 -- Post Success:
458 -- Process continues if :
459 --
460 -- Post Failure:
461 -- An application error is raised and processing is terminated if any of
462
463 -- Access Status:
464 -- Internal Table Handler Use Only.
465 --
466 --
467 Procedure chk_party_id(
468 p_rec in out nocopy per_qua_shd.g_rec_type
469 ,p_effective_date in date
470 )is
471 --
472 l_proc varchar2(72) := g_package||'chk_party_id';
473 l_party_id per_qualifications.party_id%TYPE;
474 l_party_id2 per_qualifications.party_id%TYPE;
475 l_person_id per_establishment_attendances.person_id%TYPE;
476 --
477 --
478 -- cursor to check that the party_id maches person_id
479 --
480 cursor csr_get_party_id is
481 select party_id
482 from per_all_people_f per
483 where per.person_id = p_rec.person_id
484 and p_effective_date
485 between per.effective_start_date
486 and nvl(per.effective_end_date,hr_api.g_eot);
487 --
488 cursor csr_valid_party_id is
489 select party_id
490 from hz_parties hzp
491 where hzp.party_id = p_rec.party_id;
492 --
493 cursor csr_attendances is
494 select party_id
495 ,person_id
496 from per_establishment_attendances pea
497 where pea.attendance_id = p_rec.attendance_id;
498 --
499 begin
500 hr_utility.set_location('Entering:'|| l_proc, 1);
501 --
502 --
503 if p_rec.person_id is not null then
504 if hr_multi_message.no_all_inclusive_error
505 (p_check_column1 => 'PER_QUALIFICATIONS.PERSON_ID'
506 ,p_check_column2 => 'PER_QUALIFICATIONS.PARTY_ID'
507 ) then
508 open csr_get_party_id;
509 fetch csr_get_party_id into l_party_id;
510 close csr_get_party_id;
511 hr_utility.set_location(l_proc,20);
512 if p_rec.party_id is not null then
513 if p_rec.party_id <> nvl(l_party_id,-1) then
514 hr_utility.set_message(800, 'HR_289343_PERSONPARTY_MISMATCH');
515 hr_utility.set_location(l_proc,30);
516 hr_multi_message.add
517 (p_associated_column1 => 'PER_QUALIFICATIONS.PERSON_ID'
518 ,p_associated_column2 => 'PER_QUALIFICATIONS.PARTY_ID'
519 );
520 end if;
521 else
522 --
523 -- derive party_id from per_all_people_f using person_id
524 --
525 hr_utility.set_location(l_proc,50);
526 p_rec.party_id := l_party_id;
527 end if;
528 end if; --end if for no_all_inclusive_error
529 else
530 if p_rec.attendance_id is not null then
531 if hr_multi_message.no_all_inclusive_error
532 (p_check_column1 => 'PER_QUALIFICATIONS.ATTENDANCE_ID'
533 ,p_check_column2 => 'PER_QUALIFICATIONS.PARTY_ID'
534 ) then
535 open csr_attendances;
536 fetch csr_attendances into l_party_id,l_person_id;
537 close csr_attendances;
538 hr_utility.set_location(l_proc,60);
539 if p_rec.party_id is not null then
540 if p_rec.party_id <> l_party_id then
541 hr_utility.set_message(800, 'PER_289342_PARTY_ID_INVALID');
542 hr_utility.set_location(l_proc,70);
543 hr_multi_message.add
544 (p_associated_column1 => 'PER_QUALIFICATIONS.ATTENDANCE_ID'
545 ,p_associated_column2 => 'PER_QUALIFICATIONS.PARTY_ID'
546 );
547 end if;
548 else
549 --
550 -- derive party_id from per_establishment_attendances
551 --
552 hr_utility.set_location(l_proc,80);
553 -- p_rec.person_id := l_person_id; WWBUG#2289195
554 p_rec.party_id := l_party_id;
555 end if;
556 end if;--end if for no_all_inclusive_error
557 else
558 if p_rec.party_id is null then
559 /* chk_person_id ensures that this does not occur*/
560 hr_utility.set_message(800, 'HR_289341_CHK_PERSON_OR_PARTY');
561 hr_utility.set_location(l_proc,90);
562 hr_multi_message.add
563 (p_associated_column1 => 'PER_QUALIFICATIONS.PERSON_ID'
564 ,p_associated_column2 => 'PER_QUALIFICATIONS.PARTY_ID'
565 ,p_associated_column3 => 'PER_QUALIFICATIONS.ATTENDANCE_ID'
566 );
567 else
568 open csr_valid_party_id;
569 fetch csr_valid_party_id into l_party_id2;
570 if csr_valid_party_id%notfound then
571 close csr_valid_party_id;
572 hr_utility.set_message(800, 'PER_289342_PARTY_ID_INVALID');
573 hr_utility.set_location(l_proc,100);
577 else
574 hr_multi_message.add
575 (p_associated_column1 => 'PER_QUALIFICATIONS.PARTY_ID'
576 );
578 close csr_valid_party_id;
579 end if;
580 end if; -- party_id is null
581 end if; -- att_id is not null
582 end if; -- person_id is not null
583 --
584 hr_utility.set_location(' Leaving:'||l_proc,200);
585 End chk_party_id;
586
587 --
588 -- ----------------------------------------------------------------------------
589 -- |---------------------------< chk_status >---------------------------------|
590 -- ----------------------------------------------------------------------------
591 --
592 -- Description
593 -- This procedure checks that the status for a qualification is within the
594 -- lookup PER_SUBJECT_STATUSES.
595 --
596 -- Pre-Conditions
597 -- None.
598 --
599 -- In Parameters
600 -- p_qualification_id PK
601 -- p_status status of qualification
602 -- p_effective_date effective date of session.
603 -- p_object_version_number object version number
604 --
605 -- Post Success
606 -- Processing continues
607 --
608 -- Post Failure
609 -- Error raised.
610 --
611 -- Access Status
612 -- Internal table handler use only.
613 --
614 Procedure chk_status (p_qualification_id in number,
615 p_status in varchar2,
616 p_effective_date in date,
617 p_object_version_number in number) is
618 --
619 l_proc varchar2(72) := g_package||'chk_status';
620 l_api_updating boolean;
621 --
622 Begin
623 --
624 hr_utility.set_location('Entering:'||l_proc,5);
625 --
626 l_api_updating := per_qua_shd.api_updating
627 (p_qualification_id => p_qualification_id,
628 p_object_version_number => p_object_version_number);
629 --
630 if p_status is not null then
631 --
632 if (l_api_updating
633 and nvl(p_status,hr_api.g_varchar2)
634 <> nvl(per_qua_shd.g_old_rec.status,hr_api.g_varchar2)
635 or not l_api_updating) then
636 --
637 -- check if status value exists in PER_SUBJECT_STATUSES lookup.
638 --
639 if hr_api.not_exists_in_hr_lookups
640 (p_effective_date => p_effective_date,
641 p_lookup_type => 'PER_SUBJECT_STATUSES',
642 p_lookup_code => p_status) then
643 --
644 hr_utility.set_message(801,'HR_51835_QUA_STATUS_LKP');
645 hr_utility.raise_error;
646 --
647 end if;
648 --
649 end if;
650 --
651 end if;
652 --
653 hr_utility.set_location('Leaving:'||l_proc,10);
654 --
655 exception
656 when app_exception.application_exception then
657 if hr_multi_message.exception_add
658 (p_associated_column1 => 'PER_QUALIFICATIONS.STATUS'
659 ) then
660 --
661 hr_utility.set_location(' Leaving:'||l_proc, 11);
662 --
663 raise;
664 end if;
665 --
666 hr_utility.set_location(' Leaving:'||l_proc, 12);
667 --
668 End chk_status;
669 --
670 -- ----------------------------------------------------------------------------
671 -- |---------------------------< chk_awarded_date >---------------------------|
672 -- ----------------------------------------------------------------------------
673 --
674 -- Description
675 -- This procedure checks that the awarded date is after the start date and
676 -- later than or equal to the end date.
677 --
678 -- Pre-Conditions
679 -- None.
680 --
681 -- In Parameters
682 -- p_qualification_id PK
683 -- p_awarded_date status of qualification
684 -- p_object_version_number object version number
685 --
686 -- Post Success
687 -- Processing continues
688 --
689 -- Post Failure
690 -- Error raised.
691 --
692 -- Access Status
693 -- Internal table handler use only.
694 --
695 Procedure chk_awarded_date (p_qualification_id in number,
696 p_awarded_date in date,
697 p_start_date in date,
698 p_object_version_number in number) is
699 --
700 --
701 Begin
702 --
703 per_qua_bus.chk_awarded_date
704 (p_qualification_id ,
705 p_awarded_date ,
706 p_start_date ,
707 NULL ,
708 NULL ,
709 p_object_version_number );
710
711 --
712 End chk_awarded_date;
713 --
714 -- ----------------------------------------------------------------------------
715 -- |---------------------------< chk_awarded_date >---------------------------|
716 -- ----------------------------------------------------------------------------
717 --
718 -- This is the overload procedure for chk_awarded_date
719 --
720 -- Description
721 -- This procedure checks that the awarded date is after the start date and
722 -- later than or equal to the end date.
723 --
724 -- Pre-Conditions
725 -- None.
726 --
727 -- In Parameters
728 -- p_qualification_id PK
732 -- Post Success
729 -- p_awarded_date status of qualification
730 -- p_object_version_number object version number
731 --
733 -- Processing continues
734 --
735 -- Post Failure
736 -- Error raised.
737 --
738 -- Access Status
739 -- Internal table handler use only.
740 --
741 Procedure chk_awarded_date (p_qualification_id in number,
742 p_awarded_date in date,
743 p_start_date in date,
744 p_end_date in date,
745 p_projected_completion_date in date,
746 p_object_version_number in number) is
747 --
748 l_proc varchar2(72) := g_package||'chk_awarded_date';
749 l_old_awarded_date varchar2(30) := to_char(per_qua_shd.g_old_rec.awarded_date);
750 l_old_start_date varchar2(30) := to_char(per_qua_shd.g_old_rec.start_date);
751 l_old_proj_comp_date varchar2(30) := to_char(per_qua_shd.g_old_rec.projected_completion_date);
752 l_old_end_date varchar2(30) := to_char(per_qua_shd.g_old_rec.end_date);
753 l_api_updating boolean;
754 --
755 Begin
756 --
757 hr_utility.set_location('********BUG1956358********',99);
758 hr_utility.set_location('Entering:'||l_proc,5);
759 --
760 l_api_updating := per_qua_shd.api_updating
761 (p_qualification_id => p_qualification_id,
762 p_object_version_number => p_object_version_number);
763 --
764 if p_awarded_date is not null then
765
766
767 hr_utility.set_location('Start Date= '||p_start_date,20);
768 hr_utility.set_location('Prev Start Date= '||l_old_start_date,30);
769 hr_utility.set_location('End Date= '||p_end_date,40);
770 hr_utility.set_location('Prev End Date= '||l_old_end_date,50);
771 hr_utility.set_location('Awarded Date= '||p_awarded_date,60);
772 hr_utility.set_location('Prev Awarded Date= '||l_old_awarded_date,70);
773 hr_utility.set_location('Proj Comp Date= '||p_projected_completion_date,80);
774 hr_utility.set_location('Prev Proj Comp Date= '||l_old_proj_comp_date,90);
775 --
776 if (l_api_updating
777 and (nvl(p_awarded_date,hr_api.g_date)
778 <> nvl(per_qua_shd.g_old_rec.awarded_date,hr_api.g_date)
779 or nvl(p_start_date,hr_api.g_date)
780 <> nvl(per_qua_shd.g_old_rec.start_date,hr_api.g_date)
781 or nvl(p_projected_completion_date,hr_api.g_date)
782 <> nvl (per_qua_shd.g_old_rec.projected_completion_date,hr_api.g_date)
783 or nvl(p_end_date,hr_api.g_date)
784 <> nvl (per_qua_shd.g_old_rec.end_date,hr_api.g_date))
785 or not l_api_updating) then
786 --
787 -- check if awarded_date is after the start_date and greater than or
788 -- equal to the projected/actual completion date.
789 --
790
791 hr_utility.set_location('enter check for invalid dates',100);
792 hr_utility.set_location('start of time= '||hr_api.g_sot,110);
793 hr_utility.set_location('end of time= '||hr_api.g_eot,115);
794
795
796 hr_utility.set_location('Start Date= '||p_start_date,20);
797 hr_utility.set_location('Awarded Date= '||p_awarded_date,20);
798
799
800 IF p_start_date is not null then
801 hr_utility.set_location('p_start_date1',40);
802 --hr_utility.set_message(801,'1Start Date Error');
803 if p_awarded_date < p_start_date then
804 hr_utility.set_location('Start Date Error',30);
805 hr_utility.set_message(801,'HR_51836_QUA_AWARD_DATE_INV');
806 hr_utility.set_message(801,'Start Date Error');
807 hr_utility.raise_error;
808 END IF;
809 end if;
810
811 hr_utility.set_location('Project_comp_date= '||p_projected_completion_date,40);
812 hr_utility.set_location('End Date= '||p_end_date,40);
813 hr_utility.set_location('Awarded Date= '||p_awarded_date,40);
814
815 IF p_end_date is not null then
816 hr_utility.set_location('p_end_date1',40);
817 --hr_utility.set_message(801,'1End Date Error');
818 if p_awarded_date < p_end_date then
819 hr_utility.set_location('End/Projected date error',50);
820 hr_utility.set_message(800,'PER_289710_INVALID_AWARD_DATE');
821 hr_utility.raise_error;
822 END IF;
823
824 else
825 if p_projected_completion_date is not null then
826 hr_utility.set_location('p_end_date2',40);
827 --hr_utility.set_message(801,'1Projected Date Error');
828 if p_awarded_date < p_projected_completion_date then
829 hr_utility.set_location('End/Projected date error',50);
830 hr_utility.set_message(800,'PER_289711_INVALID_AWARD_DATE');
831 hr_utility.raise_error;
832 END IF;
833 end if;
834 end if;
835 --
836 end if;
837 --
838 end if;
839 --
840 hr_utility.set_location('Leaving:'||l_proc,10);
841 --
842 exception
843 when app_exception.application_exception then
844 if hr_multi_message.exception_add
845 (p_associated_column1 => 'PER_QUALIFICATIONS.START_DATE'
846 ,p_associated_column2 => 'PER_QUALIFICATIONS.AWARDED_DATE'
847 ) then
848 --
849 hr_utility.set_location(' Leaving:'||l_proc, 11);
850 --
851 raise;
852 end if;
853 --
854 hr_utility.set_location(' Leaving:'||l_proc, 12);
855 --
859 -- |---------------------------< chk_fee >------------------------------------|
856 End chk_awarded_date;
857 --
858 -- ----------------------------------------------------------------------------
860 -- ----------------------------------------------------------------------------
861 --
862 -- Description
863 -- This procedure checks that the fee value is correct. If the fee has been
864 -- entered then the fee currency must lso be entered, likewise if the fee is
865 -- blank then the fee currency must also be blank.
866 --
867 -- Pre-Conditions
868 -- None.
869 --
870 -- In Parameters
871 -- p_qualification_id PK
872 -- p_fee value of fee to take qualification
873 -- p_fee_currency currency of fee
874 -- p_object_version_number object version number
875 --
876 -- Post Success
877 -- Processing continues
878 --
879 -- Post Failure
880 -- Error raised.
881 --
882 -- Access Status
883 -- Internal table handler use only.
884 --
885 Procedure chk_fee (p_qualification_id in number,
886 p_fee in number,
887 p_fee_currency in varchar2,
888 p_object_version_number in number) is
889 --
890 l_proc varchar2(72) := g_package||'chk_fee';
891 l_api_updating boolean;
892 l_dummy varchar2(1);
893 --
894 cursor c1 is
895 select null
896 from fnd_currencies fnd
897 where fnd.currency_code = p_fee_currency;
898 --
899 Begin
900 --
901 hr_utility.set_location('Entering:'||l_proc,5);
902 --
903 l_api_updating := per_qua_shd.api_updating
904 (p_qualification_id => p_qualification_id,
905 p_object_version_number => p_object_version_number);
906 --
907 if (l_api_updating
908 and (nvl(p_fee,hr_api.g_number)
909 <> nvl(per_qua_shd.g_old_rec.fee,hr_api.g_number)
910 or nvl(p_fee_currency,hr_api.g_varchar2)
911 <> nvl(per_qua_shd.g_old_rec.fee_currency,hr_api.g_varchar2))
912 or not l_api_updating) then
913 --
914 -- This if statement forces one of the following conditions
915 -- a) FEE is NOT NULL and FEE CURRENCY is NOT NULL
916 -- b) FEE is NULL and FEE CURRENCY is NULL
917 --
918 if (p_fee_currency is null
919 and p_fee is not null
920 or p_fee_currency is not null
921 and p_fee is null) then
922 --
923 -- raise error as fee or fee currency has been set without the other
924 -- having been set.
925 --
926 hr_utility.set_message(801,'HR_51840_QUA_FEE_CURRENCY');
927 hr_multi_message.add
928 (p_associated_column1 => 'PER_QUALIFICATIONS.FEE'
929 ,p_associated_column2 => 'PER_QUALIFICATIONS.FEE_CURRENCY'
930 );
931 --
932 end if;
933 --
934 -- check fee exists in fnd_currencies table
935 --
936 if p_fee_currency is not null
937 and p_fee is not null then
938 --
939 open c1;
940 --
941 fetch c1 into l_dummy;
942 if c1%notfound then
943 --
944 -- raise error as currency does not exist in table
945 --
946 close c1;
947 hr_utility.set_message(801,'HR_51855_QUA_CCY_INV');
948 --
949 hr_multi_message.add
950 (p_associated_column1 => 'PER_QUALIFICATIONS.FEE_CURRENCY'
951 );
952 else
953 --
954 close c1;
955 --
956 end if;
957 --
958 end if; --fee_curr is not null
959 --
960 end if;
961 --
962 hr_utility.set_location('Leaving:'||l_proc,10);
963 --
964 End chk_fee;
965 --
966 -- ----------------------------------------------------------------------------
967 -- |---------------------------< chk_start_date >-----------------------------|
968 -- ----------------------------------------------------------------------------
969 --
970 -- Description
971 -- This procedure checks that the start date and end date are valid values.
972 -- The end_date must be after the start_date. The start and end dates must
973 -- bound all subjects taken and be within the dates of the establishment
974 -- attendance.
975 --
976 -- Bug: 1664055 Starts here.
977 --
978 -- This procedure also checks that the start date is greater than the Date of
979 -- Birth of the person if date of birth is not null. The start date can be
980 -- provided only if date of birth is not null.
981 --
982 -- Bug: 1664055 Ends here
983 --
984 -- Pre-Conditions
985 -- None.
986 --
987 -- In Parameters
988 -- p_qualification_id PK
989 -- p_attendance_id id of establishment attendance
990 -- p_start_date start date of qualification
991 -- p_end_date end date of qualification
992 -- p_object_version_number object version number
993 -- p_effective_date Effective date
994 -- p_person_id id of the person
995 --
996 -- Post Success
997 -- Processing continues
998 --
999 -- Post Failure
1000 -- Error raised.
1001 --
1002 -- Access Status
1006 p_attendance_id in number,
1003 -- Internal table handler use only.
1004 --
1005 Procedure chk_start_date (p_qualification_id in number,
1007 p_start_date in date,
1008 p_end_date in date,
1009 p_object_version_number in number,
1010 -- Bug: 1664055 Starts here.
1011 p_effective_date in date,
1012 p_person_id in number)
1013 -- Bug: 1664055 Ends here.
1014 is
1015 --
1016 l_proc varchar2(72) := g_package||'chk_start_date';
1017 l_api_updating boolean;
1018 l_dummy varchar2(1);
1019 l_dob date;
1020 --
1021 -- This cursor checks that the dates of the qualification fall
1022 -- within the related establishment attendance
1023 --
1024 cursor c1 is
1025 select null
1026 from per_establishment_attendances per
1027 where per.attendance_id = p_attendance_id
1028 and nvl(p_start_date,nvl(per.attended_start_date,hr_api.g_sot))
1029 between nvl(per.attended_start_date,hr_api.g_sot)
1030 and nvl(per.attended_end_date,hr_api.g_eot)
1031 and nvl(p_end_date,nvl(per.attended_end_date,hr_api.g_eot))
1032 between nvl(per.attended_start_date,hr_api.g_sot)
1033 and nvl(per.attended_end_date,hr_api.g_eot);
1034 --
1035 -- This cursor is used to check that the subjects taken are within the
1036 -- dates of the qualification.
1037 --
1038 cursor c2 is
1039 select null
1040 from per_subjects_taken per
1041 where per.qualification_id = p_qualification_id
1042 and per.start_date
1043 not between nvl(p_start_date,hr_api.g_sot)
1044 and nvl(p_end_date,hr_api.g_eot);
1045 --
1046 -- Bug: 1664055 Starts here.
1047 --
1048 cursor c3 is
1049 select DATE_OF_BIRTH
1050 from per_all_people_f per
1051 where per.person_id = p_person_id
1052 and p_effective_date
1053 between per.effective_start_date
1054 and nvl(per.effective_end_date,hr_api.g_eot)
1055 and date_of_birth is not null;
1056 --
1057 -- Bug: 1664055 Ends here.
1058 --
1059 Begin
1060 --
1061 hr_utility.set_location('Entering:'||l_proc,5);
1062 --
1063 l_api_updating := per_qua_shd.api_updating
1064 (p_qualification_id => p_qualification_id,
1065 p_object_version_number => p_object_version_number);
1066 --
1067 if (l_api_updating
1068 or not l_api_updating) then
1069 --
1070 -- Bug: 1664055 Starts here.
1071 --
1072 if (p_start_date is not null) then
1073 open c3;
1074 fetch c3 into l_dob;
1075 if (c3%found) then
1076 if (l_dob is null) then
1077 close c3;
1078 hr_utility.set_message(800,'HR_289739_QUA_NULL_DOB');
1079 hr_utility.raise_error;
1080 elsif (l_dob > p_start_date) then
1081 close c3;
1082 hr_utility.set_message(800,'HR_289383_QUA_START_DATE');
1083 hr_utility.raise_error;
1084 end if;
1085 end if;
1086 close c3;
1087 end if;
1088 --
1089 -- Bug: 1664055 Ends here.
1090 --
1091 -- Check that the end date of the qualification is later than the start
1092 -- date for the qualification.
1093 --
1094 if nvl(p_start_date,hr_api.g_sot) >
1095 nvl(p_end_date,hr_api.g_eot) then
1096 --
1097 -- raise error as the qualification start date is after the qualification
1098 -- end date.
1099 --
1100 per_qua_shd.constraint_error('PER_QUA_CHK_DATES');
1101 --
1102 end if;
1103 --
1104 -- Only carry out checks if the start and end date for the qualification
1105 -- are not null and we have an attendance id.
1106 --
1107 if ((p_start_date is not null
1108 or p_end_date is not null)
1109 and p_attendance_id is not null) then
1110 if hr_multi_message.no_all_inclusive_error
1111 (p_check_column1 => 'PER_QUALIFICATIONS.ATTENDANCE_ID'
1112 ) then
1113 --
1114 -- Only carry out test on establishment attendance dates if attendance_id
1115 -- is not null.
1116 --
1117 open c1;
1118 --
1119 fetch c1 into l_dummy;
1120 if c1%notfound then
1121 --
1122 -- raise error as qualification start and end dates are outside of
1123 -- the dates of the establishment attendance.
1124 --
1125 close c1;
1126 hr_utility.set_message(801,'HR_51841_QUA_DATES_OUT_ESA');
1127 hr_multi_message.add
1128 (p_associated_column1 => 'PER_QUALIFICATIONS.ATTENDANCE_ID'
1129 ,p_associated_column2 => 'PER_QUALIFICATIONS.START_DATE'
1130 ,p_associated_column3 => 'PER_QUALIFICATIONS.END_DATE'
1131 );
1132 --
1133 else
1134 --
1135 close c1;
1136 --
1137 end if; -- c1 not found
1138 --
1139 end if; -- hr_multi_message.no_all_inc_error
1140 --
1141 end if; -- start_date/end_date is not null and att_id is not null
1142 --
1143 -- WWBUG 2502284 drove this.
1144 --
1148 -- start date through SSHR or FUI.
1145 -- Bug fix 3239115. Same as 2502284.
1146 -- Validation of qualification start date with subject start date is
1147 -- commented to avoid the error occured while updating the qualification
1149
1150 /* -- check if there are any subjects taken records that are not bound by the
1151 -- qualification start and end dates.
1152 --
1153 open c2;
1154 --
1155 if p_start_date is not null then
1156 fetch c2 into l_dummy;
1157 if c2%found then
1158 --
1159 -- raise error as we have found a linked subjects taken record that is
1160 -- not bounded by the qualification record.
1161 --
1162 close c2;
1163 hr_utility.set_message(801,'HR_51842_QUA_SUB_DATES');
1164 hr_multi_message.add
1165 (p_associated_column1 => 'PER_QUALIFICATIONS.QUALIFICATION_ID'
1166 ,p_associated_column2 => 'PER_QUALIFICATIONS.START_DATE'
1167 ,p_associated_column3 => 'PER_QUALIFICATIONS.END_DATE'
1168 );
1169 --
1170 else
1171 --
1172 close c2;
1173 --
1174 end if;
1175 --
1176 end if;*/
1177 end if; -- l_api_updating
1178 --
1179 hr_utility.set_location('Leaving:'||l_proc,10);
1180 --
1181 End chk_start_date;
1182 --
1183 -- ----------------------------------------------------------------------------
1184 -- |---------------------------< chk_end_date >-------------------------------|
1185 -- ----------------------------------------------------------------------------
1186 --
1187 -- Description
1188 -- This procedure checks that the end date end date is a valid values.
1189 -- It checks to see if the end date of the qualification is valid against
1190 -- the subject end date.
1191 --
1192 -- This procedure was create to resolve bug 1854046
1193 --
1194 -- Pre-Conditions
1195 -- None.
1196 --
1197 -- In Parameters
1198 -- p_qualification_id PK
1199 -- p_start_date start date of qualification
1200 -- p_end_date end date of qualification
1201 -- p_object_version_number object version number
1202 --
1203 -- Post Success
1204 -- Processing continues
1205 --
1206 -- Post Failure
1207 -- Error raised.
1208 --
1209 -- Access Status
1210 -- Internal table handler use only.
1211 --
1212 Procedure chk_end_date (p_qualification_id in number,
1213 p_start_date in date,
1214 p_end_date in date,
1215 p_object_version_number in number)
1216 is
1217 --
1218 l_proc varchar2(72) := g_package||'chk_end_date';
1219 l_api_updating boolean;
1220 l_dummy varchar2(1);
1221 l_subject_start_date date;
1222 --
1223 -- This cursor checks the end date of the qualification against the end date
1224 -- of the subject end date, and ensures that it is valid.
1225 --
1226
1227 cursor c1 is
1228 select null
1229 from per_subjects_taken per
1230 where per.qualification_id = p_qualification_id
1231 and nvl(per.end_date,per.start_date) > nvl(p_end_date,hr_api.g_eot);
1232
1233 Begin
1234 --
1235 hr_utility.set_location('Entering:'||l_proc,5);
1236 --
1237 --
1238 open c1;
1239 --
1240 fetch c1 into l_dummy;
1241 if c1%found then
1242 --
1243 -- raise error as well have found a linked subjects taken record that is
1244 -- not bounded by the qualification record.
1245 --
1246 close c1;
1247
1248 hr_utility.set_message(801,'HR_51842_QUA_SUB_DATES');
1249 hr_multi_message.add
1250 (p_associated_column1 => 'PER_QUALIFICATIONS.QUALIFICATION_ID'
1251 ,p_associated_column2 => 'PER_QUALIFICATIONS.START_DATE'
1252 ,p_associated_column3 => 'PER_QUALIFICATIONS.END_DATE'
1253 );
1254 --
1255 else
1256 --
1257 close c1;
1258 --
1259 end if;
1260 --
1261 hr_utility.set_location('Leaving:'||l_proc,10);
1262 --
1263 End chk_end_date;
1264 --
1265 -- ----------------------------------------------------------------------------
1266 -- |-----------------------< chk_projected_completion_date--------------------|
1267 -- ----------------------------------------------------------------------------
1268 --
1269 -- Description
1270 -- This procedure checks that the projected completion date is after the
1271 -- start date of the qualification.
1272 --
1273 -- Pre-Conditions
1274 -- None.
1275 --
1276 -- In Parameters
1277 -- p_qualification_id PK
1278 -- p_start_date start date of qualification
1279 -- p_projected_completion_date projected completion date.
1280 -- p_object_version_number object version number
1281 --
1282 -- Post Success
1283 -- Processing continues
1284 --
1285 -- Post Failure
1286 -- Error raised.
1287 --
1288 -- Access Status
1289 -- Internal table handler use only.
1290 --
1291 Procedure chk_projected_completion_date
1292 (p_qualification_id in number,
1293 p_start_date in date,
1294 p_projected_completion_date in date,
1295 p_object_version_number in number) is
1299 --
1296 --
1297 l_proc varchar2(72) := g_package||'chk_projected_completion_date';
1298 l_api_updating boolean;
1300 Begin
1301 --
1302 hr_utility.set_location('Entering:'||l_proc,5);
1303 --
1304 if hr_multi_message.no_all_inclusive_error
1305 (p_check_column1 => 'PER_QUALIFICATIONS.START_DATE'
1306 ) then
1307 l_api_updating := per_qua_shd.api_updating
1308 (p_qualification_id => p_qualification_id,
1309 p_object_version_number => p_object_version_number);
1310 --
1311 if (l_api_updating
1312 and (nvl(p_start_date,hr_api.g_date)
1313 <> nvl(per_qua_shd.g_old_rec.start_date,hr_api.g_date)
1314 or nvl(p_projected_completion_date,hr_api.g_date)
1315 <> nvl(per_qua_shd.g_old_rec.projected_completion_date,hr_api.g_date))
1316 or not l_api_updating) then
1317 --
1318 -- Check that if the projected completion date has been entered that it is
1319 -- later than the start date.
1320 --
1321 if p_projected_completion_date is not null
1322 and (p_start_date is null
1323 or p_projected_completion_date < p_start_date) then
1324 --
1325 -- raise error as projected completion date is not after the
1326 -- start date.
1327 --
1328 hr_utility.set_message(801,'HR_51844_QUA_PROJ_DATE');
1329 hr_utility.raise_error;
1330 --
1331 end if;
1332 --
1333 end if;
1334 --
1335 end if; -- for no_all_inclusive_error
1336 --
1337 hr_utility.set_location('Leaving:'||l_proc,10);
1338 --
1339 exception
1340 when app_exception.application_exception then
1341 if hr_multi_message.exception_add
1342 (p_associated_column1 => 'PER_QUALIFICATIONS.START_DATE'
1343 ,p_associated_column2 => 'PER_QUALIFICATIONS.PROJECTED_COMPLETION_DATE'
1344 ) then
1345 --
1346 hr_utility.set_location(' Leaving:'||l_proc, 11);
1347 --
1348 raise;
1349 end if;
1350 --
1351 hr_utility.set_location(' Leaving:'||l_proc, 12);
1352 --
1353 End chk_projected_completion_date;
1354 --
1355 -- ----------------------------------------------------------------------------
1356 -- |-----------------------< chk_tuition_method >-----------------------------|
1357 -- ----------------------------------------------------------------------------
1358 --
1359 -- Description
1360 -- This procedure checks that the tuition method is within the lookup
1361 -- PER_TUITION_METHODS.
1362 --
1363 -- Pre-Conditions
1364 -- None.
1365 --
1366 -- In Parameters
1367 -- p_qualification_id PK
1368 -- p_tuition_method Tuition method used.
1369 -- p_effective_date date of session
1370 -- p_object_version_number object version number
1371 --
1372 -- Post Success
1373 -- Processing continues
1374 --
1375 -- Post Failure
1376 -- Error raised.
1377 --
1378 -- Access Status
1379 -- Internal table handler use only.
1380 --
1381 Procedure chk_tuition_method (p_qualification_id in number,
1382 p_tuition_method in varchar2,
1383 p_effective_date in date,
1384 p_object_version_number in number) is
1385 --
1386 l_proc varchar2(72) := g_package||'chk_tuition_method';
1387 l_api_updating boolean;
1388 --
1389 Begin
1390 --
1391 hr_utility.set_location('Entering:'||l_proc,5);
1392 --
1393 l_api_updating := per_qua_shd.api_updating
1394 (p_qualification_id => p_qualification_id,
1395 p_object_version_number => p_object_version_number);
1396 --
1397 if (l_api_updating
1398 and (nvl(p_tuition_method,hr_api.g_varchar2)
1399 <> nvl(per_qua_shd.g_old_rec.tuition_method,hr_api.g_varchar2))
1400 or not l_api_updating) then
1401 --
1402 if p_tuition_method is not null then
1403 --
1404 -- Check if tuition method exists in lookup PER_TUITION_METHODS
1405 --
1406 if hr_api.not_exists_in_hr_lookups
1407 (p_effective_date => p_effective_date,
1408 p_lookup_type => 'PER_TUITION_METHODS',
1409 p_lookup_code => p_tuition_method) then
1410 --
1411 hr_utility.set_message(801,'HR_51845_QUA_TUITION_MTHD');
1412 hr_utility.raise_error;
1413 --
1414 end if;
1415 --
1416 end if;
1417 --
1418 end if;
1419 --
1420 hr_utility.set_location('Leaving:'||l_proc,10);
1421 --
1422 exception
1423 when app_exception.application_exception then
1424 if hr_multi_message.exception_add
1425 (p_associated_column1 => 'PER_QUALIFICATIONS.TUITION_METHOD'
1426 ) then
1427 --
1428 hr_utility.set_location(' Leaving:'||l_proc, 11);
1429 --
1430 raise;
1431 end if;
1432 --
1433 hr_utility.set_location(' Leaving:'||l_proc, 12);
1434 --
1435 End chk_tuition_method;
1436 --
1437 -- ----------------------------------------------------------------------------
1438 -- |-----------------------< chk_estab_att_bg >-------------------------------|
1442 -- This procedure checks that the establishment attendance business group
1439 -- ----------------------------------------------------------------------------
1440 --
1441 -- Description
1443 -- is the same as the business group for the qualification.
1444 --
1445 -- Pre-Conditions
1446 -- None.
1447 --
1448 -- In Parameters
1449 -- p_qualification_id PK
1450 -- p_attendance_id id of related establishment attendance
1451 -- p_business_group_id id of business group
1452 -- p_object_version_number object version number
1453 --
1454 -- Post Success
1455 -- Processing continues
1456 --
1457 -- Post Failure
1458 -- Error raised.
1459 --
1460 -- Access Status
1461 -- Internal table handler use only.
1462 --
1463 Procedure chk_estab_att_bg (p_qualification_id in number,
1464 p_attendance_id in number,
1465 p_business_group_id in number,
1466 p_object_version_number in number) is
1467 --
1468 l_proc varchar2(72) := g_package||'chk_estab_att_bg';
1469 l_api_updating boolean;
1470 l_dummy varchar2(1);
1471 --
1472 cursor c1 is
1473 select null
1474 from per_establishment_attendances per
1475 where per.attendance_id = p_attendance_id
1476 and nvl(per.business_group_id,-1) = nvl(p_business_group_id,
1477 nvl(per.business_group_id,-1));
1478 --
1479 Begin
1480 --
1481 hr_utility.set_location('Entering:'||l_proc,5);
1482 --
1483 if hr_multi_message.no_all_inclusive_error
1484 (p_check_column1 => 'PER_QUALIFICATIONS.ATTENDANCE_ID'
1485 ) then
1486 --
1487 l_api_updating := per_qua_shd.api_updating
1488 (p_qualification_id => p_qualification_id,
1489 p_object_version_number => p_object_version_number);
1490 --
1491 if (l_api_updating
1492 and (nvl(p_attendance_id,hr_api.g_number)
1493 <> per_qua_shd.g_old_rec.attendance_id
1494 or nvl(p_business_group_id,hr_api.g_number)
1495 <> per_qua_shd.g_old_rec.business_group_id)
1496 or not l_api_updating) then
1497 --
1498 if p_attendance_id is not null then
1499 --
1500 -- check if BG for establishment attendance is the same as BG for
1501 -- qualification record.
1502 --
1503 open c1;
1504 --
1505 fetch c1 into l_dummy;
1506 if c1%notfound then
1507 --
1508 -- raise error as BG is different for establishment attendance and
1509 -- qualification record.
1510 --
1511 close c1;
1512 hr_utility.set_message(801,'HR_51848_QUA_ESTAB_ATT_BG');
1513 hr_utility.raise_error;
1514 --
1515 end if;
1516 --
1517 close c1;
1518 --
1519 end if; -- p_attendance_id is not null
1520 --
1521 end if; -- l_api_updating
1522 --
1523 end if; -- no_all_inclusive_error
1524 --
1525 hr_utility.set_location('Leaving:'||l_proc,10);
1526 --
1527 exception
1528 when app_exception.application_exception then
1529 if hr_multi_message.exception_add
1530 (p_associated_column1 => 'PER_QUALIFICATIONS.ATTENDANCE_ID'
1531 ) then
1532 --
1533 hr_utility.set_location(' Leaving:'||l_proc, 11);
1534 --
1535 raise;
1536 end if;
1537 --
1538 hr_utility.set_location(' Leaving:'||l_proc, 12);
1539 --
1540 End chk_estab_att_bg;
1541 --
1542 -- ----------------------------------------------------------------------------
1543 -- |--------------------------< chk_person_bg >-------------------------------|
1544 -- ----------------------------------------------------------------------------
1545 --
1546 -- Description
1547 -- This procedure checks that the person being referenced is in the same
1548 -- business group as the qualification and that the person exists as of
1549 -- the effective date.
1550 --
1551 -- Pre-Conditions
1552 -- None.
1553 --
1554 -- In Parameters
1555 -- p_effective_date effective date
1556 -- p_qualification_id PK
1557 -- p_person_id id of related establishment attendance
1558 -- p_business_group_id id of business group
1559 -- p_object_version_number object version number
1560 --
1561 -- Post Success
1562 -- Processing continues
1563 --
1564 -- Post Failure
1565 -- Error raised.
1566 --
1567 -- Access Status
1568 -- Internal table handler use only.
1569 --
1570 Procedure chk_person_bg (p_effective_date in date,
1571 p_qualification_id in number,
1572 p_person_id in number,
1573 p_business_group_id in number,
1574 p_object_version_number in number) is
1575 --
1576 l_proc varchar2(72) := g_package||'chk_person_bg';
1577 l_api_updating boolean;
1578 l_dummy varchar2(1);
1579 --
1580 cursor c1 is
1581 select null
1582 from per_all_people_f per -- Bug 3148893. Replaced per_all_people_f with per_people_f
1583 where per.person_id = p_person_id
1584 and per.business_group_id +0 = nvl(p_business_group_id,
1588 and nvl(per.effective_end_date,hr_api.g_eot);
1585 per.business_group_id)
1586 and p_effective_date
1587 between per.effective_start_date
1589 --
1590 Begin
1591 --
1592 hr_utility.set_location('Entering:'||l_proc,5);
1593 --
1594 if hr_multi_message.no_all_inclusive_error
1595 (p_check_column1 => 'PER_QUALIFICATIONS.PERSON_ID'
1596 ) then
1597 --
1598 l_api_updating := per_qua_shd.api_updating
1599 (p_qualification_id => p_qualification_id,
1600 p_object_version_number => p_object_version_number);
1601 --
1602 if (l_api_updating
1603 and (nvl(p_person_id,hr_api.g_number)
1604 <> per_qua_shd.g_old_rec.person_id
1605 or nvl(p_business_group_id,hr_api.g_number)
1606 <> per_qua_shd.g_old_rec.business_group_id)
1607 or not l_api_updating) then
1608 --
1609 if p_person_id is not null then
1610 --
1611 -- check if BG for person is the same as BG for qualification record.
1612 --
1613 open c1;
1614 --
1615 fetch c1 into l_dummy;
1616 if c1%notfound then
1617 --
1618 -- raise error as BG is different for person and qualification record.
1619 --
1620 close c1;
1621 hr_utility.set_message(801,'HR_51849_QUA_PERSON_BG');
1622 hr_utility.raise_error;
1623 --
1624 end if;
1625 --
1626 close c1;
1627 --
1628 end if; --p_person_id is not null
1629 --
1630 end if; -- l_api_updating
1631 --
1632 end if; -- no_all_inclusive_error
1633 --
1634 hr_utility.set_location(l_proc,9);
1635 --
1636 --UPDATE of BG_ID not allowed unless currently null(U)
1637 --
1638 if (l_api_updating
1639 and nvl(per_qua_shd.g_old_rec.business_group_id,hr_api.g_number) <> hr_api.g_number
1640 and per_qua_shd.g_old_rec.business_group_id <> p_business_group_id ) then
1641 --
1642 hr_utility.set_message(800, 'HR_289947_INV_UPD_BG_ID');
1643 hr_utility.raise_error;
1644 --
1645 end if;
1646 --
1647 hr_utility.set_location('Leaving:'||l_proc,10);
1648 --
1649 exception
1650 when app_exception.application_exception then
1651 if hr_multi_message.exception_add
1652 (p_associated_column1 => 'PER_QUALIFICATIONS.PERSON_ID'
1653 ) then
1654 --
1655 hr_utility.set_location(' Leaving:'||l_proc, 11);
1656 --
1657 raise;
1658 end if;
1659 --
1660 hr_utility.set_location(' Leaving:'||l_proc, 12);
1661 --
1662 End chk_person_bg;
1663 --
1664 -- ----------------------------------------------------------------------------
1665 -- |--------------------------< chk_qualification_delete >--------------------|
1666 -- ----------------------------------------------------------------------------
1667 --
1668 -- Description
1669 -- This procedure checks whether a qualification record can be deleted. If
1670 -- a SUBJECTS_TAKEN record is referencing this record then it can not be
1671 -- deleted.
1672 --
1673 -- Pre-Conditions
1674 -- None.
1675 --
1676 -- In Parameters
1677 -- p_qualification_id PK
1678 --
1679 -- Post Success
1680 -- Processing continues
1681 --
1682 -- Post Failure
1683 -- Error raised.
1684 --
1685 -- Access Status
1686 -- Internal table handler use only.
1687 --
1688 Procedure chk_qualification_delete (p_qualification_id in number) is
1689 --
1690 l_proc varchar2(72) := g_package||'chk_qualification_delete';
1691 l_api_updating boolean;
1692 l_dummy varchar2(1);
1693 --
1694 cursor c1 is
1695 select null
1696 from per_subjects_taken per
1697 where per.qualification_id = p_qualification_id;
1698 --
1699 Begin
1700 --
1701 hr_utility.set_location('Entering:'||l_proc,5);
1702 --
1703 if hr_multi_message.no_all_inclusive_error
1704 (p_check_column1 => 'PER_QUALIFICATIONS.QUALIFICATION_ID'
1705 ) then
1706 --
1707 -- check if referenced records exist in the PER_SUBJECTS_TAKEN table.
1708 --
1709 open c1;
1710 --
1711 fetch c1 into l_dummy;
1712 if c1%found then
1713 --
1714 -- raise error as child records exist.
1715 --
1716 close c1;
1717 hr_utility.set_message(801,'HR_51857_QUA_REC_DEL');
1718 hr_utility.raise_error;
1719 --
1720 end if;
1721 --
1722 close c1;
1723 --
1724 end if;
1725 --
1726 hr_utility.set_location('Leaving:'||l_proc,10);
1727 --
1728 exception
1729 when app_exception.application_exception then
1730 if hr_multi_message.exception_add
1731 (p_associated_column1 => 'PER_QUALIFICATIONS.QUALIFICATION_ID'
1732 ) then
1733 --
1734 hr_utility.set_location(' Leaving:'||l_proc, 11);
1735 --
1736 raise;
1737 end if;
1738 --
1739 hr_utility.set_location(' Leaving:'||l_proc, 12);
1740 --
1741 End chk_qualification_delete;
1742 --
1746 --
1743 -- ----------------------------------------------------------------------------
1744 -- |-----------------------< chk_qual_overlap >-------------------------------|
1745 -- ----------------------------------------------------------------------------
1747 -- Description
1748 -- This procedure checks that the qualification does not overlap for the
1749 -- same person. The qualification is distinguished by business_group_id,
1750 -- person_id, attendance_id, qualification_id and start date. The start date
1751 -- must not overlap an identical qualification for the same person.
1752 --
1753 -- Pre-Conditions
1754 -- None.
1755 --
1756 -- In Parameters
1757 -- p_qualification_id PK
1758 -- p_qualification_type_id id of related qualification type
1759 -- p_person_id id of person
1760 -- p_attendance_id id of related establishment attendance
1761 -- p_business_group_id id of business group
1762 -- p_start_date start date of qualification
1763 -- p_end_date end date of qualification
1764 -- p_title title of course taken
1765 -- p_object_version_number object version number
1766 -- p_party_id id of party -- HR/TCA merge
1767 --
1768 -- Post Success
1769 -- Processing continues
1770 --
1771 -- Post Failure
1772 -- Error raised.
1773 --
1774 -- Access Status
1775 -- Internal table handler use only.
1776 --
1777 Procedure chk_qual_overlap (p_qualification_id in number,
1778 p_qualification_type_id in number,
1779 p_person_id in number,
1780 p_attendance_id in number,
1781 p_business_group_id in number,
1782 p_start_date in date,
1783 p_end_date in date,
1784 p_title in varchar2,
1785 p_object_version_number in number,
1786 p_party_id in number default null
1787 ) is
1788 --
1789 l_proc varchar2(72) := g_package||'chk_qual_overlap';
1790 --
1791 begin
1792 hr_utility.set_location('Entering:'||l_proc, 10);
1793 --
1794 per_qat_bus.chk_qual_overlap
1795 (p_qualification_id => p_qualification_id
1796 ,p_qualification_type_id => p_qualification_type_id
1797 ,p_person_id => p_person_id
1798 ,p_attendance_id => p_attendance_id
1799 ,p_business_group_id => p_business_group_id
1800 ,p_start_date => p_start_date
1801 ,p_end_date => p_end_date
1802 ,p_title => p_title
1803 ,p_object_version_number => p_object_version_number
1804 ,p_party_id => p_party_id
1805 ,p_language => userenv('LANG'));
1806 --
1807 hr_utility.set_location(' Leaving:'||l_proc, 20);
1808 end chk_qual_overlap;
1809 --
1810 -- -----------------------------------------------------------------------
1811 -- |------------------------------< chk_df >-----------------------------|
1812 -- -----------------------------------------------------------------------
1813 --
1814 -- Description:
1815 -- Validates the all Descriptive Flexfield values.
1816 --
1817 -- Pre-conditions:
1818 -- All other columns have been validated. Must be called as the
1819 -- last step from insert_validate and update_validate.
1820 --
1821 -- In Arguments:
1822 -- p_rec
1823 --
1824 -- Post Success:
1825 -- If the Descriptive Flexfield structure column and data values are
1826 -- all valid this procedure will end normally and processing will
1827 -- continue.
1828 --
1829 -- Post Failure:
1830 -- If the Descriptive Flexfield structure column value or any of
1831 -- the data values are invalid then an application error is raised as
1832 -- a PL/SQL exception.
1833 --
1834 -- Access Status:
1835 -- Internal Row Handler Use Only.
1836 --
1837 -- {End Of Comments}
1838 -- ----------------------------------------------------------------------------
1839 --
1840 procedure chk_df
1841 (p_rec in per_qua_shd.g_rec_type) is
1842 --
1843 l_proc varchar2(72) := g_package||'chk_df';
1844 --
1845 begin
1846 hr_utility.set_location('Entering:'||l_proc, 10);
1847 --
1848 if ((p_rec.qualification_id is not null) and (
1849 nvl(per_qua_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
1850 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
1851 nvl(per_qua_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
1852 nvl(p_rec.attribute1, hr_api.g_varchar2) or
1853 nvl(per_qua_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
1854 nvl(p_rec.attribute2, hr_api.g_varchar2) or
1855 nvl(per_qua_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
1856 nvl(p_rec.attribute3, hr_api.g_varchar2) or
1857 nvl(per_qua_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
1858 nvl(p_rec.attribute4, hr_api.g_varchar2) or
1859 nvl(per_qua_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
1860 nvl(p_rec.attribute5, hr_api.g_varchar2) or
1861 nvl(per_qua_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
1862 nvl(p_rec.attribute6, hr_api.g_varchar2) or
1863 nvl(per_qua_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
1864 nvl(p_rec.attribute7, hr_api.g_varchar2) or
1868 nvl(p_rec.attribute9, hr_api.g_varchar2) or
1865 nvl(per_qua_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
1866 nvl(p_rec.attribute8, hr_api.g_varchar2) or
1867 nvl(per_qua_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
1869 nvl(per_qua_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
1870 nvl(p_rec.attribute10, hr_api.g_varchar2) or
1871 nvl(per_qua_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
1872 nvl(p_rec.attribute11, hr_api.g_varchar2) or
1873 nvl(per_qua_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
1874 nvl(p_rec.attribute12, hr_api.g_varchar2) or
1875 nvl(per_qua_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
1876 nvl(p_rec.attribute13, hr_api.g_varchar2) or
1877 nvl(per_qua_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
1878 nvl(p_rec.attribute14, hr_api.g_varchar2) or
1879 nvl(per_qua_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
1880 nvl(p_rec.attribute15, hr_api.g_varchar2) or
1881 nvl(per_qua_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
1882 nvl(p_rec.attribute16, hr_api.g_varchar2) or
1883 nvl(per_qua_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
1884 nvl(p_rec.attribute17, hr_api.g_varchar2) or
1885 nvl(per_qua_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
1886 nvl(p_rec.attribute18, hr_api.g_varchar2) or
1887 nvl(per_qua_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
1888 nvl(p_rec.attribute19, hr_api.g_varchar2) or
1889 nvl(per_qua_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
1890 nvl(p_rec.attribute20, hr_api.g_varchar2)))
1891 or
1892 (p_rec.qualification_id is null) then
1893 --
1894 -- Only execute the validation if absolutely necessary:
1895 -- a) During update, the structure column value or any
1896 -- of the attribute values have actually changed.
1897 -- b) During insert.
1898 --
1899 hr_dflex_utility.ins_or_upd_descflex_attribs
1900 (p_appl_short_name => 'PER'
1901 ,p_descflex_name => 'PER_QUALIFICATIONS'
1902 ,p_attribute_category => p_rec.attribute_category
1903 ,p_attribute1_name => 'ATTRIBUTE1'
1904 ,p_attribute1_value => p_rec.attribute1
1905 ,p_attribute2_name => 'ATTRIBUTE2'
1906 ,p_attribute2_value => p_rec.attribute2
1907 ,p_attribute3_name => 'ATTRIBUTE3'
1908 ,p_attribute3_value => p_rec.attribute3
1909 ,p_attribute4_name => 'ATTRIBUTE4'
1910 ,p_attribute4_value => p_rec.attribute4
1911 ,p_attribute5_name => 'ATTRIBUTE5'
1912 ,p_attribute5_value => p_rec.attribute5
1913 ,p_attribute6_name => 'ATTRIBUTE6'
1914 ,p_attribute6_value => p_rec.attribute6
1915 ,p_attribute7_name => 'ATTRIBUTE7'
1916 ,p_attribute7_value => p_rec.attribute7
1917 ,p_attribute8_name => 'ATTRIBUTE8'
1918 ,p_attribute8_value => p_rec.attribute8
1919 ,p_attribute9_name => 'ATTRIBUTE9'
1920 ,p_attribute9_value => p_rec.attribute9
1921 ,p_attribute10_name => 'ATTRIBUTE10'
1922 ,p_attribute10_value => p_rec.attribute10
1923 ,p_attribute11_name => 'ATTRIBUTE11'
1924 ,p_attribute11_value => p_rec.attribute11
1925 ,p_attribute12_name => 'ATTRIBUTE12'
1926 ,p_attribute12_value => p_rec.attribute12
1927 ,p_attribute13_name => 'ATTRIBUTE13'
1928 ,p_attribute13_value => p_rec.attribute13
1929 ,p_attribute14_name => 'ATTRIBUTE14'
1930 ,p_attribute14_value => p_rec.attribute14
1931 ,p_attribute15_name => 'ATTRIBUTE15'
1932 ,p_attribute15_value => p_rec.attribute15
1933 ,p_attribute16_name => 'ATTRIBUTE16'
1934 ,p_attribute16_value => p_rec.attribute16
1935 ,p_attribute17_name => 'ATTRIBUTE17'
1936 ,p_attribute17_value => p_rec.attribute17
1937 ,p_attribute18_name => 'ATTRIBUTE18'
1938 ,p_attribute18_value => p_rec.attribute18
1939 ,p_attribute19_name => 'ATTRIBUTE19'
1940 ,p_attribute19_value => p_rec.attribute19
1941 ,p_attribute20_name => 'ATTRIBUTE20'
1942 ,p_attribute20_value => p_rec.attribute20);
1943 end if;
1944 --
1945 hr_utility.set_location(' Leaving:'||l_proc, 20);
1946 end chk_df;
1947 -- ----------------------------------------------------------------------------
1948 -- |-----------------------------< chk_ddf >----------------------------------|
1949 -- ----------------------------------------------------------------------------
1950 --
1951 -- Description:
1952 -- Validates all the Developer Descriptive Flexfield values.
1953 --
1954 -- Prerequisites:
1955 -- All other columns have been validated. Must be called as the
1956 -- last step from insert_validate and update_validate.
1957 --
1958 -- In Arguments:
1959 -- p_rec
1960 --
1961 -- Post Success:
1962 -- If the Developer Descriptive Flexfield structure column and data values
1963 -- are all valid this procedure will end normally and processing will
1964 -- continue.
1965 --
1966 -- Post Failure:
1967 -- If the Developer Descriptive Flexfield structure column value or any of
1968 -- the data values are invalid then an application error is raised as
1969 -- a PL/SQL exception.
1970 --
1971 -- Access Status:
1972 -- Internal Row Handler Use Only.
1973 --
1974 -- ----------------------------------------------------------------------------
1975 procedure chk_ddf
1979 l_proc varchar2(72) := g_package || 'chk_ddf';
1976 (p_rec in per_qua_shd.g_rec_type
1977 ) is
1978 --
1980 --
1981 begin
1982 hr_utility.set_location('Entering:'||l_proc,10);
1983 --
1984 if ((p_rec.qualification_id is not null) and (
1985 nvl(per_qua_shd.g_old_rec.qua_information_category, hr_api.g_varchar2) <>
1986 nvl(p_rec.qua_information_category, hr_api.g_varchar2) or
1987 nvl(per_qua_shd.g_old_rec.qua_information1, hr_api.g_varchar2) <>
1988 nvl(p_rec.qua_information1, hr_api.g_varchar2) or
1989 nvl(per_qua_shd.g_old_rec.qua_information2, hr_api.g_varchar2) <>
1990 nvl(p_rec.qua_information2, hr_api.g_varchar2) or
1991 nvl(per_qua_shd.g_old_rec.qua_information3, hr_api.g_varchar2) <>
1992 nvl(p_rec.qua_information3, hr_api.g_varchar2) or
1993 nvl(per_qua_shd.g_old_rec.qua_information4, hr_api.g_varchar2) <>
1994 nvl(p_rec.qua_information4, hr_api.g_varchar2) or
1995 nvl(per_qua_shd.g_old_rec.qua_information5, hr_api.g_varchar2) <>
1996 nvl(p_rec.qua_information5, hr_api.g_varchar2) or
1997 nvl(per_qua_shd.g_old_rec.qua_information6, hr_api.g_varchar2) <>
1998 nvl(p_rec.qua_information6, hr_api.g_varchar2) or
1999 nvl(per_qua_shd.g_old_rec.qua_information7, hr_api.g_varchar2) <>
2000 nvl(p_rec.qua_information7, hr_api.g_varchar2) or
2001 nvl(per_qua_shd.g_old_rec.qua_information8, hr_api.g_varchar2) <>
2002 nvl(p_rec.qua_information8, hr_api.g_varchar2) or
2003 nvl(per_qua_shd.g_old_rec.qua_information9, hr_api.g_varchar2) <>
2004 nvl(p_rec.qua_information9, hr_api.g_varchar2) or
2005 nvl(per_qua_shd.g_old_rec.qua_information10, hr_api.g_varchar2) <>
2006 nvl(p_rec.qua_information10, hr_api.g_varchar2) or
2007 nvl(per_qua_shd.g_old_rec.qua_information11, hr_api.g_varchar2) <>
2008 nvl(p_rec.qua_information11, hr_api.g_varchar2) or
2009 nvl(per_qua_shd.g_old_rec.qua_information12, hr_api.g_varchar2) <>
2010 nvl(p_rec.qua_information12, hr_api.g_varchar2) or
2011 nvl(per_qua_shd.g_old_rec.qua_information13, hr_api.g_varchar2) <>
2012 nvl(p_rec.qua_information13, hr_api.g_varchar2) or
2013 nvl(per_qua_shd.g_old_rec.qua_information14, hr_api.g_varchar2) <>
2014 nvl(p_rec.qua_information14, hr_api.g_varchar2) or
2015 nvl(per_qua_shd.g_old_rec.qua_information15, hr_api.g_varchar2) <>
2016 nvl(p_rec.qua_information15, hr_api.g_varchar2) or
2017 nvl(per_qua_shd.g_old_rec.qua_information16, hr_api.g_varchar2) <>
2018 nvl(p_rec.qua_information16, hr_api.g_varchar2) or
2019 nvl(per_qua_shd.g_old_rec.qua_information17, hr_api.g_varchar2) <>
2020 nvl(p_rec.qua_information17, hr_api.g_varchar2) or
2021 nvl(per_qua_shd.g_old_rec.qua_information18, hr_api.g_varchar2) <>
2022 nvl(p_rec.qua_information18, hr_api.g_varchar2) or
2023 nvl(per_qua_shd.g_old_rec.qua_information19, hr_api.g_varchar2) <>
2024 nvl(p_rec.qua_information19, hr_api.g_varchar2) or
2025 nvl(per_qua_shd.g_old_rec.qua_information20, hr_api.g_varchar2) <>
2026 nvl(p_rec.qua_information20, hr_api.g_varchar2) ))
2027 or (p_rec.qualification_id is null) then
2028 --
2029 -- Only execute the validation if absolutely necessary:
2030 -- a) During update, the structure column value or any
2031 -- of the attribute values have actually changed.
2032 -- b) During insert.
2033 --
2034 hr_dflex_utility.ins_or_upd_descflex_attribs
2035 (p_appl_short_name => 'PER'
2036 ,p_descflex_name => 'Qualification Developer DF'
2037 ,p_attribute_category => p_rec.qua_INFORMATION_CATEGORY
2038 ,p_attribute1_name => 'QUA_INFORMATION1'
2039 ,p_attribute1_value => p_rec.qua_information1
2040 ,p_attribute2_name => 'QUA_INFORMATION2'
2041 ,p_attribute2_value => p_rec.qua_information2
2042 ,p_attribute3_name => 'QUA_INFORMATION3'
2043 ,p_attribute3_value => p_rec.qua_information3
2044 ,p_attribute4_name => 'QUA_INFORMATION4'
2045 ,p_attribute4_value => p_rec.qua_information4
2046 ,p_attribute5_name => 'QUA_INFORMATION5'
2047 ,p_attribute5_value => p_rec.qua_information5
2048 ,p_attribute6_name => 'QUA_INFORMATION6'
2049 ,p_attribute6_value => p_rec.qua_information6
2050 ,p_attribute7_name => 'QUA_INFORMATION7'
2051 ,p_attribute7_value => p_rec.qua_information7
2052 ,p_attribute8_name => 'QUA_INFORMATION8'
2053 ,p_attribute8_value => p_rec.qua_information8
2054 ,p_attribute9_name => 'QUA_INFORMATION9'
2055 ,p_attribute9_value => p_rec.qua_information9
2056 ,p_attribute10_name => 'QUA_INFORMATION10'
2057 ,p_attribute10_value => p_rec.qua_information10
2058 ,p_attribute11_name => 'QUA_INFORMATION11'
2059 ,p_attribute11_value => p_rec.qua_information11
2060 ,p_attribute12_name => 'QUA_INFORMATION12'
2061 ,p_attribute12_value => p_rec.qua_information12
2062 ,p_attribute13_name => 'QUA_INFORMATION13'
2063 ,p_attribute13_value => p_rec.qua_information13
2064 ,p_attribute14_name => 'QUA_INFORMATION14'
2065 ,p_attribute14_value => p_rec.qua_information14
2066 ,p_attribute15_name => 'QUA_INFORMATION15'
2070 ,p_attribute17_name => 'QUA_INFORMATION17'
2067 ,p_attribute15_value => p_rec.qua_information15
2068 ,p_attribute16_name => 'QUA_INFORMATION16'
2069 ,p_attribute16_value => p_rec.qua_information16
2071 ,p_attribute17_value => p_rec.qua_information17
2072 ,p_attribute18_name => 'QUA_INFORMATION18'
2073 ,p_attribute18_value => p_rec.qua_information18
2074 ,p_attribute19_name => 'QUA_INFORMATION19'
2075 ,p_attribute19_value => p_rec.qua_information19
2076 ,p_attribute20_name => 'QUA_INFORMATION20'
2077 ,p_attribute20_value => p_rec.qua_information20
2078 );
2079 end if;
2080 --
2081 hr_utility.set_location(' Leaving:'||l_proc,20);
2082 end chk_ddf;
2083 --
2084 -- ----------------------------------------------------------------------------
2085 -- |---------------------------< insert_validate >----------------------------|
2086 -- ----------------------------------------------------------------------------
2087 Procedure insert_validate(p_rec in out nocopy per_qua_shd.g_rec_type,
2088 p_effective_date in date) is
2089 --
2090 l_proc varchar2(72) := g_package||'insert_validate';
2091 --
2092 Begin
2093 hr_utility.set_location('Entering:'||l_proc, 5);
2094 --
2095 -- Call all supporting business operations
2096 --
2097 -- Business Rule Mapping
2098 -- =====================
2099 -- CHK_BUSINESS_GROUP_ID
2100 -- HR/TCA merge
2101 -- if party_id is not null, business_group_id is not mandatory parameter
2102 --
2103 if p_rec.party_id is null and p_rec.business_group_id is not null then
2104 hr_api.validate_bus_grp_id
2105 (p_business_group_id => p_rec.business_group_id
2106 ,p_associated_column1 => per_qua_shd.g_tab_nam || '.BUSINESS_GROUP_ID'
2107 ); -- Validate Bus Grp
2108 end if;
2109 --
2110 -- After validating the set of important attributes,
2111 -- if Multiple Message Detection is enabled and at least
2112 -- one error has been found then abort further validation.
2113 --
2114 hr_multi_message.end_validation_set;
2115 --
2116 --
2117 -- Business Rule Mapping
2118 -- =====================
2119 -- CHK_QUALIFICATION_ID
2120 chk_qualification_id
2121 (p_qualification_id => p_rec.qualification_id,
2122 p_object_version_number => p_rec.object_version_number);
2123 --
2124 -- Business Rule Mapping
2125 -- =====================
2126 -- CHK_QUALIFICATION_TYPE_ID
2127 chk_qualification_type_id
2128 (p_qualification_id => p_rec.qualification_id,
2129 p_qualification_type_id => p_rec.qualification_type_id,
2130 p_object_version_number => p_rec.object_version_number);
2131 --
2132 -- Business Rule Mapping
2133 -- =====================
2134 -- CHK_PERSON_ID
2135 chk_person_id
2136 (p_effective_date => p_effective_date,
2137 p_qualification_id => p_rec.qualification_id,
2138 p_person_id => p_rec.person_id,
2139 p_attendance_id => p_rec.attendance_id,
2140 p_object_version_number => p_rec.object_version_number,
2141 p_party_id => p_rec.party_id);
2142 --
2143 -- Business Rule Mapping
2144 -- =====================
2145 -- CHK_PARTY_ID
2146 chk_party_id
2147 (p_rec
2148 ,p_effective_date
2149 );
2150 --
2151 -- Business Rule Mapping
2152 -- =====================
2153 -- CHK_STATUS
2154 chk_status
2155 (p_qualification_id => p_rec.qualification_id,
2156 p_status => p_rec.status,
2157 p_effective_date => p_effective_date,
2158 p_object_version_number => p_rec.object_version_number);
2159 --
2160 -- Business Rule Mapping
2161 -- =====================
2162 -- CHK_AWARDED_DATE
2163 chk_awarded_date
2164 (p_qualification_id => p_rec.qualification_id,
2165 p_awarded_date => p_rec.awarded_date,
2166 p_start_date => p_rec.start_date,
2167 p_end_date => p_rec.end_date,
2168 p_projected_completion_date => p_rec.projected_completion_date,
2169 p_object_version_number => p_rec.object_version_number);
2170 --
2171 -- Business Rule Mapping
2172 -- =====================
2173 -- CHK_FEE
2174 -- CHK_FEE_CURRENCY
2175 chk_fee
2176 (p_qualification_id => p_rec.qualification_id,
2177 p_fee => p_rec.fee,
2178 p_fee_currency => p_rec.fee_currency,
2179 p_object_version_number => p_rec.object_version_number);
2180 --
2181 -- Busines Rule Mapping
2182 -- ====================
2183 -- CHK_START_DATE
2184 -- CHK_END_DATE
2185 chk_start_date
2186 (p_qualification_id => p_rec.qualification_id,
2187 p_attendance_id => p_rec.attendance_id,
2188 p_start_date => p_rec.start_date,
2189 p_end_date => p_rec.end_date,
2190 p_object_version_number => p_rec.object_version_number,
2191 p_effective_date => p_effective_date,
2192 p_person_id => p_rec.person_id);
2193
2194 -- Bug Fix 3267372.
2198 /* --
2195 -- Validation of qualification end date against subject
2196 -- start date and end date is relaxed.
2197
2199 -- Busines Rule Mapping
2200 -- ====================
2201 -- CHK_END_DATE
2202 chk_end_date
2203 (p_qualification_id => p_rec.qualification_id,
2204 p_start_date => p_rec.start_date,
2205 p_end_date => p_rec.end_date,
2206 p_object_version_number => p_rec.object_version_number);*/
2207 --
2208 -- Business Rule Mapping
2209 -- =====================
2210 -- CHK_PROJECTED_COMPLETION_DATE
2211 chk_projected_completion_date
2212 (p_qualification_id => p_rec.qualification_id,
2213 p_start_date => p_rec.start_date,
2214 p_projected_completion_date => p_rec.projected_completion_date,
2215 p_object_version_number => p_rec.object_version_number);
2216 --
2217 -- Business Rule Mapping
2218 -- =====================
2219 -- CHK_TUITION_METHOD
2220 chk_tuition_method
2221 (p_qualification_id => p_rec.qualification_id,
2222 p_tuition_method => p_rec.tuition_method,
2223 p_effective_date => p_effective_date,
2224 p_object_version_number => p_rec.object_version_number);
2225 --
2226 --
2227 -- Business Rule Mapping
2228 -- =====================
2229 -- CHK_ESTAB_ATT_BG
2230 chk_estab_att_bg
2231 (p_qualification_id => p_rec.qualification_id,
2232 p_attendance_id => p_rec.attendance_id,
2233 p_business_group_id => p_rec.business_group_id,
2234 p_object_version_number => p_rec.object_version_number);
2235 --
2236 -- Business Rule Mapping
2237 -- =====================
2238 -- CHK_PERSON_BG
2239 chk_person_bg
2240 (p_effective_date => p_effective_date,
2241 p_qualification_id => p_rec.qualification_id,
2242 p_person_id => p_rec.person_id,
2243 p_business_group_id => p_rec.business_group_id,
2244 p_object_version_number => p_rec.object_version_number);
2245 --
2246 -- Descriptive Flex Check
2247 -- ======================
2248 --
2249 /*
2250 IF hr_general.get_calling_context <>FORMS' THEN
2251 per_qua_flex.df(p_rec => p_rec);
2252 END IF;
2253 */
2254 --
2255 -- call descriptive flexfield validation routines
2256 --
2257 per_qua_bus.chk_df(p_rec => p_rec);
2258 --
2259 per_qua_bus.chk_ddf(p_rec);
2260 --
2261 hr_utility.set_location(' Leaving:'||l_proc, 10);
2262 End insert_validate;
2263 --
2264 -- ----------------------------------------------------------------------------
2265 -- |---------------------------< update_validate >----------------------------|
2266 -- ----------------------------------------------------------------------------
2267 Procedure update_validate(p_rec in out nocopy per_qua_shd.g_rec_type,
2268 p_effective_date in date) is
2269 --
2270 l_proc varchar2(72) := g_package||'update_validate';
2271 --
2272 Begin
2273 hr_utility.set_location('Entering:'||l_proc, 5);
2274 hr_utility.set_location('End Date Hello = '||p_rec.end_date,998);
2275 --
2276 -- Business Rule Mapping
2277 -- =====================
2278 -- CHK_BUSINESS_GROUP_ID
2279 -- if party_id is not null, business_group_id is not mandatory parameter
2280 --
2281 if p_rec.party_id is null and p_rec.business_group_id is not null then
2282 hr_api.validate_bus_grp_id
2283 (p_business_group_id => p_rec.business_group_id
2284 ,p_associated_column1 => per_qua_shd.g_tab_nam || '.BUSINESS_GROUP_ID'
2285 ); -- Validate Bus Grp
2286 end if;
2287 --
2288 -- After validating the set of important attributes,
2289 -- if Multiple Message Detection is enabled and at least
2290 -- one error has been found then abort further validation.
2291 --
2292 hr_multi_message.end_validation_set;
2293 --
2294 --
2295 -- Business Rule Mapping
2296 -- =====================
2297 -- CHK_QUALIFICATION_ID
2298 chk_qualification_id
2299 (p_qualification_id => p_rec.qualification_id,
2300 p_object_version_number => p_rec.object_version_number);
2301 --
2302 -- Business Rule Mapping
2303 -- =====================
2304 -- CHK_QUALIFICATION_TYPE_ID
2305 chk_qualification_type_id
2306 (p_qualification_id => p_rec.qualification_id,
2307 p_qualification_type_id => p_rec.qualification_type_id,
2308 p_object_version_number => p_rec.object_version_number);
2309 --
2310 -- Business Rule Mapping
2311 -- =====================
2312 -- CHK_PERSON_ID
2313 chk_person_id
2314 (p_effective_date => p_effective_date,
2315 p_qualification_id => p_rec.qualification_id,
2316 p_person_id => p_rec.person_id,
2317 p_attendance_id => p_rec.attendance_id,
2318 p_object_version_number => p_rec.object_version_number,
2319 p_party_id => p_rec.party_id);
2320 --
2321 -- Business Rule Mapping
2322 -- =====================
2323 -- CHK_PARTY_ID
2324 chk_party_id
2325 (p_rec
2326 ,p_effective_date
2327 );
2328 --
2329 --
2330 -- Business Rule Mapping
2331 -- =====================
2332 -- CHK_STATUS
2333 chk_status
2337 p_object_version_number => p_rec.object_version_number);
2334 (p_qualification_id => p_rec.qualification_id,
2335 p_status => p_rec.status,
2336 p_effective_date => p_effective_date,
2338 --
2339 -- Business Rule Mapping
2340 -- =====================
2341 hr_utility.set_location('End Date Hello = '||p_rec.end_date,999);
2342 -- CHK_AWARDED_DATE
2343 chk_awarded_date
2344 (p_qualification_id => p_rec.qualification_id,
2345 p_awarded_date => p_rec.awarded_date,
2346 p_start_date => p_rec.start_date,
2347 p_end_date => p_rec.end_date,
2348 p_projected_completion_date => p_rec.projected_completion_date,
2349 p_object_version_number => p_rec.object_version_number);
2350 --
2351 -- Business Rule Mapping
2352 -- =====================
2353 -- CHK_FEE
2354 -- CHK_FEE_CURRENCY
2355 chk_fee
2356 (p_qualification_id => p_rec.qualification_id,
2357 p_fee => p_rec.fee,
2358 p_fee_currency => p_rec.fee_currency,
2359 p_object_version_number => p_rec.object_version_number);
2360 --
2361 -- Busines Rule Mapping
2362 -- ====================
2363 -- CHK_START_DATE
2364 chk_start_date
2365 (p_qualification_id => p_rec.qualification_id,
2366 p_attendance_id => p_rec.attendance_id,
2367 p_start_date => p_rec.start_date,
2368 p_end_date => p_rec.end_date,
2369 p_object_version_number => p_rec.object_version_number,
2370 p_effective_date => p_effective_date,
2371 p_person_id => p_rec.person_id);
2372
2373 -- Bug Fix 3267372.
2374 -- Validation of qualification end date against subject
2375 -- start date and end date is relaxed.
2376 --
2377 /*
2378 -- Busines Rule Mapping
2379 -- ====================
2380 -- CHK_END_DATE
2381 chk_end_date
2382 (p_qualification_id => p_rec.qualification_id,
2383 p_start_date => p_rec.start_date,
2384 p_end_date => p_rec.end_date,
2385 p_object_version_number => p_rec.object_version_number);
2386 --
2387 */
2388 -- Business Rule Mapping
2389 -- =====================
2390 -- CHK_PROJECTED_COMPLETION_DATE
2391 chk_projected_completion_date
2392 (p_qualification_id => p_rec.qualification_id,
2393 p_start_date => p_rec.start_date,
2394 p_projected_completion_date => p_rec.projected_completion_date,
2395 p_object_version_number => p_rec.object_version_number);
2396 --
2397 -- Business Rule Mapping
2398 -- =====================
2399 -- CHK_TUITION_METHOD
2400 chk_tuition_method
2401 (p_qualification_id => p_rec.qualification_id,
2402 p_tuition_method => p_rec.tuition_method,
2403 p_effective_date => p_effective_date,
2404 p_object_version_number => p_rec.object_version_number);
2405 --
2406 -- Business Rule Mapping
2407 -- =====================
2408 -- CHK_ESTAB_ATT_BG
2409 chk_estab_att_bg
2410 (p_qualification_id => p_rec.qualification_id,
2411 p_attendance_id => p_rec.attendance_id,
2412 p_business_group_id => p_rec.business_group_id,
2413 p_object_version_number => p_rec.object_version_number);
2414 --
2415 -- Business Rule Mapping
2416 -- =====================
2417 -- CHK_PERSON_BG
2418 chk_person_bg
2419 (p_effective_date => p_effective_date,
2420 p_qualification_id => p_rec.qualification_id,
2421 p_person_id => p_rec.person_id,
2422 p_business_group_id => p_rec.business_group_id,
2423 p_object_version_number => p_rec.object_version_number);
2424 --
2425 -- Descriptive Flex Check
2426 -- ======================
2427 --
2428 /*
2429 IF hr_general.get_calling_context <>FORMS' THEN
2430 per_qua_flex.df(p_rec => p_rec);
2431 END IF;
2432 */
2433 --
2434 -- call descriptive flexfield validation routines
2435 --
2436 per_qua_bus.chk_df(p_rec => p_rec);
2437 --
2438 per_qua_bus.chk_ddf(p_rec);
2439 --
2440 hr_utility.set_location(' Leaving:'||l_proc, 10);
2441 End update_validate;
2442 --
2443 -- ----------------------------------------------------------------------------
2444 -- |---------------------------< delete_validate >----------------------------|
2445 -- ----------------------------------------------------------------------------
2446 Procedure delete_validate(p_rec in per_qua_shd.g_rec_type) is
2447 --
2448 l_proc varchar2(72) := g_package||'delete_validate';
2449 --
2450 Begin
2451 hr_utility.set_location('Entering:'||l_proc, 5);
2452 --
2453 -- Call all supporting business operations
2454 --
2455 -- Business Rule Mapping
2456 -- =====================
2457 -- CHK_QUALIFICATION_DELETE
2458 chk_qualification_delete(p_qualification_id => p_rec.qualification_id);
2459 --
2460 hr_utility.set_location(' Leaving:'||l_proc, 10);
2461 End delete_validate;
2462 --
2463 end per_qua_bus;