[Home] [Help]
PACKAGE BODY: APPS.OTA_TRB_API_PROCEDURES
Source
1 Package Body ota_trb_api_procedures as
2 /* $Header: ottrb02t.pkb 120.18.12000000.4 2007/07/05 09:18:38 aabalakr noship $ */
3 --
4 g_package varchar2(33) := 'ota_trb_api_procedures.';
5 --
6 -- The business rules......
7 --
8 --
9 --
10 -- --------------------------------------------------------------------
11 -- |---------------------< check_resource_type >-----------------------
12 -- --------------------------------------------------------------------
13 -- PRIVATE
14 -- Description: This function returns a TRUE if the resource type is a
15 -- venue based upon the given supplied_resource_id. This is
16 -- only for use by procedures in this package.
17 --
18 function check_resource_type(p_supplied_resource_id in number,
19 p_type in varchar2)
20 return boolean is
21 --
22 l_proc varchar2(72) := g_package||'check_resource_type';
23 l_exists number;
24 l_return boolean;
25 --
26 -- cursor to make the check
27 --
28 cursor chk_type is
29 select 1
30 from ota_suppliable_resources
31 where supplied_resource_id = p_supplied_resource_id
32 and resource_type = p_type;
33 --
34 begin
35 hr_utility.set_location('Entering:'||l_proc,5);
36 --
37 open chk_type;
38 fetch chk_type into l_exists;
39 IF chk_type%found THEN
40 l_return := TRUE;
41 ELSE
42 l_return := FALSE;
43 END IF;
44 close chk_type;
45 --
46 return (l_return);
47 --
48 hr_utility.set_location(' Leaving:'||l_proc, 10);
49 end check_resource_type;
50 --
51 -- ---------------------------------------------------------------------
52 -- |------------------< check_number_delegates >------------------------
53 -- ---------------------------------------------------------------------
54 -- PUBLIC
55 -- Description: check number delegates
56 --
57 -- When event is not planned the number of delegates per
58 -- unit should be greater than or equal to max attendees
59 -- on the event or activity version.
60 --
61 -- This function returns FALSE if a warning needs to be
62 -- issued and TRUE otherwise.
63 --
64 /* function check_number_delegates(p_event_id IN NUMBER,
65 p_supplied_resource_id IN NUMBER)
66 return boolean is
67 --
68 l_proc varchar2(72) := g_package||'check_number_delegates';
69 l_plnd varchar2(30);
70 l_venue varchar2(30);
71 l_exists number;
72 l_event_id number := p_event_id;
73 l_parent_id number;
74 l_type varchar2(30);
75 l_return boolean;
76 --
77 -- cursor to check if event is PLANNED
78 --
79 cursor chk_pland is
80 select event_status
81 from ota_events
82 where event_id = l_event_id;
83 --
84 -- cursor to check if resource booking is for a SESSION
85 --
86 cursor chk_sess is
87 select event_type,parent_event_id
88 from ota_events
89 where event_id = l_event_id;
90 --
91 -- cursor to check numbers of delegates per unit
92 --
93 cursor chk_number is
94 select 1
95 from ota_suppliable_resources sr,
96 ota_events e,
97 ota_offerings off
98 where sr.supplied_resource_id = p_supplied_resource_id
99 and e.event_id = l_event_id
100 and off.offering_id = e.parent_offering_id --bug 3494404
101 and sr.delegates_per_unit >=
102 nvl(e.maximum_attendees,off.maximum_attendees);
103 --
104 begin
105 hr_utility.set_location('Entering:'||l_proc,5);
106 --
107 open chk_pland;
108 fetch chk_pland into l_plnd;
109 close chk_pland;
110 IF l_plnd IN ('F','N') THEN
111 IF check_resource_type(p_supplied_resource_id,
112 'V') THEN
113 open chk_sess;
114 fetch chk_sess into l_type,l_parent_id;
115 close chk_sess;
116 IF l_type = 'SESSION' THEN
117 l_event_id := l_parent_id;
118 END IF;
119 open chk_number;
120 fetch chk_number into l_exists;
121 IF chk_number%notfound THEN
122 close chk_number;
123 l_return := FALSE;
124 ELSE close chk_number;
125 l_return := TRUE;
126 END IF;
127 ELSE l_return := TRUE;
128 END IF;
129 ELSE l_return := TRUE;
130 END IF;
131 return (l_return);
132 --
133 hr_utility.set_location('Leaving:'||l_proc,10);
134 end check_number_delegates; */
135 --
136 -- ---------------------------------------------------------------------
137 -- |-------------------< check_role_to_play >---------------------------
138 -- ---------------------------------------------------------------------
139 -- PUBLIC
140 -- Description: The role_to_play field must be in the domain
141 -- Trainer Participation.
142 --
143 procedure check_role_to_play(p_role_to_play in varchar2) is
144 --
145 l_proc varchar2(72) := g_package||'check_role_to_play';
146 l_exists number;
147 --
148 -- cursor to perform check
149 --
150 cursor chk_roleplay is
151 select 1
152 from hr_lookups
153 where lookup_type = 'TRAINER_PARTICIPATION'
154 and lookup_code = p_role_to_play;
155 --
156 begin
157 hr_utility.set_location('Entering:'||l_proc,5);
158 --
159 open chk_roleplay;
160 fetch chk_roleplay into l_exists;
161 IF chk_roleplay%notfound THEN
162 fnd_message.set_name('OTA','OTA_13264_TRB_ROLE_TO_PLAY_TYP');
163 close chk_roleplay;
164 fnd_message.raise_error;
165 END IF;
166 close chk_roleplay;
167 --
168 hr_utility.set_location('Leaving:'||l_proc,10);
169 end check_role_to_play;
170 --
171 -- ---------------------------------------------------------------------
172 -- |------------------< check_role_res_type_excl >----------------------
173 -- ---------------------------------------------------------------------
174 -- PUBLIC
175 -- Description:
176 --
177 procedure check_role_res_type_excl(p_supplied_resource_id in number,
178 p_role_to_play in varchar2) is
179 --
180 l_proc varchar2(72) := g_package||'check_role_res_type_excl';
181 --
182 begin
183 hr_utility.set_location('Entering:'||l_proc,5);
184 --
185 IF p_role_to_play is not null THEN
186 IF not check_resource_type(p_supplied_resource_id,
187 'T') THEN
188 fnd_message.set_name('OTA','OTA_13264_TRB_ROLE_TO_PLAY_TYP');
189 fnd_message.raise_error;
190 END IF;
191 END IF;
192 --
193 hr_utility.set_location('Leaving:'||l_proc,10);
194 end check_role_res_type_excl;
195 --
196 -- ---------------------------------------------------------------------
197 -- |------------------------< get_total_cost >--------------------------
198 -- ---------------------------------------------------------------------
199 -- PUBLIC
200 -- Description: get the total cost of all resources required for the
201 -- event.
202 --
203 procedure get_total_cost(p_event_id in number,
204 p_total_cost in out nocopy number) is
205 --
206 l_proc varchar2(72) := g_package||'get_total_cost';
207 l_exists number;
208 l_tot number;
209 l_business_group_id ota_events.business_group_id%type; -- bug 4304067
210 --
211 -- cursor to perform check on currency codes
212 --
213 cursor chk_curr (p_business_group_id in number) is -- bug 4304067
214 select 1
215 from ota_events e
216 where business_group_id = p_business_group_id
217 and exists
218 (select 1
219 from ota_suppliable_resources sr,
220 ota_resource_bookings rb
221 where rb.event_id = e.event_id
222 and sr.business_group_id = p_business_group_id
223 and sr.supplied_resource_id = rb.supplied_resource_id
224 and sr.currency_code <> e.currency_code);
225
226 --
227 -- cursor to get business_group_id bug 4304067
228 --
229 cursor csr_business_group_id is
230 select e.business_group_id
231 from ota_events e
232 where e.event_id = p_event_id;
233
234 --
235 -- cursor to perform calculations
236 --
237 cursor get_rescost is
238 select sr.cost
239 from ota_suppliable_resources sr,
240 ota_events e,
241 ota_resource_bookings rb
242 where rb.event_id = p_event_id
243 and rb.status = 'C'
244 and sr.supplied_resource_id = rb.supplied_resource_id
245 and e.event_id = p_event_id;
246 --
247 begin
248 hr_utility.set_location('Entering:'||l_proc,5);
249 --
250
251 open csr_business_group_id ; --bug 4304067
252 fetch csr_business_group_id into l_business_group_id;
253 close csr_business_group_id;
254
255 open chk_curr(l_business_group_id);
256 fetch chk_curr into l_exists;
257 IF chk_curr%notfound THEN
258 close chk_curr;
259 FOR get_totcost IN get_rescost LOOP
260 l_tot := l_tot + get_totcost.cost;
261 END LOOP;
262 p_total_cost := l_tot;
263 ELSE close chk_curr;
264 END IF;
265 --
266 hr_utility.set_location('Leaving:'||l_proc,10);
267 end get_total_cost;
268 --
269 -- ---------------------------------------------------------------------
270 -- |--------------------< check_quantity_entered >----------------------
271 -- ---------------------------------------------------------------------
272 -- PUBLIC
273 -- Description: The quantity field must be set to 1 if the resource is
274 -- a venue of the person is a named trainer
275 --
276 procedure check_quantity_entered(p_supplied_resource_id in number,
277 p_quantity in number) is
278 --
279 l_proc varchar2(72) := g_package||'check_quantity_entered';
280 --l_person_id number;
281 --
282 --code commented out as person_id is no longer available from suppliable
283 --resources KLS 04/09/95
284 --
285 -- cursor to check person id
286 --
287 --cursor chk_per is
288 --select person_id
289 --from ota_suppliable_resources
290 --where supplied_resource_id = p_supplied_resource_id;
291 --
292 begin
293 hr_utility.set_location('Entering:'||l_proc,5);
294 --
295 if p_quantity > 1 then
296 if check_resource_type(p_supplied_resource_id,
297 'V') THEN
298 fnd_message.set_name('OTA','OTA_13265_TRB_QUANTITY_ENTERED');
299 fnd_message.raise_error;
300 elsif check_resource_type(p_supplied_resource_id,
301 'T') THEN
302 fnd_message.set_name('OTA','OTA_13265_TRB_QUANTITY_ENTERED');
303 fnd_message.raise_error;
304 else
305 null;
306 --open chk_per;
307 --fetch chk_per into l_person_id;
308 --close chk_per;
309 --IF l_person_id is not null THEN
310 --fnd_message.set_name('OTA','OTA_13265_TRB_QUANTITY_ENTERED');
311 --fnd_message.raise_error;
312 --end if;
313 end if;
314 end if;
315 --
316 hr_utility.set_location('Leaving:'||l_proc,10);
317 end check_quantity_entered;
318 --
319 -- ---------------------------------------------------------------------
320 -- |-------------------< check_delivery_address >-----------------------
321 -- ---------------------------------------------------------------------
322 -- PUBLIC
323 -- Description: Delivery address cannot be entered if resource type is
324 -- VENUE or TRAINER.
325 --
326 procedure check_delivery_address(p_supplied_resource_id in number,
327 p_del_add in varchar2) is
328 --
329 l_proc varchar2(72) := g_package||'check_delivery_address';
330 l_exists number;
331 --
332 cursor chk_type is
333 select 1
334 from ota_suppliable_resources sr,
335 hr_lookups l
336 where sr.supplied_resource_id = p_supplied_resource_id
337 and sr.resource_type = l.lookup_code
338 and l.lookup_type in ('VENUE','TRAINER');
339 --
340 begin
341 hr_utility.set_location('Entering:'||l_proc,5);
342 --
343 open chk_type;
344 fetch chk_type into l_exists;
345 IF l_exists = '1' AND p_del_add is not null THEN
346 fnd_message.set_name('OTA','OTA_13266_TRB_DELIVERY_ADDRESS');
347 close chk_type;
348 fnd_message.raise_error;
349 END IF;
350 close chk_type;
351 --
352 hr_utility.set_location('Leaving:'||l_proc,10);
353 end check_delivery_address;
354 --
355 -- ---------------------------------------------------------------------
356 -- |------------------------< get_resource_booking_id >-----------------
357 -- ---------------------------------------------------------------------
358 -- PUBLIC
359 -- Description: Return the RESOURCE BOOKING ID when supplied with only
360 -- the SUPPLIED RESOURCE ID and EVENT ID.
361 --
362 function get_resource_booking_id(p_supplied_resource_id in number,
363 p_event_id in number)
364 return number is
365 --
366 l_proc varchar2(72) := g_package||'get_resource_booking_id';
367 l_resbook_id number;
368 --
369 -- cursor to retrieve booking id
370 --
371 cursor get_book is
372 select resource_booking_id
373 from ota_resource_bookings
374 where supplied_resource_id = p_supplied_resource_id
375 and event_id = p_event_id;
376 --
377 begin
378 hr_utility.set_location('Entering:'||l_proc,5);
379 --
380 open get_book;
381 fetch get_book into l_resbook_id;
382 IF get_book%notfound THEN
383 fnd_message.set_name('OTA','OTA_13267_TRB_BOOKING_ID');
384 close get_book;
385 fnd_message.raise_error;
386 END IF;
387 close get_book;
388 --
389 return (l_resbook_id);
390 --
391 hr_utility.set_location('Leaving:'||l_proc,10);
392 end get_resource_booking_id;
393 --
394 -- ---------------------------------------------------------------------
395 -- |-----------------------< resource_booked_for_event >----------------
396 -- ---------------------------------------------------------------------
397 -- PUBLIC
398 -- Description: Return a TRUE if any resource bookings have been made
399 -- for the specified event. Otherwise return a FALSE.
400 --
401 function resource_booked_for_event(p_event_id in number)
402 return boolean is
403 --
404 l_proc varchar2(72) := g_package||'resource_booked_for_event';
405 l_exists number;
406 l_return boolean;
407 --
408 -- cursor to perform check
409 --
410 cursor chk_bookings is
411 select 1
412 from ota_resource_bookings
413 where event_id = p_event_id;
414 --
415 begin
416 hr_utility.set_location('Entering:'||l_proc,5);
417 --
418 open chk_bookings;
419 fetch chk_bookings into l_exists;
420 IF chk_bookings%found THEN l_return := TRUE;
421 ELSE l_return := FALSE;
422 END IF;
423 close chk_bookings;
424 --
425 return (l_return);
426 --
427 hr_utility.set_location('Leaving:'||l_proc,10);
428 end resource_booked_for_event;
429
430 -- ---------------------------------------------------------------------
431 -- |----------------------------< check_obj_booking_dates >---------------------
432 -- ---------------------------------------------------------------------
433 -- PUBLIC
434 -- Description: Required dates must be within boudaries of suppliable
435 -- resource validity dates.
436 --
437 procedure check_obj_booking_dates(p_supplied_resource_id in number,
438 p_req_from in date,
439 p_req_to in date,
440 p_event_id in number,
441 p_chat_id in number,
442 p_forum_id in number,
443 p_timezone_code in varchar2,
444 p_req_time_from in varchar2,
445 p_req_time_to in varchar2,
446 p_warning out nocopy varchar2) is
447 --
448 l_proc varchar2(72) := g_package||'check_obj_booking_dates';
449 l_exists number;
450
451 --cursor to get dates of object
452
453 cursor get_event_dates is
454 select course_start_date,course_end_date,event_type,timezone,course_start_time,course_end_time
455 from ota_events
456 where event_id = p_event_id;
457
458 cursor get_forum_dates is
459 select start_date_active,end_date_active
460 from ota_forums_b
461 where forum_id = p_forum_id;
462
463 cursor get_chat_dates is
464 select start_date_active,end_date_active,timezone_code
465 from ota_chats_b
466 where chat_id = p_chat_id;
467 --
468 -- cursor to check date ranges of resource being booked
469 --
470
471 cursor chk_dates_tsr(crs_start_date date,crs_end_date date,crs_timezone varchar2, crs_start_time varchar2, crs_end_time varchar2) is
472 select 1
473 from dual
474 where decode(crs_start_date, null, ota_timezone_util.convert_date(p_req_from, nvl(p_req_time_from,'23:59'), p_timezone_code,crs_timezone), to_date(to_char(to_char(crs_start_date,'dd-mm-yyyy')||' '||nvl(crs_start_time,'23:59')), 'dd-mm-yyyy HH24:MI'))
475 >= ota_timezone_util.convert_date(p_req_from, nvl(p_req_time_from,'23:59'), p_timezone_code,crs_timezone)
476 --to_date(to_char(to_char(crs_start_date,'dd-mm-yyyy')||' '||nvl(crs_start_time,'00:00')), 'dd-mm-yyyy HH24:MI') <= ota_timezone_util.convert_date(p_req_from, nvl(p_req_time_from,'00:00'), p_timezone_code,crs_timezone)
477 and decode(crs_end_date, null, ota_timezone_util.convert_date(p_req_to, nvl(p_req_time_to,'23:59'), p_timezone_code,crs_timezone), to_date(to_char(to_char(crs_end_date,'dd-mm-yyyy')||' '||nvl(crs_end_time,'23:59')), 'dd-mm-yyyy HH24:MI'))
478 >= ota_timezone_util.convert_date(p_req_to, nvl(p_req_time_to,'23:59'), p_timezone_code,crs_timezone);
479
480
481 l_start_date date;
482 l_end_date date;
483 l_start_time varchar2(10) := null;
484 l_end_time varchar2(10) :=null;
485 l_token varchar2(20);
486 l_event_type varchar2(20);
487 l_timezone varchar2(30) := null;
488 l_id_passed boolean := false;
489 --
490 begin
491 hr_utility.set_location('Entering:'||l_proc,5);
492
493 if p_event_id is not null then
494 l_id_passed :=true;
495 open get_event_dates;
496 fetch get_event_dates into l_start_date,l_end_date,l_event_type,l_timezone,l_start_time,l_end_time;
497 close get_event_dates;
498 if l_event_type <> 'SESSION' then
499 l_token := ota_utility.get_lookup_meaning('OTA_OBJECT_TYPE','CL',810);
500 else
501 l_token := ota_utility.get_lookup_meaning('OTA_OBJECT_TYPE','S',810);
502 end if;
503
504 elsif p_chat_id is not null then
505 l_id_passed :=true;
506 open get_chat_dates;
507 fetch get_chat_dates into l_start_date,l_end_date,l_timezone;
508 close get_chat_dates;
509 l_token := ota_utility.get_lookup_meaning('OTA_OBJECT_TYPE','CHT',810);
510 elsif p_forum_id is not null then
511 l_id_passed :=true;
512 open get_forum_dates;
513 fetch get_forum_dates into l_start_date,l_end_date;
514 close get_forum_dates;
515 l_token := ota_utility.get_lookup_meaning('OTA_OBJECT_TYPE','FRM',810);
516 end if;
517 --
518 p_warning := 'N';
519
520 if l_id_passed then
521 open chk_dates_tsr(l_start_date,l_end_date,l_timezone,l_start_time,l_end_time);
522 fetch chk_dates_tsr into l_exists;
523 IF chk_dates_tsr%notfound THEN
524 p_warning := 'Y';
525 end if;
526 close chk_dates_tsr;
527 end if;
528 --
529 hr_utility.set_location('Leaving:'||l_proc,10);
530 end check_obj_booking_dates;
531 --
532 -- ---------------------------------------------------------------------
533 -- |----------------------------< check_dates_tsr >---------------------
534 -- ---------------------------------------------------------------------
535 -- PUBLIC
536 -- Description: Required dates must be within boudaries of suppliable
537 -- resource validity dates.
538 --
539 procedure check_dates_tsr(p_supplied_resource_id in number,
540 p_req_from in date,
541 p_req_to in date,
542 p_req_start_time in varchar2,
543 p_req_end_time in varchar2,
544 p_timezone_code in varchar2) is
545 --
546 l_proc varchar2(72) := g_package||'check_dates_tsr';
547 l_exists number;
548 --
549 -- cursor to check date ranges
550 --
551 cursor chk_dates_tsr is
552 select 1
553 from ota_suppliable_resources
554 where supplied_resource_id = p_supplied_resource_id
555 and start_date <= ota_timezone_util.convert_date(p_req_from, p_req_start_time, p_timezone_code, ota_timezone_util.get_server_timezone_code)
556 and decode(end_date, null, ota_timezone_util.convert_date(p_req_to, p_req_end_time, p_timezone_code, ota_timezone_util.get_server_timezone_code), end_date)
557 >= ota_timezone_util.convert_date(p_req_to, p_req_end_time, p_timezone_code, ota_timezone_util.get_server_timezone_code);
558
559
560 /* commented for bug6078493
561 cursor chk_dates_tsr is
562 select 1
563 from ota_suppliable_resources
564 where supplied_resource_id = p_supplied_resource_id
565 and start_date <= ota_timezone_util.convert_date(p_req_from, null, p_timezone_code, ota_timezone_util.get_server_timezone_code)
566 and decode(end_date, null, ota_timezone_util.convert_date(p_req_to, null, p_timezone_code, ota_timezone_util.get_server_timezone_code), end_date)
567 >= ota_timezone_util.convert_date(p_req_to, null, p_timezone_code, ota_timezone_util.get_server_timezone_code);
568 */
569 /*
570 cursor chk_dates_tsr is
571 select 1
572 from ota_suppliable_resources
573 where supplied_resource_id = p_supplied_resource_id
574 and start_date <= p_req_from
575 and nvl(end_date,nvl(p_req_to,hr_api.g_eot)) >= nvl(p_req_to,hr_api.g_eot);
576 */
577
578 --
579 begin
580 hr_utility.set_location('Entering:'||l_proc,5);
581 --
582 open chk_dates_tsr;
583 fetch chk_dates_tsr into l_exists;
584 IF chk_dates_tsr%notfound THEN
585 fnd_message.set_name('OTA','OTA_13269_TRB_REQUIRED_DATES');
586 close chk_dates_tsr;
587 fnd_message.raise_error;
588 END IF;
589 close chk_dates_tsr;
590 --
591 hr_utility.set_location('Leaving:'||l_proc,10);
592 end check_dates_tsr;
593 --
594 -- ---------------------------------------------------------------------
595 -- |-------------------------< check_evt_tsr_bus_grp >------------------
596 -- ---------------------------------------------------------------------
597 -- PUBLIC
598 -- Description: The events business group id must be the same as that of
599 -- the suppliable resource.
600 --
601 -- NB: This business rule may well disappear in the future as the
602 -- structure of organizations for OTA may change with the
603 -- addition of a VENDORS table. Suppliable Resources table may
604 -- well then contain a business_group_id column anyway.
605 -- KLS 24/11/94.
606 --
607 procedure check_evt_tsr_bus_grp(p_event_id in number,
608 p_supplied_resource_id in number) is
609 --
610 l_proc varchar2(72) := g_package||'check_evt_tsr_bus_grp';
611 l_exists number;
612 --
613 -- cursor to check business group id's
614 --
615 cursor chk_bgroup is
616 select 1
617 from ota_events e,
618 ota_suppliable_resources sr
619 where sr.supplied_resource_id = p_supplied_resource_id
620 and e.event_id = p_event_id
621 and sr.business_group_id = e.business_group_id;
622 --
623 begin
624 hr_utility.set_location('Entering:'||l_proc,5);
625 --
626 open chk_bgroup;
627 fetch chk_bgroup into l_exists;
628 IF chk_bgroup%notfound THEN
629 fnd_message.set_name('OTA','OTA_13270_BUS_GROUP_EQUAL');
630 close chk_bgroup;
631 fnd_message.raise_error;
632 END IF;
633 close chk_bgroup;
634 --
635 hr_utility.set_location('Leaving:'||l_proc,10);
636 end check_evt_tsr_bus_grp;
637 --
638 -- ---------------------------------------------------------------------
639 -- |---------------------------< check_from_to_dates >------------------
640 -- ---------------------------------------------------------------------
641 -- PUBLIC
642 -- Description: required date from must be less than or equal to the
643 -- required date to.
644 --
645 procedure check_from_to_dates(p_req_from in date,
646 p_req_to in date) is
647 --
648 l_proc varchar2(72) := g_package||'check_from_to_dates';
649 --
650 begin
651 hr_utility.set_location('Entering:'||l_proc,5);
652 --
653 IF p_req_from <= nvl(p_req_to,hr_api.g_eot) THEN null;
654 ELSE fnd_message.set_name('OTA','OTA_13271_TRB_FROM_TO_DATES');
655 fnd_message.raise_error;
656 END IF;
657 --
658 hr_utility.set_location('Leaving:'||l_proc,10);
659 end check_from_to_dates;
660 --
661 -- ---------------------------------------------------------------------
662 -- |----------------------------< check_update_tra >--------------------
663 -- ---------------------------------------------------------------------
664 -- PUBLIC
665 -- Description: Update of required dates must not invalidate any
666 -- resource allocations.
667 --
668 procedure check_update_tra(p_resource_booking_id in number,
669 p_req_date_from in date,
670 p_req_date_to in date) is
671 --
672 l_proc varchar2(72) := g_package||'check_update_tra';
673 l_exists number;
674 --
675 -- cursor to perform the check
676 --
677 cursor chk_dates is
678 select 1
679 from ota_resource_allocations
680 where (trainer_resource_booking_id = p_resource_booking_id
681 OR equipment_resource_booking_id = p_resource_booking_id)
682 and (start_date < p_req_date_from OR end_date > p_req_date_to);
683 --
684 begin
685 hr_utility.set_location('Entering:'||l_proc,5);
686 --
687 open chk_dates;
688 fetch chk_dates into l_exists;
689 IF chk_dates%found THEN
690 fnd_message.set_name('OTA','OTA_13278_TRB_CHECK_TRA_DATES');
691 close chk_dates;
692 fnd_message.raise_error;
693 END IF;
694 close chk_dates;
695 --
696 hr_utility.set_location('Leaving:'||l_proc,10);
697 end check_update_tra;
698 --
699 -- ---------------------------------------------------------------------
700 -- |------------------------< check_tra_trainer_exists >----------------
701 -- ---------------------------------------------------------------------
702 -- PUBLIC
703 -- Description: A resource booking may not be deleted if a row exists
704 -- in OTA_RESOURCE_ALLOCATIONS with this resource booking
705 -- id.
706 --
707 procedure check_tra_trainer_exists(p_resource_booking_id in number) is
708 --
709 l_proc varchar2(72) := g_package||'check_tra_trainer_exists';
710 l_exists number;
711 --
712 -- cursor to perform check
713 --
714 cursor chk_trn is
715 select 1
716 from ota_resource_allocations
717 where trainer_resource_booking_id = p_resource_booking_id;
718 --
719 begin
720 hr_utility.set_location('Entering:'||l_proc,5);
721 --
722 open chk_trn;
723 fetch chk_trn into l_exists;
724 IF chk_trn%found THEN
725 fnd_message.set_name('OTA','OTA_13272_TRB_ALLOCATION_EXIST');
726 close chk_trn;
727 fnd_message.raise_error;
728 END IF;
729 close chk_trn;
730 --
731 hr_utility.set_location('Leaving:'||l_proc,10);
732 end check_tra_trainer_exists;
733 --
734 -- ---------------------------------------------------------------------
735 -- |--------------------< check_tra_resource_exists >-------------------
736 -- ---------------------------------------------------------------------
737 -- PUBLIC
738 -- Description: A resource booking may not be deleted if in use as a
739 -- EQUIPMENT_RESOURCE_BOOKING_ID in
740 -- OTA_RESOURCE_ALLOCATIONS.
741 --
742 procedure check_tra_resource_exists(p_resource_booking_id in number) is
743 --
744 l_proc varchar2(72) := g_package||'check_tra_resource_exists';
745 l_exists number;
746 --
747 -- cursor to perform check
748 --
749 cursor chk_res is
750 select 1
751 from ota_resource_allocations
752 where equipment_resource_booking_id = p_resource_booking_id;
753 --
754 begin
755 hr_utility.set_location('Entering:'||l_proc,5);
756 --
757 open chk_res;
758 fetch chk_res into l_exists;
759 IF chk_res%found THEN
760 fnd_message.set_name('OTA','OTA_13272_TRB_ALLOCATION_EXIST');
761 close chk_res;
762 fnd_message.raise_error;
763 END IF;
764 close chk_res;
765 --
766 hr_utility.set_location('Leaving:'||l_proc,10);
767 end check_tra_resource_exists;
768 --
769 -- ---------------------------------------------------------------------
770 -- |------------------------< check_status >----------------------------
771 -- ---------------------------------------------------------------------
772 -- PUBLIC
773 -- Description: The user status must be in the domain RESOURCE BOOKING
774 -- STATUS.
775 --
776 procedure check_status(p_status in varchar2) is
777 --
778 l_proc varchar2(72) := g_package||'check_status';
779 l_exists number;
780 --
781 -- cursor to perform check
782 --
783 cursor chk_status is
784 select 1
785 from hr_lookups l
786 where lookup_type = 'RESOURCE_BOOKING_STATUS'
787 and lookup_code = p_status;
788 --
789 begin
790 hr_utility.set_location('Entering:'||l_proc,5);
791 --
792 open chk_status;
793 fetch chk_status into l_exists;
794 IF chk_status%notfound THEN
795 fnd_message.set_name('OTA','OTA_13204_GEN_INVALID_LOOKUP');
796 fnd_message.set_token('FIELD','Status');
797 fnd_message.set_token('LOOKUP_TYPE','RESOURCE_BOOKING_STATUS');
798 close chk_status;
799 fnd_message.raise_error;
800 END IF;
801 close chk_status;
802 --
803 hr_utility.set_location('Leaving:'||l_proc,10);
804 end check_status;
805 --
806 -- ---------------------------------------------------------------------
807 -- |-----------------------< check_status_value >-----------------------
808 -- ---------------------------------------------------------------------
809 -- PUBLIC
810 -- Description: If status is confirmed then check that its valid to have
811 -- confirmed resource bookings against this event.
812 --
813 procedure check_status_value is
814 --
815 l_proc varchar2(72) := g_package||'check_status_value';
816 --
817 -- left for now until API for events entity written so code can be
818 -- shared. KLS 25/11/94.
819 --
820 begin
821 hr_utility.set_location('Entering:'||l_proc,5);
822 --
823 null;
824 --
825 hr_utility.set_location('Leaving:'||l_proc,10);
826 end check_status_value;
827 --
828 -- ---------------------------------------------------------------------
829 -- |---------------------< check_primary_venue >------------------------
830 -- ---------------------------------------------------------------------
831 -- PUBLIC
832 -- Description: Only one venue resource booking may be a primary venue.
833 --
834 procedure check_primary_venue(p_event_id in number,
835 p_resource_booking_id in number,
836 p_prim_ven in varchar2,
837 p_req_from in date,
838 p_req_to in date) is
839 --
840 l_proc varchar2(72) := g_package||'check_primary_venue';
841 l_exists number;
842 --
843 -- cursor to perform check
844 --
845 cursor chk_primven is
846 select 1
847 from ota_resource_bookings rb,
848 ota_events e,
849 ota_offerings off
850 where e.event_id = rb.event_id
851 and off.offering_id = e.parent_offering_id --bug 3494404
852 and ((rb.required_date_from between
853 p_req_from and p_req_to)
854 or (rb.required_date_to between
855 p_req_from and p_req_to))
856 and rb.primary_venue_flag = 'Y'
857 and rb.event_id = p_event_id
858 and rb.resource_booking_id <> nvl(p_resource_booking_id, -1);
859 --
860 begin
861 hr_utility.set_location('Entering:'||l_proc,5);
862 --
863 IF p_prim_ven = 'Y' THEN
864 open chk_primven;
865 fetch chk_primven into l_exists;
866 IF chk_primven%found THEN
867 fnd_message.set_name('OTA','OTA_13273_TRB_PRIMARY_VENUE');
868 close chk_primven;
869 fnd_message.raise_error;
870 END IF;
871 close chk_primven;
872 END IF;
873 --
874 hr_utility.set_location('Leaving:'||l_proc,10);
875 end check_primary_venue;
876
877 -- ---------------------------------------------------------------------
878 -- |--------------------< check_booking_conflict >-----------------------|
879 -- ---------------------------------------------------------------------
880 -- PUBLIC
881 -- Description: Function returning Y if is another CONFIRMEDor Planned booking
882 -- for the resource is found
883 --
884 --
885 function check_booking_conflict(p_supplied_resource_id in number
886 ,p_required_date_from in Date
887 ,p_required_start_time in varchar2
888 ,p_required_date_to in Date
889 ,p_required_end_time in varchar2
890 ,p_timezone in varchar2
891 ,p_resource_booking_id in number
892 ,p_book_entire_period_flag in varchar2
893 )return varchar2 IS
894 --
895 l_proc varchar2(72) := g_package||'check_booking_conflict';
896 l_exists number;
897 l_book_entire_period varchar2(1);
898
899
900
901 l_resource_type varchar2(30);
902
903 l_return_value varchar2(1) := 'N';
904
905 cursor get_resource_type is
906 select resource_type
907 from ota_suppliable_resources
908 where supplied_resource_id = p_supplied_resource_id;
909
910
911 -- For entire duration flag null or N
912 cursor double_booking is
913 select 1
914 from ota_resource_bookings trb
915 where trb.supplied_resource_id = p_supplied_resource_id
916 and (
917 (p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))
918 and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
919 and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
920 and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
921 )
922 /*or
923 (to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
924 || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
925 and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
926 || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
927 )*/)
928 and (p_resource_booking_id is null
929 or (p_resource_booking_id is not null
930 and p_resource_booking_id <> trb.resource_booking_id));
931
932
933
934 Cursor csr_chk_date_overlap is
935 select Book_entire_period_flag,required_end_time,required_start_time,
936 required_date_from,required_date_to,timezone_code
937 from ota_resource_bookings trb
938 where trb.supplied_resource_id = p_supplied_resource_id
939 and (
940 (p_required_date_from between
941 trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
942 and
943 trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
944 or
945 (p_required_date_to between
946 trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
947 and
948 trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
949 or
950 ((p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)))
951 and
952 (p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))))
953 )
954 --and trb.status = 'C'
955 and (p_resource_booking_id is null
956 or (p_resource_booking_id is not null
957 and p_resource_booking_id <> trb.resource_booking_id));
958
959
960
961 --
962 begin
963 hr_utility.set_location('Entering:'||l_proc,5);
964
965
966 --
967 open get_resource_type;
968 fetch get_resource_type into l_resource_type;
969 close get_resource_type;
970
971 if(l_resource_type = 'T' or l_resource_type = 'V') then
972
973
974 open double_booking;
975 fetch double_booking into l_exists;
976
977 if double_booking%found then
978 --
979 close double_booking;
980 l_return_value :='Y';
981 return l_return_value;
982 else
983 close double_booking;
984
985 for rec in csr_chk_date_overlap
986 loop
987
988 /*Fetch csr_chk_trainer_date_overlap into l_book_entire_period;
989 if csr_chk_trainer_date_overlap%NotFound then
990 close csr_chk_trainer_date_overlap;
991 --No date overlap
992 return FALSE;
993 else
994 close csr_chk_trainer_date_overlap;*/
995 --Date overlap present
996 -- Check new or existing either one is book enire period Y
997
998 if ((p_required_date_from <> p_required_date_to)
999 and (rec.required_date_from <> rec.required_date_to)) then
1000 if rec.book_entire_period_flag = 'Y' or p_book_entire_period_flag = 'Y' then
1001 --check time overlap
1002 if (
1003 (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1004 and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1005 or
1006 (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1007 and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1008 ) then
1009
1010 l_return_value :='N';
1011 --return l_return_value;
1012 else
1013 l_return_value :='Y';
1014 return l_return_value;
1015 end if;
1016
1017 end if;
1018 --bug 5152139
1019 elsif(
1020 (p_required_date_from = p_required_date_to and rec.book_entire_period_flag = 'Y')
1021 or (rec.required_date_from = rec.required_date_to and p_book_entire_period_flag = 'Y')
1022 ) then
1023
1024 --since first cursor didn't give problem this means new and old record dates cannot be equal
1025 --and time
1026 if(
1027 (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone)) and
1028 nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1029 or
1030 (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)) and
1031 nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
1032 )
1033 ) then
1034
1035 l_return_value :='N';
1036
1037
1038 else
1039
1040 l_return_value :='Y';
1041 return l_return_value;
1042 end if;
1043 --bug 5116223
1044 elsif((p_required_date_from = p_required_date_to or rec.required_date_from = rec.required_date_to) and rec.timezone_code <> p_timezone ) then
1045 if(trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1046 <> trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))) then
1047 if((to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1048 || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
1049 and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1050 || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1051 ) then
1052 l_return_value :='Y';
1053 return l_return_value;
1054 end if;
1055 end if;
1056 end if;
1057 end loop;
1058
1059
1060 end if;
1061 else
1062 return l_return_value;
1063 end if; -- for 'T' or 'V'
1064
1065 --close double_booking;
1066 return l_return_value;
1067 --
1068 hr_utility.set_location('Leaving:'||l_proc,10);
1069
1070 end check_booking_conflict;
1071
1072
1073
1074 -- ---------------------------------------------------------------------
1075 -- |--------------------< is_booking_conflict >-----------------------|
1076 -- ---------------------------------------------------------------------
1077 -- PUBLIC
1078 -- Description: Function returning Y if is another CONFIRMEDor Planned booking
1079 -- for the resource is found
1080 --
1081 --
1082 function is_booking_conflict(p_supplied_resource_id in number
1083 ,p_required_date_from in Date
1084 ,p_required_start_time in varchar2
1085 ,p_required_date_to in Date
1086 ,p_required_end_time in varchar2
1087 ,p_timezone in varchar2
1088 ,p_target_resource_booking_id in number
1089 ,p_book_entire_period_flag in varchar2
1090 )return varchar2 IS
1091 --
1092 l_proc varchar2(72) := g_package||'is_booking_conflict';
1093 l_exists number;
1094 l_book_entire_period varchar2(1);
1095
1096 /*p_required_date_from Date := to_date(param_required_date_from,g_date_format);
1097 p_required_date_to Date := to_date(param_required_date_to,g_date_format);
1098 p_required_start_time varchar2(50) := param_required_start_time;
1099 p_required_end_time varchar2(50) := param_required_end_time;*/
1100
1101 l_resource_type varchar2(30);
1102
1103 l_return_value varchar2(1) := 'N';
1104
1105 cursor get_resource_type is
1106 select resource_type
1107 from ota_suppliable_resources
1108 where supplied_resource_id = p_supplied_resource_id;
1109
1110
1111 -- For entire duration flag null or N
1112 cursor double_booking is
1113 select 1
1114 from ota_resource_bookings trb
1115 where trb.supplied_resource_id = p_supplied_resource_id
1116 and (
1117 (p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))
1118 and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1119 and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
1120 and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
1121 )
1122 /*or
1123 (to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1124 || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
1125 and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1126 || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
1127 )*/)
1128 and trb.resource_booking_id = p_target_resource_booking_id;
1129
1130
1131
1132 Cursor csr_chk_date_overlap is
1133 select Book_entire_period_flag,required_end_time,required_start_time,
1134 required_date_from,required_date_to,timezone_code
1135 from ota_resource_bookings trb
1136 where trb.supplied_resource_id = p_supplied_resource_id
1137 and (
1138 (p_required_date_from between
1139 trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1140 and
1141 trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
1142 or
1143 (p_required_date_to between
1144 trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1145 and
1146 trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
1147 or
1148 ((p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)))
1149 and
1150 (p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))))
1151 )
1152 and trb.resource_booking_id = p_target_resource_booking_id;
1153
1154
1155
1156 --
1157 begin
1158 hr_utility.set_location('Entering:'||l_proc,5);
1159
1160
1161 --
1162 open get_resource_type;
1163 fetch get_resource_type into l_resource_type;
1164 close get_resource_type;
1165
1166 if(l_resource_type = 'T' or l_resource_type = 'V') then
1167
1168
1169 open double_booking;
1170 fetch double_booking into l_exists;
1171
1172 if double_booking%found then
1173 --
1174 close double_booking;
1175 l_return_value :='Y';
1176 return l_return_value;
1177 else
1178 close double_booking;
1179
1180 for rec in csr_chk_date_overlap
1181 loop
1182
1183 /*Fetch csr_chk_trainer_date_overlap into l_book_entire_period;
1184 if csr_chk_trainer_date_overlap%NotFound then
1185 close csr_chk_trainer_date_overlap;
1186 --No date overlap
1187 return FALSE;
1188 else
1189 close csr_chk_trainer_date_overlap;*/
1190 --Date overlap present
1191 -- Check new or existing either one is book enire period Y
1192
1193 if ((p_required_date_from <> p_required_date_to)
1194 and (rec.required_date_from <> rec.required_date_to)) then
1195 if rec.book_entire_period_flag = 'Y' or p_book_entire_period_flag = 'Y' then
1196 --check time overlap
1197 if (
1198 (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1199 and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1200 or
1201 (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1202 and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1203 ) then
1204
1205 l_return_value :='N';
1206 --return l_return_value;
1207 else
1208 l_return_value :='Y';
1209 return l_return_value;
1210 end if;
1211
1212 end if;
1213
1214 --bug 5152139
1215 elsif(
1216 (p_required_date_from = p_required_date_to and rec.book_entire_period_flag = 'Y')
1217 or (rec.required_date_from = rec.required_date_to and p_book_entire_period_flag = 'Y')
1218 ) then
1219
1220 --since first cursor didn't give problem this means new and old record dates cannot be equal
1221 --and time
1222 if(
1223 (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone)) and
1224 nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1225 or
1226 (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)) and
1227 nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
1228 )
1229 ) then
1230
1231 l_return_value :='N';
1232
1233
1234 else
1235
1236 l_return_value :='Y';
1237 return l_return_value;
1238 end if;
1239 --bug 5116223
1240 elsif((p_required_date_from = p_required_date_to or rec.required_date_from = rec.required_date_to )and rec.timezone_code <> p_timezone) then
1241 if(trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1242 <> trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))) then
1243 if((to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1244 || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
1245 and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1246 || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1247 ) then
1248 l_return_value :='Y';
1249 return l_return_value;
1250 end if;
1251 end if;
1252 end if;
1253 end loop;
1254
1255
1256 end if;
1257 else
1258 return l_return_value;
1259 end if; -- for 'T' or 'V'
1260
1261 --close double_booking;
1262 return l_return_value;
1263 --
1264 hr_utility.set_location('Leaving:'||l_proc,10);
1265
1266 end is_booking_conflict;
1267
1268
1269 function check_SS_double_booking(p_supplied_resource_id in number
1270 ,p_required_date_from in date
1271 ,p_required_start_time in varchar2
1272 ,p_required_date_to in date
1273 ,p_required_end_time in varchar2
1274 ,p_resource_booking_id in number
1275 ,p_book_entire_period_flag in varchar2
1276 ,p_timezone in varchar2
1277 )return varchar2 IS
1278
1279 l_return_value varchar2(5):='N';
1280 begin
1281
1282 if check_double_booking (p_supplied_resource_id
1283 ,p_required_date_from
1284 ,p_required_start_time
1285 ,p_required_date_to
1286 ,p_required_end_time
1287 ,p_resource_booking_id
1288 ,p_book_entire_period_flag
1289 ,p_timezone ) then
1290 l_return_value := 'Y';
1291 end if;
1292
1293 return l_return_value;
1294 end check_SS_double_booking;
1295 --
1296
1297 --
1298 -- ---------------------------------------------------------------------
1299 -- |--------------------< check_double_booking >-----------------------|
1300 -- ---------------------------------------------------------------------
1301 -- PUBLIC
1302 -- Description: Function returning TRUE is another CONFIRMED booking for the
1303 -- resource is found
1304 --
1305 --
1306 function check_double_booking(p_supplied_resource_id in number
1307 ,p_required_date_from in date
1308 ,p_required_start_time in varchar2
1309 ,p_required_date_to in date
1310 ,p_required_end_time in varchar2
1311 ,p_resource_booking_id in number
1312 ,p_book_entire_period_flag in varchar2
1313 ,p_timezone in varchar2
1314 ,p_last_res_bkng_id in number)return boolean IS
1315 --
1316 l_proc varchar2(72) := g_package||'check_double_booking';
1317 l_exists number;
1318 l_book_entire_period varchar2(1);
1319
1320
1321 l_resource_type varchar2(30);
1322
1323 cursor get_resource_type is
1324 select resource_type
1325 from ota_suppliable_resources
1326 where supplied_resource_id = p_supplied_resource_id;
1327
1328
1329 -- For entire duration flag null or N
1330 cursor double_booking is
1331 select 1
1332 from ota_resource_bookings trb
1333 where trb.supplied_resource_id = p_supplied_resource_id
1334 and (
1335 (p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))
1336 and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1337 and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
1338 and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
1339 )
1340 /*or
1341 (to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1342 || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
1343 and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1344 || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
1345 )*/)
1346 and trb.status = 'C'
1347 and (p_resource_booking_id is null
1348 or (p_resource_booking_id is not null
1349 and p_resource_booking_id <> trb.resource_booking_id
1350 and trb.resource_booking_id > nvl(p_last_res_bkng_id,0)));
1351
1352 -- Modified to exclude forum and chat related bookings
1353 -- For entire duration flag null or N
1354 cursor trainer_double_booking is
1355 select 1
1356 from ota_resource_bookings trb
1357 where trb.supplied_resource_id = p_supplied_resource_id
1358 and (
1359 (p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))
1360 and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1361 and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
1362 and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1363 /*or
1364 (to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1365 || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
1366 and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1367 || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1368 */)
1369 and trb.status = 'C'
1370 and trb.chat_id is null
1371 and trb.forum_id is null
1372 and (p_resource_booking_id is null
1373 or (p_resource_booking_id is not null
1374 and p_resource_booking_id <> trb.resource_booking_id
1375 and trb.resource_booking_id > nvl(p_last_res_bkng_id,0)));
1376 --bug 5110895
1377
1378
1379 Cursor csr_chk_date_overlap is
1380 select Book_entire_period_flag,required_end_time,required_start_time,
1381 required_date_from,required_date_to,timezone_code
1382 from ota_resource_bookings trb
1383 where trb.supplied_resource_id = p_supplied_resource_id
1384 and p_required_date_from <= trunc (ota_timezone_util.convert_date
1385 (trb.required_date_to, nvl (trb.required_end_time, '23:59'),
1386 trb.timezone_code, p_timezone))
1387 and p_required_date_to >= trunc (ota_timezone_util.convert_date
1388 (trb.required_date_from, nvl (trb.required_start_time, '00:00'),
1389 trb.timezone_code, p_timezone))
1390 and trb.status = 'C'
1391 and (p_resource_booking_id is null
1392 or (p_resource_booking_id is not null
1393 and p_resource_booking_id <> trb.resource_booking_id
1394 and trb.resource_booking_id > nvl(p_last_res_bkng_id,0)));
1395
1396
1397 -- Modified to exclude forum and chat related bookings
1398 Cursor csr_chk_trainer_date_overlap is
1399 select Book_entire_period_flag,required_end_time,required_start_time,
1400 required_date_from,required_date_to,timezone_code
1401 from ota_resource_bookings trb
1402 where trb.supplied_resource_id = p_supplied_resource_id
1403 and (
1404 (p_required_date_from between
1405 trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1406 and
1407 trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
1408 or
1409 (p_required_date_to between
1410 trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1411 and
1412 trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
1413 or
1414 ((p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)))
1415 and
1416 (p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))))
1417 )
1418 and trb.status = 'C'
1419 and trb.chat_id is null
1420 and trb.forum_id is null
1421 and (p_resource_booking_id is null
1422 or (p_resource_booking_id is not null
1423 and p_resource_booking_id <> trb.resource_booking_id
1424 and trb.resource_booking_id > nvl(p_last_res_bkng_id,0)));
1425
1426
1427 --
1428 begin
1429 hr_utility.set_location('Entering:'||l_proc,5);
1430 --
1431 open get_resource_type;
1432 fetch get_resource_type into l_resource_type;
1433 close get_resource_type;
1434
1435 if (l_resource_type = 'T') then
1436
1437 open trainer_double_booking;
1438 fetch trainer_double_booking into l_exists;
1439
1440 if trainer_double_booking%found then
1441 --
1442 close trainer_double_booking;
1443 return TRUE;
1444 else
1445 close trainer_double_booking;
1446
1447 for trainer_rec in csr_chk_trainer_date_overlap
1448 loop
1449
1450 /*Fetch csr_chk_trainer_date_overlap into l_book_entire_period;
1451 if csr_chk_trainer_date_overlap%NotFound then
1452 close csr_chk_trainer_date_overlap;
1453 --No date overlap
1454 return FALSE;
1455 else
1456 close csr_chk_trainer_date_overlap;*/
1457 --Date overlap present
1458 -- Check new or existing either one is book enire period Y
1459
1460 if ((p_required_date_from <> p_required_date_to)
1461 and (trainer_rec.required_date_from <> trainer_rec.required_date_to)) then
1462 if trainer_rec.book_entire_period_flag = 'Y' or p_book_entire_period_flag = 'Y' then
1463 --check time overlap
1464 if (
1465 (p_required_date_from = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone))
1466 and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone))
1467 or
1468 (p_required_date_to = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
1469 and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
1470 ) then
1471
1472 return false;
1473 else
1474 return TRUE ;
1475 end if;
1476
1477 end if;
1478 --bug 5152139
1479 elsif(
1480 (p_required_date_from = p_required_date_to and trainer_rec.book_entire_period_flag = 'Y')
1481 or (trainer_rec.required_date_from = trainer_rec.required_date_to and p_book_entire_period_flag = 'Y')
1482 ) then
1483
1484 --since first cursor didn't give problem this means new and old record dates cannot be equal
1485 --and time
1486 if(
1487 (p_required_date_from = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone)) and
1488 nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
1489 or
1490 (p_required_date_to = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone)) and
1491 nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone)
1492 )
1493 ) then
1494
1495 return false;
1496
1497 else
1498
1499 return True;
1500 end if;
1501 --bug 5116223
1502 elsif((p_required_date_from = p_required_date_to or trainer_rec.required_date_from = trainer_rec.required_date_to ) and trainer_rec.timezone_code <> p_timezone)then
1503 if(trunc(ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
1504 <> trunc(ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone))) then
1505 if((to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1506 || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone)
1507 and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1508 || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
1509 ) then
1510 return true;
1511 end if;
1512 end if;
1513 end if;
1514 end loop;
1515
1516 end if;
1517
1518 else
1519
1520 open double_booking;
1521 fetch double_booking into l_exists;
1522
1523 if double_booking%found then
1524 --
1525 close double_booking;
1526 return TRUE;
1527 else
1528 close double_booking;
1529 -- not sure if still conflict or not depending on book_entire_period_flag of existing or new record
1530
1531 --get date overlap record
1532
1533 for rec in csr_chk_date_overlap
1534 loop
1535
1536 /*Fetch csr_chk_trainer_date_overlap into l_book_entire_period;
1537 if csr_chk_trainer_date_overlap%NotFound then
1538 close csr_chk_trainer_date_overlap;
1539 --No date overlap
1540 return FALSE;
1541 else
1542 close csr_chk_trainer_date_overlap;*/
1543 --Date overlap present
1544 -- Check new or existing either one is book enire period Y
1545
1546 /* if ((p_required_date_from <> p_required_date_to)
1547 and (rec.required_date_from <> rec.required_date_to)) then*/
1548 if rec.book_entire_period_flag = 'Y' or p_book_entire_period_flag = 'Y' then
1549 --check time overlap
1550 if (
1551 (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1552 and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1553 or
1554 (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1555 and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1556 ) then
1557
1558 return false;
1559 else
1560 return TRUE ;
1561 end if;
1562
1563 elsif(
1564 (p_required_date_from = p_required_date_to and rec.book_entire_period_flag = 'Y')
1565 or (rec.required_date_from = rec.required_date_to and p_book_entire_period_flag = 'Y')
1566 ) then
1567
1568 --since first cursor didn't give problem this means new and old record dates cannot be equal
1569 --and time
1570 if(
1571 (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone)) and
1572 nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1573 or
1574 (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)) and
1575 nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
1576 )
1577 ) then
1578
1579 return false;
1580
1581 else
1582
1583 return True;
1584 end if;
1585 --bug 5116223
1586 elsif((p_required_date_from = p_required_date_to or rec.required_date_from = rec.required_date_to) and rec.timezone_code <> p_timezone ) then
1587 if(trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1588 <> trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))) then
1589 if((to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1590 || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
1591 and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1592 || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1593 ) then
1594 return true;
1595 end if;
1596 end if;
1597 end if;
1598 end loop;
1599
1600 end if;
1601
1602 end if;
1603
1604
1605 --close double_booking;
1606 return FALSE;
1607 --
1608 hr_utility.set_location('Leaving:'||l_proc,10);
1609
1610 end check_double_booking;
1611
1612 --
1613 -- ---------------------------------------------------------------------
1614 -- -------------------< check_trainer_venue_book >----------------------
1615 -- ---------------------------------------------------------------------
1616 --
1617 -- PUBLIC
1618 -- Description: Check that Trainers and Venues cannot be double booked
1619 -- if Confirmed
1620 --
1621 --
1622 procedure check_trainer_venue_book
1623 (p_supplied_resource_id in number
1624 ,p_required_date_from in date
1625 ,p_required_start_time in varchar2
1626 ,p_required_date_to in date
1627 ,p_required_end_time in varchar2
1628 ,p_resource_booking_id in number
1629 ,p_book_entire_period_flag in varchar2
1630 ,p_timezone in varchar2
1631 ) IS
1632 --
1633 cursor get_resource_type is
1634 select resource_type
1635 from ota_suppliable_resources
1636 where supplied_resource_id = p_supplied_resource_id;
1637 --
1638 l_resource_type varchar2(30);
1639 l_proc varchar2(72) := g_package||'check_trainer_venue_book';
1640 --
1641 begin
1642 --
1643 open get_resource_type;
1644 fetch get_resource_type into l_resource_type;
1645 close get_resource_type;
1646 --
1647 if l_resource_type in ('T','V') then
1648 if check_double_booking (p_supplied_resource_id
1649 ,p_required_date_from
1650 ,p_required_start_time
1651 ,p_required_date_to
1652 ,p_required_end_time
1653 ,p_resource_booking_id
1654 ,p_book_entire_period_flag
1655 ,p_timezone
1656 ) then
1657 fnd_message.set_name('OTA','OTA_13395_TRB_RES_DOUBLEBOOK');
1658 fnd_message.raise_error;
1659 end if;
1660 end if;
1661 --
1662 end check_trainer_venue_book;
1663 -- ---------------------------------------------------------------------
1664 -- |------------------< deduct_consumable_stock_check >-----------------
1665 -- ---------------------------------------------------------------------
1666 -- PUBLIC
1667 -- Description: Check that the quantity required can be deducted from
1668 -- the stock level on SUPPLIABLE_RESOURCES without causing
1669 -- a negative stock level.
1670 --
1671 -- returns True if stock levels OK and false if not.
1672 --
1673 /* function deduct_consumable_stock_check(p_supplied_resource_id in number,
1674 p_quantity in number)
1675 return boolean is
1676 --
1677 l_proc varchar2(72) := g_package||'deduct_consumable_stock';
1678 l_sign number;
1679 l_return boolean;
1680 --
1681 -- cursor to perform check on consumable stock levels
1682 --
1683 cursor chk_slevels is
1684 select sign(sr.stock - p_quantity)
1685 from ota_suppliable_resources sr
1686 where sr.supplied_resource_id = p_supplied_resource_id
1687 and sr.consumable_flag = 'Y';
1688 --
1689 begin
1690 hr_utility.set_location('Entering:'||l_proc,5);
1691 --
1692 open chk_slevels;
1693 fetch chk_slevels into l_sign;
1694 IF chk_slevels%found THEN
1695 IF l_sign < 0 THEN
1696 l_return := FALSE;
1697 ELSE l_return := TRUE;
1698 END IF;
1699 END IF;
1700 close chk_slevels;
1701 return (l_return);
1702 --
1703 hr_utility.set_location('Leaving:'||l_proc,10);
1704 end deduct_consumable_stock_check; */
1705 --
1706 -- ---------------------------------------------------------------------
1707 -- |---------------------< deduct_consumable_stock >--------------------
1708 -- ---------------------------------------------------------------------
1709 -- PUBLIC
1710 -- Description: Deduct the quantity of a consumable resource booked from
1711 -- the stock level on SUPPLIABLE_RESOURCES.
1712 --
1713 /* procedure deduct_consumable_stock(p_supplied_resource_id in number,
1714 p_quantity in number) is
1715 --
1716 l_proc varchar2(72) := g_package||'deduct_consumable_stock';
1717 l_new_stock number;
1718 --
1719 -- cursor to calculate third party update to OTA_SUPPLIABLE_RESOURCES
1720 --
1721 cursor calc_newstock is
1722 select tsr.stock - p_quantity
1723 from ota_suppliable_resources tsr
1724 where tsr.supplied_resource_id = p_supplied_resource_id
1725 and tsr.consumable_flag = 'Y';
1726 --
1727 begin
1728 hr_utility.set_location('Entering:'||l_proc,5);
1729 --
1730 open calc_newstock;
1731 fetch calc_newstock into l_new_stock;
1732 close calc_newstock;
1733 --
1734 -- perform update
1735 --
1736 update ota_suppliable_resources
1737 set stock = l_new_stock
1738 where supplied_resource_id = p_supplied_resource_id;
1739 --
1740 hr_utility.set_location('Leaving:'||l_proc,10);
1741 end deduct_consumable_stock; */
1742 --
1743 -- ---------------------------------------------------------------------
1744 -- |---------------------< check_if_tfl_exists >------------------------
1745 -- ---------------------------------------------------------------------
1746 -- PUBLIC
1747 -- Description: If finance lines exist for the booking it may not be
1748 -- deleted.
1749 --
1750 procedure check_if_tfl_exists(p_resource_booking_id in number) is
1751 --
1752 l_proc varchar2(72) := g_package||'check_if_tfl_exists';
1753 l_exists number;
1754 --
1755 -- cursor to perform check
1756 --
1757 cursor chk_tfl is
1758 select 1
1759 from ota_finance_lines
1760 where resource_booking_id = p_resource_booking_id;
1761 --
1762 begin
1763 hr_utility.set_location('Entering:'||l_proc,5);
1764 --
1765 open chk_tfl;
1766 fetch chk_tfl into l_exists;
1767 IF chk_tfl%found THEN
1768 fnd_message.set_name('OTA','OTA_13274_TRB_TFL_EXIST');
1769 close chk_tfl;
1770 fnd_message.raise_error;
1771 ELSE close chk_tfl;
1772 END IF;
1773 --
1774 hr_utility.set_location('Leaving:'||l_proc,10);
1775 end check_if_tfl_exists;
1776 --
1777 -- -------------------------------------------------------------------
1778 -- |-------------------< check_event_type >---------------------------
1779 -- -------------------------------------------------------------------
1780 -- PUBLIC
1781 -- Description: Resource bookings may be made for the following event
1782 -- types: SCEDULED, SESSION, PROGRAMME MEMBER, DEVELOPMENT
1783 --
1784 procedure check_event_type(p_event_id in number) is
1785 --
1786 l_proc varchar2(72) := g_package||'check_event_type';
1787 l_exists number;
1788 --
1789 -- cursor to perform check on event type
1790 --
1791 cursor chk_type is
1792 select 1
1793 from ota_events
1794 where event_id = p_event_id
1795 and event_type in
1796 ('SCHEDULED','SESSION','PROGRAMME MEMBER','DEVELOPMENT','SELFPACED');
1797 --
1798 begin
1799 hr_utility.set_location('Entering:'||l_proc,5);
1800 --
1801 open chk_type;
1802 fetch chk_type into l_exists;
1803 IF chk_type%notfound THEN
1804 close chk_type;
1805 fnd_message.set_name('OTA','OTA_13275_TRB_WRONG_EVENT_TYPE');
1806 fnd_message.raise_error;
1807 ELSE close chk_type;
1808 END IF;
1809 --
1810 hr_utility.set_location('Leaving:'||l_proc,10);
1811 end check_event_type;
1812 --
1813 -- -------------------------------------------------------------------
1814 -- |-------------------< check_update_quant_del >---------------------
1815 -- -------------------------------------------------------------------
1816 -- PUBLIC
1817 -- Description: If the Quantity or Delegates_per_unit fields are upated
1818 -- then a check needs to made to ensure that their sum is
1819 -- not exceeded by the number of resource allocations made
1820 -- to the booking.
1821 --
1822 -- Returns TRUE if no. of allocations is ok or the
1823 -- calculation cannot be performed due to one of the
1824 -- variables being null. Returns FALSE if no. of
1825 -- allocations not ok.
1826 --
1827 function check_update_quant_del(p_resource_booking_id in number,
1828 p_quantity in number,
1829 p_del_per_unit in number)
1830 return boolean is
1831 --
1832 l_proc varchar2(72) := g_package||'check_update_quant_del';
1833 l_count number;
1834 l_calc number;
1835 l_return boolean;
1836 --
1837 -- cursor to perform count
1838 --
1839 cursor get_count is
1840 select count(*)
1841 from ota_resource_allocations
1842 where equipment_resource_booking_id = p_resource_booking_id;
1843 --
1844 begin
1845 hr_utility.set_location('Entering:'||l_proc,5);
1846 --
1847 IF p_quantity is not null AND p_del_per_unit is not null THEN
1848 l_calc := p_quantity * p_del_per_unit;
1849 open get_count;
1850 fetch get_count into l_count;
1851 close get_count;
1852 IF l_count <= l_calc THEN
1853 l_return := TRUE;
1854 ELSE l_return := FALSE;
1855 END IF;
1856 ELSE l_return := TRUE;
1857 END IF;
1858 return (l_return);
1859 --
1860 hr_utility.set_location('Leaving:'||l_proc,10);
1861 end check_update_quant_del;
1862 --
1863 -- ------------------------------------------------------------------
1864 -- |---------------------< get_required_resources >------------------
1865 -- ------------------------------------------------------------------
1866 -- Description: Get the mandatory resources defined for an event
1867
1868 Procedure get_required_resources(p_activity_version_id in number,
1869 p_event_id in number,
1870 p_date_booking_placed in date,
1871 p_event_start_date in date,
1872 p_event_end_date in date) is
1873 --
1874 l_resource_booking_id number(38);
1875 l_ovn number(38);
1876 l_finance_line_id number(38);
1877 l_finance_line_ovn number(38);
1878 l_proc varchar2(72) := g_package||'get_required_resources';
1879 l_supplied_resource_id number(38);
1880 l_quantity number(38);
1881 l_event_timezone varchar2(30);
1882 --
1883 Cursor get_resources is
1884 Select
1885 rud.supplied_resource_id,
1886 rud.quantity
1887 From
1888 ota_resource_usages rud
1889 Where rud.offering_id = (select evt.parent_offering_id from ota_events evt -- bug 3494404
1890 where evt.event_id = p_event_id) -- bug 3494404
1891 and p_event_start_date between
1892 nvl(rud.start_date, hr_api.g_sot) and nvl(rud.end_date, hr_api.g_eot)
1893 and rud.supplied_resource_id is not null
1894 and rud.required_flag = 'Y'
1895 and not exists
1896 (Select null
1897 From ota_resource_bookings trb
1898 Where
1899 trb.supplied_resource_id = rud.supplied_resource_id
1900 and trb.event_id = p_event_id);
1901 --
1902
1903 Cursor get_event_timezone is --Bug#5126185
1904 select timezone
1905 from ota_events
1906 where event_id=p_event_id;
1907
1908 --
1909 Begin
1910 --
1911 hr_utility.set_location('Entering:'||l_proc,5);
1912 --
1913 Open get_resources;
1914 --
1915 Loop
1916 --
1917 Fetch get_resources into l_supplied_resource_id,
1918 l_quantity;
1919 --
1920 Exit when get_resources%notfound or get_resources%notfound is null;
1921 open get_event_timezone;
1922 fetch get_event_timezone into l_event_timezone;
1923 close get_event_timezone;
1924
1925 -- bug 3891115
1926 ota_utility.ignore_dff_validation('OTA_RESOURCE_BOOKINGS');
1927 --
1928 ota_resource_booking.ins(p_resource_booking_id => l_resource_booking_id,
1929 p_supplied_resource_id => l_supplied_resource_id,
1930 p_event_id => p_event_id,
1931 p_date_booking_placed => p_date_booking_placed,
1932 p_object_version_number => l_ovn,
1933 p_status => 'P',
1934 p_quantity => l_quantity,
1935 p_required_date_from => p_event_start_date,
1936 p_required_date_to => p_event_end_date,
1937 p_primary_venue_flag => 'N',
1938 p_finance_line_id => l_finance_line_id,
1939 p_finance_line_ovn => l_finance_line_ovn,
1940 p_booking_person_id => fnd_profile.value('USER_ID'),
1941 p_display_to_learner_flag => 'Y',
1942 p_book_entire_period_flag => 'Y',
1943 p_timezone_code =>l_event_timezone --Bug#5126185
1944 );
1945 End Loop;
1946 Close get_resources;
1947 --
1948 commit;
1949
1950 hr_utility.set_location('Leaving:'||l_proc,10);
1951 end get_required_resources;
1952
1953 -- -------------------------------------------------------------------
1954 -- |----------------------< get_evt_defaults >------------------------
1955 -- -------------------------------------------------------------------
1956 -- PUBLIC
1957 procedure get_evt_defaults(p_event_id in number,
1958 p_event_title in out nocopy varchar2,
1959 p_event_start_date in out nocopy date,
1960 p_event_end_date in out nocopy date,
1961 p_event_start_time in out nocopy varchar2,
1962 p_event_end_time in out nocopy varchar2,
1963 p_curr_code in out nocopy varchar2,
1964 p_curr_meaning in out nocopy varchar2) is
1965 --
1966 l_proc varchar2(72) := g_package||'get_evt_defaults';
1967 --
1968 -- cursor to get defaults
1969 --
1970 cursor get_defs is
1971 select evt.title,
1972 evt.course_start_date,
1973 evt.course_end_date,
1974 evt.course_start_time,
1975 evt.course_end_time,
1976 evt.currency_code,
1977 fnd.name
1978 from ota_events_vl evt, -- MLS change _vl added
1979 fnd_currencies_vl fnd
1980 where evt.event_id = p_event_id
1981 and fnd.currency_code = evt.currency_code;
1982 --
1983 begin
1984 hr_utility.set_location('Entering:'||l_proc,5);
1985 --
1986 open get_defs;
1987 fetch get_defs into p_event_title,
1988 p_event_start_date,
1989 p_event_end_date,
1990 p_event_start_time,
1991 p_event_end_time,
1992 p_curr_code,
1993 p_curr_meaning;
1994 close get_defs;
1995 --
1996 --
1997 hr_utility.set_location('Leaving:'||l_proc,10);
1998 end get_evt_defaults;
1999
2000 -- ---------------------------------------------------------------------
2001 -- |--------------------< check_start_end_times >-----------------------
2002 -- ---------------------------------------------------------------------
2003 -- PUBLIC
2004 -- Description: Start time must be before end time.
2005 --
2006 procedure check_start_end_times(p_start_time in varchar2,
2007 p_end_time in varchar2) is
2008 --
2009 l_proc varchar2(72) := g_package||'check_start_end_times';
2010 --
2011 begin
2012 --
2013 hr_utility.set_location('Entering:'||l_proc,5);
2014 --
2015 if to_number(substr(p_start_time, 1, 2)) >
2016 to_number(substr(p_end_time, 1, 2))
2017 or (to_number(substr(p_start_time, 1, 2)) =
2018 to_number(substr(p_end_time, 1, 2))
2019 and to_number(substr(p_start_time, 4, 2)) >
2020 to_number(substr(p_end_time, 4, 2))) then
2021 --
2022 fnd_message.set_name('OTA','OTA_13640_TRB_START_END_TIMES');
2023 fnd_message.raise_error;
2024 --
2025 end if;
2026 --
2027 hr_utility.set_location('Leaving:'||l_proc,10);
2028 --
2029 end check_start_end_times;
2030 --
2031 ----------------------------------------------------------------------
2032
2033 -- ---------------------------------------------------------------------
2034 -- |--------------------< check_trainer_competence >--------------------
2035 -- ---------------------------------------------------------------------
2036 -- PUBLIC
2037 -- Description: Check trainer competence match the activity.
2038 --
2039 procedure check_trainer_competence (p_event_id in number,
2040 p_supplied_resource_id in number,
2041 p_required_date_from IN DATE,
2042 p_required_date_to IN DATE,
2043 p_end_of_time IN DATE,
2044 p_warn out nocopy boolean) is
2045 --
2046 l_proc varchar2(72) := g_package|| 'check_trainer_competence';
2047 --
2048 l_resource_type varchar2(1);
2049 l_trainer_id number;
2050 l_competence varchar2(3000);
2051 l_person_id number;
2052 l_event_type ota_events.event_type%type;
2053 l_parent_event ota_events.event_id%type;
2054 l_event_id ota_events.event_id%type;
2055 --l_language_id ota_events.language_id%type; -- 2733966
2056 l_competence_id per_competence_elements.competence_id%type;
2057 l_proficiency_level_id per_competence_elements.proficiency_level_id%type;
2058 l_step_value number := null;
2059 l_language_code ota_offerings.language_code%type;
2060
2061 cursor c_resource
2062 IS
2063 SELECT resource_type, trainer_id
2064 FROM ota_suppliable_resources
2065 WHERE supplied_resource_id = p_supplied_resource_id ;
2066
2067 Cursor
2068 C_Trainer_comp (p_event_id in number)
2069 IS
2070 SELECT pce.competence_id ,
2071 nvl(pce.proficiency_level_id,0) proficiency_level_id ,
2072 nvl(rat.step_value,0) step_value
2073 FROM per_competence_elements pce, per_rating_levels rat
2074 WHERE nvl(pce.effective_date_from, p_required_date_from ) <= p_required_date_from --bug 3332972
2075 AND nvl(pce.effective_date_to,p_required_date_to) >= p_required_date_to --bug 3332972
2076 AND rat.rating_level_id = pce.proficiency_level_id
2077 AND pce.type = 'OTA_OFFERING' --bug 3494404
2078 AND pce.Object_id in (
2079 SELECT parent_offering_id --bug 3494404
2080 FROM ota_events
2081 WHERE event_id = p_event_id);
2082
2083 Cursor
2084 c_event_type
2085 IS
2086 SELECT ev.event_type,ev.parent_event_id ,off.language_code
2087 FROM
2088 OTA_EVENTS ev,
2089 OTA_OFFERINGS_VL off
2090 WHERE ev.EVENT_ID = p_event_id
2091 AND ev.parent_offering_id = off.offering_id;
2092
2093 Cursor
2094 c_event_lang (p_event_id number)
2095 IS
2096 select off.language_code
2097 From OTA_EVENTS ev,
2098 OTA_OFFERINGS_VL off
2099 WHERE EVENT_ID = p_event_id
2100 AND ev.parent_offering_id = off.offering_id;
2101
2102 cursor c_comp_lang(p_language_code in varchar2)
2103 IS
2104 Select ocl.competence_id,
2105 nvl(ocl.min_proficiency_level_id,0) min_proficiency_level_id,
2106 nvl(rat.step_value,0) step_value
2107 From ota_competence_languages ocl,
2108 per_rating_levels rat
2109 Where
2110 ocl.language_code = p_language_code and
2111 ocl.business_group_id = ota_general.get_business_group_id and
2112 nvl(rat.rating_level_id,0) = nvl(ocl.min_proficiency_level_id,0);
2113
2114
2115 cursor c_wo_level(p_language_code in varchar2)
2116 IS
2117 Select ocl.competence_id
2118 From ota_competence_languages ocl
2119 Where
2120 ocl.language_code = p_language_code and
2121 ocl.business_group_id = ota_general.get_business_group_id;
2122
2123
2124 begin
2125 --
2126 hr_utility.set_location('Entering:'||l_proc,5);
2127 p_warn := false;
2128 IF p_event_id is not null then
2129 For a in c_resource LOOP
2130 l_resource_type := a.resource_type;
2131 l_trainer_id := a.trainer_id;
2132 end loop ;
2133
2134 For event in c_event_type LOOP
2135 l_event_type := event.event_type;
2136 l_parent_event := event.parent_event_id;
2137 l_language_code := event.language_code;
2138 end loop;
2139
2140 If l_resource_type = 'T' then
2141 hr_utility.set_location('Entering:'||l_proc,10);
2142 if l_parent_event is not null and
2143 l_event_type = 'SESSION' then
2144 l_event_id := l_parent_event;
2145
2146 For evt_lang in c_event_lang(l_event_id)
2147 Loop
2148 l_language_code := evt_lang.language_code;
2149 End Loop;
2150
2151 else
2152 l_event_id := p_event_id;
2153
2154 end if;
2155
2156 if l_trainer_id is not null then
2157 For b in c_trainer_comp(l_event_id)
2158 LOOP
2159 if l_competence is null then
2160 l_competence := ' select pce.person_id from per_competence_elements pce ,' ||
2161 ' per_rating_levels rat '||
2162 ' where ' ||
2163 ' pce.competence_id = '||b.competence_id ||
2164 ' and rat.rating_level_id = pce.proficiency_level_id ' ||
2165 ' and pce.person_id = :person_id ' ||
2166 ' and pce.effective_date_from <= '''||p_required_date_from||
2167 ''' and NVL(pce.effective_date_to,'''||p_end_of_time||''') >= '''||p_required_date_to||
2168 ''' and nvl(rat.step_value,0) >= '|| b.step_value;
2169
2170 else
2171
2172 l_competence := l_competence || ' AND ' ||
2173 ' pce.person_id in ('||
2174 ' select pce.person_id from per_competence_elements pce , '||
2175 ' per_rating_levels rat ' ||
2176 ' where' ||
2177 ' pce.competence_id = '||b.competence_id ||
2178 ' and pce.effective_date_from <= '''||p_required_date_from||
2179 ''' and NVL(pce.effective_date_to,'''||p_end_of_time||''') >= '''||p_required_date_to||
2180 ''' and rat.rating_level_id = pce.proficiency_level_id ' ||
2181 ' and nvl(rat.step_value,0) >= '|| b.step_value || ')';
2182
2183 end if;
2184
2185 end loop;
2186
2187
2188 if l_language_code is not null then
2189
2190 For lang_comp in c_comp_lang(l_language_code)
2191 LOOP
2192 if l_competence is null then
2193 l_competence := ' select pce.person_id from per_competence_elements pce, '||
2194 ' per_rating_levels rat ' ||
2195 ' where' ||
2196 ' pce.competence_id = '||lang_comp.competence_id ||
2197 ' and pce.effective_date_from <= '''||p_required_date_from||
2198 ''' and NVL(pce.effective_date_to,'''||p_end_of_time||''') >= '''||p_required_date_to||
2199 ''' and rat.rating_level_id = pce.proficiency_level_id ' ||
2200 ' and pce.person_id = :person_id ' ||
2201 ' and nvl(rat.step_value,0) >= '|| lang_comp.step_value ;
2202 -- ' and nvl(proficiency_level_id,0) = '|| lang_comp.min_proficiency_level_id;
2203 l_step_value := lang_comp.step_value ;
2204 else
2205
2206 l_competence := l_competence || ' AND ' ||
2207 ' pce.person_id in ('||
2208 ' select person_id from per_competence_elements pce , '||
2209 ' per_rating_levels rat ' ||
2210 ' where' ||
2211 ' pce.competence_id = '||lang_comp.competence_id ||
2212 ' and pce.effective_date_from <= '''||p_required_date_from||
2213 ''' and NVL(pce.effective_date_to,'''||p_end_of_time||''') >= '''||p_required_date_to||
2214 ''' and rat.rating_level_id = pce.proficiency_level_id ' ||
2215 ' and nvl(rat.step_value,0) >= '|| lang_comp.step_value || ')';
2216 -- ' and nvl(proficiency_level_id,0) = '|| lang_comp.min_proficiency_level_id || ')';
2217
2218
2219 l_step_value := lang_comp.step_value ;
2220 end if;
2221
2222 END LOOP;
2223
2224
2225 if l_step_value is null then
2226
2227 For e in c_wo_level(l_language_code)
2228 LOOP
2229 if l_competence is null then
2230 l_competence := ' select pce.person_id from per_competence_elements pce ' ||
2231 ' where ' ||
2232 ' pce.competence_id = '||e.competence_id ||
2233 ' and pce.effective_date_from <= '''||p_required_date_from||
2234 ''' and NVL(pce.effective_date_to,'''||p_end_of_time||''') >= '''||p_required_date_to||
2235 ''' and pce.person_id = :person_id ' ;
2236
2237
2238 else
2239
2240 l_competence := l_competence || ' AND ' ||
2241 ' pce.person_id in ('||
2242 ' select pce.person_id from per_competence_elements pce '||
2243 ' where' ||
2244 ' pce.effective_date_from <= '''||p_required_date_from||
2245 ''' and NVL(pce.effective_date_to,'''||p_end_of_time||''') >= '''||p_required_date_to||
2246 ''' and pce.competence_id = '||e.competence_id || ')';
2247
2248 end if;
2249
2250 end loop;
2251
2252
2253 end if;
2254
2255 end if;
2256 IF l_competence is not null then
2257 hr_utility.set_location('Entering:'||l_proc,15);
2258
2259 BEGIN
2260
2261
2262 execute immediate l_competence
2263 into l_person_id
2264 using l_trainer_id;
2265 EXCEPTION WHEN NO_DATA_FOUND Then
2266 p_warn := TRUE;
2267 END;
2268 ELSE
2269 p_warn := FALSE; --Bug 2039862. Added Else clause.
2270 END IF;
2271 else
2272 -- Bug 2039862. Modified the return value of p_warn.
2273 -- p_warn := TRUE;
2274 p_warn := FALSE;
2275 end if;
2276 end if;
2277 end if;
2278 hr_utility.set_location('leaving:'||l_proc,20);
2279 end check_trainer_competence;
2280
2281 end ota_trb_api_procedures;