[Home] [Help]
PACKAGE BODY: APPS.BEN_EXT_PURGE
Source
1 package body BEN_EXT_PURGE as
2 /* $Header: benxpurg.pkb 120.2 2007/05/01 22:02:46 tjesumic noship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 Name:
11 Extract Write Process.
12 Purpose:
13 This process delete the record as per the paramter provided
14 to a flat output file.
15 History:
16 Date Who Version What?
17 ---- --- ------- -----
18 26 Aug 98 tjesumic 115.0 Created.
19 18 Sep 98 tjesumic 115.1 log purge added.
20 18 Sep 98 tjesumic 115.2 log purge added.
21 16 Feb 06 tjesumic 115.6 the system allows to delete the extract dfn though the result exist
22 the resull without defintion can be deleted now
23 01-May-07 tjesumic 115.7 commit added for every result and every 1000 log for performance
24 */
25 -----------------------------------------------------------------------------------
26 --
27 g_package varchar2(30) := ' ben_ext_purge.';
28
29
30
31 Procedure MAIN
32 (errbuf out nocopy varchar2, --needed by concurrent manager.
33 retcode out nocopy number, --needed by concurrent manager.
34 p_validate in varchar2 ,
35 p_ext_dfn_id in number default null ,
36 p_ext_rslt_date in varchar2,
37 p_business_group_id in number ,
38 p_benefit_action_id in number default null,
39 p_ext_rslt_id in number default null ) is
40 --
41 --
42 --
43 l_effective_date date ;
44
45 cursor c_xrs (p_date date ) is
46 select xrs.ext_rslt_id,xrs.ext_dfn_id ,
47 xrs.object_version_number,
48 xrs.eff_dt
49 from ben_ext_rslt xrs
50 where ( xrs.ext_dfn_id = p_ext_dfn_id
51 or p_ext_dfn_id is null )
52 and ( xrs.ext_rslt_id = p_ext_rslt_id
53 or p_ext_rslt_id is null )
54 and xrs.eff_dt <= p_date
55 and xrs.business_group_id = p_business_group_id ;
56
57 cursor c_xrd (p_ext_rslt_id number)is
58 select ext_rslt_dtl_id,
59 object_version_number
60 from ben_ext_rslt_dtl xrd
61 where xrd.ext_rslt_id = p_ext_rslt_id ;
62
63
64 cursor c_xre (p_ext_rslt_id number)is
65 select ext_rslt_err_id,
66 object_version_number
67 from ben_Ext_rslt_err xre
68 where xre.ext_rslt_id = p_ext_rslt_id ;
69
70
71 cursor c_Xdf (p_ext_dfn_id number ) is
72 select a.name
73 from ben_ext_dfn a
74 where a.ext_dfn_id = p_ext_dfn_id ;
75
76 --
77 l_proc varchar2(72) := g_package||'main';
78 l_object_version_number number ;
79 l_rcd_count number := 0 ;
80 l_file_count number := 0;
81 l_name ben_Ext_dfn.name%type ;
82 --
83 begin
84 --
85 hr_Utility.set_location('Entering'||l_proc, 5);
86 l_effective_date := to_date(p_ext_rslt_date, 'YYYY/MM/DD HH24:MI:SS');
87 l_effective_date := to_date(to_char(trunc(l_effective_date), 'DD/MM/RRRR'),
88 'DD/MM/RRRR');
89
90 for l_xrs in c_xrs(l_effective_date)
91 Loop
92
93 open c_xdf(l_xrs.ext_dfn_id) ;
94 fetch c_xdf into l_name ;
95 close c_Xdf ;
96
97
98 hr_Utility.set_location('processing '||l_name, 5);
99 --- Deleting Details
100 l_rcd_count := 0 ;
101 for l_xrd in c_xrd(l_xrs.ext_rslt_id)
102 Loop
103 hr_Utility.set_location('detail '||l_name || ' ' || l_rcd_count, 5);
104 l_object_version_number := l_xrd.object_version_number ;
105 ben_EXT_RSLT_DTL_api. delete_EXT_RSLT_DTL
106 (p_ext_rslt_dtl_id => l_xrd.ext_rslt_dtl_id
107 ,p_object_version_number => l_object_version_number
108 ) ;
109 l_rcd_count := l_rcd_count + 1 ;
110
111 End loop ;
112
113 -- Deleting Error detail for the resultr
114 for l_xre in c_xre(l_xrs.ext_rslt_id)
115 Loop
116 hr_Utility.set_location('error '||l_name , 5);
117 l_object_version_number := l_xre.object_version_number ;
118 ben_EXT_RSLT_ERR_api. delete_EXT_RSLT_ERR
119 (p_ext_rslt_err_id => l_xre.ext_rslt_err_id
120 ,p_object_version_number => l_object_version_number
121 ,p_effective_date => l_xrs.eff_dt
122 ) ;
123
124 End loop ;
125
126
127 -- Deleting Result
128 l_object_version_number := l_xrs.object_version_number ;
129 ben_EXT_RSLT_api.delete_EXT_RSLT
130 (p_ext_rslt_id => l_xrs.ext_rslt_id
131 ,p_object_version_number => l_object_version_number
132 ,p_effective_date => l_xrs.eff_dt
133 ) ;
134 l_file_count := l_file_count + 1 ;
135 fnd_file.put_line(fnd_file.log, rpad(l_name, 40)||' '||to_char(l_xrs.eff_dt)||' '
136 || lpad(to_char(l_rcd_count),7) ) ;
137
138 --- for performance commit every single extrct result when the mode is not rollbak
139
140 if not ben_populate_rbv.validate_mode
141 (p_validate => p_validate)
142 then
143 --
144 hr_utility.set_location('commit '||l_xrs.ext_rslt_id, 15);
145 commit ;
146
147 --
148 end if;
149
150 end Loop ;
151
152 if ben_populate_rbv.validate_mode
153 (p_validate => p_validate
154 )
155 then
156 --
157 hr_utility.set_location('rollback '||l_proc, 15);
158 rollback;
159 --
160 end if;
161
162 -- write to logfile a successful completion message
163 fnd_message.set_name('BEN','BEN_91877_GENERAL_JOB_SUCCESS');
164 fnd_file.put_line(fnd_file.log, fnd_message.get);
165
166
167 -- write to logfile the record count
168 fnd_file.put_line(fnd_file.log, to_char(l_file_count) ||' '||'Results are purged') ;
169
170 commit;
171 hr_utility.set_location('Exiting'||l_proc, 15);
172 --
173 --
174 EXCEPTION
175 --
176
177 WHEN others THEN
178 fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
179 fnd_message.set_token('2',substr(sqlerrm,1,200));
180 fnd_file.put_line(fnd_file.log, fnd_message.get);
181 fnd_message.raise_error;
182 --
183 END main;
184 --
185
186
187 Procedure chg_log_purge
188 (errbuf out nocopy varchar2, --needed by concurrent manager.
189 retcode out nocopy number, --needed by concurrent manager.
190 p_validate in varchar2 ,
191 p_person_id in number default null ,
192 p_effective_date in varchar2 default null,
193 p_actual_date in varchar2 default null,
194 p_business_group_id in number ,
195 p_benefit_action_id in number default null
196 ) is
197
198 cursor c_chg_log (p_eff_date date ,
199 p_act_date date ,
200 p_person_id number ) is
201 select cel.person_id,
202 cel.object_version_number,
203 cel.ext_chg_evt_log_id ,
204 cel.chg_eff_dt
205 from ben_ext_chg_evt_log cel
206 where (cel.person_id = p_person_id
207 or p_person_id is null )
208 and (trunc(cel.chg_eff_dt) <= p_eff_date
209 or p_eff_date is null )
210 and (trunc(cel.chg_actl_dt) <= p_act_date
211 or p_act_date is null )
212 and cel.business_group_id = p_business_group_id
213 order by cel.person_id ;
214
215 cursor c_name(p_person_id number) is
216 select full_name
217 from per_all_people_f
218 where person_id = p_person_id ;
219
220
221
222 --
223 l_proc varchar2(72) := g_package||'chg_log_purge';
224 l_object_version_number number ;
225 l_log_count number := 0 ;
226 l_person_id number ;
227 l_person_count number := 0;
228 l_prv_person_id number := -1 ;
229 l_ext_chg_evt_log_id ben_ext_chg_evt_log.ext_chg_evt_log_id%type ;
230 l_effective_date date ;
231 l_eff_date date ;
232 l_actual_date date ;
233 l_person_name per_all_people_f.full_name%type ;
234 --
235 begin
236 --
237 hr_Utility.set_location('Entering'||l_proc, 5);
238
239 if p_effective_date is null and p_actual_date is null then
240 fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
241 fnd_message.set_token('2' , 'Either Effective date or Actual date must be entered' );
242 fnd_file.put_line(fnd_file.log,'Either Effective date or Actual date must be entered');
243 fnd_message.raise_error;
244
245 end if ;
246
247 l_effective_date := to_date(p_effective_date, 'YYYY/MM/DD HH24:MI:SS');
248 l_effective_date := to_date(to_char(trunc(l_effective_date), 'DD/MM/RRRR'),
249 'DD/MM/RRRR');
250
251 l_actual_date := to_date(p_actual_date, 'YYYY/MM/DD HH24:MI:SS');
252 l_actual_date := to_date(to_char(trunc(l_actual_date), 'DD/MM/RRRR'),
253 'DD/MM/RRRR');
254
255 open c_chg_log (l_effective_date,l_actual_date,p_person_id ) ;
256 Loop
257 fetch c_chg_log into l_person_id,
258 l_object_version_number,
259 l_ext_chg_evt_log_id ,
260 l_eff_date
261 ;
262 exit when c_chg_log%notfound ;
263 --- count the person when the person_id changex
264 if l_person_id <> l_prv_person_id then
265 -- first time dont print , whne the second porson is in the loop
266 -- first time , print for the first person
267 if l_person_name is not null then
268 -- write to logfile the record count
269 fnd_file.put_line(fnd_file.log, rpad(l_person_name,45) ||' '||lpad(l_person_count,6)) ;
270 hr_utility.set_location(rpad(l_person_name,45) ||' '||lpad(l_person_count,6),99 ) ;
271 end if ;
272 l_person_count := 0 ;
273 l_prv_person_id := l_person_id ;
274 open c_name (l_person_id) ;
275 fetch c_name into l_person_name ;
276 close c_name ;
277 end if;
278
279
280 ben_EXT_CHG_EVT_api.delete_EXT_CHG_EVT
281 (p_ext_chg_evt_log_id => l_ext_chg_evt_log_id
282 ,p_object_version_number => l_object_version_number
283 ,p_effective_date => l_eff_date
284 ) ;
285 l_person_count := l_person_count + 1 ;
286 l_log_count := l_log_count + 1 ;
287
288 if mod(l_log_count,1000) = 0 then
289 if not ben_populate_rbv.validate_mode
290 (p_validate => p_validate)
291 then
292 commit;
293 end if ;
294 end if;
295
296
297 end loop ;
298
299 if (l_person_count is not null) then
300
301 --Bug 4080783 : Display person name even when no. of records purged is 0
302 -- provided person name exists.
303 open c_name (p_person_id) ;
304 fetch c_name into l_person_name ;
305 close c_name ;
306
307 if l_person_name is not null then
308 fnd_file.put_line(fnd_file.log, rpad(l_person_name,45) ||' '||lpad(l_person_count,6)) ;
309 end if;
310 hr_utility.set_location(rpad(l_person_name,45) ||' '||lpad(l_person_count,6),99 ) ;
311 fnd_file.put_line(fnd_file.log, rpad(' ',45) ||' '||'------') ;
312 fnd_file.put_line(fnd_file.log, rpad(' ',45) ||' '||lpad(l_log_count,6)) ;
313 hr_utility.set_location(lpad(l_log_count,6),99 ) ;
314 end if ;
315 close c_chg_log ;
316
317
318 if ben_populate_rbv.validate_mode
319 (p_validate => p_validate
320 )
321 then
322 --
323 hr_utility.set_location('rollback '||l_proc, 15);
324 rollback;
325 --
326 end if;
327
328 -- write to logfile a successful completion message
329 fnd_message.set_name('BEN','BEN_91877_GENERAL_JOB_SUCCESS');
330 fnd_file.put_line(fnd_file.log, fnd_message.get);
331 commit;
332 hr_utility.set_location('Exiting'||l_proc, 15);
333 --
334 --
335 EXCEPTION
336 --
337 WHEN others THEN
338 fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
339 fnd_message.set_token('2',substr(sqlerrm,1,200));
340 fnd_file.put_line(fnd_file.log, fnd_message.get);
341 fnd_message.raise_error;
342
343 End chg_log_purge ;
344
345
346
347 END; --package