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