1 PACKAGE BODY pay_core_upgrade_pkg AS
2 /* $Header: pycougpk.pkb 120.13.12010000.1 2008/07/27 22:23:46 appldev ship $ */
3
4 -- Cache Used by Sparse Matrix Run Result Value Purge upgrade.
5 g_leg_code_cached boolean := FALSE;
6 g_jur_name pay_input_values_f.name%type;
7
8
9 /* Name : upg_single_lat_bal_tab
10 Purpose : This procedure is used to upgrade to a single
11 latest balance table from a pay_assignment_latest_balabces
12 and pay_person_latest_balances.
13 Arguments :
14 Notes :
15 */
16 procedure upg_single_lat_bal_tab (p_person_id in number)
17 is
18 --
19 type t_def_bal_id is table of pay_latest_balances.defined_balance_id%type
20 index by binary_integer;
21 type t_asg_act_id is table of pay_latest_balances.assignment_action_id%type
22 index by binary_integer;
23 type t_asg_id is table of pay_latest_balances.assignment_id%type
24 index by binary_integer;
25 type t_per_id is table of pay_latest_balances.person_id%type
26 index by binary_integer;
27 type t_value is table of pay_latest_balances.value%type
28 index by binary_integer;
29 type t_lat_bal_id is table of pay_latest_balances.latest_balance_id%type
30 index by binary_integer;
31 type t_tu_id is table of pay_latest_balances.tax_unit_id%type
32 index by binary_integer;
33 type t_jc is table of pay_latest_balances.jurisdiction_code%type
34 index by binary_integer;
35 type t_oe_id is table of pay_latest_balances.original_entry_id%type
36 index by binary_integer;
37 type t_si is table of pay_latest_balances.source_id%type
38 index by binary_integer;
39 type t_st is table of pay_latest_balances.source_text%type
40 index by binary_integer;
41 type t_st2 is table of pay_latest_balances.source_text2%type
42 index by binary_integer;
43 type t_sn is table of pay_latest_balances.source_number%type
44 index by binary_integer;
45 type t_tg is table of pay_latest_balances.tax_group%type
46 index by binary_integer;
47 type t_pay_id is table of pay_latest_balances.payroll_id%type
48 index by binary_integer;
49 type t_cont_nm is table of ff_contexts.context_name%type
50 index by binary_integer;
51 type t_cont_vl is table of pay_balance_context_values.value%type
52 index by binary_integer;
53 --
54 l_def_bal_id t_def_bal_id;
55 l_asg_act_id t_asg_act_id;
56 l_e_asg_act_id t_asg_act_id;
57 l_p_asg_act_id t_asg_act_id;
58 l_asg_id t_asg_id;
59 l_per_id t_per_id;
60 l_value t_value;
61 l_e_value t_value;
62 l_p_value t_value;
63 l_lat_bal_id t_lat_bal_id;
64 l_cont_value t_cont_vl;
65 l_cont_name t_cont_nm;
66 --
67 l_def_bal_id_ins t_def_bal_id;
68 l_asg_act_id_ins t_asg_act_id;
69 l_e_asg_act_id_ins t_asg_act_id;
70 l_p_asg_act_id_ins t_asg_act_id;
71 l_asg_id_ins t_asg_id;
72 l_per_id_ins t_per_id;
73 l_value_ins t_value;
74 l_e_value_ins t_value;
75 l_p_value_ins t_value;
76 l_lat_bal_id_ins t_lat_bal_id;
77 l_tu_tab t_tu_id;
78 l_jc_tab t_jc;
79 l_oei_tab t_oe_id;
80 l_si_tab t_si;
81 l_st_tab t_st;
82 l_st2_tab t_st2;
83 l_sn_tab t_sn;
84 l_tg_tab t_tg;
85 l_pay_id_tab t_pay_id;
86 --
87 prev_latest_bal_id number;
88 free_def_cnt number;
89 curr_def_cnt number;
90 --
91 cursor c_get_cont(p_latest_bal_id in number)
92 is
93 select fc.context_name,
94 pbcv.value
95 from pay_balance_context_values pbcv,
96 ff_contexts fc
97 where pbcv.latest_balance_id = p_latest_bal_id
98 and pbcv.context_id = fc.context_id;
99 --
100 cursor c_get_asgs (p_person_id in number)
101 is
102 select distinct assignment_id
103 from per_all_assignments_f
104 where person_id = p_person_id;
105 --
106 begin
107 --
108 l_def_bal_id.delete;
109 l_asg_act_id.delete;
110 l_e_asg_act_id.delete;
111 l_p_asg_act_id.delete;
112 l_asg_id.delete;
113 l_per_id.delete;
114 l_value.delete;
115 l_e_value.delete;
116 l_p_value.delete;
117 l_lat_bal_id.delete;
118 l_cont_value.delete;
119 l_cont_name.delete;
120 l_def_bal_id_ins.delete;
121 l_asg_act_id_ins.delete;
122 l_e_asg_act_id_ins.delete;
123 l_p_asg_act_id_ins.delete;
124 l_asg_id_ins.delete;
125 l_per_id_ins.delete;
126 l_value_ins.delete;
127 l_e_value_ins.delete;
128 l_p_value_ins.delete;
129 l_lat_bal_id_ins.delete;
130 l_tu_tab.delete;
131 l_jc_tab.delete;
132 l_oei_tab.delete;
133 l_si_tab.delete;
134 l_st_tab.delete;
135 l_st2_tab.delete;
136 l_sn_tab.delete;
137 l_tg_tab.delete;
138 l_pay_id_tab.delete;
139 --
140 select /*+ ORDERED USE_NL(pplb pbcv fc) */
141 pplb.defined_balance_id,
142 pplb.assignment_action_id,
143 pplb.person_id,
144 pplb.value,
145 pplb.latest_balance_id,
146 pplb.expired_assignment_action_id,
147 pplb.expired_value,
148 pplb.prev_balance_value,
149 pplb.prev_assignment_action_id,
150 pbcv.value,
151 fc.context_name
152 bulk collect into
153 l_def_bal_id,
154 l_asg_act_id,
155 l_per_id,
156 l_value,
157 l_lat_bal_id,
158 l_e_asg_act_id,
159 l_e_value,
160 l_p_value,
161 l_p_asg_act_id,
162 l_cont_value,
163 l_cont_name
164 from pay_person_latest_balances pplb,
165 pay_balance_context_values pbcv,
166 ff_contexts fc
167 where pplb.person_id = p_person_id
168 and pplb.latest_balance_id = pbcv.latest_balance_id (+)
169 and nvl(pbcv.context_id, -1) = fc.context_id (+)
170 order by pplb.latest_balance_id;
171 --
172 prev_latest_bal_id := -1;
173 free_def_cnt := 1;
174 for i in 1..l_def_bal_id.count loop
175 --
176 if (prev_latest_bal_id <> l_lat_bal_id(i)) then
177 l_def_bal_id_ins(free_def_cnt) := l_def_bal_id(i);
178 l_asg_act_id_ins(free_def_cnt) := l_asg_act_id(i);
179 l_per_id_ins(free_def_cnt) := l_per_id(i);
180 l_value_ins(free_def_cnt) := l_value(i);
181 l_lat_bal_id_ins(free_def_cnt) := l_lat_bal_id(i);
182 l_e_asg_act_id_ins(free_def_cnt) := l_e_asg_act_id(i);
183 l_e_value_ins(free_def_cnt) := l_e_value(i);
184 l_p_value_ins(free_def_cnt) := l_p_value(i);
185 l_p_asg_act_id_ins(free_def_cnt) := l_p_asg_act_id(i);
186 l_tu_tab(free_def_cnt) := null;
187 l_jc_tab(free_def_cnt) := null;
188 l_oei_tab(free_def_cnt) := null;
189 l_si_tab(free_def_cnt) := null;
190 l_st_tab(free_def_cnt) := null;
191 l_st2_tab(free_def_cnt) := null;
192 l_sn_tab(free_def_cnt) := null;
193 l_tg_tab(free_def_cnt) := null;
194 l_pay_id_tab(free_def_cnt) := null;
195 --
196 curr_def_cnt := free_def_cnt;
197 free_def_cnt := free_def_cnt + 1;
198 prev_latest_bal_id := l_lat_bal_id(i);
199 end if;
200 --
201 if (l_cont_name(i) is not null) then
202 if (l_cont_name(i) = 'TAX_UNIT_ID') then
203 l_tu_tab(curr_def_cnt) := l_cont_value(i);
204 elsif (l_cont_name(i) = 'JURISDICTION_CODE') then
205 l_jc_tab(curr_def_cnt) := l_cont_value(i);
206 elsif (l_cont_name(i) = 'ORIGINAL_ENTRY_ID') then
207 l_oei_tab(curr_def_cnt) := l_cont_value(i);
208 elsif (l_cont_name(i) = 'SOURCE_ID') then
209 l_si_tab(curr_def_cnt) := l_cont_value(i);
210 elsif (l_cont_name(i) = 'SOURCE_TEXT') then
211 l_st_tab(curr_def_cnt) := l_cont_value(i);
212 elsif (l_cont_name(i) = 'SOURCE_TEXT2') then
213 l_st2_tab(curr_def_cnt) := l_cont_value(i);
214 elsif (l_cont_name(i) = 'SOURCE_NUMBER') then
215 l_sn_tab(curr_def_cnt) := l_cont_value(i);
216 elsif (l_cont_name(i) = 'TAX_GROUP') then
217 l_tg_tab(curr_def_cnt) := l_cont_value(i);
218 elsif (l_cont_name(i) = 'PAYROLL_ID') then
219 l_pay_id_tab(curr_def_cnt) := l_cont_value(i);
220 end if;
221 end if;
222 --
223 end loop;
224 --
225 forall i in 1..l_def_bal_id_ins.count
226 insert into pay_latest_balances
227 (latest_balance_id,
228 defined_balance_id,
229 assignment_action_id,
230 value,
231 person_id,
232 expired_assignment_action_id,
233 expired_value,
234 prev_assignment_action_id,
235 prev_balance_value,
236 tax_unit_id,
237 jurisdiction_code,
238 original_entry_id,
239 source_id,
240 source_text,
241 source_text2,
242 source_number,
243 tax_group,
244 payroll_id)
245 values (
246 l_lat_bal_id_ins(i),
247 l_def_bal_id_ins(i),
248 l_asg_act_id_ins(i),
249 l_value_ins(i),
250 l_per_id_ins(i),
251 l_e_asg_act_id_ins(i),
252 l_e_value_ins(i),
253 l_p_asg_act_id_ins(i),
254 l_p_value_ins(i),
255 l_tu_tab(i),
256 l_jc_tab(i),
257 l_oei_tab(i),
258 l_si_tab(i),
259 l_st_tab(i),
260 l_st2_tab(i),
261 l_sn_tab(i),
262 l_tg_tab(i),
263 l_pay_id_tab(i));
264 --
265 for asgrec in c_get_asgs(p_person_id) loop
266 --
267 l_def_bal_id.delete;
268 l_asg_act_id.delete;
269 l_e_asg_act_id.delete;
270 l_p_asg_act_id.delete;
271 l_asg_id.delete;
272 l_per_id.delete;
273 l_value.delete;
274 l_e_value.delete;
275 l_p_value.delete;
276 l_lat_bal_id.delete;
277 l_cont_value.delete;
278 l_cont_name.delete;
279 l_def_bal_id_ins.delete;
280 l_asg_act_id_ins.delete;
281 l_e_asg_act_id_ins.delete;
282 l_p_asg_act_id_ins.delete;
283 l_asg_id_ins.delete;
284 l_per_id_ins.delete;
285 l_value_ins.delete;
286 l_e_value_ins.delete;
287 l_p_value_ins.delete;
288 l_lat_bal_id_ins.delete;
289 l_tu_tab.delete;
290 l_jc_tab.delete;
291 l_oei_tab.delete;
292 l_si_tab.delete;
293 l_st_tab.delete;
294 l_st2_tab.delete;
295 l_sn_tab.delete;
296 l_tg_tab.delete;
297 l_pay_id_tab.delete;
298 --
299 select /*+ ORDERED USE_NL(palb pbcv fc) */
300 palb.defined_balance_id,
301 palb.assignment_action_id,
302 palb.assignment_id,
303 palb.value,
304 palb.latest_balance_id,
305 palb.expired_assignment_action_id,
306 palb.expired_value,
307 palb.prev_balance_value,
308 palb.prev_assignment_action_id,
309 pbcv.value,
310 fc.context_name
311 bulk collect into
312 l_def_bal_id,
313 l_asg_act_id,
314 l_asg_id,
315 l_value,
316 l_lat_bal_id,
317 l_e_asg_act_id,
318 l_e_value,
319 l_p_value,
320 l_p_asg_act_id,
321 l_cont_value,
322 l_cont_name
323 from pay_assignment_latest_balances palb,
324 pay_balance_context_values pbcv,
325 ff_contexts fc
326 where palb.assignment_id = asgrec.assignment_id
327 and palb.latest_balance_id = pbcv.latest_balance_id (+)
328 and nvl(pbcv.context_id, -1) = fc.context_id (+)
329 order by palb.latest_balance_id;
330 --
331 prev_latest_bal_id := -1;
332 free_def_cnt := 1;
333 for i in 1..l_def_bal_id.count loop
334 --
335 if (prev_latest_bal_id <> l_lat_bal_id(i)) then
336 l_def_bal_id_ins(free_def_cnt) := l_def_bal_id(i);
337 l_asg_act_id_ins(free_def_cnt) := l_asg_act_id(i);
338 l_asg_id_ins(free_def_cnt) := l_asg_id(i);
339 l_per_id_ins(free_def_cnt) := p_person_id;
340 l_value_ins(free_def_cnt) := l_value(i);
341 l_lat_bal_id_ins(free_def_cnt) := l_lat_bal_id(i);
342 l_e_asg_act_id_ins(free_def_cnt) := l_e_asg_act_id(i);
343 l_e_value_ins(free_def_cnt) := l_e_value(i);
344 l_p_value_ins(free_def_cnt) := l_p_value(i);
345 l_p_asg_act_id_ins(free_def_cnt) := l_p_asg_act_id(i);
346 l_tu_tab(free_def_cnt) := null;
347 l_jc_tab(free_def_cnt) := null;
348 l_oei_tab(free_def_cnt) := null;
349 l_si_tab(free_def_cnt) := null;
350 l_st_tab(free_def_cnt) := null;
351 l_st2_tab(free_def_cnt) := null;
352 l_sn_tab(free_def_cnt) := null;
353 l_tg_tab(free_def_cnt) := null;
354 l_pay_id_tab(free_def_cnt) := null;
355 --
356 curr_def_cnt := free_def_cnt;
357 free_def_cnt := free_def_cnt + 1;
358 prev_latest_bal_id := l_lat_bal_id(i);
359 end if;
360 --
361 if (l_cont_name(i) is not null) then
362 if (l_cont_name(i) = 'TAX_UNIT_ID') then
363 l_tu_tab(curr_def_cnt) := l_cont_value(i);
364 elsif (l_cont_name(i) = 'JURISDICTION_CODE') then
365 l_jc_tab(curr_def_cnt) := l_cont_value(i);
366 elsif (l_cont_name(i) = 'ORIGINAL_ENTRY_ID') then
367 l_oei_tab(curr_def_cnt) := l_cont_value(i);
368 elsif (l_cont_name(i) = 'SOURCE_ID') then
369 l_si_tab(curr_def_cnt) := l_cont_value(i);
370 elsif (l_cont_name(i) = 'SOURCE_TEXT') then
371 l_st_tab(curr_def_cnt) := l_cont_value(i);
372 elsif (l_cont_name(i) = 'SOURCE_TEXT2') then
373 l_st2_tab(curr_def_cnt) := l_cont_value(i);
374 elsif (l_cont_name(i) = 'SOURCE_NUMBER') then
375 l_sn_tab(curr_def_cnt) := l_cont_value(i);
376 elsif (l_cont_name(i) = 'TAX_GROUP') then
377 l_tg_tab(curr_def_cnt) := l_cont_value(i);
378 elsif (l_cont_name(i) = 'PAYROLL_ID') then
379 l_pay_id_tab(curr_def_cnt) := l_cont_value(i);
380 end if;
381 end if;
382 end loop;
383 --
384 forall i in 1..l_def_bal_id_ins.count
385 insert into pay_latest_balances
386 (latest_balance_id,
387 defined_balance_id,
388 assignment_action_id,
389 value,
390 person_id,
391 assignment_id,
392 expired_assignment_action_id,
393 expired_value,
394 prev_assignment_action_id,
395 prev_balance_value,
396 tax_unit_id,
397 jurisdiction_code,
401 source_text2,
398 original_entry_id,
399 source_id,
400 source_text,
402 source_number,
403 tax_group,
404 payroll_id)
405 values (
406 l_lat_bal_id_ins(i),
407 l_def_bal_id_ins(i),
408 l_asg_act_id_ins(i),
409 l_value_ins(i),
410 l_per_id_ins(i),
411 l_asg_id_ins(i),
412 l_e_asg_act_id_ins(i),
413 l_e_value_ins(i),
414 l_p_asg_act_id_ins(i),
415 l_p_value_ins(i),
416 l_tu_tab(i),
417 l_jc_tab(i),
418 l_oei_tab(i),
419 l_si_tab(i),
420 l_st_tab(i),
421 l_st2_tab(i),
422 l_sn_tab(i),
423 l_tg_tab(i),
424 l_pay_id_tab(i));
425 --
426 end loop;
427 --
428 end upg_single_lat_bal_tab;
429 --
430 /* Name : upg_retro_proc_det_frm_ee
431 Purpose : This procedure is used to upgrade the entry process
432 details table. This is a new table used by the Retropay
433 process.
434 Arguments :
435 Notes :
436 */
437 procedure upg_retro_proc_det_frm_ee (p_asg_id in number)
438 is
439 cursor get_retro_ee(p_asg_id in number) is
440 select pee.element_entry_id,
441 pee.element_type_id,
442 pee.creator_type,
443 pee.source_id,
444 pee.source_asg_action_id,
445 pee.source_run_type
446 from pay_element_entries_f pee
447 where pee.creator_type in ('RR', 'EE')
448 and pee.assignment_id = p_asg_id;
449 --
450 l_run_result_id pay_entry_process_details.run_result_id%type;
451 l_src_entry_id pay_entry_process_details.source_entry_id%type;
452 l_ppath pay_entry_process_details.process_path%type;
453 l_src_asg_act_id pay_entry_process_details.source_asg_action_id%type;
454 l_src_et_id pay_entry_process_details.source_element_type_id%type;
455 l_dummy varchar2(2);
456 l_loc_aa_id number;
457 l_loc_src_aa_id number;
458 l_upgrade boolean;
459 begin
460 --
461 for eerec in get_retro_ee(p_asg_id) loop
462 --
463 begin
464 --
465 select ''
466 into l_dummy
467 from pay_entry_process_details
468 where element_entry_id = eerec.element_entry_id;
469 --
470 l_src_asg_act_id := eerec.source_asg_action_id;
471 --
472 if (eerec.creator_type = 'RR') then
473 l_run_result_id:= eerec.source_id;
474 begin
475 --
476 select prr.source_id,
477 prr.element_type_id,
478 pay_core_utils.get_process_path(prr.assignment_action_id),
479 prr.assignment_action_id,
480 paa.source_action_id
481 into l_src_entry_id,
482 l_src_et_id,
483 l_ppath,
484 l_loc_aa_id,
485 l_loc_src_aa_id
486 from pay_run_results prr,
487 pay_assignment_actions paa
488 where prr.run_result_id = l_run_result_id
489 and prr.assignment_action_id = paa.assignment_action_id;
490 --
491 exception
492 when no_data_found then
493 pay_core_utils.assert_condition(
494 'upg_retro_proc_det_frm_ee:1',
495 1 = 2);
496 --
497 end;
498 if (l_src_asg_act_id is null) then
499 if (l_loc_src_aa_id is null) then
500 l_src_asg_act_id := l_loc_aa_id;
501 else
502 while (l_loc_src_aa_id is not null) loop
503 select assignment_action_id,
504 source_action_id
505 into l_loc_aa_id,
506 l_loc_src_aa_id
507 from pay_assignment_actions
508 where assignment_action_id = l_loc_src_aa_id;
509 end loop;
510 l_src_asg_act_id := l_loc_aa_id;
511 end if;
512 update pay_element_entries_f
513 set source_asg_action_id = l_src_asg_act_id
514 where element_entry_id = eerec.element_entry_id;
515 end if;
516 else
517 l_run_result_id:= null;
518 l_src_entry_id := eerec.source_id;
519 --
520 if (eerec.element_type_id is null) then
521 pay_core_utils.assert_condition(
522 'upg_retro_proc_det_frm_ee:4',
523 1 = 2);
524 end if;
525 --
526 begin
530 pay_element_links_f pel,
527 select distinct pet.element_type_id
528 into l_src_et_id
529 from pay_element_types_f pet,
531 pay_element_entries_f pee
532 where pee.element_entry_id = l_src_entry_id
533 and pee.element_link_id = pel.element_link_id
534 and pel.element_type_id = pet.element_type_id;
535 --
536 /* This double checks the value. It could
537 be an indriect thus have a different
538 element type
539 */
540 if (l_src_et_id <> eerec.element_type_id) then
541 declare
542 l_retro_et_cnt number;
543 begin
544 select count(distinct(element_type_id))
545 into l_retro_et_cnt
546 from pay_element_types_f
547 where retro_summ_ele_id = eerec.element_type_id;
548 --
549 if (l_retro_et_cnt = 0) then
550 l_src_et_id := eerec.element_type_id;
551 elsif (l_retro_et_cnt = 1) then
552 select distinct(element_type_id)
553 into l_src_et_id
554 from pay_element_types_f
555 where retro_summ_ele_id = eerec.element_type_id;
556 else
557 --pay_core_utils.assert_condition(
558 -- 'upg_retro_proc_det_frm_ee:8',
559 -- 1 = 2);
560 --
561 -- Since there can be many then take the first one.
562 select distinct(element_type_id)
563 into l_src_et_id
564 from pay_element_types_f
565 where retro_summ_ele_id = eerec.element_type_id
566 and rownum = 1;
567 end if;
568 --
569 end;
570 end if;
571 --
572 exception
573 when no_data_found then
574 --
575 -- OK we're in s difficult position try to
576 -- find a match for this element type.
577 -- Since the original element entry has been
578 -- deleted.
579 declare
580 l_retro_et_cnt number;
581 begin
582 select count(distinct(element_type_id))
583 into l_retro_et_cnt
584 from pay_element_types_f
585 where retro_summ_ele_id = eerec.element_type_id;
586 --
587 if (l_retro_et_cnt = 0) then
588 l_src_et_id := eerec.element_type_id;
589 else
590 select distinct element_type_id
591 into l_src_et_id
592 from pay_element_types_f
593 where retro_summ_ele_id = eerec.element_type_id
594 and rownum = 1;
595 end if;
596 end;
597 end;
598 begin
599 select pay_core_utils.get_process_path(assignment_action_id)
600 into l_ppath
601 from pay_assignment_actions
602 where source_action_id = l_src_asg_act_id
603 and run_type_id = eerec.source_run_type
604 and rownum = 1;
605 --
606 exception
607 when no_data_found then
608 l_ppath := '.';
609 end;
610 end if;
611 --
612 --
613 update pay_entry_process_details
614 set run_result_id = l_run_result_id,
615 source_entry_id = l_src_entry_id,
616 process_path = l_ppath,
617 source_asg_action_id = l_src_asg_act_id,
618 source_element_type_id = l_src_et_id
619 where element_entry_id = eerec.element_entry_id;
620 --
621 exception
622 when no_data_found then
623 l_src_asg_act_id := eerec.source_asg_action_id;
624 --
625 if (eerec.creator_type = 'RR') then
626 l_run_result_id:= eerec.source_id;
627 begin
628 --
629 select prr.source_id,
630 prr.element_type_id,
631 pay_core_utils.get_process_path(prr.assignment_action_id),
632 prr.assignment_action_id,
633 paa.source_action_id
634 into l_src_entry_id,
635 l_src_et_id,
636 l_ppath,
637 l_loc_aa_id,
638 l_loc_src_aa_id
639 from pay_run_results prr,
643 --
640 pay_assignment_actions paa
641 where prr.run_result_id = l_run_result_id
642 and prr.assignment_action_id = paa.assignment_action_id;
644 exception
645 when no_data_found then
646 pay_core_utils.assert_condition(
647 'upg_retro_proc_det_frm_ee:3',
648 1 = 2);
649 --
650 end;
651 --
652 if (l_src_asg_act_id is null) then
653 if (l_loc_src_aa_id is null) then
654 l_src_asg_act_id := l_loc_aa_id;
655 else
656 while (l_loc_src_aa_id is not null) loop
657 select assignment_action_id,
658 source_action_id
659 into l_loc_aa_id,
660 l_loc_src_aa_id
661 from pay_assignment_actions
662 where assignment_action_id = l_loc_src_aa_id;
663 end loop;
664 l_src_asg_act_id := l_loc_aa_id;
665 end if;
666 update pay_element_entries_f
667 set source_asg_action_id = l_src_asg_act_id
668 where element_entry_id = eerec.element_entry_id;
669 end if;
670 --
671 else
672 l_run_result_id:= null;
673 l_src_entry_id := eerec.source_id;
674 --
675 if (eerec.element_type_id is null) then
676 pay_core_utils.assert_condition(
677 'upg_retro_proc_det_frm_ee:4',
678 1 = 2);
679 end if;
680 --
681 begin
682 select distinct pet.element_type_id
683 into l_src_et_id
684 from pay_element_types_f pet,
685 pay_element_links_f pel,
686 pay_element_entries_f pee
687 where pee.element_entry_id = l_src_entry_id
688 and pee.element_link_id = pel.element_link_id
689 and pel.element_type_id = pet.element_type_id;
690 --
691 /* This double checks the value. It could
692 be an indriect thus have a different
693 element type
694 */
695 if (l_src_et_id <> eerec.element_type_id) then
696 declare
697 l_retro_et_cnt number;
698 begin
699 select count(distinct(element_type_id))
700 into l_retro_et_cnt
701 from pay_element_types_f
702 where retro_summ_ele_id = eerec.element_type_id;
703 -- and element_type_id = l_src_et_id;
704 --
705 if (l_retro_et_cnt = 0) then
706 l_src_et_id := eerec.element_type_id;
707 elsif (l_retro_et_cnt = 1) then
708 select distinct(element_type_id)
709 into l_src_et_id
710 from pay_element_types_f
711 where retro_summ_ele_id = eerec.element_type_id;
712 -- and element_type_id = l_src_et_id;
713 else
714 -- pay_core_utils.assert_condition(
715 -- 'upg_retro_proc_det_frm_ee:9',
716 -- 1 = 2);
717 --
718 -- Since there can be many then take the first one.
719 select distinct(element_type_id)
720 into l_src_et_id
721 from pay_element_types_f
722 where retro_summ_ele_id = eerec.element_type_id
723 and rownum = 1;
724 end if;
725 --
726 end;
727 end if;
728 --
729 exception
730 when no_data_found then
731 --
732 -- OK we're in a difficult position try to
733 -- find a match for this element type.
734 -- Since the original element entry has been
735 -- deleted.
736 declare
737 l_retro_et_cnt number;
738 begin
739 select count(distinct(element_type_id))
740 into l_retro_et_cnt
741 from pay_element_types_f
742 where retro_summ_ele_id = eerec.element_type_id;
743 --
744 if (l_retro_et_cnt = 0) then
745 l_src_et_id := eerec.element_type_id;
746 else
747 select distinct element_type_id
748 into l_src_et_id
749 from pay_element_types_f
750 where retro_summ_ele_id = eerec.element_type_id
751 and rownum = 1;
752 end if;
753 end;
754 end;
755 begin
756 select pay_core_utils.get_process_path(assignment_action_id)
757 into l_ppath
758 from pay_assignment_actions
759 where source_action_id = l_src_asg_act_id
760 and run_type_id = eerec.source_run_type
761 and rownum = 1;
762 --
763 exception
764 when no_data_found then
765 l_ppath := '.';
766 end;
767 end if;
768 --
769 insert into pay_entry_process_details
770 (element_entry_id,
771 run_result_id,
772 source_entry_id,
773 process_path,
774 source_asg_action_id,
775 source_element_type_id
776 )
777 values
778 (eerec.element_entry_id,
779 l_run_result_id,
780 l_src_entry_id,
781 l_ppath,
782 l_src_asg_act_id,
783 l_src_et_id);
784 --
785 end;
786 --
787 end loop;
788 --
789 -- end if;
790 --
791 end upg_retro_proc_det_frm_ee;
792 --
793 /* Name : upg_retro_proc_det_frm_ee
794 Purpose : This procedure is used to qualify the object for the
795 upgrade.
796 Arguments :
797 Notes :
798 */
799 procedure qual_retro_proc_det_frm_ee(p_object_id in number,
800 p_qualified out nocopy varchar2
801 )
802 is
803 l_dummy varchar2(2);
804 l_qualifier varchar2(10);
805 begin
806 --
807 begin
808 select ''
809 into l_dummy
810 from dual
811 where exists (select ''
812 from pay_payroll_actions ppa,
813 pay_assignment_actions paa
814 where paa.assignment_id = p_object_id
815 and paa.payroll_action_id = ppa.payroll_action_id
816 and action_type = 'L');
817 l_qualifier := 'Y';
818 exception
819 when no_data_found then
820 l_qualifier := 'N';
821 end;
822 p_qualified := l_qualifier;
823 --
824 end qual_retro_proc_det_frm_ee;
825 --
826 /* Name : chk_retro_by_ele_exists
827 Purpose : Find out if any Retropay by Elements exists.
831 Notes :
828 This procedure is used to decide if a concurrent
829 request is needed to run an upgrade.
830 Arguments :
832 */
833 procedure chk_retro_by_ele_exists(p_exists out nocopy varchar2)
834 is
835 l_count number;
836 begin
837 --
838 select count(*)
839 into l_count
840 from pay_payroll_actions
841 where action_type = 'L';
842 --
843 if (l_count = 0) then
844 p_exists := 'FALSE';
845 else
846 p_exists := 'TRUE';
847 end if;
848 --
849 exception
850 when no_data_found then
851 p_exists := 'FALSE';
852 end chk_retro_by_ele_exists;
853 --
854 procedure chk_qpay_inclusions_exist (p_qpay_inclusions_exist out nocopy varchar2)
855 is
856 --
857 cursor csr_qpay_inclusions
858 is
859 select 'TRUE'
860 from pay_quickpay_inclusions
861 where rownum = 1;
862 --
863 l_inclusions_exist varchar2(10) := 'FALSE';
864 --
865 begin
866 --
867 open csr_qpay_inclusions;
868 fetch csr_qpay_inclusions into l_inclusions_exist;
869 close csr_qpay_inclusions;
870 --
871 p_qpay_inclusions_exist := l_inclusions_exist;
872 --
873 end chk_qpay_inclusions_exist;
874 --
875 procedure upg_qpay_excl_tab (p_assignment_id in number)
876 is
877 --
878 type num_tab is table of number(15) index by binary_integer;
879 --
880 asgt_action_ids num_tab;
881 element_entry_ids num_tab;
882 --
883 cursor c_qp_exclusions (p_asg_id in number)
884 is
885 SELECT /*+ ORDERED
886 USE_NL (ASGT_ACTION, EE, ET)
887 INDEX(
888 EE PAY_ELEMENT_ENTRIES_F_N50,
889 ET PAY_ELEMENT_TYPES_F_PK)
890 */
891 DISTINCT ASGT_ACTION.assignment_action_id, EE.element_entry_id
892 FROM pay_assignment_actions ASGT_ACTION,
893 pay_payroll_actions PAY_ACTION,
894 pay_element_entries_f EE,
895 pay_element_types_f ET
896 WHERE ASGT_ACTION.assignment_id = p_asg_id
897 AND ASGT_ACTION.payroll_action_id = PAY_ACTION.payroll_action_id
898 AND PAY_ACTION.action_type = 'Q'
899 AND ASGT_ACTION.source_action_id is null
900 AND ASGT_ACTION.assignment_id = EE.assignment_id
901 AND EE.effective_start_date <= PAY_ACTION.date_earned
902 AND EE.effective_end_date >= DECODE (
903 ET.proration_group_id,
904 null, PAY_ACTION.date_earned,
905 pay_interpreter_pkg.prorate_start_date (
906 ASGT_ACTION.assignment_action_id, ET.proration_group_id
907 )
908 )
909 AND ET.element_type_id = EE.element_type_id
910 AND PAY_ACTION.date_earned BETWEEN ET.effective_start_date
911 AND ET.effective_end_date
912 --
913 -- Create exclusions for all entries that do not exist in
914 -- PAY_QUICKPAY_INCLUSIONS...
915 --
916 AND NOT EXISTS (
917 SELECT 'x'
918 FROM pay_quickpay_inclusions qi
919 WHERE qi.assignment_action_id = ASGT_ACTION.assignment_action_id
920 AND qi.element_entry_id = EE.element_entry_id
921 )
922 --
923 -- The QuickPay process will be modified to always ignore entries whose
924 -- element type has a process_in_run_flag of 'N', therefore these can also be
925 -- ignored...
926 --
927 AND ET.process_in_run_flag = 'Y'
928 --
929 -- The QuickPay process will be modified to always ignore balance adjustments,
930 -- replacement adjustments and additive adjustments, therefore these can also
931 -- be ignored...
932 --
933 AND EE.entry_type NOT IN ('B', 'A', 'R')
934 --
935 -- The QuickPay process will be modified to ignore nonrecurring entries that
936 -- have already been processed, therefore we only want to create exclusions for
937 -- nonrecurring entries that have not been processed...
938 --
939 AND ( ( ( (ET.processing_type = 'N'
940 )
941 --
942 -- Recurring additional or override entries are handled as if they
943 -- were non-recurring.
944 --
945 OR ( ET.processing_type = 'R'
946 AND EE.entry_type <> 'E'
947 )
948 )
949 AND (NOT EXISTS (SELECT null
950 FROM pay_run_results pr1
951 WHERE pr1.source_id = EE.element_entry_id
952 AND pr1.source_type = 'E'
953 AND pr1.status <> 'U'
954 )
955 OR EXISTS (SELECT null
956 FROM pay_run_results pr1
957 WHERE pr1.source_id = EE.element_entry_id
958 AND pr1.source_type = 'E'
959 AND pr1.status = 'U'
960 )
961 )
962 )
963 --
964 -- Exclude other recurring entries.
965 -- i.e. Those which are not additional or overrides entries.
966 --
967 OR ( ET.processing_type = 'R'
968 AND EE.entry_type = 'E'
969 )
970 );
971 --
972 begin
973 --
974 open c_qp_exclusions(p_assignment_id);
978 bulk collect into asgt_action_ids, element_entry_ids limit 100;
975 loop
976 --
977 fetch c_qp_exclusions
979 --
980 forall i in 1..asgt_action_ids.COUNT
981 insert into pay_quickpay_exclusions (
982 assignment_action_id,
983 element_entry_id,
984 last_update_date,
985 last_updated_by,
986 created_by,
987 creation_date
988 )
989 values (
990 asgt_action_ids(i),
991 element_entry_ids(i),
992 SYSDATE,
993 1,
994 1,
995 SYSDATE
996 );
997 --
998 exit when c_qp_exclusions%notfound;
999 --
1000 end loop;
1001 close c_qp_exclusions;
1002 --
1003 end upg_qpay_excl_tab;
1004 --
1005 /* Name : qual_qpay_excl_tab
1006 Purpose : This procedure is used to qualify an assignment for the
1007 QuickPay Exclusions upgrade.
1008 Arguments :
1009 Notes :
1010 */
1011 procedure qual_qpay_excl_tab(p_object_id in number,
1012 p_qualified out nocopy varchar2
1013 )
1014 is
1015 l_dummy varchar2(2);
1016 l_qualifier varchar2(10);
1017 begin
1018 --
1019 begin
1020 select ''
1021 into l_dummy
1022 from dual
1023 where exists (select ''
1024 from pay_payroll_actions ppa,
1025 pay_assignment_actions paa
1026 where paa.assignment_id = p_object_id
1027 and paa.payroll_action_id = ppa.payroll_action_id
1028 and ppa.action_type = 'Q');
1029 l_qualifier := 'Y';
1030 exception
1031 when no_data_found then
1032 l_qualifier := 'N';
1033 end;
1034 p_qualified := l_qualifier;
1035 --
1036 end qual_qpay_excl_tab;
1037 --
1038 /* Name : qual_enable_sparse_matrix
1039 Purpose : This procedure is used to qualify that no persons
1040 need upgrading (the upgrade is used for the customer
1041 to indicate that they are happy for use of spars ematrix)
1042 Arguments :
1043 Notes :
1044 */
1045 procedure qual_enable_sparse_matrix(p_object_id in number,
1046 p_qualified out nocopy varchar2
1047 )
1048 is
1049 begin
1050 --
1051 p_qualified := 'N';
1052 --
1053 end qual_enable_sparse_matrix;
1054 --
1055 /* Name : upg_enable_sparse_matrix
1056 Purpose : This procedure is never called
1057 Arguments :
1058 Notes :
1059 */
1060 --
1061 procedure upg_enable_sparse_matrix (p_person_id in number)
1062 is
1063 begin
1064 --
1065 null;
1066 --
1067 end upg_enable_sparse_matrix;
1068 --
1069 /* Name : qual_sparse_matrix_asg
1070 Purpose : This procedure is used to qualify the assignment for
1071 the sparse matrix upgrade.
1072 Arguments :
1073 Notes :
1074 */
1075 procedure qual_sparse_matrix_asg(p_object_id in number,
1076 p_qualified out nocopy varchar2
1077 )
1078 is
1079 l_dummy varchar2(2);
1080 l_qualifier varchar2(10);
1081 begin
1082 --
1083 begin
1084 select ''
1085 into l_dummy
1086 from dual
1087 where exists (select ''
1088 from pay_payroll_actions ppa,
1089 pay_assignment_actions paa
1090 where paa.assignment_id = p_object_id
1091 and paa.payroll_action_id = ppa.payroll_action_id
1092 and action_type in ('R', 'Q', 'B', 'V', 'I'));
1093
1094 l_qualifier := 'Y';
1095 exception
1096 when no_data_found then
1097 l_qualifier := 'N';
1098 end;
1099 p_qualified := l_qualifier;
1100 --
1101 end qual_sparse_matrix_asg;
1102 --
1103 /* Name : upg_sparse_matrix_rrvs
1104 Purpose : This procedure is used to delete any null value run
1105 result values for an assignment
1106 NB Other than the Jurisidiction code result value
1107 Arguments :
1108 Notes :
1109 */
1110 --
1111 procedure upg_sparse_matrix_rrvs (p_assignment_id in number)
1112 is
1113 --
1114 type t_asg_act_id is table of pay_assignment_actions.assignment_action_id%type
1118 asgt_action_ids t_asg_act_id;
1115 index by binary_integer;
1116 type date_tab is table of date index by binary_integer;
1117 --
1119 run_dates date_tab;
1120 --
1121 l_leg_code per_business_groups_perf.legislation_code%type;
1122 l_bus_grp per_business_groups_perf.business_group_id%type;
1123 l_found boolean := FALSE;
1124 l_status varchar2(30);
1125 --
1126 cursor c_get_aas (p_assignment_id in number) is
1127 select /*+ ORDERED use_nl(aa pa)*/
1128 aa.assignment_action_id, pa.effective_date
1129 from pay_assignment_actions aa,
1130 pay_payroll_actions pa
1131 where aa.assignment_id = p_assignment_id
1132 and pa.payroll_action_id = aa.payroll_action_id
1133 and pa.action_type in ('R', 'Q', 'B', 'V', 'I');
1134 --
1135 begin
1136 --
1137 if (g_leg_code_cached = FALSE) then
1138 --
1139 select pbg.legislation_code, pbg.business_group_id
1140 into l_leg_code, l_bus_grp
1141 from per_all_assignments_f asg,
1142 per_business_groups_perf pbg
1143 where asg.assignment_id = p_assignment_id
1144 and pbg.business_group_id = asg.business_group_id
1145 and rownum = 1;
1146 --
1147 pay_core_utils.get_leg_context_iv_name('JURISDICTION_CODE', l_leg_code,
1148 g_jur_name, l_found);
1149 --
1150 if (l_found = FALSE) then
1151 g_jur_name := 'Jurisdiction';
1152 end if;
1153 --
1154 pay_core_utils.get_upgrade_status(l_bus_grp,
1155 'RR_SPARSE_JC',
1156 l_status);
1157 if (upper(l_status)='Y') then
1158 l_found := FALSE;
1159 else
1160 l_found := TRUE;
1161 end if;
1162 --
1163 g_leg_code_cached := TRUE;
1164 --
1165 end if;
1166 --
1167 open c_get_aas(p_assignment_id);
1168 loop
1169 --
1170 fetch c_get_aas
1171 bulk collect into asgt_action_ids, run_dates limit 200;
1172 --
1173 if (l_found = TRUE) then
1174 --
1175 forall i in 1..asgt_action_ids.COUNT
1176 delete from pay_run_result_values rrv
1177 where result_value is null
1178 and run_result_id in
1179 (select rr.run_result_id
1180 from pay_run_results rr
1181 where rr.assignment_action_id = asgt_action_ids(i))
1182 and not exists
1183 (select 1
1184 from pay_input_values_f iv
1185 where iv.input_value_id = rrv.input_value_id
1186 and run_dates(i) between iv.effective_start_date
1187 and iv.effective_end_date
1188 and iv.name = g_jur_name);
1189 --
1190 else
1191 --
1192 forall i in 1..asgt_action_ids.COUNT
1193 delete from pay_run_result_values rrv
1194 where result_value is null
1195 and run_result_id in
1196 (select rr.run_result_id
1197 from pay_run_results rr
1198 where rr.assignment_action_id = asgt_action_ids(i));
1199 --
1200 end if;
1201 --
1202 exit when c_get_aas%notfound;
1203 --
1204 end loop;
1205 close c_get_aas;
1206 --
1207 end upg_sparse_matrix_rrvs;
1208 --
1209 /* Name : qual_sparse_matrix_asg
1210 Purpose : This procedure is used to qualify the person for
1211 the latest balance upgrade to process groups.
1212 Arguments :
1213 Notes :
1214 */
1215 procedure qual_latest_bal_pg(p_object_id in number,
1216 p_qualified out nocopy varchar2
1217 )
1218 is
1219 l_dummy varchar2(2);
1220 l_qualifier varchar2(10);
1221 begin
1222 --
1223 begin
1224 select ''
1225 into l_dummy
1226 from dual
1227 where exists (select ''
1228 from pay_latest_balances plb
1229 where plb.person_id = p_object_id
1230 );
1231
1232 l_qualifier := 'Y';
1233 exception
1234 when no_data_found then
1235 l_qualifier := 'N';
1236 end;
1237 p_qualified := l_qualifier;
1238 --
1239 end qual_latest_bal_pg;
1240 --
1241 /* Name : upgrade_latest_bal_pg
1242 Purpose : This performs the upgrade of pay_latest_balances from person to
1243 process group
1244 Arguments :
1245 Notes :
1246 */
1247 Procedure upgrade_latest_bal_pg (p_person_id IN NUMBER) is
1248
1249 cursor c_process_group_id(p_person_id number) is
1250 select object_group_id
1251 from pay_object_groups
1252 where source_id = p_person_id
1253 and source_type = 'PPF';
1254
1255 l_process_group_id number;
1256 l_process_group_id2 number;
1257
1258 begin
1259 -- if only 1 process group exists for the person its valid to upgrade the person balance to process group
1260 -- if more than 1 exists then the value isn't valid for any one process group so trash the person balance
1261 open c_process_group_id(p_person_id);
1262 Fetch c_process_group_id into l_process_group_id;
1266 -- perform upgrade
1263 Fetch c_process_group_id into l_process_group_id2;
1264 close c_process_group_id;
1265
1267 if l_process_group_id2 is null then
1268 update pay_latest_balances
1269 set process_group_id = l_process_group_id
1270 where person_id = p_person_id
1271 and assignment_id is null
1272 and process_group_id is null
1273 and defined_balance_id in (
1274 select db.defined_balance_id from
1275 pay_balance_dimensions d,
1276 pay_defined_balances db
1277 where d.balance_dimension_id = db.balance_dimension_id
1278 and d.dimension_type = 'O'
1279 and d.dimension_level = 'PG');
1280
1281 else
1282 delete from pay_latest_balances
1283 where assignment_id is null
1284 and person_id = p_person_id
1285 and process_group_id is null
1286 and defined_balance_id in (
1287 select db.defined_balance_id from pay_balance_dimensions d, pay_defined_balances db
1288 where d.balance_dimension_id = db.balance_dimension_id
1289 and d.dimension_type = 'O'
1290 and d.dimension_level = 'PG');
1291
1292 end if;
1293
1294 end upgrade_latest_bal_pg;
1295 --
1296 /* Name : upg_timedef_baldate
1297 Purpose : This procedure is used to qualify the assignment for
1298 the time definition and balance date upgrade.
1299 Arguments :
1300 Notes :
1301 */
1302 procedure qual_timedef_baldate(p_object_id in number,
1303 p_qualified out nocopy varchar2
1304 )
1305 is
1306 l_dummy varchar2(2);
1307 l_qualifier varchar2(10);
1308 begin
1309 --
1310 begin
1311 select ''
1312 into l_dummy
1313 from dual
1314 where exists (select ''
1315 from pay_payroll_actions ppa,
1316 pay_assignment_actions paa
1317 where paa.assignment_id = p_object_id
1318 and paa.payroll_action_id = ppa.payroll_action_id
1319 and action_type in ('R', 'Q', 'B', 'I'));
1320 l_qualifier := 'Y';
1321 exception
1322 when no_data_found then
1323 l_qualifier := 'N';
1324 end;
1325 p_qualified := l_qualifier;
1326 --
1327 end qual_timedef_baldate;
1328 --
1329 /* Name : upg_timedef_baldate
1330 Purpose : This procedure is used to upgrade the element entries
1331 and run results for the time definitions
1332 and Balance Dates
1333 Arguments :
1334 Notes :
1335 This upgrade is dependant on the EE_PROC_DETAILS
1336 upgrade.
1337 */
1338 procedure upg_timedef_baldate (p_asg_id in number)
1339 is
1340 --
1341 type t_ee_id is table of pay_element_entries_f.element_entry_id%type
1342 index by binary_integer;
1343 type t_rr_id is table of pay_run_results.run_result_id%type
1344 index by binary_integer;
1345 type t_td_id is table of pay_time_definitions.time_definition_id%type
1346 index by binary_integer;
1347 type t_action is table of pay_payroll_actions.action_type%type
1348 index by binary_integer;
1349 type date_tab is table of date index by binary_integer;
1350 --
1351 l_ee_tab t_ee_id;
1352 l_std_tab date_tab;
1353 l_end_tab date_tab;
1354 l_action t_action;
1355 l_td_tab t_td_id;
1356 l_rr_tab t_rr_id;
1357 --
1358 cursor get_retro_ee(p_asg_id in number,
1359 p_std_time_def_id in number) is
1360 select pee.element_entry_id,
1361 nvl(pee.source_start_date, ptp.start_date) start_date,
1362 nvl(pee.source_end_date, ptp.end_date) end_date,
1363 decode(nvl(p_std_time_def_id, -1),
1364 -1, null,
1365 decode(nvl(pet.time_definition_type, 'N'),
1366 'S', pet.time_definition_id,
1367 'G', p_std_time_def_id,
1368 null)
1369 ) time_definition_id
1370 from pay_element_entries_f pee,
1371 pay_entry_process_details pepd,
1372 pay_assignment_actions paa,
1373 pay_payroll_actions ppa,
1374 per_time_periods ptp,
1375 pay_element_types_f pet
1376 where pee.creator_type in ('RR', 'EE')
1377 and pee.assignment_id = p_asg_id
1378 and pee.element_entry_id = pepd.element_entry_id
1379 and pepd.source_asg_action_id = paa.assignment_action_id
1380 and paa.payroll_action_id = ppa.payroll_action_id
1381 and ppa.payroll_id = ptp.payroll_id
1382 and pet.element_type_id = pepd.source_element_type_id
1383 and ppa.date_earned between pet.effective_start_date
1384 and pet.effective_end_date
1385 and ppa.date_earned between ptp.start_date
1386 and ptp.end_date;
1387 --
1388 cursor get_td_rr (p_asg_id in number,
1389 p_std_time_def_id in number)
1390 is
1391 SELECT
1392 prr.run_result_id,
1393 decode(nvl(pet.time_definition_type, 'N'),
1397 ppa.action_type
1394 'S', pet.time_definition_id,
1395 'G', p_std_time_def_id,
1396 null) time_definition_id,
1398 from
1399 pay_assignment_actions paa,
1400 pay_payroll_actions ppa,
1401 pay_element_types_f pet,
1402 pay_run_results prr
1403 where paa.assignment_id = p_asg_id
1404 and paa.assignment_action_id = prr.assignment_action_id
1405 and ppa.payroll_action_id = paa.payroll_action_id
1406 and ppa.action_type in ('R', 'Q', 'B', 'V', 'I')
1407 and prr.element_type_id = pet.element_type_id
1408 and prr.time_definition_id is null
1409 and ppa.date_earned between pet.effective_start_date
1410 and pet.effective_end_date
1411 and pet.time_definition_type in ('G', 'S')
1412 order by decode(action_type,
1413 'V', 2,
1414 1);
1415 --
1416 l_std_time_def_id pay_time_definitions.time_definition_id%type;
1417 l_bus_grpid per_business_groups.business_group_id%type;
1418 l_leg_code per_business_groups.legislation_code%type;
1419 l_complete pay_upgrade_status.status%type;
1420 --
1421 begin
1422 --
1423 select distinct pbg.business_group_id,
1424 pbg.legislation_code
1425 into l_bus_grpid,
1426 l_leg_code
1427 from per_business_groups_perf pbg,
1428 per_all_assignments_f paf
1429 where paf.assignment_id = p_asg_id
1430 and paf.business_group_id = pbg.business_group_id;
1431 --
1432 -- We can not do the upgrade unless
1433 -- a previous upgrade has completed
1434 -- successfully
1435 --
1436 pay_core_utils.get_upgrade_status
1437 (l_bus_grpid,
1438 'EE_PROC_DETAILS',
1439 l_complete);
1440
1441 if (l_complete <> 'Y') then
1442 pay_core_utils.assert_condition(
1443 'upg_timedef_baldate:1',
1444 1 = 2);
1445 end if;
1446 --
1447 -- First find out if we need to do a
1448 -- Time definition upgrade
1449 begin
1450 --
1451 select ptd.time_definition_id
1452 into l_std_time_def_id
1453 from pay_time_definitions ptd
1454 where ptd.short_name = l_leg_code||'_STANDARD';
1455 --
1456 exception
1457 when no_data_found then
1458 l_std_time_def_id := null;
1459 end;
1460
1461 if (l_std_time_def_id is not null) then
1462 --
1463 open get_td_rr(p_asg_id, l_std_time_def_id);
1464 loop
1465 --
1466 fetch get_td_rr
1467 bulk collect into l_rr_tab, l_td_tab, l_action limit 100;
1468 --
1469 forall i in 1..l_rr_tab.COUNT
1470 update pay_run_results
1471 set time_definition_id = l_td_tab(i)
1472 where run_result_id = l_rr_tab(i);
1473
1474 forall i in 1..l_rr_tab.COUNT
1475 update pay_run_results prr
1476 set prr.start_date = (select pee.date_earned
1477 from pay_element_entries_f pee
1478 where pee.element_entry_id = prr.source_id
1479 ),
1480 prr.end_date = (select pee.date_earned
1481 from pay_element_entries_f pee
1482 where pee.element_entry_id = prr.source_id
1483 )
1484 where prr.run_result_id = l_rr_tab(i)
1485 and exists (select ''
1486 from pay_element_entries_f pee
1487 where pee.element_entry_id = prr.source_id
1488 and pee.date_earned is not null)
1489 and prr.start_date is null
1490 and l_action(i) <> 'V';
1491 --
1492 forall i in 1..l_rr_tab.COUNT
1493 update pay_run_results prr
1494 set prr.start_date = (select prr.start_date
1495 from pay_run_results prr2
1496 where prr2.run_result_id = prr.source_id
1497 ),
1498 prr.end_date = (select prr.end_date
1499 from pay_run_results prr2
1500 where prr2.run_result_id = prr.source_id
1501 )
1502 where prr.run_result_id = l_rr_tab(i)
1503 and exists (select ''
1504 from pay_run_results prr2
1505 where prr2.run_result_id = prr.source_id
1506 and prr2.start_date is not null)
1507 and prr.start_date is null
1508 and l_action(i) = 'V';
1509 --
1510
1511 --
1512 exit when get_td_rr%notfound;
1513 --
1514 end loop;
1515 close get_td_rr;
1516 --
1517 end if;
1518 --
1519 -- Now upgrade the Retropay Results
1520 --
1521 open get_retro_ee(p_asg_id, l_std_time_def_id);
1522 loop
1523 --
1524 fetch get_retro_ee
1525 bulk collect into l_ee_tab,
1526 l_std_tab,
1527 l_end_tab,
1528 l_td_tab limit 100;
1529 --
1530 forall i in 1..l_ee_tab.COUNT
1531 update pay_element_entries_f
1535 --
1532 set source_start_date = l_std_tab(i),
1533 source_end_date = l_end_tab(i)
1534 where element_entry_id = l_ee_tab(i);
1536 -- Only upgrade the time definition if the
1537 -- legislation has enabled the Standard upgrade
1538 --
1539 if (l_std_time_def_id is not null) then
1540 --
1541 forall i in 1..l_ee_tab.COUNT
1542 update pay_entry_process_details
1543 set time_definition_id = l_td_tab(i)
1544 where element_entry_id = l_ee_tab(i);
1545 --
1546 end if;
1547 --
1548 --
1549 forall i in 1..l_ee_tab.COUNT
1550 update pay_run_results
1551 set start_date = l_std_tab(i),
1552 end_date = l_end_tab(i),
1553 time_definition_id = l_td_tab(i)
1554 where source_id = l_ee_tab(i)
1555 and source_type in ('E', 'I');
1556 --
1557 --
1558 forall i in 1..l_ee_tab.COUNT
1559 update pay_run_results prr
1560 set prr.start_date = l_std_tab(i),
1561 prr.end_date = l_end_tab(i),
1562 prr.time_definition_id = l_td_tab(i)
1563 where prr.source_id in (select prr1.run_result_id
1564 from pay_run_results prr1
1565 where prr1.source_id = l_ee_tab(i)
1566 and prr1.source_type in ('E', 'I')
1567 )
1568 and prr.source_type in ('R', 'V');
1569 --
1570 exit when get_retro_ee%notfound;
1571 --
1572 end loop;
1573 close get_retro_ee;
1574 --
1575 end upg_timedef_baldate;
1576 --
1577 /* Name : qual_remove_appl_alus
1578 Purpose : This procedure is used to qualify an assignment for the
1579 REMOVE_APPL_ALUS data upgrade.
1580 Arguments :
1581 Notes :
1582 */
1583 procedure qual_remove_appl_alus(p_object_id in number,
1584 p_qualified out nocopy varchar2
1585 )
1586 is
1587 l_dummy varchar2(2);
1588 l_qualifier varchar2(10);
1589 begin
1590 --
1591 begin
1592 -- An assignment is qualified if it has a non-null people group id
1593 select ''
1594 into l_dummy
1595 from dual
1596 where exists (select ''
1597 from per_all_assignments_f asg
1598 where asg.assignment_id = p_object_id
1599 and asg.people_group_id is not null
1600 and asg.assignment_type in ('A','O'));
1601 l_qualifier := 'Y';
1602 exception
1603 when no_data_found then
1604 l_qualifier := 'N';
1605 end;
1606 p_qualified := l_qualifier;
1607 --
1608 end qual_remove_appl_alus;
1609 --
1610 /* Name : remove_appl_alus
1611 Purpose : This procedure removes all ALUs from applicant assignments.
1612 Arguments :
1613 Notes :
1614 */
1615 procedure remove_appl_alus (p_assignment_id in number)
1616 is
1617 --
1618 type t_alu_table_rec is record (
1619 alu_id dbms_sql.number_table,
1620 effective_start_date dbms_sql.date_table
1621 );
1622 --
1623 cursor csr_asg (p_asg_id number) is
1624 select asg.assignment_type,
1625 asg.effective_start_date,
1626 asg.effective_end_date
1627 from per_all_assignments_f asg
1628 where asg.assignment_id = p_asg_id
1629 order by asg.effective_start_date;
1630 --
1631 cursor csr_alu (p_asg_id number,
1632 p_asg_effective_start_date date,
1633 p_asg_effective_end_date date) is
1634 select alu.assignment_link_usage_id,
1635 alu.effective_start_date,
1636 alu.effective_end_date
1637 from pay_assignment_link_usages_f alu
1638 where alu.assignment_id = p_asg_id
1639 and alu.effective_start_date <= p_asg_effective_end_date
1640 and alu.effective_end_date >= p_asg_effective_start_date;
1641 --
1642 r_this_asg csr_asg%rowtype;
1643 r_next_asg csr_asg%rowtype;
1644 --
1645 -- We do bulk updates/deletes of ALUs
1646 --
1647 l_alu_update_table t_alu_table_rec;
1648 l_alu_delete_table t_alu_table_rec;
1649 l_update_counter number := 1;
1650 l_delete_counter number := 1;
1651 --
1652 begin
1653 --
1654 -- Delete all assigment link usages where no part of the parent assignment
1655 -- is a non-applicant assignment (i.e. the entire assignment is for an
1656 -- applicant).
1657 --
1658 delete pay_assignment_link_usages_f alu
1659 where not exists (
1660 select null
1661 from per_all_assignments_f asg
1662 where asg.assignment_id = alu.assignment_id
1663 and asg.assignment_type not in ('A','O')
1664 )
1665 and alu.assignment_id = p_assignment_id;
1666 --
1667 -- Now, all that remains is to either -
1668 -- 1. Move assignment link usages that span applicant assignments, so that
1669 -- they no longer do so, i.e.
1670 --
1671 -- 'O' 'A' 'E'
1672 -- ASG |--------------|-------------|----------------------->
1673 -- ALU (old) |---------------------------------------------------->
1674 -- ALU (new) |----------------------->
1675 --
1676 -- We assume that only the ALU effective_start_date will require updating.
1677 --
1678 -- 2. Delete any ALUs that exist purely for applicant assignment pieces
1679 -- (this situation is unlikely, but possible, so we do cater for it) i.e.
1680 --
1681 -- 'A' 'E'
1682 -- ASG |--------------|------------------------------------->
1683 -- ALU (old) |--------------|
1684 --
1685 open csr_asg(p_assignment_id);
1686 fetch csr_asg into r_this_asg;
1687 --
1688 -- Look at all applicant assignment pieces (assignment_type = 'A' or 'O')
1689 --
1690 while csr_asg%found
1691 and ( r_this_asg.assignment_type = 'A'
1692 or r_this_asg.assignment_type = 'O') loop
1693 --
1694 -- Look ahead at 'next' assignment piece
1695 --
1696 fetch csr_asg into r_next_asg;
1697 --
1698 -- Loop through all ALUs that span 'this' assignment piece
1699 --
1700 for r_alu in
1701 csr_alu(p_assignment_id,
1702 r_this_asg.effective_start_date,
1703 r_this_asg.effective_end_date) loop
1704 --
1705 -- Either the ALU spans the next assignment piece or it doesn't.
1706 -- + If it *does* span the next assignment piece, then update this ALU's
1710 -- applicant part of the assignment.
1707 -- start date to the start date of the next assignment piece.
1708 -- + If it doesn't span the next assignment piece, then delete it. It's
1709 -- possible (although unlikely) that the ALU only exists for the
1711 --
1712 if csr_asg%found
1713 and r_alu.effective_end_date >= r_next_asg.effective_start_date then
1714 --
1715 -- Update this ALU's start date to the start date of the next
1716 -- assignment piece
1717 --
1718 l_alu_update_table.alu_id(l_update_counter) :=
1719 r_alu.assignment_link_usage_id;
1720 l_alu_update_table.effective_start_date(l_update_counter) :=
1721 r_next_asg.effective_start_date;
1722 l_update_counter := l_update_counter + 1;
1723 --
1724 else
1725 --
1726 -- No other assignment pieces found, or ALU does not span the next
1727 -- assignment piece. Either way this ALU must be entirely contained
1728 -- within applicant assignment pieces, so we can delete it.
1729 --
1730 l_alu_delete_table.alu_id(l_delete_counter) :=
1731 r_alu.assignment_link_usage_id;
1732 l_alu_delete_table.effective_start_date(l_delete_counter) :=
1733 r_alu.effective_start_date;
1734 l_delete_counter := l_delete_counter + 1;
1735 --
1736 end if;
1737 --
1738 end loop;
1739 --
1740 if l_alu_update_table.alu_id.count > 0 then
1741 --
1742 -- Do bulk update of ALU start dates
1743 --
1744 forall i in 1 .. l_alu_update_table.alu_id.count
1745 update pay_assignment_link_usages_f
1746 set effective_start_date = l_alu_update_table.effective_start_date(i)
1747 where assignment_link_usage_id = l_alu_update_table.alu_id(i);
1748 --
1749 end if;
1750 --
1751 if l_alu_delete_table.alu_id.count > 0 then
1752 --
1753 -- Do bulk delete of ALUs that only belong to applicant assignments
1754 --
1755 forall i in 1 .. l_alu_delete_table.alu_id.count
1756 delete pay_assignment_link_usages_f
1757 where assignment_link_usage_id = l_alu_delete_table.alu_id(i)
1758 and effective_start_date = l_alu_delete_table.effective_start_date(i);
1759 --
1760 end if;
1761 --
1762 -- Reset counters and flush update/delete tables
1763 --
1764 l_update_counter := 1;
1765 l_delete_counter := 1;
1766 --
1767 l_alu_update_table.alu_id.delete;
1768 l_alu_update_table.effective_start_date.delete;
1769 --
1770 l_alu_delete_table.alu_id.delete;
1771 l_alu_delete_table.effective_start_date.delete;
1772 --
1773 -- Get next assignment piece and repeat
1774 --
1775 r_this_asg := r_next_asg;
1776 --
1777 end loop;
1778 --
1779 close csr_asg;
1780 --
1781 end remove_appl_alus;
1782 --
1783 END pay_core_upgrade_pkg;
[Home] [Help]