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