1 Package Body pqh_bce_upd as
2 /* $Header: pqbcerhi.pkb 115.7 2004/04/28 17:17:08 rthiagar ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_bce_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 -- On the update dml failure it is important to note that we always reset the
37 -- If a check, unique or parent integrity constraint violation is raised the
38 -- constraint_error procedure will be called.
39 -- If any other error is reported, the error will be raised after the
40 --
41 -- Developer Implementation Notes:
42 -- The update 'set' attribute list should be modified if any of your
43 -- attributes are not updateable.
44 --
45 -- Access Status:
46 -- Internal Row Handler Use Only.
47 --
48 -- {End Of Comments}
49 -- ----------------------------------------------------------------------------
50 Procedure update_dml(p_rec in out nocopy pqh_bce_shd.g_rec_type) is
51 --
52 l_proc varchar2(72) := g_package||'update_dml';
53 --
54 Begin
55 hr_utility.set_location('Entering:'||l_proc, 5);
56 --
57 -- Increment the object version
58 --
59 p_rec.object_version_number := p_rec.object_version_number + 1;
60 --
61 --
62 -- Update the pqh_bdgt_cmmtmnt_elmnts Row
63 --
64 update pqh_bdgt_cmmtmnt_elmnts
65 set
66 bdgt_cmmtmnt_elmnt_id = p_rec.bdgt_cmmtmnt_elmnt_id,
67 budget_id = p_rec.budget_id,
68 actual_commitment_type = p_rec.actual_commitment_type,
69 element_type_id = p_rec.element_type_id,
70 salary_basis_flag = p_rec.salary_basis_flag,
71 element_input_value_id = p_rec.element_input_value_id,
72 balance_type_id = p_rec.balance_type_id,
73 frequency_input_value_id = p_rec.frequency_input_value_id,
74 formula_id = p_rec.formula_id,
75 dflt_elmnt_frequency = p_rec.dflt_elmnt_frequency,
76 overhead_percentage = p_rec.overhead_percentage,
77 object_version_number = p_rec.object_version_number
78 where bdgt_cmmtmnt_elmnt_id = p_rec.bdgt_cmmtmnt_elmnt_id;
79 --
80 --
81 hr_utility.set_location(' Leaving:'||l_proc, 10);
82 --
83 Exception
84 When hr_api.check_integrity_violated Then
85 -- A check constraint has been violated
86 pqh_bce_shd.constraint_error
87 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
88 When hr_api.parent_integrity_violated Then
89 -- Parent integrity has been violated
90 pqh_bce_shd.constraint_error
91 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
92 When hr_api.unique_integrity_violated Then
93 -- Unique integrity has been violated
94 pqh_bce_shd.constraint_error
95 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
96 When Others Then
97 Raise;
98 End update_dml;
99 --
100 -- ----------------------------------------------------------------------------
101 -- |------------------------------< pre_update >------------------------------|
102 -- ----------------------------------------------------------------------------
103 -- {Start Of Comments}
104 --
105 -- Description:
106 -- This private procedure contains any processing which is required before
107 -- the update dml.
108 --
109 -- Prerequisites:
110 -- This is an internal procedure which is called from the upd procedure.
111 --
112 -- In Parameters:
113 -- A Pl/Sql record structre.
114 --
115 -- Post Success:
116 -- Processing continues.
117 --
118 -- Post Failure:
119 -- If an error has occurred, an error message and exception will be raised
120 -- but not handled.
121 --
122 -- Developer Implementation Notes:
123 -- Any pre-processing required before the update dml is issued should be
124 -- coded within this procedure. It is important to note that any 3rd party
125 -- maintenance should be reviewed before placing in this procedure.
126 --
127 -- Access Status:
128 -- Internal Row Handler Use Only.
129 --
130 -- {End Of Comments}
131 -- ----------------------------------------------------------------------------
132 Procedure pre_update(p_rec in pqh_bce_shd.g_rec_type) is
133 --
134 l_proc varchar2(72) := g_package||'pre_update';
135 --
136 Begin
137 hr_utility.set_location('Entering:'||l_proc, 5);
138 --
139 hr_utility.set_location(' Leaving:'||l_proc, 10);
140 End pre_update;
141 --
142 -- ----------------------------------------------------------------------------
143 -- |-----------------------------< post_update >------------------------------|
144 -- ----------------------------------------------------------------------------
145 -- {Start Of Comments}
146 --
147 -- Description:
148 -- This private procedure contains any processing which is required after the
149 -- update dml.
150 --
151 -- Prerequisites:
152 -- This is an internal procedure which is called from the upd procedure.
153 --
154 -- In Parameters:
155 -- A Pl/Sql record structre.
156 --
157 -- Post Success:
158 -- Processing continues.
159 --
160 -- Post Failure:
161 -- If an error has occurred, an error message and exception will be raised
162 -- but not handled.
163 --
164 -- Developer Implementation Notes:
165 -- Any post-processing required after the update dml is issued should be
166 -- coded within this procedure. It is important to note that any 3rd party
167 -- maintenance should be reviewed before placing in this procedure.
168 --
169 -- Access Status:
170 -- Internal Row Handler Use Only.
171 --
172 -- {End Of Comments}
173 -- ----------------------------------------------------------------------------
174 Procedure post_update(
175 p_effective_date in date,p_rec in pqh_bce_shd.g_rec_type) is
176 --
177 l_proc varchar2(72) := g_package||'post_update';
178 --
179 Begin
180 hr_utility.set_location('Entering:'||l_proc, 5);
181 --
182 --
183 -- Start of API User Hook for post_update.
184 --
185 begin
186 --
187 pqh_bce_rku.after_update
188 (
189 p_bdgt_cmmtmnt_elmnt_id =>p_rec.bdgt_cmmtmnt_elmnt_id
190 ,p_budget_id =>p_rec.budget_id
191 ,p_actual_commitment_type =>p_rec.actual_commitment_type
192 ,p_element_type_id =>p_rec.element_type_id
193 ,p_salary_basis_flag =>p_rec.salary_basis_flag
194 ,p_element_input_value_id =>p_rec.element_input_value_id
195 ,p_balance_type_id =>p_rec.balance_type_id
196 ,p_frequency_input_value_id =>p_rec.frequency_input_value_id
197 ,p_formula_id =>p_rec.formula_id
198 ,p_dflt_elmnt_frequency =>p_rec.dflt_elmnt_frequency
199 ,p_overhead_percentage =>p_rec.overhead_percentage
200 ,p_object_version_number =>p_rec.object_version_number
201 ,p_effective_date =>p_effective_date
202 ,p_budget_id_o =>pqh_bce_shd.g_old_rec.budget_id
203 ,p_actual_commitment_type_o =>pqh_bce_shd.g_old_rec.actual_commitment_type
204 ,p_element_type_id_o =>pqh_bce_shd.g_old_rec.element_type_id
205 ,p_salary_basis_flag_o =>pqh_bce_shd.g_old_rec.salary_basis_flag
206 ,p_element_input_value_id_o =>pqh_bce_shd.g_old_rec.element_input_value_id
207 ,p_balance_type_id_o =>pqh_bce_shd.g_old_rec.balance_type_id
208 ,p_frequency_input_value_id_o =>pqh_bce_shd.g_old_rec.frequency_input_value_id
209 ,p_formula_id_o =>pqh_bce_shd.g_old_rec.formula_id
210 ,p_dflt_elmnt_frequency_o =>pqh_bce_shd.g_old_rec.dflt_elmnt_frequency
211 ,p_overhead_percentage_o =>pqh_bce_shd.g_old_rec.overhead_percentage
212 ,p_object_version_number_o =>pqh_bce_shd.g_old_rec.object_version_number
213 );
214 --
215 exception
216 --
217 when hr_api.cannot_find_prog_unit then
218 --
219 hr_api.cannot_find_prog_unit_error
220 (p_module_name => 'pqh_bdgt_cmmtmnt_elmnts'
221 ,p_hook_type => 'AU');
222 --
223 end;
224 --
225 -- End of API User Hook for post_update.
226 --
227 --
228 hr_utility.set_location(' Leaving:'||l_proc, 10);
229 End post_update;
230 --
231 -- ----------------------------------------------------------------------------
232 -- |-----------------------------< convert_defs >-----------------------------|
233 -- ----------------------------------------------------------------------------
234 -- {Start Of Comments}
235 --
236 -- Description:
237 -- The Convert_Defs procedure has one very important function:
238 -- It must return the record structure for the row with all system defaulted
239 -- values converted into its corresponding parameter value for update. When
240 -- we attempt to update a row through the Upd process , certain
241 -- parameters can be defaulted which enables flexibility in the calling of
242 -- the upd process (e.g. only attributes which need to be updated need to be
243 -- specified). For the upd process to determine which attributes
244 -- have NOT been specified we need to check if the parameter has a reserved
245 -- system default value. Therefore, for all parameters which have a
246 -- corresponding reserved system default mechanism specified we need to
247 -- check if a system default is being used. If a system default is being
248 -- used then we convert the defaulted value into its corresponding attribute
249 -- value held in the g_old_rec data structure.
250 --
251 -- Prerequisites:
252 -- This private function can only be called from the upd process.
253 --
254 -- In Parameters:
255 -- A Pl/Sql record structre.
256 --
257 -- Post Success:
258 -- The record structure will be returned with all system defaulted parameter
259 -- values converted into its current row attribute value.
260 --
261 -- Post Failure:
262 -- No direct error handling is required within this function. Any possible
263 -- errors within this procedure will be a PL/SQL value error due to conversion
264 -- of datatypes or data lengths.
265 --
266 -- Developer Implementation Notes:
267 -- None.
268 --
269 -- Access Status:
270 -- Internal Row Handler Use Only.
271 --
272 -- {End Of Comments}
273 -- ----------------------------------------------------------------------------
274 Procedure convert_defs(p_rec in out nocopy pqh_bce_shd.g_rec_type) is
275 --
276 l_proc varchar2(72) := g_package||'convert_defs';
277 --
278 Begin
279 --
280 hr_utility.set_location('Entering:'||l_proc, 5);
281 --
282 -- We must now examine each argument value in the
283 -- p_rec plsql record structure
284 -- to see if a system default is being used. If a system default
285 -- is being used then we must set to the 'current' argument value.
286 --
287 If (p_rec.budget_id = hr_api.g_number) then
288 p_rec.budget_id :=
289 pqh_bce_shd.g_old_rec.budget_id;
290 End If;
291 If (p_rec.actual_commitment_type = hr_api.g_varchar2) then
292 p_rec.actual_commitment_type :=
293 pqh_bce_shd.g_old_rec.actual_commitment_type;
294 End If;
295 If (p_rec.element_type_id = hr_api.g_number) then
296 p_rec.element_type_id :=
297 pqh_bce_shd.g_old_rec.element_type_id;
298 End If;
299 If (p_rec.salary_basis_flag = hr_api.g_varchar2) then
300 p_rec.salary_basis_flag :=
301 pqh_bce_shd.g_old_rec.salary_basis_flag;
302 End If;
303 If (p_rec.element_input_value_id = hr_api.g_number) then
304 p_rec.element_input_value_id :=
305 pqh_bce_shd.g_old_rec.element_input_value_id;
306 End If;
307 If (p_rec.balance_type_id = hr_api.g_number) then
308 p_rec.balance_type_id :=
309 pqh_bce_shd.g_old_rec.balance_type_id;
310 End If;
311 If (p_rec.frequency_input_value_id = hr_api.g_number) then
312 p_rec.frequency_input_value_id :=
313 pqh_bce_shd.g_old_rec.frequency_input_value_id;
314 End If;
315 If (p_rec.formula_id = hr_api.g_number) then
316 p_rec.formula_id :=
317 pqh_bce_shd.g_old_rec.formula_id;
318 End If;
319 If (p_rec.dflt_elmnt_frequency = hr_api.g_varchar2) then
320 p_rec.dflt_elmnt_frequency :=
321 pqh_bce_shd.g_old_rec.dflt_elmnt_frequency;
322 End If;
323 If (p_rec.overhead_percentage = hr_api.g_number) then
324 p_rec.overhead_percentage :=
325 pqh_bce_shd.g_old_rec.overhead_percentage;
326 End If;
327
328 --
329 hr_utility.set_location(' Leaving:'||l_proc, 10);
330 --
331 End convert_defs;
332 --
333 -- ----------------------------------------------------------------------------
334 -- |---------------------------------< upd >----------------------------------|
335 -- ----------------------------------------------------------------------------
336 Procedure upd
337 (
338 p_effective_date in date,
339 p_rec in out nocopy pqh_bce_shd.g_rec_type
340 ) is
341 --
342 l_proc varchar2(72) := g_package||'upd';
343 --
344 Begin
345 hr_utility.set_location('Entering:'||l_proc, 5);
346 --
347 -- We must lock the row which we need to update.
348 --
349 pqh_bce_shd.lck
350 (
351 p_rec.bdgt_cmmtmnt_elmnt_id,
352 p_rec.object_version_number
353 );
354 --
355 -- 1. During an update system defaults are used to determine if
356 -- arguments have been defaulted or not. We must therefore
357 -- derive the full record structure values to be updated.
358 --
359 -- 2. Call the supporting update validate operations.
360 --
361 convert_defs(p_rec);
362 pqh_bce_bus.update_validate(p_rec
363 ,p_effective_date);
364 --
365 -- Call the supporting pre-update operation
366 --
367 pre_update(p_rec);
368 --
369 -- Update the row.
370 --
371 update_dml(p_rec);
372 --
373 -- Call the supporting post-update operation
374 --
375 post_update(
376 p_effective_date,p_rec);
377 End upd;
378 --
379 -- ----------------------------------------------------------------------------
380 -- |---------------------------------< upd >----------------------------------|
381 -- ----------------------------------------------------------------------------
382 Procedure upd
383 (
384 p_effective_date in date,
385 p_bdgt_cmmtmnt_elmnt_id in number,
386 p_budget_id in number default hr_api.g_number,
387 p_actual_commitment_type in varchar2 default hr_api.g_varchar2,
388 p_element_type_id in number default hr_api.g_number,
389 p_salary_basis_flag in varchar2 default hr_api.g_varchar2,
390 p_element_input_value_id in number default hr_api.g_number,
391 p_balance_type_id in number default hr_api.g_number,
392 p_frequency_input_value_id in number default hr_api.g_number,
393 p_formula_id in number default hr_api.g_number,
394 p_dflt_elmnt_frequency in varchar2 default hr_api.g_varchar2,
395 p_overhead_percentage in number default hr_api.g_number,
396 p_object_version_number in out nocopy number
397 ) is
398 --
399 l_rec pqh_bce_shd.g_rec_type;
400 l_proc varchar2(72) := g_package||'upd';
401 --
402 Begin
403 hr_utility.set_location('Entering:'||l_proc, 5);
404 --
405 -- Call conversion function to turn arguments into the
406 -- l_rec structure.
407 --
408 l_rec :=
409 pqh_bce_shd.convert_args
410 (
411 p_bdgt_cmmtmnt_elmnt_id,
412 p_budget_id,
413 p_actual_commitment_type,
414 p_element_type_id,
415 p_salary_basis_flag,
416 p_element_input_value_id,
417 p_balance_type_id,
418 p_frequency_input_value_id,
419 p_formula_id,
420 p_dflt_elmnt_frequency,
421 p_overhead_percentage,
422 p_object_version_number
423 );
424 --
425 -- Having converted the arguments into the
426 -- plsql record structure we call the corresponding record
427 -- business process.
428 --
429 upd(
430 p_effective_date,l_rec);
431 p_object_version_number := l_rec.object_version_number;
432 --
433 hr_utility.set_location(' Leaving:'||l_proc, 10);
434 End upd;
435 --
436 end pqh_bce_upd;