1 Package Body per_pdm_upd as
2 /* $Header: pepdmrhi.pkb 115.8 2002/12/09 14:33:06 pkakar ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_pdm_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 per_pdm_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 -- Update the per_person_dlvry_methods Row
60 --
61 update per_person_dlvry_methods
62 set
63 delivery_method_id = p_rec.delivery_method_id,
64 date_start = p_rec.date_start,
65 date_end = p_rec.date_end,
66 comm_dlvry_method = p_rec.comm_dlvry_method,
67 preferred_flag = p_rec.preferred_flag,
68 object_version_number = p_rec.object_version_number,
69 request_id = p_rec.request_id,
70 program_update_date = p_rec.program_update_date,
71 program_application_id = p_rec.program_application_id,
72 program_id = p_rec.program_id,
73 attribute_category = p_rec.attribute_category,
74 attribute1 = p_rec.attribute1,
75 attribute2 = p_rec.attribute2,
76 attribute3 = p_rec.attribute3,
77 attribute4 = p_rec.attribute4,
78 attribute5 = p_rec.attribute5,
79 attribute6 = p_rec.attribute6,
80 attribute7 = p_rec.attribute7,
81 attribute8 = p_rec.attribute8,
82 attribute9 = p_rec.attribute9,
83 attribute10 = p_rec.attribute10,
84 attribute11 = p_rec.attribute11,
85 attribute12 = p_rec.attribute12,
86 attribute13 = p_rec.attribute13,
87 attribute14 = p_rec.attribute14,
88 attribute15 = p_rec.attribute15,
89 attribute16 = p_rec.attribute16,
90 attribute17 = p_rec.attribute17,
91 attribute18 = p_rec.attribute18,
92 attribute19 = p_rec.attribute19,
93 attribute20 = p_rec.attribute20
94 where delivery_method_id = p_rec.delivery_method_id;
95 --
96 hr_utility.set_location(' Leaving:'||l_proc, 10);
97 --
98 Exception
99 When hr_api.check_integrity_violated Then
100 -- A check constraint has been violated
101 per_pdm_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 per_pdm_shd.constraint_error
106 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
107 When hr_api.unique_integrity_violated Then
108 -- Unique integrity has been violated
109 per_pdm_shd.constraint_error
110 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
111 When Others Then
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 structre.
129 --
130 -- Post Success:
131 -- Processing continues.
132 --
133 -- Post Failure:
134 -- If an error has occurred, an error message and exception will 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(p_rec in per_pdm_shd.g_rec_type) is
148 --
149 l_proc varchar2(72) := g_package||'pre_update';
150 --
151 Begin
152 hr_utility.set_location('Entering:'||l_proc, 5);
153 --
154 hr_utility.set_location(' Leaving:'||l_proc, 10);
155 End pre_update;
156 --
157 -- ----------------------------------------------------------------------------
158 -- |-----------------------------< post_update >------------------------------|
159 -- ----------------------------------------------------------------------------
160 -- {Start Of Comments}
161 --
162 -- Description:
163 -- This private procedure contains any processing which is required after the
164 -- update dml.
165 --
166 -- Prerequisites:
167 -- This is an internal procedure which is called from the upd procedure.
168 --
169 -- In Parameters:
170 -- A Pl/Sql record structre.
171 --
172 -- Post Success:
173 -- Processing continues.
174 --
175 -- Post Failure:
176 -- If an error has occurred, an error message and exception will be raised
177 -- but not handled.
178 --
179 -- Developer Implementation Notes:
180 -- Any post-processing required after the update dml is issued should be
181 -- coded within this procedure. It is important to note that any 3rd party
182 -- maintenance should be reviewed before placing in this procedure.
183 --
184 -- Access Status:
185 -- Internal Row Handler Use Only.
186 --
187 -- {End Of Comments}
188 -- ----------------------------------------------------------------------------
189 Procedure post_update(
190 p_effective_date in date,p_rec in per_pdm_shd.g_rec_type) is
191 --
192 l_proc varchar2(72) := g_package||'post_update';
193 --
194 Begin
195 hr_utility.set_location('Entering:'||l_proc, 5);
196 --
197 --
198 -- Start of API User Hook for post_update.
199 --
200 begin
201 --
202 per_pdm_rku.after_update
203 (
204 p_delivery_method_id =>p_rec.delivery_method_id
205 ,p_date_start =>p_rec.date_start
206 ,p_date_end =>p_rec.date_end
207 ,p_comm_dlvry_method =>p_rec.comm_dlvry_method
208 ,p_preferred_flag =>p_rec.preferred_flag
209 ,p_object_version_number =>p_rec.object_version_number
210 ,p_request_id =>p_rec.request_id
211 ,p_program_update_date =>p_rec.program_update_date
212 ,p_program_application_id =>p_rec.program_application_id
213 ,p_program_id =>p_rec.program_id
214 ,p_attribute_category =>p_rec.attribute_category
215 ,p_attribute1 =>p_rec.attribute1
216 ,p_attribute2 =>p_rec.attribute2
217 ,p_attribute3 =>p_rec.attribute3
218 ,p_attribute4 =>p_rec.attribute4
219 ,p_attribute5 =>p_rec.attribute5
220 ,p_attribute6 =>p_rec.attribute6
221 ,p_attribute7 =>p_rec.attribute7
222 ,p_attribute8 =>p_rec.attribute8
223 ,p_attribute9 =>p_rec.attribute9
224 ,p_attribute10 =>p_rec.attribute10
225 ,p_attribute11 =>p_rec.attribute11
226 ,p_attribute12 =>p_rec.attribute12
227 ,p_attribute13 =>p_rec.attribute13
228 ,p_attribute14 =>p_rec.attribute14
229 ,p_attribute15 =>p_rec.attribute15
230 ,p_attribute16 =>p_rec.attribute16
231 ,p_attribute17 =>p_rec.attribute17
232 ,p_attribute18 =>p_rec.attribute18
233 ,p_attribute19 =>p_rec.attribute19
234 ,p_attribute20 =>p_rec.attribute20
235 ,p_effective_date =>p_effective_date
236 ,p_person_id_o =>per_pdm_shd.g_old_rec.person_id
237 ,p_date_start_o =>per_pdm_shd.g_old_rec.date_start
238 ,p_date_end_o =>per_pdm_shd.g_old_rec.date_end
239 ,p_comm_dlvry_method_o =>per_pdm_shd.g_old_rec.comm_dlvry_method
240 ,p_preferred_flag_o =>per_pdm_shd.g_old_rec.preferred_flag
241 ,p_object_version_number_o =>per_pdm_shd.g_old_rec.object_version_number
242 ,p_request_id_o =>per_pdm_shd.g_old_rec.request_id
243 ,p_program_update_date_o =>per_pdm_shd.g_old_rec.program_update_date
244 ,p_program_application_id_o =>per_pdm_shd.g_old_rec.program_application_id
245 ,p_program_id_o =>per_pdm_shd.g_old_rec.program_id
246 ,p_attribute_category_o =>per_pdm_shd.g_old_rec.attribute_category
247 ,p_attribute1_o =>per_pdm_shd.g_old_rec.attribute1
248 ,p_attribute2_o =>per_pdm_shd.g_old_rec.attribute2
249 ,p_attribute3_o =>per_pdm_shd.g_old_rec.attribute3
250 ,p_attribute4_o =>per_pdm_shd.g_old_rec.attribute4
251 ,p_attribute5_o =>per_pdm_shd.g_old_rec.attribute5
252 ,p_attribute6_o =>per_pdm_shd.g_old_rec.attribute6
253 ,p_attribute7_o =>per_pdm_shd.g_old_rec.attribute7
254 ,p_attribute8_o =>per_pdm_shd.g_old_rec.attribute8
255 ,p_attribute9_o =>per_pdm_shd.g_old_rec.attribute9
256 ,p_attribute10_o =>per_pdm_shd.g_old_rec.attribute10
257 ,p_attribute11_o =>per_pdm_shd.g_old_rec.attribute11
258 ,p_attribute12_o =>per_pdm_shd.g_old_rec.attribute12
259 ,p_attribute13_o =>per_pdm_shd.g_old_rec.attribute13
260 ,p_attribute14_o =>per_pdm_shd.g_old_rec.attribute14
261 ,p_attribute15_o =>per_pdm_shd.g_old_rec.attribute15
262 ,p_attribute16_o =>per_pdm_shd.g_old_rec.attribute16
263 ,p_attribute17_o =>per_pdm_shd.g_old_rec.attribute17
264 ,p_attribute18_o =>per_pdm_shd.g_old_rec.attribute18
265 ,p_attribute19_o =>per_pdm_shd.g_old_rec.attribute19
266 ,p_attribute20_o =>per_pdm_shd.g_old_rec.attribute20
267 );
268 --
269 exception
270 --
271 when hr_api.cannot_find_prog_unit then
272 --
273 hr_api.cannot_find_prog_unit_error
274 (p_module_name => 'PER_PERSON_DLVRY_METHODS'
275 ,p_hook_type => 'AU');
276 --
277 end;
278 --
279 -- End of API User Hook for post_update.
280 --
281 --
282 hr_utility.set_location(' Leaving:'||l_proc, 10);
283 End post_update;
284 --
285 -- ----------------------------------------------------------------------------
286 -- |-----------------------------< convert_defs >-----------------------------|
287 -- ----------------------------------------------------------------------------
288 -- {Start Of Comments}
289 --
290 -- Description:
291 -- The Convert_Defs procedure has one very important function:
292 -- It must return the record structure for the row with all system defaulted
293 -- values converted into its corresponding parameter value for update. When
294 -- we attempt to update a row through the Upd process , certain
295 -- parameters can be defaulted which enables flexibility in the calling of
296 -- the upd process (e.g. only attributes which need to be updated need to be
297 -- specified). For the upd process to determine which attributes
298 -- have NOT been specified we need to check if the parameter has a reserved
299 -- system default value. Therefore, for all parameters which have a
300 -- corresponding reserved system default mechanism specified we need to
301 -- check if a system default is being used. If a system default is being
302 -- used then we convert the defaulted value into its corresponding attribute
303 -- value held in the g_old_rec data structure.
304 --
305 -- Prerequisites:
306 -- This private function can only be called from the upd process.
307 --
308 -- In Parameters:
309 -- A Pl/Sql record structre.
310 --
311 -- Post Success:
312 -- The record structure will be returned with all system defaulted parameter
313 -- values converted into its current row attribute value.
314 --
315 -- Post Failure:
316 -- No direct error handling is required within this function. Any possible
317 -- errors within this procedure will be a PL/SQL value error due to conversion
318 -- of datatypes or data lengths.
319 --
320 -- Developer Implementation Notes:
321 -- None.
322 --
323 -- Access Status:
324 -- Internal Row Handler Use Only.
325 --
326 -- {End Of Comments}
327 -- ----------------------------------------------------------------------------
328 Procedure convert_defs(p_rec in out nocopy per_pdm_shd.g_rec_type) is
329 --
330 l_proc varchar2(72) := g_package||'convert_defs';
331 --
332 Begin
333 --
334 hr_utility.set_location('Entering:'||l_proc, 5);
335 --
336 -- We must now examine each argument value in the
337 -- p_rec plsql record structure
338 -- to see if a system default is being used. If a system default
339 -- is being used then we must set to the 'current' argument value.
340 --
341 If (p_rec.person_id = hr_api.g_number) then
342 p_rec.person_id :=
343 per_pdm_shd.g_old_rec.person_id;
344 End If;
345 If (p_rec.date_start = hr_api.g_date) then
346 p_rec.date_start :=
347 per_pdm_shd.g_old_rec.date_start;
348 End If;
349 If (p_rec.date_end = hr_api.g_date) then
350 p_rec.date_end :=
351 per_pdm_shd.g_old_rec.date_end;
352 End If;
353 If (p_rec.comm_dlvry_method = hr_api.g_varchar2) then
354 p_rec.comm_dlvry_method :=
355 per_pdm_shd.g_old_rec.comm_dlvry_method;
356 End If;
357 If (p_rec.preferred_flag = hr_api.g_varchar2) then
358 p_rec.preferred_flag :=
359 per_pdm_shd.g_old_rec.preferred_flag;
360 End If;
361 If (p_rec.request_id = hr_api.g_number) then
362 p_rec.request_id :=
363 per_pdm_shd.g_old_rec.request_id;
364 End If;
365 If (p_rec.program_update_date = hr_api.g_date) then
366 p_rec.program_update_date :=
367 per_pdm_shd.g_old_rec.program_update_date;
368 End If;
369 If (p_rec.program_application_id = hr_api.g_number) then
370 p_rec.program_application_id :=
371 per_pdm_shd.g_old_rec.program_application_id;
372 End If;
373 If (p_rec.program_id = hr_api.g_number) then
374 p_rec.program_id :=
375 per_pdm_shd.g_old_rec.program_id;
376 End If;
377 If (p_rec.attribute_category = hr_api.g_varchar2) then
378 p_rec.attribute_category :=
379 per_pdm_shd.g_old_rec.attribute_category;
380 End If;
381 If (p_rec.attribute1 = hr_api.g_varchar2) then
382 p_rec.attribute1 :=
383 per_pdm_shd.g_old_rec.attribute1;
384 End If;
385 If (p_rec.attribute2 = hr_api.g_varchar2) then
386 p_rec.attribute2 :=
387 per_pdm_shd.g_old_rec.attribute2;
388 End If;
389 If (p_rec.attribute3 = hr_api.g_varchar2) then
390 p_rec.attribute3 :=
391 per_pdm_shd.g_old_rec.attribute3;
392 End If;
393 If (p_rec.attribute4 = hr_api.g_varchar2) then
394 p_rec.attribute4 :=
395 per_pdm_shd.g_old_rec.attribute4;
396 End If;
397 If (p_rec.attribute5 = hr_api.g_varchar2) then
398 p_rec.attribute5 :=
399 per_pdm_shd.g_old_rec.attribute5;
400 End If;
401 If (p_rec.attribute6 = hr_api.g_varchar2) then
402 p_rec.attribute6 :=
403 per_pdm_shd.g_old_rec.attribute6;
404 End If;
405 If (p_rec.attribute7 = hr_api.g_varchar2) then
406 p_rec.attribute7 :=
407 per_pdm_shd.g_old_rec.attribute7;
408 End If;
409 If (p_rec.attribute8 = hr_api.g_varchar2) then
410 p_rec.attribute8 :=
411 per_pdm_shd.g_old_rec.attribute8;
412 End If;
413 If (p_rec.attribute9 = hr_api.g_varchar2) then
414 p_rec.attribute9 :=
415 per_pdm_shd.g_old_rec.attribute9;
416 End If;
417 If (p_rec.attribute10 = hr_api.g_varchar2) then
418 p_rec.attribute10 :=
419 per_pdm_shd.g_old_rec.attribute10;
420 End If;
421 If (p_rec.attribute11 = hr_api.g_varchar2) then
422 p_rec.attribute11 :=
423 per_pdm_shd.g_old_rec.attribute11;
424 End If;
425 If (p_rec.attribute12 = hr_api.g_varchar2) then
426 p_rec.attribute12 :=
427 per_pdm_shd.g_old_rec.attribute12;
428 End If;
429 If (p_rec.attribute13 = hr_api.g_varchar2) then
430 p_rec.attribute13 :=
431 per_pdm_shd.g_old_rec.attribute13;
432 End If;
433 If (p_rec.attribute14 = hr_api.g_varchar2) then
434 p_rec.attribute14 :=
435 per_pdm_shd.g_old_rec.attribute14;
436 End If;
437 If (p_rec.attribute15 = hr_api.g_varchar2) then
438 p_rec.attribute15 :=
439 per_pdm_shd.g_old_rec.attribute15;
440 End If;
441 If (p_rec.attribute16 = hr_api.g_varchar2) then
442 p_rec.attribute16 :=
443 per_pdm_shd.g_old_rec.attribute16;
444 End If;
445 If (p_rec.attribute17 = hr_api.g_varchar2) then
446 p_rec.attribute17 :=
447 per_pdm_shd.g_old_rec.attribute17;
448 End If;
449 If (p_rec.attribute18 = hr_api.g_varchar2) then
450 p_rec.attribute18 :=
451 per_pdm_shd.g_old_rec.attribute18;
452 End If;
453 If (p_rec.attribute19 = hr_api.g_varchar2) then
454 p_rec.attribute19 :=
455 per_pdm_shd.g_old_rec.attribute19;
456 End If;
457 If (p_rec.attribute20 = hr_api.g_varchar2) then
458 p_rec.attribute20 :=
459 per_pdm_shd.g_old_rec.attribute20;
460 End If;
461 --
462 hr_utility.set_location(' Leaving:'||l_proc, 10);
463 --
464 End convert_defs;
465 --
466 -- ----------------------------------------------------------------------------
467 -- |---------------------------------< upd >----------------------------------|
468 -- ----------------------------------------------------------------------------
469 Procedure upd
470 (
471 p_effective_date in date,
472 p_rec in out nocopy per_pdm_shd.g_rec_type
473 ) is
474 --
475 l_proc varchar2(72) := g_package||'upd';
476 --
477 Begin
478 hr_utility.set_location('Entering:'||l_proc, 5);
479 --
480 -- We must lock the row which we need to update.
481 --
482 per_pdm_shd.lck
483 (
484 p_rec.delivery_method_id,
485 p_rec.object_version_number
486 );
487 --
488 -- 1. During an update system defaults are used to determine if
489 -- arguments have been defaulted or not. We must therefore
490 -- derive the full record structure values to be updated.
491 --
492 -- 2. Call the supporting update validate operations.
493 --
494 convert_defs(p_rec);
495 per_pdm_bus.update_validate(p_rec, p_effective_date);
496 --
497 -- Call the supporting pre-update operation
498 --
499 pre_update(p_rec);
500 --
501 -- Update the row.
502 --
503 update_dml(p_rec);
504 --
505 -- Call the supporting post-update operation
506 --
507 post_update(
508 p_effective_date,p_rec);
509 End upd;
510 --
511 -- ----------------------------------------------------------------------------
512 -- |---------------------------------< upd >----------------------------------|
513 -- ----------------------------------------------------------------------------
514 Procedure upd
515 (
516 p_effective_date in date,
517 p_delivery_method_id in number,
518 p_date_start in date default hr_api.g_date,
519 p_date_end in date default hr_api.g_date,
520 p_comm_dlvry_method in varchar2 default hr_api.g_varchar2,
521 p_preferred_flag in varchar2 default hr_api.g_varchar2,
522 p_object_version_number in out nocopy number,
523 p_request_id in number default hr_api.g_number,
524 p_program_update_date in date default hr_api.g_date,
525 p_program_application_id in number default hr_api.g_number,
526 p_program_id in number default hr_api.g_number,
527 p_attribute_category in varchar2 default hr_api.g_varchar2,
528 p_attribute1 in varchar2 default hr_api.g_varchar2,
529 p_attribute2 in varchar2 default hr_api.g_varchar2,
530 p_attribute3 in varchar2 default hr_api.g_varchar2,
531 p_attribute4 in varchar2 default hr_api.g_varchar2,
532 p_attribute5 in varchar2 default hr_api.g_varchar2,
533 p_attribute6 in varchar2 default hr_api.g_varchar2,
534 p_attribute7 in varchar2 default hr_api.g_varchar2,
535 p_attribute8 in varchar2 default hr_api.g_varchar2,
536 p_attribute9 in varchar2 default hr_api.g_varchar2,
537 p_attribute10 in varchar2 default hr_api.g_varchar2,
538 p_attribute11 in varchar2 default hr_api.g_varchar2,
539 p_attribute12 in varchar2 default hr_api.g_varchar2,
540 p_attribute13 in varchar2 default hr_api.g_varchar2,
541 p_attribute14 in varchar2 default hr_api.g_varchar2,
542 p_attribute15 in varchar2 default hr_api.g_varchar2,
543 p_attribute16 in varchar2 default hr_api.g_varchar2,
544 p_attribute17 in varchar2 default hr_api.g_varchar2,
545 p_attribute18 in varchar2 default hr_api.g_varchar2,
546 p_attribute19 in varchar2 default hr_api.g_varchar2,
547 p_attribute20 in varchar2 default hr_api.g_varchar2
548 ) is
549 --
550 l_rec per_pdm_shd.g_rec_type;
551 l_proc varchar2(72) := g_package||'upd';
552 --
553 Begin
554 hr_utility.set_location('Entering:'||l_proc, 5);
555 --
556 -- Call conversion function to turn arguments into the
557 -- l_rec structure.
558 --
559 l_rec :=
560 per_pdm_shd.convert_args
561 (
562 p_delivery_method_id,
563 p_date_start,
564 p_date_end,
565 hr_api.g_number,
566 p_comm_dlvry_method,
567 p_preferred_flag,
568 p_object_version_number,
569 p_request_id,
570 p_program_update_date,
571 p_program_application_id,
572 p_program_id,
573 p_attribute_category,
574 p_attribute1,
575 p_attribute2,
576 p_attribute3,
577 p_attribute4,
578 p_attribute5,
579 p_attribute6,
580 p_attribute7,
581 p_attribute8,
582 p_attribute9,
583 p_attribute10,
584 p_attribute11,
585 p_attribute12,
586 p_attribute13,
587 p_attribute14,
588 p_attribute15,
589 p_attribute16,
590 p_attribute17,
591 p_attribute18,
592 p_attribute19,
593 p_attribute20
594 );
595 --
596 -- Having converted the arguments into the
597 -- plsql record structure we call the corresponding record
598 -- business process.
599 --
600 upd( p_effective_date,l_rec);
601 p_object_version_number := l_rec.object_version_number;
602 --
603 hr_utility.set_location(' Leaving:'||l_proc, 10);
604 End upd;
605 --
606 end per_pdm_upd;
[Home] [Help]