1 package body hr_nonrun_asact as
2 /* $Header: pynonrun.pkb 120.21.12020000.3 2012/07/15 13:31:25 pparate 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 /*+ ORDERED
2236 index(pa2 PAY_PAYROLL_ACTIONS_PK)
2237 index(pos PER_PERIODS_OF_SERVICE_N3)
2238 index(as1 PER_ASSIGNMENTS_F_N4)
2239 index(act PAY_ASSIGNMENT_ACTIONS_N51)
2240 index(as2 PER_ASSIGNMENTS_F_PK)
2241 index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
2242 USE_NL(pos pop act pa2 as1 as2) */
2243 act.assignment_action_id,
2244 act.assignment_id,
2245 act.tax_unit_id,
2246 ppp.pre_payment_id,
2247 pa1.assignment_set_id,
2248 as1.payroll_id
2249 from pay_payroll_actions pa1,
2250 pay_population_ranges pop,
2251 per_periods_of_service pos,
2252 per_all_assignments_f as1,
2253 pay_assignment_actions act,
2254 pay_payroll_actions pa2,
2255 pay_action_classifications pcl,
2256 pay_pre_payments ppp,
2257 per_all_assignments_f as2,
2258 pay_org_payment_methods_f opm
2259 where pa1.payroll_action_id = pactid
2260 and pa2.consolidation_set_id + 0 = pa1.consolidation_set_id
2261 and pa2.effective_date between
2262 pa1.start_date and pa1.effective_date
2263 and act.payroll_action_id = pa2.payroll_action_id
2264 and act.action_status = 'C'
2265 and pcl.classification_name = class
2266 and pa2.action_type = pcl.action_type
2267 and as1.assignment_id = act.assignment_id
2268 and pa1.effective_date between
2269 as1.effective_start_date and as1.effective_end_date
2270 and as2.assignment_id = act.assignment_id
2271 and pa2.effective_date between
2272 as2.effective_start_date and as2.effective_end_date
2273 and as2.payroll_id + 0 = as1.payroll_id + 0
2274 and pos.period_of_service_id = as1.period_of_service_id
2275 and pop.payroll_action_id = pactid
2276 and pop.chunk_number = chunk
2277 and pos.person_id = pop.person_id
2278 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
2279 and ppp.assignment_action_id = act.assignment_action_id
2280 and opm.org_payment_method_id = ppp.org_payment_method_id
2281 and pa1.effective_date between
2282 opm.effective_start_date and opm.effective_end_date
2283 and opm.payment_type_id +0 = ptype
2284 and ppp.organization_id is null
2285 and (opm.org_payment_method_id = pa1.org_payment_method_id
2286 or pa1.org_payment_method_id is null)
2287 and not exists (
2288 select /*+ ORDERED*/
2289 null
2290 from pay_action_interlocks int,
2291 pay_assignment_actions ac2
2292 where int.locked_action_id = act.assignment_action_id
2293 and ac2.assignment_action_id = int.locking_action_id
2294 and ac2.pre_payment_id = ppp.pre_payment_id
2295 and not exists (
2296 select null
2297 from pay_assignment_actions paa_void,
2298 pay_action_interlocks pai_void,
2299 pay_payroll_actions ppa_void
2300 where pai_void.locked_action_id = ac2.assignment_action_id
2301 and pai_void.locking_action_id = paa_void.assignment_action_id
2302 and paa_void.payroll_action_id = ppa_void.payroll_action_id
2303 and ppa_void.action_type = 'D')
2304 )
2305 and not exists (
2306 select /*+ ORDERED*/
2307 null
2308 from per_all_assignments_f as3,
2309 pay_assignment_actions ac3
2310 where itpflg = 'N'
2311 and ac3.payroll_action_id = pa2.payroll_action_id
2312 and ac3.action_status not in ('C', 'S')
2313 and as3.assignment_id = ac3.assignment_id
2314 and pa2.effective_date between
2315 as3.effective_start_date and as3.effective_end_date
2316 and as3.person_id = as2.person_id)
2317 order by act.assignment_id
2318 for update of as1.assignment_id, pos.period_of_service_id;
2319 --
2320 cursor chkasg
2321 (
2322 pasgsetid number,
2323 ppayrollid number,
2324 pasgid number,
2325 plockedid number
2326 ) is
2327 SELECT 1
2328 FROM hr_assignment_sets aset
2329 WHERE aset.assignment_set_id = pasgsetid
2330 and nvl(aset.payroll_id,ppayrollid) = ppayrollid
2331 and (not exists
2332 (select 1
2333 from hr_assignment_set_amendments hasa
2334 where hasa.assignment_set_id = aset.assignment_set_id
2335 and hasa.include_or_exclude = 'I')
2336 or 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 = 'I'))
2342 and not exists
2343 (select 1
2344 from hr_assignment_set_amendments hasa
2345 where hasa.assignment_set_id = aset.assignment_set_id
2346 and hasa.assignment_id = pasgid
2347 and hasa.include_or_exclude = 'E')
2348 -- Ensure there exists a voided check for this payment.
2349 and exists
2350 (select 1
2351 from pay_action_interlocks lck1,
2352 pay_assignment_actions chk_paa,
2353 pay_payroll_actions chk_ppa,
2354 pay_action_interlocks lck2,
2355 pay_assignment_actions vd_paa,
2356 pay_payroll_actions vd_ppa
2357 where lck1.locked_action_id = plockedid
2358 and lck1.locking_action_id = chk_paa.assignment_action_id
2359 and chk_paa.payroll_action_id = chk_ppa.payroll_action_id
2360 and chk_ppa.action_type = 'H'
2361 and lck2.locked_action_id = chk_paa.assignment_action_id
2362 and lck2.locking_action_id = vd_paa.assignment_action_id
2363 and vd_paa.payroll_action_id = vd_ppa.payroll_action_id
2364 and vd_ppa.action_type = 'D');
2365 --
2366 lockingactid number;
2367 lockedactid number;
2368 assignid number;
2369 prepayid number;
2370 greid number;
2371 --
2372 asgsetid number;
2373 payrollid number;
2374 inasgset boolean;
2375 dummy number;
2376 --
2377 -- algorithm is quite similar to the other process cases,
2378 -- but we have to take into account assignments and
2379 -- personal payment methods.
2380 begin
2381 if (g_many_procs_in_period = 'Y') then
2382 open paymentmpipcur(pactid,chunk,itpflg,ptype,class);
2383 elsif (use_pop_person = 1) then
2384 open paymentpopcur(pactid,chunk,itpflg,ptype,class);
2385 else
2386 open paymentcur(pactid,stperson,endperson,itpflg,ptype,class);
2387 end if;
2388 loop
2389 if (g_many_procs_in_period = 'Y') then
2390 fetch paymentmpipcur into lockedactid,assignid,greid,prepayid,asgsetid,payrollid;
2391 exit when paymentmpipcur%notfound;
2392 elsif (use_pop_person = 1) then
2393 fetch paymentpopcur into lockedactid,assignid,greid,prepayid,asgsetid,payrollid;
2394 exit when paymentpopcur%notfound;
2395 else
2396 fetch paymentcur into lockedactid,assignid,greid,prepayid,asgsetid,payrollid;
2397 exit when paymentcur%notfound;
2398 end if;
2399 --
2400 inasgset := TRUE;
2401 --
2402 if asgsetid is not null then
2403 open chkasg(asgsetid,payrollid,assignid,lockedactid);
2404 fetch chkasg into dummy;
2405 --
2406 if chkasg%notfound then
2407 inasgset := FALSE;
2408 end if;
2409 --
2410 close chkasg;
2411 end if;
2412 --
2413 -- Only create the assignment action if the assignment is part
2414 -- of the assignment set.
2415 if inasgset then
2416 -- we need to insert one action for each of the
2417 -- rows that we return from the cursor (i.e. one
2418 -- for each assignment/pre-payment).
2419 select pay_assignment_actions_s.nextval
2420 into lockingactid
2421 from dual;
2422 --
2423 -- insert the action record.
2424 insact(lockingactid,assignid,pactid,rand_chunk,greid,prepayid);
2425 --
2426 -- insert an interlock to this action.
2427 insint(lockingactid,lockedactid);
2428 end if;
2429 --
2430 end loop;
2431 if (g_many_procs_in_period = 'Y') then
2432 close paymentmpipcur;
2433 elsif (use_pop_person = 1) then
2434 close paymentpopcur;
2435 else
2436 close paymentcur;
2437 end if;
2438 --
2439 -- Now populate the org payments
2440 procorgpyt
2441 (
2442 pactid => pactid,
2443 chunk => chunk,
2444 rand_chunk => rand_chunk,
2445 ptype => ptype,
2446 class => class
2447 );
2448 --
2449 commit;
2450 end procchq;
2451 --
2452 ---------------------------------- procmag ---------------------------------
2453 /*
2454 NAME
2455 procmag - process a single chunk for magnetic transfer process.
2456 DESCRIPTION
2457 This function takes a range as defined by the starting and
2458 ending person_id and inserts a chunk of assignment actions
2459 plus their associated interlock rows. This function for the
2460 magnetic transfer action only.
2461 NOTES
2462 <none>
2463 */
2464 procedure procmag
2465 (
2466 pactid in number, -- payroll_action_id.
2467 stperson in number, -- starting person_id of range.
2468 endperson in number, -- ending person_id of range.
2469 chunk in number, -- current chunk_number.
2470 rand_chunk in number, -- current chunk_number.
2471 itpflg in varchar2, -- legislation type.
2472 ptype in number, -- payment_type_id.
2473 use_pop_person in number -- use population_ranges person_id column
2474 ) is
2475 cursor magpopcur
2476 (
2477 pactid number,
2478 chunk number,
2479 itpflg varchar2,
2480 ptype number
2481 ) is
2482 select /*+ ORDERED
2483 INDEX(pa2 PAY_PAYROLL_ACTIONS_N5)
2484 INDEX(pos PER_PERIODS_OF_SERVICE_N3)
2485 INDEX(as1 PER_ASSIGNMENTS_N4)
2486 INDEX(as2 PER_ASSIGNMENTS_F_PK)
2487 INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
2488 index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
2489 USE_NL(pop pos ppp opm as1 act as2) */
2490 act.assignment_action_id,
2491 act.assignment_id,
2492 act.tax_unit_id,
2493 ppp.pre_payment_id
2494 from pay_payroll_actions pa1,
2495 pay_payroll_actions pa2,
2496 pay_action_classifications pcl,
2497 pay_population_ranges pop,
2498 per_periods_of_service pos,
2499 per_all_assignments_f as1,
2500 pay_assignment_actions act,
2501 per_all_assignments_f as2,
2502 pay_pre_payments ppp,
2503 pay_org_payment_methods_f opm
2504 where pa1.payroll_action_id = pactid
2505 and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
2506 and pa2.effective_date between
2507 pa1.start_date and pa1.effective_date
2508 and act.payroll_action_id = pa2.payroll_action_id
2509 and act.action_status = 'C'
2510 and pcl.classification_name = 'MAGTAPE'
2511 and pa2.action_type = pcl.action_type
2512 and as1.assignment_id = act.assignment_id
2513 and pa2.effective_date between
2514 as1.effective_start_date and as1.effective_end_date
2515 and as2.assignment_id = act.assignment_id
2516 and pa1.effective_date between
2517 as2.effective_start_date and as2.effective_end_date
2518 and as2.payroll_id + 0 = as1.payroll_id + 0
2519 and pos.period_of_service_id = as1.period_of_service_id
2520 and pop.payroll_action_id = pactid
2521 and pop.chunk_number = chunk
2522 and pos.person_id = pop.person_id
2523 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
2524 and ppp.assignment_action_id = act.assignment_action_id
2525 and opm.org_payment_method_id = ppp.org_payment_method_id
2526 and pa1.effective_date between
2527 opm.effective_start_date and opm.effective_end_date
2528 and opm.payment_type_id +0 = ptype
2529 and (opm.org_payment_method_id = pa1.org_payment_method_id
2530 or pa1.org_payment_method_id is null)
2531 and not exists (
2532 select null
2533 from per_all_assignments_f as3,
2534 pay_assignment_actions ac3
2535 where itpflg = 'N'
2536 and ac3.payroll_action_id = pa2.payroll_action_id
2537 and ac3.action_status not in ('C', 'S')
2538 and as3.assignment_id = ac3.assignment_id
2539 and pa2.effective_date between
2540 as3.effective_start_date and as3.effective_end_date
2541 and as3.person_id = as2.person_id)
2542 and not exists (
2543 select /*+ ORDERED*/
2544 null
2545 from pay_action_interlocks int,
2546 pay_assignment_actions ac2
2547 where int.locked_action_id = act.assignment_action_id
2548 and ac2.assignment_action_id = int.locking_action_id
2549 and ac2.pre_payment_id = ppp.pre_payment_id
2550 and not exists (
2551 select null
2552 from pay_assignment_actions paa_void,
2553 pay_action_interlocks pai_void,
2554 pay_payroll_actions ppa_void
2555 where pai_void.locked_action_id = ac2.assignment_action_id
2556 and pai_void.locking_action_id = paa_void.assignment_action_id
2557 and paa_void.payroll_action_id = ppa_void.payroll_action_id
2558 and ppa_void.action_type = 'D')
2559 )
2560 order by act.assignment_id
2561 for update of as1.assignment_id, pos.period_of_service_id;
2562 --
2563 cursor magcur
2564 (
2565 pactid number,
2566 stperson number,
2567 endperson number,
2568 itpflg varchar2,
2569 ptype number
2570 ) is
2571 select /*+ ORDERED
2572 INDEX(pa2 PAY_PAYROLL_ACTIONS_N5)
2573 INDEX(pos PER_PERIODS_OF_SERVICE_N3)
2574 INDEX(as1 PER_ASSIGNMENTS_N4)
2575 INDEX(as2 PER_ASSIGNMENTS_F_PK)
2576 INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
2577 index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
2578 USE_NL(pos ppp opm as1 act as2) */
2579 act.assignment_action_id,
2580 act.assignment_id,
2581 act.tax_unit_id,
2582 ppp.pre_payment_id
2583 from pay_payroll_actions pa1,
2584 pay_payroll_actions pa2,
2585 pay_action_classifications pcl,
2586 per_periods_of_service pos,
2587 per_all_assignments_f as1,
2588 pay_assignment_actions act,
2589 per_all_assignments_f as2,
2590 pay_pre_payments ppp,
2591 pay_org_payment_methods_f opm
2592 where pa1.payroll_action_id = pactid
2593 and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
2594 and pa2.effective_date between
2595 pa1.start_date and pa1.effective_date
2596 and act.payroll_action_id = pa2.payroll_action_id
2597 and act.action_status = 'C'
2598 and pcl.classification_name = 'MAGTAPE'
2599 and pa2.action_type = pcl.action_type
2600 and as1.assignment_id = act.assignment_id
2601 and pa2.effective_date between
2602 as1.effective_start_date and as1.effective_end_date
2603 and as2.assignment_id = act.assignment_id
2604 and pa1.effective_date between
2605 as2.effective_start_date and as2.effective_end_date
2606 and as2.payroll_id + 0 = as1.payroll_id + 0
2607 and pos.period_of_service_id = as1.period_of_service_id
2608 and pos.person_id between stperson and endperson
2609 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
2610 and ppp.assignment_action_id = act.assignment_action_id
2611 and opm.org_payment_method_id = ppp.org_payment_method_id
2612 and pa1.effective_date between
2613 opm.effective_start_date and opm.effective_end_date
2614 and opm.payment_type_id +0 = ptype
2615 and (opm.org_payment_method_id = pa1.org_payment_method_id
2616 or pa1.org_payment_method_id is null)
2617 and not exists (
2618 select null
2619 from per_all_assignments_f as3,
2620 pay_assignment_actions ac3
2621 where itpflg = 'N'
2622 and ac3.payroll_action_id = pa2.payroll_action_id
2623 and ac3.action_status not in ('C', 'S')
2624 and as3.assignment_id = ac3.assignment_id
2625 and pa2.effective_date between
2626 as3.effective_start_date and as3.effective_end_date
2627 and as3.person_id = as2.person_id)
2628 and not exists (
2629 select /*+ ORDERED*/
2630 null
2631 from pay_action_interlocks int,
2632 pay_assignment_actions ac2
2633 where int.locked_action_id = act.assignment_action_id
2634 and ac2.assignment_action_id = int.locking_action_id
2635 and ac2.pre_payment_id = ppp.pre_payment_id
2636 and not exists (
2637 select null
2638 from pay_assignment_actions paa_void,
2639 pay_action_interlocks pai_void,
2640 pay_payroll_actions ppa_void
2641 where pai_void.locked_action_id = ac2.assignment_action_id
2642 and pai_void.locking_action_id = paa_void.assignment_action_id
2643 and paa_void.payroll_action_id = ppa_void.payroll_action_id
2644 and ppa_void.action_type = 'D')
2645 )
2646 order by act.assignment_id
2647 for update of as1.assignment_id, pos.period_of_service_id;
2648 --
2649 cursor magmpipcur
2650 (
2651 pactid number,
2652 chunk number,
2653 itpflg varchar2,
2654 ptype number
2655 ) is
2656 select /*+ ORDERED
2657 INDEX(pa2 PAY_PAYROLL_ACTIONS_PK)
2658 INDEX(pos PER_PERIODS_OF_SERVICE_N3)
2659 INDEX(as1 PER_ASSIGNMENTS_N4)
2660 INDEX(as2 PER_ASSIGNMENTS_F_PK)
2661 INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
2662 index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
2663 USE_NL(pos pop ppp opm as1 act as2) */
2664 act.assignment_action_id,
2665 act.assignment_id,
2666 act.tax_unit_id,
2667 ppp.pre_payment_id
2668 from pay_payroll_actions pa1,
2669 pay_population_ranges pop,
2670 per_periods_of_service pos,
2671 per_all_assignments_f as1,
2672 pay_assignment_actions act,
2673 pay_payroll_actions pa2,
2674 pay_action_classifications pcl,
2675 per_all_assignments_f as2,
2676 pay_pre_payments ppp,
2677 pay_org_payment_methods_f opm
2678 where pa1.payroll_action_id = pactid
2679 and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
2680 and pa2.effective_date between
2681 pa1.start_date and pa1.effective_date
2682 and act.payroll_action_id = pa2.payroll_action_id
2683 and act.action_status = 'C'
2684 and pcl.classification_name = 'MAGTAPE'
2685 and pa2.action_type = pcl.action_type
2686 and as1.assignment_id = act.assignment_id
2687 and pa1.effective_date between
2688 as1.effective_start_date and as1.effective_end_date
2689 and as2.assignment_id = act.assignment_id
2690 and pa2.effective_date between
2691 as2.effective_start_date and as2.effective_end_date
2692 and as2.payroll_id + 0 = as1.payroll_id + 0
2693 and pos.period_of_service_id = as1.period_of_service_id
2694 and pop.payroll_action_id = pactid
2695 and pop.chunk_number = chunk
2696 and pos.person_id = pop.person_id
2697 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
2698 and ppp.assignment_action_id = act.assignment_action_id
2699 and opm.org_payment_method_id = ppp.org_payment_method_id
2700 and pa1.effective_date between
2701 opm.effective_start_date and opm.effective_end_date
2702 and opm.payment_type_id +0 = ptype
2703 and (opm.org_payment_method_id = pa1.org_payment_method_id
2704 or pa1.org_payment_method_id is null)
2705 and not exists (
2706 select null
2707 from per_all_assignments_f as3,
2708 pay_assignment_actions ac3
2709 where itpflg = 'N'
2710 and ac3.payroll_action_id = pa2.payroll_action_id
2711 and ac3.action_status not in ('C', 'S')
2712 and as3.assignment_id = ac3.assignment_id
2713 and pa2.effective_date between
2714 as3.effective_start_date and as3.effective_end_date
2715 and as3.person_id = as2.person_id)
2716 and not exists (
2717 select /*+ ORDERED*/
2718 null
2719 from pay_action_interlocks int,
2720 pay_assignment_actions ac2
2721 where int.locked_action_id = act.assignment_action_id
2722 and ac2.assignment_action_id = int.locking_action_id
2723 and ac2.pre_payment_id = ppp.pre_payment_id
2724 and not exists (
2725 select null
2726 from pay_assignment_actions paa_void,
2727 pay_action_interlocks pai_void,
2728 pay_payroll_actions ppa_void
2729 where pai_void.locked_action_id = ac2.assignment_action_id
2730 and pai_void.locking_action_id = paa_void.assignment_action_id
2731 and paa_void.payroll_action_id = ppa_void.payroll_action_id
2732 and ppa_void.action_type = 'D')
2733 )
2734 order by act.assignment_id
2735 for update of as1.assignment_id, pos.period_of_service_id;
2736 --
2737 lockingactid number;
2738 lockedactid number;
2739 assignid number;
2740 prepayid number;
2741 greid number;
2742 --
2743 -- algorithm is quite similar to the other process cases,
2744 -- but we have to take into account assignments and
2745 -- personal payment methods.
2746 begin
2747 if (g_many_procs_in_period = 'Y') then
2748 open magmpipcur(pactid,chunk,itpflg,ptype);
2749 elsif (use_pop_person = 1) then
2750 open magpopcur(pactid,chunk,itpflg,ptype);
2751 else
2752 open magcur(pactid,stperson,endperson,itpflg,ptype);
2753 end if;
2754 loop
2755 if (g_many_procs_in_period = 'Y') then
2756 fetch magmpipcur into lockedactid,assignid,greid,prepayid;
2757 exit when magmpipcur%notfound;
2758 elsif (use_pop_person = 1) then
2759 fetch magpopcur into lockedactid,assignid,greid,prepayid;
2760 exit when magpopcur%notfound;
2761 else
2762 fetch magcur into lockedactid,assignid,greid,prepayid;
2763 exit when magcur%notfound;
2764 end if;
2765 --
2766 -- we need to insert one action for each of the
2767 -- rows that we return from the cursor (i.e. one
2768 -- for each assignment/pre-payment).
2769 select pay_assignment_actions_s.nextval
2770 into lockingactid
2771 from dual;
2772 --
2773 -- insert the action record.
2774 insact(lockingactid,assignid,pactid,rand_chunk,greid,prepayid);
2775 --
2776 -- insert an interlock to this action.
2777 insint(lockingactid,lockedactid);
2778 --
2779 end loop;
2780 if (g_many_procs_in_period = 'Y') then
2781 close magmpipcur;
2782 elsif (use_pop_person = 1) then
2783 close magpopcur;
2784 else
2785 close magcur;
2786 end if;
2787 --
2788 -- Now populate the org payments
2789 procorgpyt
2790 (
2791 pactid => pactid,
2792 chunk => chunk,
2793 rand_chunk => rand_chunk,
2794 ptype => ptype,
2795 class => 'MAGTAPE'
2796 );
2797 commit;
2798 end procmag;
2799 --
2800 -------------------------------- proc_prepay -------------------------------
2801 /*
2802 NAME
2803 proc_prepay - insert actions for pre-payment action type.
2804 DESCRIPTION
2805 For the range defined by the starting and ending person_id,
2806 inserts a chunk of assignment actions and associated interlocks.
2807 NOTES
2808 <none>
2809 */
2810 procedure proc_prepay
2811 (
2812 pactid in number,
2813 stperson in number,
2814 endperson in number,
2815 chunk in number,
2816 rand_chunk in number,
2817 class in varchar2,
2818 itpflg in varchar2,
2819 mult_asg_flag in varchar2 default 'N',
2820 use_pop_person in number
2821 ) is
2822 --
2823 cursor prepaypopcur
2824 (
2825 pactid number,
2826 chunk number,
2827 class varchar2,
2828 itpflg varchar2
2829 ) is
2830 select /*+ ORDERED
2831 index(pa2 PAY_PAYROLL_ACTIONS_N5)
2832 index(as1 PER_ASSIGNMENTS_F_N4)
2833 index(as2 PER_ASSIGNMENTS_F_PK)
2834 USE_NL(pop pos as1) */
2835 act.assignment_action_id,
2836 act.assignment_id,
2837 act.tax_unit_id,
2838 as1.person_id,
2839 as1.effective_start_date,
2840 as1.primary_flag
2841 from pay_payroll_actions pa1,
2842 pay_payroll_actions pa2,
2843 pay_action_classifications pcl,
2844 pay_population_ranges pop,
2845 per_periods_of_service pos,
2846 per_all_assignments_f as1,
2847 pay_assignment_actions act,
2848 per_all_assignments_f as2
2849 where pa1.payroll_action_id = pactid
2850 and pa2.payroll_id = pa1.payroll_id
2851 and pa2.effective_date between
2852 pa1.start_date and pa1.effective_date
2853 and act.payroll_action_id = pa2.payroll_action_id
2854 and act.action_status in ('C','S')
2855 and pcl.classification_name = class
2856 and pa2.consolidation_set_id = pa1.consolidation_set_id
2857 and pa2.action_type = pcl.action_type
2858 and nvl(pa2.future_process_mode, 'Y') = 'Y'
2859 and as1.assignment_id = act.assignment_id
2860 and pa2.effective_date between
2861 as1.effective_start_date and as1.effective_end_date
2862 and as2.assignment_id = act.assignment_id
2863 and pa1.effective_date between
2864 as2.effective_start_date and as2.effective_end_date
2865 and as2.payroll_id = as1.payroll_id
2866 and pos.period_of_service_id = as1.period_of_service_id
2867 and pop.payroll_action_id = pactid
2868 and pop.chunk_number = chunk
2869 and pos.person_id = pop.person_id
2870 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
2871 and not exists (
2872 select null
2873 from pay_assignment_actions ac2,
2874 pay_payroll_actions pa3,
2875 pay_action_interlocks int
2876 where int.locked_action_id = act.assignment_action_id
2877 and ac2.assignment_action_id = int.locking_action_id
2878 and pa3.payroll_action_id = ac2.payroll_action_id
2879 and pa3.action_type in ('P', 'U'))
2880 and not exists (
2881 select /*+ ORDERED*/
2882 null
2883 from per_all_assignments_f as3,
2884 pay_assignment_actions ac3
2885 where itpflg = 'N'
2886 and ac3.payroll_action_id = pa2.payroll_action_id
2887 and ac3.action_status not in ( 'C', 'S')
2888 and as3.assignment_id = ac3.assignment_id
2889 and pa2.effective_date between
2890 as3.effective_start_date and as3.effective_end_date
2891 and as3.person_id = as2.person_id)
2892 order by as1.person_id,as1.primary_flag desc ,as1.effective_start_date,act.assignment_id
2893 for update of as1.assignment_id, pos.period_of_service_id;
2894 --
2895 cursor prepaycur
2896 (
2897 pactid number,
2898 stperson number,
2899 endperson number,
2900 class varchar2,
2901 itpflg varchar2
2902 ) is
2903 select /*+ ORDERED
2904 index(pa2 PAY_PAYROLL_ACTIONS_N5)
2905 index(as1 PER_ASSIGNMENTS_F_N4)
2906 index(as2 PER_ASSIGNMENTS_F_PK)
2907 USE_NL(pos as1) */
2908 act.assignment_action_id,
2909 act.assignment_id,
2910 act.tax_unit_id,
2911 as1.person_id,
2912 as1.effective_start_date,
2913 as1.primary_flag
2914 from pay_payroll_actions pa1,
2915 pay_payroll_actions pa2,
2916 pay_action_classifications pcl,
2917 per_periods_of_service pos,
2918 per_all_assignments_f as1,
2919 pay_assignment_actions act,
2920 per_all_assignments_f as2
2921 where pa1.payroll_action_id = pactid
2922 and pa2.payroll_id = pa1.payroll_id
2923 and pa2.effective_date between
2924 pa1.start_date and pa1.effective_date
2925 and act.payroll_action_id = pa2.payroll_action_id
2926 and act.action_status in ('C','S')
2927 and pcl.classification_name = class
2928 and pa2.consolidation_set_id = pa1.consolidation_set_id
2929 and pa2.action_type = pcl.action_type
2930 and nvl(pa2.future_process_mode, 'Y') = 'Y'
2931 and as1.assignment_id = act.assignment_id
2932 and pa2.effective_date between
2933 as1.effective_start_date and as1.effective_end_date
2934 and as2.assignment_id = act.assignment_id
2935 and pa1.effective_date between
2936 as2.effective_start_date and as2.effective_end_date
2937 and as2.payroll_id = as1.payroll_id
2938 and pos.period_of_service_id = as1.period_of_service_id
2939 and pos.person_id between stperson and endperson
2940 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
2941 and not exists (
2942 select null
2943 from pay_assignment_actions ac2,
2944 pay_payroll_actions pa3,
2945 pay_action_interlocks int
2946 where int.locked_action_id = act.assignment_action_id
2947 and ac2.assignment_action_id = int.locking_action_id
2948 and pa3.payroll_action_id = ac2.payroll_action_id
2949 and pa3.action_type in ('P', 'U'))
2950 and not exists (
2951 select /*+ ORDERED*/
2952 null
2953 from per_all_assignments_f as3,
2954 pay_assignment_actions ac3
2955 where itpflg = 'N'
2956 and ac3.payroll_action_id = pa2.payroll_action_id
2957 and ac3.action_status not in ( 'C', 'S')
2958 and as3.assignment_id = ac3.assignment_id
2959 and pa2.effective_date between
2960 as3.effective_start_date and as3.effective_end_date
2961 and as3.person_id = as2.person_id)
2962 order by as1.person_id,as1.primary_flag desc ,as1.effective_start_date,act.assignment_id
2963 for update of as1.assignment_id, pos.period_of_service_id;
2964 --
2965 cursor prepaympipcur
2966 (
2967 pactid number,
2968 chunk number,
2969 class varchar2,
2970 itpflg varchar2
2971 ) is
2972 select /*+ ORDERED
2973 index(pa2 PAY_PAYROLL_ACTIONS_PK)
2974 index(pos PER_PERIODS_OF_SERVICE_N3)
2975 index(act PAY_ASSIGNMENT_ACTIONS_N51)
2976 index(as1 PER_ASSIGNMENTS_F_N4)
2977 index(as2 PER_ASSIGNMENTS_F_PK)
2978 USE_NL(pos pop act as1 as2 pa2) */
2979 act.assignment_action_id,
2980 act.assignment_id,
2981 act.tax_unit_id,
2982 as1.person_id,
2983 as1.effective_start_date,
2984 as1.primary_flag
2985 from pay_payroll_actions pa1,
2986 pay_population_ranges pop,
2987 per_periods_of_service pos,
2988 per_all_assignments_f as1,
2989 pay_assignment_actions act,
2990 pay_payroll_actions pa2,
2991 pay_action_classifications pcl,
2992 per_all_assignments_f as2
2993 where pa1.payroll_action_id = pactid
2994 and pa2.payroll_id = pa1.payroll_id
2995 and pa2.effective_date between
2996 pa1.start_date and pa1.effective_date
2997 and act.payroll_action_id = pa2.payroll_action_id
2998 and act.action_status in ('C','S')
2999 and pcl.classification_name = class
3000 and pa2.consolidation_set_id = pa1.consolidation_set_id
3001 and pa2.action_type = pcl.action_type
3002 and nvl(pa2.future_process_mode, 'Y') = 'Y'
3003 and as1.assignment_id = act.assignment_id
3004 and pa1.effective_date between
3005 as1.effective_start_date and as1.effective_end_date
3006 and as2.assignment_id = act.assignment_id
3007 and pa2.effective_date between
3008 as2.effective_start_date and as2.effective_end_date
3009 and as2.payroll_id = as1.payroll_id
3010 and pos.period_of_service_id = as1.period_of_service_id
3011 and pop.payroll_action_id = pactid
3012 and pop.chunk_number = chunk
3013 and pos.person_id = pop.person_id
3014 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3015 and not exists (
3016 select null
3017 from pay_assignment_actions ac2,
3018 pay_payroll_actions pa3,
3019 pay_action_interlocks int
3020 where int.locked_action_id = act.assignment_action_id
3021 and ac2.assignment_action_id = int.locking_action_id
3022 and pa3.payroll_action_id = ac2.payroll_action_id
3023 and pa3.action_type in ('P', 'U'))
3024 and not exists (
3025 select /*+ ORDERED*/
3026 null
3027 from per_all_assignments_f as3,
3028 pay_assignment_actions ac3
3029 where itpflg = 'N'
3030 and ac3.payroll_action_id = pa2.payroll_action_id
3031 and ac3.action_status not in ( 'C', 'S')
3032 and as3.assignment_id = ac3.assignment_id
3033 and pa2.effective_date between
3034 as3.effective_start_date and as3.effective_end_date
3035 and as3.person_id = as2.person_id)
3036 order by as1.person_id,as1.primary_flag desc ,as1.effective_start_date,act.assignment_id
3037 for update of as1.assignment_id, pos.period_of_service_id;
3038 --
3039 lockingactid number;
3040 lockedactid number;
3041 assignid number;
3042 prev_assignid number;
3043 greid number;
3044 --
3045 person_id number;
3046 primary_flag varchar2(30);
3047 asg_start_date date;
3048 prev_person_id number;
3049 begin
3050 prev_assignid := null;
3051 prev_person_id := null;
3052 if (g_many_procs_in_period = 'Y') then
3053 open prepaympipcur(pactid,chunk,class,itpflg);
3054 elsif (use_pop_person = 1) then
3055 open prepaypopcur(pactid,chunk,class,itpflg);
3056 else
3057 open prepaycur(pactid,stperson,endperson,class,itpflg);
3058 end if;
3059 loop
3060 if (g_many_procs_in_period = 'Y') then
3061 fetch prepaympipcur into lockedactid,assignid,greid,person_id,asg_start_date,primary_flag;
3062 exit when prepaympipcur%notfound;
3063 elsif (use_pop_person = 1) then
3064 fetch prepaypopcur into lockedactid,assignid,greid,person_id,asg_start_date,primary_flag;
3065 exit when prepaypopcur%notfound;
3066 else
3067 fetch prepaycur into lockedactid,assignid,greid,person_id,asg_start_date,primary_flag;
3068 exit when prepaycur%notfound;
3069 end if;
3070 --
3071 if (mult_asg_flag = 'Y')
3072 then
3073 -- insert master actions
3074 if (prev_person_id is null or prev_person_id <> person_id) then
3075 select pay_assignment_actions_s.nextval
3076 into lockingactid
3077 from dual;
3078
3079 -- insert into pay_assignment_actions.
3080 insact(lockingactid,assignid,pactid,rand_chunk,greid);
3081
3082 end if;
3083 -- insert interlocks
3084 insint(lockingactid,lockedactid);
3085 prev_assignid := assignid;
3086 prev_person_id := person_id;
3087
3088 else
3089 /* process the insert of assignment actions */
3090 /* logic prevents more than one action per assignment */
3091 if(prev_assignid is null OR prev_assignid <> assignid) then
3092 -- get a value for the action id that is locking.
3093 select pay_assignment_actions_s.nextval
3094 into lockingactid
3095 from dual;
3096 --
3097 -- insert into pay_assignment_actions.
3098 insact(lockingactid,assignid,pactid,rand_chunk,greid);
3099 end if;
3100 --
3101 -- insert into interlocks table.
3102 insint(lockingactid,lockedactid);
3103 prev_assignid := assignid;
3104 end if;
3105 --
3106 end loop;
3107 if (g_many_procs_in_period = 'Y') then
3108 close prepaympipcur;
3109 elsif (use_pop_person = 1) then
3110 close prepaypopcur;
3111 else
3112 close prepaycur;
3113 end if;
3114 commit;
3115 end proc_prepay;
3116 --
3117 ------------------------------- proc_costing -------------------------------
3118 /*
3119 NAME
3120 proc_costing - insert actions for non Costing action type.
3121 DESCRIPTION
3122 For the range defined by the starting and ending person_id,
3123 inserts a chunk of assignment actions and associated interlocks.
3124 NOTES
3125 <none>
3126 */
3127 procedure proc_costing
3128 (
3129 pactid in number,
3130 stperson in number,
3131 endperson in number,
3132 chunk in number,
3133 rand_chunk in number,
3134 class in varchar2,
3135 itpflg in varchar2,
3136 use_pop_person in number
3137 ) is
3138 --
3139 cursor costingpopcur
3140 (
3141 pactid number,
3142 chunk number,
3143 class varchar2,
3144 itpflg varchar2
3145 ) is
3146 select /*+ ORDERED
3147 index(pa2 PAY_PAYROLL_ACTIONS_N5)
3148 index(as1 PER_ASSIGNMENTS_F_N4)
3149 index(as2 PER_ASSIGNMENTS_F_PK)
3150 index(act PAY_ASSIGNMENT_ACTIONS_N51)
3151 USE_NL(pos pop as1) */ -- Bug 14184691 Added hint index(act PAY_ASSIGNMENT_ACTIONS_N51)
3152 act.assignment_action_id,
3153 act.assignment_id,
3154 act.tax_unit_id
3155 from pay_payroll_actions pa1,
3156 pay_payroll_actions pa2,
3157 pay_action_classifications pcl,
3158 pay_population_ranges pop,
3159 per_periods_of_service pos,
3160 per_all_assignments_f as1,
3161 pay_assignment_actions act,
3162 per_all_assignments_f as2
3163 where pa1.payroll_action_id = pactid
3164 and pa2.consolidation_set_id = pa1.consolidation_set_id
3165 and pa2.effective_date between
3166 pa1.start_date and pa1.effective_date
3167 and act.payroll_action_id = pa2.payroll_action_id
3168 and act.action_status in ('C','S')
3169 and pcl.classification_name = class
3170 and pa2.action_type = pcl.action_type
3171 and as1.assignment_id = act.assignment_id
3172 and pa2.effective_date between
3173 as1.effective_start_date and as1.effective_end_date
3174 and as2.assignment_id = act.assignment_id
3175 and pa1.effective_date between
3176 as2.effective_start_date and as2.effective_end_date
3177 and pop.payroll_action_id = pactid
3178 and pop.chunk_number = chunk
3179 and pos.person_id = pop.person_id
3180 and pos.period_of_service_id = as1.period_of_service_id
3181 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3182 and not exists (
3183 select null
3184 from pay_assignment_actions ac2,
3185 pay_payroll_actions pa3,
3186 pay_action_interlocks int
3187 where int.locked_action_id = act.assignment_action_id
3188 and ac2.assignment_action_id = int.locking_action_id
3189 and pa3.payroll_action_id = ac2.payroll_action_id
3190 and pa3.action_type in ('C', 'S'))
3191 and not exists (
3192 select /*+ ORDERED*/
3193 null
3194 from per_all_assignments_f as3,
3195 pay_assignment_actions ac3
3196 where itpflg = 'N'
3197 and ac3.payroll_action_id = pa2.payroll_action_id
3198 and ac3.action_status not in ('C','S')
3199 and as3.assignment_id = ac3.assignment_id
3200 and pa2.effective_date between
3201 as3.effective_start_date and as3.effective_end_date
3202 and as3.person_id = as2.person_id)
3203 order by act.assignment_id
3204 for update of as1.assignment_id, pos.period_of_service_id;
3205 --
3206 cursor costingcur
3207 (
3208 pactid number,
3209 stperson number,
3210 endperson number,
3211 class varchar2,
3212 itpflg varchar2
3213 ) is
3214 select /*+ ORDERED
3215 index(pa2 PAY_PAYROLL_ACTIONS_N5)
3216 index(as1 PER_ASSIGNMENTS_F_N4)
3217 index(as2 PER_ASSIGNMENTS_F_PK)
3218 index(act PAY_ASSIGNMENT_ACTIONS_N51)
3219 USE_NL(pos as1) */ -- Bug 14184691 Added hint index(act PAY_ASSIGNMENT_ACTIONS_N51)
3220 act.assignment_action_id,
3221 act.assignment_id,
3222 act.tax_unit_id
3223 from pay_payroll_actions pa1,
3224 pay_payroll_actions pa2,
3225 pay_action_classifications pcl,
3226 per_periods_of_service pos,
3227 per_all_assignments_f as1,
3228 pay_assignment_actions act,
3229 per_all_assignments_f as2
3230 where pa1.payroll_action_id = pactid
3231 and pa2.consolidation_set_id = pa1.consolidation_set_id
3232 and pa2.effective_date between
3233 pa1.start_date and pa1.effective_date
3234 and act.payroll_action_id = pa2.payroll_action_id
3235 and act.action_status in ('C','S')
3236 and pcl.classification_name = class
3237 and pa2.action_type = pcl.action_type
3238 and as1.assignment_id = act.assignment_id
3239 and pa2.effective_date between
3240 as1.effective_start_date and as1.effective_end_date
3241 and as2.assignment_id = act.assignment_id
3242 and pa1.effective_date between
3243 as2.effective_start_date and as2.effective_end_date
3244 and pos.period_of_service_id = as1.period_of_service_id
3245 and pos.person_id between stperson and endperson
3246 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3247 and not exists (
3248 select null
3249 from pay_assignment_actions ac2,
3250 pay_payroll_actions pa3,
3251 pay_action_interlocks int
3252 where int.locked_action_id = act.assignment_action_id
3253 and ac2.assignment_action_id = int.locking_action_id
3254 and pa3.payroll_action_id = ac2.payroll_action_id
3255 and pa3.action_type in ('C', 'S'))
3256 and not exists (
3257 select /*+ ORDERED*/
3258 null
3259 from per_all_assignments_f as3,
3260 pay_assignment_actions ac3
3261 where itpflg = 'N'
3262 and ac3.payroll_action_id = pa2.payroll_action_id
3263 and ac3.action_status not in ('C','S')
3264 and as3.assignment_id = ac3.assignment_id
3265 and pa2.effective_date between
3266 as3.effective_start_date and as3.effective_end_date
3267 and as3.person_id = as2.person_id)
3268 order by act.assignment_id
3269 for update of as1.assignment_id, pos.period_of_service_id;
3270 --
3271 cursor costingmpipcur
3272 (
3273 pactid number,
3274 chunk number,
3275 class varchar2,
3276 itpflg varchar2
3277 ) is
3278 select /*+ ORDERED
3279 index(pa2 PAY_PAYROLL_ACTIONS_PK)
3280 index(pos PER_PERIODS_OF_SERVICE_N3)
3281 index(as1 PER_ASSIGNMENTS_F_N4)
3282 index(act PAY_ASSIGNMENT_ACTIONS_N51)
3283 index(as2 PER_ASSIGNMENTS_F_PK)
3284 USE_NL(pos pop act pa2 as2 as1) */
3285 act.assignment_action_id,
3286 act.assignment_id,
3287 act.tax_unit_id
3288 from pay_payroll_actions pa1,
3289 pay_population_ranges pop,
3290 per_periods_of_service pos,
3291 per_all_assignments_f as1,
3292 pay_assignment_actions act,
3293 pay_payroll_actions pa2,
3294 pay_action_classifications pcl,
3295 per_all_assignments_f as2
3296 where pa1.payroll_action_id = pactid
3297 and pa2.consolidation_set_id = pa1.consolidation_set_id
3298 and pa2.effective_date between
3299 pa1.start_date and pa1.effective_date
3300 and act.payroll_action_id = pa2.payroll_action_id
3301 and act.action_status in ('C','S')
3302 and pcl.classification_name = class
3303 and pa2.action_type = pcl.action_type
3304 and as1.assignment_id = act.assignment_id
3305 and pa1.effective_date between
3306 as1.effective_start_date and as1.effective_end_date
3307 and as2.assignment_id = act.assignment_id
3308 and pa2.effective_date between
3309 as2.effective_start_date and as2.effective_end_date
3310 and pos.period_of_service_id = as1.period_of_service_id
3311 and pop.payroll_action_id = pactid
3312 and pop.chunk_number = chunk
3313 and pos.person_id = pop.person_id
3314 and (as2.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3315 and not exists (
3316 select null
3317 from pay_assignment_actions ac2,
3318 pay_payroll_actions pa3,
3319 pay_action_interlocks int
3320 where int.locked_action_id = act.assignment_action_id
3321 and ac2.assignment_action_id = int.locking_action_id
3322 and pa3.payroll_action_id = ac2.payroll_action_id
3323 and pa3.action_type in ('C', 'S'))
3324 and not exists (
3325 select /*+ ORDERED*/
3326 null
3327 from per_all_assignments_f as3,
3328 pay_assignment_actions ac3
3329 where itpflg = 'N'
3330 and ac3.payroll_action_id = pa2.payroll_action_id
3331 and ac3.action_status not in ('C','S')
3332 and as3.assignment_id = ac3.assignment_id
3333 and pa2.effective_date between
3334 as3.effective_start_date and as3.effective_end_date
3335 and as3.person_id = as2.person_id)
3336 order by act.assignment_id
3337 for update of as1.assignment_id, pos.period_of_service_id;
3338 --
3339 lockingactid number;
3340 lockedactid number;
3341 assignid number;
3342 prev_assignid number;
3343 greid number;
3344 --
3345 begin
3346 prev_assignid := null;
3347 if (g_many_procs_in_period = 'Y') then
3348 open costingmpipcur(pactid,chunk,class,itpflg);
3349 elsif (use_pop_person = 1) then
3350 open costingpopcur(pactid,chunk,class,itpflg);
3351 else
3352 open costingcur(pactid,stperson,endperson,class,itpflg);
3353 end if;
3354 loop
3355 if (g_many_procs_in_period = 'Y') then
3356 fetch costingmpipcur into lockedactid,assignid,greid;
3357 exit when costingmpipcur%notfound;
3358 elsif (use_pop_person = 1) then
3359 fetch costingpopcur into lockedactid,assignid,greid;
3360 exit when costingpopcur%notfound;
3361 else
3362 fetch costingcur into lockedactid,assignid,greid;
3363 exit when costingcur%notfound;
3364 end if;
3365 --
3366 /* process the insert of assignment actions */
3367 /* logic prevents more than one action per assignment */
3368 if(prev_assignid is null OR prev_assignid <> assignid) then
3369 -- get a value for the action id that is locking.
3370 select pay_assignment_actions_s.nextval
3371 into lockingactid
3372 from dual;
3373 --
3374 -- insert into pay_assignment_actions.
3375 insact(lockingactid,assignid,pactid,rand_chunk,greid);
3376 end if;
3377 --
3378 -- insert into interlocks table.
3379 insint(lockingactid,lockedactid);
3380 --
3381 prev_assignid := assignid;
3382 end loop;
3383 if (g_many_procs_in_period = 'Y') then
3384 close costingmpipcur;
3385 elsif (use_pop_person = 1) then
3386 close costingpopcur;
3387 else
3388 close costingcur;
3389 end if;
3390 commit;
3391 end proc_costing;
3392 --
3393 ------------------------------- proc_paymcosting ---------------------------
3394 /*
3395 NAME
3396 proc_paymcosting - insert actions for Payment Costing action type.
3397 DESCRIPTION
3398 For the range defined by the starting and ending person_id,
3399 inserts a chunk of assignment actions and associated interlocks.
3400 NOTES
3401 <none>
3402 */
3403 procedure proc_paymcosting
3404 (
3405 pactid in number,
3406 stperson in number,
3407 endperson in number,
3408 chunk in number,
3409 rand_chunk in number,
3410 class in varchar2,
3411 itpflg in varchar2,
3412 use_pop_person in number
3413 ) is
3414 --
3415 cursor pmcostingpopcur
3416 (
3417 pactid number,
3418 chunk number,
3419 class varchar2,
3420 itpflg varchar2
3421 ) is
3422 select /*+ ORDERED
3423 index(pa2 PAY_PAYROLL_ACTIONS_N5)
3424 index(as1 PER_ASSIGNMENTS_F_N4)
3425 index(as2 PER_ASSIGNMENTS_F_PK)
3426 USE_NL(pos pop as1) */
3427 act.assignment_action_id,
3428 act.assignment_id,
3429 act.tax_unit_id,
3430 act.payroll_action_id
3431 from pay_payroll_actions pa1,
3432 pay_payroll_actions pa2,
3433 pay_action_classifications pcl,
3434 pay_population_ranges pop,
3435 per_periods_of_service pos,
3436 per_all_assignments_f as1,
3437 pay_assignment_actions act,
3438 per_all_assignments_f as2
3439 where pa1.payroll_action_id = pactid
3440 and pa2.consolidation_set_id = pa1.consolidation_set_id
3441 and pa2.effective_date between
3442 pa1.start_date and pa1.effective_date
3443 and act.payroll_action_id = pa2.payroll_action_id
3444 and act.action_status in ('C','S')
3445 and pcl.classification_name = class
3446 and pa2.action_type = pcl.action_type
3447 and as1.assignment_id = act.assignment_id
3448 and pa2.effective_date between
3449 as1.effective_start_date and as1.effective_end_date
3450 and as2.assignment_id = act.assignment_id
3451 and pa1.effective_date between
3452 as2.effective_start_date and as2.effective_end_date
3453 and pop.payroll_action_id = pactid
3454 and pop.chunk_number = chunk
3455 and pos.person_id = pop.person_id
3456 and pos.period_of_service_id = as1.period_of_service_id
3457 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3458 and not exists (
3459 select null
3460 from pay_assignment_actions ac2,
3461 pay_payroll_actions pa3,
3462 pay_action_interlocks int
3463 where int.locked_action_id = act.assignment_action_id
3464 and ac2.assignment_action_id = int.locking_action_id
3465 and pa3.payroll_action_id = ac2.payroll_action_id
3466 and pa3.action_type = 'CP')
3467 and not exists (
3468 select /*+ ORDERED*/
3469 null
3470 from per_all_assignments_f as3,
3471 pay_assignment_actions ac3
3472 where itpflg = 'N'
3473 and ac3.payroll_action_id = pa2.payroll_action_id
3474 and ac3.action_status not in ('C','S')
3475 and as3.assignment_id = ac3.assignment_id
3476 and pa2.effective_date between
3477 as3.effective_start_date and as3.effective_end_date
3478 and as3.person_id = as2.person_id)
3479 and ((pa2.action_type in ('P', 'U')
3480 and pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3481 and exists (select 1
3482 from pay_pre_payments ppp,
3483 pay_org_payment_methods_f pom
3484 where ppp.assignment_action_id = act.assignment_action_id
3485 and pom.org_payment_method_id = ppp.org_payment_method_id
3486 and pom.cost_payment = 'Y'
3487 and pa2.effective_date between
3488 pom.effective_start_date and pom.effective_end_date))
3489 or (pa2.action_type in ('H', 'M')
3490 and pa1.batch_process_mode in ('CLEARED', 'ALL')
3491 and exists (select 1
3492 from pay_pre_payments ppp,
3493 pay_org_payment_methods_f pom,
3494 pay_ce_reconciled_payments crp
3495 where ppp.pre_payment_id = act.pre_payment_id
3496 and pom.org_payment_method_id = ppp.org_payment_method_id
3497 and pom.cost_cleared_payment = 'Y'
3498 and crp.assignment_action_id = act.assignment_action_id
3499 and pa2.effective_date between
3500 pom.effective_start_date and pom.effective_end_date))
3501 or (pa2.action_type = 'E'
3502 and pa1.batch_process_mode in ('CLEARED', 'ALL')
3503 and exists (select 1
3504 from pay_pre_payments ppp,
3505 pay_org_payment_methods_f pom,
3506 pay_ce_reconciled_payments crp
3507 where ppp.pre_payment_id = act.pre_payment_id
3508 and pom.org_payment_method_id = ppp.org_payment_method_id
3509 and pom.cost_cleared_payment = 'Y'
3510 and nvl(pom.exclude_manual_payment, 'N') = 'N'
3511 and crp.assignment_action_id = act.assignment_action_id
3512 and pa2.effective_date between
3513 pom.effective_start_date and pom.effective_end_date))
3514 or (pa2.action_type = 'D'
3515 and ((pa1.batch_process_mode in ('CLEARED', 'ALL')
3516 and exists (select 1
3517 from pay_action_interlocks int,
3518 pay_assignment_actions chq,
3519 pay_payroll_actions pcq,
3520 pay_pre_payments ppp,
3521 pay_org_payment_methods_f pom,
3522 pay_ce_reconciled_payments crp
3523 where int.locking_action_id = act.assignment_action_id
3524 and chq.assignment_action_id = int.locked_action_id
3525 and pcq.payroll_action_id = chq.payroll_action_id
3526 and pcq.action_type = 'H'
3527 and ppp.pre_payment_id = chq.pre_payment_id
3528 and pom.org_payment_method_id = ppp.org_payment_method_id
3529 and pom.cost_cleared_payment = 'Y'
3530 and crp.assignment_action_id = act.assignment_action_id
3531 and pa2.effective_date between
3532 pom.effective_start_date and pom.effective_end_date))
3533 or (pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3534 and exists (select 1
3535 from pay_action_interlocks int,
3536 pay_assignment_actions chq,
3537 pay_payroll_actions pcq,
3538 pay_pre_payments ppp,
3539 pay_org_payment_methods_f pom
3540 where int.locking_action_id = act.assignment_action_id
3541 and chq.assignment_action_id = int.locked_action_id
3542 and pcq.payroll_action_id = chq.payroll_action_id
3543 and pcq.action_type = 'H'
3544 and ppp.pre_payment_id = chq.pre_payment_id
3545 and pom.org_payment_method_id = ppp.org_payment_method_id
3546 and pom.cost_payment = 'Y'
3547 and nvl(pom.cost_cleared_void_payment, 'N') = 'N'
3548 and pa2.effective_date between
3549 pom.effective_start_date and pom.effective_end_date)))))
3550 order by act.assignment_id
3551 for update of as1.assignment_id, pos.period_of_service_id;
3552 --
3553 cursor pmcostingcur
3554 (
3555 pactid number,
3556 stperson number,
3557 endperson number,
3558 class varchar2,
3559 itpflg varchar2
3560 ) is
3561 select /*+ ORDERED
3562 index(pa2 PAY_PAYROLL_ACTIONS_N5)
3563 index(as1 PER_ASSIGNMENTS_F_N4)
3564 index(as2 PER_ASSIGNMENTS_F_PK)
3565 USE_NL(pos as1) */
3566 act.assignment_action_id,
3567 act.assignment_id,
3568 act.tax_unit_id,
3569 act.payroll_action_id
3570 from pay_payroll_actions pa1,
3571 pay_payroll_actions pa2,
3572 pay_action_classifications pcl,
3573 per_periods_of_service pos,
3574 per_all_assignments_f as1,
3575 pay_assignment_actions act,
3576 per_all_assignments_f as2
3577 where pa1.payroll_action_id = pactid
3578 and pa2.consolidation_set_id = pa1.consolidation_set_id
3579 and pa2.effective_date between
3580 pa1.start_date and pa1.effective_date
3581 and act.payroll_action_id = pa2.payroll_action_id
3582 and act.action_status in ('C','S')
3583 and pcl.classification_name = class
3584 and pa2.action_type = pcl.action_type
3585 and as1.assignment_id = act.assignment_id
3586 and pa2.effective_date between
3587 as1.effective_start_date and as1.effective_end_date
3588 and as2.assignment_id = act.assignment_id
3589 and pa1.effective_date between
3590 as2.effective_start_date and as2.effective_end_date
3591 and pos.period_of_service_id = as1.period_of_service_id
3592 and pos.person_id between stperson and endperson
3593 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3594 and not exists (
3595 select null
3596 from pay_assignment_actions ac2,
3597 pay_payroll_actions pa3,
3598 pay_action_interlocks int
3599 where int.locked_action_id = act.assignment_action_id
3600 and ac2.assignment_action_id = int.locking_action_id
3601 and pa3.payroll_action_id = ac2.payroll_action_id
3602 and pa3.action_type = 'CP')
3603 and not exists (
3604 select /*+ ORDERED*/
3605 null
3606 from per_all_assignments_f as3,
3607 pay_assignment_actions ac3
3608 where itpflg = 'N'
3609 and ac3.payroll_action_id = pa2.payroll_action_id
3610 and ac3.action_status not in ('C','S')
3611 and as3.assignment_id = ac3.assignment_id
3612 and pa2.effective_date between
3613 as3.effective_start_date and as3.effective_end_date
3614 and as3.person_id = as2.person_id)
3615 and ((pa2.action_type in ('P', 'U')
3616 and pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3617 and exists (select 1
3618 from pay_pre_payments ppp,
3619 pay_org_payment_methods_f pom
3620 where ppp.assignment_action_id = act.assignment_action_id
3621 and pom.org_payment_method_id = ppp.org_payment_method_id
3622 and pom.cost_payment = 'Y'
3623 and pa2.effective_date between
3624 pom.effective_start_date and pom.effective_end_date))
3625 or (pa2.action_type in ('H', 'M')
3626 and pa1.batch_process_mode in ('CLEARED', 'ALL')
3627 and exists (select 1
3628 from pay_pre_payments ppp,
3629 pay_org_payment_methods_f pom,
3630 pay_ce_reconciled_payments crp
3631 where ppp.pre_payment_id = act.pre_payment_id
3632 and pom.org_payment_method_id = ppp.org_payment_method_id
3633 and pom.cost_cleared_payment = 'Y'
3634 and crp.assignment_action_id = act.assignment_action_id
3635 and pa2.effective_date between
3636 pom.effective_start_date and pom.effective_end_date))
3637 or (pa2.action_type = 'E'
3638 and pa1.batch_process_mode in ('CLEARED', 'ALL')
3639 and exists (select 1
3640 from pay_pre_payments ppp,
3641 pay_org_payment_methods_f pom,
3642 pay_ce_reconciled_payments crp
3643 where ppp.pre_payment_id = act.pre_payment_id
3644 and pom.org_payment_method_id = ppp.org_payment_method_id
3645 and pom.cost_cleared_payment = 'Y'
3646 and nvl(pom.exclude_manual_payment, 'N') = 'N'
3647 and crp.assignment_action_id = act.assignment_action_id
3648 and pa2.effective_date between
3649 pom.effective_start_date and pom.effective_end_date))
3650 or (pa2.action_type = 'D'
3651 and ((pa1.batch_process_mode in ('CLEARED', 'ALL')
3652 and exists (select 1
3653 from pay_action_interlocks int,
3654 pay_assignment_actions chq,
3655 pay_payroll_actions pcq,
3656 pay_pre_payments ppp,
3657 pay_org_payment_methods_f pom,
3658 pay_ce_reconciled_payments crp
3659 where int.locking_action_id = act.assignment_action_id
3660 and chq.assignment_action_id = int.locked_action_id
3661 and pcq.payroll_action_id = chq.payroll_action_id
3662 and pcq.action_type = 'H'
3663 and ppp.pre_payment_id = chq.pre_payment_id
3664 and pom.org_payment_method_id = ppp.org_payment_method_id
3665 and pom.cost_cleared_payment = 'Y'
3666 and crp.assignment_action_id = act.assignment_action_id
3667 and pa2.effective_date between
3668 pom.effective_start_date and pom.effective_end_date))
3669 or (pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3670 and exists (select 1
3671 from pay_action_interlocks int,
3672 pay_assignment_actions chq,
3673 pay_payroll_actions pcq,
3674 pay_pre_payments ppp,
3675 pay_org_payment_methods_f pom
3676 where int.locking_action_id = act.assignment_action_id
3677 and chq.assignment_action_id = int.locked_action_id
3678 and pcq.payroll_action_id = chq.payroll_action_id
3679 and pcq.action_type = 'H'
3680 and ppp.pre_payment_id = chq.pre_payment_id
3681 and pom.org_payment_method_id = ppp.org_payment_method_id
3682 and pom.cost_payment = 'Y'
3683 and nvl(pom.cost_cleared_void_payment, 'N') = 'N'
3684 and pa2.effective_date between
3685 pom.effective_start_date and pom.effective_end_date)))))
3686 order by act.assignment_id
3687 for update of as1.assignment_id, pos.period_of_service_id;
3688 --
3689 cursor pmcostingmpipcur
3690 (
3691 pactid number,
3692 chunk number,
3693 class varchar2,
3694 itpflg varchar2
3695 ) is
3696 select /*+ ORDERED
3697 index(pa2 PAY_PAYROLL_ACTIONS_PK)
3698 index(pos PER_PERIODS_OF_SERVICE_N3)
3699 index(as1 PER_ASSIGNMENTS_F_N4)
3700 index(act PAY_ASSIGNMENT_ACTIONS_N51)
3701 index(as2 PER_ASSIGNMENTS_F_PK)
3702 USE_NL(pos pop act pa2 as2 as1) */
3703 act.assignment_action_id,
3704 act.assignment_id,
3705 act.tax_unit_id,
3706 act.payroll_action_id
3707 from pay_payroll_actions pa1,
3708 pay_population_ranges pop,
3709 per_periods_of_service pos,
3710 per_all_assignments_f as1,
3711 pay_assignment_actions act,
3712 pay_payroll_actions pa2,
3713 pay_action_classifications pcl,
3714 per_all_assignments_f as2
3715 where pa1.payroll_action_id = pactid
3716 and pa2.consolidation_set_id = pa1.consolidation_set_id
3717 and pa2.effective_date between
3718 pa1.start_date and pa1.effective_date
3719 and act.payroll_action_id = pa2.payroll_action_id
3720 and act.action_status in ('C','S')
3721 and pcl.classification_name = class
3722 and pa2.action_type = pcl.action_type
3723 and as1.assignment_id = act.assignment_id
3724 and pa1.effective_date between
3725 as1.effective_start_date and as1.effective_end_date
3726 and as2.assignment_id = act.assignment_id
3727 and pa2.effective_date between
3728 as2.effective_start_date and as2.effective_end_date
3729 and pos.period_of_service_id = as1.period_of_service_id
3730 and pop.payroll_action_id = pactid
3731 and pop.chunk_number = chunk
3732 and pos.person_id = pop.person_id
3733 and (as2.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3734 and not exists (
3735 select null
3736 from pay_assignment_actions ac2,
3737 pay_payroll_actions pa3,
3738 pay_action_interlocks int
3739 where int.locked_action_id = act.assignment_action_id
3740 and ac2.assignment_action_id = int.locking_action_id
3741 and pa3.payroll_action_id = ac2.payroll_action_id
3742 and pa3.action_type = 'CP')
3743 and not exists (
3744 select /*+ ORDERED*/
3745 null
3746 from per_all_assignments_f as3,
3747 pay_assignment_actions ac3
3748 where itpflg = 'N'
3749 and ac3.payroll_action_id = pa2.payroll_action_id
3750 and ac3.action_status not in ('C','S')
3751 and as3.assignment_id = ac3.assignment_id
3752 and pa2.effective_date between
3753 as3.effective_start_date and as3.effective_end_date
3754 and as3.person_id = as2.person_id)
3755 and ((pa2.action_type in ('P', 'U')
3756 and pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3757 and exists (select 1
3758 from pay_pre_payments ppp,
3759 pay_org_payment_methods_f pom
3760 where ppp.assignment_action_id = act.assignment_action_id
3761 and pom.org_payment_method_id = ppp.org_payment_method_id
3762 and pom.cost_payment = 'Y'
3763 and pa2.effective_date between
3764 pom.effective_start_date and pom.effective_end_date))
3765 or (pa2.action_type in ('H', 'M')
3766 and pa1.batch_process_mode in ('CLEARED', 'ALL')
3767 and exists (select 1
3768 from pay_pre_payments ppp,
3769 pay_org_payment_methods_f pom,
3770 pay_ce_reconciled_payments crp
3771 where ppp.pre_payment_id = act.pre_payment_id
3772 and pom.org_payment_method_id = ppp.org_payment_method_id
3773 and pom.cost_cleared_payment = 'Y'
3774 and crp.assignment_action_id = act.assignment_action_id
3775 and pa2.effective_date between
3776 pom.effective_start_date and pom.effective_end_date))
3777 or (pa2.action_type = 'E'
3778 and pa1.batch_process_mode in ('CLEARED', 'ALL')
3779 and exists (select 1
3780 from pay_pre_payments ppp,
3781 pay_org_payment_methods_f pom,
3782 pay_ce_reconciled_payments crp
3783 where ppp.pre_payment_id = act.pre_payment_id
3784 and pom.org_payment_method_id = ppp.org_payment_method_id
3785 and pom.cost_cleared_payment = 'Y'
3786 and nvl(pom.exclude_manual_payment, 'N') = 'N'
3787 and crp.assignment_action_id = act.assignment_action_id
3788 and pa2.effective_date between
3789 pom.effective_start_date and pom.effective_end_date))
3790 or (pa2.action_type = 'D'
3791 and ((pa1.batch_process_mode in ('CLEARED', 'ALL')
3792 and exists (select 1
3793 from pay_action_interlocks int,
3794 pay_assignment_actions chq,
3795 pay_payroll_actions pcq,
3796 pay_pre_payments ppp,
3797 pay_org_payment_methods_f pom,
3798 pay_ce_reconciled_payments crp
3799 where int.locking_action_id = act.assignment_action_id
3800 and chq.assignment_action_id = int.locked_action_id
3801 and pcq.payroll_action_id = chq.payroll_action_id
3802 and pcq.action_type = 'H'
3803 and ppp.pre_payment_id = chq.pre_payment_id
3804 and pom.org_payment_method_id = ppp.org_payment_method_id
3805 and pom.cost_cleared_payment = 'Y'
3806 and crp.assignment_action_id = act.assignment_action_id
3807 and pa2.effective_date between
3808 pom.effective_start_date and pom.effective_end_date))
3809 or (pa1.batch_process_mode in ('UNCLEARED', 'ALL')
3810 and exists (select 1
3811 from pay_action_interlocks int,
3812 pay_assignment_actions chq,
3813 pay_payroll_actions pcq,
3814 pay_pre_payments ppp,
3815 pay_org_payment_methods_f pom
3816 where int.locking_action_id = act.assignment_action_id
3817 and chq.assignment_action_id = int.locked_action_id
3818 and pcq.payroll_action_id = chq.payroll_action_id
3819 and pcq.action_type = 'H'
3820 and ppp.pre_payment_id = chq.pre_payment_id
3821 and pom.org_payment_method_id = ppp.org_payment_method_id
3822 and pom.cost_payment = 'Y'
3823 and nvl(pom.cost_cleared_void_payment, 'N') = 'N'
3824 and pa2.effective_date between
3825 pom.effective_start_date and pom.effective_end_date)))))
3826 order by act.assignment_id
3827 for update of as1.assignment_id, pos.period_of_service_id;
3828 --
3829 lockingactid number;
3830 lockedactid number;
3831 assignid number;
3832 prev_assignid number;
3833 lpactid number;
3834 prev_pactid number;
3835 greid number;
3836 --
3837 begin
3838 prev_assignid := null;
3839 prev_pactid := null;
3840 if (g_many_procs_in_period = 'Y') then
3841 open pmcostingmpipcur(pactid,chunk,class,itpflg);
3842 elsif (use_pop_person = 1) then
3843 open pmcostingpopcur(pactid,chunk,class,itpflg);
3844 else
3845 open pmcostingcur(pactid,stperson,endperson,class,itpflg);
3846 end if;
3847 loop
3848 if (g_many_procs_in_period = 'Y') then
3849 fetch pmcostingmpipcur into lockedactid,assignid,greid,lpactid;
3850 exit when pmcostingmpipcur%notfound;
3851 elsif (use_pop_person = 1) then
3852 fetch pmcostingpopcur into lockedactid,assignid,greid,lpactid;
3853 exit when pmcostingpopcur%notfound;
3854 else
3855 fetch pmcostingcur into lockedactid,assignid,greid,lpactid;
3856 exit when pmcostingcur%notfound;
3857 end if;
3858 --
3859 /* process the insert of assignment actions */
3860 /* logic prevents more than one action per assignment */
3861 if(prev_assignid is null OR prev_assignid <> assignid OR
3862 prev_pactid <> lpactid) then
3863 -- get a value for the action id that is locking.
3864 select pay_assignment_actions_s.nextval
3865 into lockingactid
3866 from dual;
3867 --
3868 -- insert into pay_assignment_actions.
3869 insact(lockingactid,assignid,pactid,rand_chunk,greid);
3870 end if;
3871 --
3872 -- insert into interlocks table.
3873 insint(lockingactid,lockedactid);
3874 --
3875 prev_assignid := assignid;
3876 prev_pactid := lpactid;
3877 end loop;
3878 if (g_many_procs_in_period = 'Y') then
3879 close pmcostingmpipcur;
3880 elsif (use_pop_person = 1) then
3881 close pmcostingpopcur;
3882 else
3883 close pmcostingcur;
3884 end if;
3885 commit;
3886 end proc_paymcosting;
3887 --
3888 ------------------------------- proc_estcosts ------------------------------
3889 /*
3890 NAME
3891 proc_estcosts - insert actions for Estimate Costing action type.
3892 DESCRIPTION
3893 For the range defined by the starting and ending person_id,
3894 inserts a chunk of assignment actions
3895 NOTES
3896 <none>
3897 */
3898 procedure proc_estcosts
3899 (
3900 pactid in number,
3901 stperson in number,
3902 endperson in number,
3903 chunk in number,
3904 rand_chunk in number,
3905 class in varchar2,
3906 itpflg in varchar2,
3907 use_pop_person in number
3908 ) is
3909 --
3910 cursor estcostingpopcur
3911 (
3912 pactid number,
3913 chunk number,
3914 class varchar2,
3915 itpflg varchar2
3916 ) is
3917 select /*+ ORDERED
3918 index(pa2 PAY_PAYROLL_ACTIONS_N5)
3919 index(as1 PER_ASSIGNMENTS_F_N4)
3920 index(as2 PER_ASSIGNMENTS_F_PK)
3921 USE_NL(pop pos as1) */
3922 act.assignment_action_id,
3923 act.assignment_id,
3924 act.tax_unit_id
3925 from pay_payroll_actions pa1,
3926 pay_all_payrolls_f pay,
3927 per_time_periods ptp,
3928 pay_payroll_actions pa2,
3929 pay_action_classifications pcl,
3930 pay_population_ranges pop,
3931 per_periods_of_service pos,
3932 per_all_assignments_f as1,
3933 pay_assignment_actions act,
3934 per_all_assignments_f as2
3935 where pa1.payroll_action_id = pactid
3936 and pay.consolidation_set_id = pa1.consolidation_set_id
3937 and pa1.effective_date between
3938 pay.effective_start_date and pay.effective_end_date
3939 and ptp.payroll_id = pay.payroll_id
3940 and pa1.start_date between
3941 ptp.start_date and ptp.end_date
3942 and pa2.consolidation_set_id = pa1.consolidation_set_id
3943 and pa2.effective_date between
3944 ptp.start_date and ptp.end_date
3945 and act.payroll_action_id = pa2.payroll_action_id
3946 and act.action_status in ('C','S')
3947 and pcl.classification_name = class
3948 and pa2.action_type = pcl.action_type
3949 and as1.assignment_id = act.assignment_id
3950 and pa2.effective_date between
3951 as1.effective_start_date and as1.effective_end_date
3952 and as2.assignment_id = act.assignment_id
3953 and pa1.effective_date between
3954 as2.effective_start_date and as2.effective_end_date
3955 and as2.payroll_id = as1.payroll_id
3956 and pop.payroll_action_id = pactid
3957 and pop.chunk_number = chunk
3958 and pos.person_id = pop.person_id
3959 and pos.period_of_service_id = as1.period_of_service_id
3960 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
3961 and not exists (
3962 select /*+ ORDERED*/
3963 null
3964 from per_all_assignments_f as3,
3965 pay_assignment_actions ac3
3966 where itpflg = 'N'
3967 and ac3.payroll_action_id = pa2.payroll_action_id
3968 and ac3.action_status not in ('C','S')
3969 and as3.assignment_id = ac3.assignment_id
3970 and pa2.effective_date between
3971 as3.effective_start_date and as3.effective_end_date
3972 and as3.person_id = as2.person_id)
3973 order by act.assignment_id
3974 for update of as1.assignment_id, pos.period_of_service_id;
3975 --
3976 cursor estcostingcur
3977 (
3978 pactid number,
3979 stperson number,
3980 endperson number,
3981 class varchar2,
3982 itpflg varchar2
3983 ) is
3984 select /*+ ORDERED
3985 index(pa2 PAY_PAYROLL_ACTIONS_N5)
3986 index(as1 PER_ASSIGNMENTS_F_N4)
3987 index(as2 PER_ASSIGNMENTS_F_PK)
3988 USE_NL(pos as1) */
3989 act.assignment_action_id,
3990 act.assignment_id,
3991 act.tax_unit_id
3992 from pay_payroll_actions pa1,
3993 pay_all_payrolls_f pay,
3994 per_time_periods ptp,
3995 pay_payroll_actions pa2,
3996 pay_action_classifications pcl,
3997 per_periods_of_service pos,
3998 per_all_assignments_f as1,
3999 pay_assignment_actions act,
4000 per_all_assignments_f as2
4001 where pa1.payroll_action_id = pactid
4002 and pay.consolidation_set_id = pa1.consolidation_set_id
4003 and pa1.effective_date between
4004 pay.effective_start_date and pay.effective_end_date
4005 and ptp.payroll_id = pay.payroll_id
4006 and pa1.start_date between
4007 ptp.start_date and ptp.end_date
4008 and pa2.consolidation_set_id = pa1.consolidation_set_id
4009 and pa2.effective_date between
4010 ptp.start_date and ptp.end_date
4011 and act.payroll_action_id = pa2.payroll_action_id
4012 and act.action_status in ('C','S')
4013 and pcl.classification_name = class
4014 and pa2.action_type = pcl.action_type
4015 and as1.assignment_id = act.assignment_id
4016 and pa2.effective_date between
4017 as1.effective_start_date and as1.effective_end_date
4018 and as2.assignment_id = act.assignment_id
4019 and pa1.effective_date between
4020 as2.effective_start_date and as2.effective_end_date
4021 and as2.payroll_id = as1.payroll_id
4022 and pos.period_of_service_id = as1.period_of_service_id
4023 and pos.person_id between stperson and endperson
4024 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
4025 and not exists (
4026 select /*+ ORDERED*/
4027 null
4028 from per_all_assignments_f as3,
4029 pay_assignment_actions ac3
4030 where itpflg = 'N'
4031 and ac3.payroll_action_id = pa2.payroll_action_id
4032 and ac3.action_status not in ('C','S')
4033 and as3.assignment_id = ac3.assignment_id
4034 and pa2.effective_date between
4035 as3.effective_start_date and as3.effective_end_date
4036 and as3.person_id = as2.person_id)
4037 order by act.assignment_id
4038 for update of as1.assignment_id, pos.period_of_service_id;
4039 --
4040 lockingactid number;
4041 lockedactid number;
4042 assignid number;
4043 prev_assignid number;
4044 greid number;
4045 --
4046 begin
4047 prev_assignid := null;
4048 if (use_pop_person = 1) then
4049 open estcostingpopcur(pactid,chunk,class,itpflg);
4050 else
4051 open estcostingcur(pactid,stperson,endperson,class,itpflg);
4052 end if;
4053 loop
4054 if (use_pop_person = 1) then
4055 fetch estcostingpopcur into lockedactid,assignid,greid;
4056 exit when estcostingpopcur%notfound;
4057 else
4058 fetch estcostingcur into lockedactid,assignid,greid;
4059 exit when estcostingcur%notfound;
4060 end if;
4061 --
4062 /* process the insert of assignment actions */
4063 /* logic prevents more than one action per assignment */
4064 if(prev_assignid is null OR prev_assignid <> assignid) then
4065 -- get a value for the action id that is locking.
4066 select pay_assignment_actions_s.nextval
4067 into lockingactid
4068 from dual;
4069 --
4070 -- insert into pay_assignment_actions.
4071 insact(lockingactid,assignid,pactid,rand_chunk,greid);
4072 end if;
4073 --
4074 prev_assignid := assignid;
4075 end loop;
4076 if (use_pop_person = 1) then
4077 close estcostingpopcur;
4078 else
4079 close estcostingcur;
4080 end if;
4081 commit;
4082 end proc_estcosts;
4083 --
4084 ---------------------------------- procbee ---------------------------------
4085 /*
4086 NAME
4087 procbee - insert assignment actions for Batch Element Entry.
4088 DESCRIPTION
4089 Insert assignment actions for the Batch Element Entry process.
4090 NOTES
4091 The insert of assignment actions for Batch Element Entry is based
4092 on the followig logic: We select all the assignments within the
4093 specified range. One assignment action is then inserted
4094 for each of the assignment selected.
4095 */
4096 procedure procbee
4097 (
4098 pactid in number,
4099 stperson in number,
4100 endperson in number,
4101 chunk in number,
4102 rand_chunk in number,
4103 use_pop_person in number
4104 ) is
4105 --
4106 cursor beepopcur
4107 (
4108 pactid number,
4109 chunk number
4110 ) is
4111 select asg.assignment_id
4112 from pay_payroll_actions pac,
4113 pay_population_ranges pop,
4114 pay_batch_headers bth,
4115 pay_batch_lines btl,
4116 per_all_assignments_f asg
4117 where pac.payroll_action_id = pactid
4118 and pac.action_type = 'BEE'
4119 and pac.batch_id = bth.batch_id
4120 and bth.batch_id = btl.batch_id
4121 and btl.assignment_id = asg.assignment_id
4122 and btl.effective_date between asg.effective_start_date
4123 and asg.effective_end_date
4124 and pop.payroll_action_id = pactid
4125 and pop.chunk_number = chunk
4126 and asg.person_id = pop.person_id
4127 order by asg.assignment_id
4128 for update of asg.assignment_id, btl.batch_line_id;
4129 --
4130 cursor beecur
4131 (
4132 pactid number,
4133 stperson number,
4134 endperson number
4135 ) is
4136 select asg.assignment_id
4137 from pay_payroll_actions pac,
4138 pay_batch_lines btl,
4139 per_all_assignments_f asg
4140 where pac.payroll_action_id = pactid
4141 and pac.action_type = 'BEE'
4142 and pac.batch_id = btl.batch_id
4143 and btl.assignment_id = asg.assignment_id
4144 and btl.effective_date between asg.effective_start_date
4145 and asg.effective_end_date
4146 and asg.person_id between stperson and endperson
4147 order by asg.assignment_id
4148 for update of asg.assignment_id, btl.batch_line_id;
4149 --
4150 asgactid number;
4151 assignid number;
4152 preasgid number;
4153 --
4154 begin
4155 preasgid := null;
4156 if (use_pop_person = 1) then
4157 open beepopcur(pactid,chunk);
4158 else
4159 open beecur(pactid,stperson,endperson);
4160 end if;
4161 loop
4162 if (use_pop_person = 1) then
4163 fetch beepopcur into assignid;
4164 exit when beepopcur%notfound;
4165 else
4166 fetch beecur into assignid;
4167 exit when beecur%notfound;
4168 end if;
4169 --
4170 -- Get an assignment_action_id.
4171 select pay_assignment_actions_s.nextval
4172 into asgactid
4173 from dual;
4174 --
4175 if preasgid is null or preasgid <> assignid then
4176 -- Insert an assignment action for each action.
4177 insact(asgactid,assignid,pactid,rand_chunk,null,null);
4178 preasgid := assignid;
4179 end if;
4180 --
4181 end loop;
4182 if (use_pop_person = 1) then
4183 close beepopcur;
4184 else
4185 close beecur;
4186 end if;
4187 end procbee;
4188 --
4189 ---------------------------------- proctgl ---------------------------------
4190 /*
4191 NAME
4192 proctgl - insert assignment actions for Transfer to GL.
4193 DESCRIPTION
4194 Insert assignment actions for the Transfer to GL process.
4195 NOTES
4196 The insert of assignment actions for Transfer to GL is based
4197 on the followig logic: We select all the (Payroll Run)
4198 assignment actions that have been costed within the
4199 specified date range. One assignment action is then inserted
4200 for each of the assignment actions selected. In addition,
4201 an interlock row is inserted from the newly created TGL action
4202 to both the Costing action and to the Payroll Run actions that
4203 were costed by it. (Phew)
4204 */
4205 procedure proctgl
4206 (
4207 pactid in number,
4208 stperson in number,
4209 endperson in number,
4210 chunk in number,
4211 rand_chunk in number,
4212 itpflg in varchar2,
4213 use_pop_person in number
4214 ) is
4215 cursor tglpopcur
4216 (
4217 pactid number,
4218 chunk number,
4219 itpflg varchar2
4220 ) is
4221 select /*+ ORDERED
4222 index(pa2 PAY_PAYROLL_ACTIONS_N5)
4223 index(as1 PER_ASSIGNMENTS_F_PK)
4224 index(as2 PER_ASSIGNMENTS_F_N4)
4225 index(ac2 PAY_ASSIGNMENT_ACTIONS_N51)
4226 USE_NL(pop pos as1 as2) */ -- Bug 14184691 Added hint index(ac2 PAY_ASSIGNMENT_ACTIONS_N51)
4227 ac2.assignment_action_id,
4228 ac2.assignment_id,
4229 ac2.tax_unit_id,
4230 pa2.action_type
4231 from pay_payroll_actions pa,
4232 pay_payroll_actions pa2,
4233 pay_action_classifications pcl,
4234 pay_population_ranges pop,
4235 per_periods_of_service pos,
4236 per_all_assignments_f as2,
4237 pay_assignment_actions ac2,
4238 per_all_assignments_f as1
4239 where pa.payroll_action_id = pactid
4240 and pa2.consolidation_set_id = pa.consolidation_set_id
4241 and pa2.effective_date between
4242 pa.start_date and pa.effective_date
4243 and ac2.payroll_action_id = pa2.payroll_action_id
4244 and ac2.action_status = 'C'
4245 and pcl.classification_name = 'TRANSGL'
4246 and pa2.action_type = pcl.action_type
4247 and as2.assignment_id = ac2.assignment_id
4248 and pa.effective_date between
4249 as2.effective_start_date and as2.effective_end_date
4250 and as1.assignment_id = ac2.assignment_id
4251 and pa2.effective_date between
4252 as1.effective_start_date and as1.effective_end_date
4253 and nvl(decode(pa2.action_type, 'EC', nvl(pa.payroll_id, as1.payroll_id),
4254 'CP', nvl(pa.payroll_id, as1.payroll_id),
4255 as1.payroll_id), -999) = nvl(as1.payroll_id, -999)
4256 and pos.period_of_service_id = as2.period_of_service_id
4257 and pop.payroll_action_id = pactid
4258 and pop.chunk_number = chunk
4259 and pos.person_id = pop.person_id
4260 and not exists (
4261 select null
4262 from pay_assignment_actions ac3,
4263 pay_payroll_actions pa3,
4264 pay_action_interlocks in3
4265 where in3.locked_action_id = ac2.assignment_action_id
4266 and ac3.assignment_action_id = in3.locking_action_id
4267 and pa3.payroll_action_id = ac3.payroll_action_id
4268 and pa3.action_type = pa.action_type)
4269 and not exists (
4270 select /*+ ORDERED*/
4271 null
4272 from per_all_assignments_f as3,
4273 pay_assignment_actions ac3
4274 where itpflg = 'N'
4275 and ac3.payroll_action_id = pa2.payroll_action_id
4276 and ac3.action_status not in ('C','S')
4277 and as3.assignment_id = ac3.assignment_id
4278 and pa2.effective_date between
4279 as3.effective_start_date and as3.effective_end_date
4280 and as3.person_id = as1.person_id)
4281 order by ac2.assignment_id, ac2.payroll_action_id, ac2.assignment_action_id
4282 for update of as2.assignment_id, pos.period_of_service_id;
4283 --
4284 cursor tglcur
4285 (
4286 pactid number,
4287 stperson number,
4288 endperson number,
4289 itpflg varchar2
4290 ) is
4291 select /*+ ORDERED
4292 index(pa2 PAY_PAYROLL_ACTIONS_N5)
4293 index(as1 PER_ASSIGNMENTS_F_PK)
4294 index(as2 PER_ASSIGNMENTS_F_N4)
4295 index(ac2 PAY_ASSIGNMENT_ACTIONS_N51)
4296 USE_NL(pos as1 as2) */ -- Bug 14184691 Added hint index(ac2 PAY_ASSIGNMENT_ACTIONS_N51)
4297 ac2.assignment_action_id,
4298 ac2.assignment_id,
4299 ac2.tax_unit_id,
4300 pa2.action_type
4301 from pay_payroll_actions pa,
4302 pay_payroll_actions pa2,
4303 pay_action_classifications pcl,
4304 per_periods_of_service pos,
4305 per_all_assignments_f as2,
4306 pay_assignment_actions ac2,
4307 per_all_assignments_f as1
4308 where pa.payroll_action_id = pactid
4309 and pa2.consolidation_set_id = pa.consolidation_set_id
4310 and pa2.effective_date between
4311 pa.start_date and pa.effective_date
4312 and ac2.payroll_action_id = pa2.payroll_action_id
4313 and ac2.action_status = 'C'
4314 and pcl.classification_name = 'TRANSGL'
4315 and pa2.action_type = pcl.action_type
4316 and as2.assignment_id = ac2.assignment_id
4317 and pa.effective_date between
4318 as2.effective_start_date and as2.effective_end_date
4319 and as1.assignment_id = ac2.assignment_id
4320 and pa2.effective_date between
4321 as1.effective_start_date and as1.effective_end_date
4322 and nvl(decode(pa2.action_type, 'EC', nvl(pa.payroll_id, as1.payroll_id),
4323 'CP', nvl(pa.payroll_id, as1.payroll_id),
4324 as1.payroll_id), -999) = nvl(as1.payroll_id, -999)
4325 and pos.period_of_service_id = as2.period_of_service_id
4326 and pos.person_id between
4327 stperson and endperson
4328 and not exists (
4329 select null
4330 from pay_assignment_actions ac3,
4331 pay_payroll_actions pa3,
4332 pay_action_interlocks in3
4333 where in3.locked_action_id = ac2.assignment_action_id
4334 and ac3.assignment_action_id = in3.locking_action_id
4335 and pa3.payroll_action_id = ac3.payroll_action_id
4336 and pa3.action_type = pa.action_type)
4337 and not exists (
4338 select /*+ ORDERED*/
4339 null
4340 from per_all_assignments_f as3,
4341 pay_assignment_actions ac3
4342 where itpflg = 'N'
4343 and ac3.payroll_action_id = pa2.payroll_action_id
4344 and ac3.action_status not in ('C','S')
4345 and as3.assignment_id = ac3.assignment_id
4346 and pa2.effective_date between
4347 as3.effective_start_date and as3.effective_end_date
4348 and as3.person_id = as1.person_id)
4349 order by ac2.assignment_id, ac2.payroll_action_id, ac2.assignment_action_id
4350 for update of as2.assignment_id, pos.period_of_service_id;
4351 --
4352 cursor tglmpipcur
4353 (
4354 pactid number,
4355 chunk number,
4356 itpflg varchar2
4357 ) is
4358 select /*+ ORDERED
4359 index(pa2 PAY_PAYROLL_ACTIONS_PK)
4360 index(pos PER_PERIODS_OF_SERVICE_N3)
4361 index(as2 PER_ASSIGNMENTS_F_N4)
4362 index(ac2 PAY_ASSIGNMENT_ACTIONS_N51)
4363 index(as1 PER_ASSIGNMENTS_F_PK)
4364 USE_NL(pos pop as1 as2) */
4365 ac2.assignment_action_id,
4366 ac2.assignment_id,
4367 ac2.tax_unit_id,
4368 pa2.action_type
4369 from pay_payroll_actions pa,
4370 pay_population_ranges pop,
4371 per_periods_of_service pos,
4372 per_all_assignments_f as2,
4373 pay_assignment_actions ac2,
4374 pay_payroll_actions pa2,
4375 pay_action_classifications pcl,
4376 per_all_assignments_f as1
4377 where pa.payroll_action_id = pactid
4378 and pa2.consolidation_set_id = pa.consolidation_set_id
4379 and pa2.effective_date between
4380 pa.start_date and pa.effective_date
4381 and ac2.payroll_action_id = pa2.payroll_action_id
4382 and ac2.action_status = 'C'
4383 and pcl.classification_name = 'TRANSGL'
4384 and pa2.action_type = pcl.action_type
4385 and as2.assignment_id = ac2.assignment_id
4386 and pa.effective_date between
4387 as2.effective_start_date and as2.effective_end_date
4388 and as1.assignment_id = ac2.assignment_id
4389 and pa2.effective_date between
4390 as1.effective_start_date and as1.effective_end_date
4391 and nvl(decode(pa2.action_type, 'EC', nvl(pa.payroll_id, as1.payroll_id),
4392 'CP', nvl(pa.payroll_id, as1.payroll_id),
4393 as1.payroll_id), -999) = nvl(as1.payroll_id, -999)
4394 and pos.period_of_service_id = as2.period_of_service_id
4395 and pop.payroll_action_id = pactid
4396 and pop.chunk_number = chunk
4397 and pos.person_id = pop.person_id
4398 and not exists (
4399 select null
4400 from pay_assignment_actions ac3,
4401 pay_payroll_actions pa3,
4402 pay_action_interlocks in3
4403 where in3.locked_action_id = ac2.assignment_action_id
4404 and ac3.assignment_action_id = in3.locking_action_id
4405 and pa3.payroll_action_id = ac3.payroll_action_id
4406 and pa3.action_type = pa.action_type)
4407 and not exists (
4408 select /*+ ORDERED*/
4409 null
4410 from per_all_assignments_f as3,
4411 pay_assignment_actions ac3
4412 where itpflg = 'N'
4413 and ac3.payroll_action_id = pa2.payroll_action_id
4414 and ac3.action_status not in ('C','S')
4415 and as3.assignment_id = ac3.assignment_id
4416 and pa2.effective_date between
4417 as3.effective_start_date and as3.effective_end_date
4418 and as3.person_id = as1.person_id)
4419 order by ac2.assignment_id, ac2.payroll_action_id, ac2.assignment_action_id
4420 for update of as2.assignment_id, pos.period_of_service_id;
4421 --
4422 cursor costedacts
4423 (
4424 pactid number,
4425 costactid number
4426 ) is
4427 select ac1.assignment_action_id
4428 from pay_action_interlocks in2,
4429 pay_assignment_actions ac1,
4430 pay_payroll_actions pa1,
4431 pay_action_classifications pcl1,
4432 per_all_assignments_f as1,
4433 pay_payroll_actions pa
4434 where pa.payroll_action_id = pactid
4435 and in2.locking_action_id = costactid
4436 and ac1.assignment_action_id = in2.locked_action_id
4437 and ac1.source_action_id is null
4438 and pa1.payroll_action_id = ac1.payroll_action_id
4439 and pcl1.action_type = pa1.action_type
4440 and pcl1.classification_name = 'COSTED'
4441 and as1.assignment_id = ac1.assignment_id
4442 and (as1.payroll_id = pa.payroll_id or pa.payroll_id is null)
4443 and pa1.effective_date between
4444 as1.effective_start_date and as1.effective_end_date;
4445 --
4446 lockingactid number;
4447 lockedactid number;
4448 assignid number;
4449 actype pay_payroll_actions.action_type%TYPE;
4450 pmnt_act_type pay_payroll_actions.action_type%TYPE;
4451 prepay_aa_id number;
4452 runactid number;
4453 greid number;
4454 not_paid number;
4455 --
4456 begin
4457 if (g_many_procs_in_period = 'Y') then
4458 open tglmpipcur(pactid,chunk,itpflg);
4459 elsif (use_pop_person = 1) then
4460 open tglpopcur(pactid,chunk,itpflg);
4461 else
4462 open tglcur(pactid,stperson,endperson,itpflg);
4463 end if;
4464 loop
4465 if (g_many_procs_in_period = 'Y') then
4466 fetch tglmpipcur into lockedactid,assignid,greid,actype;
4467 exit when tglmpipcur%notfound;
4468 elsif (use_pop_person = 1) then
4469 fetch tglpopcur into lockedactid,assignid,greid,actype;
4470 exit when tglpopcur%notfound;
4471 else
4472 fetch tglcur into lockedactid,assignid,greid,actype;
4473 exit when tglcur%notfound;
4474 end if;
4475 --
4476 if (actype <> 'EC' and actype <> 'CP') then
4477
4478 -- For costings and Retrocostings we create an assignment
4479 -- action for each run action - and interlock it
4480 open costedacts(pactid,lockedactid);
4481 loop
4482 fetch costedacts into runactid;
4483 exit when costedacts%notfound;
4484 --
4485 --
4486 -- Get an assignment_action_id.
4487 select pay_assignment_actions_s.nextval
4488 into lockingactid
4489 from dual;
4490 --
4491 -- Insert an assignment action for each action.
4492 insact(lockingactid,assignid,pactid,rand_chunk,greid,null);
4493 --
4494 -- We follow this with the insert of two interlock
4495 -- rows. One interlock points to the Costing action
4496 -- and the other to the Payroll Run action that was
4497 -- costed in the first place.
4498 insint(lockingactid,lockedactid); -- lock to the Costing.
4499 insint(lockingactid,runactid); -- lock to original Payroll Run.
4500 end loop;
4501 close costedacts;
4502 else
4503 -- Estimate Costings and Payment Costings we're not interested
4504 -- in runs and don't
4505 -- interlock them
4506
4507 not_paid := 0;
4508
4509 -- if Payment Costing check that its from a Prepayment
4510 -- that hasn't had all pre payments paid
4511 if (actype = 'CP') then
4512
4513 select distinct(pa.action_type)
4514 into pmnt_act_type
4515 from pay_action_interlocks int,
4516 pay_assignment_actions aa,
4517 pay_payroll_actions pa
4518 where int.locking_action_id = lockedactid
4519 and aa.assignment_action_id = int.locked_action_id
4520 and pa.payroll_action_id = aa.payroll_action_id;
4521
4522 if (pmnt_act_type in ('P', 'U')) then
4523 -- Bug 6919216 - Fixed query to consider only payments
4524 -- that are costed and needed be to transferred to GL.
4525 select count(*)
4526 into not_paid
4527 from pay_action_interlocks int,
4528 pay_pre_payments ppp,
4529 pay_org_payment_methods_f opm,
4530 pay_assignment_actions paa,
4531 pay_payroll_actions ppa
4532 where int.locking_action_id = lockedactid
4533 and ppp.assignment_action_id = int.locked_action_id
4534 and paa.assignment_action_id = ppp.assignment_action_id
4535 and paa.payroll_action_id = ppa.payroll_action_id /* Bug 8619201 - Date eff. join */
4536 and ppa.effective_date between opm.effective_start_date and opm.effective_end_date
4537 and opm.org_payment_method_id = ppp.org_payment_method_id
4538 and opm.cost_payment = 'Y'
4539 and opm.transfer_to_gl_flag = 'Y'
4540 and not exists
4541 (select 1
4542 from pay_assignment_actions aa
4543 where aa.pre_payment_id = ppp.pre_payment_id);
4544
4545 end if;
4546
4547 end if;
4548
4549 if (not_paid = 0) then
4550
4551 -- Get an assignment_action_id.
4552 select pay_assignment_actions_s.nextval
4553 into lockingactid
4554 from dual;
4555 --
4556 -- Insert an assignment action for each action.
4557 insact(lockingactid,assignid,pactid,rand_chunk,greid,null);
4558 --
4559 -- We interlock the costing action
4560 insint(lockingactid,lockedactid); -- lock to the Costing.
4561
4562 end if;
4563 end if;
4564 end loop;
4565 if (g_many_procs_in_period = 'Y') then
4566 close tglmpipcur;
4567 elsif (use_pop_person = 1) then
4568 close tglpopcur;
4569 else
4570 close tglcur;
4571 end if;
4572 end proctgl;
4573 --
4574 ---------------------------------- proqpp ---------------------------------
4575 /*
4576 NAME
4577 proqpp - insert assignment actions for QuickPay prepayment
4578 DESCRIPTION
4579 Insert assignment actions for the QuickPay prepayment process
4580 NOTES
4581 An assignment action is inserted for the assignment which is specified
4582 on the target_payroll_action_id column of the Quick Pay action.
4583 When this is done the action population status is set to complete
4584 */
4585 procedure proqpp
4586 (
4587 pactid in number,
4588 lub in varchar2,
4589 lul in varchar2
4590 ) is
4591 cursor qpcur ( pactid number ) is
4592 select ac1.assignment_action_id,
4593 ac1.assignment_id,
4594 ac1.tax_unit_id,
4595 pa1.action_type
4596 from pay_assignment_actions ac1,
4597 pay_payroll_actions pa1
4598 where pa1.payroll_action_id = pactid
4599 and pa1.target_payroll_action_id = ac1.payroll_action_id
4600 and not exists (
4601 select 1
4602 from pay_assignment_actions ac2
4603 where ac2.payroll_action_id = pactid
4604 and ac2.assignment_id = ac1.assignment_id)
4605 for update of ac1.assignment_action_id ;
4606 --
4607 lockingactid number;
4608 lockedactid number;
4609 assignid number;
4610 greid number;
4611 atype pay_payroll_actions.action_type%type;
4612 --
4613 begin
4614 open qpcur(pactid);
4615 fetch qpcur into lockedactid, assignid, greid, atype;
4616 if qpcur%notfound then
4617 close qpcur ;
4618 return ;
4619 end if;
4620 close qpcur ;
4621 --
4622 -- Get an assignment_action_id.
4623 select pay_assignment_actions_s.nextval
4624 into lockingactid
4625 from dual;
4626 --
4627 -- Insert an assignment action for the action
4628 insact(lockingactid,assignid,pactid,1,greid);
4629 --
4630 -- Insert an interlock row to lock the QuickPay run assignment action
4631 insint(lockingactid,lockedactid);
4632 --
4633 -- Set the action population status to 'C' (complete)
4634 -- Also sets date_earned value.
4635 update_pact(pactid, 'C', atype, sysdate,lub,lul);
4636 --
4637 end proqpp ;
4638 --
4639 ---------------------------------- procarc --------------------------------
4640 /*
4641 NAME
4642 procarc - insert assignment actions for Archive process
4643 DESCRIPTION
4644 Insert assignment actions for the Archive process
4645 NOTES
4646 This dynamically calls legislative code to perform the insertion
4647 of the assignment actions, since it is the legislation that
4648 knows which assignments are to be included in the archive.
4649 */
4650 procedure procarc(pactid in number,
4651 stperson in number,
4652 endperson in number,
4653 chunk in number
4654 )
4655 is
4656 sql_cur number;
4657 ignore number;
4658 action_proc varchar2(60);
4659 statem varchar2(256);
4660 begin
4661 select assignment_action_code
4662 into action_proc
4663 from pay_report_format_mappings_f prfm,
4664 pay_payroll_actions ppa
4665 where ppa.payroll_action_id = pactid
4666 and ppa.report_type = prfm.report_type
4667 and ppa.report_qualifier = prfm.report_qualifier
4668 and ppa.report_category = prfm.report_category
4669 and ppa.effective_date between prfm.effective_start_date
4670 and prfm.effective_end_date;
4671 --
4672 statem := 'BEGIN '||action_proc||'(:pactid, :stperson,'||
4673 ' :endperson, :chunk); END;';
4674 --
4675 sql_cur := dbms_sql.open_cursor;
4676 dbms_sql.parse(sql_cur,
4677 statem,
4678 dbms_sql.v7);
4679 dbms_sql.bind_variable(sql_cur, ':pactid', pactid);
4680 dbms_sql.bind_variable(sql_cur, ':stperson', stperson);
4681 dbms_sql.bind_variable(sql_cur, ':endperson', endperson);
4682 dbms_sql.bind_variable(sql_cur, ':chunk', chunk);
4683 ignore := dbms_sql.execute(sql_cur);
4684 dbms_sql.close_cursor(sql_cur);
4685 --
4686 return;
4687 --
4688 exception
4689 when others then
4690 if (dbms_sql.is_open(sql_cur)) then
4691 dbms_sql.close_cursor(sql_cur);
4692 end if;
4693 raise;
4694 end procarc;
4695 --
4696 ---------------------------------- procpp ----------------------------------
4697 /*
4698 NAME
4699 procpp - process a single chunk for PP payment (Bank or Post Office payment)
4700 process.
4701 DESCRIPTION
4702 This function takes a range as defined by the starting and
4703 ending person_id and inserts a chunk of assignment actions
4704 plus their associated interlock rows. This function for the
4705 Bank or Post Office payment (PP) action only.
4706 NOTES
4707 <none>
4708 */
4709 procedure procpp
4710 (
4711 pactid in number, -- payroll_action_id.
4712 stperson in number, -- starting person_id of range.
4713 endperson in number, -- ending person_id of range.
4714 chunk in number, -- current chunk_number.
4715 rand_chunk in number, -- current chunk_number.
4716 itpflg in varchar2, -- legislation type.
4717 ptype in number, -- payment_type_id.
4718 use_pop_person in number -- use population_ranges person_id column
4719 ) is
4720 cursor pppopcur
4721 (
4722 pactid number,
4723 chunk number,
4724 itpflg varchar2,
4725 ptype number
4726 ) is
4727 select /*+ ORDERED
4728 INDEX(pa2 PAY_PAYROLL_ACTIONS_N5)
4729 INDEX(pos PER_PERIODS_OF_SERVICE_N3)
4730 INDEX(as1 PER_ASSIGNMENTS_N4)
4731 INDEX(as2 PER_ASSIGNMENTS_F_PK)
4732 INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
4733 index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
4734 USE_NL(pop pos ppp opm as1 act as2) */
4735 act.assignment_action_id,
4736 act.assignment_id,
4737 act.tax_unit_id,
4738 ppp.pre_payment_id
4739 from pay_payroll_actions pa1,
4740 pay_payroll_actions pa2,
4741 pay_action_classifications pcl,
4742 pay_population_ranges pop,
4743 per_periods_of_service pos,
4744 per_all_assignments_f as1,
4745 pay_assignment_actions act,
4746 per_all_assignments_f as2,
4747 pay_pre_payments ppp,
4748 pay_org_payment_methods_f opm
4749 where pa1.payroll_action_id = pactid
4750 and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
4751 and pa2.effective_date between
4752 pa1.start_date and pa1.effective_date
4753 and act.payroll_action_id = pa2.payroll_action_id
4754 and act.action_status = 'C'
4755 and pcl.classification_name = 'PPPAYMENT'
4756 and pa2.action_type = pcl.action_type
4757 and as1.assignment_id = act.assignment_id
4758 and pa2.effective_date between
4759 as1.effective_start_date and as1.effective_end_date
4760 and as2.assignment_id = act.assignment_id
4761 and pa1.effective_date between
4762 as2.effective_start_date and as2.effective_end_date
4763 and as2.payroll_id + 0 = as1.payroll_id + 0
4764 and pos.period_of_service_id = as1.period_of_service_id
4765 and pop.payroll_action_id = pactid
4766 and pop.chunk_number = chunk
4767 and pos.person_id = pop.person_id
4768 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
4769 and ppp.assignment_action_id = act.assignment_action_id
4770 and opm.org_payment_method_id = ppp.org_payment_method_id
4771 and pa1.effective_date between
4772 opm.effective_start_date and opm.effective_end_date
4773 and opm.payment_type_id +0 = ptype
4774 and (opm.org_payment_method_id = pa1.org_payment_method_id
4775 or pa1.org_payment_method_id is null)
4776 and not exists (
4777 select null
4778 from per_all_assignments_f as3,
4779 pay_assignment_actions ac3
4780 where itpflg = 'N'
4781 and ac3.payroll_action_id = pa2.payroll_action_id
4782 and ac3.action_status not in ('C', 'S')
4783 and as3.assignment_id = ac3.assignment_id
4784 and pa2.effective_date between
4785 as3.effective_start_date and as3.effective_end_date
4786 and as3.person_id = as2.person_id)
4787 and not exists (
4788 select /*+ ORDERED*/
4789 null
4790 from pay_action_interlocks int,
4791 pay_assignment_actions ac2
4792 where int.locked_action_id = act.assignment_action_id
4793 and ac2.assignment_action_id = int.locking_action_id
4794 and ac2.pre_payment_id = ppp.pre_payment_id
4795 and not exists (
4796 select null
4797 from pay_assignment_actions paa_void,
4798 pay_action_interlocks pai_void,
4799 pay_payroll_actions ppa_void
4800 where pai_void.locked_action_id = ac2.assignment_action_id
4801 and pai_void.locking_action_id = paa_void.assignment_action_id
4802 and paa_void.payroll_action_id = ppa_void.payroll_action_id
4803 and ppa_void.action_type = 'D')
4804 )
4805 order by act.assignment_id
4806 for update of as1.assignment_id, pos.period_of_service_id;
4807 --
4808 cursor ppcur
4809 (
4810 pactid number,
4811 stperson number,
4812 endperson number,
4813 itpflg varchar2,
4814 ptype number
4815 ) is
4816 select /*+ ORDERED
4817 INDEX(pa2 PAY_PAYROLL_ACTIONS_N5)
4818 INDEX(pos PER_PERIODS_OF_SERVICE_N3)
4819 INDEX(as1 PER_ASSIGNMENTS_N4)
4820 INDEX(as2 PER_ASSIGNMENTS_F_PK)
4821 INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
4822 index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
4823 USE_NL(pos ppp opm as1 act as2) */
4824 act.assignment_action_id,
4825 act.assignment_id,
4826 act.tax_unit_id,
4827 ppp.pre_payment_id
4828 from pay_payroll_actions pa1,
4829 pay_payroll_actions pa2,
4830 pay_action_classifications pcl,
4831 per_periods_of_service pos,
4832 per_all_assignments_f as1,
4833 pay_assignment_actions act,
4834 per_all_assignments_f as2,
4835 pay_pre_payments ppp,
4836 pay_org_payment_methods_f opm
4837 where pa1.payroll_action_id = pactid
4838 and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
4839 and pa2.effective_date between
4840 pa1.start_date and pa1.effective_date
4841 and act.payroll_action_id = pa2.payroll_action_id
4842 and act.action_status = 'C'
4843 and pcl.classification_name = 'PPPAYMENT'
4844 and pa2.action_type = pcl.action_type
4845 and as1.assignment_id = act.assignment_id
4846 and pa2.effective_date between
4847 as1.effective_start_date and as1.effective_end_date
4848 and as2.assignment_id = act.assignment_id
4849 and pa1.effective_date between
4850 as2.effective_start_date and as2.effective_end_date
4851 and as2.payroll_id + 0 = as1.payroll_id + 0
4852 and pos.period_of_service_id = as1.period_of_service_id
4853 and pos.person_id between stperson and endperson
4854 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
4855 and ppp.assignment_action_id = act.assignment_action_id
4856 and opm.org_payment_method_id = ppp.org_payment_method_id
4857 and pa1.effective_date between
4858 opm.effective_start_date and opm.effective_end_date
4859 and opm.payment_type_id +0 = ptype
4860 and (opm.org_payment_method_id = pa1.org_payment_method_id
4861 or pa1.org_payment_method_id is null)
4862 and not exists (
4863 select null
4864 from per_all_assignments_f as3,
4865 pay_assignment_actions ac3
4866 where itpflg = 'N'
4867 and ac3.payroll_action_id = pa2.payroll_action_id
4868 and ac3.action_status not in ('C', 'S')
4869 and as3.assignment_id = ac3.assignment_id
4870 and pa2.effective_date between
4871 as3.effective_start_date and as3.effective_end_date
4872 and as3.person_id = as2.person_id)
4873 and not exists (
4874 select /*+ ORDERED*/
4875 null
4876 from pay_action_interlocks int,
4877 pay_assignment_actions ac2
4878 where int.locked_action_id = act.assignment_action_id
4879 and ac2.assignment_action_id = int.locking_action_id
4880 and ac2.pre_payment_id = ppp.pre_payment_id
4881 and not exists (
4882 select null
4883 from pay_assignment_actions paa_void,
4884 pay_action_interlocks pai_void,
4885 pay_payroll_actions ppa_void
4886 where pai_void.locked_action_id = ac2.assignment_action_id
4887 and pai_void.locking_action_id = paa_void.assignment_action_id
4888 and paa_void.payroll_action_id = ppa_void.payroll_action_id
4889 and ppa_void.action_type = 'D')
4890 )
4891 order by act.assignment_id
4892 for update of as1.assignment_id, pos.period_of_service_id;
4893 --
4894 cursor ppmpipcur
4895 (
4896 pactid number,
4897 chunk number,
4898 itpflg varchar2,
4899 ptype number
4900 ) is
4901 select /*+ ORDERED
4902 INDEX(pa2 PAY_PAYROLL_ACTIONS_PK)
4903 INDEX(pos PER_PERIODS_OF_SERVICE_N3)
4904 INDEX(as1 PER_ASSIGNMENTS_N4)
4905 INDEX(as2 PER_ASSIGNMENTS_F_PK)
4906 INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
4907 index(opm PAY_ORG_PAYMENT_METHODS_F_PK)
4908 USE_NL(pos pop ppp opm as1 act as2) */
4909 act.assignment_action_id,
4910 act.assignment_id,
4911 act.tax_unit_id,
4912 ppp.pre_payment_id
4913 from pay_payroll_actions pa1,
4914 pay_population_ranges pop,
4915 per_periods_of_service pos,
4916 per_all_assignments_f as1,
4917 pay_assignment_actions act,
4918 pay_payroll_actions pa2,
4919 pay_action_classifications pcl,
4920 per_all_assignments_f as2,
4921 pay_pre_payments ppp,
4922 pay_org_payment_methods_f opm
4923 where pa1.payroll_action_id = pactid
4924 and pa2.consolidation_set_id +0 = pa1.consolidation_set_id
4925 and pa2.effective_date between
4926 pa1.start_date and pa1.effective_date
4927 and act.payroll_action_id = pa2.payroll_action_id
4928 and act.action_status = 'C'
4929 and pcl.classification_name = 'PPPAYMENT'
4930 and pa2.action_type = pcl.action_type
4931 and as1.assignment_id = act.assignment_id
4932 and pa1.effective_date between
4933 as1.effective_start_date and as1.effective_end_date
4934 and as2.assignment_id = act.assignment_id
4935 and pa2.effective_date between
4936 as2.effective_start_date and as2.effective_end_date
4937 and as2.payroll_id + 0 = as1.payroll_id + 0
4938 and pos.period_of_service_id = as1.period_of_service_id
4939 and pop.payroll_action_id = pactid
4940 and pop.chunk_number = chunk
4941 and pos.person_id = pop.person_id
4942 and (as1.payroll_id = pa1.payroll_id or pa1.payroll_id is null)
4943 and ppp.assignment_action_id = act.assignment_action_id
4944 and opm.org_payment_method_id = ppp.org_payment_method_id
4945 and pa1.effective_date between
4946 opm.effective_start_date and opm.effective_end_date
4947 and opm.payment_type_id +0 = ptype
4948 and (opm.org_payment_method_id = pa1.org_payment_method_id
4949 or pa1.org_payment_method_id is null)
4950 and not exists (
4951 select null
4952 from per_all_assignments_f as3,
4953 pay_assignment_actions ac3
4954 where itpflg = 'N'
4955 and ac3.payroll_action_id = pa2.payroll_action_id
4956 and ac3.action_status not in ('C', 'S')
4957 and as3.assignment_id = ac3.assignment_id
4958 and pa2.effective_date between
4959 as3.effective_start_date and as3.effective_end_date
4960 and as3.person_id = as2.person_id)
4961 and not exists (
4962 select /*+ ORDERED*/
4963 null
4964 from pay_action_interlocks int,
4965 pay_assignment_actions ac2
4966 where int.locked_action_id = act.assignment_action_id
4967 and ac2.assignment_action_id = int.locking_action_id
4968 and ac2.pre_payment_id = ppp.pre_payment_id
4969 and not exists (
4970 select null
4971 from pay_assignment_actions paa_void,
4972 pay_action_interlocks pai_void,
4973 pay_payroll_actions ppa_void
4974 where pai_void.locked_action_id = ac2.assignment_action_id
4975 and pai_void.locking_action_id = paa_void.assignment_action_id
4976 and paa_void.payroll_action_id = ppa_void.payroll_action_id
4977 and ppa_void.action_type = 'D')
4978 )
4979 order by act.assignment_id
4980 for update of as1.assignment_id, pos.period_of_service_id;
4981 --
4982 lockingactid number;
4983 lockedactid number;
4984 assignid number;
4985 prepayid number;
4986 greid number;
4987 --
4988 -- algorithm is quite similar to the other process cases,
4989 -- but we have to take into account assignments and
4990 -- personal payment methods.
4991 begin
4992 if (g_many_procs_in_period = 'Y') then
4993 open ppmpipcur(pactid,chunk,itpflg,ptype);
4994 elsif (use_pop_person = 1) then
4995 open pppopcur(pactid,chunk,itpflg,ptype);
4996 else
4997 open ppcur(pactid,stperson,endperson,itpflg,ptype);
4998 end if;
4999 loop
5000 if (g_many_procs_in_period = 'Y') then
5001 fetch ppmpipcur into lockedactid,assignid,greid,prepayid;
5002 exit when ppmpipcur%notfound;
5003 elsif (use_pop_person = 1) then
5004 fetch pppopcur into lockedactid,assignid,greid,prepayid;
5005 exit when pppopcur%notfound;
5006 else
5007 fetch ppcur into lockedactid,assignid,greid,prepayid;
5008 exit when ppcur%notfound;
5009 end if;
5010 --
5011 -- we need to insert one action for each of the
5012 -- rows that we return from the cursor (i.e. one
5013 -- for each assignment/pre-payment).
5014 select pay_assignment_actions_s.nextval
5015 into lockingactid
5016 from dual;
5017 --
5018 -- insert the action record.
5019 insact(lockingactid,assignid,pactid,rand_chunk,greid,prepayid);
5020 --
5021 -- insert an interlock to this action.
5022 insint(lockingactid,lockedactid);
5023 --
5024 end loop;
5025 if (g_many_procs_in_period = 'Y') then
5026 close ppmpipcur;
5027 elsif (use_pop_person = 1) then
5028 close pppopcur;
5029 else
5030 close ppcur;
5031 end if;
5032 commit;
5033 end procpp;
5034 ----------------------------------- asact ----------------------------------
5035 /*
5036 NAME
5037 asact - insert assignment actions and interlocks
5038 DESCRIPTION
5039 Overall control of the insertion of assignment actions
5040 and interlocks for the non run payroll actions.
5041 NOTES
5042 <none>
5043 */
5044 procedure asact
5045 (
5046 pactid in number, -- payroll_action_id
5047 atype in varchar2, -- action_type.
5048 itpflg in varchar2, -- independent time periods flag.
5049 ptype in number, -- payment_type_id.
5050 lub in varchar2, -- last_updated_by.
5051 lul in varchar2, -- last_update_login.
5052 use_pop_person in number -- use population_ranges person_id column
5053 ) is
5054 QPPREPAY constant varchar2(1) := 'U';
5055 PREPAY constant varchar2(1) := 'P';
5056 COSTING constant varchar2(1) := 'C';
5057 ESTCOSTING constant varchar2(2) := 'EC';
5058 PAYMCOSTING constant varchar2(2) := 'CP';
5059 TRANSGL constant varchar2(1) := 'T';
5060 MAGTAPE constant varchar2(1) := 'M';
5061 CASH constant varchar2(1) := 'A';
5062 CHEQUE constant varchar2(1) := 'H';
5063 ARCHIVE constant varchar2(1) := 'X';
5064 BEE constant varchar2(3) := 'BEE';
5065 PPPAYMENT constant varchar2(2) := 'PP';
5066 --
5067 l_found boolean;
5068 stperson number;
5069 endperson number;
5070 chunk number;
5071 rand_chunk number;
5072 multi_asg_fg pay_all_payrolls_f.multi_assignments_flag%type;
5073 l_use_pop_person number := use_pop_person;
5074 begin
5075 pay_core_utils.get_action_parameter('SET_DATE_EARNED',
5076 g_set_date_earned,
5077 l_found);
5078 if (l_found = FALSE) then
5079 g_set_date_earned := 'N';
5080 end if;
5081 --
5082 -- As quick pay only has a single assignment action process separately
5083 if (atype = QPPREPAY) then
5084 proqpp(pactid,lub,lul);
5085 commit ;
5086 return ;
5087 elsif (atype = PREPAY) then
5088 select nvl(multi_assignments_flag, 'N')
5089 into multi_asg_fg
5090 from pay_all_payrolls_f prl,
5091 pay_payroll_Actions pact
5092 where pact.payroll_action_id = pactid
5093 and prl.payroll_id = pact.payroll_id
5094 and pact.effective_date between prl.effective_start_date
5095 and prl.effective_end_date;
5096 end if;
5097 --
5098 -- find value of MANY_PROCS_IN_PERIOD pay_action_parameter
5099 if cached = FALSE THEN
5100 begin
5101 select parameter_value
5102 into g_many_procs_in_period
5103 from pay_action_parameters
5104 where parameter_name = 'MANY_PROCS_IN_PERIOD';
5105 exception
5106 when others then
5107 g_many_procs_in_period := 'N';
5108 end;
5109 begin
5110 select parameter_value
5111 into g_plsql_proc_insert
5112 from pay_action_parameters
5113 where parameter_name = 'PLSQL_PROC_INSERT';
5114 exception
5115 when others then
5116 g_plsql_proc_insert := 'Y';
5117 end;
5118 cached := TRUE;
5119 end if;
5120 --
5121 -- If a payment process AND PLSQL_PROC_INSERT
5122 -- enforce range_person_id (many_procs_in_period unless
5123 -- was disabled above)
5124 if (atype = MAGTAPE or atype = CHEQUE or
5125 atype = CASH or atype = PPPAYMENT) then
5126 if g_plsql_proc_insert = 'Y' then
5127 if g_many_procs_in_period = 'N' then
5128 l_use_pop_person := 1;
5129 else
5130 g_many_procs_in_period := 'Y';
5131 end if;
5132 end if;
5133 end if;
5134 --
5135 -- MANY_PROCS_IN_PERIOD is now used if RANGE_PERSON_ID is set
5136 -- and MANY_PROCS_IN_PERIOD was not set to N
5137 if (l_use_pop_person = 1 and
5138 g_many_procs_in_period <> 'N') then
5139 g_many_procs_in_period := 'Y';
5140 end if;
5141 --
5142 dbms_lock.allocate_unique(
5143 lockname => 'PAY_PAYROLL_ACTIONS_'||pactid,
5144 lockhandle => g_lckhandle);
5145 --
5146 loop
5147 -- start by processing the range row.
5148 rangerow(pactid,lub,lul,stperson,endperson,chunk,rand_chunk,atype);
5149 -- chunk begin null indicates end of processing.
5150 exit when chunk is null;
5151 --
5152 -- 'lock' the range row grabbed by updating is status.
5153 -- check to see if want to use randomised chnks or sequential
5154 --
5155 update pay_population_ranges rge
5156 set rge.range_status = 'P'
5157 where rge.payroll_action_id = pactid
5158 and rge.chunk_number = chunk;
5159 --
5160 commit;
5161 --
5162 begin
5163 if(atype = PREPAY) then
5164 proc_prepay(pactid,stperson,endperson,chunk,rand_chunk,'PREPAID',
5165 itpflg,multi_asg_fg,l_use_pop_person);
5166 elsif(atype = COSTING) then
5167 proc_costing(pactid,stperson,endperson,chunk,rand_chunk,'COSTED',
5168 itpflg,l_use_pop_person);
5169 elsif(atype = PAYMCOSTING) then
5170 proc_paymcosting(pactid,stperson,endperson,chunk,rand_chunk,'COSTEDPAYM',
5171 itpflg,l_use_pop_person);
5172 elsif(atype = ESTCOSTING) then
5173 proc_estcosts(pactid,stperson,endperson,chunk,rand_chunk,'COSTED',
5174 itpflg,l_use_pop_person);
5175 elsif(atype = TRANSGL) then
5176 proctgl(pactid,stperson,endperson,chunk,rand_chunk,itpflg,l_use_pop_person);
5177 elsif(atype = MAGTAPE) then
5178 procmag(pactid,stperson,endperson,chunk,rand_chunk,itpflg,ptype,
5179 l_use_pop_person);
5180 elsif(atype = CASH) then
5181 proccash(pactid,stperson,endperson,chunk,rand_chunk,itpflg,l_use_pop_person);
5182 elsif(atype = CHEQUE) then
5183 procchq(pactid,stperson,endperson,chunk,rand_chunk,itpflg,ptype,
5184 'CHEQUEWRITER',l_use_pop_person);
5185 elsif(atype = ARCHIVE) then
5186 procarc(pactid,stperson,endperson,chunk);
5187 elsif(atype = BEE) then
5188 procbee(pactid,stperson,endperson,chunk,rand_chunk,l_use_pop_person);
5189 elsif(atype = PPPAYMENT) then
5190 procpp(pactid,stperson,endperson,chunk,rand_chunk,itpflg,ptype,
5191 l_use_pop_person);
5192 elsif(atype = pay_proc_environment_pkg.PYG_AT_PRU) then
5193 procpru(pactid,
5194 stperson,
5195 endperson,
5196 chunk,
5197 rand_chunk,
5198 'P_ROLLEDUP',
5199 itpflg,
5200 l_use_pop_person
5201 );
5202 else
5203 -- unrecognised action type.
5204 hr_utility.set_message(801,'HR_UNRECOGNISED_ACTION_TYPE');
5205 hr_utility.raise_error;
5206 end if;
5207 --
5208 -- we have processed the range, so delete the row.
5209 delete from pay_population_ranges rge
5210 where rge.payroll_action_id = pactid
5211 and rge.chunk_number = chunk;
5212 --
5213 commit;
5214
5215 exception
5216 when others then
5217
5218 rollback;
5219 --
5220 -- set chunk to 'E'rrored
5221 update pay_population_ranges rge
5222 set rge.range_status = 'E'
5223 where rge.payroll_action_id = pactid
5224 and rge.chunk_number = chunk;
5225
5226 update_pact(pactid, 'E', itpflg,sysdate,stperson,endperson);
5227
5228 commit;
5229
5230 raise;
5231 --
5232 end;
5233 --
5234
5235 end loop;
5236 end asact;
5237 -----------------------------------------------------------------------------
5238 -- Name: ins_additional_asg_action
5239 -- Desc: Insert an assignment action to an already existing payroll action.
5240 -----------------------------------------------------------------------------
5241 Procedure ins_additional_asg_action(p_asg_id number default null
5242 ,p_pact_id number
5243 ,p_gre_id number default null
5244 ,p_object_id number default null
5245 ,p_object_type varchar2 default null
5246 )
5247 is
5248 cursor pact_details
5249 is
5250 select ppa.action_status
5251 , ppa.action_type
5252 , rfm.report_name
5253 from pay_payroll_actions ppa
5254 , pay_report_format_mappings_f rfm
5255 where ppa.payroll_action_id = p_pact_id
5256 and ppa.report_type = rfm.report_type(+)
5257 and ppa.report_qualifier = rfm.report_qualifier(+)
5258 and ppa.report_category = rfm.report_category(+);
5259 --
5260 cursor get_existing_person_chunk(p_ppa_id number
5261 ,p_paf_id number)
5262 is
5263 select paa.chunk_number
5264 from pay_assignment_actions paa
5265 , per_all_assignments_f paf
5266 , per_all_people_f ppf
5267 where paa.payroll_action_id = p_ppa_id
5268 and paa.assignment_id = p_paf_id
5269 and paa.assignment_id = paf.assignment_id
5270 and paf.person_id = ppf.person_id
5271 and rownum = 1;
5272 --
5273 -- This cursor returns the chunck number of the chunck with the least number
5274 -- of assignment actions in it. If there is more than one chunk all with the
5275 -- same min number of asg actions, then it will pick the min chunk number.
5276 --
5277 cursor get_min_chunk(p_ppa_id number)
5278 is
5279 select min(chunk_number)
5280 from (select chunk_number, count(assignment_action_id) ct
5281 from pay_assignment_actions
5282 where payroll_action_id = p_ppa_id
5283 group by chunk_number) v1
5284 where v1.ct = (select min(v2.ct) from (select count(assignment_action_id) ct
5285 from pay_assignment_actions
5286 where payroll_action_id = p_ppa_id
5287 group by chunk_number) v2);
5288 --
5289 l_act_status pay_payroll_actions.action_status%type;
5290 l_act_type pay_payroll_actions.action_type%type;
5291 l_rep_name pay_report_format_mappings_f.report_name%type;
5292 l_chunk pay_assignment_actions.chunk_number%type;
5293 l_asg_act_id pay_assignment_actions.assignment_action_id%type;
5294 --
5295 BEGIN
5296 --
5297 -- Determine whether new action can be inserted: 1. if payroll_action is
5298 -- still processing - error. 2. If it is an archive action and there is an
5299 -- associated Oracle Reports report - error. 3. Else insert action
5300 --
5301 open pact_details;
5302 fetch pact_details into l_act_status, l_act_type, l_rep_name;
5303 if pact_details%notfound then
5304 --
5305 close pact_details;
5306 hr_utility.set_message(801, 'PAY_33170_INVALID_PACT_ID');
5307 hr_utility.raise_error;
5308 --
5309 else
5310 close pact_details;
5311 if l_act_status = 'P' then
5312 --
5313 hr_utility.set_message(801, 'PAY_33171_PACT_PROCESSING');
5314 hr_utility.raise_error;
5315 elsif l_act_type = 'X' then
5316 --
5317 if l_rep_name is not null then
5318 --
5319 hr_utility.set_message(801, 'PAY_33172_ARCH_REPORT');
5320 hr_utility.raise_error;
5321 end if;
5322 end if;
5323 end if;
5324 --
5325 -- Determine what chunk number to give the new asg action
5326 --
5327 -- does this person already have a chunk?
5328 --
5329 open get_existing_person_chunk(p_pact_id, p_asg_id);
5330 fetch get_existing_person_chunk into l_chunk;
5331 if get_existing_person_chunk%found then
5332 --
5333 close get_existing_person_chunk;
5334 --
5335 -- insert action using l_chunk
5336 --
5337 else -- new person, so figure out smallest chunk
5338 --
5339 open get_min_chunk(p_pact_id);
5340 fetch get_min_chunk into l_chunk;
5341 if get_min_chunk%notfound then
5342 --
5343 close get_min_chunk;
5344 --
5345 else
5346 close get_min_chunk;
5347 end if;
5348 --
5349 end if;
5350 --
5351 select pay_assignment_actions_s.nextval
5352 into l_asg_act_id
5353 from dual;
5354 --
5355 -- insert the action
5356 --
5357 insert into pay_assignment_actions
5358 (assignment_action_id
5359 ,assignment_id
5360 ,payroll_action_id
5361 ,action_status
5362 ,chunk_number
5363 ,action_sequence
5364 ,pre_payment_id
5365 ,object_version_number
5366 ,tax_unit_id
5367 ,source_action_id
5368 ,object_id
5369 ,object_type
5370 ,start_date
5371 ,end_date
5372 )
5373 values
5374 (l_asg_act_id
5375 ,p_asg_id
5376 ,p_pact_id
5377 ,'U'
5378 ,l_chunk
5379 ,l_asg_act_id
5380 ,''
5381 ,1
5382 ,p_gre_id
5383 ,''
5384 ,p_object_id
5385 ,p_object_type
5386 ,''
5387 ,''
5388 );
5389 --
5390 END ins_additional_asg_action;
5391 -----------------------------------------------------------------------------
5392 end hr_nonrun_asact;
[Home] [Help]