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