[Home] [Help]
PACKAGE BODY: APPS.PQP_SS_VEHICLE_TRANSACTIONS
Source
1 PACKAGE BODY PQP_SS_VEHICLE_TRANSACTIONS AS
2 /* $Header: pqpssvehinfo.pkb 120.3.12020000.2 2012/07/05 12:17:59 amnaraya ship $*/
3
4
5
6
7 ---Check if extra info exists
8 PROCEDURE IS_EXTRA_INFO_EXISTS (
9 itemtype IN VARCHAR2,
10 itemkey IN VARCHAR2,
11 actid IN NUMBER,
12 funcmode IN VARCHAR2,
13 result OUT NOCOPY VARCHAR2 )
14
15 IS
16 l_transaction_id number;
17 l_attr varchar2(30);
18 l_ignore boolean ;
19 Begin
20
21 --Get the status whether the Extra info exists or not
22 --returns 'Y' or 'N'
23
24 l_attr:= wf_engine.GetItemAttrText(
25 itemtype => itemtype,
26 itemkey =>itemkey,
27 aname =>'PQP_EXTRA_INFO_EXISTS_ATTR',
28 ignore_notfound =>l_ignore);
29
30 --Set the same value to determine the process
31
32 result := 'COMPLETE:'||l_attr ; --'COMPLETE:I' ; --l_attr;
33
34 end;
35
36 ---Get global valie for extra info exist checking
37 PROCEDURE set_extra_info_val (
38 itemtype IN VARCHAR2,
39 itemkey IN VARCHAR2,
40 result OUT NOCOPY VARCHAR2 )
41 IS
42 l_transaction_id number;
43 l_attr varchar2(30);
44 l_ignore boolean ;
45 l_responsibility_id number;
46 l_count number:=0;
47 l_exists VARCHAR2(1);
48 CURSOR c_get_info IS
49 SELECT COUNT(1)
50 FROM pqp_veh_repos_info_types ait, fnd_descr_flex_contexts_vl flv
51 WHERE ait.information_type = flv.descriptive_flex_context_code
52 AND flv.descriptive_flexfield_name IN ( 'Vehicle Repos Extra Info DDF'
53 ,'Vehicle Alloc Extra Info DDF')
54 AND flv.enabled_flag = 'Y'
55 AND exists ( SELECT NULL
56 FROM per_info_type_security its,
57 pqp_veh_repos_info_types ait
58 WHERE its.info_type_table_name IN ( 'PQP_VEH_REPOS_INFO_TYPES'
59 ,'PQP_VEH_ALLOC_INFO_TYPES')
60 AND its.information_type = ait.information_type
61 AND responsibility_id = l_responsibility_id );
62
63 Begin
64
65 --Get the status whether the process is in Update or Insert mode
66
67 l_responsibility_id:= wf_engine.GetItemAttrNumber(
68 itemtype => itemtype,
69 itemkey =>itemkey,
70 aname =>'PQP_RESPONSIBILITY_ID_ATTR',
71 -- actid =>actid ,
72 ignore_notfound =>l_ignore);
73
74 --Set the same value to determine the process
75 OPEN c_get_info;
76 FETCH c_get_info INTO l_count;
77 CLOSE c_get_info;
78
79 IF l_count > 0 THEN
80 l_exists :='Y';
81 ELSE
82 l_exists :='N';
83 END IF;
84
85
86 wf_engine.SetItemAttrText(
87 itemtype => itemtype,
88 itemkey =>itemkey,
89 aname =>'PQP_EXTRA_INFO_EXISTS_ATTR',
90 avalue =>l_exists);
91 result := 'Success'; --'COMPLETE:I' ; --l_attr;
92 end;
93
94
95 --This process is called after the approval process is over
96 --for delete operation
97 --this will end date the vehicle.
98 --Note:This call is not currently used as the delete
99 --operation is done directly.
100 function get_transaction_id
101 (p_transaction_step_id in number) return number is
102 -- --------------------------------------------------------------------------
103 -- declare local variables
104 -- --------------------------------------------------------------------------
105 l_proc varchar2(72);
106 l_transaction_id hr_api_transactions.transaction_id%type;
107 -- cursor to select the transaction_id of the step
108 cursor csr_hats is
109 select hats.transaction_id
110 from hr_api_transaction_steps hats
111 where hats.transaction_step_id = p_transaction_step_id;
112 begin
113
114
115
116 open csr_hats;
117 fetch csr_hats into l_transaction_id;
118 if csr_hats%notfound then
119 -- the transaction step doesn't exist
120 close csr_hats;
121 hr_utility.set_message(801, 'HR_51751_WEB_TRA_STEP_EXISTS');
122 hr_utility.raise_error;
123 end if;
124 close csr_hats;
125 return(l_transaction_id);
126
127
128 hr_utility.set_location(' Leaving:'||l_proc, 10);
129
130
131 end get_transaction_id;
132 PROCEDURE delete_process (
133 p_validate IN BOOLEAN
134 ,p_effective_date IN DATE
135 ,p_person_id IN NUMBER
136 ,p_assignment_id IN NUMBER
137 ,p_business_group_id IN NUMBER
138 ,p_vehicle_allocation_id IN NUMBER
139 ,p_error_status OUT NOCOPY VARCHAR2
140 )
141 --
142 --
143 IS
144
145 l_person_id NUMBER;
146 l_ovn NUMBER :=1;
147
148 CURSOR c_get_ovn (cp_allocation_id NUMBER
149 ,cp_business_group_id NUMBER
150 ,cp_assignment_id NUMBER
151 ,cp_effective_date DATE
152 )
153 IS
154 SELECT pva.object_version_number
155 ,pva.vehicle_repository_id repository_id
156 FROM pqp_vehicle_allocations_f pva
157 WHERE pva.vehicle_allocation_id =cp_allocation_id
158 AND pva.assignment_id =cp_assignment_id
159 AND pva.business_group_id =cp_business_group_id
160 AND rtrim(ltrim(cp_effective_date)) BETWEEN pva.effective_start_date
161 AND pva.effective_end_date;
162
163 CURSOR c_get_tot_users (cp_repository_id NUMBER
164 ,cp_business_group_id NUMBER
165 ,cp_assignment_id NUMBER
166 ,cp_effective_date DATE
167 )
168 IS
169 SELECT COUNT(pva.vehicle_repository_id) usr_count
170 FROM pqp_vehicle_allocations_f pva
171 WHERE pva.vehicle_repository_id = cp_repository_id
172 AND pva.business_group_id =cp_business_group_id
173 AND pva.assignment_id = cp_assignment_id
174 AND rtrim(ltrim(cp_effective_date)) BETWEEN pva.effective_start_date
175 AND pva.effective_end_date;
176
177 CURSOR c_get_rep_ovn (cp_repository_id NUMBER
178 ,cp_business_group_id NUMBER
179 ,cp_effective_date DATE
180 )
181 IS
182 SELECT pvr.object_version_number ovn
183 FROM pqp_vehicle_repository_f pvr
184 WHERE pvr.vehicle_repository_id = cp_repository_id
185 AND pvr.business_group_id = cp_business_group_id
186 AND rtrim(ltrim(cp_effective_date)) BETWEEN pvr.effective_start_date
187 AND pvr.effective_end_date;
188
189 l_get_rep_ovn c_get_rep_ovn%ROWTYPE;
190 l_get_ovn c_get_ovn%ROWTYPE;
191 l_get_tot_users c_get_tot_users%ROWTYPE;
192 l_effective_start_date DATE;
193 l_effective_end_date DATE;
194 BEGIN
195
196 OPEN c_get_ovn (
197 p_vehicle_allocation_id
198 ,p_business_group_id
199 ,p_assignment_id
200 ,p_effective_date
201 );
202 FETCH c_get_ovn INTO l_get_ovn;
203 CLOSE c_get_ovn;
204 OPEN c_get_tot_users (l_get_ovn.repository_id
205 ,p_business_group_id
206 ,p_assignment_id
207 ,p_effective_date
208 );
209 FETCH c_get_tot_users INTO l_get_tot_users;
210 CLOSE c_get_tot_users;
211
212 --Calling delete api for allocation.
213 PQP_VEHICLE_ALLOCATIONS_API.delete_vehicle_allocation(
214 p_validate => p_validate
215 ,p_effective_date => ltrim(rtrim(p_effective_date))
216 ,p_datetrack_mode =>'DELETE'
217 ,p_vehicle_allocation_id =>p_vehicle_allocation_id
218 ,p_object_version_number =>l_get_ovn.object_version_number
219 ,p_effective_start_date =>l_effective_start_date
220 ,p_effective_end_date =>l_effective_end_date
221 );
222
223 IF l_get_tot_users.usr_count = 0 THEN
224 OPEN c_get_rep_ovn (l_get_ovn.repository_id
225 ,p_business_group_id
226 ,p_effective_date
227 );
228 FETCH c_get_rep_ovn INTO l_get_rep_ovn;
229
230 CLOSE c_get_rep_ovn;
231
232 --Callin delete api for vehicles.
233 pqp_vehicle_repository_api.delete_vehicle
234 (p_validate => p_validate
235 ,p_effective_date => ltrim(rtrim(p_effective_date))
236 ,p_datetrack_mode => 'DELETE'
237 ,p_vehicle_repository_id => l_get_ovn.repository_id
238 ,p_object_version_number => l_get_rep_ovn.ovn
239 ,p_effective_start_date => l_effective_start_date
240 ,p_effective_end_date => l_effective_end_date
241 );
242 END IF;
243
244
245
246
247 END;
248
249 --This is not used.(obselete)
250 --This was introduced when the page flow was not
251 --based on work flow process
252 PROCEDURE update_transaction_itemkey (
253 itemtype IN VARCHAR2,
254 itemkey IN VARCHAR2,
255 actid IN NUMBER,
256 funcmode IN VARCHAR2,
257 result OUT NOCOPY VARCHAR2 )
258
259 IS
260 l_transaction_id number;
261 l_attr varchar2(30);
262 l_ignore boolean ;
263 Begin
264 l_transaction_id:= wf_engine.GetItemAttrNumber(itemtype => itemtype,
265 itemkey =>itemkey,
266 aname => 'TRANSACTION_ID',
267 ignore_notfound =>l_ignore);
268
269 l_attr:= wf_engine.GetActivityAttrText(itemtype => itemtype,
270 itemkey =>itemkey,
271 aname =>'PQP_APPROVAL_REQUIRED',
272 actid =>actid , --'PQP_APPROVAL_REQUIRED',
273 ignore_notfound =>l_ignore);
274 wf_engine.SetItemAttrText(itemtype => itemtype,
275 itemkey =>itemkey,
276 aname =>'HR_RUNTIME_APPROVAL_REQ_FLAG',
277 avalue =>'YES');
278
279 UPDATE hr_api_transactions hat
280 set hat.item_key = itemkey
281 WHERE hat.transaction_id=l_transaction_id;
282
283 UPDATE hr_api_transaction_steps hats
284 set hats.item_key = itemkey
285 WHERE hats.transaction_id=l_transaction_id;
286 result :='Y';
287 end;
288
289 FUNCTION get_vehicle_details_hgrid (
290 p_transaction_step_id in varchar2 ) RETURN ref_cursor IS
291 csr ref_cursor;
292 BEGIN
293 OPEN csr FOR
294 SELECT Vehtrn.*,lkp.meaning vehicletype from (SELECT
295 hr_transaction_api.get_number_Value
296 ( p_transaction_step_id,'P_LOGIN_PERSON_ID') login_person_id
297 ,hr_transaction_api.get_number_Value
298 ( p_transaction_step_id, 'P_PERSON_ID' ) person_id
299 ,hr_transaction_api.get_number_Value
300 (p_transaction_step_id,'P_ASSIGNMENT_ID') assignment_id
301 ,hr_transaction_api.get_date_Value
302 (p_transaction_step_id, 'P_EFFECTIVE_DATE' ) effective_date
303 ,hr_transaction_api.get_number_Value
304 (p_transaction_step_id ,'P_ITEM_TYPE') item_type
305 ,hr_transaction_api.get_number_Value
306 (p_transaction_step_id, 'P_item_key') item_key
307 ,hr_transaction_api.get_number_Value
308 (p_transaction_step_id, 'P_ACTIVITY_ID' ) activity_id
309 ,hr_transaction_api.get_varchar2_Value
310 (p_transaction_step_id,'P_REGISTRATION_NUMBER') registration_number
311 ,hr_transaction_api.get_varchar2_Value
312 (p_transaction_step_id, 'P_VEHICLE_OWNERSHIP') vehicle_ownership
313 ,hr_transaction_api.get_varchar2_Value
314 (p_transaction_step_id, 'P_VEHICLE_TYPE' ) vehicle_type
315 ,hr_transaction_api.get_varchar2_Value
316 (p_transaction_step_id, 'P_VEHICLE_ID_NUMBER') vehicle_id_number
317 ,hr_transaction_api.get_number_Value
318 (p_transaction_step_id, 'P_BUSINESS_GROUP_ID') business_group_id
319 ,hr_transaction_api.get_varchar2_Value
320 (p_transaction_step_id, 'P_MAKE') make
321 ,hr_transaction_api.get_number_Value
322 (p_transaction_step_id, 'P_ENGINE_CAPACITY_IN_CC') engine_capacity_in_cc
323 , hr_transaction_api.get_number_Value
324 (p_transaction_step_id, 'P_MODEL_YEAR' ) model_year
325 ,hr_transaction_api.get_varchar2_Value
326 (p_transaction_step_id, 'P_INSURANCE_NUMBER' ) insurance_number
327 ,hr_transaction_api.get_date_Value
328 (p_transaction_step_id, 'P_INSURANCE_EXPIRY_DATE') insurance_expiry_date
329 ,hr_transaction_api.get_varchar2_Value
330 (p_transaction_step_id, 'P_MODEL' ) model
331 ,hr_transaction_api.get_number_value
332 (p_transaction_step_id, 'P_VEHICLE_ALLOCATION_ID') vehicle_allocation_id
333 ,hr_transaction_api.get_number_Value
334 (p_transaction_step_id, 'P_VEHICLE_REPOSITORY_ID' ) vehicle_repository_id
335 FROM DUAL) Vehtrn
336 ,hr_lookups lkp
337 WHERE vehtrn.vehicle_type=lkp.lookup_code
338 AND lkp.lookup_type='PQP_VEHICLE_TYPE' ;
339
340 RETURN csr;
341 END get_vehicle_details_hgrid;
342
343 --
344 --
345 ---This function that get the vehicle user details
346 ---when the back button is clicked.
347 ---The data that are inserted into transaction table is
348 ---again queried to populate all the entered fields.
349 --Here user of the vehicle could be one or many.
350 FUNCTION get_vehicle_usr_details
351 (p_transaction_step_id in VARCHAR2 )
352 RETURN ref_cursor IS
353 csr ref_cursor;
354
355
356 BEGIN
357 OPEN csr FOR
358 SELECT DISTINCT (a.number_value) person_usr_id
359 , b.number_value assignment_usr_id
360 , c.varchar2_value usr_type
361 FROM hr_api_transaction_steps s,
362 hr_api_transaction_values a,
363 hr_api_transaction_steps s1,
364 hr_api_transaction_values b,
365 hr_api_transaction_steps s2,
366 hr_api_transaction_values c
367 WHERE s.transaction_step_id = a.transaction_step_id
368 AND s1.transaction_step_id = b.transaction_step_id
369 AND s2.transaction_step_id = c.transaction_step_id
370 AND s.transaction_step_id = p_transaction_step_id
371 AND s.api_name = 'PQP_SS_VEHICLE_TRANSACTIONS.PROCESS_API'
372 AND a.name like 'P_PERSON_USR_ID%'
373 AND s1.transaction_step_id = p_transaction_step_id
374 AND s1.api_name = 'PQP_SS_VEHICLE_TRANSACTIONS.PROCESS_API'
375 AND b.name like 'P_ASSIGNMENT_USR_ID%'
376 AND s2.transaction_step_id = p_transaction_step_id
377 AND s2.api_name = 'PQP_SS_VEHICLE_TRANSACTIONS.PROCESS_API'
378 AND c.name like 'P_USER_TYPE%'
379 AND substr(a.name,-1) = substr(b.name,-1)
380 AND substr(a.name,-1) = substr(c.name,-1);
381
382 RETURN csr;
383 END;
384
385 --This Function gets all other details related to vehicles
386 --that are there in the transaction table
387 --when back button is clicked.
388
389 FUNCTION get_vehicle_details (
390 p_transaction_step_id in varchar2 ) RETURN ref_cursor IS
391 csr ref_cursor;
392 BEGIN
393 OPEN csr FOR
394 SELECT
395 hr_transaction_api.get_number_Value
396 (p_transaction_step_id,'P_LOGIN_PERSON_ID') login_person_id
397 ,hr_transaction_api.get_number_Value
398 (p_transaction_step_id, 'P_PERSON_ID' ) person_id
399 ,hr_transaction_api.get_number_Value
400 (p_transaction_step_id,'P_ASSIGNMENT_ID') assignment_id
401 ,hr_transaction_api.get_date_Value
402 (p_transaction_step_id, 'P_EFFECTIVE_DATE' ) effective_date
403 ,hr_transaction_api.get_number_Value
404 (p_transaction_step_id ,'P_ITEM_TYPE') item_type
405 ,hr_transaction_api.get_number_Value
406 (p_transaction_step_id, 'P_item_key') item_key
407 ,hr_transaction_api.get_number_Value
408 (p_transaction_step_id, 'P_ACTIVITY_ID' ) activity_id
409 ,hr_transaction_api.get_varchar2_Value
410 (p_transaction_step_id,'P_REGISTRATION_NUMBER') registration_number
411 ,hr_transaction_api.get_varchar2_Value
412 (p_transaction_step_id, 'P_VEHICLE_OWNERSHIP') vehicle_ownership
413 ,hr_transaction_api.get_varchar2_Value
414 (p_transaction_step_id, 'P_VEHICLE_TYPE' ) vehicle_type
415 ,hr_transaction_api.get_varchar2_Value
416 (p_transaction_step_id, 'P_VEHICLE_ID_NUMBER') vehicle_id_number
417 ,hr_transaction_api.get_number_Value
418 (p_transaction_step_id, 'P_BUSINESS_GROUP_ID') business_group_id
419 ,hr_transaction_api.get_varchar2_Value
420 (p_transaction_step_id, 'P_MAKE') make
421 ,hr_transaction_api.get_number_Value
422 (p_transaction_step_id, 'P_ENGINE_CAPACITY_IN_CC') engine_capacity_in_cc
423 , hr_transaction_api.get_number_Value
424 (p_transaction_step_id, 'P_MODEL_YEAR' ) model_year
425 ,hr_transaction_api.get_varchar2_Value
426 (p_transaction_step_id, 'P_INSURANCE_NUMBER' ) insurance_number
427 ,hr_transaction_api.get_date_Value
428 (p_transaction_step_id, 'P_INSURANCE_EXPIRY_DATE') insurance_expiry_date
429 ,hr_transaction_api.get_varchar2_Value
430 (p_transaction_step_id, 'P_MODEL' ) model
431 ,hr_transaction_api.get_varchar2_Value
432 (p_transaction_step_id, 'P_FUEL_TYPE' ) fuel_type
433 ,hr_transaction_api.get_number_Value
434 (p_transaction_step_id, 'P_VEHICLE_REPOSITORY_ID' ) vehicle_repository_id
435 ,hr_transaction_api.get_number_Value
436 (p_transaction_step_id, 'P_OBJECT_VERSION_NUMBER') object_version_number
437 ,hr_transaction_api.get_varchar2_Value
438 (p_transaction_step_id, 'P_CURRENCY_CODE') currency_code
439 ,hr_transaction_api.get_date_Value
440 (p_transaction_step_id, 'P_INITIAL_REGISTRATION') initial_registration
441 ,hr_transaction_api.get_date_Value
442 (p_transaction_step_id, 'P_LAST_REGISTRATION_RENEW_DATE' )
443 last_registration_renew_date
444 ,hr_transaction_api.get_number_Value
445 (p_transaction_step_id, 'P_FISCAL_RATINGS' ) fiscal_ratings
446 ,hr_transaction_api.get_varchar2_Value
447 (p_transaction_step_id, 'P_SHARED_VEHICLE' ) shared_vehicle
448 ,hr_transaction_api.get_varchar2_Value
449 (p_transaction_step_id, 'P_COLOR' ) color
450 ,hr_transaction_api.get_number_Value
451 (p_transaction_step_id, 'P_SEATING_CAPACITY' ) seating_capacity
452 ,hr_transaction_api.get_number_Value
453 (p_transaction_step_id, 'P_WEIGHT' ) weight
454 ,hr_transaction_api.get_varchar2_Value
455 (p_transaction_step_id, 'P_WEIGHT_UOM' ) weight_uom
456 ,hr_transaction_api.get_varchar2_Value
457 (p_transaction_step_id, 'P_TAXATION_METHOD' ) taxation_method
458 ,hr_transaction_api.get_varchar2_Value
459 (p_transaction_step_id, 'P_COMMENTS' ) comments
460 ,hr_transaction_api.get_varchar2_value
461 (p_transaction_step_id, 'P_VRE_ATTRIBUTE_CATEGORY')
462 vre_attribute_category
463 ,hr_transaction_api.get_varchar2_value
464 (p_transaction_step_id, 'P_VRE_ATTRIBUTE1') vre_attribute1
465 ,hr_transaction_api.get_varchar2_value
466 (p_transaction_step_id, 'P_VRE_ATTRIBUTE2') vre_attribute2
467 ,hr_transaction_api.get_varchar2_value
468 (p_transaction_step_id, 'P_VRE_ATTRIBUTE3') vre_attribute3
469 ,hr_transaction_api.get_varchar2_value
470 (p_transaction_step_id, 'P_VRE_ATTRIBUTE4') vre_attribute4
471 ,hr_transaction_api.get_varchar2_value
472 (p_transaction_step_id, 'P_VRE_ATTRIBUTE5') vre_attribute5
473 ,hr_transaction_api.get_varchar2_value
474 (p_transaction_step_id, 'P_VRE_ATTRIBUTE6') vre_attribute6
475 ,hr_transaction_api.get_varchar2_value
476 (p_transaction_step_id, 'P_VRE_ATTRIBUTE7') vre_attribute7
477 ,hr_transaction_api.get_varchar2_value
478 (p_transaction_step_id, 'P_VRE_ATTRIBUTE8') vre_attribute8
479 ,hr_transaction_api.get_varchar2_value
480 (p_transaction_step_id, 'P_VRE_ATTRIBUTE9') vre_attribute9
481 ,hr_transaction_api.get_varchar2_value
482 (p_transaction_step_id, 'P_VRE_ATTRIBUTE10') vre_attribute10
483 ,hr_transaction_api.get_varchar2_value
484 (p_transaction_step_id, 'P_VRE_ATTRIBUTE11')vre_attribute11
485 ,hr_transaction_api.get_varchar2_value
486 (p_transaction_step_id, 'P_VRE_ATTRIBUTE12') vre_attribute12
487 ,hr_transaction_api.get_varchar2_value
488 (p_transaction_step_id, 'P_VRE_ATTRIBUTE13') vre_attribute13
489 ,hr_transaction_api.get_varchar2_value
490 (p_transaction_step_id, 'P_VRE_ATTRIBUTE14') vre_attribute14
491 ,hr_transaction_api.get_varchar2_value
492 (p_transaction_step_id, 'P_VRE_ATTRIBUTE15') vre_attribute15
493 ,hr_transaction_api.get_varchar2_value
494 (p_transaction_step_id, 'P_VRE_ATTRIBUTE16') vre_attribute16
495 ,hr_transaction_api.get_varchar2_value
496 (p_transaction_step_id, 'P_VRE_ATTRIBUTE17') vre_attribute17
497 ,hr_transaction_api.get_varchar2_value
498 (p_transaction_step_id, 'P_VRE_ATTRIBUTE18') vre_attribute18
499 ,hr_transaction_api.get_varchar2_value
500 (p_transaction_step_id, 'P_VRE_ATTRIBUTE19') vre_attribute19
501 ,hr_transaction_api.get_varchar2_value
502 (p_transaction_step_id, 'P_VRE_ATTRIBUTE20') vre_attribute20
503 ,hr_transaction_api.get_varchar2_value
504 (p_transaction_step_id, 'P_VRE_INFORMATION_CATEGORY')
505 vre_information_category
506 ,hr_transaction_api.get_varchar2_value
507 (p_transaction_step_id, 'P_VRE_INFORMATION1') vre_information1
508 ,hr_transaction_api.get_varchar2_value
509 (p_transaction_step_id, 'P_VRE_INFORMATION2') vre_information2
510 ,hr_transaction_api.get_varchar2_value
511 (p_transaction_step_id, 'P_VRE_INFORMATION3') vre_information3
512 ,hr_transaction_api.get_varchar2_value
513 (p_transaction_step_id, 'P_VRE_INFORMATION4') vre_information4
514 ,hr_transaction_api.get_varchar2_value
515 (p_transaction_step_id, 'P_VRE_INFORMATION5') vre_information5
516 ,hr_transaction_api.get_varchar2_value
517 (p_transaction_step_id, 'P_VRE_INFORMATION6') vre_information6
518 ,hr_transaction_api.get_varchar2_value
519 (p_transaction_step_id, 'P_VRE_INFORMATION7') vre_information7
520 ,hr_transaction_api.get_varchar2_value
521 (p_transaction_step_id, 'P_VRE_INFORMATION8') vre_information8
522 ,hr_transaction_api.get_varchar2_value
523 (p_transaction_step_id, 'P_VRE_INFORMATION9') vre_information9
524 ,hr_transaction_api.get_varchar2_value
525 (p_transaction_step_id, 'P_VRE_INFORMATION10') vre_information10
526 ,hr_transaction_api.get_varchar2_value
527 (p_transaction_step_id, 'P_VRE_INFORMATION11') vre_information11
528 ,hr_transaction_api.get_varchar2_value
529 (p_transaction_step_id, 'P_VRE_INFORMATION12') vre_information12
530 ,hr_transaction_api.get_varchar2_value
531 (p_transaction_step_id, 'P_VRE_INFORMATION13') vre_information13
532 ,hr_transaction_api.get_varchar2_value
533 (p_transaction_step_id, 'P_VRE_INFORMATION14') vre_information14
534 ,hr_transaction_api.get_varchar2_value
535 (p_transaction_step_id, 'P_VRE_INFORMATION15') vre_information15
536 ,hr_transaction_api.get_varchar2_value
537 (p_transaction_step_id, 'P_VRE_INFORMATION16') vre_information16
538 ,hr_transaction_api.get_varchar2_value
539 (p_transaction_step_id, 'P_VRE_INFORMATION17') vre_information17
540 ,hr_transaction_api.get_varchar2_value
541 (p_transaction_step_id, 'P_VRE_INFORMATION18') vre_information18
542 ,hr_transaction_api.get_varchar2_value
543 (p_transaction_step_id, 'P_VRE_INFORMATION19') vre_information19
544 ,hr_transaction_api.get_varchar2_value
545 (p_transaction_step_id, 'P_VRE_INFORMATION20') vre_information20
546 ,hr_transaction_api.get_varchar2_value
547 (p_transaction_step_id, 'P_ACROSS_ASSIGNMENTS') across_assignments
548 ,hr_transaction_api.get_varchar2_value
549 (p_transaction_step_id, 'P_USAGE_TYPE') usage_type
550 ,hr_transaction_api.get_varchar2_value
551 (p_transaction_step_id, 'P_DEFAULT_VEHICLE') default_vehicle
552 ,hr_transaction_api.get_varchar2_value
553 (p_transaction_step_id, 'P_FUEL_CARD') fuel_card
554 ,hr_transaction_api.get_varchar2_value
555 (p_transaction_step_id, 'P_FUEL_CARD_NUMBER') fuel_card_number
556 ,hr_transaction_api.get_varchar2_value
557 (p_transaction_step_id, 'P_FUEL_BENEFIT') fuel_benefit
558 ,hr_transaction_api.get_number_value
559 (p_transaction_step_id, 'P_VEHICLE_ALLOCATION_ID') vehicle_allocation_id
560 ,hr_transaction_api.get_varchar2_value
561 (p_transaction_step_id, 'P_VAL_ATTRIBUTE_CATEGORY')
562 val_attribute_category
563 ,hr_transaction_api.get_varchar2_value
564 (p_transaction_step_id, 'P_VAL_ATTRIBUTE1') val_attribute1
565 ,hr_transaction_api.get_varchar2_value
566 (p_transaction_step_id, 'P_VAL_ATTRIBUTE2') val_attribute2
567 ,hr_transaction_api.get_varchar2_value
568 (p_transaction_step_id, 'P_VAL_ATTRIBUTE3') val_attribute3
569 ,hr_transaction_api.get_varchar2_value
570 (p_transaction_step_id, 'P_VAL_ATTRIBUTE4') val_attribute4
571 ,hr_transaction_api.get_varchar2_value
572 (p_transaction_step_id, 'P_VAL_ATTRIBUTE5') val_attribute5
573 ,hr_transaction_api.get_varchar2_value
574 (p_transaction_step_id, 'P_VAL_ATTRIBUTE6') val_attribute6
575 ,hr_transaction_api.get_varchar2_value
576 (p_transaction_step_id, 'P_VAL_ATTRIBUTE7') val_attribute7
577 ,hr_transaction_api.get_varchar2_value
578 (p_transaction_step_id, 'P_VAL_ATTRIBUTE8') val_attribute8
579 ,hr_transaction_api.get_varchar2_value
580 (p_transaction_step_id, 'P_VAL_ATTRIBUTE9') val_attribute9
581 ,hr_transaction_api.get_varchar2_value
582 (p_transaction_step_id, 'P_VAL_ATTRIBUTE10') val_attribute10
583 ,hr_transaction_api.get_varchar2_value
584 (p_transaction_step_id, 'P_VAL_ATTRIBUTE11') val_attribute11
585 ,hr_transaction_api.get_varchar2_value
586 (p_transaction_step_id, 'P_VAL_ATTRIBUTE12') val_attribute12
587 ,hr_transaction_api.get_varchar2_value
588 (p_transaction_step_id, 'P_VAL_ATTRIBUTE13') val_attribute13
589 ,hr_transaction_api.get_varchar2_value
590 (p_transaction_step_id, 'P_VAL_ATTRIBUTE14') val_attribute14
591 ,hr_transaction_api.get_varchar2_value
592 (p_transaction_step_id, 'P_VAL_ATTRIBUTE15') val_attribute15
593 ,hr_transaction_api.get_varchar2_value
594 (p_transaction_step_id, 'P_VAL_ATTRIBUTE16') val_attribute16
595 ,hr_transaction_api.get_varchar2_value
596 (p_transaction_step_id, 'P_VAL_ATTRIBUTE17') val_attribute17
597 ,hr_transaction_api.get_varchar2_value
598 (p_transaction_step_id, 'P_VAL_ATTRIBUTE18') val_attribute18
599 ,hr_transaction_api.get_varchar2_value
600 (p_transaction_step_id, 'P_VAL_ATTRIBUTE19') val_attribute19
601 ,hr_transaction_api.get_varchar2_value
602 (p_transaction_step_id, 'P_VAL_ATTRIBUTE20') val_attribute20
603 ,hr_transaction_api.get_varchar2_value
604 (p_transaction_step_id, 'P_VAL_INFORMATION_CATEGORY')
605 val_information_category
606 ,hr_transaction_api.get_varchar2_value
607 (p_transaction_step_id, 'P_VAL_INFORMATION1') val_information1
608 ,hr_transaction_api.get_varchar2_value
609 (p_transaction_step_id, 'P_VAL_INFORMATION2') val_information2
610 ,hr_transaction_api.get_varchar2_value
611 (p_transaction_step_id, 'P_VAL_INFORMATION3') val_information3
612 ,hr_transaction_api.get_varchar2_value
613 (p_transaction_step_id, 'P_VAL_INFORMATION4') val_information4
614 ,hr_transaction_api.get_varchar2_value
615 (p_transaction_step_id, 'P_VAL_INFORMATION5') val_information5
616 ,hr_transaction_api.get_varchar2_value
617 (p_transaction_step_id, 'P_VAL_INFORMATION6') val_information6
618 ,hr_transaction_api.get_varchar2_value
619 (p_transaction_step_id, 'P_VAL_INFORMATION7') val_information7
620 ,hr_transaction_api.get_varchar2_value
621 (p_transaction_step_id, 'P_VAL_INFORMATION8') val_information8
622 ,hr_transaction_api.get_varchar2_value
623 (p_transaction_step_id, 'P_VAL_INFORMATION9') val_information9
624 ,hr_transaction_api.get_varchar2_value
625 (p_transaction_step_id, 'P_VAL_INFORMATION10') val_information10
626 ,hr_transaction_api.get_varchar2_value
627 (p_transaction_step_id, 'P_VAL_INFORMATION11') val_information11
628 ,hr_transaction_api.get_varchar2_value
629 (p_transaction_step_id, 'P_VAL_INFORMATION12') val_information12
630 ,hr_transaction_api.get_varchar2_value
631 (p_transaction_step_id, 'P_VAL_INFORMATION13') val_information13
632 ,hr_transaction_api.get_varchar2_value
633 (p_transaction_step_id, 'P_VAL_INFORMATION14') val_information14
634 ,hr_transaction_api.get_varchar2_value
635 (p_transaction_step_id, 'P_VAL_INFORMATION15') val_information15
636 ,hr_transaction_api.get_varchar2_value
637 (p_transaction_step_id, 'P_VAL_INFORMATION16') val_information16
638 ,hr_transaction_api.get_varchar2_value
639 (p_transaction_step_id, 'P_VAL_INFORMATION17') val_information17
640 ,hr_transaction_api.get_varchar2_value
641 (p_transaction_step_id, 'P_VAL_INFORMATION18') val_information18
642 ,hr_transaction_api.get_varchar2_value
643 (p_transaction_step_id, 'P_VAL_INFORMATION19') val_information19
644 ,hr_transaction_api.get_varchar2_value
645 (p_transaction_step_id, 'P_VAL_INFORMATION20') val_information20
646 FROM DUAL;
647
648 RETURN csr;
649 END get_vehicle_details;
650
651
652
653
654
655 --
656 --
657
658 --Updates the vehicle allocation when the transaction mode update
659 PROCEDURE update_vehicle_allocations
660 (p_validate in boolean
661 ,p_effective_date in date
662 ,p_business_group_id in number
663 ,p_registration_number in varchar2
664 ,p_vehicle_repository_id in number
665 ,p_across_assignments in varchar2
666 ,p_usage_type in varchar2
667 ,p_default_vehicle in varchar2
668 ,p_fuel_card in varchar2
669 ,p_fuel_card_number in varchar2
670 ,p_insurance_number in varchar2
671 ,p_insurance_expiry_date in date
672 ,p_val_attribute_category in varchar2
673 ,p_val_attribute1 in varchar2
674 ,p_val_attribute2 in varchar2
675 ,p_val_attribute3 in varchar2
676 ,p_val_attribute4 in varchar2
677 ,p_val_attribute5 in varchar2
678 ,p_val_attribute6 in varchar2
679 ,p_val_attribute7 in varchar2
680 ,p_val_attribute8 in varchar2
681 ,p_val_attribute9 in varchar2
682 ,p_val_attribute10 in varchar2
683 ,p_val_attribute11 in varchar2
684 ,p_val_attribute12 in varchar2
685 ,p_val_attribute13 in varchar2
686 ,p_val_attribute14 in varchar2
687 ,p_val_attribute15 in varchar2
688 ,p_val_attribute16 in varchar2
689 ,p_val_attribute17 in varchar2
690 ,p_val_attribute18 in varchar2
691 ,p_val_attribute19 in varchar2
692 ,p_val_attribute20 in varchar2
693 ,p_val_information_category in varchar2
694 ,p_val_information1 in varchar2
695 ,p_val_information2 in varchar2
696 ,p_val_information3 in varchar2
697 ,p_val_information4 in varchar2
698 ,p_val_information5 in varchar2
699 ,p_val_information6 in varchar2
700 ,p_val_information7 in varchar2
701 ,p_val_information8 in varchar2
702 ,p_val_information9 in varchar2
703 ,p_val_information10 in varchar2
704 ,p_val_information11 in varchar2
705 ,p_val_information12 in varchar2
706 ,p_val_information13 in varchar2
707 ,p_val_information14 in varchar2
708 ,p_val_information15 in varchar2
709 ,p_val_information16 in varchar2
710 ,p_val_information17 in varchar2
711 ,p_val_information18 in varchar2
712 ,p_val_information19 in varchar2
713 ,p_val_information20 in varchar2
714 ,p_fuel_benefit in varchar2
715 ,p_user_info in t_user_info
716 ,p_error_message in varchar2
717 )
718 IS
719
720
721 lc_object_version_number NUMBER;
722 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
723 TYPE r_assignment_rec IS RECORD
724 (assignment_id per_all_assignments_f.assignment_id%TYPE,
725 allocation_id pqp_vehicle_allocations_f.vehicle_allocation_id%TYPE
726 ,user_type VARCHAR2(10));
727
728 TYPE t_assignment_tab IS TABLE OF r_assignment_rec
729 INDEX BY BINARY_INTEGER;
730
731 l_assignment_tab t_assignment_tab;
732 l_new_assignment_tab t_assignment_tab;
733 l_del_assignment_tab t_assignment_tab;
734 l_user_info t_assignment_tab;
735
736 CURSOR c_get_alloc_info (cp_registration_number VARCHAR2
737 ,cp_business_group_id NUMBER
738 ,cp_effective_date DATE
739 )
740 IS
741 SELECT pva.assignment_id ,
742 pva.vehicle_allocation_id allocation_id
743 FROM pqp_vehicle_allocations_f pva
744 ,pqp_vehicle_repository_f pvr
745 WHERE pvr.registration_number = cp_registration_number
746 AND pvr.business_group_id = cp_business_group_id
747 AND pvr.business_group_id=pva.business_group_id
748 AND pvr.vehicle_repository_id=pva.vehicle_repository_id
749 AND cp_effective_date BETWEEN pvr.effective_start_date
750 AND pvr.effective_end_date
751 AND cp_effective_date BETWEEN pva.effective_start_date
752 AND pva.effective_end_date;
753
754
755 CURSOR c_get_object_version_number (
756 cp_vehicle_allocation_id NUMBER
757 ,cp_assignment_id NUMBER
758 ,cp_business_group_id NUMBER
759 ,cp_effective_date DATE
760 )
761 IS
762 SELECT pva.object_version_number
763 FROM pqp_vehicle_allocations_f pva
764 WHERE pva.vehicle_allocation_id =cp_vehicle_allocation_id
765 AND pva.assignment_id = cp_assignment_id
766 AND pva.business_group_id = cp_business_group_id
767 AND cp_effective_date BETWEEN pva.effective_start_date
768 AND pva.effective_end_date;
769
770
771 CURSOR c_get_repository (cp_registration_number VARCHAR2
772 ,cp_business_group_id NUMBER
773 ,cp_effective_date DATE
774 )
775 IS
776 SELECT pvr.vehicle_repository_id
777 FROM pqp_vehicle_repository_f pvr
778 WHERE pvr.registration_number = cp_registration_number
779 AND pvr.business_group_id = cp_business_group_id
780 AND cp_effective_date BETWEEN pvr.effective_start_date
781 AND pvr.effective_end_date;
782
783 l_object_version_number NUMBER;
784 l_datetrack_mode VARCHAr2(30);
785 l_get_alloc_info c_get_alloc_info%ROWTYPE;
786 l_count NUMBER :=0;
787 l_assignment_present VARCHAR2(1) :=NULL;
788 l_assignment_add VARCHAr2(1) :=NULL;
789 l_new_assignment_id per_all_assignments_f.assignment_id%TYPE;
790 l_correction NUMBER;
791 l_update NUMBER;
792 l_update_override NUMBER;
793 l_update_change_insert NUMBER;
794 l_effective_start_date DATE;
795 l_effective_end_date DATE;
796 l_vehicle_repository_id pqp_vehicle_repository_f.vehicle_repository_id%TYPE;
797 l_vehicle_allocation_id pqp_vehicle_allocations_f.vehicle_allocation_id%TYPE;
798 l_cnt NUMBER :=0;
799
800
801 BEGIN
802
803 --Get all the users for that registration number
804 --from the allocation table.
805 --The reason for the logic is to compare
806 --incoming useri id where it could be
807 --same or less or even more than what
808 --is present in the allocation table.
809 l_count :=0;
810
811 OPEN c_get_alloc_info (p_registration_number
812 ,p_business_group_id
813 ,p_effective_date
814 );
815 LOOP
816 FETCH c_get_alloc_info INTO l_get_alloc_info;
817 EXIT WHEN c_get_alloc_info%NOTFOUND;
818
819 l_assignment_tab(l_count+1).assignment_id :=l_get_alloc_info.assignment_id;
820 l_assignment_tab(l_count+1).allocation_id :=l_get_alloc_info.allocation_id;
821
822 l_count:=l_count+1;
823 END LOOP;
824 CLOSE c_get_alloc_info;
825
826 --compare the assignments with the incoming table parameter for
827 --assignments and check for reduced assignments.
828 --This check is for the users who are no longer using the vehicle
829 FOR i in 1..l_assignment_tab.count
830 LOOP
831 FOR j in 1..p_user_info.count
832 LOOP
833
834 --check if the already allocated assignments is present in the param
835 --table.
836 IF l_assignment_tab(i).assignment_id = p_user_info(j).assignment_id THEN
837 l_assignment_present :='Y';
838
839 END IF;
840 END LOOP;
841 --Add the users into the delete table for further processing
842 IF l_assignment_present is NULL OR l_assignment_present <>'Y' THEN
843
844 l_del_assignment_tab(l_cnt+1).assignment_id:=
845 l_assignment_tab(i).assignment_id;
846 l_del_assignment_tab(l_cnt+1).allocation_id:=
847 l_assignment_tab(i).allocation_id;
848
849 l_cnt :=l_cnt+1;
850 END IF;
851 l_assignment_present:='N';
852 END LOOP;
853
854
855
856
857 l_count:=0;
858 --Check if there are any new asignments in the param table.
859 --There could be additional user and this information will be
860 --stored in the other plsql table for further processing
861 FOR i in 1..p_user_info.count
862 LOOP
863 FOR j in 1..l_assignment_tab.count
864 LOOP
865 IF p_user_info(i).assignment_id = l_assignment_tab(j).assignment_id THEN
866 l_assignment_add := 'N' ;
867 l_user_info(l_count+1).assignment_id
868 := p_user_info(i).assignment_id;
869 l_user_info(l_count+1).allocation_id
870 := l_assignment_tab(j).allocation_id;
871 l_user_info(l_count+1).user_type :=p_user_info(i).user_type;
872 l_count:=l_count+1;
873
874 ENd IF;
875 END LOOP;
876 IF l_assignment_add IS NULL OR l_assignment_add <> 'N' THEN
877 l_new_assignment_tab(l_new_assignment_tab.count+1).assignment_id
878 := p_user_info(i).assignment_id;
879 END IF;
880 l_assignment_add := 'Y' ;
881 END LOOP;
882
883 --Create allocation for new assignments
884
885 IF (l_new_assignment_tab.count) > 0 THEN
886 /* OPEN c_get_repository (p_registration_number
887 ,p_business_group_id
888 ,p_effective_date
889 );
890 FETCH c_get_repository INTO l_vehicle_repository_id;
891
892 CLOSE c_get_repository;*/
893
894 FOR i in 1..(l_new_assignment_tab.count)
895 LOOP
896 pqp_vehicle_allocations_api.create_vehicle_allocation
897 (p_validate => p_validate
898 ,p_effective_date => p_effective_date
899 ,p_assignment_id => l_new_assignment_tab(i).assignment_id
900 ,p_business_group_id => p_business_group_id
901 ,p_vehicle_repository_id => p_vehicle_repository_id
902 ,p_across_assignments => p_across_assignments
903 ,p_usage_type => p_usage_type
904 ,p_default_vehicle => p_default_vehicle
905 ,p_fuel_card => p_fuel_card
906 ,p_fuel_card_number => p_fuel_card_number
907 ,p_insurance_number => p_insurance_number
908 ,p_insurance_expiry_date => p_insurance_expiry_date
909 ,p_val_attribute_category => p_val_attribute_category
910 ,p_val_attribute1 => p_val_attribute1
911 ,p_val_attribute2 => p_val_attribute2
912 ,p_val_attribute3 => p_val_attribute3
913 ,p_val_attribute4 => p_val_attribute4
914 ,p_val_attribute5 => p_val_attribute5
915 ,p_val_attribute6 => p_val_attribute6
916 ,p_val_attribute7 => p_val_attribute7
917 ,p_val_attribute8 => p_val_attribute8
918 ,p_val_attribute9 => p_val_attribute9
919 ,p_val_attribute10 => p_val_attribute10
920 ,p_val_attribute11 => p_val_attribute11
921 ,p_val_attribute12 => p_val_attribute12
922 ,p_val_attribute13 => p_val_attribute13
923 ,p_val_attribute14 => p_val_attribute14
924 ,p_val_attribute15 => p_val_attribute15
925 ,p_val_attribute16 => p_val_attribute16
926 ,p_val_attribute17 => p_val_attribute17
927 ,p_val_attribute18 => p_val_attribute18
928 ,p_val_attribute19 => p_val_attribute19
929 ,p_val_attribute20 => p_val_attribute20
930 ,p_val_information_category => p_val_information_category
931 ,p_val_information1 => p_val_information1
932 ,p_val_information2 => p_val_information2
933 ,p_val_information3 => p_val_information3
934 ,p_val_information4 => p_val_information4
935 ,p_val_information5 => p_val_information5
936 ,p_val_information6 => p_val_information6
937 ,p_val_information7 => p_val_information7
938 ,p_val_information8 => p_val_information8
939 ,p_val_information9 => p_val_information9
940 ,p_val_information10 => p_val_information10
941 ,p_val_information11 => p_val_information11
942 ,p_val_information12 => p_val_information12
943 ,p_val_information13 => p_val_information13
944 ,p_val_information14 => p_val_information14
945 ,p_val_information15 => p_val_information15
946 ,p_val_information16 => p_val_information16
947 ,p_val_information17 => p_val_information17
948 ,p_val_information18 => p_val_information18
949 ,p_val_information19 => p_val_information19
950 ,p_val_information20 => p_val_information20
951 ,p_fuel_benefit => p_fuel_benefit
952 ,p_vehicle_allocation_id => l_vehicle_allocation_id
953 ,p_object_version_number => l_object_version_number
954 ,p_effective_start_date => l_effective_start_date
955 ,p_effective_end_date => l_effective_end_date
956 );
957
958
959 END LOOP;
960 END IF;
961
962 --End date the allocation for those assignments
963 --that are no longer using the vehicle.
964 IF (l_del_assignment_tab.count) > 0 THEN
965 FOR i in 1..(l_del_assignment_tab.count)
966 LOOP
967 OPEN c_get_object_version_number
968 (
969 l_del_assignment_tab(i).allocation_id
970 ,l_del_assignment_tab(i).assignment_id
971 ,p_business_group_id
972 ,p_effective_date
973 );
974 FETCH c_get_object_version_number INTO lc_object_version_number;
975 CLOSE c_get_object_version_number;
976
977 pqp_vehicle_allocations_api.delete_vehicle_allocation
978 (p_validate => p_validate
979 ,p_effective_date => p_effective_date
980 ,p_datetrack_mode => 'DELETE'
981 ,p_vehicle_allocation_id => l_del_assignment_tab(i).allocation_id
982 ,p_object_version_number => lc_object_version_number
983 ,p_effective_start_date => l_effective_start_date
984 ,p_effective_end_date => l_effective_end_date
985 );
986 END LOOP;
987 END IF;
988
989
990
991 --Update rest of the assignments for any change in
992 --allocations.
993
994 FOR i in 1..l_user_info.count
995 LOOP
996 --get object_version_number
997 IF l_user_info(i).user_type <>'OA' THEN
998 OPEN c_get_object_version_number
999 (
1000 l_user_info(i).allocation_id
1001 ,l_user_info(i).assignment_id
1002 ,p_business_group_id
1003 ,p_effective_date
1004 );
1005 FETCH c_get_object_version_number INTO lc_object_version_number;
1006 CLOSE c_get_object_version_number;
1007 ---get_date track mode
1008 pqp_get_date_mode.find_dt_upd_modes
1009 (p_effective_date =>p_effective_date
1010 ,p_base_table_name =>'PQP_VEHICLE_ALLOCATIONS_F'
1011 ,p_base_key_column =>'VEHICLE_ALLOCATION_ID'
1012 ,p_base_key_value =>l_user_info(i).allocation_id
1013 ,p_correction =>l_correction
1014 ,p_update =>l_update
1015 ,p_update_override =>l_update_override
1016 ,p_update_change_insert =>l_update_change_insert
1017 );
1018
1019 IF l_correction = 1 THEN
1020 l_datetrack_mode :='CORRECTION' ;
1021 ELSIF l_update = 1 THEN
1022 l_datetrack_mode :='UPDATE' ;
1023 END IF;
1024
1025
1026 --Call update api for allocation.
1027 pqp_vehicle_allocations_api.update_vehicle_allocation
1028 (p_validate => p_validate
1029 ,p_effective_date => p_effective_date
1030 ,p_datetrack_mode => l_datetrack_mode
1031 ,p_vehicle_allocation_id => l_user_info(i).allocation_id
1032 ,p_object_version_number => lc_object_version_number
1033 ,p_assignment_id => l_user_info(i).assignment_id
1034 ,p_business_group_id => p_business_group_id
1035 ,p_across_assignments => p_across_assignments
1036 ,p_usage_type => p_usage_type
1037 ,p_default_vehicle => p_default_vehicle
1038 ,p_fuel_card => p_fuel_card
1039 ,p_fuel_card_number => p_fuel_card_number
1040 ,p_insurance_number => p_insurance_number
1041 ,p_insurance_expiry_date => p_insurance_expiry_date
1042 ,p_val_attribute_category => p_val_attribute_category
1043 ,p_val_attribute1 => p_val_attribute1
1044 ,p_val_attribute2 => p_val_attribute2
1045 ,p_val_attribute3 => p_val_attribute3
1046 ,p_val_attribute4 => p_val_attribute4
1047 ,p_val_attribute5 => p_val_attribute5
1048 ,p_val_attribute6 => p_val_attribute6
1049 ,p_val_attribute7 => p_val_attribute7
1050 ,p_val_attribute8 => p_val_attribute8
1051 ,p_val_attribute9 => p_val_attribute9
1052 ,p_val_attribute10 => p_val_attribute10
1053 ,p_val_attribute11 => p_val_attribute11
1054 ,p_val_attribute12 => p_val_attribute12
1055 ,p_val_attribute13 => p_val_attribute13
1056 ,p_val_attribute14 => p_val_attribute14
1057 ,p_val_attribute15 => p_val_attribute15
1058 ,p_val_attribute16 => p_val_attribute16
1059 ,p_val_attribute17 => p_val_attribute17
1060 ,p_val_attribute18 => p_val_attribute18
1061 ,p_val_attribute19 => p_val_attribute19
1062 ,p_val_attribute20 => p_val_attribute20
1063 ,p_val_information1 => p_val_information1
1064 ,p_val_information2 => p_val_information2
1065 ,p_val_information3 => p_val_information3
1066 ,p_val_information4 => p_val_information4
1067 ,p_val_information5 => p_val_information5
1068 ,p_val_information6 => p_val_information6
1069 ,p_val_information7 => p_val_information7
1070 ,p_val_information8 => p_val_information8
1071 ,p_val_information9 => p_val_information9
1072 ,p_val_information10 => p_val_information10
1073 ,p_val_information11 => p_val_information11
1074 ,p_val_information12 => p_val_information12
1075 ,p_val_information13 => p_val_information13
1076 ,p_val_information14 => p_val_information14
1077 ,p_val_information15 => p_val_information15
1078 ,p_val_information16 => p_val_information16
1079 ,p_val_information17 => p_val_information17
1080 ,p_val_information18 => p_val_information18
1081 ,p_val_information19 => p_val_information19
1082 ,p_val_information20 => p_val_information20
1083 ,p_fuel_benefit => p_fuel_benefit
1084 ,p_effective_start_date => l_effective_start_date
1085 ,p_effective_end_date => l_effective_end_date
1086 );
1087 END IF;
1088 END LOOP;
1089 END update_vehicle_allocations;
1090
1091
1092
1093
1094
1095 --This procedure is called to create vehicle information in both
1096 --allocation and repository.
1097 PROCEDURE create_vehicle_details
1098 (
1099 p_validate in boolean default false
1100 ,p_effective_date in date
1101 ,p_registration_number in varchar2
1102 ,p_vehicle_type in varchar2
1103 ,p_vehicle_id_number in varchar2
1104 ,p_business_group_id in number
1105 ,p_make in varchar2
1106 ,p_engine_capacity_in_cc in number
1107 ,p_fuel_type in varchar2
1108 ,p_currency_code in varchar2
1109 ,p_model in varchar2
1110 ,p_initial_registration in date
1111 ,p_last_registration_renew_date in date
1112 ,p_fiscal_ratings in number
1113 ,p_vehicle_ownership in varchar2
1114 ,p_shared_vehicle in varchar2
1115 ,p_color in varchar2
1116 ,p_seating_capacity in number
1117 ,p_weight in number
1118 ,p_weight_uom in varchar2
1119 ,p_model_year in number
1120 ,p_insurance_number in varchar2
1121 ,p_insurance_expiry_date in date
1122 ,p_taxation_method in varchar2
1123 ,p_comments in varchar2
1124 ,p_vre_attribute_category in varchar2
1125 ,p_vre_attribute1 in varchar2
1126 ,p_vre_attribute2 in varchar2
1127 ,p_vre_attribute3 in varchar2
1128 ,p_vre_attribute4 in varchar2
1129 ,p_vre_attribute5 in varchar2
1130 ,p_vre_attribute6 in varchar2
1131 ,p_vre_attribute7 in varchar2
1132 ,p_vre_attribute8 in varchar2
1133 ,p_vre_attribute9 in varchar2
1134 ,p_vre_attribute10 in varchar2
1135 ,p_vre_attribute11 in varchar2
1136 ,p_vre_attribute12 in varchar2
1137 ,p_vre_attribute13 in varchar2
1138 ,p_vre_attribute14 in varchar2
1139 ,p_vre_attribute15 in varchar2
1140 ,p_vre_attribute16 in varchar2
1141 ,p_vre_attribute17 in varchar2
1142 ,p_vre_attribute18 in varchar2
1143 ,p_vre_attribute19 in varchar2
1144 ,p_vre_attribute20 in varchar2
1145 ,p_vre_information_category in varchar2
1146 ,p_vre_information1 in varchar2
1147 ,p_vre_information2 in varchar2
1148 ,p_vre_information3 in varchar2
1149 ,p_vre_information4 in varchar2
1150 ,p_vre_information5 in varchar2
1151 ,p_vre_information6 in varchar2
1152 ,p_vre_information7 in varchar2
1153 ,p_vre_information8 in varchar2
1154 ,p_vre_information9 in varchar2
1155 ,p_vre_information10 in varchar2
1156 ,p_vre_information11 in varchar2
1157 ,p_vre_information12 in varchar2
1158 ,p_vre_information13 in varchar2
1159 ,p_vre_information14 in varchar2
1160 ,p_vre_information15 in varchar2
1161 ,p_vre_information16 in varchar2
1162 ,p_vre_information17 in varchar2
1163 ,p_vre_information18 in varchar2
1164 ,p_vre_information19 in varchar2
1165 ,p_vre_information20 in varchar2
1166 ,p_across_assignments in varchar2
1167 ,p_usage_type in varchar2
1168 ,p_default_vehicle in varchar2
1169 ,p_fuel_card in varchar2
1170 ,p_fuel_card_number in varchar2
1171 ,p_val_attribute_category in varchar2
1172 ,p_val_attribute1 in varchar2
1173 ,p_val_attribute2 in varchar2
1174 ,p_val_attribute3 in varchar2
1175 ,p_val_attribute4 in varchar2
1176 ,p_val_attribute5 in varchar2
1177 ,p_val_attribute6 in varchar2
1178 ,p_val_attribute7 in varchar2
1179 ,p_val_attribute8 in varchar2
1180 ,p_val_attribute9 in varchar2
1181 ,p_val_attribute10 in varchar2
1182 ,p_val_attribute11 in varchar2
1183 ,p_val_attribute12 in varchar2
1184 ,p_val_attribute13 in varchar2
1185 ,p_val_attribute14 in varchar2
1186 ,p_val_attribute15 in varchar2
1187 ,p_val_attribute16 in varchar2
1188 ,p_val_attribute17 in varchar2
1189 ,p_val_attribute18 in varchar2
1190 ,p_val_attribute19 in varchar2
1191 ,p_val_attribute20 in varchar2
1192 ,p_val_information_category in varchar2
1193 ,p_val_information1 in varchar2
1194 ,p_val_information2 in varchar2
1195 ,p_val_information3 in varchar2
1196 ,p_val_information4 in varchar2
1197 ,p_val_information5 in varchar2
1198 ,p_val_information6 in varchar2
1199 ,p_val_information7 in varchar2
1200 ,p_val_information8 in varchar2
1201 ,p_val_information9 in varchar2
1202 ,p_val_information10 in varchar2
1203 ,p_val_information11 in varchar2
1204 ,p_val_information12 in varchar2
1205 ,p_val_information13 in varchar2
1206 ,p_val_information14 in varchar2
1207 ,p_val_information15 in varchar2
1208 ,p_val_information16 in varchar2
1209 ,p_val_information17 in varchar2
1210 ,p_val_information18 in varchar2
1211 ,p_val_information19 in varchar2
1212 ,p_val_information20 in varchar2
1213 ,p_fuel_benefit in varchar2
1214 ,p_user_info in t_user_info
1215 ,p_vehicle_repository_id in number
1216 ,p_vehicle_allocation_id in number
1217 ,p_object_version_number in number
1218 ,p_error_message OUT nocopy varchar2
1219 ,p_error_status OUT nocopy varchar2
1220 )
1221 IS
1222
1223
1224 CURSOR c_get_user_assignments (cp_person_id NUMBER
1225 ,cp_business_group_id NUMBER
1226 ,cp_effective_date DATE
1227 )
1228 IS
1229 SELECT paa.assignment_id
1230 FROM per_all_assignments_f paa
1231 WHERE paa.person_id = cp_person_id
1232 AND paa.primary_flag ='Y'
1233 AND paa.business_group_id = cp_business_group_id
1234 AND cp_effective_date BETWEEN paa.effective_start_date
1235 AND paa.effective_end_date;
1236
1237 CURSOR c_get_main_user_assignments
1238 (cp_person_id NUMBER
1239 ,cp_assignment_id NUMBER
1240 ,cp_business_group_id NUMBER
1241 ,cp_effective_date DATE
1242 )
1243 IS
1244 SELECT paa.assignment_id
1245 FROM per_all_assignments_f paa
1246 WHERE paa.person_id = cp_person_id
1247 AND paa.assignment_id <> cp_assignment_id
1248 AND paa.business_group_id = cp_business_group_id
1249 AND cp_effective_date BETWEEN paa.effective_start_date
1250 AND paa.effective_end_date;
1251
1252 CURSOR c_fiscal_uom IS
1253 SELECT hrl.lookup_code
1254 FROM hr_lookups hrl
1255 WHERE lookup_type = 'PQP_FISCAL_RATINGS_UOM'
1256 AND enabled_flag = 'Y';
1257
1258 CURSOR c_get_repository_id (cp_registration_number VARCHAR2
1259 ,cp_business_group_id NUMBER
1260 ,cp_effective_date DATE
1261 )
1262 IS
1263 SELECT pvr.vehicle_repository_id
1264 ,pvr.object_version_number
1265 ,pvr.shared_vehicle
1266 FROM pqp_vehicle_repository_f pvr
1267 WHERE pvr.registration_number = cp_registration_number
1268 AND pvr.business_group_id = cp_business_group_id
1269 AND cp_effective_date BETWEEN pvr.effective_start_date
1270 AND pvr.effective_end_date;
1271
1272
1273 l_vehicle_repository_id pqp_vehicle_repository_f.vehicle_repository_id%TYPE;
1274 l_object_version_number1 NUMBER;
1275 l_object_version_number NUMBER;
1276 l_user_info t_user_info;
1277 l_vehicle_allocation_id pqp_vehicle_allocations_f.vehicle_allocation_id%TYPE;
1278 l_get_repository_id c_get_repository_id%ROWTYPE;
1279 l_lookup_code hr_lookups.lookup_code%TYPE;
1280 l_leg_code pqp_configuration_values.legislation_code%TYPE;
1281 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
1282 l_correction NUMBER;
1283 l_update NUMBER;
1284 l_update_override NUMBER;
1285 l_update_change_insert NUMBER;
1286 l_datetrack_mode VARCHAR2(30);
1287 l_cnt NUMBER :=0;
1288 l_count NUMBER :=0; -- Added as a part of fix for bug#12937050.
1289 l_effective_start_date DATE;
1290 l_effective_end_date DATE;
1291 l_cnt1 NUMBER;
1292 l_chk NUMBER:=0;
1293 l_dt_adj number:=0;
1294 e_exist_other_asg EXCEPTION;
1295 BEGIN
1296
1297 l_user_info(1).person_id := p_user_info(1).person_id;
1298 l_user_info(1).assignment_id := p_user_info(1).assignment_id;
1299 l_user_info(1).user_type := p_user_info(1).user_type;
1300
1301 --Check if the main user has chosen to share across his own
1302 --assignments, get all assignemnts for that person
1303 -- and store it in a plsql table.
1304 IF p_across_assignments = 'Y' THEN
1305 l_cnt1:=l_user_info.count;
1306 OPEN c_get_main_user_assignments (
1307 p_user_info(1).person_id
1308 ,p_user_info(1).assignment_id
1309 ,p_business_group_id
1310 ,p_effective_date
1311 );
1312 LOOP
1313 FETCH c_get_main_user_assignments INTO l_assignment_id;
1314 EXIT WHEN c_get_main_user_assignments%NOTFOUND;
1315 l_user_info(l_cnt1+1).person_id := p_user_info(1).person_id;
1316 l_user_info(l_cnt1+1).assignment_id
1317 := l_assignment_id;
1318 --'SA' Stands for secondary assignment
1319 l_user_info(l_cnt1+1).user_type := 'SA';
1320 l_cnt1:=l_cnt1+1;
1321 END LOOP;
1322 CLOSE c_get_main_user_assignments;
1323 END IF;
1324
1325 BEGIN
1326
1327 IF p_shared_vehicle='Y' AND p_user_info.count>1 THEN
1328 l_cnt:=l_user_info.count;
1329
1330 FOR k in 2..(p_user_info.count)
1331 LOOP
1332 l_cnt:=l_cnt+1;
1333 l_user_info(l_cnt).person_id := p_user_info(k).person_id;
1334 l_user_info(l_cnt).assignment_id
1335 := p_user_info(k).assignment_id;
1336 --'OA' Stands for other employee's assignments
1337 l_user_info(l_cnt).user_type := 'OA';
1338
1339 END LOOP;
1340 END IF; --shared vehicle;
1341 EXCEPTION
1342 WHEN hr_utility.hr_error THEN
1343 hr_utility.raise_error;
1344 WHEN OTHERS THEN
1345 RAISE; -- Raise error here relevant to the new tech stack.
1346 END;
1347 ---check if other users flag is set to yes, if set to yes
1348 --then get the assignments for all their person ids
1349
1350 --Call create apis for both repository and allocations
1351 --and based on number of users and whether the allocation
1352 --is for all the assignments of that person, the api is
1353 --called in the loop.
1354
1355 --Call Create vehicle repository api
1356
1357 IF p_vehicle_repository_id is NULL THEN
1358 Begin
1359
1360 --Getting the legislationId for business groupId
1361 l_leg_code :=
1362 pqp_vre_bus.get_legislation_code(p_business_group_id);
1363 --setting the lg context
1364 hr_api.set_legislation_context(l_leg_code);
1365
1366 OPEN c_fiscal_uom;
1367 FETCH c_fiscal_uom INTO l_lookup_code;
1368 CLOSE c_fiscal_uom;
1369 EXCEPTION
1370 WHEN no_data_found THEN
1371 l_lookup_code := NULL;
1372 End ;
1373 pqp_vehicle_repository_api.create_vehicle
1374 (p_validate => p_validate
1375 ,p_effective_date => p_effective_date
1376 ,p_registration_number => p_registration_number
1377 ,p_vehicle_type => p_vehicle_type
1378 ,p_vehicle_id_number => p_vehicle_id_number
1379 ,p_business_group_id => p_business_group_id
1380 ,p_make => p_make
1381 ,p_engine_capacity_in_cc => p_engine_capacity_in_cc
1382 ,p_fuel_type => p_fuel_type
1383 ,p_currency_code => p_currency_code
1384 ,p_vehicle_status => 'A'
1385 ,p_model => p_model
1386 ,p_initial_registration => p_initial_registration
1387 ,p_last_registration_renew_date => p_last_registration_renew_date
1388 ,p_fiscal_ratings => p_fiscal_ratings
1389 ,p_fiscal_ratings_uom => l_lookup_code --p_fiscal_ratings_uom
1390 ,p_vehicle_ownership => p_vehicle_ownership
1391 ,p_shared_vehicle => p_shared_vehicle
1392 ,p_taxation_method => p_taxation_method
1393 ,p_color => p_color
1394 ,p_seating_capacity => p_seating_capacity
1395 ,p_weight => p_weight
1396 ,p_weight_uom => p_weight_uom
1397 ,p_model_year => p_model_year
1398 ,p_insurance_number => p_insurance_number
1399 ,p_insurance_expiry_date => p_insurance_expiry_date
1400 ,p_comments => p_comments
1401 ,p_vre_attribute_category => p_vre_attribute_category
1402 ,p_vre_attribute1 => p_vre_attribute1
1403 ,p_vre_attribute2 => p_vre_attribute2
1404 ,p_vre_attribute3 => p_vre_attribute3
1405 ,p_vre_attribute4 => p_vre_attribute4
1406 ,p_vre_attribute5 => p_vre_attribute5
1407 ,p_vre_attribute6 => p_vre_attribute6
1408 ,p_vre_attribute7 => p_vre_attribute7
1409 ,p_vre_attribute8 => p_vre_attribute8
1410 ,p_vre_attribute9 => p_vre_attribute9
1411 ,p_vre_attribute10 => p_vre_attribute10
1412 ,p_vre_attribute11 => p_vre_attribute11
1413 ,p_vre_attribute12 => p_vre_attribute12
1414 ,p_vre_attribute13 => p_vre_attribute13
1415 ,p_vre_attribute14 => p_vre_attribute14
1416 ,p_vre_attribute15 => p_vre_attribute15
1417 ,p_vre_attribute16 => p_vre_attribute16
1418 ,p_vre_attribute17 => p_vre_attribute17
1419 ,p_vre_attribute18 => p_vre_attribute18
1420 ,p_vre_attribute19 => p_vre_attribute19
1421 ,p_vre_attribute20 => p_vre_attribute20
1422 ,p_vre_information_category => p_vre_information_category
1423 ,p_vre_information1 => p_vre_information1
1424 ,p_vre_information2 => p_vre_information2
1425 ,p_vre_information3 => p_vre_information3
1426 ,p_vre_information4 => p_vre_information4
1427 ,p_vre_information5 => p_vre_information5
1428 ,p_vre_information6 => p_vre_information6
1429 ,p_vre_information7 => p_vre_information7
1430 ,p_vre_information8 => p_vre_information8
1431 ,p_vre_information9 => p_vre_information9
1432 ,p_vre_information10 => p_vre_information10
1433 ,p_vre_information11 => p_vre_information11
1434 ,p_vre_information12 => p_vre_information12
1435 ,p_vre_information13 => p_vre_information13
1436 ,p_vre_information14 => p_vre_information14
1437 ,p_vre_information15 => p_vre_information15
1438 ,p_vre_information16 => p_vre_information16
1439 ,p_vre_information17 => p_vre_information17
1440 ,p_vre_information18 => p_vre_information18
1441 ,p_vre_information19 => p_vre_information19
1442 ,p_vre_information20 => p_vre_information20
1443 ,p_vehicle_repository_id => l_vehicle_repository_id
1444 ,p_object_version_number => l_object_version_number
1445 ,p_effective_start_date => l_effective_start_date
1446 ,p_effective_end_date => l_effective_end_date
1447 );
1448
1449 -- Create allocations
1450
1451 FOR i in 1..l_user_info.count
1452 LOOP
1453
1454 /* Added below Code Snippet to check for Duplicate rows for same assignment as a part of bug#12937050.*/
1455 BEGIN
1456 SELECT COUNT(1)
1457 INTO l_count
1458 FROM pqp_vehicle_allocations_f pvf
1459 WHERE pvf.vehicle_repository_id = l_vehicle_repository_id
1460 AND pvf.assignment_id = l_user_info(i).assignment_id;
1461 END;
1462 IF l_count = 0 -- Condition to check whether vehicle for this assignment already exists or not.
1463 THEN
1464 pqp_vehicle_allocations_api.create_vehicle_allocation
1465 (p_validate => p_validate
1466 ,p_effective_date => p_effective_date
1467 ,p_assignment_id => l_user_info(i).assignment_id
1468 ,p_business_group_id => p_business_group_id
1469 ,p_vehicle_repository_id => l_vehicle_repository_id
1470 ,p_across_assignments => p_across_assignments
1471 ,p_usage_type => p_usage_type
1472 ,p_default_vehicle => p_default_vehicle
1473 ,p_fuel_card => p_fuel_card
1474 ,p_fuel_card_number => p_fuel_card_number
1475 ,p_insurance_number => p_insurance_number
1476 ,p_insurance_expiry_date => p_insurance_expiry_date
1477 ,p_val_attribute_category => p_val_attribute_category
1478 ,p_val_attribute1 => p_val_attribute1
1479 ,p_val_attribute2 => p_val_attribute2
1480 ,p_val_attribute3 => p_val_attribute3
1481 ,p_val_attribute4 => p_val_attribute4
1482 ,p_val_attribute5 => p_val_attribute5
1483 ,p_val_attribute6 => p_val_attribute6
1484 ,p_val_attribute7 => p_val_attribute7
1485 ,p_val_attribute8 => p_val_attribute8
1486 ,p_val_attribute9 => p_val_attribute9
1487 ,p_val_attribute10 => p_val_attribute10
1488 ,p_val_attribute11 => p_val_attribute11
1489 ,p_val_attribute12 => p_val_attribute12
1490 ,p_val_attribute13 => p_val_attribute13
1491 ,p_val_attribute14 => p_val_attribute14
1492 ,p_val_attribute15 => p_val_attribute15
1493 ,p_val_attribute16 => p_val_attribute16
1494 ,p_val_attribute17 => p_val_attribute17
1495 ,p_val_attribute18 => p_val_attribute18
1496 ,p_val_attribute19 => p_val_attribute19
1497 ,p_val_attribute20 => p_val_attribute20
1498 ,p_val_information_category => p_val_information_category
1499 ,p_val_information1 => p_val_information1
1500 ,p_val_information2 => p_val_information2
1501 ,p_val_information3 => p_val_information3
1502 ,p_val_information4 => p_val_information4
1503 ,p_val_information5 => p_val_information5
1504 ,p_val_information6 => p_val_information6
1505 ,p_val_information7 => p_val_information7
1506 ,p_val_information8 => p_val_information8
1507 ,p_val_information9 => p_val_information9
1508 ,p_val_information10 => p_val_information10
1509 ,p_val_information11 => p_val_information11
1510 ,p_val_information12 => p_val_information12
1511 ,p_val_information13 => p_val_information13
1512 ,p_val_information14 => p_val_information14
1513 ,p_val_information15 => p_val_information15
1514 ,p_val_information16 => p_val_information16
1515 ,p_val_information17 => p_val_information17
1516 ,p_val_information18 => p_val_information18
1517 ,p_val_information19 => p_val_information19
1518 ,p_val_information20 => p_val_information20
1519 ,p_fuel_benefit => p_fuel_benefit
1520 ,p_vehicle_allocation_id => l_vehicle_allocation_id
1521 ,p_object_version_number => l_object_version_number
1522 ,p_effective_start_date => l_effective_start_date
1523 ,p_effective_end_date => l_effective_end_date
1524 );
1525 END IF;
1526 END LOOP;
1527
1528
1529 ELSE
1530 --Update
1531 l_dt_adj:=0;
1532
1533 OPEN c_get_repository_id (p_registration_number
1534 ,p_business_group_id
1535 ,p_effective_date
1536 );
1537
1538 FETCH c_get_repository_id INTO l_get_repository_id;
1539 CLOSE c_get_repository_id;
1540
1541 l_object_version_number := l_get_repository_id.object_version_number;
1542 pqp_get_date_mode.find_dt_upd_modes
1543 (p_effective_date => p_effective_date
1544 ,p_base_table_name => 'PQP_VEHICLE_REPOSITORY_F'
1545 ,p_base_key_column => 'VEHICLE_REPOSITORY_ID'
1546 ,p_base_key_value => l_get_repository_id.vehicle_repository_id
1547 ,p_correction => l_correction
1548 ,p_update => l_update
1549 ,p_update_override => l_update_override
1550 ,p_update_change_insert => l_update_change_insert
1551 );
1552
1553 IF l_correction = 1 THEN
1554 l_datetrack_mode :='CORRECTION' ;
1555 ELSIF l_update = 1 THEN
1556 l_datetrack_mode :='UPDATE' ;
1557 END IF;
1558
1559 --Update vehicle repository
1560 <<update_vehicle>>
1561 BEGIN
1562 --Checking to see if the shared_vehicle is switched from Yes to No
1563 --This will reverse the process of calling the API as
1564 -- we need to end date all the allocations for the additional
1565 --users and then comeback to update the vehicle repository
1566 --by incrementing the update date by one. Without incrementing
1567 --the date the api will give an error because the end dated
1568 --allocation will fall on the same day when updating vehicle
1569 --repository with flag 'N' and this would give an error.
1570 IF l_get_repository_id.shared_vehicle ='Y'
1571 AND p_shared_vehicle ='N' AND l_chk<>2THEN
1572 RAISE e_exist_other_asg;
1573 END IF;
1574
1575 pqp_vehicle_repository_api.update_vehicle
1576 (p_validate => p_validate
1577 ,p_effective_date => p_effective_date+(l_dt_adj)
1578 ,p_datetrack_mode => l_datetrack_mode
1579 ,p_vehicle_repository_id => l_get_repository_id.vehicle_repository_id
1580 ,p_object_version_number => l_object_version_number
1581 ,p_registration_number => p_registration_number
1582 ,p_vehicle_type => p_vehicle_type
1583 ,p_vehicle_id_number => p_vehicle_id_number
1584 ,p_business_group_id => p_business_group_id
1585 ,p_make => p_make
1586 ,p_engine_capacity_in_cc => p_engine_capacity_in_cc
1587 ,p_fuel_type => p_fuel_type
1588 ,p_currency_code => p_currency_code
1589 ,p_vehicle_status => 'A'
1590 ,p_model => p_model
1591 ,p_initial_registration => p_initial_registration
1592 ,p_last_registration_renew_date => p_last_registration_renew_date
1593 ,p_fiscal_ratings => p_fiscal_ratings
1594 ,p_fiscal_ratings_uom => l_lookup_code
1595 ,p_vehicle_ownership => p_vehicle_ownership
1596 ,p_shared_vehicle => p_shared_vehicle
1597 ,p_taxation_method => p_taxation_method
1598 ,p_color => p_color
1599 ,p_seating_capacity => p_seating_capacity
1600 ,p_weight => p_weight
1601 ,p_weight_uom => p_weight_uom
1602 ,p_model_year => p_model_year
1603 ,p_insurance_number => p_insurance_number
1604 ,p_insurance_expiry_date => p_insurance_expiry_date
1605 ,p_comments => p_comments
1606 ,p_vre_attribute_category => p_vre_attribute_category
1607 ,p_vre_attribute1 => p_vre_attribute1
1608 ,p_vre_attribute2 => p_vre_attribute2
1609 ,p_vre_attribute3 => p_vre_attribute3
1610 ,p_vre_attribute4 => p_vre_attribute4
1611 ,p_vre_attribute5 => p_vre_attribute5
1612 ,p_vre_attribute6 => p_vre_attribute6
1613 ,p_vre_attribute7 => p_vre_attribute7
1614 ,p_vre_attribute8 => p_vre_attribute8
1615 ,p_vre_attribute9 => p_vre_attribute9
1616 ,p_vre_attribute10 => p_vre_attribute10
1617 ,p_vre_attribute11 => p_vre_attribute11
1618 ,p_vre_attribute12 => p_vre_attribute12
1619 ,p_vre_attribute13 => p_vre_attribute13
1620 ,p_vre_attribute14 => p_vre_attribute14
1621 ,p_vre_attribute15 => p_vre_attribute15
1622 ,p_vre_attribute16 => p_vre_attribute16
1623 ,p_vre_attribute17 => p_vre_attribute17
1624 ,p_vre_attribute18 => p_vre_attribute18
1625 ,p_vre_attribute19 => p_vre_attribute19
1626 ,p_vre_attribute20 => p_vre_attribute20
1627 ,p_vre_information1 => p_vre_information1
1628 ,p_vre_information2 => p_vre_information2
1629 ,p_vre_information3 => p_vre_information3
1630 ,p_vre_information4 => p_vre_information4
1631 ,p_vre_information5 => p_vre_information5
1632 ,p_vre_information6 => p_vre_information6
1633 ,p_vre_information7 => p_vre_information7
1634 ,p_vre_information8 => p_vre_information8
1635 ,p_vre_information9 => p_vre_information9
1636 ,p_vre_information10 => p_vre_information10
1637 ,p_vre_information11 => p_vre_information11
1638 ,p_vre_information12 => p_vre_information12
1639 ,p_vre_information13 => p_vre_information13
1640 ,p_vre_information14 => p_vre_information14
1641 ,p_vre_information15 => p_vre_information15
1642 ,p_vre_information16 => p_vre_information16
1643 ,p_vre_information17 => p_vre_information17
1644 ,p_vre_information18 => p_vre_information18
1645 ,p_vre_information19 => p_vre_information19
1646 ,p_vre_information20 => p_vre_information20
1647 ,p_effective_start_date => l_effective_start_date
1648 ,p_effective_end_date => l_effective_end_date
1649 );
1650 EXCEPTION
1651 WHEN e_exist_other_asg THEN
1652
1653 l_chk :=1;
1654 l_dt_adj:=1;
1655
1656
1657 END;
1658 -- call update allocation process
1659 --this process works in two different ways
1660 --first it checks for number of assignments that are using the
1661 --vehicle and if the assignment has increased then it will
1662 --create allocation for that assignment and then updates
1663 --all other existing assignments
1664 ---if the assignment has reduced then it will end date the allocation
1665 --for that assignment and updates the rest.
1666 IF l_chk < 2 THEN
1667
1668 update_vehicle_allocations
1669 (p_validate => p_validate
1670 ,p_effective_date => p_effective_date
1671 ,p_business_group_id => p_business_group_id
1672 ,p_registration_number => p_registration_number
1673 ,p_vehicle_repository_id => l_get_repository_id.vehicle_repository_id
1674 ,p_across_assignments => p_across_assignments
1675 ,p_usage_type => p_usage_type
1676 ,p_default_vehicle => p_default_vehicle
1677 ,p_fuel_card => p_fuel_card
1678 ,p_fuel_card_number => p_fuel_card_number
1679 ,p_insurance_number => p_insurance_number
1680 ,p_insurance_expiry_date => p_insurance_expiry_date
1681 ,p_val_attribute_category => p_val_attribute_category
1682 ,p_val_attribute1 => p_val_attribute1
1683 ,p_val_attribute2 => p_val_attribute2
1684 ,p_val_attribute3 => p_val_attribute3
1685 ,p_val_attribute4 => p_val_attribute4
1686 ,p_val_attribute5 => p_val_attribute5
1687 ,p_val_attribute6 => p_val_attribute6
1688 ,p_val_attribute7 => p_val_attribute7
1689 ,p_val_attribute8 => p_val_attribute8
1690 ,p_val_attribute9 => p_val_attribute9
1691 ,p_val_attribute10 => p_val_attribute10
1692 ,p_val_attribute11 => p_val_attribute11
1693 ,p_val_attribute12 => p_val_attribute12
1694 ,p_val_attribute13 => p_val_attribute13
1695 ,p_val_attribute14 => p_val_attribute14
1696 ,p_val_attribute15 => p_val_attribute15
1697 ,p_val_attribute16 => p_val_attribute16
1698 ,p_val_attribute17 => p_val_attribute17
1699 ,p_val_attribute18 => p_val_attribute18
1700 ,p_val_attribute19 => p_val_attribute19
1701 ,p_val_attribute20 => p_val_attribute20
1702 ,p_val_information_category => p_val_information_category
1703 ,p_val_information1 => p_val_information1
1704 ,p_val_information2 => p_val_information2
1705 ,p_val_information3 => p_val_information3
1706 ,p_val_information4 => p_val_information4
1707 ,p_val_information5 => p_val_information5
1708 ,p_val_information6 => p_val_information6
1709 ,p_val_information7 => p_val_information7
1710 ,p_val_information8 => p_val_information8
1711 ,p_val_information9 => p_val_information9
1712 ,p_val_information10 => p_val_information10
1713 ,p_val_information11 => p_val_information11
1714 ,p_val_information12 => p_val_information12
1715 ,p_val_information13 => p_val_information13
1716 ,p_val_information14 => p_val_information14
1717 ,p_val_information15 => p_val_information15
1718 ,p_val_information16 => p_val_information16
1719 ,p_val_information17 => p_val_information17
1720 ,p_val_information18 => p_val_information18
1721 ,p_val_information19 => p_val_information19
1722 ,p_val_information20 => p_val_information20
1723 ,p_fuel_benefit => p_fuel_benefit
1724 ,p_user_info => l_user_info
1725 ,p_error_message => p_error_message
1726 );
1727
1728 IF l_chk=1 THEN
1729 l_chk:=2;
1730 GOTO update_vehicle;
1731 END IF;
1732 END IF;
1733 END IF;
1734 EXCEPTION
1735 WHEN hr_utility.hr_error THEN
1736 hr_utility.raise_error;
1737 WHEN OTHERS THEN
1738 RAISE; -- Raise error here relevant to the new tech stack.
1739 END;
1740
1741 --This procedure is called to validate the incomming data
1742 --before inserting into the transaction table.
1743 PROCEDURE val_create_vehicle_details
1744 (
1745 p_validate in boolean default false
1746 ,p_effective_date in date
1747 ,p_registration_number in varchar2
1748 ,p_vehicle_type in varchar2
1749 ,p_vehicle_id_number in varchar2
1750 ,p_business_group_id in number
1751 ,p_make in varchar2
1752 ,p_engine_capacity_in_cc in number
1753 ,p_fuel_type in varchar2
1754 ,p_currency_code in varchar2
1755 ,p_model in varchar2
1756 ,p_initial_registration in date
1757 ,p_last_registration_renew_date in date
1758 ,p_fiscal_ratings in number
1759 ,p_vehicle_ownership in varchar2
1760 ,p_shared_vehicle in varchar2
1761 ,p_color in varchar2
1762 ,p_seating_capacity in number
1763 ,p_weight in number
1764 ,p_weight_uom in varchar2
1765 ,p_model_year in number
1766 ,p_insurance_number in varchar2
1767 ,p_insurance_expiry_date in date
1768 ,p_taxation_method in varchar2
1769 ,p_comments in varchar2
1770 ,p_vre_attribute_category in varchar2
1771 ,p_vre_attribute1 in varchar2
1772 ,p_vre_attribute2 in varchar2
1773 ,p_vre_attribute3 in varchar2
1774 ,p_vre_attribute4 in varchar2
1775 ,p_vre_attribute5 in varchar2
1776 ,p_vre_attribute6 in varchar2
1777 ,p_vre_attribute7 in varchar2
1778 ,p_vre_attribute8 in varchar2
1779 ,p_vre_attribute9 in varchar2
1780 ,p_vre_attribute10 in varchar2
1781 ,p_vre_attribute11 in varchar2
1782 ,p_vre_attribute12 in varchar2
1783 ,p_vre_attribute13 in varchar2
1784 ,p_vre_attribute14 in varchar2
1785 ,p_vre_attribute15 in varchar2
1786 ,p_vre_attribute16 in varchar2
1787 ,p_vre_attribute17 in varchar2
1788 ,p_vre_attribute18 in varchar2
1789 ,p_vre_attribute19 in varchar2
1790 ,p_vre_attribute20 in varchar2
1791 ,p_vre_information_category in varchar2
1792 ,p_vre_information1 in varchar2
1793 ,p_vre_information2 in varchar2
1794 ,p_vre_information3 in varchar2
1795 ,p_vre_information4 in varchar2
1796 ,p_vre_information5 in varchar2
1797 ,p_vre_information6 in varchar2
1798 ,p_vre_information7 in varchar2
1799 ,p_vre_information8 in varchar2
1800 ,p_vre_information9 in varchar2
1801 ,p_vre_information10 in varchar2
1802 ,p_vre_information11 in varchar2
1803 ,p_vre_information12 in varchar2
1804 ,p_vre_information13 in varchar2
1805 ,p_vre_information14 in varchar2
1806 ,p_vre_information15 in varchar2
1807 ,p_vre_information16 in varchar2
1808 ,p_vre_information17 in varchar2
1809 ,p_vre_information18 in varchar2
1810 ,p_vre_information19 in varchar2
1811 ,p_vre_information20 in varchar2
1812 ,p_across_assignments in varchar2
1813 ,p_usage_type in varchar2
1814 ,p_default_vehicle in varchar2
1815 ,p_fuel_card in varchar2
1816 ,p_fuel_card_number in varchar2
1817 ,p_val_attribute_category in varchar2
1818 ,p_val_attribute1 in varchar2
1819 ,p_val_attribute2 in varchar2
1820 ,p_val_attribute3 in varchar2
1821 ,p_val_attribute4 in varchar2
1822 ,p_val_attribute5 in varchar2
1823 ,p_val_attribute6 in varchar2
1824 ,p_val_attribute7 in varchar2
1825 ,p_val_attribute8 in varchar2
1826 ,p_val_attribute9 in varchar2
1827 ,p_val_attribute10 in varchar2
1828 ,p_val_attribute11 in varchar2
1829 ,p_val_attribute12 in varchar2
1830 ,p_val_attribute13 in varchar2
1831 ,p_val_attribute14 in varchar2
1832 ,p_val_attribute15 in varchar2
1833 ,p_val_attribute16 in varchar2
1834 ,p_val_attribute17 in varchar2
1835 ,p_val_attribute18 in varchar2
1836 ,p_val_attribute19 in varchar2
1837 ,p_val_attribute20 in varchar2
1838 ,p_val_information_category in varchar2
1839 ,p_val_information1 in varchar2
1840 ,p_val_information2 in varchar2
1841 ,p_val_information3 in varchar2
1842 ,p_val_information4 in varchar2
1843 ,p_val_information5 in varchar2
1844 ,p_val_information6 in varchar2
1845 ,p_val_information7 in varchar2
1846 ,p_val_information8 in varchar2
1847 ,p_val_information9 in varchar2
1848 ,p_val_information10 in varchar2
1849 ,p_val_information11 in varchar2
1850 ,p_val_information12 in varchar2
1851 ,p_val_information13 in varchar2
1852 ,p_val_information14 in varchar2
1853 ,p_val_information15 in varchar2
1854 ,p_val_information16 in varchar2
1855 ,p_val_information17 in varchar2
1856 ,p_val_information18 in varchar2
1857 ,p_val_information19 in varchar2
1858 ,p_val_information20 in varchar2
1859 ,p_fuel_benefit in varchar2
1860 ,p_user_info in t_user_info
1861 ,p_vehicle_repository_id in number
1862 ,p_vehicle_allocation_id in number
1863 ,p_object_version_number in number
1864 ,p_error_message OUT nocopy varchar2
1865 ,p_error_status OUT nocopy varchar2
1866
1867 )
1868 IS
1869 pragma autonomous_transaction;
1870 BEGIN
1871
1872 create_vehicle_details
1873 (
1874 p_validate => false
1875 ,p_effective_date =>p_effective_date
1876 ,p_registration_number =>p_registration_number
1877 ,p_vehicle_type =>p_vehicle_type
1878 ,p_vehicle_id_number =>p_vehicle_id_number
1879 ,p_business_group_id =>p_business_group_id
1880 ,p_make =>p_make
1881 ,p_engine_capacity_in_cc =>p_engine_capacity_in_cc
1882 ,p_fuel_type =>p_fuel_type
1883 ,p_currency_code =>p_currency_code
1884 ,p_model =>p_model
1885 ,p_initial_registration =>p_initial_registration
1886 ,p_last_registration_renew_date =>p_last_registration_renew_date
1887 ,p_fiscal_ratings =>p_fiscal_ratings
1888 ,p_vehicle_ownership =>p_vehicle_ownership
1889 ,p_shared_vehicle =>p_shared_vehicle
1890 ,p_color =>p_color
1891 ,p_seating_capacity =>p_seating_capacity
1892 ,p_weight =>p_weight
1893 ,p_weight_uom =>p_weight_uom
1894 ,p_model_year =>p_model_year
1895 ,p_insurance_number =>p_insurance_number
1896 ,p_insurance_expiry_date =>p_insurance_expiry_date
1897 ,p_taxation_method =>p_taxation_method
1898 ,p_comments =>p_comments
1899 ,p_vre_attribute_category =>p_vre_attribute_category
1900 ,p_vre_attribute1 =>p_vre_attribute1
1901 ,p_vre_attribute2 =>p_vre_attribute2
1902 ,p_vre_attribute3 =>p_vre_attribute3
1903 ,p_vre_attribute4 =>p_vre_attribute4
1904 ,p_vre_attribute5 =>p_vre_attribute5
1905 ,p_vre_attribute6 =>p_vre_attribute6
1906 ,p_vre_attribute7 =>p_vre_attribute7
1907 ,p_vre_attribute8 =>p_vre_attribute8
1908 ,p_vre_attribute9 =>p_vre_attribute9
1909 ,p_vre_attribute10 =>p_vre_attribute10
1910 ,p_vre_attribute11 =>p_vre_attribute11
1911 ,p_vre_attribute12 =>p_vre_attribute12
1912 ,p_vre_attribute13 =>p_vre_attribute13
1913 ,p_vre_attribute14 =>p_vre_attribute14
1914 ,p_vre_attribute15 =>p_vre_attribute15
1915 ,p_vre_attribute16 =>p_vre_attribute16
1916 ,p_vre_attribute17 =>p_vre_attribute17
1917 ,p_vre_attribute18 =>p_vre_attribute18
1918 ,p_vre_attribute19 =>p_vre_attribute19
1919 ,p_vre_attribute20 =>p_vre_attribute20
1920 ,p_vre_information_category =>p_vre_information_category
1921 ,p_vre_information1 =>p_vre_information1
1922 ,p_vre_information2 =>p_vre_information2
1923 ,p_vre_information3 =>p_vre_information3
1924 ,p_vre_information4 =>p_vre_information4
1925 ,p_vre_information5 =>p_vre_information5
1926 ,p_vre_information6 =>p_vre_information6
1927 ,p_vre_information7 =>p_vre_information7
1928 ,p_vre_information8 =>p_vre_information8
1929 ,p_vre_information9 =>p_vre_information9
1930 ,p_vre_information10 =>p_vre_information10
1931 ,p_vre_information11 =>p_vre_information11
1932 ,p_vre_information12 =>p_vre_information12
1933 ,p_vre_information13 =>p_vre_information13
1934 ,p_vre_information14 =>p_vre_information14
1935 ,p_vre_information15 =>p_vre_information15
1936 ,p_vre_information16 =>p_vre_information16
1937 ,p_vre_information17 =>p_vre_information17
1938 ,p_vre_information18 =>p_vre_information18
1939 ,p_vre_information19 =>p_vre_information19
1940 ,p_vre_information20 =>p_vre_information20
1941 ,p_across_assignments =>p_across_assignments
1942 ,p_usage_type =>p_usage_type
1943 ,p_default_vehicle =>p_default_vehicle
1944 ,p_fuel_card =>p_fuel_card
1945 ,p_fuel_card_number =>p_fuel_card_number
1946 ,p_val_attribute_category =>p_val_attribute_category
1947 ,p_val_attribute1 =>p_val_attribute1
1948 ,p_val_attribute2 =>p_val_attribute2
1949 ,p_val_attribute3 =>p_val_attribute3
1950 ,p_val_attribute4 =>p_val_attribute4
1951 ,p_val_attribute5 =>p_val_attribute5
1952 ,p_val_attribute6 =>p_val_attribute6
1953 ,p_val_attribute7 =>p_val_attribute7
1954 ,p_val_attribute8 =>p_val_attribute8
1955 ,p_val_attribute9 =>p_val_attribute9
1956 ,p_val_attribute10 =>p_val_attribute10
1957 ,p_val_attribute11 =>p_val_attribute11
1958 ,p_val_attribute12 =>p_val_attribute12
1959 ,p_val_attribute13 =>p_val_attribute13
1960 ,p_val_attribute14 =>p_val_attribute14
1961 ,p_val_attribute15 =>p_val_attribute15
1962 ,p_val_attribute16 =>p_val_attribute16
1963 ,p_val_attribute17 =>p_val_attribute17
1964 ,p_val_attribute18 =>p_val_attribute18
1965 ,p_val_attribute19 =>p_val_attribute19
1966 ,p_val_attribute20 =>p_val_attribute20
1967 ,p_val_information_category =>p_val_information_category
1968 ,p_val_information1 =>p_val_information1
1969 ,p_val_information2 =>p_val_information2
1970 ,p_val_information3 =>p_val_information3
1971 ,p_val_information4 =>p_val_information4
1972 ,p_val_information5 =>p_val_information5
1973 ,p_val_information6 =>p_val_information6
1974 ,p_val_information7 =>p_val_information7
1975 ,p_val_information8 =>p_val_information8
1976 ,p_val_information9 =>p_val_information9
1977 ,p_val_information10 =>p_val_information10
1978 ,p_val_information11 =>p_val_information11
1979 ,p_val_information12 =>p_val_information12
1980 ,p_val_information13 =>p_val_information13
1981 ,p_val_information14 =>p_val_information14
1982 ,p_val_information15 =>p_val_information15
1983 ,p_val_information16 =>p_val_information16
1984 ,p_val_information17 =>p_val_information17
1985 ,p_val_information18 =>p_val_information18
1986 ,p_val_information19 =>p_val_information19
1987 ,p_val_information20 =>p_val_information20
1988 ,p_fuel_benefit =>p_fuel_benefit
1989 ,p_user_info =>p_user_info
1990 ,p_vehicle_repository_id =>p_vehicle_repository_id
1991 ,p_vehicle_allocation_id =>p_vehicle_allocation_id
1992 ,p_object_version_number =>p_object_version_number
1993 ,p_error_message =>p_error_message
1994 ,p_error_status =>p_error_status
1995 );
1996
1997
1998 ROLLBACK;
1999 END;
2000
2001
2002 ---Not used at the moment
2003
2004 PROCEDURE delete_vehicle_details(
2005 x_p_validate IN BOOLEAN
2006 ,x_effective_date IN DATE
2007 ,x_login_person_id IN NUMBER
2008 ,x_person_id IN NUMBER
2009 ,x_assignment_id IN NUMBER
2010 ,x_business_group_id IN NUMBER
2011 ,x_item_key IN NUMBER
2012 ,x_item_type IN VARCHAR2
2013 ,x_activity_id IN NUMBER
2014 ,x_vehicle_allocation_id IN NUMBER
2015 ,x_status IN VARCHAR2
2016 ,x_transaction_id IN OUT NOCOPY NUMBER
2017 ,x_error_status OUT NOCOPY VARCHAR2
2018 )
2019
2020 IS
2021
2022
2023
2024 CURSOR c_del_values
2025 IS
2026 SELECT transaction_step_id
2027 FROM hr_api_transaction_steps hats
2028 WHERE transaction_id = x_transaction_id;
2029
2030 l_del_values c_del_values%ROWTYPE;
2031 l_transaction_id NUMBER;
2032 l_trans_tbl hr_transaction_ss.transaction_table;
2033 l_count NUMBER :=0;
2034 l_transaction_step_id NUMBER;
2035 l_api_name hr_api_transaction_steps.api_name%TYPE
2036 := 'PQP_SS_VEHICLE_TRANSACTIONS.DELETE_PROCESS_API';
2037 l_result VARCHAR2(100);
2038 l_trns_object_version_number NUMBER;
2039 l_review_proc_call VARCHAR2(30) := 'PqpVehDelReview';
2040 l_effective_date DATE := SYSDATE;
2041 l_ovn NUMBER;
2042 l_error_message VARCHAR2(80);
2043 l_error_status VARCHAR2(10);
2044 BEGIN
2045
2046 --Validate the data before inserting into
2047 -- transaction table.
2048 delete_process (
2049 p_validate => true
2050 ,p_effective_date => x_effective_date
2051 ,p_person_id => x_person_id
2052 ,p_assignment_id => x_assignment_id
2053 ,p_business_group_id => x_business_group_id
2054 ,p_vehicle_allocation_id => x_vehicle_allocation_id
2055 ,p_error_status => l_error_status
2056 );
2057
2058 l_count:=1;
2059 l_trans_tbl(l_count).param_name := 'P_LOGIN_PERSON_ID';
2060 l_trans_tbl(l_count).param_value := x_login_person_id;
2061 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2062
2063 l_count:=l_count+1;
2064 l_trans_tbl(l_count).param_name := 'P_EFFECTIVE_DATE';
2065 l_trans_tbl(l_count).param_value := x_effective_date;
2066 l_trans_tbl(l_count).param_data_type := 'DATE';
2067
2068 l_count:=l_count+1;
2069 l_trans_tbl(l_count).param_name := 'P_REVIEW_PROC_CALL';
2070 l_trans_tbl(l_count).param_value := l_review_proc_call;
2071 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2072
2073 l_count:=l_count+1;
2074 l_trans_tbl(l_count).param_name := 'P_REVIEW_ACTID';
2075 l_trans_tbl(l_count).param_value := x_activity_id;
2076 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2077
2078 l_count:=l_count+1;
2079 l_trans_tbl(l_count).param_name := 'P_PERSON_ID';
2080 l_trans_tbl(l_count).param_value := x_person_id;
2081 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2082
2083 l_count:=l_count+1;
2084 l_trans_tbl(l_count).param_name := 'P_ASSIGNMENT_ID';
2085 l_trans_tbl(l_count).param_value := x_ASSIGNMENT_id;
2086 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2087
2088 l_count:=l_count+1;
2089 l_trans_tbl(l_count).param_name := 'P_BUSINESS_GROUP_ID';
2090 l_trans_tbl(l_count).param_value := x_business_group_id;
2091 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2092
2093 l_count:=l_count+1;
2094 l_trans_tbl(l_count).param_name := 'P_ALLOCATION_ID';
2095 l_trans_tbl(l_count).param_value := x_vehicle_allocation_id;
2096 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2097
2098 IF x_transaction_id is NULl THEN
2099 hr_transaction_api.create_transaction(
2100 p_validate => false
2101 ,p_creator_person_id => x_login_person_id
2102 ,p_transaction_privilege => 'PRIVATE'
2103 ,p_product_code => 'PQP'
2104 ,p_url => NULL
2105 ,p_status => x_status
2106 ,p_section_display_name => NULL
2107 ,p_function_id => NULL
2108 ,p_transaction_ref_table => NULL
2109 ,p_transaction_ref_id => NULL
2110 ,p_transaction_type => 'WF'
2111 ,p_assignment_id => x_assignment_id
2112 ,p_selected_person_id => x_person_id
2113 ,p_item_type => x_item_type
2114 ,p_item_key => x_item_key
2115 ,p_transaction_effective_date => x_effective_date
2116 ,p_process_name => NULL
2117 ,p_plan_id => NULL
2118 ,p_rptg_grp_id => NULL
2119 ,p_effective_date_option => x_effective_date
2120 ,p_transaction_id => l_transaction_id
2121 );
2122 wf_engine.setitemattrnumber
2123 (itemtype => x_item_type
2124 ,itemkey => x_item_key
2125 ,aname => 'TRANSACTION_ID'
2126 ,avalue => l_transaction_id);
2127 x_transaction_id := l_transaction_id;
2128 --Create transaction steps
2129 hr_transaction_api.create_transaction_step
2130 (p_validate => false
2131 ,p_creator_person_id => x_login_person_id
2132 ,p_transaction_id => l_transaction_id
2133 ,p_api_name => l_api_name
2134 ,p_api_display_name => l_api_name
2135 ,p_item_type => x_item_type
2136 ,p_item_key => x_item_key
2137 ,p_activity_id => x_activity_id
2138 ,p_transaction_step_id => l_transaction_step_id
2139 ,p_object_version_number => l_ovn
2140 );
2141 ELSE
2142
2143 OPEN c_del_values;
2144 FETCH c_del_values INTO l_del_values;
2145 CLOSE c_del_values;
2146
2147 DELETE from hr_api_transaction_values
2148 WHERE transaction_step_id = l_del_values.transaction_step_id;
2149 l_transaction_step_id := l_del_values.transaction_step_id;
2150 END IF;
2151
2152
2153 FOR i in 1..l_trans_tbl.count
2154 LOOP
2155 IF l_trans_tbl(i).param_data_type ='VARCHAR2' THEN
2156 hr_transaction_api.set_varchar2_value
2157 (p_transaction_step_id => l_transaction_step_id
2158 ,p_person_id => x_person_id
2159 ,p_name => l_trans_tbl (i).param_name
2160 ,p_value => l_trans_tbl (i).param_value
2161 );
2162
2163 ELSIF l_trans_tbl(i).param_data_type ='DATE' THEN
2164 hr_transaction_api.set_date_value
2165 (
2166 p_transaction_step_id => l_transaction_step_id
2167 ,p_person_id => x_person_id
2168 ,p_name => l_trans_tbl (i).param_name
2169 ,p_value => fnd_date.displaydate_to_date
2170 (l_trans_tbl (i).param_value ) );
2171 -- ,p_original_value );
2172
2173
2174 ELSIF l_trans_tbl(i).param_data_type ='NUMBER' THEN
2175 hr_transaction_api.set_number_value
2176 (
2177 p_transaction_step_id => l_transaction_step_id
2178 ,p_person_id => x_person_id
2179 ,p_name =>l_trans_tbl (i).param_name
2180 ,p_value =>TO_NUMBER(l_trans_tbl (i).param_value ));
2181 END IF;
2182 END LOOP;
2183
2184 EXCEPTION
2185 WHEN hr_utility.hr_error THEN
2186 hr_utility.raise_error;
2187 WHEN OTHERS THEN
2188 RAISE; -- Raise error here relevant to the new tech stack.
2189
2190 END;
2191
2192 PROCEDURE set_vehicle_details (
2193 x_p_validate IN BOOLEAN
2194 ,x_effective_date IN DATE
2195 ,x_login_person_id IN NUMBER
2196 ,x_person_id IN NUMBER
2197 ,x_assignment_id IN NUMBER
2198 ,x_item_type IN VARCHAR2
2199 ,x_item_key IN NUMBER
2200 ,x_activity_id IN NUMBER
2201 ,x_registration_number IN VARCHAR2
2202 ,x_vehicle_ownership IN VARCHAR2
2203 ,x_vehicle_type IN VARCHAR2
2204 ,x_vehicle_id_number IN VARCHAR2
2205 ,x_business_group_id IN NUMBER
2206 ,x_make IN VARCHAR2
2207 ,x_engine_capacity_in_cc IN NUMBER
2208 ,x_fuel_type IN VARCHAR2
2209 ,x_currency_code IN VARCHAR2
2210 ,x_model IN VARCHAR2
2211 ,x_initial_registration IN DATE
2212 ,x_last_registration_renew_date IN DATE
2213 ,x_fiscal_ratings IN NUMBER
2214 ,x_shared_vehicle IN VARCHAR2
2215 ,x_color IN VARCHAR2
2216 ,x_seating_capacity IN NUMBER
2217 ,x_weight IN NUMBER
2218 ,x_weight_uom IN VARCHAR2
2219 ,x_model_year IN NUMBER
2220 ,x_insurance_number IN VARCHAR2
2221 ,x_insurance_expiry_date IN DATE
2222 ,x_taxation_method IN VARCHAR2
2223 ,x_comments IN VARCHAR2
2224 ,x_vre_attribute_category IN VARCHAR2
2225 ,x_vre_attribute1 IN VARCHAR2
2226 ,x_vre_attribute2 IN VARCHAR2
2227 ,x_vre_attribute3 IN VARCHAR2
2228 ,x_vre_attribute4 IN VARCHAR2
2229 ,x_vre_attribute5 IN VARCHAR2
2230 ,x_vre_attribute6 IN VARCHAR2
2231 ,x_vre_attribute7 IN VARCHAR2
2232 ,x_vre_attribute8 IN VARCHAR2
2233 ,x_vre_attribute9 IN VARCHAR2
2234 ,x_vre_attribute10 IN VARCHAR2
2235 ,x_vre_attribute11 IN VARCHAR2
2236 ,x_vre_attribute12 IN VARCHAR2
2237 ,x_vre_attribute13 IN VARCHAR2
2238 ,x_vre_attribute14 IN VARCHAR2
2239 ,x_vre_attribute15 IN VARCHAR2
2240 ,x_vre_attribute16 IN VARCHAR2
2241 ,x_vre_attribute17 IN VARCHAR2
2242 ,x_vre_attribute18 IN VARCHAR2
2243 ,x_vre_attribute19 IN VARCHAR2
2244 ,x_vre_attribute20 IN VARCHAR2
2245 ,x_vre_information_category IN VARCHAR2
2246 ,x_vre_information1 IN VARCHAR2
2247 ,x_vre_information2 IN VARCHAR2
2248 ,x_vre_information3 IN VARCHAR2
2249 ,x_vre_information4 IN VARCHAR2
2250 ,x_vre_information5 IN VARCHAR2
2251 ,x_vre_information6 IN VARCHAR2
2252 ,x_vre_information7 IN VARCHAR2
2253 ,x_vre_information8 IN VARCHAR2
2254 ,x_vre_information9 IN VARCHAR2
2255 ,x_vre_information10 IN VARCHAR2
2256 ,x_vre_information11 IN VARCHAR2
2257 ,x_vre_information12 IN VARCHAR2
2258 ,x_vre_information13 IN VARCHAR2
2259 ,x_vre_information14 IN VARCHAR2
2260 ,x_vre_information15 IN VARCHAR2
2261 ,x_vre_information16 IN VARCHAR2
2262 ,x_vre_information17 IN VARCHAR2
2263 ,x_vre_information18 IN VARCHAR2
2264 ,x_vre_information19 IN VARCHAR2
2265 ,x_vre_information20 IN VARCHAR2
2266 ,x_across_assignments IN VARCHAR2
2267 ,x_usage_type IN VARCHAR2
2268 ,x_default_vehicle IN VARCHAR2
2269 ,x_fuel_card IN VARCHAR2
2270 ,x_fuel_card_number IN VARCHAR2
2271 ,x_val_attribute_category IN VARCHAR2
2272 ,x_val_attribute1 IN VARCHAR2
2273 ,x_val_attribute2 IN VARCHAR2
2274 ,x_val_attribute3 IN VARCHAR2
2275 ,x_val_attribute4 IN VARCHAR2
2276 ,x_val_attribute5 IN VARCHAR2
2277 ,x_val_attribute6 IN VARCHAR2
2278 ,x_val_attribute7 IN VARCHAR2
2279 ,x_val_attribute8 IN VARCHAR2
2280 ,x_val_attribute9 IN VARCHAR2
2281 ,x_val_attribute10 IN VARCHAR2
2282 ,x_val_attribute11 IN VARCHAR2
2283 ,x_val_attribute12 IN VARCHAR2
2284 ,x_val_attribute13 IN VARCHAR2
2285 ,x_val_attribute14 IN VARCHAR2
2286 ,x_val_attribute15 IN VARCHAR2
2287 ,x_val_attribute16 IN VARCHAR2
2288 ,x_val_attribute17 IN VARCHAR2
2289 ,x_val_attribute18 IN VARCHAR2
2290 ,x_val_attribute19 IN VARCHAR2
2291 ,x_val_attribute20 IN VARCHAR2
2292 ,x_val_information_category IN VARCHAR2
2293 ,x_val_information1 IN VARCHAR2
2294 ,x_val_information2 IN VARCHAR2
2295 ,x_val_information3 IN VARCHAR2
2296 ,x_val_information4 IN VARCHAR2
2297 ,x_val_information5 IN VARCHAR2
2298 ,x_val_information6 IN VARCHAR2
2299 ,x_val_information7 IN VARCHAR2
2300 ,x_val_information8 IN VARCHAR2
2301 ,x_val_information9 IN VARCHAR2
2302 ,x_val_information10 IN VARCHAR2
2303 ,x_val_information11 IN VARCHAR2
2304 ,x_val_information12 IN VARCHAR2
2305 ,x_val_information13 IN VARCHAR2
2306 ,x_val_information14 IN VARCHAR2
2307 ,x_val_information15 IN VARCHAR2
2308 ,x_val_information16 IN VARCHAR2
2309 ,x_val_information17 IN VARCHAR2
2310 ,x_val_information18 IN VARCHAR2
2311 ,x_val_information19 IN VARCHAR2
2312 ,x_val_information20 IN VARCHAR2
2313 ,x_fuel_benefit IN VARCHAR2
2314 ,x_user_info IN t_user_info
2315 ,x_status IN VARCHAR2
2316 ,x_effective_date_option IN VARCHAR2
2317 ,x_vehicle_repository_id IN NUMBER
2318 ,x_vehicle_allocation_id IN NUMBER
2319 ,x_object_version_number in NUMBER
2320 ,x_error_status OUT NOCOPY VARCHAR2
2321 ,x_transaction_id IN OUT NOCOPY NUMBER
2322 )
2323 IS
2324 CURSOR c_del_values
2325 IS
2326 SELECT transaction_step_id
2327 FROM hr_api_transaction_steps hats
2328 WHERE transaction_id = x_transaction_id;
2329
2330 l_del_values c_del_values%ROWTYPE;
2331 l_transaction_id NUMBER;
2332 l_trans_tbl hr_transaction_ss.transaction_table;
2333 l_count NUMBER :=0;
2334 l_transaction_step_id NUMBER;
2335 l_api_name hr_api_transaction_steps.api_name%TYPE
2336 := 'PQP_SS_VEHICLE_TRANSACTIONS.PROCESS_API';
2337 l_result VARCHAR2(100);
2338 l_trns_object_version_number number;
2339 l_review_proc_call VARCHAR2(30) := 'PqpVehInfoReview';
2340 l_effective_date DATE := SYSDATE;
2341 l_ovn NUMBER;
2342 l_error_message VARCHAR2(80);
2343 l_error_status VARCHAR2(10);
2344 u_count NUMBER;
2345 l_sec_result VARCHAR2(100);
2346 BEGIN
2347 /* Bug Fix 13870983 Starts here.
2348 Assigning back null values to below two parameters if they have a value of zero. */
2349
2350 if x_transaction_id = 0 then
2351 x_transaction_id := null;
2352 end if;
2353
2354 /* Bug Fix 13870983 Ends here */
2355
2356 hr_utility.set_location('Enter:Set Vehicle Details' ,5);
2357 SAVEPOINT pqp_vehicle_proc_start;
2358 BEGIN
2359 hr_utility.set_location('Enter:Set Paramaters' ,10);
2360 SAVEPOINT pqp_vehicle_validate;
2361 hr_multi_message.enable_message_list;
2362 hr_utility.set_location('Entering: enter set vehicle details',5);
2363 l_count:=l_count+1;
2364 l_trans_tbl(l_count).param_name := 'P_LOGIN_PERSON_ID';
2365 l_trans_tbl(l_count).param_value := x_login_person_id;
2366 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2367
2368 l_count:=l_count+1;
2369 l_trans_tbl(l_count).param_name := 'P_REVIEW_PROC_CALL';
2370 l_trans_tbl(l_count).param_value := l_review_proc_call;
2371 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2372
2373 l_count:=l_count+1;
2374 l_trans_tbl(l_count).param_name := 'P_REVIEW_ACTID';
2375 l_trans_tbl(l_count).param_value := x_activity_id;
2376 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2377
2378 l_count:=l_count+1;
2379 l_trans_tbl(l_count).param_name := 'P_EFFECTIVE_DATE';
2380 l_trans_tbl(l_count).param_value := x_effective_date;
2381 l_trans_tbl(l_count).param_data_type := 'DATE';
2382
2383 l_count:=l_count+1;
2384 l_trans_tbl(l_count).param_name := 'P_PERSON_ID';
2385 l_trans_tbl(l_count).param_value := x_person_id;
2386 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2387
2388 l_count:=l_count+1;
2389 l_trans_tbl(l_count).param_name := 'P_ASSIGNMENT_ID';
2390 l_trans_tbl(l_count).param_value := x_ASSIGNMENT_id;
2391 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2392
2393 l_count:=l_count+1;
2394 l_trans_tbl(l_count).param_name := 'P_REGISTRATION_NUMBER';
2395 l_trans_tbl(l_count).param_value := x_registration_number;
2396 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2397
2398 l_count:=l_count+1;
2399 l_trans_tbl(l_count).param_name := 'P_VEHICLE_OWNERSHIP';
2400 l_trans_tbl(l_count).param_value := x_vehicle_ownership;
2401 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2402
2403 l_count:=l_count+1;
2404 l_trans_tbl(l_count).param_name := 'P_VEHICLE_TYPE';
2405 l_trans_tbl(l_count).param_value := x_vehicle_type;
2406 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2407
2408
2409 l_count:=l_count+1;
2410 l_trans_tbl(l_count).param_name := 'P_VEHICLE_ID_NUMBER';
2411 l_trans_tbl(l_count).param_value := x_vehicle_id_number;
2412 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2413
2414
2415 l_count:=l_count+1;
2416 l_trans_tbl(l_count).param_name := 'P_BUSINESS_GROUP_ID';
2417 l_trans_tbl(l_count).param_value := x_business_group_id;
2418 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2419
2420
2421 l_count:=l_count+1;
2422 l_trans_tbl(l_count).param_name := 'P_MAKE';
2423 l_trans_tbl(l_count).param_value := x_make;
2424 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2425
2426
2427 l_count:=l_count+1;
2428 l_trans_tbl(l_count).param_name := 'P_ENGINE_CAPACITY_IN_CC';
2429 l_trans_tbl(l_count).param_value := x_engine_capacity_in_cc;
2430 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2431
2432 l_count:=l_count+1;
2433 l_trans_tbl(l_count).param_name := 'P_FUEL_TYPE';
2434 l_trans_tbl(l_count).param_value := x_fuel_type;
2435 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2436
2437
2438 l_count:=l_count+1;
2439 l_trans_tbl(l_count).param_name := 'P_CURRENCY_CODE';
2440 l_trans_tbl(l_count).param_value := x_currency_code;
2441 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2442
2443
2444 l_count:=l_count+1;
2445 l_trans_tbl(l_count).param_name := 'P_MODEL';
2446 l_trans_tbl(l_count).param_value := x_model;
2447 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2448
2449
2450 l_count:=l_count+1;
2451 l_trans_tbl(l_count).param_name := 'P_INITIAL_REGISTRATION';
2452 l_trans_tbl(l_count).param_value := x_initial_registration;
2453 l_trans_tbl(l_count).param_data_type := 'DATE';
2454
2455
2456 l_count:=l_count+1;
2457 l_trans_tbl(l_count).param_name := 'P_LAST_REGISTRATION_RENEW_DATE';
2458 l_trans_tbl(l_count).param_value := x_last_registration_renew_date;
2459 l_trans_tbl(l_count).param_data_type := 'DATE';
2460
2461
2462 l_count:=l_count+1;
2463 l_trans_tbl(l_count).param_name := 'P_FISCAL_RATINGS';
2464 l_trans_tbl(l_count).param_value := x_fiscal_ratings;
2465 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2466
2467
2468 l_count:=l_count+1;
2469 l_trans_tbl(l_count).param_name := 'P_SHARED_VEHICLE';
2470 l_trans_tbl(l_count).param_value := x_shared_vehicle;
2471 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2472
2473
2474 l_count:=l_count+1;
2475 l_trans_tbl(l_count).param_name := 'P_COLOR';
2476 l_trans_tbl(l_count).param_value := x_color;
2477 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2478
2479
2480 l_count:=l_count+1;
2481 l_trans_tbl(l_count).param_name := 'P_SEATING_CAPACITY';
2482 l_trans_tbl(l_count).param_value := x_seating_capacity;
2483 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2484
2485
2486
2487 l_count:=l_count+1;
2488 l_trans_tbl(l_count).param_name := 'P_WEIGHT';
2489 l_trans_tbl(l_count).param_value := x_weight;
2490 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2491
2492
2493 l_count:=l_count+1;
2494 l_trans_tbl(l_count).param_name := 'P_WEIGHT_UOM';
2495 l_trans_tbl(l_count).param_value := x_weight_uom;
2496 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2497
2498
2499 l_count:=l_count+1;
2500 l_trans_tbl(l_count).param_name := 'P_MODEL_YEAR';
2501 l_trans_tbl(l_count).param_value := x_model_year;
2502 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2503
2504 l_count:=l_count+1;
2505 l_trans_tbl(l_count).param_name := 'P_INSURANCE_NUMBER';
2506 l_trans_tbl(l_count).param_value := x_insurance_number;
2507 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2508
2509 l_count:=l_count+1;
2510 l_trans_tbl(l_count).param_name := 'P_INSURANCE_EXPIRY_DATE';
2511 l_trans_tbl(l_count).param_value := x_insurance_expiry_date;
2512 l_trans_tbl(l_count).param_data_type := 'DATE';
2513
2514 l_count:=l_count+1;
2515 l_trans_tbl(l_count).param_name := 'P_TAXATION_METHOD';
2516 l_trans_tbl(l_count).param_value := x_taxation_method;
2517 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2518
2519 l_count:=l_count+1;
2520 l_trans_tbl(l_count).param_name := 'P_COMMENTS';
2521 l_trans_tbl(l_count).param_value := x_comments;
2522 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2523
2524
2525 l_count:=l_count+1;
2526 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE_CATEGORY';
2527 l_trans_tbl(l_count).param_value := x_vre_attribute_category;
2528 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2529
2530
2531 l_count:=l_count+1;
2532 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE1';
2533 l_trans_tbl(l_count).param_value := x_vre_attribute1;
2534 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2535
2536 l_count:=l_count+1;
2537 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE2';
2538 l_trans_tbl(l_count).param_value := x_vre_attribute2;
2539 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2540
2541
2542 l_count:=l_count+1;
2543 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE3';
2544 l_trans_tbl(l_count).param_value := x_vre_attribute3;
2545 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2546
2547
2548 l_count:=l_count+1;
2549 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE4';
2550 l_trans_tbl(l_count).param_value := x_vre_attribute4;
2551 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2552
2553
2554 l_count:=l_count+1;
2555 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE5';
2556 l_trans_tbl(l_count).param_value := x_vre_attribute5;
2557 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2558
2559
2560
2561 l_count:=l_count+1;
2562 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE6';
2563 l_trans_tbl(l_count).param_value := x_vre_attribute6;
2564 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2565
2566
2567 l_count:=l_count+1;
2568 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE7';
2569 l_trans_tbl(l_count).param_value := x_vre_attribute7;
2570 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2571
2572
2573 l_count:=l_count+1;
2574 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE8';
2575 l_trans_tbl(l_count).param_value := x_vre_attribute8;
2576 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2577
2578
2579 l_count:=l_count+1;
2580 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE9';
2581 l_trans_tbl(l_count).param_value := x_vre_attribute9;
2582 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2583
2584
2585 l_count:=l_count+1;
2586 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE10';
2587 l_trans_tbl(l_count).param_value := x_vre_attribute10;
2588 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2589
2590
2591 l_count:=l_count+1;
2592 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE11';
2593 l_trans_tbl(l_count).param_value := x_vre_attribute11;
2594 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2595
2596
2597 l_count:=l_count+1;
2598 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE12';
2599 l_trans_tbl(l_count).param_value := x_vre_attribute12;
2600 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2601
2602
2603 l_count:=l_count+1;
2604 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE13';
2605 l_trans_tbl(l_count).param_value := x_vre_attribute13;
2606 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2607
2608
2609 l_count:=l_count+1;
2610 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE14';
2611 l_trans_tbl(l_count).param_value := x_vre_attribute14;
2612 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2613
2614 l_count:=l_count+1;
2615 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE15';
2616 l_trans_tbl(l_count).param_value := x_vre_attribute15;
2617 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2618
2619
2620 l_count:=l_count+1;
2621 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE16';
2622 l_trans_tbl(l_count).param_value := x_vre_attribute16;
2623 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2624
2625
2626 l_count:=l_count+1;
2627 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE17';
2628 l_trans_tbl(l_count).param_value := x_vre_attribute17;
2629 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2630
2631
2632 l_count:=l_count+1;
2633 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE18';
2634 l_trans_tbl(l_count).param_value := x_vre_attribute18;
2635 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2636
2637
2638 l_count:=l_count+1;
2639 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE19';
2640 l_trans_tbl(l_count).param_value := x_vre_attribute19;
2641 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2642
2643 l_count:=l_count+1;
2644 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE20';
2645 l_trans_tbl(l_count).param_value := x_vre_attribute20;
2646 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2647
2648
2649 l_count:=l_count+1;
2650 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION_CATEGORY';
2651 l_trans_tbl(l_count).param_value := x_vre_information_category;
2652 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2653
2654
2655 l_count:=l_count+1;
2656 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION1';
2657 l_trans_tbl(l_count).param_value := x_vre_information1;
2658 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2659
2660
2661 l_count:=l_count+1;
2662 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION2';
2663 l_trans_tbl(l_count).param_value := x_vre_information2;
2664 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2665
2666 l_count:=l_count+1;
2667 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION3';
2668 l_trans_tbl(l_count).param_value := x_vre_information3;
2669 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2670
2671
2672 l_count:=l_count+1;
2673 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION4';
2674 l_trans_tbl(l_count).param_value := x_vre_information4;
2675 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2676
2677 l_count:=l_count+1;
2678 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION5';
2679 l_trans_tbl(l_count).param_value := x_vre_information5;
2680 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2681
2682
2683 l_count:=l_count+1;
2684 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION6';
2685 l_trans_tbl(l_count).param_value := x_vre_information6;
2686 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2687
2688
2689 l_count:=l_count+1;
2690 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION7';
2691 l_trans_tbl(l_count).param_value := x_vre_information7;
2692 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2693
2694 l_count:=l_count+1;
2695 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION8';
2696 l_trans_tbl(l_count).param_value := x_vre_information8;
2697 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2698
2699
2700 l_count:=l_count+1;
2701 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION9';
2702 l_trans_tbl(l_count).param_value := x_vre_information9;
2703 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2704
2705 l_count:=l_count+1;
2706 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION10';
2707 l_trans_tbl(l_count).param_value := x_vre_information10;
2708 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2709
2710 l_count:=l_count+1;
2711 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION11';
2712 l_trans_tbl(l_count).param_value := x_vre_information11;
2713 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2714
2715 l_count:=l_count+1;
2716 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION12';
2717 l_trans_tbl(l_count).param_value := x_vre_information12;
2718 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2719
2720
2721 l_count:=l_count+1;
2722 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION13';
2723 l_trans_tbl(l_count).param_value := x_vre_information13;
2724 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2725
2726 l_count:=l_count+1;
2727 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION14';
2728 l_trans_tbl(l_count).param_value := x_vre_information14;
2729 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2730
2731 l_count:=l_count+1;
2732 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION15';
2733 l_trans_tbl(l_count).param_value := x_vre_information15;
2734 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2735
2736
2737 l_count:=l_count+1;
2738 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION16';
2739 l_trans_tbl(l_count).param_value := x_vre_information16;
2740 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2741
2742 l_count:=l_count+1;
2743 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION17';
2744 l_trans_tbl(l_count).param_value := x_vre_information17;
2745 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2746
2747
2748 l_count:=l_count+1;
2749 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION18';
2750 l_trans_tbl(l_count).param_value := x_vre_information18;
2751 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2752
2753 l_count:=l_count+1;
2754 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION19';
2755 l_trans_tbl(l_count).param_value := x_vre_information19;
2756 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2757
2758
2759 l_count:=l_count+1;
2760 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION20';
2761 l_trans_tbl(l_count).param_value := x_vre_information20;
2762 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2763
2764 l_count:=l_count+1;
2765 l_trans_tbl(l_count).param_name := 'P_ACROSS_ASSIGNMENTS';
2766 l_trans_tbl(l_count).param_value := x_across_assignments;
2767 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2768
2769 l_count:=l_count+1;
2770 l_trans_tbl(l_count).param_name := 'P_USAGE_TYPE';
2771 l_trans_tbl(l_count).param_value := x_usage_type;
2772 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2773
2774 l_count:=l_count+1;
2775 l_trans_tbl(l_count).param_name := 'P_DEFAULT_VEHICLE';
2776 l_trans_tbl(l_count).param_value := x_default_vehicle;
2777 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2778
2779
2780 l_count:=l_count+1;
2781 l_trans_tbl(l_count).param_name := 'P_FUEL_CARD';
2782 l_trans_tbl(l_count).param_value := x_fuel_card;
2783 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2784
2785 l_count:=l_count+1;
2786 l_trans_tbl(l_count).param_name := 'P_FUEL_CARD_NUMBER';
2787 l_trans_tbl(l_count).param_value := x_fuel_card_number;
2788 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2789
2790
2791 l_count:=l_count+1;
2792 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE_CATEGORY';
2793 l_trans_tbl(l_count).param_value := x_val_attribute_category;
2794 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2795
2796
2797 l_count:=l_count+1;
2798 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE1';
2799 l_trans_tbl(l_count).param_value := x_val_attribute1;
2800 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2801
2802
2803 l_count:=l_count+1;
2804 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE2';
2805 l_trans_tbl(l_count).param_value := x_val_attribute2;
2806 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2807
2808
2809 l_count:=l_count+1;
2810 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE3';
2811 l_trans_tbl(l_count).param_value := x_val_attribute3;
2812 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2813
2814
2815 l_count:=l_count+1;
2816 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE4';
2817 l_trans_tbl(l_count).param_value := x_val_attribute4;
2818 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2819
2820
2821 l_count:=l_count+1;
2822 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE5';
2823 l_trans_tbl(l_count).param_value := x_val_attribute5;
2824 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2825
2826
2827 l_count:=l_count+1;
2828 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE6';
2829 l_trans_tbl(l_count).param_value := x_val_attribute6;
2830 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2831
2832
2833 l_count:=l_count+1;
2834 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE7';
2835 l_trans_tbl(l_count).param_value := x_val_attribute7;
2836 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2837
2838
2839 l_count:=l_count+1;
2840 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE8';
2841 l_trans_tbl(l_count).param_value := x_val_attribute8;
2842 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2843
2844 l_count:=l_count+1;
2845 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE9';
2846 l_trans_tbl(l_count).param_value := x_val_attribute9;
2847 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2848
2849
2850 l_count:=l_count+1;
2851 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE10';
2852 l_trans_tbl(l_count).param_value := x_val_attribute10;
2853 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2854
2855
2856
2857 l_count:=l_count+1;
2858 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE11';
2859 l_trans_tbl(l_count).param_value := x_val_attribute11;
2860 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2861
2862
2863 l_count:=l_count+1;
2864 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE12';
2865 l_trans_tbl(l_count).param_value := x_val_attribute12;
2866 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2867
2868
2869 l_count:=l_count+1;
2870 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE13';
2871 l_trans_tbl(l_count).param_value := x_val_attribute13;
2872 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2873
2874
2875 l_count:=l_count+1;
2876 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE14';
2877 l_trans_tbl(l_count).param_value := x_val_attribute14;
2878 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2879
2880
2881 l_count:=l_count+1;
2882 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE15';
2883 l_trans_tbl(l_count).param_value := x_val_attribute15;
2884 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2885
2886
2887 l_count:=l_count+1;
2888 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE16';
2889 l_trans_tbl(l_count).param_value := x_val_attribute16;
2890 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2891
2892
2893 l_count:=l_count+1;
2894 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE17';
2895 l_trans_tbl(l_count).param_value := x_val_attribute17;
2896 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2897
2898
2899 l_count:=l_count+1;
2900 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE18';
2901 l_trans_tbl(l_count).param_value := x_val_attribute18;
2902 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2903
2904 l_count:=l_count+1;
2905 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE19';
2906 l_trans_tbl(l_count).param_value := x_val_attribute19;
2907 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2908
2909 l_count:=l_count+1;
2910 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE20';
2911 l_trans_tbl(l_count).param_value := x_val_attribute20;
2912 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2913
2914
2915 l_count:=l_count+1;
2916 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION_CATEGORY';
2917 l_trans_tbl(l_count).param_value := x_val_INFORMATION_category;
2918 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2919
2920
2921 l_count:=l_count+1;
2922 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION1';
2923 l_trans_tbl(l_count).param_value := x_val_INFORMATION1;
2924 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2925
2926
2927 l_count:=l_count+1;
2928 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION2';
2929 l_trans_tbl(l_count).param_value := x_val_INFORMATION2;
2930 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2931
2932
2933 l_count:=l_count+1;
2934 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION3';
2935 l_trans_tbl(l_count).param_value := x_val_INFORMATION3;
2936 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2937
2938
2939 l_count:=l_count+1;
2940 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION4';
2941 l_trans_tbl(l_count).param_value := x_val_INFORMATION4;
2942 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2943
2944
2945 l_count:=l_count+1;
2946 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION5';
2947 l_trans_tbl(l_count).param_value := x_val_INFORMATION5;
2948 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2949
2950
2951 l_count:=l_count+1;
2952 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION6';
2953 l_trans_tbl(l_count).param_value := x_val_INFORMATION6;
2954 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2955
2956
2957 l_count:=l_count+1;
2958 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION7';
2959 l_trans_tbl(l_count).param_value := x_val_INFORMATION7;
2960 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2961
2962
2963 l_count:=l_count+1;
2964 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION8';
2965 l_trans_tbl(l_count).param_value := x_val_INFORMATION8;
2966 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2967
2968 l_count:=l_count+1;
2969 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION9';
2970 l_trans_tbl(l_count).param_value := x_val_INFORMATION9;
2971 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2972
2973
2974 l_count:=l_count+1;
2975 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION10';
2976 l_trans_tbl(l_count).param_value := x_val_INFORMATION10;
2977 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2978
2979 l_count:=l_count+1;
2980 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION11';
2981 l_trans_tbl(l_count).param_value := x_val_INFORMATION11;
2982 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2983
2984
2985 l_count:=l_count+1;
2986 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION12';
2987 l_trans_tbl(l_count).param_value := x_val_INFORMATION12;
2988 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2989
2990
2991 l_count:=l_count+1;
2992 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION13';
2993 l_trans_tbl(l_count).param_value := x_val_INFORMATION13;
2994 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2995
2996
2997 l_count:=l_count+1;
2998 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION14';
2999 l_trans_tbl(l_count).param_value := x_val_INFORMATION14;
3000 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
3001
3002
3003 l_count:=l_count+1;
3004 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION15';
3005 l_trans_tbl(l_count).param_value := x_val_INFORMATION15;
3006 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
3007
3008
3009 l_count:=l_count+1;
3010 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION16';
3011 l_trans_tbl(l_count).param_value := x_val_INFORMATION16;
3012 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
3013
3014
3015 l_count:=l_count+1;
3016 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION17';
3017 l_trans_tbl(l_count).param_value := x_val_INFORMATION17;
3018 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
3019
3020 l_count:=l_count+1;
3021 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION18';
3022 l_trans_tbl(l_count).param_value := x_val_INFORMATION18;
3023 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
3024
3025 l_count:=l_count+1;
3026 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION19';
3027 l_trans_tbl(l_count).param_value := x_val_INFORMATION19;
3028 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
3029
3030 l_count:=l_count+1;
3031 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION20';
3032 l_trans_tbl(l_count).param_value := x_val_INFORMATION20;
3033 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
3034
3035 l_count:=l_count+1;
3036 l_trans_tbl(l_count).param_name := 'P_FUEL_BENEFIT';
3037 l_trans_tbl(l_count).param_value := x_fuel_benefit;
3038 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
3039
3040
3041 l_count:=l_count+1;
3042 l_trans_tbl(l_count).param_name := 'P_OBJECT_VERSION_NUMBER';
3043 l_trans_tbl(l_count).param_value := x_object_version_number;
3044 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
3045 --
3046 hr_utility.set_location('Entering: Second',5);
3047 l_count:=l_count+1;
3048 l_trans_tbl(l_count).param_name := 'P_REVIEW_PROC_CALL';
3049 l_trans_tbl(l_count).param_value := l_review_proc_call;
3050 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
3051 --
3052 l_count:=l_count+1;
3053 l_trans_tbl(l_count).param_name := 'P_REVIEW_ACTID';
3054 l_trans_tbl(l_count).param_value := x_activity_id;
3055 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
3056 --
3057
3058 l_count:=l_count+1;
3059 l_trans_tbl(l_count).param_name := 'P_VEHICLE_REPOSITORY_ID';
3060 l_trans_tbl(l_count).param_value := x_vehicle_repository_id;
3061 l_trans_tbl(l_count).param_data_type := 'NUMBER';
3062 hr_utility.set_location('Entering: Set p_vehicle_allocation_id',5);
3063 l_count:=l_count+1;
3064 l_trans_tbl(l_count).param_name := 'P_VEHICLE_ALLOCATION_ID';
3065 l_trans_tbl(l_count).param_value := x_vehicle_allocation_id;
3066 l_trans_tbl(l_count).param_data_type := 'NUMBER';
3067 hr_utility.set_location('Leaving:Set Paramaters' ,15);
3068
3069 u_count:=x_user_info.count;
3070 FOR i in 1..x_user_info.count
3071 LOOP
3072 hr_utility.set_location('Entering: userinfo',5);
3073 l_count:=l_count+1;
3074 hr_utility.set_location('Entering: enter loop',10);
3075 l_trans_tbl(l_count).param_name := 'P_PERSON_USR_ID'||i;
3076 l_trans_tbl(l_count).param_value := x_user_info(i).person_id;
3077 l_trans_tbl(l_count).param_data_type := 'NUMBER';
3078 l_count:=l_count+1;
3079 l_trans_tbl(l_count).param_name := 'P_ASSIGNMENT_USR_ID'||i;
3080 l_trans_tbl(l_count).param_value := x_user_info(i).assignment_id;
3081 l_trans_tbl(l_count).param_data_type := 'NUMBER';
3082 l_count:=l_count+1;
3083 l_trans_tbl(l_count).param_name := 'P_USER_TYPE'||i;
3084 l_trans_tbl(l_count).param_value := x_user_info(i).user_type;
3085 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
3086 hr_utility.set_location('Leaving: userinfo',10);
3087 END LOOP;
3088 ---Validate the incomming values against acual table.
3089 --The validation is done in false mode and then
3090 --rolled back using autonomous transaction.The resaon for going
3091 --in for Validate false mode is due to the fact that
3092 -- the when two apis are called where some of the condition in
3093 --first api could affect the validation of the second api. Validate True mode
3094 --would rollback once the transaction is completed and this would
3095 -- get the second api with the wrong info as the value is alredy rolled back
3096 --in the first Api.
3097 hr_utility.set_location('Entering val_create_vehicle_details:',15);
3098 IF x_status <> 'S' -- Added the condition to solve SFL issue in bug#12966484
3099 THEN
3100 val_create_vehicle_details
3101 (
3102 p_validate => false
3103 ,p_effective_date =>x_effective_date
3104 ,p_registration_number =>x_registration_number
3105 ,p_vehicle_type =>x_vehicle_type
3106 ,p_vehicle_id_number =>x_vehicle_id_number
3107 ,p_business_group_id =>x_business_group_id
3108 ,p_make =>x_make
3109 ,p_engine_capacity_in_cc =>x_engine_capacity_in_cc
3110 ,p_fuel_type =>x_fuel_type
3111 ,p_currency_code =>x_currency_code
3112 ,p_model =>x_model
3113 ,p_initial_registration =>x_initial_registration
3114 ,p_last_registration_renew_date =>x_last_registration_renew_date
3115 ,p_fiscal_ratings =>x_fiscal_ratings
3116 ,p_vehicle_ownership =>x_vehicle_ownership
3117 ,p_shared_vehicle =>x_shared_vehicle
3118 ,p_color =>x_color
3119 ,p_seating_capacity =>x_seating_capacity
3120 ,p_weight =>x_weight
3121 ,p_weight_uom =>x_weight_uom
3122 ,p_model_year =>x_model_year
3123 ,p_insurance_number =>x_insurance_number
3124 ,p_insurance_expiry_date =>x_insurance_expiry_date
3125 ,p_taxation_method =>x_taxation_method
3126 ,p_comments =>x_comments
3127 ,p_vre_attribute_category =>x_vre_attribute_category
3128 ,p_vre_attribute1 =>x_vre_attribute1
3129 ,p_vre_attribute2 =>x_vre_attribute2
3130 ,p_vre_attribute3 =>x_vre_attribute3
3131 ,p_vre_attribute4 =>x_vre_attribute4
3132 ,p_vre_attribute5 =>x_vre_attribute5
3133 ,p_vre_attribute6 =>x_vre_attribute6
3134 ,p_vre_attribute7 =>x_vre_attribute7
3135 ,p_vre_attribute8 =>x_vre_attribute8
3136 ,p_vre_attribute9 =>x_vre_attribute9
3137 ,p_vre_attribute10 =>x_vre_attribute10
3138 ,p_vre_attribute11 =>x_vre_attribute11
3139 ,p_vre_attribute12 =>x_vre_attribute12
3140 ,p_vre_attribute13 =>x_vre_attribute13
3141 ,p_vre_attribute14 =>x_vre_attribute14
3142 ,p_vre_attribute15 =>x_vre_attribute15
3143 ,p_vre_attribute16 =>x_vre_attribute16
3144 ,p_vre_attribute17 =>x_vre_attribute17
3145 ,p_vre_attribute18 =>x_vre_attribute18
3146 ,p_vre_attribute19 =>x_vre_attribute19
3147 ,p_vre_attribute20 =>x_vre_attribute20
3148 ,p_vre_information_category =>x_vre_information_category
3149 ,p_vre_information1 =>x_vre_information1
3150 ,p_vre_information2 =>x_vre_information2
3151 ,p_vre_information3 =>x_vre_information3
3152 ,p_vre_information4 =>x_vre_information4
3153 ,p_vre_information5 =>x_vre_information5
3154 ,p_vre_information6 =>x_vre_information6
3155 ,p_vre_information7 =>x_vre_information7
3156 ,p_vre_information8 =>x_vre_information8
3157 ,p_vre_information9 =>x_vre_information9
3158 ,p_vre_information10 =>x_vre_information10
3159 ,p_vre_information11 =>x_vre_information11
3160 ,p_vre_information12 =>x_vre_information12
3161 ,p_vre_information13 =>x_vre_information13
3162 ,p_vre_information14 =>x_vre_information14
3163 ,p_vre_information15 =>x_vre_information15
3164 ,p_vre_information16 =>x_vre_information16
3165 ,p_vre_information17 =>x_vre_information17
3166 ,p_vre_information18 =>x_vre_information18
3167 ,p_vre_information19 =>x_vre_information19
3168 ,p_vre_information20 =>x_vre_information20
3169 ,p_across_assignments =>x_across_assignments
3170 ,p_usage_type =>x_usage_type
3171 ,p_default_vehicle =>x_default_vehicle
3172 ,p_fuel_card =>x_fuel_card
3173 ,p_fuel_card_number =>x_fuel_card_number
3174 ,p_val_attribute_category =>x_val_attribute_category
3175 ,p_val_attribute1 =>x_val_attribute1
3176 ,p_val_attribute2 =>x_val_attribute2
3177 ,p_val_attribute3 =>x_val_attribute3
3178 ,p_val_attribute4 =>x_val_attribute4
3179 ,p_val_attribute5 =>x_val_attribute5
3180 ,p_val_attribute6 =>x_val_attribute6
3181 ,p_val_attribute7 =>x_val_attribute7
3182 ,p_val_attribute8 =>x_val_attribute8
3183 ,p_val_attribute9 =>x_val_attribute9
3184 ,p_val_attribute10 =>x_val_attribute10
3185 ,p_val_attribute11 =>x_val_attribute11
3186 ,p_val_attribute12 =>x_val_attribute12
3187 ,p_val_attribute13 =>x_val_attribute13
3188 ,p_val_attribute14 =>x_val_attribute14
3189 ,p_val_attribute15 =>x_val_attribute15
3190 ,p_val_attribute16 =>x_val_attribute16
3191 ,p_val_attribute17 =>x_val_attribute17
3192 ,p_val_attribute18 =>x_val_attribute18
3193 ,p_val_attribute19 =>x_val_attribute19
3194 ,p_val_attribute20 =>x_val_attribute20
3195 ,p_val_information_category =>x_val_information_category
3196 ,p_val_information1 =>x_val_information1
3197 ,p_val_information2 =>x_val_information2
3198 ,p_val_information3 =>x_val_information3
3199 ,p_val_information4 =>x_val_information4
3200 ,p_val_information5 =>x_val_information5
3201 ,p_val_information6 =>x_val_information6
3202 ,p_val_information7 =>x_val_information7
3203 ,p_val_information8 =>x_val_information8
3204 ,p_val_information9 =>x_val_information9
3205 ,p_val_information10 =>x_val_information10
3206 ,p_val_information11 =>x_val_information11
3207 ,p_val_information12 =>x_val_information12
3208 ,p_val_information13 =>x_val_information13
3209 ,p_val_information14 =>x_val_information14
3210 ,p_val_information15 =>x_val_information15
3211 ,p_val_information16 =>x_val_information16
3212 ,p_val_information17 =>x_val_information17
3213 ,p_val_information18 =>x_val_information18
3214 ,p_val_information19 =>x_val_information19
3215 ,p_val_information20 =>x_val_information20
3216 ,p_fuel_benefit =>x_fuel_benefit
3217 ,p_user_info =>x_user_info
3218 ,p_vehicle_repository_id =>x_vehicle_repository_id
3219 ,p_vehicle_allocation_id =>x_vehicle_allocation_id
3220 ,p_object_version_number =>x_object_version_number
3221 ,p_error_message =>l_error_message
3222 ,p_error_status =>l_error_status
3223 );
3224 END IF;
3225 hr_utility.set_location('Leaving val_create_vehicle_details:',20);
3226 exception
3227 when hr_multi_message.error_message_exist then
3228 --
3229 -- Catch the Multiple Message List exception which
3230 -- indicates API processing has been aborted because
3231 -- at least one message exists in the list.
3232 --
3233 rollback to savepoint pqp_vehicle_validate;
3234 --
3235 -- Reset IN OUT parameters and set OUT parameters
3236 x_error_status := hr_multi_message.get_return_status_disable;
3237
3238 hr_utility.set_location(' Leaving:' ,40);
3239 when others then
3240 --
3241 -- When Multiple Message Detection is enabled catch
3242 -- any Application specific or other unexpected
3243 -- exceptions. Adding appropriate details to the
3244 -- Multiple Message List. Otherwise re-raise the
3245 -- error.
3246 --
3247 rollback to savepoint pqp_vehicle_validate;
3248 if hr_multi_message.unexpected_error_add('l_proc') then
3249 --raise;
3250 x_error_status := hr_multi_message.get_return_status_disable;
3251 end if;
3252 -- Reset IN OUT parameters and set OUT parameters
3253
3254 x_error_status := hr_multi_message.get_return_status_disable;
3255 hr_utility.set_location(' Leaving:' || 'l_proc',50);
3256 --raise;
3257 hr_utility.set_location('Leaving validation :',30);
3258 END;
3259 -- If there are no error messages then insert values into transaction table
3260 SAVEPOINT pqp_create_transaction;
3261 IF x_transaction_id is NULl THEN
3262 hr_utility.set_location('Entering create_transaction :',10);
3263 hr_transaction_api.create_transaction(
3264 p_validate => false
3265 ,p_creator_person_id => x_login_person_id
3266 ,p_transaction_privilege => 'PRIVATE'
3267 ,p_product_code => 'PQP'
3268 ,p_url => NULL
3269 ,p_status => x_status
3270 ,p_section_display_name =>NULL
3271 ,p_function_id =>NULL
3272 ,p_transaction_ref_table =>NULL
3273 ,p_transaction_ref_id =>NULL
3274 ,p_transaction_type =>NULL
3275 ,p_assignment_id =>x_assignment_id
3276 ,p_selected_person_id =>x_person_id
3277 ,p_item_type =>x_item_type
3278 ,p_item_key =>x_item_key
3279 ,p_transaction_effective_date =>x_effective_date
3280 ,p_process_name =>NULL
3281 ,p_plan_id =>NULL
3282 ,p_rptg_grp_id =>NULL
3283 ,p_effective_date_option =>x_effective_date_option
3284 ,p_transaction_id =>l_transaction_id
3285 );
3286
3287 hr_utility.set_location('Leaving create_transaction :',15);
3288 wf_engine.setitemattrnumber
3289 (itemtype => x_item_type
3290 ,itemkey => x_item_key
3291 ,aname => 'TRANSACTION_ID'
3292 ,avalue => l_transaction_id
3293 );
3294
3295 x_transaction_id := l_transaction_id;
3296 hr_utility.set_location('Leaving setitemattrnumber :',20);
3297 --Create transaction steps
3298 hr_transaction_api.create_transaction_step
3299 (p_validate =>false
3300 ,p_creator_person_id =>x_login_person_id
3301 ,p_transaction_id =>l_transaction_id
3302 ,p_api_name =>l_api_name
3303 ,p_api_display_name =>l_api_name
3304 ,p_item_type =>x_item_type
3305 ,p_item_key =>x_item_key
3306 ,p_activity_id =>x_activity_id
3307 ,p_transaction_step_id =>l_transaction_step_id
3308 ,p_object_version_number =>l_ovn
3309 );
3310 hr_utility.set_location('Leaving create_transaction_step :',25);
3311 ELSE
3312 hr_utility.set_location('Entering update transaction :',10);
3313 hr_transaction_api.update_transaction
3314 (p_transaction_id => x_transaction_id
3315 ,p_status => x_status
3316 );
3317 hr_utility.set_location('leaving update transaction :',15);
3318 OPEN c_del_values;
3319 FETCH c_del_values INTO l_del_values;
3320 CLOSE c_del_values;
3321
3322 hr_utility.set_location('leaving c del value :',20);
3323 DELETE from hr_api_transaction_values
3324 WHERE transaction_step_id = l_del_values.transaction_step_id;
3325 l_transaction_step_id := l_del_values.transaction_step_id;
3326 hr_utility.set_location('leaving delete :',25);
3327 END IF;
3328 FOR i in 1..l_trans_tbl.count
3329 LOOP
3330 IF l_trans_tbl(i).param_data_type ='VARCHAR2' THEN
3331 hr_utility.set_location('Enter varchar param||l_trans_tbl (i).param_name :',10);
3332 hr_utility.set_location('Enter varchar param value||l_trans_tbl (i).param_value :',10);
3333 hr_transaction_api.set_varchar2_value
3334 (p_transaction_step_id =>l_transaction_step_id
3335 ,p_person_id =>x_person_id
3336 ,p_name =>l_trans_tbl (i).param_name
3337 ,p_value =>l_trans_tbl (i).param_value
3338 );
3339 hr_utility.set_location('Leaving param data type :',20);
3340
3341 ELSIF l_trans_tbl(i).param_data_type ='DATE' THEN
3342 hr_utility.set_location('Enter date Param||l_trans_tbl (i).param_name :',10);
3343 hr_utility.set_location('Enter date Param Value||
3344 l_trans_tbl (i).param_value :',10);
3345 hr_transaction_api.set_date_value
3346 (
3347 p_transaction_step_id => l_transaction_step_id
3348 ,p_person_id => x_person_id
3349 ,p_name => l_trans_tbl (i).param_name
3350 ,p_value => fnd_date.displaydate_to_date(l_trans_tbl (i)
3351 .param_value ) );
3352 hr_utility.set_location('Leaving param date :',20);
3353 ELSIF l_trans_tbl(i).param_data_type ='NUMBER' THEN
3354 hr_utility.set_location('Enter number param||l_trans_tbl (i).param_name :',10);
3355 hr_utility.set_location('Enter number param value||l_trans_tbl (i).param_value :',10);
3356 hr_transaction_api.set_number_value
3357 (
3358 p_transaction_step_id => l_transaction_step_id
3359 ,p_person_id => x_person_id
3360 ,p_name =>l_trans_tbl (i).param_name
3361 ,p_value =>TO_NUMBER(l_trans_tbl (i).param_value ));
3362 hr_utility.set_location('Leaving param number :',20);
3363 END IF;
3364 END LOOP;
3365
3366
3367 ---Set Global value to open extra info page
3368 set_extra_info_val (
3369 itemtype =>x_item_type,
3370 itemkey =>x_item_key,
3371 result =>l_sec_result );
3372 EXCEPTION
3373 WHEN hr_multi_message.error_message_exist THEN
3374 --
3375 -- Catch the Multiple Message List exception which
3376 -- indicates API processing has been aborted because
3377 -- at least one message exists in the list.
3378 --
3379 ROLLBACK TO SAVEPOINT pqp_vehicle_proc_start;
3380 --
3381 -- Reset IN OUT parameters and set OUT parameters
3382 x_error_status := hr_multi_message.get_return_status_disable;
3383
3384 hr_utility.set_location(' Leaving:' ,40);
3385 WHEN others then
3386 --
3387 -- When Multiple Message Detection is enabled catch
3388 -- any Application specific or other unexpected
3389 -- exceptions. Adding appropriate details to the
3390 -- Multiple Message List. Otherwise re-raise the
3391 -- error.
3392 --
3393 ROLLBACK TO SAVEPOINT pqp_vehicle_proc_start;
3394 IF hr_multi_message.unexpected_error_add('l_proc') then
3395 --raise;
3396 x_error_status := hr_multi_message.get_return_status_disable;
3397 END IF;
3398 -- Reset IN OUT parameters and set OUT parameters
3399
3400 x_error_status := hr_multi_message.get_return_status_disable;
3401 hr_utility.set_location(' Leaving:' || 'l_proc',50);
3402 --raise;
3403
3404
3405
3406
3407
3408 END;
3409 --
3410 --not used at the moment
3411
3412 PROCEDURE delete_process_api (
3413 p_validate IN BOOLEAN DEFAULT FALSE,
3414 p_transaction_step_id IN NUMBER,
3415 p_effective_date IN VARCHAR2 DEFAULT NULL ) IS
3416 --
3417 --
3418 l_ovn NUMBER :=1;
3419 l_error_status VARCHAR2(10);
3420 l_effective_start_date DATE;
3421 l_effective_end_date DATE;
3422 l_person_id per_all_people_f.person_id%TYPE;
3423 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
3424 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
3425 l_allocation_id pqp_vehicle_allocations_f.vehicle_allocation_id%TYPE;
3426 l_effective_date DATE;
3427 BEGIN
3428 l_person_id := hr_transaction_api.get_number_value (
3429 p_transaction_step_id => p_transaction_step_id,
3430 p_name =>'P_PERSON_ID' );
3431
3432 l_effective_date := hr_transaction_api.get_date_value (
3433 p_transaction_step_id => p_transaction_step_id,
3434 p_name =>'P_EFFECTIVE_DATE' );
3435 l_assignment_id :=hr_transaction_api.get_number_value (
3436 p_transaction_step_id => p_transaction_step_id,
3437 p_name =>'P_ASSIGNMENT_ID' );
3438
3439 l_business_group_id :=hr_transaction_api.get_number_value (
3440 p_transaction_step_id => p_transaction_step_id,
3441 p_name =>'P_BUSINESS_GROUP_ID' );
3442 l_allocation_id :=hr_transaction_api.get_number_value (
3443 p_transaction_step_id => p_transaction_step_id,
3444 p_name =>'P_ALLOCATION_ID' );
3445
3446
3447
3448
3449 delete_process (
3450 p_validate => false
3451 ,p_effective_date => l_effective_date
3452 ,p_person_id => l_person_id
3453 ,p_assignment_id => l_assignment_id
3454 ,p_business_group_id => l_business_group_id
3455 ,p_vehicle_allocation_id => l_allocation_id
3456 ,p_error_status => l_error_status
3457 );
3458
3459 -- EXCEPTION
3460 -- WHEN hr_utility.hr_error THEN
3461 --hr_utility.raise_error;
3462 -- WHEN OTHERS THEN
3463 -- RAISE; -- Raise error here relevant to the new tech stack.
3464
3465 END;
3466
3467 --This process is called when the transaction is approved and
3468 --the information is created in the base tables.
3469 PROCEDURE process_api (
3470 p_validate IN BOOLEAN DEFAULT FALSE,
3471 p_transaction_step_id IN NUMBER,
3472 p_effective_date IN VARCHAR2 DEFAULT NULL ) IS
3473 --
3474 --
3475 l_person_id NUMBER;
3476 l_ovn NUMBER :=1;
3477 l_assignment_id NUMBER;
3478 l_effective_date DATE;
3479 l_registration_number pqp_vehicle_repository_f.registration_number%TYPE;
3480 l_vehicle_type pqp_vehicle_repository_f.vehicle_type%TYPE;
3481 l_vehicle_id_number pqp_vehicle_repository_f.vehicle_id_number%TYPE;
3482 l_business_group_id pqp_vehicle_repository_f.business_group_id%TYPE;
3483 l_make pqp_vehicle_repository_f.make%TYPE;
3484 l_engine_capacity_in_cc pqp_vehicle_repository_f.engine_capacity_in_cc%TYPE;
3485 l_fuel_type pqp_vehicle_repository_f.fuel_type%TYPE;
3486 l_currency_code pqp_vehicle_repository_f.currency_code%TYPE;
3487 l_model pqp_vehicle_repository_f.model%TYPE;
3488 l_initial_registration pqp_vehicle_repository_f.initial_registration%TYPE;
3489 l_last_registration_renew_date pqp_vehicle_repository_f.last_registration_renew_date%TYPE;
3490 l_fiscal_ratings pqp_vehicle_repository_f.fiscal_ratings%TYPE;
3491 l_vehicle_ownership pqp_vehicle_repository_f.vehicle_ownership%TYPE;
3492 l_shared_vehicle pqp_vehicle_repository_f.shared_vehicle%TYPE;
3493 l_color pqp_vehicle_repository_f.color%TYPE;
3494 l_seating_capacity pqp_vehicle_repository_f.seating_capacity%TYPE;
3495 l_weight pqp_vehicle_repository_f.weight%TYPE;
3496 l_weight_uom pqp_vehicle_repository_f.weight_uom%TYPE;
3497 l_model_year pqp_vehicle_repository_f.model_year%TYPE;
3498 l_insurance_number pqp_vehicle_repository_f.insurance_number%TYPE;
3499 l_insurance_expiry_date pqp_vehicle_repository_f.insurance_expiry_date%TYPE;
3500 l_taxation_method pqp_vehicle_repository_f.taxation_method%TYPE;
3501 l_comments pqp_vehicle_repository_f.comments%TYPE;
3502 l_vre_attribute_category pqp_vehicle_repository_f.vre_attribute_category%TYPE;
3503 l_vre_attribute1 pqp_vehicle_repository_f.vre_attribute1%TYPE;
3504 l_vre_attribute2 pqp_vehicle_repository_f.vre_attribute2%TYPE;
3505 l_vre_attribute3 pqp_vehicle_repository_f.vre_attribute3%TYPE;
3506 l_vre_attribute4 pqp_vehicle_repository_f.vre_attribute4%TYPE;
3507 l_vre_attribute5 pqp_vehicle_repository_f.vre_attribute5%TYPE;
3508 l_vre_attribute6 pqp_vehicle_repository_f.vre_attribute6%TYPE;
3509 l_vre_attribute7 pqp_vehicle_repository_f.vre_attribute7%TYPE;
3510 l_vre_attribute8 pqp_vehicle_repository_f.vre_attribute8%TYPE;
3511 l_vre_attribute9 pqp_vehicle_repository_f.vre_attribute9%TYPE;
3512 l_vre_attribute10 pqp_vehicle_repository_f.vre_attribute10%TYPE;
3513 l_vre_attribute11 pqp_vehicle_repository_f.vre_attribute11%TYPE;
3514 l_vre_attribute12 pqp_vehicle_repository_f.vre_attribute12%TYPE;
3515 l_vre_attribute13 pqp_vehicle_repository_f.vre_attribute13%TYPE;
3516 l_vre_attribute14 pqp_vehicle_repository_f.vre_attribute14%TYPE;
3517 l_vre_attribute15 pqp_vehicle_repository_f.vre_attribute15%TYPE;
3518 l_vre_attribute16 pqp_vehicle_repository_f.vre_attribute16%TYPE;
3519 l_vre_attribute17 pqp_vehicle_repository_f.vre_attribute17%TYPE;
3520 l_vre_attribute18 pqp_vehicle_repository_f.vre_attribute18%TYPE;
3521 l_vre_attribute19 pqp_vehicle_repository_f.vre_attribute19%TYPE;
3522 l_vre_attribute20 pqp_vehicle_repository_f.vre_attribute20%TYPE;
3523 l_vre_information_category pqp_vehicle_repository_f.vre_information_category%TYPE;
3524 l_vre_information1 pqp_vehicle_repository_f.vre_information1%TYPE;
3525 l_vre_information2 pqp_vehicle_repository_f.vre_information2%TYPE;
3526 l_vre_information3 pqp_vehicle_repository_f.vre_information3%TYPE;
3527 l_vre_information4 pqp_vehicle_repository_f.vre_information4%TYPE;
3528 l_vre_information5 pqp_vehicle_repository_f.vre_information5%TYPE;
3529 l_vre_information6 pqp_vehicle_repository_f.vre_information6%TYPE;
3530 l_vre_information7 pqp_vehicle_repository_f.vre_information7%TYPE;
3531 l_vre_information8 pqp_vehicle_repository_f.vre_information8%TYPE;
3532 l_vre_information9 pqp_vehicle_repository_f.vre_information9%TYPE;
3533 l_vre_information10 pqp_vehicle_repository_f.vre_information10%TYPE;
3534 l_vre_information11 pqp_vehicle_repository_f.vre_information11%TYPE;
3535 l_vre_information12 pqp_vehicle_repository_f.vre_information12%TYPE;
3536 l_vre_information13 pqp_vehicle_repository_f.vre_information13%TYPE;
3537 l_vre_information14 pqp_vehicle_repository_f.vre_information14%TYPE;
3538 l_vre_information15 pqp_vehicle_repository_f.vre_information15%TYPE;
3539 l_vre_information16 pqp_vehicle_repository_f.vre_information16%TYPE;
3540 l_vre_information17 pqp_vehicle_repository_f.vre_information17%TYPE;
3541 l_vre_information18 pqp_vehicle_repository_f.vre_information18%TYPE;
3542 l_vre_information19 pqp_vehicle_repository_f.vre_information19%TYPE;
3543 l_vre_information20 pqp_vehicle_repository_f.vre_information20%TYPE;
3544 l_across_assignments pqp_vehicle_allocations_f.across_assignments%TYPE;
3545 l_usage_type pqp_vehicle_allocations_f.usage_type%TYPE;
3546 l_default_vehicle pqp_vehicle_allocations_f.default_vehicle%TYPE;
3547 l_fuel_card pqp_vehicle_allocations_f.fuel_card%TYPE;
3548 l_fuel_card_number pqp_vehicle_allocations_f.fuel_card_number%TYPE;
3549 l_val_attribute_category pqp_vehicle_allocations_f.val_attribute_category%TYPE;
3550 l_val_attribute1 pqp_vehicle_allocations_f.val_attribute1%TYPE;
3551 l_val_attribute2 pqp_vehicle_allocations_f.val_attribute2%TYPE;
3552 l_val_attribute3 pqp_vehicle_allocations_f.val_attribute3%TYPE;
3553 l_val_attribute4 pqp_vehicle_allocations_f.val_attribute4%TYPE;
3554 l_val_attribute5 pqp_vehicle_allocations_f.val_attribute5%TYPE;
3555 l_val_attribute6 pqp_vehicle_allocations_f.val_attribute6%TYPE;
3556 l_val_attribute7 pqp_vehicle_allocations_f.val_attribute7%TYPE;
3557 l_val_attribute8 pqp_vehicle_allocations_f.val_attribute8%TYPE;
3558 l_val_attribute9 pqp_vehicle_allocations_f.val_attribute9%TYPE;
3559 l_val_attribute10 pqp_vehicle_allocations_f.val_attribute10%TYPE;
3560 l_val_attribute11 pqp_vehicle_allocations_f.val_attribute11%TYPE;
3561 l_val_attribute12 pqp_vehicle_allocations_f.val_attribute12%TYPE;
3562 l_val_attribute13 pqp_vehicle_allocations_f.val_attribute13%TYPE;
3563 l_val_attribute14 pqp_vehicle_allocations_f.val_attribute14%TYPE;
3564 l_val_attribute15 pqp_vehicle_allocations_f.val_attribute15%TYPE;
3565 l_val_attribute16 pqp_vehicle_allocations_f.val_attribute16%TYPE;
3566 l_val_attribute17 pqp_vehicle_allocations_f.val_attribute17%TYPE;
3567 l_val_attribute18 pqp_vehicle_allocations_f.val_attribute18%TYPE;
3568 l_val_attribute19 pqp_vehicle_allocations_f.val_attribute19%TYPE;
3569 l_val_attribute20 pqp_vehicle_allocations_f.val_attribute20%TYPE;
3570 l_val_information_category pqp_vehicle_allocations_f.val_information_category%TYPE;
3571 l_val_information1 pqp_vehicle_allocations_f.val_information1%TYPE;
3572 l_val_information2 pqp_vehicle_allocations_f.val_information2%TYPE;
3573 l_val_information3 pqp_vehicle_allocations_f.val_information3%TYPE;
3574 l_val_information4 pqp_vehicle_allocations_f.val_information4%TYPE;
3575 l_val_information5 pqp_vehicle_allocations_f.val_information5%TYPE;
3576 l_val_information6 pqp_vehicle_allocations_f.val_information6%TYPE;
3577 l_val_information7 pqp_vehicle_allocations_f.val_information7%TYPE;
3578 l_val_information8 pqp_vehicle_allocations_f.val_information8%TYPE;
3579 l_val_information9 pqp_vehicle_allocations_f.val_information9%TYPE;
3580 l_val_information10 pqp_vehicle_allocations_f.val_information10%TYPE;
3581 l_val_information11 pqp_vehicle_allocations_f.val_information11%TYPE;
3582 l_val_information12 pqp_vehicle_allocations_f.val_information12%TYPE;
3583 l_val_information13 pqp_vehicle_allocations_f.val_information13%TYPE;
3584 l_val_information14 pqp_vehicle_allocations_f.val_information14%TYPE;
3585 l_val_information15 pqp_vehicle_allocations_f.val_information15%TYPE;
3586 l_val_information16 pqp_vehicle_allocations_f.val_information16%TYPE;
3587 l_val_information17 pqp_vehicle_allocations_f.val_information17%TYPE;
3588 l_val_information18 pqp_vehicle_allocations_f.val_information18%TYPE;
3589 l_val_information19 pqp_vehicle_allocations_f.val_information19%TYPE;
3590 l_val_information20 pqp_vehicle_allocations_f.val_information20%TYPE;
3591 l_fuel_benefit pqp_vehicle_allocations_f.fuel_benefit%TYPE;
3592 l_object_version_number NUMBER;
3593 l_error_message VARCHAr2(80);
3594 l_error_status VARCHAr2(30);
3595 l_vehicle_allocation_id NUMBER;
3596 l_vehicle_repository_id NUMBER;
3597 l_user_info t_user_info;
3598 l_get_count NUMBER;
3599 l_transaction_id NUMBER;
3600 CURSOR c_get_count
3601 IS
3602 SELECT count(hatv.name)
3603 FROM hr_api_transaction_values hatv
3604 WHERE hatv.transaction_step_id=p_transaction_step_id
3605 AND hatv.name like 'P_PERSON_USR_ID%';
3606 --
3607 --
3608 --
3609 --
3610 p_user_info t_user_info;
3611 CURSOR c_get_other_tstep
3612 IS
3613 SELECT hats.item_type,hats.item_key
3614 FROM hr_api_transaction_steps hats
3615 WHERE hats.transaction_id=l_transaction_id
3616 AND hats.transaction_step_id =p_transaction_step_id;
3617
3618 l_get_other_tstep c_get_other_tstep%ROWTYPE;
3619 CURSOR c_get_details IS
3620 SELECT pvr.vehicle_repository_id
3621 ,pva.vehicle_allocation_id
3622 FROM pqp_vehicle_repository_f pvr
3623 ,pqp_vehicle_allocations_f pva
3624 WHERE pvr.vehicle_repository_id =pva.vehicle_repository_id
3625 AND pva.assignment_id =l_assignment_id
3626 AND NVL(l_effective_date,SYSDATE) BETWEEN pvr.effective_start_date
3627 AND pvr.effective_end_date
3628 AND NVL(l_effective_date,sysdate) BETWEEN pva.effective_start_date
3629 AND pva.effective_end_date
3630 AND pvr.registration_number=l_registration_number;
3631 l_get_details c_get_details%ROWTYPE;
3632 BEGIN
3633 -- hr_utility.trace_on(NULL,'gattu');
3634 hr_utility.set_location('Entering:process_api',5);
3635 hr_utility.set_location(' p_transaction_step_id'|| p_transaction_step_id,5);
3636
3637 --
3638 savepoint process_veh_details;
3639 --
3640 l_transaction_id :=get_transaction_id(
3641 p_transaction_step_id =>p_transaction_step_id);
3642
3643
3644 l_person_id := hr_transaction_api.get_number_value (
3645 p_transaction_step_id => p_transaction_step_id,
3646 p_name =>'P_PERSON_ID' );
3647
3648
3649 l_effective_date := hr_transaction_api.get_date_value (
3650 p_transaction_step_id => p_transaction_step_id,
3651 p_name =>'P_EFFECTIVE_DATE' );
3652
3653 l_registration_number := hr_transaction_api.get_varchar2_value (
3654 p_transaction_step_id => p_transaction_step_id,
3655 p_name =>'P_REGISTRATION_NUMBER' );
3656
3657 l_vehicle_type := hr_transaction_api.get_varchar2_value (
3658 p_transaction_step_id => p_transaction_step_id,
3659 p_name =>'P_VEHICLE_TYPE' );
3660
3661 l_vehicle_id_number := hr_transaction_api.get_varchar2_value (
3662 p_transaction_step_id => p_transaction_step_id,
3663 p_name =>'P_VEHICLE_ID_NUMBER' );
3664 l_business_group_id := hr_transaction_api.get_number_value (
3665 p_transaction_step_id => p_transaction_step_id,
3666 p_name =>'P_BUSINESS_GROUP_ID');
3667
3668 l_make := hr_transaction_api.get_varchar2_value (
3669 p_transaction_step_id => p_transaction_step_id,
3670 p_name =>'P_MAKE' );
3671
3672 l_engine_capacity_in_cc := hr_transaction_api.get_number_value (
3673 p_transaction_step_id => p_transaction_step_id,
3674 p_name =>'P_ENGINE_CAPACITY_IN_CC');
3675
3676 l_fuel_type := hr_transaction_api.get_varchar2_value (
3677 p_transaction_step_id => p_transaction_step_id,
3678 p_name =>'P_FUEL_TYPE');
3679
3680 l_currency_code := hr_transaction_api.get_varchar2_value (
3681 p_transaction_step_id => p_transaction_step_id,
3682 p_name =>'P_CURRENCY_CODE');
3683
3684 l_model := hr_transaction_api.get_varchar2_value (
3685 p_transaction_step_id => p_transaction_step_id,
3686 p_name =>'P_MODEL' );
3687
3688 l_initial_registration := hr_transaction_api.get_date_value (
3689 p_transaction_step_id => p_transaction_step_id,
3690 p_name =>'P_INITIAL_REGISTRATION');
3691
3692 l_last_registration_renew_date := hr_transaction_api.get_date_value (
3693 p_transaction_step_id => p_transaction_step_id,
3694 p_name =>'P_LAST_REGISTRATION_RENEW_DATE');
3695
3696 l_fiscal_ratings := hr_transaction_api.get_number_value (
3697 p_transaction_step_id => p_transaction_step_id,
3698 p_name =>'P_FISCAL_RATINGS' );
3699
3700 l_vehicle_ownership := hr_transaction_api.get_varchar2_value (
3701 p_transaction_step_id => p_transaction_step_id,
3702 p_name =>'P_VEHICLE_OWNERSHIP');
3703
3704 l_shared_vehicle := hr_transaction_api.get_varchar2_value (
3705 p_transaction_step_id => p_transaction_step_id,
3706 p_name =>'P_SHARED_VEHICLE' );
3707
3708 l_color := hr_transaction_api.get_varchar2_value (
3709 p_transaction_step_id => p_transaction_step_id,
3710 p_name =>'P_COLOR' );
3711
3712 l_seating_capacity := hr_transaction_api.get_number_value (
3713 p_transaction_step_id => p_transaction_step_id,
3714 p_name =>'P_SEATING_CAPACITY' );
3715
3716 l_weight := hr_transaction_api.get_number_value (
3717 p_transaction_step_id => p_transaction_step_id,
3718 p_name =>'P_WEIGHT' );
3719
3720 l_weight_uom := hr_transaction_api.get_varchar2_value (
3721 p_transaction_step_id => p_transaction_step_id,
3722 p_name =>'P_WEIGHT_UOM' );
3723
3724 l_model_year := hr_transaction_api.get_number_value (
3725 p_transaction_step_id => p_transaction_step_id,
3726 p_name =>'P_MODEL_YEAR' );
3727
3728 l_insurance_number := hr_transaction_api.get_varchar2_value (
3729 p_transaction_step_id => p_transaction_step_id,
3730 p_name =>'P_INSURANCE_NUMBER' );
3731
3732 l_insurance_expiry_date := hr_transaction_api.get_date_value (
3733 p_transaction_step_id => p_transaction_step_id,
3734 p_name =>'P_INSURANCE_EXPIRY_DATE' );
3735
3736 l_taxation_method := hr_transaction_api.get_varchar2_value (
3737 p_transaction_step_id => p_transaction_step_id,
3738 p_name =>'P_TAXATION_METHOD');
3739
3740 l_comments := hr_transaction_api.get_varchar2_value (
3741 p_transaction_step_id => p_transaction_step_id,
3742 p_name =>'P_COMMENTS' );
3743
3744 l_vre_attribute_category := hr_transaction_api.get_varchar2_value (
3745 p_transaction_step_id => p_transaction_step_id,
3746 p_name =>'P_VRE_ATTRIBUTE_CATEGORY' );
3747
3748 l_vre_attribute1 := hr_transaction_api.get_varchar2_value (
3749 p_transaction_step_id => p_transaction_step_id,
3750 p_name =>'P_VRE_ATTRIBUTE1' );
3751
3752 l_vre_attribute2 := hr_transaction_api.get_varchar2_value (
3753 p_transaction_step_id => p_transaction_step_id,
3754 p_name =>'P_VRE_ATTRIBUTE2' );
3755
3756 l_vre_attribute3 := hr_transaction_api.get_varchar2_value (
3757 p_transaction_step_id => p_transaction_step_id,
3758 p_name =>'P_VRE_ATTRIBUTE3' );
3759
3760 l_vre_attribute4 := hr_transaction_api.get_varchar2_value (
3761 p_transaction_step_id => p_transaction_step_id,
3762 p_name =>'P_VRE_ATTRIBUTE4' );
3763
3764
3765 l_vre_attribute5 := hr_transaction_api.get_varchar2_value (
3766 p_transaction_step_id => p_transaction_step_id,
3767 p_name =>'P_VRE_ATTRIBUTE5' );
3768
3769 l_vre_attribute6 := hr_transaction_api.get_varchar2_value (
3770 p_transaction_step_id => p_transaction_step_id,
3771 p_name =>'P_VRE_ATTRIBUTE6' );
3772
3773 l_vre_attribute7 := hr_transaction_api.get_varchar2_value (
3774 p_transaction_step_id => p_transaction_step_id,
3775 p_name =>'P_VRE_ATTRIBUTE7' );
3776
3777 l_vre_attribute8 := hr_transaction_api.get_varchar2_value (
3778 p_transaction_step_id => p_transaction_step_id,
3779 p_name =>'P_VRE_ATTRIBUTE8' );
3780
3781 l_vre_attribute9 := hr_transaction_api.get_varchar2_value (
3782 p_transaction_step_id => p_transaction_step_id,
3783 p_name =>'P_VRE_ATTRIBUTE9' );
3784
3785 l_vre_attribute10 := hr_transaction_api.get_varchar2_value (
3786 p_transaction_step_id => p_transaction_step_id,
3787 p_name =>'P_VRE_ATTRIBUTE10' );
3788
3789 l_vre_attribute11 := hr_transaction_api.get_varchar2_value (
3790 p_transaction_step_id => p_transaction_step_id,
3791 p_name =>'P_VRE_ATTRIBUTE11' );
3792
3793 l_vre_attribute12 := hr_transaction_api.get_varchar2_value (
3794 p_transaction_step_id => p_transaction_step_id,
3795 p_name =>'P_VRE_ATTRIBUTE12' );
3796
3797 l_vre_attribute13 := hr_transaction_api.get_varchar2_value (
3798 p_transaction_step_id => p_transaction_step_id,
3799 p_name =>'P_VRE_ATTRIBUTE13' );
3800
3801 l_vre_attribute14 := hr_transaction_api.get_varchar2_value (
3802 p_transaction_step_id => p_transaction_step_id,
3803 p_name =>'P_VRE_ATTRIBUTE14' );
3804
3805 l_vre_attribute15 := hr_transaction_api.get_varchar2_value (
3806 p_transaction_step_id => p_transaction_step_id,
3807 p_name =>'P_VRE_ATTRIBUTE15' );
3808
3809 l_vre_attribute16 := hr_transaction_api.get_varchar2_value (
3810 p_transaction_step_id => p_transaction_step_id,
3811 p_name =>'P_VRE_ATTRIBUTE16' );
3812
3813 l_vre_attribute17 := hr_transaction_api.get_varchar2_value (
3814 p_transaction_step_id => p_transaction_step_id,
3815 p_name =>'P_VRE_ATTRIBUTE17' );
3816
3817 l_vre_attribute18 := hr_transaction_api.get_varchar2_value (
3818 p_transaction_step_id => p_transaction_step_id,
3819 p_name =>'P_VRE_ATTRIBUTE18' );
3820
3821 l_vre_attribute19 := hr_transaction_api.get_varchar2_value (
3822 p_transaction_step_id => p_transaction_step_id,
3823 p_name =>'P_VRE_ATTRIBUTE19' );
3824
3825 l_vre_attribute20 := hr_transaction_api.get_varchar2_value (
3826 p_transaction_step_id => p_transaction_step_id,
3827 p_name =>'P_VRE_ATTRIBUTE20' );
3828
3829 l_vre_information_category := hr_transaction_api.get_varchar2_value (
3830 p_transaction_step_id => p_transaction_step_id,
3831 p_name =>'P_VRE_INFORMATION_CATEGORY' );
3832
3833 l_vre_information1 := hr_transaction_api.get_varchar2_value (
3834 p_transaction_step_id => p_transaction_step_id,
3835 p_name =>'P_VRE_INFORMATION1' );
3836
3837 l_vre_information2 := hr_transaction_api.get_varchar2_value (
3838 p_transaction_step_id => p_transaction_step_id,
3839 p_name =>'P_VRE_INFORMATION2' );
3840
3841 l_vre_information3 := hr_transaction_api.get_varchar2_value (
3842 p_transaction_step_id => p_transaction_step_id,
3843 p_name =>'P_VRE_INFORMATION3' );
3844
3845 l_vre_information4 := hr_transaction_api.get_varchar2_value (
3846 p_transaction_step_id => p_transaction_step_id,
3847 p_name =>'P_VRE_INFORMATION4' );
3848
3849 l_vre_information5 := hr_transaction_api.get_varchar2_value (
3850 p_transaction_step_id => p_transaction_step_id,
3851 p_name =>'P_VRE_INFORMATION5' );
3852
3853 l_vre_information6 := hr_transaction_api.get_varchar2_value (
3854 p_transaction_step_id => p_transaction_step_id,
3855 p_name =>'P_VRE_INFORMATION6' );
3856
3857 l_vre_information7 := hr_transaction_api.get_varchar2_value (
3858 p_transaction_step_id => p_transaction_step_id,
3859 p_name =>'P_VRE_INFORMATION7' );
3860
3861 l_vre_information8 := hr_transaction_api.get_varchar2_value (
3862 p_transaction_step_id => p_transaction_step_id,
3863 p_name =>'P_VRE_INFORMATION8' );
3864
3865 l_vre_information9 := hr_transaction_api.get_varchar2_value (
3866 p_transaction_step_id => p_transaction_step_id,
3867 p_name =>'P_VRE_INFORMATION9' );
3868
3869 l_vre_information10 := hr_transaction_api.get_varchar2_value (
3870 p_transaction_step_id => p_transaction_step_id,
3871 p_name =>'P_VRE_INFORMATION10' );
3872
3873 l_vre_information11 := hr_transaction_api.get_varchar2_value (
3874 p_transaction_step_id => p_transaction_step_id,
3875 p_name =>'P_VRE_INFORMATION11' );
3876
3877 l_vre_information12 := hr_transaction_api.get_varchar2_value (
3878 p_transaction_step_id => p_transaction_step_id,
3879 p_name =>'P_VRE_INFORMATION12' );
3880
3881 l_vre_information13 := hr_transaction_api.get_varchar2_value (
3882 p_transaction_step_id => p_transaction_step_id,
3883 p_name =>'P_VRE_INFORMATION13' );
3884
3885 l_vre_information14 := hr_transaction_api.get_varchar2_value (
3886 p_transaction_step_id => p_transaction_step_id,
3887 p_name =>'P_VRE_INFORMATION14' );
3888
3889 l_vre_information15 := hr_transaction_api.get_varchar2_value (
3890 p_transaction_step_id => p_transaction_step_id,
3891 p_name =>'P_VRE_INFORMATION15' );
3892
3893 l_vre_information16 := hr_transaction_api.get_varchar2_value (
3894 p_transaction_step_id => p_transaction_step_id,
3895 p_name =>'P_VRE_INFORMATION16' );
3896
3897 l_vre_information17 := hr_transaction_api.get_varchar2_value (
3898 p_transaction_step_id => p_transaction_step_id,
3899 p_name =>'P_VRE_INFORMATION17' );
3900
3901 l_vre_information18 := hr_transaction_api.get_varchar2_value (
3902 p_transaction_step_id => p_transaction_step_id,
3903 p_name =>'P_VRE_INFORMATION18' );
3904
3905
3906 l_vre_information19 := hr_transaction_api.get_varchar2_value (
3907 p_transaction_step_id => p_transaction_step_id,
3908 p_name =>'P_VRE_INFORMATION19' );
3909
3910
3911 l_vre_information20 := hr_transaction_api.get_varchar2_value (
3912 p_transaction_step_id => p_transaction_step_id,
3913 p_name =>'P_VRE_INFORMATION20' );
3914
3915
3916 l_across_assignments := hr_transaction_api.get_varchar2_value (
3917 p_transaction_step_id => p_transaction_step_id,
3918 p_name =>'P_ACROSS_ASSIGNMENTS');
3919
3920
3921 l_usage_type := hr_transaction_api.get_varchar2_value (
3922 p_transaction_step_id => p_transaction_step_id,
3923 p_name =>'P_USAGE_TYPE');
3924
3925 l_default_vehicle := hr_transaction_api.get_varchar2_value (
3926 p_transaction_step_id => p_transaction_step_id,
3927 p_name =>'P_DEFAULT_VEHICLE');
3928
3929 l_fuel_card := hr_transaction_api.get_varchar2_value (
3930 p_transaction_step_id => p_transaction_step_id,
3931 p_name =>'P_FUEL_CARD');
3932
3933 l_fuel_card_number := hr_transaction_api.get_varchar2_value (
3934 p_transaction_step_id => p_transaction_step_id,
3935 p_name =>'P_FUEL_CARD_NUMBER');
3936
3937 l_val_attribute_category := hr_transaction_api.get_varchar2_value (
3938 p_transaction_step_id => p_transaction_step_id,
3939 p_name =>'P_VAL_ATTRIBUTE_CATEGORY');
3940
3941 l_val_attribute1 := hr_transaction_api.get_varchar2_value (
3942 p_transaction_step_id => p_transaction_step_id,
3943 p_name =>'P_VAL_ATTRIBUTE1');
3944
3945 l_val_attribute2 := hr_transaction_api.get_varchar2_value (
3946 p_transaction_step_id => p_transaction_step_id,
3947 p_name =>'P_VAL_ATTRIBUTE2');
3948
3949 l_val_attribute3 := hr_transaction_api.get_varchar2_value (
3950 p_transaction_step_id => p_transaction_step_id,
3951 p_name =>'P_VAL_ATTRIBUTE3');
3952
3953 l_val_attribute4 := hr_transaction_api.get_varchar2_value (
3954 p_transaction_step_id => p_transaction_step_id,
3955 p_name =>'P_VAL_ATTRIBUTE4');
3956
3957 l_val_attribute5 := hr_transaction_api.get_varchar2_value (
3958 p_transaction_step_id => p_transaction_step_id,
3959 p_name =>'P_VAL_ATTRIBUTE5');
3960 l_val_attribute6 :=hr_transaction_api.get_varchar2_value (
3961 p_transaction_step_id => p_transaction_step_id,
3962 p_name =>'P_VAL_ATTRIBUTE6');
3963 l_val_attribute7 :=hr_transaction_api.get_varchar2_value (
3964 p_transaction_step_id => p_transaction_step_id,
3965 p_name =>'P_VAL_ATTRIBUTE7');
3966 l_val_attribute8 :=hr_transaction_api.get_varchar2_value (
3967 p_transaction_step_id => p_transaction_step_id,
3968 p_name =>'P_VAL_ATTRIBUTE8');
3969 l_val_attribute9 :=hr_transaction_api.get_varchar2_value (
3970 p_transaction_step_id => p_transaction_step_id,
3971 p_name =>'P_VAL_ATTRIBUTE9');
3972 l_val_attribute10 :=hr_transaction_api.get_varchar2_value (
3973 p_transaction_step_id => p_transaction_step_id,
3974 p_name =>'P_VAL_ATTRIBUTE10');
3975 l_val_attribute11 := hr_transaction_api.get_varchar2_value (
3976 p_transaction_step_id => p_transaction_step_id,
3977 p_name =>'P_VAL_ATTRIBUTE11');
3978
3979 l_val_attribute12 :=hr_transaction_api.get_varchar2_value (
3980 p_transaction_step_id => p_transaction_step_id,
3981 p_name =>'P_VAL_ATTRIBUTE12');
3982 l_val_attribute13 :=hr_transaction_api.get_varchar2_value (
3983 p_transaction_step_id => p_transaction_step_id,
3984 p_name =>'P_VAL_ATTRIBUTE13');
3985 l_val_attribute14 :=hr_transaction_api.get_varchar2_value (
3986 p_transaction_step_id => p_transaction_step_id,
3987 p_name =>'P_VAL_ATTRIBUTE14');
3988 l_val_attribute15 :=hr_transaction_api.get_varchar2_value (
3989 p_transaction_step_id => p_transaction_step_id,
3990 p_name =>'P_VAL_ATTRIBUTE15');
3991 l_val_attribute16 :=hr_transaction_api.get_varchar2_value (
3992 p_transaction_step_id => p_transaction_step_id,
3993 p_name =>'P_VAL_ATTRIBUTE16');
3994 l_val_attribute17 :=hr_transaction_api.get_varchar2_value (
3995 p_transaction_step_id => p_transaction_step_id,
3996 p_name =>'P_VAL_ATTRIBUTE17');
3997 l_val_attribute18 :=hr_transaction_api.get_varchar2_value (
3998 p_transaction_step_id => p_transaction_step_id,
3999 p_name =>'P_VAL_ATTRIBUTE18');
4000 l_val_attribute19 :=hr_transaction_api.get_varchar2_value (
4001 p_transaction_step_id => p_transaction_step_id,
4002 p_name =>'P_VAL_ATTRIBUTE19');
4003 l_val_attribute20 :=hr_transaction_api.get_varchar2_value (
4004 p_transaction_step_id => p_transaction_step_id,
4005 p_name =>'P_VAL_ATTRIBUTE20');
4006 l_val_information_category := hr_transaction_api.get_varchar2_value (
4007 p_transaction_step_id => p_transaction_step_id,
4008 p_name =>'P_VAL_INFORMATION_CATEGORY');
4009
4010 l_val_information1 := hr_transaction_api.get_varchar2_value (
4011 p_transaction_step_id => p_transaction_step_id,
4012 p_name =>'P_VAL_INFORMATION1');
4013
4014
4015 l_val_information2 := hr_transaction_api.get_varchar2_value (
4016 p_transaction_step_id => p_transaction_step_id,
4017 p_name =>'P_VAL_INFORMATION2');
4018
4019 l_val_information3 := hr_transaction_api.get_varchar2_value (
4020 p_transaction_step_id => p_transaction_step_id,
4021 p_name =>'P_VAL_INFORMATION3');
4022
4023 l_val_information4 := hr_transaction_api.get_varchar2_value (
4024 p_transaction_step_id => p_transaction_step_id,
4025 p_name =>'P_VAL_INFORMATION4');
4026
4027 l_val_information5 := hr_transaction_api.get_varchar2_value (
4028 p_transaction_step_id => p_transaction_step_id,
4029 p_name =>'P_VAL_INFORMATION5');
4030
4031 l_val_information6 := hr_transaction_api.get_varchar2_value (
4032 p_transaction_step_id => p_transaction_step_id,
4033 p_name =>'P_VAL_INFORMATION6');
4034
4035 l_val_information7 := hr_transaction_api.get_varchar2_value (
4036 p_transaction_step_id => p_transaction_step_id,
4037 p_name =>'P_VAL_INFORMATION7');
4038
4039 l_val_information8 := hr_transaction_api.get_varchar2_value (
4040 p_transaction_step_id => p_transaction_step_id,
4041 p_name =>'P_VAL_INFORMATION8');
4042
4043 l_val_information9 := hr_transaction_api.get_varchar2_value (
4044 p_transaction_step_id => p_transaction_step_id,
4045 p_name =>'P_VAL_INFORMATION9');
4046
4047 l_val_information10 := hr_transaction_api.get_varchar2_value (
4048 p_transaction_step_id => p_transaction_step_id,
4049 p_name =>'P_VAL_INFORMATION10');
4050
4051 l_val_information11 := hr_transaction_api.get_varchar2_value (
4052 p_transaction_step_id => p_transaction_step_id,
4053 p_name =>'P_VAL_INFORMATION11');
4054
4055 l_val_information12 := hr_transaction_api.get_varchar2_value (
4056 p_transaction_step_id => p_transaction_step_id,
4057 p_name =>'P_VAL_INFORMATION12');
4058
4059 l_val_information13 := hr_transaction_api.get_varchar2_value (
4060 p_transaction_step_id => p_transaction_step_id,
4061 p_name =>'P_VAL_INFORMATION13');
4062
4063 l_val_information14 := hr_transaction_api.get_varchar2_value (
4064 p_transaction_step_id => p_transaction_step_id,
4065 p_name =>'P_VAL_INFORMATION14');
4066
4067 l_val_information15 := hr_transaction_api.get_varchar2_value (
4068 p_transaction_step_id => p_transaction_step_id,
4069 p_name =>'P_VAL_INFORMATION15');
4070
4071 l_val_information16 := hr_transaction_api.get_varchar2_value (
4072 p_transaction_step_id => p_transaction_step_id,
4073 p_name =>'P_VAL_INFORMATION16');
4074
4075 l_val_information17 := hr_transaction_api.get_varchar2_value (
4076 p_transaction_step_id => p_transaction_step_id,
4077 p_name =>'P_VAL_INFORMATION17');
4078
4079 l_val_information18 := hr_transaction_api.get_varchar2_value (
4080 p_transaction_step_id => p_transaction_step_id,
4081 p_name =>'P_VAL_INFORMATION18');
4082
4083 l_val_information19 := hr_transaction_api.get_varchar2_value (
4084 p_transaction_step_id => p_transaction_step_id,
4085 p_name =>'P_VAL_INFORMATION19');
4086
4087 l_val_information20 := hr_transaction_api.get_varchar2_value (
4088 p_transaction_step_id => p_transaction_step_id,
4089 p_name =>'P_VAL_INFORMATION20');
4090
4091 l_fuel_benefit := hr_transaction_api.get_varchar2_value (
4092 p_transaction_step_id => p_transaction_step_id,
4093 p_name =>'P_FUEL_BENEFIT');
4094
4095
4096 l_vehicle_repository_id := hr_transaction_api.get_number_value (
4097 p_transaction_step_id => p_transaction_step_id,
4098 p_name =>'P_VEHICLE_REPOSITORY_ID');
4099 l_vehicle_allocation_id := hr_transaction_api.get_number_value (
4100 p_transaction_step_id => p_transaction_step_id,
4101 p_name =>'P_VEHICLE_ALLOCATION_ID');
4102 l_object_version_number := hr_transaction_api.get_number_value (
4103 p_transaction_step_id => p_transaction_step_id,
4104 p_name =>'P_OBJECT_VERSION_NUMBER');
4105
4106
4107 OPEN c_get_count;
4108 FETCH c_get_count INTO l_get_count;
4109 CLOSE c_get_count;
4110
4111 FOR i in 1..l_get_count
4112 LOOP
4113 l_user_info (i).person_id :=hr_transaction_api.get_number_value (
4114 p_transaction_step_id => p_transaction_step_id,
4115 p_name =>'P_PERSON_USR_ID'||i);
4116
4117 l_user_info (i).assignment_id :=hr_transaction_api.get_number_value (
4118 p_transaction_step_id => p_transaction_step_id,
4119 p_name =>'P_ASSIGNMENT_USR_ID'||i);
4120
4121
4122 l_user_info (i).user_type :=hr_transaction_api.get_varchar2_value (
4123 p_transaction_step_id => p_transaction_step_id,
4124 p_name =>'P_USER_TYPE'||i);
4125
4126
4127 END LOOP;
4128
4129
4130
4131
4132 create_vehicle_details
4133 (
4134 p_validate => false
4135 ,p_effective_date => l_effective_date
4136 ,p_registration_number =>l_registration_number
4137 ,p_vehicle_type =>l_vehicle_type
4138 ,p_vehicle_id_number =>l_vehicle_id_number
4139 ,p_business_group_id =>l_business_group_id
4140 ,p_make =>l_make
4141 ,p_engine_capacity_in_cc =>l_engine_capacity_in_cc
4142 ,p_fuel_type =>l_fuel_type
4143 ,p_currency_code =>l_currency_code
4144 ,p_model =>l_model
4145 ,p_initial_registration =>l_initial_registration
4146 ,p_last_registration_renew_date =>l_last_registration_renew_date
4147 ,p_fiscal_ratings =>l_fiscal_ratings
4148 ,p_vehicle_ownership =>l_vehicle_ownership
4149 ,p_shared_vehicle =>l_shared_vehicle
4150 ,p_color =>l_color
4151 ,p_seating_capacity =>l_seating_capacity
4152 ,p_weight =>l_weight
4153 ,p_weight_uom =>l_weight_uom
4154 ,p_model_year =>l_model_year
4155 ,p_insurance_number =>l_insurance_number
4156 ,p_insurance_expiry_date =>l_insurance_expiry_date
4157 ,p_taxation_method =>l_taxation_method
4158 ,p_comments =>l_comments
4159 ,p_vre_attribute_category =>l_vre_attribute_category
4160 ,p_vre_attribute1 =>l_vre_attribute1
4161 ,p_vre_attribute2 =>l_vre_attribute2
4162 ,p_vre_attribute3 =>l_vre_attribute3
4163 ,p_vre_attribute4 =>l_vre_attribute4
4164 ,p_vre_attribute5 =>l_vre_attribute5
4165 ,p_vre_attribute6 =>l_vre_attribute6
4166 ,p_vre_attribute7 =>l_vre_attribute7
4167 ,p_vre_attribute8 =>l_vre_attribute8
4168 ,p_vre_attribute9 =>l_vre_attribute9
4169 ,p_vre_attribute10 =>l_vre_attribute10
4170 ,p_vre_attribute11 =>l_vre_attribute11
4171 ,p_vre_attribute12 =>l_vre_attribute12
4172 ,p_vre_attribute13 =>l_vre_attribute13
4173 ,p_vre_attribute14 =>l_vre_attribute14
4174 ,p_vre_attribute15 =>l_vre_attribute15
4175 ,p_vre_attribute16 =>l_vre_attribute16
4176 ,p_vre_attribute17 =>l_vre_attribute17
4177 ,p_vre_attribute18 =>l_vre_attribute18
4178 ,p_vre_attribute19 =>l_vre_attribute19
4179 ,p_vre_attribute20 =>l_vre_attribute20
4180 ,p_vre_information_category =>l_vre_information_category
4181 ,p_vre_information1 =>l_vre_information1
4182 ,p_vre_information2 =>l_vre_information2
4183 ,p_vre_information3 =>l_vre_information3
4184 ,p_vre_information4 =>l_vre_information4
4185 ,p_vre_information5 =>l_vre_information5
4186 ,p_vre_information6 =>l_vre_information6
4187 ,p_vre_information7 =>l_vre_information7
4188 ,p_vre_information8 =>l_vre_information8
4189 ,p_vre_information9 =>l_vre_information9
4190 ,p_vre_information10 =>l_vre_information10
4191 ,p_vre_information11 =>l_vre_information11
4192 ,p_vre_information12 =>l_vre_information12
4193 ,p_vre_information13 =>l_vre_information13
4194 ,p_vre_information14 =>l_vre_information14
4195 ,p_vre_information15 =>l_vre_information15
4196 ,p_vre_information16 =>l_vre_information16
4197 ,p_vre_information17 =>l_vre_information17
4198 ,p_vre_information18 =>l_vre_information18
4199 ,p_vre_information19 =>l_vre_information19
4200 ,p_vre_information20 =>l_vre_information20
4201 ,p_across_assignments =>l_across_assignments
4202 ,p_usage_type =>l_usage_type
4203 ,p_default_vehicle =>l_default_vehicle
4204 ,p_fuel_card =>l_fuel_card
4205 ,p_fuel_card_number =>l_fuel_card_number
4206 ,p_val_attribute_category =>l_val_attribute_category
4207 ,p_val_attribute1 =>l_val_attribute1
4208 ,p_val_attribute2 =>l_val_attribute2
4209 ,p_val_attribute3 =>l_val_attribute3
4210 ,p_val_attribute4 =>l_val_attribute4
4211 ,p_val_attribute5 =>l_val_attribute5
4212 ,p_val_attribute6 =>l_val_attribute6
4213 ,p_val_attribute7 =>l_val_attribute7
4214 ,p_val_attribute8 =>l_val_attribute8
4215 ,p_val_attribute9 =>l_val_attribute9
4216 ,p_val_attribute10 =>l_val_attribute10
4217 ,p_val_attribute11 =>l_val_attribute11
4218 ,p_val_attribute12 =>l_val_attribute12
4219 ,p_val_attribute13 =>l_val_attribute13
4220 ,p_val_attribute14 =>l_val_attribute14
4221 ,p_val_attribute15 =>l_val_attribute15
4222 ,p_val_attribute16 =>l_val_attribute16
4223 ,p_val_attribute17 =>l_val_attribute17
4224 ,p_val_attribute18 =>l_val_attribute18
4225 ,p_val_attribute19 =>l_val_attribute19
4226 ,p_val_attribute20 =>l_val_attribute20
4227 ,p_val_information_category =>l_val_information_category
4228 ,p_val_information1 =>l_val_information1
4229 ,p_val_information2 =>l_val_information2
4230 ,p_val_information3 =>l_val_information3
4231 ,p_val_information4 =>l_val_information4
4232 ,p_val_information5 =>l_val_information5
4233 ,p_val_information6 =>l_val_information6
4234 ,p_val_information7 =>l_val_information7
4235 ,p_val_information8 =>l_val_information8
4236 ,p_val_information9 =>l_val_information9
4237 ,p_val_information10 =>l_val_information10
4238 ,p_val_information11 =>l_val_information11
4239 ,p_val_information12 =>l_val_information12
4240 ,p_val_information13 =>l_val_information13
4241 ,p_val_information14 =>l_val_information14
4242 ,p_val_information15 =>l_val_information15
4243 ,p_val_information16 =>l_val_information16
4244 ,p_val_information17 =>l_val_information17
4245 ,p_val_information18 =>l_val_information18
4246 ,p_val_information19 =>l_val_information19
4247 ,p_val_information20 =>l_val_information20
4248 ,p_fuel_benefit =>l_fuel_benefit
4249 ,p_user_info =>l_user_info
4250 ,p_vehicle_repository_id =>l_vehicle_repository_id
4251 ,p_vehicle_allocation_id =>l_vehicle_allocation_id
4252 ,p_object_version_number =>l_object_version_number
4253 ,p_error_message =>l_error_message
4254 ,p_error_status =>l_error_status
4255 );
4256 l_assignment_id:=l_user_info(1).assignment_id;
4257 OPEN c_get_other_tstep;
4258 LOOP
4259 FETCH c_get_other_tstep INTO l_get_other_tstep;
4260 EXIT WHEN c_get_other_tstep%NOTFOUND;
4261
4262
4263 END LOOP;
4264 CLOSE c_get_other_tstep;
4265
4266 OPEN c_get_details;
4267 FETCH c_get_details INTO l_get_details;
4268 CLOSE c_get_details;
4269
4270 wf_engine.SetItemAttrNumber( itemtype =>l_get_other_tstep.item_type,
4271 itemkey =>l_get_other_tstep.item_key,
4272 aname =>'PQP_VEH_REPOSITORY_ID_ATTR',
4273 avalue =>l_get_details.vehicle_repository_id);
4274
4275 wf_engine.SetItemAttrNumber( itemtype =>l_get_other_tstep.item_type,
4276 itemkey =>l_get_other_tstep.item_key,
4277 aname =>'PQP_VEH_ALLOCATION_ID_ATTR',
4278 avalue =>l_get_details.vehicle_allocation_id);
4279
4280 --
4281 --
4282 --
4283 hr_utility.set_location('Leaving: process_api',10);
4284 --hr_utility.trace_off;
4285 EXCEPTION
4286 WHEN hr_utility.hr_error THEN
4287 ROLLBACK TO process_veh_details;
4288
4289 hr_utility.raise_error;
4290 WHEN OTHERS THEN
4291 RAISE; -- Raise error here relevant to the new tech stack.
4292 END process_api;
4293
4294 --
4295 ---Delete call
4296 PROCEDURE delete_allocation(
4297 p_validate IN BOOLEAN
4298 ,p_effective_date IN DATE
4299 ,p_assignment_id IN NUMBER
4300 ,p_vehicle_allocation_id IN NUMBER
4301 ,p_business_group_id IN NUMBER
4302 ,p_error_status OUT NOCOPY VARCHAR2
4303 )
4304
4305 IS
4306
4307 CURSOR c_get_ovn (cp_allocation_id NUMBER
4308 ,cp_business_group_id NUMBER
4309 ,cp_assignment_id NUMBER
4310 ,cp_effective_date DATE
4311 )
4312 IS
4313 SELECT pva.object_version_number
4314 ,pva.vehicle_repository_id repository_id
4315 FROM pqp_vehicle_allocations_f pva
4316 WHERE pva.vehicle_allocation_id =cp_allocation_id
4317 AND pva.assignment_id =cp_assignment_id
4318 AND pva.business_group_id =cp_business_group_id
4319 AND rtrim(ltrim(cp_effective_date)) BETWEEN pva.effective_start_date
4320 AND pva.effective_end_date;
4321
4322 CURSOR c_get_tot_users (cp_repository_id NUMBER
4323 ,cp_business_group_id NUMBER
4324 ,cp_assignment_id NUMBER
4325 ,cp_effective_date DATE
4326 )
4327 IS
4328 SELECT COUNT(pva.vehicle_repository_id) usr_count
4329 FROM pqp_vehicle_allocations_f pva
4330 WHERE pva.vehicle_repository_id = cp_repository_id
4331 AND pva.business_group_id =cp_business_group_id
4332 AND pva.assignment_id = cp_assignment_id
4333 AND rtrim(ltrim(cp_effective_date)) BETWEEN pva.effective_start_date
4334 AND pva.effective_end_date;
4335
4336 CURSOR c_get_rep_ovn (cp_repository_id NUMBER
4337 ,cp_business_group_id NUMBER
4338 ,cp_effective_date DATE
4339 )
4340 IS
4341 SELECT pvr.object_version_number ovn
4342 FROM pqp_vehicle_repository_f pvr
4343 WHERE pvr.vehicle_repository_id = cp_repository_id
4344 AND pvr.business_group_id = cp_business_group_id
4345 AND rtrim(ltrim(cp_effective_date)) BETWEEN pvr.effective_start_date
4346 AND pvr.effective_end_date;
4347
4348 l_get_rep_ovn c_get_rep_ovn%ROWTYPE;
4349 l_get_ovn c_get_ovn%ROWTYPE;
4350 l_get_tot_users c_get_tot_users%ROWTYPE;
4351 l_effective_start_date DATE;
4352 l_effective_end_date DATE;
4353
4354 BEGIN
4355
4356 hr_utility.set_location('Leaving: process_api',10);
4357
4358
4359 OPEN c_get_ovn (
4360 p_vehicle_allocation_id
4361 ,p_business_group_id
4362 ,p_assignment_id
4363 ,p_effective_date
4364 );
4365 FETCH c_get_ovn INTO l_get_ovn;
4366 CLOSE c_get_ovn;
4367
4368 OPEN c_get_tot_users (l_get_ovn.repository_id
4369 ,p_business_group_id
4370 ,p_assignment_id
4371 ,p_effective_date
4372 );
4373 FETCH c_get_tot_users INTO l_get_tot_users;
4374 CLOSE c_get_tot_users;
4375
4376
4377 --Calling delete api for allocation.
4378 PQP_VEHICLE_ALLOCATIONS_API.delete_vehicle_allocation(
4379 p_validate => p_validate
4380 ,p_effective_date => ltrim(rtrim(p_effective_date))
4381 ,p_datetrack_mode =>'DELETE'
4382 ,p_vehicle_allocation_id =>p_vehicle_allocation_id
4383 ,p_object_version_number =>l_get_ovn.object_version_number
4384 ,p_effective_start_date =>l_effective_start_date
4385 ,p_effective_end_date =>l_effective_end_date
4386 );
4387
4388 IF l_get_tot_users.usr_count = 0 THEN
4389 OPEN c_get_rep_ovn (l_get_ovn.repository_id
4390 ,p_business_group_id
4391 ,p_effective_date
4392 );
4393 FETCH c_get_rep_ovn INTO l_get_rep_ovn;
4394
4395 CLOSE c_get_rep_ovn;
4396
4397 --Callin delete api for vehicles.
4398 pqp_vehicle_repository_api.delete_vehicle
4399 (p_validate => p_validate
4400 ,p_effective_date => ltrim(rtrim(p_effective_date))
4401 ,p_datetrack_mode => 'DELETE'
4402 ,p_vehicle_repository_id => l_get_ovn.repository_id
4403 ,p_object_version_number => l_get_rep_ovn.ovn
4404 ,p_effective_start_date => l_effective_start_date
4405 ,p_effective_end_date => l_effective_end_date
4406 );
4407 END IF;
4408
4409 p_error_status := 'Y';
4410
4411 EXCEPTION
4412 WHEN hr_utility.hr_error THEN
4413 p_error_status := 'N';
4414 hr_utility.raise_error;
4415 WHEN OTHERS THEN
4416 RAISE; -- Raise error here relevant to the new tech stack.
4417
4418 END delete_allocation;
4419 END PQP_SS_VEHICLE_TRANSACTIONS;