[Home] [Help]
PACKAGE BODY: APPS.PER_CPN_INS
Source
1 Package Body per_cpn_ins as
2 /* $Header: pecpnrhi.pkb 120.2 2011/09/09 10:51:47 schowdhu ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_cpn_ins.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------------< insert_dml >------------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 -- This procedure controls the actual dml insert logic. The processing of
17 -- this procedure are as follows:
18 -- 1) Initialise the object_version_number to 1 if the object_version_number
19 -- is defined as an attribute for this entity.
20 -- 2) To insert the row into the schema.
21 -- 3) To trap any constraint violations that may have occurred.
22 -- 4) To raise any other errors.
23 --
24 -- Pre Conditions:
25 -- This is an internal private procedure which must be called from the ins
26 -- procedure and must have all mandatory attributes set (except the
27 -- object_version_number which is initialised within this procedure).
28 --
29 -- In Parameters:
30 -- A Pl/Sql record structre.
31 --
32 -- Post Success:
33 -- The specified row will be inserted into the schema.
34 --
35 -- Post Failure:
36 -- If a check, unique or parent integrity constraint violation is raised the
37 -- constraint_error procedure will be called.
38 -- If any other error is reported, the error will be raised
39 --
40 -- Developer Implementation Notes:
41 -- None.
42 --
43 -- Access Status:
44 -- Internal Table Handler Use Only.
45 --
46 -- {End Of Comments}
47 -- ----------------------------------------------------------------------------
48 Procedure insert_dml(p_rec in out nocopy per_cpn_shd.g_rec_type) is
49 --
50 l_proc varchar2(72) := g_package||'insert_dml';
51 --
52 Begin
53 hr_utility.set_location('Entering:'||l_proc, 5);
54 p_rec.object_version_number := 1; -- Initialise the object version
55 --
56 -- Insert the row into: per_competences
57 -- ngundura added column competence_alias
58 insert into per_competences
59 ( competence_id,
60 name,
61 business_group_id,
62 object_version_number,
63 description,
64 date_from,
65 date_to,
66 behavioural_indicator,
67 certification_required,
68 evaluation_method,
69 renewal_period_frequency,
70 renewal_period_units,
71 rating_scale_id,
72 min_level,
73 max_level,
74 attribute_category,
75 attribute1,
76 attribute2,
77 attribute3,
78 attribute4,
79 attribute5,
80 attribute6,
81 attribute7,
82 attribute8,
83 attribute9,
84 attribute10,
85 attribute11,
86 attribute12,
87 attribute13,
88 attribute14,
89 attribute15,
90 attribute16,
91 attribute17,
92 attribute18,
93 attribute19,
94 attribute20,
95 competence_alias,
96 competence_definition_id
97 ,competence_cluster -- BUG3356369
98 ,unit_standard_id
99 ,credit_type
100 ,credits
101 ,level_type
102 ,level_number
103 ,field
104 ,sub_field
105 ,provider
106 ,qa_organization
107 ,information_category
108 ,information1
109 ,information2
110 ,information3
111 ,information4
112 ,information5
113 ,information6
114 ,information7
115 ,information8
116 ,information9
117 ,information10
118 ,information11
119 ,information12
120 ,information13
124 ,information17
121 ,information14
122 ,information15
123 ,information16
125 ,information18
126 ,information19
127 ,information20
128 )
129 Values
130 ( p_rec.competence_id,
131 p_rec.name,
132 p_rec.business_group_id,
133 p_rec.object_version_number,
134 p_rec.description,
135 p_rec.date_from,
136 p_rec.date_to,
137 p_rec.behavioural_indicator,
138 p_rec.certification_required,
139 p_rec.evaluation_method,
140 p_rec.renewal_period_frequency,
141 p_rec.renewal_period_units,
142 p_rec.rating_scale_id,
143 p_rec.min_level,
144 p_rec.max_level,
145 p_rec.attribute_category,
146 p_rec.attribute1,
147 p_rec.attribute2,
148 p_rec.attribute3,
149 p_rec.attribute4,
150 p_rec.attribute5,
151 p_rec.attribute6,
152 p_rec.attribute7,
153 p_rec.attribute8,
154 p_rec.attribute9,
155 p_rec.attribute10,
156 p_rec.attribute11,
157 p_rec.attribute12,
158 p_rec.attribute13,
159 p_rec.attribute14,
160 p_rec.attribute15,
161 p_rec.attribute16,
162 p_rec.attribute17,
163 p_rec.attribute18,
164 p_rec.attribute19,
165 p_rec.attribute20,
166 p_rec.competence_alias,
167 p_rec.competence_definition_id
168 ,p_rec.competence_cluster -- BUG3356369
169 ,p_rec.unit_standard_id
170 ,p_rec.credit_type
171 ,p_rec.credits
172 ,p_rec.level_type
173 ,p_rec.level_number
174 ,p_rec.field
175 ,p_rec.sub_field
176 ,p_rec.provider
177 ,p_rec.qa_organization
178 ,p_rec.information_category
179 ,p_rec.information1
180 ,p_rec.information2
181 ,p_rec.information3
182 ,p_rec.information4
183 ,p_rec.information5
184 ,p_rec.information6
185 ,p_rec.information7
186 ,p_rec.information8
187 ,p_rec.information9
188 ,p_rec.information10
189 ,p_rec.information11
190 ,p_rec.information12
191 ,p_rec.information13
192 ,p_rec.information14
193 ,p_rec.information15
194 ,p_rec.information16
195 ,p_rec.information17
196 ,p_rec.information18
197 ,p_rec.information19
198 ,p_rec.information20
199 );
200 --
201 hr_utility.set_location(' Leaving:'||l_proc, 10);
202 Exception
203 When hr_api.check_integrity_violated Then
204 -- A check constraint has been violated
205 per_cpn_shd.constraint_error
206 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
207 When hr_api.parent_integrity_violated Then
208 -- Parent integrity has been violated
209 per_cpn_shd.constraint_error
210 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
211 When hr_api.unique_integrity_violated Then
212 -- Unique integrity has been violated
213 per_cpn_shd.constraint_error
214 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
215 When Others Then
216 Raise;
217 End insert_dml;
218 --
219 -- ----------------------------------------------------------------------------
220 -- |------------------------------< pre_insert >------------------------------|
221 -- ----------------------------------------------------------------------------
222 -- {Start Of Comments}
223 --
224 -- Description:
225 -- This private procedure contains any processing which is required before
226 -- the insert dml. Presently, if the entity has a corresponding primary
227 -- key which is maintained by an associating sequence, the primary key for
228 -- the entity will be populated with the next sequence value in
229 -- preparation for the insert dml.
230 --
231 -- Pre Conditions:
232 -- This is an internal procedure which is called from the ins procedure.
233 --
234 -- In Parameters:
235 -- A Pl/Sql record structre.
236 --
237 -- Post Success:
238 -- Processing continues.
239 --
240 -- Post Failure:
241 -- If an error has occurred, an error message and exception will be raised
242 -- but not handled.
243 --
244 -- Developer Implementation Notes:
245 -- Any pre-processing required before the insert dml is issued should be
246 -- coded within this procedure. As stated above, a good example is the
247 -- generation of a primary key number via a corresponding sequence.
248 -- It is important to note that any 3rd party maintenance should be reviewed
249 -- before placing in this procedure.
250 --
251 -- Access Status:
252 -- Internal Table Handler Use Only.
253 --
254 -- {End Of Comments}
255 -- ----------------------------------------------------------------------------
256 Procedure pre_insert(p_rec in out nocopy per_cpn_shd.g_rec_type) is
257 --
258 l_proc varchar2(72) := g_package||'pre_insert';
259 --
260 cursor C_Sel1 is select per_competences_s.nextval from sys.dual;
261 --
262 Begin
263 hr_utility.set_location('Entering:'||l_proc, 5);
264 --
265 Open C_Sel1;
266 Fetch C_Sel1 Into p_rec.competence_id;
267 Close C_Sel1;
268 --
269 hr_utility.set_location(' Leaving:'||l_proc, 10);
270 End pre_insert;
271 --
272 -- ----------------------------------------------------------------------------
273 -- |-----------------------------< post_insert >------------------------------|
277 -- Description:
274 -- ----------------------------------------------------------------------------
275 -- {Start Of Comments}
276 --
278 -- This private procedure contains any processing which is required after the
279 -- insert dml.
280 --
281 -- Pre Conditions:
282 -- This is an internal procedure which is called from the ins procedure.
283 --
284 -- In Parameters:
285 -- A Pl/Sql record structre.
286 --
287 -- Post Success:
288 -- Processing continues.
289 --
290 -- Post Failure:
291 -- If an error has occurred, an error message and exception will be raised
292 -- but not handled.
293 --
294 -- Developer Implementation Notes:
295 -- Any post-processing required after the insert dml is issued should be
296 -- coded within this procedure. It is important to note that any 3rd party
297 -- maintenance should be reviewed before placing in this procedure.
298 --
299 -- Access Status:
300 -- Internal Table Handler Use Only.
301 --
302 -- {End Of Comments}
303 -- ----------------------------------------------------------------------------
304 Procedure post_insert(p_rec in per_cpn_shd.g_rec_type) is
305 --
306 l_proc varchar2(72) := g_package||'post_insert';
307 --
308 Begin
309 hr_utility.set_location('Entering:'||l_proc, 5);
310 --
311 -- This is a hook point and the user hook for post_insert is called here.
312 --
313 begin
314 per_cpn_rki.after_insert (
315 p_competence_id => p_rec.competence_id ,
316 p_business_group_id => p_rec.business_group_id ,
317 p_object_version_number => p_rec.object_version_number ,
318 p_name => p_rec.name ,
319 p_description => p_rec.description ,
320 p_date_from => p_rec.date_from ,
321 p_date_to => p_rec.date_to ,
322 p_behavioural_indicator => p_rec.behavioural_indicator ,
323 p_certification_required => p_rec.certification_required ,
324 p_evaluation_method => p_rec.evaluation_method ,
325 p_renewal_period_frequency => p_rec.renewal_period_frequency ,
326 p_renewal_period_units => p_rec.renewal_period_units ,
327 p_max_level => p_rec.max_level ,
328 p_min_level => p_rec.min_level ,
329 p_rating_scale_id => p_rec.rating_scale_id ,
330 p_attribute_category => p_rec.attribute_category ,
331 p_attribute1 => p_rec.attribute1 ,
332 p_attribute2 => p_rec.attribute2 ,
333 p_attribute3 => p_rec.attribute3 ,
334 p_attribute4 => p_rec.attribute4 ,
335 p_attribute5 => p_rec.attribute5 ,
336 p_attribute6 => p_rec.attribute6 ,
337 p_attribute7 => p_rec.attribute7 ,
338 p_attribute8 => p_rec.attribute8 ,
339 p_attribute9 => p_rec.attribute9 ,
340 p_attribute10 => p_rec.attribute10 ,
341 p_attribute11 => p_rec.attribute11 ,
342 p_attribute12 => p_rec.attribute12 ,
343 p_attribute13 => p_rec.attribute13 ,
344 p_attribute14 => p_rec.attribute14 ,
345 p_attribute15 => p_rec.attribute15 ,
346 p_attribute16 => p_rec.attribute16 ,
347 p_attribute17 => p_rec.attribute17 ,
348 p_attribute18 => p_rec.attribute18 ,
349 p_attribute19 => p_rec.attribute19 ,
350 p_attribute20 => p_rec.attribute20 ,
351 p_competence_alias => p_rec.competence_alias,
352 p_competence_definition_id => p_rec.competence_definition_id,
353 p_competence_cluster => p_rec.competence_cluster ,
354 p_unit_standard_id => p_rec.unit_standard_id ,
355 p_credit_type => p_rec.credit_type ,
356 p_credits => p_rec.credits ,
357 p_level_type => p_rec.level_type ,
358 p_level_number => p_rec.level_number ,
359 p_field => p_rec.field ,
360 p_sub_field => p_rec.sub_field ,
361 p_provider => p_rec.provider ,
362 p_qa_organization => p_rec.qa_organization ,
363 p_information_category => p_rec.information_category ,
364 p_information1 => p_rec.information1 ,
365 p_information2 => p_rec.information2 ,
366 p_information3 => p_rec.information3 ,
367 p_information4 => p_rec.information4 ,
368 p_information5 => p_rec.information5 ,
369 p_information6 => p_rec.information6 ,
370 p_information7 => p_rec.information7 ,
371 p_information8 => p_rec.information8 ,
372 p_information9 => p_rec.information9 ,
373 p_information10 => p_rec.information10 ,
374 p_information11 => p_rec.information11 ,
375 p_information12 => p_rec.information12 ,
376 p_information13 => p_rec.information13 ,
377 p_information14 => p_rec.information14 ,
378 p_information15 => p_rec.information15 ,
379 p_information16 => p_rec.information16 ,
380 p_information17 => p_rec.information17 ,
381 p_information18 => p_rec.information18 ,
382 p_information19 => p_rec.information19 ,
383 p_information20 => p_rec.information20
384 );
385 exception
386 when hr_api.cannot_find_prog_unit then
387 hr_api.cannot_find_prog_unit_error
391 end;
388 ( p_module_name => 'PER_COMPETENCES'
389 ,p_hook_type => 'AI'
390 );
392 -- End of API User Hook for post_insert.
393 --
394 hr_utility.set_location(' Leaving:'||l_proc, 10);
395 End post_insert;
396 --
397 -- ----------------------------------------------------------------------------
398 -- |---------------------------------< ins >----------------------------------|
399 -- ----------------------------------------------------------------------------
400 Procedure ins
401 (
402 p_rec in out nocopy per_cpn_shd.g_rec_type,
403 p_effective_date in date default null,
404 p_validate in boolean default false
405 ) is
406 --
407 l_proc varchar2(72) := g_package||'ins';
408 --
409 Begin
410 hr_utility.set_location('Entering:'||l_proc, 5);
411 --
412 -- Determine if the business process is to be validated.
413 --
414 If p_validate then
415 --
416 -- Issue the savepoint.
417 --
418 SAVEPOINT ins_per_cpn;
419 End If;
420 --
421 -- Call the supporting insert validate operations
422 --
423 per_cpn_bus.insert_validate(p_rec,p_effective_date);
424 --
425 -- Call the supporting pre-insert operation
426 --
427 pre_insert(p_rec);
428 --
429 -- Insert the row
430 --
431 insert_dml(p_rec);
432 --
433 -- Call the supporting post-insert operation
434 --
435 post_insert(p_rec);
436 --
437 -- If we are validating then raise the Validate_Enabled exception
438 --
439 If p_validate then
440 Raise HR_Api.Validate_Enabled;
441 End If;
442 --
443 hr_utility.set_location(' Leaving:'||l_proc, 10);
444 Exception
445 When HR_Api.Validate_Enabled Then
446 --
447 -- As the Validate_Enabled exception has been raised
448 -- we must rollback to the savepoint
449 --
450 ROLLBACK TO ins_per_cpn;
451 end ins;
452 --
453 -- ----------------------------------------------------------------------------
454 -- |---------------------------------< ins >----------------------------------|
455 -- ----------------------------------------------------------------------------
456 -- ngundura added competence_alias as a parameter to ins
457 Procedure ins
458 (
459 p_competence_id out nocopy number,
460 p_name in varchar2,
461 p_business_group_id in number default null,
462 p_object_version_number out nocopy number,
463 p_description in varchar2 default null,
464 p_date_from in date,
465 p_date_to in date default null,
466 p_behavioural_indicator in varchar2 default null,
467 p_certification_required in varchar2 default 'N',
468 p_evaluation_method in varchar2 default null,
469 p_renewal_period_frequency in number default null,
470 p_renewal_period_units in varchar2 default null,
471 p_rating_scale_id in number default null,
472 p_attribute_category in varchar2 default null,
473 p_attribute1 in varchar2 default null,
474 p_attribute2 in varchar2 default null,
475 p_attribute3 in varchar2 default null,
476 p_attribute4 in varchar2 default null,
477 p_attribute5 in varchar2 default null,
478 p_attribute6 in varchar2 default null,
479 p_attribute7 in varchar2 default null,
480 p_attribute8 in varchar2 default null,
481 p_attribute9 in varchar2 default null,
482 p_attribute10 in varchar2 default null,
483 p_attribute11 in varchar2 default null,
484 p_attribute12 in varchar2 default null,
485 p_attribute13 in varchar2 default null,
486 p_attribute14 in varchar2 default null,
487 p_attribute15 in varchar2 default null,
488 p_attribute16 in varchar2 default null,
489 p_attribute17 in varchar2 default null,
490 p_attribute18 in varchar2 default null,
491 p_attribute19 in varchar2 default null,
492 p_attribute20 in varchar2 default null,
493 p_effective_date in date ,
494 p_validate in boolean default false,
495 p_competence_alias in varchar2 default null ,
496 p_competence_definition_id in number
497 ,p_competence_cluster in varchar2 default null
498 ,p_unit_standard_id in varchar2 default null
499 ,p_credit_type in varchar2 default null
500 ,p_credits in number default null
501 ,p_level_type in varchar2 default null
502 ,p_level_number in number default null
503 ,p_field in varchar2 default null
504 ,p_sub_field in varchar2 default null
505 ,p_provider in varchar2 default null
506 ,p_qa_organization in varchar2 default null
507 ,p_information_category in varchar2 default null
508 ,p_information1 in varchar2 default null
509 ,p_information2 in varchar2 default null
510 ,p_information3 in varchar2 default null
511 ,p_information4 in varchar2 default null
512 ,p_information5 in varchar2 default null
513 ,p_information6 in varchar2 default null
514 ,p_information7 in varchar2 default null
515 ,p_information8 in varchar2 default null
516 ,p_information9 in varchar2 default null
517 ,p_information10 in varchar2 default null
518 ,p_information11 in varchar2 default null
519 ,p_information12 in varchar2 default null
520 ,p_information13 in varchar2 default null
521 ,p_information14 in varchar2 default null
522 ,p_information15 in varchar2 default null
523 ,p_information16 in varchar2 default null
524 ,p_information17 in varchar2 default null
525 ,p_information18 in varchar2 default null
526 ,p_information19 in varchar2 default null
527 ,p_information20 in varchar2 default null
528 ) is
529 --
530 l_rec per_cpn_shd.g_rec_type;
531 l_proc varchar2(72) := g_package||'ins';
532 --
533 Begin
534 hr_utility.set_location('Entering:'||l_proc, 5);
535 --
536 -- Call conversion function to turn arguments into the
537 -- p_rec structure.
538 -- ngundura added competence_alias as a parameter to convert args
539 l_rec :=
540 per_cpn_shd.convert_args
541 (
542 null,
543 p_name,
544 p_business_group_id,
545 null,
546 p_description,
547 p_date_from,
548 p_date_to,
549 p_behavioural_indicator,
550 p_certification_required,
551 p_evaluation_method,
552 p_renewal_period_frequency,
553 p_renewal_period_units,
554 0,
555 0,
556 p_rating_scale_id,
557 p_attribute_category,
558 p_attribute1,
559 p_attribute2,
560 p_attribute3,
561 p_attribute4,
562 p_attribute5,
563 p_attribute6,
564 p_attribute7,
565 p_attribute8,
566 p_attribute9,
567 p_attribute10,
568 p_attribute11,
569 p_attribute12,
570 p_attribute13,
571 p_attribute14,
572 p_attribute15,
573 p_attribute16,
574 p_attribute17,
575 p_attribute18,
576 p_attribute19,
577 p_attribute20,
578 p_competence_alias,
579 p_competence_definition_id
580 ,p_competence_cluster -- BUG3356369
581 ,p_unit_standard_id
582 ,p_credit_type
583 ,p_credits
584 ,p_level_type
585 ,p_level_number
586 ,p_field
587 ,p_sub_field
588 ,p_provider
589 ,p_qa_organization
590 ,p_information_category
591 ,p_information1
592 ,p_information2
593 ,p_information3
594 ,p_information4
595 ,p_information5
596 ,p_information6
597 ,p_information7
598 ,p_information8
599 ,p_information9
600 ,p_information10
601 ,p_information11
602 ,p_information12
603 ,p_information13
604 ,p_information14
605 ,p_information15
606 ,p_information16
607 ,p_information17
608 ,p_information18
609 ,p_information19
610 ,p_information20
611 );
612 --
613 -- Having converted the arguments into the per_cpn_rec
614 -- plsql record structure we call the corresponding record business process.
615 --
616 ins(l_rec, p_effective_date, p_validate);
617 --
618 -- As the primary key argument(s)
619 -- are specified as an OUT's we must set these values.
620 --
621 p_competence_id := l_rec.competence_id;
622 p_object_version_number := l_rec.object_version_number;
623 --
624 hr_utility.set_location(' Leaving:'||l_proc, 10);
625 End ins;
626 --
627 end per_cpn_ins;