1 PACKAGE BODY pqp_gb_css_daily_absences AS
2 /* $Header: pqgbdcss.pkb 120.1 2006/02/27 03:49:39 rvishwan noship $ */
3 -----------------
4
5 e_novalue EXCEPTION;
6 -----------------
7
8 g_nested_level NUMBER:= 0;
9 g_package_name VARCHAR2(31) := 'pqp_gb_css_daily_absences.' ;
10 -- g_plan_information pqp_absval_pkg.rec_plan_information ;
11 g_debug BOOLEAN ;
12 -- Cache for rounding of factors
13 g_pt_entitl_rounding_type VARCHAR2(10):=null;
14 g_pt_rounding_precision pqp_gap_daily_absences.duration%TYPE;
15 g_ft_rounding_precision pqp_gap_daily_absences.duration%TYPE;
16 g_ft_entitl_rounding_type VARCHAR2(10):=null ;
17 g_open_ended_no_pay_days NUMBER;
18
19 PROCEDURE debug
20 (p_trace_message IN VARCHAR2
21 ,p_trace_location IN NUMBER
22 )
23 IS
24 BEGIN
25 pqp_utilities.debug(p_trace_message,p_trace_location);
26 END debug;
27 --
28 --
29 --
30 PROCEDURE debug
31 (p_trace_number IN NUMBER )
32 IS
33 BEGIN
34 debug(fnd_number.number_to_canonical(p_trace_number));
35 END debug;
36 --
37 --
38 --
39 PROCEDURE debug
40 (p_trace_date IN DATE )
41 IS
42 BEGIN
43 debug(fnd_date.date_to_canonical(p_trace_date));
44 END debug;
45 --
46 --
47 --
48 PROCEDURE debug_enter
49 (p_proc_name IN VARCHAR2
50 ,p_trace_on IN VARCHAR2
51 )
52 IS
53 -- l_trace_options VARCHAR2(200);
54 BEGIN
55 pqp_utilities.debug_enter(p_proc_name,p_trace_on);
56 END debug_enter;
57 --
58 --
59 --
60 PROCEDURE debug_exit
61 (p_proc_name IN VARCHAR2
62 ,p_trace_off IN VARCHAR2
63 )
64 IS
65 BEGIN
66 pqp_utilities.debug_exit(p_proc_name,p_trace_off);
67 END debug_exit;
68
69 -- This Procedure is called from pqp_absval_pkg.create_absence_plan_details
70 -- The logic of derving Review Dates and calculating the Remaining
71 -- entitlement is coded in this.
72
73 PROCEDURE create_absence_plan_details
74 ( p_assignment_id IN NUMBER
75 ,p_business_group_id IN NUMBER
76 ,p_absence_id IN NUMBER
77 ,p_pl_id IN NUMBER
78 ,p_pl_typ_id IN NUMBER
79 ,p_create_start_date IN DATE
80 ,p_create_end_date IN DATE
81 ,p_entitlements IN pqp_absval_pkg.t_entitlements
82 ,p_plan_information IN pqp_absval_pkg.rec_plan_information
83 ,p_entitlements_remaining OUT NOCOPY pqp_absval_pkg.t_entitlements
84 ,p_entitlement_UOM OUT NOCOPY VARCHAR2
85 ,p_working_days_per_week OUT NOCOPY NUMBER
86 ,p_fte OUT NOCOPY NUMBER
87 ) IS
88
89 l_error_code fnd_new_messages.message_number%TYPE := 0 ;
90 l_proc_name VARCHAR2(61) := g_package_name||'create_absence_plan_details';
91 l_proc_step NUMBER(20,10);
92 l_nopay_review_date DATE;
93 l_halfpay_review_date DATE;
94 l_dual_rolling_period NUMBER;
95 l_secondary_rolling_period NUMBER;
96 l_total_entitlements NUMBER := 0 ;
97 l_total_remaining NUMBER ;
98 l_fullpay_duration NUMBER ;
99 l_error_message fnd_new_messages.message_text%TYPE;
100 l_working_days_in_week
101 pqp_gap_daily_absences.working_days_per_week%TYPE ;
102 i NUMBER ;
103
104 l_entitlements_remaining_nc pqp_absval_pkg.t_entitlements
105 := p_entitlements_remaining;
106 l_entitlement_UOM_nc VARCHAR2(30) := p_entitlement_UOM ;
107 l_working_days_per_week_nc pqp_gap_daily_absences.working_days_per_week%TYPE
108 := p_working_days_per_week;
109 l_standard_work_days_in_week
110 pqp_gap_daily_absences.working_days_per_week%TYPE ;
111 l_fulltime BOOLEAN ;
112
113 BEGIN
114
115 g_debug := hr_utility.debug_enabled ;
116
117 IF g_debug THEN
118 debug_enter(l_proc_name) ;
119 debug('p_assignment_id:'||p_assignment_id);
120 debug('p_business_group_id:'||p_business_group_id);
121 debug('p_absence_id:'||p_absence_id);
122 debug('p_pl_id:'||p_pl_id);
123 debug('p_pl_typ_id:'||p_pl_typ_id);
124 debug('p_start_date:'||p_create_start_date);
125 debug('p_end_date:'||p_create_end_date);
126 END IF ;
127
128
129 --Set the global rounding factor cache if the values are not already set
130
131 IF g_ft_entitl_rounding_type is null THEN
132 PQP_GB_OSP_FUNCTIONS.set_osp_omp_rounding_factors
133 (p_pl_id => p_pl_id
134 ,p_pt_entitl_rounding_type => g_pt_entitl_rounding_type
135 ,p_pt_rounding_precision => g_pt_rounding_precision
136 ,p_ft_entitl_rounding_type => g_ft_entitl_rounding_type
137 ,p_ft_rounding_precision => g_ft_rounding_precision
138 );
139 END IF;
140
141 IF g_debug THEN
142 debug('p_pt_entitl_rounding_type' || g_pt_entitl_rounding_type);
143 debug('p_pt_rounding_precision' , g_pt_rounding_precision);
144 debug('p_ft_entitl_rounding_type' || g_ft_entitl_rounding_type);
145 debug('p_ft_rounding_precision' , g_ft_rounding_precision);
146 debug(l_proc_name, 15);
147 END IF;
148
149
150 -- The Input parameters we have are the Entitlements and Plan Information
151 -- we need to work out the Review Dates and calculate the entitlements
152 -- Remaining
153 -- First loop through the entitlements and determine the Entitlements
154 -- Over a 4 year period duration.
155 -- then call get_review_date to derive NoPAY Review Date i.e. the Date NoPay
156 -- Starts and Half Pay Review Date i.e. the date Half Pay Starts
157 -- Once these 2 dates are determined along with the duration check for
158 -- the work pattern attached and the number of working days in a week.
159 -- If there is no work pattern attached or workpattern with working days
160 -- morethan 5 then mark that as a Full Time employee i.e. the entitlements
161 -- and Payments both will be set to Calendar ( we set entitlements only
162 -- to calendar here.Payment will be picked up from scheme info )
163 -- If the employee is Part Time i.e. the number of working days in the week
164 -- are less than 5 then mark his entitlements as Working Days.
165 -- Calculate the BAND1 and BANd2 days and populate the structure
166 -- p_entitlements_remaining.
167
168
169 i := p_entitlements.FIRST;
170
171 WHILE i IS NOT NULL
172 LOOP
173
174 IF g_debug THEN
175 debug('i:'||i);
176 END IF ;
177 l_total_entitlements := l_total_entitlements +
178 p_entitlements(i).entitlement ;
179 i := p_entitlements.NEXT(i);
180 END LOOP ;
181
182
183 l_proc_step := 10 ;
184
185 l_dual_rolling_period :=fnd_number.canonical_to_number(
186 p_plan_information.dual_rolling_period_duration
187 );
188
189 l_proc_step := 20 ;
190
191
192 l_working_days_in_week :=
193 pqp_schedule_calculation_pkg.
194 get_working_days_in_week
195 (
196 p_assignment_id => p_assignment_id
197 ,p_business_group_id => p_business_group_id
198 ,p_effective_date => p_create_start_date
199 ,p_default_wp =>
200 p_plan_information.default_work_pattern_name
201 ) ;
202
203 l_standard_work_days_in_week :=
204 pqp_schedule_calculation_pkg.get_working_days_in_week (
205 p_assignment_id => p_assignment_id
206 ,p_business_group_id => p_business_group_id
207 ,p_effective_date => p_create_start_date
208 ,p_override_wp =>
209 p_plan_information.default_work_pattern_name
210 ) ;
211
212 l_proc_step := 50 ;
213 IF g_debug THEN
214 debug(' No of Working Days in Week:'||l_working_days_in_week);
215 END IF;
216
217 IF NVL(l_working_days_in_week,l_standard_work_days_in_week) >=
218 l_standard_work_days_in_week THEN
219 p_entitlement_uom := 'C' ;
220 p_working_days_per_week := 7 ;
221 l_fulltime := TRUE ;
222 p_fte:=1;
223 ELSE
224 p_entitlement_uom := 'W' ;
225 p_fte:= l_working_days_in_week/l_standard_work_days_in_week;
226 p_working_days_per_week := l_working_days_in_week ;
227 l_fulltime := FALSE ;
228 END IF;
229
230 l_proc_step := 60 ;
231 IF g_debug THEN
232 debug(' No of Working Days in week:'||p_working_days_per_week);
233 END IF;
234
235
236 l_nopay_review_date :=
237 get_review_date(
238 p_absence_start_date => trunc(p_create_start_date)
239 ,p_absence_end_date => trunc(p_create_end_date)
240 ,p_assignment_id => p_assignment_id
241 ,p_business_group_id => p_business_group_id
242 ,p_pl_typ_id => p_pl_typ_id
243 ,p_scheme_period_duration => l_dual_rolling_period
244 ,p_scheme_period_type => p_plan_information.scheme_period_type
245 ,p_scheme_period_uom => p_plan_information.dual_rolling_period_uom
246 ,p_total_entitlement => l_total_entitlements
247 ,p_total_remaining => l_total_remaining
248 ,p_4_year_rolling_period => TRUE
249 ,p_working_days_in_week => p_working_days_per_week
250 ,p_standard_work_days_in_week => l_standard_work_days_in_week
251 ,p_fulltime => l_fulltime
252 ,p_lookup_type => p_plan_information.plan_types_to_extend_period
253 ) ;
254
255 l_proc_step := 30 ;
256 IF g_debug THEN
257 debug(' 4-Year Review Date:'||l_nopay_review_date );
258 debug(' Remaining Entitlements:'||l_total_remaining);
259 END IF ;
260
261
262 l_halfpay_review_date :=
263 get_review_date(
264 p_absence_start_date => trunc(p_create_start_date)
265 ,p_absence_end_date => trunc(p_create_end_date)
266 ,p_assignment_id => p_assignment_id
267 ,p_business_group_id => p_business_group_id
268 ,p_pl_typ_id => p_pl_typ_id
269 ,p_scheme_period_duration => p_plan_information.scheme_period_duration
270 ,p_scheme_period_type => p_plan_information.scheme_period_type
271 ,p_scheme_period_uom => p_plan_information.scheme_period_uom
272 ,p_total_entitlement => p_entitlements(1).entitlement
273 ,p_total_remaining => l_fullpay_duration
274 ,p_4_year_rolling_period => FALSE
275 ,p_working_days_in_week => p_working_days_per_week
276 ,p_standard_work_days_in_week => l_standard_work_days_in_week
277 ,p_fulltime => l_fulltime
278 ,p_lookup_type => p_plan_information.plan_types_to_extend_period
279 ) ;
280 l_proc_step := 40 ;
281 IF g_debug THEN
282 debug(' 1-Year Review Date:'||l_halfpay_review_date);
283 debug(' Remaining Entitlements:'||l_fullpay_duration);
284 END IF ;
285
286 /* l_working_days_in_week :=
287 pqp_schedule_calculation_pkg.
288 get_working_days_in_week
289 (
290 p_assignment_id => p_assignment_id
291 ,p_business_group_id => p_business_group_id
292 ,p_effective_date => p_create_start_date
293 ) ;
294
295 l_proc_step := 50 ;
296 IF g_debug THEN
297 debug(' No of Working Days in Week:'||l_working_days_in_week);
298 END IF;
299
300 IF NVL(l_working_days_in_week,5) >= 5 THEN
301 p_entitlement_uom := 'C' ;
302 p_working_days_per_week := 7 ;
303 ELSE
304 p_entitlement_uom := 'W' ;
305 p_working_days_per_week := l_working_days_in_week ;
306 END IF;
307
308 l_proc_step := 60 ;
309 IF g_debug THEN
310 debug(' No of Working Days in week:'||p_working_days_per_week);
311 END IF;
312
313 */
314
315 -- Look at deriving BAND1 Days and BAND2 Days logic again
316 -- try to get it from dates rather than the existing procedure
317
318 IF l_fullpay_duration > l_total_remaining THEN
319 l_fullpay_duration := l_total_remaining ;
320 END IF;
321
322
323 l_proc_step := 70 ;
324
325
326 /*IF p_entitlement_uom = 'W' THEN
327
328 l_fullpay_duration := FLOOR(l_fullpay_duration *
329 (l_working_days_in_week/7)
330 );
331 -- Hard Coded 7..try to generalize it.
332 l_total_remaining := FLOOR(l_total_remaining*
333 (l_working_days_in_week/7)
334 );
335
336 IF g_debug THEN
337 debug(' Full Pay Duration :'||l_fullpay_duration);
338 debug(' Total Duration :'||l_total_remaining);
339 END IF;
340
341 END IF; */
342
343
344 IF g_debug THEN
345 debug('Band1 :'||l_fullpay_duration);
346 debug('l_total_duration:'||l_total_remaining);
347 END IF;
348
349
350 p_entitlements_remaining(1).band := 'BAND1' ;
351 p_entitlements_remaining(1).entitlement := l_fullpay_duration ;
352 p_entitlements_remaining(2).band := 'BAND2' ;
353 p_entitlements_remaining(2).entitlement := l_total_remaining
354 - l_fullpay_duration ;
355
356
357 IF g_debug THEN
358 debug_exit(l_proc_name) ;
359 END IF;
360 EXCEPTION
361 WHEN OTHERS THEN
362 p_entitlements_remaining := l_entitlements_remaining_nc ;
363 p_entitlement_UOM := l_entitlement_UOM_nc ;
364 p_working_days_per_week := l_working_days_per_week_nc ;
365
366 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
367 pqp_utilities.debug_others
368 (l_proc_name
369 ,l_proc_step
370 );
371 IF g_debug THEN
372 debug('Leaving: '||l_proc_name,-999);
373 END IF;
374 fnd_message.raise_error;
375 ELSE
376 RAISE;
377 END IF;
378
379 END create_absence_plan_details ;
380
381
382
383 --4 Years Rolling Period Calculation.
384 --This process will calculate the date an employee goes onto 'Unpaid Sickness'
385 --1. Go back 4 years from the absence Start Date.
386 --2. Identify any 'excluded' days within the 4 year period and
387 -- extend the 4 year period by this number of days.
388 --3. Identify if any 'excluded' days fall within the extended period.
389 -- If so, further extend the 4 year period by this number of days.
390 -- Repeat this process until all 'excluded' days are identified
391 --4. Count total days paid at Full Pay or Half Pay within period
392 -- up to absence Start Date.
393 --5. Subtract these days from 365
394 --6. If balance <= 0 Stop. The full duration of the absence
395 -- should be unpaid.
396 --7. If balance is >0 add number of days to Start Date to get Review Date1
397 --8. Go back 4 years from Review Date 1
398 --9. Identify any 'excluded' days within the 4 year period and extend
399 -- the 4 year period by this number of days.
400 --10. Identify if any 'excluded' days fall within the extended period.
401 -- If so, further extend the 4 year period by this number of days.
402 -- Repeat this process until all 'excluded' days are identified
403 --11. Count total days paid at Full Pay or Half Pay within period up
404 -- to Review Date 1 (this will now include the current absence days)
405 --12. Subtract these days from 365
406 --13. If balance <= 0 Stop.
407 --14. If balance is >0 add number of days to Start Date to get Review Date 2
408 --15. Repeat steps 8-14 for Review Date 2 and any additional Review Dates
409 -- until the balance is = 0.
410 --16. This final Review Date is the date the employee is due
411 -- to start Unpaid Sick leave
412
413 -- The same logic applies to 1-Year rolling period
414 -- and the Half Pay Start date is also calculated using the similar logic
415 -- with only change in the extending the rolling period part.
416
417 FUNCTION get_review_date ( p_absence_start_date IN DATE
418 ,p_absence_end_date IN DATE
419 ,p_assignment_id IN NUMBER
420 ,p_business_group_id IN NUMBER
421 ,p_pl_typ_id IN NUMBER
422 ,p_scheme_period_duration IN NUMBER
423 ,p_scheme_period_type IN VARCHAR2
424 ,p_scheme_period_uom IN VARCHAR2
425 ,p_total_entitlement IN NUMBER
426 ,p_total_remaining IN OUT NOCOPY NUMBER
427 ,p_4_year_rolling_period IN BOOLEAN--TRUE for 4year
428 -- PT Changes
429 ,p_working_days_in_week IN NUMBER
430 ,p_standard_work_days_in_week IN NUMBER
431 ,p_fulltime IN BOOLEAN
432 ,p_lookup_type IN VARCHAR2
433 ) RETURN DATE IS
434
435 l_absences_taken pqp_absval_pkg.t_entitlements ;
436 l_rolling_end_date DATE := p_absence_start_date ;
437 l_rolling_start_date DATE ;
438 l_total_entitlement NUMBER := 0 ; --p_total_entitlement ; -- PT Changes
439 l_total_absences_taken NUMBER := 0 ;
440 l_work_pattern_count NUMBER ;
441 l_remaining_entitlements NUMBER ;
442 l_proc_name VARCHAR2(61) := g_package_name||'get_review_date';
443 l_proc_step NUMBER(20,10) ;
444 l_total_remaining_nc NUMBER ;
445 l_error_code fnd_new_messages.message_number%TYPE ;
446 l_error_message fnd_new_messages.message_text%TYPE ;
447 i NUMBER ;
448 l_decimal_part NUMBER ;
449
450 BEGIN
451
452 g_debug := hr_utility.debug_enabled ;
453
454 IF g_debug THEN
455 debug_enter(l_proc_name);
456 debug('p_absence_start_date:'||p_absence_start_date);
457 debug('p_absence_end_date:'||p_absence_end_date);
458 debug('p_assignment_id:'||p_assignment_id);
459 debug('p_business_group_id:'||p_business_group_id);
460 debug('p_pl_typ_id:'||p_pl_typ_id);
461 debug('p_scheme_period_duration:'||p_scheme_period_duration);
462 debug('p_scheme_period_type:'||p_scheme_period_type);
463 debug('p_scheme_period_uom:'||p_scheme_period_uom);
464 debug('p_total_entitlement:'||p_total_entitlement);
465 END IF;
466
467 -- the logic followed to acheive the steps described above.
468 -- First rollback by the duration that is passed
469 -- determining the rolling start including the extension part is
470 -- coded in get_rolling_start_date
471
472 -- Now need to calcualte the remaining entitlements
473 -- First calculate the entitlements used and deduct from
474 -- the entitlements to get the remaining
475 -- now add the remaining to the start date will get a date letus call
476 -- Review Date1
477 -- what we did effectively in the above step is
478 -- we have added the remaining entitlements and derived a date. note that
479 -- these days are not processed yet so they are not there in the database
480 -- they are simply projected.so add the duration projected to the variable
481 -- absences taken.
482 -- 01-jan-00 30 01-jan-04
483 --- |-----------|--|---------------------|
484 -- let us say that there were 30 absences taken in the rolling period
485 -- ( dont look at it whether it is 4 year or 1 year. logic remains same)
486 -- at this point entitlements remaining in 4 years = 365 - 30 = 335
487 -- now add this to absence start date = 01-jan-04 + 335 = 01-dec-04
488 -- now when u loop through we should go back from 01-dec-04 to check the
489 -- entitlements remaining. but the figure 335 is not there in the database
490 -- yet.so add this 335 to absences taken so = 365.
491 -- now on 01-dec-04 the entitlements - absneces taken = 0
492 -- this is the review date.
493
494
495 -- PT Changes
496 -- Converting the entitlements into Weeks
497 -- For Full timers keeping the faction also
498 -- For Part timers round it down.
499 IF p_fulltime THEN
500 l_total_entitlement := p_total_entitlement/7 ;
501 ELSE
502 l_total_entitlement := (p_total_entitlement/7) ;
503 END IF;
504
505 p_total_remaining := 0 ;
506
507 loop
508
509 -- get_rolling_start_date should be = pqp_absbal_pkg.get_scheme_start_date
510 -- and adjust_scheme_start_date. this could be a new function???
511 -- to use this for both 4-year and 1-year pass some variable for
512 -- nopay days logic ( BOOLEAN variable)
513 l_proc_step := 10 ;
514 l_rolling_start_date :=
515 get_rolling_start_date(
516 p_rolling_end_date => l_rolling_end_date
517 ,p_scheme_period_duration => p_scheme_period_duration
518 ,p_assignment_id => p_assignment_id
519 ,p_business_group_id => p_business_group_id
520 ,p_scheme_period_type => p_scheme_period_type
521 ,p_scheme_period_uom => p_scheme_period_uom
522 ,p_pl_typ_id => p_pl_typ_id
523 ,p_4_year_rolling_period => p_4_year_rolling_period
524 ,p_lookup_type => p_lookup_type
525 ) ;
526 l_proc_step := 20 ;
527 IF g_debug THEN
528 debug('Rolling Start date:'||l_rolling_start_date);
529 debug('Eligible Days:'||l_total_entitlement);
530 END IF;
531
532 -- get_paid_absence_days use get_absences_taken in pqp_absval_pkg
533 -- For 4 year scheme sum all the bands returned from that
534 -- For Full Paid only take BAND1
535
536 IF g_debug THEN
537 debug('p_assignment_id:'||p_assignment_id);
538 debug('p_pl_typ_id:'||p_pl_typ_id);
539 debug('l_rolling_start_date:'||l_rolling_start_date);
540 debug('l_rolling_end_date:'||l_rolling_end_date);
541 END IF ;
542 l_absences_taken.DELETE;
543 pqp_absval_pkg.get_absences_taken
544 (p_assignment_id => p_assignment_id
545 ,p_pl_typ_id => p_pl_typ_id
546 ,p_range_from_date => l_rolling_start_date
547 ,p_range_to_date => l_rolling_end_date-1
548 ,p_absences_taken => l_absences_taken
549 ) ;
550 -- The above procedure returns the absneces taken Band wise in
551 -- PL/SQL table. loop through it and sum the required Bands.
552 -- if this proc is called for 4-Year rolling period calcualtion
553 -- sum all the bands i.e. BAND% or NOBAND
554 -- If called from 1-Year Review Date then count only BAND1 days.
555 l_proc_Step := 30 ;
556
557 IF p_4_year_rolling_period THEN
558
559 i := l_absences_taken.FIRST;
560
561 WHILE i IS NOT NULL
562 LOOP
563 l_proc_step := 40 ;
564 IF g_debug THEN
565 debug('Band:'||l_absences_taken(i).band);
566 debug('Absences taken:'||l_absences_taken(i).duration_per_week);
567 END IF;
568
569 IF l_absences_taken(i).band like 'BAND%' OR
570 l_absences_taken(i).band like 'NOBAND%' THEN
571 l_total_absences_taken:=l_total_absences_taken +
572 l_absences_taken(i).duration_per_week;
573 END IF ;
574 i := l_absences_taken.NEXT(i);
575 END LOOP ;
576
577 IF g_debug THEN
578 debug('l_total_absences_taken:'||l_total_absences_taken);
579 END IF;
580 ELSE -- IF p_4_year_rolling_period THEN
581 i := l_absences_taken.FIRST;
582
583 WHILE i IS NOT NULL
584 LOOP
585 l_proc_step := 50 ;
586 IF g_debug THEN
587 debug('Band:'||l_absences_taken(i).band);
588 debug('Absences taken:'||l_absences_taken(i).duration_per_week);
589 END IF;
590 IF l_absences_taken(i).band = 'BAND1' THEN
591 l_total_absences_taken:=l_absences_taken(i).duration_per_week;
592 END IF;
593 i := l_absences_taken.NEXT(i);
594 END LOOP ;
595
596
597 IF g_debug THEN
598 debug('l_total_absences_taken BAND1:'||l_total_absences_taken);
599 END IF;
600
601 END IF; --IF p_4_year_rolling_period THEN
602
603 l_proc_step := 60 ;
604
605 -- PT Changes
606 -- Here need to convert the total entitlements into weeks
607 -- then get the absences taken also in weeks
608 -- deduct and convert that into days by multiplying by the
609 -- no of working days in week or 7
610
611 IF g_debug THEN
612 debug('Entitled Weeks :'||l_total_entitlement);
613 END IF ;
614
615 l_remaining_entitlements := ( l_total_entitlement -
616 NVL(l_total_absences_taken,0) ) *
617 p_working_days_in_week ;
618
619 -- Round the Remaining Entitlements first to 2 decimals
620 --l_remaining_entitlements := ROUND(l_remaining_entitlements,2) ;
621
622 --- then round to
623 -- For Full timers always a upper 0.5
624 -- For Part-timers round it to lower 0.5
625 -- 4.4 for PT = 4. or 4.6 = 4.5
626 -- 4.4 for a FT = 4.5 or 4.6 = 5
627
628 /* IF p_fulltime THEN
629 -- For FT round to the upper 0.5
630 l_remaining_entitlements :=
631 pqp_utilities.round_value_up_down(
632 p_value_to_round => l_remaining_entitlements
633 ,p_base_value => g_ft_rounding_precision
634 ,p_rounding_type => g_ft_entitl_rounding_type
635 ) ;
636
637 ELSE -- IF p_fulltime THEN
638 -- For PT round to the lower 0.5
639
640 l_remaining_entitlements :=
641 pqp_utilities.round_value_up_down(
642 p_value_to_round => l_remaining_entitlements
643 ,p_base_value => g_pt_rounding_precision
644 ,p_rounding_type => g_pt_entitl_rounding_type
645 ) ;
646 END IF ; -- IF p_fulltime THEN
647 */
648 IF g_debug THEN
649 debug('Remaining Ent Days :'||l_remaining_entitlements);
650 END IF;
651 -- PT Changes cchappid
652 exit when l_remaining_entitlements <= 0 ;
653
654 --IF l_remaining_entitlements < 0 THEN
655 -- l_remaining_entitlements := 0 ;
656 --END IF;
657 l_proc_step := 70 ;
658 p_total_remaining := p_total_remaining + l_remaining_entitlements ;
659
660 l_total_entitlement := l_total_absences_taken ;
661
662 IF g_debug THEN
663 debug(' Rolling Start Date :'||l_rolling_start_date);
664 END IF ;
665
666
667 -- PT Changes
668 -- Here we need check if FT or PT and add days accrodingly
669 -- From 5 Change it to refer to Default_work_pattern days
670
671 IF p_working_days_in_week < p_standard_work_days_in_week THEN
672 -- call add_working_days function here
673 l_proc_step := 75 ;
674 -- l_rolling_end_date :=
675 -- pqp_schedule_calculation_pkg.add_working_days
676 -- (p_assignment_id => p_assignment_id
677 -- ,p_business_group_id => p_business_group_id
678 -- ,p_date_start => l_rolling_end_date
679 -- ,p_days => l_remaining_entitlements - 1
680 -- ,p_error_code => l_error_code
681 -- ,p_error_message => l_error_message
682 -- ) ;
683 l_rolling_end_date :=
684 pqp_schedule_calculation_pkg.add_working_days_using_one_wp
685 (p_assignment_id => p_assignment_id
686 ,p_business_group_id => p_business_group_id
687 ,p_date_start => l_rolling_end_date
688 ,p_working_days_to_add => l_remaining_entitlements
689 ) ;
690 l_rolling_end_date := l_rolling_end_date + 1 ;
691 ELSE
692 l_proc_step := 80 ;
693 l_rolling_end_date := l_rolling_end_date + l_remaining_entitlements ;
694 END IF;
695
696 IF g_debug THEN
697 debug(' Rolling End Date :'||l_rolling_end_date);
698 END IF ;
699 -- PT Changes
700 -- check if this exit part needs to be commented out
701 -- IF p_absence_end_date < l_rolling_end_date THEN
702 -- l_rolling_end_date := p_absence_end_date ;
703 -- p_total_remaining := l_rolling_end_date - p_absence_start_date + 1 ;
704 -- exit ;
705 -- END IF;
706
707 -- moved this as we shud recalculate the review date
708 -- though the remaining entitlements are 0 but
709 -- absence end date is before the review date
710 -- exit when l_remaining_entitlements <= 0 ;
711 l_total_absences_taken := 0 ;
712 end loop ;
713
714 IF p_fulltime THEN
715 -- For FT round to the upper 0.5
716 p_total_remaining :=
717 pqp_utilities.round_value_up_down(
718 p_value_to_round => p_total_remaining
719 ,p_base_value => g_ft_rounding_precision
720 ,p_rounding_type => g_ft_entitl_rounding_type
721 ) ;
722
723 ELSE -- IF p_fulltime THEN
724 -- For PT round to the lower 0.5
725
726 p_total_remaining :=
727 pqp_utilities.round_value_up_down(
728 p_value_to_round => p_total_remaining
729 ,p_base_value => g_pt_rounding_precision
730 ,p_rounding_type => g_pt_entitl_rounding_type
731 ) ;
732 END IF ; -- IF p_fulltime THEN
733
734 l_proc_step := 90 ;
735 IF g_debug THEN
736 debug('Review Date :'||l_rolling_end_date);
737 debug('Remaining Absence :'||p_total_remaining);
738 debug_exit(l_proc_name) ;
739 END IF ;
740
741 RETURN l_rolling_end_date ;
742
743 EXCEPTION
744 WHEN OTHERS THEN
745 p_total_remaining := l_total_remaining_nc;
746 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
747 pqp_utilities.debug_others
748 (l_proc_name
749 ,l_proc_step
750 );
751 IF g_debug THEN
752 debug('Leaving: '||l_proc_name,-999);
753 END IF;
754 fnd_message.raise_error;
755 ELSE
756 RAISE;
757 END IF;
758
759 END get_review_date ;
760
761
762 -- The function returns the Rolling period Start date,
763 -- based on Rolling period and Rolling End Date.
764
765 FUNCTION get_rolling_start_date ( p_rolling_end_date IN DATE
766 ,p_scheme_period_duration IN NUMBER
767 ,p_assignment_id IN NUMBER
768 ,p_business_group_id IN NUMBER
769 ,p_scheme_period_type IN VARCHAR2
770 ,p_scheme_period_uom IN VARCHAR2
771 ,p_pl_typ_id IN NUMBER
772 ,p_4_year_rolling_period IN BOOLEAN
773 ,p_lookup_type IN VARCHAR2
774 -- lookup type that contains the plan types
775 -- to exendn the rolling period.
776 ) RETURN DATE IS
777 l_period_end_date date := p_rolling_end_date ;
778 l_period_start_date date ;
779 l_no_pay_days number ;
780 l_proc_name VARCHAR2(61) := g_package_name||'get_rolling_start_date';
781 l_proc_step NUMBER(20,10) ;
782 BEGIN
783 g_debug := hr_utility.debug_enabled ;
784 IF g_debug THEN
785 debug_enter(l_proc_name);
786 debug('p_rolling_end_date:'||p_rolling_end_date);
787 debug('p_scheme_period_duration:'||p_scheme_period_duration);
788 debug('p_assignment_id:'||p_assignment_id);
789 debug('p_scheme_period_type:'||p_scheme_period_type);
790 debug('p_scheme_period_uom:'||p_scheme_period_uom);
791 debug('p_pl_typ_id:'||p_pl_typ_id);
792 END IF;
793
794 l_proc_step := 10 ;
795 -- get period end date for the calander period
796 l_period_start_date :=
797 pqp_absval_pkg.get_scheme_start_date(
798 p_assignment_id => p_assignment_id
799 ,p_scheme_period_type => p_scheme_period_type
800 ,p_scheme_period_duration => p_scheme_period_duration
801 ,p_scheme_period_uom => p_scheme_period_uom
802 ,p_fixed_year_start_date => fnd_date.date_to_canonical(
803 p_rolling_end_date)
804 ,p_balance_effective_date => p_rolling_end_date
805 ) ;
806 -- get all no pay days in the period (including sickness + maternity
807 -- + NOPAY absences extend the period end date by no of nopay days
808 -- to find any more nopay days in the extended period.
809 -- loop thru the extended period till there are no more nopay days.
810
811 loop
812 --Was earlier calling get_no_pay_days of the same package
813 l_no_pay_days :=
814 pqp_absval_pkg.get_calendar_days_to_extend(
815 p_period_start_date => l_period_start_date
816 ,p_period_end_date => l_period_end_date
817 ,p_assignment_id => p_assignment_id
818 ,p_business_group_id => p_business_group_id
819 ,p_pl_typ_id => p_pl_typ_id
820 ,p_count_nopay_days => p_4_year_rolling_period
821 ,p_plan_types_lookup_type => p_lookup_type
822 );
823
824 -- For 4 Years
825 -- get_no_pay_days should be split into
826 -- 1.get any nopay days except the sickness ( = CSS )
827 -- i.e. excluding this plan type
828 -- 2. get paid maternity i.e. in the date range if there are
829 -- any paid maternities
830 -- extend the period by sum of 1 + 2
831 -- For 1 Year
832 -- 1.get any nopay days inc;uding the sickness ( = CSS )
833 -- i.e. include this plan type
834 -- 2. get paid maternity i.e. in the date range if there
835 -- are any paid maternities
836 -- extend the period by sum of 1 + 2
837
838 l_proc_step := 20 ;
839 IF g_debug THEN
840 debug('NOPAY Days are :'||l_no_pay_days);
841 END IF ;
842
843 exit when l_no_pay_days <= 0 ;
844 -- if no more unpaid days are left in the extended period exit the loop
845 l_period_end_date := l_period_start_date ;
846 l_period_start_date := l_period_end_date - l_no_pay_days ;
847 end loop ;
848
849 l_proc_step := 30 ;
850 IF g_debug THEN
851 debug_exit(l_proc_name) ;
852 END IF ;
853
854 RETURN l_period_start_date ;
855
856 EXCEPTION
857 WHEN OTHERS THEN
858 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
859 pqp_utilities.debug_others
860 (l_proc_name
861 ,l_proc_step
862 );
863 IF g_debug THEN
864 debug('Leaving: '||l_proc_name,-999);
865 END IF;
866 fnd_message.raise_error;
867 ELSE
868 RAISE;
869 END IF;
870
871 END get_rolling_start_date;
872
873
874 -- Function returns all non paid absences in the given duration
875 -- include absences + maternity + NOPAY absences.
876 FUNCTION get_no_pay_days ( p_rolling_start_date IN DATE
877 ,p_rolling_end_date IN DATE
878 ,p_assignment_id IN NUMBER
879 ,p_business_group_id IN NUMBER
880 ,p_pl_typ_id IN NUMBER
881 ,p_dont_count_css_nopay_days IN BOOLEAN
882 ,p_lookup_type IN VARCHAR2
883 ) RETURN NUMBER IS
884 l_tot_no_pay_days NUMBER ;
885 l_tot_css_no_pay_days NUMBER ;
886 l_proc_name VARCHAR2(61) := g_package_name||'get_no_pay_days';
887 l_proc_step NUMBER(20,10) ;
888
889 -- This cursor gets the NOPAID days only
890 -- for Civil Service Scheme, as in 4 years
891 CURSOR csr_css_no_pay_days IS
892 select NVL(SUM(gda.duration),0)
893 from pqp_gap_daily_absences gda
894 ,pqp_gap_absence_plans gap
895 ,ben_pl_f pl
896 where pl.pl_id = gap.pl_id
897 and pl.pl_typ_id = p_pl_typ_id
898 and gap.gap_absence_plan_id = gda.gap_absence_plan_id
899 and gap.assignment_id = p_assignment_id
900 and gda.level_of_pay = 'NOBAND'
901 and gda.absence_date between p_rolling_start_date
902 and p_rolling_end_date ;
903
904 l_pl_typ_id ben_pl_f.pl_typ_id%TYPE ;
905 BEGIN
906
907 IF g_debug THEN
908 debug_enter(l_proc_name);
909 debug('p_rolling_start_date:'||p_rolling_start_date);
910 debug('p_rolling_end_date:'||p_rolling_end_date);
911 debug('p_assignment_id:'||p_assignment_id);
912 debug('p_business_group_id:'||p_business_group_id);
913 debug('p_pl_typ_id:'||p_pl_typ_id);
914 END IF;
915
916 -- the number of days to be extended are returned by this function
917 -- for both 4-year and 1-year we have to roll back by all paid absences
918 -- and nopiad absences of absences other than CS.
919 -- only exception being for 1-year we have to extend even the NOPAID days
920 -- of CS.
921 -- So assume that the plan types are stored for all the absence categories
922 -- that needs to be considered in extending in a lookup
923 -- PQP_GAP_PLAN_TYPES_TO_EXTEND. This is required as there is no UI option
924 -- yet to support the selection of such plan types.
925 -- if it is for 4-year return the sum of those plan types absences
926 -- if for 1-year include even the CS NOPAID days and return.
927
928 l_proc_step := 10 ;
929
930 OPEN csr_get_days_to_extend (
931 p_business_group_id => p_business_group_id
932 ,p_assignment_id => p_assignment_id
933 ,p_rolling_start_date => p_rolling_start_date
934 ,p_rolling_end_date => p_rolling_end_date
935 ,p_lookup_type => p_lookup_type --'PQP_GAP_PLAN_TYPES_TO_EXTEND'
936 ) ;
937 FETCH csr_get_days_to_extend INTO l_tot_no_pay_days ;
938 CLOSE csr_get_days_to_extend ;
939
940 l_proc_step := 20 ;
941 -- p_dont_chk_pl_typ_id should have FALSE for 1-year rolling period
942 -- and TRUE for 4-year rolling period
943 IF NOT p_dont_count_css_nopay_days THEN
944
945 l_proc_step := 30 ;
946
947 OPEN csr_css_no_pay_days ;
948 FETCH csr_css_no_pay_days INTO l_tot_css_no_pay_days ;
949 CLOSE csr_css_no_pay_days ;
950
951 l_tot_no_pay_days := l_tot_no_pay_days + l_tot_css_no_pay_days ;
952
953 l_proc_step := 40 ;
954 IF g_debug THEN
955 debug('4-Year Rolling Period no pay days:'||l_tot_no_pay_days);
956 ENd IF;
957
958 END IF;
959
960 IF g_debug THEN
961 debug('No Pay Days:'||l_tot_no_pay_days);
962 debug_exit(l_proc_name) ;
963 END IF ;
964
965 RETURN NVL(l_tot_no_pay_days,0) ;
966
967 EXCEPTION
968 WHEN OTHERS THEN
969 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
970 pqp_utilities.debug_others
971 (l_proc_name
972 ,l_proc_step
973 );
974 IF g_debug THEN
975 debug('Leaving: '||l_proc_name,-999);
976 END IF;
977 fnd_message.raise_error;
978 ELSE
979 RAISE;
980 END IF;
981
982 END get_no_pay_days ;
983
984 END pqp_gb_css_daily_absences ;
[Home] [Help]