1 Package Body pqh_vld_upd as
2 /* $Header: pqvldrhi.pkb 115.2 2002/12/13 00:33:20 rpasapul noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_vld_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_vld_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_fr_validations Row
69 --
70 update pqh_fr_validations
71 set
72 validation_id = p_rec.validation_id
73 ,pension_fund_type_code = p_rec.pension_fund_type_code
74 ,pension_fund_id = p_rec.pension_fund_id
75 ,business_group_id = p_rec.business_group_id
76 ,person_id = p_rec.person_id
77 ,request_date = p_rec.request_date
78 ,completion_date = p_rec.completion_date
79 ,previous_employer_id = p_rec.previous_employer_id
80 ,previously_validated_flag = p_rec.previously_validated_flag
81 ,status = p_rec.status
82 ,employer_amount = p_rec.employer_amount
83 ,employer_currency_code = p_rec.employer_currency_code
84 ,employee_amount = p_rec.employee_amount
85 ,employee_currency_code = p_rec.employee_currency_code
86 ,deduction_per_period = p_rec.deduction_per_period
87 ,deduction_currency_code = p_rec.deduction_currency_code
88 ,percent_of_salary = p_rec.percent_of_salary
89 ,object_version_number = p_rec.object_version_number
90 where validation_id = p_rec.validation_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_vld_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_vld_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_vld_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_vld_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_effective_date in date
195 ,p_rec in pqh_vld_shd.g_rec_type
196 ) is
197 --
198 l_proc varchar2(72) := g_package||'post_update';
199 --
200 Begin
201 hr_utility.set_location('Entering:'||l_proc, 5);
202 begin
203 --
204 pqh_vld_rku.after_update
205 (p_effective_date => p_effective_date
206 ,p_validation_id
207 => p_rec.validation_id
208 ,p_pension_fund_type_code
209 => p_rec.pension_fund_type_code
210 ,p_pension_fund_id
211 => p_rec.pension_fund_id
212 ,p_business_group_id
213 => p_rec.business_group_id
214 ,p_person_id
215 => p_rec.person_id
216 ,p_request_date
217 => p_rec.request_date
218 ,p_completion_date
219 => p_rec.completion_date
220 ,p_previous_employer_id
221 => p_rec.previous_employer_id
222 ,p_previously_validated_flag
223 => p_rec.previously_validated_flag
224 ,p_status
225 => p_rec.status
226 ,p_employer_amount
227 => p_rec.employer_amount
228 ,p_employer_currency_code
229 => p_rec.employer_currency_code
230 ,p_employee_amount
231 => p_rec.employee_amount
232 ,p_employee_currency_code
233 => p_rec.employee_currency_code
234 ,p_deduction_per_period
235 => p_rec.deduction_per_period
236 ,p_deduction_currency_code
237 => p_rec.deduction_currency_code
238 ,p_percent_of_salary
239 => p_rec.percent_of_salary
240 ,p_object_version_number
241 => p_rec.object_version_number
242 ,p_pension_fund_type_code_o
243 => pqh_vld_shd.g_old_rec.pension_fund_type_code
244 ,p_pension_fund_id_o
245 => pqh_vld_shd.g_old_rec.pension_fund_id
246 ,p_business_group_id_o
247 => pqh_vld_shd.g_old_rec.business_group_id
248 ,p_person_id_o
249 => pqh_vld_shd.g_old_rec.person_id
250 ,p_request_date_o
251 => pqh_vld_shd.g_old_rec.request_date
252 ,p_completion_date_o
253 => pqh_vld_shd.g_old_rec.completion_date
254 ,p_previous_employer_id_o
255 => pqh_vld_shd.g_old_rec.previous_employer_id
256 ,p_previously_validated_flag_o
257 => pqh_vld_shd.g_old_rec.previously_validated_flag
258 ,p_status_o
259 => pqh_vld_shd.g_old_rec.status
260 ,p_employer_amount_o
261 => pqh_vld_shd.g_old_rec.employer_amount
262 ,p_employer_currency_code_o
263 => pqh_vld_shd.g_old_rec.employer_currency_code
264 ,p_employee_amount_o
265 => pqh_vld_shd.g_old_rec.employee_amount
266 ,p_employee_currency_code_o
267 => pqh_vld_shd.g_old_rec.employee_currency_code
268 ,p_deduction_per_period_o
269 => pqh_vld_shd.g_old_rec.deduction_per_period
270 ,p_deduction_currency_code_o
271 => pqh_vld_shd.g_old_rec.deduction_currency_code
272 ,p_percent_of_salary_o
273 => pqh_vld_shd.g_old_rec.percent_of_salary
274 ,p_object_version_number_o
275 => pqh_vld_shd.g_old_rec.object_version_number
276 );
277 --
278 exception
279 --
280 when hr_api.cannot_find_prog_unit then
281 --
282 hr_api.cannot_find_prog_unit_error
283 (p_module_name => 'PQH_FR_VALIDATIONS'
284 ,p_hook_type => 'AU');
285 --
286 end;
287 --
288 hr_utility.set_location(' Leaving:'||l_proc, 10);
289 End post_update;
290 --
291 -- ----------------------------------------------------------------------------
292 -- |-----------------------------< convert_defs >-----------------------------|
293 -- ----------------------------------------------------------------------------
294 -- {Start Of Comments}
295 --
296 -- Description:
297 -- The Convert_Defs procedure has one very important function:
298 -- It must return the record structure for the row with all system defaulted
299 -- values converted into its corresponding parameter value for update. When
300 -- we attempt to update a row through the Upd process , certain
301 -- parameters can be defaulted which enables flexibility in the calling of
302 -- the upd process (e.g. only attributes which need to be updated need to be
303 -- specified). For the upd process to determine which attributes
304 -- have NOT been specified we need to check if the parameter has a reserved
305 -- system default value. Therefore, for all parameters which have a
306 -- corresponding reserved system default mechanism specified we need to
307 -- check if a system default is being used. If a system default is being
308 -- used then we convert the defaulted value into its corresponding attribute
309 -- value held in the g_old_rec data structure.
310 --
311 -- Prerequisites:
312 -- This private function can only be called from the upd process.
313 --
314 -- In Parameters:
315 -- A Pl/Sql record structure.
316 --
317 -- Post Success:
318 -- The record structure will be returned with all system defaulted parameter
319 -- values converted into its current row attribute value.
320 --
321 -- Post Failure:
322 -- No direct error handling is required within this function. Any possible
323 -- errors within this procedure will be a PL/SQL value error due to
324 -- conversion of datatypes or data lengths.
325 --
326 -- Developer Implementation Notes:
327 -- None.
328 --
329 -- Access Status:
330 -- Internal Row Handler Use Only.
331 --
332 -- {End Of Comments}
333 -- ----------------------------------------------------------------------------
334 Procedure convert_defs
335 (p_rec in out nocopy pqh_vld_shd.g_rec_type
336 ) is
337 --
338 Begin
339 --
340 -- We must now examine each argument value in the
341 -- p_rec plsql record structure
342 -- to see if a system default is being used. If a system default
343 -- is being used then we must set to the 'current' argument value.
344 --
345 If (p_rec.pension_fund_type_code = hr_api.g_varchar2) then
346 p_rec.pension_fund_type_code :=
347 pqh_vld_shd.g_old_rec.pension_fund_type_code;
348 End If;
349 If (p_rec.pension_fund_id = hr_api.g_number) then
350 p_rec.pension_fund_id :=
351 pqh_vld_shd.g_old_rec.pension_fund_id;
352 End If;
353 If (p_rec.business_group_id = hr_api.g_number) then
354 p_rec.business_group_id :=
355 pqh_vld_shd.g_old_rec.business_group_id;
356 End If;
357 If (p_rec.person_id = hr_api.g_number) then
358 p_rec.person_id :=
359 pqh_vld_shd.g_old_rec.person_id;
360 End If;
361 If (p_rec.request_date = hr_api.g_date) then
362 p_rec.request_date :=
363 pqh_vld_shd.g_old_rec.request_date;
364 End If;
365 If (p_rec.completion_date = hr_api.g_date) then
366 p_rec.completion_date :=
367 pqh_vld_shd.g_old_rec.completion_date;
368 End If;
369 If (p_rec.previous_employer_id = hr_api.g_number) then
370 p_rec.previous_employer_id :=
371 pqh_vld_shd.g_old_rec.previous_employer_id;
372 End If;
373 If (p_rec.previously_validated_flag = hr_api.g_varchar2) then
374 p_rec.previously_validated_flag :=
375 pqh_vld_shd.g_old_rec.previously_validated_flag;
376 End If;
377 If (p_rec.status = hr_api.g_varchar2) then
378 p_rec.status :=
379 pqh_vld_shd.g_old_rec.status;
380 End If;
381 If (p_rec.employer_amount = hr_api.g_number) then
382 p_rec.employer_amount :=
383 pqh_vld_shd.g_old_rec.employer_amount;
384 End If;
385 If (p_rec.employer_currency_code = hr_api.g_varchar2) then
386 p_rec.employer_currency_code :=
387 pqh_vld_shd.g_old_rec.employer_currency_code;
388 End If;
389 If (p_rec.employee_amount = hr_api.g_number) then
390 p_rec.employee_amount :=
391 pqh_vld_shd.g_old_rec.employee_amount;
392 End If;
393 If (p_rec.employee_currency_code = hr_api.g_varchar2) then
394 p_rec.employee_currency_code :=
395 pqh_vld_shd.g_old_rec.employee_currency_code;
396 End If;
400 End If;
397 If (p_rec.deduction_per_period = hr_api.g_number) then
398 p_rec.deduction_per_period :=
399 pqh_vld_shd.g_old_rec.deduction_per_period;
401 If (p_rec.deduction_currency_code = hr_api.g_varchar2) then
402 p_rec.deduction_currency_code :=
403 pqh_vld_shd.g_old_rec.deduction_currency_code;
404 End If;
405 If (p_rec.percent_of_salary = hr_api.g_number) then
406 p_rec.percent_of_salary :=
407 pqh_vld_shd.g_old_rec.percent_of_salary;
408 End If;
409 --
410 End convert_defs;
411 --
412 -- ----------------------------------------------------------------------------
413 -- |---------------------------------< upd >----------------------------------|
414 -- ----------------------------------------------------------------------------
415 Procedure upd
416 (p_effective_date in date
417 ,p_rec in out nocopy pqh_vld_shd.g_rec_type
418 ) is
419 --
420 l_proc varchar2(72) := g_package||'upd';
421 --
422 Begin
423 hr_utility.set_location('Entering:'||l_proc, 5);
424 --
425 -- We must lock the row which we need to update.
426 --
427 pqh_vld_shd.lck
428 (p_rec.validation_id
429 ,p_rec.object_version_number
430 );
431 --
432 -- 1. During an update system defaults are used to determine if
433 -- arguments have been defaulted or not. We must therefore
434 -- derive the full record structure values to be updated.
435 --
436 -- 2. Call the supporting update validate operations.
437 --
438 convert_defs(p_rec);
439 pqh_vld_bus.update_validate
440 (p_effective_date
441 ,p_rec
442 );
443 --
444 -- Call to raise any errors on multi-message list
445 hr_multi_message.end_validation_set;
446 --
447 -- Call the supporting pre-update operation
448 --
449 pqh_vld_upd.pre_update(p_rec);
450 --
451 -- Update the row.
452 --
453 pqh_vld_upd.update_dml(p_rec);
454 --
455 -- Call the supporting post-update operation
456 --
457 pqh_vld_upd.post_update
458 (p_effective_date
459 ,p_rec
460 );
461 --
462 -- Call to raise any errors on multi-message list
463 hr_multi_message.end_validation_set;
464 End upd;
465 --
466 -- ----------------------------------------------------------------------------
467 -- |---------------------------------< upd >----------------------------------|
468 -- ----------------------------------------------------------------------------
469 Procedure upd
470 (p_effective_date in date
471 ,p_validation_id in number
472 ,p_object_version_number in out nocopy number
473 ,p_pension_fund_type_code in varchar2 default hr_api.g_varchar2
474 ,p_pension_fund_id in number default hr_api.g_number
475 ,p_business_group_id in number default hr_api.g_number
476 ,p_person_id in number default hr_api.g_number
477 ,p_previously_validated_flag in varchar2 default hr_api.g_varchar2
478 ,p_request_date in date default hr_api.g_date
479 ,p_completion_date in date default hr_api.g_date
480 ,p_previous_employer_id in number default hr_api.g_number
481 ,p_status in varchar2 default hr_api.g_varchar2
482 ,p_employer_amount in number default hr_api.g_number
483 ,p_employer_currency_code in varchar2 default hr_api.g_varchar2
484 ,p_employee_amount in number default hr_api.g_number
485 ,p_employee_currency_code in varchar2 default hr_api.g_varchar2
486 ,p_deduction_per_period in number default hr_api.g_number
487 ,p_deduction_currency_code in varchar2 default hr_api.g_varchar2
488 ,p_percent_of_salary in number default hr_api.g_number
489 ) is
490 --
491 l_rec pqh_vld_shd.g_rec_type;
492 l_proc varchar2(72) := g_package||'upd';
493 --
494 Begin
495 hr_utility.set_location('Entering:'||l_proc, 5);
496 --
497 -- Call conversion function to turn arguments into the
498 -- l_rec structure.
499 --
500 l_rec :=
501 pqh_vld_shd.convert_args
502 (p_validation_id
503 ,p_pension_fund_type_code
504 ,p_pension_fund_id
505 ,p_business_group_id
506 ,p_person_id
507 ,p_request_date
508 ,p_completion_date
509 ,p_previous_employer_id
510 ,p_previously_validated_flag
511 ,p_status
512 ,p_employer_amount
513 ,p_employer_currency_code
514 ,p_employee_amount
515 ,p_employee_currency_code
516 ,p_deduction_per_period
517 ,p_deduction_currency_code
518 ,p_percent_of_salary
519 ,p_object_version_number
520 );
521 --
522 -- Having converted the arguments into the
523 -- plsql record structure we call the corresponding record
524 -- business process.
525 --
526 pqh_vld_upd.upd
527 (p_effective_date
528 ,l_rec
529 );
530 p_object_version_number := l_rec.object_version_number;
531 --
532 hr_utility.set_location(' Leaving:'||l_proc, 10);
533 End upd;
534 --
535 end pqh_vld_upd;