1 Package Body ota_tdb_bus as
2 /* $Header: ottdb01t.pkb 120.30 2011/02/07 11:01:05 shwnayak ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_tdb_bus.'; -- Global package name
9 --
10 --***************************** STARTS HERE **********************************
11 --
12 g_event_rec ota_evt_shd.g_rec_type;
13 --
14 --
15 -- Global package name
16 --
17 -- global constants
18 --
19 -- Booking Status Types
20 --
21 g_wait_list_booking varchar2(1) := 'W';
22 g_placed_booking varchar2(1) := 'P';
23 g_attended_booking varchar2(1) := 'A';
24 g_pending_evaluation_booking varchar2(1):= 'E';
25 g_cancelled_booking varchar2(1) := 'C';
26 g_requested_booking varchar2(1) := 'R';
27 --
28 -- Event Statuses
29 --
30 g_full_event varchar2(1) := 'W';
31 g_normal_event varchar2(1) := 'N';
32 g_planned_event varchar2(1) := 'P';
33 g_closed_event varchar2(1) := 'C';
34
35 g_legislation_code varchar2(150) default null;
36 g_booking_id number default null;
37
38 -- ----------------------------------------------------------------------------
39 -- |-----------------------< chk_non_updateable_args >------------------------|
40 -- ----------------------------------------------------------------------------
41 -- {Start Of Comments}
42 --
43 -- Description:
44 -- This procedure is used to ensure that non updateable attributes have
45 -- not been updated. If an attribute has been updated an error is generated.
46 --
47 -- Pre Conditions:
48 -- g_old_rec has been populated with details of the values currently in
49 -- the database.
50 --
51 -- In Arguments:
52 -- p_rec has been populated with the updated values the user would like the
53 -- record set to.
54 --
55 -- Post Success:
56 -- Processing continues if all the non updateable attributes have not
57 -- changed.
58 --
59 -- Post Failure:
60 -- An application error is raised if any of the non updatable attributes
61 -- have been altered.
62 --
63 -- {End Of Comments}
64 -- ----------------------------------------------------------------------------
65 Procedure chk_non_updateable_args
66 (
67 p_rec in ota_tdb_shd.g_rec_type
68 ) IS
69 --
70 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
71 l_error EXCEPTION;
72 l_argument varchar2(30);
73 --
74 Begin
75 --
76 -- Only proceed with the validation if a row exists for the current
77 -- record in the HR Schema.
78 --
79 IF NOT ota_tdb_shd.api_updating
80 (p_booking_id => p_rec.booking_id
81 ,p_object_version_number => p_rec.object_version_number
82 ) THEN
83 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
84 fnd_message.set_token('PROCEDURE ', l_proc);
85 fnd_message.set_token('STEP ', '5');
86 fnd_message.raise_error;
87 END IF;
88
89 if nvl(p_rec.business_group_id,hr_api.g_number) <>
90 nvl(ota_tdb_shd.g_old_rec.business_group_id,hr_api.g_number)
91 then
92 l_argument := 'business_group_id';
93 raise l_error;
94 end if;
95 /* if nvl(p_rec.line_id,hr_api.g_number) <>
96 nvl(ota_tdb_shd.g_old_rec.Line_id,hr_api.g_number)
97 then
98 l_argument := 'Line_id';
99 raise l_error;
100 end if;*/
101
102
103 EXCEPTION
104 WHEN l_error THEN
105 hr_api.argument_changed_error
106 (p_api_name => l_proc
107 ,p_argument => l_argument);
108 WHEN OTHERS THEN
109 RAISE;
110 End chk_non_updateable_args;
111
112 --
113 --added for eBS by dhmulia
114 -- ---------------------------------------------------------------------------
115 -- |----------------------< set_security_group_id >--------------------------|
116 -- ---------------------------------------------------------------------------
117 -- {Start Of Comments}
118 --
119 -- Description:
120 -- Sets the security_group_id in CLIENT_INFO for the appropriate business
121 -- group context.
122 --
123 -- Prerequisites:
124 -- The primary key identified by p_booking_id
125 -- already exists.
126 --
127 -- In Arguments:
128 -- p_booking_id
129 --
130 --
131 -- Post Success:
132 -- The security_group_id will be set in CLIENT_INFO.
133 --
134 -- Post Failure:
135 -- An error is raised if the value does not exist.
136 --
137 -- Access Status:
138 -- Internal Development Use Only.
139 --
140 -- {End Of Comments}
141 -- ---------------------------------------------------------------------------
142 procedure set_security_group_id
143 (p_booking_id in number
144 ,p_associated_column1 in varchar2 default null
145 )IS
146 --
147 -- Declare cursor
148 --
149 cursor csr_sec_grp is
150 select pbg.security_group_id,
151 pbg.legislation_code
152 from per_business_groups_perf pbg
153 , ota_delegate_bookings tdb
154 where tdb.booking_id = p_booking_id
155 and pbg.business_group_id = tdb.business_group_id;
156 --
157 -- Declare local variables
158 --
159 l_security_group_id number;
160 l_proc varchar2(72) := g_package||'set_security_group_id';
161 l_legislation_code varchar2(150);
162 --
163 begin
164 --
165 hr_utility.set_location('Entering:'|| l_proc, 10);
166 --
167 -- Ensure that all the mandatory parameter are not null
168 --
169 hr_api.mandatory_arg_error
170 (p_api_name => l_proc
171 ,p_argument => 'booking_id'
172 ,p_argument_value => p_booking_id
173 );
174 --
175 open csr_sec_grp;
176 fetch csr_sec_grp into l_security_group_id
177 , l_legislation_code;
178 --
179 if csr_sec_grp%notfound then
180 --
181 close csr_sec_grp;
182 --
183 -- The primary key is invalid therefore we must error
184 --
185 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
186 hr_multi_message.add
187 (p_associated_column1
188 => nvl(p_associated_column1,'BOOKING_ID')
189 );
190 --
191 else
192 close csr_sec_grp;
193 --
194 -- Set the security_group_id in CLIENT_INFO
195 --
196 hr_api.set_security_group_id
197 (p_security_group_id => l_security_group_id
198 );
199 --
200 -- Set the sessions legislation context in HR_SESSION_DATA
201 --
202 hr_api.set_legislation_context(l_legislation_code);
203 end if;
204 --
205 hr_utility.set_location(' Leaving:'|| l_proc, 20);
206 END set_security_group_id;
207 --added for eBS by dhmulia
208 --
209 -- Added For Bug 4649610
210 -- ---------------------------------------------------------------------------
211 -- |---------------------< return_legislation_code >-------------------------|
212 -- ---------------------------------------------------------------------------
213 -- {Start Of Comments}
214 --
215 -- Description:
216 -- Return the legislation code for a specific primary key value
217 --
218 -- Prerequisites:
219 -- The primary key identified by p_booking_id
220 -- already exists.
221 --
222 -- In Arguments:
223 -- p_booking_id
224 --
225 --
226 -- Post Success:
227 -- The business group's legislation code will be returned.
228 --
229 -- Post Failure:
230 -- An error is raised if the value does not exist.
231 --
232 -- Access Status:
233 -- Internal Development Use Only.
234 --
235 -- {End Of Comments}
236 -- ---------------------------------------------------------------------------
237 FUNCTION return_legislation_code
238 (p_booking_id in number
239 ) RETURN varchar2
240 Is
241 --
242 -- Declare cursor
243 --
244 cursor csr_leg_code is
245 select pbg.legislation_code
246 from per_business_groups_perf pbg
247 , ota_delegate_bookings tdb
248 where tdb.booking_id = p_booking_id
249 and pbg.business_group_id = tdb.business_group_id;
250 --
251 -- Declare local variables
252 --
253 l_legislation_code varchar2(150);
254 l_proc varchar2(72) := g_package||'return_legislation_code';
255 --
256 Begin
257 --
258 hr_utility.set_location('Entering:'|| l_proc, 10);
259 --
260 -- Ensure that all the mandatory parameter are not null
261 --
262 hr_api.mandatory_arg_error
263 (p_api_name => l_proc
264 ,p_argument => 'booking_id'
265 ,p_argument_value => p_booking_id
266 );
267 --
268 if ( nvl(ota_tdb_bus.g_booking_id, hr_api.g_number)
269 = p_booking_id) then
270 --
271 -- The legislation code has already been found with a previous
272 -- call to this function. Just return the value in the global
273 -- variable.
274 --
275 l_legislation_code := ota_tdb_bus.g_legislation_code;
276 hr_utility.set_location(l_proc, 20);
277 else
278 --
279 -- The ID is different to the last call to this function
280 -- or this is the first call to this function.
281 --
282 open csr_leg_code;
283 fetch csr_leg_code into l_legislation_code;
284 --
285 if csr_leg_code%notfound then
286 --
287 -- The primary key is invalid therefore we must error
288 --
289 close csr_leg_code;
290 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
291 fnd_message.raise_error;
292 end if;
293 hr_utility.set_location(l_proc,30);
294 --
295 -- Set the global variables so the values are
296 -- available for the next call to this function.
297 --
298 close csr_leg_code;
299 ota_tdb_bus.g_booking_id := p_booking_id;
300 ota_tdb_bus.g_legislation_code := l_legislation_code;
301 end if;
302 hr_utility.set_location(' Leaving:'|| l_proc, 40);
303 return l_legislation_code;
304 end return_legislation_code;
305 -- Added For Bug 4649610
306
307 --
308 -- ----------------------------------------------------------------------------
309 -- |-------------------------------< get_event>-------------------------------|
310 -- ----------------------------------------------------------------------------
311 --
312 -- PRIVATE
313 -- Description: Get Event
314 --
315 -- Retrieves the details associated with the event required for
316 -- subsequent checks in the package and stores the values in
317 -- the global record g_event_rec
318 --
319 Procedure get_event (p_event_id in number,
320 p_record_use in varchar2 ) is
321 --
322 l_proc varchar2(72) := g_package||'get_event';
323 l_event_exists boolean;
324 --
325 Begin
326 hr_utility.set_location('Entering:'||l_proc, 5);
327 --
328 -- check that the details have not already been selected
329 --
330 if p_record_use = 'SAME EVENT' and
331 g_event_rec.event_id is not null and
332 g_event_rec.event_id = p_event_id then
333 --
334 Return;
335 --
336 end if;
337 --
338 ota_evt_shd.get_event_details (p_event_id,
339 g_event_rec,
340 l_event_exists);
341 --
342 if not l_event_exists then
343 --
344 fnd_message.set_name ('OTA', 'OTA_13202_GEN_INVALID_KEY');
345 fnd_message.set_token ('TABLE_NAME', 'OTA_EVENTS');
346 fnd_message.set_token ('COLUMN_NAME', 'EVENT_ID');
347 fnd_message.raise_error;
348 --
349 end if;
350 --
351 hr_utility.set_location(' Leaving:'||l_proc, 10);
352 End get_event;
353 --
354 --
355 -- ----------------------------------------------------------------------------
356 -- |------------------------------< reset_event >-----------------------------|
357 -- ----------------------------------------------------------------------------
358 --
359 -- PRIVATE
360 -- Description: Reset Event
361 --
362 -- Ensures that the event record is refreshed by resetting the
363 -- indicator on the global event record
364 --
365 Procedure reset_event is
366 --
367 l_proc varchar2(72) := g_package||'reset_event';
368 --
369 Begin
370 hr_utility.set_location('Entering:'||l_proc, 5);
371 --
372 g_event_rec.event_id := '';
373 --
374 hr_utility.set_location(' Leaving:'||l_proc, 10);
375 End reset_event;
376 --
377 -- ----------------------------------------------------------------------------
378 -- |--------------------------< booking_status_type >-------------------------|
379 -- ----------------------------------------------------------------------------
380 --
381 -- PRIVATE
382 -- Description: Booking Status Type
383 --
384 -- Retrieves the type of booking status type based on the ID
385 --
386 Function booking_status_type (p_booking_status_type_id in number)
387 Return varchar2 is
388 --
389 -- Cursor to retrieve the type of booking status type
390 --
391 Cursor c_status_type is
392 select type
393 from ota_booking_status_types
394 where booking_status_type_id = p_booking_status_type_id;
395 --
396 --
397 l_proc varchar2(72) := g_package||'booking_status_type';
398 l_result varchar2(30);
399 --
400 Begin
401 hr_utility.set_location('Entering:'||l_proc, 5);
402 --
403 open c_status_type;
404 fetch c_status_type into l_result;
405 close c_status_type;
406 --
407 Return (l_result);
408 --
409 hr_utility.set_location(' Leaving:'||l_proc, 10);
410 End booking_status_type;
411 --
412 -- ---------------------------------------------------------------------
413 -- |-------------------< check_authorizer >-----------------------------
414 -- ---------------------------------------------------------------------
415 --
416 -- PUBLIC
417 -- Description: Check Person
418 --
419 -- Checks that a given person is active on a given date
420 --
421 procedure check_authorizer (p_person_id in number) is
422 begin
423 if p_person_id is not null then
424 if not ota_general.check_fnd_user(p_person_id) then
425 fnd_message.set_name ('OTA', 'OTA_13281_TFH_AUTHORIZER');
426 fnd_message.raise_error;
427 end if;
428 end if;
429 end;
430 -- ---------------------------------------------------------------------
431 -- |-------------------< check_person >---------------------------------
432 -- ---------------------------------------------------------------------
433 --
434 -- PUBLIC
435 -- Description: Check Person
436 --
437 -- Checks that a given person is active on a given date
438 --
439 function check_person (p_person_id in number,
440 p_date in date,
441 p_person_type in varchar2,
442 p_person_address_type in varchar2) return boolean is
443 l_return boolean;
444 l_proc varchar2(72) := g_package||'check_person';
445 l_dummy number;
446 --
447 -- cursor to perform check for internal delegates
448 --
449 cursor c_internal is
450 select 1
451 from per_all_people_f
452 where person_id = p_person_id
453 and p_date between effective_start_date and effective_end_date;
454 --
455 --
456
457 -- arkashya: bug 2652833: Replaced the cursor query to be based directly on HZ_ Tables instead of ra_ views.
458
459 cursor c_external is
460 select 1
461
462 from HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
463 HZ_RELATIONSHIPS REL,
464 HZ_CUST_ACCOUNTS ROLE_ACCT
465
466 where ACCT_ROLE.PARTY_ID = REL.PARTY_ID
467 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
468 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
469 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
470 AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
471 AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID
472 AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_person_id;
473
474
475
476
477 --
478 begin
479 hr_utility.set_location('Entering:'||l_proc,5);
480 hr_utility.set_location('Person Type '||p_person_address_type,10);
481 --
482 if p_person_type = 'Delegate' then
483 if p_person_address_type = 'INTERNAL' then
484 open c_internal;
485 fetch c_internal into l_dummy;
486 l_return := c_internal%found;
487 close c_internal;
488 elsif p_person_address_type = 'EXTERNAL' then
489 open c_external;
490 fetch c_external into l_dummy;
491 l_return := c_external%found;
492 close c_external;
493 end if;
494 --
495 else
496 l_return := ota_general.check_person (p_person_id ,p_date);
497 end if;
498 --
499 return l_return;
500 --
501 hr_utility.set_location(' Leaving'||l_proc,10);
502 --
503 end check_person;
504 --
505 --
506 -- business rules
507 --
508 -- ----------------------------------------------------------------------------
509 -- |-------------------------< get_full_name >-------------------------|
510 -- ----------------------------------------------------------------------------
511 function get_full_name (p_last_name in varchar2
512 ,p_title in varchar2
513 ,p_first_name in varchar2) return varchar2 is
514 l_full_name per_all_people_f.full_name%TYPE; --Bug 2256328
515 begin
516 if p_last_name is not null then
517 l_full_name := p_last_name||',';
518 if p_title is not null then
519 l_full_name := l_full_name || ' '||p_title;
520 end if;
521 if p_first_name is not null then
522 l_full_name := l_full_name || ' '||p_first_name;
523 end if;
524 end if;
525 return l_full_name;
526 end;
527 --
528 -- ----------------------------------------------------------------------------
529 -- |-----------------------------< get_full_name >----------------------------|
530 -- ----------------------------------------------------------------------------
531 -- version with legislative check
532 --
533 function get_full_name
534 (p_last_name in varchar2
535 ,p_title in varchar2
536 ,p_first_name in varchar2
537 ,p_legislation_code in varchar2
538 ,p_last_name_alt in varchar2
539 ,p_first_name_alt in varchar2
540 ) return varchar2
541 is
542 l_full_name per_all_people_f.full_name%TYPE;
543 begin
544 --
545 if p_legislation_code = 'JP' then
546 --
547 -- Create JP specific full name
548 --
549 if p_last_name_alt is null then
550 l_full_name := p_last_name || ' ' || p_first_name;
551 else
552 l_full_name := p_last_name_alt || ' ' || p_first_name_alt || ' / '
553 || p_last_name || ' ' || p_first_name;
554 end if;
555 else
556 if p_last_name is not null then
557 l_full_name := p_last_name||',';
558 --
559 if p_title is not null then
560 l_full_name := l_full_name || ' '||p_title;
561 end if;
562 if p_first_name is not null then
563 l_full_name := l_full_name || ' '||p_first_name;
564 end if;
565 end if;
566 end if;
567 return l_full_name;
568
569 end get_full_name;
570 --
571 -- ----------------------------------------------------------------------------
572 -- |-----------------------------< get_full_name >----------------------------|
573 -- ----------------------------------------------------------------------------
574 -- version with legislative check
575 --
576 function get_full_name
577 (p_last_name in varchar2
578 ,p_title in varchar2
579 ,p_first_name in varchar2
580 ,p_business_group_id in number
581 ,p_last_name_alt in varchar2
582 ,p_first_name_alt in varchar2
583 ) return varchar2
584 is
585 l_full_name per_all_people_f.full_name%TYPE;
586 l_legislation varchar2(30);
587 --
588 cursor cur_leg is
589 select legislation_code
590 from per_business_groups
591 where business_group_id=p_business_group_id;
592 --
593 begin
594 --
595 -- Retrieve legislation code
596 --
597 open cur_leg;
598 fetch cur_leg into l_legislation;
599 close cur_leg;
600
601 l_full_name := get_full_name
602 (p_last_name => p_last_name
603 ,p_title => p_title
604 ,p_first_name => p_first_name
605 ,p_legislation_code => l_legislation
606 ,p_last_name_alt => p_last_name_alt
607 ,p_first_name_alt => p_first_name_alt
608 );
609 return l_full_name;
610
611 end get_full_name;
612 --
613 -- ----------------------------------------------------------------------------
614 -- |-----------------------< assignment_ok >----------------------------------|
615 -- ----------------------------------------------------------------------------
616 -- Returns true if an assignment is valid
617 --
618 function assignment_ok (p_person_type in varchar2,
619 p_assignment_id in number,
620 p_event_id in number,
621 p_date_booking_placed in date) return rowid is
622 --
623 l_dummy varchar2(1);
624 l_found boolean := true;
625 l_rowid rowid := null;
626 l_person_id number := null;
627 l_asg_rowid rowid := null;
628 l_per_rowid rowid := null;
629
630 --
631 CURSOR c_student IS
632 SELECT paf.ROWID
633 FROM per_all_assignments_f paf, ota_events evt
634 WHERE paf.assignment_id = p_assignment_id
635 AND evt.event_id = p_event_id
636 AND (
637 evt.event_status = 'P'
638 AND evt.enrolment_start_date BETWEEN paf.effective_start_date
639 AND paf.effective_end_date
640 OR (
641 evt.event_type = 'PROGRAMME'
642 AND trunc(p_date_booking_placed) BETWEEN paf.effective_start_date
643 AND paf.effective_end_date)
644 OR (
645 -- Modified for Bug#3596070
646 trunc(p_date_booking_placed) --evt.course_start_date
647 BETWEEN paf.effective_start_date AND paf.effective_end_date) );
648
649 /* For Bug 1706107 */
650 /* Changed the following query to support PTU. Added the table Per_Person_type_Usages_F. */
651 /* Added the person type 'CWK' and removed 'EMP_APL' -- Enh No 2530860 */
652 CURSOR c_student_rehire IS
653 SELECT paf.ROWID
654 FROM per_all_assignments_f paf, ota_events evt ,
655 per_all_people_f ppf,
656 per_person_type_usages_f ptu,
657 per_person_types ppt
658 WHERE paf.person_id = l_person_id
659 AND ppf.person_id = l_person_id
660 AND ptu.person_id = ppf.person_id
661 AND evt.event_id = p_event_id
662 AND (
663 evt.event_status = 'P'
664 AND evt.enrolment_start_date BETWEEN paf.effective_start_date
665 AND paf.effective_end_date
666 OR (
667 evt.event_type = 'PROGRAMME'
668 AND trunc(p_date_booking_placed) BETWEEN paf.effective_start_date
669 AND paf.effective_end_date)
670 OR (
671 -- Modified for Bug#3596070
672 trunc(p_date_booking_placed) --evt.course_start_date
673 BETWEEN paf.effective_start_date AND paf.effective_end_date) )
674 AND (
675 ( evt.event_status = 'P'
676 AND evt.enrolment_start_date BETWEEN ppf.effective_start_date
677 AND ppf.effective_end_date
678 AND evt.enrolment_start_date BETWEEN ptu.effective_start_date
679 AND ptu.effective_end_date)
680 OR (
681 evt.event_type = 'PROGRAMME'
682 AND trunc(p_date_booking_placed) BETWEEN ppf.effective_start_date
683 AND ppf.effective_end_date
684 AND trunc(p_date_booking_placed) BETWEEN ptu.effective_start_date
685 AND ptu.effective_end_date)
686 OR (
687 -- Modified for Bug#3596070
688 trunc(p_date_booking_placed) --evt.course_start_date
689 BETWEEN ppf.effective_start_date AND ppf.effective_end_date
690 AND
691 -- Modified for Bug#3596070
692 trunc(p_date_booking_placed) --evt.course_start_date
693 BETWEEN ptu.effective_start_date AND ptu.effective_end_date ) )
694 AND ppt.business_group_id = ppf.business_group_id
695 AND ppf.business_group_id = paf.business_group_id
696 AND ptu.person_type_id = ppt.person_type_id
697 AND ppt.system_person_type in ('EMP','CWK','APL') ; -- Added 'APL' for 3885568
698 /* For Bug 1706107 */
699
700
701 --
702 /* For Bug 1514278 */
703 CURSOR c_contact IS
704 SELECT paf.ROWID
705 FROM per_all_assignments_f paf
706 WHERE paf.assignment_id = p_assignment_id
707 AND trunc(p_date_booking_placed) BETWEEN paf.effective_start_date
708 AND paf.effective_end_date;
709
710 /* For Bug 1706107 */
711 /* Changed the following query to support PTU. Added the table Per_Person_type_Usages_F. */
712 /* Added the person type 'CWK' and removed 'EMP_APL' */
713 CURSOR c_contact_rehire IS
714 SELECT paf.ROWID
715 FROM per_all_assignments_f paf ,
716 per_all_people_f ppf,
717 per_person_type_usages_f ptu,
718 per_person_types ppt
719 WHERE paf.person_id = l_person_id
720 AND ppf.person_id = l_person_id
721 AND ptu.person_id = ppf.person_id
722 AND trunc(p_date_booking_placed) BETWEEN paf.effective_start_date
723 AND paf.effective_end_date
724 AND trunc(p_date_booking_placed) BETWEEN ppf.effective_start_date
725 AND ppf.effective_end_date
726 AND trunc(p_date_booking_placed) BETWEEN ptu.effective_start_date
727 AND ptu.effective_end_date
728 AND ppt.business_group_id = ppf.business_group_id
729 AND ppf.business_group_id = paf.business_group_id
730 AND ptu.person_type_id = ppt.person_type_id
731 AND ppt.system_person_type in ('EMP','CWK','APL') ; -- Added 'APL' for 3885568
732
733
734
735 Cursor c_assignment is
736 select person_id ,rowid
737 from per_all_assignments_f
738 where assignment_id = p_assignment_id ;
739 /* For Bug 1706107 */
740
741
742
743 --
744 BEGIN
745 --
746 IF p_assignment_id IS NULL
747 OR p_event_id IS NULL
748 OR p_date_booking_placed IS NULL THEN
749 RETURN (NULL);
750 ELSE
751 IF p_person_type = 'STUDENT' THEN
752 OPEN c_student;
753 FETCH c_student INTO l_rowid;
754 CLOSE c_student;
755 if l_rowid is null then
756 OPEN c_assignment;
757 FETCH c_assignment into l_person_id, l_asg_rowid;
758 CLOSE c_assignment;
759 if l_person_id is not null then
760 OPEN c_student_rehire;
761 FETCH c_student_rehire INTO l_per_rowid;
762 CLOSE c_student_rehire;
763 end if;
764 if l_per_rowid is not null then
765 l_rowid := l_asg_rowid;
766 end if;
767 end if;
768
769 ELSIF p_person_type = 'CONTACT' THEN
770 OPEN c_contact;
771 FETCH c_contact INTO l_rowid;
772 CLOSE c_contact;
773 if l_rowid is null then
774 OPEN c_assignment;
775 FETCH c_assignment into l_person_id, l_asg_rowid;
776 CLOSE c_assignment;
777 if l_person_id is not null then
778 OPEN c_contact_rehire;
779 FETCH c_contact_rehire INTO l_per_rowid;
780 CLOSE c_contact_rehire;
781 end if;
782 if l_per_rowid is not null then
783 l_rowid := l_asg_rowid;
784 end if;
785 end if;
786
787
788 ELSE
789 RETURN (NULL);
790 END IF;
791 RETURN (l_rowid);
792 END IF;
793 --
794 EXCEPTION
795 WHEN OTHERS THEN
796 RETURN (NULL); --
797 end assignment_ok;
798 -- ----------------------------------------------------------------------------
799 -- |-----------------------------< check_places >-----------------------------|
800 -- ----------------------------------------------------------------------------
801 --
802 -- PUBLIC
803 -- Description: Check Places
804 --
805 -- Checks that if a delegate is specified then the number of
806 -- places should be one
807 --
808 Procedure check_places (p_delegate_person_id in number,
809 p_number_of_places in number) is
810 --
811 l_proc varchar2(72) := g_package||'check_places';
812 --
813 Begin
814 hr_utility.set_location('Entering:'||l_proc, 5);
815 --
816 -- check that if the delegate is specified then the number of places is one
817 --
818 if p_delegate_person_id is not null and p_number_of_places > 1 then
819 --
820 fnd_message.set_name ('OTA', 'OTA_13200_TDB_SINGLE_BOOKING');
821 fnd_message.raise_error;
822 --
823 end if;
824 --
825 hr_utility.set_location(' Leaving:'||l_proc, 10);
826 End check_places;
827 --
828 -- ----------------------------------------------------------------------------
829 -- |-------------------------< check_unique_booking >-------------------------|
830 -- ----------------------------------------------------------------------------
831 --
832 -- PUBLIC
833 -- Description: Check Unique Booking
834 --
835 -- Checks that the booking being made has not already been made
836 --
837 Procedure check_unique_booking (p_customer_id in number,
838 p_organization_id in number,
839 p_event_id in number,
840 p_delegate_person_id in number,
841 p_delegate_contact_id in number,
842 p_booking_id in number) is
843 --
844 l_proc varchar2(72) := g_package||'check_unique_booking';
845 l_booking number;
846 --
847 Begin
848 hr_utility.set_location('Entering:'||l_proc, 5);
849 --
850 -- only perform check if delegate person is specified i.e. the booking is
851 -- for a particular delegate
852 --
853 if p_delegate_person_id is not null then
854 --
855 --
856 -- check if the booking already exists
857 --
858 l_booking := booking_id_for (p_customer_id,
859 p_organization_id,
860 p_event_id,
861 p_delegate_person_id);
862 --
863 if l_booking is not null then
864 --
865 if l_booking <> nvl(p_booking_id, hr_api.g_number) then
866 --
867 fnd_message.set_name ('OTA','OTA_13582_DOUBLE_BOOKING');
868 fnd_message.raise_error;
869 --
870 end if;
871 --
872 end if;
873 --
874 end if;
875 --
876 if p_delegate_contact_id is not null then
877 --
878 --
879 -- check if the booking already exists
880 --
881 l_booking := booking_id_for (p_customer_id,
882 p_organization_id,
883 p_event_id,
884 p_delegate_contact_id);
885 --
886 if l_booking is not null then
887 --
888 if l_booking <> nvl(p_booking_id, hr_api.g_number) then
889 --
890 fnd_message.set_name ('OTA','OTA_13582_DOUBLE_BOOKING');
891 fnd_message.raise_error;
892 --
893 end if;
894 --
895 end if;
896 --
897 end if;
898 --
899 hr_utility.set_location(' Leaving:'||l_proc, 10);
900 End check_unique_booking;
901 --
902 -- ----------------------------------------------------------------------------
903 -- |----------------------------< check_failure >-----------------------------|
904 -- ----------------------------------------------------------------------------
905 --
906 -- PUBLIC
907 -- Description: Check Failure
908 --
909 -- Checks that the reason for failure is not specified for a
910 -- successful delegate
911 --
912 Procedure check_failure (p_failure_reason in varchar2,
913 p_successful_attendance_flag in varchar2) is
914 --
915 l_proc varchar2(72) := g_package||'check_failure';
916 --
917 Begin
918 hr_utility.set_location('Entering:'||l_proc, 5);
919 --
920 if p_failure_reason is not null and p_successful_attendance_flag = 'Y' then
921 --
922 fnd_message.set_name ('OTA', 'OTA_13466_TDB_SUCC_FAIL_EXCL');
923 fnd_message.raise_error;
924 --
925 --
926 end if;
927 --
928 hr_utility.set_location(' Leaving:'||l_proc, 10);
929 End check_failure;
930 --
931 -- ----------------------------------------------------------------------------
932 -- |----------------------------< check_internal_booking >--------------------|
933 -- ----------------------------------------------------------------------------
934 --
935 -- PUBLIC
936 -- Description: Check Internal Booking
937 --
938 -- Checks that if the internal booking flag is checked that the
939 -- max number of internal places is not exceeded.
940 --
941 Procedure check_internal_booking (p_event_id in number,
942 p_number_of_places in number,
943 p_booking_id in number) is
944 --
945 l_proc varchar2(72) := g_package||'check_internal_booking';
946 l_max_internal number;
947 l_number_taken number;
948 --
949 -- Check if a maximum for internal students exists.
950 --
951 cursor c_max_internal is
952 select maximum_internal_attendees
953 from ota_events
954 where event_id = p_event_id;
955 --
956 -- Only placed or attended places take an internal place
957 --
958 cursor c_places_taken is
959 select sum(a.number_of_places)
960 from ota_delegate_bookings a,
961 ota_booking_status_types b
962 where a.event_id = p_event_id
963 and a.booking_status_type_id = b.booking_status_type_id
964 and b.type in ('P','A','E') --6683076.Added new enrollment status.
965 and a.internal_booking_flag = 'Y'
966 and a.booking_id <> nvl(p_booking_id, hr_api.g_number);
967 --
968 Begin
969 --
970 hr_utility.set_location('Entering:'||l_proc, 5);
971 --
972 open c_max_internal;
973 --
974 fetch c_max_internal into l_max_internal;
975 --
976 close c_max_internal;
977 --
978 -- If max internal is null then we can enroll freely without worrying
979 -- about limits on the event.
980 --
981 if l_max_internal is not null then
982 --
983 -- Check how many places we want to allocate are available as
984 -- internal places.
985 --
986 open c_places_taken;
987 --
988 fetch c_places_taken into l_number_taken;
989 --
990 close c_places_taken;
991
992 if l_number_taken is null then
993 --
994 l_number_taken := 0;
995 --
996 end if;
997
998 --
999 -- Check if number of places available is exceeded by number required
1000 --
1001 if p_number_of_places > (l_max_internal - l_number_taken) then
1002 --
1003 fnd_message.set_name ('OTA','OTA_13580_MAX_INT_EXCEEDED');
1004 fnd_message.raise_error;
1005 --
1006 end if;
1007 --
1008 end if;
1009 --
1010 hr_utility.set_location(' Leaving:'||l_proc, 10);
1011 End check_internal_booking;
1012 --
1013 -- ----------------------------------------------------------------------------
1014 -- |---------------------------< check_attendance >---------------------------|
1015 -- ----------------------------------------------------------------------------
1016 --
1017 -- PUBLIC
1018 -- Description: Check Attendance
1019 --
1020 -- Checks that successful attendance is only valid for confirmed
1021 -- bookings
1022 --
1023 Procedure check_attendance (p_successful_attendance_flag in varchar2,
1024 p_booking_status_type_id in number) is
1025 --
1026 l_proc varchar2(72) := g_package||'check_attendance';
1027 --
1028 Begin
1029 hr_utility.set_location('Entering:'||l_proc, 5);
1030 --
1031 if p_successful_attendance_flag = 'Y' then
1032 --
1033 if booking_status_type (p_booking_status_type_id) not in
1034 (g_attended_booking,g_cancelled_booking)
1035 then
1036 --
1037 fnd_message.set_name ('OTA', 'OTA_13237_TDB_SUCCESS_CONFIRM');
1038 fnd_message.raise_error;
1039 --
1040 end if;
1041 --
1042 end if;
1043 --
1044 hr_utility.set_location(' Leaving:'||l_proc, 10);
1045 End check_attendance;
1046 --
1047 -- ----------------------------------------------------------------------------
1048 -- |------------------------< check_status_date_change >----------------------|
1049 -- ----------------------------------------------------------------------------
1050 --
1051 -- PRIVATE
1052 -- Description: Check Status Date Change
1053 --
1054 -- Checks that the status of the booking is not updated prior to
1055 -- an existing status change
1056 --
1057 Procedure check_status_date_change (p_date_status_changed in date,
1058 p_previous_status_change in date) is
1059 --
1060 l_proc varchar2(72) := g_package||'check_status_date_change';
1061 --
1062 Begin
1063 hr_utility.set_location('Entering:'||l_proc, 5);
1064 --
1065 -- do not allow update if status has been changed after the session date
1066 --
1067 if p_date_status_changed < p_previous_status_change then
1068 --
1069 fnd_message.set_name ('OTA', 'OTA_13252_TDB_FUTURE_STATUS');
1070 fnd_message.raise_error;
1071 --
1072 --
1073 end if;
1074 --
1075 hr_utility.set_location(' Leaving:'||l_proc, 10);
1076 End check_status_date_change;
1077 --
1078 --
1079 -- ----------------------------------------------------------------------------
1080 -- |------------------------< add_current_time >------------------------------|
1081 -- ----------------------------------------------------------------------------
1082 --
1083 -- PRIVATE
1084 -- Description: Add Current Time
1085 --
1086 -- Adds the current time on to a date value if it does not
1087 -- have a time component
1088 --
1089 Function add_current_time (p_date in date) return date is
1090 --
1091 l_proc varchar2(72) := g_package||'add_current_time';
1092 --
1093 Begin
1094 hr_utility.set_location('Entering:'||l_proc, 5);
1095 --
1096 hr_utility.set_location(' Leaving:'||l_proc, 10);
1097 --
1098 if to_char(p_date,'HH24:MI:SS') = '00:00:00' then
1099 --
1100 return to_date(to_char(p_date,'DD-MON-YYYY')||' '||
1101 to_char(sysdate, 'HH24:MI:SS'),
1102 'DD-MON-YYYY HH24:MI:SS');
1103 --
1104 else
1105 --
1106 return p_date;
1107 --
1108 end if;
1109 --
1110 End add_current_time;
1111 --
1112 -- ----------------------------------------------------------------------------
1113 -- |---------------------< maintain_status_history >--------------------------|
1114 -- ----------------------------------------------------------------------------
1115 --
1116 -- PRIVATE
1117 -- Description: Maintain Status History
1118 --
1119 -- Maintains a history of status changes for the booking when the
1120 -- booking status type is updated
1121 --
1122 Procedure maintain_status_history (p_booking_status_type_id in number,
1123 p_date_status_changed in date,
1124 p_administrator in number,
1125 p_status_change_comments in varchar2,
1126 p_booking_id in number,
1127 p_previous_status_change in date,
1128 p_previous_status_type_id in number,
1129 p_created_by in number,
1130 p_date_booking_placed in date) is
1131 --
1132 l_proc varchar2(72) := g_package||'maintain_status_history';
1133 l_date_changed date;
1134 --
1135 Begin
1136 hr_utility.set_location('Entering:'||l_proc, 5);
1137 --
1138 -- add the time component on to the changed date
1139 --
1140 l_date_changed := add_current_time (p_date_status_changed);
1141 --
1142 -- check that the status has not already been updated in a future session
1143 --
1144 check_status_date_change (l_date_changed,
1145 p_previous_status_change);
1146 --
1147 -- create an initial record in the status histories for the first booking
1148 -- if this is the first change in status
1149 --
1150 if p_previous_status_change is null then
1151 --
1152 insert into ota_booking_status_histories
1153 (booking_id,
1154 booking_status_type_id,
1155 start_date,
1156 changed_by,
1157 comments,
1158 object_version_number)
1159 values (p_booking_id,
1160 p_previous_status_type_id,
1161 p_date_booking_placed,
1162 p_created_by,
1163 'Enrolled',
1164 1);
1165 --
1166 end if;
1167 --
1168 -- create a record in the status histories for this booking
1169 --
1170 insert into ota_booking_status_histories
1171 (booking_id,
1172 booking_status_type_id,
1173 start_date,
1174 changed_by,
1175 comments,
1176 object_version_number)
1177 values
1178 (p_booking_id,
1179 p_booking_status_type_id,
1180 l_date_changed,
1181 p_administrator,
1182 p_status_change_comments,
1183 1);
1184 --
1185 --
1186 hr_utility.set_location(' Leaving:'||l_proc, 10);
1187 End maintain_status_history;
1188 --
1189 --
1190 -- ----------------------------------------------------------------------------
1191 -- |--------------------------< check_resources >-----------------------------|
1192 -- ----------------------------------------------------------------------------
1193 --
1194 -- PUBLIC
1195 -- Description: Check resources
1196 --
1197 -- Checks if any resources exists for the booking
1198 --
1199 Procedure check_resources (p_booking_id in number) is
1200 --
1201 -- cursor to check if resources exist for the booking
1202 --
1203 Cursor c_details is
1204 select 'X'
1205 from ota_resource_allocations
1206 where booking_id = p_booking_id;
1207 --
1208 l_proc varchar2(72) := g_package||'check_resources';
1209 l_dummy varchar2(1);
1210 --
1211 Begin
1212 hr_utility.set_location('Entering:'||l_proc, 5);
1213 --
1214 open c_details;
1215 fetch c_details into l_dummy;
1216 if c_details%found then
1217 --
1218 close c_details;
1219 -- bug 4499950
1220 fnd_message.set_name ('OTA', 'OTA_443818_NO_DEL_HAS_CHILD');
1221 fnd_message.raise_error;
1222 --
1223 --
1224 end if;
1225 --
1226 close c_details;
1227 --
1228 hr_utility.set_location(' Leaving:'||l_proc, 10);
1229 End check_resources;
1230 --
1231 --
1232 -- ----------------------------------------------------------------------------
1233 -- |--------------------------< check_finance_line >-----------------------------|
1234 -- ----------------------------------------------------------------------------
1235 --
1236 -- PUBLIC
1237 -- Description: Check finance_line
1238 --
1239 -- Checks if any finance lines exists for the booking which
1240 --
1241 Procedure check_finance_lines (p_booking_id in number) is
1242 --
1243 cursor c_check_finance_line is
1244 select nvl(sum(booking_id),0)
1245 from ota_finance_lines tfl
1246 where tfl.booking_id = p_booking_id;
1247 --
1248 l_finance_line_exists number;
1249 --
1250 l_proc varchar2(72) := g_package||'check_finance_lines';
1251 --
1252 begin
1253 --
1254 hr_utility.set_location('Entering:'|| l_proc, 5);
1255 --
1256 -- Check if finance line exists.
1257 --
1258 open c_check_finance_line;
1259 fetch c_check_finance_line into l_finance_line_exists;
1260 close c_check_finance_line;
1261 --
1262 hr_utility.set_location('Leaving:'|| l_proc, 10);
1263 --
1264 if l_finance_line_exists <> 0 then
1265 --
1266 fnd_message.set_name ('OTA', 'OTA_13609_TDB_CHK_TFL');
1267 fnd_message.raise_error;
1268 --
1269 end if;
1270 --
1271 hr_utility.set_location(' Leaving:'||l_proc, 10);
1272 --
1273 End check_finance_lines;
1274 --
1275 --
1276 -- ----------------------------------------------------------------------------
1277 -- |---------------------< check_training_plan_costs>------------------------|
1278 -- ----------------------------------------------------------------------------
1279 --
1280 -- PUBLIC
1281 -- Description: Check training plan costs
1282 --
1283 -- Checks if booking id is referenced in ota_training_plan_costs
1284 --
1285 Procedure check_training_plan_costs(p_booking_id in number) is
1286 --
1287 l_proc varchar2(72) := g_package||'check_training_plan_costs';
1288 v_exists varchar2(5) := 'N';
1289 --
1290 -- sql statment to check if the booking is referenced in training plan costs
1291 --
1292 Cursor Csr_chk_tpc_rows(p_booking_id Number) IS
1293 select 'Y'
1294 from OTA_TRAINING_PLAN_COSTS
1295 where booking_id = p_booking_id;
1296 --
1297 begin
1298 --
1299 hr_utility.set_location('Entering:'|| l_proc, 5);
1300 --
1301 -- Check if booking is referenced in training plan costs
1302 open Csr_chk_tpc_rows(p_booking_id);
1303 fetch Csr_chk_tpc_rows into v_exists;
1304 close Csr_chk_tpc_rows;
1305 --
1306 if v_exists = 'Y' then
1307 fnd_message.set_name ('OTA', 'OTA_13822_TBD_NO_DEL_TPC_EXIST');
1308 fnd_message.raise_error;
1309 end if;
1310 --
1311 hr_utility.set_location('Leaving:' || l_proc, 10);
1312 --
1313 End check_training_plan_costs;
1314 --
1315 -- ----------------------------------------------------------------------------
1316 -- |----------------------< check_type_business_group >-----------------------|
1317 -- ----------------------------------------------------------------------------
1318 --
1319 -- PUBLIC
1320 -- Description: Check Type Business Group
1321 --
1322 -- Checks that the business group of the booking is the same as
1323 -- that of the booking status type being used
1324 --
1325 Procedure check_type_business_group (p_business_group_id in number,
1326 p_booking_status_type_id in number) is
1327 --
1328 --
1329 -- cursor to check that the event is in the same business group
1330 --
1331 Cursor c_same_business_group is
1332 select 'X'
1333 from ota_booking_status_types
1334 where booking_status_type_id = p_booking_status_type_id
1335 and business_group_id = p_business_group_id;
1336 --
1337 l_proc varchar2(72) := g_package||'check_type_business_group';
1338 l_dummy varchar2(1);
1339 --
1340 Begin
1341 hr_utility.set_location('Entering:'||l_proc, 5);
1342 --
1343 open c_same_business_group;
1344 fetch c_same_business_group into l_dummy;
1345 if c_same_business_group%notfound then
1346 --
1347 fnd_message.set_name ('OTA', 'OTA_13592_BUS_GROUP_DEL_BST');
1348 fnd_message.raise_error;
1349 --
1350 --
1351 end if;
1352 --
1353 close c_same_business_group;
1354 --
1355 hr_utility.set_location(' Leaving:'||l_proc, 10);
1356 End check_type_business_group;
1357 --
1358 --
1359 -- ----------------------------------------------------------------------------
1360 -- |------------------------------< get_event_type >--------------------------|
1361 -- ----------------------------------------------------------------------------
1362 --
1363 -- PUBLIC
1364 -- Description: Get Event Type
1365 --
1366 -- Returns the event_type of the booking
1367 --
1368 Function get_event_type (p_event_id in number) return varchar2 is
1369 --
1370 l_proc varchar2(72) := g_package||'get_event_type';
1371 l_dummy varchar2(30);
1372 --
1373 cursor c1 is
1374 select event_type
1375 from ota_events
1376 where event_id = p_event_id;
1377 --
1378 Begin
1379 open c1;
1380 --
1381 fetch c1 into l_dummy;
1382 --
1383 close c1;
1384 return l_dummy;
1385 End get_event_type;
1386
1387
1388 -- ---------------------------------------------------------------------------
1389 -- |----------------------< check_event_business_group >----------------------|
1390 -- ----------------------------------------------------------------------------
1391 --
1392 -- PUBLIC
1393 -- Description: Check Event Business Group
1394 --
1395 -- Checks that the business group of the booking is the same as
1396 -- that of the event being booked
1397 --
1398 Procedure check_event_business_group
1399 (p_business_group_id in number,
1400 p_event_id in number,
1401 p_event_record_use in varchar2 ) is
1402 --
1403 l_proc varchar2(72) := g_package||'check_event_business_group';
1404 --
1405 Begin
1406 hr_utility.set_location('Entering:'||l_proc, 5);
1407 --
1408 -- retrieve event details if not already obtained
1409 --
1410 get_event (p_event_id, p_event_record_use);
1411 --
1412 if g_event_rec.business_group_id <> p_business_group_id then
1413 --
1414 fnd_message.set_name ('OTA', 'OTA_13591_BUS_GROUP_DEL_EVT');
1415 fnd_message.raise_error;
1416 --
1417 end if;
1418 --
1419 hr_utility.set_location(' Leaving:'||l_proc, 10);
1420 End check_event_business_group;
1421 --
1422 --
1423 -- ----------------------------------------------------------------------------
1424 -- |----------------------------< booking_id_for >----------------------------|
1425 -- ----------------------------------------------------------------------------
1426 --
1427 -- PUBLIC
1428 -- Description: Booking ID For
1429 --
1430 -- Returns the Booking Id for a given Organization-Event-Delegate
1431 -- combination
1432 --
1433 Function booking_id_for (p_customer_id in number,
1434 p_organization_id in number,
1435 p_event_id in number,
1436 p_person_id in number) Return number is
1437
1438 l_proc varchar2(72) := g_package||'booking_id';
1439 l_result number := null;
1440 l_type varchar2(4) := null;
1441 l_flag varchar2(1) :='F';
1442 --
1443 -- cursor to select the booking ID
1444 --
1445 Cursor c_booking_customer is
1446 select booking_id
1447 from ota_delegate_bookings
1448 where event_id = p_event_id
1449 and delegate_contact_id = p_person_id;
1450 --
1451 Cursor c_booking_organization is
1452 select booking_id
1453 from ota_delegate_bookings
1454 where event_id = p_event_id
1455 and delegate_person_id = p_person_id;
1456
1457 /** Created for Bug 1576558 **/
1458 cursor c_booking_customer_cancelled is
1459 select bst.type
1460 from ota_delegate_bookings tdb,
1461 ota_booking_status_types bst
1462 where tdb.booking_id = l_result
1463 and bst.booking_status_type_id = tdb.booking_status_type_id;
1464 /** End Created for Bug 1576558 **/
1465
1466 /** Created for Bug 1823617 **/
1467 cursor c_booking_internal_cancelled is
1468 select bst.type
1469 from ota_delegate_bookings tdb,
1470 ota_booking_status_types bst
1471 where tdb.booking_id = l_result
1472 and bst.booking_status_type_id = tdb.booking_status_type_id;
1473 /** End Created for Bug 1823617 **/
1474
1475
1476 --
1477 --
1478 Begin
1479 hr_utility.set_location('Entering:'||l_proc, 5);
1480 --
1481 if p_customer_id is not null then
1482 --
1483 -- Check external enrollments
1484 --
1485 open c_booking_customer;
1486 --
1487 loop
1488 fetch c_booking_customer into l_result;
1489 exit when c_booking_customer%notfound;
1490 -- if l_result is not null then
1491
1492 /** Created for Bug 1576558 **/
1493
1494 open c_booking_customer_cancelled;
1495 fetch c_booking_customer_cancelled into l_type;
1496 if l_type = 'C' then
1497 null;
1498 else
1499 l_flag := 'T';
1500 exit; -- bug no 3008523
1501 end if;
1502 close c_booking_customer_cancelled ;
1503 -- end if;
1504 --
1505 /** Created for Bug 1576558 **/
1506
1507 end loop;
1508 close c_booking_customer;
1509 /** Created for Bug 1576558 **/
1510
1511 if l_flag = 'F' then
1512 l_result := null;
1513 end if;
1514 /** Created for Bug 1576558 **/
1515
1516 --
1517 else
1518 --
1519 -- Check internal enrollments
1520 --
1521 --open c_booking_organization;
1522 --
1523 For r_student in c_booking_organization
1524 loop
1525 l_result := r_student.booking_id;
1526 -- fetch c_booking_organization into l_result;
1527 -- exit when c_booking_organization%notfound;
1528 --
1529 /** Created for Bug 1823617 **/
1530
1531 open c_booking_internal_cancelled;
1532 fetch c_booking_internal_cancelled into l_type;
1533 if l_type = 'C' then
1534 null;
1535 else
1536 l_flag := 'T';
1537 exit; -- bug no 3008523
1538 end if;
1539 close c_booking_internal_cancelled ;
1540 -- end if;
1541 --
1542 /** Created for Bug 1823617 **/
1543
1544 end loop;
1545
1546 -- close c_booking_organization;
1547
1548 /** Created for Bug 1823617 **/
1549
1550 if l_flag = 'F' then
1551 l_result := null;
1552 end if;
1553 /** Created for Bug 1823617 **/
1554
1555 --
1556 end if;
1557 --
1558 hr_utility.set_location(' Leaving:'||l_proc, 10);
1559 --
1560 Return (l_result);
1561 --
1562 End booking_id_for;
1563 --
1564 --
1565 -- ----------------------------------------------------------------------------
1566 -- |--------------------------< Finance Line Exists >-------------------------|
1567 -- ----------------------------------------------------------------------------
1568 --
1569 -- PUBLIC
1570 -- Description: Checks whether a finance line exists for a particular booking_Id.
1571 --
1572 --
1573 Function Finance_Line_Exists (p_booking_id in number
1574 ,p_cancelled_flag in varchar2) return boolean is
1575 --
1576 cursor c_check_finance_line is
1577 select nvl(sum(booking_id),0)
1578 from ota_finance_lines tfl
1579 where tfl.booking_id = p_booking_id
1580 and tfl.cancelled_flag = p_cancelled_flag;
1581 l_finance_line_exists number;
1582 --
1583 l_proc varchar2(72) := g_package||'finance_line_exists';
1584 --
1585 begin
1586 --
1587 hr_utility.set_location('Entering:'|| l_proc, 5);
1588 --
1589 -- Check if finance line exists.
1590 --
1591 open c_check_finance_line;
1592 fetch c_check_finance_line into l_finance_line_exists;
1593 close c_check_finance_line;
1594 --
1595 hr_utility.set_location('Leaving:'|| l_proc, 10);
1596 --
1597 if l_finance_line_exists = 0 then
1598 return (false);
1599 else
1600 return (true);
1601 end if;
1602 --
1603 end Finance_Line_Exists;
1604 --
1605 --
1606 -- ----------------------------------------------------------------------------
1607 -- |--------------------------< internal_booking >----------------------------|
1608 -- ----------------------------------------------------------------------------
1609 --
1610 -- PUBLIC
1611 -- Description: Internal Booking
1612 --
1613 -- Checks if the booking is internal then the person (Contact or
1614 -- Delegate) is also internal
1615 --
1616 Function internal_booking (p_internal_booking_flag in varchar2,
1617 p_person_id in number,
1618 p_date_booking_placed in date)
1619 Return boolean is
1620 --
1621 l_proc varchar2(72) := g_package||'internal_booking';
1622 --
1623 Begin
1624 hr_utility.set_location('Entering:'||l_proc, 5);
1625 --
1626 hr_utility.set_location(' Leaving:'||l_proc, 10);
1627 --
1628 -- if the booking is internal
1629 --
1630 if p_person_id is not null and p_internal_booking_flag = 'Y' then
1631 --
1632 -- the person must be a current employee on the date the booking is made
1633 --
1634 Return (ota_general.check_current_employee (p_person_id,
1635 p_date_booking_placed));
1636 else
1637 --
1638 Return (True);
1639 --
1640 end if;
1641 --
1642 End internal_booking;
1643 --
1644 -- ----------------------------------------------------------------------------
1645 -- |-----------------------< check_booking_status_type >----------------------|
1646 -- ----------------------------------------------------------------------------
1647 --
1648 -- PUBLIC
1649 -- Description: Check Booking Status Type
1650 --
1651 -- Chrecks that the booking status type is valid
1652 --
1653 Procedure check_booking_status_type (p_booking_status_type_id in number,
1654 p_event_id number) is -- bug 3677661
1655 --
1656 l_proc varchar2(72) := g_package||'check_booking_status_type';
1657 l_status ota_booking_status_types.name%type; -- bug 3677661
1658 l_validate_event NUMBER; -- bug 3677661
1659 --
1660 CURSOR csr_course_not_in_future -- bug 3677661
1661 IS
1662 SELECT 1
1663 FROM ota_events e
1664 WHERE
1665 -- Added for bug#5169098
1666 ota_timezone_util.convert_date(trunc(sysdate), to_char(sysdate,'HH24:MI'), ota_timezone_util.get_server_timezone_code, e.timezone)
1667 >= ota_timezone_util.convert_date(nvl(e.course_start_date,to_date('0001/01/01','YYYY/MM/DD')), course_start_time, e.timezone, e.timezone)
1668 --(sysdate >= nvl( e.course_start_date, sysdate))
1669 AND e.event_id = p_event_id;
1670
1671 --
1672
1673 CURSOR csr_booking_sts --- bug 3677661
1674 IS
1675 SELECT name
1676 FROM ota_booking_status_types bst
1677 WHERE bst.booking_status_type_id = p_booking_status_type_id
1678 AND bst.type in ('A','E'); --- bug 9009925
1679
1680 --
1681 Begin
1682 hr_utility.set_location('Entering:'||l_proc, 5);
1683 --
1684 if booking_status_type (p_booking_status_type_id) is null then
1685 --
1686 fnd_message.set_name ('OTA','OTA_13623_TDB_NO_STATUS');
1687 fnd_message.raise_error;
1688 --
1689 else
1690 /* bug 3677661 */
1691
1692 open csr_course_not_in_future ;
1693 fetch csr_course_not_in_future into l_validate_event;
1694 close csr_course_not_in_future ;
1695 --
1696 if l_validate_event is null then
1697 open csr_booking_sts;
1698 fetch csr_booking_sts into l_status;
1699 close csr_booking_sts;
1700 --
1701 if l_status is not null then
1702 fnd_message.set_name('OTA','OTA_443469_TDB_ATTENDED_STATUS');
1703 fnd_message.set_token('STATUS', l_status);
1704 fnd_message.raise_error;
1705
1706 end if;
1707 end if;
1708 /* bug 3677661 */
1709 --
1710 end if;
1711 --
1712 hr_utility.set_location(' Leaving:'||l_proc, 10);
1713 End check_booking_status_type;
1714 --
1715 -- ----------------------------------------------------------------------------
1716 -- |------------------------< check_delegate_eligible >-----------------------|
1717 -- ----------------------------------------------------------------------------
1718 --
1719 -- PUBLIC
1720 -- Description: Check Delegate Eligible
1721 --
1722 -- If the event is not public, only delegates from organizations
1723 -- which have an association with the event are eligible
1724 --
1725 Procedure check_delegate_eligible (p_event_id in number,
1726 p_customer_id in number,
1727 p_delegate_contact_id in number,
1728 p_organization_id in number,
1729 p_delegate_person_id in number,
1730 p_delegate_assignment_id in number) is
1731 --
1732 l_proc varchar2(72) := g_package||'check_delegate_eligible';
1733 l_price_basis ota_events.price_basis%type;
1734 l_start_date ota_events.course_start_date%type;
1735 l_dummy varchar2(1);
1736 l_organization_id number;
1737 l_job_id number;
1738 l_position_id number;
1739 l_count number;
1740 l_found boolean := false;
1741 /* bug 3463908 */
1742 l_party_id number := null;
1743 l_public_event_flag OTA_EVENTS.public_event_flag%TYPE;
1744 l_max_internal OTA_EVENTS.maximum_internal_attendees%TYPE;
1745 l_event_start_date OTA_EVENTS.course_start_date%TYPE;
1746 l_parent_offering_id OTA_EVENTS.parent_offering_id%TYPE;
1747 -- l_employee_can_enroll VArchar2(9);
1748 /* bug 3463908 */
1749 l_learner_can_enroll Varchar2(9); -- bug no 4201444
1750 --
1751 Cursor c_associations_exist is
1752 select count(event_association_id)
1753 from ota_event_associations
1754 where event_id = p_event_id;
1755 --
1756 /* bug 3463908 */
1757 /*Cursor c_event_start_date is
1758 select course_start_date
1759 from ota_events
1760 where event_id = p_event_id;
1761 */
1762 --
1763 Cursor c_not_public_course is
1764 select 'X'
1765 from ota_events b
1766 where b.event_id = p_event_id
1767 and public_event_flag = 'N';
1768 --
1769 Cursor c_event_association (l_party_id in number) is
1770 select 'X'
1771 from ota_event_associations
1772 where (event_id = p_event_id
1773 and ((p_customer_id is not null and customer_id = p_customer_id)
1774 or (l_party_id is not null and party_id =l_party_id))); /* bug 3463908 */
1775
1776 /* bug 4887325
1777 or not exists (select null
1778 from ota_event_associations evt
1779 where evt.event_id = p_event_id);*/
1780 --
1781 /* bug 3463908 */
1782 /* cursor c_assignment_details is
1783 select organization_id, job_id, position_id
1784 from per_assignments_f
1785 where assignment_id = p_delegate_assignment_id
1786 and NVL(l_start_date,TRUNC(sysdate))
1787 between effective_start_date
1788 and effective_end_date;
1789 --
1790 */ /* 3632386 */ /*
1791 cursor c_organization_association (l_organization_id number,
1792 l_job_id number,
1793 l_position_id number) is
1794 select 'Y'
1795 from ota_event_associations
1796 where (event_id = p_event_id
1797 and nvl(organization_id,-1) = decode(organization_id,null,-1,nvl(l_organization_id,-1))
1798 and nvl(position_id,-1) = decode(position_id,null,-1,nvl(l_position_id,-1))
1799 and nvl(job_id,-1) = decode(job_id,null,-1,nvl(l_job_id,-1)))
1800 or not exists (select null
1801 from ota_event_associations evt
1802 where evt.event_id = p_event_id);
1803 --
1804 Cursor c_event_price_basis is
1805 select price_basis
1806 from ota_events
1807 where event_id = p_event_id;
1808 */
1809 --
1810 /* bug 3463908 */
1811 Cursor c_party is
1812 SELECT party.party_id
1813 FROM HZ_CUST_ACCOUNT_ROLES acct_role,
1814 HZ_PARTIES party,
1815 HZ_RELATIONSHIPS rel,
1816 HZ_ORG_CONTACTS org_cont,
1817 HZ_PARTIES rel_party,
1818 HZ_CUST_ACCOUNTS role_acct
1819 WHERE acct_role.party_id = rel.party_id
1820 AND acct_role.role_type = 'CONTACT'
1821 AND org_cont.party_relationship_id = rel.relationship_id
1822 AND rel.subject_id = party.party_id
1823 AND rel.party_id = rel_party.party_id
1824 AND rel.subject_table_name = 'HZ_PARTIES'
1825 AND rel.object_table_name = 'HZ_PARTIES'
1826 AND acct_role.cust_account_id = role_acct.cust_account_id
1827 AND role_acct.party_id = rel.object_id
1828 AND ACCT_ROLE.cust_account_role_id = p_delegate_contact_id;
1829 /* bug 3463908 */
1830 --Bug No. 4201444
1831 Cursor event_details is
1832 Select public_event_flag, maximum_internal_attendees, course_start_date, parent_offering_id
1833 From ota_events
1834 Where event_id = p_event_id;
1835 --
1836 Begin
1837 hr_utility.set_location('Entering:'||l_proc, 5);
1838 --
1839 -- check if the course is not public
1840 --
1841 open c_not_public_course;
1842 fetch c_not_public_course into l_dummy;
1843 if c_not_public_course%found then
1844 l_found := true;
1845 end if;
1846 close c_not_public_course;
1847
1848 --
1849 -- See if any restictions exist in the database.
1850 --
1851 open c_associations_exist;
1852 fetch c_associations_exist into l_count;
1853 close c_associations_exist;
1854
1855 if nvl(l_count, 0) = 0 then
1856 l_found := false;
1857 end if;
1858
1859 --/* bug 3463908 */
1860 if p_delegate_contact_id is not null then
1861 Open c_party;
1862 Fetch c_party into l_party_id;
1863 Close c_party;
1864 end if;
1865 /* bug 3463908 */
1866 --
1867 -- check that the organization or customer has an association with the event
1868 --
1869 if l_found then
1870 --
1871 if p_customer_id is not null or p_delegate_contact_id is not null then /* bug 3463908 */
1872 --
1873 -- Check customer has an event association with this event
1874 --
1875 /* bug no 4201444 */
1876 if p_delegate_contact_id is not null then
1877 open event_details;
1878 fetch event_details into l_public_event_flag, l_max_internal, l_event_start_date, l_parent_offering_id;
1879 l_learner_can_enroll := ota_learner_access_util.learner_can_enroll
1880 (p_person_id => null,
1881 p_party_id => l_party_id,
1882 p_event_id => p_event_id,
1883 p_public_event_flag => l_public_event_flag ,
1884 p_max_internal => l_max_internal ,
1885 p_event_start_date => l_event_start_date ,
1886 p_parent_offering_id => l_parent_offering_id);
1887 close event_details;
1888
1889 if l_learner_can_enroll = 'N' then
1890 fnd_message.set_name ('OTA','OTA_13239_TDB_NO_EVENT_ASSOC');
1891 fnd_message.raise_error;
1892 end if;
1893 else
1894 /* Bug No. 4201444 */
1895 open c_event_association(l_party_id); /* bug 3463908 */
1896 --
1897 fetch c_event_association into l_dummy;
1898 if c_event_association%notfound then
1899 --
1900 close c_event_association;
1901 --
1902 fnd_message.set_name ('OTA','OTA_13239_TDB_NO_EVENT_ASSOC');
1903 fnd_message.raise_error;
1904 --
1905 end if;
1906 --
1907 close c_event_association;
1908 --
1909 /* bug no 3460968 */
1910 end if; --Bug no 4201444
1911
1912 -- Bug#4354377
1913 -- learner access util need not be checked if enrollment is not created
1914 -- for a specific internal learner
1915 elsif p_delegate_person_id IS NOT NULL THEN
1916 open event_details;
1917 fetch event_details into l_public_event_flag, l_max_internal, l_event_start_date, l_parent_offering_id;
1918
1919 /*
1920 Select public_event_flag, maximum_internal_attendees, course_start_date, parent_offering_id
1921 into l_public_event_flag, l_max_internal, l_event_start_date, l_parent_offering_id
1922 From ota_events
1923 Where event_id = p_event_id;
1924 */
1925 l_learner_can_enroll := ota_learner_access_util.employee_can_enroll(p_person_id => p_delegate_person_id,
1926 p_event_id => p_event_id,
1927 p_public_event_flag => l_public_event_flag ,
1928 p_max_internal => l_max_internal ,
1929 p_event_start_date => l_event_start_date ,
1930 p_parent_offering_id => l_parent_offering_id);
1931 if l_learner_can_enroll = 'N' then
1932 fnd_message.set_name ('OTA','OTA_13524_DELEGATE_ASSOCIATION');
1933 fnd_message.raise_error;
1934 end if;
1935 end if;
1936 /* bug no 3460968 */
1937 --
1938 /* bug no 3460968
1939 if p_organization_id is not null then
1940 --
1941 -- Check that job, position, organizaton matches criteria for association
1942 -- of event
1943 --
1944 if p_delegate_person_id is not null then
1945 --
1946 open c_event_start_date;
1947 --
1948 fetch c_event_start_date into l_start_date;
1949 --
1950 close c_event_start_date;
1951 --
1952 --
1953 -- Check to see a valid organization association exists
1954 --
1955 --
1956 l_found := false;
1957 --
1958 open c_assignment_details;
1959 --
1960 loop
1961 --
1962 fetch c_assignment_details into l_organization_id, l_job_id, l_position_id;
1963 exit when c_assignment_details%notfound or l_found;
1964 --
1965 -- Pass values to cursor
1966 --
1967 open c_organization_association(l_organization_id,l_job_id,l_position_id);
1968 --
1969 fetch c_organization_association into l_dummy;
1970 --
1971 if c_organization_association%found then
1972 --
1973 l_found := true;
1974 --
1975 end if;
1976 --
1977 close c_organization_association;
1978 --
1979 end loop;
1980 --
1981 close c_assignment_details;
1982 --
1983 if not l_found then
1984 --
1985 -- Display error message
1986 --
1987 fnd_message.set_name ('OTA','OTA_13524_DELEGATE_ASSOCIATION');
1988 fnd_message.raise_error;
1989 --
1990 end if;
1991 --
1992 end if;
1993 end if;
1994 Bug no 3460968 */
1995
1996 end if;
1997 --
1998 hr_utility.set_location(' Leaving:'||l_proc, 10);
1999 End check_delegate_eligible;
2000 --
2001 --
2002 --
2003 -- ----------------------------------------------------------------------------
2004 -- |--------------------------< places_for_status >---------------------------|
2005 -- ----------------------------------------------------------------------------
2006 --
2007 -- PUBLIC
2008 -- Description: Places for status
2009 --
2010 -- Returns the number of places on an event at either a given
2011 -- status type or a given status type ID
2012 -- for either ALL delegates or only INTERNAL delegates not
2013 -- counting the given booking
2014 --
2015 Function places_for_status (p_event_id in number,
2016 p_all_or_internal in varchar2,
2017 p_booking_status_type_id in number,
2018 p_status_type in varchar2 ,
2019 p_usage_type in varchar2 ,
2020 p_booking_id in number)
2021 Return number is
2022 --
2023 --
2024 -- cursor to count the number of confirmed bookings for the event to date
2025 --
2026 Cursor c_number_of_bookings is
2027 select nvl(sum(db.number_of_places),0)
2028 from ota_delegate_bookings db,
2029 ota_booking_status_types bst
2030 where bst.booking_status_type_id = nvl(p_booking_status_type_id,
2031 bst.booking_status_type_id)
2032 and bst.type = nvl(p_status_type, bst.type)
2033 and (p_usage_type is null or
2034 ota_tdb_bus.event_place_needed(bst.booking_status_type_id) = 1)
2035 and bst.booking_status_type_id = db.booking_status_type_id
2036 and (p_booking_id is null or
2037 p_booking_id is not null and db.booking_id <> p_booking_id)
2038 and db.internal_booking_flag = decode(p_all_or_internal,
2039 'INTERNAL','Y',
2040 db.internal_booking_flag)
2041 and db.event_id = p_event_id;
2042 --
2043 --
2044 l_max_bookings number;
2045 l_number_of_bookings number;
2046 -- l_proc varchar2(72) := g_package||'places_for_status';
2047 --
2048 Begin
2049 -- hr_utility.set_location('Entering:'||l_proc, 5);
2050 --
2051 -- get the number of confirmed bookings for the event
2052 --
2053 open c_number_of_bookings;
2054 fetch c_number_of_bookings into l_number_of_bookings;
2055 close c_number_of_bookings;
2056 --
2057 Return (l_number_of_bookings);
2058 --
2059 End places_for_status;
2060 --
2061 -- ----------------------------------------------------------------------------
2062 -- |---------------------------< places_allowed >-----------------------------|
2063 -- ----------------------------------------------------------------------------
2064 --
2065 -- PUBLIC
2066 -- Description: Places allowed
2067 --
2068 -- Returns the number of places allowed on an event for either
2069 -- ALL delegates or only INTERNAL delegates
2070 --
2071 Function places_allowed (p_event_id in number,
2072 p_all_or_internal in varchar2) Return number is
2073 --
2074 l_proc varchar2(72) := g_package||'places_allowed';
2075 --
2076 Begin
2077 --
2078 -- retrieve event details if not already obtained
2079 --
2080 get_event (p_event_id, 'NEW EVENT');
2081 --
2082 -- get the maximum number allowed for the event
2083 --
2084 if p_all_or_internal = 'ALL' then
2085 --
2086 Return (g_event_rec.maximum_attendees);
2087 --
2088 else
2089 --
2090 Return g_event_rec.maximum_internal_attendees;
2091 --
2092 end if;
2093 --
2094 End places_allowed;
2095 --
2096 --
2097 -- ----------------------------------------------------------------------------
2098 -- |-----------------------< check_programme_member >-------------------------|
2099 -- ----------------------------------------------------------------------------
2100 --
2101 -- PUBLIC
2102 -- Description: Check Programme Member
2103 --
2104 -- Checks that a booking made for a programme member has another
2105 -- existing booking for the programme
2106 --
2107 Procedure check_programme_member
2108 (p_event_id in number,
2109 p_customer_id in number,
2110 p_organization_id in number,
2111 p_delegate_person_id in number,
2112 p_delegate_contact_id in number,
2113 p_event_record_use in varchar2 ,
2114 p_booking_id in number ) is
2115 --
2116 -- cursor to check the existence of a booking to the parent event
2117 --
2118 cursor c_customer_parent_booking is
2119 select 'X'
2120 from ota_delegate_bookings
2121 where customer_id = p_customer_id
2122 and event_id in (select a.program_event_id
2123 from ota_program_memberships a
2124 where a.event_id = p_event_id)
2125 and (
2126 (p_booking_id is not null and booking_id <> p_booking_id
2127 )
2128 or
2129 p_booking_id is null
2130 )
2131 and (delegate_contact_id = p_delegate_contact_id
2132 or
2133 (delegate_contact_id is null and p_delegate_contact_id is null
2134 )
2135 );
2136 --
2137 cursor c_organization_parent_booking is
2138 select 'X'
2139 from ota_delegate_bookings
2140 where event_id in (select a.program_event_id
2141 from ota_program_memberships a
2142 where a.event_id = p_event_id)
2143 and (
2144 (p_booking_id is not null and booking_id <> p_booking_id
2145 )
2146 or
2147 p_booking_id is null
2148 )
2149 and (delegate_person_id = p_delegate_person_id
2150 or
2151 (delegate_person_id is null and p_delegate_person_id is null
2152 )
2153 );
2154 --
2155 -- Check if event is part of a program
2156 --
2157 cursor c_part_of_program is
2158 select null
2159 from ota_program_memberships a
2160 where a.event_id = p_event_id;
2161 --
2162 l_proc varchar2(72) := g_package||'check_programme_member';
2163 l_dummy varchar2(1);
2164 l_found boolean := false;
2165 --
2166 Begin
2167 hr_utility.set_location('Entering:'||l_proc, 5);
2168 --
2169 -- retrieve event details if not already obtained
2170 --
2171 get_event (p_event_id, p_event_record_use);
2172 --
2173 -- check only required if the event is a Programme member
2174 --
2175 hr_utility.set_location(g_event_rec.parent_event_id,5);
2176 --
2177 open c_part_of_program;
2178 --
2179 fetch c_part_of_program into l_dummy;
2180 if c_part_of_program%found then
2181 --
2182 l_found := true;
2183 --
2184 end if;
2185 --
2186 close c_part_of_program;
2187 --
2188 -- Check delegate is enrolled on parent event
2189 --
2190 hr_utility.set_location('Customer ID '||p_customer_id,10);
2191 hr_utility.set_location('Event ID '||p_event_id,10);
2192 --
2193 if l_found then
2194 --
2195 if p_customer_id is not null then
2196 --
2197 open c_customer_parent_booking;
2198 --
2199 fetch c_customer_parent_booking into l_dummy;
2200 --
2201 if c_customer_parent_booking%notfound then
2202 --
2203 close c_customer_parent_booking;
2204 --
2205 fnd_message.set_name ('OTA','OTA_13581_TDB_NO_PROGRAMME');
2206 fnd_message.raise_error;
2207 --
2208 end if;
2209 --
2210 close c_customer_parent_booking;
2211 --
2212 else
2213 --
2214 open c_organization_parent_booking;
2215 --
2216 fetch c_organization_parent_booking into l_dummy;
2217 --
2218 if c_organization_parent_booking%notfound then
2219 --
2220 close c_organization_parent_booking;
2221 --
2222 fnd_message.set_name ('OTA','OTA_13581_TDB_NO_PROGRAMME');
2223 fnd_message.raise_error;
2224 --
2225 end if;
2226 --
2227 close c_organization_parent_booking;
2228 --
2229 end if;
2230 --
2231 end if;
2232 --
2233 hr_utility.set_location(' Leaving:'||l_proc, 10);
2234 --
2235 End check_programme_member;
2236 -- ----------------------------------------------------------------------------
2237 -- |--------------------------< event_place_needed >--------------------------|
2238 -- ----------------------------------------------------------------------------
2239 --
2240 -- PUBLIC
2241 -- Description: Event Place Needed
2242 --
2243 -- Checks whether a place on an event is needed, in other words
2244 -- is it a placed or attended enrollment status
2245 --
2246 Function event_place_needed(p_booking_status_type_id in number) return number is
2247 --
2248 l_type varchar2(30);
2249 l_proc varchar2(72) := g_package||'check_max_allowance';
2250 --
2251 cursor c_event is
2252 select type
2253 from ota_booking_status_types
2254 where booking_status_type_id = p_booking_status_type_id;
2255 --
2256 begin
2257 open c_event;
2258 --
2259 fetch c_event into l_type;
2260 --
2261 close c_event;
2262 --
2263 -- Return true if enrollment requires an enrollment place
2264 --
2265 if l_type in (g_attended_booking,g_placed_booking,g_pending_evaluation_booking) then
2266 --
2267 return 1;
2268 --
2269 else
2270 --
2271 return 0;
2272 --
2273 end if;
2274 --
2275 end event_place_needed;
2276 --
2277 -- ----------------------------------------------------------------------------
2278 -- |--------------------------< check_max_allowance >-------------------------|
2279 -- ----------------------------------------------------------------------------
2280 --
2281 -- PUBLIC
2282 -- Description: Check Maximum Allowance
2283 --
2284 -- Checks if after the booking, the number for the event exceeds
2285 -- or has reached the maximum allowed for the event
2286 --
2287 Procedure check_max_allowance
2288 (p_event_id in number,
2289 p_booking_status_type_id in number,
2290 p_number_of_places in number,
2291 p_internal_booking_flag in varchar2,
2292 p_max_reached out nocopy boolean,
2293 p_max_exceeded out nocopy boolean,
2294 p_all_or_internal in varchar2 ,
2295 p_booking_id in number ) is
2296 --
2297 l_proc varchar2(72) := g_package||'check_max_allowance';
2298 l_places_used number;
2299 l_places_allowed number;
2300 l_extra_places number;
2301 --
2302 Begin
2303 hr_utility.set_location('Entering:'||l_proc, 5);
2304 --
2305 -- check that the booking being made is of confirmed status
2306 --
2307 if p_event_id is not null
2308 and ota_tdb_bus.event_place_needed(p_booking_status_type_id) = 1 then
2309 --
2310 -- retrieve the places confirmed and places allowed for the event
2311 --
2312 l_places_used := places_for_status
2313 (p_event_id => p_event_id,
2314 p_all_or_internal => p_all_or_internal,
2315 p_usage_type => 'PLACE_USED',
2316 p_booking_id => p_booking_id);
2317 --
2318 l_places_allowed := places_allowed (p_event_id, p_all_or_internal);
2319 --
2320 --
2321 -- obtain the number of extra places the current booking will generate to
2322 -- be included for the purposes of the check
2323 --
2324 if p_all_or_internal = 'ALL' then
2325 --
2326 -- all places being booked are included
2327 --
2328 l_extra_places := p_number_of_places;
2329 --
2330 else
2331 --
2332 -- check for internal bookings only
2333 --
2334 if p_internal_booking_flag = 'Y' then
2335 --
2336 -- internal bookings are included for the internal check
2337 --
2338 l_extra_places := p_number_of_places;
2339 --
2340 else
2341 -- not internal and are therefore not included in the check
2342 --
2343 l_extra_places := 0;
2344 --
2345 end if;
2346 --
2347 end if;
2348 --
2349 -- check if the number of bookings uses up all the remaining places
2350 --
2351 p_max_reached := (l_places_used + l_extra_places =
2352 l_places_allowed);
2353 --
2354 -- check that the number of bookings does not exceed the maximum
2355 --
2356 p_max_exceeded := (l_places_used + l_extra_places >
2357 l_places_allowed);
2358 --
2359 else
2360 --
2361 p_max_reached := false;
2362 p_max_exceeded := false;
2363 --
2364 end if;
2365 --
2366 hr_utility.set_location(' Leaving:'||l_proc, 10);
2367 End check_max_allowance;
2368 --
2369 --
2370 -- ----------------------------------------------------------------------------
2371 -- |------------------------------< enrolling >-------------------------------|
2372 -- ----------------------------------------------------------------------------
2373 --
2374 -- PUBLIC
2375 -- Description: Enrolling
2376 --
2377 -- Checks if the given event is enrolling
2378 --
2379 Function enrolling (p_event_id in number,
2380 p_event_record_use in varchar2 )
2381 return BOOLEAN is
2382 --
2383 l_proc varchar2(72) := g_package||'enrolling';
2384 l_parent_enrollment_sd date;
2385
2386 cursor c_get_parent is
2387 select enrolment_start_date
2388 from ota_events
2389 where event_id = nvl(g_event_rec.parent_event_id, -1);
2390 --
2391 Begin
2392 hr_utility.set_location('Entering:'||l_proc, 5);
2393 --
2394 -- retrieve event details if not already obtained
2395 --
2396 get_event (p_event_id, p_event_record_use);
2397 --
2398 hr_utility.trace(g_event_rec.event_type);
2399 hr_utility.trace(g_event_rec.course_start_date);
2400 hr_utility.trace(g_event_rec.enrolment_start_date);
2401 hr_utility.set_location('Leaving:'||l_proc,5);
2402 --
2403 if g_event_rec.event_type in ('AD HOC','DEVELOPMENT') then
2404 Return (g_event_rec.course_start_date is not null);
2405 elsif g_event_rec.event_type in ('SESSION') then
2406 --
2407 open c_get_parent;
2408 fetch c_get_parent into l_parent_enrollment_sd;
2409 close c_get_parent;
2410 return (l_parent_enrollment_sd is not null);
2411 --
2412 else
2413 Return (g_event_rec.enrolment_start_date is not null);
2414 end if;
2415 --
2416 End enrolling;
2417 --
2418 --
2419 -- ----------------------------------------------------------------------------
2420 -- |--------------------------< enrolling_on_date >---------------------------|
2421 -- ----------------------------------------------------------------------------
2422 --
2423 -- PUBLIC
2424 -- Description: Enrolling On Date
2425 --
2426 -- Checks if the given event is enrolling on the given date
2427 --
2428 Function enrolling_on_date
2429 (p_event_id in number,
2430 p_date in date,
2431 p_event_record_use in varchar2 )
2432 return BOOLEAN is
2433 --
2434 l_proc varchar2(72) := g_package||'enrolling_on_date';
2435 l_enrollment_sd date;
2436 l_enrollment_ed date;
2437 l_timezone ota_events.timezone%TYPE;
2438 l_conv_booking_date date := p_date;
2439
2440 cursor c_get_parent is
2441 select enrolment_start_date,
2442 enrolment_end_date,
2443 timezone
2444 from ota_events
2445 where event_id = nvl(g_event_rec.parent_event_id, -1);
2446 --
2447 Begin
2448 hr_utility.set_location('Entering:'||l_proc, 5);
2449 --
2450 -- retrieve event details if not already obtained
2451 --
2452 get_event (p_event_id, p_event_record_use);
2453 --
2454 if g_event_rec.event_type = 'SESSION' then
2455 --
2456 open c_get_parent;
2457 fetch c_get_parent into l_enrollment_sd,
2458 l_enrollment_ed,
2459 l_timezone;
2460 close c_get_parent;
2461 --
2462 else
2463 --
2464 l_enrollment_sd := g_event_rec.enrolment_start_date;
2465 l_enrollment_ed := g_event_rec.enrolment_end_date;
2466 l_timezone := g_event_rec.timezone;
2467 --
2468 end if;
2469
2470 IF g_event_rec.event_type IN ('SCHEDULED', 'SELFPACED') THEN
2471
2472 l_enrollment_sd := to_date( to_char(l_enrollment_sd,'YYYY/MM/DD')
2473 || ' ' || '00:00', 'YYYY/MM/DD HH24:MI');
2474 l_enrollment_ed := to_date( nvl(to_char(l_enrollment_ed,'YYYY/MM/DD'),'4712/12/31')
2475 || ' ' || '23:59', 'YYYY/MM/DD HH24:MI');
2476
2477 l_conv_booking_date := ota_timezone_util.convert_date(trunc(p_date)
2478 , to_char(p_date,'HH24:MI')
2479 , ota_timezone_util.get_server_timezone_code
2480 , l_timezone);
2481 END IF;
2482
2483 Return (l_conv_booking_date between l_enrollment_sd
2484 and nvl(l_enrollment_ed, to_date ('31/12/4712','DD/MM/YYYY')));
2485 --
2486 End enrolling_on_date;
2487 --
2488 --
2489 -- ----------------------------------------------------------------------------
2490 -- |--------------------------< check_enrollment_dates >---------------------------|
2491 -- ----------------------------------------------------------------------------
2492 --
2493 -- PUBLIC
2494 -- Description: Check whether learner can be enrolled on given date
2495 --
2496 -- Checks if the given event is enrolling on the given date
2497 --
2498 Function check_enrollment_dates
2499 (p_event_id in number,
2500 p_date in date,
2501 p_throw_error IN VARCHAR2 DEFAULT 'Y')
2502 return VARCHAR2 is
2503 --
2504 l_proc varchar2(72) := g_package||'check_enrollment_dates';
2505 l_enrollment_sd date;
2506 l_enrollment_ed date;
2507 l_timezone ota_events.timezone%TYPE;
2508 l_event_type ota_events.event_type%TYPE;
2509 l_conv_booking_date date := p_date;
2510
2511 cursor c_get_parent is
2512 select enrolment_start_date,
2513 enrolment_end_date,
2514 timezone,
2515 event_type
2516 from ota_events
2517 where event_id = p_event_id;
2518 --
2519 Begin
2520 hr_utility.set_location('Entering:'||l_proc, 5);
2521
2522 open c_get_parent;
2523 fetch c_get_parent into l_enrollment_sd,
2524 l_enrollment_ed,
2525 l_timezone,
2526 l_event_type;
2527 close c_get_parent;
2528
2529 IF l_event_type IN ('SCHEDULED', 'SELFPACED') THEN
2530
2531 l_enrollment_sd := to_date( to_char(l_enrollment_sd,'YYYY/MM/DD')
2532 || ' ' || '00:00', 'YYYY/MM/DD HH24:MI');
2533 l_enrollment_ed := to_date( nvl(to_char(l_enrollment_ed,'YYYY/MM/DD'),'4712/12/31')
2534 || ' ' || '23:59', 'YYYY/MM/DD HH24:MI');
2535
2536 l_conv_booking_date := ota_timezone_util.convert_date(trunc(p_date)
2537 , to_char(p_date,'HH24:MI')
2538 , ota_timezone_util.get_server_timezone_code
2539 , l_timezone);
2540 ELSE
2541 RETURN 'N';
2542 END IF;
2543
2544 IF (l_conv_booking_date between l_enrollment_sd
2545 and nvl(l_enrollment_ed, to_date ('31/12/4712','DD/MM/YYYY'))) THEN
2546 RETURN 'N';
2547 ELSE
2548 IF p_throw_error = 'Y' THEN
2549 fnd_message.set_name ('OTA', 'OTA_13599_EVT_VALID_BOOKINGS');
2550 fnd_message.raise_error;
2551 ELSE
2552 RETURN 'Y';
2553 END IF;
2554 END IF;
2555 --
2556 End check_enrollment_dates;
2557 --
2558 -- ----------------------------------------------------------------------------
2559 -- |-----------------------------< closed_event >-----------------------------|
2560 -- ----------------------------------------------------------------------------
2561 --
2562 -- PUBLIC
2563 -- Description: Closed Event
2564 --
2565 -- Checks if the given event is closed
2566 --
2567 Function closed_event (p_event_id in number,
2568 p_event_record_use in varchar2 )
2569 return BOOLEAN is
2570 --
2571 l_proc varchar2(72) := g_package||'closed_event';
2572 --
2573 Begin
2574 hr_utility.set_location('Entering:'||l_proc, 5);
2575 --
2576 --
2577 -- retrieve event details if not already obtained
2578 --
2579 get_event (p_event_id, p_event_record_use);
2580 --
2581 Return (g_event_rec.event_status = g_closed_event);
2582 --
2583 end closed_event;
2584 --
2585 -- ----------------------------------------------------------------------------
2586 -- |--------------------------< check_closed_event >--------------------------|
2587 -- ----------------------------------------------------------------------------
2588 --
2589 -- PUBLIC
2590 -- Description: Check Closed Event
2591 --
2592 -- Checks that the event to which the booking is being made is
2593 -- not closed
2594 --
2595 Procedure check_closed_event
2596 (p_event_id in number,
2597 p_date_booking_placed in date,
2598 p_event_record_use in varchar2 ) is
2599 --
2600 l_proc varchar2(72) := g_package||'check_closed_event';
2601 --
2602 Begin
2603 hr_utility.set_location('Entering:'||l_proc, 5);
2604 --
2605 --
2606 -- retrieve event details if not already obtained
2607 --
2608 get_event (p_event_id, p_event_record_use);
2609 --
2610 -- check if the event is closed
2611 --
2612 if closed_event (p_event_id, 'SAME EVENT') then
2613 --
2614 --
2615 fnd_message.set_name ('OTA', 'OTA_13249_TDB_CLOSED_EVENT');
2616 fnd_message.raise_error;
2617 --
2618 --
2619 --
2620 -- check if the enrolment dates of the event are given
2621 -- if not given then no bookings are allowed
2622 --
2623 elsif not enrolling (p_event_id, 'SAME EVENT') then
2624 --
2625 fnd_message.set_name ('OTA', 'OTA_13250_TDB_NO_ENROLMENT');
2626 fnd_message.raise_error;
2627 --
2628 --
2629 -- check if the booking date falls between the enrolment dates of the event
2630 --
2631 elsif not enrolling_on_date (p_event_id,p_date_booking_placed,'SAME EVENT')
2632 then
2633 --
2634 --
2635 fnd_message.set_name ('OTA', 'OTA_13583_TDB_NO_ENROLL_DATE');
2636 fnd_message.raise_error;
2637 --
2638 --
2639 end if;
2640 --
2641 hr_utility.set_location(' Leaving:'||l_proc, 10);
2642 End check_closed_event;
2643 --
2644 -- ----------------------------------------------------------------------------
2645 -- |-------------------------< get_letter_request_id >------------------------|
2646 -- ----------------------------------------------------------------------------
2647 --
2648 -- A function to create a letter request for the specified letter type.
2649 -- Enhancement 2764968.
2650 FUNCTION get_letter_request_id (p_letter_type_id in number,
2651 p_event_id in number) return number IS
2652 --
2653 Cursor c_next_id is
2654 select per_letter_requests_s.nextval
2655 from dual;
2656 --
2657 l_request_id number(15);
2658
2659 --
2660 BEGIN
2661 --
2662 -- no curent request exists
2663 --
2664 open c_next_id;
2665 fetch c_next_id into l_request_id;
2666 close c_next_id;
2667 --
2668 insert into per_letter_requests
2669 (letter_request_id
2670 ,business_group_id
2671 ,letter_type_id
2672 ,date_from
2673 ,request_status
2674 ,auto_or_manual
2675 ,event_id)
2676 select l_request_id
2677 , a.business_group_id
2678 , p_letter_type_id
2679 , sysdate
2680 , 'PENDING'
2681 , 'AUTO'
2682 , p_event_id
2683 from per_letter_types a
2684 where a.letter_type_id = p_letter_type_id;
2685 --
2686 --
2687 RETURN l_request_id;
2688 --
2689 --
2690 END get_letter_request_id;
2691 --------------------------------------------------------------------------------
2692 /***FUNCTION get_letter_request_id (p_letter_type_id in number) return number IS
2693 --
2694 Cursor c_request is
2695 select letter_request_id
2696 from per_letter_requests
2697 where letter_type_id = p_letter_type_id
2698 and request_status = 'PENDING';
2699 --
2700 Cursor c_next_id is
2701 select per_letter_requests_s.nextval
2702 from dual;
2703 --
2704 l_request_id number(15);
2705 l_proc varchar2(72) := g_package||'get_letter_request_id';
2706 --
2707 BEGIN
2708 --
2709 open c_request;
2710 fetch c_request into l_request_id;
2711 if c_request%notfound THEN
2712 --
2713 -- no curent request exists
2714 --
2715 open c_next_id;
2716 fetch c_next_id into l_request_id;
2717 close c_next_id;
2718 --
2719 insert into per_letter_requests
2720 (letter_request_id
2721 ,business_group_id
2722 ,letter_type_id
2723 ,date_from
2724 ,request_status
2725 ,auto_or_manual)
2726 select l_request_id
2727 , a.business_group_id
2728 , p_letter_type_id
2729 , sysdate
2730 , 'PENDING'
2731 , 'AUTO'
2732 from per_letter_types a
2733 where a.letter_type_id = p_letter_type_id;
2734 --
2735 end if;
2736 --
2737 close c_request;
2738 --
2739 RETURN l_request_id;
2740 --
2741 --
2742 END get_letter_request_id; ***/
2743 -------------------------------------------------------------------------------
2744 --
2745 -- ----------------------------------------------------------------------------
2746 -- |---------------------------< ota_letter_lines >---------------------------|
2747 -- ----------------------------------------------------------------------------
2748 -- procedure new code. Enhancement 2764968.
2749 --
2750 procedure ota_letter_lines (p_booking_id in number,
2751 p_booking_status_type_id in number,
2752 p_event_id in number,
2753 p_delegate_person_id in number default null) Is
2754 --added person_id parameter.Bug#2791524
2755 --
2756 l_dummy varchar2 (1);
2757 l_found boolean := true;
2758
2759 l_letter_request_id per_letter_requests.letter_request_id%TYPE;
2760 l_letter_req_id per_letter_requests.letter_request_id%TYPE;
2761 l_letter_type_id per_letter_types.letter_type_id%TYPE;
2762 l_business_group_id per_letter_types.business_group_id%TYPE;
2763 l_event_id number;
2764 --
2765
2766
2767 Cursor c_letter_requests_evt(cp_status_id in number) is
2768 select b.letter_type_id,
2769 c.letter_request_id
2770 from per_letter_gen_statuses a,
2771 per_letter_types b,
2772 per_letter_requests c
2773 where a.assignment_status_type_id = p_booking_status_type_id
2774 and b.letter_type_id = a.letter_type_id
2775 and b.generation_status_type = 'OTA_BOOKING'
2776 and c.letter_type_id = b.letter_type_id
2777 and c.event_id= p_event_id
2778 and c.request_status = 'PENDING'
2779 and c.auto_or_manual = 'AUTO'
2780 and a.enabled_flag='Y'; ---***added for bug#2791524;
2781
2782 Cursor c_letter_requests(cp_status_id in number) is
2783 select b.letter_type_id,
2784 c.letter_request_id
2785 from per_letter_gen_statuses a,
2786 per_letter_types b,
2787 per_letter_requests c
2788 where a.assignment_status_type_id = p_booking_status_type_id
2789 and b.letter_type_id = a.letter_type_id
2790 and b.generation_status_type = 'OTA_BOOKING'
2791 and c.letter_type_id = b.letter_type_id
2792 AND c.event_id IS null
2793 and c.request_status = 'PENDING'
2794 and c.auto_or_manual = 'AUTO'
2795 and a.enabled_flag='Y'; ---***added for bug#2791524;
2796 --
2797 CURSOR c_letters (cp_status_id in number) IS
2798 select a.letter_type_id
2799 from per_letter_gen_statuses a,
2800 per_letter_types b
2801 where a.assignment_status_type_id = cp_status_id
2802 and b.letter_type_id = a.letter_type_id
2803 and b.generation_status_type = 'OTA_BOOKING'
2804 and a.enabled_flag='Y'; ---***added for bug#2791524;
2805
2806 cursor c_request_exists is
2807 select null
2808 from per_letter_request_lines
2809 where ota_booking_id = p_booking_id
2810 and ota_booking_status_type_id = p_booking_status_type_id
2811 and letter_request_id = l_letter_request_id;
2812
2813 -- Added for Bug#3007934
2814 cursor csr_get_business_group_id is
2815 select business_group_id
2816 from ota_events
2817 where event_id = p_event_id;
2818 --
2819 BEGIN
2820 --Modified for Bug#3007934
2821 -- l_business_group_id := ota_general.get_business_group_id;
2822 OPEN csr_get_business_group_id;
2823 FETCH csr_get_business_group_id INTO l_business_group_id;
2824 CLOSE csr_get_business_group_id;
2825
2826 IF ( NVL(FND_PROFILE.VALUE('HR_LETTER_BY_VACANCY'), 'N') <> 'Y' ) THEN
2827 --
2828 OPEN c_letter_requests(p_booking_status_type_id);
2829 --
2830 FETCH c_letter_requests INTO l_letter_type_id,
2831 l_letter_request_id;
2832 IF c_letter_requests%NOTFOUND THEN
2833 l_event_id :=NULL; --Hr_letter_by_vacancy is No. So no event association.
2834 OPEN c_letters(p_booking_status_type_id);
2835 FETCH c_letters INTO l_letter_type_id;
2836 IF c_letters%NOTFOUND THEN
2837 l_found := FALSE;
2838 END IF;
2839 CLOSE c_letters;
2840 END IF;
2841 --
2842 CLOSE c_letter_requests;
2843 --
2844 ELSE
2845 --
2846 OPEN c_letter_requests_evt(p_booking_status_type_id);
2847 FETCH c_letter_requests_evt INTO l_letter_type_id,
2848 l_letter_request_id;
2849
2850 IF c_letter_requests_evt%notfound THEN
2851 l_event_id := p_event_id; --Hr_letter_by_vacancy is Yes. So event association.
2852 OPEN c_letters(p_booking_status_type_id);
2853 FETCH c_letters INTO l_letter_type_id;
2854 IF c_letters%NOTFOUND THEN
2855 l_found := FALSE;
2856 END IF;
2857 CLOSE c_letters;
2858 END IF;
2859 CLOSE C_LETTER_REQUESTS_EVT;
2860 --
2861 END IF;
2862
2863 IF l_found THEN
2864 IF l_letter_request_id IS NULL then
2865 l_letter_req_id := get_letter_request_id(l_letter_type_id,l_event_id);
2866 ELSE l_letter_req_id := l_letter_request_id;
2867 END IF;
2868
2869 -- Check if request exists that the request lines do not exist
2870 --
2871 open c_request_exists;
2872 --
2873 fetch c_request_exists into l_dummy;
2874 --
2875 if c_request_exists%notfound then
2876 --
2877 ----***added person_id in insert statement.Bug#2791524.
2878 insert into per_letter_request_lines
2879 (letter_request_line_id,
2880 business_group_id,
2881 letter_request_id,
2882 person_id,
2883 ota_booking_id,
2884 ota_booking_status_type_id,
2885 date_from
2886 )
2887 values
2888 (per_letter_request_lines_s.nextval,
2889 l_business_group_id,
2890 l_letter_req_id,
2891 p_delegate_person_id,
2892 p_booking_id,
2893 p_booking_status_type_id,
2894 trunc(sysdate)
2895 );
2896 --
2897 end if;
2898 --
2899 close c_request_exists;
2900 END IF;
2901
2902 END ota_letter_lines;
2903 -------------------------------------------------------------------------------
2904 /***procedure ota_letter_lines (p_booking_id in number,
2905 p_booking_status_type_id in number) Is
2906 --
2907 l_dummy varchar2 (1);
2908 l_found boolean := false;
2909 l_letter_request_id number (15);
2910 l_proc varchar2 (72) := g_package||'ota_letter_lines';
2911 --
2912 CURSOR c_letters (cp_status_id in number) IS
2913 select a.letter_type_id
2914 , a.business_group_id
2915 from per_letter_gen_statuses a
2916 , per_letter_types b
2917 where a.assignment_status_type_id = cp_status_id
2918 and b.letter_type_id = a.letter_type_id
2919 and b.generation_status_type = 'OTA_BOOKING';
2920 --
2921 cursor c_requests (p_letter_type_id in number) is
2922 select letter_request_id
2923 from per_letter_requests
2924 where letter_type_id = p_letter_type_id
2925 and request_status = 'PENDING'
2926 and auto_or_manual = 'AUTO';
2927 --
2928 cursor c_request_exists is
2929 select null
2930 from per_letter_request_lines
2931 where ota_booking_id = p_booking_id
2932 and ota_booking_status_type_id = p_booking_status_type_id
2933 and letter_request_id = l_letter_request_id;
2934 --
2935 BEGIN
2936 --
2937 FOR r_letters in c_letters(p_booking_status_type_id) LOOP
2938 --
2939 -- Check whether there is an automatic letter required and request_status
2940 -- is pending.
2941 --
2942 open c_requests(r_letters.letter_type_id);
2943 --
2944 fetch c_requests into l_letter_request_id;
2945 --
2946 if c_requests%notfound then
2947 --
2948 l_letter_request_id := get_letter_request_id(r_letters.letter_type_id);
2949 --
2950 end if;
2951 --
2952 close c_requests;
2953 --
2954 -- Check if request exists that the request lines do not exist
2955 --
2956 open c_request_exists;
2957 --
2958 fetch c_request_exists into l_dummy;
2959 --
2960 if c_request_exists%notfound then
2961 --
2962 insert into per_letter_request_lines
2963 (letter_request_line_id,
2964 business_group_id,
2965 letter_request_id,
2966 ota_booking_id,
2967 ota_booking_status_type_id,
2968 date_from
2969 )
2970 values
2971 (per_letter_request_lines_s.nextval,
2972 r_letters.business_group_id,
2973 l_letter_request_id,
2974 p_booking_id,
2975 p_booking_status_type_id,
2976 trunc(sysdate)
2977 );
2978 --
2979 end if;
2980 --
2981 close c_request_exists;
2982 --
2983 END LOOP r_letters;
2984 --
2985 END ota_letter_lines; ***/
2986 -------------------------------------------------------------------------------
2987 --
2988 -- ----------------------------------------------------------------------------
2989 -- |--------------------------< check_constraints >---------------------------|
2990 -- ----------------------------------------------------------------------------
2991 --
2992 Procedure check_constraints
2993 (
2994 p_internal_booking_flag in varchar2,
2995 p_successful_attendance_flag in varchar2
2996 ) Is
2997 --
2998 l_proc varchar2(72) := g_package||'check_constraints';
2999 --
3000 Begin
3001 hr_utility.set_location('Entering:'||l_proc, 5);
3002 --
3003 If NOT (p_internal_booking_flag in ('N', 'Y')) then
3004 hr_utility.set_message(801,'HR_7166_OBJECT_CHK_CONSTRAINT');
3005 hr_utility.set_message_token('CONSTRAINT_NAME',
3006 'OTA_TDB_INTERNAL_BOOKING_F_CHK');
3007 hr_utility.set_message_token('TABLE_NAME', 'OTA_DELEGATE_BOOKINGS');
3008 hr_utility.raise_error;
3009 End If;
3010 If NOT (p_successful_attendance_flag in ('N', 'Y')) then
3011 hr_utility.set_message(801,'HR_7166_OBJECT_CHK_CONSTRAINT');
3012 hr_utility.set_message_token('CONSTRAINT_NAME',
3013 'OTA_TDB_SUCCESSFUL_ATTENDA_CHK');
3014 hr_utility.set_message_token('TABLE_NAME', 'OTA_DELEGATE_BOOKINGS');
3015 hr_utility.raise_error;
3016 End If;
3017 --
3018 hr_utility.set_location(' Leaving:'||l_proc, 10);
3019 End check_constraints;
3020 --
3021 -- ----------------------------------------------------------------------------
3022 -- |----------------< check_program_member_enrollments >----------------------|
3023 -- ----------------------------------------------------------------------------
3024 --
3025 -- Description: Determines whether a person is enrolled onto program
3026 -- member events before their program enrollment can be
3027 -- deleted.
3028 --
3029 Procedure check_pmm_enrollments is
3030 --
3031 l_event_id number := ota_tdb_shd.g_old_rec.event_id;
3032 l_customer_id number := ota_tdb_shd.g_old_rec.customer_id;
3033 l_organization_id number := ota_tdb_shd.g_old_rec.organization_id;
3034 l_delegate_person_id number := ota_tdb_shd.g_old_rec.delegate_person_id;
3035 l_delegate_contact_id number := ota_tdb_shd.g_old_rec.delegate_contact_id;
3036 l_proc varchar2(72) := g_package||'check_pmm_enrollments';
3037 l_result number;
3038
3039 cursor c_pmm_int_enrollments is
3040 select 1
3041 from ota_delegate_bookings tdb,
3042 ota_program_memberships pmm
3043 where tdb.event_id = pmm.event_id
3044 and pmm.program_event_id = l_event_id
3045 and tdb.delegate_person_id = l_delegate_person_id;
3046
3047 cursor c_pmm_ext_enrollments is
3048 select 1
3049 from ota_delegate_bookings tdb,
3050 ota_program_memberships pmm
3051 where tdb.event_id = pmm.event_id
3052 and pmm.program_event_id = l_event_id
3053 and tdb.delegate_contact_id = l_delegate_contact_id;
3054
3055 --
3056 Begin
3057 --
3058 hr_utility.set_location('Entering:'||l_proc, 5);
3059
3060 if get_event_type(l_event_id) = 'PROGRAMME' then
3061 --
3062 if l_customer_id is not null then
3063 --
3064 open c_pmm_ext_enrollments;
3065 fetch c_pmm_ext_enrollments into l_result;
3066 if c_pmm_ext_enrollments%found then
3067 --
3068 close c_pmm_ext_enrollments;
3069 fnd_message.set_name('OTA', 'OTA_13685_TDB_PMM_EXISTS');
3070 fnd_message.raise_error;
3071 --
3072 else
3073 --
3074 close c_pmm_ext_enrollments;
3075 --
3076 end if;
3077 --
3078 elsif l_organization_id is not null then
3079 --
3080 open c_pmm_int_enrollments;
3081 fetch c_pmm_int_enrollments into l_result;
3082 if c_pmm_int_enrollments%found then
3083 --
3084 close c_pmm_int_enrollments;
3085 fnd_message.set_name('OTA', 'OTA_13685_TDB_PMM_EXISTS');
3086 fnd_message.raise_error;
3087 --
3088 else
3089 --
3090 close c_pmm_int_enrollments;
3091 --
3092 end if;
3093 --
3094 end if;
3095 --
3096 end if;
3097 --
3098 hr_utility.set_location(' Leaving:'||l_proc, 10);
3099 End check_pmm_enrollments;
3100
3101 -- ----------------------------------------------------------------------------
3102 -- |---------------------------< chk_line_id >---------------------------|
3103 -- ----------------------------------------------------------------------------
3104 Procedure chk_line_id
3105 (p_booking_id in number
3106 ,p_line_id in number
3107 ,p_org_id in number) is
3108
3109 --
3110 l_proc varchar2(72) := g_package||'chk_line_id';
3111 l_exists varchar2(1);
3112
3113 --
3114 -- cursor to check if line is exist in OE_ORDER_LINES .
3115 --
3116 cursor csr_order_line is
3117 select null
3118 from oe_order_lines_all
3119 where line_id = p_line_id;
3120
3121 Begin
3122 hr_utility.set_location('Entering:'||l_proc, 5);
3123
3124 if (((p_booking_id is not null) and
3125 nvl(ota_tdb_shd.g_old_rec.line_id,hr_api.g_number) <>
3126 nvl(p_line_id,hr_api.g_number))
3127 or (p_booking_id is null)) then
3128 --
3129 hr_utility.set_location('Entering:'||l_proc, 10);
3130 if p_line_id is not null then
3131
3132 hr_utility.set_location('Entering:'||l_proc, 15);
3133 open csr_order_line;
3134 fetch csr_order_line into l_exists;
3135 if csr_order_line%notfound then
3136 close csr_order_line;
3137 fnd_message.set_name('OTA','OTA_13888_TDB_LINE_INVALID');
3138 fnd_message.raise_error;
3139 end if;
3140 close csr_order_line;
3141 hr_utility.set_location('Entering:'||l_proc, 20);
3142 end if;
3143 end if;
3144 hr_utility.set_location('Leaving:'||l_proc, 30);
3145 end chk_line_id;
3146
3147 -- ----------------------------------------------------------------------------
3148 -- |---------------------------< chk_Order_line_exist >----------------------|
3149 -- ----------------------------------------------------------------------------
3150 Procedure chk_Order_line_exist
3151 (p_line_id in number
3152 ,p_org_id in number) is
3153
3154 --
3155 l_proc varchar2(72) := g_package||'chk_order_line_exist';
3156
3157
3158 Begin
3159 hr_utility.set_location('Entering:'||l_proc, 5);
3160
3161 if p_line_id is not null then
3162 fnd_message.set_name('OTA','OTA_13885_TDB_ORDER_LINE_EXIST');
3163 fnd_message.raise_error;
3164 hr_utility.set_location('Entering:'||l_proc, 20);
3165
3166 end if;
3167 hr_utility.set_location('Leaving:'||l_proc, 30);
3168 end chk_order_line_exist;
3169 --
3170 -- ----------------------------------------------------------------------------
3171 -- |---------------------------< chk_status_changed >----------------------|
3172 -- ----------------------------------------------------------------------------
3173 -- This procedure will check whether the status is changed. this procedure is
3174 -- called by post_update procedure and will be only used by OM integration.
3175 -- The purpose of this procedure is to cancel an order line, Create RMA and
3176 -- To notify the Workflow to continue.
3177 Procedure chk_status_changed
3178 (p_line_id in number
3179 ,p_status_type_id in number
3180 ,p_daemon_type in varchar2
3181 ,p_event_id in number
3182 ,p_booking_id in number
3183 ,p_org_id in number
3184 ) is
3185
3186 l_proc varchar2(72) := g_package||'chk_status_changed';
3187
3188 l_booking_status_changed boolean :=
3189 ota_general.value_changed (ota_tdb_shd.g_old_rec.booking_status_type_id,
3190 p_status_type_id);
3191 l_status_type ota_booking_status_types.type%type;
3192 l_old_status_type ota_booking_status_types.type%type;
3193 l_invoice_rule varchar2(80);
3194 l_exist varchar2(1);
3195 l_return boolean;
3196 l_err_num VARCHAR2(30) := '';
3197 l_err_msg VARCHAR2(1000) := '';
3198 l_dynamicSqlString VARCHAR2(2000);
3199 l_ins_status VARCHAR2(1);
3200 l_industry VARCHAR2(1);
3201 l_msg_data VARCHAR2(1000);
3202 l_event_exist varchar2(1);
3203 l_line_id number;
3204
3205 CURSOR C_RE IS
3206 Select LINE_ID
3207 FROM OTA_EVENTS
3208 WHERE EVENT_ID = p_event_id
3209 AND LINE_ID IS NOT NULL ;
3210 --
3211 Begin
3212 hr_utility.set_location('Entering:'||l_proc, 5);
3213 --
3214 IF p_line_id is not null THEN
3215 IF l_booking_status_changed THEN
3216 /*ota_utility.get_invoice_rule (
3217 p_line_id => p_line_id,
3218 p_invoice_rule => l_invoice_rule);*/
3219
3220 ota_utility.get_booking_status_type(
3221 p_status_type_id => ota_tdb_shd.g_old_rec.booking_status_type_id,
3222 p_type => l_old_status_type) ;
3223 IF p_status_type_id is not null THEN
3224 ota_utility.get_booking_status_type(
3225 p_status_type_id => p_status_type_id,
3226 p_type => l_status_type);
3227
3228 IF l_status_type = 'C' THEN
3229 IF p_daemon_type = 'W' THEN
3230
3231 BEGIN
3232 hr_utility.set_location('Entering:'||l_proc, 10);
3233
3234 ota_utility.check_invoice(
3235 p_line_id => p_line_id,
3236 p_org_id => p_org_id,
3237 p_exist => l_exist);
3238 IF fnd_installation.get(660, 660, l_ins_status, l_industry) THEN
3239 IF l_exist = 'Y' THEN
3240 BEGIN
3241 hr_utility.set_location('Entering:'||l_proc, 15);
3242 ota_om_upd_api.create_rma(p_line_id,p_org_id);
3243 --
3244 -- Start bug #1657510 Comment out exception handler
3245 --
3246 /*
3247 exception when others then
3248 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3249 hr_utility.set_message_token('PROCEDURE', l_proc);
3250 hr_utility.set_message_token('STEP','15');
3251 hr_utility.raise_error;
3252 */
3253 --
3254 -- End bug #1657510 Comment out exception handler
3255 --
3256 END;
3257 ELSE
3258 BEGIN
3259 hr_utility.set_location('Entering:'||l_proc, 20);
3260
3261 ota_om_upd_api.cancel_order(p_line_id,p_org_id);
3262 --
3263 -- Start bug #1657510 Comment out exception handler
3264 --
3265 /*
3266 exception when others then
3267 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3268 hr_utility.set_message_token('PROCEDURE', l_proc);
3269 hr_utility.set_message_token('STEP','20');
3270 hr_utility.raise_error;
3271 */
3272 --
3273 -- End bug #1657510 Comment out exception handler
3274 --
3275 END;
3276 END IF;
3277 END IF;
3278
3279
3280 END;
3281
3282 END IF;
3283 /* Fix Bug 1549427 :Remove the checked for invoicing rule */
3284 ELSIF l_status_type = 'A' THEN
3285 IF l_old_status_type in ('W','P','R') THEN
3286 l_return := ota_utility.check_wf_status(p_line_id,'WAIT_FOR_ATTENDED');
3287 IF l_return = TRUE THEN
3288 wf_engine.Completeactivity('OEOL',
3289 to_char(p_line_id),
3290 'WAIT_FOR_ATTENDED',null);
3291 END IF;
3292 END IF;
3293
3294
3295
3296 /* ELSIF l_status_type = 'P' THEN
3297 IF l_old_status_type = 'W' THEN
3298 IF l_invoice_rule = 'ADVANCED' THEN
3299 l_return := ota_utility.check_wf_status(p_line_id,'WAIT_FOR_PLACED');
3300 IF l_return = TRUE THEN
3301 wf_engine.Completeactivity('OEOL',
3302 to_char(p_line_id),
3303 'WAIT_FOR_PLACED',null);
3304 END IF;
3305 END IF;
3306 END IF;
3307 ELSIF l_status_type = 'A' THEN
3308 IF l_invoice_rule = 'ARREARS' THEN
3309 l_return := ota_utility.check_wf_status(p_line_id,'WAIT_FOR_ATTENDED');
3310 IF l_return = TRUE THEN
3311 wf_engine.Completeactivity('OEOL',
3312 to_char(p_line_id),
3313 'WAIT_FOR_ATTENDED',null);
3314 END IF;
3315 END IF; */
3316
3317 END IF;
3318 END IF;
3319 END IF;
3320 -- END IF;
3321
3322
3323 ELSIF p_line_id is null THEN
3324 IF l_booking_status_changed THEN
3325
3326 ota_utility.get_booking_status_type(
3327 p_status_type_id => ota_tdb_shd.g_old_rec.booking_status_type_id,
3328 p_type => l_old_status_type) ;
3329
3330 IF p_status_type_id is not null THEN
3331 ota_utility.get_booking_status_type(
3332 p_status_type_id => p_status_type_id,
3333 p_type => l_status_type);
3334 OPEN C_RE;
3335 FETCH C_RE INTO l_line_id;
3336 IF C_RE%FOUND THEN
3337 IF l_status_type = 'A' and l_old_status_type is not null then
3338
3339 l_return := ota_utility.check_wf_status(l_line_id,'WAIT_FOR_ENROLLMENT_ATTENDED');
3340 IF l_return = TRUE THEN
3341 wf_engine.Completeactivity('OEOL',
3342 to_char(l_line_id),
3343 'WAIT_FOR_ENROLLMENT_ATTENDED',null);
3344
3345 END IF;
3346
3347 END IF;
3348 END IF;
3349 CLOSE C_RE;
3350 END IF;
3351 END IF;
3352
3353 END IF;
3354
3355 hr_utility.set_location('Leaving:'||l_proc, 30);
3356 /*EXCEPTION WHEN OTHERS THEN
3357 l_err_num := SQLCODE;
3358 l_err_msg := SUBSTR(SQLERRM, 1, 100);
3359
3360 raise_application_error(-20001,l_err_num||': '||l_err_msg);*/
3361
3362 end chk_status_changed;
3363
3364 -- ----------------------------------------------------------------------------
3365 -- |------------------------------< check_secure_event >----------------------------------|
3366 -- ----------------------------------------------------------------------------
3367 -- Added for bug#4606760
3368 PROCEDURE check_secure_event(p_event_id IN NUMBER
3369 ,p_delegate_person_id IN NUMBER)
3370 IS
3371 CURSOR csr_is_secure_event IS
3372 SELECT organization_id
3373 FROM ota_events
3374 WHERE event_id = p_event_id
3375 AND nvl(secure_event_flag,'N') = 'Y';
3376
3377 l_organization_id OTA_EVENTS.organization_id%TYPE;
3378 l_is_match VARCHAR2(1);
3379 BEGIN
3380 OPEN csr_is_secure_event;
3381 FETCH csr_is_secure_event INTO l_organization_id;
3382 IF csr_is_secure_event%FOUND THEN
3383 l_is_match := ota_utility.check_organization_match(p_delegate_person_id,l_organization_id);
3384 IF l_is_match = 'N' THEN
3385 fnd_message.set_name('OTA','OTA_443939_SECURE_EVT_LRN_ERR');
3386 fnd_message.raise_error;
3387 END IF;
3388 END IF;
3389 CLOSE csr_is_secure_event;
3390 END check_secure_event;
3391
3392
3393 -- ----------------------------------------------------------------------------
3394 -- |------------------------------< check_online_enr_change >----------------------------------|
3395 -- ----------------------------------------------------------------------------
3396 --Added for bug#4650304
3397 PROCEDURE check_online_enr_change(
3398 p_booking_id IN NUMBER
3399 ,p_event_id IN NUMBER
3400 ,p_booking_status_type_id IN NUMBER
3401 ,p_content_player_status IN VARCHAR2
3402 ,p_delegate_person_id IN NUMBER
3403 ,p_delegate_contact_id IN NUMBER) IS
3404
3405 l_event_id OTA_DELEGATE_BOOKINGS.event_id%TYPE := p_event_id;
3406
3407 CURSOR csr_get_class_type(p_class_id NUMBER) IS
3408 SELECT oft.learning_object_id
3409 ,ctu.online_flag
3410 ,evt.offering_id
3411 FROM ota_offerings oft
3412 , ota_events evt
3413 , ota_category_usages ctu
3414 WHERE oft.offering_id = evt.parent_offering_id
3415 AND ctu.category_usage_id = oft.delivery_mode_id
3416 AND evt.event_id = p_class_id;
3417
3418 l_online_flag VARCHAR2(9) := NULL;
3419 l_lo_id ota_offerings.learning_object_id%TYPE;
3420 l_imported_off_id ota_events.offering_id%TYPE;
3421 l_bkng_status_type VARCHAR2(30);
3422 l_old_bst VARCHAR2(30);
3423
3424 l_player_status ota_delegate_bookings.content_player_status%TYPE;
3425
3426 CURSOR csr_get_performance_data(p_user_id NUMBER
3427 ,p_user_type VARCHAR2
3428 ,p_lo_id NUMBER) IS
3429 SELECT lesson_status
3430 FROM ota_performances
3431 WHERE learning_object_id = p_lo_id
3432 AND user_id = p_user_id
3433 AND user_type = p_user_type
3434 AND lesson_status IN ('P', 'C');
3435
3436 l_user_type ota_performances.user_type%TYPE;
3437 l_user_id ota_performances.user_id%TYPE;
3438 l_person_id ota_delegate_bookings.delegate_person_id%TYPE := p_delegate_person_id;
3439 l_contact_id ota_delegate_bookings.delegate_contact_id%TYPE := p_delegate_contact_id;
3440
3441 BEGIN
3442 IF p_booking_status_type_id <> hr_api.g_number THEN
3443 ota_utility.get_booking_status_type(p_booking_status_type_id,l_bkng_status_type);
3444 ota_utility.get_booking_status_type(ota_tdb_shd.g_old_rec.booking_status_type_id,l_old_bst);
3445
3446 IF l_bkng_status_type = 'A'
3447 OR l_old_bst not in('A','E') THEN RETURN; END IF;--Added for 6989133.
3448
3449 IF p_event_id = hr_api.g_number THEN
3450 l_event_id := ota_tdb_shd.g_old_rec.event_id;
3451 END IF;
3452
3453 OPEN csr_get_class_type(l_event_id);
3454 FETCH csr_get_class_type INTO l_lo_id, l_online_flag, l_imported_off_id;
3455 CLOSE csr_get_class_type;
3456
3457
3458 IF nvl(l_online_flag, 'N') = 'Y' THEN
3459 -- Add logic to check performance status and throw error acc.
3460 IF l_imported_off_id IS NOT NULL THEN
3461 -- Imported class. Performance data to be fetched from TDB table
3462 IF l_player_status = hr_api.g_varchar2 THEN
3463 l_player_status := ota_tdb_shd.g_old_rec.content_player_status;
3464 ELSE
3465 l_player_status := p_content_player_status;
3466 END IF;
3467 ELSE
3468 -- Performance to be fetched from OTA_PERFORMANCES
3469 IF l_contact_id = hr_api.g_number THEN
3470 l_contact_id := ota_tdb_shd.g_old_rec.delegate_contact_id;
3471 END IF;
3472 IF l_person_id = hr_api.g_number THEN
3473 l_person_id := ota_tdb_shd.g_old_rec.delegate_person_id;
3474 END IF;
3475
3476 IF l_person_id IS NOT NULL THEN
3477 OPEN csr_get_performance_data(l_person_id, 'E', l_lo_id);
3478 ELSIF l_contact_id IS NOT NULL THEN
3479 OPEN csr_get_performance_data(
3480 ota_utility.get_ext_lrnr_party_id(l_contact_id)
3481 ,'C'
3482 ,l_lo_id);
3483 ELSE
3484 RETURN;
3485 END IF;
3486 FETCH csr_get_performance_data INTO l_player_status;
3487 CLOSE csr_get_performance_data;
3488
3489 IF l_player_status = 'P' OR l_player_status = 'C' THEN
3490 fnd_message.set_name ('OTA','OTA_443964_TDB_STATUS_CHG_ERR');
3491 fnd_message.raise_error;
3492 END IF;
3493
3494
3495 END IF;
3496 END IF;
3497 END IF;
3498 END check_online_enr_change;
3499
3500 -- ----------------------------------------------------------------------------
3501 -- |------------------------------< chk_df >----------------------------------|
3502 -- ----------------------------------------------------------------------------
3503 --
3504 -- Description:
3505 -- Validates all the Descriptive Flexfield values.
3506 --
3507 -- Prerequisites:
3508 -- All other columns have been validated. Must be called as the
3509 -- last step from insert_validate and update_validate.
3510 --
3511 -- In Arguments:
3512 -- p_rec
3513 --
3514 -- Post Success:
3515 -- If the Descriptive Flexfield structure column and data values are
3516 -- all valid this procedure will end normally and processing will
3517 -- continue.
3518 --
3519 -- Post Failure:
3520 -- If the Descriptive Flexfield structure column value or any of
3521 -- the data values are invalid then an application error is raised as
3522 -- a PL/SQL exception.
3523 --
3524 -- Access Status:
3525 -- Internal Row Handler Use Only.
3526 --
3527 -- ----------------------------------------------------------------------------
3528 PROCEDURE chk_df
3529 (p_rec IN ota_tdb_shd.g_rec_type
3530 ) IS
3531 --
3532 l_proc varchar2(72) := g_package || 'chk_df';
3533 --
3534 BEGIN
3535 hr_utility.set_location('Entering:'||l_proc,10);
3536 --
3537 IF ((p_rec.booking_id IS NOT NULL) AND (
3538 NVL(ota_tdb_shd.g_old_rec.tdb_information_category, hr_api.g_varchar2) <>
3539 NVL(p_rec.tdb_information_category, hr_api.g_varchar2) OR
3540 NVL(ota_tdb_shd.g_old_rec.tdb_information1, hr_api.g_varchar2) <>
3541 NVL(p_rec.tdb_information1, hr_api.g_varchar2) OR
3542 NVL(ota_tdb_shd.g_old_rec.tdb_information2, hr_api.g_varchar2) <>
3543 NVL(p_rec.tdb_information2, hr_api.g_varchar2) OR
3544 NVL(ota_tdb_shd.g_old_rec.tdb_information3, hr_api.g_varchar2) <>
3545 NVL(p_rec.tdb_information3, hr_api.g_varchar2) OR
3546 NVL(ota_tdb_shd.g_old_rec.tdb_information4, hr_api.g_varchar2) <>
3547 NVL(p_rec.tdb_information4, hr_api.g_varchar2) OR
3548 NVL(ota_tdb_shd.g_old_rec.tdb_information5, hr_api.g_varchar2) <>
3549 NVL(p_rec.tdb_information5, hr_api.g_varchar2) OR
3550 NVL(ota_tdb_shd.g_old_rec.tdb_information6, hr_api.g_varchar2) <>
3551 NVL(p_rec.tdb_information6, hr_api.g_varchar2) OR
3552 NVL(ota_tdb_shd.g_old_rec.tdb_information7, hr_api.g_varchar2) <>
3553 NVL(p_rec.tdb_information7, hr_api.g_varchar2) OR
3554 NVL(ota_tdb_shd.g_old_rec.tdb_information8, hr_api.g_varchar2) <>
3555 NVL(p_rec.tdb_information8, hr_api.g_varchar2) OR
3556 NVL(ota_tdb_shd.g_old_rec.tdb_information9, hr_api.g_varchar2) <>
3557 NVL(p_rec.tdb_information9, hr_api.g_varchar2) OR
3558 NVL(ota_tdb_shd.g_old_rec.tdb_information10, hr_api.g_varchar2) <>
3559 NVL(p_rec.tdb_information10, hr_api.g_varchar2) OR
3560 NVL(ota_tdb_shd.g_old_rec.tdb_information11, hr_api.g_varchar2) <>
3561 NVL(p_rec.tdb_information11, hr_api.g_varchar2) OR
3562 NVL(ota_tdb_shd.g_old_rec.tdb_information12, hr_api.g_varchar2) <>
3563 NVL(p_rec.tdb_information12, hr_api.g_varchar2) OR
3564 NVL(ota_tdb_shd.g_old_rec.tdb_information13, hr_api.g_varchar2) <>
3565 NVL(p_rec.tdb_information13, hr_api.g_varchar2) OR
3566 NVL(ota_tdb_shd.g_old_rec.tdb_information14, hr_api.g_varchar2) <>
3567 NVL(p_rec.tdb_information14, hr_api.g_varchar2) OR
3568 NVL(ota_tdb_shd.g_old_rec.tdb_information15, hr_api.g_varchar2) <>
3569 NVL(p_rec.tdb_information15, hr_api.g_varchar2) OR
3570 NVL(ota_tdb_shd.g_old_rec.tdb_information16, hr_api.g_varchar2) <>
3571 NVL(p_rec.tdb_information16, hr_api.g_varchar2) OR
3572 NVL(ota_tdb_shd.g_old_rec.tdb_information17, hr_api.g_varchar2) <>
3573 NVL(p_rec.tdb_information17, hr_api.g_varchar2) OR
3574 NVL(ota_tdb_shd.g_old_rec.tdb_information18, hr_api.g_varchar2) <>
3575 NVL(p_rec.tdb_information18, hr_api.g_varchar2) OR
3576 NVL(ota_tdb_shd.g_old_rec.tdb_information19, hr_api.g_varchar2) <>
3577 NVL(p_rec.tdb_information19, hr_api.g_varchar2) OR
3578 NVL(ota_tdb_shd.g_old_rec.tdb_information20, hr_api.g_varchar2) <>
3579 NVL(p_rec.tdb_information20, hr_api.g_varchar2) ) )
3580 OR (p_rec.booking_id IS NULL) THEN
3581 --
3582 -- Only execute the validation if absolutely necessary:
3583 -- a) During update, the structure column value or any
3584 -- of the tdb_information values have actually changed.
3585 -- b) During insert.
3586 --
3587
3588 hr_dflex_utility.ins_or_upd_descflex_attribs
3589 (p_appl_short_name => 'OTA'
3590 ,p_descflex_name => 'OTA_DELEGATE_BOOKINGS'
3591 ,p_attribute_category => p_rec.tdb_information_category
3592 ,p_attribute1_name => 'TDB_INFORMATION1'
3593 ,p_attribute1_value => p_rec.tdb_information1
3594 ,p_attribute2_name => 'TDB_INFORMATION2'
3595 ,p_attribute2_value => p_rec.tdb_information2
3596 ,p_attribute3_name => 'TDB_INFORMATION3'
3597 ,p_attribute3_value => p_rec.tdb_information3
3598 ,p_attribute4_name => 'TDB_INFORMATION4'
3599 ,p_attribute4_value => p_rec.tdb_information4
3600 ,p_attribute5_name => 'TDB_INFORMATION5'
3601 ,p_attribute5_value => p_rec.tdb_information5
3602 ,p_attribute6_name => 'TDB_INFORMATION6'
3603 ,p_attribute6_value => p_rec.tdb_information6
3604 ,p_attribute7_name => 'TDB_INFORMATION7'
3605 ,p_attribute7_value => p_rec.tdb_information7
3606 ,p_attribute8_name => 'TDB_INFORMATION8'
3607 ,p_attribute8_value => p_rec.tdb_information8
3608 ,p_attribute9_name => 'TDB_INFORMATION9'
3609 ,p_attribute9_value => p_rec.tdb_information9
3610 ,p_attribute10_name => 'TDB_INFORMATION10'
3611 ,p_attribute10_value => p_rec.tdb_information10
3612 ,p_attribute11_name => 'TDB_INFORMATION11'
3613 ,p_attribute11_value => p_rec.tdb_information11
3614 ,p_attribute12_name => 'TDB_INFORMATION12'
3615 ,p_attribute12_value => p_rec.tdb_information12
3616 ,p_attribute13_name => 'TDB_INFORMATION13'
3617 ,p_attribute13_value => p_rec.tdb_information13
3618 ,p_attribute14_name => 'TDB_INFORMATION14'
3619 ,p_attribute14_value => p_rec.tdb_information14
3620 ,p_attribute15_name => 'TDB_INFORMATION15'
3621 ,p_attribute15_value => p_rec.tdb_information15
3622 ,p_attribute16_name => 'TDB_INFORMATION16'
3623 ,p_attribute16_value => p_rec.tdb_information16
3624 ,p_attribute17_name => 'TDB_INFORMATION17'
3625 ,p_attribute17_value => p_rec.tdb_information17
3626 ,p_attribute18_name => 'TDB_INFORMATION18'
3627 ,p_attribute18_value => p_rec.tdb_information18
3628 ,p_attribute19_name => 'TDB_INFORMATION19'
3629 ,p_attribute19_value => p_rec.tdb_information19
3630 ,p_attribute20_name => 'TDB_INFORMATION20'
3631 ,p_attribute20_value => p_rec.tdb_information20
3632 );
3633 END IF;
3634
3635 --
3636 hr_utility.set_location(' Leaving:'||l_proc,20);
3637 END chk_df;
3638
3639 --
3640 -- ----------------------------------------------------------------------------
3641 -- |---------------------------< insert_validate >----------------------------|
3642 -- ----------------------------------------------------------------------------
3643 --
3644 -- Description: Controls the validation execution on insert.
3645 --
3646 Procedure insert_validate(
3647 p_rec in ota_tdb_shd.g_rec_type,
3648 p_enrollment_type in varchar2
3649 ) is
3650 --
3651 l_proc varchar2(72) := g_package||'insert_validate';
3652 l_event_record_use varchar2(10);
3653 --
3654 Begin
3655 hr_utility.set_location('Entering:'||l_proc, 5);
3656 --
3657 -- Call all supporting business operations
3658 --
3659 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
3660 --
3661 --
3662 -- Call all supporting business operations
3663 --
3664 --
3665 -- check whether an event is customer based in which case if enrollment
3666 -- type is 'C' (Customer enrollments) then delegate_person_id must be null
3667 -- otherwise delegate_person_id must not be null, in other words it is
3668 -- a student enrollment.
3669
3670 /****************************************************************************
3671 Comment out as fix for bug 640958. We do not want to prevent block customer
3672 bookings on the student enrollment form, even for a restricted
3673 event.
3674 ****************************************************************************/
3675
3676 /*
3677 if p_rec.customer_id is not null then
3678
3679 ota_tdb_bus2.check_enrollment_type(p_rec.event_id,
3680 p_rec.delegate_contact_id,
3681 p_enrollment_type,
3682 p_rec.booking_id);
3683
3684 end if;
3685 */
3686 -- check that the delegate, contact and authorizer are visible when the
3687 -- booking is made
3688 --
3689 ota_tdb_bus2.check_person_visible (p_rec.delegate_person_id,
3690 p_rec.date_booking_placed,
3691 'Delegate',
3692 p_rec.person_address_type);
3693 --
3694 --
3695 -- Check that sponsor and delegate are valid for enrollment
3696 --
3697 ota_tdb_bus2.check_spon_del_validity (p_rec.event_id,
3698 p_rec.organization_id,
3699 p_rec.delegate_person_id,
3700 p_rec.sponsor_person_id,
3701 p_rec.date_booking_placed);
3702 --
3703 check_authorizer (p_rec.authorizer_person_id);
3704 --
3705 -- Check that the maximum number of internal delegates is not
3706 -- exceeded.
3707 if p_rec.internal_booking_flag = 'Y' and
3708 p_rec.number_of_places > 0 and
3709 event_place_needed(p_rec.booking_status_type_id) = 1 then
3710 --
3711 check_internal_booking(p_rec.event_id,
3712 p_rec.number_of_places,
3713 p_rec.booking_id);
3714 --
3715 end if;
3716 --
3717 -- Check that the delegate contact and sponsor contact exist for
3718 -- the customer for an external enrollment
3719 --
3720 ota_tdb_bus2.check_customer_details(p_rec.customer_id,
3721 p_rec.delegate_contact_id,
3722 p_rec.contact_id);
3723 --
3724 -- Checks whether contact address id is not null and that :
3725 -- The address_id is valid for the customer
3726 --
3727 -- ota_tdb_bus2.check_contact_address (p_rec.contact_address_id,
3728 -- p_rec.customer_id);
3729 --
3730 -- Check that when an internal booking is used that certain variables
3731 -- are populated while certain other variables are null
3732 --
3733 ota_tdb_bus2.check_organization_details (p_rec.organization_id,
3734 p_rec.delegate_person_id,
3735 p_rec.delegate_assignment_id,
3736 p_rec.sponsor_person_id,
3737 p_rec.sponsor_assignment_id);
3738 --
3739 -- check that the booking status type is valid
3740 --
3741 check_booking_status_type (p_rec.booking_status_type_id
3742 , p_rec.event_id); -- bug 3677661
3743 --
3744 -- check that the priority is within the domain 'Priority Level'
3745 --
3746 if p_rec.booking_priority is not null then
3747 --
3748 ota_general.check_domain_value ('PRIORITY_LEVEL', p_rec.booking_priority);
3749 --
3750 end if;
3751 --
3752 --
3753 -- check that the source is within the domain 'Booking Source'
3754 --
3755 if p_rec.source_of_booking is not null then
3756 --
3757 ota_general.check_domain_value ('BOOKING_SOURCE', p_rec.source_of_booking);
3758 --
3759 end if;
3760 --
3761 -- check that the number of places is one for a delegate
3762 --
3763 check_places (p_rec.delegate_person_id, p_rec.number_of_places);
3764 check_places (p_rec.delegate_contact_id, p_rec.number_of_places);
3765 --
3766 --
3767 -- check that the booking has not already been made for the delegate
3768 --
3769 check_unique_booking (p_rec.customer_id,
3770 p_rec.organization_id,
3771 p_rec.event_id,
3772 p_rec.delegate_person_id,
3773 p_rec.delegate_contact_id,
3774 p_rec.booking_id);
3775 --
3776 -- check that the event is still open for bookings
3777 --
3778 check_closed_event (p_rec.event_id,
3779 p_rec.date_booking_placed,
3780 'NEW EVENT');
3781 --
3782 -- Check that the person address is valid for the correspondent
3783 --
3784 if p_rec.person_address_id is not null then
3785 if p_rec.person_address_type <> 'E' then
3786 --
3787 -- Person address type should be external
3788 -- show error
3789 --
3790 fnd_message.set_name ('OTA','OTA_13506_ADDRESS_TYPE_NOT_I');
3791 fnd_message.raise_error;
3792 --
3793 -- Address must be a valid one for the correspondent
3794 --
3795 else
3796 if p_rec.corespondent = 'S' then
3797 --
3798 -- Check delegate has a valid address in per_addresses
3799 --
3800 ota_tdb_bus2.check_person_address(p_rec.delegate_person_id,
3801 p_rec.person_address_id,
3802 'Delegate');
3803 --
3804 elsif p_rec.corespondent = 'C' then
3805 --
3806 -- Check contact has a valid address in per_addresses
3807 --
3808 ota_tdb_bus2.check_person_address(p_rec.sponsor_person_id,
3809 p_rec.person_address_id,
3810 'Contact');
3811 --
3812 end if;
3813 end if;
3814 end if;
3815
3816 --
3817 -- subsequent checks requiring event details may re-use the event record
3818 -- refreshed by the previous procedure
3819 --
3820
3821 --
3822 -- check that if the booking is for a programme member then the parent has
3823 -- already been booked
3824 --
3825 check_programme_member (p_rec.event_id,
3826 p_rec.customer_id,
3827 p_rec.organization_id,
3828 p_rec.delegate_person_id,
3829 p_rec.delegate_contact_id,
3830 'SAME EVENT');
3831 --
3832 -- check that the business group of the event being booked is the same
3833 --
3834 check_event_business_group (p_rec.business_group_id,
3835 p_rec.event_id,
3836 'SAME EVENT');
3837 --
3838 --
3839 -- check that the business group of the booking status type is the same
3840 --
3841 check_type_business_group (p_rec.business_group_id,
3842 p_rec.booking_status_type_id);
3843 --
3844 --
3845 -- check that the successful attendance flag is only set for confirmed
3846 -- bookings
3847 --
3848 check_attendance (p_rec.successful_attendance_flag,
3849 p_rec.booking_status_type_id);
3850 --
3851 --
3852 --
3853 -- check that the failure reason is within the domain 'Delegate Failure
3854 -- Reason'
3855 --
3856 if p_rec.failure_reason is not null then
3857 --
3858 ota_general.check_domain_value ('DELEGATE_FAILURE_REASON',
3859 p_rec.failure_reason);
3860 --
3861 end if;
3862 --
3863 -- check that the reason for failure is not entered for a successful
3864 -- delegate
3865 --
3866 check_failure (p_rec.failure_reason,
3867 p_rec.successful_attendance_flag);
3868 /* This validation is moved to ota_tdb_api_ins2.create_enrollment for Bulk Enrollment.
3869 --
3870 -- check that the delegate is eligible to be booked on to the event
3871 --
3872 check_delegate_eligible (p_rec.event_id,
3873 p_rec.customer_id,
3874 p_rec.delegate_contact_id,
3875 p_rec.organization_id,
3876 p_rec.delegate_person_id,
3877 p_rec.delegate_assignment_id);
3878 */
3879 --
3880 check_constraints
3881 (
3882 p_rec.internal_booking_flag,
3883 p_rec.successful_attendance_flag
3884 );
3885 --
3886 -- Check business group is the same for all persons and assignments
3887 --
3888 ota_tdb_bus2.check_org_business_group (p_rec.event_id,
3889 p_rec.business_group_id,
3890 p_rec.organization_id,
3891 p_rec.delegate_person_id,
3892 p_rec.sponsor_person_id,
3893 p_rec.delegate_assignment_id,
3894 p_rec.sponsor_assignment_id,
3895 p_rec.date_booking_placed);
3896 --
3897 chk_line_id(p_rec.booking_id
3898 ,p_rec.line_id
3899 ,p_rec.org_id);
3900
3901 /*Enhancement 1823602*/
3902 IF p_rec.line_id IS NOT NULL THEN
3903 ota_tdb_bus2.check_commitment_date(p_rec.line_id,
3904 p_rec.event_id);
3905 END IF;
3906 /*Enhancement 1823602*/
3907
3908 --Bug 3619960
3909 ota_tdb_bus.chk_df(p_rec);
3910 hr_utility.set_location(' Leaving:'||l_proc, 10);
3911 End insert_validate;
3912 --
3913 -- ----------------------------------------------------------------------------
3914 -- |---------------------------< update_validate >----------------------------|
3915 -- ----------------------------------------------------------------------------
3916 --
3917 -- Description: Controls the validation execution on update.
3918 --
3919 Procedure update_validate(
3920 p_rec in ota_tdb_shd.g_rec_type,
3921 p_enrollment_type in varchar2
3922 ) is
3923 --
3924 l_proc varchar2(72) := g_package||'update_validate';
3925 --
3926 l_customer_id_changed boolean :=
3927 ota_general.value_changed (ota_tdb_shd.g_old_rec.customer_id,
3928 p_rec.customer_id);
3929 --
3930 l_status_type_id_changed boolean :=
3931 ota_general.value_changed (ota_tdb_shd.g_old_rec.booking_status_type_id,
3932 p_rec.booking_status_type_id);
3933 --
3934 l_event_id_changed boolean :=
3935 ota_general.value_changed (ota_tdb_shd.g_old_rec.event_id,
3936 p_rec.event_id);
3937 --
3938 l_business_group_id_changed boolean :=
3939 ota_general.value_changed (ota_tdb_shd.g_old_rec.business_group_id,
3940 p_rec.business_group_id);
3941 --
3942 l_date_booking_placed_changed boolean :=
3943 ota_general.value_changed (ota_tdb_shd.g_old_rec.date_booking_placed,
3944 p_rec.date_booking_placed);
3945 --
3946 l_delegate_person_id_changed boolean :=
3947 ota_general.value_changed (ota_tdb_shd.g_old_rec.delegate_person_id,
3948 p_rec.delegate_person_id);
3949 --
3950 l_sponsor_person_id_changed boolean :=
3951 ota_general.value_changed (ota_tdb_shd.g_old_rec.sponsor_person_id,
3952 p_rec.sponsor_person_id);
3953 --
3954 l_organization_id_changed boolean :=
3955 ota_general.value_changed (ota_tdb_shd.g_old_rec.organization_id,
3956 p_rec.organization_id);
3957 --
3958 l_contact_id_changed boolean :=
3959 ota_general.value_changed (ota_tdb_shd.g_old_rec.contact_id,
3960 p_rec.contact_id);
3961 --
3962 l_contact_address_id_changed boolean :=
3963 ota_general.value_changed (ota_tdb_shd.g_old_rec.contact_address_id,
3964 p_rec.contact_address_id);
3965 --
3966 l_authorizer_person_id_changed boolean :=
3967 ota_general.value_changed (ota_tdb_shd.g_old_rec.authorizer_person_id,
3968 p_rec.authorizer_person_id);
3969 --
3970 l_number_of_places_changed boolean :=
3971 ota_general.value_changed (ota_tdb_shd.g_old_rec.number_of_places,
3972 p_rec.number_of_places);
3973 --
3974 l_delegate_ass_changed boolean :=
3975 ota_general.value_changed (ota_tdb_shd.g_old_rec.delegate_assignment_id,
3976 p_rec.delegate_assignment_id);
3977 --
3978 l_sponsor_ass_changed boolean :=
3979 ota_general.value_changed (ota_tdb_shd.g_old_rec.sponsor_assignment_id,
3980 p_rec.sponsor_assignment_id);
3981 --
3982 l_booking_priority_changed boolean :=
3983 ota_general.value_changed (ota_tdb_shd.g_old_rec.booking_priority,
3984 p_rec.booking_priority);
3985 --
3986 l_person_address_id_changed boolean :=
3987 ota_general.value_changed (ota_tdb_shd.g_old_rec.person_address_id,
3988 p_rec.person_address_id);
3989 --
3990 l_attendance_flag_changed boolean :=
3991 ota_general.value_changed (ota_tdb_shd.g_old_rec.successful_attendance_flag,
3992 p_rec.successful_attendance_flag);
3993 --
3994 l_source_of_booking_changed boolean :=
3995 ota_general.value_changed (ota_tdb_shd.g_old_rec.source_of_booking,
3996 p_rec.source_of_booking);
3997 --
3998 l_failure_reason_changed boolean :=
3999 ota_general.value_changed (ota_tdb_shd.g_old_rec.failure_reason,
4000 p_rec.failure_reason);
4001 --
4002 l_delegate_contact_id_changed boolean :=
4003 ota_general.value_changed (ota_tdb_shd.g_old_rec.delegate_contact_id,
4004 p_rec.delegate_contact_id);
4005
4006 l_old_event_id_changed boolean :=
4007 ota_general.value_changed (ota_tdb_shd.g_old_rec.old_event_id,
4008 p_rec.old_event_id);
4009 --
4010 l_event_record_use varchar2(10);
4011 --
4012 /* bug no 4509873 */
4013 l_new_inv_id ota_activity_versions.inventory_item_id%type;
4014 l_old_inv_id ota_activity_versions.inventory_item_id%type;
4015
4016 CURSOR get_inv_id (p_event_id in number) is
4017 SELECT nvl(avt.inventory_item_id,1)
4018 FROM ota_events evt, ota_activity_versions avt
4019 WHERE evt.activity_version_id = avt.activity_version_id
4020 AND evt.event_id = p_event_id ;
4021
4022 /* bug no 4509873 */
4023
4024 l_status_type ota_booking_status_types.type%TYPE;
4025 --Bug 10253738
4026 l_booking_status_type ota_booking_status_types.type%TYPE;
4027 --
4028 Begin
4029 hr_utility.set_location('Entering:'||l_proc, 5);
4030 --
4031 -- Call check non updateable argument
4032 chk_non_updateable_args
4033 (
4034 p_rec => p_rec
4035 );
4036 --
4037 -- Call all supporting business operations
4038 --
4039 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
4040 --
4041 --
4042 -- if the event has been updated then refresh the global event record
4043 -- and set an indicator to allow any subsequent event related checks to use
4044 -- the record
4045 --
4046 if l_event_id_changed then
4047 --
4048 get_event (p_rec.event_id, 'NEW EVENT');
4049 l_event_record_use := 'SAME EVENT';
4050 check_booking_status_type (p_rec.booking_status_type_id
4051 , p_rec.event_id); -- bug 3677661
4052 --
4053 else
4054 --
4055 l_event_record_use := 'NEW EVENT';
4056 --
4057 end if;
4058 --
4059 ota_tdb_bus2.check_person_visible (p_rec.delegate_person_id,
4060 p_rec.date_booking_placed,
4061 'Delegate',
4062 p_rec.person_address_type);
4063 --
4064 -- Check that the maximum number of internal delegates is not
4065 -- exceeded.
4066 --
4067 if p_rec.internal_booking_flag = 'Y' and
4068 p_rec.number_of_places > 0 and
4069 event_place_needed(p_rec.booking_status_type_id) = 1 then
4070 --
4071 check_internal_booking(p_rec.event_id,
4072 p_rec.number_of_places,
4073 p_rec.booking_id);
4074 --
4075 end if;
4076 --
4077 -- check that the delegate and contact are visible when the booking is
4078 -- made
4079 --
4080 /****************************************************************************
4081 Comment out as fix for bug 640958. We do not want to prevent block customer
4082 bookings on the student enrollment form, even for a restricted
4083 event.
4084 ****************************************************************************/
4085
4086 /*
4087 if l_delegate_contact_id_changed or
4088 l_contact_id_changed or
4089 l_date_booking_placed_changed and
4090 p_rec.customer_id is not null then
4091
4092 ota_tdb_bus2.check_enrollment_type(p_rec.event_id,
4093 p_rec.delegate_contact_id,
4094 p_enrollment_type,
4095 p_rec.booking_id);
4096
4097 end if;
4098 */
4099 --
4100 --
4101 -- Checks whether contact address id is not null and that :
4102 -- The address_id is valid for the customer
4103 --
4104 if l_contact_address_id_changed or
4105 l_customer_id_changed then
4106 -- ota_tdb_bus2.check_contact_address (p_rec.contact_address_id,
4107 -- p_rec.customer_id);
4108 null;
4109 end if;
4110 --
4111 if l_event_id_changed or
4112 l_business_group_id_changed or
4113 l_organization_id_changed or
4114 l_delegate_person_id_changed or
4115 l_sponsor_person_id_changed or
4116 l_delegate_ass_changed or
4117 l_sponsor_ass_changed then
4118 --
4119 ota_tdb_bus2.check_org_business_group (p_rec.event_id,
4120 p_rec.business_group_id,
4121 p_rec.organization_id,
4122 p_rec.delegate_person_id,
4123 p_rec.sponsor_person_id,
4124 p_rec.delegate_assignment_id,
4125 p_rec.sponsor_assignment_id,
4126 p_rec.date_booking_placed);
4127 --
4128 end if;
4129 --
4130 if l_event_id_changed or
4131 l_delegate_person_id_changed or
4132 l_sponsor_person_id_changed or
4133 l_organization_id_changed then
4134 ota_tdb_bus2.check_spon_del_validity ( p_rec.event_id,
4135 p_rec.organization_id,
4136 p_rec.delegate_person_id,
4137 p_rec.sponsor_person_id,
4138 p_rec.date_booking_placed);
4139 end if;
4140 --
4141 --
4142 if l_authorizer_person_id_changed or
4143 l_date_booking_placed_changed then
4144 --
4145 check_authorizer (p_rec.authorizer_person_id);
4146 --
4147 end if;
4148 --
4149 --
4150 -- check that the booking status type is valid
4151 --
4152 if l_status_type_id_changed then
4153 --
4154 check_booking_status_type (p_rec.booking_status_type_id
4155 , p_rec.event_id); -- bug 3677661
4156 --
4157 end if;
4158 --
4159 --
4160 if l_business_group_id_changed then
4161 --
4162 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
4163 --
4164 end if;
4165 --
4166 --
4167 if l_person_address_id_changed then
4168 if p_rec.person_address_id is not null then
4169 if p_rec.person_address_type <> 'E' then
4170 --
4171 -- Person address type should be internal
4172 -- show error
4173 --
4174 fnd_message.set_name ('OTA','OTA_13506_ADDRESS_TYPE_NOT_I');
4175 fnd_message.raise_error;
4176 --
4177 -- Address must be a valid one for the correspondent
4178 --
4179 if p_rec.corespondent = 'S' then
4180 --
4181 -- Check delegate has a valid address in per_addresses
4182 --
4183 ota_tdb_bus2.check_person_address(p_rec.delegate_person_id,
4184 p_rec.person_address_id,
4185 'Delegate');
4186 --
4187 elsif p_rec.corespondent = 'C' then
4188 --
4189 -- Check contact has a valid address in per_addresses
4190 --
4191 ota_tdb_bus2.check_person_address(p_rec.sponsor_person_id,
4192 p_rec.person_address_id,
4193 'Contact');
4194 --
4195 end if;
4196 end if;
4197 end if;
4198 end if;
4199 --
4200 -- Check that the delegate contact and sponsor contact exist for
4201 -- the customer for an external enrollment
4202 --
4203 if l_customer_id_changed or
4204 l_delegate_contact_id_changed or
4205 l_contact_id_changed then
4206 ota_tdb_bus2.check_customer_details(p_rec.customer_id,
4207 p_rec.delegate_contact_id,
4208 p_rec.contact_id);
4209 end if;
4210 --
4211 -- Check if organization details need to be checked
4212 --
4213 if l_delegate_person_id_changed or
4214 l_sponsor_person_id_changed or
4215 l_organization_id_changed then
4216 ota_tdb_bus2.check_organization_details (p_rec.organization_id,
4217 p_rec.delegate_person_id,
4218 p_rec.delegate_assignment_id,
4219 p_rec.sponsor_person_id,
4220 p_rec.sponsor_assignment_id);
4221 end if;
4222 --
4223 --
4224 -- check that the number of places is one for a delegate
4225 --
4226 if l_delegate_person_id_changed or
4227 l_delegate_contact_id_changed or
4228 l_number_of_places_changed then
4229 --
4230 check_places (p_rec.delegate_contact_id, p_rec.number_of_places);
4231 check_places (p_rec.delegate_person_id, p_rec.number_of_places);
4232 --
4233 end if;
4234 --
4235 --
4236 -- check that the booking has not already been made
4237 --
4238 if l_customer_id_changed or
4239 l_organization_id_changed or
4240 l_event_id_changed or
4241 l_delegate_person_id_changed or
4242 l_delegate_contact_id_changed or
4243 l_status_type_id_changed then
4244
4245 ota_utility.get_booking_status_type( p_status_type_id =>
4246 p_rec.booking_status_type_id,
4247 p_type => l_booking_status_type) ;
4248
4249 IF l_booking_status_type <> 'C' THEN --added this condition for bug 10253738
4250 --
4251 check_unique_booking (p_rec.customer_id,
4252 p_rec.organization_id,
4253 p_rec.event_id,
4254 p_rec.delegate_person_id,
4255 p_rec.delegate_contact_id,
4256 p_rec.booking_id);
4257 end if;
4258 --
4259 end if;
4260 --
4261 --
4262 -- check that if the booking is for a programme member then the parent has
4263 -- already been booked
4264 --
4265 if l_customer_id_changed or
4266 l_organization_id_changed or
4267 l_event_id_changed or
4268 l_delegate_person_id_changed or
4269 l_delegate_contact_id_changed then
4270 --
4271 check_programme_member (p_rec.event_id,
4272 p_rec.customer_id,
4273 p_rec.organization_id,
4274 p_rec.delegate_person_id,
4275 p_rec.delegate_contact_id,
4276 l_event_record_use,
4277 p_rec.booking_id);
4278 --
4279 end if;
4280 --
4281 --
4282 -- check that the business group of the activity version being booked is
4283 -- the same
4284 --
4285 if l_business_group_id_changed or
4286 l_event_id_changed then
4287 --
4288 check_event_business_group (p_rec.business_group_id,
4289 p_rec.event_id,
4290 l_event_record_use);
4291 --
4292 end if;
4293 --
4294 --
4295 -- check that the successful attendance flag is only set for confirmed
4296 -- bookings
4297 --
4298 if l_attendance_flag_changed or
4299 l_status_type_id_changed then
4300 --
4301 check_attendance (p_rec.successful_attendance_flag,
4302 p_rec.booking_status_type_id);
4303 --
4304 end if;
4305 --
4306 --
4307 -- check that the priority is within the domain 'Priority Level'
4308 --
4309 if l_booking_priority_changed and p_rec.booking_priority is not null then
4310 --
4311 ota_general.check_domain_value ('PRIORITY_LEVEL', p_rec.booking_priority);
4312 --
4313 end if;
4314 --
4315 --
4316 -- check that the source is within the domain 'Booking Source'
4317 --
4318 if l_source_of_booking_changed and p_rec.source_of_booking is not null then
4319 --
4320 ota_general.check_domain_value ('BOOKING_SOURCE', p_rec.source_of_booking);
4321 --
4322 end if;
4323 --
4324 --
4325 -- check that the business group of the booking status type is the same
4326 --
4327 if l_business_group_id_changed or
4328 l_status_type_id_changed then
4329 --
4330 check_type_business_group (p_rec.business_group_id,
4331 p_rec.booking_status_type_id);
4332 --
4333 end if;
4334 --
4335 --
4336 -- check that the failure reason is within the domain 'Delegate Failure
4337 -- Reason'
4338 --
4339 if l_failure_reason_changed and p_rec.failure_reason is not null then
4340 --
4341 ota_general.check_domain_value ('DELEGATE_FAILURE_REASON',
4342 p_rec.failure_reason);
4343 --
4344 end if;
4345 --
4346 --
4347 -- check that the reason for failure is not entered for a successful
4348 -- delegate
4349 --
4350 if l_failure_reason_changed or
4351 l_attendance_flag_changed then
4352 --
4353 check_failure (p_rec.failure_reason,
4354 p_rec.successful_attendance_flag);
4355 --
4356 end if;
4357 --
4358
4359 --
4360 -- check that the event is still open for bookings
4361 --
4362 if l_event_id_changed or
4363 l_date_booking_placed_changed then
4364 --
4365 check_closed_event (p_rec.event_id,
4366 p_rec.date_booking_placed,
4367 l_event_record_use);
4368 --
4369 end if;
4370 --
4371 --
4372 -- check that the delegate is eligible to be booked on to the event
4373 --
4374 /* Moved the validation to ota_tdb_api_upd2
4375 if l_event_id_changed or
4376 l_customer_id_changed or
4377 l_organization_id_changed or
4378 l_delegate_person_id_changed or
4379 l_delegate_ass_changed then
4380 --
4381 check_delegate_eligible (p_rec.event_id,
4382 p_rec.customer_id,
4383 p_rec.delegate_contact_id,
4384 p_rec.organization_id,
4385 p_rec.delegate_person_id,
4386 p_rec.delegate_assignment_id);
4387 --
4388 end if;
4389 */
4390 --
4391 --
4392 check_constraints
4393 (
4394 p_rec.internal_booking_flag,
4395 p_rec.successful_attendance_flag
4396 );
4397 --
4398 chk_line_id(p_rec.booking_id
4399 ,p_rec.line_id
4400 ,p_rec.org_id);
4401
4402 if l_old_event_id_changed then
4403 ota_tdb_bus2.chk_old_event_changed
4404 (p_rec.booking_id
4405 ,p_rec.old_event_id);
4406 end if;
4407 /* Enhancement 1823602*/
4408 IF p_rec.line_id IS NOT NULL THEN
4409 ota_tdb_bus2.check_commitment_date(p_rec.line_id,
4410 p_rec.event_id);
4411 END IF;
4412 /*Enhancement 1823602*/
4413 /* Bug 4401588 */
4414 IF p_rec.line_id IS NOT NULL AND l_number_of_places_changed THEN
4415 fnd_message.set_name ('OTA','OTA_443887_TDB_OM_CHK_UPD');
4416 fnd_message.raise_error;
4417 END IF;
4418
4419 /* Bug 4401588*/
4420 /* bug no 4509873 */
4421 -- Commented for bug#4874734
4422 -- IF p_rec.line_id is not null then
4423 OPEN get_inv_id (ota_tdb_shd.g_old_rec.event_id);
4424 FETCH get_inv_id INTO l_old_inv_id;
4425 CLOSE get_inv_id ;
4426 OPEN get_inv_id (p_rec.event_id);
4427 FETCH get_inv_id INTO l_new_inv_id;
4428 CLOSE get_inv_id ;
4429 IF l_new_inv_id <> l_old_inv_id then
4430 fnd_message.set_name ('OTA','OTA_443905_TDB_TRN_ENR_DIF_INV');
4431 fnd_message.raise_error;
4432 END IF;
4433 --END IF;
4434
4435 --Added for bug#4650304
4436 IF l_status_type_id_changed THEN
4437 check_online_enr_change(
4438 p_booking_id => p_rec.booking_id
4439 ,p_event_id => p_rec.event_id
4440 ,p_booking_status_type_id => p_rec.booking_status_type_id
4441 ,p_content_player_status => p_rec.content_player_status
4442 ,p_delegate_person_id => p_rec.delegate_person_id
4443 ,p_delegate_contact_id => p_rec.delegate_contact_id);
4444 END IF;
4445
4446 -- Added for bug#4606760
4447 IF ((l_delegate_person_id_changed OR l_event_id_changed)
4448 AND p_rec.delegate_person_id IS NOT NULL) THEN
4449 ota_tdb_bus.check_secure_event(p_rec.event_id, p_rec.delegate_person_id);
4450
4451 END IF;
4452
4453 -- Bug#5614187 - Enrollments can not be moved from cancelled status after enrollment
4454 -- period is over.
4455 IF l_status_type_id_changed THEN
4456 ota_utility.get_booking_status_type( p_status_type_id => ota_tdb_shd.g_old_rec.booking_status_type_id,
4457 p_type => l_status_type) ;
4458 IF l_status_type = 'C' THEN
4459 ota_utility.get_booking_status_type( p_status_type_id => p_rec.booking_status_type_id,
4460 p_type => l_status_type) ;
4461 IF l_status_type <> 'C' THEN
4462 l_status_type := ota_tdb_bus.check_enrollment_dates(p_rec.event_id, sysdate);
4463 END IF;
4464 END IF;
4465 END IF;
4466
4467 /* bug no 4509873 */
4468 --Bug 3619960
4469 ota_tdb_bus.chk_df(p_rec);
4470 hr_utility.set_location(' Leaving:'||l_proc, 10);
4471 --
4472 End update_validate;
4473 --
4474 -- ----------------------------------------------------------------------------
4475 -- |---------------------------< delete_validate >----------------------------|
4476 -- ----------------------------------------------------------------------------
4477 --
4478 -- Description: Controls the validation execution on delete.
4479 --
4480 Procedure delete_validate(p_rec in ota_tdb_shd.g_rec_type) is
4481 --
4482 l_proc varchar2(72) := g_package||'delete_validate';
4483 --
4484 Begin
4485 hr_utility.set_location('Entering:'||l_proc, 5);
4486 --
4487 -- Call all supporting business operations
4488 --
4489 -- Check that booking id is not referenced in ota_training_plan_costs
4490 --
4491 check_training_plan_costs(p_rec.booking_id);
4492 --
4493 -- Check if Line id is not null
4494 chk_Order_line_exist(ota_tdb_shd.g_old_rec.line_id
4495 ,ota_tdb_shd.g_old_rec.org_id) ;
4496 --
4497 -- check that no resources exist for the booking
4498 --
4499 check_resources (p_rec.booking_id);
4500 --
4501 -- check that no finance line exist for the booking
4502 --
4503 check_finance_lines (p_rec.booking_id);
4504 --
4505 -- Check if we are deleting a program enrollment,
4506 -- there are no existing program member
4507 -- enrollments
4508 --
4509 check_pmm_enrollments;
4510 --
4511 hr_utility.set_location(' Leaving:'||l_proc, 10);
4512 End delete_validate;
4513 --
4514 end ota_tdb_bus;
[Home] [Help]