1 Package Body ben_xcl_upd as
2 /* $Header: bexclrhi.pkb 115.7 2002/12/24 21:28:21 rpillay ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_xcl_upd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------------< update_dml >------------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 -- This procedure controls the actual dml update logic. The processing of
17 -- this procedure is:
18 -- 1) Increment the object_version_number by 1 if the object_version_number
19 -- is defined as an attribute for this entity.
20 -- 2) To set and unset the g_api_dml status as required (as we are about to
21 -- perform dml).
22 -- 3) To update the specified row in the schema using the primary key in
23 -- the predicates.
24 -- 4) To trap any constraint violations that may have occurred.
25 -- 5) To raise any other errors.
26 --
27 -- Prerequisites:
28 -- This is an internal private procedure which must be called from the upd
29 -- procedure.
30 --
31 -- In Parameters:
32 -- A Pl/Sql record structre.
33 --
34 -- Post Success:
35 -- The specified row will be updated in the schema.
36 --
37 -- Post Failure:
38 -- On the update dml failure it is important to note that we always reset the
39 -- g_api_dml status to false.
40 -- If a check, unique or parent integrity constraint violation is raised the
41 -- constraint_error procedure will be called.
42 -- If any other error is reported, the error will be raised after the
43 -- g_api_dml status is reset.
44 --
45 -- Developer Implementation Notes:
46 -- The update 'set' attribute list should be modified if any of your
47 -- attributes are not updateable.
48 --
49 -- Access Status:
50 -- Internal Row Handler Use Only.
51 --
52 -- {End Of Comments}
53 -- ----------------------------------------------------------------------------
54 Procedure update_dml(p_rec in out nocopy ben_xcl_shd.g_rec_type) is
55 --
56 l_proc varchar2(72) := g_package||'update_dml';
57 --
58 Begin
59 hr_utility.set_location('Entering:'||l_proc, 5);
60 --
61 -- Increment the object version
62 --
63 p_rec.object_version_number := p_rec.object_version_number + 1;
64 --
65 ben_xcl_shd.g_api_dml := true; -- Set the api dml status
66 --
67 -- Update the ben_ext_chg_evt_log Row
68 --
69 update ben_ext_chg_evt_log
70 set
71 ext_chg_evt_log_id = p_rec.ext_chg_evt_log_id,
72 chg_evt_cd = p_rec.chg_evt_cd,
73 chg_eff_dt = p_rec.chg_eff_dt,
74 chg_user_id = p_rec.chg_user_id,
75 prmtr_01 = p_rec.prmtr_01,
76 prmtr_02 = p_rec.prmtr_02,
77 prmtr_03 = p_rec.prmtr_03,
78 prmtr_04 = p_rec.prmtr_04,
79 prmtr_05 = p_rec.prmtr_05,
80 prmtr_06 = p_rec.prmtr_06,
81 prmtr_07 = p_rec.prmtr_07,
82 prmtr_08 = p_rec.prmtr_08,
83 prmtr_09 = p_rec.prmtr_09,
84 prmtr_10 = p_rec.prmtr_10,
85 person_id = p_rec.person_id,
86 business_group_id = p_rec.business_group_id,
87 object_version_number = p_rec.object_version_number
88 where ext_chg_evt_log_id = p_rec.ext_chg_evt_log_id;
89 --
90 ben_xcl_shd.g_api_dml := false; -- Unset the api dml status
91 --
92 hr_utility.set_location(' Leaving:'||l_proc, 10);
93 --
94 Exception
95 When hr_api.check_integrity_violated Then
96 -- A check constraint has been violated
97 ben_xcl_shd.g_api_dml := false; -- Unset the api dml status
98 ben_xcl_shd.constraint_error
99 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
100 When hr_api.parent_integrity_violated Then
101 -- Parent integrity has been violated
102 ben_xcl_shd.g_api_dml := false; -- Unset the api dml status
103 ben_xcl_shd.constraint_error
104 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
105 When hr_api.unique_integrity_violated Then
106 -- Unique integrity has been violated
107 ben_xcl_shd.g_api_dml := false; -- Unset the api dml status
108 ben_xcl_shd.constraint_error
109 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
110 When Others Then
111 ben_xcl_shd.g_api_dml := false; -- Unset the api dml status
112 Raise;
113 End update_dml;
114 --
115 -- ----------------------------------------------------------------------------
116 -- |------------------------------< pre_update >------------------------------|
117 -- ----------------------------------------------------------------------------
118 -- {Start Of Comments}
119 --
120 -- Description:
121 -- This private procedure contains any processing which is required before
122 -- the update dml.
123 --
124 -- Prerequisites:
125 -- This is an internal procedure which is called from the upd procedure.
126 --
127 -- In Parameters:
128 -- A Pl/Sql record structre.
129 --
130 -- Post Success:
131 -- Processing continues.
132 --
133 -- Post Failure:
134 -- If an error has occurred, an error message and exception will be raised
135 -- but not handled.
136 --
137 -- Developer Implementation Notes:
138 -- Any pre-processing required before the update dml is issued should be
139 -- coded within this procedure. It is important to note that any 3rd party
140 -- maintenance should be reviewed before placing in this procedure.
141 --
142 -- Access Status:
143 -- Internal Row Handler Use Only.
144 --
145 -- {End Of Comments}
146 -- ----------------------------------------------------------------------------
147 Procedure pre_update(p_rec in ben_xcl_shd.g_rec_type) is
148 --
149 l_proc varchar2(72) := g_package||'pre_update';
150 --
151 Begin
152 hr_utility.set_location('Entering:'||l_proc, 5);
153 --
154 hr_utility.set_location(' Leaving:'||l_proc, 10);
155 End pre_update;
156 --
157 -- ----------------------------------------------------------------------------
158 -- |-----------------------------< post_update >------------------------------|
159 -- ----------------------------------------------------------------------------
160 -- {Start Of Comments}
161 --
162 -- Description:
163 -- This private procedure contains any processing which is required after the
164 -- update dml.
165 --
166 -- Prerequisites:
167 -- This is an internal procedure which is called from the upd procedure.
168 --
169 -- In Parameters:
170 -- A Pl/Sql record structre.
171 --
172 -- Post Success:
173 -- Processing continues.
174 --
175 -- Post Failure:
176 -- If an error has occurred, an error message and exception will be raised
177 -- but not handled.
178 --
179 -- Developer Implementation Notes:
180 -- Any post-processing required after the update dml is issued should be
181 -- coded within this procedure. It is important to note that any 3rd party
182 -- maintenance should be reviewed before placing in this procedure.
183 --
184 -- Access Status:
185 -- Internal Row Handler Use Only.
186 --
187 -- {End Of Comments}
188 -- ----------------------------------------------------------------------------
189 Procedure post_update(
190 p_effective_date in date,p_rec in ben_xcl_shd.g_rec_type) is
191 --
192 l_proc varchar2(72) := g_package||'post_update';
193 --
194 Begin
195 hr_utility.set_location('Entering:'||l_proc, 5);
196 --
197 --
198 -- Start of API User Hook for post_update.
199 --
200 begin
201 --
202 ben_xcl_rku.after_update
203 (
204 p_ext_chg_evt_log_id =>p_rec.ext_chg_evt_log_id
205 ,p_chg_evt_cd =>p_rec.chg_evt_cd
206 ,p_chg_eff_dt =>p_rec.chg_eff_dt
207 ,p_chg_user_id =>p_rec.chg_user_id
208 ,p_prmtr_01 =>p_rec.prmtr_01
209 ,p_prmtr_02 =>p_rec.prmtr_02
210 ,p_prmtr_03 =>p_rec.prmtr_03
211 ,p_prmtr_04 =>p_rec.prmtr_04
212 ,p_prmtr_05 =>p_rec.prmtr_05
213 ,p_prmtr_06 =>p_rec.prmtr_06
214 ,p_prmtr_07 =>p_rec.prmtr_07
215 ,p_prmtr_08 =>p_rec.prmtr_08
216 ,p_prmtr_09 =>p_rec.prmtr_09
217 ,p_prmtr_10 =>p_rec.prmtr_10
218 ,p_person_id =>p_rec.person_id
219 ,p_business_group_id =>p_rec.business_group_id
220 ,p_object_version_number =>p_rec.object_version_number
221 ,p_effective_date =>p_effective_date
222 ,p_chg_evt_cd_o =>ben_xcl_shd.g_old_rec.chg_evt_cd
223 ,p_chg_eff_dt_o =>ben_xcl_shd.g_old_rec.chg_eff_dt
224 ,p_chg_user_id_o =>ben_xcl_shd.g_old_rec.chg_user_id
225 ,p_prmtr_01_o =>ben_xcl_shd.g_old_rec.prmtr_01
226 ,p_prmtr_02_o =>ben_xcl_shd.g_old_rec.prmtr_02
227 ,p_prmtr_03_o =>ben_xcl_shd.g_old_rec.prmtr_03
228 ,p_prmtr_04_o =>ben_xcl_shd.g_old_rec.prmtr_04
229 ,p_prmtr_05_o =>ben_xcl_shd.g_old_rec.prmtr_05
230 ,p_prmtr_06_o =>ben_xcl_shd.g_old_rec.prmtr_06
231 ,p_prmtr_07_o =>ben_xcl_shd.g_old_rec.prmtr_07
232 ,p_prmtr_08_o =>ben_xcl_shd.g_old_rec.prmtr_08
233 ,p_prmtr_09_o =>ben_xcl_shd.g_old_rec.prmtr_09
234 ,p_prmtr_10_o =>ben_xcl_shd.g_old_rec.prmtr_10
235 ,p_person_id_o =>ben_xcl_shd.g_old_rec.person_id
236 ,p_business_group_id_o =>ben_xcl_shd.g_old_rec.business_group_id
237 ,p_object_version_number_o =>ben_xcl_shd.g_old_rec.object_version_number
238 );
239 --
240 exception
241 --
242 when hr_api.cannot_find_prog_unit then
243 --
244 hr_api.cannot_find_prog_unit_error
245 (p_module_name => 'ben_ext_chg_evt_log'
246 ,p_hook_type => 'AU');
247 --
248 end;
249 --
250 -- End of API User Hook for post_update.
251 --
252 --
253 hr_utility.set_location(' Leaving:'||l_proc, 10);
254 End post_update;
255 --
256 -- ----------------------------------------------------------------------------
257 -- |-----------------------------< convert_defs >-----------------------------|
258 -- ----------------------------------------------------------------------------
259 -- {Start Of Comments}
260 --
261 -- Description:
262 -- The Convert_Defs procedure has one very important function:
263 -- It must return the record structure for the row with all system defaulted
264 -- values converted into its corresponding parameter value for update. When
265 -- we attempt to update a row through the Upd process , certain
266 -- parameters can be defaulted which enables flexibility in the calling of
267 -- the upd process (e.g. only attributes which need to be updated need to be
268 -- specified). For the upd process to determine which attributes
269 -- have NOT been specified we need to check if the parameter has a reserved
270 -- system default value. Therefore, for all parameters which have a
271 -- corresponding reserved system default mechanism specified we need to
272 -- check if a system default is being used. If a system default is being
273 -- used then we convert the defaulted value into its corresponding attribute
274 -- value held in the g_old_rec data structure.
275 --
276 -- Prerequisites:
277 -- This private function can only be called from the upd process.
278 --
279 -- In Parameters:
280 -- A Pl/Sql record structre.
281 --
282 -- Post Success:
283 -- The record structure will be returned with all system defaulted parameter
284 -- values converted into its current row attribute value.
285 --
286 -- Post Failure:
287 -- No direct error handling is required within this function. Any possible
288 -- errors within this procedure will be a PL/SQL value error due to conversion
289 -- of datatypes or data lengths.
290 --
291 -- Developer Implementation Notes:
292 -- None.
293 --
294 -- Access Status:
295 -- Internal Row Handler Use Only.
296 --
297 -- {End Of Comments}
298 -- ----------------------------------------------------------------------------
299 Procedure convert_defs(p_rec in out nocopy ben_xcl_shd.g_rec_type) is
300 --
301 l_proc varchar2(72) := g_package||'convert_defs';
302 --
303 Begin
304 --
305 hr_utility.set_location('Entering:'||l_proc, 5);
306 --
307 -- We must now examine each argument value in the
308 -- p_rec plsql record structure
309 -- to see if a system default is being used. If a system default
310 -- is being used then we must set to the 'current' argument value.
311 --
312 If (p_rec.chg_evt_cd = hr_api.g_varchar2) then
313 p_rec.chg_evt_cd :=
314 ben_xcl_shd.g_old_rec.chg_evt_cd;
315 End If;
316 If (p_rec.chg_eff_dt = hr_api.g_date) then
317 p_rec.chg_eff_dt :=
318 ben_xcl_shd.g_old_rec.chg_eff_dt;
319 End If;
320 If (p_rec.chg_user_id = hr_api.g_number) then
321 p_rec.chg_user_id :=
322 ben_xcl_shd.g_old_rec.chg_user_id;
323 End If;
324 If (p_rec.prmtr_01 = hr_api.g_varchar2) then
325 p_rec.prmtr_01 :=
326 ben_xcl_shd.g_old_rec.prmtr_01;
327 End If;
328 If (p_rec.prmtr_02 = hr_api.g_varchar2) then
329 p_rec.prmtr_02 :=
330 ben_xcl_shd.g_old_rec.prmtr_02;
331 End If;
332 If (p_rec.prmtr_03 = hr_api.g_varchar2) then
333 p_rec.prmtr_03 :=
334 ben_xcl_shd.g_old_rec.prmtr_03;
335 End If;
336 If (p_rec.prmtr_04 = hr_api.g_varchar2) then
337 p_rec.prmtr_04 :=
338 ben_xcl_shd.g_old_rec.prmtr_04;
339 End If;
340 If (p_rec.prmtr_05 = hr_api.g_varchar2) then
341 p_rec.prmtr_05 :=
342 ben_xcl_shd.g_old_rec.prmtr_05;
343 End If;
344 If (p_rec.prmtr_06 = hr_api.g_varchar2) then
345 p_rec.prmtr_06 :=
346 ben_xcl_shd.g_old_rec.prmtr_06;
347 End If;
348 If (p_rec.prmtr_07 = hr_api.g_varchar2) then
349 p_rec.prmtr_07 :=
350 ben_xcl_shd.g_old_rec.prmtr_07;
351 End If;
352 If (p_rec.prmtr_08 = hr_api.g_varchar2) then
353 p_rec.prmtr_08 :=
354 ben_xcl_shd.g_old_rec.prmtr_08;
355 End If;
356 If (p_rec.prmtr_09 = hr_api.g_varchar2) then
357 p_rec.prmtr_09 :=
358 ben_xcl_shd.g_old_rec.prmtr_09;
359 End If;
360 If (p_rec.prmtr_10 = hr_api.g_varchar2) then
361 p_rec.prmtr_10 :=
362 ben_xcl_shd.g_old_rec.prmtr_10;
363 End If;
364 If (p_rec.person_id = hr_api.g_number) then
365 p_rec.person_id :=
366 ben_xcl_shd.g_old_rec.person_id;
367 End If;
368 If (p_rec.business_group_id = hr_api.g_number) then
369 p_rec.business_group_id :=
370 ben_xcl_shd.g_old_rec.business_group_id;
371 End If;
372
373 --
374 hr_utility.set_location(' Leaving:'||l_proc, 10);
375 --
376 End convert_defs;
377 --
378 -- ----------------------------------------------------------------------------
379 -- |---------------------------------< upd >----------------------------------|
380 -- ----------------------------------------------------------------------------
381 Procedure upd
382 (
383 p_effective_date in date,
384 p_rec in out nocopy ben_xcl_shd.g_rec_type
385 ) is
386 --
387 l_proc varchar2(72) := g_package||'upd';
388 --
389 Begin
390 hr_utility.set_location('Entering:'||l_proc, 5);
391 --
392 -- We must lock the row which we need to update.
393 --
394 ben_xcl_shd.lck
395 (
396 p_rec.ext_chg_evt_log_id,
397 p_rec.object_version_number
398 );
399 --
400 -- 1. During an update system defaults are used to determine if
401 -- arguments have been defaulted or not. We must therefore
402 -- derive the full record structure values to be updated.
403 --
404 -- 2. Call the supporting update validate operations.
405 --
406 convert_defs(p_rec);
407 ben_xcl_bus.update_validate(p_rec
408 ,p_effective_date);
409 --
410 -- Call the supporting pre-update operation
411 --
412 pre_update(p_rec);
413 --
414 -- Update the row.
415 --
416 update_dml(p_rec);
417 --
418 -- Call the supporting post-update operation
419 --
420 post_update(
421 p_effective_date,p_rec);
422 End upd;
423 --
424 -- ----------------------------------------------------------------------------
425 -- |---------------------------------< upd >----------------------------------|
426 -- ----------------------------------------------------------------------------
427 Procedure upd
428 (
429 p_effective_date in date,
430 p_ext_chg_evt_log_id in number,
431 p_chg_evt_cd in varchar2 default hr_api.g_varchar2,
432 p_chg_eff_dt in date default hr_api.g_date,
433 p_chg_user_id in number default hr_api.g_number,
434 p_prmtr_01 in varchar2 default hr_api.g_varchar2,
435 p_prmtr_02 in varchar2 default hr_api.g_varchar2,
436 p_prmtr_03 in varchar2 default hr_api.g_varchar2,
437 p_prmtr_04 in varchar2 default hr_api.g_varchar2,
438 p_prmtr_05 in varchar2 default hr_api.g_varchar2,
439 p_prmtr_06 in varchar2 default hr_api.g_varchar2,
440 p_prmtr_07 in varchar2 default hr_api.g_varchar2,
441 p_prmtr_08 in varchar2 default hr_api.g_varchar2,
442 p_prmtr_09 in varchar2 default hr_api.g_varchar2,
443 p_prmtr_10 in varchar2 default hr_api.g_varchar2,
444 p_person_id in number default hr_api.g_number,
445 p_business_group_id in number default hr_api.g_number,
446 p_object_version_number in out nocopy number
447 ) is
448 --
449 l_rec ben_xcl_shd.g_rec_type;
450 l_proc varchar2(72) := g_package||'upd';
451 --
452 Begin
453 hr_utility.set_location('Entering:'||l_proc, 5);
454 --
455 -- Call conversion function to turn arguments into the
456 -- l_rec structure.
457 --
458 l_rec :=
459 ben_xcl_shd.convert_args
460 (
461 p_ext_chg_evt_log_id,
462 p_chg_evt_cd,
463 p_chg_eff_dt,
464 p_chg_user_id,
465 p_prmtr_01,
466 p_prmtr_02,
467 p_prmtr_03,
468 p_prmtr_04,
469 p_prmtr_05,
470 p_prmtr_06,
471 p_prmtr_07,
472 p_prmtr_08,
473 p_prmtr_09,
474 p_prmtr_10,
475 p_person_id,
476 p_business_group_id,
477 p_object_version_number,
478 null,
479 null,
480 null,
481 null,
482 null,
483 null,
484 null,
485 null,
486 null,
487 null,
488 null,
489 null,
490 null
491 );
492 --
493 -- Having converted the arguments into the
494 -- plsql record structure we call the corresponding record
495 -- business process.
496 --
497 upd(
498 p_effective_date,l_rec);
499 p_object_version_number := l_rec.object_version_number;
500 --
501 hr_utility.set_location(' Leaving:'||l_proc, 10);
502 End upd;
503 --
504 end ben_xcl_upd;