4 -- ----------------------------------------------------------------------------
1 Package Body pay_pga_upd as
2 /* $Header: pypgarhi.pkb 120.0 2005/09/29 10:53 tvankayl noship $ */
3 --
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_pga_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).
28 -- 5) To raise any other errors.
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.
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_pga_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_payment_gl_accounts_f'
79 ,p_base_key_column => 'pay_gl_account_id'
80 ,p_base_key_value => p_rec.pay_gl_account_id
81 );
82 --
83 --
84 --
85 -- Update the pay_payment_gl_accounts_f Row
86 --
87 update pay_payment_gl_accounts_f
88 set
89 pay_gl_account_id = p_rec.pay_gl_account_id
90 ,set_of_books_id = p_rec.set_of_books_id
91 ,gl_cash_ac_id = p_rec.gl_cash_ac_id
92 ,gl_cash_clearing_ac_id = p_rec.gl_cash_clearing_ac_id
93 ,gl_control_ac_id = p_rec.gl_control_ac_id
94 ,gl_error_ac_id = p_rec.gl_error_ac_id
95 ,external_account_id = p_rec.external_account_id
96 ,org_payment_method_id = p_rec.org_payment_method_id
97 ,object_version_number = p_rec.object_version_number
98 where pay_gl_account_id = p_rec.pay_gl_account_id
99 and effective_start_date = p_validation_start_date
100 and effective_end_date = p_validation_end_date;
101 --
102 --
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 --
115 pay_pga_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 --
120 pay_pga_shd.constraint_error
121 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
122 When Others Then
123 --
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_pga_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_pga_upd.dt_update_dml
172 (p_rec => p_rec
176 ,p_validation_end_date => p_validation_end_date
173 ,p_effective_date => p_effective_date
174 ,p_datetrack_mode => p_datetrack_mode
175 ,p_validation_start_date => p_validation_start_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_pga_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_pga_shd.upd_effective_end_date
243 (p_effective_date => p_effective_date
244 ,p_base_key_value => p_rec.pay_gl_account_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_pga_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_pga_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_pga_shd.g_rec_type
315 ,p_validation_end_date in date
312 ,p_effective_date in date
313 ,p_datetrack_mode in varchar2
314 ,p_validation_start_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_pga_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 begin
381 --
382 null;
383 --
384 end;
385 --
386 hr_utility.set_location(' Leaving:'||l_proc, 10);
387 End post_update;
388 --
389 -- ----------------------------------------------------------------------------
390 -- |-----------------------------< convert_defs >-----------------------------|
391 -- ----------------------------------------------------------------------------
392 -- {Start Of Comments}
393 --
394 -- Description:
395 -- The Convert_Defs procedure has one very important function:
396 -- It must return the record structure for the row with all system defaulted
397 -- values converted into its corresponding parameter value for update. When
398 -- we attempt to update a row through the Upd process , certain
399 -- parameters can be defaulted which enables flexibility in the calling of
400 -- the upd process (e.g. only attributes which need to be updated need to be
401 -- specified). For the upd process to determine which attributes
402 -- have NOT been specified we need to check if the parameter has a reserved
403 -- system default value. Therefore, for all parameters which have a
404 -- corresponding reserved system default mechanism specified we need to
405 -- check if a system default is being used. If a system default is being
406 -- used then we convert the defaulted value into its corresponding attribute
407 -- value held in the g_old_rec data structure.
408 --
409 -- Prerequisites:
410 -- This private function can only be called from the upd process.
411 --
412 -- In Parameters:
413 -- A Pl/Sql record structure.
414 --
415 -- Post Success:
416 -- The record structure will be returned with all system defaulted parameter
417 -- values converted into its current row attribute value.
418 --
419 -- Post Failure:
420 -- No direct error handling is required within this function. Any possible
421 -- errors within this procedure will be a PL/SQL value error due to
422 -- conversion of datatypes or data lengths.
423 --
424 -- Developer Implementation Notes:
425 -- None.
426 --
427 -- Access Status:
428 -- Internal Row Handler Use Only.
429 --
430 -- {End Of Comments}
431 -- ----------------------------------------------------------------------------
432 Procedure convert_defs
433 (p_rec in out nocopy pay_pga_shd.g_rec_type
434 ) is
435 --
436 Begin
437 --
438 -- We must now examine each argument value in the
439 -- p_rec plsql record structure
440 -- to see if a system default is being used. If a system default
441 -- is being used then we must set to the 'current' argument value.
442 --
443 If (p_rec.set_of_books_id = hr_api.g_number) then
444 p_rec.set_of_books_id :=
445 pay_pga_shd.g_old_rec.set_of_books_id;
446 End If;
447 If (p_rec.gl_cash_ac_id = hr_api.g_number) then
448 p_rec.gl_cash_ac_id :=
449 pay_pga_shd.g_old_rec.gl_cash_ac_id;
450 End If;
451 If (p_rec.gl_cash_clearing_ac_id = hr_api.g_number) then
452 p_rec.gl_cash_clearing_ac_id :=
453 pay_pga_shd.g_old_rec.gl_cash_clearing_ac_id;
454 End If;
455 If (p_rec.gl_control_ac_id = hr_api.g_number) then
459 If (p_rec.gl_error_ac_id = hr_api.g_number) then
456 p_rec.gl_control_ac_id :=
457 pay_pga_shd.g_old_rec.gl_control_ac_id;
458 End If;
460 p_rec.gl_error_ac_id :=
461 pay_pga_shd.g_old_rec.gl_error_ac_id;
462 End If;
463 If (p_rec.external_account_id = hr_api.g_number) then
464 p_rec.external_account_id :=
465 pay_pga_shd.g_old_rec.external_account_id;
466 End If;
467 If (p_rec.org_payment_method_id = hr_api.g_number) then
468 p_rec.org_payment_method_id :=
469 pay_pga_shd.g_old_rec.org_payment_method_id;
470 End If;
471 --
472 End convert_defs;
473 --
474 -- ----------------------------------------------------------------------------
475 -- |---------------------------------< upd >----------------------------------|
476 -- ----------------------------------------------------------------------------
477 Procedure upd
478 (p_effective_date in date
479 ,p_datetrack_mode in varchar2
480 ,p_rec in out nocopy pay_pga_shd.g_rec_type
481 ) is
482 --
483 l_proc varchar2(72) := g_package||'upd';
484 l_validation_start_date date;
485 l_validation_end_date date;
486 --
487 Begin
488 hr_utility.set_location('Entering:'||l_proc, 5);
489 --
490 -- Ensure that the DateTrack update mode is valid
491 --
492 dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode);
493 --
494 -- We must lock the row which we need to update.
495 --
496 pay_pga_shd.lck
497 (p_effective_date => p_effective_date
498 ,p_datetrack_mode => p_datetrack_mode
502 ,p_validation_end_date => l_validation_end_date
499 ,p_pay_gl_account_id => p_rec.pay_gl_account_id
500 ,p_object_version_number => p_rec.object_version_number
501 ,p_validation_start_date => l_validation_start_date
503 );
504 --
505 -- 1. During an update system defaults are used to determine if
506 -- arguments have been defaulted or not. We must therefore
507 -- derive the full record structure values to be updated.
508 --
509 -- 2. Call the supporting update validate operations.
510 --
511 pay_pga_upd.convert_defs(p_rec);
512 --
513 pay_pga_bus.update_validate
514 (p_rec => p_rec
515 ,p_effective_date => p_effective_date
516 ,p_datetrack_mode => p_datetrack_mode
517 ,p_validation_start_date => l_validation_start_date
518 ,p_validation_end_date => l_validation_end_date
519 );
520 --
521 -- Call to raise any errors on multi-message list
522 hr_multi_message.end_validation_set;
523 --
524 -- Call the supporting pre-update operation
525 --
526 pre_update
527 (p_rec => p_rec
528 ,p_effective_date => p_effective_date
529 ,p_datetrack_mode => p_datetrack_mode
530 ,p_validation_start_date => l_validation_start_date
531 ,p_validation_end_date => l_validation_end_date
532 );
533 --
534 -- Update the row.
535 --
536 update_dml
537 (p_rec => p_rec
538 ,p_effective_date => p_effective_date
539 ,p_datetrack_mode => p_datetrack_mode
540 ,p_validation_start_date => l_validation_start_date
541 ,p_validation_end_date => l_validation_end_date
542 );
543 --
544 -- Call the supporting post-update operation
545 --
546 post_update
547 (p_rec => p_rec
548 ,p_effective_date => p_effective_date
549 ,p_datetrack_mode => p_datetrack_mode
550 ,p_validation_start_date => l_validation_start_date
551 ,p_validation_end_date => l_validation_end_date
552 );
553 --
554 -- Call to raise any errors on multi-message list
555 hr_multi_message.end_validation_set;
556 End upd;
557 --
558 -- ----------------------------------------------------------------------------
559 -- |------------------------------< upd >-------------------------------------|
560 -- ----------------------------------------------------------------------------
561 Procedure upd
562 (p_effective_date in date
563 ,p_datetrack_mode in varchar2
564 ,p_pay_gl_account_id in number
565 ,p_object_version_number in out nocopy number
566 ,p_set_of_books_id in number default hr_api.g_number
567 ,p_gl_cash_ac_id in number default hr_api.g_number
568 ,p_gl_cash_clearing_ac_id in number default hr_api.g_number
569 ,p_gl_control_ac_id in number default hr_api.g_number
570 ,p_gl_error_ac_id in number default hr_api.g_number
571 ,p_external_account_id in number default hr_api.g_number
572 ,p_org_payment_method_id in number default hr_api.g_number
573 ,p_effective_start_date out nocopy date
574 ,p_effective_end_date out nocopy date
575 ) is
576 --
577 l_rec pay_pga_shd.g_rec_type;
578 l_proc varchar2(72) := g_package||'upd';
579 --
580 Begin
581 hr_utility.set_location('Entering:'||l_proc, 5);
582 --
583 -- Call conversion function to turn arguments into the
584 -- l_rec structure.
585 --
586 l_rec :=
587 pay_pga_shd.convert_args
588 (p_pay_gl_account_id
589 ,null
590 ,null
591 ,p_set_of_books_id
592 ,p_gl_cash_ac_id
593 ,p_gl_cash_clearing_ac_id
594 ,p_gl_control_ac_id
595 ,p_gl_error_ac_id
596 ,p_external_account_id
597 ,p_org_payment_method_id
598 ,p_object_version_number
599 );
600 --
601 -- Having converted the arguments into the
602 -- plsql record structure we call the corresponding record
603 -- business process.
604 --
605 pay_pga_upd.upd
606 (p_effective_date
607 ,p_datetrack_mode
608 ,l_rec
609 );
610 --
611 -- Set the out parameters
612 --
613 p_object_version_number := l_rec.object_version_number;
614 p_effective_start_date := l_rec.effective_start_date;
615 p_effective_end_date := l_rec.effective_end_date;
616 --
617 --
618 hr_utility.set_location(' Leaving:'||l_proc, 10);
619 End upd;
620 --
621 end pay_pga_upd;