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