1 Package Body ota_tea_bus as
2 /* $Header: ottea01t.pkb 120.1 2005/06/09 01:16:02 jbharath noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_tea_bus.'; -- Global package name
9 --
10 --------------------------------------------------------------------
11 function tea_has_tdb (
12 p_event_id in number
13 ,p_customer_id in number
14 )
15 return boolean is
16 ---------------
17 cursor csr_tdb is
18 select 1
19 from ota_delegate_bookings tdb
20 where tdb.event_id = p_event_id
21 and tdb.customer_id = p_customer_id;
22 --
23 l_tdb_exist boolean;
24 l_dummy integer;
25 --
26 procedure chkp is
27 begin
28 hr_api.mandatory_arg_error(g_package,'Event_id',p_event_id);
29 hr_api.mandatory_arg_error(g_package,'Customer_id',p_customer_id);
30 end chkp;
31 -------------
32 begin
33 --
34 chkp;
35 --
36 open csr_tdb;
37 fetch csr_tdb into l_dummy;
38 l_tdb_exist := csr_tdb%found;
39 close csr_tdb;
40 --
41 return l_tdb_exist;
42 --
43 end tea_has_tdb;
44 --==============================================================
45 --==============================================================
46 procedure check_event
47 (
48 p_evt_id number
49 )
50 is
51 --
52 cursor csr_evt_type is
53 select event_type
54 from ota_events
55 where event_id = p_evt_id;
56 --
57 l_evt_type OTA_EVENTS.event_type%TYPE;
58 l_parent_exists boolean;
59 --
60 -------------
61 begin
62 --
63 if p_evt_id is null then
64 fnd_message.set_name('OTA','OTA_13222_GEN_MANDATORY_VALUE');
65 fnd_message.set_token('FIELD','Event');
66 fnd_message.set_token('OPTIONAL_EXTENSION','');
67 fnd_message.raise_error;
68 end if;
69 --
70 open csr_evt_type;
71 fetch csr_evt_type into l_evt_type;
72 l_parent_exists := csr_evt_type%found;
73 close csr_evt_type;
74 --
75 if not l_parent_exists then
76 fnd_message.set_name('OTA','OTA_13202_GEN_INVALID_KEY');
77 fnd_message.set_token('COLUMN_NAME','Event_id');
78 fnd_message.set_token('TABLE_NAME','OTA_EVENTS');
79 fnd_message.raise_error;
80 end if;
81 --
82 if not l_evt_type in ('AD-HOC','SCHEDULED','PROGRAMME') then
83 fnd_message.set_name('OTA','OTA_13288_TEA_WRONG_EVENT_TYPE');
84 fnd_message.raise_error;
85 end if;
86 --
87 end check_event;
88 --==============================================================
89 --==============================================================
90 procedure check_customer (p_customer_id number) is
91 --
92 cursor csr_customer is
93 select 1
94 from hz_parties party, hz_cust_accounts cust_acct
95 where cust_acct.party_id = party.party_id
96 and cust_acct.cust_account_id = p_customer_id;
97 --
98 l_customer_exists boolean;
99 l_dummy integer;
100 ---------------
101 begin
102 --
103 if p_customer_id is null then
104 fnd_message.set_name('OTA','OTA_13222_GEN_MANDATORY_VALUE');
105 fnd_message.set_token('FIELD','Customer');
106 fnd_message.set_token('OPTIONAL_EXTENSION','');
107 fnd_message.raise_error;
108 end if;
109 --
110 open csr_customer;
111 fetch csr_customer into l_dummy;
112 l_customer_exists := csr_customer%found;
113 close csr_customer;
114 --
115 if not l_customer_exists then
116 fnd_message.set_name('OTA','OTA_13202_GEN_INVALID_KEY');
117 fnd_message.set_token('COLUMN_NAME','CUST_ACCOUNT_ID');
118 fnd_message.set_token('TABLE_NAME','HZ_CUST_ACCOUNTS');
119 fnd_message.raise_error;
120 end if;
121 --
122 end check_customer;
123 --==============================================================
124 --==============================================================
125 procedure check_event_and_customer
126 (
127 p_tea_id number
128 ,p_evt_id number
129 ,p_cus_id number
130 )
131 is
132 --
133 cursor c_get_internal_association is
134 --
135 select 'X'
136 from ota_event_associations
137 where event_id = p_evt_id
138 and (organization_id is not null
139 or position_id is not null
140 or job_id is not null);
141 --
142 cursor csr_tea is
143 select 1
144 from ota_event_associations
145 where event_id = p_evt_id
146 and customer_id = p_cus_id
147 and (p_tea_id is null or event_association_id <> p_tea_id);
148 --
149 l_dummy number;
150 l_tea_exists boolean;
151 l_exists varchar2(30);
152 ------------
153 begin
154 --
155 check_event(p_evt_id);
156 --
157 check_customer(p_cus_id);
158 --
159 open c_get_internal_association;
160 fetch c_get_internal_association into l_exists;
161 if c_get_internal_association%found then
162 close c_get_internal_association;
163 fnd_message.set_name('OTA','OTA_13594_EVT_INT_ASSOCIATION');
164 fnd_message.raise_error;
165 end if;
166 close c_get_internal_association;
167 --
168 open csr_tea;
169 fetch csr_tea into l_dummy;
170 l_tea_exists := csr_tea%found;
171 close csr_tea;
172 --
173 if l_tea_exists then
174 fnd_message.set_name('OTA','OTA_13289_TEA_DUPLICATE_ROW');
175 fnd_message.raise_error;
176 end if;
177 --
178 end check_event_and_customer;
179 --==============================================================
180 --==============================================================
181 procedure check_event_and_assignment
182 (
183 p_event_association_id number
184 ,p_event_id number
185 ,p_organization_id number
186 ,p_job_id number
187 ,p_position_id number
188 ) is
189 --
190 l_exists varchar2(1);
191 --l_cross_business_group varchar2(1):= fnd_profile.value('HR_CROSS_BUSINESS_GROUP') ;
192 l_business_group_id ota_events.business_group_id%type := fnd_profile.value('OTA_HR_GLOBAL_BUSINESS_GROUP_ID');
193 --
194 cursor c_get_customer_association is
195 select 'X'
196 from ota_event_associations
197 where event_id = p_event_id
198 and customer_id is not null;
199 --
200 cursor get_duplicate_tea is
201 select null
202 from ota_event_associations
203 where event_id = p_event_id
204 and customer_id is null
205 and ((p_organization_id is null and
206 organization_id is null)
207 or (p_organization_id = organization_id))
208 and ((p_job_id is null and
209 job_id is null)
210 or (p_job_id = job_id))
211 and ((p_position_id is null and
212 position_id is null)
213 or (p_position_id = position_id))
214 and (p_event_association_id is null
215 or event_association_id <> p_event_association_id);
216 --
217 cursor check_org_job_pos is
218 select null
219 from ota_events evt
220 where event_id = p_event_id
221 and (p_organization_id is null or exists
222 (select null
223 from hr_organization_units
224 where organization_id = p_organization_id
225 and business_group_id = evt.business_group_id))
226 and (p_job_id is null or exists
227 (select null
228 from per_jobs
229 where job_id = p_job_id
230 and business_group_id = evt.business_group_id))
231 and (p_position_id is null or exists
232 (select null
233 from per_positions
234 where position_id = p_position_id
235 and business_group_id = evt.business_group_id));
236
237 /* For Globalization */
238 cursor check_org_job_pos_cross is
239 select null
240 from ota_events evt
241 where event_id = p_event_id
242 and (p_organization_id is null or exists
243 (select null
244 from hr_all_organization_units
245 where organization_id = p_organization_id
246 ))
247 and (p_job_id is null or exists
248 (select null
249 from per_jobs
250 where job_id = p_job_id
251 ))
252 and (p_position_id is null or exists
253 (select null
254 from per_all_positions
255 where position_id = p_position_id
256 ));
257
258 begin
259 --
260 open c_get_customer_association;
261 fetch c_get_customer_association into l_exists;
262 if c_get_customer_association%found then
263 close c_get_customer_association;
264 fnd_message.set_name('OTA','OTA_13595_EVT_CUST_ASSOCIATION');
265 fnd_message.raise_error;
266 end if;
267 close c_get_customer_association;
268 --
269
270 If l_business_group_id is not null then
271 open check_org_job_pos_cross;
272 fetch check_org_job_pos_cross into l_exists;
273 if check_org_job_pos_cross%notfound then
274 close check_org_job_pos_cross;
275 fnd_message.set_name('OTA','OTA_13529_TEA_ORG_JOB_POS');
276 fnd_message.raise_error;
277 end if;
278 close check_org_job_pos_cross;
279 else
280 open check_org_job_pos;
281 fetch check_org_job_pos into l_exists;
282 if check_org_job_pos%notfound then
283 close check_org_job_pos;
284 fnd_message.set_name('OTA','OTA_13529_TEA_ORG_JOB_POS');
285 fnd_message.raise_error;
286 end if;
287 close check_org_job_pos;
288 end if;
289 --
290 open get_duplicate_tea;
291 fetch get_duplicate_tea into l_exists;
292 if get_duplicate_tea%found then
293 close get_duplicate_tea;
294 fnd_message.set_name('OTA','OTA_13530_TEA_DUPLICATE_CRIT');
295 fnd_message.raise_error;
296 end if;
297 close get_duplicate_tea;
298 --
299 end;
300 --==============================================================
301 --==============================================================
302 --
303 -- PUBLIC
304 -- Description: Client side check for the creation of an event association
305 --
306 procedure client_check_event_customer (
307 p_event_association_id in number,
308 p_event_id in number,
309 p_customer_id in number)
310 is
311 ----------------
312 begin
313 --
314 if p_event_association_id is not null then
315 -- No creation
316 return;
317 end if;
318 --
319 check_event_and_customer (
320 p_tea_id => null,
321 p_evt_id => p_event_id,
322 p_cus_id => p_customer_id);
323 --
324 --insert_check_no_bookings (p_event_id, p_customer_id);
325 --
326 end client_check_event_customer;
327 --=======================================================
328 --
329 -- PUBLIC
330 -- Description: Check pre-purchase agreement does not exceed
331 -- limit + overdraft.
332 --
333 function check_pre_purchase_agreement (p_booking_deal_id in number,
334 p_event_id in number,
335 p_money_amount in number,
336 p_finance_header_id in number)
337 return boolean is
338 --
339 l_proc varchar2(80) := g_package||' check_pre_purchase_agreement';
340 --
341 l_overdraft_limit number := 0;
342 l_amount_so_far number := 0;
343 l_pre_purchase_limit number := 0;
344 l_warn boolean := false;
345 --
346 cursor c_overdraft_limit is
347 select nvl(overdraft_limit,0)
348 from ota_booking_deals
349 where booking_deal_id = p_booking_deal_id;
350 --
351 cursor c_amount_so_far is
352 select sum(nvl(money_amount,0))
353 from ota_finance_lines
354 where finance_header_id = p_finance_header_id
355 and booking_id is not null
356 and cancelled_flag <> 'Y'
357 and booking_deal_id <> p_booking_deal_id;
358 --
359 cursor c_pre_purchase_limit is
360 select sum(nvl(money_amount,0))
361 from ota_finance_lines
362 where booking_deal_id = p_booking_deal_id
363 and booking_id is null
364 and cancelled_flag <> 'Y';
365 --
366 begin
367 --
368 hr_utility.set_location('Entering '||l_proc,10);
369 --
370 -- Display values of all variables passed in
371 --
372 hr_utility.trace('p_booking_deal_id '||p_booking_deal_id);
373 hr_utility.trace('p_event_id '||p_event_id);
374 hr_utility.trace('p_money_amount '||p_money_amount);
375 hr_utility.trace('p_finance_header_id'||p_finance_header_id);
376 --
377 -- get pre_purchase_agreement_details
378 --
379 open c_pre_purchase_limit;
380 --
381 fetch c_pre_purchase_limit into l_pre_purchase_limit;
382 --
383 close c_pre_purchase_limit;
384 --
385 -- check if pre-purchase agreement has a limit
386 --
387 if l_pre_purchase_limit = 0 then
388 --
389 l_warn := true;
390 --
391 else
392 --
393 -- get overdraft limit
394 --
395 open c_overdraft_limit;
396 --
397 fetch c_overdraft_limit into l_overdraft_limit;
398 --
399 close c_overdraft_limit;
400 --
401 -- Get amount used so far for booking deal
402 --
403 open c_amount_so_far;
404 --
405 fetch c_amount_so_far into l_amount_so_far;
406 --
407 if c_amount_so_far%notfound or
408 l_amount_so_far is null then
409 --
410 l_amount_so_far := 0;
411 --
412 end if;
413 --
414 close c_amount_so_far;
415 --
416 -- Check if limit exceeded
417 --
418 hr_utility.trace(p_money_amount||' '||l_amount_so_far);
419 hr_utility.trace(l_overdraft_limit||' '||l_pre_purchase_limit);
420 if (p_money_amount + l_amount_so_far) >
421 (l_overdraft_limit + l_pre_purchase_limit) then
422 --
423 hr_utility.trace('Set Warning Flag');
424 l_warn := true;
425 --
426 end if;
427 --
428 end if;
429 --
430 -- Check if warning message needed
431 --
432 hr_utility.set_location('Leaving '||l_proc,10);
433 --
434 if l_warn then
435 --
436 return true;
437 --
438 else
439 --
440 return false;
441 --
442 end if;
443 --
444 end check_pre_purchase_agreement;
445 --=======================================================
446 procedure check_public_event_flag(p_event_id in number) is
447 --
448 l_public_event_flag varchar2(30);
449 --
450 cursor get_event is
451 select public_event_flag
452 from ota_events
453 where event_id = p_event_id;
454 --
455 begin
456 open get_event;
457 fetch get_event into l_public_event_flag;
458 close get_event;
459 --
460 if l_public_event_flag = 'N' then
461 null;
462 else
463 fnd_message.set_name('OTA','OTA_13531_TEA_UNRESTRICTED_EVT');
464 fnd_message.raise_error;
465 end if;
466 end check_public_event_flag;
467 --=======================================================
468 --=======================================================
469 procedure check_enrollments(p_event_id in number
470 ,p_event_association_id in number
471 ,p_organization_id in number default null
472 ,p_job_id in number default null
473 ,p_position_id in number default null
474 ,p_customer_id in number default null) is
475 --
476 l_exists varchar2(1);
477 l_proc varchar2(30) := 'check_enrollments';
478 --
479 cursor get_external_enrollments is
480 select null
481 from ota_delegate_bookings tdb
482 where tdb.event_id = p_event_id
483 and exists
484 (select null
485 from ota_event_associations tea
486 where tea.event_id = p_event_id
487 and decode(tea.event_association_id,p_event_association_id
488 ,p_customer_id,tea.customer_id) = tdb.customer_id);
489 --
490 cursor get_internal_enrollments is
491 select asg.organization_id,asg.job_id,asg.position_id
492 from ota_delegate_bookings tdb
493 , per_assignments_f asg
494 , ota_events evt
495 where evt.event_id = p_event_id
496 and evt.event_id = tdb.event_id
497 and tdb.delegate_assignment_id = asg.assignment_id
498 and tdb.date_booking_placed between
499 asg.effective_start_date and asg.effective_end_date;
500 --
501 cursor get_associations(l_organization_id number
502 ,l_job_id number
503 ,l_position_id number) is
504 select null
505 from ota_event_associations tea
506 where event_id = p_event_id
507 and event_association_id <> p_event_association_id
508 and nvl(organization_id,-1) = decode(organization_id,null,-1
509 ,nvl(l_organization_id,-1))
510 and nvl(job_id,-1) = decode(job_id,null,-1
511 ,nvl(l_job_id,-1))
512 and nvl(position_id,-1) = decode(position_id,null,-1
513 ,nvl(l_position_id,-1));
514 begin
515 hr_utility.set_location('Entering:'||l_proc, 5);
516 if p_customer_id is not null then
517 open get_external_enrollments;
518 fetch get_external_enrollments into l_exists;
519 if get_external_enrollments%notfound then
520 close get_external_enrollments;
521 fnd_message.set_name('OTA','OTA_13532_TEA_CRITERIA_UNMATCH');
522 fnd_message.raise_error;
523 end if;
524 close get_external_enrollments;
525 --
526 elsif p_customer_id is null then
527 hr_utility.trace('step 1');
528 for internal_enrollments in get_internal_enrollments loop
529 -- Test whether the Enrollment matches the new criteria
530 hr_utility.trace('step 2');
531 if ((p_organization_id is not null
532 and p_organization_id = internal_enrollments.organization_id)
533 or p_organization_id is null)
534 and ((p_job_id is not null
535 and p_job_id = internal_enrollments.job_id)
536 or p_job_id is null)
537 and ((p_position_id is not null
538 and p_position_id = internal_enrollments.position_id)
539 or p_position_id is null)
540 then null;
541 else
542 --
543 hr_utility.trace('step 3');
544 -- If it doesnt match then look for another set of criteria
545 open get_associations(internal_enrollments.organization_id
546 ,internal_enrollments.job_id
547 ,internal_enrollments.position_id);
548 hr_utility.trace('step 4');
549 fetch get_associations into l_exists;
550 hr_utility.trace('step 5');
551
552 if get_associations%notfound then
553 close get_associations;
554 fnd_message.set_name('OTA','OTA_13532_TEA_CRITERIA_UNMATCH');
555 fnd_message.raise_error;
556 end if;
557 hr_utility.trace('step 6');
558 close get_associations;
559 end if;
560 end loop;
561 end if;
562 hr_utility.set_location('Leaving:'||l_proc, 5);
563 end check_enrollments;
564 --=======================================================
565 --=======================================================
566 procedure delete_check_tdb (
567 p_event_association_id in number
568 )
569 is
570 ---------------
571 cursor csr_combination is
572 select event_id, customer_id
573 from ota_event_associations
574 where event_association_id = p_event_association_id;
575 --
576 l_event_id number;
577 l_customer_id number;
578 --
579 begin
580 --
581 open csr_combination;
582 fetch csr_combination into l_event_id,
583 l_customer_id;
584 if csr_combination%notfound then
585 fnd_message.set_name('OTA','OTA_13202_GEN_INVALID_KEY');
586 fnd_message.set_token('COLUMN_NAME','EVENT_ASSOCIATION_ID');
587 fnd_message.set_token('TABLE_NAME','OTA_EVENT_ASSOCIATIONS');
588 fnd_message.raise_error;
589 end if;
590 close csr_combination;
591 --
592 if l_customer_id is not null then
593 if tea_has_tdb(l_event_id,l_customer_id) then
594 fnd_message.set_name('OTA','OTA_13368_TEA_DEL_TDB_EXIST');
595 fnd_message.raise_error;
596 end if;
597 else
598 check_enrollments(l_event_id,
599 p_event_association_id,
600 -1,
601 -1,
602 -1,
603 null);
604 end if;
605 --
606 end delete_check_tdb;
607 -- ==========================================================================
608 -- =========================================================================
609 --
610 -- PUBLIC
611 --
612 function derive_standard_price (
613 p_event_id in number
614 ,p_business_group_id in number
615 ,p_currency_code in varchar2
616 ,p_booking_deal_type in varchar2
617 ,p_customer_total_delegates in number
618 ,p_session_date in date
619 )
620 return number is
621 -----------
622 cursor csr_price is
623 select ple.price
624 from ota_price_lists tpl,
625 ota_price_list_entries ple,
626 ota_events evt,
627 ota_vendor_supplies vsp
628 where
629 evt.event_id = p_event_id
630 and tpl.business_group_id = p_business_group_id
631 and tpl.currency_code = p_currency_code
632 and (
633 (p_booking_deal_type = 'P' and tpl.price_list_type = 'T')
634 or
635 (p_booking_deal_type <> 'P' and tpl.price_list_type = 'M')
636 or
637 (p_booking_deal_type is null and tpl.price_list_type = 'M')
638 )
639 and ple.price_list_id = tpl.price_list_id
640 and vsp.vendor_supply_id(+) = ple.vendor_supply_id
641 and ( evt.activity_version_id = ple.activity_version_id
642 or evt.activity_version_id = vsp.vendor_supply_id )
643 and ple.price_basis = 'C'
644 and p_customer_total_delegates between ple.minimum_attendees and ple.maximum_attendees
645 and (
646 (evt.course_start_date between ple.start_date and nvl(ple.end_date,hr_api.g_eot))
647 or
648 (evt.course_start_date is null and
649 p_session_date between ple.start_date and nvl(ple.end_date,hr_api.g_eot))
650 );
651 --
652 l_price number;
653 -----------
654 procedure chkp is
655 begin
656 hr_api.mandatory_arg_error(g_package,'Event_id',p_event_id);
657 hr_api.mandatory_arg_error(g_package,'Business group',p_business_group_id);
658 hr_api.mandatory_arg_error(g_package,'Currency code',p_currency_code);
659 hr_api.mandatory_arg_error(g_package,'Number of delegates',p_customer_total_delegates);
660 hr_api.mandatory_arg_error(g_package,'Session date',p_session_date);
661 end chkp;
662 -----------
663 begin
664 --
665 chkp;
666 --
667 open csr_price;
668 fetch csr_price into l_price;
669 if csr_price%notfound then
670 fnd_message.set_name('OTA','OTA_13413_TEA_PRICE_LIST_ENTRY');
671 fnd_message.raise_error;
672 end if;
673 close csr_price;
674 return l_price;
675 -----------
676 end derive_standard_price;
677 -- ==========================================================================
678 -- =========================================================================
679 --
680 -- PUBLIC
681 --
682 function number_of_delegates
683 (p_event IN number
684 ,p_customer IN number
685 )
686 return number
687 IS
688 --
689 --The following cursor sums the number of places for
690 --delegate bookings for an event for one customer.
691 --
692 Cursor delegate_count
693 IS
694 select sum(a.number_of_places)
695 from ota_delegate_bookings a
696 , ota_booking_status_types b
697 where a.booking_status_type_id = b.booking_status_type_id
698 and a.customer_id = p_customer
699 and a.event_id = p_event
700 and a.delegate_contact_id is null;
701 --
702 v_counter number := 0; --variable used to collect the result from the cursor
703 --
704 BEGIN
705 --
706 -- take the number of places from the event association
707 -- created customer bookings block.
708 --
709 open delegate_count;
710 --
711 fetch delegate_count into v_counter;
712 --
713 close delegate_count;
714 --
715 return v_counter;
716 --
717 END number_of_delegates;
718 -- ==========================================================================
719 -- =========================================================================
720 --
721 -- PUBLIC
722 --
723 function new_price_list_hit
724 (p_event_id IN number
725 ,p_business_group_id IN number
726 ,p_customer_total_delegates IN number
727 ,p_customer_total_delegates_old IN number
728 ,p_session_date IN date
729 ,p_booking_deal_type IN varchar2
730 ,p_booking_deal_id IN number
731 )
732 return boolean
733 IS
734 l_warn boolean := false;
735 l_dummy varchar2(30);
736 --
737 cursor c1 is
738 select null
739 from ota_price_list_entries ple,
740 ota_events evt,
741 ota_price_lists_v tpl
742 where evt.event_id = p_event_id
743 and tpl.business_group_id = p_business_group_id
744 and tpl.currency_code = evt.currency_code
745 and tpl.price_list_id = ple.price_list_id
746 and ple.price_basis = 'C'
747 and ple.activity_version_id = evt.activity_version_id
748 and p_customer_total_delegates
749 between ple.minimum_attendees
750 and ple.maximum_attendees
751 and p_customer_total_delegates_old
752 not between ple.minimum_attendees
753 and ple.maximum_attendees
754 and (
755 (evt.course_start_date
756 between ple.start_date
757 and nvl(ple.end_date,evt.course_start_date)
758 )
759 or
760 (evt.course_start_date is null
761 and p_session_date
762 between ple.start_date
763 and nvl(ple.end_date,p_session_date)
764 )
765 )
766 and (
767 (p_booking_deal_type is null
768 and tpl.price_list_type = 'M')
769 or
770 (p_booking_deal_type is not null
771 and exists (select null
772 from ota_booking_deals tbd
773 where tbd.booking_deal_id = p_booking_deal_id
774 and (tbd.price_list_id is null
775 or (tbd.price_list_id is not null
776 and tbd.price_list_id = tpl.price_list_id
777 )
778 )
779 )
780 )
781 )
782 order by tpl.name;
783 --
784 begin
785 --
786 open c1;
787 --
788 fetch c1 into l_dummy;
789 --
790 if c1%found then
791 --
792 l_warn := true;
793 --
794 end if;
795 --
796 close c1;
797 --
798 return l_warn;
799 --
800 end new_price_list_hit;
801 -- ----------------------------------------------------------------------------
802 -- |---------------------------< insert_validate >----------------------------|
803 -- ----------------------------------------------------------------------------
804 Procedure insert_validate(p_rec in ota_tea_shd.g_rec_type
805 ,p_association_type in varchar2) is
806 --
807 l_proc varchar2(72) := g_package||'insert_validate';
808 l_price_basis ota_events.price_basis%type; /* bug no 3476078 */
809 --
810 Begin
811 hr_utility.set_location('Entering:'||l_proc, 5);
812 --
813 -- Call all supporting business operations
814 --
815 if p_association_type = 'C' then
816 check_event_and_customer(p_tea_id => p_rec.event_association_id
817 ,p_evt_id => p_rec.event_id
818 ,p_cus_id => p_rec.customer_id);
819 --
820 else
821 /* bug no 3476078 */
822 select price_basis into l_price_basis from ota_events where event_id = p_rec.event_id;
823 if p_rec.customer_id is null and l_price_basis = 'C' then
824 fnd_message.set_name('OTA','OTA_443659_ASG_LRNR_CUST_ERR');
825 fnd_message.raise_error;
826 end if;
827 /* bug no 3476078 */
828 check_event_and_assignment(
829 p_event_association_id => p_rec.event_association_id
830 ,p_event_id => p_rec.event_id
831 ,p_organization_id => p_rec.organization_id
832 ,p_job_id => p_rec.job_id
833 ,p_position_id => p_rec.position_id
834 );
835 end if;
836 --
837 check_public_event_flag(p_rec.event_id);
838 --
839 hr_utility.set_location(' Leaving:'||l_proc, 10);
840 End insert_validate;
841 --
842 -- ----------------------------------------------------------------------------
843 -- |---------------------------< update_validate >----------------------------|
844 -- ----------------------------------------------------------------------------
845 Procedure update_validate(p_rec in ota_tea_shd.g_rec_type
846 ,p_association_type in varchar2) is
847 --
848 l_proc varchar2(72) := g_package||'update_validate';
849 l_price_basis ota_events.price_basis%type; /* bug no 3476078 */
850 --
851 l_customer_changed boolean :=
852 ota_general.value_changed( ota_tea_shd.g_old_rec.customer_id
853 , p_rec.customer_id );
854 l_organization_changed boolean :=
855 ota_general.value_changed( ota_tea_shd.g_old_rec.organization_id
856 , p_rec.organization_id );
857 l_job_changed boolean :=
858 ota_general.value_changed( ota_tea_shd.g_old_rec.job_id
859 , p_rec.job_id );
860 l_position_changed boolean :=
861 ota_general.value_changed( ota_tea_shd.g_old_rec.position_id
862 , p_rec.position_id );
863 --
864 Begin
865 hr_utility.set_location('Entering:'||l_proc, 5);
866 --
867 -- Call all supporting business operations
868 --
869 if p_association_type = 'C' then
870 if l_customer_changed then
871 check_event_and_customer(p_tea_id => p_rec.event_association_id
872 ,p_evt_id => p_rec.event_id
873 ,p_cus_id => p_rec.customer_id);
874 --
875 check_enrollments(p_event_id => p_rec.event_id
876 ,p_event_association_id => p_rec.event_association_id
877 ,p_customer_id => p_rec.customer_id);
878 end if;
879 else
880 /* bug no 3476078 */
881 select price_basis into l_price_basis from ota_events where event_id = p_rec.event_id;
882 if p_rec.customer_id is null and l_price_basis = 'C' then
883 fnd_message.set_name('OTA','OTA_443659_ASG_LRNR_CUST_ERR');
884 fnd_message.raise_error;
885 end if;
886 /* bug no 3476078 */
887
888 if l_organization_changed
889 or l_job_changed
890 or l_position_changed then
891 check_event_and_assignment(
892 p_event_association_id => p_rec.event_association_id
893 ,p_event_id => p_rec.event_id
894 ,p_organization_id => p_rec.organization_id
895 ,p_job_id => p_rec.job_id
896 ,p_position_id => p_rec.position_id
897 );
898 --
899 check_enrollments(p_event_id => p_rec.event_id
900 ,p_event_association_id => p_rec.event_association_id
901 ,p_organization_id => p_rec.organization_id
902 ,p_job_id => p_rec.job_id
903 ,p_position_id => p_rec.position_id);
904 end if;
905 end if;
906 --
907 hr_utility.set_location(' Leaving:'||l_proc, 10);
908 End update_validate;
909 --
910 -- ----------------------------------------------------------------------------
911 -- |---------------------------< delete_validate >----------------------------|
912 -- ----------------------------------------------------------------------------
913 Procedure delete_validate(p_rec in ota_tea_shd.g_rec_type) is
914 --
915 l_proc varchar2(72) := g_package||'delete_validate';
916 --
917 Begin
918 hr_utility.set_location('Entering:'||l_proc, 5);
919 --
920 -- Call all supporting business operations
921 --
922 delete_check_tdb(p_event_association_id => p_rec.event_association_id);
923 --
924 hr_utility.set_location(' Leaving:'||l_proc, 10);
925 End delete_validate;
926 --
927 end ota_tea_bus;
[Home] [Help]