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