[Home] [Help]
PACKAGE BODY: APPS.PAY_SIMULATOR_SS_PKG
Source
1 PACKAGE BODY pay_simulator_ss_pkg AS
2 /* $Header: pysimss.pkb 120.0.12020000.5 2013/04/25 15:04:10 apudiped noship $
3 ------------------------------------------------------------------------------
4 +==============================================================================+
5 | Copyright (c) 2013 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +==============================================================================+
9 Name
10 Payroll Simulation Self Service Package
11 Purpose
12 This package handles the Payroll Simulation process requests from UI.
13 The Insertion of data into the PER_ASSIGNMENT_EXTRA_INFO and Submition
14 and completion of the Payroll Simulation Concurrent Program
15 History
16 02 Feb 2013 apudiped Initial Version Created
17 04 Feb 2013 apudiped Removed GSCC errors
18 04 Feb 2013 apudiped Removed Debug Messages
19 06 Feb 2013 apudiped Removed Element Set Id
20 reference to the Concurrent
21 program of Simulation
22 11 Feb 2013 apudiped 16305540 Modified check_eligibilty
23 19 Feb 2013 apudiped Modified Check Eligibility
24 28 Feb 2013 apudiped Added check Actions
25 04 Apr 2013 apudiped Added Validate Input Values
26 25 Apr 2013 apudiped 16691978 Added nvl for date earned and
27 effective date for costing
28 and prepayments check
29 */
30
31
32 PROCEDURE clearpreviousdata
33 (p_assignment_id IN NUMBER)
34 is
35 begin
36
37 DELETE FROM pay_simulation_information
38 WHERE assignment_id = p_assignment_id
39 AND ACTION_CONTEXT_TYPE = 'ERROR';
40
41
42 DELETE FROM per_assignment_extra_info
43 WHERE assignment_id = p_assignment_id
44 AND aei_information_category like '%/_SIMULATION/_REGION_' escape '/';
45
46 commit;
47
48
49 END clearpreviousdata;
50
51 PROCEDURE insert_row
52 (p_assignment_id IN varchar2
53 , p_info_category IN varchar2
54 , p_info_type IN varchar2
55 , p_val1 IN varchar2
56 , p_val2 IN varchar2
57 , p_val3 IN varchar2
58 , p_val4 IN varchar2
59 , p_val5 IN varchar2
60 , p_val6 IN varchar2
61 , p_val7 IN varchar2
62 , p_val8 IN varchar2
63 , p_val9 IN varchar2
64 , p_val10 IN varchar2
65 , p_val11 IN varchar2
66 , p_val12 IN varchar2
67 , p_val13 IN varchar2
68 , p_val14 IN varchar2
69 , p_val15 IN varchar2
70 , p_val16 IN varchar2
71 , p_val17 IN varchar2
72 , p_val18 IN varchar2 , p_val19 IN varchar2
73 , p_val20 IN varchar2
74 , p_val21 IN varchar2
75 , p_val22 IN varchar2
76 , p_val23 IN varchar2
77 , p_val24 IN varchar2
78 , p_val25 IN varchar2
79 , p_val26 IN varchar2
80 , p_val27 IN varchar2
81 , p_val28 IN varchar2
82 , p_val29 IN varchar2
83 , p_val30 IN varchar2
84 , p_del_flag IN varchar2) IS
85 BEGIN
86 IF p_del_flag = 'true' THEN
87 DELETE
88 FROM per_assignment_extra_info
89 WHERE assignment_id = p_assignment_id
90 AND aei_information_category = p_info_category;
91 END IF;
92
93 INSERT
94 INTO per_assignment_extra_info (assignment_extra_info_id
95 , assignment_id
96 , information_type
97 , aei_information_category
98 , aei_information1
99 , aei_information2
100 , aei_information3
101 , aei_information4
102 , aei_information5
103 , aei_information6
104 , aei_information7
105 , aei_information8
106 , aei_information9
107 , aei_information10
108 , aei_information11
109 , aei_information12
110 , aei_information13
111 , aei_information14
112 , aei_information15
113 , aei_information16
114 , aei_information17
115 , aei_information18
116 , aei_information19
117 , aei_information20
118 , aei_information21
119 , aei_information22
120 , aei_information23
121 , aei_information24
122 , aei_information25
123 , aei_information26
124 , aei_information27
125 , aei_information28
126 , aei_information29
127 , aei_information30)
128 VALUES (per_assignment_extra_info_s.nextval
129 , p_assignment_id
130 , p_info_category
131 , p_info_category
132 , p_val1
133 , p_val2
134 , p_val3
135 , p_val4
136 , p_val5
137 , p_val6
138 , p_val7
139 , p_val8
140 , p_val9
141 , p_val10
142 , p_val11
143 , p_val12
144 , p_val13
145 , p_val14
146 , p_val15
147 , p_val16
148 , p_val17
149 , p_val18
150 , p_val19
151 , p_val20
152 , p_val21
153 , p_val22
154 , p_val23
155 , p_val24
156 , p_val25
157 , p_val26
158 , p_val27
159 , p_val28
160 , p_val29
161 , p_val30);
162 END insert_row;
163
164 FUNCTION get_employee_name
165 (p_person_id IN number) RETURN varchar2 IS
166 l_name per_all_people_f.full_name%TYPE;
167 BEGIN
168 SELECT full_name
169 INTO l_name
170 FROM per_all_people_f
171 WHERE person_id = p_person_id
172 AND sysdate
173 BETWEEN effective_start_date
174 AND effective_end_date
175 AND rownum = 1;
176
177 RETURN l_name;
178 END get_employee_name;
179
180 FUNCTION insert_assignment_set
181 (p_assignment_id IN number
182 , p_business_group_id IN number
183 , p_payroll_id IN number) RETURN number IS
184 l_assignment_set_id number;
185 l_assignment_set_name varchar2(240);
186 BEGIN
187 SELECT hr_assignment_sets_s.nextval
188 INTO l_assignment_set_id
189 FROM sys.dual;
190
191 l_assignment_set_name := 'Simulation'
192 || l_assignment_set_id
193 || p_payroll_id;
194
195 INSERT
196 INTO hr_assignment_sets (assignment_set_id
197 , business_group_id
198 , payroll_id
199 , assignment_set_name)
200 VALUES (l_assignment_set_id
201 , p_business_group_id
202 , p_payroll_id
203 , l_assignment_set_name);
204
205 INSERT
206 INTO hr_assignment_set_amendments (assignment_id
207 , assignment_set_id
208 , include_or_exclude)
209 VALUES (p_assignment_id
210 , l_assignment_set_id
211 , 'I');
212
213 RETURN l_assignment_set_id;
214 END insert_assignment_set;
215
216
217 FUNCTION submit_request
218 (p_assignment_id IN number
219 , p_business_group_id IN number
220 , p_payroll_id IN number
221 , p_consolidation_set_id IN number
222 , p_run_date IN date) RETURN number IS
223 l_request_id number;
224 CURSOR get_dates IS
225 SELECT ptp.start_date
226 , ptp.end_date
227 , ptp.regular_payment_date
228 FROM pay_all_payrolls_f papf
229 , per_time_periods ptp
230 WHERE papf.payroll_id = p_payroll_id
231 AND trunc(p_run_date)
232 BETWEEN papf.effective_start_date
233 AND papf.effective_end_date
234 AND papf.payroll_id = ptp.payroll_id
235 AND trunc(p_run_date)
236 BETWEEN ptp.start_date
237 AND ptp.end_date;
238 CURSOR check_pre_costing
239 (p_start_date IN date
240 , p_end_date IN date) IS
241 SELECT 1
242 FROM pay_payroll_actions ppa
243 , pay_assignment_actions paa
244 WHERE ppa.payroll_action_id = paa.payroll_action_id
245 AND paa.assignment_id = p_assignment_id
246 AND ppa.action_type IN ('C', 'P', 'U')
247 AND nvl(ppa.date_earned,ppa.effective_date)
248 BETWEEN p_start_date
249 AND p_end_date;
250 cursor leg_code is
251 select legislation_code
252 from per_business_groups
253 where business_group_id = p_business_group_id;
254
255 l_leg_code per_business_groups.legislation_code%type;
256 l_start_date date;
257 l_date_earned date;
258 l_date_paid date;
259 l_check number;
260 l_assignment_set_id number;
261 l_element_set_id number;
262 l_run_type_id number;
263 Stmt varchar2(240);
264 BEGIN
265
266 OPEN get_dates;
267
268 FETCH get_dates
269 INTO l_start_date
270 , l_date_earned
271 , l_date_paid;
272
273 IF get_dates%NOTFOUND THEN
274 CLOSE get_dates;
275
276 hr_utility.set_message (801, 'HR_51731_PTO_DATE_OUT_TIMEPRD');
277
278 hr_utility.raise_error;
279 END IF;
280
281 CLOSE get_dates;
282
283 OPEN check_pre_costing (l_start_date, l_date_earned);
284
285 FETCH check_pre_costing
286 INTO l_check;
287
288 IF check_pre_costing%FOUND THEN
289 CLOSE check_pre_costing;
290
291 hr_utility.set_message (801, 'PAY_SIM_506071_PRE_COST_EXISTS');
292
293 hr_utility.raise_error;
294 END IF;
295
296 CLOSE check_pre_costing;
297
298 l_assignment_set_id := insert_assignment_set (p_assignment_id, p_business_group_id
299 , p_payroll_id);
300
301 -- The Element Set Id defined at Business group level used by the Payroll Simulation PYUGEN
302 -- process
303 l_element_set_id := null;
304
305 open leg_code;
306 fetch leg_code into l_leg_code;
307 close leg_code;
308
309
310 Stmt := 'begin :l_run_type_id := pay_'||l_leg_code||'_simulation.get_run_type_id; end;';
311 execute immediate Stmt using out l_run_type_id;
312
313 if hr_utility.debug_enabled then
314 hr_utility.trace('Parameters for PYUGEN Simulation process are:');
315 hr_utility.trace('application => ''PAY');
316 hr_utility.trace(' program => ''PAY_SIM');
317 hr_utility.trace(' argument1 => ''RUN');
318 hr_utility.trace(' argument2 => '||p_payroll_id);
319 hr_utility.trace(' argument3 => '||p_consolidation_set_id);
320 hr_utility.trace(' argument4 => '||to_char (l_date_earned, 'YYYY/MM/DD HH24:MI:SS'));
321 hr_utility.trace(' argument5 => '||to_char (l_date_paid, 'YYYY/MM/DD HH24:MI:SS'));
322 hr_utility.trace(' argument6 => '||l_element_set_id);
323 hr_utility.trace(' argument7 => '||l_assignment_set_id);
324 hr_utility.trace(' argument8 => '||l_run_type_id);
325 hr_utility.trace(' argument9 => NULL');
326 hr_utility.trace(' argument10 => ''PAY_SIM');
327 end if;
328
329 l_request_id := fnd_request.submit_request
330 (application => 'PAY'
331 , program => 'PAY_SIM'
332 , argument1 => 'RUN'
333 , argument2 => p_payroll_id
334 , argument3 => p_consolidation_set_id
335 , argument4 => to_char (l_date_earned, 'YYYY/MM/DD HH24:MI:SS')
336 , argument5 => to_char (l_date_paid, 'YYYY/MM/DD HH24:MI:SS')
337 , argument6 => l_element_set_id
338 , argument7 => l_assignment_set_id
339 , argument8 => l_run_type_id
340 , argument9 => NULL
341 , argument10 => 'PAY_SIM' );
342
343
344 RETURN l_request_id;
345 END submit_request;
346
347 PROCEDURE wait_request
348 (p_assignment_id IN number
349 , p_request_id IN number
350 , p_request_status OUT nocopy varchar2
351 , p_error_type OUT nocopy varchar2
352 , p_error_message OUT nocopy varchar2) IS
353 CURSOR c_check_error IS
354 SELECT 1
355 FROM pay_simulation_information
356 WHERE assignment_id = p_assignment_id
357 AND action_context_type = 'ERROR';
358
359 CURSOR get_payroll_action_id is
360 SELECT payroll_action_id,assignment_set_id
361 from pay_payroll_actions
362 where request_id = p_request_id;
363
364 l_check_error number;
365 l_phase fnd_lookups.meaning%TYPE;
366 l_status fnd_lookups.meaning%TYPE;
367 l_dev_phase fnd_lookups.meaning%TYPE;
368 l_dev_status fnd_lookups.meaning%TYPE;
369 l_phase_code fnd_lookups.lookup_code%TYPE;
370 l_status_code fnd_lookups.lookup_code%TYPE;
371 l_check boolean;
372 l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
373 l_assignment_set_id pay_payroll_actions.assignment_set_id%type;
374 BEGIN
375
376 l_check := fnd_concurrent.wait_for_request
377 (request_id => p_request_id
378 , interval => 5
379 , phase => l_phase
380 , status => l_status
381 , dev_phase => l_dev_phase
382 , dev_status => l_dev_status
383 , message => p_error_message);
384
385
386
387
388 IF NOT l_check THEN
389 p_error_type := 'CP';
390 p_request_status := 'E';
391 p_error_message := 'Unknown error';
392
393 RETURN;
394 END IF;
395
396 SELECT lookup_code
397 INTO l_phase_code
398 FROM fnd_lookups
399 WHERE lookup_type = 'CP_PHASE_CODE'
400 AND meaning = l_phase;
401
402 SELECT lookup_code
403 INTO l_status_code
404 FROM fnd_lookups
405 WHERE lookup_type = 'CP_STATUS_CODE'
406 AND meaning = l_status;
407
408 open get_payroll_action_id;
409 fetch get_payroll_action_id into l_payroll_action_id,l_assignment_set_id;
410 if get_payroll_action_id%found then
411 py_rollback_pkg.rollback_payroll_action(p_payroll_action_id=>l_payroll_action_id);
412
413 delete from hr_assignment_set_amendments where assignment_set_id = l_assignment_set_id;
414 delete from hr_assignment_sets where assignment_set_id = l_assignment_set_id;
415
416 COMMIT;
417 end if;
418 close get_payroll_action_id;
419 IF l_phase_code = 'C'
420 AND l_status_code = 'C' THEN
421 OPEN c_check_error;
422 FETCH c_check_error INTO l_check_error;
423
424 IF c_check_error%NOTFOUND THEN
425 p_error_type := 'N';
426 p_request_status := 'C';
427 p_error_message := 'No Error';
428 RETURN;
429 END IF;
430 p_error_type := 'A';
431 p_request_status := 'C';
432 p_error_message := 'No Error';
433 RETURN;
434 END IF;
435
436 p_request_status := 'E';
437 p_error_type := 'CP';
438 END wait_request;
439
440 FUNCTION check_eligibility
441 (p_assignment_id in NUMBER) RETURN VARCHAR2
442 IS
443
444
445 cursor c_avail_rule is
446 SELECT application_column_name
447 FROM fnd_descr_flex_column_usages
448 WHERE descriptive_flexfield_name = 'Payroll Developer DF'
449 AND descriptive_flex_context_code = 'Global Data Elements'
450 AND END_USER_COLUMN_NAME = 'Modeling Availability Rule';
451
452
453
454 cursor c_from_date is
455 SELECT application_column_name
456 FROM fnd_descr_flex_column_usages
457 WHERE descriptive_flexfield_name = 'Payroll Developer DF'
458 AND descriptive_flex_context_code = 'Global Data Elements'
459 AND END_USER_COLUMN_NAME = 'Days After Period Start';
460
461 cursor c_to_date is
462 SELECT application_column_name
463 FROM fnd_descr_flex_column_usages
464 WHERE descriptive_flexfield_name = 'Payroll Developer DF'
465 AND descriptive_flex_context_code = 'Global Data Elements'
466 AND END_USER_COLUMN_NAME = 'Days Before Period End';
467
468 c_ref sys_refcursor;
469 c_avail_ref sys_refcursor;
470 l_from_date fnd_descr_flex_column_usages.application_column_name%type;
471 l_to_date fnd_descr_flex_column_usages.application_column_name%type;
472 l_col_rule fnd_descr_flex_column_usages.application_column_name%type;
473 l_check number;
474 l_rule varchar2(10);
475
476 BEGIN
477
478 open c_avail_rule;
479 fetch c_avail_rule into l_col_rule;
480 close c_avail_rule;
481
482 open c_avail_ref for 'select '||l_col_rule||'
483 from pay_payrolls_f ppf, per_Assignments_f paf
484 where paf.assignment_id = '||p_assignment_id||' '||'
485 and trunc(sysdate) between paf.effective_Start_Date and paf.effective_end_date
486 and paf.payroll_id = ppf.payroll_id
487 and trunc(sysdate) between ppf.effective_start_Date and ppf.effective_end_date';
488 fetch c_avail_ref into l_rule;
489 close c_avail_ref;
490
491 open c_from_date;
492 fetch c_from_date into l_from_date;
493 close c_from_date;
494
495 open c_to_date;
496 fetch c_to_date into l_to_date;
497 close c_to_date;
498
499 if l_rule = 'A' then
500
501 open c_ref for 'select 1
502 from pay_payrolls_f ppf, per_Assignments_f paf, per_time_periods ptp
503 where paf.assignment_id = '||p_assignment_id||' '||'
504 and trunc(sysdate) between paf.effective_Start_Date and paf.effective_end_date
505 and paf.payroll_id = ppf.payroll_id
506 and trunc(sysdate) between ppf.effective_start_Date and ppf.effective_end_date
507 and ptp.payroll_id = ppf.payroll_id
508 and trunc(sysdate) >= ptp.start_date + nvl(to_number('||l_from_date||'),0)
509 and trunc(sysdate) <= ptp.end_date - nvl(to_number('||l_to_date||'),0)
510 and trunc(sysdate) between ptp.start_Date and ptp.end_date';
511 fetch c_ref into l_check;
512 if c_ref%notfound then
513 close c_ref;
514 return 'false';
515 end if;
516 close c_ref;
517 RETURN 'true';
518
519 elsif l_rule = 'U' then
520
521 open c_ref for 'select 1
522 from pay_payrolls_f ppf, per_Assignments_f paf, per_time_periods ptp
523 where paf.assignment_id = '||p_assignment_id||' '||'
524 and trunc(sysdate) between paf.effective_Start_Date and paf.effective_end_date
525 and paf.payroll_id = ppf.payroll_id
526 and trunc(sysdate) between ppf.effective_start_Date and ppf.effective_end_date
527 and ptp.payroll_id = ppf.payroll_id
528 and trunc(sysdate) >= ptp.start_date + nvl(to_number('||l_from_date||'),0)
529 and trunc(sysdate) <= ptp.end_date - nvl(to_number('||l_to_date||'),0)
530 and trunc(sysdate) between ptp.start_Date and ptp.end_date';
531 fetch c_ref into l_check;
532 if c_ref%notfound then
533 close c_ref;
534 return 'true';
535 end if;
536 close c_ref;
537 RETURN 'false';
538
539
540 else
541 return 'true';
542 end if;
543
544 END check_eligibility;
545
546 FUNCTION check_actions
547 (p_assignment_id in NUMBER) RETURN VARCHAR2
548 is
549
550 CURSOR get_dates IS
551 SELECT ptp.start_date
552 , ptp.end_date
553 , ptp.regular_payment_date
554 FROM pay_all_payrolls_f papf
555 , per_time_periods ptp
556 , per_all_assignments_f paf
557 WHERE papf.payroll_id = paf.payroll_id
558 AND paf.assignment_id = p_assignment_id
559 AND trunc(sysdate)
560 BETWEEN papf.effective_start_date
561 AND papf.effective_end_date
562 AND papf.payroll_id = ptp.payroll_id
563 AND trunc(sysdate)
564 BETWEEN ptp.start_date
565 AND ptp.end_date;
566
567 CURSOR check_pre_costing
568 (p_start_date IN date
569 , p_end_date IN date) IS
570 SELECT 1
571 FROM pay_payroll_actions ppa
572 , pay_assignment_actions paa
573 WHERE ppa.payroll_action_id = paa.payroll_action_id
574 AND paa.assignment_id = p_assignment_id
575 AND ppa.action_type IN ('C', 'P', 'U')
576 AND nvl(ppa.date_earned,ppa.effective_date)
577 BETWEEN p_start_date
578 AND p_end_date;
579
580
581 l_start_date date;
582 l_date_earned date;
583 l_date_paid date;
584 l_check number;
585
586 cursor check_actions
587 is
588 select 1
589 from per_business_groups_perf pbg,
590 per_all_assignments_f asg,
591 per_all_assignments_f as2,
592 per_periods_of_service pos
593 where asg.assignment_id = p_assignment_id
594 and pos.period_of_service_id = asg.period_of_service_id
595 and l_date_paid between
596 asg.effective_start_date and asg.effective_end_date
597 and as2.assignment_id = asg.assignment_id
598 and l_date_paid between
599 as2.effective_start_date and as2.effective_end_date
600 and pbg.business_group_id = asg.business_group_id
601 and not exists (
602 select /*+ INDEX (pa2 PAY_PAYROLL_ACTIONS_PK)
603 INDEX (ac2 PAY_ASSIGNMENT_ACTIONS_N1) */ null
604 from pay_action_classifications acl,
605 pay_assignment_actions ac2,
606 pay_payroll_actions pa2,
607 per_all_assignments_f as2
608 where as2.period_of_service_id = pos.period_of_service_id
609 and ac2.assignment_id = as2.assignment_id
610 and pa2.payroll_action_id = ac2.payroll_action_id
611 and acl.classification_name = 'SEQUENCED'
612 and pa2.action_type = acl.action_type
613 and (pa2.effective_date > l_date_paid
614 or (ac2.action_status not in ('C', 'S')
615 and pa2.effective_date <= l_date_paid)));
616
617
618
619
620 BEGIN
621
622 OPEN get_dates;
623
624 FETCH get_dates
625 INTO l_start_date
626 , l_date_earned
627 , l_date_paid;
628
629 IF get_dates%NOTFOUND THEN
630 CLOSE get_dates;
631
632 RETURN 'HR_51731_PTO_DATE_OUT_TIMEPRD';
633
634 END IF;
635
636 CLOSE get_dates;
637
638 OPEN check_pre_costing (l_start_date, l_date_earned);
639
640 FETCH check_pre_costing
641 INTO l_check;
642
643 IF check_pre_costing%FOUND THEN
644 CLOSE check_pre_costing;
645
646 RETURN 'PAY_SIM_506071_PRE_COST_EXISTS';
647
648 END IF;
649
650 CLOSE check_pre_costing;
651
652 open check_actions;
653 fetch check_actions into l_check;
654 IF check_actions%NOTFOUND THEN
655 CLOSE check_actions;
656 RETURN 'HR_7010_ACTION_INTLOCK_FAIL';
657 END IF;
658 CLOSE check_actions;
659 RETURN null;
660
661
662
663 end check_actions;
664
665 PROCEDURE validate_input_values
666 (p_assignment_id in number,
667 p_input_value_id in NUMBER,
668 p_screen_entry_value in out nocopy VARCHAR2,
669 p_internal_value out nocopy varchar2,
670 p_status out nocopy varchar2)
671 is
672
673 cursor csr_table_inp_ids is
674 select pet.element_name||'-'||inv.name,inv.uom,inv.lookup_type,inv.value_set_id,inv.min_value,inv.max_value,pet.input_currency_code
675 from pay_input_values_f inv,pay_element_types_f pet
676 where inv.input_value_id = p_input_value_id
677 and trunc(sysdate) between inv.effective_start_date
678 and inv.effective_end_date
679 and inv.element_type_id = pet.element_type_id
680 and trunc(sysdate) between pet.effective_start_date
681 and pet.effective_end_date;
682
683 cursor c_input_name
684 is
685 select ACTION_INFORMATION1
686 from pay_simulation_information
687 where assignment_id = p_assignment_id
688 and ACTION_CONTEXT_TYPE = 'INPUTSLOV'
689 and ACTION_INFORMATION3 = to_char(p_input_value_id);
690
691 l_name pay_simulation_information.ACTION_INFORMATION1%type;
692 l_uom pay_input_values_f.uom%TYPE;
693 --
694 l_lookup_type pay_input_values_f.lookup_type%TYPE;
695 l_value_set_id pay_input_values_f.value_set_id%TYPE;
696 l_input_curr_code pay_element_types_f.input_currency_code%TYPE;
697 --
698 l_element_type_id pay_element_types_f.element_type_id%TYPE := null;
699 l_element_name pay_element_types_f.element_name%TYPE := null;
700 l_count number := 0;
701 l_legislation_code per_business_groups.legislation_code%TYPE := null;
702 l_business_group_id per_business_groups.business_group_id%TYPE;
703 l_batch_id pay_batch_lines.batch_id%TYPE;
704 l_effective_date pay_batch_lines.effective_date%TYPE;
705 l_date_prof_value varchar2(80);
706 l_number_prof_value varchar2(80);
707 --
708 /*Temporary variables for doing only validation when BEE_IV_UPG is 'N'*/
709 --
710 temp_iv1 pay_batch_lines.value_1%TYPE := null;
711 --
712
713
714
715 lv_dummy varchar2(100);
716 l_min_value pay_input_values_f.min_value%type;
717 l_max_value pay_input_values_f.max_value%type;
718
719 --
720 l_proc varchar2(72) := 'validate_input_values';
721 --
722 begin
723 --
724 hr_utility.set_location('Entering:'|| l_proc, 10);
725 open c_input_name;
726 fetch c_input_name into l_name;
727 close c_input_name;
728 fnd_profile.get('ICX_DATE_FORMAT_MASK', l_date_prof_value);
729 l_date_prof_value := nvl(l_date_prof_value,'DD-MON-RRRR');
730
731 open csr_table_inp_ids;
732 fetch csr_table_inp_ids into l_element_name,l_uom,l_lookup_type,l_value_set_id,l_min_value,l_max_value,l_input_curr_code;
733 close csr_table_inp_ids;
734
735 if l_name is null then
736 l_name := l_element_name;
737 end if;
738
739 begin
740 If l_uom = 'D' then
741 p_internal_value :=to_char(to_date(p_screen_entry_value,l_date_prof_value),'DD-MON-RRRR');
742 else
743
744 hr_chkfmt.checkformat (p_screen_entry_value,l_uom,p_internal_value,l_min_value,l_max_value,'Y',lv_dummy,l_input_curr_code );
745
746 end if;
747
748 exception
749 when others then
750 fnd_message.set_name('PAY','HR_51119_HRPROC_ERR_OCC_ON_ET');
751 fnd_message.set_token ('ETNAME', l_name);
752 p_status := hr_utility.get_message;
753 fnd_message.set_token ('ETNAME', 'Element');
754 fnd_message.set_name ('PAY','PAY_6306_INPUT_VALUE_FORMAT');
755 fnd_message.set_token ('UNIT_OF_MEASURE', hr_general.decode_lookup ('UNITS', l_uom ));
756 p_status := p_status ||':'|| hr_utility.get_message;
757 end;
758 if p_status is null then
759 p_status := 'NoError';
760 end if;
761
762 end validate_input_values;
763
764 END pay_simulator_ss_pkg;