1 Package Body ota_trb_bus as
2 /* $Header: ottrbrhi.pkb 120.6.12000000.3 2007/07/05 09:22:53 aabalakr noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_trb_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_resource_booking_id number default null;
15
16
17 --
18 --
19 -- ---------------------------------------------------------------------------
20 -- |----------------------< set_security_group_id >--------------------------|
21 -- ---------------------------------------------------------------------------
22 --
23 Procedure set_security_group_id
24 (p_resource_booking_id in number
25 ,p_associated_column1 in varchar2 default null
26 ) is
27 --
28 -- Declare cursor
29 --
30
31 cursor csr_sec_grp is
32 select pbg.security_group_id,
33 pbg.legislation_code
34 from per_business_groups_perf pbg
35 , ota_resource_bookings trb
36 , ota_suppliable_resources tsr
37 where trb.resource_booking_id = p_resource_booking_id
38 and trb.supplied_resource_id = tsr.supplied_resource_id
39 and pbg.business_group_id = tsr.business_group_id;
40 --
41 -- Declare local variables
42 --
43 l_security_group_id number;
44 l_proc varchar2(72) := g_package||'set_security_group_id';
45 l_legislation_code varchar2(150);
46 --
47 begin
48 --
49 hr_utility.set_location('Entering:'|| l_proc, 10);
50 --
51 -- Ensure that all the mandatory parameter are not null
52 --
53 hr_api.mandatory_arg_error
54 (p_api_name => l_proc
55 ,p_argument => 'resource_booking_id'
56 ,p_argument_value => p_resource_booking_id
57 );
58 --
59 open csr_sec_grp;
60 fetch csr_sec_grp into l_security_group_id
61 , l_legislation_code;
62 --
63 if csr_sec_grp%notfound then
64 --
65 close csr_sec_grp;
66 --
67 -- The primary key is invalid therefore we must error
68 --
69 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
70 hr_multi_message.add
71 (p_associated_column1
72 => nvl(p_associated_column1,'RESOURCE_BOOKING_ID')
73 );
74 --
75 else
76 close csr_sec_grp;
77 --
78 -- Set the security_group_id in CLIENT_INFO
79 --
80 hr_api.set_security_group_id
81 (p_security_group_id => l_security_group_id
82 );
83 --
84 -- Set the sessions legislation context in HR_SESSION_DATA
85 --
86 hr_api.set_legislation_context(l_legislation_code);
87 end if;
88 --
89 hr_utility.set_location(' Leaving:'|| l_proc, 20);
90 --
91 end set_security_group_id;
92 --
93 -- ---------------------------------------------------------------------------
94 -- |---------------------< return_legislation_code >-------------------------|
95 -- ---------------------------------------------------------------------------
96 --
97 Function return_legislation_code
98 (p_resource_booking_id in number
99 )
100 Return Varchar2 Is
101 --
102 -- Declare cursor
103 --
104 cursor csr_leg_code is
105 select pbg.legislation_code
106 from per_business_groups_perf pbg
107 , ota_resource_bookings trb
108 , ota_suppliable_Resources tsr
109 where trb.resource_booking_id = p_resource_booking_id
110 and trb.supplied_resource_id = tsr.supplied_resource_id
111 and pbg.business_group_id = tsr.business_group_id;
112 --
113 -- Declare local variables
114 --
115 l_legislation_code varchar2(150);
116 l_proc varchar2(72) := g_package||'return_legislation_code';
117 --
118 Begin
119 --
120 hr_utility.set_location('Entering:'|| l_proc, 10);
121 --
122 -- Ensure that all the mandatory parameter are not null
123 --
124 hr_api.mandatory_arg_error
125 (p_api_name => l_proc
126 ,p_argument => 'resource_booking_id'
127 ,p_argument_value => p_resource_booking_id
128 );
129 --
130 if ( nvl(ota_trb_bus.g_resource_booking_id, hr_api.g_number)
131 = p_resource_booking_id) then
132 --
133 -- The legislation code has already been found with a previous
134 -- call to this function. Just return the value in the global
135 -- variable.
136 --
137 l_legislation_code := ota_trb_bus.g_legislation_code;
138 hr_utility.set_location(l_proc, 20);
139 else
140 --
141 -- The ID is different to the last call to this function
142 -- or this is the first call to this function.
143 --
144 open csr_leg_code;
145 fetch csr_leg_code into l_legislation_code;
146 --
147 if csr_leg_code%notfound then
148 --
149 -- The primary key is invalid therefore we must error
150 --
151 close csr_leg_code;
152 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
153 fnd_message.raise_error;
154 end if;
155 hr_utility.set_location(l_proc,30);
156 --
157 -- Set the global variables so the values are
158 -- available for the next call to this function.
159 --
160 close csr_leg_code;
161 ota_trb_bus.g_resource_booking_id := p_resource_booking_id;
162 ota_trb_bus.g_legislation_code := l_legislation_code;
163 end if;
164 hr_utility.set_location(' Leaving:'|| l_proc, 40);
165 return l_legislation_code;
166 end return_legislation_code;
167 --
168 -- ----------------------------------------------------------------------------
169 -- |-----------------------------< chk_ddf >----------------------------------|
170 -- ----------------------------------------------------------------------------
171 --
172 -- Description:
173 -- Validates all the Developer Descriptive Flexfield values.
174 --
175 -- Prerequisites:
176 -- All other columns have been validated. Must be called as the
177 -- last step from insert_validate and update_validate.
178 --
179 -- In Arguments:
180 -- p_rec
181 --
182 -- Post Success:
183 -- If the Developer Descriptive Flexfield structure column and data values
184 -- are all valid this procedure will end normally and processing will
185 -- continue.
186 --
187 -- Post Failure:
188 -- If the Developer Descriptive Flexfield structure column value or any of
189 -- the data values are invalid then an application error is raised as
190 -- a PL/SQL exception.
191 --
192 -- Access Status:
193 -- Internal Row Handler Use Only.
194 --
195 -- ----------------------------------------------------------------------------
196 procedure chk_ddf
197 (p_rec in ota_trb_shd.g_rec_type
198 ) is
199 --
200 l_proc varchar2(72) := g_package || 'chk_ddf';
201 --
202 begin
203 hr_utility.set_location('Entering:'||l_proc,10);
204 --
205 if ((p_rec.resource_booking_id is not null) and (
206 nvl(ota_trb_shd.g_old_rec.trb_information_category, hr_api.g_varchar2) <>
207 nvl(p_rec.trb_information_category, hr_api.g_varchar2) or
208 nvl(ota_trb_shd.g_old_rec.trb_information1, hr_api.g_varchar2) <>
209 nvl(p_rec.trb_information1, hr_api.g_varchar2) or
210 nvl(ota_trb_shd.g_old_rec.trb_information2, hr_api.g_varchar2) <>
211 nvl(p_rec.trb_information2, hr_api.g_varchar2) or
212 nvl(ota_trb_shd.g_old_rec.trb_information3, hr_api.g_varchar2) <>
213 nvl(p_rec.trb_information3, hr_api.g_varchar2) or
214 nvl(ota_trb_shd.g_old_rec.trb_information4, hr_api.g_varchar2) <>
215 nvl(p_rec.trb_information4, hr_api.g_varchar2) or
216 nvl(ota_trb_shd.g_old_rec.trb_information5, hr_api.g_varchar2) <>
217 nvl(p_rec.trb_information5, hr_api.g_varchar2) or
218 nvl(ota_trb_shd.g_old_rec.trb_information6, hr_api.g_varchar2) <>
219 nvl(p_rec.trb_information6, hr_api.g_varchar2) or
220 nvl(ota_trb_shd.g_old_rec.trb_information7, hr_api.g_varchar2) <>
221 nvl(p_rec.trb_information7, hr_api.g_varchar2) or
222 nvl(ota_trb_shd.g_old_rec.trb_information8, hr_api.g_varchar2) <>
223 nvl(p_rec.trb_information8, hr_api.g_varchar2) or
224 nvl(ota_trb_shd.g_old_rec.trb_information9, hr_api.g_varchar2) <>
225 nvl(p_rec.trb_information9, hr_api.g_varchar2) or
226 nvl(ota_trb_shd.g_old_rec.trb_information10, hr_api.g_varchar2) <>
227 nvl(p_rec.trb_information10, hr_api.g_varchar2) or
228 nvl(ota_trb_shd.g_old_rec.trb_information11, hr_api.g_varchar2) <>
229 nvl(p_rec.trb_information11, hr_api.g_varchar2) or
230 nvl(ota_trb_shd.g_old_rec.trb_information12, hr_api.g_varchar2) <>
231 nvl(p_rec.trb_information12, hr_api.g_varchar2) or
232 nvl(ota_trb_shd.g_old_rec.trb_information13, hr_api.g_varchar2) <>
233 nvl(p_rec.trb_information13, hr_api.g_varchar2) or
234 nvl(ota_trb_shd.g_old_rec.trb_information14, hr_api.g_varchar2) <>
235 nvl(p_rec.trb_information14, hr_api.g_varchar2) or
236 nvl(ota_trb_shd.g_old_rec.trb_information15, hr_api.g_varchar2) <>
237 nvl(p_rec.trb_information15, hr_api.g_varchar2) or
238 nvl(ota_trb_shd.g_old_rec.trb_information16, hr_api.g_varchar2) <>
239 nvl(p_rec.trb_information16, hr_api.g_varchar2) or
240 nvl(ota_trb_shd.g_old_rec.trb_information17, hr_api.g_varchar2) <>
241 nvl(p_rec.trb_information17, hr_api.g_varchar2) or
242 nvl(ota_trb_shd.g_old_rec.trb_information18, hr_api.g_varchar2) <>
243 nvl(p_rec.trb_information18, hr_api.g_varchar2) or
244 nvl(ota_trb_shd.g_old_rec.trb_information19, hr_api.g_varchar2) <>
245 nvl(p_rec.trb_information19, hr_api.g_varchar2) or
246 nvl(ota_trb_shd.g_old_rec.trb_information20, hr_api.g_varchar2) <>
247 nvl(p_rec.trb_information20, hr_api.g_varchar2) ))
248 or (p_rec.resource_booking_id is null) then
249 --
250 -- Only execute the validation if absolutely necessary:
251 -- a) During update, the structure column value or any
252 -- of the attribute values have actually changed.
253 -- b) During insert.
254 --
255 hr_dflex_utility.ins_or_upd_descflex_attribs
256 (p_appl_short_name => 'OTA'
257 ,p_descflex_name => 'OTA_RESOURCE_BOOKINGS'
258 ,p_attribute_category => p_rec.trb_information_category
259 ,p_attribute1_name => 'TRB_INFORMATION1'
260 ,p_attribute1_value => p_rec.trb_information1
261 ,p_attribute2_name => 'TRB_INFORMATION2'
262 ,p_attribute2_value => p_rec.trb_information2
263 ,p_attribute3_name => 'TRB_INFORMATION3'
264 ,p_attribute3_value => p_rec.trb_information3
265 ,p_attribute4_name => 'TRB_INFORMATION4'
266 ,p_attribute4_value => p_rec.trb_information4
267 ,p_attribute5_name => 'TRB_INFORMATION5'
268 ,p_attribute5_value => p_rec.trb_information5
269 ,p_attribute6_name => 'TRB_INFORMATION6'
270 ,p_attribute6_value => p_rec.trb_information6
271 ,p_attribute7_name => 'TRB_INFORMATION7'
272 ,p_attribute7_value => p_rec.trb_information7
273 ,p_attribute8_name => 'TRB_INFORMATION8'
274 ,p_attribute8_value => p_rec.trb_information8
275 ,p_attribute9_name => 'TRB_INFORMATION9'
276 ,p_attribute9_value => p_rec.trb_information9
277 ,p_attribute10_name => 'TRB_INFORMATION10'
278 ,p_attribute10_value => p_rec.trb_information10
279 ,p_attribute11_name => 'TRB_INFORMATION11'
280 ,p_attribute11_value => p_rec.trb_information11
281 ,p_attribute12_name => 'TRB_INFORMATION12'
282 ,p_attribute12_value => p_rec.trb_information12
283 ,p_attribute13_name => 'TRB_INFORMATION13'
284 ,p_attribute13_value => p_rec.trb_information13
285 ,p_attribute14_name => 'TRB_INFORMATION14'
286 ,p_attribute14_value => p_rec.trb_information14
287 ,p_attribute15_name => 'TRB_INFORMATION15'
288 ,p_attribute15_value => p_rec.trb_information15
289 ,p_attribute16_name => 'TRB_INFORMATION16'
290 ,p_attribute16_value => p_rec.trb_information16
291 ,p_attribute17_name => 'TRB_INFORMATION17'
292 ,p_attribute17_value => p_rec.trb_information17
293 ,p_attribute18_name => 'TRB_INFORMATION18'
294 ,p_attribute18_value => p_rec.trb_information18
295 ,p_attribute19_name => 'TRB_INFORMATION19'
296 ,p_attribute19_value => p_rec.trb_information19
297 ,p_attribute20_name => 'TRB_INFORMATION20'
298 ,p_attribute20_value => p_rec.trb_information20
299 );
300 end if;
301 --
302 hr_utility.set_location(' Leaving:'||l_proc,20);
303 end chk_ddf;
304 --
305 -- ----------------------------------------------------------------------------
306 -- |-----------------------< chk_non_updateable_args >------------------------|
307 -- ----------------------------------------------------------------------------
308 -- {Start Of Comments}
309 --
310 -- Description:
311 -- This procedure is used to ensure that non updateable attributes have
312 -- not been updated. If an attribute has been updated an error is generated.
313 --
314 -- Pre Conditions:
315 -- g_old_rec has been populated with details of the values currently in
316 -- the database.
317 --
318 -- In Arguments:
319 -- p_rec has been populated with the updated values the user would like the
320 -- record set to.
321 --
322 -- Post Success:
323 -- Processing continues if all the non updateable attributes have not
324 -- changed.
325 --
326 -- Post Failure:
327 -- An application error is raised if any of the non updatable attributes
328 -- have been altered.
329 --
330 -- {End Of Comments}
331 -- ----------------------------------------------------------------------------
332 Procedure chk_non_updateable_args
333 (p_effective_date in date
334 ,p_rec in ota_trb_shd.g_rec_type
335 ) IS
336 --
337 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
338 --
339 Begin
340 --
341 -- Only proceed with the validation if a row exists for the current
342 -- record in the HR Schema.
343 --
344 IF NOT ota_trb_shd.api_updating
345 (p_resource_booking_id => p_rec.resource_booking_id
346 ,p_object_version_number => p_rec.object_version_number
347 ) THEN
348 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
349 fnd_message.set_token('PROCEDURE ', l_proc);
350 fnd_message.set_token('STEP ', '5');
351 fnd_message.raise_error;
352 END IF;
353 --
354 End chk_non_updateable_args;
355 -- ----------------------------------------------------------------------------
356 -- -------------------------< check_timezone >-----------------------------
357 -- ----------------------------------------------------------------------------
358 --
359 -- Procedure to check timezone of a chat
360 --
361 --
362 PROCEDURE check_timezone(p_timezone IN VARCHAR2)
363 IS
364 l_timezone_id NUMBER := ota_timezone_util.get_timezone_id(p_timezone);
365 BEGIN
366 IF l_timezone_id IS NULL THEN
367 fnd_message.set_name('OTA','OTA_443982_TIMEZONE_ERROR');
368 fnd_message.set_token('OBJECT_TYPE',ota_utility.get_lookup_meaning('OTA_OBJECT_TYPE','RBG',810));
369 fnd_message.raise_error;
370 END IF;
371 END check_timezone;
372 --
373 -- ----------------------------------------------------------------------------
374 -- -------------------------< check_res_bkng_time >-----------------------------
375 -- ----------------------------------------------------------------------------
376 --
377 -- Procedure to check start and end time for the book entire period flag as NO
378 --
379 --
380 --
381 procedure check_res_bkng_time(p_required_start_time IN VARCHAR2
382 ,p_required_end_time IN VARCHAR2
383 ,p_book_entire_period_flag IN VARCHAR2)is
384
385 begin
386 if (nvl(p_book_entire_period_flag,'N')='N'
387 and p_required_start_time > p_required_end_time) then
388 fnd_message.set_name('OTA','OTA_467010_RES_BKNG_TIME_ERROR');
389 fnd_message.raise_error;
390 end if;
391
392
393 end check_res_bkng_time;
394 --
395 -- ----------------------------------------------------------------------------
396 -- |---------------------------< insert_validate >----------------------------|
397 -- ----------------------------------------------------------------------------
398 -- {Start Of Comments}
399 --
400 -- Description:
401 -- This procedure controls the execution of all insert business rules
402 -- validation.
403 --
404 -- Pre Conditions:
405 -- This private procedure is called from ins procedure.
406 --
407 -- In Arguments:
408 -- A Pl/Sql record structre.
409 --
410 -- Post Success:
411 -- Processing continues.
412 --
413 -- Post Failure:
414 -- If a business rules fails the error will not be handled by this procedure
415 -- unless explicity coded.
416 --
417 -- Developer Implementation Notes:
418 -- For insert, your business rules should be coded within this procedure and
419 -- should ideally (unless really necessary) just be straight procedure or
420 -- function calls. Try and avoid using conditional branching logic.
421 --
422 -- {End Of Comments}
423 -- ----------------------------------------------------------------------------
424 Procedure insert_validate
425 (p_effective_date in date ,
426 p_rec in ota_trb_shd.g_rec_type ) is
427 --
428 l_proc varchar2(72) := g_package||'insert_validate';
429 l_resource_type varchar2(30);
430
431 cursor get_resource_type is
432 select resource_type
433 from ota_suppliable_resources
434 where supplied_resource_id = p_rec.supplied_resource_id;
435 --
436 Begin
437 hr_utility.set_location('Entering:'||l_proc, 5);
438 --
439 -- Call all supporting business operations
440 --
441 --
442 IF p_rec.event_id is not null then
443 ota_evt_bus.set_security_group_id(p_rec.event_id);
444 ELSE
445 ota_tsr_bus.set_security_group_id(p_rec.supplied_resource_id);
446 END IF;
447 --
448 -- Check the role_to_play field.
449 --
450 IF p_rec.role_to_play is not null THEN
451 ota_trb_api_procedures.check_role_to_play(p_rec.role_to_play);
452 END IF;
453 --
454 -- Check role_to_play is allowed to be populated.
455 --
456 ota_trb_api_procedures.check_role_res_type_excl(p_rec.supplied_resource_id,
457 p_rec.role_to_play);
458 --
459 -- Check quantity_entered is allowed to be populated.
460 --
461 IF p_rec.quantity is not null THEN
462 ota_trb_api_procedures.check_quantity_entered(p_rec.supplied_resource_id,
463 p_rec.quantity);
464 END IF;
465 --
466 -- Check delivery address is allowed to be populated.
467 --
468 IF p_rec.deliver_to is not null THEN
469 ota_trb_api_procedures.check_delivery_address(p_rec.supplied_resource_id,
470 p_rec.deliver_to);
471 END IF;
472 --
473 -- Check required dates validity.
474 --
475 ota_trb_api_procedures.check_from_to_dates(p_rec.required_date_from,
476 p_rec.required_date_to);
477 --
478 -- Check validity of required dates within suppliable resource
479 -- availability.
480 --
481 ota_trb_api_procedures.check_dates_tsr(p_rec.supplied_resource_id,
482 p_rec.required_date_from,
483 p_rec.required_date_to,
484 p_rec.required_start_time,
485 p_rec.required_end_time,
486 p_rec.timezone_code);
487 /*
488 ota_trb_api_procedures.check_obj_booking_dates(p_rec.supplied_resource_id,
489 p_rec.required_date_from,
490 p_rec.required_date_to,
491 p_rec.event_id,
492 p_rec.chat_id,
493 p_rec.forum_id,
494 p_rec.timezone_code,
495 p_rec.required_start_time,
496 p_rec.required_end_time);
497 */
498 --
499 -- Check the validity of the business_group_id.
500 --
501 if p_rec.event_id is not null then
502 ota_trb_api_procedures.check_evt_tsr_bus_grp(p_rec.event_id,
503 p_rec.supplied_resource_id);
504 end if;
505 --
506 -- Check user status domain.
507 --
508 IF p_rec.status is not null THEN
509 ota_trb_api_procedures.check_status(p_rec.status);
510 END IF;
511 --
512 -- Check that only one resource marked as a primary venue.
513 --
514 IF p_rec.primary_venue_flag is not null THEN
515 ota_trb_api_procedures.check_primary_venue(p_rec.event_id,
516 p_rec.resource_booking_id,
517 p_rec.primary_venue_flag,
518 p_rec.required_date_from,
519 p_rec.required_date_to);
520 END IF;
521 --
522 if p_rec.status = 'C' then
523
524 open get_resource_type;
525 fetch get_resource_type into l_resource_type;
526 close get_resource_type;
527
528 -- no double booking check for forum and chat Trainer(Moderator).
529 if( (l_resource_type = 'T') and (p_rec.chat_id is not null or p_rec.forum_id is not null)) then
530 null;
531 else
532 ota_trb_api_procedures.check_trainer_venue_book
533 (p_rec.supplied_resource_id
534 ,p_rec.required_date_from
535 ,p_rec.required_start_time
536 ,p_rec.required_date_to
537 ,p_rec.required_end_time
538 ,p_rec.resource_booking_id
539 ,p_rec.book_entire_period_flag
540 ,p_rec.timezone_code);
541 end if;
542 end if;
543 --
544 -- Check the event type
545 --
546 if p_rec.event_id is not null then
547 ota_trb_api_procedures.check_event_type(p_rec.event_id);
548 end if;
549 --
550
551 --
552 -- Check start time is after end time.
553 --
554 -- Added date check condition for bug# 3183071
555 If (trunc(p_rec.required_date_from) = trunc(p_rec.required_date_to)) then
556 ota_trb_api_procedures.check_start_end_times(p_rec.required_start_time
557 ,p_rec.required_end_time);
558 End if;
559
560 check_timezone(p_rec.timezone_code);
561
562 --Added for bug#5572125
563
564 check_res_bkng_time(p_rec.required_start_time
565 ,p_rec.required_end_time
566 ,p_rec.book_entire_period_flag);
567
568 --
569 ota_trb_bus.chk_ddf(p_rec);
570 hr_utility.set_location(' Leaving:'||l_proc, 10);
571 --
572 End insert_validate;
573 --
574 -- ----------------------------------------------------------------------------
575 -- |---------------------------< update_validate >----------------------------|
576 -- ----------------------------------------------------------------------------
577 -- {Start Of Comments}
578 --
579 -- Description:
580 -- This procedure controls the execution of all update business rules
581 -- validation.
582 --
583 -- Pre Conditions:
584 -- This private procedure is called from upd procedure.
585 --
586 -- In Arguments:
587 -- A Pl/Sql record structre.
588 --
589 -- Post Success:
590 -- Processing continues.
591 --
592 -- Post Failure:
593 -- If a business rules fails the error will not be handled by this procedure
594 -- unless explicity coded.
595 --
596 -- Developer Implementation Notes:
597 -- For update, your business rules should be coded within this procedure and
598 -- should ideally (unless really necessary) just be straight procedure or
599 -- function calls. Try and avoid using conditional branching logic.
600 --
601 -- {End Of Comments}
602 -- ----------------------------------------------------------------------------
603 Procedure update_validate
604 (p_effective_date in date
605 ,p_rec in ota_trb_shd.g_rec_type
606 ) is
607 --
608 l_supplied_resource_id_changed boolean :=
609 ota_general.value_changed(ota_trb_shd.g_old_rec.supplied_resource_id,
610 p_rec.supplied_resource_id);
611 --
612 l_event_id_changed boolean :=
613 ota_general.value_changed(ota_trb_shd.g_old_rec.event_id,
614 p_rec.event_id);
615 --
616 l_role_to_play_changed boolean :=
617 ota_general.value_changed(ota_trb_shd.g_old_rec.role_to_play,
618 p_rec.role_to_play);
619 --
620 l_quantity_changed boolean :=
621 ota_general.value_changed(ota_trb_shd.g_old_rec.quantity,
622 p_rec.quantity);
623 --
624 l_deliver_to_changed boolean :=
625 ota_general.value_changed(ota_trb_shd.g_old_rec.deliver_to,
626 p_rec.deliver_to);
627 --
628 l_number_del_per_unit_changed boolean :=
629 ota_general.value_changed(ota_trb_shd.g_old_rec.delegates_per_unit,
630 p_rec.delegates_per_unit);
631 --
632 l_required_date_from_changed boolean :=
633 ota_general.value_changed(ota_trb_shd.g_old_rec.required_date_from,
634 p_rec.required_date_from);
635 --
636 l_required_start_time_changed boolean :=
637 ota_general.value_changed(ota_trb_shd.g_old_rec.required_start_time,
638 p_rec.required_start_time);
639 --
640 l_required_date_to_changed boolean :=
641 ota_general.value_changed(ota_trb_shd.g_old_rec.required_date_to,
642 p_rec.required_date_to);
643 --
644 l_required_end_time_changed boolean :=
645 ota_general.value_changed(ota_trb_shd.g_old_rec.required_end_time,
646 p_rec.required_end_time);
647 --
648 l_status_changed boolean :=
649 ota_general.value_changed(ota_trb_shd.g_old_rec.status,
650 p_rec.status);
651 --
652 l_primary_venue_flag_changed boolean :=
653 ota_general.value_changed(ota_trb_shd.g_old_rec.primary_venue_flag,
654 p_rec.primary_venue_flag);
655
656 l_book_entire_period_changed boolean :=
657 ota_general.value_changed(ota_trb_shd.g_old_rec.book_entire_period_flag,
658 p_rec.book_entire_period_flag);
659 l_timezone_changed boolean :=
660 ota_general.value_changed(ota_trb_shd.g_old_rec.timezone_code,
661 p_rec.timezone_code);
662 --
663 --
664 l_proc varchar2(72) := g_package||'update_validate';
665 l_resource_type varchar2(30);
666
667 cursor get_resource_type is
668 select resource_type
669 from ota_suppliable_resources
670 where supplied_resource_id = p_rec.supplied_resource_id;
671
672 --
673 Begin
674 hr_utility.set_location('Entering:'||l_proc, 5);
675 --
676 -- Call all supporting business operations
677 --
678 --
679 IF ota_trb_shd.g_old_rec.event_id is not null then
680 ota_evt_bus.set_security_group_id(ota_trb_shd.g_old_rec.event_id);
681 ELSIF (ota_trb_shd.g_old_rec.supplied_resource_id = p_rec.supplied_resource_id) THEN
682 ota_tsr_bus.set_security_group_id(ota_trb_shd.g_old_rec.supplied_resource_id);
683 ELSE
684 ota_tsr_bus.set_security_group_id(p_rec.supplied_resource_id);
685 END IF;
686 --
687 --
688 chk_non_updateable_args
689 (p_effective_date => p_effective_date
690 ,p_rec => p_rec
691 );
692 --
693 IF l_event_id_changed THEN
694 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
695 hr_utility.set_message_token('PROCEDURE',l_proc);
696 hr_utility.set_message_token('STEP','2');
697 hr_utility.raise_error;
698 END IF;
699 --
700 -- Check the role_to_play field.
701 --
702 IF l_role_to_play_changed and p_rec.role_to_play is not null THEN
703 ota_trb_api_procedures.check_role_to_play(p_rec.role_to_play);
704 END IF;
705 --
706 -- Check quantity_entered is allowed to be populated.
707 --
708 IF l_quantity_changed THEN
709 ota_trb_api_procedures.check_quantity_entered(p_rec.supplied_resource_id,
710 p_rec.quantity);
711 END IF;
712 --
713 -- Check delivery address is allowed to be populated.
714 --
715 IF l_deliver_to_changed THEN
716 ota_trb_api_procedures.check_delivery_address(p_rec.supplied_resource_id,
717 p_rec.deliver_to);
718 END IF;
719 --
720 -- check required dates validity and default if necessary.
721 --
722 /*Adding the start_time change or end_time change or timezone change to this if,for bug6078493*/
723
724 IF (l_required_date_from_changed OR l_required_date_to_changed
725 OR l_required_start_time_changed OR l_required_end_time_changed OR l_timezone_changed) THEN
726 ota_trb_api_procedures.check_from_to_dates(p_rec.required_date_from,
727 p_rec.required_date_to);
728 ota_trb_api_procedures.check_dates_tsr(p_rec.supplied_resource_id,
729 p_rec.required_date_from,
730 p_rec.required_date_to,
731 p_rec.required_start_time,
732 p_rec.required_end_time,
733 p_rec.timezone_code);
734 /*
735 ota_trb_api_procedures.check_obj_booking_dates(p_rec.supplied_resource_id,
736 p_rec.required_date_from,
737 p_rec.required_date_to,
738 p_rec.event_id,
739 p_rec.chat_id,
740 p_rec.forum_id,
741 p_rec.timezone_code,
742 p_rec.required_start_time,
743 p_rec.required_end_time);
744 */
745
746 END IF;
747 --
748 -- Check status validity.
749 --
750 IF l_status_changed THEN
751 ota_trb_api_procedures.check_status(p_rec.status);
752 END IF;
753 --
754 -- Check primary venue marked only once.
755 --
756 IF (l_primary_venue_flag_changed or
757 l_required_date_from_changed or
758 l_required_date_to_changed)
759 AND p_rec.primary_venue_flag = 'Y' THEN
760 --
761 ota_trb_api_procedures.check_primary_venue(p_rec.event_id,
762 p_rec.resource_booking_id,
763 p_rec.primary_venue_flag,
764 p_rec.required_date_from,
765 p_rec.required_date_to);
766 --
767 END IF;
768 --
769 if (l_supplied_resource_id_changed or
770 l_required_date_from_changed or
771 l_required_date_to_changed or
772 l_required_start_time_changed or
773 l_required_end_time_changed or
774 l_book_entire_period_changed OR
775 l_timezone_changed OR
776 l_status_changed)
777 and p_rec.status = 'C' then
778 --
779 open get_resource_type;
780 fetch get_resource_type into l_resource_type;
781 close get_resource_type;
782
783 -- no double booking check for forum and chat Trainer(Moderator).
784 if( (l_resource_type = 'T') and (p_rec.chat_id is not null or p_rec.forum_id is not null)) then
785 null;
786 else
787 ota_trb_api_procedures.check_trainer_venue_book
788 (p_rec.supplied_resource_id
789 ,p_rec.required_date_from
790 ,p_rec.required_start_time
791 ,p_rec.required_date_to
792 ,p_rec.required_end_time
793 ,p_rec.resource_booking_id
794 ,p_rec.book_entire_period_flag
795 ,p_rec.timezone_code);
796 end if;
797 end if;
798 --
799
800 --
801 -- Check start time is after end time.
802 --
803 -- added date check condition for bug# 3183071
804 if (l_required_start_time_changed or l_required_end_time_changed)
805 and (trunc(p_rec.required_date_from) = trunc(p_rec.required_date_to)) then
806 --
807 ota_trb_api_procedures.check_start_end_times(p_rec.required_start_time
808 ,p_rec.required_end_time);
809
810 --
811 end if;
812
813 --Added for bug#5572125
814 check_res_bkng_time(p_rec.required_start_time
815 ,p_rec.required_end_time
816 ,p_rec.book_entire_period_flag);
817
818 IF p_rec.timezone_code <> hr_api.g_varchar2 THEN
819 check_timezone(p_rec.timezone_code);
820 END IF;
821
822 --
823 ota_trb_bus.chk_ddf(p_rec);
824 hr_utility.set_location(' Leaving:'||l_proc, 10); End update_validate;
825 --
826 -- ----------------------------------------------------------------------------
827 -- |---------------------------< delete_validate >----------------------------|
828 -- ----------------------------------------------------------------------------
829 -- {Start Of Comments}
830 --
831 -- Description:
832 -- This procedure controls the execution of all delete business rules
833 -- validation.
834 --
835 -- Pre Conditions:
836 -- This private procedure is called from del procedure.
837 --
838 -- In Arguments:
839 -- A Pl/Sql record structre.
840 --
841 -- Post Success:
842 -- Processing continues.
843 --
844 -- Post Failure:
845 -- If a business rules fails the error will not be handled by this procedure
846 -- unless explicity coded.
847 --
848 -- Developer Implementation Notes:
849 -- For delete, your business rules should be coded within this procedure and
850 -- should ideally (unless really necessary) just be straight procedure or
851 -- function calls. Try and avoid using conditional branching logic.
852 --
853 -- {End Of Comments}
854 -- ----------------------------------------------------------------------------
855 Procedure delete_validate
856 (p_rec in ota_trb_shd.g_rec_type
857 ) is
858 --
859 l_proc varchar2(72) := g_package||'delete_validate';
860 --
861 Begin
862 hr_utility.set_location('Entering:'||l_proc, 5);
863 --
864 -- Call all supporting business operations
865 --
866 hr_utility.set_location(' Leaving:'||l_proc, 10);
867 End delete_validate;
868 --
869 end ota_trb_bus;
[Home] [Help]