1 PACKAGE BODY pqp_gb_omp_daily_absences AS
2 /* $Header: pqgbdomp.pkb 120.3.12000000.1 2007/01/16 03:44:14 appldev noship $ */
3 -----------------
4
5 e_novalue EXCEPTION;
6 -----------------
7
8 g_nested_level NUMBER:= 0;
9 g_package_name VARCHAR2(31) := 'pqp_gb_omp_daily_absences.' ;
10 g_pl_id ben_pl_f.pl_typ_id%TYPE;
11 g_plan_information rec_plan_information ;
12 g_debug BOOLEAN ;
13 g_log_duration_summary VARCHAR2(20) := NULL;
14
15 PROCEDURE debug
16 (p_trace_message IN VARCHAR2
17 ,p_trace_location IN NUMBER
18 )
19 IS
20 BEGIN
21 pqp_utilities.debug(p_trace_message,p_trace_location);
22 END debug;
23 --
24 --
25 --
26 PROCEDURE debug
27 (p_trace_number IN NUMBER )
28 IS
29 BEGIN
30 debug(fnd_number.number_to_canonical(p_trace_number));
31 END debug;
32 --
33 --
34 --
35 PROCEDURE debug
36 (p_trace_date IN DATE )
37 IS
38 BEGIN
39 debug(fnd_date.date_to_canonical(p_trace_date));
40 END debug;
41 --
42 --
43 --
44 PROCEDURE debug_enter
45 (p_proc_name IN VARCHAR2
46 ,p_trace_on IN VARCHAR2
47 )
48 IS
49 -- l_trace_options VARCHAR2(200);
50 BEGIN
51 pqp_utilities.debug_enter(p_proc_name,p_trace_on);
52 END debug_enter;
53 --
54 --
55 --
56 PROCEDURE debug_exit
57 (p_proc_name IN VARCHAR2
58 ,p_trace_off IN VARCHAR2
59 )
60 IS
61 BEGIN
62 pqp_utilities.debug_exit(p_proc_name,p_trace_off);
63 END debug_exit;
64
65 --
66 PROCEDURE debug_others
67 (p_proc_name IN VARCHAR2
68 ,p_last_step_number IN NUMBER DEFAULT NULL
69 )
70 IS
71 l_message fnd_new_messages.message_text%TYPE;
72 BEGIN
73 IF g_debug THEN
74 debug(p_proc_name,SQLCODE);
75 debug(SQLERRM);
76 END IF;
77 l_message := p_proc_name||'{'||
78 fnd_number.number_to_canonical(p_last_step_number)||'}: '||
79 SUBSTRB(SQLERRM,1,2000);
80 IF g_debug THEN
81 debug(l_message);
82 END IF;
83 fnd_message.set_name( 'PQP', 'PQP_230661_OSP_DUMMY_MSG' );
84 fnd_message.set_token( 'TOKEN',l_message);
85 END debug_others;
86 --
87
88
89
90
91
92
93
94 ---Gets value from the pl/sql table ff_exec
95
96 PROCEDURE get_param_value (
97 p_output_type IN ff_exec.outputs_t
98 ,p_name IN VARCHAR2
99 ,p_datatype OUT NOCOPY VARCHAR2
100 ,p_value OUT NOCOPY VARCHAR2
101 ,p_error_code OUT NOCOPY NUMBER
102 ,p_message OUT NOCOPY VARCHAR2
103 )
104 IS
105 l_proc_step NUMBER(20,10) ;
106 l_proc_name VARCHAR2(61) := g_package_name||'get_param_value';
107 BEGIN
108 IF g_debug THEN
109 l_proc_step := 10;
110 debug(l_proc_name, 10);END IF;
111 FOR i in 1..p_output_type.count
112 LOOP
113
114 IF p_output_type(i).name=p_name THEN
115 p_datatype := p_output_type(i).datatype;
116 p_value := p_output_type(i).value;
117 END IF;
118
119 END LOOP;
120
121 IF g_debug THEN
122 debug_exit(l_proc_name) ;
123 END IF ;
124
125 EXCEPTION
126 --------
127 WHEN OTHERS THEN
128 debug('No Value',20);
129 p_error_code:=-1;
130 p_message:=SUBSTR(SQLERRM,0,2000);
131 fnd_message.set_name('PQP', 'PQP_230661_OSP_DUMMY_MSG');
132 fnd_message.set_token('TOKEN', p_message);
133 RAISE;
134 debug_exit(l_proc_name) ;
135 END get_param_value ;
136
137
138 -- This Procedure is Called from Legislation Specific Layer. i.e. from
139 -- package pqp_gb_absence_plan_process for a Start Life Event and can be
140 -- Called from update_absence_plan_details.
141 -- The Process in the Procedure can be summarized as
142 -- First check whether the Absence is already processed for the
143 -- Period. If not processed then check the length of service from the
144 -- Element Input Values. Based on Length of service get the eligible
145 -- entitlements. Then calculate the Absences already taken ( entitlements
146 -- used up ). the difference gives the remaining entitlements.
147 -- Then generate_daily_absences process caches the day and its
148 -- work pattern, Pay band and Absence Band in a pl/sql table
149 -- write_daily_absences will insert the data into pqp_gap_daily_absences
150 -- table using the bulk insert method.
151
152 PROCEDURE create_absence_plan_details --create_daily_absences
153 (p_assignment_id IN NUMBER
154 ,p_person_id IN NUMBER
155 ,p_business_group_id IN NUMBER
156 ,p_absence_id IN NUMBER
157 ,p_absence_date_start IN DATE
158 ,p_absence_date_end IN DATE
159 ,p_pl_id IN NUMBER
160 ,p_pl_typ_id IN NUMBER
161 ,p_element_type_id IN NUMBER
162 ,p_create_start_date IN DATE
163 ,p_create_end_date IN DATE
164 ,p_output_type IN ff_exec.outputs_t
165 ,p_error_code OUT NOCOPY NUMBER
166 ,p_message OUT NOCOPY VARCHAR2
167 ) IS
168
169 invalid_length_of_service EXCEPTION;
170
171 l_entitlements pqp_absval_pkg.t_entitlements;
172 l_absences_taken_to_date pqp_absval_pkg.t_entitlements;
173 l_entitlements_remaining pqp_absval_pkg.t_entitlements;
174 l_daily_absences pqp_absval_pkg.t_daily_absences;
175 l_object_version_number pqp_gap_absence_plans.
176 object_version_number%TYPE;
177 l_gap_absence_plan pqp_absval_pkg.csr_gap_absence_plan%ROWTYPE;
178
179 l_plan_information rec_plan_information ;
180
181 l_generate_start_date DATE;
182 l_generate_end_date DATE;
183
184 l_error_code fnd_new_messages.message_number%TYPE := 0 ;
185 l_message VARCHAR2(2500) ;
186 l_length_of_service NUMBER ;
187 l_value VARCHAR2(240) ;
188 l_datatype VARCHAR2(6);
189 l_maternity_ent_table_id NUMBER ;
190 l_abs_ent_uom VARCHAR2(1) ;
191 l_proc_name VARCHAR2(61) := g_package_name||'create_absence_plan_details';
192 l_proc_step NUMBER(20,10);
193 l_update_summary BOOLEAN;
194
195
196 BEGIN
197
198 g_debug := hr_utility.debug_enabled ;
199
200 IF g_debug THEN
201 debug_enter(l_proc_name) ;
202 debug('p_assignment_id:'||p_assignment_id);
203 debug('p_person_id:'||p_person_id);
204 debug('p_business_group_id:'||p_business_group_id);
205 debug('p_absence_id:'||p_absence_id);
206 debug('p_pl_id:'||p_pl_id);
207 debug('p_pl_typ_id:'||p_pl_typ_id);
208 debug('p_element_type_id:'||p_element_type_id);
209 debug('p_start_date:'||p_create_start_date);
210 debug('p_end_date:'||p_create_end_date);
211 debug('p_absence_date_start:'||p_absence_date_start);
212 debug('p_absence_date_end:'||p_absence_date_end);
213 END IF ;
214
215
216 l_generate_start_date := GREATEST(p_create_start_date,p_absence_date_start);
217 l_generate_end_date := LEAST(NVL(p_create_end_date,hr_api.g_eot),p_absence_date_end);
218
219
220 -- If daily absences exist for the given range for this plan then
221 -- don't continue with the create process. Exit without error.
222 -- This would happen in almost every absence, ie when the end
223 -- life event is encountered and assuming that the end hasn't changed
224 -- the batch was run
225
226 IF l_generate_start_date IS NOT NULL
227 AND l_generate_end_date IS NOT NULL -- both are not needed but
228 THEN -- just makes it more robust
229
230 IF g_debug THEN
231 l_proc_step := 10 ;
232 debug(l_proc_name,10);
233 END IF;
234
235 -- Set global switch to toggle the summary table logging
236
237 IF g_log_duration_summary is NULL
238 THEN
239
240 IF g_debug THEN
241 debug(l_proc_name, 12);
242 END IF;
243
244 g_log_duration_summary :=
245 PQP_UTILITIES.pqp_get_config_value
246 ( p_business_group_id => p_business_group_id
247 ,p_legislation_code => 'GB'
248 ,p_column_name => 'PCV_INFORMATION10'
249 ,p_information_category => 'PQP_GB_OSP_OMP_CONFIG'
250 );
251
252 g_log_duration_summary := NVL(g_log_duration_summary,'DISABLE');
253
254 IF g_debug THEN
255 debug('g_log_duration_summary' || g_log_duration_summary);
256 END IF;
257
258 END IF;
259
260
261 -- get the Scheme Details into Record g_plan_information
262 pqp_gb_omp_daily_absences.get_plan_extra_info_n_cache_it(
263 p_pl_id => p_pl_id
264 ,p_plan_information => l_plan_information
265 ,p_pl_typ_id => p_pl_typ_id
266 ,p_error_code => l_error_code
267 ,p_message => l_message ) ;
268 IF g_debug THEN
269 l_proc_step := 15 ;
270 debug(l_proc_name,15);
271 END IF ;
272
273 BEGIN
274
275 get_param_value
276 (p_output_type => p_output_type
277 ,p_name => 'LENGTH_OF_SERVICE'
278 ,p_datatype => l_datatype
279 ,p_value => l_value
280 ,p_error_code => l_error_code
281 ,p_message => l_message
282 );
283
284 l_length_of_service := TO_NUMBER(l_value);
285
286 IF l_length_of_service IS NULL
287 THEN
288 RAISE invalid_length_of_service;
289 END IF;
290
291 EXCEPTION
292 WHEN VALUE_ERROR
293 OR invalid_length_of_service
294 THEN
295 fnd_message.set_name( 'PQP', 'PQP_230012_OSPOMP_INALID_LOS' );
296 -- The message name PQP_230012_OSPOMP_INALID_LOS has missing "V" in INVALID
297 -- this is not corrected as the related OSP code was already arcsed in
298 -- The value of "length of service", TOKEN, passed from the standard rate is invalid.
299 -- Please check that the standard rate has been setup correctly and that atleast
300 -- one formula output is named exactly LENGTH_OF_SERVICE.
301 fnd_message.set_token( 'LOSVALUE', NVL(l_value,'<Null>'));
302 fnd_message.raise_error;
303
304 END;
305
306
307 l_maternity_ent_table_id := l_plan_information.Absence_Entitlement_Parameters;
308
309 IF l_plan_information.absence_entitlement_days_type
310 in ('CD','CW','CM') THEN
311 l_abs_ent_uom := 'C' ;
312 ELSIF l_plan_information.absence_entitlement_days_type = 'WD' THEN
313 l_abs_ent_uom := 'W' ;
314 ELSIF l_plan_information.absence_entitlement_days_type = 'WH' THEN
315 l_abs_ent_uom := 'H' ;
316 END IF ;
317
318 -- l_abs_ent_uom := substr(l_plan_information.absence_entitlement_days_type,1,1);
319
320
321 -- Below procedure returns the Bands and the entitlements stored in
322 -- Entitlement Table ( UDT ) and stores in g_band_info collection.
323
324 IF g_debug THEN
325 l_proc_step := 30 ;
326 debug(l_proc_name,30);
327 END IF ;
328
329 pqp_gb_omp_daily_absences.get_entitlement_info (
330 p_business_group_id => p_business_group_id
331 ,p_effective_date => p_absence_date_start
332 ,p_assignment_id => p_assignment_id
333 ,p_pl_id => p_pl_id
334 ,p_entitlement_tab_id => l_maternity_ent_table_id
335 ,p_absence_id => p_absence_id
336 ,p_absence_ent_uom =>
337 substr(l_plan_information.absence_entitlement_days_type,2,1)
338 ,p_start_date => p_absence_date_start
339 ,p_benefits_length_of_service => l_length_of_service
340 ,p_entitlements => l_entitlements
341 ,p_error_code => l_error_code
342 ,p_message => l_message );
343 IF g_debug THEN
344 l_proc_step := 35 ;
345 debug(l_proc_name,35);
346 END IF ;
347
348 -- gets the Band entitlements used already. In OMP as we consider only
349 -- the current absence, this will return rows only in case of update.
350 -- otherwise there should not be any record existing. All the used
351 -- Bands will be stored in g_band_bal_info collection.
352
353 pqp_gb_omp_daily_absences.get_entitlements_consumed(
354 p_assignment_id => p_assignment_id
355 ,p_business_group_id => p_business_group_id
356 ,p_effective_date => l_generate_start_date -- l_absence_start_date
357 ,p_absence_id => p_absence_id
358 ,p_pl_typ_id => p_pl_typ_id
359 ,p_entitlements => l_entitlements
360 ,p_absences_taken_to_date => l_absences_taken_to_date
361 ,p_lookup_type => 'PQP_GB_OMP_CALENDAR_RULES'
362 ,p_error_code => l_error_code
363 ,p_message => l_message ) ;
364
365 IF g_debug THEN
366 l_proc_step := 40 ;
367 debug(l_proc_name,40);
368 END IF ;
369
370 -- get final balance
371 pqp_gb_omp_daily_absences.get_entitlements_remaining
372 ( p_entitlements => l_entitlements
373 ,p_absences_taken_to_date => l_absences_taken_to_date
374 ,p_entitlement_uom => l_abs_ent_uom
375 ,p_entitlements_remaining => l_entitlements_remaining
376 ,p_error_code => l_error_code
377 ,p_message => l_message );
378
379 IF g_debug THEN
380 l_proc_step := 45 ;
381 debug(l_proc_name,45);
382 END IF ;
383 -- check the available entitlements and process the days and populate
384 -- the pl/sql with the records to be inserted into daily absences table.
385
386 pqp_absval_pkg.generate_daily_absences(
387 p_assignment_id => p_assignment_id
388 ,p_business_group_id => p_business_group_id
389 ,p_absence_attendance_id => p_absence_id
390 ,p_default_work_pattern_name => l_plan_information.default_work_pattern
391 ,p_calendar_user_table_id => l_plan_information.absence_entitlement_holidays
392 ,p_calendar_rules_list => l_plan_information.calendar_rules_list
393 ,p_generate_start_date => l_generate_start_date
394 ,p_generate_end_date => l_generate_end_date
395 ,p_absence_start_date => p_absence_date_start
396 ,p_absence_end_date => p_absence_date_end
397 ,p_entitlement_UOM => l_abs_ent_uom
398 ,p_payment_UOM => l_plan_information.Daily_Rate_Divisor_Type
399 ,p_output_type => p_output_type
400 ,p_entitlements_remaining => l_entitlements_remaining
401 ,p_daily_absences => l_daily_absences
402 ,p_error_code => l_error_code
403 ,p_message => l_message
404 ,p_is_assignment_wp => TRUE
405 ) ;
406 IF g_debug THEN
407 l_proc_step := 50 ;
408 debug(l_proc_name,50);
409 END IF ;
410
411
412 OPEN pqp_absval_pkg.csr_gap_absence_plan(p_absence_id, p_pl_id);
413 FETCH pqp_absval_pkg.csr_gap_absence_plan INTO l_gap_absence_plan;
414 CLOSE pqp_absval_pkg.csr_gap_absence_plan;
415
416 IF g_debug THEN
417 l_proc_step := 50 ;
418 debug(l_proc_name,50);
419 END IF ;
420
421
422 IF l_gap_absence_plan.gap_absence_plan_id IS NULL
423 THEN
424
425 IF g_debug THEN
426 l_proc_step := 55 ;
427 debug(l_proc_name, 55) ;
428 END IF;
429
430 pqp_gap_ins.ins
431 (
432 p_effective_date => p_absence_date_start
433 ,p_assignment_id => p_assignment_id
434 ,p_absence_attendance_id => p_absence_id
435 ,p_pl_id => p_pl_id
436 ,p_last_gap_daily_absence_date => l_daily_absences(l_daily_absences.LAST).absence_date
437 ,p_gap_absence_plan_id => l_gap_absence_plan.gap_absence_plan_id
438 ,p_object_version_number => l_gap_absence_plan.object_version_number
439 );
440 l_update_summary := FALSE ;
441
442 IF g_debug THEN
443 l_proc_step := 60 ;
444 debug(l_proc_name, 60) ;
445 END IF;
446
447 ELSE
448
449 IF g_debug THEN
450 l_proc_step := 65 ;
451 debug(l_proc_name, 65) ;
452 END IF;
453
454 pqp_gap_upd.upd
455 (p_effective_date => p_absence_date_start
456 ,p_gap_absence_plan_id => l_gap_absence_plan.gap_absence_plan_id
457 ,p_object_version_number => l_gap_absence_plan.object_version_number
458 ,p_assignment_id => p_assignment_id
459 ,p_absence_attendance_id => p_absence_id
460 ,p_pl_id => p_pl_id
461 ,p_last_gap_daily_absence_date => l_daily_absences(l_daily_absences.LAST).absence_date
462 );
463
464 l_update_summary := TRUE ;
465
466 IF g_debug THEN
467 l_proc_step := 70 ;
468 debug(l_proc_name, 70) ;
469 END IF;
470
471 END IF; -- IF l_gap_absence_plan.gap_absence_plan_id IS NULL
472
473
474
475 IF g_debug THEN
476 l_proc_step := 75 ;
477 debug(l_proc_name, 75) ;
478 END IF;
479
480 pqp_absval_pkg.write_daily_absences
481 (p_daily_absences => l_daily_absences
482 ,p_gap_absence_plan_id => l_gap_absence_plan.gap_absence_plan_id );
483
484 IF g_debug THEN
485 l_proc_step := 80 ;
486 debug(l_proc_name, 80) ;
487 END IF;
488 -- We feed the summary data for reporting purposes to fill in the
489 -- summary and balance tables
490
491 --Summary Table Changes Feed in to balance table
492 IF g_log_duration_summary = 'ENABLE' THEN
493
494 IF g_debug THEN
495 debug(l_proc_name, 85);
496 END IF;
497 pqp_absval_pkg.write_absence_summary
498 (P_GAP_ABSENCE_PLAN_ID => l_gap_absence_plan.gap_absence_plan_id
499 ,P_ASSIGNMENT_ID => p_assignment_id
500 ,P_ENTITLEMENT_GRANTED => l_entitlements
501 ,P_ENTITLEMENT_USED_TO_DATE => l_absences_taken_to_date
502 ,P_ENTITLEMENT_REMAINING => l_entitlements_remaining
503 ,P_ENTITLEMENT_UOM => l_abs_ent_uom
504 ,p_update => l_update_summary
505 );
506 END IF;
507 --Summary Table Changes
508 IF g_debug THEN
509 l_proc_step := 85 ;
510 debug(l_proc_name, 85) ;
511 END IF;
512
513
514 END IF ; -- l_generate_start_date IS NOT NULL
515
516 IF g_debug THEN
517 debug_exit(l_proc_name) ;
518 END IF;
519
520 EXCEPTION
521 WHEN OTHERS THEN
522 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
523 debug_others
524 (l_proc_name
525 ,l_proc_step
526 );
527 IF g_debug THEN
528 debug('Leaving: '||l_proc_name,-999);
529 END IF;
530 -- p_message := SQLERRM;
531 -- p_error_code := -1;
532 fnd_message.raise_error;
533 ELSE
534 RAISE;
535 END IF;
536
537 END create_absence_plan_details ; --create_daily_absences ;
538
539
540
541
542
543 -- This procedure gets entitlements based on LOS
544 -- from the entitlement Table ( UDT )
545 -- In OMP User can choose how the entitlements will be defined while
546 -- creation of template when the entitlement type is Calendar. It can be
547 -- Days, Weeks or Months.
548 -- If it is either Weeks or Months then in this procedure it will be
549 -- converted into Days and stored in the PL/SQL table
550 PROCEDURE get_entitlement_info
551 (p_business_group_id IN NUMBER
552 ,p_effective_date IN DATE
553 ,p_assignment_id IN NUMBER
554 ,p_pl_id IN NUMBER -- added RR
555 ,p_entitlement_tab_id IN NUMBER
556 ,p_absence_id IN NUMBER
557 ,p_absence_ent_uom IN VARCHAR2
558 ,p_start_date IN DATE
559 ,p_benefits_length_of_service IN NUMBER
560 ,p_entitlements IN OUT NOCOPY pqp_absval_pkg.t_entitlements
561 ,p_error_code OUT NOCOPY NUMBER
562 ,p_message OUT NOCOPY VARCHAR2
563 ) IS
564 l_intend_to_return VARCHAR2(1) ;
565 l_start_date DATE := p_start_date ;
566 l_end_date DATE ;
567 l_entitlements_nc pqp_absval_pkg.t_entitlements := p_entitlements ;
568 l_proc_name VARCHAR2(61) := g_package_name||'get_entitlement_info';
569 l_proc_step NUMBER(20,10);
570 l_is_ent_override BOOLEAN ;
571
572 BEGIN
573 IF g_debug THEN
574 debug_enter(l_proc_name) ;
575 debug('p_business_group_id:'||p_business_group_id);
576 debug('p_effective_date:'||p_effective_date);
577 debug('p_entitlement_tab_id:'||p_entitlement_tab_id);
578 debug('p_absence_id:'||p_absence_id);
579 debug('p_absence_ent_uom:'||p_absence_ent_uom);
580 debug('p_start_date:'||p_start_date);
581 debug('p_benefits_length_of_service:'||p_benefits_length_of_service);
582 END IF ;
583
584 -- Get the Intend to Return Flag from SSP_MATERNITIES Table.
585 -- The Entitlements are based on the above Flag in OMP.
586
587 l_intend_to_return := pqp_gb_osp_functions.pqp_get_ssp_matrnty_details
588 ( p_absence_attendance_id => p_absence_id
589 ,p_col_name => 'INTEND_TO_RETURN_FLAG'
590 ,p_error_code => p_error_code
591 ,p_message => p_message );
592
593 IF g_debug THEN
594 l_proc_step := 10 ;
595 debug(l_proc_name,10);
596 debug('INTEND_TO_RETURN_FLAG:'||l_intend_to_return) ;
597 END IF ;
598
599 IF p_message IS NOT NULL THEN
600 fnd_message.set_name('PQP', 'PQP_230661_OSP_DUMMY_MSG');
601 fnd_message.set_token('TOKEN', p_message);
602 fnd_message.raise_error;
603 END IF;
604
605
606 -- This function gets all the avialable Bands and the
607 -- respective Entitlements
608
609 p_error_code := pqp_gb_osp_functions.get_los_based_entitlements
610 ( p_business_group_id => p_business_group_id
611 ,p_effective_date => p_effective_date
612 ,p_assignment_id => p_assignment_id
613 ,p_pl_id => p_pl_id
614 ,p_absence_pay_plan_class => 'OMP'
615 ,p_entitlement_table_id => p_entitlement_tab_id
616 ,p_benefits_length_of_service => p_benefits_length_of_service
617 ,p_band_entitlements => p_entitlements
618 ,p_error_msg => p_message
619 ,p_omp_intend_to_return_to_work => l_intend_to_return
620 ,p_is_ent_override => l_is_ent_override ) ;
621 -- Added p_is_ent_override added for OMP ent overrides
622
623 IF g_debug THEN
624 l_proc_step := 20 ;
625 debug(l_proc_name,20);
626 END IF ;
627
628 IF p_message IS NOT NULL THEN
629 fnd_message.set_name('PQP', 'PQP_230603_DEF_BAND1');
630 fnd_message.raise_error;
631 END IF;
632
633 FOR i in 1..p_entitlements.COUNT
634 LOOP
635 p_entitlements(i).duration := p_entitlements(i).entitlement ;
636 END LOOP;
637
638 IF g_debug THEN
639 l_proc_step := 30 ;
640 debug(l_proc_name,30);
641 END IF ;
642
643 IF p_absence_ent_uom = 'W' THEN
644
645 IF g_debug THEN
646 l_proc_step := 35 ;
647 debug(l_proc_name,35);
648 END IF ;
649
650 -- If the Entitlements are defined in Weeks then multiply by 7
651 FOR i in 1..p_entitlements.count LOOP
652 p_entitlements(i).entitlement := p_entitlements(i).entitlement * 7 ;
653 p_entitlements(i).duration := p_entitlements(i).duration * 7 ;
654 END LOOP ;
655
656 ELSIF p_absence_ent_uom = 'M' THEN
657
658 IF g_debug THEN
659 l_proc_step := 40 ;
660 debug(l_proc_name,40);
661 END IF ;
662
663 -- If the Entitlements are Defined in Months
664
665 FOR i in 1..p_entitlements.count LOOP
666 -- logic to convert months into days.
667 IF p_entitlements(i).entitlement > 0 THEN
668
669 l_end_date := ADD_MONTHS(l_start_date,p_entitlements(i).entitlement);
670 p_entitlements(i).entitlement :=
671 pqp_gb_osp_functions.pqp_gb_get_calendar_days (
672 -- Returns the No of Days between the Given Dates.
673 p_start_date => l_start_date
674 ,p_end_date => l_end_date ) -1 ;
675
676 l_start_date := l_end_date ;
677
678 END IF ;
679 END LOOP ;
680
681 END IF ;
682 IF g_debug THEN
683 l_proc_step := 50 ;
684 debug(l_proc_name,50);
685 debug_exit(l_proc_name) ;
686 END IF ;
687
688
689 EXCEPTION
690 ---------
691 WHEN OTHERS THEN
692 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
693 debug_others
694 (l_proc_name
695 ,l_proc_step
696 );
697 IF g_debug THEN
698 debug('Leaving: '||l_proc_name,-999);
699 END IF;
700 -- p_message := SQLERRM;
701 -- p_error_code := -1;
702 fnd_message.raise_error;
703 ELSE
704 RAISE;
705 END IF;
706
707 END get_entitlement_info;
708
709
710
711 PROCEDURE get_entitlements_consumed
712 ( p_assignment_id IN NUMBER
713 ,p_business_group_id IN NUMBER
714 ,p_effective_date IN DATE
715 ,p_absence_id IN NUMBER
716 ,p_pl_typ_id IN NUMBER
717 ,p_entitlements IN OUT NOCOPY pqp_absval_pkg.t_entitlements
718 ,p_absences_taken_to_date IN OUT NOCOPY pqp_absval_pkg.t_entitlements
719 ,p_lookup_type IN VARCHAR2
720 ,p_error_code OUT NOCOPY NUMBER
721 ,p_message OUT NOCOPY VARCHAR2 ) IS
722
723 l_get_band_bal csr_get_band_bal%ROWTYPE;
724 l_flag VARCHAR2(1):='N';
725 l_count NUMBER := 0 ;
726 l_absences_taken_to_date_nc pqp_absval_pkg.t_entitlements ;
727 l_proc_name VARCHAR2(61) := g_package_name||'get_entitlements_consumed';
728 l_proc_step NUMBER(20,10) ;
729
730 BEGIN
731 IF g_debug THEN
732 debug_enter(l_proc_name) ;
733 debug('p_assignment_id:'||p_assignment_id);
734 debug('p_business_group_id:'||p_business_group_id);
735 debug('p_effective_date:'||p_effective_date);
736 debug('p_absence_id:'||p_absence_id);
737 debug('p_pl_typ_id:'||p_pl_typ_id);
738 debug('p_lookup_type:'||p_lookup_type );
739 END IF ;
740
741 l_absences_taken_to_date_nc := p_absences_taken_to_date ;
742
743 If p_absences_taken_to_date.count>0 THEN
744 p_absences_taken_to_date.delete;
745 END IF;
746 IF g_debug THEN
747 l_proc_Step := 10 ;
748 debug(l_proc_name,10);
749 END IF;
750
751 -- Cursor Returns Entitlements Band Wise.
752 OPEN csr_get_band_bal(
753 p_assignment_id => p_assignment_id
754 ,p_business_group_id => p_business_group_id
755 ,p_absence_id => p_absence_id
756 ,p_pl_typ_id => p_pl_typ_id
757 ,p_lookup_type => p_lookup_type ) ;
758 LOOP
759 FETCH csr_get_band_bal INTO l_get_band_bal;
760 EXIT WHEN csr_get_band_bal%NOTFOUND;
761 IF g_debug THEN
762 debug('Band :'||l_get_band_bal.level_of_entitlement, 20 );
763 debug('Balance :'||l_get_band_bal.consumed, 30 );
764 END IF ;
765
766 p_absences_taken_to_date(l_count+1).band := l_get_band_bal.level_of_entitlement;
767 p_absences_taken_to_date(l_count+1).entitlement := l_get_band_bal.consumed;
768 p_absences_taken_to_date(l_count+1).duration := l_get_band_bal.consumed;
769 p_absences_taken_to_date(l_count+1).duration_in_hours :=
770 l_get_band_bal.consumed_in_hours ;
771 l_count :=l_count+1;
772
773 END LOOP;
774
775 CLOSE csr_get_band_bal;
776 IF g_debug THEN
777 l_proc_step := 20 ;
778 debug(l_proc_name,20);
779 END IF ;
780
781 IF p_entitlements.count > p_absences_taken_to_date.count and l_count>0
782 THEN
783 -- i.e. When there are few used entitlements.
784 FOR i in 1..p_entitlements.count LOOP
785 IF g_debug THEN
786 debug('Band in Entitlements :'||p_entitlements(i).band);
787 END IF ;
788
789 l_flag :='N';
790
791 FOR j in 1..p_absences_taken_to_date.count
792 LOOP
793 debug('Band in Band Bal is :'||p_absences_taken_to_date(j).band);
794
795 IF p_entitlements(i).band = p_absences_taken_to_date(j).band THEN
796 l_flag :='Y';
797 END IF;
798
799 END LOOP;
800
801
802 IF l_flag='N' THEN
803 p_absences_taken_to_date(p_absences_taken_to_date.count+1).band
804 := p_entitlements(i).band;
805 p_absences_taken_to_date(p_absences_taken_to_date.count+1).entitlement
806 := 0;
807 p_absences_taken_to_date(p_absences_taken_to_date.count+1).duration
808 := 0;
809 p_absences_taken_to_date(
810 p_absences_taken_to_date.COUNT + 1).duration_in_hours:=0;
811
812
813 END IF;
814 END LOOP;
815 END IF;
816 IF g_debug THEN
817 l_proc_step := 30 ;
818 debug(l_proc_name,30);
819 END IF ;
820 -- The below loop is when there are no used entitlements.
821
822 IF l_count= 0 THEN
823 FOR i in 1..p_entitlements.count
824 LOOP
825 p_absences_taken_to_date(i).band := p_entitlements(i).band ;
826 p_absences_taken_to_date(i).entitlement := 0 ;
827 p_absences_taken_to_date(i).duration := 0 ;
828 p_absences_taken_to_date(i).duration_in_hours := 0 ;
829
830 END LOOP;
831 END IF;
832 IF g_debug THEN
833 debug_exit(l_proc_name) ;
834 END IF ;
835
836
837
838 EXCEPTION
839 ---------
840 WHEN OTHERS THEN
841 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
842 debug_others
843 (l_proc_name
844 ,l_proc_step
845 );
846 IF g_debug THEN
847 debug('Leaving: '||l_proc_name,-999);
848 END IF;
849 p_absences_taken_to_Date := l_absences_taken_to_date_nc ;
850 fnd_message.raise_error;
851 ELSE
852 RAISE;
853 END IF;
854
855 END get_entitlements_consumed ;
856
857
858 --This Procedure calculates remaining entitlements
859 PROCEDURE get_entitlements_remaining
860 (p_entitlements IN pqp_absval_pkg.t_entitlements
861 ,p_absences_taken_to_date IN pqp_absval_pkg.t_entitlements
862 ,p_entitlement_UOM IN VARCHAR2
863 ,p_entitlements_remaining IN OUT NOCOPY pqp_absval_pkg.t_entitlements
864 ,p_error_code OUT NOCOPY NUMBER
865 ,p_message OUT NOCOPY VARCHAR2
866 )
867 IS
868 l_band_count NUMBER:=0;
869 l_proc_name VARCHAR2(61) := g_package_name||'get_entitlements_remaining';
870 l_proc_step NUMBER(20,10) ;
871
872 BEGIN
873 IF g_debug THEN
874 debug_enter(l_proc_name);
875 END IF ;
876
877 FOR i in 1..p_entitlements.count
878 LOOP
879 if p_entitlements(i).entitlement = -1 THEN
880 -- p_entitlements(i).duration=-1 then
881 EXIT;
882 ELSE
883 l_band_count:=i;
884 END IF;
885 END LOOP;
886
887 IF g_debug THEN
888 l_proc_step := 10 ;
889 debug(l_proc_name,10);
890 END IF ;
891 ----------
892 FOR i in 1..l_band_count ----p_band_info.count
893 LOOP
894
895 IF g_debug THEN
896 l_proc_step := 20 ;
897 debug(l_proc_name,20);
898 END IF ;
899 --------
900 FOR j in 1..p_absences_taken_to_date.count
901 LOOP
902 --------
903 IF p_absences_taken_to_date(j).band = p_entitlements(i).band THEN
904
905 p_entitlements_remaining(i).band := p_entitlements(i).band;
906
907
908
909 IF p_entitlement_UOM = 'H' THEN
910 IF g_debug THEN
911 l_proc_step := 30 ;
912 debug(l_proc_name,30);
913 END IF ;
914 p_entitlements_remaining(i).entitlement := p_entitlements(i).entitlement
915 -nvl(p_absences_taken_to_date(j).duration_in_hours,0);
916 p_entitlements_remaining(i).duration := p_entitlements(i).duration
917 -nvl(p_absences_taken_to_date(j).duration_in_hours,0);
918 ELSE
919
920 IF g_debug THEN
921 l_proc_step := 40 ;
922 debug(l_proc_name,40);
923 debug('p_entitlements_remaining(i):'||p_entitlements(i).band);
924 debug('p_entitlements_remaining(i):'||p_entitlements(i).entitlement);
925 debug(':');
926 debug('p_absences_taken_to_date(j):'||p_absences_taken_to_date(j).entitlement) ;
927 END IF ;
928
929 p_entitlements_remaining(i).entitlement := p_entitlements(i).entitlement
930 -nvl(p_absences_taken_to_date(j).entitlement,0);
931 p_entitlements_remaining(i).duration := p_entitlements(i).duration
932 -nvl(p_absences_taken_to_date(j).duration,0);
933 END IF ;
934
935 IF g_debug THEN
936 l_proc_step := 50 ;
937 debug(l_proc_name,50);
938 debug('BAND:'||p_entitlements_remaining(i).band);
939 debug('Entitlement:'||p_entitlements_remaining(i).entitlement);
940 END IF ;
941 IF p_entitlements_remaining(i).entitlement < 0 THEN
942
943 p_entitlements_remaining(i).entitlement := 0 ;
944 p_entitlements_remaining(i).duration := 0 ;
945
946 END IF ;
947
948
949 END IF;
950 ---------
951 END LOOP;
952 --------------
953
954 END LOOP;
955 ---------------------
956
957 IF g_debug THEN
958 l_proc_step := 60 ;
959 debug(l_proc_name,60);
960 END IF ;
961
962
963 IF p_entitlements.count=0 THEN
964 RAISE e_novalue;
965 END IF;
966
967 EXCEPTION
968 ---------
969 WHEN e_novalue THEN
970 debug('No Value in the Table' ,30);
971 fnd_message.set_name('PQP', 'PQP_230661_OSP_DUMMY_MSG');
972 fnd_message.set_token('TOKEN', 'No Value in Entitlement Balance Cache');
973 RAISE;
974 WHEN OTHERS THEN
975 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
976 debug_others
977 (l_proc_name
978 ,l_proc_step
979 );
980 IF g_debug THEN
981 debug('Leaving: '||l_proc_name,-999);
982 END IF;
983 -- p_message := SQLERRM;
984 -- p_error_code := -1;
985 fnd_message.raise_error;
986 ELSE
987 RAISE;
988 END IF;
989
990 END get_entitlements_remaining ;
991
992
993 --Get Extra info
994 --This procedure gets all the plan extra information
995 -- in one go and caches the same by plan id. Given that
996 -- we need to call this once for every life event and
997 -- every absence has two life events, it is quite likely
998 -- that the same information will needed repeatedly.
999 -- note the cache stores information of one plan at a time
1000 -- it is not a pl/sql table.
1001 PROCEDURE get_plan_extra_info_n_cache_it (
1002 p_pl_id IN NUMBER
1003 ,p_plan_information IN OUT NOCOPY rec_plan_information
1004 ,p_pl_typ_id IN NUMBER
1005 ,p_error_code OUT NOCOPY NUMBER
1006 ,p_message OUT NOCOPY VARCHAR2
1007 ) IS
1008
1009 l_trunc_yn VARCHAR2(10) ;
1010 l_ret_val NUMBER ;
1011 l_proc_name VARCHAR2(61) := g_package_name||'get_plan_extra_info_n_cache_it' ;
1012 l_proc_step NUMBER(20,10);
1013
1014 BEGIN
1015
1016 IF g_debug THEN
1017 debug_enter(l_proc_name) ;
1018 debug('Caching check:g_pl_id:'||fnd_number.number_to_canonical(g_pl_id));
1019 debug('Caching check:p_pl_id:'||fnd_number.number_to_canonical(g_pl_id));
1020 END IF ;
1021
1022 IF g_pl_id IS NULL OR p_pl_id<>g_pl_id THEN
1023
1024 IF g_debug THEN
1025 l_proc_step := 10 ;
1026 debug(l_proc_name,10);
1027 debug('Before :' || p_plan_information.Absence_Entitlement_Days_Type);
1028 END IF ;
1029
1030 l_ret_val := pqp_gb_osp_functions.pqp_get_plan_extra_info
1031 ( p_pl_id => p_pl_id,
1032 p_information_type => 'PQP_GB_OMP_ABSENCE_PLAN_INFO',
1033 p_segment_name => 'Absence Entitlement Days Type',
1034 p_value => p_plan_information.Absence_Entitlement_Days_Type,
1035 p_truncated_yes_no => l_trunc_yn,
1036 p_error_msg => p_message
1037 );
1038
1039 IF g_debug THEN
1040 l_proc_step := 15 ;
1041 debug(l_proc_name,15);
1042 debug('After :'||p_plan_information.Absence_Entitlement_Days_Type);
1043 END IF ;
1044
1045 IF g_debug THEN
1046 l_proc_step := 20 ;
1047 debug(l_proc_name,20);
1048 debug('Before :'||p_plan_information.Absence_Entitlement_Parameters);
1049 END IF ;
1050
1051 l_ret_val := pqp_gb_osp_functions.pqp_get_plan_extra_info
1052 ( p_pl_id => p_pl_id,
1053 p_information_type => 'PQP_GB_OMP_ABSENCE_PLAN_INFO',
1054 p_segment_name => 'Absence Entitlement Parameters',
1055 p_value => p_plan_information.Absence_Entitlement_Parameters,
1056 p_truncated_yes_no => l_trunc_yn,
1057 p_error_msg => p_message
1058 );
1059 IF g_debug THEN
1060 l_proc_step := 25 ;
1061 debug(l_proc_name,25);
1062 debug('After :'||p_plan_information.Absence_Entitlement_Parameters);
1063 END IF ;
1064
1065 IF g_debug THEN
1066 l_proc_step := 30 ;
1067 debug(l_proc_name,30);
1068 debug('Before :'||p_plan_information.Absence_Entitlement_Holidays);
1069
1070 END IF ;
1071
1072 l_ret_val := pqp_gb_osp_functions.pqp_get_plan_extra_info
1073 ( p_pl_id => p_pl_id,
1074 p_information_type => 'PQP_GB_OMP_ABSENCE_PLAN_INFO',
1075 p_segment_name => 'Absence Entitlement Holidays',
1076 p_value => p_plan_information.Absence_Entitlement_Holidays,
1077 p_truncated_yes_no => l_trunc_yn,
1078 p_error_msg => p_message
1079 );
1080 IF g_debug THEN
1081 l_proc_step := 35 ;
1082 debug(l_proc_name,35);
1083 debug('After :'||p_plan_information.Absence_Entitlement_Holidays);
1084 END IF ;
1085
1086
1087 IF g_debug THEN
1088 l_proc_step := 40 ;
1089 debug(l_proc_name,40);
1090 debug('Before Daily Rate Divisor Type:'||p_plan_information.Daily_Rate_Divisor_Type);
1091 END IF ;
1092
1093 l_ret_val := pqp_gb_osp_functions.pqp_get_plan_extra_info
1094 ( p_pl_id => p_pl_id,
1095 p_information_type => 'PQP_GB_OMP_ABSENCE_PLAN_INFO',
1096 p_segment_name => 'Daily Rate Divisor Type',
1097 p_value => p_plan_information.Daily_Rate_Divisor_Type,
1098 p_truncated_yes_no => l_trunc_yn,
1099 p_error_msg => p_message
1100 );
1101 IF g_debug THEN
1102 l_proc_step := 45 ;
1103 debug(l_proc_name,45);
1104 debug('After Daily Rate Divisor Type:'||p_plan_information.Daily_Rate_Divisor_Type);
1105 END IF ;
1106
1107 IF g_debug THEN
1108 l_proc_step := 50 ;
1109 debug(l_proc_name,50);
1110 debug('Before Plan Name:'||p_plan_information.plan_name);
1111 END IF ;
1112
1113 l_ret_val := pqp_gb_osp_functions.pqp_get_plan_extra_info
1114 ( p_pl_id => p_pl_id,
1115 p_information_type => 'PQP_GB_OMP_ABSENCE_PLAN_INFO',
1116 p_segment_name => 'Plan Name',
1117 p_value => p_plan_information.plan_name,
1118 p_truncated_yes_no => l_trunc_yn,
1119 p_error_msg => p_message
1120 );
1121 IF g_debug THEN
1122 l_proc_step := 55 ;
1123 debug(l_proc_name,55);
1124 debug('After Plan Name:'||p_plan_information.plan_name);
1125 END IF ;
1126
1127
1128 IF g_debug THEN
1129 l_proc_step := 60 ;
1130 debug(l_proc_name,60);
1131 debug('Before Daily Rate Divisor Duration:'||
1132 p_plan_information.Daily_Rate_Divisor_Duration);
1133 END IF ;
1134
1135 l_ret_val := pqp_gb_osp_functions.pqp_get_plan_extra_info
1136 ( p_pl_id => p_pl_id,
1137 p_information_type => 'PQP_GB_OMP_ABSENCE_PLAN_INFO',
1138 p_segment_name => 'Daily Rate Divisor Duration',
1139 p_value => p_plan_information.Daily_Rate_Divisor_Duration,
1140 p_truncated_yes_no => l_trunc_yn,
1141 p_error_msg => p_message
1142 );
1143 IF g_debug THEN
1144 l_proc_step := 65 ;
1145 debug(l_proc_name,65);
1146 debug('After Daily Rate Divisor Duration:'||
1147 p_plan_information.Daily_Rate_Divisor_Duration);
1148 END IF ;
1149
1150
1151 IF g_debug THEN
1152 l_proc_step := 70 ;
1153 debug(l_proc_name,70);
1154 debug('Before Default Work Pattern :'||
1155 p_plan_information.Default_Work_Pattern );
1156 END IF ;
1157
1158 l_ret_val := pqp_gb_osp_functions.pqp_get_plan_extra_info
1159 ( p_pl_id => p_pl_id,
1160 p_information_type => 'PQP_GB_OMP_ABSENCE_PLAN_INFO',
1161 p_segment_name => 'Default Work Pattern',
1162 p_value => p_plan_information.Default_Work_Pattern,
1163 p_truncated_yes_no => l_trunc_yn,
1164 p_error_msg => p_message
1165 );
1166 IF g_debug THEN
1167 l_proc_step := 75 ;
1168 debug(l_proc_name,75);
1169 debug('After Default Work Pattern :'||
1170 p_plan_information.Default_Work_Pattern );
1171 END IF ;
1172
1173 IF g_debug THEN
1174 l_proc_step := 80 ;
1175 debug(l_proc_name,80);
1176 debug('Before Absence Entitlement Cal Rules :'||
1177 p_plan_information.calendar_rules_list );
1178 END IF ;
1179
1180 l_ret_val := pqp_gb_osp_functions.pqp_get_plan_extra_info
1181 ( p_pl_id => p_pl_id,
1182 p_information_type => 'PQP_GB_OMP_ABSENCE_PLAN_INFO',
1183 p_segment_name => 'Absence Entitlement Cal Rules',
1184 p_value => p_plan_information.calendar_rules_list,
1185 p_truncated_yes_no => l_trunc_yn,
1186 p_error_msg => p_message
1187 );
1188
1189 g_pl_id:=p_pl_id;
1190 g_plan_information := p_plan_information;
1191
1192 ELSE
1193 IF g_debug THEN
1194 l_proc_step := 85 ;
1195 debug(l_proc_name,85 );
1196 END IF ;
1197 p_plan_information := g_plan_information ;
1198 END IF;
1199
1200 IF p_message IS NOT NULL THEN
1201 RAISE e_novalue;
1202 END IF;
1203 debug_exit(l_proc_name) ;
1204 EXCEPTION
1205 --------
1206 WHEN e_novalue THEN
1207 p_error_code:=-1;
1208 fnd_message.set_name('PQP', 'PQP_230661_OSP_DUMMY_MSG');
1209 fnd_message.set_token('TOKEN', 'No Value in Plan Extra Info');
1210 debug_exit(l_proc_name) ;
1211 RAISE;
1212 WHEN OTHERS THEN
1213 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
1214 debug_others
1215 (l_proc_name
1216 ,l_proc_step
1217 );
1218 IF g_debug THEN
1219 debug('Leaving: '||l_proc_name,-999);
1220 END IF;
1221 -- p_message := SQLERRM;
1222 -- p_error_code := -1;
1223 fnd_message.raise_error;
1224 ELSE
1225 RAISE;
1226 END IF;
1227 END get_plan_extra_info_n_cache_it ;
1228
1229
1230
1231 PROCEDURE update_absence_plan_details
1232 (p_assignment_id IN NUMBER
1233 ,p_person_id IN NUMBER
1234 ,p_business_group_id IN NUMBER
1235 ,p_absence_id IN NUMBER
1236 ,p_absence_date_start IN DATE
1237 ,p_absence_date_end IN DATE
1238 ,p_pl_id IN NUMBER
1239 ,p_pl_typ_id IN NUMBER
1240 ,p_element_type_id IN NUMBER --
1241 ,p_update_start_date IN DATE --
1242 ,p_update_end_date IN DATE --
1243 ,p_output_type IN ff_exec.outputs_t
1244 ,p_error_code OUT NOCOPY NUMBER
1245 ,p_message OUT NOCOPY VARCHAR2
1246 )
1247 IS
1248
1249 l_absence_end_date DATE;
1250 l_gap_absence_plan pqp_absval_pkg.csr_gap_absence_plan%ROWTYPE;
1251 l_first_entitled_day_of_noband pqp_absval_pkg.csr_first_entitled_day_of_band%ROWTYPE;
1252 l_error_code fnd_new_messages.message_number%TYPE:=0;
1253 l_error_message fnd_new_messages.message_text%TYPE;
1254
1255 l_proc_name VARCHAR2(61):=
1256 g_package_name||
1257 'update_absence_plan_details';
1258 l_proc_step NUMBER(20,10) ;
1259
1260 BEGIN
1261
1262 g_debug := hr_utility.debug_enabled;
1263
1264 IF g_debug THEN
1265 debug(l_proc_name,10);
1266 debug(p_assignment_id );
1267 debug(p_person_id );
1268 debug(p_business_group_id );
1269 debug(p_absence_id );
1270 debug( p_absence_date_start);
1271 debug(p_absence_date_end );
1272 debug(p_pl_id );
1273 debug(p_pl_typ_id );
1274 debug(p_element_type_id );
1275 debug( p_update_start_date);
1276 debug(p_update_end_date );
1277 END IF ;
1278
1279 OPEN pqp_absval_pkg.csr_gap_absence_plan(p_absence_id, p_pl_id);
1280 FETCH pqp_absval_pkg.csr_gap_absence_plan INTO l_gap_absence_plan;
1281 CLOSE pqp_absval_pkg.csr_gap_absence_plan;
1282
1283 IF g_debug THEN
1284 l_proc_step := 10 ;
1285 debug(l_proc_name,10);
1286 END IF ;
1287
1288 IF p_absence_date_end = hr_api.g_eot -- if its an open ended absence
1289 THEN
1290 IF g_debug THEN
1291 l_proc_step := 15 ;
1292 debug(l_proc_name,15);
1293 END IF ;
1294
1295 OPEN pqp_absval_pkg.csr_first_entitled_day_of_band
1296 (l_gap_absence_plan.gap_absence_plan_id
1297 ,'NOBAND'
1298 );
1299
1300 FETCH pqp_absval_pkg.csr_first_entitled_day_of_band INTO l_first_entitled_day_of_noband;
1301
1302 IF pqp_absval_pkg.csr_first_entitled_day_of_band%FOUND
1303 THEN
1304 IF g_debug THEN
1305 l_proc_step := 20 ;
1306 debug(l_proc_name,20);
1307 END IF ;
1308
1309 IF l_first_entitled_day_of_noband.absence_date + 365 --(or 366 bug)
1310 = l_gap_absence_plan.last_gap_daily_absence_date
1311 THEN
1312 -- its an update call for an open ended absence.
1313 -- and needs no action as it has allready been generated to the
1314 -- maximum extent possible for an open ended absence.
1315 -- hence set the absence end date = last gap daily abs date
1316 l_absence_end_date := l_gap_absence_plan.last_gap_daily_absence_date;
1317
1318 END IF; -- IF l_first_entitled_day_of_noband.absence_date + 365
1319
1320 END IF; -- IF csr_first_entitled_day_of_band%FOUND THEN
1321
1322 CLOSE pqp_absval_pkg.csr_first_entitled_day_of_band;
1323
1324 IF g_debug THEN
1325 l_proc_step := 25 ;
1326 debug(l_proc_name,25);
1327 END IF ;
1328
1329 ELSE
1330
1331 l_absence_end_date := p_absence_date_end;
1332
1333 END IF; -- IF l_absence_end_date = hr_api.g_eot
1334
1335 IF g_debug THEN
1336 l_proc_step := 30 ;
1337 debug(l_proc_name,30);
1338 debug(l_absence_end_date);
1339 debug(l_gap_absence_plan.last_gap_daily_absence_date);
1340 END IF ;
1341
1342 IF l_absence_end_date > l_gap_absence_plan.last_gap_daily_absence_date
1343 THEN
1344
1345 IF g_debug THEN
1346 l_proc_step := 35 ;
1347 debug(l_proc_name,35);
1348 END IF ;
1349
1350
1351 create_absence_plan_details --create_daily_absences
1352 (p_assignment_id => p_assignment_id
1353 ,p_person_id => p_person_id
1354 ,p_business_group_id => p_business_group_id
1355 ,p_absence_id => p_absence_id
1356 ,p_absence_date_start => p_absence_date_start
1357 ,p_absence_date_end => p_absence_date_end
1358 ,p_pl_id => p_pl_id
1359 ,p_pl_typ_id => p_pl_typ_id
1360 ,p_element_type_id => p_element_type_id
1361 ,p_create_start_date => l_gap_absence_plan.last_gap_daily_absence_date+1
1362 ,p_create_end_date => l_absence_end_date
1363 ,p_output_type => p_output_type
1364 ,p_error_code => l_error_code
1365 ,p_message => l_error_message
1366 );
1367
1368 IF g_debug THEN
1369 l_proc_step := 40 ;
1370 debug(l_proc_name,40);
1371 END IF ;
1372
1373
1374
1375 ELSIF l_absence_end_date < l_gap_absence_plan.last_gap_daily_absence_date
1376 THEN
1377
1378 IF g_debug THEN
1379 l_proc_step := 45 ;
1380 debug(l_proc_name,45);
1381 END IF ;
1382
1383
1384 pqp_absval_pkg.delete_absence_plan_details
1385 (p_assignment_id => p_assignment_id
1386 ,p_business_group_id => p_business_group_id
1387 ,p_plan_id => p_pl_id
1388 ,p_absence_id => p_absence_id
1389 ,p_delete_start_date => l_absence_end_date+1
1390 ,p_delete_end_date => l_gap_absence_plan.last_gap_daily_absence_date
1391 ,p_error_code => l_error_code
1392 ,p_message => l_error_message
1393 );
1394
1395 IF g_debug THEN
1396 l_proc_step := 50 ;
1397 debug(l_proc_name,50);
1398 END IF ;
1399
1400
1401
1402 ELSE -- l_absence_end_date = l_gap_absence_plan.last_gap_daily_absence_date
1403 -- no action required -- information only step.
1404 -- IF g_debug THEN
1405 -- debug(l_proc_name, 55);
1406 NULL; -- no action required
1407 -- END IF;
1408
1409 END IF; -- IF l_absence_end_date > last_gap_daily_absence_date
1410
1411
1412 EXCEPTION
1413 WHEN OTHERS THEN
1414 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
1415 debug_others
1416 (l_proc_name
1417 ,l_proc_step
1418 );
1419 IF g_debug THEN
1420 debug('Leaving: '||l_proc_name,-999);
1421 END IF;
1422 fnd_message.raise_error;
1423 ELSE
1424 RAISE;
1425 END IF;
1426
1427 END update_absence_plan_details ;
1428
1429
1430 -- This Procedure returns the Bands and the respective entitlements
1431 -- The Bands and the entitlements are stored in pl/sql table
1432 -- Called from Fast Formula
1433
1434 PROCEDURE get_entitlement_balance (
1435 p_assignment_id IN NUMBER
1436 ,p_business_group_id IN NUMBER
1437 ,p_pl_typ_id IN NUMBER
1438 ,p_effective_date IN DATE
1439 ,p_absences_taken_to_date IN OUT NOCOPY pqp_absval_pkg.t_entitlements
1440 ,p_error_code OUT NOCOPY NUMBER
1441 ,p_message OUT NOCOPY VARCHAR2
1442 )
1443 IS
1444
1445 -- Cursor to get the Used up Bands and the number of Days
1446 CURSOR c_get_band_bal( p_assignment_id NUMBER
1447 ,p_effective_date DATE
1448 ,p_business_group_id NUMBER
1449 ,p_pl_typ_id NUMBER ) IS
1450 SELECT pgda.level_of_entitlement
1451 ,SUM(pgda.duration) days
1452 ,SUM(pgda.duration_in_hours) hours
1453 FROM pqp_gap_daily_absences pgda
1454 ,pqp_gap_absence_plans pgap
1455 ,per_absence_attendances paa
1456 ,ben_pl_f bpf
1457 WHERE pgda.gap_absence_plan_id = pgap.gap_absence_plan_id
1458 AND pgap.absence_attendance_id = paa.absence_attendance_id
1459 AND pgda.absence_date <= p_effective_date
1460 AND pgap.assignment_id = p_assignment_id
1461 AND paa.business_group_id = p_business_group_id
1462 AND paa.business_group_id = bpf.business_group_id
1463 AND bpf.pl_id = pgap.pl_id
1464 AND bpf.pl_typ_id = p_pl_typ_id
1465 GROUP BY level_of_entitlement
1466 ORDER BY level_of_entitlement;
1467
1468
1469 l_flag varchar2(1):='N';
1470 l_get_band_bal c_get_band_bal%ROWTYPE;
1471 l_count number;
1472 l_prev_date DATE;
1473 l_proc_name VARCHAR2(61) := g_package_name||'get_entitlement_balance';
1474 l_proc_step NUMBER(20,10);
1475
1476 BEGIN
1477
1478 IF g_debug THEN
1479 debug_enter(l_proc_name);
1480 debug('p_assignment_id:'||p_assignment_id,1);
1481 debug('p_business_group_id:'||p_business_group_id,2);
1482 debug('p_pl_typ_id:'||p_pl_typ_id,3);
1483 debug('p_effective_date:'||p_effective_date,4);
1484 END IF ;
1485
1486 l_count:=0;
1487
1488 IF g_debug THEN
1489 l_proc_step := 10 ;
1490 debug(l_proc_name,10) ;
1491 END IF ;
1492
1493 OPEN c_get_band_bal( p_assignment_id => p_assignment_id
1494 ,p_effective_date => p_effective_date
1495 ,p_business_group_id => p_business_group_id
1496 ,p_pl_typ_id => p_pl_typ_id);
1497 LOOP
1498 FETCH c_get_band_bal INTO l_get_band_bal;
1499 EXIT WHEN c_get_band_bal%NOTFOUND;
1500 p_absences_taken_to_date(l_count+1).band := l_get_band_bal.level_of_entitlement;
1501 p_absences_taken_to_date(l_count+1).entitlement := l_get_band_bal.days;
1502 p_absences_taken_to_date(l_count+1).duration := l_get_band_bal.days;
1503 p_absences_taken_to_date(l_count+1).duration_in_hours := l_get_band_bal.hours;
1504
1505 IF g_debug THEN
1506 l_proc_step := 20 ;
1507 debug(l_proc_name,20);
1508 debug(' Band:'||l_get_band_bal.level_of_entitlement);
1509 debug(' Days Duration:'||l_get_band_bal.days);
1510 debug(' Hours Duration:'||l_get_band_bal.hours);
1511 END IF ;
1512
1513 l_count :=l_count+1;
1514 END LOOP;
1515 CLOSE c_get_band_bal;
1516
1517 IF g_debug THEN
1518 debug_exit(l_proc_name);
1519 END IF ;
1520
1521
1522 EXCEPTION
1523 ---------
1524 WHEN OTHERS THEN
1525 IF SQLCODE <> hr_utility.HR_ERROR_NUMBER THEN
1526 debug_others
1527 (l_proc_name
1528 ,l_proc_step
1529 );
1530 IF g_debug THEN
1531 debug('Leaving: '||l_proc_name,-999);
1532 END IF;
1533 fnd_message.raise_error;
1534 ELSE
1535 RAISE;
1536 END IF;
1537
1538 END get_entitlement_balance;
1539
1540
1541 END pqp_gb_omp_daily_absences ;
[Home] [Help]