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