BEN Life Events Summary

Description
Categories: BI Publisher, Human Resources
Application: Advanced Benefits
Source: Life Events Summary Report (XML)
Short Name: BENLESUM_XML
DB package: BEN_BENLESUM_XMLP_PKG
select 
	distinct pil.per_in_ler_id PER_IN_LER_ID1,
	pil.person_id per_id_rep3,
	per.full_name PERNAME3,
	per.national_identifier PERSSN3,
	loc.location_id loc_id_plan_rep3,
	loc_tl.location_code PERLOC3,
	pil.ler_id ler_id_plan_rep3,
	ler_tl.name LERNAME3,
	hr_general.decode_lookup('BEN_PER_IN_LER_STAT', per_in_ler_stat_cd) LESTAT3,
	hr_general.decode_lookup('BEN_LER_TYP', ler.typ_cd) LERTYPE3,
	pil.lf_evt_ocrd_dt LEOCRDDT3,
	pil.ntfn_dt ntfn_dt_plan_rep3,
	pln.pl_id pl_id_plan_rep3,
	pln.name pl_name_plan_rep3,
	attr.counter COUNTER1, 
	dff1.form_left_prompt segment_prompt_text1,
	attr.col_name col_name1, 
	decode(attr.counter, 
			1,PIL.PIL_ATTRIBUTE1 ,
			2,PIL.PIL_ATTRIBUTE2 , 
			3,PIL.PIL_ATTRIBUTE3 ,
			4,PIL.PIL_ATTRIBUTE4 ,
			5,PIL.PIL_ATTRIBUTE5 , 
			6,PIL.PIL_ATTRIBUTE6 ,
			7,PIL.PIL_ATTRIBUTE7 ,
			8,PIL.PIL_ATTRIBUTE8 , 
			9,PIL.PIL_ATTRIBUTE9 ,
			10,PIL.PIL_ATTRIBUTE10 ,
			11,PIL.PIL_ATTRIBUTE11 ,
			12,PIL.PIL_ATTRIBUTE12 , 
			13,PIL.PIL_ATTRIBUTE13 ,
			14,PIL.PIL_ATTRIBUTE14 ,
			15,PIL.PIL_ATTRIBUTE15 , 
			16,PIL.PIL_ATTRIBUTE16 ,
			17,PIL.PIL_ATTRIBUTE17 ,
			18,PIL.PIL_ATTRIBUTE18 , 
			19,PIL.PIL_ATTRIBUTE19 ,
			20,PIL.PIL_ATTRIBUTE20 ,
			21,PIL.PIL_ATTRIBUTE21 ,
			22,PIL.PIL_ATTRIBUTE22 , 
			23,PIL.PIL_ATTRIBUTE23 ,
			24,PIL.PIL_ATTRIBUTE24 ,
			25,PIL.PIL_ATTRIBUTE25 , 
			26,PIL.PIL_ATTRIBUTE26 ,
			27,PIL.PIL_ATTRIBUTE27 ,
			28,PIL.PIL_ATTRIBUTE28 , 
			29,PIL.PIL_ATTRIBUTE29 ,
			30,PIL.PIL_ATTRIBUTE30 
			) attr_val1
