1 package body PAY_ASG_LINK_USAGES_PKG as
2 /* $Header: pyalu.pkb 120.2.12000000.4 2007/03/05 12:22:25 swinton ship $ */
3 --------------------------------------------------------------------------------
4 --
5 -- Declare global variables
6 --
7
8 type t_num_tab is table of number
9 index by binary_integer;
10 type t_date_tab is table of date
11 index by binary_integer;
12 --
13 type t_asg_grp_rec is record
14 (people_group_id number
15 ,effective_start_date date
16 ,effective_end_date date
17 );
18 type t_asg_grp_hist_tab is table of t_asg_grp_rec
19 index by binary_integer;
20 --
21 -- Assignment cache for ALU.
22 --
23 g_alu_assignment_id number; -- Assignment ID
24 g_alu_asg_hist t_asg_grp_hist_tab; -- Asg people group history
25 g_alu_link_pg_id number; -- Link People Group ID
26 g_alu_asg_pg_hist t_asg_grp_hist_tab; -- Asg eligibility history
27 --
28 g_package constant varchar2 (32) := 'pay_asg_link_usages_pkg.';
29
30 --------------------------------------------------------------------------------
31 --
32 procedure INSERT_ALU (
33 --
34 --******************************************************************************
35 --* Inserts Assignment Link Usages for a new element link. *
36 --******************************************************************************
37 --
38 p_business_group_id number,
39 p_people_group_id number,
40 p_element_link_id number,
41 p_effective_start_date date,
42 p_effective_end_date date) is
43 --
44 v_previous_assignment_id number;
45 v_previous_start_date date;
46 v_previous_end_date date;
47 v_termination_date date;
48 v_unwanted_out_parameter date;
49 v_rows_were_found boolean;
50 --
51 -- Table variables for bulk.
52 --
53 type t_num_tab is table of number index by binary_integer;
54 type t_date_tab is table of date index by binary_integer;
55
56 v_asg_id_tab t_num_tab;
57 v_start_date_tab t_date_tab;
58 v_end_date_tab t_date_tab;
59
60 --
61 -- Cursor to fetch the info for the re1uired people group
62 --
63 cursor link_people_group is
64 select id_flex_num,
65 segment1, segment2, segment3, segment4, segment5,
66 segment6, segment7, segment8, segment9, segment10,
67 segment11, segment12, segment13, segment14, segment15,
68 segment16, segment17, segment18, segment19, segment20,
69 segment21, segment22, segment23, segment24, segment25,
70 segment26, segment27, segment28, segment29, segment30
71 from pay_people_groups link_group
72 where link_group.people_group_id = p_people_group_id;
73 --
74 type segment_table is table of varchar2(60)
75 index by binary_integer;
76 segment segment_table;
77 i number;
78 sql_curs number;
79 rows_processed integer;
80 statem varchar2(8000);
81 row_count number := 0;
82 --
83 l_assignment_id per_assignments_f.assignment_id%type;
84 l_effective_start_date per_assignments_f.effective_start_date%type;
85 l_effective_end_date per_assignments_f.effective_end_date%type;
86 --
87 -- Bug 5408395.
88 -- This sub procedure was renamed from create_alu to set_alu as this
89 -- now only sets up the ALU data and ALUs are uploaded by bulk in
90 -- upload_alus().
91 --
92 --procedure CREATE_ALU is
93 procedure set_ALU is
94 --
95 l_alu_start date;
96 l_alu_end date;
97 l_idx number;
98 begin
99 l_alu_start := greatest(p_effective_start_date, v_previous_start_date);
100 --
101 -- Get the final process date for the assignment if there is one NB.
102 -- it will return the end of time if there is not one. This is so
103 -- that the subsequent comparison will ignore the date.
104 --
105 -- Bug 5202396.
106 -- The check for termination rule caused a significant performance
107 -- issue. Since ALU is only a part of the link eligibility rules,
108 -- we can determine the end date with the link and the assignment.
109 --
110 /*****************
111 hr_entry.entry_asg_pay_link_dates(v_previous_assignment_id,
112 p_element_link_id,
113 l_alu_start,
114 v_termination_date,
115 v_unwanted_out_parameter,
116 v_unwanted_out_parameter,
117 v_unwanted_out_parameter,
118 v_unwanted_out_parameter,
119 false);
120 --
121 l_alu_end := least(p_effective_end_date,
122 v_previous_end_date,
123 v_termination_date);
124 *****************/
125 --
126 l_alu_end := least(p_effective_end_date,
127 v_previous_end_date);
128
129 --
130 if l_alu_end >= l_alu_start then
131 hr_utility.trace('ALU : ' || l_alu_start || ' ' || l_alu_end);
132
133 l_idx := v_asg_id_tab.count+1;
134 --
135 -- Set the ALU data to table variables.
136 --
137 v_asg_id_tab(l_idx) := v_previous_assignment_id;
138 v_start_date_tab(l_idx) := l_alu_start;
139 v_end_date_tab(l_idx) := l_alu_end;
140
141 end if;
142 --
143 end set_ALU;
144 --end create_ALU;
145 --
146 --
147 -- Sub procedure to upload alus by bulk.
148 --
149 procedure upload_alus
150 is
151 l_count number;
152 begin
153 l_count := v_asg_id_tab.count;
154
155 if l_count > 0 then
156
157 forall i in 1..l_count
158 insert into pay_assignment_link_usages_f
159 (assignment_link_usage_id
160 ,effective_start_date
161 ,effective_end_date
162 ,element_link_id
163 ,assignment_id)
164 values
165 (pay_assignment_link_usages_s.nextval
166 ,v_start_date_tab(i)
167 ,v_end_date_tab(i)
168 ,p_element_link_id
169 ,v_asg_id_tab(i)
170 );
171 --
172 -- Reset the table variables.
173 --
174 v_asg_id_tab.delete;
175 v_start_date_tab.delete;
176 v_end_date_tab.delete;
177
178 end if;
179
180 end upload_alus;
181 --
182 --
183 begin
184 --
185 -- Cycle through qualifying assignments. Create an ALU when the last end date
186 -- of a continuous set of date-effective rows for an assignment is found.
187 -- eg:
188 --
189 -- Assignment ID 1 +_____________+_______+_______________+___________+
190 -- Match found? YES YES NO YES
191 --
192 -- ALU ID 1 +_____________________+
193 -- ALU ID 2 +___________+
194 --
195 for lpg in link_people_group loop
196 --
197 segment(1) := lpg.segment1;
198 segment(2) := lpg.segment2;
199 segment(3) := lpg.segment3;
200 segment(4) := lpg.segment4;
201 segment(5) := lpg.segment5;
202 segment(6) := lpg.segment6;
203 segment(7) := lpg.segment7;
204 segment(8) := lpg.segment8;
205 segment(9) := lpg.segment9;
206 segment(10) := lpg.segment10;
207 segment(11) := lpg.segment11;
208 segment(12) := lpg.segment12;
209 segment(13) := lpg.segment13;
210 segment(14) := lpg.segment14;
211 segment(15) := lpg.segment15;
212 segment(16) := lpg.segment16;
213 segment(17) := lpg.segment17;
214 segment(18) := lpg.segment18;
215 segment(19) := lpg.segment19;
216 segment(20) := lpg.segment20;
217 segment(21) := lpg.segment21;
218 segment(22) := lpg.segment22;
219 segment(23) := lpg.segment23;
220 segment(24) := lpg.segment24;
221 segment(25) := lpg.segment25;
222 segment(26) := lpg.segment26;
223 segment(27) := lpg.segment27;
224 segment(28) := lpg.segment28;
225 segment(29) := lpg.segment29;
226 segment(30) := lpg.segment30;
227 --
228 statem := '
229 select assignment.assignment_id,
230 assignment.effective_start_date,
231 assignment.effective_end_date
232 from per_all_assignments_f ASSIGNMENT,
233 pay_people_groups ASSIGNMENT_GROUP
234 where assignment.assignment_type not in (''A'',''O'')
235 and assignment.business_group_id + 0 = :p_business_group_id
236 and assignment.effective_start_date <= :p_effective_end_date
237 and assignment.effective_end_date >= :p_effective_start_date
238 and assignment_group.id_flex_num = :p_id_flex_num
239 and assignment_group.people_group_id = assignment.people_group_id';
240 --
241 for i in 1..30 loop
242 if segment(i) is not null then
243 statem := statem || ' and assignment_group.segment'||i||' = :p_segment'||i;
244 end if;
245 end loop;
246 --
247 statem := statem || ' order by assignment_id, effective_start_date';
248 statem := statem || ' for update';
249 --
250 --
251 sql_curs := dbms_sql.open_cursor;
252 --
253 dbms_sql.parse(sql_curs,
254 statem,
255 dbms_sql.v7);
256 --
257 dbms_sql.bind_variable(sql_curs, 'p_business_group_id', p_business_group_id);
258 dbms_sql.bind_variable(sql_curs, 'p_effective_start_date', p_effective_start_date);
259 dbms_sql.bind_variable(sql_curs, 'p_effective_end_date', p_effective_end_date);
260 dbms_sql.bind_variable(sql_curs, 'p_id_flex_num', lpg.id_flex_num);
261 --
262 for i in 1..30 loop
263 if segment(i) is not null then
264 dbms_sql.bind_variable(sql_curs, 'p_segment'||i, segment(i));
265 end if;
266 end loop;
267 --
268 dbms_sql.define_column(sql_curs,1,l_assignment_id);
269 dbms_sql.define_column(sql_curs,2,l_effective_start_date);
270 dbms_sql.define_column(sql_curs,3,l_effective_end_date);
271 --
272 rows_processed := dbms_sql.execute(sql_curs);
273 --
274 loop
275 if dbms_sql.fetch_rows(sql_curs) > 0 then
276 --
277 row_count := row_count + 1;
278 --
279 dbms_sql.column_value(sql_curs,1,l_assignment_id);
280 dbms_sql.column_value(sql_curs,2,l_effective_start_date);
281 dbms_sql.column_value(sql_curs,3,l_effective_end_date);
282 --
283 hr_utility.trace('l_assignment_id '||l_assignment_id);
284 hr_utility.trace('l_effective_start_date '||l_effective_start_date);
285 hr_utility.trace('l_effective_end_date '||l_effective_end_date);
286 --
287 if row_count = 1 then -- Skip first row but set ID
288 --
289 v_rows_were_found := TRUE;
290 v_previous_assignment_id := l_assignment_id;
291 v_previous_start_date := l_effective_start_date;
292 v_previous_end_date := l_effective_end_date;
293 --
294 else
295 --
296 -- Check for last record or a new asignment ie. either a new
297 -- assignment_id or the same assignment_id but not contiguous records
298 --
299 if (l_assignment_id <> v_previous_assignment_id
300 or l_effective_start_date -1 <> v_previous_end_date) then
301 --
302 -- Setup the ALU data in the table variables.
303 --
304 set_ALU;
305 --create_ALU;
306 --
307 -- Upload ALUs by a reasonable amount.
308 --
309 if v_asg_id_tab.count = 200 then
310 --
311 upload_alus;
312
313 end if;
314 --
315 -- Bugfix 2364196
316 -- Always increment the v_previous_start_date when a new
317 -- assignment is detected.
318 --
319 v_previous_start_date := l_effective_start_date;
320 --
321 end if;
322 --
323 v_previous_end_date := l_effective_end_date;
324 --
325 end if;
326 --
327 v_previous_assignment_id := l_assignment_id;
328 --
329 else
330 exit;
331 end if;
332 end loop;
333 --
334 dbms_sql.close_cursor(sql_curs);
335 --
336 end loop;
337 --
338 -- Handle the last row in the cursor (loop exits before checking it)
339 --
340 if v_rows_were_found then
341 --
342 --create_ALU;
343 set_alu;
344 --
345 end if;
346 --
347 -- Upload all of the remaining ALUs.
348 --
349 upload_alus;
350 --
351 end insert_alu;
352 --------------------------------------------------------------------------------
353 procedure CASCADE_LINK_DELETION (
354 --
355 --******************************************************************************
356 --* Deletes ALUs for a deleted link. *
357 --******************************************************************************
358 --
359 p_element_link_id number,
360 p_business_group_id number,
361 p_people_group_id number,
362 p_delete_mode varchar2,
363 p_effective_start_date date,
364 p_effective_end_date date,
365 p_validation_start_date date,
366 p_validation_end_date date ) is
367 --
368 v_session_date date;
369 --
370 begin
371 --
372 if p_delete_mode = 'DELETE' then
373 --
374 v_session_date := p_validation_start_date -1;
375 --
376 delete
377 from pay_assignment_link_usages_f
378 where element_link_id = p_element_link_id
379 and effective_start_date >= p_validation_start_date;
380 --
381 if p_delete_mode = 'DELETE' then
382 --
383 update pay_assignment_link_usages_f
384 set effective_end_date = v_session_date
385 where element_link_id = p_element_link_id
386 and v_session_date between effective_start_date
387 and effective_end_date;
388 --
389 end if;
390 --
391 elsif p_delete_mode in ('ZAP', 'DELETE_NEXT_CHANGE') then
392 --
393 delete
394 from pay_assignment_link_usages_f
395 where element_link_id = p_element_link_id;
396 --
397 if p_delete_mode = 'DELETE_NEXT_CHANGE' then
398 --
399 insert_ALU (
400 p_business_group_id,
401 p_people_group_id,
402 p_element_link_id,
403 p_effective_start_date,
404 p_effective_end_date );
405 --
406 end if;
407 --
408 end if;
409 --
410 end cascade_link_deletion;
411 --------------------------------------------------------------------------------
412 --
413 function pg_eligible
414 --
415 --******************************************************************************
416 --* Returns Y if the assignment people group is eligible for *
417 --* the link people group. Otherwise returns N. *
418 --******************************************************************************
419 --
420 (p_link_people_group_id in number
421 ,p_asg_people_group_id in number
422 ) return varchar2
423 is
424 --
425 l_dummy number;
426 l_eligible varchar2(1);
427 --
428 cursor csr_pg
429 is
433 where asg_pg.people_group_id = p_asg_people_group_id
430 select 1
431 from pay_people_groups el_pg,
432 pay_people_groups asg_pg
434 and el_pg.people_group_id = p_link_people_group_id
435 and el_pg.id_flex_num = asg_pg.id_flex_num
436 and (el_pg.segment1 is null or el_pg.segment1 = asg_pg.segment1)
437 and (el_pg.segment2 is null or el_pg.segment2 = asg_pg.segment2)
438 and (el_pg.segment3 is null or el_pg.segment3 = asg_pg.segment3)
439 and (el_pg.segment4 is null or el_pg.segment4 = asg_pg.segment4)
440 and (el_pg.segment5 is null or el_pg.segment5 = asg_pg.segment5)
441 and (el_pg.segment6 is null or el_pg.segment6 = asg_pg.segment6)
442 and (el_pg.segment7 is null or el_pg.segment7 = asg_pg.segment7)
443 and (el_pg.segment8 is null or el_pg.segment8 = asg_pg.segment8)
444 and (el_pg.segment9 is null or el_pg.segment9 = asg_pg.segment9)
445 and (el_pg.segment10 is null or el_pg.segment10 = asg_pg.segment10)
446 and (el_pg.segment11 is null or el_pg.segment11 = asg_pg.segment11)
447 and (el_pg.segment12 is null or el_pg.segment12 = asg_pg.segment12)
448 and (el_pg.segment13 is null or el_pg.segment13 = asg_pg.segment13)
449 and (el_pg.segment14 is null or el_pg.segment14 = asg_pg.segment14)
450 and (el_pg.segment15 is null or el_pg.segment15 = asg_pg.segment15)
451 and (el_pg.segment16 is null or el_pg.segment16 = asg_pg.segment16)
452 and (el_pg.segment17 is null or el_pg.segment17 = asg_pg.segment17)
453 and (el_pg.segment18 is null or el_pg.segment18 = asg_pg.segment18)
454 and (el_pg.segment19 is null or el_pg.segment19 = asg_pg.segment19)
455 and (el_pg.segment20 is null or el_pg.segment20 = asg_pg.segment20)
456 and (el_pg.segment21 is null or el_pg.segment21 = asg_pg.segment21)
457 and (el_pg.segment22 is null or el_pg.segment22 = asg_pg.segment22)
458 and (el_pg.segment23 is null or el_pg.segment23 = asg_pg.segment23)
459 and (el_pg.segment24 is null or el_pg.segment24 = asg_pg.segment24)
460 and (el_pg.segment25 is null or el_pg.segment25 = asg_pg.segment25)
461 and (el_pg.segment26 is null or el_pg.segment26 = asg_pg.segment26)
462 and (el_pg.segment27 is null or el_pg.segment27 = asg_pg.segment27)
463 and (el_pg.segment28 is null or el_pg.segment28 = asg_pg.segment28)
464 and (el_pg.segment29 is null or el_pg.segment29 = asg_pg.segment29)
465 and (el_pg.segment30 is null or el_pg.segment30 = asg_pg.segment30)
466 ;
467 begin
468 if p_link_people_group_id = p_asg_people_group_id then
469 return 'Y';
470 end if;
471
472 open csr_pg;
473 fetch csr_pg into l_dummy;
474 if csr_pg%found then
475 l_eligible := 'Y';
476 else
477 l_eligible := 'N';
478 end if;
479 close csr_pg;
480
481 return l_eligible;
482 end pg_eligible;
483 --
484 --------------------------------------------------------------------------------
485 --
486 procedure deinit_alu_asg
487 --
488 --******************************************************************************
489 --* Clears the ALU assignment cache. *
490 --******************************************************************************
491 --
492 is
493 begin
494 g_alu_assignment_id := null;
495 g_alu_asg_hist.delete;
496 g_alu_link_pg_id := null;
497 g_alu_asg_pg_hist.delete;
498 end deinit_alu_asg;
499 --
500 --------------------------------------------------------------------------------
501 --
502 procedure init_alu_asg(p_assignment_id in number)
503 --
504 --******************************************************************************
505 --* initialises the ALU assignment cache. *
506 --******************************************************************************
507 is
508 --
509 l_idx number:=0;
510 l_prev_asg t_asg_grp_rec;
511 l_pg_found boolean;
512 --
513 cursor csr_asg_hist
514 is
515 select
516 people_group_id
517 ,effective_start_date
518 ,effective_end_date
519 from per_all_assignments_f
520 where assignment_id = p_assignment_id
521 and assignment_type not in ('A','O')
522 and people_group_id is not null
523 order by effective_start_date;
524 --
525 begin
526 --
527 -- Clear existing cache.
528 --
529 deinit_alu_asg;
530 --
531 g_alu_assignment_id := p_assignment_id;
532 --
533 -- Create the distinct people group histry records for this assignment.
534 --
535 -- Assignment history
536 -- PG1 PG1 PG2 PG2 PG3
537 -- |------->|------->|------->|------->|------->
538 --
539 -- People group history
540 -- PG1 PG2 PG3
541 -- |---------------->|---------------->|------->
542 --
543 for l_asg in csr_asg_hist loop
544 --
545 if l_prev_asg.people_group_id = l_asg.people_group_id
546 and l_prev_asg.effective_end_date+1 = l_asg.effective_start_date then
547 --
548 -- Extend the previous record
549 --
550 g_alu_asg_hist(l_idx).effective_end_date := l_asg.effective_end_date;
551 else
552 l_idx := l_idx +1;
553 g_alu_asg_hist(l_idx) := l_asg;
554 --
555 end if;
556 --
557 l_prev_asg := l_asg;
558 --
559 end loop;
560 --
561 end init_alu_asg;
562 --
566 --
563 --------------------------------------------------------------------------------
564 --
565 procedure init_alu_asg_pg
567 --******************************************************************************
568 --* initialises the ALU assignment cache for the specified *
569 --* link people group. *
570 --******************************************************************************
571 --
572 (p_assignment_id in number
573 ,p_link_people_group_id in number)
574 is
575 l_idx number:= 0;
576 l_prev_idx number;
577 begin
578 if g_alu_assignment_id = p_assignment_id then
579 --
580 -- Assignment level cache already exists.
581 --
582 null;
583 else
584 init_alu_asg(p_assignment_id);
585 end if;
586 --
587 g_alu_link_pg_id := p_link_people_group_id;
588 g_alu_asg_pg_hist.delete;
589 --
590 -- Create the eligible assignment histry records for the
591 -- the specified people group.
592 --
593 -- Eligibility for the link people group
594 -- PG1 PG2 PG3 PG4
595 -- ASG |------->|------->|------->|------->
596 -- Yes Yes No Yes
597 --
598 -- Eligible assignment date range
599 -- ASG |----------------> |------->
600 --
601 for i in 1..g_alu_asg_hist.count loop
602
603 if pg_eligible
604 (p_link_people_group_id
605 ,g_alu_asg_hist(i).people_group_id
606 ) = 'Y' then
607 --
608 l_prev_idx := l_idx;
609 --
610 if l_idx = 0 then
611 l_idx := l_idx+1;
612 elsif g_alu_asg_hist(i).effective_start_date
613 = g_alu_asg_pg_hist(l_idx).effective_end_date+1 then
614 --
615 -- Extend the duration
616 --
617 g_alu_asg_pg_hist(l_idx).effective_end_date
618 := g_alu_asg_hist(i).effective_end_date;
619 else
620 l_idx := l_idx+1;
621 end if;
622 --
623 if l_idx > l_prev_idx then
624 --
625 -- Create a new date range.
626 --
627 g_alu_asg_pg_hist(l_idx).effective_start_date
628 := g_alu_asg_hist(i).effective_start_date;
629 g_alu_asg_pg_hist(l_idx).effective_end_date
630 := g_alu_asg_hist(i).effective_end_date;
631 end if;
632 end if;
633 end loop;
634 end init_alu_asg_pg;
635 --
636 --------------------------------------------------------------------------------
637 --
638 procedure chk_pg_eligibility
639 --
640 --******************************************************************************
641 --* checks the eligibility of the people group criteria. *
642 --* If the assignment is eligible for the link people group, *
643 --* p_eligible_pg_exists will be set to TRUE and the eligibility history *
644 --* records will be cached for further processing. *
645 --******************************************************************************
646 --
647 (p_assignment_id in number
648 ,p_link_people_group_id in number
649 ,p_eligible_pg_exists out nocopy boolean
650 )
651 is
652 begin
653 if g_alu_assignment_id = p_assignment_id then
654 --
655 -- Asg cache already exists.
656 --
657 if g_alu_link_pg_id = p_link_people_group_id then
658 --
659 -- People group cache also exists.
660 --
661 null;
662 else
663 init_alu_asg_pg(p_assignment_id, p_link_people_group_id);
664 end if;
665 else
666 --
667 -- Initialise ALU assignment and link cache.
668 --
669 init_alu_asg(p_assignment_id);
670 init_alu_asg_pg(p_assignment_id, p_link_people_group_id);
671 end if;
672 --
676 --
673 p_eligible_pg_exists := (g_alu_asg_pg_hist.count > 0);
674 --
675 end chk_pg_eligibility;
677 --------------------------------------------------------------------------------
678 --
679 procedure create_alu_asg_pg
680 --
681 --******************************************************************************
682 --* This procedure creates ALU's of the assignment for a particular link *
683 --* people group. *
684 --******************************************************************************
685 --
686 (p_assignment_id in number
687 ,p_link_people_group_id in number
688 ,p_link_id_tab in t_num_tab
689 ,p_link_start_date_tab in t_date_tab
690 ,p_link_end_date_tab in t_date_tab
691 )
692 is
693 --
694 l_idx number:= 0;
695 l_prev_idx number:= 0;
696 --
697 -- ALU table variables
698 --
699 l_alu_start_date_tab t_date_tab;
700 l_alu_end_date_tab t_date_tab;
701 l_alu_link_id_tab t_num_tab;
702 --
703 l_alu_start_date date;
704 l_alu_end_date date;
705 l_asg_start_date date;
706 l_asg_end_date date;
707 l_link_start_date date;
708 l_link_end_date date;
709 l_link_id number;
710 --
711 l_alu_idx number:= 0;
712 l_eligible_pg_exists boolean;
713 l_dummy number;
714 l_alu_exists boolean;
715 l_asg_locked boolean;
716 --
717 cursor csr_alu_exists
718 (p_element_link_id in number)
719 is
720 select 1 from pay_assignment_link_usages_f
721 where assignment_id = p_assignment_id
722 and element_link_id = p_element_link_id;
723 --
724 l_proc varchar2(72) := g_package||'create_alu_asg_pg';
725 --
726 -- sub procedure to lock assignment.
727 --
728 procedure lock_asg
729 (p_asg_id in number
730 ,p_start_date in date
731 ,p_end_date in date)
732 is
733 --
734 cursor csr_lock_asg
735 is
736 select 1
737 from per_all_assignments_f
738 where assignment_id = p_asg_id
739 and effective_start_date <= p_end_date
740 and effective_end_date >= p_start_date
741 for update nowait
742 ;
743 --
744 l_num_tab t_num_tab;
745 --
746 begin
747 open csr_lock_asg;
748 fetch csr_lock_asg bulk collect into l_num_tab;
749 close csr_lock_asg;
750 exception
751 when hr_api.object_locked then
752 --
753 -- Failed to lock the assignment.
754 --
755 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
756 hr_utility.set_message_token('TABLE_NAME', 'per_all_assignments_f');
757 hr_utility.raise_error;
758 end lock_asg;
759 --
760 begin
761
762 hr_utility.set_location('Entering: '||l_proc, 5);
763 --
764 -- Check the eligibility of the people group and
765 -- also establish the necessary cache.
766 --
767 chk_pg_eligibility
768 (p_assignment_id
769 ,p_link_people_group_id
770 ,l_eligible_pg_exists);
771
772 --
773 -- Construct the date tables for ALU
774 --
775 if l_eligible_pg_exists then
776
777 hr_utility.set_location(l_proc, 10);
778
779 l_asg_locked := false;
780 --
781 -- Setup the ALU table data
782 --
783 for i in 1..p_link_id_tab.count loop
784 --
785 l_link_id := p_link_id_tab(i);
786 l_link_start_date := p_link_start_date_tab(i);
787 l_link_end_date := p_link_end_date_tab(i);
788 --
789 -- Check if ALU exists for this element link.
790 --
791 open csr_alu_exists(l_link_id);
792 fetch csr_alu_exists into l_dummy;
793 if csr_alu_exists%found then
794 l_alu_exists := true;
795 else
796 l_alu_exists := false;
797 end if;
798 close csr_alu_exists;
799
800 if not l_alu_exists then
801 --
802 -- Create ALU records comparing the link dates and
803 -- the eligible assignment date range.
804 --
805 -- Element Link |--------------->
806 -- Eligible Assignment |----------> |-------->
807 --
808 -- ALU |-----> |---->
809 --
810 -- Note: We no longer check the termination rule for ALUs.
811 --
812 for j in 1..g_alu_asg_pg_hist.count loop
813 --
814 l_asg_start_date := g_alu_asg_pg_hist(j).effective_start_date;
815 l_asg_end_date := g_alu_asg_pg_hist(j).effective_end_date;
816 --
817 if NOT l_asg_locked then
818 --
819 -- Lock the assignment.
820 --
821 lock_asg(p_assignment_id, l_asg_start_date, l_asg_end_date);
822 end if;
823 --
824 if l_link_start_date <= l_asg_end_date
825 and l_link_end_date >= l_asg_start_date then
826 --
827 l_alu_start_date := greatest(l_link_start_date, l_asg_start_date);
828 l_alu_end_date := least(l_link_end_date, l_asg_end_date);
829 --
830 if l_alu_end_date >= l_alu_start_date then
831
832 l_alu_idx := l_alu_idx+1;
833 l_alu_start_date_tab(l_alu_idx) := l_alu_start_date;
834 l_alu_end_date_tab(l_alu_idx) := l_alu_end_date;
835 l_alu_link_id_tab(l_alu_idx) := l_link_id;
836
840 --
837 end if;
838 end if;
839 end loop;
841 -- The assignment rows have been locked.
842 --
843 l_asg_locked := true;
844 --
845 end if;
846 end loop;
847 --
848 -- Upload ALUs
849 --
850 forall i in 1..l_alu_start_date_tab.count
851 insert into pay_assignment_link_usages_f
852 (assignment_link_usage_id
853 ,effective_start_date
854 ,effective_end_date
855 ,element_link_id
856 ,assignment_id)
857 values
858 (pay_assignment_link_usages_s.nextval
859 ,l_alu_start_date_tab(i)
860 ,l_alu_end_date_tab(i)
861 ,l_alu_link_id_tab(i)
862 ,p_assignment_id
863 );
864 --
865 end if;
866 --
867 hr_utility.set_location('Leaving: '||l_proc, 100);
868 --
869 end create_alu_asg_pg;
870 --
871 --------------------------------------------------------------------------------
872 --
873 procedure create_alu_asg
874 --
875 --******************************************************************************
876 --* creates ALU's for the assignment with the specified element links. *
877 --* *
878 --* NOTE: The element link array should start with index number 1 and *
879 --* should be sorted by people group id and link effective dates must be *
880 --* min effective_start_date and max effective_end_date. *
881 --******************************************************************************
882 --
883 (p_assignment_id in number
884 ,p_pg_link_tab in t_pg_link_tab
885 )
886 is
887 l_proc varchar2(72) := g_package||'create_alu_asg';
888 --
889 type t_pg_index_rec is record
890 (people_group_id number
891 ,start_idx binary_integer
892 ,end_idx binary_integer
893 );
894 type t_pg_index_tab is table of t_pg_index_rec
895 index by binary_integer;
896 --
897 l_pg_idx_tab t_pg_index_tab;
898 l_idx number:=0;
899 l_prev_pg_id number;
900 --
901 l_eligible_pg_exists boolean;
902 l_link_id_tab t_num_tab;
903 l_link_start_date_tab t_date_tab;
904 l_link_end_date_tab t_date_tab;
905 l_link_idx number;
906 --
907 begin
908 --
909 hr_utility.set_location('Entering: '||l_proc, 5);
910 --
911 -- Check to see if the specified table variable contains
912 -- any element link data.
913 --
914 if p_pg_link_tab.count = 0 then
915 --
916 -- No element link was specified, hence exit this procedure.
917 --
918 hr_utility.set_location(l_proc, 10);
919 return;
920 end if;
921
922 --
923 -- Initialize the assignment cache.
924 --
925 init_alu_asg(p_assignment_id);
926
927 --
928 -- Check to see if the assignment is associated with any people group.
929 --
930 if g_alu_asg_hist.count = 0 then
931 --
932 -- No people group is associated with this assignment,
933 -- hence exit this procedure.
934 --
935 hr_utility.set_location(l_proc, 20);
936 return;
937 end if;
938
939 --
940 -- Derive distinct people groups from the specified table variable.
941 --
942 for i in 1..p_pg_link_tab.count loop
943 if p_pg_link_tab(i).people_group_id = l_prev_pg_id then
944 l_pg_idx_tab(l_idx).end_idx := i;
945 else
946 l_idx := l_idx+1;
947 l_prev_pg_id := p_pg_link_tab(i).people_group_id;
948 l_pg_idx_tab(l_idx).people_group_id := l_prev_pg_id;
949 l_pg_idx_tab(l_idx).start_idx := i;
950 l_pg_idx_tab(l_idx).end_idx := i;
951 end if;
952 end loop;
953 --
954 -- Loop for each people group
955 --
956 for i in 1..l_pg_idx_tab.count loop
957 --
958 -- Check the eligibility of the people group.
959 --
960 chk_pg_eligibility
961 (p_assignment_id
962 ,l_pg_idx_tab(i).people_group_id
963 ,l_eligible_pg_exists);
964
965 --
966 if l_eligible_pg_exists then
967 --
968 l_link_id_tab.delete;
969 l_link_start_date_tab.delete;
970 l_link_end_date_tab.delete;
971 l_link_idx := 0;
972 --
973 -- Prepare element link array for this people group.
974 --
975 for j in l_pg_idx_tab(i).start_idx..l_pg_idx_tab(i).end_idx loop
976 l_link_idx := l_link_idx+1;
977 l_link_id_tab(l_link_idx) := p_pg_link_tab(j).element_link_id;
978 l_link_start_date_tab(l_link_idx)
979 := p_pg_link_tab(j).effective_start_date;
980 l_link_end_date_tab(l_link_idx)
981 := p_pg_link_tab(j).effective_end_date;
982 end loop;
983 --
984 -- Create ALUs for this people group.
985 --
986 create_alu_asg_pg
987 (p_assignment_id => p_assignment_id
988 ,p_link_people_group_id => l_pg_idx_tab(i).people_group_id
989 ,p_link_id_tab => l_link_id_tab
990 ,p_link_start_date_tab => l_link_start_date_tab
991 ,p_link_end_date_tab => l_link_end_date_tab
992 );
993 --
994 end if;
995 --
996 end loop;
997 --
998 hr_utility.set_location('Leaving: '||l_proc, 100);
999 --
1000 end create_alu_asg;
1001 --
1002 --------------------------------------------------------------------------------
1003 --
1007 --* Name : rebuild_alus *
1004 procedure rebuild_alus
1005 --
1006 --******************************************************************************
1008 --* Purpose : This procedure rebuilds all ALUs for a given assignment id *
1009 --* and is used by the Generic Upgrade Mechanism. *
1010 --******************************************************************************
1011 --
1012 (p_assignment_id in number)
1013 is
1014 --
1015 -- user defined types
1016 --
1017 type t_asg_rec is record
1018 (effective_start_date date,
1019 effective_end_date date,
1020 business_group_id number,
1021 people_group_id number,
1022 id_flex_num number);
1023 --
1024 type t_alu_start_date is table of pay_assignment_link_usages_f.effective_start_date%type
1025 index by binary_integer;
1026 --
1027 type t_alu_end_date is table of pay_assignment_link_usages_f.effective_end_date%type
1028 index by binary_integer;
1029 --
1030 type t_alu_link_id is table of pay_assignment_link_usages_f.element_link_id%type
1031 index by binary_integer;
1032 --
1033 type t_alu_tab is record
1034 (start_date t_alu_start_date,
1035 end_date t_alu_end_date,
1036 link_id t_alu_link_id);
1037 --
1038 -- find all instances of the assignment that have a people group
1039 --
1040 cursor csr_assignment
1041 (
1042 p_assignment_id number
1043 ) is
1044 select asg.effective_start_date,
1045 asg.effective_end_date,
1046 asg.business_group_id,
1047 asg.people_group_id,
1048 ppg.id_flex_num
1049 from per_all_assignments_f asg,
1050 pay_people_groups ppg
1051 where asg.assignment_id = p_assignment_id
1052 and asg.assignment_type not in ('A','O')
1053 and asg.people_group_id is not null
1054 and ppg.people_group_id = asg.people_group_id
1055 order by asg.effective_start_date;
1056 --
1057 -- find all element links that are match the people group
1058 --
1059 cursor csr_link
1060 (
1061 p_id_flex_num number,
1062 p_business_group_id number,
1063 p_people_group_id number,
1064 p_effective_start_date date,
1065 p_effective_end_date date
1066 ) is
1067 select el.element_link_id,
1068 min(el.effective_start_date) effective_start_date,
1069 max(el.effective_end_date) effective_end_date
1070 from pay_element_links_f el,
1071 pay_people_groups el_pg,
1072 pay_people_groups asg_pg
1073 where asg_pg.id_flex_num = p_id_flex_num
1074 and asg_pg.people_group_id = p_people_group_id
1075 and el_pg.id_flex_num = asg_pg.id_flex_num
1076 and el.business_group_id + 0 = p_business_group_id
1077 and el.effective_start_date <= p_effective_end_date
1078 and el.effective_end_date >= p_effective_start_date
1079 and el_pg.people_group_id = el.people_group_id
1080 and (el_pg.segment1 is null or el_pg.segment1 = asg_pg.segment1)
1081 and (el_pg.segment2 is null or el_pg.segment2 = asg_pg.segment2)
1082 and (el_pg.segment3 is null or el_pg.segment3 = asg_pg.segment3)
1083 and (el_pg.segment4 is null or el_pg.segment4 = asg_pg.segment4)
1084 and (el_pg.segment5 is null or el_pg.segment5 = asg_pg.segment5)
1085 and (el_pg.segment6 is null or el_pg.segment6 = asg_pg.segment6)
1086 and (el_pg.segment7 is null or el_pg.segment7 = asg_pg.segment7)
1087 and (el_pg.segment8 is null or el_pg.segment8 = asg_pg.segment8)
1088 and (el_pg.segment9 is null or el_pg.segment9 = asg_pg.segment9)
1089 and (el_pg.segment10 is null or el_pg.segment10 = asg_pg.segment10)
1090 and (el_pg.segment11 is null or el_pg.segment11 = asg_pg.segment11)
1091 and (el_pg.segment12 is null or el_pg.segment12 = asg_pg.segment12)
1092 and (el_pg.segment13 is null or el_pg.segment13 = asg_pg.segment13)
1093 and (el_pg.segment14 is null or el_pg.segment14 = asg_pg.segment14)
1094 and (el_pg.segment15 is null or el_pg.segment15 = asg_pg.segment15)
1095 and (el_pg.segment16 is null or el_pg.segment16 = asg_pg.segment16)
1096 and (el_pg.segment17 is null or el_pg.segment17 = asg_pg.segment17)
1097 and (el_pg.segment18 is null or el_pg.segment18 = asg_pg.segment18)
1098 and (el_pg.segment19 is null or el_pg.segment19 = asg_pg.segment19)
1099 and (el_pg.segment20 is null or el_pg.segment20 = asg_pg.segment20)
1100 and (el_pg.segment21 is null or el_pg.segment21 = asg_pg.segment21)
1101 and (el_pg.segment22 is null or el_pg.segment22 = asg_pg.segment22)
1102 and (el_pg.segment23 is null or el_pg.segment23 = asg_pg.segment23)
1103 and (el_pg.segment24 is null or el_pg.segment24 = asg_pg.segment24)
1104 and (el_pg.segment25 is null or el_pg.segment25 = asg_pg.segment25)
1105 and (el_pg.segment26 is null or el_pg.segment26 = asg_pg.segment26)
1106 and (el_pg.segment27 is null or el_pg.segment27 = asg_pg.segment27)
1107 and (el_pg.segment28 is null or el_pg.segment28 = asg_pg.segment28)
1108 and (el_pg.segment29 is null or el_pg.segment29 = asg_pg.segment29)
1109 and (el_pg.segment30 is null or el_pg.segment30 = asg_pg.segment30)
1110 group by el.element_link_id;
1111 --
1112 -- local variables
1113 --
1114 v_assignment t_asg_rec;
1115 v_alu_tab t_alu_tab;
1116 v_asg_start_date date;
1117 v_asg_end_date date;
1118 v_people_group_id number;
1119 v_id_flex_num number;
1120 v_alu_start_date date;
1121 v_alu_end_date date;
1122 v_alu_term_date date;
1126 --
1123 v_counter number := 0;
1124 --
1125 begin
1127 -- Delete all the alus for the assignment
1128 --
1129 delete from pay_assignment_link_usages_f alu
1130 where alu.assignment_id = p_assignment_id;
1131 --
1132 --
1133 open csr_assignment(p_assignment_id);
1134 --
1135 -- get first assignment record to initialise variables
1136 --
1137 fetch csr_assignment into v_assignment;
1138 if csr_assignment%found then
1139 --
1140 -- set up variables for use in loop
1141 --
1142 v_asg_start_date := v_assignment.effective_start_date;
1143 v_asg_end_date := v_assignment.effective_end_date;
1144 v_people_group_id := v_assignment.people_group_id;
1145 v_id_flex_num := v_assignment.id_flex_num;
1146 --
1147 while csr_assignment%found loop
1148 --
1149 -- get next assignment record
1150 --
1151 fetch csr_assignment into v_assignment;
1152 --
1153 -- detect change of people group , non-contiguous people groups or
1154 -- that the last record has been read
1155 --
1156 if csr_assignment%notfound or not
1157 (v_assignment.people_group_id = v_people_group_id and
1158 v_assignment.effective_start_date = v_asg_end_date + 1) then
1159 --
1160 -- find all links that overlap with the assignment and have the same
1161 -- people group as the assignment
1162 --
1163 for v_link in csr_link(v_id_flex_num,
1164 v_assignment.business_group_id,
1165 v_people_group_id,
1166 v_asg_start_date,
1167 v_asg_end_date) loop
1168 --
1169 -- calculate the start date of the alu which is the greatest of
1170 -- the start dates of the link and assignment
1171 --
1172 v_alu_start_date := greatest(v_asg_start_date,
1173 v_link.effective_start_date);
1174 --
1175 -- find the termination date of the alu if the person has been
1176 -- terminated ie. taking inot account the termination processing
1177 -- rule of the element type. if no termination has taken place
1178 -- then the date returned is the end of time.
1179 -- nb. v_dummy_date is used to soak up some out parameters that
1180 -- are not required.
1181 --
1182 -- Bug 5202396.
1183 -- The check for termination rule caused a significant performance
1184 -- issue. Since ALU is only a part of the link eligibility rules,
1185 -- we can determine the end date with the link and the assignment.
1186 --
1187 /***
1188 hr_entry.entry_asg_pay_link_dates(p_assignment_id,
1189 v_link.element_link_id,
1190 v_alu_start_date,
1191 v_alu_term_date,
1192 v_dummy_date,
1193 v_dummy_date,
1194 v_dummy_date,
1195 v_dummy_date,
1196 false);
1197 ***/
1198 --
1199 -- calculate the end date of the alu which is the least of the
1200 -- end dates of the link and assignment.
1201 --
1202 v_alu_end_date := least(v_asg_end_date,
1203 v_link.effective_end_date);
1204 --
1205 -- Make sure that the alu start date is on or before the end date
1206 --
1207 if v_alu_start_date <= v_alu_end_date then
1208 --
1209 v_counter := v_counter + 1;
1210 --
1211 v_alu_tab.start_date(v_counter) := v_alu_start_date;
1212 v_alu_tab.end_date(v_counter) := v_alu_end_date;
1213 v_alu_tab.link_id(v_counter) := v_link.element_link_id;
1214 --
1215 end if;
1216 --
1217 end loop;
1218 --
1219 -- reset start and end dates
1220 --
1221 v_asg_start_date := v_assignment.effective_start_date;
1222 v_asg_end_date := v_assignment.effective_end_date;
1223 --
1224 else
1225 --
1226 -- increment end date of assignment
1227 --
1228 v_asg_end_date := v_assignment.effective_end_date;
1229 --
1230 end if;
1231 --
1232 -- save value for future comparison
1233 --
1234 v_people_group_id := v_assignment.people_group_id;
1235 v_id_flex_num := v_assignment.id_flex_num;
1236 --
1237 -- Create the ALUs in bulk
1238 --
1239 forall i in 1..v_counter
1240 insert into pay_assignment_link_usages_f
1241 (assignment_link_usage_id,
1242 effective_start_date,
1243 effective_end_date,
1244 element_link_id,
1245 assignment_id)
1246 values
1247 (
1248 pay_assignment_link_usages_s.nextval,
1249 v_alu_tab.start_date(i),
1250 v_alu_tab.end_date(i),
1251 v_alu_tab.link_id(i),
1252 p_assignment_id
1253 );
1254 --
1255 v_counter := 0;
1256 v_alu_tab.start_date.delete;
1257 v_alu_tab.end_date.delete;
1258 v_alu_tab.link_id.delete;
1259 --
1260 end loop;
1261 --
1262 end if;
1263 --
1264 close csr_assignment;
1265 --
1266 end rebuild_alus;
1267 --
1270 end PAY_ASG_LINK_USAGES_PKG;
1268 --------------------------------------------------------------------------------
1269 --
[Home] [Help]