View: BEN_WFREP_ELIG_INFO_VW

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

View Text

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 */

Columns