[Home] [Help]
PACKAGE BODY: APPS.PER_OBJ_INS
Source
1 Package Body per_obj_ins as
2 /* $Header: peobjrhi.pkb 120.20 2011/12/19 10:22:22 kgowripe ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_obj_ins.'; -- Global package name
9 --
10 g_objective_id_i number default null;
11 --
12 -- ----------------------------------------------------------------------------
13 -- |------------------------< set_base_key_value >----------------------------|
14 -- ----------------------------------------------------------------------------
15 --
16 procedure set_base_key_value
17 (p_objective_id in number) is
18 --
19 l_proc varchar2(72) := g_package||'set_base_key_value';
20 --
21 Begin
22 hr_utility.set_location('Entering:'||l_proc, 10);
23 --
24 per_obj_ins.g_objective_id_i := p_objective_id;
25 --
26 hr_utility.set_location(' Leaving:'||l_proc, 20);
27 End set_base_key_value;
28 --
29 --
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 insert the row into the schema.
43 -- 3) To trap any constraint violations that may have occurred.
44 -- 4) To raise any other errors.
45 --
46 -- Pre Conditions:
47 -- This is an internal private procedure which must be called from the ins
48 -- procedure and must have all mandatory attributes set (except the
49 -- object_version_number which is initialised within this procedure).
50 --
51 -- In Parameters:
52 -- A Pl/Sql record structre.
53 --
54 -- Post Success:
55 -- The specified row will be inserted into the schema.
56 --
57 -- Post Failure:
58 -- If a check, unique or parent integrity constraint violation is raised the
59 -- constraint_error procedure will be called.
60 --
61 -- Developer Implementation Notes:
62 -- None.
63 --
64 -- Access Status:
65 -- Internal Table Handler Use Only.
66 --
67 -- {End Of Comments}
68 -- ----------------------------------------------------------------------------
69 Procedure insert_dml(p_rec in out nocopy per_obj_shd.g_rec_type) is
70 --
71 l_proc varchar2(72) := g_package||'insert_dml';
72 --
73 Begin
74 hr_utility.set_location('Entering:'||l_proc, 5);
75 p_rec.object_version_number := 1; -- Initialise the object version
76 --
77 -- Insert the row into: per_objectives
78 --
79 insert into per_objectives
80 ( objective_id,
81 name,
82 target_date,
83 start_date,
84 business_group_id,
85 object_version_number,
86 owning_person_id,
87 achievement_date,
88 detail,
89 comments,
90 success_criteria,
91 appraisal_id,
92 attribute_category,
93 attribute1,
94 attribute2,
95 attribute3,
96 attribute4,
97 attribute5,
98 attribute6,
99 attribute7,
100 attribute8,
101 attribute9,
102 attribute10,
103 attribute11,
104 attribute12,
105 attribute13,
106 attribute14,
107 attribute15,
108 attribute16,
109 attribute17,
110 attribute18,
111 attribute19,
112 attribute20,
113
114 attribute21,
115 attribute22,
116 attribute23,
117 attribute24,
118 attribute25,
119 attribute26,
120 attribute27,
121 attribute28,
122 attribute29,
123 attribute30,
124
125 scorecard_id,
126 copied_from_library_id,
127 copied_from_objective_id,
128 aligned_with_objective_id,
129
130 next_review_date,
131 group_code,
132 priority_code,
133 appraise_flag,
134 verified_flag,
135
136 target_value,
137 actual_value,
138 weighting_percent,
139 complete_percent,
140 uom_code,
141
142 measurement_style_code,
143 measure_name,
144 measure_type_code,
145 measure_comments ,
146 sharing_access_code
147
148 )
149 Values
150 ( p_rec.objective_id,
151 p_rec.name,
152 p_rec.target_date,
153 p_rec.start_date,
154 p_rec.business_group_id,
155 p_rec.object_version_number,
156 p_rec.owning_person_id,
157 p_rec.achievement_date,
158 p_rec.detail,
159 p_rec.comments,
160 p_rec.success_criteria,
161 p_rec.appraisal_id,
162 p_rec.attribute_category,
163 p_rec.attribute1,
164 p_rec.attribute2,
165 p_rec.attribute3,
166 p_rec.attribute4,
167 p_rec.attribute5,
168 p_rec.attribute6,
169 p_rec.attribute7,
170 p_rec.attribute8,
171 p_rec.attribute9,
172 p_rec.attribute10,
173 p_rec.attribute11,
174 p_rec.attribute12,
175 p_rec.attribute13,
176 p_rec.attribute14,
177 p_rec.attribute15,
178 p_rec.attribute16,
179 p_rec.attribute17,
180 p_rec.attribute18,
181 p_rec.attribute19,
182 p_rec.attribute20,
183
184 p_rec.attribute21,
185 p_rec.attribute22,
186 p_rec.attribute23,
187 p_rec.attribute24,
188 p_rec.attribute25,
189 p_rec.attribute26,
190 p_rec.attribute27,
191 p_rec.attribute28,
192 p_rec.attribute29,
193 p_rec.attribute30,
194
195 p_rec.scorecard_id,
196 p_rec.copied_from_library_id,
197 p_rec.copied_from_objective_id,
198 p_rec.aligned_with_objective_id,
199
200 p_rec.next_review_date,
201 p_rec.group_code,
202 p_rec.priority_code,
203 p_rec.appraise_flag,
204 p_rec.verified_flag,
205
206 p_rec.target_value,
207 p_rec.actual_value,
208 p_rec.weighting_percent,
209 p_rec.complete_percent,
210 p_rec.uom_code,
211
212 p_rec.measurement_style_code,
213 p_rec.measure_name,
214 p_rec.measure_type_code,
215 p_rec.measure_comments ,
216 p_rec.sharing_access_code
217
218 );
219 --
220 --
221 hr_utility.set_location(' Leaving:'||l_proc, 10);
222 Exception
223 When hr_api.check_integrity_violated Then
224 -- A check constraint has been violated
225 per_obj_shd.constraint_error
226 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
227 When hr_api.parent_integrity_violated Then
228 -- Parent integrity has been violated
229 per_obj_shd.constraint_error
230 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
231 When hr_api.unique_integrity_violated Then
232 -- Unique integrity has been violated
233 per_obj_shd.constraint_error
234 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
235 When Others Then
236 Raise;
237 End insert_dml;
238 --
239 -- ----------------------------------------------------------------------------
240 -- |------------------------------< pre_insert >------------------------------|
241 -- ----------------------------------------------------------------------------
242 -- {Start Of Comments}
243 --
244 -- Description:
245 -- This private procedure contains any processing which is required before
246 -- the insert dml. Presently, if the entity has a corresponding primary
247 -- key which is maintained by an associating sequence, the primary key for
248 -- the entity will be populated with the next sequence value in
249 -- preparation for the insert dml.
250 --
251 -- Pre Conditions:
252 -- This is an internal procedure which is called from the ins procedure.
253 --
254 -- In Parameters:
255 -- A Pl/Sql record structre.
256 --
257 -- Post Success:
258 -- Processing continues.
259 --
260 -- Post Failure:
261 -- If an error has occurred, an error message and exception will be raised
262 -- but not handled.
263 --
264 -- Developer Implementation Notes:
265 -- Any pre-processing required before the insert dml is issued should be
266 -- coded within this procedure. As stated above, a good example is the
267 -- generation of a primary key number via a corresponding sequence.
268 -- It is important to note that any 3rd party maintenance should be reviewed
269 -- before placing in this procedure.
270 --
271 -- Access Status:
272 -- Internal Table Handler Use Only.
273 --
274 -- {End Of Comments}
275 -- ----------------------------------------------------------------------------
276 Procedure pre_insert(p_rec in out nocopy per_obj_shd.g_rec_type) is
277 --
278 l_proc varchar2(72) := g_package||'pre_insert';
279 l_exists varchar2(1);
280 --
281 Cursor C_Sel1 is select per_objectives_s.nextval from sys.dual;
282 --
283 --
284 Cursor C_Sel2 is
285 Select null
286 from per_objectives
287 where objective_id =
288 per_obj_ins.g_objective_id_i;
289 --
290
291 Begin
292 hr_utility.set_location('Entering:'||l_proc, 5);
293 --
294 --
295 -- Select the next sequence number
296 --
297 IF (per_obj_ins.g_objective_id_i is not null)
298 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','PER_OBJECTIVES');
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.objective_id := per_obj_ins.g_objective_id_i;
318 per_obj_ins.g_objective_id_i := null;
319 Else
320
321 Open C_Sel1;
322 Fetch C_Sel1 Into p_rec.objective_id;
323 Close C_Sel1;
324
325 END IF;
326 --
327 hr_utility.set_location(' Leaving:'||l_proc, 10);
328 End pre_insert;
329 --
330 -- ----------------------------------------------------------------------------
331 -- |-----------------------------< post_insert >------------------------------|
332 -- ----------------------------------------------------------------------------
333 -- {Start Of Comments}
334 --
335 -- Description:
336 -- This private procedure contains any processing which is required after the
337 -- insert dml.
338 --
339 -- Pre Conditions:
340 -- This is an internal procedure which is called from the ins procedure.
341 --
342 -- In Parameters:
343 -- A Pl/Sql record structre.
344 --
345 -- Post Success:
346 -- Processing continues.
347 --
348 -- Post Failure:
349 -- If an error has occurred, an error message and exception will be raised
350 -- but not handled.
351 --
352 -- Developer Implementation Notes:
353 -- Any post-processing required after the insert dml is issued should be
354 -- coded within this procedure. It is important to note that any 3rd party
355 -- maintenance should be reviewed before placing in this procedure.
356 --
357 -- Access Status:
358 -- Internal Table Handler Use Only.
359 --
360 -- {End Of Comments}
361 -- ----------------------------------------------------------------------------
362 Procedure post_insert
363 (p_rec in per_obj_shd.g_rec_type,
364 p_effective_date in date,
365 p_weighting_over_100_warning out nocopy boolean,
366 p_weighting_appraisal_warning out nocopy boolean
367 ) is
368 --
369 l_proc varchar2(72) := g_package||'post_insert';
370 --
371 Begin
372 hr_utility.set_location('Entering:'||l_proc, 5);
373 --
374 -- Start of API User Hook for post_insert.
375 begin
376 per_obj_rki.after_insert
377 (
378 p_objective_id => p_rec.objective_id,
379 p_name => p_rec.name,
380 p_target_date => p_rec.target_date,
381 p_start_date => p_rec.start_date,
382 p_business_group_id => p_rec.business_group_id,
383 p_object_version_number => p_rec.object_version_number,
384 p_owning_person_id => p_rec.owning_person_id,
385 p_achievement_date => p_rec.achievement_date,
386 p_detail => p_rec.detail,
387 p_comments => p_rec.comments,
388 p_success_criteria => p_rec.success_criteria,
389 p_appraisal_id => p_rec.appraisal_id,
390 p_attribute_category => p_rec.attribute_category,
391 p_attribute1 => p_rec.attribute1,
392 p_attribute2 => p_rec.attribute2,
393 p_attribute3 => p_rec.attribute3,
394 p_attribute4 => p_rec.attribute4,
395 p_attribute5 => p_rec.attribute5,
396 p_attribute6 => p_rec.attribute6,
397 p_attribute7 => p_rec.attribute7,
398 p_attribute8 => p_rec.attribute8,
399 p_attribute9 => p_rec.attribute9,
400 p_attribute10 => p_rec.attribute10,
401 p_attribute11 => p_rec.attribute11,
402 p_attribute12 => p_rec.attribute12,
403 p_attribute13 => p_rec.attribute13,
404 p_attribute14 => p_rec.attribute14,
405 p_attribute15 => p_rec.attribute15,
406 p_attribute16 => p_rec.attribute16,
407 p_attribute17 => p_rec.attribute17,
408 p_attribute18 => p_rec.attribute18,
409 p_attribute19 => p_rec.attribute19,
410 p_attribute20 => p_rec.attribute20,
411
412 p_attribute21 => p_rec.attribute21,
413 p_attribute22 => p_rec.attribute22,
414 p_attribute23 => p_rec.attribute23,
415 p_attribute24 => p_rec.attribute24,
416 p_attribute25 => p_rec.attribute25,
417 p_attribute26 => p_rec.attribute26,
418 p_attribute27 => p_rec.attribute27,
419 p_attribute28 => p_rec.attribute28,
420 p_attribute29 => p_rec.attribute29,
421 p_attribute30 => p_rec.attribute30,
422
423 p_scorecard_id => p_rec.scorecard_id,
424 p_copied_from_library_id => p_rec.copied_from_library_id,
425 p_copied_from_objective_id => p_rec.copied_from_objective_id,
426 p_aligned_with_objective_id => p_rec.aligned_with_objective_id,
427
428 p_next_review_date => p_rec.next_review_date,
429 p_group_code => p_rec.group_code,
430 p_priority_code => p_rec.priority_code,
431 p_appraise_flag => p_rec.appraise_flag,
432 p_verified_flag => p_rec.verified_flag,
433
434 p_target_value => p_rec.target_value,
435 p_actual_value => p_rec.actual_value,
436 p_weighting_percent => p_rec.weighting_percent,
437 p_complete_percent => p_rec.complete_percent,
438 p_uom_code => p_rec.uom_code,
439
440 p_measurement_style_code => p_rec.measurement_style_code,
441 p_measure_name => p_rec.measure_name,
442 p_measure_type_code => p_rec.measure_type_code,
443 p_measure_comments => p_rec.measure_comments ,
444 p_sharing_access_code => p_rec.sharing_access_code,
445
446 p_weighting_over_100_warning => p_weighting_over_100_warning,
447 p_weighting_appraisal_warning => p_weighting_appraisal_warning,
448
449 p_effective_date => p_effective_date
450 );
451 exception
452 when hr_api.cannot_find_prog_unit then
453 hr_api.cannot_find_prog_unit_error
454 (p_module_name => 'PER_OBJECTIVES'
455 ,p_hook_type => 'AI'
456 );
457 end;
458 -- End of API User Hook for post_insert.
459 --
460 hr_utility.set_location(' Leaving:'||l_proc, 10);
461 End post_insert;
462 --
463 -- ----------------------------------------------------------------------------
464 -- |---------------------------------< ins >----------------------------------|
465 -- ----------------------------------------------------------------------------
466 Procedure ins
467 (
468 p_rec in out nocopy per_obj_shd.g_rec_type,
469 p_effective_date in date,
470 p_validate in boolean default false,
471 p_weighting_over_100_warning out nocopy boolean,
472 p_weighting_appraisal_warning out nocopy boolean
473 ) is
474 --
475 l_proc varchar2(72) := g_package||'ins';
476 l_effective_date date;
477 --
478 Begin
479 hr_utility.set_location('Entering:'||l_proc, 5);
480 --
481 -- Determine if the business process is to be validated.
482 --
483 If p_validate then
484 --
485 -- Issue the savepoint.
486 --
487 SAVEPOINT ins_per_obj;
488 End If;
489
490 --- begin-- bug fix 6027219---
491 If(p_rec.scorecard_id IS NOT NULL AND p_rec.appraisal_id IS NOT NULL)
492 THEN
493 p_rec.appraise_flag:='Y';
494 END IF;
495
496 --- end-- bug fix 6027219 ---
497
498 --
499 -- Call the supporting insert validate operations
500 --
501 per_obj_bus.insert_validate
502 (p_rec
503 ,p_effective_date
504 ,p_weighting_over_100_warning
505 ,p_weighting_appraisal_warning
506 );
507
508 --
509 hr_multi_message.end_validation_set;
510 --
511
512 --
513 -- Call the supporting pre-insert operation
514 --
515 pre_insert(p_rec);
516 --
517 -- Insert the row
518 --
519 insert_dml(p_rec);
520 --
521 -- Call the supporting post-insert operation
522 --
523 post_insert(p_rec
524 , p_effective_date
525 , p_weighting_over_100_warning
526 , p_weighting_appraisal_warning
527 );
528
529 --
530 hr_multi_message.end_validation_set;
531 --
532
533 --
534 -- If we are validating then raise the Validate_Enabled exception
535 --
536 If p_validate then
537 Raise HR_Api.Validate_Enabled;
538 End If;
539 --
540 hr_utility.set_location(' Leaving:'||l_proc, 10);
541 Exception
542 When HR_Api.Validate_Enabled Then
543 --
544 -- As the Validate_Enabled exception has been raised
545 -- we must rollback to the savepoint
546 --
547 ROLLBACK TO ins_per_obj;
548 end ins;
549 --
550 -- ----------------------------------------------------------------------------
551 -- |---------------------------------< ins >----------------------------------|
552 -- ----------------------------------------------------------------------------
553 Procedure ins
554 (
555 p_objective_id out nocopy number,
556 p_name in varchar2,
557 p_target_date in date default null,
558 p_start_date in date,
559 p_business_group_id in number,
560 p_object_version_number out nocopy number,
561 p_owning_person_id in number,
562 p_achievement_date in date default null,
563 p_detail in varchar2 default null,
564 p_comments in varchar2 default null,
565 p_success_criteria in varchar2 default null,
566 p_appraisal_id in number default null,
567 p_attribute_category in varchar2 default null,
568 p_attribute1 in varchar2 default null,
569 p_attribute2 in varchar2 default null,
570 p_attribute3 in varchar2 default null,
571 p_attribute4 in varchar2 default null,
572 p_attribute5 in varchar2 default null,
573 p_attribute6 in varchar2 default null,
574 p_attribute7 in varchar2 default null,
575 p_attribute8 in varchar2 default null,
576 p_attribute9 in varchar2 default null,
577 p_attribute10 in varchar2 default null,
578 p_attribute11 in varchar2 default null,
579 p_attribute12 in varchar2 default null,
580 p_attribute13 in varchar2 default null,
581 p_attribute14 in varchar2 default null,
582 p_attribute15 in varchar2 default null,
583 p_attribute16 in varchar2 default null,
584 p_attribute17 in varchar2 default null,
585 p_attribute18 in varchar2 default null,
586 p_attribute19 in varchar2 default null,
587 p_attribute20 in varchar2 default null,
588
589 p_attribute21 in varchar2 default null,
590 p_attribute22 in varchar2 default null,
591 p_attribute23 in varchar2 default null,
592 p_attribute24 in varchar2 default null,
593 p_attribute25 in varchar2 default null,
594 p_attribute26 in varchar2 default null,
595 p_attribute27 in varchar2 default null,
596 p_attribute28 in varchar2 default null,
597 p_attribute29 in varchar2 default null,
598 p_attribute30 in varchar2 default null,
599
600 p_scorecard_id in number default null,
601 p_copied_from_library_id in number default null,
602 p_copied_from_objective_id in number default null,
603 p_aligned_with_objective_id in number default null,
604
605 p_next_review_date in date default null,
606 p_group_code in varchar2 default null,
607 p_priority_code in varchar2 default null,
608 p_appraise_flag in varchar2 default null,
609 p_verified_flag in varchar2 default null,
610
611 p_target_value in number default null,
612 p_actual_value in number default null,
613 p_weighting_percent in number default null,
614 p_complete_percent in number default null,
615 p_uom_code in varchar2 default null,
616
617 p_measurement_style_code in varchar2 default null,
618 p_measure_name in varchar2 default null,
619 p_measure_type_code in varchar2 default null,
620 p_measure_comments in varchar2 default null,
621 p_sharing_access_code in varchar2 default null,
622
623 p_weighting_over_100_warning out nocopy boolean,
624 p_weighting_appraisal_warning out nocopy boolean,
625
626 p_effective_date in date,
627 p_validate in boolean default false
628 ) is
629 --
630 l_rec per_obj_shd.g_rec_type;
631 l_proc varchar2(72) := g_package||'ins';
632 l_weighting_over_100_warning boolean;
633 l_weighting_appraisal_warning boolean;
634 --
635 Begin
636 hr_utility.set_location('Entering:'||l_proc, 5);
637 --
638 -- Call conversion function to turn arguments into the
639 -- p_rec structure.
640 --
641 l_rec :=
642 per_obj_shd.convert_args
643 (
644 null,
645 p_name,
646 p_target_date,
647 p_start_date,
648 p_business_group_id,
649 null,
650 p_owning_person_id,
651 p_achievement_date,
652 p_detail,
653 p_comments,
654 p_success_criteria,
655 p_appraisal_id,
656 p_attribute_category,
657 p_attribute1,
658 p_attribute2,
659 p_attribute3,
660 p_attribute4,
661 p_attribute5,
662 p_attribute6,
663 p_attribute7,
664 p_attribute8,
665 p_attribute9,
666 p_attribute10,
667 p_attribute11,
668 p_attribute12,
669 p_attribute13,
670 p_attribute14,
671 p_attribute15,
672 p_attribute16,
673 p_attribute17,
674 p_attribute18,
675 p_attribute19,
676 p_attribute20,
677
678 p_attribute21,
679 p_attribute22,
680 p_attribute23,
681 p_attribute24,
682 p_attribute25,
683 p_attribute26,
684 p_attribute27,
685 p_attribute28,
686 p_attribute29,
687 p_attribute30,
688
689 p_scorecard_id,
690 p_copied_from_library_id,
691 p_copied_from_objective_id,
692 p_aligned_with_objective_id,
693
694 p_next_review_date,
695 p_group_code,
696 p_priority_code,
697 p_appraise_flag,
698 p_verified_flag,
699
700 p_target_value,
701 p_actual_value,
702 p_weighting_percent,
703 p_complete_percent,
704 p_uom_code,
705
706 p_measurement_style_code,
707 p_measure_name,
708 p_measure_type_code,
709 p_measure_comments ,
710 p_sharing_access_code
711
712 );
713 --
714 -- Having converted the arguments into the per_obj_rec
715 -- plsql record structure we call the corresponding record business process.
716 --
717 ins(l_rec,
718 p_effective_date,
719 p_validate,
720 l_weighting_over_100_warning,
721 l_weighting_appraisal_warning
722 );
723 --
724 -- As the primary key argument(s)
725 -- are specified as an OUT's we must set these values.
726 --
727 p_objective_id := l_rec.objective_id;
728 p_object_version_number := l_rec.object_version_number;
729
730 p_weighting_over_100_warning := l_weighting_over_100_warning;
731 p_weighting_appraisal_warning := l_weighting_appraisal_warning;
732 --
733 hr_utility.set_location(' Leaving:'||l_proc, 10);
734 End ins;
735 --
736 end per_obj_ins;