[Home] [Help]
PACKAGE BODY: APPS.BEN_BATCH_REPORTING
Source
1 package body ben_batch_reporting as
2 /* $Header: benrepor.pkb 120.1 2007/05/04 11:09:59 nhunur noship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10
11 Name
12 Batch Reporting
13 Purpose
14 This package is used to perform reporting for batch processes.
15 History
16 Date Who Version What?
17 ---- --- ------- -----
18 07 Oct 98 G Perry 115.0 Created.
19 16 Oct 98 I Harding 115.1 Uncommented exit
20 20 Dec 98 G Perry 115.2 Changed reporting to drive off
21 new reporting tables.
22 02 Mar 99 G Perry 115.3 Added error message check for
23 when concurrent requests are
24 being spawned.
25 14 Nov 99 G Perry 115.4 Added parameter to
26 run certain activity report
27 based on mode. Also added
28 temporal events procedure.
29 11 Apr 00 G Perry 115.5 Added application id join
30 to sort FIDO dup row issues.
31 12 May 00 jcarpent 115.2 Changed parameters to
32 summary (127645/4424)
33 24 Jul 00 C Daniels 115.8 OraBug 5413. Changed the lookup
34 type associated with the
35 derivable factors flag from
36 'YES_NO' to 'BEN_DTCT_TMPRL_
37 LER_TYP' in cursor c_benefit_
38 actions of the standard_header
39 procedure.
40 15 Sep 00 C Daniels 115.9 Bug 1405067. Modified cursor
41 c_rate_prem_cvg_change in
42 both versions of overloaded
43 procedure activity_summary_
44 by_action to be based on
45 table ben_batch_ler_info only.
46 11 Jan 02 Pbodla 115.10 CWB Change : Extend the C mode
47 to W (Comp Workbench mode)
48 in procedure batch_reports
49 11 Jan 02 Pbodla 115.11 Added Commit at the end
50 28 Jan 02 mhoyes 115.12 - Excluded batch reporting
51 for collective agreement A mode.
52 18 Mar 02 hnarayan 115.14 bug 1560336 - changed standard_header procedure
53 18 Jun 02 ikasire 115.15 bug 2394141 NLS Changes
54 18 Jun 02 ikasire 115.16 bug 2394141 Replaced nvl with decode and
55 source_lang with language
56 17 Jul 02 mmudigon 115.17 ABSENCES : Added absences mode
57 05 Sep 02 vsethi 115.18 Bug 2547948, truncating the value returned by
58 fnd_message.get to 80. The returned value cannot
59 be greater than 80 unless it's changed for translation
60 30 Oct 02 bmanyam 115.19 Bug 2243050: Added check in queries to restrict
61 persons errored in procedures
62 activity_summary_by_action and
63 temporal_life_events
64 30 Oct 02 bmanyam 115.20 -- do --
65 27 Dec 02 rpillay 115.22 NOCOPY changes
66 14 Feb 03 tmathers 115.23 MLS Changes
67 15-May-03 rpgupta 115.24 bug 2950460
68 change the lookup type for some
69 reports as cursor does'nt fetch any rows
70 29-May-03 ikasire 115.25 Bug 2945455 added 'P' mode for
71 submit
72 30-Jun-03 vsethi 115.26 Changed reference for table ben_rptg_grp
73 MLS compliant view ben_rptg_grp_v
74
75 18-Aug-03 rpgupta 115.27 Included mode 'G' for 'BENACTIV'
76 02-jun-04 nhunur 115.28 3662774 - Added cursor c0 in temporal_life_events
77 for performance.
78 15 Jun 04 hmani 115.30 Added six more params
79 to temporal_life_events - Bug 3690166
80 19-Nov-04 abparekh 115.31 Bug 3517604 Modified cursor c_benefit_actions to take
81 outer join for lookup_type BEN_DTCT_TMPRL_LER_TYP
82 15-apr-05 nhunur 115.32 Performance changes to use benefit_action_id instead of request_id
83 */
84 -----------------------------------------------------------------------
85 g_package varchar2(30) := 'ben_batch_reporting.';
86 -----------------------------------------------------------------------
87 procedure standard_header
88 (p_concurrent_request_id in number,
89 p_concurrent_program_name out nocopy varchar2,
90 p_process_date out nocopy date,
91 p_mode out nocopy varchar2,
92 p_derivable_factors out nocopy varchar2,
93 p_validate out nocopy varchar2,
94 p_person out nocopy varchar2,
95 p_person_type out nocopy varchar2,
96 p_program out nocopy varchar2,
97 p_business_group out nocopy varchar2,
98 p_plan out nocopy varchar2,
99 p_popl_enrt_typ_cycl out nocopy varchar2,
100 p_plans_not_in_programs out nocopy varchar2,
101 p_just_programs out nocopy varchar2,
102 p_comp_object_selection_rule out nocopy varchar2,
103 p_person_selection_rule out nocopy varchar2,
104 p_life_event_reason out nocopy varchar2,
105 p_organization out nocopy varchar2,
106 p_postal_zip_range out nocopy varchar2,
107 p_reporting_group out nocopy varchar2,
108 p_plan_type out nocopy varchar2,
109 p_option out nocopy varchar2,
110 p_eligibility_profile out nocopy varchar2,
111 p_variable_rate_profile out nocopy varchar2,
112 p_legal_entity out nocopy varchar2,
113 p_payroll out nocopy varchar2,
114 p_status out nocopy varchar2) is
115 --
116 l_proc varchar2(80) := g_package||'.standard_header';
117 l_all varchar2(80);
118 l_none varchar2(80);
119 -- bug 1560336
120 l_mode_cd_lookup_type hr_lookups.lookup_type%type := 'BEN_BENMNGLE_MD';
121 l_drvbl_fctrs_lookup_type hr_lookups.lookup_type%type := 'BEN_DTCT_TMPRL_LER_TYP';
122 l_conc_pgm_name fnd_concurrent_programs.concurrent_program_name%type ;
123 --
124 cursor c_benefit_actions is
125 select bft.process_date,
126 hr.meaning,
127 hr1.meaning,
128 hr2.meaning,
129 /* Default null return columns using local variables
130 declared above */
131 nvl(ppf.full_name,l_all),
132 nvl(ppt.user_person_type,l_all),
133 nvl(pgm.name,l_all),
134 pbg.name,
135 nvl(pln.name,l_all),
136 decode(hr5.meaning,
137 null,
138 l_all,
139 hr5.meaning||
140 ' '||
141 pln2.name||
142 ' '||
143 pgm2.name||
144 ' '||
145 epo.strt_dt||
146 ' '||
147 epo.end_dt),
148 hr3.meaning,
149 hr4.meaning,
150 nvl(ff.formula_name,l_none),
151 nvl(ff2.formula_name,l_none),
152 nvl(ler.name,l_all),
153 nvl(org.name,l_all),
154 decode(rzr.from_value||'-'||rzr.to_value,
155 '-',
156 l_all,
157 rzr.from_value||'-'||rzr.to_value),
158 nvl(bnr.name,l_all),
159 nvl(ptp.name,l_all),
160 nvl(opt.name,l_all),
161 nvl(elp.name,l_all),
162 nvl(vpf.name,l_all),
163 nvl(org2.name,l_all),
164 nvl(pay.payroll_name,l_all),
165 conc.user_concurrent_program_name,
166 fnd1.meaning
167 from ben_benefit_actions bft,
168 hr_lookups hr,
169 hr_lookups hr1,
170 hr_lookups hr2,
171 hr_lookups hr3,
172 hr_lookups hr4,
173 hr_lookups hr5,
174 fnd_lookups fnd1,
175 per_people_f ppf,
176 per_person_types ppt,
177 ben_pgm_f pgm,
178 per_business_groups pbg,
179 ben_pl_f pln,
180 ff_formulas_f ff,
181 ff_formulas_f ff2,
182 ben_ler_f ler,
183 hr_all_organization_units_vl org,
184 ben_rptg_grp_v bnr,
185 ben_pl_typ_f ptp,
186 ben_opt_f opt,
187 ben_eligy_prfl_f elp,
188 ben_vrbl_rt_prfl_f vpf,
189 pay_payrolls_f pay,
190 ben_pstl_zip_rng_f rzr,
191 hr_all_organization_units_tl org2,
192 ben_popl_enrt_typ_cycl_f pop,
193 ben_enrt_perd epo,
194 ben_pl_f pln2,
195 ben_pgm_f pgm2,
196 fnd_concurrent_requests fnd,
197 fnd_concurrent_programs_tl conc
198 where fnd.request_id = p_concurrent_request_id
199 and conc.concurrent_program_id = fnd.concurrent_program_id
200 and conc.application_id = 805
201 and userenv('LANG') = conc.language --Bug 2394141
202 and bft.request_id = fnd.request_id
203 and hr.lookup_code = bft.mode_cd
204 -- bug fix 1560336
205 -- and hr.lookup_type = 'BEN_BENMNGLE_MD'
206 and hr.lookup_type = l_mode_cd_lookup_type
207 and hr1.lookup_code (+)= bft.derivable_factors_flag -- Bug 3517604 Added outer join
208 -- bug fix 1560336
209 -- and hr1.lookup_type = 'BEN_DTCT_TMPRL_LER_TYP'
210 and hr1.lookup_type (+)= l_drvbl_fctrs_lookup_type -- Bug 3517604 Added outer join
211 and hr2.lookup_code = bft.validate_flag
212 and hr2.lookup_type = 'YES_NO'
213 and hr3.lookup_code = bft.no_programs_flag
214 and hr3.lookup_type = 'YES_NO'
215 and hr4.lookup_code = bft.no_plans_flag
216 and hr4.lookup_type = 'YES_NO'
217 and hr5.lookup_code(+) = pop.enrt_typ_cycl_cd
218 and hr5.lookup_type(+) = 'BEN_ENRT_TYP_CYCL'
219 and fnd.status_code = fnd1.lookup_code
220 and fnd1.lookup_type = 'CP_STATUS_CODE'
221 and pop.popl_enrt_typ_cycl_id(+) = epo.popl_enrt_typ_cycl_id
222 and bft.process_date
223 between nvl(pop.effective_start_date,bft.process_date)
224 and nvl(pop.effective_end_date,bft.process_date)
225 and epo.enrt_perd_id(+) = bft.popl_enrt_typ_cycl_id
226 and pln2.pl_id(+) = pop.pl_id
227 and bft.process_date
228 between nvl(pln2.effective_start_date,bft.process_date)
229 and nvl(pln2.effective_end_date,bft.process_date)
230 and pgm2.pgm_id(+) = pop.pgm_id
231 and bft.process_date
232 between nvl(pgm2.effective_start_date,bft.process_date)
233 and nvl(pgm2.effective_end_date,bft.process_date)
234 and ppf.person_id(+) = bft.person_id
235 and bft.process_date
236 between nvl(ppf.effective_start_date,bft.process_date)
237 and nvl(ppf.effective_end_date,bft.process_date)
238 and pay.payroll_id(+) = bft.payroll_id
239 and bft.process_date
240 between nvl(pay.effective_start_date,bft.process_date)
241 and nvl(pay.effective_end_date,bft.process_date)
242 and ppt.person_type_id(+) = bft.person_type_id
243 and pgm.pgm_id(+) = bft.pgm_id
244 and bft.process_date
245 between nvl(pgm.effective_start_date,bft.process_date)
246 and nvl(pgm.effective_end_date,bft.process_date)
247 and pbg.business_group_id = bft.business_group_id
248 and org2.organization_id(+) = bft.legal_entity_id
249 and decode(org2.language,null,'1',org2.language)
250 = decode(org2.language,null,'1',userenv('LANG'))
251 and pln.pl_id(+) = bft.pl_id
252 and bft.process_date
253 between nvl(pln.effective_start_date,bft.process_date)
254 and nvl(pln.effective_end_date,bft.process_date)
255 and ler.ler_id(+) = bft.ler_id
256 and bft.process_date
257 between nvl(ler.effective_start_date,bft.process_date)
258 and nvl(ler.effective_end_date,bft.process_date)
259 and rzr.pstl_zip_rng_id(+) = bft.pstl_zip_rng_id
260 and bft.process_date
261 between nvl(rzr.effective_start_date,bft.process_date)
262 and nvl(rzr.effective_end_date,bft.process_date)
263 and ptp.pl_typ_id(+) = bft.pl_typ_id
264 and bft.process_date
265 between nvl(ptp.effective_start_date,bft.process_date)
266 and nvl(ptp.effective_end_date,bft.process_date)
267 and opt.opt_id(+) = bft.opt_id
268 and bft.process_date
269 between nvl(opt.effective_start_date,bft.process_date)
270 and nvl(opt.effective_end_date,bft.process_date)
271 and ff.formula_id(+) = bft.comp_selection_rl
272 and bft.process_date
273 between nvl(ff.effective_start_date,bft.process_date)
274 and nvl(ff.effective_end_date,bft.process_date)
275 and ff2.formula_id(+) = bft.person_selection_rl
276 and bft.process_date
277 between nvl(ff2.effective_start_date,bft.process_date)
278 and nvl(ff2.effective_end_date,bft.process_date)
279 and bnr.rptg_grp_id(+) = bft.rptg_grp_id
280 and elp.eligy_prfl_id(+) = bft.eligy_prfl_id
281 and bft.process_date
282 between nvl(elp.effective_start_date,bft.process_date)
283 and nvl(elp.effective_end_date,bft.process_date)
284 and vpf.vrbl_rt_prfl_id(+) = bft.vrbl_rt_prfl_id
285 and bft.process_date
286 between nvl(vpf.effective_start_date,bft.process_date)
287 and nvl(vpf.effective_end_date,bft.process_date)
288 and org.organization_id(+) = bft.organization_id
289 and bft.process_date
290 between nvl(org.date_from,bft.process_date)
291 and nvl(org.date_to,bft.process_date);
292 --
293 -- bug fix 1560336
294 cursor c_conc_pgm_name is
295 select conc.concurrent_program_name
296 from fnd_concurrent_requests fnd,
297 fnd_concurrent_programs conc
298 where fnd.request_id = p_concurrent_request_id
299 and conc.concurrent_program_id = fnd.concurrent_program_id
300 and conc.application_id = 805;
301 -- end fix 1560336
302
303 begin
304 --
305 hr_utility.set_location('Entering :'||l_proc,10);
306 --
307 -- Default return values for nulls
308 --
309 fnd_message.set_name('BEN','BEN_91792_ALL_PROMPT');
310 l_all := substrb(fnd_message.get,1,80); -- Bug 2547948
311 fnd_message.set_name('BEN','BEN_91793_NONE_PROMPT');
312 l_none := substrb(fnd_message.get,1,80); -- Bug 2547948
313 --
314 -- bug fix 1560336
315 open c_conc_pgm_name;
316 --
317 fetch c_conc_pgm_name into l_conc_pgm_name;
318 if c_conc_pgm_name%FOUND then
319 --
320 if (l_conc_pgm_name = 'BENCLENR') then
321 l_mode_cd_lookup_type := 'BEN_BENCLENR_MD' ;
322 l_drvbl_fctrs_lookup_type := 'YES_NO' ;
323 elsif (l_conc_pgm_name = 'BENDSGEL') then
324 l_mode_cd_lookup_type := 'BEN_BENMNGLE_MD' ;
325 l_drvbl_fctrs_lookup_type := 'YES_NO' ;
326 /* bug 2950460 */
327 elsif (l_conc_pgm_name in ('BENBOCON', 'BENFRCON', 'BENPRCON', 'BENEADEB')) then
328 l_drvbl_fctrs_lookup_type := 'YES_NO';
329
330 /* end 2950460 */
331 end if ;
332 end if ;
333 --
334 close c_conc_pgm_name;
335 -- end fix 1560336
336 --
337 -- Get parameter information from batch process run
338 --
339 open c_benefit_actions;
340 --
341 fetch c_benefit_actions into p_process_date,
342 p_mode,
343 p_derivable_factors,
344 p_validate,
345 p_person,
346 p_person_type,
347 p_program,
348 p_business_group,
349 p_plan,
350 p_popl_enrt_typ_cycl,
351 p_plans_not_in_programs,
352 p_just_programs,
353 p_comp_object_selection_rule,
354 p_person_selection_rule,
355 p_life_event_reason,
356 p_organization,
357 p_postal_zip_range,
358 p_reporting_group,
359 p_plan_type,
360 p_option,
361 p_eligibility_profile,
362 p_variable_rate_profile,
363 p_legal_entity,
364 p_payroll,
365 p_concurrent_program_name,
366 p_status;
367 --
368 close c_benefit_actions;
369 --
370 hr_utility.set_location('Leaving :'||l_proc,10);
371 --
372 end standard_header;
373 -----------------------------------------------------------------------
374 procedure temporal_life_events
375 (p_concurrent_request_id in number,
376 p_age_changed out nocopy varchar2,
377 p_los_changed out nocopy varchar2,
378 p_comb_age_los_changed out nocopy varchar2,
379 p_pft_changed out nocopy varchar2,
380 p_comp_lvl_changed out nocopy varchar2,
381 p_hrs_wkd_changed out nocopy varchar2,
382 p_loss_of_eligibility out nocopy varchar2,
383 p_late_payment out nocopy varchar2,
384 p_max_enrollment_rchd out nocopy varchar2,
385 p_period_enroll_changed out nocopy varchar2,
386 p_voulntary_end_cvg out nocopy varchar2,
387 p_waiting_satisfied out nocopy varchar2,
388 p_persons_no_potential out nocopy varchar2,
389 p_persons_with_potential out nocopy varchar2,
390 p_number_of_events_created out nocopy varchar2) is
391 --
392 l_proc varchar2(80) := g_package||'.temporal_life_events';
393
394 --
395 cursor c0 is
396 select benefit_action_id
397 from ben_benefit_actions bft
398 where bft.request_id = p_concurrent_request_id ;
399 --
400 l_c0 c0%rowtype;
401 --
402 /* cursor c1 is
403 select count(*) amount,ler.typ_cd
404 from ben_batch_ler_info bli,
405 ben_benefit_actions bft,
406 ben_ler_f ler,
407 ben_person_actions bpa
408 where bft.benefit_action_id = bli.benefit_action_id
409 and bft.benefit_action_id = l_c0.benefit_action_id
410 and bpa.benefit_action_id = bft.benefit_action_id
411 and ler.ler_id = bli.ler_id
412 and bft.process_date between ler.effective_start_date and ler.effective_end_date
413 and bli.tmprl_flag = 'Y'
414 and bpa.benefit_action_id = bli.benefit_action_id
415 and bpa.person_id = bli.person_id
416 and bpa.action_status_cd <> 'E'
417 group by ler.typ_cd;
418 */
419 cursor c1 is
420 SELECT /*+ BEN_BATCH_REPORTING.temporal_life_events.c1 */
421 COUNT(*) AMOUNT,LER.TYP_CD
422 FROM BEN_BATCH_LER_INFO BLI,
423 BEN_BENEFIT_ACTIONS BFT,
424 BEN_LER_F LER
425 WHERE BFT.BENEFIT_ACTION_ID = BLI.BENEFIT_ACTION_ID
426 AND BLI.BENEFIT_ACTION_ID = l_c0.benefit_action_id
427 AND LER.LER_ID = BLI.LER_ID
428 AND BFT.PROCESS_DATE BETWEEN LER.EFFECTIVE_START_DATE AND LER.EFFECTIVE_END_DATE
429 AND BLI.TMPRL_FLAG = 'Y'
430 and BLI.PERSON_ID in
431 (select BPA.PERSON_ID
432 from BEN_PERSON_ACTIONS BPA
433 where BPA.ACTION_STATUS_CD in ('P','U')
434 AND BPA.BENEFIT_ACTION_ID = l_c0.benefit_action_id
435 )
436 GROUP BY LER.TYP_CD;
437
438 --
439 cursor c2 is
440 select count(*)
441 from ben_person_actions pac,
442 ben_benefit_actions bft
443 where bft.benefit_action_id = pac.benefit_action_id
444 /* and bft.request_id = p_concurrent_request_id */
445 and bft.benefit_action_id = l_c0.benefit_action_id
446 and pac.action_status_cd = 'P'
447 and exists (select null
448 from ben_batch_ler_info bli
449 where bli.benefit_action_id = bft.benefit_action_id
450 and bli.person_id = pac.person_id
451 and bli.tmprl_flag = 'Y');
452 --
453 cursor c3 is
454 select count(*)
455 from ben_person_actions pac,
456 ben_benefit_actions bft
457 where bft.benefit_action_id = pac.benefit_action_id
458 /* and bft.request_id = p_concurrent_request_id */
459 and bft.benefit_action_id = l_c0.benefit_action_id
460 and pac.action_status_cd = 'P'
461 and not exists (select null
462 from ben_batch_ler_info bli
463 where bli.benefit_action_id = bft.benefit_action_id
464 and bli.person_id = pac.person_id
465 and bli.tmprl_flag = 'Y');
466 --
467 l_age_changed number := 0;
468 l_los_changed number := 0;
469 l_comb_age_los_changed number := 0;
470 l_pft_changed number := 0;
471 l_comp_lvl_changed number := 0;
472 l_hrs_wkd_changed number := 0;
473
474 l_loss_of_eligibility number := 0;
475 l_late_payment number := 0;
476 l_max_enrollment_rchd number := 0;
477 l_period_enroll_changed number := 0;
478 l_voulntary_end_cvg number := 0;
479 l_waiting_satisfied number := 0;
480
481 l_c1 c1%rowtype;
482 --
483 begin
484 --
485 hr_utility.set_location('Entering :'||l_proc,10);
486 --
487 open c0;
488 fetch c0 into l_c0 ;
489 close c0 ;
490 --
491 open c1;
492 --
493 loop
494 --
495 fetch c1 into l_c1;
496 exit when c1%notfound;
497 --
498 if l_c1.typ_cd = 'DRVDAGE' then
499 --
500 l_age_changed := l_c1.amount;
501 --
502 elsif l_c1.typ_cd = 'DRVDLOS' then
503 --
504 l_los_changed := l_c1.amount;
505 --
506 elsif l_c1.typ_cd = 'DRVDCAL' then
507 --
508 l_comb_age_los_changed := l_c1.amount;
509 --
510 elsif l_c1.typ_cd = 'DRVDHRW' then
511 --
512 l_hrs_wkd_changed := l_c1.amount;
513 --
514 elsif l_c1.typ_cd = 'DRVDTPF' then
515 --
516 l_pft_changed := l_c1.amount;
517 --
518 elsif l_c1.typ_cd = 'DRVDCMP' then
519 --
520 l_comp_lvl_changed := l_c1.amount;
521 --
522 -- Added another six codes for bug 3690166
523 elsif l_c1.typ_cd = 'DRVDLSELG' then
524 --
525 l_loss_of_eligibility := l_c1.amount;
526 --
527 elsif l_c1.typ_cd = 'DRVDNLP' then
528 --
529 l_late_payment := l_c1.amount;
530 --
531 elsif l_c1.typ_cd = 'DRVDPOEELG' then
532 --
533 l_max_enrollment_rchd := l_c1.amount;
534 --
535 elsif l_c1.typ_cd = 'DRVDPOERT' then
536 --
537 l_period_enroll_changed := l_c1.amount;
538 --
539 elsif l_c1.typ_cd = 'DRVDVEC' then
540 --
541 l_voulntary_end_cvg := l_c1.amount;
542 --
543 elsif l_c1.typ_cd = 'DRVDWTGSTF' then
544 --
545 l_waiting_satisfied := l_c1.amount;
546 --
547 end if;
548 --
549 end loop;
550 --
551 close c1;
552 --
553 open c2;
554 --
555 fetch c2 into p_persons_with_potential;
556 --
557 close c2;
558 --
559 open c3;
560 --
561 fetch c3 into p_persons_no_potential;
562 --
563 close c3;
564 --
565 p_age_changed := l_age_changed;
566 p_los_changed := l_los_changed;
567 p_comb_age_los_changed := l_comb_age_los_changed;
568 p_pft_changed := l_pft_changed;
569 p_comp_lvl_changed := l_comp_lvl_changed;
570 p_hrs_wkd_changed := l_hrs_wkd_changed;
571
572 -- Added for bug 3690166
573 p_loss_of_eligibility := l_loss_of_eligibility;
574 p_late_payment := l_late_payment ;
575 p_max_enrollment_rchd := l_max_enrollment_rchd ;
576 p_period_enroll_changed := l_period_enroll_changed ;
577 p_voulntary_end_cvg := l_voulntary_end_cvg ;
578 p_waiting_satisfied := l_waiting_satisfied ;
579
580 p_number_of_events_created := l_age_changed +
581 l_los_changed +
582 l_comb_age_los_changed +
583 l_pft_changed +
584 l_comp_lvl_changed +
585 l_hrs_wkd_changed +
586 l_loss_of_eligibility +
587 l_late_payment +
588 l_max_enrollment_rchd +
589 l_period_enroll_changed +
590 l_voulntary_end_cvg +
591 l_waiting_satisfied ;
592 --
593 hr_utility.set_location('Leaving :'||l_proc,10);
594 --
595 end temporal_life_events;
596 -----------------------------------------------------------------------
597 procedure process_information
598 (p_concurrent_request_id in number,
599 p_start_date out nocopy varchar2,
600 p_end_date out nocopy varchar2,
601 p_start_time out nocopy varchar2,
602 p_end_time out nocopy varchar2,
603 p_elapsed_time out nocopy varchar2,
604 p_persons_selected out nocopy varchar2,
605 p_persons_processed out nocopy varchar2,
606 p_persons_unprocessed out nocopy varchar2,
607 p_persons_processed_succ out nocopy varchar2,
608 p_persons_errored out nocopy varchar2
609 ) is
610 --
611 l_proc varchar2(80) := g_package||'.process_information';
612 --
613 cursor c_proc_info is
614 select bpi.strt_dt,
615 bpi.end_dt,
616 bpi.strt_tm,
617 bpi.end_tm,
618 bpi.elpsd_tm,
619 bpi.per_slctd,
620 bpi.per_proc,
621 bpi.per_unproc,
622 bpi.per_proc_succ,
623 bpi.per_err
624 from ben_batch_proc_info bpi,
625 ben_benefit_actions bft
626 where bft.benefit_action_id = bpi.benefit_action_id
627 and bft.request_id = p_concurrent_request_id;
628 --
629 l_proc_info c_proc_info%rowtype;
630 --
631 begin
632 --
633 hr_utility.set_location('Entering :'||l_proc,10);
634 --
635 -- Get execution control data
636 --
637 open c_proc_info;
638 --
639 fetch c_proc_info into p_start_date,
640 p_end_date,
641 p_start_time,
642 p_end_time,
643 p_elapsed_time,
644 p_persons_selected,
645 p_persons_processed,
646 p_persons_unprocessed,
647 p_persons_processed_succ,
648 p_persons_errored;
649 close c_proc_info;
650 --
651 hr_utility.set_location('Leaving :'||l_proc,10);
652 --
653 end process_information;
654 -----------------------------------------------------------------------
655 procedure activity_summary_by_action
656 (p_concurrent_request_id in number,
657 p_without_active_life_event out nocopy varchar2,
658 p_with_active_life_event out nocopy varchar2,
659 p_no_life_event_created out nocopy varchar2,
660 p_life_event_open_and_closed out nocopy varchar2,
661 p_life_event_created out nocopy varchar2,
662 p_life_event_still_active out nocopy varchar2,
663 p_life_event_closed out nocopy varchar2,
664 p_life_event_replaced out nocopy varchar2,
665 p_life_event_dsgn_only out nocopy varchar2,
666 p_life_event_choices out nocopy varchar2,
667 p_life_event_no_effect out nocopy varchar2,
668 p_life_event_rt_pr_chg out nocopy varchar2) is
669 --
670 l_proc varchar2(80) := g_package||'.activity_summary_by_action';
671 --
672 cursor c0 is
673 select benefit_action_id
674 from ben_benefit_actions bft
675 where bft.request_id = p_concurrent_request_id ;
676 --
677 l_c0 c0%rowtype;
678 --
679 cursor c_ler_info is
680 select replcd_flag,
681 crtd_flag,
682 not_crtd_flag,
683 stl_actv_flag,
684 clsd_flag,
685 open_and_clsd_flag,
686 bli.benefit_action_id,
687 bli.person_id
688 from ben_batch_ler_info bli,
689 ben_benefit_actions bft,
690 ben_person_actions bpa
691 where bft.benefit_action_id = bli.benefit_action_id
692 and bli.tmprl_flag = 'N'
693 and bft.benefit_action_id = l_c0.benefit_action_id
694 and bpa.benefit_action_id = bft.benefit_action_id
695 and bpa.person_id = bli.person_id
696 and bpa.action_status_cd <> 'E';
697
698 l_ler_info c_ler_info%rowtype;
699
700 /* Convert to using count */
701 cursor c_choices is
702 select null
703 from ben_batch_elctbl_chc_info epe,
704 ben_per_in_ler pil,
705 ben_elig_per_elctbl_chc epe1
706 where epe.benefit_action_id = l_ler_info.benefit_action_id
707 and epe.person_id= l_ler_info.person_id
708 and pil.person_id=epe.person_id
709 and epe1.per_in_ler_id=pil.per_in_ler_id
710 and epe1.elctbl_flag='Y'
711 and rownum=1;
712 cursor c_rate_prem_cvg_change is
713 select NULL
714 from ben_batch_ler_info bli
715 where bli.benefit_action_id = l_ler_info.benefit_action_id
716 and bli.person_id = l_ler_info.person_id
717 and exists (
718 select null
719 from ben_prtt_enrt_rslt_f pen
720 where pen.per_in_ler_id=bli.per_in_ler_id
721 and rownum=1
722 union
723 select null
724 from ben_prtt_rt_val prv
725 where prv.per_in_ler_id=bli.per_in_ler_id
726 and rownum=1
727 union
728 select null
729 from ben_prtt_prem_f ppe
730 where ppe.per_in_ler_id=bli.per_in_ler_id
731 and rownum=1
732 )
733 and rownum=1;
734 --
735 l_without_active_life_event number :=0;
736 l_with_active_life_event number :=0;
737 l_no_life_event_created number :=0;
738 l_life_event_open_and_closed number :=0;
739 l_life_event_created number :=0;
740 l_life_event_still_active number :=0;
741 l_life_event_closed number :=0;
742 l_life_event_replaced number :=0;
743 --
744 l_life_event_dsgn_only number :=0;
745 l_life_event_choices number :=0;
746 l_life_event_no_effect number :=0;
747 l_life_event_rt_pr_chg number :=0;
748 l_dummy varchar2(30);
749 --
750 begin
751 --
752 hr_utility.set_location('Entering :'||l_proc,10);
753 --
754 open c0 ;
755 fetch c0 into l_c0;
756 close c0;
757 -- Get execution control data
758 --
759 open c_ler_info;
760 --
761 loop
762 --
763 fetch c_ler_info into l_ler_info;
764 exit when c_ler_info%notfound;
765 --
766 if l_ler_info.replcd_flag = 'Y' then
767 --
768 l_with_active_life_event := l_with_active_life_event+1;
769 l_life_event_replaced := l_life_event_replaced+1;
770 --
771 elsif l_ler_info.crtd_flag = 'Y' then
772 --
773 l_without_active_life_event := l_without_active_life_event+1;
774 l_life_event_created := l_life_event_created+1;
775 --
776 -- Count choices
777 --
778 open c_choices;
779 fetch c_choices into l_dummy;
780 if c_choices%found then
781 l_life_event_choices:=l_life_event_choices+1;
782 else
783 --
784 -- ben_batch_dpnt_info
785 --
786 -- Sum for person all choices
787 --
788 l_life_event_dsgn_only:=l_life_event_dsgn_only+1;
789 end if;
790 close c_choices;
791 --
792 elsif l_ler_info.not_crtd_flag = 'Y' then
793 --
794 l_without_active_life_event := l_without_active_life_event+1;
795 l_no_life_event_created := l_no_life_event_created+1;
796 --
797 elsif l_ler_info.open_and_clsd_flag = 'Y' then
798 --
799 l_life_event_open_and_closed := l_life_event_open_and_closed+1;
800 l_without_active_life_event := l_without_active_life_event+1;
801 open c_rate_prem_cvg_change;
802 fetch c_rate_prem_cvg_change into l_dummy;
803 if c_rate_prem_cvg_change%found then
804 l_life_event_rt_pr_chg:=l_life_event_rt_pr_chg+1;
805 else
806 l_life_event_no_effect:=l_life_event_no_effect+1;
807 end if;
808 close c_rate_prem_cvg_change;
809 --
810 elsif l_ler_info.stl_actv_flag = 'Y' then
811 --
812 l_life_event_still_active := l_life_event_still_active+1;
813 l_with_active_life_event := l_with_active_life_event+1;
814 --
815 elsif l_ler_info.clsd_flag = 'Y' then
816 --
817 l_life_event_closed := l_life_event_closed+1;
818 l_with_active_life_event := l_with_active_life_event+1;
819 --
820 end if;
821 --
822 end loop;
823 --
824 close c_ler_info;
825 --
826 p_without_active_life_event := l_without_active_life_event;
827 p_with_active_life_event := l_with_active_life_event;
828 p_no_life_event_created := l_no_life_event_created;
829 p_life_event_open_and_closed := l_life_event_open_and_closed;
830 p_life_event_created := l_life_event_created;
831 p_life_event_still_active := l_life_event_still_active;
832 p_life_event_closed := l_life_event_closed;
833 p_life_event_replaced := l_life_event_replaced;
834 --
835 p_life_event_dsgn_only := l_life_event_dsgn_only;
836 p_life_event_choices := l_life_event_choices;
837 p_life_event_no_effect := l_life_event_no_effect;
838 p_life_event_rt_pr_chg := l_life_event_rt_pr_chg;
839 --
840 hr_utility.set_location('Leaving :'||l_proc,10);
841 --
842 end activity_summary_by_action;
843 -----------------------------------------------------------------------
844 -- Procedure activity_summary_by_action is overloaded as two more parameters for life
845 -- event collapsed and life event collision added
846
847 procedure activity_summary_by_action
848 (p_concurrent_request_id in number,
849 p_without_active_life_event out nocopy varchar2,
850 p_with_active_life_event out nocopy varchar2,
851 p_no_life_event_created out nocopy varchar2,
852 p_life_event_open_and_closed out nocopy varchar2,
853 p_life_event_created out nocopy varchar2,
854 p_life_event_still_active out nocopy varchar2,
855 p_life_event_closed out nocopy varchar2,
856 p_life_event_replaced out nocopy varchar2,
857 p_life_event_dsgn_only out nocopy varchar2,
858 p_life_event_choices out nocopy varchar2,
859 p_life_event_no_effect out nocopy varchar2,
860 p_life_event_rt_pr_chg out nocopy varchar2,
861 p_life_event_collapsed out nocopy varchar2,
862 p_life_event_collision out nocopy varchar2) is
863 --
864 l_proc varchar2(80) := g_package||'.activity_summary_by_action';
865 --
866 --
867 cursor c_ler_info is
868 select replcd_flag,
869 crtd_flag,
870 not_crtd_flag,
871 stl_actv_flag,
872 clsd_flag,
873 open_and_clsd_flag,
874 clpsd_flag,
875 clsn_flag,
876 bli.benefit_action_id,
877 bli.person_id
878 from ben_batch_ler_info bli,
879 ben_benefit_actions bft,
880 /* Bug 2243050: Check whether the person has errored out nocopy */
881 ben_person_actions bpa
882 /* Bug 2243050: Check whether the person has errored out nocopy */
883 where bft.benefit_action_id = bli.benefit_action_id
884 and bli.tmprl_flag = 'N'
885 and bft.request_id = p_concurrent_request_id
886 /* Bug 2243050: Check whether the person has errored out nocopy */
887 and bpa.benefit_action_id = bli.benefit_action_id
888 and bpa.person_id = bli.person_id
889 and bpa.action_status_cd <> 'E';
890 /* Bug 2243050: Check whether the person has errored out nocopy */
891
892 l_ler_info c_ler_info%rowtype;
893 --
894 cursor c_choices is
895 select null
896 from ben_batch_elctbl_chc_info epe,
897 ben_per_in_ler pil,
898 ben_elig_per_elctbl_chc epe1
899 where epe.benefit_action_id = l_ler_info.benefit_action_id
900 and epe.person_id= l_ler_info.person_id
901 and pil.person_id=epe.person_id
902 and epe1.per_in_ler_id=pil.per_in_ler_id
903 and epe1.elctbl_flag='Y'
904 and rownum=1;
905 cursor c_rate_prem_cvg_change is
906 select NULL
907 from ben_batch_ler_info bli
908 where bli.benefit_action_id = l_ler_info.benefit_action_id
909 and bli.person_id = l_ler_info.person_id
910 and exists (
911 select null
912 from ben_prtt_enrt_rslt_f pen
913 where pen.per_in_ler_id=bli.per_in_ler_id
914 and rownum=1
915 union
916 select null
917 from ben_prtt_rt_val prv
918 where prv.per_in_ler_id=bli.per_in_ler_id
919 and rownum=1
920 union
921 select null
922 from ben_prtt_prem_f ppe
923 where ppe.per_in_ler_id=bli.per_in_ler_id
924 and rownum=1
925 )
926 and rownum=1;
927 --
928 l_without_active_life_event number :=0;
929 l_with_active_life_event number :=0;
930 l_no_life_event_created number :=0;
931 l_life_event_open_and_closed number :=0;
932 l_life_event_created number :=0;
933 l_life_event_still_active number :=0;
934 l_life_event_closed number :=0;
935 l_life_event_replaced number :=0;
936 --
937 l_life_event_dsgn_only number :=0;
938 l_life_event_choices number :=0;
939 l_life_event_no_effect number :=0;
940 l_life_event_rt_pr_chg number :=0;
941 l_dummy varchar2(30);
942 l_life_event_collapsed number :=0;
943 l_life_event_collision number :=0;
944 --
945 begin
946 --
947 hr_utility.set_location('Entering :'||l_proc,10);
948 --
949 -- Get execution control data
950 --
951 open c_ler_info;
952 --
953 loop
954 --
955 fetch c_ler_info into l_ler_info;
956 exit when c_ler_info%notfound;
957 --
958 if l_ler_info.replcd_flag = 'Y' then
959 --
960 l_with_active_life_event := l_with_active_life_event+1;
961 l_life_event_replaced := l_life_event_replaced+1;
962 --
963 elsif l_ler_info.crtd_flag = 'Y' then
964 --
965 l_without_active_life_event := l_without_active_life_event+1;
966 l_life_event_created := l_life_event_created+1;
967 --
968 -- Count choices
969 --
970 open c_choices;
971 fetch c_choices into l_dummy;
972 if c_choices%found then
973 l_life_event_choices:=l_life_event_choices+1;
974 else
975 --
976 --
977 l_life_event_dsgn_only:=l_life_event_dsgn_only+1;
978 end if;
979 close c_choices;
980 --
981 elsif l_ler_info.not_crtd_flag = 'Y' then
982 --
983 l_without_active_life_event := l_without_active_life_event+1;
984 l_no_life_event_created := l_no_life_event_created+1;
985 --
986 elsif l_ler_info.open_and_clsd_flag = 'Y' then
987 --
988 l_life_event_open_and_closed := l_life_event_open_and_closed+1;
989 l_without_active_life_event := l_without_active_life_event+1;
990 open c_rate_prem_cvg_change;
991 fetch c_rate_prem_cvg_change into l_dummy;
992 if c_rate_prem_cvg_change%found then
993 l_life_event_rt_pr_chg:=l_life_event_rt_pr_chg+1;
994 else
995 l_life_event_no_effect:=l_life_event_no_effect+1;
996 end if;
997 close c_rate_prem_cvg_change;
998 --
999 elsif l_ler_info.clpsd_flag = 'Y' then
1000 --
1001 l_life_event_collapsed := l_life_event_collapsed +1;
1002 l_with_active_life_event := l_with_active_life_event+1;
1003 --
1004 elsif l_ler_info.clsn_flag = 'Y' then
1005 --
1006 l_life_event_collision := l_life_event_collision +1;
1007 l_with_active_life_event := l_with_active_life_event+1;
1008 --
1009 elsif l_ler_info.stl_actv_flag = 'Y' then
1010 --
1011 l_life_event_still_active := l_life_event_still_active+1;
1012 l_with_active_life_event := l_with_active_life_event+1;
1013 --
1014 elsif l_ler_info.clsd_flag = 'Y' then
1015 --
1016 l_life_event_closed := l_life_event_closed+1;
1017 l_with_active_life_event := l_with_active_life_event+1;
1018 --
1019 end if;
1020 --
1021 end loop;
1022 --
1023 close c_ler_info;
1024 --
1025 p_without_active_life_event := l_without_active_life_event;
1026 p_with_active_life_event := l_with_active_life_event;
1027 p_no_life_event_created := l_no_life_event_created;
1028 p_life_event_open_and_closed := l_life_event_open_and_closed;
1029 p_life_event_created := l_life_event_created;
1030 p_life_event_still_active := l_life_event_still_active;
1031 p_life_event_closed := l_life_event_closed;
1032 p_life_event_replaced := l_life_event_replaced;
1033 --
1034 p_life_event_dsgn_only := l_life_event_dsgn_only;
1035 p_life_event_choices := l_life_event_choices;
1036 p_life_event_no_effect := l_life_event_no_effect;
1037 p_life_event_rt_pr_chg := l_life_event_rt_pr_chg;
1038 p_life_event_collapsed := l_life_event_collapsed;
1039 p_life_event_collision := l_life_event_collision;
1040 --
1041 hr_utility.set_location('Leaving :'||l_proc,10);
1042 --
1043
1044 end activity_summary_by_action;
1045 -------------------------------------------------------------------------------------------
1046 procedure batch_reports
1047 (p_concurrent_request_id in number,
1048 p_mode in varchar2 default 'S',
1049 p_report_type in varchar2) is
1050 --
1051 l_proc varchar2(80) := g_package||'.batch_reports';
1052 l_program_name varchar2(30);
1053 l_retcode number;
1054 l_errbuf varchar2(2000);
1055 --
1056 begin
1057 --
1058 hr_utility.set_location('Entering :'||l_proc,10);
1059 --
1060 -- Reports are not relevant in collective agreement mode
1061 --
1062 if p_mode = 'A'
1063 then
1064 --
1065 return;
1066 --
1067 end if;
1068 --
1069 -- This report runs a concurrent request to submit a reportwriter action.
1070 -- The procedure is used as an easy way to call batch reports from
1071 -- batch processes.
1072 --
1073 if p_report_type = 'GENERIC_LOG' then
1074 --
1075 l_program_name := 'BENGELOG';
1076 --
1077 elsif p_report_type = 'ACTIVITY_SUMMARY' then
1078 --
1079 if p_mode in ('L','M') then
1080 --
1081 l_program_name := 'BENACTIV';
1082 --
1083 elsif p_mode = 'S' then
1084 --
1085 l_program_name := 'BENACTIV';
1086 --
1087 --
1088 -- CWB Change : Extend the C mode to W (Comp Workbench mode)
1089 --
1090 elsif p_mode in ('C', 'W') then
1091 --
1092 l_program_name := 'BENACTIV';
1093 --
1094 elsif p_mode = 'T' then
1095 --
1096 l_program_name := 'BENACTIV';
1097 --
1098 --
1099 -- Bug 2945455 -- Personal Action Changes
1100 --
1101 elsif p_mode = 'P' then
1102 --
1103 l_program_name := 'BENACTIV';
1104 --
1105 -- 2940151
1106 elsif p_mode = 'G' then
1107 --
1108 l_program_name := 'BENACTIV';
1109 --
1110
1111 end if;
1112 --
1113 elsif p_report_type = 'ERROR_BY_ERROR_TYPE' then
1114 --
1115 l_program_name := 'BENERRTY';
1116 --
1117 elsif p_report_type = 'ERROR_BY_PERSON' then
1118 --
1119 l_program_name := 'BENERRPE';
1120 --
1121 end if;
1122 --
1123 submit_request(errbuf => l_errbuf,
1124 retcode => l_retcode,
1125 p_program_name => l_program_name,
1126 p_concurrent_request_id => p_concurrent_request_id);
1127 --
1128 hr_utility.set_location('Leaving :'||l_proc,10);
1129 --
1130 end batch_reports;
1131 -----------------------------------------------------------------------
1132 procedure submit_request(errbuf out nocopy varchar2,
1133 retcode out nocopy number,
1134 p_program_name in varchar2,
1135 p_concurrent_request_id in number) is
1136 --
1137 l_proc varchar2(80) := g_package||'.submit_request';
1138 l_request_id number;
1139 --
1140 begin
1141 --
1142 hr_utility.set_location('Entering :'||l_proc,10);
1143 --
1144 l_request_id := fnd_request.submit_request
1145 (application => 'BEN',
1146 program => p_program_name,
1147 description => NULL,
1148 sub_request => FALSE,
1149 argument1 => p_concurrent_request_id);
1150 --
1151 if l_request_id = 0 then
1152 --
1153 fnd_message.set_name('BEN','BEN_92110_CONC_REQUEST');
1154 fnd_message.set_token('NAME',p_program_name);
1155 fnd_message.raise_error;
1156 --
1157 end if;
1158 --
1159 hr_utility.set_location('Leaving :'||l_proc,10);
1160 --
1161 end submit_request;
1162 -----------------------------------------------------------------------
1163 procedure event_summary(
1164 p_concurrent_request_id in number,
1165 p_life_event_totals out nocopy ben_batch_reporting.le_total
1166 ) is
1167 --
1168 cursor c_ler_detail_info is
1169 select replcd_flag,
1170 crtd_flag,
1171 not_crtd_flag,
1172 stl_actv_flag,
1173 clsd_flag,
1174 open_and_clsd_flag,
1175 ler.name
1176 from ben_batch_ler_info bli,
1177 ben_benefit_actions bft,
1178 ben_ler_f ler
1179 where bft.benefit_action_id = bli.benefit_action_id
1180 and bft.request_id = p_concurrent_request_id
1181 and bli.ler_id=ler.ler_id
1182 and trunc(sysdate) between
1183 ler.effective_start_date and ler.effective_end_date
1184 ;
1185 --
1186 l_life_event_totals ben_batch_reporting.le_total;
1187 l_number_rows number:=0;
1188 l_new_closed_cd varchar2(1);
1189 l_found boolean;
1190 --
1191 begin
1192 for l_row in c_ler_detail_info loop
1193 if l_row.crtd_flag='Y' then
1194 l_new_closed_cd:='C';
1195 elsif l_row.open_and_clsd_flag='Y' then
1196 l_new_closed_cd:='N';
1197 else
1198 l_new_closed_cd:=null;
1199 end if;
1200 if l_new_closed_cd is not null then
1201 if l_number_rows=0 then
1202 l_number_rows:=l_number_rows+1;
1203 l_life_event_totals(l_number_rows).ler_name:=l_row.name;
1204 l_life_event_totals(l_number_rows).total:=1;
1205 l_life_event_totals(l_number_rows).new_closed_cd:=l_new_closed_cd;
1206 else
1207 l_found:=false;
1208 for i in 1..l_number_rows loop
1209 if l_life_event_totals(i).ler_name=l_row.name and
1210 l_life_event_totals(i).new_closed_cd=l_new_closed_cd then
1211 l_life_event_totals(i).total:=l_life_event_totals(i).total+1;
1212 l_found:=true;
1213 exit;
1214 end if;
1215 end loop;
1216 if not l_found then
1217 l_number_rows:=l_number_rows+1;
1218 l_life_event_totals(l_number_rows).ler_name:=l_row.name;
1219 l_life_event_totals(l_number_rows).total:=1;
1220 l_life_event_totals(l_number_rows).new_closed_cd:=l_new_closed_cd;
1221 end if;
1222 end if;
1223 end if;
1224 end loop;
1225 p_life_event_totals:=l_life_event_totals;
1226 end event_summary;
1227 -----------------------------------------------------------------------
1228 end ben_batch_reporting;