BEN Reconciliation of Premium Contributions to Element Entries

select  pl_oraganization_name, pl_location_name,  pl_payroll_name ,
	pl_full_name, pl_national_identifier, pl_bnft_amount , pl_prem_val, pl_sql_uom, pl_period_type,
	sum(ee_ptax_rt_val) , sum(ee_atax_rt_val) , sum(er_rt_val ), sum(ee_ptax_rt_val +  ee_atax_rt_val + er_rt_val) pay_perd_total1,
	sum(ee_ptax_elem_val) , sum(ee_atax_elem_val) , sum(er_elem_val ), sum(ee_ptax_elem_val +  ee_atax_elem_val + er_elem_val) actual_total1, 
	BEN_BENRECON_XMLP_PKG.cf_dsicrepencyformula(sum ( ee_ptax_rt_val + ee_atax_rt_val + er_rt_val ), sum ( ee_ptax_elem_val + ee_atax_elem_val + er_elem_val ), pl_prem_val, pl_sql_uom) CF_DSICREPENCY
from (
select distinct
 	   pen.prtt_enrt_rslt_id , 
	   hr_general.decode_organization(asg.organization_id)	   pl_oraganization_name 
      ,hr_general.decode_location(asg.location_id)		   pl_location_name 
      ,pay.payroll_name 	   pl_payroll_name 
      ,decode(:p_emp_name_format,'JP',( per.last_name || ' ' || per.first_name || ' / ' ||    	      				       per.per_information18 || ' ' || per.per_information19)
	      			       , per.full_name)  pl_full_name 
      ,per.national_identifier pl_national_identifier 
      ,pen.bnft_amt 		   pl_bnft_amount 
      ,mpr.val		   		   pl_prem_val 
      ,nvl(mpr.uom , pen.uom)  pl_sql_uom 
      ,pay.period_type 		   pl_period_type 
      ,pds.period_name 		   pl_period_name 
      ,to_char(pds.start_date,'MM/DD')|| ' - '|| to_char(pds.end_date,'MM/DD') pl_pay_prd
      ,ben_recn_rep.get_rate_val(pen.prtt_enrt_rslt_id,pds.end_date,:p_business_group_id,'PRETAX','EEPYC',pen.per_in_ler_id, pds.end_date) ee_ptax_rt_val 
      ,ben_recn_rep.get_rate_val(pen.prtt_enrt_rslt_id,pds.end_date,:p_business_group_id,'AFTERTAX','EEPYC',pen.per_in_ler_id, pds.end_date) ee_atax_rt_val 
      ,ben_recn_rep.get_rate_val(pen.prtt_enrt_rslt_id,pds.end_date,:p_business_group_id,null,'ERC',pen.per_in_ler_id, pds.end_date) er_rt_val 
      ,ben_recn_rep.get_element_val(pen.prtt_enrt_rslt_id,pds.end_date,:p_business_group_id,'PRETAX','EEPYC',pen.per_in_ler_id, pds.end_date) ee_ptax_elem_val 
      ,ben_recn_rep.get_element_val(pen.prtt_enrt_rslt_id,pds.end_date,:p_business_group_id,'AFTERTAX','EEPYC',pen.per_in_ler_id, pds.end_date) ee_atax_elem_val 
      ,ben_recn_rep.get_element_val(pen.prtt_enrt_rslt_id,pds.end_date,:p_business_group_id,null,'ERC',pen.per_in_ler_id, pds.end_date) er_elem_val
from  ben_prtt_enrt_rslt_f pen 
     ,ben_actl_prem_f acp 
     ,ben_prtt_prem_f prm 
     ,ben_prtt_prem_by_mo_f mpr 
     ,ben_per_in_ler pil 
     ,per_all_people_f per 
     ,per_person_types ptp 
     ,pay_all_payrolls_f pay 
     ,per_time_periods pds 
     ,per_all_assignments_f asg 
where :p_dsply_recn = 'Y'
and   pen.pl_id = :p_pl_id 
and   (pen.pgm_id = :p_pgm_id or :p_pgm_id is null) 
and   pen.prtt_enrt_rslt_stat_cd is null 
and   pen.business_group_id = :p_business_group_id 
and    pen.enrt_cvg_thru_dt >= pen.effective_end_date 
and   (:p_report_start_date between pen.enrt_cvg_strt_dt  and pen.enrt_cvg_thru_dt
       or :p_report_end_date between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
       or (:p_report_start_date >= pen.enrt_cvg_strt_dt and :p_report_end_date <= pen.enrt_cvg_thru_dt)
       or (:p_report_start_date <= pen.enrt_cvg_strt_dt and :p_report_end_date >= pen.enrt_cvg_thru_dt))
and   ((pen.effective_end_date < pen.enrt_cvg_thru_dt 
  	    and	(:p_report_start_date between pen.effective_start_date and pen.effective_end_date
            or :p_report_end_date between pen.effective_start_date and pen.effective_end_date
            or (:p_report_start_date >= pen.effective_start_date and :p_report_end_date <= pen.effective_end_date)
            or (:p_report_start_date <= pen.effective_start_date and :p_report_end_date >= pen.effective_end_date)))
	   or pen.effective_end_date >= pen.enrt_cvg_thru_dt   )	  
and   pen.sspndd_flag = 'N' 
and   pen.per_in_ler_id = pil.per_in_ler_id 
and   pil.per_in_ler_stat_cd not in ( 'VOIDD' , 'BCKDT') 
and   pil.person_id = per.person_id 
and   ptp.person_type_id = per.person_type_id 
and   ptp.system_person_type IN ( 'EMP' ,'EX_EMP','EMP_APL','EX_EMP_APL', 'PRTN' )
and   (:p_person_id is null or pen.person_id = :p_person_id) 
and   (:p_per_sel_rule is null or pen.person_id in (select person_id 
                                                    from ben_person_actions pac 
                                                    where pac.benefit_action_id = :p_benefit_action_id) ) 
and   (:p_ntl_identifier is null or per.national_identifier = :p_ntl_identifier) 
and   pen.prtt_enrt_rslt_id = prm.prtt_enrt_rslt_id (+) 
and   mpr.prtt_prem_id(+) = prm.prtt_prem_id 
and   mpr.yr_num(+) = to_number(to_char(:p_report_start_date,'YYYY')) 
and   mpr.mo_num(+) = to_number(to_char(:p_report_start_date,'MM')) 
and   prm.per_in_ler_id (+) = pen.per_in_ler_id 
and   acp.actl_prem_id (+) = prm.actl_prem_id 
and   (:p_prem_type is null or acp.prsptv_r_rtsptv_cd = :p_prem_type ) 
and   pen.person_id = asg.person_id 
and   pen.business_group_id = :p_business_group_id 
and   asg.business_group_id = :p_business_group_id 
and   asg.primary_flag = 'Y' 
and   (:p_payroll_id is null or asg.payroll_id = :p_payroll_id ) 
and   asg.payroll_id = pay.payroll_id 
and   pds.payroll_id = pay.payroll_id 
and   pds.start_date >= :p_report_start_date 
and   pds.end_date   <= :p_report_end_date 
and   per.business_group_id = :p_business_group_id 
and   :p_run_date between per.effective_start_date and per.effective_end_date 
and   :p_run_date between asg.effective_start_date and asg.effective_end_date 
and   :p_run_date between pay.effective_start_date and pay.effective_end_date 
and   :p_run_date between acp.effective_start_date (+) and acp.effective_end_date (+) 
and	  :p_run_date >= prm.effective_start_date (+) 
and	  :p_run_date <= prm.effective_end_date (+) 
and   (:p_organization_id is null 
	       or asg.organization_id = :p_organization_id )
	and   (:p_location_id is null 
	       or asg.location_id = :p_location_id) 
	and   (:p_benfts_grp_id is null 
	       or per.benefit_group_id = :p_benfts_grp_id) 
	and   (:p_rptg_grp_id is null 
	       or exists (select null 
	                  from   ben_popl_rptg_grp_f   prpg 
			  where ( pen.pl_id = prpg.pl_id
			  or     pen.pgm_id = prpg.pgm_id)
			  and    prpg.rptg_grp_id = :p_rptg_grp_id))
) pl 
group by pl_oraganization_name, pl_location_name,  pl_payroll_name ,
	pl_full_name, pl_national_identifier, pl_bnft_amount , pl_prem_val, pl_sql_uom, pl_period_type
order by 1,2,3,4