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