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