[Home] [Help]
PACKAGE BODY: APPS.PER_CEO_INS
Source
1 Package Body per_ceo_ins as
2 /* $Header: peceorhi.pkb 120.1.12000000.2 2007/04/16 11:05:48 arumukhe ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_ceo_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_comp_element_outcome_id_i number default null;
14 --
15 -- ----------------------------------------------------------------------------
16 -- |------------------------< set_base_key_value >----------------------------|
17 -- ----------------------------------------------------------------------------
18 procedure set_base_key_value
19 (p_comp_element_outcome_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 per_ceo_ins.g_comp_element_outcome_id_i := p_comp_element_outcome_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 per_ceo_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 per_ceo_shd.g_api_dml := true; -- Set the api dml status
86 --
87 -- Insert the row into: per_comp_element_outcomes
88 --
89 insert into per_comp_element_outcomes
90 (comp_element_outcome_id
91 ,competence_element_id
95 ,attribute_category
92 ,outcome_id
93 ,date_from
94 ,date_to
96 ,attribute1
97 ,attribute2
98 ,attribute3
99 ,attribute4
100 ,attribute5
101 ,attribute6
102 ,attribute7
103 ,attribute8
104 ,attribute9
105 ,attribute10
106 ,attribute11
107 ,attribute12
108 ,attribute13
109 ,attribute14
110 ,attribute15
111 ,attribute16
112 ,attribute17
113 ,attribute18
114 ,attribute19
115 ,attribute20
116 ,information_category
117 ,information1
118 ,information2
119 ,information3
120 ,information4
121 ,information5
122 ,information6
123 ,information7
124 ,information8
125 ,information9
126 ,information10
127 ,information11
128 ,information12
129 ,information13
130 ,information14
131 ,information15
132 ,information16
133 ,information17
134 ,information18
135 ,information19
136 ,information20
137 ,object_version_number
138 )
139 Values
140 (p_rec.comp_element_outcome_id
141 ,p_rec.competence_element_id
142 ,p_rec.outcome_id
143 ,p_rec.date_from
144 ,p_rec.date_to
145 ,p_rec.attribute_category
146 ,p_rec.attribute1
147 ,p_rec.attribute2
148 ,p_rec.attribute3
149 ,p_rec.attribute4
150 ,p_rec.attribute5
151 ,p_rec.attribute6
152 ,p_rec.attribute7
153 ,p_rec.attribute8
154 ,p_rec.attribute9
155 ,p_rec.attribute10
156 ,p_rec.attribute11
157 ,p_rec.attribute12
158 ,p_rec.attribute13
159 ,p_rec.attribute14
160 ,p_rec.attribute15
161 ,p_rec.attribute16
162 ,p_rec.attribute17
163 ,p_rec.attribute18
164 ,p_rec.attribute19
165 ,p_rec.attribute20
166 ,p_rec.information_category
167 ,p_rec.information1
168 ,p_rec.information2
169 ,p_rec.information3
170 ,p_rec.information4
171 ,p_rec.information5
172 ,p_rec.information6
173 ,p_rec.information7
174 ,p_rec.information8
175 ,p_rec.information9
176 ,p_rec.information10
177 ,p_rec.information11
178 ,p_rec.information12
179 ,p_rec.information13
180 ,p_rec.information14
181 ,p_rec.information15
182 ,p_rec.information16
183 ,p_rec.information17
184 ,p_rec.information18
185 ,p_rec.information19
186 ,p_rec.information20
187 ,p_rec.object_version_number
188 );
189 --
190 per_ceo_shd.g_api_dml := false; -- Unset the api dml status
191 --
192 hr_utility.set_location(' Leaving:'||l_proc, 10);
193 Exception
194 When hr_api.check_integrity_violated Then
195 -- A check constraint has been violated
196 per_ceo_shd.g_api_dml := false; -- Unset the api dml status
197 per_ceo_shd.constraint_error
198 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
199 When hr_api.parent_integrity_violated Then
200 -- Parent integrity has been violated
201 per_ceo_shd.g_api_dml := false; -- Unset the api dml status
202 per_ceo_shd.constraint_error
203 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
204 When hr_api.unique_integrity_violated Then
205 -- Unique integrity has been violated
206 per_ceo_shd.g_api_dml := false; -- Unset the api dml status
207 per_ceo_shd.constraint_error
208 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
209 When Others Then
210 per_ceo_shd.g_api_dml := false; -- Unset the api dml status
211 Raise;
212 End insert_dml;
213 --
214 -- ----------------------------------------------------------------------------
215 -- |------------------------------< pre_insert >------------------------------|
216 -- ----------------------------------------------------------------------------
217 -- {Start Of Comments}
218 --
219 -- Description:
220 -- This private procedure contains any processing which is required before
221 -- the insert dml. Presently, if the entity has a corresponding primary
222 -- key which is maintained by an associating sequence, the primary key for
223 -- the entity will be populated with the next sequence value in
224 -- preparation for the insert dml.
225 --
226 -- Prerequisites:
227 -- This is an internal procedure which is called from the ins procedure.
228 --
229 -- In Parameters:
230 -- A Pl/Sql record structure.
231 --
232 -- Post Success:
233 -- Processing continues.
234 --
235 -- Post Failure:
236 -- If an error has occurred, an error message and exception will be raised
237 -- but not handled.
238 --
239 -- Developer Implementation Notes:
240 -- Any pre-processing required before the insert dml is issued should be
241 -- coded within this procedure. As stated above, a good example is the
242 -- generation of a primary key number via a corresponding sequence.
243 -- It is important to note that any 3rd party maintenance should be reviewed
244 -- before placing in this procedure.
245 --
246 -- Access Status:
247 -- Internal Row Handler Use Only.
248 --
249 -- {End Of Comments}
250 -- ----------------------------------------------------------------------------
251 Procedure pre_insert
252 (p_rec in out nocopy per_ceo_shd.g_rec_type
253 ) is
254 --
258 --
255 Cursor C_Sel1 is
256 select per_comp_element_outcomes_s.nextval
257 from sys.dual;
259 Cursor C_Sel2 is
260 Select null
261 from per_comp_element_outcomes
262 where comp_element_outcome_id =
263 per_ceo_ins.g_comp_element_outcome_id_i;
264 --
265 l_proc varchar2(72) := g_package||'pre_insert';
266 l_exists varchar2(1);
267 --
268 Begin
269 hr_utility.set_location('Entering:'||l_proc, 5);
270 --
271 If (per_ceo_ins.g_comp_element_outcome_id_i is not null) Then
272 --
273 -- Verify registered primary key values not already in use
274 --
275 Open C_Sel2;
276 Fetch C_Sel2 into l_exists;
277 If C_Sel2%found Then
278 Close C_Sel2;
279 --
280 -- The primary key values are already in use.
281 --
282 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
283 fnd_message.set_token('TABLE_NAME','per_comp_element_outcomes');
284 fnd_message.raise_error;
285 End If;
286 Close C_Sel2;
287 --
288 -- Use registered key values and clear globals
289 --
290 p_rec.comp_element_outcome_id :=
291 per_ceo_ins.g_comp_element_outcome_id_i;
292 per_ceo_ins.g_comp_element_outcome_id_i := null;
293 Else
294 --
295 -- No registerd key values, so select the next sequence number
296 --
297 --
298 -- Select the next sequence number
299 --
300 Open C_Sel1;
301 Fetch C_Sel1 Into p_rec.comp_element_outcome_id;
302 Close C_Sel1;
303 End If;
304 --
305 hr_utility.set_location(' Leaving:'||l_proc, 10);
306 End pre_insert;
307 --
308 -- ----------------------------------------------------------------------------
309 -- |-----------------------------< post_insert >------------------------------|
310 -- ----------------------------------------------------------------------------
311 -- {Start Of Comments}
312 --
313 -- Description:
314 -- This private procedure contains any processing which is required after
315 -- the insert dml.
316 --
317 -- Prerequisites:
318 -- This is an internal procedure which is called from the ins procedure.
319 --
320 -- In Parameters:
321 -- A Pl/Sql record structre.
322 --
323 -- Post Success:
324 -- Processing continues.
325 --
326 -- Post Failure:
327 -- If an error has occurred, an error message and exception will be raised
328 -- but not handled.
329 --
330 -- Developer Implementation Notes:
331 -- Any post-processing required after the insert dml is issued should be
332 -- coded within this procedure. It is important to note that any 3rd party
333 -- maintenance should be reviewed before placing in this procedure.
334 --
335 -- Access Status:
336 -- Internal Row Handler Use Only.
337 --
338 -- {End Of Comments}
339 -- ----------------------------------------------------------------------------
340 Procedure post_insert
341 (p_effective_date in date
342 ,p_rec in per_ceo_shd.g_rec_type
343 ) is
344 --
345 l_proc varchar2(72) := g_package||'post_insert';
346 --
347 Begin
348 hr_utility.set_location('Entering:'||l_proc, 5);
349 begin
350 --
351 per_ceo_rki.after_insert
352 (p_effective_date => p_effective_date
353 ,p_comp_element_outcome_id
354 => p_rec.comp_element_outcome_id
355 ,p_competence_element_id
356 => p_rec.competence_element_id
357 ,p_outcome_id
358 => p_rec.outcome_id
359 ,p_date_from
360 => p_rec.date_from
361 ,p_date_to
362 => p_rec.date_to
363 ,p_object_version_number
364 => p_rec.object_version_number
365 ,p_attribute_category
366 => p_rec.attribute_category
367 ,p_attribute1
368 => p_rec.attribute1
369 ,p_attribute2
370 => p_rec.attribute2
371 ,p_attribute3
372 => p_rec.attribute3
373 ,p_attribute4
374 => p_rec.attribute4
375 ,p_attribute5
376 => p_rec.attribute5
377 ,p_attribute6
378 => p_rec.attribute6
379 ,p_attribute7
380 => p_rec.attribute7
381 ,p_attribute8
382 => p_rec.attribute8
383 ,p_attribute9
384 => p_rec.attribute9
385 ,p_attribute10
386 => p_rec.attribute10
387 ,p_attribute11
388 => p_rec.attribute11
389 ,p_attribute12
390 => p_rec.attribute12
391 ,p_attribute13
392 => p_rec.attribute13
393 ,p_attribute14
394 => p_rec.attribute14
395 ,p_attribute15
396 => p_rec.attribute15
397 ,p_attribute16
398 => p_rec.attribute16
399 ,p_attribute17
400 => p_rec.attribute17
401 ,p_attribute18
402 => p_rec.attribute18
403 ,p_attribute19
404 => p_rec.attribute19
405 ,p_attribute20
406 => p_rec.attribute20
407 ,p_information_category
408 => p_rec.information_category
409 ,p_information1
410 => p_rec.information1
411 ,p_information2
412 => p_rec.information2
413 ,p_information3
414 => p_rec.information3
415 ,p_information4
416 => p_rec.information4
417 ,p_information5
418 => p_rec.information5
419 ,p_information6
420 => p_rec.information6
424 => p_rec.information8
421 ,p_information7
422 => p_rec.information7
423 ,p_information8
425 ,p_information9
426 => p_rec.information9
427 ,p_information10
428 => p_rec.information10
429 ,p_information11
430 => p_rec.information11
431 ,p_information12
432 => p_rec.information12
433 ,p_information13
434 => p_rec.information13
435 ,p_information14
436 => p_rec.information14
437 ,p_information15
438 => p_rec.information15
439 ,p_information16
440 => p_rec.information16
441 ,p_information17
442 => p_rec.information17
443 ,p_information18
444 => p_rec.information18
445 ,p_information19
446 => p_rec.information19
447 ,p_information20
448 => p_rec.information20
449 );
450 --
451 exception
452 --
453 when hr_api.cannot_find_prog_unit then
454 --
455 hr_api.cannot_find_prog_unit_error
456 (p_module_name => 'PER_COMP_ELEMENT_OUTCOMES'
457 ,p_hook_type => 'AI');
458 --
459 end;
460 --
461 hr_utility.set_location(' Leaving:'||l_proc, 10);
462 End post_insert;
463 --
464 -- ----------------------------------------------------------------------------
465 -- |---------------------------------< ins >----------------------------------|
466 -- ----------------------------------------------------------------------------
467 Procedure ins
468 (p_effective_date in date
469 ,p_rec in out nocopy per_ceo_shd.g_rec_type
470 ) is
471 --
472 l_proc varchar2(72) := g_package||'ins';
473 --
474 Begin
475 hr_utility.set_location('Entering:'||l_proc, 10);
476 --
477 -- Call the supporting insert validate operations
478 --
479 hr_utility.set_location(l_proc, 11);
480 per_ceo_bus.insert_validate
481 (p_effective_date
482 ,p_rec
483 );
484 hr_utility.set_location(l_proc, 12);
485 --
486 -- Call to raise any errors on multi-message list
487 hr_multi_message.end_validation_set;
488 --
489 -- Call the supporting pre-insert operation
490 --
491 per_ceo_ins.pre_insert(p_rec);
492 --
493 -- Insert the row
494 --
495 per_ceo_ins.insert_dml(p_rec);
496 --
497 -- Call the supporting post-insert operation
498 --
499 per_ceo_ins.post_insert
500 (p_effective_date
501 ,p_rec
502 );
503 --
504 -- Call to raise any errors on multi-message list
505 hr_multi_message.end_validation_set;
506 --
507 hr_utility.set_location('Leaving:'||l_proc, 20);
508 end ins;
509 --
510 -- ----------------------------------------------------------------------------
511 -- |---------------------------------< ins >----------------------------------|
512 -- ----------------------------------------------------------------------------
513 Procedure ins
514 (p_effective_date in date
515 ,p_competence_element_id in number
516 ,p_outcome_id in number
517 ,p_date_from in date
518 ,p_date_to in date default null
519 ,p_attribute_category in varchar2 default null
520 ,p_attribute1 in varchar2 default null
521 ,p_attribute2 in varchar2 default null
522 ,p_attribute3 in varchar2 default null
523 ,p_attribute4 in varchar2 default null
524 ,p_attribute5 in varchar2 default null
525 ,p_attribute6 in varchar2 default null
526 ,p_attribute7 in varchar2 default null
527 ,p_attribute8 in varchar2 default null
528 ,p_attribute9 in varchar2 default null
529 ,p_attribute10 in varchar2 default null
530 ,p_attribute11 in varchar2 default null
531 ,p_attribute12 in varchar2 default null
532 ,p_attribute13 in varchar2 default null
533 ,p_attribute14 in varchar2 default null
534 ,p_attribute15 in varchar2 default null
535 ,p_attribute16 in varchar2 default null
536 ,p_attribute17 in varchar2 default null
537 ,p_attribute18 in varchar2 default null
538 ,p_attribute19 in varchar2 default null
539 ,p_attribute20 in varchar2 default null
540 ,p_information_category in varchar2 default null
541 ,p_information1 in varchar2 default null
542 ,p_information2 in varchar2 default null
543 ,p_information3 in varchar2 default null
544 ,p_information4 in varchar2 default null
545 ,p_information5 in varchar2 default null
546 ,p_information6 in varchar2 default null
547 ,p_information7 in varchar2 default null
548 ,p_information8 in varchar2 default null
549 ,p_information9 in varchar2 default null
550 ,p_information10 in varchar2 default null
551 ,p_information11 in varchar2 default null
552 ,p_information12 in varchar2 default null
553 ,p_information13 in varchar2 default null
554 ,p_information14 in varchar2 default null
555 ,p_information15 in varchar2 default null
556 ,p_information16 in varchar2 default null
557 ,p_information17 in varchar2 default null
558 ,p_information18 in varchar2 default null
559 ,p_information19 in varchar2 default null
560 ,p_information20 in varchar2 default null
561 ,p_comp_element_outcome_id out nocopy number
562 ,p_object_version_number out nocopy number
563 ) is
564 --
565 l_rec per_ceo_shd.g_rec_type;
566 l_proc varchar2(72) := g_package||'ins';
567 --
568 Begin
569 hr_utility.set_location('Entering:'||l_proc, 5);
570 --
571 -- Call conversion function to turn arguments into the
572 -- p_rec structure.
573 --
574 l_rec :=
575 per_ceo_shd.convert_args
576 (null
577 ,p_competence_element_id
578 ,p_outcome_id
579 ,p_date_from
580 ,p_date_to
581 ,p_attribute_category
582 ,p_attribute1
583 ,p_attribute2
584 ,p_attribute3
585 ,p_attribute4
586 ,p_attribute5
587 ,p_attribute6
588 ,p_attribute7
589 ,p_attribute8
590 ,p_attribute9
591 ,p_attribute10
592 ,p_attribute11
593 ,p_attribute12
594 ,p_attribute13
595 ,p_attribute14
596 ,p_attribute15
597 ,p_attribute16
598 ,p_attribute17
599 ,p_attribute18
600 ,p_attribute19
601 ,p_attribute20
602 ,p_information_category
603 ,p_information1
604 ,p_information2
605 ,p_information3
606 ,p_information4
607 ,p_information5
608 ,p_information6
609 ,p_information7
610 ,p_information8
611 ,p_information9
612 ,p_information10
613 ,p_information11
614 ,p_information12
615 ,p_information13
616 ,p_information14
617 ,p_information15
618 ,p_information16
619 ,p_information17
620 ,p_information18
621 ,p_information19
622 ,p_information20
623 ,null -- object_version_number
624 );
625 --
626 -- Having converted the arguments into the per_ceo_rec
627 -- plsql record structure we call the corresponding record business process.
628 --
629 hr_utility.set_location(l_proc, 6);
630
631 per_ceo_ins.ins
632 (p_effective_date
633 ,l_rec
634 );
635 --
636 -- As the primary key argument(s)
637 -- are specified as an OUT's we must set these values.
638 --
639 p_comp_element_outcome_id := l_rec.comp_element_outcome_id;
640 p_object_version_number := l_rec.object_version_number;
641 --
642 hr_utility.set_location(' Leaving:'||l_proc, 100);
643 End ins;
644 --
645 end per_ceo_ins;