[Home] [Help]
PACKAGE BODY: APPS.PER_PMS_INS
Source
1 Package Body per_pms_ins as
2 /* $Header: pepmsrhi.pkb 120.6.12020000.3 2012/07/05 03:39:34 amnaraya ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_pms_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_scorecard_id_i number default null;
14 --
15 -- ----------------------------------------------------------------------------
16 -- |------------------------< set_base_key_value >----------------------------|
17 -- ----------------------------------------------------------------------------
18 procedure set_base_key_value
19 (p_scorecard_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_pms_ins.g_scorecard_id_i := p_scorecard_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_pms_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_personal_scorecards
88 --
89 insert into per_personal_scorecards
90 (scorecard_id
91 ,object_version_number
92 ,scorecard_name
93 ,person_id
94 ,assignment_id
95 ,start_date
96 ,end_date
97 ,plan_id
98 ,status_code
99 ,creator_type
100 ,error_log
101 ,attribute_category
102 ,attribute1
103 ,attribute2
104 ,attribute3
105 ,attribute4
106 ,attribute5
107 ,attribute6
108 ,attribute7
109 ,attribute8
110 ,attribute9
111 ,attribute10
112 ,attribute11
113 ,attribute12
114 ,attribute13
115 ,attribute14
116 ,attribute15
117 ,attribute16
118 ,attribute17
119 ,attribute18
120 ,attribute19
121 ,attribute20
122 ,attribute21
123 ,attribute22
124 ,attribute23
125 ,attribute24
126 ,attribute25
127 ,attribute26
128 ,attribute27
129 ,attribute28
130 ,attribute29
131 ,attribute30
132 ,obj_setting_deadline
133 ,supervisor_id
134 ,supervisor_assignment_id
135 ,eligibility_status
136 ,eligibility_eval_date
137 )
138 Values
139 (p_rec.scorecard_id
140 ,p_rec.object_version_number
141 ,p_rec.scorecard_name
142 ,p_rec.person_id
143 ,p_rec.assignment_id
144 ,p_rec.start_date
145 ,p_rec.end_date
146 ,p_rec.plan_id
147 ,p_rec.status_code
148 ,p_rec.creator_type
149 ,p_rec.error_log
150 ,p_rec.attribute_category
151 ,p_rec.attribute1
152 ,p_rec.attribute2
153 ,p_rec.attribute3
154 ,p_rec.attribute4
155 ,p_rec.attribute5
156 ,p_rec.attribute6
157 ,p_rec.attribute7
158 ,p_rec.attribute8
159 ,p_rec.attribute9
160 ,p_rec.attribute10
161 ,p_rec.attribute11
162 ,p_rec.attribute12
163 ,p_rec.attribute13
164 ,p_rec.attribute14
165 ,p_rec.attribute15
166 ,p_rec.attribute16
167 ,p_rec.attribute17
168 ,p_rec.attribute18
169 ,p_rec.attribute19
170 ,p_rec.attribute20
171 ,p_rec.attribute21
172 ,p_rec.attribute22
173 ,p_rec.attribute23
174 ,p_rec.attribute24
175 ,p_rec.attribute25
176 ,p_rec.attribute26
177 ,p_rec.attribute27
178 ,p_rec.attribute28
179 ,p_rec.attribute29
180 ,p_rec.attribute30
181 ,p_rec.obj_setting_deadline
182 ,p_rec.supervisor_id
183 ,p_rec.supervisor_assignment_id
184 ,p_rec.eligibility_status
185 ,p_rec.eligibility_eval_date
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 per_pms_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 per_pms_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 per_pms_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 per_pms_shd.g_rec_type
251 ) is
252 --
253 Cursor C_Sel1 is select per_personal_scorecards_s.nextval from sys.dual;
254 --
255 Cursor C_Sel2 is
256 Select null
257 from per_personal_scorecards
258 where scorecard_id =
259 per_pms_ins.g_scorecard_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 (per_pms_ins.g_scorecard_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','per_personal_scorecards');
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.scorecard_id :=
287 per_pms_ins.g_scorecard_id_i;
288 per_pms_ins.g_scorecard_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.scorecard_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 per_pms_shd.g_rec_type
339 ,p_duplicate_name_warning in boolean
340 ) is
341 --
342 l_proc varchar2(72) := g_package||'post_insert';
343 --
344 Begin
345 hr_utility.set_location('Entering:'||l_proc, 5);
346 begin
347 --
348 per_pms_rki.after_insert
349 (p_effective_date => p_effective_date
350 ,p_scorecard_id
351 => p_rec.scorecard_id
352 ,p_object_version_number
353 => p_rec.object_version_number
354 ,p_scorecard_name
355 => p_rec.scorecard_name
356 ,p_person_id
357 => p_rec.person_id
358 ,p_assignment_id
359 => p_rec.assignment_id
360 ,p_start_date
361 => p_rec.start_date
362 ,p_end_date
363 => p_rec.end_date
364 ,p_plan_id
365 => p_rec.plan_id
366 ,p_creator_type
367 => p_rec.creator_type
368 ,p_error_log
369 => p_rec.error_log
370 ,p_attribute_category
371 => p_rec.attribute_category
372 ,p_attribute1
373 => p_rec.attribute1
374 ,p_attribute2
375 => p_rec.attribute2
376 ,p_attribute3
377 => p_rec.attribute3
378 ,p_attribute4
379 => p_rec.attribute4
380 ,p_attribute5
381 => p_rec.attribute5
382 ,p_attribute6
383 => p_rec.attribute6
384 ,p_attribute7
385 => p_rec.attribute7
386 ,p_attribute8
387 => p_rec.attribute8
388 ,p_attribute9
389 => p_rec.attribute9
390 ,p_attribute10
391 => p_rec.attribute10
392 ,p_attribute11
393 => p_rec.attribute11
394 ,p_attribute12
395 => p_rec.attribute12
396 ,p_attribute13
397 => p_rec.attribute13
398 ,p_attribute14
399 => p_rec.attribute14
400 ,p_attribute15
401 => p_rec.attribute15
402 ,p_attribute16
403 => p_rec.attribute16
404 ,p_attribute17
405 => p_rec.attribute17
406 ,p_attribute18
407 => p_rec.attribute18
408 ,p_attribute19
409 => p_rec.attribute19
410 ,p_attribute20
411 => p_rec.attribute20
412 ,p_attribute21
413 => p_rec.attribute21
414 ,p_attribute22
415 => p_rec.attribute22
416 ,p_attribute23
417 => p_rec.attribute23
418 ,p_attribute24
419 => p_rec.attribute24
420 ,p_attribute25
421 => p_rec.attribute25
422 ,p_attribute26
423 => p_rec.attribute26
424 ,p_attribute27
425 => p_rec.attribute27
426 ,p_attribute28
427 => p_rec.attribute28
428 ,p_attribute29
429 => p_rec.attribute29
430 ,p_attribute30
431 => p_rec.attribute30
432 ,p_status_code
433 => p_rec.status_code
434 ,p_duplicate_name_warning
435 => p_duplicate_name_warning
436 ,p_obj_setting_deadline
437 => p_rec.obj_setting_deadline
438 ,p_supervisor_id
439 => p_rec.supervisor_id
440 ,p_supervisor_assignment_id
441 => p_rec.supervisor_assignment_id
442 ,p_eligibility_status
443 => p_rec.eligibility_status
444 ,p_eligibility_eval_date
445 => p_rec.eligibility_eval_date
446 );
447 --
448 exception
449 --
450 when hr_api.cannot_find_prog_unit then
451 --
452 hr_api.cannot_find_prog_unit_error
453 (p_module_name => 'PER_PERSONAL_SCORECARDS'
454 ,p_hook_type => 'AI');
455 --
456 end;
457 --
458 hr_utility.set_location(' Leaving:'||l_proc, 10);
459 End post_insert;
460 --
461 -- ----------------------------------------------------------------------------
462 -- |---------------------------------< ins >----------------------------------|
463 -- ----------------------------------------------------------------------------
464 Procedure ins
465 (p_effective_date in date
466 ,p_rec in out nocopy per_pms_shd.g_rec_type
467 ,p_duplicate_name_warning out nocopy boolean
468 ) is
469 --
470 l_proc varchar2(72) := g_package||'ins';
471 l_person_id number;
472 --
473 Begin
474 hr_utility.set_location('Entering:'||l_proc, 5);
475 --
476 -- Call the supporting insert validate operations
477 --
478
479 -- While inserting the status code should be one of the following two only.
480 IF (p_rec.status_code = 'NOT_STARTED_WITH_MGR' OR
481 p_rec.status_code = 'NOT_STARTED_WITH_WKR')
482 THEN
483 null;
484 ELSE
485 fnd_message.set_name('PER','HR_50271_PMS_STATUS_TP');
486 fnd_message.raise_error;
487 end if;
488 per_pms_bus.insert_validate
489 (p_effective_date => p_effective_date
490 ,p_rec => p_rec
491 ,p_person_id => l_person_id
492 ,p_duplicate_name_warning => p_duplicate_name_warning
493 );
494 --
495 -- person_id is not passed in, it is derived.
496 -- This is fetched during chk_assignment_id to prevent another
497 -- hit to per_all_assignments_f.
498 --
499 p_rec.person_id := l_person_id;
500
501 --
502 -- Call to raise any errors on multi-message list
503 hr_multi_message.end_validation_set;
504 --
505 -- Call the supporting pre-insert operation
506 --
507 per_pms_ins.pre_insert(p_rec);
508 --
509 -- Insert the row
510 --
511 per_pms_ins.insert_dml(p_rec);
512 --
513 -- Call the supporting post-insert operation
514 --
515 per_pms_ins.post_insert
516 (p_effective_date => p_effective_date
517 ,p_rec => p_rec
518 ,p_duplicate_name_warning => p_duplicate_name_warning
519 );
520 --
521 -- Call to raise any errors on multi-message list
522 hr_multi_message.end_validation_set;
523 --
524 hr_utility.set_location('Leaving:'||l_proc, 20);
525 end ins;
526 --
527 -- ----------------------------------------------------------------------------
528 -- |---------------------------------< ins >----------------------------------|
529 -- ----------------------------------------------------------------------------
530 Procedure ins
531 (p_effective_date in date
532 ,p_scorecard_name in varchar2
533 ,p_assignment_id in number
534 ,p_start_date in date
535 ,p_end_date in date
536 ,p_creator_type in varchar2
537 ,p_plan_id in number default null
538 ,p_error_log in varchar2 default null
539 ,p_attribute_category in varchar2 default null
540 ,p_attribute1 in varchar2 default null
541 ,p_attribute2 in varchar2 default null
542 ,p_attribute3 in varchar2 default null
543 ,p_attribute4 in varchar2 default null
544 ,p_attribute5 in varchar2 default null
545 ,p_attribute6 in varchar2 default null
546 ,p_attribute7 in varchar2 default null
547 ,p_attribute8 in varchar2 default null
548 ,p_attribute9 in varchar2 default null
549 ,p_attribute10 in varchar2 default null
550 ,p_attribute11 in varchar2 default null
551 ,p_attribute12 in varchar2 default null
552 ,p_attribute13 in varchar2 default null
553 ,p_attribute14 in varchar2 default null
554 ,p_attribute15 in varchar2 default null
555 ,p_attribute16 in varchar2 default null
556 ,p_attribute17 in varchar2 default null
557 ,p_attribute18 in varchar2 default null
558 ,p_attribute19 in varchar2 default null
559 ,p_attribute20 in varchar2 default null
560 ,p_attribute21 in varchar2 default null
561 ,p_attribute22 in varchar2 default null
562 ,p_attribute23 in varchar2 default null
563 ,p_attribute24 in varchar2 default null
564 ,p_attribute25 in varchar2 default null
565 ,p_attribute26 in varchar2 default null
566 ,p_attribute27 in varchar2 default null
567 ,p_attribute28 in varchar2 default null
568 ,p_attribute29 in varchar2 default null
569 ,p_attribute30 in varchar2 default null
570 ,p_scorecard_id out nocopy number
571 ,p_object_version_number out nocopy number
572 ,p_status_code in varchar2
573 ,p_duplicate_name_warning out nocopy boolean
574 ,p_obj_setting_deadline in date default null
575 ,p_supervisor_id in number default null
576 ,p_supervisor_assignment_id in number default null
577 ,p_eligibility_status in varchar2 default null
578 ,p_eligibility_eval_date in date default null
579 ) is
580 --
581 l_rec per_pms_shd.g_rec_type;
582 l_proc varchar2(72) := g_package||'ins';
583 -- l_STATUS_CODE CONSTANT varchar2(11) := 'NOT_STARTED';
584 --
585 Begin
586 hr_utility.set_location('Entering:'||l_proc, 5);
587 --
588 -- Call conversion function to turn arguments into the
589 -- p_rec structure.
590 --
591 l_rec :=
592 per_pms_shd.convert_args
593 (null
594 ,null
595 ,p_scorecard_name
596 ,null --p_person_id (derived)
597 ,p_assignment_id
598 ,p_start_date
599 ,p_end_date
600 ,p_plan_id
601 ,p_status_code
602 ,p_creator_type
603 ,p_error_log
604 ,p_attribute_category
605 ,p_attribute1
606 ,p_attribute2
607 ,p_attribute3
608 ,p_attribute4
609 ,p_attribute5
610 ,p_attribute6
611 ,p_attribute7
612 ,p_attribute8
613 ,p_attribute9
614 ,p_attribute10
615 ,p_attribute11
616 ,p_attribute12
617 ,p_attribute13
618 ,p_attribute14
619 ,p_attribute15
620 ,p_attribute16
621 ,p_attribute17
622 ,p_attribute18
623 ,p_attribute19
624 ,p_attribute20
625 ,p_attribute21
626 ,p_attribute22
627 ,p_attribute23
628 ,p_attribute24
629 ,p_attribute25
630 ,p_attribute26
631 ,p_attribute27
632 ,p_attribute28
633 ,p_attribute29
634 ,p_attribute30
635 ,p_obj_setting_deadline
636 ,p_supervisor_id
637 ,p_supervisor_assignment_id
638 ,p_eligibility_status
639 ,p_eligibility_eval_date
640 );
641 --
642 -- Having converted the arguments into the per_pms_rec
643 -- plsql record structure we call the corresponding record business process.
644 --
645 per_pms_ins.ins
646 (p_effective_date => p_effective_date
647 ,p_rec => l_rec
648 ,p_duplicate_name_warning => p_duplicate_name_warning
649 );
650 --
651 -- As the primary key argument(s)
652 -- are specified as an OUT's we must set these values.
653 --
654 p_scorecard_id := l_rec.scorecard_id;
655 p_object_version_number := l_rec.object_version_number;
656 --p_status_code := l_rec.status_code;
657 --
658 hr_utility.set_location(' Leaving:'||l_proc, 10);
659 End ins;
660 --
661 end per_pms_ins;