[Home] [Help]
PACKAGE BODY: APPS.PQP_VEHICLE_REPOSITORY_API
Source
1 Package Body PQP_VEHICLE_REPOSITORY_API as
2 /* $Header: pqvreapi.pkb 120.0 2005/05/29 02:18:02 appldev noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' PQP_VEHICLE_REPOSITORY_API.';
7 l_currency_code pqp_vehicle_repository_f.currency_code%TYPE;
8
9 --
10 -- ----------------------------------------------------------------------------
11 -- |--------------------------< create_vehicle >--------------------------|
12 -- ----------------------------------------------------------------------------
13 --
14 procedure create_vehicle
15 (p_validate in boolean
16 ,p_effective_date in date
17 ,p_registration_number in varchar2
18 ,p_vehicle_type in varchar2
19 ,p_vehicle_id_number in varchar2
20 ,p_business_group_id in number
21 ,p_make in varchar2
22 ,p_engine_capacity_in_cc in number
23 ,p_fuel_type in varchar2
24 ,p_currency_code in varchar2
25 ,p_vehicle_status in varchar2
26 ,p_vehicle_inactivity_reason in varchar2
27 ,p_model in varchar2
28 ,p_initial_registration in date
29 ,p_last_registration_renew_date in date
30 ,p_list_price in number
31 ,p_accessory_value_at_startdate in number
32 ,p_accessory_value_added_later in number
33 ,p_market_value_classic_car in number
34 ,p_fiscal_ratings in number
35 ,p_fiscal_ratings_uom in varchar2
36 ,p_vehicle_provider in varchar2
37 ,p_vehicle_ownership in varchar2
38 ,p_shared_vehicle in varchar2
39 ,p_asset_number in varchar2
40 ,p_lease_contract_number in varchar2
41 ,p_lease_contract_expiry_date in date
42 ,p_taxation_method in varchar2
43 ,p_fleet_info in varchar2
44 ,p_fleet_transfer_date in date
45 ,p_color in varchar2
46 ,p_seating_capacity in number
47 ,p_weight in number
48 ,p_weight_uom in varchar2
49 ,p_model_year in number
50 ,p_insurance_number in varchar2
51 ,p_insurance_expiry_date in date
52 ,p_comments in varchar2
53 ,p_vre_attribute_category in varchar2
54 ,p_vre_attribute1 in varchar2
55 ,p_vre_attribute2 in varchar2
56 ,p_vre_attribute3 in varchar2
57 ,p_vre_attribute4 in varchar2
58 ,p_vre_attribute5 in varchar2
59 ,p_vre_attribute6 in varchar2
60 ,p_vre_attribute7 in varchar2
61 ,p_vre_attribute8 in varchar2
62 ,p_vre_attribute9 in varchar2
63 ,p_vre_attribute10 in varchar2
64 ,p_vre_attribute11 in varchar2
65 ,p_vre_attribute12 in varchar2
66 ,p_vre_attribute13 in varchar2
67 ,p_vre_attribute14 in varchar2
68 ,p_vre_attribute15 in varchar2
69 ,p_vre_attribute16 in varchar2
70 ,p_vre_attribute17 in varchar2
71 ,p_vre_attribute18 in varchar2
72 ,p_vre_attribute19 in varchar2
73 ,p_vre_attribute20 in varchar2
74 ,p_vre_information_category in varchar2
75 ,p_vre_information1 in varchar2
76 ,p_vre_information2 in varchar2
77 ,p_vre_information3 in varchar2
78 ,p_vre_information4 in varchar2
79 ,p_vre_information5 in varchar2
80 ,p_vre_information6 in varchar2
81 ,p_vre_information7 in varchar2
82 ,p_vre_information8 in varchar2
83 ,p_vre_information9 in varchar2
84 ,p_vre_information10 in varchar2
85 ,p_vre_information11 in varchar2
86 ,p_vre_information12 in varchar2
87 ,p_vre_information13 in varchar2
88 ,p_vre_information14 in varchar2
89 ,p_vre_information15 in varchar2
90 ,p_vre_information16 in varchar2
91 ,p_vre_information17 in varchar2
92 ,p_vre_information18 in varchar2
93 ,p_vre_information19 in varchar2
94 ,p_vre_information20 in varchar2
95 ,p_vehicle_repository_id out NOCOPY number
96 ,p_object_version_number out NOCOPY number
97 ,p_effective_start_date out NOCOPY date
98 ,p_effective_end_date out NOCOPY date
99 )
100 is
101 --
102 -- Declare cursors and local variables
103 --
104
105 l_proc varchar2(72) := g_package||'create_vehicle';
106 l_message varchar2(2500) ;
107 l_effective_date date;
108
109 begin
110
111 --used to get the currency code
112 l_currency_code :=hr_general.DEFAULT_CURRENCY_CODE(p_business_group_id);
113
114 hr_utility.set_location('Entering:'|| l_proc, 10);
115
116 --Truncate the incomming date parameter
117 l_effective_date:=TRUNC(p_effective_date);
118 --
119 -- Issue a savepoint
120 --
121 savepoint create_vehicle;
122 hr_utility.set_location(l_proc, 20);
123 --
124 -- Call Before Process User Hook
125 --
126 begin
127 pqp_vehicle_repository_bk1.create_vehicle_b
128 (p_effective_date =>l_effective_date
129 ,p_registration_number =>p_registration_number
130 ,p_vehicle_type =>p_vehicle_type
131 ,p_vehicle_id_number =>p_vehicle_id_number
132 ,p_business_group_id =>p_business_group_id
133 ,p_make =>p_make
134 ,p_engine_capacity_in_cc =>p_engine_capacity_in_cc
135 ,p_fuel_type =>p_fuel_type
136 ,p_currency_code =>l_currency_code
137 ,p_vehicle_status =>p_vehicle_status
138 ,p_vehicle_inactivity_reason =>p_vehicle_inactivity_reason
139 ,p_model =>p_model
140 ,p_initial_registration =>p_initial_registration
141 ,p_last_registration_renew_date =>p_last_registration_renew_date
142 ,p_list_price =>p_list_price
143 ,p_accessory_value_at_startdate =>p_accessory_value_at_startdate
144 ,p_accessory_value_added_later =>p_accessory_value_added_later
145 ,p_market_value_classic_car =>p_market_value_classic_car
146 ,p_fiscal_ratings =>p_fiscal_ratings
147 ,p_fiscal_ratings_uom =>p_fiscal_ratings_uom
148 ,p_vehicle_provider =>p_vehicle_provider
149 ,p_vehicle_ownership =>p_vehicle_ownership
150 ,p_shared_vehicle =>p_shared_vehicle
151 ,p_asset_number =>p_asset_number
152 ,p_lease_contract_number =>p_lease_contract_number
153 ,p_lease_contract_expiry_date =>p_lease_contract_expiry_date
154 ,p_taxation_method =>p_taxation_method
155 ,p_fleet_info =>p_fleet_info
156 ,p_fleet_transfer_date =>p_fleet_transfer_date
157 ,p_color =>p_color
158 ,p_seating_capacity =>p_seating_capacity
159 ,p_weight =>p_weight
160 ,p_weight_uom =>p_weight_uom
161 ,p_model_year =>p_model_year
162 ,p_insurance_number =>p_insurance_number
163 ,p_insurance_expiry_date =>p_insurance_expiry_date
164 ,p_comments =>p_comments
165 ,p_vre_attribute_category =>p_vre_attribute_category
166 ,p_vre_attribute1 =>p_vre_attribute1
167 ,p_vre_attribute2 =>p_vre_attribute2
168 ,p_vre_attribute3 =>p_vre_attribute3
169 ,p_vre_attribute4 =>p_vre_attribute4
170 ,p_vre_attribute5 =>p_vre_attribute5
171 ,p_vre_attribute6 =>p_vre_attribute6
172 ,p_vre_attribute7 =>p_vre_attribute7
173 ,p_vre_attribute8 =>p_vre_attribute8
174 ,p_vre_attribute9 =>p_vre_attribute9
175 ,p_vre_attribute10 =>p_vre_attribute10
176 ,p_vre_attribute11 =>p_vre_attribute11
177 ,p_vre_attribute12 =>p_vre_attribute12
178 ,p_vre_attribute13 =>p_vre_attribute13
179 ,p_vre_attribute14 =>p_vre_attribute14
180 ,p_vre_attribute15 =>p_vre_attribute15
181 ,p_vre_attribute16 =>p_vre_attribute16
182 ,p_vre_attribute17 =>p_vre_attribute17
183 ,p_vre_attribute18 =>p_vre_attribute18
184 ,p_vre_attribute19 =>p_vre_attribute19
185 ,p_vre_attribute20 =>p_vre_attribute20
186 ,p_vre_information_category =>p_vre_information_category
187 ,p_vre_information1 =>p_vre_information1
188 ,p_vre_information2 =>p_vre_information2
189 ,p_vre_information3 =>p_vre_information3
190 ,p_vre_information4 =>p_vre_information4
191 ,p_vre_information5 =>p_vre_information5
192 ,p_vre_information6 =>p_vre_information6
193 ,p_vre_information7 =>p_vre_information7
194 ,p_vre_information8 =>p_vre_information8
195 ,p_vre_information9 =>p_vre_information9
196 ,p_vre_information10 =>p_vre_information10
197 ,p_vre_information11 =>p_vre_information11
198 ,p_vre_information12 =>p_vre_information12
199 ,p_vre_information13 =>p_vre_information13
200 ,p_vre_information14 =>p_vre_information14
201 ,p_vre_information15 =>p_vre_information15
202 ,p_vre_information16 =>p_vre_information16
203 ,p_vre_information17 =>p_vre_information17
204 ,p_vre_information18 =>p_vre_information18
205 ,p_vre_information19 =>p_vre_information19
206 ,p_vre_information20 =>p_vre_information20
207 ) ;
208
209 exception
210 when hr_api.cannot_find_prog_unit then
211 hr_api.cannot_find_prog_unit_error
212 (p_module_name => 'PQP_VEHICLE_REPOSITORY_API'
213 ,p_hook_type => 'BP'
214 );
215 end;
216 --
217 -- Validation in addition to Row Handlers
218 --
219
220 --
221 -- Process Logic
222 --
223 pqp_vre_ins.ins
224 (p_effective_date =>l_effective_date
225 ,p_registration_number =>p_registration_number
226 ,p_vehicle_type =>p_vehicle_type
227 ,p_vehicle_id_number =>p_vehicle_id_number
228 ,p_business_group_id =>p_business_group_id
229 ,p_make =>p_make
230 ,p_engine_capacity_in_cc =>p_engine_capacity_in_cc
231 ,p_fuel_type =>p_fuel_type
232 ,p_currency_code =>l_currency_code
233 ,p_vehicle_status =>p_vehicle_status
234 ,p_vehicle_inactivity_reason =>p_vehicle_inactivity_reason
235 ,p_model =>p_model
236 ,p_initial_registration =>p_initial_registration
237 ,p_last_registration_renew_date =>p_last_registration_renew_date
238 ,p_list_price =>p_list_price
239 ,p_accessory_value_at_startdate =>p_accessory_value_at_startdate
240 ,p_accessory_value_added_later =>p_accessory_value_added_later
241 ,p_market_value_classic_car =>p_market_value_classic_car
242 ,p_fiscal_ratings =>p_fiscal_ratings
243 ,p_fiscal_ratings_uom =>p_fiscal_ratings_uom
244 ,p_vehicle_provider =>p_vehicle_provider
245 ,p_vehicle_ownership =>p_vehicle_ownership
246 ,p_shared_vehicle =>p_shared_vehicle
247 ,p_asset_number =>p_asset_number
248 ,p_lease_contract_number =>p_lease_contract_number
249 ,p_lease_contract_expiry_date =>p_lease_contract_expiry_date
250 ,p_taxation_method =>p_taxation_method
251 ,p_fleet_info =>p_fleet_info
252 ,p_fleet_transfer_date =>p_fleet_transfer_date
253 ,p_color =>p_color
254 ,p_seating_capacity =>p_seating_capacity
255 ,p_weight =>p_weight
256 ,p_weight_uom =>p_weight_uom
257 ,p_model_year =>p_model_year
258 ,p_insurance_number =>p_insurance_number
259 ,p_insurance_expiry_date =>p_insurance_expiry_date
260 ,p_comments =>p_comments
261 ,p_vre_attribute_category =>p_vre_attribute_category
262 ,p_vre_attribute1 =>p_vre_attribute1
263 ,p_vre_attribute2 =>p_vre_attribute2
264 ,p_vre_attribute3 =>p_vre_attribute3
265 ,p_vre_attribute4 =>p_vre_attribute4
266 ,p_vre_attribute5 =>p_vre_attribute5
267 ,p_vre_attribute6 =>p_vre_attribute6
268 ,p_vre_attribute7 =>p_vre_attribute7
269 ,p_vre_attribute8 =>p_vre_attribute8
270 ,p_vre_attribute9 =>p_vre_attribute9
271 ,p_vre_attribute10 =>p_vre_attribute10
272 ,p_vre_attribute11 =>p_vre_attribute11
273 ,p_vre_attribute12 =>p_vre_attribute12
274 ,p_vre_attribute13 =>p_vre_attribute13
275 ,p_vre_attribute14 =>p_vre_attribute14
276 ,p_vre_attribute15 =>p_vre_attribute15
277 ,p_vre_attribute16 =>p_vre_attribute16
278 ,p_vre_attribute17 =>p_vre_attribute17
279 ,p_vre_attribute18 =>p_vre_attribute18
280 ,p_vre_attribute19 =>p_vre_attribute19
281 ,p_vre_attribute20 =>p_vre_attribute20
282 ,p_vre_information_category =>p_vre_information_category
283 ,p_vre_information1 =>p_vre_information1
284 ,p_vre_information2 =>p_vre_information2
285 ,p_vre_information3 =>p_vre_information3
286 ,p_vre_information4 =>p_vre_information4
287 ,p_vre_information5 =>p_vre_information5
288 ,p_vre_information6 =>p_vre_information6
289 ,p_vre_information7 =>p_vre_information7
290 ,p_vre_information8 =>p_vre_information8
291 ,p_vre_information9 =>p_vre_information9
292 ,p_vre_information10 =>p_vre_information10
293 ,p_vre_information11 =>p_vre_information11
294 ,p_vre_information12 =>p_vre_information12
295 ,p_vre_information13 =>p_vre_information13
296 ,p_vre_information14 =>p_vre_information14
297 ,p_vre_information15 =>p_vre_information15
298 ,p_vre_information16 =>p_vre_information16
299 ,p_vre_information17 =>p_vre_information17
300 ,p_vre_information18 =>p_vre_information18
304 ,p_object_version_number =>p_object_version_number
301 ,p_vre_information19 =>p_vre_information19
302 ,p_vre_information20 =>p_vre_information20
303 ,p_vehicle_repository_id =>p_vehicle_repository_id
305 ,p_effective_start_date =>p_effective_start_date
306 ,p_effective_end_date =>p_effective_end_date
307
308 ) ;
309
310
311 --
312 -- Call After Process User Hook
313 --
314 begin
315 pqp_vehicle_repository_bk1.create_vehicle_a
316 ( p_effective_date =>l_effective_date
317 ,p_registration_number =>p_registration_number
318 ,p_vehicle_type =>p_vehicle_type
319 ,p_vehicle_id_number =>p_vehicle_id_number
320 ,p_business_group_id =>p_business_group_id
321 ,p_make =>p_make
322 ,p_engine_capacity_in_cc =>p_engine_capacity_in_cc
323 ,p_fuel_type =>p_fuel_type
324 ,p_currency_code =>l_currency_code
325 ,p_vehicle_status =>p_vehicle_status
326 ,p_vehicle_inactivity_reason =>p_vehicle_inactivity_reason
327 ,p_model =>p_model
328 ,p_initial_registration =>p_initial_registration
329 ,p_last_registration_renew_date =>p_last_registration_renew_date
330 ,p_list_price =>p_list_price
331 ,p_accessory_value_at_startdate =>p_accessory_value_at_startdate
332 ,p_accessory_value_added_later =>p_accessory_value_added_later
333 ,p_market_value_classic_car =>p_market_value_classic_car
334 ,p_fiscal_ratings =>p_fiscal_ratings
335 ,p_fiscal_ratings_uom =>p_fiscal_ratings_uom
336 ,p_vehicle_provider =>p_vehicle_provider
337 ,p_vehicle_ownership =>p_vehicle_ownership
338 ,p_shared_vehicle =>p_shared_vehicle
339 ,p_asset_number =>p_asset_number
340 ,p_lease_contract_number =>p_lease_contract_number
341 ,p_lease_contract_expiry_date =>p_lease_contract_expiry_date
342 ,p_taxation_method =>p_taxation_method
343 ,p_fleet_info =>p_fleet_info
344 ,p_fleet_transfer_date =>p_fleet_transfer_date
345 ,p_color =>p_color
346 ,p_seating_capacity =>p_seating_capacity
347 ,p_weight =>p_weight
348 ,p_weight_uom =>p_weight_uom
349 ,p_model_year =>p_model_year
350 ,p_insurance_number =>p_insurance_number
351 ,p_insurance_expiry_date =>p_insurance_expiry_date
352 ,p_comments =>p_comments
353 ,p_vre_attribute_category =>p_vre_attribute_category
354 ,p_vre_attribute1 =>p_vre_attribute1
355 ,p_vre_attribute2 =>p_vre_attribute2
356 ,p_vre_attribute3 =>p_vre_attribute3
357 ,p_vre_attribute4 =>p_vre_attribute4
358 ,p_vre_attribute5 =>p_vre_attribute5
359 ,p_vre_attribute6 =>p_vre_attribute6
360 ,p_vre_attribute7 =>p_vre_attribute7
361 ,p_vre_attribute8 =>p_vre_attribute8
362 ,p_vre_attribute9 =>p_vre_attribute9
363 ,p_vre_attribute10 =>p_vre_attribute10
364 ,p_vre_attribute11 =>p_vre_attribute11
365 ,p_vre_attribute12 =>p_vre_attribute12
366 ,p_vre_attribute13 =>p_vre_attribute13
367 ,p_vre_attribute14 =>p_vre_attribute14
368 ,p_vre_attribute15 =>p_vre_attribute15
369 ,p_vre_attribute16 =>p_vre_attribute16
370 ,p_vre_attribute17 =>p_vre_attribute17
371 ,p_vre_attribute18 =>p_vre_attribute18
372 ,p_vre_attribute19 =>p_vre_attribute19
373 ,p_vre_attribute20 =>p_vre_attribute20
374 ,p_vre_information_category =>p_vre_information_category
375 ,p_vre_information1 =>p_vre_information1
376 ,p_vre_information2 =>p_vre_information2
377 ,p_vre_information3 =>p_vre_information3
378 ,p_vre_information4 =>p_vre_information4
379 ,p_vre_information5 =>p_vre_information5
380 ,p_vre_information6 =>p_vre_information6
381 ,p_vre_information7 =>p_vre_information7
382 ,p_vre_information8 =>p_vre_information8
383 ,p_vre_information9 =>p_vre_information9
384 ,p_vre_information10 =>p_vre_information10
385 ,p_vre_information11 =>p_vre_information11
386 ,p_vre_information12 =>p_vre_information12
387 ,p_vre_information13 =>p_vre_information13
388 ,p_vre_information14 =>p_vre_information14
389 ,p_vre_information15 =>p_vre_information15
390 ,p_vre_information16 =>p_vre_information16
391 ,p_vre_information17 =>p_vre_information17
392 ,p_vre_information18 =>p_vre_information18
393 ,p_vre_information19 =>p_vre_information19
394 ,p_vre_information20 =>p_vre_information20
395 ,p_vehicle_repository_id =>p_vehicle_repository_id
396 ,p_object_version_number =>p_object_version_number
397 ,p_effective_start_date =>p_effective_start_date
398 ,p_effective_end_date =>p_effective_end_date
399 );
400
401
402 exception
403 when hr_api.cannot_find_prog_unit then
404 hr_api.cannot_find_prog_unit_error
405 (p_module_name => 'PQP_VEHICLE_REPOSITORY_API'
409 --
406 ,p_hook_type => 'AP'
407 );
408 end;
410 -- When in validation only mode raise the Validate_Enabled exception
411 --
412 if p_validate then
413 raise hr_api.validate_enabled;
414 end if;
415 --
416 -- Set all output arguments
417 --
418 p_vehicle_repository_id := p_vehicle_repository_id;
419 p_object_version_number := p_object_version_number ;
420 p_effective_start_date := p_effective_start_date ;
421 p_effective_end_date := p_effective_end_date ;
422
423 hr_utility.set_location(' Leaving:'||l_proc, 70);
424
425 exception
426 when hr_api.validate_enabled then
427 --
428 -- As the Validate_Enabled exception has been raised
429 -- we must rollback to the savepoint
430 --
431 rollback to create_vehicle;
432 --
433 -- Only set output warning arguments
434 -- (Any key or derived arguments must be set to null
435 -- when validation only mode is being used.)
436 --
437 p_vehicle_repository_id := null;
438 p_object_version_number := null;
439 p_effective_start_date := null;
440 p_effective_end_date := null;
441 hr_utility.set_location(' Leaving:'||l_proc, 80);
442 when others then
443 --
444 -- A validation or unexpected error has occured
445 --
446 rollback to create_vehicle;
447 p_vehicle_repository_id := null;
448 p_object_version_number := null;
449 p_effective_start_date := null;
450 p_effective_end_date := null;
451 hr_utility.set_location(' Leaving:'||l_proc, 90);
452 raise;
453 end create_vehicle ;
454
455
456 -- ----------------------------------------------------------------------------
457 -- --------------------------< update_vehicle >------------------------
458 -- ----------------------------------------------------------------------------
459 --
460
461
462 procedure update_vehicle
463 (p_validate in boolean
464 ,p_effective_date in date
465 ,p_datetrack_mode in varchar2
466 ,p_vehicle_repository_id in number
467 ,p_object_version_number in out NOCOPY number
468 ,p_registration_number in varchar2
469 ,p_vehicle_type in varchar2
470 ,p_vehicle_id_number in varchar2
471 ,p_business_group_id in number
472 ,p_make in varchar2
473 ,p_engine_capacity_in_cc in number
474 ,p_fuel_type in varchar2
475 ,p_currency_code in varchar2
476 ,p_vehicle_status in varchar2
477 ,p_vehicle_inactivity_reason in varchar2
478 ,p_model in varchar2
479 ,p_initial_registration in date
480 ,p_last_registration_renew_date in date
481 ,p_list_price in number
482 ,p_accessory_value_at_startdate in number
483 ,p_accessory_value_added_later in number
484 ,p_market_value_classic_car in number
485 ,p_fiscal_ratings in number
486 ,p_fiscal_ratings_uom in varchar2
487 ,p_vehicle_provider in varchar2
488 ,p_vehicle_ownership in varchar2
489 ,p_shared_vehicle in varchar2
490 ,p_asset_number in varchar2
491 ,p_lease_contract_number in varchar2
492 ,p_lease_contract_expiry_date in date
493 ,p_taxation_method in varchar2
494 ,p_fleet_info in varchar2
495 ,p_fleet_transfer_date in date
496 ,p_color in varchar2
497 ,p_seating_capacity in number
498 ,p_weight in number
499 ,p_weight_uom in varchar2
500 ,p_model_year in number
501 ,p_insurance_number in varchar2
502 ,p_insurance_expiry_date in date
503 ,p_comments in varchar2
504 ,p_vre_attribute_category in varchar2
505 ,p_vre_attribute1 in varchar2
506 ,p_vre_attribute2 in varchar2
507 ,p_vre_attribute3 in varchar2
508 ,p_vre_attribute4 in varchar2
509 ,p_vre_attribute5 in varchar2
510 ,p_vre_attribute6 in varchar2
511 ,p_vre_attribute7 in varchar2
512 ,p_vre_attribute8 in varchar2
513 ,p_vre_attribute9 in varchar2
514 ,p_vre_attribute10 in varchar2
515 ,p_vre_attribute11 in varchar2
516 ,p_vre_attribute12 in varchar2
517 ,p_vre_attribute13 in varchar2
518 ,p_vre_attribute14 in varchar2
519 ,p_vre_attribute15 in varchar2
520 ,p_vre_attribute16 in varchar2
521 ,p_vre_attribute17 in varchar2
522 ,p_vre_attribute18 in varchar2
523 ,p_vre_attribute19 in varchar2
524 ,p_vre_attribute20 in varchar2
525 ,p_vre_information_category in varchar2
526 ,p_vre_information1 in varchar2
527 ,p_vre_information2 in varchar2
531 ,p_vre_information6 in varchar2
528 ,p_vre_information3 in varchar2
529 ,p_vre_information4 in varchar2
530 ,p_vre_information5 in varchar2
532 ,p_vre_information7 in varchar2
533 ,p_vre_information8 in varchar2
534 ,p_vre_information9 in varchar2
535 ,p_vre_information10 in varchar2
536 ,p_vre_information11 in varchar2
537 ,p_vre_information12 in varchar2
538 ,p_vre_information13 in varchar2
539 ,p_vre_information14 in varchar2
540 ,p_vre_information15 in varchar2
541 ,p_vre_information16 in varchar2
542 ,p_vre_information17 in varchar2
543 ,p_vre_information18 in varchar2
544 ,p_vre_information19 in varchar2
545 ,p_vre_information20 in varchar2
546 ,p_effective_start_date out NOCOPY date
547 ,p_effective_end_date out NOCOPY date
548 )
549
550 IS
551 l_proc varchar2(72) := g_package||'update_vehicle';
552 l_message varchar2(2500) ;
553 l_effective_date date;
554 BEGIN
555
556 --used to get the currency code
557 l_currency_code :=hr_general.DEFAULT_CURRENCY_CODE(p_business_group_id);
558
559 --
560 -- Issue a savepoint
561 --
562 --truncate date parameter
563 l_effective_date :=TRUNC(p_effective_date) ;
564 savepoint update_vehicle;
565
566 hr_utility.set_location(l_proc, 20);
567
568 --
569 -- Call Before Process User Hook
570 --
571 begin
572 PQP_VEHICLE_REPOSITORY_BK2.update_vehicle_b
573 ( p_effective_date =>l_effective_date
574 ,p_datetrack_mode =>p_datetrack_mode
575 ,p_vehicle_repository_id =>p_vehicle_repository_id
576 ,p_object_version_number =>p_object_version_number
577 ,p_registration_number =>p_registration_number
578 ,p_vehicle_type =>p_vehicle_type
579 ,p_vehicle_id_number =>p_vehicle_id_number
580 ,p_business_group_id =>p_business_group_id
581 ,p_make =>p_make
582 ,p_engine_capacity_in_cc =>p_engine_capacity_in_cc
583 ,p_fuel_type =>p_fuel_type
584 ,p_currency_code =>l_currency_code
585 ,p_vehicle_status =>p_vehicle_status
586 ,p_vehicle_inactivity_reason =>p_vehicle_inactivity_reason
587 ,p_model =>p_model
588 ,p_initial_registration =>p_initial_registration
589 ,p_last_registration_renew_date =>p_last_registration_renew_date
590 ,p_list_price =>p_list_price
591 ,p_accessory_value_at_startdate =>p_accessory_value_at_startdate
592 ,p_accessory_value_added_later =>p_accessory_value_added_later
593 ,p_market_value_classic_car =>p_market_value_classic_car
594 ,p_fiscal_ratings =>p_fiscal_ratings
595 ,p_fiscal_ratings_uom =>p_fiscal_ratings_uom
596 ,p_vehicle_provider =>p_vehicle_provider
597 ,p_vehicle_ownership =>p_vehicle_ownership
598 ,p_shared_vehicle =>p_shared_vehicle
599 ,p_asset_number =>p_asset_number
600 ,p_lease_contract_number =>p_lease_contract_number
601 ,p_lease_contract_expiry_date =>p_lease_contract_expiry_date
602 ,p_taxation_method =>p_taxation_method
603 ,p_fleet_info =>p_fleet_info
604 ,p_fleet_transfer_date =>p_fleet_transfer_date
605 ,p_color =>p_color
606 ,p_seating_capacity =>p_seating_capacity
607 ,p_weight =>p_weight
608 ,p_weight_uom =>p_weight_uom
609 ,p_model_year =>p_model_year
610 ,p_insurance_number =>p_insurance_number
611 ,p_insurance_expiry_date =>p_insurance_expiry_date
612 ,p_comments =>p_comments
613 ,p_vre_attribute_category =>p_vre_attribute_category
614 ,p_vre_attribute1 =>p_vre_attribute1
615 ,p_vre_attribute2 =>p_vre_attribute2
616 ,p_vre_attribute3 =>p_vre_attribute3
617 ,p_vre_attribute4 =>p_vre_attribute4
618 ,p_vre_attribute5 =>p_vre_attribute5
619 ,p_vre_attribute6 =>p_vre_attribute6
620 ,p_vre_attribute7 =>p_vre_attribute7
621 ,p_vre_attribute8 =>p_vre_attribute8
622 ,p_vre_attribute9 =>p_vre_attribute9
623 ,p_vre_attribute10 =>p_vre_attribute10
624 ,p_vre_attribute11 =>p_vre_attribute11
625 ,p_vre_attribute12 =>p_vre_attribute12
626 ,p_vre_attribute13 =>p_vre_attribute13
627 ,p_vre_attribute14 =>p_vre_attribute14
628 ,p_vre_attribute15 =>p_vre_attribute15
629 ,p_vre_attribute16 =>p_vre_attribute16
630 ,p_vre_attribute17 =>p_vre_attribute17
631 ,p_vre_attribute18 =>p_vre_attribute18
632 ,p_vre_attribute19 =>p_vre_attribute19
633 ,p_vre_attribute20 =>p_vre_attribute20
634 ,p_vre_information_category =>p_vre_information_category
635 ,p_vre_information1 =>p_vre_information1
639 ,p_vre_information5 =>p_vre_information5
636 ,p_vre_information2 =>p_vre_information2
637 ,p_vre_information3 =>p_vre_information3
638 ,p_vre_information4 =>p_vre_information4
640 ,p_vre_information6 =>p_vre_information6
641 ,p_vre_information7 =>p_vre_information7
642 ,p_vre_information8 =>p_vre_information8
643 ,p_vre_information9 =>p_vre_information9
644 ,p_vre_information10 =>p_vre_information10
645 ,p_vre_information11 =>p_vre_information11
646 ,p_vre_information12 =>p_vre_information12
647 ,p_vre_information13 =>p_vre_information13
648 ,p_vre_information14 =>p_vre_information14
649 ,p_vre_information15 =>p_vre_information15
650 ,p_vre_information16 =>p_vre_information16
651 ,p_vre_information17 =>p_vre_information17
652 ,p_vre_information18 =>p_vre_information18
653 ,p_vre_information19 =>p_vre_information19
654 ,p_vre_information20 =>p_vre_information20
655 ) ;
656
657 exception
658 when hr_api.cannot_find_prog_unit then
659 hr_api.cannot_find_prog_unit_error
660 (p_module_name => 'PQP_VEHICLE_REPOSITORY_API'
661 ,p_hook_type => 'BP'
662 );
663 end;
664
665
666 pqp_vre_upd.upd
667 (p_effective_date =>l_effective_date
668 ,p_datetrack_mode =>p_datetrack_mode
669 ,p_vehicle_repository_id =>p_vehicle_repository_id
670 ,p_object_version_number =>p_object_version_number
671 ,p_registration_number =>p_registration_number
672 ,p_vehicle_type =>p_vehicle_type
673 ,p_vehicle_id_number =>p_vehicle_id_number
674 ,p_business_group_id =>p_business_group_id
675 ,p_make =>p_make
676 ,p_engine_capacity_in_cc =>p_engine_capacity_in_cc
677 ,p_fuel_type =>p_fuel_type
678 ,p_currency_code =>l_currency_code
679 ,p_vehicle_status =>p_vehicle_status
680 ,p_vehicle_inactivity_reason =>p_vehicle_inactivity_reason
681 ,p_model =>p_model
682 ,p_initial_registration =>p_initial_registration
683 ,p_last_registration_renew_date =>p_last_registration_renew_date
684 ,p_list_price =>p_list_price
685 ,p_accessory_value_at_startdate =>p_accessory_value_at_startdate
686 ,p_accessory_value_added_later =>p_accessory_value_added_later
687 ,p_market_value_classic_car =>p_market_value_classic_car
688 ,p_fiscal_ratings =>p_fiscal_ratings
689 ,p_fiscal_ratings_uom =>p_fiscal_ratings_uom
690 ,p_vehicle_provider =>p_vehicle_provider
691 ,p_vehicle_ownership =>p_vehicle_ownership
692 ,p_shared_vehicle =>p_shared_vehicle
693 ,p_asset_number =>p_asset_number
694 ,p_lease_contract_number =>p_lease_contract_number
695 ,p_lease_contract_expiry_date =>p_lease_contract_expiry_date
696 ,p_taxation_method =>p_taxation_method
697 ,p_fleet_info =>p_fleet_info
698 ,p_fleet_transfer_date =>p_fleet_transfer_date
699 ,p_color =>p_color
700 ,p_seating_capacity =>p_seating_capacity
701 ,p_weight =>p_weight
702 ,p_weight_uom =>p_weight_uom
703 ,p_model_year =>p_model_year
704 ,p_insurance_number =>p_insurance_number
705 ,p_insurance_expiry_date =>p_insurance_expiry_date
706 ,p_comments =>p_comments
707 ,p_vre_attribute_category =>p_vre_attribute_category
708 ,p_vre_attribute1 =>p_vre_attribute1
709 ,p_vre_attribute2 =>p_vre_attribute2
710 ,p_vre_attribute3 =>p_vre_attribute3
711 ,p_vre_attribute4 =>p_vre_attribute4
712 ,p_vre_attribute5 =>p_vre_attribute5
713 ,p_vre_attribute6 =>p_vre_attribute6
714 ,p_vre_attribute7 =>p_vre_attribute7
715 ,p_vre_attribute8 =>p_vre_attribute8
716 ,p_vre_attribute9 =>p_vre_attribute9
717 ,p_vre_attribute10 =>p_vre_attribute10
718 ,p_vre_attribute11 =>p_vre_attribute11
719 ,p_vre_attribute12 =>p_vre_attribute12
720 ,p_vre_attribute13 =>p_vre_attribute13
721 ,p_vre_attribute14 =>p_vre_attribute14
722 ,p_vre_attribute15 =>p_vre_attribute15
723 ,p_vre_attribute16 =>p_vre_attribute16
724 ,p_vre_attribute17 =>p_vre_attribute17
725 ,p_vre_attribute18 =>p_vre_attribute18
726 ,p_vre_attribute19 =>p_vre_attribute19
727 ,p_vre_attribute20 =>p_vre_attribute20
728 ,p_vre_information_category =>p_vre_information_category
729 ,p_vre_information1 =>p_vre_information1
730 ,p_vre_information2 =>p_vre_information2
731 ,p_vre_information3 =>p_vre_information3
732 ,p_vre_information4 =>p_vre_information4
733 ,p_vre_information5 =>p_vre_information5
737 ,p_vre_information9 =>p_vre_information9
734 ,p_vre_information6 =>p_vre_information6
735 ,p_vre_information7 =>p_vre_information7
736 ,p_vre_information8 =>p_vre_information8
738 ,p_vre_information10 =>p_vre_information10
739 ,p_vre_information11 =>p_vre_information11
740 ,p_vre_information12 =>p_vre_information12
741 ,p_vre_information13 =>p_vre_information13
742 ,p_vre_information14 =>p_vre_information14
743 ,p_vre_information15 =>p_vre_information15
744 ,p_vre_information16 =>p_vre_information16
745 ,p_vre_information17 =>p_vre_information17
746 ,p_vre_information18 =>p_vre_information18
747 ,p_vre_information19 =>p_vre_information19
748 ,p_vre_information20 =>p_vre_information20
749 ,p_effective_start_date =>p_effective_start_date
750 ,p_effective_end_date =>p_effective_end_date
751 ) ;
752
753 -- Call after Process User Hook
754 --
755 begin
756 PQP_VEHICLE_REPOSITORY_BK2.update_vehicle_a
757 ( p_effective_date =>l_effective_date
758 ,p_datetrack_mode =>p_datetrack_mode
759 ,p_vehicle_repository_id =>p_vehicle_repository_id
760 ,p_object_version_number =>p_object_version_number
761 ,p_registration_number =>p_registration_number
762 ,p_vehicle_type =>p_vehicle_type
763 ,p_vehicle_id_number =>p_vehicle_id_number
764 ,p_business_group_id =>p_business_group_id
765 ,p_make =>p_make
766 ,p_engine_capacity_in_cc =>p_engine_capacity_in_cc
767 ,p_fuel_type =>p_fuel_type
768 ,p_currency_code =>l_currency_code
769 ,p_vehicle_status =>p_vehicle_status
770 ,p_vehicle_inactivity_reason =>p_vehicle_inactivity_reason
771 ,p_model =>p_model
772 ,p_initial_registration =>p_initial_registration
773 ,p_last_registration_renew_date =>p_last_registration_renew_date
774 ,p_list_price =>p_list_price
775 ,p_accessory_value_at_startdate =>p_accessory_value_at_startdate
776 ,p_accessory_value_added_later =>p_accessory_value_added_later
777 ,p_market_value_classic_car =>p_market_value_classic_car
778 ,p_fiscal_ratings =>p_fiscal_ratings
779 ,p_fiscal_ratings_uom =>p_fiscal_ratings_uom
780 ,p_vehicle_provider =>p_vehicle_provider
781 ,p_vehicle_ownership =>p_vehicle_ownership
782 ,p_shared_vehicle =>p_shared_vehicle
783 ,p_asset_number =>p_asset_number
784 ,p_lease_contract_number =>p_lease_contract_number
785 ,p_lease_contract_expiry_date =>p_lease_contract_expiry_date
786 ,p_taxation_method =>p_taxation_method
787 ,p_fleet_info =>p_fleet_info
788 ,p_fleet_transfer_date =>p_fleet_transfer_date
789 ,p_color =>p_color
790 ,p_seating_capacity =>p_seating_capacity
791 ,p_weight =>p_weight
792 ,p_weight_uom =>p_weight_uom
793 ,p_model_year =>p_model_year
794 ,p_insurance_number =>p_insurance_number
795 ,p_insurance_expiry_date =>p_insurance_expiry_date
796 ,p_comments =>p_comments
797 ,p_vre_attribute_category =>p_vre_attribute_category
798 ,p_vre_attribute1 =>p_vre_attribute1
799 ,p_vre_attribute2 =>p_vre_attribute2
800 ,p_vre_attribute3 =>p_vre_attribute3
801 ,p_vre_attribute4 =>p_vre_attribute4
802 ,p_vre_attribute5 =>p_vre_attribute5
803 ,p_vre_attribute6 =>p_vre_attribute6
804 ,p_vre_attribute7 =>p_vre_attribute7
805 ,p_vre_attribute8 =>p_vre_attribute8
806 ,p_vre_attribute9 =>p_vre_attribute9
807 ,p_vre_attribute10 =>p_vre_attribute10
808 ,p_vre_attribute11 =>p_vre_attribute11
809 ,p_vre_attribute12 =>p_vre_attribute12
810 ,p_vre_attribute13 =>p_vre_attribute13
811 ,p_vre_attribute14 =>p_vre_attribute14
812 ,p_vre_attribute15 =>p_vre_attribute15
813 ,p_vre_attribute16 =>p_vre_attribute16
814 ,p_vre_attribute17 =>p_vre_attribute17
815 ,p_vre_attribute18 =>p_vre_attribute18
816 ,p_vre_attribute19 =>p_vre_attribute19
817 ,p_vre_attribute20 =>p_vre_attribute20
818 ,p_vre_information_category =>p_vre_information_category
819 ,p_vre_information1 =>p_vre_information1
820 ,p_vre_information2 =>p_vre_information2
821 ,p_vre_information3 =>p_vre_information3
822 ,p_vre_information4 =>p_vre_information4
823 ,p_vre_information5 =>p_vre_information5
824 ,p_vre_information6 =>p_vre_information6
825 ,p_vre_information7 =>p_vre_information7
826 ,p_vre_information8 =>p_vre_information8
827 ,p_vre_information9 =>p_vre_information9
828 ,p_vre_information10 =>p_vre_information10
829 ,p_vre_information11 =>p_vre_information11
833 ,p_vre_information15 =>p_vre_information15
830 ,p_vre_information12 =>p_vre_information12
831 ,p_vre_information13 =>p_vre_information13
832 ,p_vre_information14 =>p_vre_information14
834 ,p_vre_information16 =>p_vre_information16
835 ,p_vre_information17 =>p_vre_information17
836 ,p_vre_information18 =>p_vre_information18
837 ,p_vre_information19 =>p_vre_information19
838 ,p_vre_information20 =>p_vre_information20
839 ,p_effective_start_date =>p_effective_start_date
840 ,p_effective_end_date =>p_effective_end_date
841 ) ;
842
843 exception
844 when hr_api.cannot_find_prog_unit then
845 hr_api.cannot_find_prog_unit_error
846 (p_module_name => 'PQP_VEHICLE_REPOSITORY_API'
847 ,p_hook_type => 'AP'
848 );
849 end;
850
851 hr_utility.set_location(l_proc, 60);
852 --
853 -- When in validation only mode raise the Validate_Enabled exception
854 --
855 if p_validate then
856 raise hr_api.validate_enabled;
857 end if;
858 --
859 -- Set all output arguments
860 --
861 p_effective_start_date := p_effective_start_date;
862 p_effective_end_date := p_effective_end_date ;
863 --
864 hr_utility.set_location(' Leaving:'||l_proc, 70);
865
866 exception
867 when hr_api.validate_enabled then
868 --
869 -- As the Validate_Enabled exception has been raised
870 -- we must rollback to the savepoint
871 --
872 rollback to update_vehicle;
873 --
874 -- Only set output warning arguments
875 -- (Any key or derived arguments must be set to null
876 -- when validation only mode is being used.)
877 --
878 p_effective_start_date := null;
879 p_effective_end_date := null;
880 hr_utility.set_location(' Leaving:'||l_proc, 80);
881 when others then
882 --
883 -- A validation or unexpected error has occured
884 --
885 rollback to update_vehicle;
886 p_effective_start_date := null;
887 p_effective_end_date := null;
888 hr_utility.set_location(' Leaving:'||l_proc, 90);
889 raise;
890
891 END update_vehicle;
892
893
894
895 -- ----------------------------------------------------------------------------
896 -- |--------------------------< delete_vehicle >------------------------
897 --|
898 -- ----------------------------------------------------------------------------
899 --
900 Procedure delete_vehicle
901 (p_validate in boolean
902 ,p_effective_date in date
903 ,p_datetrack_mode in varchar2
904 ,p_vehicle_repository_id in number
905 ,p_object_version_number in out NOCOPY number
906 ,p_effective_start_date out NOCOPY date
907 ,p_effective_end_date out NOCOPY date
908 )
909 IS
910
911 l_proc varchar2(72) := g_package||'delete_vehicle';
912 l_effective_date date;
913
914 BEGIN
915 l_effective_date:=TRUNC(p_effective_date);
916 --
917 -- Call Before Process User Hook
918 --
919 begin
920
921 hr_utility.set_location('Entering:'|| l_proc, 10);
922 --
923 -- Issue a savepoint
924 --
925 savepoint delete_vehicle;
926 --
927 hr_utility.set_location(l_proc, 20);
928
929 PQP_VEHICLE_REPOSITORY_BK3.delete_vehicle_b
930 (p_validate =>p_validate
931 ,p_effective_date =>l_effective_date
932 ,p_datetrack_mode =>p_datetrack_mode
933 ,p_vehicle_repository_id =>p_vehicle_repository_id
934 ,p_object_version_number =>p_object_version_number
935 ) ;
936
937 exception
938 when hr_api.cannot_find_prog_unit then
939 hr_api.cannot_find_prog_unit_error
940 (p_module_name => 'PQP_VEHICLE_REPOSITORY_API'
941 ,p_hook_type => 'BP'
942 );
943 end;
944
945 pqp_vre_del.del
946 (p_effective_date =>l_effective_date
947 ,p_datetrack_mode =>p_datetrack_mode
948 ,p_vehicle_repository_id =>p_vehicle_repository_id
949 ,p_object_version_number =>p_object_version_number
950 ,p_effective_start_date =>p_effective_start_date
951 ,p_effective_end_date =>p_effective_end_date
952 );
953 --
954 -- Call Before Process User Hook
955 --
956
957 begin
958 PQP_VEHICLE_REPOSITORY_BK3.delete_vehicle_a
959 (p_validate =>p_validate
960 ,p_effective_date =>l_effective_date
961 ,p_datetrack_mode =>p_datetrack_mode
962 ,p_vehicle_repository_id =>p_vehicle_repository_id
963 ,p_object_version_number =>p_object_version_number
964 ,p_effective_start_date =>p_effective_start_date
965 ,p_effective_end_date =>p_effective_end_date
966 ) ;
967
968 exception
969 when hr_api.cannot_find_prog_unit then
970 hr_api.cannot_find_prog_unit_error
971 (p_module_name => 'PQP_VEHICLE_REPOSITORY_API'
972 ,p_hook_type => 'AP'
973 );
974 end;
975
976 hr_utility.set_location(l_proc, 60);
977 --
981 raise hr_api.validate_enabled;
978 -- When in validation only mode raise the Validate_Enabled exception
979 --
980 if p_validate then
982 end if;
983 --
984 -- Set all output arguments
985 --
986 --
987 p_object_version_number := p_object_version_number ;
988 p_effective_start_date := p_effective_start_date ;
989 p_effective_end_date := p_effective_end_date ;
990 hr_utility.set_location(' Leaving:'||l_proc, 70);
991 exception
992 when hr_api.validate_enabled then
993 --
994 -- As the Validate_Enabled exception has been raised
995 -- we must rollback to the savepoint
996 --
997 rollback to delete_vehicle;
998 --
999 -- Only set output warning arguments
1000 -- (Any key or derived arguments must be set to null
1001 -- when validation only mode is being used.)
1002 --
1003 p_effective_start_date := null;
1004 p_effective_end_date := null;
1005 p_object_version_number := null;
1006 hr_utility.set_location(' Leaving:'||l_proc, 80);
1007 when others then
1008 --
1009 -- A validation or unexpected error has occured
1010 --
1011 rollback to delete_vehicle;
1012 p_effective_start_date := null;
1013 p_effective_end_date := null;
1014 p_object_version_number := null;
1015 hr_utility.set_location(' Leaving:'||l_proc, 90);
1016 raise;
1017 END delete_vehicle;
1018
1019
1020 end PQP_VEHICLE_REPOSITORY_API;