1 Package Body ota_tdb_ins as
2 /* $Header: ottdb01t.pkb 120.26 2008/04/22 12:43:15 smahanka noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_tdb_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_booking_id_i number default null;
14 --
15 -- ----------------------------------------------------------------------------
16 -- |------------------------< set_base_key_value >----------------------------|
17 -- ----------------------------------------------------------------------------
18 procedure set_base_key_value
19 (p_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_tdb_ins.g_booking_id_i := p_booking_id;
27 --
28 hr_utility.set_location(' Leaving:'||l_proc, 20);
29 End set_base_key_value;
30 --
31 -- ----------------------------------------------------------------------------
32 -- |------------------------------< insert_dml >------------------------------|
33 -- ----------------------------------------------------------------------------
34 -- {Start Of Comments}
35 --
36 -- Description:
37 -- This procedure controls the actual dml insert logic. The functions of this
38 -- procedure are as follows:
39 -- 1) Initialise the object_version_number to 1 if the object_version_number
40 -- is defined as an attribute for this entity.
41 -- 2) To set and unset the g_api_dml status as required (as we are about to
42 -- perform dml).
43 -- 3) To insert the row into the schema.
44 -- 4) To trap any constraint violations that may have occurred.
45 -- 5) To raise any other errors.
46 --
47 -- Pre Conditions:
48 -- This is an internal private procedure which must be called from the ins
49 -- procedure and must have all mandatory arguments set (except the
50 -- object_version_number which is initialised within this procedure).
51 --
52 -- In Arguments:
53 -- A Pl/Sql record structre.
54 --
55 -- Post Success:
56 -- The specified row will be inserted into the schema.
57 --
58 -- Post Failure:
59 -- On the insert dml failure it is important to note that we always reset the
60 -- g_api_dml status to false.
61 -- If a check, unique or parent integrity constraint violation is raised the
62 -- constraint_error procedure will be called.
63 -- If any other error is reported, the error will be raised after the
64 -- g_api_dml status is reset.
65 --
66 -- Developer Implementation Notes:
67 -- None.
68 --
69 -- Access Status:
70 -- Internal Development Use Only.
71 --
72 -- {End Of Comments}
73 -- ----------------------------------------------------------------------------
74 Procedure insert_dml(p_rec in out nocopy ota_tdb_shd.g_rec_type) is
75 --
76 l_proc varchar2(72) := g_package||'insert_dml';
77 --
78 Begin
79 hr_utility.set_location('Entering:'||l_proc, 5);
80 p_rec.object_version_number := 1; -- Initialise the object version
81 --
82 ota_tdb_shd.g_api_dml := true; -- Set the api dml status
83 --
84 -- Insert the row into: ota_delegate_bookings
85 --
86 insert into ota_delegate_bookings
87 ( booking_id,
88 booking_status_type_id,
89 delegate_person_id,
90 contact_id,
91 business_group_id,
92 event_id,
93 customer_id,
94 authorizer_person_id,
95 date_booking_placed,
96 corespondent,
97 internal_booking_flag,
98 number_of_places,
99 object_version_number,
100 administrator,
101 booking_priority,
102 comments,
103 contact_address_id,
104 delegate_contact_phone,
105 delegate_contact_fax,
106 third_party_customer_id,
107 third_party_contact_id,
108 third_party_address_id,
109 third_party_contact_phone,
110 third_party_contact_fax,
111 date_status_changed,
112 failure_reason,
113 attendance_result,
114 language_id,
115 source_of_booking,
116 special_booking_instructions,
117 successful_attendance_flag,
118 tdb_information_category,
119 tdb_information1,
120 tdb_information2,
121 tdb_information3,
122 tdb_information4,
123 tdb_information5,
124 tdb_information6,
125 tdb_information7,
126 tdb_information8,
127 tdb_information9,
128 tdb_information10,
129 tdb_information11,
130 tdb_information12,
131 tdb_information13,
132 tdb_information14,
133 tdb_information15,
134 tdb_information16,
135 tdb_information17,
136 tdb_information18,
137 tdb_information19,
138 tdb_information20,
139 organization_id,
140 sponsor_person_id,
141 sponsor_assignment_id,
142 person_address_id,
143 delegate_assignment_id,
144 delegate_contact_id,
145 delegate_contact_email,
146 third_party_email,
147 person_address_type,
148 line_id,
149 org_id,
150 daemon_flag,
151 daemon_type,
152 old_event_id,
153 quote_line_id,
154 interface_source,
155 total_training_time,
156 content_player_status,
157 score,
158 completed_content,
159 total_content ,
160 booking_justification_id,
161 is_history_flag,
162 is_mandatory_enrollment
163 )
164 Values
165 ( p_rec.booking_id,
166 p_rec.booking_status_type_id,
167 p_rec.delegate_person_id,
168 p_rec.contact_id,
169 p_rec.business_group_id,
170 p_rec.event_id,
171 p_rec.customer_id,
172 p_rec.authorizer_person_id,
173 p_rec.date_booking_placed,
174 p_rec.corespondent,
175 p_rec.internal_booking_flag,
176 p_rec.number_of_places,
177 p_rec.object_version_number,
178 p_rec.administrator,
179 p_rec.booking_priority,
180 p_rec.comments,
181 p_rec.contact_address_id,
182 p_rec.delegate_contact_phone,
183 p_rec.delegate_contact_fax,
184 p_rec.third_party_customer_id,
185 p_rec.third_party_contact_id,
186 p_rec.third_party_address_id,
187 p_rec.third_party_contact_phone,
188 p_rec.third_party_contact_fax,
189 p_rec.date_status_changed,
190 p_rec.failure_reason,
191 p_rec.attendance_result,
192 p_rec.language_id,
193 p_rec.source_of_booking,
194 p_rec.special_booking_instructions,
195 p_rec.successful_attendance_flag,
196 p_rec.tdb_information_category,
197 p_rec.tdb_information1,
198 p_rec.tdb_information2,
199 p_rec.tdb_information3,
200 p_rec.tdb_information4,
201 p_rec.tdb_information5,
202 p_rec.tdb_information6,
203 p_rec.tdb_information7,
204 p_rec.tdb_information8,
205 p_rec.tdb_information9,
206 p_rec.tdb_information10,
207 p_rec.tdb_information11,
208 p_rec.tdb_information12,
209 p_rec.tdb_information13,
210 p_rec.tdb_information14,
211 p_rec.tdb_information15,
212 p_rec.tdb_information16,
213 p_rec.tdb_information17,
214 p_rec.tdb_information18,
215 p_rec.tdb_information19,
216 p_rec.tdb_information20,
217 p_rec.organization_id,
218 p_rec.sponsor_person_id,
219 p_rec.sponsor_assignment_id,
220 p_rec.person_address_id,
221 p_rec.delegate_assignment_id,
222 p_rec.delegate_contact_id,
223 p_rec.delegate_contact_email,
224 p_rec.third_party_email,
225 p_rec.person_address_type,
226 p_rec.line_id,
227 p_rec.org_id,
228 p_rec.daemon_flag,
229 p_rec.daemon_type,
230 p_rec.old_event_id,
231 p_rec.quote_line_id,
232 p_rec.interface_source,
233 p_rec.total_training_time,
234 p_rec.content_player_status,
235 p_rec.score,
236 p_rec.completed_content,
237 p_rec.total_content ,
238 p_rec.booking_justification_id,
239 p_rec.is_history_flag,
240 p_rec.is_mandatory_enrollment
241 );
242 --
243 ota_tdb_shd.g_api_dml := false; -- Unset the api dml status
244 --
245 hr_utility.set_location(' Leaving:'||l_proc, 10);
246 Exception
247 When hr_api.check_integrity_violated Then
248 -- A check constraint has been violated
249 ota_tdb_shd.g_api_dml := false; -- Unset the api dml status
250 ota_tdb_shd.constraint_error
251 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
252 When hr_api.parent_integrity_violated Then
253 -- Parent integrity has been violated
254 ota_tdb_shd.g_api_dml := false; -- Unset the api dml status
255 ota_tdb_shd.constraint_error
256 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
257 When hr_api.unique_integrity_violated Then
258 -- Unique integrity has been violated
259 ota_tdb_shd.g_api_dml := false; -- Unset the api dml status
260 ota_tdb_shd.constraint_error
261 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
262 When Others Then
263 ota_tdb_shd.g_api_dml := false; -- Unset the api dml status
264 Raise;
265 End insert_dml;
266 --
267 -- ----------------------------------------------------------------------------
268 -- |------------------------------< pre_insert >------------------------------|
269 -- ----------------------------------------------------------------------------
270 -- {Start Of Comments}
271 --
272 -- Description:
273 -- This private procedure contains any processing which is required before
274 -- the insert dml. Presently, if the entity has a corresponding primary
275 -- key which is maintained by an associating sequence, the primary key for
276 -- the entity will be populated with the next sequence value in
277 -- preparation for the insert dml.
278 --
279 -- Pre Conditions:
280 -- This is an internal procedure which is called from the ins procedure.
281 --
282 -- In Arguments:
283 -- A Pl/Sql record structre.
284 --
285 -- Post Success:
286 -- Processing continues.
287 --
288 -- Post Failure:
289 -- If an error has occurred, an error message and exception will be raised
290 -- but not handled.
291 --
292 -- Developer Implementation Notes:
293 -- Any pre-processing required before the insert dml is issued should be
294 -- coded within this procedure. As stated above, a good example is the
295 -- generation of a primary key number via a corresponding sequence.
296 -- It is important to note that any 3rd party maintenance should be reviewed
297 -- before placing in this procedure.
298 --
299 -- Access Status:
300 -- Internal Development Use Only.
301 --
302 -- {End Of Comments}
303 -- ----------------------------------------------------------------------------
304 Procedure pre_insert(p_rec in out nocopy ota_tdb_shd.g_rec_type) is
305 --
306 l_proc varchar2(72) := g_package||'pre_insert';
307 --
308 Cursor C_Sel1 is select ota_delegate_bookings_s.nextval from sys.dual;
309 --
310 --
311 Cursor C_Sel2 is
312 Select null
313 from ota_delegate_bookings
314 where booking_id =
315 ota_tdb_ins.g_booking_id_i;
316 --
317 l_exists varchar2(1);
318 --
319 Begin
320 hr_utility.set_location('Entering:'||l_proc, 5);
321
322 If (ota_tdb_ins.g_booking_id_i is not null) Then
323 --
324 -- Verify registered primary key values not already in use
325 --
326 Open C_Sel2;
327 Fetch C_Sel2 into l_exists;
328 If C_Sel2%found Then
329 Close C_Sel2;
330 --
331 -- The primary key values are already in use.
332 --
333 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
334 fnd_message.set_token('TABLE_NAME','ota_delegate_bookings');
335 fnd_message.raise_error;
336 End If;
337 Close C_Sel2;
338 --
339 -- Use registered key values and clear globals
340 --
341 p_rec.booking_id :=
342 ota_tdb_ins.g_booking_id_i;
343 ota_tdb_ins.g_booking_id_i := null;
344 Else
345 --
346 -- No registerd key values, so select the next sequence number
347 --
348 -- Select the next sequence number
349 --
350 Open C_Sel1;
351 Fetch C_Sel1 Into p_rec.booking_id;
352 Close C_Sel1;
353 --
354 End If;
355 --
356 hr_utility.set_location(' Leaving:'||l_proc, 10);
357 End pre_insert;
358 --
359 -- ----------------------------------------------------------------------------
360 -- |-----------------------------< post_insert >------------------------------|
361 -- ----------------------------------------------------------------------------
362 -- {Start Of Comments}
363 --
364 -- Description:
365 -- This private procedure contains any processing which is required after the
366 -- insert dml.
367 --
368 -- Pre Conditions:
369 -- This is an internal procedure which is called from the ins procedure.
370 --
371 -- In Arguments:
372 -- A Pl/Sql record structre.
373 --
374 -- Post Success:
375 -- Processing continues.
376 --
377 -- Post Failure:
378 -- If an error has occurred, an error message and exception will be raised
379 -- but not handled.
380 --
381 -- Developer Implementation Notes:
382 -- Any post-processing required after the insert dml is issued should be
383 -- coded within this procedure. It is important to note that any 3rd party
384 -- maintenance should be reviewed before placing in this procedure.
385 --
386 -- Access Status:
387 -- Internal Development Use Only.
388 --
389 -- {End Of Comments}
390 -- ----------------------------------------------------------------------------
391 Procedure post_insert(p_rec in ota_tdb_shd.g_rec_type) is
392 --
393 l_proc varchar2(72) := g_package||'post_insert';
394 l_dummy number;
395 l_return boolean;
396 --
397
398 CURSOR c_event /* Added for Bug 3385192 */
399 IS
400 select line_id
401 from ota_events
402 where event_id = p_rec.event_id;
403
404 Begin
405 hr_utility.set_location('Entering:'||l_proc, 5);
406 --
407 IF p_rec.line_id is not null then
408 l_return := ota_utility.check_wf_status(p_rec.line_id,'BLOCK');
409 IF l_return = TRUE THEN
410 wf_engine.Completeactivity('OEOL',
411 to_char(p_rec.line_id),
412 'BLOCK',null);
413 END IF;
414 ELSE /* Added for Bug 3385192 to complete the check of enrollment status*/
415 FOR line in c_event
416 LOOP
417 IF line.line_id is not null then
418 l_return := ota_utility.check_wf_status(line.line_id,'BLOCK');
419 IF l_return = TRUE THEN
420
421 wf_engine.Completeactivity('OEOL',
422 to_char(line.line_id),
423 'BLOCK',null);
424 END IF;
425 END IF;
426 END LOOP;
427
428 END IF;
429 --
430 hr_utility.set_location(' Leaving:'||l_proc, 10);
431
432
433 End post_insert;
434 --
435 -- ----------------------------------------------------------------------------
436 -- |---------------------------------< ins >----------------------------------|
437 -- ----------------------------------------------------------------------------
438 Procedure ins
439 (
440 p_rec in out nocopy ota_tdb_shd.g_rec_type,
441 p_create_finance_line in varchar2,
442 p_finance_header_id in number,
443 p_currency_code in varchar2,
444 p_standard_amount in number,
445 p_unitary_amount in number,
446 p_money_amount in number,
447 p_booking_deal_id in number,
448 p_booking_deal_type in varchar2,
449 p_finance_line_id in out nocopy number,
450 p_enrollment_type in varchar2,
451 p_validate in boolean
452
453 ) is
454 --
455 l_proc varchar2(72) := g_package||'ins';
456 --
457 Begin
458 hr_utility.set_location('Entering:'||l_proc, 5);
459 --
460 -- Determine if the business process is to be validated.
461 --
462 If p_validate then
463 --
464 -- Issue the savepoint.
465 --
466 SAVEPOINT ins_ota_tdb;
467 End If;
468 --
469 -- Call the supporting insert validate operations
470 --
471 ota_tdb_bus.insert_validate(p_rec,p_enrollment_type);
472 --
473 -- Call the supporting pre-insert operation
474 --
475 pre_insert(p_rec);
476 --
477 -- Insert the row
478 --
479 insert_dml(p_rec);
480 --
481 -- Call the supporting post-insert operation
482 --
483 post_insert(p_rec);
484 --
485 -- If we are validating then raise the Validate_Enabled exception
486 --
487 If p_validate then
488 Raise HR_Api.Validate_Enabled;
489 End If;
490 --
491 hr_utility.set_location(' Leaving:'||l_proc, 10);
492 Exception
493 When HR_Api.Validate_Enabled Then
494 --
495 -- As the Validate_Enabled exception has been raised
496 -- we must rollback to the savepoint
497 --
498 ROLLBACK TO ins_ota_tdb;
499 end ins;
500 --
501 -- ----------------------------------------------------------------------------
502 -- |---------------------------------< ins >----------------------------------|
503 -- ----------------------------------------------------------------------------
504 Procedure ins
505 (
506 p_booking_id out nocopy number,
507 p_booking_status_type_id in number,
508 p_delegate_person_id in number ,
509 p_contact_id in number,
510 p_business_group_id in number,
511 p_event_id in number,
512 p_customer_id in number ,
513 p_authorizer_person_id in number ,
514 p_date_booking_placed in date,
515 p_corespondent in varchar2 ,
516 p_internal_booking_flag in varchar2,
517 p_number_of_places in number,
518 p_object_version_number out nocopy number,
519 p_administrator in number ,
520 p_booking_priority in varchar2 ,
521 p_comments in varchar2 ,
522 p_contact_address_id in number ,
523 p_delegate_contact_phone in varchar2 ,
524 p_delegate_contact_fax in varchar2 ,
525 p_third_party_customer_id in number ,
526 p_third_party_contact_id in number ,
527 p_third_party_address_id in number ,
528 p_third_party_contact_phone in varchar2 ,
529 p_third_party_contact_fax in varchar2 ,
530 p_date_status_changed in date ,
531 p_failure_reason in varchar2 ,
532 p_attendance_result in varchar2 ,
533 p_language_id in number ,
534 p_source_of_booking in varchar2 ,
535 p_special_booking_instructions in varchar2 ,
536 p_successful_attendance_flag in varchar2 ,
537 p_tdb_information_category in varchar2 ,
538 p_tdb_information1 in varchar2 ,
539 p_tdb_information2 in varchar2 ,
540 p_tdb_information3 in varchar2 ,
541 p_tdb_information4 in varchar2 ,
542 p_tdb_information5 in varchar2 ,
543 p_tdb_information6 in varchar2 ,
544 p_tdb_information7 in varchar2 ,
545 p_tdb_information8 in varchar2 ,
546 p_tdb_information9 in varchar2 ,
547 p_tdb_information10 in varchar2 ,
548 p_tdb_information11 in varchar2 ,
549 p_tdb_information12 in varchar2 ,
550 p_tdb_information13 in varchar2 ,
551 p_tdb_information14 in varchar2 ,
552 p_tdb_information15 in varchar2 ,
553 p_tdb_information16 in varchar2 ,
554 p_tdb_information17 in varchar2 ,
555 p_tdb_information18 in varchar2 ,
556 p_tdb_information19 in varchar2 ,
557 p_tdb_information20 in varchar2 ,
558 p_create_finance_line in varchar2 ,
559 p_finance_header_id in number ,
560 p_currency_code in varchar2 ,
561 p_standard_amount in number ,
562 p_unitary_amount in number ,
563 p_money_amount in number ,
564 p_booking_deal_id in number ,
565 p_booking_deal_type in varchar2 ,
566 p_finance_line_id in out nocopy number,
567 p_enrollment_type in varchar2 ,
568 p_validate in boolean ,
569 p_organization_id in number ,
570 p_sponsor_person_id in number ,
571 p_sponsor_assignment_id in number ,
572 p_person_address_id in number ,
573 p_delegate_assignment_id in number ,
574 p_delegate_contact_id in number ,
575 p_delegate_contact_email in varchar2 ,
576 p_third_party_email in varchar2 ,
577 p_person_address_type in varchar2 ,
578 p_line_id in number ,
579 p_org_id in number ,
580 p_daemon_flag in varchar2 ,
581 p_daemon_type in varchar2 ,
582 p_old_event_id in number ,
583 p_quote_line_id in number ,
584 p_interface_source in varchar2 ,
585 p_total_training_time in varchar2 ,
586 p_content_player_status in varchar2 ,
587 p_score in number ,
588 p_completed_content in number ,
589 p_total_content in number ,
590 p_booking_justification_id in number,
591 p_is_history_flag in varchar2,
592 p_is_mandatory_enrollment in varchar2
593 ) is
594 --
595 l_rec ota_tdb_shd.g_rec_type;
596 l_proc varchar2(72) := g_package||'ins';
597 --
598 Begin
599 hr_utility.set_location('Entering:'||l_proc, 5);
600 --
601 -- Call conversion function to turn arguments into the
602 -- p_rec structure.
603 --
604 l_rec :=
605 ota_tdb_shd.convert_args
606 (
607 null,
608 p_booking_status_type_id,
609 p_delegate_person_id,
610 p_contact_id,
611 p_business_group_id,
612 p_event_id,
613 p_customer_id,
614 p_authorizer_person_id,
615 p_date_booking_placed,
616 p_corespondent,
617 p_internal_booking_flag,
618 p_number_of_places,
619 null,
620 p_administrator,
621 p_booking_priority,
622 p_comments,
623 p_contact_address_id,
624 p_delegate_contact_phone,
625 p_delegate_contact_fax,
626 p_third_party_customer_id,
627 p_third_party_contact_id,
628 p_third_party_address_id,
629 p_third_party_contact_phone,
630 p_third_party_contact_fax,
631 p_date_status_changed,
632 p_failure_reason,
633 p_attendance_result,
634 p_language_id,
635 p_source_of_booking,
636 p_special_booking_instructions,
637 p_successful_attendance_flag,
638 p_tdb_information_category,
639 p_tdb_information1,
640 p_tdb_information2,
641 p_tdb_information3,
642 p_tdb_information4,
643 p_tdb_information5,
644 p_tdb_information6,
645 p_tdb_information7,
646 p_tdb_information8,
647 p_tdb_information9,
648 p_tdb_information10,
649 p_tdb_information11,
650 p_tdb_information12,
651 p_tdb_information13,
652 p_tdb_information14,
653 p_tdb_information15,
654 p_tdb_information16,
655 p_tdb_information17,
656 p_tdb_information18,
657 p_tdb_information19,
658 p_tdb_information20,
659 p_organization_id,
660 p_sponsor_person_id,
661 p_sponsor_assignment_id,
662 p_person_address_id,
663 p_delegate_assignment_id,
664 p_delegate_contact_id,
665 p_delegate_contact_email,
666 p_third_party_email,
667 p_person_address_type,
668 p_line_id,
669 p_org_id,
670 p_daemon_flag,
671 p_daemon_type,
672 p_old_event_id,
673 p_quote_line_id,
674 p_interface_source,
675 p_total_training_time,
676 p_content_player_status,
677 p_score,
678 p_completed_content,
679 p_total_content,
680 p_booking_justification_id,
681 p_is_history_flag,
682 p_is_mandatory_enrollment
683 );
684 --
685 -- Having converted the arguments into the ota_tdb_rec
686 -- plsql record structure we call the corresponding record business process.
687 --
688 ins(l_rec,
689 p_create_finance_line,
690 p_finance_header_id,
691 p_currency_code,
692 p_standard_amount,
693 p_unitary_amount,
694 p_money_amount,
695 p_booking_deal_id,
696 p_booking_deal_type,
697 p_finance_line_id,
698 p_enrollment_type,
699 p_validate
700 );
701 --
702 -- As the primary key argument(s)
703 -- are specified as an OUT's we must set these values.
704 --
705 p_booking_id := l_rec.booking_id;
706 p_object_version_number := l_rec.object_version_number;
707 --
708 hr_utility.set_location(' Leaving:'||l_proc, 10);
709 End ins;
710 --
711 end ota_tdb_ins;
712
[Home] [Help]