[Home] [Help]
PACKAGE BODY: APPS.BEN_PREMIUM_CONCURRENT
Source
1 package body ben_premium_concurrent as
2 /* $Header: benprcon.pkb 120.1 2006/07/04 13:38:09 swjain ship $ */
3 --
4 /* ============================================================================
5 * Name
6 * Premium Process Concurrent Manager Processes
7 *
8 * Purpose
9 * This package simply houses the concurrent manager and multi-thread
10 * processes for Premium Calculation.
11 *
12 * History
13 * Date Who Version What?
14 * ------- --------- ------- --------------------------------------
15 * 18-Jun-99 lmcdonal 115.0 Created
16 * 08-Jul-99 lmcdonal 115.1 Added reporting
17 * 20-JUL-99 Gperry 115.2 genutils -> benutils package rename.
18 * 23-Jul-99 lmcdonal 115.3 add distinct to main cursor.
19 * 06-Oct-99 tguy 115.4 added call to dt_fndate
20 * 12-Oct-99 maagrawa 115.5 ben_env_object.init call added
21 * to do_multithread.
22 * 02-Nov-99 lmcdonal 115.6 Make ben_prem_pl_oipl_monthly
23 * multi-threaded.
24 * 08-Nov-99 lmcdonal 115.7 The last 3 parms were missing from
25 * 'BENPRCOM' submit_request call.
26 * p_first_day parm of multithread s/b char.
27 * 27-Feb-00 lmcdonal 115.8 Better debug messages. Also, do not fail
28 * if first of 3 processes finds noone to
29 * process.
30 * 26-Apr-02 nhunur 115.9 Fix for bug 2345799 / 13530340.6
31 * 08-Jun-02 pabodla 115.10 Do not select the contingent worker
32 * assignment when assignment data is
33 * fetched.
34 * 17-Jun-02 vsethi 115.11 Modified the person determination
35 * criteria to include organization and
36 * legal entity
37 * 18-Jun-02 vsethi 115.12 Modified the sub_query in c_person cursor
38 * to refer the person_id of outer query and
39 * not p_person_id
40 * 30-Dec-2002 mmudigon 115.14 NOCOPY
41 * 07-Jan-2003 rpgupta 115.15 Removed l_return from procedure
42 * prem_person_selection_rule as formula was
43 * not being picked up
44 * 04-Jun-2006 swjain 115.16 Bug 5331889 - passed person_id as input param
45 * in prem_person_selection_rule and added input1 as
46 * additional param for future use
47 * -----------------------------------------------------------------------------
48 */
49 --
50 -- Global cursor and variables declaration
51 --
52 g_package varchar2(80) := 'Ben_premium_concurrent';
53 g_persons_processed number(9) := 0;
54 g_persons_ended number(9) := 0;
55 g_persons_passed number(9) := 0;
56 g_persons_errored number(9) := 0;
57 g_max_errors_allowed number(9) := 200;
58 --
59 -- ===========================================================================
60 -- << Procedure: ben_batch_utils.batch_report >>
61 -- ===========================================================================
62 --
63 Procedure Submit_all_reports (p_rpt_flag Boolean default FALSE) is
64 l_proc varchar2(80) := g_package||'.submit_all_reports';
65 l_actn varchar2(80);
66 l_request_id number;
67 Begin
68 hr_utility.set_location ('Entering '||l_proc,05);
69 l_actn := 'Calling ben_batch_utils.batch_report (BENPRSUM)...';
70 ben_batch_utils.batch_report
71 (p_concurrent_request_id => fnd_global.conc_request_id
72 ,p_program_name => 'BENPRSUM'
73 ,p_request_id => l_request_id
74 );
75 l_actn := 'Calling ben_batch_utils.batch_report (BENPRDEA)...';
76 ben_batch_utils.batch_report
77 (p_concurrent_request_id => fnd_global.conc_request_id
78 ,p_program_name => 'BENPRDEA'
79 ,p_request_id => l_request_id
80 );
81 l_actn := 'Calling ben_batch_utils.batch_report (BENERTYP)...';
82 ben_batch_utils.batch_report
83 (p_concurrent_request_id => fnd_global.conc_request_id
84 ,p_program_name => 'BENERTYP'
85 ,p_request_id => l_request_id
86 );
87 l_actn := 'Calling ben_batch_utils.batch_report (BENERPER)...';
88 ben_batch_utils.batch_report
89 (p_concurrent_request_id => fnd_global.conc_request_id
90 ,p_program_name => 'BENERPER'
91 ,p_request_id => l_request_id
92 );
93
94 hr_utility.set_location ('Leaving '||l_proc,10);
95 Exception
96 When others then
97 ben_batch_utils.rpt_error(p_proc => l_proc
98 ,p_last_actn => l_actn
99 ,p_rpt_flag => p_rpt_flag
100 );
101 raise;
102 End Submit_all_reports;
103 --
104 -- ==================================================================================
105 -- << Procedure: prem_person_selection_rule >>
106 -- Description:
107 -- this procedure is called from 'process'. It calls the person selection rule.
108 -- this has been added to report errors for a person while executing the selection rule
109 -- and prevent the conc process from failing .
110 -- ==================================================================================
111 procedure prem_person_selection_rule
112 (p_person_id in Number
113 ,p_business_group_id in Number
114 ,p_person_selection_rule_id in Number
115 ,p_effective_date in Date
116 ,p_batch_flag in Boolean default FALSE
117 ,p_input1 in varchar2 default null -- Bug 5331889
118 ,p_input1_value in varchar2 default null
119 ,p_return in out nocopy varchar2
120 ,p_err_message in out nocopy varchar2 ) as
121
122 Cursor c1 is
123 Select assignment_id
124 From per_assignments_f paf
125 Where paf.person_id = p_person_id
126 and paf.assignment_type <> 'C'
127 And paf.primary_flag = 'Y'
128 And paf.business_group_id = p_business_group_id
129 And p_effective_date between
130 paf.effective_start_date and paf.effective_end_date ;
131 --
132 l_proc varchar2(80) := g_package||'.prem_person_selection_rule';
133 l_outputs ff_exec.outputs_t;
134 --l_return varchar2(30);
135 l_assignment_id number;
136 l_actn varchar2(80);
137 value_exception exception ;
138 Begin
139 hr_utility.set_location ('Entering '||l_proc,10);
140 --
141 -- Get assignment ID form per_assignments_f table.
142 --
143 l_actn := 'Opening C1 Assignment cursor...';
144 open c1;
145 fetch c1 into l_assignment_id;
146 If c1%notfound then
147 raise ben_batch_utils.g_record_error;
148 End if;
149 close c1;
150 -- Call formula initialise routine
151 --
152 l_actn := 'Calling benutils.formula procedure...';
153
154 l_outputs := benutils.formula
155 (p_formula_id => p_person_selection_rule_id
156 ,p_effective_date => p_effective_date
157 ,p_business_group_id => p_business_group_id
158 ,p_assignment_id => l_assignment_id
159 ,p_param1 => 'BEN_IV_PERSON_ID' -- Bug 5331889
160 ,p_param1_value => to_char(p_person_id)
161 ,p_param2 => p_input1
162 ,p_param2_value => p_input1_value);
163 p_return := l_outputs(l_outputs.first).value;
164 --
165 l_actn := 'Evaluating benutils.formula return...';
166 --
167 If upper(p_return) not in ('Y', 'N') then
168 Raise value_exception ;
169 End if;
170 --p_return := 'Y';
171 hr_utility.set_location ('Leaving '||l_proc,10);
172 Exception
173 When ben_batch_utils.g_record_error then
174 p_return := 'N' ;
175 fnd_message.set_name('BEN','BEN_91698_NO_ASSIGNMENT_FND');
176 fnd_message.set_token('ID' ,to_char(p_person_id) );
177 fnd_message.set_token('PROC',l_proc ) ;
178 p_err_message := fnd_message.get ;
179
180 When value_exception then
181 p_return := 'N' ;
182 fnd_message.set_name('BEN','BEN_91329_FORMULA_RETURN');
183 fnd_message.set_token('RL','person_selection_rule_id :'||p_person_selection_rule_id);
184 fnd_message.set_token('PROC',l_proc ) ;
185 p_err_message := fnd_message.get ;
186
187 when others then
188 p_return := 'N' ;
189 p_err_message := 'A unhandled exception has been raised while processing Person : '||to_char(p_person_id)
190 ||' in package : '|| l_proc ||'.';
191
192 End prem_person_selection_rule;
193 --
194 -- ============================================================================
195 -- << Procedure: Do_Multithread >>
196 -- Description:
197 -- this procedure is called from 'process'. It calls the premium routine.
198 -- ============================================================================
199 procedure do_multithread
200 (errbuf out nocopy varchar2
201 ,retcode out nocopy number
202 ,p_validate in varchar2 default 'N'
203 ,p_benefit_action_id in number
204 ,p_thread_id in number
205 ,p_effective_date in varchar2
206 ,p_business_group_id in number
207 ,p_mo_num in number
208 ,p_yr_num in number
209 ,p_first_day_of_month in varchar2
210 ) is
211 --
212 -- Local variable declaration
213 --
214 l_proc varchar2(80) := g_package||'.do_multithread';
215 l_person_id ben_person_actions.person_id%type;
216 l_person_action_id ben_person_actions.person_action_id%type;
217 l_object_version_number ben_person_actions.object_version_number%type;
218 l_ler_id ben_person_actions.ler_id%type;
219 l_range_id ben_batch_ranges.range_id%type;
220 l_record_number number := 0;
221 l_start_person_action_id number := 0;
222 l_end_person_action_id number := 0;
223 l_actn varchar2(80);
224 l_cnt number(5):= 0;
225 l_chunk_size number(15);
226 l_threads number(15);
227 l_effective_date date;
228 l_first_day_of_month date;
229 --
230 -- Cursors declaration
231 --
232 Cursor c_range_thread is
233 Select ran.range_id
234 ,ran.starting_person_action_id
235 ,ran.ending_person_action_id
236 From ben_batch_ranges ran
237 Where ran.range_status_cd = 'U'
238 And ran.BENEFIT_ACTION_ID = P_BENEFIT_ACTION_ID
239 And rownum < 2
240 For update of ran.range_status_cd
241 ;
242 Cursor c_person_thread is
243 Select ben.person_id
244 ,ben.person_action_id
245 ,ben.object_version_number
246 ,ben.ler_id
247 From ben_person_actions ben
248 Where ben.benefit_action_id = p_benefit_action_id
249 And ben.action_status_cd <> 'P'
250 And ben.person_action_id between
251 l_start_person_action_id and l_end_person_action_id
252 Order by ben.person_action_id
253 ;
254 Cursor c_parameter is
255 Select *
256 From ben_benefit_actions ben
257 Where ben.benefit_action_id = p_benefit_action_id
258 ;
259 l_parm c_parameter%rowtype;
260 l_ovn number := null;
261 l_commit number;
262 l_error_text varchar2(200) := null;
263
264 Begin
265
266 hr_utility.set_location ('Entering '||l_proc,05);
267
268 l_effective_date:=to_date(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
269 l_effective_date:=to_date(to_char(trunc(l_effective_date),'DD/MM/RRRR'),'DD/MM/RRRR');
270
271 l_first_day_of_month:=to_date(p_first_day_of_month,'YYYY/MM/DD HH24:MI:SS');
272 l_first_day_of_month:=to_date(to_char(trunc(l_first_day_of_month),'DD/MM/RRRR'),'DD/MM/RRRR');
273 --
274 -- Put row in fnd_sessions
275 --
276 dt_fndate.change_ses_date
277 (p_ses_date => l_effective_date,
278 p_commit => l_commit);
279 --
280 l_actn := 'Calling benutils.get_parameter...';
281 benutils.get_parameter(p_business_group_id => p_business_group_Id
282 ,p_batch_exe_cd => 'BENPRCON'
283 ,p_threads => l_threads
284 ,p_chunk_size => l_chunk_size
285 ,p_max_errors => g_max_errors_allowed);
286 --
287 -- Set up benefits environment
288 --
289 ben_env_object.init(p_business_group_id => p_business_group_id,
290 p_effective_date => l_effective_date,
291 p_thread_id => p_thread_id,
292 p_chunk_size => l_chunk_size,
293 p_threads => l_threads,
294 p_max_errors => g_max_errors_allowed,
295 p_benefit_action_id => p_benefit_action_id);
296 --
297 l_actn := 'Calling ben_batch_utils.ini...';
298 ben_batch_utils.ini;
299 --
300 -- Copy benefit action id to global in benutils package
301 --
302 benutils.g_benefit_action_id := p_benefit_action_id;
303 benutils.g_thread_id := p_thread_id;
304 g_persons_errored := 0;
305 g_persons_processed := 0;
306 open c_parameter;
307 fetch c_parameter into l_parm;
308 close c_parameter;
309 --
310 l_actn := 'Calling ben_batch_utils.print_parameters...';
311 --
312 ben_batch_utils.print_parameters
313 (p_thread_id => p_thread_id
314 ,p_benefit_action_id => p_benefit_action_id
315 ,p_validate => p_validate
316 ,p_business_group_id => p_business_group_id
317 ,p_effective_date => l_effective_date
318 ,p_person_id => l_parm.person_id
319 ,p_person_selection_rule_id => l_parm.person_selection_rl
320 ,p_comp_selection_rule_id => l_parm.comp_selection_rl
321 ,p_pgm_id => l_parm.pgm_id
322 ,p_pl_typ_id => l_parm.pl_typ_id
323 ,p_pl_id => l_parm.pl_id
324 ,p_person_type_id => l_parm.person_type_id
325 ,p_ler_id => null
326 ,p_organization_id => l_parm.organization_id
327 ,p_benfts_grp_id => null
328 ,p_location_id => null
329 ,p_legal_entity_id => l_parm.legal_entity_id
330 ,p_payroll_id => null
331 );
332 --
333 -- While loop to only try and fetch records while they exist
334 -- we always try and fetch the size of the chunk, if we get less
335 -- then we know that the process is finished so we end the while loop.
336 -- The process is as follows :
337 -- 1) Lock the rows that are not processed
338 -- 2) Grab as many rows as we can upto the chunk size
339 -- 3) Put each row into the person cache.
340 -- 4) Process the person cache
341 -- 5) Go to number 1 again.
342 --
343 Loop
344 l_actn := 'Opening c_range thread and fetch range...';
345 open c_range_thread;
346 fetch c_range_thread into l_range_id
347 ,l_start_person_action_id
348 ,l_end_person_action_id;
349 exit when c_range_thread%notfound;
350 close c_range_thread;
351 If(l_range_id is not NULL) then
352 --
353 l_actn := 'Updating ben_batch_ranges row...';
354 --
355 update ben_batch_ranges ran set ran.range_status_cd = 'P'
356 where ran.range_id = l_range_id;
357 commit;
358 End if;
359 --
360 -- Remove all records from cache
361 --
362 l_actn := 'Clearing g_cache_person_process cache...';
363 g_cache_person_process.delete;
364 open c_person_thread;
365 l_record_number := 0;
366 Loop
367
368 l_actn := 'Loading person data into g_cache_person_process cache...';
369 fetch c_person_thread
370 into g_cache_person_process(l_record_number+1).person_id
371 ,g_cache_person_process(l_record_number+1).person_action_id
372 ,g_cache_person_process(l_record_number+1).object_version_number
373 ,g_cache_person_process(l_record_number+1).ler_id;
374 exit when c_person_thread%notfound;
375 hr_utility.set_location ('Fetch person:'||
376 to_char(g_cache_person_process(l_record_number+1).person_id),20);
377 l_record_number := l_record_number + 1;
378 End loop;
379 close c_person_thread;
380 l_actn := 'Preparing to default each participant from cache...' ;
381 If l_record_number > 0 then
382 --
383 -- Process the rows from the person process cache
384 --
385 For l_cnt in 1..l_record_number loop
386 Begin
387 hr_utility.set_location ('Call ben_prem_prtt_monthly.main:',30);
388
389 ben_prem_prtt_monthly.main
390 (p_validate => p_validate
391 ,p_person_id => g_cache_person_process(l_cnt).person_id
392 ,p_person_action_id => g_cache_person_process(l_cnt).person_action_id
393 ,p_comp_selection_rl => l_parm.comp_selection_rl
394 ,p_pgm_id => l_parm.pgm_id
395 ,p_pl_typ_id => l_parm.pl_typ_id
396 ,p_pl_id => l_parm.pl_id
397 ,p_object_version_number => g_cache_person_process(l_cnt).object_version_number
398 ,p_business_group_id => p_business_group_id
399 ,p_mo_num => p_mo_num
400 ,p_yr_num => p_yr_num
401 ,p_first_day_of_month => l_first_day_of_month
402 ,p_effective_date => l_effective_date
403 );
404 g_persons_processed := g_persons_processed + 1;
405 Exception
406 When others then
407 l_error_text := sqlerrm;
408 hr_utility.set_location ('Person Failed in '||l_proc,777);
409 hr_utility.set_location (' with error '||l_error_text,777);
410
411 g_persons_errored := g_persons_errored + 1;
412
413 If (g_persons_errored > g_max_errors_allowed) then
414 hr_utility.set_location ('Person errors exceeds max allowed',778);
415 fnd_message.raise_error;
416 End if;
417 End;
418 End loop;
419 Else
420 --
421 l_actn := 'Erroring out nocopy since not person is found in range...' ;
422 --
423 hr_utility.set_location ('BEN_91709_PER_NOT_FND_IN_RNG',778);
424 fnd_message.set_name('BEN','BEN_91709_PER_NOT_FND_IN_RNG');
425 fnd_message.raise_error;
426 End if;
427 benutils.write_table_and_file(p_table => TRUE, p_file => FALSE);
428 End loop;
429 benutils.write_table_and_file(p_table => TRUE, p_file => FALSE);
430 --
431 l_actn := 'Calling Log_statistics...';
432 ben_batch_utils.write_logfile(p_num_pers_processed => g_persons_processed
433 ,p_num_pers_errored => g_persons_errored
434 );
435 hr_utility.set_location ('Leaving '||l_proc,70);
436 Exception
437 When others then
438 l_error_text := sqlerrm;
439 hr_utility.set_location ('Fail in '||l_proc,998);
440 hr_utility.set_location (' with error '||l_error_text,999);
441
442 ben_batch_utils.rpt_error(p_proc => l_proc
443 ,p_last_actn => l_actn
444 ,p_rpt_flag => TRUE
445 );
446 ben_batch_utils.write_logfile(p_num_pers_processed => g_persons_processed
447 ,p_num_pers_errored => g_persons_errored
448 );
449 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
450 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
451 fnd_message.set_token('PROCEDURE', l_proc);
452 fnd_message.set_token('STEP',l_actn );
453 fnd_message.raise_error;
454 End do_multithread;
455 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
456 -- << Procedure: Restart >>
457 -- *****************************************************************
458 --
459 procedure restart (errbuf out nocopy varchar2
460 ,retcode out nocopy number
461 ,p_benefit_action_id in number
462 ) is
463 --
464 -- Cursor Declaration
465 --
466 cursor c_parameters is
467 Select process_date
468 ,mode_cd
469 ,validate_flag
470 ,person_id
471 ,pgm_id
472 ,pl_typ_id
473 ,pl_id
474 ,business_group_id
475 ,popl_enrt_typ_cycl_id
476 ,person_selection_rl
477 ,comp_selection_rl
478 ,ler_id
479 ,organization_id
480 ,legal_entity_id
481 ,debug_messages_flag
482 From ben_benefit_actions ben
483 Where ben.benefit_action_id = p_benefit_action_id;
484 --
485 -- Local Variable declaration.
486 --
487 l_proc varchar2(80) := g_package||'.restart';
488 l_parameters c_parameters%rowtype;
489 l_errbuf varchar2(80);
490 l_retcode number;
491 l_actn varchar2(80);
492 Begin
493 hr_utility.set_location ('Entering '||l_proc,10);
494 --
495 -- get the parameters for a previous run and do a restart
496 --
497 l_actn := 'Getting parameter data...';
498 open c_parameters;
499 fetch c_parameters into l_parameters;
500 If c_parameters%notfound then
501 ben_batch_utils.rpt_error(p_proc => l_proc
502 ,p_last_actn => l_actn
503 ,p_rpt_flag => TRUE
504 );
505 hr_utility.set_location ('BEN_91710_RESTRT_PARMS_NOT_FND',778);
506 fnd_message.set_name('BEN','BEN_91710_RESTRT_PARMS_NOT_FND');
507 fnd_message.raise_error;
508 End if;
509 close c_parameters;
510 --
511 -- Call process procedure with parameters for restart
512 --
513 l_actn := 'Calling process...';
514 Process (errbuf => l_errbuf
515 ,retcode => l_retcode
516 ,p_benefit_action_id => p_benefit_action_id
517 ,p_effective_date => l_parameters.process_date
518 ,p_validate => l_parameters.validate_flag
519 ,p_person_id => l_parameters.person_id
520 ,p_business_group_id => l_parameters.business_group_id
521 ,p_pgm_id => l_parameters.pgm_id
522 ,p_pl_typ_id => l_parameters.pl_typ_id
523 ,p_pl_id => l_parameters.pl_id
524 ,p_person_selection_rule_id => l_parameters.person_selection_rl
525 ,p_comp_selection_rule_id => l_parameters.comp_selection_rl
526 ,p_organization_id => l_parameters.organization_id
527 ,p_legal_entity_id => l_parameters.legal_entity_id
528 ,p_debug_messages => l_parameters.debug_messages_flag
529 );
530 hr_utility.set_location ('Leaving '||l_proc,70);
531 Exception
532 when others then
533 ben_batch_utils.rpt_error(p_proc => l_proc
534 ,p_last_actn => l_actn
535 ,p_rpt_flag => TRUE
536 );
537 raise;
538 end restart;
539 --
540 -- *************************************************************************
541 -- * << Procedure: Process >>
542 -- *************************************************************************
543 -- This is what is called from the concurrent manager screen
544 --
545 procedure process(errbuf out nocopy varchar2
546 ,retcode out nocopy number
547 ,p_benefit_action_id in number default null
548 ,p_effective_date in varchar2
549 ,p_validate in varchar2 default 'N'
550 ,p_person_id in number default null
551 ,p_business_group_id in number
552 ,p_pgm_id in number default null
553 ,p_pl_typ_id in number default null
554 ,p_pl_id in number default null
555 ,p_person_selection_rule_id in number default null
556 ,p_comp_selection_rule_id in number default null
557 ,p_organization_id in number default null
558 ,p_legal_entity_id in number default null
559 ,p_debug_messages in varchar2 default 'N'
560 ) is
561
562 cursor c_person (p_effective_date date) is
563 select distinct pen.person_id
564 from ben_prtt_enrt_rslt_f pen
565 where pen.prtt_enrt_rslt_stat_cd is null
566 and pen.sspndd_flag = 'N'
567 and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP') -- not a dummy plan
568 -- cvg starts sometime before end of next month:
569 and pen.enrt_cvg_strt_dt <= add_months(p_effective_date,1)
570 -- check criteria user entered on the submit form:
571 and (pen.person_id = p_person_id or p_person_id is null)
572 and (pen.pl_id = p_pl_id or p_pl_id is null)
573 and (pen.pl_typ_id = p_pl_typ_id or p_pl_typ_id is null)
574 and (pen.pgm_id = p_pgm_id or p_pgm_id is null)
575 and pen.business_group_id+0 = p_business_group_id
576 and p_effective_date between
577 pen.effective_start_date and pen.effective_end_date
578 and (p_organization_id is null
579 or exists (select null from per_all_assignments_f
580 where person_id = pen.person_id
581 and business_group_id = p_business_group_id
582 and p_effective_date between nvl(effective_start_date,p_effective_date )
583 and nvl(effective_end_date, p_effective_date )
584 and primary_flag = 'Y'
585 and organization_id = p_organization_id ) )
586 and ( p_legal_entity_id is null
587 or exists (select null
588 from per_assignments_f paf,
589 hr_soft_coding_keyflex soft
590 where paf.person_id = pen.person_id
591 and p_effective_date
592 between paf.effective_start_date
593 and paf.effective_end_date
594 and paf.business_group_id = p_business_group_id
595 and paf.primary_flag = 'Y'
596 and soft.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
597 and soft.segment1 = to_char(p_legal_entity_id)));
598
599 --
600 -- local variable declaration.
601 --
602 l_request_id number;
603 l_proc varchar2(80) := g_package||'.process';
604 l_benefit_action_id ben_benefit_actions.benefit_action_id%type;
605 l_object_version_number ben_benefit_actions.object_version_number%type;
606 l_person_id per_people_f.person_id%type;
607 l_person_action_id ben_person_actions.person_action_id%type;
608 l_ler_id ben_ler_f.ler_id%type;
609 l_range_id ben_batch_ranges.range_id%type;
610 l_chunk_size number := 20;
611 l_threads number := 1;
612 l_start_person_action_id number := 0;
613 l_end_person_action_id number := 0;
614 l_prev_person_id number := 0;
615 rl_ret char(1);
616 skip boolean;
617 l_person_cnt number := 0;
618 l_cnt number := 0;
619 l_actn varchar2(80);
620 l_num_range number := 0;
621 l_chunk_num number := 1;
622 l_num_row number := 0;
623 l_commit number;
624 l_effective_date date;
625 l_effective_date_char varchar2(19);
626 -- premium fields:
627 l_first_day_of_month date;
628 l_first_day_of_month_char varchar2(19);
629 l_mo_num number;
630 l_yr_num number;
631
632 l_errbuf varchar2(80);
633 l_retcode number;
634 l_restart boolean;
635 l_err_message varchar2(800);
636 Begin
637 hr_utility.set_location ('Entering '||l_proc,10);
638
639 l_effective_date:=to_date(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
640 l_effective_date:=to_date(to_char(trunc(l_effective_date),'DD/MM/RRRR'),'DD/MM/RRRR');
641 -- No matter what date the user entered, make sure we process on the last day of the
642 -- month:
643 l_effective_date := last_day(l_effective_date);
644 l_effective_date_char := to_char(l_effective_date,'YYYY/MM/DD HH24:MI:SS');
645 --
646 -- Put row in fnd_sessions
647 --
648 dt_fndate.change_ses_date
649 (p_ses_date => l_effective_date,
650 p_commit => l_commit);
651 -- Load premium fields:
652 l_first_day_of_month :=
653 to_date('01/'||to_char(l_effective_date,'MM/RRRR'),'DD/MM/RRRR');
654 l_first_day_of_month_char := to_char(l_first_day_of_month,'YYYY/MM/DD HH24:MI:SS');
655 l_mo_num := to_char(l_effective_date,'MM');
656 l_yr_num := to_char(l_effective_date,'RRRR');
657 --
658 l_actn := 'Initialize the ben_batch_utils cache...';
659 ben_batch_utils.ini;
660 l_actn := 'Initialize the ben_batch_utils cache...';
661 ben_batch_utils.ini(p_actn_cd => 'PROC_INFO');
662 --
663 -- Check that all the mandatory input parameters
664 -- such as p_business_group_id, p_mode, p_effective_date
665 --
666 l_actn := 'Checking arguments...';
667 hr_utility.set_location('Checking arguments',12);
668 hr_api.mandatory_arg_error(p_api_name => g_package
669 ,p_argument => 'p_business_group_id'
670 ,p_argument_value => p_business_group_id
671 );
672 hr_api.mandatory_arg_error(p_api_name => g_package
673 ,p_argument => 'p_effective_date'
674 ,p_argument_value => p_effective_date
675 );
676 --
677 -- Get chunk_size and Thread values for multi-thread process, and check to
678 -- assure they are sensible.
679 -- chunk_size between(10 and 100). If not in range, default to 20.
680 -- threads between <1 and 100>. If not in range, default to 1
681 --
682 l_actn := 'Calling benutils.get_parameter...';
683 benutils.get_parameter(p_business_group_id => p_business_group_Id
684 ,p_batch_exe_cd => 'BENPRCON'
685 ,p_threads => l_threads
686 ,p_chunk_size => l_chunk_size
687 ,p_max_errors => g_max_errors_allowed);
688 benutils.g_benefit_action_id := p_benefit_action_id;
689 benutils.g_thread_id := 99;
690
691 --
692 -- Create benefit actions parameters in the benefit action table.
693 -- Do not create is a benefit action already exists, in other words
694 -- we are doing a restart.
695 --
696 If(p_benefit_action_id is null) then
697
698 hr_utility.set_location('p_benefit_action_id is null',14);
699 l_restart := FALSE;
700
701 ben_benefit_actions_api.create_benefit_actions
702 (p_validate => false
703 ,p_benefit_action_id => l_benefit_action_id
704 ,p_process_date => l_effective_date
705 ,p_mode_cd => 'S'
706 ,p_derivable_factors_flag => 'N'
707 ,p_validate_flag => p_validate
708 ,p_person_id => p_person_id
709 ,p_person_type_id => null
710 ,p_pgm_id => p_pgm_id
711 ,p_business_group_id => p_business_group_id
712 ,p_pl_typ_id => p_pl_typ_id
713 ,p_pl_id => p_pl_id
714 ,p_popl_enrt_typ_cycl_id => null
715 ,p_no_programs_flag => 'N'
716 ,p_no_plans_flag => 'N'
717 ,p_comp_selection_rl => p_comp_selection_rule_id
718 ,p_person_selection_rl => p_person_selection_rule_id
719 ,p_ler_id => null
720 ,p_organization_id => p_organization_id
721 ,p_benfts_grp_id => null
722 ,p_location_id => null
723 ,p_pstl_zip_rng_id => NULL
724 ,p_rptg_grp_id => NULL
725 ,p_opt_id => NULL
726 ,p_eligy_prfl_id => NULL
727 ,p_vrbl_rt_prfl_id => NULL
728 ,p_legal_entity_id => p_legal_entity_id
729 ,p_payroll_id => null
730 ,p_debug_messages_flag => p_debug_messages
731 ,p_object_version_number => l_object_version_number
732 ,p_effective_date => l_effective_date
733 ,p_request_id => fnd_global.conc_request_id
734 ,p_program_application_id => fnd_global.prog_appl_id
735 ,p_program_id => fnd_global.conc_program_id
736 ,p_program_update_date => sysdate
737 );
738 benutils.g_benefit_action_id := l_benefit_action_id;
739 --
740 -- Delete/clear ranges from ben_batch_ranges table
741 --
742 l_actn := 'Delete rows from ben_batch_ranges..';
743 hr_utility.set_location('Delete rows from ben_batch_ranges',16);
744
745 Delete from ben_batch_ranges
746 Where benefit_action_id = l_benefit_action_id;
747 --
748 -- Now lets create person actions for all the people we are going to
749 -- process in the Premium Calculation run.
750 --
751 open c_person (p_effective_date => l_effective_date);
752 l_person_cnt := 0;
753 l_cnt := 0;
754 l_actn := 'Loading person_actions table..';
755 Loop
756 fetch c_person into l_person_id;
757 Exit when c_person%notfound;
758 l_cnt := l_cnt + 1;
759
760
761 hr_utility.set_location('LAMC: person_id='||to_char(l_person_id)||
762 ' l_cnt='||to_char(l_cnt),18);
763
764 -- check person rule criteria that the user entered on the submit form.
765 skip := FALSE;
766 rl_ret := 'Y';
767 -- tar no - 13530340.6
768 Begin
769 l_err_message := null ;
770 If (p_person_selection_rule_id is not NULL) then
771 l_actn := 'Calling Ben_batch_utils.person_selection_rule...';
772 /* rl_ret := ben_batch_utils.person_selection_rule
773 (p_person_id => l_person_id
774 ,p_business_group_id => p_business_group_id
775 ,p_person_selection_rule_id=> p_person_selection_rule_id
776 ,p_effective_date => l_effective_date
777 );
778 */
779
780 prem_person_selection_rule
781 (p_person_id => l_person_id
782 ,p_business_group_id => p_business_group_id
783 ,p_person_selection_rule_id => p_person_selection_rule_id
784 ,p_effective_date => l_effective_date
785 ,p_return => rl_ret
786 ,p_err_message => l_err_message ) ;
787
788
789 if l_err_message is not null
790 then
791 Ben_batch_utils.write(p_text =>
792 '<< Person id : '||to_char(l_person_id)||' failed.'||
793 ' Reason : '|| l_err_message ||' >>' );
794 skip := TRUE;
795 g_persons_errored := g_persons_errored + 1;
796 else
797 If (rl_ret = 'N') then
798 skip := TRUE;
799 End if;
800 end if ;
801
802 End if;
803 End ;
804 -- tar no - 13530340.6
805 --
806 -- Store data into person actions table.
807 --
808 If ( not skip) then
809 hr_utility.set_location('not skip...Inserting Ben_person_actions',28);
810
811 l_actn := 'Calling Ben_person_actions_api.create_person_actions...';
812 Ben_person_actions_api.create_person_actions
813 (p_validate => false
814 ,p_person_action_id => l_person_action_id
815 ,p_person_id => l_person_id
816 ,p_ler_id => 0
817 ,p_benefit_action_id => l_benefit_action_id
818 ,p_action_status_cd => 'U'
819 ,p_chunk_number => l_chunk_num
820 ,p_object_version_number => l_object_version_number
821 ,p_effective_date => l_effective_date
822 );
823 l_num_row := l_num_row + 1;
824 l_person_cnt := l_person_cnt + 1;
825 l_end_person_action_id := l_person_action_id;
826 If l_num_row = 1 then
827 l_start_person_action_id := l_person_action_id;
828 End if;
829 If l_num_row = l_chunk_size then
830 --
831 -- Create a range of data to be multithreaded.
832 --
833 l_actn := 'Calling Ben_batch_ranges_api.create_batch_ranges(in)...';
834 Ben_batch_ranges_api.create_batch_ranges
835 (p_validate => false
836 ,p_benefit_action_id => l_benefit_action_id
837 ,p_range_id => l_range_id
838 ,p_range_status_cd => 'U'
839 ,p_starting_person_action_id => l_start_person_action_id
840 ,p_ending_person_action_id => l_end_person_action_id
841 ,p_object_version_number => l_object_version_number
842 ,p_effective_date => l_effective_date
843 );
844 l_start_person_action_id := 0;
845 l_end_person_action_id := 0;
846 l_num_row := 0;
847 l_num_range := l_num_range + 1;
848 l_chunk_num := l_chunk_num + 1;
849 End if;
850 End if;
851 End loop;
852 Close c_person;
853
854 hr_utility.set_location('l_num_row='||to_char(l_num_row),18);
855
856 If (l_num_row <> 0) then
857 l_actn := 'Calling Ben_batch_ranges_api.create_batch_ranges(Last)...';
858 Ben_batch_ranges_api.create_batch_ranges
859 (p_validate => false
860 ,p_benefit_action_id => l_benefit_action_id
861 ,p_range_id => l_range_id
862 ,p_range_status_cd => 'U'
863 ,p_starting_person_action_id => l_start_person_action_id
864 ,p_ending_person_action_id => l_end_person_action_id
865 ,p_object_version_number => l_object_version_number
866 ,p_effective_date => l_effective_date
867 );
868 l_num_range := l_num_range + 1;
869 End if;
870 Else
871 hr_utility.set_location('p_benefit_action_id is not null',30);
872 l_restart := TRUE;
873 l_benefit_action_id := p_benefit_action_id;
874 l_actn := 'Calling Ben_batch_utils.create_restart_person_actions...';
875 Ben_batch_utils.create_restart_person_actions
876 (p_benefit_action_id => p_benefit_action_id
877 ,p_effective_date => l_effective_date
878 ,p_chunk_size => l_chunk_size
879 ,p_threads => l_threads
880 ,p_num_ranges => l_num_range
881 ,p_num_persons => l_person_cnt
882 );
883 End if;
884 commit;
885 --
886 -- Now to multithread the code.
887 --
888 hr_utility.set_location('l_num_range '||to_char(l_num_range),30);
889
890 If l_num_range > 1 then
891 For l_count in 1..least(l_threads,l_num_range)-1 loop
892 --
893 l_actn := 'Submitting job to con-current manager...';
894 hr_utility.set_location('Submitting BENPRCOM to con-current manager ',32);
895
896 l_request_id := fnd_request.submit_request
897 (application => 'BEN'
898 ,program => 'BENPRCOM'
899 ,description => NULL
900 ,sub_request => FALSE
901 ,argument1 => p_validate
902 ,argument2 => l_benefit_action_id
903 ,argument3 => l_count
904 ,argument4 => l_effective_date_char
905 ,argument5 => p_business_group_id
906 ,argument6 => l_mo_num
907 ,argument7 => l_yr_num
908 ,argument8 => l_first_day_of_month_char );
909 --
910 -- Store the request id of the concurrent request
911 --
912 ben_batch_utils.g_num_processes := ben_batch_utils.g_num_processes + 1;
913 ben_batch_utils.g_processes_tbl(ben_batch_utils.g_num_processes)
914 := l_request_id;
915 End loop;
916 Elsif (l_num_range = 0 ) then
917 l_actn := 'Calling Ben_batch_utils.print_parameters...';
918 hr_utility.set_location('Calling Ben_batch_utils.print_parameters ',34);
919
920 Ben_batch_utils.print_parameters
921 (p_thread_id => 99
922 ,p_benefit_action_id => l_benefit_action_id
923 ,p_validate => p_validate
924 ,p_business_group_id => p_business_group_id
925 ,p_effective_date => l_effective_date
926 ,p_mode => null
927 ,p_comp_selection_rule_id => p_comp_selection_rule_id
928 ,p_pgm_id => p_pgm_id
929 ,p_pl_typ_id => p_pl_typ_id
930 ,p_pl_id => p_pl_id
931 ,p_popl_enrt_typ_cycl_id => null
932 ,p_person_id => p_person_id
933 ,p_person_selection_rule_id => p_person_selection_rule_id
934 ,p_person_type_id => null
935 ,p_ler_id => null
936 ,p_organization_id => p_organization_id
937 ,p_benfts_grp_id => null
938 ,p_location_id => null
939 ,p_legal_entity_id => p_legal_entity_id
940 ,p_payroll_id => null
941 );
942
943 -- Because there are other processes below, do not error if first process finds
944 -- noone to process.
945
946 Ben_batch_utils.write(p_text =>
947 '<< No Person was selected for Participant Premiums with above selection criteria >>' );
948 -- hr_utility.set_location ('BEN_91769_NOONE_TO_PROCESS',778);
949 -- fnd_message.set_name('BEN','BEN_91769_NOONE_TO_PROCESS');
950 -- fnd_message.raise_error;
951 End if;
952
953 if (l_num_range <> 0 ) then
954 l_actn := 'Calling do_multithread...';
955 hr_utility.set_location('Calling do_multithread ',34);
956 do_multithread(errbuf => errbuf
957 ,retcode => retcode
958 ,p_validate => p_validate
959 ,p_benefit_action_id => l_benefit_action_id
960 ,p_thread_id => l_threads+1
961 ,p_effective_date => l_effective_date_char
962 ,p_business_group_id => p_business_group_id
963 ,p_mo_num => l_mo_num
964 ,p_yr_num => l_yr_num
965 ,p_first_day_of_month => l_first_day_of_month_char
966 );
967 l_actn := 'Calling ben_batch_utils.check_all_slaves_finished...';
968
969 hr_utility.set_location('Calling ben_batch_utils.check_all_slaves_finished ',38);
970
971 ben_batch_utils.check_all_slaves_finished(p_rpt_flag => TRUE);
972 end if;
973 --------------------------------------------------------------------------
974 -- Now call the other two processes:
975 --------------------------------------------------------------------------
976 if p_person_id is null and p_person_selection_rule_id is null then
977 -- only process comp object premiums if no person criteria was selected
978 ben_premium_plan_concurrent.process
979 (errbuf => l_errbuf
980 ,retcode => l_retcode
981 ,p_benefit_action_id => l_benefit_action_id
982 ,p_effective_date => l_effective_date_char
983 ,p_validate => p_validate
984 ,p_pgm_id => p_pgm_id
985 ,p_pl_typ_id => p_pl_typ_id
986 ,p_pl_id => p_pl_id
987 ,p_business_group_id => p_business_group_id
988 ,p_comp_selection_rule_id => p_comp_selection_rule_id
989 ,p_debug_messages => p_debug_messages
990 ,p_mo_num => l_mo_num
991 ,p_yr_num => l_yr_num
992 ,p_first_day_of_month => l_first_day_of_month_char
993 ,p_threads => l_threads
994 ,p_chunk_size => l_chunk_size
995 ,p_max_errors => g_max_errors_allowed
996 ,p_restart => l_restart);
997
998 end if;
999 ben_prem_prtt_credits_mo.main
1000 (p_validate => p_validate
1001 ,p_person_id => p_person_id
1002 ,p_person_selection_rule_id => p_person_selection_rule_id
1003 ,p_comp_selection_rule_id => p_comp_selection_rule_id
1004 ,p_pgm_id => p_pgm_id
1005 ,p_pl_typ_id => p_pl_typ_id
1006 ,p_pl_id => p_pl_id
1007 ,p_organization_id => p_organization_id
1008 ,p_legal_entity_id => p_legal_entity_id
1009 ,p_business_group_id => p_business_group_id
1010 ,p_mo_num => l_mo_num
1011 ,p_yr_num => l_yr_num
1012 ,p_first_day_of_month => l_first_day_of_month
1013 ,p_effective_date => l_effective_date);
1014 ben_batch_utils.end_process(p_benefit_action_id => l_benefit_action_id
1015 ,p_person_selected => l_person_cnt
1016 ,p_business_group_id => p_business_group_id);
1017 submit_all_reports;
1018 hr_utility.set_location ('Leaving '||l_proc,70);
1019 --
1020 Exception
1021 when others then
1022 ben_batch_utils.rpt_error(p_proc => l_proc
1023 ,p_last_actn => l_actn
1024 ,p_rpt_flag => TRUE );
1025 --
1026 benutils.write(p_text => fnd_message.get);
1027 benutils.write(p_text => sqlerrm);
1028 benutils.write(p_text => 'Big Error Occured');
1029 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
1030 If (l_num_range > 0) then
1031 ben_batch_utils.check_all_slaves_finished(p_rpt_flag => TRUE);
1032 ben_batch_utils.end_process(p_benefit_action_id => l_benefit_action_id
1033 ,p_person_selected => l_person_cnt
1034 ,p_business_group_id => p_business_group_id
1035 ) ;
1036 End if;
1037 hr_utility.set_location ('HR_6153_ALL_PROCEDURE_FAIL',689);
1038 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1039 fnd_message.set_token('PROCEDURE', l_proc);
1040 fnd_message.set_token('STEP', l_actn );
1041 fnd_message.raise_error;
1042 End process;
1043 --
1044 end ben_premium_concurrent; -- End of Package.