1 Package Body per_grs_upd as
2 /* $Header: pegrsrhi.pkb 115.3 2002/12/05 17:40:37 pkakar ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_grs_upd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------------< update_dml >------------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 -- This procedure controls the actual dml update logic. The processing of
17 -- this procedure is:
18 -- 1) Increment the object_version_number by 1 if the object_version_number
19 -- is defined as an attribute for this entity.
20 -- 2) To set and unset the g_api_dml status as required (as we are about to
21 -- perform dml).
22 -- 3) To update the specified row in the schema using the primary key in
23 -- the predicates.
24 -- 4) To trap any constraint violations that may have occurred.
25 -- 5) To raise any other errors.
26 --
27 -- Prerequisites:
28 -- This is an internal private procedure which must be called from the upd
29 -- procedure.
30 --
31 -- In Parameters:
32 -- A Pl/Sql record structre.
33 --
34 -- Post Success:
35 -- The specified row will be updated in the schema.
36 --
37 -- Post Failure:
38 -- On the update dml failure it is important to note that we always reset the
39 -- g_api_dml status to false.
40 -- If a check, unique or parent integrity constraint violation is raised the
41 -- constraint_error procedure will be called.
42 -- If any other error is reported, the error will be raised after the
43 -- g_api_dml status is reset.
44 --
45 -- Developer Implementation Notes:
46 -- The update 'set' attribute list should be modified if any of your
47 -- attributes are not updateable.
48 --
49 -- Access Status:
50 -- Internal Row Handler Use Only.
51 --
52 -- {End Of Comments}
53 -- ----------------------------------------------------------------------------
54 Procedure update_dml(p_rec in out nocopy per_grs_shd.g_rec_type) is
55 --
56 l_proc varchar2(72) := g_package||'update_dml';
57 --
58 Begin
59 hr_utility.set_location('Entering:'||l_proc, 5);
60 --
61 -- Increment the object version
62 --
63 p_rec.object_version_number := p_rec.object_version_number + 1;
64 --
65 per_grs_shd.g_api_dml := true; -- Set the api dml status
66 --
67 -- Update the per_cagr_grade_structures Row
68 --
69 update per_cagr_grade_structures
70 set
71 cagr_grade_structure_id = p_rec.cagr_grade_structure_id,
72 collective_agreement_id = p_rec.collective_agreement_id,
73 object_version_number = p_rec.object_version_number,
74 id_flex_num = p_rec.id_flex_num,
75 dynamic_insert_allowed = p_rec.dynamic_insert_allowed,
76 attribute_category = p_rec.attribute_category,
77 attribute1 = p_rec.attribute1,
78 attribute2 = p_rec.attribute2,
79 attribute3 = p_rec.attribute3,
80 attribute4 = p_rec.attribute4,
81 attribute5 = p_rec.attribute5,
82 attribute6 = p_rec.attribute6,
83 attribute7 = p_rec.attribute7,
84 attribute8 = p_rec.attribute8,
85 attribute9 = p_rec.attribute9,
86 attribute10 = p_rec.attribute10,
87 attribute11 = p_rec.attribute11,
88 attribute12 = p_rec.attribute12,
89 attribute13 = p_rec.attribute13,
90 attribute14 = p_rec.attribute14,
91 attribute15 = p_rec.attribute15,
92 attribute16 = p_rec.attribute16,
93 attribute17 = p_rec.attribute17,
94 attribute18 = p_rec.attribute18,
95 attribute19 = p_rec.attribute19,
96 attribute20 = p_rec.attribute20
97 where cagr_grade_structure_id = p_rec.cagr_grade_structure_id;
98 --
99 per_grs_shd.g_api_dml := false; -- Unset the api dml status
100 --
101 hr_utility.set_location(' Leaving:'||l_proc, 10);
102 --
103 Exception
104 When hr_api.check_integrity_violated Then
105 -- A check constraint has been violated
106 per_grs_shd.g_api_dml := false; -- Unset the api dml status
107 per_grs_shd.constraint_error
108 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
109 When hr_api.parent_integrity_violated Then
110 -- Parent integrity has been violated
111 per_grs_shd.g_api_dml := false; -- Unset the api dml status
112 per_grs_shd.constraint_error
113 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
114 When hr_api.unique_integrity_violated Then
115 -- Unique integrity has been violated
116 per_grs_shd.g_api_dml := false; -- Unset the api dml status
117 per_grs_shd.constraint_error
118 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
119 When Others Then
120 per_grs_shd.g_api_dml := false; -- Unset the api dml status
121 Raise;
122 End update_dml;
123 --
124 -- ----------------------------------------------------------------------------
125 -- |------------------------------< pre_update >------------------------------|
126 -- ----------------------------------------------------------------------------
127 -- {Start Of Comments}
128 --
129 -- Description:
130 -- This private procedure contains any processing which is required before
131 -- the update dml.
132 --
133 -- Prerequisites:
134 -- This is an internal procedure which is called from the upd procedure.
135 --
136 -- In Parameters:
137 -- A Pl/Sql record structre.
138 --
139 -- Post Success:
140 -- Processing continues.
141 --
142 -- Post Failure:
143 -- If an error has occurred, an error message and exception will be raised
144 -- but not handled.
145 --
146 -- Developer Implementation Notes:
147 -- Any pre-processing required before the update dml is issued should be
148 -- coded within this procedure. It is important to note that any 3rd party
149 -- maintenance should be reviewed before placing in this procedure.
150 --
151 -- Access Status:
152 -- Internal Row Handler Use Only.
153 --
154 -- {End Of Comments}
155 -- ----------------------------------------------------------------------------
156 Procedure pre_update(p_effective_date in date,
157 p_rec in per_grs_shd.g_rec_type) is
158 --
159 l_proc varchar2(72) := g_package||'pre_update';
160 --
161 Begin
162 hr_utility.set_location('Entering:'||l_proc, 5);
163 --
164 hr_utility.set_location(' Leaving:'||l_proc, 10);
165 End pre_update;
166 --
167 -- ----------------------------------------------------------------------------
168 -- |-----------------------------< post_update >------------------------------|
169 -- ----------------------------------------------------------------------------
170 -- {Start Of Comments}
171 --
172 -- Description:
173 -- This private procedure contains any processing which is required after the
174 -- update dml.
175 --
176 -- Prerequisites:
177 -- This is an internal procedure which is called from the upd procedure.
178 --
179 -- In Parameters:
180 -- A Pl/Sql record structre.
181 --
182 -- Post Success:
183 -- Processing continues.
184 --
185 -- Post Failure:
186 -- If an error has occurred, an error message and exception will be raised
187 -- but not handled.
188 --
189 -- Developer Implementation Notes:
190 -- Any post-processing required after the update dml is issued should be
191 -- coded within this procedure. It is important to note that any 3rd party
192 -- maintenance should be reviewed before placing in this procedure.
193 --
194 -- Access Status:
195 -- Internal Row Handler Use Only.
196 --
197 -- {End Of Comments}
198 -- ----------------------------------------------------------------------------
199 Procedure post_update(p_effective_date in date,
200 p_rec in per_grs_shd.g_rec_type) is
201 --
202 l_proc varchar2(72) := g_package||'post_update';
203 --
204 Begin
205 hr_utility.set_location('Entering:'||l_proc, 5);
206 --
207 --
208 -- Start of API User Hook for post_update.
209 --
210 begin
211 --
212 per_grs_rku.after_update
213 (
214 p_cagr_grade_structure_id =>p_rec.cagr_grade_structure_id
215 ,p_collective_agreement_id =>p_rec.collective_agreement_id
216 ,p_object_version_number =>p_rec.object_version_number
217 ,p_id_flex_num =>p_rec.id_flex_num
218 ,p_dynamic_insert_allowed =>p_rec.dynamic_insert_allowed
219 ,p_attribute_category =>p_rec.attribute_category
220 ,p_attribute1 =>p_rec.attribute1
221 ,p_attribute2 =>p_rec.attribute2
222 ,p_attribute3 =>p_rec.attribute3
223 ,p_attribute4 =>p_rec.attribute4
224 ,p_attribute5 =>p_rec.attribute5
225 ,p_attribute6 =>p_rec.attribute6
226 ,p_attribute7 =>p_rec.attribute7
227 ,p_attribute8 =>p_rec.attribute8
228 ,p_attribute9 =>p_rec.attribute9
229 ,p_attribute10 =>p_rec.attribute10
230 ,p_attribute11 =>p_rec.attribute11
231 ,p_attribute12 =>p_rec.attribute12
232 ,p_attribute13 =>p_rec.attribute13
233 ,p_attribute14 =>p_rec.attribute14
234 ,p_attribute15 =>p_rec.attribute15
235 ,p_attribute16 =>p_rec.attribute16
236 ,p_attribute17 =>p_rec.attribute17
237 ,p_attribute18 =>p_rec.attribute18
238 ,p_attribute19 =>p_rec.attribute19
239 ,p_attribute20 =>p_rec.attribute20
240 ,p_collective_agreement_id_o =>per_grs_shd.g_old_rec.collective_agreement_id
241 ,p_object_version_number_o =>per_grs_shd.g_old_rec.object_version_number
242 ,p_id_flex_num_o =>per_grs_shd.g_old_rec.id_flex_num
243 ,p_dynamic_insert_allowed_o =>per_grs_shd.g_old_rec.dynamic_insert_allowed
244 ,p_attribute_category_o =>per_grs_shd.g_old_rec.attribute_category
245 ,p_attribute1_o =>per_grs_shd.g_old_rec.attribute1
246 ,p_attribute2_o =>per_grs_shd.g_old_rec.attribute2
247 ,p_attribute3_o =>per_grs_shd.g_old_rec.attribute3
248 ,p_attribute4_o =>per_grs_shd.g_old_rec.attribute4
249 ,p_attribute5_o =>per_grs_shd.g_old_rec.attribute5
250 ,p_attribute6_o =>per_grs_shd.g_old_rec.attribute6
251 ,p_attribute7_o =>per_grs_shd.g_old_rec.attribute7
252 ,p_attribute8_o =>per_grs_shd.g_old_rec.attribute8
253 ,p_attribute9_o =>per_grs_shd.g_old_rec.attribute9
254 ,p_attribute10_o =>per_grs_shd.g_old_rec.attribute10
255 ,p_attribute11_o =>per_grs_shd.g_old_rec.attribute11
256 ,p_attribute12_o =>per_grs_shd.g_old_rec.attribute12
257 ,p_attribute13_o =>per_grs_shd.g_old_rec.attribute13
258 ,p_attribute14_o =>per_grs_shd.g_old_rec.attribute14
259 ,p_attribute15_o =>per_grs_shd.g_old_rec.attribute15
260 ,p_attribute16_o =>per_grs_shd.g_old_rec.attribute16
261 ,p_attribute17_o =>per_grs_shd.g_old_rec.attribute17
262 ,p_attribute18_o =>per_grs_shd.g_old_rec.attribute18
263 ,p_attribute19_o =>per_grs_shd.g_old_rec.attribute19
264 ,p_attribute20_o =>per_grs_shd.g_old_rec.attribute20
265 ,p_effective_date =>p_effective_date
266 );
267 --
268 exception
269 --
270 when hr_api.cannot_find_prog_unit then
271 --
272 hr_api.cannot_find_prog_unit_error
273 (p_module_name => 'per_cagr_grade_structures'
274 ,p_hook_type => 'AU');
275 --
276 end;
277 --
278 -- End of API User Hook for post_update.
279 --
280 --
281 hr_utility.set_location(' Leaving:'||l_proc, 10);
282 End post_update;
283 --
284 -- ----------------------------------------------------------------------------
285 -- |-----------------------------< convert_defs >-----------------------------|
286 -- ----------------------------------------------------------------------------
287 -- {Start Of Comments}
288 --
289 -- Description:
290 -- The Convert_Defs procedure has one very important function:
291 -- It must return the record structure for the row with all system defaulted
292 -- values converted into its corresponding parameter value for update. When
293 -- we attempt to update a row through the Upd process , certain
294 -- parameters can be defaulted which enables flexibility in the calling of
295 -- the upd process (e.g. only attributes which need to be updated need to be
296 -- specified). For the upd process to determine which attributes
297 -- have NOT been specified we need to check if the parameter has a reserved
298 -- system default value. Therefore, for all parameters which have a
299 -- corresponding reserved system default mechanism specified we need to
300 -- check if a system default is being used. If a system default is being
301 -- used then we convert the defaulted value into its corresponding attribute
302 -- value held in the g_old_rec data structure.
303 --
304 -- Prerequisites:
305 -- This private function can only be called from the upd process.
306 --
307 -- In Parameters:
308 -- A Pl/Sql record structre.
309 --
310 -- Post Success:
311 -- The record structure will be returned with all system defaulted parameter
312 -- values converted into its current row attribute value.
313 --
314 -- Post Failure:
315 -- No direct error handling is required within this function. Any possible
316 -- errors within this procedure will be a PL/SQL value error due to conversion
317 -- of datatypes or data lengths.
318 --
319 -- Developer Implementation Notes:
320 -- None.
321 --
322 -- Access Status:
323 -- Internal Row Handler Use Only.
324 --
325 -- {End Of Comments}
326 -- ----------------------------------------------------------------------------
327 Procedure convert_defs(p_rec in out nocopy per_grs_shd.g_rec_type) is
328 --
329 l_proc varchar2(72) := g_package||'convert_defs';
330 --
331 Begin
332 --
333 hr_utility.set_location('Entering:'||l_proc, 5);
334 --
335 -- We must now examine each argument value in the
336 -- p_rec plsql record structure
337 -- to see if a system default is being used. If a system default
338 -- is being used then we must set to the 'current' argument value.
339 --
340 If (p_rec.collective_agreement_id = hr_api.g_number) then
341 p_rec.collective_agreement_id :=
342 per_grs_shd.g_old_rec.collective_agreement_id;
343 End If;
344 If (p_rec.id_flex_num = hr_api.g_number) then
345 p_rec.id_flex_num :=
346 per_grs_shd.g_old_rec.id_flex_num;
347 End If;
348 If (p_rec.dynamic_insert_allowed = hr_api.g_varchar2) then
349 p_rec.dynamic_insert_allowed :=
350 per_grs_shd.g_old_rec.dynamic_insert_allowed;
351 End If;
352 If (p_rec.attribute_category = hr_api.g_varchar2) then
353 p_rec.attribute_category :=
354 per_grs_shd.g_old_rec.attribute_category;
355 End If;
356 If (p_rec.attribute1 = hr_api.g_varchar2) then
357 p_rec.attribute1 :=
358 per_grs_shd.g_old_rec.attribute1;
359 End If;
360 If (p_rec.attribute2 = hr_api.g_varchar2) then
361 p_rec.attribute2 :=
362 per_grs_shd.g_old_rec.attribute2;
363 End If;
364 If (p_rec.attribute3 = hr_api.g_varchar2) then
365 p_rec.attribute3 :=
366 per_grs_shd.g_old_rec.attribute3;
367 End If;
368 If (p_rec.attribute4 = hr_api.g_varchar2) then
369 p_rec.attribute4 :=
370 per_grs_shd.g_old_rec.attribute4;
371 End If;
372 If (p_rec.attribute5 = hr_api.g_varchar2) then
373 p_rec.attribute5 :=
374 per_grs_shd.g_old_rec.attribute5;
375 End If;
376 If (p_rec.attribute6 = hr_api.g_varchar2) then
377 p_rec.attribute6 :=
378 per_grs_shd.g_old_rec.attribute6;
379 End If;
380 If (p_rec.attribute7 = hr_api.g_varchar2) then
381 p_rec.attribute7 :=
382 per_grs_shd.g_old_rec.attribute7;
383 End If;
384 If (p_rec.attribute8 = hr_api.g_varchar2) then
385 p_rec.attribute8 :=
386 per_grs_shd.g_old_rec.attribute8;
387 End If;
388 If (p_rec.attribute9 = hr_api.g_varchar2) then
389 p_rec.attribute9 :=
390 per_grs_shd.g_old_rec.attribute9;
391 End If;
392 If (p_rec.attribute10 = hr_api.g_varchar2) then
393 p_rec.attribute10 :=
394 per_grs_shd.g_old_rec.attribute10;
395 End If;
396 If (p_rec.attribute11 = hr_api.g_varchar2) then
397 p_rec.attribute11 :=
398 per_grs_shd.g_old_rec.attribute11;
399 End If;
400 If (p_rec.attribute12 = hr_api.g_varchar2) then
401 p_rec.attribute12 :=
402 per_grs_shd.g_old_rec.attribute12;
403 End If;
404 If (p_rec.attribute13 = hr_api.g_varchar2) then
405 p_rec.attribute13 :=
406 per_grs_shd.g_old_rec.attribute13;
407 End If;
408 If (p_rec.attribute14 = hr_api.g_varchar2) then
409 p_rec.attribute14 :=
410 per_grs_shd.g_old_rec.attribute14;
411 End If;
412 If (p_rec.attribute15 = hr_api.g_varchar2) then
413 p_rec.attribute15 :=
414 per_grs_shd.g_old_rec.attribute15;
415 End If;
416 If (p_rec.attribute16 = hr_api.g_varchar2) then
417 p_rec.attribute16 :=
418 per_grs_shd.g_old_rec.attribute16;
419 End If;
420 If (p_rec.attribute17 = hr_api.g_varchar2) then
421 p_rec.attribute17 :=
422 per_grs_shd.g_old_rec.attribute17;
423 End If;
424 If (p_rec.attribute18 = hr_api.g_varchar2) then
425 p_rec.attribute18 :=
426 per_grs_shd.g_old_rec.attribute18;
427 End If;
428 If (p_rec.attribute19 = hr_api.g_varchar2) then
429 p_rec.attribute19 :=
430 per_grs_shd.g_old_rec.attribute19;
431 End If;
432 If (p_rec.attribute20 = hr_api.g_varchar2) then
433 p_rec.attribute20 :=
434 per_grs_shd.g_old_rec.attribute20;
435 End If;
436 --
437 hr_utility.set_location(' Leaving:'||l_proc, 10);
438 --
439 End convert_defs;
440 --
441 -- ----------------------------------------------------------------------------
442 -- |---------------------------------< upd >----------------------------------|
443 -- ----------------------------------------------------------------------------
444 Procedure upd
445 (p_effective_date in date,
446 p_rec in out nocopy per_grs_shd.g_rec_type
447 ) is
448 --
449 l_proc varchar2(72) := g_package||'upd';
450 --
451 Begin
452 hr_utility.set_location('Entering:'||l_proc, 5);
453 --
454 -- We must lock the row which we need to update.
455 --
456 per_grs_shd.lck
457 (p_effective_date => p_effective_date,
458 p_cagr_grade_structure_id => p_rec.cagr_grade_structure_id,
459 p_object_version_number => p_rec.object_version_number
460 );
461 --
462 -- 1. During an update system defaults are used to determine if
463 -- arguments have been defaulted or not. We must therefore
464 -- derive the full record structure values to be updated.
465 --
466 -- 2. Call the supporting update validate operations.
467 --
468 convert_defs(p_rec);
469 per_grs_bus.update_validate(p_effective_date => p_effective_date,
470 p_rec => p_rec);
471 --
472 -- Call the supporting pre-update operation
473 --
474 pre_update(p_effective_date => p_effective_date,
475 p_rec => p_rec);
476 --
477 -- Update the row.
478 --
479 update_dml(p_rec);
480 --
481 -- Call the supporting post-update operation
482 --
483 post_update(p_effective_date => p_effective_date,
484 p_rec => p_rec);
485 --
486 hr_utility.set_location(' Leaving:'||l_proc, 10);
487 --
488 End upd;
489 --
490 -- ----------------------------------------------------------------------------
491 -- |---------------------------------< upd >----------------------------------|
492 -- ----------------------------------------------------------------------------
493 Procedure upd
494 (
495 p_cagr_grade_structure_id in number,
496 p_collective_agreement_id in number default hr_api.g_number,
497 p_object_version_number in out nocopy number,
498 p_id_flex_num in number default hr_api.g_number,
499 p_dynamic_insert_allowed in varchar2 default hr_api.g_varchar2,
500 p_attribute_category in varchar2 default hr_api.g_varchar2,
501 p_attribute1 in varchar2 default hr_api.g_varchar2,
502 p_attribute2 in varchar2 default hr_api.g_varchar2,
503 p_attribute3 in varchar2 default hr_api.g_varchar2,
504 p_attribute4 in varchar2 default hr_api.g_varchar2,
505 p_attribute5 in varchar2 default hr_api.g_varchar2,
506 p_attribute6 in varchar2 default hr_api.g_varchar2,
507 p_attribute7 in varchar2 default hr_api.g_varchar2,
508 p_attribute8 in varchar2 default hr_api.g_varchar2,
509 p_attribute9 in varchar2 default hr_api.g_varchar2,
510 p_attribute10 in varchar2 default hr_api.g_varchar2,
511 p_attribute11 in varchar2 default hr_api.g_varchar2,
512 p_attribute12 in varchar2 default hr_api.g_varchar2,
513 p_attribute13 in varchar2 default hr_api.g_varchar2,
514 p_attribute14 in varchar2 default hr_api.g_varchar2,
515 p_attribute15 in varchar2 default hr_api.g_varchar2,
516 p_attribute16 in varchar2 default hr_api.g_varchar2,
517 p_attribute17 in varchar2 default hr_api.g_varchar2,
518 p_attribute18 in varchar2 default hr_api.g_varchar2,
519 p_attribute19 in varchar2 default hr_api.g_varchar2,
520 p_attribute20 in varchar2 default hr_api.g_varchar2,
521 p_effective_date in date
522 ) is
523 --
524 l_rec per_grs_shd.g_rec_type;
525 l_proc varchar2(72) := g_package||'upd';
526 --
527 Begin
528 hr_utility.set_location('Entering:'||l_proc, 5);
529 --
530 -- Call conversion function to turn arguments into the
531 -- l_rec structure.
532 --
533 l_rec :=
534 per_grs_shd.convert_args
535 (
536 p_cagr_grade_structure_id,
537 p_collective_agreement_id,
538 p_object_version_number,
539 p_id_flex_num,
540 p_dynamic_insert_allowed,
541 p_attribute_category,
542 p_attribute1,
543 p_attribute2,
544 p_attribute3,
545 p_attribute4,
546 p_attribute5,
547 p_attribute6,
548 p_attribute7,
549 p_attribute8,
550 p_attribute9,
551 p_attribute10,
552 p_attribute11,
553 p_attribute12,
554 p_attribute13,
555 p_attribute14,
556 p_attribute15,
557 p_attribute16,
558 p_attribute17,
559 p_attribute18,
560 p_attribute19,
561 p_attribute20
562 );
563 --
564 -- Having converted the arguments into the
565 -- plsql record structure we call the corresponding record
566 -- business process.
567 --
568 upd(p_effective_date => p_effective_date,
569 p_rec => l_rec);
570 p_object_version_number := l_rec.object_version_number;
571 --
572 hr_utility.set_location(' Leaving:'||l_proc, 10);
573 End upd;
574 --
575 end per_grs_upd;
[Home] [Help]