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