1 PACKAGE BODY OTA_EVT_INS as
2 /* $Header: otevt01t.pkb 120.13.12010000.2 2008/09/17 08:05:36 srgnanas ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_evt_ins.'; -- Global package name
9 --
10 g_event_id_i number default null;
11 --
12 -- ----------------------------------------------------------------------------
13 -- |------------------------< set_base_key_value >----------------------------|
14 -- ----------------------------------------------------------------------------
15 procedure set_base_key_value
16 (p_event_id in number) is
17 --
18 l_proc varchar2(72) := g_package||'set_base_key_value';
19 --
20 Begin
21 hr_utility.set_location('Entering:'||l_proc, 10);
22 --
23 ota_evt_ins.g_event_id_i := p_event_id;
24 --
25 hr_utility.set_location(' Leaving:'||l_proc, 20);
26 End set_base_key_value;
27
28 -- ----------------------------------------------------------------------------
29 -- |------------------------------< insert_dml >------------------------------|
30 -- ----------------------------------------------------------------------------
31 -- {Start Of Comments}
32 --
33 -- Description:
34 -- This procedure controls the actual dml insert logic. The functions of this
35 -- procedure are as follows:
36 -- 1) Initialise the object_version_number to 1 if the object_version_number
37 -- is defined as an attribute for this entity.
38 -- 2) To set and unset the g_api_dml status as required (as we are about to
39 -- perform dml).
40 -- 3) To insert the row into the schema.
41 -- 4) To trap any constraint violations that may have occurred.
42 -- 5) To raise any other errors.
43 --
44 -- Pre Conditions:
45 -- This is an internal private procedure which must be called from the ins
46 -- procedure and must have all mandatory arguments set (except the
47 -- object_version_number which is initialised within this procedure).
48 --
49 -- In Arguments:
50 -- A Pl/Sql record structre.
51 --
52 -- Post Success:
53 -- The specified row will be inserted into the schema.
54 --
55 -- Post Failure:
56 -- On the insert dml failure it is important to note that we always reset the
57 -- g_api_dml status to false.
58 -- If a check, unique or parent integrity constraint violation is raised the
59 -- constraint_error procedure will be called.
60 -- If any other error is reported, the error will be raised after the
61 -- g_api_dml status is reset.
62 --
63 -- Developer Implementation Notes:
64 -- None.
65 --
66 -- Access Status:
67 -- Internal Development Use Only.
68 --
69 -- {End Of Comments}
70 -- ----------------------------------------------------------------------------
71 Procedure insert_dml(p_rec in out nocopy ota_evt_shd.g_rec_type) is
72 --
73 l_proc varchar2(72) := g_package||'insert_dml';
74 --
75 Begin
76 hr_utility.set_location('Entering:'||l_proc, 5);
77 p_rec.object_version_number := 1; -- Initialise the object version
78 --
79 ota_evt_shd.g_api_dml := true; -- Set the api dml status
80 --
81 -- Insert the row into: ota_events
82 --
83 insert into ota_events
84 ( event_id,
85 vendor_id,
86 activity_version_id,
87 business_group_id,
88 organization_id,
89 event_type,
90 object_version_number,
91 title,
92 budget_cost,
93 actual_cost,
94 budget_currency_code,
95 centre,
96 comments,
97 course_end_date,
98 course_end_time,
99 course_start_date,
100 course_start_time,
101 duration,
102 duration_units,
103 enrolment_end_date,
104 enrolment_start_date,
105 language_id,
106 user_status,
107 development_event_type,
108 event_status,
109 price_basis,
110 currency_code,
111 maximum_attendees,
112 maximum_internal_attendees,
113 minimum_attendees,
114 standard_price,
115 category_code,
116 parent_event_id,
117 book_independent_flag,
118 public_event_flag,
119 secure_event_flag,
120 evt_information_category,
121 evt_information1,
122 evt_information2,
123 evt_information3,
124 evt_information4,
125 evt_information5,
126 evt_information6,
127 evt_information7,
128 evt_information8,
129 evt_information9,
130 evt_information10,
131 evt_information11,
132 evt_information12,
133 evt_information13,
134 evt_information14,
135 evt_information15,
136 evt_information16,
137 evt_information17,
138 evt_information18,
139 evt_information19,
140 evt_information20,
141 project_id,
142 owner_id,
143 line_id,
144 org_id,
145 training_center_id,
146 location_id,
147 offering_id,
148 timezone,
149 parent_offering_id,
150 data_source
151 )
152 Values
153 ( p_rec.event_id,
154 p_rec.vendor_id,
155 p_rec.activity_version_id,
156 p_rec.business_group_id,
157 p_rec.organization_id,
158 p_rec.event_type,
159 p_rec.object_version_number,
160 p_rec.title,
161 p_rec.budget_cost,
162 p_rec.actual_cost,
163 p_rec.budget_currency_code,
164 p_rec.centre,
165 p_rec.comments,
166 p_rec.course_end_date,
167 p_rec.course_end_time,
168 p_rec.course_start_date,
169 p_rec.course_start_time,
170 p_rec.duration,
171 p_rec.duration_units,
172 p_rec.enrolment_end_date,
173 p_rec.enrolment_start_date,
174 p_rec.language_id,
175 p_rec.user_status,
176 p_rec.development_event_type,
177 p_rec.event_status,
178 p_rec.price_basis,
179 p_rec.currency_code,
180 p_rec.maximum_attendees,
181 p_rec.maximum_internal_attendees,
182 p_rec.minimum_attendees,
183 p_rec.standard_price,
184 p_rec.category_code,
185 p_rec.parent_event_id,
186 p_rec.book_independent_flag,
187 p_rec.public_event_flag,
188 p_rec.secure_event_flag,
189 p_rec.evt_information_category,
190 p_rec.evt_information1,
191 p_rec.evt_information2,
192 p_rec.evt_information3,
193 p_rec.evt_information4,
194 p_rec.evt_information5,
195 p_rec.evt_information6,
196 p_rec.evt_information7,
197 p_rec.evt_information8,
198 p_rec.evt_information9,
199 p_rec.evt_information10,
200 p_rec.evt_information11,
201 p_rec.evt_information12,
202 p_rec.evt_information13,
203 p_rec.evt_information14,
204 p_rec.evt_information15,
205 p_rec.evt_information16,
206 p_rec.evt_information17,
207 p_rec.evt_information18,
208 p_rec.evt_information19,
209 p_rec.evt_information20,
210 p_rec.project_id,
211 p_rec.owner_id,
212 p_rec.line_id,
213 p_rec.org_id,
214 p_rec.training_center_id,
215 p_rec.location_id,
216 p_rec.offering_id,
217 p_rec.timezone,
218 p_rec.parent_offering_id,
219 p_rec.data_source
220 );
221 --
222 ota_evt_shd.g_api_dml := false; -- Unset the api dml status
223 --
224 hr_utility.set_location(' Leaving:'||l_proc, 10);
225 Exception
226 When hr_api.check_integrity_violated Then
227 -- A check constraint has been violated
228 ota_evt_shd.g_api_dml := false; -- Unset the api dml status
229 ota_evt_shd.constraint_error
230 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
231 When hr_api.parent_integrity_violated Then
232 -- Parent integrity has been violated
233 ota_evt_shd.g_api_dml := false; -- Unset the api dml status
234 ota_evt_shd.constraint_error
235 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
236 When hr_api.unique_integrity_violated Then
237 -- Unique integrity has been violated
238 ota_evt_shd.g_api_dml := false; -- Unset the api dml status
239 ota_evt_shd.constraint_error
240 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
241 When Others Then
242 ota_evt_shd.g_api_dml := false; -- Unset the api dml status
243 Raise;
244 End insert_dml;
245 --
246 -- ----------------------------------------------------------------------------
247 -- |------------------------------< pre_insert >------------------------------|
248 -- ----------------------------------------------------------------------------
249 -- {Start Of Comments}
250 --
251 -- Description:
252 -- This private procedure contains any processing which is required before
253 -- the insert dml. Presently, if the entity has a corresponding primary
254 -- key which is maintained by an associating sequence, the primary key for
255 -- the entity will be populated with the next sequence value in
256 -- preparation for the insert dml.
257 --
258 -- Pre Conditions:
259 -- This is an internal procedure which is called from the ins procedure.
260 --
261 -- In Arguments:
262 -- A Pl/Sql record structre.
263 --
264 -- Post Success:
265 -- Processing continues.
266 --
267 -- Post Failure:
268 -- If an error has occurred, an error message and exception will be raised
269 -- but not handled.
270 --
271 -- Developer Implementation Notes:
272 -- Any pre-processing required before the insert dml is issued should be
273 -- coded within this procedure. As stated above, a good example is the
274 -- generation of a primary key number via a corresponding sequence.
275 -- It is important to note that any 3rd party maintenance should be reviewed
276 -- before placing in this procedure.
277 --
278 -- Access Status:
279 -- Internal Development Use Only.
280 --
281 -- {End Of Comments}
282 -- ----------------------------------------------------------------------------
283 Procedure pre_insert
284 (p_rec in out nocopy ota_evt_shd.g_rec_type
285 ) is
286 --
287 l_proc varchar2(72) := g_package||'pre_insert';
288 --
289 Cursor C_Sel1 is select ota_events_s.nextval from sys.dual;
290 --
291 Cursor C_Sel2 is
292 Select null
293 from ota_events
294 where event_id =
295 ota_evt_ins.g_event_id_i;
296 --
297 l_exists varchar2(1);
298 Begin
299 hr_utility.set_location('Entering:'||l_proc, 5);
300 If (ota_evt_ins.g_event_id_i is not null) Then
301 --
302 -- Verify registered primary key values not already in use
303 --
304 Open C_Sel2;
305 Fetch C_Sel2 into l_exists;
306 If C_Sel2%found Then
307 Close C_Sel2;
308 --
309 -- The primary key values are already in use.
310 --
311 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
312 fnd_message.set_token('TABLE_NAME','irc_documents');
313 fnd_message.raise_error;
314 End If;
315 Close C_Sel2;
316 --
317 -- Use registered key values and clear globals
318 --
319 p_rec.event_id :=
320 ota_evt_ins.g_event_id_i;
321 ota_evt_ins.g_event_id_i := null;
322 Else
323 --
324 -- No registerd key values, so select the next sequence number
325 --
326 -- Select the next sequence number
327 --
328 Open C_Sel1;
329 Fetch C_Sel1 Into p_rec.event_id;
330 Close C_Sel1;
331
332 END IF;
333 --
334 hr_utility.set_location(' Leaving:'||l_proc, 10);
335 End pre_insert;
336 --
337
338
339 -- ----------------------------------------------------------------------------
340 -- |-----------------------------< post_insert >------------------------------|
341 -- ----------------------------------------------------------------------------
342 -- {Start Of Comments}
343 --
344 -- Description:
345 -- This private procedure contains any processing which is required after the
346 -- insert dml.
347 --
348 -- Pre Conditions:
349 -- This is an internal procedure which is called from the ins procedure.
350 --
351 -- In Arguments:
352 -- A Pl/Sql record structre.
353 --
354 -- Post Success:
355 -- Processing continues.
356 --
357 -- Post Failure:
358 -- If an error has occurred, an error message and exception will be raised
359 -- but not handled.
360 --
361 -- Developer Implementation Notes:
362 -- Any post-processing required after the insert dml is issued should be
363 -- coded within this procedure. It is important to note that any 3rd party
364 -- maintenance should be reviewed before placing in this procedure.
365 --
366 -- Access Status:
367 -- Internal Development Use Only.
368 --
369 -- {End Of Comments}
370 -- ----------------------------------------------------------------------------
371 Procedure post_insert(p_rec in ota_evt_shd.g_rec_type) is
372 --
373 l_proc varchar2(72) := g_package||'post_insert';
374 l_return boolean;
375 --
376 Begin
377 hr_utility.set_location('Entering:'||l_proc, 5);
378 /* commented out to fix bug 3385192. The call is moved to ota_tdb_ins.post_insert */
379 /* IF p_rec.line_id is not null then
380 l_return := ota_utility.check_wf_status(p_rec.line_id,'BLOCK');
381 IF l_return = TRUE THEN
382
383 wf_engine.Completeactivity('OEOL',
384 to_char(p_rec.line_id),
385 'BLOCK',null);
386 END IF;
387
388 END IF;*/
389
390 hr_utility.set_location(' Leaving:'||l_proc, 10);
391 End post_insert;
392 --
393 -- ----------------------------------------------------------------------------
394 -- |---------------------------------< ins >----------------------------------|
395 -- ----------------------------------------------------------------------------
396 Procedure ins
397 (
398 p_rec in out nocopy ota_evt_shd.g_rec_type,
399 p_validate in boolean default false
400 ) is
401 --
402 l_proc varchar2(72) := g_package||'ins';
403 --
404 Begin
405 hr_utility.set_location('Entering:'||l_proc, 5);
406 --
407 -- Determine if the business process is to be validated.
408 --
409 If p_validate then
410 --
411 -- Issue the savepoint.
412 --
413 SAVEPOINT ins_ota_evt;
414 End If;
415 --
416 -- Call the supporting insert validate operations
417 --
418 ota_evt_bus.insert_validate(p_rec);
419 -- added for eBS by asud
420 hr_multi_message.end_validation_set;
421 -- added for eBS by asud
422 --
423 -- Call the supporting pre-insert operation
424 --
425 pre_insert(p_rec);
426 --
430 --
427 -- Insert the row
428 --
429 insert_dml(p_rec);
431 -- Call the supporting post-insert operation
432 --
433 post_insert(p_rec);
434 --
435 -- added for eBS by asud
436 hr_multi_message.end_validation_set;
437 -- added for eBS by asud
438 -- If we are validating then raise the Validate_Enabled exception
439 --
440 If p_validate then
441 Raise HR_Api.Validate_Enabled;
442 End If;
443 --
444 hr_utility.set_location(' Leaving:'||l_proc, 10);
445 Exception
446 When HR_Api.Validate_Enabled Then
447 --
448 -- As the Validate_Enabled exception has been raised
449 -- we must rollback to the savepoint
450 --
451 ROLLBACK TO ins_ota_evt;
452 end ins;
453 --
454 -- ----------------------------------------------------------------------------
455 -- |---------------------------------< ins >----------------------------------|
456 -- ----------------------------------------------------------------------------
457 Procedure ins
458 (
459 p_event_id out nocopy number,
460 p_vendor_id in number default null,
461 p_activity_version_id in number default null,
462 p_business_group_id in number,
463 p_organization_id in number default null,
464 p_event_type in varchar2,
465 p_object_version_number out nocopy number,
466 p_title in varchar2,
467 p_budget_cost in number default null,
468 p_actual_cost in number default null,
469 p_budget_currency_code in varchar2 default null,
470 p_centre in varchar2 default null,
471 p_comments in varchar2 default null,
472 p_course_end_date in date default null,
473 p_course_end_time in varchar2 default null,
474 p_course_start_date in date default null,
475 p_course_start_time in varchar2 default null,
476 p_duration in number default null,
477 p_duration_units in varchar2 default null,
478 p_enrolment_end_date in date default null,
479 p_enrolment_start_date in date default null,
480 p_language_id in number default null,
481 p_user_status in varchar2 default null,
482 p_development_event_type in varchar2 default null,
483 p_event_status in varchar2 default null,
484 p_price_basis in varchar2 default null,
485 p_currency_code in varchar2 default null,
486 p_maximum_attendees in number default null,
487 p_maximum_internal_attendees in number default null,
488 p_minimum_attendees in number default null,
489 p_standard_price in number default null,
490 p_category_code in varchar2 default null,
491 p_parent_event_id in number default null,
495 p_evt_information_category in varchar2 default null,
492 p_book_independent_flag in varchar2 default null,
493 p_public_event_flag in varchar2 default null,
494 p_secure_event_flag in varchar2 default null,
496 p_evt_information1 in varchar2 default null,
497 p_evt_information2 in varchar2 default null,
498 p_evt_information3 in varchar2 default null,
499 p_evt_information4 in varchar2 default null,
500 p_evt_information5 in varchar2 default null,
501 p_evt_information6 in varchar2 default null,
502 p_evt_information7 in varchar2 default null,
503 p_evt_information8 in varchar2 default null,
504 p_evt_information9 in varchar2 default null,
505 p_evt_information10 in varchar2 default null,
506 p_evt_information11 in varchar2 default null,
507 p_evt_information12 in varchar2 default null,
508 p_evt_information13 in varchar2 default null,
509 p_evt_information14 in varchar2 default null,
510 p_evt_information15 in varchar2 default null,
511 p_evt_information16 in varchar2 default null,
512 p_evt_information17 in varchar2 default null,
513 p_evt_information18 in varchar2 default null,
514 p_evt_information19 in varchar2 default null,
515 p_evt_information20 in varchar2 default null,
516 p_project_id in number default null,
517 p_owner_id in number default null,
518 p_line_id in number default null,
519 p_org_id in number default null,
520 p_training_center_id in number default null,
521 p_location_id in number default null,
522 p_offering_id in number default null,
523 p_timezone in varchar2 default null,
524 p_parent_offering_id in number default null,
525 p_data_source in varchar2 default null,
526 p_validate in boolean default false
527 ) is
528 --
529 l_rec ota_evt_shd.g_rec_type;
530 l_proc varchar2(72) := g_package||'ins';
531 --
532 Begin
533 hr_utility.set_location('Entering:'||l_proc, 5);
534 --
535 -- Call conversion function to turn arguments into the
536 -- p_rec structure.
537 --
538 l_rec :=
539 ota_evt_shd.convert_args
540 (
541 null,
542 p_vendor_id,
543 p_activity_version_id,
544 p_business_group_id,
545 p_organization_id,
546 p_event_type,
547 null,
548 p_title,
549 p_budget_cost,
550 p_actual_cost,
551 p_budget_currency_code,
552 p_centre,
553 p_comments,
554 p_course_end_date,
555 p_course_end_time,
556 p_course_start_date,
557 p_course_start_time,
558 p_duration,
559 p_duration_units,
560 p_enrolment_end_date,
561 p_enrolment_start_date,
562 p_language_id,
563 p_user_status,
564 p_development_event_type,
565 p_event_status,
566 p_price_basis,
567 p_currency_code,
568 p_maximum_attendees,
569 p_maximum_internal_attendees,
570 p_minimum_attendees,
571 p_standard_price,
572 p_category_code,
573 p_parent_event_id,
574 p_book_independent_flag,
575 p_public_event_flag,
576 p_secure_event_flag,
577 p_evt_information_category,
578 p_evt_information1,
579 p_evt_information2,
580 p_evt_information3,
581 p_evt_information4,
582 p_evt_information5,
583 p_evt_information6,
584 p_evt_information7,
585 p_evt_information8,
586 p_evt_information9,
587 p_evt_information10,
588 p_evt_information11,
589 p_evt_information12,
590 p_evt_information13,
591 p_evt_information14,
592 p_evt_information15,
593 p_evt_information16,
594 p_evt_information17,
595 p_evt_information18,
596 p_evt_information19,
597 p_evt_information20,
598 p_project_id,
599 p_owner_id,
600 p_line_id,
601 p_org_id,
602 p_training_center_id,
603 p_location_id,
604 p_offering_id,
605 p_timezone,
606 p_parent_offering_id,
607 p_data_source
608 );
609 --
610 -- Having converted the arguments into the ota_evt_rec
611 -- plsql record structure we call the corresponding record business process.
612 --
613 ins(l_rec, p_validate);
614 --
618 p_event_id := l_rec.event_id;
615 -- As the primary key argument(s)
616 -- are specified as an OUT's we must set these values.
617 --
619 p_object_version_number := l_rec.object_version_number;
620 --
621 hr_utility.set_location(' Leaving:'||l_proc, 10);
622 End ins;
623 --
624 end ota_evt_ins;
[Home] [Help]