View: BEN_PER_CM_PRVDD_D

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

View Text

SELECT PCD.ROWID ROW_ID
, PCD.PER_CM_PRVDD_ID
, PCD.EFFECTIVE_START_DATE
, PCD.EFFECTIVE_END_DATE
, CMT.NAME CM_TYP
, PCD.INSTNC_NUM
, PCD.TO_BE_SENT_DT
, INSPN_RQD.MEANING INSPN_RQD_MEANING
, PCD.SENT_DT
, RQSTD.MEANING RQSTD_MEANING
, PER_CM.RQSTBL_UNTL_DT
, PER_CM.LF_EVT_OCRD_DT
, LER.NAME LER_NAME
, RESND_RSN.MEANING RESND_RSN
, PCD.RESND_CMNT_TXT
, CM_DLVRY_MTHD.MEANING CM_DLVRY_MTHD_MEANING
, CM_DLVRY_MED.MEANING CM_DLVRY_MED_MEANING
, PCD.DLVRY_INSTN_TXT
, ADDRESS.ADDRESS_LINE1||'
, '||TOWN_OR_CITY||'
, '||REGION_2||' ' ||POSTAL_CODE ADDRESS
, PCD.LAST_UPDATE_DATE
, FUSER.USER_NAME LAST_UPDATED_BY
FROM BEN_PER_CM_PRVDD_F PCD
, BEN_PER_IN_LER PIL
, BEN_LER_F LER
, FND_USER_VIEW FUSER
, BEN_PER_CM_F PER_CM
, BEN_CM_TYP_F CMT
, PER_ADDRESSES ADDRESS
, HR_LOOKUPS PER_CM_PRVDD_STAT
, HR_LOOKUPS CM_DLVRY_MED
, HR_LOOKUPS CM_DLVRY_MTHD
, HR_LOOKUPS RQSTD
, HR_LOOKUPS RESND_RSN
, HR_LOOKUPS INSPN_RQD
WHERE FUSER.USER_ID (+)= PCD.LAST_UPDATED_BY
AND PER_CM.PER_CM_ID (+)= PCD.PER_CM_ID
AND PER_CM.CM_TYP_ID = CMT.CM_TYP_ID(+)
AND LER.LER_ID(+) = PER_CM.LER_ID
AND ADDRESS.ADDRESS_ID (+)= PCD.ADDRESS_ID
AND PER_CM_PRVDD_STAT.LOOKUP_CODE (+)= PCD.PER_CM_PRVDD_STAT_CD
AND PER_CM_PRVDD_STAT.LOOKUP_TYPE (+)= 'BEN_PER_CM_PRVDD_STAT'
AND CM_DLVRY_MED.LOOKUP_CODE (+)= PCD.CM_DLVRY_MED_CD
AND CM_DLVRY_MED.LOOKUP_TYPE (+)= 'BEN_DLVRY_MED'
AND RESND_RSN.LOOKUP_CODE (+)= PCD.RESND_RSN_CD
AND RESND_RSN.LOOKUP_TYPE (+)= 'BEN_RESND_RSN'
AND CM_DLVRY_MTHD.LOOKUP_CODE (+)= PCD.CM_DLVRY_MTHD_CD
AND CM_DLVRY_MTHD.LOOKUP_TYPE (+)= 'BEN_DLVRY_MTHD'
AND RQSTD.LOOKUP_CODE (+)= PCD.RQSTD_FLAG
AND RQSTD.LOOKUP_TYPE (+)= 'YES_NO'
AND INSPN_RQD.LOOKUP_CODE (+)= PCD.INSPN_RQD_FLAG
AND INSPN_RQD.LOOKUP_TYPE (+)= 'YES_NO'
AND PIL.PER_IN_LER_ID(+)=PER_CM.PER_IN_LER_ID
AND PIL.BUSINESS_GROUP_ID(+)=PER_CM.BUSINESS_GROUP_ID
AND ( PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD'
, 'BCKDT') OR PIL.PER_IN_LER_STAT_CD IS NULL OR PCD.SENT_DT IS NOT NULL )
AND PCD.EFFECTIVE_START_DATE BETWEEN NVL( CMT.EFFECTIVE_START_DATE
, PCD.EFFECTIVE_START_DATE)
AND NVL(CMT.EFFECTIVE_END_DATE
, PCD.EFFECTIVE_START_DATE)
AND PCD.EFFECTIVE_START_DATE BETWEEN NVL( PER_CM.EFFECTIVE_START_DATE
, PCD.EFFECTIVE_START_DATE)
AND NVL(PER_CM.EFFECTIVE_END_DATE
, PCD.EFFECTIVE_START_DATE)
AND PCD.EFFECTIVE_START_DATE BETWEEN NVL( LER.EFFECTIVE_START_DATE
, PCD.EFFECTIVE_START_DATE)
AND NVL(LER.EFFECTIVE_END_DATE
, PCD.EFFECTIVE_START_DATE)

Columns