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