1 Package Body ben_bli_upd as
2 /* $Header: beblirhi.pkb 115.7 2002/12/10 15:17:02 bmanyam ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_bli_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_bli_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_bli_shd.g_api_dml := true; -- Set the api dml status
66 --
67 -- Update the ben_batch_ler_info Row
68 --
69 update ben_batch_ler_info
70 set
71 batch_ler_id = p_rec.batch_ler_id,
72 benefit_action_id = p_rec.benefit_action_id,
73 person_id = p_rec.person_id,
74 ler_id = p_rec.ler_id,
75 lf_evt_ocrd_dt = p_rec.lf_evt_ocrd_dt,
76 replcd_flag = p_rec.replcd_flag,
77 crtd_flag = p_rec.crtd_flag,
78 tmprl_flag = p_rec.tmprl_flag,
79 dltd_flag = p_rec.dltd_flag,
80 open_and_clsd_flag = p_rec.open_and_clsd_flag,
81 clsd_flag = p_rec.clsd_flag,
82 not_crtd_flag = p_rec.not_crtd_flag,
83 stl_actv_flag = p_rec.stl_actv_flag,
84 clpsd_flag = p_rec.clpsd_flag,
85 clsn_flag = p_rec.clsn_flag,
86 no_effect_flag = p_rec.no_effect_flag,
87 cvrge_rt_prem_flag = p_rec.cvrge_rt_prem_flag,
88 per_in_ler_id = p_rec.per_in_ler_id,
89 business_group_id = p_rec.business_group_id,
90 object_version_number = p_rec.object_version_number
91 where batch_ler_id = p_rec.batch_ler_id;
92 --
93 ben_bli_shd.g_api_dml := false; -- Unset the api dml status
94 --
95 hr_utility.set_location(' Leaving:'||l_proc, 10);
96 --
97 Exception
98 When hr_api.check_integrity_violated Then
99 -- A check constraint has been violated
100 ben_bli_shd.g_api_dml := false; -- Unset the api dml status
101 ben_bli_shd.constraint_error
102 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
103 When hr_api.parent_integrity_violated Then
104 -- Parent integrity has been violated
105 ben_bli_shd.g_api_dml := false; -- Unset the api dml status
106 ben_bli_shd.constraint_error
107 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
108 When hr_api.unique_integrity_violated Then
109 -- Unique integrity has been violated
110 ben_bli_shd.g_api_dml := false; -- Unset the api dml status
111 ben_bli_shd.constraint_error
112 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
113 When Others Then
114 ben_bli_shd.g_api_dml := false; -- Unset the api dml status
115 Raise;
116 End update_dml;
117 --
118 -- ----------------------------------------------------------------------------
119 -- |------------------------------< pre_update >------------------------------|
120 -- ----------------------------------------------------------------------------
121 -- {Start Of Comments}
122 --
123 -- Description:
124 -- This private procedure contains any processing which is required before
125 -- the update dml.
126 --
127 -- Prerequisites:
128 -- This is an internal procedure which is called from the upd procedure.
129 --
130 -- In Parameters:
131 -- A Pl/Sql record structre.
132 --
133 -- Post Success:
134 -- Processing continues.
135 --
136 -- Post Failure:
137 -- If an error has occurred, an error message and exception will be raised
138 -- but not handled.
139 --
140 -- Developer Implementation Notes:
141 -- Any pre-processing required before the update dml is issued should be
142 -- coded within this procedure. It is important to note that any 3rd party
143 -- maintenance should be reviewed before placing in this procedure.
144 --
145 -- Access Status:
146 -- Internal Row Handler Use Only.
147 --
148 -- {End Of Comments}
149 -- ----------------------------------------------------------------------------
150 Procedure pre_update(p_rec in ben_bli_shd.g_rec_type) is
151 --
152 l_proc varchar2(72) := g_package||'pre_update';
153 --
154 Begin
155 hr_utility.set_location('Entering:'||l_proc, 5);
156 --
157 hr_utility.set_location(' Leaving:'||l_proc, 10);
158 End pre_update;
159 --
160 -- ----------------------------------------------------------------------------
161 -- |-----------------------------< post_update >------------------------------|
162 -- ----------------------------------------------------------------------------
163 -- {Start Of Comments}
164 --
165 -- Description:
166 -- This private procedure contains any processing which is required after the
167 -- update dml.
168 --
169 -- Prerequisites:
170 -- This is an internal procedure which is called from the upd procedure.
171 --
172 -- In Parameters:
173 -- A Pl/Sql record structre.
174 --
175 -- Post Success:
176 -- Processing continues.
177 --
178 -- Post Failure:
179 -- If an error has occurred, an error message and exception will be raised
180 -- but not handled.
181 --
182 -- Developer Implementation Notes:
183 -- Any post-processing required after the update dml is issued should be
184 -- coded within this procedure. It is important to note that any 3rd party
185 -- maintenance should be reviewed before placing in this procedure.
186 --
187 -- Access Status:
188 -- Internal Row Handler Use Only.
189 --
190 -- {End Of Comments}
191 -- ----------------------------------------------------------------------------
192 Procedure post_update(p_effective_date in date,
193 p_rec in ben_bli_shd.g_rec_type) is
194 --
195 l_proc varchar2(72) := g_package||'post_update';
196 --
197 Begin
198 hr_utility.set_location('Entering:'||l_proc, 5);
199 --
200 --
201 -- Start of API User Hook for post_update.
202 --
203 begin
204 --
205 ben_bli_rku.after_update
206 (p_batch_ler_id =>p_rec.batch_ler_id
207 ,p_benefit_action_id =>p_rec.benefit_action_id
208 ,p_person_id =>p_rec.person_id
209 ,p_ler_id =>p_rec.ler_id
210 ,p_lf_evt_ocrd_dt =>p_rec.lf_evt_ocrd_dt
211 ,p_replcd_flag =>p_rec.replcd_flag
212 ,p_crtd_flag =>p_rec.crtd_flag
213 ,p_tmprl_flag =>p_rec.tmprl_flag
214 ,p_dltd_flag =>p_rec.dltd_flag
215 ,p_open_and_clsd_flag =>p_rec.open_and_clsd_flag
216 ,p_clsd_flag =>p_rec.clsd_flag
217 ,p_not_crtd_flag =>p_rec.not_crtd_flag
218 ,p_stl_actv_flag =>p_rec.stl_actv_flag
219 ,p_clpsd_flag =>p_rec.clpsd_flag
220 ,p_clsn_flag =>p_rec.clsn_flag
221 ,p_no_effect_flag =>p_rec.no_effect_flag
222 ,p_cvrge_rt_prem_flag =>p_rec.cvrge_rt_prem_flag
223 ,p_per_in_ler_id =>p_rec.per_in_ler_id
224 ,p_business_group_id =>p_rec.business_group_id
225 ,p_object_version_number =>p_rec.object_version_number
226 ,p_effective_date =>p_effective_date
227 ,p_benefit_action_id_o =>ben_bli_shd.g_old_rec.benefit_action_id
228 ,p_person_id_o =>ben_bli_shd.g_old_rec.person_id
229 ,p_ler_id_o =>ben_bli_shd.g_old_rec.ler_id
230 ,p_lf_evt_ocrd_dt_o =>ben_bli_shd.g_old_rec.lf_evt_ocrd_dt
231 ,p_replcd_flag_o =>ben_bli_shd.g_old_rec.replcd_flag
232 ,p_crtd_flag_o =>ben_bli_shd.g_old_rec.crtd_flag
233 ,p_tmprl_flag_o =>ben_bli_shd.g_old_rec.tmprl_flag
234 ,p_dltd_flag_o =>ben_bli_shd.g_old_rec.dltd_flag
235 ,p_open_and_clsd_flag_o =>ben_bli_shd.g_old_rec.open_and_clsd_flag
236 ,p_clsd_flag_o =>ben_bli_shd.g_old_rec.clsd_flag
237 ,p_not_crtd_flag_o =>ben_bli_shd.g_old_rec.not_crtd_flag
238 ,p_stl_actv_flag_o =>ben_bli_shd.g_old_rec.stl_actv_flag
239 ,p_clpsd_flag_o =>ben_bli_shd.g_old_rec.clpsd_flag
240 ,p_clsn_flag_o =>ben_bli_shd.g_old_rec.clsn_flag
241 ,p_no_effect_flag_o =>ben_bli_shd.g_old_rec.no_effect_flag
242 ,p_cvrge_rt_prem_flag_o =>ben_bli_shd.g_old_rec.cvrge_rt_prem_flag
243 ,p_per_in_ler_id_o =>ben_bli_shd.g_old_rec.per_in_ler_id
244 ,p_business_group_id_o =>ben_bli_shd.g_old_rec.business_group_id
245 ,p_object_version_number_o =>ben_bli_shd.g_old_rec.object_version_number);
246 --
247 exception
248 --
249 when hr_api.cannot_find_prog_unit then
250 --
251 hr_api.cannot_find_prog_unit_error
252 (p_module_name => 'ben_batch_ler_info'
253 ,p_hook_type => 'AU');
254 --
255 end;
256 --
257 -- End of API User Hook for post_update.
258 --
259 --
260 hr_utility.set_location(' Leaving:'||l_proc, 10);
261 End post_update;
262 --
263 -- ----------------------------------------------------------------------------
264 -- |-----------------------------< convert_defs >-----------------------------|
265 -- ----------------------------------------------------------------------------
266 -- {Start Of Comments}
267 --
268 -- Description:
269 -- The Convert_Defs procedure has one very important function:
270 -- It must return the record structure for the row with all system defaulted
271 -- values converted into its corresponding parameter value for update. When
272 -- we attempt to update a row through the Upd process , certain
273 -- parameters can be defaulted which enables flexibility in the calling of
274 -- the upd process (e.g. only attributes which need to be updated need to be
275 -- specified). For the upd process to determine which attributes
276 -- have NOT been specified we need to check if the parameter has a reserved
277 -- system default value. Therefore, for all parameters which have a
278 -- corresponding reserved system default mechanism specified we need to
279 -- check if a system default is being used. If a system default is being
280 -- used then we convert the defaulted value into its corresponding attribute
281 -- value held in the g_old_rec data structure.
282 --
283 -- Prerequisites:
284 -- This private function can only be called from the upd process.
285 --
286 -- In Parameters:
287 -- A Pl/Sql record structre.
288 --
289 -- Post Success:
290 -- The record structure will be returned with all system defaulted parameter
291 -- values converted into its current row attribute value.
292 --
293 -- Post Failure:
294 -- No direct error handling is required within this function. Any possible
295 -- errors within this procedure will be a PL/SQL value error due to conversion
296 -- of datatypes or data lengths.
297 --
298 -- Developer Implementation Notes:
299 -- None.
300 --
301 -- Access Status:
302 -- Internal Row Handler Use Only.
303 --
304 -- {End Of Comments}
305 -- ----------------------------------------------------------------------------
306 Procedure convert_defs(p_rec in out nocopy ben_bli_shd.g_rec_type) is
307 --
308 l_proc varchar2(72) := g_package||'convert_defs';
309 --
310 Begin
311 --
312 hr_utility.set_location('Entering:'||l_proc, 5);
313 --
314 -- We must now examine each argument value in the
315 -- p_rec plsql record structure
316 -- to see if a system default is being used. If a system default
317 -- is being used then we must set to the 'current' argument value.
318 --
319 If (p_rec.benefit_action_id = hr_api.g_number) then
320 p_rec.benefit_action_id :=
321 ben_bli_shd.g_old_rec.benefit_action_id;
322 End If;
323 If (p_rec.person_id = hr_api.g_number) then
324 p_rec.person_id :=
325 ben_bli_shd.g_old_rec.person_id;
326 End If;
327 If (p_rec.ler_id = hr_api.g_number) then
328 p_rec.ler_id :=
329 ben_bli_shd.g_old_rec.ler_id;
330 End If;
331 If (p_rec.lf_evt_ocrd_dt = hr_api.g_date) then
332 p_rec.lf_evt_ocrd_dt :=
333 ben_bli_shd.g_old_rec.lf_evt_ocrd_dt;
334 End If;
335 If (p_rec.replcd_flag = hr_api.g_varchar2) then
336 p_rec.replcd_flag :=
337 ben_bli_shd.g_old_rec.replcd_flag;
338 End If;
339 If (p_rec.crtd_flag = hr_api.g_varchar2) then
340 p_rec.crtd_flag :=
341 ben_bli_shd.g_old_rec.crtd_flag;
342 End If;
343 If (p_rec.tmprl_flag = hr_api.g_varchar2) then
344 p_rec.tmprl_flag :=
345 ben_bli_shd.g_old_rec.tmprl_flag;
346 End If;
347 If (p_rec.dltd_flag = hr_api.g_varchar2) then
348 p_rec.dltd_flag :=
349 ben_bli_shd.g_old_rec.dltd_flag;
350 End If;
351 If (p_rec.open_and_clsd_flag = hr_api.g_varchar2) then
352 p_rec.open_and_clsd_flag :=
353 ben_bli_shd.g_old_rec.open_and_clsd_flag;
354 End If;
355 If (p_rec.clsd_flag = hr_api.g_varchar2) then
356 p_rec.clsd_flag :=
357 ben_bli_shd.g_old_rec.clsd_flag;
358 End If;
359 If (p_rec.not_crtd_flag = hr_api.g_varchar2) then
360 p_rec.not_crtd_flag :=
361 ben_bli_shd.g_old_rec.not_crtd_flag;
362 End If;
363 If (p_rec.stl_actv_flag = hr_api.g_varchar2) then
364 p_rec.stl_actv_flag :=
365 ben_bli_shd.g_old_rec.stl_actv_flag;
366 End If;
367 If (p_rec.clpsd_flag = hr_api.g_varchar2) then
368 p_rec.clpsd_flag :=
369 ben_bli_shd.g_old_rec.clpsd_flag;
370 End If;
371 If (p_rec.clsn_flag = hr_api.g_varchar2) then
372 p_rec.clsn_flag :=
373 ben_bli_shd.g_old_rec.clsn_flag;
374 End If;
375 If (p_rec.no_effect_flag = hr_api.g_varchar2) then
376 p_rec.no_effect_flag :=
377 ben_bli_shd.g_old_rec.no_effect_flag;
378 End If;
379 If (p_rec.cvrge_rt_prem_flag = hr_api.g_varchar2) then
380 p_rec.cvrge_rt_prem_flag :=
381 ben_bli_shd.g_old_rec.cvrge_rt_prem_flag;
382 End If;
383 If (p_rec.per_in_ler_id = hr_api.g_number) then
384 p_rec.per_in_ler_id :=
385 ben_bli_shd.g_old_rec.per_in_ler_id;
386 End If;
387 If (p_rec.business_group_id = hr_api.g_number) then
388 p_rec.business_group_id :=
389 ben_bli_shd.g_old_rec.business_group_id;
390 End If;
391
392 --
393 hr_utility.set_location(' Leaving:'||l_proc, 10);
394 --
395 End convert_defs;
396 --
397 -- ----------------------------------------------------------------------------
398 -- |---------------------------------< upd >----------------------------------|
399 -- ----------------------------------------------------------------------------
400 Procedure upd
401 (
402 p_effective_date in date,
403 p_rec in out nocopy ben_bli_shd.g_rec_type
404 ) is
405 --
406 l_proc varchar2(72) := g_package||'upd';
407 --
408 Begin
409 hr_utility.set_location('Entering:'||l_proc, 5);
410 --
411 -- We must lock the row which we need to update.
412 --
413 ben_bli_shd.lck
414 (
415 p_rec.batch_ler_id,
416 p_rec.object_version_number
417 );
418 --
419 -- 1. During an update system defaults are used to determine if
420 -- arguments have been defaulted or not. We must therefore
421 -- derive the full record structure values to be updated.
422 --
423 -- 2. Call the supporting update validate operations.
424 --
425 convert_defs(p_rec);
426 ben_bli_bus.update_validate(p_rec,p_effective_date);
427 --
428 -- Call the supporting pre-update operation
429 --
430 pre_update(p_rec);
431 --
432 -- Update the row.
433 --
434 update_dml(p_rec);
435 --
436 -- Call the supporting post-update operation
437 --
438 post_update(p_effective_date,p_rec);
439 --
440 End upd;
441 --
442 -- ----------------------------------------------------------------------------
443 -- |---------------------------------< upd >----------------------------------|
444 -- ----------------------------------------------------------------------------
445 Procedure upd
446 (
447 p_effective_date in date,
448 p_batch_ler_id in number,
449 p_benefit_action_id in number default hr_api.g_number,
450 p_person_id in number default hr_api.g_number,
451 p_ler_id in number default hr_api.g_number,
452 p_lf_evt_ocrd_dt in date default hr_api.g_date,
453 p_replcd_flag in varchar2 default hr_api.g_varchar2,
454 p_crtd_flag in varchar2 default hr_api.g_varchar2,
455 p_tmprl_flag in varchar2 default hr_api.g_varchar2,
456 p_dltd_flag in varchar2 default hr_api.g_varchar2,
457 p_open_and_clsd_flag in varchar2 default hr_api.g_varchar2,
458 p_clsd_flag in varchar2 default hr_api.g_varchar2,
459 p_not_crtd_flag in varchar2 default hr_api.g_varchar2,
460 p_stl_actv_flag in varchar2 default hr_api.g_varchar2,
461 p_clpsd_flag in varchar2 default hr_api.g_varchar2,
462 p_clsn_flag in varchar2 default hr_api.g_varchar2,
463 p_no_effect_flag in varchar2 default hr_api.g_varchar2,
464 p_cvrge_rt_prem_flag in varchar2 default hr_api.g_varchar2,
465 p_per_in_ler_id in number default hr_api.g_number,
466 p_business_group_id in number default hr_api.g_number,
467 p_object_version_number in out nocopy number
468 ) is
469 --
470 l_rec ben_bli_shd.g_rec_type;
471 l_proc varchar2(72) := g_package||'upd';
472 --
473 Begin
474 hr_utility.set_location('Entering:'||l_proc, 5);
475 --
476 -- Call conversion function to turn arguments into the
477 -- l_rec structure.
478 --
479 l_rec :=
480 ben_bli_shd.convert_args
481 (
482 p_batch_ler_id,
483 p_benefit_action_id,
484 p_person_id,
485 p_ler_id,
486 p_lf_evt_ocrd_dt,
487 p_replcd_flag,
488 p_crtd_flag,
489 p_tmprl_flag,
490 p_dltd_flag,
491 p_open_and_clsd_flag,
492 p_clsd_flag ,
493 p_not_crtd_flag,
494 p_stl_actv_flag,
495 p_clpsd_flag,
496 p_clsn_flag,
497 p_no_effect_flag,
498 p_cvrge_rt_prem_flag,
499 p_per_in_ler_id,
500 p_business_group_id,
501 p_object_version_number
502 );
503 --
504 -- Having converted the arguments into the
505 -- plsql record structure we call the corresponding record
506 -- business process.
507 --
508 upd(p_effective_date,l_rec);
509 p_object_version_number := l_rec.object_version_number;
510 --
511 hr_utility.set_location(' Leaving:'||l_proc, 10);
512 End upd;
513 --
514 end ben_bli_upd;