1 Package Body ota_cci_ins as
2 /* $Header: otccirhi.pkb 120.1 2005/07/21 15:07 estreacy noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_cci_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_category_usage_id_i number default null;
14 g_certification_id_i number default null;
15 --
16 -- ----------------------------------------------------------------------------
17 -- |------------------------< set_base_key_value >----------------------------|
18 -- ----------------------------------------------------------------------------
19 procedure set_base_key_value
20 (p_category_usage_id in number
21 ,p_certification_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_cci_ins.g_category_usage_id_i := p_category_usage_id;
29 ota_cci_ins.g_certification_id_i := p_certification_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_cci_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_cert_cat_inclusions
91 --
92 insert into ota_cert_cat_inclusions
93 (category_usage_id
94 ,certification_id
95 ,object_version_number
96 ,start_date_active
97 ,end_date_active
98 ,primary_flag
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.category_usage_id
123 ,p_rec.certification_id
124 ,p_rec.object_version_number
125 ,p_rec.start_date_active
126 ,p_rec.end_date_active
127 ,p_rec.primary_flag
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_cci_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_cci_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_cci_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_cci_shd.g_rec_type
214 ) is
215 --
216 /*
217 Cursor C_Sel1 is select ota_cert_cat_inclusions_s.nextval from sys.dual;
218 --
219 Cursor C_Sel2 is
220 Select null
221 from ota_cert_cat_inclusions
222 where category_usage_id =
223 ota_cci_ins.g_category_usage_id_i
224 or certification_id =
225 ota_cci_ins.g_certification_id_i;
226 */
227 --
228 l_proc varchar2(72) := g_package||'pre_insert';
229 l_exists varchar2(1);
230 --
231 Begin
232 hr_utility.set_location('Entering:'||l_proc, 5);
233 --
234 /*
235 If (ota_cci_ins.g_category_usage_id_i is not null or
236 ota_cci_ins.g_certification_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_cert_cat_inclusions');
249 fnd_message.raise_error;
250 End If;
251 Close C_Sel2;
252 --
253 */
254 -- Use registered key values and clear globals
255 --
256 p_rec.category_usage_id :=
257 ota_cci_ins.g_category_usage_id_i;
258 ota_cci_ins.g_category_usage_id_i := null;
259 p_rec.certification_id :=
260 ota_cci_ins.g_certification_id_i;
261 ota_cci_ins.g_certification_id_i := null;
262 /*
263 Else
264 --
265 -- No registerd key values, so select the next sequence number
266 --
267 --
268 -- Select the next sequence number
269 --
270 Open C_Sel1;
271 Fetch C_Sel1 Into p_rec.certification_id;
272 Close C_Sel1;
273 End If;
274 */
275 --
276 hr_utility.set_location(' Leaving:'||l_proc, 10);
277 End pre_insert;
278 --
279 -- ----------------------------------------------------------------------------
280 -- |-----------------------------< post_insert >------------------------------|
281 -- ----------------------------------------------------------------------------
282 -- {Start Of Comments}
283 --
284 -- Description:
285 -- This private procedure contains any processing which is required after
286 -- the insert dml.
287 --
288 -- Prerequisites:
289 -- This is an internal procedure which is called from the ins procedure.
290 --
291 -- In Parameters:
292 -- A Pl/Sql record structre.
293 --
294 -- Post Success:
295 -- Processing continues.
296 --
297 -- Post Failure:
298 -- If an error has occurred, an error message and exception will be raised
299 -- but not handled.
300 --
301 -- Developer Implementation Notes:
302 -- Any post-processing required after the insert dml is issued should be
303 -- coded within this procedure. It is important to note that any 3rd party
304 -- maintenance should be reviewed before placing in this procedure.
305 --
306 -- Access Status:
307 -- Internal Row Handler Use Only.
308 --
309 -- {End Of Comments}
310 -- ----------------------------------------------------------------------------
311 Procedure post_insert
312 (p_effective_date in date
313 ,p_rec in ota_cci_shd.g_rec_type
314 ) is
315 --
316 l_proc varchar2(72) := g_package||'post_insert';
317 --
318 Begin
319 hr_utility.set_location('Entering:'||l_proc, 5);
320 begin
321 --
325 => p_rec.category_usage_id
322 ota_cci_rki.after_insert
323 (p_effective_date => p_effective_date
324 ,p_category_usage_id
326 ,p_certification_id
327 => p_rec.certification_id
328 ,p_object_version_number
329 => p_rec.object_version_number
330 ,p_start_date_active
331 => p_rec.start_date_active
332 ,p_end_date_active
333 => p_rec.end_date_active
334 ,p_primary_flag
335 => p_rec.primary_flag
336 ,p_attribute_category
337 => p_rec.attribute_category
338 ,p_attribute1
339 => p_rec.attribute1
340 ,p_attribute2
341 => p_rec.attribute2
342 ,p_attribute3
343 => p_rec.attribute3
344 ,p_attribute4
345 => p_rec.attribute4
346 ,p_attribute5
347 => p_rec.attribute5
348 ,p_attribute6
349 => p_rec.attribute6
350 ,p_attribute7
351 => p_rec.attribute7
352 ,p_attribute8
353 => p_rec.attribute8
354 ,p_attribute9
355 => p_rec.attribute9
356 ,p_attribute10
357 => p_rec.attribute10
358 ,p_attribute11
359 => p_rec.attribute11
360 ,p_attribute12
361 => p_rec.attribute12
362 ,p_attribute13
363 => p_rec.attribute13
364 ,p_attribute14
365 => p_rec.attribute14
366 ,p_attribute15
367 => p_rec.attribute15
368 ,p_attribute16
369 => p_rec.attribute16
370 ,p_attribute17
371 => p_rec.attribute17
372 ,p_attribute18
373 => p_rec.attribute18
374 ,p_attribute19
375 => p_rec.attribute19
376 ,p_attribute20
377 => p_rec.attribute20
378 );
379 --
380 exception
381 --
382 when hr_api.cannot_find_prog_unit then
383 --
384 hr_api.cannot_find_prog_unit_error
385 (p_module_name => 'OTA_CERT_CAT_INCLUSIONS'
386 ,p_hook_type => 'AI');
387 --
388 end;
389 --
390 hr_utility.set_location(' Leaving:'||l_proc, 10);
391 End post_insert;
392 --
393 -- ----------------------------------------------------------------------------
394 -- |---------------------------------< ins >----------------------------------|
395 -- ----------------------------------------------------------------------------
396 Procedure ins
397 (p_effective_date in date
398 ,p_rec in out nocopy ota_cci_shd.g_rec_type
399 ) is
400 --
401 l_proc varchar2(72) := g_package||'ins';
402 l_certification_id OTA_CERT_CAT_INCLUSIONS.certification_id%TYPE;
403 l_category_usage_id OTA_CERT_CAT_INCLUSIONS.category_usage_id%TYPE;
404
405 --
406 Begin
407 hr_utility.set_location('Entering:'||l_proc, 5);
408
409 l_certification_id := p_rec.certification_id;
410 l_category_usage_id := p_rec.category_usage_id;
411
412 ota_cci_ins.set_base_key_value(p_category_usage_id => l_category_usage_id
413 ,p_certification_id => l_certification_id);
414 --
415 -- Call the supporting insert validate operations
416 --
417 ota_cci_bus.insert_validate
418 (p_effective_date
419 ,p_rec
420 ,l_certification_id
421 ,l_category_usage_id
422 );
423 --
424 -- Call to raise any errors on multi-message list
425 hr_multi_message.end_validation_set;
426 --
427 -- Call the supporting pre-insert operation
428 --
429 ota_cci_ins.pre_insert(p_rec);
430 --
431 -- Insert the row
432 --
433 ota_cci_ins.insert_dml(p_rec);
434 --
435 -- Call the supporting post-insert operation
436 --
437 ota_cci_ins.post_insert
438 (p_effective_date
439 ,p_rec
440 );
441 --
442 -- Call to raise any errors on multi-message list
443 hr_multi_message.end_validation_set;
444 --
445 hr_utility.set_location('Leaving:'||l_proc, 20);
446 end ins;
447 --
448 -- ----------------------------------------------------------------------------
449 -- |---------------------------------< ins >----------------------------------|
450 -- ----------------------------------------------------------------------------
451 Procedure ins
452 (p_effective_date in date
453 ,p_certification_id in number
454 ,p_category_usage_id in number
455 ,p_start_date_active in date default null
456 ,p_end_date_active in date default null
457 ,p_primary_flag in varchar2 default null
458 ,p_attribute_category in varchar2 default null
459 ,p_attribute1 in varchar2 default null
460 ,p_attribute2 in varchar2 default null
461 ,p_attribute3 in varchar2 default null
462 ,p_attribute4 in varchar2 default null
463 ,p_attribute5 in varchar2 default null
464 ,p_attribute6 in varchar2 default null
465 ,p_attribute7 in varchar2 default null
466 ,p_attribute8 in varchar2 default null
467 ,p_attribute9 in varchar2 default null
468 ,p_attribute10 in varchar2 default null
469 ,p_attribute11 in varchar2 default null
470 ,p_attribute12 in varchar2 default null
471 ,p_attribute13 in varchar2 default null
472 ,p_attribute14 in varchar2 default null
473 ,p_attribute15 in varchar2 default null
474 ,p_attribute16 in varchar2 default null
475 ,p_attribute17 in varchar2 default null
476 ,p_attribute18 in varchar2 default null
477 ,p_attribute19 in varchar2 default null
478 ,p_attribute20 in varchar2 default null
479 --,p_category_usage_id out nocopy number
480 --,p_certification_id out nocopy number
481 ,p_object_version_number out nocopy number
482 ) is
483 --
484 l_rec ota_cci_shd.g_rec_type;
485 l_proc varchar2(72) := g_package||'ins';
486 --
487 Begin
488 hr_utility.set_location('Entering:'||l_proc, 5);
489 --
490 -- Call conversion function to turn arguments into the
491 -- p_rec structure.
492 --
493 l_rec :=
494 ota_cci_shd.convert_args
495 (p_category_usage_id
496 ,p_certification_id
497 ,null
498 ,p_start_date_active
499 ,p_end_date_active
500 ,p_primary_flag
501 ,p_attribute_category
502 ,p_attribute1
503 ,p_attribute2
504 ,p_attribute3
505 ,p_attribute4
506 ,p_attribute5
507 ,p_attribute6
508 ,p_attribute7
509 ,p_attribute8
510 ,p_attribute9
511 ,p_attribute10
512 ,p_attribute11
513 ,p_attribute12
514 ,p_attribute13
515 ,p_attribute14
516 ,p_attribute15
517 ,p_attribute16
518 ,p_attribute17
519 ,p_attribute18
520 ,p_attribute19
521 ,p_attribute20
522 );
523 --
524 -- Having converted the arguments into the ota_cci_rec
525 -- plsql record structure we call the corresponding record business process.
526 --
527 ota_cci_ins.ins
528 (p_effective_date
529 ,l_rec
530 );
531 --
532 -- As the primary key argument(s)
533 -- are specified as an OUT's we must set these values.
534 --
535 --p_category_usage_id := l_rec.category_usage_id;
536 --p_certification_id := l_rec.certification_id;
537 p_object_version_number := l_rec.object_version_number;
538 --
539 hr_utility.set_location(' Leaving:'||l_proc, 10);
540 End ins;
541 --
542 end ota_cci_ins;
[Home] [Help]