1 Package Body ghr_pdh_upd as
2 /* $Header: ghpdhrhi.pkb 120.1 2006/01/17 06:21:22 sumarimu noship $ */
3
4 --
5 -- ----------------------------------------------------------------------------
6 -- | Private Global Definitions |
7 -- ----------------------------------------------------------------------------
8 --
9 g_package varchar2(33) := ' ghr_pdh_upd.'; -- Global package name
10 --
11 -- ----------------------------------------------------------------------------
12 -- |------------------------------< update_dml >------------------------------|
13 -- ----------------------------------------------------------------------------
14 -- {Start Of Comments}
15 --
16 -- Description:
17 -- This procedure controls the actual dml update logic. The processing of
18 -- this procedure is:
19 -- 1) Increment the object_version_number by 1 if the object_version_number
20 -- is defined as an attribute for this entity.
21 -- 2) To update the specified row in the schema using the primary key in
22 -- the predicates.
23 -- 3) To trap any constraint violations that may have occurred.
24 -- 4) To raise any other errors.
25 --
26 -- Pre Conditions:
27 -- This is an internal private procedure which must be called from the upd
28 -- procedure.
29 --
30 -- In Parameters:
31 -- A Pl/Sql record structre.
32 --
33 -- Post Success:
34 -- The specified row will be updated in the schema.
35 --
36 -- Post Failure:
37 -- If a check, unique or parent integrity constraint violation is raised the
38 -- constraint_error procedure will be called.
39 --
40 -- Developer Implementation Notes:
41 -- The update 'set' attribute list should be modified if any of your
42 -- attributes are not updateable.
43 --
44 -- Access Status:
45 -- Internal Table Handler Use Only.
46 --
47 -- {End Of Comments}
48 -- ----------------------------------------------------------------------------
49 Procedure update_dml(p_rec in out NOCOPY ghr_pdh_shd.g_rec_type) is
50 --
51 l_proc varchar2(72) := g_package||'update_dml';
52 --
53 Begin
54 hr_utility.set_location('Entering:'||l_proc, 5);
55 --
56 -- Increment the object version
57 --
58 p_rec.object_version_number := p_rec.object_version_number + 1;
59
60 --
61 -- Update the ghr_pd_routing_history Row
62 --
63
64
65 update ghr_pd_routing_history
66 set
67 pd_routing_history_id = p_rec.pd_routing_history_id,
68 initiator_flag = p_rec.initiator_flag,
69 requester_flag = p_rec.requester_flag,
70 approver_flag = p_rec.approver_flag,
71 reviewer_flag = p_rec.reviewer_flag,
72 authorizer_flag = p_rec.authorizer_flag,
73 personnelist_flag = p_rec.personnelist_flag,
74 approved_flag = p_rec.approved_flag,
75 user_name = p_rec.user_name,
76 user_name_employee_id = p_rec.user_name_employee_id,
77 user_name_emp_first_name = p_rec.user_name_emp_first_name,
78 user_name_emp_last_name = p_rec.user_name_emp_last_name,
79 user_name_emp_middle_names = p_rec.user_name_emp_middle_names,
80 action_taken = p_rec.action_taken,
81 date_notification_sent = p_rec.date_notification_sent,
82 object_version_number = p_rec.object_version_number
83 where pd_routing_history_id = p_rec.pd_routing_history_id;
84 --
85 hr_utility.set_location(' Leaving:'||l_proc, 10);
86 --
87 Exception
88 When hr_api.check_integrity_violated Then
89 -- A check constraint has been violated
90 ghr_pdh_shd.constraint_error
91 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
92 When hr_api.parent_integrity_violated Then
93 -- Parent integrity has been violated
94 ghr_pdh_shd.constraint_error
95 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
96 When hr_api.unique_integrity_violated Then
97 -- Unique integrity has been violated
98 ghr_pdh_shd.constraint_error
99 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
100 When Others Then
101 Raise;
102 End update_dml;
103 --
104 -- ----------------------------------------------------------------------------
105 -- |------------------------------< pre_update >------------------------------|
106 -- ----------------------------------------------------------------------------
107 -- {Start Of Comments}
108 --
109 -- Description:
110 -- This private procedure contains any processing which is required before
111 -- the update dml.
112 --
113 -- Pre Conditions:
114 -- This is an internal procedure which is called from the upd procedure.
115 --
116 -- In Parameters:
117 -- A Pl/Sql record structre.
118 --
119 -- Post Success:
120 -- Processing continues.
121 --
122 -- Post Failure:
123 -- If an error has occurred, an error message and exception will be raised
124 -- but not handled.
125 --
126 -- Developer Implementation Notes:
127 -- Any pre-processing required before the update dml is issued should be
128 -- coded within this procedure. It is important to note that any 3rd party
129 -- maintenance should be reviewed before placing in this procedure.
130 --
131 -- Access Status:
132 -- Internal Table Handler Use Only.
133 --
134 -- {End Of Comments}
135 -- ----------------------------------------------------------------------------
136 Procedure pre_update(p_rec in ghr_pdh_shd.g_rec_type) is
137 --
138 l_proc varchar2(72) := g_package||'pre_update';
139 --
140 Begin
141 hr_utility.set_location('Entering:'||l_proc, 5);
142 --
143 hr_utility.set_location(' Leaving:'||l_proc, 10);
144 End pre_update;
145 --
146 -- ----------------------------------------------------------------------------
147 -- |-----------------------------< post_update >------------------------------|
148 -- ----------------------------------------------------------------------------
149 -- {Start Of Comments}
150 --
151 -- Description:
152 -- This private procedure contains any processing which is required after the
153 -- update dml.
154 --
155 -- Pre Conditions:
156 -- This is an internal procedure which is called from the upd procedure.
157 --
158 -- In Parameters:
159 -- A Pl/Sql record structre.
160 --
161 -- Post Success:
162 -- Processing continues.
163 --
164 -- Post Failure:
165 -- If an error has occurred, an error message and exception will be raised
166 -- but not handled.
167 --
168 -- Developer Implementation Notes:
169 -- Any post-processing required after the update dml is issued should be
170 -- coded within this procedure. It is important to note that any 3rd party
171 -- maintenance should be reviewed before placing in this procedure.
172 --
173 -- Access Status:
174 -- Internal Table Handler Use Only.
175 --
176 -- {End Of Comments}
177 -- ----------------------------------------------------------------------------
178 Procedure post_update(p_rec in ghr_pdh_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 -- This is a hook point and the user hook for post_update is called here.
186 --
187 begin
188 ghr_pdh_rku.after_update (
189 p_pd_routing_history_id => p_rec.pd_routing_history_id,
190 p_position_description_id => p_rec.position_description_id,
191 p_initiator_flag => p_rec.initiator_flag,
192 p_requester_flag => p_rec.requester_flag,
193 p_approver_flag => p_rec.approver_flag,
194 p_personnelist_flag => p_rec.personnelist_flag,
195 p_reviewer_flag => p_rec.reviewer_flag,
196 p_authorizer_flag => p_rec.authorizer_flag,
197 p_approved_flag => p_rec.approved_flag,
198 p_user_name => p_rec.user_name,
199 p_user_name_employee_id => p_rec.user_name_employee_id,
200 p_user_name_emp_first_name => p_rec.user_name_emp_first_name,
201 p_user_name_emp_last_name => p_rec.user_name_emp_last_name,
202 p_user_name_emp_middle_names => p_rec.user_name_emp_middle_names,
203 p_action_taken => p_rec.action_taken,
204 p_groupbox_id => p_rec.groupbox_id,
205 p_routing_list_id => p_rec.routing_list_id,
206 p_routing_seq_number => p_rec.routing_seq_number,
207 p_date_notification_sent => p_rec.date_notification_sent,
208 p_item_key => p_rec.item_key,
209 p_object_version_number => p_rec.object_version_number,
210 p_position_description_id_o => ghr_pdh_shd.g_old_rec.position_description_id,
211 p_initiator_flag_o => ghr_pdh_shd.g_old_rec.initiator_flag,
212 p_requester_flag_o => ghr_pdh_shd.g_old_rec.requester_flag,
213 p_approver_flag_o => ghr_pdh_shd.g_old_rec.approver_flag,
214 p_personnelist_flag_o => ghr_pdh_shd.g_old_rec.personnelist_flag,
215 p_reviewer_flag_o => ghr_pdh_shd.g_old_rec.reviewer_flag,
216 p_authorizer_flag_o => ghr_pdh_shd.g_old_rec.authorizer_flag,
217 p_approved_flag_o => ghr_pdh_shd.g_old_rec.approved_flag,
218 p_user_name_o => ghr_pdh_shd.g_old_rec.user_name,
219 p_user_name_employee_id_o => ghr_pdh_shd.g_old_rec.user_name_employee_id,
220 p_user_name_emp_first_name_o => ghr_pdh_shd.g_old_rec.user_name_emp_first_name,
221 p_user_name_emp_last_name_o => ghr_pdh_shd.g_old_rec.user_name_emp_last_name,
222 p_user_name_emp_middle_names_o=> ghr_pdh_shd.g_old_rec.user_name_emp_middle_names,
223 p_action_taken_o => ghr_pdh_shd.g_old_rec.action_taken,
224 p_groupbox_id_o => ghr_pdh_shd.g_old_rec.groupbox_id,
225 p_routing_list_id_o => ghr_pdh_shd.g_old_rec.routing_list_id,
226 p_routing_seq_number_o => ghr_pdh_shd.g_old_rec.routing_seq_number,
227 p_date_notification_sent_o => ghr_pdh_shd.g_old_rec.date_notification_sent,
228 p_item_key_o => ghr_pdh_shd.g_old_rec.item_key,
229 p_object_version_number_o => ghr_pdh_shd.g_old_rec.object_version_number );
230 exception
231 when hr_api.cannot_find_prog_unit then
232 hr_api.cannot_find_prog_unit_error
233 ( p_module_name => 'GHR_PD_ROUTING_HISTORY'
234 ,p_hook_type => 'AU'
235 );
236 end;
237 -- End of API User Hook for post_update.
238 --
239 hr_utility.set_location(' Leaving:'||l_proc, 10);
240 End post_update;
241 --
242 -- ----------------------------------------------------------------------------
243 -- |-----------------------------< convert_defs >-----------------------------|
244 -- ----------------------------------------------------------------------------
245 -- {Start Of Comments}
246 --
247 -- Description:
248 -- The Convert_Defs procedure has one very important function:
249 -- It must return the record structure for the row with all system defaulted
250 -- values converted into its corresponding parameter value for update. When
251 -- we attempt to update a row through the Upd process , certain
252 -- parameters can be defaulted which enables flexibility in the calling of
253 -- the upd process (e.g. only attributes which need to be updated need to be
254 -- specified). For the upd process to determine which attributes
255 -- have NOT been specified we need to check if the parameter has a reserved
256 -- system default value. Therefore, for all parameters which have a
257 -- corresponding reserved system default mechanism specified we need to
258 -- check if a system default is being used. If a system default is being
259 -- used then we convert the defaulted value into its corresponding attribute
260 -- value held in the g_old_rec data structure.
261 --
262 -- Pre Conditions:
263 -- This private function can only be called from the upd process.
264 --
265 -- In Parameters:
266 -- A Pl/Sql record structre.
267 --
268 -- Post Success:
269 -- The record structure will be returned with all system defaulted parameter
270 -- values converted into its current row attribute value.
271 --
272 -- Post Failure:
273 -- No direct error handling is required within this function. Any possible
274 -- errors within this procedure will be a PL/SQL value error due to conversion
275
276 -- of datatypes or data lengths.
277 --
278 -- Developer Implementation Notes:
279 -- None.
280 --
281 -- Access Status:
282 -- Internal Table Handler Use Only.
283 --
284 -- {End Of Comments}
285 -- ----------------------------------------------------------------------------
286 Procedure convert_defs(p_rec in out NOCOPY ghr_pdh_shd.g_rec_type) is
287 --
288 l_proc varchar2(72) := g_package||'convert_defs';
289 --
290 Begin
291 --
292 hr_utility.set_location('Entering:'||l_proc, 5);
293 --
294 -- We must now examine each argument value in the
295 -- p_rec plsql record structure
296 -- to see if a system default is being used. If a system default
297 -- is being used then we must set to the 'current' argument value.
298 --
299 If (p_rec.position_description_id = hr_api.g_number) then
300 p_rec.position_description_id :=
301 ghr_pdh_shd.g_old_rec.position_description_id;
302 End If;
303 -- If (p_rec.attachment_modified_flag = hr_api.g_varchar2) then
304 -- p_rec.attachment_modified_flag :=
305 -- ghr_pdh_shd.g_old_rec.attachment_modified_flag;
306 -- End If;
307 If (p_rec.initiator_flag = hr_api.g_varchar2) then
308 p_rec.initiator_flag :=
309 ghr_pdh_shd.g_old_rec.initiator_flag;
310 End If;
311 If (p_rec.requester_flag = hr_api.g_varchar2) then
312 p_rec.requester_flag :=
313 ghr_pdh_shd.g_old_rec.requester_flag;
314 End If;
315 If (p_rec.approver_flag = hr_api.g_varchar2) then
316 p_rec.approver_flag :=
317 ghr_pdh_shd.g_old_rec.approver_flag;
318 End If;
319 If (p_rec.reviewer_flag = hr_api.g_varchar2) then
320 p_rec.reviewer_flag :=
321 ghr_pdh_shd.g_old_rec.reviewer_flag;
322 End If;
323 -- If (p_rec.requester_flag = hr_api.g_varchar2) then
324 -- p_rec.requester_flag :=
325 -- ghr_pdh_shd.g_old_rec.requester_flag;
326 -- End If;
327 If (p_rec.authorizer_flag = hr_api.g_varchar2) then
328 p_rec.authorizer_flag :=
329 ghr_pdh_shd.g_old_rec.authorizer_flag;
330 End If;
331 If (p_rec.personnelist_flag = hr_api.g_varchar2) then
332 p_rec.personnelist_flag :=
333 ghr_pdh_shd.g_old_rec.authorizer_flag;
334 End If;
335 If (p_rec.approved_flag = hr_api.g_varchar2) then
336 p_rec.approved_flag :=
337 ghr_pdh_shd.g_old_rec.approved_flag;
338 End If;
339 If (p_rec.user_name_employee_id = hr_api.g_number) then
340 p_rec.user_name_employee_id :=
341 ghr_pdh_shd.g_old_rec.user_name_employee_id;
342 End If;
343 -- If (p_rec.notepad = hr_api.g_varchar2) then
344 -- p_rec.notepad :=
345 -- ghr_pdh_shd.g_old_rec.notepad;
346 -- End If;
347 If (p_rec.action_taken = hr_api.g_varchar2) then
348 p_rec.action_taken :=
349 ghr_pdh_shd.g_old_rec.action_taken;
350 End If;
351 If (p_rec.user_name = hr_api.g_varchar2) then
352 p_rec.user_name :=
353 ghr_pdh_shd.g_old_rec.user_name;
354 End If;
355 If (p_rec.groupbox_id = hr_api.g_number) then
356 p_rec.groupbox_id :=
357 ghr_pdh_shd.g_old_rec.groupbox_id;
358 End If;
359 If (p_rec.routing_list_id = hr_api.g_number) then
360 p_rec.routing_list_id :=
361 ghr_pdh_shd.g_old_rec.routing_list_id;
362 End If;
363 If (p_rec.routing_seq_number = hr_api.g_number) then
364 p_rec.routing_seq_number :=
365 ghr_pdh_shd.g_old_rec.routing_seq_number;
366 End If;
367 -- If (p_rec.nature_of_action_id = hr_api.g_number) then
368 -- p_rec.nature_of_action_id :=
369 -- ghr_pdh_shd.g_old_rec.nature_of_action_id;
370 -- End If;
371 If (p_rec.user_name_emp_first_name = hr_api.g_varchar2) then
372 p_rec.user_name_emp_first_name :=
373 ghr_pdh_shd.g_old_rec.user_name_emp_first_name;
374 End If;
375 If (p_rec.user_name_emp_last_name = hr_api.g_varchar2) then
376 p_rec.user_name_emp_last_name :=
377 ghr_pdh_shd.g_old_rec.user_name_emp_last_name;
378 End If;
379 If (p_rec.user_name_emp_middle_names = hr_api.g_varchar2) then
380 p_rec.user_name_emp_middle_names :=
381 ghr_pdh_shd.g_old_rec.user_name_emp_middle_names;
382 End If;
383 If (p_rec.date_notification_sent = hr_api.g_date) then
384 p_rec.date_notification_sent :=
385 ghr_pdh_shd.g_old_rec.date_notification_sent;
386 End If;
387 If (p_rec.item_key = hr_api.g_varchar2) then
388 p_rec.item_key :=
389 ghr_pdh_shd.g_old_rec.item_key;
390 End If;
391 --
392 hr_utility.set_location(' Leaving:'||l_proc, 10);
393 --
394 End convert_defs;
395 --
396 -- ----------------------------------------------------------------------------
397 -- |---------------------------------< upd >----------------------------------|
398 -- ----------------------------------------------------------------------------
399 Procedure upd
400 (
401 p_rec in out NOCOPY ghr_pdh_shd.g_rec_type,
402 p_validate in boolean default false
403 ) is
404 --
405 l_proc varchar2(72) := g_package||'upd';
406 --
407 Begin
408 hr_utility.set_location('Entering:'||l_proc, 5);
409 --
410 -- Determine if the business process is to be validated.
411 --
412 If p_validate then
413 --
414 -- Issue the savepoint.
415 --
416 SAVEPOINT upd_ghr_pdh;
417 End If;
418 --
419 -- We must lock the row which we need to update.
420 --
421 ghr_pdh_shd.lck
422 (
423 p_rec.pd_routing_history_id,
424 p_rec.object_version_number
425 );
426 --
427 -- 1. During an update system defaults are used to determine if
428 -- arguments have been defaulted or not. We must therefore
429 -- derive the full record structure values to be updated.
430 --
431 -- 2. Call the supporting update validate operations.
432 --
433 convert_defs(p_rec);
434 ghr_pdh_bus.update_validate(p_rec);
435 --
436 -- Call the supporting pre-update operation
437 --
438 pre_update(p_rec);
439 --
440 -- Update the row.
441 --
442 update_dml(p_rec);
443 --
444 -- Call the supporting post-update operation
445 --
446 post_update(p_rec);
447 --
448 -- If we are validating then raise the Validate_Enabled exception
449 --
450 If p_validate then
451 Raise HR_Api.Validate_Enabled;
452 End If;
453 --
454 hr_utility.set_location(' Leaving:'||l_proc, 10);
455 Exception
456 When HR_Api.Validate_Enabled Then
457 --
458 -- As the Validate_Enabled exception has been raised
459 -- we must rollback to the savepoint
460 --
461 ROLLBACK TO upd_ghr_pdh;
462 End upd;
463 --
464 -- ----------------------------------------------------------------------------
465 -- |---------------------------------< upd >----------------------------------|
466 -- ----------------------------------------------------------------------------
467 Procedure upd
468 (
469 p_pd_routing_history_id in number,
470 p_initiator_flag in varchar2 default hr_api.g_varchar2,
471 p_requester_flag in varchar2 default hr_api.g_varchar2,
472 p_approver_flag in varchar2 default hr_api.g_varchar2,
473 p_reviewer_flag in varchar2 default hr_api.g_varchar2,
474 p_authorizer_flag in varchar2 default hr_api.g_varchar2,
475 p_personnelist_flag in varchar2 default hr_api.g_varchar2,
476 p_approved_flag in varchar2 default hr_api.g_varchar2,
477 p_user_name in varchar2 default hr_api.g_varchar2,
478 p_user_name_employee_id in number default hr_api.g_number,
479 p_user_name_emp_first_name in varchar2 default hr_api.g_varchar2,
480 p_user_name_emp_last_name in varchar2 default hr_api.g_varchar2,
481 p_user_name_emp_middle_names in varchar2 default hr_api.g_varchar2,
482 p_action_taken in varchar2 default hr_api.g_varchar2,
483 p_date_notification_sent in date default hr_api.g_date,
484 p_object_version_number in out NOCOPY number,
485 p_item_key in varchar2 default hr_api.g_varchar2,
486 p_validate in boolean default false
487 ) is
488 --
489 l_rec ghr_pdh_shd.g_rec_type;
490 l_proc varchar2(72) := g_package||'upd';
491 --
492 Begin
493 hr_utility.set_location('Entering:'||l_proc, 5);
494 --
495 -- Call conversion function to turn arguments into the
496 -- l_rec structure.
497 --
498 l_rec :=
499 ghr_pdh_shd.convert_args
500 (
501 p_pd_routing_history_id,
502 hr_api.g_number,
503 p_initiator_flag,
504 p_requester_flag,
505 p_approver_flag,
506 p_reviewer_flag,
507 p_authorizer_flag,
508 p_personnelist_flag,
509 p_approved_flag,
510 p_user_name,
511 p_user_name_employee_id,
512 p_user_name_emp_first_name,
513 p_user_name_emp_last_name,
514 p_user_name_emp_middle_names,
515 p_action_taken,
516 hr_api.g_number,
517 hr_api.g_number,
518 hr_api.g_number,
519 p_date_notification_sent,
520 p_object_version_number ,
521 p_item_key
522 );
523 --
524 -- Having converted the arguments into the
525 -- plsql record structure we call the corresponding record
526 -- business process.
527 --
528 upd(l_rec,p_validate);
529
530 p_object_version_number := l_rec.object_version_number;
531 --
532 hr_utility.set_location(' Leaving:'||l_proc, 10);
533 End upd;
534 --
535 end ghr_pdh_upd;