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