1 Package Body ota_ocl_upd as
2 /* $Header: otoclrhi.pkb 120.1.12000000.2 2007/02/07 09:19:37 niarora noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_ocl_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 ota_ocl_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 ota_competence_languages Row
69 --
70 update ota_competence_languages
71 set
72 competence_language_id = p_rec.competence_language_id
73 ,competence_id = p_rec.competence_id
74 ,language_code = p_rec.language_code
75 ,min_proficiency_level_id = p_rec.min_proficiency_level_id
76 ,business_group_id = p_rec.business_group_id
77 ,object_version_number = p_rec.object_version_number
78 ,ocl_information_category = p_rec.ocl_information_category
79 ,ocl_information1 = p_rec.ocl_information1
80 ,ocl_information2 = p_rec.ocl_information2
81 ,ocl_information3 = p_rec.ocl_information3
82 ,ocl_information4 = p_rec.ocl_information4
83 ,ocl_information5 = p_rec.ocl_information5
84 ,ocl_information6 = p_rec.ocl_information6
85 ,ocl_information7 = p_rec.ocl_information7
86 ,ocl_information8 = p_rec.ocl_information8
87 ,ocl_information9 = p_rec.ocl_information9
88 ,ocl_information10 = p_rec.ocl_information10
89 ,ocl_information11 = p_rec.ocl_information11
90 ,ocl_information12 = p_rec.ocl_information12
91 ,ocl_information13 = p_rec.ocl_information13
92 ,ocl_information14 = p_rec.ocl_information14
93 ,ocl_information15 = p_rec.ocl_information15
94 ,ocl_information16 = p_rec.ocl_information16
95 ,ocl_information17 = p_rec.ocl_information17
96 ,ocl_information18 = p_rec.ocl_information18
97 ,ocl_information19 = p_rec.ocl_information19
98 ,ocl_information20 = p_rec.ocl_information20
99 where competence_language_id = p_rec.competence_language_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 ota_ocl_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 ota_ocl_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 ota_ocl_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 ota_ocl_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 the
177 -- 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 ota_ocl_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 ota_ocl_rku.after_update
214 (p_effective_date => p_effective_date
215 ,p_competence_language_id
216 => p_rec.competence_language_id
217 ,p_competence_id
218 => p_rec.competence_id
219 ,p_language_code
220 => p_rec.language_code
221 ,p_min_proficiency_level_id
222 => p_rec.min_proficiency_level_id
223 ,p_business_group_id
224 => p_rec.business_group_id
225 ,p_object_version_number
226 => p_rec.object_version_number
227 ,p_ocl_information_category
228 => p_rec.ocl_information_category
229 ,p_ocl_information1
230 => p_rec.ocl_information1
231 ,p_ocl_information2
232 => p_rec.ocl_information2
233 ,p_ocl_information3
234 => p_rec.ocl_information3
235 ,p_ocl_information4
236 => p_rec.ocl_information4
237 ,p_ocl_information5
238 => p_rec.ocl_information5
239 ,p_ocl_information6
240 => p_rec.ocl_information6
241 ,p_ocl_information7
242 => p_rec.ocl_information7
243 ,p_ocl_information8
244 => p_rec.ocl_information8
245 ,p_ocl_information9
246 => p_rec.ocl_information9
247 ,p_ocl_information10
248 => p_rec.ocl_information10
249 ,p_ocl_information11
250 => p_rec.ocl_information11
251 ,p_ocl_information12
252 => p_rec.ocl_information12
253 ,p_ocl_information13
254 => p_rec.ocl_information13
255 ,p_ocl_information14
256 => p_rec.ocl_information14
257 ,p_ocl_information15
258 => p_rec.ocl_information15
259 ,p_ocl_information16
260 => p_rec.ocl_information16
261 ,p_ocl_information17
262 => p_rec.ocl_information17
263 ,p_ocl_information18
264 => p_rec.ocl_information18
265 ,p_ocl_information19
266 => p_rec.ocl_information19
267 ,p_ocl_information20
268 => p_rec.ocl_information20
269 ,p_competence_id_o
270 => ota_ocl_shd.g_old_rec.competence_id
271 ,p_language_code_o
272 => ota_ocl_shd.g_old_rec.language_code
273 ,p_min_proficiency_level_id_o
274 => ota_ocl_shd.g_old_rec.min_proficiency_level_id
275 ,p_business_group_id_o
276 => ota_ocl_shd.g_old_rec.business_group_id
277 ,p_object_version_number_o
278 => ota_ocl_shd.g_old_rec.object_version_number
279 ,p_ocl_information_category_o
280 => ota_ocl_shd.g_old_rec.ocl_information_category
281 ,p_ocl_information1_o
282 => ota_ocl_shd.g_old_rec.ocl_information1
283 ,p_ocl_information2_o
284 => ota_ocl_shd.g_old_rec.ocl_information2
285 ,p_ocl_information3_o
286 => ota_ocl_shd.g_old_rec.ocl_information3
287 ,p_ocl_information4_o
288 => ota_ocl_shd.g_old_rec.ocl_information4
289 ,p_ocl_information5_o
290 => ota_ocl_shd.g_old_rec.ocl_information5
291 ,p_ocl_information6_o
292 => ota_ocl_shd.g_old_rec.ocl_information6
293 ,p_ocl_information7_o
294 => ota_ocl_shd.g_old_rec.ocl_information7
295 ,p_ocl_information8_o
296 => ota_ocl_shd.g_old_rec.ocl_information8
297 ,p_ocl_information9_o
298 => ota_ocl_shd.g_old_rec.ocl_information9
299 ,p_ocl_information10_o
300 => ota_ocl_shd.g_old_rec.ocl_information10
301 ,p_ocl_information11_o
302 => ota_ocl_shd.g_old_rec.ocl_information11
303 ,p_ocl_information12_o
304 => ota_ocl_shd.g_old_rec.ocl_information12
305 ,p_ocl_information13_o
306 => ota_ocl_shd.g_old_rec.ocl_information13
307 ,p_ocl_information14_o
308 => ota_ocl_shd.g_old_rec.ocl_information14
309 ,p_ocl_information15_o
310 => ota_ocl_shd.g_old_rec.ocl_information15
311 ,p_ocl_information16_o
312 => ota_ocl_shd.g_old_rec.ocl_information16
313 ,p_ocl_information17_o
314 => ota_ocl_shd.g_old_rec.ocl_information17
315 ,p_ocl_information18_o
316 => ota_ocl_shd.g_old_rec.ocl_information18
317 ,p_ocl_information19_o
318 => ota_ocl_shd.g_old_rec.ocl_information19
319 ,p_ocl_information20_o
320 => ota_ocl_shd.g_old_rec.ocl_information20
321 );
322 --
323 exception
324 --
325 when hr_api.cannot_find_prog_unit then
326 --
327 hr_api.cannot_find_prog_unit_error
328 (p_module_name => 'OTA_COMPETENCE_LANGUAGES'
329 ,p_hook_type => 'AU');
330 --
331 end;
332 --
333 hr_utility.set_location(' Leaving:'||l_proc, 10);
334 End post_update;
335 --
336 -- ----------------------------------------------------------------------------
337 -- |-----------------------------< convert_defs >-----------------------------|
338 -- ----------------------------------------------------------------------------
339 -- {Start Of Comments}
340 --
341 -- Description:
342 -- The Convert_Defs procedure has one very important function:
343 -- It must return the record structure for the row with all system defaulted
344 -- values converted into its corresponding parameter value for update. When
345 -- we attempt to update a row through the Upd process , certain
346 -- parameters can be defaulted which enables flexibility in the calling of
347 -- the upd process (e.g. only attributes which need to be updated need to be
348 -- specified). For the upd process to determine which attributes
349 -- have NOT been specified we need to check if the parameter has a reserved
350 -- system default value. Therefore, for all parameters which have a
351 -- corresponding reserved system default mechanism specified we need to
352 -- check if a system default is being used. If a system default is being
353 -- used then we convert the defaulted value into its corresponding attribute
354 -- value held in the g_old_rec data structure.
355 --
356 -- Prerequisites:
357 -- This private function can only be called from the upd process.
358 --
359 -- In Parameters:
360 -- A Pl/Sql record structure.
361 --
362 -- Post Success:
363 -- The record structure will be returned with all system defaulted parameter
364 -- values converted into its current row attribute value.
365 --
366 -- Post Failure:
367 -- No direct error handling is required within this function. Any possible
368 -- errors within this procedure will be a PL/SQL value error due to
369 -- conversion of datatypes or data lengths.
370 --
371 -- Developer Implementation Notes:
372 -- None.
373 --
374 -- Access Status:
375 -- Internal Row Handler Use Only.
376 --
377 -- {End Of Comments}
378 -- ----------------------------------------------------------------------------
379 Procedure convert_defs
380 (p_rec in out nocopy ota_ocl_shd.g_rec_type
381 ) is
382 --
383 Begin
384 --
385 -- We must now examine each argument value in the
386 -- p_rec plsql record structure
387 -- to see if a system default is being used. If a system default
388 -- is being used then we must set to the 'current' argument value.
389 --
390 If (p_rec.competence_id = hr_api.g_number) then
391 p_rec.competence_id :=
392 ota_ocl_shd.g_old_rec.competence_id;
393 End If;
394 If (p_rec.language_code = hr_api.g_varchar2) then
395 p_rec.language_code :=
396 ota_ocl_shd.g_old_rec.language_code;
397 End If;
398 If (p_rec.min_proficiency_level_id = hr_api.g_number) then
399 p_rec.min_proficiency_level_id :=
400 ota_ocl_shd.g_old_rec.min_proficiency_level_id;
401 End If;
402 If (p_rec.business_group_id = hr_api.g_number) then
403 p_rec.business_group_id :=
404 ota_ocl_shd.g_old_rec.business_group_id;
405 End If;
406 If (p_rec.ocl_information_category = hr_api.g_varchar2) then
407 p_rec.ocl_information_category :=
408 ota_ocl_shd.g_old_rec.ocl_information_category;
409 End If;
410 If (p_rec.ocl_information1 = hr_api.g_varchar2) then
411 p_rec.ocl_information1 :=
412 ota_ocl_shd.g_old_rec.ocl_information1;
413 End If;
414 If (p_rec.ocl_information2 = hr_api.g_varchar2) then
415 p_rec.ocl_information2 :=
416 ota_ocl_shd.g_old_rec.ocl_information2;
417 End If;
418 If (p_rec.ocl_information3 = hr_api.g_varchar2) then
419 p_rec.ocl_information3 :=
420 ota_ocl_shd.g_old_rec.ocl_information3;
421 End If;
422 If (p_rec.ocl_information4 = hr_api.g_varchar2) then
423 p_rec.ocl_information4 :=
424 ota_ocl_shd.g_old_rec.ocl_information4;
425 End If;
426 If (p_rec.ocl_information5 = hr_api.g_varchar2) then
427 p_rec.ocl_information5 :=
428 ota_ocl_shd.g_old_rec.ocl_information5;
429 End If;
430 If (p_rec.ocl_information6 = hr_api.g_varchar2) then
431 p_rec.ocl_information6 :=
432 ota_ocl_shd.g_old_rec.ocl_information6;
433 End If;
434 If (p_rec.ocl_information7 = hr_api.g_varchar2) then
435 p_rec.ocl_information7 :=
436 ota_ocl_shd.g_old_rec.ocl_information7;
437 End If;
438 If (p_rec.ocl_information8 = hr_api.g_varchar2) then
439 p_rec.ocl_information8 :=
440 ota_ocl_shd.g_old_rec.ocl_information8;
441 End If;
442 If (p_rec.ocl_information9 = hr_api.g_varchar2) then
443 p_rec.ocl_information9 :=
444 ota_ocl_shd.g_old_rec.ocl_information9;
445 End If;
446 If (p_rec.ocl_information10 = hr_api.g_varchar2) then
447 p_rec.ocl_information10 :=
448 ota_ocl_shd.g_old_rec.ocl_information10;
449 End If;
450 If (p_rec.ocl_information11 = hr_api.g_varchar2) then
451 p_rec.ocl_information11 :=
452 ota_ocl_shd.g_old_rec.ocl_information11;
453 End If;
454 If (p_rec.ocl_information12 = hr_api.g_varchar2) then
455 p_rec.ocl_information12 :=
456 ota_ocl_shd.g_old_rec.ocl_information12;
457 End If;
458 If (p_rec.ocl_information13 = hr_api.g_varchar2) then
459 p_rec.ocl_information13 :=
460 ota_ocl_shd.g_old_rec.ocl_information13;
461 End If;
462 If (p_rec.ocl_information14 = hr_api.g_varchar2) then
463 p_rec.ocl_information14 :=
464 ota_ocl_shd.g_old_rec.ocl_information14;
465 End If;
466 If (p_rec.ocl_information15 = hr_api.g_varchar2) then
467 p_rec.ocl_information15 :=
468 ota_ocl_shd.g_old_rec.ocl_information15;
469 End If;
470 If (p_rec.ocl_information16 = hr_api.g_varchar2) then
471 p_rec.ocl_information16 :=
472 ota_ocl_shd.g_old_rec.ocl_information16;
473 End If;
474 If (p_rec.ocl_information17 = hr_api.g_varchar2) then
475 p_rec.ocl_information17 :=
476 ota_ocl_shd.g_old_rec.ocl_information17;
477 End If;
478 If (p_rec.ocl_information18 = hr_api.g_varchar2) then
479 p_rec.ocl_information18 :=
480 ota_ocl_shd.g_old_rec.ocl_information18;
481 End If;
482 If (p_rec.ocl_information19 = hr_api.g_varchar2) then
483 p_rec.ocl_information19 :=
484 ota_ocl_shd.g_old_rec.ocl_information19;
485 End If;
486 If (p_rec.ocl_information20 = hr_api.g_varchar2) then
487 p_rec.ocl_information20 :=
488 ota_ocl_shd.g_old_rec.ocl_information20;
489 End If;
490 --
491 End convert_defs;
492 --
493 -- ----------------------------------------------------------------------------
494 -- |---------------------------------< upd >----------------------------------|
495 -- ----------------------------------------------------------------------------
496 Procedure upd
497 (p_effective_date in date
498 ,p_rec in out nocopy ota_ocl_shd.g_rec_type
499 ) is
500 --
501 l_proc varchar2(72) := g_package||'upd';
502 --
503 Begin
504 hr_utility.set_location('Entering:'||l_proc, 5);
505 --
506 -- We must lock the row which we need to update.
507 --
508 ota_ocl_shd.lck
509 (p_rec.competence_language_id
510 ,p_rec.object_version_number
511 );
512 --
513 -- 1. During an update system defaults are used to determine if
514 -- arguments have been defaulted or not. We must therefore
515 -- derive the full record structure values to be updated.
516 --
517 -- 2. Call the supporting update validate operations.
518 --
519 convert_defs(p_rec);
520 ota_ocl_bus.update_validate
521 (p_effective_date
522 ,p_rec
523 );
524 --
525 -- Call the supporting pre-update operation
526 --
527 ota_ocl_upd.pre_update(p_rec);
528 --
529 -- Update the row.
530 --
531 ota_ocl_upd.update_dml(p_rec);
532 --
533 -- Call the supporting post-update operation
534 --
535 ota_ocl_upd.post_update
536 (p_effective_date
537 ,p_rec
538 );
539 End upd;
540 --
541 -- ----------------------------------------------------------------------------
542 -- |---------------------------------< upd >----------------------------------|
543 -- ----------------------------------------------------------------------------
544 Procedure upd
545 (p_effective_date in date
546 ,p_competence_language_id in number
547 ,p_object_version_number in out nocopy number
548 ,p_competence_id in number default hr_api.g_number
549 ,p_language_code in varchar2 default hr_api.g_varchar2
550 ,p_business_group_id in number default hr_api.g_number
551 ,p_min_proficiency_level_id in number default hr_api.g_number
552 ,p_ocl_information_category in varchar2 default hr_api.g_varchar2
553 ,p_ocl_information1 in varchar2 default hr_api.g_varchar2
554 ,p_ocl_information2 in varchar2 default hr_api.g_varchar2
555 ,p_ocl_information3 in varchar2 default hr_api.g_varchar2
556 ,p_ocl_information4 in varchar2 default hr_api.g_varchar2
557 ,p_ocl_information5 in varchar2 default hr_api.g_varchar2
558 ,p_ocl_information6 in varchar2 default hr_api.g_varchar2
559 ,p_ocl_information7 in varchar2 default hr_api.g_varchar2
560 ,p_ocl_information8 in varchar2 default hr_api.g_varchar2
561 ,p_ocl_information9 in varchar2 default hr_api.g_varchar2
562 ,p_ocl_information10 in varchar2 default hr_api.g_varchar2
563 ,p_ocl_information11 in varchar2 default hr_api.g_varchar2
564 ,p_ocl_information12 in varchar2 default hr_api.g_varchar2
565 ,p_ocl_information13 in varchar2 default hr_api.g_varchar2
566 ,p_ocl_information14 in varchar2 default hr_api.g_varchar2
567 ,p_ocl_information15 in varchar2 default hr_api.g_varchar2
568 ,p_ocl_information16 in varchar2 default hr_api.g_varchar2
569 ,p_ocl_information17 in varchar2 default hr_api.g_varchar2
570 ,p_ocl_information18 in varchar2 default hr_api.g_varchar2
571 ,p_ocl_information19 in varchar2 default hr_api.g_varchar2
572 ,p_ocl_information20 in varchar2 default hr_api.g_varchar2
573 ) is
574 --
575 l_rec ota_ocl_shd.g_rec_type;
576 l_proc varchar2(72) := g_package||'upd';
577 --
578 Begin
579 hr_utility.set_location('Entering:'||l_proc, 5);
580 --
581 -- Call conversion function to turn arguments into the
582 -- l_rec structure.
583 --
584 l_rec :=
585 ota_ocl_shd.convert_args
586 (p_competence_language_id
587 ,p_competence_id
588 ,p_language_code
589 ,p_min_proficiency_level_id
590 ,p_business_group_id
591 ,p_object_version_number
592 ,p_ocl_information_category
593 ,p_ocl_information1
594 ,p_ocl_information2
595 ,p_ocl_information3
596 ,p_ocl_information4
597 ,p_ocl_information5
598 ,p_ocl_information6
599 ,p_ocl_information7
600 ,p_ocl_information8
601 ,p_ocl_information9
602 ,p_ocl_information10
603 ,p_ocl_information11
604 ,p_ocl_information12
605 ,p_ocl_information13
606 ,p_ocl_information14
607 ,p_ocl_information15
608 ,p_ocl_information16
609 ,p_ocl_information17
610 ,p_ocl_information18
611 ,p_ocl_information19
612 ,p_ocl_information20
613 );
614 --
615 -- Having converted the arguments into the
616 -- plsql record structure we call the corresponding record
617 -- business process.
618 --
619 ota_ocl_upd.upd
620 (p_effective_date
621 ,l_rec
622 );
623 p_object_version_number := l_rec.object_version_number;
624 --
625 hr_utility.set_location(' Leaving:'||l_proc, 10);
626 End upd;
627 --
628 end ota_ocl_upd;
[Home] [Help]