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