BEN Reconciliation of Premium Contributions to Element Entries

Description
Categories: BI Publisher, Human Resources
Application: Advanced Benefits
Source: Reconciliation of Premium Contributions to Element Entries Report (XML)
Short Name: BENRECON_XML
DB package: BEN_BENRECON_XMLP_PKG
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
Parameter Name SQL text Validation
Display Participant Details
 
LOV Oracle
Business Group
 
Display Plan Participant Details Report
 
LOV Oracle
Display Life Event Report
 
LOV Oracle
Display Plan Discrepancies Report
 
LOV Oracle
Display Plan Reconciliation Report
 
LOV Oracle
Output Type
 
LOV Oracle
Premium Type
 
LOV Oracle
Payroll
 
LOV Oracle
Reporting Group
 
LOV Oracle
Benefits Group
 
LOV Oracle
Location
 
LOV Oracle
Organization
 
LOV Oracle
Person Selection Rule
 
LOV Oracle
Plan
 
LOV Oracle
Program
 
LOV Oracle
Employee Name Format
 
LOV Oracle
National Identifier
 
LOV Oracle
Employee Name
 
LOV Oracle
PER_DATES
 
Date
Reporting Start Date
 
Date