[Home] [Help]
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
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
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
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
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)
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
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
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
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'
1529: select pil.per_in_ler_id
1530: ,tx.transaction_id
1531: from ben_transaction tx
1532: ,ben_elig_per_elctbl_chc epe
1533: ,ben_per_in_ler pil
1534: where tx.transaction_type = 'CWBEMPRSGN'
1535: and tx.attribute1 = 'APPR'
1536: and epe.elig_per_elctbl_chc_id = to_number(tx.attribute10)
1537: and epe.per_in_ler_id = pil.per_in_ler_id
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
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
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'
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)
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
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'
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)