[Home] [Help]
PACKAGE BODY: APPS.OTA_CRT_BUS
Source
1 Package Body ota_crt_bus as
2 /* $Header: otcrtrhi.pkb 120.14 2006/03/17 14:54 cmora noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_crt_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_certification_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_certification_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_certifications_b crt
32 where crt.certification_id = p_certification_id
33 and pbg.business_group_id = crt.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 => 'certification_id'
50 ,p_argument_value => p_certification_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,'CERTIFICATION_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_certification_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_certifications_b crt
102 where crt.certification_id = p_certification_id
103 and pbg.business_group_id = crt.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 => 'certification_id'
119 ,p_argument_value => p_certification_id
120 );
121 --
122 if ( nvl(ota_crt_bus.g_certification_id, hr_api.g_number)
123 = p_certification_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_crt_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_crt_bus.g_certification_id := p_certification_id;
154 ota_crt_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_crt_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.certification_id is not null) and (
198 nvl(ota_crt_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
199 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
200 nvl(ota_crt_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
201 nvl(p_rec.attribute1, hr_api.g_varchar2) or
202 nvl(ota_crt_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
203 nvl(p_rec.attribute2, hr_api.g_varchar2) or
204 nvl(ota_crt_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
205 nvl(p_rec.attribute3, hr_api.g_varchar2) or
206 nvl(ota_crt_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
207 nvl(p_rec.attribute4, hr_api.g_varchar2) or
208 nvl(ota_crt_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
209 nvl(p_rec.attribute5, hr_api.g_varchar2) or
210 nvl(ota_crt_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
211 nvl(p_rec.attribute6, hr_api.g_varchar2) or
212 nvl(ota_crt_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
213 nvl(p_rec.attribute7, hr_api.g_varchar2) or
214 nvl(ota_crt_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
215 nvl(p_rec.attribute8, hr_api.g_varchar2) or
216 nvl(ota_crt_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
217 nvl(p_rec.attribute9, hr_api.g_varchar2) or
218 nvl(ota_crt_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
219 nvl(p_rec.attribute10, hr_api.g_varchar2) or
220 nvl(ota_crt_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
221 nvl(p_rec.attribute11, hr_api.g_varchar2) or
222 nvl(ota_crt_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
223 nvl(p_rec.attribute12, hr_api.g_varchar2) or
224 nvl(ota_crt_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
225 nvl(p_rec.attribute13, hr_api.g_varchar2) or
226 nvl(ota_crt_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
227 nvl(p_rec.attribute14, hr_api.g_varchar2) or
228 nvl(ota_crt_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
229 nvl(p_rec.attribute15, hr_api.g_varchar2) or
230 nvl(ota_crt_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
231 nvl(p_rec.attribute16, hr_api.g_varchar2) or
232 nvl(ota_crt_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
233 nvl(p_rec.attribute17, hr_api.g_varchar2) or
234 nvl(ota_crt_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
235 nvl(p_rec.attribute18, hr_api.g_varchar2) or
236 nvl(ota_crt_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
237 nvl(p_rec.attribute19, hr_api.g_varchar2) or
238 nvl(ota_crt_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
239 nvl(p_rec.attribute20, hr_api.g_varchar2) ))
240 or (p_rec.certification_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_CERTIFICATIONS'
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_crt_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.
338 ,p_object_version_number => p_rec.object_version_number
335 --
336 IF NOT ota_crt_shd.api_updating
337 (p_certification_id => p_rec.certification_id
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 hr_utility.set_location(' Step:'|| l_proc, 10);
347 IF NVL(p_rec.business_group_id, hr_api.g_number) <>
348 NVL(ota_crt_shd.g_old_rec.business_group_id, hr_api.g_number) THEN
349 hr_api.argument_changed_error
350 (p_api_name => l_proc
351 ,p_argument => 'BUSINESS_GROUP_ID'
352 ,p_base_table => ota_crt_shd.g_tab_nam);
353 END IF;
354 --
355 hr_utility.set_location(' Step:'|| l_proc, 20);
356 IF NVL(p_rec.certification_id, hr_api.g_number) <>
357 NVL(ota_crt_shd.g_old_rec.certification_id, hr_api.g_number) THEN
358 hr_api.argument_changed_error
359 (p_api_name => l_proc
360 ,p_argument => 'CERTIFICATION_ID'
361 ,p_base_table => ota_crt_shd.g_tab_nam);
362 END IF;
363 --
364 EXCEPTION
365
366 WHEN OTHERS THEN
367 RAISE;
368
369 End chk_non_updateable_args;
370
371 PROCEDURE chk_should_warn
372 (p_certification_id IN ota_certifications_b.certification_id%TYPE
373 , p_initial_completion_date IN ota_certifications_b.initial_completion_date%TYPE
374 , p_initial_completion_duration IN ota_certifications_b.initial_completion_duration%TYPE
375 , p_renewal_duration IN ota_certifications_b.renewal_duration%TYPE
376 , p_validity_duration IN ota_certifications_b.validity_duration%TYPE
377 , p_validity_start_type IN ota_certifications_b.validity_start_type%TYPE
378 , p_renewable_flag IN ota_certifications_b.renewable_flag%TYPE
379 , p_return_status OUT NOCOPY VARCHAR2)
380 IS
381 --
382
383
384 CURSOR csr_old_cert is
385 SELECT
386 c.initial_completion_date,
387 c.initial_completion_duration,
388 c.renewal_duration,
389 c.validity_duration,
390 c.validity_start_type,
391 c.renewable_flag
392 FROM ota_certifications_b c
393 WHERE certification_id = p_certification_id;
394
395 l_proc VARCHAR2(72) := g_package|| 'chk_should_warn';
396
397 l_old_init_compl_date ota_certifications_b.initial_completion_date%TYPE;
398 l_old_init_compl_duration ota_certifications_b.initial_completion_duration%TYPE;
399 l_old_renew_duration ota_certifications_b.renewal_duration%TYPE;
400 l_old_valid_duration ota_certifications_b.validity_duration%TYPE;
401 l_old_start_type ota_certifications_b.validity_start_type%TYPE;
402 l_old_renew_flag ota_certifications_b.renewable_flag%TYPE;
403
404 l_init_compl_date_changed BOOLEAN;
405 l_init_compl_dur_changed BOOLEAN;
406 l_renew_dur_changed BOOLEAN;
407 l_valid_dur_changed BOOLEAN;
408 l_start_type_changed BOOLEAN;
409 l_renew_flag_changed BOOLEAN;
410
411 --
412 Begin
413 hr_utility.set_location('Entering:'||l_proc, 5);
414 --
415
416 OPEN csr_old_cert;
417 FETCH csr_old_cert into l_old_init_compl_date, l_old_init_compl_duration,
418 l_old_renew_duration, l_old_valid_duration, l_old_start_type, l_old_renew_flag;
419 CLOSE csr_old_cert;
420
421 l_init_compl_date_changed := ota_general.value_changed(l_old_init_compl_date,
422 p_initial_completion_date);
423
424 l_init_compl_dur_changed := ota_general.value_changed(l_old_init_compl_duration,
425 p_initial_completion_duration);
426
427 l_renew_dur_changed := ota_general.value_changed(l_old_renew_duration,
428 p_renewal_duration);
429
430 l_valid_dur_changed := ota_general.value_changed(l_old_valid_duration,
431 p_validity_duration);
432
433 l_start_type_changed := ota_general.value_changed(l_old_start_type,
434 p_validity_start_type);
435
436 l_renew_flag_changed := ota_general.value_changed(l_old_renew_flag,
437 p_renewable_flag);
438
439 p_return_status := 'S';
440
441 /*
442 if l_init_compl_date_changed OR l_init_compl_dur_changed
443 OR l_renew_dur_changed OR l_valid_dur_changed
444 OR l_start_type_changed OR l_renew_flag_changed
445 */
446 if l_renew_dur_changed OR l_valid_dur_changed OR l_start_type_changed
447 then
448 p_return_status := 'E';
449 end if;
450
451 hr_utility.set_location(' Leaving:'||l_proc, 10);
452
453 End chk_should_warn;
454 --
455 --
456 -- ----------------------------------------------------------------------------
457 -- |---------------------------< chk_renewable_cert >-------------------------|
458 -- ----------------------------------------------------------------------------
459 PROCEDURE chk_renewable_cert
460 (p_renewable_flag IN ota_certifications_b.renewable_flag%TYPE
461 ,p_validity_duration IN ota_certifications_b.validity_duration%TYPE
462 ,p_certification_id IN ota_certifications_b.certification_id%type
463 ) IS
464 --
465 l_proc VARCHAR2(72) := g_package|| 'chk_renewable_cert';
466 --
467
468 BEGIN
469
470 IF p_renewable_flag = 'Y' AND p_validity_duration is null
471 THEN
472 hr_utility.set_location(' Step:'|| l_proc, 60);
476
473 fnd_message.set_name('OTA', 'OTA_443778_CERT_VALIDITY_NULL');
474 fnd_message.raise_error;
475 END IF;
477 hr_utility.set_location(' Leaving:'||l_proc, 90);
478
479 EXCEPTION
480
481 WHEN app_exception.application_exception THEN
482
483 IF hr_multi_message.exception_add
484 (p_associated_column1 => 'OTA_CERTIFICATIONS_B.VALIDITY_DURATION') THEN
485
486 hr_utility.set_location(' Leaving:'||l_proc, 92);
487 RAISE;
488
489 END IF;
490
491 hr_utility.set_location(' Leaving:'||l_proc, 94);
492
493 END chk_renewable_cert;
494
495 -- ----------------------------------------------------------------------------
496 -- |---------------------------< chk_renew_duration >-------------------------|
497 -- ----------------------------------------------------------------------------
498 PROCEDURE chk_renew_duration
499 (p_validity_duration IN ota_certifications_b.validity_duration%TYPE
500 ,p_renewal_duration IN ota_certifications_b.renewal_duration%TYPE
501 ) IS
502 --
503 l_proc VARCHAR2(72) := g_package|| 'chk_renew_duration';
504 --
505
506 BEGIN
507
508 IF p_validity_duration is not null AND p_renewal_duration is not null
509 AND p_validity_duration < p_renewal_duration
510 THEN
511 hr_utility.set_location(' Step:'|| l_proc, 60);
512 fnd_message.set_name('OTA', 'OTA_443777_CERT_RENEW');
513 fnd_message.raise_error;
514 END IF;
515
516 hr_utility.set_location(' Leaving:'||l_proc, 90);
517
518 EXCEPTION
519
520 WHEN app_exception.application_exception THEN
521
522 IF hr_multi_message.exception_add
523 (p_associated_column1 => 'OTA_CERTIFICATIONS_B.VALIDITY_DURATION') THEN
524
525 hr_utility.set_location(' Leaving:'||l_proc, 92);
526 RAISE;
527
528 END IF;
529
530 hr_utility.set_location(' Leaving:'||l_proc, 94);
531
532 END chk_renew_duration;
533
534 -- ----------------------------------------------------------------------------
535 -- |---------------------------< chk_init_completion >-------------------------|
536 -- ----------------------------------------------------------------------------
537 PROCEDURE chk_init_completion
538 (p_effective_date in date
539 ,p_init_compl_date IN ota_certifications_b.initial_completion_date%TYPE
540 ,p_init_compl_duration IN ota_certifications_b.initial_completion_duration%TYPE
541 ,p_start_date_active IN ota_certifications_b.start_date_active%type
542 ,p_end_date_active IN ota_certifications_b.end_date_active%type
543 ) IS
544 --
545 l_proc VARCHAR2(72) := g_package|| 'chk_init_completion';
546 l_current_year number;
547 l_max_value number;
548 --
549
550 BEGIN
551
552 IF p_init_compl_date is null AND p_init_compl_duration is null
553 THEN
554 hr_utility.set_location(' Step:'|| l_proc, 60);
555 fnd_message.set_name('OTA', 'OTA_443775_CERT_INIT_COMPL_NUL');
556 fnd_message.raise_error;
557 END IF;
558
559 IF p_init_compl_date is not null AND p_init_compl_duration is not null
560 THEN
561 hr_utility.set_location(' Step:'|| l_proc, 60);
562 fnd_message.set_name('OTA', 'OTA_443774_CERT_INIT_COMPL');
563 fnd_message.raise_error;
564 END IF;
565
566 if p_init_compl_date is not null
567 then
568 if p_start_date_active > p_init_compl_date
569 then
570 hr_utility.set_location(' Step:'|| l_proc, 60);
571 fnd_message.set_name('OTA', 'OTA_443953_CRT_INIT_COMP_ERROR');
572 fnd_message.raise_error;
573 Elsif p_end_date_active is not null and p_end_date_active < p_init_compl_date
574 then
575 hr_utility.set_location(' Step:'|| l_proc, 60);
576 fnd_message.set_name('OTA', 'OTA_443953_CRT_INIT_COMP_ERROR');
577 fnd_message.raise_error;
578 end if;
579 end if;
580
581 if p_init_compl_duration is not null
582 then
583 --l_current_year := EXTRACT(YEAR FROM sysdate);
584 l_current_year := EXTRACT(YEAR FROM p_effective_date);
585 --l_max_value := (4712 - l_current_year) * 365;
586 if p_init_compl_duration > 9999
587 then
588 hr_utility.set_location(' Step:'|| l_proc, 60);
589 fnd_message.set_name('OTA', 'OTA_443956_EXCEED_MAX_VALUE');
590 fnd_message.set_token('MAX_VALUE', 9999);
591 fnd_message.raise_error;
592 end if;
593 end if;
594
595
596
597 hr_utility.set_location(' Leaving:'||l_proc, 90);
598
599 EXCEPTION
600
601 WHEN app_exception.application_exception THEN
602
603 IF hr_multi_message.exception_add
604 (p_associated_column1 => 'OTA_CERTIFICATIONS_B.initial_completion_duration'
605 ,p_associated_column2 => 'OTA_CERTIFICATIONS_B.initial_completion_date') THEN
606
607 hr_utility.set_location(' Leaving:'||l_proc, 92);
608 RAISE;
609
610 END IF;
611
612 hr_utility.set_location(' Leaving:'||l_proc, 94);
613
614 END chk_init_completion;
615
616 -- ----------------------------------------------------------------------------
617 -- |---------------------------< chk_date_range >-------------------------|
618 -- ----------------------------------------------------------------------------
619 PROCEDURE chk_date_range
620 (p_start_date IN ota_certifications_b.start_date_active%TYPE
621 ,p_end_date IN ota_certifications_b.end_date_active%type
625 l_proc VARCHAR2(72) := g_package|| 'chk_date_range';
622 ,p_init_compl_date IN ota_certifications_b.initial_completion_date%type
623 ) IS
624 --
626 --
627
628 BEGIN
629
630 IF p_start_date is not null and p_end_date is not null and p_start_date > p_end_date
631 THEN
632 hr_utility.set_location(' Step:'|| l_proc, 60);
633 fnd_message.set_name('OTA', 'OTA_13312_GEN_DATE_ORDER');
634 fnd_message.raise_error;
635 END IF;
636
637 IF p_start_date is not null and p_init_compl_date is not null and p_start_date > p_init_compl_date
638 THEN
639 hr_utility.set_location(' Step:'|| l_proc, 60);
640 fnd_message.set_name('OTA', 'OTA_443771_CERT_INIT_CMPL_DATE');
641 fnd_message.raise_error;
642 END IF;
643
644
645 hr_utility.set_location(' Leaving:'||l_proc, 90);
646
647 EXCEPTION
648
649 WHEN app_exception.application_exception THEN
650
651 IF hr_multi_message.exception_add
652 (p_associated_column1 => 'OTA_CERTIFICATIONS_B.start_date_active') THEN
653
654 hr_utility.set_location(' Leaving:'||l_proc, 92);
655 RAISE;
656
657 END IF;
658
659 hr_utility.set_location(' Leaving:'||l_proc, 94);
660
661 END chk_date_range;
662 -- ----------------------------------------------------------------------------
663 -- |---------------------------< chk_notify_days >-------------------------|
664 -- ----------------------------------------------------------------------------
665 PROCEDURE chk_notify_days
666 (p_effective_date IN date
667 ,p_notify_days IN ota_certifications_b.notify_days_before_expire%type
668 ,p_initial_completion_duration IN ota_certifications_b.initial_completion_duration%type
669 ,p_initial_completion_date IN ota_certifications_b.initial_completion_date%type
670 ,p_validity_duration IN ota_certifications_b.validity_duration%type
671 ,p_certification_id IN ota_certifications_b.certification_id%type
672 ) IS
673 --
674 l_proc VARCHAR2(72) := g_package|| 'chk_notify_days';
675 l_max_value number;
676 --
677
678 BEGIN
679
680 IF p_notify_days is not null
681 then
682 if p_initial_completion_duration is not null and p_notify_days > p_initial_completion_duration
683 then
684 hr_utility.set_location(' Step:'|| l_proc, 60);
685 fnd_message.set_name('OTA', 'OTA_443957_CRT_NTF_DAY_EXCEEDS');
686 fnd_message.raise_error;
687 elsif p_initial_completion_date is not null
688 then
689 if p_initial_completion_date >= trunc(p_effective_date) then
690 l_max_value := trunc(p_initial_completion_date - p_effective_date);
691 end if;
692
693 --bypass notify days check for onetime older certs with no susbcrs
694
695 if l_max_value is not null then
696 if p_validity_duration is not null and p_validity_duration < l_max_value
697 then
698 l_max_value := p_validity_duration;
699 end if;
700 elsif p_validity_duration is not null then
701 l_max_value := p_validity_duration;
702 end if;
703
704 if p_notify_days > l_max_value
705 then
706 hr_utility.set_location(' Step:'|| l_proc, 60);
707 fnd_message.set_name('OTA', 'OTA_443956_EXCEED_MAX_VALUE');
708 fnd_message.set_token('MAX_VALUE', l_max_value);
709 fnd_message.raise_error;
710 end if;
711 end if;
712
713 end if;
714
715 hr_utility.set_location(' Leaving:'||l_proc, 90);
716
717 EXCEPTION
718
719 WHEN app_exception.application_exception THEN
720
721 IF hr_multi_message.exception_add
722 (p_associated_column1 => 'OTA_CERTIFICATIONS_B.notify_days_before_expire') THEN
723
724 hr_utility.set_location(' Leaving:'||l_proc, 92);
725 RAISE;
726
727 END IF;
728
729 hr_utility.set_location(' Leaving:'||l_proc, 94);
730
731 END chk_notify_days;
732
733 -- ----------------------------------------------------------------------------
734 -- |---------------------------< chk_init_compl_date >-------------------------|
735 -- ----------------------------------------------------------------------------
736 PROCEDURE chk_init_compl_date
737 (p_effective_date IN date
738 ,p_init_compl_date IN ota_certifications_b.initial_completion_date%type
739 ,p_certification_id IN ota_certifications_b.certification_id%type
740 ) IS
741 --
742 l_proc VARCHAR2(72) := g_package|| 'chk_init_compl_date';
743
744 --
745
746 BEGIN
747 --Bug#4570505
748 -- IF p_init_compl_date is not null and p_init_compl_date < sysdate
749 -- IF p_init_compl_date is not null and p_init_compl_date < trunc(sysdate)
750 IF p_init_compl_date is not null and p_init_compl_date < trunc(p_effective_date)
751 THEN
752 hr_utility.set_location(' Step:'|| l_proc, 60);
753 fnd_message.set_name('OTA', 'OTA_443771_CERT_INIT_CMPL_DATE');
754 fnd_message.raise_error;
755 END IF;
756
757 hr_utility.set_location(' Leaving:'||l_proc, 90);
758
759 EXCEPTION
760
761 WHEN app_exception.application_exception THEN
762
763 IF hr_multi_message.exception_add
764 (p_associated_column1 => 'OTA_CERTIFICATIONS_B.initial_completion_date') THEN
765
766 hr_utility.set_location(' Leaving:'||l_proc, 92);
767 RAISE;
768
769 END IF;
770
771 hr_utility.set_location(' Leaving:'||l_proc, 94);
772
773 END chk_init_compl_date;
774
778 PROCEDURE chk_validity_duration
775 -- ----------------------------------------------------------------------------
776 -- |---------------------------< chk_validity_duration >-------------------------|
777 -- ----------------------------------------------------------------------------
779 (p_validity_duration IN ota_certifications_b.validity_duration%type
780 ) IS
781 --
782 l_proc VARCHAR2(72) := g_package|| 'chk_validity_duration';
783 --
784
785 BEGIN
786
787 IF p_validity_duration is not null and p_validity_duration > 9999
788 THEN
789 hr_utility.set_location(' Step:'|| l_proc, 60);
790 fnd_message.set_name('OTA', 'OTA_443956_EXCEED_MAX_VALUE');
791 fnd_message.set_token('MAX_VALUE', 9999);
792 fnd_message.raise_error;
793 END IF;
794
795 hr_utility.set_location(' Leaving:'||l_proc, 90);
796
797 EXCEPTION
798
799 WHEN app_exception.application_exception THEN
800
801 IF hr_multi_message.exception_add
802 (p_associated_column1 => 'OTA_CERTIFICATIONS_B.validity_duration') THEN
803
804 hr_utility.set_location(' Leaving:'||l_proc, 92);
805 RAISE;
806
807 END IF;
808
809 hr_utility.set_location(' Leaving:'||l_proc, 94);
810
811 END chk_validity_duration;
812 -- ----------------------------------------------------------------------------
813 -- |---------------------------< chk_date_based_cert >-------------------------|
814 -- ----------------------------------------------------------------------------
815 PROCEDURE chk_date_based_cert
816 (p_init_compl_date IN ota_certifications_b.initial_completion_date%type
817 ,p_renewable_flag IN ota_certifications_b.renewable_flag%TYPE
818 ,p_renewal_duration IN ota_certifications_b.renewal_duration%TYPE
819 ,p_validity_start_type IN ota_certifications_b.validity_start_type%TYPE
820 ) IS
821 --
822 l_proc VARCHAR2(72) := g_package|| 'chk_date_based_cert';
823 --
824
825 BEGIN
826
827 IF p_init_compl_date is not null and p_renewable_flag = 'Y'
828 and p_renewal_duration is null
829 THEN
830 hr_utility.set_location(' Step:'|| l_proc, 60);
831 fnd_message.set_name('OTA', 'OTA_443772_CERT_DATE_BASED');
832 fnd_message.raise_error;
833 END IF;
834
835 IF p_init_compl_date is not null AND p_renewable_flag = 'Y'
836 and p_validity_start_type = 'A'
837 THEN
838 hr_utility.set_location(' Step:'|| l_proc, 60);
839 fnd_message.set_name('OTA', 'OTA_443773_CERT_VALIDITY_START');
840 fnd_message.raise_error;
841 END IF;
842 hr_utility.set_location(' Leaving:'||l_proc, 90);
843
844 EXCEPTION
845
846 WHEN app_exception.application_exception THEN
847
848 IF hr_multi_message.exception_add
849 (p_associated_column1 => 'OTA_CERTIFICATIONS_B.validity_start_type') THEN
850
851 hr_utility.set_location(' Leaving:'||l_proc, 92);
852 RAISE;
853
854 END IF;
855
856 hr_utility.set_location(' Leaving:'||l_proc, 94);
857
858 END chk_date_based_cert;
859 --
860 -- ----------------------------------------------------------------------------
861 -- |-------------------------< chk_enr_exists >-----------------------------|
862 -- ----------------------------------------------------------------------------
863 --
864 -- PUBLIC
865 -- Description:
866 -- Delete Validation.
867 -- This certification may not be deleted if child rows in
868 -- ota_cert_enrollments exist.
869 --
870 Procedure chk_enr_exists
871 (
872 p_certification_id in number
873 ) is
874 --
875 l_exists varchar2(1);
876 l_proc varchar2(72) := g_package||'chk_enr_exists';
877 --
878 cursor sel_enr_exists is
879 select 'Y'
880 from ota_cert_enrollments cre
881 where cre.certification_id = p_certification_id;
882 --
883 Begin
884
885 hr_utility.set_location('Entering:'|| l_proc, 10);
886
887 Open sel_enr_exists;
888 fetch sel_enr_exists into l_exists;
889
890 if sel_enr_exists%found then
891 close sel_enr_exists;
892 hr_utility.set_location(' Step:'|| l_proc, 20);
893 fnd_message.set_name('OTA', 'OTA_443762_CERT_ENROLL_EXISTS');
894 fnd_message.raise_error;
895 else
896 close sel_enr_exists;
897 end if;
898
899 hr_utility.set_location(' Leaving:'|| l_proc, 30);
900 --
901 End chk_enr_exists;
902
903 -- ----------------------------------------------------------------------------
904 -- |-------------------------< chk_enr_dates >-----------------------------|
905 -- ----------------------------------------------------------------------------
906 --
907 -- PUBLIC
908 -- Description:
909 -- Update Validation.
910 -- The certification cannot start after or end before existing subscription dates.
911 --
912 Procedure chk_enr_dates
913 (
914 p_certification_id in number
915 ,p_start_date_active in date
916 ,p_end_date_active in date
917 ) is
918 --
919 l_min_date date;
920 l_max_date date;
921 l_proc varchar2(72) := g_package||'chk_enr_dates';
922 --
923 --
924 Begin
925
926 hr_utility.set_location('Entering:'|| l_proc, 10);
927
928 select min(cre.enrollment_date), max(cre.enrollment_date)
929 into l_min_date, l_max_date
930 from ota_cert_enrollments cre
931 where cre.certification_id = p_certification_id;
932
936 hr_utility.set_location(' Step:'|| l_proc, 20);
933 if ((l_min_date is not null and l_min_date < p_start_date_active)
934 or (l_max_date is not null and p_end_date_active is not null and l_max_date > p_end_date_active))
935 then
937 fnd_message.set_name('OTA', 'OTA_443960_CRT_ENRL_DATE_INVAL');
938 fnd_message.raise_error;
939 end if;
940
941 hr_utility.set_location(' Leaving:'|| l_proc, 30);
942
943 Exception
944 when app_exception.application_exception then
945 IF hr_multi_message.exception_add
946 (p_associated_column1 => 'OTA_CERTIFICATIONS_B.START_DATE_ACTIVE'
947 ,p_associated_column2 => 'OTA_CERTIFICATIONS_B.END_DATE_ACTIVE'
948 ) THEN
949 raise;
950 END IF;
951
952 --
953 --
954 End chk_enr_dates;
955
956 -- ----------------------------------------------------------------------------
957 -- |---------------------------< chk_cmb_dates >-------------------------|
958 -- ----------------------------------------------------------------------------
959 PROCEDURE chk_cmb_dates
960 (p_start_date IN ota_certifications_b.start_date_active%TYPE
961 ,p_end_date IN ota_certifications_b.end_date_active%type
962 ,p_cert_id IN ota_certifications_b.certification_id%type
963 ) IS
964 --
965 CURSOR csr_has_cmb is
966 SELECT 'Y'
967 FROM ota_certification_members m
968 where m.certification_id = p_cert_id;
969
970 CURSOR csr_cmb_dates is
971 SELECT
972 min(m.start_date_active) earliest_start, max(m.end_date_active) latest_end
973 FROM ota_certification_members m
974 WHERE m.certification_id = p_cert_id;
975
976 l_proc VARCHAR2(72) := g_package|| 'chk_cmb_dates';
977 l_has_cmb varchar2(1);
978 l_earliest_start_date date;
979 l_latest_end_date date;
980 l_cert_start_date date;
981 l_cert_end_date date;
982 --
983
984 BEGIN
985 open csr_has_cmb;
986 fetch csr_has_cmb into l_has_cmb;
987 close csr_has_cmb;
988
989 if l_has_cmb = 'Y'
990 then
991
992 Open csr_cmb_dates;
993 fetch csr_cmb_dates into l_earliest_start_date, l_latest_end_date;
994 close csr_cmb_dates;
995
996 if l_earliest_start_date is null then
997 l_earliest_start_date := hr_api.g_sot;
998 end if;
999
1000 if l_latest_end_date is null then
1001 l_latest_end_date := hr_api.g_eot;
1002 end if;
1003
1004 --
1005 l_cert_start_date := p_start_date;
1006 l_cert_end_date := p_end_date;
1007 --
1008 if l_cert_end_date is null then
1009 l_cert_end_date := hr_api.g_eot;
1010 end if;
1011
1012 if l_earliest_start_date < l_cert_start_date or
1013 l_earliest_start_date > l_cert_end_date or
1014 l_latest_end_date > l_cert_end_date or
1015 l_latest_end_date < l_cert_start_date then
1016
1017 fnd_message.set_name('OTA','OTA_443936_CRT_DATE_OUT_OF_CMB');
1018 fnd_message.raise_error;
1019 end if;
1020 end if;
1021 --
1022 hr_utility.set_location(' Leaving:' || l_proc,10);
1023
1024 Exception
1025 when app_exception.application_exception then
1026 IF hr_multi_message.exception_add
1027 (p_associated_column1 => 'OTA_CERTIFICATIONS_B.START_DATE_ACTIVE'
1028 ,p_associated_column2 => 'OTA_CERTIFICATIONS_B.END_DATE_ACTIVE'
1029 ) THEN
1030 raise;
1031 END IF;
1032
1033 --
1034
1035 END chk_cmb_dates;
1036
1037 PROCEDURE chk_upd_exis_subscr
1038 (p_certification_id IN ota_certifications_b.certification_id%TYPE
1039 , p_initial_completion_date IN ota_certifications_b.initial_completion_date%TYPE
1040 , p_initial_completion_duration IN ota_certifications_b.initial_completion_duration%TYPE
1041 , p_renewable_flag IN ota_certifications_b.renewable_flag%TYPE
1042 , p_notify_days IN ota_certifications_b.notify_days_before_expire%type)
1043 IS
1044 --
1045
1046 CURSOR csr_exis_subscr
1047 IS
1048 SELECT 'Y'
1049 FROM OTA_CERT_ENROLLMENTS
1050 where certification_id = p_certification_id;
1051
1052 l_subscr_exists varchar2(1);
1053
1054
1055 l_proc VARCHAR2(72) := g_package|| 'chk_upd_exis_subscr';
1056
1057 l_init_compl_date_changed BOOLEAN;
1058 l_init_compl_dur_changed BOOLEAN;
1059 l_renew_flag_changed BOOLEAN;
1060 l_notify_days_changed BOOLEAN;
1061
1062 Begin
1063 hr_utility.set_location('Entering:'||l_proc, 5);
1064 --
1065 --Bug 4637071 chk for exist subscr and process
1066 OPEN csr_exis_subscr;
1067 FETCH csr_exis_subscr into l_subscr_exists;
1068 CLOSE csr_exis_subscr;
1069
1070 if l_subscr_exists = 'Y' then
1071 l_init_compl_date_changed := ota_general.value_changed(ota_crt_shd.g_old_rec.initial_completion_date,
1072 p_initial_completion_date);
1073
1074 l_init_compl_dur_changed := ota_general.value_changed(ota_crt_shd.g_old_rec.initial_completion_duration,
1075 p_initial_completion_duration);
1076
1077 l_renew_flag_changed := ota_general.value_changed(ota_crt_shd.g_old_rec.renewable_flag,
1078 p_renewable_flag);
1079
1080 l_notify_days_changed := ota_general.value_changed(ota_crt_shd.g_old_rec.notify_days_before_expire,
1081 p_notify_days);
1082
1083 if l_init_compl_date_changed OR l_init_compl_dur_changed
1084 OR l_renew_flag_changed OR l_notify_days_changed
1085 then
1086 fnd_message.set_name('OTA', 'OTA_443962_CRT_NO_UPD_EXIS_SUB');
1090
1087 fnd_message.raise_error;
1088 end if;
1089 end if;
1091 hr_utility.set_location(' Leaving:'||l_proc, 10);
1092
1093 EXCEPTION
1094 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
1095
1096 IF HR_MULTI_MESSAGE.EXCEPTION_ADD
1097 (P_ASSOCIATED_COLUMN1 => null) THEN
1098 HR_UTILITY.SET_LOCATION(' LEAVING:'||L_PROC, 15);
1099 RAISE;
1100 END IF;
1101
1102 HR_UTILITY.SET_LOCATION(' LEAVING:'||L_PROC, 20);
1103
1104 End chk_upd_exis_subscr;
1105
1106 --
1107 --
1108 -- BUG#4654544
1109 -- ----------------------------------------------------------------------------
1110 -- |--------------------------< chk_category_start_end_dates >----------------|
1111 -- ----------------------------------------------------------------------------
1112 --
1113 -- PUBLIC
1114 -- Description:
1115 -- Validates the startdate and enddate with respect to category dates.
1116 --
1117 Procedure chk_category_start_end_dates
1118 (p_certification_id in number
1119 ,p_start_date in date
1120 ,p_end_date in date
1121 ) is
1122 --
1123 -- Declare cursors and local variables
1124 --
1125 -- Cursor to get value if parent category is already exits in child hierarchy of base category
1126
1127 CURSOR csr_cat_start_end_date is
1128 select
1129 ctu.start_date_active,
1130 ctu.end_date_active
1131 from
1132 ota_category_usages ctu,
1133 ota_cert_cat_inclusions cci
1134 where
1135 ctu.category_usage_id = cci.category_usage_id
1136 and cci.primary_flag= 'Y'
1137 and cci.certification_id = p_certification_id ;
1138
1139 --
1140 -- Variables for API Boolean parameters
1141 l_proc varchar2(72) := g_package ||'chk_category_start_end_dates';
1142 l_cat_start_date date;
1143 l_cat_end_date date;
1144
1145
1146 Begin
1147 hr_utility.set_location(' Entering:' || l_proc,10);
1148 --
1149 IF hr_multi_message.no_exclusive_error
1150 (p_check_column1 => 'OTA_CERTIFICATIONS.START_DATE_ACTIVE'
1151 ,p_check_column2 => 'OTA_CERTIFICATIONS.END_DATE_ACTIVE'
1152 ,p_associated_column1 => 'OTA_CERTIFICATIONS.START_DATE_ACTIVE'
1153 ,p_associated_column2 => 'OTA_CERTIFICATIONS.END_DATE_ACTIVE'
1154 ) THEN
1155 --
1156 OPEN csr_cat_start_end_date;
1157 FETCH csr_cat_start_end_date into l_cat_start_date, l_cat_end_date;
1158
1159 IF csr_cat_start_end_date%FOUND THEN
1160 CLOSE csr_cat_start_end_date;
1161
1162 IF ( l_cat_start_date > p_start_date
1163 or nvl(l_cat_end_date,hr_api.g_eot) < nvl(p_end_date,hr_api.g_eot)
1164 ) THEN
1165 --
1166 fnd_message.set_name ( 'OTA','OTA_443896_CRT_OUT_OF_CAT_DATE');
1167 fnd_message.raise_error;
1168 --
1169 End IF;
1170 ELSE
1171 CLOSE csr_cat_start_end_date;
1172
1173 End IF;
1174 End IF;
1175 --
1176 hr_utility.set_location(' Leaving:' || l_proc,10);
1177 Exception
1178 when app_exception.application_exception then
1179 IF hr_multi_message.exception_add
1180 (p_associated_column1 => 'OTA_CERTIFICATIONS.START_DATE_ACTIVE'
1181 ,p_associated_column2 => 'OTA_CERTIFICATIONS.END_DATE_ACTIVE'
1182 ) THEN
1183 hr_utility.set_location(' Leaving:'|| l_proc,20);
1184 raise;
1185 END IF;
1186
1187 hr_utility.set_location(' Leaving:'|| l_proc,30);
1188 --
1189 End chk_category_start_end_dates;
1190
1191 --
1192 --
1193 -- ----------------------------------------------------------------------------
1194 -- |---------------------------< insert_validate >----------------------------|
1195 -- ----------------------------------------------------------------------------
1196 Procedure insert_validate
1197 (p_effective_date in date
1198 ,p_rec in ota_crt_shd.g_rec_type
1199 ) is
1200 --
1201 l_proc varchar2(72) := g_package||'insert_validate';
1202 --
1203 Begin
1204 hr_utility.set_location('Entering:'||l_proc, 5);
1205 --
1206 -- Call all supporting business operations
1207 --
1208 hr_api.validate_bus_grp_id
1209 (p_business_group_id => p_rec.business_group_id
1210 ,p_associated_column1 => ota_crt_shd.g_tab_nam
1211 || '.BUSINESS_GROUP_ID');
1212 --
1213 -- After validating the set of important attributes,
1214 -- if Multiple Message detection is enabled and at least
1215 -- one error has been found then abort further validation.
1216 --
1217 hr_multi_message.end_validation_set;
1218 --
1219 -- Validate Dependent Attributes
1220 --
1221 --
1222
1223 ota_crt_bus.chk_date_based_cert
1224 (p_init_compl_date => p_rec.initial_completion_date
1225 ,p_renewable_flag => p_rec.renewable_flag
1226 ,p_renewal_duration => p_rec.renewal_duration
1227 ,p_validity_start_type => p_rec.validity_start_type
1228 );
1229
1230 ota_crt_bus.chk_init_compl_date
1231 (p_effective_date => p_effective_date
1232 ,p_init_compl_date => p_rec.initial_completion_date
1233 ,p_certification_id => p_rec.certification_id
1234 );
1235
1236 ota_crt_bus.chk_date_range
1237 (p_start_date => p_rec.start_date_active
1238 ,p_end_date => p_rec.end_date_active
1239 ,p_init_compl_date => p_rec.initial_completion_date
1240 );
1241
1242 ota_crt_bus.chk_init_completion
1243 (p_effective_date => p_effective_date
1247 ,p_end_date_active => p_rec.end_date_active
1244 ,p_init_compl_date => p_rec.initial_completion_date
1245 ,p_init_compl_duration => p_rec.initial_completion_duration
1246 ,p_start_date_active => p_rec.start_date_active
1248 );
1249
1250 ota_crt_bus.chk_renew_duration
1251 (p_validity_duration => p_rec.validity_duration
1252 ,p_renewal_duration => p_rec.renewal_duration
1253 );
1254
1255 ota_crt_bus.chk_renewable_cert
1256 (p_renewable_flag => p_rec.renewable_flag
1257 ,p_validity_duration => p_rec.validity_duration
1258 ,p_certification_id => p_rec.certification_id
1259 );
1260
1261 ota_crt_bus.chk_notify_days
1262 (p_effective_date => p_effective_date
1263 ,p_notify_days => p_rec.notify_days_before_expire
1264 ,p_initial_completion_duration => p_rec.initial_completion_duration
1265 ,p_initial_completion_date => p_rec.initial_completion_date
1266 ,p_validity_duration => p_rec.validity_duration
1267 ,p_certification_id => p_rec.certification_id
1268 );
1269
1270 ota_crt_bus.chk_validity_duration
1271 (p_validity_duration => p_rec.validity_duration
1272 );
1273
1274 ota_crt_bus.chk_df(p_rec);
1275 --
1276 hr_multi_message.end_validation_set;
1277 --
1278 --
1279 hr_utility.set_location(' Leaving:'||l_proc, 10);
1280 End insert_validate;
1281 --
1282 -- ----------------------------------------------------------------------------
1283 -- |---------------------------< update_validate >----------------------------|
1284 -- ----------------------------------------------------------------------------
1285 Procedure update_validate
1286 (p_effective_date in date
1287 ,p_rec in ota_crt_shd.g_rec_type
1288 ) is
1289 --
1290 l_proc varchar2(72) := g_package||'update_validate';
1291 --
1292 Begin
1293 hr_utility.set_location('Entering:'||l_proc, 5);
1294 --
1295 -- Call all supporting business operations
1296 --
1297 hr_api.validate_bus_grp_id
1298 (p_business_group_id => p_rec.business_group_id
1299 ,p_associated_column1 => ota_crt_shd.g_tab_nam
1300 || '.BUSINESS_GROUP_ID');
1301 --
1302 -- After validating the set of important attributes,
1303 -- if Multiple Message detection is enabled and at least
1304 -- one error has been found then abort further validation.
1305 --
1306 hr_multi_message.end_validation_set;
1307 --
1308 -- Validate Dependent Attributes
1309 --
1310 chk_non_updateable_args
1311 (p_effective_date => p_effective_date
1312 ,p_rec => p_rec
1313 );
1314 --
1315
1316 ota_crt_bus.chk_upd_exis_subscr
1317 (p_certification_id => p_rec.certification_id
1318 , p_initial_completion_date => p_rec.initial_completion_date
1319 , p_initial_completion_duration => p_rec.initial_completion_duration
1320 , p_renewable_flag => p_rec.renewable_flag
1321 , p_notify_days => p_rec.notify_days_before_expire);
1322
1323 ota_crt_bus.chk_date_based_cert
1324 (p_init_compl_date => p_rec.initial_completion_date
1325 ,p_renewable_flag => p_rec.renewable_flag
1326 ,p_renewal_duration => p_rec.renewal_duration
1327 ,p_validity_start_type => p_rec.validity_start_type
1328 );
1329
1330 ota_crt_bus.chk_date_range
1331 (p_start_date => p_rec.start_date_active
1332 ,p_end_date => p_rec.end_date_active
1333 ,p_init_compl_date => p_rec.initial_completion_date
1334 );
1335
1336 ota_crt_bus.chk_init_completion
1337 (p_effective_date => p_effective_date
1338 ,p_init_compl_date => p_rec.initial_completion_date
1339 ,p_init_compl_duration => p_rec.initial_completion_duration
1340 ,p_start_date_active => p_rec.start_date_active
1341 ,p_end_date_active => p_rec.end_date_active
1342 );
1343
1344 ota_crt_bus.chk_renew_duration
1345 (p_validity_duration => p_rec.validity_duration
1346 ,p_renewal_duration => p_rec.renewal_duration
1347 );
1348
1349 ota_crt_bus.chk_renewable_cert
1350 (p_renewable_flag => p_rec.renewable_flag
1351 ,p_validity_duration => p_rec.validity_duration
1352 ,p_certification_id => p_rec.certification_id);
1353
1354 ota_crt_bus.chk_cmb_dates
1355 (p_start_date => p_rec.start_date_active
1356 ,p_end_date => p_rec.end_date_active
1357 ,p_cert_id => p_rec.certification_id
1358 );
1359
1360 ota_crt_bus.chk_notify_days
1361 (p_effective_date => p_effective_date
1362 ,p_notify_days => p_rec.notify_days_before_expire
1363 ,p_initial_completion_duration => p_rec.initial_completion_duration
1364 ,p_initial_completion_date => p_rec.initial_completion_date
1365 ,p_validity_duration => p_rec.validity_duration
1366 ,p_certification_id => p_rec.certification_id
1367 );
1368
1369 ota_crt_bus.chk_validity_duration
1370 (p_validity_duration => p_rec.validity_duration
1371 );
1372
1373 ota_crt_bus.chk_enr_dates
1374 (
1375 p_certification_id => p_rec.certification_id
1376 ,p_start_date_active => p_rec.start_date_active
1377 ,p_end_date_active => p_rec.end_date_active
1378 );
1379
1380 chk_category_start_end_dates(p_certification_id =>p_rec.certification_id,
1381 p_start_date =>p_rec.start_date_active,
1382 p_end_date =>p_rec.end_date_active );
1383
1384 ota_crt_bus.chk_df(p_rec);
1385 --
1386 hr_multi_message.end_validation_set;
1387 --
1388 hr_utility.set_location(' Leaving:'||l_proc, 10);
1389 End update_validate;
1390 --
1391 -- ----------------------------------------------------------------------------
1392 -- |---------------------------< delete_validate >----------------------------|
1393 -- ----------------------------------------------------------------------------
1394 Procedure delete_validate
1395 (p_rec in ota_crt_shd.g_rec_type
1396 ) is
1397 --
1398 l_proc varchar2(72) := g_package||'delete_validate';
1399 --
1400 Begin
1401 hr_utility.set_location('Entering:'||l_proc, 5);
1402 --
1403 -- Call all supporting business operations
1404 chk_enr_exists(p_certification_id => p_rec.certification_id);
1405 --
1406 hr_utility.set_location(' Leaving:'||l_proc, 10);
1407 End delete_validate;
1408 --
1409 end ota_crt_bus;