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