BEN Benefits Confirmation and Summary

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Application: Advanced Benefits
Source: Benefits Confirmation and Summary Report (XML)
Short Name: BENSMREP_XML
DB package: BEN_BENSMREP_XMLP_PKG
Run BEN Benefits Confirmation and Summary and other Oracle EBS reports with Blitz Report™ on our demo environment
	SELECT  plan_name
       ,pay_perd
       ,notapplicable
       ,ptip_ordr_num
       ,plip_ordr_num
       ,pl_ordr_num
       ,oipl_ordr_num
       ,pretax
       ,aftertax
       ,nontaxable
       ,taxable
       ,uom
       ,meaning
       ,cmcd_val
       ,bnft_amt
       ,to_char(enrt_cvg_strt_dt, :P_DATE_MASK) enrt_cvg_strt_dt
       ,pgm_id pgm_id1
       ,pl_id
       ,oipl_id
       ,enrt_mthd_cd
       ,person_id person_id2
       ,pl_ordr_num
       ,oipl_ordr_num
       ,plip_ordr_num
       ,ptip_ordr_num
FROM    (
        SELECT  pln.name plan_name
               ,enrt.bnft_amt
               ,enrt.enrt_cvg_strt_dt
               ,decode (rt.dsply_on_enrt_flag
                       ,'Y'
                       ,tax_typ.meaning
                       ,'') meaning
               ,decode (rt.dsply_on_enrt_flag
                       ,'Y'
                       ,rt.cmcd_val
                       ,0) cmcd_val
               ,enrt.pl_id
               ,enrt.oipl_id
               ,enrt.enrt_mthd_cd
               ,enrt.pgm_id
               ,enrt.person_id
               ,enrt.ptip_ordr_num
               ,enrt.plip_ordr_num
               ,enrt.pl_ordr_num
               ,enrt.oipl_ordr_num
               ,enrt.uom
               ,ref_perd.meaning pay_perd
               ,decode (rt.tx_typ_cd
                       ,'PRETAX'
                       ,decode (rt.dsply_on_enrt_flag
                               ,'Y'
                               ,rt.cmcd_val
                               ,0)
                       ,0) pretax
               ,decode (rt.tx_typ_cd
                       ,'AFTERTAX'
                       ,decode (rt.dsply_on_enrt_flag
                               ,'Y'
                               ,rt.cmcd_val
                               ,0)
                       ,0) aftertax
               ,decode (rt.tx_typ_cd
                       ,'NONTAXABLE'
                       ,decode (rt.dsply_on_enrt_flag
                               ,'Y'
                               ,rt.cmcd_val
                               ,0)
                       ,0) nontaxable
               ,decode (rt.tx_typ_cd
                       ,'TAXABLE'
                       ,decode (rt.dsply_on_enrt_flag
                               ,'Y'
                               ,rt.cmcd_val
                               ,0)
                       ,0) taxable
               ,decode (rt.tx_typ_cd
                       ,'NOTAPPLICABLE'
                       ,decode (rt.dsply_on_enrt_flag
                               ,'Y'
                               ,rt.cmcd_val
                               ,0)
                       ,0) notapplicable
               ,enrt.ptip_ordr_num ptip
               ,enrt.plip_ordr_num plip
               ,enrt.pl_ordr_num pl
               ,enrt.oipl_ordr_num oipl
        FROM    ben_prtt_enrt_rslt_f enrt
               ,ben_prtt_enrt_rt_val_v rt
               ,ben_pl_f pln
               ,hr_lookups tax_typ
               ,ben_pl_typ_f pt
               ,hr_lookups ref_perd
               ,ben_per_in_ler pil
               ,ben_ler_f ler
        WHERE   (
                        enrt.business_group_id = :CP_BUSINESS_GROUP_ID
                AND     enrt.per_in_ler_id = pil.per_in_ler_id
                AND     enrt.person_id = :PERSON_ID1
                AND     enrt.pgm_id = :PGM_ID
                AND     pil.ler_id = ler.ler_id
                AND     :P_PROCESS_DATE BETWEEN ler.effective_start_date AND ler.effective_end_date
                AND     ler.typ_cd <> 'SCHEDDU'
                AND     rt.business_group_id = enrt.business_group_id
                AND     enrt.prtt_enrt_rslt_id = rt.prtt_enrt_rslt_id
                AND     :P_PROCESS_DATE BETWEEN enrt.enrt_cvg_strt_dt AND enrt.enrt_cvg_thru_dt
                AND     rt.dsply_on_enrt_flag = 'Y'
                AND     :P_PROCESS_DATE BETWEEN pln.effective_start_date AND pln.effective_end_date
                AND     enrt.enrt_cvg_thru_dt <= enrt.effective_end_date
                AND     enrt.prtt_enrt_rslt_stat_cd IS NULL
                AND     enrt.sspndd_flag = 'N'
                AND     pln.pl_id = enrt.pl_id
                AND     rt.rt_strt_dt <= rt.rt_end_dt
                AND     tax_typ.lookup_type = 'BEN_TX_TYP'
                AND     tax_typ.lookup_code = rt.tx_typ_cd
                AND     ref_perd.lookup_type = 'BEN_ENRT_INFO_RT_FREQ'
                AND     ref_perd.lookup_code = rt.cmcd_ref_perd
                AND     pt.pl_typ_id = enrt.pl_typ_id
                AND     pt.opt_typ_cd NOT IN ('CWB','COMP','GSP')
                AND     pt.business_group_id = :CP_BUSINESS_GROUP_ID
                AND     :P_PROCESS_DATE BETWEEN pt.effective_start_date AND pt.effective_end_date
                )
        AND     EXISTS
                (
                SELECT  1
                FROM    ben_acty_base_rt_f abr
                WHERE   rt.acty_base_rt_id = abr.acty_base_rt_id
                AND     :P_PROCESS_DATE BETWEEN abr.effective_start_date AND abr.effective_end_date
                AND     (
                                (
                                        nvl (abr.rcrrg_cd
                                            ,'RCRRG') = 'RCRRG'
                                AND     :P_PROCESS_DATE BETWEEN rt.rt_strt_dt AND rt.rt_end_dt
                                )
                        OR      (
                                        abr.rcrrg_cd = 'ONCE'
                                AND     rt.rt_end_dt = rt.rt_strt_dt
                                )
                        )
                )
        UNION
        SELECT  pln.name plan_name
               ,enrt.bnft_amt
               ,enrt.enrt_cvg_strt_dt
               ,'' meaning
               ,0 cmcd_val
               ,enrt.pl_id
               ,enrt.oipl_id
               ,enrt.enrt_mthd_cd
               ,enrt.pgm_id
               ,enrt.person_id
               ,enrt.ptip_ordr_num
               ,enrt.plip_ordr_num
               ,enrt.pl_ordr_num
               ,enrt.oipl_ordr_num
               ,enrt.uom
               ,NULL pay_perd
               ,0 pretax
               ,0 aftertax
               ,0 nontaxable
               ,0 taxable
               ,0 notapplicable
               ,enrt.ptip_ordr_num ptip
               ,enrt.plip_ordr_num plip
               ,enrt.pl_ordr_num pl
               ,enrt.oipl_ordr_num oipl
        FROM    ben_prtt_enrt_rslt_f enrt
               ,ben_pl_f pln
               ,ben_pl_typ_f pt
        WHERE   enrt.business_group_id = :CP_BUSINESS_GROUP_ID
        AND     enrt.person_id = :PERSON_ID1
        AND     enrt.pgm_id = :PGM_ID
        AND     :P_PROCESS_DATE BETWEEN enrt.enrt_cvg_strt_dt AND enrt.enrt_cvg_thru_dt
        AND     :P_PROCESS_DATE BETWEEN pln.effective_start_date AND pln.effective_end_date
        AND     enrt.enrt_cvg_thru_dt <= enrt.effective_end_date
        AND     enrt.prtt_enrt_rslt_stat_cd IS NULL
        AND     enrt.sspndd_flag = 'N'
        AND     pln.pl_id = enrt.pl_id
        AND     NOT EXISTS
                    (
                    SELECT  prv.prtt_enrt_rslt_id
                    FROM    ben_prtt_rt_val prv
                    WHERE   prv.business_group_id = enrt.business_group_id
                    AND     prv.prtt_enrt_rslt_id = enrt.prtt_enrt_rslt_id
                    AND     prv.rt_end_dt >= prv.rt_strt_dt
                    AND     prv.prtt_rt_val_stat_cd IS NULL
                    )
        AND     pt.pl_typ_id = enrt.pl_typ_id
        AND     pt.opt_typ_cd NOT IN ('CWB','COMP','GSP')
        AND     pt.business_group_id = :CP_BUSINESS_GROUP_ID
        AND     :P_PROCESS_DATE BETWEEN pt.effective_start_date AND pt.effective_end_date
        UNION
        SELECT  pln.name plan_name
               ,pen.bnft_amt
               ,pen.enrt_cvg_strt_dt
               ,tax_typ.meaning meaning
               ,prv.cmcd_rt_val cmcd_val
               ,pen.pl_id
               ,pen.oipl_id
               ,pen.enrt_mthd_cd
               ,pen.pgm_id
               ,pen.person_id
               ,pen.ptip_ordr_num
               ,pen.plip_ordr_num
               ,pen.pl_ordr_num
               ,pen.oipl_ordr_num
               ,pen.uom
               ,ref_perd.meaning pay_perd
               ,decode (prv.tx_typ_cd
                       ,'PRETAX'
                       ,decode (prv.dsply_on_enrt_flag
                               ,'Y'
                               ,prv.cmcd_rt_val
                               ,0)
                       ,0) pretax
               ,decode (prv.tx_typ_cd
                       ,'AFTERTAX'
                       ,decode (prv.dsply_on_enrt_flag
                               ,'Y'
                               ,prv.cmcd_rt_val
                               ,0)
                       ,0) aftertax
               ,decode (prv.tx_typ_cd
                       ,'NONTAXABLE'
                       ,decode (prv.dsply_on_enrt_flag
                               ,'Y'
                               ,prv.cmcd_rt_val
                               ,0)
                       ,0) nontaxable
               ,decode (prv.tx_typ_cd
                       ,'TAXABLE'
                       ,decode (prv.dsply_on_enrt_flag
                               ,'Y'
                               ,prv.cmcd_rt_val
                               ,0)
                       ,0) taxable
               ,decode (prv.tx_typ_cd
                       ,'NOTAPPLICABLE'
                       ,decode (prv.dsply_on_enrt_flag
                               ,'Y'
                               ,prv.cmcd_rt_val
                               ,0)
                       ,0) notapplicable
               ,pen.ptip_ordr_num ptip
               ,pen.plip_ordr_num plip
               ,pen.pl_ordr_num pl
               ,pen.oipl_ordr_num oipl
        FROM    ben_prtt_enrt_rslt_f pen
               ,ben_pl_f pln
               ,ben_pl_typ_f pt
               ,ben_ler_f ler
               ,ben_per_in_ler pil
               ,ben_prtt_rt_val prv
               ,hr_lookups tax_typ
               ,hr_lookups ref_perd
               ,ben_acty_base_rt_f abr
        WHERE   pen.per_in_ler_id = pil.per_in_ler_id
        AND     pen.person_id = :PERSON_ID1
        AND     pen.pgm_id = :PGM_ID
        AND     pil.ler_id = ler.ler_id
        AND     pil.per_in_ler_stat_cd = 'STRTD'
        AND     ler.typ_cd = 'SCHEDDU'
        AND     pen.business_group_id = :CP_BUSINESS_GROUP_ID
        AND     prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
        AND     :P_PROCESS_DATE BETWEEN prv.rt_strt_dt AND prv.rt_end_dt
        AND     pln.pl_id = pen.pl_id
        AND     :P_PROCESS_DATE BETWEEN pen.enrt_cvg_strt_dt AND pen.enrt_cvg_thru_dt
        AND     :P_PROCESS_DATE BETWEEN pen.effective_start_date AND pen.effective_end_date
        AND     prv.acty_base_rt_id = abr.acty_base_rt_id
        AND     prv.dsply_on_enrt_flag = 'Y'
        AND     :P_PROCESS_DATE BETWEEN abr.effective_start_date AND abr.effective_end_date
        AND     (
                        (
                                nvl (abr.rcrrg_cd
                                    ,'RCRRG') = 'RCRRG'
                        AND     :P_PROCESS_DATE BETWEEN prv.rt_strt_dt AND prv.rt_end_dt
                        )
                OR      (
                                abr.rcrrg_cd = 'ONCE'
                        AND     prv.rt_strt_dt = prv.rt_strt_dt
                        )
                )
        AND     :P_PROCESS_DATE BETWEEN pln.effective_start_date AND pln.effective_end_date
        AND     pen.prtt_enrt_rslt_stat_cd IS NULL
        AND     pen.sspndd_flag = 'N'
        AND     pt.pl_typ_id = pen.pl_typ_id
        AND     pt.opt_typ_cd NOT IN ('CWB','COMP','GSP')
        AND     pt.business_group_id = :CP_BUSINESS_GROUP_ID
        AND     :P_PROCESS_DATE BETWEEN pt.effective_start_date AND pt.effective_end_date
        AND     tax_typ.lookup_type = 'BEN_TX_TYP'
        AND     tax_typ.lookup_code = prv.tx_typ_cd
        AND     ref_perd.lookup_type = 'BEN_ENRT_INFO_RT_FREQ'
        AND     ref_perd.lookup_code = prv.cmcd_ref_perd_cd
        ORDER BY ptip
                ,plip
                ,pl
                ,oipl
        )
