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