View: BEN_RCD_CNTR_AMT_V

Product:	BEN - Advanced Benefits
Description:
Implementation/DBA Data:	APPS.BEN_RCD_CNTR_AMT_V

View Text

SELECT PEN.PERSON_ID
, PGM.NAME
, PLN.NAME
, PLT.NAME
, OPT.NAME
, UOM.NAME
, NNMNTRY_UOM.MEANING
, ABR.NAME
, ABR.ACTY_BASE_RT_ID
, PEN.PL_ID
, PEN.OIPL_ID
, PLIP.PLIP_ID
, PEN.PRTT_ENRT_RSLT_ID
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE
, PEN.OBJECT_VERSION_NUMBER
FROM BEN_PRTT_ENRT_RSLT_F PEN
, BEN_PRTT_RT_VAL PRV
, BEN_ACTY_BASE_RT_F ABR
, BEN_PGM_F PGM
, BEN_PL_F PLN
, BEN_PL_TYP_F PLT
, BEN_OIPL_F OIPL
, BEN_OPT_F OPT
, BEN_PLIP_F PLIP
, FND_CURRENCIES_VL UOM
, HR_LOOKUPS NNMNTRY_UOM
, FND_SESSIONS SE
WHERE SE.SESSION_ID = USERENV('SESSIONID')
AND PEN.SSPNDD_FLAG <> 'Y'
AND PRV.ACTY_BASE_RT_ID = ABR.ACTY_BASE_RT_ID
AND SUBSTR(NVL(ABR.ACTY_TYP_CD
, 'XXX')
, 1
, 3) <> 'PBC'
AND PEN.PRTT_ENRT_RSLT_ID = PRV.PRTT_ENRT_RSLT_ID
AND SE.EFFECTIVE_DATE BETWEEN PRV.RT_STRT_DT
AND PRV.RT_END_DT
AND SE.EFFECTIVE_DATE BETWEEN ABR.EFFECTIVE_START_DATE
AND ABR.EFFECTIVE_END_DATE
AND SE.EFFECTIVE_DATE BETWEEN PEN.ENRT_CVG_STRT_DT
AND PEN.ENRT_CVG_THRU_DT
AND PEN.ENRT_CVG_THRU_DT <= PEN.EFFECTIVE_END_DATE
AND PLN.IMPTD_INCM_CALC_CD IS NULL
AND PEN.PGM_ID = PGM.PGM_ID(+)
AND SE.EFFECTIVE_DATE BETWEEN NVL( PGM.EFFECTIVE_START_DATE
, SE.EFFECTIVE_DATE)
AND NVL(PGM.EFFECTIVE_END_DATE
, SE.EFFECTIVE_DATE)
AND PEN.PL_ID = PLN.PL_ID(+)
AND SE.EFFECTIVE_DATE BETWEEN NVL(PLN.EFFECTIVE_START_DATE
, SE.EFFECTIVE_DATE)
AND NVL(PLN.EFFECTIVE_END_DATE
, SE.EFFECTIVE_DATE)
AND PEN.PL_TYP_ID = PLT.PL_TYP_ID(+)
AND SE.EFFECTIVE_DATE BETWEEN NVL( PLT.EFFECTIVE_START_DATE
, SE.EFFECTIVE_DATE)
AND NVL(PLT.EFFECTIVE_END_DATE
, SE.EFFECTIVE_DATE)
AND PEN.OIPL_ID = OIPL.OIPL_ID(+)
AND SE.EFFECTIVE_DATE BETWEEN NVL(OIPL.EFFECTIVE_START_DATE
, SE.EFFECTIVE_DATE)
AND NVL(OIPL.EFFECTIVE_END_DATE
, SE.EFFECTIVE_DATE)
AND OIPL.OPT_ID = OPT.OPT_ID(+)
AND SE.EFFECTIVE_DATE BETWEEN NVL(OPT.EFFECTIVE_START_DATE
, SE.EFFECTIVE_DATE)
AND NVL(OPT.EFFECTIVE_END_DATE
, SE.EFFECTIVE_DATE)
AND PEN.UOM = UOM.CURRENCY_CODE(+)
AND SE.EFFECTIVE_DATE BETWEEN NVL(UOM.START_DATE_ACTIVE
, SE.EFFECTIVE_DATE)
AND NVL(UOM.END_DATE_ACTIVE
, SE.EFFECTIVE_DATE)
AND UOM.ENABLED_FLAG = 'Y'
AND PEN.BNFT_NNMNTRY_UOM = NNMNTRY_UOM.LOOKUP_CODE(+)
AND NNMNTRY_UOM.LOOKUP_TYPE(+) = 'BEN_NNMNTRY_UOM'
AND SE.EFFECTIVE_DATE BETWEEN NVL(NNMNTRY_UOM.START_DATE_ACTIVE
, SE.EFFECTIVE_DATE)
AND NVL(NNMNTRY_UOM.END_DATE_ACTIVE
, SE.EFFECTIVE_DATE)
AND PEN.PGM_ID = PLIP.PGM_ID(+)
AND PEN.PL_ID = PLIP.PL_ID(+)
AND SE.EFFECTIVE_DATE BETWEEN NVL(PLIP.EFFECTIVE_START_DATE
, SE.EFFECTIVE_DATE)
AND NVL(PLIP.EFFECTIVE_END_DATE
, SE.EFFECTIVE_DATE)

Columns