[Home] [Help]
PACKAGE BODY: APPS.OTA_TAV_BUS
Source
1 PACKAGE BODY ota_tav_bus as
2 /* $Header: ottav01t.pkb 120.2.12010000.2 2008/12/19 09:26:32 shwnayak ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_tav_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |-----------------------------< chk_ddf >----------------------------------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description:
15 -- Validates all the Developer Descriptive Flexfield values.
16 --
17 -- Prerequisites:
18 -- All other columns have been validated. Must be called as the
19 -- last step from insert_validate and update_validate.
20 --
21 -- In Arguments:
22 -- p_rec
23 --
24 -- Post Success:
25 -- If the Developer Descriptive Flexfield structure column and data values
26 -- are all valid this procedure will end normally and processing will
27 -- continue.
28 --
29 -- Post Failure:
30 -- If the Developer Descriptive Flexfield structure column value or any of
31 -- the data values are invalid then an application error is raised as
32 -- a PL/SQL exception.
33 --
34 -- Access Status:
35 -- Internal Row Handler Use Only.
36 --
37 -- ----------------------------------------------------------------------------
38 procedure chk_ddf
39 (p_rec in ota_tav_shd.g_rec_type
40 ) is
41 --
42 l_proc varchar2(72) := g_package || 'chk_ddf';
43 --
44 begin
45 hr_utility.set_location('Entering:'||l_proc,10);
46 --
47 if ((p_rec.activity_version_id is not null) and (
48 nvl(ota_tav_shd.g_old_rec.tav_information_category, hr_api.g_varchar2) <>
49 nvl(p_rec.tav_information_category, hr_api.g_varchar2) or
50 nvl(ota_tav_shd.g_old_rec.tav_information1, hr_api.g_varchar2) <>
51 nvl(p_rec.tav_information1, hr_api.g_varchar2) or
52 nvl(ota_tav_shd.g_old_rec.tav_information2, hr_api.g_varchar2) <>
53 nvl(p_rec.tav_information2, hr_api.g_varchar2) or
54 nvl(ota_tav_shd.g_old_rec.tav_information3, hr_api.g_varchar2) <>
55 nvl(p_rec.tav_information3, hr_api.g_varchar2) or
56 nvl(ota_tav_shd.g_old_rec.tav_information4, hr_api.g_varchar2) <>
57 nvl(p_rec.tav_information4, hr_api.g_varchar2) or
58 nvl(ota_tav_shd.g_old_rec.tav_information5, hr_api.g_varchar2) <>
59 nvl(p_rec.tav_information5, hr_api.g_varchar2) or
60 nvl(ota_tav_shd.g_old_rec.tav_information6, hr_api.g_varchar2) <>
61 nvl(p_rec.tav_information6, hr_api.g_varchar2) or
62 nvl(ota_tav_shd.g_old_rec.tav_information7, hr_api.g_varchar2) <>
63 nvl(p_rec.tav_information7, hr_api.g_varchar2) or
64 nvl(ota_tav_shd.g_old_rec.tav_information8, hr_api.g_varchar2) <>
65 nvl(p_rec.tav_information8, hr_api.g_varchar2) or
66 nvl(ota_tav_shd.g_old_rec.tav_information9, hr_api.g_varchar2) <>
67 nvl(p_rec.tav_information9, hr_api.g_varchar2) or
68 nvl(ota_tav_shd.g_old_rec.tav_information10, hr_api.g_varchar2) <>
69 nvl(p_rec.tav_information10, hr_api.g_varchar2) or
70 nvl(ota_tav_shd.g_old_rec.tav_information11, hr_api.g_varchar2) <>
71 nvl(p_rec.tav_information11, hr_api.g_varchar2) or
72 nvl(ota_tav_shd.g_old_rec.tav_information12, hr_api.g_varchar2) <>
73 nvl(p_rec.tav_information12, hr_api.g_varchar2) or
74 nvl(ota_tav_shd.g_old_rec.tav_information13, hr_api.g_varchar2) <>
75 nvl(p_rec.tav_information13, hr_api.g_varchar2) or
76 nvl(ota_tav_shd.g_old_rec.tav_information14, hr_api.g_varchar2) <>
77 nvl(p_rec.tav_information14, hr_api.g_varchar2) or
78 nvl(ota_tav_shd.g_old_rec.tav_information15, hr_api.g_varchar2) <>
79 nvl(p_rec.tav_information15, hr_api.g_varchar2) or
80 nvl(ota_tav_shd.g_old_rec.tav_information16, hr_api.g_varchar2) <>
81 nvl(p_rec.tav_information16, hr_api.g_varchar2) or
82 nvl(ota_tav_shd.g_old_rec.tav_information17, hr_api.g_varchar2) <>
83 nvl(p_rec.tav_information17, hr_api.g_varchar2) or
84 nvl(ota_tav_shd.g_old_rec.tav_information18, hr_api.g_varchar2) <>
85 nvl(p_rec.tav_information18, hr_api.g_varchar2) or
86 nvl(ota_tav_shd.g_old_rec.tav_information19, hr_api.g_varchar2) <>
87 nvl(p_rec.tav_information19, hr_api.g_varchar2) or
88 nvl(ota_tav_shd.g_old_rec.tav_information20, hr_api.g_varchar2) <>
89 nvl(p_rec.tav_information20, hr_api.g_varchar2) ))
90 or (p_rec.activity_version_id is null) then
91 --
92 -- Only execute the validation if absolutely necessary:
93 -- a) During update, the structure column value or any
94 -- of the attribute values have actually changed.
95 -- b) During insert.
96 --
97 hr_dflex_utility.ins_or_upd_descflex_attribs
98 (p_appl_short_name => 'OTA'
99 ,p_descflex_name => 'OTA_ACTIVITY_VERSIONS'
100 ,p_attribute_category => p_rec.tav_information_category
101 ,p_attribute1_name => 'TAV_INFORMATION1'
102 ,p_attribute1_value => p_rec.tav_information1
103 ,p_attribute2_name => 'TAV_INFORMATION2'
104 ,p_attribute2_value => p_rec.tav_information2
105 ,p_attribute3_name => 'TAV_INFORMATION3'
106 ,p_attribute3_value => p_rec.tav_information3
107 ,p_attribute4_name => 'TAV_INFORMATION4'
108 ,p_attribute4_value => p_rec.tav_information4
109 ,p_attribute5_name => 'TAV_INFORMATION5'
110 ,p_attribute5_value => p_rec.tav_information5
111 ,p_attribute6_name => 'TAV_INFORMATION6'
112 ,p_attribute6_value => p_rec.tav_information6
113 ,p_attribute7_name => 'TAV_INFORMATION7'
114 ,p_attribute7_value => p_rec.tav_information7
115 ,p_attribute8_name => 'TAV_INFORMATION8'
116 ,p_attribute8_value => p_rec.tav_information8
117 ,p_attribute9_name => 'TAV_INFORMATION9'
118 ,p_attribute9_value => p_rec.tav_information9
119 ,p_attribute10_name => 'TAV_INFORMATION10'
120 ,p_attribute10_value => p_rec.tav_information10
121 ,p_attribute11_name => 'TAV_INFORMATION11'
122 ,p_attribute11_value => p_rec.tav_information11
123 ,p_attribute12_name => 'TAV_INFORMATION12'
124 ,p_attribute12_value => p_rec.tav_information12
125 ,p_attribute13_name => 'TAV_INFORMATION13'
126 ,p_attribute13_value => p_rec.tav_information13
127 ,p_attribute14_name => 'TAV_INFORMATION14'
128 ,p_attribute14_value => p_rec.tav_information14
129 ,p_attribute15_name => 'TAV_INFORMATION15'
130 ,p_attribute15_value => p_rec.tav_information15
131 ,p_attribute16_name => 'TAV_INFORMATION16'
132 ,p_attribute16_value => p_rec.tav_information16
133 ,p_attribute17_name => 'TAV_INFORMATION17'
134 ,p_attribute17_value => p_rec.tav_information17
135 ,p_attribute18_name => 'TAV_INFORMATION18'
136 ,p_attribute18_value => p_rec.tav_information18
137 ,p_attribute19_name => 'TAV_INFORMATION19'
138 ,p_attribute19_value => p_rec.tav_information19
139 ,p_attribute20_name => 'TAV_INFORMATION20'
140 ,p_attribute20_value => p_rec.tav_information20
141 );
142 end if;
143 --
144 hr_utility.set_location(' Leaving:'||l_proc,20);
145 end chk_ddf;
146 --
147
148 --
149 -- ----------------------------------------------------------------------------
150 -- |-------------------------< check_min_max_values >-------------------------|
151 -- ----------------------------------------------------------------------------
152 --
153 -- PUBLIC
154 -- Description:
155 -- The minimum attendees must be less then or equal to the maximum attendees.
156 --
157 Procedure check_min_max_values
158 (
159 p_min in number
160 ,p_max in number
161 ) Is
162 --
163 v_proc varchar2(72) := g_package||'check_min_max_values';
164 --
165 Begin
166 --
167 hr_utility.set_location('Entering:'||v_proc, 5);
168 --
169 ota_tav_api_business_rules.check_min_max_values( p_min
170 , p_max );
171 --
172 hr_utility.set_location(' Leaving:'||v_proc, 10);
173 --
174 exception
175 when app_exception.application_exception then
176 if hr_multi_message.exception_add
177 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.MINIMUM_ATTENDEES'
178 ,p_associated_column2 => 'OTA_ACTIVITY_VERSIONS.MAXIMUM_ATTENDEES'
179 ) then
180 hr_utility.set_location(' Leaving:'|| v_proc,70);
181 raise;
182 end if;
183 hr_utility.set_location(' Leaving:'|| v_proc,80);
184 End check_min_max_values;
185 --
186 -- ----------------------------------------------------------------------------
187 -- |--------------------------< check_unique_name >---------------------------|
188 -- ----------------------------------------------------------------------------
189 --
190 -- PUBLIC
191 -- Description:
192 -- Validates the unique key.
193 --
194 Procedure check_unique_name
195 (
196 p_business_group_id in number
197 ,p_activity_id in number
198 ,p_version_name in varchar2
199 ,p_activity_version_id in number
200 ) is
201 --
202 v_proc varchar2(72) := g_package||'check_unique_name';
203 --
204 Begin
205 --
206 hr_utility.set_location('Entering:'|| v_proc, 5);
207 --
208 ota_tav_api_business_rules.check_unique_name( p_business_group_id
209 , p_activity_id
210 , p_version_name
211 , p_activity_version_id);
212 --
213 hr_utility.set_location(' Leaving:'|| v_proc, 10);
214 --
215 End check_unique_name;
216
217 -- ----------------------------------------------------------------------------
218 -- |---------------------------< chk_competency_update_level >------------------------|
219 -- ----------------------------------------------------------------------------
220 PROCEDURE chk_competency_update_level (p_activity_version_id IN number
221 ,p_object_version_number IN NUMBER
222 ,p_competency_update_level IN VARCHAR2
223 ,p_effective_date IN date) IS
224
225 --
226 l_proc VARCHAR2(72) := g_package||'chk_competency_update_level';
227 l_api_updating boolean;
228
229 BEGIN
230 hr_utility.set_location(' Leaving:'||l_proc, 10);
231 --
232 -- check mandatory parameters has been set
233 --
234 hr_api.mandatory_arg_error
235 (p_api_name => l_proc
236 ,p_argument => 'effective_date'
237 ,p_argument_value => p_effective_date);
238
239 l_api_updating := ota_tav_shd.api_updating
240 (p_activity_version_id => p_activity_version_id
241 ,p_object_version_number => p_object_version_number);
242
243
244 IF ((l_api_updating AND
245 NVL(ota_tav_shd.g_old_rec.competency_update_level,hr_api.g_varchar2) <>
246 NVL(p_competency_update_level, hr_api.g_varchar2))
247 OR NOT l_api_updating AND p_competency_update_level IS NOT NULL) THEN
248
249 hr_utility.set_location(' Leaving:'||l_proc, 20);
250 --
251
252 IF p_competency_update_level IS NOT NULL THEN
253 IF hr_api.not_exists_in_hr_lookups
254 (p_effective_date => p_effective_date
255 ,p_lookup_type => 'OTA_COMPETENCY_UPDATE_LEVEL'
256 ,p_lookup_code => p_competency_update_level) THEN
257 fnd_message.set_name('OTA','OTA_443411_COMP_UPD_LEV_INVLD');
258 fnd_message.raise_error;
259 END IF;
260 hr_utility.set_location(' Leaving:'||l_proc, 30);
261
262 END IF;
263
264 END IF;
265 hr_utility.set_location(' Leaving:'||l_proc, 40);
266
267 EXCEPTION
268
269 WHEN app_exception.application_exception THEN
270
271 IF hr_multi_message.exception_add
272 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.COMPETENCY_UPDATE_LEVEL') THEN
273
274 hr_utility.set_location(' Leaving:'||l_proc, 42);
275 RAISE;
276 END IF;
277
278 hr_utility.set_location(' Leaving:'||l_proc, 44);
279
280 END chk_competency_update_level;
281
282 --
283 -- ----------------------------------------------------------------------------
284 -- |---------------------< check_superseding_version >------------------------|
285 -- ----------------------------------------------------------------------------
286 --
287 -- PUBLIC
288 -- Description:
289 -- A activity version may not be superseded ba a activity whose end_date
290 -- is greater than it's own. The supersedinthg activity version must have
291 -- an end date greater than the end date of the activity it supersedes.
292 --
293 Procedure check_superseding_version
294 (
295 p_sup_act_vers_id in number
296 ,p_end_date in date
297 ) is
298 --
299 v_proc varchar2(72) := g_package||'check_superseding_version';
300 --
301 Begin
302 --
303 hr_utility.set_location('Entering:'|| v_proc, 5);
304 --
305 ota_tav_api_business_rules.check_superseding_version( p_sup_act_vers_id
306 , p_end_date );
307 --
308 hr_utility.set_location(' Leaving:'|| v_proc, 10);
309 --
310 exception
311 when app_exception.application_exception then
312 if hr_multi_message.exception_add
313 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.SUPERSEDE_BY_ACT_VERSION_ID'
314 ) then
315 hr_utility.set_location(' Leaving:'|| v_proc,70);
316 raise;
317 end if;
318 hr_utility.set_location(' Leaving:'|| v_proc,80);
319 End check_superseding_version;
320 --
321 -- ----------------------------------------------------------------------------
322 -- |--------------------------< check_user_status >---------------------------|
323 -- ----------------------------------------------------------------------------
324 --
325 -- PUBLIC
326 -- Description:
327 -- The user status must be in the domain 'Activity User Status'.
328 --
329 Procedure check_user_status
330 (
331 p_user_status in varchar2
332 ) is
333 --
334 v_proc varchar2(72) := g_package||'check_user_status';
335 --
336 Begin
337 --
338 hr_utility.set_location('Entering:'|| v_proc, 5);
339 --
340 ota_tav_api_business_rules.check_user_status( p_user_status );
341 --
342 hr_utility.set_location(' Leaving:'|| v_proc, 10);
343 --
344 exception
345 when app_exception.application_exception then
346 if hr_multi_message.exception_add
347 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.USER_STATUS'
348 ) then
349 hr_utility.set_location(' Leaving:'|| v_proc,70);
350 raise;
351 end if;
352 hr_utility.set_location(' Leaving:'|| v_proc,80);
353 End check_user_status;
354 --
355 -- ----------------------------------------------------------------------------
356 -- |-------------------------< check_success_criteria >-----------------------|
357 -- ----------------------------------------------------------------------------
358 --
359 -- PUBLIC
360 -- Description:
361 -- The success criteria must be in the domain 'Activity Success Criteria'.
362 --
363 Procedure check_success_criteria
364 (
365 p_succ_criteria in varchar2
366 ) is
367 --
368 v_proc varchar2(72) := g_package||'check_success_criteria';
369 --
370 Begin
371 --
372 hr_utility.set_location('Entering:'|| v_proc, 5);
373 --
374 ota_tav_api_business_rules.check_success_criteria( p_succ_criteria );
375 --
376 hr_utility.set_location(' Leaving:'|| v_proc, 10);
377 --
378 exception
379 when app_exception.application_exception then
380 if hr_multi_message.exception_add
381 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.SUCCESS_CRITERIA'
382 ) then
383 hr_utility.set_location(' Leaving:'|| v_proc,70);
384 raise;
385 end if;
386 hr_utility.set_location(' Leaving:'|| v_proc,80);
387 End check_success_criteria;
388 --
389 -- ----------------------------------------------------------------------------
390 -- |----------------------< get_activity_version_id >-------------------------|
391 -- ----------------------------------------------------------------------------
392 --
393 -- PUBLIC
394 -- Description:
395 -- Return the surrogate key from a passed parameter
396 --
397 Function get_activity_version_id
398 (
399 p_activity_id in number
400 ,p_version_name in varchar2
401 )
402 Return number is
403 --
404 v_proc varchar2(72) := g_package||'get_activity_version_id';
405 --
406 Begin
407 --
408 hr_utility.set_location('Entering:'|| v_proc, 5);
409 --
410 Return ota_tav_api_business_rules.get_activity_version_id( p_activity_id
411 , p_version_name );
412 --
413 hr_utility.set_location(' Leaving:'|| v_proc, 10);
414 --
415 End get_activity_version_id;
416 --
417 -- ----------------------------------------------------------------------------
418 -- |----------------------< get_activity_version_name >-----------------------|
419 -- ----------------------------------------------------------------------------
420 --
421 -- PUBLIC
422 -- Description:
423 -- Return the activity version name.
424 --
425 Function get_activity_version_name
426 (
427 p_activity_version_id in number
428 ) Return varchar2 is
429 --
430 v_proc varchar2(72) := g_package||'get_activity_version_name';
431 --
432 Begin
433 --
434 return ota_tav_api_business_rules.get_activity_version_name
435 ( p_activity_version_id );
436 --
437 End get_activity_version_name;
438 --
439 -- ----------------------------------------------------------------------------
440 -- |--------------------------< check_start_end_dates >-----------------------|
441 -- ----------------------------------------------------------------------------
442 --
443 -- PUBLIC
444 -- Description:
445 -- Validates the startdate and enddate.
446 -- Startdate must be less than, or equal to, enddate.
447 --
448 Procedure check_start_end_dates
449 (
450 p_start_date in date
451 ,p_end_date in date
452 ) is
453 --
454 v_proc varchar2(72) := g_package||'check_start_end_dates';
455 --
456 Begin
457 --
458 hr_utility.set_location('Entering:'|| v_proc, 5);
459 --
460 ota_tav_api_business_rules.check_start_end_dates( p_start_date
461 , p_end_date );
462 --
463 hr_utility.set_location(' Leaving:'|| v_proc, 10);
464 --
465 exception
466 when app_exception.application_exception then
467 if hr_multi_message.exception_add
468 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.START_DATE'
469 ,p_associated_column2 => 'OTA_ACTIVITY_VERSIONS.END_DATE'
470 ) then
471 hr_utility.set_location(' Leaving:'|| v_proc,70);
472 raise;
473 end if;
474 hr_utility.set_location(' Leaving:'|| v_proc,80);
475 End check_start_end_dates;
476 --
477 -- ----------------------------------------------------------------------------
478 -- |-------------------------< check_dates_update_ple >-----------------------|
479 -- ----------------------------------------------------------------------------
480 --
481 -- PUBLIC
482 -- Description:
483 -- Validates the startdate and enddate.
484 -- Update of start and end dates must not invalidate price list entry
485 -- for this activity version.
486 --
487 Procedure check_dates_update_ple
488 (
489 p_activity_version_id in number
490 ,p_start_date in date
491 ,p_end_date in date
492 ) is
493 --
494 v_start_date date;
495 v_end_date date;
496 v_proc varchar2(72) := g_package||'check_dates_update_ple';
497 --
498 cursor sel_check_dates is
499 select start_date
500 , end_date
501 from ota_price_list_entries ple
502 where ple.activity_version_id = p_activity_version_id;
503 --
504 Begin
505 if hr_multi_message.no_error_message
506 (p_check_message_name1 => 'OTA_13312_GEN_DATE_ORDER'
507 ) then
508
509 --
510 hr_utility.set_location('Entering:'|| v_proc, 5);
511 --
512 ota_tav_api_business_rules.check_dates_update_ple( p_activity_version_id
513 , p_start_date
514 , p_end_date );
515 --
516 hr_utility.set_location(' Leaving:'|| v_proc, 10);
517 --
518 end if;
519 exception
520 when app_exception.application_exception then
521 if hr_multi_message.exception_add
522 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.START_DATE'
523 ,p_associated_column2 => 'OTA_ACTIVITY_VERSIONS.END_DATE'
524 ) then
525 hr_utility.set_location(' Leaving:'|| v_proc,70);
526 raise;
527 end if;
528 hr_utility.set_location(' Leaving:'|| v_proc,80);
529 End check_dates_update_ple;
530 --
531 -- ----------------------------------------------------------------------------
532 -- |-------------------------< check_dates_update_tbd >-----------------------|
533 -- ----------------------------------------------------------------------------
534 --
535 -- PUBLIC
536 -- Description:
537 -- Validates the startdate and enddate.
538 -- Update of start and end dates must not invalidate booking deals
539 -- for this activity version.
540 --
541 Procedure check_dates_update_tbd
542 (
543 p_activity_version_id in number
544 ,p_start_date in date
545 ,p_end_date in date
546 ) is
547 --
548 v_proc varchar2(72) := g_package||'check_dates_update_tbd';
549 --
550 Begin
551 if hr_multi_message.no_error_message
552 (p_check_message_name1 => 'OTA_13312_GEN_DATE_ORDER'
553 ) then
554 --
555 hr_utility.set_location('Entering:'|| v_proc, 5);
556 --
557 ota_tav_api_business_rules.check_dates_update_tbd( p_activity_version_id
558 , p_start_date
559 , p_end_date );
560 --
561 hr_utility.set_location(' Leaving:'|| v_proc, 10);
562 --
563 end if;
564 exception
565 when app_exception.application_exception then
566 if hr_multi_message.exception_add
567 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.START_DATE'
568 ,p_associated_column2 => 'OTA_ACTIVITY_VERSIONS.END_DATE'
569 ) then
570 hr_utility.set_location(' Leaving:'|| v_proc,70);
571 raise;
572 end if;
573 hr_utility.set_location(' Leaving:'|| v_proc,80);
574 End check_dates_update_tbd;
575 --
576 -- ----------------------------------------------------------------------------
577 -- |-------------------------< check_dates_update_evt >-----------------------|
578 -- ----------------------------------------------------------------------------
579 --
580 -- PUBLIC
581 -- Description:
582 -- Validates the startdate and enddate.
583 -- Update of start and end dates must not invalidate events
584 -- for this activity version.
585 -- This requires a check to ensure that the activity version dates do not
586 -- invalidate the Event Booking DAtes or the Event Course Dates if either
587 -- have been entered.
588 --
589 Procedure check_dates_update_evt
590 (
591 p_activity_version_id in number
592 ,p_start_date in date
593 ,p_end_date in date
594 ) is
595 --
596 v_proc varchar2(72) := g_package||'check_dates_update_evt';
597 --
598 Begin
599 if hr_multi_message.no_error_message
600 (p_check_message_name1 => 'OTA_13312_GEN_DATE_ORDER'
601 ) then
602 --
603 hr_utility.set_location('Entering:'|| v_proc, 5);
604 --
605 ota_tav_api_business_rules.check_dates_update_evt( p_activity_version_id
606 , p_start_date
607 , p_end_date );
608 --
609 hr_utility.set_location(' Leaving:'|| v_proc, 10);
610 --
611 end if;
612 exception
613 when app_exception.application_exception then
614 if hr_multi_message.exception_add
615 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.START_DATE'
616 ,p_associated_column2 => 'OTA_ACTIVITY_VERSIONS.END_DATE'
617 ) then
618 hr_utility.set_location(' Leaving:'|| v_proc,70);
619 raise;
620 end if;
621 hr_utility.set_location(' Leaving:'|| v_proc,80);
622 End check_dates_update_evt;
623 /* bug 3795299
624 -- ----------------------------------------------------------------------------
625 -- |-------------------------< check_dates_update_tpm >-----------------------|
626 -- ----------------------------------------------------------------------------
627 --
628 -- PUBLIC
629 -- Description:
630 -- Validates the startdate and enddate.
631 -- Update of start and end dates must not invalidate training plan members
632 -- for this activity version.
633
634 Procedure check_dates_update_tpm
635 (
636 p_activity_version_id in number
637 ,p_start_date in date
638 ,p_end_date in date
639 ) is
640 --
641 v_proc varchar2(72) := g_package||'check_dates_update_tpm';
642 --
643 Begin
644 if hr_multi_message.no_error_message
645 (p_check_message_name1 => 'OTA_13312_GEN_DATE_ORDER'
646 ) then
647 --
648 hr_utility.set_location('Entering:'|| v_proc, 5);
649 --
650 ota_trng_plan_util_ss.chk_valid_act_version_dates
651 (p_activity_version_id
652 ,p_start_date
653 ,p_end_date);
654 --
655 hr_utility.set_location(' Leaving:'|| v_proc, 10);
656 --
657 end if;
658 exception
659 when app_exception.application_exception then
660 if hr_multi_message.exception_add
661 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.START_DATE'
662 ,p_associated_column2 => 'OTA_ACTIVITY_VERSIONS.END_DATE'
663 ) then
664 hr_utility.set_location(' Leaving:'|| v_proc,70);
665 raise;
666 end if;
667 hr_utility.set_location(' Leaving:'|| v_proc,80);
668 End check_dates_update_tpm;
669 */
670 --
671 -- ----------------------------------------------------------------------------
672 -- |-------------------------< check_if_evt_exists >--------------------------|
673 -- ----------------------------------------------------------------------------
674 --
675 -- PUBLIC
676 -- Description:
677 -- Delete Validation.
678 -- This activity version may not be deleted if child rows in
679 -- ota_events exist.
680 --
681 Procedure check_if_evt_exists
682 (
683 p_activity_version_id in number
684 ) is
685 --
686 v_proc varchar2(72) := g_package||'check_if_evt_exists';
687 --
688 Begin
689 --
690 hr_utility.set_location('Entering:'|| v_proc, 5);
691 --
692 ota_tav_api_business_rules.check_if_evt_exists( p_activity_version_id );
693 --
694 hr_utility.set_location(' Leaving:'|| v_proc, 10);
695 --
696 exception
697 when app_exception.application_exception then
698 if hr_multi_message.exception_add
699 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.ACTIVITY_VERSION_ID'
700 ) then
701 hr_utility.set_location(' Leaving:'|| v_proc,70);
702 raise;
703 end if;
704 hr_utility.set_location(' Leaving:'|| v_proc,80);
705 End check_if_evt_exists;
706 -- ----------------------------------------------------------------------------
707 -- |-------------------------< check_if_evt_exists >--------------------------|
708 -- ----------------------------------------------------------------------------
709 --
710 -- PUBLIC
711 -- Description:
712 -- Delete Validation.
713 -- This activity version may not be deleted if child rows in
714 -- ota_offerings exist.
715 --
716 Procedure check_if_off_exists
717 (
718 p_activity_version_id in number
719 ) is
720 --
721 v_proc varchar2(72) := g_package||'check_if_off_exists';
722 --
723 Begin
724 --
725 hr_utility.set_location('Entering:'|| v_proc, 5);
726 --
727 ota_tav_api_business_rules.check_if_off_exists( p_activity_version_id );
728 --
729 hr_utility.set_location(' Leaving:'|| v_proc, 10);
730 --
731 exception
732 when app_exception.application_exception then
733 if hr_multi_message.exception_add
734 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.ACTIVITY_VERSION_ID'
735 ) then
736 hr_utility.set_location(' Leaving:'|| v_proc,70);
737 raise;
738 end if;
739 hr_utility.set_location(' Leaving:'|| v_proc,80);
740 End check_if_off_exists;
741 -- ---------------------------------------------------------------------------
742
743 -- ---------------------------------------------------------------------------
744 -- |-------------------------< check_if_tpm_exists >--------------------------
745 -- ---------------------------------------------------------------------------
746 -- PUBLIC
747 -- Description:
748 -- Delete Validation.
749 -- This activity version may not be deleted if child rows in
750 -- ota_training_plan_members exist.
751 --
752 Procedure check_if_tpm_exists
753 (
754 p_activity_version_id in number
755 ) is
756 --
757 v_proc varchar2(72) := g_package||'check_if_tpm_exists';
758 --
759 Begin
760 --
761 hr_utility.set_location('Entering:'|| v_proc, 5);
762 --
763 ota_tav_api_business_rules.check_if_tpm_exists( p_activity_version_id );
764 --
765 hr_utility.set_location(' Leaving:'|| v_proc, 10);
766 --
767 --
768 exception
769 when app_exception.application_exception then
770 if hr_multi_message.exception_add
771 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.ACTIVITY_VERSION_ID'
772 ) then
773 hr_utility.set_location(' Leaving:'|| v_proc,70);
774 raise;
775 end if;
776 hr_utility.set_location(' Leaving:'|| v_proc,80);
777 End check_if_tpm_exists;
778 --
779 --
780 -- ---------------------------------------------------------------------------
781 -- |-------------------------< check_if_lpm_exists >--------------------------
782 -- ---------------------------------------------------------------------------
783 -- PUBLIC
784 -- Description:
785 --Delete Validation.
786 -- This activity version may not be deleted if child rows in
787 -- ota_learning_path_members exist.
788 --
789 --
790 Procedure check_if_lpm_exists
791 (
792 p_activity_version_id in number
793 ) is
794 --
795 v_proc varchar2(72) := g_package||'check_if_lpm_exists';
796 --
797 Begin
798 --
799 hr_utility.set_location('Entering:'|| v_proc, 5);
800 --
801 ota_tav_api_business_rules.check_if_lpm_exists( p_activity_version_id );
802 --
803 hr_utility.set_location(' Leaving:'|| v_proc, 10);
804 --
805 --
806 exception
807 when app_exception.application_exception then
808 if hr_multi_message.exception_add
809 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.ACTIVITY_VERSION_ID'
810 ) then
811 hr_utility.set_location(' Leaving:'|| v_proc,70);
812 raise;
813 end if;
814 hr_utility.set_location(' Leaving:'|| v_proc,80);
815 End check_if_lpm_exists;
816 --
817 --
818
819 -- ----------------------------------------------------------------------------
820 -- |-------------------------< check_if_tbd_exists >--------------------------|
821 -- ----------------------------------------------------------------------------
822 --
823 -- PUBLIC
824 -- Description:
825 -- Delete Validation.
826 -- This activity version may not be deleted if child rows in
827 -- ota_booking_deals exist.
828 --
829 Procedure check_if_tbd_exists
830 (
831 p_activity_version_id in number
832 ) is
833 --
834 v_proc varchar2(72) := g_package||'check_if_tbd_exists';
835 --
836 Begin
837 --
838 hr_utility.set_location('Entering:'|| v_proc, 5);
839 --
840 ota_tav_api_business_rules.check_if_tbd_exists( p_activity_version_id );
841 --
842 hr_utility.set_location(' Leaving:'|| v_proc, 10);
843 --
844 exception
845 when app_exception.application_exception then
846 if hr_multi_message.exception_add
847 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.ACTIVITY_VERSION_ID'
848 ) then
849 hr_utility.set_location(' Leaving:'|| v_proc,70);
850 raise;
851 end if;
852 hr_utility.set_location(' Leaving:'|| v_proc,80);
853 End check_if_tbd_exists;
854 --
855
856 -- ----------------------------------------------------------------------------
857 -- |-------------------------< check_if_ple_exists >--------------------------|
858 -- ----------------------------------------------------------------------------
859 --
860 -- PUBLIC
861 -- Description:
862 -- Delete Validation.
863 -- This activity version may not be deleted if child rows in
864 -- ota_price_lists_entries exist.
865 --
866 Procedure check_if_ple_exists
867 (
868 p_activity_version_id in number
869 ) is
870 --
871 v_proc varchar2(72) := g_package||'check_if_ple_exists';
872 --
873 Begin
874 --
875 hr_utility.set_location('Entering:'|| v_proc, 5);
876 --
877 ota_tav_api_business_rules.check_if_ple_exists( p_activity_version_id );
878 --
879 hr_utility.set_location(' Leaving:'|| v_proc, 10);
880 --
881 exception
882 when app_exception.application_exception then
883 if hr_multi_message.exception_add
884 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.ACTIVITY_VERSION_ID'
885 ) then
886 hr_utility.set_location(' Leaving:'|| v_proc,70);
887 raise;
888 end if;
889 hr_utility.set_location(' Leaving:'|| v_proc,80);
890 End check_if_ple_exists;
891 --
892
893 -- ----------------------------------------------------------------------------
894 -- |-------------------------< check_if_comp_exists >-------------------------|
895 -- ----------------------------------------------------------------------------
896 --
897 -- PUBLIC
898 -- Description:
899 -- Delete Validation.
900 -- This activity version may not be deleted if child rows in
901 -- ota_price_lists_entries exist.
902 --
903 Procedure check_if_comp_exists
904 (
905 p_activity_version_id in number
906 ) is
907 --
908 v_proc varchar2(72) := g_package||'check_if_comp_exists';
909 --
910 Begin
911 --
912 hr_utility.set_location('Entering:'|| v_proc, 5);
913 --
914 ota_tav_api_business_rules.check_if_comp_exists( p_activity_version_id );
915 --
916 hr_utility.set_location(' Leaving:'|| v_proc, 10);
917 --
918 exception
919 when app_exception.application_exception then
920 if hr_multi_message.exception_add
921 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.ACTIVITY_VERSION_ID'
922 ) then
923 hr_utility.set_location(' Leaving:'|| v_proc,70);
924 raise;
925 end if;
926 hr_utility.set_location(' Leaving:'|| v_proc,80);
927 End check_if_comp_exists;
928
929 -- ----------------------------------------------------------------------------
930 -- |-------------------------< check_if_tav_exists >--------------------------|
931 -- ----------------------------------------------------------------------------
932 --
933 -- PUBLIC
934 -- Description:
935 -- Delete Validation.
936 -- This activity version may not be deleted if child rows in
937 -- ota_activity_versions exists where this activity version has superseded
938 -- another earlier activity version.
939 --
940 Procedure check_if_tav_exists
941 (
942 p_activity_version_id in number
943 ) is
944 --
945 v_proc varchar2(72) := g_package||'check_if_tav_exists';
946 --
947 Begin
948 --
949 hr_utility.set_location('Entering:'|| v_proc, 5);
950 --
951 ota_tav_api_business_rules.check_if_tav_exists( p_activity_version_id );
952 --
953 hr_utility.set_location(' Leaving:'|| v_proc, 10);
954 --
955 exception
956 when app_exception.application_exception then
957 if hr_multi_message.exception_add
958 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.ACTIVITY_VERSION_ID'
959 ) then
960 hr_utility.set_location(' Leaving:'|| v_proc,70);
961 raise;
962 end if;
963 hr_utility.set_location(' Leaving:'|| v_proc,80);
964 End check_if_tav_exists;
965 --
966 -- ----------------------------------------------------------------------------
967 -- |-------------------------< check_if_tsp_exists >--------------------------|
968 -- ----------------------------------------------------------------------------
969 --
970 -- PUBLIC
971 -- Description:
972 -- Delete Validation.
973 -- This activity version may not be deleted if child rows in
974 -- ota_skill_provisions
975 Procedure check_if_tsp_exists
976 (
977 p_activity_version_id in number
978 ) is
979 --
980 v_proc varchar2(72) := g_package||'check_if_tsp_exists';
981 --
982 Begin
983 --
984 hr_utility.set_location('Entering:'|| v_proc, 5);
985 --
986 ota_tav_api_business_rules.check_if_tsp_exists( p_activity_version_id );
987 --
988 hr_utility.set_location(' Leaving:'|| v_proc, 10);
989 --
990 exception
991 when app_exception.application_exception then
992 if hr_multi_message.exception_add
993 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.ACTIVITY_VERSION_ID'
994 ) then
995 hr_utility.set_location(' Leaving:'|| v_proc,70);
996 raise;
997 end if;
998 hr_utility.set_location(' Leaving:'|| v_proc,80);
999 End check_if_tsp_exists;
1000 --
1001
1002 --
1003 -- ----------------------------------------------------------------------------
1004 -- |------------------------< check_duration_units >--------------------------|
1005 -- ----------------------------------------------------------------------------
1006 --
1007 -- PUBLIC
1008 -- Description:
1009 -- The duration units must be in the domain 'Units'.
1010 --
1011 Procedure check_duration_units
1012 (
1013 p_duration_units in varchar2
1014 ) is
1015 --
1016 v_proc varchar2(72) := g_package||'check_duration_units';
1017 --
1018 Begin
1019 --
1020 hr_utility.set_location('Entering:'|| v_proc, 5);
1021 --
1022 ota_tav_api_business_rules.check_duration_units( p_duration_units );
1023 --
1024 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1025 --
1026 exception
1027 when app_exception.application_exception then
1028 if hr_multi_message.exception_add
1029 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.DURATION_UNITS'
1030 ) then
1031 hr_utility.set_location(' Leaving:'|| v_proc,70);
1032 raise;
1033 end if;
1034 hr_utility.set_location(' Leaving:'|| v_proc,80);
1035 End check_duration_units;
1036 --
1037 -- ----------------------------------------------------------------------------
1038 -- |--------------------------< check_duration >------------------------------|
1039 -- ----------------------------------------------------------------------------
1040 --
1041 -- PUBLIC
1042 -- Description:
1043 -- The duration must be a positive integer greater than zero.
1044 --
1045 Procedure check_duration
1046 (
1047 p_duration in number
1048 ) is
1049 --
1050 v_proc varchar2(72) := g_package||'check_duration';
1051 --
1052 Begin
1053 --
1054 hr_utility.set_location('Entering:'|| v_proc, 5);
1055 --
1056 ota_tav_api_business_rules.check_duration( p_duration );
1057 --
1058 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1059 --
1060 exception
1061 when app_exception.application_exception then
1062 if hr_multi_message.exception_add
1063 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.DURATION'
1064 ) then
1065 hr_utility.set_location(' Leaving:'|| v_proc,70);
1066 raise;
1067 end if;
1068 hr_utility.set_location(' Leaving:'|| v_proc,80);
1069 End check_duration;
1070 -- ----------------------------------------------------------------------------
1071 -- |---------------------------< check_dur_unit_comb >-----------------------------|
1072 -- ----------------------------------------------------------------------------
1073
1074
1075 procedure check_dur_unit_comb( p_duration number,p_duration_units varchar2) is
1076
1077 begin
1078 if( p_duration < 0 )
1079 then
1080 fnd_message.set_name('OTA','OTA_443368_POSITIVE_NUMBER');
1081 fnd_message.raise_error;
1082 end if;
1083
1084 if( (p_duration is null and p_duration_units is not null) or (p_duration is not null and p_duration_units is null) )
1085 then
1086 fnd_message.set_name('OTA','OTA_13881_NHS_COMB_INVALID');
1087 fnd_message.raise_error;
1088 end if;
1089 Exception
1090 WHEN app_exception.application_exception THEN
1091
1092 IF hr_multi_message.exception_add(
1093 p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.DURATION'
1094 ,p_associated_column2 => 'OTA_ACTIVITY_VERSIONS.DURATION_UNITS')
1095 THEN
1096
1097 --hr_utility.set_location(' Leaving:'||v_proc, 22);
1098 RAISE;
1099
1100 END IF;
1101 end check_dur_unit_comb;
1102
1103 --
1104 -- ----------------------------------------------------------------------------
1105 -- |---------------------------< check_language >-----------------------------|
1106 -- ----------------------------------------------------------------------------
1107 --
1108 -- PUBLIC
1109 -- Description:
1110 -- The language must be in the domain 'Languages'.
1111 --
1112 Procedure check_language
1113 (
1114 p_language_id in number
1115 ) is
1116 v_proc varchar2(72) := g_package||'check_language';
1117 --
1118 Begin
1119 --
1120 hr_utility.set_location('Entering:'|| v_proc, 5);
1121 --
1122 ota_tav_api_business_rules.check_language( p_language_id );
1123 --
1124 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1125 --
1126 exception
1127 when app_exception.application_exception then
1128 if hr_multi_message.exception_add
1129 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.LANGUAGE_ID'
1130 ) then
1131 hr_utility.set_location(' Leaving:'|| v_proc,70);
1132 raise;
1133 end if;
1134 hr_utility.set_location(' Leaving:'|| v_proc,80);
1135 End check_language;
1136 --
1137 -- ----------------------------------------------------------------------------
1138 -- |-------------------< check_controlling_person >---------------------------|
1139 -- ----------------------------------------------------------------------------
1140 --
1141 -- PUBLIC
1142 -- Description:
1143 -- The controlling person should exist as a valid person on the Validity
1144 -- Start Date of the Activity Version.
1145 --
1146 Procedure check_controlling_person
1147 (
1148 p_person_id in number
1149 ,p_date in date
1150 ) is
1151 --
1152 v_proc varchar2(72) := g_package||'check_controlling_person';
1153 --
1154 Begin
1155 --
1156 hr_utility.set_location('Entering:'|| v_proc, 5);
1157 --
1158 ota_tav_api_business_rules.check_controlling_person( p_person_id
1159 , p_date );
1160 --
1161 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1162 --
1163 exception
1164 when app_exception.application_exception then
1165 if hr_multi_message.exception_add
1166 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.CONTROLLING_PERSON_ID'
1167 ) then
1168 hr_utility.set_location(' Leaving:'|| v_proc,70);
1169 raise;
1170 end if;
1171 hr_utility.set_location(' Leaving:'|| v_proc,80);
1172 End check_controlling_person;
1173 --
1174 -- ----------------------------------------------------------------------------
1175 -- |-------------------< check_multiple_con_version >-----------------------|
1176 -- ----------------------------------------------------------------------------
1177 --
1178 -- PUBLIC
1179 -- Description:
1180 -- If the Activity Definitions is specified with the
1181 -- MULTIPLE_CON_VERSIONS_FLAG set to 'N' then Versions of the Activity may not
1182 -- have overlapping validity dates.
1183 --
1184 Procedure check_multiple_con_version
1185 (
1186 p_activity_id in number,
1187 p_activity_version_id in number,
1188 p_start_date in date,
1189 p_end_date in date
1190 ) is
1191 --
1192 v_proc varchar2(72) := g_package||'check_multiple_con_versions';
1193 --
1194 Begin
1195 --
1196 hr_utility.set_location('Entering:'|| v_proc, 5);
1197 --
1198 ota_tav_api_business_rules.check_multiple_con_version
1199 ( p_activity_id
1200 , p_activity_version_id
1201 , p_start_date
1202 , p_end_date);
1203 --
1204 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1205 --
1206 exception
1207 when app_exception.application_exception then
1208 if hr_multi_message.exception_add
1209 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.ACTIVITY_VERSION_ID'
1210 ,p_associated_column2 => 'OTA_ACTIVITY_VERSIONS.START_DATE'
1211 ,p_associated_column3 => 'OTA_ACTIVITY_VERSIONS.END_DATE'
1212 ) then
1213 hr_utility.set_location(' Leaving:'|| v_proc,70);
1214 raise;
1215 end if;
1216 hr_utility.set_location(' Leaving:'|| v_proc,80);
1217 End check_multiple_con_version;
1218 --
1219 -- ----------------------------------------------------------------------------
1220 -- |------------------< check_version_after_supersede >-----------------------|
1221 -- ----------------------------------------------------------------------------
1222 --
1223 -- PUBLIC
1224 -- Description:
1225 -- If the Activity Definitions is specified with the
1226 -- MULTIPLE_CON_VERSIONS_FLAG set to 'N' and the latest Activity Version has
1227 -- been superseded by a Version of a different Activity, then new Version of
1228 -- the Activity are not allowed (because there would be confusion over which
1229 -- is the valid version of the activity, the new one or the superseding one).
1230 --
1231 Procedure check_version_after_supersede
1232 (
1233 p_activity_id in number
1234 ) is
1235 --
1236 v_proc varchar2(72) := g_package||'check_version_after_supersede';
1237 --
1238 Begin
1239 --
1240 hr_utility.set_location('Entering:'|| v_proc, 5);
1241 --
1242 ota_tav_api_business_rules.check_version_after_supersede( p_activity_id );
1243 --
1244 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1245 --
1246 exception
1247 when app_exception.application_exception then
1248 if hr_multi_message.exception_add
1249 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.ACTIVITY_ID'
1250 ) then
1251 hr_utility.set_location(' Leaving:'|| v_proc,70);
1252 raise;
1253 end if;
1254 hr_utility.set_location(' Leaving:'|| v_proc,80);
1255 End check_version_after_supersede;
1256
1257 --
1258 -- ----------------------------------------------------------------------------
1259 -- |-----------------------------< check_course_lp_dates>------------------------|
1260 -- ----------------------------------------------------------------------------
1261 --
1262 -- PUBLIC
1263 -- Description:
1264 -- Check dates of Course and Learning Path
1265 --
1266 --
1267 --
1268 --
1269 Procedure check_course_lp_dates
1270 (
1271 p_activity_version_id IN NUMBER,
1272 p_start_date IN DATE,
1273 p_end_date IN DATE)
1274
1275 IS
1276
1277 l_proc varchar2(72) := g_package||'check_course_lp_dates';
1278 --
1279 l_start_date_changed boolean
1280 := ota_general.value_changed( ota_tav_shd.g_old_rec.start_date
1281 , p_start_date );
1282 --
1283 l_end_date_changed boolean
1284 := ota_general.value_changed( ota_tav_shd.g_old_rec.end_date
1285 , p_end_date );
1286
1287 Begin
1288
1289 hr_utility.set_location('Entering:'||l_proc, 5);
1290 ota_tav_api_business_rules.check_course_lp_dates( p_activity_version_id, p_start_date, p_end_date );
1291
1292 exception
1293 when app_exception.application_exception then
1294 if l_start_date_changed AND hr_multi_message.exception_add
1295 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.START_DATE'
1296 ) then
1297 hr_utility.set_location(' Leaving:'|| l_proc,70);
1298 raise;
1299 elsif l_end_date_changed AND hr_multi_message.exception_add
1300 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.END_DATE'
1301 ) then
1302 hr_utility.set_location(' Leaving:'|| l_proc,80);
1303 raise;
1304 end if;
1305 hr_utility.set_location(' Leaving:'|| l_proc,90);
1306 hr_utility.set_location('Leaving:'||l_proc, 30);
1307 End;
1308
1309 --
1310 -- ----------------------------------------------------------------------------
1311 -- |-----------------------------< check_course_crt_dates>---------------------|
1312 -- ----------------------------------------------------------------------------
1313 --
1314 -- PUBLIC
1315 -- Description:
1316 -- Check dates of Course and certification
1317 --
1318 --
1319 --
1320 --
1321 Procedure check_course_crt_dates
1322 (
1323 p_activity_version_id IN NUMBER,
1324 p_start_date IN DATE,
1325 p_end_date IN DATE)
1326
1327 IS
1328
1329 l_proc varchar2(72) := g_package||'check_course_crt_dates';
1330 --
1331 l_start_date_changed boolean
1332 := ota_general.value_changed( ota_tav_shd.g_old_rec.start_date
1333 , p_start_date );
1334 --
1335 l_end_date_changed boolean
1336 := ota_general.value_changed( ota_tav_shd.g_old_rec.end_date
1337 , p_end_date );
1338
1339 Begin
1340
1341 hr_utility.set_location('Entering:'||l_proc, 5);
1342 ota_tav_api_business_rules.check_course_crt_dates( p_activity_version_id, p_start_date, p_end_date );
1343
1344 exception
1345 when app_exception.application_exception then
1346 if l_start_date_changed AND hr_multi_message.exception_add
1347 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.START_DATE'
1348 ) then
1349 hr_utility.set_location(' Leaving:'|| l_proc,70);
1350 raise;
1351 elsif l_end_date_changed AND hr_multi_message.exception_add
1352 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.END_DATE'
1353 ) then
1354 hr_utility.set_location(' Leaving:'|| l_proc,80);
1355 raise;
1356 end if;
1357 hr_utility.set_location(' Leaving:'|| l_proc,90);
1358 hr_utility.set_location('Leaving:'||l_proc, 30);
1359 End check_course_crt_dates;
1360
1361 --
1362 -- ----------------------------------------------------------------------------
1363 -- |-----------------------------< chk_category >---------------------|
1364 -- ----------------------------------------------------------------------------
1365 --
1366 -- PUBLIC
1367 -- Description:
1368 -- Check whether course is getting created under root category
1369 --
1370 --
1371 --
1372
1373 PROCEDURE chk_category (p_activity_id IN number
1374 ) IS
1375
1376 --
1377 l_proc VARCHAR2(72) := g_package||'chk_category';
1378 l_api_updating boolean;
1379 l_parent_cat_usage_id number:= null;
1380
1381 cursor csr_is_root_category is
1382 select cat.parent_cat_usage_id
1383 from
1384 ota_activity_definitions oad,
1385 ota_category_usages cat
1386 where
1387 oad.category_usage_id=cat.category_usage_id
1388 and oad.activity_id=p_activity_id;
1389
1390
1391 BEGIN
1392 hr_utility.set_location(' Entering:'||l_proc, 10);
1393 --
1394 OPEN csr_is_root_category;
1395 FETCH csr_is_root_category into l_parent_cat_usage_id;
1396 CLOSE csr_is_root_category;
1397
1398
1399
1400 IF l_parent_cat_usage_id IS NULL THEN
1401 fnd_message.set_name('OTA','OTA_443361_COURSE_IN_ROOT_CAT');
1402 fnd_message.raise_error;
1403 END IF;
1404 hr_utility.set_location(' Leaving:'||l_proc, 20);
1405
1406 EXCEPTION
1407
1408 WHEN app_exception.application_exception THEN
1409
1410 IF hr_multi_message.exception_add
1411 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.COMPETENCY_UPDATE_LEVEL') THEN
1412
1413 hr_utility.set_location(' Leaving:'||l_proc, 30);
1414 RAISE;
1415 END IF;
1416
1417 hr_utility.set_location(' Leaving:'||l_proc, 40);
1418
1419 END chk_category;
1420
1421
1422 -- ----------------------------------------------------------------------------
1423 -- |---------------------------< insert_validate >----------------------------|
1424 -- ----------------------------------------------------------------------------
1425 Procedure insert_validate(p_rec in ota_tav_shd.g_rec_type) is
1426 --
1427 l_proc varchar2(72) := g_package||'insert_validate';
1428 --
1429 Begin
1430 hr_utility.set_location('Entering:'||l_proc, 5);
1431 --
1432 -- Validate Important Attributes
1433 --
1434 if p_rec.business_group_id is not null then
1435 hr_api.validate_bus_grp_id
1436 (p_business_group_id => p_rec.business_group_id
1437 ,p_associated_column1 => ota_tav_shd.g_tab_nam ||
1438 '.BUSINESS_GROUP_ID'
1439 );
1440 end if;
1441 --
1442 -- After validating the set of important attributes,
1443 -- if multiple message detection is enabled and atleast
1444 -- one error has been found then abort further validation.
1445 --
1446 hr_multi_message.end_validation_set;
1447 --
1448 -- Validate Dependent Attributes
1449 --
1450 --
1451 -- Call all supporting business operations
1452 --
1453 --
1454 check_min_max_values( p_rec.minimum_attendees
1455 , p_rec.maximum_attendees);
1456 --
1457
1458 check_unique_name( null
1459 , p_rec.activity_id
1460 , p_rec.version_name
1461 , p_rec.activity_version_id);
1462
1463 --
1464 check_superseding_version( p_rec.superseded_by_act_version_id
1465 , p_rec.end_date);
1466 --
1467 check_version_after_supersede( p_rec.activity_id );
1468 --
1469 check_user_status( p_rec.user_status);
1470 --
1471 check_success_criteria( p_rec.success_criteria);
1472 --
1473 check_start_end_dates( p_rec.start_date
1474 , p_rec.end_date );
1475 --
1476 check_duration_units( p_rec.duration_units);
1477 --
1478 check_duration( p_rec.duration);
1479 check_dur_unit_comb( p_rec.duration,p_rec.duration_units);
1480 --
1481 check_language( p_rec.language_id);
1482 chk_competency_update_level (p_activity_version_id => p_rec.activity_version_id
1483 ,p_object_version_number => p_rec.object_version_number
1484 ,p_competency_update_level => p_rec.competency_update_level
1485 ,p_effective_date => trunc(sysdate));
1486
1487 --
1488 ota_tav_api_business_rules.check_Inventory_item_id(
1489 p_rec.activity_version_id ,
1490 p_rec.inventory_item_id ,
1491 p_rec.organization_id );
1492 --
1493 -- **** Bug #2154926 changed call to check_controlling_person to use
1494 -- **** p_rec.end_date rather than p_rec.start_date
1495 --
1496 check_controlling_person( p_rec.controlling_person_id, p_rec.end_date);
1497 --
1498 check_multiple_con_version( p_rec.activity_id
1499 , p_rec.activity_version_id
1500 , p_rec.start_date
1501 , p_rec.end_date);
1502 --
1503 ota_tav_api_business_rules.check_vendor (p_rec.vendor_id);
1504 --
1505 ota_tav_api_business_rules.check_currency(p_rec.budget_currency_code);
1506 --
1507 ota_tav_api_business_rules.check_cost_vals
1508 (p_rec.budget_currency_code
1509 ,p_rec.budget_cost
1510 ,p_rec.actual_cost);
1511 --
1512 ota_tav_api_business_rules.check_professional_credit_vals
1513 (p_rec.professional_credit_type
1514 ,p_rec.professional_credits);
1515 --
1516 ota_tav_api_business_rules.check_professional_credit_type
1517 (p_rec.professional_credit_type);
1518
1519 /* For ILearning */
1520 ota_tav_api_business_rules.check_unique_rco_id
1521 (p_rec.activity_version_id,
1522 p_rec.rco_id);
1523 --
1524 ota_tav_bus.chk_ddf(p_rec);
1525
1526 chk_category(p_rec.activity_id);
1527 hr_utility.set_location(' Leaving:'||l_proc, 10);
1528 End insert_validate;
1529 --
1530 -- ----------------------------------------------------------------------------
1531 -- |---------------------------< update_validate >----------------------------|
1532 -- ----------------------------------------------------------------------------
1533 --
1534 Procedure update_validate(p_rec in ota_tav_shd.g_rec_type) is
1535 --
1536 l_proc varchar2(72) := g_package||'update_validate';
1537 --
1538 l_api_updating boolean
1539 := ota_tav_shd.api_updating(p_rec.activity_version_id
1540 ,p_rec.object_version_number);
1541 --
1542 l_minimum_attendees_changed boolean
1543 := ota_general.value_changed( ota_tav_shd.g_old_rec.minimum_attendees
1544 , p_rec.minimum_attendees );
1545 --
1546 l_maximum_attendees_changed boolean
1547 := ota_general.value_changed( ota_tav_shd.g_old_rec.maximum_attendees
1548 , p_rec.maximum_attendees );
1549 --
1550 l_activity_version_id_changed boolean
1551 := ota_general.value_changed( ota_tav_shd.g_old_rec.activity_version_id
1552 , p_rec.activity_version_id );
1553 --
1554 l_activity_id_changed boolean
1555 := ota_general.value_changed( ota_tav_shd.g_old_rec.activity_id
1556 , p_rec.activity_id );
1557 --
1558 l_version_name_changed boolean
1559 := ota_general.value_changed( ota_tav_shd.g_old_rec.version_name
1560 , p_rec.version_name );
1561 --
1562 l_super_by_act_vers_id_changed boolean
1563 := ota_general.value_changed( ota_tav_shd.g_old_rec.superseded_by_act_version_id
1564 , p_rec.superseded_by_act_version_id );
1565 --
1566 l_start_date_changed boolean
1567 := ota_general.value_changed( ota_tav_shd.g_old_rec.start_date
1568 , p_rec.start_date );
1569 --
1570 l_end_date_changed boolean
1571 := ota_general.value_changed( ota_tav_shd.g_old_rec.end_date
1572 , p_rec.end_date );
1573 --
1574 l_user_status_changed boolean
1575 := ota_general.value_changed( ota_tav_shd.g_old_rec.user_status
1576 , p_rec.user_status );
1577 --
1578 l_success_criteria_changed boolean
1579 := ota_general.value_changed( ota_tav_shd.g_old_rec.success_criteria
1580 , p_rec.success_criteria );
1581 --
1582 l_duration_units_changed boolean
1583 := ota_general.value_changed( ota_tav_shd.g_old_rec.duration_units
1584 , p_rec.duration_units );
1585 --
1586 l_duration_changed boolean
1587 := ota_general.value_changed( ota_tav_shd.g_old_rec.duration
1588 , p_rec.duration );
1589 --
1590 l_language_id_changed boolean
1591 := ota_general.value_changed( ota_tav_shd.g_old_rec.language_id
1592 , p_rec.language_id );
1593 --
1594 l_person_id_changed boolean
1595 := ota_general.value_changed( ota_tav_shd.g_old_rec.controlling_person_id
1596 , p_rec.controlling_person_id );
1597 --
1598 l_vendor_id_changed boolean
1599 := ota_general.value_changed( ota_tav_shd.g_old_rec.vendor_id
1600 , p_rec.vendor_id);
1601 --
1602 l_professional_ctype_changed boolean
1603 := ota_general.value_changed( ota_tav_shd.g_old_rec.professional_credit_type
1604 , p_rec.professional_credit_type);
1605 --
1606 l_professional_credits_changed boolean
1607 := ota_general.value_changed( ota_tav_shd.g_old_rec.professional_credits
1608 , p_rec.professional_credits);
1609 --
1610 l_budget_currency_code_changed boolean
1611 := ota_general.value_changed( ota_tav_shd.g_old_rec.budget_currency_code
1612 , p_rec.budget_currency_code);
1613 --
1614 l_budget_cost_changed boolean
1615 := ota_general.value_changed( ota_tav_shd.g_old_rec.budget_cost
1616 , p_rec.budget_cost);
1617 --
1618 l_actual_cost_changed boolean
1619 := ota_general.value_changed( ota_tav_shd.g_old_rec.actual_cost
1620 , p_rec.actual_cost);
1621 --
1622 l_inventory_item_id_changed boolean
1623 := ota_general.value_changed (ota_tav_shd.g_old_rec.inventory_item_id
1624 , p_rec.inventory_item_id);
1625
1626 l_rco_id_changed boolean
1627 := ota_general.value_changed (ota_tav_shd.g_old_rec.rco_id
1628 , p_rec.rco_id);
1629
1630 --
1631 Begin
1632 hr_utility.set_location('Entering:'||l_proc, 5);
1633 --
1634 -- Validate Important Attributes
1635 --
1636 if p_rec.business_group_id is not null then
1637 hr_api.validate_bus_grp_id
1638 (p_business_group_id => p_rec.business_group_id
1639 ,p_associated_column1 => ota_tav_shd.g_tab_nam ||
1640 '.BUSINESS_GROUP_ID'
1641 );
1642 end if;
1643 --
1644 -- After validating the set of important attributes,
1645 -- if multiple message detection is enabled and atleast
1646 -- one error has been found then abort further validation.
1647 hr_multi_message.end_validation_set;
1648 --
1649 --
1650 -- Validate Dependent Attributes
1651 --
1652 --
1653 -- Call all supporting business operations
1654 --
1655 If l_minimum_attendees_changed Or
1656 l_maximum_attendees_changed Then
1657 --
1658 check_min_max_values( p_rec.minimum_attendees
1659 , p_rec.maximum_attendees);
1660 --
1661 End if;
1662
1663
1664 chk_competency_update_level (p_activity_version_id => p_rec.activity_version_id
1665 ,p_object_version_number => p_rec.object_version_number
1666 ,p_competency_update_level => p_rec.competency_update_level
1667 ,p_effective_date => trunc(sysdate));
1668
1669
1670
1671 If l_activity_id_changed Or
1672 l_version_name_changed Then
1673 --
1674 check_unique_name( null
1675 , p_rec.activity_id
1676 , p_rec.version_name
1677 , p_rec.activity_version_id);
1678
1679 chk_category(p_rec.activity_id);
1680
1681 --
1682 End if;
1683 --
1684 If l_super_by_act_vers_id_changed Or
1685 l_end_date_changed Then
1686 --
1687 check_superseding_version( p_rec.superseded_by_act_version_id
1688 , p_rec.end_date);
1689 --
1690 End if;
1691 --
1692 If l_user_status_changed Then
1693 --
1694 check_user_status( p_rec.user_status);
1695 --
1696 End if;
1697 --
1698 If l_success_criteria_changed Then
1699 --
1700 check_success_criteria( p_rec.success_criteria);
1701 --
1702 End if;
1703 --
1704 If l_start_date_changed Or
1705 l_end_date_changed Then
1706 --
1707 check_start_end_dates( p_rec.start_date
1708 , p_rec.end_date );
1709 --
1710 check_dates_update_ple( p_rec.activity_version_id
1711 , p_rec.start_date
1712 , p_rec.end_date );
1713 --
1714 /*
1715 ota_tav_api_business_rules.check_dates_update_rud( p_rec.activity_version_id
1716 , p_rec.start_date
1717 , p_rec.end_date
1718 , ota_tav_shd.g_old_rec.start_date
1719 , ota_tav_shd.g_old_rec.end_date
1720 );
1721 */
1722 check_dates_update_tbd( p_rec.activity_version_id
1723 , p_rec.start_date
1724 , p_rec.end_date );
1725 --
1726 check_dates_update_evt( p_rec.activity_version_id
1727 , p_rec.start_date
1728 , p_rec.end_date );
1729 --
1730 check_multiple_con_version( p_rec.activity_id
1731 , p_rec.activity_version_id
1732 , p_rec.start_date
1733 , p_rec.end_date);
1734 /* bug 3795299
1735 -- Added by dbatra for Training plan component
1736
1737 check_dates_update_tpm
1738 (p_rec.activity_version_id
1739 ,p_rec.start_date
1740 ,p_rec.end_date);
1741 */
1742 -- Added for Learning Paths
1743 check_course_lp_dates( p_rec.activity_version_id
1744 ,p_rec.start_date
1745 ,p_rec.end_date);
1746 --
1747
1748 -- Added for certification
1749 check_course_crt_dates( p_rec.activity_version_id
1750 ,p_rec.start_date
1751 ,p_rec.end_date);
1752 End if;
1753 --
1754 ota_tav_api_business_rules.check_category_dates
1755 (
1756 p_activity_version_id => p_rec.activity_version_id
1757 ,p_start_date => p_rec.start_date
1758 ,p_end_date => p_rec.end_date
1759 );
1760 --
1761 If l_duration_units_changed Then
1762 --
1763 check_duration_units( p_rec.duration_units);
1764 --
1765 End if;
1766 --
1767 If l_duration_changed Then
1768 --
1769 check_duration( p_rec.duration);
1770 --
1771 End if;
1772
1773 If l_duration_units_changed or l_duration_changed Then
1774 check_dur_unit_comb( p_rec.duration,p_rec.duration_units);
1775 end if;
1776 --
1777 If l_language_id_changed Then
1778 --
1779 check_language( p_rec.language_id);
1780 --
1781 End if;
1782 --
1783 If l_person_id_changed Then
1784 --
1785 --
1786 -- **** Bug #2154926 changed call to check_controlling_person to use
1787 -- **** p_rec.end_date rather than p_rec.start_date
1788 --
1789 check_controlling_person( p_rec.controlling_person_id, p_rec.end_date);
1790 --
1791 End if;
1792 --
1793 if l_vendor_id_changed then
1794 ota_tav_api_business_rules.check_vendor (p_rec.vendor_id);
1795 end if;
1796 --
1797 if l_budget_currency_code_changed then
1798 ota_tav_api_business_rules.check_currency(p_rec.budget_currency_code);
1799 end if;
1800 --
1801 if l_budget_currency_code_changed or
1802 l_budget_cost_changed or
1803 l_actual_cost_changed then
1804 ota_tav_api_business_rules.check_cost_vals
1805 (p_rec.budget_currency_code
1806 ,p_rec.budget_cost
1807 ,p_rec.actual_cost);
1808 end if;
1809 --
1810 if l_professional_ctype_changed or
1811 l_professional_credits_changed then
1812 ota_tav_api_business_rules.check_professional_credit_vals
1813 (p_rec.professional_credit_type
1814 ,p_rec.professional_credits);
1815 end if;
1816 --
1817 if l_professional_ctype_changed then
1818 ota_tav_api_business_rules.check_professional_credit_type
1819 (p_rec.professional_credit_type);
1820 end if;
1821 --
1822 if l_inventory_item_id_changed then
1823 ota_tav_api_business_rules.check_Inventory_item_id(
1824 p_rec.activity_version_id ,
1825 p_rec.inventory_item_id ,
1826 p_rec.organization_id);
1827
1828
1829 ota_tav_api_business_rules.check_oe_lines_exist(
1830 p_rec.activity_version_id ,
1831 p_rec.inventory_item_id ,
1832 p_rec.organization_id);
1833
1834 end if;
1835 /* For ILearning */
1836 if l_rco_id_changed then
1837 ota_tav_api_business_rules.check_unique_rco_id(
1838 p_rec.activity_version_id ,
1839 p_rec.rco_id );
1840
1841 end if;
1842
1843 ota_tav_bus.chk_ddf(p_rec);
1844 hr_utility.set_location(' Leaving:'||l_proc, 10);
1845 End update_validate;
1846 --
1847 -- ----------------------------------------------------------------------------
1848 -- |---------------------------< delete_validate >----------------------------|
1849 -- ----------------------------------------------------------------------------
1850 Procedure delete_validate(p_rec in ota_tav_shd.g_rec_type) is
1851 --
1852 l_proc varchar2(72) := g_package||'delete_validate';
1853 --
1854 Begin
1855 hr_utility.set_location('Entering:'||l_proc, 5);
1856 --
1857 -- Call all supporting business operations
1858 --
1859 check_if_tpm_exists( p_rec.activity_version_id );
1860 --
1861 check_if_evt_exists( p_rec.activity_version_id );
1862 --
1863 check_if_tbd_exists( p_rec.activity_version_id );
1864 --
1865 check_if_ple_exists( p_rec.activity_version_id );
1866 --
1867 check_if_tav_exists( p_rec.activity_version_id );
1868 --
1869 check_if_off_exists( p_rec.activity_version_id );
1870
1871 check_if_noth_exists( p_rec.activity_version_id );
1872
1873 check_if_crt_exists(p_rec.activity_version_id);
1874 --
1875 hr_utility.set_location(' Leaving:'||l_proc, 10);
1876 End delete_validate;
1877 --
1878 --
1879 -- ----------------------------------------------------------------------------
1880 -- |-----------------------< return_legislation_code >-------------------------|
1881 -- ----------------------------------------------------------------------------
1882 --
1883 -- This function will be used by the user hooks. Currently this will be used
1884 -- hr_competence_element_api business processes and in future will be made use
1885 -- of by the user hooks of activity_versions business process.
1886 --
1887 Function return_legislation_code
1888 ( p_activity_version_id in number
1889 ) return varchar2 is
1890 --
1891 -- Declare cursor
1892 --
1893 cursor csr_leg_code is
1894 select legislation_code
1895 from per_business_groups pbg,
1896 ota_activity_versions oav,
1897 ota_activity_definitions oad
1898 where pbg.business_group_id = oad.business_group_id
1899 and oad.activity_id = oav.activity_id
1900 and oav.activity_version_id = p_activity_version_id;
1901
1902 l_proc varchar2(72) := g_package||'return_legislation_code';
1903 l_legislation_code varchar2(150);
1904 --
1905 Begin
1906 hr_utility.set_location('Entering:'||l_proc, 5);
1907 --
1908 -- Ensure that all the mandatory parameters are not null
1909 --
1910 hr_api.mandatory_arg_error (p_api_name => l_proc,
1911 p_argument => 'activity_version_id',
1912 p_argument_value => p_activity_version_id );
1913 open csr_leg_code;
1914 fetch csr_leg_code into l_legislation_code;
1915 if csr_leg_code%notfound then
1916 close csr_leg_code;
1917 --
1918 -- The primary key is invalid therefore we must error out
1919 --
1920 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1921 hr_utility.raise_error;
1922 end if;
1923 --
1924 close csr_leg_code;
1925 return l_legislation_code;
1926 --
1927 hr_utility.set_location(' Leaving:'||l_proc, 10);
1928 --
1929 End return_legislation_code;
1930
1931 -- ----------------------------------------------------------------------------
1932 -- |-------------------------< check_if_noth_exists >--------------------------|
1933 -- ----------------------------------------------------------------------------
1934 --
1935 -- PUBLIC
1936 -- Description:
1937 -- Delete Validation.
1938 -- This activity version may not be deleted if child rows in
1939 -- ota_notrng_histories exists where this activity version.
1940 --
1941 Procedure check_if_noth_exists
1942 (
1943 p_activity_version_id in number
1944 )
1945 is
1946 --
1947 v_proc varchar2(72) := g_package||'check_if_noth_exists';
1948 --
1949 Begin
1950 --
1951 hr_utility.set_location('Entering:'|| v_proc, 5);
1952 --
1953 ota_tav_api_business_rules.check_if_noth_exists( p_activity_version_id );
1954 --
1955 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1956 --
1957 exception
1958 when app_exception.application_exception then
1959 if hr_multi_message.exception_add
1960 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.ACTIVITY_VERSION_ID'
1961 ) then
1962 hr_utility.set_location(' Leaving:'|| v_proc,70);
1963 raise;
1964 end if;
1965 hr_utility.set_location(' Leaving:'|| v_proc,80);
1966 end check_if_noth_exists;
1967
1968 -- ----------------------------------------------------------------------------
1969 -- |-------------------------< check_if_crt_exists >--------------------------|
1970 -- ----------------------------------------------------------------------------
1971 --
1972 -- PUBLIC
1973 -- Description:
1974 -- Delete Validation.
1975 -- This activity version may not be deleted if child rows in
1976 -- ota_certification_members exists where this activity version.
1977 --
1978 Procedure check_if_crt_exists
1979 (
1980 p_activity_version_id in number
1981 )
1982 is
1983 --
1984 v_proc varchar2(72) := g_package||'check_if_crt_exists';
1985 --
1986 Begin
1987 --
1988 hr_utility.set_location('Entering:'|| v_proc, 5);
1989 --
1990 ota_tav_api_business_rules.check_if_crt_exists( p_activity_version_id );
1991 --
1992 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1993 --
1994 exception
1995 when app_exception.application_exception then
1996 if hr_multi_message.exception_add
1997 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.ACTIVITY_VERSION_ID'
1998 ) then
1999 hr_utility.set_location(' Leaving:'|| v_proc,70);
2000 raise;
2001 end if;
2002 hr_utility.set_location(' Leaving:'|| v_proc,80);
2003 end check_if_crt_exists;
2004 --
2005 end ota_tav_bus;