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