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