1 Package Body pqh_rng_upd as
2 /* $Header: pqrngrhi.pkb 115.18 2004/06/24 16:51:43 srajakum noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_rng_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_rng_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_attribute_ranges Row
61 --
62 update pqh_attribute_ranges
63 set
64 attribute_range_id = p_rec.attribute_range_id,
65 approver_flag = p_rec.approver_flag,
66 enable_flag = p_rec.enable_flag,
67 delete_flag = p_rec.delete_flag,
68 assignment_id = p_rec.assignment_id,
69 attribute_id = p_rec.attribute_id,
70 from_char = p_rec.from_char,
71 from_date = p_rec.from_date,
72 from_number = p_rec.from_number,
73 position_id = p_rec.position_id,
74 range_name = p_rec.range_name,
75 routing_category_id = p_rec.routing_category_id,
76 routing_list_member_id = p_rec.routing_list_member_id,
77 to_char = p_rec.to_char,
78 to_date = p_rec.to_date,
79 to_number = p_rec.to_number,
80 object_version_number = p_rec.object_version_number
81 where attribute_range_id = p_rec.attribute_range_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_rng_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_rng_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_rng_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_rng_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_rng_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_rng_rku.after_update
191 (
192 p_attribute_range_id =>p_rec.attribute_range_id
193 ,p_approver_flag =>p_rec.approver_flag
194 ,p_enable_flag =>p_rec.enable_flag
195 ,p_delete_flag =>p_rec.delete_flag
196 ,p_assignment_id =>p_rec.assignment_id
197 ,p_attribute_id =>p_rec.attribute_id
198 ,p_from_char =>p_rec.from_char
199 ,p_from_date =>p_rec.from_date
200 ,p_from_number =>p_rec.from_number
201 ,p_position_id =>p_rec.position_id
202 ,p_range_name =>p_rec.range_name
203 ,p_routing_category_id =>p_rec.routing_category_id
204 ,p_routing_list_member_id =>p_rec.routing_list_member_id
205 ,p_to_char =>p_rec.to_char
206 ,p_to_date =>p_rec.to_date
207 ,p_to_number =>p_rec.to_number
208 ,p_object_version_number =>p_rec.object_version_number
209 ,p_effective_date =>p_effective_date
210 ,p_approver_flag_o =>pqh_rng_shd.g_old_rec.approver_flag
211 ,p_enable_flag_o =>pqh_rng_shd.g_old_rec.enable_flag
212 ,p_delete_flag_o =>pqh_rng_shd.g_old_rec.delete_flag
213 ,p_assignment_id_o =>pqh_rng_shd.g_old_rec.assignment_id
214 ,p_attribute_id_o =>pqh_rng_shd.g_old_rec.attribute_id
215 ,p_from_char_o =>pqh_rng_shd.g_old_rec.from_char
216 ,p_from_date_o =>pqh_rng_shd.g_old_rec.from_date
217 ,p_from_number_o =>pqh_rng_shd.g_old_rec.from_number
218 ,p_position_id_o =>pqh_rng_shd.g_old_rec.position_id
219 ,p_range_name_o =>pqh_rng_shd.g_old_rec.range_name
220 ,p_routing_category_id_o =>pqh_rng_shd.g_old_rec.routing_category_id
221 ,p_routing_list_member_id_o =>pqh_rng_shd.g_old_rec.routing_list_member_id
222 ,p_to_char_o =>pqh_rng_shd.g_old_rec.to_char
223 ,p_to_date_o =>pqh_rng_shd.g_old_rec.to_date
224 ,p_to_number_o =>pqh_rng_shd.g_old_rec.to_number
225 ,p_object_version_number_o =>pqh_rng_shd.g_old_rec.object_version_number
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_attribute_ranges'
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_rng_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.approver_flag = hr_api.g_varchar2) then
301 p_rec.approver_flag :=
302 pqh_rng_shd.g_old_rec.approver_flag;
303 End If;
304 If (p_rec.enable_flag = hr_api.g_varchar2) then
305 p_rec.enable_flag :=
306 pqh_rng_shd.g_old_rec.enable_flag;
307 End If;
308 If (p_rec.delete_flag = hr_api.g_varchar2) then
309 p_rec.delete_flag :=
310 pqh_rng_shd.g_old_rec.delete_flag;
311 End If;
312 If (p_rec.assignment_id = hr_api.g_number) then
313 p_rec.assignment_id :=
314 pqh_rng_shd.g_old_rec.assignment_id;
315 End If;
316 If (p_rec.attribute_id = hr_api.g_number) then
317 p_rec.attribute_id :=
318 pqh_rng_shd.g_old_rec.attribute_id;
319 End If;
320 If (p_rec.from_char = hr_api.g_varchar2) then
321 p_rec.from_char :=
322 pqh_rng_shd.g_old_rec.from_char;
323 End If;
324 If (p_rec.from_date = hr_api.g_date) then
325 p_rec.from_date :=
326 pqh_rng_shd.g_old_rec.from_date;
327 End If;
328 If (p_rec.from_number = hr_api.g_number) then
329 p_rec.from_number :=
330 pqh_rng_shd.g_old_rec.from_number;
331 End If;
332 If (p_rec.position_id = hr_api.g_number) then
333 p_rec.position_id :=
334 pqh_rng_shd.g_old_rec.position_id;
335 End If;
336 If (p_rec.range_name = hr_api.g_varchar2) then
337 p_rec.range_name :=
338 pqh_rng_shd.g_old_rec.range_name;
339 End If;
340 If (p_rec.routing_category_id = hr_api.g_number) then
341 p_rec.routing_category_id :=
342 pqh_rng_shd.g_old_rec.routing_category_id;
343 End If;
344 If (p_rec.routing_list_member_id = hr_api.g_number) then
345 p_rec.routing_list_member_id :=
346 pqh_rng_shd.g_old_rec.routing_list_member_id;
347 End If;
348 If (p_rec.to_char = hr_api.g_varchar2) then
349 p_rec.to_char :=
350 pqh_rng_shd.g_old_rec.to_char;
351 End If;
352 If (p_rec.to_date = hr_api.g_date) then
353 p_rec.to_date :=
354 pqh_rng_shd.g_old_rec.to_date;
355 End If;
356 If (p_rec.to_number = hr_api.g_number) then
357 p_rec.to_number :=
358 pqh_rng_shd.g_old_rec.to_number;
359 End If;
360
361 --
362 hr_utility.set_location(' Leaving:'||l_proc, 10);
363 --
364 End convert_defs;
365 --
366 -- ----------------------------------------------------------------------------
367 -- |---------------------------------< upd >----------------------------------|
368 -- ----------------------------------------------------------------------------
369 Procedure upd
370 (
371 p_effective_date in date,
372 p_rec in out nocopy pqh_rng_shd.g_rec_type
373 ) is
374 --
375 l_proc varchar2(72) := g_package||'upd';
376 --
377 Begin
378 hr_utility.set_location('Entering:'||l_proc, 5);
379 --
380 -- We must lock the row which we need to update.
381 --
382 pqh_rng_shd.lck
383 (
384 p_rec.attribute_range_id,
385 p_rec.object_version_number
386 );
387 --
388 -- 1. During an update system defaults are used to determine if
389 -- arguments have been defaulted or not. We must therefore
390 -- derive the full record structure values to be updated.
391 --
392 -- 2. Call the supporting update validate operations.
393 --
394 convert_defs(p_rec);
395 pqh_rng_bus.update_validate(p_rec
396 ,p_effective_date);
397 --
398 -- Call the supporting pre-update operation
399 --
400 pre_update(p_rec);
401 --
402 -- Update the row.
403 --
404 update_dml(p_rec);
405 --
406 -- Call the supporting post-update operation
407 --
408 post_update(
409 p_effective_date,p_rec);
410 End upd;
411 --
412 -- ----------------------------------------------------------------------------
413 -- |---------------------------------< upd >----------------------------------|
414 -- ----------------------------------------------------------------------------
415 Procedure upd
416 (
417 p_effective_date in date,
418 p_attribute_range_id in number,
419 p_approver_flag in varchar2 default hr_api.g_varchar2,
420 p_enable_flag in varchar2 default hr_api.g_varchar2,
421 p_delete_flag in varchar2 default hr_api.g_varchar2,
422 p_assignment_id in number default hr_api.g_number,
423 p_attribute_id in number default hr_api.g_number,
424 p_from_char in varchar2 default hr_api.g_varchar2,
425 p_from_date in date default hr_api.g_date,
426 p_from_number in number default hr_api.g_number,
427 p_position_id in number default hr_api.g_number,
428 p_range_name in varchar2 default hr_api.g_varchar2,
429 p_routing_category_id in number default hr_api.g_number,
430 p_routing_list_member_id in number default hr_api.g_number,
431 p_to_char in varchar2 default hr_api.g_varchar2,
432 p_to_date in date default hr_api.g_date,
433 p_to_number in number default hr_api.g_number,
434 p_object_version_number in out nocopy number
435 ) is
436 --
437 l_rec pqh_rng_shd.g_rec_type;
438 l_proc varchar2(72) := g_package||'upd';
439 --
440 Begin
441 hr_utility.set_location('Entering:'||l_proc, 5);
442 --
443 -- Call conversion function to turn arguments into the
444 -- l_rec structure.
445 --
446 l_rec :=
447 pqh_rng_shd.convert_args
448 (
449 p_attribute_range_id,
450 p_approver_flag,
451 p_enable_flag,
452 p_delete_flag,
453 p_assignment_id,
454 p_attribute_id,
455 p_from_char,
456 p_from_date,
457 p_from_number,
458 p_position_id,
459 p_range_name,
460 p_routing_category_id,
461 p_routing_list_member_id,
462 p_to_char,
463 p_to_date,
464 p_to_number,
465 p_object_version_number
466 );
467 --
468 -- Having converted the arguments into the
469 -- plsql record structure we call the corresponding record
470 -- business process.
471 --
472 upd(
473 p_effective_date,l_rec);
474 p_object_version_number := l_rec.object_version_number;
475 --
476 hr_utility.set_location(' Leaving:'||l_proc, 10);
477 End upd;
478 --
479 end pqh_rng_upd;
[Home] [Help]