VIEW: APPS.BEN_PRTT_ENRT_CTFN_PRVDD_D

Source

View Text - Preformatted

SELECT pcs.ROWID ROW_ID , pcs.PRTT_ENRT_CTFN_PRVDD_ID ,pcs.EFFECTIVE_START_DATE ,pcs.EFFECTIVE_END_DATE ,ENRT_CTFN_TYP.MEANING enrt_ctfn_typ_meaning ,decode(pen.sspndd_flag,'Y','Yes','No') sspndd_flag ,pea.due_dt ,ENRT_CTFN_RQD.MEANING enrt_ctfn_rqd_meaning ,ENRT_R_BNFT_CTFN.MEANING enrt_r_bnft_ctfn_meaning ,decode(pcs.enrt_ctfn_recd_dt,to_date(null),'No','Yes') recvd_flag ,pcs.ENRT_CTFN_RECD_DT ,decode(pcs.enrt_ctfn_dnd_dt,to_date(null),'No','Yes') dnd_flag ,pcs.ENRT_CTFN_DND_DT ,PLAN.NAME pln_name ,PLTYP.NAME pl_typ_name ,PROGRAM.NAME pgm_name ,OPT.NAME opt_name ,LER.NAME ler_name ,pcs.LAST_UPDATE_DATE ,fuser.user_name last_updated_by from BEN_PRTT_ENRT_CTFN_PRVDD_F pcs , ben_prtt_enrt_actn_f pea , ben_per_in_ler pil , fnd_user fuser ,hr_lookups ENRT_CTFN_TYP ,hr_lookups ENRT_R_BNFT_CTFN ,hr_lookups ENRT_CTFN_RQD ,BEN_PRTT_ENRT_RSLT_F PEN ,BEN_OPT_F OPT, BEN_OIPL_F OPT2, BEN_PGM_F PROGRAM, BEN_PL_F PLAN, BEN_LER_F LER, BEN_PL_TYP_F PLTYP WHERE fuser.user_id (+)= pcs.last_updated_by and ENRT_CTFN_TYP.lookup_code (+)= pcs.ENRT_CTFN_TYP_CD and ENRT_CTFN_TYP.lookup_type (+)= 'ENRT_CTFN_TYP' and ENRT_R_BNFT_CTFN.lookup_code (+)= pcs.ENRT_R_BNFT_CTFN_CD and ENRT_R_BNFT_CTFN.lookup_type (+)= 'ENRT_R_BNFT_CTFN' and ENRT_CTFN_RQD.lookup_code (+)= pcs.ENRT_CTFN_RQD_FLAG and ENRT_CTFN_RQD.lookup_type (+)= 'YES_NO' and pea.prtt_enrt_actn_id=pcs.prtt_enrt_actn_id and pea.business_group_id=pcs.business_group_id and pil.per_in_ler_id(+)=pea.per_in_ler_id and pil.business_group_id(+)=pea.business_group_id+0 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') or pil.per_in_ler_stat_cd is null ) and PEN.PRTT_ENRT_RSLT_ID = PCS.PRTT_ENRT_RSLT_ID and OPT2.OIPL_ID (+) = PEN.OIPL_ID and OPT.OPT_ID (+) = OPT2.OPT_ID and PROGRAM.PGM_ID (+) = PEN.PGM_ID and PLAN.PL_ID (+) = PEN.PL_ID and PLTYP.PL_TYP_ID (+) = PEN.PL_TYP_ID and LER.LER_ID (+) = PEN.LER_ID and pen.prtt_enrt_rslt_stat_cd is null and pil.per_in_ler_id=pen.per_in_ler_id and pcs.effective_start_date between pea.effective_start_date and pea.effective_end_date and pcs.effective_start_date between pen.effective_start_date and pen.effective_end_date and pcs.effective_start_date between nvl( opt.effective_start_date, pcs.effective_start_date) and nvl(opt.effective_end_date, pcs.effective_start_date) and pcs.effective_start_date between nvl( opt2.effective_start_date, pcs.effective_start_date) and nvl(opt2.effective_end_date, pcs.effective_start_date) and pcs.effective_start_date between nvl(program.effective_start_date, pcs.effective_start_date) and nvl(program.effective_end_date, pcs.effective_start_date) and pcs.effective_start_date between nvl( plan.effective_start_date, pcs.effective_start_date) and nvl(plan.effective_end_date, pcs.effective_start_date) and pcs.effective_start_date between nvl(pltyp.effective_start_date, pcs.effective_start_date) and nvl(pltyp.effective_end_date, pcs.effective_start_date) and pcs.effective_start_date between nvl( ler.effective_start_date, pcs.effective_start_date) and nvl(ler.effective_end_date, pcs.effective_start_date)

View Text - HTML Formatted

