View: BEN_CNTPT_V

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

View Text

SELECT PEN.PERSON_ID
, PLN.NAME
, PLT.NAME
, OPT.NAME
, BNFT_TYP.MEANING
, PEN.BNFT_AMT
, UOM.NAME
, NNMNTRY_UOM.MEANING
, PEN.PRTT_IS_CVRD_FLAG
, PRV.RT_STRT_DT
, PRV.RT_END_DT
, RT_TYP.MEANING
, PRV.RT_VAL
, TX_TYP.MEANING
, ACTY_TYP.MEANING
, EE.SCREEN_ENTRY_VALUE
, PEN.PL_ID
, ABR.INPUT_VALUE_ID
, ABR.ACTY_BASE_RT_ID
, PEN.OIPL_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
, PAY_ELEMENT_ENTRY_VALUES_F EE
, BEN_ACTY_BASE_RT_F ABR
, BEN_PL_F PLN
, BEN_PL_TYP_F PLT
, BEN_OIPL_F OIPL
, BEN_OPT_F OPT
, HR_LOOKUPS BNFT_TYP
, HR_LOOKUPS RT_TYP
, HR_LOOKUPS TX_TYP
, HR_LOOKUPS ACTY_TYP
, HR_LOOKUPS NNMNTRY_UOM
, FND_CURRENCIES_VL 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 PRV.ELEMENT_ENTRY_VALUE_ID = EE.ELEMENT_ENTRY_VALUE_ID
AND SE.EFFECTIVE_DATE BETWEEN EE.EFFECTIVE_START_DATE
AND EE.EFFECTIVE_END_DATE
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.INVK_FLX_CR_PL_FLAG = 'N'
AND PEN.PL_ID = PLN.PL_ID
AND SE.EFFECTIVE_DATE BETWEEN PLN.EFFECTIVE_START_DATE
AND PLN.EFFECTIVE_END_DATE
AND PEN.PL_TYP_ID = PLT.PL_TYP_ID
AND SE.EFFECTIVE_DATE BETWEEN PLT.EFFECTIVE_START_DATE
AND PLT.EFFECTIVE_END_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.BNFT_TYP_CD = BNFT_TYP.LOOKUP_CODE(+)
AND BNFT_TYP.LOOKUP_TYPE(+) = 'BEN_BNFT_TYP'
AND SE.EFFECTIVE_DATE BETWEEN NVL(BNFT_TYP.START_DATE_ACTIVE
, SE.EFFECTIVE_DATE)
AND NVL(BNFT_TYP.END_DATE_ACTIVE
, SE.EFFECTIVE_DATE)
AND PEN.UOM = UOM.CURRENCY_CODE(+)
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 PRV.RT_TYP_CD = RT_TYP.LOOKUP_CODE(+)
AND RT_TYP.LOOKUP_TYPE(+) = 'BEN_RT_TYP'
AND SE.EFFECTIVE_DATE BETWEEN NVL(RT_TYP.START_DATE_ACTIVE
, SE.EFFECTIVE_DATE)
AND NVL(RT_TYP.END_DATE_ACTIVE
, SE.EFFECTIVE_DATE)
AND PRV.TX_TYP_CD = TX_TYP.LOOKUP_CODE(+)
AND TX_TYP.LOOKUP_TYPE(+) = 'BEN_TX_TYP'
AND SE.EFFECTIVE_DATE BETWEEN NVL(TX_TYP.START_DATE_ACTIVE
, SE.EFFECTIVE_DATE)
AND NVL(TX_TYP.END_DATE_ACTIVE
, SE.EFFECTIVE_DATE)
AND PRV.ACTY_TYP_CD = ACTY_TYP.LOOKUP_CODE(+)
AND ACTY_TYP.LOOKUP_TYPE(+) = 'BEN_ACTY_TYP'
AND SE.EFFECTIVE_DATE BETWEEN NVL(ACTY_TYP.START_DATE_ACTIVE
, SE.EFFECTIVE_DATE)
AND NVL(ACTY_TYP.END_DATE_ACTIVE
, SE.EFFECTIVE_DATE)

Columns