View: BEN_OIPL_D

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

View Text

SELECT COP.ROWID ROW_ID
, COP.OIPL_ID
, COP.EFFECTIVE_START_DATE
, COP.EFFECTIVE_END_DATE
, OPT.NAME
, PL.NAME
, COP.ORDR_NUM
, DFLT.MEANING
, ACTL_PREM.NAME
, MNDTRY.MEANING
, OIPL_STAT.MEANING
, ELIG_APLS.MEANING
, DFLT_ENRT_DET.FORMULA_NAME
, TRK_INELIG_PER.MEANING
, DRVBL_FCTR_PRTN_ELIG.MEANING
, MNDTRY2.FORMULA_NAME
, DFLT_ENRT.MEANING
, PRTN_ELIG_OVRID_ALWD.MEANING
, DRVBL_FCTR_APLS_RTS.MEANING
, PER_CVRD.MEANING
, POSTELCN_EDIT.FORMULA_NAME
, VRFY_FMLY_MMBR.MEANING
, VRFY_FMLY_MMBR2.FORMULA_NAME
, COP.LAST_UPDATE_DATE
, FUSER.USER_NAME
, PCP_DSGN.MEANING
, PCP_DPNT_DSGN.MEANING
, COP.SHORT_NAME
, COP.SHORT_CODE
, COP.HIDDEN_FLAG
, COP.LEGISLATION_CODE
, COP.LEGISLATION_SUBGROUP
FROM BEN_OIPL_F COP
, FND_USER FUSER
, BEN_OPT_F OPT
, BEN_PL_F PL
, BEN_ACTL_PREM_F ACTL_PREM
, HR_LOOKUPS OIPL_STAT
, HR_LOOKUPS DFLT_ENRT
, HR_LOOKUPS PER_CVRD
, HR_LOOKUPS VRFY_FMLY_MMBR
, HR_LOOKUPS DFLT
, HR_LOOKUPS MNDTRY
, HR_LOOKUPS ELIG_APLS
, HR_LOOKUPS TRK_INELIG_PER
, HR_LOOKUPS DRVBL_FCTR_PRTN_ELIG
, HR_LOOKUPS PRTN_ELIG_OVRID_ALWD
, HR_LOOKUPS DRVBL_FCTR_APLS_RTS
, HR_LOOKUPS PCP_DSGN
, HR_LOOKUPS PCP_DPNT_DSGN
, FF_FORMULAS_F DFLT_ENRT_DET
, FF_FORMULAS_F MNDTRY2
, FF_FORMULAS_F POSTELCN_EDIT
, FF_FORMULAS_F VRFY_FMLY_MMBR2
WHERE FUSER.USER_ID (+)= COP.LAST_UPDATED_BY
AND OPT.OPT_ID (+)= COP.OPT_ID
AND PL.PL_ID (+)= COP.PL_ID
AND ACTL_PREM.ACTL_PREM_ID (+)= COP.ACTL_PREM_ID
AND OIPL_STAT.LOOKUP_CODE (+)= COP.OIPL_STAT_CD
AND OIPL_STAT.LOOKUP_TYPE (+)= 'BEN_STAT'
AND DFLT_ENRT.LOOKUP_CODE (+)= COP.DFLT_ENRT_CD
AND DFLT_ENRT.LOOKUP_TYPE (+)= 'BEN_DFLT_ENRT'
AND PER_CVRD.LOOKUP_CODE (+)= COP.PER_CVRD_CD
AND PER_CVRD.LOOKUP_TYPE (+)= 'BEN_PER_CVRD'
AND VRFY_FMLY_MMBR.LOOKUP_CODE (+)= COP.VRFY_FMLY_MMBR_CD
AND VRFY_FMLY_MMBR.LOOKUP_TYPE (+)= 'BEN_FMLY_MMBR'
AND DFLT.LOOKUP_CODE (+)= COP.DFLT_FLAG
AND DFLT.LOOKUP_TYPE (+)= 'YES_NO'
AND MNDTRY.LOOKUP_CODE (+)= COP.MNDTRY_FLAG
AND MNDTRY.LOOKUP_TYPE (+)= 'YES_NO'
AND ELIG_APLS.LOOKUP_CODE (+)= COP.ELIG_APLS_FLAG
AND ELIG_APLS.LOOKUP_TYPE (+)= 'YES_NO'
AND TRK_INELIG_PER.LOOKUP_CODE (+)= COP.TRK_INELIG_PER_FLAG
AND TRK_INELIG_PER.LOOKUP_TYPE (+)='YES_NO'
AND DRVBL_FCTR_PRTN_ELIG.LOOKUP_CODE (+)= COP.DRVBL_FCTR_PRTN_ELIG_FLAG
AND DRVBL_FCTR_PRTN_ELIG.LOOKUP_TYPE (+)= 'YES_NO'
AND PRTN_ELIG_OVRID_ALWD.LOOKUP_CODE (+)= COP.PRTN_ELIG_OVRID_ALWD_FLAG
AND PRTN_ELIG_OVRID_ALWD.LOOKUP_TYPE (+)= 'YES_NO'
AND DRVBL_FCTR_APLS_RTS.LOOKUP_CODE (+)= COP.DRVBL_FCTR_APLS_RTS_FLAG
AND DRVBL_FCTR_APLS_RTS.LOOKUP_TYPE (+)= 'YES_NO'
AND DFLT_ENRT_DET.FORMULA_ID (+)= COP.DFLT_ENRT_DET_RL
AND COP.EFFECTIVE_START_DATE BETWEEN NVL(DFLT_ENRT_DET.EFFECTIVE_START_DATE
, COP.EFFECTIVE_START_DATE) AND NVL(DFLT_ENRT_DET.EFFECTIVE_END_DATE
, COP.EFFECTIVE_START_DATE)
AND MNDTRY2.FORMULA_ID (+)= COP.MNDTRY_RL
AND COP.EFFECTIVE_START_DATE BETWEEN NVL(MNDTRY2.EFFECTIVE_START_DATE
, COP.EFFECTIVE_START_DATE) AND NVL(MNDTRY2.EFFECTIVE_END_DATE
, COP.EFFECTIVE_START_DATE)
AND POSTELCN_EDIT.FORMULA_ID (+)= COP.POSTELCN_EDIT_RL
AND COP.EFFECTIVE_START_DATE BETWEEN NVL(POSTELCN_EDIT.EFFECTIVE_START_DATE
, COP.EFFECTIVE_START_DATE) AND NVL(POSTELCN_EDIT.EFFECTIVE_END_DATE
, COP.EFFECTIVE_START_DATE)
AND VRFY_FMLY_MMBR2.FORMULA_ID (+)= COP.VRFY_FMLY_MMBR_RL
AND COP.EFFECTIVE_START_DATE BETWEEN NVL(VRFY_FMLY_MMBR2.EFFECTIVE_START_DATE
, COP.EFFECTIVE_START_DATE) AND NVL(VRFY_FMLY_MMBR2.EFFECTIVE_END_DATE
, COP.EFFECTIVE_START_DATE)
AND PCP_DSGN.LOOKUP_CODE (+)= COP.PCP_DSGN_CD
AND PCP_DSGN.LOOKUP_TYPE (+)= 'BEN_PCP_DSGN'
AND PCP_DPNT_DSGN.LOOKUP_CODE (+)= COP.PCP_DPNT_DSGN_CD
AND PCP_DPNT_DSGN.LOOKUP_TYPE (+)= 'BEN_PCP_DSGN'

Columns