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