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
) |