[Home] [Help]
PACKAGE BODY: APPS.OTA_CRE_BUS
Source
1 Package Body ota_cre_bus as
2 /* $Header: otcrerhi.pkb 120.8 2006/02/01 15:02 cmora noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_cre_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_cert_enrollment_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_cert_enrollment_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 cursor csr_sec_grp is
28 select pbg.security_group_id,
29 pbg.legislation_code
30 from per_business_groups_perf pbg
31 , ota_cert_enrollments cre
32 where cre.cert_enrollment_id = p_cert_enrollment_id
33 and pbg.business_group_id = cre.business_group_id;
34 --
35 -- Declare local variables
36 --
37 l_security_group_id number;
38 l_proc varchar2(72) := g_package||'set_security_group_id';
39 l_legislation_code varchar2(150);
40 --
41 begin
42 --
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
48 (p_api_name => l_proc
49 ,p_argument => 'cert_enrollment_id'
50 ,p_argument_value => p_cert_enrollment_id
51 );
52 --
53 open csr_sec_grp;
54 fetch csr_sec_grp into l_security_group_id
55 , l_legislation_code;
56 --
57 if csr_sec_grp%notfound then
58 --
59 close csr_sec_grp;
60 --
61 -- The primary key is invalid therefore we must error
62 --
63 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
64 hr_multi_message.add
65 (p_associated_column1
66 => nvl(p_associated_column1,'CERT_ENROLLMENT_ID')
67 );
68 --
69 else
70 close csr_sec_grp;
71 --
72 -- Set the security_group_id in CLIENT_INFO
73 --
74 hr_api.set_security_group_id
75 (p_security_group_id => l_security_group_id
76 );
77 --
78 -- Set the sessions legislation context in HR_SESSION_DATA
79 --
80 hr_api.set_legislation_context(l_legislation_code);
81 end if;
82 --
83 hr_utility.set_location(' Leaving:'|| l_proc, 20);
84 --
85 end set_security_group_id;
86 --
87 -- ---------------------------------------------------------------------------
88 -- |---------------------< return_legislation_code >-------------------------|
89 -- ---------------------------------------------------------------------------
90 --
91 Function return_legislation_code
92 (p_cert_enrollment_id in number
93 )
94 Return Varchar2 Is
95 --
96 -- Declare cursor
97 --
98 cursor csr_leg_code is
99 select pbg.legislation_code
100 from per_business_groups_perf pbg
101 , ota_cert_enrollments cre
102 where cre.cert_enrollment_id = p_cert_enrollment_id
103 and pbg.business_group_id = cre.business_group_id;
104 --
105 -- Declare local variables
106 --
107 l_legislation_code varchar2(150);
108 l_proc varchar2(72) := g_package||'return_legislation_code';
109 --
110 Begin
111 --
112 hr_utility.set_location('Entering:'|| l_proc, 10);
113 --
114 -- Ensure that all the mandatory parameter are not null
115 --
116 hr_api.mandatory_arg_error
117 (p_api_name => l_proc
118 ,p_argument => 'cert_enrollment_id'
119 ,p_argument_value => p_cert_enrollment_id
120 );
121 --
122 if ( nvl(ota_cre_bus.g_cert_enrollment_id, hr_api.g_number)
123 = p_cert_enrollment_id) then
124 --
125 -- The legislation code has already been found with a previous
126 -- call to this function. Just return the value in the global
127 -- variable.
128 --
129 l_legislation_code := ota_cre_bus.g_legislation_code;
130 hr_utility.set_location(l_proc, 20);
131 else
132 --
133 -- The ID is different to the last call to this function
134 -- or this is the first call to this function.
135 --
136 open csr_leg_code;
137 fetch csr_leg_code into l_legislation_code;
138 --
139 if csr_leg_code%notfound then
140 --
141 -- The primary key is invalid therefore we must error
142 --
143 close csr_leg_code;
144 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
145 fnd_message.raise_error;
146 end if;
147 hr_utility.set_location(l_proc,30);
148 --
149 -- Set the global variables so the values are
150 -- available for the next call to this function.
151 --
152 close csr_leg_code;
153 ota_cre_bus.g_cert_enrollment_id := p_cert_enrollment_id;
154 ota_cre_bus.g_legislation_code := l_legislation_code;
155 end if;
156 hr_utility.set_location(' Leaving:'|| l_proc, 40);
157 return l_legislation_code;
158 end return_legislation_code;
159 --
160 -- ----------------------------------------------------------------------------
161 -- |------------------------------< chk_df >----------------------------------|
162 -- ----------------------------------------------------------------------------
163 --
164 -- Description:
165 -- Validates all the Descriptive Flexfield values.
166 --
167 -- Prerequisites:
168 -- All other columns have been validated. Must be called as the
169 -- last step from insert_validate and update_validate.
170 --
171 -- In Arguments:
172 -- p_rec
173 --
174 -- Post Success:
175 -- If the Descriptive Flexfield structure column and data values are
176 -- all valid this procedure will end normally and processing will
177 -- continue.
178 --
179 -- Post Failure:
180 -- If the Descriptive Flexfield structure column value or any of
181 -- the data values are invalid then an application error is raised as
182 -- a PL/SQL exception.
183 --
184 -- Access Status:
185 -- Internal Row Handler Use Only.
186 --
187 -- ----------------------------------------------------------------------------
188 procedure chk_df
189 (p_rec in ota_cre_shd.g_rec_type
190 ) is
191 --
192 l_proc varchar2(72) := g_package || 'chk_df';
193 --
194 begin
195 hr_utility.set_location('Entering:'||l_proc,10);
196 --
197 if ((p_rec.cert_enrollment_id is not null) and (
198 nvl(ota_cre_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
199 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
200 nvl(ota_cre_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
201 nvl(p_rec.attribute1, hr_api.g_varchar2) or
202 nvl(ota_cre_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
203 nvl(p_rec.attribute2, hr_api.g_varchar2) or
204 nvl(ota_cre_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
205 nvl(p_rec.attribute3, hr_api.g_varchar2) or
206 nvl(ota_cre_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
207 nvl(p_rec.attribute4, hr_api.g_varchar2) or
208 nvl(ota_cre_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
209 nvl(p_rec.attribute5, hr_api.g_varchar2) or
210 nvl(ota_cre_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
211 nvl(p_rec.attribute6, hr_api.g_varchar2) or
212 nvl(ota_cre_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
213 nvl(p_rec.attribute7, hr_api.g_varchar2) or
214 nvl(ota_cre_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
215 nvl(p_rec.attribute8, hr_api.g_varchar2) or
216 nvl(ota_cre_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
217 nvl(p_rec.attribute9, hr_api.g_varchar2) or
218 nvl(ota_cre_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
219 nvl(p_rec.attribute10, hr_api.g_varchar2) or
220 nvl(ota_cre_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
221 nvl(p_rec.attribute11, hr_api.g_varchar2) or
222 nvl(ota_cre_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
223 nvl(p_rec.attribute12, hr_api.g_varchar2) or
224 nvl(ota_cre_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
225 nvl(p_rec.attribute13, hr_api.g_varchar2) or
226 nvl(ota_cre_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
227 nvl(p_rec.attribute14, hr_api.g_varchar2) or
228 nvl(ota_cre_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
229 nvl(p_rec.attribute15, hr_api.g_varchar2) or
230 nvl(ota_cre_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
231 nvl(p_rec.attribute16, hr_api.g_varchar2) or
232 nvl(ota_cre_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
233 nvl(p_rec.attribute17, hr_api.g_varchar2) or
234 nvl(ota_cre_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
235 nvl(p_rec.attribute18, hr_api.g_varchar2) or
236 nvl(ota_cre_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
237 nvl(p_rec.attribute19, hr_api.g_varchar2) or
238 nvl(ota_cre_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
239 nvl(p_rec.attribute20, hr_api.g_varchar2) ))
240 or (p_rec.cert_enrollment_id is null) then
241 --
242 -- Only execute the validation if absolutely necessary:
243 -- a) During update, the structure column value or any
244 -- of the attribute values have actually changed.
245 -- b) During insert.
246 --
247 hr_dflex_utility.ins_or_upd_descflex_attribs
248 (p_appl_short_name => 'OTA'
249 ,p_descflex_name => 'OTA_CERT_ENROLLMENTS'
250 ,p_attribute_category => p_rec.attribute_category
251 ,p_attribute1_name => 'ATTRIBUTE1'
252 ,p_attribute1_value => p_rec.attribute1
253 ,p_attribute2_name => 'ATTRIBUTE2'
254 ,p_attribute2_value => p_rec.attribute2
255 ,p_attribute3_name => 'ATTRIBUTE3'
256 ,p_attribute3_value => p_rec.attribute3
257 ,p_attribute4_name => 'ATTRIBUTE4'
258 ,p_attribute4_value => p_rec.attribute4
259 ,p_attribute5_name => 'ATTRIBUTE5'
260 ,p_attribute5_value => p_rec.attribute5
261 ,p_attribute6_name => 'ATTRIBUTE6'
262 ,p_attribute6_value => p_rec.attribute6
263 ,p_attribute7_name => 'ATTRIBUTE7'
264 ,p_attribute7_value => p_rec.attribute7
265 ,p_attribute8_name => 'ATTRIBUTE8'
266 ,p_attribute8_value => p_rec.attribute8
267 ,p_attribute9_name => 'ATTRIBUTE9'
268 ,p_attribute9_value => p_rec.attribute9
269 ,p_attribute10_name => 'ATTRIBUTE10'
270 ,p_attribute10_value => p_rec.attribute10
271 ,p_attribute11_name => 'ATTRIBUTE11'
272 ,p_attribute11_value => p_rec.attribute11
273 ,p_attribute12_name => 'ATTRIBUTE12'
274 ,p_attribute12_value => p_rec.attribute12
275 ,p_attribute13_name => 'ATTRIBUTE13'
276 ,p_attribute13_value => p_rec.attribute13
277 ,p_attribute14_name => 'ATTRIBUTE14'
278 ,p_attribute14_value => p_rec.attribute14
279 ,p_attribute15_name => 'ATTRIBUTE15'
280 ,p_attribute15_value => p_rec.attribute15
281 ,p_attribute16_name => 'ATTRIBUTE16'
282 ,p_attribute16_value => p_rec.attribute16
283 ,p_attribute17_name => 'ATTRIBUTE17'
284 ,p_attribute17_value => p_rec.attribute17
285 ,p_attribute18_name => 'ATTRIBUTE18'
286 ,p_attribute18_value => p_rec.attribute18
287 ,p_attribute19_name => 'ATTRIBUTE19'
288 ,p_attribute19_value => p_rec.attribute19
289 ,p_attribute20_name => 'ATTRIBUTE20'
290 ,p_attribute20_value => p_rec.attribute20
291 );
292 end if;
293 --
294 hr_utility.set_location(' Leaving:'||l_proc,20);
295 end chk_df;
296 --
297 -- ----------------------------------------------------------------------------
298 -- |-----------------------< chk_non_updateable_args >------------------------|
299 -- ----------------------------------------------------------------------------
300 -- {Start Of Comments}
301 --
302 -- Description:
303 -- This procedure is used to ensure that non updateable attributes have
304 -- not been updated. If an attribute has been updated an error is generated.
305 --
306 -- Pre Conditions:
307 -- g_old_rec has been populated with details of the values currently in
308 -- the database.
309 --
310 -- In Arguments:
311 -- p_rec has been populated with the updated values the user would like the
312 -- record set to.
313 --
314 -- Post Success:
315 -- Processing continues if all the non updateable attributes have not
316 -- changed.
317 --
318 -- Post Failure:
319 -- An application error is raised if any of the non updatable attributes
320 -- have been altered.
321 --
322 -- {End Of Comments}
323 -- ----------------------------------------------------------------------------
324 Procedure chk_non_updateable_args
325 (p_effective_date in date
326 ,p_rec in ota_cre_shd.g_rec_type
327 ) IS
328 --
329 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
330 --
331 Begin
332 --
333 -- Only proceed with the validation if a row exists for the current
334 -- record in the HR Schema.
335 --
336 IF NOT ota_cre_shd.api_updating
337 (p_cert_enrollment_id => p_rec.cert_enrollment_id
338 ,p_object_version_number => p_rec.object_version_number
339 ) THEN
340 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
341 fnd_message.set_token('PROCEDURE ', l_proc);
342 fnd_message.set_token('STEP ', '5');
343 fnd_message.raise_error;
344 END IF;
345 --
346 -- Add checks to ensure non-updateable args have
347 -- not been updated.
348 --
349 End chk_non_updateable_args;
350 --
351 -- ---------------------------------------------------------------------------
352 -- |-------------------------< chk_person_contact >---------------------------|
353 -- ---------------------------------------------------------------------------
354 Procedure chk_person_contact (p_person_id ota_cert_enrollments.person_id%TYPE,
355 p_contact_id ota_cert_enrollments.contact_id%TYPE)
356 is
357 --
358 l_proc varchar2(72) := g_package||'chk_person_contact';
359
360 --
361 Begin
362 hr_utility.set_location('Entering:'||l_proc, 5);
363 IF ( p_person_id IS NULL AND p_contact_id IS NULL) OR
364 ( p_person_id IS NOT NULL AND p_contact_id IS NOT NULL) THEN
365 fnd_message.set_name('OTA', 'OTA_13077_TPE_PRSN_OR_CNTCT');
366 fnd_message.raise_error;
367 END IF;
368 hr_utility.set_location('Leaving:'||l_proc, 10);
369 End chk_person_contact;
370
371 -- ----------------------------------------------------------------------------
372 -- |----------------------<chk_person_id>-------------------------------------|
373 -- ----------------------------------------------------------------------------
374 --
375 PROCEDURE chk_person_id
376 (p_effective_date IN date
377 ,p_person_id IN ota_training_plans.person_id%TYPE
378 ) IS
379 --
380 l_exists varchar2(1);
381 l_proc varchar2(72) := g_package|| 'chk_person_id';
382 --
383 CURSOR csr_person_id IS
384 SELECT null
385 FROM PER_ALL_PEOPLE_F
386 WHERE person_id = p_person_id;
387 BEGIN
388 --
389 hr_utility.set_location(' Step:'|| l_proc, 20);
390
391 IF p_person_id IS NOT NULL THEN
392 OPEN csr_person_id;
393 FETCH csr_person_id INTO l_exists;
394 IF csr_person_id%NOTFOUND THEN
395 CLOSE csr_person_id;
396 hr_utility.set_location(' Step:'|| l_proc, 40);
397 fnd_message.set_name('OTA', 'OTA_13884_NHS_PERSON_INVALID');
398 fnd_message.raise_error;
399 ELSE
400 CLOSE csr_person_id;
401 END IF;
402 END IF;
403 --
404 hr_utility.set_location(' Leaving:'||l_proc, 50);
405
406 --MULTI MESSAGE SUPPORT
407 EXCEPTION
408
409 WHEN app_exception.application_exception THEN
413 RAISE;
410 IF hr_multi_message.exception_add(
411 p_associated_column1 => 'OTA_CERT_ENROLLMENTS.PERSON_ID') THEN
412 hr_utility.set_location(' Leaving:'||l_proc, 52);
414
415 END IF;
416 hr_utility.set_location(' Leaving:'||l_proc, 55);
417
418 END chk_person_id;
419
420 --
421 -- ----------------------------------------------------------------------------
422 -- |----------------------<chk_certification_status_code >-----------------------|
423 -- ----------------------------------------------------------------------------
424 --
425 PROCEDURE chk_certification_status_code
426 (p_effective_date IN date
427 ,p_certification_status_code IN ota_cert_enrollments.CERTIFICATION_STATUS_CODE%TYPE
428 ) IS
429 --
430 l_proc varchar2(72) := g_package|| 'chk_certification_status_code';
431 --
432 BEGIN
433 --
434 -- check mandatory parameters have been set
435 --
436 hr_utility.set_location(' Step:'|| l_proc, 10);
437 hr_api.mandatory_arg_error
438 (p_api_name => l_proc
439 ,p_argument => 'p_certification_status_code'
440 ,p_argument_value => p_certification_status_code
441 );
442 --
443 hr_utility.set_location(' Step:'|| l_proc, 20);
444 IF hr_api.not_exists_in_hr_lookups
445 (p_effective_date => p_effective_date
446 ,p_lookup_type => 'OTA_CERT_ENROLL_STATUS'
447 ,p_lookup_code => p_certification_status_code
448 ) THEN
449 -- Error, lookup not available
450 fnd_message.set_name('OTA', 'OTA_443665_CRE_STAT_INVALID');
451 fnd_message.raise_error;
452 END IF;
453 --
454 hr_utility.set_location(' Leaving:'||l_proc, 30);
455
456 --MULTI MESSAGE SUPPORT
457 EXCEPTION
458 WHEN app_exception.application_exception THEN
459 IF hr_multi_message.exception_add(
460 p_associated_column1 => 'OTA_CERT_ENROLLMENTS.CERTIFICATION_STATUS_CODE') THEN
461 hr_utility.set_location(' Leaving:'||l_proc, 32);
462 RAISE;
463
464 END IF;
465 hr_utility.set_location(' Leaving:'||l_proc, 35);
466
467 END chk_certification_status_code;
468
469 --
470 -- ----------------------------------------------------------------------------
471 -- |----------------------<chk_del_cert_enrollment_id >-------------------------|
472 -- ----------------------------------------------------------------------------
473 --
474 PROCEDURE chk_del_cert_enrollment_id
475 (p_cert_enrollment_id IN ota_cert_enrollments.cert_enrollment_id%TYPE)
476 IS
477 --
478 l_exists varchar2(1);
479 l_proc varchar2(72) := g_package|| 'chk_del_cert_enrollment_id';
480 l_cert_enroll_status varchar2(30);
481
482 CURSOR csr_cert_prd_enr_id IS
483 SELECT null
484 FROM ota_cert_prd_enrollments cpe
485 WHERE cpe.cert_enrollment_id = p_cert_enrollment_id;
486
487 CURSOR csr_cert_enr_status is
488 SELECT certification_status_code
489 FROM ota_cert_enrollments
490 WHERE cert_enrollment_id = p_cert_enrollment_id;
491
492
493 --
494 BEGIN
495 --
496 -- check mandatory parameters have been set
497 --
498 hr_utility.set_location(' Step:'|| l_proc, 10);
499 hr_api.mandatory_arg_error
500 (p_api_name => l_proc
501 ,p_argument => 'p_cert_enrollment_id'
502 ,p_argument_value => p_cert_enrollment_id
503 );
504 --
505 -- Check that the cert_enrollment can be deleted
506 --
507 OPEN csr_cert_prd_enr_id;
508 FETCH csr_cert_prd_enr_id INTO l_exists;
509 IF csr_cert_prd_enr_id%FOUND THEN
510 CLOSE csr_cert_prd_enr_id;
511 hr_utility.set_location(' Step:'|| l_proc, 20);
512 fnd_message.set_name('OTA', 'OTA_443818_NO_DEL_HAS_CHILD');
513 FND_MESSAGE.SET_TOKEN ('ENTITY_NAME', 'Certification Period Enrollment');
514 fnd_message.raise_error;
515 ELSE
516 CLOSE csr_cert_prd_enr_id;
517 END IF;
518
519 OPEN csr_cert_enr_status;
520 FETCH csr_cert_enr_status INTO l_cert_enroll_status;
521 IF csr_cert_enr_status%FOUND THEN
522 CLOSE csr_cert_enr_status;
523 IF l_cert_enroll_status= 'AWAITING_APPROVAL' then
524 hr_utility.set_location(' Step:'|| l_proc, 30);
525 fnd_message.set_name('OTA', 'OTA_443920_CRT_NO_DEL_APV_WAIT');
526 fnd_message.raise_error;
527 END IF;
528 ELSE
529 CLOSE csr_cert_enr_status;
530 END IF;
531
532 hr_utility.set_location(' Leaving:'||l_proc, 40);
533
534 END chk_del_cert_enrollment_id;
535
536 --
537 --
538 --
539 -- ----------------------------------------------------------------------------
540 -- |----------------------<check_duplicate_subscription >-------------------------|
541 -- ----------------------------------------------------------------------------
542 --
543 PROCEDURE check_duplicate_subscription
544 ( p_certification_id IN ota_cert_enrollments.certification_id%TYPE
545 ,p_contact_id IN ota_cert_enrollments.contact_id%TYPE default NULL
546 ,p_person_id IN ota_cert_enrollments.person_id%TYPE default NULL)
547 IS
548 --
549 l_exists varchar2(1);
550 l_proc varchar2(72) := g_package|| 'check_duplicate_subscription';
551
552 CURSOR csr_cert_enr_id IS
553 SELECT null
554 FROM ota_cert_enrollments cre
555 WHERE cre.certification_id = p_certification_id
556 AND ( cre.person_id = p_person_id and cre.contact_id IS NULL
557 OR cre.contact_id = p_contact_id and cre.person_id IS NULL)
561 SELECT meaning
558 AND cre.certification_status_code <> 'CANCELLED';
559
560 CURSOR csr_get_object_type IS
562 FROM hr_lookups
563 WHERE lookup_type = 'OTA_OBJECT_TYPE'
564 and lookup_code = 'CRT';
565
566 l_person_name per_all_people_f.full_name%TYPE;
567 l_object_type varchar2(240);
568
569 --
570 BEGIN
571 --
572 -- check mandatory parameters have been set
573 --
574 hr_utility.set_location(' Step:'|| l_proc, 10);
575 hr_api.mandatory_arg_error
576 (p_api_name => l_proc
577 ,p_argument => 'p_certification_id'
578 ,p_argument_value => p_certification_id
579 );
580 --
581 -- check if learner is already subscribed
582 --
583 OPEN csr_cert_enr_id;
584 FETCH csr_cert_enr_id INTO l_exists;
585 IF csr_cert_enr_id%FOUND THEN
586 CLOSE csr_cert_enr_id;
587
588 OPEN csr_get_object_type;
589 FETCH csr_get_object_type INTO l_object_type;
590 CLOSE csr_get_object_type;
591
592 l_person_name := ota_utility.get_learner_name(
593 p_person_id => p_person_id
594 ,p_customer_id => NULL
595 ,p_contact_id => p_contact_id);
596
597 hr_utility.set_location(' Step:'|| l_proc, 10);
598 fnd_message.set_name('OTA', 'OTA_443908_LRNR_DUPL_SUBSC_ERR');
599 fnd_message.set_token('LEARNER_NAME', l_person_name);
600 fnd_message.set_token('OBJECT_TYPE', l_object_type);
601 fnd_message.raise_error;
602 ELSE
603 CLOSE csr_cert_enr_id;
604 END IF;
605
606
607 hr_utility.set_location(' Leaving:'||l_proc, 20);
608
609 END check_duplicate_subscription;
610
611 -- ----------------------------------------------------------------------------
612 -- |----------------------<check_due_date_passed >-------------------------|
613 -- ----------------------------------------------------------------------------
614 -- Removed this check for renewal based certifications to allow for
615 -- new employees to subscribe to ongoing date based certs.
616 --
617 PROCEDURE check_due_date_passed
618 ( p_certification_id IN ota_cert_enrollments.certification_id%TYPE)
619 IS
620 --
621 l_exists varchar2(1);
622 l_proc varchar2(72) := g_package|| 'check_due_date_passed';
623
624 CURSOR csr_crt IS
625 select
626 b.INITIAL_COMPLETION_DATE
627 from ota_certifications_b b
628 where certification_id = p_certification_id
629 and renewable_flag = 'N';
630
631 rec_crt csr_crt%rowtype;
632
633 --
634 BEGIN
635 --
636 -- check mandatory parameters have been set
637 --
638 hr_utility.set_location(' Step:'|| l_proc, 10);
639 hr_api.mandatory_arg_error
640 (p_api_name => l_proc
641 ,p_argument => 'p_certification_id'
642 ,p_argument_value => p_certification_id
643 );
644 --
645 -- check if the due date is passed already
646 --
647
648 OPEN csr_crt;
649 FETCH csr_crt INTO rec_crt;
650 CLOSE csr_crt;
651
652 if (rec_crt.initial_completion_date is not null and trunc(sysdate) > rec_crt.initial_completion_date) then
653 --throw error for subscriptions beyond the due date.
654 hr_utility.set_location(' Step:'|| l_proc, 20);
655 fnd_message.set_name('OTA', 'OTA_443958_CRT_DUE_DATE_PASSED');
656 FND_MESSAGE.SET_TOKEN ('DUE_DATE', rec_crt.INITIAL_COMPLETION_DATE);
657 fnd_message.raise_error;
658 end if;
659
660 hr_utility.set_location(' Leaving:'||l_proc, 20);
661
662 END check_due_date_passed;
663 -- ----------------------------------------------------------------------------
664 -- |---------------------------< insert_validate >----------------------------|
665 -- ----------------------------------------------------------------------------
666 Procedure insert_validate
667 (p_effective_date in date
668 ,p_rec in ota_cre_shd.g_rec_type
669 ) is
670 --
671 l_proc varchar2(72) := g_package||'insert_validate';
672 --
673 Begin
674 hr_utility.set_location('Entering:'||l_proc, 5);
675 --
676 -- Call all supporting business operations
677 --
678 -- Validate Important Attributes
679 hr_api.validate_bus_grp_id
680 (p_business_group_id => p_rec.business_group_id
681 ,p_associated_column1 => ota_cre_shd.g_tab_nam
682 || '.BUSINESS_GROUP_ID');
683 --
684 -- After validating the set of important attributes,
685 -- if Multiple Message detection is enabled and at least
686 -- one error has been found then abort further validation.
687 --
688 hr_multi_message.end_validation_set;
689 --
690 -- Validate Dependent Attributes
691 --
692 --
693 chk_person_contact(p_person_id => p_rec.person_id,
694 p_contact_id => p_rec.contact_id);
695
696 chk_person_id(p_effective_date => p_effective_date,
697 p_person_id => p_rec.person_id);
698
699 check_duplicate_subscription( p_person_id => p_rec.person_id
700 ,p_contact_id => p_rec.contact_id
701 ,p_certification_id => p_rec.certification_id);
702 --chk_path_status_code(p_effective_date => p_effective_date,
703 -- p_path_status_code => p_rec.path_status_code);
704
705 chk_certification_status_code(p_effective_date => p_effective_date,
706 p_certification_status_code => p_rec.certification_status_code);
707 --
711 --
708 check_due_date_passed(p_certification_id => p_rec.certification_id);
709 --
710 ota_cre_bus.chk_df(p_rec);
712 hr_utility.set_location(' Leaving:'||l_proc, 10);
713 End insert_validate;
714 --
715 -- ----------------------------------------------------------------------------
716 -- |---------------------------< update_validate >----------------------------|
717 -- ----------------------------------------------------------------------------
718 Procedure update_validate
719 (p_effective_date in date
720 ,p_rec in ota_cre_shd.g_rec_type
721 ) is
722 --
723 l_proc varchar2(72) := g_package||'update_validate';
724 --
725 Begin
726 hr_utility.set_location('Entering:'||l_proc, 5);
727 --
728 -- Call all supporting business operations
729 --
730 -- Validate Important Attributes
731 hr_api.validate_bus_grp_id
732 (p_business_group_id => p_rec.business_group_id
733 ,p_associated_column1 => ota_cre_shd.g_tab_nam
734 || '.BUSINESS_GROUP_ID');
735 --
736 -- After validating the set of important attributes,
737 -- if Multiple Message detection is enabled and at least
738 -- one error has been found then abort further validation.
739 --
740 hr_multi_message.end_validation_set;
741 --
742 -- Validate Dependent Attributes
743 --
744 chk_non_updateable_args
745 (p_effective_date => p_effective_date
746 ,p_rec => p_rec
747 );
748 --
749 chk_person_contact(p_person_id => p_rec.person_id,
750 p_contact_id => p_rec.contact_id);
751
752 chk_person_id(p_effective_date => p_effective_date,
753 p_person_id => p_rec.person_id);
754
755 --chk_path_status_code(p_effective_date => p_effective_date,
756 -- p_path_status_code => p_rec.path_status_code);
757
758 chk_certification_status_code(p_effective_date => p_effective_date,
759 p_certification_status_code => p_rec.certification_status_code);
760 --
761 --
762 ota_cre_bus.chk_df(p_rec);
763 --
764 hr_utility.set_location(' Leaving:'||l_proc, 10);
765 End update_validate;
766 --
767 -- ----------------------------------------------------------------------------
768 -- |---------------------------< delete_validate >----------------------------|
769 -- ----------------------------------------------------------------------------
770 Procedure delete_validate
771 (p_rec in ota_cre_shd.g_rec_type
772 ) is
773 --
774 l_proc varchar2(72) := g_package||'delete_validate';
775 --
776 Begin
777 hr_utility.set_location('Entering:'||l_proc, 5);
778 --
779 -- Call all supporting business operations
780
781 chk_del_cert_enrollment_id(p_cert_enrollment_id => p_rec.cert_enrollment_id);
782
783 --
784 hr_utility.set_location(' Leaving:'||l_proc, 10);
785 End delete_validate;
786 --
787 end ota_cre_bus;