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