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