[Home] [Help]
PACKAGE BODY: APPS.PAY_POPULATION_RANGES_PKG
Source
1 package body pay_population_ranges_pkg as
2 /* $Header: pycoppr.pkb 120.6.12010000.1 2008/07/27 22:22:50 appldev ship $ */
3 --
4 -- Setup Globals
5 --
6 g_use_person_id boolean;
7 g_multi_object boolean;
8 g_chunk_shuffle boolean;
9
10 /*
11
12 remove_existing_ranges
13
14 Remove any data from an errored range build.
15
16 */
17 procedure remove_existing_ranges(p_payroll_action_id in number)
18 is
19 l_dummy number;
20 begin
21 --
22 pay_proc_logging.PY_ENTRY('pay_population_ranges_pkg.remove_existing_ranges');
23 --
24 SELECT 1
25 into l_dummy
26 FROM SYS.DUAL
27 WHERE EXISTS (
28 SELECT NULL
29 FROM PAY_ASSIGNMENT_ACTIONS ACT
30 WHERE ACT.PAYROLL_ACTION_ID = p_payroll_action_id)
31 OR EXISTS (
32 SELECT NULL
33 FROM PAY_MESSAGE_LINES PML
34 WHERE PML.SOURCE_TYPE = 'P'
35 AND PML.SOURCE_ID = p_payroll_action_id)
36 OR EXISTS (
37 SELECT NULL
38 FROM PAY_POPULATION_RANGES POP
39 WHERE POP.PAYROLL_ACTION_ID = p_payroll_action_id);
40 --
41 --
42 -- OK, we've found rows, need to remove them
43 --
44 py_rollback_pkg.rollback_payroll_action(p_payroll_action_id,'ROLLBACK',TRUE);
45 --
46 pay_proc_logging.PY_EXIT('pay_population_ranges_pkg.remove_existing_ranges');
47 --
48 exception
49 when no_data_found then
50 pay_proc_logging.PY_EXIT(
51 'pay_population_ranges_pkg.remove_existing_ranges');
52 null;
53 --
54 end remove_existing_ranges;
55 --
56 /*
57 set_range_globals
58
59 Set the internal globals
60 */
61
62 procedure set_range_globals(p_payroll_action_id in number,
63 p_action_type in varchar
64 )
65 is
66 l_range_person pay_legislation_rules.rule_mode%type;
67 l_max_pinp pay_legislation_rules.rule_mode%type;
68 l_chunk_shuffle pay_legislation_rules.rule_mode%type;
69 l_found boolean;
70 begin
71 --
72 pay_proc_logging.PY_ENTRY('pay_population_ranges_pkg.set_range_globals');
73 --
74 pay_core_utils.get_action_parameter(p_para_name => 'RANGE_PERSON_ID',
75 p_para_value => l_range_person,
76 p_found => l_found);
77 --
78 if (l_found = FALSE) then
79 l_range_person := 'N';
80 end if;
81
82 if (l_range_person = 'Y') then
83 g_use_person_id := TRUE;
84 else
85 g_use_person_id := FALSE;
86 end if;
87 --
88 g_multi_object := TRUE;
89 --
90 -- MANY_PROCS_IN_PERIOD switches on RANGE_PERSON_ID for Core Interlocking
91 -- processes
92
93 if (
94 p_action_type = pay_proc_environment_pkg.PYG_AT_MAG or
95 p_action_type = pay_proc_environment_pkg.PYG_AT_CHQ or
96 p_action_type = pay_proc_environment_pkg.PYG_AT_CSH or
97 p_action_type = pay_proc_environment_pkg.PYG_AT_PST
98 ) then
99 --
100 g_use_person_id := TRUE;
101 pay_proc_logging.PY_LOG('Override RANGE_PERSON_ID set to Y');
102 --
103 elsif (p_action_type = pay_proc_environment_pkg.PYG_AT_PAY or
104 p_action_type = pay_proc_environment_pkg.PYG_AT_COS or
105 p_action_type = pay_proc_environment_pkg.PYG_AT_TGL) then
106
107 pay_core_utils.get_action_parameter(p_para_name => 'MANY_PROCS_IN_PERIOD',
108 p_para_value => l_max_pinp,
109 p_found => l_found);
110
111 if (l_found = FALSE) then
112 l_max_pinp := 'N';
113 end if;
114
115 if (l_max_pinp = 'Y') then
116 g_use_person_id := TRUE;
117 end if;
118 --
119 end if;
120 --
121 if (p_action_type = pay_proc_environment_pkg.PYG_AT_ARC) then
122 --
123 pay_core_utils.get_report_f_parameter(
124 p_payroll_action_id =>p_payroll_action_id,
125 p_para_name => 'RANGE_PERSON_ID',
126 p_para_value => l_range_person,
127 p_found => l_found);
128
129 if (l_found = FALSE) then
130 l_range_person := 'N';
131 end if;
132 --
133 if (l_range_person = 'M') then
134 g_use_person_id := TRUE;
135 elsif ( l_range_person = 'Y'
136 and g_use_person_id = TRUE) then
137 g_use_person_id := TRUE;
138 else
139 g_use_person_id := FALSE;
140 end if;
141 --
142 pay_core_utils.get_report_f_parameter(
143 p_payroll_action_id =>p_payroll_action_id,
144 p_para_name => 'MULTI_OBJECT_ACTIONS',
145 p_para_value => l_range_person,
146 p_found => l_found);
147 --
148 if (l_found = FALSE) then
149 l_range_person := 'N';
150 end if;
151 --
152 if (l_range_person = 'Y') then
153 g_multi_object := TRUE;
154 else
155 g_multi_object := FALSE;
156 end if;
157 --
158 end if;
159 --
160 pay_core_utils.get_action_parameter(p_para_name => 'CHUNK SHUFFLE',
161 p_para_value => l_chunk_shuffle,
162 p_found => l_found);
163 --
164 if (l_found = FALSE) then
165 l_chunk_shuffle := 'N';
166 end if;
167 --
168 if (l_chunk_shuffle = 'Y') then
169 g_chunk_shuffle := TRUE;
170 else
171 g_chunk_shuffle := FALSE;
172 end if;
173 --
174 pay_proc_logging.PY_EXIT('pay_population_ranges_pkg.set_range_globals');
175 --
176 end set_range_globals;
177
178 /*
179 get_range_statement
180
181 Generates the appropriate SQL to create the ranges
182
183 */
184
185 procedure get_range_statement(p_payroll_action_id in number,
186 p_statement out nocopy varchar2)
187 is
188 action pay_payroll_actions.action_type%type;
189 sqlid number;
190 len number;
191 begin
192 --
193 pay_proc_logging.PY_ENTRY('pay_population_ranges_pkg.get_range_statement');
194 --
195 action := pay_proc_environment_pkg.action_type;
196 --
197 if (pay_proc_environment_pkg.action_type =
198 pay_proc_environment_pkg.PYG_AT_RET or
199 pay_proc_environment_pkg.action_type =
200 pay_proc_environment_pkg.PYG_AT_RTA) then
201
202 sqlid := SQL_RUNRGE;
203
204 elsif (pay_proc_environment_pkg.action_type =
205 pay_proc_environment_pkg.PYG_AT_RTE) then
206
207 if (pay_proc_environment_pkg.retro_definition_id is null) then
208 sqlid := SQL_RUNRGE;
209 else
210 sqlid := SQL_RETRGE;
211 end if;
212
213 elsif (pay_proc_environment_pkg.action_type =
214 pay_proc_environment_pkg.PYG_AT_RUN) then
215
216 sqlid := SQL_RUNRGE;
217
218 elsif (pay_proc_environment_pkg.action_type =
219 pay_proc_environment_pkg.PYG_AT_ADV) then
220
221 sqlid := SQL_RUNRGE;
222
223 elsif (pay_proc_environment_pkg.action_type =
224 pay_proc_environment_pkg.PYG_AT_ADE) then
225
226 sqlid := SQL_RUNRGE;
227
228 elsif (pay_proc_environment_pkg.action_type =
229 pay_proc_environment_pkg.PYG_AT_ARC) then
230
231 sqlid := SQL_RUNRGE;
232
233 elsif (pay_proc_environment_pkg.action_type =
234 pay_proc_environment_pkg.PYG_AT_PUR) then
235
236 sqlid := SQL_PURRGE;
237
238 elsif (pay_proc_environment_pkg.action_type =
239 pay_proc_environment_pkg.PYG_AT_BEE) then
240
241 sqlid := SQL_RUNRGE;
242
243 elsif (pay_proc_environment_pkg.action_type =
244 pay_proc_environment_pkg.PYG_AT_BAL) then
245
246 sqlid := SQL_RUNRGE;
247
248 elsif (pay_proc_environment_pkg.action_type =
249 pay_proc_environment_pkg.PYG_AT_REV) then
250
251 sqlid := SQL_RUNRGE;
252
253 elsif (pay_proc_environment_pkg.action_type =
254 pay_proc_environment_pkg.PYG_AT_RCS or
255 pay_proc_environment_pkg.action_type =
256 pay_proc_environment_pkg.PYG_AT_ECS) then
257
258 if (pay_proc_environment_pkg.payroll_id is null) then
259 sqlid := SQL_NONRGE;
260 else
261 sqlid := SQL_RESRGE;
262 end if;
263
264 else
265 if (pay_proc_environment_pkg.action_type <>
266 pay_proc_environment_pkg.PYG_AT_CHQ and
267 pay_proc_environment_pkg.action_type <>
268 pay_proc_environment_pkg.PYG_AT_MAG and
269 pay_proc_environment_pkg.action_type <>
270 pay_proc_environment_pkg.PYG_AT_PST and
271 pay_proc_environment_pkg.action_type <>
272 pay_proc_environment_pkg.PYG_AT_CSH and
273 pay_proc_environment_pkg.action_type <>
274 pay_proc_environment_pkg.PYG_AT_PRU) then
275 --
276 action := 'R';
277 --
278 end if;
279 --
280 if (pay_proc_environment_pkg.payroll_id is null) then
281 sqlid := SQL_NONRGE;
282 else
283 sqlid := SQL_RESRGE;
284 end if;
285 end if;
286 --
287 hr_dynsql.pyrsql(sqlid,null,null,p_statement,len,action,p_payroll_action_id);
288 --
289 pay_proc_logging.PY_EXIT('pay_population_ranges_pkg.get_range_statement');
290 --
291 end get_range_statement;
292 --
293 /*
294 do_randomisation
295
296 Randomises the chunks to process in different orders every run.
297
298 */
299 procedure do_randomisation(p_payroll_action_id in number,
300 p_chunk_number in number)
301 is
302 --
303 type t_pay_act_id_tab IS TABLE OF pay_population_ranges.payroll_action_id%type
304 index by binary_integer;
305 type t_chunk_num_tab IS TABLE OF pay_population_ranges.chunk_number%type
306 index by binary_integer;
307 type t_person_id_tab IS TABLE OF pay_population_ranges.payroll_action_id%type
308 index by binary_integer;
309 --
310 l_pay_act_tab t_person_id_tab;
311 l_chunk_num_tab t_chunk_num_tab;
312 l_rand_chunk_num_tab t_chunk_num_tab;
313 --
314 rand_num number;
315 loop_count number;
316 --
317 begin
318 --
319 pay_proc_logging.PY_ENTRY('pay_population_ranges_pkg.do_randomisation');
320 --
321 l_pay_act_tab.delete;
322 l_chunk_num_tab.delete;
323 l_rand_chunk_num_tab.delete;
324
325 for i in 1..p_chunk_number loop
326 l_chunk_num_tab(i) := i;
327 l_rand_chunk_num_tab(i) := 0;
328 l_pay_act_tab(i) := p_payroll_action_id;
329 end loop;
330 --
331 for i in 1..p_chunk_number loop
332 --
333 select (mod(fnd_crypto.SmallRandomNumber,p_chunk_number-1)+1)
334 into rand_num
335 from dual;
336
337 loop_count := 0;
338 --
339 -- Look for an unset random chunk
340 while ( l_rand_chunk_num_tab(rand_num) <> 0
341 and loop_count <= p_chunk_number) loop
342 --
343 if (rand_num = p_chunk_number) then
344 rand_num := 1;
345 else
346 rand_num := rand_num +1;
347 end if;
348 loop_count := loop_count + 1;
349 --
350 end loop;
351 --
352 if (loop_count > p_chunk_number) then
353 pay_core_utils.assert_condition('pay_population_ranges_pkg.do_randomisation:1',
354 1 = 2);
355 end if;
356 --
357 l_rand_chunk_num_tab(rand_num) := l_chunk_num_tab(i);
358 --
359 pay_proc_logging.PY_LOG('Chunk '||l_chunk_num_tab(i)||
360 ' reassined '||rand_num);
361 --
362 end loop;
363 --
364 --
365 forall i in 1..l_chunk_num_tab.count
366 update pay_population_ranges
367 set rand_chunk_number = l_rand_chunk_num_tab(i)
368 where payroll_action_id = l_pay_act_tab(i)
369 and chunk_number = l_chunk_num_tab(i);
370 --
371 commit;
372 --
373 pay_proc_logging.PY_EXIT('pay_population_ranges_pkg.do_randomisation');
374 --
375 end do_randomisation;
376 --
377 /*
378 insert_chunk_statii
379
380 Create the rows in chunk status if needed
381 */
382
383 procedure insert_chunk_statii(p_payroll_action_id in number)
384 is
385 begin
386 --
387 pay_proc_logging.PY_ENTRY('pay_population_ranges_pkg.insert_chunk_statii');
388 --
389 INSERT into PAY_CHUNK_STATUS
390 (PAYROLL_ACTION_ID,
391 CHUNK_NUMBER,
392 RAND_CHUNK_NUMBER,
393 POPULATION_STATUS,
394 PROCESS_STATUS)
395 SELECT DISTINCT p_payroll_action_id,
396 CHUNK_NUMBER,
397 nvl(RAND_CHUNK_NUMBER, CHUNK_NUMBER),
398 'U',
399 'U'
400 FROM pay_population_ranges
401 WHERE payroll_action_id = p_payroll_action_id;
402 --
403 pay_proc_logging.PY_EXIT('pay_population_ranges_pkg.insert_chunk_statii');
404 --
405 end insert_chunk_statii;
406 --
407 /*
408 insert_ranges
409
410 This is the procedure that actually inserts the ranges, then
411 performs additional steps (randomisation etc).
412 */
413
414 procedure insert_ranges(p_payroll_action_id in number,
415 p_statement in varchar2)
416 is
417
418 type t_curs_ref is ref cursor;
419 type t_person_id_tab IS TABLE OF pay_population_ranges.person_id%type
420 index by binary_integer;
421 type t_source_id_tab IS TABLE OF pay_population_ranges.source_id%type
422 index by binary_integer;
423 type t_source_type_tab IS TABLE OF pay_population_ranges.source_type%type
424 index by binary_integer;
425 type t_pay_act_id_tab IS TABLE OF pay_population_ranges.payroll_action_id%type
426 index by binary_integer;
427 type t_chunk_num_tab IS TABLE OF pay_population_ranges.chunk_number%type
428 index by binary_integer;
429 type t_rge_stat_tab IS TABLE OF pay_population_ranges.range_status%type
430 index by binary_integer;
431 --
432 l_pay_act_tab t_person_id_tab;
433 l_chunk_num_tab t_chunk_num_tab;
434 l_rand_chunk_num_tab t_chunk_num_tab;
435 l_rge_stat_tab t_rge_stat_tab;
436 l_person_id_tab t_person_id_tab;
437 l_source_id_tab t_source_id_tab;
438 l_source_type_tab t_source_type_tab;
439 l_strt_person_id_tab t_person_id_tab;
440 l_end_person_id_tab t_person_id_tab;
441 --
442 l_end_person_id pay_population_ranges.person_id%type;
443 --
444 actioncur t_curs_ref;
445 chunk_number pay_population_ranges.chunk_number%type;
446 pactid number;
447 --
448 begin
449 --
450 pay_proc_logging.PY_ENTRY('pay_population_ranges_pkg.insert_ranges');
451 --
452 chunk_number := 0;
453 --
454 open actioncur for p_statement using p_payroll_action_id;
455 --
456 loop
457
458 l_pay_act_tab.delete;
459 l_strt_person_id_tab.delete;
460 l_end_person_id_tab.delete;
461 l_chunk_num_tab.delete;
462 l_rge_stat_tab.delete;
463 l_person_id_tab.delete;
464 l_source_id_tab.delete;
465 l_source_type_tab.delete;
466
467 if (g_multi_object = TRUE) then
468 fetch actioncur bulk collect into l_person_id_tab,
469 l_source_id_tab,
470 l_source_type_tab
471 limit pay_proc_environment_pkg.chunk_size;
472 else
473 fetch actioncur bulk collect into l_person_id_tab
474 limit pay_proc_environment_pkg.chunk_size;
475 end if;
476 --
477 hr_utility.trace('l_person_id_tab ' || l_person_id_tab.count);
478 hr_utility.trace('l_source_id_tab ' || l_source_id_tab.count);
479 hr_utility.trace('l_source_type_tab ' || l_source_id_tab.count);
480 --
481 if (l_person_id_tab.count <> 0) then
482 chunk_number := chunk_number + 1;
483 --
484 if (g_use_person_id = TRUE) then
485 --
486 for i in 1..l_person_id_tab.count loop
487 l_chunk_num_tab(i) := chunk_number;
488 l_rge_stat_tab(i) := 'U';
489 l_strt_person_id_tab(i) := l_person_id_tab(1);
490 l_end_person_id_tab(i) := l_person_id_tab(l_person_id_tab.count);
491 l_pay_act_tab(i) := p_payroll_action_id;
492 --
493 if (g_multi_object = FALSE) then
494 l_source_id_tab(i) := null;
495 l_source_type_tab(i) := null;
496 end if;
497 end loop;
498 --
499 forall i in 1..l_person_id_tab.COUNT
500 insert into pay_population_ranges (
501 payroll_action_id,
502 starting_person_id,
503 ending_person_id,
504 chunk_number,
505 range_status,
506 person_id,
507 source_id,
508 source_type)
509 values (
510 l_pay_act_tab(i),
511 l_strt_person_id_tab(i),
512 l_end_person_id_tab(i),
513 l_chunk_num_tab(i),
514 l_rge_stat_tab(i),
515 l_person_id_tab(i),
516 l_source_id_tab(i),
517 l_source_type_tab(i));
518 --
519 else
520 l_end_person_id := l_person_id_tab(l_person_id_tab.count);
521 insert into pay_population_ranges (
522 payroll_action_id,
523 starting_person_id,
524 ending_person_id,
525 chunk_number,
526 range_status
527 )
528 values (
529 p_payroll_action_id,
530 l_person_id_tab(1),
531 l_end_person_id,
532 chunk_number,
533 'U'
534 );
535 end if;
536 --
537 pay_proc_logging.PY_LOG('Chunk = '||chunk_number||
538 ' Start id '||l_person_id_tab(1)||
539 ' End id = '||
540 l_person_id_tab(l_person_id_tab.count));
541 --
542 commit;
543 --
544 end if;
545 --
546 exit when actioncur%notfound;
547 --
548 --
549 end loop;
550 --
551 close actioncur;
552 --
553 -- Don't call chunk shuffle if only 1 chunk (no point!)
554 --
555 if (g_chunk_shuffle = TRUE and
556 chunk_number <> 1) then
557 --
558 do_randomisation(p_payroll_action_id,
559 chunk_number);
560 --
561 end if;
562 --
563 if (pay_proc_environment_pkg.chunk_method = 'ORIGINAL') then
564 insert_chunk_statii(p_payroll_action_id);
565 end if;
566 --
567 pay_proc_logging.PY_EXIT('pay_population_ranges_pkg.insert_ranges');
568 --
569 end insert_ranges;
570 --
571 /*
572 perform_range_creation
573
574 This procedure generates the population ranges then executes a commit
575 */
576 procedure perform_range_creation (p_payroll_action_id in number)
577 is
578 l_statement varchar2(4000);
579 begin
580 --
581 pay_proc_logging.PY_ENTRY('pay_population_ranges_pkg.perform_range_creation');
582 --
583 set_range_globals(p_payroll_action_id,
584 pay_proc_environment_pkg.action_type
585 );
586 --
587 remove_existing_ranges(p_payroll_action_id);
588 --
589 pay_proc_environment_pkg.update_pop_action_status(
590 p_payroll_action_id,
591 APS_POP_RANGES );
592 --
593 get_range_statement(p_payroll_action_id,
594 l_statement);
595 --
596 pay_proc_logging.PY_LOG('Statement:-');
597 pay_proc_logging.PY_LOG(l_statement);
598 --
599 insert_ranges(p_payroll_action_id,
600 l_statement);
601 --
602 pay_proc_environment_pkg.update_pop_action_status(
603 p_payroll_action_id,
604 APS_POP_ACTIONS );
605 --
606 pay_proc_logging.PY_EXIT('pay_population_ranges_pkg.perform_range_creation');
607 --
608 end perform_range_creation;
609 --
610
611 /*
612 reset_errored_ranges
613
614 This procedure resets errored population ranges, ready to be reloaded
615 the issues a commit
616 */
617 procedure reset_errored_ranges(p_payroll_action_id in number)
618 is
619 --
620 type t_pay_act_id_tab IS TABLE OF pay_population_ranges.payroll_action_id%type
621 index by binary_integer;
622 type t_chunk_num_tab IS TABLE OF pay_population_ranges.chunk_number%type
623 index by binary_integer;
624 --
625 l_pay_act_tab t_pay_act_id_tab;
626 l_chunk_num_tab t_chunk_num_tab;
627 --
628 cursor get_err_chunks
629 is
630 select payroll_action_id,
631 nvl(rand_chunk_number, chunk_number)
632 from pay_population_ranges
633 where payroll_action_id = p_payroll_action_id
634 and range_status = 'E';
635 --
636 begin
637 --
638 pay_proc_logging.PY_ENTRY('pay_population_ranges_pkg.reset_errored_ranges');
639 --
640 open get_err_chunks;
641 --
642 loop
643 --
644 l_pay_act_tab.delete;
645 l_chunk_num_tab.delete;
646
647 fetch get_err_chunks bulk collect into l_pay_act_tab,
648 l_chunk_num_tab
649 limit pay_proc_environment_pkg.chunk_size;
650 --
651 forall i in 1..l_pay_act_tab.count
652 delete from pay_action_interlocks pai
653 where pai.locking_action_id in
654 (select aa.assignment_action_id
655 from pay_assignment_actions aa
656 where payroll_action_id = l_pay_act_tab(i)
657 and chunk_number = l_chunk_num_tab(i));
658 --
659 forall i in 1..l_pay_act_tab.count
660 delete from pay_assignment_actions aa
661 where aa.payroll_action_id = l_pay_act_tab(i)
662 and aa.chunk_number = l_chunk_num_tab(i);
663 --
664 forall i in 1..l_pay_act_tab.count
665 delete from pay_temp_object_actions aa
666 where aa.payroll_action_id = l_pay_act_tab(i)
667 and aa.chunk_number = l_chunk_num_tab(i);
668 --
669 forall i in 1..l_pay_act_tab.count
670 update pay_population_ranges ppr
671 set range_status = 'U'
672 where ppr.payroll_action_id = l_pay_act_tab(i)
673 and ppr.chunk_number = l_chunk_num_tab(i);
674 --
675 if (pay_proc_environment_pkg.chunk_method = 'ORIGINAL') then
676 --
677 forall i in 1..l_pay_act_tab.count
678 update pay_chunk_status
679 set population_status = 'U'
680 where payroll_action_id = l_pay_act_tab(i)
681 and chunk_number = l_chunk_num_tab(i);
682 --
683 end if;
684 --
685 commit;
686 --
687 exit when get_err_chunks%notfound;
688 --
689 end loop;
690 --
691 pay_proc_environment_pkg.update_pop_action_status(
692 p_payroll_action_id,
693 APS_POP_ACTIONS );
694 --
695 pay_proc_logging.PY_EXIT('pay_population_ranges_pkg.reset_errored_ranges');
696 --
697 end;
698 --
699 end pay_population_ranges_pkg;