VIEW: APPS.BEN_PER_CM_PRVDD_D

Source

View Text - Preformatted

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 )

View Text - HTML Formatted

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 )