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