1 Package Body ota_tdb_bus2 as
2 /* $Header: ottdb01t.pkb 120.30 2011/02/07 11:01:05 shwnayak ship $ */
3 g_package varchar2(33) := ' ota_tdb_bus2.'; -- Global package name
4 --
5 --***************************** STARTS HERE **********************************
6 --
7 g_event_rec ota_evt_shd.g_rec_type;
8 --
9 --
10 -- Global package name
11 --
12 -- global constants
13 --
14 -- Booking Status Types
15 --
16 g_wait_list_booking varchar2(1) := 'W';
17 g_placed_booking varchar2(1) := 'P';
18 g_attended_booking varchar2(1) := 'A';
19 g_cancelled_booking varchar2(1) := 'C';
20 g_requested_booking varchar2(1) := 'R';
21 --
22 -- Event Statuses
23 --
24 g_full_event varchar2(1) := 'W';
25 g_normal_event varchar2(1) := 'N';
26 g_planned_event varchar2(1) := 'P';
27 g_closed_event varchar2(1) := 'C';
28 -- ----------------------------------------------------------------------------
29 -- |-------------------------< check_person_address >-------------------------|
30 -- ----------------------------------------------------------------------------
31 --
32 -- PUBLIC
33 -- Description: Check Person Address
34 --
35 -- Checks that the given person is registered at the given address
36 --
37 Procedure check_person_address (p_person_id in number,
38 p_address_id in number,
39 p_delegate_or_contact in varchar2) is
40 --
41 -- Cursor to check that the person and address are associated
42 --
43 cursor c_address is
44 select 'X'
45 from per_addresses
46 where person_id = p_person_id
47 and address_id = p_address_id;
48 --
49 l_proc varchar2(72) := 'OTA_TDB_BUS2 '||'check_person_address';
50 l_dummy varchar2(1);
51 --
52 Begin
53 hr_utility.set_location('Entering:'||l_proc, 5);
54 --
55 if p_person_id is not null and p_address_id is not null then
56 --
57 open c_address;
58 fetch c_address into l_dummy;
59 if c_address%notfound then
60 --
61 close c_address;
62 --
63 fnd_message.set_name ('OTA', 'OTA_13236_TDB_NO_PERSON_ADDR');
64 fnd_message.set_token ('PERSON_TYPE', p_delegate_or_contact);
65 fnd_message.raise_error;
66 --
67 end if;
68 --
69 close c_address;
70 --
71 end if;
72 --
73 hr_utility.set_location(' Leaving:'||l_proc, 10);
74 End check_person_address;
75 -- ----------------------------------------------------------------------------
76 -- |-------------------------< other_bookings_clash >-------------------------|
77 -- ----------------------------------------------------------------------------
78 --
79 -- PUBLIC
80 -- Description: Other Bookings Clash
81 --
82 -- Checks if the booking being made clashes with any other
83 -- bookings for the delegate
84 -- Note - bookings only clash if they are confirmed
85 --
86 Function other_bookings_clash (p_delegate_person_id in varchar2,
87 p_delegate_contact_id in varchar2,
88 p_event_id in number,
89 p_booking_status_type_id in varchar2)
90 Return boolean is
91 --
92 --
93 -- cursor to select any confirmed bookings for events which
94 -- clash with the event being booked
95 --
96 --Bug 5169354
97 cursor c_other_person_bookings IS
98 /*
99 Modified for bug#5498011
100 Convert ev dates into evt timezone and then compare
101 Common cursor for both person as well as contact
102 */
103 Select type
104 from
105 (
106 select bst.TYPE
107 ,ota_timezone_util.convert_date(ev.course_start_date, ev.course_start_time, ev.timezone, evt.timezone) ev_course_start_date
108 ,ota_timezone_util.convert_date(ev.course_end_date, nvl(ev.course_end_time,'23:59'), ev.timezone, evt.timezone) ev_course_end_date
109 ,evt.course_start_date evt_course_start_date
110 ,decode(evt.course_start_date, NULL, NULL, nvl(evt.course_start_time,'00:00')) evt_course_start_time
111 ,evt.course_end_date evt_course_end_date
112 ,decode(evt.course_end_date, NULL, NULL, nvl(evt.course_end_time,'23:59')) evt_course_end_time
113 from ota_delegate_bookings db,
114 ota_booking_status_types bst,
115 ota_events ev,
116 ota_events evt
117 where ( (p_delegate_contact_id IS NULL AND db.delegate_person_id = p_delegate_person_id
118 OR (p_delegate_person_id IS NULL AND db.delegate_contact_id = p_delegate_contact_id)))
119 and db.booking_status_type_id = bst.booking_status_type_id
120 and bst.type <> g_cancelled_booking
121 and db.event_id = ev.event_id
122 and evt.event_id = p_event_id
123 and ev.event_id <> p_event_id
124 and ev.event_type <>'SELFPACED' -- Added for Bug 2241280
125 )
126 Where
127 (
128 ((trunc(ev_course_start_date) = trunc(ev_course_end_date) and
129 evt_course_start_date = evt_course_end_date and
130 trunc(ev_course_start_date) = evt_course_start_date) or
131 (
132 (trunc(ev_course_start_date) <> trunc(ev_course_end_date) or
133 evt_course_start_date <> evt_course_end_date) and
134 (trunc(ev_course_start_date) <= evt_course_end_date and
135 trunc(ev_course_end_date) >= evt_course_start_date)
136 ))
137 AND
138 (
139 (((nvl(evt_course_start_time, '-99:99')
140 > nvl(to_char(ev_course_start_date,'HH24:MI'), '99:99') and
141 nvl(evt_course_start_time, '-99:99') <
142 nvl(to_char(ev_course_end_date,'HH24:MI'), '99:99'))) OR
143 ((nvl(evt_course_end_time, '99:99')
144 > nvl(to_char(ev_course_start_date,'HH24:MI'), '99:99') and
145 nvl(evt_course_end_time, '99:99') <
146 nvl(to_char(ev_course_end_date,'HH24:MI'), '99:99'))) OR
147 ((nvl(to_char(ev_course_end_date,'HH24:MI'), '99:99') >
148 nvl(evt_course_start_time, '-99:99') and
149 nvl(to_char(ev_course_end_date,'HH24:MI'), '99:99') <
150 nvl(evt_course_end_time, '-99:99'))) OR
151 ((nvl(to_char(ev_course_start_date,'HH24:MI'), '99:99') >
152 nvl(evt_course_start_time, '-99:99') and
153 nvl(to_char(ev_course_start_date,'HH24:MI'), '99:99') <
154 nvl(evt_course_end_time, '-99:99')))) OR
155 ((nvl(evt_course_end_time, '-99:99') =
156 nvl(to_char(ev_course_end_date,'HH24:MI'), '-99:99') and
157 nvl(evt_course_start_time, '-99:99') =
158 nvl(to_char(ev_course_start_date,'HH24:MI'), '-99:99')))
159 )
160 )
161 order by type;
162 --
163
164 /* For Bug 2241280 */
165 CURSOR csr_event_type
166 IS
167 SELECT evt.event_type
168 FROM OTA_EVENTS evt
169 WHERE evt.event_id= p_event_id;
170 --
171 l_proc varchar2(72) := g_package||'other_bookings_clash';
172 l_result boolean;
173 l_warn boolean := false;
174 l_booking_status varchar2(80);
175 l_dummy varchar2(80);
176 --
177 Begin
178 hr_utility.set_location('Entering:'||l_proc, 5);
179 --
180 -- Modified for bug#5498011
181 -- Common cursor for both person as well as contact
182 For event in csr_event_type
183 LOOP
184 exit when csr_event_type%notfound;
185 if event.event_type <>'SELFPACED' then
186 open c_other_person_bookings;
187 fetch c_other_person_bookings into l_dummy;
188 l_result := c_other_person_bookings%found;
189 close c_other_person_bookings;
190 end if;
191 END LOOP;
192 --
193
194 if l_result then
195 --
196 l_booking_status := ota_tdb_bus.booking_status_type(p_booking_status_type_id);
197
198 if l_booking_status in (g_attended_booking, g_placed_booking) and
199 l_dummy in (g_attended_booking, g_placed_booking) then
200 --
201 -- Professional UI requires only a warning message.
202 --
203 --fnd_message.set_name('OTA', 'OTA_13670_TDB_DOUBLE_BOOKING');
204 --fnd_message.raise_error;
205 l_warn := true;
206 --
207 else
208 --
209 if l_booking_status <> g_cancelled_booking then
210 --
211 l_warn := true;
212 --
213 end if;
214 --
215 end if;
216 --
217 end if;
218 --
219 return(l_warn);
220 --
221 hr_utility.set_location(' Leaving:'||l_proc, 10);
222 End other_bookings_clash;
223 -- ----------------------------------------------------------------------------
224 -- |-------------------------< overdraft_exceeded >---------------------------|
225 -- ----------------------------------------------------------------------------
226 --
227 -- PUBLIC
228 -- Description: Overdraft Exceeded
229 --
230 -- Checks if the overdraft is exceeded for a booking using a
231 -- pre-purchase agreement.
232 --
233 Function overdraft_exceeded (p_booking_deal_id in number,
234 p_money_amount in number)
235 Return boolean is
236 --
237 -- cursor to check if pre-purchase agreement has an overdraft limit
238 --
239 cursor c1 is
240 select nvl(overdraft_limit,0)
241 from ota_booking_deals
242 where booking_deal_id = p_booking_deal_id;
243 --
244 l_proc varchar2(72) := g_package||'overdraft_exceeded';
245 l_overdraft_limit number(9,2);
246 l_balance number(9,2) := ota_tbd_api.tfl_balance(p_booking_deal_id,
247 'M');
248 --
249 Begin
250 --
251 hr_utility.set_location('Entering:'||l_proc, 5);
252 --
253 -- get overdraft limit.
254 --
255 open c1;
256 --
257 fetch c1 into l_overdraft_limit;
258 --
259 close c1;
260 --
261 hr_utility.set_location('Overdraft Limit '||l_overdraft_limit,10);
262 hr_utility.set_location('Balance '||l_balance,10);
263 --
264 l_balance := l_balance + l_overdraft_limit;
265 hr_utility.set_location('Balance '||l_balance,10);
266 --
267 if l_balance - p_money_amount < 0 then
268 return true;
269 else
270 return false;
271 end if;
272 --
273 hr_utility.set_location(' Leaving:'||l_proc, 10);
274 --
275 end overdraft_exceeded;
276 --
277 --
278 -- ----------------------------------------------------------------------------
279 -- |--------------------------< check_person_visible >------------------------|
280 -- ----------------------------------------------------------------------------
281 --
282 -- PRIVATE
283 -- Description: Check Person Visible
284 --
285 -- Checks that the specified person is visible on the given date
286 --
287 Procedure check_person_visible (p_person_id in number,
288 p_date_booking_placed in date,
289 p_person_type in varchar2,
290 p_person_address_type in varchar2) is
291 --
292 l_proc varchar2(72) := g_package||'check_person_visible';
293 --
294 Begin
295 hr_utility.set_location('Entering:'||l_proc, 5);
296 --
297 -- only perform the check if the person is specified
298 --
299 if p_person_id is not null then
300 --
301 hr_utility.trace(p_person_type||' Person ID -> '||to_char(p_person_id));
302 if not ota_tdb_bus.check_person (p_person_id,
303 p_date_booking_placed,
304 p_person_type,
305 p_person_address_type) then
306 --
307 if p_person_address_type = 'INTERNAL' then
308 fnd_message.set_name ('OTA', 'OTA_13202_GEN_INVALID_KEY');
309 fnd_message.set_token ('TABLE_NAME', 'OTA_PEOPLE_V');
310 fnd_message.set_token ('COLUMN_NAME', p_person_type||' Person');
311 fnd_message.raise_error;
312 elsif p_person_address_type = 'INTERNAL' then
313 fnd_message.set_name ('OTA', 'OTA_13202_GEN_INVALID_KEY');
314 fnd_message.set_token ('TABLE_NAME', 'OTA_CUST_CONTACTS_V');
315 fnd_message.set_token ('COLUMN_NAME', p_person_type||' Person');
316 fnd_message.raise_error;
317 end if;
318 --
319 end if;
320 --
321 end if;
322 --
323 hr_utility.set_location(' Leaving:'||l_proc, 10);
324 End check_person_visible;
325 --
326 --
327 -- ----------------------------------------------------------------------------
328 -- |--------------------------< check_org_business_group >--------------------|
329 -- ----------------------------------------------------------------------------
330 --
331 --
332 -- Description: Checks Organization business group information
333 --
334 -- Checks whether business group id for an internal enrollment is :
335 -- the same as the organization_id
336 -- the same as the delegate_person_id
337 -- the same as the sponsor_person_id
338 -- the same as the delegate_assignment_id
339 -- the same as the sponsor_assignment_id
340 --
341 Procedure check_org_business_group (p_event_id in number,
342 p_business_group_id in number,
343 p_organization_id in number,
344 p_delegate_person_id in number,
345 p_sponsor_person_id in number,
346 p_delegate_assignment_id in number,
347 p_sponsor_assignment_id in number,
348 p_date_booking_placed in date) is
349 l_proc varchar2(72) := g_package||'check_org_business_group';
350 l_dummy varchar2(30);
351 l_global_bg ota_delegate_bookings.business_group_id%type :=
352 FND_PROFILE.VALUE('OTA_HR_GLOBAL_BUSINESS_GROUP_ID');
353 --
354 --
355 cursor c_person (l_person_id number) is
356 select 1
357 from per_all_people_f
358 where person_id = l_person_id
359 and business_group_id = p_business_group_id;
360
361
362
363 --
364 cursor c_assignment (l_assignment_id number) is
365 select 1
366 from per_assignments_f
367 where assignment_id = l_assignment_id
368 and business_group_id = p_business_group_id;
369
370
371 --
372 cursor c_organization is
373 select 1
374 from hr_all_organization_units
375 where organization_id = p_organization_id
376 and business_group_id = p_business_group_id;
377
378 --
379 --
380 Begin
381 hr_utility.set_location('Entering:'||l_proc, 5);
382 --
383 -- Only perform validation checks if we are dealing with an
384 -- internal enrollment, in other words organization id is not null
385 --
386 if p_organization_id is not null then
387 if l_global_bg is null then
388
389 --
390 -- Check organization business group
391 --
392 open c_organization;
393 fetch c_organization into l_dummy;
394 if not c_organization%found then
395 --
396 -- This organization has a different business group
397 --
398 fnd_message.set_name ('OTA','OTA_13510_ORG_BUSINESS_GROUP');
399 fnd_message.raise_error;
400 --
401 end if;
402 close c_organization;
403 --
404 -- Check delegate business group
405 --
406 if p_delegate_person_id is not null then
407 --
408 -- Check delegate business group
409 --
410 open c_person(p_delegate_person_id);
411 fetch c_person into l_dummy;
412 if not c_person%found then
413 --
414 -- The delegate has adifferent business group
415 --
416 fnd_message.set_name ('OTA','OTA_13584_DEL_BUSINESS_GROUP');
417 fnd_message.raise_error;
418 --
419 end if;
420 close c_person;
421 --
422 end if;
423 --
424 -- Check sponsor business group
425 --
426 if p_sponsor_person_id is not null then
427 --
428 -- Check sponsor business group
429 --
430 open c_person(p_sponsor_person_id);
431 fetch c_person into l_dummy;
432 if not c_person%found then
433 --
434 -- The delegate has adifferent business group
435 --
436 fnd_message.set_name ('OTA','OTA_13585_CON_BUSINESS_GROUP');
437 fnd_message.raise_error;
438 --
439 end if;
440 close c_person;
441 --
442 end if;
443 --
444 -- Check delegate assignment
445 --
446 if p_delegate_assignment_id is not null then
447 --
448 -- Check delegate assignment business group
449 --
450 open c_assignment(p_delegate_assignment_id);
451 fetch c_assignment into l_dummy;
452 if not c_assignment%found then
453 --
454 -- The delegate assignment has a different business group
455 --
456 fnd_message.set_name ('OTA','OTA_13586_DEL_ASS_BUS_GROUP');
457 fnd_message.raise_error;
458 --
459 end if;
460 close c_assignment;
461 --
462 end if;
463 --
464 -- Check contact assignment
465 --
466 if p_sponsor_assignment_id is not null then
467 --
468 -- Check sponsor assignment business group
469 --
470 open c_assignment(p_sponsor_assignment_id);
471 fetch c_assignment into l_dummy;
472 if not c_assignment%found then
473 --
474 -- The sponsor assignment has a different business group
475 --
476 fnd_message.set_name ('OTA','OTA_13587_SPON_ASS_BUS_GROUP');
477 fnd_message.raise_error;
478 --
479 end if;
480 close c_assignment;
481 --
482 end if;
483 end if;
484 end if;
485 hr_utility.set_location(' Leaving:'||l_proc, 10);
486 End check_org_business_group;
487 --
488 --
489 -- ----------------------------------------------------------------------------
490 -- |--------------------------< check_contact_address >-----------------------|
491 -- ----------------------------------------------------------------------------
492 --
493 -- Description: Check Contact Address Id related information
494 --
495 -- Checks whether contact address id is not null and that :
496 -- The address_id is valid for the customer
497 --
498 Procedure check_contact_address (p_contact_address_id in number,
499 p_customer_id in number) is
500 --
501 l_proc varchar2(72) := g_package||'check_contact_address';
502 l_dummy varchar2(30);
503 --
504 -- Bug#2063604 hdshah use ra_addresses_all instead of ra_addresses.
505 -- Bug#2652833 arkashya replaced cursor query to use HZ_ tables directly instead of ra_ views
506
507 cursor l_address is
508 select 1
509 from HZ_LOCATIONS loc,
510 HZ_CUST_ACCT_SITES acct_site,
511 HZ_PARTY_SITES party_site
512 where PARTY_SITE.location_id = LOC.location_id
513 and ACCT_SITE.party_site_id = PARTY_SITE.party_site_id
514 and ACCT_SITE.CUST_ACCOUNT_ID = p_customer_id
515 and ACCT_SITE.CUST_ACCT_SITE_ID = p_contact_address_id
516 AND DECODE(fnd_profile.value('OTA_HR_GLOBAL_BUSINESS_GROUP_ID'),
517 null, (NVL(ORG_ID , NVL(TO_NUMBER(DECODE(SUBSTRB( USERENV('CLIENT_INFO'),1,1),' ',
518 NULL, SUBSTRB( USERENV('CLIENT_INFO'),1,10))),
519 -99))), 1 ) =
520 DECODE(fnd_profile.value('OTA_HR_GLOBAL_BUSINESS_GROUP_ID'),
521 null, (NVL(TO_NUMBER(DECODE(SUBSTRB( USERENV('CLIENT_INFO'),1,1),' ',
522 NULL,SUBSTRB(USERENV( 'CLIENT_INFO'),1,10))),
523 -99)),1 );
524
525
526
527
528 --
529 Begin
530 hr_utility.set_location('Entering:'||l_proc, 5);
531 --
532 -- Only perform validation checks if we are dealing with an
533 -- external enrollment, in other words customer id is not null
534 --
535 if p_customer_id is not null and
536 p_contact_address_id is not null then
537 --
538 -- Check if address exists for customer
539 --
540 open l_address;
541 fetch l_address into l_dummy;
542 if not l_address%found then
543 --
544 -- Not a valid address for this customer
545 --
546 fnd_message.set_name ('OTA','OTA_13509_CONTACT_ADDRESS_INV');
547 fnd_message.raise_error;
548 --
549 end if;
550 close l_address;
551 --
552 end if;
553 hr_utility.set_location(' Leaving:'||l_proc, 10);
554 End check_contact_address;
555 --
556 --
557 -- ----------------------------------------------------------------------------
558 -- |--------------------------< check_organization_details >------------------|
559 -- ----------------------------------------------------------------------------
560 --
561 -- Description: Check Organization Id related information
562 --
563 -- Checks whether organization id is not null and whether :
564 -- if sponsor_person_id is not null then
565 -- sponsor_assignment_id must be not null
566 -- if delegate_person_id is not null then
567 -- delegate_assignment_id must be not null
568 --
569 Procedure check_organization_details(p_organization_id in number,
570 p_delegate_person_id in number,
571 p_delegate_assignment_id in number,
572 p_sponsor_person_id in number,
573 p_sponsor_assignment_id in number) is
574 --
575 l_proc varchar2(72) := g_package||'check_organization_details';
576 --
577 Begin
578 hr_utility.set_location('Entering:'||l_proc, 5);
579 --
580 -- Only perform validation checks if we are dealing with an
581 -- internal enrollment, in other words organization id is not null
582 --
583 if p_organization_id is not null then
584 --
585 if p_sponsor_person_id is not null and
586 p_sponsor_assignment_id is null then
587 --
588 -- Display error message as this should not occur
589 -- in this case p_sponsor_assignment_id is mandatory
590 --
591 fnd_message.set_name ('OTA','OTA_13503_SPONSOR_ASSIGNMENT');
592 fnd_message.raise_error;
593 end if;
594 --
595 if p_delegate_person_id is not null and
596 p_delegate_assignment_id is null then
597 --
598 -- Display error message as this should not occur
599 -- in this case p_delegate_assignment_id is mandatory
600 --
601 fnd_message.set_name ('OTA','OTA_13502_DELEGATE_ASSIGNMENT');
602 fnd_message.raise_error;
603 end if;
604 --
605 end if;
606 hr_utility.set_location(' Leaving:'||l_proc, 10);
607 End check_organization_details;
608 --
609 --
610 -- ----------------------------------------------------------------------------
611 -- |--------------------------< check_enrollment_type >-----------------------|
612 -- ----------------------------------------------------------------------------
613 --
614 -- PRIVATE
615 -- Description: Check Enrollment Type
616 --
617 -- Checks that the enrollment type is valid for the event type
618 --
619 Procedure check_enrollment_type(p_event_id in number,
620 p_person_id in number,
621 p_enrollment_type in varchar2,
622 p_booking_id in number) is
623 --
624 l_proc varchar2(72) := g_package||'oheck_enrollment_type';
625 l_event_type ota_events.price_basis%type;
626 cursor c1 is
627 select price_basis
628 from ota_events
629 where event_id = p_event_id;
630 --
631 Begin
632 hr_utility.set_location('Entering:'||l_proc, 5);
633 hr_utility.set_location('Event Id:'||to_char(p_event_id),5);
634 --
635 -- only perform the check if the person is specified
636 --
637 open c1;
638 fetch c1 into l_event_type;
639 close c1;
640 if l_event_type = 'C' then
641 --
642 if p_enrollment_type = 'S' then
643 if (p_person_id is not null) and
644 (ota_tdb_shd.g_old_rec.delegate_contact_id is null) and
645 (p_booking_id is not null) then
646 fnd_message.set_name ('OTA','OTA_13485_DELEGATE_MUST_NULL');
647 fnd_message.raise_error;
648 end if;
649 if p_person_id is null then
650 --
651 fnd_message.set_name ('OTA', 'OTA_13484_DELEGATE_NULL');
652 fnd_message.raise_error;
653 --
654 end if;
655 end if;
656 --
657 end if;
658 --
659 hr_utility.set_location(' Leaving:'||l_proc, 10);
660 End check_enrollment_type;
661 --
662 -- ----------------------------------------------------------------------------
663 -- |-------------------------< check_spon_del_validity >----------------------|
664 -- ----------------------------------------------------------------------------
665 --
666 -- PUBLIC
667 -- Description: Check whether delegate and sponsor are valid at the time
668 -- of the enrollment and at the time when the event started.
669 -- They must exist as employees around the time periods above.
670 --
671 -- Checks whether organization id is not null and whether :
672 -- if sponsor_person_id is not null then
673 -- sponsor_person_id must exist on
674 -- the day of the enrollment
675 -- if delegate_person_id is not null then
676 -- delegate_person_id must exist from before the
677 -- event start date and on the day of the enrollment
678 --
679 Procedure check_spon_del_validity (p_event_id in number,
680 p_organization_id in number,
681 p_delegate_person_id in number,
682 p_sponsor_person_id in number,
683 p_date_booking_placed in date) is
684 --
685 l_proc varchar2(72) := g_package||'check_spon_del_validity';
686 l_event_start_date date;
687 l_delegate_start_date date;
688 l_delegate_end_date date;
689 l_sponsor_start_date date;
690 l_sponsor_end_date date;
691 --
692 cursor c_event is
693 select course_start_date
694 from ota_events
695 where event_id = p_event_id;
696 --
697 /* Modified p_date_booking_placed to trunc(p_date_booking_placed)for bug 6402358*/
698
699 cursor c_delegate is
700 select effective_start_date, effective_end_date
701 from per_all_people_f
702 where person_id = p_delegate_person_id
703 and trunc(p_date_booking_placed)
704 between effective_start_date
705 and nvl(effective_end_date,hr_api.g_eot);
706 --
707 cursor c_sponsor is
708 select effective_start_date, effective_end_date
709 from per_all_people_f
710 where person_id = p_sponsor_person_id
711 and p_date_booking_placed
712 between effective_start_date
713 and nvl(effective_end_date,hr_api.g_eot);
714 --
715 Begin
716 hr_utility.set_location('Entering:'||l_proc, 5);
717 --
718 -- Only perform check if we are dealing with an internal enrollment
719 -- In other words check if p_organization_id is not null
720 --
721 if p_organization_id is not null then
722 --
723 -- Get event start_date
724 --
725 open c_event;
726 fetch c_event into l_event_start_date;
727 close c_event;
728 --
729 -- get delegate start_date
730 --
731 if p_delegate_person_id is not null then
732 open c_delegate;
733 fetch c_delegate into l_delegate_start_date,l_delegate_end_date;
734 if c_delegate%notfound then
735 fnd_message.set_name ('OTA','OTA_13505_DELEGATE_VALID');
736 fnd_message.raise_error;
737 end if;
738 close c_delegate;
739 --
740 -- Check if delegate is valid for event and date booking placed date
741 --
742 hr_utility.set_location('Delegate start date '||to_char(l_delegate_start_date),5);
743 hr_utility.set_location('Delegate end date '||to_char(l_delegate_end_date),5);
744 hr_utility.set_location('date Booking Placed '||to_char(p_date_booking_placed),5);
745 hr_utility.set_location('Event Start Date'||to_char(l_event_start_date),5);
746
747 /*
748 if (l_delegate_start_date > p_date_booking_placed) or
749 (nvl(l_delegate_end_date,hr_api.g_eot)
750 < p_date_booking_placed) or
751 (l_delegate_start_date > l_event_start_date) then
752 */
753 --
754 -- Delegate is not valid, display error
755 --
756 /*
757 fnd_message.set_name ('OTA','OTA_13505_DELEGATE_VALID');
758 fnd_message.raise_error;
759 end if;
760 */
761 end if;
762 --
763 -- get sponsor start_date
764 --
765 if p_sponsor_person_id is not null then
766 open c_sponsor;
767 fetch c_sponsor into l_sponsor_start_date,l_sponsor_end_date;
768 close c_sponsor;
769 --
770 -- Check if sponsor is valid for session dates
771 --
772 if (l_sponsor_start_date > p_date_booking_placed) or
773 (nvl(l_sponsor_end_date,hr_api.g_eot)
774 < p_date_booking_placed) then
775 --
776 -- Sponsor is not valid, display error
777 --
778 fnd_message.set_name ('OTA','OTA_13504_SPONSOR_VALID');
779 fnd_message.raise_error;
780 end if;
781 end if;
782 end if;
783 hr_utility.set_location(' Leaving:'||l_proc, 10);
784 End check_spon_del_validity;
785 --
786 -- ----------------------------------------------------------------------------
787 -- |-------------------------< check_customer_details >-----------------------|
788 -- ----------------------------------------------------------------------------
789 --
790 -- PUBLIC
791 -- Description: Check whether delegate_contact_id and contact_id
792 -- exist for a customer.
793 --
794 -- Checks whether customer_id is not null and whether :
795 -- if contact_id is not null then
796 -- contact_id must exist for the customer
797 -- if delegate_contact_id is not null then
798 -- delegate_contact_id must exist for the customer
799 --
800 Procedure check_customer_details (p_customer_id in number,
801 p_delegate_contact_id in number,
802 p_sponsor_contact_id in number) is
803 --
804 l_proc varchar2(72) := g_package||'check_customer_details';
805 l_dummy varchar2(30);
806 --
807
808
809 --arkashya Bug no: 2652833 replaced the select queries in c_delegate, c_sponsor to use HZ_ tables directly instead of ra_ views.
810
811 cursor c_delegate is
812 select 1
813
814 from HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
815 HZ_RELATIONSHIPS REL,
816 HZ_CUST_ACCOUNTS ROLE_ACCT
817
818 where ACCT_ROLE.PARTY_ID = REL.PARTY_ID
819 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
820 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
821 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
822 AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
823 AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID
824 AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_delegate_contact_id
825 AND ACCT_ROLE.CUST_ACCOUNT_ID = p_customer_id;
826
827
828
829 cursor c_sponsor is
830 select 1
831
832 from HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
833 HZ_RELATIONSHIPS REL,
834 HZ_CUST_ACCOUNTS ROLE_ACCT
835
836 where ACCT_ROLE.PARTY_ID = REL.PARTY_ID
837 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
838 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
839 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
840 AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
841 AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID
842 AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_sponsor_contact_id
843 AND ACCT_ROLE.CUST_ACCOUNT_ID = p_customer_id;
844
845
846 --
847 --
848 Begin
849 hr_utility.set_location('Entering:'||l_proc, 5);
850 --
851 -- Only perform check if we are dealing with an external enrollment
852 --
853 if p_customer_id is not null then
854 --
855 -- check if delegate_contact_id exists for customer
856 --
857 if p_delegate_contact_id is not null then
858 open c_delegate;
859 fetch c_delegate into l_dummy;
860 if not c_delegate%found then
861 --
862 -- Delegate contact is not valid, display error
863 --
864 fnd_message.set_name ('OTA','OTA_13507_DELEGATE_CONTACT_INV');
865 fnd_message.raise_error;
866 end if;
867 close c_delegate;
868 end if;
869 --
870 -- check if sponsor_contact_id is exists for customer
871 --
872 if p_sponsor_contact_id is not null then
873 open c_sponsor;
874 fetch c_sponsor into l_dummy;
875 if not c_sponsor%found then
876 --
877 -- Sponsor contact is not valid, display error
878 --
879 fnd_message.set_name ('OTA','OTA_13508_SPONSOR_CONTACT_INV');
880 fnd_message.raise_error;
881 end if;
882 close c_sponsor;
883 end if;
884 end if;
885 hr_utility.set_location(' Leaving:'||l_proc, 10);
886 End check_customer_details;
887
888 --
889 -- ----------------------------------------------------------------------------
890 -- |---------------------------< chk_old_event_changed >------------------------|
891 -- ----------------------------------------------------------------------------
892 -- This procedure will check whether the event id is changed.
893 Procedure chk_old_event_changed
894 (p_booking_id in number
895 ,p_event_id in number
896 ) is
897
898 l_old_event_id NUMBER;
899
900 CURSOR C_EVENT
901 IS
902 SELECT OLD_EVENT_ID,DAEMON_TYPE
903 FROM OTA_DELEGATE_BOOKINGS
904 WHERE
905 BOOKING_ID = p_booking_id;
906
907 l_proc varchar2(72) := g_package||'chk_event_changed';
908 BEGIN
909 hr_utility.set_location('Entering:'||l_proc, 5);
910 FOR C_EVENT_REC in C_EVENT
911 LOOP
912 hr_utility.set_location('Entering:'||l_proc, 20);
913 IF C_EVENT_REC.old_event_id is not null then
914 if p_event_id is not null and
915 p_event_id <> C_EVENT_REC.old_event_id then
916
917 fnd_message.set_name('OTA', 'OTA_13905_UPDATE_EVENT_FAILURE');
918 fnd_message.raise_error;
919
920
921 end if;
922 END IF;
923 END LOOP;
924 hr_utility.set_location('Leaving:'||l_proc, 30);
925 END chk_old_event_changed ;
926
927 --
928 -- ----------------------------------------------------------------------------
929 -- |---------------------------< check_commitment_date >------------------------|
930 -- ----------------------------------------------------------------------------
931 -- This procedure will check whether the event_end_date is after commitment_end_date.
932 -- If it is, then an error is raised.
933 Procedure check_commitment_date
934 (p_line_id in number
935 ,p_event_id in number
936 ) is
937 l_commitment_id ra_customer_trx_all.customer_trx_id%TYPE;
938 l_commitment_number ra_customer_trx_all.trx_number%TYPE;
939 l_commitment_end_date ra_customer_trx_all.end_date_commitment%TYPE;
940 l_commitment_start_date ra_customer_trx_all.start_date_commitment%TYPE;
941 l_event_end_date ota_events.course_end_date%TYPE;
942 --
943 CURSOR c_event
944 IS SELECT course_end_date
945 FROM ota_events
946 WHERE event_id = p_event_id;
947 l_proc VARCHAR2(72) := g_package||'check_commitment_date';
948 BEGIN
949 hr_utility.set_location('Entering:'||l_proc,5);
950 FOR c_event_rec IN c_event
951 LOOP
952 hr_utility.set_location('Entering:'||l_proc,20);
953 l_event_end_date := c_event_rec.course_end_date;
954 END LOOP;
955 ota_utility.get_commitment_detail(p_line_id,
956 l_commitment_id,
957 l_commitment_number,
958 l_commitment_start_date,
959 l_commitment_end_date);
960 IF l_commitment_end_date IS NOT NULL AND
961 l_event_end_date > l_commitment_end_date THEN
962 fnd_message.set_name('OTA','OTA_OM_COMMITMENT');
963 fnd_message.set_token('COMMITMENT_NUMBER',l_commitment_number);
964 fnd_message.set_token('COMMITMENT_END_DATE',fnd_date.date_to_chardate(l_commitment_end_date));
965 END IF;
966 hr_utility.set_location('Leaving:'||l_proc,30);
967 END check_commitment_date;
968
969 -- ----------------------------------------------------------------------------
970 -- |-------------------------< Check Location >----------------------------|
971 -- ----------------------------------------------------------------------------
972 --
973 -- Description: Check the event location when Inserting or updating. Compare the
974 -- country for event location to the country for OM org. If the country
975 -- is not the same, raise an error.
976 --
977 procedure Check_Location(p_event_id IN NUMBER,
978 p_om_org_id IN VARCHAR2) IS
979 --
980 CURSOR org_country_cr IS
981 SELECT org.name,
982 org.organization_id,
983 loc.country,
984 loc.location_id
985 FROM hr_all_organization_units_tl org,
986 hr_all_organization_units org1,
987 hr_locations_all loc
988 WHERE org.organization_id = org1.organization_id
989 AND loc.location_id(+) = org1.location_id
990 AND org.language = USERENV('LANG')
991 AND org1.organization_id = p_om_org_id;
992
993 CURSOR evt_country_cr IS
994 SELECT loc.country
995 FROM hr_locations_all loc
996 WHERE loc.location_id = ota_utility.get_event_location(p_event_id);
997 --
998 --
999 l_org_country hr_locations_all.country%TYPE := null;
1000 l_evt_country hr_locations_all.country%TYPE := null;
1001 l_proc VARCHAR2(72) := g_package||'check_location';
1002 --
1003 begin
1004 --
1005 --
1006 hr_utility.set_location('Entering:'|| l_proc, 5);
1007 --
1008 -- get country for OM org
1009 if p_om_org_id is not null then
1010 --
1011 FOR org_country IN org_country_cr
1012 LOOP
1013 l_org_country := org_country.country;
1014 END LOOP;
1015 end if;
1016 --
1017 --
1018 -- Get country for event
1019 --
1020 if p_event_id is not null then
1021 --
1022 FOR evt_country IN evt_country_cr
1023 LOOP
1024 l_evt_country := evt_country.country;
1025 END LOOP;
1026 end if;
1027 IF l_evt_country IS NOT NULL AND l_org_country IS NOT NULL THEN
1028 --check if the countries are same
1029 IF l_evt_country <> l_org_country THEN
1030 --
1031 fnd_message.set_name('OTA','OTA_13956_TDB_CHECK_LOCATION');
1032 fnd_message.raise_error;
1033 --
1034 END IF;
1035 END IF;
1036 --
1037 hr_utility.set_location('Leaving:'|| l_proc, 10);
1038 --
1039 end Check_location;
1040 --
1041 end ota_tdb_bus2;
[Home] [Help]