[Home] [Help]
PACKAGE BODY: APPS.PQP_VEH_MULTI_ALLOC
Source
1 Package Body PQP_VEH_MULTI_ALLOC as
2 /* $Header: pqvalmul.pkb 120.2 2011/04/26 11:46:37 apjaiswa noship $ */
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 -- Added where condition to prevent vehicle allocation to
358 -- assignments other than ACTIVE_ASSIGN and ACTIVE_CWK Bug#12340048
359 AND paa.assignment_status_type_id IN (select
360 assignment_status_type_id from PER_ASSIGNMENT_STATUS_TYPES past
361 where past.per_system_status IN ('ACTIVE_ASSIGN', 'ACTIVE_CWK'))
362 AND p_effective_date BETWEEN paa.effective_start_date
363 AND paa.effective_end_date;
364 l_get_asg c_get_asg%ROWTYPE;
365 l_vehicle_allocation_id NUMBER;
366 l_object_version_number NUMBER;
367 l_effective_start_date DATE;
368 l_effective_end_date DATE;
369 begin
370 hr_utility.set_location('Entering:'|| l_proc, 10);
371 --
372
373
374 OPEN c_get_asg;
375 LOOP
376 FETCH c_get_asg INTO l_get_asg;
377 EXIT WHEN c_get_asg%NOTFOUND;
378
379 pqp_val_ins.ins
380 (p_effective_date => p_effective_date
381 ,p_assignment_id => l_get_asg.assignment_id
382 ,p_business_group_id => p_business_group_id
383 ,p_vehicle_repository_id => p_vehicle_repository_id
384 ,p_across_assignments => p_across_assignments
385 ,p_usage_type => p_usage_type
386 ,p_capital_contribution => p_capital_contribution
387 ,p_private_contribution => p_private_contribution
388 ,p_default_vehicle => p_default_vehicle
389 ,p_fuel_card => p_fuel_card
390 ,p_fuel_card_number => p_fuel_card_number
391 ,p_calculation_method => p_calculation_method
392 ,p_rates_table_id => p_rates_table_id
393 ,p_element_type_id => p_element_type_id
394 ,p_private_use_flag => p_private_use_flag
395 ,p_insurance_number => p_insurance_number
396 ,p_insurance_expiry_date => p_insurance_expiry_date
397 ,p_val_attribute_category => p_val_attribute_category
398 ,p_val_attribute1 => p_val_attribute1
399 ,p_val_attribute2 => p_val_attribute2
400 ,p_val_attribute3 => p_val_attribute3
401 ,p_val_attribute4 => p_val_attribute4
402 ,p_val_attribute5 => p_val_attribute5
403 ,p_val_attribute6 => p_val_attribute6
404 ,p_val_attribute7 => p_val_attribute7
405 ,p_val_attribute8 => p_val_attribute8
406 ,p_val_attribute9 => p_val_attribute9
407 ,p_val_attribute10 => p_val_attribute10
408 ,p_val_attribute11 => p_val_attribute11
409 ,p_val_attribute12 => p_val_attribute12
410 ,p_val_attribute13 => p_val_attribute13
411 ,p_val_attribute14 => p_val_attribute14
412 ,p_val_attribute15 => p_val_attribute15
413 ,p_val_attribute16 => p_val_attribute16
414 ,p_val_attribute17 => p_val_attribute17
415 ,p_val_attribute18 => p_val_attribute18
416 ,p_val_attribute19 => p_val_attribute19
417 ,p_val_attribute20 => p_val_attribute20
418 ,p_val_information_category => p_val_information_category
419 ,p_val_information1 => p_val_information1
420 ,p_val_information2 => p_val_information2
421 ,p_val_information3 => p_val_information3
422 ,p_val_information4 => p_val_information4
423 ,p_val_information5 => p_val_information5
424 ,p_val_information6 => p_val_information6
425 ,p_val_information7 => p_val_information7
426 ,p_val_information8 => p_val_information8
427 ,p_val_information9 => p_val_information9
428 ,p_val_information10 => p_val_information10
429 ,p_val_information11 => p_val_information11
430 ,p_val_information12 => p_val_information12
431 ,p_val_information13 => p_val_information13
432 ,p_val_information14 => p_val_information14
433 ,p_val_information15 => p_val_information15
434 ,p_val_information16 => p_val_information16
435 ,p_val_information17 => p_val_information17
436 ,p_val_information18 => p_val_information18
437 ,p_val_information19 => p_val_information19
438 ,p_val_information20 => p_val_information20
439 ,p_fuel_benefit => p_fuel_benefit
440 ,p_sliding_rates_info => p_sliding_rates_info
441 ,p_vehicle_allocation_id => l_vehicle_allocation_id
442 ,p_object_version_number => l_object_version_number
443 ,p_effective_start_date => l_effective_start_date
444 ,p_effective_end_date => l_effective_end_date
445
446 );
447
448 END LOOP;
449 CLOSE c_get_asg;
450
451 hr_utility.set_location(' Leaving:'||l_proc, 70);
452 exception
453 when others then
454 --
455 -- A validation or unexpected error has occured
456 --
457 --rollback to <BUS_PROCESS_NAME>
458 hr_utility.set_location(' Leaving:'||l_proc, 90);
459 raise;
460 end create_veh_multi_alloc;
461 --
462
463
464 --
465 -- ----------------------------------------------------------------------------
466 -- |------------------< UPDATE_VEHICLE_MULTIPLE_ALLOCATION >------------------|
467 -- ----------------------------------------------------------------------------
468
469 procedure update_veh_multi_alloc
470 (p_validate in boolean
471 ,p_effective_date in date
472 ,p_datetrack_mode in varchar2
473 ,p_vehicle_allocation_id in number
474 ,p_object_version_number in out nocopy number
475 ,p_assignment_id in number
476 ,p_business_group_id in number
477 ,p_vehicle_repository_id in number
478 ,p_across_assignments in varchar2
479 ,p_usage_type in varchar2
480 ,p_capital_contribution in number
481 ,p_private_contribution in number
482 ,p_default_vehicle in varchar2
483 ,p_fuel_card in varchar2
484 ,p_fuel_card_number in varchar2
485 ,p_calculation_method in varchar2
486 ,p_rates_table_id in number
487 ,p_element_type_id in number
488 ,p_private_use_flag in varchar2
489 ,p_insurance_number in varchar2
490 ,p_insurance_expiry_date in date
491 ,p_val_attribute_category in varchar2
492 ,p_val_attribute1 in varchar2
493 ,p_val_attribute2 in varchar2
494 ,p_val_attribute3 in varchar2
495 ,p_val_attribute4 in varchar2
496 ,p_val_attribute5 in varchar2
497 ,p_val_attribute6 in varchar2
498 ,p_val_attribute7 in varchar2
499 ,p_val_attribute8 in varchar2
500 ,p_val_attribute9 in varchar2
501 ,p_val_attribute10 in varchar2
502 ,p_val_attribute11 in varchar2
503 ,p_val_attribute12 in varchar2
504 ,p_val_attribute13 in varchar2
505 ,p_val_attribute14 in varchar2
506 ,p_val_attribute15 in varchar2
507 ,p_val_attribute16 in varchar2
508 ,p_val_attribute17 in varchar2
509 ,p_val_attribute18 in varchar2
510 ,p_val_attribute19 in varchar2
511 ,p_val_attribute20 in varchar2
512 ,p_val_information_category in varchar2
513 ,p_val_information1 in varchar2
514 ,p_val_information2 in varchar2
515 ,p_val_information3 in varchar2
516 ,p_val_information4 in varchar2
517 ,p_val_information5 in varchar2
518 ,p_val_information6 in varchar2
519 ,p_val_information7 in varchar2
520 ,p_val_information8 in varchar2
521 ,p_val_information9 in varchar2
522 ,p_val_information10 in varchar2
523 ,p_val_information11 in varchar2
524 ,p_val_information12 in varchar2
525 ,p_val_information13 in varchar2
526 ,p_val_information14 in varchar2
527 ,p_val_information15 in varchar2
528 ,p_val_information16 in varchar2
529 ,p_val_information17 in varchar2
530 ,p_val_information18 in varchar2
531 ,p_val_information19 in varchar2
532 ,p_val_information20 in varchar2
533 ,p_fuel_benefit in varchar2
534 ,p_sliding_rates_info in varchar2
535 )
536 IS
537
538
539 CURSOR c_get_asg
540 IS
541 SELECT paa.assignment_id
542 ,pva.object_version_number ovn
543 ,pva.vehicle_allocation_id
544 FROM per_all_assignments_f paa
545 ,pqp_vehicle_allocations_f pva
546 WHERE paa.person_id =(SELECT paa1.person_id
547 FROM per_all_assignments_f paa1
548 WHERE paa1.assignment_id=p_assignment_id
549 AND paa1.business_group_id=p_business_group_id
550 AND p_effective_date BETWEEN paa1.effective_start_date
551 AND paa1.effective_end_date)
552 AND paa.assignment_id=pva.assignment_id
553 AND paa.business_group_id=pva.business_group_id
554 AND paa.business_group_id=p_business_group_id
555 AND p_effective_date BETWEEN paa.effective_start_date
556 AND paa.effective_end_date
557 -- fix for bug 7025352
558 AND p_effective_date BETWEEN pva.effective_start_date
559 AND pva.effective_end_date
560 AND pva.vehicle_repository_id=p_vehicle_repository_id
561 AND paa.assignment_id<>p_assignment_id;
562
563
564
565 l_get_asg c_get_asg%ROWTYPE;
566 l_effective_start_date DATE;
567 l_effective_end_date DATE;
568
569 l_vehicle_allocation_id NUMBER;
570 l_object_version_number NUMBER;
571 l_rec pqp_val_shd.g_rec_type;
572 BEGIN
573
574
575 IF pqp_val_shd.g_old_rec.across_assignments='N' AND
576 p_across_assignments='Y' THEN
577
578 l_rec.usage_type :=p_usage_type;
579 l_rec.capital_contribution :=p_capital_contribution;
580 l_rec.private_contribution :=p_private_contribution;
581 l_rec.default_vehicle :=p_default_vehicle;
582 l_rec.fuel_card :=p_fuel_card;
583 l_rec.fuel_card_number :=p_fuel_card_number;
584 l_rec.calculation_method :=p_calculation_method;
585 l_rec.rates_table_id :=p_rates_table_id;
586 l_rec.element_type_id :=p_element_type_id;
587 l_rec.private_use_flag :=p_private_use_flag;
588 l_rec.insurance_number :=p_insurance_number;
589 l_rec.insurance_expiry_date :=p_insurance_expiry_date;
590 l_rec.val_attribute_category :=p_val_attribute_category;
591 l_rec.val_attribute1 :=p_val_attribute1;
592 l_rec.val_attribute2 :=p_val_attribute2;
593 l_rec.val_attribute3 :=p_val_attribute3;
594 l_rec.val_attribute4 :=p_val_attribute4;
595 l_rec.val_attribute5 :=p_val_attribute5;
596 l_rec.val_attribute6 :=p_val_attribute6;
597 l_rec.val_attribute7 :=p_val_attribute7;
598 l_rec.val_attribute8 :=p_val_attribute8;
599 l_rec.val_attribute9 :=p_val_attribute9;
600 l_rec.val_attribute10 :=p_val_attribute10;
601 l_rec.val_attribute11 :=p_val_attribute11;
602 l_rec.val_attribute12 :=p_val_attribute12;
603 l_rec.val_attribute13 :=p_val_attribute13;
604 l_rec.val_attribute14 :=p_val_attribute14;
605 l_rec.val_attribute15 :=p_val_attribute15;
606 l_rec.val_attribute16 :=p_val_attribute16;
607 l_rec.val_attribute17 :=p_val_attribute17;
608 l_rec.val_attribute18 :=p_val_attribute18;
609 l_rec.val_attribute19 :=p_val_attribute19;
610 l_rec.val_attribute20 :=p_val_attribute20;
611 l_rec.val_information_category :=p_val_information_category ;
612 l_rec.val_information1 :=p_val_information1;
613 l_rec.val_information2 :=p_val_information2;
614 l_rec.val_information3 :=p_val_information3;
615 l_rec.val_information4 :=p_val_information4;
616 l_rec.val_information5 :=p_val_information5;
617 l_rec.val_information6 :=p_val_information6;
618 l_rec.val_information7 :=p_val_information7;
619 l_rec.val_information8 :=p_val_information8;
620 l_rec.val_information9 :=p_val_information9;
621 l_rec.val_information10 :=p_val_information10;
622 l_rec.val_information11 :=p_val_information11;
623 l_rec.val_information12 :=p_val_information12;
624 l_rec.val_information13 :=p_val_information13;
625 l_rec.val_information14 :=p_val_information14;
626 l_rec.val_information15 :=p_val_information15;
627 l_rec.val_information16 :=p_val_information16;
628 l_rec.val_information17 :=p_val_information17;
629 l_rec.val_information18 :=p_val_information18;
630 l_rec.val_information19 :=p_val_information19;
631 l_rec.val_information20 :=p_val_information20;
632 l_rec.fuel_benefit :=p_fuel_benefit ;
633 l_rec.sliding_rates_info :=p_sliding_rates_info ;
634
635
636 convert_defs
637 (p_rec =>l_rec
638 ) ;
639
640 pqp_veh_multi_alloc.create_veh_multi_alloc
641 (p_validate =>p_validate
642 ,p_effective_date => p_effective_date
643 ,p_assignment_id => p_assignment_id
644 ,p_business_group_id => p_business_group_id
645 ,p_vehicle_repository_id => p_vehicle_repository_id
646 ,p_across_assignments => p_across_assignments
647 ,p_usage_type => l_rec.usage_type
648 ,p_capital_contribution => l_rec.capital_contribution
649 ,p_private_contribution => l_rec.private_contribution
650 ,p_default_vehicle => l_rec.default_vehicle
651 ,p_fuel_card => l_rec.fuel_card
652 ,p_fuel_card_number => l_rec.fuel_card_number
653 ,p_calculation_method => l_rec.calculation_method
654 ,p_rates_table_id => l_rec.rates_table_id
655 ,p_element_type_id => l_rec.element_type_id
656 ,p_private_use_flag => l_rec.private_use_flag
657 ,p_insurance_number => l_rec.insurance_number
658 ,p_insurance_expiry_date => l_rec.insurance_expiry_date
659 ,p_val_attribute_category => l_rec.val_attribute_category
660 ,p_val_attribute1 => l_rec.val_attribute1
661 ,p_val_attribute2 => l_rec.val_attribute2
662 ,p_val_attribute3 => l_rec.val_attribute3
663 ,p_val_attribute4 => l_rec.val_attribute4
664 ,p_val_attribute5 => l_rec.val_attribute5
665 ,p_val_attribute6 => l_rec.val_attribute6
666 ,p_val_attribute7 => l_rec.val_attribute7
667 ,p_val_attribute8 => l_rec.val_attribute8
668 ,p_val_attribute9 => l_rec.val_attribute9
669 ,p_val_attribute10 => l_rec.val_attribute10
670 ,p_val_attribute11 => l_rec.val_attribute11
671 ,p_val_attribute12 => l_rec.val_attribute12
672 ,p_val_attribute13 => l_rec.val_attribute13
673 ,p_val_attribute14 => l_rec.val_attribute14
674 ,p_val_attribute15 => l_rec.val_attribute15
675 ,p_val_attribute16 => l_rec.val_attribute16
676 ,p_val_attribute17 => l_rec.val_attribute17
677 ,p_val_attribute18 => l_rec.val_attribute18
678 ,p_val_attribute19 => l_rec.val_attribute19
679 ,p_val_attribute20 => l_rec.val_attribute20
680 ,p_val_information_category => l_rec.val_information_category
681 ,p_val_information1 => l_rec.val_information1
682 ,p_val_information2 => l_rec.val_information2
683 ,p_val_information3 => l_rec.val_information3
684 ,p_val_information4 => l_rec.val_information4
685 ,p_val_information5 => l_rec.val_information5
686 ,p_val_information6 => l_rec.val_information6
687 ,p_val_information7 => l_rec.val_information7
688 ,p_val_information8 => l_rec.val_information8
689 ,p_val_information9 => l_rec.val_information9
690 ,p_val_information10 => l_rec.val_information10
691 ,p_val_information11 => l_rec.val_information11
692 ,p_val_information12 => l_rec.val_information12
693 ,p_val_information13 => l_rec.val_information13
694 ,p_val_information14 => l_rec.val_information14
695 ,p_val_information15 => l_rec.val_information15
696 ,p_val_information16 => l_rec.val_information16
697 ,p_val_information17 => l_rec.val_information17
698 ,p_val_information18 => l_rec.val_information18
699 ,p_val_information19 => l_rec.val_information19
700 ,p_val_information20 => l_rec.val_information20
701 ,p_fuel_benefit => l_rec.fuel_benefit
702 ,p_sliding_rates_info => l_rec.sliding_rates_info
703 );
704
705 ELSE
706 OPEN c_get_asg;
707 LOOP
708 FETCH c_get_asg INTO l_get_asg;
709 EXIT WHEN c_get_asg%NOTFOUND;
710
711 l_object_version_number:=l_get_asg.ovn;
712 pqp_val_upd.upd
713 (p_effective_date => p_effective_date
714 ,p_datetrack_mode => p_datetrack_mode
715 ,p_vehicle_allocation_id => l_get_asg.vehicle_allocation_id
716 ,p_object_version_number =>l_object_version_number -- l_get_asg.ovn
717 ,p_assignment_id => l_get_asg.assignment_id
718 ,p_business_group_id => p_business_group_id
719 ,p_vehicle_repository_id => p_vehicle_repository_id
720 ,p_across_assignments => p_across_assignments
721 ,p_usage_type => p_usage_type
722 ,p_capital_contribution => p_capital_contribution
723 ,p_private_contribution => p_private_contribution
724 ,p_default_vehicle => p_default_vehicle
725 ,p_fuel_card => p_fuel_card
726 ,p_fuel_card_number => p_fuel_card_number
727 ,p_calculation_method => p_calculation_method
728 ,p_rates_table_id => p_rates_table_id
729 ,p_element_type_id => p_element_type_id
730 ,p_private_use_flag => p_private_use_flag
731 ,p_insurance_number => p_insurance_number
732 ,p_insurance_expiry_date => p_insurance_expiry_date
733 ,p_val_attribute_category => p_val_attribute_category
734 ,p_val_attribute1 => p_val_attribute1
735 ,p_val_attribute2 => p_val_attribute2
736 ,p_val_attribute3 => p_val_attribute3
737 ,p_val_attribute4 => p_val_attribute4
738 ,p_val_attribute5 => p_val_attribute5
739 ,p_val_attribute6 => p_val_attribute6
740 ,p_val_attribute7 => p_val_attribute7
741 ,p_val_attribute8 => p_val_attribute8
742 ,p_val_attribute9 => p_val_attribute9
743 ,p_val_attribute10 => p_val_attribute10
744 ,p_val_attribute11 => p_val_attribute11
745 ,p_val_attribute12 => p_val_attribute12
746 ,p_val_attribute13 => p_val_attribute13
747 ,p_val_attribute14 => p_val_attribute14
748 ,p_val_attribute15 => p_val_attribute15
749 ,p_val_attribute16 => p_val_attribute16
750 ,p_val_attribute17 => p_val_attribute17
751 ,p_val_attribute18 => p_val_attribute18
752 ,p_val_attribute19 => p_val_attribute19
753 ,p_val_attribute20 => p_val_attribute20
754 ,p_val_information_category => p_val_information_category
755 ,p_val_information1 => p_val_information1
756 ,p_val_information2 => p_val_information2
757 ,p_val_information3 => p_val_information3
758 ,p_val_information4 => p_val_information4
759 ,p_val_information5 => p_val_information5
760 ,p_val_information6 => p_val_information6
761 ,p_val_information7 => p_val_information7
762 ,p_val_information8 => p_val_information8
763 ,p_val_information9 => p_val_information9
764 ,p_val_information10 => p_val_information10
765 ,p_val_information11 => p_val_information11
766 ,p_val_information12 => p_val_information12
767 ,p_val_information13 => p_val_information13
768 ,p_val_information14 => p_val_information14
769 ,p_val_information15 => p_val_information15
770 ,p_val_information16 => p_val_information16
771 ,p_val_information17 => p_val_information17
772 ,p_val_information18 => p_val_information18
773 ,p_val_information19 => p_val_information19
774 ,p_val_information20 => p_val_information20
775 ,p_fuel_benefit => p_fuel_benefit
776 ,p_sliding_rates_info =>p_sliding_rates_info
777 ,p_effective_start_date => l_effective_start_date
778 ,p_effective_end_date => l_effective_end_date
779 );
780 END LOOP;
781 CLOSE c_get_asg;
782
783 END IF;
784
785 END update_veh_multi_alloc;
786
787
788 --
789 -- ----------------------------------------------------------------------------
790 -- |-------------------< DELETE_VEHICLE_MULTIPLE_ALLOCATION >----------------|
791 -- ----------------------------------------------------------------------------
792 procedure delete_veh_multi_alloc
793 (p_validate in boolean
794 ,p_effective_date in date
795 ,p_datetrack_mode in varchar2
796 ,p_vehicle_allocation_id in number
797 )
798 IS
799 CURSOR c_get_asg
800 IS
801 SELECT paa.assignment_id
802 ,pva.object_version_number ovn
803 FROM per_all_assignments_f paa
804 ,pqp_vehicle_allocations_f pva
805 WHERE EXISTS (SELECT paa1.person_id
806 FROM per_all_assignments_f paa1
807 ,pqp_vehicle_allocations_f pva1
808 WHERE pva1.vehicle_allocation_id=p_vehicle_allocation_id
809 AND pva1.vehicle_repository_id=pva.vehicle_repository_id
810 AND paa1.assignment_id=pva1.assignment_id
811 AND paa1.business_group_id=pva1.business_group_id
812 AND p_effective_date BETWEEN paa1.effective_start_date
813 AND paa1.effective_end_date
814 AND p_effective_date BETWEEN pva1.effective_start_date
815 AND pva1.effective_end_date)
816 AND paa.assignment_id=pva.assignment_id
817 AND paa.business_group_id=pva.business_group_id
818 AND p_effective_date BETWEEN paa.effective_start_date
819 AND paa.effective_end_date;
820
821 l_get_asg c_get_asg%ROWTYPE;
822 BEGIN
823 OPEN c_get_asg;
824 LOOP
825 FETCH c_get_asg INTO l_get_asg;
826 -- fnd_message.raise_error;
827 EXIT;
828 END LOOP;
829 CLOSE c_get_asg;
830
831 END delete_veh_multi_alloc;
832
833 end PQP_VEH_MULTI_ALLOC;