VIEW: APPS.BEN_CVG_AMT_CALC_MTHD_V

Source

View Text - Preformatted

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)

View Text - HTML Formatted

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)