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