1 Package Body ota_tsr_upd as
2 /* $Header: ottsr01t.pkb 120.2 2005/08/08 23:27:40 ssur noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_tsr_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 functions of this
17 -- procedure are as follows:
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 -- Pre Conditions:
28 -- This is an internal private procedure which must be called from the upd
29 -- procedure.
30 --
31 -- In Arguments:
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' arguments list should be modified if any of your
47 -- attributes are not updateable.
48 --
49 -- Access Status:
50 -- Internal Development Use Only.
51 --
52 -- {End Of Comments}
53 -- ----------------------------------------------------------------------------
54 Procedure update_dml(p_rec in out nocopy ota_tsr_shd.g_rec_type) is
55 --
56 l_proc varchar2(72) := g_package||'update_dml';
57 --
58 Begin
59 hr_utility.set_location('Entering:'||l_proc, 5);
60 --
61 -- Increment the object version
62 --
63 p_rec.object_version_number := p_rec.object_version_number + 1;
64 --
65 ota_tsr_shd.g_api_dml := true; -- Set the api dml status
66 --
67 -- Update the ota_suppliable_resources Row
68 --
69 update ota_suppliable_resources
70 set
71 supplied_resource_id = p_rec.supplied_resource_id,
72 vendor_id = p_rec.vendor_id,
73 business_group_id = p_rec.business_group_id,
74 resource_definition_id = p_rec.resource_definition_id,
75 consumable_flag = p_rec.consumable_flag,
76 object_version_number = p_rec.object_version_number,
77 resource_type = p_rec.resource_type,
78 start_date = p_rec.start_date,
79 comments = p_rec.comments,
80 cost = p_rec.cost,
81 cost_unit = p_rec.cost_unit,
82 currency_code = p_rec.currency_code,
83 end_date = p_rec.end_date,
84 internal_address_line = p_rec.internal_address_line,
85 lead_time = p_rec.lead_time,
86 name = p_rec.name,
87 supplier_reference = p_rec.supplier_reference,
88 tsr_information_category = p_rec.tsr_information_category,
89 tsr_information1 = p_rec.tsr_information1,
90 tsr_information2 = p_rec.tsr_information2,
91 tsr_information3 = p_rec.tsr_information3,
92 tsr_information4 = p_rec.tsr_information4,
93 tsr_information5 = p_rec.tsr_information5,
94 tsr_information6 = p_rec.tsr_information6,
95 tsr_information7 = p_rec.tsr_information7,
96 tsr_information8 = p_rec.tsr_information8,
97 tsr_information9 = p_rec.tsr_information9,
98 tsr_information10 = p_rec.tsr_information10,
99 tsr_information11 = p_rec.tsr_information11,
100 tsr_information12 = p_rec.tsr_information12,
101 tsr_information13 = p_rec.tsr_information13,
102 tsr_information14 = p_rec.tsr_information14,
103 tsr_information15 = p_rec.tsr_information15,
104 tsr_information16 = p_rec.tsr_information16,
105 tsr_information17 = p_rec.tsr_information17,
106 tsr_information18 = p_rec.tsr_information18,
107 tsr_information19 = p_rec.tsr_information19,
108 tsr_information20 = p_rec.tsr_information20,
109 training_center_id = p_rec.training_center_id,
110 location_id = p_rec.location_id,
111 trainer_id = p_rec.trainer_id,
112 special_instruction = p_rec.special_instruction
113 where supplied_resource_id = p_rec.supplied_resource_id;
114 --
115 ota_tsr_shd.g_api_dml := false; -- Unset the api dml status
116 --
117 hr_utility.set_location(' Leaving:'||l_proc, 10);
118 --
119 Exception
120 When hr_api.check_integrity_violated Then
121 -- A check constraint has been violated
122 ota_tsr_shd.g_api_dml := false; -- Unset the api dml status
123 ota_tsr_shd.constraint_error
124 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
125 When hr_api.parent_integrity_violated Then
126 -- Parent integrity has been violated
127 ota_tsr_shd.g_api_dml := false; -- Unset the api dml status
128 ota_tsr_shd.constraint_error
129 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
130 When hr_api.unique_integrity_violated Then
131 -- Unique integrity has been violated
132 ota_tsr_shd.g_api_dml := false; -- Unset the api dml status
133 ota_tsr_shd.constraint_error
134 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
135 When Others Then
136 ota_tsr_shd.g_api_dml := false; -- Unset the api dml status
137 Raise;
138 End update_dml;
139 --
140 -- ----------------------------------------------------------------------------
141 -- |------------------------------< pre_update >------------------------------|
142 -- ----------------------------------------------------------------------------
143 -- {Start Of Comments}
144 --
145 -- Description:
146 -- This private procedure contains any processing which is required before
147 -- the update dml.
148 --
149 -- Pre Conditions:
150 -- This is an internal procedure which is called from the upd procedure.
151 --
152 -- In Arguments:
153 -- A Pl/Sql record structre.
154 --
155 -- Post Success:
156 -- Processing continues.
157 --
158 -- Post Failure:
159 -- If an error has occurred, an error message and exception will be raised
160 -- but not handled.
161 --
162 -- Developer Implementation Notes:
163 -- Any pre-processing required before the update dml is issued should be
164 -- coded within this procedure. It is important to note that any 3rd party
165 -- maintenance should be reviewed before placing in this procedure.
166 --
167 -- Access Status:
168 -- Internal Development Use Only.
169 --
170 -- {End Of Comments}
171 -- ----------------------------------------------------------------------------
172 Procedure pre_update(p_rec in ota_tsr_shd.g_rec_type) is
173 --
174 l_proc varchar2(72) := g_package||'pre_update';
175 --
176 Begin
177 hr_utility.set_location('Entering:'||l_proc, 5);
178 --
179 hr_utility.set_location(' Leaving:'||l_proc, 10);
180 End pre_update;
181 --
182 -- ----------------------------------------------------------------------------
183 -- |-----------------------------< post_update >------------------------------|
184 -- ----------------------------------------------------------------------------
185 -- {Start Of Comments}
186 --
187 -- Description:
188 -- This private procedure contains any processing which is required after the
189 -- update dml.
190 --
191 -- Pre Conditions:
192 -- This is an internal procedure which is called from the upd procedure.
193 --
194 -- In Arguments:
195 -- A Pl/Sql record structre.
196 --
197 -- Post Success:
198 -- Processing continues.
199 --
200 -- Post Failure:
201 -- If an error has occurred, an error message and exception will be raised
202 -- but not handled.
203 --
204 -- Developer Implementation Notes:
205 -- Any post-processing required after the update dml is issued should be
206 -- coded within this procedure. It is important to note that any 3rd party
207 -- maintenance should be reviewed before placing in this procedure.
208 --
209 -- Access Status:
210 -- Internal Development Use Only.
211 --
212 -- {End Of Comments}
213 -- ----------------------------------------------------------------------------
214 Procedure post_update(p_rec in ota_tsr_shd.g_rec_type) is
215 --
216 l_proc varchar2(72) := g_package||'post_update';
217 --
218 Begin
219 hr_utility.set_location('Entering:'||l_proc, 5);
220 --
221 hr_utility.set_location(' Leaving:'||l_proc, 10);
222 End post_update;
223 --
224 -- ----------------------------------------------------------------------------
225 -- |-----------------------------< convert_defs >-----------------------------|
226 -- ----------------------------------------------------------------------------
227 -- {Start Of Comments}
228 --
229 -- Description:
230 -- The Convert_Defs function has one very important function:
231 -- It must return the record structure for the row with all system defaulted
232 -- values converted into its corresponding argument value for update. When
233 -- we attempt to update a row through the Upd business process , certain
234 -- arguments can be defaulted which enables flexibility in the calling of
235 -- the upd process (e.g. only attributes which need to be updated need to be
236 -- specified). For the upd business process to determine which attributes
237 -- have NOT been specified we need to check if the argument has a reserved
238 -- system default value. Therefore, for all attributes which have a
239 -- corresponding reserved system default mechanism specified we need to
240 -- check if a system default is being used. If a system default is being
241 -- used then we convert the defaulted value into its corresponding attribute
242 -- value held in the g_old_rec data structure.
243 --
244 -- Pre Conditions:
245 -- This private function can only be called from the upd process.
246 --
247 -- In Arguments:
248 -- A Pl/Sql record structre.
249 --
250 -- Post Success:
251 -- The record structure will be returned with all system defaulted argument
252 -- values converted into its current row attribute value.
253 --
254 -- Post Failure:
255 -- No direct error handling is required within this function. Any possible
256 -- errors within this function will be a PL/SQL value error due to conversion
257 -- of datatypes or data lengths.
258 --
259 -- Developer Implementation Notes:
260 -- None.
261 --
262 -- Access Status:
263 -- Internal Development Use Only.
264 --
265 -- {End Of Comments}
266 -- ----------------------------------------------------------------------------
267 Function convert_defs(p_rec in out nocopy ota_tsr_shd.g_rec_type)
268 Return ota_tsr_shd.g_rec_type is
269 --
270 l_proc varchar2(72) := g_package||'convert_defs';
271 --
272 Begin
273 --
274 hr_utility.set_location('Entering:'||l_proc, 5);
275 --
276 -- We must now examine each argument value in the
277 -- p_rec plsql record structure
278 -- to see if a system default is being used. If a system default
279 -- is being used then we must set to the 'current' argument value.
280 --
281 If (p_rec.vendor_id = hr_api.g_number) then
282 p_rec.vendor_id :=
283 ota_tsr_shd.g_old_rec.vendor_id;
284 End If;
285 If (p_rec.business_group_id = hr_api.g_number) then
286 p_rec.business_group_id :=
287 ota_tsr_shd.g_old_rec.business_group_id;
288 End If;
289 If (p_rec.resource_definition_id = hr_api.g_number) then
290 p_rec.resource_definition_id :=
291 ota_tsr_shd.g_old_rec.resource_definition_id;
292 End If;
293 If (p_rec.consumable_flag = hr_api.g_varchar2) then
294 p_rec.consumable_flag :=
295 ota_tsr_shd.g_old_rec.consumable_flag;
296 End If;
297 If (p_rec.resource_type = hr_api.g_varchar2) then
298 p_rec.resource_type :=
299 ota_tsr_shd.g_old_rec.resource_type;
300 End If;
301 If (p_rec.start_date = hr_api.g_date) then
302 p_rec.start_date :=
303 ota_tsr_shd.g_old_rec.start_date;
304 End If;
305 If (p_rec.comments = hr_api.g_varchar2) then
306 p_rec.comments :=
307 ota_tsr_shd.g_old_rec.comments;
308 End If;
309 If (p_rec.cost = hr_api.g_number) then
310 p_rec.cost :=
311 ota_tsr_shd.g_old_rec.cost;
312 End If;
313 If (p_rec.cost_unit = hr_api.g_varchar2) then
314 p_rec.cost_unit :=
315 ota_tsr_shd.g_old_rec.cost_unit;
316 End If;
317 If (p_rec.currency_code = hr_api.g_varchar2) then
318 p_rec.currency_code :=
319 ota_tsr_shd.g_old_rec.currency_code;
320 End If;
321 If (p_rec.end_date = hr_api.g_date) then
322 p_rec.end_date :=
323 ota_tsr_shd.g_old_rec.end_date;
324 End If;
325 If (p_rec.internal_address_line = hr_api.g_varchar2) then
326 p_rec.internal_address_line :=
327 ota_tsr_shd.g_old_rec.internal_address_line;
328 End If;
329 If (p_rec.lead_time = hr_api.g_number) then
330 p_rec.lead_time :=
331 ota_tsr_shd.g_old_rec.lead_time;
332 End If;
333 If (p_rec.name = hr_api.g_varchar2) then
334 p_rec.name :=
335 ota_tsr_shd.g_old_rec.name;
336 End If;
337 If (p_rec.supplier_reference = hr_api.g_varchar2) then
338 p_rec.supplier_reference :=
339 ota_tsr_shd.g_old_rec.supplier_reference;
340 End If;
341 If (p_rec.tsr_information_category = hr_api.g_varchar2) then
342 p_rec.tsr_information_category :=
343 ota_tsr_shd.g_old_rec.tsr_information_category;
344 End If;
345 If (p_rec.tsr_information1 = hr_api.g_varchar2) then
346 p_rec.tsr_information1 :=
347 ota_tsr_shd.g_old_rec.tsr_information1;
348 End If;
349 If (p_rec.tsr_information2 = hr_api.g_varchar2) then
350 p_rec.tsr_information2 :=
351 ota_tsr_shd.g_old_rec.tsr_information2;
352 End If;
353 If (p_rec.tsr_information3 = hr_api.g_varchar2) then
354 p_rec.tsr_information3 :=
355 ota_tsr_shd.g_old_rec.tsr_information3;
356 End If;
357 If (p_rec.tsr_information4 = hr_api.g_varchar2) then
358 p_rec.tsr_information4 :=
359 ota_tsr_shd.g_old_rec.tsr_information4;
360 End If;
361 If (p_rec.tsr_information5 = hr_api.g_varchar2) then
362 p_rec.tsr_information5 :=
363 ota_tsr_shd.g_old_rec.tsr_information5;
364 End If;
365 If (p_rec.tsr_information6 = hr_api.g_varchar2) then
366 p_rec.tsr_information6 :=
367 ota_tsr_shd.g_old_rec.tsr_information6;
368 End If;
369 If (p_rec.tsr_information7 = hr_api.g_varchar2) then
370 p_rec.tsr_information7 :=
371 ota_tsr_shd.g_old_rec.tsr_information7;
372 End If;
373 If (p_rec.tsr_information8 = hr_api.g_varchar2) then
374 p_rec.tsr_information8 :=
375 ota_tsr_shd.g_old_rec.tsr_information8;
376 End If;
377 If (p_rec.tsr_information9 = hr_api.g_varchar2) then
381 If (p_rec.tsr_information10 = hr_api.g_varchar2) then
378 p_rec.tsr_information9 :=
379 ota_tsr_shd.g_old_rec.tsr_information9;
380 End If;
382 p_rec.tsr_information10 :=
383 ota_tsr_shd.g_old_rec.tsr_information10;
384 End If;
385 If (p_rec.tsr_information11 = hr_api.g_varchar2) then
386 p_rec.tsr_information11 :=
387 ota_tsr_shd.g_old_rec.tsr_information11;
388 End If;
389 If (p_rec.tsr_information12 = hr_api.g_varchar2) then
390 p_rec.tsr_information12 :=
391 ota_tsr_shd.g_old_rec.tsr_information12;
392 End If;
393 If (p_rec.tsr_information13 = hr_api.g_varchar2) then
394 p_rec.tsr_information13 :=
395 ota_tsr_shd.g_old_rec.tsr_information13;
396 End If;
397 If (p_rec.tsr_information14 = hr_api.g_varchar2) then
398 p_rec.tsr_information14 :=
399 ota_tsr_shd.g_old_rec.tsr_information14;
400 End If;
401 If (p_rec.tsr_information15 = hr_api.g_varchar2) then
402 p_rec.tsr_information15 :=
403 ota_tsr_shd.g_old_rec.tsr_information15;
404 End If;
405 If (p_rec.tsr_information16 = hr_api.g_varchar2) then
406 p_rec.tsr_information16 :=
407 ota_tsr_shd.g_old_rec.tsr_information16;
408 End If;
409 If (p_rec.tsr_information17 = hr_api.g_varchar2) then
410 p_rec.tsr_information17 :=
411 ota_tsr_shd.g_old_rec.tsr_information17;
412 End If;
413 If (p_rec.tsr_information18 = hr_api.g_varchar2) then
414 p_rec.tsr_information18 :=
415 ota_tsr_shd.g_old_rec.tsr_information18;
416 End If;
417 If (p_rec.tsr_information19 = hr_api.g_varchar2) then
418 p_rec.tsr_information19 :=
419 ota_tsr_shd.g_old_rec.tsr_information19;
420 End If;
421 If (p_rec.tsr_information20 = hr_api.g_varchar2) then
422 p_rec.tsr_information20 :=
423 ota_tsr_shd.g_old_rec.tsr_information20;
424 End If;
425 If (p_rec.training_center_id = hr_api.g_number) then
426 p_rec.training_center_id :=
427 ota_tsr_shd.g_old_rec.training_center_id;
428 End If;
429 If (p_rec.location_id = hr_api.g_number) then
430 p_rec.location_id :=
431 ota_tsr_shd.g_old_rec.location_id;
432 End If;
433 If (p_rec.trainer_id = hr_api.g_number) then
434 p_rec.trainer_id :=
435 ota_tsr_shd.g_old_rec.trainer_id;
436 End If;
437 If (p_rec.special_instruction = hr_api.g_varchar2) then
438 p_rec.special_instruction :=
439 ota_tsr_shd.g_old_rec.special_instruction;
440 End If; --
441 -- Return the plsql record structure.
442 --
443 hr_utility.set_location(' Leaving:'||l_proc, 10);
444 Return(p_rec);
445 --
446 End convert_defs;
447 --
448 -- ----------------------------------------------------------------------------
449 -- |---------------------------------< upd >----------------------------------|
450 -- ----------------------------------------------------------------------------
451 Procedure upd
452 (
453 p_rec in out nocopy ota_tsr_shd.g_rec_type,
454 p_validate in boolean
455 ) is
456 --
457 l_proc varchar2(72) := g_package||'upd';
458 --
459 Begin
460 hr_utility.set_location('Entering:'||l_proc, 5);
461 --
462 -- Determine if the business process is to be validated.
463 --
464 If p_validate then
465 --
466 -- Issue the savepoint.
467 --
468 SAVEPOINT upd_ota_tsr;
469 End If;
470 --
471 -- We must lock the row which we need to update.
472 --
473 ota_tsr_shd.lck
474 (
475 p_rec.supplied_resource_id,
476 p_rec.object_version_number
477 );
478 --
479 -- 1. During an update system defaults are used to determine if
480 -- arguments have been defaulted or not. We must therefore
481 -- derive the full record structure values to be updated.
482 --
483 -- 2. Call the supporting update validate operations.
484 --
485 ota_tsr_bus.update_validate(convert_defs(p_rec));
486 --
487 -- Call the supporting pre-update operation
488 --
489 pre_update(p_rec);
490 --
491 -- Update the row.
492 --
493 update_dml(p_rec);
494 --
495 -- Call the supporting post-update operation
496 --
497 post_update(p_rec);
498 --
499 -- If we are validating then raise the Validate_Enabled exception
500 --
501 If p_validate then
502 Raise HR_Api.Validate_Enabled;
503 End If;
504 --
505 hr_utility.set_location(' Leaving:'||l_proc, 10);
506 Exception
507 When HR_Api.Validate_Enabled Then
508 --
509 -- As the Validate_Enabled exception has been raised
510 -- we must rollback to the savepoint
511 --
512 ROLLBACK TO upd_ota_tsr;
513 End upd;
514 --
515 -- ----------------------------------------------------------------------------
516 -- |---------------------------------< upd >----------------------------------|
517 -- ----------------------------------------------------------------------------
518 Procedure upd
519 (
520 p_supplied_resource_id in number,
521 p_vendor_id in number,
522 p_business_group_id in number,
523 p_resource_definition_id in number,
524 p_consumable_flag in varchar2,
525 p_object_version_number in out nocopy number,
526 p_resource_type in varchar2,
530 p_cost_unit in varchar2,
527 p_start_date in date ,
528 p_comments in varchar2,
529 p_cost in number ,
531 p_currency_code in varchar2,
532 p_end_date in date ,
533 p_internal_address_line in varchar2,
534 p_lead_time in number ,
535 p_name in varchar2,
536 p_supplier_reference in varchar2,
537 p_tsr_information_category in varchar2,
538 p_tsr_information1 in varchar2 ,
539 p_tsr_information2 in varchar2,
540 p_tsr_information3 in varchar2,
541 p_tsr_information4 in varchar2,
542 p_tsr_information5 in varchar2,
543 p_tsr_information6 in varchar2,
544 p_tsr_information7 in varchar2,
545 p_tsr_information8 in varchar2,
546 p_tsr_information9 in varchar2,
547 p_tsr_information10 in varchar2,
548 p_tsr_information11 in varchar2,
549 p_tsr_information12 in varchar2,
550 p_tsr_information13 in varchar2,
551 p_tsr_information14 in varchar2,
552 p_tsr_information15 in varchar2,
553 p_tsr_information16 in varchar2,
554 p_tsr_information17 in varchar2,
555 p_tsr_information18 in varchar2,
556 p_tsr_information19 in varchar2,
557 p_tsr_information20 in varchar2,
558 p_training_center_id in number,
559 p_location_id in number,
560 p_trainer_id in number,
561 p_special_instruction in varchar2,
562 p_validate in boolean
563 ) is
564 --
565 l_rec ota_tsr_shd.g_rec_type;
566 l_proc varchar2(72) := g_package||'upd';
567 --
568 Begin
569 hr_utility.set_location('Entering:'||l_proc, 5);
570 --
571 -- Call conversion function to turn arguments into the
572 -- l_rec structure.
573 --
574 l_rec :=
575 ota_tsr_shd.convert_args
576 (
577 p_supplied_resource_id,
578 p_vendor_id,
579 p_business_group_id,
580 p_resource_definition_id,
581 p_consumable_flag,
582 p_object_version_number,
583 p_resource_type,
584 p_start_date,
585 p_comments,
586 p_cost,
587 p_cost_unit,
588 p_currency_code,
589 p_end_date,
590 p_internal_address_line,
591 p_lead_time,
592 p_name,
593 p_supplier_reference,
594 p_tsr_information_category,
595 p_tsr_information1,
596 p_tsr_information2,
597 p_tsr_information3,
598 p_tsr_information4,
599 p_tsr_information5,
600 p_tsr_information6,
601 p_tsr_information7,
602 p_tsr_information8,
603 p_tsr_information9,
604 p_tsr_information10,
605 p_tsr_information11,
606 p_tsr_information12,
607 p_tsr_information13,
608 p_tsr_information14,
609 p_tsr_information15,
610 p_tsr_information16,
611 p_tsr_information17,
612 p_tsr_information18,
613 p_tsr_information19,
614 p_tsr_information20,
615 p_training_center_id,
616 p_location_id,
617 p_trainer_id,
618 p_special_instruction
619 );
620 --
621 -- Having converted the arguments into the
622 -- plsql record structure we call the corresponding record
623 -- business process.
624 --
625 upd(l_rec, p_validate);
626 p_object_version_number := l_rec.object_version_number;
627 --
628 hr_utility.set_location(' Leaving:'||l_proc, 10);
629 End upd;
630 --
631 end ota_tsr_upd;
[Home] [Help]