[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.12020000.2 2012/07/05 12:19:28 amnaraya ship $*/
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
708 /* Bug Fix 13107787 Starts here.
709 Assigning back null values to below two parameters if they have a value of zero. */
710
711 if x_transaction_id = 0 then
712 x_transaction_id := null;
713 end if;
714
715 if x_transaction_step_id = 0 then
716 x_transaction_step_id := null;
717 end if;
718
719 /* Bug Fix 13107787 Ends here */
720
721 hr_multi_message.enable_message_list;
722
723 hr_utility.set_location('Entering: Set_vehicle_mileage_claim',5);
724
725 l_count:=l_count+1;
726 l_trans_tbl(l_count).param_name := 'P_LOGIN_PERSON_ID';
727 l_trans_tbl(l_count).param_value := x_login_person_id;
728 l_trans_tbl(l_count).param_data_type := 'NUMBER';
729
730 l_count:=l_count+1;
731 l_trans_tbl(l_count).param_name := 'P_REVIEW_PROC_CALL';
732 l_trans_tbl(l_count).param_value := l_review_proc_call;
733 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
734
735 l_count:=l_count+1;
736 l_trans_tbl(l_count).param_name := 'P_REVIEW_ACTID';
737 l_trans_tbl(l_count).param_value := x_activity_id;
738 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
739
740 l_count:=l_count+1;
741 l_trans_tbl(l_count).param_name := 'P_EFFECTIVE_DATE';
742 l_trans_tbl(l_count).param_value := x_effective_date;
743 l_trans_tbl(l_count).param_data_type := 'DATE';
744
745 l_count:=l_count+1;
746 l_trans_tbl(l_count).param_name := 'P_PERSON_ID';
747 l_trans_tbl(l_count).param_value := x_person_id;
748 l_trans_tbl(l_count).param_data_type := 'NUMBER';
749
750 l_count:=l_count+1;
751 l_trans_tbl(l_count).param_name := 'P_ASSIGNMENT_ID';
752 l_trans_tbl(l_count).param_value := x_ASSIGNMENT_id;
753 l_trans_tbl(l_count).param_data_type := 'NUMBER';
754
755 l_count:=l_count+1;
756 l_trans_tbl(l_count).param_name := 'P_BUSINESS_GROUP_ID';
757 l_trans_tbl(l_count).param_value := x_business_group_id;
758 l_trans_tbl(l_count).param_data_type := 'NUMBER';
759
760 l_count:=l_count+1;
761 l_trans_tbl(l_count).param_name := 'P_LEGISLATION_CODE';
762 l_trans_tbl(l_count).param_value := x_legislation_code;
763 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
764
765 l_count:=l_count+1;
766 l_trans_tbl(l_count).param_name := 'P_OWNERSHIP';
767 l_trans_tbl(l_count).param_value := x_ownership;
768 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
769
770 l_count:=l_count+1;
771 l_trans_tbl(l_count).param_name := 'P_USAGE_TYPE';
772 l_trans_tbl(l_count).param_value := x_usage_type;
773 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
774
775 l_count:=l_count+1;
776 l_trans_tbl(l_count).param_name := 'P_VEHICLE_TYPE';
777 l_trans_tbl(l_count).param_value := x_vehicle_type;
778 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
779
780 l_count:=l_count+1;
781 l_trans_tbl(l_count).param_name := 'P_START_DATE';
782 l_trans_tbl(l_count).param_value := x_start_date;
783 l_trans_tbl(l_count).param_data_type := 'DATE';
784
785
786 l_count:=l_count+1;
787 l_trans_tbl(l_count).param_name := 'P_END_DATE';
788 l_trans_tbl(l_count).param_value := x_end_date;
789 l_trans_tbl(l_count).param_data_type := 'DATE';
790
791
792 l_count:=l_count+1;
793 l_trans_tbl(l_count).param_name := 'P_CLAIMED_MILEAGE';
794 l_trans_tbl(l_count).param_value := x_claimed_mileage;
795 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
796
797 l_count:=l_count+1;
798 l_trans_tbl(l_count).param_name := 'P_ACTUAL_MILEAGE';
799 l_trans_tbl(l_count).param_value := x_actual_mileage;
800 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
801
802 l_count:=l_count+1;
803 l_trans_tbl(l_count).param_name := 'P_CLAIMED_MILEAGE_O';
804 l_trans_tbl(l_count).param_value := x_claimed_mileage_o;
805 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
806
807 l_count:=l_count+1;
808 l_trans_tbl(l_count).param_name := 'P_ACTUAL_MILEAGE_O';
809 l_trans_tbl(l_count).param_value := x_actual_mileage_o;
810 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
811
812 l_count:=l_count+1;
813 l_trans_tbl(l_count).param_name := 'P_REGISTRATION_NUMBER';
814 l_trans_tbl(l_count).param_value := x_registration_number;
815 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
816
817 l_count:=l_count+1;
818 l_trans_tbl(l_count).param_name := 'P_ENGINE_CAPACITY';
819 l_trans_tbl(l_count).param_value := x_engine_capacity;
820 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
821
822 l_count:=l_count+1;
823 l_trans_tbl(l_count).param_name := 'P_FUEL_TYPE';
824 l_trans_tbl(l_count).param_value := x_fuel_type;
825 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
826
827 l_count:=l_count+1;
828 l_trans_tbl(l_count).param_name := 'P_FISCAL_RATINGS';
829 l_trans_tbl(l_count).param_value := x_fiscal_ratings;
830 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
831
832
833 l_count:=l_count+1;
834 l_trans_tbl(l_count).param_name := 'P_NO_OF_PASSENGERS';
835 l_trans_tbl(l_count).param_value := x_no_of_passengers;
836 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
837
838
839 l_count:=l_count+1;
840 l_trans_tbl(l_count).param_name := 'P_PURPOSE';
841 l_trans_tbl(l_count).param_value := x_purpose;
842 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
843
844
845 l_count:=l_count+1;
846 l_trans_tbl(l_count).param_name := 'P_ELEMENT_ENTRY_ID';
847 l_trans_tbl(l_count).param_value := x_element_entry_id;
848 l_trans_tbl(l_count).param_data_type := 'NUMBER';
849
850
851 ---Now Validate the incomming values against actual table.
852 ---Setting the mode determines the dml status.
853 IF x_element_entry_id is NULL THEN
854 l_mode :='I' ;
855
856 ELSE
857 l_mode:='U';
858 END IF;
859
860
861
862
863 create_validate_mileage_claim
864 (
865 p_effective_date => x_effective_date,
866 p_web_adi_identifier => NULL ,
867 p_info_id => NULL,
868 p_time_stamp => NULL,
869 p_assignment_id => x_assignment_id,
870 p_business_group_id => x_business_group_id,
871 p_ownership => x_ownership,
872 p_usage_type => x_usage_type,
873 p_vehicle_type => x_vehicle_type,
874 p_start_date => x_start_date,
875 p_end_date => x_end_date,
876 p_claimed_mileage => x_claimed_mileage,
877 p_actual_mileage => x_actual_mileage,
878 p_claimed_mileage_o => x_claimed_mileage_o,
879 p_actual_mileage_o => x_actual_mileage_o,
880 p_registration_number => x_registration_number,
881 p_engine_capacity => x_engine_capacity,
882 p_fuel_type => x_fuel_type,
883 p_fiscal_ratings => x_fiscal_ratings,
884 p_no_of_passengers => x_no_of_passengers,
885 p_purpose => x_purpose,
886 p_user_type =>'SS',
887 p_mileage_claim_element => l_mileage_claim_element,
888 p_element_entry_id => l_element_entry_id,
889 p_element_entry_date => l_element_entry_date,
890 p_mode => l_mode,
891 p_return_status => l_error_status
892 );
893 -- If there are no error messages then insert values into transaction table
894
895 IF x_transaction_id is NULL THEN
896
897 hr_transaction_api.create_transaction(
898 p_validate =>false
899 ,p_creator_person_id =>x_login_person_id
900 ,p_transaction_privilege =>'PRIVATE'
901 ,p_product_code =>'PQP'
902 ,p_url =>NULL
903 ,p_status =>x_status
904 ,p_section_display_name =>NULL
905 ,p_function_id =>NULL
906 ,p_transaction_ref_table =>NULL
907 ,p_transaction_ref_id =>NULL
908 ,p_transaction_type =>NULL
909 ,p_assignment_id =>x_assignment_id
910 ,p_selected_person_id =>x_person_id
911 ,p_item_type =>x_item_type
912 ,p_item_key =>x_item_key
913 ,p_transaction_effective_date =>x_effective_date
914 ,p_process_name =>NULL
915 ,p_plan_id =>NULL
916 ,p_rptg_grp_id =>NULL
917 ,p_effective_date_option =>x_effective_date_option
918 ,p_transaction_id => l_transaction_id
919 );
920
921 wf_engine.setitemattrnumber
922 (itemtype => x_item_type
923 ,itemkey => x_item_key
924 ,aname => 'TRANSACTION_ID'
925 ,avalue => l_transaction_id);
926 x_transaction_id := l_transaction_id;
927 --Create transaction steps
928 hr_transaction_api.create_transaction_step
929 (p_validate =>false
930 ,p_creator_person_id =>x_login_person_id
931 ,p_transaction_id =>l_transaction_id
932 ,p_api_name =>l_api_name
933 ,p_api_display_name =>l_api_name
934 ,p_item_type =>x_item_type
935 ,p_item_key =>x_item_key
936 ,p_activity_id =>x_activity_id
937 ,p_transaction_step_id =>l_transaction_step_id
938 ,p_object_version_number =>l_ovn
939 );
940 ELSE
941 IF x_transaction_step_id IS NOT NULL THEN
942 hr_transaction_api.update_transaction
943 (p_transaction_id =>x_transaction_id
944 ,p_status =>x_status
945 );
946 DELETE from hr_api_transaction_values
947 WHERE transaction_step_id = x_transaction_step_id;
948 l_transaction_step_id := x_transaction_step_id;
949 ELSE
950 l_transaction_step_id := x_transaction_step_id;
951 l_transaction_id := x_transaction_id;
952
953 hr_transaction_api.create_transaction_step
954 (p_validate =>false
955 ,p_creator_person_id =>x_login_person_id
956 ,p_transaction_id =>l_transaction_id
957 ,p_api_name =>l_api_name
958 ,p_api_display_name =>l_api_name
959 ,p_item_type =>x_item_type
960 ,p_item_key =>x_item_key
961 ,p_activity_id =>x_activity_id
962 ,p_transaction_step_id =>l_transaction_step_id
963 ,p_object_version_number =>l_ovn
964 );
965
966
967 END IF;
968 END IF;
969 FOR i in 1..l_trans_tbl.count
970 LOOP
971 IF l_trans_tbl(i).param_data_type ='VARCHAR2' THEN
972 hr_transaction_api.set_varchar2_value
973 (p_transaction_step_id => l_transaction_step_id
974 ,p_person_id => x_person_id
975 ,p_name => l_trans_tbl (i).param_name
976 ,p_value => l_trans_tbl (i).param_value
977 );
978
979 ELSIF l_trans_tbl(i).param_data_type ='DATE' THEN
980 hr_transaction_api.set_date_value
981 (
982 p_transaction_step_id => l_transaction_step_id
983 ,p_person_id => x_person_id
984 ,p_name => l_trans_tbl (i).param_name
985 ,p_value => fnd_date.displaydate_to_date
986 (l_trans_tbl (i).param_value ));
987 -- ,p_original_value );
988
989
990 ELSIF l_trans_tbl(i).param_data_type ='NUMBER' THEN
991 hr_transaction_api.set_number_value
992 (
993 p_transaction_step_id => l_transaction_step_id
994 ,p_person_id => x_person_id
995 ,p_name =>l_trans_tbl (i).param_name
996 ,p_value =>TO_NUMBER(l_trans_tbl (i).param_value ));
997 END IF;
998 END LOOP;
999 Commit;
1000 EXCEPTION
1001 WHEN hr_multi_message.error_message_exist THEN
1002 --
1003 -- Catch the Multiple Message List exception which
1004 -- indicates API processing has been aborted because
1005 -- at least one message exists in the list.
1006 --
1007 --
1008 -- Reset IN OUT parameters and set OUT parameters
1009 x_return_status := hr_multi_message.get_return_status_disable;
1010
1011 hr_utility.set_location(' Leaving:' ,40);
1012 WHEN others then
1013 --
1014 -- When Multiple Message Detection is enabled catch
1015 -- any Application specific or other unexpected
1016 -- exceptions. Adding appropriate details to the
1017 -- Multiple Message List. Otherwise re-raise the
1018 -- error.
1019 --
1020 IF hr_multi_message.unexpected_error_add('l_proc') then
1021 --raise;
1022 x_return_status := hr_multi_message.get_return_status_disable;
1023 END IF;
1024 -- Reset IN OUT parameters and set OUT parameters
1025
1026 x_return_status := hr_multi_message.get_return_status_disable;
1027 hr_utility.set_location(' Leaving:' || 'l_proc',50);
1028
1029 /* WHEN hr_utility.hr_error THEN
1030 hr_utility.raise_error;
1031 WHEN OTHERS THEN
1032 RAISE; -- Raise error here relevant to the new tech stack.
1033 */
1034 END;
1035 --
1036 --
1037
1038 PROCEDURE delete_process_api (
1039 p_validate IN BOOLEAN DEFAULT FALSE,
1040 p_transaction_step_id IN NUMBER ) IS
1041 --
1042 --
1043 l_ovn NUMBER :=1;
1044 l_error_status VARCHAR2(10);
1045 l_effective_start_date DATE;
1046 l_effective_end_date DATE;
1047 l_person_id per_all_people_f.person_id%TYPE;
1048 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
1049 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
1050 l_element_entry_id NUMBER;
1051 l_effective_date DATE;
1052 l_mileage_claim_element NUMBER;
1053 l_confirmation_number NUMBER;
1054 l_element_entry_date DATE;
1055 BEGIN
1056 l_person_id := hr_transaction_api.get_number_value (
1057 p_transaction_step_id => p_transaction_step_id,
1058 p_name =>'P_PERSON_ID' );
1059
1060 l_effective_date := hr_transaction_api.get_date_value (
1061 p_transaction_step_id => p_transaction_step_id,
1062 p_name =>'P_EFFECTIVE_DATE' );
1063 l_assignment_id :=hr_transaction_api.get_number_value (
1064 p_transaction_step_id => p_transaction_step_id,
1065 p_name =>'P_ASSIGNMENT_ID' );
1066
1067 l_business_group_id :=hr_transaction_api.get_number_value (
1068 p_transaction_step_id => p_transaction_step_id,
1069 p_name =>'P_BUSINESS_GROUP_ID' );
1070 l_element_entry_id :=hr_transaction_api.get_number_value (
1071 p_transaction_step_id => p_transaction_step_id,
1072 p_name =>'P_ELEMENT_ENTRY_ID' );
1073
1074
1075
1076
1077 pqp_mileage_claim_pkg.pqp_delete_mileage_claim
1078 ( p_effective_date =>l_effective_date,
1079 p_assignment_id =>l_assignment_id,
1080 p_mileage_claim_element =>l_mileage_claim_element ,
1081 p_element_entry_id =>l_element_entry_id ,
1082 p_element_entry_date =>l_element_entry_date,
1083 p_return_status =>l_error_status
1084 );
1085
1086 EXCEPTION
1087 WHEN hr_utility.hr_error THEN
1088 hr_utility.raise_error;
1089 WHEN OTHERS THEN
1090 RAISE; -- Raise error here relevant to the new tech stack.
1091
1092 END;
1093
1094 PROCEDURE process_api
1095 (p_validate in boolean default false
1096 ,p_transaction_step_id in number default null
1097 ,p_effective_date in varchar2 default null
1098 ) is
1099 --
1100 --
1101 l_assignment_id NUMBER;
1102 l_person_id NUMBER;
1103 l_ovn NUMBER :=1;
1104 l_effective_date DATE;
1105 l_registration_number VARCHAR2(80);
1106 l_vehicle_type VARCHAR2(80);
1107 l_business_group_id NUMBER;
1108 l_engine_capacity VARCHAR2(80);
1109 l_fuel_type VARCHAR2(80);
1110 l_fiscal_ratings VARCHAR2(80);
1111 l_ownership VARCHAR2(80);
1112 l_usage_type VARCHAR2(80);
1113 l_object_version_number NUMBER;
1114 l_error_message VARCHAR2(80);
1115 l_error_status VARCHAR2(30);
1116 l_get_count NUMBER;
1117 l_mileage_claim_element NUMBER;
1118 l_element_entry_date DATE;
1119 l_confirmation_number NUMBER;
1120 l_start_date DATE;
1121 l_end_date DATE;
1122 l_claimed_mileage VARCHAR2(30);
1123 l_actual_mileage VARCHAR2(30);
1124 l_claimed_mileage_o VARCHAR2(30);
1125 l_actual_mileage_o VARCHAR2(30);
1126 l_no_of_passengers VARCHAR2(30);
1127 l_mode VARCHAR2(30);
1128 l_purpose VARCHAR2(80);
1129 l_element_entry_id NUMBER;
1130
1131 BEGIN
1132 hr_utility.set_location('Entering:process_api',5);
1133 --
1134 savepoint process_veh_mileage;
1135 --
1136
1137
1138
1139 l_person_id := hr_transaction_api.get_number_value (
1140 p_transaction_step_id => p_transaction_step_id,
1141 p_name =>'P_PERSON_ID' );
1142
1143 l_assignment_id := hr_transaction_api.get_number_value (
1144 p_transaction_step_id => p_transaction_step_id,
1145 p_name =>'P_ASSIGNMENT_ID' );
1146
1147
1148 l_effective_date := hr_transaction_api.get_date_value (
1149 p_transaction_step_id => p_transaction_step_id,
1150 p_name =>'P_EFFECTIVE_DATE' );
1151
1152 l_registration_number := hr_transaction_api.get_varchar2_value (
1153 p_transaction_step_id => p_transaction_step_id,
1154 p_name =>'P_REGISTRATION_NUMBER' );
1155
1156 l_vehicle_type := hr_transaction_api.get_varchar2_value (
1157 p_transaction_step_id => p_transaction_step_id,
1158 p_name =>'P_VEHICLE_TYPE' );
1159
1160 l_business_group_id := hr_transaction_api.get_number_value (
1161 p_transaction_step_id => p_transaction_step_id,
1162 p_name =>'P_BUSINESS_GROUP_ID');
1163
1164
1165 l_engine_capacity := hr_transaction_api.get_varchar2_value (
1166 p_transaction_step_id => p_transaction_step_id,
1167 p_name =>'P_ENGINE_CAPACITY');
1168
1169
1170
1171 l_start_date := hr_transaction_api.get_date_value (
1172 p_transaction_step_id => p_transaction_step_id,
1173 p_name =>'P_START_DATE');
1174
1175
1176 l_end_date := hr_transaction_api.get_date_value (
1177 p_transaction_step_id => p_transaction_step_id,
1178 p_name =>'P_END_DATE');
1179
1180 l_claimed_mileage := hr_transaction_api.get_varchar2_value (
1181 p_transaction_step_id => p_transaction_step_id,
1182 p_name =>'P_CLAIMED_MILEAGE');
1183
1184 l_actual_mileage := hr_transaction_api.get_varchar2_value (
1185 p_transaction_step_id => p_transaction_step_id,
1186 p_name =>'P_ACTUAL_MILEAGE');
1187
1188
1189 l_claimed_mileage_o := hr_transaction_api.get_varchar2_value (
1190 p_transaction_step_id => p_transaction_step_id,
1191 p_name =>'P_CLAIMED_MILEAGE_O');
1192
1193 l_actual_mileage_o := hr_transaction_api.get_varchar2_value (
1194 p_transaction_step_id => p_transaction_step_id,
1195 p_name =>'P_ACTUAL_MILEAGE_O');
1196
1197 l_fuel_type := hr_transaction_api.get_varchar2_value (
1198 p_transaction_step_id => p_transaction_step_id,
1199 p_name =>'P_FUEL_TYPE');
1200
1201 l_fiscal_ratings := hr_transaction_api.get_varchar2_value (
1202 p_transaction_step_id => p_transaction_step_id,
1203 p_name =>'P_FISCAL_RATINGS' );
1204
1205 l_ownership := hr_transaction_api.get_varchar2_value (
1206 p_transaction_step_id => p_transaction_step_id,
1207 p_name =>'P_OWNERSHIP');
1208
1209 l_usage_type := hr_transaction_api.get_varchar2_value (
1210 p_transaction_step_id => p_transaction_step_id,
1211 p_name =>'P_USAGE_TYPE');
1212
1213
1214 l_no_of_passengers := hr_transaction_api.get_varchar2_value (
1215 p_transaction_step_id => p_transaction_step_id,
1216 p_name =>'P_NO_OF_PASSENGERS');
1217
1218
1219 l_purpose := hr_transaction_api.get_varchar2_value (
1220 p_transaction_step_id => p_transaction_step_id,
1221 p_name =>'P_PURPOSE');
1222
1223
1224 l_element_entry_id := hr_transaction_api.get_number_value (
1225 p_transaction_step_id => p_transaction_step_id,
1226 p_name =>'P_ELEMENT_ENTRY_ID');
1227 create_vehicle_mileage_claims
1228 (
1229 p_effective_date => l_effective_date,
1230 p_web_adi_identifier => NULL,
1231 p_info_id => NULL,
1232 p_time_stamp => NULL,
1233 p_assignment_id => l_assignment_id,
1234 p_business_group_id => l_business_group_id,
1235 p_ownership => l_ownership,
1236 p_usage_type => l_usage_type,
1237 p_vehicle_type => l_vehicle_type,
1238 p_start_date => l_start_date,
1239 p_end_date => l_end_date,
1240 p_claimed_mileage => l_claimed_mileage,
1241 p_actual_mileage => l_actual_mileage,
1242 p_claimed_mileage_o => l_claimed_mileage_o,
1243 p_actual_mileage_o => l_actual_mileage_o,
1244 p_registration_number => l_registration_number,
1245 p_engine_capacity => l_engine_capacity,
1246 p_fuel_type => l_fuel_type,
1247 p_fiscal_ratings => l_fiscal_ratings,
1248 p_no_of_passengers => l_no_of_passengers,
1249 p_purpose => l_purpose,
1250 p_user_type => 'SS',
1251 p_mileage_claim_element => l_mileage_claim_element,
1252 p_element_entry_id => l_element_entry_id,
1253 p_element_entry_date => l_element_entry_date,
1254 p_mode => l_mode,
1255 p_return_status => l_error_status
1256 );
1257
1258
1259
1260
1261 --
1262 --
1263 --
1264 hr_utility.set_location('Leaving: process_api',10);
1265 EXCEPTION
1266 WHEN hr_utility.hr_error THEN
1267 ROLLBACK TO process_veh_mileage;
1268 hr_utility.raise_error;
1269 WHEN OTHERS THEN
1270 RAISE; -- Raise error here relevant to the new tech stack.
1271 END process_api;
1272
1273 PROCEDURE self_or_subordinate (
1274 itemtype IN VARCHAR2,
1275 itemkey IN VARCHAR2,
1276 actid IN NUMBER,
1277 funcmode IN VARCHAR2,
1278 resultout IN OUT NOCOPY VARCHAR2) IS
1279 --
1280 nval1 number;
1281 nval2 number;
1282 l_resultout varchar2(200) := resultout;
1283 --
1284 BEGIN
1285 --
1286 nval1 := Wf_Engine.GetActivityAttrNumber(itemtype,itemkey,actid, 'VALUE1');
1287 nval2 := Wf_Engine.GetActivityAttrNumber(itemtype,itemkey,actid, 'VALUE2');
1288
1289 IF nval1 = nval2 THEN
1290 resultout := 'SELF';
1291 ELSE
1292 resultout := 'SUBORDINATE';
1293 END IF;
1294 EXCEPTION
1295 WHEN OTHERS THEN
1296 resultout := l_resultout;
1297 RAISE;
1298 --
1299 END self_or_subordinate;
1300 --
1301
1302 END PQP_SS_VEHICLE_MILEAGE_CLAIMS;
1303