[Home] [Help]
PACKAGE BODY: APPS.OTA_EVENT_SWI
Source
1 PACKAGE BODY OTA_EVENT_SWI As
2 /* $Header: otevtswi.pkb 120.4.12010000.2 2009/05/05 12:39:18 pekasi ship $ */
3 --
4 -- Package variables
5 --
6 g_package varchar2(33) := 'ota_event_swi.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-----------------------------< create_event >-----------------------------|
10 -- ----------------------------------------------------------------------------
11 PROCEDURE create_event
12 (p_effective_date in date default sysdate
13 ,p_event_id in number
14 ,p_vendor_id in number default null
15 ,p_activity_version_id in number default null
16 ,p_business_group_id in number
17 ,p_organization_id in number default null
18 ,p_event_type in varchar2
19 ,p_object_version_number out nocopy number
20 ,p_title in varchar2
21 ,p_budget_cost in number default null
22 ,p_actual_cost in number default null
23 ,p_budget_currency_code in varchar2 default null
24 ,p_centre in varchar2 default null
25 ,p_comments in varchar2 default null
26 ,p_course_end_date in date default null
27 ,p_course_end_time in varchar2 default null
28 ,p_course_start_date in date default null
29 ,p_course_start_time in varchar2 default null
30 ,p_duration in number default null
31 ,p_duration_units in varchar2 default null
32 ,p_enrolment_end_date in date default null
33 ,p_enrolment_start_date in date default null
34 ,p_language_id in number default null
35 ,p_user_status in varchar2 default null
36 ,p_development_event_type in varchar2 default null
37 ,p_event_status in varchar2 default null
38 ,p_price_basis in varchar2 default null
39 ,p_currency_code in varchar2 default null
40 ,p_maximum_attendees in number default null
41 ,p_maximum_internal_attendees in number default null
42 ,p_minimum_attendees in number default null
43 ,p_standard_price in number default null
44 ,p_category_code in varchar2 default null
45 ,p_parent_event_id in number default null
46 ,p_book_independent_flag in varchar2 default null
47 ,p_public_event_flag in varchar2 default null
48 ,p_secure_event_flag in varchar2 default null
49 ,p_evt_information_category in varchar2 default null
50 ,p_evt_information1 in varchar2 default null
51 ,p_evt_information2 in varchar2 default null
52 ,p_evt_information3 in varchar2 default null
53 ,p_evt_information4 in varchar2 default null
54 ,p_evt_information5 in varchar2 default null
55 ,p_evt_information6 in varchar2 default null
56 ,p_evt_information7 in varchar2 default null
57 ,p_evt_information8 in varchar2 default null
58 ,p_evt_information9 in varchar2 default null
59 ,p_evt_information10 in varchar2 default null
60 ,p_evt_information11 in varchar2 default null
61 ,p_evt_information12 in varchar2 default null
62 ,p_evt_information13 in varchar2 default null
63 ,p_evt_information14 in varchar2 default null
64 ,p_evt_information15 in varchar2 default null
65 ,p_evt_information16 in varchar2 default null
66 ,p_evt_information17 in varchar2 default null
67 ,p_evt_information18 in varchar2 default null
68 ,p_evt_information19 in varchar2 default null
69 ,p_evt_information20 in varchar2 default null
70 ,p_project_id in number default null
71 ,p_owner_id in number default null
72 ,p_line_id in number default null
73 ,p_org_id in number default null
74 ,p_training_center_id in number default null
75 ,p_location_id in number default null
76 ,p_offering_id in number default null
77 ,p_timezone in varchar2 default null
78 ,p_parent_offering_id in number
79 ,p_validate in number default hr_api.g_false_num
80 ,p_return_status out nocopy varchar2
81 ,p_data_source in varchar2 default null
82 ,p_event_availability in varchar2 default null
83 ) is
84 --
85 -- Variables for API Boolean parameters
86 l_validate boolean;
87 --
88 -- Variables for IN/OUT parameters
89 --
90 l_event_id ota_events.event_id%TYPE;
91 -- Other variables
92 l_proc varchar2(72) := g_package ||'create_event';
93 Begin
94 hr_utility.set_location(' Entering:' || l_proc,10);
95 --
96 -- Issue a savepoint
97 --
98 savepoint create_event_swi;
99 --
100 -- Initialise Multiple Message Detection
101 --
102 hr_multi_message.enable_message_list;
103 --
104 -- Remember IN OUT parameter IN values
105 --
106 --
107 -- Convert constant values to their corresponding boolean value
108 --
109 l_validate :=
110 hr_api.constant_to_boolean
111 (p_constant_value => p_validate);
112
113 -- Ignore dff validation
114
115 IF p_event_type = 'SELFPACED' THEN
116
117
118
119 ota_utility.ignore_dff_validation(p_dff_name => 'OTA_EVENTS');
120
121 END IF;
122 --
123 -- Register Surrogate ID or user key values
124 --
125 ota_evt_ins.set_base_key_value
126 (p_event_id => p_event_id
127 );
128 --
129 -- Call API
130 --
131 ota_event_api.create_class
132 (p_effective_date => p_effective_date
133 ,p_event_id => l_event_id
134 ,p_vendor_id => p_vendor_id
135 ,p_activity_version_id => p_activity_version_id
136 ,p_business_group_id => p_business_group_id
137 ,p_organization_id => p_organization_id
138 ,p_event_type => p_event_type
139 ,p_object_version_number => p_object_version_number
140 ,p_title => p_title
141 ,p_budget_cost => p_budget_cost
142 ,p_actual_cost => p_actual_cost
143 ,p_budget_currency_code => p_budget_currency_code
144 ,p_centre => p_centre
145 ,p_comments => p_comments
146 ,p_course_end_date => p_course_end_date
147 ,p_course_end_time => p_course_end_time
148 ,p_course_start_date => p_course_start_date
149 ,p_course_start_time => p_course_start_time
150 ,p_duration => p_duration
151 ,p_duration_units => p_duration_units
152 ,p_enrolment_end_date => p_enrolment_end_date
153 ,p_enrolment_start_date => p_enrolment_start_date
154 ,p_language_id => p_language_id
155 ,p_user_status => p_user_status
156 ,p_development_event_type => p_development_event_type
157 ,p_event_status => p_event_status
158 ,p_price_basis => p_price_basis
159 ,p_currency_code => p_currency_code
160 ,p_maximum_attendees => p_maximum_attendees
161 ,p_maximum_internal_attendees => p_maximum_internal_attendees
162 ,p_minimum_attendees => p_minimum_attendees
163 ,p_standard_price => p_standard_price
164 ,p_category_code => p_category_code
165 ,p_parent_event_id => p_parent_event_id
166 ,p_book_independent_flag => p_book_independent_flag
167 ,p_public_event_flag => p_public_event_flag
168 ,p_secure_event_flag => p_secure_event_flag
169 ,p_evt_information_category => p_evt_information_category
170 ,p_evt_information1 => p_evt_information1
171 ,p_evt_information2 => p_evt_information2
172 ,p_evt_information3 => p_evt_information3
173 ,p_evt_information4 => p_evt_information4
174 ,p_evt_information5 => p_evt_information5
175 ,p_evt_information6 => p_evt_information6
176 ,p_evt_information7 => p_evt_information7
177 ,p_evt_information8 => p_evt_information8
178 ,p_evt_information9 => p_evt_information9
179 ,p_evt_information10 => p_evt_information10
180 ,p_evt_information11 => p_evt_information11
181 ,p_evt_information12 => p_evt_information12
182 ,p_evt_information13 => p_evt_information13
183 ,p_evt_information14 => p_evt_information14
184 ,p_evt_information15 => p_evt_information15
185 ,p_evt_information16 => p_evt_information16
186 ,p_evt_information17 => p_evt_information17
187 ,p_evt_information18 => p_evt_information18
188 ,p_evt_information19 => p_evt_information19
189 ,p_evt_information20 => p_evt_information20
190 ,p_project_id => p_project_id
191 ,p_owner_id => p_owner_id
192 ,p_line_id => p_line_id
193 ,p_org_id => p_org_id
194 ,p_training_center_id => p_training_center_id
195 ,p_location_id => p_location_id
196 ,p_offering_id => p_offering_id
197 ,p_timezone => p_timezone
198 ,p_parent_offering_id => p_parent_offering_id
199 ,p_validate => l_validate
200 ,p_data_source => p_data_source
201 ,p_event_availability => p_event_availability
202 );
203 --
204 -- Convert API warning boolean parameter values to specific
205 -- messages and add them to Multiple Message List
206 --
207 --
208 -- Convert API non-warning boolean parameter values
209 --
210 --
211 -- Derive the API return status value based on whether
212 -- messages of any type exist in the Multiple Message List.
213 -- Also disable Multiple Message Detection.
214 --
215 p_return_status := hr_multi_message.get_return_status_disable;
216 hr_utility.set_location(' Leaving:' || l_proc,20);
217 --
218 exception
219 when hr_multi_message.error_message_exist then
220 --
221 -- Catch the Multiple Message List exception which
222 -- indicates API processing has been aborted because
223 -- at least one message exists in the list.
224 --
225 rollback to create_event_swi;
226 --
227 -- Reset IN OUT parameters and set OUT parameters
228 --
229 -- p_event_id := null;
230 p_object_version_number := null;
231 p_return_status := hr_multi_message.get_return_status_disable;
232 hr_utility.set_location(' Leaving:' || l_proc, 30);
233 when others then
234 --
235 -- When Multiple Message Detection is enabled catch
236 -- any Application specific or other unexpected
237 -- exceptions. Adding appropriate details to the
238 -- Multiple Message List. Otherwise re-raise the
239 -- error.
240 --
241 rollback to create_event_swi;
242 if hr_multi_message.unexpected_error_add(l_proc) then
243 hr_utility.set_location(' Leaving:' || l_proc,40);
244 raise;
245 end if;
246 --
247 -- Reset IN OUT and set OUT parameters
248 --
249 --p_event_id := null;
250 p_object_version_number := null;
251 p_return_status := hr_multi_message.get_return_status_disable;
252 hr_utility.set_location(' Leaving:' || l_proc,50);
253 end create_event;
254 -- ----------------------------------------------------------------------------
255 -- |-----------------------------< delete_event >-----------------------------|
256 -- ----------------------------------------------------------------------------
257 PROCEDURE delete_event
258 (p_validate in number default hr_api.g_false_num
259 ,p_event_id in number
260 ,p_object_version_number in number
261 ,p_return_status out nocopy varchar2
262 ) is
263 --
264 -- Variables for API Boolean parameters
265 l_validate boolean;
266 --
267 -- Variables for IN/OUT parameters
268 --
269 -- Other variables
270 l_proc varchar2(72) := g_package ||'delete_event';
271 Begin
272 hr_utility.set_location(' Entering:' || l_proc,10);
273 --
274 -- Issue a savepoint
275 --
276 savepoint delete_event_swi;
277 --
278 -- Initialise Multiple Message Detection
279 --
280 hr_multi_message.enable_message_list;
281 --
282 -- Remember IN OUT parameter IN values
283 --
284 --
285 -- Convert constant values to their corresponding boolean value
286 --
287 l_validate :=
288 hr_api.constant_to_boolean
289 (p_constant_value => p_validate);
290 --
291 -- Register Surrogate ID or user key values
292 --
293 --
294 -- Call API
295 --
296 ota_event_api.delete_class
297 (p_validate => l_validate
298 ,p_event_id => p_event_id
299 ,p_object_version_number => p_object_version_number
300 );
301 --
302 -- Convert API warning boolean parameter values to specific
303 -- messages and add them to Multiple Message List
304 --
305 --
306 -- Convert API non-warning boolean parameter values
307 --
308 --
309 -- Derive the API return status value based on whether
310 -- messages of any type exist in the Multiple Message List.
311 -- Also disable Multiple Message Detection.
312 --
313 p_return_status := hr_multi_message.get_return_status_disable;
314 hr_utility.set_location(' Leaving:' || l_proc,20);
315 --
316 exception
317 when hr_multi_message.error_message_exist then
318 --
319 -- Catch the Multiple Message List exception which
320 -- indicates API processing has been aborted because
321 -- at least one message exists in the list.
322 --
323 rollback to delete_event_swi;
324 --
325 -- Reset IN OUT parameters and set OUT parameters
326 --
327 p_return_status := hr_multi_message.get_return_status_disable;
328 hr_utility.set_location(' Leaving:' || l_proc, 30);
329 when others then
330 --
331 -- When Multiple Message Detection is enabled catch
332 -- any Application specific or other unexpected
333 -- exceptions. Adding appropriate details to the
334 -- Multiple Message List. Otherwise re-raise the
335 -- error.
336 --
337 rollback to delete_event_swi;
338 if hr_multi_message.unexpected_error_add(l_proc) then
339 hr_utility.set_location(' Leaving:' || l_proc,40);
340 raise;
341 end if;
342 --
343 -- Reset IN OUT and set OUT parameters
344 --
345 p_return_status := hr_multi_message.get_return_status_disable;
346 hr_utility.set_location(' Leaving:' || l_proc,50);
347 end delete_event;
348 -- ----------------------------------------------------------------------------
349 -- |-----------------------------< update_event >-----------------------------|
350 -- ----------------------------------------------------------------------------
351 PROCEDURE update_event
352 (p_event_id in number
353 ,p_effective_date in date default trunc(sysdate)
354 ,p_vendor_id in number default hr_api.g_number
355 ,p_activity_version_id in number default hr_api.g_number
356 ,p_business_group_id in number default hr_api.g_number
357 ,p_organization_id in number default hr_api.g_number
358 ,p_event_type in varchar2 default hr_api.g_varchar2
359 ,p_object_version_number in out nocopy number
360 ,p_title in varchar2 default hr_api.g_varchar2
361 ,p_budget_cost in number default hr_api.g_number
362 ,p_actual_cost in number default hr_api.g_number
363 ,p_budget_currency_code in varchar2 default hr_api.g_varchar2
364 ,p_centre in varchar2 default hr_api.g_varchar2
365 ,p_comments in varchar2 default hr_api.g_varchar2
366 ,p_course_end_date in date default hr_api.g_date
367 ,p_course_end_time in varchar2 default hr_api.g_varchar2
368 ,p_course_start_date in date default hr_api.g_date
369 ,p_course_start_time in varchar2 default hr_api.g_varchar2
370 ,p_duration in number default hr_api.g_number
371 ,p_duration_units in varchar2 default hr_api.g_varchar2
372 ,p_enrolment_end_date in date default hr_api.g_date
373 ,p_enrolment_start_date in date default hr_api.g_date
374 ,p_language_id in number default hr_api.g_number
375 ,p_user_status in varchar2 default hr_api.g_varchar2
376 ,p_development_event_type in varchar2 default hr_api.g_varchar2
377 ,p_event_status in varchar2 default hr_api.g_varchar2
378 ,p_price_basis in varchar2 default hr_api.g_varchar2
379 ,p_currency_code in varchar2 default hr_api.g_varchar2
380 ,p_maximum_attendees in number default hr_api.g_number
381 ,p_maximum_internal_attendees in number default hr_api.g_number
382 ,p_minimum_attendees in number default hr_api.g_number
383 ,p_standard_price in number default hr_api.g_number
384 ,p_category_code in varchar2 default hr_api.g_varchar2
385 ,p_parent_event_id in number default hr_api.g_number
386 ,p_book_independent_flag in varchar2 default hr_api.g_varchar2
387 ,p_public_event_flag in varchar2 default hr_api.g_varchar2
388 ,p_secure_event_flag in varchar2 default hr_api.g_varchar2
389 ,p_evt_information_category in varchar2 default hr_api.g_varchar2
390 ,p_evt_information1 in varchar2 default hr_api.g_varchar2
391 ,p_evt_information2 in varchar2 default hr_api.g_varchar2
392 ,p_evt_information3 in varchar2 default hr_api.g_varchar2
393 ,p_evt_information4 in varchar2 default hr_api.g_varchar2
394 ,p_evt_information5 in varchar2 default hr_api.g_varchar2
395 ,p_evt_information6 in varchar2 default hr_api.g_varchar2
396 ,p_evt_information7 in varchar2 default hr_api.g_varchar2
397 ,p_evt_information8 in varchar2 default hr_api.g_varchar2
398 ,p_evt_information9 in varchar2 default hr_api.g_varchar2
399 ,p_evt_information10 in varchar2 default hr_api.g_varchar2
400 ,p_evt_information11 in varchar2 default hr_api.g_varchar2
401 ,p_evt_information12 in varchar2 default hr_api.g_varchar2
402 ,p_evt_information13 in varchar2 default hr_api.g_varchar2
403 ,p_evt_information14 in varchar2 default hr_api.g_varchar2
404 ,p_evt_information15 in varchar2 default hr_api.g_varchar2
405 ,p_evt_information16 in varchar2 default hr_api.g_varchar2
406 ,p_evt_information17 in varchar2 default hr_api.g_varchar2
407 ,p_evt_information18 in varchar2 default hr_api.g_varchar2
408 ,p_evt_information19 in varchar2 default hr_api.g_varchar2
409 ,p_evt_information20 in varchar2 default hr_api.g_varchar2
410 ,p_project_id in number default hr_api.g_number
411 ,p_owner_id in number default hr_api.g_number
412 ,p_line_id in number default hr_api.g_number
413 ,p_org_id in number default hr_api.g_number
414 ,p_training_center_id in number default hr_api.g_number
415 ,p_location_id in number default hr_api.g_number
416 ,p_offering_id in number default hr_api.g_number
417 ,p_timezone in varchar2 default hr_api.g_varchar2
418 ,p_parent_offering_id in number default hr_api.g_number
419 ,p_validate in number default hr_api.g_false_num
420 ,p_return_status out nocopy varchar2
421 ,p_data_source in varchar2 default hr_api.g_varchar2
422 ,p_event_availability in varchar2 default null
423 ) is
424 --
425 -- Variables for API Boolean parameters
426 l_validate boolean;
427 --
428 -- Variables for IN/OUT parameters
429 l_object_version_number number;
430 --
431 -- Other variables
432 l_proc varchar2(72) := g_package ||'update_event';
433 Begin
434 hr_utility.set_location(' Entering:' || l_proc,10);
435 --
436 -- Issue a savepoint
437 --
438 savepoint update_event_swi;
439 --
440 -- Initialise Multiple Message Detection
441 --
442 hr_multi_message.enable_message_list;
443 --
444 -- Remember IN OUT parameter IN values
445 --
446 l_object_version_number := p_object_version_number;
447 --
448 -- Convert constant values to their corresponding boolean value
449 --
450 l_validate :=
451 hr_api.constant_to_boolean
452 (p_constant_value => p_validate);
453 --
454 -- Register Surrogate ID or user key values
455 --
456 --
457 -- Call API
458 --
459 ota_event_api.update_class
460 (p_event_id => p_event_id
461 ,p_effective_date => p_effective_date
462 ,p_vendor_id => p_vendor_id
463 ,p_activity_version_id => p_activity_version_id
464 ,p_business_group_id => p_business_group_id
465 ,p_organization_id => p_organization_id
466 ,p_event_type => p_event_type
467 ,p_object_version_number => p_object_version_number
468 ,p_title => p_title
469 ,p_budget_cost => p_budget_cost
470 ,p_actual_cost => p_actual_cost
471 ,p_budget_currency_code => p_budget_currency_code
472 ,p_centre => p_centre
473 ,p_comments => p_comments
474 ,p_course_end_date => p_course_end_date
475 ,p_course_end_time => p_course_end_time
476 ,p_course_start_date => p_course_start_date
477 ,p_course_start_time => p_course_start_time
478 ,p_duration => p_duration
479 ,p_duration_units => p_duration_units
480 ,p_enrolment_end_date => p_enrolment_end_date
481 ,p_enrolment_start_date => p_enrolment_start_date
482 ,p_language_id => p_language_id
483 ,p_user_status => p_user_status
484 ,p_development_event_type => p_development_event_type
485 ,p_event_status => p_event_status
486 ,p_price_basis => p_price_basis
487 ,p_currency_code => p_currency_code
488 ,p_maximum_attendees => p_maximum_attendees
489 ,p_maximum_internal_attendees => p_maximum_internal_attendees
490 ,p_minimum_attendees => p_minimum_attendees
491 ,p_standard_price => p_standard_price
492 ,p_category_code => p_category_code
493 ,p_parent_event_id => p_parent_event_id
494 ,p_book_independent_flag => p_book_independent_flag
495 ,p_public_event_flag => p_public_event_flag
496 ,p_secure_event_flag => p_secure_event_flag
497 ,p_evt_information_category => p_evt_information_category
498 ,p_evt_information1 => p_evt_information1
499 ,p_evt_information2 => p_evt_information2
500 ,p_evt_information3 => p_evt_information3
501 ,p_evt_information4 => p_evt_information4
502 ,p_evt_information5 => p_evt_information5
503 ,p_evt_information6 => p_evt_information6
504 ,p_evt_information7 => p_evt_information7
505 ,p_evt_information8 => p_evt_information8
506 ,p_evt_information9 => p_evt_information9
507 ,p_evt_information10 => p_evt_information10
508 ,p_evt_information11 => p_evt_information11
509 ,p_evt_information12 => p_evt_information12
510 ,p_evt_information13 => p_evt_information13
511 ,p_evt_information14 => p_evt_information14
512 ,p_evt_information15 => p_evt_information15
513 ,p_evt_information16 => p_evt_information16
514 ,p_evt_information17 => p_evt_information17
515 ,p_evt_information18 => p_evt_information18
516 ,p_evt_information19 => p_evt_information19
517 ,p_evt_information20 => p_evt_information20
518 ,p_project_id => p_project_id
519 ,p_owner_id => p_owner_id
520 ,p_line_id => p_line_id
521 ,p_org_id => p_org_id
522 ,p_training_center_id => p_training_center_id
523 ,p_location_id => p_location_id
524 ,p_offering_id => p_offering_id
525 ,p_timezone => p_timezone
526 ,p_parent_offering_id => p_parent_offering_id
527 ,p_validate => l_validate
528 ,p_data_source => p_data_source
529 ,p_event_availability => p_event_availability
530 );
531 --
532 -- Convert API warning boolean parameter values to specific
533 -- messages and add them to Multiple Message List
534 --
535 --
536 -- Convert API non-warning boolean parameter values
537 --
538 --
539 -- Derive the API return status value based on whether
540 -- messages of any type exist in the Multiple Message List.
541 -- Also disable Multiple Message Detection.
542 --
543 p_return_status := hr_multi_message.get_return_status_disable;
544 hr_utility.set_location(' Leaving:' || l_proc,20);
545 --
546 exception
547 when hr_multi_message.error_message_exist then
548 --
549 -- Catch the Multiple Message List exception which
550 -- indicates API processing has been aborted because
551 -- at least one message exists in the list.
552 --
553 rollback to update_event_swi;
554 --
555 -- Reset IN OUT parameters and set OUT parameters
556 --
557 p_object_version_number := l_object_version_number;
558 p_return_status := hr_multi_message.get_return_status_disable;
559 hr_utility.set_location(' Leaving:' || l_proc, 30);
560 when others then
561 --
562 -- When Multiple Message Detection is enabled catch
563 -- any Application specific or other unexpected
564 -- exceptions. Adding appropriate details to the
565 -- Multiple Message List. Otherwise re-raise the
566 -- error.
567 --
568 rollback to update_event_swi;
569 if hr_multi_message.unexpected_error_add(l_proc) then
570 hr_utility.set_location(' Leaving:' || l_proc,40);
571 raise;
572 end if;
573 --
574 -- Reset IN OUT and set OUT parameters
575 --
576 p_object_version_number := l_object_version_number;
577 p_return_status := hr_multi_message.get_return_status_disable;
578 hr_utility.set_location(' Leaving:' || l_proc,50);
579 end update_event;
580
581 -- ----------------------------------------------------------------------------
582 -- |-----------------------------< update_enrollment >------------------------|
583 -- ----------------------------------------------------------------------------
584
585 procedure update_enrollment (p_booking_id IN NUMBER,
586 p_daemon_flag IN VARCHAR2,
587 p_daemon_type IN VARCHAR2,
588 p_booking_status_type_id IN NUMBER,
589 p_event_id IN NUMBER,
590 p_object_version_number IN NUMBER,
591 p_return_status out nocopy varchar2) is
592
593 l_proc varchar2(72) := g_package ||'update_enrollment';
594
595 l_object_version_number number;
596 l_tfl_object_version_number number;
597 l_finance_line_id number;
598 begin
599 savepoint update_enrollment;
600 --
601 hr_multi_message.enable_message_list;
602 l_object_version_number := p_object_version_number ;
603 ota_tdb_api_upd2.update_enrollment
604 (
605 p_booking_id => p_booking_id,
606 p_booking_status_type_id => p_booking_status_type_id ,
607 P_event_id => p_event_id,
608 p_enrollment_type => 'S' ,
609 p_daemon_flag => p_daemon_flag ,
610 p_daemon_type => p_daemon_type ,
611 p_object_version_number => l_object_version_number,
612 p_tfl_object_version_number => l_tfl_object_version_number,
613 p_finance_line_id => l_finance_line_id
614 );
615 p_return_status := hr_multi_message.get_return_status_disable;
616 hr_utility.set_location(' Leaving:' || l_proc,20);
617
618
619 exception
620 when hr_multi_message.error_message_exist then
621 --
622 -- Catch the Multiple Message List exception which
623 -- indicates API processing has been aborted because
624 -- at least one message exists in the list.
625 --
626 rollback to update_enrollment;
627 --
628 -- Reset IN OUT parameters and set OUT parameters
629 --
630
631 p_return_status := hr_multi_message.get_return_status_disable;
632 hr_utility.set_location(' Leaving:' || l_proc, 30);
633 when others then
634 --
635 -- When Multiple Message Detection is enabled catch
636 -- any Application specific or other unexpected
637 -- exceptions. Adding appropriate details to the
638 -- Multiple Message List. Otherwise re-raise the
639 -- error.
640 --
641 rollback to update_enrollment;
642 if hr_multi_message.unexpected_error_add(l_proc) then
643 hr_utility.set_location(' Leaving:' || l_proc,40);
644 raise;
645 end if;
646 --
647 -- Reset IN OUT and set OUT parameters
648 --
649
650 p_return_status := hr_multi_message.get_return_status_disable;
651 hr_utility.set_location(' Leaving:' || l_proc,50);
652 end update_enrollment;
653
654 --
655 -- ----------------------------------------------------------------------------
656 -- |-----------------------------< upd2_update_event >------------------------|
657 -- ----------------------------------------------------------------------------
658
659 procedure upd2_update_event
660 (
661 p_event in varchar2,
662 p_event_id in number,
663 p_object_version_number in out nocopy number,
664 p_event_status in out nocopy varchar2,
665 p_validate in number default hr_api.g_false_num,
666 p_reset_max_attendees in number default hr_api.g_false_num,
667 p_update_finance_line in varchar2 default 'N',
668 p_booking_status_type_id in number default null,
669 p_date_status_changed in date default null,
670 p_maximum_attendees in number default null,
671 p_change_status in varchar2 default 'A',
672 p_return_status out nocopy varchar2,
673 p_check_for_warning in varchar2 default 'Y',
674 p_message_name out nocopy varchar2) is
675 --
676 --
677 l_event_type varchar2(30);
678 l_invalid_profile boolean;
679 l_waitlist_hours number;
680 l_maximum_attendees_old number;
681
682 l_different_hours number(13,2);
683 l_current_date date;
684 l_event_date date;
685 l_event_title ota_events.title%type;
686 l_owner_id ota_events.owner_id%type;
687 l_sysdate varchar2(60);
688 l_boolean boolean;
689
690 CURSOR C_EVENT_DATE (p_event_id ota_events.event_ID%type) IS
691 SELECT to_date(to_char(evt.Course_start_date,'DD-MM-YYYY')||EVT.Course_start_time,'DD-MM-YYYYHH24:MI'),EVENT_TYPE,TITLE,OWNER_ID,MAXIMUM_ATTENDEES
692 FROM OTA_EVENTS EVT
693 WHERE evt.event_id = p_event_id;
694
695 CURSOR C_DATE IS
696 SELECT SYSDATE
697 FROM DUAL;
698
699
700 business_group_id hr_all_organization_units.organization_id%TYPE; --- ** Globalization changes
701
702 l_validate boolean;
703 l_reset_max_attendees boolean;
704 l_proc varchar2(72) := g_package ||'upd2_update_event';
705
706 --
707 begin
708 hr_utility.set_location(' Entering:' || l_proc,10);
709 --
710 -- Issue a savepoint
711 --
712 savepoint upd2_update_event;
713 --
714 -- Initialise Multiple Message Detection
715 --
716 hr_multi_message.enable_message_list;
717
718 OPEN c_event_date(p_event_id);
719 FETCH c_event_date into l_event_date,l_event_type,l_event_title,l_owner_id,l_maximum_attendees_old;
720 CLOSE c_event_date;
721 if p_check_for_warning = 'Y' then
722
723 if p_change_status = 'S' and p_event_status = 'A'
724 then
725 p_message_name := 'OTA_13557_EVT_CANCEL_TDB';
726 p_return_status := hr_multi_message.get_return_status_disable;
727 hr_utility.set_location(' Leaving:' || l_proc,20);
728 return;
729 end if;
730
731 if p_change_status = 'S' and
732 ota_evt_bus2.resource_booking_exists(p_event_id) then
733 p_message_name := 'OTA_13525_EVT_CANCEL_RESOURCE';
734 p_return_status := hr_multi_message.get_return_status_disable;
735 hr_utility.set_location(' Leaving:' || l_proc,20);
736 return;
737 end if;
738
739 IF p_change_status = 'A' AND
740 p_maximum_attendees <> l_maximum_attendees_old THEN
741 p_message_name := 'OTA_13699_EVT_PRICING';
742 p_return_status := hr_multi_message.get_return_status_disable;
743 hr_utility.set_location(' Leaving:' || l_proc,20);
744 return;
745 END IF;
746
747 end if;
748
749 l_validate := hr_api.constant_to_boolean
750 (p_constant_value => p_validate);
751
752 l_reset_max_attendees := hr_api.constant_to_boolean
753 (p_constant_value => p_reset_max_attendees);
754
755 OTA_EVT_API_UPD2.UPDATE_EVENT (
756 P_EVENT => p_event,
757 P_EVENT_ID => p_event_id,
758 P_OBJECT_VERSION_NUMBER => p_object_version_number ,
759 P_EVENT_STATUS => p_event_status ,
760 P_VALIDATE => l_validate ,
761 P_BOOKING_STATUS_TYPE_ID => p_booking_status_type_id,
762 P_UPDATE_FINANCE_LINE => p_update_finance_line,
763 P_RESET_MAX_ATTENDEES => l_reset_max_attendees,
764 P_DATE_STATUS_CHANGED => p_date_status_changed ,
765 P_maximum_attendees => p_maximum_attendees );
766
767 commit;
768
769 if ota_evt_bus2.wait_list_required(p_event_type => 'EVENT'
770 ,p_event_id => p_event_id
771 ,p_event_status => p_event_status
772 ,p_booking_status_type_id => 1) then
773 --
774
775
776 begin
777 --
778 l_waitlist_hours := to_number(fnd_profile.value('OTA_AUTO_WAITLIST_DAYS'));
779 l_invalid_profile := false;
780 --
781 exception
782 when OTHERS then
783 l_invalid_profile := true;
784 --
785 end;
786
787 if fnd_profile.value('OTA_AUTO_WAITLIST_ACTIVE') = 'Y' then
788
789
790 OPEN C_DATE;
791 FETCH C_DATE INTO l_CURRENT_DATE;
792 close c_date;
793
794 l_different_hours := l_event_date - l_current_date ;
795 l_different_hours := l_different_hours * 24 ;
796
797 IF (not l_invalid_profile) and
798 fnd_profile.value('OTA_AUTO_WAITLIST_BOOKING_STATUS') is not null then
799 --
800 IF l_different_hours > nvl(l_waitlist_hours,0) THEN
801 business_group_id := OTA_GENERAL.get_business_group_id;
802 ota_tdb_waitlist_api.auto_enroll_from_waitlist (
803 p_validate => false
804 ,p_business_group_id => business_group_id
805 ,p_event_id => p_event_id
806 );
807
808 ELSE
809
810 select to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS') into l_sysdate from dual;
811
812 OTA_INITIALIZATION_WF.MANUAL_ENROLL_WAITLIST(
813 p_itemtype => 'OTWF',
814 p_process => 'OTA_MANUAL_ENROLL_WAITLIST',
815 p_Event_title => p_event_id, --Enh 5606090: Language support for Event Details.
816 p_item_key => p_event_id||':'||l_sysdate,
817 p_owner_id => l_owner_id
818 );
819
820 END IF;
821
822
823 -- if l_event_type = 'SCHEDULED' then
824 if (l_event_type ='SCHEDULED' or l_event_type ='SELFPACED') then
825 --
826 --
827 -- We require an explicit commit if the enrollment has been
828 -- performed by way of increasing the max on the events form.
829 -- This is because the update has been carried out independently of
830 -- the forms commit process, and no implicit commit has been done.
831 --
832 commit;
833 --
834 end if;
835
836 END IF;
837
838 else
839 p_message_name := 'OTA_13553_WAITLIST_EXISTS';
840 p_return_status := hr_multi_message.get_return_status_disable;
841 hr_utility.set_location(' Leaving:' || l_proc,20);
842 return;
843
844 -- ***
845 --
846 --
847 end if;
848 --
849 end if;
850 --
851 p_return_status := hr_multi_message.get_return_status_disable;
852 hr_utility.set_location(' Leaving:' || l_proc,20);
853 exception
854 when hr_multi_message.error_message_exist then
855 --
856 -- Catch the Multiple Message List exception which
857 -- indicates API processing has been aborted because
858 -- at least one message exists in the list.
859 --
860 rollback to upd2_update_event;
861 --
862 -- Reset IN OUT parameters and set OUT parameters
863 --
864 p_return_status := hr_multi_message.get_return_status_disable;
865 hr_utility.set_location(' Leaving:' || l_proc, 30);
866 when others then
867 --
868 -- When Multiple Message Detection is enabled catch
869 -- any Application specific or other unexpected
870 -- exceptions. Adding appropriate details to the
871 -- Multiple Message List. Otherwise re-raise the
872 -- error.
873 --
874 rollback to upd2_update_event;
875 if hr_multi_message.unexpected_error_add(l_proc) then
876 hr_utility.set_location(' Leaving:' || l_proc,40);
877 raise;
878 end if;
879 --
880 -- Reset IN OUT and set OUT parameters
881 --
882
883 p_return_status := hr_multi_message.get_return_status_disable;
884 hr_utility.set_location(' Leaving:' || l_proc,50);
885 end upd2_update_event;
886
887
888 --
889 -- ----------------------------------------------------------------------------
890 -- |-----------------------------< check_session_overlap >------------------------|
891 -- ----------------------------------------------------------------------------
892 PROCEDURE check_session_overlap
893 ( p_event_id IN NUMBER
894 ,p_parent_event_id IN NUMBER
895 ,p_session_date IN DATE
896 ,p_session_start_time IN VARCHAR2
897 ,p_session_end_time IN VARCHAR2
898 ,p_warning OUT NOCOPY VARCHAR2) IS
899
900 cursor c_get_session is
901 select nvl(course_start_time, '-99:99') course_start_time,
902 nvl(course_end_time, '99:99') course_end_time
903 from ota_events
904 where event_id <> p_event_id
905 and parent_event_id = p_parent_event_id
906 and nvl(course_start_date, sysdate) = nvl(p_session_date, sysdate)
907 and event_type = 'SESSION';
908
909 BEGIN
910 p_warning := 'N';
911 for l_session in c_get_session loop
912 --
913 if nvl(p_session_start_time,'-99:99') <= l_session.course_end_time and
914 nvl(p_session_end_time,'99:99') >= l_session.course_start_time then
915 --
916 p_warning := 'Y';
917 --
918 end if;
919 --
920 end loop;
921 --
922 END check_session_overlap;
923
924
925 end ota_event_swi;