1 Package Body pay_ecu_upd as
2 /* $Header: pyecurhi.pkb 120.2 2006/02/06 05:37 pgongada noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_ecu_upd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |----------------------------< dt_update_dml >-----------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 -- This procedure controls the execution of dml from the datetrack mode
17 -- of CORRECTION only. It is important to note that the object version
18 -- number is only increment by 1 because the datetrack correction is
19 -- soley for one datetracked row.
20 -- This procedure controls the actual dml update logic. The functions of
21 -- this procedure are as follows:
22 -- 1) Get the next object_version_number.
23 -- 2) To set and unset the g_api_dml status as required (as we are about to
24 -- perform dml).
25 -- 3) To update the specified row in the schema using the primary key in
26 -- the predicates.
27 -- 4) To trap any constraint violations that may have occurred.
28 -- 5) To raise any other errors.
29 --
30 -- Prerequisites:
31 -- This is an internal private procedure which must be called from the
32 -- update_dml procedure.
33 --
34 -- In Parameters:
35 -- A Pl/Sql record structure.
36 --
37 -- Post Success:
38 -- The specified row will be updated in the schema.
39 --
40 -- Post Failure:
41 -- On the update dml failure it is important to note that we always reset the
42 -- g_api_dml status to false.
43 -- If a check or unique integrity constraint violation is raised the
44 -- constraint_error procedure will be called.
45 -- If any other error is reported, the error will be raised after the
46 -- g_api_dml status is reset.
47 --
48 -- Developer Implementation Notes:
49 -- The update 'set' arguments list should be modified if any of your
50 -- attributes are not updateable.
51 --
52 -- Access Status:
53 -- Internal Row Handler Use Only.
54 --
55 -- {End Of Comments}
56 -- ----------------------------------------------------------------------------
57 Procedure dt_update_dml
58 (p_rec in out nocopy pay_ecu_shd.g_rec_type
59 ,p_effective_date in date
60 ,p_datetrack_mode in varchar2
61 ,p_validation_start_date in date
62 ,p_validation_end_date in date
63 ) is
64 --
65 l_proc varchar2(72) := g_package||'dt_update_dml';
66 --
67 Begin
68 hr_utility.set_location('Entering:'||l_proc, 5);
69 --
70 If (p_datetrack_mode = hr_api.g_correction) then
71 hr_utility.set_location(l_proc, 10);
72 --
73 -- Because we are updating a row we must get the next object
74 -- version number.
75 --
76 p_rec.object_version_number :=
77 dt_api.get_object_version_number
78 (p_base_table_name => 'pay_element_class_usages_f'
79 ,p_base_key_column => 'element_class_usage_id'
80 ,p_base_key_value => p_rec.element_class_usage_id
81 );
82 --
83 --
84 --
85 -- Update the pay_element_class_usages_f Row
86 --
87 update pay_element_class_usages_f
88 set
89 element_class_usage_id = p_rec.element_class_usage_id
90 ,run_type_id = p_rec.run_type_id
91 ,classification_id = p_rec.classification_id
92 ,business_group_id = p_rec.business_group_id
93 ,legislation_code = p_rec.legislation_code
94 ,object_version_number = p_rec.object_version_number
95 where element_class_usage_id = p_rec.element_class_usage_id
96 and effective_start_date = p_validation_start_date
97 and effective_end_date = p_validation_end_date;
98 --
99 --
100 --
101 -- Set the effective start and end dates
102 --
103 p_rec.effective_start_date := p_validation_start_date;
104 p_rec.effective_end_date := p_validation_end_date;
105 End If;
106 --
107 hr_utility.set_location(' Leaving:'||l_proc, 15);
108 Exception
109 When hr_api.check_integrity_violated Then
110 -- A check constraint has been violated
111 --
112 pay_ecu_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 --
117 pay_ecu_shd.constraint_error
118 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
119 When Others Then
120 --
121 Raise;
122 End dt_update_dml;
123 --
124 -- ----------------------------------------------------------------------------
125 -- |------------------------------< update_dml >------------------------------|
126 -- ----------------------------------------------------------------------------
127 -- {Start Of Comments}
128 --
129 -- Description:
130 -- This procedure calls the dt_update_dml control logic which handles
131 -- the actual datetrack dml.
132 --
133 -- Prerequisites:
134 -- This is an internal private procedure which must be called from the upd
135 -- procedure.
136 --
137 -- In Parameters:
138 -- A Pl/Sql record structre.
139 --
140 -- Post Success:
141 -- Processing contines.
142 --
143 -- Post Failure:
144 -- No specific error handling is required within this procedure.
145 --
146 -- Developer Implementation Notes:
147 -- The update 'set' arguments list should be modified if any of your
148 -- attributes are not updateable.
149 --
150 -- Access Status:
151 -- Internal Row Handler Use Only.
152 --
153 -- {End Of Comments}
154 -- ----------------------------------------------------------------------------
155 Procedure update_dml
156 (p_rec in out nocopy pay_ecu_shd.g_rec_type
157 ,p_effective_date in date
158 ,p_datetrack_mode in varchar2
159 ,p_validation_start_date in date
160 ,p_validation_end_date in date
161 ) is
162 --
163 l_proc varchar2(72) := g_package||'update_dml';
164 --
165 Begin
166 hr_utility.set_location('Entering:'||l_proc, 5);
167 --
168 pay_ecu_upd.dt_update_dml
169 (p_rec => p_rec
170 ,p_effective_date => p_effective_date
171 ,p_datetrack_mode => p_datetrack_mode
172 ,p_validation_start_date => p_validation_start_date
173 ,p_validation_end_date => p_validation_end_date
174 );
175 --
176 hr_utility.set_location(' Leaving:'||l_proc, 10);
177 End update_dml;
178 --
179 -- ----------------------------------------------------------------------------
180 -- |----------------------------< dt_pre_update >-----------------------------|
181 -- ----------------------------------------------------------------------------
182 -- {Start Of Comments}
183 --
184 -- Description:
185 -- The dt_pre_update procedure controls the execution
186 -- of dml for the datetrack modes of: UPDATE, UPDATE_OVERRIDE
187 -- and UPDATE_CHANGE_INSERT only. The execution required is as
188 -- follows:
189 --
190 -- 1) Providing the datetrack update mode is not 'CORRECTION'
191 -- then set the effective end date of the current row (this
192 -- will be the validation_start_date - 1).
193 -- 2) If the datetrack mode is 'UPDATE_OVERRIDE' then call the
194 -- corresponding delete_dml process to delete any future rows
195 -- where the effective_start_date is greater than or equal to
196 -- the validation_start_date.
197 -- 3) Call the insert_dml process to insert the new updated row
198 -- details.
199 --
200 -- Prerequisites:
201 -- This is an internal procedure which is called from the
202 -- pre_update procedure.
203 --
204 -- In Parameters:
205 --
206 -- Post Success:
207 -- Processing continues.
208 --
209 -- Post Failure:
210 -- If an error has occurred, an error message and exception will be raised
211 -- but not handled.
212 --
213 -- Developer Implementation Notes:
214 -- This is an internal procedure which is required by Datetrack. Don't
215 -- remove or modify.
216 --
217 -- Access Status:
218 -- Internal Row Handler Use Only.
219 --
220 -- {End Of Comments}
221 -- ----------------------------------------------------------------------------
222 Procedure dt_pre_update
223 (p_rec in out nocopy pay_ecu_shd.g_rec_type
224 ,p_effective_date in date
225 ,p_datetrack_mode in varchar2
226 ,p_validation_start_date in date
227 ,p_validation_end_date in date
228 ) is
229 --
230 l_proc varchar2(72) := g_package||'dt_pre_update';
231 l_dummy_version_number number;
232 --
233 Begin
234 hr_utility.set_location('Entering:'||l_proc, 5);
235 If (p_datetrack_mode <> hr_api.g_correction) then
236 --
237 -- Update the current effective end date
238 --
239 pay_ecu_shd.upd_effective_end_date
240 (p_effective_date => p_effective_date
241 ,p_base_key_value => p_rec.element_class_usage_id
242 ,p_new_effective_end_date => (p_validation_start_date - 1)
243 ,p_validation_start_date => p_validation_start_date
244 ,p_validation_end_date => p_validation_end_date
245 ,p_object_version_number => l_dummy_version_number
246 );
247 --
248 If (p_datetrack_mode = hr_api.g_update_override) then
249 --
250 -- As the datetrack mode is 'UPDATE_OVERRIDE' then we must
251 -- delete any future rows
252 --
253 pay_ecu_del.delete_dml
254 (p_rec => p_rec
255 ,p_effective_date => p_effective_date
256 ,p_datetrack_mode => p_datetrack_mode
257 ,p_validation_start_date => p_validation_start_date
258 ,p_validation_end_date => p_validation_end_date
259 );
260 End If;
261 --
262 -- We must now insert the updated row
263 --
264 pay_ecu_ins.insert_dml
265 (p_rec => p_rec
266 ,p_effective_date => p_effective_date
267 ,p_datetrack_mode => p_datetrack_mode
268 ,p_validation_start_date => p_validation_start_date
269 ,p_validation_end_date => p_validation_end_date
270 );
271 End If;
272 hr_utility.set_location(' Leaving:'||l_proc, 20);
273 End dt_pre_update;
274 --
275 -- ----------------------------------------------------------------------------
276 -- |------------------------------< pre_update >------------------------------|
277 -- ----------------------------------------------------------------------------
278 -- {Start Of Comments}
279 --
280 -- Description:
281 -- This private procedure contains any processing which is required before
282 -- the update dml.
283 --
284 -- Prerequisites:
285 -- This is an internal procedure which is called from the upd procedure.
286 --
287 -- In Parameters:
288 -- A Pl/Sql record structure.
289 --
290 -- Post Success:
291 -- Processing continues.
292 --
293 -- Post Failure:
294 -- If an error has occurred, an error message and exception will be raised
295 -- but not handled.
296 -- Developer Implementation Notes:
297 -- Any pre-processing required before the update dml is issued should be
298 -- coded within this procedure. It is important to note that any 3rd party
299 -- maintenance should be reviewed before placing in this procedure. The call
300 -- to the dt_update_dml procedure should NOT be removed.
301 --
302 -- Access Status:
303 -- Internal Row Handler Use Only.
304 --
305 -- {End Of Comments}
306 -- ----------------------------------------------------------------------------
307 Procedure pre_update
308 (p_rec in out nocopy pay_ecu_shd.g_rec_type
309 ,p_effective_date in date
310 ,p_datetrack_mode in varchar2
311 ,p_validation_start_date in date
312 ,p_validation_end_date in date
313 ) is
314 --
315 l_proc varchar2(72) := g_package||'pre_update';
316 --
317 Begin
318 hr_utility.set_location('Entering:'||l_proc, 5);
319 --
320 --
321 --
322 dt_pre_update
323 (p_rec => p_rec
324 ,p_effective_date => p_effective_date
325 ,p_datetrack_mode => p_datetrack_mode
326 ,p_validation_start_date => p_validation_start_date
327 ,p_validation_end_date => p_validation_end_date
328 );
329 --
330 hr_utility.set_location(' Leaving:'||l_proc, 10);
331 End pre_update;
332 --
333 -- ----------------------------------------------------------------------------
334 -- |----------------------------< post_update >-------------------------------|
335 -- ----------------------------------------------------------------------------
336 -- {Start Of Comments}
337 --
338 -- Description:
339 -- This private procedure contains any processing which is required after
340 -- the update dml.
341 --
342 -- Prerequisites:
343 -- This is an internal procedure which is called from the upd procedure.
344 --
345 -- In Parameters:
346 -- A Pl/Sql record structure.
347 --
348 -- Post Success:
349 -- Processing continues.
350 --
351 -- Post Failure:
352 -- If an error has occurred, an error message and exception will be raised
353 -- but not handled.
354 --
355 -- Developer Implementation Notes:
356 -- Any post-processing required after the update dml is issued should be
357 -- coded within this procedure. It is important to note that any 3rd party
358 -- maintenance should be reviewed before placing in this procedure.
359 --
360 -- Access Status:
361 -- Internal Row Handler Use Only.
362 --
363 -- {End Of Comments}
364 -- ----------------------------------------------------------------------------
365 Procedure post_update
366 (p_rec in pay_ecu_shd.g_rec_type
367 ,p_effective_date in date
368 ,p_datetrack_mode in varchar2
369 ,p_validation_start_date in date
370 ,p_validation_end_date in date
371 ) is
372 --
373 l_proc varchar2(72) := g_package||'post_update';
374 --
375 Begin
376 hr_utility.set_location('Entering:'||l_proc, 5);
377 begin
378 --
379 pay_ecu_rku.after_update
380 (p_effective_date
381 => p_effective_date
382 ,p_datetrack_mode
383 => p_datetrack_mode
384 ,p_validation_start_date
385 => p_validation_start_date
386 ,p_validation_end_date
387 => p_validation_end_date
388 ,p_element_class_usage_id
389 => p_rec.element_class_usage_id
390 ,p_effective_start_date
391 => p_rec.effective_start_date
392 ,p_effective_end_date
393 => p_rec.effective_end_date
394 ,p_run_type_id
395 => p_rec.run_type_id
396 ,p_classification_id
397 => p_rec.classification_id
398 ,p_business_group_id
399 => p_rec.business_group_id
400 ,p_legislation_code
401 => p_rec.legislation_code
402 ,p_object_version_number
403 => p_rec.object_version_number
404 ,p_effective_start_date_o
405 => pay_ecu_shd.g_old_rec.effective_start_date
406 ,p_effective_end_date_o
407 => pay_ecu_shd.g_old_rec.effective_end_date
408 ,p_run_type_id_o
409 => pay_ecu_shd.g_old_rec.run_type_id
410 ,p_classification_id_o
411 => pay_ecu_shd.g_old_rec.classification_id
412 ,p_business_group_id_o
413 => pay_ecu_shd.g_old_rec.business_group_id
414 ,p_legislation_code_o
415 => pay_ecu_shd.g_old_rec.legislation_code
416 ,p_object_version_number_o
417 => pay_ecu_shd.g_old_rec.object_version_number
418 );
419 --
420 exception
421 --
422 when hr_api.cannot_find_prog_unit then
423 --
424 hr_api.cannot_find_prog_unit_error
425 (p_module_name => 'PAY_ELEMENT_CLASS_USAGES_F'
426 ,p_hook_type => 'AU');
427 --
428 end;
429 --
430 hr_utility.set_location(' Leaving:'||l_proc, 10);
431 End post_update;
432 --
433 -- ----------------------------------------------------------------------------
434 -- |-----------------------------< convert_defs >-----------------------------|
435 -- ----------------------------------------------------------------------------
436 -- {Start Of Comments}
437 --
438 -- Description:
439 -- The Convert_Defs procedure has one very important function:
440 -- It must return the record structure for the row with all system defaulted
441 -- values converted into its corresponding parameter value for update. When
442 -- we attempt to update a row through the Upd process , certain
443 -- parameters can be defaulted which enables flexibility in the calling of
444 -- the upd process (e.g. only attributes which need to be updated need to be
445 -- specified). For the upd process to determine which attributes
446 -- have NOT been specified we need to check if the parameter has a reserved
447 -- system default value. Therefore, for all parameters which have a
448 -- corresponding reserved system default mechanism specified we need to
449 -- check if a system default is being used. If a system default is being
450 -- used then we convert the defaulted value into its corresponding attribute
451 -- value held in the g_old_rec data structure.
452 --
453 -- Prerequisites:
454 -- This private function can only be called from the upd process.
455 --
456 -- In Parameters:
457 -- A Pl/Sql record structure.
458 --
459 -- Post Success:
460 -- The record structure will be returned with all system defaulted parameter
461 -- values converted into its current row attribute value.
462 --
463 -- Post Failure:
464 -- No direct error handling is required within this function. Any possible
465 -- errors within this procedure will be a PL/SQL value error due to
466 -- conversion of datatypes or data lengths.
467 --
468 -- Developer Implementation Notes:
469 -- None.
470 --
471 -- Access Status:
472 -- Internal Row Handler Use Only.
473 --
474 -- {End Of Comments}
475 -- ----------------------------------------------------------------------------
476 Procedure convert_defs
477 (p_rec in out nocopy pay_ecu_shd.g_rec_type
478 ) is
479 --
480 Begin
481 --
482 -- We must now examine each argument value in the
483 -- p_rec plsql record structure
484 -- to see if a system default is being used. If a system default
485 -- is being used then we must set to the 'current' argument value.
486 --
487 If (p_rec.run_type_id = hr_api.g_number) then
488 p_rec.run_type_id :=
489 pay_ecu_shd.g_old_rec.run_type_id;
490 End If;
491 If (p_rec.classification_id = hr_api.g_number) then
492 p_rec.classification_id :=
493 pay_ecu_shd.g_old_rec.classification_id;
494 End If;
495 If (p_rec.business_group_id = hr_api.g_number) then
496 p_rec.business_group_id :=
497 pay_ecu_shd.g_old_rec.business_group_id;
498 End If;
499 If (p_rec.legislation_code = hr_api.g_varchar2) then
500 p_rec.legislation_code :=
501 pay_ecu_shd.g_old_rec.legislation_code;
502 End If;
503 --
504 End convert_defs;
505 --
506 -- ----------------------------------------------------------------------------
507 -- |---------------------------------< upd >----------------------------------|
508 -- ----------------------------------------------------------------------------
509 Procedure upd
510 (p_effective_date in date
511 ,p_datetrack_mode in varchar2
512 ,p_rec in out nocopy pay_ecu_shd.g_rec_type
513 ) is
514 --
515 l_proc varchar2(72) := g_package||'upd';
516 l_validation_start_date date;
517 l_validation_end_date date;
518 --
519 Begin
520 hr_utility.set_location('Entering:'||l_proc, 5);
521 --
522 -- Ensure that the DateTrack update mode is valid
523 --
524 dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode);
525 --
526 -- We must lock the row which we need to update.
527 --
528 pay_ecu_shd.lck
529 (p_effective_date => p_effective_date
530 ,p_datetrack_mode => p_datetrack_mode
531 ,p_element_class_usage_id => p_rec.element_class_usage_id
532 ,p_object_version_number => p_rec.object_version_number
533 ,p_validation_start_date => l_validation_start_date
534 ,p_validation_end_date => l_validation_end_date
535 );
536 --
537 -- 1. During an update system defaults are used to determine if
538 -- arguments have been defaulted or not. We must therefore
539 -- derive the full record structure values to be updated.
540 --
541 -- 2. Call the supporting update validate operations.
542 --
543 pay_ecu_upd.convert_defs(p_rec);
544 --
545 pay_ecu_bus.update_validate
546 (p_rec => p_rec
547 ,p_effective_date => p_effective_date
548 ,p_datetrack_mode => p_datetrack_mode
549 ,p_validation_start_date => l_validation_start_date
550 ,p_validation_end_date => l_validation_end_date
551 );
552 --
553 -- Call to raise any errors on multi-message list
554 hr_multi_message.end_validation_set;
555 --
556 -- Call the supporting pre-update operation
557 --
558 pre_update
559 (p_rec => p_rec
560 ,p_effective_date => p_effective_date
561 ,p_datetrack_mode => p_datetrack_mode
562 ,p_validation_start_date => l_validation_start_date
563 ,p_validation_end_date => l_validation_end_date
564 );
565 --
566 -- Update the row.
567 --
568 update_dml
569 (p_rec => p_rec
570 ,p_effective_date => p_effective_date
571 ,p_datetrack_mode => p_datetrack_mode
572 ,p_validation_start_date => l_validation_start_date
573 ,p_validation_end_date => l_validation_end_date
574 );
575 --
576 -- Call the supporting post-update operation
577 --
578 post_update
579 (p_rec => p_rec
580 ,p_effective_date => p_effective_date
581 ,p_datetrack_mode => p_datetrack_mode
582 ,p_validation_start_date => l_validation_start_date
583 ,p_validation_end_date => l_validation_end_date
584 );
585 --
586 -- Call to raise any errors on multi-message list
587 hr_multi_message.end_validation_set;
588 End upd;
589 --
590 -- ----------------------------------------------------------------------------
591 -- |------------------------------< upd >-------------------------------------|
592 -- ----------------------------------------------------------------------------
593 Procedure upd
594 (p_effective_date in date
595 ,p_datetrack_mode in varchar2
596 ,p_element_class_usage_id in number
597 ,p_object_version_number in out nocopy number
598 ,p_run_type_id in number default hr_api.g_number
599 ,p_classification_id in number default hr_api.g_number
600 ,p_business_group_id in number default hr_api.g_number
601 ,p_legislation_code in varchar2 default hr_api.g_varchar2
602 ,p_effective_start_date out nocopy date
603 ,p_effective_end_date out nocopy date
604 ) is
605 --
606 l_rec pay_ecu_shd.g_rec_type;
607 l_proc varchar2(72) := g_package||'upd';
608 --
609 Begin
610 hr_utility.set_location('Entering:'||l_proc, 5);
611 --
612 -- Call conversion function to turn arguments into the
613 -- l_rec structure.
614 --
615 l_rec :=
616 pay_ecu_shd.convert_args
617 (p_element_class_usage_id
618 ,null
619 ,null
620 ,p_run_type_id
621 ,p_classification_id
622 ,p_business_group_id
623 ,p_legislation_code
624 ,p_object_version_number
625 );
626 --
627 -- Having converted the arguments into the
628 -- plsql record structure we call the corresponding record
629 -- business process.
630 --
631 pay_ecu_upd.upd
632 (p_effective_date
633 ,p_datetrack_mode
634 ,l_rec
635 );
636 --
637 -- Set the out parameters
638 --
639 p_object_version_number := l_rec.object_version_number;
640 p_effective_start_date := l_rec.effective_start_date;
641 p_effective_end_date := l_rec.effective_end_date;
642 --
643 --
644 hr_utility.set_location(' Leaving:'||l_proc, 10);
645 End upd;
646 --
647 end pay_ecu_upd;
[Home] [Help]