1 Package Body ota_cpe_ins as
2 /* $Header: otcperhi.pkb 120.3 2005/12/01 15:24 cmora noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_cpe_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_prd_enrollment_id_i number default null;
14 --
15 -- ----------------------------------------------------------------------------
16 -- |------------------------< set_base_key_value >----------------------------|
17 -- ----------------------------------------------------------------------------
18 procedure set_base_key_value
19 (p_cert_prd_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_cpe_ins.g_cert_prd_enrollment_id_i := p_cert_prd_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_cpe_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_prd_enrollments
88 --
89 insert into ota_cert_prd_enrollments
90 (cert_prd_enrollment_id
91 ,cert_enrollment_id
92 ,object_version_number
93 ,period_status_code
94 ,completion_date
95 ,cert_period_start_date
96 ,cert_period_end_date
97 ,business_group_id
98 ,attribute_category
99 ,attribute1
100 ,attribute2
101 ,attribute3
102 ,attribute4
103 ,attribute5
104 ,attribute6
105 ,attribute7
106 ,attribute8
107 ,attribute9
108 ,attribute10
109 ,attribute11
110 ,attribute12
111 ,attribute13
112 ,attribute14
113 ,attribute15
114 ,attribute16
115 ,attribute17
116 ,attribute18
117 ,attribute19
118 ,attribute20
119 ,expiration_date
120 )
121 Values
122 (p_rec.cert_prd_enrollment_id
123 ,p_rec.cert_enrollment_id
124 ,p_rec.object_version_number
125 ,p_rec.period_status_code
126 ,p_rec.completion_date
127 ,p_rec.cert_period_start_date
128 ,p_rec.cert_period_end_date
129 ,p_rec.business_group_id
130 ,p_rec.attribute_category
131 ,p_rec.attribute1
132 ,p_rec.attribute2
133 ,p_rec.attribute3
134 ,p_rec.attribute4
135 ,p_rec.attribute5
136 ,p_rec.attribute6
137 ,p_rec.attribute7
138 ,p_rec.attribute8
139 ,p_rec.attribute9
140 ,p_rec.attribute10
141 ,p_rec.attribute11
142 ,p_rec.attribute12
143 ,p_rec.attribute13
144 ,p_rec.attribute14
145 ,p_rec.attribute15
146 ,p_rec.attribute16
147 ,p_rec.attribute17
148 ,p_rec.attribute18
149 ,p_rec.attribute19
150 ,p_rec.attribute20
151 ,p_rec.expiration_date
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_cpe_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_cpe_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_cpe_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_cpe_shd.g_rec_type
217 ) is
218 --
219 Cursor C_Sel1 is select ota_cert_prd_enrollments_s.nextval from sys.dual;
220 --
221 Cursor C_Sel2 is
222 Select null
223 from ota_cert_prd_enrollments
224 where cert_prd_enrollment_id =
225 ota_cpe_ins.g_cert_prd_enrollment_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_cpe_ins.g_cert_prd_enrollment_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_cert_prd_enrollments');
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.cert_prd_enrollment_id :=
253 ota_cpe_ins.g_cert_prd_enrollment_id_i;
254 ota_cpe_ins.g_cert_prd_enrollment_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.cert_prd_enrollment_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_cpe_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_cpe_rki.after_insert
314 (p_effective_date => p_effective_date
315 ,p_cert_prd_enrollment_id
316 => p_rec.cert_prd_enrollment_id
317 ,p_cert_enrollment_id
318 => p_rec.cert_enrollment_id
319 ,p_object_version_number
320 => p_rec.object_version_number
321 ,p_period_status_code
322 => p_rec.period_status_code
323 ,p_completion_date
324 => p_rec.completion_date
325 ,p_cert_period_start_date
326 => p_rec.cert_period_start_date
327 ,p_cert_period_end_date
328 => p_rec.cert_period_end_date
329 ,p_business_group_id
330 => p_rec.business_group_id
331 ,p_attribute_category
332 => p_rec.attribute_category
333 ,p_attribute1
334 => p_rec.attribute1
335 ,p_attribute2
336 => p_rec.attribute2
337 ,p_attribute3
338 => p_rec.attribute3
339 ,p_attribute4
340 => p_rec.attribute4
341 ,p_attribute5
342 => p_rec.attribute5
343 ,p_attribute6
344 => p_rec.attribute6
345 ,p_attribute7
346 => p_rec.attribute7
347 ,p_attribute8
348 => p_rec.attribute8
349 ,p_attribute9
350 => p_rec.attribute9
351 ,p_attribute10
352 => p_rec.attribute10
353 ,p_attribute11
354 => p_rec.attribute11
355 ,p_attribute12
356 => p_rec.attribute12
357 ,p_attribute13
358 => p_rec.attribute13
359 ,p_attribute14
360 => p_rec.attribute14
361 ,p_attribute15
362 => p_rec.attribute15
363 ,p_attribute16
364 => p_rec.attribute16
365 ,p_attribute17
366 => p_rec.attribute17
367 ,p_attribute18
368 => p_rec.attribute18
369 ,p_attribute19
370 => p_rec.attribute19
371 ,p_attribute20
372 => p_rec.attribute20
373 ,p_expiration_date
374 => p_rec.expiration_date
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_CERT_PRD_ENROLLMENTS'
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_cpe_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_cpe_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_cpe_ins.pre_insert(p_rec);
416 --
417 -- Insert the row
418 --
419 ota_cpe_ins.insert_dml(p_rec);
420 --
421 -- Call the supporting post-insert operation
422 --
423 ota_cpe_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_cert_enrollment_id in number
440 ,p_period_status_code in varchar2
441 ,p_cert_period_start_date in date
442 ,p_cert_period_end_date in date
443 ,p_completion_date in date default null
444 ,p_business_group_id in number default null
445 ,p_attribute_category in varchar2 default null
446 ,p_attribute1 in varchar2 default null
447 ,p_attribute2 in varchar2 default null
448 ,p_attribute3 in varchar2 default null
449 ,p_attribute4 in varchar2 default null
450 ,p_attribute5 in varchar2 default null
451 ,p_attribute6 in varchar2 default null
452 ,p_attribute7 in varchar2 default null
453 ,p_attribute8 in varchar2 default null
454 ,p_attribute9 in varchar2 default null
455 ,p_attribute10 in varchar2 default null
456 ,p_attribute11 in varchar2 default null
457 ,p_attribute12 in varchar2 default null
458 ,p_attribute13 in varchar2 default null
459 ,p_attribute14 in varchar2 default null
460 ,p_attribute15 in varchar2 default null
461 ,p_attribute16 in varchar2 default null
462 ,p_attribute17 in varchar2 default null
463 ,p_attribute18 in varchar2 default null
464 ,p_attribute19 in varchar2 default null
465 ,p_attribute20 in varchar2 default null
466 ,p_expiration_date in date default null
467 ,p_cert_prd_enrollment_id out nocopy number
468 ,p_object_version_number out nocopy number
469 ) is
470 --
471 l_rec ota_cpe_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_cpe_shd.convert_args
482 (null
483 ,p_cert_enrollment_id
484 ,null
485 ,p_period_status_code
486 ,p_completion_date
487 ,p_cert_period_start_date
488 ,p_cert_period_end_date
489 ,p_business_group_id
490 ,p_attribute_category
491 ,p_attribute1
492 ,p_attribute2
493 ,p_attribute3
494 ,p_attribute4
495 ,p_attribute5
496 ,p_attribute6
497 ,p_attribute7
498 ,p_attribute8
499 ,p_attribute9
500 ,p_attribute10
501 ,p_attribute11
502 ,p_attribute12
503 ,p_attribute13
504 ,p_attribute14
505 ,p_attribute15
506 ,p_attribute16
507 ,p_attribute17
508 ,p_attribute18
509 ,p_attribute19
510 ,p_attribute20
511 ,p_expiration_date
512 );
513 --
514 -- Having converted the arguments into the ota_cpe_rec
515 -- plsql record structure we call the corresponding record business process.
516 --
517 ota_cpe_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_cert_prd_enrollment_id := l_rec.cert_prd_enrollment_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_cpe_ins;
[Home] [Help]