BEN Benefits Confirmation and Summary

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

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

	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