1 package body pay_asg_process_grp_pkg as
2 /* $Header: pycorapg.pkb 120.11.12020000.3 2012/10/05 15:44:44 jkvallab ship $ */
3 /* Name
4 is_pog_enable
5 Description
6
7 This function is used to indicate whether the POG functionality
8 is enabled for a localisation.
9 */
10 g_debug boolean := hr_utility.debug_enabled;
11 function is_pog_enable(p_bgp_id in number)
12 return boolean
13 is
14 --
15 l_leg_code per_business_groups.legislation_code%type;
16 l_value pay_legislation_rules.rule_mode%type;
17 l_found boolean;
18 --
19 begin
20 --
21 if g_debug then
22 hr_utility.trace('In is_pog_enable()');
23 end if;
24 select pbg.legislation_code
25 into l_leg_code
26 from per_business_groups_perf pbg
27 where pbg.business_group_id = p_bgp_id;
28 --
29 pay_core_utils.get_legislation_rule
30 (p_legrul_name => 'I',
31 p_legislation => l_leg_code,
32 p_legrul_value => l_value,
33 p_found => l_found);
34 --
35 if (l_found = FALSE) then
36 l_value := 'N';
37 end if;
38 --
39 if (l_value <> 'G') then
40 return FALSE;
41 else
42 return TRUE;
43 end if;
44 --
45 if g_debug then
46 hr_utility.trace('Out is_pog_enable()');
47 end if;
48 end is_pog_enable;
49 --
50 /*
51 Name
52 get_group_definition
53 Description
54
55 This function is used to retrieve/Create a group definition.
56 */
57 function get_group_definition(p_definition_name in varchar2)
58 return number
59 is
60 l_definition_id number;
61 begin
62 --
63 if g_debug then
64 hr_utility.trace('In get_group_definition()');
65 end if;
66 select group_definition_id
67 into l_definition_id
68 from pay_group_definitions
69 where name = p_definition_name;
70 --
71 return l_definition_id;
72 --
73 exception
74 when no_data_found then
75 begin
76 --
77 -- Need to lock the table to esnure only
78 -- one thread creates the group row.
79 --
80 lock table pay_group_definitions in share mode;
81 --
82 -- Ensure that nothing has created
83 -- the row in the meantime
84 select group_definition_id
85 into l_definition_id
86 from pay_group_definitions
87 where name = p_definition_name;
88 --
89 return l_definition_id;
90 --
91 exception
92 when no_data_found then
93 --
94 select pay_group_definitions_s.nextval
95 into l_definition_id
96 from dual;
97 --
98 insert into pay_group_definitions
99 (group_definition_id,
100 name)
101 values
102 (l_definition_id,
103 p_definition_name);
104 --
105 return l_definition_id;
106 end;
107 --
108 if g_debug then
109 hr_utility.trace('Out get_group_definition()');
110 end if;
111 end get_group_definition;
112 --
113 /*
114 Name
115 get_assignment_group
116 Description
117
118 This function is used to retrieve/Create an assignment group
119 */
120 function get_assignment_group(p_person_group_id in number,
121 p_assignment_id in number,
122 p_effective_start_date in date,
123 p_effective_end_date in date,
124 p_payroll_id in number,
125 p_update_row in varchar2 default 'Y'
126 )
127 return number
128 is
129 l_object_group_id pay_object_groups.object_group_id%type;
130 l_start_date pay_object_groups.start_date%type;
131 l_end_date pay_object_groups.end_date%type;
132 l_cnt number;
133 begin
134 --
135 if g_debug then
136 hr_utility.trace('In get_assignment_group()');
137 hr_utility.trace('p_person_group_id ' || p_person_group_id );
138 hr_utility.trace('p_assignment_id ' || p_assignment_id );
139 hr_utility.trace('p_effective_start_date ' || p_effective_start_date );
140 hr_utility.trace('p_effective_end_date ' || p_effective_end_date );
141 hr_utility.trace('p_payroll_id ' || p_payroll_id );
142 hr_utility.trace('p_update_row ' || p_update_row );
143 end if;
144
145 --Bug 14585781 case c
146 --changed the start/end date boundary condition check
147 select object_group_id,
148 start_date,
149 end_date
150 into l_object_group_id,
151 l_start_date,
152 l_end_date
153 from pay_object_groups
154 where source_id = p_assignment_id
155 and source_type = 'PAF'
156 and start_date = p_effective_start_date
157 and end_date = p_effective_end_date
158 and parent_object_group_id = p_person_group_id
159 and payroll_id = p_payroll_id;
160 --
161 -- changed the date joins as this can return more than one rows under certain condition,
162 -- where it shouldn't return those rows
163
164 return l_object_group_id;
165 --
166 exception
167 when no_data_found then
168 --
169 -- An assignment can not switch
170 -- from one processing group to another
171 --
172 select count(*)
173 into l_cnt
174 from pay_object_groups
175 where source_id = p_assignment_id
176 and source_type = 'PAF'
177 and parent_object_group_id <> p_person_group_id;
178 --
179 if (l_cnt <> 0) then
180 pay_core_utils.assert_condition('pay_asg_process_grp_pkg.get_assignment_group:1',
181 1 = 2);
182 end if;
183 --
184 if (p_update_row = 'Y') then
185 --
186 select pay_object_groups_s.nextval
187 into l_object_group_id
188 from dual;
189 --
190 if g_debug then
191 hr_utility.trace('Inserting into POG with ');
192 hr_utility.trace('Object_group_id ' || l_object_group_id );
193 hr_utility.trace('p_effective_start_date ' || p_effective_start_date );
194 hr_utility.trace('p_effective_start_date ' || p_effective_end_date );
195 end if;
196 insert into pay_object_groups
197 (object_group_id,
198 source_id,
199 source_type,
200 start_date,
201 end_date,
202 payroll_id,
203 parent_object_group_id)
204 values
205 (l_object_group_id,
206 p_assignment_id,
207 'PAF',
208 p_effective_start_date,
209 p_effective_end_date, -- Bug 14585781 Case A
210 p_payroll_id,
211 p_person_group_id
212 );
213 --
214 else
215 --
216 l_object_group_id := null;
217 --
218 end if;
219 --
220 return l_object_group_id;
221 --
222
223 --
224 if g_debug then
225 hr_utility.trace('Out get_assignment_group()');
226 end if;
227 end get_assignment_group;
228 --
229 /*
230 Name
231 get_person_group
232 Description
233
234 This function is used to retrieve/Create a person group.
235 */
236
237 function get_person_group(p_person_id in number,
238 p_period_of_service_id in number,
239 p_effective_start_date in date,
240 p_effective_end_date in date,
241 p_definition_name in varchar2,
242 p_update_row in varchar2 default 'Y'
243 )
244 return number
245 is
246 l_definition_id pay_group_definitions.group_definition_id%type;
247 l_object_group_id pay_object_groups.object_group_id%type;
248 l_start_date pay_object_groups.start_date%type;
249 l_end_date pay_object_groups.end_date%type;
250 l_update boolean;
251 l_upd_start_date date;
252 l_upd_end_date date;
253 begin
254 --
255 if g_debug then
256 hr_utility.trace('In get_person_group()');
257 end if;
258 l_definition_id := get_group_definition(p_definition_name);
259 --
260 select object_group_id,
261 start_date,
262 end_date
263 into l_object_group_id,
264 l_start_date,
265 l_end_date
266 from pay_object_groups
267 where source_id = p_person_id
268 and source_type = 'PPF'
269 -- bug 9354495
270 -- and start_date <= p_effective_end_date
271 -- and end_date >= p_effective_start_date
272 and period_of_service_id = p_period_of_service_id
273 and group_definition_id = l_definition_id;
274 --
275 -- Ensure that the object group dates
276 -- are correct
277 --
278 if g_debug then
279 hr_utility.trace('p_update_row = '|| p_update_row);
280 hr_utility.trace('l_start_date = '|| l_start_date);
281 hr_utility.trace('p_effective_start_date = '|| p_effective_start_date);
282 hr_utility.trace('l_end_date = '|| l_end_date);
283 hr_utility.trace(' p_effective_end_date = '|| p_effective_end_date);
284 end if;
285 if (p_update_row = 'Y') then
286 if (l_start_date > p_effective_start_date) then
287 l_update := TRUE;
288 l_upd_start_date := p_effective_start_date;
289 else
290 l_upd_start_date := l_start_date;
291 end if;
292 if (l_end_date < p_effective_end_date) then
293 l_upd_end_date := p_effective_end_date;
294 else
295 l_update := TRUE;
296 l_upd_end_date := l_end_date;
297 end if;
298 if (l_update = TRUE) then
299 if g_debug then
300 hr_utility.trace('object group id = '|| l_object_group_id);
301 hr_utility.trace('start date = '|| l_upd_start_date);
302 hr_utility.trace('end date = '|| l_upd_end_date);
303 end if;
304 update pay_object_groups
305 set start_date = l_upd_start_date,
306 end_date = l_upd_end_date
307 where object_group_id = l_object_group_id;
308 end if;
309 end if;
310 --
311 return l_object_group_id;
312 --
313 exception
314 when no_data_found then
315 --
316 if (p_update_row = 'Y') then
317 --
318 select pay_object_groups_s.nextval
319 into l_object_group_id
320 from dual;
321 --
322 insert into pay_object_groups
323 (object_group_id,
324 source_id,
325 source_type,
326 start_date,
327 end_date,
328 group_definition_id,
329 period_of_service_id)
330 values
331 (l_object_group_id,
332 p_person_id,
333 'PPF',
334 p_effective_start_date,
335 to_date('4712/12/31', 'YYYY/MM/DD'),
336 l_definition_id,
337 p_period_of_service_id
338 );
339 --
340 else
341 --
342 l_object_group_id := null;
343 --
344 end if;
345 --
346 return l_object_group_id;
347 --
348
349 --
350 if g_debug then
351 hr_utility.trace('Out get_person_group()');
352 end if;
353 end get_person_group;
354 --
355 /*
356 Name
357 evaluate_asg_group
358 Description
359
360 This procedure calls the legislative function to retrieve the
361 process group name.
362 */
363
364 procedure evaluate_asg_group(p_assignment_id in number,
365 p_effective_start_date in date,
366 p_effective_end_date in date,
367 p_group_name out nocopy varchar2)
368 is
369 l_leg_code varchar2(30);
370 l_bg_id number;
371 statem varchar2(2000); -- used with dynamic pl/sql
372 sql_cursor integer;
373 l_rows integer;
374 begin
375 --
376 if g_debug then
377 hr_utility.trace('In evaluate_asg_group()');
378 end if;
379 select distinct legislation_code, paf.business_group_id
380 into l_leg_code, l_bg_id
381 from per_business_groups_perf pbg,
382 per_all_assignments_f paf
383 where paf.assignment_id = p_assignment_id
384 and p_effective_start_date between paf.effective_start_date
385 and paf.effective_end_date
386 and paf.business_group_id = pbg.business_group_id;
387 --
388 -- bug 9354495 set business group profile from the assignments bg
389 fnd_profile.put('PER_BUSINESS_GROUP_ID',l_bg_id);
390 --
391 statem :=
392 'begin
393 pay_'||l_leg_code||'_rules.get_asg_process_group(
394 :assignment_id,
395 :effective_start_date,
396 :effective_end_date,
397 :group_name);
398 end;
399 ';
400 --
401 sql_cursor := dbms_sql.open_cursor;
402 --
403 dbms_sql.parse(sql_cursor, statem, dbms_sql.v7);
404 --
405 --
406 dbms_sql.bind_variable(sql_cursor, 'assignment_id', p_assignment_id);
407 --
408 dbms_sql.bind_variable(sql_cursor, 'effective_start_date', p_effective_start_date);
409 --
410 dbms_sql.bind_variable(sql_cursor, 'effective_end_date', p_effective_end_date);
411 --
412 dbms_sql.bind_variable(sql_cursor, 'group_name', p_group_name, 30);
413 --
414 l_rows := dbms_sql.execute (sql_cursor);
415 --
416 if (l_rows = 1) then
417 dbms_sql.variable_value(sql_cursor, 'group_name',
418 p_group_name);
419 dbms_sql.close_cursor(sql_cursor);
420 --
421 else
422 dbms_sql.close_cursor(sql_cursor);
423 pay_core_utils.assert_condition
424 ('pay_asg_process_grp_pkg.evaluate_asg_group:1',
425 1 = 2);
426 end if;
427 --
428 pay_core_utils.assert_condition
429 ('pay_asg_process_grp_pkg.evaluate_asg_group:2',
430 p_group_name is not null);
431 --
432 if g_debug then
433 hr_utility.trace('Out evaluate_asg_group()');
434 end if;
435 end evaluate_asg_group;
436 --
437 /*
438 Name
439 generate_asg_group
440 Description
441
442 This procedure calls all the required procedures to
443 create an assignment group.
444 */
445 procedure generate_asg_group(p_assignment_id in number,
446 p_person_id in number,
447 p_period_of_service_id in number,
448 p_effective_start_date in date,
449 p_effective_end_date in date,
450 p_payroll_id in number
451 )
452 is
453 l_group_name pay_group_definitions.name%type;
454 l_person_group_id pay_object_groups.object_group_id%type;
455 l_asg_group_id pay_object_groups.object_group_id%type;
456 begin
457 --
458 if g_debug then
459 hr_utility.trace('In generate_asg_group()');
460 end if;
461
462 if (p_payroll_id is not null) then
463 --
464 evaluate_asg_group(p_assignment_id,
465 p_effective_start_date,
466 p_effective_end_date,
467 l_group_name
468 );
469 l_person_group_id := get_person_group
470 (p_person_id,
471 p_period_of_service_id,
472 p_effective_start_date,
473 p_effective_end_date,
474 l_group_name
475 );
476 l_asg_group_id := get_assignment_group
477 (p_person_group_id => l_person_group_id,
478 p_assignment_id => p_assignment_id,
479 p_effective_start_date => p_effective_start_date,
480 p_effective_end_date => p_effective_end_date,
481 p_payroll_id => p_payroll_id
482 );
483 --
484 end if;
485 --
486 if g_debug then
487 hr_utility.trace('Out generate_asg_group()');
488 end if;
489 end generate_asg_group;
490 --
491 /*
492 Name
493 asg_datetracked_insert
494 Description
495
496 This is called from the Assignment Dynamic Triggers
497 to maintain the assignment groups
498 */
499
500 procedure asg_datetracked_insert(p_assignment_id in number,
501 p_person_id in number,
502 p_period_of_service_id in number,
503 p_effective_start_date in date,
504 p_effective_end_date in date,
505 p_payroll_id in number
506 )
507 is
508 begin
509 --
510 if g_debug then
511 hr_utility.trace('In asg_datetracked_insert()');
512 end if;
513 generate_asg_group(p_assignment_id,
514 p_person_id,
515 p_period_of_service_id,
516 p_effective_start_date,
517 p_effective_end_date,
518 p_payroll_id
519 );
520 --
521 if g_debug then
522 hr_utility.trace('Out asg_datetracked_insert()');
523 end if;
524 end asg_datetracked_insert;
525 --
526 /*
527 Name
528 zap_object_group
529 Description
530
531 This is purges an assignment processing group from the
532 system, then removes the person object groups if no
533 other assignment processing groups exist for it.
534 */
535
536 procedure zap_object_group(p_object_group_id in number)
537 is
538 l_parent_object_group_id pay_object_groups.parent_object_group_id%type;
539 l_cnt number;
540 begin
541 --
542 if g_debug then
543 hr_utility.trace('In zap_object_group()');
544 end if;
545 select parent_object_group_id
546 into l_parent_object_group_id
547 from pay_object_groups
548 where object_group_id = p_object_group_id;
549 --
550 if (l_parent_object_group_id is not null) then
551 --
552 -- Find out how many object groups are on the
553 -- parent. If its only one then delete it.
554 -- Since its the current object group.
555 --
556 select count(*)
557 into l_cnt
558 from pay_object_groups
559 where parent_object_group_id = l_parent_object_group_id;
560 --
561 if (l_cnt = 1) then
562 delete from pay_object_groups
563 where object_group_id = l_parent_object_group_id;
564 end if;
565 end if;
566 --
567 delete from pay_object_groups
568 where object_group_id = p_object_group_id;
569 --
570 if g_debug then
571 hr_utility.trace('Out zap_object_group()');
572 end if;
573 end zap_object_group;
574 --
575 /*
576 Name
577 end_date_object_group
578 Description
579
580 This is end dates an object group.
581 */
582
583 procedure end_date_object_group(p_object_group_id in number,
584 p_effective_end_date in date)
585 is
586 begin
587 --
588 if g_debug then
589 hr_utility.trace('In end_date_object_group()');
590 hr_utility.trace('Object group id = '|| p_object_group_id);
591 hr_utility.trace('Effective date = '|| p_effective_end_date);
592 end if;
593 update pay_object_groups
594 set end_date = p_effective_end_date
595 where object_group_id = p_object_group_id;
596 --
597 if g_debug then
598 hr_utility.trace('Out end_date_object_group()');
599 end if;
600 end end_date_object_group;
601 --
602 /*
603 Name
604 asg_datetracked_delete_next
605 Description
606
607 This is called from the Assignment Dynamic Triggers
608 to maintain the assignment groups
609 */
610 procedure asg_datetracked_delete_next(p_assignment_id in number,
611 p_effective_end_date in date,
612 p_effective_end_date_o in date,
613 p_val_end_date in date
614 )
615 is
616 cursor get_pogpaf(p_assignment_id number,
617 p_effective_end_date_o date)
618 is
619 select object_group_id,
620 parent_object_group_id,
621 start_date,
622 end_date
623 from pay_object_groups pog_paf
624 where pog_paf.source_id = p_assignment_id
625 and pog_paf.source_type = 'PAF'
626 and pog_paf.end_date = p_effective_end_date_o;
627 --
628 cursor get_pogppf(p_par_object_group_id number,
629 p_effective_end_date_o date)
630 is
631 select object_group_id,
632 start_date,
633 end_date
634 from pay_object_groups pog_paf
635 where object_group_id = p_par_object_group_id
636 and pog_paf.end_date = p_effective_end_date_o;
637 begin
638 --
639 if g_debug then
640 hr_utility.trace('In asg_datetracked_delete_next()');
641 end if;
642 for pogrec in get_pogpaf(p_assignment_id, p_effective_end_date_o) loop
643 --
644 if g_debug then
645 hr_utility.trace('Object group id = '|| pogrec.object_group_id);
646 hr_utility.trace('Effective date = '|| p_effective_end_date);
647 hr_utility.trace('p_effective_end_date_o = ' || p_effective_end_date_o);
648 end if;
649 update pay_object_groups
650 set end_date = p_val_end_date --Bug 14585781 ,Case delete_next/future
651 where object_group_id = pogrec.object_group_id;
652 --
653
654 -- Bug 14585781, perform the delete of the next record
655 delete from pay_object_groups where source_id = p_assignment_id and source_type = 'PAF'
656 and start_date > = p_effective_end_date_o
657 and end_date <= p_val_end_date;
658
659 for ppfrec in get_pogppf(pogrec.parent_object_group_id,
660 p_effective_end_date_o)
661 loop
662 --
663 if g_debug then
664 hr_utility.trace('Object group id = '|| ppfrec.object_group_id);
665 hr_utility.trace('Effective date = '|| p_effective_end_date);
666 end if;
667 update pay_object_groups
668 set end_date = p_effective_end_date
669 where object_group_id = ppfrec.object_group_id;
670 --
671 end loop;
672 --
673 end loop;
674 --
675
676 --
677 if g_debug then
678 hr_utility.trace('In asg_datetracked_delete_next()');
679 end if;
680 end asg_datetracked_delete_next;
681 --
682 /*
683 Name
684 asg_datetracked_end_date
685 Description
686
687 This is called from the Assignment Dynamic Triggers
688 to maintain the assignment groups
689 */
690 procedure asg_datetracked_end_date(p_assignment_id in number,
691 p_effective_end_date in date
692 )
693 is
694 cursor get_pogpaf(p_assignment_id number,
695 p_effective_end_date date)
696 is
697 select object_group_id,
698 start_date,
699 end_date
700 from pay_object_groups pog_paf
701 where pog_paf.source_id = p_assignment_id
702 and pog_paf.source_type = 'PAF'
703 and pog_paf.end_date > p_effective_end_date
704 order by start_date;
705 begin
706 --
707 -- OK the assignment has ended, we need to end all the
708 -- POG PAFs that the assignment has.
709 if g_debug then
710 hr_utility.trace('In asg_datetracked_end_date()');
711 end if;
712 for pafrec in get_pogpaf(p_assignment_id, p_effective_end_date) loop
713 --
714 if g_debug then
715 hr_utility.trace('pafrec.start_date ' || pafrec.start_date);
716 hr_utility.trace('p_effective_end_date ' || p_effective_end_date);
717 end if;
718 if (pafrec.start_date > p_effective_end_date) then
719 zap_object_group(pafrec.object_group_id);
720 else
721 end_date_object_group(pafrec.object_group_id,
722 p_effective_end_date);
723 end if;
724 --
725 end loop;
726 --
727 if g_debug then
728 hr_utility.trace('Out asg_datetracked_end_date()');
729 end if;
730 end asg_datetracked_end_date;
731 --
732 /*
733 Name
734 find_asg_group_definition
735 Description
736
737 This retrieves the group name for an assignment group.
738 */
739 procedure find_asg_group_definition(p_assignment_id in number,
740 p_payroll_id in number,
741 p_effective_date in date,
742 p_definition_name out nocopy varchar2)
743 is
744 begin
745 --
746 if g_debug then
747 hr_utility.trace('In find_asg_group_definition()');
748 end if;
749 select pgd.name
750 into p_definition_name
751 from pay_group_definitions pgd,
752 pay_object_groups pog_ppf,
753 pay_object_groups pog_paf
754 where pog_paf.source_id = p_assignment_id
755 and pog_paf.source_type = 'PAF'
756 and pog_paf.payroll_id = p_payroll_id
757 and p_effective_date between pog_paf.start_date
758 and pog_paf.end_date
759 and pog_paf.parent_object_group_id = pog_ppf.object_group_id
760 and pog_ppf.group_definition_id = pgd.group_definition_id;
761 --
762 if g_debug then
763 hr_utility.trace('Out find_asg_group_definition()');
764 end if;
765 end find_asg_group_definition;
766 --
767 -- start of Bug 14585781
768 /*
769 Name
770 asg_datetracked_correction
771 Description
772
773 This is called from the Assignment Dynamic Triggers
774 to maintain the assignment groups
775 */
776 procedure asg_datetracked_correction(p_assignment_id in number,
777 p_person_id in number,
778 p_period_of_service_id in number,
779 p_old_effective_start_date in date,
780 p_old_effective_end_date in date,
781 p_new_effective_start_date in date,
782 p_new_effective_end_date in date,
783 p_old_payroll_id in number,
784 p_new_payroll_id in number
785 )
786 is
787 l_old_group_name pay_group_definitions.name%type;
788 l_new_group_name pay_group_definitions.name%type;
789 l_object_group_id pay_object_groups.object_group_id%type;
790
791 procedure purge_curr_obj_group is
792 begin
793 select object_group_id into l_object_group_id
794 from pay_object_groups pog_paf
795 where pog_paf.source_id = p_assignment_id
796 and pog_paf.source_type = 'PAF'
797 and pog_paf.payroll_id = p_old_payroll_id
798 and pog_paf.start_date = p_old_effective_start_date
799 and pog_paf.end_date = p_old_effective_end_date;
800
801 --Now Zap the object group since the change is in Correction Mode and there is no further payroll
802 zap_object_group(l_object_group_id);
803 exception
804 when NO_DATA_FOUND then
805 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
806 hr_utility.set_message_token('PROCEDURE',
807 'pay_asg_process_grp_pkg.asg_datetracked_correction');
808 hr_utility.set_message_token('STEP','1');
809 hr_utility.raise_error;
810 end purge_curr_obj_group;
811
812 begin
813 if g_debug then
814 hr_utility.trace('In asg_datetracked_correction()');
815 end if;
816 if ( p_old_payroll_id is null
817 and p_new_payroll_id is null) then
818 --
819 return;
820 --
821 elsif ( p_old_payroll_id is null
822 and p_new_payroll_id is not null) then
823 --
824 generate_asg_group(p_assignment_id,
825 p_person_id,
826 p_period_of_service_id,
827 p_new_effective_start_date,
828 p_new_effective_end_date,
829 p_new_payroll_id
830 );
831 elsif ( p_old_payroll_id is not null
832 and p_new_payroll_id is null) then
833 --
834 purge_curr_obj_group;
835 return;
836 --
837 elsif (p_old_payroll_id = p_new_payroll_id) then
838 --
839 find_asg_group_definition(p_assignment_id => p_assignment_id,
840 p_payroll_id => p_old_payroll_id,
841 p_effective_date => p_old_effective_end_date,
842 p_definition_name => l_old_group_name
843 );
844 --
845 evaluate_asg_group(p_assignment_id,
846 p_new_effective_start_date,
847 p_new_effective_end_date,
848 l_new_group_name
849 );
850 if (l_old_group_name <> l_new_group_name) then
851 generate_asg_group(p_assignment_id,
852 p_person_id,
853 p_period_of_service_id,
854 p_new_effective_start_date,
855 p_new_effective_end_date,
856 p_new_payroll_id
857 );
858 end if;
859 --
860 else
861 --different payrolls, so delete the record for old payroll and generate new record for new payroll
862
863 purge_curr_obj_group;
864 generate_asg_group(p_assignment_id,
865 p_person_id,
866 p_period_of_service_id,
867 p_new_effective_start_date,
868 p_new_effective_end_date,
869 p_new_payroll_id
870 );
871
872 end if;
873 if g_debug then
874 hr_utility.trace('Out asg_datetracked_correction()');
875 end if;
876 end asg_datetracked_correction;
877
878 --End of Bug 14585781
879 /*
880 Name
881 asg_datetracked_update
882 Description
883
884 This is called from the Assignment Dynamic Triggers
885 to maintain the assignment groups
886 */
887
888 procedure asg_datetracked_update(p_assignment_id in number,
889 p_person_id in number,
890 p_period_of_service_id in number,
891 p_old_effective_start_date in date,
892 p_old_effective_end_date in date,
893 p_new_effective_start_date in date,
894 p_new_effective_end_date in date,
895 p_old_payroll_id in number,
896 p_new_payroll_id in number
897 )
898 is
899 l_old_group_name pay_group_definitions.name%type;
900 l_new_group_name pay_group_definitions.name%type;
901 begin
902 --
903 if g_debug then
904 hr_utility.trace('In asg_datetracked_update()');
905 end if;
906 if ( p_old_payroll_id is null
907 and p_new_payroll_id is null) then
908 --
909 return;
910 --
911 elsif ( p_old_payroll_id is null
912 and p_new_payroll_id is not null) then
913 --
914 generate_asg_group(p_assignment_id,
915 p_person_id,
916 p_period_of_service_id,
917 p_new_effective_start_date,
918 p_new_effective_end_date,
919 p_new_payroll_id
920 );
921 elsif ( p_old_payroll_id is not null
922 and p_new_payroll_id is null) then
923 --
924 update pay_object_groups
925 set end_date = p_new_effective_start_date -1
926 where source_id = p_assignment_id
927 and source_type = 'PAF'
928 and payroll_id = p_old_payroll_id
929 and p_old_effective_end_date between start_date and end_date
930 and p_old_effective_start_date <> p_new_effective_start_date;
931
932 return;
933 --
934 elsif (p_old_payroll_id = p_new_payroll_id) then
935 --
936 find_asg_group_definition(p_assignment_id => p_assignment_id,
937 p_payroll_id => p_old_payroll_id,
938 p_effective_date => p_old_effective_end_date,
939 p_definition_name => l_old_group_name
940 );
941 --
942 evaluate_asg_group(p_assignment_id,
943 p_new_effective_start_date,
944 p_new_effective_end_date,
945 l_new_group_name
946 );
947 if g_debug then
948 hr_utility.trace ('location X old group id = '||l_old_group_name||' new gr id = '||l_new_group_name);
949 hr_utility.trace ('p_assignment_id = '||p_assignment_id);
950 hr_utility.trace ('p_person_id = '||p_person_id);
951 hr_utility.trace ('p_period_of_service_id = '||p_period_of_service_id);
952 hr_utility.trace ('p_new_effective_start_date = '||p_new_effective_start_date);
953 hr_utility.trace ('p_new_effective_end_date = '||p_new_effective_end_date);
954 hr_utility.trace ('p_new_payroll_id = '||p_new_payroll_id);
955 end if;
956 if (l_old_group_name <> l_new_group_name) then
957 generate_asg_group(p_assignment_id,
958 p_person_id,
959 p_period_of_service_id,
960 p_new_effective_start_date,
961 p_new_effective_end_date,
962 p_new_payroll_id
963 );
964 end if;
965 --
966 else
967
968 update pay_object_groups
969 set end_date = p_new_effective_start_date -1
970
971 where source_id = p_assignment_id
972 and source_type = 'PAF'
973 and payroll_id = p_old_payroll_id
974 and p_old_effective_end_date between start_date and end_date;
975
976
977
978 generate_asg_group(p_assignment_id,
979 p_person_id,
980 p_period_of_service_id,
981 p_new_effective_start_date,
982 p_new_effective_end_date,
983 p_new_payroll_id
984 );
985
986
987 end if;
988 --
989 if g_debug then
990 hr_utility.trace('Out asg_datetracked_update()');
991 end if;
992 end asg_datetracked_update;
993 --
994 /*
995 Name
996 asg_datetracked_strt_early
997 Description
998 This is called from the Assignment Dynamic Triggers
999 to maintain the assignment groups
1000 */
1001
1002 procedure asg_datetracked_strt_early(p_assignment_id in number,
1003 p_person_id in number,
1004 p_period_of_service_id in number,
1005 p_old_effective_start_date in date,
1006 p_old_effective_end_date in date,
1007 p_new_effective_start_date in date,
1008 p_new_effective_end_date in date,
1009 p_old_payroll_id in number,
1010 p_new_payroll_id in number
1011 )
1012 is
1013 l_period_of_service_id NUMBER;
1014 l_new_effective_start_date DATE;
1015 begin
1016 --
1017 if g_debug then
1018 hr_utility.trace('In asg_datetracked_strt_early()');
1019 hr_utility.trace('Start date = '|| p_new_effective_start_date);
1020 hr_utility.trace('Source id = '|| p_assignment_id);
1021 hr_utility.trace('Old start date = '|| p_old_effective_start_date);
1022 end if;
1023 update pay_object_groups
1024 set start_date = p_new_effective_start_date
1025 where source_id = p_assignment_id
1026 and source_type = 'PAF'
1027 and start_date = p_old_effective_start_date;
1028
1029 --start of bug 12696936
1030 begin
1031 select pos.date_start
1032 ,pos.period_of_service_id
1033 into l_new_effective_start_date
1034 ,l_period_of_service_id
1035 from per_all_assignments_f asg
1036 ,per_periods_of_service pos
1037 where asg.assignment_id = p_assignment_id
1038 and asg.period_of_service_id = pos.period_of_service_id;
1039
1040 update pay_object_groups
1041 set start_date = p_new_effective_start_date
1042 where source_type = 'PPF'
1043 and period_of_service_id = l_period_of_service_id;
1044 exception
1045 when others then
1046 null;
1047 end;
1048 --end of bug 12696936
1049 --
1050 if g_debug then
1051 hr_utility.trace('Out asg_datetracked_strt_early()');
1052 end if;
1053 end asg_datetracked_strt_early;
1054
1055 --start of bug 12696936
1056 --
1057 /*
1058 Name
1059 asg_datetracked_strt_later
1060 Description
1061 This is called from the Assignment Dynamic Triggers
1062 to maintain the assignment groups
1063 */
1064
1065 procedure asg_datetracked_strt_later(p_assignment_id in number,
1066 p_person_id in number,
1067 p_period_of_service_id in number,
1068 p_old_effective_start_date in date,
1069 p_old_effective_end_date in date,
1070 p_new_effective_start_date in date,
1071 p_new_effective_end_date in date,
1072 p_old_payroll_id in number,
1073 p_new_payroll_id in number
1074 )
1075 is
1076 l_period_of_service_id NUMBER;
1077 l_new_effective_start_date DATE;
1078 begin
1079 --
1080 if g_debug then
1081 hr_utility.trace('In asg_datetracked_strt_later()');
1082 hr_utility.trace('Start date = '|| p_new_effective_start_date);
1083 hr_utility.trace('Source id = '|| p_assignment_id);
1084 hr_utility.trace('Old start date = '|| p_old_effective_start_date);
1085 end if;
1086 update pay_object_groups
1087 set start_date = p_new_effective_start_date
1088 where source_id = p_assignment_id
1089 and source_type = 'PAF'
1090 and start_date = p_old_effective_start_date;
1091
1092 begin
1093 select pos.date_start
1094 ,pos.period_of_service_id
1095 into l_new_effective_start_date
1096 ,l_period_of_service_id
1097 from per_all_assignments_f asg
1098 ,per_periods_of_service pos
1099 where asg.assignment_id = p_assignment_id
1100 and asg.period_of_service_id = pos.period_of_service_id;
1101
1102 update pay_object_groups
1103 set start_date = p_new_effective_start_date
1104 where source_type = 'PPF'
1105 and period_of_service_id = l_period_of_service_id
1106 and start_date = p_old_effective_start_date;
1107 exception
1108 when others then
1109 null;
1110 end;
1111
1112 --
1113 if g_debug then
1114 hr_utility.trace('Out asg_datetracked_strt_later()');
1115 end if;
1116 end asg_datetracked_strt_later;
1117
1118 --end of bug 12696936
1119
1120 /*
1121 Name
1122 asg_datetracked_ovrr_update
1123 Description
1124
1125 This is called from the Assignment Dynamic Triggers
1126 to maintain the assignment groups
1127 */
1128
1129 procedure asg_datetracked_ovrr_update(p_assignment_id in number,
1130 p_person_id in number,
1131 p_period_of_service_id in number,
1132 p_old_effective_start_date in date,
1133 p_old_effective_end_date in date,
1134 p_new_effective_start_date in date,
1135 p_new_effective_end_date in date,
1136 p_old_payroll_id in number,
1137 p_new_payroll_id in number
1138 )
1139 is
1140 cursor get_pogpaf(p_assignment_id number,
1141 p_effective_end_date date)
1142 is
1143 select object_group_id,
1144 start_date,
1145 end_date
1146 from pay_object_groups pog_paf
1147 where pog_paf.source_id = p_assignment_id
1148 and pog_paf.source_type = 'PAF';
1149 --Bug 14585781,changed the cur,Case C
1150 begin
1151 --
1152 if g_debug then
1153 hr_utility.trace('In asg_datetracked_ovrr_update()');
1154 end if;
1155 asg_datetracked_update(p_assignment_id,
1156 p_person_id,
1157 p_period_of_service_id,
1158 p_old_effective_start_date,
1159 p_old_effective_end_date,
1160 p_new_effective_start_date,
1161 p_new_effective_end_date,
1162 p_old_payroll_id,
1163 p_new_payroll_id
1164 );
1165 --
1166 -- Any POG_PAF that starts after the new start date
1167 -- then they must be removed as a they have been
1168 -- overriden
1169 if g_debug then
1170 hr_utility.trace(' p_new_effective_start_date '|| p_new_effective_start_date);
1171 hr_utility.trace('p_new_effective_end_date '|| p_new_effective_end_date);
1172 end if;
1173 for pafrec in get_pogpaf(p_assignment_id, p_new_effective_end_date) loop
1174 -- Bug 14585781, changed below condition,Case C
1175 if (pafrec.start_date > p_new_effective_start_date) then
1176 zap_object_group(pafrec.object_group_id);
1177 end if;
1178 --
1179 end loop;
1180 --
1181 if g_debug then
1182 hr_utility.trace('Out asg_datetracked_ovrr_update()');
1183 end if;
1184 end asg_datetracked_ovrr_update;
1185 --
1186 /*
1187 Name
1188 asg_datetracked_zap
1189 Description
1190
1191 This is called from the Assignment Dynamic Triggers
1192 to maintain the assignment groups
1193 */
1194
1195 procedure asg_datetracked_zap(p_assignment_id in number)
1196 is
1197 cursor get_pogpaf(p_assignment_id number)
1198 is
1199 select object_group_id,
1200 start_date,
1201 end_date
1202 from pay_object_groups pog_paf
1203 where pog_paf.source_id = p_assignment_id
1204 and pog_paf.source_type = 'PAF';
1205 begin
1206 --
1207 if g_debug then
1208 hr_utility.trace('In asg_datetracked_zap()');
1209 end if;
1210 -- all POG_PAF for the assignment must be removed
1211 for pafrec in get_pogpaf(p_assignment_id) loop
1212 --
1213 zap_object_group(pafrec.object_group_id);
1214 --
1215 end loop;
1216 --
1217 if g_debug then
1218 hr_utility.trace('Out asg_datetracked_zap()');
1219 end if;
1220 end;
1221
1222 /*
1223 Name
1224 asg_insert_trigger
1225 Description
1226
1227 This is the Dynamic Trigger Code for Assignment Processing
1228 Groups
1229 */
1230
1231 procedure asg_insert_trigger(p_assignment_id in number,
1232 p_person_id in number,
1233 p_period_of_service_id in number,
1234 p_new_effective_start_date in date,
1235 p_new_effective_end_date in date,
1236 p_new_payroll_id in number,
1237 p_business_group_id in number
1238 )
1239 is
1240 dt_mode varchar2(20);
1241 begin
1242 --
1243 if g_debug then
1244 hr_utility.trace('In asg_insert_trigger()');
1245 end if;
1246 if (is_pog_enable(p_business_group_id) = TRUE) then
1247 asg_datetracked_insert(p_assignment_id,
1248 p_person_id,
1249 p_period_of_service_id,
1250 p_new_effective_start_date,
1251 p_new_effective_end_date,
1252 p_new_payroll_id
1253 );
1254 end if;
1255 --
1256 if g_debug then
1257 hr_utility.trace('Out asg_insert_trigger()');
1258 end if;
1259 end asg_insert_trigger;
1260
1261 /*
1262 Name
1263 asg_update_trigger
1264 Description
1265
1266 This is the Dynamic Trigger Code for Assignment Processing
1267 Groups
1268 */
1269
1270 procedure asg_update_trigger(p_assignment_id in number,
1271 p_person_id in number,
1272 p_period_of_service_id in number,
1273 p_old_effective_start_date in date,
1274 p_old_effective_end_date in date,
1275 p_new_effective_start_date in date,
1276 p_new_effective_end_date in date,
1277 p_old_payroll_id in number,
1278 p_new_payroll_id in number,
1279 p_business_group_id in number
1280 )
1281 is
1282 dt_mode varchar2(20);
1283 begin
1284 --
1285 if g_debug then
1286 hr_utility.trace('In asg_update_trigger()');
1287 end if;
1288 if (is_pog_enable(p_business_group_id) = TRUE) then
1289 --
1290 -- First set the Date Track mode
1291 --
1292 dt_mode := PAY_POG_ALL_ASSIGNMENTS_PKG.dyt_mode;
1293 if g_debug then
1294 hr_utility.trace ('Location 02 dt_mode = '||dt_mode);
1295 end if;
1296 --Bug 14585781 added Correction mode
1297 if ( dt_mode = 'CORRECTION') then
1298
1299 asg_datetracked_correction (p_assignment_id,
1300 p_person_id,
1301 p_period_of_service_id,
1302 p_old_effective_start_date,
1303 p_old_effective_end_date,
1304 p_new_effective_start_date,
1305 p_new_effective_end_date,
1306 p_old_payroll_id,
1307 p_new_payroll_id
1308 );
1309 elsif ( dt_mode = 'UPDATE'
1310 or dt_mode = 'UPDATE_CHANGE_INSERT') then
1311 --
1312 asg_datetracked_update(p_assignment_id,
1313 p_person_id,
1314 p_period_of_service_id,
1315 p_old_effective_start_date,
1316 p_old_effective_end_date,
1317 p_new_effective_start_date,
1318 p_new_effective_end_date,
1319 p_old_payroll_id,
1320 p_new_payroll_id
1321 );
1322 --
1323 elsif ( dt_mode = 'UPDATE_OVERRIDE') then
1324 --
1325 asg_datetracked_ovrr_update(p_assignment_id,
1326 p_person_id,
1327 p_period_of_service_id,
1328 p_old_effective_start_date,
1329 p_old_effective_end_date,
1330 p_new_effective_start_date,
1331 p_new_effective_end_date,
1332 p_old_payroll_id,
1333 p_new_payroll_id
1334 );
1335 --
1336 elsif ( dt_mode = 'START_EARLIER') then
1337 --
1338 asg_datetracked_strt_early(p_assignment_id,
1339 p_person_id,
1340 p_period_of_service_id,
1341 p_old_effective_start_date,
1342 p_old_effective_end_date,
1343 p_new_effective_start_date,
1344 p_new_effective_end_date,
1345 p_old_payroll_id,
1346 p_new_payroll_id
1347 );
1348 --start of bug 12696936
1349 elsif ( dt_mode = 'START_LATER') then
1350 --
1351 asg_datetracked_strt_later(p_assignment_id,
1352 p_person_id,
1353 p_period_of_service_id,
1354 p_old_effective_start_date,
1355 p_old_effective_end_date,
1356 p_new_effective_start_date,
1357 p_new_effective_end_date,
1358 p_old_payroll_id,
1359 p_new_payroll_id
1360 );
1361 --end of bug 12696936
1362 end if;
1363 end if;
1364 --
1365 if g_debug then
1366 hr_utility.trace('Out asg_update_trigger()');
1367 end if;
1368 end asg_update_trigger;
1369 --
1370 /*
1371 Name
1372 asg_delete_trigger
1373 Description
1374
1375 This is the Dynamic Trigger Code for Assignment Processing
1376 Groups
1377 */
1378
1379 procedure asg_delete_trigger(p_assignment_id in number,
1380 p_effective_end_date in date,
1381 p_business_group_id in number,
1382 p_effective_end_date_o in date,
1383 p_val_end_date in date
1384 )
1385 is
1386 dt_mode varchar2(20);
1387 begin
1388 --
1389 if g_debug then
1390 hr_utility.trace('In asg_delete_trigger()');
1391 end if;
1392 if (is_pog_enable(p_business_group_id) = TRUE) then
1393 --
1394 -- First set the Date Track mode
1395 --
1396 dt_mode := PAY_POG_ALL_ASSIGNMENTS_PKG.dyt_mode;
1397 --
1398 if ( dt_mode = 'ZAP') then
1399 --
1400 asg_datetracked_zap(p_assignment_id);
1401 --
1402 elsif ( dt_mode = 'DELETE') then
1403 --
1404 if g_debug then
1405 hr_utility.trace ('Asg del trig - eff date = '||p_effective_end_date);
1406 end if;
1407 asg_datetracked_end_date(p_assignment_id,
1408 p_effective_end_date
1409 );
1410 --
1411 elsif dt_mode = 'FUTURE_CHANGE' or dt_mode = 'DELETE_NEXT_CHANGE' then
1412 asg_datetracked_delete_next(p_assignment_id,
1413 p_effective_end_date,
1414 p_effective_end_date_o,
1415 p_val_end_date
1416 );
1417 end if;
1418 end if;
1419 --
1420 if g_debug then
1421 hr_utility.trace('Out asg_delete_trigger()');
1422 end if;
1423 end asg_delete_trigger;
1424 --
1425 /*
1426 Name
1427 upgrade_asg
1428 Description
1429
1430 Upgrades existing data to have process groups. This is used by
1431 the generic upgrade process to convert existing legislations.
1432 */
1433 procedure upgrade_asg(p_asg_id in number)
1434 is
1435 --
1436 cursor get_asg_info(p_asg_id in number)
1437 is
1438 select assignment_id,
1439 person_id,
1440 period_of_service_id,
1441 payroll_id,
1442 effective_start_date,
1443 effective_end_date,
1444 business_group_id
1445 from per_all_assignments_f
1446 where assignment_id = p_asg_id
1447 order by assignment_id, effective_start_date;
1448 --
1449 asg_id per_all_assignments_f.assignment_id%type;
1450 per_id per_all_assignments_f.person_id%type;
1451 pos_id per_all_assignments_f.period_of_service_id%type;
1452 pay_id per_all_assignments_f.payroll_id%type;
1453 effective_start_date date;
1454 effective_end_date date;
1455 first_row boolean;
1456 --
1457 begin
1458 --
1459 if g_debug then
1460 hr_utility.trace('In upgrade_asg()');
1461 end if;
1462 first_row := TRUE;
1463 for asgrec in get_asg_info(p_asg_id) loop
1464 --
1465 if (first_row = TRUE) then
1466 --
1467 PAY_POG_ALL_ASSIGNMENTS_PKG.dyt_mode := 'INSERT';
1468 asg_insert_trigger
1469 (p_assignment_id => asgrec.assignment_id,
1470 p_person_id => asgrec.person_id,
1471 p_period_of_service_id => asgrec.period_of_service_id,
1472 p_new_effective_start_date => asgrec.effective_start_date,
1473 p_new_effective_end_date => asgrec.effective_end_date,
1474 p_new_payroll_id => asgrec.payroll_id,
1475 p_business_group_id => asgrec.business_group_id
1476 );
1477 asg_id := asgrec.assignment_id;
1478 per_id := asgrec.person_id;
1479 pos_id := asgrec.period_of_service_id;
1480 pay_id := asgrec.payroll_id;
1481 effective_start_date := asgrec.effective_start_date;
1482 effective_end_date := asgrec.effective_end_date;
1483 first_row := FALSE;
1484 --
1485 else
1486 --
1487 PAY_POG_ALL_ASSIGNMENTS_PKG.dyt_mode := 'UPDATE';
1488 asg_update_trigger
1489 (p_assignment_id => asgrec.assignment_id,
1490 p_person_id => asgrec.person_id,
1491 p_period_of_service_id => asgrec.period_of_service_id,
1492 p_old_effective_start_date => effective_start_date,
1493 p_old_effective_end_date => effective_end_date,
1494 p_new_effective_start_date => asgrec.effective_start_date,
1495 p_new_effective_end_date => asgrec.effective_end_date,
1496 p_old_payroll_id => pay_id,
1497 p_new_payroll_id => asgrec.payroll_id,
1498 p_business_group_id => asgrec.business_group_id
1499 );
1500 --
1501 asg_id := asgrec.assignment_id;
1502 per_id := asgrec.person_id;
1503 pos_id := asgrec.period_of_service_id;
1504 pay_id := asgrec.payroll_id;
1505 effective_start_date := asgrec.effective_start_date;
1506 effective_end_date := asgrec.effective_end_date;
1507 first_row := FALSE;
1508 --
1509 end if;
1510 --
1511 end loop;
1512 --
1513 if g_debug then
1514 hr_utility.trace('Out upgrade_asg()');
1515 end if;
1516 end upgrade_asg;
1517 --
1518 end pay_asg_process_grp_pkg;
[Home] [Help]