[Home] [Help]
PACKAGE BODY: APPS.PQP_SS_VEHICLE_MILEAGE_CLAIMS
Source
1 PACKAGE BODY PQP_SS_VEHICLE_MILEAGE_CLAIMS AS
2 /* $Header: pqpssvehmlgclm.pkb 120.0 2005/05/29 02:23:13 appldev noship $*/
3
4 --This function gets the confirmation number which is
5 --the transaction step id which was created originally
6 --when the claim is created.
7 --This function is currently not used as it was decided that
8 --the confirmation number will not be given.
9 --if the confirmation number is decided to be shown then
10 -- we need to uncomment in few places where metioned
11 --uncomment for conf
12 FUNCTION get_conf_number (p_element_entry_id IN NUMBER)
13 RETURN NUMBER
14 IS
15
16 CURSOR c_get_conf_no (cp_element_entry_id NUMBER)
17 IS
18 SELECT psvh.value conf_number
19 FROM pqh_ss_value_history psvh
20 WHERE psvh.name='P_CONFIRMATION_NUMBER'
21 AND psvh.value IS NOT NULL
22 AND psvh.step_history_id IN
23 (SELECT psvh1.step_history_id
24 FROM pqh_ss_value_history psvh1
25 WHERE psvh1.name='P_ELEMENT_ENTRY_ID'
26 AND psvh1.value =cp_element_entry_id);
27
28
29 l_get_conf_no c_get_conf_no%ROWTYPE;
30
31
32 BEGIN
33 OPEN c_get_conf_no (p_element_entry_id);
34 LOOP
35 FETCH c_get_conf_no INTO l_get_conf_no;
36 EXIT WHEN c_get_conf_no%NOTFOUND;
37 END LOOP;
38 RETURN TO_NUMBER(l_get_conf_no.conf_number);
39 END;
40
41 --This function will be used in the work flow to determine
42 --which page to open based on the update,insert and delete.
43
44 PROCEDURE get_dml_status (
45 itemtype IN VARCHAR2,
46 itemkey IN VARCHAR2,
47 actid IN NUMBER,
48 funcmode IN VARCHAR2,
49 result OUT NOCOPY VARCHAR2 )
50
51 IS
52 l_transaction_id number;
53 l_attr varchar2(30);
54 l_ignore boolean ;
55 BEGIN
56
57 --Get the status whether the process is in Update or Insert mode
58
59 l_attr:= wf_engine.GetItemAttrText(
60 itemtype => itemtype,
61 itemkey =>itemkey,
62 aname =>'PQP_DML_STATUS_TYPE_ATTR',
63 ignore_notfound =>l_ignore);
64
65 --Set the same value to determine the process
66 result := l_attr ; --'COMPLETE:I' ; --l_attr;
67 END;
68
69 --This function is not used currently,but may need when the
70 --pages are not fully based on WF.
71 PROCEDURE update_transaction_itemkey (
72 itemtype IN VARCHAR2,
73 itemkey IN VARCHAR2,
74 actid IN NUMBER,
75 funcmode IN VARCHAR2,
76 result OUT NOCOPY VARCHAR2 )
77
78 IS
79 l_transaction_id number;
80 l_attr varchar2(30);
81 l_ignore boolean ;
82 BEGIN
83 l_transaction_id:= wf_engine.GetItemAttrNumber(
84 itemtype => itemtype,
85 itemkey =>itemkey,
86 aname => 'TRANSACTION_ID',
87 ignore_notfound =>l_ignore);
88
89 --Check if approval flag is set to yes in the WF
90
91 l_attr:= wf_engine.GetActivityAttrText(
92 itemtype => itemtype,
93 itemkey =>itemkey,
94 aname =>'PQP_APPROVAL_REQUIRED',
95 actid =>actid , --'PQP_APPROVAL_REQUIRED',
96 ignore_notfound =>l_ignore);
97
98 --Set the same value to determine the process
99 wf_engine.SetItemAttrText(itemtype => itemtype,
100 itemkey =>itemkey,
101 aname =>'HR_RUNTIME_APPROVAL_REQ_FLAG',
102 avalue =>'YES');
103
104 UPDATE hr_api_transactions hat
105 SET hat.item_key = itemkey
106 WHERE hat.transaction_id=l_transaction_id;
107
108 UPDATE hr_api_transaction_steps hats
109 SET hats.item_key = itemkey
110 WHERE hats.transaction_id=l_transaction_id;
111 result :='Y';
112 end;
113
114
115 PROCEDURE rollback_transaction(
116 itemType IN VARCHAR2,
117 itemKey IN VARCHAR2,
118 result OUT NOCOPY VARCHAR2) IS
119 BEGIN
120 --
121 savepoint rollback_transaction;
122 --
123 wf_engine.setItemAttrNumber (
124 itemType => itemType,
125 itemKey => itemKey,
126 aname => 'TRANSACTION_ID',
127 avalue => null );
128 --
129 --
130 hr_transaction_ss.rollback_transaction (
131 itemType => itemType,
132 itemKey => itemKey,
133 actid => 0,
134 funmode => 'RUN',
135 result => result );
136 --
137 --
138 result := 'SUCCESS';
139 --
140 --
141 EXCEPTION
142 --
143 WHEN Others THEN
144 rollback to rollback_transaction;
145 result := 'FAILURE';
146 --
147 END;
148
149 --
150 --
151 --This function is used to get the values that are inserted into
152 --the transaction table, this can be used during back button click
153 --or to show the data from the query page.
154 FUNCTION get_vehicle_mileage_claim (
155 p_transaction_step_id IN VARCHAR2
156 )
157 RETURN ref_cursor
158 IS
159 csr ref_cursor;
160 BEGIN
161 OPEN csr FOR
162 SELECT
163 hr_transaction_api.get_number_Value
164 (p_transaction_step_id, 'P_LOGIN_PERSON_ID') login_person_id
165 ,hr_transaction_api.get_number_Value
166 (p_transaction_step_id, 'P_PERSON_ID' ) person_id
167 ,hr_transaction_api.get_number_Value
168 (p_transaction_step_id, 'P_ASSIGNMENT_ID') assignment_id
169 ,hr_transaction_api.get_number_Value
170 (p_transaction_step_id ,'P_ITEM_TYPE') item_type
171 ,hr_transaction_api.get_number_Value
172 (p_transaction_step_id, 'P_ITEM_KEY') item_key
173 ,hr_transaction_api.get_number_Value
174 (p_transaction_step_id, 'P_ACTIVITY_ID' ) activity_id
175 ,hr_transaction_api.get_varchar2_Value
176 (p_transaction_step_id, 'P_REGISTRATION_NUMBER' ) registration_number
177 ,hr_transaction_api.get_varchar2_Value
178 (p_transaction_step_id, 'P_OWNERSHIP') ownership
179 ,hr_transaction_api.get_varchar2_Value
180 (p_transaction_step_id, 'P_VEHICLE_TYPE' ) vehicle_type
181 ,hr_transaction_api.get_date_Value
182 (p_transaction_step_id, 'P_START_DATE') start_date
183 ,hr_transaction_api.get_date_Value
184 (p_transaction_step_id, 'P_END_DATE' ) end_date
185 ,hr_transaction_api.get_varchar2_Value
186 (p_transaction_step_id, 'P_CLAIMED_MILEAGE' ) claimed_mileage
187 ,hr_transaction_api.get_varchar2_Value
188 (p_transaction_step_id, 'P_ACTUAL_MILEAGE' ) actual_mileage
189 ,hr_transaction_api.get_number_Value
190 (p_transaction_step_id, 'P_BUSINESS_GROUP_ID') business_group_id
191 ,hr_transaction_api.get_varchar2_Value
192 (p_transaction_step_id, 'P_ENGINE_CAPACITY' ) engine_capacity
193 ,hr_transaction_api.get_varchar2_Value
194 (p_transaction_step_id, 'P_FUEL_TYPE' ) fuel_type
195 ,hr_transaction_api.get_varchar2_Value
196 (p_transaction_step_id, 'P_FISCAL_RATINGS') fiscal_ratings
197 ,hr_transaction_api.get_varchar2_Value
198 (p_transaction_step_id, 'P_USAGE_TYPE') usage_type
199 ,hr_transaction_api.get_varchar2_Value
200 (p_transaction_step_id, 'P_NO_OF_PASSENGERS') no_of_passengers
201 ,hr_transaction_api.get_varchar2_Value
202 (p_transaction_step_id, 'P_PURPOSE') purpose
203 ,hr_transaction_api.get_number_Value
204 (p_transaction_step_id, 'P_ELEMENT_ENTRY_ID') element_entry_id
205 ,hr_transaction_api.get_number_Value
206 (p_transaction_step_id, 'P_OBJECT_VERSION_NUMBER') object_version_number
207 ,hr_transaction_api.get_date_Value
208 (p_transaction_step_id, 'P_EFFECTIVE_DATE') p_effective_date
209 FROM dual;
210
211 RETURN csr;
212 END get_vehicle_mileage_claim;
213
214 --
215 --
216 --This procedure is called before the data is inserted into transaction table,
217 --This procedure makes sure the data entered is valid by validating against
218 --the base table.
219 PROCEDURE create_validate_mileage_claim
220 (
221 p_effective_date IN DATE,
222 p_web_adi_identifier IN VARCHAR2 ,
223 p_info_id IN VARCHAR2 ,
224 p_time_stamp IN VARCHAR2 ,
225 p_assignment_id IN NUMBER ,
226 p_business_group_id IN NUMBER ,
227 p_ownership IN VARCHAR2 ,
228 p_usage_type IN VARCHAR2 ,
229 p_vehicle_type IN VARCHAR2 ,
230 p_start_date IN DATE ,
231 p_end_date IN DATE ,
232 p_claimed_mileage IN VARCHAR2 ,
233 p_actual_mileage IN VARCHAR2 ,
234 p_claimed_mileage_o IN VARCHAR2 ,
235 p_actual_mileage_o IN VARCHAR2 ,
236 p_registration_number IN VARCHAR2 ,
237 p_engine_capacity IN VARCHAR2 ,
238 p_fuel_type IN VARCHAR2 ,
239 p_fiscal_ratings IN VARCHAR2 ,
240 p_no_of_passengers IN VARCHAR2 ,
241 p_purpose IN VARCHAR2 ,
242 p_user_type IN VARCHAR2 ,
243 p_mileage_claim_element IN OUT NOCOPY NUMBER ,
244 p_element_entry_id IN OUT NOCOPY NUMBER ,
245 p_element_entry_date IN OUT NOCOPY DATE,
246 p_mode OUT NOCOPY VARCHAR2,
247 p_return_status OUT NOCOPY VARCHAR2
248
249 )
250 IS
251
252 pragma autonomous_transaction;
253 BEGIN
254 create_vehicle_mileage_claims
255 (
256 p_effective_date => p_effective_date,
257 p_web_adi_identifier => p_web_adi_identifier ,
258 p_info_id => p_info_id,
259 p_time_stamp => p_time_stamp,
260 p_assignment_id => p_assignment_id,
261 p_business_group_id => p_business_group_id,
262 p_ownership => p_ownership,
263 p_usage_type => p_usage_type,
264 p_vehicle_type => p_vehicle_type,
265 p_start_date => p_start_date,
266 p_end_date => p_end_date,
267 p_claimed_mileage => p_claimed_mileage,
268 p_actual_mileage => p_actual_mileage,
269 p_claimed_mileage_o => p_claimed_mileage_o,
270 p_actual_mileage_o => p_actual_mileage_o,
271 p_registration_number => p_registration_number,
272 p_engine_capacity => p_engine_capacity,
273 p_fuel_type => p_fuel_type,
274 p_fiscal_ratings => p_fiscal_ratings,
275 p_no_of_passengers => p_no_of_passengers,
276 p_purpose => p_purpose,
277 p_user_type => p_user_type,
278 p_mileage_claim_element => p_mileage_claim_element,
279 p_element_entry_id => p_element_entry_id,
280 p_element_entry_date => p_element_entry_date,
281 p_mode => p_mode,
282 p_return_status => p_return_status
283 );
284 ROLLBACK;
285 EXCEPTION
286 WHEN hr_utility.hr_error THEN
287 hr_utility.raise_error;
288 WHEN OTHERS THEN
289 RAISE; -- Raise error here relevant to the new tech stack.
290 END;
291
292
293
294 -- ----------------------------------------------------------------------------
295 -- |------------------------< get_transaction_id >----------------------------|
296 -- ----------------------------------------------------------------------------
297 PROCEDURE get_transaction_id
298 (p_transaction_step_id IN NUMBER
299 ,p_transaction OUT NOCOPY NUMBER) is
300 -- --------------------------------------------------------------------------
301 -- declare local variables
302 -- --------------------------------------------------------------------------
303 l_proc varchar2(72);
304 l_transaction_id hr_api_transactions.transaction_id%type;
305 -- cursor to select the transaction_id of the step
306 cursor csr_hats is
307 select hats.transaction_id
308 from hr_api_transaction_steps hats
309 where hats.transaction_step_id = p_transaction_step_id;
310 begin
311
312
313 open csr_hats;
314 fetch csr_hats into l_transaction_id;
315 if csr_hats%notfound then
316 -- the transaction step doesn't exist
317 close csr_hats;
318 hr_utility.set_message(801, 'HR_51751_WEB_TRA_STEP_EXISTS');
319 hr_utility.raise_error;
320 end if;
321 close csr_hats;
322 p_transaction := l_transaction_id;
323
324
325
326 end get_transaction_id;
327 --This procedure creates mileage claims after the approval process.
328 --This procedure handles both update and create.
329
330 PROCEDURE create_vehicle_mileage_claims
331 (
332 p_effective_date IN DATE,
333 p_web_adi_identifier IN VARCHAR2 ,
334 p_info_id IN VARCHAR2 ,
335 p_time_stamp IN VARCHAR2 ,
336 p_assignment_id IN NUMBER,
337 p_business_group_id IN NUMBER,
338 p_ownership IN VARCHAR2 ,
339 p_usage_type IN VARCHAR2 ,
340 p_vehicle_type IN VARCHAR2,
341 p_start_date IN VARCHAR2 ,
342 p_end_date IN VARCHAR2 ,
343 p_claimed_mileage IN VARCHAR2 ,
344 p_actual_mileage IN VARCHAR2 ,
345 p_claimed_mileage_o IN VARCHAR2 ,
346 p_actual_mileage_o IN VARCHAR2 ,
347 p_registration_number IN VARCHAR2 ,
348 p_engine_capacity IN VARCHAR2 ,
349 p_fuel_type IN VARCHAR2 ,
350 p_fiscal_ratings IN VARCHAR2 ,
351 p_no_of_passengers IN VARCHAR2 ,
352 p_purpose IN VARCHAR2 ,
353 p_user_type IN VARCHAR2 ,
354 p_mileage_claim_element IN OUT NOCOPY NUMBER ,
355 p_element_entry_id IN OUT NOCOPY NUMBER ,
356 p_element_entry_date IN OUT NOCOPY DATE,
357 p_mode OUT NOCOPY VARCHAR2,
358 p_return_status OUT NOCOPY VARCHAR2
359
360
361 )
362 IS
363
364
365 BEGIN
366 IF p_element_entry_id is NULL THEN
367 pqp_gb_mileage_claim_pkg.insert_mileage_claim
368 (
369 p_effective_date => RTRIM(LTRIM(p_effective_date)),
370 p_web_adi_identifier => p_web_adi_identifier,
371 p_info_id => p_info_id,
372 p_time_stamp => p_time_stamp,
373 p_assignment_id => p_assignment_id,
374 p_business_group_id => p_business_group_id,
375 p_ownership => p_ownership,
376 p_usage_type => p_usage_type,
377 p_vehicle_type => p_vehicle_type,
378 p_start_date => p_start_date,
379 p_end_date => p_end_date,
380 p_claimed_mileage => p_claimed_mileage,
381 p_actual_mileage => p_actual_mileage,
382 p_registration_number => p_registration_number,
383 p_engine_capacity => p_engine_capacity,
384 p_fuel_type => p_fuel_type,
385 p_fiscal_ratings => p_fiscal_ratings,
386 p_no_of_passengers => p_no_of_passengers,
387 p_purpose => p_purpose,
388 p_user_type => p_user_type,
389 p_mileage_claim_element => p_mileage_claim_element,
390 p_element_entry_id => p_element_entry_id,
391 p_element_entry_date => p_element_entry_date
392 );
393
394
395
396 p_mode:='I';
397
398 ELSE
399 pqp_gb_mileage_claim_pkg.update_mileage_claim
400 (
401 p_effective_date => RTRIM(LTRIM(p_effective_date)),
402 p_assignment_id => p_assignment_id,
403 p_business_group_id => p_business_group_id,
404 p_ownership => p_ownership,
405 p_usage_type => p_usage_type,
406 p_vehicle_type => p_vehicle_type,
407 p_start_date => p_start_date,
408 p_end_date => p_end_date,
409 p_claimed_mileage_o => p_claimed_mileage_o,
410 p_claimed_mileage => p_claimed_mileage,
411 p_actual_mileage_o => p_actual_mileage_o,
412 p_actual_mileage => p_actual_mileage,
413 p_registration_number => p_registration_number,
414 p_engine_capacity => p_engine_capacity,
415 p_fuel_type => p_fuel_type,
416 p_fiscal_ratings => p_fiscal_ratings,
417 p_no_of_passengers => p_no_of_passengers,
418 p_purpose => p_purpose,
419 p_mileage_claim_element => p_mileage_claim_element,
420 p_element_entry_id => p_element_entry_id,
421 p_element_entry_date => p_element_entry_date
422 );
423 p_mode:='U';
424 END IF;
425
426 EXCEPTION
427 WHEN hr_utility.hr_error THEN
428 hr_utility.raise_error;
429 WHEN OTHERS THEN
430 RAISE; -- Raise error here relevant to the new tech stack.
431 END;
432
433 PROCEDURE delete_validate_mileage_claim (
434 p_effective_date IN DATE
435 ,p_assignment_id IN NUMBER
436 ,p_mileage_claim_element IN NUMBER
437 ,p_element_entry_id IN OUT NOCOPY NUMBER
438 ,p_element_entry_date IN OUT NOCOPY DATE
439 ,p_error_status OUT NOCOPY VARCHAR2
440 )
441
442 IS
443 pragma autonomous_transaction;
444 l_mileage_claim_element NUMBER;
445 BEGIN
446 pqp_mileage_claim_pkg.pqp_delete_mileage_claim
447 ( p_effective_date =>p_effective_date,
448 p_assignment_id =>p_assignment_id,
449 p_mileage_claim_element =>l_mileage_claim_element ,
450 p_element_entry_id =>p_element_entry_id ,
451 p_element_entry_date =>p_element_entry_date,
452 p_return_status =>p_error_status
453 );
454 ROLLBACK;
455 EXCEPTION
456 WHEN hr_utility.hr_error THEN
457 hr_utility.raise_error;
458 WHEN OTHERS THEN
459 RAISE; -- Raise error here relevant to the new tech stack.
460
461
462 END;
463
464
465 PROCEDURE delete_vehicle_mileage_claim(
466 x_effective_date IN DATE
467 ,x_login_person_id IN NUMBER
468 ,x_person_id IN NUMBER
469 ,x_assignment_id IN NUMBER
470 ,x_business_group_id IN NUMBER
471 ,x_item_key IN NUMBER
472 ,x_item_type IN VARCHAR2
473 ,x_element_entry_id IN NUMBER
474 ,p_status IN VARCHAR2
475 ,x_transaction_id IN OUT NOCOPY NUMBER
476 ,x_transaction_step_id IN OUT NOCOPY NUMBER
477 ,x_confirmation_number OUT NOCOPY NUMBER
478 ,x_error_status OUT NOCOPY VARCHAR2
479 )
480
481 IS
482
483
484
485 CURSOR c_del_values
486 IS
487 SELECT transaction_step_id
488 FROM hr_api_transaction_steps hats
489 WHERE transaction_id = x_transaction_id;
490
491 l_del_values c_del_values%ROWTYPE;
492 l_transaction_id NUMBER;
493 l_trans_tbl hr_transaction_ss.transaction_table;
494 l_count NUMBER :=0;
495 l_transaction_step_id NUMBER;
496 l_api_name hr_api_transaction_steps.api_name%TYPE
497 := 'PQP_SS_VEHICLE_MILEAGE_CLAIMS.DELETE_PROCESS_API';
498 l_result VARCHAR2(100);
499 l_trns_object_version_number NUMBER;
500 l_review_proc_call VARCHAR2(30);-- := 'PqpVehStatusReview';
501 l_effective_date DATE := SYSDATE;
502 l_ovn NUMBER ;
503 l_error_message VARCHAR2(80);
504 l_error_status VARCHAR2(10);
505 l_mileage_claim_element NUMBER;
506 l_element_entry_date DATE ;
507 l_element_entry_id NUMBER;
508 BEGIN
509
510 --Validate the data before inserting into
511 -- transaction table.
512 l_element_entry_id :=x_element_entry_id;
513 delete_validate_mileage_claim
514 ( p_effective_date =>x_effective_date,
515 p_assignment_id =>x_assignment_id,
516 p_mileage_claim_element =>l_mileage_claim_element ,
517 p_element_entry_id =>l_element_entry_id ,
518 p_element_entry_date =>l_element_entry_date,
519 p_error_status =>l_error_status
520 );
521
522
523
524
525 l_count:=1;
526 l_trans_tbl(l_count).param_name := 'P_LOGIN_PERSON_ID';
527 l_trans_tbl(l_count).param_value := x_login_person_id;
528 l_trans_tbl(l_count).param_data_type := 'NUMBER';
529
530 l_count:=l_count+1;
531 l_trans_tbl(l_count).param_name := 'P_EFFECTIVE_DATE';
532 l_trans_tbl(l_count).param_value := x_effective_date;
533 l_trans_tbl(l_count).param_data_type := 'DATE';
534
535 l_count:=l_count+1;
536 l_trans_tbl(l_count).param_name := 'P_PERSON_ID';
537 l_trans_tbl(l_count).param_value := x_person_id;
538 l_trans_tbl(l_count).param_data_type := 'NUMBER';
539
540 l_count:=l_count+1;
541 l_trans_tbl(l_count).param_name := 'P_ASSIGNMENT_ID';
542 l_trans_tbl(l_count).param_value := x_ASSIGNMENT_id;
543 l_trans_tbl(l_count).param_data_type := 'NUMBER';
544
545 l_count:=l_count+1;
546 l_trans_tbl(l_count).param_name := 'P_BUSINESS_GROUP_ID';
547 l_trans_tbl(l_count).param_value := x_business_group_id;
548 l_trans_tbl(l_count).param_data_type := 'NUMBER';
549
550 l_count:=l_count+1;
551 l_trans_tbl(l_count).param_name := 'P_ELEMENT_ENTRY_ID';
552 l_trans_tbl(l_count).param_value := x_element_entry_id;
553 l_trans_tbl(l_count).param_data_type := 'NUMBER';
554
555 IF x_transaction_id is NULl THEN
556 hr_transaction_api.create_transaction(
557 p_validate => false
558 ,p_creator_person_id => x_login_person_id
559 ,p_transaction_privilege => 'PRIVATE'
560 ,p_product_code => 'PQP'
561 ,p_url => NULL
562 ,p_status =>NULL
563 ,p_section_display_name =>NULL
564 ,p_function_id =>NULL
565 ,p_transaction_ref_table =>NULL
566 ,p_transaction_ref_id =>NULL
567 ,p_transaction_type =>'WF'
568 ,p_assignment_id =>x_assignment_id
569 ,p_selected_person_id =>x_person_id
570 ,p_item_type => x_item_type
571 ,p_item_key =>x_item_key
572 ,p_transaction_effective_date =>x_effective_date
573 ,p_process_name =>NULL
574 ,p_plan_id =>NULL
575 ,p_rptg_grp_id =>NULL
576 ,p_effective_date_option =>x_effective_date
577 ,p_transaction_id => l_transaction_id
578 );
579 x_transaction_id := l_transaction_id;
580 --Create transaction steps
581 hr_transaction_api.create_transaction_step
582 (p_validate => false
583 ,p_creator_person_id => x_login_person_id
584 ,p_transaction_id => l_transaction_id
585 ,p_api_name => l_api_name
586 ,p_api_display_name => l_api_name
587 ,p_item_type => null --x_item_type
588 ,p_item_key => NULL --x_item_key
589 ,p_activity_id => NULL --x_activity_id
590 ,p_transaction_step_id => l_transaction_step_id
591 ,p_object_version_number => l_ovn
592 );
593 ELSE
594 DELETE from hr_api_transaction_values
595 WHERE transaction_step_id = x_transaction_step_id;
596 l_transaction_step_id := x_transaction_step_id;
597 END IF;
598
599 FOR i in 1..l_trans_tbl.count
600 LOOP
601 IF l_trans_tbl(i).param_data_type ='VARCHAR2' THEN
602 hr_transaction_api.set_varchar2_value
603 (p_transaction_step_id => l_transaction_step_id
604 ,p_person_id => x_person_id
605 ,p_name => l_trans_tbl (i).param_name
606 ,p_value => l_trans_tbl (i).param_value
607 );
608
609 ELSIF l_trans_tbl(i).param_data_type ='DATE' THEN
610 hr_transaction_api.set_date_value
611 (
612 p_transaction_step_id => l_transaction_step_id
613 ,p_person_id => x_person_id
614 ,p_name => l_trans_tbl (i).param_name
615 ,p_value => fnd_date.displaydate_to_date
616 (l_trans_tbl (i).param_value ) );
617 ELSIF l_trans_tbl(i).param_data_type ='NUMBER' THEN
618 hr_transaction_api.set_number_value
619 (
620 p_transaction_step_id => l_transaction_step_id
621 ,p_person_id => x_person_id
622 ,p_name =>l_trans_tbl (i).param_name
623 ,p_value =>TO_NUMBER(l_trans_tbl (i).param_value ));
624 END IF;
625 END LOOP;
626
627 EXCEPTION
628 WHEN hr_utility.hr_error THEN
629 hr_utility.raise_error;
630 WHEN OTHERS THEN
631 RAISE; -- Raise error here relevant to the new tech stack.
632
633
634
635 END;
636
637 PROCEDURE set_vehicle_mileage_claim (
638 x_p_validate IN BOOLEAN
639 ,x_effective_date IN DATE
640 ,x_login_person_id IN NUMBER
641 ,x_person_id IN NUMBER
642 ,x_assignment_id IN NUMBER
643 ,x_item_type IN VARCHAR2
644 ,x_item_key IN NUMBER
645 ,x_activity_id IN NUMBER
646 ,x_business_group_id IN NUMBER
647 ,x_legislation_code IN VARCHAR2
648 ,x_ownership IN VARCHAR2
649 ,x_usage_type IN VARCHAR2
650 ,x_vehicle_type IN VARCHAR2
651 ,x_start_date IN DATE
652 ,x_end_date IN DATE
653 ,x_claimed_mileage IN VARCHAR2
654 ,x_actual_mileage IN VARCHAR2 DEFAULT NULL
655 ,x_claimed_mileage_o IN VARCHAR2 DEFAULT NULL
656 ,x_actual_mileage_o IN VARCHAR2 DEFAULT NULL
657 ,x_registration_number IN VARCHAR2 DEFAULT NULL
658 ,x_engine_capacity IN VARCHAR2 DEFAULT NULL
659 ,x_fuel_type IN VARCHAR2 DEFAULT NULL
660 ,x_fiscal_ratings IN VARCHAR2 DEFAULT NULL
661 ,x_no_of_passengers IN VARCHAR2 DEFAULT NULL
662 ,x_purpose IN VARCHAR2 DEFAULT NULL
663 ,x_element_entry_id IN NUMBER DEFAULT NULL
664 ,x_status IN VARCHAR2 DEFAULT NULL
665 ,x_effective_date_option IN VARCHAR2 DEFAULT NULL
666 ,x_return_status OUT NOCOPY VARCHAR2
667 ,x_object_version_number IN NUMBER
668 ,x_error_status OUT NOCOPY VARCHAR2
669 ,x_transaction_id IN OUT NOCOPY NUMBER
670 ,x_transaction_step_id IN OUT NOCOPY NUMBER
671 ,x_confirmation_number OUT NOCOPY NUMBER
672 )
673 IS
674 CURSOR c_del_values
675 IS
676 SELECT transaction_step_id
677 FROM hr_api_transaction_steps hats
678 WHERE transaction_id = x_transaction_id;
679 l_del_values c_del_values%ROWTYPE;
680 l_transaction_id NUMBER;
681 l_trans_tbl hr_transaction_ss.transaction_table;
682 l_count NUMBER :=0;
683 l_transaction_step_id NUMBER;
684 l_api_name hr_api_transaction_steps.api_name%TYPE
685 := 'PQP_SS_VEHICLE_MILEAGE_CLAIMS.PROCESS_API';
686 l_result VARCHAR2(100);
687 l_trns_object_version_number NUMBER;
688 l_review_proc_call VARCHAR2(30) := 'PqpMileageClaimReview';
689 l_effective_date DATE := SYSDATE;
690 l_ovn NUMBER;
691 l_error_message VARCHAR2(80);
692 l_error_status VARCHAR2(10);
693 l_mileage_claim_element VARCHAR2(80);
694 l_element_entry_id NUMBER;
695 l_element_entry_date DATE;
696 l_mode VARCHAR2(10);
697 l_function_id hr_api_transactions.function_id%TYPE;
698 p_function_id hr_api_transactions.function_id%TYPE;
699 ln_selected_person_id hr_api_transactions.selected_person_id%TYPE;
700 lv_process_name hr_api_transactions.process_name%TYPE;
701 lv_status hr_api_transactions.status%TYPE;
702 lv_section_display_name hr_api_transactions.section_display_name%TYPE;
703 ln_assignment_id hr_api_transactions.assignment_id%TYPE;
704 ld_trans_effec_date hr_api_transactions.transaction_effective_date%TYPE;
705 lv_transaction_type hr_api_transactions.transaction_type%TYPE;
706 BEGIN
707 hr_multi_message.enable_message_list;
708
709 hr_utility.set_location('Entering: Set_vehicle_mileage_claim',5);
710
711 l_count:=l_count+1;
712 l_trans_tbl(l_count).param_name := 'P_LOGIN_PERSON_ID';
713 l_trans_tbl(l_count).param_value := x_login_person_id;
714 l_trans_tbl(l_count).param_data_type := 'NUMBER';
715
716 l_count:=l_count+1;
717 l_trans_tbl(l_count).param_name := 'P_REVIEW_PROC_CALL';
718 l_trans_tbl(l_count).param_value := l_review_proc_call;
719 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
720
721 l_count:=l_count+1;
722 l_trans_tbl(l_count).param_name := 'P_REVIEW_ACTID';
723 l_trans_tbl(l_count).param_value := x_activity_id;
724 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
725
726 l_count:=l_count+1;
727 l_trans_tbl(l_count).param_name := 'P_EFFECTIVE_DATE';
728 l_trans_tbl(l_count).param_value := x_effective_date;
729 l_trans_tbl(l_count).param_data_type := 'DATE';
730
731 l_count:=l_count+1;
732 l_trans_tbl(l_count).param_name := 'P_PERSON_ID';
733 l_trans_tbl(l_count).param_value := x_person_id;
734 l_trans_tbl(l_count).param_data_type := 'NUMBER';
735
736 l_count:=l_count+1;
737 l_trans_tbl(l_count).param_name := 'P_ASSIGNMENT_ID';
738 l_trans_tbl(l_count).param_value := x_ASSIGNMENT_id;
739 l_trans_tbl(l_count).param_data_type := 'NUMBER';
740
741 l_count:=l_count+1;
742 l_trans_tbl(l_count).param_name := 'P_BUSINESS_GROUP_ID';
743 l_trans_tbl(l_count).param_value := x_business_group_id;
744 l_trans_tbl(l_count).param_data_type := 'NUMBER';
745
746 l_count:=l_count+1;
747 l_trans_tbl(l_count).param_name := 'P_LEGISLATION_CODE';
748 l_trans_tbl(l_count).param_value := x_legislation_code;
749 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
750
751 l_count:=l_count+1;
752 l_trans_tbl(l_count).param_name := 'P_OWNERSHIP';
753 l_trans_tbl(l_count).param_value := x_ownership;
754 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
755
756 l_count:=l_count+1;
757 l_trans_tbl(l_count).param_name := 'P_USAGE_TYPE';
758 l_trans_tbl(l_count).param_value := x_usage_type;
759 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
760
761 l_count:=l_count+1;
762 l_trans_tbl(l_count).param_name := 'P_VEHICLE_TYPE';
763 l_trans_tbl(l_count).param_value := x_vehicle_type;
764 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
765
766 l_count:=l_count+1;
767 l_trans_tbl(l_count).param_name := 'P_START_DATE';
768 l_trans_tbl(l_count).param_value := x_start_date;
769 l_trans_tbl(l_count).param_data_type := 'DATE';
770
771
772 l_count:=l_count+1;
773 l_trans_tbl(l_count).param_name := 'P_END_DATE';
774 l_trans_tbl(l_count).param_value := x_end_date;
775 l_trans_tbl(l_count).param_data_type := 'DATE';
776
777
778 l_count:=l_count+1;
779 l_trans_tbl(l_count).param_name := 'P_CLAIMED_MILEAGE';
780 l_trans_tbl(l_count).param_value := x_claimed_mileage;
781 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
782
783 l_count:=l_count+1;
784 l_trans_tbl(l_count).param_name := 'P_ACTUAL_MILEAGE';
785 l_trans_tbl(l_count).param_value := x_actual_mileage;
786 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
787
788 l_count:=l_count+1;
789 l_trans_tbl(l_count).param_name := 'P_CLAIMED_MILEAGE_O';
790 l_trans_tbl(l_count).param_value := x_claimed_mileage_o;
791 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
792
793 l_count:=l_count+1;
794 l_trans_tbl(l_count).param_name := 'P_ACTUAL_MILEAGE_O';
795 l_trans_tbl(l_count).param_value := x_actual_mileage_o;
796 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
797
798 l_count:=l_count+1;
799 l_trans_tbl(l_count).param_name := 'P_REGISTRATION_NUMBER';
800 l_trans_tbl(l_count).param_value := x_registration_number;
801 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
802
803 l_count:=l_count+1;
804 l_trans_tbl(l_count).param_name := 'P_ENGINE_CAPACITY';
805 l_trans_tbl(l_count).param_value := x_engine_capacity;
806 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
807
808 l_count:=l_count+1;
809 l_trans_tbl(l_count).param_name := 'P_FUEL_TYPE';
810 l_trans_tbl(l_count).param_value := x_fuel_type;
811 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
812
813 l_count:=l_count+1;
814 l_trans_tbl(l_count).param_name := 'P_FISCAL_RATINGS';
815 l_trans_tbl(l_count).param_value := x_fiscal_ratings;
816 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
817
818
819 l_count:=l_count+1;
820 l_trans_tbl(l_count).param_name := 'P_NO_OF_PASSENGERS';
821 l_trans_tbl(l_count).param_value := x_no_of_passengers;
822 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
823
824
825 l_count:=l_count+1;
826 l_trans_tbl(l_count).param_name := 'P_PURPOSE';
827 l_trans_tbl(l_count).param_value := x_purpose;
828 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
829
830
831 l_count:=l_count+1;
832 l_trans_tbl(l_count).param_name := 'P_ELEMENT_ENTRY_ID';
833 l_trans_tbl(l_count).param_value := x_element_entry_id;
834 l_trans_tbl(l_count).param_data_type := 'NUMBER';
835
836
837 ---Now Validate the incomming values against actual table.
838 ---Setting the mode determines the dml status.
839 IF x_element_entry_id is NULL THEN
840 l_mode :='I' ;
841
842 ELSE
843 l_mode:='U';
844 END IF;
845
846
847
848
849 create_validate_mileage_claim
850 (
851 p_effective_date => x_effective_date,
852 p_web_adi_identifier => NULL ,
853 p_info_id => NULL,
854 p_time_stamp => NULL,
855 p_assignment_id => x_assignment_id,
856 p_business_group_id => x_business_group_id,
857 p_ownership => x_ownership,
858 p_usage_type => x_usage_type,
859 p_vehicle_type => x_vehicle_type,
860 p_start_date => x_start_date,
861 p_end_date => x_end_date,
862 p_claimed_mileage => x_claimed_mileage,
863 p_actual_mileage => x_actual_mileage,
864 p_claimed_mileage_o => x_claimed_mileage_o,
865 p_actual_mileage_o => x_actual_mileage_o,
866 p_registration_number => x_registration_number,
867 p_engine_capacity => x_engine_capacity,
868 p_fuel_type => x_fuel_type,
869 p_fiscal_ratings => x_fiscal_ratings,
870 p_no_of_passengers => x_no_of_passengers,
871 p_purpose => x_purpose,
872 p_user_type =>'SS',
873 p_mileage_claim_element => l_mileage_claim_element,
874 p_element_entry_id => l_element_entry_id,
875 p_element_entry_date => l_element_entry_date,
876 p_mode => l_mode,
877 p_return_status => l_error_status
878 );
879 -- If there are no error messages then insert values into transaction table
880
881 IF x_transaction_id is NULL THEN
882
883 hr_transaction_api.create_transaction(
884 p_validate =>false
885 ,p_creator_person_id =>x_login_person_id
886 ,p_transaction_privilege =>'PRIVATE'
887 ,p_product_code =>'PQP'
888 ,p_url =>NULL
889 ,p_status =>x_status
890 ,p_section_display_name =>NULL
891 ,p_function_id =>NULL
892 ,p_transaction_ref_table =>NULL
893 ,p_transaction_ref_id =>NULL
894 ,p_transaction_type =>NULL
895 ,p_assignment_id =>x_assignment_id
896 ,p_selected_person_id =>x_person_id
897 ,p_item_type =>x_item_type
898 ,p_item_key =>x_item_key
899 ,p_transaction_effective_date =>x_effective_date
900 ,p_process_name =>NULL
901 ,p_plan_id =>NULL
902 ,p_rptg_grp_id =>NULL
903 ,p_effective_date_option =>x_effective_date_option
904 ,p_transaction_id => l_transaction_id
905 );
906
907 wf_engine.setitemattrnumber
908 (itemtype => x_item_type
909 ,itemkey => x_item_key
910 ,aname => 'TRANSACTION_ID'
911 ,avalue => l_transaction_id);
912 x_transaction_id := l_transaction_id;
913 --Create transaction steps
914 hr_transaction_api.create_transaction_step
915 (p_validate =>false
916 ,p_creator_person_id =>x_login_person_id
917 ,p_transaction_id =>l_transaction_id
918 ,p_api_name =>l_api_name
919 ,p_api_display_name =>l_api_name
920 ,p_item_type =>x_item_type
921 ,p_item_key =>x_item_key
922 ,p_activity_id =>x_activity_id
923 ,p_transaction_step_id =>l_transaction_step_id
924 ,p_object_version_number =>l_ovn
925 );
926 ELSE
927 IF x_transaction_step_id IS NOT NULL THEN
928 hr_transaction_api.update_transaction
929 (p_transaction_id =>x_transaction_id
930 ,p_status =>x_status
931 );
932 DELETE from hr_api_transaction_values
933 WHERE transaction_step_id = x_transaction_step_id;
934 l_transaction_step_id := x_transaction_step_id;
935 ELSE
936 l_transaction_step_id := x_transaction_step_id;
937 l_transaction_id := x_transaction_id;
938
939 hr_transaction_api.create_transaction_step
940 (p_validate =>false
941 ,p_creator_person_id =>x_login_person_id
942 ,p_transaction_id =>l_transaction_id
943 ,p_api_name =>l_api_name
944 ,p_api_display_name =>l_api_name
945 ,p_item_type =>x_item_type
946 ,p_item_key =>x_item_key
947 ,p_activity_id =>x_activity_id
948 ,p_transaction_step_id =>l_transaction_step_id
949 ,p_object_version_number =>l_ovn
950 );
951
952
953 END IF;
954 END IF;
955 FOR i in 1..l_trans_tbl.count
956 LOOP
957 IF l_trans_tbl(i).param_data_type ='VARCHAR2' THEN
958 hr_transaction_api.set_varchar2_value
959 (p_transaction_step_id => l_transaction_step_id
960 ,p_person_id => x_person_id
961 ,p_name => l_trans_tbl (i).param_name
962 ,p_value => l_trans_tbl (i).param_value
963 );
964
965 ELSIF l_trans_tbl(i).param_data_type ='DATE' THEN
966 hr_transaction_api.set_date_value
967 (
968 p_transaction_step_id => l_transaction_step_id
969 ,p_person_id => x_person_id
970 ,p_name => l_trans_tbl (i).param_name
971 ,p_value => fnd_date.displaydate_to_date
972 (l_trans_tbl (i).param_value ));
973 -- ,p_original_value );
974
975
976 ELSIF l_trans_tbl(i).param_data_type ='NUMBER' THEN
977 hr_transaction_api.set_number_value
978 (
979 p_transaction_step_id => l_transaction_step_id
980 ,p_person_id => x_person_id
981 ,p_name =>l_trans_tbl (i).param_name
982 ,p_value =>TO_NUMBER(l_trans_tbl (i).param_value ));
983 END IF;
984 END LOOP;
985 Commit;
986 EXCEPTION
987 WHEN hr_multi_message.error_message_exist THEN
988 --
989 -- Catch the Multiple Message List exception which
990 -- indicates API processing has been aborted because
991 -- at least one message exists in the list.
992 --
993 --
994 -- Reset IN OUT parameters and set OUT parameters
995 x_return_status := hr_multi_message.get_return_status_disable;
996
997 hr_utility.set_location(' Leaving:' ,40);
998 WHEN others then
999 --
1000 -- When Multiple Message Detection is enabled catch
1001 -- any Application specific or other unexpected
1002 -- exceptions. Adding appropriate details to the
1003 -- Multiple Message List. Otherwise re-raise the
1004 -- error.
1005 --
1006 IF hr_multi_message.unexpected_error_add('l_proc') then
1007 --raise;
1008 x_return_status := hr_multi_message.get_return_status_disable;
1009 END IF;
1010 -- Reset IN OUT parameters and set OUT parameters
1011
1012 x_return_status := hr_multi_message.get_return_status_disable;
1013 hr_utility.set_location(' Leaving:' || 'l_proc',50);
1014
1015 /* WHEN hr_utility.hr_error THEN
1016 hr_utility.raise_error;
1017 WHEN OTHERS THEN
1018 RAISE; -- Raise error here relevant to the new tech stack.
1019 */
1020 END;
1021 --
1022 --
1023
1024 PROCEDURE delete_process_api (
1025 p_validate IN BOOLEAN DEFAULT FALSE,
1026 p_transaction_step_id IN NUMBER ) IS
1027 --
1028 --
1029 l_ovn NUMBER :=1;
1030 l_error_status VARCHAR2(10);
1031 l_effective_start_date DATE;
1032 l_effective_end_date DATE;
1033 l_person_id per_all_people_f.person_id%TYPE;
1034 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
1035 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
1036 l_element_entry_id NUMBER;
1037 l_effective_date DATE;
1038 l_mileage_claim_element NUMBER;
1039 l_confirmation_number NUMBER;
1040 l_element_entry_date DATE;
1041 BEGIN
1042 l_person_id := hr_transaction_api.get_number_value (
1043 p_transaction_step_id => p_transaction_step_id,
1044 p_name =>'P_PERSON_ID' );
1045
1046 l_effective_date := hr_transaction_api.get_date_value (
1047 p_transaction_step_id => p_transaction_step_id,
1048 p_name =>'P_EFFECTIVE_DATE' );
1049 l_assignment_id :=hr_transaction_api.get_number_value (
1050 p_transaction_step_id => p_transaction_step_id,
1051 p_name =>'P_ASSIGNMENT_ID' );
1052
1053 l_business_group_id :=hr_transaction_api.get_number_value (
1054 p_transaction_step_id => p_transaction_step_id,
1055 p_name =>'P_BUSINESS_GROUP_ID' );
1056 l_element_entry_id :=hr_transaction_api.get_number_value (
1057 p_transaction_step_id => p_transaction_step_id,
1058 p_name =>'P_ELEMENT_ENTRY_ID' );
1059
1060
1061
1062
1063 pqp_mileage_claim_pkg.pqp_delete_mileage_claim
1064 ( p_effective_date =>l_effective_date,
1065 p_assignment_id =>l_assignment_id,
1066 p_mileage_claim_element =>l_mileage_claim_element ,
1067 p_element_entry_id =>l_element_entry_id ,
1068 p_element_entry_date =>l_element_entry_date,
1069 p_return_status =>l_error_status
1070 );
1071
1072 EXCEPTION
1073 WHEN hr_utility.hr_error THEN
1074 hr_utility.raise_error;
1075 WHEN OTHERS THEN
1076 RAISE; -- Raise error here relevant to the new tech stack.
1077
1078 END;
1079
1080 PROCEDURE process_api
1081 (p_validate in boolean default false
1082 ,p_transaction_step_id in number default null
1083 ,p_effective_date in varchar2 default null
1084 ) is
1085 --
1086 --
1087 l_assignment_id NUMBER;
1088 l_person_id NUMBER;
1089 l_ovn NUMBER :=1;
1090 l_effective_date DATE;
1091 l_registration_number VARCHAR2(80);
1092 l_vehicle_type VARCHAR2(80);
1093 l_business_group_id NUMBER;
1094 l_engine_capacity VARCHAR2(80);
1095 l_fuel_type VARCHAR2(80);
1096 l_fiscal_ratings VARCHAR2(80);
1097 l_ownership VARCHAR2(80);
1098 l_usage_type VARCHAR2(80);
1099 l_object_version_number NUMBER;
1100 l_error_message VARCHAR2(80);
1101 l_error_status VARCHAR2(30);
1102 l_get_count NUMBER;
1103 l_mileage_claim_element NUMBER;
1104 l_element_entry_date DATE;
1105 l_confirmation_number NUMBER;
1106 l_start_date DATE;
1107 l_end_date DATE;
1108 l_claimed_mileage VARCHAR2(30);
1109 l_actual_mileage VARCHAR2(30);
1110 l_claimed_mileage_o VARCHAR2(30);
1111 l_actual_mileage_o VARCHAR2(30);
1112 l_no_of_passengers VARCHAR2(30);
1113 l_mode VARCHAR2(30);
1114 l_purpose VARCHAR2(80);
1115 l_element_entry_id NUMBER;
1116
1117 BEGIN
1118 hr_utility.set_location('Entering:process_api',5);
1119 --
1120 savepoint process_veh_mileage;
1121 --
1122
1123
1124
1125 l_person_id := hr_transaction_api.get_number_value (
1126 p_transaction_step_id => p_transaction_step_id,
1127 p_name =>'P_PERSON_ID' );
1128
1129 l_assignment_id := hr_transaction_api.get_number_value (
1130 p_transaction_step_id => p_transaction_step_id,
1131 p_name =>'P_ASSIGNMENT_ID' );
1132
1133
1134 l_effective_date := hr_transaction_api.get_date_value (
1135 p_transaction_step_id => p_transaction_step_id,
1136 p_name =>'P_EFFECTIVE_DATE' );
1137
1138 l_registration_number := hr_transaction_api.get_varchar2_value (
1139 p_transaction_step_id => p_transaction_step_id,
1140 p_name =>'P_REGISTRATION_NUMBER' );
1141
1142 l_vehicle_type := hr_transaction_api.get_varchar2_value (
1143 p_transaction_step_id => p_transaction_step_id,
1144 p_name =>'P_VEHICLE_TYPE' );
1145
1146 l_business_group_id := hr_transaction_api.get_number_value (
1147 p_transaction_step_id => p_transaction_step_id,
1148 p_name =>'P_BUSINESS_GROUP_ID');
1149
1150
1151 l_engine_capacity := hr_transaction_api.get_varchar2_value (
1152 p_transaction_step_id => p_transaction_step_id,
1153 p_name =>'P_ENGINE_CAPACITY');
1154
1155
1156
1157 l_start_date := hr_transaction_api.get_date_value (
1158 p_transaction_step_id => p_transaction_step_id,
1159 p_name =>'P_START_DATE');
1160
1161
1162 l_end_date := hr_transaction_api.get_date_value (
1163 p_transaction_step_id => p_transaction_step_id,
1164 p_name =>'P_END_DATE');
1165
1166 l_claimed_mileage := hr_transaction_api.get_varchar2_value (
1167 p_transaction_step_id => p_transaction_step_id,
1168 p_name =>'P_CLAIMED_MILEAGE');
1169
1170 l_actual_mileage := hr_transaction_api.get_varchar2_value (
1171 p_transaction_step_id => p_transaction_step_id,
1172 p_name =>'P_ACTUAL_MILEAGE');
1173
1174
1175 l_claimed_mileage_o := hr_transaction_api.get_varchar2_value (
1176 p_transaction_step_id => p_transaction_step_id,
1177 p_name =>'P_CLAIMED_MILEAGE_O');
1178
1179 l_actual_mileage_o := hr_transaction_api.get_varchar2_value (
1180 p_transaction_step_id => p_transaction_step_id,
1181 p_name =>'P_ACTUAL_MILEAGE_O');
1182
1183 l_fuel_type := hr_transaction_api.get_varchar2_value (
1184 p_transaction_step_id => p_transaction_step_id,
1185 p_name =>'P_FUEL_TYPE');
1186
1187 l_fiscal_ratings := hr_transaction_api.get_varchar2_value (
1188 p_transaction_step_id => p_transaction_step_id,
1189 p_name =>'P_FISCAL_RATINGS' );
1190
1191 l_ownership := hr_transaction_api.get_varchar2_value (
1192 p_transaction_step_id => p_transaction_step_id,
1193 p_name =>'P_OWNERSHIP');
1194
1195 l_usage_type := hr_transaction_api.get_varchar2_value (
1196 p_transaction_step_id => p_transaction_step_id,
1197 p_name =>'P_USAGE_TYPE');
1198
1199
1200 l_no_of_passengers := hr_transaction_api.get_varchar2_value (
1201 p_transaction_step_id => p_transaction_step_id,
1202 p_name =>'P_NO_OF_PASSENGERS');
1203
1204
1205 l_purpose := hr_transaction_api.get_varchar2_value (
1206 p_transaction_step_id => p_transaction_step_id,
1207 p_name =>'P_PURPOSE');
1208
1209
1210 l_element_entry_id := hr_transaction_api.get_number_value (
1211 p_transaction_step_id => p_transaction_step_id,
1212 p_name =>'P_ELEMENT_ENTRY_ID');
1213 create_vehicle_mileage_claims
1214 (
1215 p_effective_date => l_effective_date,
1216 p_web_adi_identifier => NULL,
1217 p_info_id => NULL,
1218 p_time_stamp => NULL,
1219 p_assignment_id => l_assignment_id,
1220 p_business_group_id => l_business_group_id,
1221 p_ownership => l_ownership,
1222 p_usage_type => l_usage_type,
1223 p_vehicle_type => l_vehicle_type,
1224 p_start_date => l_start_date,
1225 p_end_date => l_end_date,
1226 p_claimed_mileage => l_claimed_mileage,
1227 p_actual_mileage => l_actual_mileage,
1228 p_claimed_mileage_o => l_claimed_mileage_o,
1229 p_actual_mileage_o => l_actual_mileage_o,
1230 p_registration_number => l_registration_number,
1231 p_engine_capacity => l_engine_capacity,
1232 p_fuel_type => l_fuel_type,
1233 p_fiscal_ratings => l_fiscal_ratings,
1234 p_no_of_passengers => l_no_of_passengers,
1235 p_purpose => l_purpose,
1236 p_user_type => 'SS',
1237 p_mileage_claim_element => l_mileage_claim_element,
1238 p_element_entry_id => l_element_entry_id,
1239 p_element_entry_date => l_element_entry_date,
1240 p_mode => l_mode,
1241 p_return_status => l_error_status
1242 );
1243
1244
1245
1246
1247 --
1248 --
1249 --
1250 hr_utility.set_location('Leaving: process_api',10);
1251 EXCEPTION
1252 WHEN hr_utility.hr_error THEN
1253 ROLLBACK TO process_veh_mileage;
1254 hr_utility.raise_error;
1255 WHEN OTHERS THEN
1256 RAISE; -- Raise error here relevant to the new tech stack.
1257 END process_api;
1258
1259 PROCEDURE self_or_subordinate (
1260 itemtype IN VARCHAR2,
1261 itemkey IN VARCHAR2,
1262 actid IN NUMBER,
1263 funcmode IN VARCHAR2,
1264 resultout IN OUT NOCOPY VARCHAR2) IS
1265 --
1266 nval1 number;
1267 nval2 number;
1268 l_resultout varchar2(200) := resultout;
1269 --
1270 BEGIN
1271 --
1272 nval1 := Wf_Engine.GetActivityAttrNumber(itemtype,itemkey,actid, 'VALUE1');
1273 nval2 := Wf_Engine.GetActivityAttrNumber(itemtype,itemkey,actid, 'VALUE2');
1274
1275 IF nval1 = nval2 THEN
1276 resultout := 'SELF';
1277 ELSE
1278 resultout := 'SUBORDINATE';
1279 END IF;
1280 EXCEPTION
1281 WHEN OTHERS THEN
1282 resultout := l_resultout;
1283 RAISE;
1284 --
1285 END self_or_subordinate;
1286 --
1287
1288 END PQP_SS_VEHICLE_MILEAGE_CLAIMS;
1289