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