VIEW: APPS.BEN_COMP_OBJ_ABR_LVL_V

Source

View Text - Preformatted

SELECT pln.name, null, null, pln.pl_id, to_number(null), to_number(null), to_number(null), to_number(null), pln.business_group_id from ben_pl_f pln, fnd_sessions fnd WHERE fnd.session_id = userenv('sessionid') and fnd.effective_date between pln.effective_start_date and pln.effective_end_date UNION /* Oipl Level */ select pln.name, opt.name, null, to_number(null), oipl.oipl_id, to_number(null), to_number(null), to_number(null), oipl.business_group_id from ben_oipl_f oipl, ben_pl_f pln, ben_opt_f opt, fnd_sessions fnd where oipl.pl_id = pln.pl_id and oipl.opt_id = opt.opt_id and fnd.session_id = userenv('sessionid') and fnd.effective_date between oipl.effective_start_date and oipl.effective_end_date and fnd.effective_date between pln.effective_start_date and pln.effective_end_date and fnd.effective_date between opt.effective_start_date and opt.effective_end_date UNION /* Oiplip Level */ select pln.name, opt.name, pgm.name, to_number(null), to_number(null), to_number(null), oiplip.oiplip_id, to_number(null), oiplip.business_group_id from ben_oiplip_f oiplip, ben_oipl_f oipl, ben_plip_f plip, ben_pl_f pln, ben_opt_f opt, ben_pgm_f pgm, fnd_sessions fnd where oiplip.oipl_id = oipl.oipl_id and oiplip.plip_id = plip.plip_id and oipl.pl_id = pln.pl_id and plip.pl_id = pln.pl_id and oipl.opt_id = opt.opt_id and plip.pgm_id = pgm.pgm_id and fnd.session_id = userenv('sessionid') and fnd.effective_date between oiplip.effective_start_date and oiplip.effective_end_date and fnd.effective_date between oipl.effective_start_date and oipl.effective_end_date and fnd.effective_date between plip.effective_start_date and plip.effective_end_date and fnd.effective_date between pln.effective_start_date and pln.effective_end_date and fnd.effective_date between opt.effective_start_date and opt.effective_end_date and fnd.effective_date between pgm.effective_start_date and pgm.effective_end_date UNION /* Plip Level */ select pln.name, null, pgm.name, to_number(null), to_number(null), plip.plip_id, to_number(null), to_number(null), plip.business_group_id from ben_plip_f plip, ben_pl_f pln, ben_pgm_f pgm, fnd_sessions fnd where plip.pl_id = pln.pl_id and plip.pgm_id = pgm.pgm_id and fnd.session_id = userenv('sessionid') and fnd.effective_date between plip.effective_start_date and plip.effective_end_date and fnd.effective_date between pln.effective_start_date and pln.effective_end_date and fnd.effective_date between pgm.effective_start_date and pgm.effective_end_date UNION /* Option Level */ SELECT null, opt.name, null, to_number(null), to_number(null), to_number(null), to_number(null), opt.opt_id, opt.business_group_id from ben_opt_f opt, fnd_sessions fnd WHERE fnd.session_id = userenv('sessionid') and fnd.effective_date between opt.effective_start_date and opt.effective_end_date

View Text - HTML Formatted

