VIEW: APPS.BEN_CWB_WS_MGR_LOV_V

Source

View Text - Preformatted

SELECT distinct ppf.person_id id, FIRST_NAME ||' ' ||LAST_NAME || ' ' ||SUFFIX value, pil.person_id manager_id from per_all_people_f ppf, ben_elig_per_elctbl_chc chc, ben_cwb_mgr_hrchy hier, ben_cwb_mgr_hrchy hier2, ben_elig_per_elctbl_chc chc2, ben_per_in_ler pil, ben_per_in_ler pil2 WHERE pil.per_in_ler_id = chc.per_in_ler_id and chc.elig_per_elctbl_chc_id = hier.mgr_elig_per_elctbl_chc_id and hier.lvl_num > 0 and hier.emp_elig_per_elctbl_chc_id = chc2.elig_per_elctbl_chc_id and chc2.elig_flag = 'Y' and chc2.per_in_ler_id = pil2.per_in_ler_id and pil2.per_in_ler_stat_cd in ('STRTD','PROCD') and pil2.person_id = ppf.person_id and sysdate between ppf.effective_start_date and ppf.effective_end_date and chc2.elig_per_elctbl_chc_id = hier2.mgr_elig_per_elctbl_chc_id

View Text - HTML Formatted

SELECT DISTINCT PPF.PERSON_ID ID
, FIRST_NAME ||' ' ||LAST_NAME || ' ' ||SUFFIX VALUE
, PIL.PERSON_ID MANAGER_ID
FROM PER_ALL_PEOPLE_F PPF
, BEN_ELIG_PER_ELCTBL_CHC CHC
, BEN_CWB_MGR_HRCHY HIER
, BEN_CWB_MGR_HRCHY HIER2
, BEN_ELIG_PER_ELCTBL_CHC CHC2
, BEN_PER_IN_LER PIL
, BEN_PER_IN_LER PIL2
WHERE PIL.PER_IN_LER_ID = CHC.PER_IN_LER_ID
AND CHC.ELIG_PER_ELCTBL_CHC_ID = HIER.MGR_ELIG_PER_ELCTBL_CHC_ID
AND HIER.LVL_NUM > 0
AND HIER.EMP_ELIG_PER_ELCTBL_CHC_ID = CHC2.ELIG_PER_ELCTBL_CHC_ID
AND CHC2.ELIG_FLAG = 'Y'
AND CHC2.PER_IN_LER_ID = PIL2.PER_IN_LER_ID
AND PIL2.PER_IN_LER_STAT_CD IN ('STRTD'
, 'PROCD')
AND PIL2.PERSON_ID = PPF.PERSON_ID
AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND CHC2.ELIG_PER_ELCTBL_CHC_ID = HIER2.MGR_ELIG_PER_ELCTBL_CHC_ID