SELECT DECODE(LER.TYP_CD , 'IREC' , 5 , 'ABS' , 4 , 'COMP' , 2 , 'GSP' , 3 , 1) ORDER_NUM0 , PGM.PGM_ID ORDER_NUM1 , -1 ORDER_NUM2 , -1 ORDER_NUM3 , PGM.NAME NAME , PGM.PGM_ID ID , PGM.BUSINESS_GROUP_ID BUSINESS_GROUP_ID , PEP.PERSON_ID PERSON_ID , ELI.MEANING MEANING , 'PGM' TYPE , PGM.PGM_TYP_CD OBJECT_TYPE_CD FROM BEN_ELIG_PER_F PEP , BEN_PER_IN_LER PIL , BEN_PGM_F PGM , HR_LOOKUPS ELI , FND_SESSIONS SE , BEN_LER_F LER WHERE SE.SESSION_ID = USERENV('SESSIONID') AND PEP.PGM_ID = PGM.PGM_ID AND PEP.PL_ID IS NULL AND PEP.PLIP_ID IS NULL AND PEP.PTIP_ID IS NULL AND SE.EFFECTIVE_DATE BETWEEN PGM.EFFECTIVE_START_DATE AND PGM.EFFECTIVE_END_DATE AND PEP.BUSINESS_GROUP_ID = PGM.BUSINESS_GROUP_ID AND SE.EFFECTIVE_DATE BETWEEN PEP.EFFECTIVE_START_DATE AND PEP.EFFECTIVE_END_DATE AND ELI.LOOKUP_CODE (+)= PEP.ELIG_FLAG AND ELI.LOOKUP_TYPE (+)= 'YES_NO' AND PIL.PER_IN_LER_ID(+)=PEP.PER_IN_LER_ID AND PIL.LER_ID=LER.LER_ID AND SE.EFFECTIVE_DATE BETWEEN LER.EFFECTIVE_START_DATE AND LER.EFFECTIVE_END_DATE AND PIL.BUSINESS_GROUP_ID(+)=PEP.BUSINESS_GROUP_ID+0 AND ( PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD' , 'BCKDT') OR PIL.PER_IN_LER_STAT_CD IS NULL ) UNION ALL SELECT DECODE(LER.TYP_CD , 'IREC' , 5 , 'ABS' , 4 , 'COMP' , 2 , 'GSP' , 3 , 1) ORDER_NUM0 , PEP.PGM_ID ORDER_NUM1 , PL.PL_ID ORDER_NUM2 , -1 ORDER_NUM3 , ' ' || PL.NAME NAME , PL.PL_ID ID , PL.BUSINESS_GROUP_ID BUSINESS_GROUP_ID , PEP.PERSON_ID PERSON_ID , ELI.MEANING MEANING , 'PL' TYPE , PL.SVGS_PL_FLAG OBJECT_TYPE_CD FROM BEN_ELIG_PER_F PEP , BEN_PER_IN_LER PIL , BEN_PL_F PL , HR_LOOKUPS ELI , FND_SESSIONS SE , BEN_LER_F LER WHERE SE.SESSION_ID = USERENV('SESSIONID') AND PEP.PL_ID = PL.PL_ID AND PEP.PGM_ID IS NOT NULL AND PEP.PL_ID IS NOT NULL AND SE.EFFECTIVE_DATE BETWEEN PEP.EFFECTIVE_START_DATE AND PEP.EFFECTIVE_END_DATE AND SE.EFFECTIVE_DATE BETWEEN PL.EFFECTIVE_START_DATE AND PL.EFFECTIVE_END_DATE AND ELI.LOOKUP_CODE (+)= PEP.ELIG_FLAG AND ELI.LOOKUP_TYPE (+)= 'YES_NO' AND EXISTS (SELECT NULL FROM BEN_PLIP_F WHERE PL_ID = PL.PL_ID) AND PIL.PER_IN_LER_ID(+)=PEP.PER_IN_LER_ID AND PIL.LER_ID=LER.LER_ID AND SE.EFFECTIVE_DATE BETWEEN LER.EFFECTIVE_START_DATE AND LER.EFFECTIVE_END_DATE AND PIL.BUSINESS_GROUP_ID(+)=PEP.BUSINESS_GROUP_ID+0 AND ( PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD' , 'BCKDT') OR PIL.PER_IN_LER_STAT_CD IS NULL ) UNION ALL SELECT DECODE(LER.TYP_CD , 'IREC' , 5 , 'ABS' , 4 , 'COMP' , 2 , 'GSP' , 3 , 1) ORDER_NUM0 , PEP.PGM_ID ORDER_NUM1 , PEP.PL_ID ORDER_NUM2 , PIO.OPT_ID ORDER_NUM3 , ' ' || OPT.NAME NAME , PIO.OPT_ID ID , PIO.BUSINESS_GROUP_ID BUSINESS_GROUP_ID , PEP.PERSON_ID PERSON_ID , ELI.MEANING MEANING , 'OPT' TYPE , NULL OBJECT_TYPE_CD FROM BEN_ELIG_PER_OPT_F PIO , BEN_PER_IN_LER PIL , BEN_ELIG_PER_F PEP , BEN_PL_F PL , BEN_OPT_F OPT , HR_LOOKUPS ELI , FND_SESSIONS SE , BEN_LER_F LER WHERE SE.SESSION_ID = USERENV('SESSIONID') AND PIO.ELIG_PER_ID = PEP.ELIG_PER_ID AND PIO.OPT_ID = OPT.OPT_ID AND PEP.PL_ID = PL.PL_ID AND PEP.PGM_ID IS NOT NULL AND PEP.PL_ID IS NOT NULL AND SE.EFFECTIVE_DATE BETWEEN PEP.EFFECTIVE_START_DATE AND PEP.EFFECTIVE_END_DATE AND SE.EFFECTIVE_DATE BETWEEN PIO.EFFECTIVE_START_DATE AND PIO.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 ELI.LOOKUP_CODE (+)= PIO.ELIG_FLAG AND ELI.LOOKUP_TYPE (+)= 'YES_NO' AND EXISTS (SELECT NULL FROM BEN_PLIP_F WHERE PL_ID = PL.PL_ID) AND PIL.PER_IN_LER_ID(+)=PIO.PER_IN_LER_ID AND PIL.LER_ID=LER.LER_ID AND SE.EFFECTIVE_DATE BETWEEN LER.EFFECTIVE_START_DATE AND LER.EFFECTIVE_END_DATE AND PIL.BUSINESS_GROUP_ID(+)=PIO.BUSINESS_GROUP_ID+0 AND ( PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD' , 'BCKDT') OR PIL.PER_IN_LER_STAT_CD IS NULL ) UNION ALL SELECT DECODE(LER.TYP_CD , 'IREC' , 5 , 'ABS' , 4 , 'COMP' , 2 , 'GSP' , 3 , 1) ORDER_NUM0 , 9999999999999999999999999999999 ORDER_NUM1 , PEP.PL_ID ORDER_NUM2 , -1 ORDER_NUM3 , PL.NAME NAME , PL.PL_ID ID , PL.BUSINESS_GROUP_ID BUSINESS_GROUP_ID , PEP.PERSON_ID PERSON_ID , ELI.MEANING MEANING , 'PL' TYPE , PL.SVGS_PL_FLAG OBJECT_TYPE_CD FROM BEN_ELIG_PER_F PEP , BEN_PER_IN_LER PIL , BEN_PL_F PL , HR_LOOKUPS ELI , FND_SESSIONS SE , BEN_LER_F LER WHERE SE.SESSION_ID = USERENV('SESSIONID') AND PEP.PL_ID = PL.PL_ID AND PEP.PGM_ID IS NULL AND SE.EFFECTIVE_DATE BETWEEN PEP.EFFECTIVE_START_DATE AND PEP.EFFECTIVE_END_DATE AND SE.EFFECTIVE_DATE BETWEEN PL.EFFECTIVE_START_DATE AND PL.EFFECTIVE_END_DATE AND ELI.LOOKUP_CODE (+)= PEP.ELIG_FLAG AND ELI.LOOKUP_TYPE (+)= 'YES_NO' AND NOT EXISTS (SELECT NULL FROM BEN_PLIP_F WHERE PL_ID = PL.PL_ID) AND PIL.PER_IN_LER_ID(+)=PEP.PER_IN_LER_ID AND PIL.LER_ID=LER.LER_ID AND SE.EFFECTIVE_DATE BETWEEN LER.EFFECTIVE_START_DATE AND LER.EFFECTIVE_END_DATE AND PIL.BUSINESS_GROUP_ID(+)=PEP.BUSINESS_GROUP_ID+0 AND ( PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD' , 'BCKDT') OR PIL.PER_IN_LER_STAT_CD IS NULL ) UNION ALL SELECT DECODE(LER.TYP_CD , 'IREC' , 5 , 'ABS' , 4 , 'COMP' , 2 , 'GSP' , 3 , 1) ORDER_NUM0 , 9999999999999999999999999999999 ORDER_NUM1 , PEP.PL_ID ORDER_NUM2 , PIO.OPT_ID ORDER_NUM3 , ' ' || OPT.NAME NAME , PIO.OPT_ID ID , PIO.BUSINESS_GROUP_ID BUSINESS_GROUP_ID , PEP.PERSON_ID PERSON_ID , ELI.MEANING MEANING , 'OPT' TYPE , NULL OBJECT_TYPE_CD FROM BEN_ELIG_PER_OPT_F PIO , BEN_PER_IN_LER PIL , BEN_ELIG_PER_F PEP , BEN_PL_F PL , BEN_OPT_F OPT , HR_LOOKUPS ELI , FND_SESSIONS SE , BEN_LER_F LER WHERE SE.SESSION_ID = USERENV('SESSIONID') AND PIO.ELIG_PER_ID = PEP.ELIG_PER_ID AND PIO.OPT_ID = OPT.OPT_ID AND PEP.PL_ID = PL.PL_ID AND PEP.PGM_ID IS NULL AND PEP.PL_ID IS NOT NULL AND SE.EFFECTIVE_DATE BETWEEN PEP.EFFECTIVE_START_DATE AND PEP.EFFECTIVE_END_DATE AND SE.EFFECTIVE_DATE BETWEEN PIO.EFFECTIVE_START_DATE AND PIO.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 ELI.LOOKUP_CODE (+)= PIO.ELIG_FLAG AND ELI.LOOKUP_TYPE (+)= 'YES_NO' AND NOT EXISTS (SELECT NULL FROM BEN_PLIP_F WHERE PL_ID = PL.PL_ID) AND PIL.PER_IN_LER_ID(+)=PIO.PER_IN_LER_ID AND PIL.LER_ID=LER.LER_ID AND SE.EFFECTIVE_DATE BETWEEN LER.EFFECTIVE_START_DATE AND LER.EFFECTIVE_END_DATE AND PIL.BUSINESS_GROUP_ID(+)=PIO.BUSINESS_GROUP_ID+0 AND ( PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD' , 'BCKDT') OR PIL.PER_IN_LER_STAT_CD IS NULL )