[Home] [Help]
PACKAGE BODY: APPS.OTA_TPM_BUS1
Source
1 PACKAGE BODY OTA_TPM_BUS1 AS
2 /* $Header: ottpmrhi.pkb 120.1 2005/12/14 15:33:09 asud noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package VARCHAR2(33) := ' ota_tpm_bus1.'; -- Global package name
9 --
10 --
11 -- ----------------------------------------------------------------------------
12 -- |----------------------<chk_training_plan_id>-------------------------------|
13 -- ----------------------------------------------------------------------------
14 --
15 PROCEDURE chk_training_plan_id
16 (p_training_plan_id IN ota_training_plan_members.training_plan_id%TYPE
17 ,p_business_group_id IN ota_training_plan_members.business_group_id%TYPE
18 ) IS
19 --
20 l_exists VARCHAR2(1);
21 l_proc VARCHAR2(72) := g_package|| 'chk_training_plan_id';
22 --
23 CURSOR csr_training_plan_id IS
24 SELECT NULL
25 FROM OTA_TRAINING_PLANS
26 WHERE training_plan_id = p_training_plan_id
27 AND business_group_id = p_business_group_id;
28 BEGIN
29 --
30 -- check mandatory parameters have been set
31 --
32 hr_utility.set_location(' Step:'|| l_proc, 20);
33 hr_api.mandatory_arg_error
34 (p_api_name => l_proc
35 ,p_argument => 'p_training_plan_id'
36 ,p_argument_value => p_training_plan_id
37 );
38 --
39 hr_utility.set_location(' Step:'|| l_proc, 30);
40 hr_api.mandatory_arg_error
41 (p_api_name => l_proc
42 ,p_argument => 'p_business_group_id'
43 ,p_argument_value => p_business_group_id
44 );
45 --
46 --
47 hr_utility.set_location(' Step:'|| l_proc, 50);
48 OPEN csr_training_plan_id;
49 FETCH csr_training_plan_id INTO l_exists;
50 IF csr_training_plan_id%NOTFOUND THEN
51 CLOSE csr_training_plan_id;
52 hr_utility.set_location(' Step:'|| l_proc, 60);
53 fnd_message.set_name('OTA', 'OTA_13828_TPC_NO_TRAINING_PLAN');
54 fnd_message.raise_error;
55 ELSE
56 hr_utility.set_location(' Step:'|| l_proc, 80);
57 CLOSE csr_training_plan_id;
58 END IF;
59 --
60 hr_utility.set_location(' Leaving:'||l_proc, 90);
61
62 EXCEPTION
63
64 WHEN app_exception.application_exception THEN
65
66 IF hr_multi_message.exception_add
67 (p_associated_column1 => 'OTA_TRAINING_PLAN_MEMBERS.TRAINING_PLAN_ID') THEN
68
69 hr_utility.set_location(' Leaving:'||l_proc, 92);
70 RAISE;
71 END IF;
72
73 hr_utility.set_location(' Leaving:'||l_proc, 94);
74
75 END chk_training_plan_id;
76 -- ----------------------------------------------------------------------------
77 -- |----------------------<chk_activity_definition_id>------------------------|
78 -- ----------------------------------------------------------------------------
79 --
80 PROCEDURE chk_activity_definition_id (
81 p_training_plan_member_id IN ota_training_plan_members.training_plan_member_id%TYPE
82 ,p_object_version_number IN ota_training_plan_members.object_version_number%TYPE
83 ,p_activity_definition_id IN ota_training_plan_members.activity_definition_id%TYPE
84 ,p_business_group_id IN ota_training_plan_members.business_group_id%TYPE
85 ) IS
86 --
87 l_proc VARCHAR2(72) := g_package|| 'activity_definition_id';
88 l_api_updating boolean;
89 l_business_group_id ota_training_plan_members.business_group_id%TYPE;
90 --
91 CURSOR csr_activity_definition_id IS
92 SELECT oad.business_group_id
93 FROM OTA_ACTIVITY_DEFINITIONS oad
94 WHERE oad.activity_id = p_activity_definition_id;
95 --
96 BEGIN
97 --
98 -- check mandatory parameters have been set.
99 --
100 hr_utility.set_location(' Step:'|| l_proc, 10);
101 hr_api.mandatory_arg_error
102 (p_api_name => l_proc
103 ,p_argument => 'p_business_group_id'
104 ,p_argument_value => p_business_group_id
105 );
106 --
107 l_api_updating := ota_tpm_shd.api_updating
108 (p_training_plan_member_id => p_training_plan_member_id
109 ,p_object_version_number => p_object_version_number
110 );
111 --
112 -- If this is a changing update, or a new insert, test
113 --
114 IF p_activity_definition_id IS NOT NULL THEN
115 IF ((l_api_updating AND
116 NVL(ota_tpm_shd.g_old_rec.activity_definition_id, hr_api.g_number) <>
117 NVL(p_activity_definition_id, hr_api.g_number) )
118 OR (NOT l_api_updating) )
119 THEN
120 -- Check that the definition exists
121 --
122 hr_utility.set_location(' Step:'|| l_proc, 20);
123 OPEN csr_activity_definition_id;
124 FETCH csr_activity_definition_id INTO l_business_group_id;
125 IF csr_activity_definition_id%NOTFOUND THEN
126 CLOSE csr_activity_definition_id;
127 fnd_message.set_name('OTA', 'OTA_13848_TPM_BAD_ACT_DEF');
128 fnd_message.raise_error;
129 ELSE
130 IF l_business_group_id <> p_business_group_id THEN
131 CLOSE csr_activity_definition_id;
132 fnd_message.set_name('OTA', 'OTA_13849_TPM_WRONG_ACT_DEF');
133 fnd_message.raise_error;
134 ELSE
135 hr_utility.set_location(' Step:'|| l_proc, 30);
136 CLOSE csr_activity_definition_id;
137 END IF;
138 END IF;
139 END IF;
140 END IF;
141 --
142 hr_utility.set_location(' Leaving:'||l_proc, 40);
143
144 EXCEPTION
145
146 WHEN app_exception.application_exception THEN
147
148 IF hr_multi_message.exception_add
149 (p_associated_column1 => 'OTA_TRAINING_PLAN_MEMBERS.ACTIVITY_DEFINITION_ID') THEN
150
151 hr_utility.set_location(' Leaving:'||l_proc, 42);
152 RAISE;
153 END IF;
154
155 hr_utility.set_location(' Leaving:'||l_proc, 44);
156
157 END chk_activity_definition_id;
158 -- ----------------------------------------------------------------------------
159 -- |----------------------<chk_version_definition>----------------------------|
160 -- ----------------------------------------------------------------------------
161 --
162 PROCEDURE chk_version_definition (
163 p_training_plan_member_id IN ota_training_plan_members.training_plan_member_id%TYPE
164 ,p_object_version_number IN ota_training_plan_members.object_version_number%TYPE
165 ,p_activity_version_id IN ota_training_plan_members.activity_version_id%TYPE
166 ,p_activity_definition_id IN ota_training_plan_members.activity_definition_id%TYPE
167 ,p_business_group_id IN ota_training_plan_members.business_group_id%TYPE
168 ,p_training_plan_id IN ota_training_plan_members.training_plan_id%TYPE
169 ) IS
170 --
171 l_api_updating boolean;
172 l_exists VARCHAR2(1);
173 l_proc VARCHAR2(72) := g_package|| 'chk_version_definition';
174 --
175 CURSOR csr_version_definition IS
176 SELECT NULL
177 FROM PER_BUDGET_ELEMENTS
178 WHERE training_plan_member_id = p_training_plan_member_id
179 AND training_plan_id = p_training_plan_id;
180 BEGIN
181 --
182 -- check mandatory parameters have been set
183 --
184 --
185 hr_utility.set_location(' Step:'|| l_proc, 30);
186 hr_api.mandatory_arg_error
187 (p_api_name => l_proc
188 ,p_argument => 'p_business_group_id'
189 ,p_argument_value => p_business_group_id
190 );
191 --
192 --
193 hr_utility.set_location(' Step:'|| l_proc, 40);
194 hr_api.mandatory_arg_error
195 (p_api_name => l_proc
196 ,p_argument => 'p_training_plan_id'
197 ,p_argument_value => p_training_plan_id
198 );
199 --
200 -- One and only one must be null;
201 --
202 IF (p_activity_definition_id IS NOT NULL AND p_activity_version_id IS NOT NULL)
203 OR (p_activity_definition_id IS NULL AND p_activity_version_id IS NULL)
204 THEN
205 fnd_message.set_name('OTA', 'OTA_13877_TPM_ONE_MEMBER');
206 fnd_message.raise_error;
207 END IF;
208 --
209 --
210 l_api_updating := ota_tpm_shd.api_updating
211 (p_training_plan_member_id => p_training_plan_member_id
212 ,p_object_version_number => p_object_version_number
213 );
214 --
215 -- If this is a changing update, no budget records are allowed
216 --
217 IF (l_api_updating AND
218 (NVL(ota_tpm_shd.g_old_rec.activity_version_id, hr_api.g_number) <>
219 NVL(p_activity_version_id, hr_api.g_number)
220 OR NVL(ota_tpm_shd.g_old_rec.activity_definition_id, hr_api.g_number) <>
221 NVL(p_activity_definition_id, hr_api.g_number) )) THEN
222 --
223 hr_utility.set_location(' Step:'|| l_proc, 50);
224 OPEN csr_version_definition;
225 FETCH csr_version_definition INTO l_exists;
226 IF csr_version_definition%FOUND THEN
227 CLOSE csr_version_definition;
228 hr_utility.set_location(' Step:'|| l_proc, 60);
229 fnd_message.set_name('OTA', 'OTA_13845_TPM_HAS_BUDGET_RECS');
230 fnd_message.raise_error;
231 ELSE
232 hr_utility.set_location(' Step:'|| l_proc, 70);
233 CLOSE csr_version_definition;
234 END IF;
235 END IF;
236 --
237 hr_utility.set_location(' Leaving:'||l_proc, 100);
238
239 EXCEPTION
240
241 WHEN app_exception.application_exception THEN
242
243 IF hr_multi_message.exception_add
244 (p_associated_column1 => 'OTA_TRAINING_PLAN_MEMBERS.ACTIVITY_VERSION_ID'
245 ,p_associated_column2 => 'OTA_TRAINING_PLAN_MEMBERS.ACTIVITY_DEFINITION_ID') THEN
246
247 hr_utility.set_location(' Leaving:'||l_proc, 102);
248 RAISE;
249 END IF;
250
251 hr_utility.set_location(' Leaving:'||l_proc, 104);
252
253 END chk_version_definition;
254 -- ----------------------------------------------------------------------------
255 -- |----------------------<chk_activity_version_id>---------------------------|
256 -- ----------------------------------------------------------------------------
257 --
258 PROCEDURE chk_activity_version_id (
259 p_training_plan_member_id IN ota_training_plan_members.training_plan_member_id%TYPE
260 ,p_object_version_number IN ota_training_plan_members.object_version_number%TYPE
261 ,p_activity_version_id IN ota_training_plan_members.activity_version_id%TYPE
262 ,p_business_group_id IN ota_training_plan_members.business_group_id%TYPE
263 ,p_training_plan_id IN ota_training_plan_members.training_plan_id%TYPE
264 ) IS
265 --
266 --
267 l_proc VARCHAR2(72) := g_package|| 'activity_definition_id';
268 l_api_updating BOOLEAN;
269 l_business_group_id ota_training_plan_members.business_group_id%TYPE;
270 l_plan_start_date ota_training_plans.start_date%TYPE;
271 l_plan_end_date ota_training_plans.end_date%TYPE;
272 l_version_start_date DATE;
273 l_version_end_date DATE;
274 l_exists VARCHAR2(1);
275
276 --
277 /* cursor csr_activity_version_id is
278 select oav.business_group_id
279 from OTA_ACTIVITY_VERSIONS_V oav
280 where oav.activity_version_id = p_activity_version_id;*/
281
282 CURSOR csr_activity_version_id IS
283 SELECT oad.business_group_id
284 FROM ota_activity_versions oav,
285 ota_activity_definitions oad
286 WHERE oav.activity_id = oad.activity_id
287 AND oav.activity_version_id = p_activity_version_id;
288 --
289 CURSOR csr_version_date_range IS
290 SELECT oav.start_date, oav.end_date
291 FROM ota_activity_versions oav
292 WHERE oav.activity_version_id = p_activity_version_id;
293 --
294 CURSOR csr_plan_date_range IS
295 SELECT otp.start_date, otp.end_date
296 FROM ota_training_plans otp
297 WHERE training_plan_id = p_training_plan_id;
298 /* select ptp.start_date, ptp.end_date
299 from per_time_periods ptp
300 ,ota_training_plans tps
301 where tps.time_period_id = ptp.time_period_id
302 and tps.training_plan_id = p_training_plan_id;*/
303 --
304 --
305 BEGIN
306 --
307 -- check mandatory parameters have been set.
308 --
309 hr_utility.set_location(' Step:'|| l_proc, 10);
310
311 IF hr_multi_message.no_exclusive_error
312 (p_check_column1 => 'OTA_TRAINING_PLAN_MEMBERS.TRAINING_PLAN_ID'
313 ,p_associated_column1 => 'OTA_TRAINING_PLAN_MEMBERS.TRAINING_PLAN_ID') THEN
314
315 hr_api.mandatory_arg_error
316 (p_api_name => l_proc
317 ,p_argument => 'p_business_group_id'
318 ,p_argument_value => p_business_group_id
319 );
320 --
321 l_api_updating := ota_tpm_shd.api_updating
322 (p_training_plan_member_id => p_training_plan_member_id
323 ,p_object_version_number => p_object_version_number
324 );
325 --
326 -- If this is a changing update, or a new insert, test
327 --
328 IF p_activity_version_id IS NOT NULL THEN
329 IF (l_api_updating AND
333 THEN
330 NVL(ota_tpm_shd.g_old_rec.activity_version_id, hr_api.g_number) <>
331 NVL(p_activity_version_id, hr_api.g_number) )
332 OR (NOT l_api_updating)
334 -- Check that the definition exists
335 --
336 hr_utility.set_location(' Step:'|| l_proc, 20);
337 OPEN csr_activity_version_id;
338 FETCH csr_activity_version_id INTO l_business_group_id;
339 IF csr_activity_version_id%NOTFOUND THEN
340 CLOSE csr_activity_version_id;
341 fnd_message.set_name('OTA', 'OTA_13850_TPM_BAD_ACT_VER');
342 fnd_message.raise_error;
343 ELSE
344 IF l_business_group_id <> p_business_group_id THEN
345 CLOSE csr_activity_version_id;
346 fnd_message.set_name('OTA', 'OTA_13851_TPM_WRONG_ACT_VER');
347 fnd_message.raise_error;
348 ELSE
349 hr_utility.set_location(' Step:'|| l_proc, 30);
350 CLOSE csr_activity_version_id;
351 END IF;
352 END IF;
353 --
354 -- Now test to see if the activity dates are in range
355 -- Get the plan start dates
356 --
357 OPEN csr_plan_date_range;
358 FETCH csr_plan_date_range INTO l_plan_start_date, l_plan_end_date;
359 CLOSE csr_plan_date_range;
360 --
361 -- Explicitly get the activity version start dates.
362 --
363 OPEN csr_version_date_range;
364 FETCH csr_version_date_range INTO l_version_start_date, l_version_end_date;
365 CLOSE csr_version_date_range;
366 --
367 -- Test
368 --
369 IF NVL(l_version_end_date, hr_api.g_eot) < NVL(l_plan_start_date, hr_api.g_sot) THEN
370 IF (OTA_TRNG_PLAN_UTIL_SS.is_personal_trng_plan(p_training_plan_id)) THEN
371 fnd_message.set_name('OTA', 'OTA_443569_PLPM_VER_TOO_EARLY');
372 fnd_message.set_token('TP_STARTDATE',l_plan_start_date);
373 ELSE
374 fnd_message.set_name('OTA', 'OTA_13852_TPM_VER_TOO_EARLY');
375 END IF;
376 fnd_message.raise_error;
377 ELSIF NVL(l_version_start_date, hr_api.g_sot) > NVL(l_plan_end_date, hr_api.g_eot) THEN
378 fnd_message.set_name('OTA', 'OTA_13853_TPM_VER_TOO_LATE');
379 fnd_message.raise_error;
380 END IF;
381 END IF;
382 END IF;
383 --
384
385 END IF;
386 hr_utility.set_location(' Leaving:'||l_proc, 40);
387
388 EXCEPTION
389
390 WHEN app_exception.application_exception THEN
391
392 IF hr_multi_message.exception_add
393 (p_same_associated_columns => 'Y') THEN
394
395 hr_utility.set_location(' Leaving:'||l_proc, 42);
396 RAISE;
397
398 END IF;
399
400 hr_utility.set_location(' Leaving:'||l_proc, 44);
401
402
403 END chk_activity_version_id;
404 -- ----------------------------------------------------------------------------
405 -- |----------------------<chk_member_status_type_id>-------------------------|
406 -- ----------------------------------------------------------------------------
407 --
408 PROCEDURE chk_member_status_type_id
409 (p_effective_date IN date
410 ,p_member_status_type_id IN ota_training_plan_members.member_status_type_id%TYPE
411 ,p_business_group_id IN ota_training_plan_members.business_group_id%TYPE
412 ,p_training_plan_member_id IN ota_training_plan_members.training_plan_member_id%TYPE
413 ,p_object_version_number IN ota_training_plan_members.object_version_number%TYPE
414 ,p_activity_version_id IN ota_training_plan_members.activity_version_id%TYPE
415 ,p_activity_definition_id IN ota_training_plan_members.activity_definition_id%TYPE
416 ,p_training_plan_id IN ota_training_plan_members.training_plan_id%TYPE
417 ,p_target_completion_date IN ota_training_plan_members.target_completion_date%TYPE)
418 IS
419 --
420 l_proc VARCHAR2(72) := g_package|| 'chk_member_status_type_id';
421 l_api_updating boolean;
422 l_exists VARCHAR2(1);
423 --
424 CURSOR csr_seek_statuses IS
425 SELECT NULL
426 FROM OTA_ACTIVITY_DEFINITIONS oad
427 ,OTA_ACTIVITY_VERSIONS oav
428 ,OTA_TRAINING_PLAN_MEMBERS tpm
429 WHERE ((oad.activity_id = p_activity_definition_id AND p_activity_definition_id IS NOT NULL)
430 OR (oav.activity_version_id = p_activity_version_id AND p_activity_version_id IS NOT NULL))
431 AND oad.activity_id = oav.activity_id
432 AND ( oav.activity_version_id = tpm.activity_version_id
433 OR oav.activity_id = tpm.activity_definition_id )
434 AND tpm.member_status_type_id <> 'CANCELLED'
435 AND tpm.training_plan_id = p_training_plan_id
436 AND (( p_training_plan_member_id IS NULL )
437 OR (p_training_plan_member_id IS NOT NULL AND tpm.training_plan_member_id <> p_training_plan_member_id))
438 AND (
439 (p_target_completion_date IS NOT NULL AND tpm.target_completion_date = p_target_completion_date)
440 or p_target_completion_date is null);--
441 --
442 -- Following two cursor statements disused
443 --
444 -- cursor csr_parents is
445 -- select null
446 -- from ota_training_plan_members
450 -- and activity_version_id in (
447 -- where business_group_id = p_business_group_id
448 -- and member_status_type_id <> 'CANCELLED'
449 -- and ( nvl(training_plan_member_id, -1) <> nvl(p_training_plan_member_id, -1) )
451 -- select activity_version_id
452 -- from ota_activity_versions
453 -- connect by PRIOR activity_version_id = superseded_by_act_version_id
454 -- start with activity_version_id = p_activity_version_id);
455 --
456 -- cursor csr_children is
457 -- select null
458 -- from ota_training_plan_members
459 -- where business_group_id = p_business_group_id
460 -- and member_status_type_id <> 'CANCELLED'
461 -- and ( nvl(training_plan_member_id, -1) <> nvl(p_training_plan_member_id, -1) ) and activity_version_id in (
462 -- select activity_version_id
463 -- from ota_activity_versions
464 -- connect by activity_version_id = PRIOR superseded_by_act_version_id
465 -- start with activity_version_id = p_activity_version_id);
466 --
467 BEGIN
468 --
469 -- check mandatory parameters have been set.
470 --
471 hr_utility.set_location(' Step:'|| l_proc, 30);
472
473 IF hr_multi_message.no_exclusive_error
474 (p_check_column1 => 'OTA_TRAINING_PLAN_MEMBERS.TRAINING_PLAN_ID'
475 ,p_check_column2 => 'OTA_TRAINING_PLAN_MEMBERS.ACTIVITY_VERSION_ID'
476 ,p_check_column3 => 'OTA_TRAINING_PLAN_MEMBERS.ACTIVITY_DEFINITION_ID'
477 ,p_associated_column1 => 'OTA_TRAINING_PLAN_MEMBERS.TRAINING_PLAN_ID'
478 ,p_associated_column2 => 'OTA_TRAINING_PLAN_MEMBERS.ACTIVITY_VERSION_ID'
479 ,p_associated_column3 => 'OTA_TRAINING_PLAN_MEMBERS.ACTIVITY_DEFINITION_ID') THEN
480
481
482 hr_api.mandatory_arg_error
483 (p_api_name => l_proc
484 ,p_argument => 'p_business_group_id'
485 ,p_argument_value => p_business_group_id
486 );
487 --
488 hr_utility.set_location(' Step:'|| l_proc, 40);
489 hr_api.mandatory_arg_error
490 (p_api_name => l_proc
491 ,p_argument => 'p_member_status_type_id'
492 ,p_argument_value => p_member_status_type_id
493 );
494 --
495 hr_utility.set_location(' Step:'|| l_proc, 45);
496 hr_api.mandatory_arg_error
497 (p_api_name => l_proc
498 ,p_argument => 'p_effective_date'
499 ,p_argument_value => p_effective_date
500 );
501 --
502 hr_utility.set_location(' Step:'|| l_proc, 50);
503 hr_api.mandatory_arg_error
504 (p_api_name => l_proc
505 ,p_argument => 'p_training_plan_id'
506 ,p_argument_value => p_training_plan_id
507 );
508 --
509 l_api_updating := ota_tpm_shd.api_updating
510 (p_training_plan_member_id => p_training_plan_member_id
511 ,p_object_version_number => p_object_version_number
512 );
513 --
514 -- If this is a changing update, or a new insert, test status is valid
515 --
516 IF (l_api_updating AND
517 NVL(ota_tpm_shd.g_old_rec.member_status_type_id, hr_api.g_VARCHAR2) <>
518 NVL(p_member_status_type_id, hr_api.g_VARCHAR2) )
519 OR (NOT l_api_updating)
520 THEN
521 -- Check that the lookup code is valid
522 --
523 hr_utility.set_location(' Step:'|| l_proc, 55);
524 IF hr_api.not_exists_in_hr_lookups
525 (p_effective_date => p_effective_date
526 ,p_lookup_type => 'OTA_MEMBER_USER_STATUS_TYPE'
527 ,p_lookup_code => p_member_status_type_id
528 ) THEN
529 -- Error, lookup not available
530 fnd_message.set_name('OTA', 'OTA_13843_TPM_BAD_STATUS');
531 fnd_message.raise_error;
532 END IF;
533 --
534 -- If the status is not CANCELLED, test no parents (children) are in
535 -- the same plan, whose status is also not cancelled.
536 --
537 IF p_member_status_type_id <> 'CANCELLED' THEN
538 OPEN csr_seek_statuses;
539 FETCH csr_seek_statuses INTO l_exists;
540 IF csr_seek_statuses%FOUND THEN
541 CLOSE csr_seek_statuses;
542 hr_utility.set_location(' Step:'|| l_proc, 60);
543 IF p_activity_version_id IS NOT NULL THEN
544 IF (OTA_TRNG_PLAN_UTIL_SS.is_personal_trng_plan(p_training_plan_id)) THEN
545 fnd_message.set_name('OTA', 'OTA_443567_PLPM_OTHER_MEM_V');
546 Else
547 fnd_message.set_name('OTA', 'OTA_13846_TPM_OTHER_MEMBERS_V');
548 END IF;
549 fnd_message.raise_error;
550 ELSE
551 IF (OTA_TRNG_PLAN_UTIL_SS.is_personal_trng_plan(p_training_plan_id)) THEN
552 fnd_message.set_name('OTA', 'OTA_443568_PLPM_OTHER_MEM_D');
553 Else
554 fnd_message.set_name('OTA', 'OTA_13847_TPM_OTHER_MEMBERS_D');
555 END IF;
556 fnd_message.set_name('OTA', 'OTA_13847_TPM_OTHER_MEMBERS_D');
557 fnd_message.raise_error;
558 END IF;
559 ELSE
560 hr_utility.set_location(' Step:'|| l_proc, 70);
561 CLOSE csr_seek_statuses;
562 END IF;
563 END IF;
564 END IF;
565 END IF;
566 hr_utility.set_location(' Leaving:'||l_proc, 80);
567
568 EXCEPTION
569
570 WHEN app_exception.application_exception THEN
574
571
572 IF hr_multi_message.exception_add
573 (p_same_associated_columns => 'Y') THEN
575 hr_utility.set_location(' Leaving:'||l_proc, 82);
576 RAISE;
577
578 END IF;
579
580 hr_utility.set_location(' Leaving:'||l_proc, 84);
581
582
583 END chk_member_status_type_id;
584 -- ----------------------------------------------------------------------------
585 -- |----------------------<chk_unique>-----------------------------------------|
586 -- ----------------------------------------------------------------------------
587 --
588 PROCEDURE chk_unique
589 (p_training_plan_member_id IN ota_training_plan_members.training_plan_member_id%TYPE
590 ,p_object_version_number IN ota_training_plan_members.object_version_number%TYPE
591 ,p_activity_definition_id IN ota_training_plan_members.activity_definition_id%TYPE
592 ,p_activity_version_id IN ota_training_plan_members.activity_version_id%TYPE
593 ,p_training_plan_id IN ota_training_plan_members.training_plan_id%TYPE
594 ) IS
595 --
596 l_proc VARCHAR2(72) := g_package|| 'chk_unique';
597 l_exists VARCHAR2(1);
598 l_api_updating boolean;
599 --
600 CURSOR csr_unique IS
601 SELECT NULL
602 FROM OTA_TRAINING_PLAN_MEMBERS
603 WHERE training_plan_id = p_training_plan_id
604 AND ( (p_activity_version_id IS NOT NULL AND
605 p_activity_version_id = activity_version_id)
606 OR (p_activity_definition_id IS NOT NULL AND
607 p_activity_definition_id = activity_definition_id) );
608 BEGIN
609 --
610 -- check mandatory parameters have been set
611 --
612 --
613 hr_utility.set_location(' Step:'|| l_proc, 30);
614
615 IF hr_multi_message.no_exclusive_error
616 (p_check_column1 => 'OTA_TRAINING_PLAN_MEMBERS.TRAINING_PLAN_ID'
617 ,p_check_column2 => 'OTA_TRAINING_PLAN_MEMBERS.ACTIVITY_VERSION_ID'
618 ,p_check_column3 => 'OTA_TRAINING_PLAN_MEMBERS.ACTIVITY_DEFINITION_ID'
619 ,p_associated_column1 => 'OTA_TRAINING_PLAN_MEMBERS.TRAINING_PLAN_ID'
620 ,p_associated_column2 => 'OTA_TRAINING_PLAN_MEMBERS.ACTIVITY_VERSION_ID'
621 ,p_associated_column3 => 'OTA_TRAINING_PLAN_MEMBERS.ACTIVITY_DEFINITION_ID') THEN
622
623
624 hr_api.mandatory_arg_error
625 (p_api_name => l_proc
626 ,p_argument => 'p_training_plan_id'
627 ,p_argument_value => p_training_plan_id
628 );
629
630 l_api_updating := ota_tpm_shd.api_updating
631 (p_training_plan_member_id => p_training_plan_member_id
632 ,p_object_version_number => p_object_version_number);
633 --
634 -- Check if anything is changing, or this is an insert
635 --
636 IF (l_api_updating AND
637 NVL(ota_tpm_shd.g_old_rec.activity_version_id, hr_api.g_number) <>
638 NVL(p_activity_version_id, hr_api.g_number)
639 OR NVL(ota_tpm_shd.g_old_rec.activity_definition_id, hr_api.g_number) <>
640 NVL(p_activity_definition_id, hr_api.g_number))
641 OR (NOT l_api_updating) THEN
642 --
643 -- check the combination is unique
644 --
645 hr_utility.set_location(' Step:'|| l_proc, 50);
646 OPEN csr_unique;
647 FETCH csr_unique INTO l_exists;
648 IF csr_unique%FOUND THEN
649 CLOSE csr_unique;
650 hr_utility.set_location(' Step:'|| l_proc, 60);
651 fnd_message.set_name('OTA', 'OTA_13844_TPM_NOT_UNIQUE');
652 fnd_message.raise_error;
653 ELSE
654 CLOSE csr_unique;
655 hr_utility.set_location(' Step:'|| l_proc, 70);
656 END IF;
657 END IF;
658 --
659 END IF;
660 hr_utility.set_location(' Leaving:'||l_proc, 90);
661
662 EXCEPTION
663
664 WHEN app_exception.application_exception THEN
665
666 IF hr_multi_message.exception_add
667 (p_same_associated_columns => 'Y') THEN
668
669 hr_utility.set_location(' Leaving:'||l_proc, 92);
670 RAISE;
671
672 END IF;
673
674 hr_utility.set_location(' Leaving:'||l_proc, 94);
675
676 END chk_unique;
677
678 -- ----------------------------------------------------------------------------
679 -- |----------------------<chk_unique1>-----------------------------------------|
680 -- ----------------------------------------------------------------------------
681 --
682 PROCEDURE chk_unique1
683 (p_training_plan_member_id IN ota_training_plan_members.training_plan_member_id%TYPE
684 ,p_object_version_number IN ota_training_plan_members.object_version_number%TYPE
685 ,p_activity_definition_id IN ota_training_plan_members.activity_definition_id%TYPE
686 ,p_activity_version_id IN ota_training_plan_members.activity_version_id%TYPE
687 ,p_training_plan_id IN ota_training_plan_members.training_plan_id%TYPE
688 ,p_target_completion_date IN ota_training_plan_members.target_completion_date%TYPE
689 ) IS
690 --
691 l_proc VARCHAR2(72) := g_package|| 'chk_unique1';
692 l_exists VARCHAR2(1);
693 l_api_updating boolean;
694 l_member_status_type_id varchar2(30);
695 --
696 CURSOR csr_unique IS
697 SELECT 1,member_status_type_id
698 FROM ota_training_plan_members
699 WHERE training_plan_id = p_training_plan_id
703 p_activity_definition_id = activity_definition_id))
700 AND ( (p_activity_version_id IS NOT NULL AND
701 p_activity_version_id = activity_version_id)
702 OR (p_activity_definition_id IS NOT NULL AND
704 AND (p_target_completion_date IS NOT NULL AND
705 p_target_completion_date = target_completion_date);
706 -- AND member_status_type_id <> 'CANCELLED' ;
707
708
709 BEGIN
710 --
711 -- check mandatory parameters have been set
712 --
713 --
714 hr_utility.set_location(' Step:'|| l_proc, 30);
715
716 IF hr_multi_message.no_exclusive_error
717 (p_check_column1 => 'OTA_TRAINING_PLAN_MEMBERS.TRAINING_PLAN_ID'
718 ,p_check_column2 => 'OTA_TRAINING_PLAN_MEMBERS.ACTIVITY_VERSION_ID'
719 ,p_check_column3 => 'OTA_TRAINING_PLAN_MEMBERS.ACTIVITY_DEFINITION_ID'
720 ,p_associated_column1 => 'OTA_TRAINING_PLAN_MEMBERS.TRAINING_PLAN_ID'
721 ,p_associated_column2 => 'OTA_TRAINING_PLAN_MEMBERS.ACTIVITY_VERSION_ID'
722 ,p_associated_column3 => 'OTA_TRAINING_PLAN_MEMBERS.ACTIVITY_DEFINITION_ID') THEN
723
724
725 hr_api.mandatory_arg_error
726 (p_api_name => l_proc
727 ,p_argument => 'p_training_plan_id'
728 ,p_argument_value => p_training_plan_id
729 );
730
731 l_api_updating := ota_tpm_shd.api_updating
732 (p_training_plan_member_id => p_training_plan_member_id
733 ,p_object_version_number => p_object_version_number);
734 --
735 -- Check if anything is changing, or this is an insert
736 --
737 IF (l_api_updating AND
738 NVL(ota_tpm_shd.g_old_rec.activity_version_id, hr_api.g_number) <>
739 NVL(p_activity_version_id, hr_api.g_number)
740 OR NVL(ota_tpm_shd.g_old_rec.activity_definition_id, hr_api.g_number) <>
741 NVL(p_activity_definition_id, hr_api.g_number)
742 OR NVL(ota_tpm_shd.g_old_rec.target_completion_date, hr_api.g_date) <>
743 NVL(p_target_completion_date, hr_api.g_date))
744 OR (NOT l_api_updating) THEN
745 --
746 -- check the combination is unique
747 --
748 hr_utility.set_location(' Step:'|| l_proc, 50);
749 OPEN csr_unique;
750 FETCH csr_unique INTO l_exists,l_member_status_type_id;
751 IF csr_unique%FOUND THEN
752 CLOSE csr_unique;
753 hr_utility.set_location(' Step:'|| l_proc, 60);
754 if l_member_status_type_id = 'CANCELLED' then
755 fnd_message.set_name('OTA', 'OTA_13189_TPM_CANCEL_EXISTS');
756 else
757 fnd_message.set_name('OTA', 'OTA_13182_TPM_ACT_NOT_UNIQUE');
758 end if;
759 fnd_message.raise_error;
760 ELSE
761 CLOSE csr_unique;
762 hr_utility.set_location(' Step:'|| l_proc, 70);
763 END IF;
764
765 END IF;
766 --
767 END IF;
768 hr_utility.set_location(' Leaving:'||l_proc, 90);
769
770 EXCEPTION
771
772 WHEN app_exception.application_exception THEN
773
774 IF hr_multi_message.exception_add
775 (p_same_associated_columns => 'Y') THEN
776
777 hr_utility.set_location(' Leaving:'||l_proc, 92);
778 RAISE;
779
780 END IF;
781
782 hr_utility.set_location(' Leaving:'||l_proc, 94);
783
784
785
786 END chk_unique1;
787
788
789
790 -- ----------------------------------------------------------------------------
791 -- |----------------------<chk_delete>----------------------------------------|
792 -- ----------------------------------------------------------------------------
793 --
794 PROCEDURE chk_delete (
795 p_training_plan_member_id IN ota_training_plan_members.training_plan_member_id%TYPE
796 ,p_training_plan_id IN ota_training_plan_members.training_plan_id%TYPE
797 ) IS
798 --
799 l_api_updating boolean;
800 l_exists VARCHAR2(1);
801 l_proc VARCHAR2(72) := g_package|| 'chk_delete';
802 --
803 CURSOR csr_chk_delete IS
804 SELECT NULL
805 FROM PER_BUDGET_ELEMENTS
806 WHERE training_plan_member_id = p_training_plan_member_id
807 AND training_plan_id = p_training_plan_id;
808 BEGIN
809 --
810 -- check mandatory parameters have been set
811 --
812 --
813 hr_utility.set_location(' Step:'|| l_proc, 30);
814
815 IF hr_multi_message.no_exclusive_error
816 (p_check_column1 => 'OTA_TRAINING_PLAN_MEMBERS.TRAINING_PLAN_ID'
817 ,p_associated_column1 => 'OTA_TRAINING_PLAN_MEMBERS.TRAINING_PLAN_ID') THEN
818
819
820 hr_api.mandatory_arg_error
821 (p_api_name => l_proc
822 ,p_argument => 'p_training_plan_member_id'
823 ,p_argument_value => p_training_plan_member_id
824 );
825 --
826 --
827 hr_utility.set_location(' Step:'|| l_proc, 40);
828 hr_api.mandatory_arg_error
829 (p_api_name => l_proc
830 ,p_argument => 'p_training_plan_id'
831 ,p_argument_value => p_training_plan_id
832 );
833 --
834 --
835 --
836 hr_utility.set_location(' Step:'|| l_proc, 50);
837 OPEN csr_chk_delete;
838 FETCH csr_chk_delete INTO l_exists;
842 fnd_message.set_name('OTA', 'OTA_13845_TPM_HAS_BUDGET_RECS');
839 IF csr_chk_delete%FOUND THEN
840 CLOSE csr_chk_delete;
841 hr_utility.set_location(' Step:'|| l_proc, 60);
843 fnd_message.raise_error;
844 ELSE
845 hr_utility.set_location(' Step:'|| l_proc, 70);
846 CLOSE csr_chk_delete;
847 END IF;
848 --
849 END IF;
850 hr_utility.set_location(' Leaving:'||l_proc, 100);
851
852 EXCEPTION
853
854 WHEN app_exception.application_exception THEN
855
856 IF hr_multi_message.exception_add
857 (p_same_associated_columns => 'Y') THEN
858
859 hr_utility.set_location(' Leaving:'||l_proc, 102);
860 RAISE;
861
862 END IF;
863
864 hr_utility.set_location(' Leaving:'||l_proc, 104);
865
866 END chk_delete;
867
868 -- ----------------------------------------------------------------------------
869 -- |---------------------------< chk_source_function >----------------------------|
870 -- ----------------------------------------------------------------------------
871 PROCEDURE chk_source_function
872 (p_training_plan_member_id IN NUMBER
873 ,p_source_function IN VARCHAR2
874 ,p_effective_date IN DATE) IS
875
876 --
877 l_proc VARCHAR2(72) := g_package||'chk_source_function';
878 l_api_updating boolean;
879
880 BEGIN
881 hr_utility.set_location(' Leaving:'||l_proc, 10);
882 --
883 -- check mandatory parameters has been set
884 --
885 hr_api.mandatory_arg_error
886 (p_api_name => l_proc
887 ,p_argument => 'effective_date'
888 ,p_argument_value => p_effective_date);
889
890 IF (((p_training_plan_member_id IS NOT NULL) AND
891 NVL(ota_tpm_shd.g_old_rec.source_function,hr_api.g_VARCHAR2) <>
892 NVL(p_source_function,hr_api.g_VARCHAR2))
893 OR
894 (p_training_plan_member_id IS NULL)) THEN
895
896 hr_utility.set_location(' Leaving:'||l_proc, 20);
897 --
898
899 --
900 IF p_source_function IS NOT NULL THEN
901 IF hr_api.not_exists_in_hr_lookups
902 (p_effective_date => p_effective_date
903 ,p_lookup_type => 'OTA_PLAN_COMPONENT_SOURCE'
904 ,p_lookup_code => p_source_FUNCTION) THEN
905 fnd_message.set_name('OTA','OTA_13178_TPM_SRC_FUNC_INVLD');
906 fnd_message.raise_error;
907 END IF;
908 hr_utility.set_location(' Leaving:'||l_proc, 30);
909
910 END IF;
911
912 END IF;
913 hr_utility.set_location(' Leaving:'||l_proc, 40);
914
915 EXCEPTION
916
917 WHEN app_exception.application_exception THEN
918
919 IF hr_multi_message.exception_add
920 (p_associated_column1 => 'OTA_TRAINING_PLAN_MEMBERS.SOURCE_FUNCTION') THEN
921
922 hr_utility.set_location(' Leaving:'||l_proc, 42);
923 RAISE;
924 END IF;
925
926 hr_utility.set_location(' Leaving:'||l_proc, 44);
927
928 END chk_source_function;
929
930 -- ----------------------------------------------------------------------------
931 -- |---------------------------< chk_cancellation_reason >----------------------------|
932 -- ----------------------------------------------------------------------------
933 PROCEDURE chk_cancellation_reason (p_training_plan_member_id IN number
934 ,p_cancellation_reason IN VARCHAR2
935 ,p_effective_date IN date) IS
936
937 --
938 l_proc VARCHAR2(72) := g_package||'chk_cancellation_reason';
939 l_api_updating boolean;
940
941 BEGIN
942 hr_utility.set_location(' Leaving:'||l_proc, 10);
943 --
944 -- check mandatory parameters has been set
945 --
946 hr_api.mandatory_arg_error
947 (p_api_name => l_proc
948 ,p_argument => 'effective_date'
949 ,p_argument_value => p_effective_date);
950
951 IF (((p_training_plan_member_id IS NOT NULL) AND
952 NVL(ota_tpm_shd.g_old_rec.cancellation_reason,hr_api.g_VARCHAR2) <>
953 NVL(p_cancellation_reason,hr_api.g_VARCHAR2))
954 OR
955 (p_training_plan_member_id IS NULL)) THEN
956
957 hr_utility.set_location(' Leaving:'||l_proc, 20);
958 --
959
960 --
961 IF p_cancellation_reason IS NOT NULL THEN
962 IF hr_api.not_exists_in_hr_lookups
963 (p_effective_date => p_effective_date
964 ,p_lookup_type => 'OTA_PLAN_CANCELLATION_SOURCE'
965 ,p_lookup_code => p_cancellation_reason) THEN
966 fnd_message.set_name('OTA','OTA_13177_TPM_CANCL_RSN_INVLD');
967 fnd_message.raise_error;
968 END IF;
969 hr_utility.set_location(' Leaving:'||l_proc, 30);
970
971 END IF;
972
973 END IF;
974 hr_utility.set_location(' Leaving:'||l_proc, 40);
975
976 EXCEPTION
977
978 WHEN app_exception.application_exception THEN
979
980 IF hr_multi_message.exception_add
984 RAISE;
981 (p_associated_column1 => 'OTA_TRAINING_PLAN_MEMBERS.CANCELLATION_REASON') THEN
982
983 hr_utility.set_location(' Leaving:'||l_proc, 42);
985 END IF;
986
987 hr_utility.set_location(' Leaving:'||l_proc, 44);
988
989 END chk_cancellation_reason;
990
991 -- ----------------------------------------------------------------------------
992 -- |---------------------------< chk_tpc_tp_actver_dates >----------------------------|
993 -- ----------------------------------------------------------------------------
994
995
996 PROCEDURE chk_tpc_tp_actver_dates (p_training_plan_id IN ota_training_plans.training_plan_id%TYPE
997 ,p_training_plan_member_id IN ota_training_plan_members.training_plan_member_id%TYPE
998 ,p_activity_version_id IN ota_training_plan_members.activity_version_id%TYPE
999 ,p_earliest_start_date IN ota_training_plan_members.earliest_start_date%TYPE
1000 ,p_target_completion_date IN ota_training_plan_members.target_completion_date%TYPE
1001 ,p_object_version_number IN ota_training_plan_members.object_version_number%TYPE)
1002 IS
1003
1004 l_proc VARCHAR2(72) := g_package|| 'chk_tpc_tp_actver_dates';
1005
1006 CURSOR csr_comp_tpc_tp_dates IS
1007 SELECT 1,otp.start_date,decode(otp.end_date,null,'',otp.end_date)
1008 FROM ota_training_plans otp
1009 WHERE otp.training_plan_id = p_training_plan_id
1010 AND ( otp.start_date > p_earliest_start_date
1011 OR ( otp.end_date IS NOT NULL
1012 AND otp.end_date < p_target_completion_date) );
1013
1014 CURSOR csr_tpm_overlap IS
1015 SELECT NULL
1016 FROM ota_training_plan_members
1017 WHERE training_plan_id = p_training_plan_id
1018 AND activity_version_id = p_activity_version_id
1019 AND target_completion_date >= p_earliest_start_date
1020 AND earliest_start_date <= p_target_completion_date
1021 AND member_status_type_id <> 'CANCELLED'
1022 and (p_training_plan_member_id is null
1023 or training_plan_member_id<> p_training_plan_member_id
1024 ) ;
1025 --
1026 CURSOR csr_version_date_range IS
1027 SELECT oav.start_date, oav.end_date
1028 FROM ota_activity_versions oav
1029 WHERE oav.activity_version_id = p_activity_version_id;
1030
1031 l_exists NUMBER(9);
1032 l_api_updating BOOLEAN;
1033 l_flag VARCHAR2(30);
1034 l_version_start_date DATE;
1035 l_version_end_date DATE;
1036 l_start_Date Date;
1037 l_end_date Date;
1038
1039 BEGIN
1040
1041 -- check mandatory parameters have been set
1042 --
1043 hr_utility.set_location(' Step:'|| l_proc, 10);
1044 hr_api.mandatory_arg_error
1045 (p_api_name => l_proc
1046 ,p_argument => 'p_training_plan_id'
1047 ,p_argument_value => p_training_plan_id
1048 );
1049
1050 l_api_updating := ota_tpm_shd.api_updating
1051 (p_training_plan_member_id => p_training_plan_member_id
1052 ,p_object_version_number => p_object_version_number
1053 );
1054 --
1055 --
1056 -- If this is a changing update, or a new insert, test
1057 --
1058 IF ((l_api_updating AND
1059 NVL(ota_tpm_shd.g_old_rec.earliest_start_date, hr_api.g_date) <>
1060 NVL(p_earliest_start_date, hr_api.g_date)
1061 OR NVL(ota_tpm_shd.g_old_rec.target_completion_date, hr_api.g_date) <>
1062 NVL(p_target_completion_date, hr_api.g_date) )
1063 OR (NOT l_api_updating))
1064 THEN
1065 hr_utility.set_location(' Step:'|| l_proc, 20);
1066
1067 /* IF( NOT l_api_updating
1068 or NVL(ota_tpm_shd.g_old_rec.earliest_start_date, hr_api.g_date) <>
1069 NVL(p_earliest_start_date, hr_api.g_date)) THEN
1070
1071 IF ( p_earliest_start_date < TRUNC(SYSDATE))THEN
1072 l_flag := 'START_DATE';
1073 fnd_message.set_name('OTA', 'OTA_13179_TPM_STRT_DATE');
1074 fnd_message.raise_error;
1075
1076 END IF;
1077
1078 END IF;*/
1079
1080
1081 IF ( p_target_completion_date >= p_earliest_start_date ) THEN
1082
1083 OPEN csr_comp_tpc_tp_dates;
1084 FETCH csr_comp_tpc_tp_dates INTO l_exists,l_start_date,l_end_date;
1085 IF csr_comp_tpc_tp_dates%FOUND THEN
1086 CLOSE csr_comp_tpc_tp_dates;
1087
1088 fnd_message.set_name('OTA', 'OTA_13994_TPS_TPM_DATES');
1089 fnd_message.set_token('TP_STARTDATE',l_start_date);
1090 fnd_message.set_token('TP_ENDDATE',l_end_date);
1091 fnd_message.raise_error;
1092 ELSE
1093
1094 CLOSE csr_comp_tpc_tp_dates;
1095 END IF;
1096 -- Explicitly get the activity version start dates.
1097 --
1098 OPEN csr_version_date_range;
1099 FETCH csr_version_date_range INTO l_version_start_date, l_version_end_date;
1100 CLOSE csr_version_date_range;
1101 --
1102 IF NVL(l_version_end_date, hr_api.g_eot) < NVL(p_earliest_start_date, hr_api.g_sot) THEN
1103 fnd_message.set_name('OTA', 'OTA_13181_TPM_ACT_VRSN_EARLY');
1104 fnd_message.raise_error;
1105 ELSIF NVL(l_version_start_date, hr_api.g_sot) > NVL(p_target_completion_date, hr_api.g_eot) THEN
1106 fnd_message.set_name('OTA', 'OTA_13180_TPM_ACT_VRSN_LATE');
1107 fnd_message.raise_error;
1108 elsif (l_version_start_date>p_earliest_start_date) or (l_version_end_date<p_target_completion_date) then
1109 fnd_message.set_name('OTA', 'OTA_13188_TPM_DATES_RANGE');
1110 fnd_message.raise_error;
1111 END IF;
1112
1113 OPEN csr_tpm_overlap;
1114 FETCH csr_tpm_overlap INTO l_exists;
1115 IF csr_tpm_overlap%FOUND THEN
1116 CLOSE csr_tpm_overlap;
1117
1118 fnd_message.set_name('OTA', 'OTA_13184_TPM_DATES_OVERLAP');
1119 fnd_message.raise_error;
1120 ELSE
1121
1122 CLOSE csr_tpm_overlap;
1123 END IF;
1124
1125
1126 ELSE
1127 l_flag := 'END_DATE';
1128 fnd_message.set_name('OTA', 'OTA_13993_TPM_DATES');
1129 fnd_message.raise_error;
1130 END IF;
1131
1132
1133 END IF;
1134
1135 hr_utility.set_location(' Step:'|| l_proc, 30);
1136
1140 WHEN app_exception.application_exception THEN
1137 --MULTI MESSAGE SUPPORT
1138 EXCEPTION
1139
1141
1142 IF l_flag ='END_DATE' THEN
1143 /* IF hr_multi_message.exception_add(
1144 p_associated_column1 => 'OTA_TRAINING_PLAN_MEMBERS.EARLIEST_START_DATE') THEN
1145 -- ,p_associated_column2 => 'OTA_TRAINING_PLAN_MEMBERS.TARGET_COMPLETION_DATE') THEN
1146
1147 hr_utility.set_location(' Leaving:'||l_proc, 32);
1148 RAISE;
1149
1150 END IF;
1151 ELSIF l_flag = 'END_DATE' THEN */
1152 IF hr_multi_message.exception_add(
1153 -- p_associated_column1 => 'OTA_TRAINING_PLAN_MEMBERS.EARLIEST_START_DATE') THEN
1154 p_associated_column1 => 'OTA_TRAINING_PLAN_MEMBERS.TARGET_COMPLETION_DATE') THEN
1155
1156 hr_utility.set_location(' Leaving:'||l_proc, 35);
1157 RAISE;
1158
1159 END IF;
1160 ELSE
1161 IF hr_multi_message.exception_add(
1162 p_associated_column1 => 'OTA_TRAINING_PLAN_MEMBERS.EARLIEST_START_DATE'
1163 ,p_associated_column2 => 'OTA_TRAINING_PLAN_MEMBERS.TARGET_COMPLETION_DATE') THEN
1164
1165 hr_utility.set_location(' Leaving:'||l_proc, 36);
1166 RAISE;
1167
1168 END IF;
1169
1170 END IF;
1171 hr_utility.set_location(' Leaving:'||l_proc, 37);
1172
1173 END chk_tpc_tp_actver_dates;
1174
1175
1176
1177
1178
1179
1180 END ota_tpm_bus1;