from ben_per_in_ler pil,
	 per_all_people_f per,
	 per_all_assignments_f asg,
	 hr_locations_all loc,hr_locations_all_tl loc_tl,
	 ben_pl_f pln,
	 ben_ler_f ler,ben_ler_f_tl ler_tl,
	 (  select distinct person_id,pl_id 
		from ben_prtt_enrt_rslt_f pen
		where pen.business_group_id = :p_business_group_id
			and pen.person_id=nvl(:p_person_id,pen.person_id)
		and pen.comp_lvl_cd in ('PLAN', 'PLIP', 'OIPL')
		and :p_run_date between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
		and pen.enrt_cvg_thru_dt <= pen.effective_end_date
		and pen.prtt_enrt_rslt_stat_cd is null
	 )  pl_per,
	 FND_DESCR_FLEX_COL_USAGE_TL dff1,
	   (
   select 1 counter, 'PIL_ATTRIBUTE1' col_name from dual union 
select 2 counter, 'PIL_ATTRIBUTE2' col_name
from dual union 
select 3 counter, 'PIL_ATTRIBUTE3' col_name
from dual union 
select 4 counter, 'PIL_ATTRIBUTE4' col_name
from dual union 
select 5 counter, 'PIL_ATTRIBUTE5' col_name
from dual union 
select 6 counter, 'PIL_ATTRIBUTE6' col_name
from dual union 
select 7 counter, 'PIL_ATTRIBUTE7' col_name
from dual union 
select 8 counter, 'PIL_ATTRIBUTE8' col_name
from dual union 
select 9 counter, 'PIL_ATTRIBUTE9' col_name
from dual union 
select 10 counter, 'PIL_ATTRIBUTE10' col_name
from dual union 
select 11 counter, 'PIL_ATTRIBUTE11' col_name
from dual union 
select 12 counter, 'PIL_ATTRIBUTE12' col_name
from dual union 
select 13 counter, 'PIL_ATTRIBUTE13' col_name
from dual union 
select 14 counter, 'PIL_ATTRIBUTE14' col_name
from dual union 
select 15 counter, 'PIL_ATTRIBUTE15' col_name
from dual union 
select 16 counter, 'PIL_ATTRIBUTE16' col_name
from dual union 
select 17 counter, 'PIL_ATTRIBUTE17' col_name
from dual union 
select 18 counter, 'PIL_ATTRIBUTE18' col_name
from dual union 
select 19 counter, 'PIL_ATTRIBUTE19' col_name
from dual union 
select 20 counter, 'PIL_ATTRIBUTE20' col_name
from dual union 
select 21 counter, 'PIL_ATTRIBUTE21' col_name
from dual union 
select 22 counter, 'PIL_ATTRIBUTE22' col_name
from dual union 
select 23 counter, 'PIL_ATTRIBUTE23' col_name
from dual union 
select 24 counter, 'PIL_ATTRIBUTE24' col_name
from dual union 
select 25 counter, 'PIL_ATTRIBUTE25' col_name
from dual union 
select 26 counter, 'PIL_ATTRIBUTE26' col_name
from dual union 
select 27 counter, 'PIL_ATTRIBUTE27' col_name
from dual union 
select 28 counter, 'PIL_ATTRIBUTE28' col_name
from dual union 
select 29 counter, 'PIL_ATTRIBUTE29' col_name
from dual union 
select 30 counter, 'PIL_ATTRIBUTE30' col_name
from dual  
  ) attr
  where :p_report_module_cd in ('LEBYPLAN','ALLMODLS')
	and :P_DISP_FLEX_FIELDS_FLAG ='Y' 
	and dff1.descriptive_flexfield_name = 'BEN_PER_IN_LER' 
	and dff1.APPLICATION_ID = 805
	and dff1.APPLICATION_COLUMN_NAME = attr.col_name
	and ( (dff1.DESCRIPTIVE_FLEX_CONTEXT_CODE = 'Global Data Elements')
	     or 
	  (dff1.DESCRIPTIVE_FLEX_CONTEXT_CODE = nvl(PIL.PIL_attribute_category, 'Global Data Elements'))
	)
	and :p_run_report ='Y'
	and loc_tl.language=userenv('LANG') 
	and loc_tl.location_id=loc.location_id 
	and ler_tl.language=userenv('LANG') 
	and ler_tl.ler_id=ler.ler_id 
	and ler.effective_start_date = ler_tl.effective_start_date 
	and  pil.business_group_id = :p_business_group_id
	and ler.business_group_id = per.business_group_id
	and ler.business_group_id = asg.business_group_id
	and pil.lf_evt_ocrd_dt between :p_comp_perd_strt_dt and :p_comp_perd_end_dt
	and pil.per_in_ler_stat_cd='PROCD' 
	and (:p_person_id is null or pil.person_id = :p_person_id)
	and (:p_nat_ident is null or per.national_identifier = :p_nat_ident)
	and pil.person_id = pl_per.person_id 
	and pl_per.person_id = per.person_id
	and pln.pl_id=pl_per.pl_id
	and :p_run_date between pln.effective_start_date and pln.effective_end_date
	and (:p_benefit_group_id is null or per.benefit_group_id = :p_benefit_group_id) 
	and :p_run_date between per.effective_start_date and per.effective_end_date 
	and ler.ler_id = pil.ler_id
	and (:p_ler_id is null or ler.ler_id = :p_ler_id)
	and ler.typ_cd not in ('ABS','COMP','GSP') 
	and (:p_ler_type is null
	or
	(:p_ler_type = 'SCHEDULED' and ler.typ_cd in ('SCHEDDO','SCHEDDA','SCHEDDU') )
	or
	(:p_ler_type = 'EXPLICIT' and ler.typ_cd in ('ADDDURENRT','DRVDLSELG','DSBLTY','ENDDSBLTY','DRVDNLP','DRVDPOEELG',
		'INFO','OVRRIDE','DRVDPOERT','PRSNL','REDUHRS','DRVDVEC','DRVDWTGSTF','UNRSTR','WORK','QUAINGR') )
	or 
	(:p_ler_type = 'TEMPORAL' and ler.typ_cd in ('DRVDAGE','DRVDCAL','DRVDCMP','DRVDHRW','DRVDLOS','DRVDTPF') )
	  )
	and :p_run_date between ler.effective_start_date and ler.effective_end_date
	and pil.lf_evt_ocrd_dt between asg.effective_start_date and asg.effective_end_date
	and asg.person_id = pil.person_id
	and asg.location_id = loc.location_id (+)
	and (:p_location_id is null or asg.location_id = :p_location_id)
	and (instr(nvl(:p_assignment_type,assignment_type),assignment_type) >0) 
	and asg.assignment_type <> 'C'	  
	and (:p_organization_id is null or asg.organization_id = :p_organization_id)
	and :p_run_date between per.effective_start_date and per.effective_end_date
	and ((asg.primary_flag = 'Y'  and ((asg.assignment_type='E') or (asg.assignment_type='B')))
	  or
	(asg.assignment_type = 'A'))
	and (
			decode(assignment_type,'E',1,'B',2,'A',3,10) = 
	(
		(SELECT min(decode(assignment_type,'E',1,'B',2,'A',3,10)) min_decd_val
		FROM per_all_assignments_f asg1
		WHERE	
		(	   
			(
				((asg1.assignment_type='E') or (asg1.assignment_type='B')) and 
				(asg1.primary_flag='Y')
			)
			or 
			(asg1.assignment_type='A')  
		)	  
		and asg1.assignment_type <> 'C'	
		and pil.lf_evt_ocrd_dt between asg1.EFFECTIVE_START_DATE and asg1.EFFECTIVE_END_DATE
		and (instr(nvl(:p_assignment_type,assignment_type),assignment_type) >0) 
		and  asg1.person_id=pil.person_id
		)
	)
	)
 and pln.pl_id=:valid_pl_id1
 UNION ALL
