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