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