VIEW: APPS.BEN_SS_ROLE_COMP_OBJECT

Source

View Text - Preformatted

SELECT 'LE', name, ler_id From ben_ler_f_vl WHERE business_group_id=hr_general.get_business_group_id and hr_general.effective_date between effective_start_date and effective_end_date Union all Select 'PT', pt.name, pt.pl_typ_id From ben_pl_typ_f pt Where pt.business_group_id=hr_general.get_business_group_id and hr_general.effective_date between effective_start_date and effective_end_date /* and not exists (Select ptip_id From ben_ptip_f ptip Where ptip.pl_typ_id=pt.pl_typ_id and hr_general.effective_date between ptip.effective_start_date and ptip.effective_end_date) */ Union all Select 'PGM' ,name,pgm_id From ben_pgm_f Where business_group_id=hr_general.get_business_group_id and hr_general.effective_date between effective_start_date and effective_end_date Union all Select 'PTIP', pt.name || ' - ' || pgm.name ,ptip.ptip_id From ben_pl_typ_f pt , ben_ptip_f ptip, ben_pgm_f pgm Where ptip.business_group_id=hr_general.get_business_group_id and ptip.pl_typ_id =Pt.pl_typ_id and ptip.pgm_id=pgm.pgm_id and hr_general.effective_date between pt.effective_start_date and pt.effective_end_date and hr_general.effective_date between ptip.effective_start_date and ptip.effective_end_date and hr_general.effective_date between pgm.effective_start_date and pgm.effective_end_date Union all Select 'PNIP', pl.name || ' - ' || pt.name ,pl.pl_id From ben_pl_f pl, ben_pl_typ_f pt Where pl.business_group_id=hr_general.get_business_group_id and hr_general.effective_date between pl.effective_start_date and pl.effective_end_date and hr_general.effective_date between pt.effective_start_date and pt.effective_end_date and pl.pl_typ_id=pt.pl_typ_id and not exists (Select plip.plip_id From ben_plip_f plip Where plip.pl_id=pl.pl_id and hr_general.effective_date between plip.effective_start_date and plip.effective_end_date ) Union all Select 'PLIP', pl.name || ' - ' || pgm.name , /* plip.pl_id */ plip.plip_id From ben_plip_f plip, ben_pl_f pl, ben_pgm_f pgm Where plip.business_group_id=hr_general.get_business_group_id and plip.pl_id=pl.pl_id and plip.pgm_id=pgm.pgm_id and hr_general.effective_date between plip.effective_start_date and plip.effective_end_date and hr_general.effective_date between pl.effective_start_date and pl.effective_end_date and hr_general.effective_date between pgm.effective_start_date and pgm.effective_end_date Union all Select 'OIPLIP', opt.name || ' - ' || pl.name || ' - ' || pgm.name , oiplip.oiplip_id From ben_oiplip_f oiplip, ben_plip_f plip, ben_oipl_f oipl, ben_opt_f opt, ben_pl_f pl, ben_pgm_f pgm Where oiplip.business_group_id=hr_general.get_business_group_id and oiplip.plip_id=plip.plip_id and pl.pl_id=plip.pl_id and pgm.pgm_id=plip.pgm_id and oiplip.oipl_id=oipl.oipl_id and opt.opt_id=oipl.opt_id and hr_general.effective_date between oiplip.effective_start_date and oiplip.effective_end_date and hr_general.effective_date between plip.effective_start_date and plip.effective_end_date and hr_general.effective_date between oipl.effective_start_date and oipl.effective_end_date and hr_general.effective_date between opt.effective_start_date and opt.effective_end_date and hr_general.effective_date between pl.effective_start_date and pl.effective_end_date and hr_general.effective_date between pgm.effective_start_date and pgm.effective_end_date Union all Select 'OIPNIP', opt.name || ' - ' || pl.name || ' - ' || pt.name , oipl.oipl_id From ben_oipl_f oipl, ben_opt_f opt, ben_pl_f pl, ben_pl_typ_f pt Where oipl.business_group_id=hr_general.get_business_group_id and oipl.opt_id=opt.opt_id and oipl.pl_id=pl.pl_id and pl.pl_typ_id=pt.pl_typ_id and not exists (Select plip.plip_id From ben_plip_f plip Where plip.pl_id=pl.pl_id and hr_general.effective_date between plip.effective_start_date and plip.effective_end_date ) and hr_general.effective_date between oipl.effective_start_date and oipl.effective_end_date and hr_general.effective_date between opt.effective_start_date and opt.effective_end_date and hr_general.effective_date between pl.effective_start_date and pl.effective_end_date and hr_general.effective_date between pt.effective_start_date and pt.effective_end_date