select   
	distinct pil.per_in_ler_id per_in_ler_id1,  
	pil.person_id per_id_rep3,
	per.full_name PERNAME3,
	per.national_identifier PERSSN3,
	loc.location_id loc_id_plan_rep3,
	loc_tl.location_code PERLOC3,
	pil.ler_id ler_id_plan_rep3,
	ler_tl.name LERNAME3,
	hr_general.decode_lookup('BEN_PER_IN_LER_STAT', per_in_ler_stat_cd) LESTAT3,
	hr_general.decode_lookup('BEN_LER_TYP', ler.typ_cd) LERTYPE3,
	pil.lf_evt_ocrd_dt LEOCRDDT3,
	pil.ntfn_dt ntfn_dt_plan_rep3,
	pln.pl_id pl_id_plan_rep3,
	pln.name pl_name_plan_rep3,
	attr.counter counter1, 
	dff1.form_left_prompt segment_prompt_text1,
	attr.col_name col_name1, 
	decode(attr.counter, 
			1,PIL.PIL_ATTRIBUTE1 ,
			2,PIL.PIL_ATTRIBUTE2 , 
			3,PIL.PIL_ATTRIBUTE3 ,
			4,PIL.PIL_ATTRIBUTE4 ,
			5,PIL.PIL_ATTRIBUTE5 , 
			6,PIL.PIL_ATTRIBUTE6 ,
			7,PIL.PIL_ATTRIBUTE7 ,
			8,PIL.PIL_ATTRIBUTE8 , 
			9,PIL.PIL_ATTRIBUTE9 ,
			10,PIL.PIL_ATTRIBUTE10 ,
			11,PIL.PIL_ATTRIBUTE11 ,
			12,PIL.PIL_ATTRIBUTE12 , 
			13,PIL.PIL_ATTRIBUTE13 ,
			14,PIL.PIL_ATTRIBUTE14 ,
			15,PIL.PIL_ATTRIBUTE15 , 
			16,PIL.PIL_ATTRIBUTE16 ,
			17,PIL.PIL_ATTRIBUTE17 ,
			18,PIL.PIL_ATTRIBUTE18 , 
			19,PIL.PIL_ATTRIBUTE19 ,
			20,PIL.PIL_ATTRIBUTE20 ,
			21,PIL.PIL_ATTRIBUTE21 ,
			22,PIL.PIL_ATTRIBUTE22 , 
			23,PIL.PIL_ATTRIBUTE23 ,
			24,PIL.PIL_ATTRIBUTE24 ,
			25,PIL.PIL_ATTRIBUTE25 , 
			26,PIL.PIL_ATTRIBUTE26 ,
			27,PIL.PIL_ATTRIBUTE27 ,
			28,PIL.PIL_ATTRIBUTE28 , 
			29,PIL.PIL_ATTRIBUTE29 ,
			30,PIL.PIL_ATTRIBUTE30 
			) attr_val1
