1 Package Body per_prt_ins as
2 /* $Header: peprtrhi.pkb 120.2 2006/05/03 18:37:44 kandra noship $ */
3
4 -- ---------------------------------------------------------------------------+
5 -- | Private Global Definitions |
6 -- ---------------------------------------------------------------------------+
7
8 g_package varchar2(33) := ' per_prt_ins.'; -- Global package name
9 --
10 g_performance_rating_id_i number default null;
11 --
12 -- ----------------------------------------------------------------------------
13 -- |------------------------< set_base_key_value >----------------------------|
14 -- ----------------------------------------------------------------------------
15
16 procedure set_base_key_value
17 (p_performance_rating_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_prt_ins.g_performance_rating_id_i := p_performance_rating_id;
25 --
26 hr_utility.set_location(' Leaving:'||l_proc, 20);
27 End set_base_key_value;
28 --
29 --
30 -- ---------------------------------------------------------------------------+
31 -- |------------------------------< insert_dml >------------------------------|
32 -- ---------------------------------------------------------------------------+
33 -- {Start Of Comments}
34
35 -- Description:
36 -- This procedure controls the actual dml insert logic. The processing of
37 -- this procedure are as follows:
38 -- 1) Initialise the object_version_number to 1 if the object_version_number
39 -- is defined as an attribute for this entity.
40 -- 2) To set and unset the g_api_dml status as required (as we are about to
41 -- perform dml).
42 -- 3) To insert the row into the schema.
43 -- 4) To trap any constraint violations that may have occurred.
44 -- 5) 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 -- On the insert dml failure it is important to note that we always reset the
59 -- g_api_dml status to false.
60 -- If a check, unique or parent integrity constraint violation is raised the
61 -- constraint_error procedure will be called.
62 -- If any other error is reported, the error will be raised after the
63 -- g_api_dml status is reset.
64
65 -- Developer Implementation Notes:
66 -- None.
67
68 -- Access Status:
69 -- Internal Table Handler Use Only.
70
71 -- {End Of Comments}
72 -- ---------------------------------------------------------------------------+
73 Procedure insert_dml(p_rec in out nocopy per_prt_shd.g_rec_type) is
74
75 l_proc varchar2(72) := g_package||'insert_dml';
76
77 Begin
78 hr_utility.set_location('Entering:'||l_proc, 5);
79 p_rec.object_version_number := 1; -- Initialise the object version
80
81 -- Insert the row into: per_performance_ratings
82
83 insert into per_performance_ratings
84 ( performance_rating_id,
85 person_id,
86 objective_id,
87 object_version_number,
88 appraisal_id,
89 performance_level_id,
90 comments,
91 attribute_category,
92 attribute1,
93 attribute2,
94 attribute3,
95 attribute4,
96 attribute5,
97 attribute6,
98 attribute7,
99 attribute8,
100 attribute9,
101 attribute10,
102 attribute11,
103 attribute12,
104 attribute13,
105 attribute14,
106 attribute15,
107 attribute16,
108 attribute17,
109 attribute18,
110 attribute19,
111 attribute20,
112 appr_line_score
113 )
114 Values
115 ( p_rec.performance_rating_id,
116 p_rec.person_id,
117 p_rec.objective_id,
118 p_rec.object_version_number,
119 p_rec.appraisal_id,
120 p_rec.performance_level_id,
121 p_rec.comments,
122 p_rec.attribute_category,
123 p_rec.attribute1,
124 p_rec.attribute2,
125 p_rec.attribute3,
126 p_rec.attribute4,
127 p_rec.attribute5,
128 p_rec.attribute6,
129 p_rec.attribute7,
130 p_rec.attribute8,
131 p_rec.attribute9,
132 p_rec.attribute10,
133 p_rec.attribute11,
134 p_rec.attribute12,
135 p_rec.attribute13,
136 p_rec.attribute14,
137 p_rec.attribute15,
138 p_rec.attribute16,
139 p_rec.attribute17,
140 p_rec.attribute18,
141 p_rec.attribute19,
142 p_rec.attribute20,
143 p_rec.appr_line_score
144 );
145
146 hr_utility.set_location(' Leaving:'||l_proc, 10);
147 Exception
148 When hr_api.check_integrity_violated Then
149 -- A check constraint has been violated
150 per_prt_shd.constraint_error
151 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
152 When hr_api.parent_integrity_violated Then
153 -- Parent integrity has been violated
154 per_prt_shd.constraint_error
155 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
156 When hr_api.unique_integrity_violated Then
157 -- Unique integrity has been violated
158 per_prt_shd.constraint_error
159 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
160 When Others Then
161 Raise;
162 End insert_dml;
163
164 -- ---------------------------------------------------------------------------+
165 -- |------------------------------< pre_insert >------------------------------|
166 -- ---------------------------------------------------------------------------+
167 -- {Start Of Comments}
168
169 -- Description:
170 -- This private procedure contains any processing which is required before
171 -- the insert dml. Presently, if the entity has a corresponding primary
172 -- key which is maintained by an associating sequence, the primary key for
173 -- the entity will be populated with the next sequence value in
174 -- preparation for the insert dml.
175
176 -- Pre Conditions:
177 -- This is an internal procedure which is called from the ins procedure.
178
179 -- In Parameters:
180 -- A Pl/Sql record structre.
181
182 -- Post Success:
183 -- Processing continues.
184
185 -- Post Failure:
186 -- If an error has occurred, an error message and exception will be raised
187 -- but not handled.
188
189 -- Developer Implementation Notes:
190 -- Any pre-processing required before the insert dml is issued should be
191 -- coded within this procedure. As stated above, a good example is the
192 -- generation of a primary key number via a corresponding sequence.
193 -- It is important to note that any 3rd party maintenance should be reviewed
194 -- before placing in this procedure.
195
196 -- Access Status:
197 -- Internal Table Handler Use Only.
198
199 -- {End Of Comments}
200 -- ---------------------------------------------------------------------------+
201 Procedure pre_insert(p_rec in out nocopy per_prt_shd.g_rec_type) is
202
203 l_proc varchar2(72) := g_package||'pre_insert';
204 l_exists varchar2(1);
205 --
206 Cursor C_Sel1 is select per_performance_ratings_s.nextval from sys.dual;
207 --
208 --
209 Cursor C_Sel2 is
210 Select null
211 from per_performance_ratings
212 where performance_rating_id =
213 per_prt_ins.g_performance_rating_id_i;
214 --
215
216 Begin
217 hr_utility.set_location('Entering:'||l_proc, 5);
218
219
220 -- Select the next sequence number
221 IF (per_prt_ins.g_performance_rating_id_i is not null)
222 then
223 --
224 -- Verify registered primary key values not already in use
225 --
226 Open C_Sel2;
227 Fetch C_Sel2 into l_exists;
228 If C_Sel2%found Then
229 Close C_Sel2;
230 --
231 -- The primary key values are already in use.
232 --
233 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
234 fnd_message.set_token('TABLE_NAME','PER_PERFORMANCE_RATINGS');
235 fnd_message.raise_error;
236 End If;
237 Close C_Sel2;
238 --
239 -- Use registered key values and clear globals
240 --
241 p_rec.performance_rating_id := per_prt_ins.g_performance_rating_id_i;
242 per_prt_ins.g_performance_rating_id_i := null;
243 Else
244
245 Open C_Sel1;
246 Fetch C_Sel1 Into p_rec.performance_rating_id;
247 Close C_Sel1;
248 END IF;
249
250 hr_utility.set_location(' Leaving:'||l_proc, 10);
251 End pre_insert;
252
253 -- ---------------------------------------------------------------------------+
254 -- |-----------------------------< post_insert >------------------------------|
255 -- ---------------------------------------------------------------------------+
256 -- {Start Of Comments}
257
258 -- Description:
259 -- This private procedure contains any processing which is required after the
260 -- insert dml.
261
262 -- Pre Conditions:
263 -- This is an internal procedure which is called from the ins procedure.
264
265 -- In Parameters:
266 -- A Pl/Sql record structre.
267
268 -- Post Success:
269 -- Processing continues.
270
271 -- Post Failure:
272 -- If an error has occurred, an error message and exception will be raised
273 -- but not handled.
274
275 -- Developer Implementation Notes:
276 -- Any post-processing required after the insert dml is issued should be
277 -- coded within this procedure. It is important to note that any 3rd party
278 -- maintenance should be reviewed before placing in this procedure.
279
280 -- Access Status:
281 -- Internal Table Handler Use Only.
282
283 -- {End Of Comments}
284 -- ---------------------------------------------------------------------------+
285 Procedure post_insert(p_rec in per_prt_shd.g_rec_type) is
286
287 l_proc varchar2(72) := g_package||'post_insert';
288
289 Begin
290 hr_utility.set_location('Entering:'||l_proc, 5);
291
292 -- This is a hook point and the user hook for post_insert is called here.
293
294 begin
295 per_prt_rki.after_insert (
296 p_performance_rating_id => p_rec.performance_rating_id ,
297 p_person_id => p_rec.person_id,
298 p_objective_id => p_rec.objective_id ,
299 p_object_version_number => p_rec.object_version_number ,
300 p_appraisal_id => p_rec.appraisal_id ,
301 p_performance_level_id => p_rec.performance_level_id ,
302 p_comments => p_rec.comments ,
303 p_attribute_category => p_rec.attribute_category ,
304 p_attribute1 => p_rec.attribute1 ,
305 p_attribute2 => p_rec.attribute2 ,
306 p_attribute3 => p_rec.attribute3 ,
307 p_attribute4 => p_rec.attribute4 ,
308 p_attribute5 => p_rec.attribute5 ,
309 p_attribute6 => p_rec.attribute6 ,
310 p_attribute7 => p_rec.attribute7 ,
311 p_attribute8 => p_rec.attribute8 ,
312 p_attribute9 => p_rec.attribute9 ,
313 p_attribute10 => p_rec.attribute10 ,
314 p_attribute11 => p_rec.attribute11 ,
315 p_attribute12 => p_rec.attribute12 ,
316 p_attribute13 => p_rec.attribute13 ,
317 p_attribute14 => p_rec.attribute14 ,
318 p_attribute15 => p_rec.attribute15 ,
319 p_attribute16 => p_rec.attribute16 ,
320 p_attribute17 => p_rec.attribute17 ,
321 p_attribute18 => p_rec.attribute18 ,
322 p_attribute19 => p_rec.attribute19 ,
323 p_attribute20 => p_rec.attribute20 ,
324 p_appr_line_score => p_rec.appr_line_score);
325 exception
326 when hr_api.cannot_find_prog_unit then
327 hr_api.cannot_find_prog_unit_error
328 ( p_module_name => 'PER_PERFORMANCE_RATINGS'
329 ,p_hook_type => 'AI'
330 );
331 end;
332 -- End of API User Hook for post_insert.
333
334 hr_utility.set_location(' Leaving:'||l_proc, 10);
335 End post_insert;
336
337 -- ---------------------------------------------------------------------------+
338 -- |---------------------------------< ins >----------------------------------|
339 -- ---------------------------------------------------------------------------+
340 Procedure ins
341 (
342 p_rec in out nocopy per_prt_shd.g_rec_type,
343 p_validate in boolean default false,
344 p_effective_date in date
345 ) is
346
347 l_proc varchar2(72) := g_package||'ins';
348
349 Begin
350 hr_utility.set_location('Entering:'||l_proc, 5);
351
352 -- Determine if the business process is to be validated.
353
354 If p_validate then
355
356 -- Issue the savepoint.
357
358 SAVEPOINT ins_per_prt;
359 End If;
360
361 -- Call the supporting insert validate operations
362
363 per_prt_bus.insert_validate(p_rec, p_effective_date);
364
365 --
366 hr_multi_message.end_validation_set;
367 --
368
369 --
370 hr_multi_message.end_validation_set;
371 --
372
373 -- Call the supporting pre-insert operation
374
375 pre_insert(p_rec);
376
377 -- Insert the row
378
379 insert_dml(p_rec);
380
381 -- Call the supporting post-insert operation
382
383 post_insert(p_rec);
384
385 --
386 hr_multi_message.end_validation_set;
387 --
388 --
389 hr_multi_message.end_validation_set;
390 --
391
392 -- If we are validating then raise the Validate_Enabled exception
393
394 If p_validate then
395 Raise HR_Api.Validate_Enabled;
396 End If;
397
398 hr_utility.set_location(' Leaving:'||l_proc, 10);
399 Exception
400 When HR_Api.Validate_Enabled Then
401
402 -- As the Validate_Enabled exception has been raised
403 -- we must rollback to the savepoint
404
405 ROLLBACK TO ins_per_prt;
406 end ins;
407
408 -- ---------------------------------------------------------------------------+
409 -- |---------------------------------< ins >----------------------------------|
410 -- ---------------------------------------------------------------------------+
411 Procedure ins
412 (
413 p_performance_rating_id out nocopy number,
414 p_person_id in number,
415 p_objective_id in number,
416 p_object_version_number out nocopy number,
417 p_appraisal_id in number,
418 p_performance_level_id in number default null,
419 p_comments in varchar2 default null,
420 p_attribute_category in varchar2 default null,
421 p_attribute1 in varchar2 default null,
422 p_attribute2 in varchar2 default null,
423 p_attribute3 in varchar2 default null,
424 p_attribute4 in varchar2 default null,
425 p_attribute5 in varchar2 default null,
426 p_attribute6 in varchar2 default null,
427 p_attribute7 in varchar2 default null,
428 p_attribute8 in varchar2 default null,
429 p_attribute9 in varchar2 default null,
430 p_attribute10 in varchar2 default null,
431 p_attribute11 in varchar2 default null,
432 p_attribute12 in varchar2 default null,
433 p_attribute13 in varchar2 default null,
434 p_attribute14 in varchar2 default null,
435 p_attribute15 in varchar2 default null,
436 p_attribute16 in varchar2 default null,
437 p_attribute17 in varchar2 default null,
438 p_attribute18 in varchar2 default null,
439 p_attribute19 in varchar2 default null,
440 p_attribute20 in varchar2 default null,
441 p_validate in boolean default false,
442 p_effective_date in date,
443 p_appr_line_score in number default null
444 ) is
445
446 l_rec per_prt_shd.g_rec_type;
447 l_proc varchar2(72) := g_package||'ins';
448
449 Begin
450 hr_utility.set_location('Entering:'||l_proc, 5);
451
452 -- Call conversion function to turn arguments into the
453 -- p_rec structure.
454
455 l_rec :=
456 per_prt_shd.convert_args
457 (
458 null,
459 p_person_id,
460 p_objective_id,
461 null,
462 p_appraisal_id,
463 p_performance_level_id,
464 p_comments,
465 p_attribute_category,
466 p_attribute1,
467 p_attribute2,
468 p_attribute3,
469 p_attribute4,
470 p_attribute5,
471 p_attribute6,
472 p_attribute7,
473 p_attribute8,
474 p_attribute9,
475 p_attribute10,
476 p_attribute11,
477 p_attribute12,
478 p_attribute13,
479 p_attribute14,
480 p_attribute15,
481 p_attribute16,
482 p_attribute17,
483 p_attribute18,
484 p_attribute19,
485 p_attribute20,
486 p_appr_line_score
487 );
488
489 -- Having converted the arguments into the per_prt_rec
490 -- plsql record structure we call the corresponding record business process.
491
492 ins(l_rec, p_validate, p_effective_date);
493
494 -- As the primary key argument(s)
495 -- are specified as an OUT's we must set these values.
496
497 p_performance_rating_id := l_rec.performance_rating_id;
498 p_object_version_number := l_rec.object_version_number;
499
500 hr_utility.set_location(' Leaving:'||l_proc, 10);
501 End ins;
502
503 end per_prt_ins;