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