[Home] [Help]
PACKAGE BODY: APPS.PQP_VEH_MULTI_ALLOC
Source
1 Package Body PQP_VEH_MULTI_ALLOC as
2 /* $Header: pqvalmul.pkb 120.0.12010000.2 2008/08/08 07:21:35 ubhat ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' PQP_VEH_MULTI_ALLOC.';
7 --
8
9 Procedure convert_defs
10 (p_rec in out nocopy pqp_val_shd.g_rec_type
11 ) is
12 --
13 Begin
14 --
15 -- We must now examine each argument value in the
16 -- p_rec plsql record structure
17 -- to see if a system default is being used. If a system default
18 -- is being used then we must set to the 'current' argument value.
19 --
20 If (p_rec.assignment_id = hr_api.g_number) then
21 p_rec.assignment_id :=
22 pqp_val_shd.g_old_rec.assignment_id;
23 End If;
24 If (p_rec.business_group_id = hr_api.g_number) then
25 p_rec.business_group_id :=
26 pqp_val_shd.g_old_rec.business_group_id;
27 End If;
28 If (p_rec.across_assignments = hr_api.g_varchar2) then
29 p_rec.across_assignments :=
30 pqp_val_shd.g_old_rec.across_assignments;
31 End If;
32 If (p_rec.vehicle_repository_id = hr_api.g_number) then
33 p_rec.vehicle_repository_id :=
34 pqp_val_shd.g_old_rec.vehicle_repository_id;
35 End If;
36 If (p_rec.usage_type = hr_api.g_varchar2) then
37 p_rec.usage_type :=
38 pqp_val_shd.g_old_rec.usage_type;
39 End If;
40 If (p_rec.capital_contribution = hr_api.g_number) then
41 p_rec.capital_contribution :=
42 pqp_val_shd.g_old_rec.capital_contribution;
43 End If;
44 If (p_rec.private_contribution = hr_api.g_number) then
45 p_rec.private_contribution :=
46 pqp_val_shd.g_old_rec.private_contribution;
47 End If;
48 If (p_rec.default_vehicle = hr_api.g_varchar2) then
49 p_rec.default_vehicle :=
50 pqp_val_shd.g_old_rec.default_vehicle;
51 End If;
52 If (p_rec.fuel_card = hr_api.g_varchar2) then
53 p_rec.fuel_card :=
54 pqp_val_shd.g_old_rec.fuel_card;
55 End If;
56 If (p_rec.fuel_card_number = hr_api.g_varchar2) then
57 p_rec.fuel_card_number :=
58 pqp_val_shd.g_old_rec.fuel_card_number;
59 End If;
60 If (p_rec.calculation_method = hr_api.g_varchar2) then
61 p_rec.calculation_method :=
62 pqp_val_shd.g_old_rec.calculation_method;
63 End If;
64 If (p_rec.rates_table_id = hr_api.g_number) then
65 p_rec.rates_table_id :=
66 pqp_val_shd.g_old_rec.rates_table_id;
67 End If;
68 If (p_rec.element_type_id = hr_api.g_number) then
69 p_rec.element_type_id :=
70 pqp_val_shd.g_old_rec.element_type_id;
71 End If;
72 If (p_rec.private_use_flag = hr_api.g_varchar2) then
73 p_rec.private_use_flag :=
74 pqp_val_shd.g_old_rec.private_use_flag;
75 End If;
76 If (p_rec.insurance_number = hr_api.g_varchar2) then
77 p_rec.insurance_number :=
78 pqp_val_shd.g_old_rec.insurance_number;
79 End If;
80 If (p_rec.insurance_expiry_date = hr_api.g_date) then
81 p_rec.insurance_expiry_date :=
82 pqp_val_shd.g_old_rec.insurance_expiry_date;
83 End If;
84
85 If (p_rec.val_attribute_category = hr_api.g_varchar2) then
86 p_rec.val_attribute_category :=
87 pqp_val_shd.g_old_rec.val_attribute_category;
88 End If;
89 If (p_rec.val_attribute1 = hr_api.g_varchar2) then
90 p_rec.val_attribute1 :=
91 pqp_val_shd.g_old_rec.val_attribute1;
92 End If;
93 If (p_rec.val_attribute2 = hr_api.g_varchar2) then
94 p_rec.val_attribute2 :=
95 pqp_val_shd.g_old_rec.val_attribute2;
96 End If;
97 If (p_rec.val_attribute3 = hr_api.g_varchar2) then
98 p_rec.val_attribute3 :=
99 pqp_val_shd.g_old_rec.val_attribute3;
100 End If;
101 If (p_rec.val_attribute4 = hr_api.g_varchar2) then
102 p_rec.val_attribute4 :=
103 pqp_val_shd.g_old_rec.val_attribute4;
104 End If;
105 If (p_rec.val_attribute5 = hr_api.g_varchar2) then
106 p_rec.val_attribute5 :=
107 pqp_val_shd.g_old_rec.val_attribute5;
108 End If;
109 If (p_rec.val_attribute6 = hr_api.g_varchar2) then
110 p_rec.val_attribute6 :=
111 pqp_val_shd.g_old_rec.val_attribute6;
112 End If;
113 If (p_rec.val_attribute7 = hr_api.g_varchar2) then
114 p_rec.val_attribute7 :=
115 pqp_val_shd.g_old_rec.val_attribute7;
116 End If;
117 If (p_rec.val_attribute8 = hr_api.g_varchar2) then
118 p_rec.val_attribute8 :=
119 pqp_val_shd.g_old_rec.val_attribute8;
120 End If;
121 If (p_rec.val_attribute9 = hr_api.g_varchar2) then
122 p_rec.val_attribute9 :=
123 pqp_val_shd.g_old_rec.val_attribute9;
124 End If;
125 If (p_rec.val_attribute10 = hr_api.g_varchar2) then
126 p_rec.val_attribute10 :=
127 pqp_val_shd.g_old_rec.val_attribute10;
128 End If;
129 If (p_rec.val_attribute11 = hr_api.g_varchar2) then
130 p_rec.val_attribute11 :=
131 pqp_val_shd.g_old_rec.val_attribute11;
132 End If;
133 If (p_rec.val_attribute12 = hr_api.g_varchar2) then
134 p_rec.val_attribute12 :=
135 pqp_val_shd.g_old_rec.val_attribute12;
136 End If;
137 If (p_rec.val_attribute13 = hr_api.g_varchar2) then
138 p_rec.val_attribute13 :=
139 pqp_val_shd.g_old_rec.val_attribute13;
140 End If;
141 If (p_rec.val_attribute14 = hr_api.g_varchar2) then
142 p_rec.val_attribute14 :=
143 pqp_val_shd.g_old_rec.val_attribute14;
144 End If;
145 If (p_rec.val_attribute15 = hr_api.g_varchar2) then
146 p_rec.val_attribute15 :=
147 pqp_val_shd.g_old_rec.val_attribute15;
148 End If;
149 If (p_rec.val_attribute16 = hr_api.g_varchar2) then
150 p_rec.val_attribute16 :=
151 pqp_val_shd.g_old_rec.val_attribute16;
152 End If;
153 If (p_rec.val_attribute17 = hr_api.g_varchar2) then
154 p_rec.val_attribute17 :=
155 pqp_val_shd.g_old_rec.val_attribute17;
156 End If;
157 If (p_rec.val_attribute18 = hr_api.g_varchar2) then
158 p_rec.val_attribute18 :=
159 pqp_val_shd.g_old_rec.val_attribute18;
160 End If;
161 If (p_rec.val_attribute19 = hr_api.g_varchar2) then
162 p_rec.val_attribute19 :=
163 pqp_val_shd.g_old_rec.val_attribute19;
164 End If;
165 If (p_rec.val_attribute20 = hr_api.g_varchar2) then
166 p_rec.val_attribute20 :=
167 pqp_val_shd.g_old_rec.val_attribute20;
168 End If;
169 If (p_rec.val_information_category = hr_api.g_varchar2) then
170 p_rec.val_information_category :=
171 pqp_val_shd.g_old_rec.val_information_category;
172 End If;
173 If (p_rec.val_information1 = hr_api.g_varchar2) then
174 p_rec.val_information1 :=
175 pqp_val_shd.g_old_rec.val_information1;
176 End If;
177 If (p_rec.val_information2 = hr_api.g_varchar2) then
178 p_rec.val_information2 :=
179 pqp_val_shd.g_old_rec.val_information2;
180 End If;
181 If (p_rec.val_information3 = hr_api.g_varchar2) then
182 p_rec.val_information3 :=
183 pqp_val_shd.g_old_rec.val_information3;
184 End If;
185 If (p_rec.val_information4 = hr_api.g_varchar2) then
186 p_rec.val_information4 :=
187 pqp_val_shd.g_old_rec.val_information4;
188 End If;
189 If (p_rec.val_information5 = hr_api.g_varchar2) then
190 p_rec.val_information5 :=
191 pqp_val_shd.g_old_rec.val_information5;
192 End If;
193 If (p_rec.val_information6 = hr_api.g_varchar2) then
194 p_rec.val_information6 :=
195 pqp_val_shd.g_old_rec.val_information6;
196 End If;
197 If (p_rec.val_information7 = hr_api.g_varchar2) then
198 p_rec.val_information7 :=
199 pqp_val_shd.g_old_rec.val_information7;
200 End If;
201 If (p_rec.val_information8 = hr_api.g_varchar2) then
202 p_rec.val_information8 :=
203 pqp_val_shd.g_old_rec.val_information8;
204 End If;
205 If (p_rec.val_information9 = hr_api.g_varchar2) then
206 p_rec.val_information9 :=
207 pqp_val_shd.g_old_rec.val_information9;
208 End If;
209 If (p_rec.val_information10 = hr_api.g_varchar2) then
210 p_rec.val_information10 :=
211 pqp_val_shd.g_old_rec.val_information10;
212 End If;
213 If (p_rec.val_information11 = hr_api.g_varchar2) then
214 p_rec.val_information11 :=
215 pqp_val_shd.g_old_rec.val_information11;
216 End If;
217 If (p_rec.val_information12 = hr_api.g_varchar2) then
218 p_rec.val_information12 :=
219 pqp_val_shd.g_old_rec.val_information12;
220 End If;
221 If (p_rec.val_information13 = hr_api.g_varchar2) then
222 p_rec.val_information13 :=
223 pqp_val_shd.g_old_rec.val_information13;
224 End If;
225 If (p_rec.val_information14 = hr_api.g_varchar2) then
226 p_rec.val_information14 :=
227 pqp_val_shd.g_old_rec.val_information14;
228 End If;
229 If (p_rec.val_information15 = hr_api.g_varchar2) then
230 p_rec.val_information15 :=
231 pqp_val_shd.g_old_rec.val_information15;
232 End If;
233 If (p_rec.val_information16 = hr_api.g_varchar2) then
234 p_rec.val_information16 :=
235 pqp_val_shd.g_old_rec.val_information16;
236 End If;
237 If (p_rec.val_information17 = hr_api.g_varchar2) then
238 p_rec.val_information17 :=
239 pqp_val_shd.g_old_rec.val_information17;
240 End If;
241 If (p_rec.val_information18 = hr_api.g_varchar2) then
242 p_rec.val_information18 :=
243 pqp_val_shd.g_old_rec.val_information18;
244 End If;
245 If (p_rec.val_information19 = hr_api.g_varchar2) then
246 p_rec.val_information19 :=
247 pqp_val_shd.g_old_rec.val_information19;
248 End If;
249 If (p_rec.val_information20 = hr_api.g_varchar2) then
250 p_rec.val_information20 :=
251 pqp_val_shd.g_old_rec.val_information20;
252 End If;
253 If (p_rec.fuel_benefit = hr_api.g_varchar2) then
254 p_rec.fuel_benefit :=
255 pqp_val_shd.g_old_rec.fuel_benefit;
256 End If;
257 If (p_rec.sliding_rates_info = hr_api.g_varchar2) then
258 p_rec.sliding_rates_info :=
259 pqp_val_shd.g_old_rec.sliding_rates_info;
260 End If;
261
262 --
263 End convert_defs;
264
265 -- ----------------------------------------------------------------------------
266 -- |------------------< CREATE_VEHICLE_MULTIPLE_ALLOCATION >------------------|
267 -- ----------------------------------------------------------------------------
268 --
269 procedure create_veh_multi_alloc
270 (p_validate in boolean
271 ,p_effective_date in date
272 ,p_assignment_id in number
273 ,p_business_group_id in number
274 ,p_vehicle_repository_id in number
275 ,p_across_assignments in varchar2
276 ,p_usage_type in varchar2
277 ,p_capital_contribution in number
278 ,p_private_contribution in number
279 ,p_default_vehicle in varchar2
280 ,p_fuel_card in varchar2
281 ,p_fuel_card_number in varchar2
282 ,p_calculation_method in varchar2
283 ,p_rates_table_id in number
284 ,p_element_type_id in number
285 ,p_private_use_flag in varchar2
286 ,p_insurance_number in varchar2
287 ,p_insurance_expiry_date in date
288 ,p_val_attribute_category in varchar2
289 ,p_val_attribute1 in varchar2
290 ,p_val_attribute2 in varchar2
291 ,p_val_attribute3 in varchar2
292 ,p_val_attribute4 in varchar2
293 ,p_val_attribute5 in varchar2
294 ,p_val_attribute6 in varchar2
295 ,p_val_attribute7 in varchar2
296 ,p_val_attribute8 in varchar2
297 ,p_val_attribute9 in varchar2
298 ,p_val_attribute10 in varchar2
299 ,p_val_attribute11 in varchar2
300 ,p_val_attribute12 in varchar2
301 ,p_val_attribute13 in varchar2
302 ,p_val_attribute14 in varchar2
303 ,p_val_attribute15 in varchar2
304 ,p_val_attribute16 in varchar2
305 ,p_val_attribute17 in varchar2
306 ,p_val_attribute18 in varchar2
307 ,p_val_attribute19 in varchar2
308 ,p_val_attribute20 in varchar2
309 ,p_val_information_category in varchar2
310 ,p_val_information1 in varchar2
311 ,p_val_information2 in varchar2
312 ,p_val_information3 in varchar2
313 ,p_val_information4 in varchar2
314 ,p_val_information5 in varchar2
315 ,p_val_information6 in varchar2
316 ,p_val_information7 in varchar2
317 ,p_val_information8 in varchar2
318 ,p_val_information9 in varchar2
319 ,p_val_information10 in varchar2
320 ,p_val_information11 in varchar2
321 ,p_val_information12 in varchar2
322 ,p_val_information13 in varchar2
323 ,p_val_information14 in varchar2
324 ,p_val_information15 in varchar2
325 ,p_val_information16 in varchar2
326 ,p_val_information17 in varchar2
327 ,p_val_information18 in varchar2
328 ,p_val_information19 in varchar2
329 ,p_val_information20 in varchar2
330 ,p_fuel_benefit in varchar2
331 ,p_sliding_rates_info in varchar2
332 )
333 is
334 --
335 -- Declare cursors and local variables
336 --
337
338 l_proc varchar2(72) := g_package||'<BUS_PROCESS_NAME>';
339
340 CURSOR c_get_asg
341 IS
342 SELECT paa.assignment_id
343 FROM per_all_assignments_f paa
344 WHERE paa.person_id =(SELECT paa1.person_id
345 FROM per_all_assignments_f paa1
346 WHERE paa1.assignment_id=p_assignment_id
347 AND paa1.business_group_id=p_business_group_id
348 AND p_effective_date BETWEEN paa1.effective_start_date
349 AND paa1.effective_end_date)
350 AND NOT EXISTS (SELECT 'X'
351 FROM pqp_vehicle_allocations_f pva
352 WHERE pva.vehicle_repository_id=p_vehicle_repository_id
353 AND p_effective_date BETWEEN pva.effective_start_date
354 AND pva.effective_end_date
355 and pva.assignment_id =paa.assignment_id
356 and pva.business_group_id=paa.business_group_id)
357 AND p_effective_date BETWEEN paa.effective_start_date
358 AND paa.effective_end_date;
359 l_get_asg c_get_asg%ROWTYPE;
360 l_vehicle_allocation_id NUMBER;
361 l_object_version_number NUMBER;
362 l_effective_start_date DATE;
363 l_effective_end_date DATE;
364 begin
365 hr_utility.set_location('Entering:'|| l_proc, 10);
366 --
367
368
369 OPEN c_get_asg;
370 LOOP
371 FETCH c_get_asg INTO l_get_asg;
372 EXIT WHEN c_get_asg%NOTFOUND;
373
374 pqp_val_ins.ins
375 (p_effective_date => p_effective_date
376 ,p_assignment_id => l_get_asg.assignment_id
377 ,p_business_group_id => p_business_group_id
378 ,p_vehicle_repository_id => p_vehicle_repository_id
379 ,p_across_assignments => p_across_assignments
380 ,p_usage_type => p_usage_type
381 ,p_capital_contribution => p_capital_contribution
382 ,p_private_contribution => p_private_contribution
383 ,p_default_vehicle => p_default_vehicle
384 ,p_fuel_card => p_fuel_card
385 ,p_fuel_card_number => p_fuel_card_number
386 ,p_calculation_method => p_calculation_method
387 ,p_rates_table_id => p_rates_table_id
388 ,p_element_type_id => p_element_type_id
389 ,p_private_use_flag => p_private_use_flag
390 ,p_insurance_number => p_insurance_number
391 ,p_insurance_expiry_date => p_insurance_expiry_date
392 ,p_val_attribute_category => p_val_attribute_category
393 ,p_val_attribute1 => p_val_attribute1
394 ,p_val_attribute2 => p_val_attribute2
395 ,p_val_attribute3 => p_val_attribute3
396 ,p_val_attribute4 => p_val_attribute4
397 ,p_val_attribute5 => p_val_attribute5
398 ,p_val_attribute6 => p_val_attribute6
399 ,p_val_attribute7 => p_val_attribute7
400 ,p_val_attribute8 => p_val_attribute8
401 ,p_val_attribute9 => p_val_attribute9
402 ,p_val_attribute10 => p_val_attribute10
403 ,p_val_attribute11 => p_val_attribute11
404 ,p_val_attribute12 => p_val_attribute12
405 ,p_val_attribute13 => p_val_attribute13
406 ,p_val_attribute14 => p_val_attribute14
407 ,p_val_attribute15 => p_val_attribute15
408 ,p_val_attribute16 => p_val_attribute16
409 ,p_val_attribute17 => p_val_attribute17
410 ,p_val_attribute18 => p_val_attribute18
411 ,p_val_attribute19 => p_val_attribute19
412 ,p_val_attribute20 => p_val_attribute20
413 ,p_val_information_category => p_val_information_category
414 ,p_val_information1 => p_val_information1
415 ,p_val_information2 => p_val_information2
416 ,p_val_information3 => p_val_information3
417 ,p_val_information4 => p_val_information4
418 ,p_val_information5 => p_val_information5
419 ,p_val_information6 => p_val_information6
420 ,p_val_information7 => p_val_information7
421 ,p_val_information8 => p_val_information8
422 ,p_val_information9 => p_val_information9
423 ,p_val_information10 => p_val_information10
424 ,p_val_information11 => p_val_information11
425 ,p_val_information12 => p_val_information12
426 ,p_val_information13 => p_val_information13
427 ,p_val_information14 => p_val_information14
428 ,p_val_information15 => p_val_information15
429 ,p_val_information16 => p_val_information16
430 ,p_val_information17 => p_val_information17
431 ,p_val_information18 => p_val_information18
432 ,p_val_information19 => p_val_information19
433 ,p_val_information20 => p_val_information20
434 ,p_fuel_benefit => p_fuel_benefit
435 ,p_sliding_rates_info => p_sliding_rates_info
436 ,p_vehicle_allocation_id => l_vehicle_allocation_id
437 ,p_object_version_number => l_object_version_number
438 ,p_effective_start_date => l_effective_start_date
439 ,p_effective_end_date => l_effective_end_date
440
441 );
442
443 END LOOP;
444 CLOSE c_get_asg;
445
446 hr_utility.set_location(' Leaving:'||l_proc, 70);
447 exception
448 when others then
449 --
450 -- A validation or unexpected error has occured
451 --
452 --rollback to <BUS_PROCESS_NAME>
453 hr_utility.set_location(' Leaving:'||l_proc, 90);
454 raise;
455 end create_veh_multi_alloc;
456 --
457
458
459 --
460 -- ----------------------------------------------------------------------------
461 -- |------------------< UPDATE_VEHICLE_MULTIPLE_ALLOCATION >------------------|
462 -- ----------------------------------------------------------------------------
463
464 procedure update_veh_multi_alloc
465 (p_validate in boolean
466 ,p_effective_date in date
467 ,p_datetrack_mode in varchar2
468 ,p_vehicle_allocation_id in number
469 ,p_object_version_number in out nocopy number
470 ,p_assignment_id in number
471 ,p_business_group_id in number
472 ,p_vehicle_repository_id in number
473 ,p_across_assignments in varchar2
474 ,p_usage_type in varchar2
475 ,p_capital_contribution in number
476 ,p_private_contribution in number
477 ,p_default_vehicle in varchar2
478 ,p_fuel_card in varchar2
479 ,p_fuel_card_number in varchar2
480 ,p_calculation_method in varchar2
481 ,p_rates_table_id in number
482 ,p_element_type_id in number
483 ,p_private_use_flag in varchar2
484 ,p_insurance_number in varchar2
485 ,p_insurance_expiry_date in date
486 ,p_val_attribute_category in varchar2
487 ,p_val_attribute1 in varchar2
488 ,p_val_attribute2 in varchar2
489 ,p_val_attribute3 in varchar2
490 ,p_val_attribute4 in varchar2
491 ,p_val_attribute5 in varchar2
492 ,p_val_attribute6 in varchar2
493 ,p_val_attribute7 in varchar2
494 ,p_val_attribute8 in varchar2
495 ,p_val_attribute9 in varchar2
496 ,p_val_attribute10 in varchar2
497 ,p_val_attribute11 in varchar2
498 ,p_val_attribute12 in varchar2
499 ,p_val_attribute13 in varchar2
500 ,p_val_attribute14 in varchar2
501 ,p_val_attribute15 in varchar2
502 ,p_val_attribute16 in varchar2
503 ,p_val_attribute17 in varchar2
504 ,p_val_attribute18 in varchar2
505 ,p_val_attribute19 in varchar2
506 ,p_val_attribute20 in varchar2
507 ,p_val_information_category in varchar2
508 ,p_val_information1 in varchar2
509 ,p_val_information2 in varchar2
510 ,p_val_information3 in varchar2
511 ,p_val_information4 in varchar2
512 ,p_val_information5 in varchar2
513 ,p_val_information6 in varchar2
514 ,p_val_information7 in varchar2
515 ,p_val_information8 in varchar2
516 ,p_val_information9 in varchar2
517 ,p_val_information10 in varchar2
518 ,p_val_information11 in varchar2
519 ,p_val_information12 in varchar2
520 ,p_val_information13 in varchar2
521 ,p_val_information14 in varchar2
522 ,p_val_information15 in varchar2
523 ,p_val_information16 in varchar2
524 ,p_val_information17 in varchar2
525 ,p_val_information18 in varchar2
526 ,p_val_information19 in varchar2
527 ,p_val_information20 in varchar2
528 ,p_fuel_benefit in varchar2
529 ,p_sliding_rates_info in varchar2
530 )
531 IS
532
533
534 CURSOR c_get_asg
535 IS
536 SELECT paa.assignment_id
537 ,pva.object_version_number ovn
538 ,pva.vehicle_allocation_id
539 FROM per_all_assignments_f paa
540 ,pqp_vehicle_allocations_f pva
541 WHERE paa.person_id =(SELECT paa1.person_id
542 FROM per_all_assignments_f paa1
543 WHERE paa1.assignment_id=p_assignment_id
544 AND paa1.business_group_id=p_business_group_id
545 AND p_effective_date BETWEEN paa1.effective_start_date
546 AND paa1.effective_end_date)
547 AND paa.assignment_id=pva.assignment_id
548 AND paa.business_group_id=pva.business_group_id
549 AND paa.business_group_id=p_business_group_id
550 AND p_effective_date BETWEEN paa.effective_start_date
551 AND paa.effective_end_date
552 -- fix for bug 7025352
553 AND p_effective_date BETWEEN pva.effective_start_date
554 AND pva.effective_end_date
555 AND pva.vehicle_repository_id=p_vehicle_repository_id
556 AND paa.assignment_id<>p_assignment_id;
557
558
559
560 l_get_asg c_get_asg%ROWTYPE;
561 l_effective_start_date DATE;
562 l_effective_end_date DATE;
563
564 l_vehicle_allocation_id NUMBER;
565 l_object_version_number NUMBER;
566 l_rec pqp_val_shd.g_rec_type;
567 BEGIN
568
569
570 IF pqp_val_shd.g_old_rec.across_assignments='N' AND
571 p_across_assignments='Y' THEN
572
573 l_rec.usage_type :=p_usage_type;
574 l_rec.capital_contribution :=p_capital_contribution;
575 l_rec.private_contribution :=p_private_contribution;
576 l_rec.default_vehicle :=p_default_vehicle;
577 l_rec.fuel_card :=p_fuel_card;
578 l_rec.fuel_card_number :=p_fuel_card_number;
579 l_rec.calculation_method :=p_calculation_method;
580 l_rec.rates_table_id :=p_rates_table_id;
581 l_rec.element_type_id :=p_element_type_id;
582 l_rec.private_use_flag :=p_private_use_flag;
583 l_rec.insurance_number :=p_insurance_number;
584 l_rec.insurance_expiry_date :=p_insurance_expiry_date;
585 l_rec.val_attribute_category :=p_val_attribute_category;
586 l_rec.val_attribute1 :=p_val_attribute1;
587 l_rec.val_attribute2 :=p_val_attribute2;
588 l_rec.val_attribute3 :=p_val_attribute3;
589 l_rec.val_attribute4 :=p_val_attribute4;
590 l_rec.val_attribute5 :=p_val_attribute5;
591 l_rec.val_attribute6 :=p_val_attribute6;
592 l_rec.val_attribute7 :=p_val_attribute7;
593 l_rec.val_attribute8 :=p_val_attribute8;
594 l_rec.val_attribute9 :=p_val_attribute9;
595 l_rec.val_attribute10 :=p_val_attribute10;
596 l_rec.val_attribute11 :=p_val_attribute11;
597 l_rec.val_attribute12 :=p_val_attribute12;
598 l_rec.val_attribute13 :=p_val_attribute13;
599 l_rec.val_attribute14 :=p_val_attribute14;
600 l_rec.val_attribute15 :=p_val_attribute15;
601 l_rec.val_attribute16 :=p_val_attribute16;
602 l_rec.val_attribute17 :=p_val_attribute17;
603 l_rec.val_attribute18 :=p_val_attribute18;
604 l_rec.val_attribute19 :=p_val_attribute19;
605 l_rec.val_attribute20 :=p_val_attribute20;
606 l_rec.val_information_category :=p_val_information_category ;
607 l_rec.val_information1 :=p_val_information1;
608 l_rec.val_information2 :=p_val_information2;
609 l_rec.val_information3 :=p_val_information3;
610 l_rec.val_information4 :=p_val_information4;
611 l_rec.val_information5 :=p_val_information5;
612 l_rec.val_information6 :=p_val_information6;
613 l_rec.val_information7 :=p_val_information7;
614 l_rec.val_information8 :=p_val_information8;
615 l_rec.val_information9 :=p_val_information9;
616 l_rec.val_information10 :=p_val_information10;
617 l_rec.val_information11 :=p_val_information11;
618 l_rec.val_information12 :=p_val_information12;
619 l_rec.val_information13 :=p_val_information13;
620 l_rec.val_information14 :=p_val_information14;
621 l_rec.val_information15 :=p_val_information15;
622 l_rec.val_information16 :=p_val_information16;
623 l_rec.val_information17 :=p_val_information17;
624 l_rec.val_information18 :=p_val_information18;
625 l_rec.val_information19 :=p_val_information19;
626 l_rec.val_information20 :=p_val_information20;
627 l_rec.fuel_benefit :=p_fuel_benefit ;
628 l_rec.sliding_rates_info :=p_sliding_rates_info ;
629
630
631 convert_defs
632 (p_rec =>l_rec
633 ) ;
634
635 pqp_veh_multi_alloc.create_veh_multi_alloc
636 (p_validate =>p_validate
637 ,p_effective_date => p_effective_date
638 ,p_assignment_id => p_assignment_id
639 ,p_business_group_id => p_business_group_id
640 ,p_vehicle_repository_id => p_vehicle_repository_id
641 ,p_across_assignments => p_across_assignments
642 ,p_usage_type => l_rec.usage_type
643 ,p_capital_contribution => l_rec.capital_contribution
644 ,p_private_contribution => l_rec.private_contribution
645 ,p_default_vehicle => l_rec.default_vehicle
646 ,p_fuel_card => l_rec.fuel_card
647 ,p_fuel_card_number => l_rec.fuel_card_number
648 ,p_calculation_method => l_rec.calculation_method
649 ,p_rates_table_id => l_rec.rates_table_id
650 ,p_element_type_id => l_rec.element_type_id
651 ,p_private_use_flag => l_rec.private_use_flag
652 ,p_insurance_number => l_rec.insurance_number
653 ,p_insurance_expiry_date => l_rec.insurance_expiry_date
654 ,p_val_attribute_category => l_rec.val_attribute_category
655 ,p_val_attribute1 => l_rec.val_attribute1
656 ,p_val_attribute2 => l_rec.val_attribute2
657 ,p_val_attribute3 => l_rec.val_attribute3
658 ,p_val_attribute4 => l_rec.val_attribute4
659 ,p_val_attribute5 => l_rec.val_attribute5
660 ,p_val_attribute6 => l_rec.val_attribute6
661 ,p_val_attribute7 => l_rec.val_attribute7
662 ,p_val_attribute8 => l_rec.val_attribute8
663 ,p_val_attribute9 => l_rec.val_attribute9
664 ,p_val_attribute10 => l_rec.val_attribute10
665 ,p_val_attribute11 => l_rec.val_attribute11
666 ,p_val_attribute12 => l_rec.val_attribute12
667 ,p_val_attribute13 => l_rec.val_attribute13
668 ,p_val_attribute14 => l_rec.val_attribute14
669 ,p_val_attribute15 => l_rec.val_attribute15
670 ,p_val_attribute16 => l_rec.val_attribute16
671 ,p_val_attribute17 => l_rec.val_attribute17
672 ,p_val_attribute18 => l_rec.val_attribute18
673 ,p_val_attribute19 => l_rec.val_attribute19
674 ,p_val_attribute20 => l_rec.val_attribute20
675 ,p_val_information_category => l_rec.val_information_category
676 ,p_val_information1 => l_rec.val_information1
677 ,p_val_information2 => l_rec.val_information2
678 ,p_val_information3 => l_rec.val_information3
679 ,p_val_information4 => l_rec.val_information4
680 ,p_val_information5 => l_rec.val_information5
681 ,p_val_information6 => l_rec.val_information6
682 ,p_val_information7 => l_rec.val_information7
683 ,p_val_information8 => l_rec.val_information8
684 ,p_val_information9 => l_rec.val_information9
685 ,p_val_information10 => l_rec.val_information10
686 ,p_val_information11 => l_rec.val_information11
687 ,p_val_information12 => l_rec.val_information12
688 ,p_val_information13 => l_rec.val_information13
689 ,p_val_information14 => l_rec.val_information14
690 ,p_val_information15 => l_rec.val_information15
691 ,p_val_information16 => l_rec.val_information16
692 ,p_val_information17 => l_rec.val_information17
693 ,p_val_information18 => l_rec.val_information18
694 ,p_val_information19 => l_rec.val_information19
695 ,p_val_information20 => l_rec.val_information20
696 ,p_fuel_benefit => l_rec.fuel_benefit
697 ,p_sliding_rates_info => l_rec.sliding_rates_info
698 );
699
700 ELSE
701 OPEN c_get_asg;
702 LOOP
703 FETCH c_get_asg INTO l_get_asg;
704 EXIT WHEN c_get_asg%NOTFOUND;
705
706 l_object_version_number:=l_get_asg.ovn;
707 pqp_val_upd.upd
708 (p_effective_date => p_effective_date
709 ,p_datetrack_mode => p_datetrack_mode
710 ,p_vehicle_allocation_id => l_get_asg.vehicle_allocation_id
711 ,p_object_version_number =>l_object_version_number -- l_get_asg.ovn
712 ,p_assignment_id => l_get_asg.assignment_id
713 ,p_business_group_id => p_business_group_id
714 ,p_vehicle_repository_id => p_vehicle_repository_id
715 ,p_across_assignments => p_across_assignments
716 ,p_usage_type => p_usage_type
717 ,p_capital_contribution => p_capital_contribution
718 ,p_private_contribution => p_private_contribution
719 ,p_default_vehicle => p_default_vehicle
720 ,p_fuel_card => p_fuel_card
721 ,p_fuel_card_number => p_fuel_card_number
722 ,p_calculation_method => p_calculation_method
723 ,p_rates_table_id => p_rates_table_id
724 ,p_element_type_id => p_element_type_id
725 ,p_private_use_flag => p_private_use_flag
726 ,p_insurance_number => p_insurance_number
727 ,p_insurance_expiry_date => p_insurance_expiry_date
728 ,p_val_attribute_category => p_val_attribute_category
729 ,p_val_attribute1 => p_val_attribute1
730 ,p_val_attribute2 => p_val_attribute2
731 ,p_val_attribute3 => p_val_attribute3
732 ,p_val_attribute4 => p_val_attribute4
733 ,p_val_attribute5 => p_val_attribute5
734 ,p_val_attribute6 => p_val_attribute6
735 ,p_val_attribute7 => p_val_attribute7
736 ,p_val_attribute8 => p_val_attribute8
737 ,p_val_attribute9 => p_val_attribute9
738 ,p_val_attribute10 => p_val_attribute10
739 ,p_val_attribute11 => p_val_attribute11
740 ,p_val_attribute12 => p_val_attribute12
741 ,p_val_attribute13 => p_val_attribute13
742 ,p_val_attribute14 => p_val_attribute14
743 ,p_val_attribute15 => p_val_attribute15
744 ,p_val_attribute16 => p_val_attribute16
745 ,p_val_attribute17 => p_val_attribute17
746 ,p_val_attribute18 => p_val_attribute18
747 ,p_val_attribute19 => p_val_attribute19
748 ,p_val_attribute20 => p_val_attribute20
749 ,p_val_information_category => p_val_information_category
750 ,p_val_information1 => p_val_information1
751 ,p_val_information2 => p_val_information2
752 ,p_val_information3 => p_val_information3
753 ,p_val_information4 => p_val_information4
754 ,p_val_information5 => p_val_information5
755 ,p_val_information6 => p_val_information6
756 ,p_val_information7 => p_val_information7
757 ,p_val_information8 => p_val_information8
758 ,p_val_information9 => p_val_information9
759 ,p_val_information10 => p_val_information10
760 ,p_val_information11 => p_val_information11
761 ,p_val_information12 => p_val_information12
762 ,p_val_information13 => p_val_information13
763 ,p_val_information14 => p_val_information14
764 ,p_val_information15 => p_val_information15
765 ,p_val_information16 => p_val_information16
766 ,p_val_information17 => p_val_information17
767 ,p_val_information18 => p_val_information18
768 ,p_val_information19 => p_val_information19
769 ,p_val_information20 => p_val_information20
770 ,p_fuel_benefit => p_fuel_benefit
771 ,p_sliding_rates_info =>p_sliding_rates_info
772 ,p_effective_start_date => l_effective_start_date
773 ,p_effective_end_date => l_effective_end_date
774 );
775 END LOOP;
776 CLOSE c_get_asg;
777
778 END IF;
779
780 END update_veh_multi_alloc;
781
782
783 --
784 -- ----------------------------------------------------------------------------
785 -- |-------------------< DELETE_VEHICLE_MULTIPLE_ALLOCATION >----------------|
786 -- ----------------------------------------------------------------------------
787 procedure delete_veh_multi_alloc
788 (p_validate in boolean
789 ,p_effective_date in date
790 ,p_datetrack_mode in varchar2
791 ,p_vehicle_allocation_id in number
792 )
793 IS
794 CURSOR c_get_asg
795 IS
796 SELECT paa.assignment_id
797 ,pva.object_version_number ovn
798 FROM per_all_assignments_f paa
799 ,pqp_vehicle_allocations_f pva
800 WHERE EXISTS (SELECT paa1.person_id
801 FROM per_all_assignments_f paa1
802 ,pqp_vehicle_allocations_f pva1
803 WHERE pva1.vehicle_allocation_id=p_vehicle_allocation_id
804 AND pva1.vehicle_repository_id=pva.vehicle_repository_id
805 AND paa1.assignment_id=pva1.assignment_id
806 AND paa1.business_group_id=pva1.business_group_id
807 AND p_effective_date BETWEEN paa1.effective_start_date
808 AND paa1.effective_end_date
809 AND p_effective_date BETWEEN pva1.effective_start_date
810 AND pva1.effective_end_date)
811 AND paa.assignment_id=pva.assignment_id
812 AND paa.business_group_id=pva.business_group_id
813 AND p_effective_date BETWEEN paa.effective_start_date
814 AND paa.effective_end_date;
815
816 l_get_asg c_get_asg%ROWTYPE;
817 BEGIN
818 OPEN c_get_asg;
819 LOOP
820 FETCH c_get_asg INTO l_get_asg;
821 -- fnd_message.raise_error;
822 EXIT;
823 END LOOP;
824 CLOSE c_get_asg;
825
826 END delete_veh_multi_alloc;
827
828 end PQP_VEH_MULTI_ALLOC;