[Home] [Help]
PACKAGE BODY: APPS.PAY_US_PTO_CO_PKG
Source
1 package body pay_us_pto_co_pkg as
2 /* $Header: pyuspaco.pkb 120.1.12010000.2 2008/08/06 08:35:10 ubhat ship $ */
3
4 --
5 -- Package (private) constants.
6 --
7 LOGGING CONSTANT pay_action_parameters.parameter_name%TYPE := 'LOGGING';
8 MAX_ERRORS CONSTANT pay_action_parameters.parameter_name%TYPE := 'MAX_ERRORS_ALLOWED';
9 NEWLINE CONSTANT VARCHAR2(10) := fnd_global.newline;
10 TAB CONSTANT VARCHAR2(30) := fnd_global.tab;
11
12 --
13 -- Package variables.
14 --
15 g_debug BOOLEAN := hr_utility.debug_enabled;
16 g_conc_req_debug BOOLEAN;
17 g_max_errors NUMBER;
18 g_plan_status NUMBER;
19
20 PROCEDURE initialize_logging
21 (p_action_parameter_group_id IN NUMBER)
22 IS
23
24 --
25 -- Gets an action parameter value.
26 --
27 CURSOR csr_get_action_param
28 (p_parameter_name IN VARCHAR2) IS
29 SELECT pap.parameter_value
30 FROM pay_action_parameters pap
31 WHERE pap.parameter_name = p_parameter_name;
32
33 l_logging pay_action_parameters.parameter_value%TYPE;
34 l_max_errors pay_action_parameters.parameter_value%TYPE;
35 l_string VARCHAR2(500);
36
37 BEGIN
38
39 --
40 -- Reset the package globals.
41 --
42 g_errbuf := NULL;
43 g_retcode := SUCCESS;
44 g_conc_req_debug := NULL;
45 g_max_errors := 0;
46
47 --
48 -- If the action parameter ID is passed in, the action param group
49 -- is set. Native dynamic PL/SQL is used to eliminate the
50 -- the dependency on the pay package procedure.
51 --
52 IF p_action_parameter_group_id IS NOT NULL THEN
53
54 l_string :=
55 'BEGIN
56 pay_core_utils.set_pap_group_id(p_pap_group_id => ' ||
57 to_char(p_action_parameter_group_id) || ');
58 END;';
59
60 EXECUTE IMMEDIATE l_string;
61
62 END IF;
63
64 --
65 -- Fetch the action parameter values.
66 --
67 OPEN csr_get_action_param (LOGGING);
68 FETCH csr_get_action_param INTO l_logging;
69 CLOSE csr_get_action_param;
70
71 --
72 -- If logging is set to General, enable debugging.
73 --
74 IF instr(NVL(l_logging, 'N'), 'G') <> 0 THEN
75 g_conc_req_debug := TRUE;
76 END IF;
77
78 IF g_conc_req_debug IS NULL THEN
79 g_conc_req_debug := FALSE;
80 END IF;
81
82 --
83 -- Set the max number of errors allowed.
84 --
85 OPEN csr_get_action_param (MAX_ERRORS);
86 FETCH csr_get_action_param INTO l_max_errors;
87 CLOSE csr_get_action_param;
88
89 g_max_errors := NVL(to_number(l_max_errors), 0);
90
91 END initialize_logging;
92
93 PROCEDURE write_log
94 (p_text IN VARCHAR2
95 ,p_type IN NUMBER)
96 IS
97
98 BEGIN
99
100 --
101 -- Output the PYUPIP.
102 --
103 IF g_debug THEN
104 hr_utility.trace(p_text);
105 END IF;
106
107 --
108 -- Exit immediately if processing a debug line and SRS debugging is disabled.
109 --
110 IF p_type = DEBUG AND NOT g_conc_req_debug THEN
111 RETURN;
112 END IF;
113
114 --
115 -- Write to the concurrent request log.
116 --
117 fnd_file.put_line(FND_FILE.log, p_text);
118
119 END write_log;
120
121 PROCEDURE carry_over (ERRBUF OUT NOCOPY varchar2,
122 RETCODE OUT NOCOPY number,
123 p_calculation_date varchar2,
124 p_business_group_id number,
125 p_plan_id number,
126 p_plan_category varchar2,
127 p_mode varchar2,
128 p_accrual_term varchar2,
129 p_action_parameter_group_id number)
130 IS
131
132 CURSOR csr_accrual_plan (P_category varchar2,
133 P_date date ) is
134 select pap.accrual_plan_id accrual_plan_id,
135 pap.accrual_plan_name accrual_plan_name,
136 pap.co_formula_id co_formula_id,
137 pap.accrual_plan_element_type_id accrual_plan_element_type_id,
138 pap.co_input_value_id co_input_value_id,
139 pap.residual_input_value_id residual_input_value_id,
140 pap.co_date_input_value_id co_date_input_value_id,
141 pap.co_exp_date_input_value_id co_exp_date_input_value_id,
142 pap.residual_date_input_value_id residual_date_input_value_id,
143 piv1.element_type_id co_element_type_id,
144 piv2.element_type_id residual_element_type_id
145 from pay_accrual_plans pap,
146 pay_input_values_f piv1,
147 pay_input_values_f piv2
148 where (pap.accrual_plan_id = nvl(p_plan_id, -1) OR
149 pap.accrual_category like p_category)
150 and pap.business_group_id = p_business_group_id
151 and piv1.input_value_id = pap.CO_INPUT_VALUE_ID
152 and P_date between piv1.effective_start_date and
153 piv1.effective_end_date
154 and piv2.input_value_id = pap.RESIDUAL_INPUT_VALUE_ID
155 and P_date between piv2.effective_start_date and
156 piv2.effective_end_date;
157 --
158 -- Local variables
159 --
160
161 l_proc varchar2(80) := 'pay_us_pto_co_pkg.carry_over';
162 l_plan_category varchar2(30) := p_plan_category;
163 l_count number := 0;
164 l_message_count number := 0;
165 l_message varchar2(256);
166 l_session_date date;
167 l_calculation_date date;
168
169 -- Bug no 2932073 And 2878657
170 -- added local Variable to hold the ligislation code.
171
172 l_legislation_code hr_organization_information.org_information9%TYPE;
173
174 -- Cursor for selecting the Legislation code
175
176 cursor c_legislation_code (p_bg_id number) is
177 select hoi.org_information9
178 from hr_all_organization_units org
179 , hr_organization_information hoi
180 where hoi.organization_id = org.business_group_id
181 and hoi.org_information_context = 'Business Group Information'
182 and org.business_group_id = p_bg_id;
183
184 /*End 2878657 And 2932073*/
185
186
187 BEGIN
188
189 initialize_logging
190 (p_action_parameter_group_id => p_action_parameter_group_id);
191 --
192 -- Fix for bug 3434710 starts here.
193 -- As this procedure is called from concurrent request, a row in fnd_session
194 -- should be inserted sothat the fastformula databse item SQL's which are having joins
195 -- to fnd_sessions would not fail.
196 --
197 dt_fndate.set_effective_date
198 (p_effective_date => trunc(sysdate)
199 ,p_do_commit => true
200 ) ;
201 --
202 -- Fix for bug 3434710 ends here.
203 --
204 -- Pipe the parameters for ease of debugging.
205 --
206 write_log(' ', DEBUG);
207 write_log(' --------------------------------'||
208 '---------------------------------', DEBUG);
209 write_log(' ENTERING '||upper(l_proc), DEBUG);
210 write_log(' --------------------------------'||
211 '+--------------------------------', DEBUG);
212 write_log(' p_calculation_date '||
213 p_calculation_date, DEBUG);
214 write_log(' p_business_group_id '||
215 to_char(p_business_group_id), DEBUG);
216 write_log(' p_plan_id '||
217 to_char(p_plan_id), DEBUG);
218 write_log(' p_plan_category '||
219 p_plan_category, DEBUG);
220 write_log(' p_mode '||
221 p_mode, DEBUG);
222 write_log(' p_accrual_term '||
223 p_accrual_term, DEBUG);
224 write_log(' --------------------------------'||
225 '---------------------------------', DEBUG);
226 write_log(' ', DEBUG);
227
228 per_accrual_message_pkg.clear_table;
229
230 begin
231 select effective_date into l_session_date
232 from fnd_sessions
233 where session_id = userenv('sessionid');
234 exception
235 when others then
236 select sysdate into l_session_date
237 from dual;
238 end;
239
240 write_log('l_session_date: '||to_char(l_session_date), DEBUG);
241
242 l_calculation_date := trunc(fnd_date.canonical_to_date(p_calculation_date));
243
244 write_log('l_calculation_date: '||to_char(l_calculation_date), DEBUG);
245 --
246 -- If no plan parameters are passed, process all plans.
247 -- If a specific plan name is passed, ignore the
248 -- accrual category parameter
249 --
250
251 IF p_plan_id is null and p_plan_category is null THEN
252 --
253 l_plan_category := '%%';
254 --
255 ELSIF p_plan_id is not null THEN
256 --
257 l_plan_category := null;
258 --
259 END IF;
260
261 write_log('l_plan_category: '||l_plan_category, DEBUG);
262
263 -- Bug no 2932073 And 2878657
264 -- Get the Legislation of the Business Group
265 open c_legislation_code (p_business_group_id);
266 fetch c_legislation_code into l_legislation_code;
267 close c_legislation_code;
268
269 write_log('l_legislation_code: '||l_legislation_code, DEBUG);
270 --End Bug no 2932073 And 2878657
271 FOR l_accrual_plan IN csr_accrual_plan(l_plan_category, l_calculation_date) LOOP
272
273 --
274 -- Set this plan's flag to "successful" by default. If an error occurs
275 -- during assignment processing this global variable will be set
276 -- to an error status as appropriate.
277 --
278 g_plan_status := SUCCESS;
279
280 l_count := l_count + 1;
281
282 write_log(NEWLINE);
283 write_log('Processing Plan ' || l_accrual_plan.accrual_plan_name||
284 ' (' || to_char(l_accrual_plan.accrual_plan_id) || ')...');
285
286 --
287 -- Loop for each Accrual Plan in the Carry Over process.
288 --
289 pay_us_pto_co_pkg.pto_carry_over_for_plan(
290 p_plan_id => l_accrual_plan.accrual_plan_id,
291 p_co_formula_id => l_accrual_plan.co_formula_id,
292 P_plan_ele_type_id => l_accrual_plan.accrual_plan_element_type_id,
293 P_co_ele_type_id => l_accrual_plan.co_element_type_id,
294 P_co_input_val_id => l_accrual_plan.co_input_value_id,
295 P_co_date_input_value_id => l_accrual_plan.co_date_input_value_id,
296 P_co_exp_date_input_value_id => l_accrual_plan.co_exp_date_input_value_id,
297 P_res_ele_type_id => l_accrual_plan.residual_element_type_id,
298 P_res_input_val_id => l_accrual_plan.residual_input_value_id,
299 P_res_date_input_value_id => l_accrual_plan.residual_date_input_value_id,
300 p_business_group_id => p_business_group_id,
301 P_Calculation_date => l_calculation_date,
302 P_co_mode => p_mode,
303 p_accrual_term => p_accrual_term,
304 p_session_date => l_session_date,
305 p_legislation_code => l_legislation_code
306 );
307 /*added p_Legislation_code => l_legislation_code
308 for bug no 2932073 and 2878657 */
309 --
310 -- Write the status of processing this plan to the log.
311 --
312 IF g_plan_status = SUCCESS THEN
313 write_log(l_accrual_plan.accrual_plan_name||' processed successfully.');
314 ELSIF g_plan_status = WARNING THEN
315 write_log(l_accrual_plan.accrual_plan_name||' processed with '||
316 'one or more errors.');
317 ELSIF g_plan_status = ERROR THEN
318 write_log(l_accrual_plan.accrual_plan_name||' encountered too '||
319 'many errors. Processing was aborted.');
320 END IF;
321
322 END LOOP;
323
324 write_log(l_proc||', 30', DEBUG);
325
326 l_message_count := per_accrual_message_pkg.count_messages;
327 for i in 1..l_message_count loop
328 --
329 l_message := per_accrual_message_pkg.get_message(i);
330 write_log(l_message, DEBUG);
331 --
332 end loop;
333
334 write_log(l_proc||', 35', DEBUG);
335
336 --
337 -- If no plans were found, error
338 --
339 if l_count = 0 then
340
341 write_log(l_proc||', 40', DEBUG);
342 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
343 hr_utility.set_message_token('PROCEDURE','US_PTO_CARRY_OVER');
344 hr_utility.set_message_token('STEP','1');
345 hr_utility.raise_error;
346
347 end if;
348
349 --
350 -- Set the concurrent request completion status.
351 --
352 ERRBUF := g_errbuf;
353 RETCODE:= g_retcode;
354 write_log(NEWLINE);
355
356 --
357 -- Pipe the parameters for ease of debugging.
358 --
359 write_log(' ', DEBUG);
360 write_log(' --------------------------------'||
361 '---------------------------------', DEBUG);
362 write_log(' LEAVING '||upper(l_proc), DEBUG);
363 write_log(' --------------------------------'||
364 '+--------------------------------', DEBUG);
365 write_log(' errbuf '||
366 errbuf, DEBUG);
367 write_log(' retcode '||
368 to_char(retcode), DEBUG);
369 write_log(' --------------------------------'||
370 '---------------------------------', DEBUG);
371 write_log(' ', DEBUG);
372
373 END carry_over;
374 --
375 ------------------------- pto_carry_over_for_plan ---------------------------
376 procedure pto_carry_over_for_plan
377 (p_plan_id number,
378 p_co_formula_id number,
379 P_plan_ele_type_id number,
380 P_co_ele_type_id number,
381 P_co_input_val_id number,
382 P_co_date_input_value_id number,
383 P_co_exp_date_input_value_id number,
384 P_res_ele_type_id number,
385 P_res_input_val_id number,
386 P_res_date_input_value_id number,
387 P_business_group_id number,
388 P_Calculation_date date,
389 P_co_mode varchar2,
390 p_accrual_term varchar2,
391 p_session_date date,
392 p_legislation_code Varchar2
393 ) is
394
395 --
396 -- The following cursor is designed to fetch all assignments enrolled onto
397 -- the given accrual plan. Because we do not know at this stage what the
398 -- effective co date will be (it may be calculated at a per assignment level),
399 -- we simply retrieve all distinct assigments from enrollments effective as at the
400 -- calculation date. We only get future assignments if the accrual term is
401 -- PTO_CURRENT because we may be carrying over ahead of the calculation date.
402 --
403 -- This cursor is tuned as part of performance bug fix 3420490.
404 cursor csr_get_assignment is
405 select distinct pee.assignment_id assignment_id, asg.assignment_number
406 from pay_element_entries_f pee,
407 pay_element_links_f pel,
408 per_all_assignments_f asg
409 where pee.element_type_id = p_plan_ele_type_id
410 and pee.element_type_id = pel.element_type_id
411 and pee.element_link_id = pel.element_link_id
412 and pee.assignment_id = asg.assignment_id
413 and asg.period_of_service_id is not null
414 and (p_accrual_term = 'PTO_CURRENT' or
415 (p_accrual_term = 'PTO_PREVIOUS' and
416 asg.effective_start_date < p_calculation_date));
417
418 l_proc varchar2(80) := 'pay_us_pto_co_pkg.pto_carry_over_for_plan';
419 l_accrual_term varchar2(30);
420 l_num_errors number := 0;
421
422 BEGIN
423
424 --
425 -- Pipe the parameters for ease of debugging.
426 --
427 write_log(' ', DEBUG);
428 write_log(' --------------------------------'||
429 '---------------------------------', DEBUG);
430 write_log(' ENTERING '||upper(l_proc), DEBUG);
431 write_log(' for plan '||to_char(p_plan_id), DEBUG);
432 write_log(' --------------------------------'||
433 '+--------------------------------', DEBUG);
434 write_log(' p_plan_id '||
435 to_char(p_plan_id), DEBUG);
436 write_log(' p_co_formula_id '||
437 to_char(p_co_formula_id), DEBUG);
438 write_log(' p_plan_ele_type_id '||
439 to_char(p_plan_ele_type_id), DEBUG);
440 write_log(' p_co_ele_type_id '||
441 to_char(p_co_ele_type_id), DEBUG);
442 write_log(' p_co_input_val_id '||
443 to_char(p_co_input_val_id), DEBUG);
444 write_log(' p_co_date_input_value_id '||
445 to_char(p_co_date_input_value_id), DEBUG);
446 write_log(' p_co_exp_date_input_value_id '||
447 to_char(p_co_exp_date_input_value_id), DEBUG);
448 write_log(' p_res_ele_type_id '||
449 to_char(p_res_ele_type_id), DEBUG);
450 write_log(' p_res_input_val_id '||
451 to_char(p_res_input_val_id), DEBUG);
452 write_log(' p_res_date_input_value_id '||
453 to_char(p_res_date_input_value_id), DEBUG);
454 write_log(' p_business_group_id '||
455 to_char(p_business_group_id), DEBUG);
456 write_log(' p_calculation_date '||
457 to_char(p_calculation_date), DEBUG);
458 write_log(' p_co_mode '||
459 p_co_mode, DEBUG);
460 write_log(' p_accrual_term '||
461 p_accrual_term, DEBUG);
462 write_log(' p_session_date '||
463 to_char(p_session_date), DEBUG);
464
465 /*start bug no 2932073 and 2878657 */
466 write_log(' p_legislation_code '||
467 p_legislation_code, DEBUG);
468
469 /*start bug no 2932073 and 2878657 */
470 write_log(' --------------------------------'||
471 '---------------------------------', DEBUG);
472 write_log(' ', DEBUG);
473
474 if p_accrual_term = 'PTO_CURRENT' then
475 l_accrual_term := 'CURRENT';
476 else
477 l_accrual_term := 'PREVIOUS';
478 end if;
479
480 write_log('l_accrual_term: '||l_accrual_term, DEBUG);
481
482 FOR l_asg IN csr_get_assignment LOOP
483
484 BEGIN
485
486 pay_us_pto_co_pkg.pto_carry_over_for_asg(
487 p_plan_id => p_plan_id
488 ,p_assignment_id => l_asg.assignment_id
489 ,p_co_formula_id => p_co_formula_id
490 ,p_plan_ele_type_id => p_plan_ele_type_id
491 ,p_co_ele_type_id => p_co_ele_type_id
492 ,p_co_input_val_id => p_co_input_val_id
493 ,p_co_date_input_value_id => p_co_date_input_value_id
494 ,p_co_exp_date_input_value_id => p_co_exp_date_input_value_id
495 ,p_res_ele_type_id => p_res_ele_type_id
496 ,p_res_input_val_id => p_res_input_val_id
497 ,p_res_date_input_value_id => p_res_date_input_value_id
498 ,p_business_group_id => p_business_group_id
499 ,p_calculation_date => p_calculation_date
500 ,p_co_mode => p_co_mode
501 ,p_accrual_term => l_accrual_term
502 ,p_session_date => p_session_date
503 ,p_legislation_code => p_legislation_code
504 );
505 /*added p_Legislation_code => l_legislation_code
506 for bug no 2932073 and 2878657 */
507
508
509 write_log(' Processing Assignment '||l_asg.assignment_number||
510 ' ('||to_char(l_asg.assignment_id)||')... successful.');
511
512 EXCEPTION
513
514 WHEN others THEN
515
516 --
517 -- Trap any errors that occur. Initially raise these as a warning.
518 --
519 g_plan_status := WARNING;
520 g_retcode := WARNING;
521 g_errbuf := 'An error occurred during the processing of one or more '||
522 'assignments.';
523 l_num_errors := l_num_errors + 1;
524
525 write_log(' Processing Assignment '||l_asg.assignment_number||
526 ' ('||to_char(l_asg.assignment_id)||')... failed.');
527 write_log(SQLERRM);
528
529 --
530 -- If the max number of errors has been exceeded, raise the error and
531 -- terminate processing of this plan.
532 --
533 IF l_num_errors > g_max_errors THEN
534
535 g_plan_status := ERROR;
536 g_retcode := ERROR;
537 g_errbuf := 'The number of assignments that errored is greater '||
538 'than the maximum number of errors allowed.';
539
540 RETURN;
541
542 END IF;
543
544 END;
545
546 END LOOP;
547
548 write_log('Leaving: '||l_proc||', 20', DEBUG);
549
550 END pto_carry_over_for_plan;
551
552 ------------------------- pto_carry_over_for_asg ---------------------------
553 procedure pto_carry_over_for_asg
554 ( p_plan_id number,
555 p_assignment_id number,
556 p_co_formula_id number,
557 p_plan_ele_type_id number,
558 p_co_ele_type_id number,
559 p_co_input_val_id number,
560 p_co_date_input_value_id number,
561 p_co_exp_date_input_value_id number,
562 p_res_ele_type_id number,
563 p_res_input_val_id number,
564 p_res_date_input_value_id number,
565 p_business_group_id number,
566 p_calculation_date date,
567 p_co_mode varchar2,
568 p_accrual_term varchar2,
569 p_session_date date,
570 p_legislation_code varchar2
571 ) is
572
573 --
574 -- Cursor to check whether an assignment has already been
575 -- processed for the current accrual term.
576 --
577 cursor csr_check_ee_exist (p_ele_link_id number,
578 p_effective_date date,
579 p_date_input_value_id number) is
580 select pee.element_entry_id
581 from pay_element_entries_f pee,
582 pay_element_entry_values_f pev
583 where pee.assignment_id = p_assignment_id
584 and pee.element_link_id = p_ele_link_id
585 and pee.element_entry_id = pev.element_entry_id
586 and pev.input_value_id = p_date_input_value_id
587 and p_effective_date between pee.effective_start_date
588 and pee.effective_end_date;
589
590 --
591 -- Cursor to fetch payroll details for a given asg
592 --
593 cursor c_payroll_period
594 (p_payroll_id number,
595 p_effective_date date) is
596 select ptp.start_date,
597 ptp.end_date
598 from per_time_periods ptp
599 where ptp.payroll_id = p_payroll_id
600 and p_effective_date between ptp.start_date
601 and ptp.end_date;
602
603
604 --
605 -- Cursor to get the first effective start date of the assignment
606 -- so that assignments starting mid payroll period can be
607 -- evaluated.
608 --
609 cursor c_get_asg_start_date is
610 select min(asg.effective_start_date)
611 from per_all_assignments_f asg
612 where asg.assignment_id = p_assignment_id
613 and asg.period_of_service_id is not null;
614
615 --
616 -- Cursor to get the last effective end date of the assignment
617 -- so that terminated assignments can be evaluated.
618 --
619 cursor c_get_asg_end_date is
620 select max(asg.effective_end_date)
621 from per_all_assignments_f asg
622 where asg.assignment_id = p_assignment_id
623 and asg.period_of_service_id is not null;
624
625 --
626 -- Gets the payroll_id of the assignment at the effective date.
627 -- If there is no payroll on the assignment at the effective
628 -- date, check for terminated assigments. If the accrual term is
629 -- 'CURRENT', we also need to consider future assignments.
630 --
631 cursor c_payroll_id (p_effective_date date) is
632 select asg.payroll_id
633 from per_all_assignments_f asg
634 where asg.assignment_id = p_assignment_id
635 and asg.period_of_service_id is not null
636 and asg.payroll_id is not null
637 and ((p_effective_date between
638 asg.effective_start_date and asg.effective_end_date)
639 or (asg.effective_end_date < p_effective_date
640 and asg.effective_end_date =
641 (select max(asg2.effective_end_date)
642 from per_all_assignments_f asg2
643 where asg2.assignment_id = asg.assignment_id
644 and asg2.period_of_service_id is not null
645 and asg2.payroll_id is not null))
646 or (p_accrual_term = 'CURRENT'
647 and asg.effective_start_date > p_effective_date
648 and asg.effective_start_date =
649 (select min(asg3.effective_start_date)
650 from per_all_assignments_f asg3
651 where asg3.assignment_id = asg.assignment_id
652 and asg3.period_of_service_id is not null
653 and asg3.payroll_id is not null)));
654
655 --
656 -- Local Variables
657 --
658 l_proc varchar2(80) := 'pay_us_pto_co_pkg.pto_carry_over_for_asg';
659 l_co_link_id number := null;
660 l_res_link_id number := null;
661 l_max_carryover number;
662 l_effective_date date;
663 l_expiry_date date;
664 l_carryover number;
665 l_residual number;
666 l_total_accrual number;
667 l_net_entitlement number;
668 l_payroll_id number;
669 l_co_ele_entry_id number := 0;
670 l_res_ele_entry_id number := 0;
671 l_dummy1 date;
672 l_dummy2 date;
673 l_dummy3 date;
674 l_start_date date;
675 l_end_date date;
676 l_min_asg_start_date date;
677 l_max_asg_end_date date;
678 l_new_ee_id number;
679 l_temp_payroll_id number;
680 l_enrolled boolean := true;
681
682
683
684 -- Declare tables for input value ids and Screen Entry Values
685
686 inp_value_id_tbl hr_entry.number_table;
687 scr_valuetbl hr_entry.varchar2_table;
688
689 BEGIN
690
691 --
692 -- Pipe the parameters for ease of debugging.
693 --
694 write_log(' ', DEBUG);
695 write_log(' --------------------------------'||
696 '---------------------------------', DEBUG);
697 write_log(' ENTERING '||upper(l_proc), DEBUG);
698 write_log(' for assignment '||to_char(p_assignment_id), DEBUG);
699 write_log(' --------------------------------'||
700 '+--------------------------------', DEBUG);
701 write_log(' p_plan_id '||
702 to_char(p_plan_id), DEBUG);
703 write_log(' p_assignment_id '||
704 to_char(p_assignment_id), DEBUG);
705 write_log(' p_co_formula_id '||
706 to_char(p_co_formula_id), DEBUG);
707 write_log(' p_plan_ele_type_id '||
708 to_char(p_plan_ele_type_id), DEBUG);
709 write_log(' p_co_ele_type_id '||
710 to_char(p_co_ele_type_id), DEBUG);
711 write_log(' p_co_input_val_id '||
712 to_char(p_co_input_val_id), DEBUG);
713 write_log(' p_co_date_input_value_id '||
714 to_char(p_co_date_input_value_id), DEBUG);
715 write_log(' p_co_exp_date_input_value_id '||
716 to_char(p_co_exp_date_input_value_id), DEBUG);
717 write_log(' p_res_ele_type_id '||
718 to_char(p_res_ele_type_id), DEBUG);
719 write_log(' p_res_input_val_id '||
720 to_char(p_res_input_val_id), DEBUG);
721 write_log(' p_res_date_input_value_id '||
722 to_char(p_res_date_input_value_id), DEBUG);
723 write_log(' p_business_group_id '||
724 to_char(p_business_group_id), DEBUG);
725 write_log(' p_calculation_date '||
726 to_char(p_calculation_date), DEBUG);
727 write_log(' p_co_mode '||
728 p_co_mode, DEBUG);
729 write_log(' p_accrual_term '||
730 p_accrual_term, DEBUG);
731 write_log(' p_session_date '||
732 to_char(p_session_date), DEBUG);
733
734 /*start bug no 2932073 and 2878657 */
735 write_log(' p_legislation_code '||
736 p_legislation_code, DEBUG);
737
738 /*start bug no 2932073 and 2878657 */
739 write_log(' --------------------------------'||
740 '---------------------------------', DEBUG);
741 write_log(' ', DEBUG);
742
743 --
744 -- Get the persons payroll_id.
745 --
746 open c_payroll_id(p_effective_date => p_calculation_date);
747 fetch c_payroll_id into l_payroll_id;
748 close c_payroll_id;
749
750 write_log('1st l_payroll_id: '||to_char(l_payroll_id), DEBUG);
751
752 --
753 -- We retrieve the max co and effective date here, so that
754 -- the exact effective date can be used immediately, rather
755 -- than the calculation date entered by the user
756 --
757 per_accrual_calc_functions.get_carry_over_values (
758 p_assignment_id => p_assignment_id,
759 p_co_formula_id => p_co_formula_id,
760 p_accrual_plan_id => p_plan_id,
761 p_business_group_id => p_business_group_id,
762 p_payroll_id => l_payroll_id,
763 p_calculation_date => p_calculation_date,
764 p_session_date => p_session_date,
765 p_accrual_term => p_accrual_term,
766 p_max_carry_over => l_max_carryover,
767 p_effective_date => l_effective_date,
768 p_expiry_date => l_expiry_date
769 );
770
771 write_log('p_max_carry_over: '||to_char(l_max_carryover), DEBUG);
772 write_log('p_effective_date: '||to_char(l_effective_date), DEBUG);
773 write_log('p_expiry_date: '||to_char(l_expiry_date), DEBUG);
774
775 if l_max_carryover is not null then
776 --
777 --
778 -- Get the persons payroll_id, effective at the time of
779 -- carry over.
780 --
781 write_log(l_proc||', 30', DEBUG);
782 open c_payroll_id(p_effective_date => l_effective_date);
783 fetch c_payroll_id into l_temp_payroll_id;
784 close c_payroll_id;
785
786 l_payroll_id := nvl(l_temp_payroll_id, l_payroll_id);
787
788 write_log('2nd l_payroll_id: '||to_char(l_payroll_id), DEBUG);
789
790 --
791 -- Get the links for the co and residual elements
792 --
793 write_log(l_proc||', 35', DEBUG);
794
795 l_co_link_id := hr_entry_api.get_link(
796 p_assignment_id => p_assignment_id,
797 p_element_type_id => p_co_ele_type_id,
798 p_session_date => l_effective_date + 1);
799
800 write_log('l_co_link_id: '||to_char(l_co_link_id), DEBUG);
801
802 l_res_link_id := hr_entry_api.get_link(
803 p_assignment_id => p_assignment_id,
804 p_element_type_id => p_res_ele_type_id,
805 p_session_date => l_effective_date + 1);
806
807 write_log('l_res_link_id: '||to_char(l_res_link_id), DEBUG);
808
809 l_enrolled := per_accrual_calc_functions.check_assignment_enrollment(
810 p_assignment_id => p_assignment_id,
811 p_accrual_plan_element_type_id => p_plan_ele_type_id,
812 p_calculation_date => l_effective_date + 1);
813
814 if l_enrolled then
815 write_log('l_enrolled: TRUE', DEBUG);
816 else
817 write_log('l_enrolled: FALSE', DEBUG);
818 end if;
819
820 end if;
821
822 --
823 -- Only proceed with this asg if links exist for both elements.
824 --
825
826 write_log(l_proc||', 55', DEBUG);
827
828 IF l_co_link_id is not null AND
829 l_res_link_id is not null AND
830 l_enrolled AND
831 l_max_carryover is not null THEN
832
833 write_log(l_proc||', 60', DEBUG);
834
835 per_accrual_calc_functions.Get_Net_Accrual (
836 P_Assignment_ID => p_assignment_id
837 ,P_Plan_ID => p_plan_id
838 ,P_Payroll_ID => l_payroll_id
839 ,P_Business_Group_ID => p_business_group_id
840 ,P_Assignment_Action_Id => -1
841 ,P_Accrual_Start_Date => null
842 ,P_Accrual_Latest_Balance => null
843 ,P_Calculation_Date => l_effective_date
844 ,P_Start_Date => l_dummy1
845 ,P_End_Date => l_dummy2
846 ,P_Accrual_End_Date => l_dummy3
847 ,P_Accrual => l_total_accrual
848 ,P_Net_Entitlement => l_net_entitlement
849 );
850
851 write_log('l_total_accrual: '||to_char(l_total_accrual), DEBUG);
852 write_log('l_net_entitlement: '||to_char(l_net_entitlement), DEBUG);
853
854 /*Added for 2878657 And 2932073 */
855
856
857
858 if p_legislation_code = 'ZA' then
859
860 write_log(l_proc||', 65', DEBUG);
861 PER_ZA_PTO_ACCRUAL_PERIOD.ZA_PTO_CARRYOVER_RESI_VALUE (
862 p_assignment_id => p_assignment_id
863 ,p_plan_id => p_plan_id
864 ,l_payroll_id => l_payroll_id
865 ,p_business_group_id => p_business_group_id
866 ,l_effective_date => l_effective_date
867 ,l_total_accrual => l_total_accrual
868 ,l_net_entitlement => l_net_entitlement
869 ,l_max_carryover => l_max_carryover
870 ,l_residual => l_residual
871 ,l_carryover => l_carryover
872 );
873
874 Else
875 if l_net_entitlement <= l_max_carryover then
876 --
877 write_log(l_proc||', 70', DEBUG);
878 l_carryover := round(l_net_entitlement, 3);
879 l_residual := 0;
880 --
881 else
882 --
883 write_log(l_proc||', 75', DEBUG);
884 l_carryover := round(l_max_carryover, 3);
885 l_residual := round((l_net_entitlement - l_max_carryover), 3);
886 --
887 end if;
888 End if;
889
890 /* End of the 2932073 and 2878657 */
891 /* The below code was there before adding the abov code for the 2932073 */
892 /*
893
894 if l_net_entitlement <= l_max_carryover then
895 --
896 write_log(l_proc||', 70', DEBUG);
897 l_carryover := round(l_net_entitlement, 3);
898 l_residual := 0;
899 --
900 else
901 --
902 write_log(l_proc||', 75', DEBUG);
903 l_carryover := round(l_max_carryover, 3);
904 l_residual := round((l_net_entitlement - l_max_carryover), 3);
905 --
906 end if;
907
908 */
909
910 write_log('l_carryover: '||to_char(l_carryover), DEBUG);
911 write_log('l_residual: '||to_char(l_residual), DEBUG);
912
913 --
914 -- We must get the payroll period start and end dates for
915 -- the period in which the element entry will be made,
916 -- as these are the effective start and end dates for all
917 -- non-recurring element entries.
918 --
919 open c_payroll_period(l_payroll_id,
920 l_effective_date + 1);
921 fetch c_payroll_period into l_start_date,
922 l_end_date;
923 close c_payroll_period;
924
925 write_log('l_start_date: '||to_char(l_start_date), DEBUG);
926 write_log('l_end_date: '||to_char(l_end_date), DEBUG);
927
928 --
929 -- Get the earliest assignment start date.
930 --
931 open c_get_asg_start_date;
932 fetch c_get_asg_start_date into l_min_asg_start_date;
933 close c_get_asg_start_date;
934
935 --
936 -- Get the latest assignment end date.
937 --
938 open c_get_asg_end_date;
939 fetch c_get_asg_end_date into l_max_asg_end_date;
940 close c_get_asg_end_date;
941
942 write_log('l_min_asg_start_date: '||to_char(l_min_asg_start_date), DEBUG);
943 write_log('l_max_asg_end_date: '||to_char(l_max_asg_end_date), DEBUG);
944
945 If l_min_asg_start_date <= l_start_date
946 and l_max_asg_end_date >= l_start_date then
947
948 -- Modified the if condition for the bug 6969078
949 -- Create the carryover element if the assignment
950 -- is valid for atleast one day in the pay period
951 -- rather than being valid for the whole pay period
952 -- and l_max_asg_end_date >= l_end_date then
953 --
954 -- Proceed with the element entries.
955 --
956 -- Check whether a carry over element entry already exists
957 -- for the given accrual plan, accrual term and assignment
958 --
959 write_log(l_proc||', 95', DEBUG);
960
961 open csr_check_ee_exist (l_co_link_id,
962 l_effective_date + 1,
963 p_co_date_input_value_id);
964
965 fetch csr_check_ee_exist into l_co_ele_entry_id;
966
967 write_log('l_co_ele_entry_id: '||to_char(l_co_ele_entry_id), DEBUG);
968
969 if csr_check_ee_exist%NOTFOUND and l_carryover <> 0 then
970 --
971 -- Insert entry for Carried Over element
972 --
973 write_log(l_proc||', 100', DEBUG);
974
975 inp_value_id_tbl(1) := p_co_date_input_value_id;
976 scr_valuetbl(1) := fnd_date.date_to_displaydate(l_effective_date + 1);
977
978 inp_value_id_tbl(2) := p_co_input_val_id;
979 scr_valuetbl(2) := to_char(l_carryover); -- Bug 4752106
980
981 inp_value_id_tbl(3) := p_co_exp_date_input_value_id;
982 scr_valuetbl(3) := fnd_date.date_to_displaydate(l_expiry_date);
983
984 write_log(l_proc||', 105', DEBUG);
985
986 hr_entry_api.insert_element_entry(
987 p_effective_start_date => l_start_date,
988 p_effective_end_date => l_end_date,
989 p_element_entry_id => l_new_ee_id,
990 p_assignment_id => p_assignment_id,
991 p_element_link_id => l_co_link_id,
992 p_creator_type => 'F',
993 p_entry_type => 'E',
994 p_num_entry_values => 3,
995 p_input_value_id_tbl => inp_value_id_tbl,
996 p_entry_value_tbl => scr_valuetbl);
997
998 write_log(l_proc||', 110', DEBUG);
999
1000 elsif csr_check_ee_exist%FOUND and p_co_mode = 'Y' THEN
1001
1002 --
1003 -- Update element entry for CO element, using
1004 -- date track CORRECTION mode
1005 --
1006 write_log(l_proc||', 115', DEBUG);
1007
1008 inp_value_id_tbl(1) := p_co_input_val_id;
1009 scr_valuetbl(1) := to_char(l_carryover); -- Bug 4752106
1010
1011 inp_value_id_tbl(2) := p_co_date_input_value_id;
1012 scr_valuetbl(2) := fnd_date.date_to_displaydate(l_effective_date + 1);
1013
1014 inp_value_id_tbl(3) := p_co_exp_date_input_value_id;
1015 scr_valuetbl(3) := fnd_date.date_to_displaydate(l_expiry_date);
1016
1017 hr_entry_api.update_element_entry(
1018 p_dt_update_mode => 'CORRECTION',
1019 p_session_date => l_start_date,
1020 p_element_entry_id => l_co_ele_entry_id,
1021 p_num_entry_values => 3,
1022 p_input_value_id_tbl => inp_value_id_tbl,
1023 p_entry_value_tbl => scr_valuetbl);
1024
1025 write_log(l_proc||', 120', DEBUG);
1026
1027 end if;
1028
1029 close csr_check_ee_exist;
1030
1031 write_log(l_proc||', 120', DEBUG);
1032
1033 --
1034 -- Check whether a residual element entry already exists
1035 -- for the given accrual plan, accrual term and assignment
1036 --
1037 open csr_check_ee_exist (l_res_link_id,
1038 l_effective_date + 1,
1039 p_res_date_input_value_id);
1040
1041 fetch csr_check_ee_exist INTO l_res_ele_entry_id;
1042
1043 write_log('l_res_ele_entry_id: '||to_char(l_res_ele_entry_id), DEBUG);
1044
1045 IF csr_check_ee_exist%NOTFOUND and l_residual <> 0 then
1046 --
1047 -- Insert entry for Residual element
1048 --
1049 write_log(l_proc||', 130', DEBUG);
1050
1051 inp_value_id_tbl(1) := p_res_input_val_id;
1052 scr_valuetbl(1) := to_char(l_residual); -- Bug 4752106
1053
1054 inp_value_id_tbl(2) := p_res_date_input_value_id;
1055 scr_valuetbl(2) := fnd_date.date_to_displaydate(l_effective_date + 1);
1056
1057 hr_entry_api.insert_element_entry(
1058 p_effective_start_date => l_start_date,
1059 p_effective_end_date => l_end_date,
1060 p_element_entry_id => l_new_ee_id,
1061 p_assignment_id => p_assignment_id,
1062 p_element_link_id => l_res_link_id,
1063 p_creator_type => 'F',
1064 p_entry_type => 'E',
1065 p_num_entry_values => 2,
1066 p_input_value_id_tbl => inp_value_id_tbl,
1067 p_entry_value_tbl => scr_valuetbl);
1068
1069 write_log(l_proc||', 135', DEBUG);
1070
1071 elsif csr_check_ee_exist%FOUND AND p_co_mode = 'Y' THEN
1072 --
1073 -- Update Element entry for Residual element, using
1074 -- date track CORRECTION mode.
1075 --
1076 write_log(l_proc||', 140', DEBUG);
1077
1078 inp_value_id_tbl(1) := p_res_input_val_id;
1079 scr_valuetbl(1) := to_char(l_residual); -- Bug 4752106
1080
1081 inp_value_id_tbl(2) := P_res_date_input_value_id;
1082 scr_valuetbl(2) := fnd_date.date_to_displaydate(l_effective_date + 1);
1083
1084 hr_entry_api.update_element_entry(
1085 p_dt_update_mode => 'CORRECTION',
1086 p_session_date => l_start_date,
1087 p_element_entry_id => l_res_ele_entry_id,
1088 p_num_entry_values => 2,
1089 p_input_value_id_tbl => inp_value_id_tbl,
1090 p_entry_value_tbl => scr_valuetbl);
1091
1092 write_log(l_proc||', 145', DEBUG);
1093
1094 end if;
1095
1096 close csr_check_ee_exist;
1097
1098 end if;
1099
1100 end if;
1101
1102 write_log('Leaving: '||l_proc||', 150', DEBUG);
1103
1104 end pto_carry_over_for_asg;
1105
1106 END pay_us_pto_co_pkg;