1 Package Body ghr_dut_upd as
2 /* $Header: ghdutrhi.pkb 120.0.12000000.1 2007/01/18 13:42:07 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ghr_dut_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 this
21 -- 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 structre.
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 ghr_dut_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) is
63 --
64 l_proc varchar2(72) := g_package||'dt_update_dml';
65 --
66 Begin
67 hr_utility.set_location('Entering:'||l_proc, 5);
68 --
69 If (p_datetrack_mode = 'CORRECTION') then
70 hr_utility.set_location(l_proc, 10);
71 --
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 => 'ghr_duty_stations_f',
79 p_base_key_column => 'duty_station_id',
80 p_base_key_value => p_rec.duty_station_id);
81 --
82 ghr_dut_shd.g_api_dml := true; -- Set the api dml status
83 --
84 -- Update the ghr_duty_stations_f Row
85 --
86 update ghr_duty_stations_f
87 set
88 duty_station_id = p_rec.duty_station_id,
89 locality_pay_area_id = p_rec.locality_pay_area_id,
90 leo_pay_area_code = p_rec.leo_pay_area_code,
91 name = p_rec.name,
92 duty_station_code = p_rec.duty_station_code,
93 msa_code = p_rec.msa_code ,
94 cmsa_code = p_rec.cmsa_code ,
95 state_or_country_code = p_rec.state_or_country_code,
96 county_code = p_rec.county_code,
97 is_duty_station = p_rec.is_duty_station
98 where duty_station_id = p_rec.duty_station_id
99 and effective_start_date = p_validation_start_date
100 and effective_end_date = p_validation_end_date;
101 --
102 ghr_dut_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 ghr_dut_shd.g_api_dml := false; -- Unset the api dml status
115 ghr_dut_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 ghr_dut_shd.g_api_dml := false; -- Unset the api dml status
120 ghr_dut_shd.constraint_error
121 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
122 When Others Then
123 ghr_dut_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 ghr_dut_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) is
164 --
165 l_proc varchar2(72) := g_package||'update_dml';
166 --
167 Begin
168 hr_utility.set_location('Entering:'||l_proc, 5);
169 --
170 dt_update_dml(p_rec => p_rec,
171 p_effective_date => p_effective_date,
172 p_datetrack_mode => p_datetrack_mode,
173 p_validation_start_date => p_validation_start_date,
174 p_validation_end_date => p_validation_end_date);
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 ghr_dut_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) is
228 --
229 l_proc varchar2(72) := g_package||'dt_pre_update';
230 l_dummy_version_number number;
231 --
232 Begin
233 hr_utility.set_location('Entering:'||l_proc, 5);
234 If (p_datetrack_mode <> 'CORRECTION') then
235 hr_utility.set_location(l_proc, 10);
236 --
237 -- Update the current effective end date
238 --
239 ghr_dut_shd.upd_effective_end_date
240 (p_effective_date => p_effective_date,
241 p_base_key_value => p_rec.duty_station_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 => p_rec.object_version_number);
246 --
247 If (p_datetrack_mode = 'UPDATE_OVERRIDE') then
248 hr_utility.set_location(l_proc, 15);
249 --
250 -- As the datetrack mode is 'UPDATE_OVERRIDE' then we must
251 -- delete any future rows
252 --
253 ghr_dut_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 End If;
260 hr_utility.set_location(l_proc, 20);
261 --
262 -- We must now insert the updated row
263 --
264 ghr_dut_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 End If;
271 hr_utility.set_location(' Leaving:'||l_proc, 20);
272 End dt_pre_update;
273 --
274 -- ----------------------------------------------------------------------------
275 -- |------------------------------< pre_update >------------------------------|
276 -- ----------------------------------------------------------------------------
277 -- {Start Of Comments}
278 --
279 -- Description:
280 -- This private procedure contains any processing which is required before
281 -- the update dml.
282 --
283 -- Prerequisites:
284 -- This is an internal procedure which is called from the upd procedure.
285 --
286 -- In Parameters:
287 -- A Pl/Sql record structre.
288 --
289 -- Post Success:
290 -- Processing continues.
291 --
292 -- Post Failure:
293 -- If an error has occurred, an error message and exception will be raised
294 -- but not handled.
295 --
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 ghr_dut_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) is
313 --
314 l_proc varchar2(72) := g_package||'pre_update';
315 --
316 Begin
317 hr_utility.set_location('Entering:'||l_proc, 5);
318 --
319 --
320 dt_pre_update
321 (p_rec => p_rec,
322 p_effective_date => p_effective_date,
323 p_datetrack_mode => p_datetrack_mode,
324 p_validation_start_date => p_validation_start_date,
325 p_validation_end_date => p_validation_end_date);
326 --
327 hr_utility.set_location(' Leaving:'||l_proc, 10);
328 End pre_update;
329 --
330 -- ----------------------------------------------------------------------------
331 -- |-----------------------------< post_update >------------------------------|
332 -- ----------------------------------------------------------------------------
333 -- {Start Of Comments}
334 --
335 -- Description:
336 -- This private procedure contains any processing which is required after the
337 -- update dml.
338 --
339 -- Prerequisites:
340 -- This is an internal procedure which is called from the upd procedure.
341 --
342 -- In Parameters:
343 -- A Pl/Sql record structre.
344 --
345 -- Post Success:
346 -- Processing continues.
347 --
348 -- Post Failure:
349 -- If an error has occurred, an error message and exception will be raised
350 -- but not handled.
351 --
352 -- Developer Implementation Notes:
353 -- Any post-processing required after the update dml is issued should be
354 -- coded within this procedure. It is important to note that any 3rd party
355 -- maintenance should be reviewed before placing in this procedure.
356 --
357 -- Access Status:
358 -- Internal Row Handler Use Only.
359 --
360 -- {End Of Comments}
361 -- ----------------------------------------------------------------------------
362 Procedure post_update
363 (p_rec in ghr_dut_shd.g_rec_type,
364 p_effective_date in date,
365 p_datetrack_mode in varchar2,
366 p_validation_start_date in date,
367 p_validation_end_date in date) is
368 --
369 l_proc varchar2(72) := g_package||'post_update';
370 --
371 Begin
372 hr_utility.set_location('Entering:'||l_proc, 5);
373 --
374 ghr_dut_rku.after_update(
375 p_duty_station_id => p_rec.duty_station_id
376 ,p_effective_start_date => p_rec.effective_start_date
377 ,p_effective_end_date => p_rec.effective_end_date
378 ,p_locality_pay_area_id => p_rec.locality_pay_area_id
379 ,p_leo_pay_area_code => p_rec.leo_pay_area_code
380 ,p_name => p_rec.name
381 ,p_duty_station_code => p_rec.duty_station_code
382 ,p_is_duty_station => p_rec.is_duty_station
383 ,p_effective_date => p_effective_date
384 ,p_datetrack_update_mode => p_datetrack_mode
385 ,p_object_version_number => p_rec.object_version_number
386 );
387 hr_utility.set_location(' Leaving:'||l_proc, 10);
388 End post_update;
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 structre.
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 conversion
422 -- 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(p_rec in out nocopy ghr_dut_shd.g_rec_type) is
433 --
434 l_proc varchar2(72) := g_package||'convert_defs';
435 --
436 Begin
437 --
438 hr_utility.set_location('Entering:'||l_proc, 5);
439 --
440 -- We must now examine each argument value in the
441 -- p_rec plsql record structure
442 -- to see if a system default is being used. If a system default
443 -- is being used then we must set to the 'current' argument value.
444 --
445 If (p_rec.duty_station_code = hr_api.g_varchar2) then
446 p_rec.duty_station_code :=
447 ghr_dut_shd.g_old_rec.duty_station_code;
448 End If;
449 --
450 --
451 If (p_rec.name = hr_api.g_varchar2) then
452 p_rec.name :=
453 ghr_dut_shd.g_old_rec.name;
454 End If;
455 --
456 hr_utility.set_location(' Leaving:'||l_proc, 10);
457 --
458 End convert_defs;
459 --
460 -- ----------------------------------------------------------------------------
461 -- |---------------------------------< upd >----------------------------------|
462 -- ----------------------------------------------------------------------------
463 Procedure upd
464 (
465 p_rec in out nocopy ghr_dut_shd.g_rec_type,
466 p_effective_date in date,
467 p_datetrack_update_mode in varchar2
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_update_mode);
480 --
481 -- We must lock the row which we need to update.
482 --
483 ghr_dut_shd.lck
484 (p_effective_date => p_effective_date,
485 p_datetrack_mode => p_datetrack_update_mode,
486 p_duty_station_id => p_rec.duty_station_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 -- 1. During an update system defaults are used to determine if
492 -- arguments have been defaulted or not. We must therefore
493 -- derive the full record structure values to be updated.
494 --
495 -- 2. Call the supporting update validate operations.
496 --
497 convert_defs(p_rec);
498 ghr_dut_bus.update_validate
499 (p_rec => p_rec,
500 p_effective_date => p_effective_date,
501 p_datetrack_mode =>p_datetrack_update_mode,
502 p_validation_start_date => l_validation_start_date,
503 p_validation_end_date => l_validation_end_date);
504 --
505 -- Call the supporting pre-update operation
506 --
507 pre_update
508 (p_rec => p_rec,
509 p_effective_date => p_effective_date,
510 p_datetrack_mode => p_datetrack_update_mode,
511 p_validation_start_date => l_validation_start_date,
512 p_validation_end_date => l_validation_end_date);
513 --
514 -- Update the row.
515 --
516 update_dml
517 (p_rec => p_rec,
518 p_effective_date => p_effective_date,
519 p_datetrack_mode => p_datetrack_update_mode,
520 p_validation_start_date => l_validation_start_date,
521 p_validation_end_date => l_validation_end_date);
522 --
523 -- Call the supporting post-update operation
524 --
525 post_update
526 (p_rec => p_rec,
527 p_effective_date => p_effective_date,
528 p_datetrack_mode => p_datetrack_update_mode,
529 p_validation_start_date => l_validation_start_date,
530 p_validation_end_date => l_validation_end_date);
531 End upd;
532 --
533 -- ----------------------------------------------------------------------------
534 -- |---------------------------------< upd >----------------------------------|
535 -- ----------------------------------------------------------------------------
536 Procedure upd
537 (
538 p_duty_station_id in number,
539 p_effective_start_date out nocopy date,
540 p_effective_end_date out nocopy date,
541 p_locality_pay_area_id in number default hr_api.g_number ,
542 p_leo_pay_area_code in varchar2 default hr_api.g_varchar2 ,
543 p_name in varchar2 default hr_api.g_varchar2 ,
544 p_duty_station_code in varchar2 ,
545 p_msa_code in varchar2 default hr_api.g_varchar2 ,
546 p_cmsa_code in varchar2 default hr_api.g_varchar2 ,
547 p_state_or_country_code in varchar2 default hr_api.g_varchar2 ,
548 p_county_code in varchar2 default hr_api.g_varchar2 ,
549 p_is_duty_station in varchar2 default hr_api.g_varchar2 ,
550 p_object_version_number in out nocopy number,
551 p_effective_date in date,
552 p_datetrack_update_mode in varchar2
553 ) is
554 --
555 l_rec ghr_dut_shd.g_rec_type;
556 l_proc varchar2(72) := g_package||'upd';
557 --
558 Begin
559 hr_utility.set_location('Entering:'||l_proc, 5);
560 --
561 -- Call conversion function to turn arguments into the
562 -- l_rec structure.
563 --
564 l_rec :=
565 ghr_dut_shd.convert_args
566 (
567 p_duty_station_id => p_duty_station_id
568 ,p_effective_start_date => null
569 ,p_effective_end_date => null
570 ,p_locality_pay_area_id => p_locality_pay_area_id
571 ,p_leo_pay_area_code => p_leo_pay_area_code
572 ,p_name => p_name
573 ,p_duty_station_code => p_duty_station_code
574 ,p_msa_code => p_msa_code
575 ,p_cmsa_code => p_cmsa_code
576 ,p_state_or_country_code => p_state_or_country_code
577 ,p_county_code => p_county_code
578 ,p_is_duty_station => p_is_duty_station
579 ,p_object_version_number => p_object_version_number
580 );
581
582 --
583 -- Having converted the arguments into the
584 -- plsql record structure we call the corresponding record
585 -- business process.
586 --
587 --
588 upd(l_rec, p_effective_date, p_datetrack_update_mode);
589 p_effective_start_date := l_rec.effective_start_date;
590 p_effective_end_date := l_rec.effective_end_date;
591 --
592 --
593 hr_utility.set_location(' Leaving:'||l_proc, 10);
594 End upd;
595 --
596 end ghr_dut_upd;
[Home] [Help]