[Home] [Help]
PACKAGE BODY: APPS.BEN_DELETE_ORPHAN_ROWS
Source
1 Package Body ben_delete_orphan_rows as
2 /* $Header: bedeorph.pkb 120.0.12010000.3 2008/08/05 14:21:29 ubhat ship $ */
3 --
4 -- Global Variable Declaration
5 --
6 g_package varchar2(80) := 'ben_delete_orphan_rows';
7 g_request_tbl g_request_table;
8 g_num_processes number := 0;
9 --
10 -- ----------------------------------------------------------------------------
11 -- |-------------------------< check_all_slaves_finished >--------------------------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- This procedure will make sure all the slaves belong to the master process
15 -- are completed before exit the loop.
16 --
17 procedure check_all_slaves_finished (p_rpt_flag Boolean default FALSE)
18 is
19 --
20 l_proc varchar2(80) := g_package || '.check_all_slaves_finished';
21 l_no_slaves boolean := true;
22 l_dummy varchar2(1);
23 --
24 Cursor c_slaves(p_request_id number) is
25 SELECT NULL
26 FROM fnd_concurrent_requests fnd
27 WHERE fnd.phase_code <> 'C' AND fnd.request_id = p_request_id;
28 --
29 begin
30 --
31 hr_utility.set_location ('Entering ' || l_proc, 5);
32 --
33 While l_no_slaves loop
34 --
35 l_no_slaves := false;
36 --
37 For l_count in 2..g_num_processes loop
38 --
39 open c_slaves(g_request_tbl(l_count));
40 fetch c_slaves into l_dummy;
41 --
42 If c_slaves%found then
43 --
44 l_no_slaves := true;
45 close c_slaves;
46 exit;
47 --
48 End if;
49 --
50 Close c_slaves;
51 --
52 End loop;
53 If (l_no_slaves) then
54 --
55 dbms_lock.sleep(5);
56 --
57 End if;
58 --
59 End loop;
60 --
61 hr_utility.set_location ('Leaving ' || l_proc, 10);
62 --
63 end check_all_slaves_finished;
64 --
65 -- ----------------------------------------------------------------------------
66 -- |-------------------------< delete_per_con >--------------------------------|
67 -- ----------------------------------------------------------------------------
68 --
69 -- This is procedure to delete orphan records from PER_CONTACT_RELATIOSHSHIPS
70 -- where deleted person_id is directly referenced.
71 --
72 procedure delete_per_con
73 is
74 --
75 l_proc varchar2(80) := g_package || '.delete_per_con';
76 --
77 begin
78 --
79 --
80 hr_utility.set_location ('Entering ' || l_proc, 5);
81 --
82 --
83 delete /*+ parallel(a) */ from per_contact_relationships a
84 where a.contact_person_id is not null
85 and a.contact_person_id not in ( select /*+ hash_aj index_ffs(ppf) parallel_index(ppf) */ person_id
86 from per_all_people_f ppf );
87 --
88 delete /*+ parallel(a) */ from per_contact_relationships a
89 where a.person_id is not null
90 and a.person_id not in ( select /*+ hash_aj index_ffs(ppf) parallel_index(ppf) */ person_id
91 from per_all_people_f ppf );
92 --
93
94 ben_batch_utils.write(p_text => 'PER_CONTACT_RELATIOSHSHIPS : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
95 --
96 hr_utility.set_location ('Leaving ' || l_proc, 10);
97 --
98 end delete_per_con;
99 --
100 -- ----------------------------------------------------------------------------
101 -- |-------------------------< delete_cbr_quald_bnf >--------------------------|
102 -- ----------------------------------------------------------------------------
103 --
104 -- This is procedure to delete orphan records from BEN_CBR_QUALD_BNF
105 -- where deleted person_id is directly referenced.
106 --
107 procedure delete_cbr_quald_bnf
108 is
109 --
110 l_proc varchar2(80) := g_package || '.delete_cbr_quald_bnf';
111 --
112 --
113 begin
114 --
115 hr_utility.set_location ('Entering ' || l_proc, 5);
116 --
117 --
118 DELETE FROM ben_cbr_quald_bnf a
119 WHERE NOT EXISTS (SELECT 1
120 FROM per_all_people_f per
121 WHERE per.person_id = a.cvrd_emp_person_id);
122 --
123 --
124 ben_batch_utils.write(p_text => 'BEN_CBR_QUALD_BNF : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
125
126 hr_utility.set_location ('Leaving ' || l_proc, 10);
127 --
128 end delete_cbr_quald_bnf;
129 --
130 -- ----------------------------------------------------------------------------
131 -- |-------------------------< delete_crt_ordr >--------------------------|
132 -- ----------------------------------------------------------------------------
133 --
134 -- This is procedure to delete orphan records from BEN_CRT_ORDR
135 -- where deleted person_id is directly referenced.
136 --
137 procedure delete_crt_ordr
138 is
139 --
140 l_proc varchar2(80) := g_package || '.delete_crt_ordr';
141 --
142 begin
143 --
144 --
145 hr_utility.set_location ('Entering ' || l_proc, 5);
146 --
147 --
148 DELETE FROM ben_crt_ordr a
149 WHERE NOT EXISTS (SELECT 1
150 FROM per_all_people_f per
151 WHERE per.person_id = a.person_id);
152 ben_batch_utils.write(p_text => 'BEN_CRT_ORDR : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
153 --
154 hr_utility.set_location ('Leaving ' || l_proc, 10);
155 --
156 end delete_crt_ordr;
157 --
158 -- ----------------------------------------------------------------------------
159 -- |-------------------------< delete_crt_ordr_cvrd_per >--------------------------|
160 -- ----------------------------------------------------------------------------
161 --
162 -- This is procedure to delete orphan records from BEN_CRT_ORDR_CVRD_PER
163 -- where deleted person_id is directly referenced.
164 --
165 procedure delete_crt_ordr_cvrd_per
166 is
167 --
168 l_proc varchar2(80) := g_package || '.delete_crt_ordr_cvrd_per';
169 --
170 begin
171 --
172 --
173 hr_utility.set_location ('Entering ' || l_proc, 5);
174 --
175 --
176 DELETE FROM ben_crt_ordr_cvrd_per a
177 WHERE NOT EXISTS (SELECT 1
178 FROM per_all_people_f per
179 WHERE per.person_id = a.person_id);
180 ben_batch_utils.write(p_text => 'BEN_CRT_ORDR_CVRD_PER : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
181 --
182 hr_utility.set_location ('Leaving ' || l_proc, 10);
183 --
184 end delete_crt_ordr_cvrd_per;
185 --
186 -- ----------------------------------------------------------------------------
187 -- |-------------------------< delete_elig_dpnt >--------------------------|
188 -- ----------------------------------------------------------------------------
189 --
190 -- This is procedure to delete orphan records from BEN_ELIG_DPNT
191 -- where deleted person_id is directly referenced.
192 --
193 procedure delete_elig_dpnt
194 is
195 --
196 l_proc varchar2(80) := g_package || '.delete_elig_dpnt';
197 --
198 begin
199 --
200 --
201 hr_utility.set_location ('Entering ' || l_proc, 5);
202 --
203 --
204 DELETE /*+ PARALLEL(A) */ FROM ben_elig_dpnt a
205 WHERE a.dpnt_person_id IS NOT NULL
206 AND a.dpnt_person_id NOT IN (SELECT /*+ HASH_AJ INDEX_FFS(PER) PARALLEL_INDEX(PER) */ person_id
207 FROM per_all_people_f per);
208 ben_batch_utils.write(p_text => 'BEN_ELIG_DPNT : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
209 --
210 hr_utility.set_location ('Leaving ' || l_proc, 10);
211 --
212 end delete_elig_dpnt;
213 --
214 -- ----------------------------------------------------------------------------
215 -- |-------------------------< delete_elig_per_f >--------------------------|
216 -- ----------------------------------------------------------------------------
217 --
218 -- This is procedure to delete orphan records from BEN_ELIG_PER_F
219 -- where deleted person_id is directly referenced.
220 --
221 procedure delete_elig_per_f
222 is
223 --
224 l_proc varchar2(80) := g_package || '.delete_elig_per_f';
225 --
226 begin
227 --
228 --
229 hr_utility.set_location ('Entering ' || l_proc, 5);
230 --
231 --
232 DELETE /*+ PARALLEL(A) */FROM ben_elig_per_f a
233 WHERE a.person_id IS NOT NULL
234 AND a.person_id NOT IN (SELECT /*+ HASH_AJ INDEX_FFS(PER) PARALLEL_INDEX(PER) */ person_id
235 FROM per_all_people_f per);
236 ben_batch_utils.write(p_text => 'BEN_ELIG_PER_F : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
237 --
238 hr_utility.set_location ('Leaving ' || l_proc, 10);
239 --
240 end delete_elig_per_f;
241 --
242 -- ----------------------------------------------------------------------------
243 -- |-------------------------< delete_ext_chg_evt_log >--------------------------|
244 -- ----------------------------------------------------------------------------
245 --
246 -- This is procedure to delete orphan records from BEN_EXT_CHG_EVT_LOG
247 -- where deleted person_id is directly referenced.
248 --
249 procedure delete_ext_chg_evt_log
250 is
251 --
252 l_proc varchar2(80) := g_package || '.delete_ext_chg_evt_log';
253 --
254 begin
255 --
256 --
257 hr_utility.set_location ('Entering ' || l_proc, 5);
258 --
259 --
260 DELETE /*+ PARALLEL(A) */FROM ben_ext_chg_evt_log a
261 WHERE a.person_id IS NOT NULL
262 AND a.person_id NOT IN (SELECT /*+ HASH_AJ INDEX_FFS(PER) PARALLEL_INDEX(PER) */ person_id
263 FROM per_all_people_f per);
264 ben_batch_utils.write(p_text => 'BEN_EXT_CHG_EVT_LOG : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
265 --
266 hr_utility.set_location ('Leaving ' || l_proc, 10);
267 --
268 end delete_ext_chg_evt_log;
269 --
270 -- ----------------------------------------------------------------------------
271 -- |-------------------------< delete_ext_rslt_dtl >--------------------------|
272 -- ----------------------------------------------------------------------------
273 --
274 -- This is procedure to delete orphan records from BEN_EXT_RSLT_DTL
275 -- where deleted person_id is directly referenced.
276 --
277 procedure delete_ext_rslt_dtl
278 is
279 --
280 l_proc varchar2(80) := g_package || '.delete_ext_rslt_dtl';
281 --
282 begin
283 --
284 --
285 hr_utility.set_location ('Entering ' || l_proc, 5);
286 --
287 --
288 DELETE /*+ PARALLEL(A) */FROM ben_ext_rslt_dtl a
289 WHERE a.person_id IS NOT NULL
290 AND a.person_id NOT IN (0, 999999999999)
291 AND a.person_id NOT IN (SELECT /*+ HASH_AJ INDEX_FFS(PER) PARALLEL_INDEX(PER) */ person_id
292 FROM per_all_people_f per);
293 ben_batch_utils.write(p_text => 'BEN_EXT_RSLT_DTL : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
294 --
295 hr_utility.set_location ('Leaving ' || l_proc, 10);
296 --
297 end delete_ext_rslt_dtl;
298 --
299 -- ----------------------------------------------------------------------------
300 -- |-------------------------< delete_ext_rslt_err >--------------------------|
301 -- ----------------------------------------------------------------------------
302 --
303 -- This is procedure to delete orphan records from BEN_EXT_RSLT_ERR
304 -- where deleted person_id is directly referenced.
305 --
306 procedure delete_ext_rslt_err
307 is
308 --
309 l_proc varchar2(80) := g_package || '.delete_ext_rslt_err';
310 --
311 begin
312 --
313 --
314 hr_utility.set_location ('Entering ' || l_proc, 5);
315 --
316 --
317 DELETE /*+ PARALLEL(A) */FROM ben_ext_rslt_err a
318 WHERE a.person_id IS NOT NULL
319 AND a.person_id NOT IN (SELECT /*+ HASH_AJ INDEX_FFS(PER) PARALLEL_INDEX(PER) */ person_id
320 FROM per_all_people_f per);
321 ben_batch_utils.write(p_text => 'BEN_EXT_RSLT_ERR : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
322 --
323 hr_utility.set_location ('Leaving ' || l_proc, 10);
324 --
325 end delete_ext_rslt_err;
326 --
327 -- ----------------------------------------------------------------------------
328 -- |-------------------------< delete_le_clsn_n_rstr >--------------------------|
329 -- ----------------------------------------------------------------------------
330 --
331 -- This is procedure to delete orphan records from BEN_LE_CLSN_N_RSTR
332 -- where deleted person_id is directly referenced.
333 --
334 procedure delete_le_clsn_n_rstr
335 is
336 --
337 l_proc varchar2(80) := g_package || '.delete_le_clsn_n_rstr';
338 --
339 begin
340 --
341 --
342 hr_utility.set_location ('Entering ' || l_proc, 5);
343 --
344 --
345 DELETE /*+ PARALLEL(A) */FROM ben_le_clsn_n_rstr a
346 WHERE a.person_id IS NOT NULL
347 AND a.person_id NOT IN (SELECT /*+ HASH_AJ INDEX_FFS(PER) PARALLEL_INDEX(PER) */ person_id
348 FROM per_all_people_f per);
349 ben_batch_utils.write(p_text => 'BEN_LE_CLSN_N_RSTR : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
350 --
351 hr_utility.set_location ('Leaving ' || l_proc, 10);
352 --
353 end delete_le_clsn_n_rstr;
354 --
355 -- ----------------------------------------------------------------------------
356 -- |-------------------------< delete_person_actions >--------------------------|
357 -- ----------------------------------------------------------------------------
358 --
359 -- This is procedure to delete orphan records from BEN_PERSON_ACTIONS
360 -- where deleted person_id is directly referenced.
361 --
362 procedure delete_person_actions
363 is
364 --
365 l_proc varchar2(80) := g_package || '.delete_person_actions';
366 --
367 begin
368 --
369 --
373 DELETE /*+ PARALLEL(A) */FROM ben_person_actions a
370 hr_utility.set_location ('Entering ' || l_proc, 5);
371 --
372 --
374 WHERE a.person_id IS NOT NULL
375 AND a.person_id NOT IN (SELECT /*+ HASH_AJ INDEX_FFS(PER) PARALLEL_INDEX(PER) */ person_id
376 FROM per_all_people_f per);
377 ben_batch_utils.write(p_text => 'BEN_PERSON_ACTIONS : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
378 --
379 hr_utility.set_location ('Leaving ' || l_proc, 10);
380 --
381 end delete_person_actions;
382 --
383 -- ----------------------------------------------------------------------------
384 -- |-------------------------< delete_per_bnfts_bal_f >--------------------------|
385 -- ----------------------------------------------------------------------------
386 --
387 -- This is procedure to delete orphan records from BEN_PER_BNFTS_BAL_F
388 -- where deleted person_id is directly referenced.
389 --
390 procedure delete_per_bnfts_bal_f
391 is
392 --
393 l_proc varchar2(80) := g_package || '.delete_per_bnfts_bal_f';
394 --
395 begin
396 --
397 --
398 hr_utility.set_location ('Entering ' || l_proc, 5);
399 --
400 --
401 DELETE FROM ben_per_bnfts_bal_f a
402 WHERE NOT EXISTS (SELECT 1
403 FROM per_all_people_f per
404 WHERE per.person_id = a.person_id);
405 ben_batch_utils.write(p_text => 'BEN_PER_BNFTS_BAL_F : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
406 --
407 hr_utility.set_location ('Leaving ' || l_proc, 10);
408 --
409 end delete_per_bnfts_bal_f;
410 --
411 -- ----------------------------------------------------------------------------
412 -- |-------------------------< delete_per_dlvry_mthd_f >--------------------------|
413 -- ----------------------------------------------------------------------------
414 --
415 -- This is procedure to delete orphan records from BEN_PER_DLVRY_MTHD_F
416 -- where deleted person_id is directly referenced.
417 --
418 procedure delete_per_dlvry_mthd_f
419 is
420 --
421 l_proc varchar2(80) := g_package || '.delete_per_dlvry_mthd_f';
422 --
423 begin
424 --
425 --
426 hr_utility.set_location ('Entering ' || l_proc, 5);
427 --
428 --
429 DELETE FROM ben_per_dlvry_mthd_f a
430 WHERE NOT EXISTS (SELECT 1
431 FROM per_all_people_f per
432 WHERE per.person_id = a.person_id);
433 ben_batch_utils.write(p_text => 'BEN_PER_DLVRY_MTHD_F : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
434 --
435 hr_utility.set_location ('Leaving ' || l_proc, 10);
436 --
437 end delete_per_dlvry_mthd_f;
438 --
439 -- ----------------------------------------------------------------------------
440 -- |-------------------------< delete_per_pin_f >--------------------------|
441 -- ----------------------------------------------------------------------------
442 --
443 -- This is procedure to delete orphan records from BEN_PER_PIN_F
444 -- where deleted person_id is directly referenced.
445 --
446 procedure delete_per_pin_f
447 is
448 --
449 l_proc varchar2(80) := g_package || '.delete_per_pin_f';
450 --
451 begin
452 --
453 --
454 hr_utility.set_location ('Entering ' || l_proc, 5);
455 --
456 --
457 DELETE FROM ben_per_pin_f a
458 WHERE NOT EXISTS (SELECT 1
459 FROM per_all_people_f per
460 WHERE per.person_id = a.person_id);
461 ben_batch_utils.write(p_text => 'BEN_PER_PIN_F : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
462 --
463 hr_utility.set_location ('Leaving ' || l_proc, 10);
464 --
465 end delete_per_pin_f;
466 --
467 -- ----------------------------------------------------------------------------
468 -- |-------------------------< delete_pl_bnf_f >--------------------------|
469 -- ----------------------------------------------------------------------------
470 --
471 -- This is procedure to delete orphan records from BEN_PL_BNF_F
472 -- where deleted person_id is directly referenced.
473 --
474 procedure delete_pl_bnf_f
475 is
476 --
477 l_proc varchar2(80) := g_package || '.delete_pl_bnf_f';
478 --
479 begin
480 --
481 --
482 hr_utility.set_location ('Entering ' || l_proc, 5);
483 --
484 --
485 DELETE FROM ben_pl_bnf_f a
486 WHERE NOT EXISTS (
487 SELECT 1
488 FROM per_all_people_f per
489 WHERE per.person_id = a.bnf_person_id
490 OR per.person_id = a.ttee_person_id);
491 ben_batch_utils.write(p_text => 'BEN_PL_BNF_F : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
492 --
493 hr_utility.set_location ('Leaving ' || l_proc, 10);
494 --
495 end delete_pl_bnf_f;
496 --
497 -- ----------------------------------------------------------------------------
498 -- |-------------------------< delete_prtt_reimbmt_rqst_f >--------------------------|
499 -- ----------------------------------------------------------------------------
500 --
501 -- This is procedure to delete orphan records from BEN_PRTT_REIMBMT_RQST_F
505 is
502 -- where deleted person_id is directly referenced.
503 --
504 procedure delete_prtt_reimbmt_rqst_f
506 --
507 l_proc varchar2(80) := g_package || '.delete_prtt_reimbmt_rqst_f';
508 --
509 begin
510 --
511 --
512 hr_utility.set_location ('Entering ' || l_proc, 5);
513 --
514 --
515 DELETE FROM ben_prtt_reimbmt_rqst_f a
516 WHERE NOT EXISTS (
517 SELECT 1
518 FROM per_all_people_f per
519 WHERE per.person_id = a.submitter_person_id
520 OR per.person_id = a.recipient_person_id
521 OR per.person_id = a.provider_person_id
522 OR per.person_id = a.provider_ssn_person_id);
523 ben_batch_utils.write(p_text => 'BEN_PRTT_REIMBMT_RQST_F : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
524 --
525 hr_utility.set_location ('Leaving ' || l_proc, 10);
526 --
527 end delete_prtt_reimbmt_rqst_f;
528 --
529 -- ----------------------------------------------------------------------------
530 -- |-------------------------< delete_ptnl_ler_for_per >--------------------------|
531 -- ----------------------------------------------------------------------------
532 --
533 -- This is procedure to delete orphan records from BEN_PTNL_LER_FOR_PER
534 -- where deleted person_id is directly referenced.
535 --
536 procedure delete_ptnl_ler_for_per
537 is
538 --
539 l_proc varchar2(80) := g_package || '.delete_ptnl_ler_for_per';
540 --
541 begin
542 --
543 --
544 hr_utility.set_location ('Entering ' || l_proc, 5);
545 --
546 --
547 DELETE /*+ PARALLEL(A) */FROM ben_ptnl_ler_for_per a
548 WHERE a.person_id IS NOT NULL
549 AND a.person_id NOT IN (SELECT /*+ HASH_AJ INDEX_FFS(PER) PARALLEL_INDEX(PER) */ person_id
550 FROM per_all_people_f per);
551 ben_batch_utils.write(p_text => 'BEN_PTNL_LER_FOR_PER : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
552 --
553 hr_utility.set_location ('Leaving ' || l_proc, 10);
554 --
555 end delete_ptnl_ler_for_per;
556 --
557 -- ----------------------------------------------------------------------------
558 -- |-------------------------< delete_benefit_actions >--------------------------|
559 -- ----------------------------------------------------------------------------
560 --
561 -- This is procedure to delete all orphan child records of BEN_BENEFIT_ACTIONS
562 -- and then records of BEN_BENEFIT_ACTIONS itself where deleted person_id is directly
563 -- referenced.
564 -- Hierarchy to use
565 --
566 -- BEN_BENEFIT_ACTIONS
567 -- BEN_BATCH_ACTN_ITEM_INFO
568 -- BEN_BATCH_BNFT_CERT_INFO
569 -- BEN_BATCH_COMMU_INFO
570 -- BEN_BATCH_DPNT_INFO
571 -- BEN_BATCH_ELCTBL_CHC_INFO
572 -- BEN_BATCH_ELIG_INFO
573 -- BEN_BATCH_LER_INFO
574 -- BEN_BATCH_RATE_INFO
575 -- BEN_BATCH_PROC_INFO
576 -- BEN_BATCH_RANGES
577 -- BEN_REPORTING
578 --
579 procedure delete_benefit_actions
580 is
581 --
582 l_proc varchar2(80) := g_package || '.delete_benefit_actions';
583 l_data Numdata;
584 --
585 cursor c1 is
586 SELECT /*+ PARALLEL(A) */
587 DISTINCT benefit_action_id
588 FROM ben_benefit_actions a
589 WHERE a.person_id IS NOT NULL
590 AND a.person_id NOT IN (SELECT /*+ HASH_AJ INDEX_FFS(PER) PARALLEL_INDEX(PER) */ person_id
591 FROM per_all_people_f per);
592 --
593 begin
594 --
595 hr_utility.set_location ('Entering ' || l_proc, 5);
596 --
597 --
598 OPEN c1 ;
599 LOOP
600 FETCH c1 BULK COLLECT INTO l_data ;
601 --
602 FORALL i IN 1..l_data.COUNT
603 DELETE FROM BEN_BATCH_ACTN_ITEM_INFO
604 WHERE benefit_action_id = l_data(i);
605 --
606 ben_batch_utils.write(p_text => 'BEN_BATCH_ACTN_ITEM_INFO : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
607 --
608 FORALL i IN 1..l_data.COUNT
609 DELETE FROM BEN_BATCH_BNFT_CERT_INFO
610 WHERE benefit_action_id = l_data(i);
611 --
612 ben_batch_utils.write(p_text => 'BEN_BATCH_BNFT_CERT_INFO : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
613 --
614 FORALL i IN 1..l_data.COUNT
615 DELETE FROM BEN_BATCH_COMMU_INFO
616 WHERE benefit_action_id = l_data(i);
617 --
618 ben_batch_utils.write(p_text => 'BEN_BATCH_COMMU_INFO : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
619 --
620 FORALL i IN 1..l_data.COUNT
621 DELETE FROM BEN_BATCH_DPNT_INFO
622 WHERE benefit_action_id = l_data(i);
623 --
624 ben_batch_utils.write(p_text => 'BEN_BATCH_DPNT_INFO : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
625 --
626 FORALL i IN 1..l_data.COUNT
627 DELETE FROM BEN_BATCH_ELCTBL_CHC_INFO
628 WHERE benefit_action_id = l_data(i);
629 --
630 ben_batch_utils.write(p_text => 'BEN_BATCH_ELCTBL_CHC_INFO : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
631 --
632 FORALL i IN 1..l_data.COUNT
633 DELETE FROM BEN_BATCH_ELIG_INFO
634 WHERE benefit_action_id = l_data(i);
638 FORALL i IN 1..l_data.COUNT
635 --
636 ben_batch_utils.write(p_text => 'BEN_BATCH_ELIG_INFO : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
637 --
639 DELETE FROM BEN_BATCH_LER_INFO
640 WHERE benefit_action_id = l_data(i);
641 --
642 ben_batch_utils.write(p_text => 'BEN_BATCH_LER_INFO : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
643 --
644 FORALL i IN 1..l_data.COUNT
645 DELETE FROM BEN_BATCH_RATE_INFO
646 WHERE benefit_action_id = l_data(i);
647 --
648 ben_batch_utils.write(p_text => 'BEN_BATCH_RATE_INFO : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
649 --
650 FORALL i IN 1..l_data.COUNT
651 DELETE FROM BEN_BATCH_PROC_INFO
652 WHERE benefit_action_id = l_data(i);
653 --
654 ben_batch_utils.write(p_text => 'BEN_BATCH_PROC_INFO : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
655 --
656 FORALL i IN 1..l_data.COUNT
657 DELETE FROM BEN_BATCH_RANGES
658 WHERE benefit_action_id = l_data(i);
659 --
660 ben_batch_utils.write(p_text => 'BEN_BATCH_RANGES : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
661 --
662 FORALL i IN 1..l_data.COUNT
663 DELETE FROM BEN_REPORTING
664 WHERE benefit_action_id = l_data(i);
665 --
666 ben_batch_utils.write(p_text => 'BEN_REPORTING : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
667 --
668 FORALL i IN 1..l_data.COUNT
669 DELETE FROM ben_benefit_actions
670 WHERE benefit_action_id = l_data(i);
671 --
672 ben_batch_utils.write(p_text => 'BEN_BENEFIT_ACTIONS : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
673 --
674 EXIT WHEN c1%NOTFOUND;
675 --
676 end loop;
677 --
678 CLOSE c1;
679 --
680 hr_utility.set_location ('Leaving ' || l_proc, 10);
681 --
682 end delete_benefit_actions;
683 --
684 -- ----------------------------------------------------------------------------
685 -- |-------------------------< delete_per_in_ler >--------------------------|
686 -- ----------------------------------------------------------------------------
687 --
688 -- This is procedure to delete all orphan child records of BEN_PER_IN_LER
689 -- and then records of BEN_PER_IN_LER itself where deleted person_id is directly
690 -- referenced.
691 -- Hierarchy to use
692 --
693 -- BEN_PER_IN_LER
694 -- BEN_BNFT_PRVDD_LDGR_F
695 -- BEN_CBR_PER_IN_LER
696 -- BEN_ELIG_PER_OPT_F
697 -- BEN_ELIG_DPNT
698 -- BEN_PIL_ELCTBL_CHC_POPL
699 -- BEN_PRTT_PREM_BY_MO_F
700 -- BEN_PRTT_PREM_F
701 -- BEN_ENRT_PREM
702 -- BEN_ENRT_BNFT
703 -- BEN_ENRT_RT
704 -- BEN_ELIG_PER_ELCTBL_CHC
705 --
706 procedure delete_per_in_ler
707 is
708 --
709 l_proc varchar2(80) := g_package || '.delete_per_in_ler';
710 l_data Numdata;
711 --
712 cursor c2 is
713 SELECT /*+ PARALLEL(A) */
714 DISTINCT per_in_ler_id
715 FROM ben_per_in_ler a
716 WHERE a.person_id IS NOT NULL
717 AND a.person_id NOT IN (SELECT /*+ HASH_AJ INDEX_FFS(PER) PARALLEL_INDEX(PER) */ person_id
718 FROM per_all_people_f per);
719 --
720 begin
721 --
722 hr_utility.set_location ('Entering ' || l_proc, 5);
723 --
724 --
725 OPEN c2 ;
726 LOOP
727 FETCH c2 BULK COLLECT INTO l_data;
728 --
729 FORALL i IN 1..l_data.COUNT
730 DELETE FROM BEN_BNFT_PRVDD_LDGR_F
731 WHERE PER_IN_LER_ID = l_data(i);
732 --
733 ben_batch_utils.write(p_text => 'BEN_BNFT_PRVDD_LDGR_F : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
734 --
735 FORALL i IN 1..l_data.COUNT
736 DELETE FROM BEN_CBR_PER_IN_LER
737 WHERE PER_IN_LER_ID = l_data(i);
738 --
739 ben_batch_utils.write(p_text => 'BEN_CBR_PER_IN_LER : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
740 --
741 FORALL i IN 1..l_data.COUNT
742 DELETE FROM BEN_ELIG_PER_OPT_F
743 WHERE PER_IN_LER_ID = l_data(i);
744 --
745 ben_batch_utils.write(p_text => 'BEN_ELIG_PER_OPT_F : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
746 --
747 FORALL i IN 1..l_data.COUNT
748 delete from BEN_ELIG_DPNT a
749 where PER_IN_LER_ID = l_data(i);
750 --
751 ben_batch_utils.write(p_text => 'BEN_ELIG_DPNT : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
752 --
753 FORALL i IN 1..l_data.COUNT
754 delete from BEN_PIL_ELCTBL_CHC_POPL a
755 where PER_IN_LER_ID = l_data(i);
756 --
757 ben_batch_utils.write(p_text => 'BEN_PIL_ELCTBL_CHC_POPL : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
758 --
759 FORALL i IN 1..l_data.COUNT
760 delete from BEN_ENRT_RT
761 where ELIG_PER_ELCTBL_CHC_ID in ( select ELIG_PER_ELCTBL_CHC_ID
762 from BEN_ELIG_PER_ELCTBL_CHC a
763 where a.PER_IN_LER_ID = l_data(i) );
764 --
768 delete from BEN_PRTT_PREM_BY_MO_F
765 ben_batch_utils.write(p_text => 'BEN_ENRT_RT : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
766 --
767 FORALL i IN 1..l_data.COUNT
769 where PRTT_PREM_ID IN ( select PRTT_PREM_ID
770 from BEN_PRTT_PREM_F a
771 where a.PER_IN_LER_ID = l_data(i) );
772 --
773 ben_batch_utils.write(p_text => 'BEN_PRTT_PREM_BY_MO_F : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
774 --
775 FORALL i IN 1..l_data.COUNT
776 delete from BEN_PRTT_PREM_F a
777 where PER_IN_LER_ID = l_data(i);
778 --
779 ben_batch_utils.write(p_text => 'BEN_PRTT_PREM_F : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
780 --
781 FORALL i IN 1..l_data.COUNT
782 delete from BEN_ENRT_PREM
783 where ELIG_PER_ELCTBL_CHC_ID in ( select ELIG_PER_ELCTBL_CHC_ID
784 from BEN_ELIG_PER_ELCTBL_CHC a
785 where a.PER_IN_LER_ID = l_data(i) );
786 --
787 ben_batch_utils.write(p_text => 'BEN_ENRT_PREM : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
788 --
789 FORALL i IN 1..l_data.COUNT
790 delete from BEN_ENRT_BNFT
791 where ELIG_PER_ELCTBL_CHC_ID in ( select ELIG_PER_ELCTBL_CHC_ID
792 from BEN_ELIG_PER_ELCTBL_CHC a
793 where a.PER_IN_LER_ID = l_data(i) );
794 --
795 ben_batch_utils.write(p_text => 'BEN_ENRT_BNFT : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
796 --
797 FORALL i IN 1..l_data.COUNT
798 delete from BEN_ELIG_PER_ELCTBL_CHC a
799 where PER_IN_LER_ID = l_data(i) ;
800 --
801 ben_batch_utils.write(p_text => 'BEN_ELIG_PER_ELCTBL_CHC : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
802 --
803 FORALL i IN 1..l_data.COUNT
804 DELETE FROM BEN_PER_IN_LER
805 WHERE PER_IN_LER_ID = l_data(i) ;
806 --
807 ben_batch_utils.write(p_text => 'BEN_PER_IN_LER : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
808 --
809 EXIT WHEN c2%NOTFOUND;
810 --
811 end loop;
812 --
813 CLOSE c2;
814 --
815 hr_utility.set_location ('Leaving ' || l_proc, 10);
816 --
817 end delete_per_in_ler;
818 --
819 -- ----------------------------------------------------------------------------
820 -- |-------------------------< delete_prtt_enrt_rslt_f >--------------------------|
821 -- ----------------------------------------------------------------------------
822 --
823 -- This is procedure to delete all orphan child records of BEN_PRTT_ENRT_RSLT_F
824 -- and then records of BEN_PRTT_ENRT_RSLT_F itself where deleted person_id is directly
825 -- referenced.
826 -- Hierarchy to use
827 --
828 -- BEN_PRTT_ENRT_RSLT_F
829 -- BEN_PRMRY_CARE_PRVDR_F
830 -- BEN_PRTT_ENRT_ACTN_F
831 -- BEN_PRTT_ENRT_CTFN_PRVDD_F
832 -- BEN_PRTT_PREM_F
833 -- BEN_PRTT_RT_VAL
834 --
835 procedure delete_prtt_enrt_rslt_f
836 is
837 --
838 l_proc varchar2(80) := g_package || '.delete_prtt_enrt_rslt_f';
839 l_data Numdata;
840 --
841 cursor c3 is
842 SELECT /*+ PARALLEL(A) */
843 DISTINCT prtt_enrt_rslt_id
844 FROM ben_prtt_enrt_rslt_f a
845 WHERE a.person_id IS NOT NULL
846 AND a.person_id NOT IN (SELECT /*+ HASH_AJ INDEX_FFS(PER) PARALLEL_INDEX(PER) */ person_id
847 FROM per_all_people_f per);
848 --
849 begin
850 --
851 hr_utility.set_location ('Entering ' || l_proc, 5);
852 --
853 --
854 OPEN c3 ;
855 LOOP
856 FETCH c3 BULK COLLECT INTO l_data;
857 --
858 FORALL i IN 1..l_data.COUNT
859 DELETE FROM BEN_PRMRY_CARE_PRVDR_F
860 WHERE prtt_enrt_rslt_id = l_data(i);
861 --
862 ben_batch_utils.write(p_text => 'BEN_PRMRY_CARE_PRVDR_F : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
863 --
864 FORALL i IN 1..l_data.COUNT
865 DELETE FROM BEN_PRTT_ENRT_ACTN_F
866 WHERE prtt_enrt_rslt_id = l_data(i);
867 --
868 ben_batch_utils.write(p_text => 'BEN_PRTT_ENRT_ACTN_F : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
869 --
870 FORALL i IN 1..l_data.COUNT
871 DELETE FROM BEN_PRTT_ENRT_CTFN_PRVDD_F
872 WHERE prtt_enrt_rslt_id = l_data(i);
873 --
874 ben_batch_utils.write(p_text => 'BEN_PRTT_ENRT_CTFN_PRVDD_F : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
875 --
876 FORALL i IN 1..l_data.COUNT
877 DELETE FROM BEN_PRTT_PREM_F
878 WHERE prtt_enrt_rslt_id = l_data(i);
879 --
880 ben_batch_utils.write(p_text => 'BEN_PRTT_PREM_F : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
881 --
882 FORALL i IN 1..l_data.COUNT
883 DELETE FROM BEN_PRTT_RT_VAL
884 WHERE prtt_enrt_rslt_id = l_data(i);
885 --
886 ben_batch_utils.write(p_text => 'BEN_PRTT_RT_VAL : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
887 --
888 FORALL i IN 1..l_data.COUNT
889 DELETE FROM BEN_PRTT_ENRT_RSLT_F
890 WHERE prtt_enrt_rslt_id = l_data(i);
891 --
895 --
892 ben_batch_utils.write(p_text => 'BEN_PRTT_ENRT_RSLT_F : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
893 --
894 EXIT WHEN c3%NOTFOUND;
896 end loop;
897 --
898 CLOSE c3;
899 --
900 hr_utility.set_location ('Leaving ' || l_proc, 10);
901 --
902 end delete_prtt_enrt_rslt_f;
903 --
904 -- ----------------------------------------------------------------------------
905 -- |-------------------------< delete_elig_cvrd_dpnt_f >--------------------------|
906 -- ----------------------------------------------------------------------------
907 --
908 -- This is procedure to delete all orphan child records of BEN_ELIG_CVRD_DPNT_F
909 -- and then records of BEN_ELIG_CVRD_DPNT_F itself where deleted person_id is directly
910 -- referenced.
911 -- Hierarchy to use
912 --
913 -- BEN_ELIG_CVRD_DPNT_F
914 -- BEN_EXT_CRIT_VAL
915 -- BEN_CVRD_DPNT_CTFN_PRVDD_F
916 --
917 procedure delete_elig_cvrd_dpnt_f
918 is
919 --
920 l_proc varchar2(80) := g_package || '.delete_elig_cvrd_dpnt_f';
921 l_data Numdata;
922 --
923 cursor c4 is
924 SELECT /*+ PARALLEL(A) */
925 DISTINCT elig_cvrd_dpnt_id
926 FROM ben_elig_cvrd_dpnt_f a
927 WHERE NOT EXISTS (SELECT /*+ HASH_AJ INDEX_FFS(PER) PARALLEL(PER) */ 1
928 FROM per_all_people_f per
929 WHERE per.person_id = a.dpnt_person_id);
930 --
931 begin
932 --
933 hr_utility.set_location ('Entering ' || l_proc, 5);
934 --
935 --
936 OPEN c4 ;
937 LOOP
938 FETCH c4 BULK COLLECT INTO l_data ;
939 --
940 FORALL i IN 1..l_data.COUNT
941 DELETE FROM ben_cvrd_dpnt_ctfn_prvdd_f
942 WHERE elig_cvrd_dpnt_id = l_data (i);
943 --
944 ben_batch_utils.write(p_text => 'BEN_CVRD_DPNT_CTFN_PRVDD_F : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
945 --
946 FORALL i IN 1..l_data.COUNT
947 DELETE FROM ben_ext_crit_val
948 WHERE ext_crit_val_id IN (
949 SELECT DISTINCT ext_crit_val_id
950 FROM ben_ext_crit_val val, ben_ext_crit_typ typ
951 WHERE typ.crit_typ_cd = 'PID'
952 AND val.ext_crit_typ_id = typ.ext_crit_typ_id
953 AND val.val_1 = TO_CHAR (l_data (i)));
954 --
955 ben_batch_utils.write(p_text => 'BEN_EXT_CRIT_VAL : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
956 --
957 FORALL i IN 1..l_data.COUNT
958 DELETE FROM ben_elig_cvrd_dpnt_f
959 WHERE elig_cvrd_dpnt_id = l_data (i);
960 --
961 ben_batch_utils.write(p_text => 'BEN_ELIG_CVRD_DPNT_F : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
962 --
963 EXIT WHEN c4%NOTFOUND;
964 --
965 end loop;
966 --
967 CLOSE c4;
968 --
969 hr_utility.set_location ('Leaving ' || l_proc, 10);
970 --
971 end delete_elig_cvrd_dpnt_f;
972 --
973 -- ----------------------------------------------------------------------------
974 -- |-------------------------< delete_per_cm_f >--------------------------|
975 -- ----------------------------------------------------------------------------
976 --
977 -- This is procedure to delete all orphan child records of BEN_PER_CM_F
978 -- and then records of BEN_PER_CM_F itself where deleted person_id is directly
979 -- referenced.
980 -- Hierarchy to use
981 --
982 -- BEN_PER_CM_F
983 -- BEN_PER_CM_PRVDD_F
984 -- BEN_PER_CM_TRGR_F
985 -- BEN_PER_CM_USG_F
986 --
987 procedure delete_per_cm_f
988 is
989 --
990 l_proc varchar2(80) := g_package || '.delete_per_cm_f';
991 l_data Numdata;
992 --
993 cursor c5 is
994 SELECT /*+ PARALLEL(A) */
995 DISTINCT per_cm_id
996 FROM ben_per_cm_f a
997 WHERE NOT EXISTS (SELECT /*+ HASH_AJ INDEX_FFS(PER) PARALLEL(PER) */ 1
998 FROM per_all_people_f per
999 WHERE per.person_id = a.person_id);
1000 --
1001 begin
1002 --
1003 hr_utility.set_location ('Entering ' || l_proc, 5);
1004 --
1005 --
1006 OPEN c5 ;
1007 LOOP
1008 FETCH c5 BULK COLLECT INTO l_data ;
1009 --
1010 FORALL i IN 1..l_data.COUNT
1011 delete FROM ben_per_cm_prvdd_f
1012 WHERE per_cm_id = l_data(i) ;
1013 --
1014 ben_batch_utils.write(p_text => 'BEN_PER_CM_PRVDD_F : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
1015 --
1016 FORALL i IN 1..l_data.COUNT
1017 delete FROM ben_per_cm_trgr_f
1018 WHERE per_cm_id = l_data(i) ;
1019 --
1020 ben_batch_utils.write(p_text => 'BEN_PER_CM_TRGR_F : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
1021 --
1022 FORALL i IN 1..l_data.COUNT
1023 delete FROM ben_per_cm_usg_f
1024 WHERE per_cm_id = l_data(i) ;
1025 --
1026 ben_batch_utils.write(p_text => 'BEN_PER_CM_USG_F : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
1027 --
1028 FORALL i IN 1..l_data.COUNT
1029 delete FROM ben_per_cm_f
1033 --
1030 WHERE per_cm_id = l_data(i) ;
1031 --
1032 ben_batch_utils.write(p_text => 'BEN_PER_CM_F : ' || nvl(to_char(SQL%ROWCOUNT),'0'));
1034 EXIT WHEN c5%NOTFOUND;
1035 --
1036 end loop;
1037 --
1038 CLOSE c5;
1039 --
1040 hr_utility.set_location ('Leaving ' || l_proc, 10);
1041 --
1042 end delete_per_cm_f;
1043 --
1044 -- -----------------------------------------------------------------------------
1045 -- |--------------------------< do_multithread >-------------------------------|
1046 -- -----------------------------------------------------------------------------
1047 --
1048 -- This is the main batch procedure to be called from the concurrent manager
1049 --
1050 procedure do_multithread
1051 (errbuf out nocopy varchar2
1052 ,retcode out nocopy number
1053 ,p_parent_request_id in number
1054 ,p_thread_id in number )
1055 is
1056 --
1057 -- Local variable declaration
1058 --
1059 l_effective_date date;
1060 l_proc varchar2(80) := g_package || '.do_multithread';
1061 l_title_text varchar2(85);
1062 --
1063 begin
1064 --
1065 hr_utility.set_location ('Entering ' || l_proc, 5);
1066 --
1067 l_title_text := 'Number of rows deleted from the following tables :';
1068 --
1069 if p_thread_id = 1 then
1070 --
1071 ben_batch_utils.write(p_text => l_title_text);
1072 --
1073 delete_elig_per_f;
1074 delete_cbr_quald_bnf;
1075 delete_crt_ordr;
1076 delete_crt_ordr_cvrd_per ;
1077 --
1078 elsif p_thread_id = 2 then
1079 --
1080 ben_batch_utils.write(p_text => l_title_text);
1081 --
1082 delete_elig_dpnt;
1083 delete_per_dlvry_mthd_f ;
1084 delete_pl_bnf_f;
1085 delete_per_pin_f ;
1086 --
1087 elsif p_thread_id = 3 then
1088 --
1089 ben_batch_utils.write(p_text => l_title_text);
1090 --
1091 delete_ext_rslt_dtl ;
1092 delete_per_bnfts_bal_f ;
1093 delete_prtt_reimbmt_rqst_f;
1094 --
1095 elsif p_thread_id = 4 then
1096 --
1097 ben_batch_utils.write(p_text => l_title_text);
1098 --
1099 delete_ext_rslt_err;
1100 delete_person_actions;
1101 --
1102 elsif p_thread_id = 5 then
1103 --
1104 ben_batch_utils.write(p_text => l_title_text);
1105 --
1106 delete_benefit_actions;
1107 delete_le_clsn_n_rstr;
1108 --
1109 elsif p_thread_id = 6 then
1110 --
1111 ben_batch_utils.write(p_text => l_title_text);
1112 --
1113 delete_per_in_ler;
1114 --
1115 elsif p_thread_id = 7 then
1116 --
1117 ben_batch_utils.write(p_text => l_title_text);
1118 --
1119 delete_prtt_enrt_rslt_f;
1120 --
1121 elsif p_thread_id = 8 then
1122 --
1123 ben_batch_utils.write(p_text => l_title_text);
1124 --
1125 delete_elig_cvrd_dpnt_f;
1126 delete_ext_chg_evt_log;
1127 --
1128 elsif p_thread_id = 9 then
1129 --
1130 ben_batch_utils.write(p_text => l_title_text);
1131 --
1132 delete_per_cm_f;
1133 delete_ptnl_ler_for_per;
1134 delete_per_con ;
1135 --
1136 end if;
1137 --
1138 hr_utility.set_location ('Leaving ' || l_proc, 10);
1139 --
1140 end do_multithread;
1141 --
1142 -- ----------------------------------------------------------------------------
1143 -- |--------------------------------< process >-------------------------------|
1144 -- ----------------------------------------------------------------------------
1145 --
1146 -- This is the main batch procedure to be called from the concurrent manager.
1147 --
1148 procedure process
1149 ( errbuf out nocopy varchar2
1150 ,retcode out nocopy number
1151 )
1152 is
1153 --
1154 -- Local variable declaration.
1155 --
1156 l_proc varchar2(80) := g_package || '.process';
1157 l_benefit_action_id number(15);
1158 l_effective_date date;
1159 l_object_version_number number(15);
1160 l_threads number(5) := 1;
1161 l_request_id number(15);
1162 l_parent_request_id number(15);
1163 --
1164 -- Cursor Declaration.
1165 --
1166 begin
1167 --
1168 hr_utility.set_location ('Entering ' || l_proc, 5);
1169 --
1170 l_effective_date := trunc(sysdate);
1171 l_parent_request_id := fnd_global.conc_request_id;
1172 --
1173 -- Create a new benefit_action row.
1174 --
1175 ben_benefit_actions_api.create_perf_benefit_actions
1176 (p_validate => FALSE
1177 ,p_benefit_action_id => l_benefit_action_id
1178 ,p_process_date => l_effective_date
1179 ,p_mode_cd => 'S'
1180 ,p_derivable_factors_flag => 'N'
1181 ,p_validate_flag => 'N'
1182 ,p_business_group_id => fnd_global.per_business_group_id
1183 ,p_no_programs_flag => 'N'
1184 ,p_no_plans_flag => 'N'
1185 ,p_audit_log_flag => 'N'
1186 ,p_debug_messages_flag => 'N'
1187 ,p_object_version_number => l_object_version_number
1188 ,p_effective_date => l_effective_date
1189 ,p_request_id => l_parent_request_id
1190 ,p_program_application_id => fnd_global.prog_appl_id
1191 ,p_program_id => fnd_global.conc_program_id
1192 ,p_program_update_date => sysdate
1193 );
1194 --
1195 -- As of now the number of threads to be spawned is kept fixed to 9
1196 l_threads := 9;
1197 g_num_processes := 1;
1198 --
1199 for l_count in 2..l_threads
1200 loop
1201 --
1202 -- We start submitting threads from TWO as the main process will act as first thread.
1203 --
1204 hr_utility.set_location('Submitting request ' || l_count, 10);
1205 --
1206 l_request_id := fnd_request.submit_request
1207 (application => 'BEN'
1208 ,program => 'BEDEORTHRD'
1209 ,description => NULL
1210 ,sub_request => FALSE
1211 ,argument1 => l_parent_request_id
1212 ,argument2 => l_count);
1213 --
1214 -- Store the request id of the concurrent request
1215 --
1216 g_num_processes := g_num_processes + 1;
1217 g_request_tbl(l_count) := l_request_id;
1218 commit;
1219 --
1220 end loop;
1221 --
1222 -- Carry on with the master.
1223 --
1224 hr_utility.set_location('Submitting the master process', 10);
1225 --
1226 do_multithread
1227 (errbuf => errbuf
1228 ,retcode => retcode
1229 ,p_parent_request_id => l_parent_request_id
1230 ,p_thread_id => 1 );
1231 --
1232 -- Check if all the slave processes are finished.
1233 --
1234 check_all_slaves_finished(p_rpt_flag => TRUE);
1235 --
1236 hr_utility.set_location ('Leaving ' || l_proc, 10);
1237 --
1238 exception
1239 --
1240 when others then
1241 --
1242 null;
1243 --
1244 --
1245 end process;
1246 --
1247 end ben_delete_orphan_rows;