[Home] [Help]
PACKAGE BODY: APPS.OTA_RESOURCE_BOOKING_API
Source
1 PACKAGE BODY OTA_RESOURCE_BOOKING_API as
2 /* $Header: ottrbapi.pkb 120.4 2006/03/06 02:37:26 rdola noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' OTA_RESOURCE_BOOKING_API.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-------------------------< create_resource_booking >----------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_resource_booking
13 (p_effective_date in date
14 ,p_supplied_resource_id in number
15 ,p_date_booking_placed in date
16 ,p_status in varchar2
17 ,p_event_id in number default null
18 ,p_absolute_price in number default null
19 ,p_booking_person_id in number default null
20 ,p_comments in varchar2 default null
21 ,p_contact_name in varchar2 default null
22 ,p_contact_phone_number in varchar2 default null
23 ,p_delegates_per_unit in number default null
24 ,p_quantity in number default null
25 ,p_required_date_from in date default null
26 ,p_required_date_to in date default null
27 ,p_required_end_time in varchar2 default null
28 ,p_required_start_time in varchar2 default null
29 ,p_deliver_to in varchar2 default null
30 ,p_primary_venue_flag in varchar2 default null
31 ,p_role_to_play in varchar2 default null
32 ,p_trb_information_category in varchar2 default null
33 ,p_trb_information1 in varchar2 default null
34 ,p_trb_information2 in varchar2 default null
35 ,p_trb_information3 in varchar2 default null
36 ,p_trb_information4 in varchar2 default null
37 ,p_trb_information5 in varchar2 default null
38 ,p_trb_information6 in varchar2 default null
39 ,p_trb_information7 in varchar2 default null
40 ,p_trb_information8 in varchar2 default null
41 ,p_trb_information9 in varchar2 default null
42 ,p_trb_information10 in varchar2 default null
43 ,p_trb_information11 in varchar2 default null
44 ,p_trb_information12 in varchar2 default null
45 ,p_trb_information13 in varchar2 default null
46 ,p_trb_information14 in varchar2 default null
47 ,p_trb_information15 in varchar2 default null
48 ,p_trb_information16 in varchar2 default null
49 ,p_trb_information17 in varchar2 default null
50 ,p_trb_information18 in varchar2 default null
51 ,p_trb_information19 in varchar2 default null
52 ,p_trb_information20 in varchar2 default null
53 ,p_display_to_learner_flag in varchar2 default null
54 ,p_book_entire_period_flag in varchar2 default null
55 -- ,p_unbook_request_flag in varchar2 default null
56 ,p_chat_id in number default null
57 ,p_forum_id in number default null
58 ,p_validate in boolean default false
59 ,p_resource_booking_id out nocopy number
60 ,p_object_version_number out nocopy number
61 ,p_timezone_code IN VARCHAR2 DEFAULT NULL
62 ) is
63 --
64 -- Declare cursors and local variables
65 --
66 l_proc varchar2(72) := g_package||' create_resource_booking ';
67 l_object_version_number number;
68 l_effective_date date;
69 l_resource_booking_id ota_resource_bookings.resource_booking_id%type ;
70
71 l_person_id per_people_f.person_id%type;
72
73 cursor get_trainer_id is
74 select osr.trainer_id from ota_suppliable_resources osr
75 where
76 osr.resource_type ='T'
77 and osr.supplied_resource_id = p_supplied_resource_id;
78
79 begin
80 hr_utility.set_location('Entering:'|| l_proc, 10);
81 --
82 -- Issue a savepoint
83 --
84 savepoint create_resource_booking;
85 --
86 -- Truncate the time portion from all IN date parameters
87 --
88 l_effective_date := trunc(p_effective_date);
89
90 --
91 -- Call Before Process User Hook
92 --
93 begin
94 ota_resource_booking_bk1.create_resource_booking_b(
95 p_effective_date => l_effective_date ,
96 p_supplied_resource_id => p_supplied_resource_id,
97 p_date_booking_placed => p_date_booking_placed ,
98 p_status => p_status ,
99 p_event_id => p_event_id ,
100 p_absolute_price => p_absolute_price ,
101 p_booking_person_id => p_booking_person_id ,
102 p_comments => p_comments ,
103 p_contact_name => p_contact_name ,
104 p_contact_phone_number => p_contact_phone_number,
105 p_delegates_per_unit => p_delegates_per_unit ,
106 p_quantity => p_quantity ,
107 p_required_date_from => p_required_date_from ,
108 p_required_date_to => p_required_date_to ,
109 p_required_end_time => p_required_end_time ,
110 p_required_start_time => p_required_start_time ,
111 p_deliver_to => p_deliver_to ,
112 p_primary_venue_flag => p_primary_venue_flag ,
113 p_role_to_play => p_role_to_play ,
114 p_trb_information_category => p_trb_information_category ,
115 p_trb_information1 => p_trb_information1 ,
116 p_trb_information2 => p_trb_information2 ,
117 p_trb_information3 => p_trb_information3 ,
118 p_trb_information4 => p_trb_information4 ,
119 p_trb_information5 => p_trb_information5 ,
120 p_trb_information6 => p_trb_information6 ,
121 p_trb_information7 => p_trb_information7 ,
122 p_trb_information8 => p_trb_information8 ,
123 p_trb_information9 => p_trb_information9 ,
124 p_trb_information10 => p_trb_information10 ,
125 p_trb_information11 => p_trb_information11 ,
126 p_trb_information12 => p_trb_information12 ,
127 p_trb_information13 => p_trb_information13 ,
128 p_trb_information14 => p_trb_information14 ,
129 p_trb_information15 => p_trb_information15 ,
130 p_trb_information16 => p_trb_information16 ,
131 p_trb_information17 => p_trb_information17 ,
132 p_trb_information18 => p_trb_information18 ,
133 p_trb_information19 => p_trb_information19 ,
134 p_trb_information20 => p_trb_information20 ,
135 p_display_to_learner_flag => p_display_to_learner_flag
136 ,p_book_entire_period_flag => p_book_entire_period_flag
137 -- ,p_unbook_request_flag => p_unbook_request_flag ,
138 ,p_chat_id => p_chat_id
139 ,p_forum_id => p_forum_id
140 ,p_resource_booking_id => l_resource_booking_id ,
141 p_object_version_number => l_object_version_number
142 ,p_timezone_code => p_timezone_code );
143
144
145 exception
146 when hr_api.cannot_find_prog_unit then
147 hr_api.cannot_find_prog_unit_error
148 (p_module_name => 'create_resource_booking'
149 ,p_hook_type => 'BP'
150 );
151 end;
152 --
153 -- Validation in addition to Row Handlers
154 --
155 --
156 -- Process Logic
157 --
158 ota_trb_ins.ins(
159 p_effective_date => l_effective_date ,
160 p_supplied_resource_id => p_supplied_resource_id,
161 p_date_booking_placed => p_date_booking_placed ,
162 p_status => p_status ,
163 p_event_id => p_event_id ,
164 p_absolute_price => p_absolute_price ,
165 p_booking_person_id => p_booking_person_id ,
166 p_comments => p_comments ,
167 p_contact_name => p_contact_name ,
168 p_contact_phone_number => p_contact_phone_number,
169 p_delegates_per_unit => p_delegates_per_unit ,
170 p_quantity => p_quantity ,
171 p_required_date_from => p_required_date_from ,
172 p_required_date_to => p_required_date_to ,
173 p_required_end_time => p_required_end_time ,
174 p_required_start_time => p_required_start_time ,
175 p_deliver_to => p_deliver_to ,
176 p_primary_venue_flag => p_primary_venue_flag ,
177 p_role_to_play => p_role_to_play ,
178 p_trb_information_category => p_trb_information_category ,
179 p_trb_information1 => p_trb_information1 ,
180 p_trb_information2 => p_trb_information2 ,
181 p_trb_information3 => p_trb_information3 ,
182 p_trb_information4 => p_trb_information4 ,
183 p_trb_information5 => p_trb_information5 ,
184 p_trb_information6 => p_trb_information6 ,
185 p_trb_information7 => p_trb_information7 ,
186 p_trb_information8 => p_trb_information8 ,
187 p_trb_information9 => p_trb_information9 ,
188 p_trb_information10 => p_trb_information10 ,
189 p_trb_information11 => p_trb_information11 ,
190 p_trb_information12 => p_trb_information12 ,
191 p_trb_information13 => p_trb_information13 ,
192 p_trb_information14 => p_trb_information14 ,
193 p_trb_information15 => p_trb_information15 ,
194 p_trb_information16 => p_trb_information16 ,
195 p_trb_information17 => p_trb_information17 ,
196 p_trb_information18 => p_trb_information18 ,
197 p_trb_information19 => p_trb_information19 ,
198 p_trb_information20 => p_trb_information20 ,
199 p_display_to_learner_flag => p_display_to_learner_flag
200 ,p_book_entire_period_flag => p_book_entire_period_flag
201 -- ,p_unbook_request_flag => p_unbook_request_flag,
202 , p_chat_id => p_chat_id
203 , p_forum_id => p_forum_id
204 , p_resource_booking_id => l_resource_booking_id ,
205 p_object_version_number => l_object_version_number
206 ,p_timezone_code => p_timezone_code);
207
208 --
209 -- Call After Process User Hook
210 --
211 begin
212 OTA_resource_booking_bk1.create_resource_booking_a
213 (
214 p_effective_date => l_effective_date ,
215 p_supplied_resource_id => p_supplied_resource_id,
216 p_date_booking_placed => p_date_booking_placed ,
217 p_status => p_status ,
218 p_event_id => p_event_id ,
219 p_absolute_price => p_absolute_price ,
220 p_booking_person_id => p_booking_person_id ,
221 p_comments => p_comments ,
222 p_contact_name => p_contact_name ,
223 p_contact_phone_number => p_contact_phone_number,
224 p_delegates_per_unit => p_delegates_per_unit ,
225 p_quantity => p_quantity ,
226 p_required_date_from => p_required_date_from ,
227 p_required_date_to => p_required_date_to ,
228 p_required_end_time => p_required_end_time ,
229 p_required_start_time => p_required_start_time ,
230 p_deliver_to => p_deliver_to ,
231 p_primary_venue_flag => p_primary_venue_flag ,
232 p_role_to_play => p_role_to_play ,
233 p_trb_information_category => p_trb_information_category ,
234 p_trb_information1 => p_trb_information1 ,
235 p_trb_information2 => p_trb_information2 ,
236 p_trb_information3 => p_trb_information3 ,
237 p_trb_information4 => p_trb_information4 ,
238 p_trb_information5 => p_trb_information5 ,
239 p_trb_information6 => p_trb_information6 ,
240 p_trb_information7 => p_trb_information7 ,
241 p_trb_information8 => p_trb_information8 ,
242 p_trb_information9 => p_trb_information9 ,
243 p_trb_information10 => p_trb_information10 ,
244 p_trb_information11 => p_trb_information11 ,
245 p_trb_information12 => p_trb_information12 ,
246 p_trb_information13 => p_trb_information13 ,
247 p_trb_information14 => p_trb_information14 ,
248 p_trb_information15 => p_trb_information15 ,
249 p_trb_information16 => p_trb_information16 ,
250 p_trb_information17 => p_trb_information17 ,
251 p_trb_information18 => p_trb_information18 ,
252 p_trb_information19 => p_trb_information19 ,
253 p_trb_information20 => p_trb_information20 ,
254 p_display_to_learner_flag => p_display_to_learner_flag
255 ,p_book_entire_period_flag => p_book_entire_period_flag
256 -- ,p_unbook_request_flag => p_unbook_request_flag,
257 ,p_chat_id => p_chat_id
258 ,p_forum_id => p_forum_id
259 ,p_resource_booking_id => l_resource_booking_id ,
260 p_object_version_number => l_object_version_number
261 ,p_timezone_code => p_timezone_code);
262
263
264 exception
265 when hr_api.cannot_find_prog_unit then
266 hr_api.cannot_find_prog_unit_error
267 (p_module_name => 'create_resource_booking'
268 ,p_hook_type => 'AP'
269 );
270 end;
271 --
272 -- When in validation only mode raise the Validate_Enabled exception
273 --
274 if p_validate then
275 raise hr_api.validate_enabled;
276 end if;
277 --
278 -- Set all output arguments
279 --
280 --
281 p_object_version_number := l_object_version_number;
282 p_resource_booking_id := l_resource_booking_id ;
283
284
285 open get_trainer_id;
286 fetch get_trainer_id into l_person_id;
287 close get_trainer_id;
288
289 if l_person_id is not null and p_event_id is not null then
290 -- call to instructor notification process not for independent resources
291 OTA_INITIALIZATION_WF.initialize_instructor_wf(
292 p_item_type => 'OTWF',
293 p_eventid => p_event_id,
294 p_sup_res_id => p_supplied_resource_id,
295 p_start_date => p_required_date_from,
296 p_end_date => p_required_date_to,
297 p_start_time => p_required_start_time,
298 p_end_time => p_required_end_time,
299 p_status => p_status,
300 p_res_book_id => p_resource_booking_id,
301 p_person_id => l_person_id,
302 p_event_fired => 'INSTRUCTOR_BOOK');
303
304 end if;
305 --
306 hr_utility.set_location(' Leaving:'||l_proc, 70);
307 exception
308 when hr_api.validate_enabled then
309 --
310 -- As the Validate_Enabled exception has been raised
311 -- we must rollback to the savepoint
312 --
313 rollback to create_resource_booking;
314 --
315 -- Only set output warning arguments
316 -- (Any key or derived arguments must be set to null
317 -- when validation only mode is being used.)
318 --
319 p_object_version_number := null;
320 hr_utility.set_location(' Leaving:'||l_proc, 80);
321 when others then
322 --
323 -- A validation or unexpected error has occured
327 hr_utility.set_location(' Leaving:'||l_proc, 90);
324 --
325 rollback to create_resource_booking;
326 p_object_version_number := null;
328 raise;
329 end create_resource_booking ;
330 -- ----------------------------------------------------------------------------
331 -- |-------------------------< update_resource_booking >-----------------------|
332 -- ----------------------------------------------------------------------------
333 --
334 procedure update_resource_booking
335 (p_effective_date in date
336 ,p_supplied_resource_id in number
337 ,p_date_booking_placed in date
338 ,p_status in varchar2
339 ,p_event_id in number default hr_api.g_number
340 ,p_absolute_price in number default hr_api.g_number
341 ,p_booking_person_id in number default hr_api.g_number
342 ,p_comments in varchar2 default hr_api.g_varchar2
343 ,p_contact_name in varchar2 default hr_api.g_varchar2
344 ,p_contact_phone_number in varchar2 default hr_api.g_varchar2
345 ,p_delegates_per_unit in number default hr_api.g_number
346 ,p_quantity in number default hr_api.g_number
347 ,p_required_date_from in date default hr_api.g_date
348 ,p_required_date_to in date default hr_api.g_date
349 ,p_required_end_time in varchar2 default hr_api.g_varchar2
350 ,p_required_start_time in varchar2 default hr_api.g_varchar2
351 ,p_deliver_to in varchar2 default hr_api.g_varchar2
352 ,p_primary_venue_flag in varchar2 default hr_api.g_varchar2
353 ,p_role_to_play in varchar2 default hr_api.g_varchar2
354 ,p_trb_information_category in varchar2 default hr_api.g_varchar2
355 ,p_trb_information1 in varchar2 default hr_api.g_varchar2
356 ,p_trb_information2 in varchar2 default hr_api.g_varchar2
357 ,p_trb_information3 in varchar2 default hr_api.g_varchar2
358 ,p_trb_information4 in varchar2 default hr_api.g_varchar2
359 ,p_trb_information5 in varchar2 default hr_api.g_varchar2
360 ,p_trb_information6 in varchar2 default hr_api.g_varchar2
361 ,p_trb_information7 in varchar2 default hr_api.g_varchar2
362 ,p_trb_information8 in varchar2 default hr_api.g_varchar2
363 ,p_trb_information9 in varchar2 default hr_api.g_varchar2
364 ,p_trb_information10 in varchar2 default hr_api.g_varchar2
365 ,p_trb_information11 in varchar2 default hr_api.g_varchar2
366 ,p_trb_information12 in varchar2 default hr_api.g_varchar2
367 ,p_trb_information13 in varchar2 default hr_api.g_varchar2
368 ,p_trb_information14 in varchar2 default hr_api.g_varchar2
369 ,p_trb_information15 in varchar2 default hr_api.g_varchar2
370 ,p_trb_information16 in varchar2 default hr_api.g_varchar2
371 ,p_trb_information17 in varchar2 default hr_api.g_varchar2
372 ,p_trb_information18 in varchar2 default hr_api.g_varchar2
373 ,p_trb_information19 in varchar2 default hr_api.g_varchar2
374 ,p_trb_information20 in varchar2 default hr_api.g_varchar2
375 ,p_display_to_learner_flag in varchar2 default hr_api.g_varchar2
376 ,p_book_entire_period_flag in varchar2 default hr_api.g_varchar2
377 -- ,p_unbook_request_flag in varchar2 default hr_api.g_varchar2
378 ,p_chat_id in number default hr_api.g_number
379 ,p_forum_id in number default hr_api.g_number
380 ,p_validate in boolean
381 ,p_resource_booking_id in number
382 ,p_object_version_number in out nocopy number
383 ,p_timezone_code IN VARCHAR2 DEFAULT hr_api.g_varchar2
384 ) is
385 --
386 -- Declare cursors and local variables
387 --
388 l_proc varchar2(72) := g_package||' update_resource_booking ';
389 l_object_version_number number := p_object_version_number;
390 l_effective_date date;
391 l_person_id per_people_f.person_id%type;
392
393 cursor get_res_info
394 is
395 select orb.status,orb.event_id,orb.required_date_from
396 from ota_resource_bookings orb
397 where resource_booking_id = p_resource_booking_id;
398
399 cursor get_trainer_id is
400 select osr.trainer_id from ota_suppliable_resources osr
401 where
402 osr.resource_type ='T'
403 and osr.supplied_resource_id = p_supplied_resource_id;
404
405
406 l_status varchar2(30);
407 l_event_id ota_events.event_id%type;
408 l_date_from date;
409 l_start_date_changed boolean;
410
411 l_notify_days_before number(9) := fnd_profile.value('OTA_INST_REMIND_NTF_DAYS');
412
413 begin
414 hr_utility.set_location('Entering:'|| l_proc, 10);
415 --
416 -- Issue a savepoint
417 --
418 savepoint update_resource_booking ;
419
420 --get the values req for ntf process
421
422 open get_res_info;
423 fetch get_res_info into
424 l_status,l_event_id,l_date_from;
425 close get_res_info;
426
427 open get_trainer_id;
428 fetch get_trainer_id into l_person_id;
429 close get_trainer_id;
430 --
434 --
431 -- Truncate the time portion from all IN date parameters
432 --
433 l_effective_date := trunc(p_effective_date);
435
436 -- Call Before Process User Hook
437 --
438 begin
439 ota_resource_booking_bk2.update_resource_booking_b (
440 p_effective_date => l_effective_date ,
441 p_resource_booking_id => p_resource_booking_id ,
442 p_object_version_number => l_object_version_number,
443 p_supplied_resource_id => p_supplied_resource_id ,
444 p_date_booking_placed => p_date_booking_placed ,
445 p_status => p_status ,
446 p_event_id => p_event_id ,
447 p_absolute_price => p_absolute_price ,
448 p_booking_person_id => p_booking_person_id ,
449 p_comments => p_comments ,
450 p_contact_name => p_contact_name ,
451 p_contact_phone_number => p_contact_phone_number,
452 p_delegates_per_unit => p_delegates_per_unit ,
453 p_quantity => p_quantity ,
454 p_required_date_from => p_required_date_from ,
455 p_required_date_to => p_required_date_to ,
456 p_required_end_time => p_required_end_time ,
457 p_required_start_time => p_required_start_time ,
458 p_deliver_to => p_deliver_to ,
459 p_primary_venue_flag => p_primary_venue_flag ,
460 p_role_to_play => p_role_to_play ,
461 p_trb_information_category => p_trb_information_category ,
462 p_trb_information1 => p_trb_information1 ,
463 p_trb_information2 => p_trb_information2 ,
464 p_trb_information3 => p_trb_information3 ,
465 p_trb_information4 => p_trb_information4 ,
466 p_trb_information5 => p_trb_information5 ,
467 p_trb_information6 => p_trb_information6 ,
468 p_trb_information7 => p_trb_information7 ,
469 p_trb_information8 => p_trb_information8 ,
470 p_trb_information9 => p_trb_information9 ,
471 p_trb_information10 => p_trb_information10 ,
472 p_trb_information11 => p_trb_information11 ,
473 p_trb_information12 => p_trb_information12 ,
474 p_trb_information13 => p_trb_information13 ,
475 p_trb_information14 => p_trb_information14 ,
476 p_trb_information15 => p_trb_information15 ,
477 p_trb_information16 => p_trb_information16 ,
478 p_trb_information17 => p_trb_information17 ,
479 p_trb_information18 => p_trb_information18 ,
480 p_trb_information19 => p_trb_information19 ,
481 p_trb_information20 => p_trb_information20
482 ,p_display_to_learner_flag => p_display_to_learner_flag
483 ,p_book_entire_period_flag => p_book_entire_period_flag
484 -- ,p_unbook_request_flag => p_unbook_request_flag
485 ,p_chat_id => p_chat_id
486 ,p_forum_id => p_forum_id
487 ,p_timezone_code => p_timezone_code
488 );
489
490 exception
491 when hr_api.cannot_find_prog_unit then
492 hr_api.cannot_find_prog_unit_error
493 (p_module_name => 'update_resource_booking'
494 ,p_hook_type => 'BP'
495 );
496 end;
497 --
498 -- Validation in addition to Row Handlers
499 --
500 --
501 -- Process Logic
502 --
503 ota_trb_upd.upd (
504 p_effective_date => l_effective_date ,
505 p_resource_booking_id => p_resource_booking_id ,
506 p_object_version_number => l_object_version_number,
507 p_supplied_resource_id => p_supplied_resource_id ,
508 p_date_booking_placed => p_date_booking_placed ,
509 p_status => p_status ,
510 p_event_id => p_event_id ,
511 p_absolute_price => p_absolute_price ,
512 p_booking_person_id => p_booking_person_id ,
513 p_comments => p_comments ,
514 p_contact_name => p_contact_name ,
515 p_contact_phone_number => p_contact_phone_number,
516 p_delegates_per_unit => p_delegates_per_unit ,
517 p_quantity => p_quantity ,
518 p_required_date_from => p_required_date_from ,
519 p_required_date_to => p_required_date_to ,
520 p_required_end_time => p_required_end_time ,
521 p_required_start_time => p_required_start_time ,
522 p_deliver_to => p_deliver_to ,
523 p_primary_venue_flag => p_primary_venue_flag ,
524 p_role_to_play => p_role_to_play ,
525 p_trb_information_category => p_trb_information_category ,
526 p_trb_information1 => p_trb_information1 ,
527 p_trb_information2 => p_trb_information2 ,
528 p_trb_information3 => p_trb_information3 ,
529 p_trb_information4 => p_trb_information4 ,
530 p_trb_information5 => p_trb_information5 ,
531 p_trb_information6 => p_trb_information6 ,
535 p_trb_information10 => p_trb_information10 ,
532 p_trb_information7 => p_trb_information7 ,
533 p_trb_information8 => p_trb_information8 ,
534 p_trb_information9 => p_trb_information9 ,
536 p_trb_information11 => p_trb_information11 ,
537 p_trb_information12 => p_trb_information12 ,
538 p_trb_information13 => p_trb_information13 ,
539 p_trb_information14 => p_trb_information14 ,
540 p_trb_information15 => p_trb_information15 ,
541 p_trb_information16 => p_trb_information16 ,
542 p_trb_information17 => p_trb_information17 ,
543 p_trb_information18 => p_trb_information18 ,
544 p_trb_information19 => p_trb_information19 ,
545 p_trb_information20 => p_trb_information20
546 ,p_display_to_learner_flag => p_display_to_learner_flag
547 ,p_book_entire_period_flag => p_book_entire_period_flag
548 -- ,p_unbook_request_flag => p_unbook_request_flag
549 ,p_chat_id => p_chat_id
550 ,p_forum_id => p_forum_id
551 ,p_timezone_code => p_timezone_code
552 );
553
554 --
555 -- Call After Process User Hook
556 --
557 begin
558 OTA_resource_booking_bk2.update_resource_booking_a (
559 p_effective_date => l_effective_date ,
560 p_resource_booking_id => p_resource_booking_id ,
561 p_object_version_number => l_object_version_number,
562 p_supplied_resource_id => p_supplied_resource_id ,
563 p_date_booking_placed => p_date_booking_placed ,
564 p_status => p_status ,
565 p_event_id => p_event_id ,
566 p_absolute_price => p_absolute_price ,
567 p_booking_person_id => p_booking_person_id ,
568 p_comments => p_comments ,
569 p_contact_name => p_contact_name ,
570 p_contact_phone_number => p_contact_phone_number,
571 p_delegates_per_unit => p_delegates_per_unit ,
572 p_quantity => p_quantity ,
573 p_required_date_from => p_required_date_from ,
574 p_required_date_to => p_required_date_to ,
575 p_required_end_time => p_required_end_time ,
576 p_required_start_time => p_required_start_time ,
577 p_deliver_to => p_deliver_to ,
578 p_primary_venue_flag => p_primary_venue_flag ,
579 p_role_to_play => p_role_to_play ,
580 p_trb_information_category => p_trb_information_category ,
581 p_trb_information1 => p_trb_information1 ,
582 p_trb_information2 => p_trb_information2 ,
583 p_trb_information3 => p_trb_information3 ,
584 p_trb_information4 => p_trb_information4 ,
585 p_trb_information5 => p_trb_information5 ,
586 p_trb_information6 => p_trb_information6 ,
587 p_trb_information7 => p_trb_information7 ,
588 p_trb_information8 => p_trb_information8 ,
589 p_trb_information9 => p_trb_information9 ,
590 p_trb_information10 => p_trb_information10 ,
591 p_trb_information11 => p_trb_information11 ,
592 p_trb_information12 => p_trb_information12 ,
593 p_trb_information13 => p_trb_information13 ,
594 p_trb_information14 => p_trb_information14 ,
595 p_trb_information15 => p_trb_information15 ,
596 p_trb_information16 => p_trb_information16 ,
597 p_trb_information17 => p_trb_information17 ,
598 p_trb_information18 => p_trb_information18 ,
599 p_trb_information19 => p_trb_information19 ,
600 p_trb_information20 => p_trb_information20
601 ,p_display_to_learner_flag => p_display_to_learner_flag
602 ,p_book_entire_period_flag => p_book_entire_period_flag
603 -- ,p_unbook_request_flag => p_unbook_request_flag
604 ,p_chat_id => p_chat_id
605 ,p_forum_id => p_forum_id
606 ,p_timezone_code => p_timezone_code
607 );
608
609 exception
610 when hr_api.cannot_find_prog_unit then
611 hr_api.cannot_find_prog_unit_error
612 (p_module_name => 'update_resource_booking'
613 ,p_hook_type => 'AP'
614 );
615 end;
616 --
617 -- When in validation only mode raise the Validate_Enabled exception
618 --
619 if p_validate then
620 raise hr_api.validate_enabled;
621 end if;
622 --
623 -- Set all output arguments
624 --
625 p_object_version_number := l_object_version_number;
626
627 if l_person_id is not null and l_event_id is not null and l_status <> p_status and p_status= 'C'then
628 -- call to instructor notification process on status change to confirmation
629 OTA_INITIALIZATION_WF.initialize_instructor_wf(
630 p_item_type => 'OTWF',
631 p_eventid => l_event_id,
632 p_res_book_id => p_resource_booking_id,
633 p_status => p_status,
634 p_event_fired => 'INST_BOOK_CONFIRM');
635
636 end if;
640 l_start_date_changed
637
638 -- Fire reminder notification to instructor on booking date change
639
641 := ota_general.value_changed(l_date_from,
642 p_required_date_from);
643
644 if l_person_id is not null and l_event_id is not null
645 and l_start_date_changed and l_date_from > trunc(sysdate)
646 and p_required_date_from <= (trunc(sysdate) + l_notify_days_before)
647 and (l_status = 'C' or p_status= 'C')
648 then
649
650 if (ota_utility.is_con_prog_periodic('OTINSTRNTF')) then
651 OTA_INITIALIZATION_WF.initialize_instructor_wf(
652 p_item_type => 'OTWF',
653 p_eventid => l_event_id,
654 p_res_book_id => p_resource_booking_id,
655 p_event_fired => 'INSTRUCTOR_REMIND');
656
657 end if;
658
659 end if;
660 --
661 hr_utility.set_location(' Leaving:'||l_proc, 70);
662 exception
663 when hr_api.validate_enabled then
664 --
665 -- As the Validate_Enabled exception has been raised
666 -- we must rollback to the savepoint
667 --
668 rollback to update_resource_booking ;
669 --
670 -- Only set output warning arguments
671 -- (Any key or derived arguments must be set to null
672 -- when validation only mode is being used.)
673 --
674 p_object_version_number := null;
675 hr_utility.set_location(' Leaving:'||l_proc, 80);
676 when others then
677 --
678 -- A validation or unexpected error has occured
679 --
680 rollback to update_resource_booking ;
681 hr_utility.set_location(' Leaving:'||l_proc, 90);
682 p_object_version_number := l_object_version_number;
683 raise;
684 end update_resource_booking ;
685
686 --
687 -- ----------------------------------------------------------------------------
688 -- |-------------------------< delete_resource_booking >----------------------|
689 -- ----------------------------------------------------------------------------
690 --
691 procedure delete_resource_booking
692 (
693 p_resource_booking_id in number,
694 p_object_version_number in number,
695 p_validate in boolean
696 ) is
697 --
698 -- Declare cursors and local variables
699 --
700 l_proc varchar2(72) := g_package||' delete_resource_booking ';
701
702 cursor get_res_info
703 is
704 select orb.required_date_From,orb.required_date_to,
705 orb.required_start_time,orb.required_end_time,
706 orb.status,orb.supplied_resource_id,orb.event_id
707 from ota_resource_bookings orb
708 where resource_booking_id = p_resource_booking_id;
709
710 cursor get_trainer_id (crs_sup_res_id number)is
711 select osr.trainer_id from ota_suppliable_resources osr
712 where
713 osr.resource_type ='T'
714 and osr.supplied_resource_id = crs_sup_res_id;
715
716 l_start_date varchar2(100);
717 l_end_date varchar2(100);
718 l_start_time ota_events.course_start_time%type;
719 l_end_time ota_events.course_start_time%type;
720 l_status varchar2(30);
721 l_event_id ota_events.event_id%type;
725 --
722 l_sup_res_id ota_resource_bookings.supplied_resource_id%type;
723 l_person_id per_people_f.person_id%type;
724 --
726 begin
727 hr_utility.set_location('Entering:'|| l_proc, 10);
728 --
729 -- Issue a savepoint
730 --
731 savepoint delete_resource_booking ;
732
733 --get the values req for ntf process
734
735 open get_res_info;
736 fetch get_res_info into l_start_date,l_end_date,l_start_time,l_end_time,
737 l_status,l_sup_res_id,l_event_id;
738 close get_res_info;
739
740 open get_trainer_id (l_sup_res_id);
741 fetch get_trainer_id into l_person_id;
742 close get_trainer_id;
743 --
744 -- Call Before Process User Hook
745 --
746 begin
747 OTA_resource_booking_bk3.delete_resource_booking_b
748 (p_resource_booking_id => p_resource_booking_id ,
749 p_object_version_number => p_object_version_number,
750 p_validate => p_validate);
751
752 exception
753 when hr_api.cannot_find_prog_unit then
754 hr_api.cannot_find_prog_unit_error
755 (p_module_name => 'delete_resource_booking'
756 ,p_hook_type => 'BP'
757 );
758 end;
759 --
760 -- Validation in addition to Row Handlers
761 --
762 --
763 -- Process Logic
764 --
765 OTA_trb_del.del
766 (p_resource_booking_id => p_resource_booking_id ,
767 p_object_version_number => p_object_version_number
768 ) ;
769 --
770 -- Call After Process User Hook
771 --
772 begin
773 OTA_resource_booking_bk3.delete_resource_booking_a
774 (p_resource_booking_id => p_resource_booking_id ,
775 p_object_version_number => p_object_version_number,
776 p_validate => p_validate);
777 exception
778 when hr_api.cannot_find_prog_unit then
779 hr_api.cannot_find_prog_unit_error
780 (p_module_name => 'delete_resource_booking'
781 ,p_hook_type => 'AP'
782 );
783 end;
784 --
785 -- When in validation only mode raise the Validate_Enabled exception
786 --
787 if p_validate then
788 raise hr_api.validate_enabled;
789 end if;
790 -- Set all output arguments
791 if l_person_id is not null and l_event_id is not null then
792
793 hr_utility.trace ('before wf ' ||20);
794 -- call to instructor notification process
795 OTA_INITIALIZATION_WF.initialize_instructor_wf(
796 p_item_type => 'OTWF',
797 p_eventid => l_event_id,
798 p_sup_res_id => l_sup_res_id,
799 p_start_date => l_start_date,
800 p_end_date => l_end_date,
801 p_start_time => l_start_time,
802 p_end_time => l_end_time,
803 p_status => l_status,
804 p_person_id => l_person_id,
805 p_event_fired => 'INSTRUCTOR_CANCEL');
806 hr_utility.trace ('after wf ' ||20);
807
808 end if;
809 --
810 -- Set all output arguments
811 --
812 --
813 hr_utility.set_location(' Leaving:'||l_proc, 170);
814 exception
815 when hr_api.validate_enabled then
816 --
817 -- As the Validate_Enabled exception has been raised
818 -- we must rollback to the savepoint
819 --
820 rollback to delete_resource_booking ;
821 --
822 -- Only set output warning arguments
823 -- (Any key or derived arguments must be set to null
824 -- when validation only mode is being used.)
825 --
826 hr_utility.set_location(' Leaving:'||l_proc, 180);
827 when others then
828 --
829 -- A validation or unexpected error has occured
830 --
831 rollback to delete_resource_booking ;
832 hr_utility.set_location(' Leaving:'||l_proc, 190);
833 raise;
834 end delete_resource_booking;
835 --
836 end ota_resource_booking_api;