[Home] [Help]
PACKAGE BODY: APPS.HR_RUNGEN
Source
1 package body hr_rungen as
2 /* $Header: pyrungen.pkb 115.12 2002/12/09 15:12:28 divicker ship $ */
3 --
4 TYPE number_tbl IS TABLE OF NUMBER INDEX BY binary_integer;
5 --
6 TYPE asg_sets_type IS RECORD
7 (
8 set_id number_tbl,
9 sz INTEGER
10 );
11 --
12 g_sepcheck_asg_sets asg_sets_type;
13 g_taxsep_asg_sets asg_sets_type;
14 --
15 PROCEDURE setup_taxsep_asg_sets(
16 p_process_name IN VARCHAR2,
17 p_primary_action_id IN NUMBER,
18 p_payact_earned_date IN VARCHAR2,
19 p_business_group_id IN NUMBER,
20 p_payroll_id IN NUMBER
21 )
22 IS
23 --
24 CURSOR get_asg_ids (p_pyrll_action_id NUMBER) IS
25 SELECT ASA.assignment_id
26 FROM pay_assignment_actions ASA
27 WHERE ASA.payroll_action_id = p_pyrll_action_id
28 AND ASA.action_status = 'C';
29 --
30 l_set_name VARCHAR2(80);
31 l_master_set_name VARCHAR2(80);
32 l_taxsep_count NUMBER;
33 l_dednproc_count NUMBER;
34 l_dp_nots_count NUMBER;
35 l_tsdp_count NUMBER;
36 l_ts_counter NUMBER;
37 l_ts_asg_set_id NUMBER;
38 --
39 BEGIN
40 -- Initialise cache
41 --
42 g_taxsep_asg_sets.sz := 0;
43 -- RUN or RETRY mode...
44 -- IF p_process_name = 'RUN' THEN
45 --
46 l_master_set_name := 'Run Gen '||p_primary_action_id||'_TSDP ASG Set_';
47 --
48 for asgrec in get_asg_ids(p_primary_action_id) loop
49 --
50 -- OK how many Additional Runs are needed?
51 --
52 -- G1188 (03-Aug-1994):
53 -- Need to look for entries where 'Tax Separately' = 'Y' AND
54 -- 'Separate Check' = 'N'.
55 -- Aaaah, this is what G1529 needs.
56 -- G1529 also needs to KNOW when a TaxSep = Y and SepCheck = N GTN is
57 -- being submitted - in order to set RUN_TYPE = 'TAXSEP' for proper
58 -- tax calculation by VERTEX; and also for proper setting of
59 -- consolidation set on these GTNs and submission of Pre-Payments
60 -- process for SepCheck GTNs only. See below.
61 --
62 SELECT COUNT(ELE.element_entry_id)
63 INTO l_taxsep_count
64 FROM pay_element_entries_f ELE,
65 pay_element_entry_values_f EEV,
66 pay_input_values_f IPV
67 WHERE ELE.assignment_id = asgrec.assignment_id
68 AND fnd_date.canonical_to_date(p_payact_earned_date)
69 BETWEEN ELE.effective_start_date
70 AND ELE.effective_end_date
71 AND EEV.element_entry_id = ELE.element_entry_id
72 AND NVL(EEV.screen_entry_value,'N') = 'Y'
73 AND fnd_date.canonical_to_date(p_payact_earned_date)
74 BETWEEN EEV.effective_start_date
75 AND EEV.effective_end_date
76 AND IPV.input_value_id = EEV.input_value_id
77 AND UPPER(IPV.name) = 'TAX SEPARATELY'
78 AND EXISTS (SELECT 'x'
79 FROM pay_element_entries_f ELE2,
80 pay_element_entry_values_f EEV2,
81 pay_input_values_f IPV2
82 WHERE ELE2.assignment_id
83 = asgrec.assignment_id
84 AND ELE2.element_entry_id
85 = ELE.element_entry_id
86 AND fnd_date.canonical_to_date(p_payact_earned_date)
87 BETWEEN ELE2.effective_start_date
88 AND ELE2.effective_end_date
89 AND EEV2.element_entry_id
90 = ELE2.element_entry_id
91 AND EEV2.screen_entry_value = 'N'
92 AND fnd_date.canonical_to_date(p_payact_earned_date)
93 BETWEEN EEV2.effective_start_date
94 AND EEV2.effective_end_date
95 AND IPV2.input_value_id
96 = EEV2.input_value_id
97 AND UPPER(IPV2.name)
98 = 'SEPARATE CHECK');
99 --
100 -- Also need to look for entries where 'Tax Separately' = 'N' AND
101 -- 'Separate Check' = 'N' AND 'Deduction Processing' is other
102 -- than 'A'll.
103 --
104 SELECT COUNT(ELE.element_entry_id)
105 INTO l_dednproc_count
106 FROM pay_element_entries_f ELE,
107 pay_element_entry_values_f EEV,
108 pay_input_values_f IPV
109 WHERE ELE.assignment_id = asgrec.assignment_id
110 AND fnd_date.canonical_to_date(p_payact_earned_date)
111 BETWEEN ELE.effective_start_date
112 AND ELE.effective_end_date
113 AND EEV.element_entry_id = ELE.element_entry_id
114 AND EEV.screen_entry_value <> 'A'
115 AND fnd_date.canonical_to_date(p_payact_earned_date)
116 BETWEEN EEV.effective_start_date
117 AND EEV.effective_end_date
118 AND IPV.input_value_id = EEV.input_value_id
119 AND UPPER(IPV.name) = 'DEDUCTION PROCESSING'
120 AND EXISTS (SELECT 'x'
121 FROM pay_element_entries_f ELE2,
122 pay_element_entry_values_f EEV2,
123 pay_input_values_f IPV2
124 WHERE ELE2.assignment_id = asgrec.assignment_id
125 AND ELE2.element_entry_id
126 = ELE.element_entry_id
127 AND fnd_date.canonical_to_date(p_payact_earned_date)
128 BETWEEN ELE2.effective_start_date
129 AND ELE2.effective_end_date
130 AND EEV2.element_entry_id
131 = ELE2.element_entry_id
132 AND EEV2.screen_entry_value = 'N'
133 AND fnd_date.canonical_to_date(p_payact_earned_date)
134 BETWEEN EEV2.effective_start_date
135 AND EEV2.effective_end_date
136 AND IPV2.input_value_id
137 = EEV2.input_value_id
138 AND UPPER(IPV2.name) = 'SEPARATE CHECK')
139 AND EXISTS (SELECT 'x'
140 FROM pay_element_entries_f ELE3,
141 pay_element_entry_values_f EEV3,
142 pay_input_values_f IPV3
143 WHERE ELE3.assignment_id = asgrec.assignment_id
144 AND ELE3.element_entry_id
145 = ELE.element_entry_id
146 AND fnd_date.canonical_to_date(p_payact_earned_date)
147 BETWEEN ELE3.effective_start_date
148 AND ELE3.effective_end_date
149 AND EEV3.element_entry_id
150 = ELE3.element_entry_id
151 AND EEV3.screen_entry_value = 'N'
152 AND fnd_date.canonical_to_date(p_payact_earned_date)
153 BETWEEN EEV3.effective_start_date
154 AND EEV3.effective_end_date
155 AND IPV3.input_value_id
156 = EEV3.input_value_id
157 AND UPPER(IPV3.name)
158 = 'TAX SEPARATELY');
159 --
160 -- Also need to look for entries where 'Tax Separately' does not exist
161 -- (ie. for Regular "Earnings" elements, AND 'Separate Check' = 'N' AND
162 -- 'Deduction Processing' is other than 'A'll.
163 --
164 SELECT COUNT(ELE.element_entry_id)
165 INTO l_dp_nots_count
166 FROM pay_element_entries_f ELE,
167 pay_element_entry_values_f EEV,
168 pay_input_values_f IPV
169 WHERE ELE.assignment_id = asgrec.assignment_id
170 AND fnd_date.canonical_to_date(p_payact_earned_date)
171 BETWEEN ELE.effective_start_date
172 AND ELE.effective_end_date
173 AND EEV.element_entry_id = ELE.element_entry_id
174 AND EEV.screen_entry_value <> 'A'
175 AND fnd_date.canonical_to_date(p_payact_earned_date)
176 BETWEEN EEV.effective_start_date
177 AND EEV.effective_end_date
178 AND IPV.input_value_id = EEV.input_value_id
179 AND UPPER(IPV.name) = 'DEDUCTION PROCESSING'
180 AND EXISTS (SELECT 'x'
181 FROM pay_element_entries_f ELE2,
182 pay_element_entry_values_f EEV2,
183 pay_input_values_f IPV2
184 WHERE ELE2.assignment_id = asgrec.assignment_id
185 AND ELE2.element_entry_id
186 = ELE.element_entry_id
187 AND fnd_date.canonical_to_date(p_payact_earned_date)
188 BETWEEN ELE2.effective_start_date
189 AND ELE2.effective_end_date
190 AND EEV2.element_entry_id
191 = ELE2.element_entry_id
192 AND EEV2.screen_entry_value = 'N'
193 AND fnd_date.canonical_to_date(p_payact_earned_date)
194 BETWEEN EEV2.effective_start_date
195 AND EEV2.effective_end_date
196 AND IPV2.input_value_id
197 = EEV2.input_value_id
198 AND UPPER(IPV2.name) = 'SEPARATE CHECK')
199 AND NOT EXISTS (SELECT 'x'
200 FROM pay_element_entries_f ELE3,
201 pay_element_links_f ELI3,
202 pay_input_values_f IPV3
203 WHERE ELE3.assignment_id = asgrec.assignment_id
204 AND ELE3.element_entry_id
205 = ELE.element_entry_id
206 AND fnd_date.canonical_to_date(p_payact_earned_date)
207 BETWEEN ELE3.effective_start_date
208 AND ELE3.effective_end_date
209 AND ELI3.element_link_id
210 = ELE3.element_link_id
211 AND fnd_date.canonical_to_date(p_payact_earned_date)
212 BETWEEN ELI3.effective_start_date
213 AND ELI3.effective_end_date
214 AND IPV3.element_type_id
215 = ELI3.element_type_id
216 AND UPPER(IPV3.name)
217 = 'TAX SEPARATELY');
218 --
219 l_tsdp_count := l_taxsep_count + l_dednproc_count + l_dp_nots_count;
220 --
221 if (l_tsdp_count <> 0) then
222 for l_ts_counter in 1..l_tsdp_count loop
223 if l_ts_counter > g_taxsep_asg_sets.sz then
224 --
225 -- OK we need to insert a new assignment set.
226 --
227 l_set_name := l_master_set_name||l_ts_counter;
228 --
229 SELECT hr_assignment_sets_s.nextval
230 INTO l_ts_asg_set_id
231 FROM sys.dual;
232 --
233 INSERT INTO hr_assignment_sets (
234 ASSIGNMENT_SET_ID
235 ,BUSINESS_GROUP_ID
236 ,PAYROLL_ID
237 ,ASSIGNMENT_SET_NAME)
238 VALUES ( l_ts_asg_set_id
239 ,p_business_group_id
240 ,p_payroll_id
241 ,l_set_name);
242 --
243 -- Set the entry in the Set cache
244 g_taxsep_asg_sets.sz := l_ts_counter;
245 g_taxsep_asg_sets.set_id(l_ts_counter)
246 := l_ts_asg_set_id;
247 end if;
248 --
249 -- Now add this assignment to the appropreate set.
250 --
251 INSERT INTO hr_assignment_set_amendments
252 (ASSIGNMENT_ID
253 ,ASSIGNMENT_SET_ID
254 ,INCLUDE_OR_EXCLUDE
255 )
256 VALUES
257 ( asgrec.assignment_id
258 ,g_taxsep_asg_sets.set_id(l_ts_counter)
259 ,'I'
260 );
261 end loop;
262 end if;
263 end loop;
264 -- END IF;
265 --
266 END setup_taxsep_asg_sets;
267 --
268 PROCEDURE setup_sepcheck_asg_sets(
269 p_process_name IN VARCHAR2,
270 p_primary_action_id IN NUMBER,
271 p_payact_earned_date IN VARCHAR2,
272 p_business_group_id IN NUMBER,
273 p_payroll_id IN NUMBER
274 )
275 IS
276 --
277 CURSOR get_asg_ids (p_pyrll_action_id NUMBER) IS
278 SELECT ASA.assignment_id
279 FROM pay_assignment_actions ASA
280 WHERE ASA.payroll_action_id = p_pyrll_action_id
281 AND ASA.action_status = 'C';
282 --
283 l_set_name VARCHAR2(80);
284 l_master_set_name VARCHAR2(80);
285 l_sc_count NUMBER;
286 l_sc_counter NUMBER;
287 l_sc_asg_set_id NUMBER;
288 --
289 BEGIN
290 -- Initialise cache
291 --
292 g_sepcheck_asg_sets.sz := 0;
293 -- RUN or RETRY mode...
294 -- IF p_process_name = 'RUN' THEN
295 --
296 l_master_set_name := 'Run Gen '||p_primary_action_id||' SC ASG Set_';
297 --
298 for asgrec in get_asg_ids(p_primary_action_id) loop
299 --
300 -- OK how many Separate Checks does this guy have?
301 --
302 --
303 SELECT COUNT(ELE.element_entry_id)
304 INTO l_sc_count
305 FROM pay_element_entries_f ELE,
306 pay_element_entry_values_f EEV,
307 pay_input_values_f IPV
308 WHERE ELE.assignment_id = asgrec.assignment_id
309 AND fnd_date.canonical_to_date(p_payact_earned_date)
310 BETWEEN ELE.effective_start_date
311 AND ELE.effective_end_date
312 AND EEV.element_entry_id = ELE.element_entry_id
313 AND NVL(EEV.screen_entry_value,'N') = 'Y'
314 AND fnd_date.canonical_to_date(p_payact_earned_date)
315 BETWEEN EEV.effective_start_date
316 AND EEV.effective_end_date
317 AND IPV.input_value_id = EEV.input_value_id
318 AND UPPER(IPV.name) = 'SEPARATE CHECK';
319 --
320 if (l_sc_count <> 0) then
321 for l_sc_counter in 1..l_sc_count loop
322 if l_sc_counter > g_sepcheck_asg_sets.sz then
323 --
324 -- OK we need to insert a new assignment set.
325 --
326 l_set_name := l_master_set_name||l_sc_counter;
327 --
328 SELECT hr_assignment_sets_s.nextval
329 INTO l_sc_asg_set_id
330 FROM sys.dual;
331 --
332 INSERT INTO hr_assignment_sets (
333 ASSIGNMENT_SET_ID
334 ,BUSINESS_GROUP_ID
335 ,PAYROLL_ID
336 ,ASSIGNMENT_SET_NAME)
337 VALUES ( l_sc_asg_set_id
338 ,p_business_group_id
339 ,p_payroll_id
340 ,l_set_name);
341 --
342 -- Set the entry in the Set cache
343 g_sepcheck_asg_sets.sz := l_sc_counter;
344 g_sepcheck_asg_sets.set_id(l_sc_counter)
345 := l_sc_asg_set_id;
346 end if;
347 --
348 -- Now add this assignment to the appropreate set.
349 --
350 INSERT INTO hr_assignment_set_amendments
351 (ASSIGNMENT_ID
352 ,ASSIGNMENT_SET_ID
353 ,INCLUDE_OR_EXCLUDE
354 )
355 VALUES
356 ( asgrec.assignment_id
357 ,g_sepcheck_asg_sets.set_id(l_sc_counter)
358 ,'I'
359 );
360 end loop;
361 end if;
362 end loop;
363 -- END IF;
364 --
365 END setup_sepcheck_asg_sets;
366 --
367 procedure perform_run (p_payroll_id in number,
368 p_consolidation_set_id in number,
369 p_earned_date in varchar2,
370 p_date_paid in varchar2,
371 p_ele_set_id in number,
372 p_assignment_set_id in number,
373 p_leg_params in varchar2,
374 p_req_id in out nocopy number,
375 p_success out nocopy boolean,
376 errbuf out nocopy varchar2)
377 --
378 is
379 l_wait_outcome BOOLEAN;
380 l_phase VARCHAR2(80);
381 l_status VARCHAR2(80);
382 l_dev_phase VARCHAR2(80);
383 l_dev_status VARCHAR2(80);
384 l_message VARCHAR2(80);
385 l_errbuf VARCHAR2(240);
386 --
387 begin
388
389 p_req_id := fnd_request.submit_request(
390 application => 'PAY',
391 program => 'PAYROLL_RUN_GENERIC',
392 argument1 => 'RUN',
393 argument2 => p_payroll_id,
394 argument3 => p_consolidation_set_id,
395 argument4 => p_earned_date,
396 argument5 => p_date_paid,
397 argument6 => p_ele_set_id,
398 argument7 => p_assignment_set_id,
399 argument8 => p_leg_params);
400
401 IF p_req_id = 0 THEN
402 p_success := FALSE;
403 fnd_message.retrieve(l_errbuf);
404 hr_utility.trace('Error when submitting request: ' || SQLERRM || ' ' || SQLCODE);
405 raise zero_req_id;
406 ELSE
407 --
408 COMMIT;
409 --
410 l_wait_outcome := FND_CONCURRENT.WAIT_FOR_REQUEST(
411 request_id => p_req_id,
412 interval => 2,
413 phase => l_phase,
414 status => l_status,
415 dev_phase => l_dev_phase,
416 dev_status => l_dev_status,
417 message => l_message);
418 --
419 p_success := TRUE;
420 END IF;
421
422 errbuf := l_errbuf;
423
424 exception
425 when zero_req_id then
426 raise;
427 when others then
428 p_success := FALSE;
429 l_errbuf := SQLERRM;
430 errbuf := l_errbuf;
431 hr_utility.trace('Error when submitting request: ' || SQLERRM || ' ' || SQLCODE);
432
433 end perform_run;
434 --
435 procedure perform_retry (p_payroll_act_id in number,
436 p_req_id in out nocopy number,
437 p_success out nocopy boolean,
438 errbuf out nocopy varchar2)
439 --
440 is
441 l_wait_outcome BOOLEAN;
442 l_phase VARCHAR2(80);
443 l_status VARCHAR2(80);
444 l_dev_phase VARCHAR2(80);
445 l_dev_status VARCHAR2(80);
446 l_message VARCHAR2(80);
447 l_errbuf VARCHAR2(240);
448 --
449 begin
450 declare
451 dummy number;
452 begin
453 --
454 select 1
455 into dummy
456 from sys.dual
457 where exists (select ''
458 from pay_assignment_actions
459 where payroll_action_id = p_payroll_act_id
460 and action_status <> 'C');
461 --
462 p_req_id := fnd_request.submit_request(
463 application => 'PAY',
464 program => 'RETRY-RUN',
465 argument1 => 'RERUN',
466 argument2 => p_payroll_act_id
467 );
468 IF p_req_id = 0 THEN
469 p_success := FALSE;
470 fnd_message.retrieve(l_errbuf);
471 raise zero_req_id;
472 ELSE
473 --
474 COMMIT;
475 l_wait_outcome := FND_CONCURRENT.WAIT_FOR_REQUEST(
476 request_id => p_req_id,
477 interval => 2,
478 phase => l_phase,
479 status => l_status,
480 dev_phase => l_dev_phase,
481 dev_status => l_dev_status,
482 message => l_message);
483 --
484 p_success := TRUE;
485 END IF;
486 --
487 errbuf := l_errbuf;
488
489 exception
490 when no_data_found then
491 hr_utility.trace('No non-completed assignment actions for this payroll action');
492 when zero_req_id then
493 raise;
494 when others then
495 p_success := FALSE;
496 l_errbuf := SQLERRM;
497 errbuf := l_errbuf;
498 hr_utility.trace('Error when submitting request: ' || SQLERRM || ' ' || SQLCODE);
499 end;
500
501 end perform_retry;
502 --
503 procedure perform_prepay (p_payroll_id in number,
504 p_consolidation_set_id in number,
505 p_date_paid in varchar2,
506 p_req_id in out nocopy number,
507 p_success out nocopy boolean,
508 errbuf out nocopy varchar2)
509 --
510 is
511 l_wait_outcome BOOLEAN;
512 l_phase VARCHAR2(80);
513 l_status VARCHAR2(80);
514 l_dev_phase VARCHAR2(80);
515 l_dev_status VARCHAR2(80);
516 l_message VARCHAR2(80);
517 l_errbuf VARCHAR2(240);
518 --
519 begin
520 p_req_id := fnd_request.submit_request(
521 application => 'PAY',
522 program => 'PREPAY',
523 argument1 => 'PREPAY',
524 argument2 => p_payroll_id,
525 argument3 => p_consolidation_set_id,
526 argument4 => p_date_paid,
527 argument5 => p_date_paid,
528 argument6 => NULL);
529 IF p_req_id = 0 THEN
530 p_success := FALSE;
531 fnd_message.retrieve(l_errbuf);
532 raise zero_req_id;
533 ELSE
534 --
535 COMMIT;
536 l_wait_outcome := FND_CONCURRENT.WAIT_FOR_REQUEST(
537 request_id => p_req_id,
538 interval => 2,
539 phase => l_phase,
540 status => l_status,
541 dev_phase => l_dev_phase,
542 dev_status => l_dev_status,
543 message => l_message);
544 --
545 p_success := TRUE;
546 END IF;
547
548 errbuf := l_errbuf;
549
550 exception
551 when zero_req_id then
552 raise;
553 when others then
554 p_success := FALSE;
555 l_errbuf := SQLERRM;
556 errbuf := l_errbuf;
557 hr_utility.trace('Error when submitting request: ' || SQLERRM || ' ' || SQLCODE);
558
559 end perform_prepay;
560 --
561 procedure delete_sepcheck_asg_set
562 is
563 l_num_sepchecks number;
564 l_sc_counter number;
565 begin
566 --
567 l_num_sepchecks := g_sepcheck_asg_sets.sz;
568 --
569 for l_sc_counter in 1..l_num_sepchecks loop
570 --
571 delete from hr_assignment_set_amendments
572 where ASSIGNMENT_SET_ID = g_sepcheck_asg_sets.set_id(l_sc_counter);
573 --
574 delete from hr_assignment_sets
575 where ASSIGNMENT_SET_ID = g_sepcheck_asg_sets.set_id(l_sc_counter);
576 --
577 end loop;
578 end delete_sepcheck_asg_set;
579 --
580 procedure delete_taxsep_asg_set
581 is
582 l_num_taxsep number;
583 l_ts_counter number;
584 begin
585 --
586 l_num_taxsep := g_taxsep_asg_sets.sz;
587 --
588 for l_ts_counter in 1..l_num_taxsep loop
589 --
590 delete from hr_assignment_set_amendments
591 where ASSIGNMENT_SET_ID = g_taxsep_asg_sets.set_id(l_ts_counter);
592 --
593 delete from hr_assignment_sets
594 where ASSIGNMENT_SET_ID = g_taxsep_asg_sets.set_id(l_ts_counter);
595 --
596 end loop;
597 end delete_taxsep_asg_set;
598 --
599 procedure retry_taxsep(
600 p_primary_action_id IN NUMBER,
601 p_payroll_id IN NUMBER,
602 p_master_con_set_id IN NUMBER,
603 p_date_paid IN VARCHAR2,
604 p_runs_reprocessed IN OUT NOCOPY NUMBER
605 )
606 is
607
608 l_errbuf varchar2(240);
609 --
610 cursor retactions is
611 select payroll_action_id
612 from pay_payroll_actions
613 where target_payroll_action_id = p_primary_action_id
614 and payroll_id = p_payroll_id
615 and action_type = 'R'
616 and effective_date = fnd_date.canonical_to_date(p_date_paid)
617 and legislative_parameters like '%SPECIALPROC%'
618 order by action_sequence;
619 --
620 l_req_id number;
621 l_success boolean;
622 --
623 begin
624 --
625 for actrec in retactions loop
626 --
627 p_runs_reprocessed := p_runs_reprocessed + 1;
628 --
629 perform_retry(actrec.payroll_action_id,
630 l_req_id,
631 l_success,
632 l_errbuf);
633 --
634 end loop;
635 --
636 end retry_taxsep;
637 --
638 procedure retry_sepcheck(
639 p_primary_action_id IN NUMBER,
640 p_payroll_id IN NUMBER,
641 p_master_con_set_id IN NUMBER,
642 p_sc_con_set_id IN NUMBER,
643 p_date_paid IN VARCHAR2,
644 p_runs_reprocessed IN OUT NOCOPY NUMBER
645 )
646 is
647 --
648 cursor retactions is
649 select payroll_action_id
650 from pay_payroll_actions
651 where target_payroll_action_id = p_primary_action_id
652 and payroll_id = p_payroll_id
653 and action_type = 'R'
654 and effective_date = fnd_date.canonical_to_date(p_date_paid)
655 and legislative_parameters like '%SEPCHECK%'
656 order by action_sequence;
657 --
658 l_req_id number;
659 l_success boolean;
660 l_prepay_id number;
661 l_sc_preact_id number;
662 l_errbuf varchar2(240);
663 --
664 begin
665 --
666 for actrec in retactions loop
667 --
668 p_runs_reprocessed := p_runs_reprocessed + 1;
669 --
670 perform_retry(actrec.payroll_action_id,
671 l_req_id,
672 l_success,
673 l_errbuf);
674 --
675 -- OK now deal with the pre payment
676 begin
677 --
678 select distinct asg2.payroll_action_id
679 into l_prepay_id
680 from pay_assignment_actions asg2,
681 pay_action_interlocks pai,
682 pay_payroll_actions ppa,
683 pay_assignment_actions asg1
684 where asg1.payroll_action_id = actrec.payroll_action_id
685 and asg1.assignment_action_id = pai.locked_action_id
686 and asg2.assignment_action_id = pai.locking_action_id
687 and ppa.payroll_action_id = asg2.payroll_action_id
688 and ppa.action_type = 'P';
689 --
690 perform_retry(l_prepay_id,
691 l_req_id,
692 l_success,
693 l_errbuf);
694 --
695 exception
696 when no_data_found then
697 --
698 -- Wow, the prepayment has been rolled back.
699 --
700 update pay_payroll_actions
701 set consolidation_set_id = p_sc_con_set_id
702 where payroll_action_id = actrec.payroll_action_id;
703 --
704 commit;
705 --
706 perform_prepay (p_payroll_id,
707 p_sc_con_set_id,
708 p_date_paid,
709 l_req_id,
710 l_success,
711 l_errbuf);
712 --
713 SELECT payroll_action_id
714 INTO l_sc_preact_id
715 FROM pay_payroll_actions
716 WHERE request_id = l_req_id
717 AND payroll_id = p_payroll_id
718 AND action_type = 'P'
719 AND effective_date = fnd_date.canonical_to_date(p_date_paid);
720 --
721 update pay_payroll_actions
722 set consolidation_set_id = p_master_con_set_id
723 where payroll_action_id = actrec.payroll_action_id;
724 --
725 update pay_payroll_actions
726 set consolidation_set_id = p_master_con_set_id,
727 target_payroll_action_id = p_primary_action_id
728 where payroll_action_id = l_sc_preact_id;
729 --
730 commit;
731 --
732 end;
733 --
734 end loop;
735 --
736 end retry_sepcheck;
737 --
738 PROCEDURE do_sep_check(
739 p_process_name IN VARCHAR2,
740 p_primary_action_id IN NUMBER,
741 p_payroll_id IN NUMBER,
742 p_consolidation_set_id IN NUMBER,
743 p_earned_date IN VARCHAR2,
744 p_date_paid IN VARCHAR2,
745 p_assignment_set_id IN NUMBER,
746 p_ele_set_id IN NUMBER,
747 p_leg_params IN VARCHAR2,
748 p_business_group_id IN NUMBER,
749 p_pay_advice_message IN VARCHAR2)
750 IS
751 l_num_sepchecks number;
752 l_sc_counter number;
753 l_req_id number;
754 l_success boolean;
755 l_sc_consoset_name VARCHAR2(60);
756 l_sc_consoset_id number;
757 l_sc_payact_id number;
758 l_sc_preact_id number;
759 l_reprocessed_runs number;
760 l_errbuf varchar2(240);
761 BEGIN
762 setup_sepcheck_asg_sets (
763 p_process_name,
764 p_primary_action_id,
765 p_earned_date,
766 p_business_group_id,
767 p_payroll_id
768 );
769 --
770 l_num_sepchecks := g_sepcheck_asg_sets.sz;
771 --
772 if (l_num_sepchecks <> 0) then
773 --
774 -- Create new consolidation set
775 --
776 l_sc_consoset_name := 'Separate Check Consolidation';
777 --
778 SELECT pay_consolidation_sets_s.nextval
779 INTO l_sc_consoset_id
780 FROM sys.dual;
781 --
782 INSERT INTO pay_consolidation_sets (
783 CONSOLIDATION_SET_ID,
784 BUSINESS_GROUP_ID,
785 CONSOLIDATION_SET_NAME)
786 VALUES ( l_sc_consoset_id,
787 p_business_group_id,
788 l_sc_consoset_name);
789 --
790 commit;
791 --
792 -- RUN or RETRY mode...
793 --
794 l_reprocessed_runs := 1;
795 --
796 if p_process_name = 'RERUN' then
797 --
798 retry_sepcheck(
799 p_primary_action_id,
800 p_payroll_id,
801 p_consolidation_set_id,
802 l_sc_consoset_id,
803 p_date_paid,
804 l_reprocessed_runs
805 );
806 --
807 end if;
808 --
809 -- Now do the payroll runs
810 --
811 for l_sc_counter in l_reprocessed_runs..l_num_sepchecks loop
812 perform_run (p_payroll_id,
813 l_sc_consoset_id,
814 p_earned_date,
815 p_date_paid,
816 p_ele_set_id,
817 g_sepcheck_asg_sets.set_id(l_sc_counter),
818 p_leg_params,
819 l_req_id,
820 l_success,
821 l_errbuf);
822 --
823 SELECT payroll_action_id
824 INTO l_sc_payact_id
825 FROM pay_payroll_actions
826 WHERE request_id = l_req_id
827 AND payroll_id = p_payroll_id
828 AND action_type = 'R'
829 AND effective_date = fnd_date.canonical_to_date(p_date_paid);
830 --
831 perform_prepay (p_payroll_id,
832 l_sc_consoset_id,
833 p_date_paid,
834 l_req_id,
835 l_success,
836 l_errbuf);
837 --
838 SELECT payroll_action_id
839 INTO l_sc_preact_id
840 FROM pay_payroll_actions
841 WHERE request_id = l_req_id
842 AND payroll_id = p_payroll_id
843 AND action_type = 'P'
844 AND effective_date = fnd_date.canonical_to_date(p_date_paid);
845 --
846 -- Update the actions with the new details.
847 UPDATE pay_payroll_actions
848 SET consolidation_set_id = p_consolidation_set_id,
849 assignment_set_id = NULL,
850 target_payroll_action_id = p_primary_action_id
851 WHERE payroll_action_id = l_sc_payact_id;
852 --
853 UPDATE pay_payroll_actions
854 SET consolidation_set_id = p_consolidation_set_id,
855 target_payroll_action_id = p_primary_action_id
856 WHERE payroll_action_id = l_sc_preact_id;
857 --
858 commit;
859 --
860 end loop;
861 --
862 DELETE from pay_consolidation_sets
863 WHERE consolidation_set_id = l_sc_preact_id;
864 --
865 delete_sepcheck_asg_set;
866 --
867 COMMIT;
868 --
869 end if;
870 END do_sep_check;
871 --
872 PROCEDURE do_tax_sep(
873 p_process_name IN VARCHAR2,
874 p_primary_action_id IN NUMBER,
875 p_payroll_id IN NUMBER,
876 p_consolidation_set_id IN NUMBER,
877 p_earned_date IN VARCHAR2,
878 p_date_paid IN VARCHAR2,
879 p_assignment_set_id IN NUMBER,
880 p_ele_set_id IN NUMBER,
881 p_leg_params IN VARCHAR2,
882 p_business_group_id IN NUMBER,
883 p_pay_advice_message IN VARCHAR2)
884 IS
885 l_req_id number;
886 l_success boolean;
887 l_ts_payact_id number;
888 l_num_taxsep number;
889 l_reprocessed_runs number;
890 l_errbuf varchar2(240);
891 BEGIN
892 setup_taxsep_asg_sets (
893 p_process_name,
894 p_primary_action_id,
895 p_earned_date,
896 p_business_group_id,
897 p_payroll_id
898 );
899 --
900 l_num_taxsep := g_taxsep_asg_sets.sz;
901 --
902 commit;
903 --
904 if (l_num_taxsep <> 0) then
905 --
906 l_reprocessed_runs := 1;
907 --
908 if p_process_name = 'RERUN' then
909 --
910 retry_taxsep(
911 p_primary_action_id,
912 p_payroll_id,
913 p_consolidation_set_id,
914 p_date_paid,
915 l_reprocessed_runs
916 );
917 --
918 end if;
919 --
920 -- Now do the payroll runs
921 --
922 for l_ts_counter in l_reprocessed_runs..l_num_taxsep loop
923 perform_run (p_payroll_id,
924 p_consolidation_set_id,
925 p_earned_date,
926 p_date_paid,
927 p_ele_set_id,
928 g_taxsep_asg_sets.set_id(l_ts_counter),
929 p_leg_params,
930 l_req_id,
931 l_success,
932 l_errbuf);
933 --
934 --
935 SELECT payroll_action_id
936 INTO l_ts_payact_id
937 FROM pay_payroll_actions
938 WHERE request_id = l_req_id
939 AND payroll_id = p_payroll_id
940 AND action_type = 'R'
941 AND effective_date = fnd_date.canonical_to_date(p_date_paid);
942 --
943 -- Update the actions with the new details.
944 UPDATE pay_payroll_actions
945 SET assignment_set_id = NULL,
946 target_payroll_action_id = p_primary_action_id
947 WHERE payroll_action_id = l_ts_payact_id;
948 --
949 commit;
950 --
951 end loop;
952 --
953 delete_taxsep_asg_set;
954 --
955 COMMIT;
956 --
957 end if;
958 END do_tax_sep;
959 --
960 PROCEDURE generate_runs (
961 ERRBUF OUT NOCOPY VARCHAR2,
962 RETCODE OUT NOCOPY NUMBER,
963 p_process_name IN VARCHAR2 default 'RUN',
964 p_pay_action_id IN NUMBER default NULL,
965 p_payroll_id IN NUMBER default NULL,
966 p_consolidation_set_id IN NUMBER default NULL,
967 p_earned_date IN VARCHAR2 default NULL,
968 p_date_paid IN VARCHAR2 default NULL,
969 p_assignment_set_id IN NUMBER default NULL,
970 p_ele_set_id IN NUMBER default NULL,
971 p_leg_params IN VARCHAR2 default 'R',
972 p_pay_advice_message IN VARCHAR2 default NULL)
973 IS
974 l_leg_params VARCHAR2(240);
975 l_business_group_id NUMBER;
976 l_payact_id NUMBER;
977 l_req_id NUMBER;
978 l_success BOOLEAN;
979 l_primary_action NUMBER;
980 l_payroll_id NUMBER;
981 l_consolidation_set_id NUMBER;
982 l_earned_date VARCHAR2(20);
983 l_date_paid VARCHAR2(20);
984 l_assignment_set_id NUMBER;
985 l_ele_set_id NUMBER;
986 l_errbuf VARCHAR2(240);
987 --
988 BEGIN
989 --
990 l_errbuf := '';
991 fnd_message.set_name('PAY', 'HR_9999_ZERO_REQUEST_ID');
992
993 -- RUN or RETRY mode...
994 IF p_process_name = 'RUN' THEN
995 --
996 -- Get processing period dates for primary GTN:
997 --
998 IF p_leg_params IS NULL THEN
999 l_leg_params := 'R';
1000 ELSE
1001 l_leg_params := substr(p_leg_params,1,1);
1002 END IF;
1003 --
1004 SELECT DISTINCT business_group_id
1005 INTO l_business_group_id
1006 FROM pay_payrolls_f
1007 WHERE payroll_id = p_payroll_id;
1008 --
1009 -- Submit primary GTN:
1010 --
1011 hr_utility.set_location('hr_rungen - SUBMITTING PRIMARY GTN', 11);
1012 hr_utility.set_location('earned date = '||p_earned_date, 11);
1013 hr_utility.set_location('paid date = '||p_date_paid, 11);
1014 --
1015 perform_run (p_payroll_id,
1016 p_consolidation_set_id,
1017 p_earned_date,
1018 p_date_paid,
1019 p_ele_set_id,
1020 p_assignment_set_id,
1021 l_leg_params,
1022 l_req_id,
1023 l_success,
1024 l_errbuf);
1025 --
1026 -- Need to get payroll_action_id of primary GTN just submitted.
1027 --
1028 SELECT payroll_action_id
1029 INTO l_payact_id
1030 FROM pay_payroll_actions
1031 WHERE request_id = l_req_id
1032 AND payroll_id = p_payroll_id
1033 AND action_type = 'R'
1034 AND effective_date = fnd_date.canonical_to_date(p_date_paid);
1035 --
1036 l_primary_action := l_payact_id;
1037 --
1038 -- Update payroll action with pay advice message.
1039 IF p_pay_advice_message IS NOT NULL THEN
1040 --
1041 UPDATE pay_payroll_actions
1042 SET pay_advice_message = p_pay_advice_message
1043 WHERE payroll_action_id = l_payact_id;
1044 --
1045 END IF;
1046 --
1047 l_payroll_id := p_payroll_id;
1048 l_consolidation_set_id := p_consolidation_set_id;
1049 l_earned_date := p_earned_date;
1050 l_date_paid := p_date_paid;
1051 l_assignment_set_id := p_assignment_set_id;
1052 l_ele_set_id := p_ele_set_id;
1053 --
1054 ELSE -- Retry
1055 l_primary_action := p_pay_action_id;
1056 --
1057 select payroll_id,
1058 consolidation_set_id,
1059 fnd_date.canonical_to_date(date_earned),
1060 fnd_date.canonical_to_date(effective_date),
1061 assignment_set_id,
1062 element_set_id,
1063 business_group_id
1064 into l_payroll_id,
1065 l_consolidation_set_id,
1066 l_earned_date,
1067 l_date_paid,
1068 l_assignment_set_id,
1069 l_ele_set_id,
1070 l_business_group_id
1071 from pay_payroll_actions
1072 where payroll_action_id = l_primary_action;
1073 --
1074 perform_retry(l_primary_action,
1075 l_req_id,
1076 l_success,
1077 l_errbuf);
1078
1079 END IF;
1080 --
1081 l_leg_params := 'SEPCHECK';
1082 --
1083 do_sep_check(
1084 p_process_name,
1085 l_primary_action,
1086 l_payroll_id,
1087 l_consolidation_set_id,
1088 l_earned_date,
1089 l_date_paid,
1090 l_assignment_set_id,
1091 l_ele_set_id,
1092 l_leg_params,
1093 l_business_group_id,
1094 p_pay_advice_message);
1095 --
1096 l_leg_params := 'SPECIALPROC';
1097 --
1098 do_tax_sep(
1099 p_process_name,
1100 l_primary_action,
1101 l_payroll_id,
1102 l_consolidation_set_id,
1103 l_earned_date,
1104 l_date_paid,
1105 l_assignment_set_id,
1106 l_ele_set_id,
1107 l_leg_params,
1108 l_business_group_id,
1109 p_pay_advice_message);
1110
1111 errbuf := l_errbuf;
1112
1113 EXCEPTION
1114 WHEN zero_req_id THEN
1115 hr_utility.set_location('hr_rungen - No req ID', 100);
1116 errbuf := l_errbuf;
1117 fnd_message.raise_error;
1118 WHEN OTHERS THEN
1119 NULL;
1120 --
1121 --
1122 END generate_runs;
1123 --
1124 PROCEDURE Del_Asg_Amends (p_assignment_set_id IN NUMBER) IS
1125 --
1126 BEGIN
1127 --
1128 hr_utility.set_location('hr_rungen.Del_Asg_Amends', 7);
1129 /*
1130 UPDATE pay_payroll_actions
1131 SET assignment_set_id = NULL
1132 WHERE assignment_set_id = p_assignment_set_id;
1133 */
1134 --
1135 hr_utility.set_location('hr_rungen.Del_Asg_Amends', 11);
1136 DELETE FROM hr_assignment_set_amendments
1137 WHERE assignment_set_id = p_assignment_set_id;
1138 --
1139 EXCEPTION WHEN NO_DATA_FOUND THEN
1140 NULL;
1141 --
1142 END Del_Asg_Amends;
1143 --
1144 END hr_rungen;