[Home] [Help]
PACKAGE BODY: APPS.OTA_LPM_BUS
Source
1 Package Body ota_lpm_bus as
2 /* $Header: otlpmrhi.pkb 120.0 2005/05/29 07:22:37 appldev noship $ */
3
4 --
5 -- ----------------------------------------------------------------------------
6 -- | Private Global Definitions |
7 -- ----------------------------------------------------------------------------
8 --
9 g_package varchar2(33) := ' ota_lpm_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_member_id number default null;
16 --
17 -- ---------------------------------------------------------------------------
18 -- |----------------------< set_security_group_id >--------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 Procedure set_security_group_id
22 (p_learning_path_member_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_learning_path_members lpm
33 where lpm.learning_path_member_id = p_learning_path_member_id
34 and pbg.business_group_id = lpm.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_member_id'
51 ,p_argument_value => p_learning_path_member_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_MEMBER_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
83 --
80 --
81 hr_api.set_legislation_context(l_legislation_code);
82 end if;
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_member_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_learning_path_members lpm
103 where lpm.learning_path_member_id = p_learning_path_member_id
104 and pbg.business_group_id = lpm.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_member_id'
120 ,p_argument_value => p_learning_path_member_id
121 );
122 --
123 if ( nvl(ota_lpm_bus.g_learning_path_member_id, hr_api.g_number)
124 = p_learning_path_member_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_lpm_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_lpm_bus.g_learning_path_member_id := p_learning_path_member_id;
155 ota_lpm_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_lpm_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_member_id is not null) and (
199 nvl(ota_lpm_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
200 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
201 nvl(ota_lpm_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
202 nvl(p_rec.attribute1, hr_api.g_varchar2) or
203 nvl(ota_lpm_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
204 nvl(p_rec.attribute2, hr_api.g_varchar2) or
205 nvl(ota_lpm_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
206 nvl(p_rec.attribute3, hr_api.g_varchar2) or
207 nvl(ota_lpm_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
208 nvl(p_rec.attribute4, hr_api.g_varchar2) or
209 nvl(ota_lpm_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
210 nvl(p_rec.attribute5, hr_api.g_varchar2) or
211 nvl(ota_lpm_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
212 nvl(p_rec.attribute6, hr_api.g_varchar2) or
213 nvl(ota_lpm_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
214 nvl(p_rec.attribute7, hr_api.g_varchar2) or
215 nvl(ota_lpm_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
216 nvl(p_rec.attribute8, hr_api.g_varchar2) or
217 nvl(ota_lpm_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
218 nvl(p_rec.attribute9, hr_api.g_varchar2) or
219 nvl(ota_lpm_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
223 nvl(ota_lpm_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
220 nvl(p_rec.attribute10, hr_api.g_varchar2) or
221 nvl(ota_lpm_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
222 nvl(p_rec.attribute11, hr_api.g_varchar2) or
224 nvl(p_rec.attribute12, hr_api.g_varchar2) or
225 nvl(ota_lpm_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
226 nvl(p_rec.attribute13, hr_api.g_varchar2) or
227 nvl(ota_lpm_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
228 nvl(p_rec.attribute14, hr_api.g_varchar2) or
229 nvl(ota_lpm_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
230 nvl(p_rec.attribute15, hr_api.g_varchar2) or
231 nvl(ota_lpm_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
232 nvl(p_rec.attribute16, hr_api.g_varchar2) or
233 nvl(ota_lpm_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
234 nvl(p_rec.attribute17, hr_api.g_varchar2) or
235 nvl(ota_lpm_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
236 nvl(p_rec.attribute18, hr_api.g_varchar2) or
237 nvl(ota_lpm_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
238 nvl(p_rec.attribute19, hr_api.g_varchar2) or
239 nvl(ota_lpm_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
240 nvl(p_rec.attribute20, hr_api.g_varchar2) ))
241 or (p_rec.learning_path_member_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_LEARNING_PATH_MEMBERS'
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:
307 -- Pre Conditions:
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 --
311 -- In Arguments:
308 -- g_old_rec has been populated with details of the values currently in
309 -- the database.
310 --
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_lpm_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_lpm_shd.api_updating
338 (p_learning_path_member_id => p_rec.learning_path_member_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 -- |---------------------< is_catalog_lp >-------------------------------------|
355 -- ----------------------------------------------------------------------------
356 --
357 -- PUBLIC
358 -- Description:
359 --
360 --
361 Function is_catalog_lp(p_learning_path_id IN NUMBER)
362 RETURN BOOLEAN is
363 --
364 v_path_source_code ota_learning_paths.path_source_code%TYPE;
365 l_return boolean := FALSE;
366 v_proc varchar2(72) := g_package||'is_catalog_lp';
367 --
368 cursor csr_is_catalog_lp is
369 select path_source_code
370 from ota_learning_paths lps
371 where lps.learning_path_id = p_learning_path_id;
372 --
373 Begin
374 --
375 hr_utility.set_location('Entering:'|| v_proc, 5);
376 --
377 Open csr_is_catalog_lp;
378 fetch csr_is_catalog_lp into v_path_source_code;
379 close csr_is_catalog_lp;
380 --
381 IF v_path_source_code = 'CATALOG' THEN
382 l_return := TRUE;
383 END IF;
384 hr_utility.set_location(' Leaving:'|| v_proc, 10);
385 --
386 RETURN l_return;
387
388 End is_catalog_lp;
389 --
390 -- ----------------------------------------------------------------------------
391 -- |-----------------------<chk_notify_days_before_target >--------------------|
392 -- ----------------------------------------------------------------------------
393 PROCEDURE chk_notify_days_before_target(p_learning_path_id IN ota_learning_paths.learning_path_id%TYPE
394 ,p_duration IN ota_learning_path_members.duration%TYPE
395 ,p_notify_days_before_target IN ota_learning_path_members.notify_days_before_target%TYPE)
396 IS
397 --
398 l_proc VARCHAR2(72) := g_package||'chk_notify_days_before_target';
399 l_api_updating boolean;
400
401 BEGIN
402 hr_utility.set_location(' Leaving:'||l_proc, 10);
403 --
404 IF is_catalog_lp(p_learning_path_id) THEN
405 IF p_duration IS NOT NULL AND p_notify_days_before_target IS NOT NULL THEN
406 --Modified for Bug#3861864
407 IF p_duration <= p_notify_days_before_target THEN
408 fnd_message.set_name('OTA','OTA_13079_LPS_NOTIFY_TARGET');
409 fnd_message.raise_error;
410 END IF;
411 ELSIF p_duration IS NULL and p_notify_days_before_target IS NOT NULL THEN
412 fnd_message.set_name('OTA','OTA_13083_LP_CT_NOT_ERR');
413 fnd_message.raise_error;
414 END IF;
415 END IF;
416
417 IF p_notify_days_before_target < 0 THEN
418 fnd_message.set_name('OTA','OTA_443368_POSITIVE_NUMBER');
419 fnd_message.raise_error;
420 END IF;
421
422 hr_utility.set_location(' Leaving:'||l_proc, 40);
423
424 EXCEPTION
425
426 WHEN app_exception.application_exception THEN
427
428 IF hr_multi_message.exception_add
429 (p_associated_column1 => 'OTA_LEARNING_PATHS.NOTIFY_DAYS_BEFORE_TARGET') THEN
430
431 hr_utility.set_location(' Leaving:'||l_proc, 42);
432 RAISE;
433 END IF;
434
435 hr_utility.set_location(' Leaving:'||l_proc, 44);
436
437 END chk_notify_days_before_target;
438 --
439 -- ----------------------------------------------------------------------------
440 -- |----------------------<chk_activity_version_id>---------------------------|
441 -- ----------------------------------------------------------------------------
442 --
443 PROCEDURE chk_activity_version_id (
444 p_learning_path_member_id IN ota_learning_path_members.learning_path_member_id%TYPE
445 ,p_object_version_number IN ota_learning_path_members.object_version_number%TYPE
446 ,p_activity_version_id IN ota_learning_path_members.activity_version_id%TYPE
447 ,p_business_group_id IN ota_learning_path_members.business_group_id%TYPE
451 --
448 ,p_learning_path_id IN ota_learning_path_members.learning_path_id%TYPE
449 ) IS
450 --
452 l_proc VARCHAR2(72) := g_package|| 'activity_definition_id';
453 l_api_updating BOOLEAN;
454 l_business_group_id ota_learning_path_members.business_group_id%TYPE;
455 l_exists VARCHAR2(1);
456
457 --
458
459 CURSOR csr_activity_version_id IS
460 SELECT oad.business_group_id
461 FROM ota_activity_versions oav,
462 ota_activity_definitions oad
463 WHERE oav.activity_id = oad.activity_id
464 AND oav.activity_version_id = p_activity_version_id;
465 --
466 --
467 BEGIN
468 --
469 -- check mandatory parameters have been set.
470 --
471 hr_utility.set_location(' Step:'|| l_proc, 10);
472
473 IF hr_multi_message.no_exclusive_error
474 (p_check_column1 => 'OTA_LEARNING_PATH_MEMBERS.LEARNING_PATH_ID'
475 ,p_associated_column1 => 'OTA_LEARNING_PATH_MEMBERS.LEARNING_PATH_ID') THEN
476
477 hr_api.mandatory_arg_error
478 (p_api_name => l_proc
479 ,p_argument => 'p_business_group_id'
480 ,p_argument_value => p_business_group_id
481 );
482 --
483 l_api_updating := ota_lpm_shd.api_updating
484 (p_learning_path_member_id => p_learning_path_member_id
485 ,p_object_version_number => p_object_version_number
486 );
487 --
488 -- If this is a changing update, or a new insert, test
489 --
490 IF p_activity_version_id IS NOT NULL THEN
491 IF (l_api_updating AND
492 NVL(ota_lpm_shd.g_old_rec.activity_version_id, hr_api.g_number) <>
493 NVL(p_activity_version_id, hr_api.g_number) )
494 OR (NOT l_api_updating)
495 THEN
496 -- Check that the definition exists
497 --
498 hr_utility.set_location(' Step:'|| l_proc, 20);
499 OPEN csr_activity_version_id;
500 FETCH csr_activity_version_id INTO l_business_group_id;
501 IF csr_activity_version_id%NOTFOUND THEN
502 CLOSE csr_activity_version_id;
503 fnd_message.set_name('OTA', 'OTA_13850_TPM_BAD_ACT_VER');
504 fnd_message.raise_error;
505 ELSE
506 IF l_business_group_id <> p_business_group_id THEN
507 CLOSE csr_activity_version_id;
508 fnd_message.set_name('OTA', 'OTA_13851_TPM_WRONG_ACT_VER');
509 fnd_message.raise_error;
510 ELSE
511 hr_utility.set_location(' Step:'|| l_proc, 30);
512 CLOSE csr_activity_version_id;
513 END IF;
514 END IF;
515 --
516 END IF;
517 END IF;
518 --
519
520 END IF;
521 hr_utility.set_location(' Leaving:'||l_proc, 40);
522
523 EXCEPTION
524
525 WHEN app_exception.application_exception THEN
526
527 IF hr_multi_message.exception_add
528 (p_associated_column1 => 'OTA_LEARNING_PATH_MEMBERS.ACTIVITY_VERSION_ID') THEN
529
530 hr_utility.set_location(' Leaving:'||l_proc, 42);
531 RAISE;
532
533 END IF;
534
535 hr_utility.set_location(' Leaving:'||l_proc, 44);
536
537
538 END chk_activity_version_id;
539
540 -- ----------------------------------------------------------------------------
541 -- |----------------------<chk_learning_path_id>-------------------------------|
542 -- ----------------------------------------------------------------------------
543 --
544 PROCEDURE chk_learning_path_id
545 (p_learning_path_id IN ota_learning_path_members.learning_path_id%TYPE
546 ,p_business_group_id IN ota_learning_path_members.business_group_id%TYPE
547 ) IS
548 --
549 l_exists VARCHAR2(1);
550 l_proc VARCHAR2(72) := g_package|| 'chk_learning_path_id';
551 --
552 CURSOR csr_learning_path_id IS
553 SELECT NULL
554 FROM OTA_LEARNING_PATHS
555 WHERE learning_path_id = p_learning_path_id
556 AND business_group_id = p_business_group_id;
557 BEGIN
558 --
559 -- check mandatory parameters have been set
560 --
561 hr_utility.set_location(' Step:'|| l_proc, 20);
562 hr_api.mandatory_arg_error
563 (p_api_name => l_proc
564 ,p_argument => 'p_learning_path_id'
565 ,p_argument_value => p_learning_path_id
566 );
567 --
568 hr_utility.set_location(' Step:'|| l_proc, 30);
569 hr_api.mandatory_arg_error
570 (p_api_name => l_proc
571 ,p_argument => 'p_business_group_id'
572 ,p_argument_value => p_business_group_id
573 );
574 --
575 --
576 hr_utility.set_location(' Step:'|| l_proc, 50);
577 OPEN csr_learning_path_id;
578 FETCH csr_learning_path_id INTO l_exists;
579 IF csr_learning_path_id%NOTFOUND THEN
580 CLOSE csr_learning_path_id;
581 hr_utility.set_location(' Step:'|| l_proc, 60);
582 fnd_message.set_name('OTA', 'OTA_13605_LPM_NO_LEARNING_PATH');
583 fnd_message.raise_error;
584 ELSE
585 hr_utility.set_location(' Step:'|| l_proc, 80);
586 CLOSE csr_learning_path_id;
587 END IF;
588 --
589 hr_utility.set_location(' Leaving:'||l_proc, 90);
590
591 EXCEPTION
592
593 WHEN app_exception.application_exception THEN
594
595 IF hr_multi_message.exception_add
596 (p_associated_column1 => 'OTA_LEARNING_PATH_MEMBERS.LEARNING_PATH_ID') THEN
597
598 hr_utility.set_location(' Leaving:'||l_proc, 92);
599 RAISE;
603
600 END IF;
601
602 hr_utility.set_location(' Leaving:'||l_proc, 94);
604 END chk_learning_path_id;
605 --
606 -- ----------------------------------------------------------------------------
607 -- |----------------------<chk_unique_course>---------------------------------|
608 -- ----------------------------------------------------------------------------
609 --
610 PROCEDURE chk_unique_course
611 (p_learning_path_member_id IN ota_learning_path_members.learning_path_member_id%TYPE
612 ,p_object_version_number IN ota_learning_path_members.object_version_number%TYPE
613 ,p_activity_version_id IN ota_learning_path_members.activity_version_id%TYPE
614 ,p_learning_path_id IN ota_learning_path_members.learning_path_id%TYPE
615 ) IS
616 --
617 l_proc VARCHAR2(72) := g_package|| 'chk_unique';
618 l_exists VARCHAR2(1);
619 l_api_updating boolean;
620 --
621 CURSOR csr_unique IS
622 SELECT NULL
623 FROM OTA_LEARNING_PATH_MEMBERS
624 WHERE learning_path_id = p_learning_path_id
625 AND (p_activity_version_id IS NOT NULL AND
626 p_activity_version_id = activity_version_id)
627 AND (p_learning_path_member_id IS NULL
628 OR p_learning_path_member_id <> learning_path_member_id);
629 BEGIN
630 --
631 -- check mandatory parameters have been set
632 --
633 --
634 hr_utility.set_location(' Step:'|| l_proc, 30);
635
636 IF hr_multi_message.no_exclusive_error
637 (p_check_column1 => 'OTA_LEARNING_PATH_MEMBERS.LEARNING_PATH_ID'
638 ,p_check_column2 => 'OTA_LEARNING_PATH_MEMBERS.ACTIVITY_VERSION_ID'
639 ,p_associated_column1 => 'OTA_LEARNING_PATH_MEMBERS.LEARNING_PATH_ID'
640 ,p_associated_column2 => 'OTA_LEARNING_PATH_MEMBERS.ACTIVITY_VERSION_ID' ) THEN
641
642
643 hr_api.mandatory_arg_error
644 (p_api_name => l_proc
645 ,p_argument => 'p_learning_path_id'
646 ,p_argument_value => p_learning_path_id
647 );
648
649 l_api_updating := ota_lpm_shd.api_updating
650 (p_learning_path_member_id => p_learning_path_member_id
651 ,p_object_version_number => p_object_version_number);
652 --
653 -- Check if anything is changing, or this is an insert
654 --
655 IF (l_api_updating AND
656 NVL(ota_lpm_shd.g_old_rec.activity_version_id, hr_api.g_number) <>
657 NVL(p_activity_version_id, hr_api.g_number))
658 OR (NOT l_api_updating) THEN
659 --
660 -- check the combination is unique
661 --
662 hr_utility.set_location(' Step:'|| l_proc, 50);
663 OPEN csr_unique;
664 FETCH csr_unique INTO l_exists;
665 IF csr_unique%FOUND THEN
666 CLOSE csr_unique;
667 hr_utility.set_location(' Step:'|| l_proc, 60);
668 fnd_message.set_name('OTA', 'OTA_13620_LPM_NOT_UNIQUE');
669 fnd_message.raise_error;
670 ELSE
671 CLOSE csr_unique;
672 hr_utility.set_location(' Step:'|| l_proc, 70);
673 END IF;
674 END IF;
675 --
676 END IF;
677 hr_utility.set_location(' Leaving:'||l_proc, 90);
678
679 EXCEPTION
680
681 WHEN app_exception.application_exception THEN
682
683 IF hr_multi_message.exception_add
684 (p_associated_column1 => 'OTA_LEARNING_PATH_MEMBERS.ACTIVITY_VERSION_ID') THEN
685
686 hr_utility.set_location(' Leaving:'||l_proc, 92);
687 RAISE;
688
689 END IF;
690
691 hr_utility.set_location(' Leaving:'||l_proc, 94);
692
693 END chk_unique_course;
694 --
695
696 /*
697 -- ----------------------------------------------------------------------------
698 -- |----------------------<chk_unique_sequence>--------------------------------|
699 -- ----------------------------------------------------------------------------
700 --
701 PROCEDURE chk_unique_sequence
702 (p_learning_path_member_id IN ota_learning_path_members.learning_path_member_id%TYPE
703 ,p_object_version_number IN ota_learning_path_members.object_version_number%TYPE
704 ,p_course_sequence IN ota_learning_path_members.course_sequence%TYPE
705 ,p_learning_path_id IN ota_learning_path_members.learning_path_id%TYPE
706 ) IS
707 --
708 l_proc VARCHAR2(72) := g_package|| 'chk_unique';
709 l_exists VARCHAR2(1);
710 l_api_updating boolean;
711 --
712 CURSOR csr_unique IS
713 SELECT NULL
714 FROM OTA_LEARNING_PATH_MEMBERS
715 WHERE learning_path_id = p_learning_path_id
716 AND (p_course_sequence IS NOT NULL AND
717 p_course_sequence = course_sequence)
718 AND (p_learning_path_member_id IS NULL
719 OR p_learning_path_member_id <> learning_path_member_id);
720 BEGIN
721 --
722 -- check mandatory parameters have been set
723 --
724 --
725 hr_utility.set_location(' Step:'|| l_proc, 30);
726
727 IF hr_multi_message.no_exclusive_error
728 (p_check_column1 => 'OTA_LEARNING_PATH_MEMBERS.ACTIVITY_VERSION_ID'
729 ,p_associated_column1 => 'OTA_LEARNING_PATH_MEMBERS.ACTIVITY_VERSION_ID' ) THEN
730
731
732 hr_api.mandatory_arg_error
733 (p_api_name => l_proc
734 ,p_argument => 'p_learning_path_id'
735 ,p_argument_value => p_learning_path_id
736 );
737
738 l_api_updating := ota_lpm_shd.api_updating
739 (p_learning_path_member_id => p_learning_path_member_id
740 ,p_object_version_number => p_object_version_number);
741 --
745 NVL(ota_lpm_shd.g_old_rec.course_sequence, hr_api.g_number) <>
742 -- Check if anything is changing, or this is an insert
743 --
744 IF (l_api_updating AND
746 NVL(p_course_sequence, hr_api.g_number))
747 OR (NOT l_api_updating) THEN
748 --
749 -- check the sequence is unique
750 --
751 hr_utility.set_location(' Step:'|| l_proc, 50);
752 OPEN csr_unique;
753 FETCH csr_unique INTO l_exists;
754 IF csr_unique%FOUND THEN
755 CLOSE csr_unique;
756 hr_utility.set_location(' Step:'|| l_proc, 60);
757 fnd_message.set_name('OTA', 'OTA_13844_TPM_NOT_UNIQUE');
758 fnd_message.raise_error;
759 ELSE
760 CLOSE csr_unique;
761 hr_utility.set_location(' Step:'|| l_proc, 70);
762 END IF;
763 END IF;
764 --
765 END IF;
766 hr_utility.set_location(' Leaving:'||l_proc, 90);
767
768 EXCEPTION
769
770 WHEN app_exception.application_exception THEN
771
772 IF hr_multi_message.exception_add
773 (p_associated_column1 => 'OTA_LEARNING_PATH_MEMBERS.COURSE_SEQUENCE') THEN
774
775 hr_utility.set_location(' Leaving:'||l_proc, 92);
776 RAISE;
777
778 END IF;
779
780 hr_utility.set_location(' Leaving:'||l_proc, 94);
781
782 END chk_unique_sequence;
783 --
784
785 */
786
787
788 -- ----------------------------------------------------------------------------
789 -- |----------------------<chk_duration>---------------------------------------|
790 -- ----------------------------------------------------------------------------
791 --
792 PROCEDURE chk_duration
793 (p_learning_path_member_id IN ota_learning_path_members.learning_path_member_id%TYPE
794 ,p_object_version_number IN ota_learning_path_members.object_version_number%TYPE
795 ,p_duration IN ota_learning_path_members.duration%TYPE
796 ,p_duration_units IN ota_learning_path_members.duration_units%TYPE
797 ) IS
798 --
799 l_proc VARCHAR2(72) := g_package|| 'chk_duration';
800 l_exists VARCHAR2(1);
801 l_api_updating boolean;
802 --
803
804 BEGIN
805 --
806 -- check mandatory parameters have been set
807 --
808 --
809 hr_utility.set_location(' Step:'|| l_proc, 30);
810
811 IF hr_multi_message.no_exclusive_error
812 (p_check_column1 => 'OTA_LEARNING_PATH_MEMBERS.LEARNING_PATH_ID'
813 ,p_associated_column1 => 'OTA_LEARNING_PATH_MEMBERS.LEARNING_PATH_ID' ) THEN
814
815
816 hr_utility.set_location(' Step:'|| l_proc, 40);
817
818 l_api_updating := ota_lpm_shd.api_updating
819 (p_learning_path_member_id => p_learning_path_member_id
820 ,p_object_version_number => p_object_version_number);
821 --
822 -- Check if anything is changing, or this is an insert
823 --
824 IF (l_api_updating AND
825 NVL(ota_lpm_shd.g_old_rec.duration, hr_api.g_number) <>
826 NVL(p_duration, hr_api.g_number)
827 OR NVL(ota_lpm_shd.g_old_rec.duration_units,hr_api.g_varchar2) <>
828 NVL(p_duration_units, hr_api.g_varchar2))
829 OR (NOT l_api_updating AND (p_duration IS NOT NULL OR p_duration_units IS NOT NULL)) THEN
830 --
831 -- check the duration is positive
832 --
833 hr_utility.set_location(' Step:'|| l_proc, 50);
834 IF (p_duration <= 0) THEN
835 hr_utility.set_location(' Step:'|| l_proc, 60);
836 fnd_message.set_name('OTA', 'OTA_13443_EVT_DURATION_NOT_0');
837 fnd_message.raise_error;
838 ELSE IF((p_duration IS NOT NULL AND p_duration_units IS NULL)
839 OR (p_duration IS NULL AND p_duration_units IS NOT NULL)) THEN
840 hr_utility.set_location(' Step:'|| l_proc, 60);
841 fnd_message.set_name('OTA', 'OTA_13881_NHS_COMB_INVALID');
842 fnd_message.raise_error;
843 END IF;
844 END IF;
845 END IF;
846 --
847 END IF;
848 hr_utility.set_location(' Leaving:'||l_proc, 90);
849
850 EXCEPTION
851
852 WHEN app_exception.application_exception THEN
853
854 IF hr_multi_message.exception_add
855 (p_associated_column1 => 'OTA_LEARNING_PATH_MEMBERS.DURATION') THEN
856
857 hr_utility.set_location(' Leaving:'||l_proc, 92);
858 RAISE;
859
860 END IF;
861
862 hr_utility.set_location(' Leaving:'||l_proc, 94);
863
864 END chk_duration;
865 --
866 -- ----------------------------------------------------------------------------
867 -- |---------------------------< chk_duration_units >------------------------|
868 -- ----------------------------------------------------------------------------
869 PROCEDURE chk_duration_units (p_learning_path_member_id IN number
870 ,p_object_version_number IN NUMBER
871 ,p_duration_units IN VARCHAR2
872 ,p_effective_date IN date) IS
873
874 --
875 l_proc VARCHAR2(72) := g_package||'chk_duration_units';
876 l_api_updating boolean;
877
878 BEGIN
879 hr_utility.set_location(' Leaving:'||l_proc, 10);
880 --
881 -- check mandatory parameters has been set
882 --
883 hr_api.mandatory_arg_error
884 (p_api_name => l_proc
885 ,p_argument => 'effective_date'
886 ,p_argument_value => p_effective_date);
887
888 l_api_updating := ota_lpm_shd.api_updating
889 (p_learning_path_member_id => p_learning_path_member_id
893 IF ((l_api_updating AND
890 ,p_object_version_number => p_object_version_number);
891
892
894 NVL(ota_lpm_shd.g_old_rec.duration_units,hr_api.g_varchar2) <>
895 NVL(p_duration_units, hr_api.g_varchar2))
896 OR NOT l_api_updating AND p_duration_units IS NOT NULL) THEN
897
898 hr_utility.set_location(' Leaving:'||l_proc, 20);
899 --
900
901 IF p_duration_units IS NOT NULL THEN
902 IF hr_api.not_exists_in_hr_lookups
903 (p_effective_date => p_effective_date
904 ,p_lookup_type => 'OTA_DURATION_UNITS'
905 ,p_lookup_code => p_duration_units) THEN
906 fnd_message.set_name('OTA','OTA_13882_NHS_DURATION_INVALID');
907 fnd_message.raise_error;
908 END IF;
909 hr_utility.set_location(' Leaving:'||l_proc, 30);
910
911 END IF;
912
913 END IF;
914 hr_utility.set_location(' Leaving:'||l_proc, 40);
915
916 EXCEPTION
917
918 WHEN app_exception.application_exception THEN
919
920 IF hr_multi_message.exception_add
921 (p_associated_column1 => 'OTA_LEARNING_PATH_MEMBERS.DURATION_UNITS') THEN
922
923 hr_utility.set_location(' Leaving:'||l_proc, 42);
924 RAISE;
925 END IF;
926
927 hr_utility.set_location(' Leaving:'||l_proc, 44);
928
929 END chk_duration_units;
930 --
931
932
933 -- ----------------------------------------------------------------------------
934 -- |---------------------------< insert_validate >----------------------------|
935 -- ----------------------------------------------------------------------------
936 Procedure insert_validate
937 (p_effective_date in date
938 ,p_rec in ota_lpm_shd.g_rec_type
939 ) is
940 --
941 l_proc varchar2(72) := g_package||'insert_validate';
942 --
943 Begin
944 hr_utility.set_location('Entering:'||l_proc, 5);
945 --
946 -- Call all supporting business operations
947 --
948 hr_api.validate_bus_grp_id
949 (p_business_group_id => p_rec.business_group_id
950 ,p_associated_column1 => ota_lpm_shd.g_tab_nam
951 || '.BUSINESS_GROUP_ID');
952 --
953 -- After validating the set of important attributes,
954 -- if Multiple Message detection is enabled and at least
955 -- one error has been found then abort further validation.
956 --
957 hr_multi_message.end_validation_set;
958 hr_utility.set_location(' Step:'|| l_proc, 10);
959 --
960 -- Validate Dependent Attributes
961 --
962 --
963 /*
964 ota_lpm_bus.chk_learning_path_id(
965 p_learning_path_id => p_rec.learning_path_id
966 ,p_business_group_id => p_rec.business_group_id);
967 */
968
969 hr_utility.set_location(' Step:'|| l_proc, 20);
970
971 ota_lpm_bus.chk_notify_days_before_target(
972 p_learning_path_id => p_rec.learning_path_id
973 ,p_duration => p_rec.duration
974 ,p_notify_days_before_target => p_rec.notify_days_before_target);
975
976 --validations based on learning path source
977 IF is_catalog_lp(p_rec.learning_path_id) THEN
978
979 ota_lpm_bus.chk_activity_version_id (
980 p_learning_path_member_id => p_rec.learning_path_member_id
981 ,p_object_version_number => p_rec.object_version_number
982 ,p_activity_version_id => p_rec.activity_version_id
983 ,p_learning_path_id => p_rec.learning_path_id
984 ,p_business_group_id => p_rec.business_group_id);
985
986 ota_lpm_bus.chk_unique_course(
987 p_learning_path_member_id => p_rec.learning_path_member_id
988 ,p_object_version_number => p_rec.object_version_number
989 ,p_activity_version_id => p_rec.activity_version_id
990 ,p_learning_path_id => p_rec.learning_path_id);
991
992 ota_lpm_bus.chk_duration_units(
993 p_learning_path_member_id => p_rec.learning_path_member_id
994 ,p_object_version_number => p_rec.object_version_number
995 ,p_duration_units => p_rec.duration_units
996 ,p_effective_date => p_effective_date);
997
998 ota_lpm_bus.chk_duration(
999 p_learning_path_member_id => p_rec.learning_path_member_id
1000 ,p_object_version_number => p_rec.object_version_number
1001 ,p_duration => p_rec.duration
1002 ,p_duration_units => p_rec.duration_units);
1003
1004 ota_lpm_bus.chk_df(p_rec);
1005 END IF;
1006
1007 --
1008 hr_utility.set_location(' Leaving:'||l_proc, 10);
1009 End insert_validate;
1010 --
1011 -- ----------------------------------------------------------------------------
1012 -- |---------------------------< update_validate >----------------------------|
1013 -- ----------------------------------------------------------------------------
1014 Procedure update_validate
1015 (p_effective_date in date
1016 ,p_rec in ota_lpm_shd.g_rec_type
1017 ) is
1018 --
1019 l_proc varchar2(72) := g_package||'update_validate';
1020 --
1021 Begin
1022 hr_utility.set_location('Entering:'||l_proc, 5);
1023 --
1024 -- Call all supporting business operations
1025 --
1026 hr_api.validate_bus_grp_id
1030 --
1027 (p_business_group_id => p_rec.business_group_id
1028 ,p_associated_column1 => ota_lpm_shd.g_tab_nam
1029 || '.BUSINESS_GROUP_ID');
1031 -- After validating the set of important attributes,
1032 -- if Multiple Message detection is enabled and at least
1033 -- one error has been found then abort further validation.
1034 --
1035 hr_multi_message.end_validation_set;
1036 --
1037 -- Validate Dependent Attributes
1038 --
1039 chk_non_updateable_args
1040 (p_effective_date => p_effective_date
1041 ,p_rec => p_rec
1042 );
1043 --
1044 ota_lpm_bus.chk_notify_days_before_target(
1045 p_learning_path_id => p_rec.learning_path_id
1046 ,p_duration => p_rec.duration
1047 ,p_notify_days_before_target => p_rec.notify_days_before_target);
1048
1049 --
1050 --validations based on learning path source
1051 IF is_catalog_lp(p_rec.learning_path_id) THEN
1052
1053 ota_lpm_bus.chk_duration_units(
1054 p_learning_path_member_id => p_rec.learning_path_member_id
1055 ,p_object_version_number => p_rec.object_version_number
1056 ,p_duration_units => p_rec.duration_units
1057 ,p_effective_date => p_effective_date);
1058
1059 ota_lpm_bus.chk_duration(
1060 p_learning_path_member_id => p_rec.learning_path_member_id
1061 ,p_object_version_number => p_rec.object_version_number
1062 ,p_duration => p_rec.duration
1063 ,p_duration_units => p_rec.duration_units);
1064 --
1065 ota_lpm_bus.chk_df(p_rec);
1066 END IF;
1067
1068 --
1069 hr_utility.set_location(' Leaving:'||l_proc, 10);
1070 End update_validate;
1071 --
1072 -- ----------------------------------------------------------------------------
1073 -- |---------------------------< delete_validate >----------------------------|
1074 -- ----------------------------------------------------------------------------
1075 Procedure delete_validate
1076 (p_rec in ota_lpm_shd.g_rec_type
1077 ) is
1078 --
1079 l_proc varchar2(72) := g_package||'delete_validate';
1080 --
1081 Begin
1082 hr_utility.set_location('Entering:'||l_proc, 5);
1083 --
1084 -- Call all supporting business operations
1085 --
1086 hr_utility.set_location(' Leaving:'||l_proc, 10);
1087 End delete_validate;
1088
1089
1090 --
1091 end ota_lpm_bus;