VIEW: APPS.BEN_OIPL_D

Source

View Text - Preformatted

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' AND cop.effective_start_date BETWEEN opt.effective_start_date(+) AND opt.effective_end_date(+) AND cop.effective_start_date BETWEEN pl.effective_start_date(+) AND pl.effective_end_date(+) AND cop.effective_start_date BETWEEN actl_prem.effective_start_date(+) AND actl_prem.effective_end_date(+)

View Text - HTML Formatted

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'
AND COP.EFFECTIVE_START_DATE BETWEEN OPT.EFFECTIVE_START_DATE(+)
AND OPT.EFFECTIVE_END_DATE(+)
AND COP.EFFECTIVE_START_DATE BETWEEN PL.EFFECTIVE_START_DATE(+)
AND PL.EFFECTIVE_END_DATE(+)
AND COP.EFFECTIVE_START_DATE BETWEEN ACTL_PREM.EFFECTIVE_START_DATE(+)
AND ACTL_PREM.EFFECTIVE_END_DATE(+)