[Home] [Help]
PACKAGE BODY: APPS.PER_SUB_BUS
Source
1 Package Body per_sub_bus as
2 /* $Header: pesubrhi.pkb 115.14 2004/02/23 01:47:08 smparame ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_sub_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< chk_subjects_taken_id >----------------------|
12 -- ----------------------------------------------------------------------------
13 -- Description
14 -- This procedure checks that a subjects_taken_id is unique. This column
15 -- is the primary key for the entity and so must be null on insert and
16 -- non-updateable on update.
17 --
18 -- Pre-Conditions
19 -- None.
20 --
21 -- In Parameters
22 -- p_subjects_taken_id PK
23 -- p_object_version_number object version number
24 --
25 -- Post Success
26 -- Processing continues
27 --
28 -- Post Failure
29 -- Error raised.
30 --
31 -- Access Status
32 -- Internal table handler use only.
33 --
34 Procedure chk_subjects_taken_id (p_subjects_taken_id in number,
35 p_object_version_number in number) is
36 --
37 l_proc varchar2(72) := g_package||'chk_subjects_taken_id';
38 l_api_updating boolean;
39 --
40 Begin
41 --
42 hr_utility.set_location('Entering:'||l_proc,5);
43 --
44 l_api_updating := per_sub_shd.api_updating
45 (p_subjects_taken_id => p_subjects_taken_id,
46 p_object_version_number => p_object_version_number);
47 --
48 if (l_api_updating
49 and nvl(p_subjects_taken_id,hr_api.g_number)
50 <> per_sub_shd.g_old_rec.subjects_taken_id) then
51 --
52 -- raise error as PK has changed
53 --
54 per_sub_shd.constraint_error('PER_SUBJECTS_TAKEN_PK');
55 --
56 elsif not l_api_updating then
57 --
58 -- check if PK is null
59 --
60 if p_subjects_taken_id is not null then
61 --
62 -- raise error as PK is not null
63 --
64 per_sub_shd.constraint_error('PER_SUBJECTS_TAKEN_PK');
65 --
66 end if;
67 --
68 end if;
69 --
70 hr_utility.set_location('Leaving:'||l_proc,10);
71 --
72 End chk_subjects_taken_id;
73 --
74 -- ----------------------------------------------------------------------------
75 -- |---------------------------< chk_qualification_id >-----------------------|
76 -- ----------------------------------------------------------------------------
77 -- Description
78 -- This procedure checks that the qualification_id is not null. This is
79 -- a foreign key to the PER_QUALIFICATIONS table and so must be populated
80 -- on insert and on update.
81 --
82 -- Pre-Conditions
83 -- None.
84 --
85 -- In Parameters
86 -- p_subjects_taken_id PK
87 -- p_qualification_id ID of referenced qualification record.
88 -- p_object_version_number object version number
89 --
90 -- Post Success
91 -- Processing continues
92 --
93 -- Post Failure
94 -- Error raised.
95 --
96 -- Access Status
97 -- Internal table handler use only.
98 --
99 Procedure chk_qualification_id (p_subjects_taken_id in number,
100 p_qualification_id in number,
101 p_object_version_number in number) is
102 --
103 l_proc varchar2(72) := g_package||'chk_qualification_id';
104 l_api_updating boolean;
105 l_dummy varchar2(1);
106 --
107 cursor c1 is
108 select null
109 from per_qualifications per
110 where per.qualification_id = p_qualification_id;
111 --
112 Begin
113 --
114 hr_utility.set_location('Entering:'||l_proc,5);
115 --
116 l_api_updating := per_sub_shd.api_updating
117 (p_subjects_taken_id => p_subjects_taken_id,
118 p_object_version_number => p_object_version_number);
119 --
120 if (l_api_updating
121 and nvl(p_qualification_id,hr_api.g_number)
122 <> per_sub_shd.g_old_rec.qualification_id
123 or not l_api_updating) then
124 --
125 -- check if qualification id exists in per_qualifications table.
126 -- it must also be a not null value.
127 --
128 open c1;
129 --
130 fetch c1 into l_dummy;
131 if c1%notfound then
132 --
133 close c1;
134 per_sub_shd.constraint_error('PER_SUBJECTS_TAKEN_FK1');
135 --
136 end if;
137 --
138 close c1;
139 --
140 end if;
141 --
142 hr_utility.set_location('Leaving:'||l_proc,10);
143 --
144 End chk_qualification_id;
145 --
146 -- ----------------------------------------------------------------------------
147 -- |---------------------------< chk_start_date >-----------------------------|
148 -- ----------------------------------------------------------------------------
149 -- Description
150 -- This procedure checks that the start date for the subject is valid. This
151 -- includes checking that the start date of the subject is before the end
152 -- date of the subject. This procedure also ensures that the start
153 -- dates of the subject are within the start and end dates of the
154 -- qualification.
155 --
156 -- Pre-Conditions
157 -- None.
158 --
159 -- In Parameters
160 -- p_subjects_taken_id PK
161 -- p_qualification_id ID of referenced qualification record.
162 -- p_start_date start date of subject
163 -- p_end_date end date of subject
164 -- p_object_version_number object version number
165 --
166 -- Post Success
167 -- Processing continues
168 --
169 -- Post Failure
170 -- Error raised.
171 --
172 -- Access Status
173 -- Internal table handler use only.
174 --
175 Procedure chk_start_date (p_subjects_taken_id in number,
176 p_qualification_id in number,
177 p_start_date in date,
178 p_end_date in date,
179 p_object_version_number in number) is
180 --
181 l_proc varchar2(72) := g_package||'chk_start_date';
182 l_api_updating boolean;
183 l_dummy varchar2(1);
184 l_start_date date; -- part of bug fix 1854046
185 l_end_date date;
186
187 --
188 -- If a qualification has not got a start date then subjects can not be
189 -- assigned to that qualification as subjects must have a start date.
190 --
191 --
192 -- Bug Fix 3267372.
193 -- Validation of qualification end date against subject
194 -- start date and end date is relaxed.
195 cursor c1 is
196 select null
197 from per_qualifications per
198 where per.qualification_id = p_qualification_id
199 and p_start_date between nvl(per.start_date,hr_api.g_sot)
200 and nvl(per.end_date,p_start_date)
201 and (p_start_date <= nvl(per.end_date,hr_api.g_eot));
202
203 --
204 Begin
205 --
206 select start_date,end_date
207 into l_start_date,l_end_date
208 from per_qualifications per
209 where per.qualification_id = p_qualification_id;
210 --
211 hr_utility.set_location('Entering:'||l_proc,5);
212 --
213 l_api_updating := per_sub_shd.api_updating
214 (p_subjects_taken_id => p_subjects_taken_id,
215 p_object_version_number => p_object_version_number);
216 --
217 if (l_api_updating
218 /*
219 WWBUG 2502284 backport drove this.
220 and (nvl(p_start_date,hr_api.g_date)
221 <> per_sub_shd.g_old_rec.start_date
222 or nvl(p_end_date,hr_api.g_date)
223 <> per_sub_shd.g_old_rec.end_date)
224 */
225 or not l_api_updating) then
226 --
227 -- check if start_date and end date fall within the dates of the
228 -- qualification that they are linked to.
229 --
230 open c1;
231 --
232 fetch c1 into l_dummy;
233 if l_start_date is not null then
234 if c1%notfound then
235 --
236 close c1;
237 hr_utility.set_message(801,'HR_51817_SUB_START_DATE_QUAL');
238 hr_utility.raise_error;
239 --
240 end if;
241 end if;
242 --
243 close c1;
244 --
245 -- Check if end date is greater than start date
246 --
247 if p_end_date < p_start_date then
248 --
249 per_sub_shd.constraint_error('PER_SUB_START_DATES');
250 --
251 end if;
252 --
253 if p_start_date > nvl(l_end_date,p_start_date) then
254 --
255 hr_utility.set_message(801,'HR_51817_SUB_START_DATE_QUAL');
256 hr_utility.raise_error;
257 --
258 end if;
259 --
260 end if;
261 --
262 hr_utility.set_location('Leaving:'||l_proc,5);
263 --
264 End chk_start_date;
265 --
266 --
267
268 --
269 -- ----------------------------------------------------------------------------
270 -- |---------------------------< chk_end_date >-----------------------------|
271 -- ----------------------------------------------------------------------------
272 -- Description
273 -- This procedure checks that the end date for the subject is valid. This
274 -- includes checking that the end date of the subject is after the start
275 -- date of the subject. This procedure also ensures that the end
276 -- dates of the subject are within the start and end dates of the
277 -- qualification.
278 --
279 --This check procedure has been included to reslove the bug 1854046
280 --
281 -- Pre-Conditions
282 -- None.
283 --
284 -- In Parameters
285 -- p_subjects_taken_id PK
286 -- p_qualification_id ID of referenced qualification record.
287 -- p_start_date start date of subject
288 -- p_end_date end date of subject
289 -- p_object_version_number object version number
290 --
291 -- Post Success
292 -- Processing continues
293 --
294 -- Post Failure
295 -- Error raised.
296 --
297 -- Access Status
298 -- Internal table handler use only.
299 --
300 Procedure chk_end_date (p_subjects_taken_id in number,
301 p_qualification_id in number,
302 p_start_date in date,
303 p_end_date in date,
304 p_object_version_number in number) is
305 --
306 l_proc varchar2(72) := g_package||'chk_end_date';
307 l_api_updating boolean;
308 l_dummy varchar2(1);
309 l_end_date date; -- part of bug fix 1854046
310
311 --
312 -- If a qualification has got an end date then subjects can not be
313 -- assigned to that qualification unless their dates lie within the
314 -- qualification end/start dates.
315 --
316 -- Bug Fix 3267372.
317 -- Validation of qualification end date against subject
318 -- start date and end date is relaxed.
319
320 cursor c1 is
321 select null
322 from per_qualifications per
323 where per.qualification_id = p_qualification_id
324 and nvl(p_end_date,nvl(per.end_date,hr_api.g_eot))
325 between nvl(per.start_date,hr_api.g_sot)
326 and nvl(per.end_date,hr_api.g_eot);
327
328 --
329 Begin
330 --
331 select end_date
332 into l_end_date
333 from per_qualifications per
334 where per.qualification_id = p_qualification_id;
335 --
336 hr_utility.set_location('Entering:'||l_proc,5);
337 --
338 l_api_updating := per_sub_shd.api_updating
339 (p_subjects_taken_id => p_subjects_taken_id,
340 p_object_version_number => p_object_version_number);
341 --
342 if (l_api_updating
343 or not l_api_updating) then
344 --
345 -- check if end date fall within the dates of the
346 -- qualification that they are linked to.
347 -- It only checks the two if they are entered.
348 open c1;
349 --
350 fetch c1 into l_dummy;
351 if l_end_date is not null and p_end_date is not null then
352 if c1%notfound then
353 --
354 close c1;
355 hr_utility.set_message(801,'HR_289802_SUB_END_QUAL');
356 hr_utility.raise_error;
357 --
358 end if;
359 end if;
360 --
361 close c1;
362
363 --
364 end if;
365 --
366 End chk_end_date;
367 --
368 -- ----------------------------------------------------------------------------
369 -- |---------------------------< chk_major >----------------------------------|
370 -- ----------------------------------------------------------------------------
371 -- Description
372 -- This procedure checks that the major value exists in the lookup YES_NO.
373 --
374 -- Pre-Conditions
375 -- None.
376 --
377 -- In Parameters
378 -- p_subjects_taken_id PK
379 -- p_major value of major column
380 -- p_effective_date effective date of session
381 -- p_object_version_number object version number
382 --
383 -- Post Success
384 -- Processing continues
385 --
386 -- Post Failure
387 -- Error raised.
388 --
389 -- Access Status
390 -- Internal table handler use only.
391 --
392 Procedure chk_major (p_subjects_taken_id in number,
393 p_major in varchar2,
394 p_effective_date in date,
395 p_object_version_number in number) is
396 --
397 l_proc varchar2(72) := g_package||'chk_major';
398 l_api_updating boolean;
399 --
400 Begin
401 --
402 hr_utility.set_location('Entering:'||l_proc,5);
403 --
404 l_api_updating := per_sub_shd.api_updating
405 (p_subjects_taken_id => p_subjects_taken_id,
406 p_object_version_number => p_object_version_number);
407 --
408 if p_major is not null then --p_major is optional field.
409 if (l_api_updating
410 and nvl(p_major,hr_api.g_varchar2)
411 <> per_sub_shd.g_old_rec.major
412 or not l_api_updating) then
413 --
414 -- check if major exists in the lookup YES_NO.
415 --
416 if hr_api.not_exists_in_hr_lookups
417 (p_effective_date => p_effective_date,
418 p_lookup_type => 'YES_NO',
419 p_lookup_code => p_major) then
420 --
421 hr_utility.set_message(801,'HR_51818_SUB_MAJOR_LKP_INV');
422 hr_utility.raise_error;
423 --
424 end if;
425 --
426 end if;
427 end if;
428 --
429 hr_utility.set_location('Leaving:'||l_proc,5);
430 --
431 End chk_major;
432 --
433 -- ----------------------------------------------------------------------------
434 -- |---------------------------< chk_subject_status >-------------------------|
435 -- ----------------------------------------------------------------------------
436 -- Description
437 -- This procedure checks that the subject_status value exists in the lookup
438 -- PER_SUBJECT_STATUSES.
439 --
440 -- Pre-Conditions
441 -- None.
442 --
443 -- In Parameters
444 -- p_subjects_taken_id PK
445 -- p_subject_status value of subject_status column
446 -- p_effective_date effective date of session
447 -- p_object_version_number object version number
448 --
449 -- Post Success
450 -- Processing continues
451 --
452 -- Post Failure
453 -- Error raised.
454 --
455 -- Access Status
456 -- Internal table handler use only.
457 --
458 Procedure chk_subject_status (p_subjects_taken_id in number,
459 p_subject_status in varchar2,
460 p_effective_date in date,
461 p_object_version_number in number) is
462 --
463 l_proc varchar2(72) := g_package||'chk_subject_status';
464 l_api_updating boolean;
465 --
466 Begin
467 --
468 hr_utility.set_location('Entering:'||l_proc,5);
469 --
470 l_api_updating := per_sub_shd.api_updating
471 (p_subjects_taken_id => p_subjects_taken_id,
472 p_object_version_number => p_object_version_number);
473 --
474 if (l_api_updating
475 and nvl(p_subject_status,hr_api.g_varchar2)
476 <> per_sub_shd.g_old_rec.subject_status
477 or not l_api_updating) then
478 --
479 -- check if subject_status exists in the lookup PER_SUBJECT_STATUSES.
480 --
481 if hr_api.not_exists_in_hr_lookups
482 (p_effective_date => p_effective_date,
483 p_lookup_type => 'PER_SUBJECT_STATUSES',
484 p_lookup_code => p_subject_status) then
485 --
486 hr_utility.set_message(801,'HR_51819_SUB_STATUS_LKP_INV');
487 hr_utility.raise_error;
488 --
489 end if;
490 --
491 end if;
492 --
493 hr_utility.set_location('Leaving:'||l_proc,5);
494 --
495 End chk_subject_status;
496 --
497 -- ----------------------------------------------------------------------------
498 -- |---------------------------< chk_subject >--------------------------------|
499 -- ----------------------------------------------------------------------------
500 -- Description
501 -- This procedure checks that the subject value exists in the lookup
502 -- PER_SUBJECTS.
503 --
504 -- Pre-Conditions
505 -- None.
506 --
507 -- In Parameters
508 -- p_subjects_taken_id PK
509 -- p_subject value of subject column
510 -- p_effective_date effective date of session
511 -- p_object_version_number object version number
512 --
513 -- Post Success
514 -- Processing continues
515 --
516 -- Post Failure
517 -- Error raised.
518 --
519 -- Access Status
520 -- Internal table handler use only.
521 --
522 Procedure chk_subject (p_subjects_taken_id in number,
523 p_subject in varchar2,
524 p_qualification_id in number,
525 p_start_date in date,
526 p_end_date in date,
527 p_effective_date in date,
528 p_object_version_number in number) is
529 --
530 l_proc varchar2(72) := g_package||'chk_subject';
531 l_api_updating boolean;
532 l_dummy varchar2(1);
533 --
534 -- This cursor checks that this subject has not been used for this
535 -- qualification before. It checks for the boundaries of the existing
536 -- identical subjects and whether these have been breached. As it is
537 -- possible to take the same subject again in order to improve one's grade
538 -- this option must be catered for.
539 --
540 cursor c1 is
541 select null
542 from per_subjects_taken per
543 where per.qualification_id = p_qualification_id
544 and per.subject = p_subject
545 and (p_start_date
546 between per.start_date
547 and nvl(per.end_date,hr_api.g_eot)
548 or p_end_date
549 between per.start_date
550 and nvl(per.end_date,hr_api.g_eot))
551 and per.subjects_taken_id <> nvl(p_subjects_taken_id,-1);
552 --
553 Begin
554 --
555 hr_utility.set_location('Entering:'||l_proc,5);
556 --
557 l_api_updating := per_sub_shd.api_updating
558 (p_subjects_taken_id => p_subjects_taken_id,
559 p_object_version_number => p_object_version_number);
560 --
561 if (l_api_updating
562 and (nvl(p_subject,hr_api.g_varchar2)
563 <> per_sub_shd.g_old_rec.subject
564 or nvl(p_start_date,hr_api.g_date)
565 <> per_sub_shd.g_old_rec.start_date
566 or nvl(p_end_date,hr_api.g_date)
567 <> per_sub_shd.g_old_rec.end_date
568 or nvl(p_qualification_id,hr_api.g_number)
569 <> per_sub_shd.g_old_rec.qualification_id)
570 or not l_api_updating) then
571 --
572 -- check if subject exists in the lookup PER_SUBJECTS.
573 --
574 if hr_api.not_exists_in_hr_lookups
575 (p_effective_date => p_effective_date,
576 p_lookup_type => 'PER_SUBJECTS',
577 p_lookup_code => p_subject) then
578 --
579 hr_utility.set_message(801,'HR_51820_SUB_SUBJECT_LKP_INV');
580 hr_utility.raise_error;
581 --
582 end if;
583 --
584 -- Check if subject conflicts with a previous subject for the same
585 -- qualification that has an end date that is not before the start
586 -- date of the current subject.
587 --
588 open c1;
589 --
590 fetch c1 into l_dummy;
591 --
592 if c1%found then
593 --
594 -- raise error as subject trying to be added twice
595 --
596 close c1;
597 hr_utility.set_message(801,'HR_51821_SUB_SUB_DATE_OVLAP');
598 hr_utility.raise_error;
599 --
600 end if;
601 --
602 close c1;
603 --
604 end if;
605 --
606 hr_utility.set_location('Leaving:'||l_proc,5);
607 --
608 End chk_subject;
609 --
610 -- -----------------------------------------------------------------------
611 -- |------------------------------< chk_df >-----------------------------|
612 -- -----------------------------------------------------------------------
613 --
614 -- Description:
615 -- Validates the all Descriptive Flexfield values.
616 --
617 -- Pre-conditions:
618 -- All other columns have been validated. Must be called as the
619 -- last step from insert_validate and update_validate.
620 --
621 -- In Arguments:
622 -- p_rec
623 --
624 -- Post Success:
625 -- If the Descriptive Flexfield structure column and data values are
626 -- all valid this procedure will end normally and processing will
627 -- continue.
628 --
629 -- Post Failure:
630 -- If the Descriptive Flexfield structure column value or any of
631 -- the data values are invalid then an application error is raised as
632 -- a PL/SQL exception.
633 --
634 -- Access Status:
635 -- Internal Row Handler Use Only.
636 --
637 -- {End Of Comments}
638 -- ----------------------------------------------------------------------------
639 --
640 procedure chk_df
641 (p_rec in per_sub_shd.g_rec_type) is
642 --
643 l_proc varchar2(72) := g_package||'chk_df';
644 --
645 begin
646 hr_utility.set_location('Entering:'||l_proc, 10);
647 --
648 if ((p_rec.subjects_taken_id is not null) and (
649 nvl(per_sub_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
650 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
651 nvl(per_sub_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
652 nvl(p_rec.attribute1, hr_api.g_varchar2) or
653 nvl(per_sub_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
654 nvl(p_rec.attribute2, hr_api.g_varchar2) or
655 nvl(per_sub_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
656 nvl(p_rec.attribute3, hr_api.g_varchar2) or
657 nvl(per_sub_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
658 nvl(p_rec.attribute4, hr_api.g_varchar2) or
659 nvl(per_sub_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
660 nvl(p_rec.attribute5, hr_api.g_varchar2) or
661 nvl(per_sub_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
662 nvl(p_rec.attribute6, hr_api.g_varchar2) or
663 nvl(per_sub_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
664 nvl(p_rec.attribute7, hr_api.g_varchar2) or
665 nvl(per_sub_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
666 nvl(p_rec.attribute8, hr_api.g_varchar2) or
667 nvl(per_sub_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
668 nvl(p_rec.attribute9, hr_api.g_varchar2) or
669 nvl(per_sub_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
670 nvl(p_rec.attribute10, hr_api.g_varchar2) or
671 nvl(per_sub_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
672 nvl(p_rec.attribute11, hr_api.g_varchar2) or
673 nvl(per_sub_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
674 nvl(p_rec.attribute12, hr_api.g_varchar2) or
675 nvl(per_sub_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
676 nvl(p_rec.attribute13, hr_api.g_varchar2) or
677 nvl(per_sub_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
678 nvl(p_rec.attribute14, hr_api.g_varchar2) or
679 nvl(per_sub_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
680 nvl(p_rec.attribute15, hr_api.g_varchar2) or
681 nvl(per_sub_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
682 nvl(p_rec.attribute16, hr_api.g_varchar2) or
683 nvl(per_sub_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
684 nvl(p_rec.attribute17, hr_api.g_varchar2) or
685 nvl(per_sub_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
686 nvl(p_rec.attribute18, hr_api.g_varchar2) or
687 nvl(per_sub_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
688 nvl(p_rec.attribute19, hr_api.g_varchar2) or
689 nvl(per_sub_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
690 nvl(p_rec.attribute20, hr_api.g_varchar2)))
691 or
692 (p_rec.subjects_taken_id is null) then
693 --
694 -- Only execute the validation if absolutely necessary:
695 -- a) During update, the structure column value or any
696 -- of the attribute values have actually changed.
697 -- b) During insert.
698 --
699 hr_dflex_utility.ins_or_upd_descflex_attribs
700 (p_appl_short_name => 'PER'
701 ,p_descflex_name => 'PER_SUBJECTS_TAKEN'
702 ,p_attribute_category => p_rec.attribute_category
703 ,p_attribute1_name => 'ATTRIBUTE1'
704 ,p_attribute1_value => p_rec.attribute1
705 ,p_attribute2_name => 'ATTRIBUTE2'
706 ,p_attribute2_value => p_rec.attribute2
707 ,p_attribute3_name => 'ATTRIBUTE3'
708 ,p_attribute3_value => p_rec.attribute3
709 ,p_attribute4_name => 'ATTRIBUTE4'
710 ,p_attribute4_value => p_rec.attribute4
711 ,p_attribute5_name => 'ATTRIBUTE5'
712 ,p_attribute5_value => p_rec.attribute5
713 ,p_attribute6_name => 'ATTRIBUTE6'
714 ,p_attribute6_value => p_rec.attribute6
715 ,p_attribute7_name => 'ATTRIBUTE7'
716 ,p_attribute7_value => p_rec.attribute7
717 ,p_attribute8_name => 'ATTRIBUTE8'
718 ,p_attribute8_value => p_rec.attribute8
719 ,p_attribute9_name => 'ATTRIBUTE9'
720 ,p_attribute9_value => p_rec.attribute9
721 ,p_attribute10_name => 'ATTRIBUTE10'
722 ,p_attribute10_value => p_rec.attribute10
723 ,p_attribute11_name => 'ATTRIBUTE11'
724 ,p_attribute11_value => p_rec.attribute11
725 ,p_attribute12_name => 'ATTRIBUTE12'
726 ,p_attribute12_value => p_rec.attribute12
727 ,p_attribute13_name => 'ATTRIBUTE13'
728 ,p_attribute13_value => p_rec.attribute13
729 ,p_attribute14_name => 'ATTRIBUTE14'
730 ,p_attribute14_value => p_rec.attribute14
731 ,p_attribute15_name => 'ATTRIBUTE15'
732 ,p_attribute15_value => p_rec.attribute15
733 ,p_attribute16_name => 'ATTRIBUTE16'
734 ,p_attribute16_value => p_rec.attribute16
735 ,p_attribute17_name => 'ATTRIBUTE17'
736 ,p_attribute17_value => p_rec.attribute17
737 ,p_attribute18_name => 'ATTRIBUTE18'
738 ,p_attribute18_value => p_rec.attribute18
739 ,p_attribute19_name => 'ATTRIBUTE19'
740 ,p_attribute19_value => p_rec.attribute19
741 ,p_attribute20_name => 'ATTRIBUTE20'
742 ,p_attribute20_value => p_rec.attribute20);
743 end if;
744 --
745 hr_utility.set_location(' Leaving:'||l_proc, 20);
746 end chk_df;
747 -- ----------------------------------------------------------------------------
748 -- |-----------------------------< chk_ddf >----------------------------------|
749 -- ----------------------------------------------------------------------------
750 --
751 -- Description:
752 -- Validates all the Developer Descriptive Flexfield values.
753 --
754 -- Prerequisites:
755 -- All other columns have been validated. Must be called as the
756 -- last step from insert_validate and update_validate.
757 --
758 -- In Arguments:
759 -- p_rec
760 --
761 -- Post Success:
762 -- If the Developer Descriptive Flexfield structure column and data values
763 -- are all valid this procedure will end normally and processing will
764 -- continue.
765 --
766 -- Post Failure:
767 -- If the Developer Descriptive Flexfield structure column value or any of
768 -- the data values are invalid then an application error is raised as
769 -- a PL/SQL exception.
770 --
771 -- Access Status:
772 -- Internal Row Handler Use Only.
773 --
774 -- ----------------------------------------------------------------------------
775 procedure chk_ddf
776 (p_rec in per_sub_shd.g_rec_type
777 ) is
778 --
779 l_proc varchar2(72) := g_package || 'chk_ddf';
780 --
781 begin
782 hr_utility.set_location('Entering:'||l_proc,10);
783 --
784 if ((p_rec.subjects_taken_id is not null) and (
785 nvl(per_sub_shd.g_old_rec.sub_information_category, hr_api.g_varchar2) <>
786 nvl(p_rec.sub_information_category, hr_api.g_varchar2) or
787 nvl(per_sub_shd.g_old_rec.sub_information1, hr_api.g_varchar2) <>
788 nvl(p_rec.sub_information1, hr_api.g_varchar2) or
789 nvl(per_sub_shd.g_old_rec.sub_information2, hr_api.g_varchar2) <>
790 nvl(p_rec.sub_information2, hr_api.g_varchar2) or
791 nvl(per_sub_shd.g_old_rec.sub_information3, hr_api.g_varchar2) <>
792 nvl(p_rec.sub_information3, hr_api.g_varchar2) or
793 nvl(per_sub_shd.g_old_rec.sub_information4, hr_api.g_varchar2) <>
794 nvl(p_rec.sub_information4, hr_api.g_varchar2) or
795 nvl(per_sub_shd.g_old_rec.sub_information5, hr_api.g_varchar2) <>
796 nvl(p_rec.sub_information5, hr_api.g_varchar2) or
797 nvl(per_sub_shd.g_old_rec.sub_information6, hr_api.g_varchar2) <>
798 nvl(p_rec.sub_information6, hr_api.g_varchar2) or
799 nvl(per_sub_shd.g_old_rec.sub_information7, hr_api.g_varchar2) <>
800 nvl(p_rec.sub_information7, hr_api.g_varchar2) or
801 nvl(per_sub_shd.g_old_rec.sub_information8, hr_api.g_varchar2) <>
802 nvl(p_rec.sub_information8, hr_api.g_varchar2) or
803 nvl(per_sub_shd.g_old_rec.sub_information9, hr_api.g_varchar2) <>
804 nvl(p_rec.sub_information9, hr_api.g_varchar2) or
805 nvl(per_sub_shd.g_old_rec.sub_information10, hr_api.g_varchar2) <>
806 nvl(p_rec.sub_information10, hr_api.g_varchar2) or
807 nvl(per_sub_shd.g_old_rec.sub_information11, hr_api.g_varchar2) <>
808 nvl(p_rec.sub_information11, hr_api.g_varchar2) or
809 nvl(per_sub_shd.g_old_rec.sub_information12, hr_api.g_varchar2) <>
810 nvl(p_rec.sub_information12, hr_api.g_varchar2) or
811 nvl(per_sub_shd.g_old_rec.sub_information13, hr_api.g_varchar2) <>
812 nvl(p_rec.sub_information13, hr_api.g_varchar2) or
813 nvl(per_sub_shd.g_old_rec.sub_information14, hr_api.g_varchar2) <>
814 nvl(p_rec.sub_information14, hr_api.g_varchar2) or
815 nvl(per_sub_shd.g_old_rec.sub_information15, hr_api.g_varchar2) <>
816 nvl(p_rec.sub_information15, hr_api.g_varchar2) or
817 nvl(per_sub_shd.g_old_rec.sub_information16, hr_api.g_varchar2) <>
818 nvl(p_rec.sub_information16, hr_api.g_varchar2) or
819 nvl(per_sub_shd.g_old_rec.sub_information17, hr_api.g_varchar2) <>
820 nvl(p_rec.sub_information17, hr_api.g_varchar2) or
821 nvl(per_sub_shd.g_old_rec.sub_information18, hr_api.g_varchar2) <>
822 nvl(p_rec.sub_information18, hr_api.g_varchar2) or
823 nvl(per_sub_shd.g_old_rec.sub_information19, hr_api.g_varchar2) <>
824 nvl(p_rec.sub_information19, hr_api.g_varchar2) or
825 nvl(per_sub_shd.g_old_rec.sub_information20, hr_api.g_varchar2) <>
826 nvl(p_rec.sub_information20, hr_api.g_varchar2) ))
827 or (p_rec.subjects_taken_id is null) then
828 --
829 -- Only execute the validation if absolutely necessary:
830 -- a) During update, the structure column value or any
831 -- of the attribute values have actually changed.
832 -- b) During insert.
833 --
834 hr_dflex_utility.ins_or_upd_descflex_attribs
835 (p_appl_short_name => 'PER'
836 ,p_descflex_name => 'Subject Developer DF'
837 ,p_attribute_category => p_rec.sub_INFORMATION_CATEGORY
838 ,p_attribute1_name => 'SUB_INFORMATION1'
839 ,p_attribute1_value => p_rec.sub_information1
840 ,p_attribute2_name => 'SUB_INFORMATION2'
841 ,p_attribute2_value => p_rec.sub_information2
842 ,p_attribute3_name => 'SUB_INFORMATION3'
843 ,p_attribute3_value => p_rec.sub_information3
844 ,p_attribute4_name => 'SUB_INFORMATION4'
845 ,p_attribute4_value => p_rec.sub_information4
846 ,p_attribute5_name => 'SUB_INFORMATION5'
847 ,p_attribute5_value => p_rec.sub_information5
848 ,p_attribute6_name => 'SUB_INFORMATION6'
849 ,p_attribute6_value => p_rec.sub_information6
850 ,p_attribute7_name => 'SUB_INFORMATION7'
851 ,p_attribute7_value => p_rec.sub_information7
852 ,p_attribute8_name => 'SUB_INFORMATION8'
853 ,p_attribute8_value => p_rec.sub_information8
854 ,p_attribute9_name => 'SUB_INFORMATION9'
855 ,p_attribute9_value => p_rec.sub_information9
856 ,p_attribute10_name => 'SUB_INFORMATION10'
857 ,p_attribute10_value => p_rec.sub_information10
858 ,p_attribute11_name => 'SUB_INFORMATION11'
859 ,p_attribute11_value => p_rec.sub_information11
860 ,p_attribute12_name => 'SUB_INFORMATION12'
861 ,p_attribute12_value => p_rec.sub_information12
862 ,p_attribute13_name => 'SUB_INFORMATION13'
863 ,p_attribute13_value => p_rec.sub_information13
864 ,p_attribute14_name => 'SUB_INFORMATION14'
865 ,p_attribute14_value => p_rec.sub_information14
866 ,p_attribute15_name => 'SUB_INFORMATION15'
867 ,p_attribute15_value => p_rec.sub_information15
868 ,p_attribute16_name => 'SUB_INFORMATION16'
869 ,p_attribute16_value => p_rec.sub_information16
870 ,p_attribute17_name => 'SUB_INFORMATION17'
871 ,p_attribute17_value => p_rec.sub_information17
872 ,p_attribute18_name => 'SUB_INFORMATION18'
873 ,p_attribute18_value => p_rec.sub_information18
874 ,p_attribute19_name => 'SUB_INFORMATION19'
875 ,p_attribute19_value => p_rec.sub_information19
876 ,p_attribute20_name => 'SUB_INFORMATION20'
877 ,p_attribute20_value => p_rec.sub_information20
878 );
879 end if;
880 --
881 hr_utility.set_location(' Leaving:'||l_proc,20);
882 end chk_ddf;
883
884 --
885 -- ----------------------------------------------------------------------------
886 -- |---------------------------< insert_validate >----------------------------|
887 -- ----------------------------------------------------------------------------
888 Procedure insert_validate(p_rec in per_sub_shd.g_rec_type,
889 p_effective_date in date) is
890 --
891 l_proc varchar2(72) := g_package||'insert_validate';
892 --
893 Begin
894 hr_utility.set_location('Entering:'||l_proc, 5);
895 --
896 per_qua_bus.set_security_group_id
897 (
898 p_qualification_id => p_rec.qualification_id);
899 --
900 hr_utility.set_location('Entering:'||l_proc, 7);
901 --
902 -- Call all supporting business operations
903 --
904 -- Business Rule Mapping
905 -- ---------------------
906 -- CHK_SUBJECTS_TAKEN_ID
907 chk_subjects_taken_id
908 (p_subjects_taken_id => p_rec.subjects_taken_id,
909 p_object_version_number => p_rec.object_version_number);
910 --
911 -- Business Rule Mapping
912 -- ---------------------
913 -- CHK_QUALIFICATION_ID
914 chk_qualification_id
915 (p_subjects_taken_id => p_rec.subjects_taken_id,
916 p_qualification_id => p_rec.qualification_id,
917 p_object_version_number => p_rec.object_version_number);
918
919 -- Bug Fix 3267372.
920 -- Validation of subject start date and end date against
921 -- qyualification start date and end date is relaxed.
922 ---
923 /*
924 --
925 -- Business Rule Mapping
926 -- ---------------------
927 -- CHK_START_DATE
928 chk_start_date
929 (p_subjects_taken_id => p_rec.subjects_taken_id,
930 p_qualification_id => p_rec.qualification_id,
931 p_start_date => p_rec.start_date,
932 p_end_date => p_rec.end_date,
933 p_object_version_number => p_rec.object_version_number);
934
935 --
936 -- Business Rule Mapping
937 -- ---------------------
938 -- CHK_END_DATE
939 chk_end_date
940 (p_subjects_taken_id => p_rec.subjects_taken_id,
941 p_qualification_id => p_rec.qualification_id,
942 p_start_date => p_rec.start_date,
943 p_end_date => p_rec.end_date,
944 p_object_version_number => p_rec.object_version_number);
945
946 */
947 ---
948 --
949 -- Business Rule Mapping
950 -- ---------------------
951 -- CHK_MAJOR
952 chk_major
953 (p_subjects_taken_id => p_rec.subjects_taken_id,
954 p_major => p_rec.major,
955 p_effective_date => p_effective_date,
956 p_object_version_number => p_rec.object_version_number);
957 --
958 -- Business Rule Mapping
959 -- ---------------------
960 -- CHK_SUBJECT_STATUS
961 chk_subject_status
962 (p_subjects_taken_id => p_rec.subjects_taken_id,
963 p_subject_status => p_rec.subject_status,
964 p_effective_date => p_effective_date,
965 p_object_version_number => p_rec.object_version_number);
966 --
967 -- Business Rule Mapping
968 -- ---------------------
969 -- CHK_SUBJECT
970 chk_subject
971 (p_subjects_taken_id => p_rec.subjects_taken_id,
972 p_subject => p_rec.subject,
973 p_start_date => p_rec.start_date,
974 p_end_date => p_rec.end_date,
975 p_qualification_id => p_rec.qualification_id,
976 p_effective_date => p_effective_date,
977 p_object_version_number => p_rec.object_version_number);
978 --
979 /*
980 -- Descriptive Flex Check
981 -- ----------------------
982 IF hr_general.get_calling_context <>FORMS' THEN
983 per_sub_flex.df(p_rec => p_rec);
984 END IF;
985 */
986
987 --
988 -- call descriptive flexfield validation routines
989 --
990 per_sub_bus.chk_df(p_rec => p_rec);
991 --
992 per_sub_bus.chk_ddf(p_rec => p_rec);
993 --
994 hr_utility.set_location(' Leaving:'||l_proc, 10);
995 End insert_validate;
996 --
997 -- ----------------------------------------------------------------------------
998 -- |---------------------------< update_validate >----------------------------|
999 -- ----------------------------------------------------------------------------
1000 Procedure update_validate(p_rec in per_sub_shd.g_rec_type,
1001 p_effective_date in date) is
1002 --
1003 l_proc varchar2(72) := g_package||'update_validate';
1004 --
1005 Begin
1006 hr_utility.set_location('Entering:'||l_proc, 5);
1007 --
1008 per_qua_bus.set_security_group_id
1009 (
1010 p_qualification_id => p_rec.qualification_id);
1011 --
1012 hr_utility.set_location('Entering:'||l_proc, 7);
1013 --
1014 -- Call all supporting business operations
1015 --
1016 -- Business Rule Mapping
1017 -- ---------------------
1018 -- CHK_SUBJECTS_TAKEN_ID
1019 chk_subjects_taken_id
1020 (p_subjects_taken_id => p_rec.subjects_taken_id,
1021 p_object_version_number => p_rec.object_version_number);
1022 --
1023 -- Business Rule Mapping
1024 -- ---------------------
1025 -- CHK_QUALIFICATION_ID
1026 chk_qualification_id
1027 (p_subjects_taken_id => p_rec.subjects_taken_id,
1028 p_qualification_id => p_rec.qualification_id,
1029 p_object_version_number => p_rec.object_version_number);
1030
1031 -- Bug Fix 3267372.
1032 -- Validation of subject start date and end date against
1033 -- qualification start date and end date is relaxed is relaxed.
1034 /*
1035
1036 --
1037 -- Business Rule Mapping
1038 -- ---------------------
1039 -- CHK_START_DATE
1040 chk_start_date
1041 (p_subjects_taken_id => p_rec.subjects_taken_id,
1042 p_qualification_id => p_rec.qualification_id,
1043 p_start_date => p_rec.start_date,
1044 p_end_date => p_rec.end_date,
1045 p_object_version_number => p_rec.object_version_number);
1046 --
1047 --
1048 -- Business Rule Mapping
1049 -- ---------------------
1050 -- CHK_END_DATE
1051 chk_end_date
1052 (p_subjects_taken_id => p_rec.subjects_taken_id,
1053 p_qualification_id => p_rec.qualification_id,
1054 p_start_date => p_rec.start_date,
1055 p_end_date => p_rec.end_date,
1056 p_object_version_number => p_rec.object_version_number);*/
1057 --
1058 -- Business Rule Mapping
1059 -- ---------------------
1060 -- CHK_MAJOR
1061 chk_major
1062 (p_subjects_taken_id => p_rec.subjects_taken_id,
1063 p_major => p_rec.major,
1064 p_effective_date => p_effective_date,
1065 p_object_version_number => p_rec.object_version_number);
1066 --
1067 -- Business Rule Mapping
1068 -- ---------------------
1069 -- CHK_SUBJECT_STATUS
1070 chk_subject_status
1071 (p_subjects_taken_id => p_rec.subjects_taken_id,
1072 p_subject_status => p_rec.subject_status,
1073 p_effective_date => p_effective_date,
1074 p_object_version_number => p_rec.object_version_number);
1075 --
1076 -- Business Rule Mapping
1077 -- ---------------------
1078 -- CHK_SUBJECT
1079 chk_subject
1080 (p_subjects_taken_id => p_rec.subjects_taken_id,
1081 p_subject => p_rec.subject,
1082 p_start_date => p_rec.start_date,
1083 p_end_date => p_rec.end_date,
1084 p_qualification_id => p_rec.qualification_id,
1085 p_effective_date => p_effective_date,
1086 p_object_version_number => p_rec.object_version_number);
1087 --
1088 /*
1089 -- Descriptive Flex Check
1090 -- ----------------------
1091 IF hr_general.get_calling_context <>FORMS' THEN
1092 per_sub_flex.df(p_rec => p_rec);
1093 END IF;
1094 */
1095 --
1096 --
1097 -- call descriptive flexfield validation routines
1098 --
1099 per_sub_bus.chk_df(p_rec => p_rec);
1100 --
1101 per_sub_bus.chk_ddf(p_rec => p_rec);
1102 --
1103 hr_utility.set_location(' Leaving:'||l_proc, 10);
1104 End update_validate;
1105 --
1106 -- ----------------------------------------------------------------------------
1107 -- |---------------------------< delete_validate >----------------------------|
1108 -- ----------------------------------------------------------------------------
1109 Procedure delete_validate(p_rec in per_sub_shd.g_rec_type) is
1110 --
1111 l_proc varchar2(72) := g_package||'delete_validate';
1112 --
1113 Begin
1114 hr_utility.set_location('Entering:'||l_proc, 5);
1115 --
1116 -- Call all supporting business operations
1117 --
1118 hr_utility.set_location(' Leaving:'||l_proc, 10);
1119 End delete_validate;
1120 --
1121 end per_sub_bus;