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