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