View: BEN_PL_OIPL_ACTL_PREM_V

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

View Text

SELECT DISTINCT APR.PL_ID
, PL.NAME
, APR.BUSINESS_GROUP_ID
, TO_NUMBER(NULL)
, NULL
FROM BEN_ACTL_PREM_F APR
, BEN_PL_F PL
, FND_SESSIONS SE
WHERE APR.PL_ID = PL.PL_ID
AND SE.EFFECTIVE_DATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND APR.OIPL_ID IS NULL
AND APR.PREM_ASNMT_LVL_CD IN ('PRTTNPLOIPL'
, 'PLOIPL')
AND SE.EFFECTIVE_DATE BETWEEN APR.EFFECTIVE_START_DATE
AND APR.EFFECTIVE_END_DATE
AND SE.SESSION_ID = USERENV('SESSIONID') UNION SELECT DISTINCT APR.PL_ID
, PL.NAME
, APR.BUSINESS_GROUP_ID
, APR.OIPL_ID
, OPT.NAME
FROM BEN_ACTL_PREM_F APR
, BEN_PL_F PL
, BEN_OIPL_F OIPL
, BEN_OPT_F OPT
, FND_SESSIONS SE
WHERE APR.OIPL_ID = OIPL.OIPL_ID
AND OIPL.OPT_ID = OPT.OPT_ID
AND OIPL.PL_ID = PL.PL_ID
AND APR.PREM_ASNMT_LVL_CD IN ('PRTTNPLOIPL'
, 'PLOIPL')
AND SE.EFFECTIVE_DATE BETWEEN OIPL.EFFECTIVE_START_DATE
AND OIPL.EFFECTIVE_END_DATE
AND SE.EFFECTIVE_DATE BETWEEN OPT.EFFECTIVE_START_DATE
AND OPT.EFFECTIVE_END_DATE
AND SE.EFFECTIVE_DATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND SE.EFFECTIVE_DATE BETWEEN APR.EFFECTIVE_START_DATE
AND APR.EFFECTIVE_END_DATE
AND SE.SESSION_ID = USERENV('SESSIONID')

Columns