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