1 Package Body pqh_rmv_upd as
2 /* $Header: pqrmvrhi.pkb 120.2 2005/06/23 03:41 srenukun noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_rmv_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 pqh_rmv_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 pqh_rt_matrix_node_values Row
69 --
70 update pqh_rt_matrix_node_values
71 set
72 node_value_id = p_rec.node_value_id
73 ,rate_matrix_node_id = p_rec.rate_matrix_node_id
74 ,short_code = p_rec.short_code
75 ,char_value1 = p_rec.char_value1
76 ,char_value2 = p_rec.char_value2
77 ,char_value3 = p_rec.char_value3
78 ,char_value4 = p_rec.char_value4
79 ,number_value1 = p_rec.number_value1
80 ,number_value2 = p_rec.number_value2
81 ,number_value3 = p_rec.number_value3
82 ,number_value4 = p_rec.number_value4
83 ,date_value1 = p_rec.date_value1
84 ,date_value2 = p_rec.date_value2
85 ,date_value3 = p_rec.date_value3
86 ,date_value4 = p_rec.date_value4
87 ,business_group_id = p_rec.business_group_id
88 ,legislation_code = p_rec.legislation_code
89 ,object_version_number = p_rec.object_version_number
90 where node_value_id = p_rec.node_value_id;
91 --
92 --
93 --
94 hr_utility.set_location(' Leaving:'||l_proc, 10);
95 --
96 Exception
97 When hr_api.check_integrity_violated Then
98 -- A check constraint has been violated
99 --
100 pqh_rmv_shd.constraint_error
101 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
102 When hr_api.parent_integrity_violated Then
103 -- Parent integrity has been violated
104 --
105 pqh_rmv_shd.constraint_error
106 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
107 When hr_api.unique_integrity_violated Then
108 -- Unique integrity has been violated
109 --
110 pqh_rmv_shd.constraint_error
111 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
112 When Others Then
113 --
114 Raise;
115 End update_dml;
116 --
117 -- ----------------------------------------------------------------------------
118 -- |------------------------------< pre_update >------------------------------|
119 -- ----------------------------------------------------------------------------
120 -- {Start Of Comments}
121 --
122 -- Description:
123 -- This private procedure contains any processing which is required before
124 -- the update dml.
125 --
126 -- Prerequisites:
127 -- This is an internal procedure which is called from the upd procedure.
128 --
129 -- In Parameters:
130 -- A Pl/Sql record structure.
131 --
132 -- Post Success:
133 -- Processing continues.
134 --
135 -- Post Failure:
136 -- If an error has occurred, an error message and exception wil be raised
137 -- but not handled.
138 --
139 -- Developer Implementation Notes:
140 -- Any pre-processing required before the update dml is issued should be
141 -- coded within this procedure. It is important to note that any 3rd party
142 -- maintenance should be reviewed before placing in this procedure.
143 --
144 -- Access Status:
145 -- Internal Row Handler Use Only.
146 --
147 -- {End Of Comments}
148 -- ----------------------------------------------------------------------------
149 Procedure pre_update
150 (p_rec in pqh_rmv_shd.g_rec_type
151 ) is
152 --
153 l_proc varchar2(72) := g_package||'pre_update';
154 --
155 Begin
156 hr_utility.set_location('Entering:'||l_proc, 5);
157 --
158 hr_utility.set_location(' Leaving:'||l_proc, 10);
159 End pre_update;
160 --
161 -- ----------------------------------------------------------------------------
162 -- |-----------------------------< post_update >------------------------------|
163 -- ----------------------------------------------------------------------------
164 -- {Start Of Comments}
165 --
166 -- Description:
167 -- This private procedure contains any processing which is required after
168 -- the update dml.
169 --
170 -- Prerequisites:
171 -- This is an internal procedure which is called from the upd procedure.
172 --
173 -- In Parameters:
174 -- A Pl/Sql record structure.
175 --
176 -- Post Success:
177 -- Processing continues.
178 --
179 -- Post Failure:
180 -- If an error has occurred, an error message and exception will be raised
181 -- but not handled.
182 --
183 -- Developer Implementation Notes:
184 -- Any post-processing required after the update dml is issued should be
185 -- coded within this procedure. It is important to note that any 3rd party
186 -- maintenance should be reviewed before placing in this procedure.
187 --
188 -- Access Status:
189 -- Internal Row Handler Use Only.
190 --
191 -- {End Of Comments}
192 -- ----------------------------------------------------------------------------
193 Procedure post_update
194 (p_rec in pqh_rmv_shd.g_rec_type
195 ) is
196 --
197 l_proc varchar2(72) := g_package||'post_update';
198 --
199 Begin
200 hr_utility.set_location('Entering:'||l_proc, 5);
201 begin
202 --
203 pqh_rmv_rku.after_update
204 (p_node_value_id
205 => p_rec.node_value_id
206 ,p_rate_matrix_node_id
207 => p_rec.rate_matrix_node_id
208 ,p_short_code
209 => p_rec.short_code
210 ,p_char_value1
211 => p_rec.char_value1
212 ,p_char_value2
213 => p_rec.char_value2
214 ,p_char_value3
215 => p_rec.char_value3
216 ,p_char_value4
217 => p_rec.char_value4
218 ,p_number_value1
219 => p_rec.number_value1
220 ,p_number_value2
221 => p_rec.number_value2
222 ,p_number_value3
223 => p_rec.number_value3
224 ,p_number_value4
225 => p_rec.number_value4
226 ,p_date_value1
227 => p_rec.date_value1
228 ,p_date_value2
229 => p_rec.date_value2
230 ,p_date_value3
231 => p_rec.date_value3
232 ,p_date_value4
233 => p_rec.date_value4
234 ,p_business_group_id
235 => p_rec.business_group_id
236 ,p_legislation_code
237 => p_rec.legislation_code
238 ,p_object_version_number
239 => p_rec.object_version_number
240 ,p_rate_matrix_node_id_o
241 => pqh_rmv_shd.g_old_rec.rate_matrix_node_id
242 ,p_short_code_o
243 => pqh_rmv_shd.g_old_rec.short_code
244 ,p_char_value1_o
245 => pqh_rmv_shd.g_old_rec.char_value1
246 ,p_char_value2_o
247 => pqh_rmv_shd.g_old_rec.char_value2
248 ,p_char_value3_o
249 => pqh_rmv_shd.g_old_rec.char_value3
250 ,p_char_value4_o
251 => pqh_rmv_shd.g_old_rec.char_value4
252 ,p_number_value1_o
253 => pqh_rmv_shd.g_old_rec.number_value1
254 ,p_number_value2_o
255 => pqh_rmv_shd.g_old_rec.number_value2
256 ,p_number_value3_o
257 => pqh_rmv_shd.g_old_rec.number_value3
258 ,p_number_value4_o
259 => pqh_rmv_shd.g_old_rec.number_value4
260 ,p_date_value1_o
261 => pqh_rmv_shd.g_old_rec.date_value1
262 ,p_date_value2_o
263 => pqh_rmv_shd.g_old_rec.date_value2
264 ,p_date_value3_o
265 => pqh_rmv_shd.g_old_rec.date_value3
266 ,p_date_value4_o
267 => pqh_rmv_shd.g_old_rec.date_value4
268 ,p_business_group_id_o
269 => pqh_rmv_shd.g_old_rec.business_group_id
270 ,p_legislation_code_o
271 => pqh_rmv_shd.g_old_rec.legislation_code
272 ,p_object_version_number_o
273 => pqh_rmv_shd.g_old_rec.object_version_number
274 );
275 --
276 exception
277 --
278 when hr_api.cannot_find_prog_unit then
279 --
280 hr_api.cannot_find_prog_unit_error
281 (p_module_name => 'PQH_RT_MATRIX_NODE_VALUES'
282 ,p_hook_type => 'AU');
283 --
284 end;
285 --
286 hr_utility.set_location(' Leaving:'||l_proc, 10);
287 End post_update;
288 --
289 -- ----------------------------------------------------------------------------
290 -- |-----------------------------< convert_defs >-----------------------------|
291 -- ----------------------------------------------------------------------------
292 -- {Start Of Comments}
293 --
294 -- Description:
295 -- The Convert_Defs procedure has one very important function:
296 -- It must return the record structure for the row with all system defaulted
297 -- values converted into its corresponding parameter value for update. When
298 -- we attempt to update a row through the Upd process , certain
299 -- parameters can be defaulted which enables flexibility in the calling of
300 -- the upd process (e.g. only attributes which need to be updated need to be
301 -- specified). For the upd process to determine which attributes
302 -- have NOT been specified we need to check if the parameter has a reserved
303 -- system default value. Therefore, for all parameters which have a
304 -- corresponding reserved system default mechanism specified we need to
305 -- check if a system default is being used. If a system default is being
306 -- used then we convert the defaulted value into its corresponding attribute
307 -- value held in the g_old_rec data structure.
308 --
309 -- Prerequisites:
310 -- This private function can only be called from the upd process.
311 --
312 -- In Parameters:
313 -- A Pl/Sql record structure.
314 --
315 -- Post Success:
316 -- The record structure will be returned with all system defaulted parameter
317 -- values converted into its current row attribute value.
318 --
319 -- Post Failure:
320 -- No direct error handling is required within this function. Any possible
321 -- errors within this procedure will be a PL/SQL value error due to
322 -- conversion of datatypes or data lengths.
323 --
324 -- Developer Implementation Notes:
325 -- None.
326 --
327 -- Access Status:
328 -- Internal Row Handler Use Only.
329 --
330 -- {End Of Comments}
331 -- ----------------------------------------------------------------------------
332 Procedure convert_defs
333 (p_rec in out nocopy pqh_rmv_shd.g_rec_type
334 ) is
335 --
336 Begin
337 --
338 -- We must now examine each argument value in the
339 -- p_rec plsql record structure
340 -- to see if a system default is being used. If a system default
341 -- is being used then we must set to the 'current' argument value.
342 --
343 If (p_rec.rate_matrix_node_id = hr_api.g_number) then
344 p_rec.rate_matrix_node_id :=
345 pqh_rmv_shd.g_old_rec.rate_matrix_node_id;
346 End If;
347 If (p_rec.short_code = hr_api.g_varchar2) then
348 p_rec.short_code :=
349 pqh_rmv_shd.g_old_rec.short_code;
350 End If;
351 If (p_rec.char_value1 = hr_api.g_varchar2) then
352 p_rec.char_value1 :=
353 pqh_rmv_shd.g_old_rec.char_value1;
354 End If;
355 If (p_rec.char_value2 = hr_api.g_varchar2) then
356 p_rec.char_value2 :=
357 pqh_rmv_shd.g_old_rec.char_value2;
358 End If;
359 If (p_rec.char_value3 = hr_api.g_varchar2) then
360 p_rec.char_value3 :=
361 pqh_rmv_shd.g_old_rec.char_value3;
362 End If;
363 If (p_rec.char_value4 = hr_api.g_varchar2) then
364 p_rec.char_value4 :=
365 pqh_rmv_shd.g_old_rec.char_value4;
366 End If;
367 If (p_rec.number_value1 = hr_api.g_number) then
368 p_rec.number_value1 :=
369 pqh_rmv_shd.g_old_rec.number_value1;
370 End If;
371 If (p_rec.number_value2 = hr_api.g_number) then
372 p_rec.number_value2 :=
373 pqh_rmv_shd.g_old_rec.number_value2;
374 End If;
375 If (p_rec.number_value3 = hr_api.g_number) then
376 p_rec.number_value3 :=
377 pqh_rmv_shd.g_old_rec.number_value3;
378 End If;
379 If (p_rec.number_value4 = hr_api.g_number) then
380 p_rec.number_value4 :=
381 pqh_rmv_shd.g_old_rec.number_value4;
382 End If;
383 If (p_rec.date_value1 = hr_api.g_date) then
384 p_rec.date_value1 :=
385 pqh_rmv_shd.g_old_rec.date_value1;
386 End If;
387 If (p_rec.date_value2 = hr_api.g_date) then
388 p_rec.date_value2 :=
389 pqh_rmv_shd.g_old_rec.date_value2;
390 End If;
391 If (p_rec.date_value3 = hr_api.g_date) then
392 p_rec.date_value3 :=
393 pqh_rmv_shd.g_old_rec.date_value3;
394 End If;
395 If (p_rec.date_value4 = hr_api.g_date) then
396 p_rec.date_value4 :=
397 pqh_rmv_shd.g_old_rec.date_value4;
398 End If;
399 If (p_rec.business_group_id = hr_api.g_number) then
400 p_rec.business_group_id :=
401 pqh_rmv_shd.g_old_rec.business_group_id;
402 End If;
403 If (p_rec.legislation_code = hr_api.g_varchar2) then
404 p_rec.legislation_code :=
405 pqh_rmv_shd.g_old_rec.legislation_code;
406 End If;
407 --
408 End convert_defs;
409 --
410 -- ----------------------------------------------------------------------------
411 -- |---------------------------------< upd >----------------------------------|
412 -- ----------------------------------------------------------------------------
413 Procedure upd
414 (p_rec in out nocopy pqh_rmv_shd.g_rec_type
415 ) is
416 --
417 l_proc varchar2(72) := g_package||'upd';
418 --
419 Begin
420 hr_utility.set_location('Entering:'||l_proc, 5);
421 --
422 -- We must lock the row which we need to update.
423 --
424 pqh_rmv_shd.lck
425 (p_rec.node_value_id
426 ,p_rec.object_version_number
427 );
428 --
429 -- 1. During an update system defaults are used to determine if
430 -- arguments have been defaulted or not. We must therefore
431 -- derive the full record structure values to be updated.
432 --
433 -- 2. Call the supporting update validate operations.
434 --
435 convert_defs(p_rec);
436 pqh_rmv_bus.update_validate
437 (p_rec
438 );
439 --
440 -- Call to raise any errors on multi-message list
441 hr_multi_message.end_validation_set;
442 --
443 -- Call the supporting pre-update operation
444 --
445 pqh_rmv_upd.pre_update(p_rec);
446 --
447 -- Update the row.
448 --
449 pqh_rmv_upd.update_dml(p_rec);
450 --
451 -- Call the supporting post-update operation
452 --
453 pqh_rmv_upd.post_update
454 (p_rec
455 );
456 --
457 -- Call to raise any errors on multi-message list
458 hr_multi_message.end_validation_set;
459 End upd;
460 --
461 -- ----------------------------------------------------------------------------
462 -- |---------------------------------< upd >----------------------------------|
463 -- ----------------------------------------------------------------------------
464 Procedure upd
465 (p_node_value_id in number
466 ,p_object_version_number in out nocopy number
467 ,p_rate_matrix_node_id in number default hr_api.g_number
468 ,p_short_code in varchar2 default hr_api.g_varchar2
469 ,p_char_value1 in varchar2 default hr_api.g_varchar2
470 ,p_char_value2 in varchar2 default hr_api.g_varchar2
471 ,p_char_value3 in varchar2 default hr_api.g_varchar2
472 ,p_char_value4 in varchar2 default hr_api.g_varchar2
473 ,p_number_value1 in number default hr_api.g_number
474 ,p_number_value2 in number default hr_api.g_number
475 ,p_number_value3 in number default hr_api.g_number
476 ,p_number_value4 in number default hr_api.g_number
477 ,p_date_value1 in date default hr_api.g_date
478 ,p_date_value2 in date default hr_api.g_date
479 ,p_date_value3 in date default hr_api.g_date
480 ,p_date_value4 in date default hr_api.g_date
481 ,p_business_group_id in number default hr_api.g_number
482 ,p_legislation_code in varchar2 default hr_api.g_varchar2
483 ) is
484 --
485 l_rec pqh_rmv_shd.g_rec_type;
486 l_proc varchar2(72) := g_package||'upd';
487 --
488 Begin
489 hr_utility.set_location('Entering:'||l_proc, 5);
490 --
491 -- Call conversion function to turn arguments into the
492 -- l_rec structure.
493 --
494 l_rec :=
495 pqh_rmv_shd.convert_args
496 (p_node_value_id
497 ,p_rate_matrix_node_id
498 ,p_short_code
499 ,p_char_value1
500 ,p_char_value2
501 ,p_char_value3
502 ,p_char_value4
503 ,p_number_value1
504 ,p_number_value2
505 ,p_number_value3
506 ,p_number_value4
507 ,p_date_value1
508 ,p_date_value2
509 ,p_date_value3
510 ,p_date_value4
511 ,p_business_group_id
512 ,p_legislation_code
513 ,p_object_version_number
514 );
515 --
516 -- Having converted the arguments into the
517 -- plsql record structure we call the corresponding record
518 -- business process.
519 --
520 pqh_rmv_upd.upd
521 (l_rec
522 );
523 p_object_version_number := l_rec.object_version_number;
524 --
525 hr_utility.set_location(' Leaving:'||l_proc, 10);
526 End upd;
527 --
528 end pqh_rmv_upd;