VIEW: APPS.BEN_BENEFITS_SUMMARY_V

Source

View Text - Preformatted

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.lookup_code 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.lookup_code 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.lookup_code 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.lookup_code 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.lookup_code 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 )

View Text - HTML Formatted

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.LOOKUP_CODE 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.LOOKUP_CODE 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.LOOKUP_CODE 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.LOOKUP_CODE 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.LOOKUP_CODE 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 )