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