[Home] [Help]
PACKAGE BODY: APPS.BEN_TEST_HARNESS
Source
1 package body ben_test_harness as
2 /* $Header: bentsthn.pkb 120.0 2005/05/28 09:31:40 appldev noship $ */
3 --
4 g_package varchar2(50) := 'ben_test_harness.';
5 --
6 g_alertsev1reas_va benutils.g_v2_150_table := benutils.g_v2_150_table();
7 g_alertsev1bftid_va benutils.g_number_table := benutils.g_number_table();
8 g_alertsev1prevbftid_va benutils.g_number_table := benutils.g_number_table();
9 --
10 g_alertsev2reas_va benutils.g_v2_150_table := benutils.g_v2_150_table();
11 g_alertsev2bftid_va benutils.g_number_table := benutils.g_number_table();
12 --
13 g_alertsev1_en pls_integer := 1;
14 g_alertsev2_en pls_integer := 1;
15 --
16 PROCEDURE filter_clcode_nondiscreps
17 (p_clcode_va in out nocopy benutils.g_v2_150_table
18 ,p_clcodecnt_va in out nocopy benutils.g_number_table
19 ,p_clsumovn_va in out nocopy benutils.g_number_table
20 ,p_clminesd_va in out nocopy benutils.g_date_table
21 ,p_clmineed_va in out nocopy benutils.g_date_table
22 )
23 IS
24 --
25 l_tmpclcode_va benutils.g_v2_150_table := benutils.g_v2_150_table();
26 l_tmpclcodecnt_va benutils.g_number_table := benutils.g_number_table();
27 l_tmpclsumovn_va benutils.g_number_table := benutils.g_number_table();
28 l_tmpclminesd_va benutils.g_date_table := benutils.g_date_table();
29 l_tmpclmineed_va benutils.g_date_table := benutils.g_date_table();
30 --
31 l_tmp_en pls_integer;
32 --
33 BEGIN
34 --
35 -- Filter out non discrepancy rollups
36 --
37 if p_clcode_va.count > 0 then
38 --
39 l_tmpclcode_va.delete;
40 l_tmpclcodecnt_va.delete;
41 l_tmpclsumovn_va.delete;
42 l_tmpclminesd_va.delete;
43 l_tmpclmineed_va.delete;
44 l_tmp_en := 1;
45 --
46 for filtvaen in p_clcode_va.first..p_clcode_va.last
47 loop
48 --
49 if instr(p_clcode_va(filtvaen),'WWBUGS') > 0
50 then
51 --
52 null;
53 --
54 else
55 --
56 l_tmpclcode_va.extend(1);
57 l_tmpclcodecnt_va.extend(1);
58 l_tmpclsumovn_va.extend(1);
59 l_tmpclminesd_va.extend(1);
60 l_tmpclmineed_va.extend(1);
61 --
62 l_tmpclcode_va(l_tmp_en) := p_clcode_va(filtvaen);
63 l_tmpclcodecnt_va(l_tmp_en) := p_clcodecnt_va(filtvaen);
64 l_tmpclsumovn_va(l_tmp_en) := p_clsumovn_va(filtvaen);
65 l_tmpclminesd_va(l_tmp_en) := p_clminesd_va(filtvaen);
66 l_tmpclmineed_va(l_tmp_en) := p_clmineed_va(filtvaen);
67 l_tmp_en := l_tmp_en+1;
68 --
69 end if;
70 --
71 end loop;
72 --
73 p_clcode_va := l_tmpclcode_va;
74 p_clcodecnt_va := l_tmpclcodecnt_va;
75 p_clsumovn_va := l_tmpclsumovn_va;
76 p_clminesd_va := l_tmpclminesd_va;
77 p_clmineed_va := l_tmpclmineed_va;
78 --
79 end if;
80 --
81 end filter_clcode_nondiscreps;
82 --
83 PROCEDURE filter_discrepperids
84 (p_perid_va in benutils.g_number_table
85 ,p_mmperid_va in out nocopy benutils.g_number_table
86 ,p_mmperlud_va in out nocopy benutils.g_date_table
87 ,p_mmcombnm_va in out nocopy benutils.g_varchar2_table
88 ,p_mmcombnm2_va in out nocopy benutils.g_varchar2_table
89 ,p_mmcombid_va in out nocopy benutils.g_number_table
90 ,p_mmcombid2_va in out nocopy benutils.g_number_table
91 ,p_mmcnt_va in out nocopy benutils.g_number_table
92 ,p_exclperid_va in out nocopy benutils.g_number_table
93 )
94 IS
95 --
96 l_newmmperid_va benutils.g_number_table := benutils.g_number_table();
97 l_newmmperlud_va benutils.g_date_table := benutils.g_date_table();
98 l_newmmcombnm_va benutils.g_varchar2_table := benutils.g_varchar2_table();
99 l_newmmcombnm2_va benutils.g_varchar2_table := benutils.g_varchar2_table();
100 l_newmmcombid_va benutils.g_number_table := benutils.g_number_table();
101 l_newmmcombid2_va benutils.g_number_table := benutils.g_number_table();
102 l_newmmcnt_va benutils.g_number_table := benutils.g_number_table();
103 --
104 l_exclperid_va benutils.g_number_table := benutils.g_number_table();
105 --
106 l_exclvaen pls_integer;
107 l_mmvaen pls_integer;
108 l_perid_match boolean;
109 --
110 l_mmperlud_cnt pls_integer;
111 l_mmcombid_cnt pls_integer;
112 l_mmcombid2_cnt pls_integer;
113 --
114 BEGIN
115 --
116 -- Filter out application error person ids
117 --
118 if p_mmperid_va.count > 0
119 and p_perid_va.count > 0
120 then
121 --
122 l_mmvaen := 1;
123 l_exclvaen := 1;
124 --
125 l_mmperlud_cnt := p_mmperlud_va.count;
126 l_mmcombid2_cnt := p_mmcombid2_va.count;
127 --
128 for vaen in p_mmperid_va.first..p_mmperid_va.last
129 loop
130 --
131 l_perid_match := FALSE;
132 --
133 for subvaen in p_perid_va.first..p_perid_va.last
134 loop
135 --
136 if p_perid_va(subvaen) = p_mmperid_va(vaen)
137 then
138 --
139 l_perid_match := TRUE;
140 exit;
141 --
142 end if;
143 --
144 end loop;
145 --
146 if not l_perid_match
147 then
148 --
149 l_newmmperid_va.extend(1);
150 l_newmmperlud_va.extend(1);
151 l_newmmcnt_va.extend(1);
152 --
153 l_newmmperid_va(l_mmvaen) := p_mmperid_va(vaen);
154 l_newmmcnt_va(l_mmvaen) := p_mmcnt_va(vaen);
155 --
156 if l_mmcombid_cnt > 0
157 then
158 --
159 l_newmmcombnm_va.extend(1);
160 l_newmmcombid_va.extend(1);
161 --
162 l_newmmcombnm_va(l_mmvaen) := p_mmcombnm_va(vaen);
163 l_newmmcombid_va(l_mmvaen) := p_mmcombid_va(vaen);
164 --
165 end if;
166 --
167 if l_mmperlud_cnt > 0
168 then
169 --
170 l_newmmperlud_va(l_mmvaen) := p_mmperlud_va(vaen);
171 --
172 end if;
173 --
174 if l_mmcombid2_cnt > 0
175 then
176 --
177 l_newmmcombnm2_va.extend(1);
178 l_newmmcombid2_va.extend(1);
179 l_newmmcombnm2_va(l_mmvaen) := p_mmcombnm2_va(vaen);
180 l_newmmcombid2_va(l_mmvaen) := p_mmcombid2_va(vaen);
181 --
182 end if;
183 --
184 l_mmvaen := l_mmvaen+1;
185 --
186 else
187 --
188 -- Excludes
189 --
190 l_exclperid_va.extend(1);
191 --
192 l_exclperid_va(l_exclvaen) := p_mmperid_va(vaen);
193 l_exclvaen := l_exclvaen+1;
194 --
195 end if;
196 --
197 end loop;
198 --
199 p_mmperid_va := l_newmmperid_va;
200 p_mmperlud_va := l_newmmperlud_va;
201 p_mmcombnm_va := l_newmmcombnm_va;
202 p_mmcombnm2_va := l_newmmcombnm2_va;
203 p_mmcombid_va := l_newmmcombid_va;
204 p_mmcombid2_va := l_newmmcombid2_va;
205 p_mmcnt_va := l_newmmcnt_va;
206 --
207 end if;
208 --
209 p_exclperid_va := l_exclperid_va;
210 --
211 end filter_discrepperids;
212 --
213 PROCEDURE filter_ledatachgperids
214 (p_lud in date
215 --
216 ,p_mmperid_va in out nocopy benutils.g_number_table
217 ,p_mmperlud_va in out nocopy benutils.g_date_table
218 ,p_mmcombnm_va in out nocopy benutils.g_varchar2_table
219 ,p_mmcombnm2_va in out nocopy benutils.g_varchar2_table
220 ,p_mmcombid_va in out nocopy benutils.g_number_table
221 ,p_mmcombid2_va in out nocopy benutils.g_number_table
222 ,p_mmcnt_va in out nocopy benutils.g_number_table
223 ,p_exclperid_va in out nocopy benutils.g_number_table
224 )
225 IS
226 --
227 l_newmmperid_va benutils.g_number_table := benutils.g_number_table();
228 l_newmmperlud_va benutils.g_date_table := benutils.g_date_table();
229 l_newmmcombnm_va benutils.g_varchar2_table := benutils.g_varchar2_table();
230 l_newmmcombnm2_va benutils.g_varchar2_table := benutils.g_varchar2_table();
231 l_newmmcombid_va benutils.g_number_table := benutils.g_number_table();
232 l_newmmcombid2_va benutils.g_number_table := benutils.g_number_table();
233 l_newmmcnt_va benutils.g_number_table := benutils.g_number_table();
234 --
235 l_exclperid_va benutils.g_number_table := benutils.g_number_table();
236 --
237 l_exclvaen pls_integer;
238 l_mmvaen pls_integer;
239 --
240 l_mmperlud_cnt pls_integer;
241 l_mmcombid_cnt pls_integer;
242 l_mmcombid2_cnt pls_integer;
243 --
244 BEGIN
245 --
246 -- Check for person lud information
247 --
248 l_mmperlud_cnt := p_mmperlud_va.count;
249 --
250 if l_mmperlud_cnt > 0
251 then
252 --
253 l_mmvaen := 1;
254 l_exclvaen := 1;
255 --
256 l_mmcombid_cnt := p_mmcombid_va.count;
257 l_mmcombid2_cnt := p_mmcombid2_va.count;
258 --
259 for vaen in p_mmperid_va.first..p_mmperid_va.last
260 loop
261 --
262 -- Check for a person data change
263 --
264 if p_mmperlud_va(vaen) < p_lud
265 then
266 --
267 l_newmmperid_va.extend(1);
268 l_newmmperlud_va.extend(1);
269 l_newmmcnt_va.extend(1);
270 --
271 l_newmmperid_va(l_mmvaen) := p_mmperid_va(vaen);
272 l_newmmperlud_va(l_mmvaen) := p_mmperlud_va(vaen);
273 l_newmmcnt_va(l_mmvaen) := p_mmcnt_va(vaen);
274 --
275 if l_mmcombid_cnt > 0
276 then
277 --
278 l_newmmcombnm_va.extend(1);
279 l_newmmcombid_va.extend(1);
280 --
281 l_newmmcombnm_va(l_mmvaen) := p_mmcombnm_va(vaen);
282 l_newmmcombid_va(l_mmvaen) := p_mmcombid_va(vaen);
283 --
284 end if;
285 --
286 if l_mmcombid2_cnt > 0
287 then
288 --
289 l_newmmcombnm2_va.extend(1);
290 l_newmmcombid2_va.extend(1);
291 --
292 l_newmmcombnm2_va(l_mmvaen) := p_mmcombnm2_va(vaen);
293 l_newmmcombid2_va(l_mmvaen) := p_mmcombid2_va(vaen);
294 --
295 end if;
296 --
297 l_mmvaen := l_mmvaen+1;
298 --
299 else
300 --
301 -- Excludes
302 --
303 l_exclperid_va.extend(1);
304 --
305 l_exclperid_va(l_exclvaen) := p_mmperid_va(vaen);
306 l_exclvaen := l_exclvaen+1;
307 --
308 end if;
309 --
310 end loop;
311 --
312 p_mmperid_va := l_newmmperid_va;
313 p_mmperlud_va := l_newmmperlud_va;
314 p_mmcombnm_va := l_newmmcombnm_va;
315 p_mmcombnm2_va := l_newmmcombnm2_va;
316 p_mmcombid_va := l_newmmcombid_va;
317 p_mmcombid2_va := l_newmmcombid2_va;
318 p_mmcnt_va := l_newmmcnt_va;
319 --
320 end if;
321 --
322 p_exclperid_va := l_exclperid_va;
323 --
324 end filter_ledatachgperids;
325 --
326 PROCEDURE get_appl_error_dets
327 (p_bft_id in number
328 --
329 ,p_errmesscd_va in out nocopy benutils.g_v2_150_table
330 ,p_mxperid_va in out nocopy benutils.g_number_table
331 ,p_errcnt_va in out nocopy benutils.g_number_table
332 ,p_errtot in out nocopy number
333 )
334 IS
335 --
336 l_errmesscd_va benutils.g_v2_150_table := benutils.g_v2_150_table();
337 l_mxperid_va benutils.g_number_table := benutils.g_number_table();
338 l_errcnt_va benutils.g_number_table := benutils.g_number_table();
339 --
340 l_errtot pls_integer;
341 --
342 cursor c_gbenrepercdsum
343 (c_bft_id in number
344 )
345 is
346 select ERROR_MESSAGE_CODE,
347 max(person_id) mx_perid,
348 count(*) cnt
349 from ben_reporting
350 where benefit_action_id = c_bft_id
351 and ERROR_MESSAGE_CODE is not null
352 group by ERROR_MESSAGE_CODE
353 order by count(*) desc;
354 --
355 BEGIN
356 --
357 open c_gbenrepercdsum
358 (c_bft_id => p_bft_id
359 );
360 fetch c_gbenrepercdsum BULK COLLECT INTO l_errmesscd_va,
361 l_mxperid_va,
362 l_errcnt_va;
363 close c_gbenrepercdsum;
364 --
365 l_errtot := 0;
366 --
367 if l_errmesscd_va.count > 0
368 then
369 --
370 for vaen in l_errmesscd_va.first..l_errmesscd_va.last
371 loop
372 --
373 l_errtot := l_errtot+l_errcnt_va(vaen);
374 --
375 end loop;
376 --
377 end if;
378 --
379 p_errmesscd_va := l_errmesscd_va;
380 p_mxperid_va := l_mxperid_va;
381 p_errcnt_va := l_errcnt_va;
382 p_errtot := l_errtot;
383 --
384 end get_appl_error_dets;
385 --
386 procedure rollupcode_getrollupdesc
387 (p_rollup_code in varchar2
388 --
389 ,p_rollup_desc out nocopy varchar2
390 )
391 is
392 begin
393 --
394 if p_rollup_code = 'PIL'
395 then
396 --
397 p_rollup_desc := 'Life Event';
398 --
399 elsif p_rollup_code = 'PILBCKDT'
400 then
401 --
402 p_rollup_desc := 'Life Event Backed Out';
403 --
404 elsif p_rollup_code = 'PILSTRTD'
405 then
406 --
407 p_rollup_desc := 'Life Event Started';
408 --
409 elsif p_rollup_code = 'PILPROCD'
410 then
411 --
412 p_rollup_desc := 'Life Event Processed';
413 --
414 elsif p_rollup_code = 'PILVOIDD'
415 then
416 --
417 p_rollup_desc := 'Life Event Voided';
418 --
419 elsif p_rollup_code = 'PEP'
420 then
421 --
422 p_rollup_desc := 'Eligibility Program/PTIP/PLIP/Plan';
423 --
424 elsif p_rollup_code = 'PEPIE'
425 then
426 --
427 p_rollup_desc := 'Ineligibility Program/PTIP/PLIP/Plan';
428 --
429 elsif p_rollup_code = 'PEPPGM'
430 then
431 --
432 p_rollup_desc := 'Eligibility Program';
433 --
434 elsif p_rollup_code = 'PEPPTIP'
435 then
436 --
437 p_rollup_desc := 'Eligibility Plan Type In Program';
438 --
439 elsif p_rollup_code = 'PEPPLN'
440 then
441 --
442 p_rollup_desc := 'Eligibility Plan';
443 --
444 elsif p_rollup_code = 'PEPPLNIP'
445 then
446 --
447 p_rollup_desc := 'Eligibility Plan Not In Program';
448 --
449 elsif p_rollup_code = 'PEPPLIP'
450 then
451 --
452 p_rollup_desc := 'Eligibility Plan In Program';
453 --
454 elsif p_rollup_code = 'EPO'
455 then
456 --
457 p_rollup_desc := 'Eligibility Option/OIPLIP';
458 --
459 elsif p_rollup_code = 'EPOIE'
460 then
461 --
462 p_rollup_desc := 'Ineligibility Option/OIPLIP';
463 --
464 elsif p_rollup_code = 'EGD'
465 then
466 --
467 p_rollup_desc := 'Dependent Eligibility';
468 --
469 elsif p_rollup_code = 'EPE'
470 then
471 --
472 p_rollup_desc := 'Electability';
473 --
474 elsif p_rollup_code = 'EPEPLN'
475 then
476 --
477 p_rollup_desc := 'Electability Plan';
478 --
479 elsif p_rollup_code = 'EPEOIPL'
480 then
481 --
482 p_rollup_desc := 'Electability Option In Plan';
483 --
484 elsif p_rollup_code = 'EPEAUTOENR'
485 then
486 --
487 p_rollup_desc := 'Electability Automatic Enrollment';
488 --
489 elsif p_rollup_code = 'EPECURRENR'
490 then
491 --
492 p_rollup_desc := 'Electability Currently Enrolled';
493 --
494 elsif p_rollup_code = 'PEL'
495 then
496 --
497 p_rollup_desc := 'Electable Program/Plan not in Program';
498 --
499 elsif p_rollup_code = 'ECC'
500 then
501 --
502 p_rollup_desc := 'Electable Choice Certification';
503 --
504 elsif p_rollup_code = 'ENB'
505 then
506 --
507 p_rollup_desc := 'Coverage';
508 --
509 elsif p_rollup_code = 'EPR'
510 then
511 --
512 p_rollup_desc := 'Premium';
513 --
514 elsif p_rollup_code = 'ECR'
515 then
516 --
517 p_rollup_desc := 'Enrolment Rates';
518 --
519 elsif p_rollup_code = 'ECRFLXCR'
520 then
521 --
522 p_rollup_desc := 'Enrolment Rates Flex Credits';
523 --
524 elsif p_rollup_code = 'ECRSTD'
525 then
526 --
527 p_rollup_desc := 'Enrolment Rates Standard';
528 --
529 elsif p_rollup_code = 'PDP'
530 then
531 --
532 p_rollup_desc := 'Eligible Covered Dependent';
533 --
534 elsif p_rollup_code = 'PRV'
535 then
536 --
537 p_rollup_desc := 'Participant rate value';
538 --
539 elsif p_rollup_code = 'PRVFLFX'
540 then
541 --
542 p_rollup_desc := 'Participant rate value flat amount';
543 --
544 elsif p_rollup_code = 'PRVCVG'
545 then
546 --
547 p_rollup_desc := 'Participant rate value multiple of coverage';
548 --
549 elsif p_rollup_code = 'PRVNSVU'
550 then
551 --
552 p_rollup_desc := 'Participant rate value no standard values used';
553 --
554 elsif p_rollup_code = 'PRVCL'
555 then
556 --
557 p_rollup_desc := 'Participant rate multiple of compensation';
558 --
559 elsif p_rollup_code = 'PRVSAREC'
560 then
561 --
562 p_rollup_desc := 'Participant rate set annual rate equal coverage';
563 --
564 elsif p_rollup_code = 'PRVAP'
565 then
566 --
567 p_rollup_desc := 'Participant rate multiple of premium';
568 --
569 elsif p_rollup_code = 'PRVRL'
570 then
571 --
572 p_rollup_desc := 'Participant rate rule';
573 --
574 elsif p_rollup_code = 'PEN'
575 then
576 --
577 p_rollup_desc := 'Enrolment Result';
578 --
579 elsif p_rollup_code = 'PENAUTO'
580 then
581 --
582 p_rollup_desc := 'Enrolment Result Automatic';
583 --
584 elsif p_rollup_code = 'PENEXPL'
585 then
586 --
587 p_rollup_desc := 'Enrolment Result Explicit';
588 --
589 elsif p_rollup_code = 'PENDFLT'
590 then
591 --
592 p_rollup_desc := 'Enrolment Result Default';
593 --
594 elsif p_rollup_code = 'PENSSPND'
595 then
596 --
597 p_rollup_desc := 'Enrolment Result Suspended';
598 --
599 elsif p_rollup_code = 'PENBCKDT'
600 then
601 --
602 p_rollup_desc := 'Enrolment Result Backed Out';
603 --
604 elsif p_rollup_code = 'PENVOIDD'
605 then
606 --
607 p_rollup_desc := 'Enrolment Result Voided';
608 --
609 elsif p_rollup_code = 'BPL'
610 then
611 --
612 p_rollup_desc := 'Benefit Provider Ledger';
613 --
614 elsif p_rollup_code = 'PCM'
615 then
616 --
617 p_rollup_desc := 'Communication';
618 --
619 elsif p_rollup_code = 'PERACT'
620 then
621 --
622 p_rollup_desc := 'Person Action';
623 --
624 elsif p_rollup_code = 'PERACTPROC'
625 then
626 --
627 p_rollup_desc := 'Person Action Processed';
628 --
629 elsif p_rollup_code = 'PERACTERR'
630 then
631 --
632 p_rollup_desc := 'Person Action Errored';
633 --
634 elsif p_rollup_code = 'PERACTUNPROC'
635 then
636 --
637 p_rollup_desc := 'Person Action Unprocessed';
638 --
639 elsif p_rollup_code = 'WWBUGS'
640 then
641 --
642 p_rollup_desc := 'Applied Bug';
643 --
644 elsif p_rollup_code = 'WWBUGSBEN'
645 then
646 --
647 p_rollup_desc := 'Applied BEN Bug';
648 --
649 elsif p_rollup_code = 'WWBUGSPER'
650 then
651 --
652 p_rollup_desc := 'Applied PER Bug';
653 --
654 elsif p_rollup_code = 'WWBUGSPAY'
655 then
656 --
657 p_rollup_desc := 'Applied PAY Bug';
658 --
659 end if;
660 --
661 end;
662 --
663 function Var2Value_StripMltBlanks
664 (p_var2_value in varchar2
665 ) return varchar2
666 is
667 --
668 l_char_cnt pls_integer;
669 l_str_length pls_integer;
670 --
671 l_stripv2_value long;
672 l_var2_value long;
673 l_blankseq_count pls_integer;
674 --
675 begin
676 --
677 /*
678 --
679 -- Temporary
680 --
681 dbms_output.put_line(' BENTSTHN: length: '||length(p_var2_value)
682 ||' instr: '||instr(p_var2_value,' ')
683 );
684 --
685 */
686 if instr(p_var2_value,' ') > 0
687 then
688 --
689 l_var2_value := replace(replace(p_var2_value,fnd_global.local_chr(10),' '),fnd_global.local_chr(13),' ');
690 /*
691 --
692 -- Temporary
693 --
694 dbms_output.put_line(' BENTSTHN: length l_var2_value: '||length(l_var2_value)
695 );
696 --
697 */
698 --
699 l_char_cnt := 1;
700 l_str_length := length(l_var2_value);
701 l_stripv2_value := null;
702 l_blankseq_count := 0;
703 --
704 loop
705 --
706 if l_char_cnt >= l_str_length
707 then
708 --
709 exit;
710 --
711 end if;
712 --
713 if substr(l_var2_value,l_char_cnt,1) <> ' '
714 then
715 --
716 l_stripv2_value := l_stripv2_value||substr(l_var2_value,l_char_cnt,1);
717 --
718 l_blankseq_count := 0;
719 --
720 else
721 --
722 l_stripv2_value := l_stripv2_value||' ';
723 --
724 l_blankseq_count := 1;
725 --
726 end if;
727 --
728 l_char_cnt := l_char_cnt+1;
729 --
730 end loop;
731 /*
732 --
733 -- Temporary
734 --
735 dbms_output.put_line(' BENTSTHN: l_stripv2_value: '||substr(l_stripv2_value,200));
736 --
737 */
738 --
739 else
740 --
741 l_stripv2_value := p_var2_value;
742 --
743 end if;
744 --
745 return l_stripv2_value;
746 --
747 end Var2Value_StripMltBlanks;
748 --
749 procedure Var2Value_Portion
750 (p_var2_value in varchar2
751 ,p_portion_length in number
752 --
753 ,p_v2_val_portions in out nocopy benutils.g_varchar2_table
754 )
755 is
756 --
757 l_v2_val_portions benutils.g_varchar2_table := benutils.g_varchar2_table();
758 --
759 l_v2_value_length pls_integer;
760 l_v2_value_char_count pls_integer;
761 l_string_portion_count pls_integer;
762 l_v2charnum pls_integer;
763 --
764 begin
765 --
766 if p_var2_value is not null then
767 --
768 -- Check the length of the message
769 --
770 l_v2_value_length := length(p_var2_value);
771 --
772 if l_v2_value_length > p_portion_length then
773 --
774 -- Truncate the message text into 80 char portions
775 --
776 l_v2_value_char_count := 0;
777 l_string_portion_count := 0;
778 --
779 loop
780 --
781 -- Check if the varchar2 value character count is greater than
782 -- the message length.
783 --
784 if l_v2_value_char_count > l_v2_value_length then
785 exit;
786 else
787 --
788 if l_v2_value_char_count > 0
789 then
790 --
791 l_v2charnum := l_v2_value_char_count+1;
792 --
793 else
794 --
795 l_v2charnum := l_v2_value_char_count;
796 --
797 end if;
798 --
799 l_v2_val_portions.extend(1);
800 l_v2_val_portions(l_string_portion_count+1) := substr(p_var2_value, l_v2charnum,p_portion_length);
801 --
802 l_string_portion_count := l_string_portion_count + 1;
803 --
804 end if;
805 --
806 l_v2_value_char_count := l_v2_value_char_count + p_portion_length;
807 --
808 end loop;
809 --
810 else
811 --
812 l_v2_val_portions.extend(1);
813 l_v2_val_portions(1) := p_var2_value;
814 --
815 end if;
816 --
817 end if;
818 --
819 p_v2_val_portions := l_v2_val_portions;
820 --
821 end Var2Value_Portion;
822 --
823 procedure NewBftAlertCheck
824 (p_bft_id in number
825 ,p_upperact_cnt in number
826 ,p_ccroraerrreq_cnt in number
827 ,p_ccrhanderrreq_cnt in number
828 ,p_oraerr_tot in number
829 )
830 is
831 --
832 l_alert_reas varchar2(1000);
833 --
834 begin
835 --
836 if p_upperact_cnt > 0
837 then
838 --
839 -- Unprocessed person actions
840 --
841 l_alert_reas := ' UnprocPerAct('||p_upperact_cnt||')';
842 --
843 -- Check for concurrent request errors
844 --
845 if p_ccroraerrreq_cnt > 0
846 then
847 --
848 l_alert_reas := l_alert_reas||':ConcReqErr('||p_ccroraerrreq_cnt||')';
849 --
850 end if;
851 --
852 if p_ccrhanderrreq_cnt > 0
853 then
854 --
855 l_alert_reas := l_alert_reas||':ConcReqEnvErr('||p_ccrhanderrreq_cnt||')';
856 --
857 end if;
858 --
859 if p_oraerr_tot > 0
860 then
861 --
862 l_alert_reas := l_alert_reas||':OraErr('||p_oraerr_tot||')';
863 --
864 end if;
865 --
866 g_alertsev1reas_va.extend(1);
867 g_alertsev1bftid_va.extend(1);
868 g_alertsev1prevbftid_va.extend(1);
869 --
870 g_alertsev1reas_va(g_alertsev1_en) := l_alert_reas;
871 g_alertsev1bftid_va(g_alertsev1_en) := p_bft_id;
872 g_alertsev1prevbftid_va(g_alertsev1_en) := null;
873 g_alertsev1_en := g_alertsev1_en+1;
874 --
875 elsif p_upperact_cnt = 0
876 and p_ccroraerrreq_cnt > 0
877 then
878 --
879 -- Concurrent request errors
880 --
881 l_alert_reas := ' ConcReqErr('||p_ccroraerrreq_cnt||')';
882 --
883 if p_oraerr_tot > 0
884 then
885 --
886 l_alert_reas := l_alert_reas||':OraErr('||p_oraerr_tot||')';
887 --
888 end if;
889 --
890 g_alertsev1reas_va.extend(1);
891 g_alertsev1bftid_va.extend(1);
892 g_alertsev1prevbftid_va.extend(1);
893 --
894 g_alertsev1reas_va(g_alertsev1_en) := l_alert_reas;
895 g_alertsev1bftid_va(g_alertsev1_en) := p_bft_id;
896 g_alertsev1prevbftid_va(g_alertsev1_en) := null;
897 g_alertsev1_en := g_alertsev1_en+1;
898 --
899 elsif p_upperact_cnt = 0
900 and p_ccrhanderrreq_cnt = 0
901 and p_oraerr_tot > 0
902 then
903 --
904 -- Oracle errors no unprocessed person actions
905 --
906 l_alert_reas := p_oraerr_tot||' OraErr';
907 --
908 g_alertsev2reas_va.extend(1);
909 g_alertsev2bftid_va.extend(1);
910 --
911 g_alertsev2reas_va(g_alertsev2_en) := l_alert_reas;
912 g_alertsev2bftid_va(g_alertsev2_en) := p_bft_id;
913 g_alertsev2_en := g_alertsev2_en+1;
914 --
915 end if;
916 --
917 end NewBftAlertCheck;
918 --
919 procedure PerActCompAlertCheck
920 (p_bft_id in number
921 ,p_prevbft_id in number
922 ,p_oldprperact_cnt in number
923 ,p_prperact_cnt in number
924 ,p_olderrperact_cnt in number
925 ,p_errperact_cnt in number
926 ,p_oldupperact_cnt in number
927 ,p_upperact_cnt in number
928 )
929 is
930 --
931 l_alert_reas varchar2(1000);
932 --
933 l_prperact_discrep number;
934 l_errperact_discrep number;
935 l_upperact_discrep number;
936 --
937 begin
938 --
939 -- Check for person action statistic discrepancies
940 --
941 l_prperact_discrep := 0;
942 l_errperact_discrep := 0;
943 l_upperact_discrep := 0;
944 --
945 if p_oldprperact_cnt <> p_prperact_cnt
946 then
947 --
948 l_prperact_discrep := p_oldprperact_cnt-p_prperact_cnt;
949 --
950 elsif p_olderrperact_cnt <> p_errperact_cnt
951 then
952 --
953 l_errperact_discrep := p_olderrperact_cnt-p_errperact_cnt;
954 --
955 elsif p_oldupperact_cnt <> p_upperact_cnt
956 then
957 --
958 l_upperact_discrep := p_oldupperact_cnt-p_upperact_cnt;
959 --
960 end if;
961 --
962 if l_prperact_discrep > 0
963 then
964 --
965 l_alert_reas := ' ProcPerActDisc('||l_prperact_discrep||')';
966 --
967 g_alertsev1reas_va.extend(1);
968 g_alertsev1bftid_va.extend(1);
969 g_alertsev1prevbftid_va.extend(1);
970 --
971 g_alertsev1reas_va(g_alertsev1_en) := l_alert_reas;
972 g_alertsev1bftid_va(g_alertsev1_en) := p_bft_id;
973 g_alertsev1prevbftid_va(g_alertsev1_en) := p_prevbft_id;
974 g_alertsev1_en := g_alertsev1_en+1;
975 --
976 end if;
977 --
978 if l_errperact_discrep > 0
979 then
980 --
981 -- Processed person actions
982 --
983 l_alert_reas := ' ErrPerActDisc('||l_errperact_discrep||')';
984 --
985 g_alertsev1reas_va.extend(1);
986 g_alertsev1bftid_va.extend(1);
987 g_alertsev1prevbftid_va.extend(1);
988 --
989 g_alertsev1reas_va(g_alertsev1_en) := l_alert_reas;
990 g_alertsev1bftid_va(g_alertsev1_en) := p_bft_id;
991 g_alertsev1prevbftid_va(g_alertsev1_en) := p_prevbft_id;
992 g_alertsev1_en := g_alertsev1_en+1;
993 --
994 end if;
995 --
996 if l_upperact_discrep > 0
997 then
998 --
999 l_alert_reas := ' ErrPerActDisc('||l_upperact_discrep||')';
1000 --
1001 g_alertsev1reas_va.extend(1);
1002 g_alertsev1bftid_va.extend(1);
1003 g_alertsev1prevbftid_va.extend(1);
1004 --
1005 g_alertsev1reas_va(g_alertsev1_en) := l_alert_reas;
1006 g_alertsev1bftid_va(g_alertsev1_en) := p_bft_id;
1007 g_alertsev1prevbftid_va(g_alertsev1_en) := p_prevbft_id;
1008 g_alertsev1_en := g_alertsev1_en+1;
1009 --
1010 end if;
1011 --
1012 end PerActCompAlertCheck;
1013 --
1014 procedure BuildAlertStrVa
1015 (p_alertstr_va in out nocopy benutils.g_v2_150_table
1016 )
1017 is
1018 --
1019 l_alertstr_va benutils.g_v2_150_table := benutils.g_v2_150_table();
1020 --
1021 l_alert_reas varchar2(1000);
1022 --
1023 begin
1024 --
1025 if g_alertsev1reas_va.count > 0
1026 then
1027 --
1028 for alertvaen in g_alertsev1reas_va.first..g_alertsev1reas_va.last
1029 loop
1030 --
1031 l_alertstr_va.extend(1);
1032 --
1033 if g_alertsev1prevbftid_va(alertvaen) is not null
1034 then
1035 --
1036 l_alertstr_va(alertvaen) := '-- ALERTSEV1:('||g_alertsev1bftid_va(alertvaen)
1037 ||')('||g_alertsev1prevbftid_va(alertvaen)
1038 ||') '||g_alertsev1reas_va(alertvaen);
1039 --
1040 else
1041 --
1042 l_alertstr_va(alertvaen) := '-- ALERTSEV1:('||g_alertsev1bftid_va(alertvaen)
1043 ||') '||g_alertsev1reas_va(alertvaen);
1044 --
1045 end if;
1046 --
1047 end loop;
1048 --
1049 end if;
1050 --
1051 if g_alertsev2reas_va.count > 0
1052 then
1053 --
1054 for alertvaen in g_alertsev2reas_va.first..g_alertsev2reas_va.last
1055 loop
1056 --
1057 l_alertstr_va.extend(1);
1058 l_alertstr_va(alertvaen) := '-- ALERTSEV2:('||g_alertsev2bftid_va(alertvaen)
1059 ||') '||g_alertsev2reas_va(alertvaen);
1060 --
1061 end loop;
1062 --
1063 end if;
1064 --
1065 p_alertstr_va := l_alertstr_va;
1066 --
1067 end BuildAlertStrVa;
1068 --
1069 PROCEDURE process
1070 (errbuf out nocopy varchar2
1071 ,retcode out nocopy number
1072 ,p_person_id in number default null
1073 ,p_business_group_id in number default null
1074 ,p_days in number default null
1075 ,p_baselines in number default null
1076 ,p_submit_validate in varchar2 default 'Y'
1077 ,p_rollup_rbvs in varchar2 default 'Y'
1078 ,p_refresh_rollups in varchar2 default 'N'
1079 ,p_testcycle_type in varchar2 default null
1080 ,p_mode_cd in varchar2 default null
1081 --
1082 ,p_ler_id in number default null
1083 ,p_pgm_id in number default null
1084 ,p_process_date in date default null
1085 )
1086 IS
1087 --
1088 TYPE cur_type IS REF CURSOR;
1089 --
1090 c_bftinst_cur cur_type;
1091 --
1092 type t_v2_4000_va is varray(10000000) of varchar2(4000);
1093 --
1094 Type MxBFTInstType is record
1095 (business_group_id number
1096 ,person_id number
1097 ,ler_id number
1098 ,process_date date
1099 ,mx_bftid number
1100 );
1101 --
1102 Type MxPILBGPInstType is record
1103 (business_group_id number
1104 ,process_date date
1105 ,cnt number
1106 );
1107 --
1108 Type MxPILLERInstType is record
1109 (business_group_id number
1110 ,ler_id number
1111 ,process_date date
1112 ,cnt number
1113 );
1114 --
1115 Type MxPILPERInstType is record
1116 (business_group_id number
1117 ,person_id number
1118 ,process_date date
1119 ,cnt number
1120 );
1121 --
1122 Type BFTInstType is record
1123 (benefit_action_id number
1124 ,business_group_id number
1125 ,person_id number
1126 ,ler_id number
1127 ,process_date date
1128 );
1129 --
1130 Type BFTDetType is record
1131 (max_bftid number
1132 ,process_date date
1133 ,mode_cd varchar2(100)
1134 ,business_group_id number
1135 ,BENFTS_GRP_ID number
1136 ,person_id number
1137 ,pgm_id number
1138 ,pl_id number
1139 ,ler_id number
1140 ,opt_id number
1141 /*
1142 ,LF_EVT_OCRD_DT date
1143 */
1144 ,max_credt date
1145 ,request_id number
1146 );
1147 --
1148 l_mxbft_inst MxBFTInstType;
1149 l_mxpilbgp_inst MxPILBGPInstType;
1150 l_mxpiller_inst MxPILLERInstType;
1151 l_mxpilper_inst MxPILPERInstType;
1152 l_bft_inst BFTInstType;
1153 l_bft_dets BFTDetType;
1154 --
1155 l_newbft_id_va benutils.g_number_table := benutils.g_number_table();
1156 l_newbftmode_cd_va benutils.g_v2_150_table := benutils.g_v2_150_table();
1157 l_newbftbgp_id_va benutils.g_number_table := benutils.g_number_table();
1158 l_newbftler_id_va benutils.g_number_table := benutils.g_number_table();
1159 l_newbftopt_id_va benutils.g_number_table := benutils.g_number_table();
1160 l_newbftper_id_va benutils.g_number_table := benutils.g_number_table();
1161 l_newbftpl_id_va benutils.g_number_table := benutils.g_number_table();
1162 l_newbftbfg_id_va benutils.g_number_table := benutils.g_number_table();
1163 l_newbftpgm_id_va benutils.g_number_table := benutils.g_number_table();
1164 l_newbftproc_dt_va benutils.g_date_table := benutils.g_date_table();
1165 l_newbftleodt_dt_va benutils.g_date_table := benutils.g_date_table();
1166 l_newbftmax_credt_va benutils.g_date_table := benutils.g_date_table();
1167 l_newbftreqid_va benutils.g_number_table := benutils.g_number_table();
1168 --
1169 l_clcode_va benutils.g_v2_150_table := benutils.g_v2_150_table();
1170 l_clcodecnt_va benutils.g_number_table := benutils.g_number_table();
1171 l_clsumovn_va benutils.g_number_table := benutils.g_number_table();
1172 l_clminesd_va benutils.g_date_table := benutils.g_date_table();
1173 l_clmineed_va benutils.g_date_table := benutils.g_date_table();
1174 l_clidstr_va t_v2_4000_va := t_v2_4000_va();
1175 --
1176 l_rbv_clcode_va benutils.g_v2_150_table := benutils.g_v2_150_table();
1177 l_rbv_clcodecnt_va benutils.g_number_table := benutils.g_number_table();
1178 l_rbv_clsumovn_va benutils.g_number_table := benutils.g_number_table();
1179 l_rbv_clminesd_va benutils.g_date_table := benutils.g_date_table();
1180 l_rbv_clmineed_va benutils.g_date_table := benutils.g_date_table();
1181 l_rbv_clidstr_va t_v2_4000_va := t_v2_4000_va();
1182 --
1183 l_mmclccode_va benutils.g_v2_150_table := benutils.g_v2_150_table();
1184 l_mmoclcount_va benutils.g_v2_150_table := benutils.g_v2_150_table();
1185 l_mmnclcount_va benutils.g_v2_150_table := benutils.g_v2_150_table();
1186 l_mtclccode_va benutils.g_v2_150_table := benutils.g_v2_150_table();
1187 l_mtoclcount_va benutils.g_number_table := benutils.g_number_table();
1188 l_mtnclcount_va benutils.g_number_table := benutils.g_number_table();
1189 --
1190 l_reptext_va benutils.g_v2_150_table := benutils.g_v2_150_table();
1191 l_num1_col benutils.g_number_table := benutils.g_number_table();
1192 l_num2_col benutils.g_number_table := benutils.g_number_table();
1193 l_num3_col benutils.g_number_table := benutils.g_number_table();
1194 l_num4_col benutils.g_number_table := benutils.g_number_table();
1195 l_num5_col benutils.g_number_table := benutils.g_number_table();
1196 l_num6_col benutils.g_number_table := benutils.g_number_table();
1197 l_num7_col benutils.g_number_table := benutils.g_number_table();
1198 l_num8_col benutils.g_number_table := benutils.g_number_table();
1199 l_num9_col benutils.g_number_table := benutils.g_number_table();
1200 l_num10_col benutils.g_number_table := benutils.g_number_table();
1201 l_num11_col benutils.g_number_table := benutils.g_number_table();
1202 l_num12_col benutils.g_number_table := benutils.g_number_table();
1203 l_num13_col benutils.g_number_table := benutils.g_number_table();
1204 l_num14_col benutils.g_number_table := benutils.g_number_table();
1205 l_num15_col benutils.g_number_table := benutils.g_number_table();
1206 l_num16_col benutils.g_number_table := benutils.g_number_table();
1207 l_num17_col benutils.g_number_table := benutils.g_number_table();
1208 l_num18_col benutils.g_number_table := benutils.g_number_table();
1209 l_var1_col benutils.g_varchar2_table := benutils.g_varchar2_table();
1210 l_var2_col benutils.g_varchar2_table := benutils.g_varchar2_table();
1211 l_var3_col benutils.g_varchar2_table := benutils.g_varchar2_table();
1212 l_var4_col benutils.g_varchar2_table := benutils.g_varchar2_table();
1213 l_var5_col benutils.g_varchar2_table := benutils.g_varchar2_table();
1214 l_var6_col benutils.g_varchar2_table := benutils.g_varchar2_table();
1215 l_var7_col benutils.g_varchar2_table := benutils.g_varchar2_table();
1216 l_var8_col benutils.g_varchar2_table := benutils.g_varchar2_table();
1217 l_var9_col benutils.g_varchar2_table := benutils.g_varchar2_table();
1218 l_var10_col benutils.g_varchar2_table := benutils.g_varchar2_table();
1219 l_var11_col benutils.g_varchar2_table := benutils.g_varchar2_table();
1220 l_var12_col benutils.g_varchar2_table := benutils.g_varchar2_table();
1221 --
1222 l_mmperid_va benutils.g_number_table := benutils.g_number_table();
1223 l_mmcombid_va benutils.g_number_table := benutils.g_number_table();
1224 l_mmcombid2_va benutils.g_number_table := benutils.g_number_table();
1225 l_mmcombid3_va benutils.g_number_table := benutils.g_number_table();
1226 l_mmcombid4_va benutils.g_number_table := benutils.g_number_table();
1227 l_mmcnt_va benutils.g_number_table := benutils.g_number_table();
1228 l_mmcombnm_va benutils.g_varchar2_table := benutils.g_varchar2_table();
1229 l_mmcombnm2_va benutils.g_varchar2_table := benutils.g_varchar2_table();
1230 l_mmcombnm3_va benutils.g_varchar2_table := benutils.g_varchar2_table();
1231 l_mmcombnm4_va benutils.g_varchar2_table := benutils.g_varchar2_table();
1232 l_adfname_va benutils.g_varchar2_table := benutils.g_varchar2_table();
1233 l_adffilver_va benutils.g_varchar2_table := benutils.g_varchar2_table();
1234 l_adflud_va benutils.g_date_table := benutils.g_date_table();
1235 --
1236 l_sql_str long;
1237 l_sel_str long;
1238 l_from_str long;
1239 l_grpby_str long;
1240 l_ordby_str long;
1241 l_where_str long;
1242 --
1243 l_st_date date;
1244 l_end_date date;
1245 --
1246 l_PPL_ID number;
1247 l_PPL_OVN number;
1248 l_benmngle_sqlerrm varchar2(1000);
1249 --
1250 l_sqlstr long;
1251 --
1252 l_vaen pls_integer;
1253 --
1254 l_hist_duration pls_integer;
1255 l_show_matches boolean;
1256 --
1257 l_mmrltyp_rbvclcd_va benutils.g_v2_150_table := benutils.g_v2_150_table();
1258 l_mmrltyp_rbvclcd_vaen pls_integer;
1259 --
1260 l_mmperid_rbvclcd_va benutils.g_v2_150_table := benutils.g_v2_150_table();
1261 --
1262 l_tmpmmclccode_va benutils.g_v2_150_table := benutils.g_v2_150_table();
1263 l_bugtype_va benutils.g_v2_150_table := benutils.g_v2_150_table();
1264 l_mmerrmesscd_va benutils.g_v2_150_table := benutils.g_v2_150_table();
1265 l_mmmxperid_va benutils.g_number_table := benutils.g_number_table();
1266 l_mmerrcnt_va benutils.g_number_table := benutils.g_number_table();
1267 l_errmesscd_va benutils.g_v2_150_table := benutils.g_v2_150_table();
1268 l_mxperid_va benutils.g_number_table := benutils.g_number_table();
1269 l_errcnt_va benutils.g_number_table := benutils.g_number_table();
1270 l_oraerrtext_va benutils.g_varchar2_table := benutils.g_varchar2_table();
1271 l_oraerrtextinst_va benutils.g_varchar2_table := benutils.g_varchar2_table();
1272 l_oraerrmxperid_va benutils.g_number_table := benutils.g_number_table();
1273 l_oraerrcnt_va benutils.g_number_table := benutils.g_number_table();
1274 l_bugpscode_va benutils.g_v2_150_table := benutils.g_v2_150_table();
1275 l_bugnum_va benutils.g_number_table := benutils.g_number_table();
1276 l_buglud_va benutils.g_date_table := benutils.g_date_table();
1277 l_tmpmmerrmesscd_va benutils.g_v2_150_table := benutils.g_v2_150_table();
1278 l_tmpmmmxperid_va benutils.g_number_table := benutils.g_number_table();
1279 l_tmpmmerrcnt_va benutils.g_number_table := benutils.g_number_table();
1280 --
1281 l_apperrtypdiscr_messcd_va benutils.g_v2_150_table := benutils.g_v2_150_table();
1282 l_apperrtypdiscr_cnt_va benutils.g_number_table := benutils.g_number_table();
1283 l_perid_va1 benutils.g_number_table := benutils.g_number_table();
1284 l_perid_va2 benutils.g_number_table := benutils.g_number_table();
1285 l_periddiscrep_perid_va benutils.g_number_table := benutils.g_number_table();
1286 l_periddiscrep_errcd_va benutils.g_v2_150_table := benutils.g_v2_150_table();
1287 l_mmperlud_va benutils.g_date_table := benutils.g_date_table();
1288 l_mmexclperid_va benutils.g_number_table := benutils.g_number_table();
1289 l_newapperr_peridva benutils.g_number_table := benutils.g_number_table();
1290 l_mmexclperid_nwva benutils.g_number_table := benutils.g_number_table();
1291 l_mmexclperid_leva benutils.g_number_table := benutils.g_number_table();
1292 --
1293 l_text_va benutils.g_varchar2_table := benutils.g_varchar2_table();
1294 l_ccrerrcomptxt_va benutils.g_varchar2_table := benutils.g_varchar2_table();
1295 l_ccrhanderrcomptxt_va benutils.g_varchar2_table := benutils.g_varchar2_table();
1296 --
1297 l_alertstr_va benutils.g_v2_150_table := benutils.g_v2_150_table();
1298 --
1299 l_mm_elenum pls_integer;
1300 l_mt_elenum pls_integer;
1301 --
1302 l_mxbft_lud date;
1303 l_thnbft_id number;
1304 l_thnbft_ovn number;
1305 l_reptext_en pls_integer;
1306 --
1307 l_bgp_name varchar2(1000);
1308 l_per_fullname varchar2(1000);
1309 l_rollup_desc varchar2(1000);
1310 --
1311 l_days pls_integer;
1312 l_pgm_name varchar2(1000);
1313 l_ler_name varchar2(1000);
1314 l_opt_name varchar2(1000);
1315 --
1316 l_rbvcode_match boolean;
1317 --
1318 l_pln_name varchar2(1000);
1319 --
1320 l_bugvaen pls_integer;
1321 l_tmpvaen pls_integer;
1322 --
1323 l_apperr_cnt pls_integer;
1324 l_pacterr_cnt pls_integer;
1325 l_pactup_cnt pls_integer;
1326 l_pactproc_cnt pls_integer;
1327 l_oraerr_en pls_integer;
1328 l_tmpmmerrmesscd_en pls_integer;
1329 l_errfound boolean;
1330 l_mmapperr_tot pls_integer;
1331 l_apperr_tot pls_integer;
1332 l_apperrtyp_discr_en pls_integer;
1333 l_check_match boolean;
1334 l_periddiscrep_en pls_integer;
1335 --
1336 l_baselines number;
1337 l_baseline_cnt number;
1338 l_discrepancy boolean;
1339 --
1340 l_mmcombid_cnt pls_integer;
1341 l_mmcombid2_cnt pls_integer;
1342 l_mmperlud_cnt pls_integer;
1343 --
1344 l_reptext varchar2(150);
1345 --
1346 l_rollref_cnt pls_integer;
1347 l_env_errors boolean;
1348 --
1349 l_mmapperrfilt_cnt pls_integer;
1350 l_mmledatafilt_cnt pls_integer;
1351 --
1352 l_oraerr_text long;
1353 l_prevbft_id number;
1354 l_v2esecs varchar2(150);
1355 l_esecs number;
1356 l_ehrs number;
1357 l_ehrmins number;
1358 l_prperact_cnt number;
1359 l_errperact_cnt number;
1360 l_upperact_cnt number;
1361 l_tphr number;
1362 l_bbrthread_cnt number;
1363 l_thread_mxdursecs number;
1364 l_thread_mndursecs number;
1365 l_thread_mxchkcnt number;
1366 l_thread_mnchkcnt number;
1367 --
1368 l_ccrreq_cnt number;
1369 l_ccrcompreq_cnt number;
1370 l_ccrrunreq_cnt number;
1371 l_ccrerrreq_cnt number;
1372 l_ccrscdtreq_cnt number;
1373 l_ccrscddreq_cnt number;
1374 l_ccrscdgreq_cnt number;
1375 l_ccrscdxreq_cnt number;
1376 l_ccrscdireq_cnt number;
1377 l_ccrscdqreq_cnt number;
1378 --
1379 l_ccroraerrreq_cnt number;
1380 l_ccrhanderrreq_cnt number;
1381 l_ccrapperrreq_cnt number;
1382 --
1383 l_alertsev1_en pls_integer;
1384 l_alertsev2_en pls_integer;
1385 l_alert_reas varchar2(1000);
1386 l_oraerr_tot pls_integer;
1387 --
1388 l_oldprperact_cnt pls_integer;
1389 l_olderrperact_cnt pls_integer;
1390 l_oldupperact_cnt pls_integer;
1391 --
1392 cursor c_bftdets
1393 (c_bft_id number
1394 )
1395 is
1396 select bft.last_update_date
1397 from ben_benefit_actions bft
1398 where bft.benefit_action_id = c_bft_id;
1399 --
1400 l_bftdets c_bftdets%rowtype;
1401 --
1402 cursor c_maxpildets
1403 (c_per_id number
1404 ,c_ler_id number
1405 )
1406 is
1407 select pil.ler_id,
1408 pil.LF_EVT_OCRD_DT
1409 from ben_per_in_ler pil
1410 where pil.per_in_ler_id =
1411 (select max(pil1.per_in_ler_id)
1412 from ben_per_in_ler pil1
1413 where pil1.person_id = pil.person_id
1414 and pil1.PER_IN_LER_STAT_CD not in ('VOIDD','BCKDT')
1415 )
1416 and pil.person_id = c_per_id
1417 and pil.ler_id = c_ler_id;
1418 --
1419 l_maxpildets c_maxpildets%rowtype;
1420 --
1421 cursor c_rbvdets
1422 (c_bft_id in number
1423 )
1424 is
1425 select rbv.rollup_code,
1426 rbv.rollup_count,
1427 rbv.rollup_sumovn,
1428 rbv.rollup_minesd,
1429 rbv.rollup_mineed,
1430 rbv.rollup_id_string
1431 from ben_rollup_rbv_summary rbv,
1432 ben_batch_actions pba
1433 where pba.batch_id = c_bft_id
1434 and rbv.batch_action_id = pba.batch_action_id
1435 and rbv.rollup_count > 0
1436 order by rbv.rollup_id;
1437 --
1438 cursor c_ghistbftdets
1439 (c_duration in number
1440 ,c_mode_cd in varchar2
1441 ,c_process_date in date
1442 ,c_bgp_id in number
1443 ,c_ler_id in number
1444 ,c_per_id in number
1445 ,c_pgm_id in number
1446 ,c_pl_id in number
1447 ,c_opt_id in number
1448 ,c_bfg_id in number
1449 ,c_bft_credt in date
1450 )
1451 is
1452 select bft.benefit_action_id,
1453 bft.mode_cd,
1454 bft.process_date,
1455 bft.last_update_date,
1456 bft.business_group_id,
1457 bft.BENFTS_GRP_ID,
1458 bft.person_id,
1459 bft.pgm_id,
1460 bft.pl_id,
1461 bft.COMP_SELECTION_RL,
1462 bft.AUDIT_LOG_FLAG,
1463 bft.VALIDATE_FLAG,
1464 bft.LF_EVT_OCRD_DT
1465 from ben_benefit_actions bft
1466 where bft.last_update_date > c_bft_credt-c_duration
1467 and bft.last_update_date < c_bft_credt
1468 and bft.mode_cd = c_mode_cd
1469 and bft.process_date = c_process_date
1470 and bft.business_group_id = c_bgp_id
1471 and nvl(bft.ler_id,-1) = nvl(c_ler_id,-1)
1472 and nvl(bft.person_id,-1) = nvl(c_per_id,-1)
1473 and nvl(bft.pgm_id,-1) = nvl(c_pgm_id,-1)
1474 and nvl(bft.pl_id,-1) = nvl(c_pl_id,-1)
1475 and nvl(bft.opt_id,-1) = nvl(c_opt_id,-1)
1476 and nvl(bft.BENFTS_GRP_ID,-1) = nvl(c_bfg_id,-1)
1477 and bft.VALIDATE_FLAG = 'B'
1478 and exists
1479 (select 1
1480 from ben_batch_actions pba
1481 where pba.batch_id = bft.benefit_action_id
1482 )
1483 order by bft.benefit_action_id desc;
1484 --
1485 cursor c_bgp_dets
1486 (c_bgp_id number
1487 )
1488 is
1489 select bgp.name
1490 from per_business_groups bgp
1491 where bgp.business_group_id = c_bgp_id;
1492 --
1493 cursor c_perdets
1494 (c_per_id number
1495 )
1496 is
1497 select per.full_name
1498 from per_all_people_f per
1499 where per.person_id = c_per_id;
1500 --
1501 cursor c_pgmdets
1502 (c_pgm_id number
1503 )
1504 is
1505 select pgm.name
1506 from ben_pgm_f pgm
1507 where pgm.pgm_id = c_pgm_id;
1508 --
1509 cursor c_plndets
1510 (c_pl_id number
1511 )
1512 is
1513 select pln.name
1514 from ben_pl_f pln
1515 where pln.pl_id = c_pl_id;
1516 --
1517 cursor c_lerdets
1518 (c_ler_id number
1519 )
1520 is
1521 select ler.name
1522 from ben_ler_f ler
1523 where ler.ler_id = c_ler_id;
1524 --
1525 cursor c_optdets
1526 (c_opt_id number
1527 )
1528 is
1529 select opt.name
1530 from ben_opt_f opt
1531 where opt.opt_id = c_opt_id;
1532 --
1533 cursor c_gbenreporaerrinst
1534 (c_bft_id in number
1535 )
1536 is
1537 select text
1538 from ben_reporting
1539 where benefit_action_id = c_bft_id
1540 and text like '%ORA-%'
1541 order by THREAD_ID, REPORTING_ID;
1542 --
1543 cursor c_adfiles
1544 (c_basebft_id number
1545 ,c_bft_id number
1546 )
1547 is
1548 select adf.FILENAME,
1549 adv.VERSION,
1550 adv.last_update_date
1551 from ad_files adf,
1552 AD_FILE_VERSIONS adv
1553 where adf.file_id = adv.file_id
1554 and adf.LAST_UPDATE_DATE >
1555 (select bft.last_update_date
1556 from ben_benefit_actions bft
1557 where bft.benefit_action_id = c_basebft_id)
1558 and adf.LAST_UPDATE_DATE <
1559 (select bft.last_update_date
1560 from ben_benefit_actions bft
1561 where bft.benefit_action_id = c_bft_id)
1562 and adf.APP_SHORT_NAME = 'BEN'
1563 and adf.SUBDIR = 'patch/115/sql'
1564 order by adv.last_update_date desc;
1565 --
1566 /*
1567 cursor c_prodbugs
1568 (c_basebft_id number
1569 ,c_bft_id number
1570 )
1571 is
1572 select adb.APPLICATION_SHORT_NAME,
1573 adb.BUG_NUMBER,
1574 adb.last_update_date
1575 from ad_bugs adb
1576 where adb.LAST_UPDATE_DATE >
1577 (select bft.last_update_date
1578 from ben_benefit_actions bft
1579 where bft.benefit_action_id = c_basebft_id)
1580 and adb.LAST_UPDATE_DATE <
1581 (select bft.last_update_date
1582 from ben_benefit_actions bft
1583 where bft.benefit_action_id = c_bft_id)
1584 and adb.APPLICATION_SHORT_NAME = 'BEN'
1585 order by adb.last_update_date desc;
1586 --
1587 */
1588 cursor c_bfterrcdperids
1589 (c_bft_id number
1590 ,c_err_cd varchar2
1591 )
1592 is
1593 select rep.person_id
1594 from ben_reporting rep
1595 where rep.benefit_action_id = c_bft_id
1596 and rep.ERROR_MESSAGE_CODE = c_err_cd;
1597 --
1598 cursor c_bpidets
1599 (c_bft_id number
1600 )
1601 is
1602 select bpi.ELPSD_TM
1603 from BEN_BATCH_PROC_INFO bpi
1604 where bpi.benefit_action_id = c_bft_id;
1605 --
1606 procedure PersonActionStats
1607 (p_bft_id number
1608 --
1609 ,p_prperact_cnt in out nocopy number
1610 ,p_errperact_cnt in out nocopy number
1611 ,p_upperact_cnt in out nocopy number
1612 )
1613 is
1614 --
1615 l_pr_cnt number;
1616 l_err_cnt number;
1617 l_up_cnt number;
1618 --
1619 cursor c_prperact
1620 (c_bft_id number
1621 )
1622 is
1623 select count(*)
1624 from ben_person_actions
1625 where benefit_action_id = c_bft_id
1626 and action_status_cd = 'P';
1627 --
1628 cursor c_errperact
1629 (c_bft_id number
1630 )
1631 is
1632 select count(*)
1633 from ben_person_actions
1634 where benefit_action_id = c_bft_id
1635 and action_status_cd = 'E';
1636 --
1637 cursor c_upperact
1638 (c_bft_id number
1639 )
1640 is
1641 select count(*)
1642 from ben_person_actions
1643 where benefit_action_id = c_bft_id
1644 and action_status_cd = 'U';
1645 --
1646 begin
1647 --
1648 l_pr_cnt := 0;
1649 l_err_cnt := 0;
1650 l_up_cnt := 0;
1651 --
1652 open c_prperact
1653 (c_bft_id => p_bft_id
1654 );
1655 fetch c_prperact into l_pr_cnt;
1656 close c_prperact;
1657 --
1658 open c_errperact
1659 (c_bft_id => p_bft_id
1660 );
1661 fetch c_errperact into l_err_cnt;
1662 close c_errperact;
1663 --
1664 open c_upperact
1665 (c_bft_id => p_bft_id
1666 );
1667 fetch c_upperact into l_up_cnt;
1668 close c_upperact;
1669 --
1670 p_prperact_cnt := l_pr_cnt;
1671 p_errperact_cnt := l_err_cnt;
1672 p_upperact_cnt := l_up_cnt;
1673 --
1674 end PersonActionStats;
1675 --
1676 procedure BBRThreadStats
1677 (p_bft_id in number
1678 --
1679 ,p_thread_cnt in out nocopy number
1680 ,p_mxdursecs in out nocopy number
1681 ,p_mndursecs in out nocopy number
1682 ,p_mxchkcnt in out nocopy number
1683 ,p_mnchkcnt in out nocopy number
1684 )
1685 is
1686 --
1687 l_lul_va benutils.g_number_table := benutils.g_number_table();
1688 l_bftcredt_va benutils.g_date_table := benutils.g_date_table();
1689 l_lud_va benutils.g_date_table := benutils.g_date_table();
1690 l_cnt_va benutils.g_number_table := benutils.g_number_table();
1691 l_lultotsecs_va benutils.g_number_table := benutils.g_number_table();
1692 l_lulavgsecs_va benutils.g_number_table := benutils.g_number_table();
1693 --
1694 l_thread_cnt number;
1695 l_mx_en pls_integer;
1696 l_mn_en pls_integer;
1697 l_mxdursecs pls_integer;
1698 l_mndursecs pls_integer;
1699 l_mxchkcnt pls_integer;
1700 l_mnchkcnt pls_integer;
1701 --
1702 cursor c_gthreads
1703 (c_bft_id in number
1704 )
1705 is
1706 select bbr.last_update_login,
1707 bft.creation_date,
1708 max(bbr.last_update_date) max_lud,
1709 count(*) cnt,
1710 round((max(bbr.last_update_date)-bft.creation_date)*(24*3600),2) tot_secs,
1711 round(((max(bbr.last_update_date)-bft.creation_date)*(24*3600))/count(*),2) avg_secs
1712 from ben_batch_ranges bbr,
1713 ben_benefit_actions bft
1714 where bbr.benefit_action_id = c_bft_id
1715 and bbr.benefit_action_id = bft.benefit_action_id
1716 group by bbr.last_update_login,
1717 bft.creation_date
1718 order by count(*) desc;
1719 --
1720 begin
1721 --
1722 open c_gthreads
1723 (c_bft_id => p_bft_id
1724 );
1725 fetch c_gthreads BULK COLLECT INTO l_lul_va,
1726 l_bftcredt_va,
1727 l_lud_va,
1728 l_cnt_va,
1729 l_lultotsecs_va,
1730 l_lulavgsecs_va;
1731 close c_gthreads;
1732 --
1733 l_thread_cnt := l_lul_va.count;
1734 --
1735 if l_lultotsecs_va.count > 0
1736 then
1737 --
1738 l_mx_en := l_lultotsecs_va.first;
1739 l_mn_en := l_lultotsecs_va.last;
1740 --
1741 l_mxdursecs := l_lultotsecs_va(l_mx_en);
1742 l_mndursecs := l_lultotsecs_va(l_mn_en);
1743 l_mxchkcnt := l_cnt_va(l_mx_en);
1744 l_mnchkcnt := l_cnt_va(l_mn_en);
1745 --
1746 else
1747 --
1748 l_mxdursecs := 0;
1749 l_mndursecs := 0;
1750 l_mxchkcnt := 0;
1751 l_mnchkcnt := 0;
1752 --
1753 end if;
1754 --
1755 p_thread_cnt := l_thread_cnt;
1756 p_mxdursecs := l_mxdursecs;
1757 p_mndursecs := l_mndursecs;
1758 p_mxchkcnt := l_mxchkcnt;
1759 p_mnchkcnt := l_mnchkcnt;
1760 --
1761 end BBRThreadStats;
1762 --
1763 procedure CCRequestStats
1764 (p_bft_id in number
1765 --
1766 ,p_ccrreq_cnt in out nocopy number
1767 ,p_ccrcompreq_cnt in out nocopy number
1768 ,p_ccrrunreq_cnt in out nocopy number
1769 ,p_ccrerrreq_cnt in out nocopy number
1770 ,p_ccrscdtreq_cnt in out nocopy number
1771 ,p_ccrscddreq_cnt in out nocopy number
1772 ,p_ccrscdgreq_cnt in out nocopy number
1773 ,p_ccrscdxreq_cnt in out nocopy number
1774 ,p_ccrscdireq_cnt in out nocopy number
1775 ,p_ccrscdqreq_cnt in out nocopy number
1776 ,p_ccroraerrreq_cnt in out nocopy number
1777 ,p_ccrhanderrreq_cnt in out nocopy number
1778 ,p_ccrapperrreq_cnt in out nocopy number
1779 ,p_ccrerrcomptxt_va in out nocopy benutils.g_varchar2_table
1780 ,p_ccrhanderrcomptxt_va in out nocopy benutils.g_varchar2_table
1781 )
1782 is
1783 --
1784 l_reqid_va benutils.g_number_table := benutils.g_number_table();
1785 l_ospid_va benutils.g_number_table := benutils.g_number_table();
1786 l_ossid_va benutils.g_number_table := benutils.g_number_table();
1787 l_parreqid_va benutils.g_number_table := benutils.g_number_table();
1788 l_phcode_va benutils.g_varchar2_table := benutils.g_varchar2_table();
1789 l_stcode_va benutils.g_varchar2_table := benutils.g_varchar2_table();
1790 l_comptxt_va benutils.g_varchar2_table := benutils.g_varchar2_table();
1791 l_reqchkcnt_va benutils.g_number_table := benutils.g_number_table();
1792 --
1793 l_errcomptxt_va benutils.g_varchar2_table := benutils.g_varchar2_table();
1794 l_handerrcomptxt_va benutils.g_varchar2_table := benutils.g_varchar2_table();
1795 --
1796 l_req_cnt number;
1797 l_compreq_cnt number;
1798 l_runreq_cnt number;
1799 l_errreq_cnt number;
1800 l_scdtreq_cnt number;
1801 l_scddreq_cnt number;
1802 l_scdgreq_cnt number;
1803 l_scdxreq_cnt number;
1804 l_scdireq_cnt number;
1805 l_scdqreq_cnt number;
1806 --
1807 l_oraerrreq_cnt number;
1808 l_handerrreq_cnt number;
1809 l_apperrreq_cnt number;
1810 --
1811 l_errcomp_en pls_integer;
1812 l_handerrcomp_en pls_integer;
1813 --
1814 cursor c_fndreqstats
1815 (c_bft_id number
1816 )
1817 is
1818 select ccr.request_id,
1819 ccr.ORACLE_PROCESS_ID,
1820 ccr.ORACLE_SESSION_ID,
1821 ccr.PARENT_REQUEST_ID,
1822 ccr.phase_code,
1823 ccr.status_code,
1824 substr(replace(replace(ccr.completion_text,fnd_global.local_chr(10),' ')
1825 ,fnd_global.local_chr(13),' '),1,1000),
1826 count(*)
1827 from fnd_concurrent_requests ccr,
1828 ben_benefit_actions bft,
1829 ben_batch_ranges bbr
1830 where ccr.parent_request_id = bft.request_id
1831 and bbr.last_update_login = ccr.last_update_login
1832 and bft.benefit_action_id = bbr.benefit_action_id
1833 and bft.benefit_action_id = c_bft_id
1834 group by ccr.request_id,
1835 ccr.ORACLE_PROCESS_ID,
1836 ccr.ORACLE_SESSION_ID,
1837 ccr.PARENT_REQUEST_ID,
1838 ccr.phase_code,
1839 ccr.status_code,
1840 substr(replace(replace(ccr.completion_text,fnd_global.local_chr(10),' ')
1841 ,fnd_global.local_chr(13),' '),1,1000);
1842 --
1843 cursor c_fndparreqstats
1844 (c_bft_id number
1845 )
1846 is
1847 select ccr.request_id,
1848 ccr.ORACLE_PROCESS_ID,
1849 ccr.ORACLE_SESSION_ID,
1850 ccr.PARENT_REQUEST_ID,
1851 ccr.phase_code,
1852 ccr.status_code,
1853 substr(replace(replace(ccr.completion_text,fnd_global.local_chr(10),' ')
1854 ,fnd_global.local_chr(13),' '),1,1000),
1855 count(*)
1856 from fnd_concurrent_requests ccr,
1857 ben_benefit_actions bft
1858 where ccr.request_id = bft.request_id
1859 and bft.benefit_action_id = c_bft_id
1860 group by ccr.request_id,
1861 ccr.ORACLE_PROCESS_ID,
1862 ccr.ORACLE_SESSION_ID,
1863 ccr.PARENT_REQUEST_ID,
1864 ccr.phase_code,
1865 ccr.status_code,
1866 substr(replace(replace(ccr.completion_text,fnd_global.local_chr(10),' ')
1867 ,fnd_global.local_chr(13),' '),1,1000);
1868 --
1869 begin
1870 --
1871 open c_fndreqstats
1872 (c_bft_id => p_bft_id
1873 );
1874 fetch c_fndreqstats BULK COLLECT INTO l_reqid_va,
1875 l_ospid_va,
1876 l_ossid_va,
1877 l_parreqid_va,
1878 l_phcode_va,
1879 l_stcode_va,
1880 l_comptxt_va,
1881 l_reqchkcnt_va;
1882 close c_fndreqstats;
1883 --
1884 l_compreq_cnt := 0;
1885 l_runreq_cnt := 0;
1886 l_errreq_cnt := 0;
1887 l_scdtreq_cnt := 0;
1888 l_scddreq_cnt := 0;
1889 l_scdgreq_cnt := 0;
1890 l_scdxreq_cnt := 0;
1891 l_scdireq_cnt := 0;
1892 l_scdqreq_cnt := 0;
1893 --
1894 l_oraerrreq_cnt := 0;
1895 l_handerrreq_cnt := 0;
1896 l_apperrreq_cnt := 0;
1897 --
1898 l_errcomp_en := 1;
1899 l_handerrcomp_en := 1;
1900 --
1901 if l_reqid_va.count > 0
1902 then
1903 --
1904 for reqvaen in l_reqid_va.first..l_reqid_va.last
1905 loop
1906 --
1907 if l_stcode_va(reqvaen) = 'C'
1908 then
1909 --
1910 l_compreq_cnt := l_compreq_cnt+1;
1911 --
1912 elsif l_stcode_va(reqvaen) = 'R'
1913 then
1914 --
1915 l_runreq_cnt := l_runreq_cnt+1;
1916 --
1917 elsif l_stcode_va(reqvaen) = 'E'
1918 then
1919 --
1920 l_errreq_cnt := l_errreq_cnt+1;
1921 --
1922 if instr(l_comptxt_va(reqvaen),'ORA-20001') = 0
1923 then
1924 --
1925 l_errcomptxt_va.extend(1);
1926 l_errcomptxt_va(l_errcomp_en) := l_comptxt_va(reqvaen);
1927 l_errcomp_en := l_errcomp_en+1;
1928 --
1929 l_oraerrreq_cnt := l_oraerrreq_cnt+1;
1930 --
1931 --
1932 -- Check for logging error
1933 --
1934 elsif instr(l_comptxt_va(reqvaen),'_91663_') > 0
1935 then
1936 --
1937 l_handerrcomptxt_va.extend(1);
1938 l_handerrcomptxt_va(l_handerrcomp_en) := l_comptxt_va(reqvaen);
1939 l_handerrcomp_en := l_handerrcomp_en+1;
1940 --
1941 l_handerrreq_cnt := l_handerrreq_cnt+1;
1942 --
1943 else
1944 --
1945 l_apperrreq_cnt := l_apperrreq_cnt+1;
1946 --
1947 end if;
1948 --
1949 elsif l_stcode_va(reqvaen) = 'T'
1950 then
1951 --
1952 l_scdtreq_cnt := l_scdtreq_cnt+1;
1953 --
1954 elsif l_stcode_va(reqvaen) = 'G'
1955 then
1956 --
1957 l_scdgreq_cnt := l_scdgreq_cnt+1;
1958 --
1959 elsif l_stcode_va(reqvaen) = 'X'
1960 then
1961 --
1962 l_scdxreq_cnt := l_scdxreq_cnt+1;
1963 --
1964 elsif l_stcode_va(reqvaen) = 'I'
1965 then
1966 --
1967 l_scdireq_cnt := l_scdireq_cnt+1;
1968 --
1969 elsif l_stcode_va(reqvaen) = 'Q'
1970 then
1971 --
1972 l_scdqreq_cnt := l_scdqreq_cnt+1;
1973 --
1974 end if;
1975 --
1976 end loop;
1977 --
1978 end if;
1979 --
1980 l_req_cnt := l_reqid_va.count;
1981 --
1982 open c_fndparreqstats
1983 (c_bft_id => p_bft_id
1984 );
1985 fetch c_fndparreqstats BULK COLLECT INTO l_reqid_va,
1986 l_ospid_va,
1987 l_ossid_va,
1988 l_parreqid_va,
1989 l_phcode_va,
1990 l_stcode_va,
1991 l_comptxt_va,
1992 l_reqchkcnt_va;
1993 close c_fndparreqstats;
1994 --
1995 if l_reqid_va.count > 0
1996 then
1997 --
1998 for reqvaen in l_reqid_va.first..l_reqid_va.last
1999 loop
2000 --
2001 if l_stcode_va(reqvaen) = 'C'
2002 then
2003 --
2004 l_compreq_cnt := l_compreq_cnt+1;
2005 --
2006 elsif l_stcode_va(reqvaen) = 'R'
2007 then
2008 --
2009 l_runreq_cnt := l_runreq_cnt+1;
2010 --
2011 elsif l_stcode_va(reqvaen) = 'E'
2012 then
2013 --
2014 l_errreq_cnt := l_errreq_cnt+1;
2015 --
2016 if instr(l_comptxt_va(reqvaen),'ORA-20001') = 0
2017 then
2018 --
2019 l_errcomptxt_va.extend(1);
2020 l_errcomptxt_va(l_errcomp_en) := l_comptxt_va(reqvaen);
2021 l_errcomp_en := l_errcomp_en+1;
2022 --
2023 l_oraerrreq_cnt := l_oraerrreq_cnt+1;
2024 --
2025 --
2026 -- Check for logging error
2027 --
2028 elsif instr(l_comptxt_va(reqvaen),'_91663_') > 0
2029 then
2030 --
2031 l_handerrcomptxt_va.extend(1);
2032 l_handerrcomptxt_va(l_handerrcomp_en) := l_comptxt_va(reqvaen);
2033 l_handerrcomp_en := l_handerrcomp_en+1;
2034 --
2035 l_handerrreq_cnt := l_handerrreq_cnt+1;
2036 --
2037 else
2038 --
2039 l_apperrreq_cnt := l_apperrreq_cnt+1;
2040 --
2041 end if;
2042 --
2043 elsif l_stcode_va(reqvaen) = 'T'
2044 then
2045 --
2046 l_scdtreq_cnt := l_scdtreq_cnt+1;
2047 --
2048 elsif l_stcode_va(reqvaen) = 'G'
2049 then
2050 --
2051 l_scdgreq_cnt := l_scdgreq_cnt+1;
2052 --
2053 elsif l_stcode_va(reqvaen) = 'X'
2054 then
2055 --
2056 l_scdxreq_cnt := l_scdxreq_cnt+1;
2057 --
2058 elsif l_stcode_va(reqvaen) = 'I'
2059 then
2060 --
2061 l_scdireq_cnt := l_scdireq_cnt+1;
2062 --
2063 elsif l_stcode_va(reqvaen) = 'Q'
2064 then
2065 --
2066 l_scdqreq_cnt := l_scdqreq_cnt+1;
2067 --
2068 end if;
2069 --
2070 end loop;
2071 --
2072 end if;
2073 --
2074 l_req_cnt := l_req_cnt+l_reqid_va.count;
2075 --
2076 p_ccrreq_cnt := l_req_cnt;
2077 p_ccrcompreq_cnt := l_compreq_cnt;
2078 p_ccrrunreq_cnt := l_runreq_cnt;
2079 p_ccrerrreq_cnt := l_errreq_cnt;
2080 p_ccrscdtreq_cnt := l_scdtreq_cnt;
2081 p_ccrscddreq_cnt := l_scddreq_cnt;
2082 p_ccrscdgreq_cnt := l_scdgreq_cnt;
2083 p_ccrscdxreq_cnt := l_scdxreq_cnt;
2084 p_ccrscdireq_cnt := l_scdireq_cnt;
2085 p_ccrscdqreq_cnt := l_scdqreq_cnt;
2086 --
2087 p_ccroraerrreq_cnt := l_oraerrreq_cnt;
2088 p_ccrhanderrreq_cnt := l_handerrreq_cnt;
2089 p_ccrapperrreq_cnt := l_apperrreq_cnt;
2090 --
2091 p_ccrerrcomptxt_va := l_errcomptxt_va;
2092 p_ccrhanderrcomptxt_va := l_handerrcomptxt_va;
2093 --
2094 end CCRequestStats;
2095 --
2096 procedure OracleErrs
2097 (p_bft_id in number
2098 --
2099 ,p_oraerr_tot in out nocopy number
2100 ,p_oraerrtext_va in out nocopy benutils.g_varchar2_table
2101 ,p_oraerrcnt_va in out nocopy benutils.g_number_table
2102 ,p_oraerrmxperid_va in out nocopy benutils.g_number_table
2103 ,p_oraerrtextinst_va in out nocopy benutils.g_varchar2_table
2104 )
2105 is
2106 --
2107 l_text_va benutils.g_varchar2_table := benutils.g_varchar2_table();
2108 l_textinst_va benutils.g_varchar2_table := benutils.g_varchar2_table();
2109 l_mxperid_va benutils.g_number_table := benutils.g_number_table();
2110 l_cnt_va benutils.g_number_table := benutils.g_number_table();
2111 --
2112 l_err_en pls_integer;
2113 l_err_tot pls_integer;
2114 --
2115 cursor c_gbenreporaerrsum
2116 (c_bft_id in number
2117 )
2118 is
2119 select text,
2120 max(person_id) mx_perid,
2121 count(*) cnt
2122 from ben_reporting
2123 where benefit_action_id = c_bft_id
2124 and text like '%ORA-%'
2125 group by text
2126 order by count(*) desc;
2127 --
2128 begin
2129 --
2130 -- Get oracle error information
2131 --
2132 l_err_en := 1;
2133 l_err_tot := 0;
2134 --
2135 for row in c_gbenreporaerrsum
2136 (c_bft_id => p_bft_id
2137 )
2138 loop
2139 --
2140 l_text_va.extend(1);
2141 l_mxperid_va.extend(1);
2142 l_errcnt_va.extend(1);
2143 l_text_va(l_err_en) := row.text;
2144 l_mxperid_va(l_err_en) := row.mx_perid;
2145 l_errcnt_va(l_err_en) := row.cnt;
2146 --
2147 l_err_tot := l_err_tot+row.cnt;
2148 l_err_en := l_err_en+1;
2149 --
2150 end loop;
2151 --
2152 if l_text_va.count > 0
2153 then
2154 --
2155 open c_gbenreporaerrinst
2156 (c_bft_id => p_bft_id
2157 );
2158 fetch c_gbenreporaerrinst BULK COLLECT INTO l_textinst_va;
2159 close c_gbenreporaerrinst;
2160 --
2161 end if;
2162 --
2163 p_oraerr_tot := l_err_tot;
2164 p_oraerrtext_va := l_text_va;
2165 p_oraerrcnt_va := l_errcnt_va;
2166 p_oraerrmxperid_va := l_mxperid_va;
2167 p_oraerrtextinst_va := l_textinst_va;
2168 --
2169 end OracleErrs;
2170 --
2171 BEGIN
2172 --
2173 -- Defaults
2174 --
2175 if p_days is null
2176 then
2177 --
2178 l_days := 1000;
2179 --
2180 else
2181 --
2182 l_days := p_days;
2183 --
2184 end if;
2185 --
2186 if p_baselines is null
2187 then
2188 --
2189 l_baselines := 5;
2190 --
2191 else
2192 --
2193 l_baselines := p_baselines;
2194 --
2195 end if;
2196 --
2197 -- Sweep up all non rolled up benefit actions
2198 --
2199 l_st_date := sysdate-l_days;
2200 l_end_date := sysdate;
2201 --
2202 l_reptext_va.delete;
2203 --
2204 l_reptext_va.extend(1);
2205 l_reptext_en := 1;
2206 l_reptext_va(l_reptext_en) := '-- ';
2207 --
2208 l_reptext_va.extend(1);
2209 l_reptext_en := l_reptext_en+1;
2210 l_reptext_va(l_reptext_en) := '-- Reporting days: '||p_days;
2211 --
2212 l_reptext_va.extend(1);
2213 l_reptext_en := l_reptext_en+1;
2214 l_reptext_va(l_reptext_en) := '-- Reporting date range: '||l_st_date||' to '||l_end_date;
2215 --
2216 if p_rollup_rbvs = 'Y'
2217 then
2218 --
2219 l_hist_duration := 1000;
2220 l_show_matches := FALSE;
2221 --
2222 -- Rollup non rolled up benefit actions
2223 --
2224 l_where_str := ' where bft.validate_flag in ('||''''||'C'||''''
2225 ||','||''''||'B'||''''||') '
2226 ||' and bft.last_update_date '
2227 ||' between :st_date and :end_date ';
2228 --
2229 if p_person_id is not null
2230 then
2231 --
2232 l_where_str := l_where_str||' and bft.person_id = :per_id ';
2233 --
2234 elsif p_business_group_id is not null
2235 then
2236 --
2237 l_where_str := l_where_str||' and bft.business_group_id = :bgp_id ';
2238 --
2239 end if;
2240 --
2241 l_sel_str := ' select bft.benefit_action_id, '
2242 ||' bft.business_group_id, '
2243 ||' bft.person_id, '
2244 ||' bft.ler_id, '
2245 ||' bft.process_date ';
2246 l_from_str := ' from ben_benefit_actions bft ';
2247 l_ordby_str := ' order by bft.benefit_action_id desc ';
2248 --
2249 l_sql_str := l_sel_str
2250 ||' '||l_from_str
2251 ||' '||l_where_str
2252 ||' '||l_ordby_str;
2253 --
2254 if p_person_id is not null
2255 then
2256 --
2257 open c_bftinst_cur FOR l_sql_str using l_st_date,l_end_date,p_person_id;
2258 --
2259 elsif p_business_group_id is not null
2260 then
2261 --
2262 open c_bftinst_cur FOR l_sql_str using l_st_date,l_end_date,p_business_group_id;
2263 --
2264 else
2265 --
2266 open c_bftinst_cur FOR l_sql_str using l_st_date,l_end_date;
2267 --
2268 end if;
2269 --
2270 l_rollref_cnt := 0;
2271 --
2272 loop
2273 FETCH c_bftinst_cur INTO l_bft_inst;
2274 EXIT WHEN c_bftinst_cur%NOTFOUND;
2275 /*
2276 --
2277 -- Debugging
2278 --
2279 l_reptext_va.extend(1);
2280 l_reptext_en := l_reptext_en+1;
2281 l_reptext_va(l_reptext_en) := '-- l_bft_inst.benefit_action_id: '||l_bft_inst.benefit_action_id;
2282 --
2283 l_reptext_va.extend(1);
2284 l_reptext_en := l_reptext_en+1;
2285 l_reptext_va(l_reptext_en) := '-- p_refresh_rollups: '||p_refresh_rollups;
2286
2287 */
2288 --
2289 ben_rollup_rbvs.rollup_benmngle_rbvs
2290 (p_benefit_action_id => l_bft_inst.benefit_action_id
2291 ,p_refresh_rollups => 'Y'
2292 );
2293 --
2294 l_rollref_cnt := l_rollref_cnt+1;
2295 commit;
2296 --
2297 end loop;
2298 close c_bftinst_cur;
2299 --
2300 l_reptext_va.extend(1);
2301 l_reptext_en := l_reptext_en+1;
2302 l_reptext_va(l_reptext_en) := '-- Rolled up: '||l_rollref_cnt||' benefit actions ';
2303 --
2304 end if;
2305 --
2306 -- Report out results
2307 --
2308 l_where_str := ' where bft.last_update_date '
2309 ||' between :st_date and :end_date '
2310 ||' and pba.batch_id = bft.benefit_action_id '
2311 ||' and pba.batch_type = '||''''||'BEN_BFT'||'''';
2312 --
2313 if p_person_id is not null
2314 then
2315 --
2316 l_where_str := l_where_str||' and bft.person_id = :per_id ';
2317 --
2318 elsif p_ler_id is not null
2319 then
2320 --
2321 l_where_str := l_where_str||' and bft.ler_id = :ler_id ';
2322 --
2323 elsif p_business_group_id is not null
2324 then
2325 --
2326 l_where_str := l_where_str||' and bft.business_group_id = :bgp_id ';
2327 --
2328 end if;
2329 --
2330 l_sql_str := 'select max(bft.benefit_action_id) max_bftid, '
2331 ||' bft.process_date, '
2332 ||' bft.mode_cd, '
2333 ||' bft.business_group_id, '
2334 ||' bft.BENFTS_GRP_ID, '
2335 ||' bft.person_id, '
2336 ||' bft.pgm_id, '
2337 ||' bft.pl_id, '
2338 ||' bft.ler_id, '
2339 ||' bft.opt_id, '
2340 ||' max(bft.creation_date) max_credt, '
2341 ||' max(bft.request_id) '
2342 ||' from ben_benefit_actions bft, '
2343 ||' ben_batch_actions pba '
2344 ||' '||l_where_str
2345 ||' group by bft.process_date, '
2346 ||' bft.mode_cd, '
2347 ||' bft.business_group_id, '
2348 ||' bft.BENFTS_GRP_ID, '
2349 ||' bft.person_id, '
2350 ||' bft.pgm_id, '
2351 ||' bft.pl_id, '
2352 ||' bft.ler_id, '
2353 ||' bft.opt_id '
2354 ||' order by max(bft.benefit_action_id) desc ';
2355 --
2356 if p_person_id is not null
2357 then
2358 --
2359 open c_bftinst_cur FOR l_sql_str using l_st_date,l_end_date,p_person_id;
2360 --
2361 elsif p_ler_id is not null
2362 then
2363 --
2364 open c_bftinst_cur FOR l_sql_str using l_st_date,l_end_date,p_ler_id;
2365 --
2366 elsif p_business_group_id is not null
2367 then
2368 --
2369 open c_bftinst_cur FOR l_sql_str using l_st_date,l_end_date,p_business_group_id;
2370 --
2371 else
2372 --
2373 open c_bftinst_cur FOR l_sql_str using l_st_date,l_end_date;
2374 --
2375 end if;
2376 --
2377 l_reptext_va.extend(1);
2378 l_reptext_en := l_reptext_en+1;
2379 l_reptext_va(l_reptext_en) := '-- ';
2380 --
2381 l_vaen := 1;
2382 --
2383 loop
2384 FETCH c_bftinst_cur INTO l_bft_dets;
2385 EXIT WHEN c_bftinst_cur%NOTFOUND;
2386 --
2387 l_newbft_id_va.extend(1);
2388 l_newbftmode_cd_va.extend(1);
2389 l_newbftbgp_id_va.extend(1);
2390 l_newbftper_id_va.extend(1);
2391 l_newbftpgm_id_va.extend(1);
2392 l_newbftpl_id_va.extend(1);
2393 l_newbftbfg_id_va.extend(1);
2394 l_newbftproc_dt_va.extend(1);
2395 l_newbftler_id_va.extend(1);
2396 l_newbftopt_id_va.extend(1);
2397 /*
2398 l_newbftleodt_dt_va.extend(1);
2399 */
2400 l_newbftmax_credt_va.extend(1);
2401 l_newbftreqid_va.extend(1);
2402 --
2403 l_newbft_id_va(l_vaen) := l_bft_dets.max_bftid;
2404 l_newbftmode_cd_va(l_vaen) := l_bft_dets.mode_cd;
2405 l_newbftbgp_id_va(l_vaen) := l_bft_dets.business_group_id;
2406
2407 l_newbftper_id_va(l_vaen) := l_bft_dets.person_id;
2408 l_newbftpgm_id_va(l_vaen) := l_bft_dets.pgm_id;
2409 l_newbftpl_id_va(l_vaen) := l_bft_dets.pl_id;
2410 l_newbftbfg_id_va(l_vaen) := l_bft_dets.BENFTS_GRP_ID;
2411 l_newbftproc_dt_va(l_vaen) := l_bft_dets.process_date;
2412 l_newbftler_id_va(l_vaen) := l_bft_dets.ler_id;
2413 l_newbftopt_id_va(l_vaen) := l_bft_dets.opt_id;
2414 /*
2415 l_newbftleodt_dt_va(l_vaen) := l_bft_dets.LF_EVT_OCRD_DT;
2416 */
2417 l_newbftmax_credt_va(l_vaen) := l_bft_dets.max_credt;
2418 l_newbftreqid_va(l_vaen) := l_bft_dets.request_id;
2419 /*
2420 --
2421 -- Debugging parameters
2422 --
2423 l_reptext_va.extend(1);
2424 l_reptext_en := l_reptext_en+1;
2425 l_reptext_va(l_reptext_en) := '-- Debugging parameters ';
2426 --
2427 l_reptext_va.extend(1);
2428 l_reptext_en := l_reptext_en+1;
2429 l_reptext_va(l_reptext_en) := '-- BFTPGMID: '||l_bft_dets.pgm_id;
2430 --
2431 l_reptext_va.extend(1);
2432 l_reptext_en := l_reptext_en+1;
2433 l_reptext_va(l_reptext_en) := '-- BFTPLNID: '||l_bft_dets.pl_id;
2434 --
2435 l_reptext_va.extend(1);
2436 l_reptext_en := l_reptext_en+1;
2437 l_reptext_va(l_reptext_en) := '-- BFTLERID: '||l_bft_dets.ler_id;
2438 --
2439 */
2440 l_vaen := l_vaen+1;
2441 --
2442 end loop;
2443 close c_bftinst_cur;
2444 --
2445 -- Display benefit action info
2446 --
2447 if l_newbft_id_va.count > 0
2448 then
2449 --
2450 ben_benefit_actions_api.create_perf_benefit_actions
2451 (p_validate => false
2452 ,p_process_date => trunc(sysdate)
2453 ,p_mode_cd => 'Q'
2454 ,p_derivable_factors_flag => 'N'
2455 ,p_validate_flag => 'N'
2456 ,p_person_id => null
2457 ,p_person_type_id => null
2458 ,p_pgm_id => null
2459 ,p_business_group_id => nvl(l_bft_dets.business_group_id,-999999999999999)
2460 ,p_pl_id => null
2461 ,p_no_programs_flag => 'N'
2462 ,p_no_plans_flag => 'N'
2463 ,p_comp_selection_rl => null
2464 ,p_person_selection_rl => null
2465 ,p_ler_id => null
2466 ,p_organization_id => null
2467 ,p_benfts_grp_id => null
2468 ,p_location_id => null
2469 ,p_pstl_zip_rng_id => null
2470 ,p_rptg_grp_id => null
2471 ,p_pl_typ_id => null
2472 ,p_opt_id => null
2473 ,p_eligy_prfl_id => null
2474 ,p_vrbl_rt_prfl_id => null
2475 ,p_legal_entity_id => null
2476 ,p_payroll_id => null
2477 ,p_debug_messages_flag => 'N'
2478 ,p_audit_log_flag => 'N'
2479 ,p_lmt_prpnip_by_org_flag => 'N'
2480 ,p_request_id => fnd_global.conc_request_id
2481 ,p_program_application_id => fnd_global.prog_appl_id
2482 ,p_program_id => fnd_global.conc_program_id
2483 ,p_program_update_date => sysdate
2484 ,p_object_version_number => l_thnbft_ovn
2485 ,p_lf_evt_ocrd_dt => null
2486 ,p_effective_date => null
2487 --
2488 ,p_benefit_action_id => l_thnbft_id
2489 );
2490 --
2491 commit;
2492 /*
2493 --
2494 -- Debugging parameters
2495 --
2496 l_reptext_va.extend(1);
2497 l_reptext_en := l_reptext_en+1;
2498 l_reptext_va(l_reptext_en) := '-- Debugging parameters ';
2499 --
2500 l_reptext_va.extend(1);
2501 l_reptext_en := l_reptext_en+1;
2502 l_reptext_va(l_reptext_en) := '-- BGPID: '||p_business_group_id;
2503 --
2504 l_reptext_va.extend(1);
2505 l_reptext_en := l_reptext_en+1;
2506 l_reptext_va(l_reptext_en) := '-- PERID: '||p_person_id;
2507 --
2508 l_reptext_va.extend(1);
2509 l_reptext_en := l_reptext_en+1;
2510 l_reptext_va(l_reptext_en) := '-- ';
2511 --
2512 */
2513 for vaen in l_newbft_id_va.first..l_newbft_id_va.last
2514 loop
2515 --
2516 l_bgp_name := null;
2517 l_per_fullname := null;
2518 l_pgm_name := null;
2519 l_pln_name := null;
2520 l_ler_name := null;
2521 l_opt_name := null;
2522 --
2523 if l_newbftbgp_id_va(vaen) is not null
2524 then
2525 --
2526 open c_bgp_dets
2527 (c_bgp_id => l_newbftbgp_id_va(vaen)
2528 );
2529 fetch c_bgp_dets into l_bgp_name;
2530 close c_bgp_dets;
2531 --
2532 end if;
2533 --
2534 if l_newbftper_id_va(vaen) is not null
2535 then
2536 --
2537 open c_perdets
2538 (c_per_id => l_newbftper_id_va(vaen)
2539 );
2540 fetch c_perdets into l_per_fullname;
2541 close c_perdets;
2542 --
2543 end if;
2544 --
2545 if l_newbftpgm_id_va(vaen) is not null
2546 then
2547 --
2548 open c_pgmdets
2549 (c_pgm_id => l_newbftpgm_id_va(vaen)
2550 );
2551 fetch c_pgmdets into l_pgm_name;
2552 close c_pgmdets;
2553 --
2554 end if;
2555 --
2556 if l_newbftpl_id_va(vaen) is not null
2557 then
2558 --
2559 open c_plndets
2560 (c_pl_id => l_newbftpl_id_va(vaen)
2561 );
2562 fetch c_plndets into l_pln_name;
2563 close c_plndets;
2564 --
2565 end if;
2566 --
2567 if l_newbftler_id_va(vaen) is not null
2568 then
2569 --
2570 open c_lerdets
2571 (c_ler_id => l_newbftler_id_va(vaen)
2572 );
2573 fetch c_lerdets into l_ler_name;
2574 close c_lerdets;
2575 --
2576 end if;
2577 --
2578 if l_newbftopt_id_va(vaen) is not null
2579 then
2580 --
2581 open c_optdets
2582 (c_opt_id => l_newbftopt_id_va(vaen)
2583 );
2584 fetch c_optdets into l_opt_name;
2585 close c_optdets;
2586 --
2587 end if;
2588 --
2589 -- Check for batch info
2590 --
2591 open c_bpidets
2592 (c_bft_id => l_newbft_id_va(vaen)
2593 );
2594 fetch c_bpidets into l_v2esecs;
2595 close c_bpidets;
2596 --
2597 -- Get processed person actions
2598 --
2599 PersonActionStats
2600 (p_bft_id => l_newbft_id_va(vaen)
2601 --
2602 ,p_prperact_cnt => l_prperact_cnt
2603 ,p_errperact_cnt => l_errperact_cnt
2604 ,p_upperact_cnt => l_upperact_cnt
2605 );
2606 --
2607 BBRThreadStats
2608 (p_bft_id => l_newbft_id_va(vaen)
2609 --
2610 ,p_thread_cnt => l_bbrthread_cnt
2611 ,p_mxdursecs => l_thread_mxdursecs
2612 ,p_mndursecs => l_thread_mndursecs
2613 ,p_mxchkcnt => l_thread_mxchkcnt
2614 ,p_mnchkcnt => l_thread_mnchkcnt
2615 );
2616 --
2617 -- Check for the concurrent manager env
2618 --
2619 l_ccrreq_cnt := 0;
2620 l_ccrcompreq_cnt := 0;
2621 l_ccrrunreq_cnt := 0;
2622 l_ccrerrreq_cnt := 0;
2623 l_ccrscdtreq_cnt := 0;
2624 l_ccrscddreq_cnt := 0;
2625 l_ccrscdgreq_cnt := 0;
2626 l_ccrscdxreq_cnt := 0;
2627 l_ccrscdireq_cnt := 0;
2628 l_ccrscdqreq_cnt := 0;
2629 l_ccroraerrreq_cnt := 0;
2630 l_ccrhanderrreq_cnt := 0;
2631 l_ccrapperrreq_cnt := 0;
2632 l_ccrerrcomptxt_va.delete;
2633 l_ccrhanderrcomptxt_va.delete;
2634 --
2635 if l_newbftreqid_va(vaen) <> -1
2636 then
2637 --
2638 CCRequestStats
2639 (p_bft_id => l_newbft_id_va(vaen)
2640 --
2641 ,p_ccrreq_cnt => l_ccrreq_cnt
2642 ,p_ccrcompreq_cnt => l_ccrcompreq_cnt
2643 ,p_ccrrunreq_cnt => l_ccrrunreq_cnt
2644 ,p_ccrerrreq_cnt => l_ccrerrreq_cnt
2645 ,p_ccrscdtreq_cnt => l_ccrscdtreq_cnt
2646 ,p_ccrscddreq_cnt => l_ccrscddreq_cnt
2647 ,p_ccrscdgreq_cnt => l_ccrscdgreq_cnt
2648 ,p_ccrscdxreq_cnt => l_ccrscdxreq_cnt
2649 ,p_ccrscdireq_cnt => l_ccrscdireq_cnt
2650 ,p_ccrscdqreq_cnt => l_ccrscdqreq_cnt
2651 ,p_ccroraerrreq_cnt => l_ccroraerrreq_cnt
2652 ,p_ccrhanderrreq_cnt => l_ccrhanderrreq_cnt
2653 ,p_ccrapperrreq_cnt => l_ccrapperrreq_cnt
2654 ,p_ccrerrcomptxt_va => l_ccrerrcomptxt_va
2655 ,p_ccrhanderrcomptxt_va => l_ccrhanderrcomptxt_va
2656 );
2657 --
2658 end if;
2659 --
2660 OracleErrs
2661 (p_bft_id => l_newbft_id_va(vaen)
2662 --
2663 ,p_oraerr_tot => l_oraerr_tot
2664 ,p_oraerrtext_va => l_oraerrtext_va
2665 ,p_oraerrcnt_va => l_oraerrcnt_va
2666 ,p_oraerrmxperid_va => l_oraerrmxperid_va
2667 ,p_oraerrtextinst_va => l_oraerrtextinst_va
2668 );
2669 --
2670 -- Check for new bft alerts
2671 --
2672 g_alertsev1_en := 1;
2673 g_alertsev2_en := 1;
2674 g_alertsev1reas_va.delete;
2675 g_alertsev1bftid_va.delete;
2676 g_alertsev1prevbftid_va.delete;
2677 g_alertsev2reas_va.delete;
2678 g_alertsev2bftid_va.delete;
2679 --
2680 NewBftAlertCheck
2681 (p_bft_id => l_newbft_id_va(vaen)
2682 ,p_upperact_cnt => l_upperact_cnt
2683 ,p_ccroraerrreq_cnt => l_ccroraerrreq_cnt
2684 ,p_ccrhanderrreq_cnt => l_ccrhanderrreq_cnt
2685 ,p_oraerr_tot => l_oraerr_tot
2686 );
2687 --
2688 -- Calculate performance metrics
2689 --
2690 l_v2esecs := replace(replace(l_v2esecs,'seconds',null),',',null);
2691 l_esecs := l_v2esecs;
2692 l_ehrs := floor(l_esecs/3600);
2693 l_ehrmins := floor((l_esecs-(l_ehrs*3600))/60);
2694 --
2695 if l_prperact_cnt > 0
2696 then
2697 --
2698 l_tphr := round(((60/(l_esecs/l_prperact_cnt))*60),2);
2699 --
2700 else
2701 --
2702 l_tphr := null;
2703 --
2704 end if;
2705 --
2706 l_reptext_va.extend(1);
2707 l_reptext_en := l_reptext_en+1;
2708 l_reptext_va(l_reptext_en) := '==========================================================';
2709 --
2710 l_reptext_va.extend(1);
2711 l_reptext_en := l_reptext_en+1;
2712 l_reptext_va(l_reptext_en) := '-- Benefit Action: '||l_newbft_id_va(vaen)
2713 ||' Time: '||to_char(l_newbftmax_credt_va(vaen),'DD-MON-YYYY-HH24-MI-SS');
2714 --
2715 l_reptext_va.extend(1);
2716 l_reptext_en := l_reptext_en+1;
2717 l_reptext_va(l_reptext_en) := '-- ';
2718 --
2719 l_reptext_va.extend(1);
2720 l_reptext_en := l_reptext_en+1;
2721 l_reptext_va(l_reptext_en) := '-- Duration: '
2722 ||l_ehrs||':'||l_ehrmins
2723 ||' Secs: '||l_esecs
2724 ||' Proc: '||l_prperact_cnt
2725 ||' TP: '||l_tphr;
2726
2727 --
2728 l_reptext_va.extend(1);
2729 l_reptext_en := l_reptext_en+1;
2730 l_reptext_va(l_reptext_en) := '-- Person Actions: Proc: '||l_prperact_cnt
2731 ||' Err: '||l_errperact_cnt
2732 ||' UP: '||l_upperact_cnt
2733 ||' ORAErr: '||l_oraerr_tot;
2734 --
2735 l_reptext_va.extend(1);
2736 l_reptext_en := l_reptext_en+1;
2737 l_reptext_va(l_reptext_en) := '-- Threads: '||l_bbrthread_cnt
2738 ||' Dur: Mx/Mn '||l_thread_mxdursecs||'/'||l_thread_mndursecs
2739 ||' Chunks: Mx/Mn '||l_thread_mxchkcnt||'/'||l_thread_mnchkcnt;
2740 --
2741 if l_newbftreqid_va(vaen) <> -1
2742 then
2743 --
2744 l_reptext_va.extend(1);
2745 l_reptext_en := l_reptext_en+1;
2746 l_reptext_va(l_reptext_en) := '-- Concurrent Requests: '||l_ccrreq_cnt
2747 ||' Comp: '||l_ccrcompreq_cnt
2748 ||' Run: '||l_ccrrunreq_cnt
2749 ||' Err: '||l_ccrerrreq_cnt
2750 ||' ORAErr: '||l_ccroraerrreq_cnt
2751 ||' EnvErr: '||l_ccrhanderrreq_cnt
2752 ||' AppErr: '||l_ccrapperrreq_cnt;
2753 --
2754 end if;
2755 --
2756 BuildAlertStrVa
2757 (p_alertstr_va => l_alertstr_va
2758 );
2759 --
2760 -- Display alerts
2761 --
2762 l_reptext_va.extend(1);
2763 l_reptext_en := l_reptext_en+1;
2764 l_reptext_va(l_reptext_en) := '-- ';
2765 --
2766 if l_alertstr_va.count > 0
2767 then
2768 --
2769 for alertvaen in l_alertstr_va.first..l_alertstr_va.last
2770 loop
2771 --
2772 l_reptext_va.extend(1);
2773 l_reptext_en := l_reptext_en+1;
2774 l_reptext_va(l_reptext_en) := l_alertstr_va(alertvaen);
2775 --
2776 end loop;
2777 --
2778 end if;
2779 --
2780 l_reptext_va.extend(1);
2781 l_reptext_en := l_reptext_en+1;
2782 l_reptext_va(l_reptext_en) := '-- ';
2783 --
2784 l_reptext_va.extend(1);
2785 l_reptext_en := l_reptext_en+1;
2786 l_reptext_va(l_reptext_en) := rpad('-- Business Group: ',25)||substr(l_bgp_name,1,30)
2787 ||' ('||l_newbftbgp_id_va(vaen)||') ';
2788 --
2789 l_reptext_va.extend(1);
2790 l_reptext_en := l_reptext_en+1;
2791 l_reptext_va(l_reptext_en) := rpad('-- Mode: ',25)||l_newbftmode_cd_va(vaen);
2792 --
2793 l_reptext_va.extend(1);
2794 l_reptext_en := l_reptext_en+1;
2795 l_reptext_va(l_reptext_en) := rpad('-- Process Date: ',25)||l_newbftproc_dt_va(vaen);
2796 --
2797 l_reptext_va.extend(1);
2798 l_reptext_en := l_reptext_en+1;
2799 l_reptext_va(l_reptext_en) := rpad('-- Person: ',25)||substr(l_per_fullname,1,30)
2800 ||' ('||l_newbftper_id_va(vaen)||') ';
2801 --
2802 l_reptext_va.extend(1);
2803 l_reptext_en := l_reptext_en+1;
2804 l_reptext_va(l_reptext_en) := rpad('-- Life Event Reason: ',25)||substr(l_ler_name,1,30)
2805 ||' ('||l_newbftler_id_va(vaen)||') ';
2806 --
2807 l_reptext_va.extend(1);
2808 l_reptext_en := l_reptext_en+1;
2809 l_reptext_va(l_reptext_en) := rpad('-- Program: ',25)||substr(l_pgm_name,1,30)
2810 ||' ('||l_newbftpgm_id_va(vaen)||') ';
2811 --
2812 l_reptext_va.extend(1);
2813 l_reptext_en := l_reptext_en+1;
2814 l_reptext_va(l_reptext_en) := rpad('-- Plan: ',25)||substr(l_pln_name,1,30)
2815 ||' ('||l_newbftpl_id_va(vaen)||') ';
2816 --
2817 l_reptext_va.extend(1);
2818 l_reptext_en := l_reptext_en+1;
2819 l_reptext_va(l_reptext_en) := rpad('-- Option: ',25)||substr(l_opt_name,1,30)
2820 ||' ('||l_newbftopt_id_va(vaen)||') ';
2821 --
2822 l_reptext_va.extend(1);
2823 l_reptext_en := l_reptext_en+1;
2824 l_reptext_va(l_reptext_en) := '-- ';
2825 --
2826 open c_rbvdets
2827 (c_bft_id => l_newbft_id_va(vaen)
2828 );
2829 fetch c_rbvdets BULK COLLECT INTO l_clcode_va,
2830 l_clcodecnt_va,
2831 l_clsumovn_va,
2832 l_clminesd_va,
2833 l_clmineed_va,
2834 l_clidstr_va;
2835 close c_rbvdets;
2836 --
2837 -- Filter out non discrepancy rollups
2838 --
2839 filter_clcode_nondiscreps
2840 (p_clcode_va => l_clcode_va
2841 ,p_clcodecnt_va => l_clcodecnt_va
2842 ,p_clsumovn_va => l_clsumovn_va
2843 ,p_clminesd_va => l_clminesd_va
2844 ,p_clmineed_va => l_clmineed_va
2845 );
2846 --
2847 if l_clcode_va.count > 0 then
2848 --
2849 l_reptext_va.extend(1);
2850 l_reptext_en := l_reptext_en+1;
2851 l_reptext_va(l_reptext_en) := '--- Row counts by functional area: '||l_clcode_va.count;
2852 --
2853 l_reptext_va.extend(1);
2854 l_reptext_en := l_reptext_en+1;
2855 l_reptext_va(l_reptext_en) := '--- ';
2856 --
2857 for elenum in l_clcode_va.first..l_clcode_va.last
2858 loop
2859 --
2860 -- Get rollup type description
2861 --
2862 rollupcode_getrollupdesc
2863 (p_rollup_code => l_clcode_va(elenum)
2864 --
2865 ,p_rollup_desc => l_rollup_desc
2866 );
2867 --
2868 l_reptext_va.extend(1);
2869 l_reptext_en := l_reptext_en+1;
2870 l_reptext_va(l_reptext_en) := '--- '||rpad(l_clcodecnt_va(elenum),4)||' '
2871 ||l_rollup_desc||' ('||l_clcode_va(elenum)||') ';
2872 --
2873 end loop;
2874 --
2875 end if;
2876 --
2877 -- Get Error and person action details
2878 --
2879 get_appl_error_dets
2880 (p_bft_id => l_newbft_id_va(vaen)
2881 --
2882 ,p_errmesscd_va => l_errmesscd_va
2883 ,p_mxperid_va => l_mxperid_va
2884 ,p_errcnt_va => l_errcnt_va
2885 ,p_errtot => l_apperr_tot
2886 );
2887 --
2888 -- Get person action information
2889 --
2890 l_pactup_cnt := 0;
2891 l_pactproc_cnt := 0;
2892 l_pacterr_cnt := 0;
2893 --
2894 if l_clcode_va.count > 0
2895 then
2896 --
2897 for errvaen in l_clcode_va.first..l_clcode_va.last
2898 loop
2899 --
2900 if l_clcode_va(errvaen) = 'PERACTUNPROC'
2901 then
2902 --
2903 l_pactup_cnt := l_clcodecnt_va(errvaen);
2904 --
2905 elsif l_clcode_va(errvaen) = 'PERACTPROC'
2906 then
2907 --
2908 l_pactproc_cnt := l_clcodecnt_va(errvaen);
2909 --
2910 elsif l_clcode_va(errvaen) = 'PERACTERR'
2911 then
2912 --
2913 l_pacterr_cnt := l_clcodecnt_va(errvaen);
2914 --
2915 end if;
2916 --
2917 end loop;
2918 --
2919 end if;
2920 --
2921 -- Display error information
2922 --
2923 if l_apperr_tot > 0
2924 then
2925 --
2926 l_reptext_va.extend(1);
2927 l_reptext_en := l_reptext_en+1;
2928 l_reptext_va(l_reptext_en) := '---- ';
2929 --
2930 l_reptext_va.extend(1);
2931 l_reptext_en := l_reptext_en+1;
2932 l_reptext_va(l_reptext_en) := '---- Application Errors: '||l_apperr_tot;
2933 --
2934 if l_pacterr_cnt <> l_apperr_cnt
2935 then
2936 --
2937 l_reptext_va.extend(1);
2938 l_reptext_en := l_reptext_en+1;
2939 l_reptext_va(l_reptext_en) := '----- Unaccounted errors exist. '||l_apperr_tot
2940 ||' are accounted but '||l_pacterr_cnt||' were raised. ';
2941 --
2942 end if;
2943 --
2944 if l_errmesscd_va.count > 0
2945 then
2946 --
2947 for errvaen in l_errmesscd_va.first..l_errmesscd_va.last
2948 loop
2949 --
2950 l_reptext_va.extend(1);
2951 l_reptext_en := l_reptext_en+1;
2952 l_reptext_va(l_reptext_en) := '----- '||l_errcnt_va(errvaen)
2953 ||' '||l_errmesscd_va(errvaen)
2954 ||' '||l_mxperid_va(errvaen);
2955 --
2956 end loop;
2957 --
2958 end if;
2959 --
2960 end if;
2961 --
2962 -- UnHandled oracle errors
2963 --
2964 if l_ccrerrcomptxt_va.count > 0
2965 then
2966 --
2967 l_reptext_va.extend(1);
2968 l_reptext_en := l_reptext_en+1;
2969 l_reptext_va(l_reptext_en) := '---- ';
2970 --
2971 l_reptext_va.extend(1);
2972 l_reptext_en := l_reptext_en+1;
2973 l_reptext_va(l_reptext_en) := '---- Concurrent Request Oracle Errors: '||l_ccrerrcomptxt_va.count;
2974 --
2975 for ccrerrvaen in l_ccrerrcomptxt_va.first..l_ccrerrcomptxt_va.last
2976 loop
2977 --
2978 l_oraerr_text := Var2Value_StripMltBlanks(l_ccrerrcomptxt_va(ccrerrvaen));
2979 --
2980 Var2Value_Portion
2981 (p_var2_value => l_oraerr_text
2982 ,p_portion_length => 70
2983 --
2984 ,p_v2_val_portions => l_text_va
2985 );
2986 --
2987 if l_text_va.count > 0
2988 then
2989 --
2990 for txtvaen in l_text_va.first..l_text_va.last
2991 loop
2992 --
2993 l_reptext_va.extend(1);
2994 l_reptext_en := l_reptext_en+1;
2995 l_reptext_va(l_reptext_en) := '----- '||l_text_va(txtvaen);
2996 --
2997 end loop;
2998 --
2999 else
3000 --
3001 l_reptext_va.extend(1);
3002 l_reptext_en := l_reptext_en+1;
3003 l_reptext_va(l_reptext_en) := '----- '||l_oraerr_text;
3004 --
3005 end if;
3006 --
3007 end loop;
3008 --
3009 end if;
3010 --
3011 -- Handled oracle errors
3012 --
3013 if l_ccrhanderrcomptxt_va.count > 0
3014 then
3015 --
3016 l_reptext_va.extend(1);
3017 l_reptext_en := l_reptext_en+1;
3018 l_reptext_va(l_reptext_en) := '---- ';
3019 --
3020 l_reptext_va.extend(1);
3021 l_reptext_en := l_reptext_en+1;
3022 l_reptext_va(l_reptext_en) := '---- Concurrent Request Env Errors: '||l_ccrhanderrcomptxt_va.count;
3023 --
3024 for ccrerrvaen in l_ccrhanderrcomptxt_va.first..l_ccrhanderrcomptxt_va.last
3025 loop
3026 --
3027 l_oraerr_text := Var2Value_StripMltBlanks(l_ccrhanderrcomptxt_va(ccrerrvaen));
3028 --
3029 Var2Value_Portion
3030 (p_var2_value => l_oraerr_text
3031 ,p_portion_length => 70
3032 --
3033 ,p_v2_val_portions => l_text_va
3034 );
3035 --
3036 if l_text_va.count > 0
3037 then
3038 --
3039 for txtvaen in l_text_va.first..l_text_va.last
3040 loop
3041 --
3042 l_reptext_va.extend(1);
3043 l_reptext_en := l_reptext_en+1;
3044 l_reptext_va(l_reptext_en) := '----- '||l_text_va(txtvaen);
3045 --
3046 end loop;
3047 --
3048 else
3049 --
3050 l_reptext_va.extend(1);
3051 l_reptext_en := l_reptext_en+1;
3052 l_reptext_va(l_reptext_en) := '----- '||l_oraerr_text;
3053 --
3054 end if;
3055 --
3056 end loop;
3057 --
3058 end if;
3059 --
3060 -- Handled oracle errors
3061 --
3062 if l_oraerrtext_va.count > 0
3063 then
3064 --
3065 l_reptext_va.extend(1);
3066 l_reptext_en := l_reptext_en+1;
3067 l_reptext_va(l_reptext_en) := '---- ';
3068 --
3069 l_reptext_va.extend(1);
3070 l_reptext_en := l_reptext_en+1;
3071 l_reptext_va(l_reptext_en) := '---- Person Action Oracle Errors: '||l_oraerrtext_va.count;
3072 --
3073 for errvaen in l_oraerrtext_va.first..l_oraerrtext_va.last
3074 loop
3075 --
3076 l_oraerr_text := l_oraerrcnt_va(errvaen)||' '||Var2Value_StripMltBlanks(l_oraerrtext_va(errvaen));
3077 --
3078 Var2Value_Portion
3079 (p_var2_value => l_oraerr_text
3080 ,p_portion_length => 70
3081 --
3082 ,p_v2_val_portions => l_text_va
3083 );
3084 --
3085 if l_text_va.count > 0
3086 then
3087 --
3088 for txtvaen in l_text_va.first..l_text_va.last
3089 loop
3090 --
3091 l_reptext_va.extend(1);
3092 l_reptext_en := l_reptext_en+1;
3093 l_reptext_va(l_reptext_en) := '----- '||l_text_va(txtvaen);
3094 --
3095 end loop;
3096 --
3097 else
3098 --
3099 l_reptext_va.extend(1);
3100 l_reptext_en := l_reptext_en+1;
3101 l_reptext_va(l_reptext_en) := '----- '||l_oraerr_text;
3102 --
3103 end if;
3104 --
3105 end loop;
3106 --
3107 end if;
3108 --
3109 -- Check for person action issues
3110 --
3111 if l_pactup_cnt > 0
3112 or l_pactproc_cnt = 0
3113 then
3114 --
3115 l_reptext_va.extend(1);
3116 l_reptext_en := l_reptext_en+1;
3117 l_reptext_va(l_reptext_en) := '---- ';
3118 --
3119 -- Unprocessed person actions
3120 --
3121 if l_pactup_cnt > 0
3122 then
3123 --
3124 l_reptext_va.extend(1);
3125 l_reptext_en := l_reptext_en+1;
3126 l_reptext_va(l_reptext_en) := '---- Environment Errors: '||l_pactup_cnt;
3127 --
3128 elsif l_pactproc_cnt = 0
3129 then
3130 --
3131 l_reptext_va.extend(1);
3132 l_reptext_en := l_reptext_en+1;
3133 l_reptext_va(l_reptext_en) := '---- No person actions processed ';
3134 --
3135 end if;
3136 --
3137 end if;
3138 --
3139 -- Get historic benefit actions
3140 --
3141 l_baseline_cnt := 0;
3142 --
3143 -- Set the latest benefit action
3144 --
3145 l_prevbft_id := l_newbft_id_va(vaen);
3146 --
3147 /*
3148 --
3149 -- Debugging
3150 --
3151 l_reptext_va.extend(1);
3152 l_reptext_en := l_reptext_en+1;
3153 l_reptext_va(l_reptext_en) := 'c_ghistbftdets: l_hist_duration: '||l_hist_duration;
3154 --
3155 */
3156 for row in c_ghistbftdets
3157 (c_duration => l_hist_duration
3158 ,c_mode_cd => l_newbftmode_cd_va(vaen)
3159 ,c_process_date => l_newbftproc_dt_va(vaen)
3160 ,c_bgp_id => l_newbftbgp_id_va(vaen)
3161 ,c_ler_id => l_newbftler_id_va(vaen)
3162 ,c_per_id => l_newbftper_id_va(vaen)
3163 ,c_pgm_id => l_newbftpgm_id_va(vaen)
3164 ,c_pl_id => l_newbftpl_id_va(vaen)
3165 ,c_opt_id => l_newbftopt_id_va(vaen)
3166 ,c_bfg_id => l_newbftbfg_id_va(vaen)
3167 ,c_bft_credt => l_newbftmax_credt_va(vaen)
3168 )
3169 loop
3170 --
3171 /*
3172 --
3173 -- Debugging
3174 --
3175 l_reptext_va.extend(1);
3176 l_reptext_en := l_reptext_en+1;
3177 l_reptext_va(l_reptext_en) := '---- l_baseline_cnt: '||l_baseline_cnt
3178 ||' l_baselines: '||l_baselines;
3179 --
3180 */
3181 if l_baseline_cnt > l_baselines
3182 then
3183 --
3184 exit;
3185 --
3186 end if;
3187 --
3188 -- Get old bft processed person action stats
3189 --
3190 PersonActionStats
3191 (p_bft_id => row.benefit_action_id
3192 --
3193 ,p_prperact_cnt => l_oldprperact_cnt
3194 ,p_errperact_cnt => l_olderrperact_cnt
3195 ,p_upperact_cnt => l_oldupperact_cnt
3196 );
3197 --
3198 -- Check for new bft alerts
3199 --
3200 g_alertsev1_en := 1;
3201 g_alertsev2_en := 1;
3202 g_alertsev1reas_va.delete;
3203 g_alertsev1bftid_va.delete;
3204 g_alertsev1prevbftid_va.delete;
3205 g_alertsev2reas_va.delete;
3206 g_alertsev2bftid_va.delete;
3207 --
3208 PerActCompAlertCheck
3209 (p_bft_id => l_newbft_id_va(vaen)
3210 ,p_prevbft_id => row.benefit_action_id
3211 ,p_oldprperact_cnt => l_oldprperact_cnt
3212 ,p_prperact_cnt => l_prperact_cnt
3213 ,p_olderrperact_cnt => l_olderrperact_cnt
3214 ,p_errperact_cnt => l_errperact_cnt
3215 ,p_oldupperact_cnt => l_oldupperact_cnt
3216 ,p_upperact_cnt => l_upperact_cnt
3217 );
3218 --
3219 BuildAlertStrVa
3220 (p_alertstr_va => l_alertstr_va
3221 );
3222 --
3223 -- Display alerts
3224 --
3225 if l_alertstr_va.count > 0
3226 then
3227 --
3228 l_reptext_va.extend(1);
3229 l_reptext_en := l_reptext_en+1;
3230 l_reptext_va(l_reptext_en) := '-- ';
3231 --
3232 for alertvaen in l_alertstr_va.first..l_alertstr_va.last
3233 loop
3234 --
3235 l_reptext_va.extend(1);
3236 l_reptext_en := l_reptext_en+1;
3237 l_reptext_va(l_reptext_en) := l_alertstr_va(alertvaen);
3238 --
3239 end loop;
3240 --
3241 end if;
3242 --
3243 -- Reset latest RBV information to the previous baseline
3244 --
3245 open c_rbvdets
3246 (c_bft_id => l_prevbft_id
3247 );
3248 fetch c_rbvdets BULK COLLECT INTO l_clcode_va,
3249 l_clcodecnt_va,
3250 l_clsumovn_va,
3251 l_clminesd_va,
3252 l_clmineed_va,
3253 l_clidstr_va;
3254 close c_rbvdets;
3255 /*
3256 --
3257 -- Temporary
3258 --
3259 dbms_output.put_line('- BENTSTHN: c_rbvdets: l_prevbft_id: '||l_prevbft_id
3260 );
3261 --
3262 for tmpvaen in l_clcode_va.first..l_clcode_va.last
3263 loop
3264 --
3265 dbms_output.put_line('-- '||l_clcode_va(tmpvaen)
3266 );
3267 --
3268 end loop;
3269 --
3270 */
3271 --
3272 -- Filter out non discrepancy rollups
3273 --
3274 filter_clcode_nondiscreps
3275 (p_clcode_va => l_clcode_va
3276 ,p_clcodecnt_va => l_clcodecnt_va
3277 ,p_clsumovn_va => l_clsumovn_va
3278 ,p_clminesd_va => l_clminesd_va
3279 ,p_clmineed_va => l_clmineed_va
3280 );
3281 --
3282 -- Get Error and person action details
3283 --
3284 get_appl_error_dets
3285 (p_bft_id => l_prevbft_id
3286 --
3287 ,p_errmesscd_va => l_errmesscd_va
3288 ,p_mxperid_va => l_mxperid_va
3289 ,p_errcnt_va => l_errcnt_va
3290 ,p_errtot => l_apperr_tot
3291 );
3292 --
3293 l_prevbft_id := row.benefit_action_id;
3294 --
3295 open c_rbvdets
3296 (c_bft_id => row.benefit_action_id
3297 );
3298 fetch c_rbvdets BULK COLLECT INTO l_rbv_clcode_va,
3299 l_rbv_clcodecnt_va,
3300 l_rbv_clsumovn_va,
3301 l_rbv_clminesd_va,
3302 l_rbv_clmineed_va,
3303 l_rbv_clidstr_va;
3304 close c_rbvdets;
3305 /*
3306 --
3307 -- Temporary
3308 --
3309 dbms_output.put_line('- BENTSTHN: c_rbvdets: rowbft_id: '||row.benefit_action_id
3310 );
3311 --
3312 for tmpvaen in l_rbv_clcode_va.first..l_rbv_clcode_va.last
3313 loop
3314 --
3315 dbms_output.put_line('-- '||l_rbv_clcode_va(tmpvaen)
3316 );
3317 --
3318 end loop;
3319 --
3320 */
3321 --
3322 -- Filter non discrepancy rollup types
3323 --
3324 filter_clcode_nondiscreps
3325 (p_clcode_va => l_rbv_clcode_va
3326 ,p_clcodecnt_va => l_rbv_clcodecnt_va
3327 ,p_clsumovn_va => l_rbv_clsumovn_va
3328 ,p_clminesd_va => l_rbv_clminesd_va
3329 ,p_clmineed_va => l_rbv_clmineed_va
3330 );
3331 --
3332 -- Check matches
3333 --
3334 l_mm_elenum := 1;
3335 l_mmclccode_va.delete;
3336 l_mmoclcount_va.delete;
3337 l_mmnclcount_va.delete;
3338 --
3339 l_mt_elenum := 1;
3340 l_mtclccode_va.delete;
3341 l_mtoclcount_va.delete;
3342 l_mtnclcount_va.delete;
3343 --
3344 if l_rbv_clcode_va.count <> l_clcode_va.count
3345 and l_rbv_clcode_va.count > 0
3346 and l_clcode_va.count > 0
3347 then
3348 --
3349 l_mmrltyp_rbvclcd_vaen := 1;
3350 l_mmrltyp_rbvclcd_va.delete;
3351 /*
3352 --
3353 -- Debugging parameters
3354 --
3355 l_reptext_va.extend(1);
3356 l_reptext_en := l_reptext_en+1;
3357 l_reptext_va(l_reptext_en) := '-- Debugging parameters ';
3358 --
3359 l_reptext_va.extend(1);
3360 l_reptext_en := l_reptext_en+1;
3361 l_reptext_va(l_reptext_en) := '-- l_rbv_clcode_va.count > l_clcode_va.count: ';
3362 --
3363 */
3364 --
3365 for clvaen in l_rbv_clcode_va.first..l_rbv_clcode_va.last
3366 loop
3367 --
3368 l_rbvcode_match := FALSE;
3369 --
3370 for subvaen in l_clcode_va.first..l_clcode_va.last
3371 loop
3372 --
3373 if l_rbv_clcode_va(clvaen) = l_clcode_va(subvaen)
3374 then
3375 --
3376 l_rbvcode_match := TRUE;
3377 exit;
3378 --
3379 end if;
3380 --
3381 end loop;
3382 --
3383 if not l_rbvcode_match
3384 then
3385 --
3386 l_mmrltyp_rbvclcd_va.extend(1);
3387 l_mmrltyp_rbvclcd_va(l_mmrltyp_rbvclcd_vaen) := l_rbv_clcode_va(clvaen);
3388 l_mmrltyp_rbvclcd_vaen := l_mmrltyp_rbvclcd_vaen+1;
3389 --
3390 end if;
3391 --
3392 end loop;
3393 /*
3394 --
3395 -- Debugging parameters
3396 --
3397 l_reptext_va.extend(1);
3398 l_reptext_en := l_reptext_en+1;
3399 l_reptext_va(l_reptext_en) := '-- Debugging parameters ';
3400 --
3401 l_reptext_va.extend(1);
3402 l_reptext_en := l_reptext_en+1;
3403 l_reptext_va(l_reptext_en) := '-- l_mmrltyp_rbvclcd_va.count: '||l_mmrltyp_rbvclcd_va.count;
3404 --
3405 */
3406 /*
3407 --
3408 -- Debugging parameters
3409 --
3410 l_reptext_va.extend(1);
3411 l_reptext_en := l_reptext_en+1;
3412 l_reptext_va(l_reptext_en) := '-- Debugging parameters ';
3413 --
3414 l_reptext_va.extend(1);
3415 l_reptext_en := l_reptext_en+1;
3416 l_reptext_va(l_reptext_en) := '-- l_clcode_va.count > l_rbv_clcode_va.count ';
3417 --
3418 */
3419 for clvaen in l_clcode_va.first..l_clcode_va.last
3420 loop
3421 --
3422 l_rbvcode_match := FALSE;
3423 --
3424 for subvaen in l_rbv_clcode_va.first..l_rbv_clcode_va.last
3425 loop
3426 --
3427 if l_clcode_va(clvaen) = l_rbv_clcode_va(subvaen)
3428 then
3429 --
3430 l_rbvcode_match := TRUE;
3431 exit;
3432 --
3433 end if;
3434 --
3435 end loop;
3436 --
3437 if not l_rbvcode_match
3438 then
3439 --
3440 l_mmrltyp_rbvclcd_va.extend(1);
3441 l_mmrltyp_rbvclcd_va(l_mmrltyp_rbvclcd_vaen) := l_clcode_va(clvaen);
3442 l_mmrltyp_rbvclcd_vaen := l_mmrltyp_rbvclcd_vaen+1;
3443 --
3444 end if;
3445 --
3446 end loop;
3447 --
3448 /*
3449 --
3450 -- Temporary debugging
3451 --
3452 if l_mmrltyp_rbvclcd_va.count > 0
3453 then
3454 --
3455 for tmpvaen in l_mmrltyp_rbvclcd_va.first..l_mmrltyp_rbvclcd_va.last
3456 loop
3457 --
3458 l_reptext_va.extend(1);
3459 l_reptext_en := l_reptext_en+1;
3460 l_reptext_va(l_reptext_en) := '----- l_mmrltyp_rbvclcd_va('||tmpvaen||') '
3461 ||l_mmrltyp_rbvclcd_va(tmpvaen);
3462 --
3463 end loop;
3464 --
3465 end if;
3466 --
3467 */
3468 elsif l_rbv_clcode_va.count > 0
3469 and l_clcode_va.count > 0
3470 then
3471 --
3472 for rbv_en in l_rbv_clcode_va.first..l_rbv_clcode_va.last
3473 loop
3474 --
3475 for clcd_en in l_clcode_va.first..l_clcode_va.last
3476 loop
3477 --
3478 if l_rbv_clcode_va(rbv_en) = l_clcode_va(clcd_en)
3479 then
3480 --
3481 if l_rbv_clcodecnt_va(rbv_en) <> l_clcodecnt_va(clcd_en)
3482 then
3483 --
3484 l_mmclccode_va.extend(1);
3485 l_mmoclcount_va.extend(1);
3486 l_mmnclcount_va.extend(1);
3487 --
3488 l_mmclccode_va(l_mm_elenum) := l_clcode_va(clcd_en);
3489 l_mmoclcount_va(l_mm_elenum) := l_rbv_clcodecnt_va(rbv_en);
3490 l_mmnclcount_va(l_mm_elenum) := l_clcodecnt_va(clcd_en);
3491 l_mm_elenum := l_mm_elenum+1;
3492 --
3493 else
3494 --
3495 l_mtclccode_va.extend(1);
3496 l_mtoclcount_va.extend(1);
3497 l_mtnclcount_va.extend(1);
3498 --
3499 l_mtclccode_va(l_mt_elenum) := l_clcodecnt_va(clcd_en);
3500 l_mtoclcount_va(l_mt_elenum) := l_rbv_clcodecnt_va(rbv_en);
3501 l_mtnclcount_va(l_mt_elenum) := l_clcodecnt_va(clcd_en);
3502 l_mt_elenum := l_mt_elenum+1;
3503 --
3504 end if;
3505 --
3506 end if;
3507 --
3508 end loop;
3509 --
3510 end loop;
3511 --
3512 /*
3513 --
3514 -- Temporary debugging
3515 --
3516 if l_mmclccode_va.count > 0
3517 then
3518 --
3519 for tmpvaen in l_mmclccode_va.first..l_mmclccode_va.last
3520 loop
3521 --
3522 l_reptext_va.extend(1);
3523 l_reptext_en := l_reptext_en+1;
3524 l_reptext_va(l_reptext_en) := '----- l_mmclccode_va('||tmpvaen||') '
3525 ||l_mmclccode_va(tmpvaen)
3526 ||' Base: '||l_mmoclcount_va(tmpvaen)
3527 ||' Nw: '||l_mmnclcount_va(tmpvaen);
3528 --
3529 end loop;
3530 --
3531 end if;
3532 --
3533 */
3534 end if;
3535 --
3536 -- Get baseline application error details
3537 --
3538 get_appl_error_dets
3539 (p_bft_id => row.benefit_action_id
3540 --
3541 ,p_errmesscd_va => l_mmerrmesscd_va
3542 ,p_mxperid_va => l_mmmxperid_va
3543 ,p_errcnt_va => l_mmerrcnt_va
3544 ,p_errtot => l_mmapperr_tot
3545 );
3546 --
3547 if l_rbv_clcode_va.count = l_clcode_va.count
3548 and l_mmclccode_va.count = 0
3549 and l_mmapperr_tot = l_apperr_tot
3550 and l_mmerrmesscd_va.count = l_errmesscd_va.count
3551 then
3552 --
3553 l_reptext_va.extend(1);
3554 l_reptext_en := l_reptext_en+1;
3555 l_reptext_va(l_reptext_en) := '---- ';
3556 --
3557 if l_baseline_cnt = 0
3558 then
3559 --
3560 l_reptext_va.extend(1);
3561 l_reptext_en := l_reptext_en+1;
3562 l_reptext_va(l_reptext_en) := '---- No discrepancies with baseline: ('||row.benefit_action_id||') '
3563 ||' Time: '||to_char(row.LAST_UPDATE_DATE,'DD-MON-YYYY-HH24-MI-SS');
3564 --
3565 else
3566 --
3567 l_reptext_va.extend(1);
3568 l_reptext_en := l_reptext_en+1;
3569 l_reptext_va(l_reptext_en) := '---- No discrepancies with previous baseline: ('||row.benefit_action_id||') '
3570 ||' Time: '||to_char(row.LAST_UPDATE_DATE,'DD-MON-YYYY-HH24-MI-SS');
3571 --
3572 end if;
3573 --
3574 else
3575 --
3576 -- Check for bug discrepancies
3577 --
3578 l_pactup_cnt := 0;
3579 --
3580 /*
3581 --
3582 -- Debugging parameters
3583 --
3584 l_reptext_va.extend(1);
3585 l_reptext_en := l_reptext_en+1;
3586 l_reptext_va(l_reptext_en) := '-- Debugging parameters ';
3587 --
3588 l_reptext_va.extend(1);
3589 l_reptext_en := l_reptext_en+1;
3590 l_reptext_va(l_reptext_en) := '-- l_mmclccode_va.count: '||l_mmclccode_va.count;
3591 --
3592 */
3593 --
3594 -- Check for unprocessed person actions for baseline
3595 --
3596 if l_rbv_clcode_va.count > 0
3597 then
3598 --
3599 l_pactup_cnt := 0;
3600 l_pactproc_cnt := 0;
3601 --
3602 for errvaen in l_rbv_clcode_va.first..l_rbv_clcode_va.last
3603 loop
3604 --
3605 if l_rbv_clcode_va(errvaen) = 'PERACTUNPROC'
3606 then
3607 --
3608 l_pactup_cnt := l_rbv_clcodecnt_va(errvaen);
3609 --
3610 elsif l_rbv_clcode_va(errvaen) = 'PERACTPROC'
3611 then
3612 --
3613 l_pactproc_cnt := l_rbv_clcodecnt_va(errvaen);
3614 --
3615 end if;
3616 --
3617 end loop;
3618 --
3619 end if;
3620 --
3621 end if;
3622 --
3623 -- Check person action issues
3624 --
3625 l_env_errors := false;
3626 l_discrepancy := false;
3627 --
3628 if (l_pactup_cnt > 0
3629 or l_pactproc_cnt = 0)
3630 and (l_pactup_cnt <> l_pactproc_cnt)
3631 then
3632 --
3633 l_discrepancy := true;
3634 --
3635 l_reptext_va.extend(1);
3636 l_reptext_en := l_reptext_en+1;
3637 l_reptext_va(l_reptext_en) := '---- ';
3638 --
3639 l_reptext_va.extend(1);
3640 l_reptext_en := l_reptext_en+1;
3641 l_reptext_va(l_reptext_en) := '---- Baseline: ('||row.benefit_action_id||') '
3642 ||' Time: '||to_char(row.LAST_UPDATE_DATE,'DD-MON-YYYY-HH24-MI-SS');
3643 --
3644 if l_pactup_cnt > 0
3645 then
3646 --
3647 l_env_errors := true;
3648 --
3649 l_reptext_va.extend(1);
3650 l_reptext_en := l_reptext_en+1;
3651 l_reptext_va(l_reptext_en) := '----- Baseline Environment Errors: '||l_pactup_cnt;
3652 --
3653 elsif l_pactproc_cnt = 0
3654 then
3655 --
3656 l_env_errors := true;
3657 --
3658 l_reptext_va.extend(1);
3659 l_reptext_en := l_reptext_en+1;
3660 l_reptext_va(l_reptext_en) := '----- No person actions processed for baseline';
3661 --
3662 end if;
3663 --
3664 --
3665 -- Check for application error type or total discrepancies
3666 --
3667 elsif l_mmapperr_tot <> l_apperr_tot
3668 or l_mmerrmesscd_va.count <> l_errmesscd_va.count
3669 then
3670 --
3671 l_discrepancy := true;
3672 --
3673 -- Determine application error type discrepancies
3674 --
3675 l_apperrtyp_discr_en := 1;
3676 l_apperrtypdiscr_messcd_va.delete;
3677 l_apperrtypdiscr_cnt_va.delete;
3678 --
3679 if l_mmerrmesscd_va.count > 0
3680 then
3681 --
3682 for errvaen in l_mmerrmesscd_va.first..l_mmerrmesscd_va.last
3683 loop
3684 --
3685 l_check_match := FALSE;
3686 --
3687 if l_errmesscd_va.count > 0
3688 then
3689 --
3690 for errsubvaen in l_errmesscd_va.first..l_errmesscd_va.last
3691 loop
3692 --
3693 if l_mmerrmesscd_va(errvaen) = l_errmesscd_va(errsubvaen)
3694 and l_mmerrcnt_va(errvaen) = l_errcnt_va(errsubvaen)
3695 then
3696 --
3697 l_check_match := TRUE;
3698 exit;
3699 --
3700 end if;
3701 --
3702 end loop;
3703 --
3704 end if;
3705 --
3706 if not l_check_match
3707 then
3708 --
3709 l_apperrtypdiscr_messcd_va.extend(1);
3710 l_apperrtypdiscr_cnt_va.extend(1);
3711 l_apperrtypdiscr_messcd_va(l_apperrtyp_discr_en) := l_mmerrmesscd_va(errvaen);
3712 l_apperrtypdiscr_cnt_va(l_apperrtyp_discr_en) := l_mmerrcnt_va(errvaen);
3713 l_apperrtyp_discr_en := l_apperrtyp_discr_en+1;
3714 --
3715 end if;
3716 --
3717 end loop;
3718 --
3719 end if;
3720 --
3721 if l_errmesscd_va.count > 0
3722 then
3723 --
3724 for errvaen in l_errmesscd_va.first..l_errmesscd_va.last
3725 loop
3726 --
3727 l_check_match := FALSE;
3728 --
3729 if l_mmerrmesscd_va.count > 0
3730 then
3731 --
3732 for errsubvaen in l_mmerrmesscd_va.first..l_mmerrmesscd_va.last
3733 loop
3734 --
3735 if l_errmesscd_va(errvaen) = l_mmerrmesscd_va(errsubvaen)
3736 and l_errcnt_va(errvaen) = l_mmerrcnt_va(errsubvaen)
3737 then
3738 --
3739 l_check_match := TRUE;
3740 exit;
3741 --
3742 end if;
3743 --
3744 end loop;
3745 --
3746 end if;
3747 --
3748 if not l_check_match
3749 then
3750 --
3751 l_apperrtypdiscr_messcd_va.extend(1);
3752 l_apperrtypdiscr_cnt_va.extend(1);
3753 l_apperrtypdiscr_messcd_va(l_apperrtyp_discr_en) := l_errmesscd_va(errvaen);
3754 l_apperrtypdiscr_cnt_va(l_apperrtyp_discr_en) := l_errcnt_va(errvaen);
3755 l_apperrtyp_discr_en := l_apperrtyp_discr_en+1;
3756 --
3757 end if;
3758 --
3759 end loop;
3760 --
3761 end if;
3762 --
3763 -- Deduce application error person id discrepancies
3764 --
3765 l_newapperr_peridva.delete;
3766 --
3767 if l_apperrtypdiscr_messcd_va.count > 0
3768 then
3769 --
3770 l_periddiscrep_en := 1;
3771 l_periddiscrep_errcd_va.delete;
3772 l_periddiscrep_perid_va.delete;
3773 --
3774 for apperrvaen in l_apperrtypdiscr_messcd_va.first..l_apperrtypdiscr_messcd_va.last
3775 loop
3776 --
3777 open c_bfterrcdperids
3778 (c_bft_id => row.benefit_action_id
3779 ,c_err_cd => l_apperrtypdiscr_messcd_va(apperrvaen)
3780 );
3781 fetch c_bfterrcdperids BULK COLLECT INTO l_perid_va1;
3782 close c_bfterrcdperids;
3783 --
3784 open c_bfterrcdperids
3785 (c_bft_id => l_newbft_id_va(vaen)
3786 ,c_err_cd => l_apperrtypdiscr_messcd_va(apperrvaen)
3787 );
3788 fetch c_bfterrcdperids BULK COLLECT INTO l_perid_va2;
3789 close c_bfterrcdperids;
3790 --
3791 l_newapperr_peridva := l_perid_va2;
3792 --
3793 if l_perid_va1.count > 0
3794 then
3795 --
3796 for pervaen in l_perid_va1.first..l_perid_va1.last
3797 loop
3798 --
3799 l_check_match := FALSE;
3800 --
3801 if l_perid_va2.count > 0
3802 then
3803 --
3804 for persubvaen in l_perid_va2.first..l_perid_va2.last
3805 loop
3806 --
3807 if l_perid_va1(pervaen) = l_perid_va2(persubvaen)
3808 then
3809 --
3810 l_check_match := TRUE;
3811 exit;
3812 --
3813 end if;
3814 --
3815 end loop;
3816 --
3817 end if;
3818 --
3819 if not l_check_match
3820 then
3821 --
3822 l_periddiscrep_perid_va.extend(1);
3823 l_periddiscrep_errcd_va.extend(1);
3824 l_periddiscrep_perid_va(l_periddiscrep_en) := l_perid_va1(pervaen);
3825 l_periddiscrep_errcd_va(l_periddiscrep_en) := l_apperrtypdiscr_messcd_va(apperrvaen);
3826 l_periddiscrep_en := l_periddiscrep_en+1;
3827 --
3828 end if;
3829 --
3830 end loop;
3831 --
3832 end if;
3833 --
3834 if l_perid_va2.count > 0
3835 then
3836 --
3837 for pervaen in l_perid_va2.first..l_perid_va2.last
3838 loop
3839 --
3840 l_check_match := FALSE;
3841 --
3842 if l_perid_va1.count > 0
3843 then
3844 --
3845 for persubvaen in l_perid_va1.first..l_perid_va1.last
3846 loop
3847 --
3848 if l_perid_va2(pervaen) = l_perid_va1(persubvaen)
3849 then
3850 --
3851 l_check_match := TRUE;
3852 exit;
3853 --
3854 end if;
3855 --
3856 end loop;
3857 --
3858 end if;
3859 --
3860 if not l_check_match
3861 then
3862 --
3863 l_periddiscrep_perid_va.extend(1);
3864 l_periddiscrep_errcd_va.extend(1);
3865 l_periddiscrep_perid_va(l_periddiscrep_en) := l_perid_va2(pervaen);
3866 l_periddiscrep_errcd_va(l_periddiscrep_en) := l_apperrtypdiscr_messcd_va(apperrvaen);
3867 l_periddiscrep_en := l_periddiscrep_en+1;
3868 --
3869 end if;
3870 --
3871 end loop;
3872 --
3873 end if;
3874 --
3875 end loop;
3876 --
3877 end if;
3878 --
3879 l_reptext_va.extend(1);
3880 l_reptext_en := l_reptext_en+1;
3881 l_reptext_va(l_reptext_en) := '---- ';
3882 --
3883 l_reptext_va.extend(1);
3884 l_reptext_en := l_reptext_en+1;
3885 l_reptext_va(l_reptext_en) := '---- Baseline: ('||row.benefit_action_id||') '
3886 ||' Time: '||to_char(row.LAST_UPDATE_DATE,'DD-MON-YYYY-HH24-MI-SS');
3887 --
3888 if l_mmerrmesscd_va.count <> l_errmesscd_va.count
3889 then
3890 --
3891 l_reptext_va.extend(1);
3892 l_reptext_en := l_reptext_en+1;
3893 l_reptext_va(l_reptext_en) := '----- Baseline Application Error type discrepancy: New: '||l_errmesscd_va.count
3894 ||' Baseline: '||l_mmerrmesscd_va.count;
3895 --
3896 elsif l_mmapperr_tot <> l_apperr_tot
3897 then
3898 --
3899 l_reptext_va.extend(1);
3900 l_reptext_en := l_reptext_en+1;
3901 l_reptext_va(l_reptext_en) := '----- Baseline Application Error total discrepancy: New: '||l_apperr_tot
3902 ||' Baseline: '||l_mmapperr_tot;
3903 --
3904 end if;
3905 --
3906 if l_apperrtypdiscr_messcd_va.count > 0
3907 then
3908 --
3909 for errvaen in l_apperrtypdiscr_messcd_va.first..l_apperrtypdiscr_messcd_va.last
3910 loop
3911 --
3912 l_reptext_va.extend(1);
3913 l_reptext_en := l_reptext_en+1;
3914 l_reptext_va(l_reptext_en) := '------ '||l_apperrtypdiscr_cnt_va(errvaen)
3915 ||' '||l_apperrtypdiscr_messcd_va(errvaen);
3916 --
3917 end loop;
3918 --
3919 if l_periddiscrep_errcd_va.count > 0
3920 then
3921 --
3922 l_reptext_va.extend(1);
3923 l_reptext_en := l_reptext_en+1;
3924 l_reptext_va(l_reptext_en) := '------- Person id discrepancies ';
3925 --
3926 for pervaen in l_periddiscrep_errcd_va.first..l_periddiscrep_errcd_va.last
3927 loop
3928 --
3929 l_reptext_va.extend(1);
3930 l_reptext_en := l_reptext_en+1;
3931 l_reptext_va(l_reptext_en) := '------- '||l_periddiscrep_errcd_va(pervaen)
3932 ||' '||l_periddiscrep_perid_va(pervaen);
3933 --
3934 end loop;
3935 --
3936 end if;
3937 --
3938 end if;
3939 --
3940 end if;
3941 --
3942 -- Check for rollup type discrepancies
3943 --
3944 if not l_env_errors
3945 then
3946 --
3947 if l_mmrltyp_rbvclcd_va.count > 0
3948 and l_clcode_va.count <> l_rbv_clcode_va.count
3949 then
3950 --
3951 l_reptext_va.extend(1);
3952 l_reptext_en := l_reptext_en+1;
3953 l_reptext_va(l_reptext_en) := '---- ';
3954 --
3955 -- Check for the first discrepancy
3956 --
3957 if not l_discrepancy
3958 then
3959 --
3960 l_reptext_va.extend(1);
3961 l_reptext_en := l_reptext_en+1;
3962 l_reptext_va(l_reptext_en) := '---- Baseline: ('||row.benefit_action_id||') '
3963 ||' Time: '||to_char(row.LAST_UPDATE_DATE,'DD-MON-YYYY-HH24-MI-SS');
3964 --
3965 end if;
3966 --
3967 l_discrepancy := true;
3968 --
3969 l_reptext_va.extend(1);
3970 l_reptext_en := l_reptext_en+1;
3971 l_reptext_va(l_reptext_en) := '----- Rollup type discrepancies '
3972 ||' New: '||l_clcode_va.count
3973 ||' Baseline: '||l_rbv_clcode_va.count;
3974 --
3975 l_mmapperrfilt_cnt := 0;
3976 l_mmledatafilt_cnt := 0;
3977 --
3978 for clvaen in l_mmrltyp_rbvclcd_va.first..l_mmrltyp_rbvclcd_va.last
3979 loop
3980 /*
3981 --
3982 -- Temporary
3983 --
3984 dbms_output.put_line('- BENTSTHN: Roll: '||l_mmrltyp_rbvclcd_va(clvaen)
3985 ||' OBFTID: '||row.benefit_action_id
3986 ||' NBFTID: '||l_newbft_id_va(vaen)
3987 );
3988 --
3989 */
3990 --
3991 ben_rollup_rbvs.get_rollup_code_combkey_va
3992 (p_rollup_code => l_mmrltyp_rbvclcd_va(clvaen)
3993 ,p_old_benefit_action_id => row.benefit_action_id
3994 ,p_new_benefit_action_id => l_newbft_id_va(vaen)
3995 --
3996 ,p_perid_va => l_mmperid_va
3997 ,p_perlud_va => l_mmperlud_va
3998 ,p_combnm_va => l_mmcombnm_va
3999 ,p_combnm2_va => l_mmcombnm2_va
4000 ,p_combnm3_va => l_mmcombnm3_va
4001 ,p_combnm4_va => l_mmcombnm4_va
4002 ,p_combid_va => l_mmcombid_va
4003 ,p_combid2_va => l_mmcombid2_va
4004 ,p_combid3_va => l_mmcombid3_va
4005 ,p_combid4_va => l_mmcombid4_va
4006 ,p_cnt_va => l_mmcnt_va
4007 );
4008 /*
4009 --
4010 -- Debugging
4011 --
4012 l_reptext_va.extend(1);
4013 l_reptext_en := l_reptext_en+1;
4014 l_reptext_va(l_reptext_en) := '----- Pre filt: '||l_mmrltyp_rbvclcd_va(clvaen)
4015 ||' l_mmperid_va.count: '||l_mmperid_va.count
4016 ||' l_mmcombid2_va.count: '||l_mmcombid2_va.count;
4017 --
4018 */
4019 --
4020 -- Filter out new application error person ids
4021 --
4022 if l_periddiscrep_perid_va.count > 0
4023 then
4024 --
4025 filter_discrepperids
4026 (p_perid_va => l_newapperr_peridva
4027 ,p_mmperid_va => l_mmperid_va
4028 ,p_mmperlud_va => l_mmperlud_va
4029 ,p_mmcombnm_va => l_mmcombnm_va
4030 ,p_mmcombnm2_va => l_mmcombnm2_va
4031 ,p_mmcombid_va => l_mmcombid_va
4032 ,p_mmcombid2_va => l_mmcombid2_va
4033 ,p_mmcnt_va => l_mmcnt_va
4034 ,p_exclperid_va => l_mmexclperid_nwva
4035 );
4036 --
4037 -- Filter out discrepancy application error person ids
4038 --
4039 filter_discrepperids
4040 (p_perid_va => l_periddiscrep_perid_va
4041 ,p_mmperid_va => l_mmperid_va
4042 ,p_mmperlud_va => l_mmperlud_va
4043 ,p_mmcombnm_va => l_mmcombnm_va
4044 ,p_mmcombnm2_va => l_mmcombnm2_va
4045 ,p_mmcombid_va => l_mmcombid_va
4046 ,p_mmcombid2_va => l_mmcombid2_va
4047 ,p_mmcnt_va => l_mmcnt_va
4048 ,p_exclperid_va => l_mmexclperid_va
4049 );
4050 --
4051 end if;
4052 --
4053 -- Filter out life event data changes
4054 --
4055 filter_ledatachgperids
4056 (p_lud => row.last_update_date
4057 ,p_mmperid_va => l_mmperid_va
4058 ,p_mmperlud_va => l_mmperlud_va
4059 ,p_mmcombnm_va => l_mmcombnm_va
4060 ,p_mmcombnm2_va => l_mmcombnm2_va
4061 ,p_mmcombid_va => l_mmcombid_va
4062 ,p_mmcombid2_va => l_mmcombid2_va
4063 ,p_mmcnt_va => l_mmcnt_va
4064 ,p_exclperid_va => l_mmexclperid_leva
4065 );
4066 /*
4067 --
4068 -- Debugging
4069 --
4070 l_reptext_va.extend(1);
4071 l_reptext_en := l_reptext_en+1;
4072 l_reptext_va(l_reptext_en) := '----- '||l_mmrltyp_rbvclcd_va(clvaen)
4073 ||' l_mmperid_va.count: '||l_mmperid_va.count
4074 ||' l_mmcombid2_va.count: '||l_mmcombid2_va.count;
4075 --
4076 */
4077 --
4078 -- Check if all perids were app error perids
4079 --
4080 if l_mmperid_va.count > 0
4081 then
4082 --
4083 l_reptext_va.extend(1);
4084 l_reptext_en := l_reptext_en+1;
4085 l_reptext_va(l_reptext_en) := '------ '||l_mmrltyp_rbvclcd_va(clvaen);
4086 --
4087 -- Check for app error exclusions
4088 --
4089 elsif l_mmexclperid_va.count > 0
4090 or l_mmexclperid_nwva.count > 0
4091 then
4092 --
4093 l_mmapperrfilt_cnt := l_mmapperrfilt_cnt+1;
4094 --
4095 --
4096 -- Check for app error exclusions
4097 --
4098 elsif l_mmexclperid_leva.count > 0
4099 then
4100 --
4101 l_mmledatafilt_cnt := l_mmledatafilt_cnt+1;
4102 --
4103 end if;
4104 --
4105 if l_mmperid_va.count > 0
4106 then
4107 --
4108 l_mmcombid_cnt := l_mmcombid_va.count;
4109 l_mmcombid2_cnt := l_mmcombid2_va.count;
4110 l_mmperlud_cnt := l_mmperlud_va.count;
4111 --
4112 for subvaen in l_mmperid_va.first..l_mmperid_va.last
4113 loop
4114 --
4115 -- Only display a maximum of 5 person ids
4116 --
4117 if subvaen > 5
4118 then
4119 --
4120 exit;
4121 --
4122 end if;
4123 --
4124 l_reptext := 'PERID: '||l_mmperid_va(subvaen);
4125 --
4126 l_reptext := l_reptext||' Cnt: '||l_mmcnt_va(subvaen);
4127 --
4128 if l_mmperlud_cnt > 0
4129 then
4130 --
4131 l_reptext := l_reptext||' LUD: '
4132 ||to_char(l_mmperlud_va(subvaen),'DD-MON-YYYY HH24-MI-SS');
4133
4134 --
4135 -- Check for a person data change
4136 --
4137 if l_mmperlud_va(subvaen) > row.last_update_date
4138 then
4139 --
4140 l_reptext := 'LE Change: '||l_reptext;
4141 --
4142 end if;
4143 --
4144 end if;
4145 --
4146 l_reptext := '-------- '||l_reptext;
4147 --
4148 l_reptext_va.extend(1);
4149 l_reptext_en := l_reptext_en+1;
4150 l_reptext_va(l_reptext_en) := substr(l_reptext,1,150);
4151 --
4152 l_reptext := null;
4153 --
4154 if l_mmcombid_cnt > 0
4155 and l_mmcombid_va(subvaen) is not null
4156 then
4157 --
4158 l_reptext := l_reptext||' '||l_mmcombnm_va(subvaen)||': '||l_mmcombid_va(subvaen);
4159
4160 --
4161 end if;
4162 --
4163 if l_mmcombid2_cnt > 0
4164 and l_mmcombid2_va(subvaen) is not null
4165 then
4166 --
4167 l_reptext := l_reptext||' '||l_mmcombnm2_va(subvaen)||': '||l_mmcombid2_va(subvaen);
4168
4169
4170 --
4171 end if;
4172 --
4173 if l_reptext is not null
4174 then
4175 --
4176 l_reptext := '--------- '||l_reptext;
4177 --
4178 l_reptext_va.extend(1);
4179 l_reptext_en := l_reptext_en+1;
4180 l_reptext_va(l_reptext_en) := substr(l_reptext,1,150);
4181 --
4182 end if;
4183 --
4184 end loop;
4185 --
4186 end if;
4187 --
4188 end loop;
4189 --
4190 -- Check for filtered discrepancy types
4191 --
4192 if l_mmapperrfilt_cnt > 0
4193 then
4194 --
4195 l_reptext_va.extend(1);
4196 l_reptext_en := l_reptext_en+1;
4197 l_reptext_va(l_reptext_en) := '------ Caused by app error discrepancies: '||l_mmapperrfilt_cnt;
4198 --
4199 elsif l_mmledatafilt_cnt > 0
4200 then
4201 --
4202 l_reptext_va.extend(1);
4203 l_reptext_en := l_reptext_en+1;
4204 l_reptext_va(l_reptext_en) := '------ Caused by life event data changes: '||l_mmledatafilt_cnt;
4205 --
4206 end if;
4207 --
4208 --
4209 -- Check for rollup count discrepancies
4210 --
4211 elsif l_mmclccode_va.count > 0
4212 then
4213 --
4214 l_reptext_va.extend(1);
4215 l_reptext_en := l_reptext_en+1;
4216 l_reptext_va(l_reptext_en) := '---- ';
4217 --
4218 -- Check for the first discrepancy
4219 --
4220 if not l_discrepancy
4221 then
4222 --
4223 l_reptext_va.extend(1);
4224 l_reptext_en := l_reptext_en+1;
4225 l_reptext_va(l_reptext_en) := '---- Baseline: ('||row.benefit_action_id||') '
4226 ||' Time: '||to_char(row.LAST_UPDATE_DATE,'DD-MON-YYYY-HH24-MI-SS');
4227 --
4228 end if;
4229 --
4230 l_discrepancy := true;
4231 --
4232 l_reptext_va.extend(1);
4233 l_reptext_en := l_reptext_en+1;
4234 l_reptext_va(l_reptext_en) := '----- Row count discrepancies: '||l_mmclccode_va.count;
4235 --
4236 l_mmapperrfilt_cnt := 0;
4237 l_mmledatafilt_cnt := 0;
4238 --
4239 for elenum in l_mmclccode_va.first..l_mmclccode_va.last
4240 loop
4241 --
4242 -- Don't flag bug count discrepancies
4243 --
4244 if instr(l_mmclccode_va(elenum),'WWBUGS') > 0
4245 then
4246 --
4247 null;
4248 --
4249 elsif l_mmoclcount_va(elenum) <> l_mmnclcount_va(elenum)
4250 then
4251 --
4252 ben_rollup_rbvs.get_rollup_code_combkey_va
4253 (p_rollup_code => l_mmclccode_va(elenum)
4254 ,p_old_benefit_action_id => row.benefit_action_id
4255 ,p_new_benefit_action_id => l_newbft_id_va(vaen)
4256 --
4257 ,p_perid_va => l_mmperid_va
4258 ,p_perlud_va => l_mmperlud_va
4259 ,p_combnm_va => l_mmcombnm_va
4260 ,p_combnm2_va => l_mmcombnm2_va
4261 ,p_combnm3_va => l_mmcombnm3_va
4262 ,p_combnm4_va => l_mmcombnm4_va
4263 ,p_combid_va => l_mmcombid_va
4264 ,p_combid2_va => l_mmcombid2_va
4265 ,p_combid3_va => l_mmcombid3_va
4266 ,p_combid4_va => l_mmcombid4_va
4267 ,p_cnt_va => l_mmcnt_va
4268 );
4269 /*
4270 --
4271 -- Debugging
4272 --
4273 l_reptext_va.extend(1);
4274 l_reptext_en := l_reptext_en+1;
4275 l_reptext_va(l_reptext_en) := '----- '||l_mmclccode_va(elenum)
4276 ||' l_mmperid_va.count: '||l_mmperid_va.count
4277 ||' cbid cnt: '||l_mmcombid_va.count
4278 ||' cbid2 cnt: '||l_mmcombid2_va.count
4279 ||' cbid3 cnt: '||l_mmcombid3_va.count
4280 ||' cbid4 cnt: '||l_mmcombid4_va.count;
4281 */
4282 /*
4283 dbms_output.put_line('----- '||l_mmclccode_va(elenum)
4284 ||' l_mmperid_va.count: '||l_mmperid_va.count
4285 ||' NwBFTID: '||row.benefit_action_id
4286 ||' OldBFTID: '||l_newbft_id_va(vaen)
4287 );
4288 --
4289 */
4290 --
4291 -- Filter out new application error person ids
4292 --
4293 if l_periddiscrep_perid_va.count > 0
4294 then
4295 --
4296 filter_discrepperids
4297 (p_perid_va => l_newapperr_peridva
4298 ,p_mmperid_va => l_mmperid_va
4299 ,p_mmperlud_va => l_mmperlud_va
4300 ,p_mmcombnm_va => l_mmcombnm_va
4301 ,p_mmcombnm2_va => l_mmcombnm2_va
4302 ,p_mmcombid_va => l_mmcombid_va
4303 ,p_mmcombid2_va => l_mmcombid2_va
4304 ,p_mmcnt_va => l_mmcnt_va
4305 ,p_exclperid_va => l_mmexclperid_nwva
4306 );
4307 --
4308 -- Filter out discrepancy application error person ids
4309 --
4310 filter_discrepperids
4311 (p_perid_va => l_periddiscrep_perid_va
4312 ,p_mmperid_va => l_mmperid_va
4313 ,p_mmperlud_va => l_mmperlud_va
4314 ,p_mmcombnm_va => l_mmcombnm_va
4315 ,p_mmcombnm2_va => l_mmcombnm2_va
4316 ,p_mmcombid_va => l_mmcombid_va
4317 ,p_mmcombid2_va => l_mmcombid2_va
4318 ,p_mmcnt_va => l_mmcnt_va
4319 ,p_exclperid_va => l_mmexclperid_va
4320 );
4321 --
4322 end if;
4323 /*
4324 --
4325 -- Debugging
4326 --
4327 l_reptext_va.extend(1);
4328 l_reptext_en := l_reptext_en+1;
4329 l_reptext_va(l_reptext_en) := '----- '||l_mmclccode_va(elenum)
4330 ||' l_mmperid_va.count: '||l_mmperid_va.count
4331 ||' exclperid cnt: '||l_mmexclperid_va.count
4332 ||' perdiscrep cnt: '||l_periddiscrep_perid_va.count;
4333 dbms_output.put_line('----- '||l_mmclccode_va(elenum)
4334 ||' l_mmperid_va.count: '||l_mmperid_va.count
4335 ||' NwBFTID: '||row.benefit_action_id
4336 ||' OldBFTID: '||l_newbft_id_va(vaen)
4337 );
4338 --
4339 */
4340 --
4341 -- Filter out life event data changes
4342 --
4343 filter_ledatachgperids
4344 (p_lud => row.last_update_date
4345 ,p_mmperid_va => l_mmperid_va
4346 ,p_mmperlud_va => l_mmperlud_va
4347 ,p_mmcombnm_va => l_mmcombnm_va
4348 ,p_mmcombnm2_va => l_mmcombnm2_va
4349 ,p_mmcombid_va => l_mmcombid_va
4350 ,p_mmcombid2_va => l_mmcombid2_va
4351 ,p_mmcnt_va => l_mmcnt_va
4352 ,p_exclperid_va => l_mmexclperid_leva
4353 );
4354 --
4355 -- Check if all perids were app error perids
4356 --
4357 if l_mmperid_va.count > 0
4358 then
4359 --
4360 l_reptext_va.extend(1);
4361 l_reptext_en := l_reptext_en+1;
4362 l_reptext_va(l_reptext_en) := '------- '||l_mmclccode_va(elenum)
4363 ||' New: '||l_mmnclcount_va(elenum)
4364 ||' Baseline: '||l_mmoclcount_va(elenum);
4365 --
4366 -- Check for app error exclusions
4367 --
4368 elsif l_mmexclperid_va.count > 0
4369 or l_mmexclperid_nwva.count > 0
4370 then
4371 --
4372 l_mmapperrfilt_cnt := l_mmapperrfilt_cnt+1;
4373 --
4374 --
4375 -- Check for app error exclusions
4376 --
4377 elsif l_mmexclperid_leva.count > 0
4378 then
4379 --
4380 l_mmledatafilt_cnt := l_mmledatafilt_cnt+1;
4381 --
4382 end if;
4383 --
4384 /*
4385 --
4386 -- Debugging
4387 --
4388 l_reptext_va.extend(1);
4389 l_reptext_en := l_reptext_en+1;
4390 l_reptext_va(l_reptext_en) := '------- '||l_mmclccode_va(elenum)
4391 ||' PERIDCnt: '||l_mmperid_va.count
4392 ||' CNmCnt: '||l_mmcombnm_va.count
4393 ||' CIdCnt: '||l_mmcombid_va.count
4394 ||' CNm2Cnt: '||l_mmcombnm_va.count
4395 ||' CId2Cnt: '||l_mmcombid_va.count;
4396 --
4397 */
4398 --
4399 if l_mmperid_va.count > 0
4400 then
4401 --
4402 l_mmcombid_cnt := l_mmcombid_va.count;
4403 l_mmperlud_cnt := l_mmperlud_va.count;
4404 l_mmcombid2_cnt := l_mmcombid2_va.count;
4405 --
4406 for subvaen in l_mmperid_va.first..l_mmperid_va.last
4407 loop
4408 --
4409 -- Only display a maximum of 5 person ids
4410 --
4411 if subvaen > 5
4412 then
4413 --
4414 exit;
4415 --
4416 end if;
4417 --
4418 l_reptext := 'PERID: '||l_mmperid_va(subvaen);
4419 --
4420 l_reptext := l_reptext||' Cnt: '||l_mmcnt_va(subvaen);
4421 --
4422 if l_mmperlud_cnt > 0
4423 then
4424 --
4425 l_reptext := l_reptext||' LUD: '
4426 ||to_char(l_mmperlud_va(subvaen),'DD-MON-YYYY HH24-MI-SS');
4427
4428 --
4429 -- Check for a person data change
4430 --
4431 if l_mmperlud_va(subvaen) > row.last_update_date
4432 then
4433 --
4434 l_reptext := 'LE Change: '||l_reptext;
4435 --
4436 end if;
4437 --
4438 end if;
4439 --
4440 l_reptext := '-------- '||l_reptext;
4441 --
4442 l_reptext_va.extend(1);
4443 l_reptext_en := l_reptext_en+1;
4444 l_reptext_va(l_reptext_en) := substr(l_reptext,1,150);
4445 --
4446 l_reptext := null;
4447 --
4448 if l_mmcombid_cnt > 0
4449 and l_mmcombid_va(subvaen) is not null
4450 then
4451 --
4452 l_reptext := l_reptext||' '||l_mmcombnm_va(subvaen)||': '||l_mmcombid_va(subvaen);
4453
4454 --
4455 end if;
4456 --
4457 if l_mmcombid2_cnt > 0
4458 and l_mmcombid2_va(subvaen) is not null
4459 then
4460 --
4461 l_reptext := l_reptext||' '||l_mmcombnm2_va(subvaen)||': '||l_mmcombid2_va(subvaen);
4462
4463
4464 --
4465 end if;
4466 --
4467 if l_reptext is not null
4468 then
4469 --
4470 l_reptext := '--------- '||l_reptext;
4471 --
4472 l_reptext_va.extend(1);
4473 l_reptext_en := l_reptext_en+1;
4474 l_reptext_va(l_reptext_en) := substr(l_reptext,1,150);
4475 --
4476 end if;
4477 --
4478 end loop;
4479
4480 end if;
4481 --
4482 end if;
4483 --
4484 end loop;
4485 --
4486 -- Check for filtered discrepancy types
4487 --
4488 if l_mmapperrfilt_cnt > 0
4489 then
4490 --
4491 l_reptext_va.extend(1);
4492 l_reptext_en := l_reptext_en+1;
4493 l_reptext_va(l_reptext_en) := '------ Caused by app error discrepancies: '||l_mmapperrfilt_cnt;
4494 --
4495 end if;
4496 --
4497 if l_mmledatafilt_cnt > 0
4498 then
4499 --
4500 l_reptext_va.extend(1);
4501 l_reptext_en := l_reptext_en+1;
4502 l_reptext_va(l_reptext_en) := '------ Caused by life event data changes: '||l_mmledatafilt_cnt;
4503 --
4504 end if;
4505 --
4506 end if;
4507 --
4508 end if;
4509 --
4510 l_baseline_cnt := l_baseline_cnt+1;
4511 --
4512 end loop;
4513 --
4514 end loop;
4515 /*
4516 --
4517 -- Display patching info
4518 --
4519 -- Get bug discrepancies
4520 --
4521 open c_prodbugs
4522 (c_basebft_id => l_newbft_id_va(l_newbft_id_va.last)
4523 ,c_bft_id => l_newbft_id_va(l_newbft_id_va.first)
4524 );
4525 fetch c_prodbugs BULK COLLECT INTO l_bugpscode_va, l_bugnum_va, l_buglud_va;
4526 close c_prodbugs;
4527 --
4528 if l_bugpscode_va.count > 0
4529 then
4530 --
4531 l_reptext_va.extend(1);
4532 l_reptext_en := l_reptext_en+1;
4533 l_reptext_va(l_reptext_en) := '----- ';
4534 --
4535 l_reptext_va.extend(1);
4536 l_reptext_en := l_reptext_en+1;
4537 l_reptext_va(l_reptext_en) := '----- Bugs applied: '||l_bugpscode_va.count;
4538 --
4539 l_reptext_va.extend(1);
4540 l_reptext_en := l_reptext_en+1;
4541 l_reptext_va(l_reptext_en) := '----- ';
4542 --
4543 for bugvaen in l_bugpscode_va.first..l_bugpscode_va.last
4544 loop
4545 --
4546 l_reptext_va.extend(1);
4547 l_reptext_en := l_reptext_en+1;
4548 l_reptext_va(l_reptext_en) := '------ '||l_bugpscode_va(bugvaen)
4549 ||' '||l_bugnum_va(bugvaen)
4550 ||' '||to_char(l_buglud_va(bugvaen),'DD-MON-YYYY-HH24-MI-SS');
4551 --
4552 end loop;
4553 --
4554 l_reptext_va.extend(1);
4555 l_reptext_en := l_reptext_en+1;
4556 l_reptext_va(l_reptext_en) := '----';
4557 --
4558 end if;
4559 */
4560 --
4561 -- Get BEN file discrepancies
4562 --
4563 open c_adfiles
4564 (c_basebft_id => l_newbft_id_va(l_newbft_id_va.last)
4565 ,c_bft_id => l_newbft_id_va(l_newbft_id_va.first)
4566 );
4567 fetch c_adfiles BULK COLLECT INTO l_adfname_va, l_adffilver_va,l_adflud_va;
4568 close c_adfiles;
4569 --
4570 if l_adfname_va.count > 0
4571 then
4572 --
4573 l_reptext_va.extend(1);
4574 l_reptext_en := l_reptext_en+1;
4575 l_reptext_va(l_reptext_en) := '----- Files applied: '||l_adfname_va.count;
4576 --
4577 for bugvaen in l_adfname_va.first..l_adfname_va.last
4578 loop
4579 --
4580 -- Only display a maximum of 100 files
4581 --
4582 if bugvaen > 200
4583 then
4584 --
4585 exit;
4586 --
4587 end if;
4588 --
4589 l_reptext_va.extend(1);
4590 l_reptext_en := l_reptext_en+1;
4591 l_reptext_va(l_reptext_en) := '------ '||l_adfname_va(bugvaen)
4592 ||' '||l_adffilver_va(bugvaen)
4593 ||' '||to_char(l_adflud_va(bugvaen),'DD-MON-YYYY-HH24-MI-SS');
4594 --
4595 end loop;
4596 --
4597 l_reptext_va.extend(1);
4598 l_reptext_en := l_reptext_en+1;
4599 l_reptext_va(l_reptext_en) := '----';
4600 --
4601 end if;
4602 --
4603 -- Populate ben_reporting
4604 --
4605 if l_reptext_va.count > 0 then
4606 --
4607 for repvaen in l_reptext_va.first..l_reptext_va.last
4608 loop
4609 --
4610 if fnd_global.conc_request_id <> -1
4611 then
4612 --
4613 fnd_file.put_line
4614 (which => fnd_file.log
4615 ,buff => l_reptext_va(repvaen)
4616 );
4617 --
4618 end if;
4619 --
4620 -- Copy all varray to single column varrays.
4621 --
4622 l_num1_col.extend(1);
4623 --
4624 select ben_reporting_s.nextval into
4625 l_num1_col(repvaen)
4626 from sys.dual;
4627 /*
4628 l_num1_col(l_count) :=
4629 g_report_table_object(l_count).reporting_id;
4630 */
4631 --
4632 -- Benefit action
4633 --
4634 l_num2_col.extend(1);
4635 l_num2_col(repvaen) := l_thnbft_id;
4636 --
4637 -- Thread
4638 --
4639 l_num3_col.extend(1);
4640 l_num3_col(repvaen) := 1;
4641 --
4642 -- Sequence
4643 --
4644 l_num4_col.extend(1);
4645 l_num4_col(repvaen) := repvaen;
4646 --
4647 -- Text
4648 --
4649 l_var1_col.extend(1);
4650 l_var1_col(repvaen) := l_reptext_va(repvaen);
4651 --
4652 -- OVN
4653 --
4654 l_num5_col.extend(1);
4655 l_num5_col(repvaen) := 1;
4656 --
4657 -- OVN
4658 --
4659 l_var2_col.extend(1);
4660 l_var2_col(repvaen) := null;
4661 l_var3_col.extend(1);
4662 l_var3_col(repvaen) := null;
4663 l_var4_col.extend(1);
4664 l_var4_col(repvaen) := null;
4665 l_num6_col.extend(1);
4666 l_num6_col(repvaen) := null;
4667 l_num7_col.extend(1);
4668 l_num7_col(repvaen) := null;
4669 l_num8_col.extend(1);
4670 l_num8_col(repvaen) := null;
4671 l_num9_col.extend(1);
4672 l_num9_col(repvaen) := null;
4673 l_num10_col.extend(1);
4674 l_num10_col(repvaen) := null;
4675 l_num11_col.extend(1);
4676 l_num11_col(repvaen) := null;
4677 l_num12_col.extend(1);
4678 l_num12_col(repvaen) := null;
4679 l_num13_col.extend(1);
4680 l_num13_col(repvaen) := null;
4681 l_num14_col.extend(1);
4682 l_num14_col(repvaen) := null;
4683 l_num15_col.extend(1);
4684 l_num15_col(repvaen) := null;
4685 l_num16_col.extend(1);
4686 l_num16_col(repvaen) := null;
4687 l_num17_col.extend(1);
4688 l_num17_col(repvaen) := null;
4689 l_num18_col.extend(1);
4690 l_num18_col(repvaen) := null;
4691 --
4692 end loop;
4693 --
4694 forall insvaen in l_num1_col.first..l_num1_col.last
4695 insert into ben_reporting
4696 (reporting_id,
4697 benefit_action_id,
4698 thread_id,
4699 sequence,
4700 text,
4701 object_version_number,
4702 rep_typ_cd,
4703 error_message_code,
4704 national_identifier,
4705 related_person_ler_id,
4706 temporal_ler_id,
4707 ler_id,
4708 person_id,
4709 pgm_id,
4710 pl_id,
4711 related_person_id,
4712 oipl_id,
4713 pl_typ_id,
4714 actl_prem_id,
4715 val,
4716 mo_num,
4717 yr_num)
4718 values
4719 (l_num1_col(insvaen),
4720 l_num2_col(insvaen),
4721 l_num3_col(insvaen),
4722 l_num4_col(insvaen),
4723 l_var1_col(insvaen),
4724 l_num5_col(insvaen),
4725 l_var2_col(insvaen),
4726 l_var3_col(insvaen),
4727 l_var4_col(insvaen),
4728 l_num6_col(insvaen),
4729 l_num7_col(insvaen),
4730 l_num8_col(insvaen),
4731 l_num9_col(insvaen),
4732 l_num10_col(insvaen),
4733 l_num11_col(insvaen),
4734 l_num12_col(insvaen),
4735 l_num13_col(insvaen),
4736 l_num14_col(insvaen),
4737 l_num15_col(insvaen),
4738 l_num16_col(insvaen),
4739 l_num17_col(insvaen),
4740 l_num18_col(insvaen));
4741 --
4742 end if;
4743 --
4744 end if;
4745 --
4746 END process;
4747 --
4748 procedure BFT_DispRepErrInfo
4749 (p_bft_id in number
4750 ,p_reperr_text in boolean
4751 ,p_disp_rows in number
4752 ,p_ext_rslt_id in number
4753 --
4754 ,p_dispout_va in out nocopy benutils.g_varchar2_table
4755 )
4756 is
4757 --
4758 l_dispout_va benutils.g_varchar2_table := benutils.g_varchar2_table();
4759 --
4760 l_errcode varchar2(2000);
4761 l_ele_num pls_integer;
4762 l_dispout_en pls_integer;
4763 l_ersltdet_count number;
4764 --
4765 cursor c_gerrcode
4766 (c_bft_id in number
4767 )
4768 is
4769 select rep.ERROR_MESSAGE_CODE
4770 from ben_reporting rep
4771 where rep.benefit_action_id = c_bft_id
4772 and rep.ERROR_MESSAGE_CODE is not null;
4773 --
4774 cursor c_gbenrepercdsum
4775 (c_bft_id in number
4776 )
4777 is
4778 select ERROR_MESSAGE_CODE,
4779 max(person_id) mx_perid,
4780 count(*) cnt
4781 from ben_reporting
4782 where benefit_action_id = c_bft_id
4783 and ERROR_MESSAGE_CODE is not null
4784 group by ERROR_MESSAGE_CODE
4785 order by count(*) desc;
4786 --
4787 cursor c_gbenrepdets
4788 (c_bft_id in number
4789 )
4790 is
4791 select rep.text,
4792 rep.thread_id,
4793 rep.person_id,
4794 rep.last_update_login
4795 from ben_reporting rep
4796 where rep.benefit_action_id = c_bft_id
4797 order by thread_id, reporting_id;
4798 --
4799 cursor c_ersltdetcnt
4800 (c_ext_rslt_id number
4801 )
4802 is
4803 select count(*)
4804 from BEN_EXT_RSLT_DTL
4805 where EXT_RSLT_ID = c_ext_rslt_id;
4806 --
4807 cursor c_erslterrsum
4808 (c_ext_rslt_id number
4809 )
4810 is
4811 select TYP_CD,
4812 ERR_NUM,
4813 count(*) cnt
4814 from BEN_EXT_RSLT_ERR
4815 where EXT_RSLT_ID = c_ext_rslt_id
4816 group by TYP_CD,
4817 ERR_NUM
4818 order by count(*) desc;
4819 --
4820 cursor c_erslterrdet
4821 (c_ext_rslt_id number
4822 ,c_typ_cd varchar2
4823 )
4824 is
4825 select ERR_NUM,
4826 ERR_TXT,
4827 count(*) cnt
4828 from BEN_EXT_RSLT_ERR
4829 where EXT_RSLT_ID = c_ext_rslt_id
4830 and TYP_CD = c_typ_cd
4831 group by ERR_NUM,
4832 ERR_TXT
4833 order by count(*) desc;
4834 --
4835 begin
4836 --
4837 l_dispout_en := 1;
4838 l_dispout_va.extend(1);
4839 l_dispout_va(l_dispout_en) := '-- Error code summary: ';
4840 --
4841 -- Get error code
4842 --
4843 for codesum in c_gbenrepercdsum
4844 (c_bft_id => p_bft_id
4845 )
4846 loop
4847 --
4848 l_dispout_va.extend(1);
4849 l_dispout_en := l_dispout_en+1;
4850 l_dispout_va(l_dispout_en) := codesum.cnt||' '||codesum.ERROR_MESSAGE_CODE||' '||codesum.mx_perid;
4851 --
4852 end loop;
4853 --
4854 -- Get reporting info
4855 --
4856 if p_reperr_text
4857 then
4858 --
4859 l_dispout_va.extend(1);
4860 l_dispout_en := l_dispout_en+1;
4861 l_dispout_va(l_dispout_en) := 'Reporting lines ';
4862 --
4863 l_ele_num := 0;
4864 --
4865 for row_rep in c_gbenrepdets(p_bft_id)
4866 loop
4867 --
4868 if l_ele_num > p_disp_rows
4869 then
4870 --
4871 exit;
4872 --
4873 end if;
4874 --
4875 l_dispout_va.extend(1);
4876 l_dispout_en := l_dispout_en+1;
4877 l_dispout_va(l_dispout_en) := row_rep.thread_id||' '||substr(row_rep.text,1,250);
4878 --
4879 l_ele_num := l_ele_num+1;
4880 --
4881 end loop;
4882 --
4883 end if;
4884 --
4885 -- Check for an extract BFT
4886 --
4887 if p_ext_rslt_id is not null
4888 then
4889 --
4890 open c_ersltdetcnt
4891 (c_ext_rslt_id => p_ext_rslt_id
4892 );
4893 fetch c_ersltdetcnt into l_ersltdet_count;
4894 close c_ersltdetcnt;
4895 --
4896 l_dispout_va.extend(1);
4897 l_dispout_en := l_dispout_en+1;
4898 l_dispout_va(l_dispout_en) := ' Ex RSLT Det Cnt: '||l_ersltdet_count;
4899 --
4900 -- Extract error summary
4901 --
4902 for exterr in c_erslterrsum
4903 (c_ext_rslt_id => p_ext_rslt_id
4904 )
4905 loop
4906 --
4907 l_dispout_va.extend(1);
4908 l_dispout_en := l_dispout_en+1;
4909 l_dispout_va(l_dispout_en) := ' - Err Typ: '||exterr.TYP_CD
4910 ||' Err Num: '||exterr.ERR_NUM
4911 ||' Err Cnt: '||exterr.cnt;
4912 --
4913 if exterr.TYP_CD = 'F'
4914 then
4915 --
4916 for fatexterr in c_erslterrdet
4917 (c_ext_rslt_id => p_ext_rslt_id
4918 ,c_typ_cd => exterr.TYP_CD
4919 )
4920 loop
4921 --
4922 l_dispout_va.extend(1);
4923 l_dispout_en := l_dispout_en+1;
4924 l_dispout_va(l_dispout_en) := ' -- Num: '||fatexterr.ERR_NUM
4925 ||' '||substr(fatexterr.ERR_TXT,1,60);
4926 --
4927 end loop;
4928 --
4929 end if;
4930 --
4931 end loop;
4932 --
4933 end if;
4934 --
4935 p_dispout_va := l_dispout_va;
4936 --
4937 end BFT_DispRepErrInfo;
4938 --
4939 end ben_test_harness;