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