VIEW: APPS.BEN_WFREP_ELIG_INFO_VW

Source

View Text - Preformatted

SELECT bei.elig_flag elig_flag, 1 ord_num, pgm.name name, bei.inelig_text inelig_text, bft.benefit_action_id benefit_action_id, bei.person_id person_id, bei.pgm_id pgm_id, bei.pgm_id pl_id, bei.pgm_id oipl_id from ben_benefit_actions bft, ben_batch_elig_info bei, ben_pgm_f pgm WHERE bft.benefit_action_id = bei.benefit_action_id and bei.pl_id is null and bei.oipl_id is null and bei.pgm_id is not null and bei.pgm_id = pgm.pgm_id and bft.process_date between pgm.effective_start_date and pgm.effective_end_date union select bei.elig_flag elig_flag, 2 ord_num, decode(pgm.name,null,pln.name,pgm.name||' - '||pln.name) name, bei.inelig_text inelig_text, bft.benefit_action_id benefit_action_id, bei.person_id person_id, bei.pgm_id pgm_id, bei.pl_id pl_id, bei.pl_id oipl_id from ben_benefit_actions bft, ben_batch_elig_info bei, ben_pgm_f pgm, ben_pl_f pln where bft.benefit_action_id = bei.benefit_action_id and bei.pl_id is not null and bei.oipl_id is null and bei.pgm_id = pgm.pgm_id(+) and bft.process_date between nvl(pgm.effective_start_date,bft.process_date) and nvl(pgm.effective_end_date,bft.process_date) and bei.pl_id = pln.pl_id and bft.process_date between pln.effective_start_date and pln.effective_end_date union select bei.elig_flag elig_flag, 3 ord_num, decode(pgm.name,null,pln.name||' - '||opt.name,pgm.name||' - '||pln.name||' - '||opt.name) name, /* opt.name name, */ bei.inelig_text inelig_text, bft.benefit_action_id benefit_action_id, bei.person_id person_id, bei.pgm_id pgm_id, bei.pl_id pl_id, bei.oipl_id oipl_id from ben_benefit_actions bft, ben_batch_elig_info bei, ben_pgm_f pgm, ben_pl_f pln, ben_oipl_f cop, ben_opt_f opt where bft.benefit_action_id = bei.benefit_action_id and bei.pl_id is not null and bei.oipl_id is not null and bei.pgm_id = pgm.pgm_id(+) and bft.process_date between nvl(pgm.effective_start_date,bft.process_date) and nvl(pgm.effective_end_date,bft.process_date) and bei.pl_id = pln.pl_id and bft.process_date between pln.effective_start_date and pln.effective_end_date and bei.oipl_id = cop.oipl_id and bft.process_date between cop.effective_start_date and cop.effective_end_date and opt.opt_id = cop.opt_id and bft.process_date between opt.effective_start_date and opt.effective_end_date /* order by 1, 2, 3 */

View Text - HTML Formatted

SELECT BEI.ELIG_FLAG ELIG_FLAG
, 1 ORD_NUM
, PGM.NAME NAME
, BEI.INELIG_TEXT INELIG_TEXT
, BFT.BENEFIT_ACTION_ID BENEFIT_ACTION_ID
, BEI.PERSON_ID PERSON_ID
, BEI.PGM_ID PGM_ID
, BEI.PGM_ID PL_ID
, BEI.PGM_ID OIPL_ID
FROM BEN_BENEFIT_ACTIONS BFT
, BEN_BATCH_ELIG_INFO BEI
, BEN_PGM_F PGM
WHERE BFT.BENEFIT_ACTION_ID = BEI.BENEFIT_ACTION_ID
AND BEI.PL_ID IS NULL
AND BEI.OIPL_ID IS NULL
AND BEI.PGM_ID IS NOT NULL
AND BEI.PGM_ID = PGM.PGM_ID
AND BFT.PROCESS_DATE BETWEEN PGM.EFFECTIVE_START_DATE
AND PGM.EFFECTIVE_END_DATE UNION SELECT BEI.ELIG_FLAG ELIG_FLAG
, 2 ORD_NUM
, DECODE(PGM.NAME
, NULL
, PLN.NAME
, PGM.NAME||' - '||PLN.NAME) NAME
, BEI.INELIG_TEXT INELIG_TEXT
, BFT.BENEFIT_ACTION_ID BENEFIT_ACTION_ID
, BEI.PERSON_ID PERSON_ID
, BEI.PGM_ID PGM_ID
, BEI.PL_ID PL_ID
, BEI.PL_ID OIPL_ID
FROM BEN_BENEFIT_ACTIONS BFT
, BEN_BATCH_ELIG_INFO BEI
, BEN_PGM_F PGM
, BEN_PL_F PLN
WHERE BFT.BENEFIT_ACTION_ID = BEI.BENEFIT_ACTION_ID
AND BEI.PL_ID IS NOT NULL
AND BEI.OIPL_ID IS NULL
AND BEI.PGM_ID = PGM.PGM_ID(+)
AND BFT.PROCESS_DATE BETWEEN NVL(PGM.EFFECTIVE_START_DATE
, BFT.PROCESS_DATE)
AND NVL(PGM.EFFECTIVE_END_DATE
, BFT.PROCESS_DATE)
AND BEI.PL_ID = PLN.PL_ID
AND BFT.PROCESS_DATE BETWEEN PLN.EFFECTIVE_START_DATE
AND PLN.EFFECTIVE_END_DATE UNION SELECT BEI.ELIG_FLAG ELIG_FLAG
, 3 ORD_NUM
, DECODE(PGM.NAME
, NULL
, PLN.NAME||' - '||OPT.NAME
, PGM.NAME||' - '||PLN.NAME||' - '||OPT.NAME) NAME
, /* OPT.NAME NAME
, */ BEI.INELIG_TEXT INELIG_TEXT
, BFT.BENEFIT_ACTION_ID BENEFIT_ACTION_ID
, BEI.PERSON_ID PERSON_ID
, BEI.PGM_ID PGM_ID
, BEI.PL_ID PL_ID
, BEI.OIPL_ID OIPL_ID
FROM BEN_BENEFIT_ACTIONS BFT
, BEN_BATCH_ELIG_INFO BEI
, BEN_PGM_F PGM
, BEN_PL_F PLN
, BEN_OIPL_F COP
, BEN_OPT_F OPT
WHERE BFT.BENEFIT_ACTION_ID = BEI.BENEFIT_ACTION_ID
AND BEI.PL_ID IS NOT NULL
AND BEI.OIPL_ID IS NOT NULL
AND BEI.PGM_ID = PGM.PGM_ID(+)
AND BFT.PROCESS_DATE BETWEEN NVL(PGM.EFFECTIVE_START_DATE
, BFT.PROCESS_DATE)
AND NVL(PGM.EFFECTIVE_END_DATE
, BFT.PROCESS_DATE)
AND BEI.PL_ID = PLN.PL_ID
AND BFT.PROCESS_DATE BETWEEN PLN.EFFECTIVE_START_DATE
AND PLN.EFFECTIVE_END_DATE
AND BEI.OIPL_ID = COP.OIPL_ID
AND BFT.PROCESS_DATE BETWEEN COP.EFFECTIVE_START_DATE
AND COP.EFFECTIVE_END_DATE
AND OPT.OPT_ID = COP.OPT_ID
AND BFT.PROCESS_DATE BETWEEN OPT.EFFECTIVE_START_DATE
AND OPT.EFFECTIVE_END_DATE /* ORDER BY 1
, 2
, 3 */