[Home] [Help]
PACKAGE BODY: APPS.BEN_BENMNGLE_PURGE
Source
1 package body ben_benmngle_purge as
2 /* $Header: benpurge.pkb 120.0.12000000.4 2007/06/25 10:12:05 nhunur noship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 Name
12 Purge BENMNGLE related tables
13 Purpose
14 This package is used to purge BENMNGLE related data from tables.
15 History
16 Date Who Version What?
17 ---- --- ------- -----
18 10-AUG-98 GPERRY 110.0 Created.
19 06-JAN-99 GPERRY 115.2 Corrected to use concurrent
20 request id.
21 24-FEB-99 GPERRY 115.3 Fixed dates for canonical
22 22-MAR-99 TMATHERS 115.5 Changed -MON- to /MM/
23 20-JUL-99 Gperry 115.6 genutils -> benutils package
24 rename.
25 04-APR-00 mmogel 115.7 Added tokens to messages to make
26 them more meaningful to the user
27 28-APR-00 gperry 115.8 Converted API calls to base
28 tables for performance.
29 18-SEP-02 hmani 115.9 Bug# 2573240 modified
30 delete_reporting_rows procedure.
31 26-DEC-02 rpillay 115.11 NOCOPY changes
32 02-Aug-04 nhunur 115.12 3805304 - Added code to handle null request_id
33 rows in ben_benefit_actions.
34 03-Dec-04 ikasire 115.13 Bug 4046914
35 28-Dec-07 nhunur 115.14 Bug 6075014 - perf changes
36 */
37 --------------------------------------------------------------------------------
38 --
39 g_package varchar2(80) := 'ben_benmngle_purge';
40 --
41 procedure write_params(p_concurrent_request_id in number,
42 p_business_group_id in number,
43 p_effective_date in date) is
44 --
45 l_package varchar2(80) := g_package||'.write_params';
46 --
47 begin
48 --
49 hr_utility.set_location ('Entering '||l_package,10);
50 --
51 fnd_file.put_line
52 (which => fnd_file.log,
53 buff => 'Runtime Parameters');
54 --
55 fnd_file.put_line
56 (which => fnd_file.log,
57 buff => benutils.g_banner_minus);
58 --
59 fnd_file.put_line
60 (which => fnd_file.log,
61 buff => 'Concurrent Request ID : '||p_concurrent_request_id);
62 --
63 fnd_file.put_line
64 (which => fnd_file.log,
65 buff => 'Business Group ID : '||p_business_group_id);
66 --
67 fnd_file.put_line
68 (which => fnd_file.log,
69 buff => 'Effective Date : '||to_char(p_effective_date,'DD/MM/YYYY'));
70 --
71 hr_utility.set_location ('Leaving '||l_package,10);
72 --
73 exception
74 --
75 when others then
76 --
77 fnd_message.set_name('BEN','BEN_91663_BENMNGLE_LOGGING');
78 fnd_message.set_token('PROC',l_package);
79 fnd_message.raise_error;
80 --
81 end write_params;
82 --
83 procedure write_logfile(p_benefit_action_id in number,
84 p_benefit_action_rows in number,
85 p_batch_range_rows in number,
86 p_person_action_rows in number,
87 p_reporting_rows in number,
88 p_dpnt_rows in number,
89 p_elctbl_chc_rows in number,
90 p_elig_rows in number,
91 p_proc_rows in number,
92 p_rate_rows in number,
93 p_ler_rows in number) is
94 --
95 l_package varchar2(80) := g_package||'.write_logfile';
96 --
97 begin
98 --
99 hr_utility.set_location ('Entering '||l_package,10);
100 --
101 fnd_file.put_line
102 (which => fnd_file.log,
103 buff => benutils.g_banner_minus);
104 --
105 fnd_file.put_line
106 (which => fnd_file.log,
107 buff => 'Benefit Action ID Deleted = '||p_benefit_action_id);
108 --
112 --
109 fnd_file.put_line
110 (which => fnd_file.log,
111 buff => 'Benefit Action Rows Deleted = '||p_benefit_action_rows);
113 fnd_file.put_line
114 (which => fnd_file.log,
115 buff => 'Batch Range Rows Deleted = '||p_batch_range_rows);
116 --
117 fnd_file.put_line
118 (which => fnd_file.log,
119 buff => 'Person Action Rows Deleted = '||p_person_action_rows);
120 --
121 fnd_file.put_line
122 (which => fnd_file.log,
123 buff => 'Dependent Information Rows Deleted = '||p_dpnt_rows);
124 --
125 fnd_file.put_line
126 (which => fnd_file.log,
127 buff => 'Electable Choice Information Rows Deleted = '||p_elctbl_chc_rows);
128 --
129 fnd_file.put_line
130 (which => fnd_file.log,
131 buff => 'Eligibility Rows Deleted = '||p_elig_rows);
132 --
133 fnd_file.put_line
134 (which => fnd_file.log,
135 buff => 'Process Information Rows Deleted = '||p_proc_rows);
136 --
137 fnd_file.put_line
138 (which => fnd_file.log,
139 buff => 'Rate Information Rows Deleted = '||p_rate_rows);
140 --
141 fnd_file.put_line
142 (which => fnd_file.log,
143 buff => 'Life Event Information Rows Deleted = '||p_ler_rows);
144 --
145 fnd_file.put_line
146 (which => fnd_file.log,
147 buff => 'Reporting Rows Deleted = '||p_reporting_rows);
148 --
149 fnd_file.put_line
150 (which => fnd_file.log,
151 buff => benutils.g_banner_minus);
152 --
153 hr_utility.set_location ('Leaving '||l_package,10);
154 --
155 exception
156 --
157 when others then
158 --
159 fnd_message.set_name('BEN','BEN_91663_BENMNGLE_LOGGING');
160 fnd_message.set_token('PROC',l_package);
161 fnd_message.raise_error;
162 --
163 end write_logfile;
164 --
165 procedure delete_reporting_rows(p_benefit_action_id in number,
166 p_rows out nocopy number) is
167 --
168 l_package varchar2(80) := g_package||'.delete_reporting_rows';
169 l_records_to_be_deleted number := 5000; /* Deleting 5000 records at a time */
170
171 -- Procedure slightly modified for Bug# 2573240 to delete 5000
172 -- records at a time
173 --
174 begin
175 --
176 hr_utility.set_location ('Entering '||l_package,10);
177 --
178 p_rows :=0;
179 loop
180 delete from ben_reporting
181 where benefit_action_id = p_benefit_action_id
182 and rownum <=l_records_to_be_deleted;
183 --
184 p_rows := p_rows + sql%rowcount;
185 --
186 exit when sql%rowcount=0;
187 commit;
188 end loop;
189 --
190 commit;
191 hr_utility.set_location ('Leaving '||l_package,10);
192 --
193 end delete_reporting_rows;
194 --
195 procedure delete_batch_range_rows(p_benefit_action_id in number,
196 p_rows out nocopy number) is
197 --
198 l_package varchar2(80) := g_package||'.delete_batch_range_rows';
199 l_records_to_be_deleted number := 5000;
200 --
201 begin
202 --
203 hr_utility.set_location ('Entering '||l_package,10);
204 --
205 p_rows :=0;
206 loop
207 delete from ben_batch_ranges
208 where benefit_action_id = p_benefit_action_id
209 and rownum <=l_records_to_be_deleted;
210 --
211 p_rows := p_rows + sql%rowcount;
212 --
213 exit when sql%rowcount=0;
214 commit;
215 end loop;
216 --
217 commit;
218 hr_utility.set_location ('Leaving '||l_package,10);
219 --
220 end delete_batch_range_rows;
221 --
222 procedure delete_batch_ler_rows(p_benefit_action_id in number,
223 p_rows out nocopy number) is
224 --
225 l_package varchar2(80) := g_package||'.delete_batch_ler_rows';
226 --
227 l_records_to_be_deleted number := 5000;
228 begin
229 --
230 hr_utility.set_location ('Entering '||l_package,10);
231 --
232 p_rows :=0;
233 loop
234 delete from ben_batch_ler_info
235 where benefit_action_id = p_benefit_action_id
236 and rownum <=l_records_to_be_deleted;
237 --
238 p_rows := p_rows + sql%rowcount;
239 --
240 exit when sql%rowcount=0;
241 commit;
242 end loop;
243 --
244 commit;
245 hr_utility.set_location ('Leaving '||l_package,10);
246 --
247 end delete_batch_ler_rows;
248 --
249 procedure delete_batch_dpnt_rows(p_benefit_action_id in number,
250 p_rows out nocopy number) is
251 --
252 l_package varchar2(80) := g_package||'.delete_batch_dpnt_rows';
253 --
254 l_records_to_be_deleted number := 5000;
255 begin
256 --
257 hr_utility.set_location ('Entering '||l_package,10);
258 --
259 p_rows :=0;
260 loop
261 delete from ben_batch_dpnt_info
262 where benefit_action_id = p_benefit_action_id
263 and rownum <=l_records_to_be_deleted;
264 --
265 p_rows := p_rows + sql%rowcount;
266 --
267 commit;
268 exit when sql%rowcount=0;
269 end loop;
270 --
271 commit;
272 hr_utility.set_location ('Leaving '||l_package,10);
273 --
274 end delete_batch_dpnt_rows;
275 --
276 procedure delete_batch_elctbl_rows(p_benefit_action_id in number,
277 p_rows out nocopy number) is
278 --
279 l_package varchar2(80) := g_package||'.delete_batch_elctbl_rows';
283 --
280 l_records_to_be_deleted number := 5000;
281 --
282 begin
284 hr_utility.set_location ('Entering '||l_package,10);
285 --
286 p_rows :=0;
287 loop
288 delete from ben_batch_elctbl_chc_info
289 where benefit_action_id = p_benefit_action_id
290 and rownum <= l_records_to_be_deleted ;
291 --
292 p_rows := p_rows + sql%rowcount;
293 --
294 commit;
295 exit when sql%rowcount=0;
296 end loop;
297 --
298 commit;
299 --
300 hr_utility.set_location ('Leaving '||l_package,10);
301 --
302 end delete_batch_elctbl_rows;
303 --
304 procedure delete_batch_elig_rows(p_benefit_action_id in number,
305 p_rows out nocopy number) is
306 --
307 l_package varchar2(80) := g_package||'.delete_batch_elig_rows';
308 --
309 l_records_to_be_deleted number := 5000;
310 begin
311 --
312 hr_utility.set_location ('Entering '||l_package,10);
313 --
314 p_rows := 0 ;
315 loop
316 delete from ben_batch_elig_info
317 where benefit_action_id = p_benefit_action_id
318 and rownum <= l_records_to_be_deleted ;
319 --
320 p_rows := p_rows + sql%rowcount;
321 --
322 exit when sql%rowcount=0;
323 commit;
324 end loop;
325 --
326 commit;
327 hr_utility.set_location ('Leaving '||l_package,10);
328 --
329 end delete_batch_elig_rows;
330 --
331 procedure delete_batch_proc_rows(p_benefit_action_id in number,
332 p_rows out nocopy number) is
333 --
334 l_package varchar2(80) := g_package||'.delete_batch_proc_rows';
335 l_records_to_be_deleted number := 5000;
336 --
337 begin
338 --
339 hr_utility.set_location ('Entering '||l_package,10);
340 --
341 p_rows := 0 ;
342 loop
343 delete from ben_batch_proc_info
344 where benefit_action_id = p_benefit_action_id
345 and rownum <= l_records_to_be_deleted ;
346 --
347 p_rows := p_rows + sql%rowcount;
348 --
349 commit;
350 exit when sql%rowcount=0;
351 end loop;
352 --
353 commit;
354 --
355 hr_utility.set_location ('Leaving '||l_package,10);
356 --
357 end delete_batch_proc_rows;
358 --
359 procedure delete_batch_rate_rows(p_benefit_action_id in number,
360 p_rows out nocopy number) is
361 --
362 l_package varchar2(80) := g_package||'.delete_batch_rate_rows';
363 --
364 l_records_to_be_deleted number := 5000;
365 begin
366 --
367 hr_utility.set_location ('Entering '||l_package,10);
368 --
369 p_rows := 0 ;
370 loop
371 delete from ben_batch_rate_info
372 where benefit_action_id = p_benefit_action_id
373 and rownum <= l_records_to_be_deleted ;
374 --
375 p_rows := p_rows + sql%rowcount;
376 --
377 commit;
378 exit when sql%rowcount=0;
379 end loop;
380 --
381 commit;
382 --
383 hr_utility.set_location ('Leaving '||l_package,10);
384 --
385 end delete_batch_rate_rows;
386 --
387 procedure delete_person_action_rows(p_benefit_action_id in number,
388 p_rows out nocopy number) is
389 --
390 l_package varchar2(80) := g_package||'.delete_person_action_rows';
391 --
392 l_records_to_be_deleted number := 5000;
393 begin
394 --
395 hr_utility.set_location ('Entering '||l_package,10);
396 --
397 p_rows :=0;
398 loop
399 delete from ben_person_actions
400 where benefit_action_id = p_benefit_action_id
401 and rownum <= l_records_to_be_deleted;
402 --
403 p_rows := p_rows + sql%rowcount;
404 --
405 exit when sql%rowcount=0;
406 commit;
407 end loop;
408 --
409 commit;
410 hr_utility.set_location ('Leaving '||l_package,10);
411 --
412 end delete_person_action_rows;
413 --
414 procedure delete_benefit_action_rows(p_benefit_action_id in number,
415 p_rows out nocopy number) is
416 --
417 l_package varchar2(80) := g_package||'.delete_benefit_action_rows';
418 --
419 begin
420 --
421 hr_utility.set_location ('Entering '||l_package,10);
422 --
423 delete from ben_benefit_actions
424 where benefit_action_id = p_benefit_action_id;
425 --
426 p_rows := sql%rowcount;
427 --
428 commit;
429 --
430 hr_utility.set_location ('Leaving '||l_package,10);
431 --
432 end delete_benefit_action_rows;
433 --
434 procedure purge_single(p_benefit_action_id in number) is
435 --
436 l_package varchar2(80) := g_package||'.purge_single';
437 --
438 -- Variables to store rows being deleted
439 --
440 l_reporting_rows number := 0;
441 l_batch_range_rows number := 0;
442 l_person_action_rows number := 0;
443 l_dpnt_rows number := 0;
444 l_elctbl_chc_rows number := 0;
445 l_elig_rows number := 0;
446 l_proc_rows number := 0;
447 l_rate_rows number := 0;
448 l_ler_rows number := 0;
449 l_benefit_action_rows number := 0;
450 --
451 begin
452 --
453 hr_utility.set_location ('Entering '||l_package,10);
454 --
455 -- Delete in the order
456 --
457 -- 1) Reporting Rows
458 -- 2) Batch Range Rows
459 -- 3) Person Action Rows
460 -- 4) Dependent Information Rows
461 -- 5) Electable Choice Information Rows
462 -- 6) Eligibility Information Rows
463 -- 7) Process Information Rows
464 -- 8) Rate Information Rows
465 -- 9) Life Event Information Rows
466 -- 10) Benefit Action Rows
467 --
468 delete_reporting_rows(p_benefit_action_id => p_benefit_action_id,
469 p_rows => l_reporting_rows);
470 delete_batch_range_rows(p_benefit_action_id => p_benefit_action_id,
471 p_rows => l_batch_range_rows);
472 delete_person_action_rows(p_benefit_action_id => p_benefit_action_id,
473 p_rows => l_person_action_rows);
474 delete_batch_dpnt_rows(p_benefit_action_id => p_benefit_action_id,
475 p_rows => l_dpnt_rows);
476 delete_batch_elctbl_rows(p_benefit_action_id => p_benefit_action_id,
477 p_rows => l_elctbl_chc_rows);
478 delete_batch_elig_rows(p_benefit_action_id => p_benefit_action_id,
479 p_rows => l_elig_rows);
480 delete_batch_proc_rows(p_benefit_action_id => p_benefit_action_id,
481 p_rows => l_proc_rows);
482 delete_batch_rate_rows(p_benefit_action_id => p_benefit_action_id,
483 p_rows => l_rate_rows);
484 delete_batch_ler_rows(p_benefit_action_id => p_benefit_action_id,
485 p_rows => l_ler_rows);
486 delete_benefit_action_rows(p_benefit_action_id => p_benefit_action_id,
487 p_rows => l_benefit_action_rows);
488 --
489 write_logfile(p_benefit_action_id => p_benefit_action_id,
490 p_benefit_action_rows => l_benefit_action_rows,
491 p_batch_range_rows => l_batch_range_rows,
492 p_person_action_rows => l_person_action_rows,
493 p_reporting_rows => l_reporting_rows,
494 p_dpnt_rows => l_dpnt_rows,
495 p_elctbl_chc_rows => l_elctbl_chc_rows,
496 p_elig_rows => l_elig_rows,
497 p_proc_rows => l_proc_rows,
498 p_rate_rows => l_rate_rows,
499 p_ler_rows => l_ler_rows);
500 --
501 hr_utility.set_location ('Leaving '||l_package,10);
502 --
503 end purge_single;
504 --
505 procedure purge_all(errbuf out nocopy varchar2,
506 retcode out nocopy number,
507 p_concurrent_request_id in number default null,
508 p_business_group_id in number default null,
509 p_effective_date in varchar2 default null) is
510 --
511 l_package varchar2(80) := g_package||'.purge_all';
512 l_effective_date date;
513 --
514 cursor c_benefit_actions is
515 select bft.benefit_action_id
516 from ben_benefit_actions bft,
517 fnd_concurrent_requests fnd
518 where bft.business_group_id = nvl(p_business_group_id,bft.business_group_id)
519 and nvl(bft.request_id,-1) = nvl(p_concurrent_request_id,nvl(bft.request_id,-1))
520 and bft.process_date = nvl(l_effective_date,bft.process_date)
521 /* Outer join to provide backwards compatability, for all cases where request id is blank */
522 and fnd.request_id(+) = bft.request_id
523 and nvl(fnd.phase_code,'C') = 'C';
524 --
525 -- l_benefit_actions c_benefit_actions%rowtype;
526 l_errbuf varchar2(2000);
527 l_retcode number;
528 --
529 type benactionTable is table of c_benefit_actions%rowtype;
530 l_benefit_actions benactionTable;
531
532 begin
533 --
534 hr_utility.set_location ('Entering '||l_package,10);
535 --
536 -- Convert date from canonical to regular date
537 --
538 -- Convert varchar2 dates to real dates
539 -- 1) First remove time component
540 -- 2) Next convert format
541 /*
542 l_effective_date := to_date(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
543 l_effective_date := to_date(to_char(trunc(l_effective_date),'DD/MM/RRRR'),'DD/MM/RRRR');
544 */
545 l_effective_date := trunc(fnd_date.canonical_to_date(p_effective_date));
546 --
547 -- Sanity check that at least one field has been entered
548 --
549 if p_concurrent_request_id is null and
550 p_business_group_id is null and
551 p_effective_date is null then
552 --
553 fnd_message.set_name('BEN','BEN_91752_BENPURGE_PARAMS');
554 fnd_message.set_token('PROC',l_package);
555 fnd_message.raise_error;
556 --
557 end if;
558 --
559 -- Log runtime parameters
560 --
561 write_params(p_concurrent_request_id => p_concurrent_request_id,
562 p_business_group_id => p_business_group_id,
563 p_effective_date => l_effective_date);
564 --
565 -- Open cursor and purge single benefit action
566 --
567 open c_benefit_actions;
568 fetch c_benefit_actions BULK COLLECT INTO l_benefit_actions;
569 close c_benefit_actions;
570 --
571 FOR i IN 1..l_benefit_actions.COUNT
572 loop
573 purge_single(p_benefit_action_id => l_benefit_actions(i).benefit_action_id);
574 end loop;
575 --
576 hr_utility.set_location ('Leaving '||l_package,10);
577 --
578 end purge_all;
579 --
580 end ben_benmngle_purge;