[Home] [Help]
PACKAGE BODY: APPS.OTA_TPS_BUS1
Source
1 PACKAGE BODY OTA_TPS_BUS1 AS
2 /* $Header: ottpsrhi.pkb 120.2 2005/12/14 15:17:58 asud noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_tps_bus1.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |----------------------<chk_unique>-----------------------------------------|
12 -- ----------------------------------------------------------------------------
13 --
14 PROCEDURE chk_unique
15 (p_training_plan_id IN ota_training_plans.training_plan_id%TYPE
16 ,p_object_version_number IN ota_training_plans.object_version_number%TYPE
17 ,p_organization_id IN ota_training_plans.organization_id%TYPE
18 ,p_person_id IN ota_training_plans.person_id%TYPE
19 ,p_time_period_id IN ota_training_plans.time_period_id%TYPE
20 ) IS
21 --
22 l_proc varchar2(72) := g_package|| 'chk_unique';
23 l_exists varchar2(1);
24 l_api_updating boolean;
25 --
26 CURSOR csr_unique IS
27 SELECT NULL
28 FROM OTA_TRAINING_PLANS
29 WHERE training_plan_id <> NVL(p_training_plan_id, -1)
30 AND ( (p_organization_id IS NOT NULL AND organization_id = p_organization_id )
31 OR (p_person_id IS NOT NULL AND person_id = p_person_id) )
32 AND time_period_id = p_time_period_id;
33 --
34 BEGIN
35 --
36 -- check mandatory parameters have been set
37 --
38 --
39 hr_utility.set_location(' Step:'|| l_proc, 45);
40
41 IF hr_multi_message.no_exclusive_error
42 (p_check_column1 => 'OTA_TRAINING_PLANS.TIME_PERIOD_ID'
43 ,p_check_column2 => 'OTA_TRAINING_PLANS.PERSON_ID'
44 ,p_check_column3 => 'OTA_TRAINING_PLANS.ORGANIZATION_ID'
45 ,p_associated_column1 => 'OTA_TRAINING_PLANS.TIME_PERIOD_ID'
46 ,p_associated_column2 => 'OTA_TRAINING_PLANS.PERSON_ID'
47 ,p_associated_column3 => 'OTA_TRAINING_PLANS.ORGANIZATION_ID'
48 ) THEN
49
50 hr_api.mandatory_arg_error
51 (p_api_name => l_proc
52 ,p_argument => 'p_time_period_id'
53 ,p_argument_value => p_time_period_id
54 );
55 --
56 -- if time period is changing or is an insert,
57 -- check the combination is unique
58 --
59 l_api_updating := ota_tps_shd.api_updating
60 (p_training_plan_id => p_training_plan_id
61 ,p_object_version_number => p_object_version_number
62 );
63 --
64 -- If the time_period is changing
65 -- or this is an insert
66 --
67 IF (l_api_updating AND
68 NVL(ota_tps_shd.g_old_rec.time_period_id, hr_api.g_number) <>
69 NVL(p_time_period_id, hr_api.g_number) )
70 OR (NOT l_api_updating)
71 THEN
72 hr_utility.set_location(' Step:'|| l_proc, 50);
73 OPEN csr_unique;
74 FETCH csr_unique INTO l_exists;
75 IF csr_unique%FOUND THEN
76 CLOSE csr_unique;
77 hr_utility.set_location(' Step:'|| l_proc, 60);
78 fnd_message.set_name('OTA', 'OTA_13867_TPS_DUPLICATE_TP');
79 fnd_message.raise_error;
80 ELSE
81 CLOSE csr_unique;
82 hr_utility.set_location(' Step:'|| l_proc, 70);
83 END IF;
84 END IF;
85 --
86 END IF;
87 hr_utility.set_location(' Leaving:'||l_proc, 90);
88
89 --MULTI MESSAGE SUPPORT
90 EXCEPTION
91
92 WHEN app_exception.application_exception THEN
93
94 IF hr_multi_message.exception_add(
95 p_same_associated_columns => 'Y') THEN
96
97 hr_utility.set_location(' Leaving:'||l_proc, 92);
98 RAISE;
99
100 END IF;
101 hr_utility.set_location(' Leaving:'||l_proc, 95);
102
103 END chk_unique;
104
105 -- ----------------------------------------------------------------------------
106 -- |----------------------<chk_org_person>------------------------------------|
107 -- ----------------------------------------------------------------------------
108 --
109 PROCEDURE chk_org_person
110 (p_organization_id IN ota_training_plans.organization_id%TYPE
111 ,p_person_id IN ota_training_plans.person_id%TYPE
112 ,p_contact_id IN ota_training_plans.contact_id%TYPE
113 ) IS
114 --
115 l_proc varchar2(72) := g_package|| 'chk_org_person';
116 --
117 BEGIN
118 --
119 -- check mandatory parameters have been set
120 --
121 hr_utility.set_location(' Entering:'||l_proc, 10);
122 /*
123 IF ( (p_organization_id IS NOT NULL AND p_person_id IS NOT NULL )
124 OR (p_organization_id IS NULL AND p_person_id IS NULL) ) THEN
125 */
126 IF ( (p_organization_id IS NOT NULL AND (p_person_id IS NOT NULL OR p_contact_id IS NOT NULL))
127 OR (p_organization_id IS NULL AND p_person_id IS NULL AND p_contact_id IS NULL)
128 OR (p_organization_id IS NULL AND p_person_id IS NOT NULL AND p_contact_id IS NOT NULL)) THEN
129 fnd_message.set_name('OTA', 'OTA_13858_TPS_ORG_OR_PERSON');
130 fnd_message.raise_error;
131 END IF;
132 --
133 hr_utility.set_location(' Leaving:'||l_proc, 20);
134
135 --MULTI MESSAGE SUPPORT
136 EXCEPTION
137
138 WHEN app_exception.application_exception THEN
139
140 IF hr_multi_message.exception_add(
141 p_associated_column1 => 'OTA_TRAINING_PLANS.ORGANIZATION_ID'
142 ,p_associated_column2 => 'OTA_TRAINING_PLANS.PERSON_ID'
143 ,p_associated_column3 => 'OTA_TRAINING_PLANS.CONTACT_ID') THEN
144
145 hr_utility.set_location(' Leaving:'||l_proc, 22);
146 RAISE;
147
148 END IF;
149 hr_utility.set_location(' Leaving:'||l_proc, 25);
150
151 END chk_org_person;
152 -- ----------------------------------------------------------------------------
153 -- |----------------------<chk_organization_id>-------------------------------|
154 -- ----------------------------------------------------------------------------
155 --
156 PROCEDURE chk_organization_id
157 (p_organization_id IN ota_training_plans.organization_id%TYPE
158 ,p_business_group_id IN ota_training_plans.business_group_id%TYPE
159 ) IS
160 --
161 l_exists varchar2(1);
162 l_proc varchar2(72) := g_package|| 'chk_organization_id';
163 l_business_group_id ota_training_plans.business_group_id%TYPE;
164 --
165 CURSOR csr_organization_id IS
166 SELECT business_group_id
167 FROM HR_ALL_ORGANIZATION_UNITS
168 WHERE organization_id = p_organization_id;
169 BEGIN
170 --
171 -- check mandatory parameters have been set
172 --
173 hr_utility.set_location(' Step:'|| l_proc, 20);
174 hr_api.mandatory_arg_error
175 (p_api_name => l_proc
176 ,p_argument => 'p_business_group_id'
177 ,p_argument_value => p_business_group_id
178 );
179 --
180 hr_utility.set_location(' Step:'|| l_proc, 30);
181 IF p_organization_id IS NOT NULL THEN
182 OPEN csr_organization_id;
183 FETCH csr_organization_id INTO l_business_group_id;
184 IF csr_organization_id%NOTFOUND THEN
185 CLOSE csr_organization_id;
186 hr_utility.set_location(' Step:'|| l_proc, 40);
187 fnd_message.set_name('OTA', 'OTA_13859_TPS_BAD_ORG');
188 fnd_message.raise_error;
189 ELSIF l_business_group_id <> p_business_group_id THEN
190 CLOSE csr_organization_id;
191 fnd_message.set_name('OTA', 'OTA_13860_TPS_WRONG_ORG');
192 fnd_message.raise_error;
193 ELSE
194 CLOSE csr_organization_id;
195 END IF;
196 END IF;
197 --
198 hr_utility.set_location(' Leaving:'||l_proc, 50);
199
200 --MULTI MESSAGE SUPPORT
201 EXCEPTION
202
203 WHEN app_exception.application_exception THEN
204
205 IF hr_multi_message.exception_add(
206 p_associated_column1 => 'OTA_TRAINING_PLANS.ORGANIZATION_ID') THEN
207
208 hr_utility.set_location(' Leaving:'||l_proc, 52);
209 RAISE;
210
211 END IF;
212 hr_utility.set_location(' Leaving:'||l_proc, 55);
213
214
215 END chk_organization_id;
216
217 -- ----------------------------------------------------------------------------
218 -- |----------------------<chk_person_id>-------------------------------------|
219 -- ----------------------------------------------------------------------------
220 --
221 PROCEDURE chk_person_id
222 (p_effective_date IN date
223 ,p_person_id IN ota_training_plans.person_id%TYPE
224 ,p_business_group_id IN ota_training_plans.business_group_id%TYPE
225 ) IS
226 --
227 l_exists varchar2(1);
228 l_proc varchar2(72) := g_package|| 'chk_person_id';
229 l_business_group_id ota_training_plans.business_group_id%TYPE;
230 --
231 CURSOR csr_person_id IS
232 SELECT business_group_id
233 FROM PER_ALL_PEOPLE_F
234 WHERE person_id = p_person_id;
235 BEGIN
236 --
237 -- check mandatory parameters have been set
238 --
239 hr_utility.set_location(' Step:'|| l_proc, 20);
240 hr_api.mandatory_arg_error
241 (p_api_name => l_proc
242 ,p_argument => 'p_business_group_id'
243 ,p_argument_value => p_business_group_id
244 );
245 --
246 hr_utility.set_location(' Step:'|| l_proc, 30);
247 IF p_person_id IS NOT NULL THEN
248 OPEN csr_person_id;
249 FETCH csr_person_id INTO l_business_group_id;
250 IF csr_person_id%NOTFOUND THEN
251 CLOSE csr_person_id;
252 hr_utility.set_location(' Step:'|| l_proc, 40);
253 fnd_message.set_name('OTA', 'OTA_13884_NHS_PERSON_INVALID');
254 fnd_message.raise_error;
255 /*
256 selected person can be from a business group other than
257 the one set up in the profiles.
258 ELSIF l_business_group_id <> p_business_group_id THEN
259 CLOSE csr_person_id;
260 fnd_message.set_name('OTA', 'OTA_13862_TPS_WRONG_PERSON');
261 fnd_message.raise_error;
262 */
263 ELSE
264 CLOSE csr_person_id;
265 END IF;
266 END IF;
267 --
268 hr_utility.set_location(' Leaving:'||l_proc, 50);
269
270 --MULTI MESSAGE SUPPORT
271 EXCEPTION
272
273 WHEN app_exception.application_exception THEN
274
275 IF hr_multi_message.exception_add(
276 p_associated_column1 => 'OTA_TRAINING_PLANS.PERSON_ID') THEN
277
278 hr_utility.set_location(' Leaving:'||l_proc, 52);
279 RAISE;
280
281 END IF;
282 hr_utility.set_location(' Leaving:'||l_proc, 55);
283
284 END chk_person_id;
285 -- ----------------------------------------------------------------------------
286 -- |----------------------<chk_time_period_id>---------------------------------|
287 -- ----------------------------------------------------------------------------
288 --
289 PROCEDURE chk_time_period_id
290 (p_training_plan_id IN ota_training_plans.training_plan_id%TYPE
291 ,p_object_version_number IN ota_training_plans.object_version_number%TYPE
292 ,p_time_period_id IN ota_training_plans.time_period_id%TYPE
293 ,p_business_group_id IN ota_training_plans.business_group_id%TYPE
294 ) IS
295 --
296 l_exists varchar2(1);
297 l_proc varchar2(72) := g_package|| 'chk_time_period_id';
298 l_api_updating boolean;
299 --
300 CURSOR csr_time_period_id IS
301 SELECT NULL
302 FROM PER_TIME_PERIODS
303 WHERE time_period_id = p_time_period_id;
304 --
305 CURSOR csr_members IS
306 SELECT NULL
307 FROM OTA_TRAINING_PLAN_MEMBERS
308 WHERE training_plan_id = p_training_plan_id;
309 --
310 BEGIN
311 --
312 -- check mandatory parameters have been set
313 --
314 hr_utility.set_location(' Step:'|| l_proc, 20);
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_tps_shd.api_updating
322 (p_training_plan_id => p_training_plan_id
323 ,p_object_version_number => p_object_version_number
324 );
325 --
326 -- If the time_period is changing
327 -- or this is an insert
328 --
329 IF (l_api_updating AND
330 (NVL(ota_tps_shd.g_old_rec.time_period_id, hr_api.g_number) <>
331 NVL(p_time_period_id, hr_api.g_number) )
332 OR (NOT l_api_updating))
333 THEN
334 --
335 -- The time period_id must exist in per_time_periods
336 --
337 hr_utility.set_location(' Step:'|| l_proc, 30);
338 OPEN csr_time_period_id;
339 FETCH csr_time_period_id INTO l_exists;
340 IF csr_time_period_id%NOTFOUND THEN
341 CLOSE csr_time_period_id;
342 fnd_message.set_name('OTA', 'OTA_13865_TPS_BAD_TIME_PERIOD');
343 fnd_message.raise_error;
344 ELSE
345 CLOSE csr_time_period_id;
346 END IF;
347 END IF;
348 --
349 -- If it is a changing update, no members are allowed
350 -- in OTA_TRAINING_PLAN_MEMBERS
351 --
352 hr_utility.set_location(' Step:'|| l_proc, 40);
353 IF l_api_updating AND
354 NVL(ota_tps_shd.g_old_rec.time_period_id, hr_api.g_number) <>
355 NVL(p_time_period_id, hr_api.g_number) THEN
356 hr_utility.set_location(' Step:'|| l_proc, 50);
357 OPEN csr_members;
358 FETCH csr_members INTO l_exists;
359 IF csr_members%FOUND THEN
360 CLOSE csr_members;
361 fnd_message.set_name('OTA', 'OTA_13866_TPS_NO_CHANGE_TIME');
362 fnd_message.raise_error;
363 ELSE
364 CLOSE csr_members;
365 END IF;
366 END IF;
367 hr_utility.set_location(' Leaving:'||l_proc, 60);
368
369 --MULTI MESSAGE SUPPORT
370 EXCEPTION
371
372 WHEN app_exception.application_exception THEN
373
374 IF hr_multi_message.exception_add(
375 p_associated_column1 => 'OTA_TRAINING_PLANS.TIME_PERIOD_ID') THEN
376
377 hr_utility.set_location(' Leaving:'||l_proc, 62);
378 RAISE;
379
380 END IF;
381 hr_utility.set_location(' Leaving:'||l_proc, 65);
382
383
384 END chk_time_period_id;
385 -- ----------------------------------------------------------------------------
386 -- |----------------------<chk_plan_status_type_id>---------------------------|
387 -- ----------------------------------------------------------------------------
388 --
389 PROCEDURE chk_plan_status_type_id
390 (p_effective_date IN date
391 ,p_plan_status_type_id IN ota_training_plans.plan_status_type_id%TYPE
392 ) IS
393 --
394 l_proc varchar2(72) := g_package|| 'chk_plan_status_type_id';
395 --
396 BEGIN
397 --
398 -- check mandatory parameters have been set
399 --
400 --
401 hr_utility.set_location(' Step:'|| l_proc, 10);
402 hr_api.mandatory_arg_error
403 (p_api_name => l_proc
404 ,p_argument => 'p_plan_status_type_id'
405 ,p_argument_value => p_plan_status_type_id
406 );
407 --
408 hr_utility.set_location(' Step:'|| l_proc, 20);
412 ,p_lookup_code => p_plan_status_type_id
409 IF hr_api.not_exists_in_hr_lookups
410 (p_effective_date => p_effective_date
411 ,p_lookup_type => 'OTA_PLAN_USER_STATUS_TYPE'
413 ) THEN
414 -- Error, lookup not available
415 If (OTA_TRNG_PLAN_UTIL_SS.is_personal_trng_plan()) THEN
416 fnd_message.set_name('OTA', 'OTA_13864_TPS_BAD_PLAN_STATUS');
417 Else
418 fnd_message.set_name('OTA', 'OTA_13864_TPS_BAD_PLAN_STATUS');
419 END IF;
420 fnd_message.raise_error;
421 END IF;
422 --
423 --
424 hr_utility.set_location(' Leaving:'||l_proc, 30);
425
426 --MULTI MESSAGE SUPPORT
427 EXCEPTION
428
429 WHEN app_exception.application_exception THEN
430
431 IF hr_multi_message.exception_add(
432 p_associated_column1 => 'OTA_TRAINING_PLANS.PLAN_STATUS_TYPE_ID') THEN
433
434 hr_utility.set_location(' Leaving:'||l_proc, 32);
435 RAISE;
436
437 END IF;
438 hr_utility.set_location(' Leaving:'||l_proc, 35);
439
440 END chk_plan_status_type_id;
441 -- ----------------------------------------------------------------------------
442 -- |----------------------<chk_period_overlap>---------------------------------|
443 -- ----------------------------------------------------------------------------
444 --
445 PROCEDURE chk_period_overlap
446 (p_training_plan_id IN ota_training_plans.training_plan_id%TYPE
447 ,p_object_version_number IN ota_training_plans.object_version_number%TYPE
448 ,p_plan_status_type_id IN ota_training_plans.plan_status_type_id%TYPE
449 ,p_time_period_id IN ota_training_plans.time_period_id%TYPE
450 ,p_person_id IN ota_training_plans.person_id%TYPE
451 ,p_organization_id IN ota_training_plans.organization_id%TYPE
452 ) IS
453 --
454 l_exists varchar2(1);
455 l_proc varchar2(72) := g_package|| 'chk_period_overlap';
456 l_start_date date;
457 l_end_date date;
458 l_api_updating boolean;
459 --
460 --
461 CURSOR csr_get_dates IS
462 SELECT start_date, end_date
463 FROM PER_TIME_PERIODS
464 WHERE time_period_id = p_time_period_id;
465 --
466 CURSOR csr_plan_overlap IS
467 SELECT NULL
468 FROM PER_TIME_PERIODS ptp
469 ,OTA_TRAINING_PLANS tps
470 WHERE ( (p_person_id IS NOT NULL AND tps.person_id = p_person_id )
471 OR (p_organization_id IS NOT NULL AND tps.organization_id = p_organization_id) )
472 AND (NVL(p_training_plan_id, -1) <> training_plan_id)
473 AND tps.plan_status_type_id <> 'CANCELLED'
474 AND tps.time_period_id = ptp.time_period_id
475 AND ( (l_start_date >= ptp.start_date
476 AND
477 l_start_date <= ptp.end_date)
478 OR
479 (l_end_date >= ptp.start_date
480 AND
481 l_end_date <= ptp.end_date)
482 OR
483 (l_start_date <= ptp.start_date
484 AND
485 l_end_date >= ptp.end_date)
486 );
487 --
488 BEGIN
489 --
490 -- check mandatory parameters have been set
491 --
492 --
493 hr_utility.set_location(' Step:'|| l_proc, 10);
494
495
496 --MULTI MESSAGE SUPPORT
497
498 IF hr_multi_message.no_exclusive_error
499 (p_check_column1 => 'OTA_TRAINING_PLANS.TIME_PERIOD_ID'
500 ,p_check_column2 => 'OTA_TRAINING_PLANS.PERSON_ID'
501 ,p_check_column3 => 'OTA_TRAINING_PLANS.ORGANIZATION_ID'
502 ,p_check_column4 => 'OTA_TRAINING_PLANS.PLAN_STATUS_TYPE_ID'
503 ,p_associated_column1 => 'OTA_TRAINING_PLANS.TIME_PERIOD_ID'
504 ,p_associated_column2 => 'OTA_TRAINING_PLANS.PERSON_ID'
505 ,p_associated_column3 => 'OTA_TRAINING_PLANS.ORGANIZATION_ID'
506 ,p_associated_column4 => 'OTA_TRAINING_PLANS.PLAN_STATUS_TYPE_ID'
507 ) THEN
508
509 hr_api.mandatory_arg_error
510 (p_api_name => l_proc
511 ,p_argument => 'p_plan_status_type_id'
512 ,p_argument_value => p_plan_status_type_id
513 );
514 --
515 --
516 hr_utility.set_location(' Step:'|| l_proc, 20);
517 hr_api.mandatory_arg_error
518 (p_api_name => l_proc
519 ,p_argument => 'p_time_period_id'
520 ,p_argument_value => p_time_period_id
521 );
522 --
523 hr_utility.set_location(' Step:'|| l_proc, 30);
524 --
525 l_api_updating := ota_tps_shd.api_updating
526 (p_training_plan_id => p_training_plan_id
527 ,p_object_version_number => p_object_version_number
528 );
529 --
530 -- If the status is changing away from cancelled
531 -- or the time period is changing
532 -- or this is an insert and its not cancelled
533 --
534 IF (l_api_updating AND
535 (NVL(ota_tps_shd.g_old_rec.time_period_id, hr_api.g_number) <>
536 NVL(p_time_period_id, hr_api.g_number)
537 AND p_plan_status_type_id <> 'CANCELLED' )
541 (p_plan_status_type_id <> 'CANCELLED') )
538 OR (NVL(ota_tps_shd.g_old_rec.plan_status_type_id, hr_api.g_varchar2) <>
539 NVL(p_plan_status_type_id, hr_api.g_varchar2)
540 AND
542 OR (NOT l_api_updating AND p_plan_status_type_id <> 'CANCELLED'))
543 THEN
544 --
545 -- Fetch the plan dates
546 --
547 hr_utility.set_location(' Step:'|| l_proc, 50);
548 OPEN csr_get_dates;
549 FETCH csr_get_dates INTO l_start_date, l_end_date;
550 CLOSE csr_get_dates;
551 --
552 -- Look for duplicates
553 --
554 hr_utility.set_location(' Step:'|| l_proc, 60);
555 OPEN csr_plan_overlap;
556 FETCH csr_plan_overlap INTO l_exists;
557 IF csr_plan_overlap%FOUND THEN
558 CLOSE csr_plan_overlap;
559 hr_utility.set_location(' Step:'|| l_proc, 70);
560 fnd_message.set_name('OTA', 'OTA_13863_TPS_OVERLAP_PLANS');
561 fnd_message.raise_error;
562 ELSE
563 CLOSE csr_plan_overlap;
564 END IF;
565 END IF;
566 --
567 END IF;
568 hr_utility.set_location(' Leaving:'||l_proc, 80);
569
570 EXCEPTION
571
572 WHEN app_exception.application_exception THEN
573
574 IF hr_multi_message.exception_add
575 (p_same_associated_columns => 'Y') THEN
576
577 hr_utility.set_location(' Leaving:'||l_proc, 82);
578 RAISE;
579 END IF;
580
581 hr_utility.set_location(' Leaving:'||l_proc, 85);
582
583
584 END chk_period_overlap;
585 -- ----------------------------------------------------------------------------
586 -- |----------------------<chk_currency_code>---------------------------------|
587 -- ----------------------------------------------------------------------------
588 --
589 PROCEDURE chk_currency_code
590 ( p_budget_currency IN ota_training_plans.budget_currency%TYPE
591 ,p_training_plan_id IN ota_training_plans.training_plan_id%TYPE
592 ,p_business_group_id IN ota_training_plans.business_group_id%TYPE
593 ,p_object_version_number IN ota_training_plans.object_version_number%TYPE
594 )IS
595 --
596 l_exists varchar2(1);
597 l_proc varchar2(72) := g_package|| 'chk_currency_value';
598 l_api_updating boolean;
599 --
600 CURSOR csr_currency_code IS
601 SELECT NULL
602 FROM FND_CURRENCIES
603 WHERE currency_code = p_budget_currency;
604 --
605 BEGIN
606 --
607 -- check mandatory parameters have been set. Currency code can
608 -- be null, so it is not mandatory.
609 --
610 hr_utility.set_location(' Step:'|| l_proc, 30);
611 hr_api.mandatory_arg_error
612 (p_api_name => l_proc
613 ,p_argument => 'p_business_group_id'
614 ,p_argument_value => p_business_group_id
615 );
616 --
617 hr_utility.set_location(' Step:'|| l_proc, 40);
618 hr_api.mandatory_arg_error
619 (p_api_name => l_proc
620 ,p_argument => 'p_budget_currency'
621 ,p_argument_value => p_budget_currency
622 );
623 --
624 l_api_updating := ota_tps_shd.api_updating
625 (p_training_plan_id => p_training_plan_id
626 ,p_object_version_number => p_object_version_number
627 );
628 --
629 -- If this is a changing update, or a new insert, test
630 --
631 IF ((l_api_updating AND
632 NVL(ota_tps_shd.g_old_rec.budget_currency, hr_api.g_varchar2) <>
633 NVL(p_budget_currency, hr_api.g_varchar2) )
634 OR (NOT l_api_updating))
635 THEN
636 hr_utility.set_location(' Step:'|| l_proc, 50);
637 IF p_budget_currency IS NOT NULL THEN
638 OPEN csr_currency_code;
639 FETCH csr_currency_code INTO l_exists;
640 IF csr_currency_code%NOTFOUND THEN
641 CLOSE csr_currency_code;
642 fnd_message.set_name('AOL', 'MC_INVALID_CURRENCY');
643 fnd_message.set_token('CODE', p_budget_currency);
644 fnd_message.raise_error;
645 ELSE
646 CLOSE csr_currency_code;
647 END IF;
648 END IF;
649 END IF;
650 --
651 hr_utility.set_location(' Leaving:'||l_proc, 90);
652
653 --MULTI MESSAGE SUPPORT
654 EXCEPTION
655
656 WHEN app_exception.application_exception THEN
657
658 IF hr_multi_message.exception_add(
659 p_associated_column1 => 'OTA_TRAINING_PLANS.BUDGET_CURRENCY') THEN
660
661 hr_utility.set_location(' Leaving:'||l_proc, 92);
662 RAISE;
663
664 END IF;
665 hr_utility.set_location(' Leaving:'||l_proc, 95);
666
667 END chk_currency_code;
668 --
669 --
670 -- ----------------------------------------------------------------------------
671 -- |----------------------<chk_name>-------------------------------------------|
672 -- ----------------------------------------------------------------------------
673 PROCEDURE chk_name
674 (p_name IN ota_training_plans.name%TYPE
675 ,p_training_plan_id IN ota_training_plans.training_plan_id%TYPE
676 ,p_person_id IN ota_training_plans.person_id%TYPE
680 --
677 ,p_contact_id IN ota_training_plans.contact_id%TYPE
678 ,p_business_group_id IN ota_training_plans.business_group_id%TYPE
679 ,p_object_version_number IN ota_training_plans.object_version_number%TYPE ) IS
681 l_proc varchar2(72) := g_package||'chk_name';
682 l_api_updating boolean;
683 l_exists varchar2(1);
684 --
685 CURSOR csr_name IS
686 SELECT NULL
687 FROM OTA_TRAINING_PLANS
688 WHERE NVL(p_training_plan_id, -1) <> training_plan_id
689 AND name = p_name
690 AND business_group_id = p_business_group_id;
691 --
692 --Bug#3484692
693 CURSOR csr_plp_name IS
694 SELECT NULL
695 FROM OTA_TRAINING_PLANS
696 WHERE NVL(p_training_plan_id, -1) <> training_plan_id
697 AND name = p_name
698 -- Modified for bug#3855813
699 AND ((p_person_id IS NOT NULL AND person_id = p_person_id)
700 OR (p_contact_id IS NOT NULL AND contact_id = p_contact_id))
701 AND business_group_id = p_business_group_id;
702 --
703 --
704 BEGIN
705 --
706 -- check mandatory parameters have been set
707 --
708 hr_utility.set_location('Entering:'||l_proc, 5);
709 hr_api.mandatory_arg_error
710 (p_api_name => l_proc
711 ,p_argument => 'p_name'
712 ,p_argument_value => p_name
713 );
714 --
715 --
716 l_api_updating := ota_tps_shd.api_updating
717 (p_training_plan_id => p_training_plan_id
718 ,p_object_version_number => p_object_version_number
719 );
720 --
721 --
722 -- If this is a changing update, or a new insert, test
723 --
724 IF ((l_api_updating AND
725 NVL(ota_tps_shd.g_old_rec.name, hr_api.g_varchar2) <>
726 NVL(p_name, hr_api.g_varchar2) )
727 OR (NOT l_api_updating))
728 THEN
729 hr_utility.set_location(' Step:'|| l_proc, 50);
730 IF p_name IS NOT NULL THEN
731 --Bug#3484692
732 --check if lp is org or non-org
733 IF (OTA_TRNG_PLAN_UTIL_SS.is_personal_trng_plan()) THEN
734 OPEN csr_plp_name;
735 FETCH csr_plp_name INTO l_exists;
736 IF csr_plp_name%FOUND THEN
737 CLOSE csr_plp_name;
738 fnd_message.set_name('OTA', 'OTA_443572_PLP_UNIQUE_NAME');
739 fnd_message.raise_error;
740 ELSE
741 CLOSE csr_plp_name;
742 END IF;
743 ELSE
744 OPEN csr_name;
745 FETCH csr_name INTO l_exists;
746 IF csr_name%FOUND THEN
747 CLOSE csr_name;
748 fnd_message.set_name('OTA', 'OTA_13897_TPS_UNIQUE_NAME');
749 fnd_message.raise_error;
750 ELSE
751 CLOSE csr_name;
752 END IF;
753 END IF;
754 END IF;
755 END IF;
756 hr_utility.set_location(' Leaving:'||l_proc, 10);
757
758 --MULTI MESSAGE SUPPORT
759 EXCEPTION
760
761 WHEN app_exception.application_exception THEN
762
763 IF hr_multi_message.exception_add(
764 p_associated_column1 => 'OTA_TRAINING_PLANS.NAME') THEN
765
766 hr_utility.set_location(' Leaving:'||l_proc, 12);
767 RAISE;
768
769 END IF;
770 hr_utility.set_location(' Leaving:'||l_proc, 15);
771 --
772 END chk_name;
773 -- ----------------------------------------------------------------------------
774 -- |----------------------<chk_del_training_plan_id>---------------------------|
775 -- ----------------------------------------------------------------------------
776 PROCEDURE chk_del_training_plan_id
777 (p_training_plan_id IN ota_training_plans.training_plan_id%TYPE
778 ) IS
779 --
780 l_exists varchar2(1);
781 l_proc varchar2(72) := g_package||'chk_del_training_plan_id';
782
783 CURSOR csr_del_training_plan_id IS
784 SELECT NULL
785 FROM OTA_TRAINING_PLAN_MEMBERS tpm
786 WHERE tpm.training_plan_id = p_training_plan_id
787 UNION
788 SELECT NULL
789 FROM OTA_TRAINING_PLAN_COSTS tpc
790 WHERE tpc.training_plan_id = p_training_plan_id
791 UNION
792 SELECT NULL
793 FROM PER_BUDGET_ELEMENTS pbe
794 WHERE pbe.training_plan_id = p_training_plan_id;
795
796 BEGIN
797 --
798 -- check mandatory parameters have been set
799 --
800 hr_utility.set_location('Entering:'||l_proc, 5);
801 hr_api.mandatory_arg_error
802 (p_api_name => l_proc
803 ,p_argument => 'p_training_plan_id'
804 ,p_argument_value => p_training_plan_id
805 );
806 --
807 -- Check that the code can be deleted
808 --
809 OPEN csr_del_training_plan_id;
810 FETCH csr_del_training_plan_id INTO l_exists;
811 IF csr_del_training_plan_id%FOUND THEN
812 CLOSE csr_del_training_plan_id;
813 hr_utility.set_location(' Step:'|| l_proc, 10);
814 fnd_message.set_name('OTA', 'OTA_13868_TPS_CHILD_RECORDS');
815 fnd_message.raise_error;
816 END IF;
817 CLOSE csr_del_training_plan_id;
818 hr_utility.set_location(' Leaving:'||l_proc, 20);
819 --
820 END chk_del_training_plan_id;
824 -- |---------------------------< chk_plan_source >----------------------------|
821 --
822
823 -- ----------------------------------------------------------------------------
825 -- ----------------------------------------------------------------------------
826 PROCEDURE chk_plan_source
827 (p_training_plan_id IN number
828 ,p_plan_source IN varchar2
829 ,p_effective_date IN date) IS
830
831 --
832 l_proc varchar2(72) := g_package||'chk_plan_source';
833 l_api_updating boolean;
834
835 BEGIN
836 hr_utility.set_location(' Leaving:'||l_proc, 10);
837 --
838 -- check mandatory parameters has been set
839 --
840 hr_api.mandatory_arg_error
841 (p_api_name => l_proc
842 ,p_argument => 'effective_date'
843 ,p_argument_value => p_effective_date);
844
845 IF (((p_training_plan_id IS NOT NULL) AND
846 NVL(ota_tps_shd.g_old_rec.plan_source,hr_api.g_varchar2) <>
847 NVL(p_plan_source,hr_api.g_varchar2))
848 OR
849 (p_training_plan_id IS NULL)) THEN
850
851 hr_utility.set_location(' Leaving:'||l_proc, 20);
852 --
853
854 --
855 IF p_plan_source IS NOT NULL THEN
856 IF hr_api.not_exists_in_hr_lookups
857 (p_effective_date => p_effective_date
858 ,p_lookup_type => 'OTA_TRAINING_PLAN_SOURCE'
859 ,p_lookup_code => p_plan_source) THEN
860 fnd_message.set_name('OTA','OTA_13176_TPM_PLN_SRC_INVLD');
861 fnd_message.raise_error;
862 END IF;
863 hr_utility.set_location(' Leaving:'||l_proc, 30);
864
865 END IF;
866
867 END IF;
868 hr_utility.set_location(' Leaving:'||l_proc, 40);
869
870 --MULTI MESSAGE SUPPORT
871 EXCEPTION
872
873 WHEN app_exception.application_exception THEN
874
875 IF hr_multi_message.exception_add(
876 p_associated_column1 => 'OTA_TRAINING_PLANS.PLAN_SOURCE') THEN
877
878 hr_utility.set_location(' Leaving:'||l_proc, 42);
879 RAISE;
880
881 END IF;
882 hr_utility.set_location(' Leaving:'||l_proc, 45);
883
884
885 END chk_plan_source;
886
887 -- ----------------------------------------------------------------------------
888 -- |---------------------------< chk_tp_date_range >----------------------------|
889 -- ----------------------------------------------------------------------------
890
891 PROCEDURE chk_tp_date_range (p_training_plan_id IN ota_training_plans.training_plan_id%TYPE
892 ,p_start_date IN ota_training_plans.start_date%TYPE
893 ,p_end_date IN ota_training_plans.end_date%TYPE DEFAULT NULL
894 ,p_object_version_number IN ota_training_plans.object_version_number%TYPE)
895 IS
896
897 l_proc VARCHAR2(72) := g_package|| 'chk_tp_date_range';
898
899 CURSOR csr_get_tpm IS
900 SELECT training_plan_member_id
901 FROM ota_training_plan_members
902 WHERE training_plan_id = p_training_plan_id
903 AND ( earliest_start_date < p_start_date
904 OR ( p_end_date IS NOT NULL AND target_completion_date > p_end_date) )
905 and member_status_type_id <>'CANCELLED'
906 AND ROWNUM = 1;
907
908 CURSOR csr_max_tpm_tcd IS
909 SELECT max(target_completion_date)
910 FROM ota_training_plan_members
911 WHERE training_plan_id = p_training_plan_id
912 and member_status_type_id <>'CANCELLED';
913
914 l_exists NUMBER(9);
915 l_api_updating BOOLEAN;
916 l_flag VARCHAR2(30);
917 l_end_date date;
918 l_target_completion_date date := '';
919
920 BEGIN
921
922 -- check mandatory parameters have been set
923 --
924 hr_utility.set_location(' Step:'|| l_proc, 10);
925
926 hr_api.mandatory_arg_error
927 (p_api_name => l_proc
928 ,p_argument => 'p_start_date'
929 ,p_argument_value => p_start_date
930 );
931
932 l_api_updating := ota_tps_shd.api_updating
933 (p_training_plan_id => p_training_plan_id
934 ,p_object_version_number => p_object_version_number
935 );
936 --
937 --
938 -- If this is a changing update, or a new insert, test
939 --
940 IF ((l_api_updating AND
941 NVL( ota_tps_shd.g_old_rec.start_date, hr_api.g_date ) <>
942 NVL( p_start_date, hr_api.g_date )
943 OR NVL( ota_tps_shd.g_old_rec.end_date, hr_api.g_date ) <>
944 NVL( p_end_date, hr_api.g_date) )
945 OR ( NOT l_api_updating) )
946 THEN
947 hr_utility.set_location(' Step:'|| l_proc, 20);
948
949 /* IF ( NOT l_api_updating
950 or NVL( ota_tps_shd.g_old_rec.start_date, hr_api.g_date ) <>
951 NVL( p_start_date, hr_api.g_date )) THEN
952
953 IF ( p_start_date < TRUNC(SYSDATE) ) THEN
954 l_flag :='START_DATE';
955 fnd_message.set_name('OTA', 'OTA_13999_TPS_STRT_DATE');
956 fnd_message.raise_error;
957 END IF;
958
959 END IF;*/
960 if p_end_date is not null then
961 l_end_date:=p_end_date;
962 else
963 l_end_date :='';
964 end if;
965 /*
966 IF ( p_end_date is null or p_end_date >= p_start_date ) THEN
967 OPEN csr_get_tpm;
968 FETCH csr_get_tpm INTO l_exists;
969 IF csr_get_tpm%FOUND THEN
970 CLOSE csr_get_tpm;
971
972 OPEN csr_max_tpm_tcd;
973 FETCH csr_max_tpm_tcd into l_target_completion_date;
974 CLOSE csr_max_tpm_tcd;
975 fnd_message.set_name('OTA', 'OTA_443687_TPS_MAX_TPM_DATE');
976 fnd_message.set_token('MAX_TPC_TARGET_DATE',l_target_completion_date);
977 fnd_message.raise_error;
978 ELSE
979 CLOSE csr_get_TPM;
980 END IF;
981 -- Bug 3529382 ELSIF ( p_start_date > p_end_date ) THEN
982 ELSE
983 l_flag :='END_DATE';
984 fnd_message.set_name('OTA', 'OTA_13992_TPS_DATES');
985 -- Bug 3484721
986 fnd_message.set_token('TP_STARTDATE',p_start_date);
987 fnd_message.raise_error;
988
989 END IF;
990 */
991
992 OPEN csr_get_tpm;
993 FETCH csr_get_tpm INTO l_exists;
994
995 --Throw error if the completion target is less than any components TCD,
996 --or if its less than the path start date.
997 IF csr_get_tpm%FOUND THEN
998 CLOSE csr_get_tpm;
999
1000 OPEN csr_max_tpm_tcd;
1001 FETCH csr_max_tpm_tcd into l_target_completion_date;
1002 CLOSE csr_max_tpm_tcd;
1003 l_flag :='END_DATE';
1004 fnd_message.set_name('OTA', 'OTA_443687_TPS_MAX_TPM_DATE');
1005 fnd_message.set_token('MAX_TPC_TARGET_DATE',l_target_completion_date);
1006 fnd_message.raise_error;
1007 --When there are no components created yet
1008 ELSIF (p_end_date < p_start_date) THEN
1009 CLOSE csr_get_TPM;
1010 l_flag :='END_DATE';
1011 fnd_message.set_name('OTA', 'OTA_13992_TPS_DATES');
1012 fnd_message.set_token('TP_STARTDATE',p_start_date);
1013 fnd_message.raise_error;
1014 ELSE
1015 CLOSE csr_get_TPM;
1016 END IF;
1017
1018
1019 END IF;
1020
1021 hr_utility.set_location(' Step:'|| l_proc, 30);
1022
1023
1024 --MULTI MESSAGE SUPPORT
1025 EXCEPTION
1026
1027 WHEN app_exception.application_exception THEN
1028
1029 IF l_flag = 'END_DATE' THEN
1030
1031 /* IF hr_multi_message.exception_add(
1032 p_associated_column1 => 'OTA_TRAINING_PLANS.START_DATE') THEN
1033 hr_utility.set_location(' Leaving:'||l_proc, 32);
1034 RAISE;
1035
1036 END IF;
1037 ELSIF l_flag = 'END_DATE' THEN */
1038
1039 IF hr_multi_message.exception_add(
1040 p_associated_column1 => 'OTA_TRAINING_PLANS.END_DATE') THEN
1041
1042 hr_utility.set_location(' Leaving:'||l_proc, 34);
1043 RAISE;
1044
1045 END IF;
1046
1047 ELSE
1048
1049 IF hr_multi_message.exception_add(
1050 p_associated_column1 => 'OTA_TRAINING_PLANS.START_DATE'
1051 ,p_associated_column2 => 'OTA_TRAINING_PLANS.END_DATE') THEN
1052
1053 hr_utility.set_location(' Leaving:'||l_proc, 36);
1054 RAISE;
1055
1056 END IF;
1057
1058 END IF;
1059 hr_utility.set_location(' Leaving:'||l_proc, 38);
1060
1061 END chk_tp_date_range;
1062
1063 END ota_tps_bus1;