[Home] [Help]
PACKAGE BODY: APPS.OTA_LPS_BUS
Source
1 Package Body ota_lps_bus as
2 /* $Header: otlpsrhi.pkb 120.0 2005/05/29 07:24:04 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_lps_bus.'; -- Global package name
9 g_dummy NUMBER(1);
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_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_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_paths lps
33 where lps.learning_path_id = p_learning_path_id
34 and pbg.business_group_id = lps.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_id'
51 ,p_argument_value => p_learning_path_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_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_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_paths lps
103 where lps.learning_path_id = p_learning_path_id
104 and pbg.business_group_id = lps.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_id'
120 ,p_argument_value => p_learning_path_id
121 );
122 --
123 if ( nvl(ota_lps_bus.g_learning_path_id, hr_api.g_number)
124 = p_learning_path_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_lps_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_lps_bus.g_learning_path_id := p_learning_path_id;
155 ota_lps_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_lps_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_id is not null) and (
199 nvl(ota_lps_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
200 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
201 nvl(ota_lps_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
202 nvl(p_rec.attribute1, hr_api.g_varchar2) or
203 nvl(ota_lps_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
204 nvl(p_rec.attribute2, hr_api.g_varchar2) or
205 nvl(ota_lps_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
206 nvl(p_rec.attribute3, hr_api.g_varchar2) or
207 nvl(ota_lps_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
208 nvl(p_rec.attribute4, hr_api.g_varchar2) or
209 nvl(ota_lps_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
210 nvl(p_rec.attribute5, hr_api.g_varchar2) or
211 nvl(ota_lps_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
212 nvl(p_rec.attribute6, hr_api.g_varchar2) or
213 nvl(ota_lps_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
214 nvl(p_rec.attribute7, hr_api.g_varchar2) or
215 nvl(ota_lps_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
216 nvl(p_rec.attribute8, hr_api.g_varchar2) or
217 nvl(ota_lps_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
218 nvl(p_rec.attribute9, hr_api.g_varchar2) or
219 nvl(ota_lps_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
220 nvl(p_rec.attribute10, hr_api.g_varchar2) or
221 nvl(ota_lps_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
222 nvl(p_rec.attribute11, hr_api.g_varchar2) or
223 nvl(ota_lps_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
224 nvl(p_rec.attribute12, hr_api.g_varchar2) or
225 nvl(ota_lps_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
226 nvl(p_rec.attribute13, hr_api.g_varchar2) or
227 nvl(ota_lps_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
228 nvl(p_rec.attribute14, hr_api.g_varchar2) or
229 nvl(ota_lps_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
230 nvl(p_rec.attribute15, hr_api.g_varchar2) or
231 nvl(ota_lps_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
232 nvl(p_rec.attribute16, hr_api.g_varchar2) or
233 nvl(ota_lps_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
234 nvl(p_rec.attribute17, hr_api.g_varchar2) or
235 nvl(ota_lps_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
236 nvl(p_rec.attribute18, hr_api.g_varchar2) or
237 nvl(ota_lps_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
238 nvl(p_rec.attribute19, hr_api.g_varchar2) or
239 nvl(ota_lps_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
240 nvl(p_rec.attribute20, hr_api.g_varchar2) ))
241 or (p_rec.learning_path_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_PATHS'
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_lps_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_lps_shd.api_updating
338 (p_learning_path_id => p_rec.learning_path_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 hr_utility.set_location(' Step:'|| l_proc, 10);
348 IF NVL(p_rec.business_group_id, hr_api.g_number) <>
349 NVL(ota_lps_shd.g_old_rec.business_group_id, hr_api.g_number) THEN
350 hr_api.argument_changed_error
351 (p_api_name => l_proc
352 ,p_argument => 'BUSINESS_GROUP_ID'
353 ,p_base_table => ota_lps_shd.g_tab_nam);
354 END IF;
355 --
356 hr_utility.set_location(' Step:'|| l_proc, 20);
357 IF NVL(p_rec.learning_path_id, hr_api.g_number) <>
358 NVL(ota_lps_shd.g_old_rec.learning_path_id, hr_api.g_number) THEN
359 hr_api.argument_changed_error
360 (p_api_name => l_proc
361 ,p_argument => 'LEARNING_PATH_ID'
362 ,p_base_table => ota_lps_shd.g_tab_nam);
363 END IF;
364 --
365 EXCEPTION
366
367 WHEN OTHERS THEN
368 RAISE;
369 End chk_non_updateable_args;
370 --
371 -- ----------------------------------------------------------------------------
372 -- |----------------------<chk_duration>---------------------------------------|
373 -- ----------------------------------------------------------------------------
374 --
375 PROCEDURE chk_duration
376 (p_learning_path_id IN ota_learning_paths.learning_path_id%TYPE
377 ,p_object_version_number IN ota_learning_paths.object_version_number%TYPE
378 ,p_duration IN ota_learning_paths.duration%TYPE
379 ,p_duration_units IN ota_learning_paths.duration_units%TYPE
380 ) IS
381 --
382 l_proc VARCHAR2(72) := g_package|| 'chk_duration';
383 l_exists VARCHAR2(1);
384 l_api_updating boolean;
385 --
386
387 BEGIN
388 --
389 -- check mandatory parameters have been set
390 --
391 --
392 hr_utility.set_location(' Step:'|| l_proc, 30);
393
394 IF hr_multi_message.no_exclusive_error
395 (p_check_column1 => 'OTA_LEARNING_PATHS.LEARNING_PATH_ID'
396 ,p_associated_column1 => 'OTA_LEARNING_PATHS.LEARNING_PATH_ID' ) THEN
397
398
399 hr_utility.set_location(' Step:'|| l_proc, 40);
400
401 l_api_updating := ota_lps_shd.api_updating
402 (p_learning_path_id => p_learning_path_id
403 ,p_object_version_number => p_object_version_number);
404 --
405 -- Check if anything is changing, or this is an insert
406 --
407 IF (l_api_updating AND
408 NVL(ota_lps_shd.g_old_rec.duration, hr_api.g_number) <>
409 NVL(p_duration, hr_api.g_number)
410 OR NVL(ota_lps_shd.g_old_rec.duration_units,hr_api.g_varchar2) <>
411 NVL(p_duration_units, hr_api.g_varchar2))
412 OR (NOT l_api_updating AND (p_duration IS NOT NULL OR p_duration_units IS NOT NULL)) THEN
413 --
414 -- check the duration is positive
415 --
416 hr_utility.set_location(' Step:'|| l_proc, 50);
417 IF (p_duration <= 0) THEN
418 hr_utility.set_location(' Step:'|| l_proc, 60);
419 fnd_message.set_name('OTA', 'OTA_13443_EVT_DURATION_NOT_0');
420 fnd_message.raise_error;
421 ELSE IF((p_duration IS NOT NULL AND p_duration_units IS NULL)
422 OR (p_duration IS NULL AND p_duration_units IS NOT NULL)) THEN
423 hr_utility.set_location(' Step:'|| l_proc, 60);
424 fnd_message.set_name('OTA', 'OTA_13881_NHS_COMB_INVALID');
425 fnd_message.raise_error;
426 END IF;
427 END IF;
428 END IF;
429 --
430 END IF;
431 hr_utility.set_location(' Leaving:'||l_proc, 90);
432
433 EXCEPTION
434
435 WHEN app_exception.application_exception THEN
436
437 IF hr_multi_message.exception_add
438 (p_associated_column1 => 'OTA_LEARNING_PATHS.DURATION') THEN
439
440 hr_utility.set_location(' Leaving:'||l_proc, 92);
441 RAISE;
442
443 END IF;
444
445 hr_utility.set_location(' Leaving:'||l_proc, 94);
446
447 END chk_duration;
448 --
449 -- ----------------------------------------------------------------------------
450 -- |---------------------------< chk_duration_units >------------------------|
451 -- ----------------------------------------------------------------------------
452 PROCEDURE chk_duration_units (p_learning_path_id IN number
453 ,p_object_version_number IN NUMBER
454 ,p_duration_units IN VARCHAR2
455 ,p_effective_date IN date) IS
456
457 --
458 l_proc VARCHAR2(72) := g_package||'chk_duration_units';
459 l_api_updating boolean;
460
461 BEGIN
462 hr_utility.set_location(' Leaving:'||l_proc, 10);
463 --
464 -- check mandatory parameters has been set
465 --
466 hr_api.mandatory_arg_error
467 (p_api_name => l_proc
468 ,p_argument => 'effective_date'
469 ,p_argument_value => p_effective_date);
470
471 l_api_updating := ota_lps_shd.api_updating
472 (p_learning_path_id => p_learning_path_id
473 ,p_object_version_number => p_object_version_number);
474
475
476 IF ((l_api_updating AND
477 NVL(ota_lps_shd.g_old_rec.duration_units,hr_api.g_varchar2) <>
478 NVL(p_duration_units, hr_api.g_varchar2))
479 OR NOT l_api_updating AND p_duration_units IS NOT NULL) THEN
480
481 hr_utility.set_location(' Leaving:'||l_proc, 20);
482 --
483
484 IF p_duration_units IS NOT NULL THEN
485 IF hr_api.not_exists_in_hr_lookups
486 (p_effective_date => p_effective_date
487 ,p_lookup_type => 'OTA_DURATION_UNITS'
488 ,p_lookup_code => p_duration_units) THEN
489 fnd_message.set_name('OTA','OTA_13882_NHS_DURATION_INVALID');
490 fnd_message.raise_error;
491 END IF;
492 hr_utility.set_location(' Leaving:'||l_proc, 30);
493
494 END IF;
495
496 END IF;
497 hr_utility.set_location(' Leaving:'||l_proc, 40);
498
499 EXCEPTION
500
501 WHEN app_exception.application_exception THEN
502
503 IF hr_multi_message.exception_add
504 (p_associated_column1 => 'OTA_LEARNING_PATHS.DURATION_UNITS') THEN
505
506 hr_utility.set_location(' Leaving:'||l_proc, 42);
507 RAISE;
508 END IF;
509
510 hr_utility.set_location(' Leaving:'||l_proc, 44);
511
512 END chk_duration_units;
513 --
514 -- ----------------------------------------------------------------------------
515 -- |-----------------------<chk_notify_days_before_target >--------------------|
516 -- ----------------------------------------------------------------------------
517 PROCEDURE chk_notify_days_before_target(p_path_source_code IN ota_learning_paths.path_source_code%TYPE
518 ,p_duration IN ota_learning_paths.duration%TYPE
519 ,p_notify_days_before_target IN ota_learning_paths.notify_days_before_target%TYPE)
520 IS
521 --
522 l_proc VARCHAR2(72) := g_package||'chk_notify_days_before_target';
523 l_api_updating boolean;
524
525 BEGIN
526 hr_utility.set_location(' Leaving:'||l_proc, 10);
527 --
528 IF p_path_source_code = 'CATALOG' THEN
529 IF p_duration IS NOT NULL AND p_notify_days_before_target IS NOT NULL THEN
530 -- Modified for Bug#3861864
531 IF p_duration <= p_notify_days_before_target THEN
532 fnd_message.set_name('OTA','OTA_13079_LPS_NOTIFY_TARGET');
533 fnd_message.raise_error;
534 END IF;
535 ELSIF p_duration IS NULL and p_notify_days_before_target IS NOT NULL THEN
536 fnd_message.set_name('OTA','OTA_13083_LP_CT_NOT_ERR');
537 fnd_message.raise_error;
538 END IF;
539 END IF;
540
541 IF p_notify_days_before_target < 0 THEN
542 fnd_message.set_name('OTA','OTA_443368_POSITIVE_NUMBER');
543 fnd_message.raise_error;
544 END IF;
545
546 hr_utility.set_location(' Leaving:'||l_proc, 40);
547
548 EXCEPTION
549
550 WHEN app_exception.application_exception THEN
551
552 IF hr_multi_message.exception_add
553 (p_associated_column1 => 'OTA_LEARNING_PATHS.NOTIFY_DAYS_BEFORE_TARGET') THEN
554
555 hr_utility.set_location(' Leaving:'||l_proc, 42);
556 RAISE;
557 END IF;
558
559 hr_utility.set_location(' Leaving:'||l_proc, 44);
560
561 END chk_notify_days_before_target;
562 --
563 -- ----------------------------------------------------------------------------
564 -- |---------------------------< chk_path_source_code>------------------------|
565 -- ----------------------------------------------------------------------------
566 PROCEDURE chk_path_source_code(p_learning_path_id IN number
567 ,p_object_version_number IN NUMBER
568 ,p_path_source_code IN VARCHAR2
569 ,p_effective_date IN date) IS
570
571 --
572 l_proc VARCHAR2(72) := g_package||'chk_path_source_code';
573 l_api_updating boolean;
574
575 BEGIN
576 hr_utility.set_location(' Leaving:'||l_proc, 10);
577 --
578 -- check mandatory parameters has been set
579 --
580 hr_api.mandatory_arg_error
581 (p_api_name => l_proc
582 ,p_argument => 'effective_date'
583 ,p_argument_value => p_effective_date);
584
585 l_api_updating := ota_lps_shd.api_updating
586 (p_learning_path_id => p_learning_path_id
587 ,p_object_version_number => p_object_version_number);
588
589 IF ((l_api_updating AND
590 NVL(ota_lps_shd.g_old_rec.path_source_code,hr_api.g_varchar2) <>
591 NVL(p_path_source_code, hr_api.g_varchar2))
592 OR NOT l_api_updating AND p_path_source_code IS NOT NULL) THEN
593
594 hr_utility.set_location(' Leaving:'||l_proc, 20);
595 --
596
597 IF p_path_source_code IS NOT NULL THEN
598 IF hr_api.not_exists_in_hr_lookups
599 (p_effective_date => p_effective_date
600 ,p_lookup_type => 'OTA_TRAINING_PLAN_SOURCE'
601 ,p_lookup_code => p_path_source_code) THEN
602 fnd_message.set_name('OTA','OTA_13176_TPS_PLN_SRC_INVLD');
603 fnd_message.raise_error;
604 END IF;
605 hr_utility.set_location(' Leaving:'||l_proc, 30);
606
607 END IF;
608
609 END IF;
610 hr_utility.set_location(' Leaving:'||l_proc, 40);
611
612 EXCEPTION
613
614 WHEN app_exception.application_exception THEN
615
616 IF hr_multi_message.exception_add
617 (p_associated_column1 => 'OTA_LEARNING_PATHS.PATH_SOURCE_CODE') THEN
618
619 hr_utility.set_location(' Leaving:'||l_proc, 42);
620 RAISE;
621 END IF;
622
623 hr_utility.set_location(' Leaving:'||l_proc, 44);
624
625 END chk_path_source_code;
626 --
627 --
628 -- ----------------------------------------------------------------------------
629 -- |---------------------------< chk_source_function_code >------------------------|
630 -- ----------------------------------------------------------------------------
631 PROCEDURE chk_source_function_code (p_learning_path_id IN number
632 ,p_object_version_number IN NUMBER
633 ,p_source_function_code IN VARCHAR2
634 ,p_effective_date IN date) IS
635
636 --
637 l_proc VARCHAR2(72) := g_package||'chk_source_function_code';
638 l_api_updating boolean;
639
640 BEGIN
641 hr_utility.set_location(' Leaving:'||l_proc, 10);
642 --
643 -- check mandatory parameters has been set
644 --
645 hr_api.mandatory_arg_error
646 (p_api_name => l_proc
647 ,p_argument => 'effective_date'
648 ,p_argument_value => p_effective_date);
649
650 l_api_updating := ota_lps_shd.api_updating
651 (p_learning_path_id => p_learning_path_id
652 ,p_object_version_number => p_object_version_number);
653
654
655 IF ((l_api_updating AND
656 NVL(ota_lps_shd.g_old_rec.source_function_code,hr_api.g_varchar2) <>
657 NVL(p_source_function_code, hr_api.g_varchar2))
658 OR NOT l_api_updating AND p_source_function_code IS NOT NULL) THEN
659
660 hr_utility.set_location(' Leaving:'||l_proc, 20);
661 --
662
663 IF p_source_function_code IS NOT NULL THEN
664 IF hr_api.not_exists_in_hr_lookups
665 (p_effective_date => p_effective_date
666 ,p_lookup_type => 'OTA_PLAN_COMPONENT_SOURCE'
667 ,p_lookup_code => p_source_function_code) THEN
668 fnd_message.set_name('OTA','OTA_13178_TPM_SRC_FUNC_INVLD');
669 fnd_message.raise_error;
670 END IF;
671 hr_utility.set_location(' Leaving:'||l_proc, 30);
672
673 END IF;
674
675 END IF;
676 hr_utility.set_location(' Leaving:'||l_proc, 40);
677
678 EXCEPTION
679
680 WHEN app_exception.application_exception THEN
681
682 IF hr_multi_message.exception_add
683 (p_associated_column1 => 'OTA_LEARNING_PATHS.SOURCE_FUNCTION_CODE') THEN
684
685 hr_utility.set_location(' Leaving:'||l_proc, 42);
686 RAISE;
687 END IF;
688
689 hr_utility.set_location(' Leaving:'||l_proc, 44);
690
691 END chk_source_function_code;
692 --
693 -- ----------------------------------------------------------------------------
694 -- |---------------------------< chk_competency_update_level >------------------------|
695 -- ----------------------------------------------------------------------------
696 PROCEDURE chk_competency_update_level (p_learning_path_id IN number
697 ,p_object_version_number IN NUMBER
698 ,p_competency_update_level IN VARCHAR2
699 ,p_effective_date IN date) IS
700
701 --
702 l_proc VARCHAR2(72) := g_package||'chk_competency_update_level';
703 l_api_updating boolean;
704
705 BEGIN
706 hr_utility.set_location(' Leaving:'||l_proc, 10);
707 --
708 -- check mandatory parameters has been set
709 --
710 hr_api.mandatory_arg_error
711 (p_api_name => l_proc
712 ,p_argument => 'effective_date'
713 ,p_argument_value => p_effective_date);
714
715 l_api_updating := ota_lps_shd.api_updating
716 (p_learning_path_id => p_learning_path_id
717 ,p_object_version_number => p_object_version_number);
718
719
720 IF ((l_api_updating AND
721 NVL(ota_lps_shd.g_old_rec.competency_update_level,hr_api.g_varchar2) <>
722 NVL(p_competency_update_level, hr_api.g_varchar2))
723 OR NOT l_api_updating AND p_competency_update_level IS NOT NULL) THEN
724
725 hr_utility.set_location(' Leaving:'||l_proc, 20);
726 --
727
728 IF p_competency_update_level IS NOT NULL THEN
729 IF hr_api.not_exists_in_hr_lookups
730 (p_effective_date => p_effective_date
731 ,p_lookup_type => 'OTA_COMPETENCY_UPDATE_LEVEL'
732 ,p_lookup_code => p_competency_update_level) THEN
733 fnd_message.set_name('OTA','OTA_443411_COMP_UPD_LEV_INVLD');
734 fnd_message.raise_error;
735 END IF;
736 hr_utility.set_location(' Leaving:'||l_proc, 30);
737
738 END IF;
739
740 END IF;
741 hr_utility.set_location(' Leaving:'||l_proc, 40);
742
743 EXCEPTION
744
745 WHEN app_exception.application_exception THEN
746
747 IF hr_multi_message.exception_add
748 (p_associated_column1 => 'OTA_LEARNING_PATHS.competency_update_level') THEN
749
750 hr_utility.set_location(' Leaving:'||l_proc, 42);
751 RAISE;
752 END IF;
753
754 hr_utility.set_location(' Leaving:'||l_proc, 44);
755
756 END chk_competency_update_level;
757
758 --
759
760 -- ----------------------------------------------------------------------------
761 -- |----------------------------< check_start_end_dates >-----------------|
762 -- ----------------------------------------------------------------------------
763 -- PUBLIC
764 -- Description:
765 -- Validates the startdate and enddate.
766 -- Startdate must be less than, or equal to, enddate.
767 --
768 Procedure check_start_end_dates
769 ( p_start_date IN DATE
770 ,p_end_date in date
771 ) is
772 --
773 v_proc varchar2(72) := g_package||'check_start_end_dates';
774 --
775 Begin
776 --
777 hr_utility.set_location('Entering:'|| v_proc, 5);
778 --
779 ota_general.check_start_end_dates( p_start_date, p_end_date);
780 --
781 hr_utility.set_location(' Leaving:'|| v_proc, 10);
782
783 Exception
784 WHEN app_exception.application_exception THEN
785
786 IF hr_multi_message.exception_add(
787 p_associated_column1 => 'OTA_LEARNING_PATHS.START_DATE_ACTIVE')
788 -- ,p_associated_column2 => 'OTA_LEARNING_PATHS.END_DATE_ACTIVE')
789 THEN
790
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 --
797 End check_start_end_dates;
798 --
799
800 -- ----------------------------------------------------------------------------
801 -- |----------------------------< check_lp_course_dates >-----------------|
802 -- ----------------------------------------------------------------------------
803 -- PUBLIC
804 -- Description:
805 -- Validates the LP dates with Course Dates
806 -- Startdate must be less than, or equal to, enddate.
807 --
808 Procedure check_lp_course_dates
809 (
810 p_learning_path_id IN NUMBER
811 ,p_start_date IN DATE
812 ,p_end_date IN DATE
813 ) is
814 --
815 v_proc varchar2(72) := g_package||'check_lp_course_dates';
816
817 l_start_date_active DATE;
818 l_end_date_active DATE;
819 l_course_id NUMBER;
820 l_upd_start_date BOOLEAN;
821 l_upd_end_date BOOLEAN;
822
823 CURSOR csr_lp_course_dates(l_start_date_active DATE, l_end_date_active DATE)IS
824 SELECT 1
825 FROM ota_learning_path_members lpm,
826 ota_activity_versions tav
827 WHERE tav.activity_version_id = lpm.activity_version_id
828 AND lpm.learning_path_id = p_learning_path_id
829 AND (( tav.end_date IS NOT NULL AND l_start_date_active > tav.end_date)
830 OR (l_end_date_active IS NOT NULL AND tav.start_date > l_end_date_active));
831 --
832 Begin
833 --
834 hr_utility.set_location('Entering:'|| v_proc, 5);
835 --
836 IF hr_multi_message.no_exclusive_error
837 (p_check_column1 => 'OTA_LEARNING_PATHS.START_DATE_ACTIVE'
838 ,p_check_column2 => 'OTA_LEARNING_PATHS.END_DATE_ACTIVE'
839 ,p_associated_column1 => 'OTA_LEARNING_PATHS.START_DATE_ACTIVE'
840 ,p_associated_column2 => 'OTA_LEARNING_PATHS.END_DATE_ACTIVE' ) THEN
841
842 IF (NVL(ota_lps_shd.g_old_rec.start_date_active, hr_api.g_date) <>
843 NVL( p_start_date, hr_api.g_date )) THEN
844 l_upd_start_date := TRUE;
845 l_start_date_active := p_start_date;
846 ELSE
847 l_upd_start_date := FALSE;
848 l_start_date_active := ota_lps_shd.g_old_rec.start_date_active;
849 END IF;
850
851 IF (NVL(ota_lps_shd.g_old_rec.end_date_active, hr_api.g_date) <>
852 NVL( p_end_date, hr_api.g_date )) THEN
853 l_upd_end_date := TRUE;
854 l_end_date_active := p_end_date;
855 ELSE
856 l_upd_end_date := FALSE;
857 l_end_date_active := ota_lps_shd.g_old_rec.end_date_active;
858 END IF;
859
860 IF (l_upd_start_date OR l_upd_end_date) THEN
861
862 OPEN csr_lp_course_dates(l_start_date_active, l_end_date_active);
863 FETCH csr_lp_course_dates INTO l_course_id;
864
865 IF csr_lp_course_dates%FOUND THEN
866 fnd_message.set_name('OTA', 'OTA_443062_LP_CRS_DTS_INVALID');
867 fnd_message.raise_error;
868 CLOSE csr_lp_course_dates;
869 ELSE
870 CLOSE csr_lp_course_dates;
871 END IF;
872 END IF;
873 END IF;
874
875 --
876 hr_utility.set_location(' Leaving:'|| v_proc, 10);
877
878 Exception
879 WHEN app_exception.application_exception THEN
880
881 IF l_upd_start_date THEN
882 IF hr_multi_message.exception_add(
883 p_associated_column1 => 'OTA_LEARNING_PATHS.START_DATE_ACTIVE')
884 THEN
885
886 hr_utility.set_location(' Leaving:'||v_proc, 22);
887 RAISE;
888 END IF;
889 ELSIF l_upd_end_date THEN
890 IF hr_multi_message.exception_add(
891 p_associated_column1 => 'OTA_LEARNING_PATHS.END_DATE_ACTIVE')
892 THEN
893
894 hr_utility.set_location(' Leaving:'||v_proc, 22);
895 RAISE;
896 END IF;
897 END IF;
898 hr_utility.set_location(' Leaving:'||v_proc, 25);
899 --
900 End check_lp_course_dates;
901 --
902 -- ----------------------------------------------------------------------------
903 -- |--------------------------< check_category_dates >------------------------|
904 -- ----------------------------------------------------------------------------
905 --
906 -- PUBLIC
907 -- Description:
908 -- Validates the startdate and enddate with respect to category dates.
909 --
910 Procedure check_category_dates
911 (
912 p_learning_path_id in number
913 , p_object_version_number in number
914 ,p_start_date in date
915 ,p_end_date in date
916 ) is
917 --
918 -- Declare cursors and local variables
919 --
920 -- Cursor to get value if parent category is already exits in child hierarchy of base category
921
922 CURSOR cur_cat_start_end_date is
923 select
924 ctu.start_date_active,
925 nvl(ctu.end_date_active, to_date ('31-12-4712', 'DD-MM-YYYY'))
926 from
927 ota_lp_cat_inclusions lci,
928 ota_category_usages ctu
929 where
930 ctu.category_usage_id = lci.category_usage_id
931 and lci.learning_path_id = p_learning_path_id
932 and lci.primary_flag = 'Y';
933 --
934 -- Variables for API Boolean parameters
935 l_proc varchar2(72) := g_package ||'check_category_dates';
936 l_cat_start_date date;
937 l_cat_end_date date;
938 l_start_date_active date;
939 l_end_date_active date;
940
941 Begin
942 hr_utility.set_location(' Entering:' || l_proc,10);
943 --
944 IF hr_multi_message.no_exclusive_error
945 (p_check_column1 => 'OTA_LEARNING_PATHS.START_DATE_ACTIVE'
946 ,p_check_column2 => 'OTA_LEARNING_PATHS.END_DATE_ACTIVE'
947 ,p_associated_column1 => 'OTA_LEARNING_PATHS.START_DATE_ACTIVE'
948 ,p_associated_column2 => 'OTA_LEARNING_PATHS.END_DATE_ACTIVE'
949 ) THEN
950 --
951 OPEN cur_cat_start_end_date;
952 FETCH cur_cat_start_end_date into l_cat_start_date, l_cat_end_date;
953
954 IF (NVL(ota_lps_shd.g_old_rec.start_date_active, hr_api.g_date) <>
955 NVL( p_start_date, hr_api.g_date )) THEN
956 l_start_date_active := p_start_date;
957 ELSE
958 l_start_date_active := ota_lps_shd.g_old_rec.start_date_active;
959 END IF;
960
961 IF (NVL(ota_lps_shd.g_old_rec.end_date_active, hr_api.g_date) <>
962 NVL( p_end_date, hr_api.g_date )) THEN
963 l_end_date_active := p_end_date;
964 ELSE
965 l_end_date_active := ota_lps_shd.g_old_rec.end_date_active;
966 END IF;
967
968 IF cur_cat_start_end_date%FOUND THEN
969 CLOSE cur_cat_start_end_date;
970 IF ( l_cat_start_date > l_start_date_active
971 or l_cat_end_date < nvl(l_end_date_active, to_date ('31-12-4712', 'DD-MM-YYYY'))
972 ) THEN
973 --
974 fnd_message.set_name ( 'OTA','OTA_443382_LP_OUT_OF_CAT_DATES');
975 fnd_message.raise_error;
976 --
977 End IF;
978 ELSE
979 CLOSE cur_cat_start_end_date;
980 End IF;
981 End IF;
982 --
983 hr_utility.set_location(' Leaving:' || l_proc,10);
984 Exception
985 when app_exception.application_exception then
986 IF hr_multi_message.exception_add
987 (p_associated_column1 => 'OTA_LEARNING_PATHS.START_DATE_ACTIVE'
988 ,p_associated_column2 => 'OTA_LEARNING_PATHS.END_DATE_ACTIVE'
989 ) THEN
990 hr_utility.set_location(' Leaving:'|| l_proc,20);
991 raise;
992 END IF;
993
994 hr_utility.set_location(' Leaving:'|| l_proc,30);
995 --
996 End check_category_dates;
997 --
998 --
999 -- ----------------------------------------------------------------------------
1000 -- |----------------------< get_path_source_code >-----------------------------|
1001 -- ----------------------------------------------------------------------------
1002 --
1003 FUNCTION get_path_source_code
1004 (p_learning_path_id IN ota_learning_paths.learning_path_id%TYPE
1005 ) RETURN VARCHAR2 IS
1006 --
1007 l_path_source_code ota_learning_paths.path_source_code%TYPE;
1008 l_proc VARCHAR2(72) := g_package||'get_path_source_code';
1009
1010 CURSOR csr_get_path_source IS
1011 SELECT path_source_code
1012 FROM ota_learning_paths lps
1013 WHERE lps.learning_path_id = p_learning_path_id;
1014
1015
1016 BEGIN
1017 --
1018 -- check mandatory parameters have been set
1019 --
1020 hr_utility.set_location('Entering:'||l_proc, 5);
1021 hr_api.mandatory_arg_error
1022 (p_api_name => l_proc
1023 ,p_argument => 'p_learning_path_id'
1024 ,p_argument_value => p_learning_path_id
1025 );
1026 --
1027 OPEN csr_get_path_source;
1028 FETCH csr_get_path_source INTO l_path_source_code;
1029 CLOSE csr_get_path_source;
1030 hr_utility.set_location(' Leaving:'||l_proc, 20);
1031 --
1032 RETURN l_path_source_code;
1033 END get_path_source_code;
1034 --
1035 -- ----------------------------------------------------------------------------
1036 -- |----------------------<chk_lp_enrollments_exist>---------------------------|
1037 -- ----------------------------------------------------------------------------
1038 --
1039 FUNCTION chk_lp_enrollments_exist
1040 (p_learning_path_id IN ota_learning_paths.learning_path_id%TYPE
1041 ) RETURN BOOLEAN IS
1042 --
1043 l_exists BOOLEAN := FALSE;
1044 l_proc VARCHAR2(72) := g_package||'chk_lp_enrollments_exist';
1045
1046 CURSOR csr_del_lp_id IS
1047 SELECT 1
1048 FROM ota_lp_enrollments lpm
1049 WHERE lpm.learning_path_id = p_learning_path_id;
1050
1051 BEGIN
1052 --
1053 -- check mandatory parameters have been set
1054 --
1055 hr_utility.set_location('Entering:'||l_proc, 5);
1056 hr_api.mandatory_arg_error
1057 (p_api_name => l_proc
1058 ,p_argument => 'p_learning_path_id'
1059 ,p_argument_value => p_learning_path_id
1060 );
1061 --
1062 -- Check that the code can be deleted
1063 --
1064 OPEN csr_del_lp_id;
1065 FETCH csr_del_lp_id INTO g_dummy;
1066 IF csr_del_lp_id%FOUND THEN
1067 l_exists := TRUE;
1068 hr_utility.set_location(' Step:'|| l_proc, 10);
1069 END IF;
1070 CLOSE csr_del_lp_id;
1071 hr_utility.set_location(' Leaving:'||l_proc, 20);
1072 --
1073 RETURN l_exists;
1074 END chk_lp_enrollments_exist;
1075 --
1076
1077 -- ----------------------------------------------------------------------------
1078 -- |---------------------------< insert_validate >----------------------------|
1079 -- ----------------------------------------------------------------------------
1080 Procedure insert_validate
1081 (p_effective_date in date
1082 ,p_rec in ota_lps_shd.g_rec_type
1083 ) is
1084 --
1085 l_proc varchar2(72) := g_package||'insert_validate';
1086 --
1087 Begin
1088 hr_utility.set_location('Entering:'||l_proc, 5);
1089 --
1090 -- Call all supporting business operations
1091 --
1092 hr_api.validate_bus_grp_id
1093 (p_business_group_id => p_rec.business_group_id
1094 ,p_associated_column1 => ota_lps_shd.g_tab_nam
1095 || '.BUSINESS_GROUP_ID');
1096 --
1097 -- After validating the set of important attributes,
1098 -- if Multiple Message detection is enabled and at least
1099 -- one error has been found then abort further validation.
1100 --
1101 -- hr_multi_message.end_validation_set;
1102 --
1103 -- Validate Dependent Attributes
1104 --
1105 --
1106 hr_utility.set_location(' Step:'|| l_proc, 10);
1107 ota_lps_bus.chk_path_source_code(
1108 p_learning_path_id => p_rec.learning_path_id
1109 ,p_object_version_number => p_rec.object_version_number
1110 ,p_path_source_code => p_rec.path_source_code
1111 ,p_effective_date => p_effective_date);
1112
1113 ota_lps_bus.chk_notify_days_before_target(
1114 p_path_source_code => p_rec.path_source_code
1115 ,p_duration => p_rec.duration
1116 ,p_notify_days_before_target => p_rec.notify_days_before_target);
1117
1118 ota_lps_bus.chk_competency_update_level (p_learning_path_id => p_rec.learning_path_id
1119 ,p_object_version_number => p_rec.object_version_number
1120 ,p_competency_update_level => p_rec.competency_update_level
1121 ,p_effective_date => p_effective_date);
1122
1123 IF p_rec.path_source_code = 'CATALOG' THEN
1124
1125
1126 ota_lps_bus.chk_duration_units(
1127 p_learning_path_id => p_rec.learning_path_id
1128 ,p_object_version_number => p_rec.object_version_number
1129 ,p_duration_units => p_rec.duration_units
1130 ,p_effective_date => p_effective_date);
1131
1132 ota_lps_bus.chk_duration(
1133 p_learning_path_id => p_rec.learning_path_id
1134 ,p_object_version_number => p_rec.object_version_number
1135 ,p_duration => p_rec.duration
1136 ,p_duration_units => p_rec.duration_units);
1137
1138 ota_lps_bus.check_start_end_dates(p_rec.start_date_active
1139 ,p_rec.end_date_active);
1140
1141 -- enable dff validation for only Catalog Learning Paths
1142 ota_lps_bus.chk_df(p_rec);
1143
1144 ELSIF p_rec.path_source_code = 'TALENT_MGMT' THEN
1145
1146 ota_lps_bus.chk_source_function_code(
1147 p_learning_path_id => p_rec.learning_path_id
1148 ,p_object_version_number => p_rec.object_version_number
1149 ,p_source_function_code => p_rec.source_function_code
1150 ,p_effective_date => p_effective_date);
1151
1152 END IF;
1153 --
1154 hr_multi_message.end_validation_set;
1155 --
1156 --
1157 hr_utility.set_location(' Leaving:'||l_proc, 10);
1158 End insert_validate;
1159 --
1160 -- ----------------------------------------------------------------------------
1161 -- |---------------------------< update_validate >----------------------------|
1162 -- ----------------------------------------------------------------------------
1163 Procedure update_validate
1164 (p_effective_date in date
1165 ,p_rec in ota_lps_shd.g_rec_type
1166 ) is
1167 --
1168 l_proc varchar2(72) := g_package||'update_validate';
1169
1170 l_duration_changed BOOLEAN
1171 := ota_general.value_changed(ota_lps_shd.g_old_rec.duration,
1172 p_rec.duration);
1173 l_duration_units_changed BOOLEAN
1174 := ota_general.value_changed(ota_lps_shd.g_old_rec.duration_units,
1175 p_rec.duration_units);
1176
1177 l_start_date_active_changed BOOLEAN
1178 := ota_general.value_changed(ota_lps_shd.g_old_rec.start_date_active,
1179 p_rec.start_date_active);
1180
1181
1182 --
1183 Begin
1184 hr_utility.set_location('Entering:'||l_proc, 5);
1185 --
1186 -- Call all supporting business operations
1187 --
1188 hr_api.validate_bus_grp_id
1189 (p_business_group_id => p_rec.business_group_id
1190 ,p_associated_column1 => ota_lps_shd.g_tab_nam
1191 || '.BUSINESS_GROUP_ID');
1192 --
1193 -- After validating the set of important attributes,
1194 -- if Multiple Message detection is enabled and at least
1195 -- one error has been found then abort further validation.
1196 --
1197 hr_multi_message.end_validation_set;
1198 --
1199 -- Validate Dependent Attributes
1200 --
1201 chk_non_updateable_args
1202 (p_effective_date => p_effective_date
1203 ,p_rec => p_rec
1204 );
1205 --
1206 ota_lps_bus.chk_path_source_code(
1207 p_learning_path_id => p_rec.learning_path_id
1208 ,p_object_version_number => p_rec.object_version_number
1209 ,p_path_source_code => p_rec.path_source_code
1210 ,p_effective_date => p_effective_date);
1211
1212 ota_lps_bus.chk_notify_days_before_target(
1213 p_path_source_code => p_rec.path_source_code
1214 ,p_duration => p_rec.duration
1215 ,p_notify_days_before_target => p_rec.notify_days_before_target);
1216
1217 ota_lps_bus.chk_competency_update_level (p_learning_path_id => p_rec.learning_path_id
1218 ,p_object_version_number => p_rec.object_version_number
1219 ,p_competency_update_level => p_rec.competency_update_level
1220 ,p_effective_date => p_effective_date);
1221
1222 IF p_rec.path_source_code = 'CATALOG' THEN
1223
1224 ota_lps_bus.chk_duration_units(
1225 p_learning_path_id => p_rec.learning_path_id
1226 ,p_object_version_number => p_rec.object_version_number
1227 ,p_duration_units => p_rec.duration_units
1228 ,p_effective_date => p_effective_date);
1229
1230 ota_lps_bus.chk_duration(
1231 p_learning_path_id => p_rec.learning_path_id
1232 ,p_object_version_number => p_rec.object_version_number
1233 ,p_duration => p_rec.duration
1234 ,p_duration_units => p_rec.duration_units);
1235
1236 check_start_end_dates(p_rec.start_date_active
1237 ,p_rec.end_date_active);
1238 check_lp_course_dates(p_rec.learning_path_id
1239 ,p_rec.start_date_active
1240 ,p_rec.end_date_active);
1241
1242 check_category_dates(p_learning_path_id => p_rec.learning_path_id
1243 ,p_object_version_number => p_rec.object_version_number
1244 ,p_start_date => p_rec.start_date_active
1245 ,p_end_date => p_rec.end_date_active);
1246
1247 IF l_duration_changed OR
1248 l_duration_units_changed OR
1249 l_start_date_active_changed THEN
1250 IF chk_lp_enrollments_exist(p_learning_path_id => p_rec.learning_path_id) THEN
1251 fnd_message.set_name('OTA','OTA_13063_LPM_UPD_ERR');
1252 fnd_message.raise_error;
1253 END IF;
1254 END IF;
1255 --
1256 -- enable dff validation for only Catalog Learning Paths
1257 ota_lps_bus.chk_df(p_rec);
1258
1259 ELSIF p_rec.path_source_code = 'TALENT_MGMT' THEN
1260
1261 ota_lps_bus.chk_source_function_code(
1262 p_learning_path_id => p_rec.learning_path_id
1263 ,p_object_version_number => p_rec.object_version_number
1264 ,p_source_function_code => p_rec.source_function_code
1265 ,p_effective_date => p_effective_date);
1266
1267 END IF;
1268
1269 --
1270 hr_utility.set_location(' Leaving:'||l_proc, 10);
1271 End update_validate;
1272 --
1273 -- ----------------------------------------------------------------------------
1274 -- |---------------------------< delete_validate >----------------------------|
1275 -- ----------------------------------------------------------------------------
1276 Procedure delete_validate
1277 (p_rec in ota_lps_shd.g_rec_type
1278 ) is
1279 --
1280 l_proc varchar2(72) := g_package||'delete_validate';
1281 --
1282 Begin
1283 hr_utility.set_location('Entering:'||l_proc, 5);
1284 --
1285 -- Call all supporting business operations
1286 IF get_path_source_code(ota_lps_shd.g_old_rec.learning_path_id) = 'CATALOG' THEN
1287
1288 IF chk_lp_enrollments_exist(
1289 p_learning_path_id => ota_lps_shd.g_old_rec.learning_path_id) THEN
1290 fnd_message.set_name('OTA','OTA_443383_LP_TP_EXISTS');
1291 fnd_message.raise_error;
1292 END IF;
1293
1294 END IF;
1295 --
1296 hr_utility.set_location(' Leaving:'||l_proc, 10);
1297 End delete_validate;
1298
1299
1300 --
1301 end ota_lps_bus;