VIEW: APPS.BEN_PER_CM_USG_D

Source

View Text - Preformatted

SELECT pcu.ROWID ROW_ID , pcu.PER_CM_USG_ID ,pcu.EFFECTIVE_START_DATE ,pcu.EFFECTIVE_END_DATE ,ler.NAME ler_name ,pgm.name pgm_name ,pl.name pl_name ,pl_typ.name pl_typ_name ,actn.name actn_name ,ep.strt_dt ,ep.end_dt ,pcu.LAST_UPDATE_DATE ,fuser.user_name last_updated_by from BEN_PER_CM_USG_F pcu , ben_per_cm_f pcm , ben_per_in_ler pil , fnd_user fuser ,BEN_CM_TYP_USG_F ctu ,ben_ler_f ler ,ben_pgm_f pgm ,ben_pl_f pl ,ben_pl_typ_f pl_typ ,ben_actn_typ actn ,ben_enrt_perd ep WHERE fuser.user_id (+)= pcu.last_updated_by and ctu.CM_TYP_USG_ID = pcu.CM_TYP_USG_ID and ctu.ler_id = ler.ler_id(+) and ctu.pgm_id = pgm.pgm_id(+) and ctu.pl_id = pl.pl_id(+) and ctu.pl_typ_id = pl_typ.pl_typ_id(+) and ctu.actn_typ_id = actn.actn_typ_id(+) and ctu.enrt_perd_id = ep.enrt_perd_id(+) and pcm.per_cm_id=pcu.per_cm_id and pcm.business_group_id = pcu.business_group_id and pil.per_in_ler_id(+)=pcm.per_in_ler_id and pil.business_group_id(+)=pcm.business_group_id and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') or pil.per_in_ler_stat_cd is null ) and pcu.effective_start_date between ctu.effective_start_date and ctu.effective_end_date and pcu.effective_start_date between nvl(ler.effective_start_date, pcu.effective_start_date) and nvl(ler.effective_end_date,pcu.effective_start_date) and pcu.effective_start_date between nvl(pgm.effective_start_date, pcu.effective_start_date) and nvl(pgm.effective_end_date,pcu.effective_start_date) and pcu.effective_start_date between nvl(pl.effective_start_date, pcu.effective_start_date) and nvl(pl.effective_end_date,pcu.effective_start_date) and pcu.effective_start_date between pcm.effective_start_date and pcm.effective_end_date and pcu.effective_start_date between nvl(pl_typ.effective_start_date, pcu.effective_start_date) and nvl(pl_typ.effective_end_date,pcu.effective_start_date)

View Text - HTML Formatted

SELECT PCU.ROWID ROW_ID
, PCU.PER_CM_USG_ID
, PCU.EFFECTIVE_START_DATE
, PCU.EFFECTIVE_END_DATE
, LER.NAME LER_NAME
, PGM.NAME PGM_NAME
, PL.NAME PL_NAME
, PL_TYP.NAME PL_TYP_NAME
, ACTN.NAME ACTN_NAME
, EP.STRT_DT
, EP.END_DT
, PCU.LAST_UPDATE_DATE
, FUSER.USER_NAME LAST_UPDATED_BY
FROM BEN_PER_CM_USG_F PCU
, BEN_PER_CM_F PCM
, BEN_PER_IN_LER PIL
, FND_USER FUSER
, BEN_CM_TYP_USG_F CTU
, BEN_LER_F LER
, BEN_PGM_F PGM
, BEN_PL_F PL
, BEN_PL_TYP_F PL_TYP
, BEN_ACTN_TYP ACTN
, BEN_ENRT_PERD EP
WHERE FUSER.USER_ID (+)= PCU.LAST_UPDATED_BY
AND CTU.CM_TYP_USG_ID = PCU.CM_TYP_USG_ID
AND CTU.LER_ID = LER.LER_ID(+)
AND CTU.PGM_ID = PGM.PGM_ID(+)
AND CTU.PL_ID = PL.PL_ID(+)
AND CTU.PL_TYP_ID = PL_TYP.PL_TYP_ID(+)
AND CTU.ACTN_TYP_ID = ACTN.ACTN_TYP_ID(+)
AND CTU.ENRT_PERD_ID = EP.ENRT_PERD_ID(+)
AND PCM.PER_CM_ID=PCU.PER_CM_ID
AND PCM.BUSINESS_GROUP_ID = PCU.BUSINESS_GROUP_ID
AND PIL.PER_IN_LER_ID(+)=PCM.PER_IN_LER_ID
AND PIL.BUSINESS_GROUP_ID(+)=PCM.BUSINESS_GROUP_ID
AND ( PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD'
, 'BCKDT') OR PIL.PER_IN_LER_STAT_CD IS NULL )
AND PCU.EFFECTIVE_START_DATE BETWEEN CTU.EFFECTIVE_START_DATE
AND CTU.EFFECTIVE_END_DATE
AND PCU.EFFECTIVE_START_DATE BETWEEN NVL(LER.EFFECTIVE_START_DATE
, PCU.EFFECTIVE_START_DATE)
AND NVL(LER.EFFECTIVE_END_DATE
, PCU.EFFECTIVE_START_DATE)
AND PCU.EFFECTIVE_START_DATE BETWEEN NVL(PGM.EFFECTIVE_START_DATE
, PCU.EFFECTIVE_START_DATE)
AND NVL(PGM.EFFECTIVE_END_DATE
, PCU.EFFECTIVE_START_DATE)
AND PCU.EFFECTIVE_START_DATE BETWEEN NVL(PL.EFFECTIVE_START_DATE
, PCU.EFFECTIVE_START_DATE)
AND NVL(PL.EFFECTIVE_END_DATE
, PCU.EFFECTIVE_START_DATE)
AND PCU.EFFECTIVE_START_DATE BETWEEN PCM.EFFECTIVE_START_DATE
AND PCM.EFFECTIVE_END_DATE
AND PCU.EFFECTIVE_START_DATE BETWEEN NVL(PL_TYP.EFFECTIVE_START_DATE
, PCU.EFFECTIVE_START_DATE)
AND NVL(PL_TYP.EFFECTIVE_END_DATE
, PCU.EFFECTIVE_START_DATE)