[Home] [Help]
PACKAGE BODY: APPS.PER_APR_INS
Source
1 Package Body per_apr_ins as
2 /* $Header: peaprrhi.pkb 120.29.12020000.2 2012/07/05 00:47:21 amnaraya ship $ */
3
4 -- ---------------------------------------------------------------------------+
5 -- | Private Global Definitions |
6 -- ---------------------------------------------------------------------------+
7
8 g_package varchar2(33) := ' per_apr_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_appraisal_id_i number default null;
14 --
15 --
16 -- ----------------------------------------------------------------------------
17 -- |------------------------< set_base_key_value >----------------------------|
18 -- ----------------------------------------------------------------------------
19 procedure set_base_key_value
20 (p_appraisal_id in number) is
21 --
22 l_proc varchar2(72) := g_package||'set_base_key_value';
23 --
24 Begin
25 hr_utility.set_location('Entering:'||l_proc, 10);
26 --
27 per_apr_ins.g_appraisal_id_i := p_appraisal_id;
28 --
29 hr_utility.set_location(' Leaving:'||l_proc, 20);
30 End set_base_key_value;
31 --
32 --
33 -- ---------------------------------------------------------------------------+
34 -- |------------------------------< insert_dml >------------------------------|
35 -- ---------------------------------------------------------------------------+
36 -- {Start Of Comments}
37
38 -- Description:
39 -- This procedure controls the actual dml insert logic. The processing of
40 -- this procedure are as follows:
41 -- 1) Initialise the object_version_number to 1 if the object_version_number
42 -- is defined as an attribute for this entity.
43 -- 2) To insert the row into the schema.
44 -- 3) To trap any constraint violations that may have occurred.
45 -- 4) To raise any other errors.
46
47 -- Pre Conditions:
48 -- This is an internal private procedure which must be called from the ins
49 -- procedure and must have all mandatory attributes set (except the
50 -- object_version_number which is initialised within this procedure).
51
52 -- In Parameters:
53 -- A Pl/Sql record structre.
54
55 -- Post Success:
56 -- The specified row will be inserted into the schema.
57
58 -- Post Failure:
59 -- If a check, unique or parent integrity constraint violation is raised the
60 -- constraint_error procedure will be called.
61
62 -- Developer Implementation Notes:
63 -- None.
64
65 -- Access Status:
66 -- Internal Table Handler Use Only.
67
68 -- {End Of Comments}
69 -- ---------------------------------------------------------------------------+
70 Procedure insert_dml(p_rec in out nocopy per_apr_shd.g_rec_type) is
71
72 l_proc varchar2(72) := g_package||'insert_dml';
73
74 Begin
75 hr_utility.set_location('Entering:'||l_proc, 5);
76 p_rec.object_version_number := 1; -- Initialise the object version
77
78
79 -- Insert the row into: per_appraisals
80
81 insert into per_appraisals
82 ( appraisal_id,
86 appraisee_person_id,
83 business_group_id,
84 object_version_number,
85 appraisal_template_id,
87 appraiser_person_id,
88 appraisal_date,
89 appraisal_period_end_date,
90 appraisal_period_start_date,
91 type,
92 next_appraisal_date,
93 status,
94 group_date,
95 group_initiator_id,
96 comments,
97 overall_performance_level_id,
98 open,
99 attribute_category,
100 attribute1,
101 attribute2,
102 attribute3,
103 attribute4,
104 attribute5,
105 attribute6,
106 attribute7,
107 attribute8,
108 attribute9,
109 attribute10,
110 attribute11,
111 attribute12,
112 attribute13,
113 attribute14,
114 attribute15,
115 attribute16,
116 attribute17,
117 attribute18,
118 attribute19,
119 attribute20,
120 system_type,
121 system_params,
122 appraisee_access,
123 main_appraiser_id,
124 assignment_id,
125 assignment_start_date,
126 assignment_business_group_id,
127 assignment_organization_id ,
128 assignment_job_id ,
129 assignment_position_id ,
130 assignment_grade_id,
131 appraisal_system_status,
132 potential_readiness_level,
133 potential_short_term_workopp,
134 potential_long_term_workopp,
135 potential_details,
136 event_id,
137 show_competency_ratings,
138 show_objective_ratings,
139 show_questionnaire_info,
140 show_participant_details,
141 show_participant_ratings,
142 show_participant_names,
143 show_overall_ratings,
144 show_overall_comments,
145 update_appraisal,
146 provide_overall_feedback,
147 appraisee_comments,
148 plan_id,
149 offline_status,
150 retention_potential,
151 show_participant_comments -- 8651478 bug fix
152 )
153 Values
154 ( p_rec.appraisal_id,
155 p_rec.business_group_id,
156 p_rec.object_version_number,
157 p_rec.appraisal_template_id,
158 p_rec.appraisee_person_id,
159 p_rec.appraiser_person_id,
160 p_rec.appraisal_date,
161 p_rec.appraisal_period_end_date,
162 p_rec.appraisal_period_start_date,
163 p_rec.type,
164 p_rec.next_appraisal_date,
165 p_rec.status,
166 p_rec.group_date,
167 p_rec.group_initiator_id,
168 p_rec.comments,
169 p_rec.overall_performance_level_id,
170 p_rec.open,
171 p_rec.attribute_category,
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.system_type,
193 p_rec.system_params,
194 p_rec.appraisee_access,
195 p_rec.main_appraiser_id,
196 p_rec.assignment_id,
197 p_rec.assignment_start_date,
198 p_rec.assignment_business_group_id,
199 p_rec.assignment_organization_id ,
200 p_rec.assignment_job_id ,
201 p_rec.assignment_position_id ,
202 p_rec.assignment_grade_id,
203 p_rec.appraisal_system_status,
204 p_rec.potential_readiness_level,
205 p_rec.potential_short_term_workopp,
206 p_rec.potential_long_term_workopp,
207 p_rec.potential_details,
208 p_rec.event_id,
209 p_rec.show_competency_ratings,
210 p_rec.show_objective_ratings,
211 p_rec.show_questionnaire_info,
212 p_rec.show_participant_details,
213 p_rec.show_participant_ratings,
214 p_rec.show_participant_names,
215 p_rec.show_overall_ratings,
216 p_rec.show_overall_comments,
217 p_rec.update_appraisal,
218 p_rec.provide_overall_feedback,
219 p_rec.appraisee_comments,
220 p_rec.plan_id,
221 p_rec.offline_status,
222 p_rec.retention_potential,
223 p_rec.show_participant_comments -- 8651478 bug fix
224 );
225
226
227 hr_utility.set_location(' Leaving:'||l_proc, 10);
228 Exception
229 When hr_api.check_integrity_violated Then
230 -- A check constraint has been violated
231 per_apr_shd.constraint_error
232 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
233 When hr_api.parent_integrity_violated Then
234 -- Parent integrity has been violated
235 per_apr_shd.constraint_error
236 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
237 When hr_api.unique_integrity_violated Then
238 -- Unique integrity has been violated
239 per_apr_shd.constraint_error
240 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
241 When Others Then
242 Raise;
243 End insert_dml;
244
245 -- ---------------------------------------------------------------------------+
246 -- |------------------------------< pre_insert >------------------------------|
247 -- ---------------------------------------------------------------------------+
248 -- {Start Of Comments}
249
250 -- Description:
251 -- This private procedure contains any processing which is required before
252 -- the insert dml. Presently, if the entity has a corresponding primary
253 -- key which is maintained by an associating sequence, the primary key for
257 -- Pre Conditions:
254 -- the entity will be populated with the next sequence value in
255 -- preparation for the insert dml.
256
258 -- This is an internal procedure which is called from the ins procedure.
259
260 -- In Parameters:
261 -- A Pl/Sql record structre.
262
263 -- Post Success:
264 -- Processing continues.
265
266 -- Post Failure:
267 -- If an error has occurred, an error message and exception will be raised
268 -- but not handled.
269
270 -- Developer Implementation Notes:
271 -- Any pre-processing required before the insert dml is issued should be
272 -- coded within this procedure. As stated above, a good example is the
273 -- generation of a primary key number via a corresponding sequence.
274 -- It is important to note that any 3rd party maintenance should be reviewed
275 -- before placing in this procedure.
276
277 -- Access Status:
278 -- Internal Table Handler Use Only.
279
280 -- {End Of Comments}
281 -- ---------------------------------------------------------------------------+
282 Procedure pre_insert(p_rec in out nocopy per_apr_shd.g_rec_type) is
283
284 l_proc varchar2(72) := g_package||'pre_insert';
285 l_exists varchar2(1);
286
287 Cursor C_Sel1 is select per_appraisals_s.nextval from sys.dual;
288 --
289 Cursor C_Sel2 is
290 Select null
291 from per_appraisals
292 where appraisal_id =
293 per_apr_ins.g_appraisal_id_i;
294 --
295 Begin
296 hr_utility.set_location('Entering:'||l_proc, 5);
297
298
299 -- Select the next sequence number
300 If (per_apr_ins.g_appraisal_id_i is not null)
301 then
302 --
303 -- Verify registered primary key values not already in use
304 --
305 Open C_Sel2;
306 Fetch C_Sel2 into l_exists;
307 If C_Sel2%found Then
308 Close C_Sel2;
309 --
310 -- The primary key values are already in use.
311 --
312 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
313 fnd_message.set_token('TABLE_NAME','PER_APPRAISALS');
314 fnd_message.raise_error;
315 End If;
316 Close C_Sel2;
317 --
318 -- Use registered key values and clear globals
319 --
320 p_rec.appraisal_id := per_apr_ins.g_appraisal_id_i;
321 per_apr_ins.g_appraisal_id_i := null;
322 Else
323
324 Open C_Sel1;
325 Fetch C_Sel1 Into p_rec.appraisal_id;
326 Close C_Sel1;
327
328 End If;
329 hr_utility.set_location(' Leaving:'||l_proc, 10);
330 End pre_insert;
331
332 -- ---------------------------------------------------------------------------+
333 -- |-----------------------------< post_insert >------------------------------|
334 -- ---------------------------------------------------------------------------+
335 -- {Start Of Comments}
336
337 -- Description:
338 -- This private procedure contains any processing which is required after the
339 -- insert dml.
340
341 -- Pre Conditions:
342 -- This is an internal procedure which is called from the ins procedure.
343
344 -- In Parameters:
345 -- A Pl/Sql record structre.
346
347 -- Post Success:
348 -- Processing continues.
349
350 -- Post Failure:
351 -- If an error has occurred, an error message and exception will be raised
352 -- but not handled.
353
354 -- Developer Implementation Notes:
355 -- Any post-processing required after the insert dml is issued should be
356 -- coded within this procedure. It is important to note that any 3rd party
357 -- maintenance should be reviewed before placing in this procedure.
358
359 -- Access Status:
360 -- Internal Table Handler Use Only.
361
362 -- {End Of Comments}
363 -- ---------------------------------------------------------------------------+
364 Procedure post_insert(p_rec in per_apr_shd.g_rec_type) is
365
366 l_proc varchar2(72) := g_package||'post_insert';
367
368 Begin
369 hr_utility.set_location('Entering:'||l_proc, 5);
370
371 -- This is a hook point and the user hook for post_insert is called here.
372
373 begin
374 per_apr_rki.after_insert (
375 p_appraisal_id => p_rec.appraisal_id ,
376 p_business_group_id => p_rec.business_group_id ,
377 p_appraisal_template_id => p_rec.appraisal_template_id ,
378 p_appraisee_person_id => p_rec.appraisee_person_id ,
379 p_appraiser_person_id => p_rec.appraiser_person_id ,
380 p_appraisal_date => p_rec.appraisal_date ,
381 p_group_date => p_rec.group_date ,
382 p_group_initiator_id => p_rec.group_initiator_id ,
383 p_appraisal_period_end_date => p_rec.appraisal_period_end_date ,
384 p_appraisal_period_start_date => p_rec.appraisal_period_start_date ,
385 p_type => p_rec.type ,
386 p_next_appraisal_date => p_rec.next_appraisal_date ,
387 p_status => p_rec.status ,
388 p_comments => p_rec.comments ,
389 p_overall_performance_level_id => p_rec.overall_performance_level_id ,
390 p_open => p_rec.open ,
391 p_attribute_category => p_rec.attribute_category ,
392 p_attribute1 => p_rec.attribute1 ,
393 p_attribute2 => p_rec.attribute2 ,
394 p_attribute3 => p_rec.attribute3 ,
395 p_attribute4 => p_rec.attribute4 ,
396 p_attribute5 => p_rec.attribute5 ,
397 p_attribute6 => p_rec.attribute6 ,
398 p_attribute7 => p_rec.attribute7 ,
399 p_attribute8 => p_rec.attribute8 ,
400 p_attribute9 => p_rec.attribute9 ,
401 p_attribute10 => p_rec.attribute10 ,
402 p_attribute11 => p_rec.attribute11 ,
403 p_attribute12 => p_rec.attribute12 ,
404 p_attribute13 => p_rec.attribute13 ,
405 p_attribute14 => p_rec.attribute14 ,
406 p_attribute15 => p_rec.attribute15 ,
407 p_attribute16 => p_rec.attribute16 ,
408 p_attribute17 => p_rec.attribute17 ,
409 p_attribute18 => p_rec.attribute18 ,
410 p_attribute19 => p_rec.attribute19 ,
411 p_attribute20 => p_rec.attribute20 ,
412 p_object_version_number => p_rec.object_version_number,
413 p_system_type => p_rec.system_type ,
414 p_system_params => p_rec.system_params,
415 p_appraisee_access => p_rec.appraisee_access ,
416 p_main_appraiser_id => p_rec.main_appraiser_id ,
417 p_assignment_id => p_rec.assignment_id ,
418 p_assignment_start_date => p_rec.assignment_start_date ,
419 p_asg_business_group_id => p_rec.assignment_business_group_id ,
420 p_assignment_organization_id => p_rec.assignment_organization_id ,
421 p_assignment_job_id => p_rec.assignment_job_id ,
422 p_assignment_position_id => p_rec.assignment_position_id ,
423 p_assignment_grade_id => p_rec.assignment_grade_id,
424 p_appraisal_system_status => p_rec.appraisal_system_status,
425 p_potential_readiness_level => p_rec.potential_readiness_level,
426 p_potential_short_term_workopp => p_rec.potential_short_term_workopp,
427 p_potential_long_term_workopp => p_rec.potential_long_term_workopp,
428 p_potential_details => p_rec.potential_details,
429 p_event_id => p_rec.event_id,
430 p_show_competency_ratings => p_rec.show_competency_ratings,
431 p_show_objective_ratings => p_rec.show_objective_ratings,
432 p_show_questionnaire_info => p_rec.show_questionnaire_info,
433 p_show_participant_details => p_rec.show_participant_details,
434 p_show_participant_ratings => p_rec.show_participant_ratings,
435 p_show_participant_names => p_rec.show_participant_names,
436 p_show_overall_ratings => p_rec.show_overall_ratings,
437 p_show_overall_comments => p_rec.show_overall_comments,
438 p_update_appraisal => p_rec.update_appraisal,
439 p_provide_overall_feedback => p_rec.provide_overall_feedback,
440 p_appraisee_comments => p_rec.appraisee_comments,
441 p_plan_id => p_rec.plan_id,
442 p_offline_status => p_rec.offline_status,
443 p_retention_potential => p_rec.retention_potential,
444 p_show_participant_comments => p_rec.show_participant_comments -- 8651478 bug fix
445 );
446 exception
447 when hr_api.cannot_find_prog_unit then
448 hr_api.cannot_find_prog_unit_error
449 ( p_module_name => 'PER_APPRAISALS'
450 ,p_hook_type => 'AI'
451 );
452 end;
453 -- End of API User Hook for post_insert.
454
455 hr_utility.set_location(' Leaving:'||l_proc, 10);
456 End post_insert;
457
458 -- ---------------------------------------------------------------------------+
459 -- |---------------------------------< ins >----------------------------------|
460 -- ---------------------------------------------------------------------------+
461 Procedure ins
462 (
463 p_rec in out nocopy per_apr_shd.g_rec_type,
464 p_effective_date in date ,
465 p_validate in boolean default false
466 ) is
467
468 l_proc varchar2(72) := g_package||'ins';
469
470 Begin
471 hr_utility.set_location('Entering:'||l_proc, 5);
472
473 -- Determine if the business process is to be validated.
474
475 If p_validate then
476
477 -- Issue the savepoint.
478
479 SAVEPOINT ins_per_apr;
480 End If;
481
482 -- Call the supporting insert validate operations
483
484 per_apr_bus.insert_validate(p_rec,p_effective_date);
485
486 -- raise any errors
487 hr_multi_message.end_validation_set;
488
489 -- Call the supporting pre-insert operation
490
491 pre_insert(p_rec);
492
493 -- Insert the row
494
495 insert_dml(p_rec);
496
497 -- Call the supporting post-insert operation
498
499 post_insert(p_rec);
500
501 -- raise any errors
502 hr_multi_message.end_validation_set;
503
504 -- If we are validating then raise the Validate_Enabled exception
505
506 If p_validate then
507 Raise HR_Api.Validate_Enabled;
508 End If;
509
510 hr_utility.set_location(' Leaving:'||l_proc, 10);
511 Exception
512 When HR_Api.Validate_Enabled Then
513
514 -- As the Validate_Enabled exception has been raised
515 -- we must rollback to the savepoint
516
517 ROLLBACK TO ins_per_apr;
518 end ins;
519
520 -- ---------------------------------------------------------------------------+
521 -- |---------------------------------< ins >----------------------------------|
522 -- ---------------------------------------------------------------------------+
523 Procedure ins
524 (
525 p_appraisal_id out nocopy number,
526 p_business_group_id in number,
527 p_object_version_number out nocopy number,
528 p_appraisal_template_id in number,
529 p_appraisee_person_id in number,
530 p_appraiser_person_id in number,
531 p_appraisal_date in date default null,
532 p_appraisal_period_end_date in date,
533 p_appraisal_period_start_date in date,
534 p_type in varchar2 default null,
535 p_next_appraisal_date in date default null,
536 p_status in varchar2 default null,
537 p_group_date in date default null,
538 p_group_initiator_id in number default null,
539 p_comments in varchar2 default null,
540 p_overall_performance_level_id in number default null,
541 p_open in varchar2 default 'Y',
542 p_attribute_category in varchar2 default null,
543 p_attribute1 in varchar2 default null,
544 p_attribute2 in varchar2 default null,
545 p_attribute3 in varchar2 default null,
546 p_attribute4 in varchar2 default null,
547 p_attribute5 in varchar2 default null,
548 p_attribute6 in varchar2 default null,
549 p_attribute7 in varchar2 default null,
550 p_attribute8 in varchar2 default null,
551 p_attribute9 in varchar2 default null,
552 p_attribute10 in varchar2 default null,
553 p_attribute11 in varchar2 default null,
554 p_attribute12 in varchar2 default null,
555 p_attribute13 in varchar2 default null,
556 p_attribute14 in varchar2 default null,
557 p_attribute15 in varchar2 default null,
558 p_attribute16 in varchar2 default null,
559 p_attribute17 in varchar2 default null,
560 p_attribute18 in varchar2 default null,
561 p_attribute19 in varchar2 default null,
562 p_attribute20 in varchar2 default null,
563 p_effective_date in date,
564 p_system_type in varchar2 default null,
565 p_system_params in varchar2 default null,
566 p_appraisee_access in varchar2 default null,
567 p_main_appraiser_id in number default null,
568 p_assignment_id in number default null,
569 p_assignment_start_date in date default null,
570 p_asg_business_group_id in number default null,
571 p_assignment_organization_id in number default null,
572 p_assignment_job_id in number default null,
573 p_assignment_position_id in number default null,
574 p_assignment_grade_id in number default null,
575 p_appraisal_system_status in varchar2 default null,
576 p_potential_readiness_level in varchar2 default null,
577 p_potential_short_term_workopp in varchar2 default null,
578 p_potential_long_term_workopp in varchar2 default null,
579 p_potential_details in varchar2 default null,
580 p_event_id in number default null,
581 p_show_competency_ratings in varchar2 default null,
582 p_show_objective_ratings in varchar2 default null,
583 p_show_questionnaire_info in varchar2 default null,
584 p_show_participant_details in varchar2 default null,
585 p_show_participant_ratings in varchar2 default null,
586 p_show_participant_names in varchar2 default null,
587 p_show_overall_ratings in varchar2 default null,
588 p_show_overall_comments in varchar2 default null,
589 p_update_appraisal in varchar2 default null,
590 p_provide_overall_feedback in varchar2 default null,
591 p_appraisee_comments in varchar2 default null,
592 p_plan_id in number default null,
593 p_offline_status in varchar2 default null,
594 p_retention_potential in varchar2 default null,
595 p_validate in boolean default false,
596 p_show_participant_comments in varchar2 default null -- 8651478 bug fix
597 ) is
598
599 l_rec per_apr_shd.g_rec_type;
600 l_proc varchar2(72) := g_package||'ins';
601
602 Begin
603 hr_utility.set_location('Entering:'||l_proc, 5);
604
605 -- Call conversion function to turn arguments into the
606 -- p_rec structure.
607
608 l_rec :=
609 per_apr_shd.convert_args
610 (
611 null,
612 p_business_group_id,
613 null,
614 p_appraisal_template_id,
615 p_appraisee_person_id,
616 p_appraiser_person_id,
617 p_appraisal_date,
618 p_appraisal_period_end_date,
619 p_appraisal_period_start_date,
620 p_type,
621 p_next_appraisal_date,
622 p_status,
623 p_group_date,
624 p_group_initiator_id,
625 p_comments,
626 p_overall_performance_level_id,
627 p_open,
628 p_attribute_category,
629 p_attribute1,
630 p_attribute2,
631 p_attribute3,
632 p_attribute4,
633 p_attribute5,
634 p_attribute6,
635 p_attribute7,
636 p_attribute8,
637 p_attribute9,
638 p_attribute10,
639 p_attribute11,
640 p_attribute12,
641 p_attribute13,
642 p_attribute14,
643 p_attribute15,
644 p_attribute16,
645 p_attribute17,
646 p_attribute18,
647 p_attribute19,
648 p_attribute20,
649 p_system_type,
650 p_system_params,
651 p_appraisee_access,
652 p_main_appraiser_id,
653 p_assignment_id,
654 p_assignment_start_date,
655 p_asg_business_group_id,
656 p_assignment_organization_id,
657 p_assignment_job_id,
658 p_assignment_position_id,
659 p_assignment_grade_id,
660 p_appraisal_system_status,
661 p_potential_readiness_level,
662 p_potential_short_term_workopp,
663 p_potential_long_term_workopp,
664 p_potential_details,
665 p_event_id,
666 p_show_competency_ratings,
667 p_show_objective_ratings,
668 p_show_questionnaire_info,
669 p_show_participant_details,
670 p_show_participant_ratings,
671 p_show_participant_names,
672 p_show_overall_ratings,
673 p_show_overall_comments,
674 p_update_appraisal,
675 p_provide_overall_feedback,
676 p_appraisee_comments,
677 p_plan_id,
678 p_offline_status,
679 p_retention_potential,
680 p_show_participant_comments -- 8651478 bug fix
681 );
682
683 -- Having converted the arguments into the per_apr_rec
684 -- plsql record structure we call the corresponding record business process.
685
686 ins(l_rec, p_effective_date,p_validate);
687
688 -- As the primary key argument(s)
689 -- are specified as an OUT's we must set these values.
690
691 p_appraisal_id := l_rec.appraisal_id;
692 p_object_version_number := l_rec.object_version_number;
693
694 hr_utility.set_location(' Leaving:'||l_proc, 10);
695 End ins;
696
697 end per_apr_ins;