VIEW: APPS.BEN_PL_OIPL_ACTL_PREM_V

Source

View Text - Preformatted

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')

View Text - HTML Formatted

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')