[Home] [Help]
PACKAGE BODY: APPS.PQP_SS_VEHICLE_TRANSACTIONS
Source
1 PACKAGE BODY PQP_SS_VEHICLE_TRANSACTIONS AS
2 /* $Header: pqpssvehinfo.pkb 120.0 2005/05/29 02:22:18 appldev noship $*/
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_effective_start_date DATE;
1289 l_effective_end_date DATE;
1290 l_cnt1 NUMBER;
1291 l_chk NUMBER:=0;
1292 l_dt_adj number:=0;
1293 e_exist_other_asg EXCEPTION;
1294 BEGIN
1295
1296 l_user_info(1).person_id := p_user_info(1).person_id;
1297 l_user_info(1).assignment_id := p_user_info(1).assignment_id;
1298 l_user_info(1).user_type := p_user_info(1).user_type;
1299
1300 --Check if the main user has chosen to share across his own
1301 --assignments, get all assignemnts for that person
1302 -- and store it in a plsql table.
1303 IF p_across_assignments = 'Y' THEN
1304 l_cnt1:=l_user_info.count;
1305 OPEN c_get_main_user_assignments (
1306 p_user_info(1).person_id
1307 ,p_user_info(1).assignment_id
1308 ,p_business_group_id
1309 ,p_effective_date
1310 );
1311 LOOP
1312 FETCH c_get_main_user_assignments INTO l_assignment_id;
1313 EXIT WHEN c_get_main_user_assignments%NOTFOUND;
1314 l_user_info(l_cnt1+1).person_id := p_user_info(1).person_id;
1315 l_user_info(l_cnt1+1).assignment_id
1316 := l_assignment_id;
1317 --'SA' Stands for secondary assignment
1318 l_user_info(l_cnt1+1).user_type := 'SA';
1319 l_cnt1:=l_cnt1+1;
1320 END LOOP;
1321 CLOSE c_get_main_user_assignments;
1322 END IF;
1323
1324 BEGIN
1325
1326 IF p_shared_vehicle='Y' AND p_user_info.count>1 THEN
1327 l_cnt:=l_user_info.count;
1328
1329 FOR k in 2..(p_user_info.count)
1330 LOOP
1331 l_cnt:=l_cnt+1;
1332 l_user_info(l_cnt).person_id := p_user_info(k).person_id;
1333 l_user_info(l_cnt).assignment_id
1334 := p_user_info(k).assignment_id;
1335 --'OA' Stands for other employee's assignments
1336 l_user_info(l_cnt).user_type := 'OA';
1337
1338 END LOOP;
1339 END IF; --shared vehicle;
1340 EXCEPTION
1341 WHEN hr_utility.hr_error THEN
1342 hr_utility.raise_error;
1343 WHEN OTHERS THEN
1344 RAISE; -- Raise error here relevant to the new tech stack.
1345 END;
1346 ---check if other users flag is set to yes, if set to yes
1347 --then get the assignments for all their person ids
1348
1349 --Call create apis for both repository and allocations
1350 --and based on number of users and whether the allocation
1351 --is for all the assignments of that person, the api is
1352 --called in the loop.
1353
1354 --Call Create vehicle repository api
1355
1356 IF p_vehicle_repository_id is NULL THEN
1357 Begin
1358
1359 --Getting the legislationId for business groupId
1360 l_leg_code :=
1361 pqp_vre_bus.get_legislation_code(p_business_group_id);
1362 --setting the lg context
1363 hr_api.set_legislation_context(l_leg_code);
1364
1365 OPEN c_fiscal_uom;
1366 FETCH c_fiscal_uom INTO l_lookup_code;
1367 CLOSE c_fiscal_uom;
1368 EXCEPTION
1369 WHEN no_data_found THEN
1370 l_lookup_code := NULL;
1371 End ;
1372 pqp_vehicle_repository_api.create_vehicle
1373 (p_validate => p_validate
1374 ,p_effective_date => p_effective_date
1375 ,p_registration_number => p_registration_number
1376 ,p_vehicle_type => p_vehicle_type
1377 ,p_vehicle_id_number => p_vehicle_id_number
1378 ,p_business_group_id => p_business_group_id
1379 ,p_make => p_make
1380 ,p_engine_capacity_in_cc => p_engine_capacity_in_cc
1381 ,p_fuel_type => p_fuel_type
1382 ,p_currency_code => p_currency_code
1383 ,p_vehicle_status => 'A'
1384 ,p_model => p_model
1385 ,p_initial_registration => p_initial_registration
1386 ,p_last_registration_renew_date => p_last_registration_renew_date
1387 ,p_fiscal_ratings => p_fiscal_ratings
1388 ,p_fiscal_ratings_uom => l_lookup_code --p_fiscal_ratings_uom
1389 ,p_vehicle_ownership => p_vehicle_ownership
1390 ,p_shared_vehicle => p_shared_vehicle
1391 ,p_taxation_method => p_taxation_method
1392 ,p_color => p_color
1393 ,p_seating_capacity => p_seating_capacity
1394 ,p_weight => p_weight
1395 ,p_weight_uom => p_weight_uom
1396 ,p_model_year => p_model_year
1397 ,p_insurance_number => p_insurance_number
1398 ,p_insurance_expiry_date => p_insurance_expiry_date
1399 ,p_comments => p_comments
1400 ,p_vre_attribute_category => p_vre_attribute_category
1401 ,p_vre_attribute1 => p_vre_attribute1
1402 ,p_vre_attribute2 => p_vre_attribute2
1403 ,p_vre_attribute3 => p_vre_attribute3
1404 ,p_vre_attribute4 => p_vre_attribute4
1405 ,p_vre_attribute5 => p_vre_attribute5
1406 ,p_vre_attribute6 => p_vre_attribute6
1407 ,p_vre_attribute7 => p_vre_attribute7
1408 ,p_vre_attribute8 => p_vre_attribute8
1409 ,p_vre_attribute9 => p_vre_attribute9
1410 ,p_vre_attribute10 => p_vre_attribute10
1411 ,p_vre_attribute11 => p_vre_attribute11
1412 ,p_vre_attribute12 => p_vre_attribute12
1413 ,p_vre_attribute13 => p_vre_attribute13
1414 ,p_vre_attribute14 => p_vre_attribute14
1415 ,p_vre_attribute15 => p_vre_attribute15
1416 ,p_vre_attribute16 => p_vre_attribute16
1417 ,p_vre_attribute17 => p_vre_attribute17
1418 ,p_vre_attribute18 => p_vre_attribute18
1419 ,p_vre_attribute19 => p_vre_attribute19
1420 ,p_vre_attribute20 => p_vre_attribute20
1421 ,p_vre_information_category => p_vre_information_category
1422 ,p_vre_information1 => p_vre_information1
1423 ,p_vre_information2 => p_vre_information2
1424 ,p_vre_information3 => p_vre_information3
1425 ,p_vre_information4 => p_vre_information4
1426 ,p_vre_information5 => p_vre_information5
1427 ,p_vre_information6 => p_vre_information6
1428 ,p_vre_information7 => p_vre_information7
1429 ,p_vre_information8 => p_vre_information8
1430 ,p_vre_information9 => p_vre_information9
1431 ,p_vre_information10 => p_vre_information10
1432 ,p_vre_information11 => p_vre_information11
1433 ,p_vre_information12 => p_vre_information12
1434 ,p_vre_information13 => p_vre_information13
1435 ,p_vre_information14 => p_vre_information14
1436 ,p_vre_information15 => p_vre_information15
1437 ,p_vre_information16 => p_vre_information16
1438 ,p_vre_information17 => p_vre_information17
1439 ,p_vre_information18 => p_vre_information18
1440 ,p_vre_information19 => p_vre_information19
1441 ,p_vre_information20 => p_vre_information20
1442 ,p_vehicle_repository_id => l_vehicle_repository_id
1443 ,p_object_version_number => l_object_version_number
1444 ,p_effective_start_date => l_effective_start_date
1445 ,p_effective_end_date => l_effective_end_date
1446 );
1447
1448 -- Create allocations
1449
1450 FOR i in 1..l_user_info.count
1451 LOOP
1452 pqp_vehicle_allocations_api.create_vehicle_allocation
1453 (p_validate => p_validate
1454 ,p_effective_date => p_effective_date
1455 ,p_assignment_id => l_user_info(i).assignment_id
1456 ,p_business_group_id => p_business_group_id
1457 ,p_vehicle_repository_id => l_vehicle_repository_id
1458 ,p_across_assignments => p_across_assignments
1459 ,p_usage_type => p_usage_type
1460 ,p_default_vehicle => p_default_vehicle
1461 ,p_fuel_card => p_fuel_card
1462 ,p_fuel_card_number => p_fuel_card_number
1463 ,p_insurance_number => p_insurance_number
1464 ,p_insurance_expiry_date => p_insurance_expiry_date
1465 ,p_val_attribute_category => p_val_attribute_category
1466 ,p_val_attribute1 => p_val_attribute1
1467 ,p_val_attribute2 => p_val_attribute2
1468 ,p_val_attribute3 => p_val_attribute3
1469 ,p_val_attribute4 => p_val_attribute4
1470 ,p_val_attribute5 => p_val_attribute5
1471 ,p_val_attribute6 => p_val_attribute6
1472 ,p_val_attribute7 => p_val_attribute7
1473 ,p_val_attribute8 => p_val_attribute8
1474 ,p_val_attribute9 => p_val_attribute9
1475 ,p_val_attribute10 => p_val_attribute10
1476 ,p_val_attribute11 => p_val_attribute11
1477 ,p_val_attribute12 => p_val_attribute12
1478 ,p_val_attribute13 => p_val_attribute13
1479 ,p_val_attribute14 => p_val_attribute14
1480 ,p_val_attribute15 => p_val_attribute15
1481 ,p_val_attribute16 => p_val_attribute16
1482 ,p_val_attribute17 => p_val_attribute17
1483 ,p_val_attribute18 => p_val_attribute18
1484 ,p_val_attribute19 => p_val_attribute19
1485 ,p_val_attribute20 => p_val_attribute20
1486 ,p_val_information_category => p_val_information_category
1487 ,p_val_information1 => p_val_information1
1488 ,p_val_information2 => p_val_information2
1489 ,p_val_information3 => p_val_information3
1490 ,p_val_information4 => p_val_information4
1491 ,p_val_information5 => p_val_information5
1492 ,p_val_information6 => p_val_information6
1493 ,p_val_information7 => p_val_information7
1494 ,p_val_information8 => p_val_information8
1495 ,p_val_information9 => p_val_information9
1496 ,p_val_information10 => p_val_information10
1497 ,p_val_information11 => p_val_information11
1498 ,p_val_information12 => p_val_information12
1499 ,p_val_information13 => p_val_information13
1500 ,p_val_information14 => p_val_information14
1501 ,p_val_information15 => p_val_information15
1502 ,p_val_information16 => p_val_information16
1503 ,p_val_information17 => p_val_information17
1504 ,p_val_information18 => p_val_information18
1505 ,p_val_information19 => p_val_information19
1506 ,p_val_information20 => p_val_information20
1507 ,p_fuel_benefit => p_fuel_benefit
1508 ,p_vehicle_allocation_id => l_vehicle_allocation_id
1509 ,p_object_version_number => l_object_version_number
1510 ,p_effective_start_date => l_effective_start_date
1511 ,p_effective_end_date => l_effective_end_date
1512 );
1513 END LOOP;
1514
1515
1516 ELSE
1517 --Update
1518 l_dt_adj:=0;
1519
1520 OPEN c_get_repository_id (p_registration_number
1521 ,p_business_group_id
1522 ,p_effective_date
1523 );
1524
1525 FETCH c_get_repository_id INTO l_get_repository_id;
1526 CLOSE c_get_repository_id;
1527
1528 l_object_version_number := l_get_repository_id.object_version_number;
1529 pqp_get_date_mode.find_dt_upd_modes
1530 (p_effective_date => p_effective_date
1531 ,p_base_table_name => 'PQP_VEHICLE_REPOSITORY_F'
1532 ,p_base_key_column => 'VEHICLE_REPOSITORY_ID'
1533 ,p_base_key_value => l_get_repository_id.vehicle_repository_id
1534 ,p_correction => l_correction
1535 ,p_update => l_update
1536 ,p_update_override => l_update_override
1537 ,p_update_change_insert => l_update_change_insert
1538 );
1539
1540 IF l_correction = 1 THEN
1541 l_datetrack_mode :='CORRECTION' ;
1542 ELSIF l_update = 1 THEN
1543 l_datetrack_mode :='UPDATE' ;
1544 END IF;
1545
1546 --Update vehicle repository
1547 <<update_vehicle>>
1548 BEGIN
1549 --Checking to see if the shared_vehicle is switched from Yes to No
1550 --This will reverse the process of calling the API as
1551 -- we need to end date all the allocations for the additional
1552 --users and then comeback to update the vehicle repository
1553 --by incrementing the update date by one. Without incrementing
1554 --the date the api will give an error because the end dated
1555 --allocation will fall on the same day when updating vehicle
1556 --repository with flag 'N' and this would give an error.
1557 IF l_get_repository_id.shared_vehicle ='Y'
1558 AND p_shared_vehicle ='N' AND l_chk<>2THEN
1559 RAISE e_exist_other_asg;
1560 END IF;
1561
1562 pqp_vehicle_repository_api.update_vehicle
1563 (p_validate => p_validate
1564 ,p_effective_date => p_effective_date+(l_dt_adj)
1565 ,p_datetrack_mode => l_datetrack_mode
1566 ,p_vehicle_repository_id => l_get_repository_id.vehicle_repository_id
1567 ,p_object_version_number => l_object_version_number
1568 ,p_registration_number => p_registration_number
1569 ,p_vehicle_type => p_vehicle_type
1570 ,p_vehicle_id_number => p_vehicle_id_number
1571 ,p_business_group_id => p_business_group_id
1572 ,p_make => p_make
1573 ,p_engine_capacity_in_cc => p_engine_capacity_in_cc
1574 ,p_fuel_type => p_fuel_type
1575 ,p_currency_code => p_currency_code
1576 ,p_vehicle_status => 'A'
1577 ,p_model => p_model
1578 ,p_initial_registration => p_initial_registration
1579 ,p_last_registration_renew_date => p_last_registration_renew_date
1580 ,p_fiscal_ratings => p_fiscal_ratings
1581 ,p_fiscal_ratings_uom => l_lookup_code
1582 ,p_vehicle_ownership => p_vehicle_ownership
1583 ,p_shared_vehicle => p_shared_vehicle
1584 ,p_taxation_method => p_taxation_method
1585 ,p_color => p_color
1586 ,p_seating_capacity => p_seating_capacity
1587 ,p_weight => p_weight
1588 ,p_weight_uom => p_weight_uom
1589 ,p_model_year => p_model_year
1590 ,p_insurance_number => p_insurance_number
1591 ,p_insurance_expiry_date => p_insurance_expiry_date
1592 ,p_comments => p_comments
1593 ,p_vre_attribute_category => p_vre_attribute_category
1594 ,p_vre_attribute1 => p_vre_attribute1
1595 ,p_vre_attribute2 => p_vre_attribute2
1596 ,p_vre_attribute3 => p_vre_attribute3
1597 ,p_vre_attribute4 => p_vre_attribute4
1598 ,p_vre_attribute5 => p_vre_attribute5
1599 ,p_vre_attribute6 => p_vre_attribute6
1600 ,p_vre_attribute7 => p_vre_attribute7
1601 ,p_vre_attribute8 => p_vre_attribute8
1602 ,p_vre_attribute9 => p_vre_attribute9
1603 ,p_vre_attribute10 => p_vre_attribute10
1604 ,p_vre_attribute11 => p_vre_attribute11
1605 ,p_vre_attribute12 => p_vre_attribute12
1606 ,p_vre_attribute13 => p_vre_attribute13
1607 ,p_vre_attribute14 => p_vre_attribute14
1608 ,p_vre_attribute15 => p_vre_attribute15
1609 ,p_vre_attribute16 => p_vre_attribute16
1610 ,p_vre_attribute17 => p_vre_attribute17
1611 ,p_vre_attribute18 => p_vre_attribute18
1612 ,p_vre_attribute19 => p_vre_attribute19
1613 ,p_vre_attribute20 => p_vre_attribute20
1614 ,p_vre_information1 => p_vre_information1
1615 ,p_vre_information2 => p_vre_information2
1616 ,p_vre_information3 => p_vre_information3
1617 ,p_vre_information4 => p_vre_information4
1618 ,p_vre_information5 => p_vre_information5
1619 ,p_vre_information6 => p_vre_information6
1620 ,p_vre_information7 => p_vre_information7
1621 ,p_vre_information8 => p_vre_information8
1622 ,p_vre_information9 => p_vre_information9
1623 ,p_vre_information10 => p_vre_information10
1624 ,p_vre_information11 => p_vre_information11
1625 ,p_vre_information12 => p_vre_information12
1626 ,p_vre_information13 => p_vre_information13
1627 ,p_vre_information14 => p_vre_information14
1628 ,p_vre_information15 => p_vre_information15
1629 ,p_vre_information16 => p_vre_information16
1630 ,p_vre_information17 => p_vre_information17
1631 ,p_vre_information18 => p_vre_information18
1632 ,p_vre_information19 => p_vre_information19
1633 ,p_vre_information20 => p_vre_information20
1634 ,p_effective_start_date => l_effective_start_date
1635 ,p_effective_end_date => l_effective_end_date
1636 );
1637 EXCEPTION
1638 WHEN e_exist_other_asg THEN
1639
1640 l_chk :=1;
1641 l_dt_adj:=1;
1642
1643
1644 END;
1645 -- call update allocation process
1646 --this process works in two different ways
1647 --first it checks for number of assignments that are using the
1648 --vehicle and if the assignment has increased then it will
1649 --create allocation for that assignment and then updates
1650 --all other existing assignments
1651 ---if the assignment has reduced then it will end date the allocation
1652 --for that assignment and updates the rest.
1653 IF l_chk < 2 THEN
1654
1655 update_vehicle_allocations
1656 (p_validate => p_validate
1657 ,p_effective_date => p_effective_date
1658 ,p_business_group_id => p_business_group_id
1659 ,p_registration_number => p_registration_number
1660 ,p_vehicle_repository_id => l_get_repository_id.vehicle_repository_id
1661 ,p_across_assignments => p_across_assignments
1662 ,p_usage_type => p_usage_type
1663 ,p_default_vehicle => p_default_vehicle
1664 ,p_fuel_card => p_fuel_card
1665 ,p_fuel_card_number => p_fuel_card_number
1666 ,p_insurance_number => p_insurance_number
1667 ,p_insurance_expiry_date => p_insurance_expiry_date
1668 ,p_val_attribute_category => p_val_attribute_category
1669 ,p_val_attribute1 => p_val_attribute1
1670 ,p_val_attribute2 => p_val_attribute2
1671 ,p_val_attribute3 => p_val_attribute3
1672 ,p_val_attribute4 => p_val_attribute4
1673 ,p_val_attribute5 => p_val_attribute5
1674 ,p_val_attribute6 => p_val_attribute6
1675 ,p_val_attribute7 => p_val_attribute7
1676 ,p_val_attribute8 => p_val_attribute8
1677 ,p_val_attribute9 => p_val_attribute9
1678 ,p_val_attribute10 => p_val_attribute10
1679 ,p_val_attribute11 => p_val_attribute11
1680 ,p_val_attribute12 => p_val_attribute12
1681 ,p_val_attribute13 => p_val_attribute13
1682 ,p_val_attribute14 => p_val_attribute14
1683 ,p_val_attribute15 => p_val_attribute15
1684 ,p_val_attribute16 => p_val_attribute16
1685 ,p_val_attribute17 => p_val_attribute17
1686 ,p_val_attribute18 => p_val_attribute18
1687 ,p_val_attribute19 => p_val_attribute19
1688 ,p_val_attribute20 => p_val_attribute20
1689 ,p_val_information_category => p_val_information_category
1690 ,p_val_information1 => p_val_information1
1691 ,p_val_information2 => p_val_information2
1692 ,p_val_information3 => p_val_information3
1693 ,p_val_information4 => p_val_information4
1694 ,p_val_information5 => p_val_information5
1695 ,p_val_information6 => p_val_information6
1696 ,p_val_information7 => p_val_information7
1697 ,p_val_information8 => p_val_information8
1698 ,p_val_information9 => p_val_information9
1699 ,p_val_information10 => p_val_information10
1700 ,p_val_information11 => p_val_information11
1701 ,p_val_information12 => p_val_information12
1702 ,p_val_information13 => p_val_information13
1703 ,p_val_information14 => p_val_information14
1704 ,p_val_information15 => p_val_information15
1705 ,p_val_information16 => p_val_information16
1706 ,p_val_information17 => p_val_information17
1707 ,p_val_information18 => p_val_information18
1708 ,p_val_information19 => p_val_information19
1709 ,p_val_information20 => p_val_information20
1710 ,p_fuel_benefit => p_fuel_benefit
1711 ,p_user_info => l_user_info
1712 ,p_error_message => p_error_message
1713 );
1714
1715 IF l_chk=1 THEN
1716 l_chk:=2;
1717 GOTO update_vehicle;
1718 END IF;
1719 END IF;
1720 END IF;
1721 EXCEPTION
1722 WHEN hr_utility.hr_error THEN
1723 hr_utility.raise_error;
1724 WHEN OTHERS THEN
1725 RAISE; -- Raise error here relevant to the new tech stack.
1726 END;
1727
1728 --This procedure is called to validate the incomming data
1729 --before inserting into the transaction table.
1730 PROCEDURE val_create_vehicle_details
1731 (
1732 p_validate in boolean default false
1733 ,p_effective_date in date
1734 ,p_registration_number in varchar2
1735 ,p_vehicle_type in varchar2
1736 ,p_vehicle_id_number in varchar2
1737 ,p_business_group_id in number
1738 ,p_make in varchar2
1739 ,p_engine_capacity_in_cc in number
1740 ,p_fuel_type in varchar2
1741 ,p_currency_code in varchar2
1742 ,p_model in varchar2
1743 ,p_initial_registration in date
1744 ,p_last_registration_renew_date in date
1745 ,p_fiscal_ratings in number
1746 ,p_vehicle_ownership in varchar2
1747 ,p_shared_vehicle in varchar2
1748 ,p_color in varchar2
1749 ,p_seating_capacity in number
1750 ,p_weight in number
1751 ,p_weight_uom in varchar2
1752 ,p_model_year in number
1753 ,p_insurance_number in varchar2
1754 ,p_insurance_expiry_date in date
1755 ,p_taxation_method in varchar2
1756 ,p_comments in varchar2
1757 ,p_vre_attribute_category in varchar2
1758 ,p_vre_attribute1 in varchar2
1759 ,p_vre_attribute2 in varchar2
1760 ,p_vre_attribute3 in varchar2
1761 ,p_vre_attribute4 in varchar2
1762 ,p_vre_attribute5 in varchar2
1763 ,p_vre_attribute6 in varchar2
1764 ,p_vre_attribute7 in varchar2
1765 ,p_vre_attribute8 in varchar2
1766 ,p_vre_attribute9 in varchar2
1767 ,p_vre_attribute10 in varchar2
1768 ,p_vre_attribute11 in varchar2
1769 ,p_vre_attribute12 in varchar2
1770 ,p_vre_attribute13 in varchar2
1771 ,p_vre_attribute14 in varchar2
1772 ,p_vre_attribute15 in varchar2
1773 ,p_vre_attribute16 in varchar2
1774 ,p_vre_attribute17 in varchar2
1775 ,p_vre_attribute18 in varchar2
1776 ,p_vre_attribute19 in varchar2
1777 ,p_vre_attribute20 in varchar2
1778 ,p_vre_information_category in varchar2
1779 ,p_vre_information1 in varchar2
1780 ,p_vre_information2 in varchar2
1781 ,p_vre_information3 in varchar2
1782 ,p_vre_information4 in varchar2
1783 ,p_vre_information5 in varchar2
1784 ,p_vre_information6 in varchar2
1785 ,p_vre_information7 in varchar2
1786 ,p_vre_information8 in varchar2
1787 ,p_vre_information9 in varchar2
1788 ,p_vre_information10 in varchar2
1789 ,p_vre_information11 in varchar2
1790 ,p_vre_information12 in varchar2
1791 ,p_vre_information13 in varchar2
1792 ,p_vre_information14 in varchar2
1793 ,p_vre_information15 in varchar2
1794 ,p_vre_information16 in varchar2
1795 ,p_vre_information17 in varchar2
1796 ,p_vre_information18 in varchar2
1797 ,p_vre_information19 in varchar2
1798 ,p_vre_information20 in varchar2
1799 ,p_across_assignments in varchar2
1800 ,p_usage_type in varchar2
1801 ,p_default_vehicle in varchar2
1802 ,p_fuel_card in varchar2
1803 ,p_fuel_card_number in varchar2
1804 ,p_val_attribute_category in varchar2
1805 ,p_val_attribute1 in varchar2
1806 ,p_val_attribute2 in varchar2
1807 ,p_val_attribute3 in varchar2
1808 ,p_val_attribute4 in varchar2
1809 ,p_val_attribute5 in varchar2
1810 ,p_val_attribute6 in varchar2
1811 ,p_val_attribute7 in varchar2
1812 ,p_val_attribute8 in varchar2
1813 ,p_val_attribute9 in varchar2
1814 ,p_val_attribute10 in varchar2
1815 ,p_val_attribute11 in varchar2
1816 ,p_val_attribute12 in varchar2
1817 ,p_val_attribute13 in varchar2
1818 ,p_val_attribute14 in varchar2
1819 ,p_val_attribute15 in varchar2
1820 ,p_val_attribute16 in varchar2
1821 ,p_val_attribute17 in varchar2
1822 ,p_val_attribute18 in varchar2
1823 ,p_val_attribute19 in varchar2
1824 ,p_val_attribute20 in varchar2
1825 ,p_val_information_category in varchar2
1826 ,p_val_information1 in varchar2
1827 ,p_val_information2 in varchar2
1828 ,p_val_information3 in varchar2
1829 ,p_val_information4 in varchar2
1830 ,p_val_information5 in varchar2
1831 ,p_val_information6 in varchar2
1832 ,p_val_information7 in varchar2
1833 ,p_val_information8 in varchar2
1834 ,p_val_information9 in varchar2
1835 ,p_val_information10 in varchar2
1836 ,p_val_information11 in varchar2
1837 ,p_val_information12 in varchar2
1838 ,p_val_information13 in varchar2
1839 ,p_val_information14 in varchar2
1840 ,p_val_information15 in varchar2
1841 ,p_val_information16 in varchar2
1842 ,p_val_information17 in varchar2
1843 ,p_val_information18 in varchar2
1844 ,p_val_information19 in varchar2
1845 ,p_val_information20 in varchar2
1846 ,p_fuel_benefit in varchar2
1847 ,p_user_info in t_user_info
1848 ,p_vehicle_repository_id in number
1849 ,p_vehicle_allocation_id in number
1850 ,p_object_version_number in number
1851 ,p_error_message OUT nocopy varchar2
1852 ,p_error_status OUT nocopy varchar2
1853
1854 )
1855 IS
1856 pragma autonomous_transaction;
1857 BEGIN
1858
1859 create_vehicle_details
1860 (
1861 p_validate => false
1862 ,p_effective_date =>p_effective_date
1863 ,p_registration_number =>p_registration_number
1864 ,p_vehicle_type =>p_vehicle_type
1865 ,p_vehicle_id_number =>p_vehicle_id_number
1866 ,p_business_group_id =>p_business_group_id
1867 ,p_make =>p_make
1868 ,p_engine_capacity_in_cc =>p_engine_capacity_in_cc
1869 ,p_fuel_type =>p_fuel_type
1870 ,p_currency_code =>p_currency_code
1871 ,p_model =>p_model
1872 ,p_initial_registration =>p_initial_registration
1873 ,p_last_registration_renew_date =>p_last_registration_renew_date
1874 ,p_fiscal_ratings =>p_fiscal_ratings
1875 ,p_vehicle_ownership =>p_vehicle_ownership
1876 ,p_shared_vehicle =>p_shared_vehicle
1877 ,p_color =>p_color
1878 ,p_seating_capacity =>p_seating_capacity
1879 ,p_weight =>p_weight
1880 ,p_weight_uom =>p_weight_uom
1881 ,p_model_year =>p_model_year
1882 ,p_insurance_number =>p_insurance_number
1883 ,p_insurance_expiry_date =>p_insurance_expiry_date
1884 ,p_taxation_method =>p_taxation_method
1885 ,p_comments =>p_comments
1886 ,p_vre_attribute_category =>p_vre_attribute_category
1887 ,p_vre_attribute1 =>p_vre_attribute1
1888 ,p_vre_attribute2 =>p_vre_attribute2
1889 ,p_vre_attribute3 =>p_vre_attribute3
1890 ,p_vre_attribute4 =>p_vre_attribute4
1891 ,p_vre_attribute5 =>p_vre_attribute5
1892 ,p_vre_attribute6 =>p_vre_attribute6
1893 ,p_vre_attribute7 =>p_vre_attribute7
1894 ,p_vre_attribute8 =>p_vre_attribute8
1895 ,p_vre_attribute9 =>p_vre_attribute9
1896 ,p_vre_attribute10 =>p_vre_attribute10
1897 ,p_vre_attribute11 =>p_vre_attribute11
1898 ,p_vre_attribute12 =>p_vre_attribute12
1899 ,p_vre_attribute13 =>p_vre_attribute13
1900 ,p_vre_attribute14 =>p_vre_attribute14
1901 ,p_vre_attribute15 =>p_vre_attribute15
1902 ,p_vre_attribute16 =>p_vre_attribute16
1903 ,p_vre_attribute17 =>p_vre_attribute17
1904 ,p_vre_attribute18 =>p_vre_attribute18
1905 ,p_vre_attribute19 =>p_vre_attribute19
1906 ,p_vre_attribute20 =>p_vre_attribute20
1907 ,p_vre_information_category =>p_vre_information_category
1908 ,p_vre_information1 =>p_vre_information1
1909 ,p_vre_information2 =>p_vre_information2
1910 ,p_vre_information3 =>p_vre_information3
1911 ,p_vre_information4 =>p_vre_information4
1912 ,p_vre_information5 =>p_vre_information5
1913 ,p_vre_information6 =>p_vre_information6
1914 ,p_vre_information7 =>p_vre_information7
1915 ,p_vre_information8 =>p_vre_information8
1916 ,p_vre_information9 =>p_vre_information9
1917 ,p_vre_information10 =>p_vre_information10
1918 ,p_vre_information11 =>p_vre_information11
1919 ,p_vre_information12 =>p_vre_information12
1920 ,p_vre_information13 =>p_vre_information13
1921 ,p_vre_information14 =>p_vre_information14
1922 ,p_vre_information15 =>p_vre_information15
1923 ,p_vre_information16 =>p_vre_information16
1924 ,p_vre_information17 =>p_vre_information17
1925 ,p_vre_information18 =>p_vre_information18
1926 ,p_vre_information19 =>p_vre_information19
1927 ,p_vre_information20 =>p_vre_information20
1928 ,p_across_assignments =>p_across_assignments
1929 ,p_usage_type =>p_usage_type
1930 ,p_default_vehicle =>p_default_vehicle
1931 ,p_fuel_card =>p_fuel_card
1932 ,p_fuel_card_number =>p_fuel_card_number
1933 ,p_val_attribute_category =>p_val_attribute_category
1934 ,p_val_attribute1 =>p_val_attribute1
1935 ,p_val_attribute2 =>p_val_attribute2
1936 ,p_val_attribute3 =>p_val_attribute3
1937 ,p_val_attribute4 =>p_val_attribute4
1938 ,p_val_attribute5 =>p_val_attribute5
1939 ,p_val_attribute6 =>p_val_attribute6
1940 ,p_val_attribute7 =>p_val_attribute7
1941 ,p_val_attribute8 =>p_val_attribute8
1942 ,p_val_attribute9 =>p_val_attribute9
1943 ,p_val_attribute10 =>p_val_attribute10
1944 ,p_val_attribute11 =>p_val_attribute11
1945 ,p_val_attribute12 =>p_val_attribute12
1946 ,p_val_attribute13 =>p_val_attribute13
1947 ,p_val_attribute14 =>p_val_attribute14
1948 ,p_val_attribute15 =>p_val_attribute15
1949 ,p_val_attribute16 =>p_val_attribute16
1950 ,p_val_attribute17 =>p_val_attribute17
1951 ,p_val_attribute18 =>p_val_attribute18
1952 ,p_val_attribute19 =>p_val_attribute19
1953 ,p_val_attribute20 =>p_val_attribute20
1954 ,p_val_information_category =>p_val_information_category
1955 ,p_val_information1 =>p_val_information1
1956 ,p_val_information2 =>p_val_information2
1957 ,p_val_information3 =>p_val_information3
1958 ,p_val_information4 =>p_val_information4
1959 ,p_val_information5 =>p_val_information5
1960 ,p_val_information6 =>p_val_information6
1961 ,p_val_information7 =>p_val_information7
1962 ,p_val_information8 =>p_val_information8
1963 ,p_val_information9 =>p_val_information9
1964 ,p_val_information10 =>p_val_information10
1965 ,p_val_information11 =>p_val_information11
1966 ,p_val_information12 =>p_val_information12
1967 ,p_val_information13 =>p_val_information13
1968 ,p_val_information14 =>p_val_information14
1969 ,p_val_information15 =>p_val_information15
1970 ,p_val_information16 =>p_val_information16
1971 ,p_val_information17 =>p_val_information17
1972 ,p_val_information18 =>p_val_information18
1973 ,p_val_information19 =>p_val_information19
1974 ,p_val_information20 =>p_val_information20
1975 ,p_fuel_benefit =>p_fuel_benefit
1976 ,p_user_info =>p_user_info
1977 ,p_vehicle_repository_id =>p_vehicle_repository_id
1978 ,p_vehicle_allocation_id =>p_vehicle_allocation_id
1979 ,p_object_version_number =>p_object_version_number
1980 ,p_error_message =>p_error_message
1981 ,p_error_status =>p_error_status
1982 );
1983
1984
1985 ROLLBACK;
1986 END;
1987
1988
1989 ---Not used at the moment
1990
1991 PROCEDURE delete_vehicle_details(
1992 x_p_validate IN BOOLEAN
1993 ,x_effective_date IN DATE
1994 ,x_login_person_id IN NUMBER
1995 ,x_person_id IN NUMBER
1996 ,x_assignment_id IN NUMBER
1997 ,x_business_group_id IN NUMBER
1998 ,x_item_key IN NUMBER
1999 ,x_item_type IN VARCHAR2
2000 ,x_activity_id IN NUMBER
2001 ,x_vehicle_allocation_id IN NUMBER
2002 ,x_status IN VARCHAR2
2003 ,x_transaction_id IN OUT NOCOPY NUMBER
2004 ,x_error_status OUT NOCOPY VARCHAR2
2005 )
2006
2007 IS
2008
2009
2010
2011 CURSOR c_del_values
2012 IS
2013 SELECT transaction_step_id
2014 FROM hr_api_transaction_steps hats
2015 WHERE transaction_id = x_transaction_id;
2016
2017 l_del_values c_del_values%ROWTYPE;
2018 l_transaction_id NUMBER;
2019 l_trans_tbl hr_transaction_ss.transaction_table;
2020 l_count NUMBER :=0;
2021 l_transaction_step_id NUMBER;
2022 l_api_name hr_api_transaction_steps.api_name%TYPE
2023 := 'PQP_SS_VEHICLE_TRANSACTIONS.DELETE_PROCESS_API';
2024 l_result VARCHAR2(100);
2025 l_trns_object_version_number NUMBER;
2026 l_review_proc_call VARCHAR2(30) := 'PqpVehDelReview';
2027 l_effective_date DATE := SYSDATE;
2028 l_ovn NUMBER;
2029 l_error_message VARCHAR2(80);
2030 l_error_status VARCHAR2(10);
2031 BEGIN
2032
2033 --Validate the data before inserting into
2034 -- transaction table.
2035 delete_process (
2036 p_validate => true
2037 ,p_effective_date => x_effective_date
2038 ,p_person_id => x_person_id
2039 ,p_assignment_id => x_assignment_id
2040 ,p_business_group_id => x_business_group_id
2041 ,p_vehicle_allocation_id => x_vehicle_allocation_id
2042 ,p_error_status => l_error_status
2043 );
2044
2045 l_count:=1;
2046 l_trans_tbl(l_count).param_name := 'P_LOGIN_PERSON_ID';
2047 l_trans_tbl(l_count).param_value := x_login_person_id;
2048 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2049
2050 l_count:=l_count+1;
2051 l_trans_tbl(l_count).param_name := 'P_EFFECTIVE_DATE';
2052 l_trans_tbl(l_count).param_value := x_effective_date;
2053 l_trans_tbl(l_count).param_data_type := 'DATE';
2054
2055 l_count:=l_count+1;
2056 l_trans_tbl(l_count).param_name := 'P_REVIEW_PROC_CALL';
2057 l_trans_tbl(l_count).param_value := l_review_proc_call;
2058 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2059
2060 l_count:=l_count+1;
2061 l_trans_tbl(l_count).param_name := 'P_REVIEW_ACTID';
2062 l_trans_tbl(l_count).param_value := x_activity_id;
2063 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2064
2065 l_count:=l_count+1;
2066 l_trans_tbl(l_count).param_name := 'P_PERSON_ID';
2067 l_trans_tbl(l_count).param_value := x_person_id;
2068 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2069
2070 l_count:=l_count+1;
2071 l_trans_tbl(l_count).param_name := 'P_ASSIGNMENT_ID';
2072 l_trans_tbl(l_count).param_value := x_ASSIGNMENT_id;
2073 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2074
2075 l_count:=l_count+1;
2076 l_trans_tbl(l_count).param_name := 'P_BUSINESS_GROUP_ID';
2077 l_trans_tbl(l_count).param_value := x_business_group_id;
2078 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2079
2080 l_count:=l_count+1;
2081 l_trans_tbl(l_count).param_name := 'P_ALLOCATION_ID';
2082 l_trans_tbl(l_count).param_value := x_vehicle_allocation_id;
2083 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2084
2085 IF x_transaction_id is NULl THEN
2086 hr_transaction_api.create_transaction(
2087 p_validate => false
2088 ,p_creator_person_id => x_login_person_id
2089 ,p_transaction_privilege => 'PRIVATE'
2090 ,p_product_code => 'PQP'
2091 ,p_url => NULL
2092 ,p_status => x_status
2093 ,p_section_display_name => NULL
2094 ,p_function_id => NULL
2095 ,p_transaction_ref_table => NULL
2096 ,p_transaction_ref_id => NULL
2097 ,p_transaction_type => 'WF'
2098 ,p_assignment_id => x_assignment_id
2099 ,p_selected_person_id => x_person_id
2100 ,p_item_type => x_item_type
2101 ,p_item_key => x_item_key
2102 ,p_transaction_effective_date => x_effective_date
2103 ,p_process_name => NULL
2104 ,p_plan_id => NULL
2105 ,p_rptg_grp_id => NULL
2106 ,p_effective_date_option => x_effective_date
2107 ,p_transaction_id => l_transaction_id
2108 );
2109 wf_engine.setitemattrnumber
2110 (itemtype => x_item_type
2111 ,itemkey => x_item_key
2112 ,aname => 'TRANSACTION_ID'
2113 ,avalue => l_transaction_id);
2114 x_transaction_id := l_transaction_id;
2115 --Create transaction steps
2116 hr_transaction_api.create_transaction_step
2117 (p_validate => false
2118 ,p_creator_person_id => x_login_person_id
2119 ,p_transaction_id => l_transaction_id
2120 ,p_api_name => l_api_name
2121 ,p_api_display_name => l_api_name
2122 ,p_item_type => x_item_type
2123 ,p_item_key => x_item_key
2124 ,p_activity_id => x_activity_id
2125 ,p_transaction_step_id => l_transaction_step_id
2126 ,p_object_version_number => l_ovn
2127 );
2128 ELSE
2129
2130 OPEN c_del_values;
2131 FETCH c_del_values INTO l_del_values;
2132 CLOSE c_del_values;
2133
2134 DELETE from hr_api_transaction_values
2135 WHERE transaction_step_id = l_del_values.transaction_step_id;
2136 l_transaction_step_id := l_del_values.transaction_step_id;
2137 END IF;
2138
2139
2140 FOR i in 1..l_trans_tbl.count
2141 LOOP
2142 IF l_trans_tbl(i).param_data_type ='VARCHAR2' THEN
2143 hr_transaction_api.set_varchar2_value
2144 (p_transaction_step_id => l_transaction_step_id
2145 ,p_person_id => x_person_id
2146 ,p_name => l_trans_tbl (i).param_name
2147 ,p_value => l_trans_tbl (i).param_value
2148 );
2149
2150 ELSIF l_trans_tbl(i).param_data_type ='DATE' THEN
2151 hr_transaction_api.set_date_value
2152 (
2153 p_transaction_step_id => l_transaction_step_id
2154 ,p_person_id => x_person_id
2155 ,p_name => l_trans_tbl (i).param_name
2156 ,p_value => fnd_date.displaydate_to_date
2157 (l_trans_tbl (i).param_value ) );
2158 -- ,p_original_value );
2159
2160
2161 ELSIF l_trans_tbl(i).param_data_type ='NUMBER' THEN
2162 hr_transaction_api.set_number_value
2163 (
2164 p_transaction_step_id => l_transaction_step_id
2165 ,p_person_id => x_person_id
2166 ,p_name =>l_trans_tbl (i).param_name
2167 ,p_value =>TO_NUMBER(l_trans_tbl (i).param_value ));
2168 END IF;
2169 END LOOP;
2170
2171 EXCEPTION
2172 WHEN hr_utility.hr_error THEN
2173 hr_utility.raise_error;
2174 WHEN OTHERS THEN
2175 RAISE; -- Raise error here relevant to the new tech stack.
2176
2177 END;
2178
2179 PROCEDURE set_vehicle_details (
2180 x_p_validate IN BOOLEAN
2181 ,x_effective_date IN DATE
2182 ,x_login_person_id IN NUMBER
2183 ,x_person_id IN NUMBER
2184 ,x_assignment_id IN NUMBER
2185 ,x_item_type IN VARCHAR2
2186 ,x_item_key IN NUMBER
2187 ,x_activity_id IN NUMBER
2188 ,x_registration_number IN VARCHAR2
2189 ,x_vehicle_ownership IN VARCHAR2
2190 ,x_vehicle_type IN VARCHAR2
2191 ,x_vehicle_id_number IN VARCHAR2
2192 ,x_business_group_id IN NUMBER
2193 ,x_make IN VARCHAR2
2194 ,x_engine_capacity_in_cc IN NUMBER
2195 ,x_fuel_type IN VARCHAR2
2196 ,x_currency_code IN VARCHAR2
2197 ,x_model IN VARCHAR2
2198 ,x_initial_registration IN DATE
2199 ,x_last_registration_renew_date IN DATE
2200 ,x_fiscal_ratings IN NUMBER
2201 ,x_shared_vehicle IN VARCHAR2
2202 ,x_color IN VARCHAR2
2203 ,x_seating_capacity IN NUMBER
2204 ,x_weight IN NUMBER
2205 ,x_weight_uom IN VARCHAR2
2206 ,x_model_year IN NUMBER
2207 ,x_insurance_number IN VARCHAR2
2208 ,x_insurance_expiry_date IN DATE
2209 ,x_taxation_method IN VARCHAR2
2210 ,x_comments IN VARCHAR2
2211 ,x_vre_attribute_category IN VARCHAR2
2212 ,x_vre_attribute1 IN VARCHAR2
2213 ,x_vre_attribute2 IN VARCHAR2
2214 ,x_vre_attribute3 IN VARCHAR2
2215 ,x_vre_attribute4 IN VARCHAR2
2216 ,x_vre_attribute5 IN VARCHAR2
2217 ,x_vre_attribute6 IN VARCHAR2
2218 ,x_vre_attribute7 IN VARCHAR2
2219 ,x_vre_attribute8 IN VARCHAR2
2220 ,x_vre_attribute9 IN VARCHAR2
2221 ,x_vre_attribute10 IN VARCHAR2
2222 ,x_vre_attribute11 IN VARCHAR2
2223 ,x_vre_attribute12 IN VARCHAR2
2224 ,x_vre_attribute13 IN VARCHAR2
2225 ,x_vre_attribute14 IN VARCHAR2
2226 ,x_vre_attribute15 IN VARCHAR2
2227 ,x_vre_attribute16 IN VARCHAR2
2228 ,x_vre_attribute17 IN VARCHAR2
2229 ,x_vre_attribute18 IN VARCHAR2
2230 ,x_vre_attribute19 IN VARCHAR2
2231 ,x_vre_attribute20 IN VARCHAR2
2232 ,x_vre_information_category IN VARCHAR2
2233 ,x_vre_information1 IN VARCHAR2
2234 ,x_vre_information2 IN VARCHAR2
2235 ,x_vre_information3 IN VARCHAR2
2236 ,x_vre_information4 IN VARCHAR2
2237 ,x_vre_information5 IN VARCHAR2
2238 ,x_vre_information6 IN VARCHAR2
2239 ,x_vre_information7 IN VARCHAR2
2240 ,x_vre_information8 IN VARCHAR2
2241 ,x_vre_information9 IN VARCHAR2
2242 ,x_vre_information10 IN VARCHAR2
2243 ,x_vre_information11 IN VARCHAR2
2244 ,x_vre_information12 IN VARCHAR2
2245 ,x_vre_information13 IN VARCHAR2
2246 ,x_vre_information14 IN VARCHAR2
2247 ,x_vre_information15 IN VARCHAR2
2248 ,x_vre_information16 IN VARCHAR2
2249 ,x_vre_information17 IN VARCHAR2
2250 ,x_vre_information18 IN VARCHAR2
2251 ,x_vre_information19 IN VARCHAR2
2252 ,x_vre_information20 IN VARCHAR2
2253 ,x_across_assignments IN VARCHAR2
2254 ,x_usage_type IN VARCHAR2
2255 ,x_default_vehicle IN VARCHAR2
2256 ,x_fuel_card IN VARCHAR2
2257 ,x_fuel_card_number IN VARCHAR2
2258 ,x_val_attribute_category IN VARCHAR2
2259 ,x_val_attribute1 IN VARCHAR2
2260 ,x_val_attribute2 IN VARCHAR2
2261 ,x_val_attribute3 IN VARCHAR2
2262 ,x_val_attribute4 IN VARCHAR2
2263 ,x_val_attribute5 IN VARCHAR2
2264 ,x_val_attribute6 IN VARCHAR2
2265 ,x_val_attribute7 IN VARCHAR2
2266 ,x_val_attribute8 IN VARCHAR2
2267 ,x_val_attribute9 IN VARCHAR2
2268 ,x_val_attribute10 IN VARCHAR2
2269 ,x_val_attribute11 IN VARCHAR2
2270 ,x_val_attribute12 IN VARCHAR2
2271 ,x_val_attribute13 IN VARCHAR2
2272 ,x_val_attribute14 IN VARCHAR2
2273 ,x_val_attribute15 IN VARCHAR2
2274 ,x_val_attribute16 IN VARCHAR2
2275 ,x_val_attribute17 IN VARCHAR2
2276 ,x_val_attribute18 IN VARCHAR2
2277 ,x_val_attribute19 IN VARCHAR2
2278 ,x_val_attribute20 IN VARCHAR2
2279 ,x_val_information_category IN VARCHAR2
2280 ,x_val_information1 IN VARCHAR2
2281 ,x_val_information2 IN VARCHAR2
2282 ,x_val_information3 IN VARCHAR2
2283 ,x_val_information4 IN VARCHAR2
2284 ,x_val_information5 IN VARCHAR2
2285 ,x_val_information6 IN VARCHAR2
2286 ,x_val_information7 IN VARCHAR2
2287 ,x_val_information8 IN VARCHAR2
2288 ,x_val_information9 IN VARCHAR2
2289 ,x_val_information10 IN VARCHAR2
2290 ,x_val_information11 IN VARCHAR2
2291 ,x_val_information12 IN VARCHAR2
2292 ,x_val_information13 IN VARCHAR2
2293 ,x_val_information14 IN VARCHAR2
2294 ,x_val_information15 IN VARCHAR2
2295 ,x_val_information16 IN VARCHAR2
2296 ,x_val_information17 IN VARCHAR2
2297 ,x_val_information18 IN VARCHAR2
2298 ,x_val_information19 IN VARCHAR2
2299 ,x_val_information20 IN VARCHAR2
2300 ,x_fuel_benefit IN VARCHAR2
2301 ,x_user_info IN t_user_info
2302 ,x_status IN VARCHAR2
2303 ,x_effective_date_option IN VARCHAR2
2304 ,x_vehicle_repository_id IN NUMBER
2305 ,x_vehicle_allocation_id IN NUMBER
2306 ,x_object_version_number in NUMBER
2307 ,x_error_status OUT NOCOPY VARCHAR2
2308 ,x_transaction_id IN OUT NOCOPY NUMBER
2309 )
2310 IS
2311 CURSOR c_del_values
2312 IS
2313 SELECT transaction_step_id
2314 FROM hr_api_transaction_steps hats
2315 WHERE transaction_id = x_transaction_id;
2316
2317 l_del_values c_del_values%ROWTYPE;
2318 l_transaction_id NUMBER;
2319 l_trans_tbl hr_transaction_ss.transaction_table;
2320 l_count NUMBER :=0;
2321 l_transaction_step_id NUMBER;
2322 l_api_name hr_api_transaction_steps.api_name%TYPE
2323 := 'PQP_SS_VEHICLE_TRANSACTIONS.PROCESS_API';
2324 l_result VARCHAR2(100);
2325 l_trns_object_version_number number;
2326 l_review_proc_call VARCHAR2(30) := 'PqpVehInfoReview';
2327 l_effective_date DATE := SYSDATE;
2328 l_ovn NUMBER;
2329 l_error_message VARCHAR2(80);
2330 l_error_status VARCHAR2(10);
2331 u_count NUMBER;
2332 l_sec_result VARCHAR2(100);
2333 BEGIN
2334 hr_utility.set_location('Enter:Set Vehicle Details' ,5);
2335 SAVEPOINT pqp_vehicle_proc_start;
2336 BEGIN
2337 hr_utility.set_location('Enter:Set Paramaters' ,10);
2338 SAVEPOINT pqp_vehicle_validate;
2339 hr_multi_message.enable_message_list;
2340 hr_utility.set_location('Entering: enter set vehicle details',5);
2341 l_count:=l_count+1;
2342 l_trans_tbl(l_count).param_name := 'P_LOGIN_PERSON_ID';
2343 l_trans_tbl(l_count).param_value := x_login_person_id;
2344 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2345
2346 l_count:=l_count+1;
2347 l_trans_tbl(l_count).param_name := 'P_REVIEW_PROC_CALL';
2348 l_trans_tbl(l_count).param_value := l_review_proc_call;
2349 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2350
2351 l_count:=l_count+1;
2352 l_trans_tbl(l_count).param_name := 'P_REVIEW_ACTID';
2353 l_trans_tbl(l_count).param_value := x_activity_id;
2354 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2355
2356 l_count:=l_count+1;
2357 l_trans_tbl(l_count).param_name := 'P_EFFECTIVE_DATE';
2358 l_trans_tbl(l_count).param_value := x_effective_date;
2359 l_trans_tbl(l_count).param_data_type := 'DATE';
2360
2361 l_count:=l_count+1;
2362 l_trans_tbl(l_count).param_name := 'P_PERSON_ID';
2363 l_trans_tbl(l_count).param_value := x_person_id;
2364 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2365
2366 l_count:=l_count+1;
2367 l_trans_tbl(l_count).param_name := 'P_ASSIGNMENT_ID';
2368 l_trans_tbl(l_count).param_value := x_ASSIGNMENT_id;
2369 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2370
2371 l_count:=l_count+1;
2372 l_trans_tbl(l_count).param_name := 'P_REGISTRATION_NUMBER';
2373 l_trans_tbl(l_count).param_value := x_registration_number;
2374 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2375
2376 l_count:=l_count+1;
2377 l_trans_tbl(l_count).param_name := 'P_VEHICLE_OWNERSHIP';
2378 l_trans_tbl(l_count).param_value := x_vehicle_ownership;
2379 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2380
2381 l_count:=l_count+1;
2382 l_trans_tbl(l_count).param_name := 'P_VEHICLE_TYPE';
2383 l_trans_tbl(l_count).param_value := x_vehicle_type;
2384 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2385
2386
2387 l_count:=l_count+1;
2388 l_trans_tbl(l_count).param_name := 'P_VEHICLE_ID_NUMBER';
2389 l_trans_tbl(l_count).param_value := x_vehicle_id_number;
2390 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2391
2392
2393 l_count:=l_count+1;
2394 l_trans_tbl(l_count).param_name := 'P_BUSINESS_GROUP_ID';
2395 l_trans_tbl(l_count).param_value := x_business_group_id;
2396 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2397
2398
2399 l_count:=l_count+1;
2400 l_trans_tbl(l_count).param_name := 'P_MAKE';
2401 l_trans_tbl(l_count).param_value := x_make;
2402 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2403
2404
2405 l_count:=l_count+1;
2406 l_trans_tbl(l_count).param_name := 'P_ENGINE_CAPACITY_IN_CC';
2407 l_trans_tbl(l_count).param_value := x_engine_capacity_in_cc;
2408 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2409
2410 l_count:=l_count+1;
2411 l_trans_tbl(l_count).param_name := 'P_FUEL_TYPE';
2412 l_trans_tbl(l_count).param_value := x_fuel_type;
2413 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2414
2415
2416 l_count:=l_count+1;
2417 l_trans_tbl(l_count).param_name := 'P_CURRENCY_CODE';
2418 l_trans_tbl(l_count).param_value := x_currency_code;
2419 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2420
2421
2422 l_count:=l_count+1;
2423 l_trans_tbl(l_count).param_name := 'P_MODEL';
2424 l_trans_tbl(l_count).param_value := x_model;
2425 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2426
2427
2428 l_count:=l_count+1;
2429 l_trans_tbl(l_count).param_name := 'P_INITIAL_REGISTRATION';
2430 l_trans_tbl(l_count).param_value := x_initial_registration;
2431 l_trans_tbl(l_count).param_data_type := 'DATE';
2432
2433
2434 l_count:=l_count+1;
2435 l_trans_tbl(l_count).param_name := 'P_LAST_REGISTRATION_RENEW_DATE';
2436 l_trans_tbl(l_count).param_value := x_last_registration_renew_date;
2437 l_trans_tbl(l_count).param_data_type := 'DATE';
2438
2439
2440 l_count:=l_count+1;
2441 l_trans_tbl(l_count).param_name := 'P_FISCAL_RATINGS';
2442 l_trans_tbl(l_count).param_value := x_fiscal_ratings;
2443 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2444
2445
2446 l_count:=l_count+1;
2447 l_trans_tbl(l_count).param_name := 'P_SHARED_VEHICLE';
2448 l_trans_tbl(l_count).param_value := x_shared_vehicle;
2449 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2450
2451
2452 l_count:=l_count+1;
2453 l_trans_tbl(l_count).param_name := 'P_COLOR';
2454 l_trans_tbl(l_count).param_value := x_color;
2455 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2456
2457
2458 l_count:=l_count+1;
2459 l_trans_tbl(l_count).param_name := 'P_SEATING_CAPACITY';
2460 l_trans_tbl(l_count).param_value := x_seating_capacity;
2461 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2462
2463
2464
2465 l_count:=l_count+1;
2466 l_trans_tbl(l_count).param_name := 'P_WEIGHT';
2467 l_trans_tbl(l_count).param_value := x_weight;
2468 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2469
2470
2471 l_count:=l_count+1;
2472 l_trans_tbl(l_count).param_name := 'P_WEIGHT_UOM';
2473 l_trans_tbl(l_count).param_value := x_weight_uom;
2474 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2475
2476
2477 l_count:=l_count+1;
2478 l_trans_tbl(l_count).param_name := 'P_MODEL_YEAR';
2479 l_trans_tbl(l_count).param_value := x_model_year;
2480 l_trans_tbl(l_count).param_data_type := 'NUMBER';
2481
2482 l_count:=l_count+1;
2483 l_trans_tbl(l_count).param_name := 'P_INSURANCE_NUMBER';
2484 l_trans_tbl(l_count).param_value := x_insurance_number;
2485 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2486
2487 l_count:=l_count+1;
2488 l_trans_tbl(l_count).param_name := 'P_INSURANCE_EXPIRY_DATE';
2489 l_trans_tbl(l_count).param_value := x_insurance_expiry_date;
2490 l_trans_tbl(l_count).param_data_type := 'DATE';
2491
2492 l_count:=l_count+1;
2493 l_trans_tbl(l_count).param_name := 'P_TAXATION_METHOD';
2494 l_trans_tbl(l_count).param_value := x_taxation_method;
2495 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2496
2497 l_count:=l_count+1;
2498 l_trans_tbl(l_count).param_name := 'P_COMMENTS';
2499 l_trans_tbl(l_count).param_value := x_comments;
2500 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2501
2502
2503 l_count:=l_count+1;
2504 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE_CATEGORY';
2505 l_trans_tbl(l_count).param_value := x_vre_attribute_category;
2506 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2507
2508
2509 l_count:=l_count+1;
2510 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE1';
2511 l_trans_tbl(l_count).param_value := x_vre_attribute1;
2512 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2513
2514 l_count:=l_count+1;
2515 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE2';
2516 l_trans_tbl(l_count).param_value := x_vre_attribute2;
2517 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2518
2519
2520 l_count:=l_count+1;
2521 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE3';
2522 l_trans_tbl(l_count).param_value := x_vre_attribute3;
2523 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2524
2525
2526 l_count:=l_count+1;
2527 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE4';
2528 l_trans_tbl(l_count).param_value := x_vre_attribute4;
2529 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2530
2531
2532 l_count:=l_count+1;
2533 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE5';
2534 l_trans_tbl(l_count).param_value := x_vre_attribute5;
2535 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2536
2537
2538
2539 l_count:=l_count+1;
2540 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE6';
2541 l_trans_tbl(l_count).param_value := x_vre_attribute6;
2542 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2543
2544
2545 l_count:=l_count+1;
2546 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE7';
2547 l_trans_tbl(l_count).param_value := x_vre_attribute7;
2548 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2549
2550
2551 l_count:=l_count+1;
2552 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE8';
2553 l_trans_tbl(l_count).param_value := x_vre_attribute8;
2554 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2555
2556
2557 l_count:=l_count+1;
2558 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE9';
2559 l_trans_tbl(l_count).param_value := x_vre_attribute9;
2560 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2561
2562
2563 l_count:=l_count+1;
2564 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE10';
2565 l_trans_tbl(l_count).param_value := x_vre_attribute10;
2566 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2567
2568
2569 l_count:=l_count+1;
2570 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE11';
2571 l_trans_tbl(l_count).param_value := x_vre_attribute11;
2572 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2573
2574
2575 l_count:=l_count+1;
2576 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE12';
2577 l_trans_tbl(l_count).param_value := x_vre_attribute12;
2578 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2579
2580
2581 l_count:=l_count+1;
2582 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE13';
2583 l_trans_tbl(l_count).param_value := x_vre_attribute13;
2584 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2585
2586
2587 l_count:=l_count+1;
2588 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE14';
2589 l_trans_tbl(l_count).param_value := x_vre_attribute14;
2590 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2591
2592 l_count:=l_count+1;
2593 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE15';
2594 l_trans_tbl(l_count).param_value := x_vre_attribute15;
2595 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2596
2597
2598 l_count:=l_count+1;
2599 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE16';
2600 l_trans_tbl(l_count).param_value := x_vre_attribute16;
2601 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2602
2603
2604 l_count:=l_count+1;
2605 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE17';
2606 l_trans_tbl(l_count).param_value := x_vre_attribute17;
2607 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2608
2609
2610 l_count:=l_count+1;
2611 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE18';
2612 l_trans_tbl(l_count).param_value := x_vre_attribute18;
2613 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2614
2615
2616 l_count:=l_count+1;
2617 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE19';
2618 l_trans_tbl(l_count).param_value := x_vre_attribute19;
2619 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2620
2621 l_count:=l_count+1;
2622 l_trans_tbl(l_count).param_name := 'P_VRE_ATTRIBUTE20';
2623 l_trans_tbl(l_count).param_value := x_vre_attribute20;
2624 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2625
2626
2627 l_count:=l_count+1;
2628 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION_CATEGORY';
2629 l_trans_tbl(l_count).param_value := x_vre_information_category;
2630 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2631
2632
2633 l_count:=l_count+1;
2634 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION1';
2635 l_trans_tbl(l_count).param_value := x_vre_information1;
2636 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2637
2638
2639 l_count:=l_count+1;
2640 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION2';
2641 l_trans_tbl(l_count).param_value := x_vre_information2;
2642 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2643
2644 l_count:=l_count+1;
2645 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION3';
2646 l_trans_tbl(l_count).param_value := x_vre_information3;
2647 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2648
2649
2650 l_count:=l_count+1;
2651 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION4';
2652 l_trans_tbl(l_count).param_value := x_vre_information4;
2653 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2654
2655 l_count:=l_count+1;
2656 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION5';
2657 l_trans_tbl(l_count).param_value := x_vre_information5;
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_INFORMATION6';
2663 l_trans_tbl(l_count).param_value := x_vre_information6;
2664 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2665
2666
2667 l_count:=l_count+1;
2668 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION7';
2669 l_trans_tbl(l_count).param_value := x_vre_information7;
2670 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2671
2672 l_count:=l_count+1;
2673 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION8';
2674 l_trans_tbl(l_count).param_value := x_vre_information8;
2675 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2676
2677
2678 l_count:=l_count+1;
2679 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION9';
2680 l_trans_tbl(l_count).param_value := x_vre_information9;
2681 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2682
2683 l_count:=l_count+1;
2684 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION10';
2685 l_trans_tbl(l_count).param_value := x_vre_information10;
2686 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2687
2688 l_count:=l_count+1;
2689 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION11';
2690 l_trans_tbl(l_count).param_value := x_vre_information11;
2691 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2692
2693 l_count:=l_count+1;
2694 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION12';
2695 l_trans_tbl(l_count).param_value := x_vre_information12;
2696 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2697
2698
2699 l_count:=l_count+1;
2700 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION13';
2701 l_trans_tbl(l_count).param_value := x_vre_information13;
2702 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2703
2704 l_count:=l_count+1;
2705 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION14';
2706 l_trans_tbl(l_count).param_value := x_vre_information14;
2707 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2708
2709 l_count:=l_count+1;
2710 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION15';
2711 l_trans_tbl(l_count).param_value := x_vre_information15;
2712 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2713
2714
2715 l_count:=l_count+1;
2716 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION16';
2717 l_trans_tbl(l_count).param_value := x_vre_information16;
2718 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2719
2720 l_count:=l_count+1;
2721 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION17';
2722 l_trans_tbl(l_count).param_value := x_vre_information17;
2723 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2724
2725
2726 l_count:=l_count+1;
2727 l_trans_tbl(l_count).param_name := 'P_VRE_INFORMATION18';
2728 l_trans_tbl(l_count).param_value := x_vre_information18;
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_INFORMATION19';
2733 l_trans_tbl(l_count).param_value := x_vre_information19;
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_INFORMATION20';
2739 l_trans_tbl(l_count).param_value := x_vre_information20;
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_ACROSS_ASSIGNMENTS';
2744 l_trans_tbl(l_count).param_value := x_across_assignments;
2745 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2746
2747 l_count:=l_count+1;
2748 l_trans_tbl(l_count).param_name := 'P_USAGE_TYPE';
2749 l_trans_tbl(l_count).param_value := x_usage_type;
2750 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2751
2752 l_count:=l_count+1;
2753 l_trans_tbl(l_count).param_name := 'P_DEFAULT_VEHICLE';
2754 l_trans_tbl(l_count).param_value := x_default_vehicle;
2755 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2756
2757
2758 l_count:=l_count+1;
2759 l_trans_tbl(l_count).param_name := 'P_FUEL_CARD';
2760 l_trans_tbl(l_count).param_value := x_fuel_card;
2761 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2762
2763 l_count:=l_count+1;
2764 l_trans_tbl(l_count).param_name := 'P_FUEL_CARD_NUMBER';
2765 l_trans_tbl(l_count).param_value := x_fuel_card_number;
2766 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2767
2768
2769 l_count:=l_count+1;
2770 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE_CATEGORY';
2771 l_trans_tbl(l_count).param_value := x_val_attribute_category;
2772 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2773
2774
2775 l_count:=l_count+1;
2776 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE1';
2777 l_trans_tbl(l_count).param_value := x_val_attribute1;
2778 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2779
2780
2781 l_count:=l_count+1;
2782 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE2';
2783 l_trans_tbl(l_count).param_value := x_val_attribute2;
2784 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2785
2786
2787 l_count:=l_count+1;
2788 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE3';
2789 l_trans_tbl(l_count).param_value := x_val_attribute3;
2790 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2791
2792
2793 l_count:=l_count+1;
2794 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE4';
2795 l_trans_tbl(l_count).param_value := x_val_attribute4;
2796 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2797
2798
2799 l_count:=l_count+1;
2800 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE5';
2801 l_trans_tbl(l_count).param_value := x_val_attribute5;
2802 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2803
2804
2805 l_count:=l_count+1;
2806 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE6';
2807 l_trans_tbl(l_count).param_value := x_val_attribute6;
2808 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2809
2810
2811 l_count:=l_count+1;
2812 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE7';
2813 l_trans_tbl(l_count).param_value := x_val_attribute7;
2814 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2815
2816
2817 l_count:=l_count+1;
2818 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE8';
2819 l_trans_tbl(l_count).param_value := x_val_attribute8;
2820 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2821
2822 l_count:=l_count+1;
2823 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE9';
2824 l_trans_tbl(l_count).param_value := x_val_attribute9;
2825 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2826
2827
2828 l_count:=l_count+1;
2829 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE10';
2830 l_trans_tbl(l_count).param_value := x_val_attribute10;
2831 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2832
2833
2834
2835 l_count:=l_count+1;
2836 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE11';
2837 l_trans_tbl(l_count).param_value := x_val_attribute11;
2838 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2839
2840
2841 l_count:=l_count+1;
2842 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE12';
2843 l_trans_tbl(l_count).param_value := x_val_attribute12;
2844 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2845
2846
2847 l_count:=l_count+1;
2848 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE13';
2849 l_trans_tbl(l_count).param_value := x_val_attribute13;
2850 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2851
2852
2853 l_count:=l_count+1;
2854 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE14';
2855 l_trans_tbl(l_count).param_value := x_val_attribute14;
2856 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2857
2858
2859 l_count:=l_count+1;
2860 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE15';
2861 l_trans_tbl(l_count).param_value := x_val_attribute15;
2862 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2863
2864
2865 l_count:=l_count+1;
2866 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE16';
2867 l_trans_tbl(l_count).param_value := x_val_attribute16;
2868 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2869
2870
2871 l_count:=l_count+1;
2872 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE17';
2873 l_trans_tbl(l_count).param_value := x_val_attribute17;
2874 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2875
2876
2877 l_count:=l_count+1;
2878 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE18';
2879 l_trans_tbl(l_count).param_value := x_val_attribute18;
2880 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2881
2882 l_count:=l_count+1;
2883 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE19';
2884 l_trans_tbl(l_count).param_value := x_val_attribute19;
2885 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2886
2887 l_count:=l_count+1;
2888 l_trans_tbl(l_count).param_name := 'P_VAL_ATTRIBUTE20';
2889 l_trans_tbl(l_count).param_value := x_val_attribute20;
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_INFORMATION_CATEGORY';
2895 l_trans_tbl(l_count).param_value := x_val_INFORMATION_category;
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_INFORMATION1';
2901 l_trans_tbl(l_count).param_value := x_val_INFORMATION1;
2902 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2903
2904
2905 l_count:=l_count+1;
2906 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION2';
2907 l_trans_tbl(l_count).param_value := x_val_INFORMATION2;
2908 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2909
2910
2911 l_count:=l_count+1;
2912 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION3';
2913 l_trans_tbl(l_count).param_value := x_val_INFORMATION3;
2914 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2915
2916
2917 l_count:=l_count+1;
2918 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION4';
2919 l_trans_tbl(l_count).param_value := x_val_INFORMATION4;
2920 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2921
2922
2923 l_count:=l_count+1;
2924 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION5';
2925 l_trans_tbl(l_count).param_value := x_val_INFORMATION5;
2926 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2927
2928
2929 l_count:=l_count+1;
2930 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION6';
2931 l_trans_tbl(l_count).param_value := x_val_INFORMATION6;
2932 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2933
2934
2935 l_count:=l_count+1;
2936 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION7';
2937 l_trans_tbl(l_count).param_value := x_val_INFORMATION7;
2938 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2939
2940
2941 l_count:=l_count+1;
2942 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION8';
2943 l_trans_tbl(l_count).param_value := x_val_INFORMATION8;
2944 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2945
2946 l_count:=l_count+1;
2947 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION9';
2948 l_trans_tbl(l_count).param_value := x_val_INFORMATION9;
2949 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2950
2951
2952 l_count:=l_count+1;
2953 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION10';
2954 l_trans_tbl(l_count).param_value := x_val_INFORMATION10;
2955 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2956
2957 l_count:=l_count+1;
2958 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION11';
2959 l_trans_tbl(l_count).param_value := x_val_INFORMATION11;
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_INFORMATION12';
2965 l_trans_tbl(l_count).param_value := x_val_INFORMATION12;
2966 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2967
2968
2969 l_count:=l_count+1;
2970 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION13';
2971 l_trans_tbl(l_count).param_value := x_val_INFORMATION13;
2972 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2973
2974
2975 l_count:=l_count+1;
2976 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION14';
2977 l_trans_tbl(l_count).param_value := x_val_INFORMATION14;
2978 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2979
2980
2981 l_count:=l_count+1;
2982 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION15';
2983 l_trans_tbl(l_count).param_value := x_val_INFORMATION15;
2984 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2985
2986
2987 l_count:=l_count+1;
2988 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION16';
2989 l_trans_tbl(l_count).param_value := x_val_INFORMATION16;
2990 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2991
2992
2993 l_count:=l_count+1;
2994 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION17';
2995 l_trans_tbl(l_count).param_value := x_val_INFORMATION17;
2996 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2997
2998 l_count:=l_count+1;
2999 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION18';
3000 l_trans_tbl(l_count).param_value := x_val_INFORMATION18;
3001 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
3002
3003 l_count:=l_count+1;
3004 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION19';
3005 l_trans_tbl(l_count).param_value := x_val_INFORMATION19;
3006 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
3007
3008 l_count:=l_count+1;
3009 l_trans_tbl(l_count).param_name := 'P_VAL_INFORMATION20';
3010 l_trans_tbl(l_count).param_value := x_val_INFORMATION20;
3011 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
3012
3013 l_count:=l_count+1;
3014 l_trans_tbl(l_count).param_name := 'P_FUEL_BENEFIT';
3015 l_trans_tbl(l_count).param_value := x_fuel_benefit;
3016 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
3017
3018
3019 l_count:=l_count+1;
3020 l_trans_tbl(l_count).param_name := 'P_OBJECT_VERSION_NUMBER';
3021 l_trans_tbl(l_count).param_value := x_object_version_number;
3022 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
3023 --
3024 hr_utility.set_location('Entering: Second',5);
3025 l_count:=l_count+1;
3026 l_trans_tbl(l_count).param_name := 'P_REVIEW_PROC_CALL';
3027 l_trans_tbl(l_count).param_value := l_review_proc_call;
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_REVIEW_ACTID';
3032 l_trans_tbl(l_count).param_value := x_activity_id;
3033 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
3034 --
3035
3036 l_count:=l_count+1;
3037 l_trans_tbl(l_count).param_name := 'P_VEHICLE_REPOSITORY_ID';
3038 l_trans_tbl(l_count).param_value := x_vehicle_repository_id;
3039 l_trans_tbl(l_count).param_data_type := 'NUMBER';
3040 hr_utility.set_location('Entering: Set p_vehicle_allocation_id',5);
3041 l_count:=l_count+1;
3042 l_trans_tbl(l_count).param_name := 'P_VEHICLE_ALLOCATION_ID';
3043 l_trans_tbl(l_count).param_value := x_vehicle_allocation_id;
3044 l_trans_tbl(l_count).param_data_type := 'NUMBER';
3045 hr_utility.set_location('Leaving:Set Paramaters' ,15);
3046
3047 u_count:=x_user_info.count;
3048 FOR i in 1..x_user_info.count
3049 LOOP
3050 hr_utility.set_location('Entering: userinfo',5);
3051 l_count:=l_count+1;
3052 hr_utility.set_location('Entering: enter loop',10);
3053 l_trans_tbl(l_count).param_name := 'P_PERSON_USR_ID'||i;
3054 l_trans_tbl(l_count).param_value := x_user_info(i).person_id;
3055 l_trans_tbl(l_count).param_data_type := 'NUMBER';
3056 l_count:=l_count+1;
3057 l_trans_tbl(l_count).param_name := 'P_ASSIGNMENT_USR_ID'||i;
3058 l_trans_tbl(l_count).param_value := x_user_info(i).assignment_id;
3059 l_trans_tbl(l_count).param_data_type := 'NUMBER';
3060 l_count:=l_count+1;
3061 l_trans_tbl(l_count).param_name := 'P_USER_TYPE'||i;
3062 l_trans_tbl(l_count).param_value := x_user_info(i).user_type;
3063 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
3064 hr_utility.set_location('Leaving: userinfo',10);
3065 END LOOP;
3066 ---Validate the incomming values against acual table.
3067 --The validation is done in false mode and then
3068 --rolled back using autonomous transaction.The resaon for going
3069 --in for Validate false mode is due to the fact that
3070 -- the when two apis are called where some of the condition in
3071 --first api could affect the validation of the second api. Validate True mode
3072 --would rollback once the transaction is completed and this would
3073 -- get the second api with the wrong info as the value is alredy rolled back
3074 --in the first Api.
3075 hr_utility.set_location('Entering val_create_vehicle_details:',15);
3076 val_create_vehicle_details
3077 (
3078 p_validate => false
3079 ,p_effective_date =>x_effective_date
3080 ,p_registration_number =>x_registration_number
3081 ,p_vehicle_type =>x_vehicle_type
3082 ,p_vehicle_id_number =>x_vehicle_id_number
3083 ,p_business_group_id =>x_business_group_id
3084 ,p_make =>x_make
3085 ,p_engine_capacity_in_cc =>x_engine_capacity_in_cc
3086 ,p_fuel_type =>x_fuel_type
3087 ,p_currency_code =>x_currency_code
3088 ,p_model =>x_model
3089 ,p_initial_registration =>x_initial_registration
3090 ,p_last_registration_renew_date =>x_last_registration_renew_date
3091 ,p_fiscal_ratings =>x_fiscal_ratings
3092 ,p_vehicle_ownership =>x_vehicle_ownership
3093 ,p_shared_vehicle =>x_shared_vehicle
3094 ,p_color =>x_color
3095 ,p_seating_capacity =>x_seating_capacity
3096 ,p_weight =>x_weight
3097 ,p_weight_uom =>x_weight_uom
3098 ,p_model_year =>x_model_year
3099 ,p_insurance_number =>x_insurance_number
3100 ,p_insurance_expiry_date =>x_insurance_expiry_date
3101 ,p_taxation_method =>x_taxation_method
3102 ,p_comments =>x_comments
3103 ,p_vre_attribute_category =>x_vre_attribute_category
3104 ,p_vre_attribute1 =>x_vre_attribute1
3105 ,p_vre_attribute2 =>x_vre_attribute2
3106 ,p_vre_attribute3 =>x_vre_attribute3
3107 ,p_vre_attribute4 =>x_vre_attribute4
3108 ,p_vre_attribute5 =>x_vre_attribute5
3109 ,p_vre_attribute6 =>x_vre_attribute6
3110 ,p_vre_attribute7 =>x_vre_attribute7
3111 ,p_vre_attribute8 =>x_vre_attribute8
3112 ,p_vre_attribute9 =>x_vre_attribute9
3113 ,p_vre_attribute10 =>x_vre_attribute10
3114 ,p_vre_attribute11 =>x_vre_attribute11
3115 ,p_vre_attribute12 =>x_vre_attribute12
3116 ,p_vre_attribute13 =>x_vre_attribute13
3117 ,p_vre_attribute14 =>x_vre_attribute14
3118 ,p_vre_attribute15 =>x_vre_attribute15
3119 ,p_vre_attribute16 =>x_vre_attribute16
3120 ,p_vre_attribute17 =>x_vre_attribute17
3121 ,p_vre_attribute18 =>x_vre_attribute18
3122 ,p_vre_attribute19 =>x_vre_attribute19
3123 ,p_vre_attribute20 =>x_vre_attribute20
3124 ,p_vre_information_category =>x_vre_information_category
3125 ,p_vre_information1 =>x_vre_information1
3126 ,p_vre_information2 =>x_vre_information2
3127 ,p_vre_information3 =>x_vre_information3
3128 ,p_vre_information4 =>x_vre_information4
3129 ,p_vre_information5 =>x_vre_information5
3130 ,p_vre_information6 =>x_vre_information6
3131 ,p_vre_information7 =>x_vre_information7
3132 ,p_vre_information8 =>x_vre_information8
3133 ,p_vre_information9 =>x_vre_information9
3134 ,p_vre_information10 =>x_vre_information10
3135 ,p_vre_information11 =>x_vre_information11
3136 ,p_vre_information12 =>x_vre_information12
3137 ,p_vre_information13 =>x_vre_information13
3138 ,p_vre_information14 =>x_vre_information14
3139 ,p_vre_information15 =>x_vre_information15
3140 ,p_vre_information16 =>x_vre_information16
3141 ,p_vre_information17 =>x_vre_information17
3142 ,p_vre_information18 =>x_vre_information18
3143 ,p_vre_information19 =>x_vre_information19
3144 ,p_vre_information20 =>x_vre_information20
3145 ,p_across_assignments =>x_across_assignments
3146 ,p_usage_type =>x_usage_type
3147 ,p_default_vehicle =>x_default_vehicle
3148 ,p_fuel_card =>x_fuel_card
3149 ,p_fuel_card_number =>x_fuel_card_number
3150 ,p_val_attribute_category =>x_val_attribute_category
3151 ,p_val_attribute1 =>x_val_attribute1
3152 ,p_val_attribute2 =>x_val_attribute2
3153 ,p_val_attribute3 =>x_val_attribute3
3154 ,p_val_attribute4 =>x_val_attribute4
3155 ,p_val_attribute5 =>x_val_attribute5
3156 ,p_val_attribute6 =>x_val_attribute6
3157 ,p_val_attribute7 =>x_val_attribute7
3158 ,p_val_attribute8 =>x_val_attribute8
3159 ,p_val_attribute9 =>x_val_attribute9
3160 ,p_val_attribute10 =>x_val_attribute10
3161 ,p_val_attribute11 =>x_val_attribute11
3162 ,p_val_attribute12 =>x_val_attribute12
3163 ,p_val_attribute13 =>x_val_attribute13
3164 ,p_val_attribute14 =>x_val_attribute14
3165 ,p_val_attribute15 =>x_val_attribute15
3166 ,p_val_attribute16 =>x_val_attribute16
3167 ,p_val_attribute17 =>x_val_attribute17
3168 ,p_val_attribute18 =>x_val_attribute18
3169 ,p_val_attribute19 =>x_val_attribute19
3170 ,p_val_attribute20 =>x_val_attribute20
3171 ,p_val_information_category =>x_val_information_category
3172 ,p_val_information1 =>x_val_information1
3173 ,p_val_information2 =>x_val_information2
3174 ,p_val_information3 =>x_val_information3
3175 ,p_val_information4 =>x_val_information4
3176 ,p_val_information5 =>x_val_information5
3177 ,p_val_information6 =>x_val_information6
3178 ,p_val_information7 =>x_val_information7
3179 ,p_val_information8 =>x_val_information8
3180 ,p_val_information9 =>x_val_information9
3181 ,p_val_information10 =>x_val_information10
3182 ,p_val_information11 =>x_val_information11
3183 ,p_val_information12 =>x_val_information12
3184 ,p_val_information13 =>x_val_information13
3185 ,p_val_information14 =>x_val_information14
3186 ,p_val_information15 =>x_val_information15
3187 ,p_val_information16 =>x_val_information16
3188 ,p_val_information17 =>x_val_information17
3189 ,p_val_information18 =>x_val_information18
3190 ,p_val_information19 =>x_val_information19
3191 ,p_val_information20 =>x_val_information20
3192 ,p_fuel_benefit =>x_fuel_benefit
3193 ,p_user_info =>x_user_info
3194 ,p_vehicle_repository_id =>x_vehicle_repository_id
3195 ,p_vehicle_allocation_id =>x_vehicle_allocation_id
3196 ,p_object_version_number =>x_object_version_number
3197 ,p_error_message =>l_error_message
3198 ,p_error_status =>l_error_status
3199 );
3200 hr_utility.set_location('Leaving val_create_vehicle_details:',20);
3201 exception
3202 when hr_multi_message.error_message_exist then
3203 --
3204 -- Catch the Multiple Message List exception which
3205 -- indicates API processing has been aborted because
3206 -- at least one message exists in the list.
3207 --
3208 rollback to savepoint pqp_vehicle_validate;
3209 --
3210 -- Reset IN OUT parameters and set OUT parameters
3211 x_error_status := hr_multi_message.get_return_status_disable;
3212
3213 hr_utility.set_location(' Leaving:' ,40);
3214 when others then
3215 --
3216 -- When Multiple Message Detection is enabled catch
3217 -- any Application specific or other unexpected
3218 -- exceptions. Adding appropriate details to the
3219 -- Multiple Message List. Otherwise re-raise the
3220 -- error.
3221 --
3222 rollback to savepoint pqp_vehicle_validate;
3223 if hr_multi_message.unexpected_error_add('l_proc') then
3224 --raise;
3225 x_error_status := hr_multi_message.get_return_status_disable;
3226 end if;
3227 -- Reset IN OUT parameters and set OUT parameters
3228
3229 x_error_status := hr_multi_message.get_return_status_disable;
3230 hr_utility.set_location(' Leaving:' || 'l_proc',50);
3231 --raise;
3232 hr_utility.set_location('Leaving validation :',30);
3233 END;
3234 -- If there are no error messages then insert values into transaction table
3235 SAVEPOINT pqp_create_transaction;
3236 IF x_transaction_id is NULl THEN
3237 hr_utility.set_location('Entering create_transaction :',10);
3238 hr_transaction_api.create_transaction(
3239 p_validate => false
3240 ,p_creator_person_id => x_login_person_id
3241 ,p_transaction_privilege => 'PRIVATE'
3242 ,p_product_code => 'PQP'
3243 ,p_url => NULL
3244 ,p_status => x_status
3245 ,p_section_display_name =>NULL
3246 ,p_function_id =>NULL
3247 ,p_transaction_ref_table =>NULL
3248 ,p_transaction_ref_id =>NULL
3249 ,p_transaction_type =>NULL
3250 ,p_assignment_id =>x_assignment_id
3251 ,p_selected_person_id =>x_person_id
3252 ,p_item_type =>x_item_type
3253 ,p_item_key =>x_item_key
3254 ,p_transaction_effective_date =>x_effective_date
3255 ,p_process_name =>NULL
3256 ,p_plan_id =>NULL
3257 ,p_rptg_grp_id =>NULL
3258 ,p_effective_date_option =>x_effective_date_option
3259 ,p_transaction_id =>l_transaction_id
3260 );
3261
3262 hr_utility.set_location('Leaving create_transaction :',15);
3263 wf_engine.setitemattrnumber
3264 (itemtype => x_item_type
3265 ,itemkey => x_item_key
3266 ,aname => 'TRANSACTION_ID'
3267 ,avalue => l_transaction_id
3268 );
3269
3270 x_transaction_id := l_transaction_id;
3271 hr_utility.set_location('Leaving setitemattrnumber :',20);
3272 --Create transaction steps
3273 hr_transaction_api.create_transaction_step
3274 (p_validate =>false
3275 ,p_creator_person_id =>x_login_person_id
3276 ,p_transaction_id =>l_transaction_id
3277 ,p_api_name =>l_api_name
3278 ,p_api_display_name =>l_api_name
3279 ,p_item_type =>x_item_type
3280 ,p_item_key =>x_item_key
3281 ,p_activity_id =>x_activity_id
3282 ,p_transaction_step_id =>l_transaction_step_id
3283 ,p_object_version_number =>l_ovn
3284 );
3285 hr_utility.set_location('Leaving create_transaction_step :',25);
3286 ELSE
3287 hr_utility.set_location('Entering update transaction :',10);
3288 hr_transaction_api.update_transaction
3289 (p_transaction_id => x_transaction_id
3290 ,p_status => x_status
3291 );
3292 hr_utility.set_location('leaving update transaction :',15);
3293 OPEN c_del_values;
3294 FETCH c_del_values INTO l_del_values;
3295 CLOSE c_del_values;
3296
3297 hr_utility.set_location('leaving c del value :',20);
3298 DELETE from hr_api_transaction_values
3299 WHERE transaction_step_id = l_del_values.transaction_step_id;
3300 l_transaction_step_id := l_del_values.transaction_step_id;
3301 hr_utility.set_location('leaving delete :',25);
3302 END IF;
3303 FOR i in 1..l_trans_tbl.count
3304 LOOP
3305 IF l_trans_tbl(i).param_data_type ='VARCHAR2' THEN
3306 hr_utility.set_location('Enter varchar param||l_trans_tbl (i).param_name :',10);
3307 hr_utility.set_location('Enter varchar param value||l_trans_tbl (i).param_value :',10);
3308 hr_transaction_api.set_varchar2_value
3309 (p_transaction_step_id =>l_transaction_step_id
3310 ,p_person_id =>x_person_id
3311 ,p_name =>l_trans_tbl (i).param_name
3312 ,p_value =>l_trans_tbl (i).param_value
3313 );
3314 hr_utility.set_location('Leaving param data type :',20);
3315
3316 ELSIF l_trans_tbl(i).param_data_type ='DATE' THEN
3317 hr_utility.set_location('Enter date Param||l_trans_tbl (i).param_name :',10);
3318 hr_utility.set_location('Enter date Param Value||
3319 l_trans_tbl (i).param_value :',10);
3320 hr_transaction_api.set_date_value
3321 (
3322 p_transaction_step_id => l_transaction_step_id
3323 ,p_person_id => x_person_id
3324 ,p_name => l_trans_tbl (i).param_name
3325 ,p_value => fnd_date.displaydate_to_date(l_trans_tbl (i)
3326 .param_value ) );
3327 hr_utility.set_location('Leaving param date :',20);
3328 ELSIF l_trans_tbl(i).param_data_type ='NUMBER' THEN
3329 hr_utility.set_location('Enter number param||l_trans_tbl (i).param_name :',10);
3330 hr_utility.set_location('Enter number param value||l_trans_tbl (i).param_value :',10);
3331 hr_transaction_api.set_number_value
3332 (
3333 p_transaction_step_id => l_transaction_step_id
3334 ,p_person_id => x_person_id
3335 ,p_name =>l_trans_tbl (i).param_name
3336 ,p_value =>TO_NUMBER(l_trans_tbl (i).param_value ));
3337 hr_utility.set_location('Leaving param number :',20);
3338 END IF;
3339 END LOOP;
3340
3341
3342 ---Set Global value to open extra info page
3343 set_extra_info_val (
3344 itemtype =>x_item_type,
3345 itemkey =>x_item_key,
3346 result =>l_sec_result );
3347 EXCEPTION
3348 WHEN hr_multi_message.error_message_exist THEN
3349 --
3350 -- Catch the Multiple Message List exception which
3351 -- indicates API processing has been aborted because
3352 -- at least one message exists in the list.
3353 --
3354 ROLLBACK TO SAVEPOINT pqp_vehicle_proc_start;
3355 --
3356 -- Reset IN OUT parameters and set OUT parameters
3357 x_error_status := hr_multi_message.get_return_status_disable;
3358
3359 hr_utility.set_location(' Leaving:' ,40);
3360 WHEN others then
3361 --
3362 -- When Multiple Message Detection is enabled catch
3363 -- any Application specific or other unexpected
3364 -- exceptions. Adding appropriate details to the
3365 -- Multiple Message List. Otherwise re-raise the
3366 -- error.
3367 --
3368 ROLLBACK TO SAVEPOINT pqp_vehicle_proc_start;
3369 IF hr_multi_message.unexpected_error_add('l_proc') then
3370 --raise;
3371 x_error_status := hr_multi_message.get_return_status_disable;
3372 END IF;
3373 -- Reset IN OUT parameters and set OUT parameters
3374
3375 x_error_status := hr_multi_message.get_return_status_disable;
3376 hr_utility.set_location(' Leaving:' || 'l_proc',50);
3377 --raise;
3378
3379
3380
3381
3382
3383 END;
3384 --
3385 --not used at the moment
3386
3387 PROCEDURE delete_process_api (
3388 p_validate IN BOOLEAN DEFAULT FALSE,
3389 p_transaction_step_id IN NUMBER,
3390 p_effective_date IN VARCHAR2 DEFAULT NULL ) IS
3391 --
3392 --
3393 l_ovn NUMBER :=1;
3394 l_error_status VARCHAR2(10);
3395 l_effective_start_date DATE;
3396 l_effective_end_date DATE;
3397 l_person_id per_all_people_f.person_id%TYPE;
3398 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
3399 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
3400 l_allocation_id pqp_vehicle_allocations_f.vehicle_allocation_id%TYPE;
3401 l_effective_date DATE;
3402 BEGIN
3403 l_person_id := hr_transaction_api.get_number_value (
3404 p_transaction_step_id => p_transaction_step_id,
3405 p_name =>'P_PERSON_ID' );
3406
3407 l_effective_date := hr_transaction_api.get_date_value (
3408 p_transaction_step_id => p_transaction_step_id,
3409 p_name =>'P_EFFECTIVE_DATE' );
3410 l_assignment_id :=hr_transaction_api.get_number_value (
3411 p_transaction_step_id => p_transaction_step_id,
3412 p_name =>'P_ASSIGNMENT_ID' );
3413
3414 l_business_group_id :=hr_transaction_api.get_number_value (
3415 p_transaction_step_id => p_transaction_step_id,
3416 p_name =>'P_BUSINESS_GROUP_ID' );
3417 l_allocation_id :=hr_transaction_api.get_number_value (
3418 p_transaction_step_id => p_transaction_step_id,
3419 p_name =>'P_ALLOCATION_ID' );
3420
3421
3422
3423
3424 delete_process (
3425 p_validate => false
3426 ,p_effective_date => l_effective_date
3427 ,p_person_id => l_person_id
3428 ,p_assignment_id => l_assignment_id
3429 ,p_business_group_id => l_business_group_id
3430 ,p_vehicle_allocation_id => l_allocation_id
3431 ,p_error_status => l_error_status
3432 );
3433
3434 -- EXCEPTION
3435 -- WHEN hr_utility.hr_error THEN
3436 --hr_utility.raise_error;
3437 -- WHEN OTHERS THEN
3438 -- RAISE; -- Raise error here relevant to the new tech stack.
3439
3440 END;
3441
3442 --This process is called when the transaction is approved and
3443 --the information is created in the base tables.
3444 PROCEDURE process_api (
3445 p_validate IN BOOLEAN DEFAULT FALSE,
3446 p_transaction_step_id IN NUMBER,
3447 p_effective_date IN VARCHAR2 DEFAULT NULL ) IS
3448 --
3449 --
3450 l_person_id NUMBER;
3451 l_ovn NUMBER :=1;
3452 l_assignment_id NUMBER;
3453 l_effective_date DATE;
3454 l_registration_number pqp_vehicle_repository_f.registration_number%TYPE;
3455 l_vehicle_type pqp_vehicle_repository_f.vehicle_type%TYPE;
3456 l_vehicle_id_number pqp_vehicle_repository_f.vehicle_id_number%TYPE;
3457 l_business_group_id pqp_vehicle_repository_f.business_group_id%TYPE;
3458 l_make pqp_vehicle_repository_f.make%TYPE;
3459 l_engine_capacity_in_cc pqp_vehicle_repository_f.engine_capacity_in_cc%TYPE;
3460 l_fuel_type pqp_vehicle_repository_f.fuel_type%TYPE;
3461 l_currency_code pqp_vehicle_repository_f.currency_code%TYPE;
3462 l_model pqp_vehicle_repository_f.model%TYPE;
3463 l_initial_registration pqp_vehicle_repository_f.initial_registration%TYPE;
3464 l_last_registration_renew_date pqp_vehicle_repository_f.last_registration_renew_date%TYPE;
3465 l_fiscal_ratings pqp_vehicle_repository_f.fiscal_ratings%TYPE;
3466 l_vehicle_ownership pqp_vehicle_repository_f.vehicle_ownership%TYPE;
3467 l_shared_vehicle pqp_vehicle_repository_f.shared_vehicle%TYPE;
3468 l_color pqp_vehicle_repository_f.color%TYPE;
3469 l_seating_capacity pqp_vehicle_repository_f.seating_capacity%TYPE;
3470 l_weight pqp_vehicle_repository_f.weight%TYPE;
3471 l_weight_uom pqp_vehicle_repository_f.weight_uom%TYPE;
3472 l_model_year pqp_vehicle_repository_f.model_year%TYPE;
3473 l_insurance_number pqp_vehicle_repository_f.insurance_number%TYPE;
3474 l_insurance_expiry_date pqp_vehicle_repository_f.insurance_expiry_date%TYPE;
3475 l_taxation_method pqp_vehicle_repository_f.taxation_method%TYPE;
3476 l_comments pqp_vehicle_repository_f.comments%TYPE;
3477 l_vre_attribute_category pqp_vehicle_repository_f.vre_attribute_category%TYPE;
3478 l_vre_attribute1 pqp_vehicle_repository_f.vre_attribute1%TYPE;
3479 l_vre_attribute2 pqp_vehicle_repository_f.vre_attribute2%TYPE;
3480 l_vre_attribute3 pqp_vehicle_repository_f.vre_attribute3%TYPE;
3481 l_vre_attribute4 pqp_vehicle_repository_f.vre_attribute4%TYPE;
3482 l_vre_attribute5 pqp_vehicle_repository_f.vre_attribute5%TYPE;
3483 l_vre_attribute6 pqp_vehicle_repository_f.vre_attribute6%TYPE;
3484 l_vre_attribute7 pqp_vehicle_repository_f.vre_attribute7%TYPE;
3485 l_vre_attribute8 pqp_vehicle_repository_f.vre_attribute8%TYPE;
3486 l_vre_attribute9 pqp_vehicle_repository_f.vre_attribute9%TYPE;
3487 l_vre_attribute10 pqp_vehicle_repository_f.vre_attribute10%TYPE;
3488 l_vre_attribute11 pqp_vehicle_repository_f.vre_attribute11%TYPE;
3489 l_vre_attribute12 pqp_vehicle_repository_f.vre_attribute12%TYPE;
3490 l_vre_attribute13 pqp_vehicle_repository_f.vre_attribute13%TYPE;
3491 l_vre_attribute14 pqp_vehicle_repository_f.vre_attribute14%TYPE;
3492 l_vre_attribute15 pqp_vehicle_repository_f.vre_attribute15%TYPE;
3493 l_vre_attribute16 pqp_vehicle_repository_f.vre_attribute16%TYPE;
3494 l_vre_attribute17 pqp_vehicle_repository_f.vre_attribute17%TYPE;
3495 l_vre_attribute18 pqp_vehicle_repository_f.vre_attribute18%TYPE;
3496 l_vre_attribute19 pqp_vehicle_repository_f.vre_attribute19%TYPE;
3497 l_vre_attribute20 pqp_vehicle_repository_f.vre_attribute20%TYPE;
3498 l_vre_information_category pqp_vehicle_repository_f.vre_information_category%TYPE;
3499 l_vre_information1 pqp_vehicle_repository_f.vre_information1%TYPE;
3500 l_vre_information2 pqp_vehicle_repository_f.vre_information2%TYPE;
3501 l_vre_information3 pqp_vehicle_repository_f.vre_information3%TYPE;
3502 l_vre_information4 pqp_vehicle_repository_f.vre_information4%TYPE;
3503 l_vre_information5 pqp_vehicle_repository_f.vre_information5%TYPE;
3504 l_vre_information6 pqp_vehicle_repository_f.vre_information6%TYPE;
3505 l_vre_information7 pqp_vehicle_repository_f.vre_information7%TYPE;
3506 l_vre_information8 pqp_vehicle_repository_f.vre_information8%TYPE;
3507 l_vre_information9 pqp_vehicle_repository_f.vre_information9%TYPE;
3508 l_vre_information10 pqp_vehicle_repository_f.vre_information10%TYPE;
3509 l_vre_information11 pqp_vehicle_repository_f.vre_information11%TYPE;
3510 l_vre_information12 pqp_vehicle_repository_f.vre_information12%TYPE;
3511 l_vre_information13 pqp_vehicle_repository_f.vre_information13%TYPE;
3512 l_vre_information14 pqp_vehicle_repository_f.vre_information14%TYPE;
3513 l_vre_information15 pqp_vehicle_repository_f.vre_information15%TYPE;
3514 l_vre_information16 pqp_vehicle_repository_f.vre_information16%TYPE;
3515 l_vre_information17 pqp_vehicle_repository_f.vre_information17%TYPE;
3516 l_vre_information18 pqp_vehicle_repository_f.vre_information18%TYPE;
3517 l_vre_information19 pqp_vehicle_repository_f.vre_information19%TYPE;
3518 l_vre_information20 pqp_vehicle_repository_f.vre_information20%TYPE;
3519 l_across_assignments pqp_vehicle_allocations_f.across_assignments%TYPE;
3520 l_usage_type pqp_vehicle_allocations_f.usage_type%TYPE;
3521 l_default_vehicle pqp_vehicle_allocations_f.default_vehicle%TYPE;
3522 l_fuel_card pqp_vehicle_allocations_f.fuel_card%TYPE;
3523 l_fuel_card_number pqp_vehicle_allocations_f.fuel_card_number%TYPE;
3524 l_val_attribute_category pqp_vehicle_allocations_f.val_attribute_category%TYPE;
3525 l_val_attribute1 pqp_vehicle_allocations_f.val_attribute1%TYPE;
3526 l_val_attribute2 pqp_vehicle_allocations_f.val_attribute2%TYPE;
3527 l_val_attribute3 pqp_vehicle_allocations_f.val_attribute3%TYPE;
3528 l_val_attribute4 pqp_vehicle_allocations_f.val_attribute4%TYPE;
3529 l_val_attribute5 pqp_vehicle_allocations_f.val_attribute5%TYPE;
3530 l_val_attribute6 pqp_vehicle_allocations_f.val_attribute6%TYPE;
3531 l_val_attribute7 pqp_vehicle_allocations_f.val_attribute7%TYPE;
3532 l_val_attribute8 pqp_vehicle_allocations_f.val_attribute8%TYPE;
3533 l_val_attribute9 pqp_vehicle_allocations_f.val_attribute9%TYPE;
3534 l_val_attribute10 pqp_vehicle_allocations_f.val_attribute10%TYPE;
3535 l_val_attribute11 pqp_vehicle_allocations_f.val_attribute11%TYPE;
3536 l_val_attribute12 pqp_vehicle_allocations_f.val_attribute12%TYPE;
3537 l_val_attribute13 pqp_vehicle_allocations_f.val_attribute13%TYPE;
3538 l_val_attribute14 pqp_vehicle_allocations_f.val_attribute14%TYPE;
3539 l_val_attribute15 pqp_vehicle_allocations_f.val_attribute15%TYPE;
3540 l_val_attribute16 pqp_vehicle_allocations_f.val_attribute16%TYPE;
3541 l_val_attribute17 pqp_vehicle_allocations_f.val_attribute17%TYPE;
3542 l_val_attribute18 pqp_vehicle_allocations_f.val_attribute18%TYPE;
3543 l_val_attribute19 pqp_vehicle_allocations_f.val_attribute19%TYPE;
3544 l_val_attribute20 pqp_vehicle_allocations_f.val_attribute20%TYPE;
3545 l_val_information_category pqp_vehicle_allocations_f.val_information_category%TYPE;
3546 l_val_information1 pqp_vehicle_allocations_f.val_information1%TYPE;
3547 l_val_information2 pqp_vehicle_allocations_f.val_information2%TYPE;
3548 l_val_information3 pqp_vehicle_allocations_f.val_information3%TYPE;
3549 l_val_information4 pqp_vehicle_allocations_f.val_information4%TYPE;
3550 l_val_information5 pqp_vehicle_allocations_f.val_information5%TYPE;
3551 l_val_information6 pqp_vehicle_allocations_f.val_information6%TYPE;
3552 l_val_information7 pqp_vehicle_allocations_f.val_information7%TYPE;
3553 l_val_information8 pqp_vehicle_allocations_f.val_information8%TYPE;
3554 l_val_information9 pqp_vehicle_allocations_f.val_information9%TYPE;
3555 l_val_information10 pqp_vehicle_allocations_f.val_information10%TYPE;
3556 l_val_information11 pqp_vehicle_allocations_f.val_information11%TYPE;
3557 l_val_information12 pqp_vehicle_allocations_f.val_information12%TYPE;
3558 l_val_information13 pqp_vehicle_allocations_f.val_information13%TYPE;
3559 l_val_information14 pqp_vehicle_allocations_f.val_information14%TYPE;
3560 l_val_information15 pqp_vehicle_allocations_f.val_information15%TYPE;
3561 l_val_information16 pqp_vehicle_allocations_f.val_information16%TYPE;
3562 l_val_information17 pqp_vehicle_allocations_f.val_information17%TYPE;
3563 l_val_information18 pqp_vehicle_allocations_f.val_information18%TYPE;
3564 l_val_information19 pqp_vehicle_allocations_f.val_information19%TYPE;
3565 l_val_information20 pqp_vehicle_allocations_f.val_information20%TYPE;
3566 l_fuel_benefit pqp_vehicle_allocations_f.fuel_benefit%TYPE;
3567 l_object_version_number NUMBER;
3568 l_error_message VARCHAr2(80);
3569 l_error_status VARCHAr2(30);
3570 l_vehicle_allocation_id NUMBER;
3571 l_vehicle_repository_id NUMBER;
3572 l_user_info t_user_info;
3573 l_get_count NUMBER;
3574 l_transaction_id NUMBER;
3575 CURSOR c_get_count
3576 IS
3577 SELECT count(hatv.name)
3578 FROM hr_api_transaction_values hatv
3579 WHERE hatv.transaction_step_id=p_transaction_step_id
3580 AND hatv.name like 'P_PERSON_USR_ID%';
3581 --
3582 --
3583 --
3584 --
3585 p_user_info t_user_info;
3586 CURSOR c_get_other_tstep
3587 IS
3588 SELECT hats.item_type,hats.item_key
3589 FROM hr_api_transaction_steps hats
3590 WHERE hats.transaction_id=l_transaction_id
3591 AND hats.transaction_step_id =p_transaction_step_id;
3592
3593 l_get_other_tstep c_get_other_tstep%ROWTYPE;
3594 CURSOR c_get_details IS
3595 SELECT pvr.vehicle_repository_id
3596 ,pva.vehicle_allocation_id
3597 FROM pqp_vehicle_repository_f pvr
3598 ,pqp_vehicle_allocations_f pva
3599 WHERE pvr.vehicle_repository_id =pva.vehicle_repository_id
3600 AND pva.assignment_id =l_assignment_id
3601 AND NVL(l_effective_date,SYSDATE) BETWEEN pvr.effective_start_date
3602 AND pvr.effective_end_date
3603 AND NVL(l_effective_date,sysdate) BETWEEN pva.effective_start_date
3604 AND pva.effective_end_date
3605 AND pvr.registration_number=l_registration_number;
3606 l_get_details c_get_details%ROWTYPE;
3607 BEGIN
3608 -- hr_utility.trace_on(NULL,'gattu');
3609 hr_utility.set_location('Entering:process_api',5);
3610 hr_utility.set_location(' p_transaction_step_id'|| p_transaction_step_id,5);
3611
3612 --
3613 savepoint process_veh_details;
3614 --
3615 l_transaction_id :=get_transaction_id(
3616 p_transaction_step_id =>p_transaction_step_id);
3617
3618
3619 l_person_id := hr_transaction_api.get_number_value (
3620 p_transaction_step_id => p_transaction_step_id,
3621 p_name =>'P_PERSON_ID' );
3622
3623
3624 l_effective_date := hr_transaction_api.get_date_value (
3625 p_transaction_step_id => p_transaction_step_id,
3626 p_name =>'P_EFFECTIVE_DATE' );
3627
3628 l_registration_number := hr_transaction_api.get_varchar2_value (
3629 p_transaction_step_id => p_transaction_step_id,
3630 p_name =>'P_REGISTRATION_NUMBER' );
3631
3632 l_vehicle_type := hr_transaction_api.get_varchar2_value (
3633 p_transaction_step_id => p_transaction_step_id,
3634 p_name =>'P_VEHICLE_TYPE' );
3635
3636 l_vehicle_id_number := hr_transaction_api.get_varchar2_value (
3637 p_transaction_step_id => p_transaction_step_id,
3638 p_name =>'P_VEHICLE_ID_NUMBER' );
3639 l_business_group_id := hr_transaction_api.get_number_value (
3640 p_transaction_step_id => p_transaction_step_id,
3641 p_name =>'P_BUSINESS_GROUP_ID');
3642
3643 l_make := hr_transaction_api.get_varchar2_value (
3644 p_transaction_step_id => p_transaction_step_id,
3645 p_name =>'P_MAKE' );
3646
3647 l_engine_capacity_in_cc := hr_transaction_api.get_number_value (
3648 p_transaction_step_id => p_transaction_step_id,
3649 p_name =>'P_ENGINE_CAPACITY_IN_CC');
3650
3651 l_fuel_type := hr_transaction_api.get_varchar2_value (
3652 p_transaction_step_id => p_transaction_step_id,
3653 p_name =>'P_FUEL_TYPE');
3654
3655 l_currency_code := hr_transaction_api.get_varchar2_value (
3656 p_transaction_step_id => p_transaction_step_id,
3657 p_name =>'P_CURRENCY_CODE');
3658
3659 l_model := hr_transaction_api.get_varchar2_value (
3660 p_transaction_step_id => p_transaction_step_id,
3661 p_name =>'P_MODEL' );
3662
3663 l_initial_registration := hr_transaction_api.get_date_value (
3664 p_transaction_step_id => p_transaction_step_id,
3665 p_name =>'P_INITIAL_REGISTRATION');
3666
3667 l_last_registration_renew_date := hr_transaction_api.get_date_value (
3668 p_transaction_step_id => p_transaction_step_id,
3669 p_name =>'P_LAST_REGISTRATION_RENEW_DATE');
3670
3671 l_fiscal_ratings := hr_transaction_api.get_number_value (
3672 p_transaction_step_id => p_transaction_step_id,
3673 p_name =>'P_FISCAL_RATINGS' );
3674
3675 l_vehicle_ownership := hr_transaction_api.get_varchar2_value (
3676 p_transaction_step_id => p_transaction_step_id,
3677 p_name =>'P_VEHICLE_OWNERSHIP');
3678
3679 l_shared_vehicle := hr_transaction_api.get_varchar2_value (
3680 p_transaction_step_id => p_transaction_step_id,
3681 p_name =>'P_SHARED_VEHICLE' );
3682
3683 l_color := hr_transaction_api.get_varchar2_value (
3684 p_transaction_step_id => p_transaction_step_id,
3685 p_name =>'P_COLOR' );
3686
3687 l_seating_capacity := hr_transaction_api.get_number_value (
3688 p_transaction_step_id => p_transaction_step_id,
3689 p_name =>'P_SEATING_CAPACITY' );
3690
3691 l_weight := hr_transaction_api.get_number_value (
3692 p_transaction_step_id => p_transaction_step_id,
3693 p_name =>'P_WEIGHT' );
3694
3695 l_weight_uom := hr_transaction_api.get_varchar2_value (
3696 p_transaction_step_id => p_transaction_step_id,
3697 p_name =>'P_WEIGHT_UOM' );
3698
3699 l_model_year := hr_transaction_api.get_number_value (
3700 p_transaction_step_id => p_transaction_step_id,
3701 p_name =>'P_MODEL_YEAR' );
3702
3703 l_insurance_number := hr_transaction_api.get_varchar2_value (
3704 p_transaction_step_id => p_transaction_step_id,
3705 p_name =>'P_INSURANCE_NUMBER' );
3706
3707 l_insurance_expiry_date := hr_transaction_api.get_date_value (
3708 p_transaction_step_id => p_transaction_step_id,
3709 p_name =>'P_INSURANCE_EXPIRY_DATE' );
3710
3711 l_taxation_method := hr_transaction_api.get_varchar2_value (
3712 p_transaction_step_id => p_transaction_step_id,
3713 p_name =>'P_TAXATION_METHOD');
3714
3715 l_comments := hr_transaction_api.get_varchar2_value (
3716 p_transaction_step_id => p_transaction_step_id,
3717 p_name =>'P_COMMENTS' );
3718
3719 l_vre_attribute_category := hr_transaction_api.get_varchar2_value (
3720 p_transaction_step_id => p_transaction_step_id,
3721 p_name =>'P_VRE_ATTRIBUTE_CATEGORY' );
3722
3723 l_vre_attribute1 := hr_transaction_api.get_varchar2_value (
3724 p_transaction_step_id => p_transaction_step_id,
3725 p_name =>'P_VRE_ATTRIBUTE1' );
3726
3727 l_vre_attribute2 := hr_transaction_api.get_varchar2_value (
3728 p_transaction_step_id => p_transaction_step_id,
3729 p_name =>'P_VRE_ATTRIBUTE2' );
3730
3731 l_vre_attribute3 := hr_transaction_api.get_varchar2_value (
3732 p_transaction_step_id => p_transaction_step_id,
3733 p_name =>'P_VRE_ATTRIBUTE3' );
3734
3735 l_vre_attribute4 := hr_transaction_api.get_varchar2_value (
3736 p_transaction_step_id => p_transaction_step_id,
3737 p_name =>'P_VRE_ATTRIBUTE4' );
3738
3739
3740 l_vre_attribute5 := hr_transaction_api.get_varchar2_value (
3741 p_transaction_step_id => p_transaction_step_id,
3742 p_name =>'P_VRE_ATTRIBUTE5' );
3743
3744 l_vre_attribute6 := hr_transaction_api.get_varchar2_value (
3745 p_transaction_step_id => p_transaction_step_id,
3746 p_name =>'P_VRE_ATTRIBUTE6' );
3747
3748 l_vre_attribute7 := hr_transaction_api.get_varchar2_value (
3749 p_transaction_step_id => p_transaction_step_id,
3750 p_name =>'P_VRE_ATTRIBUTE7' );
3751
3752 l_vre_attribute8 := hr_transaction_api.get_varchar2_value (
3753 p_transaction_step_id => p_transaction_step_id,
3754 p_name =>'P_VRE_ATTRIBUTE8' );
3755
3756 l_vre_attribute9 := hr_transaction_api.get_varchar2_value (
3757 p_transaction_step_id => p_transaction_step_id,
3758 p_name =>'P_VRE_ATTRIBUTE9' );
3759
3760 l_vre_attribute10 := hr_transaction_api.get_varchar2_value (
3761 p_transaction_step_id => p_transaction_step_id,
3762 p_name =>'P_VRE_ATTRIBUTE10' );
3763
3764 l_vre_attribute11 := hr_transaction_api.get_varchar2_value (
3765 p_transaction_step_id => p_transaction_step_id,
3766 p_name =>'P_VRE_ATTRIBUTE11' );
3767
3768 l_vre_attribute12 := hr_transaction_api.get_varchar2_value (
3769 p_transaction_step_id => p_transaction_step_id,
3770 p_name =>'P_VRE_ATTRIBUTE12' );
3771
3772 l_vre_attribute13 := hr_transaction_api.get_varchar2_value (
3773 p_transaction_step_id => p_transaction_step_id,
3774 p_name =>'P_VRE_ATTRIBUTE13' );
3775
3776 l_vre_attribute14 := hr_transaction_api.get_varchar2_value (
3777 p_transaction_step_id => p_transaction_step_id,
3778 p_name =>'P_VRE_ATTRIBUTE14' );
3779
3780 l_vre_attribute15 := hr_transaction_api.get_varchar2_value (
3781 p_transaction_step_id => p_transaction_step_id,
3782 p_name =>'P_VRE_ATTRIBUTE15' );
3783
3784 l_vre_attribute16 := hr_transaction_api.get_varchar2_value (
3785 p_transaction_step_id => p_transaction_step_id,
3786 p_name =>'P_VRE_ATTRIBUTE16' );
3787
3788 l_vre_attribute17 := hr_transaction_api.get_varchar2_value (
3789 p_transaction_step_id => p_transaction_step_id,
3790 p_name =>'P_VRE_ATTRIBUTE17' );
3791
3792 l_vre_attribute18 := hr_transaction_api.get_varchar2_value (
3793 p_transaction_step_id => p_transaction_step_id,
3794 p_name =>'P_VRE_ATTRIBUTE18' );
3795
3796 l_vre_attribute19 := hr_transaction_api.get_varchar2_value (
3797 p_transaction_step_id => p_transaction_step_id,
3798 p_name =>'P_VRE_ATTRIBUTE19' );
3799
3800 l_vre_attribute20 := hr_transaction_api.get_varchar2_value (
3801 p_transaction_step_id => p_transaction_step_id,
3802 p_name =>'P_VRE_ATTRIBUTE20' );
3803
3804 l_vre_information_category := hr_transaction_api.get_varchar2_value (
3805 p_transaction_step_id => p_transaction_step_id,
3806 p_name =>'P_VRE_INFORMATION_CATEGORY' );
3807
3808 l_vre_information1 := hr_transaction_api.get_varchar2_value (
3809 p_transaction_step_id => p_transaction_step_id,
3810 p_name =>'P_VRE_INFORMATION1' );
3811
3812 l_vre_information2 := hr_transaction_api.get_varchar2_value (
3813 p_transaction_step_id => p_transaction_step_id,
3814 p_name =>'P_VRE_INFORMATION2' );
3815
3816 l_vre_information3 := hr_transaction_api.get_varchar2_value (
3817 p_transaction_step_id => p_transaction_step_id,
3818 p_name =>'P_VRE_INFORMATION3' );
3819
3820 l_vre_information4 := hr_transaction_api.get_varchar2_value (
3821 p_transaction_step_id => p_transaction_step_id,
3822 p_name =>'P_VRE_INFORMATION4' );
3823
3824 l_vre_information5 := hr_transaction_api.get_varchar2_value (
3825 p_transaction_step_id => p_transaction_step_id,
3826 p_name =>'P_VRE_INFORMATION5' );
3827
3828 l_vre_information6 := hr_transaction_api.get_varchar2_value (
3829 p_transaction_step_id => p_transaction_step_id,
3830 p_name =>'P_VRE_INFORMATION6' );
3831
3832 l_vre_information7 := hr_transaction_api.get_varchar2_value (
3833 p_transaction_step_id => p_transaction_step_id,
3834 p_name =>'P_VRE_INFORMATION7' );
3835
3836 l_vre_information8 := hr_transaction_api.get_varchar2_value (
3837 p_transaction_step_id => p_transaction_step_id,
3838 p_name =>'P_VRE_INFORMATION8' );
3839
3840 l_vre_information9 := hr_transaction_api.get_varchar2_value (
3841 p_transaction_step_id => p_transaction_step_id,
3842 p_name =>'P_VRE_INFORMATION9' );
3843
3844 l_vre_information10 := hr_transaction_api.get_varchar2_value (
3845 p_transaction_step_id => p_transaction_step_id,
3846 p_name =>'P_VRE_INFORMATION10' );
3847
3848 l_vre_information11 := hr_transaction_api.get_varchar2_value (
3849 p_transaction_step_id => p_transaction_step_id,
3850 p_name =>'P_VRE_INFORMATION11' );
3851
3852 l_vre_information12 := hr_transaction_api.get_varchar2_value (
3853 p_transaction_step_id => p_transaction_step_id,
3854 p_name =>'P_VRE_INFORMATION12' );
3855
3856 l_vre_information13 := hr_transaction_api.get_varchar2_value (
3857 p_transaction_step_id => p_transaction_step_id,
3858 p_name =>'P_VRE_INFORMATION13' );
3859
3860 l_vre_information14 := hr_transaction_api.get_varchar2_value (
3861 p_transaction_step_id => p_transaction_step_id,
3862 p_name =>'P_VRE_INFORMATION14' );
3863
3864 l_vre_information15 := hr_transaction_api.get_varchar2_value (
3865 p_transaction_step_id => p_transaction_step_id,
3866 p_name =>'P_VRE_INFORMATION15' );
3867
3868 l_vre_information16 := hr_transaction_api.get_varchar2_value (
3869 p_transaction_step_id => p_transaction_step_id,
3870 p_name =>'P_VRE_INFORMATION16' );
3871
3872 l_vre_information17 := hr_transaction_api.get_varchar2_value (
3873 p_transaction_step_id => p_transaction_step_id,
3874 p_name =>'P_VRE_INFORMATION17' );
3875
3876 l_vre_information18 := hr_transaction_api.get_varchar2_value (
3877 p_transaction_step_id => p_transaction_step_id,
3878 p_name =>'P_VRE_INFORMATION18' );
3879
3880
3881 l_vre_information19 := hr_transaction_api.get_varchar2_value (
3882 p_transaction_step_id => p_transaction_step_id,
3883 p_name =>'P_VRE_INFORMATION19' );
3884
3885
3886 l_vre_information20 := hr_transaction_api.get_varchar2_value (
3887 p_transaction_step_id => p_transaction_step_id,
3888 p_name =>'P_VRE_INFORMATION20' );
3889
3890
3891 l_across_assignments := hr_transaction_api.get_varchar2_value (
3892 p_transaction_step_id => p_transaction_step_id,
3893 p_name =>'P_ACROSS_ASSIGNMENTS');
3894
3895
3896 l_usage_type := hr_transaction_api.get_varchar2_value (
3897 p_transaction_step_id => p_transaction_step_id,
3898 p_name =>'P_USAGE_TYPE');
3899
3900 l_default_vehicle := hr_transaction_api.get_varchar2_value (
3901 p_transaction_step_id => p_transaction_step_id,
3902 p_name =>'P_DEFAULT_VEHICLE');
3903
3904 l_fuel_card := hr_transaction_api.get_varchar2_value (
3905 p_transaction_step_id => p_transaction_step_id,
3906 p_name =>'P_FUEL_CARD');
3907
3908 l_fuel_card_number := hr_transaction_api.get_varchar2_value (
3909 p_transaction_step_id => p_transaction_step_id,
3910 p_name =>'P_FUEL_CARD_NUMBER');
3911
3912 l_val_attribute_category := hr_transaction_api.get_varchar2_value (
3913 p_transaction_step_id => p_transaction_step_id,
3914 p_name =>'P_VAL_ATTRIBUTE_CATEGORY');
3915
3916 l_val_attribute1 := hr_transaction_api.get_varchar2_value (
3917 p_transaction_step_id => p_transaction_step_id,
3918 p_name =>'P_VAL_ATTRIBUTE1');
3919
3920 l_val_attribute2 := hr_transaction_api.get_varchar2_value (
3921 p_transaction_step_id => p_transaction_step_id,
3922 p_name =>'P_VAL_ATTRIBUTE2');
3923
3924 l_val_attribute3 := hr_transaction_api.get_varchar2_value (
3925 p_transaction_step_id => p_transaction_step_id,
3926 p_name =>'P_VAL_ATTRIBUTE3');
3927
3928 l_val_attribute4 := hr_transaction_api.get_varchar2_value (
3929 p_transaction_step_id => p_transaction_step_id,
3930 p_name =>'P_VAL_ATTRIBUTE4');
3931
3932 l_val_attribute5 := hr_transaction_api.get_varchar2_value (
3933 p_transaction_step_id => p_transaction_step_id,
3934 p_name =>'P_VAL_ATTRIBUTE5');
3935 l_val_attribute6 :=hr_transaction_api.get_varchar2_value (
3936 p_transaction_step_id => p_transaction_step_id,
3937 p_name =>'P_VAL_ATTRIBUTE6');
3938 l_val_attribute7 :=hr_transaction_api.get_varchar2_value (
3939 p_transaction_step_id => p_transaction_step_id,
3940 p_name =>'P_VAL_ATTRIBUTE7');
3941 l_val_attribute8 :=hr_transaction_api.get_varchar2_value (
3942 p_transaction_step_id => p_transaction_step_id,
3943 p_name =>'P_VAL_ATTRIBUTE8');
3944 l_val_attribute9 :=hr_transaction_api.get_varchar2_value (
3945 p_transaction_step_id => p_transaction_step_id,
3946 p_name =>'P_VAL_ATTRIBUTE9');
3947 l_val_attribute10 :=hr_transaction_api.get_varchar2_value (
3948 p_transaction_step_id => p_transaction_step_id,
3949 p_name =>'P_VAL_ATTRIBUTE10');
3950 l_val_attribute11 := hr_transaction_api.get_varchar2_value (
3951 p_transaction_step_id => p_transaction_step_id,
3952 p_name =>'P_VAL_ATTRIBUTE11');
3953
3954 l_val_attribute12 :=hr_transaction_api.get_varchar2_value (
3955 p_transaction_step_id => p_transaction_step_id,
3956 p_name =>'P_VAL_ATTRIBUTE12');
3957 l_val_attribute13 :=hr_transaction_api.get_varchar2_value (
3958 p_transaction_step_id => p_transaction_step_id,
3959 p_name =>'P_VAL_ATTRIBUTE13');
3960 l_val_attribute14 :=hr_transaction_api.get_varchar2_value (
3961 p_transaction_step_id => p_transaction_step_id,
3962 p_name =>'P_VAL_ATTRIBUTE14');
3963 l_val_attribute15 :=hr_transaction_api.get_varchar2_value (
3964 p_transaction_step_id => p_transaction_step_id,
3965 p_name =>'P_VAL_ATTRIBUTE15');
3966 l_val_attribute16 :=hr_transaction_api.get_varchar2_value (
3967 p_transaction_step_id => p_transaction_step_id,
3968 p_name =>'P_VAL_ATTRIBUTE16');
3969 l_val_attribute17 :=hr_transaction_api.get_varchar2_value (
3970 p_transaction_step_id => p_transaction_step_id,
3971 p_name =>'P_VAL_ATTRIBUTE17');
3972 l_val_attribute18 :=hr_transaction_api.get_varchar2_value (
3973 p_transaction_step_id => p_transaction_step_id,
3974 p_name =>'P_VAL_ATTRIBUTE18');
3975 l_val_attribute19 :=hr_transaction_api.get_varchar2_value (
3976 p_transaction_step_id => p_transaction_step_id,
3977 p_name =>'P_VAL_ATTRIBUTE19');
3978 l_val_attribute20 :=hr_transaction_api.get_varchar2_value (
3979 p_transaction_step_id => p_transaction_step_id,
3980 p_name =>'P_VAL_ATTRIBUTE20');
3981 l_val_information_category := hr_transaction_api.get_varchar2_value (
3982 p_transaction_step_id => p_transaction_step_id,
3983 p_name =>'P_VAL_INFORMATION_CATEGORY');
3984
3985 l_val_information1 := hr_transaction_api.get_varchar2_value (
3986 p_transaction_step_id => p_transaction_step_id,
3987 p_name =>'P_VAL_INFORMATION1');
3988
3989
3990 l_val_information2 := hr_transaction_api.get_varchar2_value (
3991 p_transaction_step_id => p_transaction_step_id,
3992 p_name =>'P_VAL_INFORMATION2');
3993
3994 l_val_information3 := hr_transaction_api.get_varchar2_value (
3995 p_transaction_step_id => p_transaction_step_id,
3996 p_name =>'P_VAL_INFORMATION3');
3997
3998 l_val_information4 := hr_transaction_api.get_varchar2_value (
3999 p_transaction_step_id => p_transaction_step_id,
4000 p_name =>'P_VAL_INFORMATION4');
4001
4002 l_val_information5 := hr_transaction_api.get_varchar2_value (
4003 p_transaction_step_id => p_transaction_step_id,
4004 p_name =>'P_VAL_INFORMATION5');
4005
4006 l_val_information6 := hr_transaction_api.get_varchar2_value (
4007 p_transaction_step_id => p_transaction_step_id,
4008 p_name =>'P_VAL_INFORMATION6');
4009
4010 l_val_information7 := hr_transaction_api.get_varchar2_value (
4011 p_transaction_step_id => p_transaction_step_id,
4012 p_name =>'P_VAL_INFORMATION7');
4013
4014 l_val_information8 := hr_transaction_api.get_varchar2_value (
4015 p_transaction_step_id => p_transaction_step_id,
4016 p_name =>'P_VAL_INFORMATION8');
4017
4018 l_val_information9 := hr_transaction_api.get_varchar2_value (
4019 p_transaction_step_id => p_transaction_step_id,
4020 p_name =>'P_VAL_INFORMATION9');
4021
4022 l_val_information10 := hr_transaction_api.get_varchar2_value (
4023 p_transaction_step_id => p_transaction_step_id,
4024 p_name =>'P_VAL_INFORMATION10');
4025
4026 l_val_information11 := hr_transaction_api.get_varchar2_value (
4027 p_transaction_step_id => p_transaction_step_id,
4028 p_name =>'P_VAL_INFORMATION11');
4029
4030 l_val_information12 := hr_transaction_api.get_varchar2_value (
4031 p_transaction_step_id => p_transaction_step_id,
4032 p_name =>'P_VAL_INFORMATION12');
4033
4034 l_val_information13 := hr_transaction_api.get_varchar2_value (
4035 p_transaction_step_id => p_transaction_step_id,
4036 p_name =>'P_VAL_INFORMATION13');
4037
4038 l_val_information14 := hr_transaction_api.get_varchar2_value (
4039 p_transaction_step_id => p_transaction_step_id,
4040 p_name =>'P_VAL_INFORMATION14');
4041
4042 l_val_information15 := hr_transaction_api.get_varchar2_value (
4043 p_transaction_step_id => p_transaction_step_id,
4044 p_name =>'P_VAL_INFORMATION15');
4045
4046 l_val_information16 := hr_transaction_api.get_varchar2_value (
4047 p_transaction_step_id => p_transaction_step_id,
4048 p_name =>'P_VAL_INFORMATION16');
4049
4050 l_val_information17 := hr_transaction_api.get_varchar2_value (
4051 p_transaction_step_id => p_transaction_step_id,
4052 p_name =>'P_VAL_INFORMATION17');
4053
4054 l_val_information18 := hr_transaction_api.get_varchar2_value (
4055 p_transaction_step_id => p_transaction_step_id,
4056 p_name =>'P_VAL_INFORMATION18');
4057
4058 l_val_information19 := hr_transaction_api.get_varchar2_value (
4059 p_transaction_step_id => p_transaction_step_id,
4060 p_name =>'P_VAL_INFORMATION19');
4061
4062 l_val_information20 := hr_transaction_api.get_varchar2_value (
4063 p_transaction_step_id => p_transaction_step_id,
4064 p_name =>'P_VAL_INFORMATION20');
4065
4066 l_fuel_benefit := hr_transaction_api.get_varchar2_value (
4067 p_transaction_step_id => p_transaction_step_id,
4068 p_name =>'P_FUEL_BENEFIT');
4069
4070
4071 l_vehicle_repository_id := hr_transaction_api.get_number_value (
4072 p_transaction_step_id => p_transaction_step_id,
4073 p_name =>'P_VEHICLE_REPOSITORY_ID');
4074 l_vehicle_allocation_id := hr_transaction_api.get_number_value (
4075 p_transaction_step_id => p_transaction_step_id,
4076 p_name =>'P_VEHICLE_ALLOCATION_ID');
4077 l_object_version_number := hr_transaction_api.get_number_value (
4078 p_transaction_step_id => p_transaction_step_id,
4079 p_name =>'P_OBJECT_VERSION_NUMBER');
4080
4081
4082 OPEN c_get_count;
4083 FETCH c_get_count INTO l_get_count;
4084 CLOSE c_get_count;
4085
4086 FOR i in 1..l_get_count
4087 LOOP
4088 l_user_info (i).person_id :=hr_transaction_api.get_number_value (
4089 p_transaction_step_id => p_transaction_step_id,
4090 p_name =>'P_PERSON_USR_ID'||i);
4091
4092 l_user_info (i).assignment_id :=hr_transaction_api.get_number_value (
4093 p_transaction_step_id => p_transaction_step_id,
4094 p_name =>'P_ASSIGNMENT_USR_ID'||i);
4095
4096
4097 l_user_info (i).user_type :=hr_transaction_api.get_varchar2_value (
4098 p_transaction_step_id => p_transaction_step_id,
4099 p_name =>'P_USER_TYPE'||i);
4100
4101
4102 END LOOP;
4103
4104
4105
4106
4107 create_vehicle_details
4108 (
4109 p_validate => false
4110 ,p_effective_date => l_effective_date
4111 ,p_registration_number =>l_registration_number
4112 ,p_vehicle_type =>l_vehicle_type
4113 ,p_vehicle_id_number =>l_vehicle_id_number
4114 ,p_business_group_id =>l_business_group_id
4115 ,p_make =>l_make
4116 ,p_engine_capacity_in_cc =>l_engine_capacity_in_cc
4117 ,p_fuel_type =>l_fuel_type
4118 ,p_currency_code =>l_currency_code
4119 ,p_model =>l_model
4120 ,p_initial_registration =>l_initial_registration
4121 ,p_last_registration_renew_date =>l_last_registration_renew_date
4122 ,p_fiscal_ratings =>l_fiscal_ratings
4123 ,p_vehicle_ownership =>l_vehicle_ownership
4124 ,p_shared_vehicle =>l_shared_vehicle
4125 ,p_color =>l_color
4126 ,p_seating_capacity =>l_seating_capacity
4127 ,p_weight =>l_weight
4128 ,p_weight_uom =>l_weight_uom
4129 ,p_model_year =>l_model_year
4130 ,p_insurance_number =>l_insurance_number
4131 ,p_insurance_expiry_date =>l_insurance_expiry_date
4132 ,p_taxation_method =>l_taxation_method
4133 ,p_comments =>l_comments
4134 ,p_vre_attribute_category =>l_vre_attribute_category
4135 ,p_vre_attribute1 =>l_vre_attribute1
4136 ,p_vre_attribute2 =>l_vre_attribute2
4137 ,p_vre_attribute3 =>l_vre_attribute3
4138 ,p_vre_attribute4 =>l_vre_attribute4
4139 ,p_vre_attribute5 =>l_vre_attribute5
4140 ,p_vre_attribute6 =>l_vre_attribute6
4141 ,p_vre_attribute7 =>l_vre_attribute7
4142 ,p_vre_attribute8 =>l_vre_attribute8
4143 ,p_vre_attribute9 =>l_vre_attribute9
4144 ,p_vre_attribute10 =>l_vre_attribute10
4145 ,p_vre_attribute11 =>l_vre_attribute11
4146 ,p_vre_attribute12 =>l_vre_attribute12
4147 ,p_vre_attribute13 =>l_vre_attribute13
4148 ,p_vre_attribute14 =>l_vre_attribute14
4149 ,p_vre_attribute15 =>l_vre_attribute15
4150 ,p_vre_attribute16 =>l_vre_attribute16
4151 ,p_vre_attribute17 =>l_vre_attribute17
4152 ,p_vre_attribute18 =>l_vre_attribute18
4153 ,p_vre_attribute19 =>l_vre_attribute19
4154 ,p_vre_attribute20 =>l_vre_attribute20
4155 ,p_vre_information_category =>l_vre_information_category
4156 ,p_vre_information1 =>l_vre_information1
4157 ,p_vre_information2 =>l_vre_information2
4158 ,p_vre_information3 =>l_vre_information3
4159 ,p_vre_information4 =>l_vre_information4
4160 ,p_vre_information5 =>l_vre_information5
4161 ,p_vre_information6 =>l_vre_information6
4162 ,p_vre_information7 =>l_vre_information7
4163 ,p_vre_information8 =>l_vre_information8
4164 ,p_vre_information9 =>l_vre_information9
4165 ,p_vre_information10 =>l_vre_information10
4166 ,p_vre_information11 =>l_vre_information11
4167 ,p_vre_information12 =>l_vre_information12
4168 ,p_vre_information13 =>l_vre_information13
4169 ,p_vre_information14 =>l_vre_information14
4170 ,p_vre_information15 =>l_vre_information15
4171 ,p_vre_information16 =>l_vre_information16
4172 ,p_vre_information17 =>l_vre_information17
4173 ,p_vre_information18 =>l_vre_information18
4174 ,p_vre_information19 =>l_vre_information19
4175 ,p_vre_information20 =>l_vre_information20
4176 ,p_across_assignments =>l_across_assignments
4177 ,p_usage_type =>l_usage_type
4178 ,p_default_vehicle =>l_default_vehicle
4179 ,p_fuel_card =>l_fuel_card
4180 ,p_fuel_card_number =>l_fuel_card_number
4181 ,p_val_attribute_category =>l_val_attribute_category
4182 ,p_val_attribute1 =>l_val_attribute1
4183 ,p_val_attribute2 =>l_val_attribute2
4184 ,p_val_attribute3 =>l_val_attribute3
4185 ,p_val_attribute4 =>l_val_attribute4
4186 ,p_val_attribute5 =>l_val_attribute5
4187 ,p_val_attribute6 =>l_val_attribute6
4188 ,p_val_attribute7 =>l_val_attribute7
4189 ,p_val_attribute8 =>l_val_attribute8
4190 ,p_val_attribute9 =>l_val_attribute9
4191 ,p_val_attribute10 =>l_val_attribute10
4192 ,p_val_attribute11 =>l_val_attribute11
4193 ,p_val_attribute12 =>l_val_attribute12
4194 ,p_val_attribute13 =>l_val_attribute13
4195 ,p_val_attribute14 =>l_val_attribute14
4196 ,p_val_attribute15 =>l_val_attribute15
4197 ,p_val_attribute16 =>l_val_attribute16
4198 ,p_val_attribute17 =>l_val_attribute17
4199 ,p_val_attribute18 =>l_val_attribute18
4200 ,p_val_attribute19 =>l_val_attribute19
4201 ,p_val_attribute20 =>l_val_attribute20
4202 ,p_val_information_category =>l_val_information_category
4203 ,p_val_information1 =>l_val_information1
4204 ,p_val_information2 =>l_val_information2
4205 ,p_val_information3 =>l_val_information3
4206 ,p_val_information4 =>l_val_information4
4207 ,p_val_information5 =>l_val_information5
4208 ,p_val_information6 =>l_val_information6
4209 ,p_val_information7 =>l_val_information7
4210 ,p_val_information8 =>l_val_information8
4211 ,p_val_information9 =>l_val_information9
4212 ,p_val_information10 =>l_val_information10
4213 ,p_val_information11 =>l_val_information11
4214 ,p_val_information12 =>l_val_information12
4215 ,p_val_information13 =>l_val_information13
4216 ,p_val_information14 =>l_val_information14
4217 ,p_val_information15 =>l_val_information15
4218 ,p_val_information16 =>l_val_information16
4219 ,p_val_information17 =>l_val_information17
4220 ,p_val_information18 =>l_val_information18
4221 ,p_val_information19 =>l_val_information19
4222 ,p_val_information20 =>l_val_information20
4223 ,p_fuel_benefit =>l_fuel_benefit
4224 ,p_user_info =>l_user_info
4225 ,p_vehicle_repository_id =>l_vehicle_repository_id
4226 ,p_vehicle_allocation_id =>l_vehicle_allocation_id
4227 ,p_object_version_number =>l_object_version_number
4228 ,p_error_message =>l_error_message
4229 ,p_error_status =>l_error_status
4230 );
4231 l_assignment_id:=l_user_info(1).assignment_id;
4232 OPEN c_get_other_tstep;
4233 LOOP
4234 FETCH c_get_other_tstep INTO l_get_other_tstep;
4235 EXIT WHEN c_get_other_tstep%NOTFOUND;
4236
4237
4238 END LOOP;
4239 CLOSE c_get_other_tstep;
4240
4241 OPEN c_get_details;
4242 FETCH c_get_details INTO l_get_details;
4243 CLOSE c_get_details;
4244
4245 wf_engine.SetItemAttrNumber( itemtype =>l_get_other_tstep.item_type,
4246 itemkey =>l_get_other_tstep.item_key,
4247 aname =>'PQP_VEH_REPOSITORY_ID_ATTR',
4248 avalue =>l_get_details.vehicle_repository_id);
4249
4250 wf_engine.SetItemAttrNumber( itemtype =>l_get_other_tstep.item_type,
4251 itemkey =>l_get_other_tstep.item_key,
4252 aname =>'PQP_VEH_ALLOCATION_ID_ATTR',
4253 avalue =>l_get_details.vehicle_allocation_id);
4254
4255 --
4256 --
4257 --
4258 hr_utility.set_location('Leaving: process_api',10);
4259 --hr_utility.trace_off;
4260 EXCEPTION
4261 WHEN hr_utility.hr_error THEN
4262 ROLLBACK TO process_veh_details;
4263
4264 hr_utility.raise_error;
4265 WHEN OTHERS THEN
4266 RAISE; -- Raise error here relevant to the new tech stack.
4267 END process_api;
4268
4269 --
4270 ---Delete call
4271 PROCEDURE delete_allocation(
4272 p_validate IN BOOLEAN
4273 ,p_effective_date IN DATE
4274 ,p_assignment_id IN NUMBER
4275 ,p_vehicle_allocation_id IN NUMBER
4276 ,p_business_group_id IN NUMBER
4277 ,p_error_status OUT NOCOPY VARCHAR2
4278 )
4279
4280 IS
4281
4282 CURSOR c_get_ovn (cp_allocation_id NUMBER
4283 ,cp_business_group_id NUMBER
4284 ,cp_assignment_id NUMBER
4285 ,cp_effective_date DATE
4286 )
4287 IS
4288 SELECT pva.object_version_number
4289 ,pva.vehicle_repository_id repository_id
4290 FROM pqp_vehicle_allocations_f pva
4291 WHERE pva.vehicle_allocation_id =cp_allocation_id
4292 AND pva.assignment_id =cp_assignment_id
4293 AND pva.business_group_id =cp_business_group_id
4294 AND rtrim(ltrim(cp_effective_date)) BETWEEN pva.effective_start_date
4295 AND pva.effective_end_date;
4296
4297 CURSOR c_get_tot_users (cp_repository_id NUMBER
4298 ,cp_business_group_id NUMBER
4299 ,cp_assignment_id NUMBER
4300 ,cp_effective_date DATE
4301 )
4302 IS
4303 SELECT COUNT(pva.vehicle_repository_id) usr_count
4304 FROM pqp_vehicle_allocations_f pva
4305 WHERE pva.vehicle_repository_id = cp_repository_id
4306 AND pva.business_group_id =cp_business_group_id
4307 AND pva.assignment_id = cp_assignment_id
4308 AND rtrim(ltrim(cp_effective_date)) BETWEEN pva.effective_start_date
4309 AND pva.effective_end_date;
4310
4311 CURSOR c_get_rep_ovn (cp_repository_id NUMBER
4312 ,cp_business_group_id NUMBER
4313 ,cp_effective_date DATE
4314 )
4315 IS
4316 SELECT pvr.object_version_number ovn
4317 FROM pqp_vehicle_repository_f pvr
4318 WHERE pvr.vehicle_repository_id = cp_repository_id
4319 AND pvr.business_group_id = cp_business_group_id
4320 AND rtrim(ltrim(cp_effective_date)) BETWEEN pvr.effective_start_date
4321 AND pvr.effective_end_date;
4322
4323 l_get_rep_ovn c_get_rep_ovn%ROWTYPE;
4324 l_get_ovn c_get_ovn%ROWTYPE;
4325 l_get_tot_users c_get_tot_users%ROWTYPE;
4326 l_effective_start_date DATE;
4327 l_effective_end_date DATE;
4328
4329 BEGIN
4330
4331 hr_utility.set_location('Leaving: process_api',10);
4332
4333
4334 OPEN c_get_ovn (
4335 p_vehicle_allocation_id
4336 ,p_business_group_id
4337 ,p_assignment_id
4338 ,p_effective_date
4339 );
4340 FETCH c_get_ovn INTO l_get_ovn;
4341 CLOSE c_get_ovn;
4342
4343 OPEN c_get_tot_users (l_get_ovn.repository_id
4344 ,p_business_group_id
4345 ,p_assignment_id
4346 ,p_effective_date
4347 );
4348 FETCH c_get_tot_users INTO l_get_tot_users;
4349 CLOSE c_get_tot_users;
4350
4351
4352 --Calling delete api for allocation.
4353 PQP_VEHICLE_ALLOCATIONS_API.delete_vehicle_allocation(
4354 p_validate => p_validate
4355 ,p_effective_date => ltrim(rtrim(p_effective_date))
4356 ,p_datetrack_mode =>'DELETE'
4357 ,p_vehicle_allocation_id =>p_vehicle_allocation_id
4358 ,p_object_version_number =>l_get_ovn.object_version_number
4359 ,p_effective_start_date =>l_effective_start_date
4360 ,p_effective_end_date =>l_effective_end_date
4361 );
4362
4363 IF l_get_tot_users.usr_count = 0 THEN
4364 OPEN c_get_rep_ovn (l_get_ovn.repository_id
4365 ,p_business_group_id
4366 ,p_effective_date
4367 );
4368 FETCH c_get_rep_ovn INTO l_get_rep_ovn;
4369
4370 CLOSE c_get_rep_ovn;
4371
4372 --Callin delete api for vehicles.
4373 pqp_vehicle_repository_api.delete_vehicle
4374 (p_validate => p_validate
4375 ,p_effective_date => ltrim(rtrim(p_effective_date))
4376 ,p_datetrack_mode => 'DELETE'
4377 ,p_vehicle_repository_id => l_get_ovn.repository_id
4378 ,p_object_version_number => l_get_rep_ovn.ovn
4379 ,p_effective_start_date => l_effective_start_date
4380 ,p_effective_end_date => l_effective_end_date
4381 );
4382 END IF;
4383
4384 p_error_status := 'Y';
4385
4386 EXCEPTION
4387 WHEN hr_utility.hr_error THEN
4388 p_error_status := 'N';
4389 hr_utility.raise_error;
4390 WHEN OTHERS THEN
4391 RAISE; -- Raise error here relevant to the new tech stack.
4392
4393 END delete_allocation;
4394 END PQP_SS_VEHICLE_TRANSACTIONS;