View Text - HTML Formatted

SELECT 'LE'
, NAME
, LER_ID
FROM BEN_LER_F_VL
WHERE BUSINESS_GROUP_ID=HR_GENERAL.GET_BUSINESS_GROUP_ID
AND HR_GENERAL.EFFECTIVE_DATE BETWEEN EFFECTIVE_START_DATE
AND EFFECTIVE_END_DATE UNION ALL SELECT 'PT'
, PT.NAME
, PT.PL_TYP_ID
FROM BEN_PL_TYP_F PT
WHERE PT.BUSINESS_GROUP_ID=HR_GENERAL.GET_BUSINESS_GROUP_ID
AND HR_GENERAL.EFFECTIVE_DATE BETWEEN EFFECTIVE_START_DATE
AND EFFECTIVE_END_DATE /*
AND NOT EXISTS (SELECT PTIP_ID
FROM BEN_PTIP_F PTIP
WHERE PTIP.PL_TYP_ID=PT.PL_TYP_ID
AND HR_GENERAL.EFFECTIVE_DATE BETWEEN PTIP.EFFECTIVE_START_DATE
AND PTIP.EFFECTIVE_END_DATE) */ UNION ALL SELECT 'PGM'
, NAME
, PGM_ID
FROM BEN_PGM_F
WHERE BUSINESS_GROUP_ID=HR_GENERAL.GET_BUSINESS_GROUP_ID
AND HR_GENERAL.EFFECTIVE_DATE BETWEEN EFFECTIVE_START_DATE
AND EFFECTIVE_END_DATE UNION ALL SELECT 'PTIP'
, PT.NAME || ' - ' || PGM.NAME
, PTIP.PTIP_ID
FROM BEN_PL_TYP_F PT
, BEN_PTIP_F PTIP
, BEN_PGM_F PGM
WHERE PTIP.BUSINESS_GROUP_ID=HR_GENERAL.GET_BUSINESS_GROUP_ID
AND PTIP.PL_TYP_ID =PT.PL_TYP_ID
AND PTIP.PGM_ID=PGM.PGM_ID
AND HR_GENERAL.EFFECTIVE_DATE BETWEEN PT.EFFECTIVE_START_DATE
AND PT.EFFECTIVE_END_DATE
AND HR_GENERAL.EFFECTIVE_DATE BETWEEN PTIP.EFFECTIVE_START_DATE
AND PTIP.EFFECTIVE_END_DATE
AND HR_GENERAL.EFFECTIVE_DATE BETWEEN PGM.EFFECTIVE_START_DATE
AND PGM.EFFECTIVE_END_DATE UNION ALL SELECT 'PNIP'
, PL.NAME || ' - ' || PT.NAME
, PL.PL_ID
FROM BEN_PL_F PL
, BEN_PL_TYP_F PT
WHERE PL.BUSINESS_GROUP_ID=HR_GENERAL.GET_BUSINESS_GROUP_ID
AND HR_GENERAL.EFFECTIVE_DATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND HR_GENERAL.EFFECTIVE_DATE BETWEEN PT.EFFECTIVE_START_DATE
AND PT.EFFECTIVE_END_DATE
AND PL.PL_TYP_ID=PT.PL_TYP_ID
AND NOT EXISTS (SELECT PLIP.PLIP_ID
FROM BEN_PLIP_F PLIP
WHERE PLIP.PL_ID=PL.PL_ID
AND HR_GENERAL.EFFECTIVE_DATE BETWEEN PLIP.EFFECTIVE_START_DATE
AND PLIP.EFFECTIVE_END_DATE ) UNION ALL SELECT 'PLIP'
, PL.NAME || ' - ' || PGM.NAME
, /* PLIP.PL_ID */ PLIP.PLIP_ID
FROM BEN_PLIP_F PLIP
, BEN_PL_F PL
, BEN_PGM_F PGM
WHERE PLIP.BUSINESS_GROUP_ID=HR_GENERAL.GET_BUSINESS_GROUP_ID
AND PLIP.PL_ID=PL.PL_ID
AND PLIP.PGM_ID=PGM.PGM_ID
AND HR_GENERAL.EFFECTIVE_DATE BETWEEN PLIP.EFFECTIVE_START_DATE
AND PLIP.EFFECTIVE_END_DATE
AND HR_GENERAL.EFFECTIVE_DATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND HR_GENERAL.EFFECTIVE_DATE BETWEEN PGM.EFFECTIVE_START_DATE
AND PGM.EFFECTIVE_END_DATE UNION ALL SELECT 'OIPLIP'
, OPT.NAME || ' - ' || PL.NAME || ' - ' || PGM.NAME
, OIPLIP.OIPLIP_ID
FROM BEN_OIPLIP_F OIPLIP
, BEN_PLIP_F PLIP
, BEN_OIPL_F OIPL
, BEN_OPT_F OPT
, BEN_PL_F PL
, BEN_PGM_F PGM
WHERE OIPLIP.BUSINESS_GROUP_ID=HR_GENERAL.GET_BUSINESS_GROUP_ID
AND OIPLIP.PLIP_ID=PLIP.PLIP_ID
AND PL.PL_ID=PLIP.PL_ID
AND PGM.PGM_ID=PLIP.PGM_ID
AND OIPLIP.OIPL_ID=OIPL.OIPL_ID
AND OPT.OPT_ID=OIPL.OPT_ID
AND HR_GENERAL.EFFECTIVE_DATE BETWEEN OIPLIP.EFFECTIVE_START_DATE
AND OIPLIP.EFFECTIVE_END_DATE
AND HR_GENERAL.EFFECTIVE_DATE BETWEEN PLIP.EFFECTIVE_START_DATE
AND PLIP.EFFECTIVE_END_DATE
AND HR_GENERAL.EFFECTIVE_DATE BETWEEN OIPL.EFFECTIVE_START_DATE
AND OIPL.EFFECTIVE_END_DATE
AND HR_GENERAL.EFFECTIVE_DATE BETWEEN OPT.EFFECTIVE_START_DATE
AND OPT.EFFECTIVE_END_DATE
AND HR_GENERAL.EFFECTIVE_DATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND HR_GENERAL.EFFECTIVE_DATE BETWEEN PGM.EFFECTIVE_START_DATE
AND PGM.EFFECTIVE_END_DATE UNION ALL SELECT 'OIPNIP'
, OPT.NAME || ' - ' || PL.NAME || ' - ' || PT.NAME
, OIPL.OIPL_ID
FROM BEN_OIPL_F OIPL
, BEN_OPT_F OPT
, BEN_PL_F PL
, BEN_PL_TYP_F PT
WHERE OIPL.BUSINESS_GROUP_ID=HR_GENERAL.GET_BUSINESS_GROUP_ID
AND OIPL.OPT_ID=OPT.OPT_ID
AND OIPL.PL_ID=PL.PL_ID
AND PL.PL_TYP_ID=PT.PL_TYP_ID
AND NOT EXISTS (SELECT PLIP.PLIP_ID
FROM BEN_PLIP_F PLIP
WHERE PLIP.PL_ID=PL.PL_ID
AND HR_GENERAL.EFFECTIVE_DATE BETWEEN PLIP.EFFECTIVE_START_DATE
AND PLIP.EFFECTIVE_END_DATE )
AND HR_GENERAL.EFFECTIVE_DATE BETWEEN OIPL.EFFECTIVE_START_DATE
AND OIPL.EFFECTIVE_END_DATE
AND HR_GENERAL.EFFECTIVE_DATE BETWEEN OPT.EFFECTIVE_START_DATE
AND OPT.EFFECTIVE_END_DATE
AND HR_GENERAL.EFFECTIVE_DATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND HR_GENERAL.EFFECTIVE_DATE BETWEEN PT.EFFECTIVE_START_DATE
AND PT.EFFECTIVE_END_DATE