1 PACKAGE BODY pqp_absval_pkg AS
2 /* $Header: pqabsbal.pkb 120.21 2012/01/12 11:21:03 rajganga noship $ */
3 --
4 -- Global Varaibles
5
6 g_package_name VARCHAR2(31):= 'pqp_absval_pkg.';
7
8 g_plan_information rec_plan_information;
9 g_pl_id ben_pl_f.pl_typ_id%TYPE;
10 g_debug BOOLEAN:= hr_utility.debug_enabled;
11 -- Person Absence Aggregation globals
12 g_deduct_absence_for pqp_configuration_values.PCV_INFORMATION9%TYPE;
13
14 -- Cache for rounding of factors
15 g_pt_entitl_rounding_type VARCHAR2(10):=null;
16 g_pt_rounding_precision pqp_gap_daily_absences.duration%TYPE;
17 g_ft_rounding_precision pqp_gap_daily_absences.duration%TYPE;
18 g_round_cache_plan_id NUMBER;
19 g_ft_entitl_rounding_type VARCHAR2(10):=null ;
20 g_open_ended_no_pay_days NUMBER;
21 g_log_duration_summary VARCHAR2(20) := NULL;
22
23 --
24
25 -- Cursors for processing summary table values
26
27 CURSOR csr_pay_level_summary(
28 p_gap_absence_plan_id NUMBER
29 )
30 IS
31 SELECT level_of_pay GAP_LEVEL, MIN(absence_date) START_DATE,
32 MAX(absence_date) END_DATE, SUM(DURATION) DURATION,
33 SUM(DURATION_IN_HOURS) DURATION_IN_HOURS
34 FROM pqp_gap_daily_absences
35 WHERE gap_absence_plan_id = p_gap_absence_plan_id
36 GROUP BY level_of_pay
37 HAVING level_of_pay LIKE '%BAND%'
38 OR level_of_pay LIKE 'NOBANDMIN'
39 ORDER BY level_of_pay ;
40
41
42 CURSOR csr_ent_level_summary(
43 p_gap_absence_plan_id NUMBER
44 )
45 IS
46 SELECT level_of_entitlement GAP_LEVEL, MIN(absence_date) START_DATE,
47 MAX(absence_date) END_DATE,SUM(DURATION) DURATION,
48 SUM(DURATION_IN_HOURS) DURATION_IN_HOURS
49 FROM pqp_gap_daily_absences
50 WHERE gap_absence_plan_id = p_gap_absence_plan_id
51 GROUP BY level_of_entitlement
52 HAVING level_of_entitlement LIKE '%BAND%'
53 OR level_of_entitlement LIKE 'WAITINGDAY'
54 ORDER BY level_of_entitlement ;
55
56
57 CURSOR csr_level_typ_in_summary(
58 p_gap_absence_plan_id NUMBER
59 ,p_summary_type VARCHAR2
60 )
61 IS
62 SELECT gap_level GAP_LEVEL,gap_duration_summary_id GAP_DURATION_SUMMARY_ID,
63 object_version_number OBJECT_VERSION_NUMBER,'D' ACTION_TYPE
64 FROM pqp_gap_duration_summary
65 WHERE summary_type = p_summary_type AND
66 gap_absence_plan_id = p_gap_absence_plan_id ;
67
68
69
70
71 PROCEDURE debug
72 (p_trace_message IN VARCHAR2
73 ,p_trace_location IN NUMBER DEFAULT NULL
74 )
75 IS
76 BEGIN
77 pqp_utilities.debug(p_trace_message,p_trace_location);
78 END debug;
79 --
80 --
81 --
82 PROCEDURE debug
83 (p_trace_number IN NUMBER )
84 IS
85 BEGIN
86 pqp_utilities.debug(fnd_number.number_to_canonical(p_trace_number));
87 END debug;
88 --
89 --
90 --
91 PROCEDURE debug
92 (p_trace_date IN DATE )
93 IS
94 BEGIN
95 pqp_utilities.debug(fnd_date.date_to_canonical(p_trace_date));
96 END debug;
97 --
98 --
99 --
100 PROCEDURE debug_enter
101 (p_proc_name IN VARCHAR2
102 ,p_trace_on IN VARCHAR2 DEFAULT NULL
103 )
104 IS
105 -- l_trace_options VARCHAR2(200);
106 BEGIN
107 pqp_utilities.debug_enter(p_proc_name,p_trace_on);
108 END debug_enter;
109 --
110 --
111 --
112 PROCEDURE debug_exit
113 (p_proc_name IN VARCHAR2
114 ,p_trace_off IN VARCHAR2 DEFAULT NULL
115 )
116 IS
117 BEGIN
118 pqp_utilities.debug_exit(p_proc_name,p_trace_off);
119 END debug_exit;
120 --
121 --
122 --
123 PROCEDURE check_error_code
124 (p_error_code IN NUMBER
125 ,p_message IN VARCHAR2
126 )
127 IS
128 BEGIN
129 pqp_utilities.check_error_code(p_error_code, p_message);
130 END check_error_code;
131 --
132 --
133 --
134 PROCEDURE debug_others
135 (p_proc_name IN VARCHAR2
136 ,p_last_step_number IN NUMBER DEFAULT NULL
137 )
138 IS
139 l_message fnd_new_messages.message_text%TYPE;
140 BEGIN
141 IF g_debug THEN
142 debug(p_proc_name,SQLCODE);
143 debug(SQLERRM);
144 END IF;
145 l_message := p_proc_name||'{'||
146 fnd_number.number_to_canonical(p_last_step_number)||'}: '||
147 SUBSTRB(SQLERRM,1,2000);
148 IF g_debug THEN
149 debug(l_message);
150 END IF;
151 fnd_message.set_name( 'PQP', 'PQP_230661_OSP_DUMMY_MSG' );
152 fnd_message.set_token( 'TOKEN',l_message);
153 END debug_others;
154 --
155 --
156 --
157 FUNCTION get_scheme_start_date
158 (p_assignment_id IN NUMBER
159 ,p_scheme_period_type IN VARCHAR2
160 ,p_scheme_period_duration IN VARCHAR2
161 ,p_scheme_period_uom IN VARCHAR2
162 ,p_fixed_year_start_date IN VARCHAR2
163 ,p_balance_effective_date IN DATE
164 ) RETURN DATE
165 IS
166
167 -- Added cursor for anniversary year changes
168 CURSOR csr_get_emp_hire_date
169 IS
170 SELECT service.date_start
171 FROM per_all_assignments_f assign
172 ,per_periods_of_service service
173 WHERE p_balance_effective_date BETWEEN assign.effective_start_date
174 AND assign.effective_end_date
175 AND assign.assignment_id = p_assignment_id
176 AND service.period_of_service_id (+) = assign.period_of_service_id;
177
178 l_scheme_start_date DATE;
179
180 -- variable to be removed once we change p_fixed_year_start_date to DATE
181 l_fixed_year_start_date DATE:=
182 fnd_date.canonical_to_date(p_fixed_year_start_date);
183
184 l_scheme_period_duration NUMBER(10):=
185 fnd_number.canonical_to_number(p_scheme_period_duration);
186
187 l_end_date DATE;
188 l_temp VARCHAR2(25);
189
190 l_proc_step NUMBER(20,10);
191 l_proc_name VARCHAR2( 61 ):=
192 g_package_name||
193 'get_scheme_start_date';
194 BEGIN
195 IF g_debug THEN
196 debug_enter(l_proc_name);
197 debug(p_scheme_period_type);
198 debug(p_scheme_period_duration);
199 debug(p_scheme_period_uom);
200 debug(p_fixed_year_start_date);
201 debug(p_balance_effective_date);
202 END IF;
203
204 -- debug('l_scheme_period_duration:');
205 -- debug(l_scheme_period_duration);
206 -- IF l_scheme_period_duration IS NULL THEN
207 -- debug('IS NULL');
208 -- l_scheme_period_duration :=
209 -- fnd_number.canonical_to_number(p_scheme_period_duration);
210 -- END IF;
211 -- debug('l_scheme_period_duration:');
212 -- debug(l_scheme_period_duration);
213
214 -- Modified code for annivesary year
215 -- to determine the scheme start date we need to know
216 -- 1. is the scheme fixed or anniversary or rolling
217 -- 2. if its fixed or anniversary
218 -- a) scheme start date = DD-MON-YearsOfBalanceDate
219 -- 3. if its rolling
220 -- a) what is the absence period UOM: days(/weeks) or months(/years) ?
221 -- b) scheme start date = BalanceDate - AbsencePeriod
222
223 IF p_scheme_period_type = 'FIXED' OR
224 p_scheme_period_type = 'EMPYEAR'
225 THEN
226
227 l_proc_step := 10;
228 IF g_debug THEN
229 debug(l_proc_name,l_proc_step);
230 END IF;
231
232 -- The logic to calculate the l_scheme start_date is as follows
233 -- find the months between the fixed_year_start_date and the absence
234 -- start date(P_balance_effective_date),
235 -- convert this to years and floor the resultant value.
236 -- This gives the difference in yrs. The new
237 -- l_scheme_start_date is by adding the number of yrs
238 -- with the l_fixed_year_start_date.
239
240 -- This can be understood by the following scenario.(assuming the
241 -- scheme is fixed start date.
242
243 --
244 -- fixed yr scheme
245 -- start date Rolling period
246 -- |---------------------------|
247 --
248 -- |-------------------------|----------------------------|--------------
249 -- 25-feb-2004 25-feb-2005 01-nov-2005
250 -- roll back till here abs start date
251 -- (fixed start date)
252 --
253 --
254 -- In the above scenario, the new l_scheme_start_date should be
255 -- 25-feb-2005
256 -- The below code handle all scenario, except that when the scheme
257 -- is 28-FEB of an non leap year, and the absence is enrolled in
258 -- the following year
259 -- which is a leap year, so when the date are rolled back from the
260 -- absence start date, the fixed year start date should be 28-FEB of the
261 -- current year but it rolls back to 29-FEB as its the last day of FEB.
262 -- When the scheme start date is 28-FEB-2003 .The absence start date
263 -- is 01-nov-2004 its has to be rolled back till 28-FEB-2004
264 -- but it roll backs to 29-FEB-2004, as 28-FEB-2003 is the last day
265 -- of the months, after rolloing it rolls back to 29-feb-2004, as its the
266 -- last day of feb for the current year(2004, which is a leap year).
267
268 IF p_scheme_period_type = 'EMPYEAR'
269 THEN
270 -- Replace fixed year start date with hire date information
271 OPEN csr_get_emp_hire_date;
272 FETCH csr_get_emp_hire_date INTO l_fixed_year_start_date;
273 CLOSE csr_get_emp_hire_date;
274 END IF; -- end if of scheme period type is Anniversary check ...
275
276 l_scheme_start_date:=
277 add_months(l_fixed_year_start_date,floor(MONTHS_BETWEEN
278 (p_balance_effective_date,l_fixed_year_start_date)/12)*12);
279
280 --l_start_date := fnd_date.canonical_to_date( l_temp );
281
282 -- if the effective date was 05-FEB-2002
283 -- and the scheme start is 01-APR-YYYY
284 -- then the prev statement would set the scheme start date as
285 -- 01-APR-2002, which would mean that its greater than the eff date
286 -- in which case pull the scheme start date back by 1 year (12 months)
287 --
288 -- |-----------------|---------------|-------------|--------------
289 -- 01-APR-2001 05-FEB-2002 01-APR-2002
290 -- ^ ^ ^
291 -- ^ ^EffectiveDate ^
292 -- ^ ^
293 -- ^ ^SchemeStartDate(as per prev calc)
294 -- ^SchemeStartDate(should be) =SchemeStartDate(as per prev calc) - 1 year
295
296
297 IF l_scheme_start_date > p_balance_effective_date
298 THEN
299 l_proc_step := 20;
300 IF g_debug THEN
301 debug(l_proc_name,l_proc_step);
302 END IF;
303 l_scheme_start_date :=
304 ADD_MONTHS( l_scheme_start_date, -12 );
305 END IF;
306
307 -- the above statement would ensure that the scheme start date is
308 -- always the DD-MON preceding the balance effective date, ie it works
309 -- fine for fixed years with a duration of 1.
310
311 -- OSP configurations allow fixed years to be more than a year.
312 -- so we need to deduct a further "duration - 1" years from the scheme
313 -- start date.
314
315 --
316 -- |-----------------|---------------|-------------|--------------
317 -- 01-APR-2000 01-APR-2001 05-FEB-2002 01-APR-2002
318 -- ^ ^
319 -- ^ ^EffectiveDate
320 -- ^
321 -- ^SchemeStartDate(as per prev calc)
322 -- SchemeStartDate =SchemeStartDate(as per prev calc) - (2-1) years
323
324 IF p_scheme_period_duration > 1
325 -- we could do without the if codn also, duration - 1 would be 0
326 -- and the date would remain unchanged, but its clearer with the if
327 -- and reduces one function call !
328 THEN
329
330 l_proc_step := 30;
331 IF g_debug THEN
332 debug(l_proc_name,l_proc_step);
333 END IF;
334
335 l_scheme_start_date :=
336 ADD_MONTHS(l_scheme_start_date, -12 * (l_scheme_period_duration - 1));
337
338 END IF;
339
340 --
341 -- |-----------------|---------------|-------------|--------------
342 -- 01-APR-2000 01-APR-2001 05-FEB-2002 01-APR-2002
343 -- ^ ^
344 -- ^ ^EffectiveDate
345 -- ^
346 -- ^SchemeStartDate(as per prev calc)
347 -- SchemeStartDate =SchemeStartDate(as per prev calc) - (2-1) years
348
349
350 ELSE -- scheme type is ROLLING or DUALROLLING
351
352 -- if the effective date is 05-MAY-2002
353 -- the scheme start date is 05-MAY-2002 - absence_period
354 -- if the period is 365 days the scheme start date would be 05-MAY-2001
355 -- if the period is 52 weeks the scheme start date would be 06-MAY-2001
356 -- if the period is 6 months the scheme start date would be 05-NOV-2001
357 -- if the period is 1 year(s)the scheme start date would be 05-MAY-2001
358
359
360 IF p_scheme_period_uom = 'DAYS'
361 THEN
362
363 l_proc_step := 40;
364 IF g_debug THEN
365 debug(l_proc_name,l_proc_step);
366 --debug(p_balance_effective_date);
367 --debug(l_scheme_period_duration);
368 END IF;
369
370 l_scheme_start_date:=
371 p_balance_effective_date - l_scheme_period_duration;
372
373 --debug(l_scheme_period_duration);
374
375 ELSIF p_scheme_period_uom = 'WEEKS'
376 THEN
377 l_proc_step := 50;
378 IF g_debug THEN
379 debug(l_proc_name,l_proc_step);
380 END IF;
381
382 l_scheme_start_date:=
383 p_balance_effective_date - (l_scheme_period_duration * 7);
384
385 ELSIF p_scheme_period_uom = 'MONTHS'
386 THEN
387 l_proc_step := 60;
388 IF g_debug THEN
389 debug(l_proc_name,l_proc_step);
390 END IF;
391
392
393
394 l_scheme_start_date:=
395 ADD_MONTHS(p_balance_effective_date, ( -l_scheme_period_duration));
396
397 ELSIF p_scheme_period_uom = 'YEARS'
398 THEN
399 l_proc_step := 70;
400 IF g_debug THEN
401 debug(l_proc_name,l_proc_step);
402 END IF;
403 l_scheme_start_date:=
404 ADD_MONTHS(p_balance_effective_date, ( -12 * l_scheme_period_duration));
405
406 END IF;
407
408 END IF;
409
410 IF g_debug THEN
411 debug('l_scheme_start_date:');
412 debug(l_scheme_start_date);
413 debug_exit(l_proc_name);
414 END IF;
415
416 RETURN l_scheme_start_date;
417 EXCEPTION
418 WHEN OTHERS THEN
419 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
420 debug_others(l_proc_name,l_proc_step);
421 IF g_debug THEN
422 debug('Leaving: '||l_proc_name,-999);
423 END IF;
424 fnd_message.raise_error;
425 ELSE
426 RAISE;
427 END IF;
428 END get_scheme_start_date;
429 --
430 --
431 --
432 FUNCTION pqp_absence_sickness_ytd_ovrld -- IS FF (analyze use)
433 (p_assignment_id IN NUMBER
434 ,p_business_group_id IN NUMBER
435 ,p_element_type_id IN NUMBER
436 ,p_balance_effective_date IN DATE
437 ,p_calendar_column_name IN VARCHAR2
438 ,p_calendar_value IN VARCHAR2
439 ) RETURN NUMBER
440 IS
441
442 l_balance NUMBER(38,5);
443
444 BEGIN
445 l_balance :=0;
446 RETURN( NVL( l_balance, 0 ));
447 END pqp_absence_sickness_ytd_ovrld;
448 --
449 --
450 --
451 PROCEDURE write_daily_absences
452 (p_daily_absences IN pqp_absval_pkg.t_daily_absences
453 ,p_gap_absence_plan_id IN pqp_gap_absence_plans.gap_absence_plan_id%TYPE
454 )
455 IS
456
457 TYPE t_gap_daily_absence_id IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
458 TYPE t_gap_absence_plan_id IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
459 TYPE t_absence_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
460 TYPE t_work_pattern_day_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
461 TYPE t_level_of_entitlement IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
462 TYPE t_level_of_pay IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
463 TYPE t_duration IS TABLE OF NUMBER(11,5) INDEX BY BINARY_INTEGER;
464 TYPE t_duration_in_hours IS TABLE OF NUMBER(8,5) INDEX BY BINARY_INTEGER;
465 TYPE t_working_days_per_week IS TABLE OF NUMBER(15,13) INDEX BY BINARY_INTEGER;
466 TYPE t_fte IS TABLE OF NUMBER(25,5) INDEX BY BINARY_INTEGER;
467 TYPE t_object_version_number IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
468
469 l_gap_daily_absence_ids t_gap_daily_absence_id;
470 l_gap_absence_plan_ids t_gap_absence_plan_id;
471 l_absence_dates t_absence_date;
472 l_work_pattern_day_types t_work_pattern_day_type;
473 l_level_of_entitlements t_level_of_entitlement;
474 l_level_of_payments t_level_of_pay;
475 l_durations t_duration;
476 l_durations_in_hours t_duration_in_hours;
477 l_working_days_per_week t_working_days_per_week ;
478 l_fte t_fte ;
479 l_object_version_numbers t_object_version_number;
480
481
482 l_proc_step NUMBER(20,10);
483 l_proc_name VARCHAR2(61):=
484 g_package_name||
485 'write_daily_absences';
486
487 BEGIN
488 IF g_debug THEN
489 debug_enter(l_proc_name);
490 END IF;
491
492 --FORALL i IN 1..5000 -- use FORALL statement
493 -- INSERT INTO parts VALUES (pnums(i), pnames(i));
494 --desc pqp_gap_daily_absences
495 -- Name Null? Type
496 -- ------------------------------- -------- ----
497 -- GAP_DAILY_ABSENCE_ID NOT NULL NUMBER(15)
498 -- GAP_ABSENCE_PLAN_ID NOT NULL NUMBER(15)
499 -- ABSENCE_DATE NOT NULL DATE
500 -- WORK_PATTERN_DAY_TYPE NOT NULL VARCHAR2(30)
501 -- LEVEL_OF_ENTITLEMENT NOT NULL VARCHAR2(30)
502 -- LEVEL_OF_PAY NOT NULL VARCHAR2(30)
503 -- DURATION NOT NULL NUMBER(11,5)
504 -- LAST_UPDATED_BY NUMBER(15)
505 -- LAST_UPDATE_DATE DATE
506 -- CREATED_BY NUMBER(15)
507 -- CREATION_DATE DATE
508 -- OBJECT_VERSION_NUMBER NUMBER(15)
509 --
510 -- 8i bulk bind does not support record structures
511 -- so we need to copy all the record segments into
512 -- seperate plsql tables. This is unncessary processing
513 -- but the performance benefit of bulk bind is outweighs
514 -- this cost.
515 --
516 l_proc_step := 10;
517 IF g_debug THEN
518 debug(l_proc_name, 10);
519 END IF;
520
521 FOR i IN p_daily_absences.FIRST..p_daily_absences.LAST
522 LOOP
523 IF g_debug THEN
524 debug(l_proc_name, 10+i/1000);
525 END IF;
526
527 --l_gap_daily_absence_ids:= p_daily_absences(i).gap_daily_absence_ids
528 l_gap_absence_plan_ids(i) := p_gap_absence_plan_id;
529 --p_daily_absences(i).gap_absence_plan_id;
530 l_absence_dates(i) := p_daily_absences(i).absence_date;
531 l_work_pattern_day_types(i) := p_daily_absences(i).work_pattern_day_type;
532 l_level_of_entitlements(i) := p_daily_absences(i).level_of_entitlement;
533 l_level_of_payments(i) := p_daily_absences(i).level_of_pay;
534 l_durations(i) := p_daily_absences(i).duration;
535 l_durations_in_hours(i) := p_daily_absences(i).duration_in_hours;
536 l_working_days_per_week(i) := p_daily_absences(i).working_days_per_week;
537 l_fte(i) := p_daily_absences(i).fte;
538 l_object_version_numbers(i) := 1; -- new record
539
540 END LOOP;
541
542 l_proc_step := 20;
543 IF g_debug THEN
544 debug(l_proc_name, 20);
545 END IF;
546
547 FORALL i IN p_daily_absences.FIRST..p_daily_absences.LAST
548 INSERT INTO pqp_gap_daily_absences
549 (gap_daily_absence_id --NOT NULL NUMBER(15)
550 ,gap_absence_plan_id --NOT NULL NUMBER(15)
551 ,absence_date --NOT NULL DATE
552 ,work_pattern_day_type --NOT NULL VARCHAR2(30)
553 ,level_of_entitlement --NOT NULL VARCHAR2(30)
554 ,level_of_pay --NOT NULL VARCHAR2(30)
555 ,duration --NOT NULL NUMBER(11,5)
556 ,duration_in_hours -- NUMER(8,5) -- added
557 ,working_days_per_week
558 ,fte
559 -- ,last_updated_by -- NUMBER(15)
560 -- ,last_update_date -- DATE
561 -- ,created_by -- NUMBER(15)
562 -- ,creation_date -- DATE
563 ,object_version_number -- NUMBER(15)
564 )
565 VALUES
566 (pqp_gap_daily_absences_s.NEXTVAL
567 ,l_gap_absence_plan_ids(i) --NOT NULL NUMBER(15)
568 ,l_absence_dates(i) --NOT NULL DATE
569 ,l_work_pattern_day_types(i) --NOT NULL VARCHAR2(30)
570 ,l_level_of_entitlements(i) --NOT NULL VARCHAR2(30)
571 ,l_level_of_payments(i) --NOT NULL VARCHAR2(30)
572 ,l_durations(i) --NOT NULL NUMBER(11,5)
573 ,l_durations_in_hours(i) -- NUMBER(8,5)
574 ,l_working_days_per_week(i)
575 ,l_fte(i)
576 -- ,l_last_updated_by -- NUMBER(15)
577 -- ,l_last_update_date -- DATE
578 -- ,l_created_by -- NUMBER(15)
579 -- ,l_creation_date -- DATE
580 ,l_object_version_numbers(i) -- NUMBER(15)
581 );
582
583 l_proc_step := 30;
584 IF g_debug THEN
585 debug(l_proc_name, 30);
586 END IF;
587
588 IF g_debug THEN
589 debug_exit(l_proc_name);
590 END IF;
591
592 END write_daily_absences;
593 --
594 --
595 --
596 -- Absence Summary Table Changes
597 -- Process flow ==>
598
599 -- 1)Absence created and fresh enrollment
600 -- create_absence_plan_details
601 -- => write_absence_summary
602 -- => create_duration_summary
603 -- => write_duration_summary
604
605 -- 2)Absence Updated (End Date extended)
606 -- update_absence_plan_details
607 -- =>create_absence_plan_details
608 -- => write_absence_summary
609 -- => update_duration_summary
610 -- => write_duration_summary
611
612 -- 3)Absence Updated (End Date curtailed)
613 -- update_absence_plan_details
614 -- =>delete_absence_plan_details
615 -- => update_duration_summary
616 -- => write_duration_summary
617
618
619
620
621
622 PROCEDURE write_duration_summary
623 (p_absence_summary_tbl IN OUT NOCOPY pqp_absval_pkg.t_duration_summary
624 )
625 IS
626
627 l_proc_name VARCHAR2(61) := g_package_name||'write_duration_summary';
628 l_object_version_number NUMBER;
629 l_gap_duration_summary_id NUMBER;
630
631 l_proc_step NUMBER(20,10) ;
632 i BINARY_INTEGER;
633
634 BEGIN
635 g_debug := hr_utility.debug_enabled;
636
637 IF g_debug THEN
638 debug_enter(l_proc_name);
639 END IF;
640
641 i := p_absence_summary_tbl.FIRST;
642
643 WHILE i IS NOT NULL
644 LOOP
645
646 IF g_debug THEN
647 debug('i' || i);
648 debug('gap_absence_plan_id',p_absence_summary_tbl(i).gap_absence_plan_id);
649 debug('assignment_id' , p_absence_summary_tbl(i).assignment_id);
650 debug('summary_type' || p_absence_summary_tbl(i).summary_type);
651 debug('gap_level' || p_absence_summary_tbl(i).gap_level);
652 debug('duration_in_days' ,p_absence_summary_tbl(i).duration_in_days);
653 debug('duration_in_hours' ,p_absence_summary_tbl(i).duration_in_hours);
654 debug('date_start' || p_absence_summary_tbl(i).date_start);
655 debug('date_end' || p_absence_summary_tbl(i).date_end);
656 debug('action_type' || p_absence_summary_tbl(i).action_type);
657 END IF;
658
659 IF p_absence_summary_tbl(i).action_type = 'I'
660 THEN
661
662
663 pqp_gds_api.create_duration_summary
664 (p_date_start => p_absence_summary_tbl(i).date_start
665 ,p_date_end => p_absence_summary_tbl(i).date_end
666 ,p_assignment_id => p_absence_summary_tbl(i).assignment_id
667 ,p_gap_absence_plan_id => p_absence_summary_tbl(i).gap_absence_plan_id
668 ,p_duration_in_days => p_absence_summary_tbl(i).duration_in_days
669 ,p_duration_in_hours => p_absence_summary_tbl(i).duration_in_hours
670 ,p_summary_type => p_absence_summary_tbl(i).summary_type
671 ,p_gap_level => p_absence_summary_tbl(i).gap_level
672 ,p_gap_duration_summary_id => l_gap_duration_summary_id
673 ,p_object_version_number => l_object_version_number
674 );
675
676 ELSE -- IF p_absence_summary_tbl(i).action_type = 'I'
677
678 pqp_gds_api.update_duration_summary
679 (p_gap_duration_summary_id => p_absence_summary_tbl(i).gap_duration_summary_id
680 ,p_date_start => p_absence_summary_tbl(i).date_start
681 ,p_date_end => p_absence_summary_tbl(i).date_end
682 ,p_assignment_id => p_absence_summary_tbl(i).assignment_id
683 ,p_gap_absence_plan_id => p_absence_summary_tbl(i).gap_absence_plan_id
684 ,p_duration_in_days => p_absence_summary_tbl(i).duration_in_days
685 ,p_duration_in_hours => p_absence_summary_tbl(i).duration_in_hours
686 ,p_summary_type => p_absence_summary_tbl(i).summary_type
687 ,p_gap_level => p_absence_summary_tbl(i).gap_level
688 ,p_object_version_number => p_absence_summary_tbl(i).object_version_number
689 );
690
691 END IF;
692
693 i := p_absence_summary_tbl.NEXT(i);
694 END LOOP;
695
696 IF g_debug THEN
697 debug_exit(l_proc_name) ;
698 END IF ;
699 EXCEPTION
700 WHEN OTHERS THEN
701
702 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
703 pqp_utilities.debug_others
704 (l_proc_name
705 ,l_proc_step
706 );
707 IF g_debug THEN
708 debug('Leaving: '||l_proc_name,-999);
709 END IF;
710 fnd_message.raise_error;
711 ELSE
712 RAISE;
713 END IF;
714 END write_duration_summary ;
715
716
717 PROCEDURE create_duration_summary
718 (p_gap_absence_plan_id IN NUMBER
719 ,p_assignment_id IN NUMBER
720 )
721 IS
722 l_proc_name VARCHAR2(61) := g_package_name||'create_duration_summary';
723 l_proc_step NUMBER(20,10) ;
724 k BINARY_INTEGER;
725 l_absence_pay_summary csr_pay_level_summary%ROWTYPE;
726 l_absence_ent_summary csr_ent_level_summary%ROWTYPE;
727 l_ent_summary_existing_rows pqp_absval_pkg.t_gap_level ;
728 l_pay_summary_existing_rows pqp_absval_pkg.t_gap_level ;
729 l_duration_summary pqp_absval_pkg.t_duration_summary ;
730
731 BEGIN
732 g_debug := hr_utility.debug_enabled;
733
734 IF g_debug THEN
735 debug_enter(l_proc_name);
736 debug('p_gap_absence_plan_id:',p_gap_absence_plan_id);
737 debug('p_assignment_id:',p_assignment_id);
738
739 END IF;
740
741 IF g_debug THEN
742 debug_exit(l_proc_name) ;
743 END IF ;
744
745 -- This blocks summarizes the data from pqp_gap_daily_absence table for life
746 -- time type ENT (probably can us bulk collect in to table).Dint use it as
747 -- needed to process few attributes and individual level.Need to chk if using
748 -- bulk collect could be more efficient.
749
750 OPEN csr_ent_level_summary
751 (p_gap_absence_plan_id =>p_gap_absence_plan_id);
752
753 k := 1;
754 LOOP
755
756 FETCH csr_ent_level_summary INTO l_absence_ent_summary;
757 EXIT WHEN csr_ent_level_summary %NOTFOUND ;
758 l_duration_summary(k).gap_absence_plan_id := p_gap_absence_plan_id;
759 l_duration_summary(k).assignment_id := p_assignment_id;
760 l_duration_summary(k).gap_level:=
761 l_absence_ent_summary.gap_level;
762 l_duration_summary(k).summary_type:= 'ENT';
763 l_duration_summary(k).date_start:= l_absence_ent_summary.START_DATE;
764 l_duration_summary(k).date_end:= l_absence_ent_summary.END_DATE;
765 l_duration_summary(k).duration_in_days:= l_absence_ent_summary.duration;
766 l_duration_summary(k).duration_in_hours:=
767 l_absence_ent_summary.duration_in_hours;
768 l_duration_summary(k).action_type := 'I';
769 k := k+1;
770 END LOOP;
771 CLOSE csr_ent_level_summary;
772
773
774
775 OPEN csr_pay_level_summary
776 (p_gap_absence_plan_id =>p_gap_absence_plan_id);
777
778 LOOP
779
780 FETCH csr_pay_level_summary INTO l_absence_pay_summary;
781 EXIT WHEN csr_pay_level_summary %NOTFOUND ;
782 l_duration_summary(k).gap_absence_plan_id := p_gap_absence_plan_id;
783 l_duration_summary(k).assignment_id := p_assignment_id;
784 l_duration_summary(k).gap_level:=
785 l_absence_pay_summary.gap_level;
786 l_duration_summary(k).summary_type:= 'PAY';
787 l_duration_summary(k).date_start:=l_absence_pay_summary.START_DATE;
788 l_duration_summary(k).date_end:= l_absence_pay_summary.END_DATE;
789 l_duration_summary(k).duration_in_days:= l_absence_pay_summary.duration;
790 l_duration_summary(k).duration_in_hours:=
791 l_absence_pay_summary.duration_in_hours;
792 l_duration_summary(k).action_type := 'I';
793 k := k+1;
794 END LOOP;
795 CLOSE csr_pay_level_summary;
796
797
798 write_duration_summary
799 (p_absence_summary_tbl => l_duration_summary
800 );
801
802
803 EXCEPTION
804 WHEN OTHERS THEN
805
806 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
807 pqp_utilities.debug_others
808 (l_proc_name
809 ,l_proc_step
810 );
811 IF g_debug THEN
812 debug('Leaving: '||l_proc_name,-999);
813 END IF;
814 fnd_message.raise_error;
815 ELSE
816 RAISE;
817 END IF;
818 END create_duration_summary ;
819
820
821 PROCEDURE update_duration_summary
822 (p_gap_absence_plan_id IN NUMBER
823 ,p_assignment_id IN NUMBER
824 )
825 IS
826 l_proc_name VARCHAR2(61) := g_package_name||'update_duration_summary';
827 l_proc_step NUMBER(20,10) ;
828 k BINARY_INTEGER;
829 l BINARY_INTEGER;
830 l_absence_pay_summary csr_pay_level_summary%ROWTYPE;
831 l_absence_ent_summary csr_ent_level_summary%ROWTYPE;
832 l_ent_summary_existing_rows pqp_absval_pkg.t_gap_level ;
833 l_pay_summary_existing_rows pqp_absval_pkg.t_gap_level ;
834 l_duration_summary pqp_absval_pkg.t_duration_summary ;
835
836 BEGIN
837 g_debug := hr_utility.debug_enabled;
838
839 IF g_debug THEN
840 debug_enter(l_proc_name);
841 debug('p_gap_absence_plan_id:',p_gap_absence_plan_id);
842 debug('p_assignment_id:',p_assignment_id);
843
844 END IF;
845
846 IF g_debug THEN
847 debug_exit(l_proc_name) ;
848 END IF ;
849
850
851 OPEN csr_level_typ_in_summary
852 (p_gap_absence_plan_id =>p_gap_absence_plan_id
853 ,p_summary_type =>'ENT'
854 );
855
856 FETCH csr_level_typ_in_summary BULK COLLECT
857 INTO l_ent_summary_existing_rows ;
858 CLOSE csr_level_typ_in_summary ;
859
860 OPEN csr_level_typ_in_summary
861 (p_gap_absence_plan_id =>p_gap_absence_plan_id
862 ,p_summary_type =>'PAY'
863 );
864
865 FETCH csr_level_typ_in_summary BULK COLLECT
866 INTO l_pay_summary_existing_rows ;
867 CLOSE csr_level_typ_in_summary ;
868
869
870
871 OPEN csr_ent_level_summary
872 (p_gap_absence_plan_id =>p_gap_absence_plan_id);
873
874 k := 1;
875 LOOP
876
877 FETCH csr_ent_level_summary INTO l_absence_ent_summary;
878 EXIT WHEN csr_ent_level_summary %NOTFOUND ;
879 l_duration_summary(k).gap_absence_plan_id := p_gap_absence_plan_id;
880 l_duration_summary(k).assignment_id := p_assignment_id;
881 l_duration_summary(k).gap_level:=
882 l_absence_ent_summary.gap_level;
883 l_duration_summary(k).summary_type:= 'ENT';
884 l_duration_summary(k).date_start:= l_absence_ent_summary.START_DATE;
885 l_duration_summary(k).date_end:= l_absence_ent_summary.END_DATE;
886 l_duration_summary(k).duration_in_days:= l_absence_ent_summary.duration;
887 l_duration_summary(k).duration_in_hours:=
888 l_absence_ent_summary.duration_in_hours;
889
890
891 l_duration_summary(k).action_type := 'I';
892
893 l := l_ent_summary_existing_rows.FIRST;
894 WHILE l IS NOT NULL
895 LOOP
896 IF(l_duration_summary(k).gap_level =
897 l_ent_summary_existing_rows(l).gap_level)
898 THEN
899 l_duration_summary(k).action_type := 'U';
900 l_duration_summary(k).gap_duration_summary_id :=
901 l_ent_summary_existing_rows(l).gap_duration_summary_id ;
902 l_duration_summary(k).object_version_number :=
903 l_ent_summary_existing_rows(l).object_version_number;
904 l_ent_summary_existing_rows(l).action_type := 'U';
905
906 END IF;
907 l := l_ent_summary_existing_rows.NEXT(l);
908 END LOOP;
909 k := k+1;
910 END LOOP;
911 CLOSE csr_ent_level_summary;
912
913
914
915
916 OPEN csr_pay_level_summary
917 (p_gap_absence_plan_id =>p_gap_absence_plan_id);
918
919 LOOP
920
921 FETCH csr_pay_level_summary INTO l_absence_pay_summary;
922 EXIT WHEN csr_pay_level_summary %NOTFOUND ;
923 l_duration_summary(k).gap_absence_plan_id := p_gap_absence_plan_id;
924 l_duration_summary(k).assignment_id := p_assignment_id;
925 l_duration_summary(k).gap_level:=
926 l_absence_pay_summary.gap_level;
927 l_duration_summary(k).summary_type:= 'PAY';
928 l_duration_summary(k).date_start:=l_absence_pay_summary.START_DATE;
929 l_duration_summary(k).date_end:= l_absence_pay_summary.END_DATE;
930 l_duration_summary(k).duration_in_days:= l_absence_pay_summary.duration;
931 l_duration_summary(k).duration_in_hours:=
932 l_absence_pay_summary.duration_in_hours;
933
934 l_duration_summary(k).action_type := 'I';
935 l := l_pay_summary_existing_rows.FIRST;
936 WHILE l IS NOT NULL
937 LOOP
938 IF(l_duration_summary(k).gap_level =
939 l_pay_summary_existing_rows(l).gap_level)
940 THEN
941 l_duration_summary(k).action_type := 'U';
942 l_duration_summary(k).gap_duration_summary_id :=
943 l_pay_summary_existing_rows(l).gap_duration_summary_id ;
944 l_duration_summary(k).object_version_number :=
945 l_pay_summary_existing_rows(l).object_version_number;
946 l_pay_summary_existing_rows(l).action_type :='U';
947 END IF;
948 l := l_pay_summary_existing_rows.NEXT(l);
949 END LOOP;
950 k := k+1;
951 END LOOP;
952 CLOSE csr_pay_level_summary;
953
954
955 l := l_ent_summary_existing_rows.FIRST;
956 WHILE l IS NOT NULL
957 LOOP
958 IF(l_ent_summary_existing_rows(l).action_type='D')
959 THEN
960 pqp_gds_api.delete_duration_summary
961 (p_gap_duration_summary_id
962 =>l_ent_summary_existing_rows(l).gap_duration_summary_id
963 ,p_object_version_number
964 =>l_ent_summary_existing_rows(l).object_version_number
965 );
966 END IF;
967 l := l_ent_summary_existing_rows.NEXT(l);
968 END LOOP;
969
970
971 l := l_pay_summary_existing_rows.FIRST;
972 WHILE l IS NOT NULL
973 LOOP
974 IF(l_pay_summary_existing_rows(l).action_type='D')
975 THEN
976 pqp_gds_api.delete_duration_summary
977 (p_gap_duration_summary_id
978 =>l_pay_summary_existing_rows(l).gap_duration_summary_id
979 ,p_object_version_number
980 =>l_pay_summary_existing_rows(l).object_version_number
981 );
982 END IF;
983 l := l_pay_summary_existing_rows.NEXT(l);
984 END LOOP;
985
986
987 write_duration_summary
988 (p_absence_summary_tbl => l_duration_summary
989 );
990
991
992 l_ent_summary_existing_rows.DELETE;
993 l_pay_summary_existing_rows.DELETE;
994 l_duration_summary.DELETE;
995
996 EXCEPTION
997 WHEN OTHERS THEN
998
999 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
1000 pqp_utilities.debug_others
1001 (l_proc_name
1002 ,l_proc_step
1003 );
1004 IF g_debug THEN
1005 debug('Leaving: '||l_proc_name,-999);
1006 END IF;
1007 fnd_message.raise_error;
1008 ELSE
1009 RAISE;
1010 END IF;
1011 END update_duration_summary ;
1012
1013
1014 PROCEDURE write_absence_summary
1015 (p_gap_absence_plan_id IN NUMBER
1016 ,p_assignment_id IN NUMBER
1017 ,p_entitlement_granted IN pqp_absval_pkg.t_entitlements
1018 ,p_entitlement_used_to_date IN pqp_absval_pkg.t_entitlements
1019 ,p_entitlement_remaining IN pqp_absval_pkg.t_entitlements
1020 ,p_fte IN NUMBER DEFAULT 1
1021 ,p_working_days_per_week IN NUMBER DEFAULT NULL
1022 ,p_entitlement_uom IN VARCHAR2
1023 ,p_update IN BOOLEAN
1024 )
1025 IS
1026
1027 l_proc_name VARCHAR2(61) := g_package_name||'write_absence_summary';
1028 l_proc_step NUMBER(20,10) ;
1029
1030 BEGIN
1031 g_debug := hr_utility.debug_enabled;
1032
1033 IF g_debug THEN
1034 debug_enter(l_proc_name);
1035 debug('p_gap_absence_plan_id:',p_gap_absence_plan_id);
1036 debug('p_assignment_id:',p_assignment_id);
1037 debug('p_fte:',p_fte);
1038 debug('p_working_days_per_week:',p_working_days_per_week);
1039 END IF;
1040
1041
1042 IF p_update
1043 THEN
1044 update_duration_summary
1045 (p_gap_absence_plan_id => p_gap_absence_plan_id
1046 ,p_assignment_id => p_assignment_id
1047 );
1048 ELSE
1049 create_duration_summary
1050 (p_gap_absence_plan_id => p_gap_absence_plan_id
1051 ,p_assignment_id => p_assignment_id
1052 );
1053 END IF ;
1054 IF g_debug THEN
1055 debug_exit(l_proc_name) ;
1056 END IF ;
1057
1058 EXCEPTION
1059 WHEN OTHERS THEN
1060
1061 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
1062 pqp_utilities.debug_others
1063 (l_proc_name
1064 ,l_proc_step
1065 );
1066 IF g_debug THEN
1067 debug('Leaving: '||l_proc_name,-999);
1068 END IF;
1069 fnd_message.raise_error;
1070 ELSE
1071 RAISE;
1072 END IF;
1073 END write_absence_summary ;
1074
1075
1076 PROCEDURE delete_absence_plan_details
1077 (p_assignment_id IN NUMBER -- unused
1078 ,p_business_group_id IN NUMBER -- unused
1079 ,p_plan_id IN NUMBER
1080 ,p_absence_id IN NUMBER
1081 ,p_delete_start_date IN DATE
1082 ,p_delete_end_date IN DATE
1083 ,p_error_code OUT NOCOPY NUMBER
1084 ,p_message OUT NOCOPY VARCHAR2
1085 )
1086 IS
1087
1088 CURSOR csr_gap_dur_sum_rows(p_gap_absence_plan_id NUMBER)
1089 IS
1090 SELECT gap_duration_summary_id ,
1091 object_version_number
1092 FROM pqp_gap_duration_summary
1093 WHERE gap_absence_plan_id = p_gap_absence_plan_id ;
1094
1095
1096 l_proc_step NUMBER(20,10);
1097 l_proc_name VARCHAR2( 61 ):=
1098 g_package_name||
1099 'delete_absence_plan_details';
1100
1101
1102 l_error_code fnd_new_messages.message_number%TYPE;
1103 l_error_message fnd_new_messages.message_text%TYPE;
1104 l_gap_daily_absences_exists csr_gap_daily_absences_exists%ROWTYPE;
1105 l_gap_absence_plan csr_gap_absence_plan%ROWTYPE;
1106 l_gap_dur_sum_rows csr_gap_dur_sum_rows%ROWTYPE;
1107
1108 BEGIN
1109
1110 g_debug := hr_utility.debug_enabled;
1111
1112 IF g_debug THEN
1113 debug_enter(l_proc_name);
1114 debug(p_assignment_id);
1115 debug(p_business_group_id);
1116 debug(p_plan_id);
1117 debug(p_absence_id);
1118 debug(p_delete_start_date);
1119 debug(p_delete_end_date);
1120 debug(p_error_code);
1121 debug(p_message);
1122 END IF;
1123
1124
1125 -- Set summary table switch
1126
1127 IF g_log_duration_summary is NULL
1128 THEN
1129
1130 IF g_debug THEN
1131 debug(l_proc_name, 12);
1132 END IF;
1133
1134 g_log_duration_summary :=
1135 PQP_UTILITIES.pqp_get_config_value
1136 ( p_business_group_id => p_business_group_id
1137 ,p_legislation_code => 'GB'
1138 ,p_column_name => 'PCV_INFORMATION10'
1139 ,p_information_category => 'PQP_GB_OSP_OMP_CONFIG'
1140 );
1141
1142 g_log_duration_summary := NVL(g_log_duration_summary,'DISABLE');
1143
1144 IF g_debug THEN
1145 debug('g_log_duration_summary' || g_log_duration_summary);
1146 END IF;
1147
1148 END IF;
1149
1150
1151
1152 OPEN csr_gap_absence_plan(p_absence_id, p_plan_id);
1153 FETCH csr_gap_absence_plan INTO l_gap_absence_plan;
1154
1155 IF csr_gap_absence_plan%FOUND
1156 THEN
1157 l_proc_step := 20;
1158 IF g_debug THEN
1159 debug(l_proc_name, l_proc_step);
1160 END IF;
1161
1162 DELETE
1163 FROM pqp_gap_daily_absences gda
1164 WHERE gda.gap_absence_plan_id = l_gap_absence_plan.gap_absence_plan_id
1165 AND gda.absence_date
1166 BETWEEN NVL(p_delete_start_date,gda.absence_date)
1167 AND NVL(p_delete_end_date,gda.absence_date);
1168
1169 l_proc_step := 30;
1170 IF g_debug THEN
1171 debug(SQL%ROWCOUNT);
1172 debug('pqp_gap_daily_absences rows deleted.');
1173 debug(l_proc_name, l_proc_step);
1174 END IF;
1175
1176 OPEN csr_gap_daily_absences_exists(l_gap_absence_plan.gap_absence_plan_id);
1177 FETCH csr_gap_daily_absences_exists INTO l_gap_daily_absences_exists;
1178 IF csr_gap_daily_absences_exists%NOTFOUND
1179 THEN
1180
1181 l_proc_step := 40;
1182 IF g_debug THEN
1183 debug(l_proc_name, l_proc_step);
1184 END IF;
1185
1186 DELETE
1187 FROM pqp_gap_absence_plans gap
1188 WHERE gap.gap_absence_plan_id = l_gap_absence_plan.gap_absence_plan_id;
1189
1190 -- Call delete using API as we need to log the delete events for all the
1191 -- rows for the summary table
1192
1193
1194 IF g_log_duration_summary = 'ENABLE'
1195 THEN
1196 IF g_debug THEN
1197 debug(l_proc_name, 45);
1198 END IF;
1199
1200 OPEN csr_gap_dur_sum_rows
1201 (p_gap_absence_plan_id =>l_gap_absence_plan.gap_absence_plan_id);
1202
1203 LOOP
1204 FETCH csr_gap_dur_sum_rows INTO l_gap_dur_sum_rows;
1205 EXIT WHEN csr_gap_dur_sum_rows %NOTFOUND ;
1206
1207 pqp_gds_api.delete_duration_summary
1208 (p_gap_duration_summary_id
1209 => l_gap_dur_sum_rows.gap_duration_summary_id
1210 ,p_object_version_number
1211 => l_gap_dur_sum_rows.object_version_number
1212 );
1213 END LOOP;
1214 CLOSE csr_gap_dur_sum_rows;
1215 END IF;
1216
1217
1218 l_proc_step := 50;
1219 IF g_debug THEN
1220 debug(SQL%ROWCOUNT);
1221 debug('pqp_gap_absence_plans rows deleted.');
1222 debug(l_proc_name, l_proc_step);
1223 END IF;
1224
1225 ELSE -- there are still some daily absences left ie was a partial delete
1226 -- a partial delete takes place when an end date has been changed
1227 -- such that it is less than the last daily absence date
1228 -- if it was a partial delete then p_delete_start_date must have been
1229 -- supplied in which case the new last gap daily absence date would
1230 -- p_delete_start_date - 1
1231
1232 pqp_gap_upd.upd
1233 (p_effective_date => p_delete_start_date - 1
1234 ,p_gap_absence_plan_id => l_gap_absence_plan.gap_absence_plan_id
1235 ,p_object_version_number => l_gap_absence_plan.object_version_number
1236 ,p_assignment_id => p_assignment_id
1237 ,p_absence_attendance_id => p_absence_id
1238 ,p_pl_id => p_plan_id
1239 ,p_last_gap_daily_absence_date => p_delete_start_date - 1
1240 );
1241
1242 IF g_log_duration_summary = 'ENABLE'
1243 THEN
1244
1245 IF g_debug THEN
1246 debug(l_proc_name, 55);
1247 END IF;
1248
1249 update_duration_summary
1250 (p_gap_absence_plan_id => l_gap_absence_plan.gap_absence_plan_id
1251 ,p_assignment_id => p_assignment_id
1252 );
1253 END IF;
1254
1255 END IF; --IF csr_gap_daily_absence%NOTFOUND THEN
1256 CLOSE csr_gap_daily_absences_exists;
1257
1258 l_proc_step := 60;
1259 IF g_debug THEN
1260 debug(l_proc_name, l_proc_step);
1261 END IF;
1262
1263 END IF; --IF csr_gap_absence_plan%FOUND
1264 CLOSE csr_gap_absence_plan;
1265
1266 IF g_debug THEN
1267 debug_exit(l_proc_name);
1268 END IF;
1269
1270 EXCEPTION
1271 WHEN OTHERS THEN
1272 -- Begin Change by Vaibhav Gupta (VAIBGUPT) on october 21st,2008
1273 -- Bug noumber- 7434754
1274 -- Reason .. the scenario of closing cursor in exception section is not
1275 -- taken care of. Hence if some exception is occurred and if the cursor
1276 -- is still open then it will be cause of failure of process further.
1277
1278
1279 IF csr_gap_absence_plan%ISOPEN then
1280 close csr_gap_absence_plan;
1281 END IF;
1282
1283 IF csr_gap_daily_absences_exists%ISOPEN then
1284 close csr_gap_daily_absences_exists;
1285 END IF;
1286
1287 IF csr_gap_dur_sum_rows%ISOPEN then
1288 close csr_gap_dur_sum_rows;
1289 END IF;
1290
1291 --end changes by Vaibhav Gupta
1292
1293 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
1294 debug_others(l_proc_name,l_proc_step);
1295 IF g_debug THEN
1296 debug('Leaving: '||l_proc_name,-999);
1297 END IF;
1298 fnd_message.raise_error;
1299 ELSE
1300 RAISE;
1301 END IF;
1302 END delete_absence_plan_details;
1303 --
1304 --This procedure gets all the plan extra information
1305 -- in one go and caches the same by plan id. Given that
1306 -- we need to call this once for every life event and
1307 -- every absence has two life events, it is quite likely
1308 -- that the same information will needed repeatedly.
1309 -- note the cache stores information of one plan at a time
1310 -- it is not a pl/sql table.
1311 --
1312 PROCEDURE get_plan_extra_info_n_cache_it
1313 (p_pl_id IN NUMBER
1314 ,p_plan_information IN OUT NOCOPY rec_plan_information
1315 ,p_business_group_id IN NUMBER
1316 ,p_assignment_id IN NUMBER
1317 ,p_effective_date IN DATE
1318 --,p_error_code OUT NOCOPY NUMBER
1319 --,p_message OUT NOCOPY VARCHAR2
1320 )
1321 IS
1322
1323 l_trunc_yn VARCHAR2(30);
1324 l_plan_information rec_plan_information;
1325 l_error_message fnd_new_messages.message_text%TYPE;
1326 l_error_code fnd_new_messages.message_number%TYPE;
1327 l_proc_step NUMBER(20,10);
1328 l_proc_name VARCHAR2(61):=
1329 g_package_name||'get_plan_extra_info_n_cache_it';
1330
1331 BEGIN
1332
1333 IF g_debug THEN
1334 debug_enter(l_proc_name);
1335 END IF;
1336
1337 IF g_debug THEN
1338 debug('Caching check:g_pl_id:'||fnd_number.number_to_canonical(g_pl_id));
1339 debug('Caching check:p_pl_id:'||fnd_number.number_to_canonical(g_pl_id));
1340 END IF;
1341
1342 IF g_pl_id IS NULL -- first time the function is called
1343 OR p_pl_id <> g_pl_id -- subsequent calls reload cache only if
1344 -- the plan id doesn't match
1345 THEN
1346
1347 -- these debugs don't require a if debug enabled if condition as these
1348 -- lines of code are not likely to be executed very frequently.
1349
1350 l_proc_step := 10;
1351 IF g_debug THEN
1352 debug(l_proc_name, 10);
1353 END IF;
1354
1355 IF g_debug THEN
1356 debug('PQP_GB_OSP_ABSENCE_PLAN_INFO');
1357 END IF;
1358
1359
1360 IF g_debug THEN
1361 debug('Before:'||'Absence Entitlement Sick Leave'||':'||
1362 p_plan_information.entitlement_parameters_UDT_id);
1363 END IF;
1364 l_error_code:=
1365 pqp_gb_osp_functions.pqp_get_plan_extra_info
1366 (p_pl_id => p_pl_id
1367 ,p_information_type => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
1368 ,p_segment_name => 'Absence Entitlement Sick Leave'
1369 ,p_value => p_plan_information.entitlement_parameters_UDT_id
1370 ,p_truncated_yes_no => l_trunc_yn
1371 ,p_error_msg => l_error_message
1372 );
1373 IF g_debug THEN
1374 debug('After:'||'Absence Entitlement Sick Leave'||':'||
1375 p_plan_information.entitlement_parameters_UDT_id);
1376 END IF;
1377 IF g_debug THEN
1378 debug(l_trunc_yn);
1379 END IF;
1380 IF l_error_code <> 0 THEN
1381 check_error_code(l_error_code,l_error_message);
1382 END IF;
1383
1384 l_proc_step := 20;
1385 IF g_debug THEN
1386 debug(l_proc_name, 20);
1387 END IF;
1388
1389 IF g_debug THEN
1390 debug('Before:'||'Absence Days'||':'||
1391 p_plan_information.absence_days_type);
1392 END IF;
1393 l_error_code:=
1394 pqp_gb_osp_functions.pqp_get_plan_extra_info
1395 (p_pl_id => p_pl_id
1396 ,p_information_type => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
1397 ,p_segment_name => 'Absence Days'
1398 ,p_value => p_plan_information.absence_days_type
1399 ,p_truncated_yes_no => l_trunc_yn
1400 ,p_error_msg => l_error_message
1401 );
1402 IF g_debug THEN
1403 debug('After:'||'Absence Days'||':'||
1404 p_plan_information.absence_days_type);
1405 END IF;
1406 IF g_debug THEN
1407 debug(l_trunc_yn);
1408 END IF;
1409 IF l_error_code <> 0 THEN
1410 check_error_code(l_error_code,l_error_message);
1411 END IF;
1412
1413 l_proc_step := 30;
1414 IF g_debug THEN
1415 debug(l_proc_name, 30);
1416 END IF;
1417
1418 IF g_debug THEN
1419 debug('Before:'||'Scheme Calendar Type'||':'||
1420 p_plan_information.scheme_period_type);
1421 END IF;
1422 l_error_code:=
1423 pqp_gb_osp_functions.pqp_get_plan_extra_info
1424 (p_pl_id => p_pl_id
1425 ,p_information_type => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
1426 ,p_segment_name => 'Scheme Calendar Type'
1427 ,p_value => p_plan_information.scheme_period_type
1428 ,p_truncated_yes_no => l_trunc_yn
1429 ,p_error_msg => l_error_message
1430 );
1431 IF g_debug THEN
1432 debug('After:'||'Scheme Calendar Type'||':'||
1433 p_plan_information.scheme_period_type);
1434 END IF;
1435 IF g_debug THEN
1436 debug(l_trunc_yn);
1437 END IF;
1438
1439 IF l_error_code <> 0 THEN
1440 check_error_code(l_error_code,l_error_message);
1441 END IF;
1442
1443 l_proc_step := 40;
1444 IF g_debug THEN
1445 debug(l_proc_name, 40);
1446 END IF;
1447
1448 IF g_debug THEN
1449 debug('Before:'||'Scheme Calendar Duration'||':'||
1450 p_plan_information.scheme_period_duration);
1451 END IF;
1452 l_error_code :=
1453 pqp_gb_osp_functions.pqp_get_plan_extra_info
1454 (p_pl_id => p_pl_id
1455 ,p_information_type => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
1456 ,p_segment_name => 'Scheme Calendar Duration'
1457 ,p_value => p_plan_information.scheme_period_duration
1458 ,p_truncated_yes_no => l_trunc_yn
1459 ,p_error_msg => l_error_message
1460 );
1461
1462 IF g_debug THEN
1463 debug('After:'||'Scheme Calendar Duration'||':'||
1464 p_plan_information.scheme_period_duration);
1465 END IF;
1466 IF g_debug THEN
1467 debug(l_trunc_yn);
1468 END IF;
1469 IF l_error_code <> 0 THEN
1470 check_error_code(l_error_code,l_error_message);
1471 END IF;
1472
1473 l_proc_step := 50;
1474 IF g_debug THEN
1475 debug(l_proc_name, 50);
1476 END IF;
1477
1478 IF g_debug THEN
1479 debug('Before:'||'Scheme Calendar UOM'||':'||
1480 p_plan_information.scheme_period_uom);
1481 END IF;
1482 l_error_code:=
1483 pqp_gb_osp_functions.pqp_get_plan_extra_info
1484 (p_pl_id => p_pl_id
1485 ,p_information_type => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
1486 ,p_segment_name => 'Scheme Calendar UOM'
1487 ,p_value => p_plan_information.scheme_period_uom
1488 ,p_truncated_yes_no => l_trunc_yn
1489 ,p_error_msg => l_error_message
1490 );
1491 IF g_debug THEN
1492 debug('After:'||'Scheme Calendar UOM'||':'||
1493 p_plan_information.scheme_period_uom);
1494 END IF;
1495 IF g_debug THEN
1496 debug(l_trunc_yn );
1497 END IF;
1498 IF l_error_code <> 0 THEN
1499 check_error_code(l_error_code,l_error_message);
1500 END IF;
1501
1502 l_proc_step := 60;
1503 IF g_debug THEN
1504 debug(l_proc_name, 60);
1505 END IF;
1506 --
1507 -- IF g_debug THEN
1508 -- debug('Before:'||'Absence Entitlement Sick Leave'||':'||
1509 -- p_plan_information.entitlement_parameters_UDT_id);
1510 -- END IF;
1511 -- l_error_code:=
1512 -- pqp_gb_osp_functions.pqp_get_plan_extra_info
1513 -- (p_pl_id => p_pl_id
1514 -- ,p_information_type => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
1515 -- ,p_segment_name => 'Absence Entitlement Sick Leave'
1516 -- ,p_value => p_plan_information.entitlement_parameters_UDT_id
1517 -- ,p_truncated_yes_no => l_trunc_yn
1518 -- ,p_error_msg => l_error_message
1519 -- );
1520 -- IF g_debug THEN
1521 -- debug('After:'||'Absence Entitlement Sick Leave'||':'||
1522 -- p_plan_information.entitlement_parameters_UDT_id);
1523 -- END IF;
1524 -- IF g_debug THEN
1525 -- debug(l_trunc_yn );
1526 -- END IF;
1527 -- IF l_error_code <> 0 THEN
1528 -- check_error_code(l_error_code,l_error_message);
1529 -- END IF;
1530 --
1531
1532 l_proc_step := 70;
1533 IF g_debug THEN
1534 debug(l_proc_name, 70);
1535 END IF;
1536
1537 IF g_debug THEN
1538 debug('Before:'||'Scheme Start Date'||':'||
1539 p_plan_information.scheme_period_start);
1540 END IF;
1541 l_error_code:=
1542 pqp_gb_osp_functions.pqp_get_plan_extra_info
1543 (p_pl_id => p_pl_id
1544 ,p_information_type => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
1545 ,p_segment_name => 'Scheme Start Date'
1546 ,p_value => p_plan_information.scheme_period_start
1547 ,p_truncated_yes_no => l_trunc_yn
1548 ,p_error_msg => l_error_message
1549 );
1550 IF g_debug THEN
1551 debug('After:'||'Scheme Start Date'||':'||
1552 p_plan_information.scheme_period_start);
1553 END IF;
1554 IF g_debug THEN
1555 debug(l_trunc_yn );
1556 END IF;
1557 IF l_error_code <> 0 THEN
1558 check_error_code(l_error_code,l_error_message);
1559 END IF;
1560
1561 l_proc_step := 80;
1562 IF g_debug THEN
1563 debug(l_proc_name, 80);
1564 END IF;
1565 --
1566 -- IF g_debug THEN
1567 -- debug('Before:'||'Absence Types List Name'||':'||
1568 --- p_plan_information.absence_types_list_name);
1569 -- END IF;
1570 -- l_error_code:=
1571 -- pqp_gb_osp_functions.pqp_get_plan_extra_info
1572 -- (p_pl_id => p_pl_id
1573 -- ,p_information_type => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
1574 -- ,p_segment_name => 'Absence Types List Name'
1575 -- ,p_value => p_plan_information.absence_types_list_name
1576 -- ,p_truncated_yes_no => l_trunc_yn
1577 -- ,p_error_msg => l_error_message
1578 -- );
1579 -- IF g_debug THEN
1580 -- debug('After:'||'Absence Types List Name'||':'||p_plan_information.absence_types_list_name);
1581 -- END IF;
1582 -- IF g_debug THEN
1583 -- debug(l_trunc_yn );
1584 -- END IF;
1585 -- IF l_error_code <> 0 THEN
1586 -- check_error_code(l_error_code,l_error_message);
1587 -- END IF;
1588 --
1589 l_proc_step := 90;
1590 IF g_debug THEN
1591 debug(l_proc_name, 90);
1592 END IF;
1593
1594 IF g_debug THEN
1595 debug('Before:'||'Absence Default Work Pattern'||':'||
1596 p_plan_information.default_work_pattern_name);
1597 END IF;
1598 l_error_code:=
1599 pqp_gb_osp_functions.pqp_get_plan_extra_info
1600 (p_pl_id => p_pl_id
1601 ,p_information_type => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
1602 ,p_segment_name => 'Absence Default Work Pattern'
1603 ,p_value => p_plan_information.default_work_pattern_name
1604 ,p_truncated_yes_no => l_trunc_yn
1605 ,p_error_msg => l_error_message
1606 );
1607 IF g_debug THEN
1608 debug('After:'||'Absence Default Work Pattern'||':'||
1609 p_plan_information.default_work_pattern_name);
1610 END IF;
1611 IF g_debug THEN
1612 debug(l_trunc_yn );
1613 END IF;
1614 IF l_error_code <> 0 THEN
1615 check_error_code(l_error_code,l_error_message);
1616 END IF;
1617
1618 l_proc_step := 100;
1619 IF g_debug THEN
1620 debug(l_proc_name, 100);
1621 END IF;
1622
1623 IF p_plan_information.default_work_pattern_name =
1624 'CONTRACT_LEVEL_WORK_PATTERN'
1625 THEN
1626
1627 p_plan_information.default_work_pattern_name :=
1628 get_contract_level_wp
1629 (p_business_group_id => p_business_group_id
1630 ,p_assignment_id => p_assignment_id
1631 ,p_effective_date => p_effective_date
1632 );
1633
1634 IF g_debug THEN
1635 debug('After:'||'DEF_WP_CONTRACT_LEVEL_WORK_PATTERN'||':'||
1636 p_plan_information.default_work_pattern_name);
1637 END IF;
1638
1639 IF p_plan_information.default_work_pattern_name IS NULL
1640 THEN
1641 hr_utility.set_message(8303, 'PQP_230000_INVALID_WORK_PAT');
1642 hr_utility.raise_error ;
1643 END IF;
1644
1645 END IF;
1646
1647 IF g_debug THEN
1648 debug(l_proc_name, 105);
1649 END IF;
1650
1651
1652
1653 IF g_debug THEN
1654 debug('Before:'||'Absence Entitlement Holidays'||':'||
1655 p_plan_information.entitlement_calendar_UDT_id);
1656 END IF;
1657 l_error_code:=
1658 pqp_gb_osp_functions.pqp_get_plan_extra_info
1659 (p_pl_id => p_pl_id
1660 ,p_information_type => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
1661 ,p_segment_name => 'Absence Entitlement Holidays'
1662 ,p_value => p_plan_information.entitlement_calendar_UDT_id
1663 ,p_truncated_yes_no => l_trunc_yn
1664 ,p_error_msg => l_error_message
1665 );
1666 IF g_debug THEN
1667 debug('After:'||'Absence Entitlement Holidays'||':'||
1668 p_plan_information.entitlement_calendar_UDT_id);
1669 END IF;
1670 IF g_debug THEN
1671 debug(l_trunc_yn );
1672 END IF;
1673 IF l_error_code <> 0 THEN
1674 check_error_code(l_error_code,l_error_message);
1675 END IF;
1676
1677 l_proc_step := 110;
1678 IF g_debug THEN
1679 debug(l_proc_name, 110);
1680 END IF;
1681
1682 IF g_debug THEN
1683 debug('Before:'||'Absence Daily Rate Calculation'||':'||
1684 p_plan_information.daily_rate_UOM);
1685 END IF;
1686 l_error_code:=
1687 pqp_gb_osp_functions.pqp_get_plan_extra_info
1688 (p_pl_id => p_pl_id
1689 ,p_information_type => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
1690 ,p_segment_name => 'Absence Daily Rate Calculation'
1691 ,p_value => p_plan_information.daily_rate_UOM
1692 ,p_truncated_yes_no => l_trunc_yn
1693 ,p_error_msg => l_error_message
1694 );
1695 IF g_debug THEN
1696 debug('After:'||'Absence Daily Rate Calculation'||':'||
1697 p_plan_information.daily_rate_UOM);
1698 END IF;
1699 IF g_debug THEN
1700 debug(l_trunc_yn );
1701 END IF;
1702 IF l_error_code <> 0 THEN
1703 check_error_code(l_error_code,l_error_message);
1704 END IF;
1705
1706 l_proc_step := 120;
1707 IF g_debug THEN
1708 debug(l_proc_name, 120);
1709 END IF;
1710 --
1711 -- IF g_debug THEN
1712 -- debug('Before:'||'Plan Name'||':'||p_plan_information.plan_name);
1713 -- END IF;
1714 -- l_error_code:=
1715 -- pqp_gb_osp_functions.pqp_get_plan_extra_info
1716 -- (p_pl_id => p_pl_id
1717 -- ,p_information_type => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
1718 -- ,p_segment_name => 'Plan Name'
1719 -- ,p_value => p_plan_information.plan_name
1720 -- ,p_truncated_yes_no => l_trunc_yn
1721 -- ,p_error_msg => l_error_message
1722 -- );
1723 -- IF g_debug THEN
1724 -- debug('After:'||'Plan Name'||':'||p_plan_information.plan_name);
1725 -- END IF;
1726 -- IF g_debug THEN
1727 -- debug(l_trunc_yn );
1728 -- END IF;
1729 -- IF l_error_code <> 0 THEN
1730 -- check_error_code(l_error_code,l_error_message);
1731 -- END IF;
1732 --
1733 l_proc_step := 130;
1734 IF g_debug THEN
1735 debug(l_proc_name, 130);
1736 END IF;
1737
1738 IF g_debug THEN
1739 debug('Before:'||'Absence Overlap Rule'||':'||
1740 p_plan_information.absence_overlap_rule);
1741 END IF;
1742 l_error_code:=
1743 pqp_gb_osp_functions.pqp_get_plan_extra_info
1744 (p_pl_id => p_pl_id
1745 ,p_information_type => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
1746 ,p_segment_name => 'Absence Overlap Rule'
1747 ,p_value => p_plan_information.absence_overlap_rule
1748 ,p_truncated_yes_no => l_trunc_yn
1749 ,p_error_msg => l_error_message
1750 );
1751 IF g_debug THEN
1752 debug('After:'||'Absence Overlap Rule'||':'||
1753 p_plan_information.absence_overlap_rule);
1754 END IF;
1755 IF g_debug THEN
1756 debug(l_trunc_yn );
1757 END IF;
1758 IF l_error_code <> 0 THEN
1759 check_error_code(l_error_code,l_error_message);
1760 END IF;
1761
1762 l_proc_step := 140;
1763 IF g_debug THEN
1764 debug(l_proc_name, 140);
1765 END IF;
1766
1767 IF g_debug THEN
1768 debug('Before:'||'Absence Pay Plan Category'||':'||
1769 p_plan_information.absence_pay_plan_category);
1770 END IF;
1771 l_error_code:=
1772 pqp_gb_osp_functions.pqp_get_plan_extra_info
1773 (p_pl_id => p_pl_id
1774 ,p_information_type => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
1775 ,p_segment_name => 'Absence Pay Plan Category'
1776 ,p_value => p_plan_information.absence_pay_plan_category
1777 ,p_truncated_yes_no => l_trunc_yn
1778 ,p_error_msg => l_error_message
1779 );
1780 IF g_debug THEN
1781 debug('After:'||'Absence Pay Plan Category'||':'||
1782 p_plan_information.absence_pay_plan_category);
1783 END IF;
1784 IF g_debug THEN
1785 debug(l_trunc_yn );
1786 END IF;
1787 IF l_error_code <> 0 THEN
1788 check_error_code(l_error_code,l_error_message);
1789 END IF;
1790 --
1791 -- IF g_debug THEN
1792 -- debug('Before:'||'Absence Entitlement List Name'||':'||p_plan_information.entitlement_band_names_list);
1793 -- END IF;
1794 -- l_error_code:=
1795 -- pqp_gb_osp_functions.pqp_get_plan_extra_info
1796 -- (p_pl_id => p_pl_id
1797 -- ,p_information_type => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
1798 -- ,p_segment_name => 'Absence Entitlement List Name'
1799 -- ,p_value => p_plan_information.entitlement_band_names_list
1800 -- ,p_truncated_yes_no => l_trunc_yn
1801 -- ,p_error_msg => l_error_message
1802 -- );
1803 -- IF g_debug THEN
1804 -- debug('After:'||'Absence Entitlement List Name'||':'||p_plan_information.entitlement_band_names_list);
1805 -- END IF;
1806 -- IF g_debug THEN
1807 -- debug(l_trunc_yn );
1808 -- END IF;
1809 -- IF l_error_code <> 0 THEN
1810 -- check_error_code(l_error_code,l_error_message);
1811 -- END IF;
1812 --
1813 IF g_debug THEN
1814 debug('Before:'||'Absence Entitlement Cal Rules'||':'||
1815 p_plan_information.calendar_rule_names_list);
1816 END IF;
1817 l_error_code:=
1818 pqp_gb_osp_functions.pqp_get_plan_extra_info
1819 (p_pl_id => p_pl_id
1820 ,p_information_type => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
1821 ,p_segment_name => 'Absence Entitlement Cal Rules'
1822 ,p_value => p_plan_information.calendar_rule_names_list
1823 ,p_truncated_yes_no => l_trunc_yn
1824 ,p_error_msg => l_error_message
1825 );
1826 IF g_debug THEN
1827 debug('After:'||'Absence Entitlement Cal Rules'||':'||
1828 p_plan_information.calendar_rule_names_list);
1829 END IF;
1830 IF g_debug THEN
1831 debug(l_trunc_yn );
1832 END IF;
1833 IF l_error_code <> 0 THEN
1834 check_error_code(l_error_code,l_error_message);
1835 END IF;
1836
1837 -- Added the two segments for civil Service Scheme
1838
1839 IF g_debug THEN
1840 debug('Before:'||'Dual Rolling Period Duration:'||
1841 p_plan_information.dual_rolling_period_duration);
1842 END IF;
1843 l_error_code:=
1844 pqp_gb_osp_functions.pqp_get_plan_extra_info
1845 (p_pl_id => p_pl_id
1846 ,p_information_type => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
1847 ,p_segment_name => 'Dual Rolling Period Duration'
1848 ,p_value => p_plan_information.dual_rolling_period_duration
1849 ,p_truncated_yes_no => l_trunc_yn
1850 ,p_error_msg => l_error_message
1851 );
1852 IF g_debug THEN
1853 debug('After:'||'Dual Rolling Period Duration:'||
1854 p_plan_information.dual_rolling_period_duration);
1855 END IF;
1856 IF g_debug THEN
1857 debug(l_trunc_yn );
1858 END IF;
1859 IF l_error_code <> 0 THEN
1860 check_error_code(l_error_code,l_error_message);
1861 END IF;
1862
1863
1864 IF g_debug THEN
1865 debug('Before:'||'Dual Rolling Period UOM:'||
1866 p_plan_information.dual_rolling_period_uom);
1867 END IF;
1868 l_error_code:=
1869 pqp_gb_osp_functions.pqp_get_plan_extra_info
1870 (p_pl_id => p_pl_id
1871 ,p_information_type => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
1872 ,p_segment_name => 'Dual Rolling Period UOM'
1873 ,p_value => p_plan_information.dual_rolling_period_uom
1874 ,p_truncated_yes_no => l_trunc_yn
1875 ,p_error_msg => l_error_message
1876 );
1877 IF g_debug THEN
1878 debug('After:'||'Dual Rolling Period UOM:'||
1879 p_plan_information.dual_rolling_period_uom);
1880 END IF;
1881 IF g_debug THEN
1882 debug(l_trunc_yn );
1883 END IF;
1884 IF l_error_code <> 0 THEN
1885 check_error_code(l_error_code,l_error_message);
1886 END IF;
1887
1888 -- FOR LG/PT
1889
1890 IF g_debug THEN
1891 debug('Before:'||'Enable Entitlement Proration:'||
1892 p_plan_information.track_part_timers);
1893 END IF;
1894 l_error_code:=
1895 pqp_gb_osp_functions.pqp_get_plan_extra_info
1896 (p_pl_id => p_pl_id
1897 ,p_information_type => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
1898 ,p_segment_name => 'Enable Entitlement Proration'
1899 ,p_value => p_plan_information.track_part_timers
1900 ,p_truncated_yes_no => l_trunc_yn
1901 ,p_error_msg => l_error_message
1902 );
1903 IF g_debug THEN
1904 debug('After:'||'Enable Entitlement Proration:'||
1905 p_plan_information.track_part_timers);
1906 END IF;
1907 IF g_debug THEN
1908 debug(l_trunc_yn );
1909 END IF;
1910 IF l_error_code <> 0 THEN
1911 check_error_code(l_error_code,l_error_message);
1912 END IF;
1913
1914
1915 IF g_debug THEN
1916 debug('Before:'||'Absence Schedule Work Pattern:'||
1917 p_plan_information.absence_schedule_work_pattern);
1918 END IF;
1919 l_error_code:=
1920 pqp_gb_osp_functions.pqp_get_plan_extra_info
1921 (p_pl_id => p_pl_id
1922 ,p_information_type => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
1923 ,p_segment_name => 'Absence Schedule Work Pattern'
1924 ,p_value => p_plan_information.absence_schedule_work_pattern
1925 ,p_truncated_yes_no => l_trunc_yn
1926 ,p_error_msg => l_error_message
1927 );
1928 IF g_debug THEN
1929 debug('After:'||'Absence Schedule Work Pattern:'||
1930 p_plan_information.absence_schedule_work_pattern);
1931 END IF;
1932 IF g_debug THEN
1933 debug(l_trunc_yn );
1934 END IF;
1935 IF l_error_code <> 0 THEN
1936 check_error_code(l_error_code,l_error_message);
1937 END IF;
1938
1939 IF p_plan_information.absence_schedule_work_pattern =
1940 'CONTRACT_LEVEL_WORK_PATTERN'
1941 THEN
1942
1943 p_plan_information.absence_schedule_work_pattern:=
1944 get_contract_level_wp
1945 (p_business_group_id => p_business_group_id
1946 ,p_assignment_id => p_assignment_id
1947 ,p_effective_date => p_effective_date
1948 );
1949
1950 IF g_debug THEN
1951 debug('After:'||'ABS_SCHDL_CONTRACT_LEVEL_WORK_PATTERN'||':'||
1952 p_plan_information.absence_schedule_work_pattern);
1953 END IF;
1954
1955 IF p_plan_information.default_work_pattern_name IS NULL
1956 THEN
1957 hr_utility.set_message(8303, 'PQP_230000_INVALID_WORK_PAT');
1958 hr_utility.raise_error ;
1959 END IF;
1960
1961
1962 END IF;
1963
1964
1965
1966
1967
1968 IF g_debug THEN
1969 debug(l_proc_name, 145);
1970 END IF;
1971
1972 IF g_debug THEN
1973 debug('Before:'||'Plan Types to extend Rolling Period'||
1974 p_plan_information.plan_types_to_extend_period);
1975 END IF;
1976 l_error_code:=
1977 pqp_gb_osp_functions.pqp_get_plan_extra_info
1978 (p_pl_id => p_pl_id
1979 ,p_information_type => 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
1980 ,p_segment_name => 'Plan Types to Extend Period'
1981 ,p_value => p_plan_information.plan_types_to_extend_period
1982 ,p_truncated_yes_no => l_trunc_yn
1983 ,p_error_msg => l_error_message
1984 );
1985 IF g_debug THEN
1986 debug('After:'||'Plan Types to extend Rolling Period:'||
1987 p_plan_information.plan_types_to_extend_period);
1988 END IF;
1989 IF g_debug THEN
1990 debug(l_trunc_yn );
1991 END IF;
1992 IF l_error_code <> 0 THEN
1993 check_error_code(l_error_code,l_error_message);
1994 END IF;
1995
1996
1997
1998
1999
2000 l_proc_step := 150;
2001 IF g_debug THEN
2002 debug(l_proc_name, 150);
2003 debug('Caching Id:g_pl_id:'||fnd_number.number_to_canonical(g_pl_id));
2004 debug('Caching Id:p_pl_id:'||fnd_number.number_to_canonical(p_pl_id));
2005 END IF;
2006
2007 g_pl_id := p_pl_id; -- set at the end only after calls have passed successfully.
2008 g_plan_information := p_plan_information;
2009
2010 l_proc_step := 160;
2011 IF g_debug THEN
2012 debug(l_proc_name, 160);
2013 END IF;
2014
2015 ELSE -- p_pl_id = g_pl_id matches with the cached one
2016
2017 -- so return plan_information from the cached copy
2018
2019 p_plan_information := g_plan_information;
2020
2021 END IF; -- IF g_pl_id IS NULL OR p_pl_id <> g_pl_id
2022
2023 l_proc_step := 170;
2024 IF g_debug THEN
2025 debug(l_proc_name, 170);
2026 END IF;
2027 IF g_debug THEN
2028 debug_exit(l_proc_name);
2029 END IF;
2030
2031 EXCEPTION
2032 WHEN OTHERS THEN
2033 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
2034 debug_others
2035 (l_proc_name
2036 ,l_proc_step
2037 );
2038 IF g_debug THEN
2039 debug('Leaving: '||l_proc_name,-999);
2040 END IF;
2041 -- p_message := SQLERRM;
2042 -- p_error_code := -1;
2043 fnd_message.raise_error;
2044 ELSE
2045 RAISE;
2046 END IF;
2047 END get_plan_extra_info_n_cache_it;
2048
2049 --
2050 -- Gets value from the plsql table ff_exec
2051 --
2052 PROCEDURE get_param_value
2053 (p_output_type IN ff_exec.outputs_t
2054 ,p_name IN VARCHAR2
2055 ,p_datatype OUT NOCOPY VARCHAR2
2056 ,p_value OUT NOCOPY VARCHAR2
2057 -- ,p_error_code OUT NOCOPY NUMBER
2058 -- ,p_message OUT NOCOPY VARCHAR2
2059 )
2060 IS
2061 l_proc_step NUMBER(20,10);
2062 l_proc_name VARCHAR2( 61 )
2063 := g_package_name ||
2064 'get_param_value';
2065 BEGIN
2066 l_proc_step := 10;
2067 IF g_debug THEN
2068 debug(l_proc_name, 10);
2069 END IF;
2070
2071 FOR i IN 1 .. p_output_type.COUNT
2072 LOOP
2073 IF p_output_type( i ).NAME = p_name
2074 THEN
2075 p_datatype := p_output_type( i ).datatype;
2076 p_value := p_output_type( i ).VALUE;
2077 END IF;
2078 END LOOP;
2079
2080 EXCEPTION
2081 WHEN OTHERS THEN
2082 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
2083 debug_others
2084 (l_proc_name
2085 ,l_proc_step
2086 );
2087 IF g_debug THEN
2088 debug('Leaving: '||l_proc_name,-999);
2089 END IF;
2090 -- p_message := SQLERRM;
2091 -- p_error_code := -1;
2092 fnd_message.raise_error;
2093 ELSE
2094 RAISE;
2095 END IF;
2096 END get_param_value;
2097 --
2098 --gets part day absence based on absence id
2099 --
2100 PROCEDURE get_absence_part_days
2101 (p_absence_id IN NUMBER
2102 ,p_part_start_day OUT NOCOPY NUMBER
2103 ,p_part_end_day OUT NOCOPY NUMBER
2104 ,p_part_day_UOM OUT NOCOPY VARCHAR2
2105 )
2106 IS
2107 --
2108 CURSOR csr_absence_part_days
2109 (p_absence_attendance_id IN
2110 per_absence_attendances.absence_attendance_id%TYPE
2111 )
2112 IS
2113 SELECT abs_information1 -- fraction of start day
2114 ,abs_information2 -- fraction of end day
2115 ,abs_information3 -- UOM of the fraction
2116 FROM per_absence_attendances
2117 WHERE abs_information_category = 'GB_PQP_OSP_OMP_PART_DAYS'
2118 AND absence_attendance_id = p_absence_attendance_id;
2119
2120
2121 l_absence_part_days csr_absence_part_days%ROWTYPE;
2122
2123 l_proc_step NUMBER(20,10);
2124 l_proc_name VARCHAR2(61):=
2125 g_package_name||
2126 'get_absence_part_days';
2127 BEGIN
2128 IF g_debug THEN
2129 debug_enter(l_proc_name);
2130 debug(p_absence_id);
2131 END IF;
2132
2133 OPEN csr_absence_part_days(p_absence_id);
2134 FETCH csr_absence_part_days INTO l_absence_part_days;
2135 CLOSE csr_absence_part_days;
2136
2137 l_proc_step := 20;
2138 IF g_debug THEN
2139 debug(l_proc_name, 20);
2140 debug(l_absence_part_days.abs_information1);
2141 END IF;
2142
2143 p_part_start_day :=
2144 fnd_number.canonical_to_number(l_absence_part_days.abs_information1);
2145
2146 l_proc_step := 30;
2147 IF g_debug THEN
2148 debug(p_part_start_day);
2149 debug(l_proc_name, 30);
2150 debug(l_absence_part_days.abs_information2);
2151 END IF;
2152
2153 p_part_end_day :=
2154 fnd_number.canonical_to_number(l_absence_part_days.abs_information2);
2155
2156 l_proc_step := 40;
2157 IF g_debug THEN
2158 debug(p_part_end_day);
2159 debug(l_proc_name, 40);
2160 debug(l_absence_part_days.abs_information3);
2161 END IF;
2162
2163 p_part_day_UOM :=
2164 l_absence_part_days.abs_information3;
2165
2166 IF g_debug THEN
2167 debug(p_part_day_UOM);
2168 debug_exit(l_proc_name);
2169 END IF;
2170
2171 EXCEPTION
2172 WHEN OTHERS THEN
2173 p_part_start_day := NULL;
2174 p_part_end_day := NULL;
2175 p_part_day_UOM := NULL;
2176 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
2177 debug_others
2178 (l_proc_name
2179 ,l_proc_step
2180 );
2181 IF g_debug THEN
2182 debug('Leaving: '||l_proc_name,-999);
2183 END IF;
2184 fnd_message.raise_error;
2185 ELSE
2186 RAISE;
2187 END IF;
2188 END get_absence_part_days;
2189 --
2190 --This function sums up the duration for each level of entitlement
2191 --for a given assignment, plan type and date range.It is the lowest
2192 --level function used to derive the absence balance. The return
2193 --value is an error code, the summed up information is returned
2194 --as an out parameter for .
2195 --
2196 PROCEDURE get_absences_taken
2197 (p_assignment_id IN NUMBER
2198 ,p_pl_typ_id IN NUMBER
2199 ,p_range_from_date IN DATE --not absence start and end dates
2200 ,p_range_to_date IN DATE --period for which sum is taken
2201 ,p_absences_taken IN OUT NOCOPY pqp_absval_pkg.t_entitlements
2202 -- ,p_message OUT NOCOPY VARCHAR2
2203 ) --RETURN NUMBER
2204 IS
2205 --Person level Absence Aggregation changes
2206
2207 -- declaration of record type for ref cursor return type
2208 TYPE r_absences_taken_typ IS RECORD
2209 (
2210 level_of_entitlement VARCHAR2(30),
2211 sum_of_duration NUMBER,
2212 sum_of_duration_in_hours NUMBER,
2213 sum_of_duration_per_week NUMBER,
2214 sum_of_fte_hours NUMBER
2215 );
2216
2217 TYPE csrv_absences_taken_typ IS REF CURSOR RETURN r_absences_taken_typ;
2218 csrv_absences_taken csrv_absences_taken_typ; -- declare cursor variable
2219 l_asg_abs_rec r_absences_taken_typ; -- declare record type variable
2220
2221
2222 l_error_code fnd_new_messages.message_number%TYPE;
2223 l_error_message fnd_new_messages.message_text%TYPE;
2224
2225 l_person_id per_all_people_f.PERSON_ID%TYPE;
2226 l_count PLS_INTEGER;
2227 l_prev_date DATE;
2228 l_proc_step NUMBER(20,10);
2229 l_proc_name VARCHAR2(61):=
2230 g_package_name||'get_absences_taken';
2231
2232 -- nocopy changes
2233 l_absences_taken_nc pqp_absval_pkg.t_entitlements;
2234
2235 BEGIN
2236 IF g_debug THEN
2237 debug_enter(l_proc_name);
2238 debug(p_assignment_id);
2239 debug(p_pl_typ_id);
2240 debug(p_range_from_date);
2241 debug(p_range_to_date);
2242 END IF;
2243
2244 -- nocopy changes
2245 l_absences_taken_nc := p_absences_taken;
2246 l_count := 0;
2247 l_proc_step := 10;
2248
2249 -- Person Level Absence Changes
2250
2251 -- Retrieve the option chosen for Deduct Absence Taken for in Config Value');
2252
2253 IF g_debug THEN
2254 debug(l_proc_name,l_proc_step);
2255 debug('g_deduct_absence_for:'||g_deduct_absence_for);
2256 END IF;
2257 --open the ref cursor as per the value in the g_deduct_absence_for
2258
2259 IF (g_deduct_absence_for = 'PRIMASGCURPOS')
2260 THEN
2261 l_proc_step := 20;
2262 IF g_debug THEN
2263 debug(l_proc_name,l_proc_step);
2264 END IF;
2265 OPEN csrv_absences_taken FOR
2266 SELECT gda.level_of_entitlement level_of_entitlement
2267 ,SUM(gda.duration) sum_of_duration
2268 ,SUM(gda.duration_in_hours) sum_of_duration_in_hours
2269 ,SUM(gda.duration/gda.working_days_per_week) sum_of_duration_per_week
2270 ,SUM(gda.duration_in_hours/fte) sum_of_fte_hours
2271 FROM pqp_gap_absence_plans gap
2272 ,ben_pl_f pln
2273 ,pqp_gap_daily_absences gda
2274 WHERE gap.assignment_id IN -- automatically makes the assignment list distinct
2275 (SELECT other_asg.assignment_id
2276 FROM per_all_assignments_f this_asg
2277 ,per_all_assignments_f other_asg
2278 WHERE this_asg.assignment_id = p_assignment_id
2279 AND other_asg.person_id = this_asg.person_id
2280 AND other_asg.primary_flag = 'Y'
2281 AND other_asg.period_of_service_id = this_asg.period_of_service_id
2282 )
2283 AND gda.gap_absence_plan_id = gap.gap_absence_plan_id
2284 AND pln.pl_id = gap.pl_id
2285 AND p_range_to_date
2286 BETWEEN pln.effective_start_date AND pln.effective_end_date
2287 AND pln.pl_typ_id = p_pl_typ_id
2288 AND gda.absence_date
2289 BETWEEN p_range_from_date AND p_range_to_date
2290 GROUP BY level_of_entitlement;
2291
2292
2293 ELSIF (g_deduct_absence_for = 'PRIMASGALLPOS')
2294 THEN
2295 -- The following ref cursor will pick all the primary assignments in the
2296 -- all the periods of service for the person and sum up all the absence
2297 -- entitlements
2298 l_proc_step := 30;
2299 IF g_debug THEN
2300 debug(l_proc_name,l_proc_step);
2301 END IF;
2302 SELECT asg.person_id
2303 INTO l_person_id
2304 FROM per_all_assignments_f asg
2305 WHERE asg.assignment_id = p_assignment_id
2306 AND ROWNUM < 2;
2307 IF g_debug THEN
2308 debug('l_person_id:' ,l_person_id);
2309 END IF;
2310 OPEN csrv_absences_taken FOR
2311 SELECT gda.level_of_entitlement level_of_entitlement
2312 ,SUM(gda.duration) sum_of_duration
2313 ,SUM(gda.duration_in_hours) sum_of_duration_in_hours
2314 ,SUM(gda.duration/gda.working_days_per_week) sum_of_duration_per_week
2315 ,SUM(gda.duration_in_hours/fte) sum_of_fte_hours
2316 FROM pqp_gap_absence_plans gap
2317 ,ben_pl_f pln
2318 ,pqp_gap_daily_absences gda
2319 WHERE gap.assignment_id IN -- automatically makes the assignment list distinct
2320 (SELECT asg.assignment_id
2321 FROM per_all_assignments_f asg
2322 WHERE asg.person_id = l_person_id
2323 AND asg.primary_flag = 'Y'
2324 )
2325 AND gda.gap_absence_plan_id = gap.gap_absence_plan_id
2326 AND pln.pl_id = gap.pl_id
2327 AND p_range_to_date
2328 BETWEEN pln.effective_start_date AND pln.effective_end_date
2329 AND pln.pl_typ_id = p_pl_typ_id
2330 AND gda.absence_date
2331 BETWEEN p_range_from_date AND p_range_to_date
2332
2333 GROUP BY level_of_entitlement;
2334 ELSE -- IF g_deduct_absence_for = NULL / Current Primary Assignemnt only
2335 -- The following ref cursor will sum up all the absence
2336 -- entitlements held against the current primary assignments
2337 -- This is the default functionality.
2338 l_proc_step := 40;
2339 IF g_debug THEN
2340 debug(l_proc_name,l_proc_step);
2341 END IF;
2342 OPEN csrv_absences_taken FOR
2343 SELECT gda.level_of_entitlement level_of_entitlement
2344 ,SUM(gda.duration) sum_of_duration
2345 ,SUM(gda.duration_in_hours) sum_of_duration_in_hours
2346 ,SUM(gda.duration/gda.working_days_per_week) sum_of_duration_per_week
2347 -- LG/PT
2348 ,SUM(gda.duration_in_hours/fte) sum_of_fte_hours
2349 FROM pqp_gap_absence_plans gap
2350 ,ben_pl_f pln
2351 ,pqp_gap_daily_absences gda
2352 WHERE gap.assignment_id = p_assignment_id
2353 AND gda.gap_absence_plan_id = gap.gap_absence_plan_id
2354 AND pln.pl_id = gap.pl_id
2355 AND p_range_to_date
2356 BETWEEN pln.effective_start_date AND pln.effective_end_date
2357 AND pln.pl_typ_id = p_pl_typ_id
2358 AND gda.absence_date BETWEEN p_range_from_date
2359 AND p_range_to_date
2360 GROUP BY level_of_entitlement;
2361
2362 END IF;
2363
2364
2365 l_proc_step := 50;
2366 IF g_debug THEN
2367 debug(l_proc_name, 50);
2368 END IF;
2369
2370 LOOP
2371 FETCH csrv_absences_taken INTO l_asg_abs_rec;
2372 EXIT WHEN csrv_absences_taken%NOTFOUND;
2373
2374 l_count:= l_count + 1;
2375
2376 IF g_debug THEN
2377 debug(l_proc_name, 50+(l_count/1000));
2378 debug('level_of_entitlement:'||l_asg_abs_rec.level_of_entitlement);
2379 debug('sum_of_duration:'||l_asg_abs_rec.sum_of_duration);
2380 debug('sum_of_duration_in_hours:'||
2381 l_asg_abs_rec.sum_of_duration_in_hours);
2382 debug('sum_of_duration_per_week:'||
2383 l_asg_abs_rec.sum_of_duration_per_week);
2384 debug('sum_of_fte_hours:'||
2385 l_asg_abs_rec.sum_of_fte_hours);
2386 END IF;
2387
2388 p_absences_taken(l_count).band := l_asg_abs_rec.level_of_entitlement;
2389 p_absences_taken(l_count).duration := l_asg_abs_rec.sum_of_duration;
2390 p_absences_taken(l_count).duration_in_hours :=
2391 l_asg_abs_rec.sum_of_duration_in_hours;
2392 p_absences_taken(l_count).duration_per_week :=
2393 l_asg_abs_rec.sum_of_duration_per_week ;
2394 p_absences_taken(l_count).fte_hours :=
2395 l_asg_abs_rec.sum_of_fte_hours ;
2396 END LOOP;
2397
2398 l_proc_step := 60;
2399 IF g_debug THEN
2400 debug(l_proc_name, 60);
2401 END IF;
2402
2403 IF g_debug THEN
2404 debug_exit(l_proc_name);
2405 END IF;
2406 --RETURN l_error_code;
2407
2408 EXCEPTION
2409 WHEN OTHERS THEN
2410 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
2411 debug_others
2412 (l_proc_name
2413 ,l_proc_step
2414 );
2415 IF g_debug THEN
2416 debug('Leaving: '||l_proc_name,-999);
2417 END IF;
2418 -- p_message := SQLERRM;
2419 -- p_error_code := -1;
2420 fnd_message.raise_error;
2421 ELSE
2422 RAISE;
2423 END IF;
2424 END get_absences_taken;
2425 --
2426 --
2427 --
2428 FUNCTION get_adjusted_scheme_start_date
2429 (p_assignment_id IN NUMBER
2430 ,p_scheme_start_date IN DATE
2431 ,p_pl_typ_id IN NUMBER
2432 ,p_scheme_period_overlap_rule IN VARCHAR2
2433 -- ,p_error_code OUT NOCOPY NUMBER
2434 -- ,p_message OUT NOCOPY VARCHAR2
2435 ) RETURN DATE
2436 IS
2437
2438 CURSOR csr_adjusted_scheme_start_date
2439 (p_assignment_id per_all_assignments_f.assignment_id%TYPE
2440 ,p_pl_typ_id ben_pl_typ_f.pl_typ_id%TYPE
2441 ,p_scheme_start_date DATE
2442 ) IS
2443 SELECT
2444 DECODE(p_scheme_period_overlap_rule
2445 ,'NC',abs.date_end+1
2446 ,'FC',abs.date_start
2447 )
2448 FROM pqp_gap_absence_plans gap
2449 ,per_absence_attendances abs
2450 ,ben_pl_f pln
2451 WHERE gap.assignment_id = p_assignment_id --an absence for this assignment
2452 AND pln.pl_typ_id = p_pl_typ_id --which is relevant, ie enrolled
2453 AND gap.pl_id = pln.pl_id --into a plan of atleast the same
2454 --plan type as the current one
2455 AND abs.absence_attendance_id = gap.absence_attendance_id
2456 AND abs.date_start < p_scheme_start_date --and which starts before
2457 AND abs.date_end >= p_scheme_start_date-- and ends on or after the
2458 -- scheme start date.
2459 ;
2460
2461 l_adjusted_scheme_start_date DATE;
2462 l_error_code fnd_new_messages.message_number%TYPE;
2463 l_error_message fnd_new_messages.message_text%TYPE;
2464 l_proc_step NUMBER(20,10);
2465 l_proc_name VARCHAR2(61):=
2466 g_package_name||'get_adjusted_scheme_start_date';
2467 BEGIN
2468 IF g_debug THEN
2469 debug_enter(l_proc_name);
2470 END IF;
2471
2472 --
2473 -- to derive the adjusted start date we need to
2474 -- 1. check if the current scheme start date falls in between an absence
2475 -- TAKE note: its not just any overlapping absence,
2476 -- its an absence that was enrolled into a plan of the same plan type.
2477 -- 2. if it does not then adjusted scheme start date is the same
2478 -- as the scheme start date
2479 -- 3. if it does the adjusted scheme start daye depends on the overlap rule
2480 -- a) if the overlap rule is Split (SC)
2481 -- then the adjusted scheme start date is the same the scheme start date
2482 -- b) if the overlap rule is Inlcude (FC)
2483 -- then the adjusted scheme start date is the start of the overlapping absence
2484 -- c) if the overlap rule is Exclude (NC)
2485 -- then the adjusted scheme start date is the first day after the end of the
2486 -- overlapping absence, ie the new year does not begin untill the employee
2487 -- returns to work.
2488 --
2489 OPEN csr_adjusted_scheme_start_date
2490 (p_assignment_id => p_assignment_id
2491 ,p_pl_typ_id => p_pl_typ_id
2492 ,p_scheme_start_date => p_scheme_start_date
2493 );
2494 FETCH csr_adjusted_scheme_start_date INTO l_adjusted_scheme_start_date;
2495 IF csr_adjusted_scheme_start_date%NOTFOUND
2496 THEN
2497 -- ie no overlapping absence was found, the scheme start date remains
2498 -- unchanged.
2499 l_proc_step := 10;
2500 IF g_debug THEN
2501 debug(l_proc_name, l_proc_step);
2502 END IF;
2503 l_adjusted_scheme_start_date := p_scheme_start_date;
2504 END IF;
2505 CLOSE csr_adjusted_scheme_start_date;
2506 IF g_debug THEN
2507 debug('l_adjusted_scheme_start_date:'||
2508 fnd_date.date_to_canonical(l_adjusted_scheme_start_date));
2509 debug_exit(l_proc_name);
2510 END IF;
2511 RETURN l_adjusted_scheme_start_date;
2512
2513 -- if this cursor finds anything then there are three possibilities
2514 -- | - ^ - | -- the scheme start falls in the middle
2515 -- | - ^| -- the scheme start fals on the last day of the absence
2516 -- ^|- | -- the scheme start falls on the first day of absence
2517 --
2518 -- from our perspective the last option is good as no overlap, ie
2519 -- the absence is counted and no adjustement is required
2520 -- in the first two cases we need to decide based on FC or NC
2521 -- note for SC we wouldn't bother calling this as in SC
2522 -- in every case we just use the scheme start date
2523 -- we could even save calling this adjustment procedure for SC
2524 --
2525 -- ok so we have now modified cursor for just the first two cases
2526 -- and assuming its not called for SC then
2527 --
2528 -- if the rule is NC...return date end + 1
2529 -- if the rule is FC...return date start
2530
2531 EXCEPTION
2532 WHEN OTHERS THEN
2533 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
2534 debug_others
2535 (l_proc_name
2536 ,l_proc_step
2537 );
2538 IF g_debug THEN
2539 debug('Leaving: '||l_proc_name,-999);
2540 END IF;
2541 -- p_message := SQLERRM;
2542 -- p_error_code := -1;
2543 fnd_message.raise_error;
2544 ELSE
2545 RAISE;
2546 END IF;
2547 END get_adjusted_scheme_start_date;
2548 --
2549 --
2550 --
2551 PROCEDURE get_absences_taken_to_date
2552 (p_assignment_id IN NUMBER
2553 -- ,p_absence_date_start IN DATE
2554 ,p_effective_date IN DATE
2555 ,p_business_group_id IN NUMBER
2556 -- Added p_business_group_id for CS
2557 ,p_pl_typ_id IN NUMBER
2558 ,p_scheme_period_overlap_rule IN VARCHAR2
2559 ,p_scheme_period_type IN VARCHAR2
2560 ,p_scheme_period_duration IN VARCHAR2
2561 ,p_scheme_period_uom IN VARCHAR2
2562 ,p_scheme_period_start IN VARCHAR2
2563 ,p_entitlements IN OUT NOCOPY pqp_absval_pkg.t_entitlements
2564 ,p_absences_taken_to_date IN OUT NOCOPY pqp_absval_pkg.t_entitlements
2565 -- ,p_message OUT NOCOPY VARCHAR2
2566 -- Added for CS
2567 ,p_dualrolling_4_year IN BOOLEAN
2568 ,p_override_scheme_start_date IN DATE
2569 ,p_plan_types_to_extend_period IN VARCHAR2 -- LG/PT
2570 ,p_entitlement_uom IN VARCHAR2 -- LG/PT
2571 ,p_default_wp IN VARCHAR2 -- LG/PT
2572 ,p_absence_schedule_wp IN VARCHAR2 -- LG/PT
2573 ,p_track_part_timers IN VARCHAR2 -- LG/PT
2574 ,p_absence_start_date IN DATE
2575 ) --RETURN NUMBER -- error code
2576 IS
2577
2578
2579 CURSOR csr_ckh_lookup(p_lookup_type VARCHAR2)
2580 IS
2581 SELECT *
2582 FROM hr_lookups hrl
2583 WHERE hrl.lookup_type = p_lookup_type ;
2584
2585
2586
2587 l_flag VARCHAR2(30) := 'N';
2588 l_scheme_start_date DATE;
2589 l_adjusted_scheme_start_date DATE;
2590 l_error_code fnd_new_messages.message_number%TYPE:= 0;
2591 l_error_message fnd_new_messages.message_text%TYPE;
2592
2593 --nocopy changes
2594 l_entitlements_nc pqp_absval_pkg.t_entitlements;
2595 l_absences_taken_to_date_nc pqp_absval_pkg.t_entitlements;
2596 l_balance_date DATE;
2597
2598 i BINARY_INTEGER:=0;
2599 j BINARY_INTEGER:=0;
2600 l_band_has_been_found BOOLEAN;
2601
2602
2603 l_proc_step NUMBER(20,10);
2604 l_proc_name VARCHAR2(61):=
2605 g_package_name||
2606 'get_absences_taken_to_date';
2607 -- Added for LG/PT.
2608 l_period_start_date DATE ;
2609 l_period_end_date DATE ;
2610 l_calendar_days_to_extend NUMBER ;
2611 l_current_factor NUMBER ;
2612 l_ft_factor NUMBER;
2613 l_fte_value NUMBER ;
2614 l_working_days_per_week NUMBER ;
2615 l_fte NUMBER ;
2616 l_ft_absence_wp pqp_assignment_attributes_f.work_pattern%TYPE ;
2617 l_ft_working_wp pqp_assignment_attributes_f.work_pattern%TYPE ;
2618 l_assignment_wp pqp_assignment_attributes_f.work_pattern%TYPE ;
2619 l_is_full_timer BOOLEAN ;
2620 l_cutoff_counter NUMBER ;
2621 l_csr_ckh_lookup csr_ckh_lookup%ROWTYPE;
2622 l_is_assignment_wp BOOLEAN;
2623
2624
2625 BEGIN
2626 IF g_debug THEN
2627 debug_enter(l_proc_name);
2628 debug(p_assignment_id);
2629 debug(p_effective_date);
2630 debug(p_pl_typ_id);
2631 debug(p_scheme_period_overlap_rule);
2632 debug(p_scheme_period_type);
2633 debug(p_scheme_period_duration);
2634 debug(p_scheme_period_uom);
2635 debug(p_scheme_period_start);
2636 debug('p_override_scheme_start_date:'||
2637 fnd_date.date_to_canonical(p_override_scheme_start_date));
2638 debug('p_override_scheme_start_date'||p_override_scheme_start_date);
2639 debug('p_plan_types_to_extend_period'||p_plan_types_to_extend_period);
2640 debug('p_entitlement_uom'||p_entitlement_uom);
2641 debug('p_default_wp'||p_default_wp);
2642 debug('p_absence_schedule_wp'||p_absence_schedule_wp);
2643 debug('p_track_part_timers'||p_track_part_timers);
2644 END IF;
2645
2646 --p_message := l_error_message;
2647
2648 -- the purpose of this procedure is to determine the entitlement used up
2649 -- , to date. or in other words the absences taken to date.
2650
2651 l_proc_step := 10;
2652 IF g_debug THEN
2653 debug(l_proc_name, 10);
2654 END IF;
2655 -- nocopy changes
2656 -- l_band_info_nc := p_band_info;
2657 -- l_band_bal_info_nc := p_band_bal_info;
2658 l_absences_taken_to_date_nc := p_absences_taken_to_date;
2659 -- PERSON LEVEL ABSENCE CHANGES
2660 -- query configuration value to find out
2661 -- the option set for deduct absence taken for
2662 -- set the global as per that for further processing
2663 -- in the procedure get_absences_taken
2664
2665 g_deduct_absence_for :=
2666 PQP_UTILITIES.pqp_get_config_value
2667 ( p_business_group_id => p_business_group_id
2668 ,p_legislation_code => 'GB'
2669 ,p_column_name => 'PCV_INFORMATION9'
2670 ,p_information_category => 'PQP_GB_OSP_OMP_CONFIG'
2671 );
2672
2673 IF g_debug THEN
2674 debug(l_proc_name,l_proc_step);
2675 debug('g_deduct_absence_for :' ||g_deduct_absence_for);
2676 END IF;
2677
2678
2679 -- to determine the balance as of a given date (in OSP/OMP as of start of absence)
2680 -- we need to
2681 -- 1. determine the scheme start and end (eff date)
2682 -- 2. adjust the scheme start for any overlapping absences
2683 -- 3. sum duration and hours between the scheme start and eff date
2684 -- grouped by each level of entitlement which is in the OSP ent lookup
2685 -- type BAND%
2686 --
2687 -- to determine the entitlement remaining we need to deduct (per band)
2688 -- the duration/hours taken from the entitlement available
2689
2690 -- NOTE for hours we need to multiply entitlement by FTE
2691 --
2692
2693 --
2694 -- 1. determine the scheme start date
2695 -- to do that we need to know the rules ie fixed,rolling,duration
2696 -- ,uom,fixed yr start date
2697 --
2698 --
2699 -- Added for CS
2700 IF p_scheme_period_type = 'DUALROLLING' THEN
2701 debug(l_proc_name,11);
2702 -- get_rolling_start_date returns the Rolling period start date
2703 -- after considering the extensions of periods
2704
2705 l_adjusted_scheme_start_date :=
2706 pqp_gb_css_daily_absences.get_rolling_start_date
2707 (p_rolling_end_date => p_effective_date
2708 ,p_scheme_period_duration => p_scheme_period_duration
2709 ,p_assignment_id => p_assignment_id
2710 ,p_business_group_id => p_business_group_id
2711 ,p_scheme_period_type => p_scheme_period_type
2712 ,p_scheme_period_uom => p_scheme_period_uom
2713 ,p_pl_typ_id => p_pl_typ_id
2714 ,p_4_year_rolling_period => p_dualrolling_4_year
2715 ,p_lookup_type => p_plan_types_to_extend_period
2716 );
2717
2718 ELSE -- IF p_scheme_period_type = 'DUALROLLING' THEN
2719
2720 l_proc_step := 20;
2721
2722 IF p_override_scheme_start_date IS NULL
2723 THEN
2724
2725 l_scheme_start_date:=
2726 get_scheme_start_date
2727 (p_assignment_id => p_assignment_id
2728 ,p_scheme_period_type => p_scheme_period_type
2729 ,p_scheme_period_duration => p_scheme_period_duration
2730 ,p_scheme_period_uom => p_scheme_period_uom
2731 ,p_fixed_year_start_date => p_scheme_period_start
2732 ,p_balance_effective_date => p_absence_start_date
2733 );
2734
2735 -- Get the NOPAID days or Maternity days to extend the assessment period.
2736 -- l_scheme_stat_date = l_adjusted_scheme_start_date +
2737 -- sum of nopay days/Maternity days
2738
2739 l_proc_step := 30;
2740 l_period_start_date := l_scheme_start_date ;
2741 l_period_end_date := p_effective_date ;
2742 l_cutoff_counter := 0;
2743
2744
2745
2746
2747 OPEN csr_ckh_lookup(p_lookup_type => p_plan_types_to_extend_period);
2748 FETCH csr_ckh_lookup INTO l_csr_ckh_lookup;
2749 IF csr_ckh_lookup %FOUND THEN
2750 LOOP
2751
2752 l_cutoff_counter := l_cutoff_counter + 1 ;
2753
2754 l_calendar_days_to_extend :=
2755 pqp_absval_pkg.get_calendar_days_to_extend(
2756 p_period_start_date => l_period_start_date
2757 ,p_period_end_date => l_period_end_date
2758 ,p_assignment_id => p_assignment_id
2759 ,p_business_group_id => p_business_group_id
2760 ,p_pl_typ_id => p_pl_typ_id
2761 ,p_count_nopay_days => FALSE
2762 ,p_plan_types_lookup_type => p_plan_types_to_extend_period
2763 ) ;
2764
2765 IF l_calendar_days_to_extend > 0 THEN
2766 l_period_end_date := l_period_start_date -1 ;
2767 l_period_start_date := l_period_start_date -l_calendar_days_to_extend ;
2768 END IF ;
2769
2770 EXIT WHEN (l_calendar_days_to_extend <= 0 OR l_cutoff_counter > 100);
2771 END LOOP ;
2772 END IF;
2773 CLOSE csr_ckh_lookup;
2774
2775 l_scheme_start_date := l_period_start_date;
2776
2777 IF g_debug THEN
2778 debug(l_proc_name,l_proc_step);
2779 debug('l_scheme_start_date :' ||l_scheme_start_date);
2780 END IF;
2781
2782 --If exited the loop due to safety cut-off
2783 IF l_cutoff_counter > 100 THEN
2784 fnd_message.set_name('PQP','PQP_230011_LOOP_MAX_ITERATIONS');
2785 fnd_message.set_token('PROCNAME',l_proc_name);
2786 fnd_message.set_token('PROCSTEP',35);
2787 fnd_message.raise_error;
2788 END IF;
2789
2790
2791 --
2792 -- 2. adjust the scheme start and end for any overlaping absences
2793 -- to do that we need to first know if there are any overlapping
2794 -- absences, and then two determine the overlap rule
2795 --
2796 -- if the p_scheme_period_overlap_rule = Split(SC) then donot bother
2797 -- getting the adjusted date as in that we take the split any
2798 -- overlapping absences exactly down to the scheme start date
2799 -- boundary.
2800 --
2801
2802 l_proc_step := 40;
2803 IF g_debug THEN
2804 debug(l_proc_name, 40);
2805 END IF;
2806
2807 IF p_scheme_period_overlap_rule = 'SC'--(Split)
2808 THEN
2809 l_proc_step := 40;
2810 IF g_debug THEN
2811 debug(l_proc_name, 40);
2812 END IF;
2813
2814 l_adjusted_scheme_start_date := l_scheme_start_date;
2815
2816 ELSE -- p_scheme_period_overlap_rule = FC(Include) or NC(Exclude)
2817
2818 l_proc_step := 50;
2819 IF g_debug THEN
2820 debug(l_proc_name, 50);
2821 END IF;
2822
2823 l_adjusted_scheme_start_date :=
2824 get_adjusted_scheme_start_date
2825 (p_assignment_id => p_assignment_id
2826 ,p_pl_typ_id => p_pl_typ_id
2827 ,p_scheme_start_date => l_scheme_start_date
2828 ,p_scheme_period_overlap_rule => p_scheme_period_overlap_rule
2829 -- ,p_error_code => l_error_code
2830 -- ,p_message => l_error_message
2831 );
2832
2833 END IF ;-- IF p_scheme_period_overlap_rule = 'SC'--(Split)
2834
2835 ELSE
2836
2837 l_adjusted_scheme_start_date := p_override_scheme_start_date;
2838
2839 END IF; -- IF p_override_scheme_start_date IS NOT NULL
2840
2841 END IF; -- IF p_scheme_period_type = 'DUALROLLING' THEN
2842
2843
2844 debug('l_adjusted_scheme_start_date:'||
2845 fnd_date.date_to_canonical(l_adjusted_scheme_start_date));
2846
2847 l_proc_step := 60;
2848 IF g_debug THEN
2849 debug(l_proc_name, 60);
2850 END IF;
2851 -- from this point onwards the adjusted the scheme start date will be
2852 -- the effective start date for all balance purposes.
2853 -- the "end date" of the balance year is always the start of the current
2854 -- absence - which for the perspective of this function is p_effective_date
2855
2856 -- 3. sum duration and hours between the scheme start and eff date
2857 -- grouped by each level of entitlement which is in the OSP ent lookup
2858 -- type BAND%
2859 -- duplicate functionality we allready have get_absences_taken
2860 -- doing the same....levarage that.
2861 --
2862
2863 -- if the scheme is rolling pass range_to_date as effective_date - 1
2864 -- if fixed pass the effective date as passed from calling procs
2865
2866
2867 l_balance_date := p_effective_date - 1;
2868
2869
2870 --l_error_code:=
2871 get_absences_taken
2872 (p_assignment_id => p_assignment_id
2873 ,p_pl_typ_id => p_pl_typ_id
2874 ,p_range_from_date => l_adjusted_scheme_start_date
2875 ,p_range_to_date => l_balance_date
2876 ,p_absences_taken => p_absences_taken_to_date
2877 -- ,p_message => l_error_message
2878 );
2879
2880 l_proc_step := 70;
2881 IF g_debug THEN
2882 debug(l_proc_name, 70);
2883 END IF;
2884
2885 -- code beyond this point: checks here to see if there are more entitlements
2886 -- than there are bands in absence taken ytd
2887 -- if so it inserts rows in the plsql table for the missing ones with 0 duration
2888 --
2889 -- whats a good way of doing this ?..first we need to check whether
2890 -- p_entitlements has a row for every band....checking code...
2891 -- shows it doesn't ... it will contain only those rows which have any
2892 -- entitlements setup in it (currently if the user
2893 -- needs to skip a band he needs to set it up with 0 entitlement)
2894 --
2895 -- do we really need this at all ? so what if bands which have some entitlement
2896 -- setup are not there in the absences taken to date , will need to check
2897 -- logic on the daily absence processing side.
2898 --
2899 -- till then leave in place
2900 --
2901
2902 IF
2903 --p_entitlements.COUNT -- if there are more entitlement bands defined
2904 -- > p_absences_taken_to_date.COUNT -- than there are
2905 --AND l_count > 0
2906 -- AND
2907 p_absences_taken_to_date.COUNT > 0
2908 THEN
2909
2910 get_factors (
2911 p_business_group_id => p_business_group_id
2912 ,p_effective_date => p_effective_date
2913 ,p_assignment_id => p_assignment_id
2914 ,p_entitlement_uom => p_entitlement_uom
2915 ,p_default_wp => p_default_wp
2916 ,p_absence_schedule_wp => p_absence_schedule_wp
2917 ,p_track_part_timers => p_track_part_timers
2918 ,p_current_factor => l_current_factor
2919 ,p_ft_factor => l_ft_factor
2920 ,p_working_days_per_week => l_working_days_per_week
2921 ,p_fte => l_fte
2922 ,p_FT_absence_wp => l_ft_absence_wp
2923 ,p_FT_working_wp => l_ft_working_wp
2924 ,p_assignment_wp => l_assignment_wp
2925 ,p_is_full_timer => l_is_full_timer
2926 ,p_is_assignment_wp => l_is_assignment_wp
2927 ) ;
2928
2929
2930
2931
2932 l_proc_step := 80;
2933 IF g_debug THEN
2934 debug(l_proc_name, 80);
2935 END IF;
2936
2937 -- if so for each entitlement band
2938 --FOR i IN 1 .. p_entitlements.COUNT
2939 i := p_entitlements.FIRST;
2940 WHILE i IS NOT NULL
2941 LOOP
2942
2943 --l_flag := 'N';
2944 l_band_has_been_found := FALSE;
2945
2946 l_proc_step := 81;
2947 IF g_debug THEN
2948 debug(l_proc_name, l_proc_step);
2949 debug('Looking to see if '||i||p_entitlements(i).band||' has been taken');
2950 END IF;
2951
2952 -- check to see if there is such a band in the absence taken ytd
2953 --FOR j IN 1 .. p_absences_taken_to_date.COUNT
2954 j := p_absences_taken_to_date.FIRST;
2955 WHILE j IS NOT NULL
2956 LOOP
2957
2958 l_proc_step := 82;
2959 IF g_debug THEN
2960 debug(l_proc_name, l_proc_step);
2961 debug('We know that '||j||p_absences_taken_to_date(j).band||' has been taken.');
2962 END IF;
2963
2964 IF p_entitlements(i).band = p_absences_taken_to_date(j).band
2965 THEN
2966
2967 l_proc_step := 83;
2968 IF g_debug THEN
2969 debug(l_proc_name, l_proc_step);
2970 debug('This confirms '||i||p_entitlements(i).band||' has been taken.So mark as Yes.');
2971 END IF;
2972
2973 -- Here convert the abences taken to date into the current factor
2974 -- i.e get the numbers into the existing fte terms or
2975 -- Work Pattern terms
2976
2977 IF NVL(p_track_part_timers,'N') = 'Y' THEN
2978 IF p_entitlement_UOM = 'H'--ours
2979 THEN
2980 -- Changed LG p_absences_taken_to_date(j).entitlement :=
2981 p_absences_taken_to_date(j).duration_in_hours :=
2982 p_absences_taken_to_date(j).fte_hours * l_current_factor ;
2983 ELSE
2984 -- Changed LG p_absences_taken_to_date(j).entitlement :=
2985 p_absences_taken_to_date(j).duration :=
2986 p_absences_taken_to_date(j).duration_per_week * l_current_factor ;
2987 END IF; -- IF p_entitlement_UOM = 'H'ours
2988
2989 ELSE -- i.e. not tracking Part Timers
2990
2991 IF p_entitlement_UOM = 'H'--ours
2992 THEN
2993 p_absences_taken_to_date(j).entitlement :=
2994 p_absences_taken_to_date(j).duration_in_hours * l_current_factor ;
2995 ELSE
2996 p_absences_taken_to_date(j).entitlement :=
2997 p_absences_taken_to_date(j).duration * l_current_factor ;
2998 END IF; -- IF p_entitlement_UOM = 'H'ours
2999
3000 END IF ; -- tracking part timers check
3001
3002
3003 --l_flag := 'Y';
3004 l_band_has_been_found := TRUE;
3005
3006 END IF;
3007
3008 j := p_absences_taken_to_date.NEXT(j);
3009 debug('Next(j):'||j);
3010
3011 END LOOP; -- j loop
3012
3013 --if a band that is in the entitlements does not exist in the absence taken ytd
3014 --then add it to the absence taken ytd with a sum of duration as 0
3015
3016 IF --l_flag = 'N'
3017 NOT l_band_has_been_found
3018 THEN
3019
3020 l_proc_step := 84;
3021 IF g_debug THEN
3022 debug(l_proc_name, 84);
3023 debug(i||p_entitlements(i).band||' has NOT been taken');
3024 END IF;
3025
3026 j := p_absences_taken_to_date.LAST + 1;
3027 p_absences_taken_to_date(j).band:= p_entitlements(i).band;
3028 p_absences_taken_to_date(j).duration:= 0;
3029 p_absences_taken_to_date(j).duration_in_hours:= 0;
3030 p_absences_taken_to_date(j).duration_per_week := 0 ;
3031 p_absences_taken_to_date(j).fte_hours := 0 ;
3032 END IF;
3033
3034 i := p_entitlements.NEXT(i);
3035 debug('Next(i):'||i);
3036
3037 END LOOP; -- i loop
3038
3039 END IF; -- if ent count > abs count and abs count > 0
3040
3041 l_proc_step := 100;
3042 IF g_debug THEN
3043 debug(l_proc_name, 100);
3044 END IF;
3045 --
3046 --code beyond this point: if no absences taken ytd were found,
3047 -- still create rows for each band with 0 sum of duration
3048 --this is not needed as the prev loop takes care of that
3049 --left in place as when absences taken count is 0 this is more efficient
3050 --
3051 IF p_absences_taken_to_date.COUNT = 0 --l_count = 0
3052 THEN
3053 l_proc_step := 110;
3054 IF g_debug THEN
3055 debug(l_proc_name, 110);
3056 END IF;
3057 FOR i IN 1 .. p_entitlements.COUNT
3058 LOOP
3059 IF g_debug THEN
3060 debug(l_proc_name, 120+i);
3061 END IF;
3062 p_absences_taken_to_date( i ).band := p_entitlements( i ).band;
3063 p_absences_taken_to_date( i ).duration := 0;
3064 p_absences_taken_to_date( i ).duration_in_hours := 0;
3065 p_absences_taken_to_date( i ).duration_per_week := 0 ;
3066 p_absences_taken_to_date( i ).fte_hours := 0 ;
3067 END LOOP;
3068
3069 END IF; -- if absence taken count = 0
3070
3071 -- END IF;
3072
3073 IF g_debug THEN
3074 debug_exit(l_proc_name);
3075 END IF;
3076
3077 --RETURN l_error_code;
3078
3079 EXCEPTION
3080 WHEN OTHERS THEN
3081 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
3082 debug_others
3083 (l_proc_name
3084 ,l_proc_step
3085 );
3086 IF g_debug THEN
3087 debug('Leaving: '||l_proc_name,-999);
3088 END IF;
3089 -- p_message := SQLERRM;
3090 -- p_error_code := -1;
3091 p_absences_taken_to_date := l_absences_taken_to_date_nc;
3092 fnd_message.raise_error;
3093 ELSE
3094 RAISE;
3095 END IF;
3096 END get_absences_taken_to_date;
3097 --
3098 --This function calculates remaining entitlements
3099 --
3100 PROCEDURE get_entitlements_remaining
3101 (p_assignment_id IN NUMBER -- LG/PT
3102 ,p_effective_date IN DATE -- LG/PT
3103 ,p_entitlements IN pqp_absval_pkg.t_entitlements
3104 ,p_absences_taken_to_date IN pqp_absval_pkg.t_entitlements
3105 ,p_entitlement_UOM IN VARCHAR2
3106 ,p_entitlements_remaining IN OUT NOCOPY pqp_absval_pkg.t_entitlements--t_ent_run_balance
3107 ,p_is_full_timer IN BOOLEAN
3108 -- ,p_avg_working_days_assignment IN NUMBER --LG/PT
3109 -- ,p_avg_working_days_standard IN NUMBER -- LG/PT
3110 -- ,p_message OUT NOCOPY VARCHAR2
3111 -- LG/PT
3112 -- ,p_track_part_timers IN VARCHAR2 DEFAULT 'N'
3113 ) --RETURN NUMBER
3114 IS
3115
3116 l_error_code NUMBER:= 0;
3117
3118 i BINARY_INTEGER;
3119 j BINARY_INTEGER;
3120
3121 l_proc_step NUMBER(20,10);
3122 l_proc_name VARCHAR2(61):=
3123 g_package_name||
3124 'get_entitlements_remaining';
3125
3126 --nocopy changes
3127 l_entitlements_remaining_nc pqp_absval_pkg.t_entitlements ;
3128 -- LG/PT
3129 l_absences_taken_to_date pqp_absval_pkg.t_entitlements
3130 := p_absences_taken_to_date ;
3131 l_fte_value pqp_gap_daily_absences.fte%TYPE ;
3132 l_current_factor NUMBER ;
3133 l_ft_factor NUMBER ;
3134
3135 BEGIN
3136 IF g_debug THEN
3137 debug_enter(l_proc_name);
3138 END IF;
3139
3140
3141 -- nocopy changes
3142 l_entitlements_remaining_nc := p_entitlements_remaining;
3143
3144 l_proc_step := 10;
3145 IF g_debug THEN
3146 debug(l_proc_name, 10);
3147 END IF;
3148
3149 -- count the number of entitlements actually avaiable
3150 -- the initial assumption that the last band (first band at which no ent
3151 -- would be setup will have the entitlement of -1
3152 -- so say for LOS 5 there was only Band1, Band2
3153 -- then Band3 would be setup as -1
3154 -- this is probably not required as I didn't see in get_LOS_based_entitlements
3155 -- anything which sets the entitlements to -1
3156 --
3157 -- DO NOT DELETE till confirmed
3158 --
3159 -- FOR i IN 1 .. p_entitlements.COUNT
3160 -- LOOP
3161 -- IF g_debug THEN
3162 -- debug(l_proc_name, 10+i/1000);
3163 -- END IF;
3164 --
3165 -- IF p_entitlements(i).entitlement = -1 -- lets say this is never found
3166 -- THEN
3167 -- EXIT;
3168 -- ELSE
3169 -- l_band_count := i; -- will represent p_entitlements.COUNT
3170 -- END IF;
3171 -- END LOOP;
3172 --DO NOT DELETE till confirmed
3173
3174 l_proc_step := 30;
3175 IF g_debug THEN
3176 debug(l_proc_name, 30);
3177 END IF;
3178
3179 -- for every band in p_entitlements
3180 -- 1 BAND1 25
3181 -- 2 BAND2 25
3182 -- 3 BAND3 0
3183 -- 4 BAND4 0
3184 IF g_debug THEN
3185 debug('p_absences_taken_to_date.COUNT:'||
3186 fnd_number.number_to_canonical(p_absences_taken_to_date.COUNT));
3187 END IF ;
3188
3189 i := p_entitlements.FIRST;
3190 WHILE i IS NOT NULL
3191 --FOR i IN 1..p_entitlements.COUNT
3192 -- i = 1 (BAND1)
3193 LOOP
3194 l_proc_step := 30+i/1000 ;
3195 IF g_debug THEN
3196 debug(l_proc_name, 30+i/1000);
3197 END IF;
3198
3199 -- set the entitlement remaining as the full entitlement remaining
3200 p_entitlements_remaining(i).band := p_entitlements(i).band;
3201 p_entitlements_remaining(i).entitlement := p_entitlements(i).entitlement;
3202
3203 --
3204 -- then loop thru every band in p_absences_taken to see how much has been used
3205 -- 1 BAND2 25
3206 -- 2 BAND3 0
3207 -- 3 BAND1 15
3208 -- 4 BAND4 0
3209 -- if no matching bands are found in the inner loop then full entitlement
3210 -- is available for use.
3211 --
3212 j:= l_absences_taken_to_date.FIRST;
3213 WHILE j IS NOT NULL
3214 --FOR j IN 1.. p_absences_taken_to_date.COUNT
3215 -- j = 3 (BAND1)
3216 LOOP
3217
3218 l_proc_Step := 50+j/1000 ;
3219 IF g_debug THEN
3220 debug(l_proc_name, 50+j/1000);
3221 debug(l_absences_taken_to_date(j).band);
3222 debug(p_entitlements(i).band);
3223 END IF;
3224
3225 -- if the the band match
3226 IF l_absences_taken_to_date(j).band = p_entitlements(i).band
3227 -- BAND1(j=3) = BAND1(i=1)
3228 THEN
3229
3230 -- then ent remaining value = ent value -
3231 p_entitlements_remaining(i).band := p_entitlements(i).band;
3232 -- (i=1) = (i=1)BAND1
3233
3234 IF g_debug THEN
3235 debug('ent(i):'||
3236 fnd_number.number_to_canonical
3237 (p_entitlements(i).entitlement));
3238
3239 debug('abs(j).duration:'||
3240 fnd_number.number_to_canonical
3241 (l_absences_taken_to_date(j).duration));
3242
3243 debug('abs(j).duration_in_hours:'||
3244 fnd_number.number_to_canonical
3245 (l_absences_taken_to_date(j).duration_in_hours));
3246 END IF ;
3247
3248
3249 IF p_entitlement_UOM = 'H'--ours
3250 THEN
3251
3252 debug(l_proc_name,60+j/1000);
3253
3254 -- if the UOM is Hours debit the entitlement by hours duration
3255
3256 p_entitlements_remaining(i).entitlement :=
3257 p_entitlements(i).entitlement
3258 - p_absences_taken_to_date(j).duration_in_hours;
3259
3260 ELSE
3261
3262 debug(l_proc_name,70+j/1000);
3263
3264 -- if the UOM is Days debit the entitlement by days duration
3265
3266 p_entitlements_remaining(i).entitlement := round((
3267 p_entitlements(i).entitlement
3268 - p_absences_taken_to_date(j).duration),8); -- Bug 6335663
3269
3270
3271 -- the value p_is_full_timer is set only from OSP.
3272 -- from OMP this parameter is not passed and this rounding
3273 -- is not required for OMP
3274 IF p_is_full_timer IS NOT NULL THEN
3275
3276 IF p_is_full_timer THEN
3277 p_entitlements_remaining(i).entitlement :=
3278 pqp_utilities.round_value_up_down
3279 ( p_value_to_round => p_entitlements_remaining(i).entitlement
3280 ,p_base_value => g_ft_rounding_precision
3281 ,p_rounding_type => g_ft_entitl_rounding_type
3282 ) ;
3283 ELSE
3284 p_entitlements_remaining(i).entitlement :=
3285 pqp_utilities.round_value_up_down
3286 ( p_value_to_round => p_entitlements_remaining(i).entitlement
3287 ,p_base_value => g_pt_rounding_precision
3288 ,p_rounding_type => g_pt_entitl_rounding_type
3289 ) ;
3290 END IF ;
3291 END IF ;
3292
3293 END IF; -- IF p_entitlement_UOM = 'H'ours
3294
3295 -- 10(i=1 BAND1) =
3296 --
3297 -- 25(i=1 BAND1)
3298 -- - 15(j=3 BAND1)
3299 --
3300
3301 -- if the person had used more than he is entitled for
3302 -- likely to occur in situiations when FTE changes
3303 -- or when the entitlements at a highler length of
3304 -- service are lower (for some reason) than they
3305 -- were at the previous length of service band.
3306
3307 IF g_debug THEN
3308 debug('rem(i):'||
3309 fnd_number.number_to_canonical
3310 (p_entitlements_remaining(i).entitlement));
3311 END IF ;
3312
3313 IF p_entitlements_remaining(i).entitlement < 0
3314 --(i=1) 10 < 0 == NOT TRUE
3315 THEN
3316
3317 -- if (i=1 BAND1) was -10 then < 0 == TRUE
3318 p_entitlements_remaining(i).entitlement := 0;
3319 -- so set (i=1 BAND1) = 0
3320 END IF;
3321
3322 -- debug('rem(i):'||
3323 -- fnd_number.number_to_canonical
3324 -- (p_entitlements_remaining(i).entitlement));
3325
3326
3327 --p_entitlements_remaining(i).ent_bal := 0;
3328 -- (i=1) = 0
3329 -- this value is used when the day by day debit is done
3330 -- it probably represents the amount left after each day
3331 -- is processed
3332
3333 END IF; -- if j.band = i.band
3334
3335 j:= l_absences_taken_to_date.NEXT(j);
3336
3337 END LOOP; -- for j in p_absences_taken_to_date
3338
3339 i := p_entitlements.NEXT(i);
3340
3341 END LOOP; -- for i in p_entitlements
3342
3343 IF g_debug THEN
3344 debug_exit(l_proc_name);
3345 END IF;
3346 --RETURN l_error_code;
3347
3348 EXCEPTION
3349 WHEN OTHERS THEN
3350 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
3351 debug_others
3352 (l_proc_name
3353 ,l_proc_step
3354 );
3355 IF g_debug THEN
3356 debug('Leaving: '||l_proc_name,-999);
3357 END IF;
3358 -- p_message := SQLERRM;
3359 -- p_error_code := -1;
3360 p_entitlements_remaining := l_entitlements_remaining_nc;
3361 fnd_message.raise_error;
3362 ELSE
3363 RAISE;
3364 END IF;
3365 END get_entitlements_remaining;
3366 --
3367 -- Adds a day or part of the day to the cache which is used later to
3368 -- bulk insert to the table.
3369 -- Note it is also in this procedure that we will call the chk procedures
3370 -- of the row handler to ensure that the data is valid prior to the bulk
3371 -- insert call
3372 --
3373 PROCEDURE set_daily_absence_cache
3374 (p_daily_absences IN OUT NOCOPY pqp_absval_pkg.t_daily_absences
3375 ,p_absence_date IN pqp_gap_daily_absences.absence_date%TYPE
3376 ,p_work_pattern_day_type IN pqp_gap_daily_absences.work_pattern_day_type%TYPE
3377 ,p_level_of_entitlement IN pqp_gap_daily_absences.level_of_entitlement%TYPE
3378 ,p_level_of_pay IN pqp_gap_daily_absences.level_of_pay%TYPE
3379 ,p_duration IN pqp_gap_daily_absences.duration%TYPE
3380 ,p_duration_in_hours IN pqp_gap_daily_absences.duration_in_hours%TYPE
3381 ,p_working_days_per_week IN pqp_gap_daily_absences.working_days_per_week%TYPE
3382 ,p_fte IN pqp_gap_daily_absences.fte%TYPE --LG/PT
3383 -- ,p_error_code OUT NOCOPY fnd_new_messages.message_number%TYPE
3384 -- ,p_message OUT NOCOPY fnd_new_messages.message_text%TYPE
3385 )
3386 IS
3387
3388 i INTEGER;
3389
3390 l_proc_step NUMBER(20,10);
3391 l_proc_name VARCHAR2(61):=
3392 g_package_name||
3393 'set_daily_absence_cache';
3394 BEGIN
3395
3396 IF g_debug THEN
3397 debug_enter(l_proc_name);
3398 END IF;
3399
3400 i := NVL(p_daily_absences.LAST,0)+1;
3401
3402 p_daily_absences(i).absence_date := p_absence_date;
3403 p_daily_absences(i).work_pattern_day_type := p_work_pattern_day_type;
3404 p_daily_absences(i).level_of_entitlement := p_level_of_entitlement;
3405 p_daily_absences(i).level_of_pay := p_level_of_pay;
3406 p_daily_absences(i).duration := p_duration;
3407 p_daily_absences(i).duration_in_hours := p_duration_in_hours;
3408 p_daily_absences(i).working_days_per_week := p_working_days_per_week ;
3409 p_daily_absences(i).fte := p_fte ; --LG/PT
3410
3411 IF g_debug THEN
3412 debug(p_daily_absences(i).absence_date);
3413 debug(p_daily_absences(i).work_pattern_day_type);
3414 debug(p_daily_absences(i).level_of_entitlement);
3415 debug(p_daily_absences(i).level_of_pay);
3416 debug(p_daily_absences(i).duration);
3417 debug(p_daily_absences(i).duration_in_hours);
3418 debug(p_daily_absences(i).working_days_per_week);
3419 debug(p_daily_absences(i).fte);
3420
3421 END IF;
3422
3423
3424 l_proc_step := 10;
3425 IF g_debug THEN
3426 debug(l_proc_name, 10);
3427 END IF;
3428
3429 --pqp_gda_bus.insert_validate(p_daily_absences(i));
3430
3431 IF g_debug THEN
3432 debug_exit(l_proc_name);
3433 END IF;
3434
3435 EXCEPTION
3436 WHEN OTHERS THEN
3437 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
3438 debug_others
3439 (l_proc_name
3440 ,l_proc_step
3441 );
3442 IF g_debug THEN
3443 debug('Leaving: '||l_proc_name,-999);
3444 END IF;
3445 -- p_message := SQLERRM;
3446 -- p_error_code := -1;
3447 fnd_message.raise_error;
3448 ELSE
3449 RAISE;
3450 END IF;
3451 END set_daily_absence_cache;
3452 --
3453 -- The purpose of this procedure is to generate daily absences
3454 -- for the current absence (event) it does not directly write
3455 -- to the daily absences table but stores it into pl/sql tables
3456 -- which then using bulk insert (hopefully) are written to the
3457 -- database.
3458 --
3459 -- becase in this procedure we will loop thru each day
3460 -- all debug calls must be made conditional for performance
3461 -- ideally we should do that everywhere.
3462 --
3463 PROCEDURE generate_daily_absences
3464 (p_assignment_id IN NUMBER
3465 ,p_business_group_id IN NUMBER
3466 ,p_absence_attendance_id IN NUMBER
3467 ,p_default_work_pattern_name IN VARCHAR2
3468 ,p_calendar_user_table_id IN NUMBER
3469 ,p_calendar_rules_list IN VARCHAR2
3470 ,p_generate_start_date IN DATE
3471 ,p_generate_end_date IN DATE
3472 ,p_absence_start_date IN DATE
3473 ,p_absence_end_date IN DATE
3474 ,p_entitlement_UOM IN VARCHAR2
3475 ,p_payment_UOM IN VARCHAR2
3476 ,p_output_type IN ff_exec.outputs_t
3477 ,p_entitlements_remaining IN OUT NOCOPY pqp_absval_pkg.t_entitlements
3478 ,p_daily_absences IN OUT NOCOPY pqp_absval_pkg.t_daily_absences
3479 ,p_error_code OUT NOCOPY NUMBER
3480 ,p_message OUT NOCOPY VARCHAR2
3481 ,p_working_days_per_week IN NUMBER
3482 ,p_fte IN NUMBER -- LG/PT
3483 ,p_override_work_pattern IN VARCHAR2 DEFAULT NULL
3484 ,p_pl_id IN NUMBER DEFAULT NULL
3485 ,p_scheme_period_type IN VARCHAR2 DEFAULT NULL
3486 ,p_is_assignment_wp IN BOOLEAN
3487 )
3488 IS
3489
3490 l_part_start_day pqp_gap_daily_absences.duration%TYPE;
3491 l_part_end_day pqp_gap_daily_absences.duration%TYPE;
3492 l_part_day_UOM per_absence_attendances.abs_information3%TYPE;
3493
3494 l_duration NUMBER ;
3495 l_duration_to_process NUMBER ;
3496 l_duration_processed NUMBER ;
3497 l_number_of_hours_in_the_day NUMBER ;
3498
3499 l_current_date DATE;
3500 l_end_date DATE;
3501
3502 l_calendar_exclusion BOOLEAN;
3503 l_count_for_entitlement BOOLEAN;
3504 l_entitled_to_be_paid BOOLEAN;
3505 l_is_working_day BOOLEAN;
3506
3507 l_work_pattern_day_type pqp_gap_daily_absences.work_pattern_day_type%TYPE;
3508 l_level_of_entitlement pqp_gap_daily_absences.level_of_entitlement%TYPE;
3509 l_level_of_pay pqp_gap_daily_absences.level_of_pay%TYPE;
3510
3511 i BINARY_INTEGER;
3512 dd BINARY_INTEGER:=0;
3513 cc BINARY_INTEGER:=0;
3514 l_first_available_band_index BINARY_INTEGER;
3515
3516 l_error_code fnd_new_messages.message_number%TYPE;
3517 l_error_message fnd_new_messages.message_text%TYPE;
3518
3519 l_calendar_rule_name fnd_lookup_values.meaning%TYPE;
3520 l_cal_day_name fnd_lookup_values.meaning%TYPE;
3521 l_cal_rule_value fnd_lookup_values.meaning%TYPE;
3522 l_calendar_rule_code fnd_lookup_values.lookup_code%TYPE;
3523 l_calendar_filter fnd_lookup_values.lookup_code%TYPE;
3524 l_calendar_value pay_user_column_instances_f.VALUE%TYPE;
3525
3526 l_datatype fnd_lookup_values.lookup_code%TYPE;
3527 l_override_work_pattern pay_user_columns.user_column_name%TYPE;
3528 l_override_work_pattern_yn fnd_lookup_values.lookup_code%TYPE;
3529
3530 l_entitlements_remaining_nc pqp_absval_pkg.t_entitlements;
3531 l_minimum_pay_defined NUMBER ;
3532 l_minimum_pay_rate NUMBER ;
3533 ------- Minimum pay rate enhancment --------
3534 l_minpay_start_date DATE;
3535 l_minpay_end_date DATE;
3536 -- cache for minimum pay rate
3537 l_effective_minpay_start_day DATE ;
3538 l_effective_minpay_end_day DATE;
3539 l_process_min_pay BOOLEAN := FALSE;
3540 l_plan_information rec_plan_information;
3541 --------Minimum pay rate enhancement -------
3542
3543
3544 ------Waiting Period enhancements
3545 l_waiting_days_txt VARCHAR2(10);--Return Value From Fast Formula.No.of waiting days.
3546 l_waiting_days_remaining NUMBER;--Waiting days remaining during the iteration
3547 l_duration_to_set_as_waiting NUMBER;--Duration of waiting period processed in one iteration
3548 l_waiting_entitlement VARCHAR2(30);
3549 l_waiting_pay VARCHAR2(30) ;
3550 l_waiting_days_used pqp_gap_daily_absences.duration%TYPE;
3551 negative_value EXCEPTION;
3552 ------Waiting Period enhancements
3553
3554
3555 l_proc_step NUMBER(20,10);
3556 l_proc_name VARCHAR2(61)
3557 := g_package_name||
3558 'generate_daily_absences';
3559 l_open_ended_no_pay_days NUMBER;
3560 l_override_wp pay_user_columns.user_column_name%TYPE;
3561 l_is_assignment_wp BOOLEAN ;
3562 l_assignment_wp NUMBER := 0; -- Added for the Bug : 13563548
3563 BEGIN
3564 g_debug := hr_utility.debug_enabled;
3565 l_is_assignment_wp := p_is_assignment_wp ;
3566
3567 IF g_debug THEN
3568 debug_enter(l_proc_name);
3569 END IF;
3570
3571 --
3572 -- to work thru this first think out what it should be doing
3573 -- for each day
3574 -- 1. evaluate whether its a holiday (excluded in the calendar)
3575 -- if so mark the day's level of entitlement as EXCLUDED
3576 -- mark the level of pay with whatever "rule" the chk_calendar_occurence
3577 -- returns for that date.
3578 --
3579 -- mark the day, date, duration, hours , level of ent and level of pay
3580 -- and loop to the next day.
3581 --
3582 -- 2. determine if the day is working or not.
3583 --
3584 --
3585 -- 3. if the day is not excluded from entitlement then
3586 --
3587 -- a) if the scheme is a working scheme and the day is not working
3588 -- set the level of entitlement as not else mark as ENTITLED
3589 --
3590 -- b) if the scheme is a working paid days scheme and the day is not
3591 -- working set the level of pay as NOT else mark as ENTITLED
3592 --
3593 -- c) if either of levels are marked as ENTITLED then
3594 -- start the next loop
3595 --
3596 -- else mark the day , duration , hours, level of ent, level of pay
3597 -- and loop to the next day.
3598 --
3599 -- c). <start the next loop> (don't actually code like this)
3600 -- loop thru all the bands of entiltements remaining from BAND1ton
3601 -- and debit the entitlement by the duration of the day.
3602 -- if the duration of the day can be covered fully by the entitlement
3603 -- mark the day
3604 -- else loop to the next band. (somehere in this optimize such that
3605 -- when the person enters no band then for the subsequent days do not
3606 -- waste processing effor in this loop and mark it straight as No Pay)
3607 --
3608 -- a day may be covered by more than one entiltement.this could
3609 -- be done by just calling process_entitlement_for_day (hypothetical)
3610 -- or by this inner loop. the likely hood that the inner loop
3611 -- will loop more than once is extremely unlikely however it is a
3612 -- neater/cleared solution.
3613 --
3614 -- special notes: for hours we will need to check UOM
3615 -- for part days and hours we need to check UOM and convert the
3616 -- part days into hours or hours into days etc.
3617 --
3618 --
3619
3620
3621 l_proc_step := 10;
3622 IF g_debug THEN
3623 debug(l_proc_name, 10);
3624 END IF;
3625 --Added by akarmaka to process minband pay
3626 --reset the variable for effective minimum pay rate start date
3627 l_effective_minpay_start_day := NULL;
3628 l_effective_minpay_end_day := NULL;
3629
3630 -- Set value for open ended absence no pay days
3631 l_open_ended_no_pay_days :=
3632 PQP_UTILITIES.pqp_get_config_value(
3633 p_business_group_id => p_business_group_id
3634 ,p_legislation_code => 'GB'
3635 ,p_column_name => 'PCV_INFORMATION8'
3636 ,p_information_category => 'PQP_GB_OSP_OMP_CONFIG'
3637 );
3638
3639 g_open_ended_no_pay_days :=
3640 FND_NUMBER.canonical_to_number(NVL(l_open_ended_no_pay_days,365));
3641
3642 IF g_debug THEN
3643 debug('g_open_ended_no_pay_days', g_open_ended_no_pay_days);
3644 END IF;
3645
3646 --nocopy changes
3647 l_entitlements_remaining_nc := p_entitlements_remaining;
3648
3649
3650
3651 -----Waiting Period Enhancement
3652
3653 BEGIN
3654
3655 ---Geting the Number of Waiting Days from the formula variable WAITING_DAYS.
3656 ---Value for WAITING_DAYS to be set and returned from the formula by the user.
3657 ---l_waiting_days_remaining is initialized with the waiting days.
3658
3659 get_param_value
3660 (p_output_type => p_output_type
3661 ,p_name => 'WAITING_DAYS'
3662 ,p_datatype => l_datatype
3663 ,p_value => l_waiting_days_txt
3664 );
3665
3666 IF g_debug THEN
3667 debug('l_waiting_days_txt:'||l_waiting_days_txt);
3668 END IF;
3669
3670 --To happen only for absences which are being updated
3671 IF p_absence_start_date <> p_generate_start_date
3672 THEN
3673 OPEN csr_sum_level_entit_duration
3674 (p_gap_absence_id => p_absence_attendance_id
3675 ,p_level_of_entitlement => 'WAITINGDAY'
3676 ,p_absence_date => p_generate_start_date);
3677
3678 FETCH csr_sum_level_entit_duration INTO l_waiting_days_used ;
3679 CLOSE csr_sum_level_entit_duration ;
3680 END IF;
3681
3682 IF g_debug THEN
3683 debug('l_waiting_days_used:',l_waiting_days_used);
3684 END IF;
3685
3686 l_waiting_days_remaining:=TO_NUMBER(NVL(l_waiting_days_txt,0));
3687 l_waiting_days_remaining:=l_waiting_days_remaining - NVL(l_waiting_days_used,0);
3688
3689 IF g_debug THEN
3690 debug('l_waiting_days_remaining:',l_waiting_days_remaining);
3691 END IF;
3692
3693
3694 IF l_waiting_days_remaining<0
3695 THEN
3696 RAISE negative_value;
3697 END IF;
3698
3699 EXCEPTION
3700 WHEN VALUE_ERROR
3701 OR negative_value
3702 OR INVALID_NUMBER
3703 THEN
3704
3705 fnd_message.set_name( 'PQP', 'PQP_230167_OSP_NON_NUM_OFFSET' );
3706 fnd_message.set_token( 'TOKEN1', l_waiting_days_txt);
3707 fnd_message.raise_error;
3708
3709 END;
3710 --
3711 --
3712
3713
3714
3715 --
3716 -- 1) to check if a day exists in the calendar or not we need to
3717 -- call chk_calendar_occurance passing appropriate filter and value.
3718 --
3719 get_param_value
3720 (p_output_type => p_output_type
3721 ,p_name => 'CALENDAR_VALUE'
3722 ,p_datatype => l_datatype
3723 ,p_value => l_calendar_value
3724 --,p_error_code => l_error_code
3725 --,p_message => l_error_message
3726 );
3727
3728 l_calendar_value := TRIM(l_calendar_value); -- maybe to think about
3729
3730 l_proc_step := 12;
3731 IF g_debug THEN
3732 debug(l_proc_name, l_proc_step);
3733 END IF;
3734
3735 get_param_value
3736 (p_output_type => p_output_type
3737 ,p_name => 'CALENDAR_FILTER'
3738 ,p_datatype => l_datatype
3739 ,p_value => l_calendar_filter
3740 --,p_error_code => l_error_code
3741 --,p_message => l_error_message
3742 );
3743
3744 l_calendar_filter := UPPER(TRIM( l_calendar_filter ));
3745
3746 IF l_calendar_filter IS NULL
3747 THEN
3748 l_calendar_filter := 'ALLMATCH';
3749 END IF;
3750
3751 --
3752 -- 2) to determine if a day is working or not we need to pass
3753 -- the default working pattern the "is working day" function.
3754 -- as a special case for Working/Calendar scheme we also
3755 -- need to pass the override work pattern (if there is any)
3756 --
3757 --
3758 --
3759 -- set up Override Workpattern that is passed from create proc.
3760 -- this is required for full timers that have absence schedule work pattern
3761 -- attached at plan level.
3762
3763 -- changes made here to store the override work pattern for using it
3764 -- later as per the override work pattern functionality.
3765 l_override_wp := p_override_work_pattern ;
3766 IF g_debug THEN
3767 debug('l_override_wp :=' ||l_override_wp );
3768 END IF;
3769
3770 -- BUG 2804329 Override work pattern by this way
3771 -- is only limited to Working entitlements and
3772 -- Calendar days paid (Teachers Setup)
3773 IF p_entitlement_UOM = 'W' AND p_payment_UOM = 'C'
3774 THEN
3775 -- NOTE the actual override functionality is meant to be
3776 -- 1. Check if the user has set override_yn to Y if Yes then
3777 -- 2. check if the user has supplied a work pattner name
3778 -- 3. if not then use the default wp as the override wp
3779 -- 4. if yes then use the named wp as the override wp
3780 -- As a added convenience we have made the override_yn as implied "Y"
3781 -- if just the override_wp is passed. Therefore the actual code logic
3782 -- is to check for the override_wp fisrt.
3783
3784
3785 -- check if the user has provided a override work pattern
3786 -- if so then use that as the override wp
3787 -- if he hasn't then check if he has set the override_work_pattern_yn to Y
3788 -- if so then use the default as the work pattern
3789
3790 l_proc_step := 15;
3791 IF g_debug THEN
3792 debug(l_proc_name, 15);
3793 END IF;
3794
3795 get_param_value
3796 (p_output_type => p_output_type
3797 ,p_name => 'OVERRIDE_WORK_PATTERN'
3798 ,p_datatype => l_datatype
3799 ,p_value => l_override_work_pattern
3800 --,p_error_code => l_error_code
3801 --,p_message => l_error_message
3802 );
3803 --IF l_error_code <> 0 THEN
3804 -- check_error_code(l_error_code,l_error_message);
3805 --END IF;
3806
3807 IF g_debug THEN
3808 debug('l_override_work_pattern:' ||l_override_work_pattern);
3809 END IF;
3810 l_override_work_pattern := TRIM( l_override_work_pattern);
3811
3812 IF l_override_work_pattern IS NULL
3813 THEN
3814 -- else check if the user has indicated to use default as override
3815 -- if so then set the default wp as the override else override is null
3816
3817 get_param_value
3818 (p_output_type => p_output_type
3819 ,p_name => 'OVERRIDE_WORK_PATTERN_YN'
3820 ,p_datatype => l_datatype
3821 ,p_value => l_override_work_pattern_yn
3822 --,p_error_code => l_error_code
3823 --,p_message => l_error_message
3824 );
3825 --IF l_error_code <> 0 THEN
3826 -- check_error_code(l_error_code,l_error_message);
3827 --END IF;
3828
3829 IF g_debug THEN
3830 debug('l_override_work_pattern_yn:' ||l_override_work_pattern_yn);
3831 END IF;
3832 l_override_work_pattern_yn :=
3833 SUBSTRB( UPPER( TRIM( l_override_work_pattern_yn )), 1, 1 );
3834
3835 IF l_override_work_pattern_yn = 'Y'
3836 THEN
3837 debug('override with p_default_work_pattern_name:' ||
3838 p_default_work_pattern_name
3839 );
3840 l_override_work_pattern := p_default_work_pattern_name;
3841 l_is_assignment_wp := FALSE;
3842
3843 ELSE
3844 l_override_work_pattern := l_override_wp;
3845
3846 END IF; -- IF l_override_work_pattern_yn = 'Y'
3847
3848 ELSE
3849 l_is_assignment_wp := FALSE;
3850 END IF; -- IF l_override_work_pattern IS NULL
3851
3852 END IF; -- if the scheme is working ent + cal days paid
3853
3854 l_proc_step := 20;
3855 IF g_debug THEN
3856 debug(l_proc_name, 20);
3857 END IF;
3858 ----Added by akarmaka to process minband pay
3859 l_minimum_pay_defined :=
3860 pqp_gb_osp_functions.get_minimum_pay_info
3861 (p_assignment_id => p_assignment_id
3862 ,p_business_group_id => p_business_group_id
3863 ,p_absence_id => p_absence_attendance_id
3864 ,p_minpay_start_date => l_minpay_start_date
3865 ,p_minpay_end_date => l_minpay_end_date
3866 ) ;
3867
3868
3869
3870 -- if l_minimimum pay is defined then
3871 -- we decide to pay the pension rate
3872 -- only for CS and LG scheme.
3873 -- for CS we use the filter criteria of DUAL ROLLING
3874 -- for LG we use W/C scheme -- further check is reqd
3875 -- not available now --to be added in future
3876
3877 debug('p_entitlement_UOM: '|| p_entitlement_UOM);
3878 debug('p_payment_UOM '|| p_payment_UOM);
3879 debug('p_scheme_period_type '|| p_scheme_period_type);
3880
3881 IF (p_entitlement_UOM = 'W' AND p_payment_UOM = 'C') --LG
3882 OR (p_scheme_period_type = 'DUALROLLING') --CS
3883
3884 THEN
3885 l_process_min_pay := TRUE;
3886
3887 END IF ;
3888
3889
3890
3891
3892 IF g_debug THEN
3893 debug('l_minpay_start_date:'|| to_char(l_minpay_start_date));
3894 debug('l_minpay_end_date'|| to_char(l_minpay_end_date));
3895 debug('l_minimum_pay_rate'|| to_char(l_minimum_pay_rate));
3896 END IF;
3897
3898
3899
3900
3901
3902 get_absence_part_days
3903 (p_absence_id => p_absence_attendance_id
3904 ,p_part_start_day => l_part_start_day
3905 ,p_part_end_day => l_part_end_day
3906 ,p_part_day_UOM => l_part_day_UOM
3907 );
3908
3909 l_end_date := p_generate_end_date;
3910 l_current_date := p_generate_start_date;
3911
3912 --
3913 -- l_current_date represents the current date
3914 -- l_end_date represents the date upto which this loop will run for
3915 -- a given absence. this can only have two values either the absence end date
3916 -- or eot. if its the eot the process will amend the end date within the loop
3917 -- to be equal to the 365th day from the first date of no pay.
3918 -- this is could be a dangerous logic as it means that the process may run
3919 -- for a very long period of time if some erroneous condition or bug
3920 -- fails to set the end date correctly. I think we should have a safety cut
3921 -- out. to check with ljg on possible impact on business rules.
3922
3923 IF g_debug THEN
3924 debug('l_current_date:');
3925 debug(l_current_date);
3926 END IF;
3927
3928 IF g_debug THEN
3929 debug('l_end_date:');
3930 debug(l_end_date);
3931 END IF;
3932
3933
3934
3935 dd := 0;
3936 WHILE l_current_date <= l_end_date
3937 -- AND we should have a safety cut out -- dd < l_max_iterations(1000??)
3938 AND dd < 2001 -- 5 * 365 = 18??
3939 LOOP
3940
3941 dd := dd + 1;
3942
3943 l_proc_step := 25+dd/1000;
3944 IF g_debug THEN
3945 debug(l_proc_name, l_proc_step);
3946 debug('l_current_date:');
3947 debug(l_current_date);
3948 END IF;
3949
3950 IF g_debug THEN
3951 l_proc_step := 30+dd/1000;
3952 debug(l_proc_name, l_proc_step);
3953 END IF;
3954
3955 -- 1. check if the day is marked in the calendar
3956 -- and if so under what rule of pay is it marked.
3957 IF p_calendar_user_table_id IS NOT NULL AND
3958 pqp_gb_osp_functions.chk_calendar_occurance
3959 (p_date => l_current_date
3960 ,p_calendar_table_id => p_calendar_user_table_id
3961 ,p_calendar_rules_list => 'PQP_GB_OSP_CALENDAR_RULES'
3962 ,p_cal_rul_name => l_calendar_rule_name -- column name (level of pay)
3963 ,p_cal_day_name => l_cal_day_name -- row name (holiday name)
3964 ,p_cal_rule_value => l_cal_rule_value -- value (filter)
3965 ,p_error_code => l_error_code
3966 ,p_error_message => l_error_message
3967 ,p_cal_value => l_calendar_value
3968 ,p_filter => l_calendar_filter
3969 ) <> -1
3970 THEN
3971 l_proc_step := 35+dd/1000;
3972 IF g_debug THEN
3973 debug(l_proc_name, l_proc_step);
3974 END IF;
3975 l_calendar_exclusion := TRUE;
3976 l_calendar_rule_code :=
3977 pqp_gb_osp_functions.get_lookup_code
3978 (p_lookup_type => 'PQP_GB_OSP_CALENDAR_RULES'
3979 ,p_lookup_meaning => l_calendar_rule_name
3980 ,p_message => l_error_message
3981 );
3982 ELSE
3983 IF g_debug THEN
3984 l_proc_step := 37+dd/1000;
3985 debug(l_proc_name, l_proc_step);
3986 END IF;
3987 check_error_code(l_error_code,l_error_message);
3988 l_calendar_exclusion := FALSE;
3989
3990 END IF; -- IF chk_calendar_exclusion <> -1
3991
3992
3993 -- 2. check if the day is a working day or not as we this then
3994 -- decides whether a day is entitled or not.
3995 -- NOTE we only need to working day check if the scheme
3996 -- has either entitlements or payments in working days/hours.
3997 --
3998 --
3999 -- replace the next call to get the number of working hours for this
4000 -- day. Use the fact that hours > 0 represents a working day
4001 --
4002 --
4003 l_proc_step := 40+dd/1000;
4004 IF g_debug THEN
4005 debug(l_proc_name, l_proc_step);
4006 END IF;
4007
4008 -- Added for the Bug : 13563548
4009 if l_is_assignment_wp then
4010 l_assignment_wp := 1;
4011 end if;
4012 -- Bug : 13563548 changes ends
4013
4014 IF p_entitlement_UOM IN ('W','H') OR p_payment_UOM IN ('W','H')
4015 THEN
4016
4017 l_proc_step := 45+dd/1000;
4018 IF g_debug THEN
4019 debug(l_proc_name, l_proc_step);
4020 END IF;
4021
4022
4023 -- IF pqp_schedule_calculation_pkg.is_working_day -- get_number_of_hours_worked
4024 -- (p_assignment_id => p_assignment_id
4025 -- ,p_business_group_id => p_business_group_id
4026 -- ,p_date => l_current_date
4027 -- ,p_error_code => l_error_code
4028 -- ,p_error_message => l_error_message
4029 -- ,p_default_wp => p_default_work_pattern_name
4030 -- ,p_override_wp => l_override_work_pattern
4031 -- ) = 'Y'
4032 -- THEN
4033 -- IF g_debug THEN
4034 -- debug(l_proc_name, 46+dd/1000);
4035 -- END IF;
4036 -- l_is_working_day := TRUE;
4037 -- ELSE
4038 -- IF g_debug THEN
4039 -- debug(l_proc_name, 47+dd/1000);
4040 -- END IF;
4041 -- check_error_code(l_error_code,l_error_message);
4042 -- l_is_working_day := FALSE;
4043 -- END IF;
4044
4045 -- populate here l_number_of_hours_in_the_day
4046 l_number_of_hours_in_the_day := pqp_schedule_calculation_pkg.get_hours_worked
4047 (p_assignment_id => p_assignment_id
4048 ,p_business_group_id => p_business_group_id
4049 ,p_date_start => l_current_date
4050 ,p_date_end => l_current_date
4051 ,p_error_code => l_error_code
4052 ,p_error_message => l_error_message
4053 ,p_default_wp => p_default_work_pattern_name
4054 ,p_override_wp => l_override_work_pattern
4055 ,p_is_assignment_wp => l_assignment_wp -- Modified for the Bug : 13563548
4056 );
4057
4058 IF l_number_of_hours_in_the_day > 0
4059 THEN
4060 l_is_working_day := TRUE;
4061 ELSE
4062 l_is_working_day := FALSE;
4063 END IF;
4064
4065
4066 ELSE -- for calendar calendar schemes all days are treated as working
4067
4068 l_proc_step := 49+dd/1000;
4069 IF g_debug THEN
4070 debug(l_proc_name, l_proc_step);
4071 END IF;
4072
4073 l_is_working_day:= TRUE;
4074 --l_number_of_hours_in_the_day := 24; -- pur
4075 l_number_of_hours_in_the_day := pqp_schedule_calculation_pkg.get_hours_worked
4076 (p_assignment_id => p_assignment_id
4077 ,p_business_group_id => p_business_group_id
4078 ,p_date_start => l_current_date
4079 ,p_date_end => l_current_date
4080 ,p_error_code => l_error_code
4081 ,p_error_message => l_error_message
4082 ,p_default_wp => p_default_work_pattern_name
4083 ,p_override_wp => l_override_work_pattern
4084 ,p_is_assignment_wp => l_assignment_wp -- Modified for the Bug : 13563548
4085 );
4086
4087
4088 END IF; -- if p_ent_UOM or p_pay_UOM IN ('W','H')
4089
4090
4091 IF l_is_working_day THEN
4092 l_work_pattern_day_type := 'WORKON';
4093 ELSE
4094 l_work_pattern_day_type := 'OFFWORK';
4095 END IF;
4096
4097
4098 -- l_duration_to_process (set as hours or days as decided by ent UOM)
4099 -- note for non-working days hours = 0 so better to run the day loop
4100 -- in days and covert to hours inside it when debitting the ent.
4101 --
4102 -- set l_duration_to_process
4103 --
4104 l_duration_to_process := 1; --normally
4105
4106 -- if the date is the absence end date
4107 -- and it has part day marked then use that as the duration
4108 -- to process.
4109
4110 l_proc_step := 50+(dd/1000);
4111 IF g_debug THEN
4112 debug(l_proc_name, l_proc_step);
4113 debug(l_current_date);
4114 debug(p_absence_end_date);
4115 debug(l_part_end_day);
4116 debug(l_proc_name, l_proc_step);
4117 END IF;
4118
4119
4120 IF l_current_date = p_absence_end_date
4121 AND l_part_end_day IS NOT NULL
4122 THEN
4123
4124 l_proc_step := 51+dd/1000;
4125 IF g_debug THEN
4126 debug(l_proc_name, l_proc_step);
4127 END IF;
4128
4129
4130 IF l_part_day_UOM = 'HOURS'
4131 AND l_number_of_hours_in_the_day > 0
4132 -- if its not a working day and somebody has
4133 -- recorded a part day in hours -- ignore it
4134 -- ideally these things should be UI validated
4135 THEN
4136
4137 l_proc_step := 52+dd/1000;
4138 IF g_debug THEN
4139 debug(l_proc_name, l_proc_step);
4140 END IF;
4141
4142
4143 l_duration_to_process
4144 := l_part_end_day / l_number_of_hours_in_the_day;
4145
4146 ELSIF l_part_day_UOM = 'DAYS'
4147 THEN
4148
4149 l_proc_step := 53+dd/1000;
4150 IF g_debug THEN
4151 debug(l_proc_name, l_proc_step);
4152 END IF;
4153
4154
4155 l_duration_to_process := l_part_end_day;
4156
4157 ELSE
4158
4159 l_proc_step := 54+dd/1000;
4160 IF g_debug THEN
4161 debug('!');
4162 debug(l_proc_name, l_proc_step);
4163 END IF;
4164
4165 -- not really sure -- should be an error -- will be ignored
4166 NULL;
4167
4168 END IF; -- IF p_part_day_UOM = 'H' THEN
4169
4170 END IF; -- IF l_current_date = p_absence_end_date
4171
4172 -- if the date is the absence start date
4173 -- and it has part day marked then use that as the duration
4174 -- to process.
4175
4176 l_proc_step := 50+(dd/1000);
4177 IF g_debug THEN
4178 debug(l_proc_name, l_proc_step);
4179 debug(l_current_date);
4180 debug(p_absence_start_date);
4181 debug(l_part_start_day);
4182 --l_proc_step := 50+(dd/1000);
4183 debug(l_proc_name, l_proc_step);
4184 END IF;
4185
4186 IF l_current_date = p_absence_start_date
4187 AND l_part_start_day IS NOT NULL
4188 THEN
4189
4190 l_proc_step := 55+dd/1000;
4191 IF g_debug THEN
4192 debug(l_proc_name, l_proc_step);
4193 END IF;
4194
4195
4196 IF l_part_day_UOM = 'HOURS'
4197 AND l_number_of_hours_in_the_day > 0
4198 -- if its not a working day and somebody has
4199 -- recorded a part day in hours -- ignore it
4200 -- ideally these things should be UI validated
4201 THEN
4202
4203 l_proc_step := 56+dd/1000;
4204 IF g_debug THEN
4205 debug(l_proc_name, l_proc_step);
4206 END IF;
4207
4208
4209 l_duration_to_process
4210 := l_part_start_day / l_number_of_hours_in_the_day;
4211
4212 ELSIF l_part_day_UOM = 'DAYS'
4213 THEN
4214
4215 l_proc_step := 57+dd/1000;
4216 IF g_debug THEN
4217 debug(l_proc_name, l_proc_step);
4218 END IF;
4219
4220
4221 l_duration_to_process := l_part_start_day;
4222
4223 ELSE
4224
4225 l_proc_step := 58+dd/1000;
4226 IF g_debug THEN
4227 debug(l_proc_name, l_proc_step);
4228 END IF;
4229
4230 -- not really sure -- should be an error -- will be ignored
4231 NULL;
4232
4233 END IF; -- IF p_part_day_UOM = 'H' THEN
4234
4235 END IF;
4236
4237 -- note if it was a single day absence
4238 -- and the part day information of start will be used
4239 -- however if the user had entered part day information
4240 -- in the end date and not in the start field then that
4241 -- may also be used as part day information.
4242 -- the order of the if conditions sets the precedence
4243 -- ie if both part days are entered for a single day
4244 -- absence then we will use the start day one
4245 -- and ignore the end day one. validation on the flex
4246 -- should be tightened.
4247 --
4248
4249 l_proc_step := 60+dd/1000;
4250 IF g_debug THEN
4251 debug(l_proc_name, l_proc_step);
4252 END IF;
4253
4254
4255 -- 3 check if the day is entitled -- ie not excluded from entitlement
4256 IF NOT l_calendar_exclusion
4257 THEN
4258
4259 l_proc_step := 65+dd/1000;
4260 IF g_debug THEN
4261 debug(l_proc_name, l_proc_step);
4262 END IF;
4263
4264 --3a) is the day eligible to be counted towards the absence entitlement
4265 IF p_entitlement_UOM IN ('W','H') AND NOT l_is_working_day
4266 THEN
4267 -- if the scheme has a working entitlement and the current day is not working
4268 -- then mark this day as NOT entitled.
4269
4270 l_proc_step := 66+dd/1000;
4271 IF g_debug THEN
4272 debug(l_proc_name, l_proc_step);
4273 END IF;
4274
4275 l_count_for_entitlement := FALSE;
4276
4277 ELSE
4278
4279 l_proc_step := 67+dd/1000;
4280 IF g_debug THEN
4281 debug(l_proc_name, l_proc_step);
4282 END IF;
4283
4284 l_count_for_entitlement:= TRUE;
4285
4286 END IF;
4287
4288 -- 3b) check if the day is entitled to be paid
4289 IF p_payment_UOM IN ('W','H') AND NOT l_is_working_day
4290 THEN
4291
4292 -- if the scheme has a working entitlement and the current day is not working
4293 -- then mark this day as NOT entitled.
4294 --
4295 -- we have W% check because the same applies to Working Days and Working Hours
4296 -- schemes. Working Days (W) and Working Hours (WH). feedback to lookup design
4297 l_proc_step := 68+dd/1000;
4298 IF g_debug THEN
4299 debug(l_proc_name, l_proc_step);
4300 END IF;
4301
4302 l_entitled_to_be_paid := FALSE;
4303
4304 ELSE
4305
4306 l_proc_step := 69+dd/1000;
4307 IF g_debug THEN
4308 debug(l_proc_name, l_proc_step);
4309 END IF;
4310
4311 l_entitled_to_be_paid := TRUE;
4312
4313 END IF;
4314
4315 -- we could have done withou these boolean variables
4316 -- however since the same condition is checked for again and again
4317 -- and also by use of boolean variables its possible to give these
4318 -- conditions a "name" hence making further code more readable.
4319
4320
4321 --Waiting Days is to be accounted for in the sick leave if the value of
4322 --l_waiting_days_txt is >0.
4323 --l_waiting_days_remaining is the reverse counter.It is initialized with l_waiting_days_txt
4324 --It is decremented by the duration of days used up by the waiting days in
4325 --every iteration and the waiting days process ends when l_waiting_days_remaining
4326 --decrements to 0.
4327
4328 IF l_waiting_days_remaining > 0 THEN
4329
4330 IF l_count_for_entitlement THEN
4331
4332 --Enter this block if some waiting days are still left to be accounted
4333 --for and if the current day is counted for entitlement ......
4334
4335 --Check if the duration to process in the current day is lees than
4336 --or greater than the waiting days remaining.
4337 --Duration to process in a current day will mostly be less than
4338 --or equal to the waiting days remamining exept
4339 --(1)Waitng days defined by the user is less than duration to
4340 -- process of a current day...then the starting day iteration
4341 -- would enter the ELSE part.
4342 --(2)The conclusive part of the waiting day would enter the ELSE part
4343 -- if waiting days remaining is in fraction due to start day
4344 -- being a part day or the waiting days itself is defined as fraction
4345
4346
4347 IF l_duration_to_process <= l_waiting_days_remaining THEN
4348 l_duration_to_set_as_waiting:=l_duration_to_process;
4349 ELSE
4350 l_duration_to_set_as_waiting:=l_waiting_days_remaining;
4351 END IF;
4352
4353 --Set the values for entitlement and pay variables which would
4354 --go into the daily absence table.
4355 IF l_entitled_to_be_paid THEN
4356 l_waiting_entitlement := 'WAITINGDAY';
4357 l_waiting_pay := 'NOBAND';
4358 ELSE
4359 l_waiting_entitlement := 'WAITINGDAY';
4360 l_waiting_pay := 'NOT';
4361 END IF;
4362
4363 ELSE -- l_count_for_entitlement
4364
4365 --If the current day is not to be counted as entitled then
4366 --set the the appropriate values of entitlement and pay.
4367 --The duration processed in this iteration equals the entire duraiotn
4368 --of the day.
4369 IF l_entitled_to_be_paid THEN
4370 l_waiting_entitlement := 'NOT';
4371 l_waiting_pay := 'NOBAND';
4372 ELSE
4373 l_waiting_entitlement := 'NOT';
4374 l_waiting_pay := 'NOT';
4375 END IF ;
4376 l_duration_to_set_as_waiting:=l_duration_to_process;
4377
4378 END IF ;-- l_count_for_entitlement
4379
4380 IF g_debug THEN
4381 debug('l_current_date:'||l_current_date);
4382 debug('l_duration_to_process:'||l_duration_to_process);
4383 debug('l_duration_to_set_as_waiting:'||l_duration_to_set_as_waiting);
4384 debug('l_waiting_days_remaining:'||l_waiting_days_remaining);
4385 debug('l_waiting_pay:'||l_waiting_pay);
4386 debug('l_waiting_entitlement:'||l_waiting_entitlement);
4387 END IF;
4388
4389 END IF; -- l_waiting_days_remaining > 0
4390
4391
4392
4393
4394
4395 -- 3c) if day is either entitled or entitled to be paid
4396 -- else write to the cache as NOT NOT
4397 IF l_count_for_entitlement OR l_entitled_to_be_paid THEN
4398
4399 l_proc_step := 70+dd/1000;
4400 IF g_debug THEN
4401 debug(l_proc_name, l_proc_step);
4402 END IF;
4403
4404 -- 3d) loop thru all the bands of entiltements remaining from BAND1 to n
4405 -- and debit the entitlement by the duration of the day.
4406
4407 -- use this if it gets to messy
4408 IF l_first_available_band_index IS NULL
4409 THEN
4410 i := p_entitlements_remaining.FIRST;
4411 l_first_available_band_index := i;
4412 ELSE
4413 i := l_first_available_band_index;
4414 END IF;
4415
4416 -- ensure that the search in the entitlement remaining bands
4417 -- skips the bands which have been completely used up in the
4418 -- previous days. read further to see how this is set.
4419 IF g_debug THEN
4420 debug('i:'||i);
4421 END IF;
4422
4423 IF g_debug THEN
4424 debug('l_duration_to_process:'||l_duration_to_process);
4425 END IF;
4426
4427 cc := 0;
4428
4429 WHILE l_duration_to_process > 0 -- run this loop always in days
4430 -- while there is still some part of the day left to be processed
4431 AND
4432 i IS NOT NULL
4433 -- while there are still some entitlement bands remaining
4434 -- (i) will become once the loop iterates beyond the last
4435 -- index or entry in the entitlements remaining plsql table.
4436 AND cc < 11
4437 LOOP
4438
4439 cc := cc + 1;
4440
4441 l_proc_step := 72+(dd/1000)+(cc/100000);
4442 IF g_debug THEN
4443 debug(l_proc_name, l_proc_step);
4444 END IF;
4445
4446 --Check if the day falls in waiting days period.
4447 --If yes then
4448 --(1)Set the daily absence cache with the pre-initialized values
4449 -- of p_level_of_entitlement and p_level_of_pay
4450 --(2)Decrement the value of duration to process by the duration
4451 -- processed as waiting days i.e. l_duration_to_set_as_waiting
4452 --(3)If the day is counted as entitiled then decrease the waiting
4453 -- days remaining by the amount of duration processed as
4454 -- waiting days
4455 IF l_waiting_days_remaining > 0 THEN
4456
4457
4458 IF g_debug THEN
4459 debug('l_waiting_days_remaining:',l_waiting_days_remaining);
4460 END IF;
4461
4462 set_daily_absence_cache
4463 (p_daily_absences => p_daily_absences
4464 ,p_absence_date => l_current_date
4465 ,p_work_pattern_day_type => l_work_pattern_day_type
4466 ,p_level_of_entitlement => l_waiting_entitlement
4467 ,p_level_of_pay => l_waiting_pay
4468 ,p_duration => l_duration_to_set_as_waiting
4469 ,p_duration_in_hours => (l_duration_to_set_as_waiting * l_number_of_hours_in_the_day)
4470 ,p_working_days_per_week => p_working_days_per_week
4471 ,p_fte => NVL(p_fte,1)
4472 );
4473
4474 l_duration_to_process := l_duration_to_process - l_duration_to_set_as_waiting;
4475
4476 IF l_count_for_entitlement THEN
4477 l_waiting_days_remaining:= l_waiting_days_remaining - l_duration_to_set_as_waiting;
4478 END IF;
4479
4480 ELSE --l_waiting_days_remaining > 0
4481
4482
4483 IF p_entitlements_remaining(i).entitlement > 0
4484 THEN
4485
4486 l_proc_step := 73+(dd/1000)+(cc/100000);
4487 IF g_debug THEN
4488 debug(l_proc_name, l_proc_step);
4489 END IF;
4490
4491 IF l_count_for_entitlement
4492 THEN
4493
4494 l_proc_step := 74+(dd/1000)+(cc/100000);
4495 IF g_debug THEN
4496 debug(l_proc_name, l_proc_step);
4497 END IF;
4498
4499 IF p_entitlement_UOM = 'H'
4500 THEN
4501
4502 l_proc_step := 75+(dd/1000)+(cc/100000);
4503 IF g_debug THEN
4504 debug(l_proc_name, l_proc_step);
4505 END IF;
4506
4507
4508 l_duration_processed :=
4509 LEAST(l_duration_to_process*l_number_of_hours_in_the_day
4510 ,p_entitlements_remaining(i).entitlement
4511 );
4512
4513 p_entitlements_remaining(i).entitlement :=
4514 p_entitlements_remaining(i).entitlement
4515 - l_duration_processed;
4516 --LEAST(l_number_of_hours_in_the_day
4517 -- ,p_entitlements_remaining(i).entitlement
4518 -- );
4519
4520 l_duration_processed := -- set duration processed back in days
4521 l_duration_processed / l_number_of_hours_in_the_day;
4522
4523
4524 ELSE -- p_entitlement_UOM = 'D'ays
4525
4526 l_proc_step := 76+(dd/1000)+(cc/100000);
4527 IF g_debug THEN
4528 debug(l_proc_name, l_proc_step);
4529 END IF;
4530
4531
4532 l_duration_processed :=
4533 LEAST(l_duration_to_process
4534 ,p_entitlements_remaining(i).entitlement
4535 );
4536
4537 p_entitlements_remaining(i).entitlement:=
4538 p_entitlements_remaining(i).entitlement
4539 - l_duration_processed;
4540 --LEAST(l_duration_to_process
4541 -- ,p_entitlements_remaining(i).entitlement
4542 -- );
4543
4544
4545 END IF; -- IF p_entitlement_UOM = 'H'
4546
4547 ELSE
4548
4549 l_proc_step := 77+(dd/1000)+(cc/100000);
4550 IF g_debug THEN
4551 debug(l_proc_name, l_proc_step);
4552 END IF;
4553 -- the day is not counted for entitlement but the fact that the
4554 -- control is in here, implies that this day is entitled to be
4555 -- paid (eg scenario an off day in a Working Calendar scheme).
4556 -- These days are not affected by part entitlements or
4557 -- part durations and are always processed completely.
4558
4559 l_duration_processed := l_duration_to_process;
4560
4561 END IF; --IF l_count_for_entitlement
4562
4563 l_duration_to_process := l_duration_to_process - l_duration_processed;
4564
4565 debug('l_duration_to_process remaining:'||
4566 fnd_number.number_to_canonical(l_duration_to_process)
4567 );
4568
4569 -- write the day to the cache
4570 -- date => l_current_date
4571 -- work_pattern_day_type => depending on l_is_working_days
4572 -- level_of_entitlement => 'NOT' if NOT l_counted_for_ent else (i).band
4573 -- level_of_pay => 'NOT' if NOT l_entitled_to_be_paid else (i).band
4574 -- duration =>l_duration_processed(converted appropriately)
4575 -- hours =>l_duration_processed(converted appropriately)
4576
4577 IF l_count_for_entitlement THEN
4578 l_proc_step := 77+(dd/1000)+(cc/100000);
4579 IF g_debug THEN
4580 debug(l_proc_name, l_proc_step);
4581 END IF;
4582 l_level_of_entitlement := p_entitlements_remaining(i).band;
4583 ELSE
4584 l_proc_step := 78+(dd/1000)+(cc/100000);
4585 IF g_debug THEN
4586 debug(l_proc_name, l_proc_step);
4587 END IF;
4588 l_level_of_entitlement := 'NOT';
4589 END IF;
4590
4591 IF l_entitled_to_be_paid THEN
4592 l_proc_step := 79+(dd/1000)+(cc/100000);
4593 IF g_debug THEN
4594 debug(l_proc_name, l_proc_step);
4595 END IF;
4596 l_level_of_pay := p_entitlements_remaining(i).band;
4597 ELSE
4598 l_proc_step := 80+(dd/1000)+(cc/100000);
4599 IF g_debug THEN
4600 debug(l_proc_name, l_proc_step);
4601 END IF;
4602 l_level_of_pay := 'NOT';
4603 END IF;
4604
4605 l_proc_step := 81;
4606 IF g_debug THEN
4607 debug(l_proc_name, 81);
4608 END IF;
4609
4610 set_daily_absence_cache
4611 (p_daily_absences => p_daily_absences
4612 ,p_absence_date => l_current_date
4613 ,p_work_pattern_day_type => l_work_pattern_day_type
4614 ,p_level_of_entitlement => l_level_of_entitlement
4615 ,p_level_of_pay => l_level_of_pay
4616 ,p_duration => l_duration_processed
4617 ,p_duration_in_hours => (l_duration_processed * l_number_of_hours_in_the_day)
4618 ,p_working_days_per_week => p_working_days_per_week
4619 ,p_fte => NVL(p_fte,1)
4620 --,p_error_code => l_error_code
4621 --,p_message => l_error_message
4622 );
4623
4624
4625 END IF; -- if p_entitlements_remaining(i).ent > 0
4626
4627
4628
4629 IF p_entitlements_remaining(i).entitlement = 0
4630 THEN
4631 l_proc_step := 82+(dd/1000)+(cc/100000);
4632 IF g_debug THEN
4633 debug(l_proc_name, l_proc_step);
4634 END IF;
4635 -- ie the current band has been completely used up
4636 -- (either exactly equal to the day or was insufficient)
4637 -- then set the first available band index to be the next
4638 -- band to ensure that for the next day this loop
4639 -- does not iterate for used up bands.
4640
4641 i := p_entitlements_remaining.NEXT(i);
4642 -- when i = LAST, NEXT will return null
4643
4644 l_first_available_band_index := i;
4645 --p_entitlements_remaining.NEXT(i);
4646 -- when i = LAST, NEXT will return null
4647
4648 END IF; -- p_entitlements_remaining(i).entitlement = 0
4649 END IF; --l_waiting_days_remaining > 0
4650
4651
4652 debug(l_proc_name, 83+cc/1000);
4653 IF g_debug THEN
4654 debug('Next(i):'||i);
4655 END IF;
4656
4657
4658 END LOOP; --while there is still some duration to process for this day
4659
4660 l_proc_step := 84;
4661 IF g_debug THEN
4662 debug(l_proc_name, 84);
4663 END IF;
4664
4665 IF cc = 11 THEN
4666 fnd_message.set_name('PQP','PQP_230011_LOOP_MAX_ITERATIONS');
4667 fnd_message.set_token('PROCNAME',l_proc_name);
4668 fnd_message.set_token('PROCSTEP',85);
4669 fnd_message.raise_error;
4670 END IF;
4671
4672 END IF;
4673
4674 l_proc_step := 85;
4675 IF g_debug THEN
4676 debug(l_proc_name, 85);
4677 END IF;
4678
4679 END IF; -- if NOT calendar exclusion
4680
4681 l_proc_step := 90;
4682 IF g_debug THEN
4683 debug(l_proc_name, 90);
4684 END IF;
4685
4686 IF l_duration_to_process > 0 -- four possible reasons
4687 THEN
4688
4689 -- is a calendar exclusion
4690 -- is a NOT NOT day
4691 -- is a day gone partly into NOBAND
4692 -- is a NOBAND day
4693 l_proc_step := 91;
4694 IF g_debug THEN
4695 debug(l_proc_name, 91);
4696 END IF;
4697
4698
4699 IF l_calendar_exclusion
4700 THEN
4701 l_proc_step := 92;
4702 IF g_debug THEN
4703 debug(l_proc_name, 92);
4704 END IF;
4705 l_level_of_entitlement := 'EXCLUDED';
4706 l_level_of_pay := l_calendar_rule_code;
4707
4708 ELSE
4709 l_proc_step := 93;
4710 IF g_debug THEN
4711 debug(l_proc_name, 93);
4712 END IF;
4713 IF l_count_for_entitlement
4714 THEN
4715 l_proc_step := 94;
4716 IF g_debug THEN
4717 debug(l_proc_name, 94);
4718 END IF;
4719 l_level_of_entitlement := 'NOBAND';
4720
4721 IF g_debug THEN
4722 debug('l_end_date:');
4723 debug(l_end_date);
4724 END IF;
4725
4726 IF l_end_date = hr_api.g_eot THEN
4727 -- this is the first day of No Pay
4728 l_proc_step := 95;
4729 IF g_debug THEN
4730 debug(l_proc_name, 95);
4731 END IF;
4732 l_end_date := l_current_date + g_open_ended_no_pay_days;
4733
4734 IF g_debug THEN
4735 debug('New l_end_date:');
4736 debug(l_end_date);
4737 END IF;
4738 l_proc_step := 96;
4739 IF g_debug THEN
4740 debug(l_proc_name, 96);
4741 END IF;
4742 END IF;
4743 l_proc_step := 97;
4744 IF g_debug THEN
4745 debug(l_proc_name, 97);
4746 END IF;
4747 ELSE
4748 l_proc_step := 98;
4749 IF g_debug THEN
4750 debug(l_proc_name, 98);
4751 END IF;
4752 l_level_of_entitlement := 'NOT';
4753
4754 END IF;
4755 l_proc_step := 99;
4756 IF g_debug THEN
4757 debug(l_proc_name, 99);
4758 END IF;
4759 IF l_entitled_to_be_paid
4760 THEN
4761 l_proc_step := 100;
4762 IF g_debug THEN
4763 debug(l_proc_name, 100);
4764 END IF;
4765 l_level_of_pay := 'NOBAND';
4766 -- check if minimum pay is defined
4767 -- and set the level of pay accordingly.
4768
4769
4770 -- days which are due to be paid as NOBANDMIN
4771 -- IF LG or CS | check attribute of scheme (future)
4772 -- is current date between pension start and end date
4773 -- if so mark level of pay as nobandmin
4774 -- else mark as noband
4775 -- else mark as noband
4776
4777 -- set the effective start date for payment of pension rate
4778 -- if start date provided is Null then
4779 -- start paying the employee with NOBANDMIN level of pay
4780 -- from first day of no band.
4781 -- Similarly, if the pension rate end date is not given then it
4782 -- wil mark level of pay as nobandmin till the end of the absence
4783 IF l_process_min_pay THEN
4784
4785 IF l_effective_minpay_start_day IS NULL THEN
4786 l_effective_minpay_start_day:= NVL(l_minpay_start_date,l_current_date);
4787 END IF;
4788 IF l_effective_minpay_end_day IS NULL THEN
4789 l_effective_minpay_end_day := NVL(l_minpay_end_date,p_generate_end_date);
4790 END IF;
4791
4792 IF g_debug THEN
4793 debug('l_effective_minpay_start_day:='||l_effective_minpay_start_day);
4794 debug('l_effective_minpay_end_day:='||l_effective_minpay_end_day);
4795 END IF;
4796 IF l_minimum_pay_defined <> 0 THEN
4797 IF ((l_current_date >= l_effective_minpay_start_day)
4798 AND (l_current_date <= l_effective_minpay_end_day ) )
4799 THEN
4800 l_level_of_pay := 'NOBANDMIN';
4801
4802 END IF ;
4803
4804 ELSE -- l_minimum_pay_defined <> 0 THEN
4805 l_level_of_pay := 'NOBAND';
4806 END IF ; -- l_minimum_pay_defined <> 0 THEN
4807 END IF ; -- IF l_process_min_pay THEN
4808
4809 ELSE
4810 l_proc_step := 101;
4811 IF g_debug THEN
4812 debug(l_proc_name, 101);
4813 END IF;
4814 l_level_of_pay := 'NOT';
4815
4816 END IF;
4817 l_proc_step := 102;
4818 IF g_debug THEN
4819 debug(l_proc_name, 102);
4820 END IF;
4821
4822 END IF; -- calendar exclusion
4823 l_proc_step := 103;
4824 IF g_debug THEN
4825 debug(l_proc_name, 103);
4826 END IF;
4827
4828 --l_shift_duration := l_duration_to_process;
4829 --l_hours_duration := l_duration_to_process * l_number_of_working_hours;
4830
4831 -- write the day to the cache
4832 -- date => l_current_date
4833 -- work_pattern_day_type => l_work_pattern_day_type
4834 -- level_of_entitlement => l_level_of_entitlement
4835 -- level_of_pay => l_level_of_payment
4836 -- duration => l_shift_duration
4837 -- hours => l_hours_duration
4838
4839 set_daily_absence_cache
4840 (p_daily_absences => p_daily_absences
4841 ,p_absence_date => l_current_date
4842 ,p_work_pattern_day_type => l_work_pattern_day_type
4843 ,p_level_of_entitlement => l_level_of_entitlement
4844 ,p_level_of_pay => l_level_of_pay
4845 ,p_duration => l_duration_to_process
4846 ,p_duration_in_hours => (l_duration_to_process * l_number_of_hours_in_the_day)
4847 ,p_working_days_per_week => p_working_days_per_week
4848 ,p_fte => NVL(p_fte,1)
4849 --,p_error_code => l_error_code
4850 --,p_message => l_error_message
4851 );
4852
4853 l_proc_step := 104;
4854 IF g_debug THEN
4855 debug(l_proc_name, 104);
4856 END IF;
4857
4858 END IF; -- if l_duration_to_process > 0
4859
4860 l_current_date := l_current_date + 1;
4861
4862 END LOOP; -- for each day of absence.
4863
4864 l_proc_step := 105;
4865 IF g_debug THEN
4866 debug(l_proc_name, 105);
4867 END IF;
4868
4869 IF dd = 2001 THEN
4870 fnd_message.set_name('PQP','PQP_230011_LOOP_MAX_ITERATIONS');
4871 fnd_message.set_token('PROCNAME',l_proc_name);
4872 fnd_message.set_token('PROCSTEP',105);
4873 fnd_message.raise_error;
4874 END IF;
4875
4876 IF g_debug THEN
4877 debug_exit(l_proc_name);
4878 END IF;
4879
4880 EXCEPTION
4881 WHEN OTHERS THEN
4882 p_entitlements_remaining := l_entitlements_remaining_nc;
4883 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
4884 debug_others
4885 (l_proc_name
4886 ,l_proc_step
4887 );
4888 IF g_debug THEN
4889 debug('Leaving: '||l_proc_name,-999);
4890 END IF;
4891 -- p_message := SQLERRM;
4892 -- p_error_code := -1;
4893 fnd_message.raise_error;
4894 ELSE
4895 RAISE;
4896 END IF;
4897 END generate_daily_absences;
4898 --
4899 --
4900 --
4901 PROCEDURE update_absence_plan_details
4902 (p_assignment_id IN NUMBER
4903 ,p_person_id IN NUMBER
4904 ,p_business_group_id IN NUMBER
4905 ,p_absence_id IN NUMBER
4906 ,p_absence_date_start IN DATE
4907 ,p_absence_date_end IN DATE
4908 ,p_pl_id IN NUMBER
4909 ,p_pl_typ_id IN NUMBER
4910 ,p_element_type_id IN NUMBER
4911 ,p_update_start_date IN DATE
4912 ,p_update_end_date IN DATE
4913 ,p_output_type IN ff_exec.outputs_t
4914 ,p_error_code OUT NOCOPY NUMBER
4915 ,p_message OUT NOCOPY VARCHAR2
4916 )
4917 IS
4918
4919 l_absence_start_date DATE;
4920 l_absence_end_date DATE;
4921 l_absence_dates csr_absence_dates%ROWTYPE;
4922 l_gap_absence_plan csr_gap_absence_plan%ROWTYPE;
4923 l_first_entitled_day_of_noband csr_first_entitled_day_of_band%ROWTYPE;
4924 l_error_code fnd_new_messages.message_number%TYPE:=0;
4925 l_error_message fnd_new_messages.message_text%TYPE;
4926
4927 l_proc_step NUMBER(20,10);
4928 l_proc_name VARCHAR2(61):=
4929 g_package_name||
4930 'update_absence_plan_details';
4931 l_open_ended_no_pay_days NUMBER;
4932 l_part_start_day pqp_gap_daily_absences.duration%TYPE;
4933 l_part_end_day pqp_gap_daily_absences.duration%TYPE;
4934 l_part_day_UOM per_absence_attendances.abs_information3%TYPE;
4935 BEGIN
4936
4937
4938 g_debug := hr_utility.debug_enabled;
4939
4940 IF g_debug THEN
4941 debug_enter(l_proc_name);
4942 debug(p_assignment_id);
4943 debug(p_person_id);
4944 debug(p_business_group_id);
4945 debug(p_absence_id);
4946 debug(p_absence_date_start);
4947 debug(p_absence_date_end);
4948 debug(p_pl_id);
4949 debug(p_pl_typ_id);
4950 debug(p_element_type_id);
4951 debug(p_update_start_date);
4952 debug(p_update_end_date);
4953 END IF; -- IF g_debug THEN
4954
4955 --
4956 -- Update logic
4957 -- an update is effectively a delete and a create
4958 -- however it would be very inefficient if for every update
4959 -- (which would occur once for every absence) we completley delete
4960 -- and recreate. For that reason the delete and create
4961 -- need to be arranged such that they only delete the extra bits
4962 -- and create the missing bits.
4963 -- to keep the logic simple we need design such that
4964 --
4965 -- delete will remove the unwanted part
4966 -- create will create any missing part.
4967 --
4968 -- lets examine the scenarios that this update is called
4969 -- i) end date processing
4970 --
4971 -- ii) end date updated
4972 --
4973 -- a) to a date greater than the current end date
4974 --
4975 -- b) to a date less than the current end date
4976 --
4977 -- iii) start date is updated or a correction takes place
4978 -- which fires a start event.
4979 --
4980 -- In this case the update is called not as part of
4981 -- start event processing but as a part of the reversing
4982 -- out the prev processed life events.
4983 --
4984 -- For case i) we normally wouldn't need to do anything because
4985 -- the start processing would have created everything upto the end date
4986 -- only exception being a) when an open ended absence is ended or b)
4987 -- an end dated absence is "re-opened"
4988 -- ia) delete everything from (new)absence_end_date+1.
4989 -- ib) create from (old)absence_end_date+1
4990 --
4991 -- For case ii)
4992 -- iia) create from old absence_end_date+1
4993 -- iib) delete from new absence_end_date+1
4994 --
4995 -- For case iii) careful, normally we need to do nothing
4996 -- as the data will be deleted when the start is reversed
4997 -- and recreated when the start is re-processed. thats generally
4998 -- true for all start event (updates).
4999 --
5000 --
5001 --
5002 -- so
5003 -- if new_end_date < last_gap_daily_absence_date then
5004 -- delete between new_end_date+1 and last_gap_daily_absence_date
5005 -- if new_end_date > last_gap_daily_absence_date then
5006 -- create between last_gap_daily_absence_date+1 and new_end_date
5007 --
5008 -- how does this logic fit with the above scenarios
5009 -- i) normal end date processing
5010 -- new_end_date = last_gap_daily_absence_date => no action
5011 --
5012 -- ia) open ended absence closed
5013 -- implies new_end_date < last_gap_absence_date hence delete excess
5014 -- may rarely be new_end_date > last_gap_absence_date hence create missing
5015 --
5016 -- ib) closed absence re-opened
5017 -- implies new_date_date(eot)> last_gap_daily_absence_date hence create
5018 -- missing
5019 --
5020 -- ii) end date updates
5021 -- iia) absence end extended
5022 -- new_end_date > last_gap_daily_absence_date => create
5023 -- iib) absence shortened
5024 -- new_end_date < last_gap_daily_absence_date => delete
5025 --
5026 -- iii) Start event (update called with same new start and same end)
5027 -- new_end_date = last_gap_daily_absence_date => no action
5028 --
5029 -- what breaks the logic are open ended absences where it is not possible
5030 -- to determine whether an existing closed absence has been re-opended
5031 -- or has an update being invoked for open ended absences.
5032 --
5033 -- if updates are issued for open ended absences then in the worst case
5034 -- scenarion we need to determin the first date of no-pay and check
5035 -- if the last gap daily absence date = 365+first date of no pay
5036 --
5037 --
5038
5039 l_proc_step := 10;
5040 IF g_debug THEN
5041 debug(l_proc_name, 10);
5042 END IF;
5043
5044 -- Set value for open ended absence no pay days
5045 l_open_ended_no_pay_days :=
5046 PQP_UTILITIES.pqp_get_config_value(
5047 p_business_group_id => p_business_group_id
5048 ,p_legislation_code => 'GB'
5049 ,p_column_name => 'PCV_INFORMATION8'
5050 ,p_information_category => 'PQP_GB_OSP_OMP_CONFIG'
5051 );
5052
5053 g_open_ended_no_pay_days :=
5054 FND_NUMBER.canonical_to_number(NVL(l_open_ended_no_pay_days,365));
5055
5056 IF g_debug THEN
5057 debug('g_open_ended_no_pay_days', g_open_ended_no_pay_days);
5058 END IF;
5059
5060
5061
5062 l_absence_end_date := NVL(p_absence_date_end,hr_api.g_eot);
5063 --NVL(l_absence_dates.date_end,hr_api.g_eot);
5064
5065 --IF p_absence_date_start IS NOT NULL THEN -- ie absence not deleted
5066
5067 l_proc_step := 31;
5068 IF g_debug THEN
5069 debug(l_proc_name, 31);
5070 END IF;
5071
5072 OPEN csr_gap_absence_plan(p_absence_id, p_pl_id);
5073 FETCH csr_gap_absence_plan INTO l_gap_absence_plan;
5074 CLOSE csr_gap_absence_plan;
5075
5076 l_proc_step := 32;
5077 IF g_debug THEN
5078 debug(l_proc_name, 32);
5079 END IF;
5080
5081 IF l_absence_end_date = hr_api.g_eot -- if its an open ended absence
5082 THEN
5083
5084 l_proc_step := 33;
5085 IF g_debug THEN
5086 debug(l_proc_name, 33);
5087 END IF;
5088 -- if it can be shown that this code does get executed then
5089 -- we need to have l_absence_end_date
5090 -- else we can get rid of the absence_end_date also
5091
5092 OPEN csr_first_entitled_day_of_band
5093 (l_gap_absence_plan.gap_absence_plan_id
5094 ,'NOBAND'
5095 );
5096
5097 l_proc_step := 34;
5098 IF g_debug THEN
5099 debug(l_proc_name, 34);
5100 END IF;
5101
5102 FETCH csr_first_entitled_day_of_band INTO l_first_entitled_day_of_noband;
5103
5104 l_proc_step := 35;
5105 IF g_debug THEN
5106 debug(l_proc_name, 35);
5107 END IF;
5108
5109 IF csr_first_entitled_day_of_band%FOUND
5110 THEN
5111
5112 l_proc_step := 36;
5113 IF g_debug THEN
5114 debug(l_proc_name, 36);
5115 END IF;
5116
5117 IF l_first_entitled_day_of_noband.absence_date + l_open_ended_no_pay_days --(or 366 bug)
5118 = l_gap_absence_plan.last_gap_daily_absence_date
5119 THEN
5120
5121 l_proc_step := 37;
5122 IF g_debug THEN
5123 debug(l_proc_name, 37);
5124 END IF;
5125
5126 -- its an update call for an open ended absence.
5127 -- and needs no action as it has allready been generated to the
5128 -- maximum extent possible for an open ended absence.
5129 -- hence set the absence end date = last gap daily abs date
5130 l_absence_end_date := l_gap_absence_plan.last_gap_daily_absence_date;
5131
5132 END IF; -- IF l_first_entitled_day_of_noband.absence_date + 365
5133
5134 l_proc_step := 38;
5135 IF g_debug THEN
5136 debug(l_proc_name, 38);
5137 END IF;
5138
5139 END IF; -- IF csr_first_entitled_day_of_band%FOUND THEN
5140
5141 l_proc_step := 39;
5142 IF g_debug THEN
5143 debug(l_proc_name, 39);
5144 END IF;
5145
5146 CLOSE csr_first_entitled_day_of_band;
5147
5148 END IF; -- IF l_absence_end_date = hr_api.g_eot
5149
5150 l_proc_step := 40;
5151 IF g_debug THEN
5152 debug(l_proc_name, 40);
5153 END IF;
5154
5155 get_absence_part_days
5156 (p_absence_id => p_absence_id
5157 ,p_part_start_day => l_part_start_day
5158 ,p_part_end_day => l_part_end_day
5159 ,p_part_day_UOM => l_part_day_UOM
5160 );
5161
5162
5163 IF l_absence_end_date > l_gap_absence_plan.last_gap_daily_absence_date
5164 THEN
5165
5166 l_proc_step := 45;
5167 IF g_debug THEN
5168 debug(l_proc_name, 45);
5169 END IF;
5170 --Part Day Correction....
5171 --If Part days then delete current end date
5172 --Process from previous end date to the modified end date
5173
5174 IF l_part_end_day IS NOT NULL
5175 THEN
5176 delete_absence_plan_details
5177 (p_assignment_id => p_assignment_id
5178 ,p_business_group_id => p_business_group_id
5179 ,p_plan_id => p_pl_id
5180 ,p_absence_id => p_absence_id
5181 ,p_delete_start_date => l_gap_absence_plan.last_gap_daily_absence_date
5182 ,p_delete_end_date => l_gap_absence_plan.last_gap_daily_absence_date
5183 ,p_error_code => l_error_code
5184 ,p_message => l_error_message
5185 );
5186
5187 create_absence_plan_details
5188 (p_assignment_id => p_assignment_id
5189 ,p_person_id => p_person_id
5190 ,p_business_group_id => p_business_group_id
5191 ,p_absence_id => p_absence_id
5192 ,p_absence_date_start => p_absence_date_start
5193 ,p_absence_date_end => p_absence_date_end
5194 ,p_pl_id => p_pl_id
5195 ,p_pl_typ_id => p_pl_typ_id
5196 ,p_element_type_id => p_element_type_id
5197 ,p_create_start_date => l_gap_absence_plan.last_gap_daily_absence_date
5198 ,p_create_end_date => l_absence_end_date
5199 ,p_output_type => p_output_type
5200 ,p_error_code => l_error_code
5201 ,p_message => l_error_message
5202 );
5203 ELSE
5204 create_absence_plan_details
5205 (p_assignment_id => p_assignment_id
5206 ,p_person_id => p_person_id
5207 ,p_business_group_id => p_business_group_id
5208 ,p_absence_id => p_absence_id
5209 ,p_absence_date_start => p_absence_date_start
5210 ,p_absence_date_end => p_absence_date_end
5211 ,p_pl_id => p_pl_id
5212 ,p_pl_typ_id => p_pl_typ_id
5213 ,p_element_type_id => p_element_type_id
5214 ,p_create_start_date => l_gap_absence_plan.last_gap_daily_absence_date + 1
5215 ,p_create_end_date => l_absence_end_date
5216 ,p_output_type => p_output_type
5217 ,p_error_code => l_error_code
5218 ,p_message => l_error_message
5219 );
5220
5221 END IF;
5222
5223 ELSIF l_absence_end_date < l_gap_absence_plan.last_gap_daily_absence_date
5224 THEN
5225
5226 l_proc_step := 50;
5227 IF g_debug THEN
5228 debug(l_proc_name, 50);
5229 END IF;
5230 --Part Day Correction....
5231 --If Part days then delete start date to be l_absence_end_date
5232 --Process end date again by calling create absence for the end date
5233 IF l_part_end_day IS NOT NULL
5234 THEN
5235
5236 delete_absence_plan_details
5237 (p_assignment_id => p_assignment_id
5238 ,p_business_group_id => p_business_group_id
5239 ,p_plan_id => p_pl_id
5240 ,p_absence_id => p_absence_id
5241 ,p_delete_start_date => l_absence_end_date
5242 ,p_delete_end_date => l_gap_absence_plan.last_gap_daily_absence_date
5243 ,p_error_code => l_error_code
5244 ,p_message => l_error_message
5245 );
5246
5247 create_absence_plan_details
5248 (p_assignment_id => p_assignment_id
5249 ,p_person_id => p_person_id
5250 ,p_business_group_id => p_business_group_id
5251 ,p_absence_id => p_absence_id
5252 ,p_absence_date_start => p_absence_date_start
5253 ,p_absence_date_end => p_absence_date_end
5254 ,p_pl_id => p_pl_id
5255 ,p_pl_typ_id => p_pl_typ_id
5256 ,p_element_type_id => p_element_type_id
5257 ,p_create_start_date => l_absence_end_date
5258 ,p_create_end_date => l_absence_end_date
5259 ,p_output_type => p_output_type
5260 ,p_error_code => l_error_code
5261 ,p_message => l_error_message
5262 );
5263
5264 ELSE
5265 delete_absence_plan_details
5266 (p_assignment_id => p_assignment_id
5267 ,p_business_group_id => p_business_group_id
5268 ,p_plan_id => p_pl_id
5269 ,p_absence_id => p_absence_id
5270 ,p_delete_start_date => l_absence_end_date+1
5271 ,p_delete_end_date => l_gap_absence_plan.last_gap_daily_absence_date
5272 ,p_error_code => l_error_code
5273 ,p_message => l_error_message
5274 );
5275 END IF;
5276 ELSE -- l_absence_end_date = l_gap_absence_plan.last_gap_daily_absence_date
5277 -- no action required -- information only step.
5278 l_proc_step := 55;
5279 IF g_debug THEN
5280 debug(l_proc_name, 55);
5281 NULL; -- no action required
5282 END IF;
5283
5284 END IF; -- IF l_absence_end_date > last_gap_daily_absence_date
5285
5286 --END IF; -- IF p_absence_date_start IS NOT NULL THEN -- ie absence not deleted
5287
5288 EXCEPTION
5289 WHEN OTHERS THEN
5290 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
5291 debug_others
5292 (l_proc_name
5293 ,l_proc_step
5294 );
5295 IF g_debug THEN
5296 debug('Leaving: '||l_proc_name,-999);
5297 END IF;
5298 -- p_message := SQLERRM;
5299 -- p_error_code := -1;
5300 fnd_message.raise_error;
5301 ELSE
5302 RAISE;
5303 END IF;
5304 END update_absence_plan_details;
5305 --
5306 -- This procedure is called during create absence plans.
5307 --
5308 PROCEDURE create_absence_plan_details
5309 (p_assignment_id IN NUMBER
5310 ,p_person_id IN NUMBER
5311 ,p_business_group_id IN NUMBER
5312 ,p_absence_id IN NUMBER
5313 ,p_absence_date_start IN DATE
5314 ,p_absence_date_end IN DATE
5315 ,p_pl_id IN NUMBER
5316 ,p_pl_typ_id IN NUMBER
5317 ,p_element_type_id IN NUMBER
5318 ,p_create_start_date IN DATE
5319 ,p_create_end_date IN DATE
5320 ,p_output_type IN ff_exec.outputs_t
5321 ,p_error_code OUT NOCOPY NUMBER
5322 ,p_message OUT NOCOPY VARCHAR2
5323 )
5324 IS
5325
5326 invalid_length_of_service EXCEPTION;
5327
5328 l_absence_dates csr_absence_dates%ROWTYPE;
5329 l_gap_absence_plan csr_gap_absence_plan%ROWTYPE;
5330
5331 l_entitlement_parameters_UDT pay_user_tables.user_table_id%TYPE:= -1;
5332 l_length_of_service NUMBER(38);
5333 l_absence_start_date DATE;
5334 l_absence_end_date DATE;
5335 l_generate_start_date DATE;
5336 l_generate_end_date DATE;
5337 l_band_val VARCHAR2(10) := 'NONE';
5338 l_datatype VARCHAR2(6);
5339 l_value VARCHAR2(240);
5340 l_gap_absence_plan_id pqp_gap_absence_plans.gap_absence_plan_id%TYPE;
5341
5342 l_plan_information rec_plan_information;
5343 l_entitlements pqp_absval_pkg.t_entitlements;
5344 l_absences_taken_to_date pqp_absval_pkg.t_entitlements;
5345 l_entitlements_remaining pqp_absval_pkg.t_entitlements;
5346 l_daily_absences pqp_absval_pkg.t_daily_absences;
5347 --table of pqp_gda_shd.g_rec_type
5348 l_object_version_number pqp_gap_absence_plans.object_version_number%TYPE;
5349 l_gap_absence_plan_id pqp_gap_absence_plans.gap_absence_plan_id%TYPE;
5350
5351 l_error_code fnd_new_messages.message_number%TYPE;
5352 l_error_message fnd_new_messages.message_text%TYPE;
5353 l_calendar_column_name VARCHAR2(30);
5354 l_proc_step NUMBER(20,10);
5355 l_proc_name VARCHAR2(61)
5356 := g_package_name ||
5357 'create_absence_plan_details';
5358
5359 l_entitlement_UOM pay_element_type_extra_info.eei_information1%TYPE ;
5360 l_scheme_category pay_element_type_extra_info.eei_information1%TYPE ;
5361 l_working_days_per_week pqp_gap_daily_absences.working_days_per_week%TYPE;
5362
5363 l_override_scheme_start_date DATE;
5364 l_assignment_work_pattern pqp_assignment_attributes_f.work_pattern%TYPE ;
5365
5366
5367 --LG/PT
5368 -- l_standard_working_days pqp_gap_daily_absences.working_days_per_week%TYPE;
5369 -- l_standard_ft_work_pattern pqp_assignment_Attributes_f.work_pattern%TYPE ;
5370 -- l_assignment_work_pattern pqp_assignment_Attributes_f.work_pattern%TYPE ;
5371 l_contract_wp pqp_assignment_Attributes_f.work_pattern%TYPE ;
5372 l_current_factor NUMBER ;
5373 l_ft_factor NUMBER ;
5374 -- i NUMBER ;
5375 l_fte pqp_gap_daily_absences.fte%TYPE ;
5376 l_FT_absence_wp pqp_assignment_Attributes_f.work_pattern%TYPE ;
5377 l_FT_working_wp pqp_assignment_Attributes_f.work_pattern%TYPE ;
5378 l_is_full_timer BOOLEAN ;
5379 l_override_wp pqp_assignment_Attributes_f.work_pattern%TYPE ;
5380 l_assignment_wp pqp_assignment_Attributes_f.work_pattern%TYPE ;
5381 l_update_summary BOOLEAN;
5382 l_is_assignment_wp BOOLEAN;
5383
5384 BEGIN
5385
5386 g_debug := hr_utility.debug_enabled;
5387
5388 IF g_debug THEN
5389 debug_enter(l_proc_name);
5390 debug(p_assignment_id);
5391 debug(p_person_id);
5392 debug(p_business_group_id);
5393 debug(p_absence_id);
5394 debug(p_absence_date_start);
5395 debug(p_absence_date_end);
5396 debug(p_pl_id);
5397 debug(p_pl_typ_id);
5398 debug(p_element_type_id);
5399 debug(p_create_start_date);
5400 debug(p_create_end_date);
5401 l_proc_step := 10;
5402 debug(l_proc_name, 10);
5403 END IF;
5404
5405 -- Set global switch to toggle the summary table logging
5406
5407 IF g_log_duration_summary is NULL
5408 THEN
5409
5410 IF g_debug THEN
5411 debug(l_proc_name, 12);
5412 END IF;
5413
5414 g_log_duration_summary :=
5415 PQP_UTILITIES.pqp_get_config_value
5416 ( p_business_group_id => p_business_group_id
5417 ,p_legislation_code => 'GB'
5418 ,p_column_name => 'PCV_INFORMATION10'
5419 ,p_information_category => 'PQP_GB_OSP_OMP_CONFIG'
5420 );
5421
5422 g_log_duration_summary := NVL(g_log_duration_summary,'DISABLE');
5423
5424 IF g_debug THEN
5425 debug('g_log_duration_summary' || g_log_duration_summary);
5426 END IF;
5427
5428 END IF;
5429
5430
5431
5432 --Set the global rounding factor cache if the values are not already set
5433
5434 IF g_ft_entitl_rounding_type is null OR g_round_cache_plan_id <> p_pl_id THEN
5435 PQP_GB_OSP_FUNCTIONS.set_osp_omp_rounding_factors
5436 (p_pl_id => p_pl_id
5437 ,p_pt_entitl_rounding_type => g_pt_entitl_rounding_type
5438 ,p_pt_rounding_precision => g_pt_rounding_precision
5439 ,p_ft_entitl_rounding_type => g_ft_entitl_rounding_type
5440 ,p_ft_rounding_precision => g_ft_rounding_precision
5441 );
5442 g_round_cache_plan_id := p_pl_id ;
5443 END IF;
5444
5445
5446 IF g_debug THEN
5447 debug('p_pt_entitl_rounding_type' || g_pt_entitl_rounding_type);
5448 debug('p_pt_rounding_precision' , g_pt_rounding_precision);
5449 debug('p_ft_entitl_rounding_type' || g_ft_entitl_rounding_type);
5450 debug('p_ft_rounding_precision' , g_ft_rounding_precision);
5451 debug(l_proc_name, 15);
5452 END IF;
5453
5454 --
5455 -- To create daily absence details we need to know
5456 --
5457 -- 1. the assignments current entitlement
5458 -- to determine the current entitlement
5459 -- we need to
5460 -- a) know the length of service
5461 -- b) know the entitlement UDT (id or name)
5462 -- c) query the UDT with the length of service as the range
5463 --
5464 -- 2. the entitlement balance, to determine that we need to
5465 -- a) know the absences taken to date (ie in the absence year)
5466 -- b) entitlement remaining = entitlement from 1(c) - absence taken 2(a)
5467 --
5468 -- 3. then start a day by day debit process of the entilements remaining
5469 -- against the current absence and generate daily absence information
5470 -- a) for performance write this information to a pl/sql table
5471 -- b) do a bulk insert.
5472
5473 --OPEN csr_absence_dates(p_absence_id);
5474 --FETCH csr_absence_dates INTO l_absence_dates;
5475 --IF g_debug THEN
5476 --l_proc_step := 20;
5477 debug(l_proc_name, 20);
5478 --END IF;
5479 --CLOSE csr_absence_dates;
5480
5481 --IF g_debug THEN
5482 -- l_proc_step := 30;
5483 debug(l_proc_name, 30);
5484 --END IF;
5485
5486 -- we need to distinguish between the absence start and end dates
5487 -- and the p_create_start_date and p_create_end_date
5488 -- the p_start and p_end_date represent the range of dates over which
5489 -- the calling procedure wants the daily absence information to be generated
5490 -- which may or may not the whole range of the absence start and end date
5491 -- for eg
5492 -- during update processing
5493 -- if the end date has been extended by say another 10 days
5494 -- then we will call the create for the same absence id
5495 -- but pass a p_create_start_date as (last_end_date+1) and p_end_date
5496 -- as the new end date (last_end_date+10...)
5497 --
5498 -- in all cases three things are important
5499 -- a) the entitlement should always be derived as of the start of the absence
5500 -- b) generate_start_date = GREATEST(p_start,l_absence_start)
5501 -- c) generate_end_date = LEAST(p_end,l_absence_end)
5502 --
5503 -- to ensure a) we need to make sure that when fetching entitlements
5504 -- we pass the effective date as l_absence_start
5505 --
5506 -- when fetching the absences_taken_to_date we need to pass the
5507 -- generate_start_date
5508 --
5509 -- when generating the absences we need to pass the generate_start and end
5510 -- not the absence_start and end
5511 --
5512 -- why all this? to maximize re-usability of this code for update processing.
5513 --
5514 --
5515 -- create should always create between
5516 -- the NVL(last_gap_daily_absence_date+1,absence_start_date)
5517 --and
5518 -- the NVL(absence end date,first_day_of_NOBAND+365)
5519 --
5520 -- no matter what the p_create_start_date and p_end_date are
5521 --
5522 l_generate_start_date :=
5523 GREATEST(p_create_start_date,p_absence_date_start);
5524 --
5525 l_generate_end_date :=
5526 LEAST(NVL(p_create_end_date,hr_api.g_eot),p_absence_date_end);
5527 --
5528 -- If daily absences exist for the given range for this plan then
5529 -- don't continue with the create process. Exit without error.
5530 -- This would happen in almost every absence, ie when the end
5531 -- life event is encountered and assuming that the end hasn't changed
5532 -- the batch was run
5533 --
5534 l_proc_step := 50;
5535 IF g_debug THEN
5536 debug(l_proc_name, 50);
5537 END IF;
5538
5539 IF l_generate_start_date IS NOT NULL
5540 AND -- both are not needed but just makes it more robust
5541 l_generate_end_date IS NOT NULL
5542 THEN
5543
5544 l_proc_step := 60;
5545 IF g_debug THEN
5546 debug(l_proc_name, 60);
5547 END IF;
5548
5549 --
5550 -- The creation of daily absences is subject to the rules of the plan
5551 -- under which the absence is being processed. Since these rules
5552 -- will be referred to at various points in the process, and
5553 -- also repeatedly for each day of the absence, we upload the
5554 -- plan details into a cache which is repeatedly referred to till
5555 -- such time that the same call is made for a different plan.
5556 ---
5557 l_proc_step := 70;
5558 IF g_debug THEN
5559 debug(l_proc_name, 70);
5560 END IF;
5561
5562 get_plan_extra_info_n_cache_it
5563 (p_pl_id => p_pl_id
5564 ,p_plan_information => l_plan_information
5565 ,p_business_group_id => p_business_group_id
5566 ,p_assignment_id => p_assignment_id
5567 ,p_effective_date => p_absence_date_start
5568 --,p_error_code => l_error_code
5569 --,p_message => l_error_message
5570 );
5571
5572
5573 -- Copy to local as this then has to be passed as parameters
5574 -- Note the efficiency achieved is not because we refer
5575 -- to globals but because the global is not fetched from
5576 -- the database unless the plan to which it relates is changed.
5577
5578 l_proc_step := 75;
5579 IF g_debug THEN
5580 debug(l_proc_name, 75);
5581 END IF;
5582
5583
5584 -- Check here if the Scheme is a UNPAID Scheme.
5585 -- If it is unpaid dont need to look at length of service or entitlements
5586 -- Populate the entitlements table with BAND1 and 0
5587 -- and set the remaining values that are required for procedure
5588 -- generate_daily_absences and that should process the schemes
5589
5590 l_scheme_category := l_plan_information.absence_pay_plan_category ;
5591
5592
5593 --------------
5594
5595 IF UPPER(l_scheme_category) = 'UNPAID' THEN
5596
5597 l_entitlements_remaining(1).band := 'BAND1' ;
5598 l_entitlements_remaining(1).entitlement := 0 ;
5599 l_entitlement_UOM := l_plan_information.absence_days_type ;
5600 OPEN csr_get_wp(p_business_group_id => p_business_group_id
5601 ,p_assignment_id => p_assignment_id
5602 ,p_effective_date => l_generate_start_date);
5603
5604 FETCH csr_get_wp INTO l_assignment_work_pattern ;
5605 CLOSE csr_get_wp ;
5606
5607 IF l_assignment_work_pattern IS NULL
5608 THEN
5609 l_is_assignment_wp := FALSE;
5610 l_assignment_wp := l_plan_information.default_work_pattern_name;
5611 ELSE
5612 l_is_assignment_wp := TRUE;
5613 l_assignment_wp := l_assignment_work_pattern;
5614
5615 END IF;
5616 l_override_wp := l_assignment_wp;
5617
5618
5619 ELSE -- else if 'UNPAID' i.e. executed for 'SICKNESS'
5620
5621 -- 1a) retrieve and validate the length of service
5622 BEGIN
5623
5624 get_param_value
5625 (p_output_type => p_output_type
5626 ,p_name => 'LENGTH_OF_SERVICE'
5627 ,p_datatype => l_datatype
5628 ,p_value => l_value
5629 --,p_error_code => l_error_code
5630 --,p_message => l_error_message
5631 );
5632
5633 l_length_of_service := TO_NUMBER(l_value);
5634
5635 IF l_length_of_service IS NULL
5636 THEN
5637 RAISE invalid_length_of_service;
5638 END IF;
5639
5640 EXCEPTION
5641 WHEN VALUE_ERROR
5642 OR invalid_length_of_service
5643 OR INVALID_NUMBER -- doesn't arise in PL/SQL
5644 THEN
5645
5646 fnd_message.set_name( 'PQP', 'PQP_230012_OSPOMP_INALID_LOS' );
5647 fnd_message.set_token( 'TOKEN', NVL(l_value,'<Null>'));
5648 fnd_message.raise_error;
5649
5650 END;
5651
5652 BEGIN
5653
5654 get_param_value
5655 (p_output_type => p_output_type
5656 ,p_name => 'OVERRIDE_SCHEME_START_DATE'
5657 ,p_datatype => l_datatype
5658 ,p_value => l_value
5659 --,p_error_code => l_error_code
5660 --,p_message => l_error_message
5661 );
5662
5663 l_override_scheme_start_date := fnd_date.canonical_to_date(l_value);
5664 debug('l_override_scheme_start_date:'||
5665 fnd_date.date_to_canonical(l_override_scheme_start_date));
5666
5667 EXCEPTION
5668 WHEN VALUE_ERROR
5669 OR invalid_length_of_service
5670 OR INVALID_NUMBER -- doesn't arise in PL/SQL
5671 THEN
5672
5673 fnd_message.set_name( 'PQP', 'PQP_230012_OSPOMP_INALID_LOS' );
5674 fnd_message.set_token( 'TOKEN', NVL(l_value,'<Null>'));
5675 fnd_message.raise_error;
5676
5677 END;
5678
5679
5680
5681 -- 1b) the entitlement UDT (id or name)
5682 l_entitlement_parameters_UDT := l_plan_information.entitlement_parameters_UDT_id;
5683
5684 l_proc_step := 80;
5685 IF g_debug THEN
5686 debug(l_proc_name, 80);
5687 END IF;
5688
5689 -- 1c) Retrieve the entitlements from the UDT using the LOS
5690 -- multiply FTE in here.
5691 -- l_error_code:=
5692 -- pqp_gb_osp_functions.get_los_based_entitlements -- ppq_get_los_based_entitlements
5693 -- (p_business_group_id => p_business_group_id
5694 -- ,p_effective_date => p_absence_date_start -- business rule hard coded
5695 -- ,p_assignment_id => p_assignment_id
5696 -- ,p_pl_id => p_pl_id
5697 -- ,p_absence_pay_plan_class => 'OSP' -- ?? hard code not good for OMP ??
5698 -- ,p_entitlement_table_id => l_entitlement_parameters_UDT
5699 -- ,p_benefits_length_of_service => l_length_of_service
5700 -- ,p_band_entitlements => l_entitlements
5701 -- ,p_error_msg => l_error_message
5702 -- ,p_entitlement_bands_list_name => 'PQP_GAP_ENTITLEMENT_BANDS'
5703 -- default
5704 -- );
5705
5706 pqp_gb_osp_functions.get_entitlements
5707 (p_assignment_id => p_assignment_id
5708 ,p_business_group_id => p_business_group_id
5709 ,p_effective_date => p_absence_date_start
5710 ,p_pl_id => p_pl_id
5711 ,p_entitlement_table_id => l_entitlement_parameters_UDT
5712 ,p_benefits_length_of_service => l_length_of_service
5713 ,p_band_entitlements => l_entitlements
5714 ) ;
5715
5716 l_proc_step := 90;
5717 IF g_debug THEN
5718 debug(l_proc_name, 90);
5719 END IF;
5720
5721 check_error_code(l_error_code,l_error_message);
5722
5723
5724
5725 IF l_plan_information.scheme_period_type = 'DUALROLLING' THEN
5726
5727 l_proc_step := 91 ;
5728 IF g_debug THEN
5729 debug(l_proc_name,91);
5730 END IF;
5731
5732 -- PERSON LEVEL ABSENCE CHANGES FOR CSS
5733 -- the CSS absence creation calls proc get_absences_taken
5734 -- to determine the absences taken.
5735 -- The global is set here for civil services coz the call to
5736 -- get_absences_taken is direct from it , unlike thru
5737 -- the get_absences_taken_to_date.
5738
5739 g_deduct_absence_for :=
5740 PQP_UTILITIES.pqp_get_config_value
5741 ( p_business_group_id => p_business_group_id
5742 ,p_legislation_code => 'GB'
5743 ,p_column_name => 'PCV_INFORMATION9'
5744 ,p_information_category => 'PQP_GB_OSP_OMP_CONFIG'
5745 );
5746
5747 IF g_debug THEN
5748 debug(l_proc_name,l_proc_step);
5749 debug('g_deduct_absence_for :' ||g_deduct_absence_for);
5750 END IF;
5751
5752
5753 --Call the css daily absences procedure here.
5754 -- is this proc name appropriate now?
5755
5756 pqp_gb_css_daily_absences.create_absence_plan_details
5757 ( p_assignment_id => p_assignment_id
5758 ,p_business_group_id => p_business_group_id
5759 ,p_absence_id => p_absence_id
5760 ,p_pl_id => p_pl_id
5761 ,p_pl_typ_id => p_pl_typ_id
5762 ,p_create_start_date => l_generate_start_date
5763 ,p_create_end_date => l_generate_end_date
5764 ,p_entitlements => l_entitlements
5765 ,p_plan_information => l_plan_information
5766 ,p_entitlements_remaining => l_entitlements_remaining
5767 ,p_entitlement_UOM => l_entitlement_UOM
5768 ,p_working_days_per_week => l_working_days_per_week
5769 ,p_fte => l_fte
5770 -- ,p_error_code => l_error_code
5771 -- ,p_message => l_error_message
5772 ) ;
5773
5774
5775 ELSE -- 'DUALROLLING' check
5776
5777 --
5778 -- 2a) Retrieve the entitlements used upto date in this sickness year
5779 -- and then determine the entitlements remaining.
5780
5781 get_factors (
5782 p_business_group_id => p_business_group_id
5783 ,p_effective_date => p_absence_date_start
5784 ,p_assignment_id => p_assignment_id
5785 ,p_entitlement_uom => l_plan_information.absence_days_type
5786 ,p_default_wp => l_plan_information.default_work_pattern_name
5787 ,p_absence_schedule_wp => l_plan_information.absence_schedule_work_pattern
5788 ,p_track_part_timers => l_plan_information.track_part_timers
5789 ,p_current_factor => l_current_factor
5790 ,p_ft_factor => l_ft_factor
5791 ,p_working_days_per_week => l_working_days_per_week
5792 ,p_fte => l_fte
5793 ,p_FT_absence_wp => l_FT_absence_wp
5794 ,p_FT_working_wp => l_FT_working_wp
5795 ,p_assignment_wp => l_assignment_wp
5796 ,p_is_full_timer => l_is_full_timer
5797 ,p_is_assignment_wp => l_is_assignment_wp
5798 ) ;
5799
5800
5801 convert_entitlements
5802 ( p_entitlements => l_entitlements
5803 ,p_current_factor => l_current_factor
5804 ,p_ft_factor => l_ft_factor
5805 ) ;
5806
5807
5808 IF l_plan_information.absence_schedule_work_pattern IS NOT NULL
5809 AND l_is_full_timer THEN
5810 l_override_wp := l_FT_absence_wp ;
5811 ELSE
5812 l_override_wp := l_assignment_wp ;
5813 END IF ;
5814
5815 --
5816 get_absences_taken_to_date
5817 (p_assignment_id => p_assignment_id
5818 ,p_effective_date => l_generate_start_date
5819 ,p_business_group_id => p_business_group_id -- LG
5820 ,p_pl_typ_id => p_pl_typ_id
5821 ,p_scheme_period_overlap_rule => l_plan_information.absence_overlap_rule
5822 ,p_scheme_period_type => l_plan_information.scheme_period_type
5823 ,p_scheme_period_duration => l_plan_information.scheme_period_duration
5824 ,p_scheme_period_uom => l_plan_information.scheme_period_uom
5825 ,p_scheme_period_start => l_plan_information.scheme_period_start
5826 ,p_entitlements => l_entitlements
5827 ,p_absences_taken_to_date => l_absences_taken_to_date
5828 ,p_override_scheme_start_date => l_override_scheme_start_date
5829 ,p_plan_types_to_extend_period => l_plan_information.plan_types_to_extend_period
5830 ,p_entitlement_uom => l_plan_information.absence_days_type
5831 ,p_default_wp => l_plan_information.default_work_pattern_name
5832 ,p_absence_schedule_wp => l_plan_information.absence_schedule_work_pattern
5833 ,p_track_part_timers => l_plan_information.track_part_timers
5834 ,p_absence_start_date => p_absence_date_start
5835 );
5836
5837 l_proc_step := 100;
5838 IF g_debug THEN
5839 debug(l_proc_name, 100);
5840 debug('l_absences_taken_to_date.COUNT');
5841 debug(l_absences_taken_to_date.COUNT);
5842 END IF;
5843
5844 -- l_entitlements_remaining := l_entitlements - l_absences_taken_to_date
5845
5846 --l_error_code:=
5847 get_entitlements_remaining
5848 (p_assignment_id => p_assignment_id --LG/PT
5849 ,p_effective_date => l_generate_start_date --LG/PT
5850 ,p_entitlements => l_entitlements
5851 ,p_absences_taken_to_date => l_absences_taken_to_date
5852 ,p_entitlement_UOM => l_plan_information.absence_days_type
5853 ,p_entitlements_remaining => l_entitlements_remaining
5854 ,p_is_full_timer => l_is_full_timer
5855 );
5856
5857 --
5858 -- Summarize status at this point
5859 -- 1. we have the entitlements of each band relevant to the LOS in:
5860 -- l_entitlements
5861 -- 2. we have the entitlements remaining for each relevant band in:
5862 -- l_entitlements_remaining
5863 --
5864 -- 3a) we now need to process the current absence day by day
5865 -- and generate daily absence information. this information
5866 -- will be returned in a plsql table l_daily_absences
5867 --
5868 --
5869
5870 l_entitlement_UOM := l_plan_information.absence_days_type ;
5871
5872 END IF ;
5873
5874
5875 l_proc_step := 110;
5876 IF g_debug THEN
5877 debug(l_proc_name, 110);
5878 END IF;
5879
5880 --------------------------
5881 END IF ; -- end of 'UNPAID' check
5882
5883
5884 -- dont pass ent UOM directly from plan.get them into local variables and pass down
5885
5886 generate_daily_absences
5887 (p_assignment_id => p_assignment_id
5888 ,p_business_group_id => p_business_group_id
5889 ,p_absence_attendance_id => p_absence_id
5890 ,p_default_work_pattern_name => NVL(l_FT_working_wp
5891 ,l_plan_information.default_work_pattern_name)
5892 ,p_calendar_user_table_id => l_plan_information.entitlement_calendar_UDT_id
5893 ,p_calendar_rules_list => l_plan_information.calendar_rule_names_list
5894 ,p_generate_start_date => l_generate_start_date
5895 ,p_generate_end_date => l_generate_end_date
5896 ,p_absence_start_date => p_absence_date_start
5897 ,p_absence_end_date => p_absence_date_end
5898 ,p_entitlement_UOM => l_entitlement_UOM
5899 ,p_payment_UOM => l_plan_information.daily_rate_UOM
5900 ,p_output_type => p_output_type
5901 ,p_entitlements_remaining => l_entitlements_remaining
5902 ,p_daily_absences => l_daily_absences
5903 ,p_error_code => l_error_code
5904 ,p_message => l_error_message
5905 ,p_working_days_per_week =>l_working_days_per_week
5906 ,p_fte => l_fte
5907 ,p_override_work_pattern => l_override_wp
5908 ,p_pl_id => p_pl_id
5909 ,p_scheme_period_type => l_plan_information.scheme_period_type
5910 ,p_is_assignment_wp => l_is_assignment_wp
5911 );
5912
5913 IF g_debug THEN
5914 l_proc_step := 120;
5915 debug(l_proc_name, 120);
5916 END IF;
5917 --
5918 -- 3b) write to the cache (plsql table) to the datbase using bulk insert.
5919 --
5920
5921 -- write the parent PQP_GAP_ABSENCE_PLANS row
5922 -- check first if it exists, as this create may
5923 -- have been called from an update. its actualy ineffecient
5924 -- to do this again as the update code has allready gap absence plans
5925 -- in that case gap_absence_plan_id should be a parameter
5926
5927 OPEN csr_gap_absence_plan(p_absence_id, p_pl_id);
5928 FETCH csr_gap_absence_plan INTO l_gap_absence_plan;
5929 CLOSE csr_gap_absence_plan;
5930
5931 l_proc_step := 130;
5932 IF g_debug THEN
5933 debug(l_proc_name, 130);
5934 END IF;
5935
5936 IF l_gap_absence_plan.gap_absence_plan_id IS NULL
5937 THEN
5938
5939 l_proc_step := 135;
5940 IF g_debug THEN
5941 debug(l_proc_name, 135);
5942 END IF;
5943
5944 pqp_gap_ins.ins
5945 (p_effective_date => l_daily_absences(l_daily_absences.LAST).absence_date
5946 ,p_assignment_id => p_assignment_id
5947 ,p_absence_attendance_id => p_absence_id
5948 ,p_pl_id => p_pl_id
5949 ,p_last_gap_daily_absence_date => l_daily_absences(l_daily_absences.LAST).absence_date
5950 ,p_gap_absence_plan_id => l_gap_absence_plan.gap_absence_plan_id
5951 ,p_object_version_number => l_gap_absence_plan.object_version_number
5952 );
5953 l_update_summary := FALSE ;
5954 ELSE
5955
5956 l_proc_step := 137;
5957 IF g_debug THEN
5958 debug(l_proc_name, 137);
5959 END IF;
5960
5961 pqp_gap_upd.upd
5962 (p_effective_date => l_daily_absences(l_daily_absences.LAST).absence_date
5963 ,p_gap_absence_plan_id => l_gap_absence_plan.gap_absence_plan_id
5964 ,p_object_version_number => l_gap_absence_plan.object_version_number
5965 ,p_assignment_id => p_assignment_id
5966 ,p_absence_attendance_id => p_absence_id
5967 ,p_pl_id => p_pl_id
5968 ,p_last_gap_daily_absence_date => l_daily_absences(l_daily_absences.LAST).absence_date
5969 );
5970 l_update_summary := TRUE ;
5971 END IF; -- IF l_gap_absence_plan.gap_absence_plan_id IS NULL
5972
5973 -- write the child PQP_GAP_DAILY_ABSENCES row
5974 l_proc_step := 140;
5975 IF g_debug THEN
5976 debug(l_proc_name, 140);
5977 END IF;
5978
5979 write_daily_absences
5980 (p_daily_absences => l_daily_absences
5981 ,p_gap_absence_plan_id => l_gap_absence_plan.gap_absence_plan_id
5982 -- ideally we wouldn't need to pass anything to this procedure
5983 -- other than the cache that needs to written. However since
5984 -- the cache does not hold a gap_absence_plan_id we need to
5985 -- populate it in the cache before calling the bulk bind process.
5986 -- Since we have to loop through the cache once before to split
5987 -- it out into seperate scalar plsql tables we use the same loop
5988 -- to populate all the entries in the cache with these absence plan
5989 -- ids, hence an additional p_gap_absence_plan_id
5990 );
5991 -- We feed the summary data for reporting purposes to fill in the
5992 -- summary and balance tables
5993
5994 --Summary Table Changes Feed in to balance table
5995 IF g_log_duration_summary = 'ENABLE' THEN
5996
5997 IF g_debug THEN
5998 debug(l_proc_name, 145);
5999 END IF;
6000
6001 write_absence_summary
6002 (P_GAP_ABSENCE_PLAN_ID => l_gap_absence_plan.gap_absence_plan_id
6003 ,P_ASSIGNMENT_ID => p_assignment_id
6004 ,P_ENTITLEMENT_GRANTED => l_entitlements
6005 ,P_ENTITLEMENT_USED_TO_DATE => l_absences_taken_to_date
6006 ,P_ENTITLEMENT_REMAINING => l_entitlements_remaining
6007 ,P_FTE => l_fte
6008 ,P_WORKING_DAYS_PER_WEEK => l_working_days_per_week
6009 ,P_ENTITLEMENT_UOM => l_plan_information.absence_days_type
6010 ,p_update => l_update_summary
6011 );
6012 END IF;
6013 --Summary Table Changes
6014 END IF; -- if chk_record does not exist
6015
6016 IF g_debug THEN
6017 debug_exit(l_proc_name);
6018 END IF;
6019
6020
6021 EXCEPTION
6022 WHEN OTHERS THEN
6023 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
6024 debug_others
6025 (l_proc_name
6026 ,l_proc_step
6027 );
6028 IF g_debug THEN
6029 debug('Leaving: '||l_proc_name,-999);
6030 END IF;
6031 -- p_message := SQLERRM;
6032 -- p_error_code := -1;
6033 fnd_message.raise_error;
6034 ELSE
6035 RAISE;
6036 END IF;
6037 END create_absence_plan_details;
6038 --
6039
6040 FUNCTION get_contract_level_wp (p_business_group_id IN NUMBER
6041 ,p_assignment_id IN NUMBER
6042 ,p_effective_date IN DATE )
6043 RETURN VARCHAR2 IS
6044 l_proc_step NUMBER(20,10);
6045 l_proc_name VARCHAR2(61):= g_package_name||'get_contract_level_wp';
6046
6047 CURSOR csr_get_contract_type(
6048 p_business_group_id NUMBER
6049 ,p_assignment_id NUMBER
6050 ,p_effective_date DATE ) IS
6051 SELECT contract_type
6052 FROM pqp_assignment_attributes_f
6053 WHERE business_group_id = p_business_group_id
6054 AND assignment_id = p_assignment_id
6055 AND p_effective_date BETWEEN effective_start_date
6056 AND effective_end_date ;
6057 l_contract_type pqp_assignment_attributes_f.contract_type%TYPE ;
6058 l_contract_level_wp pqp_assignment_attributes_f.work_pattern%TYPE ;
6059 l_error_code NUMBER ;
6060 l_error_message fnd_new_messages.message_text%TYPE ;
6061
6062
6063 BEGIN
6064
6065 g_debug := hr_utility.debug_enabled;
6066
6067 l_proc_Step := 10 ;
6068 IF g_debug THEN
6069 debug_enter(l_proc_name);
6070 debug(p_business_group_id);
6071 debug(p_assignment_id);
6072 debug(p_effective_date);
6073 END IF ;
6074
6075 OPEN csr_get_contract_type(
6076 p_business_group_id => p_business_group_id
6077 ,p_assignment_id => p_assignment_id
6078 ,p_effective_date => p_effective_date ) ;
6079 FETCH csr_get_contract_type INTO l_contract_type ;
6080 CLOSE csr_get_contract_type ;
6081
6082 l_proc_Step := 20 ;
6083 IF g_debug THEN
6084 debug(' Contract type:'||l_contract_type);
6085 END IF ;
6086
6087 IF l_contract_type IS NOT NULL THEN
6088
6089 l_error_code :=
6090 pqp_utilities.pqp_gb_get_table_value
6091 ( p_business_group_id => p_business_group_id
6092 ,p_effective_date => p_effective_date
6093 ,p_table_name => 'PQP_CONTRACT_TYPES'
6094 ,p_column_name => 'Full Time Work Pattern'
6095 ,p_row_name => l_contract_type
6096 ,p_value => l_contract_level_wp
6097 ,p_error_msg => l_error_message
6098 ) ;
6099 l_proc_Step := 30 ;
6100 IF g_debug THEN
6101 debug(' Contract Level WP:'||l_contract_level_wp);
6102 END IF ;
6103
6104 END IF ;
6105
6106 IF g_debug THEN
6107 debug_exit(l_proc_name);
6108 END IF ;
6109
6110 RETURN l_contract_level_wp ;
6111 EXCEPTION
6112 WHEN OTHERS THEN
6113 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
6114 debug_others
6115 (l_proc_name
6116 ,l_proc_step
6117 );
6118 IF g_debug THEN
6119 debug('Leaving: '||l_proc_name,-999);
6120 END IF;
6121 fnd_message.raise_error;
6122 ELSE
6123 RAISE;
6124 END IF;
6125 END get_contract_level_wp ;
6126
6127
6128
6129
6130 FUNCTION get_absence_standard_ft_wp(p_business_group_id IN NUMBER
6131 ,p_assignment_id IN NUMBER
6132 ,p_effective_date IN DATE
6133 ,p_absence_schedule_wp IN VARCHAR2
6134 ,p_default_wp IN VARCHAR2
6135 ,p_entitlement_uom IN VARCHAR2
6136 ,p_contract_wp OUT NOCOPY VARCHAR2 )
6137 RETURN VARCHAR2 IS
6138 l_proc_step NUMBER(20,10);
6139 l_proc_name VARCHAR2(61):= g_package_name||'get_absence_standard_ft_wp';
6140 l_standard_ft_work_pattern pqp_assignment_attributes_f.work_pattern%TYPE ;
6141
6142 BEGIN
6143
6144 g_debug := hr_utility.debug_enabled;
6145
6146 l_proc_step := 10 ;
6147 IF g_debug THEN
6148 debug_enter(l_proc_name);
6149 debug(p_business_group_id);
6150 debug(p_assignment_id);
6151 debug(p_effective_date);
6152 debug(p_absence_schedule_wp);
6153 debug(p_default_wp);
6154 END IF ;
6155
6156 IF p_absence_schedule_wp IS NOT NULL THEN
6157 l_proc_step := 20 ;
6158 l_standard_ft_work_pattern := p_absence_schedule_wp ;
6159 ELSIF p_default_wp IS NOT NULL THEN
6160 l_proc_step := 30 ;
6161 l_standard_ft_work_pattern := p_default_wp ;
6162 ELSE
6163 l_proc_step := 40 ;
6164 l_standard_ft_work_pattern :=
6165 get_contract_level_wp (
6166 p_business_group_id => p_business_group_id
6167 ,p_assignment_id => p_assignment_id
6168 ,p_effective_date => p_effective_date ) ;
6169
6170 p_contract_wp := l_standard_ft_work_pattern ;
6171
6172 END IF ;
6173
6174 IF l_standard_ft_work_pattern IS NULL THEN
6175 IF p_entitlement_uom <> 'H' THEN
6176 l_standard_ft_work_pattern := 'PQP_MON_FRI_8_HOURS';
6177 ELSE
6178 -- Raise Error.
6179 hr_utility.set_message(8303, 'PQP_230000_INVALID_WORK_PAT');
6180 hr_utility.raise_error ;
6181 END IF ;
6182 END IF ;
6183
6184 l_proc_step := 50 ;
6185 IF g_debug THEN
6186 debug('l_standard_ft_work_pattern:'||l_standard_ft_work_pattern);
6187 debug_exit(l_proc_name);
6188 END IF ;
6189
6190 RETURN l_standard_ft_work_pattern ;
6191
6192 EXCEPTION
6193 WHEN OTHERS THEN
6194 p_contract_wp := NULL ;
6195 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
6196 debug_others
6197 (l_proc_name
6198 ,l_proc_step
6199 );
6200 IF g_debug THEN
6201 debug('Leaving: '||l_proc_name,-999);
6202 END IF;
6203 fnd_message.raise_error;
6204 ELSE
6205 RAISE;
6206 END IF;
6207 END get_absence_standard_ft_wp ;
6208
6209
6210 -- Move to pqp_schedule_calc_pkg and llok at caching options at a correct level
6211 FUNCTION get_average_days_per_week(
6212 p_business_group_id IN NUMBER
6213 ,p_effective_date IN DATE
6214 ,p_work_pattern IN VARCHAR2 )
6215 RETURN NUMBER IS
6216 l_proc_step NUMBER(20,10);
6217 l_proc_name VARCHAR2(61):= g_package_name||'get_average_days_per_week';
6218 l_standard_ft_work_pattern pqp_assignment_attributes_f.work_pattern%TYPE ;
6219 l_average_days_per_week NUMBER ;
6220 BEGIN
6221
6222 g_debug := hr_utility.debug_enabled;
6223
6224 l_proc_step := 10 ;
6225 IF g_debug THEN
6226 debug_enter(l_proc_name);
6227 debug(p_business_group_id);
6228 debug(p_effective_date);
6229 debug(p_work_pattern);
6230 END IF ;
6231
6232 -- cache this results at this level.
6233 -- do we need to consider effective_Date for caching.
6234 -- if we, lets say the standard work pattern is same for most of the employees
6235 -- but as effective date will vary ( its absence start date ),
6236 -- the caching may not be effective....
6237
6238 l_average_days_per_week :=
6239 pqp_schedule_calculation_pkg.get_working_days_in_week (
6240 p_assignment_id => NULL
6241 ,p_business_group_id => p_business_group_id
6242 ,p_effective_date => p_effective_date
6243 ,p_override_wp => p_work_pattern
6244 ) ;
6245
6246 IF l_average_days_per_week <= 0 THEN
6247 fnd_message.set_name( 'PQP', 'PQP_23000_INV_WORK_PATTERN' );
6248 fnd_message.raise_error ;
6249 END IF ;
6250
6251 IF g_debug THEN
6252 debug('l_average_days_per_week:'||l_average_days_per_week);
6253 debug_exit(l_proc_name);
6254 END IF ;
6255
6256 RETURN l_average_days_per_week ;
6257
6258
6259 EXCEPTION
6260 WHEN OTHERS THEN
6261 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
6262 debug_others
6263 (l_proc_name
6264 ,l_proc_step
6265 );
6266 IF g_debug THEN
6267 debug('Leaving: '||l_proc_name,-999);
6268 END IF;
6269 fnd_message.raise_error;
6270 ELSE
6271 RAISE;
6272 END IF;
6273
6274 END get_average_days_per_week ;
6275
6276
6277
6278 FUNCTION get_assignment_work_pattern (p_business_group_id IN NUMBER
6279 ,p_assignment_id IN NUMBER
6280 ,p_effective_date IN DATE
6281 ,p_default_wp IN VARCHAR2
6282 ,p_contract_wp IN VARCHAR2
6283 ,p_is_assignment_wp OUT NOCOPY BOOLEAN)
6284 RETURN VARCHAR2 IS
6285 l_proc_step NUMBER(20,10);
6286 l_proc_name VARCHAR2(61):= g_package_name||'get_assignment_work_pattern';
6287 l_assignment_work_pattern pqp_assignment_attributes_f.work_pattern%TYPE ;
6288
6289
6290 BEGIN
6291
6292 g_debug := hr_utility.debug_enabled;
6293
6294 l_proc_step := 10 ;
6295 IF g_debug THEN
6296 debug_enter(l_proc_name);
6297 debug(p_business_group_id);
6298 debug(p_assignment_id);
6299 debug(p_effective_date);
6300 debug(p_default_wp);
6301 debug(p_contract_wp);
6302 END IF ;
6303
6304 OPEN csr_get_wp (p_business_group_id => p_business_group_id
6305 ,p_assignment_id => p_assignment_id
6306 ,p_effective_date => p_effective_date);
6307 FETCH csr_get_wp INTO l_assignment_work_pattern ;
6308 CLOSE csr_get_wp ;
6309
6310 IF l_assignment_work_pattern IS NULL
6311 THEN
6312 l_assignment_work_pattern := NVL(p_default_wp,p_contract_wp);
6313 p_is_assignment_wp := FALSE;
6314 ELSE
6315 p_is_assignment_wp := TRUE;
6316 END IF;
6317
6318 IF g_debug THEN
6319 debug('l_assignment_work_pattern:'||l_assignment_work_pattern);
6320 debug_exit(l_proc_name);
6321 END IF ;
6322
6323 RETURN l_assignment_work_pattern ;
6324
6325 EXCEPTION
6326 WHEN OTHERS THEN
6327 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
6328 debug_others
6329 (l_proc_name
6330 ,l_proc_step
6331 );
6332 IF g_debug THEN
6333 debug('Leaving: '||l_proc_name,-999);
6334 END IF;
6335 fnd_message.raise_error;
6336 ELSE
6337 RAISE;
6338 END IF;
6339 END get_assignment_work_pattern ;
6340
6341
6342 FUNCTION get_calendar_days_to_extend(
6343 p_period_start_date IN DATE
6344 ,p_period_end_date IN DATE
6345 ,p_assignment_id IN NUMBER
6346 ,p_business_group_id IN NUMBER
6347 ,p_pl_typ_id IN NUMBER
6348 ,p_count_nopay_days IN BOOLEAN
6349 ,p_plan_types_lookup_type IN VARCHAR2
6350 ) RETURN NUMBER IS
6351 l_tot_pay_days NUMBER ;
6352 l_tot_no_pay_days NUMBER ;
6353 l_tot_no_pay_days_extend NUMBER ;
6354 l_person_id NUMBER;
6355 l_proc_name VARCHAR2(61) := g_package_name||'get_calendar_days_to_extend';
6356 l_proc_step NUMBER(20,10) ;
6357
6358
6359 -- the comment below was applicable till 115.70..
6360 -- This cursor gets the NOPAID days only
6361 -- for Civil Service Scheme, as in 4 years
6362 -- for the current primary assignmeng.
6363 -- This is the defaulty functionality
6364
6365
6366 -- Changes done for 115.71 correspond to 7585452
6367 -- now this cursor is getting total paid absences taken for the plan that are to be included in extension rule
6368 -- wherever the query is changed the comment is wriiten accordingly.
6369 CURSOR csr_css_no_pay_days IS
6370 select NVL(SUM(gda.duration),0)
6371 from pqp_gap_daily_absences gda
6372 ,pqp_gap_absence_plans gap
6373 ,ben_pl_f pl
6374 ,hr_lookups hrl --added extra 7585452
6375 where pl.pl_id = gap.pl_id
6376 and pl.pl_typ_id = p_pl_typ_id
6377 and gap.gap_absence_plan_id = gda.gap_absence_plan_id
6378 and gap.assignment_id = p_assignment_id
6379 and gda.level_of_pay like 'BAND%' --changed from NOBAND to BAND% 7585452
6380 and hrl.lookup_code=pl.pl_typ_id --added extra 7585452
6381 and hrl.lookup_type=p_plan_types_lookup_type --added extra 7585452
6382 and gda.absence_date between p_period_start_date
6383 and (p_period_end_date - 1); -- bug 7110645
6384
6385 --
6386
6387 -- the comment below was applicable till 115.70..
6388
6389 -- This cursor gets all the NOPAID Days
6390 -- for the civil service scheme , as in 4 years
6391 -- for all the primary assignments' absences
6392 -- in the current period of service
6393
6394 -- Changes done for 115.71 correspond to 7585452
6395 -- now this cursor is getting total paid absences taken for the plan that are to be included in extension rule
6396 -- wherever the query is changed the comment is wriiten accordingly.
6397
6398 CURSOR csr_css_no_pay_curpos IS
6399 select NVL(SUM(gda.duration),0)
6400 from pqp_gap_daily_absences gda
6401 ,pqp_gap_absence_plans gap
6402 ,ben_pl_f pl
6403 ,hr_lookups hrl --added extra 7585452
6404 where gap.assignment_id IN
6405 -- automatically makes the assignment list distinct
6406 (SELECT other_asg.assignment_id
6407 FROM per_all_assignments_f this_asg
6408 ,per_all_assignments_f other_asg
6409 WHERE this_asg.assignment_id = p_assignment_id
6410 AND other_asg.person_id = this_asg.person_id
6411 AND other_asg.primary_flag = 'Y'
6412 AND other_asg.period_of_service_id = this_asg.period_of_service_id
6413 )
6414 and pl.pl_id = gap.pl_id
6415 and pl.pl_typ_id = p_pl_typ_id
6416 and gap.gap_absence_plan_id = gda.gap_absence_plan_id
6417 and gda.level_of_pay like 'BAND%' --changed from NOBAND to BAND% 7585452
6418 and hrl.lookup_code=pl.pl_typ_id --added extra 7585452
6419 and hrl.lookup_type=p_plan_types_lookup_type --added extra 7585452
6420 and gda.absence_date between p_period_start_date
6421 and (p_period_end_date - 1) ; -- bug 7110645
6422
6423
6424 --
6425
6426
6427
6428 -- the comment below was applicable till 115.70..
6429 -- This cursor gets all the NOPAID Days
6430 -- for the civil service scheme , as in 4 years
6431 -- for all the primary assignments' absences
6432 -- in all the period of service for the person
6433
6434 -- Changes done for 115.71 correspond to 7585452
6435 -- now this cursor is getting total paid absences taken for the plan that are to be included in extension rule
6436 -- wherever the query is changed the comment is written accordingly.
6437
6438 CURSOR csr_css_no_pay_allpos(p_person_id IN NUMBER) IS
6439 select NVL(SUM(gda.duration),0)
6440 from pqp_gap_daily_absences gda
6441 ,pqp_gap_absence_plans gap
6442 ,ben_pl_f pl
6443 ,hr_lookups hrl --added extra 7585452
6444 where gap.assignment_id IN
6445 -- automatically makes the assignment list distinct
6446 (SELECT asg.assignment_id
6447 FROM per_all_assignments_f asg
6448 WHERE asg.person_id = p_person_id
6449 AND asg.primary_flag = 'Y'
6450 )
6451
6452 and pl.pl_id = gap.pl_id
6453 and pl.pl_typ_id = p_pl_typ_id
6454 and gap.gap_absence_plan_id = gda.gap_absence_plan_id
6455 and gda.level_of_pay like 'BAND%' --changed from NOBAND to BAND% 7585452
6456 and hrl.lookup_code=pl.pl_typ_id --added extra 7585452
6457 and hrl.lookup_type=p_plan_types_lookup_type --added extra 7585452
6458 and gda.absence_date between p_period_start_date
6459 and (p_period_end_date - 1); -- bug 7110645
6460
6461
6462 l_pl_typ_id ben_pl_f.pl_typ_id%TYPE ;
6463 BEGIN
6464
6465 g_debug := hr_utility.debug_enabled;
6466
6467 IF g_debug THEN
6468 debug_enter(l_proc_name);
6469 debug('p_period_start_date:'||p_period_start_date);
6470 debug('p_period_end_date:'||p_period_end_date);
6471 debug('p_assignment_id:'||p_assignment_id);
6472 debug('p_business_group_id:'||p_business_group_id);
6473 debug('p_pl_typ_id:'||p_pl_typ_id);
6474 debug('p_plan_types_lookup_type:'||p_plan_types_lookup_type);
6475 END IF;
6476
6477 -- the number of days to be extended are returned by this function
6478 -- for both 4-year and 1-year we have to roll back by all paid absences
6479 -- and nopiad absences of absences other than CS.
6480 -- only exception being for 1-year we have to extend even the NOPAID days
6481 -- of CS.
6482 -- So assume that the plan types are stored for all the absence categories
6483 -- that needs to be considered in extending in a lookup
6484 -- PQP_GAP_PLAN_TYPES_TO_EXTEND. This is required as there is no UI option
6485 -- yet to support the selection of such plan types.
6486 -- if it is for 4-year return the sum of those plan types absences
6487 -- if for 1-year include even the CS NOPAID days and return.
6488
6489 l_proc_step := 10 ;
6490
6491 OPEN csr_get_days_to_extend (
6492 p_business_group_id => p_business_group_id
6493 ,p_assignment_id => p_assignment_id
6494 ,p_period_start_date => p_period_start_date
6495 ,p_period_end_date => p_period_end_date
6496 ,p_lookup_type => p_plan_types_lookup_type --'PQP_GAP_PLAN_TYPES_TO_EXTEND'
6497 ) ;
6498 FETCH csr_get_days_to_extend INTO l_tot_pay_days ;
6499 CLOSE csr_get_days_to_extend ;
6500 l_tot_no_pay_days := NVL(l_tot_pay_days,0);
6501 l_proc_step := 20 ;
6502 -- p_dont_chk_pl_typ_id should have FALSE for 1-year rolling period
6503 -- and TRUE for 4-year rolling period
6504 IF p_count_nopay_days THEN
6505
6506 l_proc_step := 30 ;
6507 IF g_debug THEN
6508 debug(l_proc_name,l_proc_step);
6509 debug('g_deduct_absence_for'||g_deduct_absence_for);
6510 END IF;
6511
6512 -- find out the option chosen for Deduct Absence Taken For
6513 -- from the global and open the appropriate cursor
6514
6515 IF (g_deduct_absence_for = 'PRIMASGCURPOS') THEN
6516 OPEN csr_css_no_pay_curpos ;
6517 FETCH csr_css_no_pay_curpos INTO l_tot_no_pay_days_extend ;
6518 CLOSE csr_css_no_pay_curpos ;
6519 ELSIF (g_deduct_absence_for = 'PRIMASGALLPOS') THEN
6520 SELECT asg.person_id
6521 INTO l_person_id
6522 FROM per_all_assignments_f asg
6523 WHERE asg.assignment_id = p_assignment_id
6524 AND ROWNUM < 2;
6525 IF g_debug THEN
6526 debug('l_person_id:' ,l_person_id);
6527 END IF;
6528 OPEN csr_css_no_pay_allpos(p_person_id => l_person_id) ;
6529 FETCH csr_css_no_pay_allpos INTO l_tot_no_pay_days_extend ;
6530 CLOSE csr_css_no_pay_allpos ;
6531 ELSE
6532 OPEN csr_css_no_pay_days ;
6533 FETCH csr_css_no_pay_days INTO l_tot_no_pay_days_extend ;
6534 CLOSE csr_css_no_pay_days ;
6535 END IF;
6536 debug(to_char(l_tot_no_pay_days)||'-vaibhav-'||to_char(l_tot_no_pay_days_extend));
6537
6538 --changed for 7585452 Please note variable understanding may be wrong here by their names.
6539 -- So new meaning of variables as follows...
6540 --l_tot_no_pay_days_extend -- it is now paid days taken for under the plan type that should be considered for extension rule.
6541 -- initial it was total no_pay taken (all without constraint of particulatr plan type like extension rule etc.)
6542 --l_tot_no_pay_days --same as before
6543 --l_tot_no_pay_days --same as before
6544
6545 l_tot_no_pay_days := l_tot_no_pay_days - NVL(l_tot_no_pay_days_extend,0) ;
6546
6547 --initially it was..(till 115.70)
6548 -- l_tot_no_pay_days := NVL(l_tot_no_pay_days_extend,0) + l_tot_no_pay_days ;
6549
6550 l_proc_step := 40 ;
6551 IF g_debug THEN
6552 debug('4-Year Rolling Period no pay days:'||l_tot_no_pay_days);
6553 ENd IF;
6554 END IF;
6555
6556 IF g_debug THEN
6557 debug('No Pay Days:'||l_tot_no_pay_days);
6558 debug_exit(l_proc_name) ;
6559 END IF ;
6560
6561 RETURN NVL(l_tot_no_pay_days,0) ;
6562
6563 EXCEPTION
6564 WHEN OTHERS THEN
6565 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
6566 pqp_utilities.debug_others
6567 (l_proc_name
6568 ,l_proc_step
6569 );
6570 IF g_debug THEN
6571 debug('Leaving: '||l_proc_name,-999);
6572 END IF;
6573 fnd_message.raise_error;
6574 ELSE
6575 RAISE;
6576 END IF;
6577
6578 END get_calendar_days_to_extend;
6579
6580
6581 PROCEDURE get_factors (
6582 p_business_group_id IN NUMBER
6583 ,p_effective_date IN DATE
6584 ,p_assignment_id IN NUMBER
6585 ,p_entitlement_uom IN VARCHAR2
6586 ,p_default_wp IN VARCHAR2
6587 ,p_absence_schedule_wp IN VARCHAR2
6588 ,p_track_part_timers IN VARCHAR2
6589 ,p_current_factor OUT NOCOPY NUMBER
6590 ,p_ft_factor OUT NOCOPY NUMBER
6591 ,p_working_days_per_week OUT NOCOPY NUMBER
6592 ,p_fte OUT NOCOPY NUMBER
6593 ,p_FT_absence_wp OUT NOCOPY VARCHAR2
6594 ,p_FT_working_wp OUT NOCOPY VARCHAR2
6595 ,p_assignment_wp OUT NOCOPY VARCHAR2
6596 ,p_is_full_timer OUT NOCOPY BOOLEAN
6597 ,p_is_assignment_wp OUT NOCOPY BOOLEAN
6598 ) IS
6599 l_proc_name VARCHAR2(61) := g_package_name||'get_factors';
6600 l_proc_step NUMBER(20,10) ;
6601
6602 l_FT_absence_wp pqp_assignment_attributes_f.work_pattern%TYPE ;
6603 l_FT_working_wp pqp_assignment_attributes_f.work_pattern%TYPE ;
6604 l_contract_wp pqp_assignment_attributes_f.work_pattern%TYPE ;
6605 l_assignment_wp pqp_assignment_attributes_f.work_pattern%TYPE ;
6606
6607 l_FT_working_dpw NUMBER ;
6608 l_FT_absence_wp_dpw NUMBER ;
6609 l_assignment_wp_dpw NUMBER ;
6610 l_fte_value NUMBER ;
6611 l_current_factor NUMBER ;
6612 l_ft_factor NUMBER ;
6613 BEGIN
6614
6615 g_debug := hr_utility.debug_enabled;
6616
6617 IF g_debug THEN
6618 debug_enter(l_proc_name);
6619 debug('p_assignment_id:'||p_assignment_id);
6620 debug('p_business_group_id:'||p_business_group_id);
6621 debug('p_effective_date:'||p_effective_date);
6622 debug('p_default_wp:'||p_default_wp);
6623 debug('p_absence_schedule_wp:'||p_absence_schedule_wp);
6624 debug('p_track_part_timers:'||p_track_part_timers);
6625 END IF;
6626
6627 p_is_full_timer := FALSE ;
6628
6629 IF p_entitlement_uom = 'H' THEN
6630 l_fte_value :=
6631 pqp_fte_utilities.get_fte_value
6632 (p_assignment_id => p_assignment_id
6633 ,p_calculation_date => p_effective_date ) ;
6634 l_fte_value := NVL(l_fte_value,1) ;
6635 END IF ;
6636
6637 -- ELSE -- IF l_plan_information.absence_days_type IN ('C','W') THEN
6638 -- This information is available for Hours too as they have
6639 -- Work Pattern attached.
6640 l_FT_absence_wp :=
6641 get_absence_standard_ft_wp(
6642 p_business_group_id => p_business_group_id
6643 ,p_assignment_id => p_assignment_id
6644 ,p_effective_date => p_effective_date
6645 ,p_absence_schedule_wp => p_absence_schedule_wp
6646 ,p_default_wp => p_default_wp
6647 ,p_entitlement_uom => p_entitlement_uom
6648 ,p_contract_wp => l_contract_wp ) ;
6649
6650
6651 l_FT_working_wp := NVL(p_default_wp,l_contract_wp);
6652
6653 l_assignment_wp :=
6654 get_assignment_work_pattern (
6655 p_business_group_id => p_business_group_id
6656 ,p_assignment_id => p_assignment_id
6657 ,p_effective_date => p_effective_date
6658 ,p_default_wp => p_default_wp
6659 ,p_contract_wp => l_contract_wp
6660 ,p_is_assignment_wp => p_is_assignment_wp
6661 );
6662
6663 l_assignment_wp_dpw :=
6664 get_average_days_per_week(
6665 p_business_group_id => p_business_group_id
6666 ,p_effective_date => p_effective_date
6667 ,p_work_pattern => l_assignment_wp );
6668
6669 l_FT_absence_wp_dpw :=
6670 get_average_days_per_week(
6671 p_business_group_id => p_business_group_id
6672 ,p_effective_date => p_effective_date
6673 ,p_work_pattern => l_FT_absence_wp );
6674
6675 l_FT_working_dpw :=
6676 get_average_days_per_week(
6677 p_business_group_id => p_business_group_id
6678 ,p_effective_date => p_effective_date
6679 ,p_work_pattern => l_FT_working_wp );
6680
6681
6682 IF g_debug THEN
6683 debug('l_assignment_wp_dpw:'||l_assignment_wp_dpw);
6684 debug('l_FT_absence_wp_dpw:'||l_FT_absence_wp_dpw);
6685 debug('l_FT_working_dpw:'||l_FT_working_dpw);
6686
6687 END IF ;
6688 -- END IF ; -- IF l_plan_information.absence_days_type IN ('C','W') THEN
6689
6690
6691
6692 -- decide whether a FT or PT
6693 IF l_assignment_wp_dpw >= l_FT_working_dpw THEN
6694 p_is_full_timer := TRUE ;
6695 -- IF the employee is FT then the work pattern shud be
6696 l_FT_working_dpw := l_FT_absence_wp_dpw ;
6697 l_assignment_wp_dpw := l_FT_absence_wp_dpw ;
6698 END IF ;
6699
6700 IF NVL(p_track_part_timers,'N') = 'Y' THEN
6701
6702 IF p_entitlement_UOM = 'H'--ours
6703 THEN
6704 l_current_factor := l_fte_value ;
6705 l_ft_factor := 1 ;
6706 ELSE
6707 l_current_factor := l_assignment_wp_dpw ;
6708 l_ft_factor := l_FT_absence_wp_dpw ;
6709 l_fte_value:=l_current_factor/l_ft_factor;
6710
6711 END IF; -- IF p_entitlement_UOM = 'H'ours
6712
6713 ELSE -- i.e. not tracking Part Timers
6714
6715 IF p_entitlement_UOM = 'H'--ours
6716 THEN
6717 l_current_factor := 1.0 ;
6718 l_ft_factor := 1/l_fte_value ;
6719 ELSE
6720 l_current_factor := 1.0 ;
6721 l_ft_factor := 1.0 ;
6722 END IF; -- IF p_entitlement_UOM = 'H'ours
6723
6724 END IF ; -- tracking part timers check
6725
6726 p_current_factor := l_current_factor ;
6727 p_ft_factor := l_ft_factor ;
6728 p_fte :=l_fte_value;
6729 p_working_days_per_week := l_assignment_wp_dpw ;
6730 p_assignment_wp := l_assignment_wp ;
6731 p_FT_absence_wp := l_FT_absence_wp ;
6732 p_FT_working_wp := l_FT_working_wp ;
6733
6734 IF g_debug THEN
6735 debug('p_current_factor:'||p_current_factor);
6736 debug('p_ft_factor:'||p_ft_factor);
6737 debug_exit(l_proc_name) ;
6738 END IF ;
6739
6740 EXCEPTION
6741 WHEN OTHERS THEN
6742 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
6743 pqp_utilities.debug_others
6744 (l_proc_name
6745 ,l_proc_step
6746 );
6747 IF g_debug THEN
6748 debug('Leaving: '||l_proc_name,-999);
6749 END IF;
6750 fnd_message.raise_error;
6751 ELSE
6752 RAISE;
6753 END IF;
6754 END get_factors ;
6755
6756 PROCEDURE convert_entitlements
6757 ( p_entitlements IN OUT NOCOPY pqp_absval_pkg.t_entitlements
6758 ,p_current_factor IN NUMBER
6759 ,p_ft_factor IN NUMBER
6760 ) IS
6761 l_proc_name VARCHAR2(61) := g_package_name||'convert_entitlements';
6762 l_proc_step NUMBER(20,10) ;
6763 l_entitlements_nc pqp_absval_pkg.t_entitlements ;
6764 i NUMBER ;
6765
6766 BEGIN
6767 g_debug := hr_utility.debug_enabled;
6768
6769 IF g_debug THEN
6770 debug_enter(l_proc_name);
6771 debug('p_current_factor:'||p_current_factor);
6772 debug('p_ft_factor:'||p_ft_factor);
6773 END IF;
6774
6775 i := p_entitlements.FIRST ;
6776
6777 WHILE i IS NOT NULL LOOP
6778 l_proc_step := 10 + i ;
6779 p_entitlements(i).entitlement := (p_entitlements(i).entitlement
6780 * p_current_factor)/p_ft_factor ;
6781
6782 /* IF p_current_factor>=p_ft_factor THEN
6783 p_entitlements(i).entitlement := pqp_utilities.round_value_up_down
6784 (
6785 p_value_to_round => p_entitlements(i).entitlement
6786 ,p_base_value => 0.5
6787 ,p_rounding_type => 'UPPER'
6788 ) ;
6789 ELSE
6790 p_entitlements(i).entitlement := pqp_utilities.round_value_up_down
6791 (
6792 p_value_to_round => p_entitlements(i).entitlement
6793 ,p_base_value => 0.5
6794 ,p_rounding_type => 'LOWER'
6795 ) ;
6796 END IF ;*/
6797
6798
6799
6800 i := p_entitlements.NEXT(i) ;
6801 END LOOP ;
6802
6803 IF g_debug THEN
6804 debug_exit(l_proc_name) ;
6805 END IF ;
6806
6807
6808 EXCEPTION
6809 WHEN OTHERS THEN
6810 p_entitlements := l_entitlements_nc ;
6811 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
6812 pqp_utilities.debug_others
6813 (l_proc_name
6814 ,l_proc_step
6815 );
6816 IF g_debug THEN
6817 debug('Leaving: '||l_proc_name,-999);
6818 END IF;
6819 fnd_message.raise_error;
6820 ELSE
6821 RAISE;
6822 END IF;
6823 END convert_entitlements ;
6824
6825
6826 END pqp_absval_pkg;
[Home] [Help]