[Home] [Help]
PACKAGE BODY: APPS.OTA_CCI_BUS
Source
1 Package Body ota_cci_bus as
2 /* $Header: otccirhi.pkb 120.1 2005/07/21 15:07 estreacy noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_cci_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_category_usage_id number default null;
15 g_certification_id number default null;
16 --
17 -- ---------------------------------------------------------------------------
18 -- |----------------------< set_security_group_id >--------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 Procedure set_security_group_id
22 (p_category_usage_id in number
23 ,p_certification_id in number
24 ,p_associated_column1 in varchar2 default null
25 ,p_associated_column2 in varchar2 default null
26 ) is
27 --
28 -- Declare cursor
29 --
30 cursor csr_sec_grp is
31 select pbg.security_group_id,
32 pbg.legislation_code
33 from per_business_groups_perf pbg
34 , ota_cert_cat_inclusions cci
35 , ota_category_usages ctu
36 where ctu.category_usage_id = p_category_usage_id
37 and cci.category_usage_id = ctu.category_usage_id
38 and cci.certification_id = p_certification_id
39 and pbg.business_group_id = ctu.business_group_id;
40 --
41 -- Declare local variables
42 --
43 l_security_group_id number;
44 l_proc varchar2(72) := g_package||'set_security_group_id';
45 l_legislation_code varchar2(150);
46 --
47 begin
48 --
49 hr_utility.set_location('Entering:'|| l_proc, 10);
50 --
51 -- Ensure that all the mandatory parameter are not null
52 --
53 hr_api.mandatory_arg_error
54 (p_api_name => l_proc
55 ,p_argument => 'category_usage_id'
56 ,p_argument_value => p_category_usage_id
57 );
58 hr_api.mandatory_arg_error
59 (p_api_name => l_proc
60 ,p_argument => 'certification_id'
61 ,p_argument_value => p_certification_id
62 );
63 --
64 open csr_sec_grp;
65 fetch csr_sec_grp into l_security_group_id
66 , l_legislation_code;
67 --
68 if csr_sec_grp%notfound then
69 --
70 close csr_sec_grp;
71 --
72 -- The primary key is invalid therefore we must error
73 --
74 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
75 hr_multi_message.add
76 (p_associated_column1
77 => nvl(p_associated_column1,'CATEGORY_USAGE_ID')
78 ,p_associated_column2
79 => nvl(p_associated_column2,'CERTIFICATION_ID')
80 );
81 --
82 else
83 close csr_sec_grp;
84 --
85 -- Set the security_group_id in CLIENT_INFO
86 --
87 hr_api.set_security_group_id
88 (p_security_group_id => l_security_group_id
89 );
90 --
91 -- Set the sessions legislation context in HR_SESSION_DATA
92 --
93 hr_api.set_legislation_context(l_legislation_code);
94 end if;
95 --
96 hr_utility.set_location(' Leaving:'|| l_proc, 20);
97 --
98 end set_security_group_id;
99 --
100 -- ---------------------------------------------------------------------------
101 -- |---------------------< return_legislation_code >-------------------------|
102 -- ---------------------------------------------------------------------------
103 --
104 Function return_legislation_code
105 (p_category_usage_id in number
106 ,p_certification_id in number
107 )
108 Return Varchar2 Is
109 --
110 -- Declare cursor
111 --
112 cursor csr_leg_code is
113 select pbg.legislation_code
114 from per_business_groups_perf pbg
115 , ota_cert_cat_inclusions cci
116 , ota_category_usages ctu
117 where ctu.category_usage_id = p_category_usage_id
118 and cci.category_usage_id = ctu.category_usage_id
119 and cci.certification_id = p_certification_id
120 and pbg.business_group_id = ctu.business_group_id;
121 --
122 -- Declare local variables
123 --
124 l_legislation_code varchar2(150);
125 l_proc varchar2(72) := g_package||'return_legislation_code';
126 --
127 Begin
128 --
129 hr_utility.set_location('Entering:'|| l_proc, 10);
130 --
131 -- Ensure that all the mandatory parameter are not null
132 --
133 hr_api.mandatory_arg_error
134 (p_api_name => l_proc
135 ,p_argument => 'category_usage_id'
136 ,p_argument_value => p_category_usage_id
137 );
138 hr_api.mandatory_arg_error
139 (p_api_name => l_proc
140 ,p_argument => 'certification_id'
141 ,p_argument_value => p_certification_id
142 );
143 --
144 if (( nvl(ota_cci_bus.g_category_usage_id, hr_api.g_number)
145 = p_category_usage_id)
146 and ( nvl(ota_cci_bus.g_certification_id, hr_api.g_number)
147 = p_certification_id)) then
148 --
149 -- The legislation code has already been found with a previous
150 -- call to this function. Just return the value in the global
151 -- variable.
152 --
153 l_legislation_code := ota_cci_bus.g_legislation_code;
154 hr_utility.set_location(l_proc, 20);
155 else
156 --
157 -- The ID is different to the last call to this function
158 -- or this is the first call to this function.
159 --
160 open csr_leg_code;
161 fetch csr_leg_code into l_legislation_code;
162 --
163 if csr_leg_code%notfound then
164 --
165 -- The primary key is invalid therefore we must error
166 --
167 close csr_leg_code;
168 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
169 fnd_message.raise_error;
170 end if;
171 hr_utility.set_location(l_proc,30);
172 --
173 -- Set the global variables so the values are
174 -- available for the next call to this function.
175 --
176 close csr_leg_code;
177 ota_cci_bus.g_category_usage_id := p_category_usage_id;
178 ota_cci_bus.g_certification_id := p_certification_id;
179 ota_cci_bus.g_legislation_code := l_legislation_code;
180 end if;
181 hr_utility.set_location(' Leaving:'|| l_proc, 40);
182 return l_legislation_code;
183 end return_legislation_code;
184 --
185 -- ----------------------------------------------------------------------------
186 -- |------------------------------< chk_df >----------------------------------|
187 -- ----------------------------------------------------------------------------
188 --
189 -- Description:
190 -- Validates all the Descriptive Flexfield values.
191 --
192 -- Prerequisites:
193 -- All other columns have been validated. Must be called as the
194 -- last step from insert_validate and update_validate.
195 --
196 -- In Arguments:
197 -- p_rec
198 --
199 -- Post Success:
200 -- If the Descriptive Flexfield structure column and data values are
201 -- all valid this procedure will end normally and processing will
202 -- continue.
203 --
204 -- Post Failure:
205 -- If the Descriptive Flexfield structure column value or any of
206 -- the data values are invalid then an application error is raised as
207 -- a PL/SQL exception.
208 --
209 -- Access Status:
210 -- Internal Row Handler Use Only.
211 --
212 -- ----------------------------------------------------------------------------
213 procedure chk_df
214 (p_rec in ota_cci_shd.g_rec_type
215 ) is
216 --
217 l_proc varchar2(72) := g_package || 'chk_df';
218 --
219 begin
220 hr_utility.set_location('Entering:'||l_proc,10);
221 --
222 if ((p_rec.category_usage_id is not null) and
223 (p_rec.certification_id is not null) and (
224 nvl(ota_cci_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
225 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
226 nvl(ota_cci_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
227 nvl(p_rec.attribute1, hr_api.g_varchar2) or
228 nvl(ota_cci_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
229 nvl(p_rec.attribute2, hr_api.g_varchar2) or
230 nvl(ota_cci_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
231 nvl(p_rec.attribute3, hr_api.g_varchar2) or
232 nvl(ota_cci_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
233 nvl(p_rec.attribute4, hr_api.g_varchar2) or
234 nvl(ota_cci_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
235 nvl(p_rec.attribute5, hr_api.g_varchar2) or
236 nvl(ota_cci_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
237 nvl(p_rec.attribute6, hr_api.g_varchar2) or
238 nvl(ota_cci_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
239 nvl(p_rec.attribute7, hr_api.g_varchar2) or
240 nvl(ota_cci_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
241 nvl(p_rec.attribute8, hr_api.g_varchar2) or
242 nvl(ota_cci_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
243 nvl(p_rec.attribute9, hr_api.g_varchar2) or
244 nvl(ota_cci_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
245 nvl(p_rec.attribute10, hr_api.g_varchar2) or
246 nvl(ota_cci_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
247 nvl(p_rec.attribute11, hr_api.g_varchar2) or
248 nvl(ota_cci_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
249 nvl(p_rec.attribute12, hr_api.g_varchar2) or
250 nvl(ota_cci_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
251 nvl(p_rec.attribute13, hr_api.g_varchar2) or
252 nvl(ota_cci_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
253 nvl(p_rec.attribute14, hr_api.g_varchar2) or
254 nvl(ota_cci_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
255 nvl(p_rec.attribute15, hr_api.g_varchar2) or
256 nvl(ota_cci_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
257 nvl(p_rec.attribute16, hr_api.g_varchar2) or
258 nvl(ota_cci_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
259 nvl(p_rec.attribute17, hr_api.g_varchar2) or
260 nvl(ota_cci_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
261 nvl(p_rec.attribute18, hr_api.g_varchar2) or
262 nvl(ota_cci_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
263 nvl(p_rec.attribute19, hr_api.g_varchar2) or
264 nvl(ota_cci_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
265 nvl(p_rec.attribute20, hr_api.g_varchar2) ))
266 or (p_rec.category_usage_id is null) and
267 (p_rec.certification_id is null) then
268 --
269 -- Only execute the validation if absolutely necessary:
270 -- a) During update, the structure column value or any
271 -- of the attribute values have actually changed.
272 -- b) During insert.
273 --
274 hr_dflex_utility.ins_or_upd_descflex_attribs
275 (p_appl_short_name => 'OTA'
276 ,p_descflex_name => 'OTA_CERT_CAT_INCLUSIONS'
277 ,p_attribute_category => p_rec.attribute_category
278 ,p_attribute1_name => 'ATTRIBUTE1'
279 ,p_attribute1_value => p_rec.attribute1
280 ,p_attribute2_name => 'ATTRIBUTE2'
281 ,p_attribute2_value => p_rec.attribute2
282 ,p_attribute3_name => 'ATTRIBUTE3'
283 ,p_attribute3_value => p_rec.attribute3
284 ,p_attribute4_name => 'ATTRIBUTE4'
285 ,p_attribute4_value => p_rec.attribute4
286 ,p_attribute5_name => 'ATTRIBUTE5'
287 ,p_attribute5_value => p_rec.attribute5
288 ,p_attribute6_name => 'ATTRIBUTE6'
289 ,p_attribute6_value => p_rec.attribute6
290 ,p_attribute7_name => 'ATTRIBUTE7'
291 ,p_attribute7_value => p_rec.attribute7
292 ,p_attribute8_name => 'ATTRIBUTE8'
293 ,p_attribute8_value => p_rec.attribute8
294 ,p_attribute9_name => 'ATTRIBUTE9'
295 ,p_attribute9_value => p_rec.attribute9
296 ,p_attribute10_name => 'ATTRIBUTE10'
297 ,p_attribute10_value => p_rec.attribute10
298 ,p_attribute11_name => 'ATTRIBUTE11'
299 ,p_attribute11_value => p_rec.attribute11
300 ,p_attribute12_name => 'ATTRIBUTE12'
301 ,p_attribute12_value => p_rec.attribute12
302 ,p_attribute13_name => 'ATTRIBUTE13'
303 ,p_attribute13_value => p_rec.attribute13
304 ,p_attribute14_name => 'ATTRIBUTE14'
305 ,p_attribute14_value => p_rec.attribute14
306 ,p_attribute15_name => 'ATTRIBUTE15'
307 ,p_attribute15_value => p_rec.attribute15
308 ,p_attribute16_name => 'ATTRIBUTE16'
309 ,p_attribute16_value => p_rec.attribute16
310 ,p_attribute17_name => 'ATTRIBUTE17'
311 ,p_attribute17_value => p_rec.attribute17
312 ,p_attribute18_name => 'ATTRIBUTE18'
313 ,p_attribute18_value => p_rec.attribute18
314 ,p_attribute19_name => 'ATTRIBUTE19'
315 ,p_attribute19_value => p_rec.attribute19
316 ,p_attribute20_name => 'ATTRIBUTE20'
317 ,p_attribute20_value => p_rec.attribute20
318 );
319 end if;
320 --
321 hr_utility.set_location(' Leaving:'||l_proc,20);
322 end chk_df;
323 --
324 -- ----------------------------------------------------------------------------
325 -- |-----------------------< chk_non_updateable_args >------------------------|
326 -- ----------------------------------------------------------------------------
327 -- {Start Of Comments}
328 --
329 -- Description:
330 -- This procedure is used to ensure that non updateable attributes have
331 -- not been updated. If an attribute has been updated an error is generated.
332 --
333 -- Pre Conditions:
334 -- g_old_rec has been populated with details of the values currently in
335 -- the database.
336 --
337 -- In Arguments:
338 -- p_rec has been populated with the updated values the user would like the
339 -- record set to.
340 --
341 -- Post Success:
342 -- Processing continues if all the non updateable attributes have not
343 -- changed.
344 --
345 -- Post Failure:
346 -- An application error is raised if any of the non updatable attributes
347 -- have been altered.
348 --
349 -- {End Of Comments}
350 -- ----------------------------------------------------------------------------
351 Procedure chk_non_updateable_args
352 (p_effective_date in date
353 ,p_rec in ota_cci_shd.g_rec_type
354 ) IS
355 --
356 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
357 --
358 Begin
359 --
360 -- Only proceed with the validation if a row exists for the current
361 -- record in the HR Schema.
362 --
363 IF NOT ota_cci_shd.api_updating
364 (p_category_usage_id => p_rec.category_usage_id
365 ,p_certification_id => p_rec.certification_id
366 ,p_object_version_number => p_rec.object_version_number
367 ) THEN
368 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
369 fnd_message.set_token('PROCEDURE ', l_proc);
370 fnd_message.set_token('STEP ', '5');
371 fnd_message.raise_error;
372 END IF;
373 --
374 -- EDIT_HERE: Add checks to ensure non-updateable args have
375 -- not been updated.
376 --
377 End chk_non_updateable_args;
378 --
379 -- ----------------------------------------------------------------------------
380 -- |--------------------------< check_crt_category_dates >------------------------|
381 -- ----------------------------------------------------------------------------
382 --
383 -- PUBLIC
384 -- Description:
385 -- Validates the startdate and enddate with respect to category dates.
386 --
387 Procedure check_cert_category_dates
388 (
389 p_certification_id in number
390 , p_category_usage_id in number
391 ) is
392 --
396
393 -- Declare cursors and local variables
394 --
395 -- Cursor to get value if parent category is already exits in child hierarchy of base category
397 CURSOR csr_cat_start_end_date is
398 SELECT
399 start_date_active,
400 nvl(end_date_active, to_date ('31-12-4712', 'DD-MM-YYYY'))
401 FROM ota_category_usages
402 WHERE category_usage_id =p_category_usage_id;
403
404 CURSOR csr_cert_start_end_date IS
405 SELECT start_date_active,
406 nvl(end_date_active, to_date ('31-12-4712', 'DD-MM-YYYY'))
407 FROM ota_certifications_b
408 WHERE certification_id = p_certification_id;
409
410
411 --
412 -- Variables for API Boolean parameters
413 l_proc varchar2(72) := g_package ||'check_category_dates';
414 l_cat_start_date date;
415 l_cat_end_date date;
416 l_cert_start_date date;
417 l_cert_end_date date;
418
419 Begin
420 hr_utility.set_location(' Entering:' || l_proc,10);
421 --
422 IF hr_multi_message.no_exclusive_error
423 (p_check_column1 => 'OTA_CERTIFICATIONS.START_DATE_ACTIVE'
424 ,p_check_column2 => 'OTA_CERTIFICATIONS.END_DATE_ACTIVE'
425 ,p_associated_column1 => 'OTA_CERTIFICATIONS.START_DATE_ACTIVE'
426 ,p_associated_column2 => 'OTA_CERTIFICATIONS.END_DATE_ACTIVE'
427 ) THEN
428 --
429 OPEN csr_cat_start_end_date;
430 FETCH csr_cat_start_end_date into l_cat_start_date, l_cat_end_date;
431
432 OPEN csr_cert_start_end_date;
433 FETCH csr_cert_start_end_date into l_cert_start_date, l_cert_end_date;
434
435 IF csr_cat_start_end_date%FOUND AND csr_cert_start_end_date%FOUND THEN
436 CLOSE csr_cat_start_end_date;
437 CLOSE csr_cert_start_end_date;
438 IF ( l_cat_start_date > l_cert_start_date
439 or l_cat_end_date < l_cert_end_date
440 ) THEN
441 --
442 fnd_message.set_name ( 'OTA','OTA_443896_CRT_OUT_OF_CAT_DATE');
443 fnd_message.raise_error;
444 --
445 End IF;
446 ELSE
447 CLOSE csr_cat_start_end_date;
448 CLOSE csr_cert_start_end_date;
449 End IF;
450 End IF;
451 --
452 hr_utility.set_location(' Leaving:' || l_proc,10);
453 Exception
454 when app_exception.application_exception then
455 IF hr_multi_message.exception_add
456 (p_associated_column1 => 'OTA_CERTIFICATIONS.START_DATE_ACTIVE'
457 ,p_associated_column2 => 'OTA_CERTIFICATIONS.END_DATE_ACTIVE'
458 ) THEN
459 hr_utility.set_location(' Leaving:'|| l_proc,20);
460 raise;
461 END IF;
462
463 hr_utility.set_location(' Leaving:'|| l_proc,30);
464 --
465 End check_cert_category_dates;
466 --
467 -- ----------------------------------------------------------------------------
468 -- |---------------------------< insert_validate >----------------------------|
469 -- ----------------------------------------------------------------------------
470 Procedure insert_validate
471 (p_effective_date in date
472 ,p_rec in ota_cci_shd.g_rec_type
473 ,p_certification_id in number
474 ,p_category_usage_id in number
475 ) is
476 --
477 l_proc varchar2(72) := g_package||'insert_validate';
478 --
479 Begin
480 hr_utility.set_location('Entering:'||l_proc, 5);
481 --
482 -- Call all supporting business operations
483 --
484 ota_ctu_bus.set_security_group_id(p_category_usage_id => p_category_usage_id);
485 --
486 -- Validate Dependent Attributes
487 --
488 --
489 check_unique_key( p_certification_id
490 , p_category_usage_id );
491
492 if p_rec.primary_flag = 'Y' then
493 check_multiple_primary_ctgr(p_certification_id);
494 check_cert_category_dates(p_certification_id => p_rec.certification_id
495 ,p_category_usage_id => p_rec.category_usage_id);
496 end if;
497
498 check_start_end_dates(p_rec.start_date_active
499 ,p_rec.end_date_active);
500
501 --
502 ota_cci_bus.chk_df(p_rec);
503 --
504
505 hr_utility.set_location(' Leaving:'||l_proc, 10);
506 End insert_validate;
507 --
508 -- ----------------------------------------------------------------------------
509 -- |---------------------------< update_validate >----------------------------|
510 -- ----------------------------------------------------------------------------
511 Procedure update_validate
512 (p_effective_date in date
513 ,p_rec in ota_cci_shd.g_rec_type
514 ) is
515 --
516 l_proc varchar2(72) := g_package||'update_validate';
517
518 l_start_date_changed boolean := ota_general.value_changed(ota_cci_shd.g_old_rec.start_date_active
519 ,p_rec.start_date_active);
520 l_end_date_changed boolean := ota_general.value_changed(ota_cci_shd.g_old_rec.end_date_active
521 ,p_rec.end_date_active );
522 l_start_date ota_cert_cat_inclusions.start_date_active%TYPE;
523 l_end_date ota_cert_cat_inclusions.end_date_active%TYPE;
524 --
525 --
526 Begin
527 hr_utility.set_location('Entering:'||l_proc, 5);
528 --
529 -- Call all supporting business operations
530 --
531 --
532 ota_ctu_bus.set_security_group_id(p_category_usage_id => p_rec.category_usage_id);
533 --
534 -- Validate Dependent Attributes
535 --
536 chk_non_updateable_args
537 (p_effective_date => p_effective_date
538 ,p_rec => p_rec
539 );
543 ,p_rec.end_date_active);
540 --
541 IF l_start_date_changed OR l_end_date_changed THEN
542 check_start_end_dates(p_rec.start_date_active
544
545 IF (l_start_date_changed) THEN
546 l_start_date := p_rec.start_date_active;
547 ELSE
548 l_start_date := ota_cci_shd.g_old_rec.start_date_active;
549 END IF;
550
551 IF (l_end_date_changed) THEN
552 l_end_date := p_rec.end_date_active;
553 ELSE
554 l_end_date := ota_cci_shd.g_old_rec.end_date_active;
555 END IF;
556
557 check_category_dates(p_rec.category_usage_id
558 ,l_start_date
559 ,l_end_date);
560 END IF;
561
562 IF p_rec.primary_flag = 'Y' THEN
563 check_cert_category_dates
564 (p_certification_id => p_rec.certification_id,
565 p_category_usage_id => p_rec.category_usage_id);
566 END IF;
567 --
568 ota_cci_bus.chk_df(p_rec);
569 --
570 hr_utility.set_location(' Leaving:'||l_proc, 10);
571 End update_validate;
572 --
573 -- ----------------------------------------------------------------------------
574 -- |---------------------------< delete_validate >----------------------------|
575 -- ----------------------------------------------------------------------------
576 Procedure delete_validate
577 (p_rec in ota_cci_shd.g_rec_type
578 ) is
579 --
580 l_proc varchar2(72) := g_package||'delete_validate';
581 --
582 Begin
583 hr_utility.set_location('Entering:'||l_proc, 5);
584 --
585 -- Call all supporting business operations
586 --
587 hr_utility.set_location(' Leaving:'||l_proc, 10);
588 End delete_validate;
589 --
590 --
591 -- ----------------------------------------------------------------------------
592 -- |----------------------------< check_multiple_primary_ctgr >---------------|
593 -- ----------------------------------------------------------------------------
594 --
595 -- PUBLIC
596 -- Description:
597 -- There can be only one primary category for an activity.
598 --
599 Procedure check_multiple_primary_ctgr
600 (
601 p_certification_id in number
602 ) is
603 --
604 v_proc varchar2(72) := g_package||'check_multiple_primary_ctgr';
605 v_exists varchar2(1);
606 cursor sel_multiple_primary is
607 select 'Y'
608 from OTA_CERT_CAT_inclusions cci
609 where cci.certification_id = p_certification_id
610 and cci.primary_flag = 'Y';
611 --
612 Begin
613 hr_utility.set_location('Entering:'|| v_proc, 5);
614 --
615 Open sel_multiple_primary;
616 fetch sel_multiple_primary into v_exists;
617 --
618 if sel_multiple_primary%found then
619 close sel_multiple_primary;
620
621 fnd_message.set_name('OTA', 'OTA_13676_DCI_DUPLICATE');
622 fnd_message.raise_error;
623 end if;
624 close sel_multiple_primary;
625 --
626 hr_utility.set_location(' Leaving:'|| v_proc, 10);
627
628 Exception
629 WHEN app_exception.application_exception THEN
630
631 IF hr_multi_message.exception_add(
632 p_associated_column1 => 'OTA_CERT_CAT_INCLUSIONS.PRIMARY_FLAG')
633
634 THEN
635
636 hr_utility.set_location(' Leaving:'||v_proc, 22);
637 RAISE;
638
639 END IF;
640 End check_multiple_primary_ctgr;
641
642 -- ----------------------------------------------------------------------------
643 -- |----------------------------< check_if_primary_category >-----------------|
644 -- ----------------------------------------------------------------------------
645 --
646 -- PUBLIC
647 -- Description:
648 -- Check if an activity category already has a primary category.
649 -- This category cannot be deleted.
650 --
651 Procedure check_if_primary_category
652 (
653 p_certification_id in number
654 ,p_category_usage_id in number
655 ) is
656 --
657 v_proc varchar2(72) := g_package||'check_if_primary_category';
658 v_exists varchar2(1);
659 cursor sel_primary_category is
660 select 'Y'
661 from OTA_CERT_CAT_inclusions cci
662 where cci.certification_id = p_certification_id
663 and cci.category_usage_id = p_category_usage_id
664 and cci.primary_flag = 'Y';
665 --
666 Begin
667 hr_utility.set_location('Entering:'|| v_proc, 5);
668 --
669 Open sel_primary_category;
670 fetch sel_primary_category into v_exists;
671 --
672 if sel_primary_category%found then
673 close sel_primary_category;
674
675 fnd_message.set_name('OTA', 'OTA_443266_DCI_DEL_PRIMARY');
676 fnd_message.raise_error;
677 end if;
678 close sel_primary_category;
679 --
680 hr_utility.set_location(' Leaving:'|| v_proc, 10);
681 Exception
682 WHEN app_exception.application_exception THEN
683
684 IF hr_multi_message.exception_add(
685 p_associated_column1 => 'OTA_CERT_CAT_INCLUSIONS.PRIMARY_FLAG')
686
687 THEN
688
689 hr_utility.set_location(' Leaving:'||v_proc, 22);
690 RAISE;
691
692 END IF;
693 hr_utility.set_location(' Leaving:'||v_proc, 25);
694 End check_if_primary_category;
695
696 --
697 -- ----------------------------------------------------------------------------
698 -- |----------------------------< check_start_end_dates >-----------------|
702 -- Validates the startdate and enddate.
699 -- ----------------------------------------------------------------------------
700 -- PUBLIC
701 -- Description:
703 -- Startdate must be less than, or equal to, enddate.
704 --
705 Procedure check_start_end_dates
706 (
707 p_start_date in date
708 ,p_end_date in date
709 ) is
710 --
711 v_proc varchar2(72) := g_package||'check_start_end_dates';
712 --
713 Begin
714 --
715 hr_utility.set_location('Entering:'|| v_proc, 5);
716 --
717 ota_general.check_start_end_dates( p_start_date, p_end_date);
718 --
719 hr_utility.set_location(' Leaving:'|| v_proc, 10);
720
721 Exception
722 WHEN app_exception.application_exception THEN
723
724 IF hr_multi_message.exception_add(
725 p_associated_column1 => 'OTA_CERT_CAT_INCLUSIONS.START_DATE_ACTIVE'
726 ,p_associated_column2 => 'OTA_CERT_CAT_INCLUSIONS.END_DATE_ACTIVE')
727 THEN
728
729 hr_utility.set_location(' Leaving:'||v_proc, 22);
730 RAISE;
731
732 END IF;
733 hr_utility.set_location(' Leaving:'||v_proc, 25);
734 --
735 End check_start_end_dates;
736
737 --
738 --
739 -- ----------------------------------------------------------------------------
740 -- |---------------------------< check_unique_key >---------------------------|
741 -- ----------------------------------------------------------------------------
742 --
743 -- PUBLIC
744 -- Description:
745 -- Validates the unique key.
746 -- The module version and module category must form a unique key.
747 --
748 Procedure check_unique_key
749 (
750 p_certification_id in number
751 ,p_category_usage_id in number
752 ) is
753 --
754 v_exists varchar2(1);
755 v_proc varchar2(72) := g_package||'check_unique_key';
756 --
757 cursor sel_unique_key is
758 select 'Y'
759 from OTA_CERT_CAT_inclusions cci
760 where cci.certification_id = p_certification_id
761 and cci.category_usage_id = p_category_usage_id;
762 --
763 Begin
764 hr_utility.set_location('Entering:'|| v_proc, 5);
765 --
766 Open sel_unique_key;
767 fetch sel_unique_key into v_exists;
768 --
769 if sel_unique_key%found then
770 close sel_unique_key;
771
772 fnd_message.set_name('OTA', 'OTA_13676_DCI_DUPLICATE');
773 fnd_message.raise_error;
774 end if;
775 close sel_unique_key;
776 --
777 hr_utility.set_location(' Leaving:'|| v_proc, 10);
778 Exception
779 WHEN app_exception.application_exception THEN
780
781 IF hr_multi_message.exception_add(
782 p_associated_column1 => 'OTA_CERT_CAT_INCLUSIONS.certification_id',
783 p_associated_column2 => 'OTA_CERT_CAT_INCLUSIONS.CATEGORY_USAGE_ID')
784 THEN
785 hr_utility.set_location(' Leaving:'||v_proc, 22);
786 RAISE;
787
788 END IF;
789 hr_utility.set_location(' Leaving:'||v_proc, 25);
790 End check_unique_key;
791 --
792
793 --
794 -- ----------------------------------------------------------------------------
795 -- |-------------------------< check_category_dates >-------------------------|
796 -- ----------------------------------------------------------------------------
797 --
798 -- PUBLIC
799 -- Description:
800 -- Validates the startdate and enddate.
801 -- Update of start and end dates must not invalidate booking deals
802 -- for this activity version.
803 --
804 Procedure check_category_dates
805 (
806 p_category_usage_id in number
807 ,p_start_date in date
808 ,p_end_date in date
809 ) is
810 --
811 v_start_date date;
812 v_end_date date;
813 l_error varchar2(10) := NULL;
814 v_proc varchar2(72) := g_package||'check_category_dates';
815 --
816 cursor sel_check_dates is
817 select start_date_active
818 , end_date_active
819 from ota_category_usages ctu
820 where ctu.category_usage_id = p_category_usage_id;
821 --
822 Begin
823 --
824 hr_utility.set_location('Entering:'|| v_proc, 5);
825 --
826 Open sel_check_dates;
827 Fetch sel_check_dates into v_start_date, v_end_date;
828 IF sel_check_dates%FOUND THEN
829 ota_general.check_par_child_dates(v_start_date, v_end_date,p_start_date,p_end_date);
830 END IF;
831 --
832 Close sel_check_dates;
833 --
834 hr_utility.set_location(' Leaving:'|| v_proc, 10);
835
836 Exception
837 WHEN app_exception.application_exception THEN
838
839 IF hr_multi_message.exception_add(
840 p_associated_column1 => 'OTA_CERT_CAT_INCLUSIONS.START_DATE_ACTIVE'
841 ,p_associated_column2 => 'OTA_CERT_CAT_INCLUSIONS.END_DATE_ACTIVE')
842 THEN
843
844 hr_utility.set_location(' Leaving:'||v_proc, 22);
845 RAISE;
846
847 END IF;
848 hr_utility.set_location(' Leaving:'||v_proc, 25);
849 --
850 End check_category_dates;
851 --
852 --
853
854 end ota_cci_bus;