1 PACKAGE BODY ben_dm_delete AS
2 /* $Header: benfdmpmdel.pkb 120.1 2006/06/13 15:23:29 nkkrishn noship $ */
3
4 procedure main(
5 errbuf out nocopy varchar2,
6 retcode out nocopy number ,
7 p_migration_id in number ,
8 p_concurrent_process in varchar2 default 'Y',
9 p_last_migration_date in varchar2,
10 p_process_number in number ,
11 p_dir_name in varchar2,
12 p_file_name in varchar2,
13 p_delimiter in varchar2,
14 p_business_group_id in number default null) is
15
16 l_no_of_threads number;
17 l_person_id number;
18
19 cursor c1 is
20 select distinct per.person_id, dif.group_order
21 from ben_dm_phase_items itm,
22 ben_dm_phases phs,
23 ben_dm_input_file dif,
24 per_all_people_f per,
25 per_business_groups bg
26 where phs.migration_id = p_migration_id
27 and phs.phase_id = itm.phase_id
28 and phs.phase_name = 'UP' --use UP phase for now
29 and mod(itm.phase_item_id,l_no_of_threads) + 1 = p_process_number
30 --added check for person records only
31 and dif.person_type = 'P'
32 and dif.group_order = itm.group_order
33 and per.national_identifier = nvl(dif.target_national_identifier,dif.source_national_identifier)
34 and bg.name = dif.target_business_group_name
35 and per.business_group_id = bg.business_group_id;
36
37 --New Cursor to identify dependent records to be deleted for each person
38 --being migrated
39 cursor c2(p_group_order varchar2
40 ,p_person_id number) is
41 select distinct per.person_id
42 from ben_dm_phase_items itm,
43 ben_dm_phases phs,
44 ben_dm_input_file dif,
45 per_all_people_f per,
46 per_business_groups bg,
47 per_contact_relationships pcr
48 where phs.migration_id = p_migration_id
49 and phs.phase_id = itm.phase_id
50 and phs.phase_name = 'UP' --use UP phase for now
51 and mod(itm.phase_item_id,l_no_of_threads) + 1 = p_process_number
52 and dif.person_type = 'D'
53 and dif.group_order = p_group_order
54 and dif.group_order = itm.group_order
55 and per.national_identifier = nvl(dif.target_national_identifier,dif.source_national_identifier)
56 and per.person_id = pcr.contact_person_id
57 and pcr.person_id = p_person_id
58 and bg.name = dif.target_business_group_name
59 and per.business_group_id = bg.business_group_id;
60
61 Type personRec is Record
62 (person_id number,
63 group_order number);
64 Type personTab is Table of personRec index by binary_integer;
65
66 l_person_rec personTab;
67
68 begin
69
70 l_no_of_threads := ben_dm_utility.number_of_threads(p_business_group_id);
71 if l_no_of_threads is null then
72 l_no_of_threads := 3 ;
73 end if ;
74 open c1;
75 fetch c1 bulk collect into l_person_rec;
76 close c1;
77 if l_person_rec.count > 0 then
78 --Delete the person and dependent records from the target business group
79 for i in 1..l_person_rec.count
80 loop
81 --Delete all dependents of the person
82 open c2(l_person_rec(i).group_order, l_person_rec(i).person_id);
83 loop
84 fetch c2 into l_person_id;
85 if c2%notfound then
86 exit;
87 end if;
88 delete_person(l_person_id);
89 commit;
90 end loop;
91 --Delete the person record
92 delete_person(p_person_id => l_person_rec(i).person_id);
93 close c2;
94 end loop;
95 commit;
96 end if;
97 end main;
98
99 procedure delete_person(p_person_id number) is
100 begin
101
102 hr_general.g_data_migrator_mode := 'Y';
103
104 delete from ben_le_clsn_n_rstr
105 where per_in_ler_id in (select per_in_ler_id from ben_per_in_ler
106 where person_id =p_person_id);
107
108 delete from ben_per_bnfts_bal_f where person_id = p_person_id;
109
110 delete from ben_cbr_per_in_ler cpl where cpl.cbr_quald_bnf_id in
111 (select cbf.cbr_quald_bnf_id from ben_cbr_quald_bnf cbf where cbf.quald_bnf_person_id = p_person_id);
112
113 delete from ben_cbr_quald_bnf where quald_bnf_person_id = p_person_id;
114
115 delete from ben_prmry_care_prvdr_f pcp
116 where pcp.prtt_enrt_rslt_id in
117 (select pen.prtt_enrt_rslt_id
118 from ben_prtt_enrt_rslt_f pen
119 where pen.person_id = p_person_id);
120
121 delete from ben_prmry_care_prvdr_f pcp
122 where pcp.elig_cvrd_dpnt_id in
123 (select egd.elig_cvrd_dpnt_id
124 from ben_elig_cvrd_dpnt_f egd,
125 ben_prtt_enrt_rslt_f pen
126 where pen.person_id = p_person_id
127 and egd.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id);
128
129 delete from ben_enrt_rt
130 where elig_per_elctbl_chc_id in
131 (select elig_per_elctbl_chc_id from ben_elig_per_elctbl_chc
132 where per_in_ler_id in (select per_in_ler_id from ben_per_in_ler
133 where person_id =p_person_id));
134
135 delete from ben_enrt_prem
136 where elig_per_elctbl_chc_id in
137 (select elig_per_elctbl_chc_id from ben_elig_per_elctbl_chc
138 where per_in_ler_id in (select per_in_ler_id from ben_per_in_ler
139 where person_id =p_person_id));
140
141 delete from ben_enrt_rt
142 where enrt_bnft_id in
143 (select enrt_bnft_id from ben_enrt_bnft
144 where elig_per_elctbl_chc_id in
145 (select elig_per_elctbl_chc_id from ben_elig_per_elctbl_chc
146 where per_in_ler_id in (select per_in_ler_id from ben_per_in_ler
147 where person_id =p_person_id)));
148
149 delete from BEN_ELCTBL_CHC_CTFN
150 where elig_per_elctbl_chc_id in
151 (select elig_per_elctbl_chc_id from ben_elig_per_elctbl_chc
152 where per_in_ler_id in (select per_in_ler_id from ben_per_in_ler
153 where person_id =p_person_id));
154
155 delete from BEN_ELCTBL_CHC_CTFN
156 where enrt_bnft_id in
157 (select enrt_bnft_id from ben_enrt_bnft
158 where elig_per_elctbl_chc_id in
159 (select elig_per_elctbl_chc_id from ben_elig_per_elctbl_chc
160 where per_in_ler_id in (select per_in_ler_id from ben_per_in_ler
161 where person_id =p_person_id)));
162
163 delete from ben_enrt_bnft
164 where elig_per_elctbl_chc_id in
165 (select elig_per_elctbl_chc_id from ben_elig_per_elctbl_chc
166 where per_in_ler_id in (select per_in_ler_id from ben_per_in_ler
167 where person_id =p_person_id));
168
169 delete from ben_elig_per_elctbl_chc
170 where per_in_ler_id in (select per_in_ler_id from ben_per_in_ler
171 where person_id =p_person_id);
172
173 delete from ben_pil_elctbl_chc_popl
174 where per_in_ler_id in (select per_in_ler_id from ben_per_in_ler
175 where person_id =p_person_id);
176
177 delete from PAY_ELEMENT_ENTRY_VALUES_F
178 where element_entry_id
179 in (select element_entry_id
180 from pay_element_entries_f
181 where assignment_id in
182 (select assignment_id
183 from per_all_assignments_f
184 where person_id =p_person_id));
185
186 delete from PAY_ELEMENT_ENTRIES_F
187 where assignment_id in
188 (select assignment_id
189 from per_all_assignments_f
190 where person_id =p_person_id);
191
192 delete from ben_prtt_prem_f
193 where prtt_enrt_rslt_id in
194 (select prtt_enrt_rslt_id from ben_prtt_enrt_rslt_f where person_id =p_person_id);
195 delete from ben_prtt_rt_val where prtt_enrt_rslt_id in
196 (select prtt_enrt_rslt_id from ben_prtt_enrt_rslt_f where person_id =p_person_id);
197
198 delete from ben_bnft_prvdd_ldgr_f where prtt_enrt_rslt_id in
199 (select prtt_enrt_rslt_id from ben_prtt_enrt_rslt_f where person_id =p_person_id);
200
201 delete from ben_prtt_enrt_actn_f where prtt_enrt_rslt_id in
202 (select prtt_enrt_rslt_id from ben_prtt_enrt_rslt_f where person_id =p_person_id);
203
204 delete from ben_prtt_enrt_actn_f where elig_cvrd_dpnt_id in
205 (select elig_cvrd_dpnt_id from ben_elig_cvrd_dpnt_f where prtt_enrt_rslt_id in
206 (select prtt_enrt_rslt_id from ben_prtt_enrt_rslt_f where person_id =p_person_id));
207
208 delete from ben_prtt_enrt_actn_f where pl_bnf_id in
209 (select pl_bnf_id from ben_pl_bnf_f where prtt_enrt_rslt_id in
210 (select prtt_enrt_rslt_id from ben_prtt_enrt_rslt_f where person_id =p_person_id));
211
212 delete from ben_prtt_enrt_ctfn_prvdd_f where prtt_enrt_rslt_id in
213 (select prtt_enrt_rslt_id from ben_prtt_enrt_rslt_f where person_id =p_person_id);
214
215 delete from ben_cvrd_dpnt_ctfn_prvdd_f where elig_cvrd_dpnt_id in
216 (select elig_cvrd_dpnt_id from ben_elig_cvrd_dpnt_f where prtt_enrt_rslt_id in
217 (select prtt_enrt_rslt_id from ben_prtt_enrt_rslt_f where person_id =p_person_id));
218
219 delete from ben_elig_cvrd_dpnt_f where prtt_enrt_rslt_id in
220 (select prtt_enrt_rslt_id from ben_prtt_enrt_rslt_f where person_id =p_person_id);
221
222 delete from ben_elig_dpnt where per_in_ler_id in
223 (select per_in_ler_id from ben_per_in_ler where person_id =p_person_id);
224
225 delete from ben_pl_bnf_ctfn_prvdd_f where pl_bnf_id in
226 (select pl_bnf_id from ben_pl_bnf_f where prtt_enrt_rslt_id in
227 (select prtt_enrt_rslt_id from ben_prtt_enrt_rslt_f where person_id =p_person_id));
228
229 delete from ben_pl_bnf_f where prtt_enrt_rslt_id in
230 (select prtt_enrt_rslt_id from ben_prtt_enrt_rslt_f where person_id =p_person_id);
231
232 delete from ben_prtt_enrt_rslt_f where person_id =p_person_id;
233
234 delete from ben_per_cm_prvdd_f where per_cm_id in
235 (select per_cm_id from ben_per_cm_f where person_id =p_person_id);
236
237 delete from ben_per_cm_trgr_f where per_cm_id in
238 (select per_cm_id from ben_per_cm_f where person_id =p_person_id);
239
240 delete from ben_per_cm_usg_f where per_cm_id in
241 (select per_cm_id from ben_per_cm_f where person_id =p_person_id);
242
243 delete from ben_per_cm_f where person_id =p_person_id;
244
245 delete from ben_per_in_ler where person_id =p_person_id;
246
247 delete from ben_ptnl_ler_for_per where person_id =p_person_id;
248
249 delete from ben_elig_per_opt_f where elig_per_id in
250 (select elig_per_id from ben_elig_per_f where person_id =p_person_id);
251
252 delete from ben_elig_per_f where person_id =p_person_id;
253
254 -- delete from extrat
255 delete from ben_ext_rslt_dtl where person_id=p_person_id;
256 delete from ben_ext_rslt_err where person_id=p_person_id;
257 delete from ben_ext_chg_evt_log where person_id=p_person_id;
258 --
259
260 delete from per_addresses where person_id=p_person_id;
261 delete from per_assignment_extra_info where assignment_id in
262 (select assignment_id from per_all_assignments_f where person_id=p_person_id);
263
264
265 delete from per_all_assignments_f where person_id=p_person_id;
266 delete from per_person_type_usages_f where person_id=p_person_id;
267 delete from per_periods_of_service where person_id=p_person_id;
268 delete from per_contact_relationships where person_id=p_person_id;
269 delete from per_absence_attendances where person_id=p_person_id;
270 delete from per_all_people_f where person_id=p_person_id;
271
272 hr_general.g_data_migrator_mode := 'N';
273
274 exception
275 when others then
276 rollback;
277 raise;
278 end delete_person;
279
280 end ben_dm_delete;
[Home] [Help]