1 PACKAGE BODY pay_fi_general AS
2 /* $Header: pyfigenr.pkb 120.23.12010000.2 2008/08/19 14:03:04 rmurahar ship $ */
3 --
4 g_formula_name ff_formulas_f.formula_name%TYPE;
5 --
6 g_formula_name ff_formulas_f.formula_name%TYPE;
7 --
8 g_package varchar2(33) := ' PAY_FI_GENERAL.'; -- Global package name
9 g_legislation_code varchar2(150) default null;
10 g_absence_attendance_id number default null;
11 FUNCTION good_time_format ( p_time IN VARCHAR2 ) RETURN BOOLEAN IS
12 --
13 BEGIN
14 --
15 IF p_time IS NOT NULL THEN
16 --
17 IF NOT (SUBSTR(p_time,1,2) BETWEEN '00' AND '23' AND
18 SUBSTR(p_time,4,2) BETWEEN '00' AND '59' AND
19 SUBSTR(p_time,3,1) = ':' AND
20 LENGTH(p_time) = 5) THEN
21 RETURN FALSE;
22 ELSE
23 RETURN TRUE;
24 END IF;
25 --
26 ELSE
27 RETURN FALSE;
28 END IF;
29 --
30 EXCEPTION
31 --
32 WHEN OTHERS THEN
33 RETURN FALSE;
34 --
35 END good_time_format;
36
37 PROCEDURE run_formula(p_formula_id IN NUMBER
38 ,p_effective_date IN DATE
39 ,p_inputs IN ff_exec.inputs_t
40 ,p_outputs IN OUT NOCOPY ff_exec.outputs_t) IS
41 l_inputs ff_exec.inputs_t;
42 l_outputs ff_exec.outputs_t;
43 BEGIN
44 hr_utility.set_location('--In Formula ',20);
45 --
46 -- Initialize the formula
47 --
48 ff_exec.init_formula(p_formula_id, p_effective_date , l_inputs, l_outputs);
49 --
50 hr_utility.trace('after ff_exec');
51 -- Set up the input values
52 --
53 IF l_inputs.count > 0 and p_inputs.count > 0 THEN
54 FOR i IN l_inputs.first..l_inputs.last LOOP
55 FOR j IN p_inputs.first..p_inputs.last LOOP
56 IF l_inputs(i).name = p_inputs(j).name THEN
57 l_inputs(i).value := p_inputs(j).value;
58 exit;
59 END IF;
60 END LOOP;
61 END LOOP;
62 END IF;
63 --
64 -- Run the formula
65 --
66 hr_utility.trace('about to exec');
67 ff_exec.run_formula(l_inputs,l_outputs);
68 --
69 -- Populate the output table
70 --
71 IF l_outputs.count > 0 and p_inputs.count > 0 then
72 FOR i IN l_outputs.first..l_outputs.last LOOP
73 FOR j IN p_outputs.first..p_outputs.last LOOP
74 IF l_outputs(i).name = p_outputs(j).name THEN
75 p_outputs(j).value := l_outputs(i).value;
76 exit;
77 END IF;
78 END LOOP;
79 END LOOP;
80 END IF;
81 EXCEPTION
82 /*WHEN hr_formula_error THEN
83 fnd_message.set_name('PER','FFX22J_FORMULA_NOT_FOUND');
84 fnd_message.set_token('1', g_formula_name);
85 fnd_message.raise_error;*/
86 WHEN OTHERS THEN
87 raise;
88 --
89 END run_formula;
90
91
92 function run_holiday_pay_formula
93 (p_assignment_id IN NUMBER
94 ,p_date_earned IN DATE
95 ,p_business_group_id IN NUMBER
96 ,p_payroll_id IN NUMBER
97 ,p_payroll_action_id IN NUMBER
98 ,p_assignment_action_id IN NUMBER
99 ,p_tax_unit_id IN NUMBER
100 ,p_element_entry_id IN NUMBER
101 ,p_element_type_id IN NUMBER
102 ,p_original_entry_id IN NUMBER
103 ,p_balance_date IN DATE
104 ,p_holiday_days OUT NOCOPY NUMBER
105 ,p_holiday_compensation OUT NOCOPY NUMBER
106 ,p_holiday_pay_reserve OUT NOCOPY NUMBER
107 ,p_working_days OUT NOCOPY NUMBER
108 ,p_working_hours OUT NOCOPY NUMBER
109 )
110 return NUMBER is
111 cursor csr_get_formula_id(p_effective_date in date,p_assignment_id in number,p_input_value in varchar2) is
112 SELECT FF.FORMULA_ID
113 FROM per_all_assignments_f asg1
114 ,pay_element_links_f el
115 ,pay_element_types_f et
116 ,pay_input_values_f iv1
117 ,pay_element_entries_f ee
118 ,pay_element_entry_values_f eev1
119 , ff_formulas_f ff
120 WHERE asg1.assignment_id = p_assignment_id
121 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
122 AND et.element_name = 'Holiday Pay Information'
123 AND et.legislation_code = 'FI'
124 AND iv1.element_type_id = et.element_type_id
125 AND iv1.name = p_input_value
126 AND el.business_group_id = asg1.business_group_id
127 AND el.element_type_id = et.element_type_id
128 AND ee.assignment_id = asg1.assignment_id
129 AND ee.element_link_id = el.element_link_id
130 AND eev1.element_entry_id = ee.element_entry_id
131 AND eev1.input_value_id = iv1.input_value_id
132 AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
133 AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
134 AND p_effective_date BETWEEN el.effective_start_date AND el.effective_end_date
135 AND p_effective_date BETWEEN et.effective_start_date AND et.effective_end_date
136 AND p_effective_date BETWEEN iv1.effective_start_date AND iv1.effective_end_date
137 AND FF.FORMULA_NAME=eev1.SCREEN_ENTRY_VALUE;
138
139
140 /* SELECT PRL_INFORMATION1
141 FROM PAY_PAYROLLS PP
142 WHERE pp.business_group_id +0 = p_business_group_id
143 and pp.payroll_id = p_payroll_id
144 and trunc(p_date_earned) between pp.effective_start_date and nvl(pp.effective_end_date,p_date_earned)
145 and PRL_INFORMATION_CATEGORY = 'FI';
146 */
147 l_formula_id NUMBER;
148 l_inputs ff_exec.inputs_t;
149 l_outputs ff_exec.outputs_t;
150 l_value NUMBER;
151 begin
152 open csr_get_formula_id(p_date_earned,p_assignment_id,'Accrual Formula');
153 fetch csr_get_formula_id into l_formula_id;
154 close csr_get_formula_id;
155 l_inputs(1).name := 'ASSIGNMENT_ID';
156 l_inputs(1).value := p_assignment_id;
157 l_inputs(2).name := 'DATE_EARNED';
158 l_inputs(2).value := fnd_date.date_to_canonical(p_date_earned);
159 l_inputs(3).name := 'BUSINESS_GROUP_ID';
160 l_inputs(3).value := p_business_group_id;
161 l_inputs(4).name := 'PAYROLL_ID';
162 l_inputs(4).value := p_payroll_id;
163 l_inputs(5).name := 'PAYROLL_ACTION_ID';
164 l_inputs(5).value := p_payroll_action_id;
165 l_inputs(6).name := 'ASSIGNMENT_ACTION_ID';
166 l_inputs(6).value := p_assignment_action_id;
167 l_inputs(7).name := 'TAX_UNIT_ID';
168 l_inputs(7).value := p_tax_unit_id;
169 l_inputs(8).name := 'ELEMENT_ENTRY_ID';
170 l_inputs(8).value := p_element_entry_id;
171 l_inputs(9).name := 'ELEMENT_TYPE_ID';
172 l_inputs(9).value := p_element_type_id;
173 l_inputs(10).name := 'ORIGINAL_ENTRY_ID';
174 l_inputs(10).value := p_original_entry_id;
175 l_inputs(11).name := 'BALANCE_DATE';
176 l_inputs(11).value := fnd_date.date_to_canonical(p_balance_date);
177
178 l_outputs(1).name := 'HOLIDAY_DAYS';
179 l_outputs(2).name := 'HOLIDAY_COMPENSATION';
180 l_outputs(3).name := 'HOLIDAY_PAY_RESERVE';
181 l_outputs(4).name := 'WORKING_DAYS';
182 l_outputs(5).name := 'WORKING_HOURS';
183
184 --hr_utility.trace_on(null,'A');
185 hr_utility.trace('p_assignment_id '||p_assignment_id);
186 hr_utility.trace('p_date_earned '||l_inputs(2).value);
187 hr_utility.trace('p_balance_date '||l_inputs(11).value);
188 l_inputs(11).value :=l_inputs(2).value ;
189 hr_utility.trace('p_date_earned '||l_inputs(2).value);
190 hr_utility.trace('p_balance_date '||l_inputs(11).value);
191 --hr_utility.trace_off;
192
193 if l_formula_id is not null then
194 run_formula (l_formula_id
195 ,p_date_earned
196 ,l_inputs
197 ,l_outputs);
198
199 end if;
200
201
202 IF l_outputs.count > 0 and l_outputs.count > 0 THEN
203 FOR i IN l_outputs.first..l_outputs.last LOOP
204 IF l_outputs(i).name like 'HOLIDAY_DAYS' THEN
205 p_holiday_days := l_outputs(i).value;
206 ELSIF l_outputs(i).name like 'HOLIDAY_COMPENSATION' THEN
207 p_holiday_compensation := l_outputs(i).value;
208 ELSIF l_outputs(i).name like 'HOLIDAY_PAY_RESERVE' THEN
209 p_holiday_pay_reserve := l_outputs(i).value;
210 ELSIF l_outputs(i).name like 'WORKING_DAYS' THEN
211 p_working_days := l_outputs(i).value;
212 ELSIF l_outputs(i).name like 'WORKING_HOURS' THEN
213 p_working_hours := l_outputs(i).value;
214 END IF;
215 END LOOP;
216 END IF;
217 RETURN '1';
218 -- return(l_value);
219 end run_holiday_pay_formula;
220
221
222 FUNCTION get_accrual_status
223 (p_time_definition_id IN NUMBER
224 ,p_balance_date IN DATE
225 ,p_payroll_start_date IN DATE
226 ,p_payroll_end_date IN DATE
227 ) RETURN NUMBER
228 AS
229 CURSOR c_ptp IS
230 SELECT end_date
231 FROM per_time_periods
232 WHERE time_definition_id = p_time_definition_id
233 AND end_date BETWEEN p_payroll_start_date
234 AND p_payroll_end_date ;
235
236 l_end_date DATE ;
237
238 BEGIN
239
240
241 OPEN c_ptp;
242 FETCH c_ptp INTO l_end_date;
243 CLOSE c_ptp ;
244
245 IF l_end_date IS NULL THEN
246
247 RETURN 0 ;
248
249 ELSE
250
251 IF trunc(l_end_date) = trunc(p_balance_date) THEN
252
253 RETURN 1 ;
254
255 ELSE
256
257 RETURN 0 ;
258
259 END IF;
260
261 END IF;
262 EXCEPTION
263
264 WHEN others THEN
265 RETURN 0 ;
266
267 END ;
268 function element_exist(p_assignment_id in number ,p_date_earned in date,p_element_name in varchar2 ) return number is
269 l_element_exist number;
270 cursor check_element_exist(p_assignment_id in number ,p_effective_date in date,p_element_name in varchar2 ) is
271 SELECT 1
272 FROM per_all_assignments_f asg
273 ,pay_element_links_f el
274 ,pay_element_types_f et
275 ,pay_element_entries_f ee
276 WHERE asg.assignment_id = p_assignment_id
277 AND et.element_name = p_element_name
278 AND et.legislation_code = 'FI'
279 AND el.business_group_id = asg.business_group_id
280 AND el.element_type_id = et.element_type_id
281 AND ee.assignment_id = asg.assignment_id
282 AND ee.element_link_id = el.element_link_id
283 AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
284 AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
285 AND p_effective_date BETWEEN et.effective_start_date AND et.effective_end_date
286 AND p_effective_date BETWEEN el.effective_start_date AND el.effective_end_date ;
287
288
289 begin
290
291 l_element_exist := 0;
292 open check_element_exist(p_assignment_id,p_date_earned ,p_element_name );
293 fetch check_element_exist into l_element_exist;
294 close check_element_exist;
295
296 return l_element_exist;
297
298 end element_exist;
299
300
301
302 function run_holiday_pay_entitlement
303 (p_assignment_id IN NUMBER
304 ,p_date_earned IN DATE
305 ,p_business_group_id IN NUMBER
306 ,p_payroll_id IN NUMBER
307 ,p_payroll_action_id IN NUMBER
308 ,p_assignment_action_id IN NUMBER
309 ,p_tax_unit_id IN NUMBER
310 ,p_element_entry_id IN NUMBER
311 ,p_element_type_id IN NUMBER
312 ,p_original_entry_id IN NUMBER
313 ,p_balance_date IN DATE
314 , p_summer_holiday_days OUT NOCOPY NUMBER
315 , p_winter_holiday_days OUT NOCOPY NUMBER
316 , p_holiday_pay OUT NOCOPY NUMBER
317 , p_holiday_compensation OUT NOCOPY NUMBER
318 , p_carryover_holiday_days OUT NOCOPY NUMBER
319 , p_carryover_holiday_pay OUT NOCOPY NUMBER
320 , p_carryover_holiday_compen OUT NOCOPY NUMBER
321
322 )
323 return NUMBER is
324 cursor csr_get_formula_id(p_effective_date in date,p_assignment_id in number,p_input_value in varchar2) is
325 SELECT FF.FORMULA_ID
326 FROM per_all_assignments_f asg1
327 ,pay_element_links_f el
328 ,pay_element_types_f et
329 ,pay_input_values_f iv1
330 ,pay_element_entries_f ee
331 ,pay_element_entry_values_f eev1
332 , ff_formulas_f ff
333 WHERE asg1.assignment_id = p_assignment_id
334 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
335 AND et.element_name = 'Holiday Pay Information'
336 AND et.legislation_code = 'FI'
337 AND iv1.element_type_id = et.element_type_id
338 AND iv1.name = p_input_value
339 AND el.business_group_id = asg1.business_group_id
340 AND el.element_type_id = et.element_type_id
341 AND ee.assignment_id = asg1.assignment_id
342 AND ee.element_link_id = el.element_link_id
343 AND eev1.element_entry_id = ee.element_entry_id
344 AND eev1.input_value_id = iv1.input_value_id
345 AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
346 AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
347 AND p_effective_date BETWEEN el.effective_start_date AND el.effective_end_date
348 AND p_effective_date BETWEEN et.effective_start_date AND et.effective_end_date
349 AND p_effective_date BETWEEN iv1.effective_start_date AND iv1.effective_end_date
350 AND FF.FORMULA_NAME=eev1.SCREEN_ENTRY_VALUE;
351
352
353 /* SELECT PRL_INFORMATION1
354 FROM PAY_PAYROLLS PP
355 WHERE pp.business_group_id +0 = p_business_group_id
356 and pp.payroll_id = p_payroll_id
357 and trunc(p_date_earned) between pp.effective_start_date and nvl(pp.effective_end_date,p_date_earned)
358 and PRL_INFORMATION_CATEGORY = 'FI';
359 */
360 l_formula_id NUMBER;
361 l_inputs ff_exec.inputs_t;
362 l_outputs ff_exec.outputs_t;
363 l_value NUMBER;
364 begin
365 open csr_get_formula_id(p_date_earned,p_assignment_id,'Entitlement Formula');
366 fetch csr_get_formula_id into l_formula_id;
367 close csr_get_formula_id;
368 l_inputs(1).name := 'ASSIGNMENT_ID';
369 l_inputs(1).value := p_assignment_id;
370 l_inputs(2).name := 'DATE_EARNED';
371 l_inputs(2).value := fnd_date.date_to_canonical(p_date_earned);
372 l_inputs(3).name := 'BUSINESS_GROUP_ID';
373 l_inputs(3).value := p_business_group_id;
374 l_inputs(4).name := 'PAYROLL_ID';
375 l_inputs(4).value := p_payroll_id;
376 l_inputs(5).name := 'PAYROLL_ACTION_ID';
377 l_inputs(5).value := p_payroll_action_id;
378 l_inputs(6).name := 'ASSIGNMENT_ACTION_ID';
379 l_inputs(6).value := p_assignment_action_id;
380 l_inputs(7).name := 'TAX_UNIT_ID';
381 l_inputs(7).value := p_tax_unit_id;
382 l_inputs(8).name := 'ELEMENT_ENTRY_ID';
383 l_inputs(8).value := p_element_entry_id;
384 l_inputs(9).name := 'ELEMENT_TYPE_ID';
385 l_inputs(9).value := p_element_type_id;
386 l_inputs(10).name := 'ORIGINAL_ENTRY_ID';
387 l_inputs(10).value := p_original_entry_id;
388 l_inputs(11).name := 'BALANCE_DATE';
389 l_inputs(11).value := fnd_date.date_to_canonical(p_balance_date);
390
391 l_outputs(1).name := 'SUMMER_HOLIDAY_DAYS';
392 l_outputs(2).name := 'WINTER_HOLIDAY_DAYS';
393 l_outputs(3).name := 'HOLIDAY_PAY';
394 l_outputs(4).name := 'HOLIDAY_COMPENSATION';
395 l_outputs(5).name := 'CARRYOVER_HOLIDAY_DAYS';
396 l_outputs(6).name := 'CARRYOVER_HOLIDAY_PAY';
397 l_outputs(7).name := 'CARRYOVER_HOLIDAY_COMPEN';
398
399 if l_formula_id is not null then
400 run_formula (l_formula_id
401 ,p_date_earned
402 ,l_inputs
403 ,l_outputs);
404
405 end if;
406
407
408 IF l_outputs.count > 0 and l_outputs.count > 0 THEN
409 FOR i IN l_outputs.first..l_outputs.last LOOP
410 IF l_outputs(i).name like 'SUMMER_HOLIDAY_DAYS' THEN
411 p_summer_holiday_days:= l_outputs(i).value;
412 ELSIF l_outputs(i).name like 'WINTER_HOLIDAY_DAYS' THEN
413 p_winter_holiday_days := l_outputs(i).value;
414 ELSIF l_outputs(i).name like 'HOLIDAY_PAY' THEN
415 p_holiday_pay := l_outputs(i).value;
416 ELSIF l_outputs(i).name like 'HOLIDAY_COMPENSATION' THEN
417 p_holiday_compensation := l_outputs(i).value;
418 ELSIF l_outputs(i).name like 'CARRYOVER_HOLIDAY_DAYS' THEN
419 p_carryover_holiday_days := l_outputs(i).value;
420 ELSIF l_outputs(i).name like 'CARRYOVER_HOLIDAY_COMPEN' THEN
421 p_carryover_holiday_compen := l_outputs(i).value;
422 ELSIF l_outputs(i).name like 'CARRYOVER_HOLIDAY_PAY' THEN
423 p_carryover_holiday_pay := l_outputs(i).value;
424
425
426 END IF;
427 END LOOP;
428 END IF;
429
430 RETURN '1';
431 -- return(l_value);
432 end run_holiday_pay_entitlement;
433 FUNCTION get_holiday_pay_accr_override
434 (p_assignment_id NUMBER
435 , p_effective_date DATE
436 , p_holiday_days OUT NOCOPY NUMBER
437 , p_holiday_compensation OUT NOCOPY NUMBER
438 , p_holiday_pay_reserve OUT NOCOPY NUMBER
439 , p_working_days OUT NOCOPY NUMBER
440 , p_working_hours OUT NOCOPY NUMBER
441 ) RETURN NUMBER IS
442 --
443 CURSOR get_details(p_assignment_id NUMBER,p_effective_date DATE , p_input_value VARCHAR2 ) IS
444 SELECT eev1.SCREEN_ENTRY_VALUE
445 FROM per_all_assignments_f asg1
446 ,pay_element_links_f el
447 ,pay_element_types_f et
448 ,pay_input_values_f iv1
449 ,pay_element_entries_f ee
450 ,pay_element_entry_values_f eev1
451 WHERE asg1.assignment_id = p_assignment_id
452 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
453 AND et.element_name = 'Holiday Pay Accrual Override'
454 AND et.legislation_code = 'FI'
455 AND iv1.element_type_id = et.element_type_id
456 AND iv1.name = p_input_value
457 AND el.business_group_id = asg1.business_group_id
458 AND el.element_type_id = et.element_type_id
459 AND ee.assignment_id = asg1.assignment_id
460 AND ee.element_link_id = el.element_link_id
461 AND eev1.element_entry_id = ee.element_entry_id
462 AND eev1.input_value_id = iv1.input_value_id
463 AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
464 AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
465
466 --
467 --
468 BEGIN
469 --
470 OPEN get_details(p_assignment_id ,p_effective_date,'Holiday Days');
471 FETCH get_details INTO p_holiday_days ;
472 CLOSE get_details;
473
474 OPEN get_details(p_assignment_id ,p_effective_date,'Holiday Compensation');
475 FETCH get_details INTO p_holiday_compensation ;
476 CLOSE get_details;
477 OPEN get_details(p_assignment_id ,p_effective_date,'Holiday Pay Reserve');
481 OPEN get_details(p_assignment_id ,p_effective_date,'Working Days');
478 FETCH get_details INTO p_holiday_pay_reserve ;
479 CLOSE get_details;
480
482 FETCH get_details INTO p_working_days ;
483 CLOSE get_details;
484
485 OPEN get_details(p_assignment_id ,p_effective_date,'Working Hours');
486 FETCH get_details INTO p_working_hours ;
487 CLOSE get_details;
488
489 IF p_holiday_days IS NULL THEN
490 p_holiday_days:= -1;
491 END IF;
492
493 IF p_holiday_compensation IS NULL THEN
494 p_holiday_compensation:= -1;
495 END IF;
496
497 IF p_holiday_pay_reserve IS NULL THEN
498 p_holiday_pay_reserve:= -1;
499 END IF;
500
501 IF p_working_days IS NULL THEN
502 p_working_days:= -1;
503 END IF;
504
505 IF p_working_hours IS NULL THEN
506 p_working_hours:= -1;
507 END IF;
508 --
509 IF p_holiday_days = -1 AND
510 p_holiday_compensation = -1 AND
511 p_holiday_pay_reserve = -1 AND
512 p_working_days = -1 AND
513 p_working_hours = -1
514 THEN RETURN -1;
515 ELSE
516 RETURN 1;
517 END IF;
518
519 -- RETURN 1;
520
521
522
523 EXCEPTION
524 WHEN OTHERS THEN
525 RETURN 0 ;
526 --
527 END ;
528
529 FUNCTION get_holiday_pay_entitle_over
530 (p_assignment_id NUMBER
531 , p_effective_date DATE
532 , p_summer_holiday_days OUT NOCOPY NUMBER
533 , p_winter_holiday_days OUT NOCOPY NUMBER
534 , p_holiday_pay OUT NOCOPY NUMBER
535 , p_holiday_compensation OUT NOCOPY NUMBER
536 , p_carryover_holiday_days OUT NOCOPY NUMBER
537 , p_carryover_holiday_pay OUT NOCOPY NUMBER
538 , p_carryover_holiday_compen OUT NOCOPY NUMBER
539 , p_average_hourly_pay OUT NOCOPY NUMBER
540 , p_average_daily_pay OUT NOCOPY NUMBER
541 ) RETURN NUMBER IS
542 --
543 CURSOR get_details(p_assignment_id NUMBER,p_effective_date DATE , p_input_value VARCHAR2 ) IS
544 SELECT eev1.SCREEN_ENTRY_VALUE
545 FROM per_all_assignments_f asg1
546 ,pay_element_links_f el
547 ,pay_element_types_f et
548 ,pay_input_values_f iv1
549 ,pay_element_entries_f ee
550 ,pay_element_entry_values_f eev1
551 WHERE asg1.assignment_id = p_assignment_id
552 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
553 AND et.element_name = 'Holiday Pay Entitlement Override'
554 AND et.legislation_code = 'FI'
555 AND iv1.element_type_id = et.element_type_id
556 AND iv1.name = p_input_value
557 AND el.business_group_id = asg1.business_group_id
558 AND el.element_type_id = et.element_type_id
559 AND ee.assignment_id = asg1.assignment_id
560 AND ee.element_link_id = el.element_link_id
561 AND eev1.element_entry_id = ee.element_entry_id
562 AND eev1.input_value_id = iv1.input_value_id
563 AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
564 AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
565
566 --
567 --
568 BEGIN
569 --
570 OPEN get_details(p_assignment_id ,p_effective_date,'Summer Holiday Days');
571 FETCH get_details INTO p_summer_holiday_days ;
572 CLOSE get_details;
573
574 OPEN get_details(p_assignment_id ,p_effective_date,'Winter Holiday Days');
575 FETCH get_details INTO p_winter_holiday_days ;
576 CLOSE get_details;
577
578 OPEN get_details(p_assignment_id ,p_effective_date,'Holiday Pay');
579 FETCH get_details INTO p_holiday_pay ;
580 CLOSE get_details;
581
582 OPEN get_details(p_assignment_id ,p_effective_date,'Holiday Compensation');
583 FETCH get_details INTO p_holiday_compensation ;
584 CLOSE get_details;
585
586 OPEN get_details(p_assignment_id ,p_effective_date,'Holiday Days Carryover');
587 FETCH get_details INTO p_carryover_holiday_days ;
588 CLOSE get_details;
589
590
591 OPEN get_details(p_assignment_id ,p_effective_date,'Holiday Pay Carryover');
592 FETCH get_details INTO p_carryover_holiday_pay ;
593 CLOSE get_details;
594
595 OPEN get_details(p_assignment_id ,p_effective_date,'Holiday Compensation Carryover');
596 FETCH get_details INTO p_carryover_holiday_compen ;
597 CLOSE get_details;
598
599 OPEN get_details(p_assignment_id ,p_effective_date,'Average Hourly Pay');
600 FETCH get_details INTO p_average_hourly_pay ;
601 CLOSE get_details;
602
603 OPEN get_details(p_assignment_id ,p_effective_date,'Average Daily Pay');
604 FETCH get_details INTO p_average_daily_pay ;
605 CLOSE get_details;
606
607
608 /* IF p_summer IS NULL THEN
609 p_holiday_days:= -1;
610 END IF;
611
612 IF p_holiday_compensation IS NULL THEN
613 p_holiday_compensation:= -1;
614 END IF;
615
616 IF p_holiday_pay_reserve IS NULL THEN
617 p_holiday_pay_reserve:= -1;
618 END IF;
619
620 IF p_working_days IS NULL THEN
621 p_working_days:= -1;
622 END IF;
623
624 IF p_working_hours IS NULL THEN
625 p_working_hours:= -1;
626 END IF;
627 --
628 IF p_holiday_days = -1 AND
629 p_holiday_compensation = -1 AND
630 p_holiday_pay_reserve = -1 AND
634 ELSE
631 p_working_days = -1 AND
632 p_working_hours = -1
633 THEN RETURN -1;
635 RETURN 1;
636 END IF;
637 */
638 RETURN 1;
639
640
641
642 EXCEPTION
643 WHEN OTHERS THEN
644 RETURN 0 ;
645 --
646 END ;
647
648
649 FUNCTION get_local_unit
650 (p_assignment_id NUMBER
651 , p_effective_date DATE )
652 RETURN VARCHAR2 AS
653 l_local_unit hr_soft_coding_keyflex.segment2%TYPE;
654
655 CURSOR c_local_unit(p_assignment_id NUMBER , p_effective_date DATE) IS
656 SELECT sck.segment2
657 FROM per_all_assignments_f asg1
658 , hr_soft_coding_keyflex sck
659 WHERE asg1.assignment_id = p_assignment_id
660 AND asg1.soft_coding_keyflex_id=sck.soft_coding_keyflex_id
661 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date;
662 BEGIN
663 OPEN c_local_unit(p_assignment_id , p_effective_date) ;
664 FETCH c_local_unit INTO l_local_unit ;
665 CLOSE c_local_unit;
666
667 RETURN l_local_unit;
668 EXCEPTION
669 WHEN OTHERS THEN
670 fnd_file.put_line(fnd_file.log,'Error message : '||SQLERRM);
671 RETURN NULL ;
672 END ;
673
674 FUNCTION get_tax_card_details
675 (p_assignment_id NUMBER
676 , p_effective_date DATE
677 ,P_julian_effective_date OUT NOCOPY NUMBER
678 ,P_tax_card_type OUT NOCOPY VARCHAR2
679 ,P_base_rate OUT NOCOPY NUMBER
680 ,P_additional_rate OUT NOCOPY NUMBER
681 ,P_yearly_income_limit OUT NOCOPY NUMBER
682 ,P_previous_income OUT NOCOPY NUMBER
683 ,p_lower_income_Percentage OUT NOCOPY NUMBER ) RETURN NUMBER IS
684 --
685 CURSOR get_details(p_assignment_id NUMBER , p_effective_date DATE , p_input_value VARCHAR2 ) IS
686 SELECT TO_NUMBER(TO_CHAR(ee.effective_start_date, 'J')) julian_effective_date
687 ,eev1.screen_entry_value screen_entry_value
688 FROM per_all_assignments_f asg1
689 ,per_all_assignments_f asg2
690 ,per_all_people_f per
691 ,pay_element_links_f el
692 ,pay_element_types_f et
693 ,pay_input_values_f iv1
694 ,pay_element_entries_f ee
695 ,pay_element_entry_values_f eev1
696 WHERE asg1.assignment_id = p_assignment_id
697 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
698 AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date
699 AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
700 AND per.person_id = asg1.person_id
701 AND asg2.person_id = per.person_id
702 AND asg2.primary_flag = 'Y'
703 AND et.element_name = 'Tax Card'
704 AND et.legislation_code = 'FI'
705 AND iv1.element_type_id = et.element_type_id
706 AND iv1.name = p_input_value
707 AND el.business_group_id = per.business_group_id
708 AND el.element_type_id = et.element_type_id
709 AND ee.assignment_id = asg2.assignment_id
710 AND ee.element_link_id = el.element_link_id
711 AND eev1.element_entry_id = ee.element_entry_id
712 AND eev1.input_value_id = iv1.input_value_id
713 AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
714 AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
715 --
716 l_rec get_details%ROWTYPE;
717 --
718 BEGIN
719 --
720
721
722 OPEN get_details(p_assignment_id , p_effective_date ,'Base Rate' );
723 FETCH get_details INTO l_rec;
724 CLOSE get_details;
725
726 p_base_rate := l_rec.screen_entry_value ;
727
728 /* Added for lower income limit */
729
730 OPEN get_details(p_assignment_id , p_effective_date ,'Lower Income Percentage' );
731 FETCH get_details INTO l_rec;
732 CLOSE get_details;
733
734 p_lower_income_Percentage := l_rec.screen_entry_value ;
735
736 OPEN get_details(p_assignment_id , p_effective_date ,'Additional Rate' );
737 FETCH get_details INTO l_rec;
738 CLOSE get_details;
739
740 p_additional_rate := l_rec.screen_entry_value ;
741
742 OPEN get_details(p_assignment_id , p_effective_date ,'Yearly Income Limit' );
743 FETCH get_details INTO l_rec;
744 CLOSE get_details;
745
746 p_yearly_income_limit := l_rec.screen_entry_value ;
747
748 OPEN get_details(p_assignment_id , p_effective_date ,'Previous Income');
749 FETCH get_details INTO l_rec;
750 CLOSE get_details;
751
752 p_previous_income := l_rec.screen_entry_value ;
753
754 OPEN get_details(p_assignment_id , p_effective_date ,'Tax Card Type' );
755 FETCH get_details INTO l_rec;
756 CLOSE get_details;
757
758 p_julian_effective_date := l_rec.julian_effective_date;
759 p_tax_card_type := l_rec.screen_entry_value ;
760
761 --
762 RETURN 1;
763 --
764 END get_tax_card_details;
765 --
766 FUNCTION get_tax_days_override
767 (p_assignment_id NUMBER
768 , p_effective_date DATE
769 ,p_tax_days OUT NOCOPY NUMBER
770 ,p_ref_tax_days OUT NOCOPY NUMBER
771
772 ) RETURN NUMBER IS
773 --
777 ,per_all_assignments_f asg2
774 CURSOR get_details(p_assignment_id NUMBER,p_effective_date DATE , p_input_value VARCHAR2 ) IS
775 SELECT eev1.screen_entry_value tax_days
776 FROM per_all_assignments_f asg1
778 ,per_all_people_f per
779 ,pay_element_links_f el
780 ,pay_element_types_f et
781 ,pay_input_values_f iv1
782 ,pay_element_entries_f ee
783 ,pay_element_entry_values_f eev1
784 WHERE asg1.assignment_id = p_assignment_id
785 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
786 AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date
787 AND per.person_id = asg1.person_id
788 AND asg2.person_id = per.person_id
789 AND asg2.primary_flag = 'Y'
790 AND et.element_name = 'Tax Days Override'
791 AND et.legislation_code = 'FI'
792 AND iv1.element_type_id = et.element_type_id
793 AND iv1.name = p_input_value
794 AND el.business_group_id = per.business_group_id
795 AND el.element_type_id = et.element_type_id
796 AND ee.assignment_id = asg2.assignment_id
797 AND ee.element_link_id = el.element_link_id
798 AND eev1.element_entry_id = ee.element_entry_id
799 AND eev1.input_value_id = iv1.input_value_id
800 AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
801 AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
802 --
803 --
804 BEGIN
805 --
806 OPEN get_details(p_assignment_id ,p_effective_date,'Tax Days');
807 FETCH get_details INTO p_tax_days ;
808 CLOSE get_details;
809
810 OPEN get_details(p_assignment_id ,p_effective_date,'Reference Tax Days');
811 FETCH get_details INTO p_ref_tax_days ;
812 CLOSE get_details;
813 IF p_tax_days IS NULL THEN
814 p_tax_days:= -1;
815 END IF;
816
817 IF p_ref_tax_days IS NULL THEN
818 p_ref_tax_days:= -1;
819 END IF;
820
821 --
822 RETURN 1 ;
823
824 EXCEPTION
825 WHEN OTHERS THEN
826 RETURN 0 ;
827 --
828 END get_tax_days_override;
829 --
830
831 function run_tax_days_formula
832 (p_assignment_id IN NUMBER
833 ,p_date_earned IN DATE
834 ,p_business_group_id IN NUMBER
835 ,p_payroll_id IN NUMBER
836 ,p_payroll_action_id IN NUMBER
837 ,p_assignment_action_id IN NUMBER
838 ,p_tax_unit_id IN NUMBER
839 ,p_element_entry_id IN NUMBER
840 ,p_element_type_id IN NUMBER
841 ,p_original_entry_id IN NUMBER
842 )
843 return NUMBER is
844 cursor csr_get_formula_id is
845 SELECT PRL_INFORMATION1
846 FROM PAY_PAYROLLS PP
847 WHERE pp.business_group_id +0 = p_business_group_id
848 and pp.payroll_id = p_payroll_id
849 and trunc(p_date_earned) between pp.effective_start_date and nvl(pp.effective_end_date,p_date_earned)
850 and PRL_INFORMATION_CATEGORY = 'FI';
851 l_formula_id NUMBER;
852 l_inputs ff_exec.inputs_t;
853 l_outputs ff_exec.outputs_t;
854 l_value NUMBER;
855 begin
856 open csr_get_formula_id;
857 fetch csr_get_formula_id into l_formula_id;
858 close csr_get_formula_id;
859 l_inputs(1).name := 'ASSIGNMENT_ID';
860 l_inputs(1).value := p_assignment_id;
861 l_inputs(2).name := 'DATE_EARNED';
862 l_inputs(2).value := fnd_date.date_to_canonical(p_date_earned);
863 l_inputs(3).name := 'BUSINESS_GROUP_ID';
864 l_inputs(3).value := p_business_group_id;
865 l_inputs(4).name := 'PAYROLL_ID';
866 l_inputs(4).value := p_payroll_id;
867 l_inputs(5).name := 'PAYROLL_ACTION_ID';
868 l_inputs(5).value := p_payroll_action_id;
869 l_inputs(6).name := 'ASSIGNMENT_ACTION_ID';
870 l_inputs(6).value := p_assignment_action_id;
871 l_inputs(7).name := 'TAX_UNIT_ID';
872 l_inputs(7).value := p_tax_unit_id;
873 l_inputs(8).name := 'ELEMENT_ENTRY_ID';
874 l_inputs(8).value := p_element_entry_id;
875 l_inputs(9).name := 'ELEMENT_TYPE_ID';
876 l_inputs(9).value := p_element_type_id;
877 l_inputs(10).name := 'ORIGINAL_ENTRY_ID';
878 l_inputs(10).value := p_original_entry_id;
879 l_outputs(1).name := 'TAX_DAYS';
880 if l_formula_id is not null then
881 run_formula (l_formula_id
882 ,p_date_earned
883 ,l_inputs
884 ,l_outputs);
885 l_value := NVL(l_outputs(l_outputs.first).value,0);
886 else
887 l_value := 0;
888 end if;
889 return(l_value);
890 end run_tax_days_formula;
891 --
892
893 FUNCTION get_tax_details
894 (p_assignment_id IN NUMBER
895 , p_effective_date IN DATE
896
897 ) RETURN NUMBER IS
898 --
899 l_julian_effective_date NUMBER;
900
901 CURSOR get_tax_details(p_assignment_id NUMBER , p_effective_date DATE) IS
902 SELECT TO_NUMBER(TO_CHAR(ee.effective_start_date, 'J')) julian_effective_date
903 FROM per_all_assignments_f asg1
904 ,per_all_people_f per
905 ,pay_element_links_f el
909 AND per.person_id = asg1.person_id
906 ,pay_element_types_f et
907 ,pay_element_entries_f ee
908 WHERE asg1.assignment_id = p_assignment_id
910 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
911 AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date
912 AND et.element_name = 'Tax'
913 AND et.legislation_code = 'FI'
914 AND el.business_group_id = per.business_group_id
915 AND el.element_type_id = et.element_type_id
916 AND ee.assignment_id = asg1.assignment_id
917 AND ee.element_link_id = el.element_link_id
918 AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date;
919 --
920 l_rec get_tax_details%ROWTYPE;
921 --
922 BEGIN
923 --
924 OPEN get_tax_details(p_assignment_id ,p_effective_date );
925 FETCH get_tax_details INTO l_rec;
926 CLOSE get_tax_details;
927 --
928 l_julian_effective_date := l_rec.julian_effective_date;
929 --
930 RETURN l_julian_effective_date;
931 EXCEPTION
932 WHEN OTHERS THEN
933 RETURN NULL;
934 --
935 END get_tax_details;
936 --
937
938 FUNCTION get_tax_calendar_days
939 ( p_business_group_id IN NUMBER
940 , p_tax_unit_id IN NUMBER
941 ) RETURN NUMBER
942 IS
943
944
945 l_tax_calendar_days hr_organization_information.org_information10%TYPE;
946
947 CURSOR c_tax_calendar_days( p_business_group_id NUMBER , p_tax_unit_id NUMBER) IS
948 SELECT NVL(hoi2.org_information10,'364') org_information10
949 FROM hr_organization_units o1
950 , hr_organization_information hoi1
951 , hr_organization_information hoi2
952 WHERE o1.business_group_id =p_business_group_id
953 AND hoi1.organization_id = o1.organization_id
954 AND hoi1.organization_id = p_tax_unit_id
955 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
956 AND hoi1.org_information_context = 'CLASS'
957 AND o1.organization_id =hoi2.organization_id
958 AND hoi2.ORG_INFORMATION_CONTEXT='FI_LEGAL_EMPLOYER_DETAILS';
959
960 BEGIN
961
962 OPEN c_tax_calendar_days( p_business_group_id , p_tax_unit_id ) ;
963 FETCH c_tax_calendar_days INTO l_tax_calendar_days ;
964 CLOSE c_tax_calendar_days;
965
966 IF l_tax_calendar_days IS NULL THEN
967 l_tax_calendar_days :='364';
968 END IF;
969
970 RETURN l_tax_calendar_days ;
971
972 EXCEPTION
973 WHEN OTHERS THEN
974 RETURN NULL;
975 END ;
976
977
978 FUNCTION get_social_security_info
979 ( p_business_group_id IN NUMBER
980 ,p_tax_unit_id IN NUMBER
981 ,p_social_security_category OUT NOCOPY NUMBER
982 ,p_social_security_exempt OUT NOCOPY VARCHAR2
983 ) RETURN NUMBER
984 IS
985
986 CURSOR c_social_security_info( p_business_group_id NUMBER , p_tax_unit_id NUMBER) IS
987 SELECT NVL(hoi2.org_information3,0 ) org_information3 ,
988 NVL(hoi2.org_information12,'N') org_information11
989 FROM hr_organization_units o1
990 , hr_organization_information hoi1
991 , hr_organization_information hoi2
992 WHERE o1.business_group_id =p_business_group_id
993 AND hoi1.organization_id = o1.organization_id
994 AND hoi1.organization_id = p_tax_unit_id
995 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
996 AND hoi1.org_information_context = 'CLASS'
997 AND o1.organization_id =hoi2.organization_id
998 AND hoi2.ORG_INFORMATION_CONTEXT='FI_LEGAL_EMPLOYER_DETAILS';
999
1000 BEGIN
1001
1002 OPEN c_social_security_info( p_business_group_id , p_tax_unit_id ) ;
1003 FETCH c_social_security_info INTO p_social_security_category , p_social_security_exempt ;
1004 CLOSE c_social_security_info;
1005
1006 IF p_social_security_category IS NULL THEN
1007 p_social_security_category := 0 ;
1008 END IF;
1009
1010 IF p_social_security_exempt IS NULL THEN
1011 p_social_security_exempt :='N';
1012 END IF;
1013
1014
1015 RETURN 1 ;
1016
1017 EXCEPTION
1018 WHEN OTHERS THEN
1019 RETURN 0 ;
1020 END ;
1021
1022
1023 FUNCTION get_accident_insurance_info
1024 ( p_business_group_id IN NUMBER
1025 ,p_tax_unit_id IN NUMBER
1026 , p_effective_date DATE
1027 ) RETURN NUMBER
1028 IS
1029
1030 l_accident_insurance_id hr_organization_information.org_information3%TYPE;
1031
1032 CURSOR c_accident_insurance_info( p_business_group_id NUMBER , p_tax_unit_id NUMBER , p_effective_date DATE ) IS
1033 SELECT hoi2.org_information3
1034 FROM hr_organization_units o1
1035 , hr_organization_information hoi1
1036 , hr_organization_information hoi2
1037 WHERE o1.business_group_id =p_business_group_id
1038 AND hoi1.organization_id = o1.organization_id
1039 AND hoi1.organization_id = p_tax_unit_id
1040 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
1041 AND hoi1.org_information_context = 'CLASS'
1042 AND o1.organization_id =hoi2.organization_id
1043 AND hoi2.ORG_INFORMATION_CONTEXT='FI_ACCIDENT_PROVIDERS'
1044 AND p_effective_date between fnd_date.canonical_to_date(hoi2.org_information1) AND
1045 nvl(fnd_date.canonical_to_date(hoi2.org_information2),to_date('31/12/4712','DD/MM/YYYY')) ;
1046
1047
1048 BEGIN
1049
1053
1050 OPEN c_accident_insurance_info( p_business_group_id , p_tax_unit_id , p_effective_date ) ;
1051 FETCH c_accident_insurance_info INTO l_accident_insurance_id ;
1052 CLOSE c_accident_insurance_info;
1054 IF l_accident_insurance_id IS NULL THEN
1055 l_accident_insurance_id := -999 ;
1056
1057 END IF;
1058
1059 RETURN l_accident_insurance_id ;
1060
1061 EXCEPTION
1062 WHEN OTHERS THEN
1063 RETURN -999 ;
1064 END ;
1065
1066
1067 FUNCTION get_accident_insurance_rate
1068 ( p_business_group_id IN NUMBER
1069 ,p_tax_unit_id IN NUMBER
1070 ,p_effective_date IN DATE
1071 ,p_assignment_id IN NUMBER
1072 ,p_rate_type IN VARCHAR2
1073 ,p_accident_insurance_id OUT NOCOPY NUMBER
1074 ,p_rate OUT NOCOPY NUMBER
1075 ) RETURN NUMBER
1076 IS
1077
1078 l_accident_insurance_id hr_organization_information.org_information3%TYPE;
1079 l_lc_accident_insurance_pct hr_organization_information.org_information4%TYPE;
1080 l_accident_insurance_pct hr_organization_information.org_information3%TYPE;
1081 l_group_insurance_pct hr_organization_information.org_information3%TYPE;
1082 l_local_unit hr_soft_coding_keyflex.segment2%TYPE;
1083
1084 CURSOR c_accident_insurance_info( p_business_group_id NUMBER , p_tax_unit_id NUMBER , p_effective_date DATE ) IS
1085 SELECT hoi2.org_information3 org_information3 ,
1086 NVL(hoi2.org_information5,0 ) org_information5 ,
1087 NVL(hoi2.org_information6,0 ) org_information6
1088 FROM hr_organization_units o1
1089 , hr_organization_information hoi1
1090 , hr_organization_information hoi2
1091 WHERE o1.business_group_id =p_business_group_id
1092 AND hoi1.organization_id = o1.organization_id
1093 AND hoi1.organization_id = p_tax_unit_id
1094 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
1095 AND hoi1.org_information_context = 'CLASS'
1096 AND o1.organization_id =hoi2.organization_id
1097 AND hoi2.ORG_INFORMATION_CONTEXT='FI_ACCIDENT_PROVIDERS'
1098 AND p_effective_date between fnd_date.canonical_to_date(hoi2.org_information1) AND
1099 nvl(fnd_date.canonical_to_date(hoi2.org_information2),to_date('31/12/4712','DD/MM/YYYY')) ;
1100
1101
1102 CURSOR c_lc_accident_insurance_info( p_business_group_id NUMBER , p_local_unit_id NUMBER , p_effective_date DATE ) IS
1103 SELECT hoi2.org_information4 org_information4
1104 FROM hr_organization_units o1
1105 , hr_organization_information hoi1
1106 , hr_organization_information hoi2
1107 WHERE o1.business_group_id =p_business_group_id
1108 AND hoi1.organization_id = o1.organization_id
1109 AND hoi1.organization_id = p_local_unit_id
1110 AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
1111 AND hoi1.org_information_context = 'CLASS'
1112 AND o1.organization_id =hoi2.organization_id
1113 AND hoi2.ORG_INFORMATION_CONTEXT='FI_LU_ACCIDENT_PROVIDERS'
1114 AND p_effective_date between fnd_date.canonical_to_date(hoi2.org_information1) AND
1115 nvl(fnd_date.canonical_to_date(hoi2.org_information2),to_date('31/12/4712','DD/MM/YYYY')) ;
1116
1117 BEGIN
1118 l_local_unit := get_local_unit( p_assignment_id , p_effective_date) ;
1119
1120 OPEN c_lc_accident_insurance_info( p_business_group_id , l_local_unit , p_effective_date );
1121 FETCH c_lc_accident_insurance_info INTO l_lc_accident_insurance_pct ;
1122 CLOSE c_lc_accident_insurance_info;
1123
1124
1125 OPEN c_accident_insurance_info( p_business_group_id , p_tax_unit_id , p_effective_date );
1126 FETCH c_accident_insurance_info INTO p_accident_insurance_id , l_accident_insurance_pct ,
1127 l_group_insurance_pct ;
1128 CLOSE c_accident_insurance_info;
1129
1130
1131 IF p_accident_insurance_id IS NULL THEN
1132 p_accident_insurance_id := -999 ;
1133
1134 END IF;
1135
1136 IF l_lc_accident_insurance_pct IS NULL THEN
1137 IF l_accident_insurance_pct IS NULL THEN
1138 l_accident_insurance_pct := 0 ;
1139
1140 END IF;
1141 ELSE
1142 l_accident_insurance_pct := l_lc_accident_insurance_pct ;
1143
1144 END IF;
1145 IF l_group_insurance_pct IS NULL THEN
1146 l_group_insurance_pct := 0 ;
1147
1148 END IF;
1149
1150 IF p_rate_type ='AI' THEN
1151 p_rate := l_accident_insurance_pct;
1152 ELSE
1153 p_rate := l_group_insurance_pct ;
1154 END IF;
1155
1156
1157 RETURN 1 ;
1158
1159 EXCEPTION
1160 WHEN OTHERS THEN
1161 RETURN 0 ;
1162 END ;
1163
1164 FUNCTION get_person_pension_info
1165 ( p_business_group_id IN NUMBER
1166 ,p_tax_unit_id IN NUMBER
1167 ,p_assignment_id IN NUMBER
1168 ,p_effective_date IN DATE
1169 ,p_pension_type OUT NOCOPY VARCHAR2
1170 ,p_pension_group OUT NOCOPY NUMBER
1171 ,p_pension_provider OUT NOCOPY NUMBER
1172 ,p_pension_rate OUT NOCOPY NUMBER
1173 ) RETURN NUMBER
1174 IS
1175
1176 l_pension_group_id hr_organization_information.org_information_id%TYPE;
1177 l_pension_num hr_organization_information.org_information1%TYPE;
1178 l_local_unit hr_soft_coding_keyflex.segment2%TYPE;
1179 l_pension_rate hr_organization_information.org_information1%TYPE;
1180
1181 CURSOR c_person_pension_num(p_assignment_id NUMBER , p_effective_date DATE) IS
1182 SELECT PER_INFORMATION15, PER_INFORMATION16, PER_INFORMATION24
1183 FROM per_all_assignments_f asg1
1184 ,per_all_people_f per
1188 AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date
1185 WHERE asg1.assignment_id = p_assignment_id
1186 AND per.person_id = asg1.person_id
1187 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
1189 AND p_effective_date BETWEEN nvl(fnd_date.canonical_to_date(per.per_information14),to_date('01/01/0001','DD/MM/YYYY'))
1190 AND nvl(fnd_date.canonical_to_date(per.per_information20),to_date('31/12/4712','DD/MM/YYYY'));
1191
1192 CURSOR c_pension_provider_info( p_business_group_id NUMBER , p_tax_unit_id NUMBER , p_pension_num VARCHAR2 , p_effective_date DATE) IS
1193 SELECT NVL(hoi2.org_information4,0 ) org_information4 , NVL(hoi2.org_information7,0 ) org_information7
1194 FROM hr_organization_units o1
1195 , hr_organization_information hoi1
1196 , hr_organization_information hoi2
1197 WHERE o1.business_group_id =p_business_group_id
1198 AND hoi1.organization_id = o1.organization_id
1199 AND hoi1.organization_id = p_tax_unit_id
1200 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
1201 AND hoi1.org_information_context = 'CLASS'
1202 AND o1.organization_id =hoi2.organization_id
1203 AND hoi2.ORG_INFORMATION_CONTEXT='FI_PENSION_PROVIDERS'
1204 AND hoi2.org_information6 = p_pension_num
1205 AND p_effective_date between fnd_date.canonical_to_date(hoi2.org_information1) AND
1206 nvl(fnd_date.canonical_to_date(hoi2.org_information2),to_date('31/12/4712','DD/MM/YYYY'))
1207 AND hoi2.org_information6 IN
1208 (
1209 SELECT NVL(hoi2.org_information1,0 )
1210 FROM hr_organization_units o1
1211 , hr_organization_information hoi1
1212 , hr_organization_information hoi2
1213 WHERE o1.business_group_id = p_business_group_id
1214 AND hoi1.organization_id = o1.organization_id
1215 AND hoi1.organization_id = l_local_unit
1216 AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
1217 AND hoi1.org_information_context = 'CLASS'
1218 AND o1.organization_id =hoi2.organization_id
1219 AND hoi2.org_information1 = p_pension_num
1220 AND hoi2.ORG_INFORMATION_CONTEXT='FI_LU_PENSION_PROVIDERS' );
1221
1222 BEGIN
1223
1224 OPEN c_person_pension_num( p_assignment_id , p_effective_date ) ;
1225 FETCH c_person_pension_num INTO p_pension_type, p_pension_group, l_pension_num ;
1226 CLOSE c_person_pension_num;
1227
1228 l_local_unit := get_local_unit( p_assignment_id , p_effective_date) ;
1229
1230 IF l_pension_num IS NOT NULL THEN
1231 OPEN c_pension_provider_info( p_business_group_id , p_tax_unit_id , l_pension_num , p_effective_date ) ;
1232 FETCH c_pension_provider_info INTO p_pension_provider , l_pension_rate ;
1233 CLOSE c_pension_provider_info;
1234
1235 p_pension_rate:= fnd_number.canonical_to_number(l_pension_rate);
1236
1237 END IF;
1238
1239 IF p_pension_provider IS NULL THEN
1240 p_pension_provider := -999 ;
1241
1242 END IF;
1243
1244 IF p_pension_type IS NULL THEN
1245 p_pension_type := ' ';
1246 END IF;
1247
1248 IF p_pension_group IS NULL THEN
1249 p_pension_group := -99;
1250 END IF;
1251
1252
1253 IF p_pension_rate IS NULL THEN
1254 p_pension_rate := 0 ;
1255
1256 END IF;
1257
1258
1259 RETURN 1 ;
1260
1261 EXCEPTION
1262 WHEN OTHERS THEN
1263 fnd_file.put_line(fnd_file.log,'Error message : '||SQLERRM);
1264 RETURN 0 ;
1265 END ;
1266
1267 FUNCTION get_retirement_date
1268 (p_assignment_id NUMBER
1269 , p_effective_date DATE )
1270 RETURN DATE AS
1271 l_return_value DATE ;
1272 l_retire_date VARCHAR2(150) ;
1273
1274 CURSOR c_retire_date IS
1275 SELECT PER_INFORMATION8
1276 FROM per_all_assignments_f asg1
1277 ,per_all_people_f per
1278 WHERE asg1.assignment_id = p_assignment_id
1279 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
1280 AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date
1281 AND per.person_id = asg1.person_id;
1282
1283 BEGIN
1284 OPEN c_retire_date ;
1285 FETCH c_retire_date INTO l_retire_date;
1286 CLOSE c_retire_date;
1287 IF l_retire_date IS NULL THEN
1288 l_return_value :=fnd_date.canonical_to_date('4712/12/31 00:00:00');
1289 ELSE
1290 l_return_value :=fnd_date.canonical_to_date(l_retire_date);
1291 END IF;
1292 RETURN l_return_value;
1293 EXCEPTION
1294 WHEN OTHERS THEN
1295 l_return_value :=fnd_date.canonical_to_date('4712/12/31 00:00:00');
1296 RETURN l_return_value;
1297
1298 END ;
1299
1300
1301 FUNCTION xml_parser
1302 ( P_DATA VARCHAR2)
1303 RETURN VARCHAR2 AS
1304 l_data VARCHAR2(4000);
1305 BEGIN
1306 l_data := REPLACE (p_data, '&', '&');
1307 l_data := REPLACE (l_data, '>', '>');
1308 l_data := REPLACE (l_data, '<', '<');
1309 l_data := REPLACE (l_data, '''', ''');
1310 l_data := REPLACE (l_data, '"', '"');
1311 return l_data;
1312 EXCEPTION
1313 WHEN OTHERS THEN
1314 l_data :=NULL ;
1315 RETURN l_data;
1316 END xml_parser;
1317
1318
1319
1320 PROCEDURE INS_OR_UPD_PERSON_EIT_COLUMN
1321 ( p_person_id IN NUMBER
1322 ,p_new_value in VARCHAR2
1323 ,p_Session_Date in VARCHAR2
1324 ,p_COLUMN_NAME in per_people_extra_info.PEI_INFORMATION3%TYPE
1328 CURSOR CSR_PERSON_EIT
1325 ,p_dt_update_mode in varchar2
1326 )
1327 IS
1329 IS
1330 select PERSON_EXTRA_INFO_ID,
1331 object_version_number,
1332 person_id,
1333 information_type,
1334 pei_information_category,
1335 pei_information1,
1336 pei_information2,
1337 pei_information3,
1338 pei_information4,
1339 pei_information5,
1340 pei_information6,
1341 pei_information7
1342 from per_people_extra_info
1343 where information_type='FI_PENSION'
1344 AND PEI_INFORMATION_CATEGORY='FI_PENSION'
1345 AND PEI_INFORMATION3=p_COLUMN_NAME
1346 AND PERSON_ID = P_PERSON_ID;
1347
1348 LR_PERSON_EIT CSR_PERSON_EIT%ROWTYPE;
1349 L_OVN per_people_extra_info.object_version_number%TYPE;
1350 L_person_extra_info_id number;
1351 l_Action VARCHAR2(3);
1352
1353 BEGIN
1354
1355 --hr_utility.trace('In Column Update or insert ');
1356 --hr_utility.trace(' p_Session_Date ==>' || p_Session_Date);
1357 --hr_utility.trace('p_new_value ==> ' ||p_new_value );
1358
1359 OPEN csr_PERSON_EIT;
1360 FETCH csr_PERSON_EIT
1361 INTO lr_PERSON_EIT;
1362 IF csr_PERSON_EIT%NOTFOUND
1363 THEN
1364 --hr_utility.trace('In Not Found So Creation is gonna happen');
1365
1366 hr_person_extra_info_api.create_person_extra_info
1367 (p_person_id => p_PERSON_ID
1368 ,p_information_type =>'FI_PENSION'
1369 ,p_pei_information_category =>'FI_PENSION'
1370 ,p_pei_information1 =>'Y'
1371 ,p_pei_information3 =>p_COLUMN_NAME
1372 ,p_pei_information4 => p_new_value
1373 ,p_pei_information5 =>'N'
1374 ,p_pei_information6 =>'I'
1375 ,p_pei_information7 =>FND_DATE.DATE_TO_CANONICAL(p_Session_Date)
1376 ,p_person_extra_info_id =>L_person_extra_info_id
1377 ,p_object_version_number =>L_OVN
1378 ) ;
1379 hr_utility.trace('p_person_extra_info_id Created ==> ' ||L_person_extra_info_id );
1380
1381
1382 ELSE
1383
1384 hr_utility.trace('Found record so Updation gonna Happen '||p_new_value);
1385
1386 L_OVN := lr_PERSON_EIT.object_version_number;
1387 IF p_dt_update_mode ='UPDATE' or p_dt_update_mode='UPDATE_CHANGE'
1388 THEN
1389 l_Action :='U';
1390 ELSE
1391 l_Action :='I';
1392 END IF;
1393
1394 IF p_dt_update_mode ='INSERT_CHANGE' or p_dt_update_mode='UPDATE_CHANGE'
1395 THEN
1396 -- as the changes update mode
1397 -- is called, we need to pass the new value,
1398 -- and the action as insert, and reported as No
1399 -- along with the session date
1400 hr_person_extra_info_api.update_person_extra_info
1401 (
1402 p_person_extra_info_id => lr_PERSON_EIT.person_extra_info_id,
1403 p_object_version_number => L_OVN,
1404 -- p_pei_information_category => lr_PERSON_EIT.pei_information_category,
1405 -- p_pei_information1 => lr_PERSON_EIT.pei_information1,
1406 p_pei_information2 => null,
1407 -- p_pei_information3 => lr_PERSON_EIT.pei_information3,
1408 p_pei_information4 => p_new_value,
1409 p_pei_information5 => 'N',
1410 p_pei_information6 => l_Action,
1411 p_pei_information7 =>FND_DATE.DATE_TO_CANONICAL(p_Session_Date)
1412 );
1413
1414 ELSE
1415
1416 IF lr_PERSON_EIT.pei_information5 ='Y'
1417 THEN
1418 hr_person_extra_info_api.update_person_extra_info
1419 (
1420 p_person_extra_info_id => lr_PERSON_EIT.person_extra_info_id,
1421 p_object_version_number => L_OVN,
1422 -- p_pei_information_category => lr_PERSON_EIT.pei_information_category,
1423 -- p_pei_information1 => lr_PERSON_EIT.pei_information1,
1424 p_pei_information2 => null,
1425 -- p_pei_information3 => lr_PERSON_EIT.pei_information3,
1426 -- p_pei_information4 => p_new_value,
1427 p_pei_information5 => 'N',
1428 p_pei_information6 => l_Action,
1429 p_pei_information7 =>FND_DATE.DATE_TO_CANONICAL(p_Session_Date)
1430 );
1431 ELSE
1432 hr_person_extra_info_api.update_person_extra_info
1433 (
1434 p_person_extra_info_id => lr_PERSON_EIT.person_extra_info_id,
1438 --p_pei_information2 => lr_PERSON_EIT.pei_information2,
1435 p_object_version_number => L_OVN,
1436 --p_pei_information_category => lr_PERSON_EIT.pei_information_category,
1437 --p_pei_information1 => lr_PERSON_EIT.pei_information1,
1439 --p_pei_information3 => lr_PERSON_EIT.pei_information3,
1440 --p_pei_information4 => p_new_value,
1441 p_pei_information5 => 'N',
1442 p_pei_information6 => l_Action
1443 );
1444 END IF;
1445 --hr_utility.trace('Updated Record ==> ' ||lr_PERSON_EIT.person_extra_info_id);
1446 END IF; -- END if of INSERT_CHANGE
1447 END IF;
1448 CLOSE csr_PERSON_EIT ;
1449 END INS_OR_UPD_PERSON_EIT_COLUMN;
1450
1451 PROCEDURE INSERT_OR_UPDATE_PERSON_EIT
1452 (p_person_id IN NUMBER,
1453 p_new_PENSION_JOINING_DATE IN VARCHAR2,
1454 p_old_PENSION_JOINING_DATE in VARCHAR2,
1455 p_new_PENSION_TYPES IN VARCHAR2,
1456 p_old_PENSION_TYPES in VARCHAR2,
1457 p_new_PENSION_INS_NUM IN VARCHAR2,
1458 p_old_PENSION_INS_NUM in VARCHAR2,
1459 p_new_PENSION_GROUP IN VARCHAR2,
1460 p_old_PENSION_GROUP in VARCHAR2,
1461 p_new_LOCAL_UNIT IN VARCHAR2,
1462 p_old_LOCAL_UNIT in VARCHAR2,
1463 p_Session_Date in VARCHAR2,
1464 p_dt_update_mode in varchar2,
1465 p_where IN VARCHAR2 default NULL
1466 )
1467 is
1468 BEGIN
1469
1470 -- if any of the 14,15,16 ,24 has been changed then call the API to insert or update acc
1471
1472 -- PER_INFORMATION14 Pension Joining Date PERSON.LOC_DATE04
1473 -- PER_INFORMATION15 Pension Types PERSON.LOC_ITEM18
1474 -- PER_INFORMATION16 Pension Group PERSON.LOC_INFORMATION_C01
1475 -- PER_INFORMATION24 Pension Insurance Number PERSON.LOC_INFORMATION_C06
1476 -- PER_INFORMATION17 Planned Retirement age PERSON.LOC_NUM03
1477
1478 --hr_utility.trace_on(NULL,'TELL');
1479 --hr_utility.trace(' In p_new_PENSION_JOINING_DATE => ' || p_new_PENSION_JOINING_DATE);
1480 --hr_utility.trace(' In p_old_PENSION_JOINING_DATE => ' || p_old_PENSION_JOINING_DATE);
1481 --hr_utility.trace(' In p_new_PENSION_TYPES => ' || p_new_PENSION_TYPES);
1482 --hr_utility.trace(' In p_old_PENSION_TYPES => ' || p_old_PENSION_TYPES);
1483 --hr_utility.trace(' In p_new_PENSION_INS_NUM => ' || p_new_PENSION_INS_NUM);
1484 --hr_utility.trace(' In p_old_PENSION_INS_NUM => ' || p_old_PENSION_INS_NUM);
1485 --hr_utility.trace(' In p_new_PENSION_GROUP => ' || p_new_PENSION_GROUP);
1486 --hr_utility.trace(' In p_old_PENSION_GROUP => ' || p_old_PENSION_GROUP);
1487 --hr_utility.trace(' In p_new_PENSION_RETIRE_DATE => ' || p_new_PENSION_RETIRE_DATE);
1488 --hr_utility.trace(' In p_old_PENSION_RETIRE_DATE => ' || p_old_PENSION_RETIRE_DATE);
1489 --hr_utility.trace(' In p_new_LOCAL_UNIT => ' || p_new_LOCAL_UNIT);
1490 --hr_utility.trace(' In p_old_LOCAL_UNIT => ' || p_old_LOCAL_UNIT);
1491
1492 IF p_where ='ASSIGN' or p_where ='MAINTAIN'
1493 THEN
1494
1495 IF (p_new_LOCAL_UNIT IS NOT null or p_old_LOCAL_UNIT IS NOT NULL )
1496 THEN
1497 IF ( trim(p_new_LOCAL_UNIT) = trim(p_old_LOCAL_UNIT) )
1498 THEN
1499 hr_utility.trace('In Equals for Local Unit');
1500 ELSE
1501 hr_utility.trace('In Not Equals for Local Unit');
1502 INS_OR_UPD_PERSON_EIT_COLUMN(p_person_id,p_new_LOCAL_UNIT,p_Session_Date,'Local Unit',p_dt_update_mode);
1503 END IF;
1504 END IF;
1505 END IF;
1506 IF p_where ='PERSON' or p_where ='MAINTAIN'
1507 THEN
1508 IF (p_new_PENSION_JOINING_DATE IS null AND p_old_PENSION_JOINING_DATE IS null )
1509 THEN
1510 INS_OR_UPD_PERSON_EIT_COLUMN(p_person_id,p_new_PENSION_JOINING_DATE,p_Session_Date,'Pension Joining Date','INSERT');
1511 ELSIF (p_new_PENSION_JOINING_DATE IS NOT null or p_old_PENSION_JOINING_DATE IS NOT null )
1512 THEN
1513 IF(FND_DATE.CANONICAL_to_date(p_new_PENSION_JOINING_DATE)=
1514 FND_DATE.CANONICAL_to_date(p_old_PENSION_JOINING_DATE))
1515 THEN
1516 hr_utility.trace('In Equals for Pension Date');
1517 ELSE
1518 hr_utility.trace('Calling the Column update ');
1519 INS_OR_UPD_PERSON_EIT_COLUMN(p_person_id,p_new_PENSION_JOINING_DATE,p_Session_Date,'Pension Joining Date',p_dt_update_mode);
1520 END IF;
1521 END IF;
1522 /*
1523 IF (p_new_PENSION_RETIRE_DATE IS NOT null OR p_old_PENSION_RETIRE_DATE IS NOT null )
1524 THEN
1525 IF ( to_number(p_new_PENSION_RETIRE_DATE) = to_number(p_old_PENSION_RETIRE_DATE) )
1526 THEN
1527 hr_utility.trace('In Equals for AGE');
1528 ELSE
1529 hr_utility.trace('In Not Equals for AGE');
1530 INS_OR_UPD_PERSON_EIT_COLUMN(p_person_id,p_new_PENSION_RETIRE_DATE,p_Session_Date,'Pension Retirement Age');
1531 END IF;
1532 END IF;
1533 */
1534
1538 ELSIF (p_new_PENSION_GROUP IS NOT null OR p_old_PENSION_GROUP IS NOT null )
1535 IF (p_new_PENSION_GROUP IS null AND p_old_PENSION_GROUP IS null )
1536 THEN
1537 INS_OR_UPD_PERSON_EIT_COLUMN(p_person_id,p_new_PENSION_GROUP,p_Session_Date,'Pension Group','INSERT');
1539 THEN
1540 hr_utility.trace('IF NOT NULL for Pension Group' || p_new_PENSION_GROUP ||' '||p_old_PENSION_GROUP);
1541 IF ( trim(p_new_PENSION_GROUP) = trim(p_old_PENSION_GROUP) )
1542 THEN
1543 hr_utility.trace('In Equals for Pension group');
1544 ELSE
1545 hr_utility.trace('value ' ||p_new_PENSION_GROUP );
1546 INS_OR_UPD_PERSON_EIT_COLUMN(p_person_id,p_new_PENSION_GROUP,p_Session_Date,'Pension Group',p_dt_update_mode);
1547 END IF;
1548 END IF;
1549
1550 IF (p_new_PENSION_INS_NUM IS null AND p_old_PENSION_INS_NUM IS null )
1551 THEN
1552 INS_OR_UPD_PERSON_EIT_COLUMN(p_person_id,p_new_PENSION_INS_NUM,p_Session_Date,'Insurance Number','INSERT');
1553 ELSIF (p_new_PENSION_INS_NUM IS NOT null OR p_old_PENSION_INS_NUM IS NOT null )
1554 THEN
1555 hr_utility.trace('IF NOT NULL for Pension Insurance Number' || p_new_PENSION_INS_NUM ||' '||p_old_PENSION_INS_NUM);
1556 IF ( trim(p_new_PENSION_INS_NUM) = trim(p_old_PENSION_INS_NUM) )
1557 THEN
1558 hr_utility.trace('In Equals for Pension Insurance Number');
1559 ELSE
1560 hr_utility.trace('value ' ||p_new_PENSION_INS_NUM );
1561 INS_OR_UPD_PERSON_EIT_COLUMN(p_person_id,p_new_PENSION_INS_NUM,p_Session_Date,'Insurance Number',p_dt_update_mode);
1562 END IF;
1563 END IF;
1564
1565 IF (p_new_PENSION_TYPES IS null AND p_old_PENSION_TYPES IS null )
1566 THEN
1567 INS_OR_UPD_PERSON_EIT_COLUMN(p_person_id,p_new_PENSION_TYPES,p_Session_Date,'Pension Types','INSERT');
1568 ELSIF (p_new_PENSION_TYPES IS NOT null OR p_old_PENSION_TYPES IS NOT null )
1569 THEN
1570 IF ( trim(p_new_PENSION_TYPES) = trim(p_old_PENSION_TYPES) )
1571 THEN
1572 hr_utility.trace('In Equals for Pension Types');
1573 ELSE
1574 hr_utility.trace('Value ' ||p_new_PENSION_TYPES );
1575 INS_OR_UPD_PERSON_EIT_COLUMN(p_person_id,p_new_PENSION_TYPES,p_Session_Date,'Pension Types',p_dt_update_mode);
1576 -- Pension type has been changed, so insert the joinig date and group.
1577 -- so that pension joindate record displayed in report
1578 INS_OR_UPD_PERSON_EIT_COLUMN(p_person_id,p_new_PENSION_JOINING_DATE,p_Session_Date,'Pension Joining Date','INSERT_CHANGE');
1579 IF ( trim(p_old_PENSION_TYPES) ='TYEL' )
1580 THEN
1581 -- if the Old was TYEL then change the joining date value and insert and Not reported
1582 -- then dont change the group value but, make insert and Not reported
1583 INS_OR_UPD_PERSON_EIT_COLUMN(p_person_id,p_new_PENSION_GROUP,p_Session_Date,'Pension Group','INSERT');
1584 -- then dont change the Type value but, make update and Not reported
1585
1586 END IF;
1587
1588
1589 END IF;
1590 END IF;
1591
1592
1593 END IF;
1594
1595 END INSERT_OR_UPDATE_PERSON_EIT;
1596 FUNCTION calc_sch_based_dur ( p_assignment_id IN NUMBER,
1597 p_days_or_hours IN VARCHAR2,
1598 -- p_include_event IN VARCHAR2 DEFAULT 'Y',
1599 p_date_start IN DATE,
1600 p_date_end IN DATE,
1601 p_time_start IN VARCHAR2,
1602 p_time_end IN VARCHAR2,
1603 p_duration IN OUT NOCOPY NUMBER
1604 ) RETURN NUMBER IS
1605 --
1606 l_return NUMBER;
1607 l_idx NUMBER;
1608 l_ref_date DATE;
1609 l_first_band BOOLEAN;
1610 l_day_start_time VARCHAR2(5);
1611 l_day_end_time VARCHAR2(5);
1612 l_start_time VARCHAR2(5);
1613 l_end_time VARCHAR2(5);
1614 --
1615 l_start_date DATE;
1616 l_end_date DATE;
1617 l_schedule cac_avlblty_time_varray;
1618 l_schedule_source VARCHAR2(10);
1619 l_return_status VARCHAR2(1);
1620 l_return_message VARCHAR2(2000);
1621 --
1622 l_time_start VARCHAR2(10); --5 to 10
1623 l_time_end VARCHAR2(10); --5 to 10
1624 --
1625 e_bad_time_format EXCEPTION;
1626 CURSOR get_time_format(l_time varchar2) is
1627 SELECT replace(trim(to_char(to_number(l_time),'00.00')),'.',':') FROM dual;
1628 --
1629 BEGIN
1630 hr_utility.set_location('Entering '||'.calc_sch_based_dur',10);
1631 l_return := 0;
1632 p_duration := 0;
1633 l_time_start := p_time_start;
1634 l_time_end := p_time_end;
1635 /*knelli */
1636 OPEN get_time_format(l_time_start);
1637 FETCH get_time_format INTO l_time_start;
1638 CLOSE get_time_format;
1639 OPEN get_time_format(l_time_end);
1640 FETCH get_time_format INTO l_time_end;
1641 CLOSE get_time_format;
1642 /* knelli */
1643 --
1644 IF l_time_start IS NULL THEN
1645 l_time_start := '00:00';
1649 END IF;
1646 ELSE
1647 IF NOT good_time_format(l_time_start) THEN
1648 RAISE e_bad_time_format;
1650 END IF;
1651 IF l_time_end IS NULL THEN
1652 l_time_end := '00:00';
1653 ELSE
1654 IF NOT good_time_format(l_time_end) THEN
1655 RAISE e_bad_time_format;
1656 END IF;
1657 END IF;
1658 l_start_date := TO_DATE(TO_CHAR(p_date_start,'DD-MM-YYYY')||' '||l_time_start,'DD-MM-YYYY HH24:MI');
1659 l_end_date := TO_DATE(TO_CHAR(p_date_end,'DD-MM-YYYY')||' '||l_time_end,'DD-MM-YYYY HH24:MI');
1660 IF p_days_or_hours = 'D' THEN
1661 l_end_date := l_end_date + 1;
1662 -- l_end_date := l_end_date; --knelli
1663 END IF;
1664 --
1665 -- Fetch the work schedule
1666 hr_utility.set_location('calling hr_wrk_sch_pkg.get_per_asg_schedule',10);
1667 --
1668 hr_wrk_sch_pkg.get_per_asg_schedule
1669 ( p_person_assignment_id => p_assignment_id
1670 , p_period_start_date => l_start_date
1671 , p_period_end_date => l_end_date
1672 , p_schedule_category => NULL --knelli change
1673 , p_include_exceptions =>'Y' --p_include_event
1674 , p_busy_tentative_as => 'FREE' --Knelli change
1675 , x_schedule_source => l_schedule_source
1676 , x_schedule => l_schedule
1677 , x_return_status => l_return_status
1678 , x_return_message => l_return_message
1679 );
1680 --
1681 --knelli
1682 hr_utility.set_location('l_return status :' || l_return_status,10);
1683 IF l_return_status = '0' THEN
1684 --
1685 -- Calculate duration
1686 --
1687 l_idx := l_schedule.first;
1688 hr_utility.set_location('l_idx - loop index :' || l_schedule.first,10);
1689 --
1690 IF p_days_or_hours = 'D' THEN
1691 --
1692 l_first_band := TRUE;
1693 l_ref_date := NULL;
1694 WHILE l_idx IS NOT NULL
1695 LOOP
1696 --knelli
1697 --hr_utility.set_location('free or busy '|| l_schedule(l_idx).FREE_BUSY_TYPE,20);
1698 --l_schedule(l_idx).FREE_BUSY_TYPE := 'FREE'; --SET BY KNELLI
1699 --knelli
1700 IF l_schedule(l_idx).FREE_BUSY_TYPE IS NOT NULL THEN
1701 IF l_schedule(l_idx).FREE_BUSY_TYPE = 'FREE' THEN --knelli changed FREE to BUSY
1702 IF l_first_band THEN
1703 l_first_band := FALSE;
1704 l_ref_date := TRUNC(l_schedule(l_idx).START_DATE_TIME);
1705 --knelli
1706 hr_utility.set_location('start date time '|| l_schedule(l_idx).START_DATE_TIME,20);
1707 hr_utility.set_location('end date time '|| l_schedule(l_idx).END_DATE_TIME,20);
1708 IF (TRUNC(l_schedule(l_idx).END_DATE_TIME) = TRUNC(l_schedule(l_idx).START_DATE_TIME)) THEN
1709 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) + 1);
1710 ELSE
1711 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME));
1712 END IF;
1713 ELSE -- not first time
1714 IF TRUNC(l_schedule(l_idx).START_DATE_TIME) = l_ref_date THEN
1715 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME));
1716 ELSE
1717 l_ref_date := TRUNC(l_schedule(l_idx).END_DATE_TIME);
1718 IF (TRUNC(l_schedule(l_idx).END_DATE_TIME) = TRUNC(l_schedule(l_idx).START_DATE_TIME)) THEN
1719 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) + 1);
1720 ELSE
1721 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME));
1722 END IF;
1723 END IF;
1724 END IF;
1725 END IF;
1726 END IF;
1727 l_idx := l_schedule(l_idx).NEXT_OBJECT_INDEX;
1728 END LOOP;
1729 --
1730 ELSE -- p_days_or_hours is 'H'
1731 --
1732 l_day_start_time := '00:00';
1733 l_day_end_time := '23:59';
1734 WHILE l_idx IS NOT NULL
1735 LOOP
1736 --knelli
1737 --hr_utility.set_location('free or busy '|| l_schedule(l_idx).FREE_BUSY_TYPE,20);
1738 --l_schedule(l_idx).FREE_BUSY_TYPE := 'FREE'; --SET BY KNELLI
1739 IF l_schedule(l_idx).FREE_BUSY_TYPE IS NOT NULL THEN
1740 IF l_schedule(l_idx).FREE_BUSY_TYPE = 'FREE' THEN ----knelli changed FREE to BUSY
1741 IF l_schedule(l_idx).END_DATE_TIME < l_schedule(l_idx).START_DATE_TIME THEN
1742 -- Skip this invalid slot which ends before it starts
1743 NULL;
1744 ELSE
1745 IF TRUNC(l_schedule(l_idx).END_DATE_TIME) > TRUNC(l_schedule(l_idx).START_DATE_TIME) THEN
1746 -- Start and End on different days
1747 --
1748 -- Get first day hours
1749 l_start_time := TO_CHAR(l_schedule(l_idx).START_DATE_TIME,'HH24:MI');
1750 SELECT p_duration + (((SUBSTR(l_day_end_time,1,2)*60 + SUBSTR(l_day_end_time,4,2)) -
1751 (SUBSTR(l_start_time,1,2)*60 + SUBSTR(l_start_time,4,2)))/60)
1752 INTO p_duration
1753 FROM DUAL;
1754 --
1755 -- Get last day hours
1756 l_end_time := TO_CHAR(l_schedule(l_idx).END_DATE_TIME,'HH24:MI');
1760 FROM DUAL;
1757 SELECT p_duration + (((SUBSTR(l_end_time,1,2)*60 + SUBSTR(l_end_time,4,2)) -
1758 (SUBSTR(l_day_start_time,1,2)*60 + SUBSTR(l_day_start_time,4,2)) + 1)/60)
1759 INTO p_duration
1761 --
1762 -- Get between full day hours
1763 SELECT p_duration + ((TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) - 1) * 24)
1764 INTO p_duration
1765 FROM DUAL;
1766 ELSE
1767 -- Start and End on same day
1768 l_start_time := TO_CHAR(l_schedule(l_idx).START_DATE_TIME,'HH24:MI');
1769 l_end_time := TO_CHAR(l_schedule(l_idx).END_DATE_TIME,'HH24:MI');
1770 SELECT p_duration + (((SUBSTR(l_end_time,1,2)*60 + SUBSTR(l_end_time,4,2)) -
1771 (SUBSTR(l_start_time,1,2)*60 + SUBSTR(l_start_time,4,2)))/60)
1772 INTO p_duration
1773 FROM DUAL;
1774 END IF;
1775 END IF;
1776 END IF;
1777 END IF;
1778 l_idx := l_schedule(l_idx).NEXT_OBJECT_INDEX;
1779 END LOOP;
1780 p_duration := ROUND(p_duration,2);
1781 --
1782 END IF;
1783 END IF;
1784 RETURN l_return;
1785 --
1786 hr_utility.set_location('Leaving '||g_package||'.calc_sch_based_dur',20);
1787 EXCEPTION
1788 --
1789 WHEN e_bad_time_format THEN
1790 hr_utility.set_location('Leaving '||g_package||'.calc_sch_based_dur',30);
1791 hr_utility.set_location(SQLERRM,35);
1792 RAISE;
1793 --
1794 WHEN OTHERS THEN
1795 hr_utility.set_location('Leaving '||g_package||'.calc_sch_based_dur',40);
1796 hr_utility.set_location(SQLERRM,45);
1797 RAISE;
1798 --
1799 RETURN l_return;
1800 END calc_sch_based_dur;
1801
1802
1803 FUNCTION GET_BALANCE_DATE(p_BALANCE_DATE IN DATE)RETURN DATE IS
1804 BEGIN
1805 RETURN P_BALANCE_DATE;
1806 END ;
1807
1808
1809 FUNCTION clear_cache RETURN NUMBER
1810 is
1811 begin
1812 g_fi_cache_table.delete;
1813 RETURN 1;
1814 end;
1815
1816
1817 FUNCTION set_value_cache(p_cache_code in varchar2, p_cache_value in varchar2)
1818 RETURN NUMBER
1819 is
1820 l_cache_index number;
1821 l_updated boolean;
1822 begin
1823 l_cache_index := g_fi_cache_table.FIRST;
1824 l_updated:= FALSE;
1825
1826 -- filter out the desired preference
1827
1828 WHILE l_cache_index IS NOT NULL
1829 LOOP
1830
1831 IF ( g_fi_cache_table(l_cache_index).cache_code = p_cache_code )
1832 THEN
1833
1834 g_fi_cache_table(l_cache_index).cache_value:= p_cache_value ;
1835 l_updated:= true;
1836 RETURN 1;
1837
1838 END IF;
1839
1840 l_cache_index := g_fi_cache_table.NEXT(l_cache_index);
1841
1842 END LOOP;
1843
1844 if (not l_updated) then
1845
1846 If (g_fi_cache_table.count > 0) then
1847 l_cache_index := g_fi_cache_table.last + 1;
1848 else
1849 l_cache_index := 1;
1850 End If;
1851
1852 g_fi_cache_table(l_cache_index).cache_code :=p_cache_code;
1853 g_fi_cache_table(l_cache_index).cache_value :=p_cache_value;
1854 RETURN 1;
1855 end if;
1856 RETURN 0;
1857 end ;
1858
1859 FUNCTION get_value_cache(p_cache_code in varchar2, p_cache_value out nocopy varchar2)
1860 RETURN NUMBER
1861 is
1862 l_cache_index number;
1863 begin
1864 l_cache_index := g_fi_cache_table.FIRST;
1865
1866 -- filter out the desired preference
1867
1868 WHILE l_cache_index IS NOT NULL
1869 LOOP
1870
1871 IF ( g_fi_cache_table(l_cache_index).cache_code = p_cache_code )
1872 THEN
1873
1874 p_cache_value := g_fi_cache_table(l_cache_index).cache_value;
1875 RETURN 1;
1876 END IF;
1877
1878 l_cache_index := g_fi_cache_table.NEXT(l_cache_index);
1879
1880 END LOOP;
1881 RETURN 0;
1882 end ;
1883
1884 FUNCTION delete_cache_table_row(p_cache_code in varchar2)RETURN NUMBER
1885 is
1886 l_cache_index number;
1887 begin
1888 l_cache_index := g_fi_cache_table.FIRST;
1889
1890
1891 -- filter out the desired preference
1892
1893 WHILE l_cache_index IS NOT NULL
1894 LOOP
1895
1896 IF ( g_fi_cache_table(l_cache_index).cache_code = p_cache_code )
1897 THEN
1898
1899 g_fi_cache_table.delete(l_cache_index);
1900
1901 RETURN 1;
1902 END IF;
1903
1904 l_cache_index := g_fi_cache_table.NEXT(l_cache_index);
1905
1906
1907 END LOOP;
1908 RETURN 0;
1909 end ;
1910
1911 FUNCTION PRINT1(P_LEVEL IN NUMBER,P_TEXT IN VARCHAR2,P_VALUE IN VARCHAR2) RETURN NUMBER
1912 IS
1913 BEGIN
1914 HR_UTILITY.TRACE_ON(NULL,'X');
1915 HR_UTILITY.TRACE(P_LEVEL ||' ' || P_TEXT || ' ' || P_VALUE );
1916 HR_UTILITY.TRACE_OFF;
1917 RETURN 1;
1918 END;
1919
1920 FUNCTION get_input_value_in_varchar
1921 (p_assignment_id in NUMBER
1922 ,p_effective_date in DATE
1923 ,p_element_name in varchar2
1924 ,p_input_value_name in varchar2
1925 ,p_input_value out nocopy varchar2
1926 ) RETURN NUMBER IS
1927 --
1928 CURSOR get_details(p_assignment_id NUMBER,p_effective_date DATE ,
1929 p_element_name varchar2,
1930 p_input_value_name varchar2)
1931 IS
1932 SELECT eev1.SCREEN_ENTRY_VALUE
1933 FROM per_all_assignments_f asg1
1934 ,pay_element_links_f el
1935 ,pay_element_types_f et
1936 ,pay_input_values_f iv1
1937 ,pay_element_entries_f ee
1938 ,pay_element_entry_values_f eev1
1939 WHERE asg1.assignment_id = p_assignment_id
1940 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
1941 AND et.element_name =p_element_name
1942 AND et.legislation_code = 'FI'
1943 AND iv1.element_type_id = et.element_type_id
1944 AND iv1.name = p_input_value_name
1945 AND el.business_group_id = asg1.business_group_id
1946 AND el.element_type_id = et.element_type_id
1947 AND ee.assignment_id = asg1.assignment_id
1948 AND ee.element_link_id = el.element_link_id
1949 AND eev1.element_entry_id = ee.element_entry_id
1950 AND eev1.input_value_id = iv1.input_value_id
1951 AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
1952 AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
1953
1954 --
1955 --
1956 BEGIN
1957 --
1958 OPEN get_details(p_assignment_id ,p_effective_date,p_element_name,p_input_value_name);
1959 FETCH get_details INTO p_input_value ;
1960 CLOSE get_details;
1961
1962 --
1963 RETURN 1 ;
1964
1965 EXCEPTION
1966 WHEN OTHERS THEN
1967 RETURN 0 ;
1968 --
1969 END get_input_value_in_varchar;
1970
1971 function get_hourly_salaried_type(p_assignment_id in number,
1972 p_date_earned in date
1973 ) return varchar2 is
1974 cursor csr_hourly_salaried_type(p_assignment_id in number,
1975 p_date_earned in date)
1976 is
1977 SELECT HOURLY_SALARIED_CODE FROM PER_ALL_ASSIGNMENTS_F WHERE ASSIGNMENT_ID=p_assignment_id and
1978 p_date_earned between effective_start_date and effective_end_Date;
1979
1980 p_hourly_salaried varchar2(1);
1981
1982 begin
1983 open csr_hourly_salaried_type(p_assignment_id,p_date_earned);
1984 fetch csr_hourly_salaried_type into p_hourly_salaried;
1985 close csr_hourly_salaried_type;
1986 return p_hourly_salaried;
1987 end;
1988
1989 FUNCTION get_payroll_period_info
1990 (p_payroll_id IN NUMBER
1991 ,p_payroll_start_date IN DATE
1992 ,p_payroll_end_date IN DATE
1993 ,p_S_hp_pcent OUT NOCOPY NUMBER
1994 ,p_W_hp_pcent OUT NOCOPY NUMBER
1995 ,p_S_hb_pcent OUT NOCOPY NUMBER
1996 ,p_W_hb_pcent OUT NOCOPY NUMBER
1997 ,p_hc_pcent OUT NOCOPY NUMBER
1998 ) RETURN NUMBER
1999 AS
2000 CURSOR c_period_info IS
2001 SELECT PRD_INFORMATION1
2002 ,PRD_INFORMATION2
2003 ,PRD_INFORMATION3
2004 ,PRD_INFORMATION4
2005 ,PRD_INFORMATION5
2006 FROM per_time_periods
2007 WHERE payroll_id = p_payroll_id
2008 AND start_date = p_payroll_start_date
2009 AND end_date = p_payroll_end_date;
2010
2011 l_return NUMBER;
2012
2013 BEGIN
2014
2015
2016 OPEN c_period_info;
2017 FETCH c_period_info INTO p_S_hp_pcent ,p_S_hb_pcent ,p_W_hp_pcent , p_W_hb_pcent ,p_hc_pcent;
2018 IF c_period_info%FOUND THEN
2019 l_return := 1 ;
2020 ELSE
2021 l_return := 0 ;
2022 END IF;
2023 CLOSE c_period_info ;
2024
2025 RETURN l_return;
2026 EXCEPTION
2027
2028 WHEN others THEN
2029 RETURN 0 ;
2030 END ;
2031
2032
2033 END pay_fi_general;
[Home] [Help]