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