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