SELECT PLN.NAME
, NULL
, NULL
, PLN.PL_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, PLN.BUSINESS_GROUP_ID
FROM BEN_PL_F PLN
, FND_SESSIONS FND
WHERE FND.SESSION_ID = USERENV('SESSIONID')
AND FND.EFFECTIVE_DATE BETWEEN PLN.EFFECTIVE_START_DATE
AND PLN.EFFECTIVE_END_DATE UNION /* OIPL LEVEL */ SELECT PLN.NAME
, OPT.NAME
, NULL
, TO_NUMBER(NULL)
, OIPL.OIPL_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, OIPL.BUSINESS_GROUP_ID
FROM BEN_OIPL_F OIPL
, BEN_PL_F PLN
, BEN_OPT_F OPT
, FND_SESSIONS FND
WHERE OIPL.PL_ID = PLN.PL_ID
AND OIPL.OPT_ID = OPT.OPT_ID
AND FND.SESSION_ID = USERENV('SESSIONID')
AND FND.EFFECTIVE_DATE BETWEEN OIPL.EFFECTIVE_START_DATE
AND OIPL.EFFECTIVE_END_DATE
AND FND.EFFECTIVE_DATE BETWEEN PLN.EFFECTIVE_START_DATE
AND PLN.EFFECTIVE_END_DATE
AND FND.EFFECTIVE_DATE BETWEEN OPT.EFFECTIVE_START_DATE
AND OPT.EFFECTIVE_END_DATE UNION /* OIPLIP LEVEL */ SELECT PLN.NAME
, OPT.NAME
, PGM.NAME
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, OIPLIP.OIPLIP_ID
, TO_NUMBER(NULL)
, OIPLIP.BUSINESS_GROUP_ID
FROM BEN_OIPLIP_F OIPLIP
, BEN_OIPL_F OIPL
, BEN_PLIP_F PLIP
, BEN_PL_F PLN
, BEN_OPT_F OPT
, BEN_PGM_F PGM
, FND_SESSIONS FND
WHERE OIPLIP.OIPL_ID = OIPL.OIPL_ID
AND OIPLIP.PLIP_ID = PLIP.PLIP_ID
AND OIPL.PL_ID = PLN.PL_ID
AND PLIP.PL_ID = PLN.PL_ID
AND OIPL.OPT_ID = OPT.OPT_ID
AND PLIP.PGM_ID = PGM.PGM_ID
AND FND.SESSION_ID = USERENV('SESSIONID')
AND FND.EFFECTIVE_DATE BETWEEN OIPLIP.EFFECTIVE_START_DATE
AND OIPLIP.EFFECTIVE_END_DATE
AND FND.EFFECTIVE_DATE BETWEEN OIPL.EFFECTIVE_START_DATE
AND OIPL.EFFECTIVE_END_DATE
AND FND.EFFECTIVE_DATE BETWEEN PLIP.EFFECTIVE_START_DATE
AND PLIP.EFFECTIVE_END_DATE
AND FND.EFFECTIVE_DATE BETWEEN PLN.EFFECTIVE_START_DATE
AND PLN.EFFECTIVE_END_DATE
AND FND.EFFECTIVE_DATE BETWEEN OPT.EFFECTIVE_START_DATE
AND OPT.EFFECTIVE_END_DATE
AND FND.EFFECTIVE_DATE BETWEEN PGM.EFFECTIVE_START_DATE
AND PGM.EFFECTIVE_END_DATE UNION /* PLIP LEVEL */ SELECT PLN.NAME
, NULL
, PGM.NAME
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, PLIP.PLIP_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, PLIP.BUSINESS_GROUP_ID
FROM BEN_PLIP_F PLIP
, BEN_PL_F PLN
, BEN_PGM_F PGM
, FND_SESSIONS FND
WHERE PLIP.PL_ID = PLN.PL_ID
AND PLIP.PGM_ID = PGM.PGM_ID
AND FND.SESSION_ID = USERENV('SESSIONID')
AND FND.EFFECTIVE_DATE BETWEEN PLIP.EFFECTIVE_START_DATE
AND PLIP.EFFECTIVE_END_DATE
AND FND.EFFECTIVE_DATE BETWEEN PLN.EFFECTIVE_START_DATE
AND PLN.EFFECTIVE_END_DATE
AND FND.EFFECTIVE_DATE BETWEEN PGM.EFFECTIVE_START_DATE
AND PGM.EFFECTIVE_END_DATE UNION /* OPTION LEVEL */ SELECT NULL
, OPT.NAME
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, OPT.OPT_ID
, OPT.BUSINESS_GROUP_ID
FROM BEN_OPT_F OPT
, FND_SESSIONS FND
WHERE FND.SESSION_ID = USERENV('SESSIONID')
AND FND.EFFECTIVE_DATE BETWEEN OPT.EFFECTIVE_START_DATE
AND OPT.EFFECTIVE_END_DATE