DBA Data[Home] [Help]

PACKAGE BODY: APPS.PER_PDP_INS

Source


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