[Home] [Help]
PACKAGE BODY: APPS.BEN_CWB_DATA_MODEL_UPGRADE
Source
1 package body ben_cwb_data_model_upgrade as
2 /* $Header: bencwbmu.pkb 120.2.12010000.2 2008/12/04 14:17:43 cakunuru ship $ */
3 /* ===========================================================================+
4 * Name
5 * Compensation Workbench Data Model Upgrade Package
6 * Purpose
7 * This package is used to migrate data of old customers to
8 * new CWB Data model.
9 *
10 * Version Date Author Comment
11 * -------+-----------+---------+----------------------------------------------
12 * 115.0 14-Jan-2004 maagrawa created
13 * 115.1 15-Jan-2004 maagrawa Added out parameters to main;used by CM.
14 * 115.2 22-Jan-2004 maagrawa Added upgrade for PP ranking.
15 * 115.3 06-Feb-2004 skota Changed the column name approval_mode to
16 * approval_mode_cd
17 * 115.4 10-Feb-2004 skota Added get_ functions and replaced the inner
18 * selects with calls to get_ functions
19 * 115.5 12-Feb-2004 maagrawa Call to upgrade_person_rates was missing.
20 * 115.6 13-Feb-2004 skota Added the supervisor_id to
21 * refresh_person_info_group_pl
22 * 115.7 01-Mar-2004 maagrawa New algo to get sub_acty_typ_cd.
23 * 115.8 02-Mar-2004 maagrawa hr_update_utility package changes.
24 * 115.9 15-Mar-2004 maagrawa Commented out hr_update_utility package
25 * call as it will not be delivered in 1st patch.
26 * 115.10 30-Mar-2004 maagrawa Null the rankings which are not integers.
27 * 115.11 02-Mar-2004 maagrawa Remove spaces for rank.
28 * Log Upgrade progress messages.
29 * 115.12 25-May-2004 maagrawa Splitting of Perf/Promo records.
30 * 115.13 21-Sep-2004 maagrawa Included upgrade for emp reassign trans.
31 * 115.14 19-Jan-2005 maagrawa Re-instate hr_update_utility calls.
32 * 115.15 28-Apr-2005 maagrawa Also check whether the process is complete
33 * before re-submit.
34 * 115.16 03-May-2005 maagrawa Increase the size of brief name from 250
35 * to 360.
36 * 115.17 03-Jan-2006 maagrawa Modified for multi-currency upgrade.
37 * 115.18 21-Dec-2007 steotia 6692393: Removed group_pl_id null check.
38 * ==========================================================================+
39 */
40
41 g_package varchar2(80) := 'ben_cwb_data_model_upgrade.';
42
43
44 g_commit_size constant number := 10000;
45
46 type t_val is table of number index by binary_integer;
47 type t_id is table of number(15) index by binary_integer;
48 type t_date is table of date index by binary_integer;
49 type t_rowid is table of rowid index by binary_integer;
50 type t_varchar_small is table of varchar2(30) index by binary_integer;
51 type t_varchar_med is table of varchar2(240) index by binary_integer;
52 type t_varchar_med2 is table of varchar2(150) index by binary_integer;
53 type t_varchar_med3 is table of varchar2(360) index by binary_integer;
54 type t_varchar_big is table of varchar2(2000) index by binary_integer;
55
56 g_approval_mode varchar2(30) := null;
57
58 procedure commit_and_log(p_text in varchar2) is
59 begin
60
61 insert into ben_transaction
62 (transaction_id
63 ,transaction_type
64 ,attribute1
65 ,attribute40)
66 values
67 (ben_transaction_s.nextval
68 ,'CWBUPGRADE'
69 ,to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss')
70 ,p_text);
71
72 commit;
73 end;
74
75 procedure upgrade_plan_design is
76
77 cursor c_profile is
78 select val.profile_option_value
79 from fnd_profile_options prf
80 ,fnd_profile_option_values val
81 where prf.profile_option_name = 'BEN_CWB_APPROVAL_MODE'
82 and prf.application_id = 805
83 and prf.application_id = val.application_id
84 and prf.profile_option_id = val.profile_option_id
85 and val.level_id = 10001
86 and val.level_value = 0;
87
88 cursor c_tasks(v_pl_id number) is
89 select min(tk.ordr_num) min_ordr
90 ,max(tk.ordr_num) max_ordr
91 from ben_cwb_wksht_grp tk
92 where tk.pl_id = v_pl_id;
93
94
95 cursor c_oipl(v_pl_id number) is
96 select distinct oipl.oipl_id
97 from ben_oipl_f oipl
98 where oipl.pl_id = v_pl_id;
99
100 cursor c_pl_dsgn is
101 select enp.enrt_perd_id
102 ,pl.pl_id
103 ,pl.business_group_id
104 ,enp.asnd_lf_evt_dt
105 ,enp.data_freeze_date
106 ,enp.uses_bdgt_flag
107 ,decode(typ.comp_typ_cd, 'ICM2', 'ICM5',
108 'ICM3', 'ICM5',
109 'ICM6', 'ICM5',
110 typ.comp_typ_cd) comp_typ_cd
111 ,typ.pl_typ_id
112 ,enp.prsvr_bdgt_cd prsrv_bdgt_cd
113 from ben_pl_typ_f typ
114 ,ben_pl_f pl
115 ,ben_popl_enrt_typ_cycl_f cyc
116 ,ben_enrt_perd enp
117 where typ.opt_typ_cd = 'CWB'
118 and typ.pl_typ_id = pl.pl_typ_id
119 and pl.pl_id = cyc.pl_id
120 and cyc.popl_enrt_typ_cycl_id = enp.popl_enrt_typ_cycl_id
121 -- and pl.group_pl_id is null
122 and enp.data_freeze_date is null
123 and not exists (select 'Y'
124 from ben_cwb_pl_dsgn pln
125 where pln.pl_id = pl.pl_id
126 and pln.lf_evt_ocrd_dt = enp.asnd_lf_evt_dt)
127 and exists (select 'Y'
128 from ben_per_in_ler pil
129 ,ben_pil_elctbl_chc_popl popl
130 where pil.lf_evt_ocrd_dt = enp.asnd_lf_evt_dt
131 and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
132 and pil.group_pl_id is null
133 and pil.per_in_ler_id = popl.per_in_ler_id
134 and popl.pl_id = pl.pl_id
135 and popl.enrt_typ_cycl_cd = 'COMP');
136
137 l_old_pl_id number := -999;
138 l_old_pl_typ_id number := -999;
139 l_task_exist boolean;
140 l_min_ordr number;
141 l_max_ordr number;
142
143 begin
144
145 for l_pl_dsgn in c_pl_dsgn loop
146
147 if g_approval_mode is null then
148 open c_profile;
149 fetch c_profile into g_approval_mode;
150 close c_profile;
151
152 if g_approval_mode is null then
153 g_approval_mode := 'NR';
154 end if;
155 end if;
156
157 if l_pl_dsgn.pl_id <> l_old_pl_id then
158
159 begin
160 update ben_pl_f pl
161 set pl.group_pl_id = pl.pl_id
162 where pl.pl_id = l_pl_dsgn.pl_id
163 and pl.group_pl_id is null;
164 exception
165 when others then
166 null;
167 end;
168
169
170 begin
171 update ben_acty_base_rt_f abr
172 set abr.sub_acty_typ_cd = decode(abr.oipl_id,
173 null, l_pl_dsgn.comp_typ_cd,
174 'ICM11')
175 where abr.acty_typ_cd = 'CWBWS'
176 and abr.sub_acty_typ_cd is null
177 and (abr.pl_id = l_pl_dsgn.pl_id
178 OR
179 abr.oipl_id in (select oipl.oipl_id
180 from ben_oipl_f oipl
181 where oipl.pl_id = l_pl_dsgn.pl_id));
182 exception
183 -- No data found.
184 when others then
185 null;
186 end;
187
188 l_task_exist := false;
189 l_min_ordr := null;
190 l_max_ordr := null;
191
192 open c_tasks(l_pl_dsgn.pl_id);
193 fetch c_tasks into l_min_ordr, l_max_ordr;
194 if l_min_ordr is not null then
195 l_task_exist := true;
196 end if;
197 close c_tasks;
198
199 if l_task_exist then
200
201 update ben_cwb_wksht_grp grp
202 set grp.status_cd = 'A'
203 ,grp.hidden_cd = null
204 where grp.pl_id = l_pl_dsgn.pl_id;
205
206 end if;
207
208 if l_min_ordr is null then
209 l_min_ordr := 10;
210 else
211 l_min_ordr := l_min_ordr -1 ;
212 end if;
213
214 if l_max_ordr is null then
215 l_max_ordr := 20;
216 else
217 l_max_ordr := l_max_ordr + 10;
218 end if;
219
220
221 if l_pl_dsgn.uses_bdgt_flag = 'Y' then
222 insert into ben_cwb_wksht_grp
223 (cwb_wksht_grp_id ,business_group_id
224 ,pl_id ,ordr_num ,wksht_grp_cd ,label
225 ,status_cd ,hidden_cd ,object_version_number)
226 values
227 (ben_cwb_wksht_grp_s.nextval ,l_pl_dsgn.business_group_id
228 ,l_pl_dsgn.pl_id ,l_min_ordr
229 ,'BDGT' ,'Set Budgets' ,'A' ,null ,1);
230 end if;
231
232 if not(l_task_exist) then
233 insert into ben_cwb_wksht_grp
234 (cwb_wksht_grp_id ,business_group_id
235 ,pl_id ,ordr_num ,wksht_grp_cd ,label
236 ,status_cd ,hidden_cd ,object_version_number)
237 values
238 (ben_cwb_wksht_grp_s.nextval ,l_pl_dsgn.business_group_id
239 ,l_pl_dsgn.pl_id ,l_max_ordr ,'COMP'
240 ,'Allocate Compensation' ,'A' ,null ,1);
241 l_max_ordr := l_max_ordr + 10;
242 end if;
243
244
245 insert into ben_cwb_wksht_grp
246 (cwb_wksht_grp_id ,business_group_id
247 ,pl_id ,ordr_num ,wksht_grp_cd ,label
248 ,status_cd ,hidden_cd ,object_version_number)
249 values
250 (ben_cwb_wksht_grp_s.nextval ,l_pl_dsgn.business_group_id
251 ,l_pl_dsgn.pl_id ,l_max_ordr ,'APPR'
252 ,'Manage Approvals' ,'A' ,null ,1);
253 l_max_ordr := l_max_ordr + 10;
254
255 insert into ben_cwb_wksht_grp
256 (cwb_wksht_grp_id ,business_group_id
257 ,pl_id ,ordr_num ,wksht_grp_cd ,label
258 ,status_cd ,hidden_cd ,object_version_number)
259 values
260 (ben_cwb_wksht_grp_s.nextval ,l_pl_dsgn.business_group_id
261 ,l_pl_dsgn.pl_id ,l_max_ordr ,'RVW'
262 ,'Review and Submit' ,'A' ,null ,1);
263
264 end if; -- pl_id <> old_pl_id
265
266 if l_pl_dsgn.pl_typ_id <> l_old_pl_typ_id then
267
268 begin
269
270 update ben_opt_f opt
271 set opt.group_opt_id = opt.opt_id
272 where opt.opt_id in (select pon.opt_id
273 from ben_pl_typ_opt_typ_f pon
274 where pon.pl_typ_id = l_pl_dsgn.pl_typ_id)
275 and opt.group_opt_id is null;
276
277 exception
278 when others then
279 null;
280 end;
281
282 end if; -- pl_typ_id <> old_pl_typ_id
283
284
285 begin
286 update ben_enrt_perd perd
287 set perd.data_freeze_date = perd.asnd_lf_evt_dt
288 ,perd.approval_mode_cd = g_approval_mode
289 where perd.enrt_perd_id = l_pl_dsgn.enrt_perd_id
290 and perd.data_freeze_date is null;
291 exception
292 when others then
293 null;
294 end;
295
296 -- Refresh Plan design information
297 ben_cwb_pl_dsgn_pkg.refresh_pl_dsgn(l_pl_dsgn.pl_id,l_pl_dsgn.asnd_lf_evt_dt,l_pl_dsgn.asnd_lf_evt_dt);
298
299 l_old_pl_id := l_pl_dsgn.pl_id;
300 l_old_pl_typ_id := l_pl_dsgn.pl_typ_id;
301
302 end loop;
303
304 commit_and_log('Plan Design Upgrade Complete');
305
306 end upgrade_plan_design;
307
308
309 procedure upgrade_hrchy is
310 begin
311
312 insert into ben_cwb_group_hrchy
313 (mgr_per_in_ler_id
314 ,emp_per_in_ler_id
315 ,lvl_num
316 ,last_update_date
317 ,last_updated_by
318 ,last_update_login
319 ,created_by
320 ,creation_date
321 ,object_version_number)
322 select /*+INDEX(emp_popl,ben_pil_elctbl_chc_popl_pk) INDEX(mgr_popl,ben_pil_elctbl_chc_popl_pk)*/
323 mgr_popl.per_in_ler_id
324 ,emp_popl.per_in_ler_id
325 ,hrchy.lvl_num
326 ,hrchy.last_update_date
327 ,hrchy.last_updated_by
328 ,hrchy.last_update_login
329 ,hrchy.created_by
330 ,hrchy.creation_date
331 ,1
332 from ben_cwb_hrchy hrchy
333 ,ben_pil_elctbl_chc_popl emp_popl
334 ,ben_pil_elctbl_chc_popl mgr_popl
335 where nvl(hrchy.object_version_number,1) < 9999
336 and lvl_num > -1
337 and hrchy.emp_pil_elctbl_chc_popl_id = emp_popl.pil_elctbl_chc_popl_id
338 and hrchy.mgr_pil_elctbl_chc_popl_id = mgr_popl.pil_elctbl_chc_popl_id;
339
340 update ben_cwb_hrchy
341 set object_version_number = 9999
342 where lvl_num > -1
343 and nvl(object_version_number,1) < 9999;
344
345 commit_and_log('Hierarchy Upgrade Complete');
346
347 end upgrade_hrchy;
348
349
350 procedure upgrade_person_groups is
351 begin
352
353 insert into ben_cwb_person_groups
354 (group_per_in_ler_id
355 ,group_pl_id
356 ,group_oipl_id
357 ,lf_evt_ocrd_dt
358 ,bdgt_pop_cd
359 ,due_dt
360 ,access_cd
361 ,approval_cd
362 ,submit_cd
363 ,dist_bdgt_val
364 ,ws_bdgt_val
368 ,dist_bdgt_incr_val
365 ,rsrv_val
366 ,dist_bdgt_mn_val
367 ,dist_bdgt_mx_val
369 ,ws_bdgt_mn_val
370 ,ws_bdgt_mx_val
371 ,ws_bdgt_incr_val
372 ,rsrv_mn_val
373 ,rsrv_mx_val
374 ,rsrv_incr_val
375 ,dist_bdgt_iss_val
376 ,ws_bdgt_iss_val
377 ,dist_bdgt_iss_date
378 ,ws_bdgt_iss_date
379 ,ws_bdgt_val_last_upd_date
380 ,dist_bdgt_val_last_upd_date
381 ,rsrv_val_last_upd_date
382 ,ws_bdgt_val_last_upd_by
383 ,dist_bdgt_val_last_upd_by
384 ,rsrv_val_last_upd_by
385 ,object_version_number)
386 select pil.per_in_ler_id
387 ,popl.pl_id
388 ,nvl(epe.oipl_id, -1)
389 ,pil.lf_evt_ocrd_dt
390 ,decode(popl.bdgt_stat_cd, null, null,
391 'NS', null,
392 popl.pop_cd) pop_cd
393 ,popl.ws_due_dt
394 ,popl.ws_acc_cd
395 ,decode(popl.ws_stat_cd,
396 'PR', 'PR',
397 'AP', 'AP',
398 null) approval_cd
399 ,decode(popl.ws_stat_cd,
400 'PR', 'SU',
401 'PA', 'SU',
402 'AP', 'SU',
403 'NS') submit_cd
404 ,db.val dist_bdgt_val
405 ,wb.val ws_bdgt_val
406 ,rs.val rsrv_val
407 ,db.mn_elcn_val dist_bdgt_mn_val
408 ,db.mx_elcn_val dist_bdgt_mx_val
409 ,db.incrmt_elcn_val dist_bdgt_incr_val
410 ,wb.mn_elcn_val ws_bdgt_mn_val
411 ,wb.mx_elcn_val ws_bdgt_mx_val
412 ,wb.incrmt_elcn_val ws_bdgt_incr_val
413 ,rs.mn_elcn_val rsrv_mn_val
414 ,rs.mx_elcn_val rsrv_mx_val
415 ,rs.incrmt_elcn_val rsrv_incr_val
416 ,db.iss_val dist_bdgt_iss_val
417 ,wb.iss_val ws_bdgt_iss_val
418 ,popl.bdgt_iss_dt dist_bdgt_iss_date
419 ,popl.ws_iss_dt ws_bdgt_iss_date
420 ,wb.val_last_upd_date ws_bdgt_val_upd_date
421 ,db.val_last_upd_date dist_bdgt_val_upd_date
422 ,rs.val_last_upd_date rsrv_val_upd_date
423 ,wb.val_last_upd_person_id ws_bdgt_val_last_upd_by
424 ,db.val_last_upd_person_id dist_bdgt_val_last_upd_by
425 ,rs.val_last_upd_person_id rsrv_val_last_upd_by
426 ,1
427 from ben_per_in_ler pil
428 ,ben_pil_elctbl_chc_popl popl
429 ,ben_elig_per_elctbl_chc epe
430 ,ben_cwb_person_groups grp
431 ,ben_enrt_rt db
432 ,ben_enrt_rt wb
433 ,ben_enrt_rt rs
434 where pil.per_in_ler_stat_cd in ('PROCD', 'STRTD')
435 and pil.group_pl_id is null
436 and pil.assignment_id is null
437 and pil.per_in_ler_id = popl.per_in_ler_id
438 and popl.enrt_typ_cycl_cd = 'COMP'
439 and popl.assignment_id is not null
440 and popl.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
441 and epe.per_in_ler_id = grp.group_per_in_ler_id (+)
442 and epe.pl_id = grp.group_pl_id (+)
443 and nvl(epe.oipl_id, -1) = grp.group_oipl_id (+)
444 and grp.group_per_in_ler_id is null
445 and epe.elig_per_elctbl_chc_id = db.elig_per_elctbl_chc_id (+)
446 and db.acty_typ_cd (+) = 'CWBDB'
447 and epe.elig_per_elctbl_chc_id = wb.elig_per_elctbl_chc_id (+)
448 and wb.acty_typ_cd (+) = 'CWBWB'
449 and epe.elig_per_elctbl_chc_id = rs.elig_per_elctbl_chc_id (+)
450 and rs.acty_typ_cd (+) = 'CWBR';
451
452 commit_and_log('Person Groups Upgrade Complete');
453
454 end upgrade_person_groups;
455
456
457 procedure upgrade_person_rates is
458 begin
459
460 insert into ben_cwb_person_rates
461 (person_rate_id
462 ,group_per_in_ler_id
463 ,pl_id
464 ,oipl_id
465 ,group_pl_id
466 ,group_oipl_id
467 ,lf_evt_ocrd_dt
468 ,elig_flag
469 ,ws_val
470 ,ws_mn_val
471 ,ws_mx_val
472 ,ws_incr_val
473 ,elig_sal_val
474 ,stat_sal_val
475 ,oth_comp_val
476 ,tot_comp_val
477 ,misc1_val
478 ,misc2_val
479 ,misc3_val
480 ,rec_val
481 ,rec_mn_val
482 ,rec_mx_val
483 ,rec_incr_val
484 ,ws_val_last_upd_date
485 ,ws_val_last_upd_by
486 ,pay_proposal_id
487 ,element_entry_value_id
488 ,person_id
489 ,assignment_id
490 ,ws_rt_start_date
491 ,object_version_number
492 ,currency)
493 select ben_cwb_person_rates_s.nextval
494 ,pil.per_in_ler_id
495 ,popl.pl_id
496 ,nvl(epe.oipl_id, -1)
497 ,popl.pl_id
498 ,nvl(epe.oipl_id, -1)
499 ,pil.lf_evt_ocrd_dt
500 ,epe.elig_flag
501 ,ws.val ws_val
502 ,ws.mn_elcn_val ws_mn_val
503 ,ws.mx_elcn_val ws_mx_val
504 ,ws.incrmt_elcn_val ws_incr_val
505 ,es.val elig_sal_val
506 ,ss.val stat_sal_val
510 ,m2.val misc2_val
507 ,oc.val oth_comp_val
508 ,tc.val tot_comp_val
509 ,m1.val misc1_val
511 ,m3.val misc3_val
512 ,rc.val rec_val
513 ,rc.mn_elcn_val rec_mn_val
514 ,rc.mx_elcn_val rec_mx_val
515 ,rc.incrmt_elcn_val rec_incr_val
516 ,ws.val_last_upd_date ws_val_upd_date
517 ,ws.val_last_upd_person_id ws_val_last_upd_by
518 ,decode(prv.pk_id_table_name,
519 'PER_PAY_PROPOSALS',prv.pk_id,null) pay_proposal_id
520 ,prv.element_entry_value_id
521 ,pil.person_id
522 ,popl.assignment_id
523 ,nvl(prv.rt_strt_dt, ws.rt_strt_dt)
524 ,1
525 ,popl.uom
526 from ben_per_in_ler pil
527 ,ben_pil_elctbl_chc_popl popl
528 ,ben_elig_per_elctbl_chc epe
529 ,ben_cwb_person_rates rate
530 ,ben_enrt_rt ws
531 ,ben_enrt_rt es
532 ,ben_enrt_rt ss
533 ,ben_enrt_rt oc
534 ,ben_enrt_rt tc
535 ,ben_enrt_rt m1
536 ,ben_enrt_rt m2
537 ,ben_enrt_rt m3
538 ,ben_enrt_rt rc
539 ,ben_prtt_rt_val prv
540 where pil.per_in_ler_stat_cd in ('PROCD', 'STRTD')
541 and pil.group_pl_id is null
542 and pil.assignment_id is null
543 and pil.per_in_ler_id = popl.per_in_ler_id
544 and popl.enrt_typ_cycl_cd = 'COMP'
545 and popl.assignment_id is not null
546 and popl.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
547 and epe.elctbl_flag = 'Y'
548 and epe.per_in_ler_id = rate.group_per_in_ler_id (+)
549 and epe.pl_id = rate.pl_id (+)
550 and nvl(epe.oipl_id, -1) = rate.oipl_id (+)
551 and rate.group_per_in_ler_id is null
552 and epe.elig_per_elctbl_chc_id = ws.elig_per_elctbl_chc_id (+)
553 and ws.acty_typ_cd (+) = 'CWBWS'
554 and epe.elig_per_elctbl_chc_id = es.elig_per_elctbl_chc_id (+)
555 and es.acty_typ_cd (+) = 'CWBES'
556 and epe.elig_per_elctbl_chc_id = ss.elig_per_elctbl_chc_id (+)
557 and ss.acty_typ_cd (+) = 'CWBSS'
558 and epe.elig_per_elctbl_chc_id = oc.elig_per_elctbl_chc_id (+)
559 and oc.acty_typ_cd (+) = 'CWBOS'
560 and epe.elig_per_elctbl_chc_id = tc.elig_per_elctbl_chc_id (+)
561 and tc.acty_typ_cd (+) = 'CWBTC'
562 and epe.elig_per_elctbl_chc_id = m1.elig_per_elctbl_chc_id (+)
563 and m1.acty_typ_cd (+) = 'CWBMR1'
564 and epe.elig_per_elctbl_chc_id = m2.elig_per_elctbl_chc_id (+)
565 and m2.acty_typ_cd (+) = 'CWBMR2'
566 and epe.elig_per_elctbl_chc_id = m3.elig_per_elctbl_chc_id (+)
567 and m3.acty_typ_cd (+) = 'CWBMR3'
568 and epe.elig_per_elctbl_chc_id = rc.elig_per_elctbl_chc_id (+)
569 and rc.acty_typ_cd (+) = 'CWBRA'
570 and ws.prtt_rt_val_id = prv.prtt_rt_val_id (+)
571 and prv.prtt_rt_val_stat_cd is null;
572
573 commit_and_log('Person Rates Upgrade Complete');
574
575 end upgrade_person_rates;
576
577 procedure upgrade_person_info is
578
579 cursor c_pil is
580 select pil.per_in_ler_id
581 ,pil.person_id
582 ,popl.assignment_id
583 ,popl.pl_id
584 ,pil.lf_evt_ocrd_dt
585 ,popl.uom
586 ,popl.comments
587 ,popl.ws_mgr_id
588 ,popl.mgr_ovrid_dt
589 ,popl.mgr_ovrid_person_id
590 ,popl.pel_attribute_category
591 ,popl.pel_attribute1
592 ,popl.pel_attribute2
593 ,popl.pel_attribute3
594 ,popl.pel_attribute4
595 ,popl.pel_attribute5
596 ,popl.pel_attribute6
597 ,popl.pel_attribute7
598 ,popl.pel_attribute8
599 ,popl.pel_attribute9
600 ,popl.pel_attribute10
601 ,popl.pel_attribute11
602 ,popl.pel_attribute12
603 ,popl.pel_attribute13
604 ,popl.pel_attribute14
605 ,popl.pel_attribute15
606 ,popl.pel_attribute16
607 ,popl.pel_attribute17
608 ,popl.pel_attribute18
609 ,popl.pel_attribute19
610 ,popl.pel_attribute20
611 ,popl.pel_attribute21
612 ,popl.pel_attribute22
613 ,popl.pel_attribute23
614 ,popl.pel_attribute24
615 ,popl.pel_attribute25
616 ,popl.pel_attribute26
617 ,popl.pel_attribute27
618 ,popl.pel_attribute28
619 ,popl.pel_attribute29
620 ,popl.pel_attribute30
621 from ben_per_in_ler pil
622 ,ben_pil_elctbl_chc_popl popl
623 ,ben_cwb_person_info per
624 where pil.group_pl_id is null
625 and pil.assignment_id is null
626 and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
627 and pil.per_in_ler_id = popl.per_in_ler_id
628 and popl.enrt_typ_cycl_cd = 'COMP'
629 and popl.assignment_id is not null
630 and popl.per_in_ler_id = per.group_per_in_ler_id (+)
631 and per.assignment_id is null;
632
633 l_t_per_in_ler_id t_id;
634 l_t_person_id t_id;
635 l_t_assignment_id t_id;
636 l_t_pl_id t_id;
637 l_t_lf_evt_ocrd_dt t_date;
638 l_t_currency t_varchar_small;
639 l_t_comments t_varchar_big;
640 l_t_ws_mgr_id t_id;
644 l_t_pel_attribute1 t_varchar_med2;
641 l_t_mgr_ovrid_dt t_date;
642 l_t_mgr_ovrid_person_id t_id;
643 l_t_pel_attribute_category t_varchar_small;
645 l_t_pel_attribute2 t_varchar_med2;
646 l_t_pel_attribute3 t_varchar_med2;
647 l_t_pel_attribute4 t_varchar_med2;
648 l_t_pel_attribute5 t_varchar_med2;
649 l_t_pel_attribute6 t_varchar_med2;
650 l_t_pel_attribute7 t_varchar_med2;
651 l_t_pel_attribute8 t_varchar_med2;
652 l_t_pel_attribute9 t_varchar_med2;
653 l_t_pel_attribute10 t_varchar_med2;
654 l_t_pel_attribute11 t_varchar_med2;
655 l_t_pel_attribute12 t_varchar_med2;
656 l_t_pel_attribute13 t_varchar_med2;
657 l_t_pel_attribute14 t_varchar_med2;
658 l_t_pel_attribute15 t_varchar_med2;
659 l_t_pel_attribute16 t_varchar_med2;
660 l_t_pel_attribute17 t_varchar_med2;
661 l_t_pel_attribute18 t_varchar_med2;
662 l_t_pel_attribute19 t_varchar_med2;
663 l_t_pel_attribute20 t_varchar_med2;
664 l_t_pel_attribute21 t_varchar_med2;
665 l_t_pel_attribute22 t_varchar_med2;
666 l_t_pel_attribute23 t_varchar_med2;
667 l_t_pel_attribute24 t_varchar_med2;
668 l_t_pel_attribute25 t_varchar_med2;
669 l_t_pel_attribute26 t_varchar_med2;
670 l_t_pel_attribute27 t_varchar_med2;
671 l_t_pel_attribute28 t_varchar_med2;
672 l_t_pel_attribute29 t_varchar_med2;
673 l_t_pel_attribute30 t_varchar_med2;
674
675 begin
676
677
678 open c_pil;
679
680 loop
681 fetch c_pil bulk collect into
682 l_t_per_in_ler_id
683 ,l_t_person_id
684 ,l_t_assignment_id
685 ,l_t_pl_id
686 ,l_t_lf_evt_ocrd_dt
687 ,l_t_currency
688 ,l_t_comments
689 ,l_t_ws_mgr_id
690 ,l_t_mgr_ovrid_dt
691 ,l_t_mgr_ovrid_person_id
692 ,l_t_pel_attribute_category
693 ,l_t_pel_attribute1
694 ,l_t_pel_attribute2
695 ,l_t_pel_attribute3
696 ,l_t_pel_attribute4
697 ,l_t_pel_attribute5
698 ,l_t_pel_attribute6
699 ,l_t_pel_attribute7
700 ,l_t_pel_attribute8
701 ,l_t_pel_attribute9
702 ,l_t_pel_attribute10
703 ,l_t_pel_attribute11
704 ,l_t_pel_attribute12
705 ,l_t_pel_attribute13
706 ,l_t_pel_attribute14
707 ,l_t_pel_attribute15
708 ,l_t_pel_attribute16
709 ,l_t_pel_attribute17
710 ,l_t_pel_attribute18
711 ,l_t_pel_attribute19
712 ,l_t_pel_attribute20
713 ,l_t_pel_attribute21
714 ,l_t_pel_attribute22
715 ,l_t_pel_attribute23
716 ,l_t_pel_attribute24
717 ,l_t_pel_attribute25
718 ,l_t_pel_attribute26
719 ,l_t_pel_attribute27
720 ,l_t_pel_attribute28
721 ,l_t_pel_attribute29
722 ,l_t_pel_attribute30
723 limit g_commit_size;
724
725
726 if l_t_per_in_ler_id.count > 0 then
727
728 forall i in l_t_per_in_ler_id.first .. l_t_per_in_ler_id.last
729 insert into ben_cwb_person_info
730 (group_per_in_ler_id
731 ,assignment_id
732 ,person_id
733 ,group_pl_id
734 ,lf_evt_ocrd_dt
735 ,base_salary_currency
736 ,ws_comments
737 ,cpi_attribute_category
738 ,cpi_attribute1
739 ,cpi_attribute2
740 ,cpi_attribute3
741 ,cpi_attribute4
742 ,cpi_attribute5
743 ,cpi_attribute6
744 ,cpi_attribute7
745 ,cpi_attribute8
746 ,cpi_attribute9
747 ,cpi_attribute10
748 ,cpi_attribute11
749 ,cpi_attribute12
750 ,cpi_attribute13
751 ,cpi_attribute14
752 ,cpi_attribute15
753 ,cpi_attribute16
754 ,cpi_attribute17
755 ,cpi_attribute18
756 ,cpi_attribute19
757 ,cpi_attribute20
758 ,cpi_attribute21
759 ,cpi_attribute22
760 ,cpi_attribute23
761 ,cpi_attribute24
762 ,cpi_attribute25
763 ,cpi_attribute26
764 ,cpi_attribute27
765 ,cpi_attribute28
766 ,cpi_attribute29
767 ,cpi_attribute30
768 ,object_version_number)
769 values
770 (l_t_per_in_ler_id(i)
771 ,l_t_assignment_id(i)
772 ,l_t_person_id(i)
773 ,l_t_pl_id(i)
774 ,l_t_lf_evt_ocrd_dt(i)
775 ,l_t_currency(i)
776 ,l_t_comments(i)
777 ,l_t_pel_attribute_category(i)
778 ,l_t_pel_attribute1(i)
779 ,l_t_pel_attribute2(i)
780 ,l_t_pel_attribute3(i)
781 ,l_t_pel_attribute4(i)
782 ,l_t_pel_attribute5(i)
783 ,l_t_pel_attribute6(i)
784 ,l_t_pel_attribute7(i)
785 ,l_t_pel_attribute8(i)
786 ,l_t_pel_attribute9(i)
787 ,l_t_pel_attribute10(i)
788 ,l_t_pel_attribute11(i)
789 ,l_t_pel_attribute12(i)
790 ,l_t_pel_attribute13(i)
794 ,l_t_pel_attribute17(i)
791 ,l_t_pel_attribute14(i)
792 ,l_t_pel_attribute15(i)
793 ,l_t_pel_attribute16(i)
795 ,l_t_pel_attribute18(i)
796 ,l_t_pel_attribute19(i)
797 ,l_t_pel_attribute20(i)
798 ,l_t_pel_attribute21(i)
799 ,l_t_pel_attribute22(i)
800 ,l_t_pel_attribute23(i)
801 ,l_t_pel_attribute24(i)
802 ,l_t_pel_attribute25(i)
803 ,l_t_pel_attribute26(i)
804 ,l_t_pel_attribute27(i)
805 ,l_t_pel_attribute28(i)
806 ,l_t_pel_attribute29(i)
807 ,l_t_pel_attribute30(i)
808 ,1);
809
810
811 forall i in l_t_per_in_ler_id.first .. l_t_per_in_ler_id.last
812 update ben_per_in_ler pil
813 set pil.assignment_id = l_t_assignment_id(i)
814 ,pil.group_pl_id = l_t_pl_id (i)
815 ,pil.ws_mgr_id = l_t_ws_mgr_id(i)
816 ,pil.mgr_ovrid_dt = l_t_mgr_ovrid_dt(i)
817 ,pil.mgr_ovrid_person_id = l_t_mgr_ovrid_person_id(i)
818 where pil.per_in_ler_id = l_t_per_in_ler_id(i);
819
820
821 end if;
822
823 commit_and_log('Person Info Upgrade Cycle Complete');
824
825 if c_pil%notfound then
826 close c_pil;
827 exit;
828 end if;
829
830 end loop;
831
832 commit_and_log('Person Info Upgrade Complete');
833
834 end upgrade_person_info;
835
836 procedure upgrade_person_tasks is
837 begin
838
839 insert into ben_cwb_person_tasks
840 (group_per_in_ler_id
841 ,task_id
842 ,group_pl_id
843 ,lf_evt_ocrd_dt
844 ,status_cd
845 ,object_version_number)
846 select pil.per_in_ler_id
847 ,tk.cwb_wksht_grp_id
848 ,tk.pl_id
849 ,pil.lf_evt_ocrd_dt
850 ,decode(tk.wksht_grp_cd
851 ,'BDGT', decode(popl.bdgt_stat_cd, 'IP', 'IP','IS','CO', 'NS')
852 ,'APPR', decode(popl.ws_stat_cd,'PR', 'CO', 'AP', 'CO', 'NS')
853 ,'RVW', decode(popl.ws_stat_cd, 'PR','CO','AP', 'CO', 'NS')
854 ,decode(popl.ws_stat_cd,'IP','IP','PR','CO','PA','CO','CO','CO','NS')
855 ) status_cd
856 ,1
857 from ben_cwb_wksht_grp tk
858 ,ben_per_in_ler pil
859 ,ben_pil_elctbl_chc_popl popl
860 where pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
861 and pil.group_pl_id is null
862 and pil.assignment_id is null
863 and pil.per_in_ler_id = popl.per_in_ler_id
864 and popl.enrt_typ_cycl_cd = 'COMP'
865 and popl.assignment_id is not null
866 and popl.pgm_id is null
867 and popl.pl_id = tk.pl_id
868 and tk.status_cd = 'A';
869
870 commit_and_log('Person Tasks Upgrade Complete');
871
872 end upgrade_person_tasks;
873
874
875 -- the following functions are referred by refresh_person_info_group_pl
876 --
877 function get_years_in_job(p_assignment_id in number
878 ,p_job_id in number
879 ,p_effective_date in date
880 ,p_asg_effective_start_date in date)
881 return number is
882 --
883 l_years_in_job number;
884 --
885 begin
886 select trunc(sum(months_between(
887 decode(asgjob.effective_end_date,
888 to_date('4712/12/31', 'yyyy/mm/dd'),p_effective_date,
889 asgjob.effective_end_date+1),asgjob.effective_start_date))/12,1)
890 into l_years_in_job
891 from per_all_assignments_f asgjob
892 where asgjob.assignment_id=p_assignment_id
893 and asgjob.job_id = p_job_id
894 and asgjob.effective_start_date <= p_asg_effective_start_date;
895 --
896 return l_years_in_job;
897 end;
898
899 function get_years_in_position(p_assignment_id in number
900 ,p_position_id in number
901 ,p_effective_date in date
902 ,p_asg_effective_start_date in date)
903 return number is
904 --
905 l_years_in_position number;
906 --
907 begin
908 select trunc(sum(months_between(
909 decode(asgpos.effective_end_date,
910 to_date('4712/12/31', 'yyyy/mm/dd'),p_effective_date,
911 asgpos.effective_end_date+1),asgpos.effective_start_date))/12,1)
912 into l_years_in_position
913 from per_all_assignments_f asgpos
914 where asgpos.assignment_id=p_assignment_id
915 and asgpos.position_id = p_position_id
916 and asgpos.effective_start_date <= p_asg_effective_start_date;
917 --
918 return l_years_in_position;
919 end;
920
921 function get_years_in_grade(p_assignment_id in number
922 ,p_grade_id in number
923 ,p_effective_date in date
924 ,p_asg_effective_start_date in date)
925 return number is
926 --
927 l_years_in_grade number;
928 --
929 begin
930 select trunc(sum(months_between(
931 decode(asggrd.effective_end_date,
932 to_date('4712/12/31', 'yyyy/mm/dd'),p_effective_date,
933 asggrd.effective_end_date+1),asggrd.effective_start_date))/12,1)
934 into l_years_in_grade
935 from per_all_assignments_f asggrd
939 --
936 where asggrd.assignment_id=p_assignment_id
937 and asggrd.grade_id = p_grade_id
938 and asggrd.effective_start_date <= p_asg_effective_start_date;
940 return l_years_in_grade;
941 end; -- get_years_in_grade
942
943 function get_grd_min_val(p_grade_id in number
944 ,p_rate_id in number
945 ,p_effective_date in date)
946 return number is
947 --
948 l_grd_min_val number;
949 --
950 begin
951 select fnd_number.canonical_to_number(minimum) into l_grd_min_val
952 from pay_grade_rules_f grdrule
953 where grdrule.rate_id = p_rate_id
954 and grdrule.grade_or_spinal_point_id = p_grade_id
955 and p_effective_date between grdrule.effective_start_date
956 and grdrule.effective_end_date;
957 --
958 return l_grd_min_val;
959 end; -- get_grd_min_val
960
961 function get_grd_max_val(p_grade_id in number
962 ,p_rate_id in number
963 ,p_effective_date in date)
964 return number is
965 --
966 l_grd_max_val number;
967 --
968 begin
969 select fnd_number.canonical_to_number(maximum) into l_grd_max_val
970 from pay_grade_rules_f grdrule
971 where grdrule.rate_id = p_rate_id
972 and grdrule.grade_or_spinal_point_id = p_grade_id
973 and p_effective_date between grdrule.effective_start_date
974 and grdrule.effective_end_date;
975 --
976 return l_grd_max_val;
977 end; -- get_grd_max_val
978
979 function get_grd_mid_point(p_grade_id in number
980 ,p_rate_id in number
981 ,p_effective_date in date)
982 return number is
983 --
984 l_grd_mid_point number;
985 --
986 begin
987 select fnd_number.canonical_to_number(mid_value) into l_grd_mid_point
988 from pay_grade_rules_f grdrule
989 where grdrule.rate_id = p_rate_id
990 and grdrule.grade_or_spinal_point_id = p_grade_id
991 and p_effective_date between grdrule.effective_start_date
992 and grdrule.effective_end_date;
993 --
994 return l_grd_mid_point;
995 end; -- get_grd_mid_point
996 --
997 -- The above functions are referred by refresh_person_info_group_pl
998
999
1000 procedure refresh_person_info_group_pl(p_group_pl_id in number,
1001 p_lf_evt_ocrd_dt in date) is
1002
1003 l_performance_rating_type varchar2(30);
1004
1005 cursor c_person_info is
1006 select pers.group_per_in_ler_id group_per_in_ler_id
1007 ,pil.lf_evt_ocrd_dt effective_date
1008 ,ppf.full_name full_name
1009 ,ppf.first_name ||' '||ppf.last_name||' '||ppf.suffix brief_name
1010 ,null custom_name
1011 ,paf.supervisor_id supervisor_id
1012 ,supv.full_name supervisor_full_name
1013 ,supv.first_name||' '||supv.last_name||' '||supv.suffix
1014 supervisor_brief_name
1015 ,null supervisor_custom_name
1016 ,bg.legislation_code legislation_code
1017 ,trunc(months_between(p_lf_evt_ocrd_dt,
1018 nvl(service_period.adjusted_svc_date,
1019 nvl(service_period.date_start,
1020 ppf.start_date)))/12,1) years_employed
1021 ,get_years_in_job(paf.assignment_id
1022 ,paf.job_id
1023 ,p_lf_evt_ocrd_dt
1024 ,paf.effective_start_date) years_in_job
1025 ,get_years_in_position(paf.assignment_id
1026 ,paf.position_id
1027 ,p_lf_evt_ocrd_dt
1028 ,paf.effective_start_date) years_in_position
1029 ,get_years_in_grade(paf.assignment_id
1030 ,paf.grade_id
1031 ,p_lf_evt_ocrd_dt
1032 ,paf.effective_start_date) years_in_grade
1033 ,ppf.employee_number employee_number
1034 ,nvl(service_period.date_start,ppf.start_date) start_date
1035 ,ppf.original_date_of_hire original_start_date
1036 ,service_period.adjusted_svc_date adjusted_svc_date
1037 ,ppp.proposed_salary_n base_salary
1038 ,ppp.change_date base_salary_change_date
1039 ,pay.payroll_name payroll_name
1040 ,perf.performance_rating performance_rating
1041 ,perf.review_date performance_rating_date
1042 ,paf.business_group_id business_group_id
1043 ,paf.organization_id organization_id
1044 ,paf.job_id job_id
1045 ,paf.grade_id grade_id
1046 ,paf.position_id position_id
1047 ,paf.people_group_id people_group_id
1048 ,paf.soft_coding_keyflex_id soft_coding_keyflex_id
1049 ,paf.location_id location_id
1050 ,ppb.rate_id pay_rate_id
1051 ,nvl(ppb.grade_annualization_factor,1) grade_annualization_factor
1052 ,nvl(ppb.pay_annualization_factor,1) pay_annualization_factor
1053 ,get_grd_min_val(paf.grade_id
1054 ,ppb.rate_id
1055 ,p_lf_evt_ocrd_dt) grd_min_val
1056 ,get_grd_max_val(paf.grade_id
1057 ,ppb.rate_id
1058 ,p_lf_evt_ocrd_dt) grd_max_val
1059 ,get_grd_mid_point(paf.grade_id
1060 ,ppb.rate_id
1061 ,p_lf_evt_ocrd_dt) grd_mid_point
1062 ,paf.employment_category emp_category
1066 ,paf.assignment_status_type_id
1063 ,paf.change_reason change_reason
1064 ,paf.normal_hours normal_hours
1065 ,ppf.email_address email_address
1067 ,paf.frequency
1068 ,paf.ass_attribute_category ass_attribute_category
1069 ,paf.ass_attribute1 ass_attribute1
1070 ,paf.ass_attribute2 ass_attribute2
1071 ,paf.ass_attribute3 ass_attribute3
1072 ,paf.ass_attribute4 ass_attribute4
1073 ,paf.ass_attribute5 ass_attribute5
1074 ,paf.ass_attribute6 ass_attribute6
1075 ,paf.ass_attribute7 ass_attribute7
1076 ,paf.ass_attribute8 ass_attribute8
1077 ,paf.ass_attribute9 ass_attribute9
1078 ,paf.ass_attribute10 ass_attribute10
1079 ,paf.ass_attribute11 ass_attribute11
1080 ,paf.ass_attribute12 ass_attribute12
1081 ,paf.ass_attribute13 ass_attribute13
1082 ,paf.ass_attribute14 ass_attribute14
1083 ,paf.ass_attribute15 ass_attribute15
1084 ,paf.ass_attribute16 ass_attribute16
1085 ,paf.ass_attribute17 ass_attribute17
1086 ,paf.ass_attribute18 ass_attribute18
1087 ,paf.ass_attribute19 ass_attribute19
1088 ,paf.ass_attribute20 ass_attribute20
1089 ,paf.ass_attribute21 ass_attribute21
1090 ,paf.ass_attribute22 ass_attribute22
1091 ,paf.ass_attribute23 ass_attribute23
1092 ,paf.ass_attribute24 ass_attribute24
1093 ,paf.ass_attribute25 ass_attribute25
1094 ,paf.ass_attribute26 ass_attribute26
1095 ,paf.ass_attribute27 ass_attribute27
1096 ,paf.ass_attribute28 ass_attribute28
1097 ,paf.ass_attribute29 ass_attribute29
1098 ,paf.ass_attribute30 ass_attribute30
1099 ,perf.appraisal_id appraisal_id
1100 from per_all_people_f ppf
1101 ,per_all_assignments_f paf
1102 ,ben_per_in_ler pil
1103 ,per_all_people_f supv
1104 ,per_business_groups bg
1105 ,per_periods_of_service service_period
1106 ,per_pay_proposals ppp
1107 ,pay_all_payrolls_f pay
1108 ,ben_cwb_person_info pers
1109 ,(select rtg1.review_date review_date
1110 ,rtg1.performance_rating performance_rating
1111 ,rtg1.person_id person_id
1112 ,apr.appraisal_id
1113 from per_performance_reviews rtg1
1114 ,per_events evt1
1115 ,per_appraisals apr
1116 where rtg1.event_id = evt1.event_id (+)
1117 and rtg1.review_date < p_lf_evt_ocrd_dt
1118 and nvl(evt1.type, '-X-X-X-') = nvl(l_performance_rating_type, '-X-X-X-')
1119 and evt1.event_id = apr.event_id(+)) perf
1120 ,per_pay_bases ppb
1121 where pil.group_pl_id = p_group_pl_id
1122 and pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
1123 and pil.per_in_ler_id = pers.group_per_in_ler_id
1124 and paf.assignment_id = pil.assignment_id
1125 and p_lf_evt_ocrd_dt between paf.effective_start_date and
1126 paf.effective_end_date
1127 and paf.person_id = ppf.person_id
1128 and p_lf_evt_ocrd_dt between ppf.effective_start_date and
1129 ppf.effective_end_date
1130 and paf.supervisor_id = supv.person_id (+)
1131 and p_lf_evt_ocrd_dt between supv.effective_start_date (+) and
1132 supv.effective_end_date (+)
1133 and bg.business_group_id = paf.business_group_id
1134 and paf.period_of_service_id = service_period.period_of_service_id (+)
1135 and paf.assignment_id = ppp.assignment_id (+)
1136 and ppp.approved (+) = 'Y'
1137 and ppp.change_date (+) <= p_lf_evt_ocrd_dt
1138 and nvl(ppp.change_date,to_date('4712/12/31', 'yyyy/mm/dd')) =
1139 (select nvl(max(ppp1.change_date), to_date('4712/12/31',
1140 'yyyy/mm/dd'))
1141 from per_pay_proposals ppp1
1142 where ppp1.assignment_id = ppp.assignment_id
1143 and ppp1.change_date <= p_lf_evt_ocrd_dt
1144 and ppp1.approved = 'Y')
1145 and paf.payroll_id = pay.payroll_id (+)
1146 and p_lf_evt_ocrd_dt between pay.effective_start_date (+) and
1147 pay.effective_end_date (+)
1148 and ppf.person_id = perf.person_id (+)
1149 and nvl(perf.review_date, to_date('4712/12/31', 'yyyy/mm/dd')) =
1150 (select nvl(max(rtg2.review_date),to_date('4712/12/31',
1151 'yyyy/mm/dd'))
1152 from per_performance_reviews rtg2
1153 ,per_events evt2
1154 where rtg2.person_id = ppf.person_id
1155 and rtg2.review_date < p_lf_evt_ocrd_dt
1156 and rtg2.event_id = evt2.event_id (+)
1157 and nvl(evt2.type, '-X-X-X-') = nvl(l_performance_rating_type, '-X-X-X-') )
1158 and paf.pay_basis_id = ppb.pay_basis_id (+);
1159
1160 l_t_group_per_in_ler_id t_id;
1161 l_t_effective_date t_date;
1162 l_t_full_name t_varchar_med;
1163 l_t_brief_name t_varchar_med3;
1164 l_t_custom_name t_varchar_med;
1165 l_t_supervisor_id t_id;
1166 l_t_supervisor_full_name t_varchar_med;
1167 l_t_supervisor_brief_name t_varchar_med3;
1168 l_t_supervisor_custom_name t_varchar_med;
1172 l_t_years_in_position t_val;
1169 l_t_legislation_code t_varchar_small;
1170 l_t_years_employed t_val;
1171 l_t_years_in_job t_val;
1173 l_t_years_in_grade t_val;
1174 l_t_employee_number t_varchar_small;
1175 l_t_start_date t_date;
1176 l_t_original_start_date t_date;
1177 l_t_adjusted_svc_date t_date;
1178 l_t_base_salary t_val;
1179 l_t_base_salary_change_date t_date;
1180 l_t_payroll_name t_varchar_med;
1181 l_t_performance_rating t_varchar_small;
1182 l_t_performance_rating_date t_date;
1183 l_t_business_group_id t_id;
1184 l_t_organization_id t_id;
1185 l_t_job_id t_id;
1186 l_t_grade_id t_id;
1187 l_t_position_id t_id;
1188 l_t_people_group_id t_id;
1189 l_t_soft_coding_keyflex_id t_id;
1190 l_t_location_id t_id;
1191 l_t_pay_rate_id t_id;
1192 l_t_grade_annualization_factor t_val;
1193 l_t_pay_annualization_factor t_val;
1194 l_t_grd_min_val t_val;
1195 l_t_grd_max_val t_val;
1196 l_t_grd_mid_point t_val;
1197 l_t_emp_category t_varchar_small;
1198 l_t_change_reason t_varchar_small;
1199 l_t_normal_hours t_val;
1200 l_t_email_address t_varchar_med;
1201 l_t_assignment_status_type_id t_id;
1202 l_t_frequency t_varchar_small;
1203 l_t_ass_attribute_category t_varchar_small;
1204 l_t_ass_attribute1 t_varchar_med2;
1205 l_t_ass_attribute2 t_varchar_med2;
1206 l_t_ass_attribute3 t_varchar_med2;
1207 l_t_ass_attribute4 t_varchar_med2;
1208 l_t_ass_attribute5 t_varchar_med2;
1209 l_t_ass_attribute6 t_varchar_med2;
1210 l_t_ass_attribute7 t_varchar_med2;
1211 l_t_ass_attribute8 t_varchar_med2;
1212 l_t_ass_attribute9 t_varchar_med2;
1213 l_t_ass_attribute10 t_varchar_med2;
1214 l_t_ass_attribute11 t_varchar_med2;
1215 l_t_ass_attribute12 t_varchar_med2;
1216 l_t_ass_attribute13 t_varchar_med2;
1217 l_t_ass_attribute14 t_varchar_med2;
1218 l_t_ass_attribute15 t_varchar_med2;
1219 l_t_ass_attribute16 t_varchar_med2;
1220 l_t_ass_attribute17 t_varchar_med2;
1221 l_t_ass_attribute18 t_varchar_med2;
1222 l_t_ass_attribute19 t_varchar_med2;
1223 l_t_ass_attribute20 t_varchar_med2;
1224 l_t_ass_attribute21 t_varchar_med2;
1225 l_t_ass_attribute22 t_varchar_med2;
1226 l_t_ass_attribute23 t_varchar_med2;
1227 l_t_ass_attribute24 t_varchar_med2;
1228 l_t_ass_attribute25 t_varchar_med2;
1229 l_t_ass_attribute26 t_varchar_med2;
1230 l_t_ass_attribute27 t_varchar_med2;
1231 l_t_ass_attribute28 t_varchar_med2;
1232 l_t_ass_attribute29 t_varchar_med2;
1233 l_t_ass_attribute30 t_varchar_med2;
1234 l_t_appraisal_id t_id;
1235
1236 begin
1237
1238
1239 select emp_interview_typ_cd
1240 into l_performance_rating_type
1241 from ben_cwb_pl_dsgn pldsgn
1242 where pldsgn.pl_id = p_group_pl_id
1243 and pldsgn.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
1244 and pldsgn.oipl_id = -1 ;
1245
1246 open c_person_info;
1247
1248 loop
1249
1250 fetch c_person_info bulk collect into
1251 l_t_group_per_in_ler_id,
1252 l_t_effective_date,
1253 l_t_full_name,
1254 l_t_brief_name,
1255 l_t_custom_name,
1256 l_t_supervisor_id,
1257 l_t_supervisor_full_name,
1258 l_t_supervisor_brief_name,
1259 l_t_supervisor_custom_name,
1260 l_t_legislation_code,
1261 l_t_years_employed,
1262 l_t_years_in_job,
1263 l_t_years_in_position,
1264 l_t_years_in_grade,
1265 l_t_employee_number,
1266 l_t_start_date,
1267 l_t_original_start_date,
1268 l_t_adjusted_svc_date,
1269 l_t_base_salary,
1270 l_t_base_salary_change_date,
1271 l_t_payroll_name,
1272 l_t_performance_rating,
1273 l_t_performance_rating_date,
1274 l_t_business_group_id,
1275 l_t_organization_id,
1276 l_t_job_id,
1277 l_t_grade_id,
1278 l_t_position_id,
1279 l_t_people_group_id,
1280 l_t_soft_coding_keyflex_id,
1281 l_t_location_id,
1282 l_t_pay_rate_id,
1283 l_t_grade_annualization_factor,
1284 l_t_pay_annualization_factor,
1285 l_t_grd_min_val,
1286 l_t_grd_max_val,
1290 l_t_normal_hours,
1287 l_t_grd_mid_point,
1288 l_t_emp_category,
1289 l_t_change_reason,
1291 l_t_email_address,
1292 l_t_assignment_status_type_id,
1293 l_t_frequency,
1294 l_t_ass_attribute_category,
1295 l_t_ass_attribute1,
1296 l_t_ass_attribute2,
1297 l_t_ass_attribute3,
1298 l_t_ass_attribute4,
1299 l_t_ass_attribute5,
1300 l_t_ass_attribute6,
1301 l_t_ass_attribute7,
1302 l_t_ass_attribute8,
1303 l_t_ass_attribute9,
1304 l_t_ass_attribute10,
1305 l_t_ass_attribute11,
1306 l_t_ass_attribute12,
1307 l_t_ass_attribute13,
1308 l_t_ass_attribute14,
1309 l_t_ass_attribute15,
1310 l_t_ass_attribute16,
1311 l_t_ass_attribute17,
1312 l_t_ass_attribute18,
1313 l_t_ass_attribute19,
1314 l_t_ass_attribute20,
1315 l_t_ass_attribute21,
1316 l_t_ass_attribute22,
1317 l_t_ass_attribute23,
1318 l_t_ass_attribute24,
1319 l_t_ass_attribute25,
1320 l_t_ass_attribute26,
1321 l_t_ass_attribute27,
1322 l_t_ass_attribute28,
1323 l_t_ass_attribute29,
1324 l_t_ass_attribute30,
1325 l_t_appraisal_id
1326 limit g_commit_size;
1327
1328 if l_t_group_per_in_ler_id.count > 0 then
1329 forall i in l_t_group_per_in_ler_id.first .. l_t_group_per_in_ler_id.last
1330 update ben_cwb_person_info
1331 set effective_date = l_t_effective_date(i)
1332 ,full_name = l_t_full_name(i)
1333 ,brief_name = l_t_brief_name(i)
1334 ,custom_name = l_t_custom_name(i)
1335 ,supervisor_id = l_t_supervisor_id(i)
1336 ,supervisor_full_name = l_t_supervisor_full_name(i)
1337 ,supervisor_brief_name = l_t_supervisor_brief_name(i)
1338 ,supervisor_custom_name = l_t_supervisor_custom_name(i)
1339 ,legislation_code = l_t_legislation_code(i)
1340 ,years_employed = l_t_years_employed(i)
1341 ,years_in_job = l_t_years_in_job(i)
1342 ,years_in_position = l_t_years_in_position(i)
1343 ,years_in_grade = l_t_years_in_grade(i)
1344 ,employee_number = l_t_employee_number(i)
1345 ,start_date = l_t_start_date(i)
1346 ,original_start_date = l_t_original_start_date(i)
1347 ,adjusted_svc_date = l_t_adjusted_svc_date(i)
1348 ,base_salary = l_t_base_salary(i)
1349 ,base_salary_change_date = l_t_base_salary_change_date(i)
1350 ,payroll_name = l_t_payroll_name(i)
1351 ,performance_rating = l_t_performance_rating(i)
1352 ,performance_rating_type = l_performance_rating_type
1353 ,performance_rating_date = l_t_performance_rating_date(i)
1354 ,business_group_id = l_t_business_group_id(i)
1355 ,organization_id = l_t_organization_id(i)
1356 ,job_id = l_t_job_id(i)
1357 ,grade_id = l_t_grade_id(i)
1358 ,position_id = l_t_position_id(i)
1359 ,people_group_id = l_t_people_group_id(i)
1360 ,soft_coding_keyflex_id = l_t_soft_coding_keyflex_id(i)
1361 ,location_id = l_t_location_id(i)
1362 ,pay_rate_id = l_t_pay_rate_id(i)
1363 ,grade_annulization_factor= l_t_grade_annualization_factor(i)
1364 ,pay_annulization_factor = l_t_pay_annualization_factor(i)
1365 ,grd_min_val = l_t_grd_min_val(i)
1366 ,grd_max_val = l_t_grd_max_val(i)
1367 ,grd_mid_point = l_t_grd_mid_point(i)
1368 ,emp_category = l_t_emp_category(i)
1369 ,change_reason = l_t_change_reason(i)
1370 ,normal_hours = l_t_normal_hours(i)
1371 ,email_address = l_t_email_address(i)
1372 ,assignment_status_type_id = l_t_assignment_status_type_id(i)
1373 ,frequency = l_t_frequency(i)
1374 ,ass_attribute_category = l_t_ass_attribute_category(i)
1375 ,ass_attribute1 = l_t_ass_attribute1(i)
1376 ,ass_attribute2 = l_t_ass_attribute2(i)
1377 ,ass_attribute3 = l_t_ass_attribute3(i)
1378 ,ass_attribute4 = l_t_ass_attribute4(i)
1379 ,ass_attribute5 = l_t_ass_attribute5(i)
1380 ,ass_attribute6 = l_t_ass_attribute6(i)
1381 ,ass_attribute7 = l_t_ass_attribute7(i)
1382 ,ass_attribute8 = l_t_ass_attribute8(i)
1383 ,ass_attribute9 = l_t_ass_attribute9(i)
1384 ,ass_attribute10 = l_t_ass_attribute10(i)
1385 ,ass_attribute11 = l_t_ass_attribute11(i)
1386 ,ass_attribute12 = l_t_ass_attribute12(i)
1387 ,ass_attribute13 = l_t_ass_attribute13(i)
1388 ,ass_attribute14 = l_t_ass_attribute14(i)
1389 ,ass_attribute15 = l_t_ass_attribute15(i)
1390 ,ass_attribute16 = l_t_ass_attribute16(i)
1394 ,ass_attribute20 = l_t_ass_attribute20(i)
1391 ,ass_attribute17 = l_t_ass_attribute17(i)
1392 ,ass_attribute18 = l_t_ass_attribute18(i)
1393 ,ass_attribute19 = l_t_ass_attribute19(i)
1395 ,ass_attribute21 = l_t_ass_attribute21(i)
1396 ,ass_attribute22 = l_t_ass_attribute22(i)
1397 ,ass_attribute23 = l_t_ass_attribute23(i)
1398 ,ass_attribute24 = l_t_ass_attribute24(i)
1399 ,ass_attribute25 = l_t_ass_attribute25(i)
1400 ,ass_attribute26 = l_t_ass_attribute26(i)
1401 ,ass_attribute27 = l_t_ass_attribute27(i)
1402 ,ass_attribute28 = l_t_ass_attribute28(i)
1403 ,ass_attribute29 = l_t_ass_attribute29(i)
1404 ,ass_attribute30 = l_t_ass_attribute30(i)
1405 ,appraisal_id = l_t_appraisal_id(i)
1406 where group_per_in_ler_id = l_t_group_per_in_ler_id(i);
1407
1408 end if;
1409
1410 commit_and_log('Refresh Person Info Cycle Complete');
1411
1412 if (c_person_info%notfound) then
1413 close c_person_info;
1414 exit;
1415 end if;
1416
1417 end loop;
1418
1419 commit_and_log('Refresh Person Info Complete');
1420
1421
1422 end refresh_person_info_group_pl;
1423
1424
1425 procedure upgrade_summary is
1426
1427 cursor c_plans is
1428 select distinct pl.group_pl_id
1429 ,pl.lf_evt_ocrd_dt
1430 from ben_cwb_pl_dsgn pl
1431 where pl.pl_id = pl.group_pl_id
1432 and not exists (select 'Y'
1433 from ben_cwb_summary summ
1434 where summ.group_pl_id = pl.group_pl_id
1435 and summ.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt);
1436
1437
1438 begin
1439 commit_and_log('Gather stats started');
1440 fnd_stats.gather_table_stats(ownname => 'BEN',tabname => 'BEN_CWB_GROUP_HRCHY');
1441 fnd_stats.gather_table_stats(ownname => 'BEN',tabname => 'BEN_CWB_PERSON_GROUPS');
1442 fnd_stats.gather_table_stats(ownname => 'BEN',tabname => 'BEN_CWB_PERSON_RATES');
1443 fnd_stats.gather_table_stats(ownname => 'BEN',tabname => 'BEN_CWB_PERSON_INFO');
1444 fnd_stats.gather_table_stats(ownname => 'BEN',tabname => 'BEN_PER_IN_LER');
1445 commit_and_log('Gather stats complete');
1446
1447
1448 for l_plans in c_plans loop
1449
1450 refresh_person_info_group_pl
1451 (p_group_pl_id => l_plans.group_pl_id
1452 ,p_lf_evt_ocrd_dt=> l_plans.lf_evt_ocrd_dt);
1453
1454 commit_and_log('Refresh Person Info Done for ' ||
1455 to_char(l_plans.group_pl_id) || ' Date ' ||
1456 to_char(l_plans.lf_evt_ocrd_dt, 'yyyy/mm/dd'));
1457
1458 ben_cwb_summary_pkg.refresh_summary_group_pl
1459 (p_group_pl_id => l_plans.group_pl_id
1460 ,p_lf_evt_ocrd_dt => l_plans.lf_evt_ocrd_dt);
1461
1462 commit_and_log('Refresh Summary Done for ' ||
1463 to_char(l_plans.group_pl_id) || ' Date ' ||
1464 to_char(l_plans.lf_evt_ocrd_dt, 'yyyy/mm/dd'));
1465
1466 end loop;
1467
1468 end upgrade_summary;
1469
1470
1471 procedure upgrade_transaction_data is
1472
1473 cursor c_pil is
1474 select 'Y'
1475 from ben_per_in_ler pil
1476 ,ben_ler_f ler
1477 where pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
1478 and pil.ler_id = ler.ler_id
1479 and ler.typ_cd = 'COMP'
1480 and pil.assignment_id is null
1481 and pil.group_pl_id is null;
1482
1483 l_exists char(1) := 'N';
1484
1485 begin
1486
1487
1488 open c_pil;
1489 fetch c_pil into l_exists;
1490 close c_pil;
1491
1492 if l_exists = 'N' then
1493 return;
1494 end if;
1495
1496
1497 upgrade_hrchy();
1498 upgrade_person_groups();
1499 upgrade_person_rates();
1500 upgrade_person_tasks();
1501 upgrade_person_info();
1502
1503 end upgrade_transaction_data;
1504
1505 procedure upgrade_temp_data is
1506
1507 cursor c_rank is
1508 select ext.assignment_extra_info_id
1509 ,pen.per_in_ler_id
1510 from per_assignment_extra_info ext
1511 ,per_all_assignments_f asg
1512 ,ben_prtt_enrt_rslt_f pen
1513 where ext.information_type = 'CWBRANK'
1514 and ext.aei_information_category = 'CWBRANK'
1515 and ext.aei_information3 is not null
1516 and ext.aei_information5 is not null
1517 and ext.aei_information6 is not null
1518 and ext.assignment_id = asg.assignment_id
1519 and fnd_date.canonical_to_date(ext.aei_information5) between
1520 asg.effective_start_date and asg.effective_end_date
1521 and asg.person_id = pen.person_id
1522 and to_number(ext.aei_information3) = pen.prtt_enrt_rslt_id
1523 and to_number(ext.aei_information6) = pen.pl_id
1524 and fnd_date.canonical_to_date(ext.aei_information5) between
1525 pen.effective_start_date and pen.effective_end_date
1526 and pen.per_in_ler_id is not null;
1527
1528 cursor c_epe_rsgn_appr is
1529 select pil.per_in_ler_id
1530 ,tx.transaction_id
1531 from ben_transaction tx
1535 and tx.attribute1 = 'APPR'
1532 ,ben_elig_per_elctbl_chc epe
1533 ,ben_per_in_ler pil
1534 where tx.transaction_type = 'CWBEMPRSGN'
1536 and epe.elig_per_elctbl_chc_id = to_number(tx.attribute10)
1537 and epe.per_in_ler_id = pil.per_in_ler_id
1538 and pil.person_id = to_number(tx.attribute13);
1539
1540 cursor c_pel_rsgn_appr is
1541 select pil.per_in_ler_id
1542 ,tx.transaction_id
1543 from ben_transaction tx
1544 ,ben_pil_elctbl_chc_popl pel
1545 ,ben_per_in_ler pil
1546 where tx.transaction_type = 'CWBEMPRSGN'
1547 and tx.attribute1 = 'APPR'
1548 and pel.pil_elctbl_chc_popl_id = to_number(tx.attribute10)
1549 and pel.per_in_ler_id = pil.per_in_ler_id
1550 and pil.person_id = to_number(tx.attribute13);
1551
1552 cursor c_epe_rsgn_emp is
1553 select emp_pil.per_in_ler_id
1554 ,curr_mgr_pil.per_in_ler_id
1555 ,prop_mgr_pil.per_in_ler_id
1556 ,emp.pl_id
1557 ,tx.transaction_id
1558 from ben_transaction tx
1559 ,ben_elig_per_elctbl_chc emp
1560 ,ben_per_in_ler emp_pil
1561 ,ben_elig_per_elctbl_chc curr_mgr
1562 ,ben_per_in_ler curr_mgr_pil
1563 ,ben_elig_per_elctbl_chc prop_mgr
1564 ,ben_per_in_ler prop_mgr_pil
1565 where tx.transaction_type = 'CWBEMPRSGN'
1566 and tx.attribute1 = 'EMP'
1567 and tx.attribute21 is null
1568 and emp.elig_per_elctbl_chc_id = to_number(tx.attribute3)
1569 and emp.per_in_ler_id = emp_pil.per_in_ler_id
1570 and curr_mgr.elig_per_elctbl_chc_id = to_number(tx.attribute16)
1571 and curr_mgr.per_in_ler_id = curr_mgr_pil.per_in_ler_id
1572 and prop_mgr.elig_per_elctbl_chc_id = to_number(tx.attribute14)
1573 and prop_mgr.per_in_ler_id = prop_mgr_pil.per_in_ler_id
1574 and emp.pl_id = curr_mgr.pl_id
1575 and emp.pl_id = prop_mgr.pl_id
1576 and emp_pil.ler_id = curr_mgr_pil.ler_id
1577 and emp_pil.ler_id = prop_mgr_pil.ler_id;
1578
1579 cursor c_pel_rsgn_emp is
1580 select emp_pil.per_in_ler_id
1581 ,curr_mgr_pil.per_in_ler_id
1582 ,prop_mgr_pil.per_in_ler_id
1583 ,emp.pl_id
1584 ,tx.transaction_id
1585 from ben_transaction tx
1586 ,ben_pil_elctbl_chc_popl emp
1587 ,ben_per_in_ler emp_pil
1588 ,ben_pil_elctbl_chc_popl curr_mgr
1589 ,ben_per_in_ler curr_mgr_pil
1590 ,ben_pil_elctbl_chc_popl prop_mgr
1591 ,ben_per_in_ler prop_mgr_pil
1592 where tx.transaction_type = 'CWBEMPRSGN'
1593 and tx.attribute1 = 'EMP'
1594 and tx.attribute21 is null
1595 and emp.pil_elctbl_chc_popl_id = to_number(tx.attribute3)
1596 and emp.per_in_ler_id = emp_pil.per_in_ler_id
1597 and curr_mgr.pil_elctbl_chc_popl_id = to_number(tx.attribute16)
1598 and curr_mgr.per_in_ler_id = curr_mgr_pil.per_in_ler_id
1599 and prop_mgr.pil_elctbl_chc_popl_id = to_number(tx.attribute14)
1600 and prop_mgr.per_in_ler_id = prop_mgr_pil.per_in_ler_id
1601 and emp.pl_id = curr_mgr.pl_id
1602 and emp.pl_id = prop_mgr.pl_id
1603 and emp_pil.ler_id = curr_mgr_pil.ler_id
1604 and emp_pil.ler_id = prop_mgr_pil.ler_id;
1605
1606 l_t_info_id t_id;
1607 l_t_per_in_ler_id t_id;
1608
1609 l_t_emp_pil_id t_id;
1610 l_t_curr_mgr_pil_id t_id;
1611 l_t_prop_mgr_pil_id t_id;
1612 l_t_grp_pl_id t_id;
1613 l_t_txn_id t_id;
1614
1615 begin
1616
1617 -- Rank Updates
1618
1619 update per_assignment_extra_info ext
1620 set ext.aei_information1 = rtrim(ltrim(ext.aei_information1))
1621 where ext.information_type = 'CWBRANK'
1622 and ext.aei_information_category = 'CWBRANK'
1623 and ext.aei_information1 is not null;
1624
1625 commit_and_log('Removing Extra Spaces from Rank Complete');
1626
1627 update per_assignment_extra_info ext
1628 set ext.aei_information1 = null
1629 where ext.information_type = 'CWBRANK'
1630 and ext.aei_information_category = 'CWBRANK'
1631 and ext.aei_information1 is not null
1632 and replace(translate(ext.aei_information1, '0123456789',' ')
1633 ,' ') is not null;
1634
1635 commit_and_log('Invalid Ranks Check Complete');
1636 --
1637
1638 open c_rank;
1639 loop
1640 fetch c_rank bulk collect into
1641 l_t_info_id
1642 ,l_t_per_in_ler_id
1643 limit g_commit_size;
1644
1645 if l_t_info_id.count > 0 then
1646
1647 forall i in l_t_info_id.first .. l_t_info_id.last
1648 update per_assignment_extra_info ext
1649 set ext.aei_information3 = l_t_per_in_ler_id(i)
1650 where ext.assignment_extra_info_id = l_t_info_id(i);
1651
1652 end if;
1653
1657 close c_rank;
1654 commit_and_log('Rank Upgrade Cycle Complete');
1655
1656 if c_rank%notfound then
1658 exit;
1659 end if;
1660
1661 end loop;
1662
1663 commit_and_log('Rank Upgrade Complete');
1664
1665 insert into ben_transaction
1666 (transaction_id
1667 ,transaction_type
1668 ,status
1669 ,attribute1
1670 ,attribute2
1671 ,attribute3)
1672 select popl.assignment_id
1673 ,'CWBPERF'||tx.attribute1||enp.emp_interview_type_cd
1674 ,tx.status
1675 ,tx.attribute1
1676 ,enp.emp_interview_type_cd
1677 ,tx.attribute3
1678 from ben_transaction tx
1679 ,ben_pil_elctbl_chc_popl popl
1680 ,ben_enrt_perd enp
1681 where tx.transaction_type = 'CWBWSASG'
1682 and tx.attribute1 is not null
1683 and tx.attribute3 is not null
1684 and tx.transaction_id = popl.pil_elctbl_chc_popl_id
1685 and popl.enrt_perd_id = enp.enrt_perd_id;
1686
1687
1688 insert into ben_transaction
1689 (transaction_id
1690 ,transaction_type
1691 ,status
1692 ,attribute1
1693 ,attribute3
1694 ,attribute5
1695 ,attribute6
1696 ,attribute7
1697 ,attribute8
1698 ,attribute9
1699 ,attribute10
1700 ,attribute11
1701 ,attribute12
1702 ,attribute13
1703 ,attribute14
1704 ,attribute15
1705 ,attribute16
1706 ,attribute17
1707 ,attribute18
1708 ,attribute19
1709 ,attribute20
1710 ,attribute21
1711 ,attribute22
1712 ,attribute23
1713 ,attribute24
1714 ,attribute25
1715 ,attribute26
1716 ,attribute27
1717 ,attribute28
1718 ,attribute29
1719 ,attribute30
1720 ,attribute31
1721 ,attribute32
1722 ,attribute33
1723 ,attribute34
1724 ,attribute35
1725 ,attribute36
1726 ,attribute37
1727 ,attribute38
1728 ,attribute39
1729 ,attribute40 )
1730 select popl.assignment_id
1731 ,'CWBASG'||tx.attribute2
1732 ,tx.status
1733 ,tx.attribute2
1734 ,tx.attribute4
1735 ,tx.attribute5
1736 ,tx.attribute6
1737 ,tx.attribute7
1738 ,tx.attribute8
1739 ,tx.attribute9
1740 ,tx.attribute10
1741 ,tx.attribute11
1742 ,tx.attribute12
1743 ,tx.attribute13
1744 ,tx.attribute14
1745 ,tx.attribute15
1746 ,tx.attribute16
1747 ,tx.attribute17
1748 ,tx.attribute18
1749 ,tx.attribute19
1750 ,tx.attribute20
1751 ,tx.attribute21
1752 ,tx.attribute22
1753 ,tx.attribute23
1754 ,tx.attribute24
1755 ,tx.attribute25
1756 ,tx.attribute26
1757 ,tx.attribute27
1758 ,tx.attribute28
1759 ,tx.attribute29
1760 ,tx.attribute30
1761 ,tx.attribute31
1762 ,tx.attribute32
1763 ,tx.attribute33
1764 ,tx.attribute34
1765 ,tx.attribute35
1766 ,tx.attribute36
1767 ,tx.attribute37
1768 ,tx.attribute38
1769 ,tx.attribute39
1770 ,tx.attribute40
1771 from ben_transaction tx
1772 ,ben_pil_elctbl_chc_popl popl
1773 where tx.transaction_type = 'CWBWSASG'
1774 and tx.attribute2 is not null
1775 and tx.transaction_id = popl.pil_elctbl_chc_popl_id;
1776
1777 delete ben_transaction
1778 where transaction_type = 'CWBWSASG';
1779
1780 commit_and_log('Pending WS Transaction Upgrade Complete');
1781
1782 open c_epe_rsgn_appr;
1783 loop
1784 fetch c_epe_rsgn_appr bulk collect into
1785 l_t_emp_pil_id
1786 ,l_t_txn_id
1787 limit g_commit_size;
1788
1789 if l_t_txn_id.count > 0 then
1790
1791 forall i in l_t_txn_id.first .. l_t_txn_id.last
1792 update ben_transaction tx
1793 set tx.attribute10 = to_char(l_t_emp_pil_id(i))
1794 where tx.transaction_id = l_t_txn_id(i)
1795 and tx.transaction_type = 'CWBEMPRSGN';
1796
1797 end if;
1798
1799 if c_epe_rsgn_appr%notfound then
1800 close c_epe_rsgn_appr;
1801 exit;
1802 end if;
1803
1804 end loop;
1805
1806 open c_pel_rsgn_appr;
1807 loop
1808 fetch c_pel_rsgn_appr bulk collect into
1809 l_t_emp_pil_id
1810 ,l_t_txn_id
1811 limit g_commit_size;
1812
1813 if l_t_txn_id.count > 0 then
1814
1815 forall i in l_t_txn_id.first .. l_t_txn_id.last
1816 update ben_transaction tx
1817 set tx.attribute10 = to_char(l_t_emp_pil_id(i))
1818 where tx.transaction_id = l_t_txn_id(i)
1819 and tx.transaction_type = 'CWBEMPRSGN';
1820
1821 end if;
1822
1823 if c_pel_rsgn_appr%notfound then
1824 close c_pel_rsgn_appr;
1828 end loop;
1825 exit;
1826 end if;
1827
1829
1830 open c_epe_rsgn_emp;
1831 loop
1832 fetch c_epe_rsgn_emp bulk collect into
1833 l_t_emp_pil_id
1834 ,l_t_curr_mgr_pil_id
1835 ,l_t_prop_mgr_pil_id
1836 ,l_t_grp_pl_id
1837 ,l_t_txn_id
1838 limit g_commit_size;
1839
1840 if l_t_txn_id.count > 0 then
1841
1842 forall i in l_t_txn_id.first .. l_t_txn_id.last
1843 update ben_transaction tx
1844 set tx.attribute3 = to_char(l_t_emp_pil_id(i))
1845 ,tx.attribute14 = to_char(l_t_prop_mgr_pil_id(i))
1846 ,tx.attribute16 = to_char(l_t_curr_mgr_pil_id(i))
1847 ,tx.attribute21 = to_char(l_t_grp_pl_id(i))
1848 where tx.transaction_id = l_t_txn_id(i)
1849 and tx.transaction_type = 'CWBEMPRSGN';
1850
1851 end if;
1852
1853 if c_epe_rsgn_emp%notfound then
1854 close c_epe_rsgn_emp;
1855 exit;
1856 end if;
1857
1858 end loop;
1859
1860 open c_pel_rsgn_emp;
1861 loop
1862 fetch c_pel_rsgn_emp bulk collect into
1863 l_t_emp_pil_id
1864 ,l_t_curr_mgr_pil_id
1865 ,l_t_prop_mgr_pil_id
1866 ,l_t_grp_pl_id
1867 ,l_t_txn_id
1868 limit g_commit_size;
1869
1870 if l_t_txn_id.count > 0 then
1871
1872 forall i in l_t_txn_id.first .. l_t_txn_id.last
1873 update ben_transaction tx
1874 set tx.attribute3 = to_char(l_t_emp_pil_id(i))
1875 ,tx.attribute14 = to_char(l_t_prop_mgr_pil_id(i))
1876 ,tx.attribute16 = to_char(l_t_curr_mgr_pil_id(i))
1877 ,tx.attribute21 = to_char(l_t_grp_pl_id(i))
1878 where tx.transaction_id = l_t_txn_id(i)
1879 and tx.transaction_type = 'CWBEMPRSGN';
1880
1881 end if;
1882
1883 if c_pel_rsgn_emp%notfound then
1884 close c_pel_rsgn_emp;
1885 exit;
1886 end if;
1887
1888 end loop;
1889
1890 commit_and_log('Reassignment Transaction Upgrade Complete');
1891
1892 exception
1893 when others then
1894 null;
1895
1896 end upgrade_temp_data;
1897
1898 procedure is_cwb_used(p_result out nocopy varchar2) is
1899
1900 cursor c_cwb_setup is
1901 select 'TRUE'
1902 from ben_ler_f
1903 where typ_cd = 'COMP';
1904
1905 begin
1906 p_result := 'FALSE';
1907
1908 open c_cwb_setup;
1909 fetch c_cwb_setup into p_result;
1910 close c_cwb_setup;
1911
1912 end is_cwb_used;
1913
1914
1915 procedure main(errbuf out nocopy varchar2
1916 ,retcode out nocopy number) is
1917
1918 l_cwb_used varchar2(30) := 'FALSE';
1919
1920 begin
1921 if hr_update_utility.isUpdateComplete
1922 (p_app_shortname => 'BEN'
1923 ,p_function_name => null
1924 ,p_business_group_id => null
1925 ,p_update_name => 'BENCWBMU') = 'TRUE' then
1926 return;
1927 end if;
1928
1929 hr_update_utility.setUpdateProcessing(p_update_name => 'BENCWBMU');
1930
1931 is_cwb_used(p_result => l_cwb_used);
1932
1933 if l_cwb_used = 'TRUE' then
1934
1935 commit_and_log('CWB Upgrade Started');
1936
1937 upgrade_plan_design();
1938 upgrade_transaction_data();
1939 upgrade_summary();
1940 upgrade_temp_data();
1941
1942 commit_and_log('CWB Upgrade Complete');
1943
1944 end if;
1945
1946 hr_update_utility.setUpdateComplete(p_update_name => 'BENCWBMU');
1947
1948
1949 end main;
1950
1951 end ben_cwb_data_model_upgrade;