1 Package Body per_ssd_upd as
2 /* $Header: pessdrhi.pkb 120.2 2011/04/21 11:28:10 sgnanama noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_ssd_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 per_ssd_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 per_ssd_shd.g_api_dml := true; -- Set the api dml status
67 --
68 -- Update the per_sp_successee_details Row
69 --
70 update per_sp_successee_details
71 set
72 successee_detail_id = p_rec.successee_detail_id
73 ,business_group_id = p_rec.business_group_id
74 ,successee_type = p_rec.successee_type
75 ,successee_id = p_rec.successee_id
76 ,display_permissions = p_rec.display_permissions
77 ,max_plans = p_rec.max_plans
78 ,allow_npws = p_rec.allow_npws
79 ,allow_applicants = p_rec.allow_applicants
80 ,allow_override_search = p_rec.allow_override_search
81 ,key_entity = p_rec.key_entity
82 ,risk_of_loss = p_rec.risk_of_loss
83 ,impact_of_loss = p_rec.impact_of_loss
84 ,plan_readiness_rule = p_rec.plan_readiness_rule
85 ,max_successors = p_rec.max_successors
86 ,object_version_number = p_rec.object_version_number
87 where successee_detail_id = p_rec.successee_detail_id;
88 --
89 per_ssd_shd.g_api_dml := false; -- Unset the api dml status
90 --
91 hr_utility.set_location(' Leaving:'||l_proc, 10);
92 --
93 Exception
94 When hr_api.check_integrity_violated Then
95 -- A check constraint has been violated
96 per_ssd_shd.g_api_dml := false; -- Unset the api dml status
97 per_ssd_shd.constraint_error
98 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
99 When hr_api.parent_integrity_violated Then
100 -- Parent integrity has been violated
101 per_ssd_shd.g_api_dml := false; -- Unset the api dml status
102 per_ssd_shd.constraint_error
103 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
104 When hr_api.unique_integrity_violated Then
105 -- Unique integrity has been violated
106 per_ssd_shd.g_api_dml := false; -- Unset the api dml status
107 per_ssd_shd.constraint_error
108 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
109 When Others Then
110 per_ssd_shd.g_api_dml := false; -- Unset the api dml status
111 Raise;
112 End update_dml;
113 --
114 -- ----------------------------------------------------------------------------
115 -- |------------------------------< pre_update >------------------------------|
116 -- ----------------------------------------------------------------------------
117 -- {Start Of Comments}
118 --
119 -- Description:
120 -- This private procedure contains any processing which is required before
121 -- the update dml.
122 --
123 -- Prerequisites:
124 -- This is an internal procedure which is called from the upd procedure.
125 --
126 -- In Parameters:
127 -- A Pl/Sql record structure.
128 --
129 -- Post Success:
130 -- Processing continues.
131 --
132 -- Post Failure:
133 -- If an error has occurred, an error message and exception wil be raised
134 -- but not handled.
135 --
136 -- Developer Implementation Notes:
137 -- Any pre-processing required before the update dml is issued should be
138 -- coded within this procedure. It is important to note that any 3rd party
139 -- maintenance should be reviewed before placing in this procedure.
140 --
141 -- Access Status:
142 -- Internal Row Handler Use Only.
143 --
144 -- {End Of Comments}
145 -- ----------------------------------------------------------------------------
146 Procedure pre_update
147 (p_rec in per_ssd_shd.g_rec_type
148 ) is
149 --
150 l_proc varchar2(72) := g_package||'pre_update';
151 --
152 Begin
153 hr_utility.set_location('Entering:'||l_proc, 5);
154 --
155 hr_utility.set_location(' Leaving:'||l_proc, 10);
156 End pre_update;
157 --
158 -- ----------------------------------------------------------------------------
159 -- |-----------------------------< post_update >------------------------------|
160 -- ----------------------------------------------------------------------------
161 -- {Start Of Comments}
162 --
163 -- Description:
164 -- This private procedure contains any processing which is required after
165 -- the update dml.
166 --
167 -- Prerequisites:
168 -- This is an internal procedure which is called from the upd procedure.
169 --
170 -- In Parameters:
171 -- A Pl/Sql record structure.
172 --
173 -- Post Success:
174 -- Processing continues.
175 --
176 -- Post Failure:
177 -- If an error has occurred, an error message and exception will be raised
178 -- but not handled.
179 --
180 -- Developer Implementation Notes:
181 -- Any post-processing required after the update dml is issued should be
182 -- coded within this procedure. It is important to note that any 3rd party
183 -- maintenance should be reviewed before placing in this procedure.
184 --
185 -- Access Status:
186 -- Internal Row Handler Use Only.
187 --
188 -- {End Of Comments}
189 -- ----------------------------------------------------------------------------
190 Procedure post_update
191 (p_effective_date in date
192 ,p_rec in per_ssd_shd.g_rec_type
193 ) is
194 --
195 l_proc varchar2(72) := g_package||'post_update';
196 --
197 Begin
198 hr_utility.set_location('Entering:'||l_proc, 5);
199 begin
200 --
201 per_ssd_rku.after_update
202 (p_effective_date => p_effective_date
203 ,p_successee_detail_id
204 => p_rec.successee_detail_id
205 ,p_business_group_id
206 => p_rec.business_group_id
207 ,p_successee_type
208 => p_rec.successee_type
209 ,p_successee_id
210 => p_rec.successee_id
211 ,p_display_permissions
212 => p_rec.display_permissions
213 ,p_max_plans
214 => p_rec.max_plans
215 ,p_allow_npws
216 => p_rec.allow_npws
217 ,p_allow_applicants
218 => p_rec.allow_applicants
219 ,p_allow_override_search
220 => p_rec.allow_override_search
221 ,p_key_entity
222 => p_rec.key_entity
223 ,p_risk_of_loss
224 => p_rec.risk_of_loss
225 ,p_impact_of_loss
226 => p_rec.impact_of_loss
227 ,p_plan_readiness_rule
228 => p_rec.plan_readiness_rule
229 ,p_max_successors
233 ,p_business_group_id_o
230 => p_rec.max_successors
231 ,p_object_version_number
232 => p_rec.object_version_number
234 => per_ssd_shd.g_old_rec.business_group_id
235 ,p_successee_type_o
236 => per_ssd_shd.g_old_rec.successee_type
237 ,p_successee_id_o
238 => per_ssd_shd.g_old_rec.successee_id
239 ,p_display_permissions_o
240 => per_ssd_shd.g_old_rec.display_permissions
241 ,p_max_plans_o
242 => per_ssd_shd.g_old_rec.max_plans
243 ,p_allow_npws_o
244 => per_ssd_shd.g_old_rec.allow_npws
245 ,p_allow_applicants_o
246 => per_ssd_shd.g_old_rec.allow_applicants
247 ,p_allow_override_search_o
248 => per_ssd_shd.g_old_rec.allow_override_search
249 ,p_key_entity_o
250 => per_ssd_shd.g_old_rec.key_entity
251 ,p_risk_of_loss_o
252 => per_ssd_shd.g_old_rec.risk_of_loss
253 ,p_impact_of_loss_o
254 => per_ssd_shd.g_old_rec.impact_of_loss
255 ,p_plan_readiness_rule_o
256 => per_ssd_shd.g_old_rec.plan_readiness_rule
257 ,p_max_successors_o
258 => per_ssd_shd.g_old_rec.max_successors
259 ,p_object_version_number_o
260 => per_ssd_shd.g_old_rec.object_version_number
261 );
262 --
263 exception
264 --
265 when hr_api.cannot_find_prog_unit then
266 --
267 hr_api.cannot_find_prog_unit_error
268 (p_module_name => 'PER_SP_SUCCESSEE_DETAILS'
269 ,p_hook_type => 'AU');
270 --
271 end;
272 --
273 hr_utility.set_location(' Leaving:'||l_proc, 10);
274 End post_update;
275 --
276 -- ----------------------------------------------------------------------------
277 -- |-----------------------------< convert_defs >-----------------------------|
278 -- ----------------------------------------------------------------------------
279 -- {Start Of Comments}
280 --
281 -- Description:
282 -- The Convert_Defs procedure has one very important function:
283 -- It must return the record structure for the row with all system defaulted
284 -- values converted into its corresponding parameter value for update. When
285 -- we attempt to update a row through the Upd process , certain
286 -- parameters can be defaulted which enables flexibility in the calling of
287 -- the upd process (e.g. only attributes which need to be updated need to be
288 -- specified). For the upd process to determine which attributes
289 -- have NOT been specified we need to check if the parameter has a reserved
290 -- system default value. Therefore, for all parameters which have a
291 -- corresponding reserved system default mechanism specified we need to
292 -- check if a system default is being used. If a system default is being
293 -- used then we convert the defaulted value into its corresponding attribute
294 -- value held in the g_old_rec data structure.
295 --
296 -- Prerequisites:
297 -- This private function can only be called from the upd process.
298 --
299 -- In Parameters:
300 -- A Pl/Sql record structure.
301 --
302 -- Post Success:
303 -- The record structure will be returned with all system defaulted parameter
304 -- values converted into its current row attribute value.
305 --
306 -- Post Failure:
307 -- No direct error handling is required within this function. Any possible
308 -- errors within this procedure will be a PL/SQL value error due to
309 -- conversion of datatypes or data lengths.
310 --
311 -- Developer Implementation Notes:
312 -- None.
313 --
314 -- Access Status:
315 -- Internal Row Handler Use Only.
316 --
317 -- {End Of Comments}
318 -- ----------------------------------------------------------------------------
319 Procedure convert_defs
320 (p_rec in out nocopy per_ssd_shd.g_rec_type
321 ) is
322 --
323 Begin
324 --
325 -- We must now examine each argument value in the
326 -- p_rec plsql record structure
327 -- to see if a system default is being used. If a system default
328 -- is being used then we must set to the 'current' argument value.
329 --
330 If (p_rec.business_group_id = hr_api.g_number) then
331 p_rec.business_group_id :=
332 per_ssd_shd.g_old_rec.business_group_id;
333 End If;
334 If (p_rec.successee_type = hr_api.g_varchar2) then
335 p_rec.successee_type :=
336 per_ssd_shd.g_old_rec.successee_type;
337 End If;
338 If (p_rec.successee_id = hr_api.g_number) then
339 p_rec.successee_id :=
340 per_ssd_shd.g_old_rec.successee_id;
341 End If;
342 If (p_rec.display_permissions = hr_api.g_varchar2) then
343 p_rec.display_permissions :=
344 per_ssd_shd.g_old_rec.display_permissions;
345 End If;
346 If (p_rec.max_plans = hr_api.g_number) then
347 p_rec.max_plans :=
348 per_ssd_shd.g_old_rec.max_plans;
349 End If;
350 If (p_rec.allow_npws = hr_api.g_varchar2) then
351 p_rec.allow_npws :=
352 per_ssd_shd.g_old_rec.allow_npws;
353 End If;
354 If (p_rec.allow_applicants = hr_api.g_varchar2) then
355 p_rec.allow_applicants :=
356 per_ssd_shd.g_old_rec.allow_applicants;
357 End If;
358 If (p_rec.allow_override_search = hr_api.g_varchar2) then
359 p_rec.allow_override_search :=
360 per_ssd_shd.g_old_rec.allow_override_search;
361 End If;
362 If (p_rec.key_entity = hr_api.g_varchar2) then
363 p_rec.key_entity :=
364 per_ssd_shd.g_old_rec.key_entity;
365 End If;
366 If (p_rec.risk_of_loss = hr_api.g_varchar2) then
367 p_rec.risk_of_loss :=
368 per_ssd_shd.g_old_rec.risk_of_loss;
369 End If;
370 If (p_rec.impact_of_loss = hr_api.g_varchar2) then
371 p_rec.impact_of_loss :=
372 per_ssd_shd.g_old_rec.impact_of_loss;
373 End If;
374 If (p_rec.plan_readiness_rule = hr_api.g_varchar2) then
375 p_rec.plan_readiness_rule :=
376 per_ssd_shd.g_old_rec.plan_readiness_rule;
377 End If;
378 If (p_rec.max_successors = hr_api.g_number) then
379 p_rec.max_successors :=
380 per_ssd_shd.g_old_rec.max_successors;
381 End If;
382 --
383 End convert_defs;
384 --
385 -- ----------------------------------------------------------------------------
386 -- |---------------------------------< upd >----------------------------------|
387 -- ----------------------------------------------------------------------------
388 Procedure upd
389 (p_effective_date in date
390 ,p_rec in out nocopy per_ssd_shd.g_rec_type
391 ) is
392 --
393 l_proc varchar2(72) := g_package||'upd';
394 --
395 Begin
396 hr_utility.set_location('Entering:'||l_proc, 5);
397 --
398 -- We must lock the row which we need to update.
399 --
400 per_ssd_shd.lck
401 (p_rec.successee_detail_id
402 ,p_rec.object_version_number
403 );
404 --
405 -- 1. During an update system defaults are used to determine if
406 -- arguments have been defaulted or not. We must therefore
407 -- derive the full record structure values to be updated.
408 --
409 -- 2. Call the supporting update validate operations.
410 --
411 convert_defs(p_rec);
412 per_ssd_bus.update_validate
413 (p_effective_date
414 ,p_rec
415 );
416 --
417 -- Call to raise any errors on multi-message list
418 hr_multi_message.end_validation_set;
419 --
420 -- Call the supporting pre-update operation
421 --
422 per_ssd_upd.pre_update(p_rec);
423 --
424 -- Update the row.
425 --
426 per_ssd_upd.update_dml(p_rec);
427 --
428 -- Call the supporting post-update operation
429 --
430 per_ssd_upd.post_update
431 (p_effective_date
432 ,p_rec
433 );
434 --
435 -- Call to raise any errors on multi-message list
436 hr_multi_message.end_validation_set;
437 End upd;
438 --
439 -- ----------------------------------------------------------------------------
440 -- |---------------------------------< upd >----------------------------------|
441 -- ----------------------------------------------------------------------------
442 Procedure upd
443 (p_effective_date in date
444 ,p_successee_detail_id in number
445 ,p_object_version_number in out nocopy number
446 ,p_business_group_id in number default hr_api.g_number
447 ,p_successee_type in varchar2 default hr_api.g_varchar2
448 ,p_successee_id in number default hr_api.g_number
449 ,p_display_permissions in varchar2 default hr_api.g_varchar2
450 ,p_allow_npws in varchar2 default hr_api.g_varchar2
451 ,p_allow_applicants in varchar2 default hr_api.g_varchar2
452 ,p_allow_override_search in varchar2 default hr_api.g_varchar2
453 ,p_max_plans in number default hr_api.g_number
454 ,p_key_entity in varchar2 default hr_api.g_varchar2
455 ,p_risk_of_loss in varchar2 default hr_api.g_varchar2
456 ,p_impact_of_loss in varchar2 default hr_api.g_varchar2
457 ,p_plan_readiness_rule in varchar2 default hr_api.g_varchar2
458 ,p_max_successors in number default hr_api.g_number
459 ) is
460 --
461 l_rec per_ssd_shd.g_rec_type;
462 l_proc varchar2(72) := g_package||'upd';
463 --
464 Begin
465 hr_utility.set_location('Entering:'||l_proc, 5);
466 --
467 -- Call conversion function to turn arguments into the
468 -- l_rec structure.
469 --
470 l_rec :=
471 per_ssd_shd.convert_args
472 (p_successee_detail_id
473 ,p_business_group_id
474 ,p_successee_type
475 ,p_successee_id
476 ,p_display_permissions
477 ,p_max_plans
478 ,p_allow_npws
479 ,p_allow_applicants
480 ,p_allow_override_search
481 ,p_key_entity
482 ,p_risk_of_loss
483 ,p_impact_of_loss
484 ,p_plan_readiness_rule
485 ,p_max_successors
486 ,p_object_version_number
487 );
488 --
489 -- Having converted the arguments into the
490 -- plsql record structure we call the corresponding record
491 -- business process.
492 --
493 per_ssd_upd.upd
494 (p_effective_date
495 ,l_rec
496 );
497 p_object_version_number := l_rec.object_version_number;
498 --
499 hr_utility.set_location(' Leaving:'||l_proc, 10);
500 End upd;
501 --
502 end per_ssd_upd;