BEN Life Events Summary

Description

Imported from BI Publisher
Description: Life Events Summary Report
Application: Advanced Benefits
Source: Life Events Summary Report (XML)
Short Name: BENLESUM_XML
DB package: BEN_BENLESUM_XMLP_PKG

BEN Life Events Summary and other Oracle EBS reports with Blitz Report™ on our demo environment

Copy

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

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 Name

SQL text

Validation

Report Module

LOV Oracle

PER_DATES

Date

Person Name