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