[Home] [Help]
PACKAGE BODY: APPS.OTA_EVT_BUS
Source
1 PACKAGE BODY OTA_EVT_BUS as
2 /* $Header: otevt01t.pkb 120.13.12010000.2 2008/09/17 08:05:36 srgnanas ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_evt_bus.'; -- Global package name
9 --
10 -- A field to select 1 into ...
11 --
12 G_DUMMY number (1);
13
14 --
15 -- Working record
16 --
17 G_FETCHED_REC ota_evt_shd.g_rec_type;
18
19 -- ----------------------------------------------------------------------------
20 -- |-----------------------------< chk_ddf >----------------------------------|
21 -- ----------------------------------------------------------------------------
22 --
23 -- Description:
24 -- Validates all the Developer Descriptive Flexfield values.
25 --
26 -- Prerequisites:
27 -- All other columns have been validated. Must be called as the
28 -- last step from insert_validate and update_validate.
29 --
30 -- In Arguments:
31 -- p_rec
32 --
33 -- Post Success:
34 -- If the Developer Descriptive Flexfield structure column and data values
35 -- are all valid this procedure will end normally and processing will
36 -- continue.
37 --
38 -- Post Failure:
39 -- If the Developer Descriptive Flexfield structure column value or any of
40 -- the data values are invalid then an application error is raised as
41 -- a PL/SQL exception.
42 --
43 -- Access Status:
44 -- Internal Row Handler Use Only.
45 --
46 -- ----------------------------------------------------------------------------
47 procedure chk_ddf
48 (p_rec in ota_evt_shd.g_rec_type
49 ) is
50 --
51 l_proc varchar2(72) := g_package || 'chk_ddf';
52 --
53 begin
54 hr_utility.set_location('Entering:'||l_proc,10);
55 --
56 if ((p_rec.event_id is not null) and (
57 nvl(ota_evt_shd.g_old_rec.evt_information_category, hr_api.g_varchar2) <>
58 nvl(p_rec.evt_information_category, hr_api.g_varchar2) or
59 nvl(ota_evt_shd.g_old_rec.evt_information1, hr_api.g_varchar2) <>
60 nvl(p_rec.evt_information1, hr_api.g_varchar2) or
61 nvl(ota_evt_shd.g_old_rec.evt_information2, hr_api.g_varchar2) <>
62 nvl(p_rec.evt_information2, hr_api.g_varchar2) or
63 nvl(ota_evt_shd.g_old_rec.evt_information3, hr_api.g_varchar2) <>
64 nvl(p_rec.evt_information3, hr_api.g_varchar2) or
65 nvl(ota_evt_shd.g_old_rec.evt_information4, hr_api.g_varchar2) <>
66 nvl(p_rec.evt_information4, hr_api.g_varchar2) or
67 nvl(ota_evt_shd.g_old_rec.evt_information5, hr_api.g_varchar2) <>
68 nvl(p_rec.evt_information5, hr_api.g_varchar2) or
69 nvl(ota_evt_shd.g_old_rec.evt_information6, hr_api.g_varchar2) <>
70 nvl(p_rec.evt_information6, hr_api.g_varchar2) or
71 nvl(ota_evt_shd.g_old_rec.evt_information7, hr_api.g_varchar2) <>
72 nvl(p_rec.evt_information7, hr_api.g_varchar2) or
73 nvl(ota_evt_shd.g_old_rec.evt_information8, hr_api.g_varchar2) <>
74 nvl(p_rec.evt_information8, hr_api.g_varchar2) or
75 nvl(ota_evt_shd.g_old_rec.evt_information9, hr_api.g_varchar2) <>
76 nvl(p_rec.evt_information9, hr_api.g_varchar2) or
77 nvl(ota_evt_shd.g_old_rec.evt_information10, hr_api.g_varchar2) <>
78 nvl(p_rec.evt_information10, hr_api.g_varchar2) or
79 nvl(ota_evt_shd.g_old_rec.evt_information11, hr_api.g_varchar2) <>
80 nvl(p_rec.evt_information11, hr_api.g_varchar2) or
81 nvl(ota_evt_shd.g_old_rec.evt_information12, hr_api.g_varchar2) <>
82 nvl(p_rec.evt_information12, hr_api.g_varchar2) or
83 nvl(ota_evt_shd.g_old_rec.evt_information13, hr_api.g_varchar2) <>
84 nvl(p_rec.evt_information13, hr_api.g_varchar2) or
85 nvl(ota_evt_shd.g_old_rec.evt_information14, hr_api.g_varchar2) <>
86 nvl(p_rec.evt_information14, hr_api.g_varchar2) or
87 nvl(ota_evt_shd.g_old_rec.evt_information15, hr_api.g_varchar2) <>
88 nvl(p_rec.evt_information15, hr_api.g_varchar2) or
89 nvl(ota_evt_shd.g_old_rec.evt_information16, hr_api.g_varchar2) <>
90 nvl(p_rec.evt_information16, hr_api.g_varchar2) or
91 nvl(ota_evt_shd.g_old_rec.evt_information17, hr_api.g_varchar2) <>
92 nvl(p_rec.evt_information17, hr_api.g_varchar2) or
93 nvl(ota_evt_shd.g_old_rec.evt_information18, hr_api.g_varchar2) <>
94 nvl(p_rec.evt_information18, hr_api.g_varchar2) or
95 nvl(ota_evt_shd.g_old_rec.evt_information19, hr_api.g_varchar2) <>
96 nvl(p_rec.evt_information19, hr_api.g_varchar2) or
97 nvl(ota_evt_shd.g_old_rec.evt_information20, hr_api.g_varchar2) <>
98 nvl(p_rec.evt_information20, hr_api.g_varchar2) ))
99 or (p_rec.event_id is null) then
100 --
101 -- Only execute the validation if absolutely necessary:
102 -- a) During update, the structure column value or any
103 -- of the attribute values have actually changed.
104 -- b) During insert.
105 --
106 hr_dflex_utility.ins_or_upd_descflex_attribs
107 (p_appl_short_name => 'OTA'
108 ,p_descflex_name => 'OTA_EVENTS'
109 ,p_attribute_category => p_rec.evt_information_category
110 ,p_attribute1_name => 'EVT_INFORMATION1'
111 ,p_attribute1_value => p_rec.evt_information1
112 ,p_attribute2_name => 'EVT_INFORMATION2'
113 ,p_attribute2_value => p_rec.evt_information2
114 ,p_attribute3_name => 'EVT_INFORMATION3'
115 ,p_attribute3_value => p_rec.evt_information3
116 ,p_attribute4_name => 'EVT_INFORMATION4'
117 ,p_attribute4_value => p_rec.evt_information4
118 ,p_attribute5_name => 'EVT_INFORMATION5'
119 ,p_attribute5_value => p_rec.evt_information5
120 ,p_attribute6_name => 'EVT_INFORMATION6'
121 ,p_attribute6_value => p_rec.evt_information6
122 ,p_attribute7_name => 'EVT_INFORMATION7'
123 ,p_attribute7_value => p_rec.evt_information7
124 ,p_attribute8_name => 'EVT_INFORMATION8'
125 ,p_attribute8_value => p_rec.evt_information8
126 ,p_attribute9_name => 'EVT_INFORMATION9'
127 ,p_attribute9_value => p_rec.evt_information9
128 ,p_attribute10_name => 'EVT_INFORMATION10'
129 ,p_attribute10_value => p_rec.evt_information10
130 ,p_attribute11_name => 'EVT_INFORMATION11'
131 ,p_attribute11_value => p_rec.evt_information11
132 ,p_attribute12_name => 'EVT_INFORMATION12'
133 ,p_attribute12_value => p_rec.evt_information12
134 ,p_attribute13_name => 'EVT_INFORMATION13'
135 ,p_attribute13_value => p_rec.evt_information13
136 ,p_attribute14_name => 'EVT_INFORMATION14'
137 ,p_attribute14_value => p_rec.evt_information14
138 ,p_attribute15_name => 'EVT_INFORMATION15'
139 ,p_attribute15_value => p_rec.evt_information15
140 ,p_attribute16_name => 'EVT_INFORMATION16'
141 ,p_attribute16_value => p_rec.evt_information16
142 ,p_attribute17_name => 'EVT_INFORMATION17'
143 ,p_attribute17_value => p_rec.evt_information17
144 ,p_attribute18_name => 'EVT_INFORMATION18'
145 ,p_attribute18_value => p_rec.evt_information18
146 ,p_attribute19_name => 'EVT_INFORMATION19'
147 ,p_attribute19_value => p_rec.evt_information19
148 ,p_attribute20_name => 'EVT_INFORMATION20'
149 ,p_attribute20_value => p_rec.evt_information20
150 );
151 end if;
152 --
153 hr_utility.set_location(' Leaving:'||l_proc,20);
154 end chk_ddf;
155
156 --
157 --
158 --added for eBS by asud
159 -- ---------------------------------------------------------------------------
160 -- |----------------------< set_security_group_id >--------------------------|
161 -- ---------------------------------------------------------------------------
162 --
163 PROCEDURE set_security_group_id (p_event_id IN number,
164 p_associated_column1 IN varchar2
165 ) IS
166 --
167 -- Declare cursor
168 --
169 CURSOR csr_sec_grp IS
170 SELECT inf.org_information14
171 FROM hr_organization_information inf
172 ,ota_events evt
173 WHERE evt.event_id = p_event_id
174 AND inf.organization_id = evt.business_group_id
175 AND inf.org_information_context || '' = 'Business Group Information';
176
177
178
179 --
180 -- Declare local variables
181 --
182 l_security_group_id number;
183 l_proc varchar2(72) := g_package||'set_security_group_id';
184 --
185 BEGIN
186 --
187 hr_utility.set_location('Entering:'|| l_proc, 10);
188 --
189 -- Ensure that all the mandatory parameter are not null
190 --
191 hr_api.mandatory_arg_error
192 (p_api_name => l_proc
193 ,p_argument => 'event_id'
194 ,p_argument_value => p_event_id
195 );
196 --
197 OPEN csr_sec_grp;
198 FETCH csr_sec_grp INTO l_security_group_id;
199 --
200 IF csr_sec_grp%NOTFOUND THEN
201 --
202 CLOSE csr_sec_grp;
203 --
204 -- The primary key is invalid therefore we must error
205 --
206 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
207 hr_multi_message.add
208 (p_associated_column1 => NVL(p_associated_column1, 'EVENT_ID'));
209 --
210 ELSE
211
212 CLOSE csr_sec_grp;
213 --
214 -- Set the security_group_id in CLIENT_INFO
215 --
216 hr_api.set_security_group_id
217 (p_security_group_id => l_security_group_id
218 );
219
220 END IF;
221 hr_utility.set_location(' Leaving:'|| l_proc, 20);
222 --
223 END set_security_group_id;
224 --added for eBS by asud
225
226 -- Added For Bug 4348949
227 -- ---------------------------------------------------------------------------
228 -- |---------------------< return_legislation_code >-------------------------|
229 -- ---------------------------------------------------------------------------
230 -- {Start Of Comments}
231 --
232 -- Description:
233 -- Return the legislation code for a specific primary key value
234 --
235 -- Prerequisites:
236 -- The primary key identified by p_event_id
237 -- already exists.
238 --
239 -- In Arguments:
240 -- p_event_id
241 --
242 --
243 -- Post Success:
244 -- The business group's legislation code will be returned.
245 --
246 -- Post Failure:
247 -- An error is raised if the value does not exist.
248 --
249 -- Access Status:
250 -- Internal Development Use Only.
251 --
252 -- {End Of Comments}
253 -- ---------------------------------------------------------------------------
254 FUNCTION return_legislation_code
255 (p_event_id in number
256 ) RETURN varchar2
257 IS
258 --
259 -- Declare cursor
260 --
261 cursor csr_leg_code is
262 select legislation_code
263 from per_business_groups_perf pbg,
264 ota_events evt
265 where pbg.business_group_id = evt.business_group_id
266 and evt.event_id = p_event_id;
267
268
269 l_proc varchar2(72) := g_package||'return_legislation_code';
270 l_legislation_code varchar2(150);
271 --
272 Begin
273 hr_utility.set_location('Entering:'||l_proc, 5);
274 --
275 -- Ensure that all the mandatory parameters are not null
276 --
277 hr_api.mandatory_arg_error (p_api_name => l_proc,
278 p_argument => 'event_id',
279 p_argument_value => p_event_id);
280 open csr_leg_code;
281 fetch csr_leg_code into l_legislation_code;
282 if csr_leg_code%notfound then
283 close csr_leg_code;
284 --
285 -- The primary key is invalid therefore we must error out
286 --
287 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
288 hr_utility.raise_error;
289 end if;
290 --
291 close csr_leg_code;
292 return l_legislation_code;
293 --
294 hr_utility.set_location(' Leaving:'||l_proc, 10);
295 --
296 End return_legislation_code;
297 --
298 --
299 -- ----------------------------------------------------------------------------
300 -- -------------------------< INVALID_PARAMETER >------------------------------
301 -- ----------------------------------------------------------------------------
302 --
303 -- Handles 'Invalid parameter' events.
304 --
305 procedure INVALID_PARAMETER (
306 P_PROCEDURE_NAME in varchar2,
307 P_OPTIONAL_MESSAGE in varchar2
308 ) is
309 begin
310 --
311 FND_MESSAGE.SET_NAME (810, 'OTA_13205_GEN_PARAMETERS');
312 FND_MESSAGE.SET_TOKEN ('PROCEDURE', P_PROCEDURE_NAME);
313 FND_MESSAGE.SET_TOKEN ('SPECIFIC_MESSAGE', P_OPTIONAL_MESSAGE);
314 FND_MESSAGE.RAISE_ERROR;
315 --
316 end INVALID_PARAMETER;
317 --
318 -- ----------------------------------------------------------------------------
319 -- -------------------------< CHANGE_TO_WAIT_STATUS >--------------------------
320 -- ----------------------------------------------------------------------------
321 --
322 -- Handles change of event to wait status if student associations
323 -- are anything less than wait status.
324 --
325 function CHANGE_TO_WAIT_STATUS (p_business_group_id in number,
326 p_event_id in number)
327 return boolean is
328 l_booking_status_type ota_booking_status_types.type%type;
329 l_success boolean := true;
330 cursor c1 is
331 select BST.TYPE
332 FROM OTA_BOOKING_STATUS_TYPES BST ,
333 PER_ALL_PEOPLE_F DEL ,
334 OTA_EVENTS EVT ,
335 OTA_DELEGATE_BOOKINGS TDB
336 where tdb.business_group_id = p_business_group_id
337 and evt.event_id = p_event_id
338 AND TDB.BOOKING_STATUS_TYPE_ID = BST.BOOKING_STATUS_TYPE_ID
339 AND TDB.EVENT_ID = EVT.EVENT_ID
340 AND TDB.DELEGATE_PERSON_ID = DEL.PERSON_ID (+)
341 AND tdb.date_booking_placed between nvl(del.effective_start_date, tdb.date_booking_placed) and nvl(del.effective_end_date, tdb.date_booking_placed);
342 begin
343 open c1;
344 loop
345 fetch c1 into l_booking_status_type;
346 exit when c1%notfound;
347 if l_booking_status_type <> 'W' then
348 l_success := false;
349 end if;
350 end loop;
351 close c1;
352 if l_success then
353 return true;
354 else
355 return false;
356 end if;
357 end;
358 -- ----------------------------------------------------------------------------
359 -- |--------------------------------------------------------------------------|
360 -- ----------------------------------------------------------------------------
361 --
362 -- Returns the program title if the event is partof a program
363 --
364 function get_prog_title (p_event_id in number) return varchar2 is
365 --
366 l_event_title ota_events.title%type := '';
367 --
368 cursor c1 is
369 select a.title
370 from ota_events_tl a,
371 ota_program_memberships b
372 where a.event_id = b.program_event_id
373 and b.event_id = p_event_id
374 and a.language = USERENV('LANG');
375 --
376 begin
377 --
378 open c1;
379 --
380 fetch c1 into l_event_title;
384 return l_event_title;
381 --
382 close c1;
383 --
385 --
386 end get_prog_title;
387 --
388 -- ----------------------------------------------------------------------------
389 -- |------------------------< check_price_basis >------------------------------|
390 -- ----------------------------------------------------------------------------
391 --
392 -- PUBLIC
393 -- Description:
394 -- Checks whether the amount field has been filled in when we are dealing
395 -- with a student priced event.
396 --
397 procedure check_price_basis(p_event_id in ota_events.event_id%TYPE,
398 p_price_basis in ota_events.price_basis%TYPE,
399 p_parent_offering_id in ota_events.parent_offering_id%TYPE,
400 p_max_internal_attendees in ota_events.maximum_internal_attendees%TYPE) is
401 --
402 l_proc varchar2(30) := 'check_price_basis';
403 l_dummy VARCHAR2(30);
404
405 CURSOR dm_cr is
406 SELECT null
407 FROM ota_offerings o,
408 ota_category_usages c
409 WHERE o.offering_id = p_parent_offering_id
410 AND o.delivery_mode_id = c.category_usage_id
411 AND c.synchronous_flag = 'Y'
412 AND c.online_flag = 'N';
413
414 CURSOR evt_associations_cr(l_cust_associations VARCHAR2, l_non_cust_associations VARCHAR2) is
415 SELECT null
416 FROM ota_event_associations
417 WHERE event_id = p_event_id
418 AND (l_cust_associations = 'N' or customer_id is not null)
419 AND (l_non_cust_associations = 'N' or customer_id is null);
420
421 --
422
423 begin
424 --
425 hr_utility.set_location('Entering '||l_proc,10);
426 --
427 if ( p_price_basis is not null and p_price_basis ='N') then
428
429 OPEN evt_associations_cr('Y','N');
430 FETCH evt_associations_cr INTO l_dummy;
431 IF evt_associations_cr%found then
432 CLOSE evt_associations_cr;
433 fnd_message.set_name('OTA','OTA_443486_NO_CHARGE_CUST_EVT');
434 fnd_message.raise_error;
435 END IF;
436 CLOSE evt_associations_cr;
437
438 end if;
439
440 IF ( p_price_basis is not null AND
441 (p_price_basis = 'C' or
442 p_price_basis = 'O')
443 )
444 THEN
445 OPEN dm_cr;
446 FETCH dm_cr INTO l_dummy;
447 IF dm_cr%notfound then
448 CLOSE dm_cr;
449 fnd_message.set_name('OTA','OTA_443489_PRICE_BASIS_DM');
450 fnd_message.raise_error;
451 END IF;
452 CLOSE dm_cr;
453
454 IF (p_max_internal_attendees IS NULL OR
455 p_max_internal_attendees > 0 )
456 THEN
457 fnd_message.set_name('OTA','OTA_443487_PRICE_BASIS_C_O');
458 fnd_message.raise_error;
459 END IF;
460
461 OPEN evt_associations_cr('N','Y');
462 FETCH evt_associations_cr INTO l_dummy;
463 IF evt_associations_cr%found then
464 CLOSE evt_associations_cr;
465 fnd_message.set_name('OTA','OTA_443488_PRICE_BASIS_INT');
466 fnd_message.raise_error;
467 END IF;
468 CLOSE evt_associations_cr;
469
470 END IF;
471
472 --
473 hr_utility.set_location('Leaving '||l_proc,10);
474 --
475 EXCEPTION
476 WHEN app_exception.application_exception THEN
477 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.PRICE_BASIS',
478 p_associated_column2 => 'OTA_EVENTS.STANDARD_PRICE') THEN
479 hr_utility.set_location(' Leaving:'||l_proc, 40);
480 RAISE;
481 END IF;
482 hr_utility.set_location(' Leaving:'||l_proc, 50);
483
484 end check_price_basis;
485 --
486 -- ----------------------------------------------------------------------------
487 -- |---------------------------< check_pricing >------------------------------|
488 -- ----------------------------------------------------------------------------
489 --
490 -- PUBLIC
491 -- Description:
492 -- Checks whether the amount field has been filled in when we are dealing
493 -- with a student priced event.
494 --
495 procedure check_pricing(p_pricing_type in varchar2,
496 p_amount in number,p_currency_code in varchar2) is
497 --
498 l_proc varchar2(30) := 'check_pricing';
499 --
500 begin
501 --
502 hr_utility.set_location('Entering '||l_proc,10);
503 --
504 if ( p_pricing_type is not null and p_pricing_type ='S' and (p_amount is null or p_currency_code is null)) then
505 fnd_message.set_name('OTA','OTA_13440_EVT_CURR_PB');
506 fnd_message.raise_error;
507 end if;
508
509 if ( p_pricing_type is not null and p_pricing_type ='C' and (p_amount is not null or p_currency_code is null)) then
510 fnd_message.set_name('OTA','OTA_13440_EVT_CURR_PB');
511 fnd_message.raise_error;
512 end if;
513
514 --
515 hr_utility.set_location('Leaving '||l_proc,10);
516 --
517 EXCEPTION
518 WHEN app_exception.application_exception THEN
522 RAISE;
519 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.PRICE_BASIS',
520 p_associated_column2 => 'OTA_EVENTS.STANDARD_PRICE') THEN
521 hr_utility.set_location(' Leaving:'||l_proc, 40);
523 END IF;
524 hr_utility.set_location(' Leaving:'||l_proc, 50);
525
526 end check_pricing;
527 --
528 -- ----------------------------------------------------------------------------
529 -- |-------------------------< status_change_normal >-------------------------|
530 -- ----------------------------------------------------------------------------
531 --
532 -- PUBLIC
533 -- Description:
534 -- Checks whether any program members are wait-listed in which case the
535 -- program can not have a normal event status
536 --
537 Function status_change_normal (p_event_id in number) return boolean is
538 --
539 l_proc varchar2(30) := 'status_change_normal';
540 l_found boolean := false;
541 l_dummy varchar2(1);
542 --
543 cursor c1 is
544 select null
545 from ota_program_memberships mem,
546 ota_events evt
547 where mem.program_event_id = p_event_id
548 and mem.event_id = evt.event_id
549 and evt.event_status = 'P';
550 --
551 begin
552 --
553 hr_utility.set_location(' Entering:'||l_proc,10);
554 --
555 open c1;
556 --
557 fetch c1 into l_dummy;
558 if c1%found then
559 --
560 -- Planned entries exist as program members
561 --
562 l_found := true;
563 --
564 end if;
565 --
566 close c1;
567 --
568 hr_utility.set_location(' Leaving:'||l_proc,10);
569 return l_found;
570 --
571 end status_change_normal;
572 -- ----------------------------------------------------------------------------
573 -- |--------------------< enrollment_dates_event_valid >----------------------|
574 -- ----------------------------------------------------------------------------
575 --
576 -- PUBLIC
577 -- Description:
578 -- Validate the enrollment startdate, enddate against the event
579 -- startdate, enddate. The enrollment start date must be before
580 -- the event startdate but must not be after the event startdate.
581 -- The enrollment enddate must be within the event enddate.
582 --
583 Procedure enrollment_dates_event_valid (p_enrollment_start_date in out nocopy date,
584 p_enrollment_end_date in out nocopy date,
585 p_course_start_date in out nocopy date,
586 p_course_end_date in out nocopy date) is
587 --
588 l_proc varchar2(30) := 'enrollment_dates_event_valid';
589 l_course_start_date date;
590 l_course_end_date date;
591 begin
592 --
593 hr_utility.set_location(' Entering:'||l_proc,10);
594 l_course_start_date := p_course_start_date;
595 l_course_end_date := p_course_end_date;
596 if l_course_start_date is null then
597 l_course_start_date := p_enrollment_start_date;
598 end if;
599 if l_course_end_date is null then
600 l_course_end_date := p_enrollment_end_date;
601 end if;
602 --
603 -- Existing date for the parent startdate => Enrollment startdate
604 --
605 If l_course_start_date is not null Then
606 --
607 -- Course startdate is earlier than enrollment startdate
608 --
609 If nvl(l_course_start_date, hr_api.g_sot) < p_enrollment_start_date Then
610 --
611 --
612 -- ** TEMP ** Add error message with the following text.
613 fnd_message.set_name('OTA', 'OTA_13481_ENROL_START_AFTER');
614 fnd_message.raise_error;
615 --
616 End if;
617 End if;
618 --
619 -- Existing date for the parent enddate <= enrollment enddate
620 --
621 If l_course_end_date is not null Then
622 --
623 -- Enrollment startdate is earlier than course enddate
624 --
625 If nvl(p_enrollment_start_date, hr_api.g_sot) > l_course_end_date Then
626 --
627 -- ** TEMP ** Add error message with the following text.
628 fnd_message.set_name('OTA','OTA_13474_ENROLL_START_AFTER');
629 fnd_message.raise_error;
630 --
631 End if;
632 --
633 -- Enrollment enddate is later than course enddate
634 --
635 /*If nvl(p_enrollment_end_date, l_course_end_date) > l_course_end_date Then
636 --
637 -- ** TEMP ** Add error message with the following text.
638 fnd_message.set_name('OTA','OTA_13475_ENROLL_END_AFTER');
639 fnd_message.raise_error;
640 --
641 End if;*/
642 --
643 End if;
644 --
645 hr_utility.set_location(' Exitting:'||l_proc,10);
646 --
647 End enrollment_dates_event_valid;
648 --
649 -- ----------------------------------------------------------------------------
650 -- |--------------------< enrollment_after_event_end >------------------------|
651 -- ----------------------------------------------------------------------------
652 --
653 -- PUBLIC
654 -- Description:
655 -- Validate the enrollment end_date against the course enddate
656 -- and check if the enrollment end date is after the course enddate.
660 p_course_end_date in out nocopy date)
657 --
658 Function enrollment_after_event_end (
659 p_enrollment_end_date in out nocopy date,
661 return boolean is
662 --
663 l_proc varchar2(30) := 'enrollment_after_event_end';
664 l_course_end_date date;
665 begin
666 --
667 hr_utility.set_location(' Entering:'||l_proc,10);
668 l_course_end_date := p_course_end_date;
669 if l_course_end_date is null then
670 l_course_end_date := p_enrollment_end_date;
671 end if;
672 hr_utility.set_location('Course End Date '||to_char(l_course_end_date),10);
673 hr_utility.set_location('Enrolment End Date '||to_char(p_enrollment_end_date),10);
674 --
675 -- Existing date for the parent enddate <= enrollment enddate
676 --
677 If l_course_end_date is not null Then
678 --
679 -- Enrollment enddate is later than course enddate
680 --
681 If nvl(p_enrollment_end_date, l_course_end_date) > l_course_end_date Then
682 hr_utility.set_location('Enrollment > Course ',10);
683 return true;
684 End if;
685 End If;
686 hr_utility.set_location(' Exitting:'||l_proc,10);
687 return false;
688 --
689 End enrollment_after_event_end;
690 --
691 -- ----------------------------------------------------------------------------
692 -- |-------------------------< check_event_status >---------------------------|
693 -- ----------------------------------------------------------------------------
694 --
695 -- PUPLIC
696 -- Description:
697 -- Check that the event status is not planned and the end_date is not null.
698 --
699 Procedure check_event_status (p_event_status in varchar2,
700 p_course_end_date in date,
701 p_event_type in varchar2) is
702 --
703 l_proc varchar2(30) := 'check_event_status';
704 begin
705 --
706 hr_utility.set_location(' Entering:'||l_proc,10);
707 if (p_event_status <> 'P' AND p_event_status <> 'A') OR p_event_type <> 'SELFPACED' then
708 if p_course_end_date is null then
709 fnd_message.set_name('OTA','OTA_13480_END_DATE_NULL');
710 fnd_message.raise_error;
711 end if;
712 end if;
713 hr_utility.set_location(' Leaving:'||l_proc,10);
714 EXCEPTION
715 WHEN app_exception.application_exception THEN
716 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.EVENT_STATUS') THEN
717 hr_utility.set_location(' Leaving:'||l_proc, 40);
718 RAISE;
719 END IF;
720 hr_utility.set_location(' Leaving:'||l_proc, 50);
721
722 end check_event_status;
723 --
724 -- ----------------------------------------------------------------------------
725 -- -------------------------< RESOURCES_AFFECTED >-----------------------------
726 -- ----------------------------------------------------------------------------
727 --
728 -- Returns TRUE if the event itime has changed so that resources are
729 -- outside the times of the event.
730 --
731 function RESOURCES_AFFECTED (
732 P_EVENT_ID in number,
733 P_START_TIME in varchar2,
734 P_END_TIME in varchar2,
735 P_COURSE_START_DATE in date,
736 P_COURSE_END_DATE in date
737 ) return boolean is
738 --
739 l_proc varchar2(30) := 'resources_affected';
740 l_dummy varchar2(30);
741 l_found boolean := false;
742 --
743 cursor c1 is
744 select null
745 from ota_resource_bookings
746 where event_id = p_event_id
747 and (required_date_from = p_course_start_date
748 and required_start_time <
749 p_start_time
750 or
751 required_date_to = p_course_end_date
752 and required_end_time >
753 p_end_time);
754 --
755 begin
756 --
757 hr_utility.set_location(' Entering:'||l_proc,10);
758 --
759 open c1;
760 --
761 fetch c1 into l_dummy;
762 if c1%found then
763 --
764 l_found := true;
765 --
766 end if;
767 --
768 close c1;
769 --
770 hr_utility.set_location(' Leaving:'||l_proc,10);
771 --
772 return l_found;
773 --
774 end resources_affected;
775 --
776 -- ----------------------------------------------------------------------------
777 -- |-------------------------< check_public_event_flag >----------------------|
778 -- ----------------------------------------------------------------------------
779 --
780 -- PUPLIC
781 -- Description:
782 -- Ensure that
783 -- a. if the Public_event_flag is changed to 'N' then ensure there are
784 -- no enrollments already existing
785 --
786 -- b. if the Public_event_flag is changed to 'Y' then ensure there are
787 -- no event associations already existing
788 --
789 procedure check_public_event_flag(p_public_event_flag in varchar2
790 ,p_event_id in number) is
791 --
792 l_proc varchar2(30) := 'check_public_event_flag';
793 l_exists varchar2(1);
794 --
795 cursor get_enrollments is
796 select null
797 from ota_delegate_bookings
801 select null
798 where event_id = p_event_id;
799 --
800 cursor get_tea is
802 from ota_event_associations
803 where event_id = p_event_id;
804 begin
805 hr_utility.set_location(' Entering:'||l_proc,10);
806 --
807 if p_public_event_flag = 'N' then
808 open get_enrollments;
809 fetch get_enrollments into l_exists;
810 if get_enrollments%found then
811 close get_enrollments;
812 fnd_message.set_name('OTA','OTA_13526_RESTRICTED_FLAG');
813 fnd_message.set_token('STEP','1');
814 fnd_message.raise_error;
815 end if;
816 close get_enrollments;
817 --
818 elsif p_public_event_flag = 'Y' then
819 open get_tea;
820 fetch get_tea into l_exists;
821 if get_tea%found then
822 close get_tea;
823 fnd_message.set_name('OTA','OTA_13526_RESTRICTED_FLAG');
824 fnd_message.set_token('STEP','2');
825 fnd_message.raise_error;
826 end if;
827 close get_tea;
828 end if;
829 --
830 hr_utility.set_location(' Leaving:'||l_proc,10);
831 EXCEPTION
832 WHEN app_exception.application_exception THEN
833 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.PUBLIC_EVENT_FLAG') THEN
834 hr_utility.set_location(' Leaving:'||l_proc, 40);
835 RAISE;
836 END IF;
837 hr_utility.set_location(' Leaving:'||l_proc, 50);
838
839 end check_public_event_flag;
840 --
841 -- ----------------------------------------------------------------------------
842 -- |-----------------------< enrollment_dates_are_valid >-------------------------|
843 -- ----------------------------------------------------------------------------
844 --
845 -- PUBLIC
846 -- Description:
847 -- Validate the parent startdate, enddate and the child enrollment
848 -- startdate, enddate. The child start must be within the parent
849 -- startdate but must be before or equal to the parent startdate.
850 -- The child end date must be before or equal to the parent enddate.
851 --
852 Procedure enrollment_dates_are_valid( p_parent_offering_id in number,
853 p_enrollment_start_date in date,
854 p_enrollment_end_date in date) Is
855 --
856 l_proc varchar2(30) := 'enrollment_dates_are_valid';
857 l_start_date date;
858 l_end_date date;
859 cursor check_dates is
860 select start_date, end_date
861 from ota_offerings
862 where offering_id = p_parent_offering_id;
863 begin
864 --
865 hr_utility.set_location(' Entering:'||l_proc,10);
866 open check_dates;
867 loop
868 fetch check_dates into l_start_date, l_end_date;
869 exit when check_dates%notfound;
870 if l_start_date is null then
871 l_start_date := p_enrollment_start_date;
872 end if;
873 if l_end_date is null then
874 l_end_date := p_enrollment_end_date;
875 end if;
876 check_enrollment_dates(l_start_date,
877 l_end_date,
878 p_enrollment_start_date,
879 p_enrollment_end_date);
880 end loop;
881 close check_dates;
882
883 hr_utility.set_location(' Exitting:'||l_proc,10);
884 --
885 EXCEPTION
886 WHEN app_exception.application_exception THEN
887 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.ENROLMENT_START_DATE',
888 p_associated_column2 => 'OTA_EVENTS.ENROLMENT_END_DATE') THEN
889 hr_utility.set_location(' Leaving:'||l_proc, 40);
890 RAISE;
891 END IF;
892 hr_utility.set_location(' Leaving:'||l_proc, 50);
893
894 End enrollment_dates_are_valid;
895
896 /*--changes made for eBS by asud
897 Procedure enrollment_dates_are_valid( p_activity_version_id in number,
898 p_enrollment_start_date in date,
899 p_enrollment_end_date in date) Is
900 --
901 l_proc varchar2(30) := 'enrollment_dates_are_valid';
902 l_start_date date;
903 l_end_date date;
904 cursor check_dates is
905 select start_date, end_date
906 from ota_activity_versions_v
907 where activity_version_id = p_activity_version_id;
908 begin
909 --
910 hr_utility.set_location(' Entering:'||l_proc,10);
911 open check_dates;
912 loop
913 fetch check_dates into l_start_date, l_end_date;
914 exit when check_dates%notfound;
915 if l_start_date is null then
916 l_start_date := p_enrollment_start_date;
917 end if;
918 if l_end_date is null then
919 l_end_date := p_enrollment_end_date;
920 end if;
921 check_enrollment_dates(l_start_date,
922 l_end_date,
923 p_enrollment_start_date,
924 p_enrollment_end_date);
925 end loop;
926 close check_dates;
927
928 hr_utility.set_location(' Exitting:'||l_proc,10);
929 --
930 End enrollment_dates_are_valid;
931 */--changes made for eBS by asud
932 -------------------------------------------------------------------
933 -- ----------------------------------------------------------------------------
937 -- PUBLIC
934 -- |-----------------------< check_enrollment_dates >-------------------------|
935 -- ----------------------------------------------------------------------------
936 --
938 -- Description:
939 -- Validate the parent startdate, enddate and the enrollment startdate, enddate.
940 -- The child start and enddate have to be within the parent start and enddate.
941 --
942 Procedure check_enrollment_dates
943 (
944 p_par_start in date
945 ,p_par_end in date
946 ,p_child_start in date
947 ,p_child_end in date
948 ) Is
949 --
950 v_proc varchar2(72) := g_package||'check_enrollment_dates';
951 --
952 Begin
953 hr_utility.set_location('Entering:'||v_proc, 5);
954 --
955 -- Existing date for the parent startdate => Boundary parent startdate
956 --
957 --
958 -- Child startdate is earlier than parent startdate
959 -- This isn't a problem
960 --
961 -- Child enddate is earlier than parent startdate
962 -- This isn't a problem as this can happen.
963 --
964 --
965 -- Existing date for the parent enddate => Boundary parent enddate
966 --
967 If p_par_end is not null Then
968 --
969 -- Child startdate is later than parent enddate
970 --
971 If nvl( p_child_start, hr_api.g_sot) > p_par_end Then
972 --
973 -- ** TEMP ** Add error message with the following text.
974 fnd_message.set_name('OTA','OTA_13474_ENROLL_START_AFTER');
975 fnd_message.raise_error;
976 --
977 End if;
978 --
979 -- Child enddate is later than parent enddate
980 -- This isn't a problem
981 --
982 End if;
983 --
984 hr_utility.set_location(' Leaving:'||v_proc, 10);
985
986
987 End check_enrollment_dates;
988
989 -------------------------------------------------------------------
990 --
991 --
992 -- ----------------------------------------------------------------------------
993 -- -------------------------< chk_start_date >-----------------------
994 -- ----------------------------------------------------------------------------
995 --
996 -- Checks if start date is null when start time is not null
997 --
998 --
999 procedure chk_start_date( p_course_start_date IN ota_events.course_start_date%TYPE,
1000 p_course_start_time IN ota_events.course_start_time%TYPE)
1001 is
1002 l_proc varchar2(30) := 'chk_start_date';
1003 begin
1004 hr_utility.set_location('Entering:'||l_proc,10);
1005
1006 if p_course_start_time is not null and p_course_start_date is null
1007 then
1008 fnd_message.set_name('OTA','OTA_13065_CLASS_START_DATE');
1009 fnd_message.raise_error;
1010 end if;
1011 hr_utility.set_location('Leaving:'||l_proc,20);
1012
1013 EXCEPTION
1014 WHEN app_exception.application_exception THEN
1015 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.COURSE_START_TIME') THEN
1016 hr_utility.set_location(' Leaving:'||l_proc, 40);
1017 RAISE;
1018 END IF;
1019 hr_utility.set_location(' Leaving:'||l_proc, 50);
1020
1021 end chk_start_date;
1022 -------------------------------------------------------------------
1023 --
1024 -- ----------------------------------------------------------------------------
1025 -- ----------------------------------------------------------------------------
1026 -- -------------------------< CHK_END_DATE >-------------------------
1027 -- ----------------------------------------------------------------------------
1028 --
1029 -- Checks if end date is not null when end time is not null
1030 --
1031 --
1032 procedure CHK_END_DATE( p_course_end_date IN ota_events.course_end_date%TYPE,
1033 p_course_end_time IN ota_events.course_end_time%TYPE)
1034 is
1035 l_proc varchar2(30) := 'chk_end_date';
1036 begin
1037 hr_utility.set_location('Entering:'||l_proc,10);
1038 if p_course_end_time is not null and p_course_end_date is null
1039 then
1040 fnd_message.set_name('OTA','OTA_443613_CLASS_END_DATE');
1041 fnd_message.raise_error;
1042 end if;
1043 hr_utility.set_location('Leaving:'||l_proc,20);
1044
1045 EXCEPTION
1046 WHEN app_exception.application_exception THEN
1047 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.COURSE_END_TIME') THEN
1048 hr_utility.set_location(' Leaving:'||l_proc, 40);
1049 RAISE;
1050 END IF;
1051 hr_utility.set_location(' Leaving:'||l_proc, 50);
1052
1053 end CHK_END_DATE;
1054 -------------------------------------------------------------------
1055 --
1056 -- ----------------------------------------------------------------------------
1057 -- -------------------------< COURSE_DATES_ARE_VALID >-------------------------
1058 -- ----------------------------------------------------------------------------
1059 --
1060 -- Checks if scheduled events are within the parent activity start date
1061 -- and end date.
1062 -- N.B. Planned Events may have NULL Dates
1063 --
1064 --
1065
1066 procedure COURSE_DATES_ARE_VALID (p_parent_offering_id in number,
1067 p_course_start_date in date,
1071 --
1068 p_course_end_date in date,
1069 p_event_status in varchar2,
1070 p_event_type in varchar2) is
1072 l_proc varchar2(30) := 'course_dates_are_valid';
1073 l_start_date date;
1074 l_end_date date;
1075 l_evt_start_date date;
1076 l_evt_end_date date;
1077 --
1078 l_act_start_date ota_activity_versions.start_date%TYPE;
1079 l_act_end_date ota_activity_versions.end_date%TYPE;
1080 l_act_vrsn_id ota_activity_versions.activity_version_id%TYPE;
1081
1082 cursor check_dates is
1083 select start_date, end_date, activity_version_id
1084 from ota_offerings
1085 where offering_id = p_parent_offering_id;
1086
1087 cursor check_act_dates is
1088 select start_date, end_date
1089 from ota_activity_versions
1090 where activity_version_id = l_act_vrsn_id;
1091
1092 begin
1093 --
1094 hr_utility.set_location(' Entering:'||l_proc,10);
1095 hr_utility.trace('p_parent_offering_id'||p_parent_offering_id);
1096 hr_utility.trace('p_course_start_date'||p_course_start_date);
1097 hr_utility.trace('p_course_end_date'||p_course_end_date);
1098 hr_utility.trace('p_event_status'||p_event_status);
1099 /*
1100 if p_event_status <> 'P' then
1101 if p_course_start_date is null or
1102 p_course_end_date is null then
1103 fnd_message.set_name('OTA','OTA_13533_EVT_DATES_NULL');
1104 fnd_message.raise_error;
1105 end if;
1106 end if;
1107 */
1108 --
1109 if (p_event_status <> 'P' AND p_event_status <> 'A') then
1110 if p_event_type <> 'SELFPACED' then
1111 if p_course_end_date is null then
1112 fnd_message.set_name('OTA','OTA_13480_END_DATE_NULL');
1113 fnd_message.raise_error;
1114 end if;
1115 end if;
1116 if p_course_start_date is null then
1117 fnd_message.set_name('OTA','OTA_13533_EVT_DATES_NULL');
1118 fnd_message.raise_error;
1119 end if;
1120
1121 end if;
1122
1123 open check_dates;
1124 fetch check_dates into l_start_date, l_end_date, l_act_vrsn_id;
1125 close check_dates;
1126 --
1127 if l_start_date is null then
1128 l_start_date := hr_api.g_sot;
1129 end if;
1130 if l_end_date is null then
1131 l_end_date := hr_api.g_eot;
1132 end if;
1133 --
1134 l_evt_start_date := p_course_start_date;
1135 l_evt_end_date := p_course_end_date;
1136 --
1137 if p_event_status = 'P' then
1138 if p_course_start_date is null then
1139 l_evt_start_date := l_start_date;
1140 end if;
1141 if p_course_end_date is null then
1142 l_evt_end_date := l_end_date;
1143 end if;
1144 end if;
1145 /* commented out for bug#4069324
1146 if p_event_type = 'SELFPACED' then
1147 if p_course_start_date is null then
1148 l_evt_start_date := l_start_date;
1149 end if;
1150 end if;
1151 */
1152 --
1153 -- added for bug#4069324
1154 if l_evt_end_date is null then
1155 l_evt_end_date := hr_api.g_eot;
1156 end if;
1157 --
1158 -- Added extra conditions to handle development events
1159 --
1160 if l_evt_start_date < l_start_date or
1161 l_evt_start_date > l_end_date or
1162 l_evt_end_date > l_end_date or
1163 l_evt_end_date < l_start_date then
1164 fnd_message.set_name('OTA','OTA_13534_EVT_INVALID_DATES');
1165 fnd_message.raise_error;
1166 end if;
1167
1168 -- added for bug 3619563
1169 open check_act_dates;
1170 fetch check_act_dates into l_act_start_date, l_act_end_date;
1171 close check_act_dates;
1172 --
1173 if l_act_start_date is null then
1174 l_act_start_date := hr_api.g_sot;
1175 end if;
1176 if l_act_end_date is null then
1177 l_act_end_date := hr_api.g_eot;
1178 end if;
1179 --
1180 l_evt_start_date := p_course_start_date;
1181 l_evt_end_date := p_course_end_date;
1182 --
1183 if p_event_status = 'P' then
1184 if p_course_start_date is null then
1185 l_evt_start_date := l_act_start_date;
1186 end if;
1187 if p_course_end_date is null then
1188 l_evt_end_date := l_act_end_date;
1189 end if;
1190 end if;
1191 /* commented out for bug#4069324
1192 if p_event_type = 'SELFPACED' then
1193 if p_course_start_date is null then
1194 l_evt_start_date := l_act_start_date;
1195 end if;
1196 end if;
1197 */
1198 -- added for bug#4069324
1199 if l_evt_end_date is null then
1200 l_evt_end_date := hr_api.g_eot;
1201 end if;
1202 --
1203
1204 --
1205 -- Added extra conditions to handle development events
1206 --
1207 if l_evt_start_date < l_act_start_date or
1208 l_evt_start_date > l_act_end_date or
1209 l_evt_end_date > l_act_end_date or
1210 l_evt_end_date < l_act_start_date then
1211 fnd_message.set_name('OTA','OTA_13168_EVT_ACT_DATE_OVERLAP');
1212 fnd_message.raise_error;
1213 end if;
1214 --
1215 -- added for bug 3619563
1216 --
1217 hr_utility.set_location(' Exiting:'||l_proc,10);
1218 end COURSE_DATES_ARE_VALID;
1219 /*--changes made for eBS by asud
1223 p_event_status in varchar2) is
1220 procedure COURSE_DATES_ARE_VALID (p_activity_version_id in number,
1221 p_course_start_date in date,
1222 p_course_end_date in date,
1224 --
1225 l_proc varchar2(30) := 'course_dates_are_valid';
1226 l_start_date date;
1227 l_end_date date;
1228 l_evt_start_date date;
1229 l_evt_end_date date;
1230 --
1231 cursor check_dates is
1232 select start_date, end_date
1233 from ota_activity_versions
1234 where activity_version_id = p_activity_version_id;
1235 begin
1236 --
1237 hr_utility.set_location(' Entering:'||l_proc,10);
1238 hr_utility.trace('p_activity_version_id'||p_activity_version_id);
1239 hr_utility.trace('p_course_start_date'||p_course_start_date);
1240 hr_utility.trace('p_course_end_date'||p_course_end_date);
1241 hr_utility.trace('p_event_status'||p_event_status);
1242 if p_event_status <> 'P' then
1243 if p_course_start_date is null or
1244 p_course_end_date is null then
1245 fnd_message.set_name('OTA','OTA_13533_EVT_DATES_NULL');
1246 fnd_message.raise_error;
1247 end if;
1248 end if;
1249 --
1250 open check_dates;
1251 fetch check_dates into l_start_date, l_end_date;
1252 close check_dates;
1253 --
1254 if l_start_date is null then
1255 l_start_date := hr_api.g_sot;
1256 end if;
1257 if l_end_date is null then
1258 l_end_date := hr_api.g_eot;
1259 end if;
1260 --
1261 l_evt_start_date := p_course_start_date;
1262 l_evt_end_date := p_course_end_date;
1263 --
1264 if p_event_status = 'P' then
1265 if p_course_start_date is null then
1266 l_evt_start_date := l_start_date;
1267 end if;
1268 if p_course_end_date is null then
1269 l_evt_end_date := l_end_date;
1270 end if;
1271 end if;
1272 --
1273 -- Added extra conditions to handle development events
1274 --
1275 if l_evt_start_date < l_start_date or
1276 l_evt_start_date > l_end_date or
1277 l_evt_end_date > l_end_date or
1278 l_evt_end_date < l_start_date then
1279 fnd_message.set_name('OTA','OTA_13534_EVT_INVALID_DATES');
1280 fnd_message.raise_error;
1281 end if;
1282 --
1283 hr_utility.set_location(' Exiting:'||l_proc,10);
1284 end COURSE_DATES_ARE_VALID;
1285 --
1286 */--changes made for eBS by asud
1287 -- -----------------------------------------------------------------
1288 --
1289 -- ----------------------------------------------------------------------------
1290 -- -------------------------< BOOKINGS VALID >----------------------------
1291 -- ----------------------------------------------------------------------------
1292 --
1293 -- Checks if Delegate Bookings are within the Event Enrollment start date
1294 -- and end date.
1295 -- N.B. Planned Events may have NULL Dates
1296 --
1297 --
1298 procedure BOOKINGS_VALID (p_event_id in number,
1299 p_enrolment_start_date in date,
1300 p_enrolment_end_date in date,
1301 p_event_type in VARCHAR2 ,
1302 p_timezone IN VARCHAR2) is
1303 --
1304 l_proc varchar2(30) := 'Bookings_valid';
1305 l_dummy varchar2(30);
1306 --
1307 cursor check_dates is
1308 select 'X'
1309 from ota_delegate_bookings
1310 where event_id = p_event_id
1311 -- Modified for bug#5107347
1312 and ota_timezone_util.convert_date(trunc(date_booking_placed)
1313 , to_char(date_booking_placed, 'HH24:MI')
1314 , ota_timezone_util.get_server_timezone_code
1315 , p_timezone)
1316 not between nvl(p_enrolment_start_date,hr_api.g_sot) and nvl(p_enrolment_end_date +1,hr_api.g_eot);
1317 begin
1318 --
1319 hr_utility.set_location(' Entering:'||l_proc,10);
1320 --
1321 if p_event_type <> 'SESSION' or p_event_type <> 'PROGRAMME' then
1322 --
1323 open check_dates;
1324 fetch check_dates into l_dummy;
1325 if check_dates%found then
1326 close check_dates;
1327 fnd_message.set_name('OTA','OTA_13599_EVT_VALID_BOOKINGS');
1328 fnd_message.raise_error;
1329 end if;
1330 close check_dates;
1331 --
1332 end if;
1333 --
1334 hr_utility.set_location(' Exiting:'||l_proc,10);
1335 --
1336 end bookings_valid;
1337 --
1338 -- ----------------------------------------------------------------------------
1339 -- -------------------------< BOOKING DEAL VALID >----------------------------
1340 -- ----------------------------------------------------------------------------
1341 --
1342 -- Checks if Booking Deals are within the Event start date
1343 -- and end date.
1344 -- N.B. Planned Events may have NULL Dates
1345 --
1346 --
1347 procedure BOOKING_DEAL_VALID (p_event_id in number,
1348 p_course_start_date in date,
1349 p_course_end_date in date,
1350 p_event_status in varchar2) is
1351 --
1352 l_proc varchar2(30) := 'Booking_deal_valid';
1353 l_start_date date;
1354 l_end_date date;
1355 l_evt_start_date date;
1356 l_evt_end_date date;
1357 --
1361 where event_id = p_event_id;
1358 cursor c_check_dates is
1359 select start_date, end_date
1360 from ota_booking_deals
1362 --
1363 begin
1364 --
1365 hr_utility.set_location(' Entering:'||l_proc,10);
1366 --
1367 open c_check_dates;
1368 loop
1369 fetch c_check_dates into l_start_date, l_end_date;
1370 exit when c_check_dates%notfound;
1371 --
1372 if l_start_date is null then
1373 l_start_date := hr_api.g_sot;
1374 end if;
1375 if l_end_date is null then
1376 l_end_date := hr_api.g_eot;
1377 end if;
1378 --
1379 l_evt_start_date := p_course_start_date;
1380 l_evt_end_date := p_course_end_date;
1381 --
1382 if p_event_status = 'P' then
1383 if p_course_start_date is null then
1384 l_evt_start_date := l_start_date;
1385 end if;
1386 if p_course_end_date is null then
1387 l_evt_end_date := l_end_date;
1388 end if;
1389 end if;
1390 --
1391 if l_start_date < l_evt_start_date or
1392 l_end_date > l_evt_end_date then
1393 fnd_message.set_name('OTA','OTA_13600_EVT_VALID_BD');
1394 fnd_message.raise_error;
1395 end if;
1396 --
1397 end loop;
1398 --
1399 close c_check_dates;
1400 --
1401 hr_utility.set_location(' Exiting:'||l_proc,10);
1402 --
1403 end booking_deal_valid;
1404 --
1405 -- ----------------------------------------------------------------------------
1406 -- -------------------------< SESSION_VALID >----------------------------------
1407 -- ----------------------------------------------------------------------------
1408 --
1409 -- Checks if scheduled events are within the parent activity start date
1410 -- and end date.
1411 --
1412 --
1413 --
1414 procedure session_valid(P_EVENT_ID in number,
1415 P_COURSE_START_DATE in date ,
1416 P_COURSE_END_DATE in date) is
1417 l_proc varchar2(30) := 'session_valid';
1418 l_dummy varchar2(30);
1419 --
1420 cursor check_dates is
1421 select null
1422 from ota_events
1423 where parent_event_id = p_event_id
1424 and event_type = 'SESSION'
1425 and (course_start_date < nvl(p_course_start_date,hr_api.g_sot)
1426 or course_start_date > nvl(p_course_end_date,hr_api.g_eot));
1427 --
1428 /*
1429 cursor check_course_null_dates is
1430 select 'X'
1431 from ota_events
1432 where parent_event_id = p_event_id
1433 and event_type = 'SESSION';
1434 */
1435 begin
1436 --
1437 hr_utility.set_location(' Entering:'||l_proc,10);
1438 --
1439 /*
1440 open check_course_null_dates;
1441 fetch check_course_null_dates into l_dummy;
1442 if check_course_null_dates%found and
1443 (p_course_start_date is null or p_course_end_date is null) then
1444 --
1445 --
1446 fnd_message.set_name('OTA', 'OTA_13579_EVT_SESSION_DATES');
1447 fnd_message.raise_error;
1448 end if;
1449 close check_course_null_dates;
1450 --
1451 */
1452 open check_dates;
1453 fetch check_dates into l_dummy;
1454 if check_dates%found then
1455 --
1456 -- Warn of session date
1457 --
1458 fnd_message.set_name('OTA', 'OTA_13482_SESSION_CONFLICT');
1459 fnd_message.raise_error;
1460 end if;
1461 close check_dates;
1462 hr_utility.set_location(' Leaving:'||l_proc,10);
1463 end session_valid;
1464 --------------------------------------------------------------------------------
1465 --
1466 -- ----------------------------------------------------------------------------
1467 -- -------------------------< UNIQUE_EVENT_TITLE >-----------------------------
1468 -- ----------------------------------------------------------------------------
1469 --
1470 -- Returns TRUE if the event has a title which is unique within its
1471 -- business group. If the event id is not null, then the check avoids
1472 -- comparing the title against itself. Titles are compared regardless
1473 -- of case.
1474 --
1475 --
1476 --
1477 function UNIQUE_EVENT_TITLE (
1478 P_TITLE in varchar2,
1479 P_BUSINESS_GROUP_ID in number,
1480 P_PARENT_EVENT_ID in number,
1481 P_EVENT_ID in number default null
1482 ) return boolean is
1483 --
1484 W_PROC varchar2 (72)
1485 := G_PACKAGE || 'UNIQUE_EVENT_TITLE';
1486 W_TITLE_IS_UNIQUE boolean;
1487 --
1488 cursor C1 is
1489 select 1
1490 from OTA_EVENTS_VL EVT
1491 where EVT.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
1492 and ( (P_PARENT_EVENT_ID is null )
1493 or (EVT.PARENT_EVENT_ID = P_PARENT_EVENT_ID))
1494 and upper (EVT.TITLE) = upper (P_TITLE)
1495 and ( (P_EVENT_ID is null )
1496 or (EVT.EVENT_ID <> P_EVENT_ID));
1497 --
1498 begin
1499 --
1500 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC, 5);
1501 --
1502 -- Check arguments
1503 --
1504 HR_API.MANDATORY_ARG_ERROR (
1505 G_PACKAGE,
1506 'P_TITLE',
1507 P_TITLE);
1508 HR_API.MANDATORY_ARG_ERROR (
1509 G_PACKAGE,
1513 -- Unique ?
1510 'P_BUSINESS_GROUP_ID',
1511 P_BUSINESS_GROUP_ID);
1512 --
1514 --
1515 open C1;
1516 fetch C1
1517 into G_DUMMY;
1518 W_TITLE_IS_UNIQUE := C1%notfound;
1519 close C1;
1520 --
1521 HR_UTILITY.SET_LOCATION (W_PROC, 10);
1522 return W_TITLE_IS_UNIQUE;
1523 --
1524 end UNIQUE_EVENT_TITLE;
1525 --
1526 -- ----------------------------------------------------------------------------
1527 -- -----------------------< CHECK_TITLE_IS_UNIQUE >----------------------------
1528 -- ----------------------------------------------------------------------------
1529 --
1530 -- Validates the uniqueness of the event title (ignoring case).
1531 --
1532 procedure CHECK_TITLE_IS_UNIQUE (
1533 P_TITLE in varchar2,
1534 P_BUSINESS_GROUP_ID in number,
1535 P_PARENT_EVENT_ID in number,
1536 P_EVENT_ID in number default null,
1537 P_OBJECT_VERSION_NUMBER in number default null
1538 ) is
1539 --
1540 W_PROC varchar2 (72)
1541 := G_PACKAGE || 'CHECK_TITLE_IS_UNIQUE';
1542 --
1543 begin
1544 --
1545 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC, 5);
1546 --
1547 -- Do not perform the uniqueness check unless inserting, or updating
1548 -- with a value different from the current value (and not just changing
1549 -- case)
1550 --
1551 if (not ( (OTA_EVT_SHD.API_UPDATING (P_EVENT_ID, P_OBJECT_VERSION_NUMBER))
1552 and (upper (P_TITLE) = upper (OTA_EVT_SHD.G_OLD_REC.TITLE) ))) then
1553 --
1554 if (not UNIQUE_EVENT_TITLE (
1555 P_TITLE => P_TITLE,
1556 P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID,
1557 P_PARENT_EVENT_ID => P_PARENT_EVENT_ID,
1558 P_EVENT_ID => P_EVENT_ID)) then
1559 OTA_EVT_SHD.CONSTRAINT_ERROR ('OTA_EVENTS_UK2');
1560 end if;
1561 --
1562 end if;
1563 --
1564 HR_UTILITY.SET_LOCATION (' Leaving:' || W_PROC, 10);
1565 --
1566 EXCEPTION
1567 WHEN app_exception.application_exception THEN
1568 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.TITLE') THEN
1569 hr_utility.set_location(' Leaving:'||W_PROC, 40);
1570 RAISE;
1571 END IF;
1572 hr_utility.set_location(' Leaving:'||W_PROC, 50);
1573
1574 end CHECK_TITLE_IS_UNIQUE;
1575 --
1576 -- ----------------------------------------------------------------------------
1577 -- ---------------------< check_session_time >-------------------------
1578 -- ----------------------------------------------------------------------------
1579 -- Added for Bug 3403113
1580 -- This procedure checks if the session time is between the parent start
1581 -- and end time.
1582 --
1583 --------------------------------------------------------------------------------
1584 PROCEDURE check_session_time ( p_parent_event_id IN NUMBER,
1585 p_session_start_date IN DATE,
1586 p_session_start_time IN VARCHAR2,
1587 p_session_end_date IN DATE,
1588 p_session_end_time IN VARCHAR2,
1589 p_event_id IN NUMBER default null,
1593 SELECT course_start_date,
1590 p_object_version_number IN NUMBER default null) is
1591 --
1592 CURSOR get_event_dates_cr is
1594 course_start_time,
1595 course_end_date,
1596 course_end_time
1597 FROM ota_events
1598 WHERE event_id = p_parent_event_id;
1599
1600 l_course_start_date ota_events.course_start_date%type;
1601 l_course_start_time ota_events.course_start_time%type;
1602 l_course_end_date ota_events.course_start_date%type;
1603 l_course_end_time ota_events.course_start_time%type;
1604
1605 w_proc constant varchar2(72) := G_PACKAGE||'check_session_time';
1606
1607 BEGIN
1608
1609 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC, 5);
1610 --
1611 -- Check parameters
1612 --
1613 HR_API.MANDATORY_ARG_ERROR (
1614 P_API_NAME => G_PACKAGE,
1615 P_ARGUMENT => 'P_PARENT_EVENT_ID',
1616 P_ARGUMENT_VALUE => P_PARENT_EVENT_ID);
1617 --
1618 --
1619 --
1620 HR_UTILITY.TRACE ('Parent: ' || to_char (P_PARENT_EVENT_ID));
1621 --bug 3451221
1622 IF (p_session_start_date IS NOT NULL AND
1623 p_session_start_time IS NOT NULL AND
1624 p_session_end_time IS NOT NULL) THEN
1625
1626 IF ( substr(p_session_start_time,1,2) > substr(p_session_end_time,1,2) ) then
1627 fnd_message.set_name('OTA', 'OTA_13064_EVT_SSN_TIME');
1628 fnd_message.raise_error;
1629 ELSIF (( substr(p_session_start_time,1,2) = substr(p_session_end_time,1,2) ) AND
1630 ( substr(p_session_start_time,4,2) > substr(p_session_end_time,4,2) )) then
1631 fnd_message.set_name('OTA', 'OTA_13064_EVT_SSN_TIME');
1632 fnd_message.raise_error;
1633 END IF;
1634
1635 END IF;
1636 --Bug 3451221
1637 OPEN get_event_dates_cr;
1638 LOOP
1639 FETCH get_event_dates_cr INTO l_course_start_date,
1640 l_course_start_time,
1641 l_course_end_date,
1642 l_course_end_time;
1643 EXIT WHEN get_event_dates_cr%NOTFOUND;
1644 END LOOP;
1645 CLOSE get_event_dates_cr;
1646
1647 -- If the Course , Session Start date are the same and
1648 -- the two start times are not null then check for correct time entries.
1649 --
1650 IF l_course_start_date = p_session_start_date THEN
1651 IF (l_course_start_time IS NOT NULL) AND
1652 (p_session_start_time IS NOT NULL) THEN
1653 IF substr(l_course_start_time ,1,2) =
1654 substr(p_session_start_time ,1,2) THEN
1655 IF substr(l_course_start_time ,4,2) >
1656 substr(p_session_start_time ,4,2) THEN
1657 fnd_message.set_name('OTA','OTA_13563_EVT_SESSION_TIME');
1658 fnd_message.raise_error;
1659 END IF;
1660 ELSIF substr(l_course_start_time ,1,2) >
1661 substr(p_session_start_time ,1,2) THEN
1662 fnd_message.set_name('OTA','OTA_13563_EVT_SESSION_TIME');
1663 fnd_message.raise_error;
1664 END IF;
1665 END IF;
1666 END IF;
1667
1668 IF l_course_end_date = p_session_start_date THEN
1669 IF (l_course_end_time IS NOT NULL) AND
1670 (p_session_end_time IS NOT NULL) THEN
1671 IF substr(l_course_end_time ,1,2) =
1672 substr(p_session_end_time ,1,2) THEN
1673 IF substr(l_course_end_time ,4,2) <
1674 substr(p_session_end_time ,4,2) THEN
1675 fnd_message.set_name('OTA','OTA_13563_EVT_SESSION_TIME');
1676 fnd_message.raise_error;
1677 END IF;
1678 ELSIF substr(l_course_end_time ,1,2) <
1679 substr(p_session_end_time ,1,2) THEN
1680 fnd_message.set_name('OTA','OTA_13563_EVT_SESSION_TIME');
1681 fnd_message.raise_error;
1682 END IF;
1683 END IF;
1684 END IF;
1685 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC, 10);
1686
1687 EXCEPTION
1688 WHEN app_exception.application_exception THEN
1689 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.COURSE_START_TIME') THEN
1690 hr_utility.set_location(' Leaving:'||w_proc, 60);
1691 RAISE;
1692 END IF;
1693 hr_utility.set_location(' Leaving:'||w_proc, 70);
1694 END check_session_time;
1695
1696 --------------------------------------------------------------------------------
1697 -- ----------------------------------------------------------------------------
1698 -- ---------------------< COURSE_DATES_SPAN_SESSIONS >-------------------------
1699 -- ----------------------------------------------------------------------------
1700 --
1701 -- Returns TRUE if the course dates for an event still span the dates of
1702 -- its sessions. This function is overloaded so that one can check either
1703 -- a new session date is valid or that updates to the course dates will
1704 -- not invalidate any sessions.
1705 --
1706 -- This version of the function checks that a new or updated session date
1707 -- lies within the course dates of its parent event.
1708 --
1709 function COURSE_DATES_SPAN_SESSIONS (
1710 P_PARENT_EVENT_ID in number,
1711 P_NEW_SESSION_DATE in date
1715 := G_PACKAGE || 'COURSE_DATES_SPAN_SESSIONS';
1712 ) return boolean is
1713 --
1714 W_PROC varchar2 (72)
1716 --
1717 L_COURSE_START_DATE date;
1718 L_COURSE_END_DATE date;
1719 L_COURSE_SPANS_SESSIONS boolean;
1720 --
1721 begin
1722 --
1723 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC, 5);
1724 --
1725 -- Check parameters
1726 --
1727 HR_API.MANDATORY_ARG_ERROR (
1728 P_API_NAME => G_PACKAGE,
1729 P_ARGUMENT => 'P_PARENT_EVENT_ID',
1730 P_ARGUMENT_VALUE => P_PARENT_EVENT_ID);
1731 --
1732 if (P_NEW_SESSION_DATE is not null) then
1733 OTA_EVT_SHD.GET_COURSE_DATES (
1734 P_PARENT_EVENT_ID,
1735 L_COURSE_START_DATE,
1736 L_COURSE_END_DATE);
1737 --
1738 HR_UTILITY.TRACE ('Start date: ' || to_char (L_COURSE_START_DATE));
1739 HR_UTILITY.TRACE (' End date: ' || to_char (L_COURSE_END_DATE));
1740 --
1741 -- check if course start and end date are null
1742 -- if null then set them to start and end of time
1743 --
1744 if l_course_start_date is null or l_course_end_date is null then
1745 fnd_message.set_name('OTA','OTA_13579_EVT_SESSION_DATES');
1746 fnd_message.raise_error;
1747 end if;
1748 /* if l_course_start_date is null then
1749 l_course_start_date := hr_api.g_sot;
1750 end if;
1751 if l_course_end_date is null then
1752 l_course_end_date := hr_api.g_eot;
1753 end if; */
1754 L_COURSE_SPANS_SESSIONS :=
1755 ( (L_COURSE_START_DATE is not null)
1756 and (L_COURSE_END_DATE is not null)
1757 and (P_NEW_SESSION_DATE between L_COURSE_START_DATE
1758 and L_COURSE_END_DATE));
1759 else
1760 L_COURSE_SPANS_SESSIONS := true;
1761 end if;
1762 --
1763 return L_COURSE_SPANS_SESSIONS;
1764 --
1765 end COURSE_DATES_SPAN_SESSIONS;
1766 --
1767 -- This version of the function checks that updated course dates do not
1768 -- invalidate any of the event's sessions.
1769 --
1770 function COURSE_DATES_SPAN_SESSIONS (
1771 p_event_id number,
1772 p_course_start_date date,
1773 p_course_end_date date) return boolean is
1774 --
1775 W_PROC constant varchar2(72) := G_PACKAGE||'course_dates_span_sessions';
1776 --
1777 l_sessions_invalidated boolean;
1778 --
1779 cursor csr_invalid_sessions is
1780 select 1
1781 from ota_events
1782 where parent_event_id = p_event_id
1783 and event_type = 'SESSION'
1784 and course_start_date
1785 not between p_course_start_date
1786 and p_course_end_date;
1787 --
1788 begin
1789 --
1790 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC, 5);
1791 --
1792 -- Check parameters
1793 --
1794 HR_API.MANDATORY_ARG_ERROR (
1795 G_PACKAGE,
1796 'p_event_id',
1797 p_event_id);
1798 --
1799 if p_course_start_date is null or p_course_end_date is null then
1800 l_sessions_invalidated := TRUE;
1801 else
1802 open csr_invalid_sessions;
1803 fetch csr_invalid_sessions into g_dummy;
1804 l_sessions_invalidated := csr_invalid_sessions%found;
1805 close csr_invalid_sessions;
1806 end if;
1807 --
1808 HR_UTILITY.SET_LOCATION (W_PROC,10);
1809 --
1810 return NOT l_sessions_invalidated;
1811 --
1812 end course_dates_span_sessions;
1813 --
1814 -- ----------------------------------------------------------------------------
1815 -- ---------------------< check_class_session_times >--------------------------
1816 -- ----------------------------------------------------------------------------
1817 -- Added for Bug 3622035
1818 -- This procedure checks if the session time is between the parent start
1819 -- and end time.
1820 --
1821 --------------------------------------------------------------------------------
1822 PROCEDURE check_class_session_times ( p_event_id IN ota_events.event_id%TYPE,
1823 p_course_start_date IN ota_events.course_start_date%TYPE,
1824 p_course_start_time IN ota_events.course_start_time%TYPE,
1825 p_course_end_date IN ota_events.course_end_date%TYPE,
1826 p_course_end_time IN ota_events.course_end_time%TYPE) is
1827 --
1828 CURSOR get_ssn_times_cr is
1829 SELECT course_start_date,
1830 course_start_time,
1831 course_end_date,
1832 course_end_time
1833 FROM ota_events
1834 WHERE parent_event_id = p_event_id
1835 AND event_type = 'SESSION';
1836
1837 l_ssn_start_date ota_events.course_start_date%type;
1838 l_ssn_start_time ota_events.course_start_time%type;
1839 l_ssn_end_date ota_events.course_start_date%type;
1840 l_ssn_end_time ota_events.course_start_time%type;
1841
1842 w_proc constant varchar2(72) := G_PACKAGE||'check_class_session_times';
1843
1844 BEGIN
1845
1846 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC, 5);
1847 -- --
1848 --
1849 --
1850 HR_UTILITY.TRACE ('Parent: ' || to_char (P_EVENT_ID));
1851
1852 OPEN get_ssn_times_cr;
1853 LOOP
1854 FETCH get_ssn_times_cr INTO l_ssn_start_date,
1855 l_ssn_start_time,
1856 l_ssn_end_date,
1860 -- If the Course , Session Start date are the same and
1857 l_ssn_end_time;
1858 EXIT WHEN get_ssn_times_cr%NOTFOUND;
1859
1861 -- the two start times are not null then check for correct time entries.
1862 --
1863 IF p_course_start_date = l_ssn_start_date THEN
1864 IF (p_course_start_time IS NOT NULL) AND
1865 (l_ssn_start_time IS NOT NULL) THEN
1866 IF substr(p_course_start_time ,1,2) =
1867 substr(l_ssn_start_time ,1,2) THEN
1868 IF substr(p_course_start_time ,4,2) >
1869 substr(l_ssn_start_time ,4,2) THEN
1870 fnd_message.set_name('OTA','OTA_13563_EVT_SESSION_TIME');
1871 fnd_message.raise_error;
1872 END IF;
1873 ELSIF substr(p_course_start_time ,1,2) >
1874 substr(l_ssn_start_time ,1,2) THEN
1875 fnd_message.set_name('OTA','OTA_13563_EVT_SESSION_TIME');
1876 fnd_message.raise_error;
1877 END IF;
1878 END IF;
1879 END IF;
1880
1881 IF p_course_end_date = l_ssn_start_date THEN
1882 IF (p_course_end_time IS NOT NULL) AND
1883 (l_ssn_end_time IS NOT NULL) THEN
1884 IF substr(p_course_end_time ,1,2) =
1885 substr(l_ssn_end_time ,1,2) THEN
1886 IF substr(p_course_end_time ,4,2) <
1887 substr(l_ssn_end_time ,4,2) THEN
1888 fnd_message.set_name('OTA','OTA_13563_EVT_SESSION_TIME');
1889 fnd_message.raise_error;
1890 END IF;
1891 ELSIF substr(p_course_end_time ,1,2) <
1892 substr(l_ssn_end_time ,1,2) THEN
1893 fnd_message.set_name('OTA','OTA_13563_EVT_SESSION_TIME');
1894 fnd_message.raise_error;
1895 END IF;
1896 END IF;
1897 END IF;
1898
1899 END LOOP;
1900 CLOSE get_ssn_times_cr;
1901
1902 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC, 10);
1903
1904 EXCEPTION
1905 WHEN app_exception.application_exception THEN
1906 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.COURSE_START_TIME') THEN
1907 hr_utility.set_location(' Leaving:'||w_proc, 60);
1908 RAISE;
1909 END IF;
1910 hr_utility.set_location(' Leaving:'||w_proc, 70);
1911 END check_class_session_times;
1912
1913 -- ----------------------------------------------------------------------------
1914 -- -----------------------< CHECK_UPDATED_COURSE_DATES >-----------------------
1915 -- ----------------------------------------------------------------------------
1916 --
1917 procedure CHECK_UPDATED_COURSE_DATES (
1918 P_EVENT_ID in number,
1919 P_OBJECT_VERSION_NUMBER in number,
1920 P_EVENT_TYPE in varchar2,
1921 P_COURSE_START_DATE in date,
1922 P_COURSE_END_DATE in date
1923 ) is
1924 --
1925 W_PROC constant varchar2(72) := G_PACKAGE||'check_updated_course_dates';
1926 --
1927 procedure check_parameters is
1928 --
1929 begin
1930 HR_API.MANDATORY_ARG_ERROR ( G_PACKAGE,
1931 'p_event_id',
1932 p_event_id);
1933 --
1934 HR_API.MANDATORY_ARG_ERROR ( G_PACKAGE,
1935 'p_event_type',
1936 p_event_type);
1937 end check_parameters;
1938 --
1939 function course_dates_have_changed return boolean is
1940 --
1941 l_dates_updated boolean := FALSE;
1942 --
1943 begin
1944 --
1945 if (OTA_EVT_SHD.api_updating (p_event_id, p_object_version_number)) then
1946 --
1947 l_dates_updated :=
1948 (nvl (p_course_start_date, hr_general.start_of_time) <>
1949 nvl (OTA_EVT_SHD.g_old_rec.course_start_date, hr_general.start_of_time)
1950 or nvl (p_course_end_date, hr_general.end_of_time) <>
1951 nvl (OTA_EVT_SHD.g_old_rec.course_end_date, hr_general.end_of_time)
1952 );
1953 --
1954 end if;
1955 --
1956 return l_dates_updated;
1957 --
1958 end course_dates_have_changed;
1959 --
1960 begin
1961 --
1962 HR_UTILITY.SET_LOCATION ('Entering:'||W_PROC,5);
1963 --
1964 check_parameters;
1965 --
1966 if course_dates_have_changed
1967 and p_event_type in ('PROGRAMME MEMBER','SCHEDULED')
1968 then
1969 --
1970 if NOT course_dates_span_sessions (
1971 p_event_id => p_event_id,
1972 p_course_start_date => p_course_start_date,
1973 p_course_end_date => p_course_end_date)
1974 then
1975 OTA_EVT_SHD.CONSTRAINT_ERROR ('OTA_EVT_EVENT_SESSION_SPAN');
1976 end if;
1977 --
1978 end if;
1979 --
1980 EXCEPTION
1981 WHEN app_exception.application_exception THEN
1982 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.COURSE_START_DATE',
1983 p_associated_column2 => 'OTA_EVENTS.COURSE_END_DATE') THEN
1984 hr_utility.set_location(' Leaving:'||w_proc, 40);
1985 RAISE;
1986 END IF;
1987 hr_utility.set_location(' Leaving:'||w_proc, 50);
1988
1989 end check_updated_course_dates;
1990 --
1991 -- ----------------------------------------------------------------------------
1992 -- ------------------------< check_cost_vals >---------------------------------
1996 (p_budget_currency_code in varchar2
1993 -- ----------------------------------------------------------------------------
1994 --
1995 procedure check_cost_vals
1997 ,p_budget_cost in number
1998 ,p_actual_cost in number) is
1999 --
2000 v_proc varchar2(72) := g_package||'check_cost_vals';
2001 begin
2002 --
2003 hr_utility.set_location('Entering:'|| v_proc, 5);
2004 --
2005 if (p_budget_cost is not null or p_actual_cost is not null) and
2006 p_budget_currency_code is null then
2007 --
2008 fnd_message.set_name('OTA','OTA_13394_TAV_COST_ATTR');
2009 fnd_message.raise_error;
2010 --
2011 end if;
2012 --
2013 hr_utility.set_location(' Leaving:'|| v_proc, 10);
2014 --
2015 EXCEPTION
2016 WHEN app_exception.application_exception THEN
2017 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.BUDGET_COST',
2018 p_associated_column2 => 'OTA_EVENTS.BUDGET_CURRENCY_CODE',
2019 p_associated_column3 => 'OTA_EVENTS.ACTUAL_COST') THEN
2020 hr_utility.set_location(' Leaving:'||v_proc, 40);
2021 RAISE;
2022 END IF;
2023 hr_utility.set_location(' Leaving:'||v_proc, 50);
2024 end check_cost_vals;
2025 --
2026 -- ----------------------------------------------------------------------------
2027 -- ------------------------< CHECK_SESSION_WITHIN_COURSE >---------------------
2028 -- ----------------------------------------------------------------------------
2029 --
2030 -- Checks that a session date lies between the course start and end dates
2031 -- of its parent event.
2032 --
2033 procedure CHECK_SESSION_WITHIN_COURSE (
2034 P_EVENT_TYPE in varchar2,
2035 P_PARENT_EVENT_ID in number,
2036 P_COURSE_START_DATE in date,
2037 P_EVENT_ID in number default null,
2038 P_OBJECT_VERSION_NUMBER in number default null
2039 ) is
2040 --
2041 W_PROC varchar2 (72)
2042 := G_PACKAGE || 'CHECK_SESSION_WITHIN_COURSE';
2043 --
2044 begin
2045 --
2046 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC, 5);
2047 --
2048 -- Check parameters
2049 --
2050 HR_API.MANDATORY_ARG_ERROR (
2051 P_API_NAME => G_PACKAGE,
2052 P_ARGUMENT => 'P_EVENT_TYPE',
2053 P_ARGUMENT_VALUE => P_EVENT_TYPE);
2054 HR_API.MANDATORY_ARG_ERROR (
2055 P_API_NAME => G_PACKAGE,
2056 P_ARGUMENT => 'P_PARENT_EVENT_ID',
2057 P_ARGUMENT_VALUE => P_PARENT_EVENT_ID);
2058 --
2059 -- OK ?
2060 --
2061 if not ( (OTA_EVT_SHD.API_UPDATING (P_EVENT_ID, P_OBJECT_VERSION_NUMBER))
2062 and (OTA_EVT_SHD.G_OLD_REC.COURSE_START_DATE = P_COURSE_START_DATE)) then
2063 --
2064 HR_UTILITY.TRACE ('Parent: ' || to_char (P_PARENT_EVENT_ID));
2065 --
2066 if ( (P_EVENT_TYPE = 'SESSION')
2067 and (not COURSE_DATES_SPAN_SESSIONS (
2068 P_PARENT_EVENT_ID => P_PARENT_EVENT_ID,
2069 P_NEW_SESSION_DATE => P_COURSE_START_DATE))) then
2070 OTA_EVT_SHD.CONSTRAINT_ERROR ('OTA_EVT_SESSION_TIMING');
2071 end if;
2072 --
2073 end if;
2074 --
2075 HR_UTILITY.SET_LOCATION (' Leaving:' || W_PROC, 10);
2076 --
2077 EXCEPTION
2078 WHEN app_exception.application_exception THEN
2079 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.COURSE_START_DATE') THEN
2080 hr_utility.set_location(' Leaving:'||w_proc, 40);
2081 RAISE;
2082 END IF;
2083 hr_utility.set_location(' Leaving:'||w_proc, 50);
2084 end CHECK_SESSION_WITHIN_COURSE;
2085 --
2086 -- ----------------------------------------------------------------------------
2087 -- -----------------------< VALID_PARENT_EVENT >-------------------------------
2088 -- ----------------------------------------------------------------------------
2089 --
2090 -- Returns TRUE if the parent event ID specified exists in the events
2091 -- table, has the same business group as the child row and is a valid
2092 -- parent for the event type specified.
2093 --
2094 function VALID_PARENT_EVENT (
2095 P_PARENT_EVENT_ID in number,
2096 P_BUSINESS_GROUP_ID in number,
2097 P_EVENT_TYPE in varchar2
2098 ) return boolean is
2099 --
2100 W_PARENT_ID_EXISTS boolean;
2101 W_VALID_PARENT boolean;
2102 --
2103 begin
2104 --
2105 HR_UTILITY.SET_LOCATION ('Entering:' || G_PACKAGE || 'VALID_PARENT_EVENT', 5);
2106 --
2107 -- Check parameters
2108 --
2109 HR_API.MANDATORY_ARG_ERROR (
2110 G_PACKAGE,
2111 'P_BUSINESS_GROUP_ID',
2112 P_BUSINESS_GROUP_ID);
2113 --
2114 HR_API.MANDATORY_ARG_ERROR (
2115 G_PACKAGE,
2116 'P_EVENT_TYPE',
2117 P_EVENT_TYPE);
2118 --
2119 -- Is there a parent ?
2120 --
2121 if (P_PARENT_EVENT_ID is not null) then
2122 --
2123 OTA_EVT_SHD.FETCH_EVENT_DETAILS (
2124 P_EVENT_ID => P_PARENT_EVENT_ID,
2125 P_EVENT_EXISTS => W_PARENT_ID_EXISTS);
2126 --
2127 if (W_PARENT_ID_EXISTS) then
2128 HR_UTILITY.TRACE ('Parent exists: True');
2129 else
2130 HR_UTILITY.TRACE ('Parent exists: Fales');
2131 end if;
2132 HR_UTILITY.TRACE ('Business grps: ' || to_char (P_BUSINESS_GROUP_ID)
2133 || '/' || to_char (G_FETCHED_REC.BUSINESS_GROUP_ID));
2134 HR_UTILITY.TRACE ('Types: ' || P_EVENT_TYPE
2138 and (P_BUSINESS_GROUP_ID
2135 || '/' || G_FETCHED_REC.EVENT_TYPE);
2136 W_VALID_PARENT :=
2137 (W_PARENT_ID_EXISTS)
2139 = G_FETCHED_REC.BUSINESS_GROUP_ID)
2140 and ( ( (P_EVENT_TYPE
2141 = 'SESSION')
2142 and (G_FETCHED_REC.EVENT_TYPE
2143 in ('SCHEDULED',
2144 'PROGRAMME MEMBER')))
2145 or ( (P_EVENT_TYPE
2146 = 'PROGRAMME MEMBER')
2147 and (G_FETCHED_REC.EVENT_TYPE
2148 = 'PROGRAMME')));
2149 --
2150 end if;
2151 --
2152 HR_UTILITY.SET_LOCATION ( ' Leaving:' || G_PACKAGE || 'VALID_PARENT_EVENT', 10);
2153 return W_VALID_PARENT;
2154 --
2155 end VALID_PARENT_EVENT;
2156 --
2157 -- ----------------------------------------------------------------------------
2158 -- -----------------------< CHECK_PARENT_EVENT_IS_VALID >----------------------
2159 -- ----------------------------------------------------------------------------
2160 --
2161 procedure CHECK_PARENT_EVENT_IS_VALID (
2162 P_PARENT_EVENT_ID in number,
2163 P_BUSINESS_GROUP_ID in number,
2164 P_EVENT_TYPE in varchar2,
2165 P_EVENT_ID in number default null,
2166 P_OBJECT_VERSION_NUMBER in number default null
2167 ) is
2168 begin
2169 --
2170 HR_UTILITY.SET_LOCATION (
2171 'Entering:' || G_PACKAGE || 'CHECK_PARENT_EVENT_IS_VALID',
2172 5);
2173 --
2174 if ( (P_PARENT_EVENT_ID is not null )
2175 and (not ( (OTA_EVT_SHD.API_UPDATING (P_EVENT_ID, P_OBJECT_VERSION_NUMBER))
2176 and (OTA_EVT_SHD.G_OLD_REC.PARENT_EVENT_ID = P_PARENT_EVENT_ID )))) then
2177 --
2178 if (not VALID_PARENT_EVENT (
2179 P_PARENT_EVENT_ID,
2180 P_BUSINESS_GROUP_ID,
2181 P_EVENT_TYPE)) then
2182 OTA_EVT_SHD.CONSTRAINT_ERROR ('OTA_EVT_INVALID_PARENT');
2183 end if;
2184 --
2185 end if;
2186 --
2187 HR_UTILITY.SET_LOCATION (
2188 ' Leaving:' || G_PACKAGE || 'CHECK_PARENT_EVENT_IS_VALID',
2189 10);
2190 --
2191 end CHECK_PARENT_EVENT_IS_VALID;
2192 --
2193 -- ---------------------------------------------------------------------------
2194 procedure CHECK_PROGRAM_ENROLMENT_SPAN (
2195 --*****************************************************************************
2196 --* Error if the programme's enrolment dates do not span the enrolment dates
2197 --* of all its members.
2198 --*****************************************************************************
2199 --
2200 p_event_id number,
2201 p_event_type varchar2,
2202 p_enrolment_start_date date,
2203 p_enrolment_end_date date,
2204 p_parent_event_id number default null,
2205 p_object_version_number number default null) is
2206 --
2207 function enrolment_dates_valid return boolean is
2208 --
2209 l_valid_enrol_dates boolean := TRUE;
2210 --
2211 cursor csr_programme_dates is
2212 --
2213 select 1
2214 from ota_events
2215 where event_id = p_parent_event_id
2216 and (p_enrolment_start_date not between enrolment_start_date
2217 and enrolment_end_date
2218 or p_enrolment_end_date not between enrolment_start_date
2219 and enrolment_end_date);
2220 --
2221 cursor csr_member_dates is
2222 --
2223 select 1
2224 from ota_events
2225 where parent_event_id = p_event_id
2226 and ((p_enrolment_start_date not between enrolment_start_date
2227 and enrolment_end_date)
2228 or(p_enrolment_end_date not between enrolment_start_date
2229 and enrolment_end_date));
2230 --
2231 begin
2232 --
2233 if p_event_type = 'PROGRAMME' then
2234 --
2235 -- Check that the parent dates still
2236 -- span all the child dates
2237 --
2238 open csr_member_dates;
2239 fetch csr_member_dates into g_dummy;
2240 l_valid_enrol_dates := csr_member_dates%notfound;
2241 close csr_member_dates;
2242 --
2243 elsif p_event_type = 'PROGRAMME MEMBER' then
2244 --
2245 -- Check that the new member dates are within the parent
2246 -- enrolment dates
2247 --
2248 open csr_programme_dates;
2249 fetch csr_programme_dates into g_dummy;
2250 l_valid_enrol_dates := csr_member_dates%notfound;
2251 close csr_member_dates;
2252 --
2253 end if;
2254 --
2255 return l_valid_enrol_dates;
2256 --
2257 end enrolment_dates_valid;
2258 --
2259 procedure check_parameters is
2260 --
2261 begin
2262 --
2263 HR_API.MANDATORY_ARG_ERROR ( G_PACKAGE,
2264 'p_event_type',
2265 p_event_type);
2266 --
2267 HR_API.MANDATORY_ARG_ERROR ( G_PACKAGE,
2268 'p_event_id',
2269 p_event_id);
2270 --
2271 HR_API.MANDATORY_ARG_ERROR ( G_PACKAGE,
2272 'p_enrolment_start_date',
2273 p_enrolment_start_date);
2274 --
2275 HR_API.MANDATORY_ARG_ERROR ( G_PACKAGE,
2276 'p_enrolment_end_date',
2277 p_enrolment_end_date);
2278 --
2279 if (p_event_type = 'PROGRAMME' and p_parent_event_id is not null)
2280 or (p_event_type = 'PROGRAMME MEMBER' and p_parent_event_id is null) then
2281 invalid_parameter(
2282 p_procedure_name=>'ota_evt_api.check_program_enrolment_span',
2283 p_optional_message=>'Test');
2284 end if;
2285 --
2289 --
2286 end check_parameters;
2287 --
2288 begin
2290 -- This check only applies to existing PROGRAMMEs and PROGRAMME_MEMBERs
2291 --
2292 if ( (P_EVENT_TYPE not in ('PROGRAMME', 'PROGRAMME MEMBER'))
2293 or (not OTA_EVT_SHD.api_updating (p_event_id, p_object_version_number))) then
2294 return;
2295 end if;
2296 --
2297 -- With changing enrolment dates
2298 --
2299 if ( (OTA_EVT_SHD.g_old_rec.enrolment_start_date <> p_enrolment_start_date)
2300 or (OTA_EVT_SHD.g_old_rec.enrolment_end_date <> p_enrolment_end_date )) then
2301 --
2302 check_parameters;
2303 --
2304 if NOT enrolment_dates_valid then
2305 OTA_EVT_SHD.CONSTRAINT_ERROR ('OTA_EVT_ENROLMENT_DATE_SPAN');
2306 end if;
2307 --
2308 end if;
2309 --
2310
2311 end check_program_enrolment_span;
2312 --
2313 -- ----------------------------------------------------------------------------
2314 -- -------------------------< Price_Basis_Change >-----------------------------
2315 -- ----------------------------------------------------------------------------
2316 --
2317 -- Price Basis Changes are not allowed if Enrollments or Event Associations
2318 -- exist for the Event
2319 --
2320 procedure price_basis_change(p_event_id number
2321 ,p_price_basis varchar2) is
2322 --
2323 l_proc varchar2(72) := 'price_basis_change';
2324 l_exists varchar2(1);
2325 --
2326 l_price_basis_changed boolean :=
2327 ota_general.value_changed(ota_evt_shd.g_old_rec.price_basis
2328 ,p_price_basis);
2329 --
2330 cursor get_enrollments is
2331 select null
2332 from ota_delegate_bookings
2333 where event_id = p_event_id;
2334 --
2335 cursor get_event_associations is
2336 select null
2337 from ota_event_associations
2338 where event_id = p_event_id;
2339 --
2340 begin
2341 hr_utility.set_location ('Entering:'||l_proc,5);
2342 --
2343 if p_event_id is not null and
2344 l_price_basis_changed then
2345 --
2346 open get_enrollments;
2347 fetch get_enrollments into l_exists;
2348 if get_enrollments%found then
2349 close get_enrollments;
2350 fnd_message.set_name('OTA','OTA_13527_PRICE_BASIS_CHANGE');
2351 fnd_message.raise_error;
2352 end if;
2353 close get_enrollments;
2354 --
2355 open get_event_associations;
2356 fetch get_event_associations into l_exists;
2357 if get_event_associations%found then
2358 close get_event_associations;
2359 fnd_message.set_name('OTA','OTA_13527_PRICE_BASIS_CHANGE');
2360 fnd_message.raise_error;
2361 end if;
2362 close get_event_associations;
2363 --
2364 end if;
2365 --
2366 hr_utility.set_location ('Leaving:'||l_proc,10);
2367 EXCEPTION
2368 WHEN app_exception.application_exception THEN
2369 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.PRICE_BASIS') THEN
2370 hr_utility.set_location(' Leaving:'||l_proc, 40);
2371 RAISE;
2372 END IF;
2373 hr_utility.set_location(' Leaving:'||l_proc, 50);
2374 end price_basis_change;
2375 --
2376 -- ----------------------------------------------------------------------------
2377 -- -------------------------< check_timezone >-----------------------------
2378 -- ----------------------------------------------------------------------------
2379 --
2380 -- Procedure to check timezone of a class
2381 --
2382 --
2383 PROCEDURE check_timezone(p_timezone IN VARCHAR2)
2384 IS
2385 l_timezone_id NUMBER := ota_timezone_util.get_timezone_id(p_timezone);
2386 BEGIN
2387 IF l_timezone_id IS NULL THEN
2388 fnd_message.set_name('OTA','OTA_443982_TIMEZONE_ERROR');
2389 fnd_message.set_token('OBJECT_TYPE',ota_utility.get_lookup_meaning('OTA_OBJECT_TYPE','CL',810));
2390 fnd_message.raise_error;
2391 END IF;
2392 END check_timezone;
2393 --
2394 -- ----------------------------------------------------------------------------
2395 -- -------------------------< check_time_format >-----------------------------
2396 -- ----------------------------------------------------------------------------
2397 --
2398 -- Procedure to check time format (HH24:MI) of a course_start_time and course_end_time bug#4895398
2399 --
2400 --
2401 PROCEDURE check_time_format(p_time IN VARCHAR2)
2402 IS
2403 BEGIN
2404 IF p_time IS NOT NULL THEN
2405 IF (NOT (LENGTH(p_time) = 5
2406 and substr (p_time, 3,1) = ':'
2407 and (substr(p_time,1,1) >= '0' and substr(p_time,1,1)<= '2')
2408 and (substr(p_time,2,1) >= '0' and substr(p_time,2,1)<= '9')
2409 and (substr(p_time,4,1) >= '0' and substr(p_time,4,1)<= '5')
2410 and (substr(p_time,5,1) >= '0' and substr(p_time,5,1)<= '9')
2411 and (to_number (substr (p_time, 1,2)) between 0 and 23
2412 and to_number (substr (p_time,4)) between 0 and 59))) then
2413 fnd_message.set_name('OTA','OTA_13444_EVT_TIME_FORMAT');
2414 fnd_message.raise_error;
2415 END IF;
2416 END IF;
2417 END;
2418
2419 -- ----------------------------------------------------------------------------
2423 -- Performs the validation routines common to both insert and update.
2420 -- -------------------------< VALIDITY_CHECKS >--------------------------------
2421 -- ----------------------------------------------------------------------------
2422 --
2424 --
2425 -- VT 05/06/97 #488173
2426 procedure VALIDITY_CHECKS (
2427 P_REC in out nocopy OTA_EVT_SHD.G_REC_TYPE
2428 ) is
2429 --
2430 W_PROC varchar2 (72)
2431 := G_PACKAGE || 'VALIDITY_CHECKS';
2432 l_course_start_date_changed boolean
2433 := ota_general.value_changed(ota_evt_shd.g_old_rec.course_start_date,
2434 p_rec.course_start_date);
2435 l_course_end_date_changed boolean
2436 := ota_general.value_changed(ota_evt_shd.g_old_rec.course_end_date,
2437 p_rec.course_end_date);
2438 l_enrolment_start_date_changed boolean
2439 := ota_general.value_changed(ota_evt_shd.g_old_rec.enrolment_start_date,
2440 p_rec.enrolment_start_date);
2441 l_enrolment_end_date_changed boolean
2442 := ota_general.value_changed(ota_evt_shd.g_old_rec.enrolment_end_date,
2443 p_rec.enrolment_end_date);
2444 l_public_event_flag_changed boolean
2445 := ota_general.value_changed(ota_evt_shd.g_old_rec.public_event_flag,
2446 p_rec.public_event_flag);
2447 l_title_changed boolean
2448 := ota_general.value_changed(ota_evt_shd.g_old_rec.title,
2449 p_rec.title);
2450 l_maximum_attendees_changed boolean
2451 := ota_general.value_changed(ota_evt_shd.g_old_rec.maximum_attendees,
2452 p_rec.maximum_attendees);
2453 l_maximum_int_att_changed boolean
2454 := ota_general.value_changed(ota_evt_shd.g_old_rec.maximum_internal_attendees,
2455 p_rec.maximum_internal_attendees);
2456 l_owner_id_changed boolean
2457 := ota_general.value_changed(ota_evt_shd.g_old_rec.owner_id,
2458 p_rec.owner_id);
2459 l_line_id_changed boolean
2460 := ota_general.value_changed(ota_evt_shd.g_old_rec.line_id,
2461 p_rec.line_id);
2462 l_training_center_id_changed boolean
2463 := ota_general.value_changed(ota_evt_shd.g_old_rec.training_center_id,
2464 p_rec.training_center_id);
2465 l_location_id_changed boolean
2466 := ota_general.value_changed(ota_evt_shd.g_old_rec.location_id,
2467 p_rec.location_id);
2468 l_offering_id_changed boolean
2469 := ota_general.value_changed(ota_evt_shd.g_old_rec.offering_id,
2470 p_rec.offering_id);
2471 l_timezone_changed boolean
2472 := ota_general.value_changed(ota_evt_shd.g_old_rec.timezone,
2473 p_rec.timezone);
2474 --bug#4895398
2475 l_course_start_time_changed boolean
2476 := ota_general.value_changed(ota_evt_shd.g_old_rec.course_start_time,p_rec.course_start_time);
2477
2478 l_course_end_time_changed boolean
2479 := ota_general.value_changed(ota_evt_shd.g_old_rec.course_end_time,p_rec.course_end_time);
2480 --bug#4895398
2481
2482 --Enhancement 1823602.
2483 l_commitment_id ra_customer_trx_all.customer_trx_id%TYPE;
2484 l_commitment_number ra_customer_trx_all.trx_number%TYPE;
2485 l_commitment_start_date ra_customer_trx_all.start_date_commitment%TYPE;
2486 l_commitment_end_date ra_customer_trx_all.end_date_commitment%TYPE;
2487
2488
2489 --
2490 begin
2491 --
2492 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC, 5);
2493 --
2494 -- Check only non-static domains (constraints trap the static ones)
2495 --
2496 OTA_GENERAL.CHECK_DOMAIN_VALUE (
2497 P_DOMAIN_TYPE => 'DEV_EVENT_TYPE',
2498 P_DOMAIN_VALUE => P_REC.DEVELOPMENT_EVENT_TYPE);
2499 --
2500 OTA_GENERAL.CHECK_DOMAIN_VALUE (
2501 P_DOMAIN_TYPE => 'TRAINING_CENTRE',
2502 P_DOMAIN_VALUE => P_REC.CENTRE);
2503 --
2504 OTA_GENERAL.CHECK_DOMAIN_VALUE (
2505 P_DOMAIN_TYPE => 'OTA_DURATION_UNITS',
2506 P_DOMAIN_VALUE => P_REC.DURATION_UNITS);
2507 --
2508 OTA_GENERAL.CHECK_DOMAIN_VALUE (
2509 P_DOMAIN_TYPE => 'EVENT_USER_STATUS',
2510 P_DOMAIN_VALUE => P_REC.USER_STATUS);
2511 --
2512 OTA_GENERAL.CHECK_DOMAIN_VALUE (
2513 P_DOMAIN_TYPE => 'SCHEDULED_EVENT_STATUS',
2514 P_DOMAIN_VALUE => P_REC.EVENT_STATUS);
2515 --
2516 OTA_GENERAL.CHECK_VENDOR_IS_VALID (
2517 P_REC.VENDOR_ID,P_REC.COURSE_START_DATE);
2518 --
2519 OTA_GENERAL.CHECK_CURRENCY_IS_VALID (
2520 P_REC.CURRENCY_CODE);
2521 --
2522 OTA_GENERAL.CHECK_LANGUAGE_IS_VALID (
2523 P_REC.LANGUAGE_ID);
2524 --
2525 if p_rec.event_id is null or l_title_changed then
2526 check_title_is_unique(
2527 P_TITLE => p_rec.title,
2528 P_BUSINESS_GROUP_ID => p_rec.business_group_id,
2529 P_PARENT_EVENT_ID => p_rec.parent_event_id,
2530 P_EVENT_ID => p_rec.event_id,
2531 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number);
2532 end if;
2533 --
2534 if P_REC.EVENT_TYPE in ('SCHEDULED','PROGRAMME', 'SELFPACED') and
2535 p_rec.event_id is not null then
2536 price_basis_change(p_rec.event_id
2537 ,p_rec.price_basis);
2538
2539 end if;
2540 --bug#4895398
2541 --
2542 --Course start time and end time format check
2543 --
2547
2544 IF(p_rec.course_start_time IS NOT NULL and l_course_start_time_changed) THEN
2545 check_time_format(p_rec.course_start_time);
2546 END IF;
2548 IF(p_rec.course_end_time IS NOT NULL and l_course_end_time_changed) THEN
2549 check_time_format(p_rec.course_end_time);
2550 END IF;
2551 --end bug#4895398
2552 --
2553 -- Development event checks
2554 --
2555 if P_REC.EVENT_TYPE in ('DEVELOPMENT','SCHEDULED','SELFPACED') then
2556 --
2557 -- Check course dates are valid
2558 --
2559 /* --changes made for eBS by asud
2560 COURSE_DATES_ARE_VALID (P_REC.ACTIVITY_VERSION_ID,
2561 P_REC.COURSE_START_DATE,
2562 P_REC.COURSE_END_DATE,
2563 P_REC.EVENT_STATUS);
2564 */--changes made for eBS by asud
2565 COURSE_DATES_ARE_VALID (P_REC.PARENT_OFFERING_ID,
2566 P_REC.COURSE_START_DATE,
2567 P_REC.COURSE_END_DATE,
2568 P_REC.EVENT_STATUS,
2569 P_REC.EVENT_TYPE);
2570
2571 CHK_END_DATE( P_REC.COURSE_END_DATE ,P_REC.COURSE_END_TIME) ;
2572 --bug 3192072
2573 chk_start_date(p_rec.course_start_date, p_rec.course_start_time);
2574 --
2575 end if;
2576 /*
2577 --Bug 2431755
2578 -- Self-Paced event specific checks
2579 if P_REC.EVENT_TYPE = 'SELFPACED' then
2580 --
2581 CHECK_PRICING (P_REC.price_basis,
2582 P_REC.standard_price);
2583 --
2584 end if;
2585 --Bug 2431755
2586 */
2587 -- Scheduled event specific checks
2588 --
2589 if P_REC.EVENT_TYPE in ('SCHEDULED','SELFPACED') then
2590 -- if P_REC.EVENT_TYPE = 'SCHEDULED' then
2591 --
2592 check_price_basis(p_rec.event_id ,
2593 p_rec.price_basis,
2594 p_rec.parent_offering_id,
2595 p_rec.maximum_internal_attendees);
2596
2597
2598 chk_activity_version_id(p_rec.activity_version_id,
2599 p_rec.parent_offering_id);
2600
2601 CHECK_PRICING (P_REC.price_basis,
2602 P_REC.standard_price,p_rec.currency_code);
2603 --
2604 /*--changes made for eBS by asud
2605 ENROLLMENT_DATES_ARE_VALID (
2606 P_REC.ACTIVITY_VERSION_ID,
2607 P_REC.ENROLMENT_START_DATE,
2608 P_REC.ENROLMENT_END_DATE);
2609 */--changes made for eBS by asud
2610 ENROLLMENT_DATES_ARE_VALID (P_REC.PARENT_OFFERING_ID,
2611 P_REC.ENROLMENT_START_DATE,
2612 P_REC.ENROLMENT_END_DATE);
2613
2614 ENROLLMENT_DATES_EVENT_VALID (
2615 P_REC.ENROLMENT_START_DATE,
2616 P_REC.ENROLMENT_END_DATE,
2617 P_REC.COURSE_START_DATE,
2618 P_REC.COURSE_END_DATE);
2619 /* bug 3795299
2620 if l_course_start_date_changed or l_course_end_date_changed then
2621
2622 --ADDED by dbatra for training plan bug 3007101
2623 ota_trng_plan_comp_ss.update_tpc_evt_change(p_rec.event_id,
2624 p_rec.course_start_date,
2625 p_rec.course_end_date);
2626 end if;
2627 bug 3795299
2628 */
2629 end if;
2630 --
2631 if (P_REC.EVENT_TYPE = 'SCHEDULED') then
2632 if l_course_start_date_changed or l_course_end_date_changed then
2633
2634 session_valid(P_REC.EVENT_ID,
2635 P_REC.COURSE_START_DATE,
2636 P_REC.COURSE_END_DATE);
2637
2638 booking_deal_valid(P_REC.EVENT_ID,
2639 P_REC.COURSE_START_DATE,
2640 P_REC.COURSE_END_DATE,
2641 P_REC.EVENT_STATUS);
2642 end if;
2643 -- added for bug 3622035
2644 check_class_session_times(p_event_id => p_rec.event_id,
2645 p_course_start_date => p_rec.course_start_date,
2646 p_course_start_time => p_rec.course_start_time,
2647 p_course_end_date => p_rec.course_end_date,
2648 p_course_end_time => p_rec.course_end_time);
2649
2650 -- added for bug 3622035
2651 end if;
2652
2653 if (P_REC.EVENT_TYPE = 'SESSION') then
2654 CHECK_SESSION_WITHIN_COURSE (
2655 P_EVENT_TYPE => P_REC.EVENT_TYPE,
2656 P_PARENT_EVENT_ID => P_REC.PARENT_EVENT_ID,
2657 P_COURSE_START_DATE => P_REC.COURSE_START_DATE,
2658 P_EVENT_ID => P_REC.EVENT_ID,
2659 P_OBJECT_VERSION_NUMBER
2660 => P_REC.OBJECT_VERSION_NUMBER);
2661 --Added for Bug 3403113
2662 check_session_time(
2663 p_parent_event_id => p_rec.parent_event_id,
2664 p_session_start_date => p_rec.course_start_date,
2665 p_session_start_time => p_rec.course_start_time,
2666 p_session_end_date => p_rec.course_end_date,
2667 p_session_end_time => p_rec.course_end_time,
2668 p_event_id => p_rec.event_id,
2669 p_object_version_number => p_rec.object_version_number);
2670
2671 end if;
2672 --
2673 CHECK_PROGRAM_ENROLMENT_SPAN (
2674 P_REC.EVENT_ID,
2678 P_REC.PARENT_EVENT_ID,
2675 P_REC.EVENT_TYPE,
2676 P_REC.ENROLMENT_START_DATE,
2677 P_REC.ENROLMENT_END_DATE,
2679 P_REC.OBJECT_VERSION_NUMBER);
2680 --
2681 check_cost_vals
2682 (p_budget_currency_code => p_rec.budget_currency_code
2683 ,p_budget_cost => p_rec.budget_cost
2684 ,p_actual_cost => p_rec.actual_cost);
2685 --
2686 if l_enrolment_start_date_changed or l_enrolment_end_date_changed OR l_timezone_changed then
2687 bookings_valid(P_REC.EVENT_ID,
2688 P_REC.ENROLMENT_START_DATE,
2689 P_REC.ENROLMENT_END_DATE,
2690 P_REC.EVENT_TYPE,
2691 P_REC.TIMEZONE);
2692 end if;
2693 --
2694 if l_maximum_attendees_changed or l_maximum_int_att_changed then
2695 ota_evt_bus2.check_places(p_rec.event_id
2696 ,p_rec.maximum_attendees
2697 ,p_rec.maximum_internal_attendees);
2698
2699 --Added for mandatory enrollments
2700 ota_evt_bus2.check_mandatory_associations(p_rec.event_id
2701 ,p_rec.maximum_attendees
2702 ,p_rec.maximum_internal_attendees);
2703 end if;
2704 --
2705 if l_public_event_flag_changed then
2706 check_public_event_flag(p_rec.public_event_flag
2707 ,p_rec.event_id);
2708 end if;
2709 --
2710 if l_owner_id_changed then
2711 check_owner_id (p_rec.event_id,
2712 p_rec.owner_id,
2713 p_rec.business_group_id,
2714 p_rec.course_start_date);
2715 end if;
2716 if l_line_id_changed then
2717 check_line_id (p_rec.event_id,
2718 p_rec.line_id,
2719 p_rec.org_id);
2720 end if;
2721 /* Globalization */
2722 if l_training_center_id_changed then
2723 chk_training_center (p_rec.event_id,
2724 p_rec.training_center_id);
2725 end if;
2726
2727 if l_location_id_changed then
2728 chk_location (p_rec.event_id,
2729 p_rec.location_id,
2730 p_rec.training_center_id,
2731 p_rec.course_end_date);
2732 end if;
2733 /*Enhancement 1823602*/
2734 IF p_rec.line_id IS NOT NULL THEN
2735 /* For Bug 4492519 */
2736 IF p_rec.event_id is null or l_course_end_date_changed then
2737 ota_utility.get_commitment_detail(p_rec.line_id,
2738 l_commitment_number,
2739 l_commitment_id,
2740 l_commitment_start_date,
2741 l_commitment_end_date);
2742 IF l_commitment_end_date IS NOT NULL
2743 AND p_rec.course_end_date > l_commitment_end_date THEN
2744 FND_MESSAGE.SET_NAME ('OTA', 'OTA_OM_COMMITMENT');
2745 FND_MESSAGE.SET_TOKEN ('COMMITMENT_NUMBER', l_commitment_number);
2746 FND_MESSAGE.SET_TOKEN ('COMMITMENT_END_DATE', fnd_date.date_to_chardate(l_commitment_end_date));
2747 FND_MESSAGE.RAISE_ERROR;
2748 END IF;
2749 END IF;
2750 END IF;
2751
2752 /*Enhancement 1823602*/
2753
2754 /* Ilearing */
2755 /*
2756 if l_offering_id_changed then
2757 check_unique_offering_id(p_rec.event_id,
2758 p_rec.offering_id);
2759 end if;
2760 */
2761
2762 IF p_rec.event_type IN ('SCHEDULED', 'SESSION', 'SELFPACED') THEN
2763 check_timezone(p_rec.timezone);
2764 END IF;
2765
2766
2767 HR_UTILITY.SET_LOCATION (' Leaving:' || W_PROC, 10);
2768 --
2769 end VALIDITY_CHECKS;
2770 --
2771 -- ----------------------------------------------------------------------------
2772 -- |---------------------------< check_child_entities >-----------------------|
2773 -- ----------------------------------------------------------------------------
2774 Procedure check_child_entities (p_event_id in number) is
2775 --
2776 -- cursor to check if sessions exist for the event
2777 --
2778 Cursor c_session_details is
2779 select 'X'
2780 from ota_events
2781 where parent_event_id = p_event_id;
2782 --
2783 -- cursor to check if resources exist for the event
2784 --
2785 Cursor c_resource_details is
2786 select 'X'
2787 from ota_resource_bookings
2788 where event_id = p_event_id;
2789 --
2790 -- cursor to check if program membership exist for the event
2791 --
2792 Cursor c_program_membership_details is
2793 select 'X'
2794 from ota_program_memberships
2795 where program_event_id = p_event_id;
2796 --
2797 -- cursor to check if event association exist for the event
2798 --
2799 Cursor c_event_associations_details is
2800 select 'X'
2801 from ota_event_associations
2802 where event_id = p_event_id;
2803 --
2804 -- cursor to check if delegate bookings exist for the event
2805 --
2806 Cursor c_delegate_bookings_details is
2807 select 'X'
2808 from ota_delegate_bookings
2809 where event_id = p_event_id;
2810 -- 6683076
2811 -- cursor to check if evaluation exists for the event
2812 --
2813 Cursor c_evaluation_details is
2814 select 'X'
2815 from ota_evaluations
2819 -- cursor to check if booking deals exist for the event
2816 where object_id = p_event_id
2817 and object_type = 'E';
2818 --
2820 --
2821 Cursor c_booking_deals_details is
2822 select 'X'
2823 from ota_booking_deals
2824 where event_id = p_event_id;
2825 --
2826 -- cursor to check if cat inclusions exist for the event
2827 --
2828 Cursor c_act_cat_inclusions_details is
2829 select 'X'
2830 from ota_act_cat_inclusions
2831 where event_id = p_event_id;
2832 --
2833 --
2834 -- cursor to check if attempts exist for the event
2835 --
2836 Cursor c_attempts_details is
2837 select 'X'
2838 from ota_attempts
2839 where event_id = p_event_id;
2840 --
2841 -- cursor to check if the event is referenced in training plan costs
2842 --
2843 Cursor c_get_tpc_rows is
2844 select 'Y'
2845 from OTA_TRAINING_PLAN_COSTS
2846 where event_id = p_event_id;
2847 --
2848 -- cursor to check if the event is referenced in per budget elements
2849 --
2850 Cursor c_get_pbe_rows is
2851 select 'Y'
2852 from per_budget_elements
2853 where event_id = p_event_id;
2854
2855 /*For bug 4407518 */
2856
2857 Cursor c_conference_details is
2858 select 'X'
2859 from OTA_CONFERENCES
2860 where event_id = p_event_id;
2861 /* for bug 4407518 */
2862 --
2863 l_dyn_curs integer;
2864 l_dyn_rows integer;
2865 --
2866 --
2867 l_proc varchar2(72) := g_package||'check_child_entities';
2868 l_dummy varchar2(1);
2869 --
2870 Begin
2871 hr_utility.set_location('Entering:'||l_proc, 5);
2872 --
2873 -- Determine if the event has training_plan_cost records
2874 open c_get_tpc_rows;
2875 fetch c_get_tpc_rows into l_dummy;
2876 if c_get_tpc_rows%found then
2877 close c_get_tpc_rows;
2878 fnd_message.set_name ('OTA', 'OTA_13823_EVT_NO_DEL_TPC_EXIST');
2879 fnd_message.raise_error;
2880 else
2881 close c_get_tpc_rows;
2882 -- Determine if the event has per_budget_element records
2883 open c_get_pbe_rows;
2884 fetch c_get_pbe_rows into l_dummy;
2885 if c_get_pbe_rows%found then
2886 close c_get_pbe_rows;
2887 fnd_message.set_name ('OTA', 'OTA_13824_EVT_NO_DEL_BGE_EXIST');
2888 fnd_message.raise_error;
2889 else
2890 close c_get_pbe_rows;
2891 end if;
2892 end if;
2893 --
2894 -- Raise error if sessions exists.
2895 --
2896 open c_session_details;
2897 fetch c_session_details into l_dummy;
2898 if c_session_details%found then
2899 --
2900 close c_session_details;
2901 --
2902 fnd_message.set_name ('OTA', 'OTA_13677_EVT_SESSION_EXISTS');
2903 fnd_message.raise_error;
2904 --
2905 end if;
2906 --
2907 close c_session_details;
2908 --
2909 -- Raise error if resoure bookings exists.
2910 --
2911 open c_resource_details;
2912 fetch c_resource_details into l_dummy;
2913 if c_resource_details%found then
2914 --
2915 close c_resource_details;
2916 --
2917 fnd_message.set_name ('OTA', 'OTA_13678_EVT_RES_EXISTS');
2918 fnd_message.raise_error;
2919 --
2920 end if;
2921 --
2922 close c_resource_details;
2923 --
2924 -- Raise error if program membership exists.
2925 --
2926 open c_program_membership_details;
2927 fetch c_program_membership_details into l_dummy;
2928 if c_program_membership_details%found then
2929 --
2930 close c_program_membership_details;
2931 --
2932 fnd_message.set_name ('OTA', 'OTA_13681_EVT_PMM_EXISTS');
2933 fnd_message.raise_error;
2934 --
2935 end if;
2936 --
2937 close c_program_membership_details;
2938 --
2939 -- Raise error if event associations exists.
2940 --
2941 open c_event_associations_details;
2942 fetch c_event_associations_details into l_dummy;
2943 if c_event_associations_details%found then
2944 --
2945 close c_event_associations_details;
2946 --
2947 fnd_message.set_name ('OTA', 'OTA_13683_EVT_TEA_EXISTS');
2948 fnd_message.raise_error;
2949 --
2950 end if;
2951 --
2952 close c_event_associations_details;
2953 --
2954 -- Raise error if delegate bookings exists.
2955 --
2956 open c_delegate_bookings_details;
2957 fetch c_delegate_bookings_details into l_dummy;
2958 if c_delegate_bookings_details%found then
2959 --
2960 close c_delegate_bookings_details;
2961 --
2962 fnd_message.set_name ('OTA', 'OTA_13679_EVT_TDB_EXISTS');
2963 fnd_message.raise_error;
2964 --
2965 end if;
2966 --
2967 close c_delegate_bookings_details;
2968 -- 6683076
2969 -- Raise error if evaluation exists.
2970 --
2971 open c_evaluation_details;
2972 fetch c_evaluation_details into l_dummy;
2973 if c_evaluation_details%found then
2974 --
2975 close c_evaluation_details;
2976 --
2977 fnd_message.set_name ('OTA', 'OTA_467095_EVT_EVAL_EXISTS');
2978 fnd_message.raise_error;
2979 --
2980 end if;
2981 --
2982 close c_evaluation_details;
2983 --
2987 fetch c_booking_deals_details into l_dummy;
2984 -- Raise error if booking deals exists.
2985 --
2986 open c_booking_deals_details;
2988 if c_booking_deals_details%found then
2989 --
2990 close c_booking_deals_details;
2991 --
2992 fnd_message.set_name ('OTA', 'OTA_13680_EVT_TBD_EXISTS');
2993 fnd_message.raise_error;
2994 --
2995 end if;
2996 --
2997 close c_booking_deals_details;
2998 --
2999 -- Raise error if activity category inclusions exists.
3000 --
3001 open c_act_cat_inclusions_details;
3002 fetch c_act_cat_inclusions_details into l_dummy;
3003 if c_act_cat_inclusions_details%found then
3004 --
3005 close c_act_cat_inclusions_details;
3006 --
3007 fnd_message.set_name ('OTA', 'OTA_13682_EVT_CAT_EXISTS');
3008 fnd_message.raise_error;
3009 --
3010 end if;
3011 --
3012 close c_act_cat_inclusions_details;
3013 --
3014 -- Raise error if activity category inclusions exists.
3015 --
3016 open c_attempts_details;
3017 fetch c_attempts_details into l_dummy;
3018 if c_attempts_details%found then
3019 --
3020 close c_attempts_details;
3021 --
3022 fnd_message.set_name ('OTA', 'OTA_443538_EVT_ATT_EXISTS');
3023 fnd_message.raise_error;
3024 --
3025 end if;
3026 --
3027 close c_attempts_details;
3028
3029 /*for bug 4407518 */
3030 open c_conference_details;
3031 fetch c_conference_details into l_dummy;
3032 if c_conference_details%found then
3033 close c_conference_details;
3034 fnd_message.set_name('OTA', 'OTA_443916_EVT_CFR_EXISTS');
3035 fnd_message.raise_error;
3036 end if;
3037 close c_conference_details;
3038
3039
3040 hr_utility.set_location(' Leaving:'||l_proc, 10);
3041 EXCEPTION
3042 WHEN app_exception.application_exception THEN
3043 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.EVENT_ID') THEN
3044 hr_utility.set_location(' Leaving:'||l_proc, 70);
3045 RAISE;
3046 END IF;
3047 hr_utility.set_location(' Leaving:'||l_proc, 80);
3048 End check_child_entities;
3049 --
3050 --
3051 -- ----------------------------------------------------------------------------
3052 -- |---------------------------< check_for_st_finance_lines >-----------------|
3053 -- ----------------------------------------------------------------------------
3054 --
3055 -- This function checks to see if any 'ST' succesful Transferred finance Lines
3056 -- which have not been cancelled exists for any booking within the Event.
3057 --
3058 function check_for_st_finance_lines (
3059 p_event_id number) return boolean is
3060 --
3061 W_PROC constant varchar2(72) := G_PACKAGE||'check_for_st_finance_lines';
3062 --
3063 l_st_finance_lines boolean;
3064 --
3065 cursor csr_st_finance_lines is
3066 select 1
3067 from ota_finance_lines tfl,
3068 ota_delegate_bookings tdb
3069 where tdb.event_id = p_event_id
3070 and tdb.booking_id = tfl.booking_id
3071 and tfl.transfer_status = 'ST'
3072 and tfl.cancelled_flag = 'N';
3073 --
3074 begin
3075 --
3076 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC, 5);
3077 --
3078 open csr_st_finance_lines;
3079 fetch csr_st_finance_lines into g_dummy;
3080 l_st_finance_lines := csr_st_finance_lines%found;
3081 close csr_st_finance_lines;
3082 --
3083 HR_UTILITY.SET_LOCATION (W_PROC,10);
3084 --
3085 return l_st_finance_lines;
3086 --
3087 end check_for_st_finance_lines;
3088 --
3089 --
3090 -- ----------------------------------------------------------------------------
3091 -- |---------------------------< check_owner_id >-----------------------------|
3092 -- ----------------------------------------------------------------------------
3093 --
3094 -- This function checks to see if any the owner_id exists in
3095 -- per_people_f table
3096 --
3097 --
3098 Procedure check_owner_id (p_event_id in number,
3099 p_owner_id in number,
3100 p_business_group_id in number,
3101 p_course_start_date in date)
3102 Is
3103 l_proc varchar2(72) := g_package||'check_owner_id';
3104
3105 CURSOR c_people
3106 IS
3107 SELECT null
3108 FROM Per_all_people_f per
3109 WHERE per.person_id = p_owner_id and
3110 per.business_group_id = p_business_group_id and
3111 NVL(p_course_start_date,TRUNC(SYSDATE)) between
3112 effective_start_date and effective_end_date;
3113
3114 CURSOR c_people_cross
3115 IS
3116 SELECT null
3117 FROM Per_all_people_f per
3118 WHERE per.person_id = p_owner_id and
3119 NVL(p_course_start_date,TRUNC(SYSDATE)) between
3120 effective_start_date and effective_end_date;
3121
3122
3123 l_exist varchar2(1);
3124 --l_cross_business_group varchar2(1):= FND_PROFILE.VALUE('HR_CROSS_BUSINESS_GROUP');
3125 l_single_business_group_id number := FND_PROFILE.VALUE('OTA_HR_GLOBAL_BUSINESS_GROUP_ID');
3126
3127
3128 Begin
3129 --
3130 hr_utility.set_location('Entering:'||l_proc, 5);
3131 --
3132
3133 if (((p_event_id is not null) and
3134 nvl(ota_evt_shd.g_old_rec.owner_id,hr_api.g_number) <>
3135 nvl(p_owner_id,hr_api.g_number))
3136 or (p_event_id is null)) then
3137
3138 IF p_owner_id is not null then
3142 OPEN c_people_cross;
3139
3140 If l_single_business_group_id is not null then
3141 hr_utility.set_location('Entering:'||l_proc, 10);
3143 FETCH c_people_cross into l_exist;
3144 if c_people_cross%notfound then
3145 close c_people_cross;
3146 fnd_message.set_name('OTA','OTA_13887_EVT_OWNER_INVALID');
3147 fnd_message.raise_error;
3148 end if;
3149 close c_people_cross;
3150 else
3151 hr_utility.set_location('Entering:'||l_proc, 20);
3152 OPEN c_people;
3153 FETCH c_people into l_exist;
3154 if c_people%notfound then
3155 close c_people;
3156 fnd_message.set_name('OTA','OTA_13887_EVT_OWNER_INVALID');
3157 fnd_message.raise_error;
3158 end if;
3159 close c_people;
3160 end if;
3161 hr_utility.set_location('Leaving:'||l_proc, 40);
3162 END IF;
3163 End if;
3164 EXCEPTION
3165 WHEN app_exception.application_exception THEN
3166 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.OWNER_ID') THEN
3167 hr_utility.set_location(' Leaving:'||l_proc, 40);
3168 RAISE;
3169 END IF;
3170 hr_utility.set_location(' Leaving:'||l_proc, 50);
3171 end check_owner_id;
3172
3173 -- ----------------------------------------------------------------------------
3174 -- |---------------------------< check_line_id >---------------------------|
3175 -- ----------------------------------------------------------------------------
3176 Procedure check_line_id
3177 (p_event_id in number
3178 ,p_line_id in number
3179 ,p_org_id in number) is
3180
3181 --
3182 l_proc varchar2(72) := g_package||'chk_line_id';
3183 l_exists varchar2(1);
3184
3185 --
3186 -- cursor to check if line is exist in OE_ORDER_LINES .
3187 --
3188 cursor csr_order_line is
3189 select null
3190 from oe_order_lines_all
3191 where line_id = p_line_id;
3192
3193 Begin
3194 hr_utility.set_location('Entering:'||l_proc, 5);
3195
3196 if (((p_event_id is not null) and
3197 nvl(ota_evt_shd.g_old_rec.line_id,hr_api.g_number) <>
3198 nvl(p_line_id,hr_api.g_number))
3199 or (p_event_id is null)) then
3200 --
3201 hr_utility.set_location('Entering:'||l_proc, 10);
3202 if (p_line_id is not null) then
3203 hr_utility.set_location('Entering:'||l_proc, 15);
3204 open csr_order_line;
3205 fetch csr_order_line into l_exists;
3206 if csr_order_line%notfound then
3207 close csr_order_line;
3208 fnd_message.set_name('OTA','OTA_13888_TDB_LINE_INVALID');
3209 fnd_message.raise_error;
3210 end if;
3211 close csr_order_line;
3212 hr_utility.set_location('Entering:'||l_proc, 20);
3213 end if;
3214 end if;
3215 hr_utility.set_location('Entering:'||l_proc, 30);
3216 EXCEPTION
3217 WHEN app_exception.application_exception THEN
3218 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.LINE_ID') THEN
3219 hr_utility.set_location(' Leaving:'||l_proc, 40);
3220 RAISE;
3221 END IF;
3222 hr_utility.set_location(' Leaving:'||l_proc, 50);
3223 end check_line_id;
3224
3225 -- ----------------------------------------------------------------------------
3226 -- |---------------------------< chk_status_changed >----------------------|
3227 -- ----------------------------------------------------------------------------
3228 -- This procedure will check whether the status is changed. this procedure is
3229 -- called by post_update procedure and will be only used by OM integration.
3230 -- The purpose of this procedure is to cancel an order line, Create RMA and
3231 -- To notify the Workflow to continue.
3232
3233 Procedure chk_status_changed
3234 (p_line_id in number
3235 ,p_event_status in varchar2
3236 ,p_event_id in number
3237 ,p_org_id in number
3238 ,p_owner_id in number
3239 ,p_event_title in varchar2
3240 ) is
3241
3242 l_proc varchar2(72) := g_package||'chk_status_changed';
3243
3244 l_event_status_changed boolean :=
3245 ota_general.value_changed (ota_evt_shd.g_old_rec.event_status,
3246 p_event_status);
3247 l_status_type ota_booking_status_types.type%type;
3248 l_old_status_type ota_booking_status_types.type%type;
3249 l_invoice_rule varchar2(80);
3250 l_exist varchar2(1);
3251 l_dynamicSqlString VARCHAR2(2000);
3252 l_ins_status VARCHAR2(1);
3253 l_industry VARCHAR2(1);
3254 l_err_num VARCHAR2(30) := '';
3255 l_err_msg VARCHAR2(1000) := '';
3256
3257 --
3258 Begin
3259 hr_utility.set_location('Entering:'||l_proc, 5);
3260 --
3261 IF p_line_id is not null THEN
3262 IF l_event_status_changed THEN
3263 IF p_event_status = 'A' THEN
3264
3265 hr_utility.set_location('Entering:'||l_proc, 10);
3266
3267 ota_utility.check_invoice(
3268 p_line_id => p_line_id,
3269 p_org_id => p_org_id,
3270 p_exist => l_exist);
3271 IF fnd_installation.get(660, 660, l_ins_status, l_industry) THEN
3275 hr_utility.set_location('Entering:'||l_proc, 15);
3272 BEGIN
3273 IF l_exist = 'Y' THEN
3274 Begin
3276
3277 /* l_dynamicSqlString := '
3278 ota_om_upd_api.create_rma(
3279 :p_Line_id,
3280 :p_org_id);';
3281 EXECUTE IMMEDIATE l_dynamicSqlString
3282 USING IN p_line_id,
3283 IN p_org_id;*/
3284 ota_om_upd_api.create_rma(p_line_id,p_org_id);
3285 exception when others then
3286 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3287 hr_utility.set_message_token('PROCEDURE', l_proc);
3288 hr_utility.set_message_token('STEP','15');
3289 hr_utility.raise_error;
3290
3291 End;
3292 ELSE
3293 Begin
3294 hr_utility.set_location('Entering:'||l_proc, 20);
3295 /*l_dynamicSqlString := '
3296 ota_om_upd_api.cancel_order(
3297 p_Line_id,
3298 p_org_id);' ;
3299 EXECUTE IMMEDIATE l_dynamicSqlString
3300 USING IN p_line_id,
3301 IN p_org_id;*/
3302 ota_om_upd_api.cancel_order(p_line_id,p_org_id);
3303 exception when others then
3304 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3305 hr_utility.set_message_token('PROCEDURE', l_proc);
3306 hr_utility.set_message_token('STEP','20');
3307 hr_utility.raise_error;
3308
3309 End;
3310
3311 END IF;
3312
3313 END;
3314
3315
3316 ota_initialization_wf.INITIALIZE_CANCEL_EVENT(
3317 p_Line_id => p_Line_id,
3318 p_org_id => p_org_id,
3319 p_Status => null,
3320 p_Event_id => p_event_id,
3321 p_owner_id => p_owner_id,
3322 p_itemtype => 'OTWF',
3323 p_event_title => p_event_title);
3324 END IF;
3325
3326
3327 END IF;
3328
3329 END IF;
3330 END IF;
3331
3332
3333 hr_utility.set_location('Leaving:'||l_proc, 10);
3334 /*EXCEPTION WHEN OTHERS
3335 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;*/
3336
3337 end chk_status_changed;
3338
3339 -- ----------------------------------------------------------------------------
3340 -- |---------------------------< chk_Order_line_exist >----------------------|
3341 -- ----------------------------------------------------------------------------
3342 -- Description : This procedure will be called by Delete_validate procedure. This
3343 -- procedure will check whether order line exist or not.
3344
3345 --
3346 Procedure chk_Order_line_exist
3347 (p_line_id in number
3348 ,p_org_id in number) is
3349
3350 --
3351 l_proc varchar2(72) := g_package||'chk_order_line_exist';
3352
3353
3354 Begin
3355 hr_utility.set_location('Entering:'||l_proc, 5);
3356
3357 if p_line_id is not null then
3358 fnd_message.set_name('OTA','OTA_13896_EVT_ORDER_LINE_EXIST');
3359 fnd_message.raise_error;
3360 hr_utility.set_location('Entering:'||l_proc, 20);
3361
3362 end if;
3363 hr_utility.set_location('Leaving:'||l_proc, 30);
3364 EXCEPTION
3365 WHEN app_exception.application_exception THEN
3366 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.LINE_ID') THEN
3367 hr_utility.set_location(' Leaving:'||l_proc, 40);
3368 RAISE;
3369 END IF;
3370 hr_utility.set_location(' Leaving:'||l_proc, 50);
3371 end chk_order_line_exist;
3372
3373
3374 -- ----------------------------------------------------------------------------
3375 -- |-----------------------< chk_Training_center >---------------------------|
3379 -- procedure will check whether Training center exist or not.
3376 -- ----------------------------------------------------------------------------
3377 -- Description : This procedure will be called by Insert_validate procedure and
3378 -- Update_validaate procedure. This
3380
3381 --
3382 Procedure chk_Training_center
3383 (p_event_id in number,
3384 p_training_center_id in number)
3385 IS
3386
3387
3388 --
3389 l_proc varchar2(72) := g_package||'chk_training_center';
3390 l_exists varchar2(1);
3391
3392 Cursor c_training_center
3393 IS
3394 Select null
3395 From HR_ALL_ORGANIZATION_UNITS
3396 Where organization_id = p_training_center_id;
3397
3398 Begin
3399 hr_utility.set_location('Entering:'||l_proc, 5);
3400 if (((p_event_id is not null) and
3401 nvl(ota_evt_shd.g_old_rec.training_center_id,hr_api.g_number) <>
3402 nvl(p_training_center_id,hr_api.g_number))
3403 or (p_event_id is null)) then
3404 --
3405 hr_utility.set_location('Entering:'||l_proc, 10);
3406 if (p_training_center_id is not null) then
3407 hr_utility.set_location('Entering:'||l_proc, 15);
3408 open c_training_center;
3409 fetch c_training_center into l_exists;
3410 if c_training_center%notfound then
3411 close c_training_center;
3412 fnd_message.set_name('OTA','OTA_13907_TSR_TRNCTR_NOT_EXIST');
3413 fnd_message.raise_error;
3414 end if;
3415 close c_training_center;
3416 hr_utility.set_location('Entering:'||l_proc, 20);
3417 end if;
3418 end if;
3419 hr_utility.set_location('Entering:'||l_proc, 30);
3420 EXCEPTION
3421 WHEN app_exception.application_exception THEN
3422 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.TRAINING_CENTER_ID') THEN
3423 hr_utility.set_location(' Leaving:'||l_proc, 40);
3424 RAISE;
3425 END IF;
3426 hr_utility.set_location(' Leaving:'||l_proc, 50);
3427 end;
3428
3429 -- ----------------------------------------------------------------------------
3430 -- |-----------------------------< chk_location >---------------------------|
3431 -- ----------------------------------------------------------------------------
3432 -- Description : This procedure will be called by Insert_validate procedure and
3433 -- Update_validaate procedure. This
3434 -- procedure will check whether Location exist or not.
3435
3436 --
3437 Procedure Chk_location
3438 (p_event_id in number,
3439 p_location_id in number,
3440 p_training_center_id in number,
3441 p_course_end_date in date)
3442 IS
3443
3444
3445 --
3446 l_proc varchar2(72) := g_package||'chk_location';
3447 l_exists varchar2(1);
3448 Cursor c_location
3449 IS
3450 Select null
3451 From HR_LOCATIONS_ALL loc
3452 Where loc.location_id = p_location_id
3453 and nvl(loc.inactive_date,to_date('31-12-4712','DD-MM-YYYY')) >= nvl(p_course_end_date,sysdate);
3454
3455 Begin
3456 hr_utility.set_location('Entering:'||l_proc, 5);
3457
3458 if (((p_event_id is not null) and
3459 nvl(ota_evt_shd.g_old_rec.location_id,hr_api.g_number) <>
3460 nvl(p_location_id,hr_api.g_number))
3461 or (p_event_id is null)) then
3462 --
3463 hr_utility.set_location('Entering:'||l_proc, 10);
3464 if (p_location_id is not null) then
3465 hr_utility.set_location('Entering:'||l_proc, 15);
3466 open c_location;
3467 fetch c_location into l_exists;
3468 if c_location%notfound then
3469 close c_location;
3470 fnd_message.set_name('OTA','OTA_13908_TSR_LOC_NOT_EXIST');
3471 fnd_message.raise_error;
3472 end if;
3473 close c_location;
3474 hr_utility.set_location('Entering:'||l_proc, 20);
3475 end if;
3476 end if;
3477 hr_utility.set_location('Entering:'||l_proc, 30);
3478 EXCEPTION
3479 WHEN app_exception.application_exception THEN
3480 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.LOCATION_ID') THEN
3481 hr_utility.set_location(' Leaving:'||l_proc, 40);
3482 RAISE;
3483 END IF;
3484 hr_utility.set_location(' Leaving:'||l_proc, 50);
3485 end;
3486
3487 --
3488 -- ----------------------------------------------------------------------------
3489 -- |-----------------------------< check_unique_offering_id>------------------------|
3490 -- ----------------------------------------------------------------------------
3491 --
3492 -- PUBLIC
3493 -- Description:
3494 -- Check uniqueness of offering_id
3495 --
3496 --
3497 --
3498 --
3499 Procedure check_unique_offering_id
3500 (
3501 p_event_id in number,
3502 p_offering_id in number)
3503
3504 IS
3505
3506 l_proc varchar2(72) := g_package||'check_unique_offering_id';
3507 l_exists varchar2(1);
3508
3509 cursor csr_offering is
3510 select null
3511 from ota_events
3512 where offering_id = p_offering_id;
3513
3514 Begin
3515
3516 hr_utility.set_location('Entering:'||l_proc, 5);
3517
3518 if (((p_event_id is not null) and
3522 --
3519 nvl(ota_evt_shd.g_old_rec.offering_id,hr_api.g_number) <>
3520 nvl(p_offering_id,hr_api.g_number))
3521 or (p_event_id is null)) then
3523 hr_utility.set_location('Entering:'||l_proc, 10);
3524 if (p_offering_id is not null) then
3525 hr_utility.set_location('Entering:'||l_proc, 15);
3526 open csr_offering;
3527 fetch csr_offering into l_exists;
3528 if csr_offering%found then
3529 ota_evt_shd.constraint_error(p_constraint_name =>'OTA_EVENTS_UK4');
3530 end if;
3531 close csr_offering;
3532 hr_utility.set_location('Leaving:'||l_proc, 20);
3533 end if;
3534 end if;
3535 hr_utility.set_location('Leaving:'||l_proc, 30);
3536 EXCEPTION
3537 WHEN app_exception.application_exception THEN
3538 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.OFFERING_ID') THEN
3539 hr_utility.set_location(' Leaving:'||l_proc, 70);
3540 RAISE;
3541 END IF;
3542 hr_utility.set_location(' Leaving:'||l_proc, 80);
3543 End;
3544
3545 --
3546 -- ----------------------------------------------------------------------------
3547 -- |-----------------------------< chk_activity_version_id>--------------------|
3548 -- ----------------------------------------------------------------------------
3549 --
3550 -- PUBLIC
3551 -- Description:
3552 -- Check if parent_offering_id belongs to the activity_version_id
3553 --
3554 --
3555 --
3556 --
3557 Procedure chk_activity_version_id
3558 (p_activity_version_id in number,
3559 p_parent_offering_id in number)
3560
3561 IS
3562
3563 l_proc varchar2(72) := g_package||'chk_activity_version_id';
3564 l_exists varchar2(1);
3565
3566 CURSOR csr_offering IS
3567 SELECT null
3568 FROM ota_offerings off,
3569 ota_activity_versions act
3570 WHERE off.offering_id = p_parent_offering_id
3571 AND off.activity_version_id = act.activity_version_id
3572 AND act.activity_version_id = p_activity_version_id;
3573
3574 Begin
3575
3576 hr_utility.set_location('Entering:'||l_proc, 5);
3577 open csr_offering;
3578 fetch csr_offering into l_exists;
3579 if csr_offering%notfound then
3580 close csr_offering;
3581 fnd_message.set_name('OTA','OTA_443321_EVT_OFF_INVALID_ACT');
3582 fnd_message.raise_error;
3583 end if;
3584 close csr_offering;
3585 hr_utility.set_location('Leaving:'||l_proc, 20);
3586 EXCEPTION
3587 WHEN app_exception.application_exception THEN
3588 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.ACTIVITY_VERSION_ID') THEN
3589 hr_utility.set_location(' Leaving:'||l_proc, 70);
3590 RAISE;
3591 END IF;
3592 hr_utility.set_location(' Leaving:'||l_proc, 80);
3593 End chk_activity_version_id;
3594
3595 --
3596 -- ----------------------------------------------------------------------------
3597 -- |----------------------< chk_secure_event_flag >--------------------------|
3598 -- ----------------------------------------------------------------------------
3599 --
3600 -- PUBLIC
3601 -- Description:
3602 -- Check if the secure class is being modified by the user who belongs to
3603 -- the sponsor org. if not, throw an error.
3604 --
3605 --
3606 --
3607 Procedure chk_secure_event_flag (p_organization_id in number)
3608 IS
3609
3610 l_proc varchar2(72) := g_package||'chk_secure_event_flag';
3611 l_username fnd_user.user_name%TYPE;
3612 l_user fnd_user.user_name%TYPE;
3613 l_condition boolean;
3614
3615 CURSOR csr_org IS
3616 SELECT user_name
3617 FROM fnd_user f,
3618 per_all_assignments_f p
3619 WHERE p.organization_id = p_organization_id
3620 AND f.employee_id = p.person_id
3621 AND trunc(sysdate) BETWEEN p.effective_start_date AND p.effective_end_date
3622 AND f.user_id = to_number(fnd_profile.value('USER_ID'));
3623
3624 BEGIN
3625 hr_utility.set_location('Entering:'||l_proc, 5);
3626
3627 OPEN csr_org;
3628 FETCH csr_org INTO l_username;
3629 if csr_org%notfound then
3630 fnd_message.set_name('OTA', 'OTA_EVT_SECURE');
3631 fnd_message.raise_error;
3632 end if;
3633 close csr_org;
3634
3635
3636
3637 /* OPEN csr_org;
3638 FETCH csr_org INTO l_username;
3639 l_user := fnd_profile.value('USERNAME');
3640 l_condition := nvl(l_user, 'UNSET1') = nvl(l_username, 'UNSET2');
3641 IF NOT l_condition THEN
3642 fnd_message.set_name('OTA', 'OTA_EVT_SECURE');
3643 fnd_message.raise_error;
3644 END IF;
3645 CLOSE csr_org;*/
3646 hr_utility.set_location('Leaving:'||l_proc, 20);
3647
3648 EXCEPTION
3649 WHEN app_exception.application_exception THEN
3650 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.SECURE_EVENT_FLAG') THEN
3651 hr_utility.set_location('Leaving:'||l_proc, 40);
3652 RAISE;
3653 END IF;
3654 hr_utility.set_location('Leaving:'||l_proc, 50);
3655 END chk_secure_event_flag;
3656 --
3657 -- ----------------------------------------------------------------------------
3658 -- |---------------------------< insert_validate >----------------------------|
3659 -- ----------------------------------------------------------------------------
3660 -- VT 05/06/97 #488173
3664 --
3661 Procedure insert_validate(p_rec in out nocopy ota_evt_shd.g_rec_type) is
3662 --
3663 l_proc varchar2(72) := g_package||'insert_validate';
3665 Begin
3666 --
3667 hr_utility.set_location('Entering:'||l_proc, 5);
3668 --modified for eBS by asud
3669 /*
3670 -- Call all supporting business operations
3671 --
3672 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
3673 --
3674 VALIDITY_CHECKS (
3675 P_REC => P_REC);
3676 --
3677 hr_utility.set_location(' Leaving:'||l_proc, 10);
3678 --*/
3679 --modified for eBS by asud
3680 -- Call all supporting business operations
3681 -- Validate Important Attributes
3682 hr_api.validate_bus_grp_id(p_business_group_id => p_rec.business_group_id,
3683 p_associated_column1 => ota_evt_shd.g_tab_nam||'.BUSINESS_GROUP_ID'); -- Validate Bus Grp
3684 --
3685 hr_multi_message.end_validation_set;
3686
3687 VALIDITY_CHECKS (
3688 P_REC => P_REC);
3689
3690 -- bug 4348022
3691 IF p_rec.secure_event_flag = 'Y' THEN
3692 chk_secure_event_flag(p_organization_id => p_rec.organization_id);
3693 END IF;
3694 -- bug 4348022
3695
3696 ota_evt_bus.chk_ddf(p_rec);
3697 --
3698 hr_utility.set_location(' Leaving:'||l_proc, 10);
3699
3700 End insert_validate;
3701 --
3702 -- ----------------------------------------------------------------------------
3703 -- |---------------------------< update_validate >----------------------------|
3704 -- ----------------------------------------------------------------------------
3705 -- VT 05/06/97 #488173
3706 Procedure update_validate(p_rec in out nocopy ota_evt_shd.g_rec_type) is
3707 --
3708 l_proc varchar2(72) := g_package||'update_validate';
3709 l_secure_event_flag_changed boolean
3710 := ota_general.value_changed(ota_evt_shd.g_old_rec.secure_event_flag, p_rec.secure_event_flag);
3711 l_organization_id_changed boolean
3712 := ota_general.value_changed(ota_evt_shd.g_old_rec.organization_id, p_rec.organization_id);
3713 --
3714 Begin
3715 --
3716 hr_utility.set_location('Entering:'||l_proc, 5);
3717 --
3718 -- Call all supporting business operations
3719 --
3720 /*
3721 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
3722 --
3723 VALIDITY_CHECKS (
3724 P_REC => P_REC);
3725 --
3726 hr_utility.set_location(' Leaving:'||l_proc, 10);
3727 --
3728 */
3729 -- Validate Important Attributes
3730 hr_api.validate_bus_grp_id(p_business_group_id => p_rec.business_group_id,
3731 p_associated_column1 => ota_evt_shd.g_tab_nam||'.BUSINESS_GROUP_ID'); -- Validate Bus Grp
3732 --
3733 hr_multi_message.end_validation_set;
3734
3735 VALIDITY_CHECKS (
3736 P_REC => P_REC);
3737 --
3738 -- bug 4348022
3739 IF p_rec.secure_event_flag = 'Y' THEN
3740 IF l_secure_event_flag_changed THEN
3741 chk_secure_event_flag(p_organization_id => p_rec.organization_id);
3742 ELSE -- secure event flag not changed
3743 IF l_organization_id_changed THEN
3744 chk_secure_event_flag(p_organization_id => ota_evt_shd.g_old_rec.organization_id);
3745 chk_secure_event_flag(p_organization_id => p_rec.organization_id);
3746 ELSE
3747 chk_secure_event_flag(p_organization_id => p_rec.organization_id);
3748 END IF;
3749 END IF;
3750 ELSE
3751 IF l_secure_event_flag_changed THEN
3755 -- bug 4348022
3752 chk_secure_event_flag(p_organization_id => ota_evt_shd.g_old_rec.organization_id);
3753 END IF;
3754 END IF;
3756
3757 ota_evt_bus.chk_ddf(p_rec);
3758 --
3759 hr_utility.set_location(' Leaving:'||l_proc, 10);
3760 --
3761
3762 End update_validate;
3763 --
3764 -- ----------------------------------------------------------------------------
3765 -- |---------------------------< delete_validate >----------------------------|
3766 -- ----------------------------------------------------------------------------
3767 Procedure delete_validate(p_rec in ota_evt_shd.g_rec_type) is
3768 --
3769 l_proc varchar2(72) := g_package||'delete_validate';
3770 --
3771 Begin
3772 hr_utility.set_location('Entering:'||l_proc, 5);
3773 --
3774 -- Call all supporting business operations
3775 check_child_entities (p_event_id => p_rec.event_id);
3776 chk_Order_line_exist(ota_evt_shd.g_old_rec.line_id
3777 ,ota_evt_shd.g_old_rec.org_id) ;
3778 --
3779 IF ota_evt_shd.g_old_rec.secure_event_flag = 'Y' THEN
3780 chk_secure_event_flag(p_organization_id => ota_evt_shd.g_old_rec.organization_id);
3781 END IF;
3782 --
3783 hr_utility.set_location(' Leaving:'||l_proc, 10);
3784 End delete_validate;
3785 --
3786 end ota_evt_bus;