[Home] [Help]
PACKAGE BODY: APPS.PAY_ASG_PROCESS_GRP_PKG
Source
1 package body pay_asg_process_grp_pkg as
2 /* $Header: pycorapg.pkb 120.6 2006/01/10 07:19:47 nbristow noship $ */
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
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 select pbg.legislation_code
22 into l_leg_code
23 from per_business_groups_perf pbg
24 where pbg.business_group_id = p_bgp_id;
25 --
26 pay_core_utils.get_legislation_rule
27 (p_legrul_name => 'I',
28 p_legislation => l_leg_code,
29 p_legrul_value => l_value,
30 p_found => l_found);
31 --
32 if (l_found = FALSE) then
33 l_value := 'N';
34 end if;
35 --
36 if (l_value <> 'G') then
37 return FALSE;
38 else
39 return TRUE;
40 end if;
41 --
42 end is_pog_enable;
43 --
44 /*
45 Name
46 get_group_definition
47 Description
48
49 This function is used to retrieve/Create a group definition.
50 */
51 function get_group_definition(p_definition_name in varchar2)
52 return number
53 is
54 l_definition_id number;
55 begin
56 --
57 select group_definition_id
58 into l_definition_id
59 from pay_group_definitions
60 where name = p_definition_name;
61 --
62 return l_definition_id;
63 --
64 exception
65 when no_data_found then
66 begin
67 --
68 -- Need to lock the table to esnure only
69 -- one thread creates the group row.
70 --
71 lock table pay_group_definitions in share mode;
72 --
73 -- Ensure that nothing has created
74 -- the row in the meantime
75 select group_definition_id
76 into l_definition_id
77 from pay_group_definitions
78 where name = p_definition_name;
79 --
80 return l_definition_id;
81 --
82 exception
83 when no_data_found then
84 --
85 select pay_group_definitions_s.nextval
86 into l_definition_id
87 from dual;
88 --
89 insert into pay_group_definitions
90 (group_definition_id,
91 name)
92 values
93 (l_definition_id,
94 p_definition_name);
95 --
96 return l_definition_id;
97 end;
98 --
99 end get_group_definition;
100 --
101 /*
102 Name
103 get_assignment_group
104 Description
105
106 This function is used to retrieve/Create an assignment group
107 */
108 function get_assignment_group(p_person_group_id in number,
109 p_assignment_id in number,
110 p_effective_start_date in date,
111 p_effective_end_date in date,
112 p_payroll_id in number,
113 p_update_row in varchar2 default 'Y'
114 )
115 return number
116 is
117 l_object_group_id pay_object_groups.object_group_id%type;
118 l_start_date pay_object_groups.start_date%type;
119 l_end_date pay_object_groups.end_date%type;
120 l_cnt number;
121 begin
122 --
123 select object_group_id,
124 start_date,
125 end_date
126 into l_object_group_id,
127 l_start_date,
128 l_end_date
129 from pay_object_groups
130 where source_id = p_assignment_id
131 and source_type = 'PAF'
132 and start_date <= p_effective_end_date
133 and end_date >= p_effective_start_date
134 and parent_object_group_id = p_person_group_id
135 and payroll_id = p_payroll_id;
136 --
137 return l_object_group_id;
138 --
139 exception
140 when no_data_found then
141 --
142 -- An assignment can not switch
143 -- from one processing group to another
144 --
145 select count(*)
146 into l_cnt
147 from pay_object_groups
148 where source_id = p_assignment_id
149 and source_type = 'PAF'
150 and parent_object_group_id <> p_person_group_id;
151 --
152 if (l_cnt <> 0) then
153 pay_core_utils.assert_condition('pay_asg_process_grp_pkg.get_assignment_group:1',
154 1 = 2);
155 end if;
156 --
157 if (p_update_row = 'Y') then
158 --
159 select pay_object_groups_s.nextval
160 into l_object_group_id
161 from dual;
162 --
163 insert into pay_object_groups
164 (object_group_id,
165 source_id,
166 source_type,
167 start_date,
168 end_date,
169 payroll_id,
170 parent_object_group_id)
171 values
172 (l_object_group_id,
173 p_assignment_id,
174 'PAF',
175 p_effective_start_date,
176 to_date('4712/12/31', 'YYYY/MM/DD'), --p_effective_end_date,
177 p_payroll_id,
178 p_person_group_id
179 );
180 --
181 else
182 --
183 l_object_group_id := null;
184 --
185 end if;
186 --
187 return l_object_group_id;
188 --
189
190 --
191 end get_assignment_group;
192 --
193 /*
194 Name
195 get_person_group
196 Description
197
198 This function is used to retrieve/Create a person group.
199 */
200
201 function get_person_group(p_person_id in number,
202 p_period_of_service_id in number,
203 p_effective_start_date in date,
204 p_effective_end_date in date,
205 p_definition_name in varchar2,
206 p_update_row in varchar2 default 'Y'
207 )
208 return number
209 is
210 l_definition_id pay_group_definitions.group_definition_id%type;
211 l_object_group_id pay_object_groups.object_group_id%type;
212 l_start_date pay_object_groups.start_date%type;
213 l_end_date pay_object_groups.end_date%type;
214 l_update boolean;
215 l_upd_start_date date;
216 l_upd_end_date date;
217 begin
218 --
219 l_definition_id := get_group_definition(p_definition_name);
220 --
221 select object_group_id,
222 start_date,
223 end_date
224 into l_object_group_id,
225 l_start_date,
226 l_end_date
227 from pay_object_groups
228 where source_id = p_person_id
229 and source_type = 'PPF'
230 and start_date <= p_effective_end_date
231 and end_date >= p_effective_start_date
232 and period_of_service_id = p_period_of_service_id
233 and group_definition_id = l_definition_id;
234 --
235 -- Ensure that the object group dates
236 -- are correct
237 --
238 if (p_update_row = 'Y') then
239 if (l_start_date > p_effective_start_date) then
240 l_update := TRUE;
241 l_upd_start_date := p_effective_start_date;
242 else
243 l_upd_start_date := l_start_date;
244 end if;
245 if (l_end_date < p_effective_end_date) then
246 l_upd_end_date := p_effective_end_date;
247 else
248 l_update := TRUE;
249 l_upd_end_date := l_end_date;
250 end if;
251 if (l_update = TRUE) then
252 update pay_object_groups
253 set start_date = l_upd_start_date,
254 end_date = l_upd_end_date
255 where object_group_id = l_object_group_id;
256 end if;
257 end if;
258 --
259 return l_object_group_id;
260 --
261 exception
262 when no_data_found then
263 --
264 if (p_update_row = 'Y') then
265 --
266 select pay_object_groups_s.nextval
267 into l_object_group_id
268 from dual;
269 --
270 insert into pay_object_groups
271 (object_group_id,
272 source_id,
273 source_type,
274 start_date,
275 end_date,
276 group_definition_id,
277 period_of_service_id)
278 values
279 (l_object_group_id,
280 p_person_id,
281 'PPF',
282 p_effective_start_date,
283 to_date('4712/12/31', 'YYYY/MM/DD'),
284 l_definition_id,
285 p_period_of_service_id
286 );
287 --
288 else
289 --
290 l_object_group_id := null;
291 --
292 end if;
293 --
294 return l_object_group_id;
295 --
296
297 --
298 end get_person_group;
299 --
300 /*
301 Name
302 evaluate_asg_group
303 Description
304
305 This procedure calls the legislative function to retrieve the
306 process group name.
307 */
308
309 procedure evaluate_asg_group(p_assignment_id in number,
310 p_effective_start_date in date,
311 p_effective_end_date in date,
312 p_group_name out nocopy varchar2)
313 is
314 l_leg_code varchar2(30);
315 statem varchar2(2000); -- used with dynamic pl/sql
316 sql_cursor integer;
317 l_rows integer;
318 begin
319 --
320 select distinct legislation_code
321 into l_leg_code
322 from per_business_groups_perf pbg,
323 per_all_assignments_f paf
324 where paf.assignment_id = p_assignment_id
325 and p_effective_start_date between paf.effective_start_date
326 and paf.effective_end_date
327 and paf.business_group_id = pbg.business_group_id;
328 --
329 statem :=
330 'begin
331 pay_'||l_leg_code||'_rules.get_asg_process_group(
332 :assignment_id,
333 :effective_start_date,
334 :effective_end_date,
335 :group_name);
336 end;
337 ';
338 --
339 sql_cursor := dbms_sql.open_cursor;
340 --
341 dbms_sql.parse(sql_cursor, statem, dbms_sql.v7);
342 --
343 --
344 dbms_sql.bind_variable(sql_cursor, 'assignment_id', p_assignment_id);
345 --
346 dbms_sql.bind_variable(sql_cursor, 'effective_start_date', p_effective_start_date);
347 --
348 dbms_sql.bind_variable(sql_cursor, 'effective_end_date', p_effective_end_date);
349 --
350 dbms_sql.bind_variable(sql_cursor, 'group_name', p_group_name, 30);
351 --
352 l_rows := dbms_sql.execute (sql_cursor);
353 --
354 if (l_rows = 1) then
355 dbms_sql.variable_value(sql_cursor, 'group_name',
356 p_group_name);
357 dbms_sql.close_cursor(sql_cursor);
358 --
359 else
360 dbms_sql.close_cursor(sql_cursor);
361 pay_core_utils.assert_condition
362 ('pay_asg_process_grp_pkg.evaluate_asg_group:1',
363 1 = 2);
364 end if;
365 --
366 pay_core_utils.assert_condition
367 ('pay_asg_process_grp_pkg.evaluate_asg_group:2',
368 p_group_name is not null);
369 --
370 end evaluate_asg_group;
371 --
372 /*
373 Name
374 generate_asg_group
375 Description
376
377 This procedure calls all the required procedures to
378 create an assignment group.
379 */
380 procedure generate_asg_group(p_assignment_id in number,
381 p_person_id in number,
385 p_payroll_id in number
382 p_period_of_service_id in number,
383 p_effective_start_date in date,
384 p_effective_end_date in date,
386 )
387 is
388 l_group_name pay_group_definitions.name%type;
389 l_person_group_id pay_object_groups.object_group_id%type;
390 l_asg_group_id pay_object_groups.object_group_id%type;
391 begin
392 --
393 if (p_payroll_id is not null) then
394 --
395 evaluate_asg_group(p_assignment_id,
396 p_effective_start_date,
397 p_effective_end_date,
398 l_group_name
399 );
400 l_person_group_id := get_person_group
401 (p_person_id,
402 p_period_of_service_id,
403 p_effective_start_date,
404 p_effective_end_date,
405 l_group_name
406 );
407 l_asg_group_id := get_assignment_group
408 (p_person_group_id => l_person_group_id,
409 p_assignment_id => p_assignment_id,
410 p_effective_start_date => p_effective_start_date,
411 p_effective_end_date => p_effective_end_date,
412 p_payroll_id => p_payroll_id
413 );
414 --
415 end if;
416 --
417 end generate_asg_group;
418 --
419 /*
420 Name
421 asg_datetracked_insert
422 Description
423
424 This is called from the Assignment Dynamic Triggers
425 to maintain the assignment groups
426 */
427
428 procedure asg_datetracked_insert(p_assignment_id in number,
429 p_person_id in number,
430 p_period_of_service_id in number,
431 p_effective_start_date in date,
432 p_effective_end_date in date,
433 p_payroll_id in number
434 )
435 is
436 begin
437 --
438 generate_asg_group(p_assignment_id,
439 p_person_id,
440 p_period_of_service_id,
441 p_effective_start_date,
442 p_effective_end_date,
443 p_payroll_id
444 );
445 --
446 end asg_datetracked_insert;
447 --
448 /*
449 Name
450 zap_object_group
451 Description
452
453 This is purges an assignment processing group from the
454 system, then removes the person object groups if no
455 other assignment processing groups exist for it.
456 */
457
458 procedure zap_object_group(p_object_group_id in number)
459 is
460 l_parent_object_group_id pay_object_groups.parent_object_group_id%type;
461 l_cnt number;
462 begin
463 --
464 select parent_object_group_id
465 into l_parent_object_group_id
466 from pay_object_groups
467 where object_group_id = p_object_group_id;
468 --
469 if (l_parent_object_group_id is not null) then
470 --
471 -- Find out how many object groups are on the
472 -- parent. If its only one then delete it.
473 -- Since its the current object group.
474 --
475 select count(*)
476 into l_cnt
477 from pay_object_groups
478 where parent_object_group_id = l_parent_object_group_id;
479 --
480 if (l_cnt = 1) then
481 delete from pay_object_groups
482 where object_group_id = l_parent_object_group_id;
483 end if;
484 end if;
485 --
486 delete from pay_object_groups
487 where object_group_id = p_object_group_id;
488 --
489 end zap_object_group;
490 --
491 /*
492 Name
493 end_date_object_group
494 Description
495
496 This is end dates an object group.
497 */
498
499 procedure end_date_object_group(p_object_group_id in number,
500 p_effective_end_date in date)
501 is
502 begin
503 --
504 update pay_object_groups
505 set end_date = p_effective_end_date
506 where object_group_id = p_object_group_id;
507 --
508 end end_date_object_group;
509 --
510 /*
511 Name
512 asg_datetracked_delete_next
513 Description
514
515 This is called from the Assignment Dynamic Triggers
516 to maintain the assignment groups
517 */
518 procedure asg_datetracked_delete_next(p_assignment_id in number,
519 p_effective_end_date in date,
520 p_effective_end_date_o in date
521 )
522 is
523 cursor get_pogpaf(p_assignment_id number,
524 p_effective_end_date_o date)
525 is
526 select object_group_id,
527 parent_object_group_id,
528 start_date,
529 end_date
530 from pay_object_groups pog_paf
531 where pog_paf.source_id = p_assignment_id
532 and pog_paf.source_type = 'PAF'
533 and pog_paf.end_date = p_effective_end_date_o;
534 --
535 cursor get_pogppf(p_par_object_group_id number,
536 p_effective_end_date_o date)
537 is
538 select object_group_id,
539 start_date,
540 end_date
541 from pay_object_groups pog_paf
542 where object_group_id = p_par_object_group_id
546 for pogrec in get_pogpaf(p_assignment_id, p_effective_end_date_o) loop
543 and pog_paf.end_date = p_effective_end_date_o;
544 begin
545 --
547 --
548 update pay_object_groups
549 set end_date = p_effective_end_date
550 where object_group_id = pogrec.object_group_id;
551 --
552 for ppfrec in get_pogppf(pogrec.parent_object_group_id,
553 p_effective_end_date_o)
554 loop
555 --
556 update pay_object_groups
557 set end_date = p_effective_end_date
558 where object_group_id = ppfrec.object_group_id;
559 --
560 end loop;
561 --
562 end loop;
563 --
564
565 --
566 end asg_datetracked_delete_next;
567 --
568 /*
569 Name
570 asg_datetracked_end_date
571 Description
572
573 This is called from the Assignment Dynamic Triggers
574 to maintain the assignment groups
575 */
576 procedure asg_datetracked_end_date(p_assignment_id in number,
577 p_effective_end_date in date
578 )
579 is
580 cursor get_pogpaf(p_assignment_id number,
581 p_effective_end_date date)
582 is
583 select object_group_id,
584 start_date,
585 end_date
586 from pay_object_groups pog_paf
587 where pog_paf.source_id = p_assignment_id
588 and pog_paf.source_type = 'PAF'
589 and pog_paf.end_date > p_effective_end_date
590 order by start_date;
591 begin
592 --
593 -- OK the assignment has ended, we need to end all the
594 -- POG PAFs that the assignment has.
595 for pafrec in get_pogpaf(p_assignment_id, p_effective_end_date) loop
596 --
597 if (pafrec.start_date > p_effective_end_date) then
598 zap_object_group(pafrec.object_group_id);
599 else
600 end_date_object_group(pafrec.object_group_id,
601 p_effective_end_date);
602 end if;
603 --
604 end loop;
605 --
606 end asg_datetracked_end_date;
607 --
608 /*
609 Name
610 find_asg_group_definition
611 Description
612
613 This retrieves the group name for an assignment group.
614 */
615 procedure find_asg_group_definition(p_assignment_id in number,
616 p_payroll_id in number,
617 p_effective_date in date,
618 p_definition_name out nocopy varchar2)
619 is
620 begin
621 --
622 select pgd.name
623 into p_definition_name
624 from pay_group_definitions pgd,
625 pay_object_groups pog_ppf,
626 pay_object_groups pog_paf
627 where pog_paf.source_id = p_assignment_id
628 and pog_paf.source_type = 'PAF'
629 and pog_paf.payroll_id = p_payroll_id
630 and p_effective_date between pog_paf.start_date
631 and pog_paf.end_date
632 and pog_paf.parent_object_group_id = pog_ppf.object_group_id
633 and pog_ppf.group_definition_id = pgd.group_definition_id;
634 --
635 end find_asg_group_definition;
636 --
637 /*
638 Name
639 asg_datetracked_update
640 Description
641
642 This is called from the Assignment Dynamic Triggers
643 to maintain the assignment groups
644 */
645
646 procedure asg_datetracked_update(p_assignment_id in number,
647 p_person_id in number,
648 p_period_of_service_id in number,
649 p_old_effective_start_date in date,
650 p_old_effective_end_date in date,
651 p_new_effective_start_date in date,
652 p_new_effective_end_date in date,
653 p_old_payroll_id in number,
654 p_new_payroll_id in number
655 )
656 is
657 l_old_group_name pay_group_definitions.name%type;
658 l_new_group_name pay_group_definitions.name%type;
659 begin
660 --
661 if ( p_old_payroll_id is null
662 and p_new_payroll_id is null) then
663 --
664 return;
665 --
666 elsif ( p_old_payroll_id is null
667 and p_new_payroll_id is not null) then
668 --
669 generate_asg_group(p_assignment_id,
670 p_person_id,
671 p_period_of_service_id,
672 p_new_effective_start_date,
673 p_new_effective_end_date,
674 p_new_payroll_id
675 );
676 elsif ( p_old_payroll_id is not null
677 and p_new_payroll_id is null) then
678 --
679 return;
680 --
681 elsif (p_old_payroll_id = p_new_payroll_id) then
682 --
683 find_asg_group_definition(p_assignment_id => p_assignment_id,
684 p_payroll_id => p_old_payroll_id,
685 p_effective_date => p_old_effective_end_date,
686 p_definition_name => l_old_group_name
687 );
688 --
689 evaluate_asg_group(p_assignment_id,
690 p_new_effective_start_date,
691 p_new_effective_end_date,
692 l_new_group_name
693 );
694 if (l_old_group_name <> l_new_group_name) then
695 generate_asg_group(p_assignment_id,
699 p_new_effective_end_date,
696 p_person_id,
697 p_period_of_service_id,
698 p_new_effective_start_date,
700 p_new_payroll_id
701 );
702 end if;
703 --
704 else
705 generate_asg_group(p_assignment_id,
706 p_person_id,
707 p_period_of_service_id,
708 p_new_effective_start_date,
709 p_new_effective_end_date,
710 p_new_payroll_id
711 );
712
713 end if;
714 --
715 end asg_datetracked_update;
716 --
717 /*
718 Name
719 asg_datetracked_strt_early
720 Description
721 This is called from the Assignment Dynamic Triggers
722 to maintain the assignment groups
723 */
724
725 procedure asg_datetracked_strt_early(p_assignment_id in number,
726 p_person_id in number,
727 p_period_of_service_id in number,
728 p_old_effective_start_date in date,
729 p_old_effective_end_date in date,
730 p_new_effective_start_date in date,
731 p_new_effective_end_date in date,
732 p_old_payroll_id in number,
733 p_new_payroll_id in number
734 )
735 is
736 begin
737 --
738 update pay_object_groups
739 set start_date = p_new_effective_start_date
740 where source_id = p_assignment_id
741 and source_type = 'PAF'
742 and start_date = p_old_effective_start_date;
743 --
744 end asg_datetracked_strt_early;
745
746 /*
747 Name
748 asg_datetracked_ovrr_update
749 Description
750
751 This is called from the Assignment Dynamic Triggers
752 to maintain the assignment groups
753 */
754
755 procedure asg_datetracked_ovrr_update(p_assignment_id in number,
756 p_person_id in number,
757 p_period_of_service_id in number,
758 p_old_effective_start_date in date,
759 p_old_effective_end_date in date,
760 p_new_effective_start_date in date,
761 p_new_effective_end_date in date,
762 p_old_payroll_id in number,
763 p_new_payroll_id in number
764 )
765 is
766 cursor get_pogpaf(p_assignment_id number,
767 p_effective_end_date date)
768 is
769 select object_group_id,
770 start_date,
771 end_date
772 from pay_object_groups pog_paf
773 where pog_paf.source_id = p_assignment_id
774 and pog_paf.source_type = 'PAF'
775 and pog_paf.end_date > p_effective_end_date;
776 begin
777 --
778 asg_datetracked_update(p_assignment_id,
779 p_person_id,
780 p_period_of_service_id,
781 p_old_effective_start_date,
782 p_old_effective_end_date,
786 p_new_payroll_id
783 p_new_effective_start_date,
784 p_new_effective_end_date,
785 p_old_payroll_id,
787 );
788 --
789 -- Any POG_PAF that starts after the new start date
790 -- then they must be removed as a they have been
791 -- overriden
792 for pafrec in get_pogpaf(p_assignment_id, p_new_effective_end_date) loop
793 --
794 if (pafrec.start_date > p_new_effective_end_date) then
795 zap_object_group(pafrec.object_group_id);
796 end if;
797 --
798 end loop;
799 --
800 end asg_datetracked_ovrr_update;
801 --
802 /*
803 Name
804 asg_datetracked_zap
805 Description
806
807 This is called from the Assignment Dynamic Triggers
808 to maintain the assignment groups
809 */
810
811 procedure asg_datetracked_zap(p_assignment_id in number)
812 is
813 cursor get_pogpaf(p_assignment_id number)
814 is
815 select object_group_id,
816 start_date,
817 end_date
818 from pay_object_groups pog_paf
819 where pog_paf.source_id = p_assignment_id
820 and pog_paf.source_type = 'PAF';
821 begin
822 --
823 -- all POG_PAF for the assignment must be removed
824 for pafrec in get_pogpaf(p_assignment_id) loop
825 --
826 zap_object_group(pafrec.object_group_id);
827 --
828 end loop;
829 --
830
831 end;
832
833 /*
834 Name
835 asg_insert_trigger
836 Description
837
838 This is the Dynamic Trigger Code for Assignment Processing
839 Groups
840 */
841
842 procedure asg_insert_trigger(p_assignment_id in number,
843 p_person_id in number,
844 p_period_of_service_id in number,
845 p_new_effective_start_date in date,
846 p_new_effective_end_date in date,
847 p_new_payroll_id in number,
848 p_business_group_id in number
849 )
850 is
851 dt_mode varchar2(20);
852 begin
853 --
854 if (is_pog_enable(p_business_group_id) = TRUE) then
855 asg_datetracked_insert(p_assignment_id,
856 p_person_id,
857 p_period_of_service_id,
858 p_new_effective_start_date,
859 p_new_effective_end_date,
860 p_new_payroll_id
861 );
862 end if;
863 --
864 end asg_insert_trigger;
865
866 /*
867 Name
868 asg_update_trigger
869 Description
870
871 This is the Dynamic Trigger Code for Assignment Processing
872 Groups
873 */
874
875 procedure asg_update_trigger(p_assignment_id in number,
876 p_person_id in number,
877 p_period_of_service_id in number,
878 p_old_effective_start_date in date,
879 p_old_effective_end_date in date,
880 p_new_effective_start_date in date,
881 p_new_effective_end_date in date,
882 p_old_payroll_id in number,
883 p_new_payroll_id in number,
884 p_business_group_id in number
885 )
886 is
887 dt_mode varchar2(20);
888 begin
889 --
890 if (is_pog_enable(p_business_group_id) = TRUE) then
891 --
892 -- First set the Date Track mode
893 --
894 dt_mode := PAY_POG_ALL_ASSIGNMENTS_PKG.dyt_mode;
895 --
896 if ( dt_mode = 'CORRECTION'
897 or dt_mode = 'UPDATE'
898 or dt_mode = 'UPDATE_CHANGE_INSERT') then
899 --
900 asg_datetracked_update(p_assignment_id,
901 p_person_id,
902 p_period_of_service_id,
903 p_old_effective_start_date,
904 p_old_effective_end_date,
905 p_new_effective_start_date,
906 p_new_effective_end_date,
907 p_old_payroll_id,
908 p_new_payroll_id
909 );
910 --
911 elsif ( dt_mode = 'UPDATE_OVERRIDE') then
912 --
913 asg_datetracked_ovrr_update(p_assignment_id,
914 p_person_id,
915 p_period_of_service_id,
916 p_old_effective_start_date,
917 p_old_effective_end_date,
918 p_new_effective_start_date,
919 p_new_effective_end_date,
920 p_old_payroll_id,
921 p_new_payroll_id
922 );
923 --
924 elsif ( dt_mode = 'START_EARLIER') then
925 --
926 asg_datetracked_strt_early(p_assignment_id,
927 p_person_id,
928 p_period_of_service_id,
929 p_old_effective_start_date,
930 p_old_effective_end_date,
931 p_new_effective_start_date,
932 p_new_effective_end_date,
933 p_old_payroll_id,
934 p_new_payroll_id
938 end if;
935 );
936 --
937 end if;
939 --
940 end asg_update_trigger;
941 --
942 /*
943 Name
944 asg_delete_trigger
945 Description
946
947 This is the Dynamic Trigger Code for Assignment Processing
948 Groups
949 */
950
951 procedure asg_delete_trigger(p_assignment_id in number,
952 p_effective_end_date in date,
953 p_business_group_id in number,
954 p_effective_end_date_o in date
955 )
956 is
957 dt_mode varchar2(20);
958 begin
959 --
960 if (is_pog_enable(p_business_group_id) = TRUE) then
961 --
962 -- First set the Date Track mode
963 --
964 dt_mode := PAY_POG_ALL_ASSIGNMENTS_PKG.dyt_mode;
965 --
966 if ( dt_mode = 'ZAP') then
967 --
968 asg_datetracked_zap(p_assignment_id);
969 --
970 elsif ( dt_mode = 'DELETE') then
971 --
972 asg_datetracked_end_date(p_assignment_id,
973 p_effective_end_date
974 );
975 --
976 elsif ( dt_mode = 'FUTURE_CHANGE'
977 or dt_mode = 'DELETE_NEXT_CHANGE') then
978 asg_datetracked_delete_next(p_assignment_id,
979 p_effective_end_date,
980 p_effective_end_date_o
981 );
982 end if;
983 end if;
984 --
985 end asg_delete_trigger;
986 --
987 /*
988 Name
989 upgrade_asg
990 Description
991
992 Upgrades existing data to have process groups. This is used by
993 the generic upgrade process to convert existing legislations.
994 */
995 procedure upgrade_asg(p_asg_id in number)
996 is
997 --
998 cursor get_asg_info(p_asg_id in number)
999 is
1000 select assignment_id,
1001 person_id,
1002 period_of_service_id,
1003 payroll_id,
1004 effective_start_date,
1005 effective_end_date,
1006 business_group_id
1007 from per_all_assignments_f
1008 where assignment_id = p_asg_id
1009 order by assignment_id, effective_start_date;
1010 --
1011 asg_id per_all_assignments_f.assignment_id%type;
1012 per_id per_all_assignments_f.person_id%type;
1013 pos_id per_all_assignments_f.period_of_service_id%type;
1014 pay_id per_all_assignments_f.payroll_id%type;
1015 effective_start_date date;
1016 effective_end_date date;
1017 first_row boolean;
1018 --
1019 begin
1020 --
1021 first_row := TRUE;
1022 for asgrec in get_asg_info(p_asg_id) loop
1023 --
1024 if (first_row = TRUE) then
1025 --
1026 PAY_POG_ALL_ASSIGNMENTS_PKG.dyt_mode := 'INSERT';
1027 asg_insert_trigger
1028 (p_assignment_id => asgrec.assignment_id,
1029 p_person_id => asgrec.person_id,
1030 p_period_of_service_id => asgrec.period_of_service_id,
1031 p_new_effective_start_date => asgrec.effective_start_date,
1032 p_new_effective_end_date => asgrec.effective_end_date,
1033 p_new_payroll_id => asgrec.payroll_id,
1034 p_business_group_id => asgrec.business_group_id
1035 );
1036 asg_id := asgrec.assignment_id;
1037 per_id := asgrec.person_id;
1038 pos_id := asgrec.period_of_service_id;
1039 pay_id := asgrec.payroll_id;
1040 effective_start_date := asgrec.effective_start_date;
1041 effective_end_date := asgrec.effective_end_date;
1042 first_row := FALSE;
1043 --
1044 else
1045 --
1046 PAY_POG_ALL_ASSIGNMENTS_PKG.dyt_mode := 'UPDATE';
1047 asg_update_trigger
1048 (p_assignment_id => asgrec.assignment_id,
1049 p_person_id => asgrec.person_id,
1050 p_period_of_service_id => asgrec.period_of_service_id,
1051 p_old_effective_start_date => effective_start_date,
1052 p_old_effective_end_date => effective_end_date,
1053 p_new_effective_start_date => asgrec.effective_start_date,
1054 p_new_effective_end_date => asgrec.effective_end_date,
1055 p_old_payroll_id => pay_id,
1056 p_new_payroll_id => asgrec.payroll_id,
1057 p_business_group_id => asgrec.business_group_id
1058 );
1059 --
1060 asg_id := asgrec.assignment_id;
1061 per_id := asgrec.person_id;
1062 pos_id := asgrec.period_of_service_id;
1063 pay_id := asgrec.payroll_id;
1064 effective_start_date := asgrec.effective_start_date;
1065 effective_end_date := asgrec.effective_end_date;
1066 first_row := FALSE;
1067 --
1068 end if;
1069 --
1070 end loop;
1071 --
1072 end upgrade_asg;
1073 --
1074 end pay_asg_process_grp_pkg;