[Home] [Help]
PACKAGE BODY: APPS.PER_VIEWS_PKG
Source
1 package body PER_VIEWS_PKG as
2 /* $Header: peronvew.pkb 120.0.12010000.3 2008/09/25 05:02:24 nshrikha ship $ */
3 --
4 CURSOR csr_get_payroll (P_assignment_id number,
5 P_calculation_date date ) IS
6 select a.payroll_id,
7 a.effective_start_date,
8 a.effective_end_date,
9 a.business_group_id,
10 b.DATE_START,
11 b.ACTUAL_TERMINATION_DATE
12 from PER_ASSIGNMENTS_F a,
13 PER_PERIODS_OF_SERVICE b
14 where a.assignment_id = P_assignment_id
15 and P_calculation_date between a.effective_start_date and
16 a.effective_end_date
17 and a.PERIOD_OF_SERVICE_ID = b.PERIOD_OF_SERVICE_ID;
18 --
19 --
20 CURSOR csr_get_period (p_payroll_id number,
21 p_effective_date date ) is
22 select PERIOD_NUM,
23 START_DATE,
24 END_DATE
25 from PER_TIME_PERIODS
26 where PAYROLL_ID = p_payroll_id
27 and p_effective_date between START_DATE and END_DATE;
28 --
29 --
30 CURSOR csr_calc_accrual (P_start_date date,
31 P_end_date date,
32 P_assignment_id number,
33 P_plan_id number ) IS
34 select sum(to_number(nvl(pev.SCREEN_ENTRY_VALUE,'0')) *
35 to_number(pnc.add_or_subtract))
36 from pay_net_calculation_rules pnc,
37 pay_element_entry_values_f pev,
38 pay_element_entries_f pee
39 where pnc.accrual_plan_id = p_plan_id
40 and pnc.input_value_id = pev.input_value_id + 0
41 and pev.element_entry_id = pee.element_entry_id
42 and pee.assignment_id = P_assignment_id
43 and pee.effective_start_date between P_start_date and
44 P_end_date;
45 --
46 --
47 CURSOR csr_get_total_periods ( p_payroll_id number,
48 p_date date ) is
49 select min(start_date),
50 min(end_date),
51 max(start_date),
52 max(end_date),
53 count(period_num)
54 from per_time_periods
55 where payroll_id = p_payroll_id
56 -- and to_char(P_date,'YYYY/MM/DD') = to_char(end_date,'YYYY/MM/DD'); -- bug 6706398
57 and to_char(P_date,'YYYY') = to_char(end_date,'YYYY'); -- bug 6706398
58
59
60 --
61 -- -------------------------------------------------------------------------
62 -- --------------------< PER_GET_GRADE_STEP >-------------------------------
63 -- -------------------------------------------------------------------------
64 function PER_GET_GRADE_STEP
65 ( p_grade_spine_id NUMBER,
66 p_step_id NUMBER,
67 p_parent_spine_id NUMBER,
68 p_effective_start_date DATE
69 )
70 return number
71 is
72 l_grade_step number ;
73 BEGIN
74
75 select count(*)
76 into l_grade_step
77 from per_spinal_point_steps_f psps
78 , per_spinal_points psp
79 , per_spinal_point_steps_f psps1
80 where psps.grade_spine_id = p_grade_spine_id
81 and psps.step_id = p_step_id
82 and psps1.grade_spine_id = psps.grade_spine_id
83 and psp.spinal_point_id = psps.spinal_point_id
84 and psps.sequence >= psps1.sequence
85 and psp.parent_spine_id = p_parent_spine_id
86 and p_effective_start_date between psps.effective_start_date
87 and psps.effective_end_date
88 and p_effective_start_date between psps1.effective_start_date
89 and psps1.effective_end_date ;
90
91 return (l_grade_step);
92
93 END PER_GET_GRADE_STEP ;
94
95 -- -------------------------------------------------------------------------
96 -- --------------------< PER_CALC_COMPARATIO >------------------------------
97 -- -------------------------------------------------------------------------
98 function PER_CALC_COMPARATIO
99 ( p_assignment_id NUMBER,
100 p_change_date DATE,
101 p_actual_salary NUMBER,
102 p_element_entry_id NUMBER,
103 p_normal_hours NUMBER,
104 p_org_working_hours NUMBER,
105 p_pos_working_hours NUMBER,
106 p_org_frequency VARCHAR2,
107 p_pos_frequency VARCHAR2,
108 p_number_per_fiscal_year NUMBER,
109 p_grade_id NUMBER,
110 p_rate_id NUMBER,
111 p_pay_basis VARCHAR2,
112 p_rate_basis VARCHAR2,
113 p_business_group_id NUMBER
114 )
115 return number
116 is
117 --
118 -- Declare Variables
119 --
120 v_minimum NUMBER;
121 v_maximum NUMBER;
122 v_mid_value NUMBER;
123 v_working_hours NUMBER;
124 v_frequency VARCHAR2(80);
125 v_adj_mid NUMBER;
126 --changes for bug no 5945278 starts here
127 v_PAY_BASIS_ID per_assignments_f.pay_basis_id%type;
128 v_PAY_ANNUALIZATION_FACTOR PER_PAY_BASES.PAY_ANNUALIZATION_FACTOR%type;
129 v_GRADE_ANNUALIZATION_FACTOR PER_PAY_BASES.GRADE_ANNUALIZATION_FACTOR%type;
130 v_proposed_salary number;
131 --changes for bug no 5945278 ends here
132 cursor grade_rate_values (l_assignment_id NUMBER,
133 l_grade_id NUMBER,
134 l_rate_id NUMBER,
135 l_change_date DATE)
136 is
137 select gr.minimum
138 , gr.mid_value
139 , gr.maximum
140 --changes for bug no 5945278 starts here
141 , a.PAY_BASIS_ID
142 --changes for bug no 5945278 ends here
143 from pay_grade_rules_f gr
144 , per_assignments_f a
145 , per_pay_proposals pp
146 where gr.grade_or_spinal_point_id = a.grade_id
147 and pp.change_date between gr.effective_start_date
148 and gr.effective_end_date
149 and pp.change_date between a.effective_start_date
150 and a.effective_end_date
151 and a.assignment_id = pp.assignment_id
152 and pp.assignment_id = l_assignment_id
153 and gr.grade_or_spinal_point_id = l_grade_id
154 and gr.rate_id = l_rate_id
155 and l_change_date between gr.effective_start_date
156 and gr.effective_end_date
157 order by gr.effective_start_date desc ;
158 --changes for bug no 5945278 starts here
159 Cursor ANNUALIZATION_FACTOR(l_PAY_BASIS_ID number) is
160 select PAY_ANNUALIZATION_FACTOR,GRADE_ANNUALIZATION_FACTOR from PER_PAY_BASES
161 where PAY_BASIS_ID = l_PAY_BASIS_ID;
162 --changes for bug no 5945278 ends here
163 ---------------------------------------------------------------------------
164 -- This function pro-rates a non-hourly salary based on the normal hours
165 -- worked and the standard hours for the organization
166 ---------------------------------------------------------------------------
167 function std_hours_adjustment(l_salary NUMBER,
168 l_normal_hours NUMBER,
169 l_working_hours NUMBER,
170 l_pay_basis VARCHAR2,
171 l_rate_basis VARCHAR2,
172 --changes for bug no 5945278 starts here
173 l_GRADE_ANNUALIZATION_FACTOR number)
174 --changes for bug no 5945278 ends here
175 return NUMBER
176 is
177 v_adjustment_factor NUMBER (15,5) ;
178 v_adjusted_salary NUMBER (15,5) ;
179 BEGIN
180 --changes for bug no 5945278 starts here
181 /* if ( (l_working_hours is not null) and (l_working_hours <> 0) ) and
182 (l_normal_hours is not null) and
183 (l_pay_basis <> 'HOURLY') and
184 (l_rate_basis <> 'HOURLY') then
185 v_adjustment_factor := l_normal_hours/l_working_hours ;*/
186 if (l_pay_basis = 'HOURLY') and
187 (l_rate_basis = 'HOURLY') then
188 -- v_adjustment_factor := l_normal_hours/l_working_hours ;
189 v_adjusted_salary := l_salary ;
190 else
191 v_adjusted_salary := l_salary * l_GRADE_ANNUALIZATION_FACTOR ;
192 --changes for bug no 5945278 ends here
193 end if;
194 return (v_adjusted_salary);
195 END std_hours_adjustment;
196
197 --changes for bug no 5945278 starts here
198 function proposed_Sal_adjustment(l_assignment_id NUMBER,
199 l_change_date Date,
200 l_salary NUMBER,
201 l_pay_basis VARCHAR2,
202 l_rate_basis VARCHAR2,
203 l_PAY_ANNUALIZATION_FACTOR number)
204 return NUMBER
205 is
206 v_fte_factor NUMBER (15,5) ;
207 v_proposed_salary NUMBER (15,5) ;
208 BEGIN
209 v_fte_factor:=PER_SALADMIN_UTILITY.get_fte_factor(l_assignment_id ,l_change_date);
210 if (l_pay_basis = 'HOURLY') and
211 (l_rate_basis = 'HOURLY') then
212 v_proposed_salary := l_salary /v_fte_factor;
213 else
214 v_proposed_salary := l_salary * l_PAY_ANNUALIZATION_FACTOR/v_fte_factor ;
215 end if;
216 return (v_proposed_salary);
217 END proposed_Sal_adjustment;
218 --changes for bug no 5945278 ends here
219 ----------------------------------------------------------------------------
220 -- Function to calculate the period salaries based on the period
221 -- salary to be calculated and salary basis of the value being passed
222 ----------------------------------------------------------------------------
223 function sal_basis_adjustment (l_basis_1 VARCHAR2,
224 l_basis_2 VARCHAR2,
225 l_value NUMBER,
226 l_normal_hours NUMBER DEFAULT NULL,
227 l_frequency VARCHAR2 DEFAULT NULL,
228 l_number_per_fiscal_year NUMBER DEFAULT NULL)
229 return number
230 is
231 --
232 v_adjusted_value NUMBER;
233 v_annual NUMBER;
234 v_monthly NUMBER;
235 v_period NUMBER;
236 --
237 BEGIN
238 if l_basis_1 = l_basis_2 then
239 return l_value;
240 end if;
241 --
242 if l_basis_1 = 'ANNUAL' then
243 if l_basis_2 = 'MONTHLY' then
244 v_annual := 12;
245 elsif l_basis_2 = 'PERIOD' then
246 v_annual := l_number_per_fiscal_year;
247 elsif l_basis_2 = 'HOURLY' then
248 if l_frequency = 'D' then
249 v_annual := 261 * l_normal_hours;
250 elsif l_frequency = 'M' then
251 v_annual := 12 * l_normal_hours;
252 elsif l_frequency = 'W' then
253 v_annual := 52 * l_normal_hours;
254 elsif l_frequency = 'Y' then
255 v_annual := l_normal_hours;
256 end if;
257 end if;
258 v_adjusted_value := round (l_value * v_annual,2);
259 --
260 elsif l_basis_1 = 'MONTHLY' then
261 if l_basis_2 = 'ANNUAL' then
262 v_monthly := 1/12;
263 elsif l_basis_2 = 'PERIOD' then
264 v_monthly := l_number_per_fiscal_year/12;
265 elsif l_basis_2 = 'HOURLY' then
266 if l_frequency = 'D' then
267 v_monthly := 22.5 * l_normal_hours;
268 elsif l_frequency = 'M' then
269 v_monthly := l_normal_hours;
270 elsif l_frequency = 'W' then
271 v_monthly := (52/12) * l_normal_hours;
272 elsif l_frequency = 'Y' then
273 v_monthly := (1/12) * l_normal_hours;
274 end if;
275 end if;
276 v_adjusted_value := round( l_value * v_monthly, 2);
277 --
278 elsif l_basis_1 = 'PERIOD' then
279 if l_basis_2 = 'ANNUAL' then
280 v_period := 1/l_number_per_fiscal_year;
281 elsif l_basis_2 = 'PERIOD' then
282 v_period := 1;
283 elsif l_basis_2 = 'MONTHLY' then
284 v_period := (1/l_number_per_fiscal_year) * 12;
285 elsif l_basis_2 = 'HOURLY' then
286 if l_frequency = 'D' then
287 v_period := (261/l_number_per_fiscal_year) * l_normal_hours;
288 elsif l_frequency = 'M' then
289 v_period := (12/l_number_per_fiscal_year) * l_normal_hours;
290 elsif l_frequency = 'W' then
291 v_period := (52/l_number_per_fiscal_year) * l_normal_hours;
292 elsif l_frequency = 'Y' then
293 v_period := (1/l_number_per_fiscal_year) * l_normal_hours;
294 end if;
295 end if;
296 v_adjusted_value := round (l_value * v_period,2);
297 end if;
298 --
299 return v_adjusted_value;
300 --
301 EXCEPTION
302 WHEN ZERO_DIVIDE then
303 return NULL;
304 --
305 END sal_basis_adjustment;
306
307 ----------------------------------------------------------------------------
308 -- Function to calculate the comparatio
309 ----------------------------------------------------------------------------
310 function comparatio (l_actual_salary NUMBER,
311 l_mid_value NUMBER,
312 l_rate_basis VARCHAR2,
313 l_salary_basis VARCHAR2,
314 l_normal_hours NUMBER DEFAULT NULL,
315 l_frequency VARCHAR2 DEFAULT NULL,
316 l_number_per_fiscal_year NUMBER DEFAULT NULL
317 )
318 return number
319 is
320 v_adj_mid_value NUMBER := l_mid_value ;
321 v_adj_actual_salary NUMBER := l_actual_salary ;
322 v_comparatio NUMBER ;
323 BEGIN
324 if(l_rate_basis = 'HOURLY') then
325 v_adj_mid_value := sal_basis_adjustment(l_salary_basis,
326 l_rate_basis,
327 l_mid_value,
328 l_normal_hours,
329 l_frequency,
330 l_number_per_fiscal_year );
331 else
332 v_adj_actual_salary := sal_basis_adjustment(l_rate_basis,
333 l_salary_basis,
334 l_actual_salary,
335 l_normal_hours,
336 l_frequency,
337 l_number_per_fiscal_year );
338 end if;
339
340 v_comparatio := round ( (v_adj_actual_salary/v_adj_mid_value) * 100, 2) ;
341
342 return v_comparatio ;
343
344 EXCEPTION
345 WHEN ZERO_DIVIDE then
346 return NULL;
347 END comparatio;
348
349 BEGIN
350 --
351 -- No Comparatio if the elements are not present
352 --
353 if p_element_entry_id is null then
354 return null;
355 end if;
356
357 --
358 -- Populate working hours and frequency that is to be used in calculations
359 --
360 if (p_pos_working_hours is null) then
361 if(p_org_working_hours is null) then
362 select fnd_number.canonical_to_number(working_hours)
363 into v_working_hours
364 from per_business_groups
365 where business_group_id = p_business_group_id ;
366 else
367 v_working_hours := p_org_working_hours ;
368 end if;
369 else
370 v_working_hours := p_pos_working_hours ;
371 end if;
372
373 if (p_pos_frequency is null) then
374 if(p_org_frequency is null) then
375 select frequency
376 into v_frequency
377 from per_business_groups
378 where business_group_id = p_business_group_id ;
379 else
380 v_frequency := p_org_frequency ;
381 end if;
382 else
383 v_frequency := p_pos_frequency ;
384 end if;
385
386 --
387 -- Get the Grade Rate Values for the particular assignment Grade
388 --
389 open grade_rate_values ( p_assignment_id,
390 p_grade_id,
391 p_rate_id,
392 p_change_date) ;
393 --changes for bug no 5945278 starts here
394 fetch grade_rate_values into v_minimum, v_mid_value, v_maximum,v_PAY_BASIS_ID ;
395 --changes for bug no 5945278 ends here
396 if grade_rate_values%found then
397
398 open ANNUALIZATION_FACTOR ( v_PAY_BASIS_ID);
399 fetch ANNUALIZATION_FACTOR into v_PAY_ANNUALIZATION_FACTOR, v_GRADE_ANNUALIZATION_FACTOR ;
400 if ANNUALIZATION_FACTOR%found then
401 v_adj_mid := std_hours_adjustment(v_mid_value,
402 p_normal_hours,
403 v_working_hours,
404 p_pay_basis,
405 p_rate_basis,
406 --changes for bug no 5945278 starts here
407 v_GRADE_ANNUALIZATION_FACTOR ) ;
408
409 v_proposed_salary:= proposed_Sal_adjustment(p_assignment_id,
410 p_change_date ,
411 p_actual_salary,
412 p_pay_basis ,
413 p_rate_basis,
414 v_PAY_ANNUALIZATION_FACTOR);
415 end if;
416 close ANNUALIZATION_FACTOR ;
417 --changes for bug no 5945278 ends here
418 end if;
419 close grade_rate_values ;
420 --changes for bug no 5945278 starts here
421 /* return ( comparatio( to_number(p_actual_salary),
422 v_adj_mid,
423 p_rate_basis,
424 p_pay_basis,
425 p_normal_hours,
426 v_frequency,
427 p_number_per_fiscal_year) ) ;
428 */
429
430 return round ( (v_proposed_salary/v_adj_mid) * 100, 3);
431 EXCEPTION
432 WHEN ZERO_DIVIDE then
433 return NULL;
434 --changes for bug no 5945278 ends here
435 END PER_CALC_COMPARATIO;
436
437
438 -- -------------------------------------------------------------------------
439 -- --------------------< PER_GET_PARENT_ORG >-------------------------------
440 -- -------------------------------------------------------------------------
441 -- View uses the function PER_GET_PARENT_ORG to find out the parent node
442 -- in the organization hierarchy, given the child organization and its
443 -- level in the hierarchy. The function traverses the hierarchy and
444 -- gets the parent at the given level.
445
446 function PER_GET_PARENT_ORG
447 ( p_org_child number,
448 p_level number,
449 p_business_group_id number,
450 p_org_structure_version_id number)
451 return number
452 is
453 org_id_parent number;
454 BEGIN
455 --------------------------------------------------------------
456 -- Traverse the hierarchy tree upto the input level starting
457 -- from the child organization and return the parent org name
458 --------------------------------------------------------------
459 select str.organization_id_parent
460 into org_id_parent
461 from per_org_structure_elements str
462 where level = p_level
463 connect by str.organization_id_child = prior str.organization_id_parent
464 and str.org_structure_version_id = p_org_structure_version_id
465 and str.business_group_id = p_business_group_id
466 start with str.organization_id_child = p_org_child
467 and str.org_structure_version_id = p_org_structure_version_id
468 and str.business_group_id = p_business_group_id ;
469
470 return org_id_parent;
471
472 END PER_GET_PARENT_ORG ;
473
474 -- -------------------------------------------------------------------------
475 -- --------------------< PER_GET_EFFECTIVE_END_DATE >-----------------------
476 -- -------------------------------------------------------------------------
477 --
478 -- Function to get the Effective End Date for the Assignment History View
479 --
480 function PER_GET_EFFECTIVE_END_DATE
481 ( p_assignment_id number,
482 p_effective_start_date date
483 )
484 return date
485 IS
486 CURSOR E_DATE1 is
487 select min(EFFECTIVE_START_DATE) - 1
488 from PER_ALL_ASSIGNMENTS_F
489 where ASSIGNMENT_ID = p_assignment_id
490 and EFFECTIVE_START_DATE > p_effective_start_date ;
491
492 CURSOR E_DATE2 is
493 select max(EFFECTIVE_END_DATE)
494 from PER_ALL_ASSIGNMENTS_F
495 where ASSIGNMENT_ID = p_assignment_id ;
496
497 d_date date ;
498 BEGIN
499 open E_DATE1 ;
500 fetch E_DATE1 into d_date ;
501
502 if E_DATE1%notfound or E_DATE1%notfound is null or d_date is null then
503
504 open E_DATE2 ;
505 fetch E_DATE2 into d_date ;
506
507 if E_DATE2%notfound or E_DATE2%notfound is null then
508 close E_DATE1;
509 close E_DATE2;
510 return (null);
511 end if;
512
513 if d_date = hr_general.end_of_time then
514 d_date := null;
515 end if;
516
517 close E_DATE2;
518 return (d_date);
519 else
520 close E_DATE1 ;
521 return (d_date);
522 end if;
523
524 END PER_GET_EFFECTIVE_END_DATE;
525
526
527 -- -------------------------------------------------------------------------
528 -- --------------------< PER_GET_ORGANIZATION_EMPLOYEES >-------------------
529 -- -------------------------------------------------------------------------
530 function PER_GET_ORGANIZATION_EMPLOYEES
531 ( p_organization_id number
532 )
533 return number
534 IS
535 l_number_of_emps number ;
536 BEGIN
537
538 select count(distinct PERSON_ID)
539 into l_number_of_emps
540 from PER_ASSIGNMENTS_X ass
541 where ass.ORGANIZATION_ID = p_organization_id
542 and ass.ASSIGNMENT_TYPE = 'E' ;
543
544 return (l_number_of_emps) ;
545
546 END PER_GET_ORGANIZATION_EMPLOYEES ;
547
548 -- -------------------------------------------------------------------------
549 -- --------------------< PER_GET_ELEMENT_ACCRUAL >--------------------------
550 -- -------------------------------------------------------------------------
551 FUNCTION PER_GET_ELEMENT_ACCRUAL
552 ( P_assignment_id number,
553 P_calculation_date date,
554 P_input_value_id number,
555 P_plan_id number DEFAULT NULL,
556 P_plan_category varchar2 DEFAULT NULL)
557 RETURN Number is
558 --
559 l_accrual number := 0;
560 --
561 c_date date := P_calculation_date;
562 n1 number;
563 n2 number;
564 n3 number;
565 d1 date;
566 d2 date;
567 d3 date;
568 d4 date;
569 d5 date;
570 d6 date;
571 d7 date;
572 p_mod varchar2(1) := 'N';
573 --
574 BEGIN
575 --
576 per_views_pkg.per_accrual_calc_detail(
577 P_assignment_id => P_assignment_id,
578 P_calculation_date => c_date,
579 P_plan_id => P_plan_id,
580 P_plan_category => P_plan_category,
581 P_accrual => l_accrual,
582 P_payroll_id => n1,
583 P_first_period_start => d1,
584 P_first_period_end => d2,
585 P_last_period_start => d3,
586 P_last_period_end => d4,
587 P_cont_service_date => d5,
588 P_start_date => d6,
589 P_end_date => d7,
590 P_current_ceiling => n2,
591 P_current_carry_over => n3);
592 --
593 select nvl(sum(to_number(nvl(pev.SCREEN_ENTRY_VALUE,'0'))), 0)
594 into l_accrual
595 from pay_element_entry_values_f pev,
596 pay_element_entries_f pee
597 where pev.input_value_id = p_input_value_id
598 and pev.element_entry_id = pee.element_entry_id
599 and pee.assignment_id = p_assignment_id
600 and pee.effective_start_date between d6 and d7 ;
601 --
602 IF l_accrual is null
603 THEN
604 l_accrual := 0;
605 END IF;
606 --
607 RETURN(l_accrual);
608 --
609 END PER_GET_ELEMENT_ACCRUAL;
610
611 -- -------------------------------------------------------------------------
612 -- --------------------< PER_GET_ACCRUAL >----------------------------------
613 -- -------------------------------------------------------------------------
614 FUNCTION PER_GET_ACCRUAL
615 ( P_assignment_id number,
616 P_calculation_date date,
617 P_plan_id number DEFAULT NULL,
618 P_plan_category varchar2 DEFAULT NULL)
619 RETURN Number is
620 --
621 l_accrual number := 0;
622 --
623 c_date date := P_calculation_date;
624 n1 number;
625 n2 number;
626 n3 number;
627 d1 date;
628 d2 date;
629 d3 date;
630 d4 date;
631 d5 date;
632 d6 date;
633 d7 date;
634 p_mod varchar2(1) := 'N';
635 --
636 BEGIN
637 --
638 per_views_pkg.per_accrual_calc_detail(
639 P_assignment_id => P_assignment_id,
640 P_calculation_date => c_date,
641 P_plan_id => P_plan_id,
642 P_plan_category => P_plan_category,
643 P_accrual => l_accrual,
644 P_payroll_id => n1,
645 P_first_period_start => d1,
646 P_first_period_end => d2,
647 P_last_period_start => d3,
648 P_last_period_end => d4,
649 P_cont_service_date => d5,
650 P_start_date => d6,
651 P_end_date => d7,
652 P_current_ceiling => n2,
653 P_current_carry_over => n3);
654 --
655 IF l_accrual is null
656 THEN
657 l_accrual := 0;
658 END IF;
659 --
660 RETURN(l_accrual);
661 --
662 END PER_GET_ACCRUAL;
663
664 -- -------------------------------------------------------------------------
665 -- --------------------< PER_ACCRUAL_CALC_DETAIL >--------------------------
666 -- -------------------------------------------------------------------------
667 PROCEDURE PER_ACCRUAL_CALC_DETAIL
668 (P_assignment_id IN number,
669 P_calculation_date IN OUT NOCOPY date,
670 P_plan_id IN number DEFAULT NULL,
671 P_plan_category IN varchar2 DEFAULT NULL,
672 P_mode IN varchar2 DEFAULT 'N',
673 P_accrual OUT NOCOPY number,
674 P_payroll_id IN OUT NOCOPY number,
675 P_first_period_start IN OUT NOCOPY date,
676 P_first_period_end IN OUT NOCOPY date,
677 P_last_period_start IN OUT NOCOPY date,
678 P_last_period_end IN OUT NOCOPY date,
679 P_cont_service_date OUT NOCOPY date,
680 P_start_date OUT NOCOPY date,
681 P_end_date OUT NOCOPY date,
682 P_current_ceiling OUT NOCOPY number,
683 P_current_carry_over OUT NOCOPY number) IS
684 -- Get Plan details
685 CURSOR csr_get_plan_details ( P_business_group Number) is
686 select pap.accrual_plan_id,
687 pap.accrual_plan_element_type_id,
688 pap.accrual_units_of_measure,
689 pap.ineligible_period_type,
690 pap.ineligible_period_length,
691 pap.accrual_start,
692 pev.SCREEN_ENTRY_VALUE,
693 pee.element_entry_id
694 from pay_accrual_plans pap,
695 pay_element_entry_values_f pev,
696 pay_element_entries_f pee,
697 pay_element_links_f pel,
698 pay_element_types_f pet,
699 pay_input_values_f piv
700 where ( pap.accrual_plan_id = p_plan_id OR
701 pap.accrual_category = P_plan_category )
702 and pap.business_group_id + 0 = P_business_group
703 and pap.accrual_plan_element_type_id = pet.element_type_id
704 and P_calculation_date between pet.effective_start_date and
705 pet.effective_end_date
706 and pet.element_type_id = pel.element_type_id
707 and P_calculation_date between pel.effective_start_date and
708 pel.effective_end_date
709 and pel.element_link_id = pee.element_link_id
710 and pee.assignment_id = P_assignment_id
711 and P_calculation_date between pee.effective_start_date and
712 pee.effective_end_date
713 and piv.element_type_id =
714 pap.accrual_plan_element_type_id
715 and piv.name = 'Continuous Service Date'
716 and P_calculation_date between piv.effective_start_date and
717 piv.effective_end_date
718 and pev.element_entry_id = pee.element_entry_id
719 and pev.input_value_id + 0 = piv.input_value_id
720 and P_calculation_date between pev.effective_start_date and
721 pev.effective_end_date;
722 --
723 --
724 l_asg_eff_start_date date := null;
725 l_asg_eff_end_date date := null;
726 l_business_group_id number := null;
727 l_service_start_date date := null;
728 l_termination_date date := null;
729 --
730 l_calc_period_num number := 0;
731 l_calc_start_date date := null;
732 l_calc_end_date date := null;
733 --
734 l_number_of_period number := 0;
735 --
736 l_acc_plan_type_id number := 0;
737 l_acc_plan_ele_type number := 0;
738 l_acc_uom varchar2(30) := null;
739 l_inelig_period varchar2(30) := null;
740 l_inelig_p_length number := 0;
741 l_accrual_start varchar2(30) := null;
742 l_cont_service_date date := null;
743 l_csd_screen_value varchar2(30) := null;
744 l_element_entry_id number := 0;
745 --
746 l_plan_start_date date := null;
747 --
748 l_total_accrual number := 0;
749 l_plan_accrual number := 0;
750 --
751 l_temp varchar2(30) := null;
752 l_temp_date date := null;
753 --
754 p_param_first_pstdt date := null;
755 p_param_first_pendt date := null;
756 p_param_first_pnum number := 0;
757 p_param_acc_calc_edt date := null;
758 p_param_acc_calc_pno number := 0;
759 --
760 -- Main process
761 --
762 BEGIN
763 --
764 P_payroll_id := 0;
765 P_first_period_start := null;
766 P_first_period_end := null;
767 P_last_period_start := null;
768 P_last_period_end := null;
769 --
770 ---
771 --- If both param null. RETURN
772 --
773 IF P_plan_id is null AND P_plan_category is null
774 THEN
775 return ;
776 END IF;
777 OPEN csr_get_payroll(P_assignment_id, P_calculation_date);
778 FETCH csr_get_payroll INTO P_payroll_id,
779 l_asg_eff_start_date,
780 l_asg_eff_end_date,
781 l_business_group_id,
782 l_service_start_date,
783 l_termination_date;
784 IF csr_get_payroll%NOTFOUND
785 THEN
786 CLOSE csr_get_payroll;
787 return ;
788 END IF;
789 CLOSE csr_get_payroll;
790 --
791 -- Get start and end date for the Calculation date
792 --
793 OPEN csr_get_period(P_payroll_id, P_calculation_date);
794 FETCH csr_get_period INTO l_calc_period_num,
795 l_calc_start_date,
796 l_calc_end_date;
797 IF csr_get_period%NOTFOUND
798 THEN
799 CLOSE csr_get_period;
800 return ;
801 END IF;
802 CLOSE csr_get_period;
803 --
804 -- Partial first period if start
805 --
806 -- Set return dates for the net process if nothing to accrue in this period
807 --
808 P_start_date := l_calc_start_date;
809 P_end_date := P_calculation_date;
810 --
811 --
812 -- Get total number of periods for the year of calculation
813 --
814 OPEN csr_get_total_periods(P_payroll_id, l_calc_end_date);
815 FETCH csr_get_total_periods INTO P_first_period_start,
816 P_first_period_end,
817 P_last_period_start,
818 P_last_period_end,
819 l_number_of_period;
820 IF csr_get_total_periods%NOTFOUND
821 THEN
822 CLOSE csr_get_total_periods;
823 return ;
824 END IF;
825 CLOSE csr_get_total_periods;
826 -- Set l_number_of_period such that it is based on NUMBER_PER_FISCAL_YEAR
827 -- for period type of payroll. Ie. The number returned from
828 -- csr_get_total_periods is the number of periods defined for this payroll
829 -- in the given calendar year - so payrolls defined mid-year accrue at a
830 -- different rate than if it had a full year of payroll periods.
831 --
832 SELECT number_per_fiscal_year
833 INTO l_number_of_period
834 FROM per_time_period_types TPT,
835 pay_payrolls_f PPF
836 WHERE TPT.period_type = PPF.period_type
837 AND PPF.payroll_id = P_payroll_id
838 AND l_calc_end_date BETWEEN PPF.effective_start_date
839 AND PPF.effective_end_date;
840 --
841 --
842 -- In case of carry over a dummy date of 31-JUL-YYYY is passed in order to get
843
844 OPEN csr_get_period (P_payroll_id, P_first_period_start);
845 FETCH csr_get_period INTO p_param_first_pnum,
846 p_param_first_pstdt,
847 p_param_first_pendt;
848 IF csr_get_period%NOTFOUND
849 THEN
850 CLOSE csr_get_period;
851 return ;
852 END IF;
853 CLOSE csr_get_period;
854 --
855 -- Check termination date and adjust end date of the last calc Period
856 --
857 OPEN csr_get_period (P_payroll_id,
858 nvl(l_termination_date,P_calculation_date));
859 FETCH csr_get_period INTO p_param_acc_calc_pno,
860 l_temp_date,
861 p_param_acc_calc_edt;
862 IF csr_get_period%NOTFOUND
863 THEN
864 CLOSE csr_get_period;
865 return ;
866 END IF;
867 CLOSE csr_get_period;
868 --
869 --
870 -- No accruals for the partial periods
871 --
872 IF nvl(l_termination_date,P_calculation_date) < p_param_acc_calc_edt
873 THEN
874 p_param_acc_calc_pno := p_param_acc_calc_pno - 1;
875 p_param_acc_calc_edt := l_temp_date - 1;
876 END IF;
877 --
878 -- Open plan cursor and check at least one plan should be there
879 --
880 OPEN csr_get_plan_details(l_business_group_id);
881 FETCH csr_get_plan_details INTO l_acc_plan_type_id,
882 l_acc_plan_ele_type,
883 l_acc_uom,
884 l_inelig_period,
885 l_inelig_p_length,
886 l_accrual_start,
887 l_csd_screen_value,
888 l_element_entry_id;
889 IF csr_get_plan_details%NOTFOUND
890 THEN
891 CLOSE csr_get_plan_details;
892 return ;
893 END IF;
894 --
895 -- Loop thru all the plans and call function to calc. accruals for a plan
896 --
897 LOOP
898 l_temp_date := null;
899 --
900 --
901 -- "Continous Service Date" is ALWAYS determined by:
902 -- 1. "Continuous Service Date" entry value on accrual plan.
903 -- 2. Hire Date of current period of service (ie. in absence of 1.)
904 --
905 IF l_csd_screen_value is null
906 THEN
907 l_cont_service_date := l_service_start_date;
908 ELSE
909 --
910 -- Fix for WWBUG 1717601.
911 -- Changed below line to use canonical_to_date rather than DD_MON-YYYY
912 -- format mask.
913 --
914 l_cont_service_date := fnd_date.canonical_to_date(l_csd_screen_value);
915 END IF;
916 --
917 -- The "p_param_first..." variables determine when accrual begins for this
918 -- plan and assignment. Accrual begins according to "Accrual Start Rule" and
919 -- hire date as follows:
920 -- Accrual Start Rule Begin Accrual on...
921 -- ================== ==================================================
922 -- Beginning of Year First period of new calendar year FOLLOWING hire date.
923 -- Hire Date First period following hire date
924 -- 6 Months After Hire First period following 6 month anniversary of hire date.
925 -- NOTE: "Hire Date" is the "Continuous Service Date" as determined above.
926 --
927 IF l_accrual_start = 'BOY'
928 THEN
929 l_temp_date := TRUNC(ADD_MONTHS(l_cont_service_date,12),'YEAR');
930 OPEN csr_get_period (P_payroll_id, l_temp_date);
931 FETCH csr_get_period INTO p_param_first_pnum,
932 p_param_first_pstdt,
933 p_param_first_pendt;
934 IF csr_get_period%NOTFOUND
935 THEN
936 CLOSE csr_get_period;
937 return ;
938 END IF;
939 CLOSE csr_get_period;
940 l_temp_date := null;
941 ELSIF l_accrual_start = 'HD'
942 THEN
943 NULL;
944 -- p_param_first... vars have been set above (location get_accrual.30)
945
946 ELSIF l_accrual_start = 'PLUS_SIX_MONTHS'
947 THEN
948 --
949 -- Actually get the period in force the day before the six months is up.
950 -- This is because we subsequently get the following period as the one
951 -- in which accruals should start. If a period starts on the six
952 -- month anniversary, the asg should qualify from that period, and
953 -- not have to wait for the next one. Example:
954 --
955 -- Assume monthly periods.
956 --
957 -- l_cont_service_date = 02-Jan-95
958 -- six month anniversary = 02-Jul-95
959 -- accruals start on 01-Aug-95
960 --
961 -- l_cont_service_date = 01-Jan-95
962 -- six month anniversary = 01-Jul-95
963 -- accruals should start on 01-Jul-95, not 01-Aug-95
964 --
965 --
966 OPEN csr_get_period (P_payroll_id,
967 ADD_MONTHS(l_cont_service_date,6) -1 );
968 FETCH csr_get_period INTO p_param_first_pnum,
969 p_param_first_pstdt,
970 l_temp_date;
971 IF csr_get_period%NOTFOUND
972 THEN
973 CLOSE csr_get_period;
974 return ;
975 END IF;
976 CLOSE csr_get_period;
977 --
978 OPEN csr_get_period (P_payroll_id, l_temp_date + 1);
979 FETCH csr_get_period INTO p_param_first_pnum,
980 p_param_first_pstdt,
981 p_param_first_pendt;
982 IF csr_get_period%NOTFOUND
983 THEN
984 CLOSE csr_get_period;
985 return ;
986 END IF;
987 CLOSE csr_get_period;
988 l_temp_date := null;
989 END IF;
990 --
991 -- Add period of ineligibility
992 --
993 IF l_accrual_start <> 'PLUS_SIX_MONTHS' AND
994 l_inelig_p_length > 0
995 THEN
996 IF l_inelig_period = 'BM'
997 THEN
998 l_temp_date := ADD_MONTHS(l_cont_service_date,
999 (l_inelig_p_length * 2));
1000 ELSIF l_inelig_period = 'F'
1001 THEN
1002 l_temp_date := to_date((l_cont_service_date +
1003 -- (l_inelig_p_length * 14)),'YYYY/MM/DD HH24:MI:SS');
1004 (l_inelig_p_length * 14))); -- bug 6706398
1005
1006 ELSIF l_inelig_period = 'CM'
1007 THEN
1008 l_temp_date := ADD_MONTHS(l_cont_service_date,
1009 l_inelig_p_length);
1010 ELSIF l_inelig_period = 'LM'
1011 THEN
1012 l_temp_date := to_date((l_cont_service_date +
1013 -- ( l_inelig_p_length * 28)),'YYYY/MM/DD HH24:MI:SS');
1014 ( l_inelig_p_length * 28))); -- bug 6706398
1015
1016 ELSIF l_inelig_period = 'Q'
1017 THEN
1018 l_temp_date := ADD_MONTHS(l_cont_service_date,
1019 (l_inelig_p_length * 3));
1020 ELSIF l_inelig_period = 'SM'
1021 THEN
1022 l_temp_date := ADD_MONTHS(l_cont_service_date,
1023 (l_inelig_p_length/2));
1024 ELSIF l_inelig_period = 'SY'
1025 THEN
1026 l_temp_date := ADD_MONTHS(l_cont_service_date,
1027 (l_inelig_p_length * 6));
1028 ELSIF l_inelig_period = 'W'
1029 THEN
1030 l_temp_date := to_date((l_cont_service_date +
1031 -- (l_inelig_p_length * 7)),'YYYY/MM/DD HH24:MI:SS');
1032 ( l_inelig_p_length * 28))); -- bug 6706398
1033
1034 ELSIF l_inelig_period = 'Y'
1035 THEN
1036 l_temp_date := ADD_MONTHS(l_cont_service_date,
1037 (l_inelig_p_length * 12));
1038 END IF;
1039 END IF;
1040 --
1041 -- Determine start and end date and setup return parmas.
1042 -- check Period of Service start date, plan element entry start date
1043 -- if later then first period start. Accrual period start date accordingly.
1044 --
1045 select min(effective_start_date)
1046 into l_plan_start_date
1047 from pay_element_entries_f
1048 where element_entry_id = l_element_entry_id;
1049 ---
1050 --- Set the return params
1051 --
1052 P_cont_service_date := l_cont_service_date;
1053 P_start_date := GREATEST(l_service_start_date,l_cont_service_date,
1054 l_plan_start_date,P_first_period_start);
1055 P_end_date := LEAST(NVL(L_termination_date,P_calculation_date)
1056 ,P_calculation_date);
1057 --
1058 IF ( l_temp_date is not null AND
1059 l_temp_date >= p_param_acc_calc_edt ) OR
1060 l_cont_service_date >= p_param_acc_calc_edt OR
1061 p_param_first_pstdt >= p_param_acc_calc_edt
1062 THEN
1063 l_plan_accrual := 0;
1064 ELSE
1065 --
1066 -- Set the Start Date appropriately.
1067 --
1068 l_temp_date := GREATEST(l_service_start_date,l_cont_service_date,
1069 l_plan_start_date);
1070 --
1071 IF l_temp_date > P_first_period_start
1072 AND l_temp_date > nvl(p_param_first_pstdt, l_temp_date - 1)
1073 THEN
1074 OPEN csr_get_period (P_payroll_id, l_temp_date);
1075 FETCH csr_get_period INTO p_param_first_pnum,
1076 p_param_first_pstdt,
1077 p_param_first_pendt;
1078 IF csr_get_period%NOTFOUND
1079 THEN
1080 CLOSE csr_get_period;
1081 return ;
1082 END IF;
1083 CLOSE csr_get_period;
1084 --
1085 -- No Accruals fro the partial periods. First period to start the
1086 -- accrual will be next one.
1087 --
1088 IF l_temp_date > p_param_first_pstdt
1089 THEN
1090 p_param_first_pendt := p_param_first_pendt +1;
1091 OPEN csr_get_period (P_payroll_id, p_param_first_pendt);
1092 FETCH csr_get_period INTO p_param_first_pnum,
1093 p_param_first_pstdt,
1094 p_param_first_pendt;
1095 IF csr_get_period%NOTFOUND
1096 THEN
1097 CLOSE csr_get_period;
1098 return ;
1099 END IF;
1100 CLOSE csr_get_period;
1101 END IF;
1102 END IF;
1103 --
1104 -- Call Function to Calculate accruals for a plan
1105 --
1106 IF p_param_acc_calc_edt < P_first_period_end
1107 THEN
1108 l_plan_accrual := 0;
1109 ELSE
1110 --
1111 per_views_pkg.per_get_accrual_for_plan
1112 ( p_plan_id => l_acc_plan_type_id,
1113 p_first_p_start_date => p_param_first_pstdt,
1114 p_first_p_end_date => p_param_first_pendt,
1115 p_first_calc_P_number => p_param_first_pnum,
1116 p_accrual_calc_p_end_date => p_param_acc_calc_edt,
1117 P_accrual_calc_P_number => p_param_acc_calc_pno,
1118 P_number_of_periods => l_number_of_period,
1119 P_payroll_id => P_payroll_id,
1120 P_assignment_id => P_assignment_id,
1121 P_plan_ele_type_id => l_acc_plan_ele_type,
1122 P_continuous_service_date => l_cont_service_date,
1123 P_Plan_accrual => l_plan_accrual,
1124 P_current_ceiling => P_current_ceiling,
1125 P_current_carry_over => P_current_carry_over);
1126 END IF;
1127 --
1128 END IF;
1129 --
1130 -- Add accrual to the total and Fetch next set of plan
1131 --
1132 l_total_accrual := l_total_accrual + l_plan_accrual;
1133 l_plan_accrual := 0;
1134 FETCH csr_get_plan_details INTO l_acc_plan_type_id,
1135 l_acc_plan_ele_type,
1136 l_acc_uom,
1137 l_inelig_period,
1138 l_inelig_p_length,
1139 l_accrual_start,
1140 l_csd_screen_value,
1141 l_element_entry_id;
1142 --
1143 EXIT WHEN csr_get_plan_details%NOTFOUND;
1144 --
1145 END LOOP;
1146 --
1147 CLOSE csr_get_plan_details;
1148 --
1149 IF l_total_accrual is null
1150 THEN
1151 l_total_accrual := 0;
1152 END IF;
1153 l_total_accrual := round(l_total_accrual,3);
1154 P_accrual := l_total_accrual;
1155 --
1156 -- Partial first period if end
1157 --
1158 --
1159 END PER_ACCRUAL_CALC_DETAIL;
1160
1161 -- -------------------------------------------------------------------------
1162 -- --------------------< PER_GET_ACCRUAL_FOR_PLAN >-------------------------
1163 -- -------------------------------------------------------------------------
1164 PROCEDURE PER_GET_ACCRUAL_FOR_PLAN
1165 ( p_plan_id Number,
1166 p_first_p_start_date date,
1167 p_first_p_end_date date,
1168 p_first_calc_P_number number,
1169 p_accrual_calc_p_end_date date,
1170 P_accrual_calc_P_number number,
1171 P_number_of_periods number,
1172 P_payroll_id number,
1173 P_assignment_id number,
1174 P_plan_ele_type_id number,
1175 P_continuous_service_date date,
1176 P_Plan_accrual OUT NOCOPY number,
1177 P_current_ceiling OUT NOCOPY number,
1178 P_current_carry_over OUT NOCOPY number) IS
1179 --
1180 --
1181 CURSOR csr_all_asg_status is
1182 select a.effective_start_date,
1183 a.effective_end_date,
1184 b.PER_SYSTEM_STATUS
1185 from per_assignments_f a,
1186 per_assignment_status_types b
1187 where a.assignment_id = P_assignment_id
1188 and a.effective_end_date between p_first_p_start_date and
1189 hr_general.end_of_time
1190 and a.ASSIGNMENT_STATUS_TYPE_ID =
1191 b.ASSIGNMENT_STATUS_TYPE_ID;
1192 --
1193 --
1194 CURSOR csr_get_bands (P_time_worked number ) is
1195 select annual_rate,
1196 ceiling,
1197 lower_limit,
1198 upper_limit,
1199 max_carry_over
1200 from pay_accrual_bands
1201 where accrual_plan_id = P_plan_id
1202 and P_time_worked >= lower_limit
1203 and P_time_worked < upper_limit;
1204 --
1205 --
1206 /* Fix for bug 6706398 starts here
1207 CURSOR csr_get_time_periods is
1208 select start_date,
1209 end_date,
1210 period_num
1211 from per_time_periods
1212 where to_char(end_date,'YYYY/MM/DD') =
1213 to_char(p_accrual_calc_p_end_date,'YYYY/MM/DD')
1214 and end_date <= p_accrual_calc_p_end_date
1215 and period_num >=
1216 decode (to_char(p_first_p_start_date,'YYYY/MM/DD'),
1217 to_char(p_accrual_calc_p_end_date,'YYYY/MM/DD'),
1218 p_first_calc_P_number, 1)
1219 and payroll_id = p_payroll_id
1220 ORDER by period_num; */
1221
1222 CURSOR csr_get_time_periods is
1223 select start_date,
1224 end_date,
1225 period_num
1226 from per_time_periods
1227 where to_char(end_date,'YYYY') =
1228 to_char(p_accrual_calc_p_end_date,'YYYY')
1229 and end_date <= p_accrual_calc_p_end_date
1230 and period_num >=
1231 decode (to_char(p_first_p_start_date,'YYYY'),
1232 to_char(p_accrual_calc_p_end_date,'YYYY'),
1233 p_first_calc_P_number, 1)
1234 and payroll_id = p_payroll_id
1235
1236 ORDER by period_num;
1237
1238 /*Fix for bug 6706398 ends here*/
1239
1240 --
1241 --Local varaiables
1242 l_start_Date date :=null;
1243 l_end_date date :=null;
1244 l_period_num number := 0;
1245 l_asg_eff_start_date date := null;
1246 l_asg_eff_end_date date := null;
1247 l_asg_status varchar2(30) := null;
1248 l_acc_rate_pp_1 number := 0;
1249 l_acc_rate_pp_2 number := 0;
1250 l_acc_deds number := 0;
1251 l_annual_rate number := 0;
1252 l_ceiling_1 number := 0;
1253 l_ceiling_2 number := 0;
1254 l_carry_over_1 number := 0;
1255 l_carry_over_2 number := 0;
1256 l_lower_limit number := 0;
1257 l_upper_limit number := 0;
1258 l_year_1 number := 0;
1259 l_year_2 number := 0;
1260 l_accrual number := 0;
1261 l_temp number := 0;
1262 l_temp2 varchar2(30) := null;
1263 l_band_change_date date := null;
1264 l_ceiling_flag varchar2(1) := 'N';
1265 l_curr_p_stdt date := null;
1266 l_curr_p_endt date := null;
1267 l_curr_p_num number := 0;
1268 l_mult_factor number := 0;
1269 l_unpaid_day number := 0;
1270 l_vac_taken number := 0;
1271 l_prev_end_date date := null;
1272 l_running_total number := 0;
1273 l_curr_p_acc number := 0;
1274 l_working_day number := 0;
1275 l_curr_ceiling number := 0;
1276 --
1277 --
1278 BEGIN
1279 --
1280 l_year_1 := TRUNC(ABS(months_between(P_continuous_service_date,
1281 P_first_p_end_date)/12));
1282 l_year_2 := TRUNC(ABS(months_between(P_continuous_service_date,
1283 p_accrual_calc_p_end_date)/12));
1284 --
1285 -- Get the band details using the years of service.
1286 --
1287 OPEN csr_get_bands (l_year_1);
1288 FETCH csr_get_bands INTO l_annual_rate,l_ceiling_1,
1289 l_lower_limit,l_upper_limit,
1290 l_carry_over_1;
1291 IF csr_get_bands%NOTFOUND THEN
1292 l_acc_rate_pp_1 := 0;
1293 ELSE
1294 l_acc_rate_pp_1 := l_annual_rate/P_number_of_periods;
1295 IF l_ceiling_1 is not null THEN
1296 l_ceiling_flag := 'Y';
1297 END IF;
1298 END IF;
1299 CLOSE csr_get_bands;
1300 --
1301 IF l_year_2 < l_upper_limit and l_acc_rate_pp_1 > 0 THEN
1302 l_acc_rate_pp_2 := 0;
1303 ELSE
1304 OPEN csr_get_bands (l_year_2);
1305 FETCH csr_get_bands INTO l_annual_rate,l_ceiling_2,
1306 l_lower_limit,l_upper_limit,
1307 l_carry_over_2;
1308 IF csr_get_bands%NOTFOUND THEN
1309 CLOSE csr_get_bands;
1310 l_accrual := 0;
1311 P_current_ceiling := 0;
1312 P_current_carry_over := 0;
1313 --
1314 -- Fix for WWBUG 1717601.
1315 -- Removed duplicate close cursor.
1316 --
1317 GOTO exit_out;
1318 ELSE
1319 l_acc_rate_pp_2 := l_annual_rate/P_number_of_periods;
1320 IF l_ceiling_1 is not null THEN
1321 l_ceiling_flag := 'Y';
1322 END IF;
1323 CLOSE csr_get_bands;
1324 END IF;
1325 END IF;
1326 --
1327 --
1328 IF ((l_acc_rate_pp_1 <> l_acc_rate_pp_2) AND
1329 l_acc_rate_pp_2 <> 0 ) THEN
1330 l_temp := trunc(ABS(months_between(P_continuous_service_date,
1331 p_accrual_calc_p_end_date))/12) * 12 ;
1332 l_band_change_date := ADD_MONTHS(P_continuous_service_date,l_temp);
1333 ELSE
1334 l_band_change_date := (p_accrual_calc_p_end_date + 2);
1335 END IF;
1336 --
1337 -- Set output params.
1338 --
1339 IF l_ceiling_2 = 0 OR l_ceiling_2 is null
1340 THEN
1341 P_current_ceiling := l_ceiling_1;
1342 ELSE
1343 P_current_ceiling := l_ceiling_2;
1344 END IF;
1345 --
1346 IF l_carry_over_2 = 0 OR l_carry_over_2 is null
1347 THEN
1348 P_current_carry_over := l_carry_over_1;
1349 ELSE
1350 P_current_carry_over := l_carry_over_2;
1351 END IF;
1352 --
1353 OPEN csr_all_asg_status;
1354 FETCH csr_all_asg_status into l_asg_eff_start_date,
1355 l_asg_eff_end_date,
1356 l_asg_status;
1357 --
1358 -- Check if calc method should use ceiling calculation or Non-ceiling
1359 -- calculation. For simplicity if there is any asg. status change then
1360 -- ceiling calculation method is used.
1361 --
1362 IF l_ceiling_flag = 'N'
1363 and (p_first_p_end_date >= l_asg_eff_start_date
1364 and p_accrual_calc_p_end_date <= l_asg_eff_end_date
1365 and l_asg_status = 'ACTIVE_ASSIGN') THEN
1366 --
1367 -- Non Ceiling Calc
1368 --
1369 OPEN csr_get_period(P_Payroll_id, l_band_change_date);
1370 FETCH csr_get_period INTO l_curr_p_num,l_curr_p_stdt,l_curr_p_endt;
1371 IF csr_get_period%NOTFOUND THEN
1372 CLOSE csr_get_period;
1373 return ;
1374 END IF;
1375 CLOSE csr_get_period;
1376 --
1377 --
1378 --
1379 if l_curr_p_num = 1 AND
1380 p_accrual_calc_p_end_date < l_band_change_date
1381 then
1382 l_curr_p_num := P_number_of_periods;
1383 elsif p_accrual_calc_p_end_date >= l_band_change_date then
1384 l_curr_p_num := l_curr_p_num - 1;
1385 else
1386 l_curr_p_num := P_accrual_calc_P_number;
1387 end if;
1388 --
1389 -- Entitlement from first period to Band change date.
1390 --
1391 l_accrual := l_acc_rate_pp_1 * (l_curr_p_num - (p_first_calc_P_number - 1));
1392
1393 --
1394 -- Entitlement from Band change date to Calc. date
1395 --
1396 IF p_accrual_calc_p_end_date >= l_band_change_date THEN
1397 l_accrual := l_accrual + l_acc_rate_pp_2 * (P_accrual_calc_P_number - l_curr_p_num);
1398
1399 END IF;
1400 ELSE
1401 --
1402 -- Ceiling Calc
1403 --
1404 OPEN csr_get_time_periods;
1405 l_running_total := 0;
1406 l_curr_p_acc := 0;
1407 LOOP
1408 FETCH csr_get_time_periods into l_start_Date,
1409 l_end_date,
1410 l_period_num;
1411 EXIT WHEN csr_get_time_periods%NOTFOUND;
1412 IF l_period_num > P_accrual_calc_P_number then
1413 EXIT;
1414 END IF;
1415 --
1416 -- Check for Any assignment status change in the current period
1417 --
1418 l_mult_factor := 1;
1419 l_working_day := 0;
1420 l_unpaid_day := 0;
1421 l_vac_taken := 0;
1422 l_prev_end_date := l_asg_eff_end_date;
1423 --
1424 IF l_asg_eff_end_date between l_start_Date and l_end_date
1425 THEN
1426 IF l_asg_status <> 'ACTIVE_ASSIGN' THEN
1427 l_unpaid_day := per_views_pkg.per_get_working_days(l_start_Date,
1428 l_asg_eff_end_date);
1429 END IF;
1430 --
1431 --
1432 LOOP
1433 l_prev_end_date := l_asg_eff_end_date;
1434 FETCH csr_all_asg_status into l_asg_eff_start_date,
1435 l_asg_eff_end_date,
1436 l_asg_status;
1437 IF csr_all_asg_status%NOTFOUND THEN
1438 CLOSE csr_all_asg_status;
1439 EXIT;
1440 ELSIF l_asg_status <> 'ACTIVE_ASSIGN' and
1441 l_asg_eff_start_date <= l_end_date
1442 THEN
1443 l_unpaid_day := l_unpaid_day +
1444 per_views_pkg.per_get_working_days(l_asg_eff_start_date,
1445 least(l_end_date,l_asg_eff_end_date));
1446 END IF;
1447 EXIT WHEN l_asg_eff_end_date > l_end_date;
1448 END LOOP;
1449 --
1450 --
1451 ELSIF csr_all_asg_status%ISOPEN and l_asg_status <> 'ACTIVE_ASSIGN' THEN
1452 l_mult_factor := 0;
1453 ELSIF NOT (csr_all_asg_status%ISOPEN ) THEN
1454 l_mult_factor := 0;
1455 ELSE
1456 l_mult_factor := 1;
1457 END IF;
1458 --
1459 --
1460 IF l_unpaid_day <> 0 THEN
1461 l_working_day := per_views_pkg.per_get_working_days(l_start_Date,l_end_date);
1462 IF l_working_day = l_unpaid_day THEN
1463 l_mult_factor := 0;
1464 ELSE
1465 l_mult_factor := (1 - (l_unpaid_day/l_working_day));
1466 END IF;
1467 END IF;
1468 --
1469 -- Find out vacation and carry over if the method is ceiling
1470 --
1471 IF l_ceiling_flag = 'Y' THEN
1472 OPEN csr_calc_accrual(l_start_Date, l_end_date,
1473 P_assignment_id, P_plan_id);
1474 FETCH csr_calc_accrual INTO l_vac_taken;
1475 IF csr_calc_accrual%NOTFOUND or l_vac_taken is null THEN
1476 l_vac_taken := 0;
1477 END IF;
1478 CLOSE csr_calc_accrual;
1479 END IF;
1480 --
1481 -- Multiply the Accrual rate for the current band and Multiplication
1482 -- Factor to get current period accrual.
1483 --
1484 IF (l_band_change_date between l_start_Date and l_end_date)
1485 OR ( l_band_change_date < l_end_date)
1486 THEN
1487 l_curr_p_acc := l_acc_rate_pp_2 * l_mult_factor;
1488 l_curr_ceiling := l_ceiling_2;
1489 ELSE
1490 l_curr_p_acc := l_acc_rate_pp_1 * l_mult_factor;
1491 l_curr_ceiling := l_ceiling_1;
1492 END IF;
1493 --
1494 --
1495 -- Check for ceiling limits
1496 --
1497 IF l_ceiling_flag = 'Y' THEN
1498 l_running_total := l_running_total + l_vac_taken + l_curr_p_acc;
1499 IF l_running_total > l_curr_ceiling THEN
1500 IF (l_running_total - l_curr_ceiling) < l_curr_p_acc
1501 THEN
1502 l_temp := (l_curr_p_acc -
1503 (l_running_total - l_curr_ceiling));
1504 l_accrual := l_accrual + l_temp;
1505 l_running_total := l_running_total + l_temp;
1506 END IF;
1507 l_running_total := l_running_total - l_curr_p_acc;
1508 ELSE
1509 l_accrual := l_accrual + l_curr_p_acc;
1510 END IF;
1511 ELSE
1512 l_accrual := l_accrual + l_curr_p_acc;
1513 END IF;
1514 --
1515 --
1516 END LOOP;
1517 --
1518 CLOSE csr_get_time_periods;
1519 --
1520 END IF;
1521 --
1522 --
1523 IF l_accrual is null THEN
1524 l_accrual := 0;
1525 END IF;
1526 --
1527 <<exit_out>>
1528 P_Plan_accrual := l_accrual;
1529 --
1530 --
1531 END PER_GET_ACCRUAL_FOR_PLAN;
1532
1533 -- -------------------------------------------------------------------------
1534 -- --------------------< PER_GET_WORKING_DAYS >-----------------------------
1535 -- -------------------------------------------------------------------------
1536 FUNCTION PER_GET_WORKING_DAYS
1537 (P_start_date date,
1538 P_end_date date )
1539 RETURN NUMBER is
1540 l_total_days NUMBER := 0;
1541 l_curr_date DATE := NULL;
1542 l_curr_day VARCHAR2(3) := NULL;
1543 --
1544 BEGIN
1545 --
1546 -- Check for valid range
1547 IF p_start_date > P_end_date THEN
1548 RETURN l_total_days;
1549 END IF;
1550 --
1551 l_curr_date := P_start_date;
1552 LOOP
1553 -- l_curr_day := TO_CHAR(l_curr_date,'YYYY/MM/DD'); -- bug6706398
1554 l_curr_day := TO_CHAR(l_curr_date,'DY'); -- bug 6706398
1555
1556
1557 IF UPPER(l_curr_day) in ('MON', 'TUE', 'WED', 'THU', 'FRI') THEN
1558 l_total_days := l_total_days + 1;
1559 END IF;
1560 l_curr_date := l_curr_date + 1;
1561 EXIT WHEN l_curr_date > P_end_date;
1562 END LOOP;
1563 --
1564 RETURN l_total_days;
1565 --
1566 END PER_GET_WORKING_DAYS;
1567
1568 -- -------------------------------------------------------------------------
1569 -- --------------------< PER_GET_NET_ACCRUAL >------------------------------
1570 -- -------------------------------------------------------------------------
1571 FUNCTION PER_GET_NET_ACCRUAL
1572 ( P_assignment_id number,
1573 P_calculation_date date,
1574 P_plan_id number default null,
1575 P_plan_category Varchar2 default null)
1576 RETURN NUMBER is
1577 --
1578 --
1579 -- Function calls the actual proc. which will calc. net accrual and pass back
1580 -- the details.In formula we will call functions so this will be the cover
1581 -- function to call the proc.
1582 --
1583 l_accrual number := 0;
1584 --
1585 c_date date := P_calculation_date;
1586 n1 number;
1587 n2 number;
1588 n3 number;
1589 n4 number;
1590 d1 date;
1591 d2 date;
1592 d3 date;
1593 d4 date;
1594 d5 date;
1595 d6 date;
1596 d7 date;
1597 --
1598 BEGIN
1599 --
1600 per_views_pkg.per_net_accruals(
1601 P_assignment_id => P_assignment_id,
1602 P_calculation_date => c_date,
1603 P_plan_id => P_plan_id,
1604 P_plan_category => P_plan_category,
1605 P_mode => 'N',
1606 P_accrual => n4,
1607 P_net_accrual => l_accrual,
1608 P_payroll_id => n1,
1609 P_first_period_start => d1,
1610 P_first_period_end => d2,
1611 P_last_period_start => d3,
1612 P_last_period_end => d4,
1613 P_cont_service_date => d5,
1614 P_start_date => d6,
1615 P_end_date => d7,
1616 P_current_ceiling => n2,
1617 P_current_carry_over => n3);
1618 --
1619 IF l_accrual is null
1620 THEN
1621 l_accrual := 0;
1622 END IF;
1623 --
1624 RETURN(l_accrual);
1625 --
1626 END PER_GET_NET_ACCRUAL;
1627
1628 -- -------------------------------------------------------------------------
1629 -- --------------------< PER_NET_ACCRUALS >---------------------------------
1630 -- -------------------------------------------------------------------------
1631 PROCEDURE PER_NET_ACCRUALS
1632 (P_assignment_id IN number,
1633 P_calculation_date IN OUT NOCOPY date,
1634 P_plan_id IN number DEFAULT NULL,
1635 P_plan_category IN varchar2 DEFAULT NULL,
1636 P_mode IN varchar2 DEFAULT 'N',
1637 P_accrual IN OUT NOCOPY number,
1638 P_net_accrual OUT NOCOPY number,
1639 P_payroll_id IN OUT NOCOPY number,
1640 P_first_period_start IN OUT NOCOPY date,
1641 P_first_period_end IN OUT NOCOPY date,
1642 P_last_period_start IN OUT NOCOPY date,
1643 P_last_period_end IN OUT NOCOPY date,
1644 P_cont_service_date OUT NOCOPY date,
1645 P_start_date IN OUT NOCOPY date,
1646 P_end_date IN OUT NOCOPY date,
1647 P_current_ceiling OUT NOCOPY number,
1648 P_current_carry_over OUT NOCOPY number) IS
1649 --
1650 --
1651 l_taken number := 0;
1652 l_temp number := 0;
1653 --
1654 BEGIN
1655 --
1656 -- Get vaction accrued
1657 --
1658 per_views_pkg.per_accrual_calc_detail(
1659 P_assignment_id => P_assignment_id,
1660 P_calculation_date => P_calculation_date,
1661 P_plan_id => P_plan_id,
1662 P_plan_category => P_plan_category,
1663 P_mode => P_mode,
1664 P_accrual => P_accrual,
1665 P_payroll_id => P_payroll_id,
1666 P_first_period_start => P_first_period_start,
1667 P_first_period_end => P_first_period_end,
1668 P_last_period_start => P_last_period_start,
1669 P_last_period_end => P_last_period_end,
1670 P_cont_service_date => P_cont_service_date,
1671 P_start_date => P_start_date,
1672 P_end_date => P_end_date,
1673 P_current_ceiling => P_current_ceiling,
1674 P_current_carry_over => P_current_carry_over);
1675 --
1676 -- Get vac taken purchase etc using net Calc rules.
1677 --
1678 OPEN csr_calc_accrual(P_start_Date, P_end_date,
1679 P_assignment_id, P_plan_id);
1680 FETCH csr_calc_accrual INTO l_taken;
1681 IF csr_calc_accrual%NOTFOUND or
1682 l_taken is null
1683 THEN
1684 l_taken := 0;
1685 END IF;
1686 CLOSE csr_calc_accrual;
1687 --
1688 --
1689 P_net_accrual := ROUND((P_accrual + l_taken),3);
1690 --
1691 -- if mode is carry over then return next years first period start
1692 -- and end dates in P_start_date nad P_end_date params.
1693 --
1694 IF P_mode = 'C'
1695 THEN
1696 OPEN csr_get_period(p_payroll_id,(P_last_period_end +1));
1697 FETCH csr_get_period into l_temp,P_start_date,P_end_date;
1698 IF csr_get_period%NOTFOUND THEN
1699 CLOSE csr_get_period;
1700 return ;
1701 END IF;
1702 CLOSE csr_get_period;
1703 END IF;
1704 --
1705 --
1706 END PER_NET_ACCRUALS;
1707
1708 END PER_VIEWS_PKG ;