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