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