[Home] [Help]
PACKAGE BODY: APPS.OTA_EVT_BUS
Source
1 PACKAGE BODY OTA_EVT_BUS as
2 /* $Header: otevt01t.pkb 120.19 2011/04/07 13:34:40 shwnayak 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;
381 --
382 close c1;
383 --
384 return l_event_title;
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
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);
522 RAISE;
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.
657 --
658 Function enrollment_after_event_end (
659 p_enrollment_end_date in out nocopy date,
660 p_course_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
798 where event_id = p_event_id;
799 --
800 cursor get_tea is
801 select null
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 -- ----------------------------------------------------------------------------
934 -- |-----------------------< check_enrollment_dates >-------------------------|
935 -- ----------------------------------------------------------------------------
936 --
937 -- PUBLIC
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,
1068 p_course_end_date in date,
1069 p_event_status in varchar2,
1070 p_event_type in varchar2) is
1071 --
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
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,
1223 p_event_status in varchar2) is
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 --
1358 cursor c_check_dates is
1359 select start_date, end_date
1360 from ota_booking_deals
1361 where event_id = p_event_id;
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,
1510 'P_BUSINESS_GROUP_ID',
1511 P_BUSINESS_GROUP_ID);
1512 --
1513 -- Unique ?
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,
1590 p_object_version_number IN NUMBER default null) is
1591 --
1592 CURSOR get_event_dates_cr is
1593 SELECT course_start_date,
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 IF (l_course_start_time IS NOT NULL) AND (p_session_start_time IS NULL) THEN
1667 fnd_message.set_name('OTA','OTA_13563_EVT_SESSION_TIME');
1668 fnd_message.raise_error;
1669 END IF;
1670 END IF;
1671
1672 IF l_course_end_date = p_session_start_date THEN
1673 IF (l_course_end_time IS NOT NULL) AND
1674 (p_session_end_time IS NOT NULL) THEN
1675 IF substr(l_course_end_time ,1,2) =
1676 substr(p_session_end_time ,1,2) THEN
1677 IF substr(l_course_end_time ,4,2) <
1678 substr(p_session_end_time ,4,2) THEN
1679 fnd_message.set_name('OTA','OTA_13563_EVT_SESSION_TIME');
1680 fnd_message.raise_error;
1681 END IF;
1682 ELSIF substr(l_course_end_time ,1,2) <
1683 substr(p_session_end_time ,1,2) THEN
1684 fnd_message.set_name('OTA','OTA_13563_EVT_SESSION_TIME');
1685 fnd_message.raise_error;
1686 END IF;
1687 END IF;
1688 IF (l_course_end_time IS NOT NULL) AND (p_session_start_time IS NOT NULL) THEN
1689 IF substr(l_course_end_time ,1,2) = substr(p_session_start_time ,1,2) THEN
1690 IF substr(l_course_end_time ,4,2) < substr(p_session_start_time ,4,2) THEN
1691 fnd_message.set_name('OTA','OTA_13563_EVT_SESSION_TIME');
1692 fnd_message.raise_error;
1693 END IF;
1694 ELSIF substr(l_course_end_time ,1,2) < substr(p_session_start_time ,1,2) THEN
1695 fnd_message.set_name('OTA','OTA_13563_EVT_SESSION_TIME');
1696 fnd_message.raise_error;
1697 END IF;
1698 END IF;
1699 IF (l_course_end_time IS NOT NULL) AND (p_session_end_time IS NULL) THEN
1700 fnd_message.set_name('OTA','OTA_13563_EVT_SESSION_TIME');
1701 fnd_message.raise_error;
1702 END IF;
1703 END IF;
1704 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC, 10);
1705
1706 EXCEPTION
1707 WHEN app_exception.application_exception THEN
1708 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.COURSE_START_TIME') THEN
1709 hr_utility.set_location(' Leaving:'||w_proc, 60);
1710 RAISE;
1711 END IF;
1712 hr_utility.set_location(' Leaving:'||w_proc, 70);
1713 END check_session_time;
1714
1715 --------------------------------------------------------------------------------
1716 -- ----------------------------------------------------------------------------
1717 -- ---------------------< COURSE_DATES_SPAN_SESSIONS >-------------------------
1718 -- ----------------------------------------------------------------------------
1719 --
1720 -- Returns TRUE if the course dates for an event still span the dates of
1721 -- its sessions. This function is overloaded so that one can check either
1722 -- a new session date is valid or that updates to the course dates will
1723 -- not invalidate any sessions.
1724 --
1725 -- This version of the function checks that a new or updated session date
1726 -- lies within the course dates of its parent event.
1727 --
1728 function COURSE_DATES_SPAN_SESSIONS (
1729 P_PARENT_EVENT_ID in number,
1730 P_NEW_SESSION_DATE in date
1731 ) return boolean is
1732 --
1733 W_PROC varchar2 (72)
1734 := G_PACKAGE || 'COURSE_DATES_SPAN_SESSIONS';
1735 --
1736 L_COURSE_START_DATE date;
1737 L_COURSE_END_DATE date;
1738 L_COURSE_SPANS_SESSIONS boolean;
1739 --
1740 begin
1741 --
1742 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC, 5);
1743 --
1744 -- Check parameters
1745 --
1746 HR_API.MANDATORY_ARG_ERROR (
1747 P_API_NAME => G_PACKAGE,
1748 P_ARGUMENT => 'P_PARENT_EVENT_ID',
1749 P_ARGUMENT_VALUE => P_PARENT_EVENT_ID);
1750 --
1751 if (P_NEW_SESSION_DATE is not null) then
1752 OTA_EVT_SHD.GET_COURSE_DATES (
1753 P_PARENT_EVENT_ID,
1754 L_COURSE_START_DATE,
1755 L_COURSE_END_DATE);
1756 --
1757 HR_UTILITY.TRACE ('Start date: ' || to_char (L_COURSE_START_DATE));
1758 HR_UTILITY.TRACE (' End date: ' || to_char (L_COURSE_END_DATE));
1759 --
1760 -- check if course start and end date are null
1761 -- if null then set them to start and end of time
1762 --
1763 if l_course_start_date is null or l_course_end_date is null then
1764 fnd_message.set_name('OTA','OTA_13579_EVT_SESSION_DATES');
1765 fnd_message.raise_error;
1766 end if;
1767 /* if l_course_start_date is null then
1768 l_course_start_date := hr_api.g_sot;
1769 end if;
1770 if l_course_end_date is null then
1771 l_course_end_date := hr_api.g_eot;
1772 end if; */
1773 L_COURSE_SPANS_SESSIONS :=
1774 ( (L_COURSE_START_DATE is not null)
1775 and (L_COURSE_END_DATE is not null)
1776 and (P_NEW_SESSION_DATE between L_COURSE_START_DATE
1777 and L_COURSE_END_DATE));
1778 else
1779 L_COURSE_SPANS_SESSIONS := true;
1780 end if;
1781 --
1782 return L_COURSE_SPANS_SESSIONS;
1783 --
1784 end COURSE_DATES_SPAN_SESSIONS;
1785 --
1786 -- This version of the function checks that updated course dates do not
1787 -- invalidate any of the event's sessions.
1788 --
1789 function COURSE_DATES_SPAN_SESSIONS (
1790 p_event_id number,
1791 p_course_start_date date,
1792 p_course_end_date date) return boolean is
1793 --
1794 W_PROC constant varchar2(72) := G_PACKAGE||'course_dates_span_sessions';
1795 --
1796 l_sessions_invalidated boolean;
1797 --
1798 cursor csr_invalid_sessions is
1799 select 1
1800 from ota_events
1801 where parent_event_id = p_event_id
1802 and event_type = 'SESSION'
1803 and course_start_date
1804 not between p_course_start_date
1805 and p_course_end_date;
1806 --
1807 begin
1808 --
1809 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC, 5);
1810 --
1811 -- Check parameters
1812 --
1813 HR_API.MANDATORY_ARG_ERROR (
1814 G_PACKAGE,
1815 'p_event_id',
1816 p_event_id);
1817 --
1818 if p_course_start_date is null or p_course_end_date is null then
1819 l_sessions_invalidated := TRUE;
1820 else
1821 open csr_invalid_sessions;
1822 fetch csr_invalid_sessions into g_dummy;
1823 l_sessions_invalidated := csr_invalid_sessions%found;
1824 close csr_invalid_sessions;
1825 end if;
1826 --
1827 HR_UTILITY.SET_LOCATION (W_PROC,10);
1828 --
1829 return NOT l_sessions_invalidated;
1830 --
1831 end course_dates_span_sessions;
1832 --
1833 -- ----------------------------------------------------------------------------
1834 -- ---------------------< check_class_session_times >--------------------------
1835 -- ----------------------------------------------------------------------------
1836 -- Added for Bug 3622035
1837 -- This procedure checks if the session time is between the parent start
1838 -- and end time.
1839 --
1840 --------------------------------------------------------------------------------
1841 PROCEDURE check_class_session_times ( p_event_id IN ota_events.event_id%TYPE,
1842 p_course_start_date IN ota_events.course_start_date%TYPE,
1843 p_course_start_time IN ota_events.course_start_time%TYPE,
1844 p_course_end_date IN ota_events.course_end_date%TYPE,
1845 p_course_end_time IN ota_events.course_end_time%TYPE) is
1846 --
1847 CURSOR get_ssn_times_cr is
1848 SELECT course_start_date,
1849 course_start_time,
1850 course_end_date,
1851 course_end_time
1852 FROM ota_events
1853 WHERE parent_event_id = p_event_id
1854 AND event_type = 'SESSION';
1855
1856 l_ssn_start_date ota_events.course_start_date%type;
1857 l_ssn_start_time ota_events.course_start_time%type;
1858 l_ssn_end_date ota_events.course_start_date%type;
1859 l_ssn_end_time ota_events.course_start_time%type;
1860
1861 w_proc constant varchar2(72) := G_PACKAGE||'check_class_session_times';
1862
1863 BEGIN
1864
1865 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC, 5);
1866 -- --
1867 --
1868 --
1869 HR_UTILITY.TRACE ('Parent: ' || to_char (P_EVENT_ID));
1870
1871 OPEN get_ssn_times_cr;
1872 LOOP
1873 FETCH get_ssn_times_cr INTO l_ssn_start_date,
1874 l_ssn_start_time,
1875 l_ssn_end_date,
1876 l_ssn_end_time;
1877 EXIT WHEN get_ssn_times_cr%NOTFOUND;
1878
1879 -- If the Course , Session Start date are the same and
1880 -- the two start times are not null then check for correct time entries.
1881 --
1882 IF p_course_start_date = l_ssn_start_date THEN
1883 IF (p_course_start_time IS NOT NULL) AND
1884 (l_ssn_start_time IS NOT NULL) THEN
1885 IF substr(p_course_start_time ,1,2) =
1886 substr(l_ssn_start_time ,1,2) THEN
1887 IF substr(p_course_start_time ,4,2) >
1888 substr(l_ssn_start_time ,4,2) THEN
1889 fnd_message.set_name('OTA','OTA_13563_EVT_SESSION_TIME');
1890 fnd_message.raise_error;
1891 END IF;
1892 ELSIF substr(p_course_start_time ,1,2) >
1893 substr(l_ssn_start_time ,1,2) THEN
1894 fnd_message.set_name('OTA','OTA_13563_EVT_SESSION_TIME');
1895 fnd_message.raise_error;
1896 END IF;
1897 END IF;
1898 END IF;
1899
1900 IF p_course_end_date = l_ssn_start_date THEN
1901 IF (p_course_end_time IS NOT NULL) AND
1902 (l_ssn_end_time IS NOT NULL) THEN
1903 IF substr(p_course_end_time ,1,2) =
1904 substr(l_ssn_end_time ,1,2) THEN
1905 IF substr(p_course_end_time ,4,2) <
1906 substr(l_ssn_end_time ,4,2) THEN
1907 fnd_message.set_name('OTA','OTA_13563_EVT_SESSION_TIME');
1908 fnd_message.raise_error;
1909 END IF;
1910 ELSIF substr(p_course_end_time ,1,2) <
1911 substr(l_ssn_end_time ,1,2) THEN
1912 fnd_message.set_name('OTA','OTA_13563_EVT_SESSION_TIME');
1913 fnd_message.raise_error;
1914 END IF;
1915 END IF;
1916 END IF;
1917
1918 END LOOP;
1919 CLOSE get_ssn_times_cr;
1920
1921 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC, 10);
1922
1923 EXCEPTION
1924 WHEN app_exception.application_exception THEN
1925 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.COURSE_START_TIME') THEN
1926 hr_utility.set_location(' Leaving:'||w_proc, 60);
1927 RAISE;
1928 END IF;
1929 hr_utility.set_location(' Leaving:'||w_proc, 70);
1930 END check_class_session_times;
1931
1932 -- ----------------------------------------------------------------------------
1933 -- -----------------------< CHECK_UPDATED_COURSE_DATES >-----------------------
1934 -- ----------------------------------------------------------------------------
1935 --
1936 procedure CHECK_UPDATED_COURSE_DATES (
1937 P_EVENT_ID in number,
1938 P_OBJECT_VERSION_NUMBER in number,
1939 P_EVENT_TYPE in varchar2,
1940 P_COURSE_START_DATE in date,
1941 P_COURSE_END_DATE in date
1942 ) is
1943 --
1944 W_PROC constant varchar2(72) := G_PACKAGE||'check_updated_course_dates';
1945 --
1946 procedure check_parameters is
1947 --
1948 begin
1949 HR_API.MANDATORY_ARG_ERROR ( G_PACKAGE,
1950 'p_event_id',
1951 p_event_id);
1952 --
1953 HR_API.MANDATORY_ARG_ERROR ( G_PACKAGE,
1954 'p_event_type',
1955 p_event_type);
1956 end check_parameters;
1957 --
1958 function course_dates_have_changed return boolean is
1959 --
1960 l_dates_updated boolean := FALSE;
1961 --
1962 begin
1963 --
1964 if (OTA_EVT_SHD.api_updating (p_event_id, p_object_version_number)) then
1965 --
1966 l_dates_updated :=
1967 (nvl (p_course_start_date, hr_general.start_of_time) <>
1968 nvl (OTA_EVT_SHD.g_old_rec.course_start_date, hr_general.start_of_time)
1969 or nvl (p_course_end_date, hr_general.end_of_time) <>
1970 nvl (OTA_EVT_SHD.g_old_rec.course_end_date, hr_general.end_of_time)
1971 );
1972 --
1973 end if;
1974 --
1975 return l_dates_updated;
1976 --
1977 end course_dates_have_changed;
1978 --
1979 begin
1980 --
1981 HR_UTILITY.SET_LOCATION ('Entering:'||W_PROC,5);
1982 --
1983 check_parameters;
1984 --
1985 if course_dates_have_changed
1986 and p_event_type in ('PROGRAMME MEMBER','SCHEDULED')
1987 then
1988 --
1989 if NOT course_dates_span_sessions (
1990 p_event_id => p_event_id,
1991 p_course_start_date => p_course_start_date,
1992 p_course_end_date => p_course_end_date)
1993 then
1994 OTA_EVT_SHD.CONSTRAINT_ERROR ('OTA_EVT_EVENT_SESSION_SPAN');
1995 end if;
1996 --
1997 end if;
1998 --
1999 EXCEPTION
2000 WHEN app_exception.application_exception THEN
2001 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.COURSE_START_DATE',
2002 p_associated_column2 => 'OTA_EVENTS.COURSE_END_DATE') THEN
2003 hr_utility.set_location(' Leaving:'||w_proc, 40);
2004 RAISE;
2005 END IF;
2006 hr_utility.set_location(' Leaving:'||w_proc, 50);
2007
2008 end check_updated_course_dates;
2009 --
2010 -- ----------------------------------------------------------------------------
2011 -- ------------------------< check_cost_vals >---------------------------------
2012 -- ----------------------------------------------------------------------------
2013 --
2014 procedure check_cost_vals
2015 (p_budget_currency_code in varchar2
2016 ,p_budget_cost in number
2017 ,p_actual_cost in number) is
2018 --
2019 v_proc varchar2(72) := g_package||'check_cost_vals';
2020 begin
2021 --
2022 hr_utility.set_location('Entering:'|| v_proc, 5);
2023 --
2024 if (p_budget_cost is not null or p_actual_cost is not null) and
2025 p_budget_currency_code is null then
2026 --
2027 fnd_message.set_name('OTA','OTA_13394_TAV_COST_ATTR');
2028 fnd_message.raise_error;
2029 --
2030 end if;
2031 --
2032 hr_utility.set_location(' Leaving:'|| v_proc, 10);
2033 --
2034 EXCEPTION
2035 WHEN app_exception.application_exception THEN
2036 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.BUDGET_COST',
2037 p_associated_column2 => 'OTA_EVENTS.BUDGET_CURRENCY_CODE',
2038 p_associated_column3 => 'OTA_EVENTS.ACTUAL_COST') THEN
2039 hr_utility.set_location(' Leaving:'||v_proc, 40);
2040 RAISE;
2041 END IF;
2042 hr_utility.set_location(' Leaving:'||v_proc, 50);
2043 end check_cost_vals;
2044 --
2045 -- ----------------------------------------------------------------------------
2046 -- ------------------------< CHECK_SESSION_WITHIN_COURSE >---------------------
2047 -- ----------------------------------------------------------------------------
2048 --
2049 -- Checks that a session date lies between the course start and end dates
2050 -- of its parent event.
2051 --
2052 procedure CHECK_SESSION_WITHIN_COURSE (
2053 P_EVENT_TYPE in varchar2,
2054 P_PARENT_EVENT_ID in number,
2055 P_COURSE_START_DATE in date,
2056 P_EVENT_ID in number default null,
2057 P_OBJECT_VERSION_NUMBER in number default null
2058 ) is
2059 --
2060 W_PROC varchar2 (72)
2061 := G_PACKAGE || 'CHECK_SESSION_WITHIN_COURSE';
2062 --
2063 begin
2064 --
2065 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC, 5);
2066 --
2067 -- Check parameters
2068 --
2069 HR_API.MANDATORY_ARG_ERROR (
2070 P_API_NAME => G_PACKAGE,
2071 P_ARGUMENT => 'P_EVENT_TYPE',
2072 P_ARGUMENT_VALUE => P_EVENT_TYPE);
2073 HR_API.MANDATORY_ARG_ERROR (
2074 P_API_NAME => G_PACKAGE,
2075 P_ARGUMENT => 'P_PARENT_EVENT_ID',
2076 P_ARGUMENT_VALUE => P_PARENT_EVENT_ID);
2077 --
2078 -- OK ?
2079 --
2080 if not ( (OTA_EVT_SHD.API_UPDATING (P_EVENT_ID, P_OBJECT_VERSION_NUMBER))
2081 and (OTA_EVT_SHD.G_OLD_REC.COURSE_START_DATE = P_COURSE_START_DATE)) then
2082 --
2083 HR_UTILITY.TRACE ('Parent: ' || to_char (P_PARENT_EVENT_ID));
2084 --
2085 if ( (P_EVENT_TYPE = 'SESSION')
2086 and (not COURSE_DATES_SPAN_SESSIONS (
2087 P_PARENT_EVENT_ID => P_PARENT_EVENT_ID,
2088 P_NEW_SESSION_DATE => P_COURSE_START_DATE))) then
2089 OTA_EVT_SHD.CONSTRAINT_ERROR ('OTA_EVT_SESSION_TIMING');
2090 end if;
2091 --
2092 end if;
2093 --
2094 HR_UTILITY.SET_LOCATION (' Leaving:' || W_PROC, 10);
2095 --
2096 EXCEPTION
2097 WHEN app_exception.application_exception THEN
2098 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.COURSE_START_DATE') THEN
2099 hr_utility.set_location(' Leaving:'||w_proc, 40);
2100 RAISE;
2101 END IF;
2102 hr_utility.set_location(' Leaving:'||w_proc, 50);
2103 end CHECK_SESSION_WITHIN_COURSE;
2104 --
2105 -- ----------------------------------------------------------------------------
2106 -- -----------------------< VALID_PARENT_EVENT >-------------------------------
2107 -- ----------------------------------------------------------------------------
2108 --
2109 -- Returns TRUE if the parent event ID specified exists in the events
2110 -- table, has the same business group as the child row and is a valid
2111 -- parent for the event type specified.
2112 --
2113 function VALID_PARENT_EVENT (
2114 P_PARENT_EVENT_ID in number,
2115 P_BUSINESS_GROUP_ID in number,
2116 P_EVENT_TYPE in varchar2
2117 ) return boolean is
2118 --
2119 W_PARENT_ID_EXISTS boolean;
2120 W_VALID_PARENT boolean;
2121 --
2122 begin
2123 --
2124 HR_UTILITY.SET_LOCATION ('Entering:' || G_PACKAGE || 'VALID_PARENT_EVENT', 5);
2125 --
2126 -- Check parameters
2127 --
2128 HR_API.MANDATORY_ARG_ERROR (
2129 G_PACKAGE,
2130 'P_BUSINESS_GROUP_ID',
2131 P_BUSINESS_GROUP_ID);
2132 --
2133 HR_API.MANDATORY_ARG_ERROR (
2134 G_PACKAGE,
2135 'P_EVENT_TYPE',
2136 P_EVENT_TYPE);
2137 --
2138 -- Is there a parent ?
2139 --
2140 if (P_PARENT_EVENT_ID is not null) then
2141 --
2142 OTA_EVT_SHD.FETCH_EVENT_DETAILS (
2143 P_EVENT_ID => P_PARENT_EVENT_ID,
2144 P_EVENT_EXISTS => W_PARENT_ID_EXISTS);
2145 --
2146 if (W_PARENT_ID_EXISTS) then
2147 HR_UTILITY.TRACE ('Parent exists: True');
2148 else
2149 HR_UTILITY.TRACE ('Parent exists: Fales');
2150 end if;
2151 HR_UTILITY.TRACE ('Business grps: ' || to_char (P_BUSINESS_GROUP_ID)
2152 || '/' || to_char (G_FETCHED_REC.BUSINESS_GROUP_ID));
2153 HR_UTILITY.TRACE ('Types: ' || P_EVENT_TYPE
2154 || '/' || G_FETCHED_REC.EVENT_TYPE);
2155 W_VALID_PARENT :=
2156 (W_PARENT_ID_EXISTS)
2157 and (P_BUSINESS_GROUP_ID
2158 = G_FETCHED_REC.BUSINESS_GROUP_ID)
2159 and ( ( (P_EVENT_TYPE
2160 = 'SESSION')
2161 and (G_FETCHED_REC.EVENT_TYPE
2162 in ('SCHEDULED',
2163 'PROGRAMME MEMBER')))
2164 or ( (P_EVENT_TYPE
2165 = 'PROGRAMME MEMBER')
2166 and (G_FETCHED_REC.EVENT_TYPE
2167 = 'PROGRAMME')));
2168 --
2169 end if;
2170 --
2171 HR_UTILITY.SET_LOCATION ( ' Leaving:' || G_PACKAGE || 'VALID_PARENT_EVENT', 10);
2172 return W_VALID_PARENT;
2173 --
2174 end VALID_PARENT_EVENT;
2175 --
2176 -- ----------------------------------------------------------------------------
2177 -- -----------------------< CHECK_PARENT_EVENT_IS_VALID >----------------------
2178 -- ----------------------------------------------------------------------------
2179 --
2180 procedure CHECK_PARENT_EVENT_IS_VALID (
2181 P_PARENT_EVENT_ID in number,
2182 P_BUSINESS_GROUP_ID in number,
2183 P_EVENT_TYPE in varchar2,
2184 P_EVENT_ID in number default null,
2185 P_OBJECT_VERSION_NUMBER in number default null
2186 ) is
2187 begin
2188 --
2189 HR_UTILITY.SET_LOCATION (
2190 'Entering:' || G_PACKAGE || 'CHECK_PARENT_EVENT_IS_VALID',
2191 5);
2192 --
2193 if ( (P_PARENT_EVENT_ID is not null )
2194 and (not ( (OTA_EVT_SHD.API_UPDATING (P_EVENT_ID, P_OBJECT_VERSION_NUMBER))
2195 and (OTA_EVT_SHD.G_OLD_REC.PARENT_EVENT_ID = P_PARENT_EVENT_ID )))) then
2196 --
2197 if (not VALID_PARENT_EVENT (
2198 P_PARENT_EVENT_ID,
2199 P_BUSINESS_GROUP_ID,
2200 P_EVENT_TYPE)) then
2201 OTA_EVT_SHD.CONSTRAINT_ERROR ('OTA_EVT_INVALID_PARENT');
2202 end if;
2203 --
2204 end if;
2205 --
2206 HR_UTILITY.SET_LOCATION (
2207 ' Leaving:' || G_PACKAGE || 'CHECK_PARENT_EVENT_IS_VALID',
2208 10);
2209 --
2210 end CHECK_PARENT_EVENT_IS_VALID;
2211 --
2212 -- ---------------------------------------------------------------------------
2213 procedure CHECK_PROGRAM_ENROLMENT_SPAN (
2214 --*****************************************************************************
2215 --* Error if the programme's enrolment dates do not span the enrolment dates
2216 --* of all its members.
2217 --*****************************************************************************
2218 --
2219 p_event_id number,
2220 p_event_type varchar2,
2221 p_enrolment_start_date date,
2222 p_enrolment_end_date date,
2223 p_parent_event_id number default null,
2224 p_object_version_number number default null) is
2225 --
2226 function enrolment_dates_valid return boolean is
2227 --
2228 l_valid_enrol_dates boolean := TRUE;
2229 --
2230 cursor csr_programme_dates is
2231 --
2232 select 1
2233 from ota_events
2234 where event_id = p_parent_event_id
2235 and (p_enrolment_start_date not between enrolment_start_date
2236 and enrolment_end_date
2237 or p_enrolment_end_date not between enrolment_start_date
2238 and enrolment_end_date);
2239 --
2240 cursor csr_member_dates is
2241 --
2242 select 1
2243 from ota_events
2244 where parent_event_id = p_event_id
2245 and ((p_enrolment_start_date not between enrolment_start_date
2246 and enrolment_end_date)
2247 or(p_enrolment_end_date not between enrolment_start_date
2248 and enrolment_end_date));
2249 --
2250 begin
2251 --
2252 if p_event_type = 'PROGRAMME' then
2253 --
2254 -- Check that the parent dates still
2255 -- span all the child dates
2256 --
2257 open csr_member_dates;
2258 fetch csr_member_dates into g_dummy;
2259 l_valid_enrol_dates := csr_member_dates%notfound;
2260 close csr_member_dates;
2261 --
2262 elsif p_event_type = 'PROGRAMME MEMBER' then
2263 --
2264 -- Check that the new member dates are within the parent
2265 -- enrolment dates
2266 --
2267 open csr_programme_dates;
2268 fetch csr_programme_dates into g_dummy;
2269 l_valid_enrol_dates := csr_member_dates%notfound;
2270 close csr_member_dates;
2271 --
2272 end if;
2273 --
2274 return l_valid_enrol_dates;
2275 --
2276 end enrolment_dates_valid;
2277 --
2278 procedure check_parameters is
2279 --
2280 begin
2281 --
2282 HR_API.MANDATORY_ARG_ERROR ( G_PACKAGE,
2283 'p_event_type',
2284 p_event_type);
2285 --
2286 HR_API.MANDATORY_ARG_ERROR ( G_PACKAGE,
2287 'p_event_id',
2288 p_event_id);
2289 --
2290 HR_API.MANDATORY_ARG_ERROR ( G_PACKAGE,
2291 'p_enrolment_start_date',
2292 p_enrolment_start_date);
2293 --
2294 HR_API.MANDATORY_ARG_ERROR ( G_PACKAGE,
2295 'p_enrolment_end_date',
2296 p_enrolment_end_date);
2297 --
2298 if (p_event_type = 'PROGRAMME' and p_parent_event_id is not null)
2299 or (p_event_type = 'PROGRAMME MEMBER' and p_parent_event_id is null) then
2300 invalid_parameter(
2301 p_procedure_name=>'ota_evt_api.check_program_enrolment_span',
2302 p_optional_message=>'Test');
2303 end if;
2304 --
2305 end check_parameters;
2306 --
2307 begin
2308 --
2309 -- This check only applies to existing PROGRAMMEs and PROGRAMME_MEMBERs
2310 --
2311 if ( (P_EVENT_TYPE not in ('PROGRAMME', 'PROGRAMME MEMBER'))
2312 or (not OTA_EVT_SHD.api_updating (p_event_id, p_object_version_number))) then
2313 return;
2314 end if;
2315 --
2316 -- With changing enrolment dates
2317 --
2318 if ( (OTA_EVT_SHD.g_old_rec.enrolment_start_date <> p_enrolment_start_date)
2319 or (OTA_EVT_SHD.g_old_rec.enrolment_end_date <> p_enrolment_end_date )) then
2320 --
2321 check_parameters;
2322 --
2323 if NOT enrolment_dates_valid then
2324 OTA_EVT_SHD.CONSTRAINT_ERROR ('OTA_EVT_ENROLMENT_DATE_SPAN');
2325 end if;
2326 --
2327 end if;
2328 --
2329
2330 end check_program_enrolment_span;
2331 --
2332 -- ----------------------------------------------------------------------------
2333 -- -------------------------< Price_Basis_Change >-----------------------------
2334 -- ----------------------------------------------------------------------------
2335 --
2336 -- Price Basis Changes are not allowed if Enrollments or Event Associations
2337 -- exist for the Event
2338 --
2339 procedure price_basis_change(p_event_id number
2340 ,p_price_basis varchar2) is
2341 --
2342 l_proc varchar2(72) := 'price_basis_change';
2343 l_exists varchar2(1);
2344 --
2345 l_price_basis_changed boolean :=
2346 ota_general.value_changed(ota_evt_shd.g_old_rec.price_basis
2347 ,p_price_basis);
2348 --
2349 cursor get_enrollments is
2350 select null
2351 from ota_delegate_bookings
2352 where event_id = p_event_id;
2353 --
2354 cursor get_event_associations is
2355 select null
2356 from ota_event_associations
2357 where event_id = p_event_id;
2358 --
2359 begin
2360 hr_utility.set_location ('Entering:'||l_proc,5);
2361 --
2362 if p_event_id is not null and
2363 l_price_basis_changed then
2364 --
2365 open get_enrollments;
2366 fetch get_enrollments into l_exists;
2367 if get_enrollments%found then
2368 close get_enrollments;
2369 fnd_message.set_name('OTA','OTA_13527_PRICE_BASIS_CHANGE');
2370 fnd_message.raise_error;
2371 end if;
2372 close get_enrollments;
2373 --
2374 open get_event_associations;
2375 fetch get_event_associations into l_exists;
2376 if get_event_associations%found then
2377 close get_event_associations;
2378 fnd_message.set_name('OTA','OTA_13527_PRICE_BASIS_CHANGE');
2379 fnd_message.raise_error;
2380 end if;
2381 close get_event_associations;
2382 --
2383 end if;
2384 --
2385 hr_utility.set_location ('Leaving:'||l_proc,10);
2386 EXCEPTION
2387 WHEN app_exception.application_exception THEN
2388 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.PRICE_BASIS') THEN
2389 hr_utility.set_location(' Leaving:'||l_proc, 40);
2390 RAISE;
2391 END IF;
2392 hr_utility.set_location(' Leaving:'||l_proc, 50);
2393 end price_basis_change;
2394 --
2395 -- ----------------------------------------------------------------------------
2396 -- -------------------------< check_timezone >-----------------------------
2397 -- ----------------------------------------------------------------------------
2398 --
2399 -- Procedure to check timezone of a class
2400 --
2401 --
2402 PROCEDURE check_timezone(p_timezone IN VARCHAR2)
2403 IS
2404 l_timezone_id NUMBER := ota_timezone_util.get_timezone_id(p_timezone);
2405 BEGIN
2406 IF l_timezone_id IS NULL THEN
2407 fnd_message.set_name('OTA','OTA_443982_TIMEZONE_ERROR');
2408 fnd_message.set_token('OBJECT_TYPE',ota_utility.get_lookup_meaning('OTA_OBJECT_TYPE','CL',810));
2409 fnd_message.raise_error;
2410 END IF;
2411 END check_timezone;
2412 --
2413 -- ----------------------------------------------------------------------------
2414 -- -------------------------< check_time_format >-----------------------------
2415 -- ----------------------------------------------------------------------------
2416 --
2417 -- Procedure to check time format (HH24:MI) of a course_start_time and course_end_time bug#4895398
2418 --
2419 --
2420 PROCEDURE check_time_format(p_time IN VARCHAR2)
2421 IS
2422 BEGIN
2423 IF p_time IS NOT NULL THEN
2424 IF (NOT (LENGTH(p_time) = 5
2425 and substr (p_time, 3,1) = ':'
2426 and (substr(p_time,1,1) >= '0' and substr(p_time,1,1)<= '2')
2427 and (substr(p_time,2,1) >= '0' and substr(p_time,2,1)<= '9')
2428 and (substr(p_time,4,1) >= '0' and substr(p_time,4,1)<= '5')
2429 and (substr(p_time,5,1) >= '0' and substr(p_time,5,1)<= '9')
2430 and (to_number (substr (p_time, 1,2)) between 0 and 23
2431 and to_number (substr (p_time,4)) between 0 and 59))) then
2432 fnd_message.set_name('OTA','OTA_13444_EVT_TIME_FORMAT');
2433 fnd_message.raise_error;
2434 END IF;
2435 END IF;
2436 END;
2437
2438 -- ----------------------------------------------------------------------------
2439 -- -------------------------< VALIDITY_CHECKS >--------------------------------
2440 -- ----------------------------------------------------------------------------
2441 --
2442 -- Performs the validation routines common to both insert and update.
2443 --
2444 -- VT 05/06/97 #488173
2445 procedure VALIDITY_CHECKS (
2446 P_REC in out nocopy OTA_EVT_SHD.G_REC_TYPE
2447 ) is
2448 --
2449 W_PROC varchar2 (72)
2450 := G_PACKAGE || 'VALIDITY_CHECKS';
2451 l_course_start_date_changed boolean
2452 := ota_general.value_changed(ota_evt_shd.g_old_rec.course_start_date,
2453 p_rec.course_start_date);
2454 l_course_end_date_changed boolean
2455 := ota_general.value_changed(ota_evt_shd.g_old_rec.course_end_date,
2456 p_rec.course_end_date);
2457 l_enrolment_start_date_changed boolean
2458 := ota_general.value_changed(ota_evt_shd.g_old_rec.enrolment_start_date,
2459 p_rec.enrolment_start_date);
2460 l_enrolment_end_date_changed boolean
2461 := ota_general.value_changed(ota_evt_shd.g_old_rec.enrolment_end_date,
2462 p_rec.enrolment_end_date);
2463 l_public_event_flag_changed boolean
2464 := ota_general.value_changed(ota_evt_shd.g_old_rec.public_event_flag,
2465 p_rec.public_event_flag);
2466 l_title_changed boolean
2467 := ota_general.value_changed(ota_evt_shd.g_old_rec.title,
2468 p_rec.title);
2469 l_maximum_attendees_changed boolean
2470 := ota_general.value_changed(ota_evt_shd.g_old_rec.maximum_attendees,
2471 p_rec.maximum_attendees);
2472 l_maximum_int_att_changed boolean
2473 := ota_general.value_changed(ota_evt_shd.g_old_rec.maximum_internal_attendees,
2474 p_rec.maximum_internal_attendees);
2475 l_owner_id_changed boolean
2476 := ota_general.value_changed(ota_evt_shd.g_old_rec.owner_id,
2477 p_rec.owner_id);
2478 l_line_id_changed boolean
2479 := ota_general.value_changed(ota_evt_shd.g_old_rec.line_id,
2480 p_rec.line_id);
2481 l_training_center_id_changed boolean
2482 := ota_general.value_changed(ota_evt_shd.g_old_rec.training_center_id,
2483 p_rec.training_center_id);
2484 l_location_id_changed boolean
2485 := ota_general.value_changed(ota_evt_shd.g_old_rec.location_id,
2486 p_rec.location_id);
2487 l_offering_id_changed boolean
2488 := ota_general.value_changed(ota_evt_shd.g_old_rec.offering_id,
2489 p_rec.offering_id);
2490 l_timezone_changed boolean
2491 := ota_general.value_changed(ota_evt_shd.g_old_rec.timezone,
2492 p_rec.timezone);
2493 --bug#4895398
2494 l_course_start_time_changed boolean
2495 := ota_general.value_changed(ota_evt_shd.g_old_rec.course_start_time,p_rec.course_start_time);
2496
2497 l_course_end_time_changed boolean
2498 := ota_general.value_changed(ota_evt_shd.g_old_rec.course_end_time,p_rec.course_end_time);
2499 --bug#4895398
2500
2501 --Enhancement 1823602.
2502 l_commitment_id ra_customer_trx_all.customer_trx_id%TYPE;
2503 l_commitment_number ra_customer_trx_all.trx_number%TYPE;
2504 l_commitment_start_date ra_customer_trx_all.start_date_commitment%TYPE;
2505 l_commitment_end_date ra_customer_trx_all.end_date_commitment%TYPE;
2506
2507
2508 --
2509 begin
2510 --
2511 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC, 5);
2512 --
2513 -- Check only non-static domains (constraints trap the static ones)
2514 --
2515 OTA_GENERAL.CHECK_DOMAIN_VALUE (
2516 P_DOMAIN_TYPE => 'DEV_EVENT_TYPE',
2517 P_DOMAIN_VALUE => P_REC.DEVELOPMENT_EVENT_TYPE);
2518 --
2519 OTA_GENERAL.CHECK_DOMAIN_VALUE (
2520 P_DOMAIN_TYPE => 'TRAINING_CENTRE',
2521 P_DOMAIN_VALUE => P_REC.CENTRE);
2522 --
2523 OTA_GENERAL.CHECK_DOMAIN_VALUE (
2524 P_DOMAIN_TYPE => 'OTA_DURATION_UNITS',
2525 P_DOMAIN_VALUE => P_REC.DURATION_UNITS);
2526 --
2527 OTA_GENERAL.CHECK_DOMAIN_VALUE (
2528 P_DOMAIN_TYPE => 'EVENT_USER_STATUS',
2529 P_DOMAIN_VALUE => P_REC.USER_STATUS);
2530 --
2531 OTA_GENERAL.CHECK_DOMAIN_VALUE (
2532 P_DOMAIN_TYPE => 'SCHEDULED_EVENT_STATUS',
2533 P_DOMAIN_VALUE => P_REC.EVENT_STATUS);
2534 --
2535 OTA_GENERAL.CHECK_VENDOR_IS_VALID (
2536 P_REC.VENDOR_ID,P_REC.COURSE_START_DATE);
2537 --
2538 OTA_GENERAL.CHECK_CURRENCY_IS_VALID (
2539 P_REC.CURRENCY_CODE);
2540 --
2541 OTA_GENERAL.CHECK_LANGUAGE_IS_VALID (
2542 P_REC.LANGUAGE_ID);
2543 --
2544 if p_rec.event_id is null or l_title_changed then
2545 check_title_is_unique(
2546 P_TITLE => p_rec.title,
2547 P_BUSINESS_GROUP_ID => p_rec.business_group_id,
2548 P_PARENT_EVENT_ID => p_rec.parent_event_id,
2549 P_EVENT_ID => p_rec.event_id,
2550 P_OBJECT_VERSION_NUMBER => p_rec.object_version_number);
2551 end if;
2552 --
2553 if P_REC.EVENT_TYPE in ('SCHEDULED','PROGRAMME', 'SELFPACED') and
2554 p_rec.event_id is not null then
2555 price_basis_change(p_rec.event_id
2556 ,p_rec.price_basis);
2557
2558 end if;
2559 --bug#4895398
2560 --
2561 --Course start time and end time format check
2562 --
2563 IF(p_rec.course_start_time IS NOT NULL and l_course_start_time_changed) THEN
2564 check_time_format(p_rec.course_start_time);
2565 END IF;
2566
2567 IF(p_rec.course_end_time IS NOT NULL and l_course_end_time_changed) THEN
2568 check_time_format(p_rec.course_end_time);
2569 END IF;
2570 --end bug#4895398
2571 --
2572 -- Development event checks
2573 --
2574 if P_REC.EVENT_TYPE in ('DEVELOPMENT','SCHEDULED','SELFPACED') then
2575 --
2576 -- Check course dates are valid
2577 --
2578 /* --changes made for eBS by asud
2579 COURSE_DATES_ARE_VALID (P_REC.ACTIVITY_VERSION_ID,
2580 P_REC.COURSE_START_DATE,
2581 P_REC.COURSE_END_DATE,
2582 P_REC.EVENT_STATUS);
2583 */--changes made for eBS by asud
2584 COURSE_DATES_ARE_VALID (P_REC.PARENT_OFFERING_ID,
2585 P_REC.COURSE_START_DATE,
2586 P_REC.COURSE_END_DATE,
2587 P_REC.EVENT_STATUS,
2588 P_REC.EVENT_TYPE);
2589
2590 CHK_END_DATE( P_REC.COURSE_END_DATE ,P_REC.COURSE_END_TIME) ;
2591 --bug 3192072
2592 chk_start_date(p_rec.course_start_date, p_rec.course_start_time);
2593 --
2594 end if;
2595 /*
2596 --Bug 2431755
2597 -- Self-Paced event specific checks
2598 if P_REC.EVENT_TYPE = 'SELFPACED' then
2599 --
2600 CHECK_PRICING (P_REC.price_basis,
2601 P_REC.standard_price);
2602 --
2603 end if;
2604 --Bug 2431755
2605 */
2606 -- Scheduled event specific checks
2607 --
2608 if P_REC.EVENT_TYPE in ('SCHEDULED','SELFPACED') then
2609 -- if P_REC.EVENT_TYPE = 'SCHEDULED' then
2610 --
2611 check_price_basis(p_rec.event_id ,
2612 p_rec.price_basis,
2613 p_rec.parent_offering_id,
2614 p_rec.maximum_internal_attendees);
2615
2616
2617 chk_activity_version_id(p_rec.activity_version_id,
2618 p_rec.parent_offering_id);
2619
2620 CHECK_PRICING (P_REC.price_basis,
2621 P_REC.standard_price,p_rec.currency_code);
2622 --
2623 /*--changes made for eBS by asud
2624 ENROLLMENT_DATES_ARE_VALID (
2625 P_REC.ACTIVITY_VERSION_ID,
2626 P_REC.ENROLMENT_START_DATE,
2627 P_REC.ENROLMENT_END_DATE);
2628 */--changes made for eBS by asud
2629 ENROLLMENT_DATES_ARE_VALID (P_REC.PARENT_OFFERING_ID,
2630 P_REC.ENROLMENT_START_DATE,
2631 P_REC.ENROLMENT_END_DATE);
2632
2633 ENROLLMENT_DATES_EVENT_VALID (
2634 P_REC.ENROLMENT_START_DATE,
2635 P_REC.ENROLMENT_END_DATE,
2636 P_REC.COURSE_START_DATE,
2637 P_REC.COURSE_END_DATE);
2638 /* bug 3795299
2639 if l_course_start_date_changed or l_course_end_date_changed then
2640
2641 --ADDED by dbatra for training plan bug 3007101
2642 ota_trng_plan_comp_ss.update_tpc_evt_change(p_rec.event_id,
2643 p_rec.course_start_date,
2644 p_rec.course_end_date);
2645 end if;
2646 bug 3795299
2647 */
2648 end if;
2649 --
2650 if (P_REC.EVENT_TYPE = 'SCHEDULED') then
2651 if l_course_start_date_changed or l_course_end_date_changed then
2652
2653 session_valid(P_REC.EVENT_ID,
2654 P_REC.COURSE_START_DATE,
2655 P_REC.COURSE_END_DATE);
2656
2657 booking_deal_valid(P_REC.EVENT_ID,
2658 P_REC.COURSE_START_DATE,
2659 P_REC.COURSE_END_DATE,
2660 P_REC.EVENT_STATUS);
2661 end if;
2662 -- added for bug 3622035
2663 check_class_session_times(p_event_id => p_rec.event_id,
2664 p_course_start_date => p_rec.course_start_date,
2665 p_course_start_time => p_rec.course_start_time,
2666 p_course_end_date => p_rec.course_end_date,
2667 p_course_end_time => p_rec.course_end_time);
2668
2669 -- added for bug 3622035
2670 end if;
2671
2672 if (P_REC.EVENT_TYPE = 'SESSION') then
2673 CHECK_SESSION_WITHIN_COURSE (
2674 P_EVENT_TYPE => P_REC.EVENT_TYPE,
2675 P_PARENT_EVENT_ID => P_REC.PARENT_EVENT_ID,
2676 P_COURSE_START_DATE => P_REC.COURSE_START_DATE,
2677 P_EVENT_ID => P_REC.EVENT_ID,
2678 P_OBJECT_VERSION_NUMBER
2679 => P_REC.OBJECT_VERSION_NUMBER);
2680 --Added for Bug 3403113
2681 check_session_time(
2682 p_parent_event_id => p_rec.parent_event_id,
2683 p_session_start_date => p_rec.course_start_date,
2684 p_session_start_time => p_rec.course_start_time,
2685 p_session_end_date => p_rec.course_end_date,
2686 p_session_end_time => p_rec.course_end_time,
2687 p_event_id => p_rec.event_id,
2688 p_object_version_number => p_rec.object_version_number);
2689
2690 end if;
2691 --
2692 CHECK_PROGRAM_ENROLMENT_SPAN (
2693 P_REC.EVENT_ID,
2694 P_REC.EVENT_TYPE,
2695 P_REC.ENROLMENT_START_DATE,
2696 P_REC.ENROLMENT_END_DATE,
2697 P_REC.PARENT_EVENT_ID,
2698 P_REC.OBJECT_VERSION_NUMBER);
2699 --
2700 check_cost_vals
2701 (p_budget_currency_code => p_rec.budget_currency_code
2702 ,p_budget_cost => p_rec.budget_cost
2703 ,p_actual_cost => p_rec.actual_cost);
2704 --
2705 if l_enrolment_start_date_changed or l_enrolment_end_date_changed OR l_timezone_changed then
2706 bookings_valid(P_REC.EVENT_ID,
2707 P_REC.ENROLMENT_START_DATE,
2708 P_REC.ENROLMENT_END_DATE,
2709 P_REC.EVENT_TYPE,
2710 P_REC.TIMEZONE);
2711 end if;
2712 --
2713 if l_maximum_attendees_changed or l_maximum_int_att_changed then
2714 ota_evt_bus2.check_places(p_rec.event_id
2715 ,p_rec.maximum_attendees
2716 ,p_rec.maximum_internal_attendees);
2717
2718 --Added for mandatory enrollments
2719 ota_evt_bus2.check_mandatory_associations(p_rec.event_id
2720 ,p_rec.maximum_attendees
2721 ,p_rec.maximum_internal_attendees);
2722 end if;
2723 --
2724 if l_public_event_flag_changed then
2725 check_public_event_flag(p_rec.public_event_flag
2726 ,p_rec.event_id);
2727 end if;
2728 --
2729 if l_owner_id_changed then
2730 check_owner_id (p_rec.event_id,
2731 p_rec.owner_id,
2732 p_rec.business_group_id,
2733 p_rec.course_start_date);
2734 end if;
2735 if l_line_id_changed then
2736 check_line_id (p_rec.event_id,
2737 p_rec.line_id,
2738 p_rec.org_id);
2739 end if;
2740 /* Globalization */
2741 if l_training_center_id_changed then
2742 chk_training_center (p_rec.event_id,
2743 p_rec.training_center_id);
2744 end if;
2745
2746 if l_location_id_changed then
2747 chk_location (p_rec.event_id,
2748 p_rec.location_id,
2749 p_rec.training_center_id,
2750 p_rec.course_end_date);
2751 end if;
2752 /*Enhancement 1823602*/
2753 IF p_rec.line_id IS NOT NULL THEN
2754 /* For Bug 4492519 */
2755 IF p_rec.event_id is null or l_course_end_date_changed then
2756 ota_utility.get_commitment_detail(p_rec.line_id,
2757 l_commitment_number,
2758 l_commitment_id,
2759 l_commitment_start_date,
2760 l_commitment_end_date);
2761 IF l_commitment_end_date IS NOT NULL
2762 AND p_rec.course_end_date > l_commitment_end_date THEN
2763 FND_MESSAGE.SET_NAME ('OTA', 'OTA_OM_COMMITMENT');
2764 FND_MESSAGE.SET_TOKEN ('COMMITMENT_NUMBER', l_commitment_number);
2765 --FND_MESSAGE.SET_TOKEN ('COMMITMENT_END_DATE', fnd_date.date_to_chardate(l_commitment_end_date));
2766 FND_MESSAGE.SET_TOKEN ('COMMITMENT_END_DATE', fnd_date.date_to_chardate((l_commitment_end_date),2));
2767 FND_MESSAGE.RAISE_ERROR;
2768 END IF;
2769 END IF;
2770 END IF;
2771
2772 /*Enhancement 1823602*/
2773
2774 /* Ilearing */
2775 /*
2776 if l_offering_id_changed then
2777 check_unique_offering_id(p_rec.event_id,
2778 p_rec.offering_id);
2779 end if;
2780 */
2781
2782 IF p_rec.event_type IN ('SCHEDULED', 'SESSION', 'SELFPACED') THEN
2783 check_timezone(p_rec.timezone);
2784 END IF;
2785
2786
2787 HR_UTILITY.SET_LOCATION (' Leaving:' || W_PROC, 10);
2788 --
2789 end VALIDITY_CHECKS;
2790 --
2791 -- ----------------------------------------------------------------------------
2792 -- |---------------------------< check_child_entities >-----------------------|
2793 -- ----------------------------------------------------------------------------
2794 Procedure check_child_entities (p_event_id in number) is
2795 --
2796 -- cursor to check if sessions exist for the event
2797 --
2798 Cursor c_session_details is
2799 select 'X'
2800 from ota_events
2801 where parent_event_id = p_event_id;
2802 --
2803 -- cursor to check if resources exist for the event
2804 --
2805 Cursor c_resource_details is
2806 select 'X'
2807 from ota_resource_bookings
2808 where event_id = p_event_id;
2809 --
2810 -- cursor to check if program membership exist for the event
2811 --
2812 Cursor c_program_membership_details is
2813 select 'X'
2814 from ota_program_memberships
2815 where program_event_id = p_event_id;
2816 --
2817 -- cursor to check if event association exist for the event
2818 --
2819 Cursor c_event_associations_details is
2820 select 'X'
2821 from ota_event_associations
2822 where event_id = p_event_id;
2823 --
2824 -- cursor to check if delegate bookings exist for the event
2825 --
2826 Cursor c_delegate_bookings_details is
2827 select 'X'
2828 from ota_delegate_bookings
2829 where event_id = p_event_id;
2830 -- 6683076
2831 -- cursor to check if evaluation exists for the event
2832 --
2833 Cursor c_evaluation_details is
2834 select 'X'
2835 from ota_evaluations
2836 where object_id = p_event_id
2837 and object_type = 'E';
2838 --
2839 -- cursor to check if booking deals exist for the event
2840 --
2841 Cursor c_booking_deals_details is
2842 select 'X'
2843 from ota_booking_deals
2844 where event_id = p_event_id;
2845 --
2846 -- cursor to check if cat inclusions exist for the event
2847 --
2848 Cursor c_act_cat_inclusions_details is
2849 select 'X'
2850 from ota_act_cat_inclusions
2851 where event_id = p_event_id;
2852 --
2853 --
2854 -- cursor to check if attempts exist for the event
2855 --
2856 Cursor c_attempts_details is
2857 select 'X'
2858 from ota_attempts
2859 where event_id = p_event_id;
2860 --
2861 -- cursor to check if the event is referenced in training plan costs
2862 --
2863 Cursor c_get_tpc_rows is
2864 select 'Y'
2865 from OTA_TRAINING_PLAN_COSTS
2866 where event_id = p_event_id;
2867 --
2868 -- cursor to check if the event is referenced in per budget elements
2869 --
2870 Cursor c_get_pbe_rows is
2871 select 'Y'
2872 from per_budget_elements
2873 where event_id = p_event_id;
2874
2875 /*For bug 4407518 */
2876
2877 Cursor c_conference_details is
2878 select 'X'
2879 from OTA_CONFERENCES
2880 where event_id = p_event_id;
2881 /* for bug 4407518 */
2882 --
2883 l_dyn_curs integer;
2884 l_dyn_rows integer;
2885 --
2886 --
2887 l_proc varchar2(72) := g_package||'check_child_entities';
2888 l_dummy varchar2(1);
2889 --
2890 Begin
2891 hr_utility.set_location('Entering:'||l_proc, 5);
2892 --
2893 -- Determine if the event has training_plan_cost records
2894 open c_get_tpc_rows;
2895 fetch c_get_tpc_rows into l_dummy;
2896 if c_get_tpc_rows%found then
2897 close c_get_tpc_rows;
2898 fnd_message.set_name ('OTA', 'OTA_13823_EVT_NO_DEL_TPC_EXIST');
2899 fnd_message.raise_error;
2900 else
2901 close c_get_tpc_rows;
2902 -- Determine if the event has per_budget_element records
2903 open c_get_pbe_rows;
2904 fetch c_get_pbe_rows into l_dummy;
2905 if c_get_pbe_rows%found then
2906 close c_get_pbe_rows;
2907 fnd_message.set_name ('OTA', 'OTA_13824_EVT_NO_DEL_BGE_EXIST');
2908 fnd_message.raise_error;
2909 else
2910 close c_get_pbe_rows;
2911 end if;
2912 end if;
2913 --
2914 -- Raise error if sessions exists.
2915 --
2916 open c_session_details;
2917 fetch c_session_details into l_dummy;
2918 if c_session_details%found then
2919 --
2920 close c_session_details;
2921 --
2922 fnd_message.set_name ('OTA', 'OTA_13677_EVT_SESSION_EXISTS');
2923 fnd_message.raise_error;
2924 --
2925 end if;
2926 --
2927 close c_session_details;
2928 --
2929 -- Raise error if resoure bookings exists.
2930 --
2931 open c_resource_details;
2932 fetch c_resource_details into l_dummy;
2933 if c_resource_details%found then
2934 --
2935 close c_resource_details;
2936 --
2937 fnd_message.set_name ('OTA', 'OTA_13678_EVT_RES_EXISTS');
2938 fnd_message.raise_error;
2939 --
2940 end if;
2941 --
2942 close c_resource_details;
2943 --
2944 -- Raise error if program membership exists.
2945 --
2946 open c_program_membership_details;
2947 fetch c_program_membership_details into l_dummy;
2948 if c_program_membership_details%found then
2949 --
2950 close c_program_membership_details;
2951 --
2952 fnd_message.set_name ('OTA', 'OTA_13681_EVT_PMM_EXISTS');
2953 fnd_message.raise_error;
2954 --
2955 end if;
2956 --
2957 close c_program_membership_details;
2958 --
2959 -- Raise error if event associations exists.
2960 --
2961 open c_event_associations_details;
2962 fetch c_event_associations_details into l_dummy;
2963 if c_event_associations_details%found then
2964 --
2965 close c_event_associations_details;
2966 --
2967 fnd_message.set_name ('OTA', 'OTA_13683_EVT_TEA_EXISTS');
2968 fnd_message.raise_error;
2969 --
2970 end if;
2971 --
2972 close c_event_associations_details;
2973 --
2974 -- Raise error if delegate bookings exists.
2975 --
2976 open c_delegate_bookings_details;
2977 fetch c_delegate_bookings_details into l_dummy;
2978 if c_delegate_bookings_details%found then
2979 --
2980 close c_delegate_bookings_details;
2981 --
2982 fnd_message.set_name ('OTA', 'OTA_13679_EVT_TDB_EXISTS');
2983 fnd_message.raise_error;
2984 --
2985 end if;
2986 --
2987 close c_delegate_bookings_details;
2988 -- 6683076
2989 -- Raise error if evaluation exists.
2990 --
2991 open c_evaluation_details;
2992 fetch c_evaluation_details into l_dummy;
2993 if c_evaluation_details%found then
2994 --
2995 close c_evaluation_details;
2996 --
2997 fnd_message.set_name ('OTA', 'OTA_467095_EVT_EVAL_EXISTS');
2998 fnd_message.raise_error;
2999 --
3000 end if;
3001 --
3002 close c_evaluation_details;
3003 --
3004 -- Raise error if booking deals exists.
3005 --
3006 open c_booking_deals_details;
3007 fetch c_booking_deals_details into l_dummy;
3008 if c_booking_deals_details%found then
3009 --
3010 close c_booking_deals_details;
3011 --
3012 fnd_message.set_name ('OTA', 'OTA_13680_EVT_TBD_EXISTS');
3013 fnd_message.raise_error;
3014 --
3015 end if;
3016 --
3017 close c_booking_deals_details;
3018 --
3019 -- Raise error if activity category inclusions exists.
3020 --
3021 open c_act_cat_inclusions_details;
3022 fetch c_act_cat_inclusions_details into l_dummy;
3023 if c_act_cat_inclusions_details%found then
3024 --
3025 close c_act_cat_inclusions_details;
3026 --
3027 fnd_message.set_name ('OTA', 'OTA_13682_EVT_CAT_EXISTS');
3028 fnd_message.raise_error;
3029 --
3030 end if;
3031 --
3032 close c_act_cat_inclusions_details;
3033 --
3034 -- Raise error if activity category inclusions exists.
3035 --
3036 open c_attempts_details;
3037 fetch c_attempts_details into l_dummy;
3038 if c_attempts_details%found then
3039 --
3040 close c_attempts_details;
3041 --
3042 fnd_message.set_name ('OTA', 'OTA_443538_EVT_ATT_EXISTS');
3043 fnd_message.raise_error;
3044 --
3045 end if;
3046 --
3047 close c_attempts_details;
3048
3049 /*for bug 4407518 */
3050 open c_conference_details;
3051 fetch c_conference_details into l_dummy;
3052 if c_conference_details%found then
3053 close c_conference_details;
3054 fnd_message.set_name('OTA', 'OTA_443916_EVT_CFR_EXISTS');
3055 fnd_message.raise_error;
3056 end if;
3057 close c_conference_details;
3058
3059
3060 hr_utility.set_location(' Leaving:'||l_proc, 10);
3061 EXCEPTION
3062 WHEN app_exception.application_exception THEN
3063 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.EVENT_ID') THEN
3064 hr_utility.set_location(' Leaving:'||l_proc, 70);
3065 RAISE;
3066 END IF;
3067 hr_utility.set_location(' Leaving:'||l_proc, 80);
3068 End check_child_entities;
3069 --
3070 --
3071 -- ----------------------------------------------------------------------------
3072 -- |---------------------------< check_for_st_finance_lines >-----------------|
3073 -- ----------------------------------------------------------------------------
3074 --
3075 -- This function checks to see if any 'ST' succesful Transferred finance Lines
3076 -- which have not been cancelled exists for any booking within the Event.
3077 --
3078 function check_for_st_finance_lines (
3079 p_event_id number) return boolean is
3080 --
3081 W_PROC constant varchar2(72) := G_PACKAGE||'check_for_st_finance_lines';
3082 --
3083 l_st_finance_lines boolean;
3084 --
3085 cursor csr_st_finance_lines is
3086 select 1
3087 from ota_finance_lines tfl,
3088 ota_delegate_bookings tdb
3089 where tdb.event_id = p_event_id
3090 and tdb.booking_id = tfl.booking_id
3091 and tfl.transfer_status = 'ST'
3092 and tfl.cancelled_flag = 'N';
3093 --
3094 begin
3095 --
3096 HR_UTILITY.SET_LOCATION ('Entering:' || W_PROC, 5);
3097 --
3098 open csr_st_finance_lines;
3099 fetch csr_st_finance_lines into g_dummy;
3100 l_st_finance_lines := csr_st_finance_lines%found;
3101 close csr_st_finance_lines;
3102 --
3103 HR_UTILITY.SET_LOCATION (W_PROC,10);
3104 --
3105 return l_st_finance_lines;
3106 --
3107 end check_for_st_finance_lines;
3108 --
3109 --
3110 -- ----------------------------------------------------------------------------
3111 -- |---------------------------< check_owner_id >-----------------------------|
3112 -- ----------------------------------------------------------------------------
3113 --
3114 -- This function checks to see if any the owner_id exists in
3115 -- per_people_f table
3116 --
3117 --
3118 Procedure check_owner_id (p_event_id in number,
3119 p_owner_id in number,
3120 p_business_group_id in number,
3121 p_course_start_date in date)
3122 Is
3123 l_proc varchar2(72) := g_package||'check_owner_id';
3124
3125 CURSOR c_people
3126 IS
3127 SELECT null
3128 FROM Per_all_people_f per
3129 WHERE per.person_id = p_owner_id and
3130 per.business_group_id = p_business_group_id and
3131 NVL(p_course_start_date,TRUNC(SYSDATE)) between
3132 effective_start_date and effective_end_date;
3133
3134 CURSOR c_people_cross
3135 IS
3136 SELECT null
3137 FROM Per_all_people_f per
3138 WHERE per.person_id = p_owner_id and
3139 NVL(p_course_start_date,TRUNC(SYSDATE)) between
3140 effective_start_date and effective_end_date;
3141
3142
3143 l_exist varchar2(1);
3144 --l_cross_business_group varchar2(1):= FND_PROFILE.VALUE('HR_CROSS_BUSINESS_GROUP');
3145 l_single_business_group_id number := FND_PROFILE.VALUE('OTA_HR_GLOBAL_BUSINESS_GROUP_ID');
3146
3147
3148 Begin
3149 --
3150 hr_utility.set_location('Entering:'||l_proc, 5);
3151 --
3152
3153 if (((p_event_id is not null) and
3154 nvl(ota_evt_shd.g_old_rec.owner_id,hr_api.g_number) <>
3155 nvl(p_owner_id,hr_api.g_number))
3156 or (p_event_id is null)) then
3157
3158 IF p_owner_id is not null then
3159
3160 If l_single_business_group_id is not null then
3161 hr_utility.set_location('Entering:'||l_proc, 10);
3162 OPEN c_people_cross;
3163 FETCH c_people_cross into l_exist;
3164 if c_people_cross%notfound then
3165 close c_people_cross;
3166 fnd_message.set_name('OTA','OTA_13887_EVT_OWNER_INVALID');
3167 fnd_message.raise_error;
3168 end if;
3169 close c_people_cross;
3170 else
3171 hr_utility.set_location('Entering:'||l_proc, 20);
3172 OPEN c_people;
3173 FETCH c_people into l_exist;
3174 if c_people%notfound then
3175 close c_people;
3176 fnd_message.set_name('OTA','OTA_13887_EVT_OWNER_INVALID');
3177 fnd_message.raise_error;
3178 end if;
3179 close c_people;
3180 end if;
3181 hr_utility.set_location('Leaving:'||l_proc, 40);
3182 END IF;
3183 End if;
3184 EXCEPTION
3185 WHEN app_exception.application_exception THEN
3186 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.OWNER_ID') THEN
3187 hr_utility.set_location(' Leaving:'||l_proc, 40);
3188 RAISE;
3189 END IF;
3190 hr_utility.set_location(' Leaving:'||l_proc, 50);
3191 end check_owner_id;
3192
3193 -- ----------------------------------------------------------------------------
3194 -- |---------------------------< check_line_id >---------------------------|
3195 -- ----------------------------------------------------------------------------
3196 Procedure check_line_id
3197 (p_event_id in number
3198 ,p_line_id in number
3199 ,p_org_id in number) is
3200
3201 --
3202 l_proc varchar2(72) := g_package||'chk_line_id';
3203 l_exists varchar2(1);
3204
3205 --
3206 -- cursor to check if line is exist in OE_ORDER_LINES .
3207 --
3208 cursor csr_order_line is
3209 select null
3210 from oe_order_lines_all
3211 where line_id = p_line_id;
3212
3213 Begin
3214 hr_utility.set_location('Entering:'||l_proc, 5);
3215
3216 if (((p_event_id is not null) and
3217 nvl(ota_evt_shd.g_old_rec.line_id,hr_api.g_number) <>
3218 nvl(p_line_id,hr_api.g_number))
3219 or (p_event_id is null)) then
3220 --
3221 hr_utility.set_location('Entering:'||l_proc, 10);
3222 if (p_line_id is not null) then
3223 hr_utility.set_location('Entering:'||l_proc, 15);
3224 open csr_order_line;
3225 fetch csr_order_line into l_exists;
3226 if csr_order_line%notfound then
3227 close csr_order_line;
3228 fnd_message.set_name('OTA','OTA_13888_TDB_LINE_INVALID');
3229 fnd_message.raise_error;
3230 end if;
3231 close csr_order_line;
3232 hr_utility.set_location('Entering:'||l_proc, 20);
3233 end if;
3234 end if;
3235 hr_utility.set_location('Entering:'||l_proc, 30);
3236 EXCEPTION
3237 WHEN app_exception.application_exception THEN
3238 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.LINE_ID') THEN
3239 hr_utility.set_location(' Leaving:'||l_proc, 40);
3240 RAISE;
3241 END IF;
3242 hr_utility.set_location(' Leaving:'||l_proc, 50);
3243 end check_line_id;
3244
3245 -- ----------------------------------------------------------------------------
3246 -- |---------------------------< chk_status_changed >----------------------|
3247 -- ----------------------------------------------------------------------------
3248 -- This procedure will check whether the status is changed. this procedure is
3249 -- called by post_update procedure and will be only used by OM integration.
3250 -- The purpose of this procedure is to cancel an order line, Create RMA and
3251 -- To notify the Workflow to continue.
3252
3253 Procedure chk_status_changed
3254 (p_line_id in number
3255 ,p_event_status in varchar2
3256 ,p_event_id in number
3257 ,p_org_id in number
3258 ,p_owner_id in number
3259 ,p_event_title in varchar2
3260 ) is
3261
3262 l_proc varchar2(72) := g_package||'chk_status_changed';
3263
3264 l_event_status_changed boolean :=
3265 ota_general.value_changed (ota_evt_shd.g_old_rec.event_status,
3266 p_event_status);
3267 l_status_type ota_booking_status_types.type%type;
3268 l_old_status_type ota_booking_status_types.type%type;
3269 l_invoice_rule varchar2(80);
3270 l_exist varchar2(1);
3271 l_dynamicSqlString VARCHAR2(2000);
3272 l_ins_status VARCHAR2(1);
3273 l_industry VARCHAR2(1);
3274 l_err_num VARCHAR2(30) := '';
3275 l_err_msg VARCHAR2(1000) := '';
3276
3277 --
3278 Begin
3279 hr_utility.set_location('Entering:'||l_proc, 5);
3280 --
3281 IF p_line_id is not null THEN
3282 IF l_event_status_changed THEN
3283 IF p_event_status = 'A' THEN
3284
3285 hr_utility.set_location('Entering:'||l_proc, 10);
3286
3287 ota_utility.check_invoice(
3288 p_line_id => p_line_id,
3289 p_org_id => p_org_id,
3290 p_exist => l_exist);
3291 IF fnd_installation.get(660, 660, l_ins_status, l_industry) THEN
3292 BEGIN
3293 IF l_exist = 'Y' THEN
3294 Begin
3295 hr_utility.set_location('Entering:'||l_proc, 15);
3296
3297 /* l_dynamicSqlString := '
3298 ota_om_upd_api.create_rma(
3299 :p_Line_id,
3300 :p_org_id);';
3301 EXECUTE IMMEDIATE l_dynamicSqlString
3302 USING IN p_line_id,
3303 IN p_org_id;*/
3304 ota_om_upd_api.create_rma(p_line_id,p_org_id);
3305 exception when others then
3306 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3307 hr_utility.set_message_token('PROCEDURE', l_proc);
3308 hr_utility.set_message_token('STEP','15');
3309 hr_utility.raise_error;
3310
3311 End;
3312 ELSE
3313 Begin
3314 hr_utility.set_location('Entering:'||l_proc, 20);
3315 /*l_dynamicSqlString := '
3316 ota_om_upd_api.cancel_order(
3317 p_Line_id,
3318 p_org_id);' ;
3319 EXECUTE IMMEDIATE l_dynamicSqlString
3320 USING IN p_line_id,
3321 IN p_org_id;*/
3322 ota_om_upd_api.cancel_order(p_line_id,p_org_id);
3323 exception when others then
3324 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3325 hr_utility.set_message_token('PROCEDURE', l_proc);
3326 hr_utility.set_message_token('STEP','20');
3327 hr_utility.raise_error;
3328
3329 End;
3330
3331 END IF;
3332
3333 END;
3334
3335
3336 ota_initialization_wf.INITIALIZE_CANCEL_EVENT(
3337 p_Line_id => p_Line_id,
3338 p_org_id => p_org_id,
3339 p_Status => null,
3340 p_Event_id => p_event_id,
3341 p_owner_id => p_owner_id,
3342 p_itemtype => 'OTWF',
3343 p_event_title => p_event_title);
3344 END IF;
3345
3346
3347 END IF;
3348
3349 END IF;
3350 END IF;
3351
3352
3353 hr_utility.set_location('Leaving:'||l_proc, 10);
3354 /*EXCEPTION WHEN OTHERS
3355 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;*/
3356
3357 end chk_status_changed;
3358
3359 -- ----------------------------------------------------------------------------
3360 -- |---------------------------< chk_Order_line_exist >----------------------|
3361 -- ----------------------------------------------------------------------------
3362 -- Description : This procedure will be called by Delete_validate procedure. This
3363 -- procedure will check whether order line exist or not.
3364
3365 --
3366 Procedure chk_Order_line_exist
3367 (p_line_id in number
3368 ,p_org_id in number) is
3369
3370 --
3371 l_proc varchar2(72) := g_package||'chk_order_line_exist';
3372
3373
3374 Begin
3375 hr_utility.set_location('Entering:'||l_proc, 5);
3376
3377 if p_line_id is not null then
3378 fnd_message.set_name('OTA','OTA_13896_EVT_ORDER_LINE_EXIST');
3379 fnd_message.raise_error;
3380 hr_utility.set_location('Entering:'||l_proc, 20);
3381
3382 end if;
3383 hr_utility.set_location('Leaving:'||l_proc, 30);
3384 EXCEPTION
3385 WHEN app_exception.application_exception THEN
3386 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.LINE_ID') THEN
3387 hr_utility.set_location(' Leaving:'||l_proc, 40);
3388 RAISE;
3389 END IF;
3390 hr_utility.set_location(' Leaving:'||l_proc, 50);
3391 end chk_order_line_exist;
3392
3393
3394 -- ----------------------------------------------------------------------------
3395 -- |-----------------------< chk_Training_center >---------------------------|
3396 -- ----------------------------------------------------------------------------
3397 -- Description : This procedure will be called by Insert_validate procedure and
3398 -- Update_validaate procedure. This
3399 -- procedure will check whether Training center exist or not.
3400
3401 --
3402 Procedure chk_Training_center
3403 (p_event_id in number,
3404 p_training_center_id in number)
3405 IS
3406
3407
3408 --
3409 l_proc varchar2(72) := g_package||'chk_training_center';
3410 l_exists varchar2(1);
3411
3412 Cursor c_training_center
3413 IS
3414 Select null
3415 From HR_ALL_ORGANIZATION_UNITS
3416 Where organization_id = p_training_center_id;
3417
3418 Begin
3419 hr_utility.set_location('Entering:'||l_proc, 5);
3420 if (((p_event_id is not null) and
3421 nvl(ota_evt_shd.g_old_rec.training_center_id,hr_api.g_number) <>
3422 nvl(p_training_center_id,hr_api.g_number))
3423 or (p_event_id is null)) then
3424 --
3425 hr_utility.set_location('Entering:'||l_proc, 10);
3426 if (p_training_center_id is not null) then
3427 hr_utility.set_location('Entering:'||l_proc, 15);
3428 open c_training_center;
3429 fetch c_training_center into l_exists;
3430 if c_training_center%notfound then
3431 close c_training_center;
3432 fnd_message.set_name('OTA','OTA_13907_TSR_TRNCTR_NOT_EXIST');
3433 fnd_message.raise_error;
3434 end if;
3435 close c_training_center;
3436 hr_utility.set_location('Entering:'||l_proc, 20);
3437 end if;
3438 end if;
3439 hr_utility.set_location('Entering:'||l_proc, 30);
3440 EXCEPTION
3441 WHEN app_exception.application_exception THEN
3442 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.TRAINING_CENTER_ID') THEN
3443 hr_utility.set_location(' Leaving:'||l_proc, 40);
3444 RAISE;
3445 END IF;
3446 hr_utility.set_location(' Leaving:'||l_proc, 50);
3447 end;
3448
3449 -- ----------------------------------------------------------------------------
3450 -- |-----------------------------< chk_location >---------------------------|
3451 -- ----------------------------------------------------------------------------
3452 -- Description : This procedure will be called by Insert_validate procedure and
3453 -- Update_validaate procedure. This
3454 -- procedure will check whether Location exist or not.
3455
3456 --
3457 Procedure Chk_location
3458 (p_event_id in number,
3459 p_location_id in number,
3460 p_training_center_id in number,
3461 p_course_end_date in date)
3462 IS
3463
3464
3465 --
3466 l_proc varchar2(72) := g_package||'chk_location';
3467 l_exists varchar2(1);
3468 Cursor c_location
3469 IS
3470 Select null
3471 From HR_LOCATIONS_ALL loc
3472 Where loc.location_id = p_location_id
3473 and nvl(loc.inactive_date,to_date('31-12-4712','DD-MM-YYYY')) >= nvl(p_course_end_date,sysdate);
3474
3475 Begin
3476 hr_utility.set_location('Entering:'||l_proc, 5);
3477
3478 if (((p_event_id is not null) and
3479 nvl(ota_evt_shd.g_old_rec.location_id,hr_api.g_number) <>
3480 nvl(p_location_id,hr_api.g_number))
3481 or (p_event_id is null)) then
3482 --
3483 hr_utility.set_location('Entering:'||l_proc, 10);
3484 if (p_location_id is not null) then
3485 hr_utility.set_location('Entering:'||l_proc, 15);
3486 open c_location;
3487 fetch c_location into l_exists;
3488 if c_location%notfound then
3489 close c_location;
3490 fnd_message.set_name('OTA','OTA_13908_TSR_LOC_NOT_EXIST');
3491 fnd_message.raise_error;
3492 end if;
3493 close c_location;
3494 hr_utility.set_location('Entering:'||l_proc, 20);
3495 end if;
3496 end if;
3497 hr_utility.set_location('Entering:'||l_proc, 30);
3498 EXCEPTION
3499 WHEN app_exception.application_exception THEN
3500 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.LOCATION_ID') THEN
3501 hr_utility.set_location(' Leaving:'||l_proc, 40);
3502 RAISE;
3503 END IF;
3504 hr_utility.set_location(' Leaving:'||l_proc, 50);
3505 end;
3506
3507 --
3508 -- ----------------------------------------------------------------------------
3509 -- |-----------------------------< check_unique_offering_id>------------------------|
3510 -- ----------------------------------------------------------------------------
3511 --
3512 -- PUBLIC
3513 -- Description:
3514 -- Check uniqueness of offering_id
3515 --
3516 --
3517 --
3518 --
3519 Procedure check_unique_offering_id
3520 (
3521 p_event_id in number,
3522 p_offering_id in number)
3523
3524 IS
3525
3526 l_proc varchar2(72) := g_package||'check_unique_offering_id';
3527 l_exists varchar2(1);
3528
3529 cursor csr_offering is
3530 select null
3531 from ota_events
3532 where offering_id = p_offering_id;
3533
3534 Begin
3535
3536 hr_utility.set_location('Entering:'||l_proc, 5);
3537
3538 if (((p_event_id is not null) and
3539 nvl(ota_evt_shd.g_old_rec.offering_id,hr_api.g_number) <>
3540 nvl(p_offering_id,hr_api.g_number))
3541 or (p_event_id is null)) then
3542 --
3543 hr_utility.set_location('Entering:'||l_proc, 10);
3544 if (p_offering_id is not null) then
3545 hr_utility.set_location('Entering:'||l_proc, 15);
3546 open csr_offering;
3547 fetch csr_offering into l_exists;
3548 if csr_offering%found then
3549 ota_evt_shd.constraint_error(p_constraint_name =>'OTA_EVENTS_UK4');
3550 end if;
3551 close csr_offering;
3552 hr_utility.set_location('Leaving:'||l_proc, 20);
3553 end if;
3554 end if;
3555 hr_utility.set_location('Leaving:'||l_proc, 30);
3556 EXCEPTION
3557 WHEN app_exception.application_exception THEN
3558 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.OFFERING_ID') THEN
3559 hr_utility.set_location(' Leaving:'||l_proc, 70);
3560 RAISE;
3561 END IF;
3562 hr_utility.set_location(' Leaving:'||l_proc, 80);
3563 End;
3564
3565 --
3566 -- ----------------------------------------------------------------------------
3567 -- |-----------------------------< chk_activity_version_id>--------------------|
3568 -- ----------------------------------------------------------------------------
3569 --
3570 -- PUBLIC
3571 -- Description:
3572 -- Check if parent_offering_id belongs to the activity_version_id
3573 --
3574 --
3575 --
3576 --
3577 Procedure chk_activity_version_id
3578 (p_activity_version_id in number,
3579 p_parent_offering_id in number)
3580
3581 IS
3582
3583 l_proc varchar2(72) := g_package||'chk_activity_version_id';
3584 l_exists varchar2(1);
3585
3586 CURSOR csr_offering IS
3587 SELECT null
3588 FROM ota_offerings off,
3589 ota_activity_versions act
3590 WHERE off.offering_id = p_parent_offering_id
3591 AND off.activity_version_id = act.activity_version_id
3592 AND act.activity_version_id = p_activity_version_id;
3593
3594 Begin
3595
3596 hr_utility.set_location('Entering:'||l_proc, 5);
3597 open csr_offering;
3598 fetch csr_offering into l_exists;
3599 if csr_offering%notfound then
3600 close csr_offering;
3601 fnd_message.set_name('OTA','OTA_443321_EVT_OFF_INVALID_ACT');
3602 fnd_message.raise_error;
3603 end if;
3604 close csr_offering;
3605 hr_utility.set_location('Leaving:'||l_proc, 20);
3606 EXCEPTION
3607 WHEN app_exception.application_exception THEN
3608 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.ACTIVITY_VERSION_ID') THEN
3609 hr_utility.set_location(' Leaving:'||l_proc, 70);
3610 RAISE;
3611 END IF;
3612 hr_utility.set_location(' Leaving:'||l_proc, 80);
3613 End chk_activity_version_id;
3614
3615 --
3616 -- ----------------------------------------------------------------------------
3617 -- |----------------------< chk_secure_event_flag >--------------------------|
3618 -- ----------------------------------------------------------------------------
3619 --
3620 -- PUBLIC
3621 -- Description:
3622 -- Check if the secure class is being modified by the user who belongs to
3623 -- the sponsor org. if not, throw an error.
3624 --
3625 --
3626 --
3627 Procedure chk_secure_event_flag (p_organization_id in number)
3628 IS
3629
3630 l_proc varchar2(72) := g_package||'chk_secure_event_flag';
3631 l_username fnd_user.user_name%TYPE;
3632 l_user fnd_user.user_name%TYPE;
3633 l_condition boolean;
3634
3635 CURSOR csr_org IS
3636 SELECT user_name
3637 FROM fnd_user f,
3638 per_all_assignments_f p
3639 WHERE p.organization_id = p_organization_id
3640 AND f.employee_id = p.person_id
3641 AND trunc(sysdate) BETWEEN p.effective_start_date AND p.effective_end_date
3642 AND f.user_id = to_number(fnd_profile.value('USER_ID'));
3643
3644 BEGIN
3645 hr_utility.set_location('Entering:'||l_proc, 5);
3646
3647 OPEN csr_org;
3648 FETCH csr_org INTO l_username;
3649 if csr_org%notfound then
3650 fnd_message.set_name('OTA', 'OTA_EVT_SECURE');
3651 fnd_message.raise_error;
3652 end if;
3653 close csr_org;
3654
3655
3656
3657 /* OPEN csr_org;
3658 FETCH csr_org INTO l_username;
3659 l_user := fnd_profile.value('USERNAME');
3660 l_condition := nvl(l_user, 'UNSET1') = nvl(l_username, 'UNSET2');
3661 IF NOT l_condition THEN
3662 fnd_message.set_name('OTA', 'OTA_EVT_SECURE');
3663 fnd_message.raise_error;
3664 END IF;
3665 CLOSE csr_org;*/
3666 hr_utility.set_location('Leaving:'||l_proc, 20);
3667
3668 EXCEPTION
3669 WHEN app_exception.application_exception THEN
3670 IF hr_multi_message.exception_add(p_associated_column1 => 'OTA_EVENTS.SECURE_EVENT_FLAG') THEN
3671 hr_utility.set_location('Leaving:'||l_proc, 40);
3672 RAISE;
3673 END IF;
3674 hr_utility.set_location('Leaving:'||l_proc, 50);
3675 END chk_secure_event_flag;
3676 --
3677 -- ----------------------------------------------------------------------------
3678 -- |---------------------------< insert_validate >----------------------------|
3679 -- ----------------------------------------------------------------------------
3680 -- VT 05/06/97 #488173
3681 Procedure insert_validate(p_rec in out nocopy ota_evt_shd.g_rec_type) is
3682 --
3683 l_proc varchar2(72) := g_package||'insert_validate';
3684 --
3685 Begin
3686 --
3687 hr_utility.set_location('Entering:'||l_proc, 5);
3688 --modified for eBS by asud
3689 /*
3690 -- Call all supporting business operations
3691 --
3692 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
3693 --
3694 VALIDITY_CHECKS (
3695 P_REC => P_REC);
3696 --
3697 hr_utility.set_location(' Leaving:'||l_proc, 10);
3698 --*/
3699 --modified for eBS by asud
3700 -- Call all supporting business operations
3701 -- Validate Important Attributes
3702 hr_api.validate_bus_grp_id(p_business_group_id => p_rec.business_group_id,
3703 p_associated_column1 => ota_evt_shd.g_tab_nam||'.BUSINESS_GROUP_ID'); -- Validate Bus Grp
3704 --
3705 hr_multi_message.end_validation_set;
3706
3707 VALIDITY_CHECKS (
3708 P_REC => P_REC);
3709
3710 -- bug 4348022
3711 IF p_rec.secure_event_flag = 'Y' THEN
3712 chk_secure_event_flag(p_organization_id => p_rec.organization_id);
3713 END IF;
3714 -- bug 4348022
3715
3716 ota_evt_bus.chk_ddf(p_rec);
3717 --
3718 hr_utility.set_location(' Leaving:'||l_proc, 10);
3719
3720 End insert_validate;
3721 --
3722 -- ----------------------------------------------------------------------------
3723 -- |---------------------------< update_validate >----------------------------|
3724 -- ----------------------------------------------------------------------------
3725 -- VT 05/06/97 #488173
3726 Procedure update_validate(p_rec in out nocopy ota_evt_shd.g_rec_type) is
3727 --
3728 l_proc varchar2(72) := g_package||'update_validate';
3729 l_secure_event_flag_changed boolean
3730 := ota_general.value_changed(ota_evt_shd.g_old_rec.secure_event_flag, p_rec.secure_event_flag);
3731 l_organization_id_changed boolean
3732 := ota_general.value_changed(ota_evt_shd.g_old_rec.organization_id, p_rec.organization_id);
3733 --
3734 Begin
3735 --
3736 hr_utility.set_location('Entering:'||l_proc, 5);
3737 --
3738 -- Call all supporting business operations
3739 --
3740 /*
3741 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
3742 --
3743 VALIDITY_CHECKS (
3744 P_REC => P_REC);
3745 --
3746 hr_utility.set_location(' Leaving:'||l_proc, 10);
3747 --
3748 */
3749 -- Validate Important Attributes
3750 hr_api.validate_bus_grp_id(p_business_group_id => p_rec.business_group_id,
3751 p_associated_column1 => ota_evt_shd.g_tab_nam||'.BUSINESS_GROUP_ID'); -- Validate Bus Grp
3752 --
3753 hr_multi_message.end_validation_set;
3754
3755 VALIDITY_CHECKS (
3756 P_REC => P_REC);
3757 --
3758 -- bug 4348022
3759 IF p_rec.secure_event_flag = 'Y' THEN
3760 IF l_secure_event_flag_changed THEN
3761 chk_secure_event_flag(p_organization_id => p_rec.organization_id);
3762 ELSE -- secure event flag not changed
3763 IF l_organization_id_changed THEN
3764 chk_secure_event_flag(p_organization_id => ota_evt_shd.g_old_rec.organization_id);
3765 chk_secure_event_flag(p_organization_id => p_rec.organization_id);
3766 ELSE
3767 chk_secure_event_flag(p_organization_id => p_rec.organization_id);
3768 END IF;
3769 END IF;
3770 ELSE
3771 IF l_secure_event_flag_changed THEN
3772 chk_secure_event_flag(p_organization_id => ota_evt_shd.g_old_rec.organization_id);
3773 END IF;
3774 END IF;
3775 -- bug 4348022
3776
3777 ota_evt_bus.chk_ddf(p_rec);
3778 --
3779 hr_utility.set_location(' Leaving:'||l_proc, 10);
3780 --
3781
3782 End update_validate;
3783 --
3784 -- ----------------------------------------------------------------------------
3785 -- |---------------------------< delete_validate >----------------------------|
3786 -- ----------------------------------------------------------------------------
3787 Procedure delete_validate(p_rec in ota_evt_shd.g_rec_type) is
3788 --
3789 l_proc varchar2(72) := g_package||'delete_validate';
3790 --
3791 Begin
3792 hr_utility.set_location('Entering:'||l_proc, 5);
3793 --
3794 -- Call all supporting business operations
3795 check_child_entities (p_event_id => p_rec.event_id);
3796 chk_Order_line_exist(ota_evt_shd.g_old_rec.line_id
3797 ,ota_evt_shd.g_old_rec.org_id) ;
3798 --
3799 IF ota_evt_shd.g_old_rec.secure_event_flag = 'Y' THEN
3800 chk_secure_event_flag(p_organization_id => ota_evt_shd.g_old_rec.organization_id);
3801 END IF;
3802 --
3803 hr_utility.set_location(' Leaving:'||l_proc, 10);
3804 End delete_validate;
3805 --
3806 end ota_evt_bus;