VIEW: APPS.BEN_PRTT_ENRT_ACTN_D

Source

View Text - Preformatted

SELECT PEA.PRTT_ENRT_ACTN_ID, PEA.EFFECTIVE_START_DATE, PEA.EFFECTIVE_END_DATE, OPT.NAME option_name, PROGRAM.NAME program_name, PLAN.NAME plan_name, PLTYP.NAME plan_typ_name, LER.NAME life_event_name, RQD.MEANING rqd_meaning, PEA.CMPLTD_DT, PEA.DUE_DT, ACTNTYP.NAME action_type_name, ACTNTYP.DESCRIPTION action_type_description, PERSON.FULL_NAME dpnt_person_name, PEA.LAST_UPDATE_DATE, fuser.LAST_UPDATED_BY FROM BEN_PRTT_ENRT_RSLT_F PEN, BEN_PRTT_ENRT_ACTN_F PEA, ben_per_in_ler pil, 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, BEN_ACTN_TYP ACTNTYP, fnd_sessions se, BEN_ELIG_CVRD_DPNT_F ELIG, PER_ALL_PEOPLE_F PERSON, HR_LOOKUPS RQD, fnd_user fuser WHERE PEN.PRTT_ENRT_RSLT_ID = PEA.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 ACTNTYP.ACTN_TYP_ID (+) = PEA.ACTN_TYP_ID and ELIG.ELIG_CVRD_DPNT_ID (+) = PEA.ELIG_CVRD_DPNT_ID and PERSON.PERSON_ID (+) = ELIG.DPNT_PERSON_ID and pil.per_in_ler_id(+) = pea.per_in_ler_id and pil.business_group_id(+)=pea.business_group_id+0 and RQD.lookup_code = PEA.RQD_FLAG and RQD.lookup_type = 'YES_NO' and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') or pil.per_in_ler_stat_cd is null ) AND SE.SESSION_ID = USERENV('sessionid') AND SE.EFFECTIVE_DATE BETWEEN PEN.EFFECTIVE_START_DATE AND PEN.EFFECTIVE_END_DATE AND SE.EFFECTIVE_DATE BETWEEN PEA.EFFECTIVE_START_DATE AND PEA.EFFECTIVE_END_DATE and fuser.user_id (+)= PEA.last_updated_by

View Text - HTML Formatted

SELECT PEA.PRTT_ENRT_ACTN_ID
, PEA.EFFECTIVE_START_DATE
, PEA.EFFECTIVE_END_DATE
, OPT.NAME OPTION_NAME
, PROGRAM.NAME PROGRAM_NAME
, PLAN.NAME PLAN_NAME
, PLTYP.NAME PLAN_TYP_NAME
, LER.NAME LIFE_EVENT_NAME
, RQD.MEANING RQD_MEANING
, PEA.CMPLTD_DT
, PEA.DUE_DT
, ACTNTYP.NAME ACTION_TYPE_NAME
, ACTNTYP.DESCRIPTION ACTION_TYPE_DESCRIPTION
, PERSON.FULL_NAME DPNT_PERSON_NAME
, PEA.LAST_UPDATE_DATE
, FUSER.LAST_UPDATED_BY
FROM BEN_PRTT_ENRT_RSLT_F PEN
, BEN_PRTT_ENRT_ACTN_F PEA
, BEN_PER_IN_LER PIL
, 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
, BEN_ACTN_TYP ACTNTYP
, FND_SESSIONS SE
, BEN_ELIG_CVRD_DPNT_F ELIG
, PER_ALL_PEOPLE_F PERSON
, HR_LOOKUPS RQD
, FND_USER FUSER
WHERE PEN.PRTT_ENRT_RSLT_ID = PEA.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 ACTNTYP.ACTN_TYP_ID (+) = PEA.ACTN_TYP_ID
AND ELIG.ELIG_CVRD_DPNT_ID (+) = PEA.ELIG_CVRD_DPNT_ID
AND PERSON.PERSON_ID (+) = ELIG.DPNT_PERSON_ID
AND PIL.PER_IN_LER_ID(+) = PEA.PER_IN_LER_ID
AND PIL.BUSINESS_GROUP_ID(+)=PEA.BUSINESS_GROUP_ID+0
AND RQD.LOOKUP_CODE = PEA.RQD_FLAG
AND RQD.LOOKUP_TYPE = 'YES_NO'
AND ( PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD'
, 'BCKDT') OR PIL.PER_IN_LER_STAT_CD IS NULL )
AND SE.SESSION_ID = USERENV('SESSIONID')
AND SE.EFFECTIVE_DATE BETWEEN PEN.EFFECTIVE_START_DATE
AND PEN.EFFECTIVE_END_DATE
AND SE.EFFECTIVE_DATE BETWEEN PEA.EFFECTIVE_START_DATE
AND PEA.EFFECTIVE_END_DATE
AND FUSER.USER_ID (+)= PEA.LAST_UPDATED_BY