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