Product: | BEN - Advanced Benefits |
---|---|
Description: | - Retrofitted |
Implementation/DBA Data: | APPS.BEN_PL_REGN_D |
SELECT PRG.ROWID ROW_ID
, PRG.PL_REGN_ID
, PRG.EFFECTIVE_END_DATE
, PRG.EFFECTIVE_START_DATE
, REGN.NAME
, PL.NAME
, RPTG_GRP.NAME
, HGHLY_COMPD_DET.FORMULA_NAME
, KEY_EE_DET.FORMULA_NAME
, CNTR_NNDSCRN.FORMULA_NAME
, CVG_NNDSCRN.FORMULA_NAME
, FIVE_PCT_OWNR.FORMULA_NAME
, REGY_PL_TYP.MEANING
, PRG.LAST_UPDATE_DATE
, FUSER.LAST_UPDATED_BY
FROM BEN_PL_REGN_F PRG
, FND_USER FUSER
, BEN_REGN_F REGN
, BEN_PL_F PL
, BEN_RPTG_GRP RPTG_GRP
, HR_LOOKUPS REGY_PL_TYP
, FF_FORMULAS_F HGHLY_COMPD_DET
, FF_FORMULAS_F KEY_EE_DET
, FF_FORMULAS_F CNTR_NNDSCRN
, FF_FORMULAS_F CVG_NNDSCRN
, FF_FORMULAS_F FIVE_PCT_OWNR
WHERE FUSER.USER_ID (+)= PRG.LAST_UPDATED_BY
AND REGN.REGN_ID (+)= PRG.REGN_ID
AND PL.PL_ID (+)= PRG.PL_ID
AND RPTG_GRP.RPTG_GRP_ID (+)= PRG.RPTG_GRP_ID
AND REGY_PL_TYP.LOOKUP_CODE (+)= PRG.REGY_PL_TYP_CD
AND REGY_PL_TYP.LOOKUP_TYPE (+)= 'BEN_REGY_PL_TYP'
AND HGHLY_COMPD_DET.FORMULA_ID (+)= PRG.HGHLY_COMPD_DET_RL
AND PRG.EFFECTIVE_START_DATE BETWEEN NVL(HGHLY_COMPD_DET.EFFECTIVE_START_DATE
, PRG.EFFECTIVE_START_DATE)
AND NVL(HGHLY_COMPD_DET.EFFECTIVE_END_DATE
, PRG.EFFECTIVE_START_DATE)
AND KEY_EE_DET.FORMULA_ID (+)= PRG.KEY_EE_DET_RL
AND PRG.EFFECTIVE_START_DATE BETWEEN NVL(KEY_EE_DET.EFFECTIVE_START_DATE
, PRG.EFFECTIVE_START_DATE)
AND NVL(KEY_EE_DET.EFFECTIVE_END_DATE
, PRG.EFFECTIVE_START_DATE)
AND CNTR_NNDSCRN.FORMULA_ID (+)= PRG.CNTR_NNDSCRN_RL
AND PRG.EFFECTIVE_START_DATE BETWEEN NVL(CNTR_NNDSCRN.EFFECTIVE_START_DATE
, PRG.EFFECTIVE_START_DATE)
AND NVL(CNTR_NNDSCRN.EFFECTIVE_END_DATE
, PRG.EFFECTIVE_START_DATE)
AND CVG_NNDSCRN.FORMULA_ID (+)= PRG.CVG_NNDSCRN_RL
AND PRG.EFFECTIVE_START_DATE BETWEEN NVL(CVG_NNDSCRN.EFFECTIVE_START_DATE
, PRG.EFFECTIVE_START_DATE)
AND NVL(CVG_NNDSCRN.EFFECTIVE_END_DATE
, PRG.EFFECTIVE_START_DATE)
AND FIVE_PCT_OWNR.FORMULA_ID (+)= PRG.FIVE_PCT_OWNR_RL
AND PRG.EFFECTIVE_START_DATE BETWEEN NVL(FIVE_PCT_OWNR.EFFECTIVE_START_DATE
, PRG.EFFECTIVE_START_DATE)
AND NVL(FIVE_PCT_OWNR.EFFECTIVE_END_DATE
, PRG.EFFECTIVE_START_DATE)