[Home] [Help]
756: --
757: procedure update_person_info(p_group_pl_id in number
758: ,p_lf_evt_ocrd_dt in date) is
759: -- cursor to fetch the person_id from ben_per_in_ler for
760: -- group_per_in_ler_ids with -1 as person_id in ben_cwb_person_info
761: cursor csr_person_ids is
762: select pi.group_per_in_ler_id, pil.person_id
763: from ben_cwb_person_info pi
764: ,ben_per_in_ler pil
759: -- cursor to fetch the person_id from ben_per_in_ler for
760: -- group_per_in_ler_ids with -1 as person_id in ben_cwb_person_info
761: cursor csr_person_ids is
762: select pi.group_per_in_ler_id, pil.person_id
763: from ben_cwb_person_info pi
764: ,ben_per_in_ler pil
765: where pi.person_id = -1
766: and pi.group_pl_id = p_group_pl_id
767: and pi.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
768: and pi.group_per_in_ler_id = pil.per_in_ler_id
769: and pil.per_in_ler_stat_cd in ('PROCD','STRTD');
770: --
771: type group_per_in_ler_id_type is table of
772: ben_cwb_person_info.group_per_in_ler_id%type;
773: type person_id_type is table of
774: ben_per_in_ler.person_id%type;
775: -- declare pl/sql tables
776: l_group_per_in_ler_id_tab group_per_in_ler_id_type;
787: ben_manage_cwb_life_events.g_error_log_rec.calling_proc :=
788: 'update_person_info';
789: ben_manage_cwb_life_events.g_error_log_rec.step_number := 77;
790: --
791: -- Set the person id in ben_cwb_person_info to correct values from -1.
792: open csr_person_ids;
793: fetch csr_person_ids bulk collect into l_group_per_in_ler_id_tab
794: ,l_person_id_tab;
795: close csr_person_ids;
796:
797: if nvl(l_group_per_in_ler_id_tab.count,0) > 0 then
798: forall i in l_group_per_in_ler_id_tab.first..
799: l_group_per_in_ler_id_tab.last
800: update ben_cwb_person_info
801: set person_id = l_person_id_tab(i)
802: where group_per_in_ler_id = l_group_per_in_ler_id_tab(i);
803:
804: --
847: ,sum(grp.ws_bdgt_iss_val * summ.elig_sal_val_direct / 100)
848: ws_bdgt_iss_val_direct
849: from ben_cwb_group_hrchy hrchy
850: ,ben_cwb_person_groups grp
851: ,ben_cwb_person_info info
852: ,ben_cwb_summary summ
853: where info.group_pl_id = p_group_pl_id
854: and info.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
855: and info.group_per_in_ler_id = hrchy.emp_per_in_ler_id
873: ,sum(decode(nvl(grp.dist_bdgt_val,0),0, grp.ws_bdgt_iss_val,
874: grp.dist_bdgt_iss_val)) bdgt_iss_val_direct
875: from ben_cwb_group_hrchy hrchy
876: ,ben_cwb_person_groups grp
877: ,ben_cwb_person_info info
878: where info.group_pl_id = p_group_pl_id
879: and info.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
880: and info.group_per_in_ler_id = hrchy.emp_per_in_ler_id
881: and hrchy.lvl_num = 1
903: ,sum(misc2_val_direct) misc2_val_all
904: ,sum(misc3_val_direct) misc3_val_all
905: from ben_cwb_group_hrchy hrchy
906: ,ben_cwb_summary summ
907: ,ben_cwb_person_info info
908: where info.group_pl_id = p_group_pl_id
909: and info.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
910: and info.group_per_in_ler_id = hrchy.emp_per_in_ler_id
911: and hrchy.lvl_num >=0
928: bdgt_iss_val_direct
929: from ben_cwb_group_hrchy hrchy
930: ,ben_cwb_person_groups grp
931: ,ben_cwb_summary summ
932: ,ben_cwb_person_info info
933: where info.group_pl_id = p_group_pl_id
934: and info.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
935: and info.group_per_in_ler_id = hrchy.emp_per_in_ler_id
936: and hrchy.lvl_num = 1
985: ben_cwb_summary.misc2_val_all%type;
986: type misc3_val_all_type is table of
987: ben_cwb_summary.misc3_val_all%type;
988: type person_id_type is table of
989: ben_cwb_person_info.person_id%type;
990: --
991: -- declare pl/sql tables
992: l_group_per_in_ler_id_tab group_per_in_ler_id_type;
993: l_group_pl_id_tab group_pl_id_type;
1502: -- and _all information.
1503: compute_bdgts_and_all(p_group_pl_id
1504: ,p_lf_evt_ocrd_dt);
1505: end if;
1506: -- Now the summary is populated. Set the person id in ben_cwb_person_info
1507: -- to correct values from -1.
1508: update_person_info(p_group_pl_id,p_lf_evt_ocrd_dt);
1509: --
1510: delete_refresh_job_marker(p_group_pl_id => p_group_pl_id
1828: -- cursor of employees which were processed in the current run
1829: cursor csr_emps is
1830: select per.group_per_in_ler_id
1831: ,hrchy.mgr_per_in_ler_id
1832: from ben_cwb_person_info per
1833: ,ben_cwb_group_hrchy hrchy
1834: where per.person_id = -1
1835: and per.group_pl_id = p_group_pl_id
1836: and per.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
1844: ,p_group_pl_id group_pl_id
1845: ,grp.group_oipl_id
1846: ,pil.person_id
1847: from ben_cwb_group_hrchy hrchy
1848: ,ben_cwb_person_info per
1849: ,ben_cwb_person_groups grp
1850: ,ben_per_in_ler pil
1851: where per.person_id = -1
1852: and per.group_per_in_ler_id = hrchy.emp_per_in_ler_id
1935: select h.mgr_per_in_ler_id
1936: ,h.lvl_num
1937: ,i.person_id
1938: from ben_cwb_group_hrchy h
1939: ,ben_cwb_person_info i
1940: where h.emp_per_in_ler_id = v_per_in_ler_id
1941: and h.lvl_num > 0
1942: and h.mgr_per_in_ler_id = i.group_per_in_ler_id
1943: order by h.lvl_num;
1959: -- person_id as -1 is less. If records are more, call the procedure
1960: -- refresh_summary_group_pl
1961:
1962: select count(per.group_per_in_ler_id) into l_count
1963: from ben_cwb_person_info per
1964: where per.person_id = -1
1965: and per.group_pl_id = p_group_pl_id
1966: and per.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
1967:
2185: --
2186: save_pl_sql_tab;
2187: --
2188: --
2189: -- Now the summary is populated. Set the person id in ben_cwb_person_info
2190: -- to correct values from -1.
2191: update_person_info(p_group_pl_id,p_lf_evt_ocrd_dt);
2192: --
2193: if g_debug then
3859: cursor csr_plans_to_refresh
3860: is
3861: select distinct group_pl_id
3862: ,lf_evt_ocrd_dt
3863: from ben_cwb_person_info cpi
3864: where cpi.person_id = -1;
3865: --
3866: --
3867: begin