1 PACKAGE BODY pqp_gb_css_daily_absences AS
2 /* $Header: pqgbdcss.pkb 120.1.12010000.3 2009/07/30 15:31:42 vaibgupt ship $ */
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 --begin changes for bug 8704523
570 -- Made changes according to Profile option (Newly added)
571 --'Count Unpaid Absence' Whose value if No then doesn't calculate
572 -- OSP NOBAND absences in the count of total absences
573 -- as it was originally required in the bug 7585452.
574 --hr_utility.set_location('vaibhav I am here : '||fnd_profile.value('BEN_COUNT_UNPAID_ABSENCE'),419);
575 IF (fnd_profile.value('BEN_COUNT_UNPAID_ABSENCE')='N') THEN
576 --hr_utility.set_location('vaibhav I am here in N: ',420);
577 IF l_absences_taken(i).band like 'BAND%' --OR
578 --l_absences_taken(i).band like 'NOBAND%' --bug 7585452, changed by vaibgupt
579 -- NOBAND should not be counted in absences taken
580
581 THEN
582 l_total_absences_taken:=l_total_absences_taken +
583 l_absences_taken(i).duration_per_week;
584 END IF ;
585 ELSE
586 --hr_utility.set_location('vaibhav I am here in Y: ',421);
587 IF l_absences_taken(i).band like 'BAND%' OR
588 l_absences_taken(i).band like 'NOBAND%' THEN
589 l_total_absences_taken:=l_total_absences_taken +
590 l_absences_taken(i).duration_per_week;
591 END IF ;
592 END IF;
593 -- end changes for bug 8704523
594
595
596 i := l_absences_taken.NEXT(i);
597 END LOOP ;
598
599 IF g_debug THEN
600 debug('l_total_absences_taken:'||l_total_absences_taken);
601 END IF;
602 ELSE -- IF p_4_year_rolling_period THEN
603 i := l_absences_taken.FIRST;
604
605 WHILE i IS NOT NULL
606 LOOP
607 l_proc_step := 50 ;
608 IF g_debug THEN
609 debug('Band:'||l_absences_taken(i).band);
610 debug('Absences taken:'||l_absences_taken(i).duration_per_week);
611 END IF;
612 IF l_absences_taken(i).band = 'BAND1' THEN
613 l_total_absences_taken:=l_absences_taken(i).duration_per_week;
614 END IF;
615 i := l_absences_taken.NEXT(i);
616 END LOOP ;
617
618
619 IF g_debug THEN
620 debug('l_total_absences_taken BAND1:'||l_total_absences_taken);
621 END IF;
622
623 END IF; --IF p_4_year_rolling_period THEN
624
625 l_proc_step := 60 ;
626
627 -- PT Changes
628 -- Here need to convert the total entitlements into weeks
629 -- then get the absences taken also in weeks
630 -- deduct and convert that into days by multiplying by the
631 -- no of working days in week or 7
632
633 IF g_debug THEN
634 debug('Entitled Weeks :'||l_total_entitlement);
635 END IF ;
636
637 l_remaining_entitlements := ( l_total_entitlement -
638 NVL(l_total_absences_taken,0) ) *
639 p_working_days_in_week ;
640
641 -- Round the Remaining Entitlements first to 2 decimals
642 --l_remaining_entitlements := ROUND(l_remaining_entitlements,2) ;
643
644 --- then round to
645 -- For Full timers always a upper 0.5
646 -- For Part-timers round it to lower 0.5
647 -- 4.4 for PT = 4. or 4.6 = 4.5
648 -- 4.4 for a FT = 4.5 or 4.6 = 5
649
650 /* IF p_fulltime THEN
651 -- For FT round to the upper 0.5
652 l_remaining_entitlements :=
653 pqp_utilities.round_value_up_down(
654 p_value_to_round => l_remaining_entitlements
655 ,p_base_value => g_ft_rounding_precision
656 ,p_rounding_type => g_ft_entitl_rounding_type
657 ) ;
658
659 ELSE -- IF p_fulltime THEN
660 -- For PT round to the lower 0.5
661
662 l_remaining_entitlements :=
663 pqp_utilities.round_value_up_down(
664 p_value_to_round => l_remaining_entitlements
665 ,p_base_value => g_pt_rounding_precision
666 ,p_rounding_type => g_pt_entitl_rounding_type
667 ) ;
668 END IF ; -- IF p_fulltime THEN
669 */
670 IF g_debug THEN
671 debug('Remaining Ent Days :'||l_remaining_entitlements);
672 END IF;
673 -- PT Changes cchappid
674 exit when l_remaining_entitlements <= 0 ;
675
676 --IF l_remaining_entitlements < 0 THEN
677 -- l_remaining_entitlements := 0 ;
678 --END IF;
679 l_proc_step := 70 ;
680 p_total_remaining := p_total_remaining + l_remaining_entitlements ;
681
682 l_total_entitlement := l_total_absences_taken ;
683
684 IF g_debug THEN
685 debug(' Rolling Start Date :'||l_rolling_start_date);
686 END IF ;
687
688
689 -- PT Changes
690 -- Here we need check if FT or PT and add days accrodingly
691 -- From 5 Change it to refer to Default_work_pattern days
692
693 IF p_working_days_in_week < p_standard_work_days_in_week THEN
694 -- call add_working_days function here
695 l_proc_step := 75 ;
696 -- l_rolling_end_date :=
697 -- pqp_schedule_calculation_pkg.add_working_days
698 -- (p_assignment_id => p_assignment_id
699 -- ,p_business_group_id => p_business_group_id
700 -- ,p_date_start => l_rolling_end_date
701 -- ,p_days => l_remaining_entitlements - 1
702 -- ,p_error_code => l_error_code
703 -- ,p_error_message => l_error_message
704 -- ) ;
705 l_rolling_end_date :=
706 pqp_schedule_calculation_pkg.add_working_days_using_one_wp
707 (p_assignment_id => p_assignment_id
708 ,p_business_group_id => p_business_group_id
709 ,p_date_start => l_rolling_end_date
710 ,p_working_days_to_add => l_remaining_entitlements
711 ) ;
712 l_rolling_end_date := l_rolling_end_date + 1 ;
713 ELSE
714 l_proc_step := 80 ;
715 l_rolling_end_date := l_rolling_end_date + l_remaining_entitlements ;
716 END IF;
717
718 IF g_debug THEN
719 debug(' Rolling End Date :'||l_rolling_end_date);
720 END IF ;
721 -- PT Changes
722 -- check if this exit part needs to be commented out
723 -- IF p_absence_end_date < l_rolling_end_date THEN
724 -- l_rolling_end_date := p_absence_end_date ;
725 -- p_total_remaining := l_rolling_end_date - p_absence_start_date + 1 ;
726 -- exit ;
727 -- END IF;
728
729 -- moved this as we shud recalculate the review date
730 -- though the remaining entitlements are 0 but
731 -- absence end date is before the review date
732 -- exit when l_remaining_entitlements <= 0 ;
733 l_total_absences_taken := 0 ;
734 end loop ;
735
736 IF p_fulltime THEN
737 -- For FT round to the upper 0.5
738 p_total_remaining :=
739 pqp_utilities.round_value_up_down(
740 p_value_to_round => p_total_remaining
741 ,p_base_value => g_ft_rounding_precision
742 ,p_rounding_type => g_ft_entitl_rounding_type
743 ) ;
744
745 ELSE -- IF p_fulltime THEN
746 -- For PT round to the lower 0.5
747
748 p_total_remaining :=
749 pqp_utilities.round_value_up_down(
750 p_value_to_round => p_total_remaining
751 ,p_base_value => g_pt_rounding_precision
752 ,p_rounding_type => g_pt_entitl_rounding_type
753 ) ;
754 END IF ; -- IF p_fulltime THEN
755
756 l_proc_step := 90 ;
757 IF g_debug THEN
758 debug('Review Date :'||l_rolling_end_date);
759 debug('Remaining Absence :'||p_total_remaining);
760 debug_exit(l_proc_name) ;
761 END IF ;
762
763 RETURN l_rolling_end_date ;
764
765 EXCEPTION
766 WHEN OTHERS THEN
767 p_total_remaining := l_total_remaining_nc;
768 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
769 pqp_utilities.debug_others
770 (l_proc_name
771 ,l_proc_step
772 );
773 IF g_debug THEN
774 debug('Leaving: '||l_proc_name,-999);
775 END IF;
776 fnd_message.raise_error;
777 ELSE
778 RAISE;
779 END IF;
780
781 END get_review_date ;
782
783
784 -- The function returns the Rolling period Start date,
785 -- based on Rolling period and Rolling End Date.
786
787 FUNCTION get_rolling_start_date ( p_rolling_end_date IN DATE
788 ,p_scheme_period_duration IN NUMBER
789 ,p_assignment_id IN NUMBER
790 ,p_business_group_id IN NUMBER
791 ,p_scheme_period_type IN VARCHAR2
792 ,p_scheme_period_uom IN VARCHAR2
793 ,p_pl_typ_id IN NUMBER
794 ,p_4_year_rolling_period IN BOOLEAN
795 ,p_lookup_type IN VARCHAR2
796 -- lookup type that contains the plan types
797 -- to exendn the rolling period.
798 ) RETURN DATE IS
799 l_period_end_date date := p_rolling_end_date ;
800 l_period_start_date date ;
801 l_no_pay_days number ;
802 l_proc_name VARCHAR2(61) := g_package_name||'get_rolling_start_date';
803 l_proc_step NUMBER(20,10) ;
804 BEGIN
805 g_debug := hr_utility.debug_enabled ;
806 IF g_debug THEN
807 debug_enter(l_proc_name);
808 debug('p_rolling_end_date:'||p_rolling_end_date);
809 debug('p_scheme_period_duration:'||p_scheme_period_duration);
810 debug('p_assignment_id:'||p_assignment_id);
811 debug('p_scheme_period_type:'||p_scheme_period_type);
812 debug('p_scheme_period_uom:'||p_scheme_period_uom);
813 debug('p_pl_typ_id:'||p_pl_typ_id);
814 END IF;
815
816 l_proc_step := 10 ;
817 -- get period end date for the calander period
818 l_period_start_date :=
819 pqp_absval_pkg.get_scheme_start_date(
820 p_assignment_id => p_assignment_id
821 ,p_scheme_period_type => p_scheme_period_type
822 ,p_scheme_period_duration => p_scheme_period_duration
823 ,p_scheme_period_uom => p_scheme_period_uom
824 ,p_fixed_year_start_date => fnd_date.date_to_canonical(
825 p_rolling_end_date)
826 ,p_balance_effective_date => p_rolling_end_date
827 ) ;
828 -- get all no pay days in the period (including sickness + maternity
829 -- + NOPAY absences extend the period end date by no of nopay days
830 -- to find any more nopay days in the extended period.
831 -- loop thru the extended period till there are no more nopay days.
832
833 loop
834 --Was earlier calling get_no_pay_days of the same package
835 l_no_pay_days :=
836 pqp_absval_pkg.get_calendar_days_to_extend(
837 p_period_start_date => l_period_start_date
838 ,p_period_end_date => l_period_end_date
839 ,p_assignment_id => p_assignment_id
840 ,p_business_group_id => p_business_group_id
841 ,p_pl_typ_id => p_pl_typ_id
842 ,p_count_nopay_days => p_4_year_rolling_period
843 ,p_plan_types_lookup_type => p_lookup_type
844 );
845
846 -- For 4 Years
847 -- get_no_pay_days should be split into
848 -- 1.get any nopay days except the sickness ( = CSS )
849 -- i.e. excluding this plan type
850 -- 2. get paid maternity i.e. in the date range if there are
851 -- any paid maternities
852 -- extend the period by sum of 1 + 2
853 -- For 1 Year
854 -- 1.get any nopay days inc;uding the sickness ( = CSS )
855 -- i.e. include this plan type
856 -- 2. get paid maternity i.e. in the date range if there
857 -- are any paid maternities
858 -- extend the period by sum of 1 + 2
859
860 l_proc_step := 20 ;
861 IF g_debug THEN
862 debug('NOPAY Days are :'||l_no_pay_days);
863 END IF ;
864
865 exit when l_no_pay_days <= 0 ;
866 -- if no more unpaid days are left in the extended period exit the loop
867 l_period_end_date := l_period_start_date ;
868 l_period_start_date := l_period_end_date - l_no_pay_days ;
869 end loop ;
870
871 l_proc_step := 30 ;
872 IF g_debug THEN
873 debug_exit(l_proc_name) ;
874 END IF ;
875
876 RETURN l_period_start_date ;
877
878 EXCEPTION
879 WHEN OTHERS THEN
880 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
881 pqp_utilities.debug_others
882 (l_proc_name
883 ,l_proc_step
884 );
885 IF g_debug THEN
886 debug('Leaving: '||l_proc_name,-999);
887 END IF;
888 fnd_message.raise_error;
889 ELSE
890 RAISE;
891 END IF;
892
893 END get_rolling_start_date;
894
895
896 -- Function returns all non paid absences in the given duration
897 -- include absences + maternity + NOPAY absences.
898 FUNCTION get_no_pay_days ( p_rolling_start_date IN DATE
899 ,p_rolling_end_date IN DATE
900 ,p_assignment_id IN NUMBER
901 ,p_business_group_id IN NUMBER
902 ,p_pl_typ_id IN NUMBER
903 ,p_dont_count_css_nopay_days IN BOOLEAN
904 ,p_lookup_type IN VARCHAR2
905 ) RETURN NUMBER IS
906 l_tot_no_pay_days NUMBER ;
907 l_tot_css_no_pay_days NUMBER ;
908 l_proc_name VARCHAR2(61) := g_package_name||'get_no_pay_days';
909 l_proc_step NUMBER(20,10) ;
910
911 -- This cursor gets the NOPAID days only
912 -- for Civil Service Scheme, as in 4 years
913 CURSOR csr_css_no_pay_days IS
914 select NVL(SUM(gda.duration),0)
915 from pqp_gap_daily_absences gda
916 ,pqp_gap_absence_plans gap
917 ,ben_pl_f pl
918 where pl.pl_id = gap.pl_id
919 and pl.pl_typ_id = p_pl_typ_id
920 and gap.gap_absence_plan_id = gda.gap_absence_plan_id
921 and gap.assignment_id = p_assignment_id
922 and gda.level_of_pay = 'NOBAND'
923 and gda.absence_date between p_rolling_start_date
924 and p_rolling_end_date ;
925
926 l_pl_typ_id ben_pl_f.pl_typ_id%TYPE ;
927 BEGIN
928
929 IF g_debug THEN
930 debug_enter(l_proc_name);
931 debug('p_rolling_start_date:'||p_rolling_start_date);
932 debug('p_rolling_end_date:'||p_rolling_end_date);
933 debug('p_assignment_id:'||p_assignment_id);
934 debug('p_business_group_id:'||p_business_group_id);
935 debug('p_pl_typ_id:'||p_pl_typ_id);
936 END IF;
937
938 -- the number of days to be extended are returned by this function
939 -- for both 4-year and 1-year we have to roll back by all paid absences
940 -- and nopiad absences of absences other than CS.
941 -- only exception being for 1-year we have to extend even the NOPAID days
942 -- of CS.
943 -- So assume that the plan types are stored for all the absence categories
944 -- that needs to be considered in extending in a lookup
945 -- PQP_GAP_PLAN_TYPES_TO_EXTEND. This is required as there is no UI option
946 -- yet to support the selection of such plan types.
947 -- if it is for 4-year return the sum of those plan types absences
948 -- if for 1-year include even the CS NOPAID days and return.
949
950 l_proc_step := 10 ;
951
952 OPEN csr_get_days_to_extend (
953 p_business_group_id => p_business_group_id
954 ,p_assignment_id => p_assignment_id
955 ,p_rolling_start_date => p_rolling_start_date
956 ,p_rolling_end_date => p_rolling_end_date
957 ,p_lookup_type => p_lookup_type --'PQP_GAP_PLAN_TYPES_TO_EXTEND'
958 ) ;
959 FETCH csr_get_days_to_extend INTO l_tot_no_pay_days ;
960 CLOSE csr_get_days_to_extend ;
961
962 l_proc_step := 20 ;
963 -- p_dont_chk_pl_typ_id should have FALSE for 1-year rolling period
964 -- and TRUE for 4-year rolling period
965 IF NOT p_dont_count_css_nopay_days THEN
966
967 l_proc_step := 30 ;
968
969 OPEN csr_css_no_pay_days ;
970 FETCH csr_css_no_pay_days INTO l_tot_css_no_pay_days ;
971 CLOSE csr_css_no_pay_days ;
972
973 l_tot_no_pay_days := l_tot_no_pay_days + l_tot_css_no_pay_days ;
974
975 l_proc_step := 40 ;
976 IF g_debug THEN
977 debug('4-Year Rolling Period no pay days:'||l_tot_no_pay_days);
978 ENd IF;
979
980 END IF;
981
982 IF g_debug THEN
983 debug('No Pay Days:'||l_tot_no_pay_days);
984 debug_exit(l_proc_name) ;
985 END IF ;
986
987 RETURN NVL(l_tot_no_pay_days,0) ;
988
989 EXCEPTION
990 WHEN OTHERS THEN
991 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
992 pqp_utilities.debug_others
993 (l_proc_name
994 ,l_proc_step
995 );
996 IF g_debug THEN
997 debug('Leaving: '||l_proc_name,-999);
998 END IF;
999 fnd_message.raise_error;
1000 ELSE
1001 RAISE;
1002 END IF;
1003
1004 END get_no_pay_days ;
1005
1006 END pqp_gb_css_daily_absences ;
[Home] [Help]