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