[Home] [Help]
PACKAGE BODY: APPS.BEN_FORFEITURE_CONCURRENT
Source
1 package body ben_forfeiture_concurrent as
2 /* $Header: benforfs.pkb 120.0 2005/05/28 09:01:45 appldev noship $ */
3 --
4 /* ============================================================================
5 * Name
6 * Process Forfeiture Concurrent Manager Processes for Contributions
7 *
8 * Purpose
9 * This package simply houses the concurrent manager and multi-thread
10 * processes for Contribution Forfeiture Calculation.
11 *
12 * History
13 * Date Who Version What?
14 * ------- --------- ------- --------------------------------------
15 * 14-Sep-01 pbodla 115.0 Created
16 * 29-Sep-01 pbodla 115.1 First cut working from concurrent
17 * program.
18 * 29-Sep-01 pbodla 115.2 Modified cursors to filter based on
19 * provided comp object parameters.
20 * 02-oct-01 pbodla 115.3 Added logic to write forfeiture data
21 * to ben_pl_frfs_val_f table.
22 * In Phase 2 use the actual api's.
23 * 03-Oct-01 pbodla 115.4 Added cursor c_abr_temp to get
24 * clf row for contribution abr.
25 * This is a temporary fix needs to be
26 * removed later.
27 * 10-Oct-01 pbodla 115.5 Added code for reporting.
28 * 18-Oct-01 pbodla 115.6 Added code to initialise the person
29 * forfeited totals.
30 * Added proc create_per_frftd_rt to
31 * prtt rt val row.
32 * 19-Oct-01 pbodla 115.7 Added code to initialise the
33 * contribution and distribution vals.
34 * 22-0ct-01 tjesumic 115.8 new if conditon added to check whether the
35 * the process is don after the last day of
36 * receipt allowed
37 * 23-oct-01 tjesumic 115.9 distribution detrmination code USERMBRQ
38 * Spelled USERMBQ, corrcted
39 * Nvl added in payment status code in cursor
40 * 25-oct-01 tjesumic 115.10 cursor calcualting reimb approve amount corrected
41 * 26-oct-01 tjesumic 115.11 Cursor c_asg is using the wrong person_id , fixed
42 * 07-may-01 tjesumic 115.12 'PDINFL','PRTLYPD' added
43 * 08-may-01 tjesumic 115.13 DBDRV added
44 * 30-dec-02 ikasire 115.14 nocopy changes
45 * 13-oct-04 mmudigon 115.15 Bug 3818453. Added call to
46 * get_latest_paa_id()
47 * 03-Dec-04 ikasire 115.16 Bug 4046914
48 * -----------------------------------------------------------------------------
49 */
50 --
51 -- Global cursor and variables declaration
52 --
53 g_package varchar2(80) := 'ben_forfeiture_concurrent';
54 g_persons_processed number(9) := 0;
55 g_persons_ended number(9) := 0;
56 g_persons_passed number(9) := 0;
57 g_persons_errored number(9) := 0;
58 g_max_errors_allowed number(9) := 200;
59 --
60 -- ============================================================================
61 -- << Procedure: create_per_frftd_rt >>
62 -- Description:
63 -- this procedure creates the participant rate val row for person
64 -- forfeited amount.
65 --
66 -- ============================================================================
67 --
68 procedure create_per_frftd_rt (
69 p_validate in varchar2 default 'N'
70 ,p_pl_id in number
71 ,p_pgm_id in number default null
72 ,p_business_group_id in number
73 ,p_effective_date in date
74 ,p_end_date in date
75 ,p_start_date in date
76 ,p_person_id in number default null
77 ,p_per_frftd_val in number
78 ) is
79 --
80 cursor c_pln
81 is
82 select pln.nip_acty_ref_perd_cd
83 ,pln.pl_id
84 from ben_pl_f pln
85 where pln.pl_id = p_pl_id
86 and p_effective_date
87 between pln.effective_start_date
88 and pln.effective_end_date;
89 --
90 Cursor c_rslt_rec (p_pl_id number ) is
91 select pen.pgm_id,
92 pen.per_in_ler_id,
93 pen.prtt_enrt_rslt_id
94 from ben_prtt_enrt_rslt_f pen
95 where pen.person_id = p_person_id
96 and pen.pl_id = p_pl_id
97 and pen.prtt_enrt_rslt_stat_cd is null
98 and pen.business_group_id = p_business_group_id
99 and p_effective_date between
100 pen.effective_start_date and pen.effective_end_date;
101 --
102 l_rslt_rec c_rslt_rec%rowtype;
103 --
104 cursor c_acty_base_rt (p_pl_id number)
105 is
106 select abr.acty_base_rt_id,
107 abr.rt_typ_cd,
108 abr.tx_typ_cd,
109 abr.acty_typ_cd,
110 abr.rt_mlt_cd,
111 abr.bnft_rt_typ_cd,
112 abr.dsply_on_enrt_flag,
113 abr.comp_lvl_fctr_id,
114 abr.actl_prem_id,
115 abr.input_value_id,
116 abr.element_type_id
117 from ben_acty_base_rt_f abr
118 where abr.pl_id = p_pl_id
119 and abr.acty_typ_cd = 'PRFRFS'
120 and abr.acty_base_rt_stat_cd = 'A'
121 and p_effective_date between
122 abr.effective_start_date and
123 abr.effective_end_date;
124 --
125 l_acty_base_rt c_acty_base_rt%rowtype;
126 --
127 cursor c_pgm
128 (c_pgm_id number)
129 is
130 select pgm.acty_ref_perd_cd
131 from ben_pgm_f pgm
132 where pgm.pgm_id = c_pgm_id
133 and p_effective_date
134 between pgm.effective_start_date
135 and pgm.effective_end_date;
136 --
137 cursor c_prv_rec (p_prtt_enrt_rslt_id number,
138 p_acty_base_rt_id number,
139 p_start_date date,
140 p_end_date date)
141 is
142 select prv.prtt_rt_val_id,
143 prv.object_version_number,
144 ecr.enrt_rt_id
145 from ben_acty_base_rt_f abr,
146 ben_prtt_rt_val prv,
147 ben_enrt_rt ecr
148 where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
149 and prv.prtt_rt_val_stat_cd is null
150 and prv.acty_base_rt_id = p_acty_base_rt_id
151 and ecr.prtt_rt_val_id = prv.prtt_rt_val_id
152 and prv.rt_strt_dt between p_start_date
153 and p_end_date
154 and prv.acty_base_rt_id = abr.acty_base_rt_id
155 and abr.acty_typ_cd = 'PRFRFS'
156 and p_start_date between
157 abr.effective_start_date and
158 abr.effective_end_date;
159 --
160 l_prv_rec c_prv_rec%rowtype;
161 --
162 -- Declare cursors and local variables
163 --
164 l_effective_start_date date;
165 l_prtt_rt_val_id number;
166 l_prtt_enrt_rslt_id number;
167 l_object_version_number number;
168 l_acty_ref_perd_cd varchar2(30);
169 l_pl_id number;
170 --
171 l_proc varchar2(72) := g_package||'.create_per_frftd_rt';
172 --
173 begin
174 --
175 -- Creating prtt rt val and element entry rows.
176 --
177 open c_pln;
178 fetch c_pln into
179 l_acty_ref_perd_cd,
180 l_pl_id ;
181 close c_pln;
182
183 open c_rslt_rec(p_pl_id);
184 fetch c_rslt_rec into l_rslt_rec;
185 close c_rslt_rec;
186
187 if l_rslt_rec.pgm_id is not null then
188 --
189 hr_utility.set_location('pgm '||l_rslt_rec.pgm_id,100);
190 open c_pgm (l_rslt_rec.pgm_id);
191 fetch c_pgm into l_acty_ref_perd_cd;
192 close c_pgm;
193 --
194 End if ;
195
196 open c_acty_base_rt(p_pl_id);
197 fetch c_acty_base_rt into l_acty_base_rt;
198 close c_acty_base_rt;
199 --
200 hr_utility.set_location(' l_prtt_rt_val_id = ' || l_prtt_rt_val_id, 9999);
201 hr_utility.set_location(' l_rslt_rec.per_in_ler_id = ' || l_rslt_rec.per_in_ler_id, 9999);
202 hr_utility.set_location(' l_acty_base_rt.rt_typ_cd = ' || l_acty_base_rt.rt_typ_cd, 9999);
203 hr_utility.set_location(' l_acty_base_rt.tx_typ_cd = ' || l_acty_base_rt.tx_typ_cd, 9999);
204 hr_utility.set_location(' l_acty_base_rt.acty_typ_cd = ' || l_acty_base_rt.acty_typ_cd, 9999);
205 hr_utility.set_location(' l_acty_base_rt.rt_mlt_cd = ' || l_acty_base_rt.rt_mlt_cd, 9999);
206 hr_utility.set_location(' l_acty_ref_perd_cd = ' || l_acty_ref_perd_cd, 9999);
207 hr_utility.set_location(' p_per_frftd_val = ' || p_per_frftd_val, 9999);
208 hr_utility.set_location(' p_end_date = ' || p_end_date, 9999);
209 hr_utility.set_location(' l_acty_base_rt.bnft_rt_typ_cd = ' || l_acty_base_rt.bnft_rt_typ_cd, 9999);
210 hr_utility.set_location(' l_acty_base_rt.comp_lvl_fctr_id = ' || l_acty_base_rt.comp_lvl_fctr_id, 9999);
211 hr_utility.set_location(' p_business_group_id = ' || p_business_group_id, 9999);
212 hr_utility.set_location(' l_object_version_number = ' || l_object_version_number, 9999);
213 hr_utility.set_location(' l_acty_base_rt.acty_base_rt_id = ' || l_acty_base_rt.acty_base_rt_id, 9999);
214 hr_utility.set_location(' p_person_id = ' || p_person_id, 9999);
215 hr_utility.set_location(' l_acty_base_rt.input_value_id = ' || l_acty_base_rt.input_value_id, 9999);
216 hr_utility.set_location(' l_acty_base_rt.element_type_id = ' || l_acty_base_rt.element_type_id, 9999);
217 hr_utility.set_location(' l_prtt_enrt_rslt_id = ' || l_rslt_rec.prtt_enrt_rslt_id, 9999);
218 hr_utility.set_location(' p_start_date = ' || p_start_date, 8888);
219 hr_utility.set_location(' p_end_date = ' || p_end_date, 8888);
220 --
221 -- if prtt_rt_val exists delete it by calling
222 --
223 l_prv_rec.prtt_rt_val_id := null;
224
225 open c_prv_rec(l_rslt_rec.prtt_enrt_rslt_id,
226 l_acty_base_rt.acty_base_rt_id,
227 p_start_date, p_end_date );
228 fetch c_prv_rec into l_prv_rec;
229 close c_prv_rec;
230 --
231 hr_utility.set_location(' l_prv_rec.prtt_rt_val_id = ' || l_prv_rec.prtt_rt_val_id, 9999);
232 hr_utility.set_location(' l_prv_rec.enrt_rt_id = ' || l_prv_rec.enrt_rt_id, 9999);
233 hr_utility.set_location(' l_prv_rec.obj = ' || l_prv_rec.object_version_number, 9999);
234 if l_prv_rec.prtt_rt_val_id is not null then
235 --
236 ben_prtt_rt_val_api.delete_prtt_rt_val
237 (p_validate => false
238 ,p_prtt_rt_val_id => l_prv_rec.prtt_rt_val_id
239 ,p_enrt_rt_id => l_prv_rec.enrt_rt_id
240 ,p_person_id => p_person_id
241 ,p_business_group_id => p_business_group_id
242 ,p_object_version_number => l_prv_rec.object_version_number
243 ,p_effective_date => p_end_date
244 );
245 --
246 end if;
247 --
248 --
249 -- Creating Prtt rate val row
250 --
251 if l_acty_base_rt.acty_base_rt_id is not null then
252 --
253 ben_prtt_rt_val_api.create_prtt_rt_val(
254 p_prtt_rt_val_id => l_prtt_rt_val_id
255 ,p_per_in_ler_id => l_rslt_rec.per_in_ler_id
256 ,p_rt_typ_cd => l_acty_base_rt.rt_typ_cd
257 ,p_tx_typ_cd => l_acty_base_rt.tx_typ_cd
258 ,p_acty_typ_cd => l_acty_base_rt.acty_typ_cd
259 ,p_mlt_cd => l_acty_base_rt.rt_mlt_cd
260 ,p_acty_ref_perd_cd => l_acty_ref_perd_cd
261 ,p_rt_val => p_per_frftd_val
262 ,p_rt_strt_dt => p_end_date
263 ,p_rt_end_dt => p_end_date
264 ,p_bnft_rt_typ_cd => l_acty_base_rt.bnft_rt_typ_cd
265 ,p_dsply_on_enrt_flag => 'N'
266 --l_acty_base_rt.dsply_on_enrt_flag
267 ,p_elctns_made_dt => p_end_date
268 ,p_cvg_amt_calc_mthd_id => null
269 ,p_actl_prem_id => null
270 ,p_comp_lvl_fctr_id => l_acty_base_rt.comp_lvl_fctr_id
271 ,p_business_group_id => p_business_group_id
272 ,p_object_version_number => l_object_version_number
273 ,p_effective_date => p_end_date
274 ,p_acty_base_rt_id => l_acty_base_rt.acty_base_rt_id
275 ,p_person_id => p_person_id
276 ,p_PRTT_REIMBMT_RQST_ID => null
277 ,p_prtt_rmt_aprvd_fr_pymt_id => null
278 ,p_input_value_id => l_acty_base_rt.input_value_id
279 ,p_element_type_id => l_acty_base_rt.element_type_id
280 ,p_prtt_enrt_rslt_id => l_rslt_rec.prtt_enrt_rslt_id
281 );
282 --
283 end if;
284 --
285 end create_per_frftd_rt;
286 --
287 -- ===========================================================================
288 -- << Procedure: Submit_all_reports >>
289 -- ===========================================================================
290 --
291 Procedure Submit_all_reports (p_rpt_flag Boolean default FALSE) is
292 l_proc varchar2(80) := g_package||'.submit_all_reports';
293 l_actn varchar2(80);
294 l_request_id number;
295 Begin
296 hr_utility.set_location ('Entering '||l_proc,05);
297 l_actn := 'Calling ben_batch_utils.batch_report (BENPRSUM)...';
298 ben_batch_utils.batch_report
299 (p_concurrent_request_id => fnd_global.conc_request_id
300 ,p_program_name => 'BENFRSUM'
301 ,p_request_id => l_request_id
302 );
303 l_actn := 'Calling ben_batch_utils.batch_report (BENFRAUD)...';
304 ben_batch_utils.batch_report
305 (p_concurrent_request_id => fnd_global.conc_request_id
306 ,p_program_name => 'BENFRAUD'
307 ,p_request_id => l_request_id
308 );
309 l_actn := 'Calling ben_batch_utils.batch_report (BENERTYP)...';
310 ben_batch_utils.batch_report
311 (p_concurrent_request_id => fnd_global.conc_request_id
312 ,p_program_name => 'BENERTYP'
313 ,p_request_id => l_request_id
314 );
315 l_actn := 'Calling ben_batch_utils.batch_report (BENERPER)...';
316 ben_batch_utils.batch_report
317 (p_concurrent_request_id => fnd_global.conc_request_id
318 ,p_program_name => 'BENERPER'
319 ,p_request_id => l_request_id
320 );
321
322 hr_utility.set_location ('Leaving '||l_proc,10);
323 Exception
324 When others then
325 ben_batch_utils.rpt_error(p_proc => l_proc
326 ,p_last_actn => l_actn
327 ,p_rpt_flag => p_rpt_flag
328 );
329 raise;
330 End Submit_all_reports;
331 --
332 -- ============================================================================
333 -- << Procedure: process_forfeitures >>
334 -- Description:
335 -- this procedure determines the forfeitures for the selected plan.
336 -- routine.
337 -- ============================================================================
338 procedure process_forfeitures (
339 p_validate in varchar2 default 'N'
340 ,p_pl_id in number
341 ,p_business_group_id in number
342 ,p_effective_date in date
343 ,p_person_id in number default null
344 ,p_person_type_id in number default null
345 ,p_person_selection_rule_id in number default null) is
346 --
347 l_package varchar2(80) := g_package||'.process_forfeitures';
348 l_error_text varchar2(200) := null;
349 --
350 cursor c_pl_subj_frfs (p_effective_date date) is
351 select pln.name, pln.FRFS_DISTR_MTHD_CD,
352 pln.FRFS_DISTR_MTHD_RL,
353 pln.FRFS_CNTR_DET_CD,
354 pln.FRFS_DISTR_DET_CD,
355 pln.COST_ALLOC_KEYFLEX_1_ID,
356 pln.COST_ALLOC_KEYFLEX_2_ID,
357 pln.POST_TO_GL_FLAG,
358 pln.FRFS_VAL_DET_CD,
359 pln.FRFS_MX_CRYFWD_VAL,
360 pln.FRFS_PORTION_DET_CD,
361 pln.BNDRY_PERD_CD,
362 pyp.Acpt_clm_rqsts_thru_dt,
363 yp.start_date,
364 yp.end_date
365 from ben_pl_f pln,
366 ben_popl_yr_perd pyp,
367 ben_yr_perd yp
368 where pln.pl_id = p_pl_id
369 and pln.pl_id = pyp.pl_id
370 and pln.pl_stat_cd = 'A'
371 and pyp.yr_perd_id = yp.yr_perd_id
372 and p_effective_date BETWEEN yp.start_date AND yp.end_date
373 and pyp.business_group_id = p_business_group_id
374 and yp.business_group_id = p_business_group_id
375 and p_effective_date between
376 pln.effective_start_date and pln.effective_end_date;
377 --
378 l_pl_subj_frfs c_pl_subj_frfs%rowtype;
379 --
380 cursor c_abr (p_effective_date date, p_acty_typ_cd varchar2) is
381 select /* abr.*,*/ clf.* -- 9999 Add the only colums required.
382 from ben_acty_base_rt_f abr,
383 ben_comp_lvl_fctr clf
384 where abr.acty_typ_cd like p_acty_typ_cd || '%'
385 -- p_acty_typ_cd is like PRD/PRC
386 and abr.pl_id = p_pl_id
387 and abr.acty_base_rt_stat_cd = 'A'
388 and abr.ttl_comp_lvl_fctr_id = clf.comp_lvl_fctr_id
389 and p_effective_date between
390 abr.effective_start_date and abr.effective_end_date;
391 --
392 cursor c_abr_temp (p_effective_date date, p_acty_typ_cd varchar2) is
393 select /* abr.*,*/ clf.* -- 9999 Add the only colums required.
394 from ben_acty_base_rt_f abr,
395 ben_comp_lvl_fctr clf
396 where abr.acty_typ_cd not like 'PRD%'
397 and abr.pl_id = p_pl_id
398 and abr.acty_base_rt_stat_cd = 'A'
399 and abr.ttl_comp_lvl_fctr_id = clf.comp_lvl_fctr_id
400 and p_effective_date between
401 abr.effective_start_date and abr.effective_end_date;
402 --
403 l_cntr_clf c_abr%rowtype;
404 l_distr_clf c_abr%rowtype;
405 --
406 -- Get all the persons who enrolled in this paln.
407 --
408 cursor c_persons (p_pl_id number, p_effective_date date,
409 p_start_date date, p_end_date date) is
410 select unique pen.person_id
411 /* pen.prtt_enrt_rslt_id, pen.person_id, pen.pl_id, pen.oipl_id,
412 pen.pgm_id, pen.pl_typ_id
413 */
414 from ben_prtt_enrt_rslt_f pen,
415 per_all_people_f per
416 where per.person_id = pen.person_id
417 and per.business_group_id = pen.business_group_id
418 and p_effective_date between per.effective_start_date
419 and per.effective_end_date
420 and pen.prtt_enrt_rslt_stat_cd is null
421 and pen.sspndd_flag = 'N'
422 and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
423 and ( (pen.enrt_cvg_thru_dt = hr_api.g_eot
424 and pen.effective_end_date = hr_api.g_eot
425 and pen.enrt_cvg_strt_dt < p_end_date
426 )
427 or(pen.enrt_cvg_thru_dt = hr_api.g_eot
428 and pen.effective_end_date between p_start_date and p_end_date
429 and pen.enrt_cvg_strt_dt < p_end_date
430 )
431 or(pen.enrt_cvg_thru_dt = hr_api.g_eot
432 and pen.effective_end_date between p_start_date and p_end_date
433 and pen.enrt_cvg_strt_dt between p_start_date and p_end_date
434 )
435 )
436 and pen.pl_id = p_pl_id
437 and pen.business_group_id = p_business_group_id
438 and (pen.person_id = p_person_id or p_person_id is null)
439 and (per.person_type_id = p_person_type_id or p_person_type_id is null) ;
440 --
441 cursor c_rmbrq_total (p_pl_id number, p_person_id number,
442 p_effective_date date,
443 p_start_date date, p_end_date date) is
444 select sum(nvl(pry.aprvd_fr_pymt_amt,0))
445 from ben_prtt_reimbmt_rqst_f prc,
446 ben_prtt_rmt_aprvd_fr_pymt_f pry
447 where prc.pl_id = p_pl_id
448 and prc.prtt_reimbmt_rqst_stat_cd in ( 'APPRVD','PDINFL','PRTLYPD')
449 and nvl(pry.pymt_stat_cd,' ') <> ('RMBPNDNG')
450 and p_effective_date between prc.effective_start_date
451 and prc.effective_end_date
452 and prc.submitter_person_id = p_person_id
453 and prc.business_group_id = p_business_group_id
454 and prc.incrd_from_dt between p_start_date and p_end_date
455 -- is it clms thru dt
456 and prc.incrd_to_dt between p_start_date and p_end_date
457 -- 9999 what is p_end_date
458 and prc.prtt_reimbmt_rqst_id = pry.prtt_reimbmt_rqst_id
459 and p_effective_date between pry.effective_start_date
460 and pry.effective_end_date;
461 --
462 cursor c_bnft_bal(p_bnfts_bal_id number, p_person_id number) is
463 select bnb.val
464 from ben_per_bnfts_bal_f bnb
465 where bnb.bnfts_bal_id = p_bnfts_bal_id
466 and bnb.person_id = p_person_id
467 and bnb.business_group_id = p_business_group_id
468 and p_effective_date
469 between bnb.effective_start_date
470 and bnb.effective_end_date;
471 --
472 cursor c_asg(p_assignment_type varchar2,p_person_id number ) is
473 select paf.assignment_id
474 from per_all_assignments_f paf
475 where paf.person_id = p_person_id
476 and paf.business_group_id = p_business_group_id
477 and paf.primary_flag = 'Y'
478 and paf.assignment_type = p_assignment_type
479 and p_effective_date
480 between paf.effective_start_date
481 and paf.effective_end_date;
482 --
483 l_ass_rec per_all_assignments_f%ROWTYPE;
484 l_assignment_id number;
485 --
486 l_per_cntr_val number;
487 l_per_distr_val number;
488 l_per_frfd_val number;
489 l_tot_pl_cntr_val number;
490 l_tot_pl_distr_val number;
491 l_total_frfd_val number;
492 l_assignment_action_id number;
493 l_start_date date;
494 l_end_date date;
495 l_per_rec per_all_people_f%rowtype;
496 l_err_message varchar2(1000) ;
497 --
498 begin
499 --
500 hr_utility.set_location ('Entering '||l_package,10);
501 --
502 savepoint process_forfeitures;
503
504 hr_utility.set_location ('process pl_id : '||to_char(p_pl_id),10);
505 --
506 open c_pl_subj_frfs(p_effective_date);
507 fetch c_pl_subj_frfs into l_pl_subj_frfs;
508 close c_pl_subj_frfs;
509 benutils.write(' Plan : ' || l_pl_subj_frfs.name);
510 if sysdate > l_pl_subj_frfs.acpt_clm_rqsts_thru_dt or p_validate = 'Y' then
511 --
512 l_start_date := l_pl_subj_frfs.start_date;
513 l_end_date := l_pl_subj_frfs.end_date;
514 --
515 hr_utility.set_location ('process l_start_date : '||to_char(l_start_date),15);
516 hr_utility.set_location ('process l_end_date : '||to_char(l_end_date),15);
517 hr_utility.set_location ('p_effective_date : '||to_char(p_effective_date),15);
518 --
519 open c_abr(p_effective_date, 'PRC');
520 fetch c_abr into l_cntr_clf;
521 close c_abr;
522 --
523 -- This is temporary fix till the new lookups for ben_acty_typ_cd
524 -- are added - PRCPR, PRCPPR, PRCPER
525 --
526 if l_cntr_clf.comp_lvl_fctr_id is null then
527 --
528 open c_abr_temp(p_effective_date, 'PRD');
529 fetch c_abr_temp into l_cntr_clf;
530 close c_abr_temp;
531 --
532 end if;
533 -- ???? 99999 ERROR if not found what to do?
534 hr_utility.set_location ('Cntr Comp level factor id = '
535 || l_cntr_clf.comp_lvl_fctr_id, 20);
536 hr_utility.set_location ('Cntr Comp level name id = ' || l_cntr_clf.name, 22);
537 open c_abr(p_effective_date, 'PRD');
538 fetch c_abr into l_distr_clf;
539 close c_abr;
540 hr_utility.set_location ('Distr Comp level factor id = '
541 || l_distr_clf.comp_lvl_fctr_id, 20);
542 hr_utility.set_location ('distr Comp level name id = ' || l_distr_clf.name, 22);
543 --
544 -- ???? 99999 ERROR if not found what to do?
545 --
546 --
547 -- Loop through the results matching pl_id and person_id
548 --
549 for l_person_rec in c_persons(p_pl_id, p_effective_date,
550 l_start_date, l_end_date)
551 loop
552 --
553 -- Initialise the totals.
554 --
555 l_per_cntr_val := 0;
556 l_per_distr_val := 0;
557 l_per_frfd_val := 0;
558 --
559 hr_utility.set_location ('process person_id : '||
560 to_char(l_person_rec.person_id),30);
561 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
562 fnd_message.set_token('PROC','ben_person_object');
563 ben_person_object.get_object(p_person_id => l_person_rec.person_id,
564 p_rec => l_per_rec);
565
566 hr_utility.set_location ('process det cd ' || l_pl_subj_frfs.frfs_cntr_det_cd,30) ;
567 hr_utility.set_location ('process src cd ' || l_cntr_clf.comp_src_cd,30) ;
568
569 if l_pl_subj_frfs.frfs_cntr_det_cd = 'USECLF' then
570 --
571 if l_cntr_clf.comp_src_cd = 'BNFTBALTYP' THEN
572 --
573 -- Get the persons balance
574 --
575 open c_bnft_bal(l_cntr_clf.bnfts_bal_id, l_person_rec.person_id);
576 fetch c_bnft_bal into l_per_cntr_val;
577 close c_bnft_bal;
578 --
579 elsif l_cntr_clf.comp_src_cd = 'BALTYP' THEN
580 --
581 -- Get the persons balance
582 --
583 l_assignment_id := null;
584 open c_asg('E',l_person_rec.person_id );
585 fetch c_asg into l_assignment_id;
586 close c_asg;
587 IF l_assignment_id IS NULL THEN
588 --
589 hr_utility.set_location (' employee failed ' || l_assignment_id , 30) ;
590 open c_asg('B',l_person_rec.person_id);
591 fetch c_asg into l_assignment_id;
592 close c_asg;
593 --
594 -- 9999 Error out if assignment is not found for person.
595 --
596 END IF;
597 --
598 hr_utility.set_location (' assignent ' || l_assignment_id , 30) ;
599
600 ben_derive_part_and_rate_facts.set_taxunit_context
601 (p_person_id => l_person_rec.person_id
602 ,p_business_group_id => p_business_group_id
603 ,p_effective_date => p_effective_date
604 ) ;
605 --
606 -- Bug 3818453. Pass assignment_action_id to get_value() to
607 -- improve performance
608 --
609 l_assignment_action_id :=
610 ben_derive_part_and_rate_facts.get_latest_paa_id
611 (p_person_id => l_person_rec.person_id
612 ,p_business_group_id => p_business_group_id
613 ,p_effective_date => p_effective_date);
614
615 if l_assignment_action_id is not null then
616 --
617 begin
618 l_per_cntr_val :=
619 pay_balance_pkg.get_value(l_cntr_clf.defined_balance_id
620 ,l_assignment_action_id);
621 exception
622 when others then
623 l_per_cntr_val := null ;
624 end ;
625 --
626 --
627 end if ;
628
629 --
630 -- old code prior to 3818453
631 --
632 /*
633 l_per_cntr_val :=
634 pay_balance_pkg.get_value(l_cntr_clf.defined_balance_id
635 ,l_assignment_id
636 ,p_effective_date); -- 9999 should it be based on comp_lvl_det_cd
637 */
638 hr_utility.set_location (' value of defined ' || l_per_cntr_val , 30) ;
639 --
640 end if;
641 --
642 IF l_cntr_clf.rndg_cd IS NOT NULL
643 OR l_cntr_clf.rndg_rl IS NOT NULL THEN
644 --
645 l_per_cntr_val :=
646 benutils.do_rounding(p_rounding_cd=> l_cntr_clf.rndg_cd
647 ,p_rounding_rl => l_cntr_clf.rndg_rl
648 ,p_value => nvl(l_per_cntr_val, 0)
649 ,p_effective_date => p_effective_date);
650 --
651 END IF;
652 --
653 end if;
654 --
655 -- Compute the total distribution for the person.
656 --
657
658 if l_pl_subj_frfs.frfs_distr_det_cd = 'USECLF' then
659 --
660 if l_distr_clf.comp_src_cd = 'BNFTBALTYP' THEN
661 --
662 -- Get the persons balance
663 --
664 open c_bnft_bal(l_distr_clf.bnfts_bal_id, l_person_rec.person_id);
665 fetch c_bnft_bal into l_per_distr_val;
666 close c_bnft_bal;
667 --
668 elsif l_distr_clf.comp_src_cd = 'BALTYP' THEN
669 --
670 -- Get the persons balance
671 --
672 l_assignment_id := null;
673 --
674 open c_asg('E', l_person_rec.person_id);
675 fetch c_asg into l_assignment_id;
676 close c_asg;
677 IF l_assignment_id IS NULL THEN
678 --
679 open c_asg('B', l_person_rec.person_id);
680 fetch c_asg into l_assignment_id;
681 close c_asg;
682 --
683 -- 9999 Error out if assignment is not found for person.
684 --
685 END IF;
686
687 ben_derive_part_and_rate_facts.set_taxunit_context
688 (p_person_id => l_person_rec.person_id
689 ,p_business_group_id => p_business_group_id
690 ,p_effective_date => p_effective_date
691 ) ;
692 --
693 -- Bug 3818453. Pass assignment_action_id to get_value() to
694 -- improve performance
695 --
696 l_assignment_action_id :=
697 ben_derive_part_and_rate_facts.get_latest_paa_id
698 (p_person_id => l_person_rec.person_id
699 ,p_business_group_id => p_business_group_id
700 ,p_effective_date => p_effective_date);
701
702 if l_assignment_action_id is not null then
703 --
704 begin
705 l_per_distr_val :=
706 pay_balance_pkg.get_value(l_distr_clf.defined_balance_id
707 ,l_assignment_action_id);
708 exception
709 when others then
710 l_per_distr_val := null ;
711 end ;
712 --
713 --
714 end if ;
715
716 --
717 -- old code prior to 3818453
718 --
719 /*
720 l_per_distr_val :=
721 pay_balance_pkg.get_value(l_distr_clf.defined_balance_id
722 ,l_assignment_id
723 ,p_effective_date); -- 9999 should it be based on comp_lvl_det_cd
724 */
725 --
726 end if;
727 --
728 IF l_distr_clf.rndg_cd IS NOT NULL
729 OR l_distr_clf.rndg_rl IS NOT NULL THEN
730 --
731 l_per_distr_val :=
732 benutils.do_rounding(p_rounding_cd=> l_distr_clf.rndg_cd
733 ,p_rounding_rl => l_distr_clf.rndg_rl
734 ,p_value => nvl(l_per_distr_val, 0)
735 ,p_effective_date => p_effective_date);
736 --
737 END IF;
738 --
739 elsif l_pl_subj_frfs.frfs_distr_det_cd = 'USERMBRQ' then
740 --
741 open c_rmbrq_total (p_pl_id, l_person_rec.person_id,
742 p_effective_date,
743 l_start_date, l_end_date );
744 fetch c_rmbrq_total into l_per_distr_val;
745 close c_rmbrq_total;
746 hr_utility.set_location(' in USERMBQ ' , 99);
747 hr_utility.set_location(' USERMBQ value '|| l_per_distr_val , 99);
748 --
749 --
750 end if;
751 --
752 hr_utility.set_location(' det cd '|| l_pl_subj_frfs.frfs_distr_det_cd , 99);
753 hr_utility.set_location(' pl ' || p_pl_id , 99);
754 hr_utility.set_location(' person ' || l_person_rec.person_id , 99 );
755 hr_utility.set_location(' start date ' || l_start_date , 99 );
756 hr_utility.set_location(' end date ' || l_end_date , 99 );
757 hr_utility.set_location(' effective date ' || p_effective_date , 99);
758
759
760 l_per_frfd_val := nvl(l_per_cntr_val, 0) - nvl(l_per_distr_val, 0) ;
761 benutils.write(' Name : ' || l_per_rec.full_name);
762 benutils.write(' Total Contributed = ' || to_char(nvl(l_per_cntr_val, 0)) );
763 benutils.write(' Total Distributed = ' || to_char(nvl(l_per_distr_val, 0)) );
764 benutils.write(' Total Forfeited = ' || to_char(nvl(l_per_frfd_val, 0)) );
765 --
766 hr_utility.set_location(' Name : ' || l_per_rec.full_name, 9999);
767 hr_utility.set_location(' Total Contributed = '
768 || to_char(l_per_cntr_val) , 9999);
769 hr_utility.set_location(' Total Distributed = '
770 || to_char(l_per_distr_val) , 9999);
771 hr_utility.set_location(' Total Forfeited = '
772 || to_char(l_per_frfd_val) , 9999);
773 -- 9999 if l_per_frfd_val is negative what to do?
774 l_tot_pl_cntr_val := nvl(l_tot_pl_cntr_val, 0) + nvl(l_per_cntr_val, 0);
775 l_tot_pl_distr_val := nvl(l_tot_pl_distr_val, 0) + nvl(l_per_distr_val, 0);
776 l_total_frfd_val := nvl(l_total_frfd_val, 0) + nvl(l_per_frfd_val, 0);
777 --
778 -- Create the participant rate val row.
779 --
780 if l_per_frfd_val >= 0 then
781 --
782 create_per_frftd_rt (
783 p_validate => p_validate
784 ,p_pl_id => p_pl_id
785 ,p_pgm_id => null
786 ,p_business_group_id => p_business_group_id
787 ,p_effective_date => p_effective_date
788 ,p_end_date => l_end_date
789 ,p_start_date => l_start_date
790 ,p_person_id => l_person_rec.person_id
791 ,p_per_frftd_val => l_per_frfd_val);
792 --
793 end if;
794 --
795 -- write forfeiture for person info to reporting table
796 --
797
798 g_rec.rep_typ_cd := 'FRPERPLVAL';
799 g_rec.person_id := l_person_rec.person_id;
800 g_rec.pgm_id := null; -- l_each_result.pgm_id;
801 g_rec.pl_id := p_pl_id;
802 g_rec.oipl_id := null;
803 g_rec.pl_typ_id := null;
804 g_rec.val := nvl(l_per_frfd_val, 0);
805 g_rec.ler_id := nvl(l_per_cntr_val, 0);
806 g_rec.related_person_id := nvl(l_per_distr_val, 0);
807 benutils.write(p_rec => g_rec);
808 end loop;
809
810 benutils.write(' Plan : ' || l_pl_subj_frfs.name);
811 benutils.write(' Total Contributed = ' || to_char(l_tot_pl_cntr_val) );
812 benutils.write(' Total Distributed = ' || to_char(l_tot_pl_distr_val) );
813 benutils.write(' Total Forfeited = ' || to_char(l_total_frfd_val) );
814 --
815 hr_utility.set_location(' Total Contributed = ' || to_char(l_tot_pl_cntr_val) , 9999);
816 hr_utility.set_location(' Total Distributed = ' || to_char(l_tot_pl_distr_val) , 9999);
817 hr_utility.set_location(' Total Forfeited = ' || to_char(l_total_frfd_val) , 9999);
818 --
819 -- write forfeiture for plan info to reporting table
820 --
821 g_rec.rep_typ_cd := 'FRPLVAL';
822 g_rec.person_id := null;
823 g_rec.pgm_id := null; -- ??l_pgm_id;
824 g_rec.pl_id := p_pl_id;
825 g_rec.oipl_id := null;
826 g_rec.pl_typ_id := null;
827 g_rec.val := l_total_frfd_val;
828 g_rec.ler_id := nvl(l_tot_pl_cntr_val, 0);
829 g_rec.related_person_id := nvl(l_tot_pl_distr_val, 0);
830
831 benutils.write(p_rec => g_rec);
832 --
833 if l_pl_subj_frfs.frfs_distr_mthd_cd = 'PRVDR' then
834 --
835 -- Write to table ben_pl_frfs_val_f
836 -- Check whether there exists any row already for
837 -- l_start_date, l_end_date and with final_clms_thru_dt.
838 -- If exists update it else create one.
839 -- Use the actual api's once the api's are generated.
840 --
841 delete from ben_pl_frfs_val_f
842 where pl_id = p_pl_id
843 and business_group_id = p_business_group_id
844 and start_date = l_start_date
845 and end_date = l_end_date
846 ;
847 --
848 insert into BEN_PL_FRFS_VAL_F
849 (PL_FRFS_VAL_ID
850 ,EFFECTIVE_START_DATE
851 ,EFFECTIVE_END_DATE
852 ,BUSINESS_GROUP_ID
853 ,PL_ID
854 ,start_date
855 ,end_date
856 ,ttl_cntr_val
857 ,ttl_distr_val
858 ,ttl_frfd_val
859 ,COST_ALLOC_KEYFLEX_1_ID
860 ,COST_ALLOC_KEYFLEX_2_ID
861 ,OBJECT_VERSION_NUMBER
862 ) values
863 (
864 ben_pl_frfs_val_f_s.nextval,
865 p_effective_date,
866 hr_api.g_eot,
867 p_business_group_id,
868 p_pl_id,
869 l_start_date,
870 l_end_date,
871 l_tot_pl_cntr_val,
872 l_tot_pl_distr_val,
873 l_total_frfd_val,
874 l_pl_subj_frfs.COST_ALLOC_KEYFLEX_1_ID,
875 l_pl_subj_frfs.COST_ALLOC_KEYFLEX_2_ID,
876 1
877 );
878 --
879 end if;
880 --
881 Else
882
883 fnd_message.set_name('BEN', 'BEN_92774_FORFEITURE_DATE');
884 fnd_message.set_token('PLAN', l_pl_subj_frfs.name);
885 l_err_message := fnd_message.get ;
886 benutils.write(' Error : ' || l_err_message);
887 end if ;
888 -- sysdate > l_pl_subj_frfs.acpt_clm_rqsts_thru_dt or p_validate = 'Y'
889 if (p_validate = 'Y') then
890 --
891 rollback to process_forfeitures;
892 --
893 end if;
894 --
895 hr_utility.set_location ('Leaving '||l_package,500);
896 --
897 exception
898 --
899 when others then
900 l_error_text := sqlerrm;
901 fnd_message.raise_error;
902 --
903 end process_forfeitures;
904 --
905 --
906 -- ============================================================================
907 -- << Procedure: Do_Multithread >>
908 -- Description:
909 -- this procedure is called from 'process'. It calls the calc_forfeiture
910 -- routine.
911 -- ============================================================================
912 procedure do_multithread
913 (errbuf out nocopy varchar2
914 ,retcode out nocopy number
915 ,p_benefit_action_id in number
916 ,p_effective_date in varchar2
917 ,p_validate in varchar2 default 'N'
918 ,p_business_group_id in number
919 ,p_thread_id in number
920 -- ,p_organization_id in number default null
921 -- ,p_frfs_perd_det_cd in varchar2 default null
922 -- ,p_person_id in number default null
923 -- For Future Enhancement.
924 -- ,p_person_type_id in number default null
925 -- For Future Enhancement.
926 -- ,p_pgm_id in number default null
927 -- ,p_pl_typ_id in number default null
928 -- ,p_pl_id in number default null
929 -- ,p_comp_selection_rule_id in number default null
930 -- ,p_person_selection_rule_id in number default null
931 -- For Future Enhancement.
932 -- ,p_debug_messages in varchar2 default 'N'
933 -- ,p_audit_log_flag in varchar2 default 'N'
934 -- ,p_commit_data_flag in varchar2 default 'Y'
935 ) is
936 --
937 -- Local variable declaration
938 -- 9999 Check all the local variables and delete them if not used after arcsin.
939 --
940 l_proc varchar2(80) := g_package||'.do_multithread';
941 l_person_id ben_person_actions.person_id%type;
942 l_person_action_id ben_person_actions.person_action_id%type;
943 l_object_version_number ben_person_actions.object_version_number%type;
944 l_ler_id ben_person_actions.ler_id%type;
945 l_range_id ben_batch_ranges.range_id%type;
946 l_record_number number := 0;
947 l_start_person_action_id number := 0;
948 l_end_person_action_id number := 0;
949 l_actn varchar2(80);
950 l_cnt number(5):= 0;
951 l_chunk_size number(15);
952 l_threads number(15);
953 l_effective_date date;
954 --
955 -- Cursors declaration
956 --
957 Cursor c_range_thread is
958 Select ran.range_id
959 ,ran.starting_person_action_id
960 ,ran.ending_person_action_id
961 From ben_batch_ranges ran
962 Where ran.range_status_cd = 'U'
963 And ran.BENEFIT_ACTION_ID = P_BENEFIT_ACTION_ID
964 And rownum < 2
965 For update of ran.range_status_cd
966 ;
967 Cursor c_person_thread is
968 Select ben.person_id
969 ,ben.person_action_id
970 ,ben.object_version_number
971 ,ben.ler_id
972 From ben_person_actions ben
973 Where ben.benefit_action_id = p_benefit_action_id
974 And ben.action_status_cd <> 'P'
975 And ben.person_action_id between
976 l_start_person_action_id and l_end_person_action_id
977 Order by ben.person_action_id
978 ;
979 Cursor c_parameter is
980 Select *
981 From ben_benefit_actions ben
982 Where ben.benefit_action_id = p_benefit_action_id
983 ;
984 l_parm c_parameter%rowtype;
985 l_ovn number := null;
986 l_commit number;
987 --
988 Begin
989 --
990 hr_utility.set_location ('Entering '||l_proc,5);
991 --
992 /*
993 l_effective_date:=to_date(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
994 l_effective_date:=to_date(to_char(trunc(l_effective_date),'DD/MM/RRRR'),'DD/MM/RRRR');
995 */
996 --
997 l_effective_date := trunc(fnd_date.canonical_to_date(p_effective_date));
998 --
999 -- Put row in fnd_sessions
1000 --
1001 dt_fndate.change_ses_date
1002 (p_ses_date => l_effective_date,
1003 p_commit => l_commit);
1004 --
1005 l_actn := 'Calling benutils.get_parameter...';
1006 --
1007 benutils.get_parameter(p_business_group_id => p_business_group_Id
1008 ,p_batch_exe_cd => 'BENFRCON'
1009 ,p_threads => l_threads
1010 ,p_chunk_size => l_chunk_size
1011 ,p_max_errors => g_max_errors_allowed);
1012 --
1013 -- Set up benefits environment
1014 --
1015 ben_env_object.init(p_business_group_id => p_business_group_id,
1016 p_effective_date => l_effective_date,
1017 p_thread_id => p_thread_id,
1018 p_chunk_size => l_chunk_size,
1019 p_threads => l_threads,
1020 p_max_errors => g_max_errors_allowed,
1021 p_benefit_action_id => p_benefit_action_id);
1022 --
1023 l_actn := 'Calling ben_batch_utils.ini...';
1024 ben_batch_utils.ini; -- deletes g_cache_person, g_cache_comp, g_pgm_tbl etc.,
1025 --
1026 -- Copy benefit action id to global in benutils package
1027 --
1028 benutils.g_benefit_action_id := p_benefit_action_id;
1029 benutils.g_thread_id := p_thread_id;
1030 g_persons_errored := 0;
1031 g_persons_processed := 0;
1032 open c_parameter;
1033 fetch c_parameter into l_parm;
1034 close c_parameter;
1035 --
1036 l_actn := 'Calling ben_batch_utils.print_parameters...';
1037 --
1038 -- 9999 It calls the write routine in benrptut, in turn calls fnd_file.put_line
1039 ben_batch_utils.print_parameters
1040 (p_thread_id => p_thread_id
1041 ,p_benefit_action_id => p_benefit_action_id
1042 ,p_effective_date => l_effective_date
1043 ,p_validate => p_validate
1044 ,p_business_group_id => l_parm.business_group_id
1045 -- ,p_frfs_perd_det_cd => l_parm.frfs_perd_det_cd -- 9999 l_parm needs to be changed.
1046 -- If it comes from l_parm then delete it as parameter.
1047 -- How to store it as param.
1048 ,p_person_id => l_parm.person_id
1049 ,p_person_type_id => l_parm.person_type_id
1050 ,p_person_selection_rule_id => null -- 9999 what to send l_parm.person_selection_rule_id
1051 ,p_comp_selection_rule_id => l_parm.comp_selection_rl
1052 ,p_pgm_id => l_parm.pgm_id
1053 ,p_pl_typ_id => l_parm.pl_typ_id
1054 ,p_pl_id => l_parm.pl_id
1055 ,p_organization_id => l_parm.organization_id -- 9999 l_parm needs to be changed.
1056 -- If it comes from l_parm then delete it as parameter.
1057 ,p_ler_id => null
1058 ,p_benfts_grp_id => null
1059 ,p_location_id => null
1060 ,p_legal_entity_id => null
1061 ,p_payroll_id => null
1062 );
1063 --
1064 -- While loop to only try and fetch records while they exist
1065 -- we always try and fetch the size of the chunk, if we get less
1066 -- then we know that the process is finished so we end the while loop.
1067 -- The process is as follows :
1068 -- 1) Lock the rows that are not processed
1069 -- 2) Grab as many rows as we can upto the chunk size
1070 -- 3) Put each row into the person cache.
1071 -- 4) Process the person cache
1072 -- 5) Go to number 1 again.
1073 --
1074 hr_utility.set_location('About to Loop for c_range_thread',38);
1075
1076 Loop
1077 l_actn := 'Opening c_range thread and fetch range...';
1078 open c_range_thread;
1079 fetch c_range_thread into l_range_id
1080 ,l_start_person_action_id
1081 ,l_end_person_action_id;
1082 exit when c_range_thread%notfound;
1083 close c_range_thread;
1084 If(l_range_id is not NULL) then
1085 --
1086 l_actn := 'Updating ben_batch_ranges row...';
1087 --
1088 update ben_batch_ranges ran set ran.range_status_cd = 'P'
1089 where ran.range_id = l_range_id;
1090 commit;
1091 End if;
1092 --
1093 -- Remove all records from cache
1094 --
1095
1096 -- 9999 ?? why is the cache used here, it's not saving any processing time
1097 --
1098 l_actn := 'Clearing g_cache_person_process cache...';
1099 g_cache_person_process.delete;
1100 open c_person_thread;
1101 l_record_number := 0;
1102 hr_utility.set_location('about to loop for c_person_thread',46);
1103 Loop
1104 --
1105 l_actn := 'Loading Plans data into g_cache_person_process cache...';
1106 --
1107 fetch c_person_thread
1108 into g_cache_person_process(l_record_number+1).person_id
1109 ,g_cache_person_process(l_record_number+1).person_action_id
1110 ,g_cache_person_process(l_record_number+1).object_version_number
1111 ,g_cache_person_process(l_record_number+1).ler_id;
1112 exit when c_person_thread%notfound;
1113 l_record_number := l_record_number + 1;
1114 End loop;
1115
1116 close c_person_thread;
1117
1118 l_actn := 'Preparing to default each plan/participant from cache...' ;
1119 If l_record_number > 0 then
1120 --
1121 -- Process the rows from the person process cache (This is plan cache)
1122 --
1123 hr_utility.set_location('about to Loop thru forfeiture....',50);
1124 For l_cnt in 1..l_record_number loop
1125 Begin
1126 --
1127 process_forfeitures (
1128 p_validate => p_validate
1129 ,p_pl_id => g_cache_person_process(l_cnt).person_id
1130 ,p_business_group_id => p_business_group_id
1131 ,p_effective_date => l_effective_date
1132 ,p_person_id => l_parm.person_id
1133 ,p_person_type_id => l_parm.person_type_id
1134 ,p_person_selection_rule_id => null); -- l_parm.person_selection_rule_id);
1135 --
1136 g_persons_processed := g_persons_processed + 1;
1137 --
1138 -- If we get here it was successful.
1139 --
1140 update ben_person_actions
1141 set action_status_cd = 'P'
1142 where person_id = g_cache_person_process(l_cnt).person_id
1143 and benefit_action_id = p_benefit_action_id;
1144 --
1145 Exception
1146 When others then
1147 g_persons_errored := g_persons_errored + 1;
1148 --
1149 -- Need to write to reporting tables as well
1150 -- by calling benutils.write(p_rec => g_rec);
1151 --
1152 update ben_person_actions
1153 set action_status_cd = 'E'
1154 where person_id = g_cache_person_process(l_cnt).person_id
1155 and benefit_action_id = p_benefit_action_id;
1156 --
1157 commit;
1158 --
1159 If (g_persons_errored > g_max_errors_allowed) then
1160 hr_utility.set_location ('Errors received exceeds max allowed',05);
1161 fnd_message.raise_error;
1162 End if;
1163 End;
1164 End loop;
1165 Else
1166 --
1167 l_actn := 'Erroring out nocopy since no plan/person is found in range...' ;
1168 hr_utility.set_location ('BEN_92452_PREM_NOT_IN_RNG',05); -- 999
1169 fnd_message.set_name('BEN','BEN_92452_PREM_NOT_IN_RNG');
1170 fnd_message.set_token('PROC', l_proc);
1171 fnd_message.raise_error;
1172 End if;
1173
1174 -- 9999 Write only if requested by the user.
1175 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
1176 End loop;
1177
1178 hr_utility.set_location('End of loops',70);
1179 -- 9999 Write only if requested by the user.
1180 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
1181 --
1182 l_actn := 'Calling Log_statistics...';
1183 ben_batch_utils.write_logfile(p_num_pers_processed => g_persons_processed
1184 ,p_num_pers_errored => g_persons_errored
1185 );
1186 hr_utility.set_location ('Leaving '||l_proc,70);
1187 Exception
1188 When others then
1189 ben_batch_utils.rpt_error(p_proc => l_proc
1190 ,p_last_actn => l_actn
1191 ,p_rpt_flag => TRUE
1192 );
1193 ben_batch_utils.write_logfile(p_num_pers_processed => g_persons_processed
1194 ,p_num_pers_errored => g_persons_errored
1195 );
1196 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
1197 hr_utility.set_location ('HR_6153_ALL_PROCEDURE_FAIL',05);
1198 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1199 fnd_message.set_token('PROCEDURE', l_proc);
1200 fnd_message.set_token('STEP',l_actn );
1201 fnd_message.raise_error;
1202 --
1203 End do_multithread;
1204 -- *************************************************************************
1205 -- * << Procedure: Process >>
1206 -- *************************************************************************
1207 -- This is what is called from the concurrent manager screen
1208 --
1209 procedure process_by_plan(errbuf out nocopy varchar2
1210 ,retcode out nocopy number
1211 ,p_benefit_action_id in number
1212 ,p_effective_date in varchar2
1213 ,p_validate in varchar2 default 'N'
1214 ,p_business_group_id in number
1215 ,p_organization_id in number default null
1216 ,p_frfs_perd_det_cd in varchar2 default null
1217 ,p_person_id in number default null -- For Future Enhancement.
1218 ,p_person_type_id in number default null -- For Future Enhancement.
1219 ,p_pgm_id in number default null
1220 ,p_pl_typ_id in number default null
1221 ,p_pl_id in number default null
1222 ,p_comp_selection_rule_id in number default null
1223 ,p_person_selection_rule_id in number default null -- For Future Enhancement.
1224 ,p_debug_messages in varchar2 default 'N'
1225 ,p_audit_log_flag in varchar2 default 'N'
1226 ,p_commit_data_flag in varchar2 default 'Y'
1227 ,p_threads in number
1228 ,p_chunk_size in number
1229 ,p_max_errors in number
1230 ,p_restart in boolean default FALSE ) is
1231 --
1232 -- Cursors declaration.
1233 --
1234
1235 -- Plans subjected to forfeiture to be processed:
1236 cursor c_pl_subj_frfs (p_effective_date date) is
1237 select pln.*
1238 from ben_pl_f pln
1239 where pln.business_group_id = p_business_group_id
1240 and pln.frfs_aply_flag = 'Y'
1241 and pln.pl_stat_cd = 'A'
1242 and pln.pl_id = NVL(p_pl_id, pln.pl_id)
1243 and pln.pl_typ_id = NVL(p_pl_typ_id, pln.pl_typ_id)
1244 and p_effective_date between
1245 pln.effective_start_date and pln.effective_end_date
1246 and (p_pgm_id is null
1247 OR EXISTS
1248 (SELECT NULL
1249 FROM ben_plip_f cpp
1250 WHERE cpp.pl_id = pln.pl_id
1251 -- AND cpp.pgm_id = NVL(p_pgm_id, cpp.pgm_id)
1252 AND cpp.pgm_id = p_pgm_id
1253 AND cpp.business_group_id = pln.business_group_id
1254 AND cpp.plip_stat_cd = 'A'
1255 AND p_effective_date BETWEEN cpp.effective_start_date
1256 AND cpp.effective_end_date));
1257 l_pl_subj_frfs c_pl_subj_frfs%rowtype;
1258
1259 l_pl_typ_id number ;
1260 l_pl_id number ;
1261 l_opt_id number ; -- 9999 it is not necessary remove it later
1262 l_pgm_id number ;
1263 --
1264 -- local variable declaration.
1265 --
1266 l_effective_date date;
1267 l_request_id number;
1268 l_proc varchar2(80) := g_package||'.process_by_plan';
1269 l_benefit_action_id ben_benefit_actions.benefit_action_id%type;
1270 l_object_version_number ben_benefit_actions.object_version_number%type;
1271 l_person_action_id ben_person_actions.person_action_id%type;
1272 l_ler_id ben_ler_f.ler_id%type;
1273 l_range_id ben_batch_ranges.range_id%type;
1274 l_start_person_action_id number := 0;
1275 l_end_person_action_id number := 0;
1276 l_prev_person_id number := 0;
1277 -- 9999 make them start with l_
1278 rl_ret char(1);
1279 skip boolean;
1280 l_person_cnt number := 0;
1281 l_cnt number := 0;
1282 l_actn varchar2(80);
1283 l_num_range number := 0;
1284 l_chunk_num number := 1;
1285 l_num_row number := 0;
1286 l_commit number;
1287 l_outputs ff_exec.outputs_t;
1288 l_return varchar2(30);
1289
1290 Begin
1291 hr_utility.set_location ('Entering '||l_proc,10);
1292 hr_utility.set_location ('p_effective_date '||p_effective_date,10);
1293 /*
1294 l_effective_date:=to_date(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
1295 l_effective_date:=to_date(to_char(trunc(l_effective_date),'DD/MM/RRRR'),'DD/MM/RRRR');
1296 */
1297 l_effective_date := trunc(fnd_date.canonical_to_date(p_effective_date));
1298 --
1299 --
1300 --9999 Needed?? l_actn := 'Initialize the ben_batch_utils cache...';
1301 --9999 Needed?? ben_batch_utils.ini;
1302 --9999 Needed?? l_actn := 'Initialize the ben_batch_utils cache...';
1303 --9999 Needed?? ben_batch_utils.ini(p_actn_cd => 'PROC_INFO');
1304 --
1305 -- Create actions if we are not doing a restart.
1306 --
1307 l_benefit_action_id := p_benefit_action_id;
1308
1309 If NOT(p_restart) then
1310 hr_utility.set_location('Not a Restart',14);
1311 --
1312 -- Now lets create person actions for all the people we are going to
1313 -- process in the Forfeiture Calculation run.
1314 --
1315 hr_utility.set_location('l_effective_date ' || l_effective_date ,14);
1316 hr_utility.set_location('p_bg ' || p_business_group_id ,14);
1317 open c_pl_subj_frfs(p_effective_date => l_effective_date);
1318 l_person_cnt := 0;
1319 l_cnt := 0;
1320 l_actn := 'Loading person_actions table..';
1321 Loop
1322 fetch c_pl_subj_frfs into l_pl_subj_frfs;
1323 Exit when c_pl_subj_frfs%notfound;
1324 l_cnt := l_cnt + 1;
1325 l_actn := 'Calling ben_batch_utils.comp_obj_selection_rule...';
1326 hr_utility.set_location('pl_id='||to_char(l_pl_subj_frfs.pl_id)||
1327 ' l_cnt='||to_char(l_cnt),18);
1328 --
1329 -- if comp_obj_selection_rule is pass, test rule.
1330 -- If the rule return 'N' then
1331 -- skip that pl_id.
1332 --
1333 skip := FALSE;
1334
1335 -- check criteria that the user entered on the submit form.
1336
1337 if p_pl_id is not null or p_pl_typ_id is not null or p_pgm_id is not null
1338 or p_comp_selection_rule_id is not null then
1339 rl_ret := 'Y';
1340 --
1341 ben_prem_pl_oipl_monthly.get_comp_object_info
1342 (p_oipl_id => null -- 9999 l_pl_subj_frfs.oipl_id
1343 ,p_pl_id => l_pl_subj_frfs.pl_id
1344 ,p_pgm_id => p_pgm_id
1345 ,p_effective_date => l_effective_date
1346 ,p_out_pgm_id => l_pgm_id
1347 ,p_out_pl_typ_id => l_pl_typ_id
1348 ,p_out_pl_id => l_pl_id
1349 ,p_out_opt_id => l_opt_id);
1350
1351 if p_pl_id is not null and p_pl_id <> l_pl_id then
1352 rl_ret := 'N';
1353 elsif p_pl_typ_id is not null and p_pl_typ_id <> l_pl_typ_id then
1354 rl_ret := 'N';
1355 elsif p_pgm_id is not null and p_pgm_id <> l_pgm_id then
1356 rl_ret := 'N';
1357 elsif rl_ret = 'Y' and p_comp_selection_rule_id is not null then
1358 l_actn := 'found a comp object rule...';
1359 hr_utility.set_location('found a comp object rule',22);
1360 l_outputs := benutils.formula
1361 (p_formula_id => p_comp_selection_rule_id
1362 ,p_effective_date => l_effective_date
1363 ,p_pgm_id => l_pgm_id
1364 ,p_pl_id => l_pl_id
1365 ,p_pl_typ_id => l_pl_typ_id
1366 ,p_opt_id => l_opt_id
1367 ,p_ler_id => null
1368 ,p_business_group_id => p_business_group_id);
1369 --
1370 l_return := l_outputs(l_outputs.first).value;
1371 if upper(l_return) not in ('Y', 'N') then
1372 l_return := 'N';
1373 end if;
1374
1375 rl_ret:= l_return;
1376
1377 --
1378 end if;
1379
1380 If (rl_ret = 'N') then
1381 skip := TRUE;
1382 End if;
1383
1384 end if;
1385
1386
1387 --
1388 -- Store pl_id into person actions table.
1389 --
1390 If ( not skip) then
1391 hr_utility.set_location('not skip...Inserting Ben_person_actions',28);
1392 l_actn := 'Inserting Ben_person_actions...';
1393 select ben_person_actions_s.nextval
1394 into l_person_action_id
1395 from sys.dual;
1396
1397 insert into ben_person_actions
1398 (person_action_id,
1399 person_id,
1400 ler_id,
1401 benefit_action_id,
1402 action_status_cd,
1403 object_version_number,
1404 chunk_number,
1405 non_person_cd)
1406 values
1407 (l_person_action_id,
1408 l_pl_subj_frfs.pl_id,
1409 0,
1410 p_benefit_action_id,
1411 'U',
1412 1,
1413 l_chunk_num,
1414 'FRFS');
1415
1416 l_num_row := l_num_row + 1;
1417 l_person_cnt := l_person_cnt + 1;
1418 l_end_person_action_id := l_person_action_id;
1419 If l_num_row = 1 then
1420 l_start_person_action_id := l_person_action_id;
1421 End if;
1422 If l_num_row = p_chunk_size then
1423 --
1424 -- Create a range of data to be multithreaded.
1425 --
1426 l_actn := 'Inserting Ben_batch_ranges.......';
1427 hr_utility.set_location('Inserting Ben_batch_ranges',32);
1428 -- Select next sequence number for the range
1429 --
1430 select ben_batch_ranges_s.nextval
1431 into l_range_id
1432 from sys.dual;
1433
1434 insert into ben_batch_ranges
1435 (range_id,
1436 benefit_action_id,
1437 range_status_cd,
1438 starting_person_action_id,
1439 ending_person_action_id,
1440 object_version_number)
1441 values
1442 (l_range_id,
1443 p_benefit_action_id,
1444 'U',
1445 l_start_person_action_id,
1446 l_end_person_action_id,
1447 1);
1448 l_start_person_action_id := 0;
1449 l_end_person_action_id := 0;
1450 l_num_row := 0;
1451 l_num_range := l_num_range + 1;
1452 l_chunk_num := l_chunk_num + 1;
1453 End if;
1454 End if;
1455 End loop;
1456 Close c_pl_subj_frfs;
1457 --
1458 hr_utility.set_location('l_num_row='||to_char(l_num_row),34);
1459 If (l_num_row <> 0) then
1460 l_actn := 'Inserting Final Ben_batch_ranges...';
1461 hr_utility.set_location('Inserting Final Ben_batch_ranges',38);
1462
1463 select ben_batch_ranges_s.nextval
1464 into l_range_id
1465 from sys.dual;
1466
1467 insert into ben_batch_ranges
1468 (range_id,
1469 benefit_action_id,
1470 range_status_cd,
1471 starting_person_action_id,
1472 ending_person_action_id,
1473 object_version_number)
1474 values
1475 (l_range_id,
1476 p_benefit_action_id,
1477 'U',
1478 l_start_person_action_id,
1479 l_end_person_action_id,
1480 1);
1481 l_num_range := l_num_range + 1;
1482 End if;
1483 Else
1484 hr_utility.set_location('This is a RESTART',42);
1485 l_actn := 'Calling Ben_batch_utils.create_restart_person_actions...';
1486 -- 9999 What this procedure does
1487 Ben_batch_utils.create_restart_person_actions
1488 (p_benefit_action_id => p_benefit_action_id
1489 ,p_effective_date => l_effective_date
1490 ,p_chunk_size => p_chunk_size
1491 ,p_threads => p_threads
1492 ,p_num_ranges => l_num_range
1493 ,p_num_persons => l_person_cnt
1494 ,p_non_person_cd => 'FRFS' -- code used in benrptut.pkb
1495 );
1496 End if;
1497 commit;
1498 --
1499 -- Now to multithread the code.
1500 --
1501 hr_utility.set_location('l_num_range '||to_char(l_num_range),46);
1502 If l_num_range > 1 then
1503 For l_count in 1..least(p_threads,l_num_range)-1 loop
1504 --
1505 l_actn := 'Submitting job to con-current manager...';
1506 hr_utility.set_location('Submitting BENFRCON to con-current manager ',50);
1507 -- Conncurrent manage needs the effective date in a varchar form.
1508 l_request_id := fnd_request.submit_request
1509 (application => 'BEN'
1510 ,program => 'BENFRCOM'
1511 ,description => NULL
1512 ,sub_request => FALSE
1513 ,argument1 => l_benefit_action_id
1514 ,argument2 => p_effective_date
1515 ,argument3 => p_validate
1516 ,argument4 => p_business_group_id
1517 ,argument5 => l_count
1518 );
1519 --
1520 -- Store the request id of the concurrent request
1521 --
1522 ben_batch_utils.g_num_processes := ben_batch_utils.g_num_processes + 1;
1523 ben_batch_utils.g_processes_tbl(ben_batch_utils.g_num_processes)
1524 := l_request_id;
1525 End loop;
1526 Elsif (l_num_range = 0 ) then
1527 l_actn := 'Calling Ben_batch_utils.print_parameters...';
1528 hr_utility.set_location('Calling Ben_batch_utils.print_parameters ',56);
1529 -- 9999 Add all other required params.
1530 Ben_batch_utils.print_parameters
1531 (p_thread_id => 99
1532 ,p_benefit_action_id => l_benefit_action_id
1533 ,p_validate => p_validate
1534 ,p_business_group_id => p_business_group_id
1535 ,p_effective_date => l_effective_date
1536 ,p_mode => null
1537 ,p_comp_selection_rule_id => p_comp_selection_rule_id
1538 ,p_pgm_id => p_pgm_id
1539 ,p_pl_typ_id => p_pl_typ_id
1540 ,p_pl_id => p_pl_id
1541 ,p_person_id => p_person_id
1542 ,p_person_selection_rule_id => p_person_selection_rule_id
1543 ,p_person_type_id => p_person_type_id
1544 ,p_ler_id => null
1545 ,p_organization_id => p_organization_id
1546 ,p_benfts_grp_id => null
1547 ,p_location_id => null
1548 ,p_legal_entity_id => null
1549 ,p_payroll_id => null
1550 );
1551
1552 -- Because there are other processes to run, do not error if first process finds
1553 -- noone to process.
1554
1555 -- 9999 Why this is needed.
1556 Ben_batch_utils.write(p_text =>
1557 '<< No Plans For Forfeiture were selected with above selection criteria >>' );
1558 --fnd_message.set_name('BEN','BEN_92453_NO_PREMS_TO_PROCESS');
1559 --fnd_message.raise_error;
1560 End if;
1561
1562 if (l_num_range <> 0 ) then
1563 -- All other parameters.
1564 l_actn := 'Calling do_multithread...';
1565 hr_utility.set_location('Calling do_multithread ',60);
1566 do_multithread(errbuf => errbuf
1567 ,retcode => retcode
1568 ,p_validate => p_validate
1569 ,p_benefit_action_id => l_benefit_action_id
1570 ,p_thread_id => p_threads+1
1571 ,p_effective_date => p_effective_date
1572 ,p_business_group_id => p_business_group_id
1573 );
1574 l_actn := 'Calling ben_batch_utils.check_all_slaves_finished...';
1575
1576 hr_utility.set_location('Calling ben_batch_utils.check_all_slaves_finished ',64);
1577 ben_batch_utils.check_all_slaves_finished(p_rpt_flag => TRUE);
1578 ben_batch_utils.end_process(p_benefit_action_id => l_benefit_action_id
1579 ,p_person_selected => l_person_cnt
1580 ,p_business_group_id => p_business_group_id
1581 ,p_non_person_cd => 'FRFS'); -- used in benrptut
1582 end if;
1583 hr_utility.set_location ('Leaving '||l_proc,99);
1584 --
1585 Exception
1586 when others then
1587 ben_batch_utils.rpt_error(p_proc => l_proc
1588 ,p_last_actn => l_actn
1589 ,p_rpt_flag => TRUE );
1590 --
1591 benutils.write(p_text => fnd_message.get);
1592 benutils.write(p_text => sqlerrm);
1593 benutils.write(p_text => 'Big Error Occured');
1594 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
1595 If (l_num_range > 0) then
1596 ben_batch_utils.check_all_slaves_finished(p_rpt_flag => TRUE);
1597 ben_batch_utils.end_process(p_benefit_action_id => l_benefit_action_id
1598 ,p_person_selected => l_person_cnt
1599 ,p_business_group_id => p_business_group_id
1600 ) ;
1601 End if;
1602 hr_utility.set_location ('HR_6153_ALL_PROCEDURE_FAIL',25);
1603 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1604 fnd_message.set_token('PROCEDURE', l_proc);
1605 fnd_message.set_token('STEP', l_actn );
1606 fnd_message.raise_error;
1607 End process_by_plan;
1608 --
1609 procedure process(errbuf out nocopy varchar2
1610 ,retcode out nocopy number
1611 ,p_benefit_action_id in number
1612 ,p_effective_date in varchar2
1613 ,p_validate in varchar2 default 'N'
1614 ,p_business_group_id in number
1615 ,p_organization_id in number default null
1616 ,p_frfs_perd_det_cd in varchar2 default null
1617 ,p_person_id in number default null -- For Future Enhancement.
1618 ,p_person_type_id in number default null -- For Future Enhancement.
1619 ,p_pgm_id in number default null
1620 ,p_pl_typ_id in number default null
1621 ,p_pl_id in number default null
1622 ,p_comp_selection_rule_id in number default null
1623 ,p_person_selection_rule_id in number default null -- For Future Enhancement.
1624 ,p_debug_messages in varchar2 default 'N'
1625 ,p_audit_log_flag in varchar2 default 'N'
1626 ,p_commit_data_flag in varchar2 default 'Y'
1627 ) is
1628 --
1629 -- local variable declaration.
1630 --
1631 l_request_id number;
1632 l_proc varchar2(80) := g_package||'.process';
1633 l_benefit_action_id ben_benefit_actions.benefit_action_id%type;
1634 l_object_version_number ben_benefit_actions.object_version_number%type;
1635 l_person_id per_people_f.person_id%type;
1636 l_person_action_id ben_person_actions.person_action_id%type;
1637 l_ler_id ben_ler_f.ler_id%type;
1638 l_range_id ben_batch_ranges.range_id%type;
1639 l_chunk_size number := 20;
1640 l_threads number := 1;
1641 l_start_person_action_id number := 0;
1642 l_end_person_action_id number := 0;
1643 l_prev_person_id number := 0;
1644 rl_ret char(1);
1645 skip boolean;
1646 l_person_cnt number := 0;
1647 l_cnt number := 0;
1648 l_actn varchar2(80);
1649 l_num_range number := 0;
1650 l_chunk_num number := 1;
1651 l_num_row number := 0;
1652 l_commit number;
1653 l_effective_date date;
1654 l_effective_date_char varchar2(19);
1655 l_errbuf varchar2(80);
1656 l_retcode number;
1657 l_restart boolean;
1658
1659 Begin
1660 hr_utility.set_location ('Entering '||l_proc,10);
1661 /*
1662 l_effective_date:=to_date(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
1663 l_effective_date:=to_date(to_char(trunc(l_effective_date),'DD/MM/RRRR'),'DD/MM/RRRR');
1664 */
1665 l_effective_date := trunc(fnd_date.canonical_to_date(p_effective_date));
1666 --
1667 hr_utility.set_location ('p_effective_date '||p_effective_date,999);
1668 hr_utility.set_location ('l_effective_date '||l_effective_date,999);
1669 --
1670 --
1671 -- Put row in fnd_sessions
1672 --
1673 dt_fndate.change_ses_date
1674 (p_ses_date => l_effective_date,
1675 p_commit => l_commit);
1676 --
1677 l_actn := 'Initialize the ben_batch_utils cache...';
1678 ben_batch_utils.ini;
1679 l_actn := 'Initialize the ben_batch_utils cache...';
1680 ben_batch_utils.ini(p_actn_cd => 'PROC_INFO');
1681 --
1682 -- Check that all the mandatory input parameters
1683 -- such as p_business_group_id, p_mode, p_effective_date
1684 --
1685 l_actn := 'Checking arguments...';
1686 hr_utility.set_location('Checking arguments',12);
1687 hr_api.mandatory_arg_error(p_api_name => g_package
1688 ,p_argument => 'p_business_group_id'
1689 ,p_argument_value => p_business_group_id
1690 );
1691 hr_api.mandatory_arg_error(p_api_name => g_package
1692 ,p_argument => 'p_effective_date'
1693 ,p_argument_value => p_effective_date );
1694 --
1695 -- Get chunk_size and Thread values for multi-thread process, and check to
1696 -- assure they are sensible.
1697 -- chunk_size between(10 and 100). If not in range, default to 20.
1698 -- threads between <1 and 100>. If not in range, default to 1
1699 --
1700 l_actn := 'Calling benutils.get_parameter...';
1701 benutils.get_parameter(p_business_group_id => p_business_group_Id
1702 ,p_batch_exe_cd => 'BENPRCON'
1703 ,p_threads => l_threads
1704 ,p_chunk_size => l_chunk_size
1705 ,p_max_errors => g_max_errors_allowed);
1706 benutils.g_benefit_action_id := p_benefit_action_id;
1707 benutils.g_thread_id := 99;
1708 --
1709 -- Create benefit actions parameters in the benefit action table.
1710 -- Do not create is a benefit action already exists, in other words
1711 -- we are doing a restart.
1712 --
1713 If(p_benefit_action_id is null) then
1714
1715 hr_utility.set_location('p_benefit_action_id is null',14);
1716 l_restart := FALSE;
1717
1718 ben_benefit_actions_api.create_benefit_actions
1719 (p_validate => false
1720 ,p_benefit_action_id => l_benefit_action_id
1721 ,p_process_date => l_effective_date
1722 ,p_mode_cd => 'S'
1723 ,p_derivable_factors_flag => 'N'
1724 ,p_validate_flag => p_validate
1725 ,p_person_id => p_person_id
1726 ,p_person_type_id => p_person_type_id
1727 ,p_pgm_id => p_pgm_id
1728 ,p_business_group_id => p_business_group_id
1729 ,p_pl_typ_id => p_pl_typ_id
1730 ,p_pl_id => p_pl_id
1731 ,p_popl_enrt_typ_cycl_id => null
1732 ,p_no_programs_flag => 'N'
1733 ,p_no_plans_flag => 'N'
1734 ,p_comp_selection_rl => p_comp_selection_rule_id
1735 ,p_person_selection_rl => p_person_selection_rule_id
1736 ,p_ler_id => null
1737 ,p_organization_id => p_organization_id
1738 ,p_benfts_grp_id => null
1739 ,p_location_id => null
1740 ,p_pstl_zip_rng_id => NULL
1741 ,p_rptg_grp_id => NULL
1742 ,p_opt_id => NULL
1743 ,p_eligy_prfl_id => NULL
1744 ,p_vrbl_rt_prfl_id => NULL
1745 ,p_legal_entity_id => null
1746 ,p_payroll_id => null
1747 ,p_debug_messages_flag => p_debug_messages
1748 ,p_object_version_number => l_object_version_number
1749 ,p_effective_date => l_effective_date
1750 ,p_request_id => fnd_global.conc_request_id
1751 ,p_program_application_id => fnd_global.prog_appl_id
1752 ,p_program_id => fnd_global.conc_program_id
1753 ,p_program_update_date => sysdate
1754 );
1755 benutils.g_benefit_action_id := l_benefit_action_id;
1756 --
1757 -- Delete/clear ranges from ben_batch_ranges table
1758 --
1759 l_actn := 'Delete rows from ben_batch_ranges..';
1760 hr_utility.set_location('Delete rows from ben_batch_ranges',16);
1761
1762 Delete from ben_batch_ranges
1763 Where benefit_action_id = l_benefit_action_id;
1764 --
1765 -- Future enhancements for individual person processing goes here.
1766 --
1767 Else
1768 --
1769 hr_utility.set_location('p_benefit_action_id is not null',30);
1770 l_restart := TRUE;
1771 l_benefit_action_id := p_benefit_action_id;
1772 l_actn := 'Calling Ben_batch_utils.create_restart_person_actions...';
1773 Ben_batch_utils.create_restart_person_actions
1774 (p_benefit_action_id => p_benefit_action_id
1775 ,p_effective_date => l_effective_date
1776 ,p_chunk_size => l_chunk_size
1777 ,p_threads => l_threads
1778 ,p_num_ranges => l_num_range
1779 ,p_num_persons => l_person_cnt
1780 );
1781 --
1782 End if;
1783 commit;
1784 --------------------------------------------------------------------------
1785 -- Now call the forfeitures by plan processes:
1786 --------------------------------------------------------------------------
1787 if p_person_id is null and p_person_selection_rule_id is null then
1788 --
1789 -- Only process Plans subject to forfeiture if no person criteria was
1790 -- selected
1791 --
1792 ben_forfeiture_concurrent.process_by_plan(
1793 errbuf => l_errbuf
1794 ,retcode => l_retcode
1795 ,p_benefit_action_id => l_benefit_action_id
1796 ,p_effective_date => p_effective_date -- l_effective_date_char
1797 ,p_validate => p_validate
1798 ,p_business_group_id => p_business_group_id
1799 ,p_organization_id => p_organization_id
1800 ,p_frfs_perd_det_cd => p_frfs_perd_det_cd
1801 ,p_person_id => p_person_id
1802 ,p_person_type_id => p_person_type_id
1803 ,p_pgm_id => p_pgm_id
1804 ,p_pl_typ_id => p_pl_typ_id
1805 ,p_pl_id => p_pl_id
1806 ,p_comp_selection_rule_id => p_comp_selection_rule_id
1807 ,p_person_selection_rule_id => p_person_selection_rule_id
1808 ,p_debug_messages => p_debug_messages
1809 ,p_audit_log_flag => p_audit_log_flag
1810 ,p_commit_data_flag => p_commit_data_flag
1811 ,p_threads => l_threads
1812 ,p_chunk_size => l_chunk_size
1813 ,p_max_errors => g_max_errors_allowed
1814 ,p_restart => l_restart);
1815 end if;
1816 --
1817 submit_all_reports;
1818 --
1819 /* Uncomment after future enhancement
1820 ben_batch_utils.end_process(p_benefit_action_id => l_benefit_action_id
1821 ,p_person_selected => l_person_cnt
1822 ,p_business_group_id => p_business_group_id);
1823 */
1824 hr_utility.set_location ('Leaving '||l_proc,70);
1825 --
1826 Exception
1827 when others then
1828 ben_batch_utils.rpt_error(p_proc => l_proc
1829 ,p_last_actn => l_actn
1830 ,p_rpt_flag => TRUE );
1831 --
1832 benutils.write(p_text => fnd_message.get);
1833 benutils.write(p_text => sqlerrm);
1834 benutils.write(p_text => 'Big Error Occured');
1835 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
1836 /* 999 Temporarily commented.
1837 If (l_num_range > 0) then
1838 ben_batch_utils.check_all_slaves_finished(p_rpt_flag => TRUE);
1839 ben_batch_utils.end_process(p_benefit_action_id => l_benefit_action_id
1840 ,p_person_selected => l_person_cnt
1841 ,p_business_group_id => p_business_group_id
1842 ) ;
1843 End if;
1844 */
1845 hr_utility.set_location ('HR_6153_ALL_PROCEDURE_FAIL',689);
1846 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1847 fnd_message.set_token('PROCEDURE', l_proc);
1848 fnd_message.set_token('STEP', l_actn );
1849 fnd_message.raise_error;
1850 End process;
1851 end ben_forfeiture_concurrent; -- End of Package.