[Home] [Help]
10: Name:
11: Determine Coverage
12:
13: Purpose:
14: Determines proper coverages based on mlt_cd. Writes to BEN_ENRT_BNFT table.
15:
16: History:
17:
18: Date Who Version What?
24: 22 Jun 98 Ty Hayden 110.3 Added Mn,Mx,Incr,and Val edits.
25: 25 Jun 98 T Guy 110.4 Replaced all occurrences of
26: PER10 with PERTEN.
27: 11 Aug 98 Ty Hayden 110.5 Added mlt cd NSVU
28: 08 Oct 98 T Guy 115.3 Added schema changes for ben_enrt_bnft
29: Added message numbers and debugging
30: messages.
31: 12 Oct 98 T Guy 115.4 Added mlt cd SAAEAR. Added dflt_val
32: logic.
227: dschwart/cdaniels condition when the maximum amount is NULL.
228:
229: 28-Dec-01 dschwart/ 115.91 - Bug#2054078: Max Coverage without certification
230: BBurns/jcarpent was not showing up on form because it was not
231: being written to ben_enrt_bnft. Made modifications
232: to chk_bnft_ctfn_val parameters + related code to
233: fix this.
234: 26-Mar-02 kmahendr 115.92 - Bug#1833008 - Added parameter p_cal_for to the call
235: determine_compensation to handle multi-assignment.
321: 06-jun-05 nhunur 115.138 4383988 - apply fnd_number on what FF returns in numeric variable.
322: 30-Jun-05 ikasire 115.139 BUG 4449437 always setting the default flag to N for FLFXPCL
323: 19-jul-05 rgajula 115.140 Bug 4436573 Added a new parameter p_cvg_mlt_cd to the
324: ben_determine_coverage.chk_bnft_ctfn to perform few more
325: validations to set the certification required flag of ben_enrt_bnft.
326: 25-Sep-05 kmahendr 115.141 Bug#4630782 - in unrestricted mode inelig
327: process not called
328: 27-Sep-05 ikasire 115.142 Bug 4627840
329: 04-Oct-05 ikasire 115.143 Bug 4649262 ERL fix regression from 4436573
333: will return the amount as calculated by the rule or the min value in the min feild in retrictions.
334: This returned value is used in the write record for Flat Amount Calculation method as
335: max(cvg_rstrn_amount,cvg_calc_mthd_min_amnt).
336: 16-Jan-06 abparekh 115.145 Bug 4954541 : Unrestricted : If coverage does not exist, then delete
337: corresponding BEN_ENRT_BNFT record
338: 11-Apr-06 bmanyam 115.147 5105118 changes (115.146) are undone
339: This is leapfrog of 115.145.
340: 5105118 is not-a-bug.
341: May 24, 2006 rgajula 115.148 Bug 5236985 Added the l_cvg_rstn_max_with_cert not null condition
371: Multiple of compensation
372: 23-Jan-2009 sallumwa Bug 7490759 :Modified the cursor c_cf_bnft_result to fetch the correct suspended
373: result ID when FONM and Event date LE's are processed in the same month.
374: 02-Feb-2009 sallumwa 115.172 Bug 7704956 : Modified the procedure main to update the
375: currently_enrld_flag in ben_enrt_bnft table to 'Y' when the
376: benefit amount matches if multiple rows are set to 'Y' corresponding
377: to one elig_per_elctbl_chc_id.
378: 11-Feb-2009 velvanop 115.173 Bug 7414757: Added parameter p_entr_val_at_enrt_flag.
379: VAPRO rates which are 'Enter value at Enrollment', Form field
483: END;
484:
485: ---------------------------------------------------
486: --
487: -- Write ben_enrt_bnft
488: --
489: ---------------------------------------------------
490: --
491: PROCEDURE write_coverage
614: --
615: cursor c_other_oipl_cvg is
616: select enb.val
617: from ben_elig_per_elctbl_chc epe,
618: ben_enrt_bnft enb
619: where epe.pl_id=l_epe.pl_id
620: and nvl(epe.pgm_id,-1)=nvl(l_epe.pgm_id,-1)
621: and epe.per_in_ler_id=l_epe.per_in_ler_id
622: and epe.business_group_id=l_epe.business_group_id
628: --
629: cursor c_other_pl_cvg is
630: select enb.val
631: from ben_elig_per_elctbl_chc epe,
632: ben_enrt_bnft enb
633: where epe.ptip_id=l_epe.ptip_id
634: and nvl(epe.pgm_id,-1)=nvl(l_epe.pgm_id,-1)
635: and epe.per_in_ler_id=l_epe.per_in_ler_id
636: and epe.business_group_id=l_epe.business_group_id
644: select enb.val
645: from ben_elig_per_elctbl_chc epe,
646: ben_oipl_f oipl,
647: ben_oipl_f oipl2,
648: ben_enrt_bnft enb
649: where epe.ptip_id=l_epe.ptip_id
650: and nvl(epe.pgm_id,-1)=nvl(l_epe.pgm_id,-1)
651: and epe.pl_typ_id=l_epe.pl_typ_id
652: and epe.per_in_ler_id=l_epe.per_in_ler_id
1093: p_enrt_bnft_id => l_enrt_bnft_id,
1094: p_ordr_num => p_ordr_num);
1095: else
1096: --
1097: ben_enrt_bnft_api.create_enrt_bnft
1098: (p_validate => false,
1099: p_dflt_flag => p_dflt_flag,
1100: p_val_has_bn_prortd_flag => 'N', -- change when prorating
1101: p_bndry_perd_cd => p_bndry_perd_cd,
1866: select
1867: pen.bnft_amt,
1868: enb.*
1869: from ben_prtt_enrt_rslt_f pen,
1870: ben_enrt_bnft enb,
1871: ben_elig_per_elctbl_chc epe,
1872: ben_per_in_ler pil
1873: where pen.business_group_id = l_business_group_id
1874: and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
1981: --
1982: ----Bug 7704956
1983: cursor c_check_dup_enb_enr(p_epe_id number) is
1984: SELECT enb.*
1985: FROM ben_enrt_bnft enb
1986: WHERE elig_per_elctbl_chc_id = p_epe_id
1987: AND crntly_enrld_flag = 'Y';
1988:
1989: l_check_dup_enb_enr c_check_dup_enb_enr%rowtype;
2062: cursor c_get_cvg_rstr(p_prev_pil_id number) is
2063: SELECT enb1.*
2064: FROM ben_elig_per_elctbl_chc epe,
2065: ben_elig_per_elctbl_chc epe1,
2066: ben_enrt_bnft enb1
2067: WHERE epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
2068: AND epe.crntly_enrd_flag = 'Y'
2069: AND epe1.per_in_ler_id = p_prev_pil_id
2070: AND epe1.pl_id = epe.pl_id
2089: and l_cvg(0).cvg_mlt_cd not in ('CLRNG','FLPCLRNG','CLPFLRNG','FLRNG')
2090: union
2091: SELECT ecc.*
2092: FROM ben_elctbl_chc_ctfn ecc,
2093: ben_enrt_bnft enb
2094: WHERE ecc.elig_per_elctbl_chc_id = p_epe_id
2095: AND ecc.business_group_id = p_business_group_id
2096: AND enb.business_group_id = ecc.business_group_id
2097: AND enb.elig_per_elctbl_chc_id = ecc.elig_per_elctbl_chc_id
2104:
2105: cursor c_chk_bnft_cert(p_business_group_id number) is
2106: SELECT NULL
2107: FROM ben_elctbl_chc_ctfn ecc,
2108: ben_enrt_bnft enb ---Added for the bug 8767376
2109: WHERE ecc.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
2110: AND ecc.business_group_id = p_business_group_id
2111: ---Added for the bug 8767376
2112: AND enb.business_group_id = ecc.business_group_id
2124: --Bug 14137476
2125:
2126: cursor c is
2127: select ENTR_VAL_AT_ENRT_FLAG
2128: from ben_enrt_bnft
2129: where enrt_bnft_id = l_enrt_bnft_id;
2130:
2131: l_proc varchar2(70) :='ben_determine_coverage.write_ctfn';
2132: l_entr_val_at_enrt_flag varchar2(1):= 'N';
2442: --
2443: IF l_enrt_bnft_id is not null
2444: THEN
2445: --
2446: delete from ben_enrt_Bnft
2447: where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
2448: --
2449: END IF;
2450: --
3351: p_ordr_num => 0);
3352: --
3353: else
3354: --
3355: ben_enrt_bnft_api.create_enrt_bnft
3356: (p_validate => false,
3357: p_dflt_flag => 'N',
3358: p_val_has_bn_prortd_flag => 'N', -- change when prorating
3359: p_bndry_perd_cd => l_cvg(0).bndry_perd_cd,
4018: close c_get_prev_bnft;
4019: for l_check in c_check_dup_enb_enr(p_elig_per_elctbl_chc_id) loop
4020: --update enb table if order number matches.
4021: if l_get_prev_bnft.bnft_amt <> l_check.val then
4022: ben_enrt_bnft_api.update_enrt_bnft
4023: (p_enrt_bnft_id => l_check.enrt_bnft_id
4024: ,p_dflt_flag => 'N' ---l_check.dflt_flag,Bug 9308931
4025: ,p_val_has_bn_prortd_flag => l_check.val_has_bn_prortd_flag
4026: ,p_bndry_perd_cd => l_check.bndry_perd_cd
4308: fetch c_get_prev_bnft into l_get_prev_bnft;
4309: close c_get_prev_bnft;
4310: for l_check in c_check_dup_enb_enr(p_elig_per_elctbl_chc_id) loop
4311: if l_get_prev_bnft.bnft_amt <> l_check.val then
4312: ben_enrt_bnft_api.update_enrt_bnft
4313: (p_enrt_bnft_id => l_check.enrt_bnft_id
4314: ,p_dflt_flag => 'N'
4315: ,p_val_has_bn_prortd_flag => l_check.val_has_bn_prortd_flag
4316: ,p_bndry_perd_cd => l_check.bndry_perd_cd
5562: nvl(l_fonm_cvg_strt_dt,nvl(p_lf_evt_ocrd_dt,p_effective_date))) ; -- FONM
5563: fetch c_current_enb into l_current_enb ;
5564: if c_current_enb%found and not p_calculate_only_mode then
5565: hr_utility.set_location('SAME interim row',111);
5566: ben_enrt_bnft_api.create_enrt_bnft
5567: (p_validate => false,
5568: p_dflt_flag => 'N',
5569: p_val_has_bn_prortd_flag => 'N', -- change when prorating
5570: p_bndry_perd_cd => l_current_enb.bndry_perd_cd, -- l_cvg(0).bndry_perd_cd,