1 Package Body per_bil_upd as
2 /* $Header: pebilrhi.pkb 115.10 2003/04/10 09:19:39 jheer noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_bil_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 per_bil_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 per_bil_shd.g_api_dml := true; -- Set the api dml status
66 --
67 -- Update the hr_summary Row
68 --
69 update hr_summary
70 set
71 type = p_rec.type,
72 business_group_id = p_rec.business_group_id,
73 object_version_number = p_rec.object_version_number,
74 id_value = p_rec.id_value,
75 fk_value1 = p_rec.fk_value1,
76 fk_value2 = p_rec.fk_value2,
77 fk_value3 = p_rec.fk_value3,
78 text_value1 = p_rec.text_value1,
79 text_value2 = p_rec.text_value2,
80 text_value3 = p_rec.text_value3,
81 text_value4 = p_rec.text_value4,
82 text_value5 = p_rec.text_value5,
83 text_value6 = p_rec.text_value6,
84 text_value7 = p_rec.text_value7,
85 num_value1 = p_rec.num_value1,
86 num_value2 = p_rec.num_value2,
87 num_value3 = p_rec.num_value3,
88 date_value1 = p_rec.date_value1,
89 date_value2 = p_rec.date_value2,
90 date_value3 = p_rec.date_value3
91 where id_value = p_rec.id_value;
92 --
93 per_bil_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 per_bil_shd.g_api_dml := false; -- Unset the api dml status
101 per_bil_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 per_bil_shd.g_api_dml := false; -- Unset the api dml status
106 per_bil_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 per_bil_shd.g_api_dml := false; -- Unset the api dml status
111 per_bil_shd.constraint_error
112 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
113 When Others Then
114 per_bil_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 per_bil_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_rec in per_bil_shd.g_rec_type) is
193 --
194 l_proc varchar2(72) := g_package||'post_update';
195 --
196 Begin
197 hr_utility.set_location('Entering:'||l_proc, 5);
198 --
199 --
200 -- Start of API User Hook for post_update.
201 --
202 begin
203 --
204 /*
205 per_bil_rku.after_update
206 (
207 p_type =>p_rec.type
208 ,p_business_group_id =>p_rec.business_group_id
209 ,p_object_version_number =>p_rec.object_version_number
210 ,p_id_value =>p_rec.id_value
211 ,p_fk_value1 =>p_rec.fk_value1
212 ,p_fk_value2 =>p_rec.fk_value2
213 ,p_fk_value3 =>p_rec.fk_value3
214 ,p_text_value1 =>p_rec.text_value1
215 ,p_text_value2 =>p_rec.text_value2
216 ,p_text_value3 =>p_rec.text_value3
217 ,p_text_value4 =>p_rec.text_value4
218 ,p_text_value5 =>p_rec.text_value5
219 ,p_text_value6 =>p_rec.text_value6
220 ,p_text_value7 =>p_rec.text_value7
221 ,p_num_value1 =>p_rec.num_value1
222 ,p_num_value2 =>p_rec.num_value2
223 ,p_num_value3 =>p_rec.num_value3
224 ,p_date_value1 =>p_rec.date_value1
225 ,p_date_value2 =>p_rec.date_value2
226 ,p_date_value3 =>p_rec.date_value3
227 ,p_type_o =>per_bil_shd.g_old_rec.type
228 ,p_business_group_id_o =>per_bil_shd.g_old_rec.business_group_id
229 ,p_object_version_number_o =>per_bil_shd.g_old_rec.object_version_number
230 ,p_fk_value1_o =>per_bil_shd.g_old_rec.fk_value1
231 ,p_fk_value2_o =>per_bil_shd.g_old_rec.fk_value2
232 ,p_fk_value3_o =>per_bil_shd.g_old_rec.fk_value3
233 ,p_text_value1_o =>per_bil_shd.g_old_rec.text_value1
234 ,p_text_value2_o =>per_bil_shd.g_old_rec.text_value2
235 ,p_text_value3_o =>per_bil_shd.g_old_rec.text_value3
236 ,p_text_value4_o =>per_bil_shd.g_old_rec.text_value4
237 ,p_text_value5_o =>per_bil_shd.g_old_rec.text_value5
238 ,p_text_value6_o =>per_bil_shd.g_old_rec.text_value6
239 ,p_text_value7_o =>per_bil_shd.g_old_rec.text_value7
240 ,p_num_value1_o =>per_bil_shd.g_old_rec.num_value1
241 ,p_num_value2_o =>per_bil_shd.g_old_rec.num_value2
242 ,p_num_value3_o =>per_bil_shd.g_old_rec.num_value3
243 ,p_date_value1_o =>per_bil_shd.g_old_rec.date_value1
244 ,p_date_value2_o =>per_bil_shd.g_old_rec.date_value2
245 ,p_date_value3_o =>per_bil_shd.g_old_rec.date_value3
246 );
247 */
248 --
249 null;
250 --
251 exception
252 --
253 when hr_api.cannot_find_prog_unit then
254 --
255 hr_api.cannot_find_prog_unit_error
256 (p_module_name => 'hr_summary'
257 ,p_hook_type => 'AU');
258 --
259 end;
260 --
261 -- End of API User Hook for post_update.
262 --
263 --
264 hr_utility.set_location(' Leaving:'||l_proc, 10);
265 End post_update;
266 --
267 -- ----------------------------------------------------------------------------
268 -- |-----------------------------< convert_defs >-----------------------------|
269 -- ----------------------------------------------------------------------------
270 -- {Start Of Comments}
271 --
272 -- Description:
273 -- The Convert_Defs procedure has one very important function:
274 -- It must return the record structure for the row with all system defaulted
275 -- values converted into its corresponding parameter value for update. When
276 -- we attempt to update a row through the Upd process , certain
277 -- parameters can be defaulted which enables flexibility in the calling of
278 -- the upd process (e.g. only attributes which need to be updated need to be
279 -- specified). For the upd process to determine which attributes
280 -- have NOT been specified we need to check if the parameter has a reserved
281 -- system default value. Therefore, for all parameters which have a
282 -- corresponding reserved system default mechanism specified we need to
283 -- check if a system default is being used. If a system default is being
284 -- used then we convert the defaulted value into its corresponding attribute
285 -- value held in the g_old_rec data structure.
286 --
287 -- Prerequisites:
288 -- This private function can only be called from the upd process.
289 --
290 -- In Parameters:
291 -- A Pl/Sql record structre.
292 --
293 -- Post Success:
294 -- The record structure will be returned with all system defaulted parameter
295 -- values converted into its current row attribute value.
296 --
297 -- Post Failure:
298 -- No direct error handling is required within this function. Any possible
299 -- errors within this procedure will be a PL/SQL value error due to conversion
300 -- of datatypes or data lengths.
301 --
302 -- Developer Implementation Notes:
303 -- None.
304 --
305 -- Access Status:
306 -- Internal Row Handler Use Only.
307 --
308 -- {End Of Comments}
309 -- ----------------------------------------------------------------------------
310 Procedure convert_defs(p_rec in out nocopy per_bil_shd.g_rec_type) is
311 --
312 l_proc varchar2(72) := g_package||'convert_defs';
313 --
314 Begin
315 --
316 hr_utility.set_location('Entering:'||l_proc, 5);
317 --
318 -- We must now examine each argument value in the
319 -- p_rec plsql record structure
320 -- to see if a system default is being used. If a system default
321 -- is being used then we must set to the 'current' argument value.
322 --
323 If (p_rec.type = hr_api.g_varchar2) then
324 p_rec.type :=
325 per_bil_shd.g_old_rec.type;
326 End If;
327 If (p_rec.business_group_id = hr_api.g_number) then
328 p_rec.business_group_id :=
329 per_bil_shd.g_old_rec.business_group_id;
330 End If;
331 If (p_rec.fk_value1 = hr_api.g_number) then
332 p_rec.fk_value1 :=
333 per_bil_shd.g_old_rec.fk_value1;
334 End If;
335 If (p_rec.fk_value2 = hr_api.g_number) then
336 p_rec.fk_value2 :=
337 per_bil_shd.g_old_rec.fk_value2;
338 End If;
339 If (p_rec.fk_value3 = hr_api.g_number) then
340 p_rec.fk_value3 :=
341 per_bil_shd.g_old_rec.fk_value3;
342 End If;
343 If (p_rec.text_value1 = hr_api.g_varchar2) then
344 p_rec.text_value1 :=
345 per_bil_shd.g_old_rec.text_value1;
346 End If;
347 If (p_rec.text_value2 = hr_api.g_varchar2) then
348 p_rec.text_value2 :=
349 per_bil_shd.g_old_rec.text_value2;
350 End If;
351 If (p_rec.text_value3 = hr_api.g_varchar2) then
352 p_rec.text_value3 :=
353 per_bil_shd.g_old_rec.text_value3;
354 End If;
355 If (p_rec.text_value4 = hr_api.g_varchar2) then
356 p_rec.text_value4 :=
357 per_bil_shd.g_old_rec.text_value4;
358 End If;
359 If (p_rec.text_value5 = hr_api.g_varchar2) then
360 p_rec.text_value5 :=
361 per_bil_shd.g_old_rec.text_value5;
362 End If;
363 If (p_rec.text_value6 = hr_api.g_varchar2) then
364 p_rec.text_value6 :=
365 per_bil_shd.g_old_rec.text_value6;
366 End If;
367 If (p_rec.text_value7 = hr_api.g_varchar2) then
368 p_rec.text_value7 :=
369 per_bil_shd.g_old_rec.text_value7;
370 End If;
371 If (p_rec.num_value1 = hr_api.g_number) then
372 p_rec.num_value1 :=
373 per_bil_shd.g_old_rec.num_value1;
374 End If;
375 If (p_rec.num_value2 = hr_api.g_number) then
376 p_rec.num_value2 :=
377 per_bil_shd.g_old_rec.num_value2;
378 End If;
379 If (p_rec.num_value3 = hr_api.g_number) then
380 p_rec.num_value3 :=
381 per_bil_shd.g_old_rec.num_value3;
382 End If;
383 If (p_rec.date_value1 = hr_api.g_date) then
384 p_rec.date_value1 :=
385 per_bil_shd.g_old_rec.date_value1;
386 End If;
387 If (p_rec.date_value2 = hr_api.g_date) then
388 p_rec.date_value2 :=
389 per_bil_shd.g_old_rec.date_value2;
390 End If;
391 If (p_rec.date_value3 = hr_api.g_date) then
392 p_rec.date_value3 :=
393 per_bil_shd.g_old_rec.date_value3;
394 End If;
395 --
396 hr_utility.set_location(' Leaving:'||l_proc, 10);
397 --
398 End convert_defs;
399 --
400 -- ----------------------------------------------------------------------------
401 -- |---------------------------------< upd >----------------------------------|
402 -- ----------------------------------------------------------------------------
403 Procedure upd
404 (
405 p_rec in out nocopy per_bil_shd.g_rec_type
406 ) is
407 --
408 l_proc varchar2(72) := g_package||'upd';
409 --
410 Begin
411 hr_utility.set_location('Entering:'||l_proc, 5);
412 --
413 -- We must lock the row which we need to update.
414 --
415 per_bil_shd.lck
416 (
417 p_rec.id_value,
418 p_rec.object_version_number
419 );
420 --
421 -- 1. During an update system defaults are used to determine if
422 -- arguments have been defaulted or not. We must therefore
423 -- derive the full record structure values to be updated.
424 --
425 -- 2. Call the supporting update validate operations.
426 --
427 convert_defs(p_rec);
428 --
429 per_bil_bus.update_validate(p_rec);
430 --
431 -- Call the supporting pre-update operation
432 --
433 pre_update(p_rec);
434 --
435 -- Update the row.
436 --
437 update_dml(p_rec);
438 --
439 -- Call the supporting post-update operation
440 --
441 post_update(p_rec);
442 End upd;
443 --
444 -- ----------------------------------------------------------------------------
445 -- |---------------------------------< upd >----------------------------------|
446 -- ----------------------------------------------------------------------------
447 Procedure upd
448 (
449 p_type in varchar2 default hr_api.g_varchar2,
450 p_business_group_id in number default hr_api.g_number,
451 p_object_version_number in out nocopy number,
452 p_id_value in number,
453 p_fk_value1 in number default hr_api.g_number,
454 p_fk_value2 in number default hr_api.g_number,
455 p_fk_value3 in number default hr_api.g_number,
456 p_text_value1 in varchar2 default hr_api.g_varchar2,
457 p_text_value2 in varchar2 default hr_api.g_varchar2,
458 p_text_value3 in varchar2 default hr_api.g_varchar2,
459 p_text_value4 in varchar2 default hr_api.g_varchar2,
460 p_text_value5 in varchar2 default hr_api.g_varchar2,
461 p_text_value6 in varchar2 default hr_api.g_varchar2,
462 p_text_value7 in varchar2 default hr_api.g_varchar2,
463 p_num_value1 in number default hr_api.g_number,
464 p_num_value2 in number default hr_api.g_number,
465 p_num_value3 in number default hr_api.g_number,
466 p_date_value1 in date default hr_api.g_date,
467 p_date_value2 in date default hr_api.g_date,
468 p_date_value3 in date default hr_api.g_date
469 ) is
470 --
471 l_rec per_bil_shd.g_rec_type;
472 l_proc varchar2(72) := g_package||'upd';
473 --
474 Begin
475 hr_utility.set_location('Entering:'||l_proc, 5);
476 --
477 -- Call conversion function to turn arguments into the
478 -- l_rec structure.
479 --
480 l_rec :=
481 per_bil_shd.convert_args
482 (
483 p_type,
484 p_business_group_id,
485 p_object_version_number,
486 p_id_value,
487 p_fk_value1,
488 p_fk_value2,
489 p_fk_value3,
490 p_text_value1,
491 p_text_value2,
492 p_text_value3,
493 p_text_value4,
494 p_text_value5,
495 p_text_value6,
496 p_text_value7,
497 p_num_value1,
498 p_num_value2,
499 p_num_value3,
500 p_date_value1,
501 p_date_value2,
502 p_date_value3
503 );
504 --
505 -- Having converted the arguments into the
506 -- plsql record structure we call the corresponding record
507 -- business process.
508 --
509 upd(l_rec);
510 p_object_version_number := l_rec.object_version_number;
511 --
512 hr_utility.set_location(' Leaving:'||l_proc, 10);
513 End upd;
514 --
515 end per_bil_upd;