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