Product: | BEN - Advanced Benefits |
---|---|
Description: | - Retrofitted |
Implementation/DBA Data: | APPS.BEN_ELIG_MRTL_STAT_CVG_D |
SELECT EMS.ROWID ROW_ID
, EMS.ELIG_MRTL_STAT_CVG_ID
, EMS.EFFECTIVE_START_DATE
, EMS.EFFECTIVE_END_DATE
, DPNT_CVG_ELIGY_PRFL.NAME
, MRTL_STAT.MEANING
, CVG_STRT.MEANING
, CVG_STRT2.FORMULA_NAME
, CVG_THRU.MEANING
, CVG_THRU2.FORMULA_NAME
, EMS.LAST_UPDATE_DATE
, FUSER.LAST_UPDATED_BY
FROM BEN_ELIG_MRTL_STAT_CVG_F EMS
, FND_USER FUSER
, BEN_DPNT_CVG_ELIGY_PRFL_F DPNT_CVG_ELIGY_PRFL
, HR_LOOKUPS MRTL_STAT
, HR_LOOKUPS CVG_STRT
, HR_LOOKUPS CVG_THRU
, FF_FORMULAS_F CVG_STRT2
, FF_FORMULAS_F CVG_THRU2
WHERE FUSER.USER_ID (+)= EMS.LAST_UPDATED_BY
AND DPNT_CVG_ELIGY_PRFL.DPNT_CVG_ELIGY_PRFL_ID (+)= EMS.DPNT_CVG_ELIGY_PRFL_ID
AND MRTL_STAT.LOOKUP_CODE (+)= EMS.MRTL_STAT_CD
AND MRTL_STAT.LOOKUP_TYPE (+)= 'MAR_STATUS'
AND CVG_STRT.LOOKUP_CODE (+)= EMS.CVG_STRT_CD
AND CVG_STRT.LOOKUP_TYPE (+)= 'BEN_DPNT_CVG_STRT'
AND CVG_THRU.LOOKUP_CODE (+)= EMS.CVG_THRU_CD
AND CVG_THRU.LOOKUP_TYPE (+)= 'BEN_DPNT_CVG_END'
AND CVG_STRT2.FORMULA_ID (+)= EMS.CVG_STRT_RL
AND EMS.EFFECTIVE_START_DATE BETWEEN CVG_STRT2.EFFECTIVE_START_DATE(+)
AND CVG_STRT2.EFFECTIVE_END_DATE(+)
AND CVG_THRU2.FORMULA_ID (+)= EMS.CVG_THRU_RL
AND EMS.EFFECTIVE_START_DATE BETWEEN CVG_THRU2.EFFECTIVE_START_DATE(+)
AND CVG_THRU2.EFFECTIVE_END_DATE(+)