1 Package Body psp_prt_upd as
2 /* $Header: PSPRTRHB.pls 120.1 2005/07/05 23:50 dpaudel noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' psp_prt_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 psp_prt_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 psp_prt_shd.g_api_dml := true; -- Set the api dml status
67 --
68 -- Update the psp_report_templates Row
69 --
70 update psp_report_templates
71 set
72 template_id = p_rec.template_id
73 ,template_name = p_rec.template_name
74 ,business_group_id = p_rec.business_group_id
75 ,set_of_books_id = p_rec.set_of_books_id
76 ,object_version_number = p_rec.object_version_number
77 ,report_type = p_rec.report_type
78 ,period_frequency_id = p_rec.period_frequency_id
79 ,report_template_code = p_rec.report_template_code
80 ,display_all_emp_distrib_flag = p_rec.display_all_emp_distrib_flag
81 ,manual_entry_override_flag = p_rec.manual_entry_override_flag
82 ,approval_type = p_rec.approval_type
83 ,custom_approval_code = p_rec.custom_approval_code
84 ,sup_levels = p_rec.sup_levels
85 ,preview_effort_report_flag = p_rec.preview_effort_report_flag
86 ,notification_reminder_in_days = p_rec.notification_reminder_in_days
87 ,sprcd_tolerance_amt = p_rec.sprcd_tolerance_amt
88 ,sprcd_tolerance_percent = p_rec.sprcd_tolerance_percent
89 ,description = p_rec.description
90 ,legislation_code = p_rec.legislation_code
91 ,hundred_pcent_eff_at_per_asg = p_rec.hundred_pcent_eff_at_per_asg
92 ,selection_match_level = p_rec.selection_match_level
93 where template_id = p_rec.template_id;
94 --
95 psp_prt_shd.g_api_dml := false; -- Unset the api dml status
96 --
97 hr_utility.set_location(' Leaving:'||l_proc, 10);
98 --
99 Exception
100 When hr_api.check_integrity_violated Then
101 -- A check constraint has been violated
102 psp_prt_shd.g_api_dml := false; -- Unset the api dml status
103 psp_prt_shd.constraint_error
104 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
105 When hr_api.parent_integrity_violated Then
106 -- Parent integrity has been violated
107 psp_prt_shd.g_api_dml := false; -- Unset the api dml status
108 psp_prt_shd.constraint_error
109 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
110 When hr_api.unique_integrity_violated Then
111 -- Unique integrity has been violated
112 psp_prt_shd.g_api_dml := false; -- Unset the api dml status
113 psp_prt_shd.constraint_error
114 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
115 When Others Then
116 psp_prt_shd.g_api_dml := false; -- Unset the api dml status
117 Raise;
118 End update_dml;
119 --
120 -- ----------------------------------------------------------------------------
121 -- |------------------------------< pre_update >------------------------------|
122 -- ----------------------------------------------------------------------------
123 -- {Start Of Comments}
124 --
125 -- Description:
126 -- This private procedure contains any processing which is required before
127 -- the update dml.
128 --
129 -- Prerequisites:
130 -- This is an internal procedure which is called from the upd procedure.
131 --
132 -- In Parameters:
133 -- A Pl/Sql record structure.
134 --
135 -- Post Success:
136 -- Processing continues.
137 --
138 -- Post Failure:
139 -- If an error has occurred, an error message and exception wil be raised
140 -- but not handled.
141 --
142 -- Developer Implementation Notes:
143 -- Any pre-processing required before the update dml is issued should be
144 -- coded within this procedure. It is important to note that any 3rd party
145 -- maintenance should be reviewed before placing in this procedure.
146 --
147 -- Access Status:
148 -- Internal Row Handler Use Only.
149 --
150 -- {End Of Comments}
151 -- ----------------------------------------------------------------------------
152 Procedure pre_update
153 (p_rec in psp_prt_shd.g_rec_type
154 ) is
155 --
156 l_proc varchar2(72) := g_package||'pre_update';
157 --
158 Begin
159 hr_utility.set_location('Entering:'||l_proc, 5);
160 --
161 hr_utility.set_location(' Leaving:'||l_proc, 10);
162 End pre_update;
163 --
164 -- ----------------------------------------------------------------------------
165 -- |-----------------------------< post_update >------------------------------|
166 -- ----------------------------------------------------------------------------
167 -- {Start Of Comments}
168 --
169 -- Description:
170 -- This private procedure contains any processing which is required after
171 -- the update dml.
172 --
173 -- Prerequisites:
174 -- This is an internal procedure which is called from the upd procedure.
175 --
176 -- In Parameters:
177 -- A Pl/Sql record structure.
178 --
179 -- Post Success:
180 -- Processing continues.
181 --
182 -- Post Failure:
183 -- If an error has occurred, an error message and exception will be raised
184 -- but not handled.
185 --
186 -- Developer Implementation Notes:
187 -- Any post-processing required after the update dml is issued should be
188 -- coded within this procedure. It is important to note that any 3rd party
189 -- maintenance should be reviewed before placing in this procedure.
190 --
191 -- Access Status:
192 -- Internal Row Handler Use Only.
193 --
194 -- {End Of Comments}
195 -- ----------------------------------------------------------------------------
196 Procedure post_update
197 (p_rec in psp_prt_shd.g_rec_type
198 ) is
199 --
200 l_proc varchar2(72) := g_package||'post_update';
201 --
202 Begin
203 hr_utility.set_location('Entering:'||l_proc, 5);
204 begin
205 --
206 psp_prt_rku.after_update
207 (p_template_id
208 => p_rec.template_id
209 ,p_template_name
210 => p_rec.template_name
211 ,p_business_group_id
212 => p_rec.business_group_id
213 ,p_set_of_books_id
214 => p_rec.set_of_books_id
215 ,p_object_version_number
216 => p_rec.object_version_number
217 ,p_report_type
218 => p_rec.report_type
219 ,p_period_frequency_id
220 => p_rec.period_frequency_id
221 ,p_report_template_code
222 => p_rec.report_template_code
223 ,p_display_all_emp_distrib_flag
224 => p_rec.display_all_emp_distrib_flag
225 ,p_manual_entry_override_flag
226 => p_rec.manual_entry_override_flag
227 ,p_approval_type
228 => p_rec.approval_type
229 ,p_custom_approval_code
230 => p_rec.custom_approval_code
231 ,p_sup_levels
232 => p_rec.sup_levels
233 ,p_preview_effort_report_flag
234 => p_rec.preview_effort_report_flag
235 ,p_notification_reminder_in_day
236 => p_rec.notification_reminder_in_days
237 ,p_sprcd_tolerance_amt
238 => p_rec.sprcd_tolerance_amt
239 ,p_sprcd_tolerance_percent
240 => p_rec.sprcd_tolerance_percent
241 ,p_description
242 => p_rec.description
243 ,p_legislation_code
244 => p_rec.legislation_code
245 ,p_hundred_pcent_eff_at_per_asg
246 => p_rec.hundred_pcent_eff_at_per_asg
247 ,p_selection_match_level
248 => p_rec.selection_match_level
249 ,p_template_name_o
250 => psp_prt_shd.g_old_rec.template_name
251 ,p_business_group_id_o
252 => psp_prt_shd.g_old_rec.business_group_id
253 ,p_set_of_books_id_o
254 => psp_prt_shd.g_old_rec.set_of_books_id
255 ,p_object_version_number_o
256 => psp_prt_shd.g_old_rec.object_version_number
257 ,p_report_type_o
258 => psp_prt_shd.g_old_rec.report_type
259 ,p_period_frequency_id_o
260 => psp_prt_shd.g_old_rec.period_frequency_id
261 ,p_report_template_code_o
262 => psp_prt_shd.g_old_rec.report_template_code
263 ,p_display_all_emp_distrib_fl_o
264 => psp_prt_shd.g_old_rec.display_all_emp_distrib_flag
265 ,p_manual_entry_override_flag_o
266 => psp_prt_shd.g_old_rec.manual_entry_override_flag
267 ,p_approval_type_o
268 => psp_prt_shd.g_old_rec.approval_type
269 ,p_custom_approval_code_o
270 => psp_prt_shd.g_old_rec.custom_approval_code
271 ,p_sup_levels_o
272 => psp_prt_shd.g_old_rec.sup_levels
273 ,p_preview_effort_report_flag_o
274 => psp_prt_shd.g_old_rec.preview_effort_report_flag
275 ,p_notification_reminder_in_d_o
276 => psp_prt_shd.g_old_rec.notification_reminder_in_days
277 ,p_sprcd_tolerance_amt_o
278 => psp_prt_shd.g_old_rec.sprcd_tolerance_amt
279 ,p_sprcd_tolerance_percent_o
280 => psp_prt_shd.g_old_rec.sprcd_tolerance_percent
281 ,p_description_o
282 => psp_prt_shd.g_old_rec.description
283 ,p_legislation_code_o
284 => psp_prt_shd.g_old_rec.legislation_code
285 ,p_hundred_pcent_eff_at_per_a_o
286 => psp_prt_shd.g_old_rec.hundred_pcent_eff_at_per_asg
287 ,p_selection_match_level_o
288 => psp_prt_shd.g_old_rec.selection_match_level
289 );
290 --
291 exception
292 --
293 when hr_api.cannot_find_prog_unit then
294 --
295 hr_api.cannot_find_prog_unit_error
296 (p_module_name => 'PSP_REPORT_TEMPLATES'
297 ,p_hook_type => 'AU');
298 --
299 end;
300 --
301 hr_utility.set_location(' Leaving:'||l_proc, 10);
302 End post_update;
303 --
304 -- ----------------------------------------------------------------------------
305 -- |-----------------------------< convert_defs >-----------------------------|
306 -- ----------------------------------------------------------------------------
307 -- {Start Of Comments}
308 --
309 -- Description:
310 -- The Convert_Defs procedure has one very important function:
311 -- It must return the record structure for the row with all system defaulted
312 -- values converted into its corresponding parameter value for update. When
313 -- we attempt to update a row through the Upd process , certain
314 -- parameters can be defaulted which enables flexibility in the calling of
315 -- the upd process (e.g. only attributes which need to be updated need to be
316 -- specified). For the upd process to determine which attributes
317 -- have NOT been specified we need to check if the parameter has a reserved
318 -- system default value. Therefore, for all parameters which have a
319 -- corresponding reserved system default mechanism specified we need to
320 -- check if a system default is being used. If a system default is being
321 -- used then we convert the defaulted value into its corresponding attribute
322 -- value held in the g_old_rec data structure.
323 --
324 -- Prerequisites:
325 -- This private function can only be called from the upd process.
326 --
327 -- In Parameters:
328 -- A Pl/Sql record structure.
329 --
330 -- Post Success:
331 -- The record structure will be returned with all system defaulted parameter
332 -- values converted into its current row attribute value.
333 --
334 -- Post Failure:
335 -- No direct error handling is required within this function. Any possible
336 -- errors within this procedure will be a PL/SQL value error due to
337 -- conversion of datatypes or data lengths.
338 --
339 -- Developer Implementation Notes:
340 -- None.
341 --
342 -- Access Status:
343 -- Internal Row Handler Use Only.
344 --
345 -- {End Of Comments}
346 -- ----------------------------------------------------------------------------
347 Procedure convert_defs
348 (p_rec in out nocopy psp_prt_shd.g_rec_type
349 ) is
350 --
351 Begin
352 --
353 -- We must now examine each argument value in the
354 -- p_rec plsql record structure
355 -- to see if a system default is being used. If a system default
356 -- is being used then we must set to the 'current' argument value.
357 --
358 If (p_rec.template_name = hr_api.g_varchar2) then
359 p_rec.template_name :=
360 psp_prt_shd.g_old_rec.template_name;
361 End If;
362 If (p_rec.business_group_id = hr_api.g_number) then
363 p_rec.business_group_id :=
364 psp_prt_shd.g_old_rec.business_group_id;
365 End If;
366 If (p_rec.set_of_books_id = hr_api.g_number) then
367 p_rec.set_of_books_id :=
368 psp_prt_shd.g_old_rec.set_of_books_id;
369 End If;
370 If (p_rec.report_type = hr_api.g_varchar2) then
371 p_rec.report_type :=
372 psp_prt_shd.g_old_rec.report_type;
373 End If;
374 If (p_rec.period_frequency_id = hr_api.g_number) then
375 p_rec.period_frequency_id :=
376 psp_prt_shd.g_old_rec.period_frequency_id;
377 End If;
378 If (p_rec.report_template_code = hr_api.g_varchar2) then
379 p_rec.report_template_code :=
380 psp_prt_shd.g_old_rec.report_template_code;
381 End If;
382 If (p_rec.display_all_emp_distrib_flag = hr_api.g_varchar2) then
383 p_rec.display_all_emp_distrib_flag :=
384 psp_prt_shd.g_old_rec.display_all_emp_distrib_flag;
385 End If;
386 If (p_rec.manual_entry_override_flag = hr_api.g_varchar2) then
387 p_rec.manual_entry_override_flag :=
388 psp_prt_shd.g_old_rec.manual_entry_override_flag;
389 End If;
390 If (p_rec.approval_type = hr_api.g_varchar2) then
391 p_rec.approval_type :=
392 psp_prt_shd.g_old_rec.approval_type;
393 End If;
394 If (p_rec.custom_approval_code = hr_api.g_varchar2) then
395 p_rec.custom_approval_code :=
396 psp_prt_shd.g_old_rec.custom_approval_code;
397 End If;
398 If (p_rec.sup_levels = hr_api.g_number) then
399 p_rec.sup_levels :=
400 psp_prt_shd.g_old_rec.sup_levels;
401 End If;
402 If (p_rec.preview_effort_report_flag = hr_api.g_varchar2) then
403 p_rec.preview_effort_report_flag :=
404 psp_prt_shd.g_old_rec.preview_effort_report_flag;
405 End If;
406 If (p_rec.notification_reminder_in_days = hr_api.g_number) then
407 p_rec.notification_reminder_in_days :=
408 psp_prt_shd.g_old_rec.notification_reminder_in_days;
409 End If;
410 If (p_rec.sprcd_tolerance_amt = hr_api.g_number) then
411 p_rec.sprcd_tolerance_amt :=
412 psp_prt_shd.g_old_rec.sprcd_tolerance_amt;
413 End If;
414 If (p_rec.sprcd_tolerance_percent = hr_api.g_number) then
415 p_rec.sprcd_tolerance_percent :=
416 psp_prt_shd.g_old_rec.sprcd_tolerance_percent;
417 End If;
418 If (p_rec.description = hr_api.g_varchar2) then
419 p_rec.description :=
420 psp_prt_shd.g_old_rec.description;
421 End If;
422 If (p_rec.legislation_code = hr_api.g_varchar2) then
423 p_rec.legislation_code :=
424 psp_prt_shd.g_old_rec.legislation_code;
425 End If;
426 If (p_rec.hundred_pcent_eff_at_per_asg = hr_api.g_varchar2) then
427 p_rec.hundred_pcent_eff_at_per_asg :=
428 psp_prt_shd.g_old_rec.hundred_pcent_eff_at_per_asg;
429 End If;
430 If (p_rec.selection_match_level = hr_api.g_varchar2) then
431 p_rec.selection_match_level :=
432 psp_prt_shd.g_old_rec.selection_match_level;
433 End If;
434 --
435 End convert_defs;
436 --
437 -- ----------------------------------------------------------------------------
438 -- |---------------------------------< upd >----------------------------------|
439 -- ----------------------------------------------------------------------------
440 Procedure upd
441 (p_rec in out nocopy psp_prt_shd.g_rec_type
442 ) is
443 --
444 l_proc varchar2(72) := g_package||'upd';
445 --
446 Begin
447 hr_utility.set_location('Entering:'||l_proc, 5);
448 --
449 -- We must lock the row which we need to update.
450 --
451 psp_prt_shd.lck
452 (p_rec.template_id
453 ,p_rec.object_version_number
454 );
455 --
456 -- 1. During an update system defaults are used to determine if
457 -- arguments have been defaulted or not. We must therefore
458 -- derive the full record structure values to be updated.
459 --
460 -- 2. Call the supporting update validate operations.
461 --
462 convert_defs(p_rec);
463 psp_prt_bus.update_validate
464 (p_rec
465 );
466 --
467 -- Call to raise any errors on multi-message list
468 hr_multi_message.end_validation_set;
469 --
470 -- Call the supporting pre-update operation
471 --
472 psp_prt_upd.pre_update(p_rec);
473 --
474 -- Update the row.
475 --
476 psp_prt_upd.update_dml(p_rec);
477 --
478 -- Call the supporting post-update operation
479 --
480 psp_prt_upd.post_update
481 (p_rec
482 );
483 --
484 -- Call to raise any errors on multi-message list
485 hr_multi_message.end_validation_set;
486 End upd;
487 --
488 -- ----------------------------------------------------------------------------
489 -- |---------------------------------< upd >----------------------------------|
490 -- ----------------------------------------------------------------------------
491 Procedure upd
492 (p_template_id in number
493 ,p_object_version_number in out nocopy number
494 ,p_template_name in varchar2 default hr_api.g_varchar2
495 ,p_business_group_id in number default hr_api.g_number
496 ,p_set_of_books_id in number default hr_api.g_number
497 ,p_report_type in varchar2 default hr_api.g_varchar2
498 ,p_period_frequency_id in number default hr_api.g_number
499 ,p_report_template_code in varchar2 default hr_api.g_varchar2
500 ,p_approval_type in varchar2 default hr_api.g_varchar2
501 ,p_preview_effort_report_flag in varchar2 default hr_api.g_varchar2
502 ,p_hundred_pcent_eff_at_per_asg in varchar2 default hr_api.g_varchar2
503 ,p_selection_match_level in varchar2 default hr_api.g_varchar2
504 ,p_display_all_emp_distrib_flag in varchar2 default hr_api.g_varchar2
505 ,p_manual_entry_override_flag in varchar2 default hr_api.g_varchar2
506 ,p_custom_approval_code in varchar2 default hr_api.g_varchar2
507 ,p_sup_levels in number default hr_api.g_number
508 ,p_notification_reminder_in_day in number default hr_api.g_number
509 ,p_sprcd_tolerance_amt in number default hr_api.g_number
510 ,p_sprcd_tolerance_percent in number default hr_api.g_number
511 ,p_description in varchar2 default hr_api.g_varchar2
512 ,p_legislation_code in varchar2 default hr_api.g_varchar2
513 ) is
514 --
515 l_rec psp_prt_shd.g_rec_type;
516 l_proc varchar2(72) := g_package||'upd';
517 --
518 Begin
519 hr_utility.set_location('Entering:'||l_proc, 5);
520 --
521 -- Call conversion function to turn arguments into the
522 -- l_rec structure.
523 --
524 l_rec :=
525 psp_prt_shd.convert_args
526 (p_template_id
527 ,p_template_name
528 ,p_business_group_id
529 ,p_set_of_books_id
530 ,p_object_version_number
531 ,p_report_type
532 ,p_period_frequency_id
533 ,p_report_template_code
534 ,p_display_all_emp_distrib_flag
535 ,p_manual_entry_override_flag
536 ,p_approval_type
537 ,p_custom_approval_code
538 ,p_sup_levels
539 ,p_preview_effort_report_flag
540 ,p_notification_reminder_in_day
541 ,p_sprcd_tolerance_amt
542 ,p_sprcd_tolerance_percent
543 ,p_description
544 ,p_legislation_code
545 ,p_hundred_pcent_eff_at_per_asg
546 ,p_selection_match_level
547 );
548 --
549 -- Having converted the arguments into the
550 -- plsql record structure we call the corresponding record
551 -- business process.
552 --
553 psp_prt_upd.upd
554 (l_rec
555 );
556 p_object_version_number := l_rec.object_version_number;
557 --
558 hr_utility.set_location(' Leaving:'||l_proc, 10);
559 End upd;
560 --
561 end psp_prt_upd;
[Home] [Help]