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