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