from 
			 per_all_people_f per,
			 per_all_assignments_f asg,
			 hr_locations_all loc,hr_locations_all_tl loc_tl,
			 ben_pl_f pln,
			 ben_ler_f ler,ben_ler_f_tl ler_tl,
			 ben_elig_per_elctbl_chc epe,
			 ben_per_in_ler pil,
	 FND_DESCR_FLEX_COL_USAGE_TL dff1,
	   (
   select 1 counter, 'PIL_ATTRIBUTE1' col_name from dual union 
select 2 counter, 'PIL_ATTRIBUTE2' col_name
from dual union 
select 3 counter, 'PIL_ATTRIBUTE3' col_name
from dual union 
select 4 counter, 'PIL_ATTRIBUTE4' col_name
from dual union 
select 5 counter, 'PIL_ATTRIBUTE5' col_name
from dual union 
select 6 counter, 'PIL_ATTRIBUTE6' col_name
from dual union 
select 7 counter, 'PIL_ATTRIBUTE7' col_name
from dual union 
select 8 counter, 'PIL_ATTRIBUTE8' col_name
from dual union 
select 9 counter, 'PIL_ATTRIBUTE9' col_name
from dual union 
select 10 counter, 'PIL_ATTRIBUTE10' col_name
from dual union 
select 11 counter, 'PIL_ATTRIBUTE11' col_name
from dual union 
select 12 counter, 'PIL_ATTRIBUTE12' col_name
from dual union 
select 13 counter, 'PIL_ATTRIBUTE13' col_name
from dual union 
select 14 counter, 'PIL_ATTRIBUTE14' col_name
from dual union 
select 15 counter, 'PIL_ATTRIBUTE15' col_name
from dual union 
select 16 counter, 'PIL_ATTRIBUTE16' col_name
from dual union 
select 17 counter, 'PIL_ATTRIBUTE17' col_name
from dual union 
select 18 counter, 'PIL_ATTRIBUTE18' col_name
from dual union 
select 19 counter, 'PIL_ATTRIBUTE19' col_name
from dual union 
select 20 counter, 'PIL_ATTRIBUTE20' col_name
from dual union 
select 21 counter, 'PIL_ATTRIBUTE21' col_name
from dual union 
select 22 counter, 'PIL_ATTRIBUTE22' col_name
from dual union 
select 23 counter, 'PIL_ATTRIBUTE23' col_name
from dual union 
select 24 counter, 'PIL_ATTRIBUTE24' col_name
from dual union 
select 25 counter, 'PIL_ATTRIBUTE25' col_name
from dual union 
select 26 counter, 'PIL_ATTRIBUTE26' col_name
from dual union 
select 27 counter, 'PIL_ATTRIBUTE27' col_name
from dual union 
select 28 counter, 'PIL_ATTRIBUTE28' col_name
from dual union 
select 29 counter, 'PIL_ATTRIBUTE29' col_name
from dual union 
select 30 counter, 'PIL_ATTRIBUTE30' col_name
from dual  
  ) attr
		where :p_report_module_cd in ('LEBYPLAN','ALLMODLS')
		and :p_run_report ='Y'
		and :P_DISP_FLEX_FIELDS_FLAG ='Y' 
		and dff1.descriptive_flexfield_name = 'BEN_PER_IN_LER' 
		and dff1.APPLICATION_ID = 805
		and dff1.APPLICATION_COLUMN_NAME = attr.col_name
		and ( (dff1.DESCRIPTIVE_FLEX_CONTEXT_CODE = 'Global Data Elements')
		     or 
		  (dff1.DESCRIPTIVE_FLEX_CONTEXT_CODE = nvl(PIL.PIL_attribute_category, 'Global Data Elements'))
		)
		and loc_tl.language=userenv('LANG') 
			and loc_tl.location_id=loc.location_id 
			and ler_tl.language=userenv('LANG') 
			and ler_tl.ler_id=ler.ler_id 
		and ler.effective_start_date = ler_tl.effective_start_date 
		and pil.business_group_id = :p_business_group_id
		and ler.business_group_id = per.business_group_id
		and pil.lf_evt_ocrd_dt between :p_comp_perd_strt_dt and :p_comp_perd_end_dt
		and (:p_person_id is null or per.person_id = :p_person_id)
		and (:p_nat_ident is null or per.national_identifier = :p_nat_ident)
		and pil.person_id = per.person_id
		and asg.person_id = pil.person_id
		and pil.ler_id=ler.ler_id
		and pln.pl_id=epe.pl_id
		and pil.per_in_ler_id=epe.per_in_ler_id
		and ler.ler_id = pil.ler_id
		and :p_run_date >= epe.enrt_cvg_strt_dt
		and pil.per_in_ler_stat_cd='STRTD' 
		and epe.elctbl_flag='Y'
		and epe.comp_lvl_cd in ('PLAN', 'PLIP', 'OIPL')
		and :p_run_date between pln.effective_start_date and pln.effective_end_date
		and (:p_benefit_group_id is null or per.benefit_group_id = :p_benefit_group_id) 
		and :p_run_date between per.effective_start_date and per.effective_end_date 
		and (:p_ler_id is null or ler.ler_id = :p_ler_id)
		and ler.typ_cd not in ('ABS','COMP','GSP') 
		and (:p_ler_type is null
		or
		(:p_ler_type = 'SCHEDULED' and ler.typ_cd in ('SCHEDDO','SCHEDDA','SCHEDDU') )
		or
		(:p_ler_type = 'EXPLICIT' and ler.typ_cd in ('ADDDURENRT','DRVDLSELG','DSBLTY','ENDDSBLTY','DRVDNLP','DRVDPOEELG',
			'INFO','OVRRIDE','DRVDPOERT','PRSNL','REDUHRS','DRVDVEC','DRVDWTGSTF','UNRSTR','WORK','QUAINGR') )
		or 
		(:p_ler_type = 'TEMPORAL' and ler.typ_cd in ('DRVDAGE','DRVDCAL','DRVDCMP','DRVDHRW','DRVDLOS','DRVDTPF') )
		  )
		and :p_run_date between ler.effective_start_date and ler.effective_end_date
		and pil.lf_evt_ocrd_dt between asg.effective_start_date and asg.effective_end_date
		and asg.location_id = loc.location_id (+)
		and (:p_location_id is null or asg.location_id = :p_location_id)
		and (instr(nvl(:p_assignment_type,assignment_type),assignment_type) >0) 
		and asg.assignment_type <> 'C'	  
		and (:p_organization_id is null or asg.organization_id = :p_organization_id)
		and :p_run_date between per.effective_start_date and per.effective_end_date
		and ((asg.primary_flag = 'Y'  and ((asg.assignment_type='E') or (asg.assignment_type='B')))
		  or
		(asg.assignment_type = 'A'))
		and (
						decode(assignment_type,'E',1,'B',2,'A',3,10) = 
		(
			(SELECT min(decode(assignment_type,'E',1,'B',2,'A',3,10)) min_decd_val
			FROM per_all_assignments_f asg1
			WHERE	
			(	   
				(
					((asg1.assignment_type='E') or (asg1.assignment_type='B')) and 
					(asg1.primary_flag='Y')
				)
				or 
				(asg1.assignment_type='A')  
			)	  
			and asg1.assignment_type <> 'C'	
			and pil.lf_evt_ocrd_dt between asg1.EFFECTIVE_START_DATE and asg1.EFFECTIVE_END_DATE
			and (instr(nvl(:p_assignment_type,assignment_type),assignment_type) >0) 
			and  asg1.person_id=pil.person_id
			)
		)
		)
and pln.pl_id=:valid_pl_id1
&p_sort_dff3 
--&p_sort_dff 
-- and pln.pl_id=:valid_pl_id1
Parameter NameSQL textValidation
Business Group Id
 
Display Flexfields
 
LOV Oracle
Sort Order 4
 
LOV Oracle
Sort Order 3
 
LOV Oracle
Sort Order 2
 
LOV Oracle
Sort Order 1
 
LOV Oracle
Life Event Type
 
LOV Oracle
Life Event
 
LOV Oracle
Plan
 
LOV Oracle
Benefit Group
 
LOV Oracle
Reporting Group
 
LOV Oracle
Organization
 
LOV Oracle
Assignment Type
 
LOV Oracle
Location
 
LOV Oracle
Comparison Period End Date
 
Date
Comparison Period Start Date
 
Date
Reporting Period End Date
 
Date
Reporting Period Start Date
 
Date
National Identifier
 
LOV Oracle
Person Name
 
LOV Oracle
PER_DATES
 
Date
Report Module
 
LOV Oracle