[Home] [Help]
PACKAGE BODY: APPS.PER_SIP_INS
Source
1 Package Body per_sip_ins as
2 /* $Header: pesiprhi.pkb 120.6.12020000.2 2012/07/05 05:02:18 amnaraya ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_sip_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_successor_in_plan_id_i number default null;
14 --
15 -- ----------------------------------------------------------------------------
16 -- |------------------------< set_base_key_value >----------------------------|
17 -- ----------------------------------------------------------------------------
18 procedure set_base_key_value
19 (p_successor_in_plan_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_sip_ins.g_successor_in_plan_id_i := p_successor_in_plan_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_sip_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 per_sip_shd.g_api_dml := true; -- Set the api dml status
86 --
87 -- Insert the row into: per_sp_successor_in_plan
88 --
89 insert into per_sp_successor_in_plan
90 (successor_in_plan_id
91 ,plan_id
92 ,successor_id
93 ,business_group_id
94 ,status
95 ,potential
96 ,performance_rating
97 ,risk_of_loss
98 ,impact_of_loss
99 ,readiness_pct
100 ,rank
101 ,eligible_for_promotion
102 ,earliest_succession_date
103 ,latest_succession_date
104 ,comments
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 ,planned_date
127 ,assignment_id
128 ,supervisor_id
129 ,grade_id
130 ,grade_ladder_id
131 ,grade_step_id
132 ,grade_point_id
133 ,position_id
134 ,job_id
135 ,assignment_status_type_id
136 ,organization_id
137 ,people_group_id
138 ,pay_basis_id
139 ,employee_category
140 ,bargaining_unit_code
141 ,labour_union_member_flag
142 ,collective_agreement_id
143 ,assignment_category
144 ,location_id
145 ,contract_id
146 ,object_version_number
147 )
148 Values
149 (p_rec.successor_in_plan_id
150 ,p_rec.plan_id
151 ,p_rec.successor_id
152 ,p_rec.business_group_id
153 ,p_rec.status
154 ,p_rec.potential
155 ,p_rec.performance_rating
156 ,p_rec.risk_of_loss
157 ,p_rec.impact_of_loss
158 ,p_rec.readiness_pct
159 ,p_rec.rank
160 ,p_rec.eligible_for_promotion
161 ,p_rec.earliest_succession_date
162 ,p_rec.latest_succession_date
163 ,p_rec.comments
164 ,p_rec.attribute_category
165 ,p_rec.attribute1
166 ,p_rec.attribute2
167 ,p_rec.attribute3
168 ,p_rec.attribute4
169 ,p_rec.attribute5
170 ,p_rec.attribute6
171 ,p_rec.attribute7
172 ,p_rec.attribute8
173 ,p_rec.attribute9
174 ,p_rec.attribute10
175 ,p_rec.attribute11
176 ,p_rec.attribute12
177 ,p_rec.attribute13
178 ,p_rec.attribute14
179 ,p_rec.attribute15
180 ,p_rec.attribute16
181 ,p_rec.attribute17
182 ,p_rec.attribute18
183 ,p_rec.attribute19
184 ,p_rec.attribute20
185 ,p_rec.planned_date
186 ,p_rec.assignment_id
187 ,p_rec.supervisor_id
188 ,p_rec.grade_id
189 ,p_rec.grade_ladder_id
190 ,p_rec.grade_step_id
191 ,p_rec.grade_point_id
192 ,p_rec.position_id
193 ,p_rec.job_id
194 ,p_rec.assignment_status_type_id
195 ,p_rec.organization_id
196 ,p_rec.people_group_id
197 ,p_rec.pay_basis_id
198 ,p_rec.employee_category
199 ,p_rec.bargaining_unit_code
200 ,p_rec.labour_union_member_flag
201 ,p_rec.collective_agreement_id
202 ,p_rec.assignment_category
203 ,p_rec.location_id
204 ,p_rec.contract_id
205 ,p_rec.object_version_number
206 );
207 --
208 per_sip_shd.g_api_dml := false; -- Unset the api dml status
209 --
210 hr_utility.set_location(' Leaving:'||l_proc, 10);
211 Exception
212 When hr_api.check_integrity_violated Then
213 -- A check constraint has been violated
214 per_sip_shd.g_api_dml := false; -- Unset the api dml status
215 per_sip_shd.constraint_error
216 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
217 When hr_api.parent_integrity_violated Then
218 -- Parent integrity has been violated
219 per_sip_shd.g_api_dml := false; -- Unset the api dml status
220 per_sip_shd.constraint_error
221 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
222 When hr_api.unique_integrity_violated Then
223 -- Unique integrity has been violated
224 per_sip_shd.g_api_dml := false; -- Unset the api dml status
225 per_sip_shd.constraint_error
226 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
227 When Others Then
228 per_sip_shd.g_api_dml := false; -- Unset the api dml status
229 Raise;
230 End insert_dml;
231 --
232 -- ----------------------------------------------------------------------------
233 -- |------------------------------< pre_insert >------------------------------|
234 -- ----------------------------------------------------------------------------
235 -- {Start Of Comments}
236 --
237 -- Description:
238 -- This private procedure contains any processing which is required before
239 -- the insert dml. Presently, if the entity has a corresponding primary
240 -- key which is maintained by an associating sequence, the primary key for
241 -- the entity will be populated with the next sequence value in
242 -- preparation for the insert dml.
243 --
244 -- Prerequisites:
245 -- This is an internal procedure which is called from the ins procedure.
246 --
247 -- In Parameters:
248 -- A Pl/Sql record structure.
249 --
250 -- Post Success:
251 -- Processing continues.
252 --
253 -- Post Failure:
254 -- If an error has occurred, an error message and exception will be raised
255 -- but not handled.
256 --
257 -- Developer Implementation Notes:
258 -- Any pre-processing required before the insert dml is issued should be
259 -- coded within this procedure. As stated above, a good example is the
260 -- generation of a primary key number via a corresponding sequence.
261 -- It is important to note that any 3rd party maintenance should be reviewed
262 -- before placing in this procedure.
263 --
264 -- Access Status:
265 -- Internal Row Handler Use Only.
266 --
267 -- {End Of Comments}
268 -- ----------------------------------------------------------------------------
269 Procedure pre_insert
270 (p_rec in out nocopy per_sip_shd.g_rec_type
271 ) is
272 --
273 Cursor C_Sel1 is select per_sp_successor_in_plan_s.nextval from sys.dual;
274 --
275 Cursor C_Sel2 is
276 Select null
277 from per_sp_successor_in_plan
278 where successor_in_plan_id =
279 per_sip_ins.g_successor_in_plan_id_i;
280 --
281 l_proc varchar2(72) := g_package||'pre_insert';
282 l_exists varchar2(1);
283 --
284 Begin
285 hr_utility.set_location('Entering:'||l_proc, 5);
286 --
287 If (per_sip_ins.g_successor_in_plan_id_i is not null) Then
288 --
289 -- Verify registered primary key values not already in use
290 --
291 Open C_Sel2;
292 Fetch C_Sel2 into l_exists;
293 If C_Sel2%found Then
294 Close C_Sel2;
295 --
296 -- The primary key values are already in use.
297 --
298 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
299 fnd_message.set_token('TABLE_NAME','per_sp_successor_in_plan');
300 fnd_message.raise_error;
301 End If;
302 Close C_Sel2;
303 --
304 -- Use registered key values and clear globals
305 --
306 p_rec.successor_in_plan_id :=
307 per_sip_ins.g_successor_in_plan_id_i;
308 per_sip_ins.g_successor_in_plan_id_i := null;
309 Else
310 --
311 -- No registerd key values, so select the next sequence number
312 --
313 --
314 -- Select the next sequence number
315 --
316 Open C_Sel1;
317 Fetch C_Sel1 Into p_rec.successor_in_plan_id;
318 Close C_Sel1;
319 End If;
320 --
321 hr_utility.set_location(' Leaving:'||l_proc, 10);
322 End pre_insert;
323 --
324 -- ----------------------------------------------------------------------------
325 -- |-----------------------------< post_insert >------------------------------|
326 -- ----------------------------------------------------------------------------
327 -- {Start Of Comments}
328 --
329 -- Description:
330 -- This private procedure contains any processing which is required after
331 -- the insert dml.
332 --
333 -- Prerequisites:
334 -- This is an internal procedure which is called from the ins procedure.
335 --
336 -- In Parameters:
337 -- A Pl/Sql record structre.
338 --
339 -- Post Success:
340 -- Processing continues.
341 --
342 -- Post Failure:
343 -- If an error has occurred, an error message and exception will be raised
344 -- but not handled.
345 --
346 -- Developer Implementation Notes:
347 -- Any post-processing required after the insert dml is issued should be
348 -- coded within this procedure. It is important to note that any 3rd party
349 -- maintenance should be reviewed before placing in this procedure.
350 --
351 -- Access Status:
352 -- Internal Row Handler Use Only.
353 --
354 -- {End Of Comments}
355 -- ----------------------------------------------------------------------------
356 Procedure post_insert
357 (p_effective_date in date
358 ,p_rec in per_sip_shd.g_rec_type
359 ) is
360 --
361 l_proc varchar2(72) := g_package||'post_insert';
362 --
363 Begin
364 hr_utility.set_location('Entering:'||l_proc, 5);
365 begin
366 --
367 per_sip_rki.after_insert
368 (p_effective_date => p_effective_date
369 ,p_successor_in_plan_id
370 => p_rec.successor_in_plan_id
371 ,p_plan_id
372 => p_rec.plan_id
373 ,p_successor_id
374 => p_rec.successor_id
375 ,p_business_group_id
376 => p_rec.business_group_id
377 ,p_status
378 => p_rec.status
379 ,p_potential
380 => p_rec.potential
381 ,p_performance_rating
382 => p_rec.performance_rating
383 ,p_risk_of_loss
384 => p_rec.risk_of_loss
385 ,p_impact_of_loss
386 => p_rec.impact_of_loss
387 ,p_readiness_pct
388 => p_rec.readiness_pct
389 ,p_rank
390 => p_rec.rank
391 ,p_eligible_for_promotion
392 => p_rec.eligible_for_promotion
393 ,p_earliest_succession_date
394 => p_rec.earliest_succession_date
395 ,p_latest_succession_date
396 => p_rec.latest_succession_date
397 ,p_comments
398 => p_rec.comments
399 ,p_attribute_category
400 => p_rec.attribute_category
401 ,p_attribute1
402 => p_rec.attribute1
403 ,p_attribute2
404 => p_rec.attribute2
405 ,p_attribute3
406 => p_rec.attribute3
407 ,p_attribute4
408 => p_rec.attribute4
409 ,p_attribute5
410 => p_rec.attribute5
411 ,p_attribute6
412 => p_rec.attribute6
413 ,p_attribute7
414 => p_rec.attribute7
415 ,p_attribute8
416 => p_rec.attribute8
417 ,p_attribute9
418 => p_rec.attribute9
419 ,p_attribute10
420 => p_rec.attribute10
421 ,p_attribute11
422 => p_rec.attribute11
423 ,p_attribute12
424 => p_rec.attribute12
425 ,p_attribute13
426 => p_rec.attribute13
427 ,p_attribute14
428 => p_rec.attribute14
429 ,p_attribute15
430 => p_rec.attribute15
431 ,p_attribute16
432 => p_rec.attribute16
433 ,p_attribute17
434 => p_rec.attribute17
435 ,p_attribute18
436 => p_rec.attribute18
437 ,p_attribute19
438 => p_rec.attribute19
439 ,p_attribute20
440 => p_rec.attribute20
441 ,p_planned_date
442 => p_rec.planned_date
443 ,p_assignment_id
444 => p_rec.assignment_id
445 ,p_supervisor_id
446 => p_rec.supervisor_id
447 ,p_grade_id
448 => p_rec.grade_id
449 ,p_grade_ladder_id
450 => p_rec.grade_ladder_id
451 ,p_grade_step_id
452 => p_rec.grade_step_id
453 ,p_grade_point_id
454 => p_rec.grade_point_id
455 ,p_position_id
456 => p_rec.position_id
457 ,p_job_id
458 => p_rec.job_id
459 ,p_assignment_status_type_id
460 => p_rec.assignment_status_type_id
461 ,p_organization_id
462 => p_rec.organization_id
463 ,p_people_group_id
464 => p_rec.people_group_id
465 ,p_pay_basis_id
466 => p_rec.pay_basis_id
467 ,p_employee_category
468 => p_rec.employee_category
469 ,p_bargaining_unit_code
470 => p_rec.bargaining_unit_code
471 ,p_labour_union_member_flag
472 => p_rec.labour_union_member_flag
473 ,p_collective_agreement_id
474 => p_rec.collective_agreement_id
475 ,p_assignment_category
476 => p_rec.assignment_category
477 ,p_location_id
478 => p_rec.location_id
479 ,p_contract_id
480 => p_rec.contract_id
481 ,p_object_version_number
482 => p_rec.object_version_number
483 );
484 --
485 exception
486 --
487 when hr_api.cannot_find_prog_unit then
488 --
489 hr_api.cannot_find_prog_unit_error
490 (p_module_name => 'PER_SP_SUCCESSOR_IN_PLAN'
491 ,p_hook_type => 'AI');
492 --
493 end;
494 --
495 hr_utility.set_location(' Leaving:'||l_proc, 10);
496 End post_insert;
497 --
498 -- ----------------------------------------------------------------------------
499 -- |---------------------------------< ins >----------------------------------|
500 -- ----------------------------------------------------------------------------
501 Procedure ins
502 (p_effective_date in date
503 ,p_rec in out nocopy per_sip_shd.g_rec_type
504 ) is
505 --
506 l_proc varchar2(72) := g_package||'ins';
507 --
508 Begin
509 hr_utility.set_location('Entering:'||l_proc, 5);
510 --
511 -- Call the supporting insert validate operations
512 --
513 per_sip_bus.insert_validate
514 (p_effective_date
515 ,p_rec
516 );
517 --
518 -- Call to raise any errors on multi-message list
519 hr_multi_message.end_validation_set;
520 --
521 -- Call the supporting pre-insert operation
522 --
523 per_sip_ins.pre_insert(p_rec);
524 --
525 -- Insert the row
526 --
527 per_sip_ins.insert_dml(p_rec);
528 --
529 -- Call the supporting post-insert operation
530 --
531 per_sip_ins.post_insert
532 (p_effective_date
533 ,p_rec
534 );
535 --
536 -- Call to raise any errors on multi-message list
537 hr_multi_message.end_validation_set;
538 --
539 hr_utility.set_location('Leaving:'||l_proc, 20);
540 end ins;
541 --
542 -- ----------------------------------------------------------------------------
543 -- |---------------------------------< ins >----------------------------------|
544 -- ----------------------------------------------------------------------------
545 Procedure ins
546 (p_effective_date in date
547 ,p_plan_id in number
548 ,p_successor_id in number
549 ,p_business_group_id in number
550 ,p_status in varchar2
551 ,p_rank in number
552 ,p_potential in varchar2 default null
553 ,p_performance_rating in varchar2 default null
554 ,p_risk_of_loss in varchar2 default null
555 ,p_impact_of_loss in varchar2 default null
556 ,p_readiness_pct in number default null
557 ,p_eligible_for_promotion in varchar2 default null
558 ,p_earliest_succession_date in date default null
559 ,p_latest_succession_date in date default null
560 ,p_comments in varchar2 default null
561 ,p_attribute_category in varchar2 default null
562 ,p_attribute1 in varchar2 default null
563 ,p_attribute2 in varchar2 default null
564 ,p_attribute3 in varchar2 default null
565 ,p_attribute4 in varchar2 default null
566 ,p_attribute5 in varchar2 default null
567 ,p_attribute6 in varchar2 default null
568 ,p_attribute7 in varchar2 default null
569 ,p_attribute8 in varchar2 default null
570 ,p_attribute9 in varchar2 default null
571 ,p_attribute10 in varchar2 default null
572 ,p_attribute11 in varchar2 default null
573 ,p_attribute12 in varchar2 default null
574 ,p_attribute13 in varchar2 default null
575 ,p_attribute14 in varchar2 default null
576 ,p_attribute15 in varchar2 default null
577 ,p_attribute16 in varchar2 default null
578 ,p_attribute17 in varchar2 default null
579 ,p_attribute18 in varchar2 default null
580 ,p_attribute19 in varchar2 default null
581 ,p_attribute20 in varchar2 default null
582 ,p_planned_date in date default null
583 ,p_assignment_id in number default null
584 ,p_supervisor_id in number default null
585 ,p_grade_id in number default null
586 ,p_grade_ladder_id in number default null
587 ,p_grade_step_id in number default null
588 ,p_grade_point_id in number default null
589 ,p_position_id in number default null
590 ,p_job_id in number default null
591 ,p_assignment_status_type_id in number default null
592 ,p_organization_id in number default null
593 ,p_people_group_id in number default null
594 ,p_pay_basis_id in number default null
595 ,p_employee_category in varchar2 default null
596 ,p_bargaining_unit_code in varchar2 default null
597 ,p_labour_union_member_flag in varchar2 default null
598 ,p_collective_agreement_id in number default null
599 ,p_assignment_category in varchar2 default null
600 ,p_location_id in number default null
601 ,p_contract_id in number default null
602 ,p_successor_in_plan_id out nocopy number
603 ,p_object_version_number out nocopy number
604 ) is
605 --
606 l_rec per_sip_shd.g_rec_type;
607 l_proc varchar2(72) := g_package||'ins';
608 --
609 Begin
610 hr_utility.set_location('Entering:'||l_proc, 5);
611 --
612 -- Call conversion function to turn arguments into the
613 -- p_rec structure.
614 --
615 l_rec :=
616 per_sip_shd.convert_args
617 (null
618 ,p_plan_id
619 ,p_successor_id
620 ,p_business_group_id
621 ,p_status
622 ,p_potential
623 ,p_performance_rating
624 ,p_risk_of_loss
625 ,p_impact_of_loss
626 ,p_readiness_pct
627 ,p_rank
628 ,p_eligible_for_promotion
629 ,p_earliest_succession_date
630 ,p_latest_succession_date
631 ,p_comments
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_planned_date
654 ,p_assignment_id
655 ,p_supervisor_id
656 ,p_grade_id
657 ,p_grade_ladder_id
658 ,p_grade_step_id
659 ,p_grade_point_id
660 ,p_position_id
661 ,p_job_id
662 ,p_assignment_status_type_id
663 ,p_organization_id
664 ,p_people_group_id
665 ,p_pay_basis_id
666 ,p_employee_category
667 ,p_bargaining_unit_code
668 ,p_labour_union_member_flag
669 ,p_collective_agreement_id
670 ,p_assignment_category
671 ,p_location_id
672 ,p_contract_id
673 ,null
674 );
675 --
676 -- Having converted the arguments into the per_sip_rec
677 -- plsql record structure we call the corresponding record business process.
678 --
679 per_sip_ins.ins
680 (p_effective_date
681 ,l_rec
682 );
683 --
684 -- As the primary key argument(s)
685 -- are specified as an OUT's we must set these values.
686 --
687 p_successor_in_plan_id := l_rec.successor_in_plan_id;
688 p_object_version_number := l_rec.object_version_number;
689 --
690 hr_utility.set_location(' Leaving:'||l_proc, 10);
691 End ins;
692 --
693 end per_sip_ins;