[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.4 2005/08/19 13:12:44 estreacy noship $ */
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 -- ---------------------------------------------------------------------------
433 -- Checks to see if an Activity has overlapping versions. If a version has a
430 --
431 -- PUBLIC
432 -- Description:
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
523 --
520 (
521 p_succ_criteria in varchar2
522 ) is
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 -- ----------------------------------------------------------------------------
680 -- PUBLIC
677 -- |-------------------------< check_dates_update_rud >-----------------------|
678 -- ----------------------------------------------------------------------------
679 --
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;
823 hr_utility.set_location(' Leaving:'|| v_proc, 10);
820 --
821 Close sel_check_dates;
822 --
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 l_obj_off varchar2(30) := ota_utility.Get_lookup_meaning('OTA_CATALOG_OBJECT_TYPE','O',810);
911 l_obj_act varchar2(30) := ota_utility.Get_lookup_meaning('OTA_CATALOG_OBJECT_TYPE','H',810);
912
913 --
914 cursor sel_check_dates is
915 select start_date
916 , end_date
917 from ota_offerings off -- bug 3534657
918 where off.activity_version_id = p_activity_version_id;
919 --
920 Begin
921 --
922 hr_utility.set_location('Entering:'|| v_proc, 5);
923 --
924 Open sel_check_dates;
925 Fetch sel_check_dates into v_start_date
926 , v_end_date;
927 --
928 Loop
929 --
930 Exit When sel_check_dates%notfound OR sel_check_dates%notfound is null;
931 --
932 -- Assignment if course_start_date or course_end_date is null
933 --
934 If v_start_date is null Then
935 --
936 v_start_date := p_start_date;
937 --
938 End if;
939 --
940 If v_end_date is null Then
941 --
942 v_end_date := hr_api.g_eot;
943 --
944 End if;
945 --
946 If ota_general.check_par_child_dates_fun( p_start_date
947 , p_end_date
948 , v_start_date
949 , v_end_date ) then
950 --
951 fnd_message.set_name ( 'OTA','OTA_443166_OBJ_CHILD_DATE');
952 fnd_message.set_token('OBJECT_NAME', l_obj_act );
953 fnd_message.set_token('CHILD_OBJECT', l_obj_off);
954 fnd_message.raise_error;
955 /*
956 call_error_message( p_error_appl => 'OTA'
957 , p_error_txt => 'OTA_13559_TAV_EVT_DATES'
958 );
959 */
960 --
961 End if;
962 --
966 --
963 Fetch sel_check_dates into v_start_date
964 , v_end_date;
965 End loop;
967 Close sel_check_dates;
968 --
969 hr_utility.set_location(' Leaving:'|| v_proc, 10);
970 --
971 End check_dates_update_evt;
972 --
973 -- ----------------------------------------------------------------------------
974 -- |--------------------------< check_category_dates >------------------------|
975 -- ----------------------------------------------------------------------------
976 --
977 -- PUBLIC
978 -- Description:
979 -- Validates the startdate and enddate with respect to category dates.
980 --
981 Procedure check_category_dates
982 (
983 p_activity_version_id in number
984 ,p_start_date in date
985 ,p_end_date in date
986 ) is
987 --
988 -- Declare cursors and local variables
989 --
990 -- Cursor to get value if parent category is already exits in child hierarchy of base category
991
992 CURSOR cur_cat_start_end_date is
993 select
994 ctu.start_date_active,
995 nvl(ctu.end_date_active, hr_api.g_eot)
996 from
997 ota_category_usages ctu,
998 ota_act_cat_inclusions aci
999 where
1000 ctu.category_usage_id = aci.category_usage_id
1001 and aci.activity_version_id = p_activity_version_id
1002 and ctu.type='C'
1003 and aci.primary_flag='Y';
1004 --
1005 -- Variables for API Boolean parameters
1006 l_proc varchar2(72) := g_package ||'check_category_dates';
1007 l_cat_start_date date;
1008 l_cat_end_date date;
1009
1010 Begin
1011 hr_utility.set_location(' Entering:' || l_proc,10);
1012 --
1013 IF hr_multi_message.no_exclusive_error
1014 (p_check_column1 => 'OTA_ACTIVITY_VERSIONS.START_DATE'
1015 ,p_check_column2 => 'OTA_ACTIVITY_VERSIONS.END_DATE'
1016 ,p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.START_DATE'
1017 ,p_associated_column2 => 'OTA_ACTIVITY_VERSIONS.END_DATE'
1018 ) THEN
1019 --
1020 OPEN cur_cat_start_end_date;
1021 FETCH cur_cat_start_end_date into l_cat_start_date, l_cat_end_date;
1022
1023 IF cur_cat_start_end_date%FOUND THEN
1024 CLOSE cur_cat_start_end_date;
1025 IF ( l_cat_start_date > p_start_date
1026 or l_cat_end_date < nvl(p_end_date, hr_api.g_eot)
1027 ) THEN
1028 --
1029 fnd_message.set_name ( 'OTA','OTA_13062_ACT_OUT_OF_CAT_DATES');
1030 fnd_message.raise_error;
1031 --
1032 End IF;
1033 ELSE
1034 CLOSE cur_cat_start_end_date;
1035 End IF;
1036 End IF;
1037 --
1038 hr_utility.set_location(' Leaving:' || l_proc,10);
1039 Exception
1040 when app_exception.application_exception then
1041 IF hr_multi_message.exception_add
1042 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.START_DATE'
1043 ,p_associated_column2 => 'OTA_ACTIVITY_VERSIONS.END_DATE'
1044 ) THEN
1045 hr_utility.set_location(' Leaving:'|| l_proc,20);
1046 raise;
1047 END IF;
1048
1049 hr_utility.set_location(' Leaving:'|| l_proc,30);
1050 --
1051 End check_category_dates;
1052 --
1053 -- ----------------------------------------------------------------------------
1054 -- |-------------------------< check_if_evt_exists >--------------------------|
1055 -- ----------------------------------------------------------------------------
1056 --
1057 -- PUBLIC
1058 -- Description:
1059 -- Delete Validation.
1060 -- This activity version may not be deleted if child rows in
1061 -- ota_events exist.
1062 --
1063 Procedure check_if_evt_exists
1064 (
1065 p_activity_version_id in number
1066 ) is
1067 --
1068 v_exists varchar2(1);
1069 v_proc varchar2(72) := g_package||'check_if_evt_exists';
1070 --
1071 cursor sel_evt_exists is
1072 select 'Y'
1073 from ota_events evt
1074 where evt.activity_version_id = p_activity_version_id;
1075 --
1076 Begin
1077 --
1078 hr_utility.set_location('Entering:'|| v_proc, 5);
1079 --
1080 Open sel_evt_exists;
1081 fetch sel_evt_exists into v_exists;
1082 --
1083 if sel_evt_exists%found then
1084 --
1085 close sel_evt_exists;
1086 --
1087 -- ** TEMP ** Add error message with the following text.
1088 --
1089 call_error_message( p_error_appl => 'OTA'
1090 , p_error_txt => 'OTA_13304_TAV_DEL_EVT_EXISTS'
1091 );
1092 --
1093 end if;
1094 --
1095 close sel_evt_exists;
1096 --
1097 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1098 --
1099 End check_if_evt_exists;
1100 --
1101 -- ----------------------------------------------------------------------------
1102 -- |-------------------------< check_if_off_exists >--------------------------|
1103 -- ----------------------------------------------------------------------------
1104 --
1105 -- PUBLIC
1109 -- ota_offerings exist.
1106 -- Description:
1107 -- Delete Validation.
1108 -- This activity version may not be deleted if child rows in
1110 --
1111 Procedure check_if_off_exists
1112 (
1113 p_activity_version_id in number
1114 ) is
1115 --
1116 v_exists varchar2(1);
1117 v_proc varchar2(72) := g_package||'check_if_off_exists';
1118 --
1119 cursor sel_off_exists is
1120 select 'Y'
1121 from ota_offerings off
1122 where off.activity_version_id = p_activity_version_id;
1123 --
1124 Begin
1125 --
1126 hr_utility.set_location('Entering:'|| v_proc, 5);
1127 --
1128 Open sel_off_exists;
1129 fetch sel_off_exists into v_exists;
1130 --
1131 if sel_off_exists%found then
1132 --
1133 close sel_off_exists;
1134 --
1135 -- ** TEMP ** Add error message with the following text.
1136 --
1137 call_error_message( p_error_appl => 'OTA'
1138 , p_error_txt => 'OTA_443400_TAV_DEL_OFF_EXISTS'
1139 );
1140 --
1141 end if;
1142 --
1143 close sel_off_exists;
1144 --
1145 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1146 --
1147 End check_if_off_exists;
1148 --
1149 -- ----------------------------------------------------------------------------
1150 -- |-------------------------< check_if_tpm_exists >--------------------------|
1151 -- ----------------------------------------------------------------------------
1152 -- PUBLIC
1153 -- Description:
1154 -- Delete Validation.
1155 -- This activity version may not be deleted if child rows in
1156 -- ota_training_plan_members exist.
1157 --
1158 Procedure check_if_tpm_exists
1159 (
1160 p_activity_version_id in number
1161 ) is
1162 --
1163 v_proc varchar2(72) := g_package||'check_if_tpm_exists';
1164 --
1165 -- dynamic sql statment to check if the activity version is referenced
1166 -- in training plan members
1167 --
1168 Cursor c_get_tpm_rows is
1169 Select 'Y'
1170 from OTA_TRAINING_PLAN_MEMBERS
1171 where activity_version_id = p_activity_version_id;
1172 --
1173 l_dyn_curs integer;
1174 l_dyn_rows integer;
1175 l_dummy varchar2(1);
1176 --
1177 Begin
1178 --
1179 hr_utility.set_location('Entering:'|| v_proc, 5);
1180 --
1181 -- Check if the activity version is referenced in training plan members table
1182 open c_get_tpm_rows;
1183 fetch c_get_tpm_rows into l_dummy;
1184 if c_get_tpm_rows%found then
1185 close c_get_tpm_rows;
1186 call_error_message( p_error_appl => 'OTA'
1187 , p_error_txt => 'OTA_13820_TAV_NO_DEL_TPM_EXIST');
1188 else
1189 close c_get_tpm_rows;
1190 end if;
1191 --
1192 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1193 --
1194 End check_if_tpm_exists;
1195 --
1196 -- ----------------------------------------------------------------------------
1197 -- |-------------------------< check_if_tbd_exists >--------------------------|
1198 -- ----------------------------------------------------------------------------
1199 --
1200 -- PUBLIC
1201 -- Description:
1202 -- Delete Validation.
1203 -- This activity version may not be deleted if child rows in
1204 -- ota_booking_deals exist.
1205 --
1206 Procedure check_if_tbd_exists
1207 (
1208 p_activity_version_id in number
1209 ) is
1210 --
1211 v_exists varchar2(1);
1212 v_proc varchar2(72) := g_package||'check_if_tbd_exists';
1213 --
1214 cursor sel_tbd_exists is
1215 select 'Y'
1216 from ota_booking_deals tbd
1217 where tbd.activity_version_id = p_activity_version_id;
1218 --
1219 Begin
1220 --
1221 hr_utility.set_location('Entering:'|| v_proc, 5);
1222 --
1223 Open sel_tbd_exists;
1224 fetch sel_tbd_exists into v_exists;
1225 --
1226 if sel_tbd_exists%found then
1227 --
1228 close sel_tbd_exists;
1229 --
1230 -- ** TEMP ** Add error message with the following text.
1231 --
1232 call_error_message( p_error_appl => 'OTA'
1233 , p_error_txt => 'OTA_13305_TAV_DEL_TBD_EXISTS'
1234 );
1235 --
1236 end if;
1237 --
1238 close sel_tbd_exists;
1239 --
1240 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1241 --
1242 End check_if_tbd_exists;
1243 --
1244 -- ----------------------------------------------------------------------------
1245 -- |-------------------------< check_if_ple_exists >--------------------------|
1246 -- ----------------------------------------------------------------------------
1247 --
1248 -- PUBLIC
1249 -- Description:
1250 -- Delete Validation.
1251 -- This activity version may not be deleted if child rows in
1252 -- ota_price_lists_entries exist.
1253 --
1254 Procedure check_if_ple_exists
1255 (
1256 p_activity_version_id in number
1257 ) is
1258 --
1259 v_exists varchar2(1);
1263 select 'Y'
1260 v_proc varchar2(72) := g_package||'check_if_ple_exists';
1261 --
1262 cursor sel_ple_exists is
1264 from ota_price_list_entries ple
1265 where ple.activity_version_id = p_activity_version_id;
1266 --
1267 Begin
1268 --
1269 hr_utility.set_location('Entering:'|| v_proc, 5);
1270 --
1271 Open sel_ple_exists;
1272 fetch sel_ple_exists into v_exists;
1273 --
1274 if sel_ple_exists%found then
1275 --
1276 close sel_ple_exists;
1277 --
1278 -- ** TEMP ** Add error message with the following text.
1279 --
1280 call_error_message( p_error_appl => 'OTA'
1281 , p_error_txt => 'OTA_13306_TAV_DEL_PLE_EXISTS'
1282 );
1283 --
1284 end if;
1285 --
1286 close sel_ple_exists;
1287 --
1288 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1289 --
1290 End check_if_ple_exists;
1291 --
1292 -- ----------------------------------------------------------------------------
1293 -- |-------------------------< check_if_comp_exists >--------------------------|
1294 -- ----------------------------------------------------------------------------
1295 --
1296 -- PUBLIC
1297 -- Description:
1298 -- Delete Validation.
1299 -- This activity version may not be deleted if child rows in
1300 -- per_competence_elements exist.
1301 --
1302 Procedure check_if_comp_exists
1303 (
1304 p_activity_version_id in number
1305 ) is
1306 --
1307 v_exists varchar2(1);
1308 v_proc varchar2(72) := g_package||'check_if_comp_exists';
1309 --
1310 cursor sel_comp_exists is
1311 select 'Y'
1312 from per_competence_elements pce
1313 where pce.activity_version_id = p_activity_version_id;
1314 --
1315 Begin
1316 --
1317 hr_utility.set_location('Entering:'|| v_proc, 5);
1318 --
1319 Open sel_comp_exists;
1320 fetch sel_comp_exists into v_exists;
1321 --
1322 if sel_comp_exists%found then
1323 --
1324 close sel_comp_exists;
1325 --
1326 -- ** TEMP ** Add error message with the following text.
1327 --
1328 call_error_message( p_error_appl => 'OTA'
1329 , p_error_txt => 'OTA_443398_TAV_DEL_COMP_EXISTS'
1330 );
1331 --
1332 end if;
1333 --
1334 close sel_comp_exists;
1335 --
1336 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1337 --
1338 End check_if_comp_exists;
1339 --
1340
1341 --
1342 -- ----------------------------------------------------------------------------
1343 -- |-------------------------< check_if_lpm_exists >--------------------------|
1344 -- ----------------------------------------------------------------------------
1345 --
1346 -- PUBLIC
1347 -- Description:
1348 -- Delete Validation.
1349 -- This activity version may not be deleted if child rows in
1350 -- ota_learning_path_members exist.
1351 --
1352 Procedure check_if_lpm_exists
1353 (
1354 p_activity_version_id in number
1355 ) is
1356 --
1357 v_exists varchar2(1);
1358 v_proc varchar2(72) := g_package||'check_if_lpm_exists';
1359 --
1360 cursor sel_lpm_exists is
1361 select 'Y'
1362 from ota_learning_path_members lpm
1363 where lpm.activity_version_id = p_activity_version_id;
1364 --
1365 Begin
1366 --
1367 hr_utility.set_location('Entering:'|| v_proc, 5);
1368 --
1369 Open sel_lpm_exists;
1370 fetch sel_lpm_exists into v_exists;
1371 --
1372 if sel_lpm_exists%found then
1373 --
1374 close sel_lpm_exists;
1375 --
1376 -- ** TEMP ** Add error message with the following text.
1377 --
1378 call_error_message( p_error_appl => 'OTA'
1379 , p_error_txt => 'OTA_443399_TAV_DEL_LPM_EXISTS'
1380 );
1381 --
1382 end if;
1383 --
1384 close sel_lpm_exists;
1385 --
1386 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1387 --
1388 End check_if_lpm_exists;
1389 --
1390
1391 -- ----------------------------------------------------------------------------
1392 -- |-------------------------< check_if_tav_exists >--------------------------|
1393 -- ----------------------------------------------------------------------------
1394 --
1395 -- PUBLIC
1396 -- Description:
1397 -- Delete Validation.
1398 -- This activity version may not be deleted if child rows in
1399 -- ota_activity_versions exists where this activity version has superseded
1400 -- another earlier activity version.
1401 --
1402 Procedure check_if_tav_exists
1403 (
1404 p_activity_version_id in number
1405 ) is
1406 --
1407 v_exists varchar2(1);
1408 v_proc varchar2(72) := g_package||'check_if_tav_exists';
1409 --
1410 cursor sel_tav_exists is
1411 select 'Y'
1412 from ota_activity_versions tav
1413 where tav.superseded_by_act_version_id = p_activity_version_id;
1414 --
1415 Begin
1416 --
1420 fetch sel_tav_exists into v_exists;
1417 hr_utility.set_location('Entering:'|| v_proc, 5);
1418 --
1419 Open sel_tav_exists;
1421 --
1422 if sel_tav_exists%found then
1423 --
1424 close sel_tav_exists;
1425 --
1426 -- ** TEMP ** Add error message with the following text.
1427 --
1428 call_error_message( p_error_appl => 'OTA'
1429 , p_error_txt => 'OTA_13307_TAV_DEL_SUP_EXISTS'
1430 );
1431 --
1432 end if;
1433 --
1434 close sel_tav_exists;
1435 --
1436 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1437 --
1438 End check_if_tav_exists;
1439 --
1440 -- ----------------------------------------------------------------------------
1441 -- |-------------------------< check_if_tsp_exists >--------------------------|
1442 -- ----------------------------------------------------------------------------
1443 --
1444 -- PUBLIC
1445 -- Description:
1446 -- Delete Validation.
1447 -- This activity version may not be deleted if child rows in
1448 -- ota_skill_provisions.
1449 --
1450 Procedure check_if_tsp_exists
1451 (
1452 p_activity_version_id in number
1453 ) is
1454 --
1455 v_exists varchar2(1);
1456 v_proc varchar2(72) := g_package||'check_if_tsp_exists';
1457 --
1458 cursor sel_tsp_exists is
1459 select 'Y'
1460 from ota_skill_provisions tsp
1461 where tsp.activity_version_id = p_activity_version_id;
1462 --
1463 Begin
1464 --
1465 hr_utility.set_location('Entering:'|| v_proc, 5);
1466 --
1467 Open sel_tsp_exists;
1468 fetch sel_tsp_exists into v_exists;
1469 --
1470 if sel_tsp_exists%found then
1471 --
1472 close sel_tsp_exists;
1473 --
1474 -- ** TEMP ** Add error message with the following text.
1475 --
1476 call_error_message( p_error_appl => 'OTA'
1477 , p_error_txt => 'OTA_443265_TAV_DEL_TSP_EXISTS'
1478 );
1479 --
1480 end if;
1481 --
1482 close sel_tsp_exists;
1483 --
1484 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1485 --
1486 End check_if_tsp_exists;
1487 --
1488
1489 -- ----------------------------------------------------------------------------
1490 -- |------------------------< check_duration_units >--------------------------|
1491 -- ----------------------------------------------------------------------------
1492 --
1493 -- PUBLIC
1494 -- Description:
1495 -- The duration units must be in the domain 'Units'.
1496 --
1497 Procedure check_duration_units
1498 (
1499 p_duration_units in varchar2
1500 ) is
1501 --
1502 v_proc varchar2(72) := g_package||'check_duration_units';
1503 --
1504 Begin
1505 --
1506 hr_utility.set_location('Entering:'|| v_proc, 5);
1507 --
1508 If p_duration_units is not null Then
1509 --
1510 ota_general.check_domain_value( 'OTA_DURATION_UNITS', p_duration_units);
1511 --
1512 End if;
1513 --
1514 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1515 --
1516 End check_duration_units;
1517 --
1518 -- ----------------------------------------------------------------------------
1519 -- |--------------------------< check_duration >------------------------------|
1520 -- ----------------------------------------------------------------------------
1521 --
1522 -- PUBLIC
1523 -- Description:
1524 -- The duration must be a positive integer greater than zero.
1525 --
1526 Procedure check_duration
1527 (
1528 p_duration in number
1529 ) is
1530 --
1531 v_proc varchar2(72) := g_package||'check_duration';
1532 --
1533 Begin
1534 --
1535 hr_utility.set_location('Entering:'|| v_proc, 5);
1536 --
1537 If p_duration <= 0 Then
1538 --
1539 -- ** TEMP ** Add error message with the following text.
1540 --
1541 call_error_message( p_error_appl => 'OTA'
1542 , p_error_txt => 'OTA_13308_TAV_DURATION_POS'
1543 );
1544 --
1545 end if;
1546 --
1547 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1548 --
1549 End check_duration;
1550 --
1551 -- ----------------------------------------------------------------------------
1552 -- |---------------------------< check_language >-----------------------------|
1553 -- ----------------------------------------------------------------------------
1554 --
1555 -- PUBLIC
1556 -- Description:
1557 -- The language must be in the domain 'Languages'.
1558 --
1559 Procedure check_language
1560 (
1561 p_language_id in number
1562 ) is
1563 v_exists varchar2(1);
1564 v_proc varchar2(72) := g_package||'check_language';
1565 --
1566 cursor sel_language is
1567 select 'Y'
1568 from fnd_languages lan
1569 where lan.language_id = p_language_id;
1570 --
1571 Begin
1572 --
1573 hr_utility.set_location('Entering:'|| v_proc, 5);
1574 --
1575 Open sel_language;
1579 --
1576 fetch sel_language into v_exists;
1577 --
1578 If p_language_id is not null Then
1580 if sel_language%notfound then
1581 --
1582 close sel_language;
1583 --
1584 -- ** TEMP ** Add error message with the following text.
1585 --
1586 call_error_message( p_error_appl => 'OTA'
1587 , p_error_txt => 'OTA_13309_TAV_NO_LANG'
1588 );
1589 --
1590 End if;
1591 --
1592 End if;
1593 --
1594 close sel_language;
1595 --
1596 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1597 --
1598 End check_language;
1599 --
1600 -- ----------------------------------------------------------------------------
1601 -- |-------------------< check_controlling_person >---------------------------|
1602 -- ----------------------------------------------------------------------------
1603 --
1604 -- PUBLIC
1605 -- Description:
1606 -- The controlling person should exist as a valid person on the Validity
1607 -- Start Date of the Activity Version.
1608 --
1609 Procedure check_controlling_person
1610 (
1611 p_person_id in number
1612 ,p_date in date
1613 ) is
1614 --
1615 v_proc varchar2(72) := g_package||'check_controlling_person';
1616 --
1617 Begin
1618 --
1619 hr_utility.set_location('Entering:'|| v_proc, 5);
1620 --
1621 If p_person_id is not null Then
1622 --
1623 If NOT ota_general.check_person( p_person_id, nvl(p_date,hr_api.g_sot)) Then
1624 --
1625 -- ** TEMP ** Add error message with the following text.
1626 --
1627 call_error_message( p_error_appl => 'OTA'
1628 , p_error_txt => 'OTA_13295_TAV_NO_MAN'
1629 );
1630 --
1631 End if;
1632 --
1633 End if;
1634 --
1635 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1636 --
1637 End check_controlling_person;
1638 --
1639 -- ----------------------------------------------------------------------------
1640 -- |----------------------< exist_multiple_versions >---------------------|
1641 -- ----------------------------------------------------------------------------
1642 --
1643 -- PUBLIC
1644 -- Description:
1645 -- Checks whether multiple activity versions exist when updating the start and-- end dates of an activity version
1646 --
1647 Function exist_multiple_versions
1648 (
1649 p_activity_id in number,
1650 p_activity_version_id in number,
1651 p_start_date in date,
1652 p_end_date in date
1653 ) Return boolean is
1654 --
1655 v_proc varchar2(72) := g_package||'exist_multiple_versions';
1656 --
1657 v_exists varchar2(1) := 'N';
1658 v_start_date date;
1659 v_end_date date;
1660 v_default_end_date date;
1661 --
1662 cursor sel_versions is
1663 select 'Y'
1664 from ota_activity_versions tav
1665 where tav.activity_id = p_activity_id
1666 and ((tav.activity_version_id <> p_activity_version_id
1667 and p_activity_version_id is not null )
1668 or p_activity_version_id is null)
1669 and v_start_date <= nvl(tav.end_date,
1670 greatest(v_default_end_date,tav.start_date))
1671 and v_end_date >= nvl(tav.start_date,hr_api.g_sot);
1672 --
1673 Begin
1674 --
1675 hr_utility.set_location('Entering:'|| v_proc, 5);
1676 --
1677 if p_start_date is null then
1678 v_start_date := hr_api.g_sot;
1679 else
1680 v_start_date := p_start_date;
1681 end if;
1682 --
1683 if p_end_date is null then
1684 v_end_date := hr_api.g_eot;
1685 else
1686 v_end_date := p_end_date;
1687 end if;
1688 --
1689 -- When inserting a new version the end date of the previous version
1690 -- will be set to p_start_date-1 if it is not already set. Therefore we
1691 -- should only validate up to this date.
1692 --
1693 -- When updating the default end date should be the end of time
1694 --
1695 if p_activity_version_id is null then
1696 if p_start_date is null then
1697 v_default_end_date := hr_api.g_eot;
1698 else
1699 v_default_end_date := p_start_date - 1;
1700 end if;
1701 else
1702 v_default_end_date := hr_api.g_eot;
1703 end if;
1704 --
1705 hr_utility.trace(to_char(v_start_date));
1706 hr_utility.trace(to_char(v_end_date));
1707 hr_utility.trace(to_char(v_default_end_date));
1708 --
1709 Open sel_versions;
1710 fetch sel_versions into v_exists;
1711 --
1712 if sel_versions%found then
1713 hr_utility.trace('Returning true');
1714 close sel_versions;
1715 return(true);
1716 else
1717 hr_utility.trace('Returning False');
1718 close sel_versions;
1719 return(false);
1720 end if;
1721 --
1722 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1723 --
1724 End exist_multiple_versions;
1725 --
1726 --
1727 -- ----------------------------------------------------------------------------
1728 -- |--------------------< check_multiple_con_version>----------------------|
1732 -- Description:
1729 -- ----------------------------------------------------------------------------
1730 --
1731 -- PUBLIC
1733 -- If the Activity Definitions is specified with the
1734 -- MULTIPLE_CON_VERSIONS_FLAG set to 'N' then Versions of the Activity may not
1735 --
1736 Procedure check_multiple_con_version
1737 (
1738 p_activity_id in number,
1739 p_activity_version_id in number,
1740 p_start_date in date,
1741 p_end_date in date
1742 ) is
1743 --
1744 v_proc varchar2(72) := g_package||'check_multiple_con_versions';
1745 v_exists varchar2(1);
1746 --
1747 cursor sel_activity is
1748 select 'Y'
1749 from ota_activity_definitions tad
1750 where tad.activity_id = p_activity_id
1751 and tad.multiple_con_versions_flag = 'N' ;
1752 --
1753 Begin
1754 --
1755 hr_utility.set_location('Entering:'|| v_proc, 5);
1756 --
1757 Open sel_activity;
1758 Fetch sel_activity into v_exists ;
1759 --
1760 If sel_activity%found Then
1761 --
1762 If exist_multiple_versions( p_activity_id
1763 , p_activity_version_id
1764 , p_start_date
1765 , p_end_date) Then
1766 --
1767 --
1768 call_error_message( p_error_appl => 'OTA'
1769 , p_error_txt => 'OTA_13310_TAV_NO_CON_VERS'
1770 );
1771 --
1772 End if;
1773 --
1774 End if;
1775 --
1776 Close sel_activity;
1777 --
1778 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1779 --
1780 End check_multiple_con_version;
1781 --
1782 --
1783 -- ----------------------------------------------------------------------------
1784 -- |------------------< set_superseding_version >-----------------------------|
1785 -- ----------------------------------------------------------------------------
1786 --
1787 -- PUBLIC
1788 -- Description:
1789 -- If the Activity Definitions is specified with the
1790 -- MULTIPLE_CON_VERSIONS_FLAG set to 'N' and a new version is created for
1791 -- that activity, the superseded by field on the previous version must be
1792 -- populated with the name of the new version
1793 --
1794 Procedure set_superseding_version
1795 (
1796 p_activity_id in number
1797 ,p_activity_version_id in number
1798 ,p_start_date in date
1799 ) is
1800 --
1801 l_ovn number(9);
1802 v_version_id number(9);
1803 v_start_date date;
1804 v_end_date date;
1805 v_object_version_number number;
1806 v_proc varchar2(72) := g_package||'set_superseding_version';
1807 --
1808 cursor get_previous_version is
1809 select tav.activity_version_id
1810 , tav.start_date
1811 , tav.end_date
1812 , tav.object_version_number
1813 from ota_activity_versions tav
1814 , ota_activity_definitions tad
1815 where tad.activity_id = p_activity_id
1816 and tad.multiple_con_versions_flag = 'N'
1817 and tav.activity_id = tad.activity_id
1818 and tav.start_date =
1819 (select max(tav2.start_date)
1820 from ota_activity_versions tav2
1821 where tav2.activity_version_id <> p_activity_version_id
1822 and tav2.activity_id = p_activity_id);
1823 --
1824 Begin
1825 --
1826 hr_utility.set_location('Entering:'|| v_proc, 5);
1827 --
1828 open get_previous_version;
1829 fetch get_previous_version into v_version_id,
1830 v_start_date,
1831 v_end_date,
1832 v_object_version_number;
1833 --
1834 if get_previous_version%notfound then
1835 null;
1836 elsif
1837 v_start_date > p_start_date-1 then
1838 /*
1839 fnd_message.set_name('OTA','OTA_13500_TAV_NO_CLOSE_DATE');
1840 fnd_message.raise_error;
1841 */
1842 null;
1843 else
1844 --
1845 -- If the end date is not null then we do not want to update it
1846 --
1847 if v_end_date is not null then
1848 v_end_date := hr_api.g_date;
1849 else
1850 v_end_date := p_start_date - 1;
1851 end if;
1852 --
1853 ota_tav_upd.upd(p_activity_version_id => v_version_id
1854 ,p_activity_id => p_activity_id
1855 ,p_superseded_by_act_version_id => p_activity_version_id
1856 ,p_end_date => v_end_date
1857 ,p_object_version_number => v_object_version_number);
1858 end if;
1859 close get_previous_version;
1860 --
1861 --
1862 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1863 --
1864 End set_superseding_version;
1865 --
1866 -- ----------------------------------------------------------------------------
1867 -- |--------------------< set_superseding_start_date>-------------------------|
1868 -- ----------------------------------------------------------------------------
1869 --
1870 -- PUBLIC
1871 -- Description:
1875 Function set_superseding_start_date
1872 -- If the previous version has an end date then the start date defaults to
1873 -- the end date of the previous version plus one
1874 --
1876 (
1877 p_activity_id in number
1878 ) Return date is
1879 --
1880 v_proc varchar2(72) := g_package||'set_superseding-start_date';
1881 v_max_end_date date;
1882 v_activity_id number;
1883 v_activity_version_id number;
1884 --
1885 cursor get_versions is
1886 select tav.end_date
1887 from ota_activity_versions tav
1888 , ota_activity_definitions tad
1889 where tav.activity_id = tad.activity_id --p_activity_id
1890 and tad.activity_id = p_activity_id -- Bug 2808274
1891 and tad.multiple_con_versions_flag = 'N'
1892 order by tav.end_date desc;
1893 --
1894 Begin
1895 --
1896 hr_utility.set_location('Entering:'|| v_proc, 5);
1897 --
1898 Open get_versions;
1899 Fetch get_versions into v_max_end_date;
1900 --
1901 If get_versions%found and v_max_end_date is not null Then
1902 --
1903 return(v_max_end_date + 1);
1904 --
1905 Else
1906 --
1907 return (sysdate);
1908 --
1909 End if;
1910 --
1911 Close get_versions;
1912 --
1913 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1914 --
1915 End set_superseding_start_date;
1916 --
1917 -- ---------------------------------------------------------------------------
1918 -- |------------------< check_version_after_supersede >----------------------|
1919 -- ---------------------------------------------------------------------------
1920 --
1921 -- PUBLIC
1922 -- Description:
1923 -- If the Activity Definitions is specified with the
1924 -- MULTIPLE_CON_VERSIONS_FLAG set to 'N' and the latest Activity Version has
1925 -- been superseded by a Version of a different Activity, then new Version of
1926 -- the Activity are not allowed (because there would be confusion over which
1927 -- is the valid versiou of the activity, the new one or the superseding one).
1928 --
1929 Procedure check_version_after_supersede
1930 (
1931 p_activity_id in number
1932 ) is
1933 --
1934 v_exists varchar2(1);
1935 v_act_version_id number(9);
1936 v_proc varchar2(72) := g_package||'check_version_after_supersede';
1937 --
1938 cursor sel_latest_version is
1939 select tav.superseded_by_act_version_id
1940 from ota_activity_definitions tad
1941 , ota_activity_versions tav
1942 where tad.activity_id = p_activity_id
1943 and tad.multiple_con_versions_flag = 'N'
1944 and tav.activity_id = tad.activity_id
1945 and nvl(tav.end_date, hr_api.g_eot) =
1946 (select max( nvl(tav2.end_date, hr_api.g_eot))
1947 from ota_activity_versions tav2
1948 where tav2.activity_id = p_activity_id);
1949 --
1950 cursor sel_superseded_act( pc_activity_version_id IN number) is
1951 select 'Y'
1952 from ota_activity_versions tav
1953 where tav.activity_id = p_activity_id
1954 and tav.activity_version_id = pc_activity_version_id ;
1955 --
1956 Begin
1957 --
1958 hr_utility.set_location('Entering:'|| v_proc, 5);
1959 --
1960 Open sel_latest_version;
1961 Fetch sel_latest_version into v_act_version_id;
1962 --
1963 If sel_latest_version%found then
1964 --
1965 If v_act_version_id is not null Then
1966 --
1967 Open sel_superseded_act( v_act_version_id);
1968 Fetch sel_superseded_act into v_exists;
1969 --
1970 If sel_superseded_act%notfound then
1971 --
1972 -- The activities of the actual activity version and the superseded
1973 -- activity version are different.
1974 --
1975 close sel_latest_version;
1976 close sel_superseded_act;
1977 --
1978 fnd_message.set_name('OTA','OTA_13311_TAV_NO_CON_SUP');
1979 fnd_message.raise_error;
1980 --
1981 End if;
1982 --
1983 close sel_superseded_act;
1984 --
1985 End if;
1986 --
1987 End if;
1988 --
1989 close sel_latest_version;
1990 --
1991 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1992 --
1993 End check_version_after_supersede;
1994 --
1995
1996 -- ----------------------------------------------------------------------------
1997 -- |-----------------------------< check_OE_Lines_exist>------------------------|
1998 -- ----------------------------------------------------------------------------
1999 --
2000 -- PUBLIC
2001 -- Description:
2002 -- If The inventory id that link to this Activity has been ordered through
2003 -- Order Line than user cannot change the inventory id.
2004 --
2005 --
2006 Procedure check_OE_lines_exist
2007 (
2008 p_activity_version_id in number,
2009 p_inventory_item_id in number,
2010 p_organization_id in number
2011 )IS
2012 l_proc varchar2(72) := g_package||'check_OE_lines_exist';
2013 l_exists varchar2(1);
2017 -- cursor to check is inventory id is valid.
2014 l_old_inventory_item_id mtl_system_items_b.inventory_item_id%type;
2015 l_old_organization_id mtl_system_items_b.organization_id%type;
2016 --
2018 --
2019 cursor csr_order_line is
2020 select null
2021 from oe_order_lines_all
2022 where inventory_item_id = p_inventory_item_id and
2023 org_id = p_organization_id;
2024
2025 cursor csr_order_line_exist is
2026 select null
2027 from oe_order_lines_all
2028 where inventory_item_id = l_old_inventory_item_id and
2029 org_id = l_old_organization_id;
2030
2031
2032 cursor csr_old_inventory is
2033 select inventory_item_id,
2034 organization_id
2035 from ota_activity_versions
2036 where activity_version_id = p_activity_version_id;
2037 --
2038 Begin
2039 --
2040 hr_utility.set_location('Entering:'|| l_proc, 5);
2041
2042 OPEN csr_old_inventory;
2043 FETCH csr_old_inventory into l_old_inventory_item_id,
2044 l_old_organization_id;
2045 CLOSE csr_old_inventory;
2046 if ((p_activity_version_id is not null) and
2047 nvl(l_old_inventory_item_id,hr_api.g_number) <>
2048 nvl(p_inventory_item_id,hr_api.g_number)) then
2049 --
2050 hr_utility.set_location('Entering:'||l_proc, 10);
2051
2052 --
2053 if p_inventory_item_id is null and l_old_inventory_item_id is not null then
2054 hr_utility.set_location('Entering:'||l_proc, 15);
2055 open csr_order_line_exist;
2056 fetch csr_order_line_exist into l_exists;
2057 if csr_order_line_exist%found then
2058 fnd_message.set_name('OTA','OTA_13695_TAV_INV_EXIST');
2059 fnd_message.raise_error;
2060 end if;
2061 close csr_order_line_exist;
2062 hr_utility.set_location('Entering:'||l_proc, 20);
2063
2064
2065 end if;
2066 if (p_inventory_item_id is not null and
2067 p_organization_id is not null) and
2068 (l_old_inventory_item_id is not null and
2069 l_old_organization_id is not null) then
2070 hr_utility.set_location('Entering:'||l_proc, 25);
2071 open csr_order_line_exist;
2072 fetch csr_order_line_exist into l_exists;
2073 if csr_order_line_exist%found then
2074 fnd_message.set_name('OTA','OTA_13695_TAV_INV_EXIST');
2075 fnd_message.raise_error;
2076 end if;
2077 close csr_order_line_exist;
2078 hr_utility.set_location('Entering:'||l_proc, 30);
2079 end if;
2080 --
2081 end if;
2082 hr_utility.set_location(' Leaving:'|| l_proc, 35);
2083 --
2084 exception
2085 when app_exception.application_exception then
2086 if hr_multi_message.exception_add
2087 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.INVENTORY_ITEM_ID'
2088 ) then
2089 hr_utility.set_location(' Leaving:'|| l_proc,70);
2090 raise;
2091 end if;
2092 hr_utility.set_location(' Leaving:'|| l_proc,80);
2093 End check_OE_lines_exist;
2094
2095 --
2096 -- ----------------------------------------------------------------------------
2097 -- |----------------------------< chk_Inventory_item_id>----------------------|
2098 -- ----------------------------------------------------------------------------
2099 --
2100 -- PUBLIC
2101 -- Description:
2102 -- If The inventory id is not a valid inventory id in MTL_SYSTEM_ITEMS_B table
2103 -- then user has to provide the correct one.
2104 --
2105 --
2106 --
2107 Procedure check_Inventory_item_id
2108 (
2109 p_activity_version_id in number,
2110 p_inventory_item_id in number,
2111 p_organization_id in number
2112 )IS
2113 l_proc varchar2(72) := g_package||'check_Inventory_item_id';
2114 l_exists varchar2(1);
2115 l_old_inventory_item_id mtl_system_items_b.inventory_item_id%type;
2116 l_old_organization_id mtl_system_items_b.organization_id%type;
2117 L_NUM number ;
2118 --
2119 -- cursor to check is inventory id is valid.
2120 --
2121 cursor csr_inventory is
2122 select null
2123 from mtl_system_items_b
2124 where inventory_item_id = p_inventory_item_id and
2125 organization_id = p_organization_id;
2126
2127 cursor csr_old_inventory is
2128 select inventory_item_id,
2129 organization_id
2130 from
2131 ota_activity_versions
2132 where activity_version_id <> p_activity_version_id;
2133
2134 cursor cur_offerings is
2135 select 1 from ota_offerings off,ota_category_usages cat
2136 where cat.type ='DM'
2137 and (cat.online_flag = 'Y' or( cat.online_flag = 'N' and cat.synchronous_flag = 'N'))
2138 and cat.category_usage_id = off.delivery_mode_id
2139 and off.activity_version_id = p_activity_version_id;
2140
2141 cursor csr_enr is -- For bug 4401602
2142 select 1 from ota_offerings off,
2143 ota_events evt,
2144 ota_delegate_bookings tdb
2145 where evt.parent_offering_id = off.offering_id
2146 and tdb.event_id = evt.event_id
2147 and off.activity_version_id = p_activity_version_id;
2148
2149 Begin
2150 --
2151
2152 hr_utility.set_location('Entering:'|| l_proc, 5);
2156 l_old_organization_id;
2153 IF p_activity_version_id is not null THEN
2154 OPEN csr_old_inventory;
2155 FETCH csr_old_inventory into l_old_inventory_item_id,
2157 CLOSE csr_old_inventory;
2158 END IF;
2159
2160 if (((p_activity_version_id is not null) and
2161 (nvl(l_old_inventory_item_id ,hr_api.g_number) <>
2162 nvl(p_inventory_item_id,hr_api.g_number)or
2163 nvl(l_old_organization_id,hr_api.g_number) <>
2164 nvl(p_organization_id,hr_api.g_number)))
2165 or (p_activity_version_id is null)) then
2166 --
2167 hr_utility.set_location('Entering:'||l_proc, 10);
2168
2169 --
2170 if p_inventory_item_id is not null and
2171 p_organization_id is not null then
2172 hr_utility.set_location('Entering:'||l_proc, 15);
2173 open csr_inventory;
2174 fetch csr_inventory into l_exists;
2175 if csr_inventory%notfound then
2176 fnd_message.set_name('OTA','OTA_13693_TAV_NO_INV');
2177 fnd_message.raise_error;
2178 end if;
2179 close csr_inventory;
2180 hr_utility.set_location('Entering:'||l_proc, 20);
2181 elsif p_inventory_item_id is not null and
2182 p_organization_id is null then
2183 fnd_message.set_name('OTA','OTA_13892_TAV_INV_COMB_INVALID');
2184 fnd_message.raise_error;
2185 elsif p_inventory_item_id is null and
2186 p_organization_id is not null then
2187 fnd_message.set_name('OTA','OTA_13892_TAV_INV_COMB_INVALID');
2188 fnd_message.raise_error;
2189
2190 end if;
2191 --
2192 end if;
2193
2194 if ( p_activity_version_id is not null
2195 and p_inventory_item_id is not null
2196 and (nvl(l_old_inventory_item_id ,hr_api.g_number) <>
2197 nvl(p_inventory_item_id,hr_api.g_number))) then
2198
2199 open cur_offerings;
2200 fetch cur_offerings
2201 into l_num;
2202 if cur_offerings%found then
2203 fnd_message.set_name('OTA','OTA_443662_ACT_INVENTORY');
2204 fnd_message.raise_error;
2205 end if;
2206 close cur_offerings;
2207
2208
2209 open csr_enr; -- Bug 4401602
2210 fetch csr_enr
2211 into l_num;
2212 if csr_enr%found then
2213 fnd_message.set_name('OTA','OTA_443915_TAV_INV_ENR_EXITS');
2214 fnd_message.raise_error;
2215 end if;
2216 close csr_enr;
2217
2218
2219 end if;
2220
2221
2222
2223 hr_utility.set_location(' Leaving:'|| l_proc, 30);
2224 --
2225 exception
2226 when app_exception.application_exception then
2227 if hr_multi_message.exception_add
2228 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.INVENTORY_ITEM_ID'
2229 ) then
2230 hr_utility.set_location(' Leaving:'|| l_proc,70);
2231 raise;
2232 end if;
2233 hr_utility.set_location(' Leaving:'|| l_proc,80);
2234 End check_inventory_item_id;
2235
2236 --
2237 -- ----------------------------------------------------------------------------
2238 -- |-----------------------------< check_unique_rco_id>------------------------|
2239 -- ----------------------------------------------------------------------------
2240 --
2241 -- PUBLIC
2242 -- Description:
2243 -- Check uniqueness of rco_id
2244 --
2245 --
2246 --
2247 --
2248 Procedure check_unique_rco_id
2249 (
2250 p_activity_version_id in number,
2251 p_rco_id in number)
2252
2253 IS
2254
2255 l_proc varchar2(72) := g_package||'check_unique_rco_id';
2256 l_exists varchar2(1);
2257
2258 cursor csr_rco is
2259 select null
2260 from ota_activity_versions
2261 where rco_id = p_rco_id;
2262
2263 Begin
2264
2265 hr_utility.set_location('Entering:'||l_proc, 5);
2266
2267 if (((p_activity_version_id is not null) and
2268 nvl(ota_tav_shd.g_old_rec.rco_id,hr_api.g_number) <>
2269 nvl(p_rco_id,hr_api.g_number))
2270 or (p_activity_version_id is null)) then
2271 --
2272 hr_utility.set_location('Entering:'||l_proc, 10);
2273 if (p_rco_id is not null) then
2274 hr_utility.set_location('Entering:'||l_proc, 15);
2275 open csr_rco;
2276 fetch csr_rco into l_exists;
2277 if csr_rco%found then
2278 ota_tav_shd.constraint_error(p_constraint_name =>'OTA_ACTIVITY_VERSIONS_UK5');
2279 end if;
2280 close csr_rco;
2281 hr_utility.set_location('Leaving:'||l_proc, 20);
2282 end if;
2283 end if;
2284 exception
2285 when app_exception.application_exception then
2286 if hr_multi_message.exception_add
2287 (p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.RCO_ID'
2288 ) then
2289 hr_utility.set_location(' Leaving:'|| l_proc,70);
2290 raise;
2291 end if;
2292 hr_utility.set_location(' Leaving:'|| l_proc,80);
2293 hr_utility.set_location('Leaving:'||l_proc, 30);
2294 End;
2295
2296 --
2297 -- ----------------------------------------------------------------------------
2301 -- PUBLIC
2298 -- |-----------------------------< check_course_lp_dates>------------------------|
2299 -- ----------------------------------------------------------------------------
2300 --
2302 -- Description:
2303 -- Check dates of Course and Learning Path
2304 --
2305 --
2306 --
2307 --
2308 Procedure check_course_lp_dates
2309 (
2310 p_activity_version_id IN NUMBER,
2311 p_start_date IN DATE,
2312 p_end_date IN DATE)
2313
2314 IS
2315
2316 l_proc varchar2(72) := g_package||'check_course_lp_dates';
2317 l_exists varchar2(1);
2318 l_upd_start_date BOOLEAN;
2319 l_upd_end_date BOOLEAN;
2320 l_start_date DATE;
2321 l_end_date DATE;
2322 l_learning_path_id NUMBER;
2323
2324 CURSOR csr_course_lp_dates(l_start_date DATE, l_end_date DATE) IS
2325 SELECT lps.learning_path_id
2326 FROM ota_learning_paths_vl lps,
2327 ota_learning_path_members lpm
2328 WHERE lpm.learning_path_id = lps.learning_path_id
2329 AND lpm.activity_version_id = p_activity_version_id
2330 AND (( l_end_date IS NOT NULL AND lps.start_date_active > l_end_date)
2331 OR (lps.end_date_active IS NOT NULL AND l_start_date > lps.end_date_active));
2332
2333 Begin
2334
2335 hr_utility.set_location('Entering:'||l_proc, 5);
2336 IF hr_multi_message.no_exclusive_error
2337 (p_check_column1 => 'OTA_ACTIVITY_VERSIONS.START_DATE'
2338 ,p_check_column2 => 'OTA_ACTIVITY_VERSIONS.END_DATE'
2339 ,p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.START_DATE'
2340 ,p_associated_column2 => 'OTA_ACTIVITY_VERSIONS.END_DATE' ) THEN
2341
2342 IF (NVL(ota_tav_shd.g_old_rec.start_date, hr_api.g_date) <>
2343 NVL( p_start_date, hr_api.g_date )) THEN
2344 l_upd_start_date := TRUE;
2345 l_start_date := p_start_date;
2346 ELSE
2347 l_upd_start_date := FALSE;
2348 l_start_date := ota_tav_shd.g_old_rec.start_date;
2349 END IF;
2350
2351 IF (NVL(ota_tav_shd.g_old_rec.end_date, hr_api.g_date) <>
2352 NVL( p_end_date, hr_api.g_date )) THEN
2353 l_upd_end_date := TRUE;
2354 l_end_date := p_end_date;
2355 ELSE
2356 l_upd_end_date := FALSE;
2357 l_end_date := ota_tav_shd.g_old_rec.end_date;
2358 END IF;
2359
2360 IF (l_upd_start_date OR l_upd_end_date) THEN
2361
2362 OPEN csr_course_lp_dates(l_start_date, l_end_date);
2363 FETCH csr_course_lp_dates INTO l_learning_path_id;
2364
2365 IF csr_course_lp_dates%FOUND THEN
2366 call_error_message( p_error_appl => 'OTA'
2367 , p_error_txt => 'OTA_443073_CRS_LP_DTS_INVALID'
2368 );
2369 CLOSE csr_course_lp_dates;
2370 ELSE
2371 CLOSE csr_course_lp_dates;
2372 END IF;
2373 END IF;
2374 END IF;
2375
2379 -- ----------------------------------------------------------------------------
2376 hr_utility.set_location('Leaving:'||l_proc, 80);
2377 End;
2378
2380 -- |-------------------------< check_if_noth_exists >--------------------------|
2381 -- ----------------------------------------------------------------------------
2382 --
2383 -- PUBLIC
2384 -- Description:
2385 -- Delete Validation.
2386 -- This activity version may not be deleted if child rows in
2387 -- ota_notrng_histories exists where this activity version.
2388 --
2389 Procedure check_if_noth_exists
2390 (
2391 p_activity_version_id in number
2392 )
2393 IS
2394 --
2395 v_exists varchar2(1);
2396 v_proc varchar2(72) := g_package||'check_if_noth_exists';
2397 --
2398 cursor sel_noth_exists is
2399 select 'Y'
2400 from ota_notrng_histories nth
2401 where nth.activity_version_id = p_activity_version_id;
2402 --
2403 Begin
2404 --
2405 hr_utility.set_location('Entering:'|| v_proc, 5);
2406 --
2407 Open sel_noth_exists;
2408 fetch sel_noth_exists into v_exists;
2409 --
2410 if sel_noth_exists%found then
2411 --
2412 close sel_noth_exists;
2413 --
2414 -- ** TEMP ** Add error message with the following text.
2415 --
2416 call_error_message( p_error_appl => 'OTA'
2417 , p_error_txt => 'OTA_443546_TAV_DEL_NTH_EXISTS'
2418 );
2419 --
2420 end if;
2421 --
2422 close sel_noth_exists;
2423 --
2424 hr_utility.set_location(' Leaving:'|| v_proc, 10);
2425 --
2426
2427 end check_if_noth_exists;
2428
2429 -- ----------------------------------------------------------------------------
2430 -- |-------------------------< check_if_crt_exists >--------------------------|
2431 -- ----------------------------------------------------------------------------
2432 --
2433 -- PUBLIC
2434 -- Description:
2435 -- Delete Validation.
2436 -- This activity version may not be deleted if child rows in
2437 -- ota_certification_members exists where this activity version.
2438 --
2439 Procedure check_if_crt_exists
2440 (
2441 p_activity_version_id in number
2442 )
2443 IS
2444 --
2445 v_exists varchar2(1);
2446 v_proc varchar2(72) := g_package||'check_if_crt_exists';
2447 --
2448 cursor sel_ctm_exists is
2449 select 'Y'
2450 from ota_certification_members ctm
2451 where ctm.object_id = p_activity_version_id
2452 and ctm.object_type = 'H';
2453 --
2454 Begin
2455 --
2456 hr_utility.set_location('Entering:'|| v_proc, 5);
2457 --
2458 Open sel_ctm_exists;
2459 fetch sel_ctm_exists into v_exists;
2460 --
2461 if sel_ctm_exists%found then
2462 --
2463 close sel_ctm_exists;
2464 --
2465 --
2466 --
2467 call_error_message( p_error_appl => 'OTA'
2468 , p_error_txt => 'OTA_443949_TAV_DEL_CTM_EXISTS'
2469 );
2470 --
2474 --
2471 end if;
2472 --
2473 close sel_ctm_exists;
2475 hr_utility.set_location(' Leaving:'|| v_proc, 10);
2476 --
2477
2478 end check_if_crt_exists;
2479
2480 --
2481 -- ----------------------------------------------------------------------------
2482 -- |-----------------------------< check_course_crt_dates>---------------------|
2483 -- ----------------------------------------------------------------------------
2484 --
2485 -- PUBLIC
2486 -- Description:
2487 -- Check dates of Course and Certification
2488 --
2489 --
2490 --
2491 --
2492 Procedure check_course_crt_dates
2493 (
2494 p_activity_version_id IN NUMBER,
2495 p_start_date IN DATE,
2496 p_end_date IN DATE)
2497
2498 IS
2499
2500 l_proc varchar2(72) := g_package||'check_course_crt_dates';
2501 l_exists varchar2(1);
2502 l_upd_start_date BOOLEAN;
2503 l_upd_end_date BOOLEAN;
2504 l_start_date DATE;
2505 l_end_date DATE;
2506 l_certification_id NUMBER;
2507
2508 CURSOR csr_course_crt_dates(l_start_date DATE, l_end_date DATE) IS
2509 SELECT ctm.certification_id
2510 FROM ota_certification_members ctm
2511 WHERE ctm.object_id = p_activity_version_id
2512 AND ctm.object_type='H'
2513 AND (
2514 (l_end_date IS NOT NULL AND ctm.start_date_active > l_end_date)
2515 OR (l_end_date is not null and ctm.end_date_active is not null AND ctm.end_date_active > l_end_date)
2516 OR (ctm.start_date_active < l_start_date)
2517 OR (ctm.end_date_active IS NOT NULL AND l_start_date > ctm.end_date_active)
2518 )
2519 ;
2520
2521
2522 Begin
2523
2524 hr_utility.set_location('Entering:'||l_proc, 5);
2525 IF hr_multi_message.no_exclusive_error
2526 (p_check_column1 => 'OTA_ACTIVITY_VERSIONS.START_DATE'
2527 ,p_check_column2 => 'OTA_ACTIVITY_VERSIONS.END_DATE'
2528 ,p_associated_column1 => 'OTA_ACTIVITY_VERSIONS.START_DATE'
2529 ,p_associated_column2 => 'OTA_ACTIVITY_VERSIONS.END_DATE' ) THEN
2530
2531 IF (NVL(ota_tav_shd.g_old_rec.start_date, hr_api.g_date) <>
2532 NVL( p_start_date, hr_api.g_date )) THEN
2533 l_upd_start_date := TRUE;
2534 l_start_date := p_start_date;
2535 ELSE
2536 l_upd_start_date := FALSE;
2537 l_start_date := ota_tav_shd.g_old_rec.start_date;
2538 END IF;
2539
2540 IF (NVL(ota_tav_shd.g_old_rec.end_date, hr_api.g_date) <>
2541 NVL( p_end_date, hr_api.g_date )) THEN
2542 l_upd_end_date := TRUE;
2543 l_end_date := p_end_date;
2544 ELSE
2545 l_upd_end_date := FALSE;
2546 l_end_date := ota_tav_shd.g_old_rec.end_date;
2547 END IF;
2548
2549 IF (l_upd_start_date OR l_upd_end_date) THEN
2550
2551 OPEN csr_course_crt_dates(l_start_date, l_end_date);
2552 FETCH csr_course_crt_dates INTO l_certification_id;
2553
2554 IF csr_course_crt_dates%FOUND THEN
2555 call_error_message( p_error_appl => 'OTA'
2556 , p_error_txt => 'OTA_443808_CRS_CRT_DTS_INVALID'
2557 );
2558 CLOSE csr_course_crt_dates;
2559 ELSE
2560 CLOSE csr_course_crt_dates;
2561 END IF;
2562 END IF;
2563 END IF;
2564
2565 hr_utility.set_location('Leaving:'||l_proc, 80);
2566 End check_course_crt_dates;
2567 end ota_tav_api_business_rules;