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