1 package body hr_nonrun_asact as
2 /* $Header: pynonrun.pkb 120.15.12010000.2 2008/10/01 06:14:06 ankagarw ship $ */
3 --
4 --
5 g_lckhandle varchar2(128);
6 cached boolean := FALSE;
7 g_many_procs_in_period varchar2(80);
8 g_plsql_proc_insert varchar2(80);
9 g_set_date_earned pay_action_parameters.parameter_value%type;
10 g_contrib_payments_exist boolean := null;
11 --
12 -------------------------------- rangerow ----------------------------------
13 /*
14 NAME
15 update_pact - update payroll action row.
16 DESCRIPTION
17 Updates relevant information on the payroll action row.
18 This includes the action_population_status and the
19 date_earned value. This is obtained in accordance with
20 the new period dates fix.
21 NOTES
22 <none>
23 */
24 procedure update_pact
25 (
26 p_payroll_action_id in number,
27 p_action_population_status in varchar2,
28 p_action_type in varchar2,
29 p_last_update_date in date,
30 p_last_updated_by in number,
31 p_last_update_login in number
32 ) is
33 l_date_earned date;
34 begin
35 if (g_set_date_earned = 'Y') then
36 --
37 select /*+ USE_NL(locked_pact locked locking locks)*/
38 max(date_earned)
39 into l_date_earned
40 from pay_payroll_actions locked_pact,
41 pay_assignment_actions locked,
42 pay_assignment_actions locking,
43 pay_action_interlocks locks
44 where locking.payroll_action_id = p_payroll_action_id
45 and locking.assignment_action_id = locks.locking_action_id
46 and locked.assignment_action_id = locks.locked_action_id
47 and locked.payroll_action_id = locked_pact.payroll_action_id;
48 --
49 else
50 l_date_earned := null;
51 end if;
52 --
53 if (p_action_type in ('R', 'Q', 'B', 'V')) then
54 update pay_payroll_actions pac
55 set pac.action_population_status = p_action_population_status,
56 pac.last_update_date = p_last_update_date,
57 pac.last_updated_by = p_last_updated_by,
58 pac.last_update_login = p_last_update_login
59 where pac.payroll_action_id = p_payroll_action_id;
60 elsif (p_action_type not in ('X', 'H')) then
61 update pay_payroll_actions pac
62 set pac.action_population_status = p_action_population_status,
63 pac.last_update_date = p_last_update_date,
64 pac.last_updated_by = p_last_updated_by,
65 pac.last_update_login = p_last_update_login,
66 pac.date_earned = l_date_earned
67 where pac.payroll_action_id = p_payroll_action_id;
68 else
69 update pay_payroll_actions pac
70 set pac.action_population_status = p_action_population_status,
71 pac.last_update_date = p_last_update_date,
72 pac.last_updated_by = p_last_updated_by,
73 pac.last_update_login = p_last_update_login,
74 pac.date_earned = l_date_earned
75 where pac.payroll_action_id = p_payroll_action_id;
76 end if;
77 --
78 end update_pact;
79 --
80 ---------------------------get_next_pop_chunk_seq-------------------------
81 /*
82 NAME
83 get_next_pop_chunk - Get the Next Popultaion chunk by Sequence
84 DESCRIPTION
85 Locks and returns person range information from
86 pay_population_ranges. This is used to insert
87 a chunk of assignments at a time.
88 NOTES
89 <none>
90 */
91 procedure get_next_pop_chunk_seq
92 (
93 pactid in number, -- payroll_action_id.
94 atype in varchar2, -- action type.
95 p_lckhandle in varchar2, -- dbms_lock id
96 lub in varchar2, -- last_updated_by.
97 lul in varchar2, -- last_update_login.
98 stperson out nocopy number, -- starting_person_id.
99 endperson out nocopy number, -- ending_person_id.
100 chunk out nocopy number, -- chunk_number.
101 rand_chunk out nocopy number -- chunk_number.
102 ) is
103 actpopstat varchar2(30);
104 norows boolean; -- used to decide if sql stat has returned rows.
105 dummy number; -- need because must select into something.
106 found boolean;
107 ret number;
108 --
109 begin
110 -- get current action_population_status.
111 found := FALSE;
112 while (found = FALSE) loop
113
114 /* First thing to do is get a lock before entering the
115 critical section
116 */
117 ret := dbms_lock.request(
118 lockhandle => p_lckhandle,
119 lockmode => dbms_lock.x_mode,
120 release_on_commit => TRUE);
121 --
122 if (ret <> 0) then
123 hr_utility.set_message(801,'HR_289135_NO_LOCK_GAINED');
124 hr_utility.set_message_token('LOCKNAME','PAY_PAYROLL_ACTIONS_'||pactid);
125 hr_utility.set_message_token('LOCKERR',ret);
126 hr_utility.raise_error;
127 end if;
128 --
129 select pac.action_population_status
130 into actpopstat
131 from pay_payroll_actions pac
132 where pac.payroll_action_id = pactid;
133 --
134 -- only bother to process if status is not complete.
135 if(actpopstat <> 'C'and actpopstat <> 'A' and actpopstat <> 'E') then
136 -- select a range row for update.
137 begin
138 norows := FALSE;
139 -- check to see if want to use randomised chnks or sequential
140 --
141 select rge.starting_person_id,
142 rge.ending_person_id,
143 rge.chunk_number,
144 nvl(rge.rand_chunk_number,rge.chunk_number)
145 into stperson,
146 endperson,
147 chunk,
148 rand_chunk
149 from pay_population_ranges rge
150 where rge.payroll_action_id = pactid
151 and rge.range_status = 'U'
152 and rownum < 2;
153 found := TRUE;
154 --
155 exception
156 when no_data_found then norows := TRUE;
157 --
158 when others then
159 rollback;
160 raise;
161 end;
162 --
163 -- if no rows remain unprocessed.
164 if(norows) then
165 -- see if there are any rows at all.
166 -- there may be if other processes are still
167 -- inserting assignment actions.
168 begin
169 norows := FALSE;
170 --
171 select null
172 into dummy
173 from pay_population_ranges rge
174 where rge.payroll_action_id = pactid
175 and rownum < 2;
176 --
177 exception
178 when no_data_found then
179 norows := TRUE;
180 end;
181 --
182 -- if there are no rows at all, i.e. no one is
183 -- doing any processing, indicate everything is done.
184 -- This should only be done if the Range code has finished
185 -- processing.
186 if(norows) then
187 if (actpopstat <> 'R') then
188 update_pact(pactid, 'A', atype,sysdate,lub,lul);
189 found := TRUE;
190 --
191 end if;
192 commit;
193 chunk := NULL;
194 else
195 chunk := NULL;
196 /* Release dbms_lock */
197 commit;
198 end if;
199 end if;
200 else
201
202 -- see if there any Errored rows
203
204 if(actpopstat = 'E') then
205 -- raise the error to cause death of thread
206
207 rollback;
208 hr_utility.set_message(801,'HR_34988_TERMINATE_THREAD');
209 hr_utility.raise_error;
210
211 else
212 chunk := NULL; -- nothing left to process.
213 found := TRUE;
214 /* Release dbms_lock */
215 commit;
216 end if;
217 end if;
218 end loop;
219 end get_next_pop_chunk_seq;
220 --
221 ---------------------------lock_pop_chunk----------------------
222 /*
223 NAME
224 lock_pop_chunk - Lock population Chunk
225 DESCRIPTION
226 This locks the population Chunk using the PAY_CHUNK_STATUS table
227 NOTES
228 <none>
229 */
230 procedure lock_pop_chunk
231 (
232 pactid in number, -- payroll_action_id.
233 p_next_chunk in number, -- Chunk to be locked
234 p_found in out nocopy boolean, -- Able to lock row.
235 stperson out nocopy number, -- starting_person_id.
236 endperson out nocopy number, -- ending_person_id.
237 chunk out nocopy number, -- chunk_number.
238 rand_chunk out nocopy number -- chunk_number.
239 ) is
240 l_lckhandle varchar2(128);
241 ret number;
242 chk_pop_status pay_chunk_status.population_status%type;
243 act_pop_status pay_payroll_actions.action_population_status%type;
244 begin
245 /* OK we have the next chunk lets lock it and confirm that its
246 unprocessed
247 */
248 --
249 dbms_lock.allocate_unique(
250 lockname => 'PAY_CHUNK_STATUS'||pactid||'_'||p_next_chunk,
251 lockhandle => l_lckhandle);
252 --
253 ret := dbms_lock.request(
254 lockhandle => l_lckhandle,
255 lockmode => dbms_lock.x_mode,
256 release_on_commit => TRUE);
257 if (ret <> 0) then
258 hr_utility.set_message(801,'HR_289135_NO_LOCK_GAINED');
259 hr_utility.set_message_token('LOCKNAME',
260 'PAY_CHUNK_STATUS'||pactid||'_'||p_next_chunk);
261 hr_utility.set_message_token('LOCKERR',ret);
262 hr_utility.raise_error;
263 end if;
264 --
265 /* OK we need to be careful here.
266 We are looping through the chunks
267 to populate them, but the status columns
268 belong to the processing side of the code.
269 This causes a problem with randomisation
270 We need to update the randonised status columns
271 */
272 select pcs_rand.population_status,
273 ppa.action_population_status
274 into chk_pop_status,
275 act_pop_status
276 from pay_payroll_actions ppa,
277 pay_chunk_status pcs_pop,
278 pay_chunk_status pcs_rand
279 where pcs_pop.payroll_action_id = pactid
280 and pcs_pop.chunk_number = p_next_chunk
281 and pcs_rand.payroll_action_id = pcs_pop.payroll_action_id
282 and pcs_rand.chunk_number = pcs_pop.rand_chunk_number
283 and ppa.payroll_action_id = pcs_pop.payroll_action_id;
284 --
285 if ( act_pop_status <> 'C'
286 and act_pop_status <> 'A'
287 and act_pop_status <> 'E') then
288 --
289 /* If the chunk is unprocessed then process it
290 other wise look to mark the population status
291 */
292
293 if (chk_pop_status = 'U') then
294 --
295 select rge.starting_person_id,
296 rge.ending_person_id,
297 rge.chunk_number,
298 nvl(rge.rand_chunk_number,rge.chunk_number)
299 into
300 stperson,
301 endperson,
302 chunk,
303 rand_chunk
304 from pay_population_ranges rge
305 where rge.payroll_action_id = pactid
306 and rge.chunk_number = p_next_chunk
307 and rownum = 1;
308 --
309 p_found := TRUE;
310 --
311 else
312 --
313 /* Another thread must have processed the chunk */
314 --
315 chunk := NULL;
316 /* Release dbms_lock */
317 commit;
318 end if;
319 --
320 else
321 -- see if there any Errored rows
322
323 if(act_pop_status = 'E') then
324 -- raise the error to cause death of thread
325
326 rollback;
327 hr_utility.set_message(801,'HR_34988_TERMINATE_THREAD');
328 hr_utility.raise_error;
329
330 else
331 chunk := NULL; -- nothing left to process.
332 p_found := TRUE;
333 /* Release dbms_lock */
334 commit;
335 end if;
336 end if;
337 end lock_pop_chunk;
338 --
339 ---------------------------get_next_pop_chunk_unalloc----------------------
340 /*
341 NAME
342 get_next_pop_chunk_unalloc - Get the Next Popultaion chunk by
343 Unalloaction
344 DESCRIPTION
345 Use the Unallocation method to get the next chunk.
346 NOTES
347 <none>
348 */
349 procedure get_next_pop_chunk_unalloc
350 (
351 pactid in number, -- payroll_action_id.
352 atype in varchar2, -- action type.
353 p_lckhandle in varchar2, -- dbms_lock id for pactid
354 lub in varchar2, -- last_updated_by.
355 lul in varchar2, -- last_update_login.
356 stperson out nocopy number, -- starting_person_id.
357 endperson out nocopy number, -- ending_person_id.
358 chunk out nocopy number, -- chunk_number.
359 rand_chunk out nocopy number -- chunk_number.
360 ) is
361 next_chunk number;
362 found boolean;
363 l_lckhandle varchar2(128);
364 ret number;
365 act_pop_status pay_payroll_actions.action_population_status%type;
366 norows boolean; -- used to decide if sql stat has returned rows.
367 dummy number; -- need because must select into something.
368 begin
369 --
370 found := FALSE;
371 while (found = FALSE) loop
372 --
373 select max(chunk_number)
374 into next_chunk
375 from pay_chunk_status
376 where payroll_action_id = pactid
377 and population_status = 'U';
378 --
379 if (next_chunk is null) then
380 --
381 -- There doesn't seem to be any rows left to process
382 -- hence lock the payroll action, and update the population
383 -- status, when there are no rows
384 --
385 /* First thing to do is get a lock before entering the
386 critical section
387 */
388 ret := dbms_lock.request(
389 lockhandle => p_lckhandle,
390 lockmode => dbms_lock.x_mode,
391 release_on_commit => TRUE);
392 --
393 if (ret <> 0) then
394 hr_utility.set_message(801,'HR_289135_NO_LOCK_GAINED');
395 hr_utility.set_message_token('LOCKNAME','PAY_PAYROLL_ACTIONS_'||pactid);
396 hr_utility.set_message_token('LOCKERR',ret);
397 hr_utility.raise_error;
398 end if;
399 --
400 select
401 ppa.action_population_status
402 into
403 act_pop_status
404 from pay_payroll_actions ppa
405 where ppa.payroll_action_id = pactid;
406 --
407 if ( act_pop_status <> 'C'
408 and act_pop_status <> 'A'
409 and act_pop_status <> 'E') then
410 --
411 -- see if there are any rows at all.
412 -- there may be if other processes are still
413 -- inserting assignment actions.
414 begin
415 norows := FALSE;
416 --
417 select null
418 into dummy
419 from pay_population_ranges rge
420 where rge.payroll_action_id = pactid
421 and rownum < 2;
422 --
423 exception
424 when no_data_found then
425 norows := TRUE;
426 end;
427 --
428 -- if there are no rows at all, i.e. no one is
429 -- doing any processing, indicate everything is done.
430 -- This should only be done if the Range code has finished
431 -- processing.
432 if(norows) then
433 if (act_pop_status <> 'R') then
434 update_pact(pactid, 'A', atype,sysdate,lub,lul);
435 found := TRUE;
436 --
437 end if;
438 commit;
439 chunk := NULL;
440 else
441 chunk := NULL;
442 /* Release dbms_lock */
443 commit;
444 end if;
445 --
446 else
447 -- see if there any Errored rows
448
449 if(act_pop_status = 'E') then
450 -- raise the error to cause death of thread
451
452 rollback;
453 hr_utility.set_message(801,'HR_34988_TERMINATE_THREAD');
454 hr_utility.raise_error;
455
456 else
457 chunk := NULL; -- nothing left to process.
458 found := TRUE;
459 /* Release dbms_lock */
460 commit;
461 end if;
462 end if;
463 --
464 else
465 --
466 lock_pop_chunk
467 (
468 pactid => pactid,
469 p_next_chunk => next_chunk,
470 p_found => found,
471 stperson => stperson,
472 endperson => endperson,
473 chunk => chunk,
474 rand_chunk => rand_chunk
475 );
476 --
477 end if;
478 --
479 end loop;
480 end get_next_pop_chunk_unalloc;
481 --
482 ---------------------------get_next_pop_chunk_prealloc----------------------
483 /*
484 NAME
485 get_next_pop_chunk_prealloc - Get the Next Popultaion chunk by
486 Prealloaction
487 DESCRIPTION
488 Use the Preallocation method to get the next chunk.
489 NOTES
490 <none>
491 */
492 procedure get_next_pop_chunk_prealloc
493 (
494 pactid in number, -- payroll_action_id.
495 atype in varchar2, -- action type.
496 p_lckhandle in varchar2, -- dbms_lock id for pact
497 lub in varchar2, -- last_updated_by.
498 lul in varchar2, -- last_update_login.
499 chunk_type in out nocopy varchar2, -- method for allocating chunk
500 threads in number default 1, -- Number of Threads
501 slave_no in number default 1, -- Slave no
502 curr_chunk in number default 1, -- current chunk
503 max_chunks in number default 9999, -- Max no of Chunks
504 stperson out nocopy number, -- starting_person_id.
505 endperson out nocopy number, -- ending_person_id.
506 chunk out nocopy number, -- chunk_number.
507 rand_chunk out nocopy number -- chunk_number.
508 ) is
509 next_chunk number;
510 found boolean;
511 pay_pop_status pay_payroll_actions.action_population_status%type;
512 chk_pop_status pay_chunk_status.population_status%type;
513 get_paused boolean;
514 begin
515 --
516 found := FALSE;
517 next_chunk := curr_chunk;
518 get_paused := FALSE;
519 while (found = FALSE) loop
520 --
521 if (get_paused <> TRUE) then
522 if (next_chunk = 0 ) then
523 next_chunk := slave_no;
524 else
525 next_chunk := next_chunk + threads;
526 end if;
527 end if;
528 get_paused := FALSE;
529 --
530 select action_population_status
531 into pay_pop_status
532 from pay_payroll_actions
533 where payroll_action_id = pactid;
534 --
535 begin
536 --
537 select population_status
538 into chk_pop_status
539 from pay_chunk_status
540 where payroll_action_id = pactid
541 and chunk_number = next_chunk;
542 --
543 /* Now lock the chunk for processing */
544 --
545 lock_pop_chunk
546 (
547 pactid => pactid,
548 p_next_chunk => next_chunk,
549 p_found => found,
550 stperson => stperson,
551 endperson => endperson,
552 chunk => chunk,
553 rand_chunk => rand_chunk
554 );
555 --
556 exception
557 when no_data_found then
558 --
559 /* If we've processed all our Preallocated
560 chunks, search for any unallocated chunks
561 */
562 --
563 if (pay_pop_status = 'R') then
564 get_paused := TRUE;
565 else
566 get_next_pop_chunk_unalloc
567 (
568 pactid => pactid,
569 atype => atype,
570 p_lckhandle => p_lckhandle,
571 lub => lub,
572 lul => lul,
573 stperson => stperson,
574 endperson => endperson,
575 chunk => chunk,
576 rand_chunk => rand_chunk
577 );
578 chunk_type := 'UNALLOCATED';
579 found := TRUE;
580 end if;
581 end;
582 --
583 end loop;
584 --
585 end get_next_pop_chunk_prealloc;
586 --
587 ---------------------------get_next_pop_chunk----------------------------
588 /*
589 NAME
590 get_next_pop_chunk - Get the Next Popultaion chunk to process
591 DESCRIPTION
592 Locks and returns person range information from
593 pay_population_ranges. This is used to insert
594 a chunk of assignments at a time.
595 NOTES
596 <none>
597 */
598 procedure get_next_pop_chunk
599 (
600 pactid in number, -- payroll_action_id.
601 atype in varchar2, -- action type.
602 p_lckhandle in varchar2, -- dbms_lock id
603 lub in varchar2, -- last_updated_by.
604 lul in varchar2, -- last_update_login.
605 chunk_type in out nocopy varchar2, -- method for allocating chunk
606 threads in number default 1, -- Number of Threads
607 slave_no in number default 1, -- Slave no
608 curr_chunk in number default 1, -- current chunk
609 max_chunks in number default 9999, -- Max no of Chunks
610 stperson out nocopy number, -- starting_person_id.
611 endperson out nocopy number, -- ending_person_id.
612 chunk out nocopy number, -- chunk_number.
613 rand_chunk out nocopy number -- chunk_number.
614 ) is
615 actpopstat varchar2(30);
616 norows boolean; -- used to decide if sql stat has returned rows.
617 dummy number; -- need because must select into something.
618 found boolean;
619 ret number;
620 --
621 begin
622 --
623 if (chunk_type = 'PREALLOCATED') then
624 get_next_pop_chunk_prealloc
625 (
626 pactid => pactid,
627 atype => atype,
628 p_lckhandle => p_lckhandle,
629 lub => lub,
630 lul => lul,
631 chunk_type => chunk_type,
632 threads => threads,
633 slave_no => slave_no,
634 curr_chunk => curr_chunk,
635 max_chunks => max_chunks,
636 stperson => stperson,
637 endperson => endperson,
638 chunk => chunk,
639 rand_chunk => rand_chunk
640 );
641 elsif (chunk_type = 'UNALLOCATED') then
642 get_next_pop_chunk_unalloc
643 (
644 pactid => pactid,
645 atype => atype,
646 p_lckhandle => p_lckhandle,
647 lub => lub,
648 lul => lul,
649 stperson => stperson,
650 endperson => endperson,
651 chunk => chunk,
652 rand_chunk => rand_chunk
653 );
654 else
655 --
656 /* Both ORIGINAL and SEQUENCED use sequenced method */
657 --
658 get_next_pop_chunk_seq(
659 pactid => pactid,
660 atype => atype,
661 p_lckhandle => p_lckhandle,
662 lub => lub,
663 lul => lul,
664 stperson => stperson,
665 endperson => endperson,
666 chunk => chunk,
667 rand_chunk => rand_chunk
668 );
669 end if;
670 --
671 end get_next_pop_chunk;
672 --
673 ---------------------------get_next_pop_chunk_seq-------------------------
674 /*
675 NAME
676 get_next_pop_chunk - Get the Next Process chunk by Sequence
677 DESCRIPTION
678 Use the Sequence method to get the next chunk.
679 NOTES
680 <none>
681 */
682 procedure get_next_proc_chunk_seq
683 (
684 pactid in number, -- payroll_action_id.
685 curr_chunk in out nocopy number -- chunk_number.
686 )
687 is
688 --
689 next_chunk number;
690 pop_chunk_number number;
691 action_status pay_payroll_actions.action_status%type;
692 action_pop_status pay_payroll_actions.action_population_status%type;
693 l_dummy number;
694 found boolean;
695 --
696 begin
697 --
698 found := FALSE;
699 while (found = FALSE) loop
700 --
701 select PAC.current_chunk_number + 1,
702 PAC.action_status,
703 PAC.action_population_status
704 into next_chunk,
705 action_status,
706 action_pop_status
707 from pay_payroll_actions PAC
708 where PAC.payroll_action_id = pactid
709 for update of PAC.current_chunk_number;
710 --
711 if (action_status = 'C') then
712 curr_chunk := 0;
713 found := TRUE;
714 elsif (action_status = 'E') then
715 curr_chunk := 0;
716 hr_utility.set_message(801,'HR_6859_HRPROC_OTHER_PROC_ERR');
717 hr_utility.raise_error;
718 elsif (action_status = 'P') then
719 --
720 declare
721 got_chunk boolean;
722 chk_pop_status pay_chunk_status.population_status%type;
723 begin
724 --
725 select pcs.population_status
726 into chk_pop_status
727 from pay_chunk_status pcs
728 where pcs.payroll_action_id = pactid
729 and pcs.chunk_number = next_chunk;
730 --
731 got_chunk := FALSE;
732 if (chk_pop_status = 'C') then
733 got_chunk := TRUE;
734 elsif (chk_pop_status = 'E') then
735 curr_chunk := 0;
736 hr_utility.set_message(801,'HR_6859_HRPROC_OTHER_PROC_ERR');
737 hr_utility.raise_error;
738 end if;
739 --
740 if (got_chunk = TRUE) then
741 --
742 update pay_payroll_actions pac
743 set pac.current_chunk_number = next_chunk
744 where pac.payroll_action_id = pactid;
745 --
746 update pay_chunk_status
747 set process_status = 'P'
748 where payroll_action_id = pactid
749 and chunk_number = next_chunk;
750 --
751 curr_chunk := next_chunk;
752 found := TRUE;
753 --
754 else
755 --
756 /* Release the lock, let something else try locking the
757 payroll action
758 */
759 rollback;
760 end if;
761 --
762 exception
763 when no_data_found then
764 if (action_pop_status <> 'R') then
765 got_chunk := FALSE;
766 curr_chunk := 0;
767 found := TRUE;
768 end if;
769 --
770 end;
771 else
772 pay_core_utils.assert_condition(
773 'hr_nonrun_asact.get_next_proc_chunk_seq:1',
774 1 = 2);
775 end if;
776 end loop;
777 --
778 end get_next_proc_chunk_seq;
779 --
780 ---------------------------get_next_proc_chunk_unalloc----------------------
781 /*
782 NAME
783 get_next_proc_chunk_unalloc - Get the Next Process chunk by
784 Unalloaction
785 DESCRIPTION
786 Use the Unallocation method to get the next chunk.
787 NOTES
788 */
789 procedure get_next_proc_chunk_unalloc
790 (
791 pactid in number, -- payroll_action_id.
792 curr_chunk in out nocopy number -- Current Chunk
793 ) is
794 next_chunk number;
795 proc_chunk_number number;
796 found boolean;
797 pact_act_status varchar2(30);
798 act_pop_status varchar2(30);
799 chk_status varchar2(30);
800 begin
801 --
802 found := FALSE;
803 while (found = FALSE) loop
804 --
805 select max(chunk_number)
806 into next_chunk
807 from pay_chunk_status
808 where payroll_action_id = pactid
809 and process_status = 'U'
810 and population_status = 'C';
811 --
812 select action_status,
813 action_population_status
814 into pact_act_status,
815 act_pop_status
816 from pay_payrolL_actions
817 where payroll_action_id = pactid;
818 --
819 if (next_chunk is not null) then
820 --
821 select process_status
822 into chk_status
823 from pay_chunk_status
824 where payroll_action_id = pactid
825 and chunk_number = next_chunk
826 for update of process_status;
827 --
828 if (pact_act_status = 'C') then
829 --
830 next_chunk := 0;
831 found := TRUE;
832 --
833 elsif (pact_act_status = 'E') then
834 --
835 hr_utility.set_message(801,'HR_6859_HRPROC_OTHER_PROC_ERR');
836 hr_utility.raise_error;
837 --
838 elsif (pact_act_status = 'P') then
839 --
840 if (chk_status = 'U') then
841 --
842 update pay_chunk_status
843 set process_status = 'P'
844 where payroll_action_id = pactid
845 and chunk_number = next_chunk;
846
847 curr_chunk := next_chunk;
848 found := TRUE;
849 --
850 end if;
851 --
852 else
853 pay_core_utils.assert_condition(
854 'hr_nonrun_asact.get_next_proc_chunk_unalloc:1',
855 1 = 2);
856 end if;
857 else
858 /* Either there is nothing left
859 or a population error has occured
860 or populations not got this far
861 */
862 if ( act_pop_status = 'C'
863 or act_pop_status = 'A'
864 ) then
865 --
866 /* No chunks left
867 */
868 curr_chunk := 0;
869 found := TRUE;
870 --
871 elsif (act_pop_status = 'E') then
872 --
873 hr_utility.set_message(801,'HR_6859_HRPROC_OTHER_PROC_ERR');
874 hr_utility.raise_error;
875 --
876 end if;
877 end if;
878 --
879 end loop;
880 --
881 end get_next_proc_chunk_unalloc;
882 --
883 ---------------------------get_next_proc_chunk_prealloc--------------------
884 /*
885 NAME
886 get_next_proc_chunk_prealloc - Get the Next Process chunk by
887 Prealloaction
888 DESCRIPTION
889 Use the Preallocation method to get the next chunk.
890 NOTES
891 <none>
892 */
893 procedure get_next_proc_chunk_prealloc
894 (
895 pactid in number, -- payroll_action_id.
896 chunk_type in out nocopy varchar2, -- method for allocating chunk
897 threads in number default 1, -- Number of Threads
898 slave_no in number default 1, -- Slave no
899 curr_chunk in out nocopy number -- current chunk
900 ) is
901 next_chunk number;
902 found boolean;
903 pact_act_status pay_payroll_actions.action_status%type;
904 act_pop_status pay_payroll_actions.action_population_status%type;
905 chk_status pay_chunk_status.process_status%type;
906 begin
907 --
908 found := FALSE;
909 next_chunk := curr_chunk;
910 while (found = FALSE) loop
911 --
912 if (next_chunk = 0 ) then
913 next_chunk := slave_no;
914 else
915 next_chunk := next_chunk + threads;
916 end if;
917 --
918 select action_status, action_population_status
919 into pact_act_status,
920 act_pop_status
921 from pay_payroll_actions
922 where payroll_action_id = pactid;
923 --
924 begin
925 --
926 select process_status
927 into chk_status
928 from pay_chunk_status
929 where payroll_action_id = pactid
930 and chunk_number = next_chunk
931 for update of process_status;
932 --
933 if (pact_act_status = 'C') then
934 --
935 next_chunk := 0;
936 found := TRUE;
937 --
938 elsif (pact_act_status = 'E') then
939 --
940 hr_utility.set_message(801,'HR_6859_HRPROC_OTHER_PROC_ERR');
941 hr_utility.raise_error;
942 --
943 elsif (pact_act_status = 'P') then
944 --
945 if (chk_status = 'U') then
946 --
947 update pay_chunk_status
948 set process_status = 'P'
949 where payroll_action_id = pactid
950 and chunk_number = next_chunk;
951 --
952 curr_chunk := next_chunk;
953 found := TRUE;
954 --
955 end if;
956 --
957 else
958 pay_core_utils.assert_condition(
959 'hr_nonrun_asact.get_next_proc_chunk_prealloc:1',
960 1 = 2);
961 end if;
962 --
963 exception
964 when no_data_found then
965 --
966 /* Either there is nothing left thats
967 been preallocated for this thread
968 or a population error has occured
969 or populations not got this far
970 */
971 if ( act_pop_status = 'C'
972 or act_pop_status = 'A'
973 ) then
974 --
975 /* No preallocated left, go for
976 the unallocated
977 */
978 get_next_proc_chunk_unalloc
979 (
980 pactid => pactid,
981 curr_chunk => curr_chunk
982 );
983 --
984 chunk_type := 'UNALLOCATED';
985 found := TRUE;
986 --
987 elsif (act_pop_status = 'E') then
988 --
989 hr_utility.set_message(801,'HR_6859_HRPROC_OTHER_PROC_ERR');
990 hr_utility.raise_error;
991 --
992 end if;
993 end;
994 --
995 end loop;
996 --
997 end get_next_proc_chunk_prealloc;
998 --
999 ---------------------------get_next_proc_chunk----------------------------
1000 /*
1001 NAME
1002 get_next_proc_chunk - Get the Next Process chunk to process
1003 DESCRIPTION
1004 Locks and returns person range information from
1005 pay_population_ranges. This is used to insert
1006 a chunk of assignments at a time.
1007 NOTES
1008 There is a COMMIT in this procedure to release
1009 the locks and update tables.
1010 */
1011 procedure get_next_proc_chunk
1012 (
1013 pactid in number, -- payroll_action_id.
1014 chunk_type in out nocopy varchar2, -- method for allocating chunk
1015 threads in number default 1, -- Number of Threads
1016 slave_no in number default 1, -- Slave no
1017 curr_chunk in out nocopy number -- current chunk
1018 ) is
1019 --
1020 begin
1021 --
1022 -- Before we do any thing mark the previous chunk as complete
1023 if (curr_chunk <> 0) then
1024 --
1025 update pay_chunk_status
1026 set process_status = 'C'
1027 where payroll_action_id = pactid
1028 and chunk_number = curr_chunk;
1029 --
1030 end if;
1031 --
1032 if (chunk_type = 'PREALLOCATED') then
1033 --
1034 get_next_proc_chunk_prealloc
1035 (
1036 pactid => pactid,
1037 chunk_type => chunk_type,
1038 threads => threads,
1039 slave_no => slave_no,
1040 curr_chunk => curr_chunk
1041 );
1042 --
1043 elsif (chunk_type = 'UNALLOCATED') then
1044 --
1045 get_next_proc_chunk_unalloc
1046 (
1047 pactid => pactid,
1048 curr_chunk => curr_chunk
1049 );
1050 --
1051 elsif (chunk_type = 'SEQUENCED') then
1052 --
1053 get_next_proc_chunk_seq
1054 (
1055 pactid => pactid,
1056 curr_chunk => curr_chunk
1057 );
1058 --
1059 else
1060 --
1061 /* Should not get here, ORIGINAL method is done by C Code */
1062 --
1063 pay_core_utils.assert_condition(
1064 'hr_nonrun_asact.get_next_proc_chunk:1',
1065 1 = 2);
1066 --
1067 end if;
1068 --
1069 commit;
1070 --
1071 end get_next_proc_chunk;
1072 --
1073 -------------------------------- rangerow ----------------------------------
1074 /*
1075 NAME
1076 rangerow - return info from range row.
1077 DESCRIPTION
1078 Locks and returns person range information from
1079 pay_population_ranges. This is used to insert
1080 a chunk of assignments at a time.
1081
1082 This is a cover for get_next_pop_chunk
1083 NOTES
1084 <none>
1085 */
1086 procedure rangerow
1087 (
1088 pactid in number, -- payroll_action_id.
1089 lub in varchar2, -- last_updated_by.
1090 lul in varchar2, -- last_update_login.
1091 stperson out nocopy number, -- starting_person_id.
1092 endperson out nocopy number, -- ending_person_id.
1093 chunk out nocopy number, -- chunk_number.
1094 rand_chunk out nocopy number, -- chunk_number.
1095 atype in varchar2 -- action type.
1096 ) is
1097 l_chunk_type varchar2(30);
1098 begin
1099 l_chunk_type := 'ORIGINAL';
1100 get_next_pop_chunk
1101 (
1102 pactid => pactid,
1103 atype => atype,
1104 p_lckhandle => g_lckhandle,
1105 lub => lub,
1106 lul => lul,
1107 chunk_type => l_chunk_type,
1108 stperson => stperson,
1109 endperson => endperson,
1110 chunk => chunk,
1111 rand_chunk => rand_chunk
1112 );
1113 end rangerow;
1114 --
1115 ---------------------------- reinterlock_child -----------------------------
1116 /*
1117 NAME
1118 reinterlock - Re Inserts Interlocks.
1119 DESCRIPTION
1120 Simply re inserts interlock rows for a child action.
1121 NOTES
1122 This procedure recursively calls itself in case the child action
1123 has children of its own.
1124 */
1125 procedure reinterlock_child
1126 (
1127 p_pp_assact number,
1128 p_run_assact number,
1129 p_asg_id number,
1130 p_pact_id number,
1131 p_actype varchar2
1132 ) is
1133 cursor get_lockers (p_run_act number,
1134 p_pre_act number,
1135 p_asg_id number,
1136 p_pact_id number)
1137 is
1138 select paa.assignment_action_id
1139 from pay_assignment_actions paa
1140 where paa.source_action_id = p_run_act
1141 and paa.assignment_id = p_asg_id
1142 and paa.payroll_action_id = p_pact_id
1143 and not exists (select ''
1144 from pay_action_interlocks pai2
1145 where pai2.locking_action_id = p_pre_act
1146 and pai2.locked_action_id = paa.assignment_action_id
1147 );
1148 --
1149 cursor get_cost_lockers (p_cost_act number, p_run_act number)
1150 is
1151 select paa.assignment_action_id
1152 from pay_action_classifications pcl,
1153 pay_payroll_actions pac,
1154 pay_assignment_actions paa,
1155 pay_action_interlocks pai
1156 where pai.locked_action_id = p_run_act
1157 and pai.locking_action_id = paa.assignment_action_id
1158 and paa.assignment_action_id <> p_cost_act
1159 and pac.payroll_action_id = paa.payroll_action_id
1160 and pcl.action_type = pac.action_type
1161 and pcl.classification_name = 'TRANSGL'
1162 and not exists (select ''
1163 from pay_action_interlocks pai2
1164 where pai2.locking_action_id = p_cost_act
1165 and pai2.locked_action_id = paa.assignment_action_id
1166 );
1167 --
1168 begin
1169 --
1170 for locrec in get_lockers(p_run_assact,
1171 p_pp_assact,
1172 p_asg_id,
1173 p_pact_id) loop
1174 --
1175 insint(p_pp_assact, locrec.assignment_action_id);
1176
1177 if (p_actype = 'S') then
1178 for costrec in get_cost_lockers(p_pp_assact, locrec.assignment_action_id) loop
1179 insint(p_pp_assact, costrec.assignment_action_id);
1180 end loop;
1181 end if;
1182 --
1183 -- Now recursively call the procedure to create interlocks for its
1184 -- Child actions.
1185 reinterlock_child(
1186 p_pp_assact,
1187 locrec.assignment_action_id,
1188 p_asg_id,
1189 p_pact_id,
1190 p_actype
1191 );
1192 --
1193 end loop;
1194 --
1195 end reinterlock_child;
1196 --
1197 ---------------------------------- reinterlock ----------------------------------
1198 /*
1199 NAME
1200 reinterlock - Re Inserts Interlocks.
1201 DESCRIPTION
1202 Simply re inserts interlock rows. Based on the primary (master) interlocked
1203 action.
1204 NOTES
1205 <none>
1206 */
1207 procedure reinterlock
1208 (
1209 p_assact number,
1210 p_actype varchar2 default 'U'
1211 ) is
1212 --
1213 cursor get_master_actions(p_act number)
1214 is
1215 select paa.assignment_action_id,
1216 paa.assignment_id,
1217 paa.payroll_action_id
1218 from pay_action_interlocks pai,
1219 pay_assignment_actions paa
1220 where pai.locking_action_id = p_act
1221 and pai.locked_action_id = paa.assignment_action_id
1222 and paa.source_action_id is null;
1223 --
1224 begin
1225 for masterrec in get_master_actions(p_assact) loop
1226 reinterlock_child(p_assact,
1227 masterrec.assignment_action_id,
1228 masterrec.assignment_id,
1229 masterrec.payroll_action_id,
1230 p_actype);
1231 end loop;
1232 end reinterlock;
1233 --
1234 ---------------------------------- insint ----------------------------------
1235 /*
1236 NAME
1237 insint - insert interlock row.
1238 DESCRIPTION
1239 Simply inserts an interlock row. Does not commit.
1240 NOTES
1241 <none>
1242 */
1243 procedure insint
1244 (
1245 lockingactid in number,
1246 lockedactid in number
1247 ) is
1248 begin
1249 insert into pay_action_interlocks (
1250 locking_action_id,
1251 locked_action_id)
1252 values (lockingactid,
1253 lockedactid);
1254 end insint;
1255 --
1256 ---------------------------------- insact ----------------------------------
1257 /*
1258 NAME
1259 insact - insert assignment action row.
1260 DESCRIPTION
1261 inserts row into pay_assignment_actions. Does not commit.
1262 NOTES
1263 <none>
1264 */
1265 procedure insact
1266 (
1267 lockingactid in number, -- locking_action_id.
1268 assignid in number default null, -- assignment_id
1269 pactid in number, -- payroll_action_id
1270 chunk in number, -- chunk_number
1271 greid in number default null, -- GRE id.
1272 prepayid in number default null, -- pre_payment_id.
1273 status in varchar2 default 'U', -- action_status.
1274 source_act in number default null, -- source_action_id
1275 object_id in number default null, -- object id
1276 object_type in varchar2 default null, -- object type
1277 start_date in date default null, -- start date
1278 end_date in date default null, -- end date
1279 p_transient_action in boolean default false -- Transient Action
1280 ) is
1281 --
1282 l_transient_action boolean;
1283 l_action_type pay_payroll_actions.action_type%type;
1284 l_report_type pay_payroll_actions.report_type%type;
1285 l_report_qualifier pay_payroll_actions.report_qualifier%type;
1286 l_report_category pay_payroll_actions.report_category%type;
1287 l_eff_date pay_payroll_actions.effective_date%type;
1288 l_temp_act_flag pay_report_format_mappings_f.temporary_action_flag%type;
1289 --
1290 begin
1291 --
1292 select action_type,
1293 report_type,
1294 report_qualifier,
1295 report_category,
1296 effective_date
1297 into l_action_type,
1298 l_report_type,
1299 l_report_qualifier,
1300 l_report_category,
1301 l_eff_date
1302 from pay_payroll_actions
1303 where payroll_action_id = pactid;
1304 --
1305 l_transient_action := FALSE;
1306 --
1307 if (l_action_type = 'X') then
1308 --
1309 select temporary_action_flag
1310 into l_temp_act_flag
1311 from pay_report_format_mappings_f
1312 where report_type = l_report_type
1313 and report_qualifier = l_report_qualifier
1314 and report_category = l_report_category
1315 and l_eff_date between effective_start_date
1316 and effective_end_date;
1317 --
1318 if (l_temp_act_flag = 'Y') then
1319 l_transient_action := TRUE;
1320 elsif (p_transient_action) then
1321 l_transient_action := TRUE;
1322 end if;
1323 --
1324 end if;
1325 --
1326 if (l_transient_action) then
1327 --
1328 if (object_type not in ('PER', 'ASG', 'PET')) then
1329 --
1330 pay_core_utils.assert_condition(
1331 'hr_nonrun_asact.insact:1',
1332 1 = 2);
1333 --
1334 end if;
1335 --
1336 insert into pay_temp_object_actions (
1337 object_action_id,
1338 object_id,
1339 object_type,
1340 payroll_action_id,
1341 action_status,
1342 chunk_number,
1343 action_sequence,
1344 object_version_number
1345 )
1346 select lockingactid,
1347 object_id,
1348 object_type,
1349 pactid,
1350 status,
1351 chunk,
1352 pay_assignment_actions_s.nextval,
1353 1
1354 from dual;
1355 else
1356 insert into pay_assignment_actions (
1357 assignment_action_id,
1358 assignment_id,
1359 payroll_action_id,
1360 action_status,
1361 chunk_number,
1362 action_sequence,
1363 pre_payment_id,
1364 object_version_number,
1365 tax_unit_id,
1366 source_action_id,
1367 object_id,
1368 object_type,
1369 start_date,
1370 end_date)
1371 select lockingactid,
1372 assignid,
1373 pactid,
1374 status,
1375 chunk,
1376 pay_assignment_actions_s.nextval,
1377 prepayid,
1378 1,
1379 greid,
1380 source_act,
1381 object_id,
1382 object_type,
1383 start_date,
1384 end_date
1385 from dual;
1386 end if;
1387 end insact;
1388 --
1389 --------------------------------- proccash ---------------------------------
1390 /*
1391 NAME
1392 proccash - process a single chunk for cash action.
1393 DESCRIPTION
1394 This function takes a range as defined by the starting and
1395 ending person_id and inserts a chunk of assignment actions
1396 plus their associated interlock rows. This function for the
1397 cash action only.
1398 NOTES
1399 <none>
1400 */
1401 procedure proccash
1402 (
1403 pactid in number, -- payroll_action_id.
1404 stperson in number, -- starting person_id of range.
1405 endperson in number, -- ending person_id of range.
1406 chunk in number, -- current chunk_number.
1407 rand_chunk in number, -- current chunk_number.
1408 itpflg in varchar2, -- legislation type.
1409 use_pop_person in number -- use population_ranges person_id column
1410 ) is
1411 cursor cashpopcur
1412 (
1413 pactid number,
1414 chunk number,
1415 itpflg varchar2
1416 ) is
1417 select /*+ ORDERED
1418 INDEX(pa2 PAY_PAYROLL_ACTIONS_N5)
1419 INDEX(pos PER_PERIODS_OF_SERVICE_N3)
1420 INDEX(as1 PER_ASSIGNMENTS_N4)
1421 INDEX(as2 PER_ASSIGNMENTS_F_PK)
1422 INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
1423 index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
1424 USE_NL(pop pos ppp opm as1 act as2) */
1425 act.assignment_action_id,
1426 act.assignment_id,
1427 act.tax_unit_id,
1428 ppp.pre_payment_id
1429 from pay_payroll_actions pa1,
1430 pay_payroll_actions pa2,
1431 pay_action_classifications pcl,
1432 pay_population_ranges pop,
1433 per_periods_of_service pos,
1434 per_all_assignments_f as1,
1435 pay_assignment_actions act,
1436 per_all_assignments_f as2,
1437 pay_pre_payments ppp,
1438 pay_org_payment_methods_f opm
1439 where pa1.payroll_action_id = pactid
1440 and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
1441 and pa2.effective_date between
1442 pa1.start_date and pa1.effective_date
1443 and act.payroll_action_id = pa2.payroll_action_id
1444 and act.action_status = 'C'
1445 and pcl.classification_name = 'CASHED'
1446 and pa2.action_type = pcl.action_type
1447 and as1.assignment_id = act.assignment_id
1448 and pa2.effective_date between
1449 as1.effective_start_date and as1.effective_end_date
1450 and as2.assignment_id = act.assignment_id
1451 and pa1.effective_date between
1452 as2.effective_start_date and as2.effective_end_date
1453 and as2.payroll_id + 0 = as1.payroll_id + 0
1454 and pos.period_of_service_id = as1.period_of_service_id
1455 and pop.payroll_action_id = pactid
1456 and pop.chunk_number = chunk
1457 and pos.person_id = pop.person_id
1458 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
1459 and ppp.assignment_action_id = act.assignment_action_id
1460 and opm.org_payment_method_id = ppp.org_payment_method_id
1461 and pa1.effective_date between
1462 opm.effective_start_date and opm.effective_end_date
1463 and opm.payment_type_id = pa1.payment_type_id
1464 and (opm.org_payment_method_id = pa1.org_payment_method_id
1465 or pa1.org_payment_method_id is null)
1466 and not exists (
1467 select null
1468 from pay_assignment_actions ac2,
1469 pay_action_interlocks int
1470 where int.locked_action_id = act.assignment_action_id
1471 and ac2.assignment_action_id = int.locking_action_id
1472 and ac2.pre_payment_id = ppp.pre_payment_id)
1473 and not exists (
1474 select null
1475 from per_all_assignments_f as3,
1476 pay_assignment_actions ac3
1477 where itpflg = 'N'
1478 and ac3.payroll_action_id = pa2.payroll_action_id
1479 and ac3.action_status not in ('C', 'S')
1480 and as3.assignment_id = ac3.assignment_id
1481 and pa2.effective_date between
1482 as3.effective_start_date and as3.effective_end_date
1483 and as3.person_id = as2.person_id)
1484 order by act.assignment_id
1485 for update of as1.assignment_id, pos.period_of_service_id;
1486 --
1487 cursor cashcur
1488 (
1489 pactid number,
1490 stperson number,
1491 endperson number,
1492 itpflg varchar2
1493 ) is
1494 select /*+ ORDERED
1495 INDEX(pa2 PAY_PAYROLL_ACTIONS_N5)
1496 INDEX(pos PER_PERIODS_OF_SERVICE_N3)
1497 INDEX(as1 PER_ASSIGNMENTS_N4)
1498 INDEX(as2 PER_ASSIGNMENTS_F_PK)
1499 INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
1500 index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
1501 USE_NL(pos ppp opm as1 act as2) */
1502 act.assignment_action_id,
1503 act.assignment_id,
1504 act.tax_unit_id,
1505 ppp.pre_payment_id
1506 from pay_payroll_actions pa1,
1507 pay_payroll_actions pa2,
1508 pay_action_classifications pcl,
1509 per_periods_of_service pos,
1510 per_all_assignments_f as1,
1511 pay_assignment_actions act,
1512 per_all_assignments_f as2,
1513 pay_pre_payments ppp,
1514 pay_org_payment_methods_f opm
1515 where pa1.payroll_action_id = pactid
1516 and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
1517 and pa2.effective_date between
1518 pa1.start_date and pa1.effective_date
1519 and act.payroll_action_id = pa2.payroll_action_id
1520 and act.action_status = 'C'
1521 and pcl.classification_name = 'CASHED'
1522 and pa2.action_type = pcl.action_type
1523 and as1.assignment_id = act.assignment_id
1524 and pa2.effective_date between
1525 as1.effective_start_date and as1.effective_end_date
1526 and as2.assignment_id = act.assignment_id
1527 and pa1.effective_date between
1528 as2.effective_start_date and as2.effective_end_date
1529 and as2.payroll_id + 0 = as1.payroll_id + 0
1530 and pos.period_of_service_id = as1.period_of_service_id
1531 and pos.person_id between stperson and endperson
1532 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
1533 and ppp.assignment_action_id = act.assignment_action_id
1534 and opm.org_payment_method_id = ppp.org_payment_method_id
1535 and pa1.effective_date between
1536 opm.effective_start_date and opm.effective_end_date
1537 and opm.payment_type_id = pa1.payment_type_id
1538 and (opm.org_payment_method_id = pa1.org_payment_method_id
1539 or pa1.org_payment_method_id is null)
1540 and not exists (
1541 select null
1542 from pay_assignment_actions ac2,
1543 pay_action_interlocks int
1544 where int.locked_action_id = act.assignment_action_id
1545 and ac2.assignment_action_id = int.locking_action_id
1546 and ac2.pre_payment_id = ppp.pre_payment_id)
1547 and not exists (
1548 select null
1549 from per_all_assignments_f as3,
1550 pay_assignment_actions ac3
1551 where itpflg = 'N'
1552 and ac3.payroll_action_id = pa2.payroll_action_id
1553 and ac3.action_status not in ('C', 'S')
1554 and as3.assignment_id = ac3.assignment_id
1555 and pa2.effective_date between
1556 as3.effective_start_date and as3.effective_end_date
1557 and as3.person_id = as2.person_id)
1558 order by act.assignment_id
1559 for update of as1.assignment_id, pos.period_of_service_id;
1560 --
1561 cursor cashmpipcur
1562 (
1563 pactid number,
1564 chunk number,
1565 itpflg varchar2
1566 ) is
1567 select /*+ ORDERED
1568 INDEX(pa2 PAY_PAYROLL_ACTIONS_PK)
1569 INDEX(pos PER_PERIODS_OF_SERVICE_N3)
1570 INDEX(as1 PER_ASSIGNMENTS_N4)
1571 INDEX(as2 PER_ASSIGNMENTS_F_PK)
1572 INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
1573 index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
1574 USE_NL(pos pop ppp opm as1 act as2) */
1575 act.assignment_action_id,
1576 act.assignment_id,
1577 act.tax_unit_id,
1578 ppp.pre_payment_id
1579 from pay_payroll_actions pa1,
1580 pay_population_ranges pop,
1581 per_periods_of_service pos,
1582 per_all_assignments_f as1,
1583 pay_assignment_actions act,
1584 pay_payroll_actions pa2,
1585 pay_action_classifications pcl,
1586 per_all_assignments_f as2,
1587 pay_pre_payments ppp,
1588 pay_org_payment_methods_f opm
1589 where pa1.payroll_action_id = pactid
1590 and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
1591 and pa1.effective_date between
1592 pa1.start_date and pa1.effective_date
1593 and act.payroll_action_id = pa2.payroll_action_id
1594 and act.action_status = 'C'
1595 and pcl.classification_name = 'CASHED'
1596 and pa2.action_type = pcl.action_type
1597 and as1.assignment_id = act.assignment_id
1598 and pa1.effective_date between
1599 as1.effective_start_date and as1.effective_end_date
1600 and as2.assignment_id = act.assignment_id
1601 and pa1.effective_date between
1602 as2.effective_start_date and as2.effective_end_date
1603 and as2.payroll_id + 0 = as1.payroll_id + 0
1604 and pos.period_of_service_id = as1.period_of_service_id
1605 and pop.payroll_action_id = pactid
1606 and pop.chunk_number = chunk
1607 and pos.person_id = pop.person_id
1608 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
1609 and ppp.assignment_action_id = act.assignment_action_id
1610 and opm.org_payment_method_id = ppp.org_payment_method_id
1611 and pa1.effective_date between
1612 opm.effective_start_date and opm.effective_end_date
1613 and opm.payment_type_id = pa1.payment_type_id
1614 and (opm.org_payment_method_id = pa1.org_payment_method_id
1615 or pa1.org_payment_method_id is null)
1616 and not exists (
1617 select null
1618 from pay_assignment_actions ac2,
1619 pay_action_interlocks int
1620 where int.locked_action_id = act.assignment_action_id
1621 and ac2.assignment_action_id = int.locking_action_id
1622 and ac2.pre_payment_id = ppp.pre_payment_id)
1623 and not exists (
1624 select null
1625 from per_all_assignments_f as3,
1626 pay_assignment_actions ac3
1627 where itpflg = 'N'
1628 and ac3.payroll_action_id = pa2.payroll_action_id
1629 and ac3.action_status not in ('C', 'S')
1630 and as3.assignment_id = ac3.assignment_id
1631 and pa2.effective_date between
1632 as3.effective_start_date and as3.effective_end_date
1633 and as3.person_id = as2.person_id)
1634 order by act.assignment_id
1635 for update of as1.assignment_id, pos.period_of_service_id;
1636 --
1637 lockingactid number;
1638 lockedactid number;
1639 assignid number;
1640 prepayid number;
1641 greid number;
1642 --
1643 begin
1644 if (g_many_procs_in_period = 'Y') then
1645 open cashmpipcur(pactid,chunk,itpflg);
1646 elsif (use_pop_person = 1) then
1647 open cashpopcur(pactid,chunk,itpflg);
1648 else
1649 open cashcur(pactid,stperson,endperson,itpflg);
1650 end if;
1651 loop
1652 if (g_many_procs_in_period = 'Y') then
1653 fetch cashmpipcur into lockedactid,assignid,greid,prepayid;
1654 exit when cashmpipcur%notfound;
1655 elsif (use_pop_person = 1) then
1656 fetch cashpopcur into lockedactid,assignid,greid,prepayid;
1657 exit when cashpopcur%notfound;
1658 else
1659 fetch cashcur into lockedactid,assignid,greid,prepayid;
1660 exit when cashcur%notfound;
1661 end if;
1662 --
1663 -- want to insert an assignment action for each of the
1664 -- rows that we return from the cursor, i.e. one for
1665 -- each assignment/pre-payment.
1666 select pay_assignment_actions_s.nextval
1667 into lockingactid
1668 from dual;
1669 --
1670 -- insert the action record.
1671 -- Note, insert as complete, because we need no further processing.
1672 insact(lockingactid,assignid,pactid,rand_chunk,greid,prepayid,'C');
1673 --
1674 -- insert an interlock to this action.
1675 insint(lockingactid,lockedactid);
1676 --
1677 end loop;
1678 if (g_many_procs_in_period = 'Y') then
1679 close cashmpipcur;
1680 elsif (use_pop_person = 1) then
1681 close cashpopcur;
1682 else
1683 close cashcur;
1684 end if;
1685 commit;
1686 end proccash;
1687 --
1688 procedure procpru
1689 (
1690 pactid in number,
1691 stperson in number,
1692 endperson in number,
1693 chunk in number,
1694 rand_chunk in number,
1695 class in varchar2,
1696 itpflg in varchar2,
1697 use_pop_person in number
1698 )
1699 is
1700 cursor prupaycur
1701 (
1702 pactid number,
1703 stperson number,
1704 endperson number,
1705 class varchar2,
1706 itpflg varchar2
1707 ) is
1708 select /*+ ORDERED
1709 index(pa2 PAY_PAYROLL_ACTIONS_N5)
1710 index(as1 PER_ASSIGNMENTS_F_N4)
1711 USE_NL(pos as1) */
1712 act.assignment_action_id,
1713 act.assignment_id,
1714 act.tax_unit_id
1715 from pay_payroll_actions pa1,
1716 pay_payroll_actions pa2,
1717 pay_action_classifications pcl,
1718 per_periods_of_service pos,
1719 per_all_assignments_f as1,
1720 pay_assignment_actions act
1721 where pa1.payroll_action_id = pactid
1722 and pa2.effective_date between
1723 pa1.start_date and pa1.effective_date
1724 and pa2.consolidation_set_id = pa1.consolidation_set_id
1725 and act.payroll_action_id = pa2.payroll_action_id
1726 and act.action_status in ('C','S')
1727 and pcl.classification_name = class
1728 and pa2.action_type = pcl.action_type
1729 and as1.assignment_id = act.assignment_id
1730 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
1731 and pa2.effective_date between
1732 as1.effective_start_date and as1.effective_end_date
1733 and pos.period_of_service_id = as1.period_of_service_id
1734 and pos.person_id between stperson and endperson
1735 and exists (
1736 select ''
1737 from pay_pre_payments ppp
1738 where ppp.assignment_action_id = act.assignment_action_id
1739 and ppp.organization_id is not null
1740 and nvl(ppp.effective_date, pa2.effective_date)
1741 <= pa1.effective_date
1742 and not exists (
1743 select null
1744 from pay_contributing_payments
1745 where contributing_pre_payment_id =
1746 ppp.pre_payment_id
1747 )
1748 )
1749 and not exists (
1750 select /*+ ORDERED*/
1751 null
1752 from per_all_assignments_f as3,
1753 pay_assignment_actions ac3
1754 where itpflg = 'N'
1755 and ac3.payroll_action_id = pa2.payroll_action_id
1756 and ac3.action_status not in ( 'C', 'S')
1757 and as3.assignment_id = ac3.assignment_id
1758 and pa2.effective_date between
1759 as3.effective_start_date and as3.effective_end_date
1760 and as3.person_id = as1.person_id)
1761 order by as1.person_id,as1.primary_flag desc ,as1.effective_start_date,act.assignment_id
1762 for update of as1.assignment_id, pos.period_of_service_id;
1763 --
1764 cursor prupaypopcur
1765 (
1766 pactid number,
1767 chunk number,
1768 class varchar2,
1769 itpflg varchar2
1770 ) is
1771 select /*+ ORDERED
1772 index(pa2 PAY_PAYROLL_ACTIONS_N5)
1773 index(as1 PER_ASSIGNMENTS_F_N4)
1774 USE_NL(pos as1) */
1775 act.assignment_action_id,
1776 act.assignment_id,
1777 act.tax_unit_id
1778 from pay_payroll_actions pa1,
1779 pay_payroll_actions pa2,
1780 pay_action_classifications pcl,
1781 pay_population_ranges pop,
1782 per_periods_of_service pos,
1783 per_all_assignments_f as1,
1784 pay_assignment_actions act
1785 where pa1.payroll_action_id = pactid
1786 and pa2.effective_date between
1787 pa1.start_date and pa1.effective_date
1788 and pa2.consolidation_set_id = pa1.consolidation_set_id
1789 and act.payroll_action_id = pa2.payroll_action_id
1790 and act.action_status in ('C','S')
1791 and pcl.classification_name = class
1792 and pa2.action_type = pcl.action_type
1793 and as1.assignment_id = act.assignment_id
1794 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
1795 and pa2.effective_date between
1796 as1.effective_start_date and as1.effective_end_date
1797 and pos.period_of_service_id = as1.period_of_service_id
1798 and pop.chunk_number = chunk
1799 and pop.payroll_action_id = pactid
1800 and pos.person_id = pop.person_id
1801 and exists (
1802 select ''
1803 from pay_pre_payments ppp
1804 where ppp.assignment_action_id = act.assignment_action_id
1805 and ppp.organization_id is not null
1806 and nvl(ppp.effective_date, pa2.effective_date)
1807 <= pa1.effective_date
1808 and not exists (
1809 select null
1810 from pay_contributing_payments
1811 where contributing_pre_payment_id =
1812 ppp.pre_payment_id
1813 )
1814 )
1815 and not exists (
1816 select /*+ ORDERED*/
1817 null
1818 from per_all_assignments_f as3,
1819 pay_assignment_actions ac3
1820 where itpflg = 'N'
1821 and ac3.payroll_action_id = pa2.payroll_action_id
1822 and ac3.action_status not in ( 'C', 'S')
1823 and as3.assignment_id = ac3.assignment_id
1824 and pa2.effective_date between
1825 as3.effective_start_date and as3.effective_end_date
1826 and as3.person_id = as1.person_id)
1827 order by as1.person_id,as1.primary_flag desc ,as1.effective_start_date,act.assignment_id
1828 for update of as1.assignment_id, pos.period_of_service_id;
1829 --
1830 --
1831 lockingactid number;
1832 lockedactid number;
1833 assignid number;
1834 prev_assignid number;
1835 greid number;
1836 --
1837 begin
1838 --
1839 pay_proc_logging.PY_ENTRY('hr_nonrun_asact.procpru');
1840 --
1841 prev_assignid := null;
1842 --
1843 pay_proc_logging.PY_LOG('stperson '||stperson);
1844 pay_proc_logging.PY_LOG('endperson '||endperson);
1845 pay_proc_logging.PY_LOG('chunk '||chunk);
1846 --
1847 if (use_pop_person = 1) then
1848 open prupaypopcur(pactid,chunk,class,itpflg);
1849 else
1850 open prupaycur(pactid,stperson,endperson,class,itpflg);
1851 end if;
1852 loop
1853 if (use_pop_person = 1) then
1854 fetch prupaypopcur into lockedactid,
1855 assignid,
1856 greid;
1857 exit when prupaypopcur%notfound;
1858 else
1859 fetch prupaycur into lockedactid,
1860 assignid,
1861 greid;
1862 exit when prupaycur%notfound;
1863 end if;
1864
1865 /* process the insert of assignment actions */
1866 /* logic prevents more than one action per assignment */
1867 if(prev_assignid is null OR prev_assignid <> assignid) then
1868 -- get a value for the action id that is locking.
1869 select pay_assignment_actions_s.nextval
1870 into lockingactid
1871 from dual;
1872 --
1873 -- insert into pay_assignment_actions.
1874 insact(lockingactid,assignid,pactid,rand_chunk,greid);
1875 end if;
1876 --
1877 -- insert into interlocks table.
1878 insint(lockingactid,lockedactid);
1879 prev_assignid := assignid;
1880
1881 end loop;
1882 --
1883 if (use_pop_person = 1) then
1884 close prupaypopcur;
1885 else
1886 close prupaycur;
1887 end if;
1888 commit;
1889 --
1890 pay_proc_logging.PY_EXIT('hr_nonrun_asact.procpru');
1891 --
1892 end procpru;
1893 --
1894 procedure procorgpyt
1895 (
1896 pactid in number, -- payroll_action_id.
1897 chunk in number, -- current chunk_number.
1898 rand_chunk in number, -- current chunk_number.
1899 ptype in number, -- payment_type_id.
1900 class in varchar2 -- payment classification.
1901 )
1902 is
1903 cursor paymentorg
1904 (
1905 pactid number,
1906 chunk number,
1907 ptype number,
1908 class varchar2
1909 ) is
1910 SELECT /*+ ORDERED
1911 */
1912 pcp.assignment_action_id,
1913 hou.organization_id,
1914 ppp.pre_payment_id
1915 from pay_payroll_actions pa1,
1916 pay_payroll_actions pa2,
1917 pay_action_classifications pcl,
1918 pay_population_ranges pop,
1919 hr_organization_units hou,
1920 pay_pre_payments ppp,
1921 pay_org_payment_methods_f opm,
1922 pay_contributing_payments pcp
1923 where pa1.payroll_action_id = pactid
1924 and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
1925 and pa2.effective_date between
1926 pa1.start_date and pa1.effective_date
1927 and pa2.action_status = 'C'
1928 and pcl.classification_name = class
1929 and pa2.action_type = pcl.action_type
1930 --
1931 and pop.payroll_action_id = pactid
1932 and pop.chunk_number = chunk
1933 and hou.organization_id = pop.source_id
1934 --
1935 and (pa2.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
1936 and ppp.payroll_action_id = pa2.payroll_action_id
1937 and ppp.organization_id = hou.organization_id
1938 and opm.org_payment_method_id = ppp.org_payment_method_id
1939 and pa1.effective_date between
1940 opm.effective_start_date and opm.effective_end_date
1941 and opm.payment_type_id +0 = ptype
1942 and (opm.org_payment_method_id = pa1.org_payment_method_id
1943 or pa1.org_payment_method_id is null)
1944 and pcp.pre_payment_id = ppp.pre_payment_id
1945 --
1946 and not exists (
1947 select /*+ ORDERED*/
1948 null
1949 from
1950 pay_assignment_actions ac2
1951 where ac2.pre_payment_id = ppp.pre_payment_id
1952 )
1953 order by hou.organization_id, ppp.pre_payment_id
1954 for update of hou.organization_id;
1955 --
1956 l_prepayid pay_pre_payments.pre_payment_id%type;
1957 prev_prepayid pay_pre_payments.pre_payment_id%type;
1958 lockedactid pay_assignment_actions.assignment_action_id%type;
1959 lockingactid pay_assignment_actions.assignment_action_id%type;
1960 orgid hr_organization_units.organization_id%type;
1961 l_cp number;
1962 --
1963 begin
1964 --
1965 pay_proc_logging.PY_ENTRY('hr_nonrun_asact.procorgpyt');
1966 --
1967 -- Check if need to run this cursor - by looking for rows in
1968 -- pay_contributing_payments
1969 --
1970 if (g_contrib_payments_exist is null) then
1971 begin
1972 select 1
1973 into l_cp
1974 from pay_payroll_actions pa1
1975 where pa1.payroll_action_id = pactid
1976 and exists
1977 (select 1
1978 from pay_payroll_actions pa2,
1979 pay_contributing_payments pcp
1980 where pa2.payroll_action_id = pcp.payroll_action_id
1981 and pa2.action_type = 'PRU'
1982 and pa2.business_group_id = pa1.business_group_id);
1983
1984 g_contrib_payments_exist := TRUE;
1985 exception
1986 when others then
1987 g_contrib_payments_exist := FALSE;
1988 end;
1989 end if;
1990 --
1991 if (g_contrib_payments_exist = TRUE) then
1992 --
1993 pay_proc_logging.PY_LOG('chunk '||chunk);
1994 --
1995 prev_prepayid := null;
1996 open paymentorg(pactid,chunk,ptype, class);
1997 loop
1998 fetch paymentorg into lockedactid,
1999 orgid,
2000 l_prepayid;
2001 exit when paymentorg%notfound;
2002
2003 /* process the insert of assignment actions */
2004 /* logic prevents more than one action per assignment */
2005 if(prev_prepayid is null OR prev_prepayid <> l_prepayid) then
2006 -- get a value for the action id that is locking.
2007 select pay_assignment_actions_s.nextval
2008 into lockingactid
2009 from dual;
2010 --
2011 -- insert into pay_assignment_actions.
2012 insact(lockingactid => lockingactid,
2013 pactid => pactid,
2014 chunk => rand_chunk,
2015 prepayid => l_prepayid,
2016 object_id => orgid,
2017 object_type => 'HOU');
2018 end if;
2019 --
2020 -- insert into interlocks table.
2021 insint(lockingactid,lockedactid);
2022 prev_prepayid := l_prepayid;
2023 --
2024 end loop;
2025 --
2026 close paymentorg;
2027 --
2028 end if;
2029 --
2030 pay_proc_logging.PY_EXIT('hr_nonrun_asact.procorgpyt');
2031 --
2032 end procorgpyt;
2033 --
2034 procedure procchq
2035 (
2036 pactid in number, -- payroll_action_id.
2037 stperson in number, -- starting person_id of range.
2038 endperson in number, -- ending person_id of range.
2039 chunk in number, -- current chunk_number.
2040 rand_chunk in number, -- current chunk_number.
2041 itpflg in varchar2, -- legislation type.
2042 ptype in number, -- payment_type_id.
2043 class in varchar2, -- payment classification.
2044 use_pop_person in number -- use population_ranges person_id column
2045 ) is
2046 --
2047 cursor paymentpopcur
2048 (
2049 pactid number,
2050 chunk number,
2051 itpflg varchar2,
2052 ptype number,
2053 class varchar2
2054 ) is
2055 SELECT /*+ ORDERED
2056 index(pa2 PAY_PAYROLL_ACTIONS_N5)
2057 index(as1 PER_ASSIGNMENTS_F_N4)
2058 index(as2 PER_ASSIGNMENTS_F_PK)
2059 index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
2060 USE_NL(pop pos as1 as2) */
2061 act.assignment_action_id,
2062 act.assignment_id,
2063 act.tax_unit_id,
2064 ppp.pre_payment_id,
2065 pa1.assignment_set_id,
2066 as1.payroll_id
2067 from pay_payroll_actions pa1,
2068 pay_payroll_actions pa2,
2069 pay_action_classifications pcl,
2070 pay_population_ranges pop,
2071 per_periods_of_service pos,
2072 per_all_assignments_f as1,
2073 pay_assignment_actions act,
2074 pay_pre_payments ppp,
2075 per_all_assignments_f as2,
2076 pay_org_payment_methods_f opm
2077 where pa1.payroll_action_id = pactid
2078 and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
2079 and pa2.effective_date between
2080 pa1.start_date and pa1.effective_date
2081 and act.payroll_action_id = pa2.payroll_action_id
2082 and act.action_status = 'C'
2083 and pcl.classification_name = class
2084 and pa2.action_type = pcl.action_type
2085 and as1.assignment_id = act.assignment_id
2086 and pa2.effective_date between
2087 as1.effective_start_date and as1.effective_end_date
2088 and as2.assignment_id = act.assignment_id
2089 and pa1.effective_date between
2090 as2.effective_start_date and as2.effective_end_date
2091 and as2.payroll_id + 0 = as1.payroll_id + 0
2092 and pos.period_of_service_id = as1.period_of_service_id
2093 and pop.payroll_action_id = pactid
2094 and pop.chunk_number = chunk
2095 and pos.person_id = pop.person_id
2096 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
2097 and ppp.assignment_action_id = act.assignment_action_id
2098 and opm.org_payment_method_id = ppp.org_payment_method_id
2099 and ppp.organization_id is null
2100 and pa1.effective_date between
2101 opm.effective_start_date and opm.effective_end_date
2102 and opm.payment_type_id +0 = ptype
2103 and (opm.org_payment_method_id = pa1.org_payment_method_id
2104 or pa1.org_payment_method_id is null)
2105 and not exists (
2106 select /*+ ORDERED*/
2107 null
2108 from pay_action_interlocks int,
2109 pay_assignment_actions ac2
2110 where int.locked_action_id = act.assignment_action_id
2111 and ac2.assignment_action_id = int.locking_action_id
2112 and ac2.pre_payment_id = ppp.pre_payment_id
2113 and not exists (
2114 select null
2115 from pay_assignment_actions paa_void,
2116 pay_action_interlocks pai_void,
2117 pay_payroll_actions ppa_void
2118 where pai_void.locked_action_id = ac2.assignment_action_id
2119 and pai_void.locking_action_id = paa_void.assignment_action_id
2120 and paa_void.payroll_action_id = ppa_void.payroll_action_id
2121 and ppa_void.action_type = 'D')
2122 )
2123 and not exists (
2124 select /*+ ORDERED*/
2125 null
2126 from per_all_assignments_f as3,
2127 pay_assignment_actions ac3
2128 where itpflg = 'N'
2129 and ac3.payroll_action_id = pa2.payroll_action_id
2130 and ac3.action_status not in ('C', 'S')
2131 and as3.assignment_id = ac3.assignment_id
2132 and pa2.effective_date between
2133 as3.effective_start_date and as3.effective_end_date
2134 and as3.person_id = as2.person_id)
2135 order by act.assignment_id
2136 for update of as1.assignment_id, pos.period_of_service_id;
2137 --
2138 cursor paymentcur
2139 (
2140 pactid number,
2141 stperson number,
2142 endperson number,
2143 itpflg varchar2,
2144 ptype number,
2145 class varchar2
2146 ) is
2147 SELECT /*+ ORDERED
2148 index(pa2 PAY_PAYROLL_ACTIONS_N5)
2149 index(as1 PER_ASSIGNMENTS_F_N4)
2150 index(as2 PER_ASSIGNMENTS_F_PK)
2151 index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
2152 USE_NL(pos as1 as2) */
2153 act.assignment_action_id,
2154 act.assignment_id,
2155 act.tax_unit_id,
2156 ppp.pre_payment_id,
2157 pa1.assignment_set_id,
2158 as1.payroll_id
2159 from pay_payroll_actions pa1,
2160 pay_payroll_actions pa2,
2161 pay_action_classifications pcl,
2162 per_periods_of_service pos,
2163 per_all_assignments_f as1,
2164 pay_assignment_actions act,
2165 pay_pre_payments ppp,
2166 per_all_assignments_f as2,
2167 pay_org_payment_methods_f opm
2168 where pa1.payroll_action_id = pactid
2169 and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
2170 and pa2.effective_date between
2171 pa1.start_date and pa1.effective_date
2172 and act.payroll_action_id = pa2.payroll_action_id
2173 and act.action_status = 'C'
2174 and pcl.classification_name = class
2175 and pa2.action_type = pcl.action_type
2176 and as1.assignment_id = act.assignment_id
2177 and pa2.effective_date between
2178 as1.effective_start_date and as1.effective_end_date
2179 and as2.assignment_id = act.assignment_id
2180 and pa1.effective_date between
2181 as2.effective_start_date and as2.effective_end_date
2182 and as2.payroll_id + 0 = as1.payroll_id + 0
2183 and pos.period_of_service_id = as1.period_of_service_id
2184 and pos.person_id between stperson and endperson
2185 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
2186 and ppp.assignment_action_id = act.assignment_action_id
2187 and opm.org_payment_method_id = ppp.org_payment_method_id
2188 and ppp.organization_id is null
2189 and pa1.effective_date between
2190 opm.effective_start_date and opm.effective_end_date
2191 and opm.payment_type_id +0 = ptype
2192 and (opm.org_payment_method_id = pa1.org_payment_method_id
2193 or pa1.org_payment_method_id is null)
2194 and not exists (
2195 select /*+ ORDERED*/
2196 null
2197 from pay_action_interlocks int,
2198 pay_assignment_actions ac2
2199 where int.locked_action_id = act.assignment_action_id
2200 and ac2.assignment_action_id = int.locking_action_id
2201 and ac2.pre_payment_id = ppp.pre_payment_id
2202 and not exists (
2203 select null
2204 from pay_assignment_actions paa_void,
2205 pay_action_interlocks pai_void,
2206 pay_payroll_actions ppa_void
2207 where pai_void.locked_action_id = ac2.assignment_action_id
2208 and pai_void.locking_action_id = paa_void.assignment_action_id
2209 and paa_void.payroll_action_id = ppa_void.payroll_action_id
2210 and ppa_void.action_type = 'D')
2211 )
2212 and not exists (
2213 select /*+ ORDERED*/
2214 null
2215 from per_all_assignments_f as3,
2216 pay_assignment_actions ac3
2217 where itpflg = 'N'
2218 and ac3.payroll_action_id = pa2.payroll_action_id
2219 and ac3.action_status not in ('C', 'S')
2220 and as3.assignment_id = ac3.assignment_id
2221 and pa2.effective_date between
2222 as3.effective_start_date and as3.effective_end_date
2223 and as3.person_id = as2.person_id)
2224 order by act.assignment_id
2225 for update of as1.assignment_id, pos.period_of_service_id;
2226 --
2227 cursor paymentmpipcur
2228 (
2229 pactid number,
2230 chunk number,
2231 itpflg varchar2,
2232 ptype number,
2233 class varchar2
2234 ) is
2235 SELECT
2236 /*+ leading(PA1 PA2 ACT) use_nl(PA1 PA2 ACT) index(ACT PAY_ASSIGNMENT_ACTIONS_N50)*/ -- Bug 6522667
2237 act.assignment_action_id,
2238 act.assignment_id,
2239 act.tax_unit_id,
2240 ppp.pre_payment_id,
2241 pa1.assignment_set_id,
2242 as1.payroll_id
2243 from pay_payroll_actions pa1,
2244 pay_population_ranges pop,
2245 per_periods_of_service pos,
2246 per_all_assignments_f as1,
2247 pay_assignment_actions act,
2248 pay_payroll_actions pa2,
2249 pay_action_classifications pcl,
2250 pay_pre_payments ppp,
2251 per_all_assignments_f as2,
2252 pay_org_payment_methods_f opm
2253 where pa1.payroll_action_id = pactid
2254 and pa2.consolidation_set_id = pa1.consolidation_set_id /* moved +0, bug 6522667 */
2255 and pa2.effective_date between
2256 pa1.start_date and pa1.effective_date
2257 and act.payroll_action_id = pa2.payroll_action_id
2258 and act.action_status = 'C'
2259 and pcl.classification_name = class
2260 and pa2.action_type = pcl.action_type
2261 and as1.assignment_id = act.assignment_id
2262 and pa1.effective_date between
2263 as1.effective_start_date and as1.effective_end_date
2264 and as2.assignment_id = act.assignment_id
2265 and pa2.effective_date between
2266 as2.effective_start_date and as2.effective_end_date
2267 and as2.payroll_id + 0 = as1.payroll_id + 0
2268 and pos.period_of_service_id = as1.period_of_service_id
2269 and pop.payroll_action_id = pactid
2270 and pop.chunk_number = chunk
2271 and pos.person_id = pop.person_id
2272 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
2273 and ppp.assignment_action_id = act.assignment_action_id
2274 and opm.org_payment_method_id = ppp.org_payment_method_id
2275 and pa1.effective_date between
2276 opm.effective_start_date and opm.effective_end_date
2277 and opm.payment_type_id +0 = ptype
2278 and ppp.organization_id is null
2279 and (opm.org_payment_method_id = pa1.org_payment_method_id
2280 or pa1.org_payment_method_id is null)
2281 and not exists (
2282 select /* Bug 6522667, moved ORDERED hint */
2283 null
2284 from pay_action_interlocks int,
2285 pay_assignment_actions ac2
2286 where int.locked_action_id = act.assignment_action_id
2287 and ac2.assignment_action_id = int.locking_action_id
2288 and ac2.pre_payment_id = ppp.pre_payment_id
2289 and not exists (
2290 select null
2291 from pay_assignment_actions paa_void,
2292 pay_action_interlocks pai_void,
2293 pay_payroll_actions ppa_void
2294 where pai_void.locked_action_id = ac2.assignment_action_id
2295 and pai_void.locking_action_id = paa_void.assignment_action_id
2296 and paa_void.payroll_action_id = ppa_void.payroll_action_id
2297 and ppa_void.action_type = 'D')
2298 )
2299 and not exists (
2300 select /* Bug 6522667, moved ORDERED hint */
2301 null
2302 from per_all_assignments_f as3,
2303 pay_assignment_actions ac3
2304 where itpflg = 'N'
2305 and ac3.payroll_action_id = pa2.payroll_action_id
2306 and ac3.action_status not in ('C', 'S')
2307 and as3.assignment_id = ac3.assignment_id
2308 and pa2.effective_date between
2309 as3.effective_start_date and as3.effective_end_date
2310 and as3.person_id = as2.person_id)
2311 order by act.assignment_id
2312 for update of as1.assignment_id, pos.period_of_service_id;
2313 --
2314 cursor chkasg
2315 (
2316 pasgsetid number,
2317 ppayrollid number,
2318 pasgid number,
2319 plockedid number
2320 ) is
2321 SELECT 1
2322 FROM hr_assignment_sets aset
2323 WHERE aset.assignment_set_id = pasgsetid
2324 and nvl(aset.payroll_id,ppayrollid) = ppayrollid
2325 and (not exists
2326 (select 1
2327 from hr_assignment_set_amendments hasa
2328 where hasa.assignment_set_id = aset.assignment_set_id
2329 and hasa.include_or_exclude = 'I')
2330 or exists
2331 (select 1
2332 from hr_assignment_set_amendments hasa
2333 where hasa.assignment_set_id = aset.assignment_set_id
2334 and hasa.assignment_id = pasgid
2335 and hasa.include_or_exclude = 'I'))
2336 and not exists
2337 (select 1
2338 from hr_assignment_set_amendments hasa
2339 where hasa.assignment_set_id = aset.assignment_set_id
2340 and hasa.assignment_id = pasgid
2341 and hasa.include_or_exclude = 'E')
2342 -- Ensure there exists a voided check for this payment.
2343 and exists
2344 (select 1
2345 from pay_action_interlocks lck1,
2346 pay_assignment_actions chk_paa,
2347 pay_payroll_actions chk_ppa,
2348 pay_action_interlocks lck2,
2349 pay_assignment_actions vd_paa,
2350 pay_payroll_actions vd_ppa
2351 where lck1.locked_action_id = plockedid
2352 and lck1.locking_action_id = chk_paa.assignment_action_id
2353 and chk_paa.payroll_action_id = chk_ppa.payroll_action_id
2354 and chk_ppa.action_type = 'H'
2355 and lck2.locked_action_id = chk_paa.assignment_action_id
2356 and lck2.locking_action_id = vd_paa.assignment_action_id
2357 and vd_paa.payroll_action_id = vd_ppa.payroll_action_id
2358 and vd_ppa.action_type = 'D');
2359 --
2360 lockingactid number;
2361 lockedactid number;
2362 assignid number;
2363 prepayid number;
2364 greid number;
2365 --
2366 asgsetid number;
2367 payrollid number;
2368 inasgset boolean;
2369 dummy number;
2370 --
2371 -- algorithm is quite similar to the other process cases,
2372 -- but we have to take into account assignments and
2373 -- personal payment methods.
2374 begin
2375 if (g_many_procs_in_period = 'Y') then
2376 open paymentmpipcur(pactid,chunk,itpflg,ptype,class);
2377 elsif (use_pop_person = 1) then
2378 open paymentpopcur(pactid,chunk,itpflg,ptype,class);
2379 else
2380 open paymentcur(pactid,stperson,endperson,itpflg,ptype,class);
2381 end if;
2382 loop
2383 if (g_many_procs_in_period = 'Y') then
2384 fetch paymentmpipcur into lockedactid,assignid,greid,prepayid,asgsetid,payrollid;
2385 exit when paymentmpipcur%notfound;
2386 elsif (use_pop_person = 1) then
2387 fetch paymentpopcur into lockedactid,assignid,greid,prepayid,asgsetid,payrollid;
2388 exit when paymentpopcur%notfound;
2389 else
2390 fetch paymentcur into lockedactid,assignid,greid,prepayid,asgsetid,payrollid;
2391 exit when paymentcur%notfound;
2392 end if;
2393 --
2394 inasgset := TRUE;
2395 --
2396 if asgsetid is not null then
2397 open chkasg(asgsetid,payrollid,assignid,lockedactid);
2398 fetch chkasg into dummy;
2399 --
2400 if chkasg%notfound then
2401 inasgset := FALSE;
2402 end if;
2403 --
2404 close chkasg;
2405 end if;
2406 --
2407 -- Only create the assignment action if the assignment is part
2408 -- of the assignment set.
2409 if inasgset then
2410 -- we need to insert one action for each of the
2411 -- rows that we return from the cursor (i.e. one
2412 -- for each assignment/pre-payment).
2413 select pay_assignment_actions_s.nextval
2414 into lockingactid
2415 from dual;
2416 --
2417 -- insert the action record.
2418 insact(lockingactid,assignid,pactid,rand_chunk,greid,prepayid);
2419 --
2420 -- insert an interlock to this action.
2421 insint(lockingactid,lockedactid);
2422 end if;
2423 --
2424 end loop;
2425 if (g_many_procs_in_period = 'Y') then
2426 close paymentmpipcur;
2427 elsif (use_pop_person = 1) then
2428 close paymentpopcur;
2429 else
2430 close paymentcur;
2431 end if;
2432 --
2433 -- Now populate the org payments
2434 procorgpyt
2435 (
2436 pactid => pactid,
2437 chunk => chunk,
2438 rand_chunk => rand_chunk,
2439 ptype => ptype,
2440 class => class
2441 );
2442 --
2443 commit;
2444 end procchq;
2445 --
2446 ---------------------------------- procmag ---------------------------------
2447 /*
2448 NAME
2449 procmag - process a single chunk for magnetic transfer process.
2450 DESCRIPTION
2451 This function takes a range as defined by the starting and
2452 ending person_id and inserts a chunk of assignment actions
2453 plus their associated interlock rows. This function for the
2454 magnetic transfer action only.
2455 NOTES
2456 <none>
2457 */
2458 procedure procmag
2459 (
2460 pactid in number, -- payroll_action_id.
2461 stperson in number, -- starting person_id of range.
2462 endperson in number, -- ending person_id of range.
2463 chunk in number, -- current chunk_number.
2464 rand_chunk in number, -- current chunk_number.
2465 itpflg in varchar2, -- legislation type.
2466 ptype in number, -- payment_type_id.
2467 use_pop_person in number -- use population_ranges person_id column
2468 ) is
2469 cursor magpopcur
2470 (
2471 pactid number,
2472 chunk number,
2473 itpflg varchar2,
2474 ptype number
2475 ) is
2476 select /*+ ORDERED
2477 INDEX(pa2 PAY_PAYROLL_ACTIONS_N5)
2478 INDEX(pos PER_PERIODS_OF_SERVICE_N3)
2479 INDEX(as1 PER_ASSIGNMENTS_N4)
2480 INDEX(as2 PER_ASSIGNMENTS_F_PK)
2481 INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
2482 index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
2483 USE_NL(pop pos ppp opm as1 act as2) */
2484 act.assignment_action_id,
2485 act.assignment_id,
2486 act.tax_unit_id,
2487 ppp.pre_payment_id
2488 from pay_payroll_actions pa1,
2489 pay_payroll_actions pa2,
2490 pay_action_classifications pcl,
2491 pay_population_ranges pop,
2492 per_periods_of_service pos,
2493 per_all_assignments_f as1,
2494 pay_assignment_actions act,
2495 per_all_assignments_f as2,
2496 pay_pre_payments ppp,
2497 pay_org_payment_methods_f opm
2498 where pa1.payroll_action_id = pactid
2499 and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
2500 and pa2.effective_date between
2501 pa1.start_date and pa1.effective_date
2502 and act.payroll_action_id = pa2.payroll_action_id
2503 and act.action_status = 'C'
2504 and pcl.classification_name = 'MAGTAPE'
2505 and pa2.action_type = pcl.action_type
2506 and as1.assignment_id = act.assignment_id
2507 and pa2.effective_date between
2508 as1.effective_start_date and as1.effective_end_date
2509 and as2.assignment_id = act.assignment_id
2510 and pa1.effective_date between
2511 as2.effective_start_date and as2.effective_end_date
2512 and as2.payroll_id + 0 = as1.payroll_id + 0
2513 and pos.period_of_service_id = as1.period_of_service_id
2514 and pop.payroll_action_id = pactid
2515 and pop.chunk_number = chunk
2516 and pos.person_id = pop.person_id
2517 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
2518 and ppp.assignment_action_id = act.assignment_action_id
2519 and opm.org_payment_method_id = ppp.org_payment_method_id
2520 and pa1.effective_date between
2521 opm.effective_start_date and opm.effective_end_date
2522 and opm.payment_type_id +0 = ptype
2523 and (opm.org_payment_method_id = pa1.org_payment_method_id
2524 or pa1.org_payment_method_id is null)
2525 and not exists (
2526 select null
2527 from per_all_assignments_f as3,
2528 pay_assignment_actions ac3
2529 where itpflg = 'N'
2530 and ac3.payroll_action_id = pa2.payroll_action_id
2531 and ac3.action_status not in ('C', 'S')
2532 and as3.assignment_id = ac3.assignment_id
2533 and pa2.effective_date between
2534 as3.effective_start_date and as3.effective_end_date
2535 and as3.person_id = as2.person_id)
2536 and not exists (
2537 select /*+ ORDERED*/
2538 null
2539 from pay_action_interlocks int,
2540 pay_assignment_actions ac2
2541 where int.locked_action_id = act.assignment_action_id
2542 and ac2.assignment_action_id = int.locking_action_id
2543 and ac2.pre_payment_id = ppp.pre_payment_id
2544 and not exists (
2545 select null
2546 from pay_assignment_actions paa_void,
2547 pay_action_interlocks pai_void,
2548 pay_payroll_actions ppa_void
2549 where pai_void.locked_action_id = ac2.assignment_action_id
2550 and pai_void.locking_action_id = paa_void.assignment_action_id
2551 and paa_void.payroll_action_id = ppa_void.payroll_action_id
2552 and ppa_void.action_type = 'D')
2553 )
2554 order by act.assignment_id
2555 for update of as1.assignment_id, pos.period_of_service_id;
2556 --
2557 cursor magcur
2558 (
2559 pactid number,
2560 stperson number,
2561 endperson number,
2562 itpflg varchar2,
2563 ptype number
2564 ) is
2565 select /*+ ORDERED
2566 INDEX(pa2 PAY_PAYROLL_ACTIONS_N5)
2567 INDEX(pos PER_PERIODS_OF_SERVICE_N3)
2568 INDEX(as1 PER_ASSIGNMENTS_N4)
2569 INDEX(as2 PER_ASSIGNMENTS_F_PK)
2570 INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
2571 index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
2572 USE_NL(pos ppp opm as1 act as2) */
2573 act.assignment_action_id,
2574 act.assignment_id,
2575 act.tax_unit_id,
2576 ppp.pre_payment_id
2577 from pay_payroll_actions pa1,
2578 pay_payroll_actions pa2,
2579 pay_action_classifications pcl,
2580 per_periods_of_service pos,
2581 per_all_assignments_f as1,
2582 pay_assignment_actions act,
2583 per_all_assignments_f as2,
2584 pay_pre_payments ppp,
2585 pay_org_payment_methods_f opm
2586 where pa1.payroll_action_id = pactid
2587 and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
2588 and pa2.effective_date between
2589 pa1.start_date and pa1.effective_date
2590 and act.payroll_action_id = pa2.payroll_action_id
2591 and act.action_status = 'C'
2592 and pcl.classification_name = 'MAGTAPE'
2593 and pa2.action_type = pcl.action_type
2594 and as1.assignment_id = act.assignment_id
2595 and pa2.effective_date between
2596 as1.effective_start_date and as1.effective_end_date
2597 and as2.assignment_id = act.assignment_id
2598 and pa1.effective_date between
2599 as2.effective_start_date and as2.effective_end_date
2600 and as2.payroll_id + 0 = as1.payroll_id + 0
2601 and pos.period_of_service_id = as1.period_of_service_id
2602 and pos.person_id between stperson and endperson
2603 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
2604 and ppp.assignment_action_id = act.assignment_action_id
2605 and opm.org_payment_method_id = ppp.org_payment_method_id
2606 and pa1.effective_date between
2607 opm.effective_start_date and opm.effective_end_date
2608 and opm.payment_type_id +0 = ptype
2609 and (opm.org_payment_method_id = pa1.org_payment_method_id
2610 or pa1.org_payment_method_id is null)
2611 and not exists (
2612 select null
2613 from per_all_assignments_f as3,
2614 pay_assignment_actions ac3
2615 where itpflg = 'N'
2616 and ac3.payroll_action_id = pa2.payroll_action_id
2617 and ac3.action_status not in ('C', 'S')
2618 and as3.assignment_id = ac3.assignment_id
2619 and pa2.effective_date between
2620 as3.effective_start_date and as3.effective_end_date
2621 and as3.person_id = as2.person_id)
2622 and not exists (
2623 select /*+ ORDERED*/
2624 null
2625 from pay_action_interlocks int,
2626 pay_assignment_actions ac2
2627 where int.locked_action_id = act.assignment_action_id
2628 and ac2.assignment_action_id = int.locking_action_id
2629 and ac2.pre_payment_id = ppp.pre_payment_id
2630 and not exists (
2631 select null
2632 from pay_assignment_actions paa_void,
2633 pay_action_interlocks pai_void,
2634 pay_payroll_actions ppa_void
2635 where pai_void.locked_action_id = ac2.assignment_action_id
2636 and pai_void.locking_action_id = paa_void.assignment_action_id
2637 and paa_void.payroll_action_id = ppa_void.payroll_action_id
2638 and ppa_void.action_type = 'D')
2639 )
2640 order by act.assignment_id
2641 for update of as1.assignment_id, pos.period_of_service_id;
2642 --
2643 cursor magmpipcur
2644 (
2645 pactid number,
2646 chunk number,
2647 itpflg varchar2,
2648 ptype number
2649 ) is
2650 select /*+ ORDERED
2651 INDEX(pa2 PAY_PAYROLL_ACTIONS_PK)
2652 INDEX(pos PER_PERIODS_OF_SERVICE_N3)
2653 INDEX(as1 PER_ASSIGNMENTS_N4)
2654 INDEX(as2 PER_ASSIGNMENTS_F_PK)
2655 INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
2656 index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
2657 USE_NL(pos pop ppp opm as1 act as2) */
2658 act.assignment_action_id,
2659 act.assignment_id,
2660 act.tax_unit_id,
2661 ppp.pre_payment_id
2662 from pay_payroll_actions pa1,
2663 pay_population_ranges pop,
2664 per_periods_of_service pos,
2665 per_all_assignments_f as1,
2666 pay_assignment_actions act,
2667 pay_payroll_actions pa2,
2668 pay_action_classifications pcl,
2669 per_all_assignments_f as2,
2670 pay_pre_payments ppp,
2671 pay_org_payment_methods_f opm
2672 where pa1.payroll_action_id = pactid
2673 and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
2674 and pa2.effective_date between
2675 pa1.start_date and pa1.effective_date
2676 and act.payroll_action_id = pa2.payroll_action_id
2677 and act.action_status = 'C'
2678 and pcl.classification_name = 'MAGTAPE'
2679 and pa2.action_type = pcl.action_type
2680 and as1.assignment_id = act.assignment_id
2681 and pa1.effective_date between
2682 as1.effective_start_date and as1.effective_end_date
2683 and as2.assignment_id = act.assignment_id
2684 and pa2.effective_date between
2685 as2.effective_start_date and as2.effective_end_date
2686 and as2.payroll_id + 0 = as1.payroll_id + 0
2687 and pos.period_of_service_id = as1.period_of_service_id
2688 and pop.payroll_action_id = pactid
2689 and pop.chunk_number = chunk
2690 and pos.person_id = pop.person_id
2691 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
2692 and ppp.assignment_action_id = act.assignment_action_id
2693 and opm.org_payment_method_id = ppp.org_payment_method_id
2694 and pa1.effective_date between
2695 opm.effective_start_date and opm.effective_end_date
2696 and opm.payment_type_id +0 = ptype
2697 and (opm.org_payment_method_id = pa1.org_payment_method_id
2698 or pa1.org_payment_method_id is null)
2699 and not exists (
2700 select null
2701 from per_all_assignments_f as3,
2702 pay_assignment_actions ac3
2703 where itpflg = 'N'
2704 and ac3.payroll_action_id = pa2.payroll_action_id
2705 and ac3.action_status not in ('C', 'S')
2706 and as3.assignment_id = ac3.assignment_id
2707 and pa2.effective_date between
2708 as3.effective_start_date and as3.effective_end_date
2709 and as3.person_id = as2.person_id)
2710 and not exists (
2711 select /*+ ORDERED*/
2712 null
2713 from pay_action_interlocks int,
2714 pay_assignment_actions ac2
2715 where int.locked_action_id = act.assignment_action_id
2716 and ac2.assignment_action_id = int.locking_action_id
2717 and ac2.pre_payment_id = ppp.pre_payment_id
2718 and not exists (
2719 select null
2720 from pay_assignment_actions paa_void,
2721 pay_action_interlocks pai_void,
2722 pay_payroll_actions ppa_void
2723 where pai_void.locked_action_id = ac2.assignment_action_id
2724 and pai_void.locking_action_id = paa_void.assignment_action_id
2725 and paa_void.payroll_action_id = ppa_void.payroll_action_id
2726 and ppa_void.action_type = 'D')
2727 )
2728 order by act.assignment_id
2729 for update of as1.assignment_id, pos.period_of_service_id;
2730 --
2731 lockingactid number;
2732 lockedactid number;
2733 assignid number;
2734 prepayid number;
2735 greid number;
2736 --
2737 -- algorithm is quite similar to the other process cases,
2738 -- but we have to take into account assignments and
2739 -- personal payment methods.
2740 begin
2741 if (g_many_procs_in_period = 'Y') then
2742 open magmpipcur(pactid,chunk,itpflg,ptype);
2743 elsif (use_pop_person = 1) then
2744 open magpopcur(pactid,chunk,itpflg,ptype);
2745 else
2746 open magcur(pactid,stperson,endperson,itpflg,ptype);
2747 end if;
2748 loop
2749 if (g_many_procs_in_period = 'Y') then
2750 fetch magmpipcur into lockedactid,assignid,greid,prepayid;
2751 exit when magmpipcur%notfound;
2752 elsif (use_pop_person = 1) then
2753 fetch magpopcur into lockedactid,assignid,greid,prepayid;
2754 exit when magpopcur%notfound;
2755 else
2756 fetch magcur into lockedactid,assignid,greid,prepayid;
2757 exit when magcur%notfound;
2758 end if;
2759 --
2760 -- we need to insert one action for each of the
2761 -- rows that we return from the cursor (i.e. one
2762 -- for each assignment/pre-payment).
2763 select pay_assignment_actions_s.nextval
2764 into lockingactid
2765 from dual;
2766 --
2767 -- insert the action record.
2768 insact(lockingactid,assignid,pactid,rand_chunk,greid,prepayid);
2769 --
2770 -- insert an interlock to this action.
2771 insint(lockingactid,lockedactid);
2772 --
2773 end loop;
2774 if (g_many_procs_in_period = 'Y') then
2775 close magmpipcur;
2776 elsif (use_pop_person = 1) then
2777 close magpopcur;
2778 else
2779 close magcur;
2780 end if;
2781 --
2782 -- Now populate the org payments
2783 procorgpyt
2784 (
2785 pactid => pactid,
2786 chunk => chunk,
2787 rand_chunk => rand_chunk,
2788 ptype => ptype,
2789 class => 'MAGTAPE'
2790 );
2791 commit;
2792 end procmag;
2793 --
2794 -------------------------------- proc_prepay -------------------------------
2795 /*
2796 NAME
2797 proc_prepay - insert actions for pre-payment action type.
2798 DESCRIPTION
2799 For the range defined by the starting and ending person_id,
2800 inserts a chunk of assignment actions and associated interlocks.
2801 NOTES
2802 <none>
2803 */
2804 procedure proc_prepay
2805 (
2806 pactid in number,
2807 stperson in number,
2808 endperson in number,
2809 chunk in number,
2810 rand_chunk in number,
2811 class in varchar2,
2812 itpflg in varchar2,
2813 mult_asg_flag in varchar2 default 'N',
2814 use_pop_person in number
2815 ) is
2816 --
2817 cursor prepaypopcur
2818 (
2819 pactid number,
2820 chunk number,
2821 class varchar2,
2822 itpflg varchar2
2823 ) is
2824 select /*+ ORDERED
2825 index(pa2 PAY_PAYROLL_ACTIONS_N5)
2826 index(as1 PER_ASSIGNMENTS_F_N4)
2827 index(as2 PER_ASSIGNMENTS_F_PK)
2828 USE_NL(pop pos as1) */
2829 act.assignment_action_id,
2830 act.assignment_id,
2831 act.tax_unit_id,
2832 as1.person_id,
2833 as1.effective_start_date,
2834 as1.primary_flag
2835 from pay_payroll_actions pa1,
2836 pay_payroll_actions pa2,
2837 pay_action_classifications pcl,
2838 pay_population_ranges pop,
2839 per_periods_of_service pos,
2840 per_all_assignments_f as1,
2841 pay_assignment_actions act,
2842 per_all_assignments_f as2
2843 where pa1.payroll_action_id = pactid
2844 and pa2.payroll_id = pa1.payroll_id
2845 and pa2.effective_date between
2846 pa1.start_date and pa1.effective_date
2847 and act.payroll_action_id = pa2.payroll_action_id
2848 and act.action_status in ('C','S')
2849 and pcl.classification_name = class
2850 and pa2.consolidation_set_id = pa1.consolidation_set_id
2851 and pa2.action_type = pcl.action_type
2852 and nvl(pa2.future_process_mode, 'Y') = 'Y'
2853 and as1.assignment_id = act.assignment_id
2854 and pa2.effective_date between
2855 as1.effective_start_date and as1.effective_end_date
2856 and as2.assignment_id = act.assignment_id
2857 and pa1.effective_date between
2858 as2.effective_start_date and as2.effective_end_date
2859 and as2.payroll_id = as1.payroll_id
2860 and pos.period_of_service_id = as1.period_of_service_id
2861 and pop.payroll_action_id = pactid
2862 and pop.chunk_number = chunk
2863 and pos.person_id = pop.person_id
2864 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
2865 and not exists (
2866 select null
2867 from pay_assignment_actions ac2,
2868 pay_payroll_actions pa3,
2869 pay_action_interlocks int
2870 where int.locked_action_id = act.assignment_action_id
2871 and ac2.assignment_action_id = int.locking_action_id
2872 and pa3.payroll_action_id = ac2.payroll_action_id
2873 and pa3.action_type in ('P', 'U'))
2874 and not exists (
2875 select /*+ ORDERED*/
2876 null
2877 from per_all_assignments_f as3,
2878 pay_assignment_actions ac3
2879 where itpflg = 'N'
2880 and ac3.payroll_action_id = pa2.payroll_action_id
2881 and ac3.action_status not in ( 'C', 'S')
2882 and as3.assignment_id = ac3.assignment_id
2883 and pa2.effective_date between
2884 as3.effective_start_date and as3.effective_end_date
2885 and as3.person_id = as2.person_id)
2886 order by as1.person_id,as1.primary_flag desc ,as1.effective_start_date,act.assignment_id
2887 for update of as1.assignment_id, pos.period_of_service_id;
2888 --
2889 cursor prepaycur
2890 (
2891 pactid number,
2892 stperson number,
2893 endperson number,
2894 class varchar2,
2895 itpflg varchar2
2896 ) is
2897 select /*+ ORDERED
2898 index(pa2 PAY_PAYROLL_ACTIONS_N5)
2899 index(as1 PER_ASSIGNMENTS_F_N4)
2900 index(as2 PER_ASSIGNMENTS_F_PK)
2901 USE_NL(pos as1) */
2902 act.assignment_action_id,
2903 act.assignment_id,
2904 act.tax_unit_id,
2905 as1.person_id,
2906 as1.effective_start_date,
2907 as1.primary_flag
2908 from pay_payroll_actions pa1,
2909 pay_payroll_actions pa2,
2910 pay_action_classifications pcl,
2911 per_periods_of_service pos,
2912 per_all_assignments_f as1,
2913 pay_assignment_actions act,
2914 per_all_assignments_f as2
2915 where pa1.payroll_action_id = pactid
2916 and pa2.payroll_id = pa1.payroll_id
2917 and pa2.effective_date between
2918 pa1.start_date and pa1.effective_date
2919 and act.payroll_action_id = pa2.payroll_action_id
2920 and act.action_status in ('C','S')
2921 and pcl.classification_name = class
2922 and pa2.consolidation_set_id = pa1.consolidation_set_id
2923 and pa2.action_type = pcl.action_type
2924 and nvl(pa2.future_process_mode, 'Y') = 'Y'
2925 and as1.assignment_id = act.assignment_id
2926 and pa2.effective_date between
2927 as1.effective_start_date and as1.effective_end_date
2928 and as2.assignment_id = act.assignment_id
2929 and pa1.effective_date between
2930 as2.effective_start_date and as2.effective_end_date
2931 and as2.payroll_id = as1.payroll_id
2932 and pos.period_of_service_id = as1.period_of_service_id
2933 and pos.person_id between stperson and endperson
2934 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
2935 and not exists (
2936 select null
2937 from pay_assignment_actions ac2,
2938 pay_payroll_actions pa3,
2939 pay_action_interlocks int
2940 where int.locked_action_id = act.assignment_action_id
2941 and ac2.assignment_action_id = int.locking_action_id
2942 and pa3.payroll_action_id = ac2.payroll_action_id
2943 and pa3.action_type in ('P', 'U'))
2944 and not exists (
2945 select /*+ ORDERED*/
2946 null
2947 from per_all_assignments_f as3,
2948 pay_assignment_actions ac3
2949 where itpflg = 'N'
2950 and ac3.payroll_action_id = pa2.payroll_action_id
2951 and ac3.action_status not in ( 'C', 'S')
2952 and as3.assignment_id = ac3.assignment_id
2953 and pa2.effective_date between
2954 as3.effective_start_date and as3.effective_end_date
2955 and as3.person_id = as2.person_id)
2956 order by as1.person_id,as1.primary_flag desc ,as1.effective_start_date,act.assignment_id
2957 for update of as1.assignment_id, pos.period_of_service_id;
2958 --
2959 cursor prepaympipcur
2960 (
2961 pactid number,
2962 chunk number,
2963 class varchar2,
2964 itpflg varchar2
2965 ) is
2966 select /*+ ORDERED
2967 index(pa2 PAY_PAYROLL_ACTIONS_PK)
2968 index(pos PER_PERIODS_OF_SERVICE_N3)
2969 index(act PAY_ASSIGNMENT_ACTIONS_N51)
2970 index(as1 PER_ASSIGNMENTS_F_N4)
2971 index(as2 PER_ASSIGNMENTS_F_PK)
2972 USE_NL(pos pop act as1 as2 pa2) */
2973 act.assignment_action_id,
2974 act.assignment_id,
2975 act.tax_unit_id,
2976 as1.person_id,
2977 as1.effective_start_date,
2978 as1.primary_flag
2979 from pay_payroll_actions pa1,
2980 pay_population_ranges pop,
2981 per_periods_of_service pos,
2982 per_all_assignments_f as1,
2983 pay_assignment_actions act,
2984 pay_payroll_actions pa2,
2985 pay_action_classifications pcl,
2986 per_all_assignments_f as2
2987 where pa1.payroll_action_id = pactid
2988 and pa2.payroll_id = pa1.payroll_id
2989 and pa2.effective_date between
2990 pa1.start_date and pa1.effective_date
2991 and act.payroll_action_id = pa2.payroll_action_id
2992 and act.action_status in ('C','S')
2993 and pcl.classification_name = class
2994 and pa2.consolidation_set_id = pa1.consolidation_set_id
2995 and pa2.action_type = pcl.action_type
2996 and nvl(pa2.future_process_mode, 'Y') = 'Y'
2997 and as1.assignment_id = act.assignment_id
2998 and pa1.effective_date between
2999 as1.effective_start_date and as1.effective_end_date
3000 and as2.assignment_id = act.assignment_id
3001 and pa2.effective_date between
3002 as2.effective_start_date and as2.effective_end_date
3003 and as2.payroll_id = as1.payroll_id
3004 and pos.period_of_service_id = as1.period_of_service_id
3005 and pop.payroll_action_id = pactid
3006 and pop.chunk_number = chunk
3007 and pos.person_id = pop.person_id
3008 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3009 and not exists (
3010 select null
3011 from pay_assignment_actions ac2,
3012 pay_payroll_actions pa3,
3013 pay_action_interlocks int
3014 where int.locked_action_id = act.assignment_action_id
3015 and ac2.assignment_action_id = int.locking_action_id
3016 and pa3.payroll_action_id = ac2.payroll_action_id
3017 and pa3.action_type in ('P', 'U'))
3018 and not exists (
3019 select /*+ ORDERED*/
3020 null
3021 from per_all_assignments_f as3,
3022 pay_assignment_actions ac3
3023 where itpflg = 'N'
3024 and ac3.payroll_action_id = pa2.payroll_action_id
3025 and ac3.action_status not in ( 'C', 'S')
3026 and as3.assignment_id = ac3.assignment_id
3027 and pa2.effective_date between
3028 as3.effective_start_date and as3.effective_end_date
3029 and as3.person_id = as2.person_id)
3030 order by as1.person_id,as1.primary_flag desc ,as1.effective_start_date,act.assignment_id
3031 for update of as1.assignment_id, pos.period_of_service_id;
3032 --
3033 lockingactid number;
3034 lockedactid number;
3035 assignid number;
3036 prev_assignid number;
3037 greid number;
3038 --
3039 person_id number;
3040 primary_flag varchar2(30);
3041 asg_start_date date;
3042 prev_person_id number;
3043 begin
3044 prev_assignid := null;
3045 prev_person_id := null;
3046 if (g_many_procs_in_period = 'Y') then
3047 open prepaympipcur(pactid,chunk,class,itpflg);
3048 elsif (use_pop_person = 1) then
3049 open prepaypopcur(pactid,chunk,class,itpflg);
3050 else
3051 open prepaycur(pactid,stperson,endperson,class,itpflg);
3052 end if;
3053 loop
3054 if (g_many_procs_in_period = 'Y') then
3055 fetch prepaympipcur into lockedactid,assignid,greid,person_id,asg_start_date,primary_flag;
3056 exit when prepaympipcur%notfound;
3057 elsif (use_pop_person = 1) then
3058 fetch prepaypopcur into lockedactid,assignid,greid,person_id,asg_start_date,primary_flag;
3059 exit when prepaypopcur%notfound;
3060 else
3061 fetch prepaycur into lockedactid,assignid,greid,person_id,asg_start_date,primary_flag;
3062 exit when prepaycur%notfound;
3063 end if;
3064 --
3065 if (mult_asg_flag = 'Y')
3066 then
3067 -- insert master actions
3068 if (prev_person_id is null or prev_person_id <> person_id) then
3069 select pay_assignment_actions_s.nextval
3070 into lockingactid
3071 from dual;
3072
3073 -- insert into pay_assignment_actions.
3074 insact(lockingactid,assignid,pactid,rand_chunk,greid);
3075
3076 end if;
3077 -- insert interlocks
3078 insint(lockingactid,lockedactid);
3079 prev_assignid := assignid;
3080 prev_person_id := person_id;
3081
3082 else
3083 /* process the insert of assignment actions */
3084 /* logic prevents more than one action per assignment */
3085 if(prev_assignid is null OR prev_assignid <> assignid) then
3086 -- get a value for the action id that is locking.
3087 select pay_assignment_actions_s.nextval
3088 into lockingactid
3089 from dual;
3090 --
3091 -- insert into pay_assignment_actions.
3092 insact(lockingactid,assignid,pactid,rand_chunk,greid);
3093 end if;
3094 --
3095 -- insert into interlocks table.
3096 insint(lockingactid,lockedactid);
3097 prev_assignid := assignid;
3098 end if;
3099 --
3100 end loop;
3101 if (g_many_procs_in_period = 'Y') then
3102 close prepaympipcur;
3103 elsif (use_pop_person = 1) then
3104 close prepaypopcur;
3105 else
3106 close prepaycur;
3107 end if;
3108 commit;
3109 end proc_prepay;
3110 --
3111 ------------------------------- proc_costing -------------------------------
3112 /*
3113 NAME
3114 proc_costing - insert actions for non Costing action type.
3115 DESCRIPTION
3116 For the range defined by the starting and ending person_id,
3117 inserts a chunk of assignment actions and associated interlocks.
3118 NOTES
3119 <none>
3120 */
3121 procedure proc_costing
3122 (
3123 pactid in number,
3124 stperson in number,
3125 endperson in number,
3126 chunk in number,
3127 rand_chunk in number,
3128 class in varchar2,
3129 itpflg in varchar2,
3130 use_pop_person in number
3131 ) is
3132 --
3133 cursor costingpopcur
3134 (
3135 pactid number,
3136 chunk number,
3137 class varchar2,
3138 itpflg varchar2
3139 ) is
3140 select /*+ ORDERED
3141 index(pa2 PAY_PAYROLL_ACTIONS_N5)
3142 index(as1 PER_ASSIGNMENTS_F_N4)
3143 index(as2 PER_ASSIGNMENTS_F_PK)
3144 USE_NL(pos pop as1) */
3145 act.assignment_action_id,
3146 act.assignment_id,
3147 act.tax_unit_id
3148 from pay_payroll_actions pa1,
3149 pay_payroll_actions pa2,
3150 pay_action_classifications pcl,
3151 pay_population_ranges pop,
3152 per_periods_of_service pos,
3153 per_all_assignments_f as1,
3154 pay_assignment_actions act,
3155 per_all_assignments_f as2
3156 where pa1.payroll_action_id = pactid
3157 and pa2.consolidation_set_id = pa1.consolidation_set_id
3158 and pa2.effective_date between
3159 pa1.start_date and pa1.effective_date
3160 and act.payroll_action_id = pa2.payroll_action_id
3161 and act.action_status in ('C','S')
3162 and pcl.classification_name = class
3163 and pa2.action_type = pcl.action_type
3164 and as1.assignment_id = act.assignment_id
3165 and pa2.effective_date between
3166 as1.effective_start_date and as1.effective_end_date
3167 and as2.assignment_id = act.assignment_id
3168 and pa1.effective_date between
3169 as2.effective_start_date and as2.effective_end_date
3170 and pop.payroll_action_id = pactid
3171 and pop.chunk_number = chunk
3172 and pos.person_id = pop.person_id
3173 and pos.period_of_service_id = as1.period_of_service_id
3174 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3175 and not exists (
3176 select null
3177 from pay_assignment_actions ac2,
3178 pay_payroll_actions pa3,
3179 pay_action_interlocks int
3180 where int.locked_action_id = act.assignment_action_id
3181 and ac2.assignment_action_id = int.locking_action_id
3182 and pa3.payroll_action_id = ac2.payroll_action_id
3183 and pa3.action_type in ('C', 'S'))
3184 and not exists (
3185 select /*+ ORDERED*/
3186 null
3187 from per_all_assignments_f as3,
3188 pay_assignment_actions ac3
3189 where itpflg = 'N'
3190 and ac3.payroll_action_id = pa2.payroll_action_id
3191 and ac3.action_status not in ('C','S')
3192 and as3.assignment_id = ac3.assignment_id
3193 and pa2.effective_date between
3194 as3.effective_start_date and as3.effective_end_date
3195 and as3.person_id = as2.person_id)
3196 order by act.assignment_id
3197 for update of as1.assignment_id, pos.period_of_service_id;
3198 --
3199 cursor costingcur
3200 (
3201 pactid number,
3202 stperson number,
3203 endperson number,
3204 class varchar2,
3205 itpflg varchar2
3206 ) is
3207 select /*+ ORDERED
3208 index(pa2 PAY_PAYROLL_ACTIONS_N5)
3209 index(as1 PER_ASSIGNMENTS_F_N4)
3210 index(as2 PER_ASSIGNMENTS_F_PK)
3211 USE_NL(pos as1) */
3212 act.assignment_action_id,
3213 act.assignment_id,
3214 act.tax_unit_id
3215 from pay_payroll_actions pa1,
3216 pay_payroll_actions pa2,
3217 pay_action_classifications pcl,
3218 per_periods_of_service pos,
3219 per_all_assignments_f as1,
3220 pay_assignment_actions act,
3221 per_all_assignments_f as2
3222 where pa1.payroll_action_id = pactid
3223 and pa2.consolidation_set_id = pa1.consolidation_set_id
3224 and pa2.effective_date between
3225 pa1.start_date and pa1.effective_date
3226 and act.payroll_action_id = pa2.payroll_action_id
3227 and act.action_status in ('C','S')
3228 and pcl.classification_name = class
3229 and pa2.action_type = pcl.action_type
3230 and as1.assignment_id = act.assignment_id
3231 and pa2.effective_date between
3232 as1.effective_start_date and as1.effective_end_date
3233 and as2.assignment_id = act.assignment_id
3234 and pa1.effective_date between
3235 as2.effective_start_date and as2.effective_end_date
3236 and pos.period_of_service_id = as1.period_of_service_id
3237 and pos.person_id between stperson and endperson
3238 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3239 and not exists (
3240 select null
3241 from pay_assignment_actions ac2,
3242 pay_payroll_actions pa3,
3243 pay_action_interlocks int
3244 where int.locked_action_id = act.assignment_action_id
3245 and ac2.assignment_action_id = int.locking_action_id
3246 and pa3.payroll_action_id = ac2.payroll_action_id
3247 and pa3.action_type in ('C', 'S'))
3248 and not exists (
3249 select /*+ ORDERED*/
3250 null
3251 from per_all_assignments_f as3,
3252 pay_assignment_actions ac3
3253 where itpflg = 'N'
3254 and ac3.payroll_action_id = pa2.payroll_action_id
3255 and ac3.action_status not in ('C','S')
3256 and as3.assignment_id = ac3.assignment_id
3257 and pa2.effective_date between
3258 as3.effective_start_date and as3.effective_end_date
3259 and as3.person_id = as2.person_id)
3260 order by act.assignment_id
3261 for update of as1.assignment_id, pos.period_of_service_id;
3262 --
3263 cursor costingmpipcur
3264 (
3265 pactid number,
3266 chunk number,
3267 class varchar2,
3268 itpflg varchar2
3269 ) is
3270 select /*+ ORDERED
3271 index(pa2 PAY_PAYROLL_ACTIONS_PK)
3272 index(pos PER_PERIODS_OF_SERVICE_N3)
3273 index(as1 PER_ASSIGNMENTS_F_N4)
3274 index(act PAY_ASSIGNMENT_ACTIONS_N51)
3275 index(as2 PER_ASSIGNMENTS_F_PK)
3276 USE_NL(pos pop act pa2 as2 as1) */
3277 act.assignment_action_id,
3278 act.assignment_id,
3279 act.tax_unit_id
3280 from pay_payroll_actions pa1,
3281 pay_population_ranges pop,
3282 per_periods_of_service pos,
3283 per_all_assignments_f as1,
3284 pay_assignment_actions act,
3285 pay_payroll_actions pa2,
3286 pay_action_classifications pcl,
3287 per_all_assignments_f as2
3288 where pa1.payroll_action_id = pactid
3289 and pa2.consolidation_set_id = pa1.consolidation_set_id
3290 and pa2.effective_date between
3291 pa1.start_date and pa1.effective_date
3292 and act.payroll_action_id = pa2.payroll_action_id
3293 and act.action_status in ('C','S')
3294 and pcl.classification_name = class
3295 and pa2.action_type = pcl.action_type
3296 and as1.assignment_id = act.assignment_id
3297 and pa1.effective_date between
3298 as1.effective_start_date and as1.effective_end_date
3299 and as2.assignment_id = act.assignment_id
3300 and pa2.effective_date between
3301 as2.effective_start_date and as2.effective_end_date
3302 and pos.period_of_service_id = as1.period_of_service_id
3303 and pop.payroll_action_id = pactid
3304 and pop.chunk_number = chunk
3305 and pos.person_id = pop.person_id
3306 and (as2.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3307 and not exists (
3308 select null
3309 from pay_assignment_actions ac2,
3310 pay_payroll_actions pa3,
3311 pay_action_interlocks int
3312 where int.locked_action_id = act.assignment_action_id
3313 and ac2.assignment_action_id = int.locking_action_id
3314 and pa3.payroll_action_id = ac2.payroll_action_id
3315 and pa3.action_type in ('C', 'S'))
3316 and not exists (
3317 select /*+ ORDERED*/
3318 null
3319 from per_all_assignments_f as3,
3320 pay_assignment_actions ac3
3321 where itpflg = 'N'
3322 and ac3.payroll_action_id = pa2.payroll_action_id
3323 and ac3.action_status not in ('C','S')
3324 and as3.assignment_id = ac3.assignment_id
3325 and pa2.effective_date between
3326 as3.effective_start_date and as3.effective_end_date
3327 and as3.person_id = as2.person_id)
3328 order by act.assignment_id
3329 for update of as1.assignment_id, pos.period_of_service_id;
3330 --
3331 lockingactid number;
3332 lockedactid number;
3333 assignid number;
3334 prev_assignid number;
3335 greid number;
3336 --
3337 begin
3338 prev_assignid := null;
3339 if (g_many_procs_in_period = 'Y') then
3340 open costingmpipcur(pactid,chunk,class,itpflg);
3341 elsif (use_pop_person = 1) then
3342 open costingpopcur(pactid,chunk,class,itpflg);
3343 else
3344 open costingcur(pactid,stperson,endperson,class,itpflg);
3345 end if;
3346 loop
3347 if (g_many_procs_in_period = 'Y') then
3348 fetch costingmpipcur into lockedactid,assignid,greid;
3349 exit when costingmpipcur%notfound;
3350 elsif (use_pop_person = 1) then
3351 fetch costingpopcur into lockedactid,assignid,greid;
3352 exit when costingpopcur%notfound;
3353 else
3354 fetch costingcur into lockedactid,assignid,greid;
3355 exit when costingcur%notfound;
3356 end if;
3357 --
3358 /* process the insert of assignment actions */
3359 /* logic prevents more than one action per assignment */
3360 if(prev_assignid is null OR prev_assignid <> assignid) then
3361 -- get a value for the action id that is locking.
3362 select pay_assignment_actions_s.nextval
3363 into lockingactid
3364 from dual;
3365 --
3366 -- insert into pay_assignment_actions.
3367 insact(lockingactid,assignid,pactid,rand_chunk,greid);
3368 end if;
3369 --
3370 -- insert into interlocks table.
3371 insint(lockingactid,lockedactid);
3372 --
3373 prev_assignid := assignid;
3374 end loop;
3375 if (g_many_procs_in_period = 'Y') then
3376 close costingmpipcur;
3377 elsif (use_pop_person = 1) then
3378 close costingpopcur;
3379 else
3380 close costingcur;
3381 end if;
3382 commit;
3383 end proc_costing;
3384 --
3385 ------------------------------- proc_paymcosting ---------------------------
3386 /*
3387 NAME
3388 proc_paymcosting - insert actions for Payment Costing action type.
3389 DESCRIPTION
3390 For the range defined by the starting and ending person_id,
3391 inserts a chunk of assignment actions and associated interlocks.
3392 NOTES
3393 <none>
3394 */
3395 procedure proc_paymcosting
3396 (
3397 pactid in number,
3398 stperson in number,
3399 endperson in number,
3400 chunk in number,
3401 rand_chunk in number,
3402 class in varchar2,
3403 itpflg in varchar2,
3404 use_pop_person in number
3405 ) is
3406 --
3407 cursor pmcostingpopcur
3408 (
3409 pactid number,
3410 chunk number,
3411 class varchar2,
3412 itpflg varchar2
3413 ) is
3414 select /*+ ORDERED
3415 index(pa2 PAY_PAYROLL_ACTIONS_N5)
3416 index(as1 PER_ASSIGNMENTS_F_N4)
3417 index(as2 PER_ASSIGNMENTS_F_PK)
3418 USE_NL(pos pop as1) */
3419 act.assignment_action_id,
3420 act.assignment_id,
3421 act.tax_unit_id,
3422 act.payroll_action_id
3423 from pay_payroll_actions pa1,
3424 pay_payroll_actions pa2,
3425 pay_action_classifications pcl,
3426 pay_population_ranges pop,
3427 per_periods_of_service pos,
3428 per_all_assignments_f as1,
3429 pay_assignment_actions act,
3430 per_all_assignments_f as2
3431 where pa1.payroll_action_id = pactid
3432 and pa2.consolidation_set_id = pa1.consolidation_set_id
3433 and pa2.effective_date between
3434 pa1.start_date and pa1.effective_date
3435 and act.payroll_action_id = pa2.payroll_action_id
3436 and act.action_status in ('C','S')
3437 and pcl.classification_name = class
3438 and pa2.action_type = pcl.action_type
3439 and as1.assignment_id = act.assignment_id
3440 and pa2.effective_date between
3441 as1.effective_start_date and as1.effective_end_date
3442 and as2.assignment_id = act.assignment_id
3443 and pa1.effective_date between
3444 as2.effective_start_date and as2.effective_end_date
3445 and pop.payroll_action_id = pactid
3446 and pop.chunk_number = chunk
3447 and pos.person_id = pop.person_id
3448 and pos.period_of_service_id = as1.period_of_service_id
3449 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3450 and not exists (
3451 select null
3452 from pay_assignment_actions ac2,
3453 pay_payroll_actions pa3,
3454 pay_action_interlocks int
3455 where int.locked_action_id = act.assignment_action_id
3456 and ac2.assignment_action_id = int.locking_action_id
3457 and pa3.payroll_action_id = ac2.payroll_action_id
3458 and pa3.action_type = 'CP')
3459 and not exists (
3460 select /*+ ORDERED*/
3461 null
3462 from per_all_assignments_f as3,
3463 pay_assignment_actions ac3
3464 where itpflg = 'N'
3465 and ac3.payroll_action_id = pa2.payroll_action_id
3466 and ac3.action_status not in ('C','S')
3467 and as3.assignment_id = ac3.assignment_id
3468 and pa2.effective_date between
3469 as3.effective_start_date and as3.effective_end_date
3470 and as3.person_id = as2.person_id)
3471 and ((pa2.action_type in ('P', 'U')
3472 and pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3473 and exists (select 1
3474 from pay_pre_payments ppp,
3475 pay_org_payment_methods_f pom
3476 where ppp.assignment_action_id = act.assignment_action_id
3477 and pom.org_payment_method_id = ppp.org_payment_method_id
3478 and pom.cost_payment = 'Y'
3479 and pa2.effective_date between
3480 pom.effective_start_date and pom.effective_end_date))
3481 or (pa2.action_type in ('H', 'M')
3482 and pa1.batch_process_mode in ('CLEARED', 'ALL')
3483 and exists (select 1
3484 from pay_pre_payments ppp,
3485 pay_org_payment_methods_f pom,
3486 pay_ce_reconciled_payments crp
3487 where ppp.pre_payment_id = act.pre_payment_id
3488 and pom.org_payment_method_id = ppp.org_payment_method_id
3489 and pom.cost_cleared_payment = 'Y'
3490 and crp.assignment_action_id = act.assignment_action_id
3491 and pa2.effective_date between
3492 pom.effective_start_date and pom.effective_end_date))
3493 or (pa2.action_type = 'E'
3494 and pa1.batch_process_mode in ('CLEARED', 'ALL')
3495 and exists (select 1
3496 from pay_pre_payments ppp,
3497 pay_org_payment_methods_f pom,
3498 pay_ce_reconciled_payments crp
3499 where ppp.pre_payment_id = act.pre_payment_id
3500 and pom.org_payment_method_id = ppp.org_payment_method_id
3501 and pom.cost_cleared_payment = 'Y'
3502 and pom.exclude_manual_payment = 'N'
3503 and crp.assignment_action_id = act.assignment_action_id
3504 and pa2.effective_date between
3505 pom.effective_start_date and pom.effective_end_date))
3506 or (pa2.action_type = 'D'
3507 and ((pa1.batch_process_mode in ('CLEARED', 'ALL')
3508 and exists (select 1
3509 from pay_action_interlocks int,
3510 pay_assignment_actions chq,
3511 pay_payroll_actions pcq,
3512 pay_pre_payments ppp,
3513 pay_org_payment_methods_f pom,
3514 pay_ce_reconciled_payments crp
3515 where int.locking_action_id = act.assignment_action_id
3516 and chq.assignment_action_id = int.locked_action_id
3517 and pcq.payroll_action_id = chq.payroll_action_id
3518 and pcq.action_type = 'H'
3519 and ppp.pre_payment_id = chq.pre_payment_id
3520 and pom.org_payment_method_id = ppp.org_payment_method_id
3521 and pom.cost_cleared_payment = 'Y'
3522 and crp.assignment_action_id = act.assignment_action_id
3523 and pa2.effective_date between
3524 pom.effective_start_date and pom.effective_end_date))
3525 or (pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3526 and exists (select 1
3527 from pay_action_interlocks int,
3528 pay_assignment_actions chq,
3529 pay_payroll_actions pcq,
3530 pay_pre_payments ppp,
3531 pay_org_payment_methods_f pom
3532 where int.locking_action_id = act.assignment_action_id
3533 and chq.assignment_action_id = int.locked_action_id
3534 and pcq.payroll_action_id = chq.payroll_action_id
3535 and pcq.action_type = 'H'
3536 and ppp.pre_payment_id = chq.pre_payment_id
3537 and pom.org_payment_method_id = ppp.org_payment_method_id
3538 and pom.cost_payment = 'Y'
3539 and pom.cost_cleared_void_payment = 'N'
3540 and pa2.effective_date between
3541 pom.effective_start_date and pom.effective_end_date)))))
3542 order by act.assignment_id
3543 for update of as1.assignment_id, pos.period_of_service_id;
3544 --
3545 cursor pmcostingcur
3546 (
3547 pactid number,
3548 stperson number,
3549 endperson number,
3550 class varchar2,
3551 itpflg varchar2
3552 ) is
3553 select /*+ ORDERED
3554 index(pa2 PAY_PAYROLL_ACTIONS_N5)
3555 index(as1 PER_ASSIGNMENTS_F_N4)
3556 index(as2 PER_ASSIGNMENTS_F_PK)
3557 USE_NL(pos as1) */
3558 act.assignment_action_id,
3559 act.assignment_id,
3560 act.tax_unit_id,
3561 act.payroll_action_id
3562 from pay_payroll_actions pa1,
3563 pay_payroll_actions pa2,
3564 pay_action_classifications pcl,
3565 per_periods_of_service pos,
3566 per_all_assignments_f as1,
3567 pay_assignment_actions act,
3568 per_all_assignments_f as2
3569 where pa1.payroll_action_id = pactid
3570 and pa2.consolidation_set_id = pa1.consolidation_set_id
3571 and pa2.effective_date between
3572 pa1.start_date and pa1.effective_date
3573 and act.payroll_action_id = pa2.payroll_action_id
3574 and act.action_status in ('C','S')
3575 and pcl.classification_name = class
3576 and pa2.action_type = pcl.action_type
3577 and as1.assignment_id = act.assignment_id
3578 and pa2.effective_date between
3579 as1.effective_start_date and as1.effective_end_date
3580 and as2.assignment_id = act.assignment_id
3581 and pa1.effective_date between
3582 as2.effective_start_date and as2.effective_end_date
3583 and pos.period_of_service_id = as1.period_of_service_id
3584 and pos.person_id between stperson and endperson
3585 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3586 and not exists (
3587 select null
3588 from pay_assignment_actions ac2,
3589 pay_payroll_actions pa3,
3590 pay_action_interlocks int
3591 where int.locked_action_id = act.assignment_action_id
3592 and ac2.assignment_action_id = int.locking_action_id
3593 and pa3.payroll_action_id = ac2.payroll_action_id
3594 and pa3.action_type = 'CP')
3595 and not exists (
3596 select /*+ ORDERED*/
3597 null
3598 from per_all_assignments_f as3,
3599 pay_assignment_actions ac3
3600 where itpflg = 'N'
3601 and ac3.payroll_action_id = pa2.payroll_action_id
3602 and ac3.action_status not in ('C','S')
3603 and as3.assignment_id = ac3.assignment_id
3604 and pa2.effective_date between
3605 as3.effective_start_date and as3.effective_end_date
3606 and as3.person_id = as2.person_id)
3607 and ((pa2.action_type in ('P', 'U')
3608 and pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3609 and exists (select 1
3610 from pay_pre_payments ppp,
3611 pay_org_payment_methods_f pom
3612 where ppp.assignment_action_id = act.assignment_action_id
3613 and pom.org_payment_method_id = ppp.org_payment_method_id
3614 and pom.cost_payment = 'Y'
3615 and pa2.effective_date between
3616 pom.effective_start_date and pom.effective_end_date))
3617 or (pa2.action_type in ('H', 'M')
3618 and pa1.batch_process_mode in ('CLEARED', 'ALL')
3619 and exists (select 1
3620 from pay_pre_payments ppp,
3621 pay_org_payment_methods_f pom,
3622 pay_ce_reconciled_payments crp
3623 where ppp.pre_payment_id = act.pre_payment_id
3624 and pom.org_payment_method_id = ppp.org_payment_method_id
3625 and pom.cost_cleared_payment = 'Y'
3626 and crp.assignment_action_id = act.assignment_action_id
3627 and pa2.effective_date between
3628 pom.effective_start_date and pom.effective_end_date))
3629 or (pa2.action_type = 'E'
3630 and pa1.batch_process_mode in ('CLEARED', 'ALL')
3631 and exists (select 1
3632 from pay_pre_payments ppp,
3633 pay_org_payment_methods_f pom,
3634 pay_ce_reconciled_payments crp
3635 where ppp.pre_payment_id = act.pre_payment_id
3636 and pom.org_payment_method_id = ppp.org_payment_method_id
3637 and pom.cost_cleared_payment = 'Y'
3638 and pom.exclude_manual_payment = 'N'
3639 and crp.assignment_action_id = act.assignment_action_id
3640 and pa2.effective_date between
3641 pom.effective_start_date and pom.effective_end_date))
3642 or (pa2.action_type = 'D'
3643 and ((pa1.batch_process_mode in ('CLEARED', 'ALL')
3644 and exists (select 1
3645 from pay_action_interlocks int,
3646 pay_assignment_actions chq,
3647 pay_payroll_actions pcq,
3648 pay_pre_payments ppp,
3649 pay_org_payment_methods_f pom,
3650 pay_ce_reconciled_payments crp
3651 where int.locking_action_id = act.assignment_action_id
3652 and chq.assignment_action_id = int.locked_action_id
3653 and pcq.payroll_action_id = chq.payroll_action_id
3654 and pcq.action_type = 'H'
3655 and ppp.pre_payment_id = chq.pre_payment_id
3656 and pom.org_payment_method_id = ppp.org_payment_method_id
3657 and pom.cost_cleared_payment = 'Y'
3658 and crp.assignment_action_id = act.assignment_action_id
3659 and pa2.effective_date between
3660 pom.effective_start_date and pom.effective_end_date))
3661 or (pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3662 and exists (select 1
3663 from pay_action_interlocks int,
3664 pay_assignment_actions chq,
3665 pay_payroll_actions pcq,
3666 pay_pre_payments ppp,
3667 pay_org_payment_methods_f pom
3668 where int.locking_action_id = act.assignment_action_id
3669 and chq.assignment_action_id = int.locked_action_id
3670 and pcq.payroll_action_id = chq.payroll_action_id
3671 and pcq.action_type = 'H'
3672 and ppp.pre_payment_id = chq.pre_payment_id
3673 and pom.org_payment_method_id = ppp.org_payment_method_id
3674 and pom.cost_payment = 'Y'
3675 and pom.cost_cleared_void_payment = 'N'
3676 and pa2.effective_date between
3677 pom.effective_start_date and pom.effective_end_date)))))
3678 order by act.assignment_id
3679 for update of as1.assignment_id, pos.period_of_service_id;
3680 --
3681 cursor pmcostingmpipcur
3682 (
3683 pactid number,
3684 chunk number,
3685 class varchar2,
3686 itpflg varchar2
3687 ) is
3688 select /*+ ORDERED
3689 index(pa2 PAY_PAYROLL_ACTIONS_PK)
3690 index(pos PER_PERIODS_OF_SERVICE_N3)
3691 index(as1 PER_ASSIGNMENTS_F_N4)
3692 index(act PAY_ASSIGNMENT_ACTIONS_N51)
3693 index(as2 PER_ASSIGNMENTS_F_PK)
3694 USE_NL(pos pop act pa2 as2 as1) */
3695 act.assignment_action_id,
3696 act.assignment_id,
3697 act.tax_unit_id,
3698 act.payroll_action_id
3699 from pay_payroll_actions pa1,
3700 pay_population_ranges pop,
3701 per_periods_of_service pos,
3702 per_all_assignments_f as1,
3703 pay_assignment_actions act,
3704 pay_payroll_actions pa2,
3705 pay_action_classifications pcl,
3706 per_all_assignments_f as2
3707 where pa1.payroll_action_id = pactid
3708 and pa2.consolidation_set_id = pa1.consolidation_set_id
3709 and pa2.effective_date between
3710 pa1.start_date and pa1.effective_date
3711 and act.payroll_action_id = pa2.payroll_action_id
3712 and act.action_status in ('C','S')
3713 and pcl.classification_name = class
3714 and pa2.action_type = pcl.action_type
3715 and as1.assignment_id = act.assignment_id
3716 and pa1.effective_date between
3717 as1.effective_start_date and as1.effective_end_date
3718 and as2.assignment_id = act.assignment_id
3719 and pa2.effective_date between
3720 as2.effective_start_date and as2.effective_end_date
3721 and pos.period_of_service_id = as1.period_of_service_id
3722 and pop.payroll_action_id = pactid
3723 and pop.chunk_number = chunk
3724 and pos.person_id = pop.person_id
3725 and (as2.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3726 and not exists (
3727 select null
3728 from pay_assignment_actions ac2,
3729 pay_payroll_actions pa3,
3730 pay_action_interlocks int
3731 where int.locked_action_id = act.assignment_action_id
3732 and ac2.assignment_action_id = int.locking_action_id
3733 and pa3.payroll_action_id = ac2.payroll_action_id
3734 and pa3.action_type = 'CP')
3735 and not exists (
3736 select /*+ ORDERED*/
3737 null
3738 from per_all_assignments_f as3,
3739 pay_assignment_actions ac3
3740 where itpflg = 'N'
3741 and ac3.payroll_action_id = pa2.payroll_action_id
3742 and ac3.action_status not in ('C','S')
3743 and as3.assignment_id = ac3.assignment_id
3744 and pa2.effective_date between
3745 as3.effective_start_date and as3.effective_end_date
3746 and as3.person_id = as2.person_id)
3747 and ((pa2.action_type in ('P', 'U')
3748 and pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3749 and exists (select 1
3750 from pay_pre_payments ppp,
3751 pay_org_payment_methods_f pom
3752 where ppp.assignment_action_id = act.assignment_action_id
3753 and pom.org_payment_method_id = ppp.org_payment_method_id
3754 and pom.cost_payment = 'Y'
3755 and pa2.effective_date between
3756 pom.effective_start_date and pom.effective_end_date))
3757 or (pa2.action_type in ('H', 'M')
3758 and pa1.batch_process_mode in ('CLEARED', 'ALL')
3759 and exists (select 1
3760 from pay_pre_payments ppp,
3761 pay_org_payment_methods_f pom,
3762 pay_ce_reconciled_payments crp
3763 where ppp.pre_payment_id = act.pre_payment_id
3764 and pom.org_payment_method_id = ppp.org_payment_method_id
3765 and pom.cost_cleared_payment = 'Y'
3766 and crp.assignment_action_id = act.assignment_action_id
3767 and pa2.effective_date between
3768 pom.effective_start_date and pom.effective_end_date))
3769 or (pa2.action_type = 'E'
3770 and pa1.batch_process_mode in ('CLEARED', 'ALL')
3771 and exists (select 1
3772 from pay_pre_payments ppp,
3773 pay_org_payment_methods_f pom,
3774 pay_ce_reconciled_payments crp
3775 where ppp.pre_payment_id = act.pre_payment_id
3776 and pom.org_payment_method_id = ppp.org_payment_method_id
3777 and pom.cost_cleared_payment = 'Y'
3778 and pom.exclude_manual_payment = 'N'
3779 and crp.assignment_action_id = act.assignment_action_id
3780 and pa2.effective_date between
3781 pom.effective_start_date and pom.effective_end_date))
3782 or (pa2.action_type = 'D'
3783 and ((pa1.batch_process_mode in ('CLEARED', 'ALL')
3784 and exists (select 1
3785 from pay_action_interlocks int,
3786 pay_assignment_actions chq,
3787 pay_payroll_actions pcq,
3788 pay_pre_payments ppp,
3789 pay_org_payment_methods_f pom,
3790 pay_ce_reconciled_payments crp
3791 where int.locking_action_id = act.assignment_action_id
3792 and chq.assignment_action_id = int.locked_action_id
3793 and pcq.payroll_action_id = chq.payroll_action_id
3794 and pcq.action_type = 'H'
3795 and ppp.pre_payment_id = chq.pre_payment_id
3796 and pom.org_payment_method_id = ppp.org_payment_method_id
3797 and pom.cost_cleared_payment = 'Y'
3798 and crp.assignment_action_id = act.assignment_action_id
3799 and pa2.effective_date between
3800 pom.effective_start_date and pom.effective_end_date))
3801 or (pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3802 and exists (select 1
3803 from pay_action_interlocks int,
3804 pay_assignment_actions chq,
3805 pay_payroll_actions pcq,
3806 pay_pre_payments ppp,
3807 pay_org_payment_methods_f pom
3808 where int.locking_action_id = act.assignment_action_id
3809 and chq.assignment_action_id = int.locked_action_id
3810 and pcq.payroll_action_id = chq.payroll_action_id
3811 and pcq.action_type = 'H'
3812 and ppp.pre_payment_id = chq.pre_payment_id
3813 and pom.org_payment_method_id = ppp.org_payment_method_id
3814 and pom.cost_payment = 'Y'
3815 and pom.cost_cleared_void_payment = 'N'
3816 and pa2.effective_date between
3817 pom.effective_start_date and pom.effective_end_date)))))
3818 order by act.assignment_id
3819 for update of as1.assignment_id, pos.period_of_service_id;
3820 --
3821 lockingactid number;
3822 lockedactid number;
3823 assignid number;
3824 prev_assignid number;
3825 lpactid number;
3826 prev_pactid number;
3827 greid number;
3828 --
3829 begin
3830 prev_assignid := null;
3831 prev_pactid := null;
3832 if (g_many_procs_in_period = 'Y') then
3833 open pmcostingmpipcur(pactid,chunk,class,itpflg);
3834 elsif (use_pop_person = 1) then
3835 open pmcostingpopcur(pactid,chunk,class,itpflg);
3836 else
3837 open pmcostingcur(pactid,stperson,endperson,class,itpflg);
3838 end if;
3839 loop
3840 if (g_many_procs_in_period = 'Y') then
3841 fetch pmcostingmpipcur into lockedactid,assignid,greid,lpactid;
3842 exit when pmcostingmpipcur%notfound;
3843 elsif (use_pop_person = 1) then
3844 fetch pmcostingpopcur into lockedactid,assignid,greid,lpactid;
3845 exit when pmcostingpopcur%notfound;
3846 else
3847 fetch pmcostingcur into lockedactid,assignid,greid,lpactid;
3848 exit when pmcostingcur%notfound;
3849 end if;
3850 --
3851 /* process the insert of assignment actions */
3852 /* logic prevents more than one action per assignment */
3853 if(prev_assignid is null OR prev_assignid <> assignid OR
3854 prev_pactid <> lpactid) then
3855 -- get a value for the action id that is locking.
3856 select pay_assignment_actions_s.nextval
3857 into lockingactid
3858 from dual;
3859 --
3860 -- insert into pay_assignment_actions.
3861 insact(lockingactid,assignid,pactid,rand_chunk,greid);
3862 end if;
3863 --
3864 -- insert into interlocks table.
3865 insint(lockingactid,lockedactid);
3866 --
3867 prev_assignid := assignid;
3868 prev_pactid := lpactid;
3869 end loop;
3870 if (g_many_procs_in_period = 'Y') then
3871 close pmcostingmpipcur;
3872 elsif (use_pop_person = 1) then
3873 close pmcostingpopcur;
3874 else
3875 close pmcostingcur;
3876 end if;
3877 commit;
3878 end proc_paymcosting;
3879 --
3880 ------------------------------- proc_estcosts ------------------------------
3881 /*
3882 NAME
3883 proc_estcosts - insert actions for Estimate Costing action type.
3884 DESCRIPTION
3885 For the range defined by the starting and ending person_id,
3886 inserts a chunk of assignment actions
3887 NOTES
3888 <none>
3889 */
3890 procedure proc_estcosts
3891 (
3892 pactid in number,
3893 stperson in number,
3894 endperson in number,
3895 chunk in number,
3896 rand_chunk in number,
3897 class in varchar2,
3898 itpflg in varchar2,
3899 use_pop_person in number
3900 ) is
3901 --
3902 cursor estcostingpopcur
3903 (
3904 pactid number,
3905 chunk number,
3906 class varchar2,
3907 itpflg varchar2
3908 ) is
3909 select /*+ ORDERED
3910 index(pa2 PAY_PAYROLL_ACTIONS_N5)
3911 index(as1 PER_ASSIGNMENTS_F_N4)
3912 index(as2 PER_ASSIGNMENTS_F_PK)
3913 USE_NL(pop pos as1) */
3914 act.assignment_action_id,
3915 act.assignment_id,
3916 act.tax_unit_id
3917 from pay_payroll_actions pa1,
3918 pay_all_payrolls_f pay,
3919 per_time_periods ptp,
3920 pay_payroll_actions pa2,
3921 pay_action_classifications pcl,
3922 pay_population_ranges pop,
3923 per_periods_of_service pos,
3924 per_all_assignments_f as1,
3925 pay_assignment_actions act,
3926 per_all_assignments_f as2
3927 where pa1.payroll_action_id = pactid
3928 and pay.consolidation_set_id = pa1.consolidation_set_id
3929 and pa1.effective_date between
3930 pay.effective_start_date and pay.effective_end_date
3931 and ptp.payroll_id = pay.payroll_id
3932 and pa1.start_date between
3933 ptp.start_date and ptp.end_date
3934 and pa2.consolidation_set_id = pa1.consolidation_set_id
3935 and pa2.effective_date between
3936 ptp.start_date and ptp.end_date
3937 and act.payroll_action_id = pa2.payroll_action_id
3938 and act.action_status in ('C','S')
3939 and pcl.classification_name = class
3940 and pa2.action_type = pcl.action_type
3941 and as1.assignment_id = act.assignment_id
3942 and pa2.effective_date between
3943 as1.effective_start_date and as1.effective_end_date
3944 and as2.assignment_id = act.assignment_id
3945 and pa1.effective_date between
3946 as2.effective_start_date and as2.effective_end_date
3947 and as2.payroll_id = as1.payroll_id
3948 and pop.payroll_action_id = pactid
3949 and pop.chunk_number = chunk
3950 and pos.person_id = pop.person_id
3951 and pos.period_of_service_id = as1.period_of_service_id
3952 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3953 and not exists (
3954 select /*+ ORDERED*/
3955 null
3956 from per_all_assignments_f as3,
3957 pay_assignment_actions ac3
3958 where itpflg = 'N'
3959 and ac3.payroll_action_id = pa2.payroll_action_id
3960 and ac3.action_status not in ('C','S')
3961 and as3.assignment_id = ac3.assignment_id
3962 and pa2.effective_date between
3963 as3.effective_start_date and as3.effective_end_date
3964 and as3.person_id = as2.person_id)
3965 order by act.assignment_id
3966 for update of as1.assignment_id, pos.period_of_service_id;
3967 --
3968 cursor estcostingcur
3969 (
3970 pactid number,
3971 stperson number,
3972 endperson number,
3973 class varchar2,
3974 itpflg varchar2
3975 ) is
3976 select /*+ ORDERED
3977 index(pa2 PAY_PAYROLL_ACTIONS_N5)
3978 index(as1 PER_ASSIGNMENTS_F_N4)
3979 index(as2 PER_ASSIGNMENTS_F_PK)
3980 USE_NL(pos as1) */
3981 act.assignment_action_id,
3982 act.assignment_id,
3983 act.tax_unit_id
3984 from pay_payroll_actions pa1,
3985 pay_all_payrolls_f pay,
3986 per_time_periods ptp,
3987 pay_payroll_actions pa2,
3988 pay_action_classifications pcl,
3989 per_periods_of_service pos,
3990 per_all_assignments_f as1,
3991 pay_assignment_actions act,
3992 per_all_assignments_f as2
3993 where pa1.payroll_action_id = pactid
3994 and pay.consolidation_set_id = pa1.consolidation_set_id
3995 and pa1.effective_date between
3996 pay.effective_start_date and pay.effective_end_date
3997 and ptp.payroll_id = pay.payroll_id
3998 and pa1.start_date between
3999 ptp.start_date and ptp.end_date
4000 and pa2.consolidation_set_id = pa1.consolidation_set_id
4001 and pa2.effective_date between
4002 ptp.start_date and ptp.end_date
4003 and act.payroll_action_id = pa2.payroll_action_id
4004 and act.action_status in ('C','S')
4005 and pcl.classification_name = class
4006 and pa2.action_type = pcl.action_type
4007 and as1.assignment_id = act.assignment_id
4008 and pa2.effective_date between
4009 as1.effective_start_date and as1.effective_end_date
4010 and as2.assignment_id = act.assignment_id
4011 and pa1.effective_date between
4012 as2.effective_start_date and as2.effective_end_date
4013 and as2.payroll_id = as1.payroll_id
4014 and pos.period_of_service_id = as1.period_of_service_id
4015 and pos.person_id between stperson and endperson
4016 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
4017 and not exists (
4018 select /*+ ORDERED*/
4019 null
4020 from per_all_assignments_f as3,
4021 pay_assignment_actions ac3
4022 where itpflg = 'N'
4023 and ac3.payroll_action_id = pa2.payroll_action_id
4024 and ac3.action_status not in ('C','S')
4025 and as3.assignment_id = ac3.assignment_id
4026 and pa2.effective_date between
4027 as3.effective_start_date and as3.effective_end_date
4028 and as3.person_id = as2.person_id)
4029 order by act.assignment_id
4030 for update of as1.assignment_id, pos.period_of_service_id;
4031 --
4032 lockingactid number;
4033 lockedactid number;
4034 assignid number;
4035 prev_assignid number;
4036 greid number;
4037 --
4038 begin
4039 prev_assignid := null;
4040 if (use_pop_person = 1) then
4041 open estcostingpopcur(pactid,chunk,class,itpflg);
4042 else
4043 open estcostingcur(pactid,stperson,endperson,class,itpflg);
4044 end if;
4045 loop
4046 if (use_pop_person = 1) then
4047 fetch estcostingpopcur into lockedactid,assignid,greid;
4048 exit when estcostingpopcur%notfound;
4049 else
4050 fetch estcostingcur into lockedactid,assignid,greid;
4051 exit when estcostingcur%notfound;
4052 end if;
4053 --
4054 /* process the insert of assignment actions */
4055 /* logic prevents more than one action per assignment */
4056 if(prev_assignid is null OR prev_assignid <> assignid) then
4057 -- get a value for the action id that is locking.
4058 select pay_assignment_actions_s.nextval
4059 into lockingactid
4060 from dual;
4061 --
4062 -- insert into pay_assignment_actions.
4063 insact(lockingactid,assignid,pactid,rand_chunk,greid);
4064 end if;
4065 --
4066 prev_assignid := assignid;
4067 end loop;
4068 if (use_pop_person = 1) then
4069 close estcostingpopcur;
4070 else
4071 close estcostingcur;
4072 end if;
4073 commit;
4074 end proc_estcosts;
4075 --
4076 ---------------------------------- procbee ---------------------------------
4077 /*
4078 NAME
4079 procbee - insert assignment actions for Batch Element Entry.
4080 DESCRIPTION
4081 Insert assignment actions for the Batch Element Entry process.
4082 NOTES
4083 The insert of assignment actions for Batch Element Entry is based
4084 on the followig logic: We select all the assignments within the
4085 specified range. One assignment action is then inserted
4086 for each of the assignment selected.
4087 */
4088 procedure procbee
4089 (
4090 pactid in number,
4091 stperson in number,
4092 endperson in number,
4093 chunk in number,
4094 rand_chunk in number,
4095 use_pop_person in number
4096 ) is
4097 --
4098 cursor beepopcur
4099 (
4100 pactid number,
4101 chunk number
4102 ) is
4103 select asg.assignment_id
4104 from pay_payroll_actions pac,
4105 pay_population_ranges pop,
4106 pay_batch_headers bth,
4107 pay_batch_lines btl,
4108 per_all_assignments_f asg
4109 where pac.payroll_action_id = pactid
4110 and pac.action_type = 'BEE'
4111 and pac.batch_id = bth.batch_id
4112 and bth.batch_id = btl.batch_id
4113 and btl.assignment_id = asg.assignment_id
4114 and btl.effective_date between asg.effective_start_date
4115 and asg.effective_end_date
4116 and pop.payroll_action_id = pactid
4117 and pop.chunk_number = chunk
4118 and asg.person_id = pop.person_id
4119 order by asg.assignment_id
4120 for update of asg.assignment_id, btl.batch_line_id;
4121 --
4122 cursor beecur
4123 (
4124 pactid number,
4125 stperson number,
4126 endperson number
4127 ) is
4128 select asg.assignment_id
4129 from pay_payroll_actions pac,
4130 pay_batch_lines btl,
4131 per_all_assignments_f asg
4132 where pac.payroll_action_id = pactid
4133 and pac.action_type = 'BEE'
4134 and pac.batch_id = btl.batch_id
4135 and btl.assignment_id = asg.assignment_id
4136 and btl.effective_date between asg.effective_start_date
4137 and asg.effective_end_date
4138 and asg.person_id between stperson and endperson
4139 order by asg.assignment_id
4140 for update of asg.assignment_id, btl.batch_line_id;
4141 --
4142 asgactid number;
4143 assignid number;
4144 preasgid number;
4145 --
4146 begin
4147 preasgid := null;
4148 if (use_pop_person = 1) then
4149 open beepopcur(pactid,chunk);
4150 else
4151 open beecur(pactid,stperson,endperson);
4152 end if;
4153 loop
4154 if (use_pop_person = 1) then
4155 fetch beepopcur into assignid;
4156 exit when beepopcur%notfound;
4157 else
4158 fetch beecur into assignid;
4159 exit when beecur%notfound;
4160 end if;
4161 --
4162 -- Get an assignment_action_id.
4163 select pay_assignment_actions_s.nextval
4164 into asgactid
4165 from dual;
4166 --
4167 if preasgid is null or preasgid <> assignid then
4168 -- Insert an assignment action for each action.
4169 insact(asgactid,assignid,pactid,rand_chunk,null,null);
4170 preasgid := assignid;
4171 end if;
4172 --
4173 end loop;
4174 if (use_pop_person = 1) then
4175 close beepopcur;
4176 else
4177 close beecur;
4178 end if;
4179 end procbee;
4180 --
4181 ---------------------------------- proctgl ---------------------------------
4182 /*
4183 NAME
4184 proctgl - insert assignment actions for Transfer to GL.
4185 DESCRIPTION
4186 Insert assignment actions for the Transfer to GL process.
4187 NOTES
4188 The insert of assignment actions for Transfer to GL is based
4189 on the followig logic: We select all the (Payroll Run)
4190 assignment actions that have been costed within the
4191 specified date range. One assignment action is then inserted
4192 for each of the assignment actions selected. In addition,
4193 an interlock row is inserted from the newly created TGL action
4194 to both the Costing action and to the Payroll Run actions that
4195 were costed by it. (Phew)
4196 */
4197 procedure proctgl
4198 (
4199 pactid in number,
4200 stperson in number,
4201 endperson in number,
4202 chunk in number,
4203 rand_chunk in number,
4204 itpflg in varchar2,
4205 use_pop_person in number
4206 ) is
4207 cursor tglpopcur
4208 (
4209 pactid number,
4210 chunk number,
4211 itpflg varchar2
4212 ) is
4213 select /*+ ORDERED
4214 index(pa2 PAY_PAYROLL_ACTIONS_N5)
4215 index(as1 PER_ASSIGNMENTS_F_PK)
4216 index(as2 PER_ASSIGNMENTS_F_N4)
4217 USE_NL(pop pos as1 as2) */
4218 ac2.assignment_action_id,
4219 ac2.assignment_id,
4220 ac2.tax_unit_id,
4221 pa2.action_type
4222 from pay_payroll_actions pa,
4223 pay_payroll_actions pa2,
4224 pay_action_classifications pcl,
4225 pay_population_ranges pop,
4226 per_periods_of_service pos,
4227 per_all_assignments_f as2,
4228 pay_assignment_actions ac2,
4229 per_all_assignments_f as1
4230 where pa.payroll_action_id = pactid
4231 and pa2.consolidation_set_id = pa.consolidation_set_id
4232 and pa2.effective_date between
4233 pa.start_date and pa.effective_date
4234 and ac2.payroll_action_id = pa2.payroll_action_id
4235 and ac2.action_status = 'C'
4236 and pcl.classification_name = 'TRANSGL'
4237 and pa2.action_type = pcl.action_type
4238 and as2.assignment_id = ac2.assignment_id
4239 and pa.effective_date between
4240 as2.effective_start_date and as2.effective_end_date
4241 and as1.assignment_id = ac2.assignment_id
4242 and pa2.effective_date between
4243 as1.effective_start_date and as1.effective_end_date
4244 and nvl(decode(pa2.action_type, 'EC', nvl(pa.payroll_id, as1.payroll_id),
4245 'CP', nvl(pa.payroll_id, as1.payroll_id),
4246 as1.payroll_id), -999) = nvl(as1.payroll_id, -999)
4247 and pos.period_of_service_id = as2.period_of_service_id
4248 and pop.payroll_action_id = pactid
4249 and pop.chunk_number = chunk
4250 and pos.person_id = pop.person_id
4251 and not exists (
4252 select null
4253 from pay_assignment_actions ac3,
4254 pay_payroll_actions pa3,
4255 pay_action_interlocks in3
4256 where in3.locked_action_id = ac2.assignment_action_id
4257 and ac3.assignment_action_id = in3.locking_action_id
4258 and pa3.payroll_action_id = ac3.payroll_action_id
4259 and pa3.action_type = pa.action_type)
4260 and not exists (
4261 select /*+ ORDERED*/
4262 null
4263 from per_all_assignments_f as3,
4264 pay_assignment_actions ac3
4265 where itpflg = 'N'
4266 and ac3.payroll_action_id = pa2.payroll_action_id
4267 and ac3.action_status not in ('C','S')
4268 and as3.assignment_id = ac3.assignment_id
4269 and pa2.effective_date between
4270 as3.effective_start_date and as3.effective_end_date
4271 and as3.person_id = as1.person_id)
4272 order by ac2.assignment_id, ac2.payroll_action_id, ac2.assignment_action_id
4273 for update of as2.assignment_id, pos.period_of_service_id;
4274 --
4275 cursor tglcur
4276 (
4277 pactid number,
4278 stperson number,
4279 endperson number,
4280 itpflg varchar2
4281 ) is
4282 select /*+ ORDERED
4283 index(pa2 PAY_PAYROLL_ACTIONS_N5)
4284 index(as1 PER_ASSIGNMENTS_F_PK)
4285 index(as2 PER_ASSIGNMENTS_F_N4)
4286 USE_NL(pos as1 as2) */
4287 ac2.assignment_action_id,
4288 ac2.assignment_id,
4289 ac2.tax_unit_id,
4290 pa2.action_type
4291 from pay_payroll_actions pa,
4292 pay_payroll_actions pa2,
4293 pay_action_classifications pcl,
4294 per_periods_of_service pos,
4295 per_all_assignments_f as2,
4296 pay_assignment_actions ac2,
4297 per_all_assignments_f as1
4298 where pa.payroll_action_id = pactid
4299 and pa2.consolidation_set_id = pa.consolidation_set_id
4300 and pa2.effective_date between
4301 pa.start_date and pa.effective_date
4302 and ac2.payroll_action_id = pa2.payroll_action_id
4303 and ac2.action_status = 'C'
4304 and pcl.classification_name = 'TRANSGL'
4305 and pa2.action_type = pcl.action_type
4306 and as2.assignment_id = ac2.assignment_id
4307 and pa.effective_date between
4308 as2.effective_start_date and as2.effective_end_date
4309 and as1.assignment_id = ac2.assignment_id
4310 and pa2.effective_date between
4311 as1.effective_start_date and as1.effective_end_date
4312 and nvl(decode(pa2.action_type, 'EC', nvl(pa.payroll_id, as1.payroll_id),
4313 'CP', nvl(pa.payroll_id, as1.payroll_id),
4314 as1.payroll_id), -999) = nvl(as1.payroll_id, -999)
4315 and pos.period_of_service_id = as2.period_of_service_id
4316 and pos.person_id between
4317 stperson and endperson
4318 and not exists (
4319 select null
4320 from pay_assignment_actions ac3,
4321 pay_payroll_actions pa3,
4322 pay_action_interlocks in3
4323 where in3.locked_action_id = ac2.assignment_action_id
4324 and ac3.assignment_action_id = in3.locking_action_id
4325 and pa3.payroll_action_id = ac3.payroll_action_id
4326 and pa3.action_type = pa.action_type)
4327 and not exists (
4328 select /*+ ORDERED*/
4329 null
4330 from per_all_assignments_f as3,
4331 pay_assignment_actions ac3
4332 where itpflg = 'N'
4333 and ac3.payroll_action_id = pa2.payroll_action_id
4334 and ac3.action_status not in ('C','S')
4335 and as3.assignment_id = ac3.assignment_id
4336 and pa2.effective_date between
4337 as3.effective_start_date and as3.effective_end_date
4338 and as3.person_id = as1.person_id)
4339 order by ac2.assignment_id, ac2.payroll_action_id, ac2.assignment_action_id
4340 for update of as2.assignment_id, pos.period_of_service_id;
4341 --
4342 cursor tglmpipcur
4343 (
4344 pactid number,
4345 chunk number,
4346 itpflg varchar2
4347 ) is
4348 select /*+ ORDERED
4349 index(pa2 PAY_PAYROLL_ACTIONS_PK)
4350 index(pos PER_PERIODS_OF_SERVICE_N3)
4351 index(as2 PER_ASSIGNMENTS_F_N4)
4352 index(ac2 PAY_ASSIGNMENT_ACTIONS_N51)
4353 index(as1 PER_ASSIGNMENTS_F_PK)
4354 USE_NL(pos pop as1 as2) */
4355 ac2.assignment_action_id,
4356 ac2.assignment_id,
4357 ac2.tax_unit_id,
4358 pa2.action_type
4359 from pay_payroll_actions pa,
4360 pay_population_ranges pop,
4361 per_periods_of_service pos,
4362 per_all_assignments_f as2,
4363 pay_assignment_actions ac2,
4364 pay_payroll_actions pa2,
4365 pay_action_classifications pcl,
4366 per_all_assignments_f as1
4367 where pa.payroll_action_id = pactid
4368 and pa2.consolidation_set_id = pa.consolidation_set_id
4369 and pa2.effective_date between
4370 pa.start_date and pa.effective_date
4371 and ac2.payroll_action_id = pa2.payroll_action_id
4372 and ac2.action_status = 'C'
4373 and pcl.classification_name = 'TRANSGL'
4374 and pa2.action_type = pcl.action_type
4375 and as2.assignment_id = ac2.assignment_id
4376 and pa.effective_date between
4377 as2.effective_start_date and as2.effective_end_date
4378 and as1.assignment_id = ac2.assignment_id
4379 and pa2.effective_date between
4380 as1.effective_start_date and as1.effective_end_date
4381 and nvl(decode(pa2.action_type, 'EC', nvl(pa.payroll_id, as1.payroll_id),
4382 'CP', nvl(pa.payroll_id, as1.payroll_id),
4383 as1.payroll_id), -999) = nvl(as1.payroll_id, -999)
4384 and pos.period_of_service_id = as2.period_of_service_id
4385 and pop.payroll_action_id = pactid
4386 and pop.chunk_number = chunk
4387 and pos.person_id = pop.person_id
4388 and not exists (
4389 select null
4390 from pay_assignment_actions ac3,
4391 pay_payroll_actions pa3,
4392 pay_action_interlocks in3
4393 where in3.locked_action_id = ac2.assignment_action_id
4394 and ac3.assignment_action_id = in3.locking_action_id
4395 and pa3.payroll_action_id = ac3.payroll_action_id
4396 and pa3.action_type = pa.action_type)
4397 and not exists (
4398 select /*+ ORDERED*/
4399 null
4400 from per_all_assignments_f as3,
4401 pay_assignment_actions ac3
4402 where itpflg = 'N'
4403 and ac3.payroll_action_id = pa2.payroll_action_id
4404 and ac3.action_status not in ('C','S')
4405 and as3.assignment_id = ac3.assignment_id
4406 and pa2.effective_date between
4407 as3.effective_start_date and as3.effective_end_date
4408 and as3.person_id = as1.person_id)
4409 order by ac2.assignment_id, ac2.payroll_action_id, ac2.assignment_action_id
4410 for update of as2.assignment_id, pos.period_of_service_id;
4411 --
4412 cursor costedacts
4413 (
4414 pactid number,
4415 costactid number
4416 ) is
4417 select ac1.assignment_action_id
4418 from pay_action_interlocks in2,
4419 pay_assignment_actions ac1,
4420 pay_payroll_actions pa1,
4421 pay_action_classifications pcl1,
4422 per_all_assignments_f as1,
4423 pay_payroll_actions pa
4424 where pa.payroll_action_id = pactid
4425 and in2.locking_action_id = costactid
4426 and ac1.assignment_action_id = in2.locked_action_id
4427 and ac1.source_action_id is null
4428 and pa1.payroll_action_id = ac1.payroll_action_id
4429 and pcl1.action_type = pa1.action_type
4430 and pcl1.classification_name = 'COSTED'
4431 and as1.assignment_id = ac1.assignment_id
4432 and (as1.payroll_id = pa.payroll_id or pa.payroll_id is null)
4433 and pa1.effective_date between
4434 as1.effective_start_date and as1.effective_end_date;
4435 --
4436 lockingactid number;
4437 lockedactid number;
4438 assignid number;
4439 actype pay_payroll_actions.action_type%TYPE;
4440 pmnt_act_type pay_payroll_actions.action_type%TYPE;
4441 prepay_aa_id number;
4442 runactid number;
4443 greid number;
4444 not_paid number;
4445 --
4446 begin
4447 if (g_many_procs_in_period = 'Y') then
4448 open tglmpipcur(pactid,chunk,itpflg);
4449 elsif (use_pop_person = 1) then
4450 open tglpopcur(pactid,chunk,itpflg);
4451 else
4452 open tglcur(pactid,stperson,endperson,itpflg);
4453 end if;
4454 loop
4455 if (g_many_procs_in_period = 'Y') then
4456 fetch tglmpipcur into lockedactid,assignid,greid,actype;
4457 exit when tglmpipcur%notfound;
4458 elsif (use_pop_person = 1) then
4459 fetch tglpopcur into lockedactid,assignid,greid,actype;
4460 exit when tglpopcur%notfound;
4461 else
4462 fetch tglcur into lockedactid,assignid,greid,actype;
4463 exit when tglcur%notfound;
4464 end if;
4465 --
4466 if (actype <> 'EC' and actype <> 'CP') then
4467
4468 -- For costings and Retrocostings we create an assignment
4469 -- action for each run action - and interlock it
4470 open costedacts(pactid,lockedactid);
4471 loop
4472 fetch costedacts into runactid;
4473 exit when costedacts%notfound;
4474 --
4475 --
4476 -- Get an assignment_action_id.
4477 select pay_assignment_actions_s.nextval
4478 into lockingactid
4479 from dual;
4480 --
4481 -- Insert an assignment action for each action.
4482 insact(lockingactid,assignid,pactid,rand_chunk,greid,null);
4483 --
4484 -- We follow this with the insert of two interlock
4485 -- rows. One interlock points to the Costing action
4486 -- and the other to the Payroll Run action that was
4487 -- costed in the first place.
4488 insint(lockingactid,lockedactid); -- lock to the Costing.
4489 insint(lockingactid,runactid); -- lock to original Payroll Run.
4490 end loop;
4491 close costedacts;
4492 else
4493 -- Estimate Costings and Payment Costings we're not interested
4494 -- in runs and don't
4495 -- interlock them
4496
4497 not_paid := 0;
4498
4499 -- if Payment Costing check that its from a Prepayment
4500 -- that hasn't had all pre payments paid
4501 if (actype = 'CP') then
4502
4503 select distinct(pa.action_type)
4504 into pmnt_act_type
4505 from pay_action_interlocks int,
4506 pay_assignment_actions aa,
4507 pay_payroll_actions pa
4508 where int.locking_action_id = lockedactid
4509 and aa.assignment_action_id = int.locked_action_id
4510 and pa.payroll_action_id = aa.payroll_action_id;
4511
4512 if (pmnt_act_type in ('P', 'U')) then
4513 -- Bug 6919216 - Fixed query to consider only payments
4514 -- that are costed and needed be to transferred to GL.
4515 select count(*)
4516 into not_paid
4517 from pay_action_interlocks int,
4518 pay_pre_payments ppp,
4519 pay_org_payment_methods_f opm
4520 where int.locking_action_id = lockedactid
4521 and ppp.assignment_action_id = int.locked_action_id
4522 and opm.org_payment_method_id = ppp.org_payment_method_id
4523 and opm.cost_payment = 'Y'
4524 and opm.transfer_to_gl_flag = 'Y'
4525 and not exists
4526 (select 1
4527 from pay_assignment_actions aa
4528 where aa.pre_payment_id = ppp.pre_payment_id);
4529
4530 end if;
4531
4532 end if;
4533
4534 if (not_paid = 0) then
4535
4536 -- Get an assignment_action_id.
4537 select pay_assignment_actions_s.nextval
4538 into lockingactid
4539 from dual;
4540 --
4541 -- Insert an assignment action for each action.
4542 insact(lockingactid,assignid,pactid,rand_chunk,greid,null);
4543 --
4544 -- We interlock the costing action
4545 insint(lockingactid,lockedactid); -- lock to the Costing.
4546
4547 end if;
4548 end if;
4549 end loop;
4550 if (g_many_procs_in_period = 'Y') then
4551 close tglmpipcur;
4552 elsif (use_pop_person = 1) then
4553 close tglpopcur;
4554 else
4555 close tglcur;
4556 end if;
4557 end proctgl;
4558 --
4559 ---------------------------------- proqpp ---------------------------------
4560 /*
4561 NAME
4562 proqpp - insert assignment actions for QuickPay prepayment
4563 DESCRIPTION
4564 Insert assignment actions for the QuickPay prepayment process
4565 NOTES
4566 An assignment action is inserted for the assignment which is specified
4567 on the target_payroll_action_id column of the Quick Pay action.
4568 When this is done the action population status is set to complete
4569 */
4570 procedure proqpp
4571 (
4572 pactid in number,
4573 lub in varchar2,
4574 lul in varchar2
4575 ) is
4576 cursor qpcur ( pactid number ) is
4577 select ac1.assignment_action_id,
4578 ac1.assignment_id,
4579 ac1.tax_unit_id,
4580 pa1.action_type
4581 from pay_assignment_actions ac1,
4582 pay_payroll_actions pa1
4583 where pa1.payroll_action_id = pactid
4584 and pa1.target_payroll_action_id = ac1.payroll_action_id
4585 and not exists (
4586 select 1
4587 from pay_assignment_actions ac2
4588 where ac2.payroll_action_id = pactid
4589 and ac2.assignment_id = ac1.assignment_id)
4590 for update of ac1.assignment_action_id ;
4591 --
4592 lockingactid number;
4593 lockedactid number;
4594 assignid number;
4595 greid number;
4596 atype pay_payroll_actions.action_type%type;
4597 --
4598 begin
4599 open qpcur(pactid);
4600 fetch qpcur into lockedactid, assignid, greid, atype;
4601 if qpcur%notfound then
4602 close qpcur ;
4603 return ;
4604 end if;
4605 close qpcur ;
4606 --
4607 -- Get an assignment_action_id.
4608 select pay_assignment_actions_s.nextval
4609 into lockingactid
4610 from dual;
4611 --
4612 -- Insert an assignment action for the action
4613 insact(lockingactid,assignid,pactid,1,greid);
4614 --
4615 -- Insert an interlock row to lock the QuickPay run assignment action
4616 insint(lockingactid,lockedactid);
4617 --
4618 -- Set the action population status to 'C' (complete)
4619 -- Also sets date_earned value.
4620 update_pact(pactid, 'C', atype, sysdate,lub,lul);
4621 --
4622 end proqpp ;
4623 --
4624 ---------------------------------- procarc --------------------------------
4625 /*
4626 NAME
4627 procarc - insert assignment actions for Archive process
4628 DESCRIPTION
4629 Insert assignment actions for the Archive process
4630 NOTES
4631 This dynamically calls legislative code to perform the insertion
4632 of the assignment actions, since it is the legislation that
4633 knows which assignments are to be included in the archive.
4634 */
4635 procedure procarc(pactid in number,
4636 stperson in number,
4637 endperson in number,
4638 chunk in number
4639 )
4640 is
4641 sql_cur number;
4642 ignore number;
4643 action_proc varchar2(60);
4644 statem varchar2(256);
4645 begin
4646 select assignment_action_code
4647 into action_proc
4648 from pay_report_format_mappings_f prfm,
4649 pay_payroll_actions ppa
4650 where ppa.payroll_action_id = pactid
4651 and ppa.report_type = prfm.report_type
4652 and ppa.report_qualifier = prfm.report_qualifier
4653 and ppa.report_category = prfm.report_category
4654 and ppa.effective_date between prfm.effective_start_date
4655 and prfm.effective_end_date;
4656 --
4657 statem := 'BEGIN '||action_proc||'(:pactid, :stperson,'||
4658 ' :endperson, :chunk); END;';
4659 --
4660 sql_cur := dbms_sql.open_cursor;
4661 dbms_sql.parse(sql_cur,
4662 statem,
4663 dbms_sql.v7);
4664 dbms_sql.bind_variable(sql_cur, ':pactid', pactid);
4665 dbms_sql.bind_variable(sql_cur, ':stperson', stperson);
4666 dbms_sql.bind_variable(sql_cur, ':endperson', endperson);
4667 dbms_sql.bind_variable(sql_cur, ':chunk', chunk);
4668 ignore := dbms_sql.execute(sql_cur);
4669 dbms_sql.close_cursor(sql_cur);
4670 --
4671 return;
4672 --
4673 exception
4674 when others then
4675 if (dbms_sql.is_open(sql_cur)) then
4676 dbms_sql.close_cursor(sql_cur);
4677 end if;
4678 raise;
4679 end procarc;
4680 --
4681 ---------------------------------- procpp ----------------------------------
4682 /*
4683 NAME
4684 procpp - process a single chunk for PP payment (Bank or Post Office payment)
4685 process.
4686 DESCRIPTION
4687 This function takes a range as defined by the starting and
4688 ending person_id and inserts a chunk of assignment actions
4689 plus their associated interlock rows. This function for the
4690 Bank or Post Office payment (PP) action only.
4691 NOTES
4692 <none>
4693 */
4694 procedure procpp
4695 (
4696 pactid in number, -- payroll_action_id.
4697 stperson in number, -- starting person_id of range.
4698 endperson in number, -- ending person_id of range.
4699 chunk in number, -- current chunk_number.
4700 rand_chunk in number, -- current chunk_number.
4701 itpflg in varchar2, -- legislation type.
4702 ptype in number, -- payment_type_id.
4703 use_pop_person in number -- use population_ranges person_id column
4704 ) is
4705 cursor pppopcur
4706 (
4707 pactid number,
4708 chunk number,
4709 itpflg varchar2,
4710 ptype number
4711 ) is
4712 select /*+ ORDERED
4713 INDEX(pa2 PAY_PAYROLL_ACTIONS_N5)
4714 INDEX(pos PER_PERIODS_OF_SERVICE_N3)
4715 INDEX(as1 PER_ASSIGNMENTS_N4)
4716 INDEX(as2 PER_ASSIGNMENTS_F_PK)
4717 INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
4718 index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
4719 USE_NL(pop pos ppp opm as1 act as2) */
4720 act.assignment_action_id,
4721 act.assignment_id,
4722 act.tax_unit_id,
4723 ppp.pre_payment_id
4724 from pay_payroll_actions pa1,
4725 pay_payroll_actions pa2,
4726 pay_action_classifications pcl,
4727 pay_population_ranges pop,
4728 per_periods_of_service pos,
4729 per_all_assignments_f as1,
4730 pay_assignment_actions act,
4731 per_all_assignments_f as2,
4732 pay_pre_payments ppp,
4733 pay_org_payment_methods_f opm
4734 where pa1.payroll_action_id = pactid
4735 and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
4736 and pa2.effective_date between
4737 pa1.start_date and pa1.effective_date
4738 and act.payroll_action_id = pa2.payroll_action_id
4739 and act.action_status = 'C'
4740 and pcl.classification_name = 'PPPAYMENT'
4741 and pa2.action_type = pcl.action_type
4742 and as1.assignment_id = act.assignment_id
4743 and pa2.effective_date between
4744 as1.effective_start_date and as1.effective_end_date
4745 and as2.assignment_id = act.assignment_id
4746 and pa1.effective_date between
4747 as2.effective_start_date and as2.effective_end_date
4748 and as2.payroll_id + 0 = as1.payroll_id + 0
4749 and pos.period_of_service_id = as1.period_of_service_id
4750 and pop.payroll_action_id = pactid
4751 and pop.chunk_number = chunk
4752 and pos.person_id = pop.person_id
4753 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
4754 and ppp.assignment_action_id = act.assignment_action_id
4755 and opm.org_payment_method_id = ppp.org_payment_method_id
4756 and pa1.effective_date between
4757 opm.effective_start_date and opm.effective_end_date
4758 and opm.payment_type_id +0 = ptype
4759 and (opm.org_payment_method_id = pa1.org_payment_method_id
4760 or pa1.org_payment_method_id is null)
4761 and not exists (
4762 select null
4763 from per_all_assignments_f as3,
4764 pay_assignment_actions ac3
4765 where itpflg = 'N'
4766 and ac3.payroll_action_id = pa2.payroll_action_id
4767 and ac3.action_status not in ('C', 'S')
4768 and as3.assignment_id = ac3.assignment_id
4769 and pa2.effective_date between
4770 as3.effective_start_date and as3.effective_end_date
4771 and as3.person_id = as2.person_id)
4772 and not exists (
4773 select /*+ ORDERED*/
4774 null
4775 from pay_action_interlocks int,
4776 pay_assignment_actions ac2
4777 where int.locked_action_id = act.assignment_action_id
4778 and ac2.assignment_action_id = int.locking_action_id
4779 and ac2.pre_payment_id = ppp.pre_payment_id
4780 and not exists (
4781 select null
4782 from pay_assignment_actions paa_void,
4783 pay_action_interlocks pai_void,
4784 pay_payroll_actions ppa_void
4785 where pai_void.locked_action_id = ac2.assignment_action_id
4786 and pai_void.locking_action_id = paa_void.assignment_action_id
4787 and paa_void.payroll_action_id = ppa_void.payroll_action_id
4788 and ppa_void.action_type = 'D')
4789 )
4790 order by act.assignment_id
4791 for update of as1.assignment_id, pos.period_of_service_id;
4792 --
4793 cursor ppcur
4794 (
4795 pactid number,
4796 stperson number,
4797 endperson number,
4798 itpflg varchar2,
4799 ptype number
4800 ) is
4801 select /*+ ORDERED
4802 INDEX(pa2 PAY_PAYROLL_ACTIONS_N5)
4803 INDEX(pos PER_PERIODS_OF_SERVICE_N3)
4804 INDEX(as1 PER_ASSIGNMENTS_N4)
4805 INDEX(as2 PER_ASSIGNMENTS_F_PK)
4806 INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
4807 index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
4808 USE_NL(pos ppp opm as1 act as2) */
4809 act.assignment_action_id,
4810 act.assignment_id,
4811 act.tax_unit_id,
4812 ppp.pre_payment_id
4813 from pay_payroll_actions pa1,
4814 pay_payroll_actions pa2,
4815 pay_action_classifications pcl,
4816 per_periods_of_service pos,
4817 per_all_assignments_f as1,
4818 pay_assignment_actions act,
4819 per_all_assignments_f as2,
4820 pay_pre_payments ppp,
4821 pay_org_payment_methods_f opm
4822 where pa1.payroll_action_id = pactid
4823 and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
4824 and pa2.effective_date between
4825 pa1.start_date and pa1.effective_date
4826 and act.payroll_action_id = pa2.payroll_action_id
4827 and act.action_status = 'C'
4828 and pcl.classification_name = 'PPPAYMENT'
4829 and pa2.action_type = pcl.action_type
4830 and as1.assignment_id = act.assignment_id
4831 and pa2.effective_date between
4832 as1.effective_start_date and as1.effective_end_date
4833 and as2.assignment_id = act.assignment_id
4834 and pa1.effective_date between
4835 as2.effective_start_date and as2.effective_end_date
4836 and as2.payroll_id + 0 = as1.payroll_id + 0
4837 and pos.period_of_service_id = as1.period_of_service_id
4838 and pos.person_id between stperson and endperson
4839 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
4840 and ppp.assignment_action_id = act.assignment_action_id
4841 and opm.org_payment_method_id = ppp.org_payment_method_id
4842 and pa1.effective_date between
4843 opm.effective_start_date and opm.effective_end_date
4844 and opm.payment_type_id +0 = ptype
4845 and (opm.org_payment_method_id = pa1.org_payment_method_id
4846 or pa1.org_payment_method_id is null)
4847 and not exists (
4848 select null
4849 from per_all_assignments_f as3,
4850 pay_assignment_actions ac3
4851 where itpflg = 'N'
4852 and ac3.payroll_action_id = pa2.payroll_action_id
4853 and ac3.action_status not in ('C', 'S')
4854 and as3.assignment_id = ac3.assignment_id
4855 and pa2.effective_date between
4856 as3.effective_start_date and as3.effective_end_date
4857 and as3.person_id = as2.person_id)
4858 and not exists (
4859 select /*+ ORDERED*/
4860 null
4861 from pay_action_interlocks int,
4862 pay_assignment_actions ac2
4863 where int.locked_action_id = act.assignment_action_id
4864 and ac2.assignment_action_id = int.locking_action_id
4865 and ac2.pre_payment_id = ppp.pre_payment_id
4866 and not exists (
4867 select null
4868 from pay_assignment_actions paa_void,
4869 pay_action_interlocks pai_void,
4870 pay_payroll_actions ppa_void
4871 where pai_void.locked_action_id = ac2.assignment_action_id
4872 and pai_void.locking_action_id = paa_void.assignment_action_id
4873 and paa_void.payroll_action_id = ppa_void.payroll_action_id
4874 and ppa_void.action_type = 'D')
4875 )
4876 order by act.assignment_id
4877 for update of as1.assignment_id, pos.period_of_service_id;
4878 --
4879 cursor ppmpipcur
4880 (
4881 pactid number,
4882 chunk number,
4883 itpflg varchar2,
4884 ptype number
4885 ) is
4886 select /*+ ORDERED
4887 INDEX(pa2 PAY_PAYROLL_ACTIONS_PK)
4888 INDEX(pos PER_PERIODS_OF_SERVICE_N3)
4889 INDEX(as1 PER_ASSIGNMENTS_N4)
4890 INDEX(as2 PER_ASSIGNMENTS_F_PK)
4891 INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
4892 index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
4893 USE_NL(pos pop ppp opm as1 act as2) */
4894 act.assignment_action_id,
4895 act.assignment_id,
4896 act.tax_unit_id,
4897 ppp.pre_payment_id
4898 from pay_payroll_actions pa1,
4899 pay_population_ranges pop,
4900 per_periods_of_service pos,
4901 per_all_assignments_f as1,
4902 pay_assignment_actions act,
4903 pay_payroll_actions pa2,
4904 pay_action_classifications pcl,
4905 per_all_assignments_f as2,
4906 pay_pre_payments ppp,
4907 pay_org_payment_methods_f opm
4908 where pa1.payroll_action_id = pactid
4909 and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
4910 and pa2.effective_date between
4911 pa1.start_date and pa1.effective_date
4912 and act.payroll_action_id = pa2.payroll_action_id
4913 and act.action_status = 'C'
4914 and pcl.classification_name = 'PPPAYMENT'
4915 and pa2.action_type = pcl.action_type
4916 and as1.assignment_id = act.assignment_id
4917 and pa1.effective_date between
4918 as1.effective_start_date and as1.effective_end_date
4919 and as2.assignment_id = act.assignment_id
4920 and pa2.effective_date between
4921 as2.effective_start_date and as2.effective_end_date
4922 and as2.payroll_id + 0 = as1.payroll_id + 0
4923 and pos.period_of_service_id = as1.period_of_service_id
4924 and pop.payroll_action_id = pactid
4925 and pop.chunk_number = chunk
4926 and pos.person_id = pop.person_id
4927 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
4928 and ppp.assignment_action_id = act.assignment_action_id
4929 and opm.org_payment_method_id = ppp.org_payment_method_id
4930 and pa1.effective_date between
4931 opm.effective_start_date and opm.effective_end_date
4932 and opm.payment_type_id +0 = ptype
4933 and (opm.org_payment_method_id = pa1.org_payment_method_id
4934 or pa1.org_payment_method_id is null)
4935 and not exists (
4936 select null
4937 from per_all_assignments_f as3,
4938 pay_assignment_actions ac3
4939 where itpflg = 'N'
4940 and ac3.payroll_action_id = pa2.payroll_action_id
4941 and ac3.action_status not in ('C', 'S')
4942 and as3.assignment_id = ac3.assignment_id
4943 and pa2.effective_date between
4944 as3.effective_start_date and as3.effective_end_date
4945 and as3.person_id = as2.person_id)
4946 and not exists (
4947 select /*+ ORDERED*/
4948 null
4949 from pay_action_interlocks int,
4950 pay_assignment_actions ac2
4951 where int.locked_action_id = act.assignment_action_id
4952 and ac2.assignment_action_id = int.locking_action_id
4953 and ac2.pre_payment_id = ppp.pre_payment_id
4954 and not exists (
4955 select null
4956 from pay_assignment_actions paa_void,
4957 pay_action_interlocks pai_void,
4958 pay_payroll_actions ppa_void
4959 where pai_void.locked_action_id = ac2.assignment_action_id
4960 and pai_void.locking_action_id = paa_void.assignment_action_id
4961 and paa_void.payroll_action_id = ppa_void.payroll_action_id
4962 and ppa_void.action_type = 'D')
4963 )
4964 order by act.assignment_id
4965 for update of as1.assignment_id, pos.period_of_service_id;
4966 --
4967 lockingactid number;
4968 lockedactid number;
4969 assignid number;
4970 prepayid number;
4971 greid number;
4972 --
4973 -- algorithm is quite similar to the other process cases,
4974 -- but we have to take into account assignments and
4975 -- personal payment methods.
4976 begin
4977 if (g_many_procs_in_period = 'Y') then
4978 open ppmpipcur(pactid,chunk,itpflg,ptype);
4979 elsif (use_pop_person = 1) then
4980 open pppopcur(pactid,chunk,itpflg,ptype);
4981 else
4982 open ppcur(pactid,stperson,endperson,itpflg,ptype);
4983 end if;
4984 loop
4985 if (g_many_procs_in_period = 'Y') then
4986 fetch ppmpipcur into lockedactid,assignid,greid,prepayid;
4987 exit when ppmpipcur%notfound;
4988 elsif (use_pop_person = 1) then
4989 fetch pppopcur into lockedactid,assignid,greid,prepayid;
4990 exit when pppopcur%notfound;
4991 else
4992 fetch ppcur into lockedactid,assignid,greid,prepayid;
4993 exit when ppcur%notfound;
4994 end if;
4995 --
4996 -- we need to insert one action for each of the
4997 -- rows that we return from the cursor (i.e. one
4998 -- for each assignment/pre-payment).
4999 select pay_assignment_actions_s.nextval
5000 into lockingactid
5001 from dual;
5002 --
5003 -- insert the action record.
5004 insact(lockingactid,assignid,pactid,rand_chunk,greid,prepayid);
5005 --
5006 -- insert an interlock to this action.
5007 insint(lockingactid,lockedactid);
5008 --
5009 end loop;
5010 if (g_many_procs_in_period = 'Y') then
5011 close ppmpipcur;
5012 elsif (use_pop_person = 1) then
5013 close pppopcur;
5014 else
5015 close ppcur;
5016 end if;
5017 commit;
5018 end procpp;
5019 ----------------------------------- asact ----------------------------------
5020 /*
5021 NAME
5022 asact - insert assignment actions and interlocks
5023 DESCRIPTION
5024 Overall control of the insertion of assignment actions
5025 and interlocks for the non run payroll actions.
5026 NOTES
5027 <none>
5028 */
5029 procedure asact
5030 (
5031 pactid in number, -- payroll_action_id
5032 atype in varchar2, -- action_type.
5033 itpflg in varchar2, -- independent time periods flag.
5034 ptype in number, -- payment_type_id.
5035 lub in varchar2, -- last_updated_by.
5036 lul in varchar2, -- last_update_login.
5037 use_pop_person in number -- use population_ranges person_id column
5038 ) is
5039 QPPREPAY constant varchar2(1) := 'U';
5040 PREPAY constant varchar2(1) := 'P';
5041 COSTING constant varchar2(1) := 'C';
5042 ESTCOSTING constant varchar2(2) := 'EC';
5043 PAYMCOSTING constant varchar2(2) := 'CP';
5044 TRANSGL constant varchar2(1) := 'T';
5045 MAGTAPE constant varchar2(1) := 'M';
5046 CASH constant varchar2(1) := 'A';
5047 CHEQUE constant varchar2(1) := 'H';
5048 ARCHIVE constant varchar2(1) := 'X';
5049 BEE constant varchar2(3) := 'BEE';
5050 PPPAYMENT constant varchar2(2) := 'PP';
5051 --
5052 l_found boolean;
5053 stperson number;
5054 endperson number;
5055 chunk number;
5056 rand_chunk number;
5057 multi_asg_fg pay_all_payrolls_f.multi_assignments_flag%type;
5058 l_use_pop_person number := use_pop_person;
5059 begin
5060 pay_core_utils.get_action_parameter('SET_DATE_EARNED',
5061 g_set_date_earned,
5062 l_found);
5063 if (l_found = FALSE) then
5064 g_set_date_earned := 'N';
5065 end if;
5066 --
5067 -- As quick pay only has a single assignment action process separately
5068 if (atype = QPPREPAY) then
5069 proqpp(pactid,lub,lul);
5070 commit ;
5071 return ;
5072 elsif (atype = PREPAY) then
5073 select nvl(multi_assignments_flag, 'N')
5074 into multi_asg_fg
5075 from pay_all_payrolls_f prl,
5076 pay_payroll_Actions pact
5077 where pact.payroll_action_id = pactid
5078 and prl.payroll_id = pact.payroll_id
5079 and pact.effective_date between prl.effective_start_date
5080 and prl.effective_end_date;
5081 end if;
5082 --
5083 -- find value of MANY_PROCS_IN_PERIOD pay_action_parameter
5084 if cached = FALSE THEN
5085 begin
5086 select parameter_value
5087 into g_many_procs_in_period
5088 from pay_action_parameters
5089 where parameter_name = 'MANY_PROCS_IN_PERIOD';
5090 exception
5091 when others then
5092 g_many_procs_in_period := 'N';
5093 end;
5094 begin
5095 select parameter_value
5096 into g_plsql_proc_insert
5097 from pay_action_parameters
5098 where parameter_name = 'PLSQL_PROC_INSERT';
5099 exception
5100 when others then
5101 g_plsql_proc_insert := 'Y';
5102 end;
5103 cached := TRUE;
5104 end if;
5105 --
5106 -- If a payment process AND PLSQL_PROC_INSERT
5107 -- enforce range_person_id (many_procs_in_period unless
5108 -- was disabled above)
5109 if (atype = MAGTAPE or atype = CHEQUE or
5110 atype = CASH or atype = PPPAYMENT) then
5111 if g_plsql_proc_insert = 'Y' then
5112 if g_many_procs_in_period = 'N' then
5113 l_use_pop_person := 1;
5114 else
5115 g_many_procs_in_period := 'Y';
5116 end if;
5117 end if;
5118 end if;
5119 --
5120 -- MANY_PROCS_IN_PERIOD is now used if RANGE_PERSON_ID is set
5121 -- and MANY_PROCS_IN_PERIOD was not set to N
5122 if (l_use_pop_person = 1 and
5123 g_many_procs_in_period <> 'N') then
5124 g_many_procs_in_period := 'Y';
5125 end if;
5126 --
5127 dbms_lock.allocate_unique(
5128 lockname => 'PAY_PAYROLL_ACTIONS_'||pactid,
5129 lockhandle => g_lckhandle);
5130 --
5131 loop
5132 -- start by processing the range row.
5133 rangerow(pactid,lub,lul,stperson,endperson,chunk,rand_chunk,atype);
5134 -- chunk begin null indicates end of processing.
5135 exit when chunk is null;
5136 --
5137 -- 'lock' the range row grabbed by updating is status.
5138 -- check to see if want to use randomised chnks or sequential
5139 --
5140 update pay_population_ranges rge
5141 set rge.range_status = 'P'
5142 where rge.payroll_action_id = pactid
5143 and rge.chunk_number = chunk;
5144 --
5145 commit;
5146 --
5147 begin
5148 if(atype = PREPAY) then
5149 proc_prepay(pactid,stperson,endperson,chunk,rand_chunk,'PREPAID',
5150 itpflg,multi_asg_fg,l_use_pop_person);
5151 elsif(atype = COSTING) then
5152 proc_costing(pactid,stperson,endperson,chunk,rand_chunk,'COSTED',
5153 itpflg,l_use_pop_person);
5154 elsif(atype = PAYMCOSTING) then
5155 proc_paymcosting(pactid,stperson,endperson,chunk,rand_chunk,'COSTEDPAYM',
5156 itpflg,l_use_pop_person);
5157 elsif(atype = ESTCOSTING) then
5158 proc_estcosts(pactid,stperson,endperson,chunk,rand_chunk,'COSTED',
5159 itpflg,l_use_pop_person);
5160 elsif(atype = TRANSGL) then
5161 proctgl(pactid,stperson,endperson,chunk,rand_chunk,itpflg,l_use_pop_person);
5162 elsif(atype = MAGTAPE) then
5163 procmag(pactid,stperson,endperson,chunk,rand_chunk,itpflg,ptype,
5164 l_use_pop_person);
5165 elsif(atype = CASH) then
5166 proccash(pactid,stperson,endperson,chunk,rand_chunk,itpflg,l_use_pop_person);
5167 elsif(atype = CHEQUE) then
5168 procchq(pactid,stperson,endperson,chunk,rand_chunk,itpflg,ptype,
5169 'CHEQUEWRITER',l_use_pop_person);
5170 elsif(atype = ARCHIVE) then
5171 procarc(pactid,stperson,endperson,chunk);
5172 elsif(atype = BEE) then
5173 procbee(pactid,stperson,endperson,chunk,rand_chunk,l_use_pop_person);
5174 elsif(atype = PPPAYMENT) then
5175 procpp(pactid,stperson,endperson,chunk,rand_chunk,itpflg,ptype,
5176 l_use_pop_person);
5177 elsif(atype = pay_proc_environment_pkg.PYG_AT_PRU) then
5178 procpru(pactid,
5179 stperson,
5180 endperson,
5181 chunk,
5182 rand_chunk,
5183 'P_ROLLEDUP',
5184 itpflg,
5185 l_use_pop_person
5186 );
5187 else
5188 -- unrecognised action type.
5189 hr_utility.set_message(801,'HR_UNRECOGNISED_ACTION_TYPE');
5190 hr_utility.raise_error;
5191 end if;
5192 --
5193 -- we have processed the range, so delete the row.
5194 delete from pay_population_ranges rge
5195 where rge.payroll_action_id = pactid
5196 and rge.chunk_number = chunk;
5197 --
5198 commit;
5199
5200 exception
5201 when others then
5202
5203 rollback;
5204 --
5205 -- set chunk to 'E'rrored
5206 update pay_population_ranges rge
5207 set rge.range_status = 'E'
5208 where rge.payroll_action_id = pactid
5209 and rge.chunk_number = chunk;
5210
5211 update_pact(pactid, 'E', itpflg,sysdate,stperson,endperson);
5212
5213 commit;
5214
5215 raise;
5216 --
5217 end;
5218 --
5219
5220 end loop;
5221 end asact;
5222 -----------------------------------------------------------------------------
5223 -- Name: ins_additional_asg_action
5224 -- Desc: Insert an assignment action to an already existing payroll action.
5225 -----------------------------------------------------------------------------
5226 Procedure ins_additional_asg_action(p_asg_id number default null
5227 ,p_pact_id number
5228 ,p_gre_id number default null
5229 ,p_object_id number default null
5230 ,p_object_type varchar2 default null
5231 )
5232 is
5233 cursor pact_details
5234 is
5235 select ppa.action_status
5236 , ppa.action_type
5237 , rfm.report_name
5238 from pay_payroll_actions ppa
5239 , pay_report_format_mappings_f rfm
5240 where ppa.payroll_action_id = p_pact_id
5241 and ppa.report_type = rfm.report_type(+)
5242 and ppa.report_qualifier = rfm.report_qualifier(+)
5243 and ppa.report_category = rfm.report_category(+);
5244 --
5245 cursor get_existing_person_chunk(p_ppa_id number
5246 ,p_paf_id number)
5247 is
5248 select paa.chunk_number
5249 from pay_assignment_actions paa
5250 , per_all_assignments_f paf
5251 , per_all_people_f ppf
5252 where paa.payroll_action_id = p_ppa_id
5253 and paa.assignment_id = p_paf_id
5254 and paa.assignment_id = paf.assignment_id
5255 and paf.person_id = ppf.person_id
5256 and rownum = 1;
5257 --
5258 -- This cursor returns the chunck number of the chunck with the least number
5259 -- of assignment actions in it. If there is more than one chunk all with the
5260 -- same min number of asg actions, then it will pick the min chunk number.
5261 --
5262 cursor get_min_chunk(p_ppa_id number)
5263 is
5264 select min(chunk_number)
5265 from (select chunk_number, count(assignment_action_id) ct
5266 from pay_assignment_actions
5267 where payroll_action_id = p_ppa_id
5268 group by chunk_number) v1
5269 where v1.ct = (select min(v2.ct) from (select count(assignment_action_id) ct
5270 from pay_assignment_actions
5271 where payroll_action_id = p_ppa_id
5272 group by chunk_number) v2);
5273 --
5274 l_act_status pay_payroll_actions.action_status%type;
5275 l_act_type pay_payroll_actions.action_type%type;
5276 l_rep_name pay_report_format_mappings_f.report_name%type;
5277 l_chunk pay_assignment_actions.chunk_number%type;
5278 l_asg_act_id pay_assignment_actions.assignment_action_id%type;
5279 --
5280 BEGIN
5281 --
5282 -- Determine whether new action can be inserted: 1. if payroll_action is
5283 -- still processing - error. 2. If it is an archive action and there is an
5284 -- associated Oracle Reports report - error. 3. Else insert action
5285 --
5286 open pact_details;
5287 fetch pact_details into l_act_status, l_act_type, l_rep_name;
5288 if pact_details%notfound then
5289 --
5290 close pact_details;
5291 hr_utility.set_message(801, 'PAY_33170_INVALID_PACT_ID');
5292 hr_utility.raise_error;
5293 --
5294 else
5295 close pact_details;
5296 if l_act_status = 'P' then
5297 --
5298 hr_utility.set_message(801, 'PAY_33171_PACT_PROCESSING');
5299 hr_utility.raise_error;
5300 elsif l_act_type = 'X' then
5301 --
5302 if l_rep_name is not null then
5303 --
5304 hr_utility.set_message(801, 'PAY_33172_ARCH_REPORT');
5305 hr_utility.raise_error;
5306 end if;
5307 end if;
5308 end if;
5309 --
5310 -- Determine what chunk number to give the new asg action
5311 --
5312 -- does this person already have a chunk?
5313 --
5314 open get_existing_person_chunk(p_pact_id, p_asg_id);
5315 fetch get_existing_person_chunk into l_chunk;
5316 if get_existing_person_chunk%found then
5317 --
5318 close get_existing_person_chunk;
5319 --
5320 -- insert action using l_chunk
5321 --
5322 else -- new person, so figure out smallest chunk
5323 --
5324 open get_min_chunk(p_pact_id);
5325 fetch get_min_chunk into l_chunk;
5326 if get_min_chunk%notfound then
5327 --
5328 close get_min_chunk;
5329 --
5330 else
5331 close get_min_chunk;
5332 end if;
5333 --
5334 end if;
5335 --
5336 select pay_assignment_actions_s.nextval
5337 into l_asg_act_id
5338 from dual;
5339 --
5340 -- insert the action
5341 --
5342 insert into pay_assignment_actions
5343 (assignment_action_id
5344 ,assignment_id
5345 ,payroll_action_id
5346 ,action_status
5347 ,chunk_number
5348 ,action_sequence
5349 ,pre_payment_id
5350 ,object_version_number
5351 ,tax_unit_id
5352 ,source_action_id
5353 ,object_id
5354 ,object_type
5355 ,start_date
5356 ,end_date
5357 )
5358 values
5359 (l_asg_act_id
5360 ,p_asg_id
5361 ,p_pact_id
5362 ,'U'
5363 ,l_chunk
5364 ,l_asg_act_id
5365 ,''
5366 ,1
5367 ,p_gre_id
5368 ,''
5369 ,p_object_id
5370 ,p_object_type
5371 ,''
5372 ,''
5373 );
5374 --
5375 END ins_additional_asg_action;
5376 -----------------------------------------------------------------------------
5377 end hr_nonrun_asact;
[Home] [Help]