1 Package Body per_res_upd as
2 /* $Header: peresrhi.pkb 115.2 2003/04/02 13:38:24 eumenyio noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_res_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
55 (p_rec in out nocopy per_res_shd.g_rec_type
56 ) is
57 --
58 l_proc varchar2(72) := g_package||'update_dml';
59 --
60 Begin
61 hr_utility.set_location('Entering:'||l_proc, 5);
62 --
63 -- Increment the object version
64 p_rec.object_version_number := p_rec.object_version_number + 1;
65 --
66 --
67 --
68 -- Update the per_cagr_entitlement_results Row
69 --
70 update per_cagr_entitlement_results
71 set
72 cagr_entitlement_result_id = p_rec.cagr_entitlement_result_id
73 ,chosen_flag = p_rec.chosen_flag
74 ,object_version_number = p_rec.object_version_number
75 where cagr_entitlement_result_id = p_rec.cagr_entitlement_result_id;
76 --
77 --
78 --
79 hr_utility.set_location(' Leaving:'||l_proc, 10);
80 --
81 Exception
82 When hr_api.check_integrity_violated Then
83 -- A check constraint has been violated
84 --
85 per_res_shd.constraint_error
86 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
87 When hr_api.parent_integrity_violated Then
88 -- Parent integrity has been violated
89 --
90 per_res_shd.constraint_error
91 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
92 When hr_api.unique_integrity_violated Then
93 -- Unique integrity has been violated
94 --
95 per_res_shd.constraint_error
96 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
97 When Others Then
98 --
99 Raise;
100 End update_dml;
101 --
102 -- ----------------------------------------------------------------------------
103 -- |------------------------------< pre_update >------------------------------|
104 -- ----------------------------------------------------------------------------
105 -- {Start Of Comments}
106 --
107 -- Description:
108 -- This private procedure contains any processing which is required before
109 -- the update dml.
110 --
111 -- Prerequisites:
112 -- This is an internal procedure which is called from the upd procedure.
113 --
114 -- In Parameters:
115 -- A Pl/Sql record structure.
116 --
117 -- Post Success:
118 -- Processing continues.
119 --
120 -- Post Failure:
121 -- If an error has occurred, an error message and exception wil be raised
122 -- but not handled.
123 --
124 -- Developer Implementation Notes:
125 -- Any pre-processing required before the update dml is issued should be
126 -- coded within this procedure. It is important to note that any 3rd party
127 -- maintenance should be reviewed before placing in this procedure.
128 --
129 -- Access Status:
130 -- Internal Row Handler Use Only.
131 --
132 -- {End Of Comments}
133 -- ----------------------------------------------------------------------------
134 Procedure pre_update
135 (p_rec in per_res_shd.g_rec_type
136 ) is
137 --
138 l_proc varchar2(72) := g_package||'pre_update';
139 --
140 Begin
141 hr_utility.set_location('Entering:'||l_proc, 5);
142 --
143 hr_utility.set_location(' Leaving:'||l_proc, 10);
144 End pre_update;
145 --
146 -- ----------------------------------------------------------------------------
147 -- |-----------------------------< post_update >------------------------------|
148 -- ----------------------------------------------------------------------------
149 -- {Start Of Comments}
150 --
151 -- Description:
152 -- This private procedure contains any processing which is required after
153 -- the update dml.
154 --
155 -- Prerequisites:
156 -- This is an internal procedure which is called from the upd procedure.
157 --
158 -- In Parameters:
159 -- A Pl/Sql record structure.
160 --
161 -- Post Success:
162 -- Processing continues.
163 --
164 -- Post Failure:
165 -- If an error has occurred, an error message and exception will be raised
166 -- but not handled.
167 --
168 -- Developer Implementation Notes:
169 -- Any post-processing required after the update dml is issued should be
170 -- coded within this procedure. It is important to note that any 3rd party
171 -- maintenance should be reviewed before placing in this procedure.
172 --
173 -- Access Status:
174 -- Internal Row Handler Use Only.
175 --
176 -- {End Of Comments}
177 -- ----------------------------------------------------------------------------
178 Procedure post_update
179 (p_effective_date in date
180 ,p_rec in per_res_shd.g_rec_type
181 ) is
182 --
183 l_proc varchar2(72) := g_package||'post_update';
184 --
185 Begin
186 hr_utility.set_location('Entering:'||l_proc, 5);
187 /*
188 begin
189 --
190 per_res_rku.after_update
191 (p_effective_date => p_effective_date
192 ,p_cagr_entitlement_result_id => p_rec.cagr_entitlement_result_id
193 ,p_beneficial_flag => p_rec.beneficial_flag
194 ,p_chosen_flag => p_rec.chosen_flag
195 ,p_object_version_number => p_rec.object_version_number
196 ,p_assignment_id_o
197 => per_res_shd.g_old_rec.assignment_id
198 ,p_start_date_o
199 => per_res_shd.g_old_rec.start_date
200 ,p_end_date_o
201 => per_res_shd.g_old_rec.end_date
202 ,p_collective_agreement_id_o
203 => per_res_shd.g_old_rec.collective_agreement_id
204 ,p_cagr_entitlement_item_id_o
205 => per_res_shd.g_old_rec.cagr_entitlement_item_id
206 ,p_element_type_id_o
207 => per_res_shd.g_old_rec.element_type_id
208 ,p_input_value_id_o
209 => per_res_shd.g_old_rec.input_value_id
210 ,p_cagr_api_id_o
211 => per_res_shd.g_old_rec.cagr_api_id
212 ,p_cagr_api_param_id_o
213 => per_res_shd.g_old_rec.cagr_api_param_id
214 ,p_category_name_o
215 => per_res_shd.g_old_rec.category_name
216 ,p_cagr_entitlement_id_o
217 => per_res_shd.g_old_rec.cagr_entitlement_id
218 ,p_cagr_entitlement_line_id_o
219 => per_res_shd.g_old_rec.cagr_entitlement_line_id
220 ,p_value_o
221 => per_res_shd.g_old_rec.value
222 ,p_units_of_measure_o
223 => per_res_shd.g_old_rec.units_of_measure
224 ,p_range_from_o
225 => per_res_shd.g_old_rec.range_from
226 ,p_range_to_o
227 => per_res_shd.g_old_rec.range_to
228 ,p_grade_spine_id_o
229 => per_res_shd.g_old_rec.grade_spine_id
230 ,p_parent_spine_id_o
231 => per_res_shd.g_old_rec.parent_spine_id
232 ,p_step_id_o
233 => per_res_shd.g_old_rec.step_id
234 ,p_from_step_id_o
235 => per_res_shd.g_old_rec.from_step_id
236 ,p_to_step_id_o
237 => per_res_shd.g_old_rec.to_step_id
238 ,p_beneficial_flag_o
239 => per_res_shd.g_old_rec.beneficial_flag
240 ,p_oipl_id_o
241 => per_res_shd.g_old_rec.oipl_id
242 ,p_chosen_flag_o
243 => per_res_shd.g_old_rec.chosen_flag
244 ,p_column_type_o
245 => per_res_shd.g_old_rec.column_type
246 ,p_column_size_o
247 => per_res_shd.g_old_rec.column_size
248 ,p_cagr_request_id_o
249 => per_res_shd.g_old_rec.cagr_request_id
250 ,p_business_group_id_o
251 => per_res_shd.g_old_rec.business_group_id
252 ,p_legislation_code_o
253 => per_res_shd.g_old_rec.legislation_code
254 ,p_eligy_prfl_id_o
255 => per_res_shd.g_old_rec.eligy_prfl_id
256 ,p_formula_id_o
257 => per_res_shd.g_old_rec.formula_id
258 ,p_object_version_number_o
259 => per_res_shd.g_old_rec.object_version_number
260 );
261 null;
262 --
263 exception
264 --
265 when hr_api.cannot_find_prog_unit then
266 --
267 hr_api.cannot_find_prog_unit_error
268 (p_module_name => 'PER_CAGR_ENTITLEMENT_RESULTS'
269 ,p_hook_type => 'AU');
270 --
271 end;
272 */
273 --
274 hr_utility.set_location(' Leaving:'||l_proc, 10);
275 End post_update;
276 --
277 -- ----------------------------------------------------------------------------
278 -- |-----------------------------< convert_defs >-----------------------------|
279 -- ----------------------------------------------------------------------------
280 -- {Start Of Comments}
281 --
282 -- Description:
283 -- The Convert_Defs procedure has one very important function:
284 -- It must return the record structure for the row with all system defaulted
285 -- values converted into its corresponding parameter value for update. When
286 -- we attempt to update a row through the Upd process , certain
287 -- parameters can be defaulted which enables flexibility in the calling of
288 -- the upd process (e.g. only attributes which need to be updated need to be
289 -- specified). For the upd process to determine which attributes
290 -- have NOT been specified we need to check if the parameter has a reserved
291 -- system default value. Therefore, for all parameters which have a
292 -- corresponding reserved system default mechanism specified we need to
293 -- check if a system default is being used. If a system default is being
294 -- used then we convert the defaulted value into its corresponding attribute
295 -- value held in the g_old_rec data structure.
296 --
297 -- Prerequisites:
298 -- This private function can only be called from the upd process.
299 --
300 -- In Parameters:
301 -- A Pl/Sql record structure.
302 --
303 -- Post Success:
304 -- The record structure will be returned with all system defaulted parameter
305 -- values converted into its current row attribute value.
306 --
307 -- Post Failure:
308 -- No direct error handling is required within this function. Any possible
309 -- errors within this procedure will be a PL/SQL value error due to
310 -- conversion of datatypes or data lengths.
311 --
312 -- Developer Implementation Notes:
313 -- None.
314 --
315 -- Access Status:
316 -- Internal Row Handler Use Only.
317 --
318 -- {End Of Comments}
319 -- ----------------------------------------------------------------------------
320 Procedure convert_defs
321 (p_rec in out nocopy per_res_shd.g_rec_type
322 ) is
323 --
324 Begin
325 --
326 -- We must now examine each argument value in the
327 -- p_rec plsql record structure
328 -- to see if a system default is being used. If a system default
329 -- is being used then we must set to the 'current' argument value.
330 --
331 If (p_rec.assignment_id = hr_api.g_number) then
332 p_rec.assignment_id :=
333 per_res_shd.g_old_rec.assignment_id;
334 End If;
335 If (p_rec.start_date = hr_api.g_date) then
336 p_rec.start_date :=
337 per_res_shd.g_old_rec.start_date;
338 End If;
339 If (p_rec.end_date = hr_api.g_date) then
340 p_rec.end_date :=
341 per_res_shd.g_old_rec.end_date;
342 End If;
343 If (p_rec.collective_agreement_id = hr_api.g_number) then
344 p_rec.collective_agreement_id :=
345 per_res_shd.g_old_rec.collective_agreement_id;
346 End If;
347 If (p_rec.cagr_entitlement_item_id = hr_api.g_number) then
348 p_rec.cagr_entitlement_item_id :=
349 per_res_shd.g_old_rec.cagr_entitlement_item_id;
350 End If;
351 If (p_rec.element_type_id = hr_api.g_number) then
352 p_rec.element_type_id :=
353 per_res_shd.g_old_rec.element_type_id;
354 End If;
355 If (p_rec.input_value_id = hr_api.g_number) then
356 p_rec.input_value_id :=
357 per_res_shd.g_old_rec.input_value_id;
358 End If;
359 If (p_rec.cagr_api_id = hr_api.g_number) then
360 p_rec.cagr_api_id :=
361 per_res_shd.g_old_rec.cagr_api_id;
362 End If;
363 If (p_rec.cagr_api_param_id = hr_api.g_number) then
364 p_rec.cagr_api_param_id :=
365 per_res_shd.g_old_rec.cagr_api_param_id;
366 End If;
367 If (p_rec.category_name = hr_api.g_varchar2) then
368 p_rec.category_name :=
369 per_res_shd.g_old_rec.category_name;
370 End If;
371 If (p_rec.cagr_entitlement_id = hr_api.g_number) then
372 p_rec.cagr_entitlement_id :=
373 per_res_shd.g_old_rec.cagr_entitlement_id;
374 End If;
375 If (p_rec.cagr_entitlement_line_id = hr_api.g_number) then
376 p_rec.cagr_entitlement_line_id :=
377 per_res_shd.g_old_rec.cagr_entitlement_line_id;
378 End If;
379 If (p_rec.value = hr_api.g_varchar2) then
380 p_rec.value :=
381 per_res_shd.g_old_rec.value;
382 End If;
383 If (p_rec.units_of_measure = hr_api.g_varchar2) then
384 p_rec.units_of_measure :=
385 per_res_shd.g_old_rec.units_of_measure;
386 End If;
387 If (p_rec.range_from = hr_api.g_varchar2) then
388 p_rec.range_from :=
389 per_res_shd.g_old_rec.range_from;
390 End If;
391 If (p_rec.range_to = hr_api.g_varchar2) then
392 p_rec.range_to :=
393 per_res_shd.g_old_rec.range_to;
394 End If;
395 If (p_rec.grade_spine_id = hr_api.g_number) then
396 p_rec.grade_spine_id :=
397 per_res_shd.g_old_rec.grade_spine_id;
398 End If;
399 If (p_rec.parent_spine_id = hr_api.g_number) then
400 p_rec.parent_spine_id :=
401 per_res_shd.g_old_rec.parent_spine_id;
402 End If;
403 If (p_rec.step_id = hr_api.g_number) then
404 p_rec.step_id :=
405 per_res_shd.g_old_rec.step_id;
409 per_res_shd.g_old_rec.from_step_id;
406 End If;
407 If (p_rec.from_step_id = hr_api.g_number) then
408 p_rec.from_step_id :=
410 End If;
411 If (p_rec.to_step_id = hr_api.g_number) then
412 p_rec.to_step_id :=
413 per_res_shd.g_old_rec.to_step_id;
414 End If;
415 If (p_rec.beneficial_flag = hr_api.g_varchar2) then
416 p_rec.beneficial_flag :=
417 per_res_shd.g_old_rec.beneficial_flag;
418 End If;
419 If (p_rec.oipl_id = hr_api.g_number) then
420 p_rec.oipl_id :=
421 per_res_shd.g_old_rec.oipl_id;
422 End If;
423 If (p_rec.chosen_flag = hr_api.g_varchar2) then
424 p_rec.chosen_flag :=
425 per_res_shd.g_old_rec.chosen_flag;
426 End If;
427 If (p_rec.column_type = hr_api.g_varchar2) then
428 p_rec.column_type :=
429 per_res_shd.g_old_rec.column_type;
430 End If;
431 If (p_rec.column_size = hr_api.g_number) then
432 p_rec.column_size :=
433 per_res_shd.g_old_rec.column_size;
434 End If;
435 If (p_rec.business_group_id = hr_api.g_number) then
436 p_rec.business_group_id :=
437 per_res_shd.g_old_rec.business_group_id;
438 End If;
439 If (p_rec.legislation_code = hr_api.g_varchar2) then
440 p_rec.legislation_code :=
441 per_res_shd.g_old_rec.legislation_code;
442 End If;
443 If (p_rec.eligy_prfl_id = hr_api.g_number) then
444 p_rec.eligy_prfl_id :=
445 per_res_shd.g_old_rec.eligy_prfl_id;
446 End If;
447 If (p_rec.formula_id = hr_api.g_number) then
448 p_rec.formula_id :=
449 per_res_shd.g_old_rec.formula_id;
450 End If;
451 --
452 End convert_defs;
453 --
454 -- ----------------------------------------------------------------------------
455 -- |---------------------------------< upd >----------------------------------|
456 -- ----------------------------------------------------------------------------
457 Procedure upd
458 (p_effective_date in date
459 ,p_rec in out nocopy per_res_shd.g_rec_type
460 ) is
461 --
462 l_proc varchar2(72) := g_package||'upd';
463 --
464 Begin
465 hr_utility.set_location('Entering:'||l_proc, 5);
466 --
467 -- We must lock the row which we need to update.
468 --
469 per_res_shd.lck
470 (p_rec.cagr_entitlement_result_id
471 ,p_rec.object_version_number
472 );
473 --
474 -- 1. During an update system defaults are used to determine if
475 -- arguments have been defaulted or not. We must therefore
476 -- derive the full record structure values to be updated.
477 --
478 -- 2. Call the supporting update validate operations.
479 --
480 convert_defs(p_rec);
481 per_res_bus.update_validate
482 (p_effective_date
483 ,p_rec
484 );
485 --
486 -- Call the supporting pre-update operation
487 --
488 per_res_upd.pre_update(p_rec);
489 --
490 -- Update the row.
491 --
492 per_res_upd.update_dml(p_rec);
493 --
494 -- Call the supporting post-update operation
495 --
496 per_res_upd.post_update
497 (p_effective_date
498 ,p_rec
499 );
500 End upd;
501 --
502 -- ----------------------------------------------------------------------------
503 -- |---------------------------------< upd >----------------------------------|
504 -- ----------------------------------------------------------------------------
505 Procedure upd
506 (p_effective_date in date
507 ,p_cagr_entitlement_result_id in number
508 ,p_object_version_number in out nocopy number
509 ,p_chosen_flag in varchar2 default hr_api.g_varchar2
510 ) is
511 --
512 l_rec per_res_shd.g_rec_type;
513 l_proc varchar2(72) := g_package||'upd';
514 --
515 Begin
516 hr_utility.set_location('Entering:'||l_proc, 5);
517 --
518 -- Call conversion function to turn arguments into the
519 -- l_rec structure.
520 --
521 l_rec :=
522 per_res_shd.convert_args
523 (p_cagr_entitlement_result_id
524 ,hr_api.g_number
525 ,hr_api.g_date
526 ,hr_api.g_date
527 ,hr_api.g_number
528 ,hr_api.g_number
529 ,hr_api.g_number
530 ,hr_api.g_number
531 ,hr_api.g_number
532 ,hr_api.g_number
533 ,hr_api.g_varchar2
534 ,hr_api.g_number
535 ,hr_api.g_number
536 ,hr_api.g_varchar2
537 ,hr_api.g_varchar2
538 ,hr_api.g_varchar2
539 ,hr_api.g_varchar2
540 ,hr_api.g_number
541 ,hr_api.g_number
542 ,hr_api.g_number
543 ,hr_api.g_number
544 ,hr_api.g_number
545 ,hr_api.g_varchar2
546 ,hr_api.g_number
547 ,p_chosen_flag -- this and ovn are only attributes that can be updated
548 ,hr_api.g_varchar2
549 ,hr_api.g_number
550 ,hr_api.g_number
551 ,hr_api.g_number
552 ,hr_api.g_varchar2
553 ,hr_api.g_number
554 ,hr_api.g_number
555 ,p_object_version_number
556 );
557 --
558 -- Having converted the arguments into the
559 -- plsql record structure we call the corresponding record
560 -- business process.
561 --
562 per_res_upd.upd
563 (p_effective_date
567 --
564 ,l_rec
565 );
566 p_object_version_number := l_rec.object_version_number;
568 hr_utility.set_location(' Leaving:'||l_proc, 10);
569 End upd;
570 --
571 end per_res_upd;
[Home] [Help]