[Home] [Help]
PACKAGE BODY: APPS.PQH_CPD_INS
Source
1 Package Body pqh_cpd_ins as
2 /* $Header: pqcpdrhi.pkb 120.0 2005/05/29 01:44:39 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_cpd_ins.'; -- Global package name
9 g_debug boolean := hr_utility.debug_enabled;
10 --
11 -- The following global variables are only to be used by
12 -- the set_base_key_value and pre_insert procedures.
13 --
14 g_corps_definition_id_i number default null;
15 --
16 -- ----------------------------------------------------------------------------
17 -- |------------------------< set_base_key_value >----------------------------|
18 -- ----------------------------------------------------------------------------
19 procedure set_base_key_value
20 (p_corps_definition_id in number) is
21 --
22 l_proc varchar2(72);
23 --
24 Begin
25 g_debug := hr_utility.debug_enabled;
26 if g_debug then
27 l_proc := g_package||'set_base_key_value';
28 hr_utility.set_location('Entering:'||l_proc, 10);
29 end if;
30 --
31 pqh_cpd_ins.g_corps_definition_id_i := p_corps_definition_id;
32 --
33 if g_debug then
34 hr_utility.set_location(' Leaving:'||l_proc, 20);
35 end if;
36 End set_base_key_value;
37 --
38 --
39 -- ----------------------------------------------------------------------------
40 -- |------------------------------< insert_dml >------------------------------|
41 -- ----------------------------------------------------------------------------
42 -- {Start Of Comments}
43 --
44 -- Description:
45 -- This procedure controls the actual dml insert logic. The processing of
46 -- this procedure are as follows:
47 -- 1) Initialise the object_version_number to 1 if the object_version_number
48 -- is defined as an attribute for this entity.
49 -- 2) To set and unset the g_api_dml status as required (as we are about to
50 -- perform dml).
51 -- 3) To insert the row into the schema.
52 -- 4) To trap any constraint violations that may have occurred.
53 -- 5) To raise any other errors.
54 --
55 -- Prerequisites:
56 -- This is an internal private procedure which must be called from the ins
57 -- procedure and must have all mandatory attributes set (except the
58 -- object_version_number which is initialised within this procedure).
59 --
60 -- In Parameters:
61 -- A Pl/Sql record structre.
62 --
63 -- Post Success:
64 -- The specified row will be inserted into the schema.
65 --
66 -- Post Failure:
67 -- On the insert dml failure it is important to note that we always reset the
68 -- g_api_dml status to false.
69 -- If a check, unique or parent integrity constraint violation is raised the
70 -- constraint_error procedure will be called.
71 -- If any other error is reported, the error will be raised after the
72 -- g_api_dml status is reset.
73 --
74 -- Developer Implementation Notes:
75 -- None.
76 --
77 -- Access Status:
78 -- Internal Row Handler Use Only.
79 --
80 -- {End Of Comments}
81 -- ----------------------------------------------------------------------------
82 Procedure insert_dml
83 (p_rec in out nocopy pqh_cpd_shd.g_rec_type
84 ) is
85 --
86 l_proc varchar2(72) ;
87 --
88 Begin
89 if g_debug then
90 l_proc := g_package||'insert_dml';
91 hr_utility.set_location('Entering:'||l_proc, 5);
92 end if;
93 p_rec.object_version_number := 1; -- Initialise the object version
94 --
95 pqh_cpd_shd.g_api_dml := true; -- Set the api dml status
96 --
97 -- Insert the row into: pqh_corps_definitions
98 --
99 insert into pqh_corps_definitions
100 (corps_definition_id
101 ,business_group_id
102 ,name
103 ,status_cd
104 ,retirement_age
105 ,category_cd
106 ,recruitment_end_date
107 ,corps_type_cd
108 ,starting_grade_step_id
109 ,task_desc
110 ,secondment_threshold
111 ,normal_hours
112 ,normal_hours_frequency
113 ,minimum_hours
114 ,minimum_hours_frequency
115 ,attribute1
116 ,attribute2
117 ,attribute3
118 ,attribute4
119 ,attribute5
120 ,attribute6
121 ,attribute7
122 ,attribute8
123 ,attribute9
124 ,attribute10
125 ,attribute11
126 ,attribute12
127 ,attribute13
128 ,attribute14
129 ,attribute15
130 ,attribute16
131 ,attribute17
132 ,attribute18
133 ,attribute19
134 ,attribute20
135 ,attribute21
136 ,attribute22
137 ,attribute23
138 ,attribute24
139 ,attribute25
140 ,attribute26
141 ,attribute27
142 ,attribute28
143 ,attribute29
144 ,attribute30
145 ,attribute_category
146 ,object_version_number
147 ,type_of_ps
148 ,date_from
149 ,date_to
150 ,primary_prof_field_id
151 ,starting_grade_id
152 ,ben_pgm_id
153 ,probation_period
154 ,probation_units
155 )
156 Values
157 (p_rec.corps_definition_id
158 ,p_rec.business_group_id
159 ,p_rec.name
160 ,p_rec.status_cd
161 ,p_rec.retirement_age
162 ,p_rec.category_cd
163 ,p_rec.recruitment_end_date
164 ,p_rec.corps_type_cd
165 ,p_rec.starting_grade_step_id
166 ,p_rec.task_desc
167 ,p_rec.secondment_threshold
168 ,p_rec.normal_hours
169 ,p_rec.normal_hours_frequency
170 ,p_rec.minimum_hours
171 ,p_rec.minimum_hours_frequency
172 ,p_rec.attribute1
173 ,p_rec.attribute2
174 ,p_rec.attribute3
175 ,p_rec.attribute4
176 ,p_rec.attribute5
177 ,p_rec.attribute6
178 ,p_rec.attribute7
179 ,p_rec.attribute8
180 ,p_rec.attribute9
181 ,p_rec.attribute10
182 ,p_rec.attribute11
183 ,p_rec.attribute12
184 ,p_rec.attribute13
185 ,p_rec.attribute14
186 ,p_rec.attribute15
187 ,p_rec.attribute16
188 ,p_rec.attribute17
189 ,p_rec.attribute18
190 ,p_rec.attribute19
191 ,p_rec.attribute20
192 ,p_rec.attribute21
193 ,p_rec.attribute22
194 ,p_rec.attribute23
195 ,p_rec.attribute24
196 ,p_rec.attribute25
197 ,p_rec.attribute26
198 ,p_rec.attribute27
199 ,p_rec.attribute28
200 ,p_rec.attribute29
201 ,p_rec.attribute30
202 ,p_rec.attribute_category
203 ,p_rec.object_version_number
204 ,p_rec.type_of_ps
205 ,p_rec.date_from
206 ,p_rec.date_to
207 ,p_rec.primary_prof_field_id
208 ,p_rec.starting_grade_id
209 ,p_rec.ben_pgm_id
210 ,p_rec.probation_period
211 ,p_rec.probation_units
212 );
213 --
214 pqh_cpd_shd.g_api_dml := false; -- Unset the api dml status
215 --
216 if g_debug then
217 hr_utility.set_location(' Leaving:'||l_proc, 10);
218 end if;
219 Exception
220 When hr_api.check_integrity_violated Then
221 -- A check constraint has been violated
222 pqh_cpd_shd.g_api_dml := false; -- Unset the api dml status
223 pqh_cpd_shd.constraint_error
224 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
225 When hr_api.parent_integrity_violated Then
226 -- Parent integrity has been violated
227 pqh_cpd_shd.g_api_dml := false; -- Unset the api dml status
228 pqh_cpd_shd.constraint_error
229 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
230 When hr_api.unique_integrity_violated Then
231 -- Unique integrity has been violated
232 pqh_cpd_shd.g_api_dml := false; -- Unset the api dml status
233 pqh_cpd_shd.constraint_error
234 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
235 When Others Then
236 pqh_cpd_shd.g_api_dml := false; -- Unset the api dml status
237 Raise;
238 End insert_dml;
239 --
240 -- ----------------------------------------------------------------------------
241 -- |------------------------------< pre_insert >------------------------------|
242 -- ----------------------------------------------------------------------------
243 -- {Start Of Comments}
244 --
245 -- Description:
246 -- This private procedure contains any processing which is required before
247 -- the insert dml. Presently, if the entity has a corresponding primary
248 -- key which is maintained by an associating sequence, the primary key for
249 -- the entity will be populated with the next sequence value in
250 -- preparation for the insert dml.
251 --
252 -- Prerequisites:
253 -- This is an internal procedure which is called from the ins procedure.
254 --
255 -- In Parameters:
256 -- A Pl/Sql record structure.
257 --
258 -- Post Success:
259 -- Processing continues.
260 --
261 -- Post Failure:
262 -- If an error has occurred, an error message and exception will be raised
263 -- but not handled.
264 --
265 -- Developer Implementation Notes:
266 -- Any pre-processing required before the insert dml is issued should be
267 -- coded within this procedure. As stated above, a good example is the
268 -- generation of a primary key number via a corresponding sequence.
269 -- It is important to note that any 3rd party maintenance should be reviewed
270 -- before placing in this procedure.
271 --
272 -- Access Status:
273 -- Internal Row Handler Use Only.
274 --
275 -- {End Of Comments}
276 -- ----------------------------------------------------------------------------
277 Procedure pre_insert
278 (p_rec in out nocopy pqh_cpd_shd.g_rec_type
279 ) is
280 --
281 Cursor C_Sel1 is select pqh_corps_definitions_s.nextval from sys.dual;
282 --
283 Cursor C_Sel2 is
284 Select null
285 from pqh_corps_definitions
286 where corps_definition_id =
287 pqh_cpd_ins.g_corps_definition_id_i;
288 --
289 l_proc varchar2(72);
290 l_exists varchar2(1);
291 --
292 Begin
293 if g_debug then
294 l_proc := g_package||'pre_insert';
295 hr_utility.set_location('Entering:'||l_proc, 5);
296 end if;
297 --
298 If (pqh_cpd_ins.g_corps_definition_id_i is not null) Then
299 --
300 -- Verify registered primary key values not already in use
301 --
302 Open C_Sel2;
303 Fetch C_Sel2 into l_exists;
304 If C_Sel2%found Then
305 Close C_Sel2;
306 --
307 -- The primary key values are already in use.
308 --
309 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
310 fnd_message.set_token('TABLE_NAME','pqh_corps_definitions');
311 fnd_message.raise_error;
312 End If;
313 Close C_Sel2;
314 --
315 -- Use registered key values and clear globals
316 --
317 p_rec.corps_definition_id :=
318 pqh_cpd_ins.g_corps_definition_id_i;
319 pqh_cpd_ins.g_corps_definition_id_i := null;
320 Else
321 --
322 -- No registerd key values, so select the next sequence number
323 --
324 --
325 -- Select the next sequence number
326 --
327 Open C_Sel1;
328 Fetch C_Sel1 Into p_rec.corps_definition_id;
329 Close C_Sel1;
330 End If;
331 --
332 if g_debug then
333 hr_utility.set_location(' Leaving:'||l_proc, 10);
334 end if;
335 End pre_insert;
336 --
337 -- ----------------------------------------------------------------------------
338 -- |-----------------------------< post_insert >------------------------------|
339 -- ----------------------------------------------------------------------------
340 -- {Start Of Comments}
341 --
342 -- Description:
343 -- This private procedure contains any processing which is required after
344 -- the insert dml.
345 --
346 -- Prerequisites:
347 -- This is an internal procedure which is called from the ins procedure.
348 --
349 -- In Parameters:
350 -- A Pl/Sql record structre.
351 --
352 -- Post Success:
353 -- Processing continues.
354 --
355 -- Post Failure:
356 -- If an error has occurred, an error message and exception will be raised
357 -- but not handled.
358 --
359 -- Developer Implementation Notes:
360 -- Any post-processing required after the insert dml is issued should be
361 -- coded within this procedure. It is important to note that any 3rd party
362 -- maintenance should be reviewed before placing in this procedure.
363 --
364 -- Access Status:
365 -- Internal Row Handler Use Only.
366 --
367 -- {End Of Comments}
368 -- ----------------------------------------------------------------------------
369 Procedure post_insert
370 (p_effective_date in date
371 ,p_rec in pqh_cpd_shd.g_rec_type
372 ) is
373 --
374 l_proc varchar2(72);
375 --
376 Begin
377 if g_debug then
378 l_proc := g_package||'post_insert';
379 hr_utility.set_location('Entering:'||l_proc, 5);
380 end if;
381 begin
382 --
383 pqh_cpd_rki.after_insert
384 (p_effective_date => p_effective_date
385 ,p_corps_definition_id
386 => p_rec.corps_definition_id
387 ,p_business_group_id
388 => p_rec.business_group_id
389 ,p_name
390 => p_rec.name
391 ,p_status_cd
392 => p_rec.status_cd
393 ,p_retirement_age
394 => p_rec.retirement_age
395 ,p_category_cd
396 => p_rec.category_cd
397 ,p_recruitment_end_date
398 => p_rec.recruitment_end_date
399 ,p_corps_type_cd
400 => p_rec.corps_type_cd
401 ,p_starting_grade_step_id
402 => p_rec.starting_grade_step_id
403 ,p_task_desc
404 => p_rec.task_desc
405 ,p_secondment_threshold
406 => p_rec.secondment_threshold
407 ,p_normal_hours
408 => p_rec.normal_hours
409 ,p_normal_hours_frequency
410 => p_rec.normal_hours_frequency
411 ,p_minimum_hours
412 => p_rec.minimum_hours
413 ,p_minimum_hours_frequency
414 => p_rec.minimum_hours_frequency
415 ,p_attribute1
416 => p_rec.attribute1
417 ,p_attribute2
418 => p_rec.attribute2
419 ,p_attribute3
420 => p_rec.attribute3
421 ,p_attribute4
422 => p_rec.attribute4
423 ,p_attribute5
424 => p_rec.attribute5
425 ,p_attribute6
426 => p_rec.attribute6
427 ,p_attribute7
428 => p_rec.attribute7
429 ,p_attribute8
430 => p_rec.attribute8
431 ,p_attribute9
432 => p_rec.attribute9
433 ,p_attribute10
434 => p_rec.attribute10
435 ,p_attribute11
436 => p_rec.attribute11
437 ,p_attribute12
438 => p_rec.attribute12
439 ,p_attribute13
440 => p_rec.attribute13
441 ,p_attribute14
442 => p_rec.attribute14
443 ,p_attribute15
444 => p_rec.attribute15
445 ,p_attribute16
446 => p_rec.attribute16
447 ,p_attribute17
448 => p_rec.attribute17
449 ,p_attribute18
450 => p_rec.attribute18
451 ,p_attribute19
452 => p_rec.attribute19
453 ,p_attribute20
454 => p_rec.attribute20
455 ,p_attribute21
456 => p_rec.attribute21
457 ,p_attribute22
458 => p_rec.attribute22
459 ,p_attribute23
460 => p_rec.attribute23
461 ,p_attribute24
462 => p_rec.attribute24
463 ,p_attribute25
464 => p_rec.attribute25
465 ,p_attribute26
466 => p_rec.attribute26
467 ,p_attribute27
468 => p_rec.attribute27
469 ,p_attribute28
470 => p_rec.attribute28
471 ,p_attribute29
472 => p_rec.attribute29
473 ,p_attribute30
474 => p_rec.attribute30
475 ,p_attribute_category
476 => p_rec.attribute_category
477 ,p_object_version_number
478 => p_rec.object_version_number
479 ,p_type_of_ps
480 => p_rec.type_of_ps
481 ,p_date_from
482 => p_rec.date_from
483 ,p_date_to
484 => p_rec.date_to
485 ,p_primary_prof_field_id
486 => p_rec.primary_prof_field_id
487 ,p_starting_grade_id
488 => p_rec.starting_grade_id
489 ,p_ben_pgm_id
490 => p_rec.ben_pgm_id
491 ,p_probation_period
492 => p_rec.probation_period
493 ,p_probation_units
494 => p_rec.probation_units
495 );
496 --
497 exception
498 --
499 when hr_api.cannot_find_prog_unit then
500 --
501 hr_api.cannot_find_prog_unit_error
502 (p_module_name => 'PQH_CORPS_DEFINITIONS'
503 ,p_hook_type => 'AI');
504 --
505 end;
506 --
507 if g_debug then
508 hr_utility.set_location(' Leaving:'||l_proc, 10);
509 end if;
510 End post_insert;
511 --
512 -- ----------------------------------------------------------------------------
513 -- |---------------------------------< ins >----------------------------------|
514 -- ----------------------------------------------------------------------------
515 Procedure ins
516 (p_effective_date in date
517 ,p_rec in out nocopy pqh_cpd_shd.g_rec_type
518 ) is
519 --
520 l_proc varchar2(72);
521 --
522 Begin
523 if g_debug then
524 l_proc := g_package||'ins';
525 hr_utility.set_location('Entering:'||l_proc, 5);
526 end if;
527 --
528 -- Call the supporting insert validate operations
529 --
530 pqh_cpd_bus.insert_validate
531 (p_effective_date
532 ,p_rec
533 );
534 --
535 -- Call to raise any errors on multi-message list
536 hr_multi_message.end_validation_set;
537 --
538 -- Call the supporting pre-insert operation
539 --
540 pqh_cpd_ins.pre_insert(p_rec);
541 --
542 -- Insert the row
543 --
544 pqh_cpd_ins.insert_dml(p_rec);
545 --
546 -- Call the supporting post-insert operation
547 --
548 pqh_cpd_ins.post_insert
549 (p_effective_date
550 ,p_rec
551 );
552 --
553 -- Call to raise any errors on multi-message list
554 hr_multi_message.end_validation_set;
555 --
556 if g_debug then
557 hr_utility.set_location('Leaving:'||l_proc, 20);
558 end if;
559 end ins;
560 --
561 -- ----------------------------------------------------------------------------
562 -- |---------------------------------< ins >----------------------------------|
563 -- ----------------------------------------------------------------------------
564 Procedure ins
565 (p_effective_date in date
566 ,p_business_group_id in number
567 ,p_name in varchar2
568 ,p_status_cd in varchar2
569 ,p_category_cd in varchar2
570 ,p_retirement_age in number default null
571 ,p_recruitment_end_date in date default null
572 ,p_corps_type_cd in varchar2 default null
573 ,p_starting_grade_step_id in number default null
574 ,p_task_desc in varchar2 default null
575 ,p_secondment_threshold in number default null
576 ,p_normal_hours in number default null
577 ,p_normal_hours_frequency in varchar2 default null
578 ,p_minimum_hours in number default null
579 ,p_minimum_hours_frequency in varchar2 default null
580 ,p_attribute1 in varchar2 default null
581 ,p_attribute2 in varchar2 default null
582 ,p_attribute3 in varchar2 default null
583 ,p_attribute4 in varchar2 default null
584 ,p_attribute5 in varchar2 default null
585 ,p_attribute6 in varchar2 default null
586 ,p_attribute7 in varchar2 default null
587 ,p_attribute8 in varchar2 default null
588 ,p_attribute9 in varchar2 default null
589 ,p_attribute10 in varchar2 default null
590 ,p_attribute11 in varchar2 default null
591 ,p_attribute12 in varchar2 default null
592 ,p_attribute13 in varchar2 default null
593 ,p_attribute14 in varchar2 default null
594 ,p_attribute15 in varchar2 default null
595 ,p_attribute16 in varchar2 default null
596 ,p_attribute17 in varchar2 default null
597 ,p_attribute18 in varchar2 default null
598 ,p_attribute19 in varchar2 default null
599 ,p_attribute20 in varchar2 default null
600 ,p_attribute21 in varchar2 default null
601 ,p_attribute22 in varchar2 default null
602 ,p_attribute23 in varchar2 default null
603 ,p_attribute24 in varchar2 default null
604 ,p_attribute25 in varchar2 default null
605 ,p_attribute26 in varchar2 default null
606 ,p_attribute27 in varchar2 default null
607 ,p_attribute28 in varchar2 default null
608 ,p_attribute29 in varchar2 default null
609 ,p_attribute30 in varchar2 default null
610 ,p_attribute_category in varchar2 default null
611 ,p_type_of_ps in varchar2 default null
612 ,p_date_from in date default null
613 ,p_date_to in date default null
614 ,p_primary_prof_field_id in number default null
615 ,p_starting_grade_id in number default null
616 ,p_ben_pgm_id in number default null
617 ,p_probation_period in number default null
618 ,p_probation_units in varchar2 default null
619 ,p_corps_definition_id out nocopy number
620 ,p_object_version_number out nocopy number
621 ) is
622 --
623 l_rec pqh_cpd_shd.g_rec_type;
624 l_proc varchar2(72);
625 --
626 Begin
627 g_debug := hr_utility.debug_enabled;
628 if g_debug then
629 l_proc := g_package||'ins';
630 hr_utility.set_location('Entering:'||l_proc, 5);
631 end if;
632 --
633 -- Call conversion function to turn arguments into the
634 -- p_rec structure.
635 --
636 l_rec :=
637 pqh_cpd_shd.convert_args
638 (null
639 ,p_business_group_id
640 ,p_name
641 ,p_status_cd
642 ,p_retirement_age
643 ,p_category_cd
644 ,p_recruitment_end_date
645 ,p_corps_type_cd
646 ,p_starting_grade_step_id
647 ,p_task_desc
648 ,p_secondment_threshold
649 ,p_normal_hours
650 ,p_normal_hours_frequency
651 ,p_minimum_hours
652 ,p_minimum_hours_frequency
653 ,p_attribute1
654 ,p_attribute2
655 ,p_attribute3
656 ,p_attribute4
657 ,p_attribute5
658 ,p_attribute6
659 ,p_attribute7
660 ,p_attribute8
661 ,p_attribute9
662 ,p_attribute10
663 ,p_attribute11
664 ,p_attribute12
665 ,p_attribute13
666 ,p_attribute14
667 ,p_attribute15
668 ,p_attribute16
669 ,p_attribute17
670 ,p_attribute18
671 ,p_attribute19
672 ,p_attribute20
673 ,p_attribute21
674 ,p_attribute22
675 ,p_attribute23
676 ,p_attribute24
677 ,p_attribute25
678 ,p_attribute26
679 ,p_attribute27
680 ,p_attribute28
681 ,p_attribute29
682 ,p_attribute30
683 ,p_attribute_category
684 ,null
685 ,p_type_of_ps
686 ,p_date_from
687 ,p_date_to
688 ,p_primary_prof_field_id
689 ,p_starting_grade_id
690 ,p_ben_pgm_id
691 ,p_probation_period
692 ,p_probation_units
693 );
694 --
695 -- Having converted the arguments into the pqh_cpd_rec
696 -- plsql record structure we call the corresponding record business process.
697 --
698 pqh_cpd_ins.ins
699 (p_effective_date
700 ,l_rec
701 );
702 --
703 -- As the primary key argument(s)
704 -- are specified as an OUT's we must set these values.
705 --
706 p_corps_definition_id := l_rec.corps_definition_id;
707 p_object_version_number := l_rec.object_version_number;
708 --
709 if g_debug then
710 hr_utility.set_location(' Leaving:'||l_proc, 10);
711 end if;
712 End ins;
713 --
714 end pqh_cpd_ins;