[Home] [Help]
PACKAGE BODY: APPS.OTA_TAV_API_BUSINESS_RULES
Source
1 PACKAGE BODY ota_tav_api_business_rules as
2 /* $Header: ottav02t.pkb 120.5 2011/04/07 07:02:19 shwnayak ship $ */
3 --
4 --
5 -- Global package name
6 --
7 g_package varchar2(33) := ' ota_tav_api.';
8 --
9 -- Global api dml status
10 --
11 g_api_dml boolean;
12 --
13 --
14 --
15 ------------------------------------------------------------------------------
16 -- |-------------------------<set_globals>-----------------------------------|
17 ------------------------------------------------------------------------------
18 --Description:
19 -- Sets the global variablesstart date and end date
20 --
21 Procedure set_globals
22 (
23 start_date date
24 ,end_date date
25 ) is
26 --
27 v_proc varchar2(72) := g_package||'set_globals';
28 --
29 Begin
30 --
31 hr_utility.set_location('Entering:'|| v_proc, 5);
32 --
33 --
34 g_version_start_date := start_date;
35 g_version_end_date := end_date;
36 --
37 hr_utility.set_location('Leaving:'|| v_proc, 10);
38 --
39 End set_globals;
40 --
41 -- ----------------------------------------------------------------------------
42 -- |--------------------------< call_error_message >--------------------------|
43 -- ----------------------------------------------------------------------------
44 --
45 -- Description:
46 -- Passes the error information to the procedure set_message of package
47 -- hr_utility.
48 --
49 Procedure call_error_message
50 (
51 p_error_appl varchar2
52 ,p_error_txt varchar2
53 ) is
54 --
55 v_proc varchar2(72) := g_package||'call_error_message';
56 --
57 Begin
58 --
59 hr_utility.set_location('Entering:'|| v_proc, 5);
60 --
61 -- ** TEMP ** Add error message with the following text.
62 --
63 fnd_message.set_name ( p_error_appl ,p_error_txt);
64 fnd_message.raise_error;
65 --
66 hr_utility.set_location(' Leaving:'|| v_proc, 10);
67 --
68 End call_error_message;
69 --
70 -- ----------------------------------------------------------------------------
71 -- |------------------------< call_warning_message >--------------------------|
72 -- ----------------------------------------------------------------------------
73 --
74 -- Description:
75 -- Passes the error information to the procedure set_message of package
76 -- hr_utility.
77 --
78 Procedure call_warning_message
79 (
80 p_warning_txt varchar2
81 ) is
82 --
83 v_proc varchar2(72) := g_package||'call_warning_message';
84 --
85 Begin
86 --
87 hr_utility.set_location('Entering:'|| v_proc, 5);
88 --
89 -- ** TEMP ** Add warning message with the following text.
90 --
91 -- fnd_message.set_name( 'OTA', OTA_TAV_<name>');
92 fnd_message.set_name( 'OTA', p_warning_txt);
93 fnd_message.raise_error;
94 --
95 hr_utility.set_location(' Leaving:'|| v_proc, 10);
96 --
97 End call_warning_message;
98 --
99 -- ----------------------------------------------------------------------------
100 -- |---------------------< check_currency >----------------------------------|
101 -- ----------------------------------------------------------------------------
102 --
103 procedure check_currency (p_currency_code in varchar2) is
104 --
105 v_proc varchar2(72) := g_package||'check_currency';
106 begin
107 --
108 hr_utility.set_location('Entering:'|| v_proc, 5);
109 --
110 ota_general.check_currency_is_valid
111 (p_currency_code);
112 --
113 hr_utility.set_location(' Leaving:'|| v_proc, 10);
114 --
115 exception
116 when app_exception.application_exception then
117 if hr_multi_message.exception_add
118 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.CURRENCY_CODE'
119 ) then
120 hr_utility.set_location(' Leaving:'|| v_proc,70);
121 raise;
122 end if;
123 hr_utility.set_location(' Leaving:'|| v_proc,80);
124 end check_currency;
125 --
126 -- ----------------------------------------------------------------------------
127 -- |---------------------< check_vendor >----------------------------------|
128 -- ----------------------------------------------------------------------------
129 --
130 procedure check_vendor (p_vendor_id in number) is
131 --
132 v_proc varchar2(72) := g_package||'check_vendor';
133 begin
134 --
135 hr_utility.set_location('Entering:'|| v_proc, 5);
136 --
137 ota_general.check_vendor_is_valid(p_vendor_id);
138 --
139 hr_utility.set_location(' Leaving:'|| v_proc, 10);
140 --
141 exception
142 when app_exception.application_exception then
143 if hr_multi_message.exception_add
144 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.VENDOR_ID'
145 ) then
146 hr_utility.set_location(' Leaving:'|| v_proc,70);
147 raise;
148 end if;
149 hr_utility.set_location(' Leaving:'|| v_proc,80);
150 end check_vendor;
151 --
152 -- ----------------------------------------------------------------------------
153 -- |---------------------< check_cost_vals >----------------------------------|
154 -- ----------------------------------------------------------------------------
155 --
156 procedure check_cost_vals
157 (p_budget_currency_code in varchar2
158 ,p_budget_cost in number
159 ,p_actual_cost in number) is
160 --
161 v_proc varchar2(72) := g_package||'check_cost_vals';
162 begin
163 --
164 hr_utility.set_location('Entering:'|| v_proc, 5);
165 --
166 if (p_budget_cost is not null or p_actual_cost is not null) and
167 p_budget_currency_code is null then
168 --
169 fnd_message.set_name('OTA','OTA_13394_TAV_COST_ATTR');
170 fnd_message.raise_error;
171 --
172 end if;
173 --
174 hr_utility.set_location(' Leaving:'|| v_proc, 10);
175 --
176 exception
177 when app_exception.application_exception then
178 if hr_multi_message.exception_add
179 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.CURRENCY_CODE'
180 ,p_associated_column2 => 'OTA_ACTIVITY_VERSIONS.BUDGET_COST'
181 ,p_associated_column3 => 'OTA_ACTIVITY_VERSIONS.ACTUAL_COST'
182 ) then
183 hr_utility.set_location(' Leaving:'|| v_proc,70);
184 raise;
185 end if;
186 hr_utility.set_location(' Leaving:'|| v_proc,80);
187 end check_cost_vals;
188 --
189 -- ----------------------------------------------------------------------------
190 -- |---------------------< check_professional_credit_vals >-------------------|
191 -- ----------------------------------------------------------------------------
192 --
193 procedure check_professional_credit_vals
194 (p_professional_credit_type in varchar2
195 ,p_professional_credits in number) is
196 --
197 v_proc varchar2(72) := g_package||'check_professional_credit_vals';
198 begin
199 --
200 hr_utility.set_location('Entering:'|| v_proc, 5);
201 --
202 if (p_professional_credit_type is null and
203 p_professional_credits is not null)
204 or (p_professional_credit_type is not null and
205 p_professional_credits is null) then
206 --
207 fnd_message.set_name('OTA','OTA_13422_TAV_PROF_CREDIT_VALS');
208 fnd_message.raise_error;
209 --
210 end if;
211 --
212 hr_utility.set_location(' Leaving:'|| v_proc, 10);
213 --
214 exception
215 when app_exception.application_exception then
216 if hr_multi_message.exception_add
217 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.PROFESSIONAL_CREDITS'
218 ,p_associated_column2 => 'OTA_ACTIVITY_VERSIONS.PROFESSIONAL_CREDIT_TYPE'
219 ) then
220 hr_utility.set_location(' Leaving:'|| v_proc,70);
221 raise;
222 end if;
223 hr_utility.set_location(' Leaving:'|| v_proc,80);
224 end check_professional_credit_vals;
225 --
226 -- ----------------------------------------------------------------------------
227 -- |---------------------< check_professional_credit_type >-------------------|
228 -- ----------------------------------------------------------------------------
229 --
230 procedure check_professional_credit_type
231 (p_professional_credit_type in varchar2) is
232 --
233 v_proc varchar2(72) := g_package||'check_professional_credit_type';
234 begin
235 --
236 hr_utility.set_location('Entering:'|| v_proc, 5);
237 --
238 ota_general.check_domain_value
239 ( 'PROFESSIONAL_CREDIT_TYPE', p_professional_credit_type);
240 --
241 hr_utility.set_location(' Leaving:'|| v_proc, 10);
242 --
243 exception
244 when app_exception.application_exception then
245 if hr_multi_message.exception_add
246 (p_associated_column2 => 'OTA_ACTIVITY_VERSIONS.PROFESSIONAL_CREDIT_TYPE'
247 ) then
248 hr_utility.set_location(' Leaving:'|| v_proc,70);
249 raise;
250 end if;
251 hr_utility.set_location(' Leaving:'|| v_proc,80);
252 end check_professional_credit_type;
253 --
254 -- ----------------------------------------------------------------------------
255 -- |-------------------------< check_min_max_values >-------------------------|
256 -- ----------------------------------------------------------------------------
257 --
258 -- PUBLIC
259 -- Description:
260 -- The minimum attendees must be less then or equal to the maximum attendees.
261 --
262 Procedure check_min_max_values
263 (
264 p_min in number
265 ,p_max in number
266 ) Is
267 --
268 v_proc varchar2(72) := g_package||'check_min_max_values';
269 --
270 Begin
271 --
272 hr_utility.set_location('Entering:'||v_proc, 5);
273 --
274 If p_min is not null AND p_max is not null then
275 --
276 If p_min > p_max Then
277 --
278 -- ** TEMP ** Add error message with the following text.
279 --
280 call_error_message( p_error_appl => 'OTA'
281 , p_error_txt => 'OTA_13298_GEN_MINMAX_ORDER'
282 );
283 --
284 End If;
285 --
286 End If;
287 --
288 hr_utility.set_location(' Leaving:'||v_proc, 10);
289 --
290 End check_min_max_values;
291 --
292 -- ----------------------------------------------------------------------------
293 -- |--------------------------< check_unique_name >---------------------------|
294 -- ----------------------------------------------------------------------------
295 --
296 -- PUBLIC
297 -- Description:
298 -- Validates the unique key.
299 --
300 Procedure check_unique_name
301 (
302 p_business_group_id in number
303 ,p_activity_id in number
304 ,p_version_name in varchar2
305 ,p_activity_version_id in number
306 ) is
307 --
308 v_exists varchar2(1);
309 v_proc varchar2(72) := g_package||'check_unique_name';
310 --
311 v_business_group_id number;
312 --
313 cursor get_activity is
314 select business_group_id
315 from ota_activity_definitions tad
316 where activity_id = p_activity_id;
317 --
318 cursor sel_unique_key is
319 select 'Y'
320 from ota_activity_versions_vl tav
321 , ota_activity_definitions tad
322 where tav.version_name = p_version_name
323 and tav.activity_id = tad.activity_id
324 and (p_activity_version_id is null or
325 (p_activity_version_id is not null and
326 tav.activity_version_id <> p_activity_version_id))
327 and tad.business_group_id+0 = v_business_group_id;
328
329 --
330 Begin
331 --
332 hr_utility.set_location('Entering:'|| v_proc, 5);
333 --
334 if p_business_group_id is null then
335 open get_activity;
336 fetch get_activity into v_business_group_id;
337 close get_activity;
338 else
339 v_business_group_id := p_business_group_id;
340 end if;
341 --
342 Open sel_unique_key;
343 fetch sel_unique_key into v_exists;
344 --
345 if sel_unique_key%found then
346 --
347 close sel_unique_key;
348 --
349 -- ** TEMP ** Add error message with the following text.
350 --
351 call_error_message( p_error_appl => 'OTA'
352 , p_error_txt => 'OTA_13301_TAV_DUPLICATE'
353 );
354 --
355 end if;
356 --
357 close sel_unique_key;
358 --
359 hr_utility.set_location(' Leaving:'|| v_proc, 10);
360 --
361 End check_unique_name;
362 --
363 -- ----------------------------------------------------------------------------
364 -- |---------------------< check_superseding_version >------------------------|
365 -- ----------------------------------------------------------------------------
366 --
367 -- PUBLIC
368 -- Description:
369 -- An activity version may not be superseded by a version whose end_date
370 -- is less then or equal to its own. The superseding activity version must
371 -- have an end date greater than the end date of the activity it supersedes.
372 --
373 Procedure check_superseding_version
374 (
375 p_sup_act_vers_id in number
376 ,p_end_date in date
377 ) is
378 --
379 v_sup_end_date date;
380 v_proc varchar2(72) := g_package||'check_superseding_version';
381 --
382 cursor sel_version is
383 select end_date
384 from ota_activity_versions tav
385 where tav.activity_version_id = p_sup_act_vers_id;
386 --
387 Begin
388 --
389 hr_utility.set_location('Entering:'|| v_proc, 5);
390 --
391 If p_sup_act_vers_id is not null Then
392 --
393 Open sel_version;
394 Fetch sel_version into v_sup_end_date;
395 --
396 If sel_version%notfound then
397 --
398 Close sel_version;
399 --
400 -- ** TEMP ** Add error message with the following text.
401 --
402 call_error_message( p_error_appl => 'OTA'
403 , p_error_txt => 'OTA_13293_TAV_NO_SUP'
404 );
405 --
406 Elsif nvl( v_sup_end_date, hr_api.g_eot)
407 < nvl( p_end_date, hr_general.end_of_time) Then
408 --
409 Close sel_version;
410 --
411 -- ** TEMP ** Add error message with the following text.
412 --
413 call_error_message( p_error_appl => 'OTA'
414 , p_error_txt => 'OTA_13302_TAV_SUP_DATE'
415 );
416 --
417 End if;
418 --
419 Close sel_version;
420 --
421 End if;
422 --
423 hr_utility.set_location(' Leaving:'|| v_proc, 10);
424 --
425 End check_superseding_version;
426 --
427 -- ---------------------------------------------------------------------------
428 -- |-------------------------< find_overlapping_versions >-------------------|
429 -- ---------------------------------------------------------------------------
430 --
431 -- PUBLIC
432 -- Description:
433 -- Checks to see if an Activity has overlapping versions. If a version has a
434 -- start date between another version's start date and end date then
435 -- overlapping versions exist.
436 --
437 Procedure find_overlapping_versions
438 (
439 p_activity_id in number
440 ) is
441 --
442 overlapping_vers number;
443 v_proc varchar2(72) := g_package||'find_overlapping_versions';
444 --
445 cursor find_overlapping_vers is
446 select 1
447 from ota_activity_versions tav1
448 where tav1.activity_id = p_activity_id
449 and exists
450 ( select 1
451 from ota_activity_versions tav2
452 where tav2.activity_id = p_activity_id
453 and tav1.activity_version_id <> tav2.activity_version_id
454 and tav2.start_date between
455 tav1.start_date and nvl(tav1.end_date, hr_general.end_of_time)
456 );
457
458 Begin
459 --
460 hr_utility.set_location('Entering:'|| v_proc, 5);
461 --
462 Open find_overlapping_vers;
463 Fetch find_overlapping_vers into overlapping_vers;
464 --
465 If find_overlapping_vers%found then
466 --
467 Close find_overlapping_vers;
468 --
469 -- ** TEMP ** Add error message with the following text.
470 --
471 call_error_message ( p_error_appl => 'OTA'
472 , p_error_txt => 'OTA_13469_TAV_OVERLAPS '
473 );
474 --
475 End if;
476 Close find_overlapping_vers;
477 --
478 hr_utility.set_location('Leaving:'|| v_proc, 10);
479 --
480 End find_overlapping_versions;
481 --
482 -- ---------------------------------------------------------------------------
483 -- |--------------------------< check_user_status >---------------------------|
484 -- ----------------------------------------------------------------------------
485 --
486 -- PUBLIC
487 -- Description:
488 -- The user status must be in the domain 'Activity User Status'.
489 --
490 Procedure check_user_status
491 (
492 p_user_status in varchar2
493 ) is
494 --
495 v_proc varchar2(72) := g_package||'check_user_status';
496 --
497 Begin
498 --
499 hr_utility.set_location('Entering:'|| v_proc, 5);
500 --
501 If p_user_status is not null Then
502 --
503 ota_general.check_domain_value( 'ACTIVITY_USER_STATUS', p_user_status);
504 --
505 End if;
506 --
507 hr_utility.set_location(' Leaving:'|| v_proc, 10);
508 --
509 End check_user_status;
510 --
511 -- ----------------------------------------------------------------------------
512 -- |-------------------------< check_success_criteria >-----------------------|
513 -- ----------------------------------------------------------------------------
514 --
515 -- PUBLIC
516 -- Description:
517 -- The success criteria must be in the domain 'Activity Success Criteria'.
518 --
519 Procedure check_success_criteria
520 (
521 p_succ_criteria in varchar2
522 ) is
523 --
524 v_proc varchar2(72) := g_package||'check_success_criteria';
525 --
526 Begin
527 --
528 hr_utility.set_location('Entering:'|| v_proc, 5);
529 --
530 IF p_succ_criteria is not null Then
531 --
532 ota_general.check_domain_value('ACTIVITY_SUCCESS_CRITERIA',p_succ_criteria);
533 --
534 End if;
535 --
536 hr_utility.set_location(' Leaving:'|| v_proc, 10);
537 --
538 End check_success_criteria;
539 --
540 -- ----------------------------------------------------------------------------
541 -- |----------------------< get_activity_version_id >-------------------------|
542 -- ----------------------------------------------------------------------------
543 --
544 -- PUBLIC
545 -- Description:
546 -- Return the surrogate key from a passed parameter
547 --
548 Function get_activity_version_id
549 (
550 p_activity_id in number
551 ,p_version_name in varchar2
552 )
553 Return number is
554 --
555 v_activity_version_id ota_activity_versions.activity_version_id%TYPE ;
556 v_proc varchar2(72) := g_package||'get_activity_version_id';
557 --
558 cursor sel_version_id is
559 select activity_version_id
560 from ota_activity_versions_vl tav
561 where tav.activity_id = p_activity_id
562 and tav.version_name = p_version_name;
563
564 --
565 Begin
566 --
567 hr_utility.set_location('Entering:'|| v_proc, 5);
568 --
569 If p_activity_id is not null AND p_version_name is not null Then
570 --
571 Open sel_version_id;
572 fetch sel_version_id into v_activity_version_id;
573 --
574 If sel_version_id%notfound then
575 --
576 close sel_version_id;
577 --
578 -- ** TEMP ** Add error message with the following text.
579 --
580 call_error_message( p_error_appl => 'OTA'
581 , p_error_txt => 'OTA_13303_TAV_NOT_EXISTS'
582 );
583 --
584 End if;
585 --
586 close sel_version_id;
587 --
588 return( v_activity_version_id);
589 --
590 Else
591 --
592 -- ** TEMP ** Add error message with the following text.
593 --
594 call_error_message( p_error_appl => 'OTA'
595 , p_error_txt => 'OTA_13303_TAV_NOT_EXISTS'
596 );
597 --
598 End if;
599 --
600 hr_utility.set_location(' Leaving:'|| v_proc, 10);
601 --
602 End get_activity_version_id;
603 --
604 -- ----------------------------------------------------------------------------
605 -- |----------------------< get_activity_version_name >-----------------------|
606 -- ----------------------------------------------------------------------------
607 --
608 -- PUBLIC
609 -- Description:
610 -- Return the activity version name.
611 --
612 Function get_activity_version_name
613 (
614 p_activity_version_id in number
615 ) Return varchar2 is
616 --
617 v_version_name ota_activity_versions.version_name%TYPE;
618 v_proc varchar2(72) := g_package||'get_activity_version_name';
619 --
620 cursor sel_vers_name is
621 select version_name
622 from ota_activity_versions_tl tav
623 where tav.activity_version_id = p_activity_version_id
624 and tav.language = USERENV('LANG');
625 --
626 Begin
627 --
628 If p_activity_version_id is not null Then
629 --
630 Open sel_vers_name;
631 fetch sel_vers_name into v_version_name;
632 --
633 If sel_vers_name%notfound then
634 --
635 v_version_name := null;
636 --
637 End if;
638 --
639 close sel_vers_name;
640 --
641 Return( v_version_name);
642 --
643 End if;
644 --
645 Return( null);
646 --
647 End get_activity_version_name;
648 --
649 -- ----------------------------------------------------------------------------
650 -- |--------------------------< check_start_end_dates >-----------------------|
651 -- ----------------------------------------------------------------------------
652 --
653 -- PUBLIC
654 -- Description:
655 -- Validates the startdate and enddate.
656 -- Startdate must be less than, or equal to, enddate.
657 --
658 Procedure check_start_end_dates
659 (
660 p_start_date in date
661 ,p_end_date in date
662 ) is
663 --
664 v_proc varchar2(72) := g_package||'check_start_end_dates';
665 --
666 Begin
667 --
668 hr_utility.set_location('Entering:'|| v_proc, 5);
669 --
670 ota_general.check_start_end_dates( p_start_date, p_end_date);
671 --
672 hr_utility.set_location(' Leaving:'|| v_proc, 10);
673 --
674 End check_start_end_dates;
675 --
676 -- ----------------------------------------------------------------------------
677 -- |-------------------------< check_dates_update_rud >-----------------------|
678 -- ----------------------------------------------------------------------------
679 --
680 -- PUBLIC
681 -- Description:
682 -- Validates the startdate and enddate.
683 -- Update of start and end dates must not invalidate valid resoruce usages
684 -- for this activity version.
685 --
686 Procedure check_dates_update_rud
687 (
688 p_activity_version_id in number
689 ,p_start_date in date
690 ,p_end_date in date
691 ,p_old_start_date in date
692 ,p_old_end_date in date
693 ) is
694 --
695 v_start_date date;
696 v_end_date date;
697 v_proc varchar2(72) := g_package||'check_dates_update_rud';
698 --
699 cursor sel_check_dates is
700 select start_date
701 , end_date
702 from ota_resource_usages rud
703 where rud.activity_version_id = p_activity_version_id;
704 --
705 Begin
706 if hr_multi_message.no_error_message
707 (p_check_message_name1 => 'OTA_13312_GEN_DATE_ORDER'
708 ) then
709 --
710 hr_utility.set_location('Entering:'|| v_proc, 5);
711 --
712 Open sel_check_dates;
713 Fetch sel_check_dates into v_start_date
714 , v_end_date;
715 --
716 Loop
717 --
718 Exit When sel_check_dates%notfound OR sel_check_dates%notfound is null;
719 --
720 -- If the resource usage end/start date has not changed then
721 -- set it's value to the activity version end/start date.
722 --
723 If not ota_general.value_changed(p_old_end_date,v_end_date) Then
724 --
725 v_end_date:=p_end_date;
726 --
727 End if;
728 --
729 If not ota_general.value_changed(p_old_start_date,v_start_date) Then
730 --
731 v_start_date:=p_start_date;
732 --
733 End if;
734 --
735 If ota_general.check_par_child_dates_fun( p_start_date
736 , p_end_date
737 , v_start_date
738 , v_end_date ) then
739 --
740 call_error_message( p_error_appl => 'OTA'
741 , p_error_txt => 'OTA_13560_TAV_RUD_DATES'
742 );
743 --
744 End if;
745 --
746 Fetch sel_check_dates into v_start_date
747 , v_end_date;
748 End loop;
749 --
750 Close sel_check_dates;
751 --
752 hr_utility.set_location(' Leaving:'|| v_proc, 10);
753 --
754 end if;
755 exception
756 when app_exception.application_exception then
757 if hr_multi_message.exception_add
758 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.START_DATE'
759 ,p_associated_column2 => 'OTA_ACTIVITY_VERSIONS.END_DATE'
760 ) then
761 hr_utility.set_location(' Leaving:'|| v_proc,70);
762 raise;
763 end if;
764 hr_utility.set_location(' Leaving:'|| v_proc,80);
765 End check_dates_update_rud;
766 --
767 -- ----------------------------------------------------------------------------
768 -- |-------------------------< check_dates_update_ple >-----------------------|
769 -- ----------------------------------------------------------------------------
770 --
771 -- PUBLIC
772 -- Description:
773 -- Validates the startdate and enddate.
774 -- Update of start and end dates must not invalidate price list entry
775 -- for this activity version.
776 --
777 Procedure check_dates_update_ple
778 (
779 p_activity_version_id in number
780 ,p_start_date in date
781 ,p_end_date in date
782 ) is
783 --
784 v_start_date date;
785 v_end_date date;
786 v_proc varchar2(72) := g_package||'check_dates_update_ple';
787 --
788 cursor sel_check_dates is
789 select start_date
790 , end_date
791 from ota_price_list_entries ple
792 where ple.activity_version_id = p_activity_version_id;
793 --
794 Begin
795 --
796 hr_utility.set_location('Entering:'|| v_proc, 5);
797 --
798 Open sel_check_dates;
799 Fetch sel_check_dates into v_start_date
800 , v_end_date;
801 --
802 Loop
803 --
804 Exit When sel_check_dates%notfound OR sel_check_dates%notfound is null;
805 --
806 If ota_general.check_par_child_dates_fun( p_start_date
807 , p_end_date
808 , v_start_date
809 , v_end_date ) then
810 --
811 call_error_message( p_error_appl => 'OTA'
812 , p_error_txt => 'OTA_13561_TAV_PLE_DATES'
813 );
814 --
815 End if;
816 --
817 Fetch sel_check_dates into v_start_date
818 , v_end_date;
819 End loop;
820 --
821 Close sel_check_dates;
822 --
823 hr_utility.set_location(' Leaving:'|| v_proc, 10);
824 --
825 End check_dates_update_ple;
826 --
827 -- ----------------------------------------------------------------------------
828 -- |-------------------------< check_dates_update_tbd >-----------------------|
829 -- ----------------------------------------------------------------------------
830 --
831 -- PUBLIC
832 -- Description:
833 -- Validates the startdate and enddate.
834 -- Update of start and end dates must not invalidate booking deals
835 -- for this activity version.
836 --
837 Procedure check_dates_update_tbd
838 (
839 p_activity_version_id in number
840 ,p_start_date in date
841 ,p_end_date in date
842 ) is
843 --
844 v_start_date date;
845 v_end_date date;
846 v_proc varchar2(72) := g_package||'check_dates_update_tbd';
847 --
848 cursor sel_check_dates is
849 select start_date
850 , end_date
851 from ota_booking_deals tbd
852 where tbd.activity_version_id = p_activity_version_id;
853 --
854 Begin
855 --
856 hr_utility.set_location('Entering:'|| v_proc, 5);
857 --
858 Open sel_check_dates;
859 Fetch sel_check_dates into v_start_date
860 , v_end_date;
861 --
862 Loop
863 --
864 Exit When sel_check_dates%notfound OR sel_check_dates%notfound is null;
865 --
866 If ota_general.check_par_child_dates_fun( p_start_date
867 , p_end_date
868 , v_start_date
869 , v_end_date ) then
870 --
871 call_error_message( p_error_appl => 'OTA'
872 , p_error_txt => 'OTA_13562_TAV_TBD_DATES'
873 );
874 --
875 End if;
876 --
877 Fetch sel_check_dates into v_start_date
878 , v_end_date;
879 End loop;
880 --
881 Close sel_check_dates;
882 --
883 hr_utility.set_location(' Leaving:'|| v_proc, 10);
884 --
885 End check_dates_update_tbd;
886 --
887 -- ----------------------------------------------------------------------------
888 -- |-------------------------< check_dates_update_evt >-----------------------|
889 -- ----------------------------------------------------------------------------
890 --
891 -- PUBLIC
892 -- Description:
893 -- Validates the startdate and enddate.
894 -- Update of start and end dates must not invalidate events
895 -- for this activity version.
896 -- This requires a check to ensure that the activity version dates do not
897 -- invalidate the Event Booking DAtes or the Event Course Dates if either
898 -- have been entered.
899 --
900 Procedure check_dates_update_evt
901 (
902 p_activity_version_id in number
903 ,p_start_date in date
904 ,p_end_date in date
905 ) is
906 --
907 v_start_date date;
908 v_end_date date;
909 v_proc varchar2(72) := g_package||'check_dates_update_evt';
910 --Modified for bug 11075903
911 -- l_obj_off varchar2(30) := ota_utility.Get_lookup_meaning('OTA_CATALOG_OBJECT_TYPE','O',810);
912 --l_obj_act varchar2(30) := ota_utility.Get_lookup_meaning('OTA_CATALOG_OBJECT_TYPE','H',810);
913
914 l_obj_off hr_lookups.meaning%type;
915 l_obj_act hr_lookups.meaning%type;
916
917 --
918 cursor sel_check_dates is
919 select start_date
920 , end_date
921 from ota_offerings off -- bug 3534657
922 where off.activity_version_id = p_activity_version_id;
923 --
924 Begin
925 --
926 hr_utility.set_location('Entering:'|| v_proc, 5);
927 --
928 Open sel_check_dates;
929 Fetch sel_check_dates into v_start_date
930 , v_end_date;
931 --
932 Loop
933 --
934 Exit When sel_check_dates%notfound OR sel_check_dates%notfound is null;
935 --
936 -- Assignment if course_start_date or course_end_date is null
937 --
938 If v_start_date is null Then
939 --
940 v_start_date := p_start_date;
941 --
942 End if;
943 --
944 If v_end_date is null Then
945 --
946 v_end_date := hr_api.g_eot;
947 --
948 End if;
949 --
950 If ota_general.check_par_child_dates_fun( p_start_date
951 , p_end_date
952 , v_start_date
953 , v_end_date ) then
954 l_obj_off := ota_utility.Get_lookup_meaning('OTA_CATALOG_OBJECT_TYPE','O',810);
955 l_obj_act := ota_utility.Get_lookup_meaning('OTA_CATALOG_OBJECT_TYPE','H',810);
956 --
957 fnd_message.set_name ( 'OTA','OTA_443166_OBJ_CHILD_DATE');
958 fnd_message.set_token('OBJECT_NAME', l_obj_act );
959 fnd_message.set_token('CHILD_OBJECT', l_obj_off);
960 fnd_message.raise_error;
961 /*
962 call_error_message( p_error_appl => 'OTA'
963 , p_error_txt => 'OTA_13559_TAV_EVT_DATES'
964 );
965 */
966 --
967 End if;
968 --
969 Fetch sel_check_dates into v_start_date
970 , v_end_date;
971 End loop;
972 --
973 Close sel_check_dates;
974 --
975 hr_utility.set_location(' Leaving:'|| v_proc, 10);
976 --
977 End check_dates_update_evt;
978 --
979 -- ----------------------------------------------------------------------------
980 -- |--------------------------< check_category_dates >------------------------|
981 -- ----------------------------------------------------------------------------
982 --
983 -- PUBLIC
984 -- Description:
985 -- Validates the startdate and enddate with respect to category dates.
986 --
987 Procedure check_category_dates
988 (
989 p_activity_version_id in number
990 ,p_start_date in date
991 ,p_end_date in date
992 ) is
993 --
994 -- Declare cursors and local variables
995 --
996 -- Cursor to get value if parent category is already exits in child hierarchy of base category
997
998 CURSOR cur_cat_start_end_date is
999 select
1000 ctu.start_date_active,
1001 nvl(ctu.end_date_active, hr_api.g_eot)
1002 from
1003 ota_category_usages ctu,
1004 ota_act_cat_inclusions aci
1005 where
1006 ctu.category_usage_id = aci.category_usage_id
1007 and aci.activity_version_id = p_activity_version_id
1008 and ctu.type='C'
1009 and aci.primary_flag='Y';
1010 --
1011 -- Variables for API Boolean parameters
1012 l_proc varchar2(72) := g_package ||'check_category_dates';
1013 l_cat_start_date date;
1014 l_cat_end_date date;
1015
1016 Begin
1017 hr_utility.set_location(' Entering:' || l_proc,10);
1018 --
1019 IF hr_multi_message.no_exclusive_error
1020 (p_check_column1 => 'OTA_ACTIVITY_VERSIONS.START_DATE'
1021 ,p_check_column2 => 'OTA_ACTIVITY_VERSIONS.END_DATE'
1022 ,p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.START_DATE'
1023 ,p_associated_column2 => 'OTA_ACTIVITY_VERSIONS.END_DATE'
1024 ) THEN
1025 --
1026 OPEN cur_cat_start_end_date;
1027 FETCH cur_cat_start_end_date into l_cat_start_date, l_cat_end_date;
1028
1029 IF cur_cat_start_end_date%FOUND THEN
1030 CLOSE cur_cat_start_end_date;
1031 IF ( l_cat_start_date > p_start_date
1032 or l_cat_end_date < nvl(p_end_date, hr_api.g_eot)
1033 ) THEN
1034 --
1035 fnd_message.set_name ( 'OTA','OTA_13062_ACT_OUT_OF_CAT_DATES');
1036 fnd_message.raise_error;
1037 --
1038 End IF;
1039 ELSE
1040 CLOSE cur_cat_start_end_date;
1041 End IF;
1042 End IF;
1043 --
1044 hr_utility.set_location(' Leaving:' || l_proc,10);
1045 Exception
1046 when app_exception.application_exception then
1047 IF hr_multi_message.exception_add
1048 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.START_DATE'
1049 ,p_associated_column2 => 'OTA_ACTIVITY_VERSIONS.END_DATE'
1050 ) THEN
1051 hr_utility.set_location(' Leaving:'|| l_proc,20);
1052 raise;
1053 END IF;
1054
1055 hr_utility.set_location(' Leaving:'|| l_proc,30);
1056 --
1057 End check_category_dates;
1058 --
1059 -- ----------------------------------------------------------------------------
1060 -- |-------------------------< check_if_evt_exists >--------------------------|
1061 -- ----------------------------------------------------------------------------
1062 --
1063 -- PUBLIC
1064 -- Description:
1065 -- Delete Validation.
1066 -- This activity version may not be deleted if child rows in
1067 -- ota_events exist.
1068 --
1069 Procedure check_if_evt_exists
1070 (
1071 p_activity_version_id in number
1072 ) is
1073 --
1074 v_exists varchar2(1);
1075 v_proc varchar2(72) := g_package||'check_if_evt_exists';
1076 --
1077 cursor sel_evt_exists is
1078 select 'Y'
1079 from ota_events evt
1080 where evt.activity_version_id = p_activity_version_id;
1081 --
1082 Begin
1083 --
1084 hr_utility.set_location('Entering:'|| v_proc, 5);
1085 --
1086 Open sel_evt_exists;
1087 fetch sel_evt_exists into v_exists;
1088 --
1089 if sel_evt_exists%found then
1090 --
1091 close sel_evt_exists;
1092 --
1093 -- ** TEMP ** Add error message with the following text.
1094 --
1095 call_error_message( p_error_appl => 'OTA'
1096 , p_error_txt => 'OTA_13304_TAV_DEL_EVT_EXISTS'
1097 );
1098 --
1099 end if;
1100 --
1101 close sel_evt_exists;
1102 --
1103 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1104 --
1105 End check_if_evt_exists;
1106 --
1107 -- ----------------------------------------------------------------------------
1108 -- |-------------------------< check_if_off_exists >--------------------------|
1109 -- ----------------------------------------------------------------------------
1110 --
1111 -- PUBLIC
1112 -- Description:
1113 -- Delete Validation.
1114 -- This activity version may not be deleted if child rows in
1115 -- ota_offerings exist.
1116 --
1117 Procedure check_if_off_exists
1118 (
1119 p_activity_version_id in number
1120 ) is
1121 --
1122 v_exists varchar2(1);
1123 v_proc varchar2(72) := g_package||'check_if_off_exists';
1124 --
1125 cursor sel_off_exists is
1126 select 'Y'
1127 from ota_offerings off
1128 where off.activity_version_id = p_activity_version_id;
1129 --
1130 Begin
1131 --
1132 hr_utility.set_location('Entering:'|| v_proc, 5);
1133 --
1134 Open sel_off_exists;
1135 fetch sel_off_exists into v_exists;
1136 --
1137 if sel_off_exists%found then
1138 --
1139 close sel_off_exists;
1140 --
1141 -- ** TEMP ** Add error message with the following text.
1142 --
1143 call_error_message( p_error_appl => 'OTA'
1144 , p_error_txt => 'OTA_443400_TAV_DEL_OFF_EXISTS'
1145 );
1146 --
1147 end if;
1148 --
1149 close sel_off_exists;
1150 --
1151 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1152 --
1153 End check_if_off_exists;
1154 --
1155 -- ----------------------------------------------------------------------------
1156 -- |-------------------------< check_if_tpm_exists >--------------------------|
1157 -- ----------------------------------------------------------------------------
1158 -- PUBLIC
1159 -- Description:
1160 -- Delete Validation.
1161 -- This activity version may not be deleted if child rows in
1162 -- ota_training_plan_members exist.
1163 --
1164 Procedure check_if_tpm_exists
1165 (
1166 p_activity_version_id in number
1167 ) is
1168 --
1169 v_proc varchar2(72) := g_package||'check_if_tpm_exists';
1170 --
1171 -- dynamic sql statment to check if the activity version is referenced
1172 -- in training plan members
1173 --
1174 Cursor c_get_tpm_rows is
1175 Select 'Y'
1176 from OTA_TRAINING_PLAN_MEMBERS
1177 where activity_version_id = p_activity_version_id;
1178 --
1179 l_dyn_curs integer;
1180 l_dyn_rows integer;
1181 l_dummy varchar2(1);
1182 --
1183 Begin
1184 --
1185 hr_utility.set_location('Entering:'|| v_proc, 5);
1186 --
1187 -- Check if the activity version is referenced in training plan members table
1188 open c_get_tpm_rows;
1189 fetch c_get_tpm_rows into l_dummy;
1190 if c_get_tpm_rows%found then
1191 close c_get_tpm_rows;
1192 call_error_message( p_error_appl => 'OTA'
1193 , p_error_txt => 'OTA_13820_TAV_NO_DEL_TPM_EXIST');
1194 else
1195 close c_get_tpm_rows;
1196 end if;
1197 --
1198 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1199 --
1200 End check_if_tpm_exists;
1201 --
1202 -- ----------------------------------------------------------------------------
1203 -- |-------------------------< check_if_tbd_exists >--------------------------|
1204 -- ----------------------------------------------------------------------------
1205 --
1206 -- PUBLIC
1207 -- Description:
1208 -- Delete Validation.
1209 -- This activity version may not be deleted if child rows in
1210 -- ota_booking_deals exist.
1211 --
1212 Procedure check_if_tbd_exists
1213 (
1214 p_activity_version_id in number
1215 ) is
1216 --
1217 v_exists varchar2(1);
1218 v_proc varchar2(72) := g_package||'check_if_tbd_exists';
1219 --
1220 cursor sel_tbd_exists is
1221 select 'Y'
1222 from ota_booking_deals tbd
1223 where tbd.activity_version_id = p_activity_version_id;
1224 --
1225 Begin
1226 --
1227 hr_utility.set_location('Entering:'|| v_proc, 5);
1228 --
1229 Open sel_tbd_exists;
1230 fetch sel_tbd_exists into v_exists;
1231 --
1232 if sel_tbd_exists%found then
1233 --
1234 close sel_tbd_exists;
1235 --
1236 -- ** TEMP ** Add error message with the following text.
1237 --
1238 call_error_message( p_error_appl => 'OTA'
1239 , p_error_txt => 'OTA_13305_TAV_DEL_TBD_EXISTS'
1240 );
1241 --
1242 end if;
1243 --
1244 close sel_tbd_exists;
1245 --
1246 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1247 --
1248 End check_if_tbd_exists;
1249 --
1250 -- ----------------------------------------------------------------------------
1251 -- |-------------------------< check_if_ple_exists >--------------------------|
1252 -- ----------------------------------------------------------------------------
1253 --
1254 -- PUBLIC
1255 -- Description:
1256 -- Delete Validation.
1257 -- This activity version may not be deleted if child rows in
1258 -- ota_price_lists_entries exist.
1259 --
1260 Procedure check_if_ple_exists
1261 (
1262 p_activity_version_id in number
1263 ) is
1264 --
1265 v_exists varchar2(1);
1266 v_proc varchar2(72) := g_package||'check_if_ple_exists';
1267 --
1268 cursor sel_ple_exists is
1269 select 'Y'
1270 from ota_price_list_entries ple
1271 where ple.activity_version_id = p_activity_version_id;
1272 --
1273 Begin
1274 --
1275 hr_utility.set_location('Entering:'|| v_proc, 5);
1276 --
1277 Open sel_ple_exists;
1278 fetch sel_ple_exists into v_exists;
1279 --
1280 if sel_ple_exists%found then
1281 --
1282 close sel_ple_exists;
1283 --
1284 -- ** TEMP ** Add error message with the following text.
1285 --
1286 call_error_message( p_error_appl => 'OTA'
1287 , p_error_txt => 'OTA_13306_TAV_DEL_PLE_EXISTS'
1288 );
1289 --
1290 end if;
1291 --
1292 close sel_ple_exists;
1293 --
1294 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1295 --
1296 End check_if_ple_exists;
1297 --
1298 -- ----------------------------------------------------------------------------
1299 -- |-------------------------< check_if_comp_exists >--------------------------|
1300 -- ----------------------------------------------------------------------------
1301 --
1302 -- PUBLIC
1303 -- Description:
1304 -- Delete Validation.
1305 -- This activity version may not be deleted if child rows in
1306 -- per_competence_elements exist.
1307 --
1308 Procedure check_if_comp_exists
1309 (
1310 p_activity_version_id in number
1311 ) is
1312 --
1313 v_exists varchar2(1);
1314 v_proc varchar2(72) := g_package||'check_if_comp_exists';
1315 --
1316 cursor sel_comp_exists is
1317 select 'Y'
1318 from per_competence_elements pce
1319 where pce.activity_version_id = p_activity_version_id;
1320 --
1321 Begin
1322 --
1323 hr_utility.set_location('Entering:'|| v_proc, 5);
1324 --
1325 Open sel_comp_exists;
1326 fetch sel_comp_exists into v_exists;
1327 --
1328 if sel_comp_exists%found then
1329 --
1330 close sel_comp_exists;
1331 --
1332 -- ** TEMP ** Add error message with the following text.
1333 --
1334 call_error_message( p_error_appl => 'OTA'
1335 , p_error_txt => 'OTA_443398_TAV_DEL_COMP_EXISTS'
1336 );
1337 --
1338 end if;
1339 --
1340 close sel_comp_exists;
1341 --
1342 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1343 --
1344 End check_if_comp_exists;
1345 --
1346
1347 --
1348 -- ----------------------------------------------------------------------------
1349 -- |-------------------------< check_if_lpm_exists >--------------------------|
1350 -- ----------------------------------------------------------------------------
1351 --
1352 -- PUBLIC
1353 -- Description:
1354 -- Delete Validation.
1355 -- This activity version may not be deleted if child rows in
1356 -- ota_learning_path_members exist.
1357 --
1358 Procedure check_if_lpm_exists
1359 (
1360 p_activity_version_id in number
1361 ) is
1362 --
1363 v_exists varchar2(1);
1364 v_proc varchar2(72) := g_package||'check_if_lpm_exists';
1365 --
1366 cursor sel_lpm_exists is
1367 select 'Y'
1368 from ota_learning_path_members lpm
1369 where lpm.activity_version_id = p_activity_version_id;
1370 --
1371 Begin
1372 --
1373 hr_utility.set_location('Entering:'|| v_proc, 5);
1374 --
1375 Open sel_lpm_exists;
1376 fetch sel_lpm_exists into v_exists;
1377 --
1378 if sel_lpm_exists%found then
1379 --
1380 close sel_lpm_exists;
1381 --
1382 -- ** TEMP ** Add error message with the following text.
1383 --
1384 call_error_message( p_error_appl => 'OTA'
1385 , p_error_txt => 'OTA_443399_TAV_DEL_LPM_EXISTS'
1386 );
1387 --
1388 end if;
1389 --
1390 close sel_lpm_exists;
1391 --
1392 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1393 --
1394 End check_if_lpm_exists;
1395 --
1396
1397 -- ----------------------------------------------------------------------------
1398 -- |-------------------------< check_if_tav_exists >--------------------------|
1399 -- ----------------------------------------------------------------------------
1400 --
1401 -- PUBLIC
1402 -- Description:
1403 -- Delete Validation.
1404 -- This activity version may not be deleted if child rows in
1405 -- ota_activity_versions exists where this activity version has superseded
1406 -- another earlier activity version.
1407 --
1408 Procedure check_if_tav_exists
1409 (
1410 p_activity_version_id in number
1411 ) is
1412 --
1413 v_exists varchar2(1);
1414 v_proc varchar2(72) := g_package||'check_if_tav_exists';
1415 --
1416 cursor sel_tav_exists is
1417 select 'Y'
1418 from ota_activity_versions tav
1419 where tav.superseded_by_act_version_id = p_activity_version_id;
1420 --
1421 Begin
1422 --
1423 hr_utility.set_location('Entering:'|| v_proc, 5);
1424 --
1425 Open sel_tav_exists;
1426 fetch sel_tav_exists into v_exists;
1427 --
1428 if sel_tav_exists%found then
1429 --
1430 close sel_tav_exists;
1431 --
1432 -- ** TEMP ** Add error message with the following text.
1433 --
1434 call_error_message( p_error_appl => 'OTA'
1435 , p_error_txt => 'OTA_13307_TAV_DEL_SUP_EXISTS'
1436 );
1437 --
1438 end if;
1439 --
1440 close sel_tav_exists;
1441 --
1442 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1443 --
1444 End check_if_tav_exists;
1445 --
1446 -- ----------------------------------------------------------------------------
1447 -- |-------------------------< check_if_tsp_exists >--------------------------|
1448 -- ----------------------------------------------------------------------------
1449 --
1450 -- PUBLIC
1451 -- Description:
1452 -- Delete Validation.
1453 -- This activity version may not be deleted if child rows in
1454 -- ota_skill_provisions.
1455 --
1456 Procedure check_if_tsp_exists
1457 (
1458 p_activity_version_id in number
1459 ) is
1460 --
1461 v_exists varchar2(1);
1462 v_proc varchar2(72) := g_package||'check_if_tsp_exists';
1463 --
1464 cursor sel_tsp_exists is
1465 select 'Y'
1466 from ota_skill_provisions tsp
1467 where tsp.activity_version_id = p_activity_version_id;
1468 --
1469 Begin
1470 --
1471 hr_utility.set_location('Entering:'|| v_proc, 5);
1472 --
1473 Open sel_tsp_exists;
1474 fetch sel_tsp_exists into v_exists;
1475 --
1476 if sel_tsp_exists%found then
1477 --
1478 close sel_tsp_exists;
1479 --
1480 -- ** TEMP ** Add error message with the following text.
1481 --
1482 call_error_message( p_error_appl => 'OTA'
1483 , p_error_txt => 'OTA_443265_TAV_DEL_TSP_EXISTS'
1484 );
1485 --
1486 end if;
1487 --
1488 close sel_tsp_exists;
1489 --
1490 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1491 --
1492 End check_if_tsp_exists;
1493 --
1494
1495 -- ----------------------------------------------------------------------------
1496 -- |------------------------< check_duration_units >--------------------------|
1497 -- ----------------------------------------------------------------------------
1498 --
1499 -- PUBLIC
1500 -- Description:
1501 -- The duration units must be in the domain 'Units'.
1502 --
1503 Procedure check_duration_units
1504 (
1505 p_duration_units in varchar2
1506 ) is
1507 --
1508 v_proc varchar2(72) := g_package||'check_duration_units';
1509 --
1510 Begin
1511 --
1512 hr_utility.set_location('Entering:'|| v_proc, 5);
1513 --
1514 If p_duration_units is not null Then
1515 --
1516 ota_general.check_domain_value( 'OTA_DURATION_UNITS', p_duration_units);
1517 --
1518 End if;
1519 --
1520 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1521 --
1522 End check_duration_units;
1523 --
1524 -- ----------------------------------------------------------------------------
1525 -- |--------------------------< check_duration >------------------------------|
1526 -- ----------------------------------------------------------------------------
1527 --
1528 -- PUBLIC
1529 -- Description:
1530 -- The duration must be a positive integer greater than zero.
1531 --
1532 Procedure check_duration
1533 (
1534 p_duration in number
1535 ) is
1536 --
1537 v_proc varchar2(72) := g_package||'check_duration';
1538 --
1539 Begin
1540 --
1541 hr_utility.set_location('Entering:'|| v_proc, 5);
1542 --
1543 If p_duration <= 0 Then
1544 --
1545 -- ** TEMP ** Add error message with the following text.
1546 --
1547 call_error_message( p_error_appl => 'OTA'
1548 , p_error_txt => 'OTA_13308_TAV_DURATION_POS'
1549 );
1550 --
1551 end if;
1552 --
1553 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1554 --
1555 End check_duration;
1556 --
1557 -- ----------------------------------------------------------------------------
1558 -- |---------------------------< check_language >-----------------------------|
1559 -- ----------------------------------------------------------------------------
1560 --
1561 -- PUBLIC
1562 -- Description:
1563 -- The language must be in the domain 'Languages'.
1564 --
1565 Procedure check_language
1566 (
1567 p_language_id in number
1568 ) is
1569 v_exists varchar2(1);
1570 v_proc varchar2(72) := g_package||'check_language';
1571 --
1572 cursor sel_language is
1573 select 'Y'
1574 from fnd_languages lan
1575 where lan.language_id = p_language_id;
1576 --
1577 Begin
1578 --
1579 hr_utility.set_location('Entering:'|| v_proc, 5);
1580 --
1581 Open sel_language;
1582 fetch sel_language into v_exists;
1583 --
1584 If p_language_id is not null Then
1585 --
1586 if sel_language%notfound then
1587 --
1588 close sel_language;
1589 --
1590 -- ** TEMP ** Add error message with the following text.
1591 --
1592 call_error_message( p_error_appl => 'OTA'
1593 , p_error_txt => 'OTA_13309_TAV_NO_LANG'
1594 );
1595 --
1596 End if;
1597 --
1598 End if;
1599 --
1600 close sel_language;
1601 --
1602 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1603 --
1604 End check_language;
1605 --
1606 -- ----------------------------------------------------------------------------
1607 -- |-------------------< check_controlling_person >---------------------------|
1608 -- ----------------------------------------------------------------------------
1609 --
1610 -- PUBLIC
1611 -- Description:
1612 -- The controlling person should exist as a valid person on the Validity
1613 -- Start Date of the Activity Version.
1614 --
1615 Procedure check_controlling_person
1616 (
1617 p_person_id in number
1618 ,p_date in date
1619 ) is
1620 --
1621 v_proc varchar2(72) := g_package||'check_controlling_person';
1622 --
1623 Begin
1624 --
1625 hr_utility.set_location('Entering:'|| v_proc, 5);
1626 --
1627 If p_person_id is not null Then
1628 --
1629 If NOT ota_general.check_person( p_person_id, nvl(p_date,hr_api.g_sot)) Then
1630 --
1631 -- ** TEMP ** Add error message with the following text.
1632 --
1633 call_error_message( p_error_appl => 'OTA'
1634 , p_error_txt => 'OTA_13295_TAV_NO_MAN'
1635 );
1636 --
1637 End if;
1638 --
1639 End if;
1640 --
1641 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1642 --
1643 End check_controlling_person;
1644 --
1645 -- ----------------------------------------------------------------------------
1646 -- |----------------------< exist_multiple_versions >---------------------|
1647 -- ----------------------------------------------------------------------------
1648 --
1649 -- PUBLIC
1650 -- Description:
1651 -- Checks whether multiple activity versions exist when updating the start and-- end dates of an activity version
1652 --
1653 Function exist_multiple_versions
1654 (
1655 p_activity_id in number,
1656 p_activity_version_id in number,
1657 p_start_date in date,
1658 p_end_date in date
1659 ) Return boolean is
1660 --
1661 v_proc varchar2(72) := g_package||'exist_multiple_versions';
1662 --
1663 v_exists varchar2(1) := 'N';
1664 v_start_date date;
1665 v_end_date date;
1666 v_default_end_date date;
1667 --
1668 cursor sel_versions is
1669 select 'Y'
1670 from ota_activity_versions tav
1671 where tav.activity_id = p_activity_id
1672 and ((tav.activity_version_id <> p_activity_version_id
1673 and p_activity_version_id is not null )
1674 or p_activity_version_id is null)
1675 and v_start_date <= nvl(tav.end_date,
1676 greatest(v_default_end_date,tav.start_date))
1677 and v_end_date >= nvl(tav.start_date,hr_api.g_sot);
1678 --
1679 Begin
1680 --
1681 hr_utility.set_location('Entering:'|| v_proc, 5);
1682 --
1683 if p_start_date is null then
1684 v_start_date := hr_api.g_sot;
1685 else
1686 v_start_date := p_start_date;
1687 end if;
1688 --
1689 if p_end_date is null then
1690 v_end_date := hr_api.g_eot;
1691 else
1692 v_end_date := p_end_date;
1693 end if;
1694 --
1695 -- When inserting a new version the end date of the previous version
1696 -- will be set to p_start_date-1 if it is not already set. Therefore we
1697 -- should only validate up to this date.
1698 --
1699 -- When updating the default end date should be the end of time
1700 --
1701 if p_activity_version_id is null then
1702 if p_start_date is null then
1703 v_default_end_date := hr_api.g_eot;
1704 else
1705 v_default_end_date := p_start_date - 1;
1706 end if;
1707 else
1708 v_default_end_date := hr_api.g_eot;
1709 end if;
1710 --
1711 hr_utility.trace(to_char(v_start_date));
1712 hr_utility.trace(to_char(v_end_date));
1713 hr_utility.trace(to_char(v_default_end_date));
1714 --
1715 Open sel_versions;
1716 fetch sel_versions into v_exists;
1717 --
1718 if sel_versions%found then
1719 hr_utility.trace('Returning true');
1720 close sel_versions;
1721 return(true);
1722 else
1723 hr_utility.trace('Returning False');
1724 close sel_versions;
1725 return(false);
1726 end if;
1727 --
1728 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1729 --
1730 End exist_multiple_versions;
1731 --
1732 --
1733 -- ----------------------------------------------------------------------------
1734 -- |--------------------< check_multiple_con_version>----------------------|
1735 -- ----------------------------------------------------------------------------
1736 --
1737 -- PUBLIC
1738 -- Description:
1739 -- If the Activity Definitions is specified with the
1740 -- MULTIPLE_CON_VERSIONS_FLAG set to 'N' then Versions of the Activity may not
1741 --
1742 Procedure check_multiple_con_version
1743 (
1744 p_activity_id in number,
1745 p_activity_version_id in number,
1746 p_start_date in date,
1747 p_end_date in date
1748 ) is
1749 --
1750 v_proc varchar2(72) := g_package||'check_multiple_con_versions';
1751 v_exists varchar2(1);
1752 --
1753 cursor sel_activity is
1754 select 'Y'
1755 from ota_activity_definitions tad
1756 where tad.activity_id = p_activity_id
1757 and tad.multiple_con_versions_flag = 'N' ;
1758 --
1759 Begin
1760 --
1761 hr_utility.set_location('Entering:'|| v_proc, 5);
1762 --
1763 Open sel_activity;
1764 Fetch sel_activity into v_exists ;
1765 --
1766 If sel_activity%found Then
1767 --
1768 If exist_multiple_versions( p_activity_id
1769 , p_activity_version_id
1770 , p_start_date
1771 , p_end_date) Then
1772 --
1773 --
1774 call_error_message( p_error_appl => 'OTA'
1775 , p_error_txt => 'OTA_13310_TAV_NO_CON_VERS'
1776 );
1777 --
1778 End if;
1779 --
1780 End if;
1781 --
1782 Close sel_activity;
1783 --
1784 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1785 --
1786 End check_multiple_con_version;
1787 --
1788 --
1789 -- ----------------------------------------------------------------------------
1790 -- |------------------< set_superseding_version >-----------------------------|
1791 -- ----------------------------------------------------------------------------
1792 --
1793 -- PUBLIC
1794 -- Description:
1795 -- If the Activity Definitions is specified with the
1796 -- MULTIPLE_CON_VERSIONS_FLAG set to 'N' and a new version is created for
1797 -- that activity, the superseded by field on the previous version must be
1798 -- populated with the name of the new version
1799 --
1800 Procedure set_superseding_version
1801 (
1802 p_activity_id in number
1803 ,p_activity_version_id in number
1804 ,p_start_date in date
1805 ) is
1806 --
1807 l_ovn number(9);
1808 v_version_id number(9);
1809 v_start_date date;
1810 v_end_date date;
1811 v_object_version_number number;
1812 v_proc varchar2(72) := g_package||'set_superseding_version';
1813 --
1814 cursor get_previous_version is
1815 select tav.activity_version_id
1816 , tav.start_date
1817 , tav.end_date
1818 , tav.object_version_number
1819 from ota_activity_versions tav
1820 , ota_activity_definitions tad
1821 where tad.activity_id = p_activity_id
1822 and tad.multiple_con_versions_flag = 'N'
1823 and tav.activity_id = tad.activity_id
1824 and tav.start_date =
1825 (select max(tav2.start_date)
1826 from ota_activity_versions tav2
1827 where tav2.activity_version_id <> p_activity_version_id
1828 and tav2.activity_id = p_activity_id);
1829 --
1830 Begin
1831 --
1832 hr_utility.set_location('Entering:'|| v_proc, 5);
1833 --
1834 open get_previous_version;
1835 fetch get_previous_version into v_version_id,
1836 v_start_date,
1837 v_end_date,
1838 v_object_version_number;
1839 --
1840 if get_previous_version%notfound then
1841 null;
1842 elsif
1843 v_start_date > p_start_date-1 then
1844 /*
1845 fnd_message.set_name('OTA','OTA_13500_TAV_NO_CLOSE_DATE');
1846 fnd_message.raise_error;
1847 */
1848 null;
1849 else
1850 --
1851 -- If the end date is not null then we do not want to update it
1852 --
1853 if v_end_date is not null then
1854 v_end_date := hr_api.g_date;
1855 else
1856 v_end_date := p_start_date - 1;
1857 end if;
1858 --
1859 ota_tav_upd.upd(p_activity_version_id => v_version_id
1860 ,p_activity_id => p_activity_id
1861 ,p_superseded_by_act_version_id => p_activity_version_id
1862 ,p_end_date => v_end_date
1863 ,p_object_version_number => v_object_version_number);
1864 end if;
1865 close get_previous_version;
1866 --
1867 --
1868 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1869 --
1870 End set_superseding_version;
1871 --
1872 -- ----------------------------------------------------------------------------
1873 -- |--------------------< set_superseding_start_date>-------------------------|
1874 -- ----------------------------------------------------------------------------
1875 --
1876 -- PUBLIC
1877 -- Description:
1878 -- If the previous version has an end date then the start date defaults to
1879 -- the end date of the previous version plus one
1880 --
1881 Function set_superseding_start_date
1882 (
1883 p_activity_id in number
1884 ) Return date is
1885 --
1886 v_proc varchar2(72) := g_package||'set_superseding-start_date';
1887 v_max_end_date date;
1888 v_activity_id number;
1889 v_activity_version_id number;
1890 --
1891 cursor get_versions is
1892 select tav.end_date
1893 from ota_activity_versions tav
1894 , ota_activity_definitions tad
1895 where tav.activity_id = tad.activity_id --p_activity_id
1896 and tad.activity_id = p_activity_id -- Bug 2808274
1897 and tad.multiple_con_versions_flag = 'N'
1898 order by tav.end_date desc;
1899 --
1900 Begin
1901 --
1902 hr_utility.set_location('Entering:'|| v_proc, 5);
1903 --
1904 Open get_versions;
1905 Fetch get_versions into v_max_end_date;
1906 --
1907 If get_versions%found and v_max_end_date is not null Then
1908 --
1909 return(v_max_end_date + 1);
1910 --
1911 Else
1912 --
1913 return (sysdate);
1914 --
1915 End if;
1916 --
1917 Close get_versions;
1918 --
1919 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1920 --
1921 End set_superseding_start_date;
1922 --
1923 -- ---------------------------------------------------------------------------
1924 -- |------------------< check_version_after_supersede >----------------------|
1925 -- ---------------------------------------------------------------------------
1926 --
1927 -- PUBLIC
1928 -- Description:
1929 -- If the Activity Definitions is specified with the
1930 -- MULTIPLE_CON_VERSIONS_FLAG set to 'N' and the latest Activity Version has
1931 -- been superseded by a Version of a different Activity, then new Version of
1932 -- the Activity are not allowed (because there would be confusion over which
1933 -- is the valid versiou of the activity, the new one or the superseding one).
1934 --
1935 Procedure check_version_after_supersede
1936 (
1937 p_activity_id in number
1938 ) is
1939 --
1940 v_exists varchar2(1);
1941 v_act_version_id number(9);
1942 v_proc varchar2(72) := g_package||'check_version_after_supersede';
1943 --
1944 cursor sel_latest_version is
1945 select tav.superseded_by_act_version_id
1946 from ota_activity_definitions tad
1947 , ota_activity_versions tav
1948 where tad.activity_id = p_activity_id
1949 and tad.multiple_con_versions_flag = 'N'
1950 and tav.activity_id = tad.activity_id
1951 and nvl(tav.end_date, hr_api.g_eot) =
1952 (select max( nvl(tav2.end_date, hr_api.g_eot))
1953 from ota_activity_versions tav2
1954 where tav2.activity_id = p_activity_id);
1955 --
1956 cursor sel_superseded_act( pc_activity_version_id IN number) is
1957 select 'Y'
1958 from ota_activity_versions tav
1959 where tav.activity_id = p_activity_id
1960 and tav.activity_version_id = pc_activity_version_id ;
1961 --
1962 Begin
1963 --
1964 hr_utility.set_location('Entering:'|| v_proc, 5);
1965 --
1966 Open sel_latest_version;
1967 Fetch sel_latest_version into v_act_version_id;
1968 --
1969 If sel_latest_version%found then
1970 --
1971 If v_act_version_id is not null Then
1972 --
1973 Open sel_superseded_act( v_act_version_id);
1974 Fetch sel_superseded_act into v_exists;
1975 --
1976 If sel_superseded_act%notfound then
1977 --
1978 -- The activities of the actual activity version and the superseded
1979 -- activity version are different.
1980 --
1981 close sel_latest_version;
1982 close sel_superseded_act;
1983 --
1984 fnd_message.set_name('OTA','OTA_13311_TAV_NO_CON_SUP');
1985 fnd_message.raise_error;
1986 --
1987 End if;
1988 --
1989 close sel_superseded_act;
1990 --
1991 End if;
1992 --
1993 End if;
1994 --
1995 close sel_latest_version;
1996 --
1997 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1998 --
1999 End check_version_after_supersede;
2000 --
2001
2002 -- ----------------------------------------------------------------------------
2003 -- |-----------------------------< check_OE_Lines_exist>------------------------|
2004 -- ----------------------------------------------------------------------------
2005 --
2006 -- PUBLIC
2007 -- Description:
2008 -- If The inventory id that link to this Activity has been ordered through
2009 -- Order Line than user cannot change the inventory id.
2010 --
2011 --
2012 Procedure check_OE_lines_exist
2013 (
2014 p_activity_version_id in number,
2015 p_inventory_item_id in number,
2016 p_organization_id in number
2017 )IS
2018 l_proc varchar2(72) := g_package||'check_OE_lines_exist';
2019 l_exists varchar2(1);
2020 l_old_inventory_item_id mtl_system_items_b.inventory_item_id%type;
2021 l_old_organization_id mtl_system_items_b.organization_id%type;
2022 --
2023 -- cursor to check is inventory id is valid.
2024 --
2025 cursor csr_order_line is
2026 select null
2027 from oe_order_lines_all
2028 where inventory_item_id = p_inventory_item_id and
2029 org_id = p_organization_id;
2030
2031 cursor csr_order_line_exist is
2032 select null
2033 from oe_order_lines_all
2034 where inventory_item_id = l_old_inventory_item_id and
2035 org_id = l_old_organization_id;
2036
2037
2038 cursor csr_old_inventory is
2039 select inventory_item_id,
2040 organization_id
2041 from ota_activity_versions
2042 where activity_version_id = p_activity_version_id;
2043 --
2044 Begin
2045 --
2046 hr_utility.set_location('Entering:'|| l_proc, 5);
2047
2048 OPEN csr_old_inventory;
2049 FETCH csr_old_inventory into l_old_inventory_item_id,
2050 l_old_organization_id;
2051 CLOSE csr_old_inventory;
2052 if ((p_activity_version_id is not null) and
2053 nvl(l_old_inventory_item_id,hr_api.g_number) <>
2054 nvl(p_inventory_item_id,hr_api.g_number)) then
2055 --
2056 hr_utility.set_location('Entering:'||l_proc, 10);
2057
2058 --
2059 if p_inventory_item_id is null and l_old_inventory_item_id is not null then
2060 hr_utility.set_location('Entering:'||l_proc, 15);
2061 open csr_order_line_exist;
2062 fetch csr_order_line_exist into l_exists;
2063 if csr_order_line_exist%found then
2064 fnd_message.set_name('OTA','OTA_13695_TAV_INV_EXIST');
2065 fnd_message.raise_error;
2066 end if;
2067 close csr_order_line_exist;
2068 hr_utility.set_location('Entering:'||l_proc, 20);
2069
2070
2071 end if;
2072 if (p_inventory_item_id is not null and
2073 p_organization_id is not null) and
2074 (l_old_inventory_item_id is not null and
2075 l_old_organization_id is not null) then
2076 hr_utility.set_location('Entering:'||l_proc, 25);
2077 open csr_order_line_exist;
2078 fetch csr_order_line_exist into l_exists;
2079 if csr_order_line_exist%found then
2080 fnd_message.set_name('OTA','OTA_13695_TAV_INV_EXIST');
2081 fnd_message.raise_error;
2082 end if;
2083 close csr_order_line_exist;
2084 hr_utility.set_location('Entering:'||l_proc, 30);
2085 end if;
2086 --
2087 end if;
2088 hr_utility.set_location(' Leaving:'|| l_proc, 35);
2089 --
2090 exception
2091 when app_exception.application_exception then
2092 if hr_multi_message.exception_add
2093 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.INVENTORY_ITEM_ID'
2094 ) then
2095 hr_utility.set_location(' Leaving:'|| l_proc,70);
2096 raise;
2097 end if;
2098 hr_utility.set_location(' Leaving:'|| l_proc,80);
2099 End check_OE_lines_exist;
2100
2101 --
2102 -- ----------------------------------------------------------------------------
2103 -- |----------------------------< chk_Inventory_item_id>----------------------|
2104 -- ----------------------------------------------------------------------------
2105 --
2106 -- PUBLIC
2107 -- Description:
2108 -- If The inventory id is not a valid inventory id in MTL_SYSTEM_ITEMS_B table
2109 -- then user has to provide the correct one.
2110 --
2111 --
2112 --
2113 Procedure check_Inventory_item_id
2114 (
2115 p_activity_version_id in number,
2116 p_inventory_item_id in number,
2117 p_organization_id in number
2118 )IS
2119 l_proc varchar2(72) := g_package||'check_Inventory_item_id';
2120 l_exists varchar2(1);
2121 l_old_inventory_item_id mtl_system_items_b.inventory_item_id%type;
2122 l_old_organization_id mtl_system_items_b.organization_id%type;
2123 L_NUM number ;
2124 --
2125 -- cursor to check is inventory id is valid.
2126 --
2127 cursor csr_inventory is
2128 select null
2129 from mtl_system_items_b
2130 where inventory_item_id = p_inventory_item_id and
2131 organization_id = p_organization_id;
2132
2133 cursor csr_old_inventory is
2134 select inventory_item_id,
2135 organization_id
2136 from
2137 ota_activity_versions
2138 where activity_version_id <> p_activity_version_id;
2139
2140 cursor cur_offerings is
2141 select 1 from ota_offerings off,ota_category_usages cat
2142 where cat.type ='DM'
2143 and (cat.online_flag = 'Y' or( cat.online_flag = 'N' and cat.synchronous_flag = 'N'))
2144 and cat.category_usage_id = off.delivery_mode_id
2145 and off.activity_version_id = p_activity_version_id;
2146
2147 cursor csr_enr is -- For bug 4401602
2148 select 1 from ota_offerings off,
2149 ota_events evt,
2150 ota_delegate_bookings tdb
2151 where evt.parent_offering_id = off.offering_id
2152 and tdb.event_id = evt.event_id
2153 and off.activity_version_id = p_activity_version_id;
2154
2155 Begin
2156 --
2157
2158 hr_utility.set_location('Entering:'|| l_proc, 5);
2159 IF p_activity_version_id is not null THEN
2160 OPEN csr_old_inventory;
2161 FETCH csr_old_inventory into l_old_inventory_item_id,
2162 l_old_organization_id;
2163 CLOSE csr_old_inventory;
2164 END IF;
2165
2166 if (((p_activity_version_id is not null) and
2167 (nvl(l_old_inventory_item_id ,hr_api.g_number) <>
2168 nvl(p_inventory_item_id,hr_api.g_number)or
2169 nvl(l_old_organization_id,hr_api.g_number) <>
2170 nvl(p_organization_id,hr_api.g_number)))
2171 or (p_activity_version_id is null)) then
2172 --
2173 hr_utility.set_location('Entering:'||l_proc, 10);
2174
2175 --
2176 if p_inventory_item_id is not null and
2177 p_organization_id is not null then
2178 hr_utility.set_location('Entering:'||l_proc, 15);
2179 open csr_inventory;
2180 fetch csr_inventory into l_exists;
2181 if csr_inventory%notfound then
2182 fnd_message.set_name('OTA','OTA_13693_TAV_NO_INV');
2183 fnd_message.raise_error;
2184 end if;
2185 close csr_inventory;
2186 hr_utility.set_location('Entering:'||l_proc, 20);
2187 elsif p_inventory_item_id is not null and
2188 p_organization_id is null then
2189 fnd_message.set_name('OTA','OTA_13892_TAV_INV_COMB_INVALID');
2190 fnd_message.raise_error;
2191 elsif p_inventory_item_id is null and
2192 p_organization_id is not null then
2193 fnd_message.set_name('OTA','OTA_13892_TAV_INV_COMB_INVALID');
2194 fnd_message.raise_error;
2195
2196 end if;
2197 --
2198 end if;
2199
2200 if ( p_activity_version_id is not null
2201 and p_inventory_item_id is not null
2202 and (nvl(l_old_inventory_item_id ,hr_api.g_number) <>
2203 nvl(p_inventory_item_id,hr_api.g_number))) then
2204
2205 open cur_offerings;
2206 fetch cur_offerings
2207 into l_num;
2208 if cur_offerings%found then
2209 fnd_message.set_name('OTA','OTA_443662_ACT_INVENTORY');
2210 fnd_message.raise_error;
2211 end if;
2212 close cur_offerings;
2213
2214
2215 open csr_enr; -- Bug 4401602
2216 fetch csr_enr
2217 into l_num;
2218 if csr_enr%found then
2219 fnd_message.set_name('OTA','OTA_443915_TAV_INV_ENR_EXITS');
2220 fnd_message.raise_error;
2221 end if;
2222 close csr_enr;
2223
2224
2225 end if;
2226
2227
2228
2229 hr_utility.set_location(' Leaving:'|| l_proc, 30);
2230 --
2231 exception
2232 when app_exception.application_exception then
2233 if hr_multi_message.exception_add
2234 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.INVENTORY_ITEM_ID'
2235 ) then
2236 hr_utility.set_location(' Leaving:'|| l_proc,70);
2237 raise;
2238 end if;
2239 hr_utility.set_location(' Leaving:'|| l_proc,80);
2240 End check_inventory_item_id;
2241
2242 --
2243 -- ----------------------------------------------------------------------------
2244 -- |-----------------------------< check_unique_rco_id>------------------------|
2245 -- ----------------------------------------------------------------------------
2246 --
2247 -- PUBLIC
2248 -- Description:
2249 -- Check uniqueness of rco_id
2250 --
2251 --
2252 --
2253 --
2254 Procedure check_unique_rco_id
2255 (
2256 p_activity_version_id in number,
2257 p_rco_id in number)
2258
2259 IS
2260
2261 l_proc varchar2(72) := g_package||'check_unique_rco_id';
2262 l_exists varchar2(1);
2263
2264 cursor csr_rco is
2265 select null
2266 from ota_activity_versions
2267 where rco_id = p_rco_id;
2268
2269 Begin
2270
2271 hr_utility.set_location('Entering:'||l_proc, 5);
2272
2273 if (((p_activity_version_id is not null) and
2274 nvl(ota_tav_shd.g_old_rec.rco_id,hr_api.g_number) <>
2275 nvl(p_rco_id,hr_api.g_number))
2276 or (p_activity_version_id is null)) then
2277 --
2278 hr_utility.set_location('Entering:'||l_proc, 10);
2279 if (p_rco_id is not null) then
2280 hr_utility.set_location('Entering:'||l_proc, 15);
2281 open csr_rco;
2282 fetch csr_rco into l_exists;
2283 if csr_rco%found then
2284 ota_tav_shd.constraint_error(p_constraint_name =>'OTA_ACTIVITY_VERSIONS_UK5');
2285 end if;
2286 close csr_rco;
2287 hr_utility.set_location('Leaving:'||l_proc, 20);
2288 end if;
2289 end if;
2290 exception
2291 when app_exception.application_exception then
2292 if hr_multi_message.exception_add
2293 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.RCO_ID'
2294 ) then
2295 hr_utility.set_location(' Leaving:'|| l_proc,70);
2296 raise;
2297 end if;
2298 hr_utility.set_location(' Leaving:'|| l_proc,80);
2299 hr_utility.set_location('Leaving:'||l_proc, 30);
2300 End;
2301
2302 --
2303 -- ----------------------------------------------------------------------------
2304 -- |-----------------------------< check_course_lp_dates>------------------------|
2305 -- ----------------------------------------------------------------------------
2306 --
2307 -- PUBLIC
2308 -- Description:
2309 -- Check dates of Course and Learning Path
2310 --
2311 --
2312 --
2313 --
2314 Procedure check_course_lp_dates
2315 (
2316 p_activity_version_id IN NUMBER,
2317 p_start_date IN DATE,
2318 p_end_date IN DATE)
2319
2320 IS
2321
2322 l_proc varchar2(72) := g_package||'check_course_lp_dates';
2323 l_exists varchar2(1);
2324 l_upd_start_date BOOLEAN;
2325 l_upd_end_date BOOLEAN;
2326 l_start_date DATE;
2327 l_end_date DATE;
2328 l_learning_path_id NUMBER;
2329
2330 CURSOR csr_course_lp_dates(l_start_date DATE, l_end_date DATE) IS
2331 SELECT lps.learning_path_id
2332 FROM ota_learning_paths_vl lps,
2333 ota_learning_path_members lpm
2334 WHERE lpm.learning_path_id = lps.learning_path_id
2335 AND lpm.activity_version_id = p_activity_version_id
2336 AND (( l_end_date IS NOT NULL AND lps.start_date_active > l_end_date)
2337 OR (lps.end_date_active IS NOT NULL AND l_start_date > lps.end_date_active));
2338
2339 Begin
2340
2341 hr_utility.set_location('Entering:'||l_proc, 5);
2342 IF hr_multi_message.no_exclusive_error
2343 (p_check_column1 => 'OTA_ACTIVITY_VERSIONS.START_DATE'
2344 ,p_check_column2 => 'OTA_ACTIVITY_VERSIONS.END_DATE'
2345 ,p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.START_DATE'
2346 ,p_associated_column2 => 'OTA_ACTIVITY_VERSIONS.END_DATE' ) THEN
2347
2348 IF (NVL(ota_tav_shd.g_old_rec.start_date, hr_api.g_date) <>
2349 NVL( p_start_date, hr_api.g_date )) THEN
2350 l_upd_start_date := TRUE;
2351 l_start_date := p_start_date;
2352 ELSE
2353 l_upd_start_date := FALSE;
2354 l_start_date := ota_tav_shd.g_old_rec.start_date;
2355 END IF;
2356
2357 IF (NVL(ota_tav_shd.g_old_rec.end_date, hr_api.g_date) <>
2358 NVL( p_end_date, hr_api.g_date )) THEN
2359 l_upd_end_date := TRUE;
2360 l_end_date := p_end_date;
2361 ELSE
2362 l_upd_end_date := FALSE;
2363 l_end_date := ota_tav_shd.g_old_rec.end_date;
2364 END IF;
2365
2366 IF (l_upd_start_date OR l_upd_end_date) THEN
2367
2368 OPEN csr_course_lp_dates(l_start_date, l_end_date);
2369 FETCH csr_course_lp_dates INTO l_learning_path_id;
2370
2371 IF csr_course_lp_dates%FOUND THEN
2372 call_error_message( p_error_appl => 'OTA'
2373 , p_error_txt => 'OTA_443073_CRS_LP_DTS_INVALID'
2374 );
2375 CLOSE csr_course_lp_dates;
2376 ELSE
2377 CLOSE csr_course_lp_dates;
2378 END IF;
2379 END IF;
2380 END IF;
2381
2382 hr_utility.set_location('Leaving:'||l_proc, 80);
2383 End;
2384
2385 -- ----------------------------------------------------------------------------
2386 -- |-------------------------< check_if_noth_exists >--------------------------|
2387 -- ----------------------------------------------------------------------------
2388 --
2389 -- PUBLIC
2390 -- Description:
2391 -- Delete Validation.
2392 -- This activity version may not be deleted if child rows in
2393 -- ota_notrng_histories exists where this activity version.
2394 --
2395 Procedure check_if_noth_exists
2396 (
2397 p_activity_version_id in number
2398 )
2399 IS
2400 --
2401 v_exists varchar2(1);
2402 v_proc varchar2(72) := g_package||'check_if_noth_exists';
2403 --
2404 cursor sel_noth_exists is
2405 select 'Y'
2406 from ota_notrng_histories nth
2407 where nth.activity_version_id = p_activity_version_id;
2408 --
2409 Begin
2410 --
2411 hr_utility.set_location('Entering:'|| v_proc, 5);
2412 --
2413 Open sel_noth_exists;
2414 fetch sel_noth_exists into v_exists;
2415 --
2416 if sel_noth_exists%found then
2417 --
2418 close sel_noth_exists;
2419 --
2420 -- ** TEMP ** Add error message with the following text.
2421 --
2422 call_error_message( p_error_appl => 'OTA'
2423 , p_error_txt => 'OTA_443546_TAV_DEL_NTH_EXISTS'
2424 );
2425 --
2426 end if;
2427 --
2428 close sel_noth_exists;
2429 --
2430 hr_utility.set_location(' Leaving:'|| v_proc, 10);
2431 --
2432
2433 end check_if_noth_exists;
2434
2435 -- ----------------------------------------------------------------------------
2436 -- |-------------------------< check_if_crt_exists >--------------------------|
2437 -- ----------------------------------------------------------------------------
2438 --
2439 -- PUBLIC
2440 -- Description:
2441 -- Delete Validation.
2442 -- This activity version may not be deleted if child rows in
2443 -- ota_certification_members exists where this activity version.
2444 --
2445 Procedure check_if_crt_exists
2446 (
2447 p_activity_version_id in number
2448 )
2449 IS
2450 --
2451 v_exists varchar2(1);
2452 v_proc varchar2(72) := g_package||'check_if_crt_exists';
2453 --
2454 cursor sel_ctm_exists is
2455 select 'Y'
2456 from ota_certification_members ctm
2457 where ctm.object_id = p_activity_version_id
2458 and ctm.object_type = 'H';
2459 --
2460 Begin
2461 --
2462 hr_utility.set_location('Entering:'|| v_proc, 5);
2463 --
2464 Open sel_ctm_exists;
2465 fetch sel_ctm_exists into v_exists;
2466 --
2467 if sel_ctm_exists%found then
2468 --
2469 close sel_ctm_exists;
2470 --
2471 --
2472 --
2473 call_error_message( p_error_appl => 'OTA'
2474 , p_error_txt => 'OTA_443949_TAV_DEL_CTM_EXISTS'
2475 );
2476 --
2477 end if;
2478 --
2479 close sel_ctm_exists;
2480 --
2481 hr_utility.set_location(' Leaving:'|| v_proc, 10);
2482 --
2483
2484 end check_if_crt_exists;
2485
2486 --
2487 -- ----------------------------------------------------------------------------
2488 -- |-----------------------------< check_course_crt_dates>---------------------|
2489 -- ----------------------------------------------------------------------------
2490 --
2491 -- PUBLIC
2492 -- Description:
2493 -- Check dates of Course and Certification
2494 --
2495 --
2496 --
2497 --
2498 Procedure check_course_crt_dates
2499 (
2500 p_activity_version_id IN NUMBER,
2501 p_start_date IN DATE,
2502 p_end_date IN DATE)
2503
2504 IS
2505
2506 l_proc varchar2(72) := g_package||'check_course_crt_dates';
2507 l_exists varchar2(1);
2508 l_upd_start_date BOOLEAN;
2509 l_upd_end_date BOOLEAN;
2510 l_start_date DATE;
2511 l_end_date DATE;
2512 l_certification_id NUMBER;
2513
2514 CURSOR csr_course_crt_dates(l_start_date DATE, l_end_date DATE) IS
2515 SELECT ctm.certification_id
2516 FROM ota_certification_members ctm
2517 WHERE ctm.object_id = p_activity_version_id
2518 AND ctm.object_type='H'
2519 AND (
2520 (l_end_date IS NOT NULL AND ctm.start_date_active > l_end_date)
2521 OR (l_end_date is not null and ctm.end_date_active is not null AND ctm.end_date_active > l_end_date)
2522 OR (ctm.start_date_active < l_start_date)
2523 OR (ctm.end_date_active IS NOT NULL AND l_start_date > ctm.end_date_active)
2524 )
2525 ;
2526
2527
2528 Begin
2529
2530 hr_utility.set_location('Entering:'||l_proc, 5);
2531 IF hr_multi_message.no_exclusive_error
2532 (p_check_column1 => 'OTA_ACTIVITY_VERSIONS.START_DATE'
2533 ,p_check_column2 => 'OTA_ACTIVITY_VERSIONS.END_DATE'
2534 ,p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.START_DATE'
2535 ,p_associated_column2 => 'OTA_ACTIVITY_VERSIONS.END_DATE' ) THEN
2536
2537 IF (NVL(ota_tav_shd.g_old_rec.start_date, hr_api.g_date) <>
2538 NVL( p_start_date, hr_api.g_date )) THEN
2539 l_upd_start_date := TRUE;
2540 l_start_date := p_start_date;
2541 ELSE
2542 l_upd_start_date := FALSE;
2543 l_start_date := ota_tav_shd.g_old_rec.start_date;
2544 END IF;
2545
2546 IF (NVL(ota_tav_shd.g_old_rec.end_date, hr_api.g_date) <>
2547 NVL( p_end_date, hr_api.g_date )) THEN
2548 l_upd_end_date := TRUE;
2549 l_end_date := p_end_date;
2550 ELSE
2551 l_upd_end_date := FALSE;
2552 l_end_date := ota_tav_shd.g_old_rec.end_date;
2553 END IF;
2554
2555 IF (l_upd_start_date OR l_upd_end_date) THEN
2556
2557 OPEN csr_course_crt_dates(l_start_date, l_end_date);
2558 FETCH csr_course_crt_dates INTO l_certification_id;
2559
2560 IF csr_course_crt_dates%FOUND THEN
2561 call_error_message( p_error_appl => 'OTA'
2562 , p_error_txt => 'OTA_443808_CRS_CRT_DTS_INVALID'
2563 );
2564 CLOSE csr_course_crt_dates;
2565 ELSE
2566 CLOSE csr_course_crt_dates;
2567 END IF;
2568 END IF;
2569 END IF;
2570
2571 hr_utility.set_location('Leaving:'||l_proc, 80);
2572 End check_course_crt_dates;
2573 end ota_tav_api_business_rules;