4 --
1 Package Body ota_lpm_ins as
2 /* $Header: otlpmrhi.pkb 120.0 2005/05/29 07:22:37 appldev noship $ */
3
5 -- ----------------------------------------------------------------------------
6 -- | Private Global Definitions |
7 -- ----------------------------------------------------------------------------
8 --
9 g_package varchar2(33) := ' ota_lpm_ins.'; -- Global package name
10 --
11 -- The following global variables are only to be used by
12 -- the set_base_key_value and pre_insert procedures.
13 --
14 g_learning_path_member_id_i number default null;
15 --
16 -- ----------------------------------------------------------------------------
17 -- |------------------------< set_base_key_value >----------------------------|
18 -- ----------------------------------------------------------------------------
19 procedure set_base_key_value
20 (p_learning_path_member_id in number) is
21 --
22 l_proc varchar2(72) := g_package||'set_base_key_value';
23 --
24 Begin
25 hr_utility.set_location('Entering:'||l_proc, 10);
26 --
27 ota_lpm_ins.g_learning_path_member_id_i := p_learning_path_member_id;
28 --
29 hr_utility.set_location(' Leaving:'||l_proc, 20);
30 End set_base_key_value;
31 --
32 --
33 -- ----------------------------------------------------------------------------
34 -- |------------------------------< insert_dml >------------------------------|
35 -- ----------------------------------------------------------------------------
36 -- {Start Of Comments}
37 --
38 -- Description:
39 -- This procedure controls the actual dml insert logic. The processing of
40 -- this procedure are as follows:
41 -- 1) Initialise the object_version_number to 1 if the object_version_number
42 -- is defined as an attribute for this entity.
43 -- 2) To set and unset the g_api_dml status as required (as we are about to
44 -- perform dml).
45 -- 3) To insert the row into the schema.
46 -- 4) To trap any constraint violations that may have occurred.
47 -- 5) To raise any other errors.
48 --
49 -- Prerequisites:
50 -- This is an internal private procedure which must be called from the ins
51 -- procedure and must have all mandatory attributes set (except the
52 -- object_version_number which is initialised within this procedure).
53 --
54 -- In Parameters:
55 -- A Pl/Sql record structre.
56 --
57 -- Post Success:
61 -- On the insert dml failure it is important to note that we always reset the
58 -- The specified row will be inserted into the schema.
59 --
60 -- Post Failure:
62 -- g_api_dml status to false.
63 -- If a check, unique or parent integrity constraint violation is raised the
64 -- constraint_error procedure will be called.
65 -- If any other error is reported, the error will be raised after the
66 -- g_api_dml status is reset.
67 --
68 -- Developer Implementation Notes:
69 -- None.
70 --
71 -- Access Status:
72 -- Internal Row Handler Use Only.
73 --
74 -- {End Of Comments}
75 -- ----------------------------------------------------------------------------
76 Procedure insert_dml
77 (p_rec in out nocopy ota_lpm_shd.g_rec_type
78 ) is
79 --
80 l_proc varchar2(72) := g_package||'insert_dml';
81 --
82 Begin
83 hr_utility.set_location('Entering:'||l_proc, 5);
84 p_rec.object_version_number := 1; -- Initialise the object version
85 --
86 --
87 --
88 -- Insert the row into: ota_learning_path_members
89 --
90 insert into ota_learning_path_members
91 (learning_path_member_id
92 ,learning_path_id
93 ,activity_version_id
94 ,course_sequence
95 ,business_group_id
96 ,duration
97 ,duration_units
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 ,learning_path_section_id
121 ,notify_days_before_target
122 )
123 Values
124 (p_rec.learning_path_member_id
125 ,p_rec.learning_path_id
126 ,p_rec.activity_version_id
127 ,p_rec.course_sequence
128 ,p_rec.business_group_id
129 ,p_rec.duration
130 ,p_rec.duration_units
131 ,p_rec.object_version_number
132 ,p_rec.attribute_category
133 ,p_rec.attribute1
134 ,p_rec.attribute2
135 ,p_rec.attribute3
136 ,p_rec.attribute4
137 ,p_rec.attribute5
138 ,p_rec.attribute6
139 ,p_rec.attribute7
140 ,p_rec.attribute8
141 ,p_rec.attribute9
142 ,p_rec.attribute10
143 ,p_rec.attribute11
144 ,p_rec.attribute12
145 ,p_rec.attribute13
146 ,p_rec.attribute14
147 ,p_rec.attribute15
148 ,p_rec.attribute16
149 ,p_rec.attribute17
150 ,p_rec.attribute18
151 ,p_rec.attribute19
152 ,p_rec.attribute20
153 ,p_rec.learning_path_section_id
154 ,p_rec.notify_days_before_target
155 );
156 --
157 --
158 --
159 hr_utility.set_location(' Leaving:'||l_proc, 10);
160 Exception
161 When hr_api.check_integrity_violated Then
162 -- A check constraint has been violated
163 --
164 ota_lpm_shd.constraint_error
165 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
166 When hr_api.parent_integrity_violated Then
167 -- Parent integrity has been violated
168 --
169 ota_lpm_shd.constraint_error
170 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
171 When hr_api.unique_integrity_violated Then
172 -- Unique integrity has been violated
173 --
174 ota_lpm_shd.constraint_error
175 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
176 When Others Then
177 --
178 Raise;
179 End insert_dml;
180 --
181 -- ----------------------------------------------------------------------------
182 -- |------------------------------< pre_insert >------------------------------|
183 -- ----------------------------------------------------------------------------
184 -- {Start Of Comments}
185 --
186 -- Description:
187 -- This private procedure contains any processing which is required before
188 -- the insert dml. Presently, if the entity has a corresponding primary
189 -- key which is maintained by an associating sequence, the primary key for
190 -- the entity will be populated with the next sequence value in
191 -- preparation for the insert dml.
192 --
193 -- Prerequisites:
194 -- This is an internal procedure which is called from the ins procedure.
195 --
196 -- In Parameters:
197 -- A Pl/Sql record structure.
198 --
199 -- Post Success:
200 -- Processing continues.
201 --
202 -- Post Failure:
203 -- If an error has occurred, an error message and exception will be raised
204 -- but not handled.
205 --
206 -- Developer Implementation Notes:
207 -- Any pre-processing required before the insert dml is issued should be
208 -- coded within this procedure. As stated above, a good example is the
209 -- generation of a primary key number via a corresponding sequence.
210 -- It is important to note that any 3rd party maintenance should be reviewed
211 -- before placing in this procedure.
212 --
213 -- Access Status:
214 -- Internal Row Handler Use Only.
215 --
216 -- {End Of Comments}
217 -- ----------------------------------------------------------------------------
221 --
218 Procedure pre_insert
219 (p_rec in out nocopy ota_lpm_shd.g_rec_type
220 ) is
222 Cursor C_Sel1 is select ota_learning_path_members_s.nextval from sys.dual;
223 --
224 Cursor C_Sel2 is
225 Select null
226 from ota_learning_path_members
227 where learning_path_member_id =
228 ota_lpm_ins.g_learning_path_member_id_i;
229 --
230 l_proc varchar2(72) := g_package||'pre_insert';
231 l_exists varchar2(1);
232 --
233 Begin
234 hr_utility.set_location('Entering:'||l_proc, 5);
235 --
236 If (ota_lpm_ins.g_learning_path_member_id_i is not null) Then
237 --
238 -- Verify registered primary key values not already in use
239 --
240 Open C_Sel2;
241 Fetch C_Sel2 into l_exists;
242 If C_Sel2%found Then
243 Close C_Sel2;
244 --
245 -- The primary key values are already in use.
246 --
247 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
248 fnd_message.set_token('TABLE_NAME','ota_learning_path_members');
249 fnd_message.raise_error;
250 End If;
251 Close C_Sel2;
252 --
253 -- Use registered key values and clear globals
254 --
255 p_rec.learning_path_member_id :=
256 ota_lpm_ins.g_learning_path_member_id_i;
257 ota_lpm_ins.g_learning_path_member_id_i := null;
258 Else
259 --
260 -- No registerd key values, so select the next sequence number
261 --
262 --
263 -- Select the next sequence number
264 --
265 Open C_Sel1;
266 Fetch C_Sel1 Into p_rec.learning_path_member_id;
267 Close C_Sel1;
268 End If;
269 --
270 hr_utility.set_location(' Leaving:'||l_proc, 10);
271 End pre_insert;
272 --
273 -- ----------------------------------------------------------------------------
274 -- |-----------------------------< post_insert >------------------------------|
275 -- ----------------------------------------------------------------------------
276 -- {Start Of Comments}
277 --
278 -- Description:
279 -- This private procedure contains any processing which is required after
280 -- the insert dml.
281 --
282 -- Prerequisites:
283 -- This is an internal procedure which is called from the ins procedure.
284 --
285 -- In Parameters:
286 -- A Pl/Sql record structre.
287 --
288 -- Post Success:
289 -- Processing continues.
290 --
291 -- Post Failure:
292 -- If an error has occurred, an error message and exception will be raised
293 -- but not handled.
294 --
295 -- Developer Implementation Notes:
296 -- Any post-processing required after the insert dml is issued should be
297 -- coded within this procedure. It is important to note that any 3rd party
298 -- maintenance should be reviewed before placing in this procedure.
299 --
300 -- Access Status:
301 -- Internal Row Handler Use Only.
302 --
303 -- {End Of Comments}
304 -- ----------------------------------------------------------------------------
305 Procedure post_insert
306 (p_effective_date in date
307 ,p_rec in ota_lpm_shd.g_rec_type
308 ) is
309 --
310 l_proc varchar2(72) := g_package||'post_insert';
311 --
312 Begin
313 hr_utility.set_location('Entering:'||l_proc, 5);
314 begin
315 --
316 ota_lpm_rki.after_insert
317 (p_effective_date => p_effective_date
318 ,p_learning_path_member_id
319 => p_rec.learning_path_member_id
320 ,p_learning_path_id
321 => p_rec.learning_path_id
322 ,p_activity_version_id
323 => p_rec.activity_version_id
324 ,p_course_sequence
325 => p_rec.course_sequence
326 ,p_business_group_id
327 => p_rec.business_group_id
328 ,p_duration
329 => p_rec.duration
330 ,p_duration_units
331 => p_rec.duration_units
332 ,p_object_version_number
333 => p_rec.object_version_number
334 ,p_attribute_category
335 => p_rec.attribute_category
336 ,p_attribute1
337 => p_rec.attribute1
338 ,p_attribute2
339 => p_rec.attribute2
340 ,p_attribute3
341 => p_rec.attribute3
342 ,p_attribute4
343 => p_rec.attribute4
344 ,p_attribute5
345 => p_rec.attribute5
346 ,p_attribute6
347 => p_rec.attribute6
348 ,p_attribute7
349 => p_rec.attribute7
350 ,p_attribute8
351 => p_rec.attribute8
352 ,p_attribute9
353 => p_rec.attribute9
354 ,p_attribute10
355 => p_rec.attribute10
356 ,p_attribute11
357 => p_rec.attribute11
358 ,p_attribute12
359 => p_rec.attribute12
360 ,p_attribute13
361 => p_rec.attribute13
362 ,p_attribute14
363 => p_rec.attribute14
364 ,p_attribute15
365 => p_rec.attribute15
366 ,p_attribute16
367 => p_rec.attribute16
368 ,p_attribute17
369 => p_rec.attribute17
370 ,p_attribute18
371 => p_rec.attribute18
372 ,p_attribute19
373 => p_rec.attribute19
374 ,p_attribute20
375 => p_rec.attribute20
376 ,p_learning_path_section_id
377 => p_rec.learning_path_section_id
378 ,p_notify_days_before_target
382 exception
379 => p_rec.notify_days_before_target
380 );
381 --
383 --
384 when hr_api.cannot_find_prog_unit then
385 --
386 hr_api.cannot_find_prog_unit_error
387 (p_module_name => 'OTA_LEARNING_PATH_MEMBERS'
388 ,p_hook_type => 'AI');
389 --
390 end;
391 --
392 hr_utility.set_location(' Leaving:'||l_proc, 10);
393 End post_insert;
394 --
395 -- ----------------------------------------------------------------------------
396 -- |---------------------------------< ins >----------------------------------|
397 -- ----------------------------------------------------------------------------
398 Procedure ins
399 (p_effective_date in date
400 ,p_rec in out nocopy ota_lpm_shd.g_rec_type
401 ) is
402 --
403 l_proc varchar2(72) := g_package||'ins';
404 --
405 Begin
406 hr_utility.set_location('Entering:'||l_proc, 5);
407 --
408 -- Call the supporting insert validate operations
409 --
410 ota_lpm_bus.insert_validate
411 (p_effective_date
412 ,p_rec
413 );
414 --
415 -- Call to raise any errors on multi-message list
416 hr_multi_message.end_validation_set;
417 --
418 -- Call the supporting pre-insert operation
419 --
420 ota_lpm_ins.pre_insert(p_rec);
421 --
422 -- Insert the row
423 --
424 ota_lpm_ins.insert_dml(p_rec);
425 --
426 -- Call the supporting post-insert operation
427 --
428 ota_lpm_ins.post_insert
429 (p_effective_date
430 ,p_rec
431 );
432 --
433 -- Call to raise any errors on multi-message list
434 hr_multi_message.end_validation_set;
435 --
436 hr_utility.set_location('Leaving:'||l_proc, 20);
437 end ins;
438 --
439 -- ----------------------------------------------------------------------------
440 -- |---------------------------------< ins >----------------------------------|
441 -- ----------------------------------------------------------------------------
442 Procedure ins
443 (p_effective_date in date
444 ,p_learning_path_id in number
445 ,p_activity_version_id in number
446 ,p_business_group_id in number
447 ,p_course_sequence in number default null
448 ,p_duration in number default null
449 ,p_duration_units in varchar2 default null
450 ,p_attribute_category in varchar2 default null
451 ,p_attribute1 in varchar2 default null
452 ,p_attribute2 in varchar2 default null
453 ,p_attribute3 in varchar2 default null
454 ,p_attribute4 in varchar2 default null
455 ,p_attribute5 in varchar2 default null
456 ,p_attribute6 in varchar2 default null
457 ,p_attribute7 in varchar2 default null
458 ,p_attribute8 in varchar2 default null
459 ,p_attribute9 in varchar2 default null
460 ,p_attribute10 in varchar2 default null
461 ,p_attribute11 in varchar2 default null
462 ,p_attribute12 in varchar2 default null
463 ,p_attribute13 in varchar2 default null
464 ,p_attribute14 in varchar2 default null
465 ,p_attribute15 in varchar2 default null
466 ,p_attribute16 in varchar2 default null
467 ,p_attribute17 in varchar2 default null
468 ,p_attribute18 in varchar2 default null
469 ,p_attribute19 in varchar2 default null
470 ,p_attribute20 in varchar2 default null
471 ,p_learning_path_section_id in number
472 ,p_notify_days_before_target in number default null
473 ,p_learning_path_member_id out nocopy number
474 ,p_object_version_number out nocopy number
475 ) is
476 --
477 l_rec ota_lpm_shd.g_rec_type;
478 l_proc varchar2(72) := g_package||'ins';
479 --
480 Begin
481 hr_utility.set_location('Entering:'||l_proc, 5);
482 --
483 -- Call conversion function to turn arguments into the
484 -- p_rec structure.
485 --
486 l_rec :=
487 ota_lpm_shd.convert_args
488 (null
489 ,p_learning_path_id
490 ,p_activity_version_id
491 ,p_course_sequence
492 ,p_business_group_id
493 ,p_duration
494 ,p_duration_units
495 ,null
496 ,p_attribute_category
497 ,p_attribute1
498 ,p_attribute2
499 ,p_attribute3
500 ,p_attribute4
501 ,p_attribute5
502 ,p_attribute6
503 ,p_attribute7
504 ,p_attribute8
505 ,p_attribute9
506 ,p_attribute10
507 ,p_attribute11
508 ,p_attribute12
509 ,p_attribute13
510 ,p_attribute14
511 ,p_attribute15
512 ,p_attribute16
513 ,p_attribute17
514 ,p_attribute18
515 ,p_attribute19
516 ,p_attribute20
517 ,p_learning_path_section_id
518 ,p_notify_days_before_target
519 );
520 --
521 -- Having converted the arguments into the ota_lpm_rec
522 -- plsql record structure we call the corresponding record business process.
523 --
524 ota_lpm_ins.ins
525 (p_effective_date
526 ,l_rec
527 );
528 --
532 p_learning_path_member_id := l_rec.learning_path_member_id;
529 -- As the primary key argument(s)
530 -- are specified as an OUT's we must set these values.
531 --
533 p_object_version_number := l_rec.object_version_number;
534 --
535 hr_utility.set_location(' Leaving:'||l_proc, 10);
536 End ins;
537 --
538 end ota_lpm_ins;
[Home] [Help]