BEN Life Events Summary

Description

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

Imported from BI Publisher
Life Events Summary Report

SQL 403 lines

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

Parameters 21

Parameter	Type	List of values SQL
Report Module	LOV Oracle
PER_DATES	Date
Person Name	LOV Oracle
National Identifier	LOV Oracle
Reporting Period Start Date	Date
Reporting Period End Date	Date
Comparison Period Start Date	Date
Comparison Period End Date	Date
Location	LOV Oracle
Assignment Type	LOV Oracle
Organization	LOV Oracle
Reporting Group	LOV Oracle
Benefit Group	LOV Oracle
Plan	LOV Oracle
Life Event	LOV Oracle
Life Event Type	LOV Oracle
Sort Order 1	LOV Oracle
Sort Order 2	LOV Oracle
Sort Order 3	LOV Oracle
Sort Order 4	LOV Oracle
Display Flexfields	LOV Oracle

Description

SQL 403 lines

Parameters 21

Find out more

Features

Download

FAQ