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