1 Package Body ota_trb_ins 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_ins.'; -- Global package name
9 --
10 -- The following global variables are only to be used by
11 -- the set_base_key_value and pre_insert procedures.
12 --
13 g_resource_booking_id_i number default null;
14 --
15 -- ----------------------------------------------------------------------------
16 -- |------------------------< set_base_key_value >----------------------------|
17 -- ----------------------------------------------------------------------------
18 procedure set_base_key_value
19 (p_resource_booking_id in number) is
20 --
21 l_proc varchar2(72) := g_package||'set_base_key_value';
22 --
23 Begin
24 hr_utility.set_location('Entering:'||l_proc, 10);
25 --
26 ota_trb_ins.g_resource_booking_id_i := p_resource_booking_id;
27 --
28 hr_utility.set_location(' Leaving:'||l_proc, 20);
29 End set_base_key_value;
30 --
31 --
32 -- ----------------------------------------------------------------------------
33 -- |------------------------------< insert_dml >------------------------------|
34 -- ----------------------------------------------------------------------------
35 -- {Start Of Comments}
36 --
37 -- Description:
38 -- This procedure controls the actual dml insert logic. The processing of
39 -- this procedure are as follows:
40 -- 1) Initialise the object_version_number to 1 if the object_version_number
41 -- is defined as an attribute for this entity.
42 -- 2) To set and unset the g_api_dml status as required (as we are about to
43 -- perform dml).
44 -- 3) To insert the row into the schema.
45 -- 4) To trap any constraint violations that may have occurred.
46 -- 5) To raise any other errors.
47 --
48 -- Prerequisites:
49 -- This is an internal private procedure which must be called from the ins
50 -- procedure and must have all mandatory attributes set (except the
51 -- object_version_number which is initialised within this procedure).
52 --
53 -- In Parameters:
54 -- A Pl/Sql record structre.
55 --
56 -- Post Success:
57 -- The specified row will be inserted into the schema.
58 --
59 -- Post Failure:
60 -- On the insert dml failure it is important to note that we always reset the
61 -- g_api_dml status to false.
62 -- If a check, unique or parent integrity constraint violation is raised the
63 -- constraint_error procedure will be called.
64 -- If any other error is reported, the error will be raised after the
65 -- g_api_dml status is reset.
66 --
67 -- Developer Implementation Notes:
68 -- None.
69 --
70 -- Access Status:
71 -- Internal Row Handler Use Only.
72 --
73 -- {End Of Comments}
74 -- ----------------------------------------------------------------------------
75 Procedure insert_dml
76 (p_rec in out nocopy ota_trb_shd.g_rec_type
77 ) is
78 --
79 l_proc varchar2(72) := g_package||'insert_dml';
80 --
81 Begin
82 hr_utility.set_location('Entering:'||l_proc, 5);
83 p_rec.object_version_number := 1; -- Initialise the object version
84 --
85 ota_trb_shd.g_api_dml := true; -- Set the api dml status
86 --
87 -- Insert the row into: ota_resource_bookings
88 --
89 insert into ota_resource_bookings
90 (resource_booking_id
91 ,supplied_resource_id
92 ,event_id
93 ,date_booking_placed
94 ,object_version_number
95 ,status
96 ,absolute_price
97 ,booking_person_id
98 ,comments
99 ,contact_name
100 ,contact_phone_number
101 ,delegates_per_unit
102 ,quantity
103 ,required_date_from
104 ,required_date_to
105 ,required_end_time
106 ,required_start_time
107 ,deliver_to
108 ,primary_venue_flag
109 ,role_to_play
110 ,trb_information_category
111 ,trb_information1
112 ,trb_information2
113 ,trb_information3
114 ,trb_information4
115 ,trb_information5
116 ,trb_information6
117 ,trb_information7
118 ,trb_information8
119 ,trb_information9
120 ,trb_information10
121 ,trb_information11
122 ,trb_information12
123 ,trb_information13
124 ,trb_information14
125 ,trb_information15
126 ,trb_information16
127 ,trb_information17
128 ,trb_information18
129 ,trb_information19
130 ,trb_information20
131 ,display_to_learner_flag
132 ,book_entire_period_flag
133 -- ,unbook_request_flag
134 ,chat_id
135 ,forum_id
136 ,timezone_code
137 )
138 Values
139 (p_rec.resource_booking_id
140 ,p_rec.supplied_resource_id
141 ,p_rec.event_id
142 ,p_rec.date_booking_placed
143 ,p_rec.object_version_number
144 ,p_rec.status
145 ,p_rec.absolute_price
146 ,p_rec.booking_person_id
147 ,p_rec.comments
148 ,p_rec.contact_name
149 ,p_rec.contact_phone_number
150 ,p_rec.delegates_per_unit
151 ,p_rec.quantity
152 ,p_rec.required_date_from
153 ,p_rec.required_date_to
154 ,p_rec.required_end_time
155 ,p_rec.required_start_time
156 ,p_rec.deliver_to
157 ,p_rec.primary_venue_flag
158 ,p_rec.role_to_play
159 ,p_rec.trb_information_category
160 ,p_rec.trb_information1
161 ,p_rec.trb_information2
162 ,p_rec.trb_information3
163 ,p_rec.trb_information4
164 ,p_rec.trb_information5
165 ,p_rec.trb_information6
166 ,p_rec.trb_information7
167 ,p_rec.trb_information8
168 ,p_rec.trb_information9
169 ,p_rec.trb_information10
170 ,p_rec.trb_information11
171 ,p_rec.trb_information12
172 ,p_rec.trb_information13
173 ,p_rec.trb_information14
174 ,p_rec.trb_information15
175 ,p_rec.trb_information16
176 ,p_rec.trb_information17
177 ,p_rec.trb_information18
178 ,p_rec.trb_information19
179 ,p_rec.trb_information20
180 ,p_rec.display_to_learner_flag
181 ,p_rec.book_entire_period_flag
182 -- ,p_rec.unbook_request_flag
183 ,p_rec.chat_id
184 ,p_rec.forum_id
185 ,p_rec.timezone_code
186 );
187 --
188 ota_trb_shd.g_api_dml := false; -- Unset the api dml status
189 --
190 hr_utility.set_location(' Leaving:'||l_proc, 10);
191 Exception
192 When hr_api.check_integrity_violated Then
193 -- A check constraint has been violated
194 ota_trb_shd.g_api_dml := false; -- Unset the api dml status
195 ota_trb_shd.constraint_error
196 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
197 When hr_api.parent_integrity_violated Then
198 -- Parent integrity has been violated
199 ota_trb_shd.g_api_dml := false; -- Unset the api dml status
200 ota_trb_shd.constraint_error
201 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
202 When hr_api.unique_integrity_violated Then
203 -- Unique integrity has been violated
204 ota_trb_shd.g_api_dml := false; -- Unset the api dml status
205 ota_trb_shd.constraint_error
206 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
207 When Others Then
208 ota_trb_shd.g_api_dml := false; -- Unset the api dml status
209 Raise;
210 End insert_dml;
211 --
212 -- ----------------------------------------------------------------------------
213 -- |------------------------------< pre_insert >------------------------------|
214 -- ----------------------------------------------------------------------------
215 -- {Start Of Comments}
216 --
217 -- Description:
218 -- This private procedure contains any processing which is required before
219 -- the insert dml. Presently, if the entity has a corresponding primary
220 -- key which is maintained by an associating sequence, the primary key for
221 -- the entity will be populated with the next sequence value in
222 -- preparation for the insert dml.
223 --
224 -- Prerequisites:
225 -- This is an internal procedure which is called from the ins procedure.
226 --
227 -- In Parameters:
228 -- A Pl/Sql record structure.
229 --
230 -- Post Success:
231 -- Processing continues.
232 --
233 -- Post Failure:
234 -- If an error has occurred, an error message and exception will be raised
235 -- but not handled.
236 --
237 -- Developer Implementation Notes:
238 -- Any pre-processing required before the insert dml is issued should be
239 -- coded within this procedure. As stated above, a good example is the
240 -- generation of a primary key number via a corresponding sequence.
241 -- It is important to note that any 3rd party maintenance should be reviewed
242 -- before placing in this procedure.
243 --
244 -- Access Status:
245 -- Internal Row Handler Use Only.
246 --
247 -- {End Of Comments}
248 -- ----------------------------------------------------------------------------
249 Procedure pre_insert
250 (p_rec in out nocopy ota_trb_shd.g_rec_type
251 ) is
252 --
253 Cursor C_Sel1 is select ota_resource_bookings_s.nextval from sys.dual;
254 --
255 -- Cursor to check Automatic Primary Venue
256 --
257 Cursor C_Sel2 is
258 Select null
259 From ota_resource_bookings orb
260 Where orb.event_id = p_rec.event_id
261 and orb.primary_venue_flag = 'Y'
262 and orb.supplied_resource_id in(
263 Select osr.supplied_resource_id
264 from ota_suppliable_resources osr
265 where osr.resource_type = 'V');
266 --
267 Cursor C_Sel3 is
268 SELECT resource_type
269 FROM OTA_SUPPLIABLE_RESOURCES
270 WHERE SUPPLIED_RESOURCE_ID = p_rec.supplied_resource_id;
271 --
272 Cursor C_Sel4 is
273 Select null
274 from ota_resource_bookings
275 where resource_booking_id =
276 ota_trb_ins.g_resource_booking_id_i;
277 --
278 l_proc varchar2(72) := g_package||'pre_insert';
279 l_exist varchar2(1);
280 l_resource_type ota_suppliable_resources.resource_type%type;
281 --
282 Begin
283 hr_utility.set_location('Entering:'||l_proc, 5);
284 --
285 If (ota_trb_ins.g_resource_booking_id_i is not null) Then
286 --
287 -- Verify registered primary key values not already in use
288 --
289 Open C_Sel4;
290 Fetch C_Sel4 into l_exist;
291 If C_Sel4%found Then
292 Close C_Sel4;
293 --
294 -- The primary key values are already in use.
295 --
296 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
297 fnd_message.set_token('TABLE_NAME','ota_resource_bookings');
298 fnd_message.raise_error;
299 End If;
300 Close C_Sel4;
301 --
302 -- Use registered key values and clear globals
303 --
304 p_rec.resource_booking_id :=
305 ota_trb_ins.g_resource_booking_id_i;
306 ota_trb_ins.g_resource_booking_id_i := null;
307 Else
308 --
309 -- No registerd key values, so select the next sequence number
310 --
311 --
312 -- Select the next sequence number
313 --
314 Open C_Sel1;
315 Fetch C_Sel1 Into p_rec.resource_booking_id;
316 Close C_Sel1;
317 End If;
318 --
319 -- Check if there's a existing Primary Venue
320 --
321 If p_rec.event_id is not null then
322 Open C_Sel2;
323 Fetch C_Sel2 into l_exist;
324 If C_Sel2%notfound Then
325 OPEN C_Sel3;
326 FETCH C_Sel3 INTO l_resource_type;
327 IF l_resource_type = 'V' then
328 --
329 -- Check the Primary Venue as a Primary if is not
330 --
331 If p_rec.primary_venue_flag ='N' then
332 p_rec.primary_venue_flag:='Y';
333 End If;
334 END IF;
335 CLOSE C_Sel3;
336 End If;
337 Close C_Sel2;
338 End If; -- Check if there's a existing Primary Venue
339 --
340 If p_rec.event_id is not null then
341 Open C_Sel2;
342 Fetch C_Sel2 into l_exist;
343 If C_Sel2%notfound Then
344 OPEN C_Sel3;
345 FETCH C_Sel3 INTO l_resource_type;
346 IF l_resource_type = 'V' then
347 --
348 -- Check the Primary Venue as a Primary if is not
349 --
350 If p_rec.primary_venue_flag ='N' then
351 p_rec.primary_venue_flag:='Y';
352 End If;
353 END IF;
354 CLOSE C_Sel3;
355 End If;
356 Close C_Sel2;
357 End If;
358 --
359 --
360 hr_utility.set_location(' Leaving:'||l_proc, 10);
361 End pre_insert;
362 --
363 -- ----------------------------------------------------------------------------
364 -- |-----------------------------< post_insert >------------------------------|
365 -- ----------------------------------------------------------------------------
366 -- {Start Of Comments}
367 --
368 -- Description:
369 -- This private procedure contains any processing which is required after
370 -- the insert dml.
371 --
372 -- Prerequisites:
373 -- This is an internal procedure which is called from the ins procedure.
374 --
375 -- In Parameters:
376 -- A Pl/Sql record structre.
377 --
378 -- Post Success:
379 -- Processing continues.
380 --
381 -- Post Failure:
382 -- If an error has occurred, an error message and exception will be raised
383 -- but not handled.
384 --
385 -- Developer Implementation Notes:
386 -- Any post-processing required after the insert dml is issued should be
387 -- coded within this procedure. It is important to note that any 3rd party
388 -- maintenance should be reviewed before placing in this procedure.
389 --
390 -- Access Status:
391 -- Internal Row Handler Use Only.
392 --
393 -- {End Of Comments}
394 -- ----------------------------------------------------------------------------
395 Procedure post_insert
396 (p_effective_date in date
397 ,p_rec in ota_trb_shd.g_rec_type
398 ) is
399 --
400 l_proc varchar2(72) := g_package||'post_insert';
401 --
402 Begin
403 hr_utility.set_location('Entering:'||l_proc, 5);
404 begin
405 --
406 ota_trb_rki.after_insert
407 (p_effective_date => p_effective_date
408 ,p_resource_booking_id
409 => p_rec.resource_booking_id
410 ,p_supplied_resource_id
411 => p_rec.supplied_resource_id
412 ,p_event_id
413 => p_rec.event_id
414 ,p_date_booking_placed
415 => p_rec.date_booking_placed
416 ,p_object_version_number
417 => p_rec.object_version_number
418 ,p_status
419 => p_rec.status
420 ,p_absolute_price
421 => p_rec.absolute_price
422 ,p_booking_person_id
423 => p_rec.booking_person_id
424 ,p_comments
425 => p_rec.comments
426 ,p_contact_name
427 => p_rec.contact_name
428 ,p_contact_phone_number
429 => p_rec.contact_phone_number
430 ,p_delegates_per_unit
431 => p_rec.delegates_per_unit
432 ,p_quantity
433 => p_rec.quantity
434 ,p_required_date_from
435 => p_rec.required_date_from
436 ,p_required_date_to
437 => p_rec.required_date_to
438 ,p_required_end_time
439 => p_rec.required_end_time
440 ,p_required_start_time
441 => p_rec.required_start_time
442 ,p_deliver_to
443 => p_rec.deliver_to
444 ,p_primary_venue_flag
445 => p_rec.primary_venue_flag
446 ,p_role_to_play
447 => p_rec.role_to_play
448 ,p_trb_information_category
449 => p_rec.trb_information_category
450 ,p_trb_information1
451 => p_rec.trb_information1
452 ,p_trb_information2
453 => p_rec.trb_information2
454 ,p_trb_information3
455 => p_rec.trb_information3
456 ,p_trb_information4
457 => p_rec.trb_information4
458 ,p_trb_information5
459 => p_rec.trb_information5
460 ,p_trb_information6
461 => p_rec.trb_information6
462 ,p_trb_information7
463 => p_rec.trb_information7
464 ,p_trb_information8
465 => p_rec.trb_information8
466 ,p_trb_information9
467 => p_rec.trb_information9
468 ,p_trb_information10
469 => p_rec.trb_information10
470 ,p_trb_information11
471 => p_rec.trb_information11
472 ,p_trb_information12
473 => p_rec.trb_information12
474 ,p_trb_information13
475 => p_rec.trb_information13
476 ,p_trb_information14
477 => p_rec.trb_information14
478 ,p_trb_information15
479 => p_rec.trb_information15
480 ,p_trb_information16
481 => p_rec.trb_information16
482 ,p_trb_information17
483 => p_rec.trb_information17
484 ,p_trb_information18
485 => p_rec.trb_information18
486 ,p_trb_information19
487 => p_rec.trb_information19
488 ,p_trb_information20
489 => p_rec.trb_information20
490 ,p_display_to_learner_flag
491 => p_rec.display_to_learner_flag
492 ,p_book_entire_period_flag
493 => p_rec.book_entire_period_flag
494 /* ,p_unbook_request_flag
495 => p_rec.unbook_request_flag*/
496 ,p_chat_id
497 => p_rec.chat_id
498 ,p_forum_id
499 => p_rec.forum_id
500 ,p_timezone_code
501 => p_rec.timezone_code
502 );
503 --
504 exception
505 --
506 when hr_api.cannot_find_prog_unit then
507 --
508 hr_api.cannot_find_prog_unit_error
509 (p_module_name => 'OTA_RESOURCE_BOOKINGS'
510 ,p_hook_type => 'AI');
511 --
512 end;
513 --
514 hr_utility.set_location(' Leaving:'||l_proc, 10);
515 End post_insert;
516 --
517 -- ----------------------------------------------------------------------------
518 -- |---------------------------------< ins >----------------------------------|
519 -- ----------------------------------------------------------------------------
520 Procedure ins
521 (p_effective_date in date
522 ,p_rec in out nocopy ota_trb_shd.g_rec_type
523 ) is
524 --
525 l_proc varchar2(72) := g_package||'ins';
526 --
527 Begin
528 hr_utility.set_location('Entering:'||l_proc, 5);
529 --
530 -- Call the supporting insert validate operations
531 --
532 ota_trb_bus.insert_validate
533 (p_effective_date
534 ,p_rec
535 );
536 --
537 -- Call to raise any errors on multi-message list
538 hr_multi_message.end_validation_set;
539 --
540 -- Call the supporting pre-insert operation
541 --
542 ota_trb_ins.pre_insert(p_rec);
543 --
544 -- Insert the row
545 --
546 ota_trb_ins.insert_dml(p_rec);
547 --
548 -- Call the supporting post-insert operation
549 --
550 ota_trb_ins.post_insert
551 (p_effective_date
552 ,p_rec
553 );
554 --
555 -- Call to raise any errors on multi-message list
556 hr_multi_message.end_validation_set;
557 --
558 hr_utility.set_location('Leaving:'||l_proc, 20);
559 end ins;
560 --
561 -- ----------------------------------------------------------------------------
562 -- |---------------------------------< ins >----------------------------------|
563 -- ----------------------------------------------------------------------------
564 Procedure ins
565 (p_effective_date in date
566 ,p_supplied_resource_id in number
567 ,p_date_booking_placed in date
568 ,p_status in varchar2
569 ,p_event_id in number default null
570 ,p_absolute_price in number default null
571 ,p_booking_person_id in number default null
572 ,p_comments in varchar2 default null
573 ,p_contact_name in varchar2 default null
574 ,p_contact_phone_number in varchar2 default null
575 ,p_delegates_per_unit in number default null
576 ,p_quantity in number default null
577 ,p_required_date_from in date default null
578 ,p_required_date_to in date default null
579 ,p_required_end_time in varchar2 default null
580 ,p_required_start_time in varchar2 default null
581 ,p_deliver_to in varchar2 default null
582 ,p_primary_venue_flag in varchar2 default null
583 ,p_role_to_play in varchar2 default null
584 ,p_trb_information_category in varchar2 default null
585 ,p_trb_information1 in varchar2 default null
586 ,p_trb_information2 in varchar2 default null
587 ,p_trb_information3 in varchar2 default null
588 ,p_trb_information4 in varchar2 default null
589 ,p_trb_information5 in varchar2 default null
590 ,p_trb_information6 in varchar2 default null
591 ,p_trb_information7 in varchar2 default null
592 ,p_trb_information8 in varchar2 default null
593 ,p_trb_information9 in varchar2 default null
594 ,p_trb_information10 in varchar2 default null
595 ,p_trb_information11 in varchar2 default null
596 ,p_trb_information12 in varchar2 default null
597 ,p_trb_information13 in varchar2 default null
598 ,p_trb_information14 in varchar2 default null
599 ,p_trb_information15 in varchar2 default null
600 ,p_trb_information16 in varchar2 default null
601 ,p_trb_information17 in varchar2 default null
602 ,p_trb_information18 in varchar2 default null
603 ,p_trb_information19 in varchar2 default null
604 ,p_trb_information20 in varchar2 default null
605 ,p_display_to_learner_flag in varchar2 default null
606 ,p_book_entire_period_flag in varchar2 default null
607 -- ,p_unbook_request_flag in varchar2 default null
608 ,p_chat_id in number default null
609 ,p_forum_id in number default null
610 ,p_resource_booking_id out nocopy number
611 ,p_object_version_number out nocopy number
612 ,p_timezone_code IN VARCHAR2 DEFAULT NULL
613 ) is
614 --
615 l_rec ota_trb_shd.g_rec_type;
616 l_proc varchar2(72) := g_package||'ins';
617 --
618 Begin
619 hr_utility.set_location('Entering:'||l_proc, 5);
620 --
621 -- Call conversion function to turn arguments into the
622 -- p_rec structure.
623 --
624 l_rec :=
625 ota_trb_shd.convert_args
626 (null
627 ,p_supplied_resource_id
628 ,p_event_id
629 ,p_date_booking_placed
630 ,null
631 ,p_status
632 ,p_absolute_price
633 ,p_booking_person_id
634 ,p_comments
635 ,p_contact_name
636 ,p_contact_phone_number
637 ,p_delegates_per_unit
638 ,p_quantity
639 ,p_required_date_from
640 ,p_required_date_to
641 ,p_required_end_time
642 ,p_required_start_time
643 ,p_deliver_to
644 ,p_primary_venue_flag
645 ,p_role_to_play
646 ,p_trb_information_category
647 ,p_trb_information1
648 ,p_trb_information2
649 ,p_trb_information3
650 ,p_trb_information4
651 ,p_trb_information5
652 ,p_trb_information6
653 ,p_trb_information7
654 ,p_trb_information8
655 ,p_trb_information9
656 ,p_trb_information10
657 ,p_trb_information11
658 ,p_trb_information12
659 ,p_trb_information13
660 ,p_trb_information14
661 ,p_trb_information15
662 ,p_trb_information16
663 ,p_trb_information17
664 ,p_trb_information18
665 ,p_trb_information19
666 ,p_trb_information20
667 ,p_display_to_learner_flag
668 ,p_book_entire_period_flag
669 -- ,p_unbook_request_flag
670 ,p_chat_id
671 ,p_forum_id
672 ,p_timezone_code
673 );
674 --
675 -- Having converted the arguments into the ota_trb_rec
676 -- plsql record structure we call the corresponding record business process.
677 --
678 ota_trb_ins.ins
679 (p_effective_date
680 ,l_rec
681 );
682 --
683 -- As the primary key argument(s)
684 -- are specified as an OUT's we must set these values.
685 --
686 p_resource_booking_id := l_rec.resource_booking_id;
687 p_object_version_number := l_rec.object_version_number;
688 --
689 hr_utility.set_location(' Leaving:'||l_proc, 10);
690 End ins;
691 --
692 end ota_trb_ins;
[Home] [Help]