1 Package Body per_psp_ins as
2 /* $Header: pepsprhi.pkb 115.5 2003/11/17 13:06:07 tpapired noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_psp_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_spinal_point_id_i number default null;
14 --
15 -- ----------------------------------------------------------------------------
16 -- |------------------------< set_base_key_value >----------------------------|
17 -- ----------------------------------------------------------------------------
18 procedure set_base_key_value
19 (p_spinal_point_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_psp_ins.g_spinal_point_id_i := p_spinal_point_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_psp_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_psp_shd.g_api_dml := true; -- Set the api dml status
86 --
87 -- Insert the row into: per_spinal_points
88 --
89 insert into per_spinal_points
90 (spinal_point_id
91 ,business_group_id
92 ,parent_spine_id
93 ,sequence
94 ,spinal_point
95 ,request_id
96 ,program_application_id
97 ,program_id
98 ,program_update_date
99 ,object_version_number
100 ,information_category
101 ,information1
102 ,information2
103 ,information3
104 ,information4
105 ,information5
106 ,information6
107 ,information7
108 ,information8
109 ,information9
110 ,information10
111 ,information11
112 ,information12
113 ,information13
114 ,information14
115 ,information15
116 ,information16
117 ,information17
118 ,information18
119 ,information19
120 ,information20
121 ,information21
122 ,information22
123 ,information23
124 ,information24
125 ,information25
126 ,information26
127 ,information27
128 ,information28
129 ,information29
130 ,information30
131 )
132 Values
133 (p_rec.spinal_point_id
134 ,p_rec.business_group_id
135 ,p_rec.parent_spine_id
136 ,p_rec.sequence
137 ,p_rec.spinal_point
138 ,p_rec.request_id
139 ,p_rec.program_application_id
140 ,p_rec.program_id
141 ,p_rec.program_update_date
142 ,p_rec.object_version_number
143 ,p_rec.information_category
144 ,p_rec.information1
145 ,p_rec.information2
146 ,p_rec.information3
147 ,p_rec.information4
148 ,p_rec.information5
149 ,p_rec.information6
150 ,p_rec.information7
151 ,p_rec.information8
152 ,p_rec.information9
153 ,p_rec.information10
154 ,p_rec.information11
155 ,p_rec.information12
156 ,p_rec.information13
157 ,p_rec.information14
158 ,p_rec.information15
159 ,p_rec.information16
160 ,p_rec.information17
161 ,p_rec.information18
162 ,p_rec.information19
163 ,p_rec.information20
164 ,p_rec.information21
165 ,p_rec.information22
166 ,p_rec.information23
167 ,p_rec.information24
168 ,p_rec.information25
169 ,p_rec.information26
170 ,p_rec.information27
171 ,p_rec.information28
172 ,p_rec.information29
173 ,p_rec.information30
174 );
175 --
176 per_psp_shd.g_api_dml := false; -- Unset the api dml status
177 --
178 hr_utility.set_location(' Leaving:'||l_proc, 10);
179 Exception
180 When hr_api.check_integrity_violated Then
181 -- A check constraint has been violated
182 per_psp_shd.g_api_dml := false; -- Unset the api dml status
183 per_psp_shd.constraint_error
184 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
185 When hr_api.parent_integrity_violated Then
186 -- Parent integrity has been violated
187 per_psp_shd.g_api_dml := false; -- Unset the api dml status
188 per_psp_shd.constraint_error
189 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
190 When hr_api.unique_integrity_violated Then
191 -- Unique integrity has been violated
192 per_psp_shd.g_api_dml := false; -- Unset the api dml status
193 per_psp_shd.constraint_error
194 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
195 When Others Then
196 per_psp_shd.g_api_dml := false; -- Unset the api dml status
197 Raise;
198 End insert_dml;
199 --
200 -- ----------------------------------------------------------------------------
201 -- |------------------------------< pre_insert >------------------------------|
202 -- ----------------------------------------------------------------------------
203 -- {Start Of Comments}
204 --
205 -- Description:
206 -- This private procedure contains any processing which is required before
207 -- the insert dml. Presently, if the entity has a corresponding primary
208 -- key which is maintained by an associating sequence, the primary key for
209 -- the entity will be populated with the next sequence value in
210 -- preparation for the insert dml.
211 --
212 -- Prerequisites:
213 -- This is an internal procedure which is called from the ins procedure.
214 --
215 -- In Parameters:
216 -- A Pl/Sql record structure.
217 --
218 -- Post Success:
219 -- Processing continues.
220 --
221 -- Post Failure:
222 -- If an error has occurred, an error message and exception will be raised
223 -- but not handled.
224 --
225 -- Developer Implementation Notes:
226 -- Any pre-processing required before the insert dml is issued should be
227 -- coded within this procedure. As stated above, a good example is the
228 -- generation of a primary key number via a corresponding sequence.
229 -- It is important to note that any 3rd party maintenance should be reviewed
230 -- before placing in this procedure.
231 --
232 -- Access Status:
233 -- Internal Row Handler Use Only.
234 --
235 -- {End Of Comments}
236 -- ----------------------------------------------------------------------------
237 Procedure pre_insert
238 (p_rec in out nocopy per_psp_shd.g_rec_type
239 ) is
240 --
241 Cursor C_Sel1 is select per_spinal_points_s.nextval from sys.dual;
242 --
243 Cursor C_Sel2 is
244 Select null
245 from per_spinal_points
246 where spinal_point_id =
247 per_psp_ins.g_spinal_point_id_i;
248 --
249 l_proc varchar2(72) := g_package||'pre_insert';
250 l_exists varchar2(1);
251 --
252 Begin
253 hr_utility.set_location('Entering:'||l_proc, 5);
254 --
255 If (per_psp_ins.g_spinal_point_id_i is not null) Then
256 --
257 -- Verify registered primary key values not already in use
258 --
259 Open C_Sel2;
260 Fetch C_Sel2 into l_exists;
261 If C_Sel2%found Then
262 Close C_Sel2;
263 --
264 -- The primary key values are already in use.
265 --
266 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
267 fnd_message.set_token('TABLE_NAME','per_spinal_points');
268 fnd_message.raise_error;
269 End If;
270 Close C_Sel2;
271 --
272 -- Use registered key values and clear globals
273 --
274 p_rec.spinal_point_id :=
275 per_psp_ins.g_spinal_point_id_i;
276 per_psp_ins.g_spinal_point_id_i := null;
277 Else
278 --
279 -- No registerd key values, so select the next sequence number
280 --
281 --
282 -- Select the next sequence number
283 --
284 Open C_Sel1;
285 Fetch C_Sel1 Into p_rec.spinal_point_id;
286 Close C_Sel1;
287 End If;
288 --
289 hr_utility.set_location(' Leaving:'||l_proc, 10);
290 End pre_insert;
291 --
292 -- ----------------------------------------------------------------------------
293 -- |-----------------------------< post_insert >------------------------------|
294 -- ----------------------------------------------------------------------------
295 -- {Start Of Comments}
296 --
297 -- Description:
298 -- This private procedure contains any processing which is required after
299 -- the insert dml.
300 --
301 -- Prerequisites:
302 -- This is an internal procedure which is called from the ins procedure.
303 --
304 -- In Parameters:
305 -- A Pl/Sql record structre.
306 --
307 -- Post Success:
308 -- Processing continues.
309 --
310 -- Post Failure:
311 -- If an error has occurred, an error message and exception will be raised
312 -- but not handled.
313 --
314 -- Developer Implementation Notes:
315 -- Any post-processing required after the insert dml is issued should be
316 -- coded within this procedure. It is important to note that any 3rd party
317 -- maintenance should be reviewed before placing in this procedure.
318 --
319 -- Access Status:
320 -- Internal Row Handler Use Only.
321 --
322 -- {End Of Comments}
323 -- ----------------------------------------------------------------------------
324 Procedure post_insert
325 (p_effective_date in date
326 ,p_rec in per_psp_shd.g_rec_type
327 ) is
328 --
329 l_proc varchar2(72) := g_package||'post_insert';
330 --
331 Begin
332 hr_utility.set_location('Entering:'||l_proc, 5);
333 begin
334 --
335 per_psp_rki.after_insert
336 (p_effective_date => p_effective_date
337 ,p_spinal_point_id
338 => p_rec.spinal_point_id
339 ,p_business_group_id
340 => p_rec.business_group_id
341 ,p_parent_spine_id
342 => p_rec.parent_spine_id
343 ,p_sequence
344 => p_rec.sequence
345 ,p_spinal_point
346 => p_rec.spinal_point
347 ,p_request_id
348 => p_rec.request_id
349 ,p_program_application_id
350 => p_rec.program_application_id
351 ,p_program_id
352 => p_rec.program_id
353 ,p_program_update_date
354 => p_rec.program_update_date
355 ,p_object_version_number
356 => p_rec.object_version_number
357 ,p_information_category
358 => p_rec.information_category
359 ,p_information1
360 => p_rec.information1
361 ,p_information2
362 => p_rec.information2
363 ,p_information3
364 => p_rec.information3
365 ,p_information4
366 => p_rec.information4
367 ,p_information5
368 => p_rec.information5
369 ,p_information6
370 => p_rec.information6
371 ,p_information7
372 => p_rec.information7
373 ,p_information8
374 => p_rec.information8
375 ,p_information9
376 => p_rec.information9
377 ,p_information10
378 => p_rec.information10
379 ,p_information11
380 => p_rec.information11
381 ,p_information12
382 => p_rec.information12
383 ,p_information13
384 => p_rec.information13
385 ,p_information14
386 => p_rec.information14
387 ,p_information15
388 => p_rec.information15
389 ,p_information16
390 => p_rec.information16
391 ,p_information17
392 => p_rec.information17
393 ,p_information18
394 => p_rec.information18
395 ,p_information19
396 => p_rec.information19
397 ,p_information20
398 => p_rec.information20
399 ,p_information21
400 => p_rec.information21
401 ,p_information22
402 => p_rec.information22
403 ,p_information23
404 => p_rec.information23
405 ,p_information24
406 => p_rec.information24
407 ,p_information25
408 => p_rec.information25
409 ,p_information26
410 => p_rec.information26
411 ,p_information27
412 => p_rec.information27
413 ,p_information28
414 => p_rec.information28
415 ,p_information29
416 => p_rec.information29
417 ,p_information30
418 => p_rec.information30
419 );
420 --
421 exception
422 --
423 when hr_api.cannot_find_prog_unit then
424 --
425 hr_api.cannot_find_prog_unit_error
426 (p_module_name => 'PER_SPINAL_POINTS'
427 ,p_hook_type => 'AI');
428 --
429 end;
430 --
431 hr_utility.set_location(' Leaving:'||l_proc, 10);
432 End post_insert;
433 --
434 -- ----------------------------------------------------------------------------
435 -- |---------------------------------< ins >----------------------------------|
439 ,p_rec in out nocopy per_psp_shd.g_rec_type
436 -- ----------------------------------------------------------------------------
437 Procedure ins
438 (p_effective_date in date
440 ) is
441 --
442 l_proc varchar2(72) := g_package||'ins';
443 --
444 Begin
445 hr_utility.set_location('Entering:'||l_proc, 5);
446 --
447 -- Call the supporting insert validate operations
448 --
449 per_psp_bus.insert_validate
450 (p_effective_date
451 ,p_rec
452 );
453 --
454 -- Call to raise any errors on multi-message list
455 hr_multi_message.end_validation_set;
456 --
457 -- Call the supporting pre-insert operation
458 --
459 per_psp_ins.pre_insert(p_rec);
460 --
461 -- Insert the row
462 --
463 per_psp_ins.insert_dml(p_rec);
464 --
465 -- Call the supporting post-insert operation
466 --
467 per_psp_ins.post_insert
468 (p_effective_date
469 ,p_rec
470 );
471 --
472 -- Call to raise any errors on multi-message list
473 hr_multi_message.end_validation_set;
474 --
475 hr_utility.set_location('Leaving:'||l_proc, 20);
476 end ins;
477 --
478 -- ----------------------------------------------------------------------------
479 -- |---------------------------------< ins >----------------------------------|
480 -- ----------------------------------------------------------------------------
481 Procedure ins
482 (p_effective_date in date
483 ,p_business_group_id in number
484 ,p_parent_spine_id in number
485 ,p_sequence in number
486 ,p_spinal_point in varchar2
487 ,p_request_id in number default null
488 ,p_program_application_id in number default null
489 ,p_program_id in number default null
490 ,p_program_update_date in date default null
491 ,p_information_category in varchar2 default null
492 ,p_information1 in varchar2 default null
493 ,p_information2 in varchar2 default null
494 ,p_information3 in varchar2 default null
495 ,p_information4 in varchar2 default null
496 ,p_information5 in varchar2 default null
497 ,p_information6 in varchar2 default null
498 ,p_information7 in varchar2 default null
499 ,p_information8 in varchar2 default null
500 ,p_information9 in varchar2 default null
501 ,p_information10 in varchar2 default null
502 ,p_information11 in varchar2 default null
503 ,p_information12 in varchar2 default null
504 ,p_information13 in varchar2 default null
505 ,p_information14 in varchar2 default null
506 ,p_information15 in varchar2 default null
507 ,p_information16 in varchar2 default null
508 ,p_information17 in varchar2 default null
509 ,p_information18 in varchar2 default null
510 ,p_information19 in varchar2 default null
511 ,p_information20 in varchar2 default null
512 ,p_information21 in varchar2 default null
513 ,p_information22 in varchar2 default null
514 ,p_information23 in varchar2 default null
515 ,p_information24 in varchar2 default null
516 ,p_information25 in varchar2 default null
517 ,p_information26 in varchar2 default null
518 ,p_information27 in varchar2 default null
519 ,p_information28 in varchar2 default null
520 ,p_information29 in varchar2 default null
521 ,p_information30 in varchar2 default null
522 ,p_spinal_point_id out nocopy number
523 ,p_object_version_number out nocopy number
524 ) is
525 --
526 l_rec per_psp_shd.g_rec_type;
527 l_proc varchar2(72) := g_package||'ins';
528 --
529 Begin
530 hr_utility.set_location('Entering:'||l_proc, 5);
531 --
532 -- Call conversion function to turn arguments into the
533 -- p_rec structure.
534 --
535 l_rec :=
536 per_psp_shd.convert_args
537 (null
538 ,p_business_group_id
539 ,p_parent_spine_id
540 ,p_sequence
541 ,p_spinal_point
542 ,p_request_id
543 ,p_program_application_id
544 ,p_program_id
545 ,p_program_update_date
546 ,null
547 ,p_information_category
548 ,p_information1
549 ,p_information2
550 ,p_information3
551 ,p_information4
552 ,p_information5
553 ,p_information6
554 ,p_information7
555 ,p_information8
556 ,p_information9
557 ,p_information10
558 ,p_information11
559 ,p_information12
560 ,p_information13
561 ,p_information14
562 ,p_information15
563 ,p_information16
564 ,p_information17
565 ,p_information18
566 ,p_information19
567 ,p_information20
568 ,p_information21
569 ,p_information22
570 ,p_information23
571 ,p_information24
572 ,p_information25
573 ,p_information26
574 ,p_information27
575 ,p_information28
576 ,p_information29
577 ,p_information30
578 );
579 --
580 -- Having converted the arguments into the per_psp_rec
581 -- plsql record structure we call the corresponding record business process.
582 --
583 per_psp_ins.ins
584 (p_effective_date
585 ,l_rec
586 );
587 --
588 -- As the primary key argument(s)
589 -- are specified as an OUT's we must set these values.
590 --
591 p_spinal_point_id := l_rec.spinal_point_id;
592 p_object_version_number := l_rec.object_version_number;
593 --
594 hr_utility.set_location(' Leaving:'||l_proc, 10);
595 End ins;
596 --
597 end per_psp_ins;
[Home] [Help]