Parameter Name SQL text Validation
PER_DATES 
 
 Date
Benefit Actions 
 
 LOV Oracle
Program 
 
 LOV Oracle
Plan Not In Program 
 
 LOV Oracle
Organization 
 
 LOV Oracle
Location 
 
 LOV Oracle
Person 
 
 LOV Oracle
Life Event Reason 
 
 LOV Oracle
p_lf_evt_ocrd_dt 
 
 Date
Person Selection Rule 
 
 LOV Oracle
Compensation Selection Rule 
 
 LOV Oracle
Business Group 
 
 Number
Plan In Program 
 
 LOV Oracle
Person Type 
 
 LOV Oracle
Reporting Group 
 
 Number
Service Area 
 
 Number
Assignment Type 
 
 LOV Oracle
Coverage Start Date 
 
 Date
Coverage End Date 
 
 Date
Benefits Selection 
 
 LOV Oracle
Flex Summary 
 
 LOV Oracle
Covered Dependent 
 
 LOV Oracle
Primary Care 
 
 LOV Oracle
Beneficiaries 
 
 LOV Oracle
Certifications 
 
 LOV Oracle
Action Items 
 
 LOV Oracle
Debug Flag 
 
 Char
Download
Related Blitz Reports
BEN Reconciliation of Premium Contributions to Element Entries BEN Life Events Summary BEN COBRA Benefit Notification Letter BEN COBRA Benefit Initial Information BEN HIPAA Letter BEN Close Action Items Audit Log BEN Dependent Eligibility Audit Log BEN Process Forfeitures Audit Log BEN Process Forfeitures Summary
Blitz Report™

Blitz Report™ provides multiple benefits: