[Home] [Help]
Skip to content
PACKAGE BODY: APPS.OTA_OFF_INS
Source
4 -- ----------------------------------------------------------------------------
1 PACKAGE BODY OTA_OFF_INS as
2 /* $Header: otoffrhi.pkb 120.1.12000000.2 2007/02/06 15:25:23 vkkolla noship $ */
3 --
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_off_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_offering_id_i number default null;
14 --
18 procedure set_base_key_value
15 -- ----------------------------------------------------------------------------
16 -- |------------------------< set_base_key_value >----------------------------|
17 -- ----------------------------------------------------------------------------
19 (p_offering_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_off_ins.g_offering_id_i := p_offering_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_off_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 --
86 --
87 -- Insert the row into: ota_offerings
88 --
89 insert into ota_offerings
90 (offering_id
91 ,activity_version_id
92 ,business_group_id
93 --,offering_name
94 ,start_date
95 ,end_date
96 ,owner_id
97 ,delivery_mode_id
98 ,language_id
99 ,duration
100 ,duration_units
101 ,learning_object_id
102 ,player_toolbar_flag
103 ,player_toolbar_bitset
104 ,player_new_window_flag
105 ,maximum_attendees
106 ,maximum_internal_attendees
107 ,minimum_attendees
108 ,actual_cost
109 ,budget_cost
110 ,budget_currency_code
111 ,price_basis
112 ,currency_code
113 ,standard_price
114 ,object_version_number
115 ,attribute_category
116 ,attribute1
117 ,attribute2
118 ,attribute3
119 ,attribute4
120 ,attribute5
121 ,attribute6
122 ,attribute7
123 ,attribute8
124 ,attribute9
125 ,attribute10
126 ,attribute11
127 ,attribute12
128 ,attribute13
129 ,attribute14
130 ,attribute15
131 ,attribute16
132 ,attribute17
133 ,attribute18
134 ,attribute19
135 ,attribute20
136 ,data_source
137 ,vendor_id
138 ,competency_update_level
139 ,language_code -- 2733966
140 )
141 Values
142 (p_rec.offering_id
143 ,p_rec.activity_version_id
144 ,p_rec.business_group_id
145 --,p_rec.offering_name
146 ,p_rec.start_date
147 ,p_rec.end_date
148 ,p_rec.owner_id
149 ,p_rec.delivery_mode_id
150 ,p_rec.language_id
151 ,p_rec.duration
152 ,p_rec.duration_units
153 ,p_rec.learning_object_id
154 ,p_rec.player_toolbar_flag
155 ,p_rec.player_toolbar_bitset
156 ,p_rec.player_new_window_flag
157 ,p_rec.maximum_attendees
158 ,p_rec.maximum_internal_attendees
159 ,p_rec.minimum_attendees
160 ,p_rec.actual_cost
161 ,p_rec.budget_cost
162 ,p_rec.budget_currency_code
163 ,p_rec.price_basis
164 ,p_rec.currency_code
165 ,p_rec.standard_price
166 ,p_rec.object_version_number
167 ,p_rec.attribute_category
168 ,p_rec.attribute1
169 ,p_rec.attribute2
170 ,p_rec.attribute3
171 ,p_rec.attribute4
172 ,p_rec.attribute5
173 ,p_rec.attribute6
174 ,p_rec.attribute7
175 ,p_rec.attribute8
176 ,p_rec.attribute9
177 ,p_rec.attribute10
178 ,p_rec.attribute11
179 ,p_rec.attribute12
183 ,p_rec.attribute16
180 ,p_rec.attribute13
181 ,p_rec.attribute14
182 ,p_rec.attribute15
184 ,p_rec.attribute17
185 ,p_rec.attribute18
186 ,p_rec.attribute19
187 ,p_rec.attribute20
188 ,p_rec.data_source
189 ,p_rec.vendor_id
190 ,p_rec.competency_update_level
191 ,p_rec.language_code -- 2733966
192 );
193 --
194 --
195 --
196 hr_utility.set_location(' Leaving:'||l_proc, 10);
197 Exception
198 When hr_api.check_integrity_violated Then
199 -- A check constraint has been violated
200 --
201 ota_off_shd.constraint_error
202 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
203 When hr_api.parent_integrity_violated Then
204 -- Parent integrity has been violated
205 --
206 ota_off_shd.constraint_error
207 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
208 When hr_api.unique_integrity_violated Then
209 -- Unique integrity has been violated
210 --
211 ota_off_shd.constraint_error
212 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
213 When Others Then
214 --
215 Raise;
216 End insert_dml;
217 --
218 -- ----------------------------------------------------------------------------
219 -- |------------------------------< pre_insert >------------------------------|
220 -- ----------------------------------------------------------------------------
221 -- {Start Of Comments}
222 --
223 -- Description:
224 -- This private procedure contains any processing which is required before
225 -- the insert dml. Presently, if the entity has a corresponding primary
226 -- key which is maintained by an associating sequence, the primary key for
227 -- the entity will be populated with the next sequence value in
228 -- preparation for the insert dml.
229 --
230 -- Prerequisites:
231 -- This is an internal procedure which is called from the ins procedure.
232 --
233 -- In Parameters:
234 -- A Pl/Sql record structure.
235 --
236 -- Post Success:
237 -- Processing continues.
238 --
239 -- Post Failure:
240 -- If an error has occurred, an error message and exception will be raised
241 -- but not handled.
242 --
243 -- Developer Implementation Notes:
244 -- Any pre-processing required before the insert dml is issued should be
245 -- coded within this procedure. As stated above, a good example is the
246 -- generation of a primary key number via a corresponding sequence.
247 -- It is important to note that any 3rd party maintenance should be reviewed
248 -- before placing in this procedure.
249 --
250 -- Access Status:
251 -- Internal Row Handler Use Only.
252 --
253 -- {End Of Comments}
254 -- ----------------------------------------------------------------------------
255 Procedure pre_insert
256 (p_rec in out nocopy ota_off_shd.g_rec_type
257 ) is
258 --
259 Cursor C_Sel1 is select ota_offerings_s.nextval from sys.dual;
260 --
261 Cursor C_Sel2 is
262 Select null
263 from ota_offerings
264 where offering_id =
265 ota_off_ins.g_offering_id_i;
266 --
267 l_proc varchar2(72) := g_package||'pre_insert';
268 l_exists varchar2(1);
269 --
270 Begin
271 hr_utility.set_location('Entering:'||l_proc, 5);
272 --
273 If (ota_off_ins.g_offering_id_i is not null) Then
274 --
275 -- Verify registered primary key values not already in use
276 --
277 Open C_Sel2;
278 Fetch C_Sel2 into l_exists;
279 If C_Sel2%found Then
280 Close C_Sel2;
281 --
282 -- The primary key values are already in use.
283 --
284 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
285 fnd_message.set_token('TABLE_NAME','ota_offerings');
286 fnd_message.raise_error;
287 End If;
288 Close C_Sel2;
289 --
290 -- Use registered key values and clear globals
291 --
292 p_rec.offering_id :=
293 ota_off_ins.g_offering_id_i;
294 ota_off_ins.g_offering_id_i := null;
295 Else
296 --
297 -- No registerd key values, so select the next sequence number
298 --
299 --
300 -- Select the next sequence number
301 --
302 Open C_Sel1;
303 Fetch C_Sel1 Into p_rec.offering_id;
304 Close C_Sel1;
305 End If;
306 --
307 hr_utility.set_location(' Leaving:'||l_proc, 10);
308 End pre_insert;
309 --
310 -- ----------------------------------------------------------------------------
311 -- |-----------------------------< post_insert >------------------------------|
312 -- ----------------------------------------------------------------------------
313 -- {Start Of Comments}
314 --
315 -- Description:
316 -- This private procedure contains any processing which is required after
317 -- the insert dml.
318 --
319 -- Prerequisites:
320 -- This is an internal procedure which is called from the ins procedure.
321 --
322 -- In Parameters:
323 -- A Pl/Sql record structre.
324 --
325 -- Post Success:
326 -- Processing continues.
327 --
328 -- Post Failure:
329 -- If an error has occurred, an error message and exception will be raised
330 -- but not handled.
331 --
332 -- Developer Implementation Notes:
333 -- Any post-processing required after the insert dml is issued should be
334 -- coded within this procedure. It is important to note that any 3rd party
338 -- Internal Row Handler Use Only.
335 -- maintenance should be reviewed before placing in this procedure.
336 --
337 -- Access Status:
339 --
340 -- {End Of Comments}
341 -- ----------------------------------------------------------------------------
342 Procedure post_insert
343 (p_effective_date in date
344 ,p_rec in ota_off_shd.g_rec_type
345 ) is
346 --
347 l_proc varchar2(72) := g_package||'post_insert';
348 --
349 Begin
350 hr_utility.set_location('Entering:'||l_proc, 5);
351 begin
352 --
353 ota_off_rki.after_insert
354 (p_effective_date => p_effective_date
355 ,p_offering_id
356 => p_rec.offering_id
357 ,p_activity_version_id
358 => p_rec.activity_version_id
359 ,p_business_group_id
360 => p_rec.business_group_id
361
362 ,p_start_date
363 => p_rec.start_date
364 ,p_end_date
365 => p_rec.end_date
366 ,p_owner_id
367 => p_rec.owner_id
368 ,p_delivery_mode_id
369 => p_rec.delivery_mode_id
370 ,p_language_id
371 => p_rec.language_id
372 ,p_duration
373 => p_rec.duration
374 ,p_duration_units
375 => p_rec.duration_units
376 ,p_learning_object_id
377 => p_rec.learning_object_id
378 ,p_player_toolbar_flag
379 => p_rec.player_toolbar_flag
380 ,p_player_toolbar_bitset
381 => p_rec.player_toolbar_bitset
382 ,p_player_new_window_flag
383 => p_rec.player_new_window_flag
384 ,p_maximum_attendees
385 => p_rec.maximum_attendees
386 ,p_maximum_internal_attendees
387 => p_rec.maximum_internal_attendees
388 ,p_minimum_attendees
389 => p_rec.minimum_attendees
390 ,p_actual_cost
391 => p_rec.actual_cost
392 ,p_budget_cost
393 => p_rec.budget_cost
394 ,p_budget_currency_code
395 => p_rec.budget_currency_code
396 ,p_price_basis
397 => p_rec.price_basis
398 ,p_currency_code
399 => p_rec.currency_code
400 ,p_standard_price
401 => p_rec.standard_price
402 ,p_object_version_number
403 => p_rec.object_version_number
404 ,p_attribute_category
405 => p_rec.attribute_category
406 ,p_attribute1
407 => p_rec.attribute1
408 ,p_attribute2
409 => p_rec.attribute2
410 ,p_attribute3
411 => p_rec.attribute3
412 ,p_attribute4
413 => p_rec.attribute4
414 ,p_attribute5
415 => p_rec.attribute5
416 ,p_attribute6
417 => p_rec.attribute6
418 ,p_attribute7
419 => p_rec.attribute7
420 ,p_attribute8
421 => p_rec.attribute8
422 ,p_attribute9
423 => p_rec.attribute9
424 ,p_attribute10
425 => p_rec.attribute10
426 ,p_attribute11
427 => p_rec.attribute11
428 ,p_attribute12
429 => p_rec.attribute12
430 ,p_attribute13
431 => p_rec.attribute13
432 ,p_attribute14
433 => p_rec.attribute14
434 ,p_attribute15
435 => p_rec.attribute15
436 ,p_attribute16
437 => p_rec.attribute16
438 ,p_attribute17
439 => p_rec.attribute17
440 ,p_attribute18
441 => p_rec.attribute18
442 ,p_attribute19
443 => p_rec.attribute19
444 ,p_attribute20
445 => p_rec.attribute20
446 ,p_data_source
447 => p_rec.data_source
448 ,p_vendor_id
449 => p_rec.vendor_id
450 ,p_competency_update_level => p_rec.competency_update_level
451 ,p_language_code => p_rec.language_code -- 2733966
452 );
453 --
454 exception
455 --
456 when hr_api.cannot_find_prog_unit then
457 --
458 hr_api.cannot_find_prog_unit_error
459 (p_module_name => 'OTA_OFFERINGS'
460 ,p_hook_type => 'AI');
461 --
462 end;
463 --
464 hr_utility.set_location(' Leaving:'||l_proc, 10);
465 End post_insert;
466 --
467 -- ----------------------------------------------------------------------------
468 -- |---------------------------------< ins >----------------------------------|
469 -- ----------------------------------------------------------------------------
470 Procedure ins
471 (p_effective_date in date
472 ,p_rec in out nocopy ota_off_shd.g_rec_type
473 ,p_name in varchar
474 ) is
475 --
476 l_proc varchar2(72) := g_package||'ins';
477 --
478 Begin
479 hr_utility.set_location('Entering:'||l_proc, 5);
480 --
481 -- Call the supporting insert validate operations
482 --
483 ota_off_bus.insert_validate
484 (p_effective_date
485 ,p_rec
486 ,p_name
487 );
488 --
489 -- Call to raise any errors on multi-message list
490 hr_multi_message.end_validation_set;
491 --
492 -- Call the supporting pre-insert operation
493 --
494 ota_off_ins.pre_insert(p_rec);
495 --
496 -- Insert the row
497 --
498 ota_off_ins.insert_dml(p_rec);
499 --
500 -- Call the supporting post-insert operation
501 --
502 ota_off_ins.post_insert
503 (p_effective_date
504 ,p_rec
505 );
506 --
510 hr_utility.set_location('Leaving:'||l_proc, 20);
507 -- Call to raise any errors on multi-message list
508 hr_multi_message.end_validation_set;
509 --
511 end ins;
512 --
513 -- ----------------------------------------------------------------------------
514 -- |---------------------------------< ins >----------------------------------|
515 -- ----------------------------------------------------------------------------
516 Procedure ins
517 (p_effective_date in date
518 ,p_business_group_id in number
519 ,p_name in varchar2
520 ,p_start_date in date
521 ,p_activity_version_id in number default null
522 ,p_end_date in date default null
523 ,p_owner_id in number default null
524 ,p_delivery_mode_id in number default null
525 ,p_language_id in number default null
526 ,p_duration in number default null
527 ,p_duration_units in varchar2 default null
528 ,p_learning_object_id in number default null
529 ,p_player_toolbar_flag in varchar2 default null
530 ,p_player_toolbar_bitset in number default null
531 ,p_player_new_window_flag in varchar2 default null
532 ,p_maximum_attendees in number default null
533 ,p_maximum_internal_attendees in number default null
534 ,p_minimum_attendees in number default null
535 ,p_actual_cost in number default null
536 ,p_budget_cost in number default null
537 ,p_budget_currency_code in varchar2 default null
538 ,p_price_basis in varchar2 default null
539 ,p_currency_code in varchar2 default null
540 ,p_standard_price in number default null
541 ,p_attribute_category in varchar2 default null
542 ,p_attribute1 in varchar2 default null
543 ,p_attribute2 in varchar2 default null
544 ,p_attribute3 in varchar2 default null
545 ,p_attribute4 in varchar2 default null
546 ,p_attribute5 in varchar2 default null
547 ,p_attribute6 in varchar2 default null
548 ,p_attribute7 in varchar2 default null
549 ,p_attribute8 in varchar2 default null
550 ,p_attribute9 in varchar2 default null
551 ,p_attribute10 in varchar2 default null
552 ,p_attribute11 in varchar2 default null
553 ,p_attribute12 in varchar2 default null
554 ,p_attribute13 in varchar2 default null
555 ,p_attribute14 in varchar2 default null
556 ,p_attribute15 in varchar2 default null
557 ,p_attribute16 in varchar2 default null
558 ,p_attribute17 in varchar2 default null
559 ,p_attribute18 in varchar2 default null
560 ,p_attribute19 in varchar2 default null
561 ,p_attribute20 in varchar2 default null
562 ,p_offering_id out nocopy number
563 ,p_object_version_number out nocopy number
564 ,p_data_source in varchar2 default null
565 ,p_vendor_id in number default null
566 ,p_competency_update_level in varchar2 default null
567 ,p_language_code in varchar2 default null -- 2733966
568
569 ) is
570 --
571 l_rec ota_off_shd.g_rec_type;
572 l_proc varchar2(72) := g_package||'ins';
573 --
574 Begin
575 hr_utility.set_location('Entering:'||l_proc, 5);
576 --
577 -- Call conversion function to turn arguments into the
578 -- p_rec structure.
579 --
580 l_rec :=
581 ota_off_shd.convert_args
582 (null
583 ,p_activity_version_id
584 ,p_business_group_id
585 ,p_name
586 ,p_start_date
587 ,p_end_date
588 ,p_owner_id
589 ,p_delivery_mode_id
590 ,p_language_id
591 ,p_duration
592 ,p_duration_units
593 ,p_learning_object_id
594 ,p_player_toolbar_flag
595 ,p_player_toolbar_bitset
596 ,p_player_new_window_flag
597 ,p_maximum_attendees
598 ,p_maximum_internal_attendees
599 ,p_minimum_attendees
600 ,p_actual_cost
601 ,p_budget_cost
602 ,p_budget_currency_code
603 ,p_price_basis
604 ,p_currency_code
605 ,p_standard_price
606 ,null
607 ,p_attribute_category
608 ,p_attribute1
609 ,p_attribute2
610 ,p_attribute3
611 ,p_attribute4
612 ,p_attribute5
613 ,p_attribute6
614 ,p_attribute7
615 ,p_attribute8
616 ,p_attribute9
617 ,p_attribute10
618 ,p_attribute11
619 ,p_attribute12
620 ,p_attribute13
621 ,p_attribute14
622 ,p_attribute15
623 ,p_attribute16
624 ,p_attribute17
625 ,p_attribute18
626 ,p_attribute19
627 ,p_attribute20
628 ,p_data_source
629 ,p_vendor_id
630 ,p_competency_update_level
631 ,p_language_code -- 2733966
632
633 );
634 --
635 -- Having converted the arguments into the ota_off_rec
636 -- plsql record structure we call the corresponding record business process.
637 --
638 ota_off_ins.ins
639 (p_effective_date
643 --
640 ,l_rec
641 ,p_name
642 );
644 -- As the primary key argument(s)
645 -- are specified as an OUT's we must set these values.
646 --
647 p_offering_id := l_rec.offering_id;
648 p_object_version_number := l_rec.object_version_number;
649 --
650 hr_utility.set_location(' Leaving:'||l_proc, 10);
651 End ins;
652 --
653 end ota_off_ins;