SELECT PCS.ROWID ROW_ID
, PCS.PRTT_ENRT_CTFN_PRVDD_ID
, PCS.EFFECTIVE_START_DATE
, PCS.EFFECTIVE_END_DATE
, ENRT_CTFN_TYP.MEANING ENRT_CTFN_TYP_MEANING
, DECODE(PEN.SSPNDD_FLAG
, 'Y'
, 'YES'
, 'NO') SSPNDD_FLAG
, PEA.DUE_DT
, ENRT_CTFN_RQD.MEANING ENRT_CTFN_RQD_MEANING
, ENRT_R_BNFT_CTFN.MEANING ENRT_R_BNFT_CTFN_MEANING
, DECODE(PCS.ENRT_CTFN_RECD_DT
, TO_DATE(NULL)
, 'NO'
, 'YES') RECVD_FLAG
, PCS.ENRT_CTFN_RECD_DT
, DECODE(PCS.ENRT_CTFN_DND_DT
, TO_DATE(NULL)
, 'NO'
, 'YES') DND_FLAG
, PCS.ENRT_CTFN_DND_DT
, PLAN.NAME PLN_NAME
, PLTYP.NAME PL_TYP_NAME
, PROGRAM.NAME PGM_NAME
, OPT.NAME OPT_NAME
, LER.NAME LER_NAME
, PCS.LAST_UPDATE_DATE
, FUSER.USER_NAME LAST_UPDATED_BY
FROM BEN_PRTT_ENRT_CTFN_PRVDD_F PCS
, BEN_PRTT_ENRT_ACTN_F PEA
, BEN_PER_IN_LER PIL
, FND_USER FUSER
, HR_LOOKUPS ENRT_CTFN_TYP
, HR_LOOKUPS ENRT_R_BNFT_CTFN
, HR_LOOKUPS ENRT_CTFN_RQD
, BEN_PRTT_ENRT_RSLT_F PEN
, BEN_OPT_F OPT
, BEN_OIPL_F OPT2
, BEN_PGM_F PROGRAM
, BEN_PL_F PLAN
, BEN_LER_F LER
, BEN_PL_TYP_F PLTYP
WHERE FUSER.USER_ID (+)= PCS.LAST_UPDATED_BY
AND ENRT_CTFN_TYP.LOOKUP_CODE (+)= PCS.ENRT_CTFN_TYP_CD
AND ENRT_CTFN_TYP.LOOKUP_TYPE (+)= 'ENRT_CTFN_TYP'
AND ENRT_R_BNFT_CTFN.LOOKUP_CODE (+)= PCS.ENRT_R_BNFT_CTFN_CD
AND ENRT_R_BNFT_CTFN.LOOKUP_TYPE (+)= 'ENRT_R_BNFT_CTFN'
AND ENRT_CTFN_RQD.LOOKUP_CODE (+)= PCS.ENRT_CTFN_RQD_FLAG
AND ENRT_CTFN_RQD.LOOKUP_TYPE (+)= 'YES_NO'
AND PEA.PRTT_ENRT_ACTN_ID=PCS.PRTT_ENRT_ACTN_ID
AND PEA.BUSINESS_GROUP_ID=PCS.BUSINESS_GROUP_ID
AND PIL.PER_IN_LER_ID(+)=PEA.PER_IN_LER_ID
AND PIL.BUSINESS_GROUP_ID(+)=PEA.BUSINESS_GROUP_ID+0
AND ( PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD'
, 'BCKDT') OR PIL.PER_IN_LER_STAT_CD IS NULL )
AND PEN.PRTT_ENRT_RSLT_ID = PCS.PRTT_ENRT_RSLT_ID
AND OPT2.OIPL_ID (+) = PEN.OIPL_ID
AND OPT.OPT_ID (+) = OPT2.OPT_ID
AND PROGRAM.PGM_ID (+) = PEN.PGM_ID
AND PLAN.PL_ID (+) = PEN.PL_ID
AND PLTYP.PL_TYP_ID (+) = PEN.PL_TYP_ID
AND LER.LER_ID (+) = PEN.LER_ID
AND PEN.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND PIL.PER_IN_LER_ID=PEN.PER_IN_LER_ID
AND PCS.EFFECTIVE_START_DATE BETWEEN PEA.EFFECTIVE_START_DATE
AND PEA.EFFECTIVE_END_DATE
AND PCS.EFFECTIVE_START_DATE BETWEEN PEN.EFFECTIVE_START_DATE
AND PEN.EFFECTIVE_END_DATE
AND PCS.EFFECTIVE_START_DATE BETWEEN NVL( OPT.EFFECTIVE_START_DATE
, PCS.EFFECTIVE_START_DATE)
AND NVL(OPT.EFFECTIVE_END_DATE
, PCS.EFFECTIVE_START_DATE)
AND PCS.EFFECTIVE_START_DATE BETWEEN NVL( OPT2.EFFECTIVE_START_DATE
, PCS.EFFECTIVE_START_DATE)
AND NVL(OPT2.EFFECTIVE_END_DATE
, PCS.EFFECTIVE_START_DATE)
AND PCS.EFFECTIVE_START_DATE BETWEEN NVL(PROGRAM.EFFECTIVE_START_DATE
, PCS.EFFECTIVE_START_DATE)
AND NVL(PROGRAM.EFFECTIVE_END_DATE
, PCS.EFFECTIVE_START_DATE)
AND PCS.EFFECTIVE_START_DATE BETWEEN NVL( PLAN.EFFECTIVE_START_DATE
, PCS.EFFECTIVE_START_DATE)
AND NVL(PLAN.EFFECTIVE_END_DATE
, PCS.EFFECTIVE_START_DATE)
AND PCS.EFFECTIVE_START_DATE BETWEEN NVL(PLTYP.EFFECTIVE_START_DATE
, PCS.EFFECTIVE_START_DATE)
AND NVL(PLTYP.EFFECTIVE_END_DATE
, PCS.EFFECTIVE_START_DATE)
AND PCS.EFFECTIVE_START_DATE BETWEEN NVL( LER.EFFECTIVE_START_DATE
, PCS.EFFECTIVE_START_DATE)
AND NVL(LER.EFFECTIVE_END_DATE
, PCS.EFFECTIVE_START_DATE)