[Home] [Help]
PACKAGE BODY: APPS.PQH_PSU_INS
Source
1 Package Body pqh_psu_ins as
2 /* $Header: pqpsurhi.pkb 120.0 2005/05/29 02:19 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_psu_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_emp_stat_situation_id_i number default null;
14 --
15 -- ----------------------------------------------------------------------------
16 -- |------------------------< set_base_key_value >----------------------------|
17 -- ----------------------------------------------------------------------------
18 procedure set_base_key_value
19 (p_emp_stat_situation_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_psu_ins.g_emp_stat_situation_id_i := p_emp_stat_situation_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_psu_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_fr_emp_stat_situations
88 --
89 insert into pqh_fr_emp_stat_situations
90 (emp_stat_situation_id
91 ,statutory_situation_id
92 ,person_id
93 ,provisional_start_date
94 ,provisional_end_date
95 ,actual_start_date
96 ,actual_end_date
97 ,approval_flag
98 ,comments
99 ,contact_person_id
100 ,contact_relationship
101 ,external_organization_id
102 ,renewal_flag
103 ,renew_stat_situation_id
104 ,seconded_career_id
105 ,attribute_category
106 ,attribute1
107 ,attribute2
108 ,attribute3
109 ,attribute4
110 ,attribute5
111 ,attribute6
112 ,attribute7
113 ,attribute8
114 ,attribute9
115 ,attribute10
116 ,attribute11
117 ,attribute12
118 ,attribute13
119 ,attribute14
120 ,attribute15
121 ,attribute16
122 ,attribute17
123 ,attribute18
124 ,attribute19
125 ,attribute20
126 ,attribute21
127 ,attribute22
128 ,attribute23
129 ,attribute24
130 ,attribute25
131 ,attribute26
132 ,attribute27
133 ,attribute28
134 ,attribute29
135 ,attribute30
136 ,object_version_number
137 )
138 Values
139 (p_rec.emp_stat_situation_id
140 ,p_rec.statutory_situation_id
141 ,p_rec.person_id
142 ,p_rec.provisional_start_date
143 ,p_rec.provisional_end_date
144 ,p_rec.actual_start_date
145 ,p_rec.actual_end_date
146 ,p_rec.approval_flag
147 ,p_rec.comments
148 ,p_rec.contact_person_id
149 ,p_rec.contact_relationship
150 ,p_rec.external_organization_id
151 ,p_rec.renewal_flag
152 ,p_rec.renew_stat_situation_id
153 ,p_rec.seconded_career_id
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.attribute21
176 ,p_rec.attribute22
177 ,p_rec.attribute23
178 ,p_rec.attribute24
179 ,p_rec.attribute25
180 ,p_rec.attribute26
181 ,p_rec.attribute27
182 ,p_rec.attribute28
183 ,p_rec.attribute29
184 ,p_rec.attribute30
185 ,p_rec.object_version_number
186 );
187 --
188 --
189 --
190 hr_utility.set_location(' Leaving:'||l_proc, 10);
191 Exception
192 When hr_api.check_integrity_violated Then
193 -- A check constraint has been violated
194 --
195 pqh_psu_shd.constraint_error
196 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
197 When hr_api.parent_integrity_violated Then
198 -- Parent integrity has been violated
199 --
200 pqh_psu_shd.constraint_error
201 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
202 When hr_api.unique_integrity_violated Then
203 -- Unique integrity has been violated
204 --
205 pqh_psu_shd.constraint_error
206 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
207 When Others Then
208 --
209 Raise;
210 End insert_dml;
211 --
212 -- ----------------------------------------------------------------------------
213 -- |------------------------------< pre_insert >------------------------------|
214 -- ----------------------------------------------------------------------------
215 -- {Start Of Comments}
216 --
217 -- Description:
218 -- This private procedure contains any processing which is required before
219 -- the insert dml. Presently, if the entity has a corresponding primary
220 -- key which is maintained by an associating sequence, the primary key for
221 -- the entity will be populated with the next sequence value in
222 -- preparation for the insert dml.
223 --
224 -- Prerequisites:
225 -- This is an internal procedure which is called from the ins procedure.
226 --
227 -- In Parameters:
228 -- A Pl/Sql record structure.
229 --
230 -- Post Success:
231 -- Processing continues.
232 --
233 -- Post Failure:
234 -- If an error has occurred, an error message and exception will be raised
235 -- but not handled.
236 --
237 -- Developer Implementation Notes:
238 -- Any pre-processing required before the insert dml is issued should be
239 -- coded within this procedure. As stated above, a good example is the
240 -- generation of a primary key number via a corresponding sequence.
241 -- It is important to note that any 3rd party maintenance should be reviewed
242 -- before placing in this procedure.
243 --
244 -- Access Status:
245 -- Internal Row Handler Use Only.
246 --
247 -- {End Of Comments}
248 -- ----------------------------------------------------------------------------
249 Procedure pre_insert
250 (p_rec in out nocopy pqh_psu_shd.g_rec_type
251 ) is
252 --
253 Cursor C_Sel1 is select pqh_fr_emp_stat_situations_s.nextval from sys.dual;
254 --
255 Cursor C_Sel2 is
256 Select null
257 from pqh_fr_emp_stat_situations
258 where emp_stat_situation_id =
259 pqh_psu_ins.g_emp_stat_situation_id_i;
260 --
261 l_proc varchar2(72) := g_package||'pre_insert';
262 l_exists varchar2(1);
263 --
264 Begin
265 hr_utility.set_location('Entering:'||l_proc, 5);
266 --
267 If (pqh_psu_ins.g_emp_stat_situation_id_i is not null) Then
268 --
269 -- Verify registered primary key values not already in use
270 --
271 Open C_Sel2;
272 Fetch C_Sel2 into l_exists;
273 If C_Sel2%found Then
274 Close C_Sel2;
275 --
276 -- The primary key values are already in use.
277 --
278 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
279 fnd_message.set_token('TABLE_NAME','pqh_fr_emp_stat_situations');
280 fnd_message.raise_error;
281 End If;
282 Close C_Sel2;
283 --
284 -- Use registered key values and clear globals
285 --
286 p_rec.emp_stat_situation_id :=
287 pqh_psu_ins.g_emp_stat_situation_id_i;
288 pqh_psu_ins.g_emp_stat_situation_id_i := null;
289 Else
290 --
291 -- No registerd key values, so select the next sequence number
292 --
293 --
294 -- Select the next sequence number
295 --
296 Open C_Sel1;
297 Fetch C_Sel1 Into p_rec.emp_stat_situation_id;
298 Close C_Sel1;
299 End If;
300 --
301 hr_utility.set_location(' Leaving:'||l_proc, 10);
302 End pre_insert;
303 --
304 -- ----------------------------------------------------------------------------
305 -- |-----------------------------< post_insert >------------------------------|
306 -- ----------------------------------------------------------------------------
307 -- {Start Of Comments}
308 --
309 -- Description:
310 -- This private procedure contains any processing which is required after
311 -- the insert dml.
312 --
313 -- Prerequisites:
314 -- This is an internal procedure which is called from the ins procedure.
315 --
316 -- In Parameters:
317 -- A Pl/Sql record structre.
318 --
319 -- Post Success:
320 -- Processing continues.
321 --
322 -- Post Failure:
323 -- If an error has occurred, an error message and exception will be raised
324 -- but not handled.
325 --
326 -- Developer Implementation Notes:
327 -- Any post-processing required after the insert dml is issued should be
328 -- coded within this procedure. It is important to note that any 3rd party
329 -- maintenance should be reviewed before placing in this procedure.
330 --
331 -- Access Status:
332 -- Internal Row Handler Use Only.
333 --
334 -- {End Of Comments}
335 -- ----------------------------------------------------------------------------
336 Procedure post_insert
337 (p_effective_date in date
338 ,p_rec in pqh_psu_shd.g_rec_type
339 ) is
340 --
341 l_proc varchar2(72) := g_package||'post_insert';
342 --
343 Begin
344 hr_utility.set_location('Entering:'||l_proc, 5);
345 begin
346 --
347 pqh_psu_rki.after_insert
348 (p_effective_date => p_effective_date
349 ,p_emp_stat_situation_id
350 => p_rec.emp_stat_situation_id
351 ,p_statutory_situation_id
352 => p_rec.statutory_situation_id
353 ,p_person_id
354 => p_rec.person_id
355 ,p_provisional_start_date
356 => p_rec.provisional_start_date
357 ,p_provisional_end_date
358 => p_rec.provisional_end_date
359 ,p_actual_start_date
360 => p_rec.actual_start_date
361 ,p_actual_end_date
362 => p_rec.actual_end_date
363 ,p_approval_flag
364 => p_rec.approval_flag
365 ,p_comments
366 => p_rec.comments
367 ,p_contact_person_id
368 => p_rec.contact_person_id
369 ,p_contact_relationship
370 => p_rec.contact_relationship
371 ,p_external_organization_id
372 => p_rec.external_organization_id
373 ,p_renewal_flag
374 => p_rec.renewal_flag
375 ,p_renew_stat_situation_id
376 => p_rec.renew_stat_situation_id
377 ,p_seconded_career_id
378 => p_rec.seconded_career_id
379 ,p_attribute_category
380 => p_rec.attribute_category
381 ,p_attribute1
382 => p_rec.attribute1
383 ,p_attribute2
384 => p_rec.attribute2
385 ,p_attribute3
386 => p_rec.attribute3
387 ,p_attribute4
388 => p_rec.attribute4
389 ,p_attribute5
390 => p_rec.attribute5
391 ,p_attribute6
392 => p_rec.attribute6
393 ,p_attribute7
394 => p_rec.attribute7
395 ,p_attribute8
396 => p_rec.attribute8
397 ,p_attribute9
398 => p_rec.attribute9
399 ,p_attribute10
400 => p_rec.attribute10
401 ,p_attribute11
402 => p_rec.attribute11
403 ,p_attribute12
404 => p_rec.attribute12
405 ,p_attribute13
406 => p_rec.attribute13
407 ,p_attribute14
408 => p_rec.attribute14
409 ,p_attribute15
410 => p_rec.attribute15
411 ,p_attribute16
412 => p_rec.attribute16
413 ,p_attribute17
414 => p_rec.attribute17
415 ,p_attribute18
416 => p_rec.attribute18
417 ,p_attribute19
418 => p_rec.attribute19
419 ,p_attribute20
420 => p_rec.attribute20
421 ,p_attribute21
422 => p_rec.attribute21
423 ,p_attribute22
424 => p_rec.attribute22
425 ,p_attribute23
426 => p_rec.attribute23
427 ,p_attribute24
428 => p_rec.attribute24
429 ,p_attribute25
430 => p_rec.attribute25
431 ,p_attribute26
432 => p_rec.attribute26
433 ,p_attribute27
434 => p_rec.attribute27
435 ,p_attribute28
436 => p_rec.attribute28
437 ,p_attribute29
438 => p_rec.attribute29
439 ,p_attribute30
440 => p_rec.attribute30
441 ,p_object_version_number
442 => p_rec.object_version_number
443 );
444 --
445 exception
446 --
447 when hr_api.cannot_find_prog_unit then
448 --
449 hr_api.cannot_find_prog_unit_error
450 (p_module_name => 'PQH_FR_EMP_STAT_SITUATIONS'
451 ,p_hook_type => 'AI');
452 --
453 end;
454 --
455 hr_utility.set_location(' Leaving:'||l_proc, 10);
456 End post_insert;
457 --
458 -- ----------------------------------------------------------------------------
459 -- |---------------------------------< ins >----------------------------------|
460 -- ----------------------------------------------------------------------------
461 Procedure ins
462 (p_effective_date in date
463 ,p_rec in out nocopy pqh_psu_shd.g_rec_type
464 ) is
465 --
466 l_proc varchar2(72) := g_package||'ins';
467 --
468 Begin
469 hr_utility.set_location('Entering:'||l_proc, 5);
470 --
471 -- Call the supporting insert validate operations
472 --
473 pqh_psu_bus.insert_validate
474 (p_effective_date
475 ,p_rec
476 );
477 --
478 -- Call to raise any errors on multi-message list
479 hr_multi_message.end_validation_set;
480 --
481 -- Call the supporting pre-insert operation
482 --
483 pqh_psu_ins.pre_insert(p_rec);
484 --
485 -- Insert the row
486 --
487 pqh_psu_ins.insert_dml(p_rec);
488 --
489 -- Call the supporting post-insert operation
490 --
491 pqh_psu_ins.post_insert
492 (p_effective_date
493 ,p_rec
494 );
495 --
496 -- Call to raise any errors on multi-message list
497 hr_multi_message.end_validation_set;
498 --
499 hr_utility.set_location('Leaving:'||l_proc, 20);
500 end ins;
501 --
502 -- ----------------------------------------------------------------------------
503 -- |---------------------------------< ins >----------------------------------|
504 -- ----------------------------------------------------------------------------
505 Procedure ins
506 (p_effective_date in date
507 ,p_statutory_situation_id in number
508 ,p_person_id in number
509 ,p_provisional_start_date in date default null
510 ,p_provisional_end_date in date default null
511 ,p_actual_start_date in date default null
512 ,p_actual_end_date in date default null
513 ,p_approval_flag in varchar2 default null
514 ,p_comments in varchar2 default null
515 ,p_contact_person_id in number default null
516 ,p_contact_relationship in varchar2 default null
517 ,p_external_organization_id in number default null
518 ,p_renewal_flag in varchar2 default null
519 ,p_renew_stat_situation_id in number default null
520 ,p_seconded_career_id in number default null
521 ,p_attribute_category in varchar2 default null
522 ,p_attribute1 in varchar2 default null
523 ,p_attribute2 in varchar2 default null
524 ,p_attribute3 in varchar2 default null
525 ,p_attribute4 in varchar2 default null
526 ,p_attribute5 in varchar2 default null
527 ,p_attribute6 in varchar2 default null
528 ,p_attribute7 in varchar2 default null
529 ,p_attribute8 in varchar2 default null
530 ,p_attribute9 in varchar2 default null
531 ,p_attribute10 in varchar2 default null
532 ,p_attribute11 in varchar2 default null
533 ,p_attribute12 in varchar2 default null
534 ,p_attribute13 in varchar2 default null
535 ,p_attribute14 in varchar2 default null
536 ,p_attribute15 in varchar2 default null
537 ,p_attribute16 in varchar2 default null
538 ,p_attribute17 in varchar2 default null
539 ,p_attribute18 in varchar2 default null
540 ,p_attribute19 in varchar2 default null
541 ,p_attribute20 in varchar2 default null
542 ,p_attribute21 in varchar2 default null
543 ,p_attribute22 in varchar2 default null
544 ,p_attribute23 in varchar2 default null
545 ,p_attribute24 in varchar2 default null
546 ,p_attribute25 in varchar2 default null
547 ,p_attribute26 in varchar2 default null
548 ,p_attribute27 in varchar2 default null
549 ,p_attribute28 in varchar2 default null
550 ,p_attribute29 in varchar2 default null
551 ,p_attribute30 in varchar2 default null
552 ,p_emp_stat_situation_id out nocopy number
553 ,p_object_version_number out nocopy number
554 ) is
555 --
556 l_rec pqh_psu_shd.g_rec_type;
557 l_proc varchar2(72) := g_package||'ins';
558 --
559 Begin
560 hr_utility.set_location('Entering:'||l_proc, 5);
561 --
562 -- Call conversion function to turn arguments into the
563 -- p_rec structure.
564 --
565 l_rec :=
566 pqh_psu_shd.convert_args
567 (null
568 ,p_statutory_situation_id
569 ,p_person_id
570 ,p_provisional_start_date
571 ,p_provisional_end_date
572 ,p_actual_start_date
573 ,p_actual_end_date
574 ,p_approval_flag
575 ,p_comments
576 ,p_contact_person_id
577 ,p_contact_relationship
578 ,p_external_organization_id
579 ,p_renewal_flag
580 ,p_renew_stat_situation_id
581 ,p_seconded_career_id
582 ,p_attribute_category
583 ,p_attribute1
584 ,p_attribute2
585 ,p_attribute3
586 ,p_attribute4
587 ,p_attribute5
588 ,p_attribute6
589 ,p_attribute7
590 ,p_attribute8
591 ,p_attribute9
592 ,p_attribute10
593 ,p_attribute11
594 ,p_attribute12
595 ,p_attribute13
596 ,p_attribute14
597 ,p_attribute15
598 ,p_attribute16
599 ,p_attribute17
600 ,p_attribute18
601 ,p_attribute19
602 ,p_attribute20
603 ,p_attribute21
604 ,p_attribute22
605 ,p_attribute23
606 ,p_attribute24
607 ,p_attribute25
608 ,p_attribute26
609 ,p_attribute27
610 ,p_attribute28
611 ,p_attribute29
612 ,p_attribute30
613 ,null
614 );
615 --
616 -- Having converted the arguments into the pqh_psu_rec
617 -- plsql record structure we call the corresponding record business process.
618 --
619 pqh_psu_ins.ins
620 (p_effective_date
621 ,l_rec
622 );
623 --
624 -- As the primary key argument(s)
625 -- are specified as an OUT's we must set these values.
626 --
627 p_emp_stat_situation_id := l_rec.emp_stat_situation_id;
628 p_object_version_number := l_rec.object_version_number;
629 --
630 hr_utility.set_location(' Leaving:'||l_proc, 10);
631 End ins;
632 --
633 end pqh_psu_ins;