1 Package Body ota_tpl_upd as
2 /* $Header: ottpl01t.pkb 115.2 99/07/16 00:55:57 porting ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_tpl_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 ota_tpl_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_tpl_shd.g_api_dml := true; -- Set the api dml status
66 --
67 -- Update the ota_price_lists Row
68 --
69 update ota_price_lists
70 set
71 price_list_id = p_rec.price_list_id,
72 business_group_id = p_rec.business_group_id,
73 currency_code = p_rec.currency_code,
74 default_flag = p_rec.default_flag,
75 name = p_rec.name,
76 object_version_number = p_rec.object_version_number,
77 price_list_type = p_rec.price_list_type,
78 start_date = p_rec.start_date,
79 comments = p_rec.comments,
80 description = p_rec.description,
81 end_date = p_rec.end_date,
82 single_unit_price = p_rec.single_unit_price,
83 training_unit_type = p_rec.training_unit_type,
84 tpl_information_category = p_rec.tpl_information_category,
85 tpl_information1 = p_rec.tpl_information1,
86 tpl_information2 = p_rec.tpl_information2,
87 tpl_information3 = p_rec.tpl_information3,
88 tpl_information4 = p_rec.tpl_information4,
89 tpl_information5 = p_rec.tpl_information5,
90 tpl_information6 = p_rec.tpl_information6,
91 tpl_information7 = p_rec.tpl_information7,
92 tpl_information8 = p_rec.tpl_information8,
93 tpl_information9 = p_rec.tpl_information9,
94 tpl_information10 = p_rec.tpl_information10,
95 tpl_information11 = p_rec.tpl_information11,
96 tpl_information12 = p_rec.tpl_information12,
97 tpl_information13 = p_rec.tpl_information13,
98 tpl_information14 = p_rec.tpl_information14,
99 tpl_information15 = p_rec.tpl_information15,
100 tpl_information16 = p_rec.tpl_information16,
101 tpl_information17 = p_rec.tpl_information17,
102 tpl_information18 = p_rec.tpl_information18,
103 tpl_information19 = p_rec.tpl_information19,
104 tpl_information20 = p_rec.tpl_information20
105 where price_list_id = p_rec.price_list_id;
106 --
107 ota_tpl_shd.g_api_dml := false; -- Unset the api dml status
108 --
109 hr_utility.set_location(' Leaving:'||l_proc, 10);
110 --
111 Exception
112 When hr_api.check_integrity_violated Then
113 -- A check constraint has been violated
114 ota_tpl_shd.g_api_dml := false; -- Unset the api dml status
115 ota_tpl_shd.constraint_error
116 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
117 When hr_api.parent_integrity_violated Then
118 -- Parent integrity has been violated
119 ota_tpl_shd.g_api_dml := false; -- Unset the api dml status
120 ota_tpl_shd.constraint_error
121 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
122 When hr_api.unique_integrity_violated Then
123 -- Unique integrity has been violated
124 ota_tpl_shd.g_api_dml := false; -- Unset the api dml status
125 ota_tpl_shd.constraint_error
126 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
127 When Others Then
128 ota_tpl_shd.g_api_dml := false; -- Unset the api dml status
129 Raise;
130 End update_dml;
131 --
132 -- ----------------------------------------------------------------------------
133 -- |------------------------------< pre_update >------------------------------|
134 -- ----------------------------------------------------------------------------
135 -- {Start Of Comments}
136 --
137 -- Description:
138 -- This private procedure contains any processing which is required before
139 -- the update dml.
140 --
141 -- Pre Conditions:
142 -- This is an internal procedure which is called from the upd procedure.
143 --
144 -- In Arguments:
145 -- A Pl/Sql record structre.
146 --
147 -- Post Success:
148 -- Processing continues.
149 --
150 -- Post Failure:
151 -- If an error has occurred, an error message and exception will be raised
152 -- but not handled.
153 --
154 -- Developer Implementation Notes:
155 -- Any pre-processing required before the update dml is issued should be
156 -- coded within this procedure. It is important to note that any 3rd party
157 -- maintenance should be reviewed before placing in this procedure.
158 --
159 -- Access Status:
160 -- Internal Development Use Only.
161 --
162 -- {End Of Comments}
163 -- ----------------------------------------------------------------------------
164 Procedure pre_update(p_rec in ota_tpl_shd.g_rec_type) is
165 --
166 l_proc varchar2(72) := g_package||'pre_update';
167 --
168 Begin
169 hr_utility.set_location('Entering:'||l_proc, 5);
170 --
171 hr_utility.set_location(' Leaving:'||l_proc, 10);
172 End pre_update;
173 --
174 -- ----------------------------------------------------------------------------
175 -- |-----------------------------< post_update >------------------------------|
176 -- ----------------------------------------------------------------------------
177 -- {Start Of Comments}
178 --
179 -- Description:
180 -- This private procedure contains any processing which is required after the
181 -- update dml.
182 --
183 -- Pre Conditions:
184 -- This is an internal procedure which is called from the upd procedure.
185 --
186 -- In Arguments:
187 -- A Pl/Sql record structre.
188 --
189 -- Post Success:
190 -- Processing continues.
191 --
192 -- Post Failure:
193 -- If an error has occurred, an error message and exception will be raised
194 -- but not handled.
195 --
196 -- Developer Implementation Notes:
197 -- Any post-processing required after the update dml is issued should be
198 -- coded within this procedure. It is important to note that any 3rd party
199 -- maintenance should be reviewed before placing in this procedure.
200 --
201 -- Access Status:
202 -- Internal Development Use Only.
203 --
204 -- {End Of Comments}
205 -- ----------------------------------------------------------------------------
206 Procedure post_update(p_rec in ota_tpl_shd.g_rec_type) is
207 --
208 l_proc varchar2(72) := g_package||'post_update';
209 --
210 Begin
211 hr_utility.set_location('Entering:'||l_proc, 5);
212 --
213 hr_utility.set_location(' Leaving:'||l_proc, 10);
214 End post_update;
215 --
216 -- ----------------------------------------------------------------------------
217 -- |-----------------------------< convert_defs >-----------------------------|
218 -- ----------------------------------------------------------------------------
219 -- {Start Of Comments}
220 --
221 -- Description:
222 -- The Convert_Defs function has one very important function:
223 -- It must return the record structure for the row with all system defaulted
224 -- values converted into its corresponding argument value for update. When
225 -- we attempt to update a row through the Upd business process , certain
226 -- arguments can be defaulted which enables flexibility in the calling of
227 -- the upd process (e.g. only attributes which need to be updated need to be
228 -- specified). For the upd business process to determine which attributes
229 -- have NOT been specified we need to check if the argument has a reserved
230 -- system default value. Therefore, for all attributes which have a
231 -- corresponding reserved system default mechanism specified we need to
232 -- check if a system default is being used. If a system default is being
233 -- used then we convert the defaulted value into its corresponding attribute
234 -- value held in the g_old_rec data structure.
235 --
236 -- Pre Conditions:
237 -- This private function can only be called from the upd process.
238 --
239 -- In Arguments:
240 -- A Pl/Sql record structre.
241 --
242 -- Post Success:
243 -- The record structure will be returned with all system defaulted argument
244 -- values converted into its current row attribute value.
245 --
246 -- Post Failure:
247 -- No direct error handling is required within this function. Any possible
248 -- errors within this function will be a PL/SQL value error due to conversion
249 -- of datatypes or data lengths.
250 --
251 -- Developer Implementation Notes:
252 -- None.
253 --
254 -- Access Status:
255 -- Internal Development Use Only.
256 --
257 -- {End Of Comments}
258 -- ----------------------------------------------------------------------------
259 Function convert_defs(p_rec in out ota_tpl_shd.g_rec_type)
260 Return ota_tpl_shd.g_rec_type is
261 --
262 l_proc varchar2(72) := g_package||'convert_defs';
263 --
264 Begin
265 --
266 hr_utility.set_location('Entering:'||l_proc, 5);
267 --
268 -- We must now examine each argument value in the
269 -- p_rec plsql record structure
270 -- to see if a system default is being used. If a system default
271 -- is being used then we must set to the 'current' argument value.
272 --
273 If (p_rec.business_group_id = hr_api.g_number) then
274 p_rec.business_group_id :=
275 ota_tpl_shd.g_old_rec.business_group_id;
276 End If;
277 If (p_rec.currency_code = hr_api.g_varchar2) then
278 p_rec.currency_code :=
279 ota_tpl_shd.g_old_rec.currency_code;
280 End If;
281 If (p_rec.default_flag = hr_api.g_varchar2) then
282 p_rec.default_flag :=
283 ota_tpl_shd.g_old_rec.default_flag;
284 End If;
285 If (p_rec.name = hr_api.g_varchar2) then
286 p_rec.name :=
287 ota_tpl_shd.g_old_rec.name;
288 End If;
289 If (p_rec.price_list_type = hr_api.g_varchar2) then
290 p_rec.price_list_type :=
291 ota_tpl_shd.g_old_rec.price_list_type;
292 End If;
293 If (p_rec.start_date = hr_api.g_date) then
294 p_rec.start_date :=
295 ota_tpl_shd.g_old_rec.start_date;
296 End If;
297 If (p_rec.comments = hr_api.g_varchar2) then
298 p_rec.comments :=
299 ota_tpl_shd.g_old_rec.comments;
300 End If;
301 If (p_rec.description = hr_api.g_varchar2) then
302 p_rec.description :=
303 ota_tpl_shd.g_old_rec.description;
304 End If;
305 If (p_rec.end_date = hr_api.g_date) then
306 p_rec.end_date :=
307 ota_tpl_shd.g_old_rec.end_date;
308 End If;
309 If (p_rec.single_unit_price = hr_api.g_number) then
310 p_rec.single_unit_price :=
311 ota_tpl_shd.g_old_rec.single_unit_price;
312 End If;
313 If (p_rec.training_unit_type = hr_api.g_varchar2) then
314 p_rec.training_unit_type :=
315 ota_tpl_shd.g_old_rec.training_unit_type;
316 End If;
317 If (p_rec.tpl_information_category = hr_api.g_varchar2) then
318 p_rec.tpl_information_category :=
319 ota_tpl_shd.g_old_rec.tpl_information_category;
320 End If;
321 If (p_rec.tpl_information1 = hr_api.g_varchar2) then
322 p_rec.tpl_information1 :=
323 ota_tpl_shd.g_old_rec.tpl_information1;
324 End If;
325 If (p_rec.tpl_information2 = hr_api.g_varchar2) then
326 p_rec.tpl_information2 :=
327 ota_tpl_shd.g_old_rec.tpl_information2;
328 End If;
329 If (p_rec.tpl_information3 = hr_api.g_varchar2) then
330 p_rec.tpl_information3 :=
331 ota_tpl_shd.g_old_rec.tpl_information3;
332 End If;
333 If (p_rec.tpl_information4 = hr_api.g_varchar2) then
334 p_rec.tpl_information4 :=
335 ota_tpl_shd.g_old_rec.tpl_information4;
336 End If;
337 If (p_rec.tpl_information5 = hr_api.g_varchar2) then
338 p_rec.tpl_information5 :=
339 ota_tpl_shd.g_old_rec.tpl_information5;
340 End If;
341 If (p_rec.tpl_information6 = hr_api.g_varchar2) then
342 p_rec.tpl_information6 :=
343 ota_tpl_shd.g_old_rec.tpl_information6;
344 End If;
345 If (p_rec.tpl_information7 = hr_api.g_varchar2) then
346 p_rec.tpl_information7 :=
347 ota_tpl_shd.g_old_rec.tpl_information7;
348 End If;
349 If (p_rec.tpl_information8 = hr_api.g_varchar2) then
350 p_rec.tpl_information8 :=
351 ota_tpl_shd.g_old_rec.tpl_information8;
352 End If;
353 If (p_rec.tpl_information9 = hr_api.g_varchar2) then
354 p_rec.tpl_information9 :=
355 ota_tpl_shd.g_old_rec.tpl_information9;
356 End If;
357 If (p_rec.tpl_information10 = hr_api.g_varchar2) then
358 p_rec.tpl_information10 :=
359 ota_tpl_shd.g_old_rec.tpl_information10;
360 End If;
361 If (p_rec.tpl_information11 = hr_api.g_varchar2) then
362 p_rec.tpl_information11 :=
363 ota_tpl_shd.g_old_rec.tpl_information11;
364 End If;
365 If (p_rec.tpl_information12 = hr_api.g_varchar2) then
366 p_rec.tpl_information12 :=
367 ota_tpl_shd.g_old_rec.tpl_information12;
368 End If;
369 If (p_rec.tpl_information13 = hr_api.g_varchar2) then
370 p_rec.tpl_information13 :=
371 ota_tpl_shd.g_old_rec.tpl_information13;
372 End If;
373 If (p_rec.tpl_information14 = hr_api.g_varchar2) then
374 p_rec.tpl_information14 :=
375 ota_tpl_shd.g_old_rec.tpl_information14;
376 End If;
377 If (p_rec.tpl_information15 = hr_api.g_varchar2) then
378 p_rec.tpl_information15 :=
379 ota_tpl_shd.g_old_rec.tpl_information15;
380 End If;
381 If (p_rec.tpl_information16 = hr_api.g_varchar2) then
382 p_rec.tpl_information16 :=
383 ota_tpl_shd.g_old_rec.tpl_information16;
384 End If;
385 If (p_rec.tpl_information17 = hr_api.g_varchar2) then
386 p_rec.tpl_information17 :=
387 ota_tpl_shd.g_old_rec.tpl_information17;
388 End If;
389 If (p_rec.tpl_information18 = hr_api.g_varchar2) then
390 p_rec.tpl_information18 :=
391 ota_tpl_shd.g_old_rec.tpl_information18;
392 End If;
393 If (p_rec.tpl_information19 = hr_api.g_varchar2) then
394 p_rec.tpl_information19 :=
395 ota_tpl_shd.g_old_rec.tpl_information19;
396 End If;
397 If (p_rec.tpl_information20 = hr_api.g_varchar2) then
398 p_rec.tpl_information20 :=
399 ota_tpl_shd.g_old_rec.tpl_information20;
400 End If;
401 --
402 -- Return the plsql record structure.
403 --
404 hr_utility.set_location(' Leaving:'||l_proc, 10);
405 Return(p_rec);
406 --
407 End convert_defs;
408 --
409 -- ----------------------------------------------------------------------------
410 -- |---------------------------------< upd >----------------------------------|
411 -- ----------------------------------------------------------------------------
412 Procedure upd
413 (
414 p_rec in out ota_tpl_shd.g_rec_type,
415 p_validate in boolean default false
416 ) is
417 --
418 l_proc varchar2(72) := g_package||'upd';
419 --
420 Begin
421 hr_utility.set_location('Entering:'||l_proc, 5);
422 --
423 -- Determine if the business process is to be validated.
424 --
425 If p_validate then
426 --
427 -- Issue the savepoint.
428 --
429 SAVEPOINT upd_ota_tpl;
430 End If;
431 --
432 -- We must lock the row which we need to update.
433 --
434 ota_tpl_shd.lck
435 (
436 p_rec.price_list_id,
437 p_rec.object_version_number
438 );
439 --
440 -- 1. During an update system defaults are used to determine if
441 -- arguments have been defaulted or not. We must therefore
442 -- derive the full record structure values to be updated.
443 --
444 -- 2. Call the supporting update validate operations.
445 --
446 ota_tpl_bus.update_validate(convert_defs(p_rec));
447 --
448 -- Call the supporting pre-update operation
449 --
450 pre_update(p_rec);
451 --
452 -- Update the row.
453 --
454 update_dml(p_rec);
455 --
456 -- Call the supporting post-update operation
457 --
458 post_update(p_rec);
459 --
460 -- If we are validating then raise the Validate_Enabled exception
461 --
462 If p_validate then
463 Raise HR_Api.Validate_Enabled;
464 End If;
465 --
466 hr_utility.set_location(' Leaving:'||l_proc, 10);
467 Exception
468 When HR_Api.Validate_Enabled Then
469 --
470 -- As the Validate_Enabled exception has been raised
471 -- we must rollback to the savepoint
472 --
473 ROLLBACK TO upd_ota_tpl;
474 End upd;
475 --
476 -- ----------------------------------------------------------------------------
477 -- |---------------------------------< upd >----------------------------------|
478 -- ----------------------------------------------------------------------------
479 Procedure upd
480 (
481 p_price_list_id in number,
482 p_business_group_id in number default hr_api.g_number,
483 p_currency_code in varchar2 default hr_api.g_varchar2,
484 p_default_flag in varchar2 default hr_api.g_varchar2,
485 p_name in varchar2 default hr_api.g_varchar2,
486 p_object_version_number in out number,
487 p_price_list_type in varchar2 default hr_api.g_varchar2,
488 p_start_date in date default hr_api.g_date,
489 p_comments in varchar2 default hr_api.g_varchar2,
490 p_description in varchar2 default hr_api.g_varchar2,
491 p_end_date in date default hr_api.g_date,
492 p_single_unit_price in number default hr_api.g_number,
493 p_training_unit_type in varchar2 default hr_api.g_varchar2,
494 p_tpl_information_category in varchar2 default hr_api.g_varchar2,
495 p_tpl_information1 in varchar2 default hr_api.g_varchar2,
496 p_tpl_information2 in varchar2 default hr_api.g_varchar2,
497 p_tpl_information3 in varchar2 default hr_api.g_varchar2,
498 p_tpl_information4 in varchar2 default hr_api.g_varchar2,
499 p_tpl_information5 in varchar2 default hr_api.g_varchar2,
500 p_tpl_information6 in varchar2 default hr_api.g_varchar2,
501 p_tpl_information7 in varchar2 default hr_api.g_varchar2,
502 p_tpl_information8 in varchar2 default hr_api.g_varchar2,
503 p_tpl_information9 in varchar2 default hr_api.g_varchar2,
504 p_tpl_information10 in varchar2 default hr_api.g_varchar2,
505 p_tpl_information11 in varchar2 default hr_api.g_varchar2,
506 p_tpl_information12 in varchar2 default hr_api.g_varchar2,
507 p_tpl_information13 in varchar2 default hr_api.g_varchar2,
508 p_tpl_information14 in varchar2 default hr_api.g_varchar2,
509 p_tpl_information15 in varchar2 default hr_api.g_varchar2,
510 p_tpl_information16 in varchar2 default hr_api.g_varchar2,
511 p_tpl_information17 in varchar2 default hr_api.g_varchar2,
512 p_tpl_information18 in varchar2 default hr_api.g_varchar2,
513 p_tpl_information19 in varchar2 default hr_api.g_varchar2,
514 p_tpl_information20 in varchar2 default hr_api.g_varchar2,
515 p_validate in boolean default false
516 ) is
517 --
518 l_rec ota_tpl_shd.g_rec_type;
519 l_proc varchar2(72) := g_package||'upd';
520 --
521 Begin
522 hr_utility.set_location('Entering:'||l_proc, 5);
523 --
524 -- Call conversion function to turn arguments into the
525 -- l_rec structure.
526 --
527 l_rec :=
528 ota_tpl_shd.convert_args
529 (
530 p_price_list_id,
531 p_business_group_id,
532 p_currency_code,
533 p_default_flag,
534 p_name,
535 p_object_version_number,
536 p_price_list_type,
537 p_start_date,
538 p_comments,
539 p_description,
540 p_end_date,
541 p_single_unit_price,
542 p_training_unit_type,
543 p_tpl_information_category,
544 p_tpl_information1,
545 p_tpl_information2,
546 p_tpl_information3,
547 p_tpl_information4,
548 p_tpl_information5,
549 p_tpl_information6,
550 p_tpl_information7,
551 p_tpl_information8,
552 p_tpl_information9,
553 p_tpl_information10,
554 p_tpl_information11,
555 p_tpl_information12,
556 p_tpl_information13,
557 p_tpl_information14,
558 p_tpl_information15,
559 p_tpl_information16,
560 p_tpl_information17,
561 p_tpl_information18,
562 p_tpl_information19,
563 p_tpl_information20
564 );
565 --
566 -- Having converted the arguments into the
567 -- plsql record structure we call the corresponding record
568 -- business process.
569 --
570 upd(l_rec, p_validate);
571 p_object_version_number := l_rec.object_version_number;
572 --
573 hr_utility.set_location(' Leaving:'||l_proc, 10);
574 End upd;
575 --
576 end ota_tpl_upd;
[Home] [Help]