[Home] [Help]
PACKAGE BODY: APPS.PQP_PVD_INS
Source
1 Package Body pqp_pvd_ins as
2 /* $Header: pqpvdrhi.pkb 115.6 2003/02/17 22:14:43 tmehra noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqp_pvd_ins.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------------< insert_dml >------------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 -- This procedure controls the actual dml insert logic. The processing of
17 -- this procedure are as follows:
18 -- 1) Initialise the object_version_number to 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 insert the row into the schema.
23 -- 4) To trap any constraint violations that may have occurred.
24 -- 5) To raise any other errors.
25 --
26 -- Prerequisites:
27 -- This is an internal private procedure which must be called from the ins
28 -- procedure and must have all mandatory attributes set (except the
29 -- object_version_number which is initialised within this procedure).
30 --
31 -- In Parameters:
32 -- A Pl/Sql record structre.
33 --
34 -- Post Success:
35 -- The specified row will be inserted into the schema.
36 --
37 -- Post Failure:
38 -- On the insert 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 -- None.
47 --
48 -- Access Status:
49 -- Internal Row Handler Use Only.
50 --
51 -- {End Of Comments}
52 -- ----------------------------------------------------------------------------
53 Procedure insert_dml
54 (p_rec in out nocopy pqp_pvd_shd.g_rec_type
55 ) is
56 --
57 l_proc varchar2(72) := g_package||'insert_dml';
58 --
59 Begin
60 hr_utility.set_location('Entering:'||l_proc, 5);
61 p_rec.object_version_number := 1; -- Initialise the object version
62 --
63 pqp_pvd_shd.g_api_dml := true; -- Set the api dml status
64 --
65 -- Insert the row into: pqp_vehicle_details
66 --
67 insert into pqp_vehicle_details
68 (vehicle_details_id
69 ,vehicle_type
70 ,business_group_id
71 ,registration_number
72 ,make
73 ,model
74 ,date_first_registered
75 ,engine_capacity_in_cc
76 ,fuel_type
77 ,fuel_card
78 ,currency_code
79 ,list_price
80 ,accessory_value_at_startdate
81 ,accessory_value_added_later
82 -- ,capital_contributions
83 -- ,private_use_contributions
84 ,market_value_classic_car
85 ,co2_emissions
86 ,vehicle_provider
87 ,object_version_number
88 ,vehicle_identification_number
89 ,vehicle_ownership
90 ,vhd_attribute_category
91 ,vhd_attribute1
92 ,vhd_attribute2
93 ,vhd_attribute3
94 ,vhd_attribute4
95 ,vhd_attribute5
96 ,vhd_attribute6
97 ,vhd_attribute7
98 ,vhd_attribute8
99 ,vhd_attribute9
100 ,vhd_attribute10
101 ,vhd_attribute11
102 ,vhd_attribute12
103 ,vhd_attribute13
104 ,vhd_attribute14
105 ,vhd_attribute15
106 ,vhd_attribute16
107 ,vhd_attribute17
111 ,vhd_information_category
108 ,vhd_attribute18
109 ,vhd_attribute19
110 ,vhd_attribute20
112 ,vhd_information1
113 ,vhd_information2
114 ,vhd_information3
115 ,vhd_information4
116 ,vhd_information5
117 ,vhd_information6
118 ,vhd_information7
119 ,vhd_information8
120 ,vhd_information9
121 ,vhd_information10
122 ,vhd_information11
123 ,vhd_information12
124 ,vhd_information13
125 ,vhd_information14
126 ,vhd_information15
127 ,vhd_information16
128 ,vhd_information17
129 ,vhd_information18
130 ,vhd_information19
131 ,vhd_information20
132 )
133 Values
134 (p_rec.vehicle_details_id
135 ,p_rec.vehicle_type
136 ,p_rec.business_group_id
137 ,p_rec.registration_number
138 ,p_rec.make
139 ,p_rec.model
140 ,p_rec.date_first_registered
141 ,p_rec.engine_capacity_in_cc
142 ,p_rec.fuel_type
143 ,p_rec.fuel_card
144 ,p_rec.currency_code
145 ,p_rec.list_price
146 ,p_rec.accessory_value_at_startdate
147 ,p_rec.accessory_value_added_later
148 -- ,p_rec.capital_contributions
149 -- ,p_rec.private_use_contributions
150 ,p_rec.market_value_classic_car
151 ,p_rec.co2_emissions
152 ,p_rec.vehicle_provider
153 ,p_rec.object_version_number
154 ,p_rec.vehicle_identification_number
155 ,p_rec.vehicle_ownership
156 ,p_rec.vhd_attribute_category
157 ,p_rec.vhd_attribute1
158 ,p_rec.vhd_attribute2
159 ,p_rec.vhd_attribute3
160 ,p_rec.vhd_attribute4
161 ,p_rec.vhd_attribute5
162 ,p_rec.vhd_attribute6
163 ,p_rec.vhd_attribute7
164 ,p_rec.vhd_attribute8
165 ,p_rec.vhd_attribute9
166 ,p_rec.vhd_attribute10
167 ,p_rec.vhd_attribute11
168 ,p_rec.vhd_attribute12
169 ,p_rec.vhd_attribute13
170 ,p_rec.vhd_attribute14
171 ,p_rec.vhd_attribute15
172 ,p_rec.vhd_attribute16
173 ,p_rec.vhd_attribute17
174 ,p_rec.vhd_attribute18
175 ,p_rec.vhd_attribute19
176 ,p_rec.vhd_attribute20
177 ,p_rec.vhd_information_category
178 ,p_rec.vhd_information1
179 ,p_rec.vhd_information2
180 ,p_rec.vhd_information3
181 ,p_rec.vhd_information4
182 ,p_rec.vhd_information5
183 ,p_rec.vhd_information6
184 ,p_rec.vhd_information7
185 ,p_rec.vhd_information8
186 ,p_rec.vhd_information9
187 ,p_rec.vhd_information10
188 ,p_rec.vhd_information11
189 ,p_rec.vhd_information12
190 ,p_rec.vhd_information13
191 ,p_rec.vhd_information14
192 ,p_rec.vhd_information15
193 ,p_rec.vhd_information16
194 ,p_rec.vhd_information17
195 ,p_rec.vhd_information18
196 ,p_rec.vhd_information19
197 ,p_rec.vhd_information20
198 );
199 --
200 pqp_pvd_shd.g_api_dml := false; -- Unset the api dml status
201 --
202 hr_utility.set_location(' Leaving:'||l_proc, 10);
203 Exception
204 When hr_api.check_integrity_violated Then
205 -- A check constraint has been violated
206 pqp_pvd_shd.g_api_dml := false; -- Unset the api dml status
207 pqp_pvd_shd.constraint_error
208 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
209 When hr_api.parent_integrity_violated Then
210 -- Parent integrity has been violated
211 pqp_pvd_shd.g_api_dml := false; -- Unset the api dml status
212 pqp_pvd_shd.constraint_error
213 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
214 When hr_api.unique_integrity_violated Then
215 -- Unique integrity has been violated
216 pqp_pvd_shd.g_api_dml := false; -- Unset the api dml status
217 pqp_pvd_shd.constraint_error
218 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
219 When Others Then
220 pqp_pvd_shd.g_api_dml := false; -- Unset the api dml status
221 Raise;
222 End insert_dml;
223 --
224 -- ----------------------------------------------------------------------------
225 -- |------------------------------< pre_insert >------------------------------|
226 -- ----------------------------------------------------------------------------
227 -- {Start Of Comments}
228 --
229 -- Description:
230 -- This private procedure contains any processing which is required before
231 -- the insert dml. Presently, if the entity has a corresponding primary
232 -- key which is maintained by an associating sequence, the primary key for
233 -- the entity will be populated with the next sequence value in
234 -- preparation for the insert dml.
235 --
236 -- Prerequisites:
237 -- This is an internal procedure which is called from the ins procedure.
238 --
239 -- In Parameters:
240 -- A Pl/Sql record structre.
241 --
242 -- Post Success:
243 -- Processing continues.
244 --
245 -- Post Failure:
246 -- If an error has occurred, an error message and exception will be raised
247 -- but not handled.
248 --
249 -- Developer Implementation Notes:
250 -- Any pre-processing required before the insert dml is issued should be
251 -- coded within this procedure. As stated above, a good example is the
252 -- generation of a primary key number via a corresponding sequence.
253 -- It is important to note that any 3rd party maintenance should be reviewed
254 -- before placing in this procedure.
255 --
256 -- Access Status:
257 -- Internal Row Handler Use Only.
258 --
259 -- {End Of Comments}
260 -- ----------------------------------------------------------------------------
261 Procedure pre_insert
262 (p_rec in out nocopy pqp_pvd_shd.g_rec_type
263 ) is
264 --
265 l_proc varchar2(72) := g_package||'pre_insert';
266 --
267 Cursor C_Sel1 is select pqp_vehicle_details_s.nextval from sys.dual;
268 --
269 Begin
270 hr_utility.set_location('Entering:'||l_proc, 5);
271 --
272 --
273 -- Select the next sequence number
274 --
275 Open C_Sel1;
276 Fetch C_Sel1 Into p_rec.vehicle_details_id;
277 Close C_Sel1;
278 --
279 hr_utility.set_location(' Leaving:'||l_proc, 10);
280 End pre_insert;
281 --
282 -- ----------------------------------------------------------------------------
283 -- |-----------------------------< post_insert >------------------------------|
284 -- ----------------------------------------------------------------------------
285 -- {Start Of Comments}
286 --
287 -- Description:
288 -- This private procedure contains any processing which is required after the
289 -- insert dml.
290 --
291 -- Prerequisites:
292 -- This is an internal procedure which is called from the ins procedure.
293 --
294 -- In Parameters:
295 -- A Pl/Sql record structre.
296 --
297 -- Post Success:
298 -- Processing continues.
299 --
300 -- Post Failure:
301 -- If an error has occurred, an error message and exception will be raised
302 -- but not handled.
303 --
304 -- Developer Implementation Notes:
305 -- Any post-processing required after the insert dml is issued should be
306 -- coded within this procedure. It is important to note that any 3rd party
307 -- maintenance should be reviewed before placing in this procedure.
308 --
309 -- Access Status:
310 -- Internal Row Handler Use Only.
311 --
312 -- {End Of Comments}
313 -- ----------------------------------------------------------------------------
314 Procedure post_insert
315 (p_effective_date in date
316 ,p_rec in pqp_pvd_shd.g_rec_type
317 ) is
318 --
319 l_proc varchar2(72) := g_package||'post_insert';
320 --
321 Begin
322 hr_utility.set_location('Entering:'||l_proc, 5);
323 begin
324 --
325 pqp_pvd_rki.after_insert
326 (p_effective_date => p_effective_date
327 ,p_vehicle_details_id
328 => p_rec.vehicle_details_id
329 ,p_vehicle_type
330 => p_rec.vehicle_type
331 ,p_business_group_id
332 => p_rec.business_group_id
333 ,p_registration_number
334 => p_rec.registration_number
335 ,p_make
336 => p_rec.make
337 ,p_model
338 => p_rec.model
339 ,p_date_first_registered
340 => p_rec.date_first_registered
341 ,p_engine_capacity_in_cc
342 => p_rec.engine_capacity_in_cc
343 ,p_fuel_type
344 => p_rec.fuel_type
345 ,p_fuel_card
346 => p_rec.fuel_card
347 ,p_currency_code
348 => p_rec.currency_code
349 ,p_list_price
350 => p_rec.list_price
351 ,p_accessory_value_at_startdate
352 => p_rec.accessory_value_at_startdate
353 ,p_accessory_value_added_later
354 => p_rec.accessory_value_added_later
355 -- ,p_capital_contributions
356 -- => p_rec.capital_contributions
357 -- ,p_private_use_contributions
358 -- => p_rec.private_use_contributions
359 ,p_market_value_classic_car
360 => p_rec.market_value_classic_car
361 ,p_co2_emissions
362 => p_rec.co2_emissions
363 ,p_vehicle_provider
364 => p_rec.vehicle_provider
365 ,p_object_version_number
366 => p_rec.object_version_number
367 ,p_vehicle_identification_numbe
368 => p_rec.vehicle_identification_number
369 ,p_vehicle_ownership
370 => p_rec.vehicle_ownership
371 ,p_vhd_attribute_category
372 => p_rec.vhd_attribute_category
373 ,p_vhd_attribute1
374 => p_rec.vhd_attribute1
375 ,p_vhd_attribute2
376 => p_rec.vhd_attribute2
377 ,p_vhd_attribute3
378 => p_rec.vhd_attribute3
379 ,p_vhd_attribute4
380 => p_rec.vhd_attribute4
381 ,p_vhd_attribute5
382 => p_rec.vhd_attribute5
383 ,p_vhd_attribute6
384 => p_rec.vhd_attribute6
385 ,p_vhd_attribute7
386 => p_rec.vhd_attribute7
387 ,p_vhd_attribute8
388 => p_rec.vhd_attribute8
392 => p_rec.vhd_attribute10
389 ,p_vhd_attribute9
390 => p_rec.vhd_attribute9
391 ,p_vhd_attribute10
393 ,p_vhd_attribute11
394 => p_rec.vhd_attribute11
395 ,p_vhd_attribute12
396 => p_rec.vhd_attribute12
397 ,p_vhd_attribute13
398 => p_rec.vhd_attribute13
399 ,p_vhd_attribute14
400 => p_rec.vhd_attribute14
401 ,p_vhd_attribute15
402 => p_rec.vhd_attribute15
403 ,p_vhd_attribute16
404 => p_rec.vhd_attribute16
405 ,p_vhd_attribute17
406 => p_rec.vhd_attribute17
407 ,p_vhd_attribute18
408 => p_rec.vhd_attribute18
409 ,p_vhd_attribute19
410 => p_rec.vhd_attribute19
411 ,p_vhd_attribute20
412 => p_rec.vhd_attribute20
413 ,p_vhd_information_category
414 => p_rec.vhd_information_category
415 ,p_vhd_information1
416 => p_rec.vhd_information1
417 ,p_vhd_information2
418 => p_rec.vhd_information2
419 ,p_vhd_information3
420 => p_rec.vhd_information3
421 ,p_vhd_information4
422 => p_rec.vhd_information4
423 ,p_vhd_information5
424 => p_rec.vhd_information5
425 ,p_vhd_information6
426 => p_rec.vhd_information6
427 ,p_vhd_information7
428 => p_rec.vhd_information7
429 ,p_vhd_information8
430 => p_rec.vhd_information8
431 ,p_vhd_information9
432 => p_rec.vhd_information9
433 ,p_vhd_information10
434 => p_rec.vhd_information10
435 ,p_vhd_information11
436 => p_rec.vhd_information11
437 ,p_vhd_information12
438 => p_rec.vhd_information12
439 ,p_vhd_information13
440 => p_rec.vhd_information13
441 ,p_vhd_information14
442 => p_rec.vhd_information14
443 ,p_vhd_information15
444 => p_rec.vhd_information15
445 ,p_vhd_information16
446 => p_rec.vhd_information16
447 ,p_vhd_information17
448 => p_rec.vhd_information17
449 ,p_vhd_information18
450 => p_rec.vhd_information18
451 ,p_vhd_information19
452 => p_rec.vhd_information19
453 ,p_vhd_information20
454 => p_rec.vhd_information20
455 );
456 --
457 exception
458 --
459 when hr_api.cannot_find_prog_unit then
460 --
461 hr_api.cannot_find_prog_unit_error
462 (p_module_name => 'PQP_VEHICLE_DETAILS'
463 ,p_hook_type => 'AI');
464 --
465 end;
466 --
467 hr_utility.set_location(' Leaving:'||l_proc, 10);
468 End post_insert;
469 --
470 -- ----------------------------------------------------------------------------
471 -- |---------------------------------< ins >----------------------------------|
472 -- ----------------------------------------------------------------------------
473 Procedure ins
474 (p_effective_date in date
475 ,p_rec in out nocopy pqp_pvd_shd.g_rec_type
476 ) is
477 --
478 l_proc varchar2(72) := g_package||'ins';
479 --
480 Begin
481 hr_utility.set_location('Entering:'||l_proc, 5);
482 --
483 -- Call the supporting insert validate operations
484 --
485 pqp_pvd_bus.insert_validate
486 (p_effective_date
487 ,p_rec
488 );
489 --
490 -- Call the supporting pre-insert operation
491 --
492 pqp_pvd_ins.pre_insert(p_rec);
493 --
494 -- Insert the row
495 --
496 pqp_pvd_ins.insert_dml(p_rec);
497 --
498 -- Call the supporting post-insert operation
499 --
500 pqp_pvd_ins.post_insert
501 (p_effective_date
502 ,p_rec
503 );
504 --
505 hr_utility.set_location('Leaving:'||l_proc, 20);
506 end ins;
507 --
508 -- ----------------------------------------------------------------------------
509 -- |---------------------------------< ins >----------------------------------|
510 -- ----------------------------------------------------------------------------
511 Procedure ins
512 (p_effective_date in date
513 ,p_vehicle_type in varchar2
514 ,p_registration_number in varchar2
515 ,p_make in varchar2
516 ,p_model in varchar2
517 ,p_date_first_registered in date
518 ,p_engine_capacity_in_cc in number
519 ,p_fuel_type in varchar2
520 ,p_fuel_card in varchar2
521 ,p_currency_code in varchar2
522 ,p_list_price in number
523 ,p_business_group_id in number default null
524 ,p_accessory_value_at_startdate in number default null
525 ,p_accessory_value_added_later in number default null
526 -- ,p_capital_contributions in number default null
527 -- ,p_private_use_contributions in number default null
528 ,p_market_value_classic_car in number default null
529 ,p_co2_emissions in number default null
530 ,p_vehicle_provider in varchar2 default null
531 ,p_vehicle_identification_numbe in varchar2 default null
532 ,p_vehicle_ownership in varchar2 default null
533 ,p_vhd_attribute_category in varchar2 default null
534 ,p_vhd_attribute1 in varchar2 default null
535 ,p_vhd_attribute2 in varchar2 default null
536 ,p_vhd_attribute3 in varchar2 default null
537 ,p_vhd_attribute4 in varchar2 default null
538 ,p_vhd_attribute5 in varchar2 default null
539 ,p_vhd_attribute6 in varchar2 default null
540 ,p_vhd_attribute7 in varchar2 default null
541 ,p_vhd_attribute8 in varchar2 default null
542 ,p_vhd_attribute9 in varchar2 default null
543 ,p_vhd_attribute10 in varchar2 default null
544 ,p_vhd_attribute11 in varchar2 default null
545 ,p_vhd_attribute12 in varchar2 default null
546 ,p_vhd_attribute13 in varchar2 default null
547 ,p_vhd_attribute14 in varchar2 default null
548 ,p_vhd_attribute15 in varchar2 default null
549 ,p_vhd_attribute16 in varchar2 default null
550 ,p_vhd_attribute17 in varchar2 default null
551 ,p_vhd_attribute18 in varchar2 default null
552 ,p_vhd_attribute19 in varchar2 default null
553 ,p_vhd_attribute20 in varchar2 default null
554 ,p_vhd_information_category in varchar2 default null
555 ,p_vhd_information1 in varchar2 default null
556 ,p_vhd_information2 in varchar2 default null
557 ,p_vhd_information3 in varchar2 default null
558 ,p_vhd_information4 in varchar2 default null
559 ,p_vhd_information5 in varchar2 default null
560 ,p_vhd_information6 in varchar2 default null
561 ,p_vhd_information7 in varchar2 default null
562 ,p_vhd_information8 in varchar2 default null
563 ,p_vhd_information9 in varchar2 default null
564 ,p_vhd_information10 in varchar2 default null
565 ,p_vhd_information11 in varchar2 default null
566 ,p_vhd_information12 in varchar2 default null
567 ,p_vhd_information13 in varchar2 default null
568 ,p_vhd_information14 in varchar2 default null
569 ,p_vhd_information15 in varchar2 default null
570 ,p_vhd_information16 in varchar2 default null
571 ,p_vhd_information17 in varchar2 default null
572 ,p_vhd_information18 in varchar2 default null
573 ,p_vhd_information19 in varchar2 default null
574 ,p_vhd_information20 in varchar2 default null
575 ,p_vehicle_details_id out nocopy number
576 ,p_object_version_number out nocopy number
577 ) is
578 --
579 l_rec pqp_pvd_shd.g_rec_type;
580 l_proc varchar2(72) := g_package||'ins';
581 --
582 Begin
583 hr_utility.set_location('Entering:'||l_proc, 5);
584 --
585 -- Call conversion function to turn arguments into the
586 -- p_rec structure.
587 --
588 l_rec :=
589 pqp_pvd_shd.convert_args
590 (null
591 ,p_vehicle_type
592 ,p_business_group_id
593 ,p_registration_number
594 ,p_make
595 ,p_model
596 ,p_date_first_registered
597 ,p_engine_capacity_in_cc
598 ,p_fuel_type
599 ,p_fuel_card
600 ,p_currency_code
601 ,p_list_price
602 ,p_accessory_value_at_startdate
603 ,p_accessory_value_added_later
604 -- ,p_capital_contributions
605 -- ,p_private_use_contributions
606 ,p_market_value_classic_car
607 ,p_co2_emissions
608 ,p_vehicle_provider
609 ,null
610 ,p_vehicle_identification_numbe
611 ,p_vehicle_ownership
612 ,p_vhd_attribute_category
613 ,p_vhd_attribute1
614 ,p_vhd_attribute2
615 ,p_vhd_attribute3
616 ,p_vhd_attribute4
617 ,p_vhd_attribute5
618 ,p_vhd_attribute6
619 ,p_vhd_attribute7
620 ,p_vhd_attribute8
621 ,p_vhd_attribute9
622 ,p_vhd_attribute10
623 ,p_vhd_attribute11
624 ,p_vhd_attribute12
625 ,p_vhd_attribute13
626 ,p_vhd_attribute14
627 ,p_vhd_attribute15
628 ,p_vhd_attribute16
629 ,p_vhd_attribute17
630 ,p_vhd_attribute18
631 ,p_vhd_attribute19
632 ,p_vhd_attribute20
633 ,p_vhd_information_category
634 ,p_vhd_information1
635 ,p_vhd_information2
636 ,p_vhd_information3
637 ,p_vhd_information4
638 ,p_vhd_information5
639 ,p_vhd_information6
640 ,p_vhd_information7
641 ,p_vhd_information8
642 ,p_vhd_information9
643 ,p_vhd_information10
644 ,p_vhd_information11
645 ,p_vhd_information12
646 ,p_vhd_information13
647 ,p_vhd_information14
648 ,p_vhd_information15
649 ,p_vhd_information16
650 ,p_vhd_information17
651 ,p_vhd_information18
652 ,p_vhd_information19
653 ,p_vhd_information20
654 );
655 --
656 -- Having converted the arguments into the pqp_pvd_rec
657 -- plsql record structure we call the corresponding record business process.
658 --
659 pqp_pvd_ins.ins
660 (p_effective_date
661 ,l_rec
662 );
663 --
664 -- As the primary key argument(s)
665 -- are specified as an OUT's we must set these values.
666 --
667 p_vehicle_details_id := l_rec.vehicle_details_id;
668 p_object_version_number := l_rec.object_version_number;
669 --
670 hr_utility.set_location(' Leaving:'||l_proc, 10);
671 End ins;
672 --
673 end pqp_pvd_ins;