BEN Reconciliation of Premium Contributions to Element Entries
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Reconciliation of Premium Contributions to Element Entries
Application: Advanced Benefits
Source: Reconciliation of Premium Contributions to Element Entries Report (XML)
Short Name: BENRECON_XML
DB package: BEN_BENRECON_XMLP_PKG
Description: Reconciliation of Premium Contributions to Element Entries
Application: Advanced Benefits
Source: Reconciliation of Premium Contributions to Element Entries Report (XML)
Short Name: BENRECON_XML
DB package: BEN_BENRECON_XMLP_PKG
Run
BEN Reconciliation of Premium Contributions to Element Entries and other Oracle EBS reports with Blitz Report™ on our demo environment
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 | |
---|---|---|---|
Reporting Start Date |
|
Date | |
PER_DATES |
|
Date | |
Employee Name |
|
LOV Oracle | |
National Identifier |
|
LOV Oracle | |
Employee Name Format |
|
LOV Oracle | |
Program |
|
LOV Oracle | |
Plan |
|
LOV Oracle | |
Person Selection Rule |
|
LOV Oracle | |
Organization |
|
LOV Oracle | |
Location |
|
LOV Oracle | |
Benefits Group |
|
LOV Oracle | |
Reporting Group |
|
LOV Oracle | |
Payroll |
|
LOV Oracle | |
Premium Type |
|
LOV Oracle | |
Output Type |
|
LOV Oracle | |
Display Plan Reconciliation Report |
|
LOV Oracle | |
Display Plan Discrepancies Report |
|
LOV Oracle | |
Display Life Event Report |
|
LOV Oracle | |
Display Plan Participant Details Report |
|
LOV Oracle |