[Home] [Help]
PACKAGE BODY: APPS.BEN_CONC_REPORTS
Source
1 package body ben_conc_reports as
2 /*$Header: becncrep.pkb 120.2.12010000.2 2008/09/18 10:39:25 pvelvano ship $*/
3 --
4 /*
5 Name
6 Benefits Concurrent reports process
7 Purpose
8 This is a wrapper batch process that accepts parameters from conc request window
9 and submits different reports.
10 History
11 Version Date Author Comment
12 -------+----------+----------+------------------------------------------------
13 115.0 29-SEP-02 nhunur Created
14 115.7 25-oct-02 nhunur added code for service area of ENRKIT.
15 115.8 28-oct-02 nhunur added code to exclude COMP plan types.
16 115.9 06-Nov-02 hnarayan bug 2643361 fixed cursors c_person in enrkit
17 and consmrep to pickup the address row as per
18 effective date in the svc_area_id sub query
19 115.10 12-Nov-02 nhunur Bug - 2665181 added format mask for cvg end dt
20 , cvg strt dt. Also made cvg end dt non mandatory.
21 115.12 30-Dec-2002 mmudigon NOCOPY
22 115.13 09-Sep-03 rpgupta Grade step
23 Changed cursor c_person of create_bensmrep_ranges
24 and create_enrkit_ranges to exclude GSP objects
25 and LE's
26 115.14 27-Sep-04 abparekh Bug 3905852 Changed format mask of dates passed to
27 reports in call to submit_request.
28 115.15 04-Jun-06 swjain Bug 5331889 - passed person_id as input param
29 in rep_person_selection_rule
30 115.16 07-Dec-06 gsehgal bug 5663102 Query changed remove option type code
31 COMP.
32 ------------------------------------------------------------------------------
33 */
34 --
35 g_package varchar2(80) := ' BECNCREP - ben_conc_reports';
36 --
37 g_person_cnt number := 0;
38 g_person_actn_cnt number := 0;
39 g_error_person_cnt number := 0;
40 -- Global structure to hold the parameters that are passed into the master
41 -- process
42 --
43 type g_processes_table is table of number index by binary_integer;
44 g_processes_rec g_processes_table;
45 --
46 --
47 type g_parm_list is record
48 (report_name varchar2(80)
49 ,benefit_action_id number(15)
50 ,effective_date varchar2(30)
51 ,business_group_id number(15)
52 ,person_id number(15)
53 ,person_type_id number(15)
54 ,person_sel_rl number(15)
55 ,organization_id number(15)
56 ,location_id number(15)
57 ,ler_id number(15)
58 ,pgm_id number(15)
59 ,pl_nip_id number(15)
60 ,plan_in_pgm_flag varchar2(30)
61 ,comp_selection_rl number(15)
62 ,lf_evt_ocrd_dt varchar2(30) -- date
63 ,rptg_grp number(15)
64 ,svc_area_id number(15)
65 ,assgn_type varchar2(30)
66 ,cvg_strt_dt varchar2(30) -- date
67 ,cvg_end_dt varchar2(30) -- date
68 ,ben_sel_flag varchar2(30) -- these 7 flags will be overloaded for other reports
69 ,flx_sum_flag varchar2(30)
70 ,actn_items_flag varchar2(30)
71 ,cov_dpnt_flag varchar2(30)
72 ,prmy_care_flag varchar2(30)
73 ,beneficaries_flag varchar2(30)
74 ,certifications_flag varchar2(30)
75 ,disp_epe_flxfld_flag varchar2(30)
76 ,disp_flex_fields varchar2(30));
77 --
78 g_parm g_parm_list;
79 --
80 g_rec ben_type.g_report_rec;
81 g_proc_rec ben_type.g_batch_proc_rec;
82 g_strt_tm_numeric number;
83 g_end_tm_numeric number;
84 --
85 g_num_processes number;
86 g_threads number;
87 g_chunk_size number;
88 g_max_errors number;
89 g_num_ranges number;
90 -- ----------------------------------------------------------------------------
91 -- -----------------------< initialize_globals >-------------------------------
92 -- ----------------------------------------------------------------------------
93 --
94 procedure initialize_globals is
95 begin
96 --
97 --fnd_file.put_line(fnd_file.log,'Inside initialise globals ');
98 g_person_cnt := 0;
99 g_person_actn_cnt := 0;
100 g_error_person_cnt := 0;
101 --
102 g_strt_tm_numeric := null;
103 g_proc_rec.business_group_id := null;
104 g_proc_rec.strt_dt := null;
105 g_proc_rec.strt_tm := null;
106 --
107 g_parm.benefit_action_id := null;
108 g_parm.effective_date := null;
109 g_parm.business_group_id := null;
110 g_parm.person_id := null;
111 g_parm.person_type_id := null;
112 g_parm.person_sel_rl := null;
113 g_parm.organization_id := null;
114 g_parm.location_id := null;
115 g_parm.ler_id := null;
116 g_parm.pgm_id := null;
117 g_parm.pl_nip_id := null;
118 g_parm.plan_in_pgm_flag := null;
119 g_parm.comp_selection_rl := null;
120 g_parm.lf_evt_ocrd_dt := null;
121 --
122 -- fnd_file.put_line(fnd_file.log,'half way ');
123 g_parm.rptg_grp := null;
124 g_parm.svc_area_id := null ;
125 g_parm.assgn_type := null ;
126 g_parm.cvg_strt_dt := null;
127 g_parm.cvg_end_dt := null;
128 g_parm.ben_sel_flag := null;
129 g_parm.flx_sum_flag := null;
130 g_parm.actn_items_flag := null;
131 g_parm.cov_dpnt_flag := null;
132 g_parm.prmy_care_flag := null;
133 g_parm.beneficaries_flag := null;
134 g_parm.certifications_flag := null ;
135 g_parm.disp_flex_fields := null ;
136 g_parm.disp_epe_flxfld_flag := null ;
137 --
138 g_num_processes := 0 ;
139 g_threads := 3 ;
140 g_chunk_size := 10 ;
141 g_max_errors := 20 ;
142 g_num_ranges := 0 ;
143 --fnd_file.put_line(fnd_file.log,'Leaving initialise globals ');
144
145 end initialize_globals;
146 --
147 -- ----------------------------------------------------------------------------
148 -- -----------------------< person_error_cnt >---------------------------------
149 -- ----------------------------------------------------------------------------
150 procedure person_error_cnt is
151 --
152 error_limit exception;
153 --
154 l_proc varchar2(80) := g_package || '.person_error_cnt';
155 --
156 begin
157 --
158 hr_utility.set_location('Entering : ' || l_proc, 10);
159 --
160 -- Increment the error count
161 --
162 g_error_person_cnt := g_error_person_cnt + 1;
163 --
164 hr_utility.set_location('Leaving : ' || l_proc, 10);
165 --
166 end person_error_cnt;
167 --
168 -- ----------------------------------------------------------------------------
169 -- -----------------------< print_parameters >---------------------------------
170 -- ----------------------------------------------------------------------------
171 --
172 procedure print_parameters is
173 --
174 l_proc varchar2(80) := g_package || '.print_parameters';
175 --
176 begin
177 --
178 hr_utility.set_location('Entering : ' || l_proc, 10);
179 --
180 if fnd_global.conc_request_id = -1 then
181 return;
182 end if;
183 --
184 fnd_file.put_line(which => fnd_file.log
185 ,buff => 'Runtime Parameters');
186 --
187 fnd_file.put_line(which => fnd_file.log
188 ,buff => '---------------------------');
189 --
190 fnd_file.put_line(which => fnd_file.log
191 ,buff => 'Benefit Action ID : '||
192 benutils.iftrue
193 (p_expression => g_parm.benefit_action_id is null
194 ,p_true => 'NONE'
195 ,p_false => g_parm.benefit_action_id));
196 --
197 fnd_file.put_line(which => fnd_file.log
198 ,buff => 'Effective Date : '||
199 g_parm.effective_date );
200 -- to_char(g_parm.effective_date,'DD-MON-YYYY'));
201 --
202 fnd_file.put_line(which => fnd_file.log
203 ,buff => 'Business Group ID : '||
204 g_parm.business_group_id);
205 --
206 fnd_file.put_line(which => fnd_file.log
207 ,buff => 'Person ID : ' ||
208 benutils.iftrue
209 (p_expression => g_parm.person_id is null
210 ,p_true => 'All'
211 ,p_false => g_parm.person_id));
212 --
213 fnd_file.put_line(which => fnd_file.log
214 ,buff => 'Person Type ID : ' ||
215 benutils.iftrue
216 (p_expression => g_parm.person_type_id is null
217 ,p_true => 'All'
218 ,p_false => g_parm.person_type_id));
219 --
220 fnd_file.put_line(which => fnd_file.log
221 ,buff => 'Person Selection Rule : ' ||
222 benutils.iftrue
223 (p_expression => g_parm.person_sel_rl is null
224 ,p_true => 'All'
225 ,p_false => g_parm.person_sel_rl));
226 --
227 fnd_file.put_line(which => fnd_file.log
228 ,buff => 'Comp Object Selection Rule : ' ||
229 benutils.iftrue
230 (p_expression => g_parm.comp_selection_rl is null
231 ,p_true => 'All'
232 ,p_false => g_parm.comp_selection_rl));
233 --
234 fnd_file.put_line(which => fnd_file.log
235 ,buff => 'Organization ID : ' ||
236 benutils.iftrue
237 (p_expression => g_parm.organization_id is null
238 ,p_true => 'All'
239 ,p_false => g_parm.organization_id));
240 --
241 fnd_file.put_line(which => fnd_file.log
242 ,buff => 'Location ID : ' ||
243 benutils.iftrue
244 (p_expression => g_parm.location_id is null
245 ,p_true => 'All'
246 ,p_false => g_parm.location_id));
247 --
248 fnd_file.put_line(which => fnd_file.log
249 ,buff => 'Life Event Reason ID : ' ||
250 benutils.iftrue
251 (p_expression => g_parm.ler_id is null
252 ,p_true => 'All'
253 ,p_false => g_parm.ler_id));
254 --
255 fnd_file.put_line(which => fnd_file.log
256 ,buff => 'Life Event Occured Date : ' ||
257 benutils.iftrue
258 (p_expression => g_parm.lf_evt_ocrd_dt is null
259 ,p_true => 'All'
260 ,p_false => g_parm.lf_evt_ocrd_dt));
261 --
262 fnd_file.put_line(which => fnd_file.log
263 ,buff => 'Program ID : '||
264 benutils.iftrue
265 (p_expression => g_parm.pgm_id is null
266 ,p_true => 'All'
267 ,p_false => g_parm.pgm_id));
268 --
269 fnd_file.put_line(which => fnd_file.log,
270 buff => 'Plan ID : '||
271 benutils.iftrue
272 (p_expression => g_parm.pl_nip_id is null
273 ,p_true => 'All'
274 ,p_false => g_parm.pl_nip_id));
275 --
276 fnd_file.put_line(which => fnd_file.log,
277 buff => 'Service Area : '||
278 benutils.iftrue
279 (p_expression => g_parm.svc_area_id is null
280 ,p_true => 'All'
281 ,p_false => g_parm.svc_area_id));
282 --
283 fnd_file.put_line(which => fnd_file.log,
284 buff => 'Assignment Type : '||
285 benutils.iftrue
286 (p_expression => g_parm.assgn_type is null
287 ,p_true => 'All'
288 ,p_false => g_parm.assgn_type));
289 --
290 fnd_file.put_line(which => fnd_file.log,
291 buff => 'Coverage Start Date : '||
292 benutils.iftrue
293 (p_expression => g_parm.cvg_strt_dt is null
294 ,p_true => 'All'
295 ,p_false => g_parm.cvg_strt_dt));
296 --
297 fnd_file.put_line(which => fnd_file.log,
298 buff => 'Coverage End Date : '||
299 benutils.iftrue
300 (p_expression => g_parm.cvg_end_dt is null
301 ,p_true => 'All'
302 ,p_false => g_parm.cvg_end_dt));
303 --
304 fnd_file.put_line(which => fnd_file.log,
305 buff => 'Is plan in program? : '||
306 g_parm.plan_in_pgm_flag);
307 --
308 fnd_file.put_line(which => fnd_file.log,
309 buff => 'Benefits Selection : '||
310 g_parm.ben_sel_flag);
311 --
312 fnd_file.put_line(which => fnd_file.log,
313 buff => 'Flex Credit Summary : '||
314 g_parm.flx_sum_flag);
315 --
316 fnd_file.put_line(which => fnd_file.log,
317 buff => 'Action Items Flag : '||
318 g_parm.actn_items_flag);
319 --
320 fnd_file.put_line(which => fnd_file.log,
321 buff => 'Covered Dependent Flag : '||
322 g_parm.cov_dpnt_flag);
323 --
324 fnd_file.put_line(which => fnd_file.log,
325 buff => 'Primary Care Provider Flag : '||
326 g_parm.prmy_care_flag);
327 --
328 fnd_file.put_line(which => fnd_file.log,
329 buff => 'Beneficiaries Flag : '||
330 g_parm.beneficaries_flag);
331 --
332 fnd_file.put_line(which => fnd_file.log,
333 buff => 'Certifications Flag : '||
334 g_parm.certifications_flag);
335 --
336 fnd_file.put_line(which => fnd_file.log,
337 buff => 'Display Flexfields Flag : '||
338 g_parm.disp_flex_fields);
339
340 --
341 fnd_file.put_line(which => fnd_file.log,
342 buff => 'Elec Choice Flexfields Flag: '||
343 g_parm.disp_epe_flxfld_flag );
344
345 hr_utility.set_location('Leaving : ' || l_proc, 10);
346 --
347 --
348 end print_parameters;
349 --
350 --
351 function verify_person_type_id(p_person_id in number,
352 p_person_type_id in number)
353 return boolean is
354 --
355 cursor c1 is
356 select 'Y'
357 from per_all_people_f ppf, per_person_types ppt
358 where ppf.person_id = p_person_id
359 and ppf.person_type_id = p_person_type_id
360 and ppf.business_group_id = g_parm.business_group_id
361 and g_parm.effective_date between ppf.effective_start_date
362 and ppf.effective_end_date
363 and ppf.person_type_id = ppt.person_type_id
364 and ppt.business_group_id = g_parm.business_group_id
365 and ppt.active_flag = 'Y';
366 --
367 l_success varchar2(30) := null;
368 --
369 begin
370 --
371 if p_person_type_id is null then
372 --
373 return(true);
374 --
375 end if;
376 --
377 open c1;
378 fetch c1 into l_success;
379 close c1;
380 --
381 if l_success = 'Y' then
382 --
383 return(true);
384 --
385 else
386 --
387 return(false);
388 --
389 end if;
390 --
391 end verify_person_type_id;
392 --
393 -- ----------------------------------------------------------------------------
394 -- -------------------------< check_business_rules >---------------------------
395 -- ----------------------------------------------------------------------------
396 --
397 procedure check_business_rules is
398 --
399 cursor c1 is
400 select null
401 from per_all_people_f ppf, per_person_types ppt
402 where ppf.person_id = g_parm.person_id
403 and ppf.person_type_id = g_parm.person_type_id
404 and ppf.business_group_id = g_parm.business_group_id
405 and g_parm.effective_date between ppf.effective_start_date
406 and ppf.effective_end_date
407 and ppf.person_type_id = ppt.person_type_id
408 and ppt.business_group_id = g_parm.business_group_id
409 and ppt.active_flag = 'Y';
410 --
411 l_person_type varchar2(30);
412 l_dummy varchar2(30);
413 --
414 l_proc varchar2(80) := g_package || '.check_business_rules';
415 --
416 begin
417 --
418 -- fnd_file.put_line(fnd_file.log,'Inside check rules ');
419 hr_utility.set_location ('Entering '||l_proc,10);
420 --
421 -- This procedure checks validity of parameters that have been passed
422 --
423 -- Check if mandatory arguments have been stipulated
424 --
425 hr_api.mandatory_arg_error(p_api_name => l_proc,
426 p_argument => 'p_business_group_id',
427 p_argument_value => g_parm.business_group_id);
428 --
432 --
429 hr_api.mandatory_arg_error(p_api_name => l_proc,
430 p_argument => 'p_effective_date',
431 p_argument_value => g_parm.effective_date);
433 --
434 -- Business Rule Checks
435 --
436 -- p_person_selection_rule_id and p_person_id are mutually exclusive
437 --
438 if g_parm.person_id is not null and
439 g_parm.person_sel_rl is not null then
440 fnd_message.set_name('BEN','BEN_91745_RULE_AND_PERSON');
441 fnd_message.set_token('PROC',l_proc);
442 fnd_message.set_token('PERSON_ID',to_char(g_parm.person_id));
443 fnd_message.set_token('PER_SELECT_RL',
444 'person_selection_rule :'||g_parm.person_sel_rl);
445 fnd_file.put_line(fnd_file.log, fnd_message.get );
446 fnd_message.raise_error;
447 end if;
448 --
449 -- p_person_id must be of p_person_type_id specified
450 --
451 if g_parm.person_id is not null and
452 g_parm.person_type_id is not null then
453 --
454 -- Make sure person is of the person type specified
455 --
456 if not(verify_person_type_id(p_person_id => g_parm.person_id,
457 p_person_type_id => g_parm.person_type_id)) then
458 --
459 fnd_message.set_name('BEN','BEN_91748_PERSON_TYPE');
460 fnd_message.set_token('PROC',l_proc);
461 fnd_message.set_token('PERSON_ID',to_char(g_parm.person_id));
462 fnd_message.set_token('PER_TYPE_ID',to_char(g_parm.person_type_id));
463 fnd_file.put_line(fnd_file.log, fnd_message.get );
464 fnd_message.raise_error;
465 --
466 end if;
467 --
468 end if;
469 --
470 -- If a plan is specified as not in a program then the pgm_id should be null
471 --
472 /*
473 if g_parm.plan_in_pgm_flag = 'N' and
474 g_parm.pgm_id is not null then
475 --
476 fnd_message.set_name('BEN', 'BEN_92164_PLN_NIP_PGM_NULL');
477 -- If you specify a plan as not in a program then the program should be blank.
478 fnd_message.set_token('PROC',l_proc);
479 fnd_message.raise_error;
480 --
481 end if;
482 --
483 if g_parm.plan_in_pgm_flag = 'Y' and
484 g_parm.pl_nip_id is not null then
485 --
486 fnd_message.set_name('BEN', 'BEN_92164_PLN_NIP_PGM_NULL');
487 -- If you specify a plan as not in a program then the program should be blank.
488 fnd_message.set_token('PROC',l_proc);
489 fnd_message.raise_error;
490 --
491 end if;
492 */
493 --
494 if g_parm.pgm_id is not null and
495 g_parm.pl_nip_id is not null then
496 --
497 fnd_message.set_name('BEN', 'BEN_93247_PLN_NIP_PGM_NULL');
498 -- If you specify a plan as not in a program then the program should be blank.
499 fnd_message.set_token('PROC',l_proc);
500 fnd_file.put_line(fnd_file.log, fnd_message.get );
501 fnd_message.raise_error;
502 --
503 end if;
504 --
505 if ( g_parm.report_name = 'BEENRKIT' and
506 ( g_parm.cvg_strt_dt is not null or g_parm.cvg_end_dt is not null )) then
507 --
508 fnd_message.set_name('BEN', 'BEN_93245_INVALID_PARM_VALUE');
509 fnd_message.set_token('PROC',l_proc);
510 fnd_file.put_line(fnd_file.log, fnd_message.get );
511 fnd_message.raise_error;
512 --
513 end if;
514 --
515 if ( g_parm.report_name = 'BENSMREP' and
516 ( g_parm.disp_flex_fields is not null or g_parm.disp_epe_flxfld_flag is not null )) then
517 --
518 fnd_message.set_name('BEN', 'BEN_93246_INVALID_PARM_VALUE');
519 fnd_message.set_token('PROC',l_proc);
520 fnd_file.put_line(fnd_file.log, fnd_message.get );
521 --
522 end if;
523
524 --
525 -- If cvg start date is specified then cvg end date must be specified
526 --
527 if ((g_parm.cvg_strt_dt is not null and g_parm.cvg_end_dt is null )
528 or (g_parm.cvg_end_dt is not null and g_parm.cvg_strt_dt is null )) then
529 --
530 fnd_message.set_name('BEN', 'BEN_93237_CVG_START_END_DT');
531 fnd_message.set_token('PROC',l_proc);
532 fnd_file.put_line(fnd_file.log, fnd_message.get );
533 fnd_message.raise_error;
534 --
535 end if;
536 --
537 hr_utility.set_location ('Leaving '||l_proc,10);
538 --
539 end check_business_rules;
540 --
541 -- ==================================================================================
542 -- << Procedure: rep_person_selection_rule >>
543 -- Description:
544 -- this procedure is called from 'process'. It calls the person selection rule.
545 -- ==================================================================================
546 procedure rep_person_selection_rule
547 (p_person_id in Number
548 ,p_business_group_id in Number
549 ,p_person_selection_rule_id in Number
550 ,p_effective_date in Date
551 ,p_batch_flag in Boolean default FALSE
552 ,p_return in out nocopy varchar2
553 ,p_err_message in out nocopy varchar2 ) as
554
555 Cursor c1 is
556 Select assignment_id
557 From per_assignments_f paf
558 Where paf.person_id = p_person_id
559 and paf.assignment_type <> 'C'
560 And paf.primary_flag = 'Y'
561 And paf.business_group_id = p_business_group_id
562 And p_effective_date between
563 paf.effective_start_date and paf.effective_end_date ;
564 --
565 l_proc varchar2(80) := g_package||'.rep_person_selection_rule';
566 l_outputs ff_exec.outputs_t;
567 l_return varchar2(30);
568 l_assignment_id number;
569 l_actn varchar2(80);
570 value_exception exception ;
571 Begin
575 --
572 hr_utility.set_location ('Entering '||l_proc,10);
573 --
574 -- Get assignment ID form per_assignments_f table.
576 l_actn := 'Opening C1 Assignment cursor...';
577 open c1;
578 fetch c1 into l_assignment_id;
579 If c1%notfound then
580 raise ben_batch_utils.g_record_error;
581 End if;
582 close c1;
583 -- Call formula initialise routine
584 --
585 l_actn := 'Calling benutils.formula procedure...';
586
587 l_outputs := benutils.formula
588 (p_formula_id => p_person_selection_rule_id
589 ,p_effective_date => p_effective_date
590 ,p_business_group_id => p_business_group_id
591 ,p_assignment_id => l_assignment_id
592 ,p_param1 => 'BEN_IV_PERSON_ID' -- Bug 5331889
593 ,p_param1_value => to_char(p_person_id));
594 p_return := l_outputs(l_outputs.first).value;
595 --
596 -- fnd_file.put_line(fnd_file.log, to_char(l_assignment_id)||' -> ' || p_return );
597 l_actn := 'Evaluating benutils.formula return...';
598 --
599 If upper(p_return) not in ('Y', 'N') then
600 Raise value_exception ;
601 End if;
602 --
603 hr_utility.set_location ('Leaving '||l_proc,10);
604 Exception
605 When ben_batch_utils.g_record_error then
606 p_return := 'N' ;
607 fnd_message.set_name('BEN','BEN_91698_NO_ASSIGNMENT_FND');
608 fnd_message.set_token('ID' ,to_char(p_person_id) );
609 fnd_message.set_token('PROC',l_proc ) ;
610 p_err_message := fnd_message.get ;
611
612 When value_exception then
613 p_return := 'N' ;
614 fnd_message.set_name('BEN','BEN_91329_FORMULA_RETURN');
615 fnd_message.set_token('RL','person_selection_rule_id :'||p_person_selection_rule_id);
616 fnd_message.set_token('PROC',l_proc ) ;
617 p_err_message := fnd_message.get ;
618
619 when others then
620 p_return := 'N' ;
621 p_err_message := 'A unhandled exception has been raised while processing Person : '||to_char(p_person_id)
622 ||' in package : '|| l_proc ||'.';
623
624 End rep_person_selection_rule;
625 --
626 -- ============================================================================
627 -- << comp_selection_Rule >>
628 -- ============================================================================
629 --
630 function comp_selection_rule
631 (p_person_id in number
632 ,p_business_group_id in number
633 ,p_pgm_id in number
634 ,p_pl_id in number
635 ,p_pl_typ_id in number
636 ,p_opt_id in number
637 ,p_ler_id in number
638 ,p_oipl_id in number
639 ,p_comp_selection_rule_id in number
640 ,p_effective_date in date
641 ) return char is
642 cursor c1 is
643 select assignment_id,organization_id
644 from per_assignments_f paf
645 where paf.person_id = p_person_id
646 and paf.assignment_type <> 'C'
647 and paf.primary_flag = 'Y'
648 and paf.business_group_id = p_business_group_id
649 and p_effective_date between
650 paf.effective_start_date and paf.effective_end_date;
651
652 l_proc varchar2(80) := g_package||' .comp_selection_rule';
653 l_outputs ff_exec.outputs_t;
654 l_return varchar2(30);
655 l_assignment_id number;
656 l_organization_id number;
657 l_step integer;
658 asg_record_error exception;
659 wrong_output_error exception;
660 begin
661 l_step := 10;
662 hr_utility.set_location ('Entering '||l_proc,10);
663 --
664 -- Get assignment ID,organization_id form per_assignments_f table.
665 --
666 open c1;
667 fetch c1 into l_assignment_id,l_organization_id;
668 if c1%notfound then
669 close c1;
670 ben_batch_utils.rpt_error(p_proc => l_proc,
671 p_last_actn => 'Step = '||to_char(l_step),p_rpt_flag => TRUE);
672 fnd_message.set_name('BEN','BEN_91698_NO_ASSIGNMENT_FND');
673 fnd_message.set_token('PROC',l_proc);
674 fnd_message.set_token('ID' , to_char(p_person_id));
675 raise asg_record_error;
676 else
677 close c1;
678 end if;
679
680 -- Call formula initialise routine
681 --
682 l_outputs := benutils.formula
683 (p_formula_id => p_comp_selection_rule_id
684 ,p_effective_date => p_effective_date
685 ,p_pgm_id => p_pgm_id
686 ,p_pl_id => p_pl_id
687 ,p_pl_typ_id => p_pl_typ_id
688 ,p_opt_id => p_opt_id
689 ,p_ler_id => p_ler_id
690 ,p_business_group_id => p_business_group_id
691 ,p_assignment_id => l_assignment_id
692 ,p_organization_id => l_organization_id
693 ,p_jurisdiction_code => null);
694
695 l_return := l_outputs(l_outputs.first).value;
696 l_step := 30;
697 if upper(l_return) not in ('Y', 'N') then
698 --
699 ben_batch_utils.rpt_error(p_proc => l_proc,
700 p_last_actn => 'Step = '||to_char(l_step),p_rpt_flag => TRUE);
701 fnd_message.set_name('BEN','BEN_91329_FORMULA_RETURN');
705 end if;
702 fnd_message.set_token('RL','formula_id :'||p_comp_selection_rule_id);
703 fnd_message.set_token('PROC',l_proc);
704 raise wrong_output_error;
706 return l_return;
707 hr_utility.set_location ('Leaving '||l_proc,10);
708 exception
709 When asg_record_error then
710 ben_batch_utils.rpt_error(p_proc => l_proc,
711 p_last_actn => 'Step = '||to_char(l_step),p_rpt_flag => TRUE);
712 When wrong_output_error then
713 ben_batch_utils.rpt_error(p_proc => l_proc,
714 p_last_actn => 'Step = '||to_char(l_step),p_rpt_flag => TRUE);
715 when others then
716 ben_batch_utils.rpt_error(p_proc => l_proc,
717 p_last_actn => 'Step = '||to_char(l_step),p_rpt_flag => TRUE);
718 end comp_selection_rule;
719 --
720 -- ----------------------------------------------------------------------------
721 -- -----------------------< write_logfile >------------------------------------
722 -- ----------------------------------------------------------------------------
723 --
724 procedure write_logfile is
725 --
726 l_proc varchar2(80) := g_package || '.write_logfile';
727 --
728 begin
729 --
730 hr_utility.set_location('Entering : ' || l_proc, 10);
731 --
732 benutils.write(p_text => benutils.g_banner_minus);
733 benutils.write(p_text => 'Batch Process Statistical Information');
734 benutils.write(p_text => benutils.g_banner_minus);
735 benutils.write(p_text => 'People processed : ' || g_person_cnt);
736 benutils.write(p_text => 'People errored : ' || g_error_person_cnt);
737 benutils.write(p_text => benutils.g_banner_minus);
738 --
739 benutils.write_table_and_file(p_table => true
740 ,p_file => true);
741 commit;
742 --
743 hr_utility.set_location('Leaving : ' || l_proc, 10);
744 --
745 exception
746 --
747 when others then
748 --
749 benutils.write(p_text => sqlerrm);
750 fnd_message.set_name('BEN','BEN_91663_BENMNGLE_LOGGING');
751 fnd_message.set_token('PROC',l_proc);
752 benutils.write(p_text => fnd_message.get);
753 fnd_message.raise_error;
754 --
755 end write_logfile;
756 --
757 -- ----------------------------------------------------------------------------
758 -- ---------------------------< create_actions_ranges >------------------------
759 -- ----------------------------------------------------------------------------
760 --
761 -- This procedure creates person actions and batch ranges based on the chunk
762 -- size. The in-out parameters keep track of the person action ids created.
763 --
764 procedure create_actions_ranges
765 (p_person_id in number default null
766 ,p_ler_id in number default null
767 ,p_start_person_action_id in out nocopy number
768 ,p_ending_person_action_id in out nocopy number) is
769 --
770 l_person_ok varchar2(1) := 'Y';
771 l_person_action_id number;
772 l_object_version_number number;
773 l_range_id number;
774 --
775 l_proc varchar2(80) := g_package || '.create_actions_ranges';
776 --
777 rl_ret char(1);
778 skip boolean;
779 l_err_message varchar2(2000);
780 l_actn varchar2(2000);
781 begin
782 --
783 hr_utility.set_location('Entering : ' || l_proc, 10);
784 --
785 skip := FALSE;
786 rl_ret := 'Y';
787 --
788 l_err_message := null ;
789 --
790 if g_parm.person_sel_rl is not null then
791 --
792 rep_person_selection_rule
793 (p_person_id => p_person_id
794 ,p_business_group_id => g_parm.business_group_id
795 ,p_person_selection_rule_id => g_parm.person_sel_rl
796 ,p_effective_date => g_parm.effective_date
797 ,p_return => rl_ret
798 ,p_err_message => l_err_message ) ;
799
800 l_actn := 'After call to person selection rule ...';
801 if l_err_message is not null
802 then
803 Ben_batch_utils.write(p_text =>
804 '<< Person id : '||to_char(p_person_id)||' failed.'||
805 ' Reason : '|| l_err_message ||' >>' );
806 skip := TRUE;
807 else
808 If (rl_ret = 'N') then
809 skip := TRUE;
810 End if;
811 end if;
812 end if;
813 --
814 -- fnd_file.put_line(fnd_file.log, ' after person selection rule ');
815 -- Create a person action only if the person passes the person selection rule
816 If ( not skip) then
817 --
818 hr_utility.set_location('not skip...Inserting Ben_person_actions',28);
819 -- fnd_file.put_line(fnd_file.log, 'not skip...Inserting Ben_person_actions');
820 --
821 l_actn := 'Create person actions ...';
822 ben_person_actions_api.create_person_actions(
823 p_validate => false
824 ,p_person_action_id => l_person_action_id
825 ,p_person_id => p_person_id
826 ,p_ler_id => p_ler_id
827 ,p_benefit_action_id => g_parm.benefit_action_id
828 ,p_action_status_cd => 'U'
829 ,p_object_version_number => l_object_version_number
830 ,p_effective_date => g_parm.effective_date);
831
832 g_person_actn_cnt := g_person_actn_cnt + 1;
833
834 if mod(g_person_actn_cnt, g_chunk_size) = 1 or g_chunk_size = 1
835 then
836 p_start_person_action_id := l_person_action_id;
837 end if;
838 --
842 if mod(g_person_actn_cnt, g_chunk_size) = 0 or g_chunk_size = 1 then
839 p_ending_person_action_id := l_person_action_id;
840 --
841 -- fnd_file.put_line(fnd_file.log, 'after we get start , end action ids');
843 --
844 -- fnd_file.put_line(fnd_file.log, ' before create_batch_ranges');
845 ben_batch_ranges_api.create_batch_ranges
846 (p_validate => FALSE
847 ,p_effective_date => g_parm.effective_date
848 ,p_benefit_action_id => g_parm.benefit_action_id
849 ,p_range_id => l_range_id
850 ,p_range_status_cd => 'U'
851 ,p_starting_person_action_id => p_start_person_action_id
852 ,p_ending_person_action_id => p_ending_person_action_id
853 ,p_object_version_number => l_object_version_number);
854 --
855 g_num_ranges := g_num_ranges + 1;
856 --
857 -- fnd_file.put_line(fnd_file.log, ' after create_batch_ranges');
858 end if;
859 else
860 -- persons excluded by the selection rule report them on the audit log
861 l_actn := 'Print person header information ...';
862 ben_batch_utils.person_header
863 (p_person_id => p_person_id
864 ,p_business_group_id => g_parm.business_group_id
865 ,p_effective_date => g_parm.effective_date );
866 fnd_file.put_line(fnd_file.log, ' persons excluded by the selection rule');
867 end if;
868 --
869 hr_utility.set_location('Leaving : ' || l_proc, 10);
870 -- fnd_file.put_line(fnd_file.log, ' Leaving : create_actions_ranges ');
871 --
872 exception
873 when others then
874 fnd_file.put_line(fnd_file.log, sqlerrm || ' ' || sqlcode);
875 raise;
876 --
877 end create_actions_ranges;
878 --
879 -- ==================================================================================
880 -- << Procedure: create_bensmrep_ranges >>
881 -- Description:
882 -- Benefits Confirmation Summary report sub process
883 -- ==================================================================================
884 --
885 procedure create_bensmrep_ranges is
886 -- Cursor for selecting the persons for the report based on the
887 -- parameters in the wrapper concurrent program
888 --
889 cursor c_person is
890 select distinct pen.person_id
891 from ben_prtt_enrt_rslt_f pen , ben_pl_typ_f ptyp ,
892 ben_per_in_ler pil
893 where pen.prtt_enrt_rslt_stat_cd is null
894 and pen.sspndd_flag = 'N' /* unsuspended enrollments */
895 and (pen.person_id = g_parm.person_id or g_parm.person_id is null)
896 and (pen.pl_id = g_parm.pl_nip_id or g_parm.pl_nip_id is null)
897 and (pen.pgm_id = g_parm.pgm_id or g_parm.pgm_id is null)
898 and (g_parm.cvg_strt_dt is null or pen.enrt_cvg_strt_dt >= g_parm.cvg_strt_dt )
899 and (g_parm.cvg_end_dt is null or pen.enrt_cvg_thru_dt <= g_parm.cvg_end_dt )
900 and pen.business_group_id = g_parm.business_group_id
901 and pen.pl_typ_id = ptyp.pl_typ_id
902 -- bug 5663102
903 -- and ptyp.opt_typ_cd not in ( 'COMP' , 'CWB' , 'GSP', 'ABS')
904 and ptyp.opt_typ_cd not in ('CWB' , 'GSP', 'ABS')
905 /* Added GSP for grade step*/
906 and ptyp.business_group_id = g_parm.business_group_id
907 and g_parm.effective_date between ptyp.effective_start_date and ptyp.effective_end_date
908 and g_parm.effective_date between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
909 and pen.enrt_cvg_thru_dt <= pen.effective_end_date
910 /* all persons within the specified organization */
911 and (g_parm.organization_id is null
912 or exists ( select '1' from
913 (select assignment_id,assignment_type,organization_id,person_id
914 from per_all_assignments_f paf
915 where business_group_id = g_parm.business_group_id
916 and paf.person_id= nvl(g_parm.person_id,paf.person_id)
917 and g_parm.effective_date
918 between nvl(effective_start_date,g_parm.effective_date )
919 and nvl(effective_end_date, g_parm.effective_date )
920 and primary_flag = 'Y'
921 )paf1
922 where paf1.organization_id = g_parm.organization_id
923 and paf1.assignment_type='E'
924 and paf1.person_id=pen.person_id
925 union
926 select '1' from
927 (select assignment_id,assignment_type,organization_id,person_id
928 from per_all_assignments_f paf
929 where business_group_id = g_parm.business_group_id
930 and paf.person_id= nvl(g_parm.person_id,paf.person_id)
931 and g_parm.effective_date
932 between nvl(effective_start_date,g_parm.effective_date )
933 and nvl(effective_end_date, g_parm.effective_date )
934 and primary_flag = 'Y'
935 )paf1
936 where paf1.organization_id = g_parm.organization_id
937 and paf1.person_id=pen.person_id
938 and (paf1.assignment_type='B' and not exists (select 1 from per_all_assignments_f paf2
939 where paf2.person_id = paf1.person_id
940 and paf2.business_group_id = g_parm.business_group_id
941 and g_parm.effective_date
942 between nvl(paf2.effective_start_date,g_parm.effective_date )
943 and nvl(paf2.effective_end_date, g_parm.effective_date )
944 and paf2.primary_flag = 'Y'
945 and paf2.assignment_type='E')) ))
946 /* person exists with specified person type */
947 and (g_parm.person_type_id is null
948 or exists (select null
949 from per_person_type_usages ptu
950 where ptu.person_id = pen.person_id
954 or exists (select null
951 and ptu.person_type_id = g_parm.person_type_id))
952 /* person exists with specified assignment type */
953 and (g_parm.assgn_type is null
955 from per_assignments_f asg
956 where asg.assignment_type = substr(g_parm.assgn_type,1,1)
957 and asg.person_id = pen.person_id
958 and asg.assignment_type <> 'C'
959 and asg.primary_flag = 'Y'
960 and asg.business_group_id = pen.business_group_id
961 and g_parm.effective_date
962 between asg.effective_start_date and asg.effective_end_date))
963 /* person exists with specified location */
964 and (g_parm.location_id is null
965 or exists (select null
966 from per_assignments_f asg
967 where asg.location_id = g_parm.location_id
968 and asg.person_id = pen.person_id
969 and asg.assignment_type <> 'C'
970 and asg.primary_flag = 'Y'
971 and asg.business_group_id = pen.business_group_id
972 and g_parm.effective_date
973 between asg.effective_start_date and asg.effective_end_date))
974 /* person's address has zip code specified in service area */
975 and (g_parm.svc_area_id is null
976 or exists (select null
977 from per_addresses addr ,
978 ben_svc_area_f svc ,
979 ben_svc_area_pstl_zip_rng_f spz ,
980 ben_pstl_zip_rng_f pstl
981 where addr.person_id = pen.person_id
982 and addr.primary_flag = 'Y'
983 and svc.svc_area_id = g_parm.svc_area_id
984 and svc. svc_area_id = spz.svc_area_id
985 and spz.pstl_zip_rng_id = pstl.pstl_zip_rng_id
986 and addr.postal_code between pstl.from_value and pstl.to_value
987 and svc.business_group_id = pen.business_group_id
988 and g_parm.effective_date
989 between addr.date_from and nvl(addr.date_to,g_parm.effective_date)
990 and g_parm.effective_date
991 between pstl.effective_start_date and pstl.effective_end_date
992 and g_parm.effective_date
993 between spz.effective_start_date and spz.effective_end_date
994 and g_parm.effective_date
995 between svc.effective_start_date and svc.effective_end_date))
996 and (g_parm.ler_id is null
997 or exists ( select null
998 from ben_per_in_ler pil2
999 where pil2.ler_id = g_parm.ler_id
1000 and pil2.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1001 and pil.per_in_ler_id = pil2.per_in_ler_id ))
1002 and (g_parm.lf_evt_ocrd_dt is null
1003 or exists ( select null
1004 from ben_per_in_ler pil3
1005 where pil3.lf_evt_ocrd_dt = g_parm.lf_evt_ocrd_dt
1006 and pil3.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1007 and pil.per_in_ler_id = pil3.per_in_ler_id ))
1008 and pil.per_in_ler_id = pen.per_in_ler_id
1009 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
1010 --
1011 l_package varchar2(80) := g_package||' .create_bensmrep_ranges';
1012 l_num_ranges number;
1013 l_num_persons number;
1014 l_flag varchar2(5);
1015 --
1016 l_start_person_action_id number;
1017 l_ending_person_action_id number;
1018 l_range_id number;
1019 l_object_version_number number;
1020 --
1021 l_per_rec c_person%rowtype ;
1022 begin
1023 hr_utility.set_location('Entering : ' || l_package , 10);
1024 --
1025 hr_utility.set_location('Creating actions and ranges ' || l_package , 15);
1026 --
1027 --fnd_file.put_line(fnd_file.log, 'before person loop : ');
1028 open c_person ;
1029 loop
1030 fetch c_person into l_per_rec ;
1031 exit when c_person%notfound ;
1032 --
1033 --fnd_file.put_line(fnd_file.log, 'inside person loop : ');
1034 create_actions_ranges
1035 (p_person_id => l_per_rec.person_id
1036 ,p_ler_id => g_parm.ler_id
1037 ,p_start_person_action_id => l_start_person_action_id
1038 ,p_ending_person_action_id => l_ending_person_action_id);
1039 --
1040 end loop;
1041 close c_person ;
1042 -- fnd_file.put_line(fnd_file.log, 'after person loop : ');
1043 --
1044 -- There could be a few person actions left over from the call in the for
1045 -- loop above. Create a batch range for them.
1046 --
1047 If g_person_actn_cnt > 0 and
1048 mod(g_person_actn_cnt, g_chunk_size) <> 0 then
1049 --
1050 hr_utility.set_location('Ranges for remaining people ' || l_package, 25);
1051 --
1052 --fnd_file.put_line(fnd_file.log, 'before create batch ranges ');
1053 ben_batch_ranges_api.create_batch_ranges
1054 (p_validate => FALSE
1055 ,p_effective_date => g_parm.effective_date
1056 ,p_benefit_action_id => g_parm.benefit_action_id
1057 ,p_range_id => l_range_id
1058 ,p_range_status_cd => 'U'
1059 ,p_starting_person_action_id => l_start_person_action_id
1060 ,p_ending_person_action_id => l_ending_person_action_id
1061 ,p_object_version_number => l_object_version_number);
1062 --
1063 g_num_ranges := g_num_ranges + 1;
1064 --
1065 end if;
1066 --
1067 hr_utility.set_location('Leaving : ' || l_package, 10);
1068 --fnd_file.put_line(fnd_file.log, 'leaving create_bensmrep_ranges ');
1072 -- << Procedure: create_enrkit_ranges >>
1069 end create_bensmrep_ranges;
1070 --
1071 -- ==================================================================================
1073 -- Description:
1074 -- Benefits Enrollment Kit report sub process
1075 -- ==================================================================================
1076 --
1077 procedure create_enrkit_ranges is
1078 --
1079 -- Cursor to select rows from ben_per_in_ler for people that haven't enrolled
1080 -- in a plan or a program as of the effective date
1081 --
1082 cursor c_person is
1083 select distinct pil.person_id, pil.ler_id
1084 from ben_per_in_ler pil,
1085 ben_pil_elctbl_chc_popl pel,
1086 ben_ler_f ler
1087 where (g_parm.person_id is null or pil.person_id = g_parm.person_id)
1088 and pil.per_in_ler_stat_cd = 'STRTD'
1089 and pil.business_group_id = g_parm.business_group_id
1090 and pil.per_in_ler_id = pel.per_in_ler_id
1091 and pil.ler_id = ler.ler_id
1092 and g_parm.effective_date between ler.effective_start_date and ler.effective_end_date
1093 and ler.business_group_id = pil.business_group_id
1094 and ler.typ_cd not in ( 'GSP', 'ABS') /* added for grade step */
1095 and pel.elcns_made_dt is null
1096 and g_parm.effective_date between
1097 nvl(pel.enrt_perd_strt_dt, g_parm.effective_date) and
1098 nvl(pel.enrt_perd_end_dt, g_parm.effective_date)
1099 and (g_parm.pgm_id is null or pel.pgm_id = g_parm.pgm_id)
1100 and (g_parm.pl_nip_id is null or g_parm.pl_nip_id = pel.pl_id )
1101 and (g_parm.ler_id is null or pil.ler_id = g_parm.ler_id)
1102 and (g_parm.lf_evt_ocrd_dt is null or pil.lf_evt_ocrd_dt = g_parm.lf_evt_ocrd_dt )
1103 /* check if the person belongs to the org or location specified */
1104 and (g_parm.organization_id is null
1105 or exists (select '1'
1106 from per_all_assignments_f per
1107 where per.person_id = pil.person_id
1108 and per.primary_flag = 'Y'
1109 and per.assignment_type <> 'C'
1110 and (g_parm.organization_id is null or
1111 per.organization_id = g_parm.organization_id)
1112 and g_parm.effective_date between per.effective_start_date
1113 and per.effective_end_date ))
1114 and (g_parm.location_id is null
1115 or exists (select '1'
1116 from per_all_assignments_f per
1117 where per.person_id = pil.person_id
1118 and per.primary_flag = 'Y'
1119 and per.assignment_type <> 'C'
1120 and (g_parm.location_id is null or
1121 per.location_id = g_parm.location_id)
1122 and g_parm.effective_date between per.effective_start_date
1123 and per.effective_end_date ))
1124 /* person exists with specified person type */
1125 and (g_parm.person_type_id is null
1126 or exists (select null
1127 from per_person_type_usages ptu
1128 where ptu.person_id = pil.person_id
1129 and ptu.person_type_id = g_parm.person_type_id))
1130 /* person exists with specified assignment type */
1131 and (g_parm.assgn_type is null
1132 or exists (select null
1133 from per_assignments_f asg
1134 where asg.assignment_type = substr(g_parm.assgn_type,1,1)
1135 and asg.person_id = pil.person_id
1136 and asg.assignment_type <> 'C'
1137 and asg.primary_flag = 'Y'
1138 and asg.business_group_id = pil.business_group_id
1139 and g_parm.effective_date
1140 between asg.effective_start_date and asg.effective_end_date))
1141 /* person's address has zip code specified in service area */
1142 and (g_parm.svc_area_id is null
1143 or exists (select null
1144 from per_addresses addr ,
1145 ben_svc_area_f svc ,
1146 ben_svc_area_pstl_zip_rng_f spz ,
1147 ben_pstl_zip_rng_f pstl
1148 where addr.person_id = pil.person_id
1149 and addr.primary_flag = 'Y'
1150 and svc.svc_area_id = g_parm.svc_area_id
1151 and svc. svc_area_id = spz.svc_area_id
1152 and spz.pstl_zip_rng_id = pstl.pstl_zip_rng_id
1153 and addr.postal_code between pstl.from_value and pstl.to_value
1154 and svc.business_group_id = pil.business_group_id
1155 and g_parm.effective_date
1156 between addr.date_from and nvl(addr.date_to,g_parm.effective_date)
1157 and g_parm.effective_date
1158 between pstl.effective_start_date and pstl.effective_end_date
1159 and g_parm.effective_date
1160 between spz.effective_start_date and spz.effective_end_date
1161 and g_parm.effective_date
1162 between svc.effective_start_date and svc.effective_end_date));
1163 --
1164 l_package varchar2(80) := g_package||' .create_bensmrep_ranges';
1165 l_num_ranges number;
1166 l_num_persons number;
1167 l_flag varchar2(5);
1168 --
1169 l_start_person_action_id number;
1170 l_ending_person_action_id number;
1171 l_range_id number;
1172 l_object_version_number number;
1173 --
1174 l_per_rec c_person%rowtype ;
1175 begin
1176 hr_utility.set_location('Entering : ' || l_package , 10);
1177 --
1178 hr_utility.set_location('Creating actions and ranges ' || l_package , 15);
1179 --
1183 fetch c_person into l_per_rec ;
1180 --fnd_file.put_line(fnd_file.log, 'before person loop : ');
1181 open c_person ;
1182 loop
1184 exit when c_person%notfound ;
1185 --
1186 --fnd_file.put_line(fnd_file.log, 'inside person loop : ');
1187 create_actions_ranges
1188 (p_person_id => l_per_rec.person_id
1189 ,p_ler_id => g_parm.ler_id
1190 ,p_start_person_action_id => l_start_person_action_id
1191 ,p_ending_person_action_id => l_ending_person_action_id);
1192 --
1193 end loop;
1194 close c_person ;
1195 -- fnd_file.put_line(fnd_file.log, 'after person loop : ');
1196 --
1197 -- There could be a few person actions left over from the call in the for
1198 -- loop above. Create a batch range for them.
1199 --
1200 If g_person_actn_cnt > 0 and
1201 mod(g_person_actn_cnt, g_chunk_size) <> 0 then
1202 --
1203 hr_utility.set_location('Ranges for remaining people ' || l_package, 25);
1204 --
1205 --fnd_file.put_line(fnd_file.log, 'before create batch ranges ');
1206 ben_batch_ranges_api.create_batch_ranges
1207 (p_validate => FALSE
1208 ,p_effective_date => g_parm.effective_date
1209 ,p_benefit_action_id => g_parm.benefit_action_id
1210 ,p_range_id => l_range_id
1211 ,p_range_status_cd => 'U'
1212 ,p_starting_person_action_id => l_start_person_action_id
1213 ,p_ending_person_action_id => l_ending_person_action_id
1214 ,p_object_version_number => l_object_version_number);
1215 --
1216 g_num_ranges := g_num_ranges + 1;
1217 --
1218 end if;
1219 --
1220 hr_utility.set_location('Leaving : ' || l_package, 10);
1221 --fnd_file.put_line(fnd_file.log, 'leaving create_bensmrep_ranges ');
1222
1223 end ;
1224 -- ==================================================================================
1225 -- << Procedure: process >>
1226 -- Description:
1227 -- this main procedure is called from SRS window.
1228 -- ==================================================================================
1229 --
1230 procedure process
1231 (errbuf out nocopy varchar2
1232 ,retcode out nocopy number
1233 ,p_report_name in varchar2
1234 ,p_effective_date in varchar2
1235 ,p_benefit_action_id in number default null
1236 ,p_pgm_id in number default null
1237 ,p_pl_nip_id in number default null
1238 ,p_plan_in_pgm_flag in varchar2 default 'N'
1239 ,p_organization_id in number default null
1240 ,p_location_id in number default null
1241 ,p_person_id in number default null
1242 ,p_ler_id in number default null
1243 ,p_lf_evt_ocrd_dt in varchar2 default null
1244 ,p_person_selection_rule_id in number default null
1245 ,p_comp_selection_rule_id in number default null
1246 ,p_business_group_id in number
1247 ,p_reporting_group_id in number default null
1248 ,p_svc_area_id in number default null
1249 ,p_assignment_type in varchar2 default null
1250 ,p_cvg_strt_dt in varchar2 default null
1251 ,p_cvg_end_dt in varchar2 default null
1252 ,p_person_type_id in number default null
1253 ,p_ben_sel_flag in varchar2 default 'Y'
1254 ,p_flx_sum_flag in varchar2 default 'Y'
1255 ,p_actn_items_flag in varchar2 default 'Y'
1256 ,p_cov_dpnt_flag in varchar2 default 'Y'
1257 ,p_prmy_care_flag in varchar2 default 'Y'
1258 ,p_beneficaries_flag in varchar2 default 'Y'
1259 ,p_certifications_flag in varchar2 default 'Y'
1260 ,p_disp_epe_flxfld_flag in varchar2 default 'Y'
1261 ,p_disp_flex_fields in varchar2 default 'Y' ) is
1262 --
1263 l_package varchar2(80) := g_package||'.process';
1264 l_num_ranges number;
1265 l_num_persons number;
1266 l_flag varchar2(5);
1267 l_effective_date date;
1268 l_lf_evt_ocrd_dt date;
1269 l_cvg_strt_dt date;
1270 l_cvg_end_dt date;
1271 --
1272 l_request_id number;
1273 l_person_id per_all_people_f.person_id%type;
1274 l_object_version_number ben_benefit_actions.object_version_number%type;
1275 l_benefit_action_id ben_benefit_actions.benefit_action_id%type;
1276 l_person_action_id ben_person_actions.person_action_id%type;
1277 --
1278 l_errbuf varchar2(80);
1279 l_retcode number;
1280 --
1281 l_commit number;
1282 l_person_cnt number;
1283 l_cnt number;
1284 l_actn varchar2(2000);
1285 l_count number;
1286 --
1287 begin
1288 --
1289 hr_utility.set_location ('Entering '|| l_package,10);
1290 l_effective_date:= to_date(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
1291 l_effective_date:= to_date(to_char(trunc(l_effective_date),'DD/MM/RRRR'),'DD/MM/RRRR');
1292 --
1293 l_lf_evt_ocrd_dt := to_date(p_lf_evt_ocrd_dt,'YYYY/MM/DD HH24:MI:SS');
1294 l_lf_evt_ocrd_dt := to_date(to_char(trunc(l_lf_evt_ocrd_dt),'DD/MM/RRRR'),'DD/MM/RRRR');
1295 --
1296 l_cvg_strt_dt := to_date(p_cvg_strt_dt,'YYYY/MM/DD HH24:MI:SS');
1297 l_cvg_strt_dt := to_date(to_char(trunc(l_cvg_strt_dt),'DD/MM/RRRR'),'DD/MM/RRRR');
1298 --
1299
1300 l_cvg_end_dt := to_date(p_cvg_end_dt,'YYYY/MM/DD HH24:MI:SS');
1301 l_cvg_end_dt := to_date(to_char(trunc(l_cvg_end_dt),'DD/MM/RRRR'),'DD/MM/RRRR');
1302
1303 -- Put row in fnd_sessions
1304 --
1305 dt_fndate.change_ses_date
1306 (p_ses_date => l_effective_date,
1307 p_commit => l_commit);
1308
1312
1309 hr_utility.set_location('Checking arguments',12);
1310 --
1311 l_actn := 'Initialise globals...';
1313 initialize_globals;
1314 --
1315 -- Log start time of process
1316 --
1317 g_proc_rec.business_group_id := p_business_group_id;
1318 g_proc_rec.strt_dt := sysdate;
1319 g_proc_rec.strt_tm := to_char(sysdate,'HH24:MI:SS');
1320 g_strt_tm_numeric := dbms_utility.get_time;
1321 --
1322 -- Flush the global-parameter-list and load all the passed parameters into it.
1323 -- All the sub procedures in the main process will be able to access this list
1324 -- and this will keep the procedure calls simple.
1325 --
1326 g_parm.report_name := p_report_name ;
1327 g_parm.benefit_action_id := p_benefit_action_id;
1328 g_parm.effective_date := l_effective_date;
1329 g_parm.business_group_id := p_business_group_id;
1330 g_parm.person_id := p_person_id;
1331 g_parm.person_type_id := p_person_type_id;
1332 g_parm.person_sel_rl := p_person_selection_rule_id;
1333 g_parm.organization_id := p_organization_id;
1334 g_parm.location_id := p_location_id;
1335 g_parm.ler_id := p_ler_id;
1336 g_parm.pgm_id := p_pgm_id;
1337 g_parm.pl_nip_id := p_pl_nip_id;
1338 g_parm.plan_in_pgm_flag := p_plan_in_pgm_flag;
1339 g_parm.comp_selection_rl := p_comp_selection_rule_id;
1340 g_parm.lf_evt_ocrd_dt := l_lf_evt_ocrd_dt;
1341 --
1342 g_parm.rptg_grp := p_reporting_group_id;
1343 g_parm.svc_area_id := p_svc_area_id ;
1344 g_parm.assgn_type := p_assignment_type ;
1345 g_parm.cvg_strt_dt := to_char(l_cvg_strt_dt,'DD-MON-YYYY'); -- 2665181
1346 g_parm.cvg_end_dt := to_char(l_cvg_end_dt,'DD-MON-YYYY' );
1347 g_parm.ben_sel_flag := p_ben_sel_flag;
1348 g_parm.flx_sum_flag := p_flx_sum_flag;
1349 g_parm.actn_items_flag := p_actn_items_flag;
1350 g_parm.cov_dpnt_flag := p_cov_dpnt_flag;
1351 g_parm.prmy_care_flag := p_prmy_care_flag;
1352 g_parm.beneficaries_flag := p_beneficaries_flag;
1353 g_parm.certifications_flag := p_certifications_flag ;
1354 g_parm.disp_epe_flxfld_flag := p_disp_epe_flxfld_flag ;
1355 g_parm.disp_flex_fields := p_disp_flex_fields ;
1356 --
1357 -- fnd_file.put_line(fnd_file.log, 'g_parm.effective_date : ' || g_parm.effective_date);
1358 -- fnd_file.put_line(fnd_file.log, 'g_parm.lf_evt_ocrd_dt : ' || g_parm.lf_evt_ocrd_dt);
1359 -- fnd_file.put_line(fnd_file.log, 'g_parm.cvg_strt_dt : ' || g_parm.cvg_strt_dt );
1360 -- fnd_file.put_line(fnd_file.log, 'g_parm.cvg_end_dt : ' || g_parm.cvg_end_dt );
1361 -- fnd_file.put_line(fnd_file.log, 'l_cvg_end_dt : ' || to_char(l_cvg_end_dt,'dd-mon-yyyy'));
1362 l_actn := 'check business rules ...';
1363 -- Check the parameters for validity and incompatibilities.
1364 check_business_rules;
1365 -- Get the parameters for the batch process so we know how many slaves to
1366 -- start and what size the chunk size is. Store them in globals.
1367 --
1368 if p_report_name = 'BENSMREP' then
1369 benutils.get_parameter
1370 (p_business_group_id => p_business_group_id
1371 ,p_batch_exe_cd => 'BENSMREP'
1372 ,p_threads => g_threads
1373 ,p_chunk_size => g_chunk_size
1374 ,p_max_errors => g_max_errors);
1375 elsif p_report_name = 'BEENRKIT' then
1376 benutils.get_parameter
1377 (p_business_group_id => p_business_group_id
1378 ,p_batch_exe_cd => 'BEENRKIT'
1379 ,p_threads => g_threads
1380 ,p_chunk_size => g_chunk_size
1381 ,p_max_errors => g_max_errors);
1382 end if;
1383 --
1384 g_threads := nvl( g_threads , 3 ) ;
1385 g_chunk_size := nvl( g_chunk_size , 10 );
1386 g_max_errors := nvl( g_max_errors , 20 );
1387 --
1388 -- fnd_file.put_line(fnd_file.log, 'g_threads : ' || g_threads );
1389 -- fnd_file.put_line(fnd_file.log, 'g_chunk_size : ' || g_chunk_size);
1390 -- fnd_file.put_line(fnd_file.log, 'g_max_errors : ' || g_max_errors);
1391 --
1392 hr_utility.set_location('Num Threads = ' || g_threads, 10);
1393 hr_utility.set_location('Chunk Size = ' || g_chunk_size, 10);
1394 hr_utility.set_location('Max Errors = ' || g_max_errors, 10);
1395 --
1396 -- Create benefit actions parameters in the benefit action table.
1397 -- Do not create if a benefit action already exists, in other words
1398 -- we are doing a restart.
1399 --
1400 if p_benefit_action_id is null then
1401 --
1402 hr_utility.set_location('p_benefit_action_id is null',14);
1403 --
1404 -- This call inserts the parameters given and the request id of
1405 -- the concurrent program into the Benefit Actions table
1406 --
1407 ben_benefit_actions_api.create_perf_benefit_actions
1408 ( p_benefit_action_id => l_benefit_action_id
1409 ,p_process_date => l_effective_date
1410 ,p_person_id => p_person_id
1411 ,p_pgm_id => p_pgm_id
1412 ,p_business_group_id => p_business_group_id
1413 ,p_pl_id => p_pl_nip_id
1414 ,p_comp_selection_rl => p_comp_selection_rule_id
1415 ,p_person_selection_rl => p_person_selection_rule_id
1416 ,p_ler_id => p_ler_id
1417 ,p_organization_id => p_organization_id
1418 ,p_location_id => p_location_id
1419 ,p_request_id => fnd_global.conc_request_id
1420 ,p_program_application_id => fnd_global.prog_appl_id
1421 ,p_program_id => fnd_global.conc_program_id
1422 ,p_program_update_date => sysdate
1423 ,p_object_version_number => l_object_version_number
1424 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1425 ,p_effective_date => l_effective_date
1426 ,p_mode_cd => 'U'
1427 ,p_derivable_factors_flag => 'N'
1428 ,p_validate_flag => 'N'
1429 ,p_debug_messages_flag => 'Y'
1430 ,p_audit_log_flag => 'N'
1434 ,p_pstl_zip_rng_id => null
1431 ,p_no_programs_flag => p_plan_in_pgm_flag
1432 ,p_no_plans_flag => 'N'
1433 ,p_benfts_grp_id => null
1435 ,p_rptg_grp_id => NULL
1436 ,p_opt_id => NULL
1437 ,p_eligy_prfl_id => NULL
1438 ,p_vrbl_rt_prfl_id => NULL
1439 ,p_legal_entity_id => null
1440 ,p_payroll_id => null
1441 );
1442
1443 g_parm.benefit_action_id := l_benefit_action_id;
1444 --
1445 commit;
1446 --
1447 -- Delete/clear ranges from ben_batch_ranges table
1448 --
1449 hr_utility.set_location('Delete rows from ben_batch_ranges',16);
1450 --
1451 Delete from ben_batch_ranges
1452 Where benefit_action_id = l_benefit_action_id;
1453 --
1454 l_actn := 'After benefit action is created ...';
1455 -- Create person-actions and batch-ranges for the process.
1456 if p_report_name = 'BENSMREP' then
1457 --
1458 l_actn := 'Before create_bensmrep_ranges ...';
1459 --
1460 create_bensmrep_ranges;
1461 --
1462 l_actn := 'After create_bensmrep_ranges ...';
1463 --
1464 elsif p_report_name = 'BEENRKIT' then
1465 --
1466 l_actn := 'Before create_beenrkit_ranges ...';
1467 --
1468 create_enrkit_ranges;
1469 --
1470 l_actn := 'After create_beenrkit_ranges ...';
1471 --
1472 end if;
1473 --
1474 else -- p_benefit_action_id not null
1475 --
1476 -- Benefit action id is not null i.e. the batch process is being restarted
1477 -- for a certain benefit action id. Create batch ranges and person actions
1478 -- for restarting.
1479 --
1480 hr_utility.set_location('restart batch process ' || l_actn, 20);
1481 --
1482 ben_batch_utils.create_restart_person_actions
1483 (p_benefit_action_id => p_benefit_action_id
1484 ,p_effective_date => l_effective_date
1485 ,p_chunk_size => g_chunk_size
1486 ,p_threads => g_threads
1487 ,p_num_ranges => g_num_ranges
1488 ,p_num_persons => g_person_cnt
1489 ,p_commit_data => 'Y');
1490 --
1491 g_parm.benefit_action_id := p_benefit_action_id;
1492 --
1493 end if;
1494 --
1495 commit ;
1496 --
1497 --
1498 g_person_cnt := g_person_actn_cnt ;
1499 g_error_person_cnt := 0 ;
1500 --
1501 fnd_file.put_line(fnd_file.log, 'Number of persons selected : ' || g_person_cnt);
1502 --
1503 print_parameters ;
1504 --
1505 write_logfile ;
1506 -- If there were no people selected with the criteria provided, the number of
1507 -- ranges created would have been zero. Raise an eexception if so.
1508 --
1509 if g_num_ranges = 0 then
1510 l_actn := 'No persons were found eligible...';
1511 --
1512 Ben_batch_utils.write(p_text =>
1513 '<< No Person got selected with above selection criteria >>' );
1514 fnd_message.set_name('BEN','BEN_91769_NOONE_TO_PROCESS');
1515 fnd_message.set_token('PROC',l_package);
1516 end if;
1517 --
1518 -- Set the number of threads to the lesser of the defined number of threads
1519 -- and the number of ranges created above.
1520 --
1521 g_threads := least(g_threads, g_num_ranges);
1522 --
1523 hr_utility.set_location('Number of Threads : ' || g_threads, 20);
1524 --
1525 --fnd_file.put_line(fnd_file.log, 'Number of Threads : ' || g_threads);
1526 -- Submit requests
1527 --
1528 -- for l_count in 1..g_threads loop
1529 if g_num_ranges <> 0 then
1530 --
1531 --
1532 hr_utility.set_location('Sumitting thread : ' || l_count, 25);
1533 --
1534 l_actn := 'Submit request for report ...';
1535 --
1536 if p_report_name = 'BENSMREP' then
1537 --
1538 -- fnd_file.put_line(fnd_file.log, 'before submitting request for BENSMREP ..');
1539 l_request_id := FND_REQUEST.SUBMIT_REQUEST
1540 (application => 'BEN',
1541 program => 'BENSMREP',
1542 sub_request => FALSE,
1543 argument1 => to_char(fnd_date.canonical_to_date(p_effective_date),'YYYY/MM/DD HH24:MI:SS') , /* Bug 3905852*/
1544 argument2 => g_parm.benefit_action_id,
1545 argument3 => g_parm.pgm_id ,
1546 argument4 => g_parm.pl_nip_id,
1547 argument5 => g_parm.organization_id,
1548 argument6 => g_parm.location_id ,
1549 argument7 => g_parm.person_id,
1550 argument8 => g_parm.ler_id,
1551 argument9 => to_char(fnd_date.canonical_to_date(p_lf_evt_ocrd_dt),'YYYY/MM/DD HH24:MI:SS'), /* Bug 3905852*/
1552 argument10 => g_parm.person_sel_rl,
1553 argument11 => g_parm.comp_selection_rl,
1554 argument12 => g_parm.business_group_id ,
1555 argument13 => g_parm.plan_in_pgm_flag ,
1556 argument14 => g_parm.person_type_id ,
1557 argument15 => g_parm.rptg_grp,
1558 argument16 => g_parm.svc_area_id,
1559 argument17 => g_parm.assgn_type ,
1560 argument18 => to_char(fnd_date.canonical_to_date(p_cvg_strt_dt),'YYYY/MM/DD HH24:MI:SS'), /* Bug 3905852*/
1561 argument19 => to_char(fnd_date.canonical_to_date(p_cvg_end_dt),'YYYY/MM/DD HH24:MI:SS'), /* Bug 3905852*/
1562 argument20 => g_parm.ben_sel_flag,
1563 argument21 => g_parm.flx_sum_flag,
1564 argument22 => g_parm.cov_dpnt_flag,
1565 argument23 => g_parm.prmy_care_flag,
1566 argument24 => g_parm.beneficaries_flag,
1567 argument25 => g_parm.certifications_flag,
1568 argument26 => g_parm.actn_items_flag
1569 );
1570
1571 elsif p_report_name = 'BEENRKIT' then
1572 --
1576 program => 'BEENRKIT',
1573 -- fnd_file.put_line(fnd_file.log, 'before submitting request for BEENRKIT..');
1574 l_request_id := FND_REQUEST.SUBMIT_REQUEST
1575 (application => 'BEN',
1577 sub_request => FALSE,
1578 argument1 => to_char(fnd_date.canonical_to_date(p_effective_date),'YYYY/MM/DD HH24:MI:SS') , /* Bug 3905852*/
1579 argument2 => g_parm.benefit_action_id,
1580 argument3 => g_parm.pgm_id ,
1581 argument4 => g_parm.pl_nip_id,
1582 argument5 => g_parm.organization_id,
1583 argument6 => g_parm.location_id ,
1584 argument7 => g_parm.person_id,
1585 argument8 => g_parm.ler_id,
1586 argument9 => to_char(fnd_date.canonical_to_date(p_lf_evt_ocrd_dt),'YYYY/MM/DD HH24:MI:SS'), /* Bug 3905852*/
1587 argument10 => g_parm.person_sel_rl,
1588 argument11 => g_parm.comp_selection_rl,
1589 argument12 => g_parm.business_group_id ,
1590 argument13 => g_parm.plan_in_pgm_flag ,
1591 argument14 => g_parm.person_type_id ,
1592 argument15 => g_parm.rptg_grp,
1593 argument16 => g_parm.svc_area_id,
1594 argument17 => g_parm.assgn_type ,
1595 argument18 => g_parm.ben_sel_flag,
1596 argument19 => g_parm.flx_sum_flag,
1597 argument20 => g_parm.cov_dpnt_flag,
1598 argument21 => g_parm.prmy_care_flag,
1599 argument22 => g_parm.beneficaries_flag,
1600 argument23 => g_parm.certifications_flag,
1601 argument24 => g_parm.actn_items_flag,
1602 argument25 => g_parm.disp_epe_flxfld_flag ,
1603 argument26 => g_parm.disp_flex_fields);
1604 --
1605 End if ;
1606 --
1607 commit;
1608 --
1609 g_num_processes := ben_batch_utils.g_num_processes + 1;
1610 g_processes_rec(g_num_processes) := l_request_id;
1611 --
1612 end if ;
1613 -- end loop;
1614 --
1615 l_actn := 'After submitting request for report ...';
1616 --
1617 -- fnd_file.put_line(fnd_file.log, 'leaving process ..');
1618 Exception
1619 when others then
1620 ben_batch_utils.rpt_error(p_proc => l_package
1621 ,p_last_actn => l_actn
1622 ,p_rpt_flag => TRUE );
1623 --
1624 benutils.write(p_text => fnd_message.get);
1625 benutils.write(p_text => sqlerrm);
1626 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
1627 hr_utility.set_location ('HR_6153_ALL_PROCEDURE_FAIL',689);
1628 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1629 fnd_message.set_token('PROCEDURE', l_package);
1630 fnd_message.set_token('STEP', l_actn );
1631 fnd_file.put_line(fnd_file.log, fnd_message.get );
1632 fnd_message.raise_error;
1633 end;
1634 --
1635 end ben_conc_reports;