Product: | BEN - Advanced Benefits |
---|---|
Description: | - Retrofitted |
Implementation/DBA Data: | APPS.BEN_CVG_AMT_CALC_MTHD_V |
SELECT CCM.ROWID ROW_ID
, CCM.CVG_AMT_CALC_MTHD_ID
, CCM.EFFECTIVE_START_DATE
, CCM.EFFECTIVE_END_DATE
, CCM.NAME
, CCM.INCRMT_VAL
, CCM.MX_VAL
, CCM.MN_VAL
, CCM.NO_MX_VAL_DFND_FLAG
, CCM.NO_MN_VAL_DFND_FLAG
, CCM.RNDG_CD
, CCM.RNDG_RL
, CCM.VAL
, CCM.VAL_OVRID_ALWD_FLAG
, CCM.VAL_CALC_RL
, CCM.UOM
, CCM.NNMNTRY_UOM
, CCM.BNDRY_PERD_CD
, CCM.BNFT_TYP_CD
, CCM.CVG_MLT_CD
, CCM.RT_TYP_CD
, CCM.DFLT_VAL
, CCM.ENTR_VAL_AT_ENRT_FLAG
, CCM.DFLT_FLAG
, CCM.COMP_LVL_FCTR_ID
, CCM.OIPL_ID
, CCM.PL_ID
, CCM.BUSINESS_GROUP_ID
, CCM.CCM_ATTRIBUTE_CATEGORY
, CCM.CCM_ATTRIBUTE1
, CCM.CCM_ATTRIBUTE2
, CCM.CCM_ATTRIBUTE3
, CCM.CCM_ATTRIBUTE4
, CCM.CCM_ATTRIBUTE5
, CCM.CCM_ATTRIBUTE6
, CCM.CCM_ATTRIBUTE7
, CCM.CCM_ATTRIBUTE8
, CCM.CCM_ATTRIBUTE9
, CCM.CCM_ATTRIBUTE10
, CCM.CCM_ATTRIBUTE11
, CCM.CCM_ATTRIBUTE12
, CCM.CCM_ATTRIBUTE13
, CCM.CCM_ATTRIBUTE14
, CCM.CCM_ATTRIBUTE15
, CCM.CCM_ATTRIBUTE16
, CCM.CCM_ATTRIBUTE17
, CCM.CCM_ATTRIBUTE18
, CCM.CCM_ATTRIBUTE19
, CCM.CCM_ATTRIBUTE20
, CCM.CCM_ATTRIBUTE21
, CCM.CCM_ATTRIBUTE22
, CCM.CCM_ATTRIBUTE23
, CCM.CCM_ATTRIBUTE24
, CCM.CCM_ATTRIBUTE25
, CCM.CCM_ATTRIBUTE26
, CCM.CCM_ATTRIBUTE27
, CCM.CCM_ATTRIBUTE28
, CCM.CCM_ATTRIBUTE29
, CCM.CCM_ATTRIBUTE30
, CCM.LAST_UPDATE_DATE
, CCM.LAST_UPDATED_BY
, CCM.LAST_UPDATE_LOGIN
, CCM.CREATED_BY
, CCM.CREATION_DATE
, CCM.OBJECT_VERSION_NUMBER
, CCM.LWR_LMT_VAL
, CCM.LWR_LMT_CALC_RL
, CCM.UPR_LMT_VAL
, CCM.UPR_LMT_CALC_RL
, CCM.PLIP_ID
, NVL(COP.PL_ID
, CCM.PL_ID) /* EITHER PLAN OR OIPL LEVEL CCM REC TO BE FETCHED */
, CPP.PL_ID
FROM BEN_CVG_AMT_CALC_MTHD_F CCM
, BEN_OIPL_F COP
, BEN_PLIP_F CPP
, FND_SESSIONS SE
WHERE SE.SESSION_ID = USERENV('SESSIONID')
AND SE.EFFECTIVE_DATE BETWEEN CCM.EFFECTIVE_START_DATE
AND CCM.EFFECTIVE_END_DATE
AND COP.OIPL_ID(+) = CCM.OIPL_ID
AND SE.EFFECTIVE_DATE BETWEEN NVL(COP.EFFECTIVE_START_DATE
, SE.EFFECTIVE_DATE)
AND NVL(COP.EFFECTIVE_END_DATE
, SE.EFFECTIVE_DATE)
AND CPP.PLIP_ID(+) = CCM.PLIP_ID
AND SE.EFFECTIVE_DATE BETWEEN NVL(CPP.EFFECTIVE_START_DATE
, SE.EFFECTIVE_DATE)
AND NVL(CPP.EFFECTIVE_END_DATE
, SE.EFFECTIVE_DATE)