FND Design Data [Home] [Help]

View: BEN_BENEFITS_SUMMARY_V

Product: BEN - Advanced Benefits
Description: - Retrofitted
Implementation/DBA Data: ViewAPPS.BEN_BENEFITS_SUMMARY_V
View Text

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 )

Columns

Name
ORDER_NUM0
ORDER_NUM1
ORDER_NUM2
ORDER_NUM3
NAME
ID
BUSINESS_GROUP_ID
PERSON_ID
MEANING
TYPE
OBJECT_TYPE_CD