View: BEN_SS_ROLE_COMP_OBJECT

Product:	BEN - Advanced Benefits
Description:	Used in value set for segment OBJ ; context BEN_SS_ROLE_COMP_OBJECTS of Extra Role Information
Implementation/DBA Data:	APPS.BEN_SS_ROLE_COMP_OBJECT

View Text

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

Columns