[Home] [Help]
PACKAGE BODY: APPS.BEN_EFC_REPORTING
Source
1 package body ben_efc_reporting as
2 /* $Header: beefcrep.pkb 120.0 2005/05/28 02:08:31 appldev noship $ */
3 --
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 History
12 Version Date Author Comments
13 --------- --------- ---------- --------------------------------------------
14 115.0 13-Aug-01 mhoyes Created.
15 115.1 17-Aug-01 mhoyes Enhanced for BEN July patch.
16 115.2 27-Aug-01 mhoyes Enhanced for BEN July patch.
17 115.3 31-Aug-01 mhoyes Enhanced for BEN July patch.
18 115.4 02-Oct-01 mhoyes Enhanced for BEN F patchset.
19 115.8 04-Jan-02 mhoyes Enhanced for BEN G patchset.
20 -----------------------------------------------------------------------------
21 */
22 g_package varchar2(33) := ' ben_efc_reporting.'; -- Global package name
23 --
24 -- Define globals
25 --
26 g_start_time pls_integer;
27 g_start_gets pls_integer;
28 g_start_phygets pls_integer;
29 g_start_ftss pls_integer;
30 g_start_rwprcs pls_integer;
31 g_start_pgamem pls_integer;
32 --
33 procedure DisplayEFCInfo
34 (p_ent_scode in varchar2
35 ,p_efc_action_id in number default null
36 --
37 ,p_disp_private in boolean default false
38 ,p_disp_succeeds in boolean default false
39 ,p_disp_exclusions in boolean default false
40 --
41 ,p_adjustment_counts in ben_efc_adjustments.g_adjustment_counts
42 ,p_rcoerr_val_set in ben_efc_adjustments.g_rcoerr_values_tbl
43 ,p_failed_adj_val_set in ben_efc_adjustments.g_failed_adj_values_tbl
44 ,p_fatal_error_val_set in ben_efc_adjustments.g_failed_adj_values_tbl
45 ,p_success_val_set in ben_efc_adjustments.g_failed_adj_values_tbl
46 )
47 is
48 --
49 l_proc varchar2(80)
50 := g_package||'DisplayEFCInfo';
51 --
52 -- PLSQL types
53 --
54 Type UniqueVals is record
55 (unqval varchar2(1000)
56 ,unqval1 varchar2(1000)
57 ,esd date
58 ,eed date
59 ,count number
60 ,mncredt date
61 ,mxcredt date
62 ,percentage number
63 );
64 --
65 type UnqVal_set is table of UniqueVals index by binary_integer;
66 --
67 l_fatal_error_val_set ben_efc_adjustments.g_failed_adj_values_tbl;
68 l_tmp_set ben_efc_adjustments.g_failed_adj_values_tbl;
69 l_success_val_set ben_efc_adjustments.g_failed_adj_values_tbl;
70 l_dup_success_val_set ben_efc_adjustments.g_failed_adj_values_tbl;
71 l_unq_success_val_set ben_efc_adjustments.g_failed_adj_values_tbl;
72 l_dup_excl_val_set ben_efc_adjustments.g_failed_adj_values_tbl;
73 l_unq_excl_val_set ben_efc_adjustments.g_failed_adj_values_tbl;
74 l_unqtype_set UnqVal_set;
75 l_unqval_set UnqVal_set;
76 l_dupval_set UnqVal_set;
77 --
78 l_exclude_perc number;
79 l_faterrs_perc number;
80 l_perc number;
81 --
82 l_unqele_num pls_integer;
83 --
84 l_found boolean;
85 --
86 l_succ_count pls_integer;
87 l_succ_perc pls_integer;
88 l_dupsuccval_count pls_integer;
89 l_rco_count pls_integer;
90 l_rco_perc pls_integer;
91 l_fail_count pls_integer;
92 l_fail_perc pls_integer;
93 l_excl_count pls_integer;
94 l_excl_perc pls_integer;
95 l_dupexclval_count pls_integer;
96 l_tabrow_count pls_integer;
97 l_conv_count pls_integer;
98 l_adjust_count pls_integer;
99 --
100 l_hv pls_integer;
101 l_dupele_num pls_integer;
102 l_public_count pls_integer;
103 l_ent_desc varchar2(100);
104 l_disp_str long;
105 --
106 l_enddays pls_integer;
107 --
108 function get_description
109 (p_label in varchar2
110 ) return varchar2
111 is
112
113 begin
114 --
115 -- Categories
116 --
117 if p_label = 'OBSOLETEDATA' then
118 --
119 return 'Obsolete Data';
120 --
121 elsif p_label = 'DELETEDINFO' then
122 --
123 return 'Deleted Data';
124 --
125 elsif p_label = 'CORRECTEDINFO' then
126 --
127 return 'Corrected Data';
128 --
129 elsif p_label = 'VALIDEXCLUSION' then
130 --
131 return 'Valid Exclusion';
132 --
133 elsif p_label = 'DATACORRUPT' then
134 --
135 return 'Corrupted Data';
136 --
137 elsif p_label = 'MISSINGSETUP' then
138 --
139 return 'Missing Setup Data';
140 --
141 -- Exclusion
142 --
143 elsif p_label = 'OLDDATA12MTHS' then
144 --
145 return 'Data created more than 12 months ago';
146 --
147 elsif p_label = 'OLDDATA10MTHS' then
148 --
149 return 'Data created more than 10 months ago';
150 --
151 elsif p_label = 'NULLCREDT' then
152 --
153 return 'No Who trigger creation date';
154 --
155 elsif p_label = 'INVOVNTRIG' then
156 --
157 return 'The Who trigger information has been modified but the object version '
158 ||' number has not. ';
159 --
160 elsif p_label = 'NOPILPERSON' then
161 --
162 return 'NOPILPERSON: The person associated with the life event has been deleted. '
163 ||'An EFC adjustment cannot be performed. ';
164 --
165 elsif p_label = 'NOPILLEODPRVPEN' then
166 --
167 return 'NOPILLEODPRVPEN: The enrolment result associated with the participant rate '
168 ||' value begins after the rate start date for the participant rate value. '
169 ||'An EFC adjustment cannot be performed. ';
170 --
171 elsif p_label = 'NOEPEDETS' then
172 --
173 return fnd_message.get_string('BEN','BEN_92771_EFC_EPENOEXIST');
174 --
175 /*
176
177 return 'NOEPEDETS: The electable choice which is being used for processing '
178 ||' does not exist. An EFC adjustment cannot be performed. ';
179 */
180 --
181 elsif p_label = 'ABRCORR' then
182 --
183 return fnd_message.get_string('BEN','BEN_92754_CORR_ECRABR');
184 --
185 /*
186 return 'Corrected activity base rate';
187 --
188 */
189 elsif p_label = 'VPFCORR' then
190 --
191 return fnd_message.get_string('BEN','BEN_92755_CORR_VPF');
192 /*
193 return 'Corrected variable rate profile';
194 */
195 --
196 elsif p_label = 'CCMDTCORR' then
197 --
198 return fnd_message.get_string('BEN','BEN_92760_EFC_CORR_CCM');
199 /*
200 return 'Corrected coverage calculation method';
201 --
202 */
203 --
204 elsif p_label = 'CCMDTCORR' then
205 --
206 return fnd_message.get_string('BEN','BEN_92760_EFC_CORR_CCM');
207 /*
208 return 'Corrected coverage calculation method';
209 --
210 */
211 elsif p_label = 'PRVMODIFIED' then
212 --
213 return 'Modified participant rate value';
214 --
215 elsif p_label = 'PRVMODIFIED' then
216 --
217 return 'End dated participant rate value';
218 --
219 elsif p_label = 'NODTCOP' then
220 --
221 return fnd_message.get_string('BEN','BEN_92761_EFC_NO_COP');
222 --
223 /*
224 return 'Deleted option in plan';
225 --
226 */
227 elsif p_label = 'NODTPRVABR' then
228 --
229 return fnd_message.get_string('BEN','BEN_92753_NO_PRVABR_EXISTS');
230 /*
231 return 'The activity base rate attached to the participant rate value has'
232 ||' been deleted. ';
233 */
234 --
235 elsif p_label = 'NODTAPR' then
236 --
237 return fnd_message.get_string('BEN','BEN_92759_EFC_NO_APR');
238 --
239 /*
240 return 'Deleted actual premium';
241 --
242 */
243 elsif p_label = 'NODTVPFABR' then
244 --
245 return 'Detached vapro from activity base rate';
246 --
247 elsif p_label = 'NOPEN' then
248 --
249 return fnd_message.get_string('BEN','BEN_92752_NO_PRVPEN_EXISTS');
250 /*
251 return 'Deleted enrolment result';
252 */
253 --
254 elsif p_label = 'CCMFLFX' then
255 --
256 return 'Flat amount coverage calculation method';
257 --
258 elsif p_label = 'ABRMCFLFX' then
259 --
260 return 'Flat amount activity base rate';
261 --
262 elsif p_label = 'ECRMCFLFX' then
263 --
264 return 'Flat amount enrolment rate';
265 --
266 elsif p_label = 'ABREVAEFLGY' then
267 --
268 return 'Enter value at enrolment activity base rate';
269 --
270 elsif p_label = 'ECRAOEFLGN' then
271 --
272 return 'ECRAOEFLGN: Assign on enrolment rate';
273 --
274 elsif p_label = 'NOPENEPEECR' then
275 --
276 return 'NOPENEPEECR: No enrolment rate exists for the elecatble choice. '
277 ||'This must exist to perform an EFC adjustment. ';
278 --
279 elsif p_label = 'PRVENDDATED' then
280 --
281 return 'PRVENDDATED: The participant rate value rate end date has been populated. '
282 ||'An EFC adjustment cannot be performed. ';
283 --
284 elsif p_label = 'ABRMCNULL' then
285 --
286 return fnd_message.get_string('BEN','BEN_92769_EFC_ABRMC_NULL');
287 /*
288 return 'ABRMCNULL: The rate multiplier code for the activity base rate is null. '
289 ||'This must be set to perform an EFC adjustment. ';
290 */
291 --
292 elsif p_label = 'ABRVALNULLEVAEFN' then
293 --
294 return fnd_message.get_string('BEN','BEN_92770_EFC_ABRVAL_NULL');
295 /*
296 return 'ABRVALNULLEVAEFN: The activity base rate value is null. This must be set '
297 ||'when the value is not being entered at enrolment during an EFC adjustment. ';
298 */
299 --
300 elsif p_label = 'CHILDABRVALNULLEVAEFN' then
301 --
302 return 'CHILDABRVALNULLEVAEFN: The child activity base rate value is null. This must be set '
303 ||'when the value is not being entered at enrolment during an EFC adjustment. ';
304 --
305 elsif p_label = 'SQLPLUSELEPRVCORR' then
306 --
307 return 'Data modified via SQL*Plus';
308 --
309 elsif p_label = 'VOIDBACKPIL' then
310 --
311 return 'Backed out or voided life event';
312 --
313 elsif p_label = 'BACKVOIDPEN' then
314 --
315 return 'Backed out or voided enrolment result';
316 --
317 elsif p_label = 'PRVFLFX' then
318 --
319 return 'Flat amount participant rate value';
320 --
321 elsif p_label = 'NOASGPAY' then
322 --
323 return fnd_message.get_string('BEN','BEN_92751_NO_PRTT_ASG_PAY');
324 /*
325 return 'No payroll is defined for the assignment';
326 */
327 --
328 elsif p_label = 'NOASGPBB' then
329 --
330 return fnd_message.get_string('BEN','BEN_92757_NO_ASG_PAYBASIS');
331 /*
332 return 'No pay basis is defined for the assignment';
333 */
334 --
335 elsif p_label in ('NOASGPPP','NOSTSALSTCOMP') then
336 --
337 return fnd_message.get_string('BEN','BEN_92758_NO_ASG_SALARY');
338 /*
339 return 'No pay proposal is defined for the assignment';
340 */
341 --
342 elsif p_label = 'NOPAYPTPNXMTH' then
343 --
344 return fnd_message.get_string('BEN','BEN_92750_NO_FUT_PAY_PPP');
345 /*
346 return 'No payroll period exists for rate start date plus one month of the payroll';
347 --
348 */
349 elsif p_label = 'NODTPBB' then
350 --
351 return 'No person benefit balance exists for the benefit balance';
352 --
353 elsif p_label = 'APRNONMONUOM' then
354 --
355 return 'Non NCU currency for actual premium';
356 --
357 elsif p_label = 'ENBPOINTSUOM' then
358 --
359 return 'Points unit of measure for enrolment coverage';
360 --
361 elsif p_label = 'VPFPOINTSUOM' then
362 --
363 return 'Points unit of measure for variable rate profile';
364 --
365 elsif p_label = 'PGMPOINTSUOM' then
366 --
367 return 'Points unit of measure for program';
368 --
369 /*
370 elsif p_label = 'ENBMODS' then
371 --
372 return 'Enrolment coverage has been modified since it was created';
373 --
374 */
375 elsif p_label = 'NULLUOM' then
376 --
377 return fnd_message.get_string('BEN','BEN_92762_EFC_NULL_UOM');
378 --
379 /*
380 return 'The UOM to be used for conversion is null.';
381 --
382 */
383 elsif p_label = 'EUROUOM' then
384 --
385 return 'The UOM to be used for conversion is in Euros.';
386 --
387 elsif p_label = 'ABRAPREUROUOM' then
388 --
389 return 'The UOM from the actual premium which is attached to the activity '
390 ||' base rate to be used for conversion is in Euros.';
391 --
392 elsif p_label = 'ABRNONMONUOM' then
393 --
394 return 'Non monetary unit of measure for the activity base rate.';
395 --
396 elsif p_label = 'VPFABRNONMONUOM' then
397 --
398 return 'Non monetary unit of measure for the activity base rate of the variable '
399 ||' rate profile.';
400 --
401 elsif p_label = 'NOPLNCCMCVGMC' then
402 --
403 return fnd_message.get_string('BEN','BEN_92741_NO_PLN_CCM_ATTACH');
404 /*
405 return 'No coverage calculation method is attached to the plan for coverage activity base rate. ';
406 */
407 --
408 elsif p_label = 'NULLENBIDCVGMC' then
409 --
410 return 'The coverage is null for an activity base rate. ';
411 --
412 elsif p_label = 'NULLCOMP' then
413 --
414 return 'The compensation is null for an activity base rate. ';
415 --
416 elsif p_label = 'NOPRVABRDTIPV' then
417 --
418 return fnd_message.get_string('BEN','BEN_92763_EFC_NO_ABR_IPV');
419 /*
420 return 'No input value exists for the element type which is attached to '
421 ||' the activity base rate.';
422 */
423 --
424 else
425 --
426 return nvl(p_label,'ZZZZZZZZZZZZZZZZZZZZ');
427 --
428 end if;
429 --
430 end;
431 --
432 begin
433 --
434 if p_ent_scode = 'PEP' then
435 --
436 l_ent_desc := 'Eligibility';
437 --
438 elsif p_ent_scode = 'EPO' then
439 --
440 l_ent_desc := 'Eligibility option';
441 --
442 elsif p_ent_scode = 'ENB' then
443 --
444 l_ent_desc := 'Coverage';
445 --
446 elsif p_ent_scode = 'EPR' then
447 --
448 l_ent_desc := 'Enrolment premium';
449 --
450 elsif p_ent_scode = 'ECR' then
451 --
452 l_ent_desc := 'Enrolment rate';
453 --
454 elsif p_ent_scode = 'PRV' then
455 --
456 l_ent_desc := 'Participant rate value';
457 --
458 elsif p_ent_scode = 'EEV' then
459 --
460 l_ent_desc := 'Element entry value';
461 --
462 elsif p_ent_scode = 'BPL' then
463 --
464 l_ent_desc := 'Benefit provider ledger';
465 --
466 end if;
467 --
468 hr_efc_info.insert_line('-- ');
469 hr_efc_info.insert_line('-- '||l_ent_desc||' details ');
470 hr_efc_info.insert_line('-- ');
471 --
472 l_fatal_error_val_set := p_fatal_error_val_set;
473 --
474 l_rco_count := p_rcoerr_val_set.count;
475 l_fail_count := p_failed_adj_val_set.count;
476 --
477 l_tabrow_count := p_adjustment_counts.tabrow_count;
478 l_conv_count := p_adjustment_counts.actconv_count;
479 --
480 if p_success_val_set.count > 0 then
481 --
482 l_dupval_set.delete;
483 l_dupele_num := 0;
484 l_dup_success_val_set.delete;
485 l_unq_success_val_set.delete;
486 l_unqele_num := 0;
487 --
488 for ele_num in p_success_val_set.first ..
489 p_success_val_set.last
490 loop
491 --
492 -- Note: Assigned to variable to avoid PLSQL error when adding
493 -- in hv calculation for hr_api.g_eot-hr_api.g_sot.
494 --
495 l_enddays := nvl(p_success_val_set(ele_num).eed,hr_api.g_eot)-hr_api.g_sot;
496 --
497 l_hv := mod(p_success_val_set(ele_num).id,ben_hash_utility.get_hash_key)
498 +nvl(p_success_val_set(ele_num).esd,hr_api.g_eot)-hr_api.g_sot
499 +l_enddays
500 ;
501 --
502 if l_hv is null then
503 --
504 l_dup_success_val_set(l_dupele_num) := p_success_val_set(ele_num);
505 l_dupele_num := l_dupele_num+1;
506 --
507 elsif l_dupval_set.exists(l_hv) then
508 --
509 if (l_dupval_set(l_hv).unqval = p_success_val_set(ele_num).id
510 and nvl(l_dupval_set(l_hv).esd,hr_api.g_eot) = nvl(p_success_val_set(ele_num).esd,hr_api.g_eot)
511 and nvl(l_dupval_set(l_hv).eed,hr_api.g_eot) = nvl(p_success_val_set(ele_num).eed,hr_api.g_eot)
512 )
513 or p_success_val_set(ele_num).id is null
514 then
515 --
516 l_dup_success_val_set(l_dupele_num) := p_success_val_set(ele_num);
517 l_dupele_num := l_dupele_num+1;
518 --
519 end if;
520 --
521 else
522 --
523 l_dupval_set(l_hv).unqval := p_success_val_set(ele_num).id;
524 l_dupval_set(l_hv).esd := p_success_val_set(ele_num).esd;
525 l_dupval_set(l_hv).eed := p_success_val_set(ele_num).eed;
526 l_unq_success_val_set(l_unqele_num) := p_success_val_set(ele_num);
527 l_unqele_num := l_unqele_num+1;
528 --
529 end if;
530 --
531 end loop;
532 --
533 end if;
534 --
535 l_dupsuccval_count := l_dup_success_val_set.count;
536 l_success_val_set := l_unq_success_val_set;
537 --
538 if l_fatal_error_val_set.count > 0 then
539 --
540 l_dupval_set.delete;
541 l_dup_excl_val_set.delete;
542 l_unq_excl_val_set.delete;
543 l_dupele_num := 0;
544 l_unqele_num := 0;
545 --
546 for ele_num in l_fatal_error_val_set.first ..
547 l_fatal_error_val_set.last
548 loop
549 --
550 l_enddays := nvl(l_fatal_error_val_set(ele_num).eed,hr_api.g_eot)-hr_api.g_sot;
551 --
552 l_hv := mod(l_fatal_error_val_set(ele_num).id,ben_hash_utility.get_hash_key)
553 +nvl(l_fatal_error_val_set(ele_num).esd,hr_api.g_eot)-hr_api.g_sot
554 +l_enddays
555 ;
556 --
557 if l_hv is null then
558 --
559 l_dup_excl_val_set(l_dupele_num) := l_fatal_error_val_set(ele_num);
560 l_dupele_num := l_dupele_num+1;
561 --
562 elsif l_dupval_set.exists(l_hv) then
563 --
564 if (l_dupval_set(l_hv).unqval = l_fatal_error_val_set(ele_num).id
565 and nvl(l_dupval_set(l_hv).esd,hr_api.g_eot) = nvl(l_fatal_error_val_set(ele_num).esd,hr_api.g_eot)
566 and nvl(l_dupval_set(l_hv).eed,hr_api.g_eot) = nvl(l_fatal_error_val_set(ele_num).eed,hr_api.g_eot)
567 )
568 or l_fatal_error_val_set(ele_num).id is null
569 then
570 --
571 l_dup_excl_val_set(l_dupele_num) := l_fatal_error_val_set(ele_num);
572 l_dupele_num := l_dupele_num+1;
573 --
574 end if;
575 --
576 else
577 --
578 l_dupval_set(l_hv).unqval := l_fatal_error_val_set(ele_num).id;
579 l_dupval_set(l_hv).esd := l_fatal_error_val_set(ele_num).esd;
580 l_dupval_set(l_hv).eed := l_fatal_error_val_set(ele_num).eed;
581 l_unq_excl_val_set(l_unqele_num) := l_fatal_error_val_set(ele_num);
582 l_unqele_num := l_unqele_num+1;
583 --
584 end if;
585 --
586 end loop;
587 --
588 -- Exclude private exclusions
589 --
590 if not p_disp_private then
591 --
592 l_tmp_set := l_unq_excl_val_set;
593 l_unq_excl_val_set.delete;
594 l_public_count := 0;
595 --
596 for elenum in l_tmp_set.first..l_tmp_set.last
597 loop
598 --
599 if l_tmp_set(elenum).faterr_type not in ('VALIDEXCLUSION'
600 ,'OBSOLETEDATA'
601 ,'UNSUPPORTTRANS'
602 ,'POTENTIALCODEBUG'
603 ,'FIXEDCODEBUG'
604 ,'CODECHANGE'
605 ,'ADJUSTBUG'
606 )
607 then
608 --
609 l_unq_excl_val_set(l_public_count) := l_tmp_set(elenum);
610 l_public_count := l_public_count+1;
611 --
612 end if;
613 --
614 end loop;
615 --
616 end if;
617 --
618 end if;
619 --
620 l_dupexclval_count := l_dup_excl_val_set.count;
621 l_fatal_error_val_set := l_unq_excl_val_set;
622 --
623 l_succ_count := l_success_val_set.count;
624 l_excl_count := l_fatal_error_val_set.count;
625 l_adjust_count := l_succ_count+l_rco_count+l_fail_count+l_excl_count;
626 --
627 if l_tabrow_count > 0 then
628 --
629 if l_succ_count > 0 then
630 --
631 l_succ_perc := (l_succ_count/l_adjust_count)*100;
632 --
633 else
634 --
635 l_succ_perc := 0;
636 --
637 end if;
638 --
639 if l_rco_count > 0 then
640 --
641 l_rco_perc := (l_rco_count/l_adjust_count)*100;
642 --
643 else
644 --
645 l_rco_perc := 0;
646 --
647 end if;
648 --
649 if l_fail_count > 0 then
650 --
651 l_fail_perc := (l_fail_count/l_adjust_count)*100;
652 --
653 else
654 --
655 l_fail_perc := 0;
656 --
657 end if;
658 --
659 if l_excl_count > 0 then
660 --
661 l_excl_perc := (l_excl_count/l_adjust_count)*100;
662 --
663 else
664 --
665 l_excl_perc := 0;
666 --
667 end if;
668 --
669 if p_disp_private then
670 --
671 hr_efc_info.insert_line('-- Convertable rows: '||l_tabrow_count);
672 hr_efc_info.insert_line('-- Converted rows: '||l_conv_count);
673 hr_efc_info.insert_line('-- Adjustable rows: '||l_adjust_count);
674 hr_efc_info.insert_line('-- ');
675 --
676 end if;
677 --
678 hr_efc_info.insert_line('-- Successfully Adjusted rows: '||l_succ_count||' '||l_succ_perc||'% ');
679 --
680 if p_disp_private then
681 --
682 hr_efc_info.insert_line('-- Duplicate Succ rows: '||l_dupsuccval_count);
683 --
684 end if;
685 --
686 hr_efc_info.insert_line('-- Application errors: '||l_rco_count||' '||l_rco_perc||'% ');
687 hr_efc_info.insert_line('-- Non Excluded failure rows: '||l_fail_count||' '||l_fail_perc||'% ');
688 hr_efc_info.insert_line('-- Exclusion rows: '||l_excl_count||' '||l_excl_perc||'% ');
689 --
690 if p_disp_private then
691 --
692 hr_efc_info.insert_line('-- Duplicate Exclusion rows: '||l_dupexclval_count);
693 --
694 end if;
695 --
696 hr_efc_info.insert_line('-- ');
697 --
698 end if;
699 --
700 if p_rcoerr_val_set.count > 0 then
701 --
702 hr_efc_info.insert_line('-- '||p_rcoerr_val_set.count||' Application errors ');
703 hr_efc_info.insert_line('-- ');
704 --
705 for errele_num in p_rcoerr_val_set.first ..
706 p_rcoerr_val_set.last
707 loop
708 --
709 hr_efc_info.insert_line(p_rcoerr_val_set(errele_num).id
710 ||' '||p_rcoerr_val_set(errele_num).esd
711 ||' '||p_rcoerr_val_set(errele_num).eed
712 ||' '||p_rcoerr_val_set(errele_num).bgp_id
713 ||' '||p_rcoerr_val_set(errele_num).rco_name
714 ||' '||to_char(p_rcoerr_val_set(errele_num).lud,'DD-MON-YYYY')
715 );
716 --
717 hr_efc_info.insert_line(' '||substr(p_rcoerr_val_set(errele_num).sql_error,1,240)
718 );
719 --
720 end loop;
721 hr_efc_info.insert_line('-- ');
722 --
723 end if;
724 --
725 if p_failed_adj_val_set.count > 0 then
726 --
727 hr_efc_info.insert_line('-- '||p_failed_adj_val_set.count||' Adjustment failures ');
728 hr_efc_info.insert_line('-- ');
729 --
730 for errele_num in p_failed_adj_val_set.first ..
731 p_failed_adj_val_set.last
732 loop
733 --
734 hr_efc_info.insert_line(p_failed_adj_val_set(errele_num).val_type
735 ||' '||p_failed_adj_val_set(errele_num).id
736 ||' '||p_failed_adj_val_set(errele_num).esd
737 ||' '||p_failed_adj_val_set(errele_num).eed
738 ||' '||p_failed_adj_val_set(errele_num).bgp_id
739 ||' '||nvl(to_char(p_failed_adj_val_set(errele_num).old_val1),'N ')
740 ||' '||nvl(to_char(p_failed_adj_val_set(errele_num).new_val1),'N ')
741 ||' '||nvl(to_char(p_failed_adj_val_set(errele_num).old_val2),'N ')
742 ||' '||nvl(to_char(p_failed_adj_val_set(errele_num).new_val2),'N ')
743 ||' '||nvl(to_char(p_failed_adj_val_set(errele_num).credt,'DD-MON-YYYY'),'NCD ')
744 ||' '||nvl(to_char(p_failed_adj_val_set(errele_num).lud,'DD-MON-YYYY'),'NLUD ')
745 ||' '||p_failed_adj_val_set(errele_num).id1
746 ||' '||p_failed_adj_val_set(errele_num).id2
747 ||' '||p_failed_adj_val_set(errele_num).code1
748 );
749 --
750 end loop;
751 hr_efc_info.insert_line('-- ');
752 --
753 end if;
754 --
755 if l_fatal_error_val_set.count > 0 then
756 --
757 -- Get unique fatal errors
758 --
759 l_unqval_set.delete;
760 l_unqele_num := 0;
761 --
762 for errele_num in l_fatal_error_val_set.first ..
763 l_fatal_error_val_set.last
764 loop
765 --
766 -- Populate null fatal error types with MISC
767 --
768 if l_fatal_error_val_set(errele_num).faterr_type is null then
769 --
770 l_fatal_error_val_set(errele_num).faterr_type := 'MISC';
771 --
772 end if;
773 --
774 -- Check if the error code exists in the unique list
775 --
776 if l_unqval_set.count > 0 then
777 --
778 l_found := FALSE;
779 --
780 for unqrow in l_unqval_set.first..l_unqval_set.last loop
781 --
782 if l_unqval_set(unqrow).unqval = l_fatal_error_val_set(errele_num).faterr_code
783 then
784 --
785 l_found := TRUE;
786 l_unqval_set(unqrow).count := l_unqval_set(unqrow).count+1;
787 --
788 -- Check for more recent creation date
789 --
790 if nvl(l_fatal_error_val_set(errele_num).credt,hr_api.g_sot)
791 > nvl(l_unqval_set(unqrow).mxcredt,hr_api.g_sot)
792 then
793 --
794 l_unqval_set(unqrow).mxcredt := l_fatal_error_val_set(errele_num).credt;
795 --
796 end if;
797 --
798 -- Check for more oldest creation date
799 --
800 if nvl(l_fatal_error_val_set(errele_num).credt,hr_api.g_sot)
801 < nvl(l_unqval_set(unqrow).mncredt,hr_api.g_sot)
802 then
803 --
804 l_unqval_set(unqrow).mncredt := l_fatal_error_val_set(errele_num).credt;
805 --
806 end if;
807 --
808 end if;
809 --
810 end loop;
811 --
812 if not l_found then
813 --
814 l_unqval_set(l_unqele_num).unqval := l_fatal_error_val_set(errele_num).faterr_code;
815 l_unqval_set(l_unqele_num).unqval1 := l_fatal_error_val_set(errele_num).faterr_type;
816 l_unqval_set(l_unqele_num).count := 1;
817 l_unqval_set(l_unqele_num).mxcredt := l_fatal_error_val_set(errele_num).credt;
818 l_unqval_set(l_unqele_num).mncredt := l_fatal_error_val_set(errele_num).credt;
819 l_unqele_num := l_unqele_num+1;
820 --
821 end if;
822 --
823 else
824 --
825 l_unqval_set(l_unqele_num).unqval := l_fatal_error_val_set(errele_num).faterr_code;
826 l_unqval_set(l_unqele_num).unqval1 := l_fatal_error_val_set(errele_num).faterr_type;
827 l_unqval_set(l_unqele_num).count := 1;
828 l_unqval_set(l_unqele_num).mxcredt := l_fatal_error_val_set(errele_num).credt;
829 l_unqval_set(l_unqele_num).mncredt := l_fatal_error_val_set(errele_num).credt;
830 l_unqele_num := l_unqele_num+1;
831 --
832 end if;
833 --
834 end loop;
835 --
836 -- Calculate percentages
837 --
838 if l_unqval_set.count > 0 then
839 --
840 for ele_num in l_unqval_set.first..l_unqval_set.last
841 loop
842 --
843 l_unqval_set(ele_num).percentage := round((l_unqval_set(ele_num).count/l_adjust_count)*100,2);
844 --
845 end loop;
846 --
847 end if;
848 --
849 -- Build type statistics
850 --
851 if l_unqval_set.count > 0 then
852 --
853 -- Get unique fatal errors
854 --
855 l_unqtype_set.delete;
856 l_unqele_num := 0;
857 --
858 for errele_num in l_unqval_set.first .. l_unqval_set.last
859 loop
860 --
861 -- Check if the error code exists in the unique list
862 --
863 if l_unqtype_set.count > 0 then
864 --
865 l_found := FALSE;
866 --
867 for unqrow in l_unqtype_set.first..l_unqtype_set.last loop
868 --
869 if l_unqtype_set(unqrow).unqval = l_unqval_set(errele_num).unqval1
870 then
871 --
872 l_found := TRUE;
873 l_unqtype_set(unqrow).count := l_unqtype_set(unqrow).count+1;
874 l_unqtype_set(unqrow).percentage := l_unqtype_set(unqrow).percentage+l_unqval_set(errele_num).percentage;
875 --
876 -- Check for more recent creation date
877 --
878 if nvl(l_unqval_set(errele_num).mxcredt,hr_api.g_sot)
879 > nvl(l_unqtype_set(unqrow).mxcredt,hr_api.g_sot)
880 then
881 --
882 l_unqtype_set(unqrow).mxcredt := l_unqval_set(errele_num).mxcredt;
883 --
884 end if;
885 --
886 -- Check for more recent creation date
887 --
888 if nvl(l_unqval_set(errele_num).mncredt,hr_api.g_sot)
889 < nvl(l_unqtype_set(unqrow).mncredt,hr_api.g_sot)
890 then
891 --
892 l_unqtype_set(unqrow).mncredt := l_unqval_set(errele_num).mncredt;
893 --
894 end if;
895 --
896 end if;
897 --
898 end loop;
899 --
900 if not l_found then
901 --
902 l_unqtype_set(l_unqele_num).unqval := l_unqval_set(errele_num).unqval1;
903 l_unqtype_set(l_unqele_num).count := 1;
904 l_unqtype_set(l_unqele_num).mxcredt := l_unqval_set(errele_num).mxcredt;
905 l_unqtype_set(l_unqele_num).mncredt := l_unqval_set(errele_num).mncredt;
906 l_unqtype_set(l_unqele_num).percentage := l_unqval_set(errele_num).percentage;
907 l_unqele_num := l_unqele_num+1;
908 --
909 end if;
910 --
911 else
912 --
913 l_unqtype_set(l_unqele_num).unqval := l_unqval_set(errele_num).unqval1;
914 l_unqtype_set(l_unqele_num).count := 1;
915 l_unqtype_set(l_unqele_num).mxcredt := l_unqval_set(errele_num).mxcredt;
916 l_unqtype_set(l_unqele_num).mncredt := l_unqval_set(errele_num).mncredt;
917 l_unqtype_set(l_unqele_num).percentage := l_unqval_set(errele_num).percentage;
918 l_unqele_num := l_unqele_num+1;
919 --
920 end if;
921 --
922 end loop;
923 --
924 end if;
925 --
926 -- Display unique fatal error types
927 --
928 if l_unqtype_set.count > 0 then
929 --
930 hr_efc_info.insert_line('-- '||l_unqtype_set.count||' Exclusion Type Categories ');
931 hr_efc_info.insert_line('-- ');
932 --
933 for ele_num in l_unqtype_set.first..l_unqtype_set.last
934 loop
935 --
936 l_disp_str := l_unqtype_set(ele_num).count
937 ||' '||l_unqtype_set(ele_num).percentage||'%'
938 ||' '||get_description(l_unqtype_set(ele_num).unqval);
939 --
940 if p_disp_private then
941 --
942 l_disp_str := l_disp_str||' Range between: '||l_unqtype_set(ele_num).mncredt
943 ||' and '||l_unqtype_set(ele_num).mxcredt;
944 --
945 end if;
946 --
947 hr_efc_info.insert_line(l_disp_str
948 );
949 --
950 end loop;
951 hr_efc_info.insert_line('-- ');
952 --
953 end if;
954 --
955 -- Display unique fatal errors
956 --
957 if l_unqval_set.count > 0 then
958 --
959 hr_efc_info.insert_line('-- '||l_unqval_set.count||' Exclusion Types ');
960 hr_efc_info.insert_line('-- ');
961 --
962 for ele_num in l_unqval_set.first..l_unqval_set.last
963 loop
964 --
965 l_disp_str := l_unqval_set(ele_num).count
966 ||' '||l_unqval_set(ele_num).percentage||'%'
967 ||' '||get_description(l_unqval_set(ele_num).unqval)
968 ||' ('||get_description(l_unqval_set(ele_num).unqval1)||') ';
969 --
970 if p_disp_private then
971 --
972 l_disp_str := l_disp_str||' Range between: '||l_unqval_set(ele_num).mncredt
973 ||' and '||l_unqval_set(ele_num).mxcredt;
974 --
975 end if;
976 --
977 hr_efc_info.insert_line(l_disp_str
978 );
979 --
980 end loop;
981 hr_efc_info.insert_line('-- ');
982 --
983 end if;
984 --
985 if p_disp_exclusions then
986 --
987 hr_efc_info.insert_line('-- '||l_fatal_error_val_set.count||' Exclusions ');
988 hr_efc_info.insert_line('-- ');
989 --
990 for errele_num in l_fatal_error_val_set.first ..
991 l_fatal_error_val_set.last
992 loop
993 --
994 hr_efc_info.insert_line(l_fatal_error_val_set(errele_num).faterr_code
995 ||' '||l_fatal_error_val_set(errele_num).faterr_type
996 ||' '||l_fatal_error_val_set(errele_num).id
997 ||' '||l_fatal_error_val_set(errele_num).esd
998 ||' '||l_fatal_error_val_set(errele_num).val_type
999 ||' '||l_fatal_error_val_set(errele_num).old_val1
1000 ||' '||l_fatal_error_val_set(errele_num).new_val1
1001 ||' '||l_fatal_error_val_set(errele_num).ovn
1002 ||' '||l_fatal_error_val_set(errele_num).bgp_id
1003 ||' '||to_char(l_fatal_error_val_set(errele_num).credt,'DD-MON-YYYY')
1004 ||' '||to_char(l_fatal_error_val_set(errele_num).lud,'DD-MON-YYYY')
1005 ||' '||l_fatal_error_val_set(errele_num).cre_by
1006 ||' '||l_fatal_error_val_set(errele_num).lu_by
1007 ||' '||l_fatal_error_val_set(errele_num).id1
1008 ||' '||l_fatal_error_val_set(errele_num).id2
1009 ||' '||l_fatal_error_val_set(errele_num).code1
1010 ||' '||l_fatal_error_val_set(errele_num).code2
1011 ||' '||l_fatal_error_val_set(errele_num).code3
1012 ||' '||l_fatal_error_val_set(errele_num).code4
1013 );
1014 --
1015 end loop;
1016 hr_efc_info.insert_line('-- ');
1017 --
1018 if l_dup_excl_val_set.count > 0 then
1019 --
1020 hr_efc_info.insert_line('-- '||l_dup_excl_val_set.count||' Duplicate Exclusions ');
1021 hr_efc_info.insert_line('-- ');
1022 --
1023 for errele_num in l_dup_excl_val_set.first ..
1024 l_dup_excl_val_set.last
1025 loop
1026 --
1027 hr_efc_info.insert_line(l_dup_excl_val_set(errele_num).faterr_code
1028 ||' '||l_dup_excl_val_set(errele_num).faterr_type
1029 ||' '||l_dup_excl_val_set(errele_num).id
1030 ||' '||l_dup_excl_val_set(errele_num).esd
1031 ||' '||l_dup_excl_val_set(errele_num).val_type
1032 ||' '||l_dup_excl_val_set(errele_num).old_val1
1033 ||' '||l_dup_excl_val_set(errele_num).new_val1
1034 ||' '||l_dup_excl_val_set(errele_num).ovn
1035 ||' '||l_dup_excl_val_set(errele_num).bgp_id
1036 ||' '||to_char(l_dup_excl_val_set(errele_num).credt,'DD-MON-YYYY')
1037 ||' '||to_char(l_dup_excl_val_set(errele_num).lud,'DD-MON-YYYY')
1038 ||' '||l_dup_excl_val_set(errele_num).cre_by
1039 ||' '||l_dup_excl_val_set(errele_num).lu_by
1040 ||' '||l_dup_excl_val_set(errele_num).id1
1041 ||' '||l_dup_excl_val_set(errele_num).id2
1042 ||' '||l_dup_excl_val_set(errele_num).code1
1043 ||' '||l_dup_excl_val_set(errele_num).code2
1044 ||' '||l_dup_excl_val_set(errele_num).code3
1045 ||' '||l_dup_excl_val_set(errele_num).code4
1046 );
1047 --
1048 end loop;
1049 --
1050 end if;
1051 hr_efc_info.insert_line('-- ');
1052 --
1053 end if;
1054 --
1055 end if;
1056 --
1057 if p_success_val_set.count > 0
1058 and p_disp_succeeds
1059 then
1060 --
1061 hr_efc_info.insert_line('-- '||l_success_val_set.count||' Successes ');
1062 hr_efc_info.insert_line('-- ');
1063 --
1064 for ele_num in l_success_val_set.first ..
1065 l_success_val_set.last
1066 loop
1067 --
1068 hr_efc_info.insert_line(l_success_val_set(ele_num).id
1069 ||' '||l_success_val_set(ele_num).esd
1070 ||' '||l_success_val_set(ele_num).eed
1071 ||' '||l_success_val_set(ele_num).old_val1
1072 ||' '||l_success_val_set(ele_num).new_val1
1073 ||' '||l_success_val_set(ele_num).ovn
1074 ||' '||l_success_val_set(ele_num).bgp_id
1075 ||' '||to_char(l_success_val_set(ele_num).credt,'DD-MON-YYYY')
1076 ||' '||to_char(l_success_val_set(ele_num).lud,'DD-MON-YYYY')
1077 ||' '||l_success_val_set(ele_num).cre_by
1078 ||' '||l_success_val_set(ele_num).lu_by
1079 ||' '||l_success_val_set(ele_num).id1
1080 ||' '||l_success_val_set(ele_num).id2
1081 ||' '||l_success_val_set(ele_num).code1
1082 ||' '||l_success_val_set(ele_num).code2
1083 ||' '||l_success_val_set(ele_num).code3
1084 ||' '||l_success_val_set(ele_num).code4
1085 );
1086 --
1087 end loop;
1088 hr_efc_info.insert_line('-- ');
1089 --
1090 if l_dup_success_val_set.count > 0 then
1091 --
1092 hr_efc_info.insert_line('-- '||l_dup_success_val_set.count||' Duplicate Successes ');
1093 hr_efc_info.insert_line('-- ');
1094 --
1095 for ele_num in l_dup_success_val_set.first..l_dup_success_val_set.last
1096 loop
1097 --
1098 hr_efc_info.insert_line(l_dup_success_val_set(ele_num).id
1099 ||' '||l_dup_success_val_set(ele_num).esd
1100 ||' '||l_dup_success_val_set(ele_num).eed
1101 ||' '||l_dup_success_val_set(ele_num).old_val1
1102 ||' '||l_dup_success_val_set(ele_num).new_val1
1103 ||' '||l_dup_success_val_set(ele_num).ovn
1104 ||' '||l_dup_success_val_set(ele_num).bgp_id
1105 ||' '||to_char(l_dup_success_val_set(ele_num).credt,'DD-MON-YYYY')
1106 ||' '||to_char(l_dup_success_val_set(ele_num).lud,'DD-MON-YYYY')
1107 ||' '||l_dup_success_val_set(ele_num).cre_by
1108 ||' '||l_dup_success_val_set(ele_num).lu_by
1109 ||' '||l_dup_success_val_set(ele_num).id2
1110 ||' '||l_dup_success_val_set(ele_num).code1
1111 ||' '||l_dup_success_val_set(ele_num).code2
1112 ||' '||l_dup_success_val_set(ele_num).code3
1113 ||' '||l_dup_success_val_set(ele_num).code4
1114 );
1115 --
1116 end loop;
1117 hr_efc_info.insert_line('-- ');
1118 --
1119 end if;
1120 --
1121 end if;
1122 --
1123 end DisplayEFCInfo;
1124 --
1125 end ben_efc_reporting;