1 package body pay_retro_pkg
2 /* $Header: pyretpay.pkb 120.28.12010000.2 2008/10/01 06:14:18 ankagarw ship $ */
3 as
4 --
5 --
6 /*
7 PRODUCT
8 Oracle*Payroll
9 --
10 NAME
11 pyretpay.pkb
12 --
13 DESCRIPTION
14 --
15 MODIFIED (DD-MON-YYYY)
16 ckesanap 17-SEP-2008 Bug 7335351. Modified process_recorded_date.
17 ckesanap 25-AUG-2008 Bug 7335351. Added a cursor get_proc_retro_rrv to
18 fetch all the processed retro entries in a given
19 pay period. Creating negative balance adjustment
20 run results for all such retro entries.
21 ckesanap 18-AUG-2008 Bug 7335351. Modified overlap_adjustments.
22 ckesanap 18-JUL-2008 Bug 7248998. Modified process_recorded_date to
23 return the assignment's reprocess date for the
24 retro_overlap enhancement.
25 salogana 18-MAR-2008 In reset_recorded_request to avoid
26 performance issues function to_char
27 has been added for l_assign_id which
28 enables the queries to use the
29 appropriate indexes.
30 kkawol 02-AUG-2007 Added reset_recorded_request and
31 process_recorded_date.
32 alogue 26-JUN-2007 Performance fix to maintain_retro_entry.
33 Bug 6147807.
34 kkawol 19-JUN-2007 get_reprocess_type now uses pay_proc_environment
35 bgid and legc.
36 kkawol 04-JUN-2007 Changed get_entry_path so reversals work correctly.
37 thabara 26-MAR-2007 Modified maintain_retro_entry to update
38 retro_component_id.
39 nbristow 15-JAN-2007 Added overlap_adjustments.
40 alogue 12-JAN-2007 Further changes to get_source_element_type.
41 alogue 10-JAN-2007 Further changes to get_source_element_type
42 and debug if required.
43 alogue 09-JAN-2007 Re-implement get_source_element_type
44 changes. Bug 5747560.
45 alogue 30-NOV-2006 Change comment for last change!
46 alogue 24-NOV-2006 Undo recent changes to get_source_element_type.
47 nbristow 06-NOV-2006 get_ee_overlap_date was not joining to
48 the retro assignments table correctly
49 in a multi-assignment environment.
50 mreid 06-OCT-2006 Added hint to business group sql
51 nbristow 28-SEP-2006 Fixed test harness failures.
52 alogue 13-SEP-2006 Avoid ORA-01422 in get_source_element_type.
53 Bug 5482805.
54 nbristow 12-SEP-2006 Added new get_entry_path.
55 alogue 07-SEP-2006 Ensure run result is a processed one in
56 get_source_element_type. Bug 5482805.
57 alogue 01-SEP-2006 Performance fix to get_ee_overlap_date_int.
58 Bug 5482574.
59 nbristow 01-JUN-2006 Added process_retro_entry
60 alogue 17-MAR-2006 Caches in get_reprocess_type. Bug 5101847.
61 nbristow 14-MAR-2006 Added get_retro_asg_id.
62 alogue 07-MAR-2006 Enhanced generate_obj_grp_actions so
63 don't create actions for assignments
64 only existing in the future. Bug 5082050.
65 alogue 01-MAR-2006 Enhanced get_ee_overlap_date_int to
66 account for POG master actions owning
67 the retro element entries. Bug 5057817.
68 kkawol 26-JAN-2006 added get_entry_path to convert entry
69 paths for retropay into new shorter format.
70 nbristow 28-SEP-2005 get_asg_from_pg_action needs
71 to take into account of date
72 effectiveity.
73 nbristow 29-JUN-2005 Overlap satetment was using the
74 wrong entry table.
75 nbristow 23-MAR-2005 Performance improvement to the overlap
76 statements.
77 nbristow 22-MAR-2005 get_ee_overlap_date now only overrides
78 the date if there are retro entries for
79 the overlapping period.
80 nbristow 12-JAN-2005 Added date effective joins to
81 retro_component_usages.
82 nbristow 25-NOV-2004 Retropay multi assignments
83 jford 08-SEP-2004 Get_retro_component moved to pyretutl.pkb
84 tbattoo 09-AUG-2004 Added functions to suporrt reversals in retropay
85 jford 05-AUG-2004 maintain_entries now Merges System and User
86 nbristow 14-JUL-2004 Changes for Enhanced version of Retro
87 NOticfications.
88 nbristow 26-MAY-2004 Fixed previous change.
89 alogue 06-MAY-2004 Qualify result to be PROCESSED ones in
90 get_source_element_type to avoid ORA-01422.
91 Bug 3598256.
92 alogue 27-APR-2004 Performance fix in latest_replace_ovl_del_ee.
93 nbristow 15-MAR-2004 Added is_retro_rr.
94 kkawol 07-JAN-2004 Added latest_replace_ovl_ee,
95 latest_replace_ovl_del_ee.
96 kkawol 20-NOV-2003 Passing bus grp id to get_retro_element,
97 this is required when calling is_date_in_span.
98 nbristow 07-OCT-2003 Added nocopy to get_ee_overlap_date.
99 nbristow 07-OCT-2003 Added process_retro_value.
100 nbristow 03-OCT-2003 Added get_ee_overlap_date.
101 nbristow 02-SEP-2003 Changed get_retro_element to
102 return correct element.
103 nbristow 28-AUG-2003 Added dbdrv statements.
104 nbristow 28-AUG-2003 Uncommented exit.
105 nbristow 27-AUG-2003 Changes for Advanced Retropay
106 jalloun 30-JUL-1996 Added error handling.
107 nbristow 12-MAR-1996 Created
108 */
109 -- Caches for get_reprocess_type
110 g_bus_grp per_business_groups_perf.business_group_id%type := null;
111 g_leg_code per_business_groups_perf.legislation_code%type := null;
112 --
113 procedure retro_run_proc
114 is
115 begin
116 null;
117 end;
118 --
119 procedure retro_end_proc
120 is
121 begin
122 null;
123 end;
124 --
125 --
126 -- Name process_retro_entry
127 -- Description
128 --
129 -- Called from the Elment Entry fetch to determin if the Entry
130 -- can be processed in the Run
131 --
132 function process_retro_entry(
133 p_element_entry_id in number,
134 p_element_type_id in number,
135 p_retro_comp_id in number,
136 p_retro_asg_id in number,
137 p_ee_creator_id in number,
138 p_action_sequence in number
139 )
140 return number
141 is
142 l_result number;
143 begin
144 select 1
145 into l_result
146 from dual
147 where pay_retro_pkg.process_retro_value(
148 p_element_entry_id,
149 p_element_type_id,
150 p_retro_comp_id,
151 p_retro_asg_id
152 ) = 'Y'
153 and exists (select ''
154 from pay_assignment_actions paa
155 where paa.assignment_action_id = p_ee_creator_id
156 and paa.action_sequence < p_action_sequence
157 );
158 --
159 return l_result;
160 --
161 exception
162 when no_data_found then
163 return 0;
164 end process_retro_entry;
165 --
166 -- Name get_reprocess_type
167 -- Description
168 --
169 -- Find out how to process the entry for this component.
170 --
171 function get_reprocess_type(
172 p_entry_id in number,
173 p_element_type_id in number,
174 p_retro_comp_id in number,
175 p_retro_asg_id in number,
176 p_default_type in varchar2 default 'R'
177 )
178 return varchar2
179 is
180 --
181 l_dummy number;
182 l_reprocess_type pay_retro_component_usages.reprocess_type%type;
183 --
184 begin
185 select 1
186 into l_dummy
187 from pay_retro_entries pre
188 where retro_assignment_id = p_retro_asg_id
189 and retro_component_id = p_retro_comp_id
190 and element_entry_id = p_entry_id;
191 --
192 return 'R';
193 --
194 exception
195 when no_data_found then
196 --
197 begin
198 --
199 hr_utility.trace('Get reprocess type bgid :' || to_char(pay_proc_environment_pkg.bgid));
200 hr_utility.trace('Get reprocess type legc:' || pay_proc_environment_pkg.legc);
201
202 select prcu.reprocess_type
203 into l_reprocess_type
204 from pay_retro_component_usages prcu
205 where prcu.retro_component_id = p_retro_comp_id
206 and prcu.creator_id = p_element_type_id
207 and prcu.creator_type = 'ET'
208 and (( prcu.business_group_id = pay_proc_environment_pkg.bgid
209 and prcu.legislation_code is null)
210 or
211 ( prcu.legislation_code = pay_proc_environment_pkg.legc
212 and prcu.business_group_id is null)
213 or
214 ( prcu.legislation_code is null
215 and prcu.business_group_id is null)
216 );
217 --
218 return l_reprocess_type;
219 --
220 exception
221 when no_data_found then
222 return p_default_type;
223 end;
224 end get_reprocess_type;
225 --
226 --
227 -- Name get_retro_process_type
228 -- Description
229 --
230 -- Determine the process type for a retro entry.
231 --
232 function get_retro_process_type(
233 p_entry_id in number,
234 p_element_type_id in number,
235 p_retro_comp_id in number,
236 p_retro_asg_id in number,
237 p_source_type in varchar2
238 )
239 return varchar2
240 is
241 proc_type pay_retro_component_usages.reprocess_type%type;
242 begin
243 --
244 proc_type := get_reprocess_type(p_entry_id,
245 p_element_type_id,
246 p_retro_comp_id,
247 p_retro_asg_id);
248 --
249 if (proc_type = 'P' and p_source_type = 'I') then
250 proc_type := 'R';
251 end if;
252 --
253 return proc_type;
254 --
255 end get_retro_process_type;
256 --
257 --
258 -- Name process_value
259 -- Description
260 --
261 -- Used by the EE fetch to determine if an entry should
262 -- be processed by this Component.
263 --
264 function process_value(p_value_type in varchar2,
265 p_entry_id in number,
266 p_element_type_id in number,
267 p_retro_comp_id in number,
268 p_retro_asg_id in number,
269 p_result_type in varchar2)
270 return varchar2
271 is
272 --
273 l_dummy number;
274 l_reprocess_type pay_retro_component_usages.reprocess_type%type;
275 --
276 begin
277 --
278 l_reprocess_type := get_reprocess_type(p_entry_id,
279 p_element_type_id,
280 p_retro_comp_id,
281 p_retro_asg_id);
282 --
283 if (p_value_type = 'EE') then
284 --
285 if (l_reprocess_type = 'R') then
286 return 'Y';
287 else
288 return 'N';
289 end if;
290 --
291 else
292 --
293 -- It must be a Run Result
294 --
295 if (l_reprocess_type = 'R') then
296 return 'N';
297 elsif (l_reprocess_type = 'S') then
298 return 'Y';
299 else
300 --
301 -- It must be a PARTIAL
302 --
303 if (p_result_type = 'E') then
304 return 'Y';
305 else
306 return 'N';
307 end if;
308 end if;
309 end if;
310 --
311 end process_value;
312 --
313 --
314 -- Name process_retro_value
315 -- Description
316 --
317 -- This function is used in the Element entry fetch to
318 -- determine if a retro entry can be processed in an
319 -- overlaping retropay.
320 --
321 function process_retro_value(
322 p_entry_id in number,
323 p_element_type_id in number,
324 p_retro_comp_id in number,
325 p_retro_asg_id in number
326 )
327 return varchar2
328 is
329 --
330 l_dummy number;
331 l_reprocess_type pay_retro_component_usages.reprocess_type%type;
332 --
333 begin
334 --
335 /* If no retro component is supplied then it must be
336 an old style retropay. Hence do not process
337 the retro entry
338 */
339 if (p_retro_comp_id is null) then
340 return 'N';
341 end if;
342 --
343 l_reprocess_type := get_reprocess_type(p_entry_id,
344 p_element_type_id,
345 p_retro_comp_id,
346 p_retro_asg_id,
347 'D'); -- Do not reprocess
348 --
349 if (l_reprocess_type = 'R') then
350 return 'Y';
351 else
352 return 'N';
353 end if;
354 --
355 end process_retro_value;
356 --
357 --
358 -- Name is_retro_entry
359 -- Description
360 --
361 -- This function determines if an entry is a retro entry
362 --
363 function is_retro_entry(p_creator_type in varchar2)
364 return number
365 is
366 begin
367 --
368 if (p_creator_type in ('P', 'R', 'RR', 'EE', 'PR', 'NR') ) then
369 return 1;
370 else
371 return 0;
372 end if;
373 --
374 end is_retro_entry;
375 --
376 function is_retro_rr(p_element_entry_id in number,
377 p_date in date)
378 return number
379 is
380 --
381 l_creator_type pay_element_entries_f.creator_type%type;
382 --
383 begin
384 --
385 select creator_type
386 into l_creator_type
387 from pay_element_entries_f
388 where element_entry_id = p_element_entry_id
389 and p_date between effective_start_date
390 and effective_end_date;
391 --
392 return pay_retro_pkg.is_retro_entry(l_creator_type);
393 --
394 exception
395 when no_data_found then
396 return 0;
397 end is_retro_rr;
398 --
399 --
400 -- Name get_source_element_type
401 -- Description
402 --
403 -- Find the originating element type.
404 --
405 function get_source_element_type (p_entry_id in number,
406 p_aa_id in number)
407 return number
408 is
409 --
410 cursor c_rr
411 is
412 select prr2.element_type_id, prr2.element_entry_id
413 from pay_run_results prr2
414 where prr2.source_id = p_entry_id
415 and nvl(prr2.element_entry_id,-999) = p_entry_id
416 and prr2.source_type = 'E'
417 and prr2.assignment_action_id = p_aa_id;
418 --
419 l_src_et_id pay_run_results.element_type_id%type;
420 --
421 begin
422 --
423 begin
424 select distinct prr2.element_type_id
425 into l_src_et_id
426 from pay_run_results prr2
427 where prr2.source_id = p_entry_id
428 and nvl(prr2.element_entry_id,-999) = p_entry_id
429 and prr2.source_type = 'E'
430 and prr2.assignment_action_id = p_aa_id;
431 --
432 exception
433 when others then
434
435 hr_utility.trace('Clash : '||p_entry_id||' '||p_aa_id);
436 for rr in c_rr loop
437 hr_utility.trace(rr.element_type_id||' '||rr.element_entry_id);
438 end loop;
439
440 raise;
441 end;
442 --
443 return l_src_et_id;
444 --
445 end get_source_element_type;
446 --
447 -- Name get_retro_element
448 -- Description
449 --
450 -- Deterime the Retro Element that should be used for this
451 -- Element Type, Component and date combination
452 --
453 procedure get_retro_element(p_element_type_id in number,
454 p_retro_eff_date in date,
455 p_run_eff_date in date,
456 p_retro_comp_id in number,
457 p_adjustment_type in varchar2,
458 p_retro_ele_type_id out nocopy number,
459 p_business_group_id in number default null
460 )
461 is
462 --
463 l_retro_ele_type_id pay_element_types_f.retro_summ_ele_id%type;
464 --
465 begin
466 --
467 l_retro_ele_type_id := null;
468 --
469 if (p_retro_comp_id is null) then
470 --
471 select nvl(pet1.retro_summ_ele_id, pet1.element_type_id)
472 into l_retro_ele_type_id
473 from pay_element_types_f pet1
474 where pet1.element_type_id = p_element_type_id
475 and p_retro_eff_date between pet1.effective_start_date
476 and pet1.effective_end_date;
477 --
478 else
479 --
480 declare
481 l_leg_code per_business_groups_perf.legislation_code%type;
482 begin
483 --
484 select legislation_code
485 into l_leg_code
486 from per_business_groups_perf
487 where business_group_id = p_business_group_id;
488 --
489 select pesu.retro_element_type_id
490 into l_retro_ele_type_id
491 from pay_element_span_usages pesu,
492 pay_retro_component_usages prcu,
493 pay_time_spans pts
494 where prcu.retro_component_id = p_retro_comp_id
495 and prcu.creator_id = p_element_type_id
496 and prcu.creator_type = 'ET'
497 and prcu.retro_component_usage_id = pesu.retro_component_usage_id
498 and nvl(pesu.adjustment_type, 'A') = p_adjustment_type
499 and pay_core_dates.is_date_in_span
500 (pts.start_time_def_id,
501 pts.end_time_def_id,
502 p_run_eff_date,
503 p_retro_eff_date,
504 p_business_group_id) = 'Y'
505 and pts.time_span_id = pesu.time_span_id
506 and pts.creator_id = prcu.retro_component_id
507 and (( prcu.business_group_id = p_business_group_id
508 and prcu.legislation_code is null)
509 or
510 ( prcu.legislation_code = l_leg_code
511 and prcu.business_group_id is null)
512 or
513 ( prcu.legislation_code is null
514 and prcu.business_group_id is null)
515 )
516 and (( pesu.business_group_id = p_business_group_id
517 and pesu.legislation_code is null)
518 or
519 ( pesu.legislation_code = l_leg_code
520 and pesu.business_group_id is null)
521 or
522 ( pesu.legislation_code is null
523 and pesu.business_group_id is null)
524 );
525 --
526 exception
527 --
528 when no_data_found then
529 --
530 /* When a Credit or Debit Retro Element does not exist look
531 for a Standard Retro Element
532 */
533 select pesu.retro_element_type_id
534 into l_retro_ele_type_id
535 from pay_element_span_usages pesu,
536 pay_retro_component_usages prcu,
537 pay_time_spans pts
538 where prcu.retro_component_id = p_retro_comp_id
539 and prcu.creator_id = p_element_type_id
540 and prcu.creator_type = 'ET'
541 and prcu.retro_component_usage_id = pesu.retro_component_usage_id
542 and nvl(pesu.adjustment_type, 'A') = 'A'
543 and pay_core_dates.is_date_in_span
544 (pts.start_time_def_id,
545 pts.end_time_def_id,
546 p_run_eff_date,
547 p_retro_eff_date,
548 p_business_group_id) = 'Y'
549 and pts.time_span_id = pesu.time_span_id
550 and pts.creator_id = prcu.retro_component_id
551 and (( prcu.business_group_id = p_business_group_id
552 and prcu.legislation_code is null)
553 or
554 ( prcu.legislation_code = l_leg_code
555 and prcu.business_group_id is null)
556 or
557 ( prcu.legislation_code is null
558 and prcu.business_group_id is null)
559 )
560 and (( pesu.business_group_id = p_business_group_id
561 and pesu.legislation_code is null)
562 or
563 ( pesu.legislation_code = l_leg_code
564 and pesu.business_group_id is null)
565 or
566 ( pesu.legislation_code is null
567 and pesu.business_group_id is null)
568 );
569 --
570 end;
571 --
572 end if;
573 --
574 p_retro_ele_type_id := l_retro_ele_type_id;
575 --
576 exception
577 when no_data_found then
578 p_retro_ele_type_id := null;
579 --
580 end get_retro_element;
581 --
582 --
583 -- Name get_ee_overlap_date
584 -- Description
585 --
586 -- Given a start date to run the Retropay process, does the
587 -- system think that we need to alter this date in order for
588 -- retropay to calculate correctly.
589 --
590 -- At the moment this acts like Retropay by Aggregate to
591 -- go back to the earliest overlapping retropay.
592 --
593 procedure get_ee_overlap_date_int(p_asg_id in number,
594 p_start_date in date,
595 p_effective_date in date,
596 p_adj_start_date out nocopy date
597 )
598 is
599 l_start_date date;
600 l_reprocess_start_date date;
601 begin
602 --
603 /* Get the earliest start date on the payroll actions */
604 select min(ppa.start_date)
605 into l_start_date
606 from pay_payroll_actions ppa,
607 pay_assignment_actions paa_ret,
608 pay_assignment_actions paa_mret
609 where ppa.effective_date between p_start_date
610 and p_effective_date
611 and ppa.action_type = 'L'
612 and paa_ret.payroll_action_id = ppa.payroll_action_id
613 and paa_ret.assignment_id = p_asg_id
614 and paa_mret.object_id = paa_ret.object_id
615 and paa_mret.object_type = paa_ret.object_type
616 and paa_mret.payroll_action_id = paa_ret.payroll_action_id
617 and paa_mret.source_action_id is null
618 and exists (select ''
619 from pay_element_entries_f pee
620 where pee.creator_id = paa_mret.assignment_action_id
621 and pee.creator_type in ('RR', 'EE', 'NR', 'PR')
622 and pee.assignment_id = paa_ret.assignment_id);
623 --
624 select min(pra.reprocess_date)
625 into l_reprocess_start_date
626 from pay_retro_assignments pra,
627 pay_payroll_actions ppa,
628 pay_assignment_actions paa_ret,
629 pay_assignment_actions paa_mret
630 where ppa.effective_date between p_start_date
631 and p_effective_date
632 and ppa.action_type = 'L'
633 and paa_ret.payroll_action_id = ppa.payroll_action_id
634 and paa_ret.assignment_id = p_asg_id
635 and pra.retro_assignment_action_id = paa_mret.assignment_action_id
636 + decode(paa_ret.action_sequence, 0, 0, 0)
637 and paa_mret.object_id = paa_ret.object_id
638 and paa_mret.object_type = paa_ret.object_type
639 and paa_mret.payroll_action_id = paa_ret.payroll_action_id
640 and paa_mret.source_action_id is null
641 and exists (select ''
642 from pay_element_entries_f pee
643 where pee.creator_id = paa_mret.assignment_action_id
644 and pee.creator_type in ('RR', 'EE', 'NR', 'PR')
645 and pee.assignment_id = paa_ret.assignment_id);
646 --
647 l_start_date := nvl(l_start_date, p_start_date);
648 l_reprocess_start_date := nvl(l_reprocess_start_date, p_start_date);
649 l_start_date := least(l_start_date, l_reprocess_start_date);
650 --
651 /* OK we need to recursively call the procedure
652 to get the absolutely earliest date the Retropay
653 should run for.
654 */
655 if (l_start_date >= p_start_date) then
656 p_adj_start_date := p_start_date;
657 else
658 --
659 get_ee_overlap_date_int(p_asg_id,
660 l_start_date,
661 p_effective_date,
662 p_adj_start_date);
663 end if;
664 end get_ee_overlap_date_int;
665 --
666 procedure get_ee_overlap_date(p_assact in number,
667 p_start_date in date,
668 p_effective_date in date,
669 p_adj_start_date out nocopy date
670 )
671 is
672 --
673 cursor c_asg (p_assact number)
674 is
675 select paa.assignment_id
676 from pay_assignment_actions paa,
677 pay_assignment_actions paa2
678 where paa2.assignment_action_id = p_assact
679 and paa2.object_id = paa.object_id
680 and paa2.object_type = paa.object_type
681 and paa2.payroll_action_id = paa.payroll_action_id
682 and paa.assignment_id is not null;
683 --
684 l_adj_start_date date;
685 --
686 begin
687 --
688 p_adj_start_date := p_start_date;
689 for asgrec in c_asg(p_assact) loop
690 --
691 get_ee_overlap_date_int(asgrec.assignment_id,
692 p_start_date,
693 p_effective_date,
694 l_adj_start_date);
695 p_adj_start_date := least(p_adj_start_date, l_adj_start_date);
696 --
697 end loop;
698 --
699 end get_ee_overlap_date;
700 --
701 -- Name latest_replace_ovl_ee
702 --
703 -- Description
704 --
705 -- For replacement retropay, we are only interested in the most recent overlap
706 -- entry. This procedure works out whether an overlap is actually the
707 -- latest one for an entry.
708 --
709 function latest_replace_ovl_ee ( p_element_entry_id in NUMBER)
710 return varchar2
711 is
712 --
713 l_ovl_exists number;
714 begin
715 --
716 select count(*)
717 into l_ovl_exists
718 from pay_entry_process_details pepd1,
719 pay_entry_process_details pepd2
720 where pepd1.element_entry_id = p_element_entry_id
721 and pepd2.element_entry_id > pepd1.element_entry_id
722 and pepd1.run_result_id = pepd2.run_result_id
723 and pepd1.source_entry_id = pepd2.source_entry_id
724 and pepd1.source_asg_action_id = pepd2.source_asg_action_id
725 and pepd1.source_element_type_id = pepd2.source_element_type_id
726 and pepd1.retro_component_id = pepd2.retro_component_id
727 and ((pepd1.tax_unit_id is null
728 and pepd2.tax_unit_id is null
729 ) OR
730 (pepd1.tax_unit_id is not null
731 and pepd2.tax_unit_id is not null
732 and pepd1.tax_unit_id = pepd2.tax_unit_id
733 ));
734 --
735 if (l_ovl_exists = 0) then
736 return 'Y';
737 else
738 return 'N';
739 end if;
740 --
741 end latest_replace_ovl_ee;
742 --
743 -- Name latest_replace_ovl_del_ee
744 --
745 -- Description
746 --
747 -- For replacement retropay, we are only interested in the most recent overlap
748 -- entry. This procedure works out whether an overlap is actually the
749 -- latest one for an entry.
750 -- This procedure is to return negative replacement entries which have no
751 -- matching positive replacement, meaning the entry has been deleted.
752 -- First check it's the last overlap, then check there's no matching PR.
753 function latest_replace_ovl_del_ee ( p_element_entry_id in NUMBER)
754 return varchar2
755 is
756 --
757 l_ovl_exists number;
758 l_matching_pr number;
759 begin
760 --
761 select count(*)
762 into l_ovl_exists
763 from pay_entry_process_details pepd1,
764 pay_entry_process_details pepd2
765 where pepd1.element_entry_id = p_element_entry_id
766 and pepd2.element_entry_id > pepd1.element_entry_id
767 and pepd1.run_result_id = pepd2.run_result_id
768 and pepd1.source_entry_id = pepd2.source_entry_id
769 and pepd1.source_asg_action_id = pepd2.source_asg_action_id
770 and pepd1.source_element_type_id = pepd2.source_element_type_id
771 and pepd1.retro_component_id = pepd2.retro_component_id
772 and ((pepd1.tax_unit_id is null
773 and pepd2.tax_unit_id is null
774 ) OR
775 (pepd1.tax_unit_id is not null
776 and pepd2.tax_unit_id is not null
777 and pepd1.tax_unit_id = pepd2.tax_unit_id
778 ));
779 --
780 if (l_ovl_exists = 0) then
781 select count(*)
782 into l_matching_pr
783 from pay_entry_process_details pepd1,
784 pay_element_entries_f pee1
785 where pepd1.element_entry_id = p_element_entry_id
786 and pepd1.element_entry_id = pee1.element_entry_id
787 and exists
788 (select 'Y'
789 from pay_entry_process_details pepd2,
790 pay_element_entries_f pee2
791 where pee2.creator_type = 'PR'
792 and pee2.element_entry_id = pepd2.element_entry_id
793 and pee2.assignment_id = pee1.assignment_id
794 and pepd1.run_result_id = pepd2.run_result_id
795 and pepd1.source_entry_id = pepd2.source_entry_id
796 and pepd1.source_asg_action_id = pepd2.source_asg_action_id
797 and pepd1.source_element_type_id = pepd2.source_element_type_id
798 and pepd1.retro_component_id = pepd2.retro_component_id
799 and ((pepd1.tax_unit_id is null
800 and pepd2.tax_unit_id is null
801 ) OR
802 (pepd1.tax_unit_id is not null
803 and pepd2.tax_unit_id is not null
804 and pepd1.tax_unit_id = pepd2.tax_unit_id
805 ))
806 and pee1.creator_id = pee2.creator_id
807 );
808 --
809 if (l_matching_pr = 0) then
810 return 'Y';
811 else
812 return 'N';
813 end if;
814 --
815 else
816 return 'N';
817 end if;
818 --
819 end latest_replace_ovl_del_ee;
820 --
821 --
822 -- ----------------------------------------------------------------------------
823 -- --
824 -- get_retro_component_id
825 --
826 -- This Function is called during the process to insert the retro_entry --
827 -- A "Recalculation Reason" (or Retro-Component) is need to associate with --
828 -- the entry details. EG What kind of change has required this entry to be--
829 -- recalculated
830 --
831 -- Result: An ID of the seeded retro_component
832 -- ----------------------------------------------------------------------------
833 --
834 FUNCTION get_retro_component_id (
835 p_element_entry_id in number,
836 p_ef_date in date) return number IS
837 --
838 l_retro_comp_id number := -1;
839 BEGIN
840 -- This procedure is obsolete
841 -- All code should be calling the following directly.
842 --
843 l_retro_comp_id := pay_retro_utils_pkg.get_retro_component_id(
844 p_element_entry_id,
845 p_ef_date,
846 null); -- Direct calls should also pass element_type_id
847 --
848 -- hr_utility.trace(' Returned component_id is '||l_retro_comp_id);
849 return l_retro_comp_id;
850 END get_retro_component_id;
851 --
852 /*
853 Procedure: create_retro_entry
854 Description:
855 This procedure creates an entry in the
856 PAY_RETRO_ENTRIES table.
857 */
858 procedure create_retro_entry(
859 p_retro_assignment_id IN NUMBER
860 , p_element_entry_id IN NUMBER
861 , p_element_type_id IN NUMBER
862 , p_reprocess_date IN DATE
863 , p_eff_date IN DATE
864 , p_retro_component_id IN NUMBER
865 , p_owner_type IN VARCHAR2
866 , p_system_reprocess_date IN DATE) is
867 --
868 Begin
869 --
870 INSERT INTO pay_retro_entries
871 ( retro_assignment_id
872 , element_entry_id
873 , reprocess_date
874 , effective_date
875 , retro_component_id
876 , element_type_id
877 , owner_type
878 , system_reprocess_date
879 )
880 VALUES
881 ( p_retro_assignment_id
882 , p_element_entry_id
883 , p_reprocess_date
884 , p_eff_date
885 , p_retro_component_id
886 , p_element_type_id
887 , p_owner_type
888 , p_system_reprocess_date
889 );
890 --
891 end create_retro_entry;
892 --
893 /*
894 Procedure: maintain_retro_entry
895 Description:
896 This procedure creates and maintains an entry in the
897 PAY_RETRO_ENTRIES table.
898 */
899 procedure maintain_retro_entry
900 (
901 p_retro_assignment_id IN NUMBER
902 , p_element_entry_id IN NUMBER
903 , p_element_type_id IN NUMBER
904 , p_reprocess_date IN DATE
905 , p_eff_date IN DATE
906 , p_retro_component_id IN NUMBER
907 , p_owner_type IN VARCHAR2 default 'S' --System
908 , p_system_reprocess_date IN DATE default hr_api.g_eot)
909 is
910 l_min_reprocess_date date;
911 l_min_effective_date date;
912 l_min_sys_reprocess_date date;
913 l_owner_type varchar2(30);
914 l_retro_component_id pay_retro_entries.retro_component_id%type;
915 n_min_reprocess_date date;
916 n_min_effective_date date;
917 n_min_sys_reprocess_date date;
918 n_owner_type varchar2(30);
919 n_retro_component_id pay_retro_entries.retro_component_id%type;
920 --
921 begin
922 --
923 select reprocess_date,
924 effective_date,
925 owner_type,
926 nvl(system_reprocess_date, hr_api.g_eot),
927 retro_component_id
928 into l_min_reprocess_date,
929 l_min_effective_date,
930 l_owner_type,
931 l_min_sys_reprocess_date,
932 l_retro_component_id
933 from pay_retro_entries
934 where retro_assignment_id = p_retro_assignment_id
935 and element_entry_id = p_element_entry_id;
936 --
937 -- The reprocess and effective dates are always the least if a row exists
938 n_min_reprocess_date := least(l_min_reprocess_date, p_reprocess_date);
939 n_min_effective_date := least(l_min_effective_date, p_eff_date);
940 --
941 -- The system date is the least of 2 of old and new as long as not User owned
942 n_min_sys_reprocess_date := least(l_min_sys_reprocess_date,
943 p_system_reprocess_date);
944 --
945 -- Test for conditions, remembering we need to differentiate if the change
946 -- was user made, or system
947 -- ( Lookup RETRO_ENTRY_OWNER_TYPE )
948 --
949 -- If old and new owners were USER or both were SYSTEM then
950 -- we leave as unaltered, else owner is MERGED
951 if (l_owner_type = 'U' and p_owner_type = 'U')
952 or (l_owner_type = 'S' and p_owner_type = 'S') then
953 n_owner_type := l_owner_type;
954 else
955 n_owner_type := 'M';
956 end if;
957
958 if (p_retro_component_id is not null) then
959 n_retro_component_id := p_retro_component_id;
960 else
961 n_retro_component_id := l_retro_component_id;
962 end if;
963 --
964 -- Only perform update if need to
965 --
966 if (l_min_reprocess_date <> n_min_reprocess_date OR
967 l_min_effective_date <> n_min_effective_date OR
968 l_min_sys_reprocess_date <> n_min_sys_reprocess_date OR
969 l_owner_type <> n_owner_type OR
970 l_retro_component_id <> n_retro_component_id) then
971 update pay_retro_entries
972 set reprocess_date = n_min_reprocess_date,
973 effective_date = n_min_effective_date,
974 retro_component_id = n_retro_component_id,
975 owner_type = n_owner_type,
976 system_reprocess_date = n_min_sys_reprocess_date
977 where retro_assignment_id = p_retro_assignment_id
978 and element_entry_id = p_element_entry_id;
979 end if;
980 --
981 exception
982 --
983 when no_data_found then
984 --
985 -- No existing retro_entry exists for this entry_id
986 -- Thus create one with passed info.
987 --
988 -- If system owned then the system_reprocess date is simply this reprocess_date
989 if ( p_owner_type = 'S' ) then
990 l_min_sys_reprocess_date := p_reprocess_date;
991 else
992 l_min_sys_reprocess_date := p_system_reprocess_date;
993 end if;
994 --
995 create_retro_entry(
996 p_retro_assignment_id => p_retro_assignment_id,
997 p_element_entry_id => p_element_entry_id,
998 p_element_type_id => p_element_type_id,
999 p_reprocess_date => p_reprocess_date,
1000 p_eff_date => p_eff_date,
1001 p_retro_component_id => p_retro_component_id,
1002 p_owner_type => p_owner_type,
1003 p_system_reprocess_date => l_min_sys_reprocess_date);
1004 --
1005 end maintain_retro_entry;
1006 --
1007 /*
1008 Procedure: merge_retro_assignments
1009 Description:
1010 This procedure is used by the Rollback process to merge
1011 any outstanding Retro assignments with the existing
1012 retro assignment that is being rolled back.
1013 */
1014 procedure merge_retro_assignments(p_asg_act_id in number)
1015 is
1016 --
1017 cursor get_unproc(p_assignment_id in number)
1018 is
1019 select pra.retro_assignment_id,
1020 pre.element_entry_id,
1021 pre.element_type_id,
1022 pre.reprocess_date,
1023 pre.effective_date,
1024 pre.retro_component_id,
1025 pre.owner_type,
1026 pre.system_reprocess_date
1027 from pay_retro_assignments pra,
1028 pay_retro_entries pre
1029 where pra.assignment_id = p_assignment_id
1030 and pra.retro_assignment_action_id is null
1031 and pra.retro_assignment_id = pre.retro_assignment_id;
1032 --
1033 cursor get_ret_asg (p_asg_act_id in number)
1034 is
1035 select retro_assignment_id,
1036 assignment_id
1037 from pay_retro_assignments
1038 where retro_assignment_action_id = p_asg_act_id;
1039 --
1040 l_ret_asg_id pay_retro_assignments.retro_assignment_id%type;
1041 l_asg_id pay_retro_assignments.assignment_id%type;
1042 --
1043 begin
1044 --
1045 for retasgrec in get_ret_asg(p_asg_act_id) loop
1046 --
1047 for unprocrec in get_unproc(retasgrec.assignment_id) loop
1048 --
1049 -- Either update or insert rows to represent those that
1050 -- exist on our unproc RA, adding them to the rolled back RA
1051 maintain_retro_entry(retasgrec.retro_assignment_id,
1052 unprocrec.element_entry_id,
1053 unprocrec.element_type_id,
1054 unprocrec.reprocess_date,
1055 unprocrec.effective_date,
1056 unprocrec.retro_component_id,
1057 unprocrec.owner_type,
1058 unprocrec.system_reprocess_date
1059 );
1060 delete from pay_retro_entries
1061 where element_entry_id = unprocrec.element_entry_id
1062 and retro_assignment_id = unprocrec.retro_assignment_id;
1063 --
1064 end loop;
1065 --
1066 -- Remove the row that has now been replicated/merged
1067 delete from pay_retro_assignments
1068 where assignment_id = retasgrec.assignment_id
1069 and retro_assignment_action_id is null;
1070 --
1071 -- Also need to upd our rolled back RA, (done in calling pyrolbak.pkb)
1072 -- i) removing the retro_asg_act_id and
1073 -- ii) updating reprocess_date to be the new min of the child REs
1074 --
1075 --
1076 end loop;
1077 --
1078 end merge_retro_assignments;
1079 --
1080 function get_rr_source_id(p_rr_id in number)
1081 return number
1082 is
1083 l_source_id number;
1084 begin
1085 select source_id
1086 into l_source_id
1087 from pay_run_results
1088 where run_result_id=p_rr_id;
1089 --
1090 return l_source_id;
1091 --
1092 end get_rr_source_id;
1093 --
1094 function get_rr_source_type(p_rr_id in number)
1095 return varchar2
1096 is
1097 l_source_type varchar2(1);
1098 begin
1099 select source_type
1100 into l_source_type
1101 from pay_run_results
1102 where run_result_id=p_rr_id;
1103 --
1104 return l_source_type;
1105 --
1106 --
1107 end get_rr_source_type;
1108 --
1109 procedure generate_obj_grp_actions (p_pactid in number,
1110 p_chunk_number in number)
1111 is
1112 --
1113 cursor get_actions(p_pactid number,
1114 p_chunk_number number)
1115 is
1116 select paa.assignment_action_id,
1117 paa.object_id process_group_id,
1118 ppa.effective_date
1119 from pay_assignment_actions paa,
1120 pay_payroll_actions ppa
1121 where paa.payroll_action_id = p_pactid
1122 and ppa.payroll_action_id = p_pactid
1123 and paa.source_action_id is null
1124 and paa.chunk_number = p_chunk_number;
1125 --
1126 cursor get_asg(p_proc_grp_id number,
1127 p_eff_date date)
1128 is
1129 select distinct
1130 pog.source_id,
1131 hr_dynsql.get_tax_unit(pog.source_id,
1132 p_eff_date) tax_unit_id
1133 from pay_object_groups pog
1134 where pog.parent_object_group_id = p_proc_grp_id
1135 and pog.source_type = 'PAF'
1136 and p_eff_date between pog.start_date
1137 and pog.end_date;
1138 --
1139 --
1140 begin
1141 --
1142 for actrec in get_actions(p_pactid, p_chunk_number) loop
1143 --
1144 for asgrec in get_asg(actrec.process_group_id, actrec.effective_date) loop
1145 --
1146 insert into pay_assignment_actions (
1147 assignment_action_id,
1148 assignment_id,
1149 payroll_action_id,
1150 action_status,
1151 chunk_number,
1152 action_sequence,
1153 object_version_number,
1154 tax_unit_id,
1155 source_action_id,
1156 object_id,
1157 object_type
1158 )
1159 select pay_assignment_actions_s.nextval,
1160 asgrec.source_id,
1161 p_pactid,
1162 'U',
1163 p_chunk_number,
1164 pay_assignment_actions_s.nextval,
1165 1,
1166 asgrec.tax_unit_id,
1167 actrec.assignment_action_id,
1168 actrec.process_group_id,
1169 'POG'
1170 from dual;
1171 --
1172 update pay_retro_assignments
1173 set retro_assignment_action_id = actrec.assignment_action_id
1174 where assignment_id = asgrec.source_id
1175 and retro_assignment_action_id is null;
1176 --
1177 end loop;
1178 --
1179 -- Now update the master Sequence
1180 update pay_assignment_actions
1181 set action_sequence = pay_assignment_actions_s.nextval
1182 where assignment_action_id = actrec.assignment_action_id;
1183 --
1184 end loop;
1185 --
1186 end generate_obj_grp_actions;
1187 --
1188 function get_asg_from_pg_action(p_obj_grp_id in number,
1189 p_obj_type in varchar2,
1190 p_pactid in number)
1191 return number
1192 is
1193 l_assignment number;
1194 begin
1195 --
1196 select paa2.assignment_id
1197 into l_assignment
1198 from pay_assignment_actions paa2,
1199 per_all_assignments_f paf,
1200 pay_payroll_actions ppa
1201 where p_obj_grp_id = paa2.object_id
1202 and p_obj_type = paa2.object_type
1203 and p_pactid = paa2.payroll_action_id
1204 and ppa.payroll_action_id = p_pactid
1205 and paa2.assignment_id is not null
1206 and paa2.assignment_id = paf.assignment_id
1207 and ppa.effective_date between paf.effective_start_date
1208 and paf.effective_end_date
1209 and rownum = 1;
1210 --
1211 return l_assignment;
1212 --
1213 end get_asg_from_pg_action;
1214 --
1215 function get_entry_path( p_entry_process_path in varchar2,
1216 p_source_type in varchar2,
1217 p_element_type_id in number,
1218 p_run_result_id in number)
1219 return varchar2
1220 is
1221 l_entry_path varchar2(1000);
1222 n number;
1223 curr_et varchar2(30);
1224 curr_pos number;
1225 next_et varchar2(30);
1226 next_pos number;
1227 curr_epath varchar2(1000);
1228 last_element number;
1229 counter number;
1230 epath_length number;
1231 recursive_level number;
1232 l_src_type varchar2(1);
1233 --
1234 begin
1235 --
1236 curr_pos := 0;
1237 next_pos := 0;
1238 counter := 1;
1239 last_element := 0;
1240 recursive_level := 1;
1241 --
1242 if (p_run_result_id is not null) then
1243 select source_type
1244 into l_src_type
1245 from pay_run_results prr2
1246 where prr2.run_result_id = p_run_result_id;
1247 end if;
1248 --
1249 if ((p_entry_process_path is null) and (p_source_type in ('R', 'E'))) then
1250 l_entry_path := p_entry_process_path;
1251 elsif ((p_entry_process_path is null) and (p_source_type in ('V', 'I'))) then
1252 l_entry_path := to_char(p_element_type_id);
1253 elsif ((p_entry_process_path is null) and (p_run_result_id is not null)) then
1254 if (l_src_type = 'E') then l_entry_path := p_entry_process_path;
1255 else l_entry_path := to_char(p_element_type_id);
1256 end if;
1257 else /* p_entry_process_path is not null */
1258 --
1259 /* If there is a square bracket, we do not need to convert format */
1260 --
1261 n := instr(p_entry_process_path, '[');
1262 epath_length := length(p_entry_process_path);
1263 --
1264 if (n <> 0) then
1265 hr_utility.set_location('Entry Proc Path in correct format', 10);
1266 l_entry_path := p_entry_process_path;
1267 else
1268 hr_utility.set_location('Convert Entry Proc Path: ' || p_entry_process_path, 20);
1269 --
1270 /* find first element type */
1271 curr_pos := instr(p_entry_process_path, '.', 1,counter);
1272 curr_et := substr(p_entry_process_path, 1, curr_pos-1);
1273 --
1274 /* find second element type */
1275 counter := counter +1;
1276 next_pos := instr(p_entry_process_path, '.', 1, counter);
1277 --
1278 if (curr_pos = 0) then
1279 l_entry_path := p_entry_process_path;
1280 else
1281 while(last_element = 0) loop
1282 hr_utility.set_location('Entry Path: ' || curr_epath || 'Counter: ' || to_char(counter), 30);
1283 --
1284 if (next_pos = 0) then
1285 next_et := substr(p_entry_process_path, curr_pos+1, epath_length-curr_pos);
1286 else
1287 next_et := substr(p_entry_process_path, curr_pos+1, next_pos-1-curr_pos);
1288 end if;
1289 --
1290 if (curr_et = next_et) then
1291 hr_utility.set_location('If Same Element Type', 40);
1292 while ((curr_et = next_et) and (last_element = 0)) loop
1293 hr_utility.set_location('While Same Element Type: ' || curr_et || ' ' || next_et, 50);
1294 hr_utility.set_location('Curr Pos and Next Pos: ' || to_char(curr_pos) || ' ' || to_char(next_pos), 55);
1295 recursive_level := recursive_level+1;
1296 --
1297 hr_utility.set_location('Recursive Level: ' || to_char(recursive_level), 56);
1298 if (next_pos = 0) then
1299 last_element := 1;
1300 next_et := substr(p_entry_process_path, curr_pos+1, epath_length-curr_pos);
1301 hr_utility.set_location('Next ET: ' || next_et || 'Curr Pos: ' || curr_pos, 81);
1302 else
1303 curr_pos := next_pos;
1304 counter := counter +1;
1305 next_pos := instr(p_entry_process_path, '.', 1, counter);
1306 if (next_pos = 0) then
1307 next_et := substr(p_entry_process_path, curr_pos+1, epath_length-curr_pos);
1308 else
1309 next_et := substr(p_entry_process_path, curr_pos+1, next_pos-1-curr_pos);
1310 end if;
1311 hr_utility.set_location('Next ET: ' || next_et, 82);
1312 end if;
1313 end loop;
1314 --
1315 hr_utility.set_location('Entry Path: ' || curr_epath, 100);
1316 --
1317 if (curr_epath is null) then
1318 curr_epath := curr_et || '[' || to_char(recursive_level) || ']';
1319 else
1320 curr_epath := curr_epath || '[' || to_char(recursive_level) || ']';
1321 end if;
1322
1323 if ((last_element = 1) and (curr_et <> next_et)) then
1324 curr_epath := curr_epath || '.' || next_et;
1325 end if;
1326 hr_utility.set_location('Entry Path: ' || curr_epath, 200);
1327 --
1328 else
1329 hr_utility.set_location('If Not Same Element Type: ' || curr_et || ' ' || next_et, 60);
1330 recursive_level := 1;
1331 --
1332 hr_utility.set_location('Entry Path: ' || curr_epath, 300);
1333 --
1334 if (curr_epath is null) then
1335 curr_epath := curr_et || '.' || next_et;
1336 else
1337 curr_epath := curr_epath || '.' || next_et;
1338 end if;
1339
1340 curr_et := next_et;
1341 if (next_pos <> 0) then
1342 curr_pos := next_pos;
1343 counter := counter + 1;
1344 next_pos := instr(p_entry_process_path, '.', 1, counter);
1345 else
1346 last_element := 1;
1347 end if;
1348 --
1349 hr_utility.set_location('Entry Path: ' || curr_epath, 400);
1350 --
1351 end if;
1352 end loop;
1353 l_entry_path := curr_epath;
1354 end if;
1355 end if;
1356 end if;
1357 --
1358 return l_entry_path;
1359 --
1360 end get_entry_path;
1361 --
1362 function get_entry_path( p_run_result_id in number)
1363 return varchar2
1364 is
1365 l_entry_process_path pay_run_results.entry_process_path%type;
1366 l_source_type pay_run_results.source_type%type;
1367 l_element_type_id pay_run_results.element_type_id%type;
1368 begin
1369 --
1370 select entry_process_path,
1371 source_type,
1372 element_type_id
1373 into l_entry_process_path,
1374 l_source_type,
1375 l_element_type_id
1376 from pay_run_results
1377 where run_result_id = p_run_result_id;
1378 --
1379 return get_entry_path(p_entry_process_path => l_entry_process_path,
1380 p_source_type => l_source_type,
1381 p_element_type_id => l_element_type_id,
1382 p_run_result_id => null);
1383 end get_entry_path;
1384 --
1385 /*
1386 get_retro_asg_id
1387 Description
1388 This function is used by the retropay process to
1389 find get a retro assignment to be processed by an assignment action
1390 */
1391 function get_retro_asg_id(p_assignment_action in number)
1392 return number
1393 is
1394 l_ret_asg number;
1395 begin
1396 --
1397 select retro_assignment_id
1398 into l_ret_asg
1399 from pay_retro_assignments
1400 where retro_assignment_action_id = p_assignment_action
1401 and rownum = 1;
1402 --
1403 return l_ret_asg;
1404 --
1405 exception
1406 when no_data_found then
1407 return null;
1408 --
1409 end get_retro_asg_id;
1410 --
1411 procedure overlap_adjustments(p_asg_act_id in number,
1412 p_definition_id in number,
1413 p_component_id in number,
1414 p_ele_set_id in number
1415 )
1416 is
1417 cursor get_overlaps (p_asg_act_id in number,
1418 p_definition_id in number,
1419 p_component_id in number,
1420 p_ele_set_id in number
1421 )
1422 is
1423 SELECT /*+ INDEX(piv pay_input_values_f_pk)
1424 INDEX(pet pay_element_types_f_pk)
1425 USE_NL(piv pet) */
1426 pee.element_entry_id,
1427 pee.source_start_date,
1428 pee.source_end_date,
1429 piv1.input_value_id,
1430 peev.screen_entry_value,
1431 piv.mandatory_flag,
1432 pet1.element_type_id,
1433 pepd.source_entry_id,
1434 pepd.run_result_id,
1435 pepd.tax_unit_id,
1436 pepd.time_definition_id,
1437 pee.source_run_type,
1438 pee.assignment_id
1439 FROM pay_element_entries_f pee,
1440 pay_input_values_f piv,
1441 pay_element_entry_values_f peev,
1442 pay_element_types_f pet,
1443 pay_element_types_f pet1,
1444 pay_input_values_f piv1,
1445 pay_entry_process_details pepd,
1446 pay_retro_components prc,
1447 pay_retro_defn_components prdc2,
1448 pay_retro_defn_components prdc
1449 WHERE pet1.element_type_id = pepd.source_element_type_id
1450 and piv1.element_type_id = pet1.element_type_id
1451 and pepd.source_asg_action_id = p_asg_act_id
1452 AND pee.element_entry_id = peev.element_entry_id
1453 AND peev.input_value_id = piv.input_value_id
1454 AND piv.name = piv1.NAME
1455 AND piv.uom NOT IN ('D','T','C')
1456 AND pee.element_type_id = pet.element_type_id
1457 AND pee.effective_end_date between peev.effective_start_date AND
1458 peev.effective_end_date
1459 AND pee.effective_end_date between piv.effective_start_date AND
1460 piv.effective_end_date
1461 AND pee.effective_end_date between pet.effective_start_date AND
1462 pet.effective_end_date
1463 AND pee.effective_end_date between pet1.effective_start_date AND
1464 pet1.effective_end_date
1465 AND pee.effective_end_date between piv1.effective_start_date AND
1466 piv1.effective_end_date
1467 AND pepd.element_entry_id = pee.element_entry_id
1468 AND pepd.retro_component_id = prc.retro_component_id (+)
1469 AND prc.retro_component_id = prdc.retro_component_id (+)
1470 AND prdc.retro_definition_id (+) = p_definition_id
1471 AND prdc2.retro_component_id (+) = p_component_id
1472 AND prdc2.retro_definition_id (+) = p_definition_id
1473 AND nvl(prdc.priority, 99) <= nvl(prdc2.priority, 99)
1474 AND ( prc.recalculation_style is null
1475 OR
1476 ( prc.recalculation_style <> 'R'
1477 OR
1478 /* Replacement overlap entries, bring back all PR OR
1479 * fetch NR with no matching PR, i.e. deleted entries.
1480 */
1481 ( prc.recalculation_style = 'R'
1482 AND
1483 ( (pee.creator_type = 'PR'
1484 AND pay_retro_pkg.latest_replace_ovl_ee (pee.element_entry_id) = 'Y'
1485 )
1486 OR
1487 (pee.creator_type = 'NR'
1488 AND pay_retro_pkg.latest_replace_ovl_del_ee (pee.element_entry_id) = 'Y'
1489 )
1490 )
1491 )
1492 )
1493 )
1494 AND ( p_ele_set_id = 0
1495 or (p_ele_set_id <> 0
1496 and EXISTS
1497 (
1498 SELECT NULL
1499 FROM pay_ele_classification_rules ECR
1500 WHERE ECR.element_set_id = p_ele_set_id
1501 AND pet1.classification_id = ECR.classification_id
1502 AND NOT EXISTS
1503 (
1504 SELECT NULL
1505 FROM pay_element_type_rules ETR
1506 WHERE ETR.element_set_id = p_ele_set_id
1507 AND ETR.element_type_id = pet1.element_type_id
1508 AND ETR.include_or_exclude = 'E'
1509 )
1510 UNION
1511 SELECT NULL
1512 FROM pay_element_type_rules ETR
1513 WHERE ETR.element_set_id = p_ele_set_id
1514 AND ETR.element_type_id = pet1.element_type_id
1515 AND ETR.include_or_exclude = 'I'
1516 )
1517 )
1518 )
1519 ORDER by pepd.tax_unit_id,
1520 pee.source_run_type,
1521 pee.element_entry_id,
1522 piv.input_value_id;
1523
1524 ----
1525 cursor get_proc_retro_rrv IS -- Added for 7335351
1526 SELECT /*+ INDEX(piv pay_input_values_f_pk)
1527 INDEX(pet pay_element_types_f_pk)
1528 USE_NL(piv pet) */
1529 pee.element_entry_id,
1530 pee.source_start_date,
1531 pee.source_end_date,
1532 piv1.input_value_id,
1533 peev.screen_entry_value,
1534 pet1.element_type_id,
1535 pepd.source_entry_id,
1536 pepd.run_result_id,
1537 pepd.tax_unit_id,
1538 pepd.time_definition_id,
1539 pee.source_run_type,
1540 pee.assignment_id
1541 FROM pay_element_entries_f pee,
1542 pay_input_values_f piv,
1543 pay_element_entry_values_f peev,
1544 pay_element_types_f pet,
1545 pay_element_types_f pet1,
1546 pay_input_values_f piv1,
1547 pay_run_results prr,
1548 pay_entry_process_details pepd,
1549 pay_assignment_actions paa,
1550 pay_payroll_actions ppa,
1551 pay_retro_components prc,
1552 pay_retro_defn_components prdc2,
1553 pay_retro_defn_components prdc
1554 where paa.assignment_action_id = p_asg_act_id
1555 and paa.payroll_action_id = ppa.payroll_action_id
1556 and paa.assignment_id = pee.assignment_id
1557 and ppa.date_earned between pee.effective_start_date and pee.effective_end_date
1558 and pee.element_entry_id = pepd.element_entry_id
1559 and pet1.element_type_id = pepd.source_element_type_id
1560 and piv1.element_type_id = pet1.element_type_id
1561 AND pee.element_entry_id = peev.element_entry_id
1562 AND peev.input_value_id = piv.input_value_id
1563 AND piv.name = piv1.NAME
1564 AND piv.uom NOT IN ('D','T','C')
1565 AND pee.element_type_id = pet.element_type_id
1566 and prr.element_type_id = pee.element_type_id
1567 and prr.source_id = pee.element_entry_id
1568 AND pee.effective_end_date between peev.effective_start_date AND
1569 peev.effective_end_date
1570 AND pee.effective_end_date between piv.effective_start_date AND
1571 piv.effective_end_date
1572 AND pee.effective_end_date between pet.effective_start_date AND
1573 pet.effective_end_date
1574 AND pee.effective_end_date between pet1.effective_start_date AND
1575 pet1.effective_end_date
1576 AND pee.effective_end_date between piv1.effective_start_date AND
1577 piv1.effective_end_date
1578 AND pepd.retro_component_id = prc.retro_component_id (+)
1579 AND prc.retro_component_id = prdc.retro_component_id (+)
1580 AND prdc.retro_definition_id (+) = p_definition_id
1581 AND prdc2.retro_component_id (+) = p_component_id
1582 AND prdc2.retro_definition_id (+) = p_definition_id
1583 AND nvl(prdc.priority, 99) <= nvl(prdc2.priority, 99);
1584 --
1585 l_pactid pay_payroll_actions.payroll_action_id%type;
1586 l_business_group_id pay_payroll_actions.business_group_id%type;
1587 l_consolidation_set_id pay_payroll_actions.consolidation_set_id%type;
1588 l_payroll_id pay_payroll_actions.payroll_id%type;
1589 l_effective_date pay_payroll_actions.effective_date%type;
1590 l_date_earned pay_payroll_actions.date_earned%type;
1591 l_time_period_id pay_payroll_actions.time_period_id%type;
1592 legcode per_business_groups.legislation_code%type;
1593 l_jc_name varchar2(40);
1594 l_rule_mode varchar2(40);
1595 l_status varchar2(40);
1596 l_rr_sparse_jc boolean;
1597 l_rr_sparse boolean;
1598 l_found boolean;
1599 l_ee_id number;
1600 l_run_type number;
1601 l_tax_unit_id number;
1602 l_asg_act_id number;
1603 l_rr_id number;
1604 l_screen_entry_value pay_element_entry_values_f.screen_entry_value%TYPE;
1605 --
1606 begin
1607
1608 select pay_payroll_actions_s.nextval,
1609 ppa.business_group_id,
1610 ppa.consolidation_set_id,
1611 ppa.payroll_id,
1612 ppa.effective_date,
1613 ppa.date_earned,
1614 ppa.time_period_id,
1615 pbg.legislation_code
1616 into l_pactid,
1617 l_business_group_id,
1618 l_consolidation_set_id,
1619 l_payroll_id,
1620 l_effective_date,
1621 l_date_earned,
1622 l_time_period_id,
1623 legcode
1624 from pay_payroll_actions ppa,
1625 pay_assignment_actions paa,
1626 per_business_groups pbg
1627 where ppa.payroll_action_id = paa.payroll_action_id
1628 and pbg.business_group_id = ppa.business_group_id
1629 and paa.assignment_action_id = p_asg_act_id;
1630 --
1631 insert into pay_payroll_actions(
1632 payroll_action_id,
1633 action_type,
1634 business_group_id,
1635 consolidation_set_id,
1636 payroll_id,
1637 action_population_status,
1638 action_status,
1639 effective_date,
1640 date_earned,
1641 time_period_id,
1642 object_version_number)
1643 values (
1644 l_pactid,
1645 'B',
1646 l_business_group_id,
1647 l_consolidation_set_id,
1648 l_payroll_id,
1649 'C',
1650 'C',
1651 l_effective_date,
1652 l_date_earned,
1653 l_time_period_id,
1654 1);
1655 --
1656 -- calc jur code name
1657 pay_core_utils.get_leg_context_iv_name
1658 ('JURISDICTION_CODE',
1659 legcode,
1660 l_jc_name,
1661 l_found);
1662
1663 if (l_found = FALSE) then
1664 l_jc_name := 'Jurisdiction';
1665 end if;
1666
1667
1668 -- set rr sparse leg_rule
1669 pay_core_utils.get_legislation_rule('RR_SPARSE',
1670 legcode,
1671 l_rule_mode,
1672 l_found
1673 );
1674 if (l_found = FALSE) then
1675 l_rule_mode := 'N';
1676 end if;
1677
1678 if upper(l_rule_mode)='Y'
1679 then
1680 -- Confirm Enabling Upgrade has been made by customer
1681 pay_core_utils.get_upgrade_status(l_business_group_id,
1682 'ENABLE_RR_SPARSE',
1683 l_status);
1684
1685 if upper(l_status)='N'
1686 then
1687 l_rule_mode := 'N';
1688 end if;
1689 end if;
1690
1691 if upper(l_rule_mode)='Y'
1692 then
1693 l_rr_sparse:=TRUE;
1694 else
1695 l_rr_sparse :=FALSE;
1696 end if;
1697 --
1698 pay_core_utils.get_upgrade_status(l_business_group_id,
1699 'RR_SPARSE_JC',
1700 l_status);
1701 --
1702 if upper(l_status)='Y'
1703 then
1704 l_rr_sparse_jc :=TRUE;
1705 else
1706 l_rr_sparse_jc :=FALSE;
1707 end if;
1708
1709
1710 --
1711 l_ee_id := -1;
1712 l_run_type := -1;
1713 l_tax_unit_id := -1;
1714 l_asg_act_id := -1;
1715
1716 for eerec in get_overlaps(p_asg_act_id,
1717 p_definition_id,
1718 p_component_id,
1719 p_ele_set_id
1720 ) loop
1721 if ( l_run_type <> eerec.source_run_type
1722 or l_tax_unit_id <> eerec.tax_unit_id) then
1723 --
1724 if (l_asg_act_id <> -1) then
1725 pay_balance_pkg.maintain_balances_for_action(l_asg_act_id);
1726 end if;
1727 --
1728 l_asg_act_id := hrassact.inassact_main
1729 (
1730 pactid => l_pactid,
1731 asgid => eerec.assignment_id,
1732 taxunt => eerec.tax_unit_id,
1733 p_run_type_id => eerec.source_run_type,
1734 p_mode => 'BACKPAY'
1735 );
1736 --
1737 l_run_type := eerec.source_run_type;
1738 l_tax_unit_id := eerec.tax_unit_id;
1739 --
1740 end if;
1741 if (l_ee_id <> eerec.element_entry_id) then
1742 --
1743 l_rr_id := pay_run_result_pkg.create_run_result_direct
1744 (p_element_type_id => eerec.element_type_id,
1745 p_assignment_action_id => l_asg_act_id,
1746 p_entry_type => 'B',
1747 p_source_id => eerec.source_entry_id,
1748 p_source_type => 'E',
1749 p_status => 'P',
1750 p_local_unit_id => null,
1751 p_start_date => eerec.source_start_date,
1752 p_end_date => eerec.source_end_date,
1753 p_element_entry_id => eerec.source_entry_id,
1754 p_time_def_id => eerec.time_definition_id
1755 );
1756 l_ee_id := eerec.element_entry_id;
1757 --
1758 end if;
1759
1760 pay_run_result_pkg.maintain_rr_value(p_run_result_id => l_rr_id,
1761 p_session_date => l_effective_date,
1762 p_input_value_id => eerec.input_value_id,
1763 p_value => eerec.screen_entry_value,
1764 p_formula_result_flag => 'N',
1765 p_jc_name => l_jc_name,
1766 p_rr_sparse => l_rr_sparse,
1767 p_rr_sparse_jc => l_rr_sparse_jc,
1768 p_mode => null
1769 );
1770 --
1771 end loop;
1772 --
1773 /* Added for 7335351. Check if the period of balance adjustment already has run results of retro entries, those entries would already
1774 have been added to the balance values during the balance adjustments of the previous(source) periods. To maintain the consistency
1775 in balance values, create run_results of type 'B' with negative values of such retro run result values.
1776 */
1777 for eerec in get_proc_retro_rrv
1778 loop
1779 if ( l_run_type <> eerec.source_run_type
1780 or l_tax_unit_id <> eerec.tax_unit_id) then
1781 --
1782 if (l_asg_act_id <> -1) then
1783 pay_balance_pkg.maintain_balances_for_action(l_asg_act_id);
1784 end if;
1785 --
1786 l_asg_act_id := hrassact.inassact_main
1787 (
1788 pactid => l_pactid,
1789 asgid => eerec.assignment_id,
1790 taxunt => eerec.tax_unit_id,
1791 p_run_type_id => eerec.source_run_type,
1792 p_mode => 'BACKPAY'
1793 );
1794 --
1795 l_run_type := eerec.source_run_type;
1796 l_tax_unit_id := eerec.tax_unit_id;
1797 --
1798 end if;
1799 if (l_ee_id <> eerec.element_entry_id) then
1800 --
1801 l_rr_id := pay_run_result_pkg.create_run_result_direct
1802 (p_element_type_id => eerec.element_type_id,
1803 p_assignment_action_id => l_asg_act_id,
1804 p_entry_type => 'B',
1805 p_source_id => eerec.source_entry_id,
1806 p_source_type => 'E',
1807 p_status => 'P',
1808 p_local_unit_id => null,
1809 p_start_date => eerec.source_start_date,
1810 p_end_date => eerec.source_end_date,
1811 p_element_entry_id => eerec.source_entry_id,
1812 p_time_def_id => eerec.time_definition_id
1813 );
1814 l_ee_id := eerec.element_entry_id;
1815 --
1816 end if;
1817
1818 l_screen_entry_value := -fnd_number.canonical_to_number(eerec.screen_entry_value);
1819
1820 pay_run_result_pkg.maintain_rr_value(p_run_result_id => l_rr_id,
1821 p_session_date => l_effective_date,
1822 p_input_value_id => eerec.input_value_id,
1823 p_value => fnd_number.number_to_canonical(l_screen_entry_value),
1824 p_formula_result_flag => 'N',
1825 p_jc_name => l_jc_name,
1826 p_rr_sparse => l_rr_sparse,
1827 p_rr_sparse_jc => l_rr_sparse_jc,
1828 p_mode => null
1829 );
1830 --
1831 end loop;
1832 --
1833 if (l_asg_act_id <> -1) then
1834 pay_balance_pkg.maintain_balances_for_action(l_asg_act_id);
1835 end if;
1836 --
1837 end overlap_adjustments;
1838 --
1839 -- Note in process_recorded_date, we're using the serial_number column to
1840 -- store the recalculation date used for the assignment in the retropay run.
1841 -- Modified for bugs 7248998, 7335351
1842 function process_recorded_date (p_process in varchar2,
1843 p_assignment_id in varchar2,
1844 p_adj_start_date in date,
1845 p_assact_id in number)
1846 return date
1847 is
1848 l_rec_date date;
1849 v_recorded_date date;
1850 l_date date;
1851 l_min_retro_asg_date date;
1852 begin
1853 hr_utility.set_location('process_recorded_date', 10);
1854
1855 -- p_adj_start_date is the earliest overlap_start_date for the assignment in this retropay run
1856 hr_utility.trace('p_adj_start_date : '|| p_adj_start_date);
1857
1858 -- Get the recorded_date for 'RETRO_OVERLAP' attribute for the assignment
1859 pay_recorded_requests_pkg.get_recorded_date_no_ins( p_process,
1860 l_rec_date,
1861 p_assignment_id);
1862 --
1863 hr_utility.trace('l_rec_date : '|| l_rec_date);
1864 --
1865 /*
1866 If retropay is being run for the first time since enabling RETRO_OVERLAP functionality or
1867 if the earliest overlapping_start_date is less than the recorded_date, then do full recalculations
1868 from the earliest overlapping_start_date.
1869 Otherwise, Balance Adjustments will be used till the reprocess_date and recalculations can be done from the reprocess_date.
1870 v_recorded_date is the date from which full recalculations are done. This is stored in serial_number column of
1871 pay_assignment_actions and can be queried after the retropay run to verify the recalculation_date used by the process.
1872 */
1873 if (l_rec_date = hr_api.g_sot OR
1874 p_adj_start_date < l_rec_date) THEN
1875 --
1876 hr_utility.set_location('process_recorded_date', 20);
1877
1878 v_recorded_date := p_adj_start_date;
1879 --
1880 pay_recorded_requests_pkg.set_recorded_date(
1881 p_process => p_process,
1882 p_recorded_date => p_adj_start_date,
1883 p_recorded_date_o => l_date,
1884 p_attribute1 => p_assignment_id);
1885 --
1886 else
1887 --
1888 hr_utility.set_location('process_recorded_date', 30);
1889
1890 -- Get the reprocess_date of the assignment for this retropay run.
1891
1892 begin
1893 select reprocess_date into l_min_retro_asg_date
1894 from pay_retro_assignments
1895 where assignment_id =p_assignment_id
1896 and retro_assignment_action_id = p_assact_id;
1897
1898 exception
1899 when others
1900 then null;
1901 end;
1902
1903 hr_utility.trace('l_min_retro_asg_date : '|| l_min_retro_asg_date);
1904
1905 v_recorded_date := l_min_retro_asg_date;
1906 --
1907 end if;
1908 --
1909 update pay_assignment_actions
1910 set serial_number = substr(fnd_date.date_to_canonical(v_recorded_date),1,11)
1911 where assignment_action_id = p_assact_id;
1912 --
1913 hr_utility.set_location('process_recorded_date', 40);
1914 --
1915 return v_recorded_date;
1916 --
1917 end process_recorded_date;
1918 --
1919 --
1920 procedure reset_recorded_request(p_assact_id in number) is
1921 l_prev_rec_date date;
1922 l_assign_id number;
1923 begin
1924 --
1925 hr_utility.set_location('reset_recorded_request', 10);
1926 --
1927 select to_date(substr(serial_number, 1,11), 'YYYY/MM/DD'), assignment_id
1928 into l_prev_rec_date, l_assign_id
1929 from pay_assignment_actions
1930 where assignment_action_id = p_assact_id;
1931 --
1932 hr_utility.set_location('reset_recorded_request', 20);
1933 --
1934
1935 /* Added to_char for l_assign_id in the following
1936 two queries for fixing Bug:6893208 */
1937 if (l_prev_rec_date is null)
1938 then
1939 delete from pay_recorded_requests
1940 where ATTRIBUTE_CATEGORY = 'RETRO_OVERLAP'
1941 and ATTRIBUTE1 =to_char(l_assign_id);
1942 else
1943 update pay_recorded_requests
1944 set RECORDED_DATE = l_prev_rec_date
1945 where ATTRIBUTE_CATEGORY = 'RETRO_OVERLAP'
1946 and ATTRIBUTE1 = to_char(l_assign_id);
1947 --
1948 end if;
1949 hr_utility.set_location('reset_recorded_request', 30);
1950 --
1951 end reset_recorded_request;
1952 --
1953 end pay_retro_pkg;
[Home] [Help]