[Home] [Help]
PACKAGE BODY: APPS.PER_AST_INS
Source
1 Package Body per_ast_ins as
2 /* $Header: peastrhi.pkb 120.7.12010000.2 2008/10/20 14:11:39 kgowripe ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_ast_ins.'; -- Global package name
9 --
10 --
11 -- The following global variables are only to be used by
12 -- the set_base_key_value and pre_insert procedures.
13 --
14 g_assessment_type_id_i number default null;
15 --
16 --
17 -- ----------------------------------------------------------------------------
18 -- |------------------------< set_base_key_value >----------------------------|
19 -- ----------------------------------------------------------------------------
20 procedure set_base_key_value
21 (p_assessment_type_id in number) is
22 --
23 l_proc varchar2(72) := g_package||'set_base_key_value';
24 --
25 Begin
26 hr_utility.set_location('Entering:'||l_proc, 10);
27 --
28 per_ast_ins.g_assessment_type_id_i := p_assessment_type_id;
29 --
30 hr_utility.set_location(' Leaving:'||l_proc, 20);
31 End set_base_key_value;
32 --
33 --
34 -- ----------------------------------------------------------------------------
35 -- |------------------------------< insert_dml >------------------------------|
36 -- ----------------------------------------------------------------------------
37 -- {Start Of Comments}
38 --
39 -- Description:
40 -- This procedure controls the actual dml insert logic. The processing of
41 -- this procedure are as follows:
42 -- 1) Initialise the object_version_number to 1 if the object_version_number
43 -- is defined as an attribute for this entity.
44 -- 2) To insert the row into the schema.
45 -- 3) To trap any constraint violations that may have occurred.
46 -- 4) To raise any other errors.
47 --
48 -- Pre Conditions:
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 -- If a check, unique or parent integrity constraint violation is raised the
61 -- constraint_error procedure will be called.
62 --
63 -- Developer Implementation Notes:
64 -- None.
65 --
66 -- Access Status:
67 -- Internal Table Handler Use Only.
68 --
69 -- {End Of Comments}
70 -- ----------------------------------------------------------------------------
71 Procedure insert_dml(p_rec in out nocopy per_ast_shd.g_rec_type) is
72 --
73 l_proc varchar2(72) := g_package||'insert_dml';
74 --
75 Begin
76 hr_utility.set_location('Entering:'||l_proc, 5);
77 p_rec.object_version_number := 1; -- Initialise the object version
78 --
79 --
80 -- Insert the row into: per_assessment_types
81 --
82 insert into per_assessment_types
83 ( assessment_type_id,
84 name,
85 business_group_id,
86 description,
87 rating_scale_id,
88 weighting_scale_id,
89 rating_scale_comment,
90 weighting_scale_comment,
91 assessment_classification,
92 display_assessment_comments,
93 date_from,
94 date_to,
95 comments,
96 instructions,
97 weighting_classification,
98 line_score_formula,
99 total_score_formula,
100 object_version_number,
101 attribute_category,
102 attribute1,
103 attribute2,
104 attribute3,
105 attribute4,
106 attribute5,
107 attribute6,
108 attribute7,
109 attribute8,
110 attribute9,
111 attribute10,
112 attribute11,
113 attribute12,
114 attribute13,
115 attribute14,
116 attribute15,
117 attribute16,
118 attribute17,
119 attribute18,
120 attribute19,
121 attribute20,
122 type,
123 line_score_formula_id,
124 default_job_competencies,
125 available_flag
126 )
127 Values
128 ( p_rec.assessment_type_id,
129 p_rec.name,
130 p_rec.business_group_id,
131 p_rec.description,
132 p_rec.rating_scale_id,
133 p_rec.weighting_scale_id,
134 p_rec.rating_scale_comment,
135 p_rec.weighting_scale_comment,
136 p_rec.assessment_classification,
137 p_rec.display_assessment_comments,
138 p_rec.date_from,
139 p_rec.date_to,
140 p_rec.comments,
141 p_rec.instructions,
142 p_rec.weighting_classification,
143 p_rec.line_score_formula,
144 p_rec.total_score_formula,
145 p_rec.object_version_number,
146 p_rec.attribute_category,
147 p_rec.attribute1,
148 p_rec.attribute2,
149 p_rec.attribute3,
150 p_rec.attribute4,
151 p_rec.attribute5,
152 p_rec.attribute6,
153 p_rec.attribute7,
154 p_rec.attribute8,
155 p_rec.attribute9,
156 p_rec.attribute10,
157 p_rec.attribute11,
158 p_rec.attribute12,
159 p_rec.attribute13,
160 p_rec.attribute14,
161 p_rec.attribute15,
162 p_rec.attribute16,
163 p_rec.attribute17,
164 p_rec.attribute18,
165 p_rec.attribute19,
166 p_rec.attribute20,
167 p_rec.type,
168 p_rec.line_score_formula_id,
169 p_rec.default_job_competencies,
170 p_rec.available_flag
171 );
172 --
173 --
174 hr_utility.set_location(' Leaving:'||l_proc, 10);
175 Exception
176 When hr_api.check_integrity_violated Then
177 -- A check constraint has been violated
178 -- Bug#885806
179 -- dbms_output.put_line('A check constraint has been violated insert-dml');
180 hr_utility.trace('A check constraint has been violated insert-dml');
181 -- dbms_output.put_line('p_rec.rating_scale_id is :'|| p_rec.rating_scale_id);
182 hr_utility.trace('p_rec.rating_scale_id is :'|| p_rec.rating_scale_id);
183 per_ast_shd.constraint_error
184 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
185 When hr_api.parent_integrity_violated Then
186 -- Bug#885806
187 -- dbms_output.put_line('Parent integrity has been violated insert-dml');
188 hr_utility.trace('Parent integrity has been violated insert-dml');
189 -- Parent integrity has been violated
190 per_ast_shd.constraint_error
191 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
192 When hr_api.unique_integrity_violated Then
193 -- Bug#885806
194 -- dbms_output.put_line('Unique integrity has been violated insert-dml');
195 hr_utility.trace('Unique integrity has been violated insert-dml');
196 -- Unique integrity has been violated
197 per_ast_shd.constraint_error
198 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
199 When Others Then
200 -- Bug#885806
201 -- dbms_output.put_line('Something else has been violated insert-dml');
202 hr_utility.trace('Something else has been violated insert-dml');
203 Raise;
204 End insert_dml;
205 --
206 -- ----------------------------------------------------------------------------
207 -- |------------------------------< pre_insert >------------------------------|
208 -- ----------------------------------------------------------------------------
209 -- {Start Of Comments}
210 --
211 -- Description:
212 -- This private procedure contains any processing which is required before
213 -- the insert dml. Presently, if the entity has a corresponding primary
214 -- key which is maintained by an associating sequence, the primary key for
215 -- the entity will be populated with the next sequence value in
216 -- preparation for the insert dml.
217 --
218 -- Pre Conditions:
219 -- This is an internal procedure which is called from the ins procedure.
220 --
221 -- In Parameters:
222 -- A Pl/Sql record structre.
223 --
224 -- Post Success:
225 -- Processing continues.
226 --
227 -- Post Failure:
228 -- If an error has occurred, an error message and exception will be raised
229 -- but not handled.
230 --
231 -- Developer Implementation Notes:
232 -- Any pre-processing required before the insert dml is issued should be
233 -- coded within this procedure. As stated above, a good example is the
234 -- generation of a primary key number via a corresponding sequence.
235 -- It is important to note that any 3rd party maintenance should be reviewed
236 -- before placing in this procedure.
237 --
238 -- Access Status:
239 -- Internal Table Handler Use Only.
240 --
241 -- {End Of Comments}
242 -- ----------------------------------------------------------------------------
243 Procedure pre_insert(p_rec in out nocopy per_ast_shd.g_rec_type) is
244 --
245 l_proc varchar2(72) := g_package||'pre_insert';
246 l_exists varchar2(1);
247 --
248 Cursor C_Sel1 is select per_assessment_types_s.nextval from sys.dual;
249 Cursor C_Sel2 is
250 select null from per_assessment_types
251 where assessment_type_id = per_ast_ins.g_assessment_type_id_i;
252
253 --
254 --
255 Begin
256 hr_utility.set_location('Entering:'||l_proc, 5);
257 --
258 -- Select the next sequence number
259 --
260 if ( per_ast_ins.g_assessment_type_id_i is not null ) then
261
262 Open C_Sel2;
263 Fetch C_Sel2 Into l_exists;
264 if C_Sel2%Found then
265 Close C_Sel2;
266 --
267 -- The primary key values are already in use.
268 --
269 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
270 fnd_message.set_token('TABLE_NAME','PER_ASSESSMENT_TYPES');
271 fnd_message.raise_error;
272 End If;
273 Close C_Sel2;
274 --
275 -- Use registered key values and clear globals
276 --
277 p_rec.assessment_type_id := per_ast_ins.g_assessment_type_id_i;
278 per_ast_ins.g_assessment_type_id_i := null;
279
280 Else
281 Open C_Sel1;
282 Fetch C_Sel1 Into p_rec.assessment_type_id;
283 Close C_Sel1;
284 end if;
285 --
286 --
287 hr_utility.set_location(' Leaving:'||l_proc, 10);
288 --
289 End pre_insert;
290 --
291 --
292 -- ----------------------------------------------------------------------------
293 -- |-----------------------------< post_insert >------------------------------|
294 -- ----------------------------------------------------------------------------
295 -- {Start Of Comments}
296 --
297 -- Description:
298 -- This private procedure contains any processing which is required after the
299 -- insert dml.
300 --
301 -- Pre Conditions:
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 Table Handler Use Only.
321 --
322 -- {End Of Comments}
323 -- ----------------------------------------------------------------------------
324 Procedure post_insert(p_rec in per_ast_shd.g_rec_type) is
325 --
326 l_proc varchar2(72) := g_package||'post_insert';
327 --
328 Begin
329 hr_utility.set_location('Entering:'||l_proc, 5);
330 --
331 -- This is a hook point and the user hook for post_insert is called here.
332 --
333 begin
334 per_ast_rki.after_insert (
335 p_assessment_type_id => p_rec.assessment_type_id ,
336 p_business_group_id => p_rec.business_group_id ,
337 p_object_version_number => p_rec.object_version_number ,
338 p_name => p_rec.name ,
339 p_date_to => p_rec.date_to ,
340 p_date_from => p_rec.date_from ,
341 p_assessment_classification => p_rec.assessment_classification ,
342 p_display_assessment_comments => p_rec.display_assessment_comments ,
343 p_description => p_rec.description ,
344 p_rating_scale_comment => p_rec.rating_scale_comment ,
345 p_weighting_scale_comment => p_rec.weighting_scale_comment ,
346 p_comments => p_rec.comments ,
347 p_instructions => p_rec.instructions ,
348 p_line_score_formula => p_rec.line_score_formula ,
349 p_total_score_formula => p_rec.total_score_formula ,
350 p_weighting_classification => p_rec.weighting_classification ,
351 p_rating_scale_id => p_rec.rating_scale_id ,
352 p_weighting_scale_id => p_rec.weighting_scale_id ,
353 p_attribute_category => p_rec.attribute_category ,
354 p_attribute1 => p_rec.attribute1 ,
355 p_attribute2 => p_rec.attribute2 ,
356 p_attribute3 => p_rec.attribute3 ,
357 p_attribute4 => p_rec.attribute4 ,
358 p_attribute5 => p_rec.attribute5 ,
359 p_attribute6 => p_rec.attribute6 ,
360 p_attribute7 => p_rec.attribute7 ,
361 p_attribute8 => p_rec.attribute8 ,
362 p_attribute9 => p_rec.attribute9 ,
363 p_attribute10 => p_rec.attribute10 ,
364 p_attribute11 => p_rec.attribute11 ,
365 p_attribute12 => p_rec.attribute12 ,
366 p_attribute13 => p_rec.attribute13 ,
367 p_attribute14 => p_rec.attribute14 ,
368 p_attribute15 => p_rec.attribute15 ,
369 p_attribute16 => p_rec.attribute16 ,
370 p_attribute17 => p_rec.attribute17 ,
371 p_attribute18 => p_rec.attribute18 ,
372 p_attribute19 => p_rec.attribute19 ,
373 p_attribute20 => p_rec.attribute20 ,
374 p_type => p_rec.type,
375 p_line_score_formula_id => p_rec.line_score_formula_id,
376 p_default_job_competencies => p_rec.default_job_competencies,
377 p_available_flag => p_rec.available_flag
378 );
379 exception
380 when hr_api.cannot_find_prog_unit then
381 hr_api.cannot_find_prog_unit_error
382 ( p_module_name => 'PER_ASSESSMENT_TYPES'
383 ,p_hook_type => 'AI'
384 );
385 end;
386 -- End of API User Hook for post_insert.
387 --
388 hr_utility.set_location(' Leaving:'||l_proc, 10);
389 End post_insert;
390 --
391 -- ----------------------------------------------------------------------------
392 -- |---------------------------------< ins >----------------------------------|
393 -- ----------------------------------------------------------------------------
394 Procedure ins
395 (p_rec in out nocopy per_ast_shd.g_rec_type
396 ,p_validate in boolean default false
397 ,p_effective_date in date
398 ) is
399 --
400 l_proc varchar2(72) := g_package||'ins';
401 --
402 Begin
403 hr_utility.set_location('Entering:'||l_proc, 5);
404 --
405 -- Determine if the business process is to be validated.
406 --
407 If p_validate then
408 --
409 -- Issue the savepoint.
410 --
411 SAVEPOINT ins_per_ast;
412 End If;
413 --
414 -- Call the supporting insert validate operations
415 --
416 hr_utility.set_location(' insert_validate called :'||l_proc, 11);
417 per_ast_bus.insert_validate(p_rec
418 ,p_effective_date);
419 --
420 hr_utility.set_location(' insert_validate finished :'||l_proc, 11);
421 --
422 -- Call the supporting pre-insert operation
423 --
424 -- Bug#885806
425 -- dbms_output.put_line('about to do pre_insert');
426 hr_utility.trace('about to do pre_insert');
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_ast;
451 end ins;
452 --
453 -- ----------------------------------------------------------------------------
454 -- |---------------------------------< ins >----------------------------------|
455 -- ----------------------------------------------------------------------------
456 Procedure ins
457 (
458 p_assessment_type_id out nocopy number,
459 p_name in varchar2,
460 p_business_group_id in number default null,
461 p_description in varchar2 default null,
462 p_rating_scale_id in number default null,
463 p_weighting_scale_id in number default null,
464 p_rating_scale_comment in varchar2 default null,
465 p_weighting_scale_comment in varchar2 default null,
466 p_assessment_classification in varchar2,
467 p_display_assessment_comments in varchar2 default 'Y',
468 p_date_from in date,
469 p_date_to in date,
470 p_comments in varchar2 default null,
471 p_instructions in varchar2 default null,
472 p_weighting_classification in varchar2 default null,
473 p_line_score_formula in varchar2 default null,
474 p_total_score_formula in varchar2 default null,
475 p_object_version_number out nocopy number,
476 p_attribute_category in varchar2 default null,
477 p_attribute1 in varchar2 default null,
478 p_attribute2 in varchar2 default null,
479 p_attribute3 in varchar2 default null,
480 p_attribute4 in varchar2 default null,
481 p_attribute5 in varchar2 default null,
482 p_attribute6 in varchar2 default null,
483 p_attribute7 in varchar2 default null,
484 p_attribute8 in varchar2 default null,
485 p_attribute9 in varchar2 default null,
486 p_attribute10 in varchar2 default null,
487 p_attribute11 in varchar2 default null,
488 p_attribute12 in varchar2 default null,
489 p_attribute13 in varchar2 default null,
490 p_attribute14 in varchar2 default null,
491 p_attribute15 in varchar2 default null,
492 p_attribute16 in varchar2 default null,
493 p_attribute17 in varchar2 default null,
494 p_attribute18 in varchar2 default null,
495 p_attribute19 in varchar2 default null,
496 p_attribute20 in varchar2 default null,
497 p_type in varchar2 default null,
498 p_line_score_formula_id in number default null,
499 p_default_job_competencies in varchar2 default null,
500 p_available_flag in varchar2 default null,
501 p_validate in boolean default false,
502 p_effective_date in date
503 ) is
504 --
505 l_rec per_ast_shd.g_rec_type;
506 l_proc varchar2(72) := g_package||'ins';
507 --
508 Begin
509 hr_utility.set_location('Entering:'||l_proc, 5);
510 --
511 -- Call conversion function to turn arguments into the
512 -- p_rec structure.
513 --
514 l_rec :=
515 per_ast_shd.convert_args
516 (
517 null,
518 p_name,
519 p_business_group_id,
520 p_description,
521 p_rating_scale_id,
522 p_weighting_scale_id,
523 p_rating_scale_comment,
524 p_weighting_scale_comment,
525 p_assessment_classification,
526 p_display_assessment_comments,
527 p_date_from,
528 p_date_to,
529 p_comments,
530 p_instructions,
531 p_weighting_classification,
532 p_line_score_formula,
533 p_total_score_formula,
534 null,
535 p_attribute_category,
536 p_attribute1,
537 p_attribute2,
538 p_attribute3,
539 p_attribute4,
540 p_attribute5,
541 p_attribute6,
542 p_attribute7,
543 p_attribute8,
544 p_attribute9,
545 p_attribute10,
546 p_attribute11,
547 p_attribute12,
548 p_attribute13,
549 p_attribute14,
550 p_attribute15,
551 p_attribute16,
552 p_attribute17,
553 p_attribute18,
554 p_attribute19,
555 p_attribute20,
556 p_type,
557 p_line_score_formula_id,
558 p_default_job_competencies,
559 p_available_flag
560 );
561 --
562 -- Having converted the arguments into the per_ast_rec
563 -- plsql record structure we call the corresponding record business process.
564 --
565 ins(l_rec
566 ,p_validate
567 ,p_effective_date);
568 --
569 -- As the primary key argument(s)
570 -- are specified as an OUT's we must set these values.
571 --
572 p_assessment_type_id := l_rec.assessment_type_id;
573 p_object_version_number := l_rec.object_version_number;
574 --
575 hr_utility.set_location(' Leaving:'||l_proc, 10);
576 End ins;
577 --
578 end per_ast_ins;