[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Person Selection rule failing as seesion
* info is not present in fnd_sessions
* 28-Jan-2003 vsethi 115.5 In reconciliation and discrepancy sections
* added a clause to restrict enrt records
* whose cvg begins before the report end date.
* 30-Jan-2003 vsethi 115.6 Life event name not getting diplayed
* 07-Feb-2003 vsethi 115.7 Removed the p_run_date and p_mon_year parameters
* 19-Feb-2003 vsethi 115.8 2791345 - For person enrolled in multiple options
* premium is not displayed for the second record.
* 30-Jun-03 vsethi 115.9 Changed reference for table ben_rptg_grp
* MLS compliant view ben_rptg_grp_v
* 18-May-04 rpgupta 115.10 3608119 - picks up sum of all rates
* when an unerstricted enrollment is
* done, rates are changed and recalculate
* participant rates is run
* 08-Jun-04 rpgupta 115.11 3608119 - added date check in FUNCTION
* get_prtt_rate_val
* 20-Jul-04 nhunur 115.12 3775260 - Report should not be restricted to Employees.
* and also look at ERPYC acty typ rates in get_rate_val and get_element_val.
* 5-apr-05 nhunur 115.13 Use pds.end_date for queries to pickup rate / element values properly
* if rate changes midway thru pay period.
* 19-May-05 rbingi 115.15 Bug-4383835 Removed to_date for p_report_start_date because its
* already of type date. part of GSCC FireDrill
* ===========================================================================
*/
--
-- Package Variables
--
f_out utl_file.file_type ;
select decode(instr(value,','),0,value,
substrb(translate(ltrim(value),',',' '),
1,
instr(translate(ltrim(value),',',' '),' ') - 1))
into l_audit_log_dir
from v$parameter
where name = 'utl_file_dir';
select pl_oraganization_name pl_org_name,
pl_location_name pl_loc_name,
pl_payroll_name pl_pay_name,
pl_full_name pl_per_name,
pl_national_identifier pl_ntnl_id,
pl_bnft_amount pl_bnft_amt,
pl_prem_val pl_prem_val,
pl_sql_uom pl_uom,
pl_period_type pl_perd_typ,
sum(ee_ptax_rt_val) ee_ptax_rt_val_tot,
sum(ee_atax_rt_val) ee_atax_rt_val_tot,
sum(er_rt_val ) er_rt_val_tot,
sum(ee_ptax_rt_val + ee_atax_rt_val + er_rt_val) pay_perd_total,
sum(ee_ptax_elem_val) ee_ptax_elem_val_tot,
sum(ee_atax_elem_val) ee_atax_elem_val_tot,
sum(er_elem_val ) er_elem_val_tot ,
sum(ee_ptax_elem_val + ee_atax_elem_val + er_elem_val) actual_total
from (select distinct 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 || ' / ' || -- the japanese name format should be kept in sync with hrjputil.pkb
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(pen.uom, mpr.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 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' , 'EX_EMP_APL' , '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.assignment_type = 'E' */
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;
select pl.*
,ee_ptax_rt_val + ee_atax_rt_val + er_rt_val pay_perd_total
,ee_ptax_elem_val + ee_atax_elem_val + er_elem_val actual_total
,((ee_ptax_elem_val + ee_atax_elem_val + er_elem_val) -
(ee_ptax_rt_val + ee_atax_rt_val + er_rt_val)) std_rt_dis
,nvl(((ee_ptax_elem_val + ee_atax_elem_val + er_elem_val ) - pl_prem_val),0) prem_dis
from (select distinct 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 || ' / ' || -- the japanese name format should be kept in sync with hrjputil.pkb
per.per_information18 || ' ' || per.per_information19)
, per.full_name) pl_full_name
,per.person_id pl_person_id
,per.national_identifier pl_national_id
,pen.bnft_amt pl_bnft_amount
,mpr.val pl_prem_val
,nvl(pen.uom,mpr.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 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' , 'EX_EMP_APL' , '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.assignment_type = 'E' */
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
where ((ee_ptax_elem_val + ee_atax_elem_val + er_elem_val) -
(ee_ptax_rt_val + ee_atax_rt_val + er_rt_val)) <> 0
or decode(((ee_ptax_elem_val + ee_atax_elem_val + er_elem_val ) - pl_prem_val),null,0,1) = 1
order by 1,2,3,4;
select * from (
select hr_general.decode_organization(asg.organization_id) lf_org_name,
hr_general.decode_location(asg.location_id) lf_location_name,
hr_general.decode_payroll(asg.payroll_id) lf_payroll_name,
decode(p_emp_name_format,'JP',( per.last_name || ' ' || per.first_name || ' / ' ||
per.per_information18 || ' ' || per.per_information19)
, per.full_name) lf_full_name,
per.national_identifier lf_national_identifier ,
ler.name lf_ler_name ,
nvl(pen.uom,popl.uom) lf_uom,
pil.lf_evt_ocrd_dt,
ben_recn_rep.get_change_eff_dt(pen.prtt_enrt_rslt_id, p_report_start_date,p_run_date) lf_chng_eff_dt,
ben_recn_rep.old_premium_val(pen.person_id,p_pl_id ,pen.pgm_id, pen.oipl_id, p_report_start_date, p_run_date, p_business_group_id, 'PREMIUM') lf_old_prem,
popl.val lf_val,
ben_recn_rep.old_premium_val(pen.person_id,p_pl_id ,pen.pgm_id, pen.oipl_id, p_report_start_date, p_run_date, p_business_group_id, 'RATE') lf_old_rate,
ben_recn_rep.get_new_rates(pen.prtt_enrt_rslt_id,p_report_start_date,p_run_date,p_business_group_id,'RATE',pen.per_in_ler_id) lf_rate,
ben_recn_rep.get_new_rates(pen.prtt_enrt_rslt_id,p_report_start_date,p_run_date,p_business_group_id,'ELEMENT',pen.per_in_ler_id ) lf_elem_val
/*,
( select sum(cmcd_rt_val)
from ben_prtt_rt_val
where prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id) lf_rate ,
( select sum(screen_entry_value)
from ben_prtt_rt_val prv
,pay_element_entry_values_f env
where prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and env.element_entry_value_id = prv.element_entry_value_id) lf_elem_val */
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil,
ben_ler_f ler ,
ben_prtt_prem_f prm ,
ben_prtt_prem_by_mo_f popl,
per_all_people_f per,
per_all_assignments_f asg,
per_person_types ptp
where pen.pl_id = p_pl_id
and (p_pgm_id is null or pen.pgm_id = p_pgm_id )
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_run_date between pen.enrt_cvg_strt_dt and enrt_cvg_thru_dt
and pen.sspndd_flag = 'N'
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 pil.per_in_ler_id = pen.per_in_ler_id
and pil.per_in_ler_stat_cd not in ( 'VOIDD' , 'BCKDT')
and pil.lf_evt_ocrd_dt = ( select max(pil2.LF_EVT_OCRD_DT)
from ben_per_in_ler pil2
where pil2.per_in_ler_id = pen.per_in_ler_id
and pil2.per_in_ler_stat_cd in ( 'STRTD','PROCD')
and pil2.lf_evt_ocrd_dt
between p_report_start_date and p_report_end_date )
and pil.ler_id = ler.ler_id
and per.person_id = pil.person_id
and (p_ntl_identifier is null or per.national_identifier = p_ntl_identifier)
and p_run_date between per.effective_start_date and per.effective_end_date
and ptp.person_type_id = per.person_type_id
and ptp.system_person_type in ( 'EMP' , 'EX_EMP' , 'EX_EMP_APL' , 'EMP_APL' , 'PRTN' )
and asg.person_id = pen.person_id
and asg.business_group_id = p_business_group_id
/* and asg.assignment_type = 'E' */
and asg.primary_flag = 'Y'
and p_run_date between asg.effective_start_date and asg.effective_end_date
and prm.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and p_run_date between prm.effective_start_date and prm.effective_end_date
and p_run_date between ler.effective_start_date and ler.effective_end_date
and prm.prtt_prem_id = popl.prtt_prem_id
and popl.yr_num = to_number(to_char(p_report_start_date,'YYYY'))
and popl.mo_num = to_number(to_char(p_report_start_date,'MM'))
and popl.business_group_id = p_business_group_id
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))
order by 1,2,3,4) pl
where lf_old_prem <> lf_val ;
select distinct decode(apr.pl_id,null,'OIPL','PL') levels ,
hr_general.decode_organization(asg.organization_id) prtt_org_name,
hr_general.decode_location(asg.location_id) prtt_location_name,
hr_general.decode_payroll(asg.payroll_id) prtt_payroll_name,
decode(p_emp_name_format,'JP',( per.last_name || ' ' || per.first_name || ' / ' ||
per.per_information18 || ' ' || per.per_information19)
, per.full_name) full_name ,
per.national_identifier,
pen.enrt_cvg_strt_dt,
decode(pen.enrt_cvg_thru_dt,to_date('31-12-4712','dd-mm-yyyy'),null,pen.enrt_cvg_thru_dt) enrt_cvg_thru_dt ,
popl.val,
nvl(pen.uom, popl.uom) uom,
decode(apr.pl_id, null, ben_batch_utils.get_opt_name(apr.oipl_id, p_business_group_id, p_run_date)) option_name,
apr.oipl_id,
apr.pl_id ,
ben_recn_rep.get_prtt_rate_val(pen.prtt_enrt_rslt_id , p_report_start_date, pen.per_in_ler_id, p_report_end_date) rate-- 3608119
from ben_prtt_enrt_rslt_f pen
,ben_per_in_ler pil
,per_all_people_f per
,per_person_types ptp
,per_all_assignments_f asg
,ben_actl_prem_f apr
,ben_prtt_prem_f prm
,ben_prtt_prem_by_mo_f popl
where pen.pl_id = p_pl_id
and (p_pgm_id is null or pen.pgm_id = p_pgm_id )
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.sspndd_flag = 'N'
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 pil.per_in_ler_id = pen.per_in_ler_id
and pil.per_in_ler_stat_cd not in ( 'VOIDD' , 'BCKDT')
and per.person_id = pil.person_id
and (p_ntl_identifier is null or per.national_identifier = p_ntl_identifier)
and p_run_date between per.effective_start_date and per.effective_end_date
and ptp.person_type_id = per.person_type_id
and ptp.system_person_type in ( 'EMP' , 'EX_EMP' , 'EX_EMP_APL' , 'EMP_APL' , 'PRTN' )
and asg.person_id = pen.person_id
and asg.business_group_id = p_business_group_id
/* and asg.assignment_type = 'E' */
and asg.primary_flag = 'Y'
and p_run_date between asg.effective_start_date and asg.effective_end_date
and prm.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and prm.per_in_ler_id = pen.per_in_ler_id
and apr.actl_prem_id = prm.actl_prem_id
and prm.prtt_prem_id = popl.prtt_prem_id
and popl.yr_num||lpad(popl.mo_num,2,0) between to_number(to_char(p_report_start_date,'YYYYMM'))
and to_number(to_char(p_report_end_date,'YYYYMM'))
and apr.business_group_id = p_business_group_id
and (p_prem_type is null or apr.prsptv_r_rtsptv_cd = p_prem_type )
and p_run_date between apr.effective_start_date and apr.effective_end_date
and popl.business_group_id = p_business_group_id
and p_run_date between prm.effective_start_date and prm.effective_end_date
and ( ( apr.oipl_id is null and apr.pl_id = p_pl_id )
or ( apr.pl_id is null
and apr.oipl_id in ( select oipl_id from ben_oipl_f oipl
where oipl.pl_id = p_pl_id
and oipl.business_group_id = p_business_group_id
and p_run_date between oipl.effective_start_date and oipl.effective_end_date )))
and (p_payroll_id is null or asg.payroll_id = p_payroll_id )
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))
order by 1,2,3,4,5;
l_report(14).col1 := 'Person Selection Rule';
l_report.delete;
l_report.delete;
l_report.delete;
l_disc_report.delete;
l_report.delete;
procedure exec_per_selection_rule
(p_pl_id number,
p_pgm_id number,
p_business_group_id number,
p_run_date date,
p_report_start_date date,
p_prem_type varchar2,
p_payroll_id number,
p_organization_id number,
p_location_id number,
p_benfts_grp_id number,
p_rptg_grp_id number,
p_person_selection_rule_id number,
p_benefit_action_id out nocopy number
) as
--
cursor c_person is
select distinct per.person_id, pil.ler_id
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_all_assignments_f asg
where pen.pl_id = p_pl_id
and (p_pgm_id is null or pen.pgm_id = p_pgm_id)
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_run_date between pen.enrt_cvg_strt_dt and 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 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.assignment_type = 'E' */
and asg.primary_flag = 'Y'
and (p_payroll_id is null or asg.payroll_id = p_payroll_id )
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 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(+)
and pen.pgm_id = prpg.pgm_id(+)
and prpg.rptg_grp_id = p_rptg_grp_id));
,p_person_selection_rl => p_person_selection_rule_id
,p_organization_id => p_organization_id
,p_location_id => p_location_id
,p_request_id => fnd_global.conc_request_id
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_program_update_date => sysdate
,p_object_version_number => l_object_version_number
,p_effective_date => p_run_date
,p_benfts_grp_id => p_benfts_grp_id
,p_payroll_id => p_payroll_id
,p_rptg_grp_id => p_rptg_grp_id
,p_mode_cd => 'U'
,p_derivable_factors_flag => 'N'
,p_validate_flag => 'N'
,p_debug_messages_flag => 'Y'
,p_audit_log_flag => 'N'
,p_no_plans_flag => 'N'
,p_no_programs_flag => 'N'
);
ben_conc_reports.rep_person_selection_rule
(p_person_id => l_person.person_id
,p_business_group_id => p_business_group_id
,p_person_selection_rule_id => p_person_selection_rule_id
,p_effective_date => p_run_date
,p_return => l_rl_ret
,p_err_message => l_err_message ) ;
end exec_per_selection_rule;
select popl.val,
pen.prtt_enrt_rslt_id
from ben_prtt_enrt_rslt_f pen
,ben_per_in_ler pil
,ben_prtt_prem_f prm
,ben_prtt_prem_by_mo_f popl
where pen.pl_id = p_pl_id
and (c_pgm_id is null or pen.pgm_id = c_pgm_id )
and (c_oipl_id is null or pen.oipl_id = c_oipl_id )
and pen.person_id = p_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.enrt_cvg_thru_dt >= pen.effective_end_date
--and enrt_cvg_thru_dt < p_report_start_date --p_run_date
and pen.sspndd_flag = 'N'
and pil.per_in_ler_id = pen.per_in_ler_id
and pil.per_in_ler_stat_cd in ('PROCD' , 'STRTD')
and pil.lf_evt_ocrd_dt = ( select max(pil2.LF_EVT_OCRD_DT)
from ben_per_in_ler pil2
where pil2.per_in_ler_stat_cd in ( 'PROCD' , 'STRTD')
and pil2.lf_evt_ocrd_dt < p_report_start_date
and pil2.person_id = p_person_id )
and prm.effective_start_date = (select max(prm2.effective_start_date)
from ben_prtt_prem_f prm2
where prm2.prtt_prem_id = prm.prtt_prem_id
and prm2.effective_start_date <= p_report_start_date )
and prm.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
--and p_report_start_date >= prm.effective_end_date
and prm.prtt_prem_id = popl.prtt_prem_id
and popl.yr_num||lpad(popl.mo_num,2,0) = to_char(add_months(last_day(p_report_start_date),-1),'YYYYMM')
and popl.business_group_id = p_business_group_id;
select sum(prv.cmcd_rt_val)
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and tx_typ_cd in ('PRETAX' , 'AFTERTAX' , 'NOTAPPLICABLE')
and acty_typ_cd in ('EEPYC','ERC', 'ERPYC')
and add_months(last_day(p_report_start_date),-1) between rt_strt_dt and rt_end_dt;
select count(*)
from ben_prtt_enrt_rslt_f pen
where pen.pl_id = p_pl_id
and pen.person_id = p_person_id
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_run_date between pen.enrt_cvg_strt_dt and enrt_cvg_thru_dt
and pen.sspndd_flag = 'N' ;
select sum(prv.cmcd_rt_val)
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and tx_typ_cd in ('PRETAX' , 'AFTERTAX' , 'NOTAPPLICABLE' )
and acty_typ_cd in ('EEPYC','ERC' , 'ERPYC' )
and prv.per_in_ler_id = p_per_in_ler_id;
select sum(screen_entry_value)
from ben_prtt_rt_val prv,
pay_element_entry_values_f env
where prv.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and env.element_entry_value_id = prv.element_entry_value_id
and tx_typ_cd in ('PRETAX' , 'AFTERTAX' , 'NOTAPPLICABLE' )
and acty_typ_cd in ('EEPYC','ERC' , 'ERPYC')
and prv.per_in_ler_id = p_per_in_ler_id
and p_run_date between env.effective_start_date and env.effective_end_date;
select min(prv.rt_strt_dt)
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and tx_typ_cd in ('PRETAX' , 'AFTERTAX')
and acty_typ_cd in ('EEPYC','ERC') ;
select sum(env.screen_entry_value)
from ben_prtt_rt_val prv,
pay_element_entry_values_f env
where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and (p_tx_typ_cd is null or prv.tx_typ_cd = p_tx_typ_cd)
and prv.acty_typ_cd = p_acty_typ_cd
and prv.prtt_rt_val_stat_cd is null
--and p_run_date between prv.rt_strt_dt and prv.rt_end_dt
and prv.per_in_ler_id = p_per_in_ler_id
and env.element_entry_value_id = prv.element_entry_value_id
and prv.rt_strt_dt between env.effective_start_date and env.effective_end_date
and (p_run_date between prv.rt_strt_dt and prv.rt_end_dt
or
nvl(p_run_date_end, prv.rt_strt_dt) between prv.rt_strt_dt and prv.rt_end_dt
);-- 3608119
select sum(env.screen_entry_value)
from ben_prtt_rt_val prv,
pay_element_entry_values_f env
where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and (p_tx_typ_cd is null or prv.tx_typ_cd = p_tx_typ_cd)
and prv.acty_typ_cd in ('ERC' , 'ERPYC' )
and prv.prtt_rt_val_stat_cd is null
--and p_run_date between prv.rt_strt_dt and prv.rt_end_dt
and prv.per_in_ler_id = p_per_in_ler_id
and env.element_entry_value_id = prv.element_entry_value_id
and prv.rt_strt_dt between env.effective_start_date and env.effective_end_date
and (p_run_date between prv.rt_strt_dt and prv.rt_end_dt
or
nvl(p_run_date_end, prv.rt_strt_dt) between prv.rt_strt_dt and prv.rt_end_dt
);
select sum(prv.cmcd_rt_val)
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and (p_tx_typ_cd is null or prv.tx_typ_cd = p_tx_typ_cd)
and prv.acty_typ_cd = p_acty_typ_cd
and prv.per_in_ler_id = p_per_in_ler_id
and (p_run_date between prv.rt_strt_dt and prv.rt_end_dt
or
nvl(p_run_date_end, prv.rt_strt_dt) between prv.rt_strt_dt and prv.rt_end_dt
)-- 3608119
;
select sum(prv.cmcd_rt_val)
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and (p_tx_typ_cd is null or prv.tx_typ_cd = p_tx_typ_cd)
and prv.acty_typ_cd in ('ERC' , 'ERPYC' )
and prv.per_in_ler_id = p_per_in_ler_id
and (p_run_date between prv.rt_strt_dt and prv.rt_end_dt
or
nvl(p_run_date_end, prv.rt_strt_dt) between prv.rt_strt_dt and prv.rt_end_dt
);
select sum(rt_val) rate
from ben_prtt_rt_val prv
where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and tx_typ_cd in ('PRETAX' , 'AFTERTAX', 'NOTAPPLICABLE')
and acty_typ_cd in ('EEPYC','ERC')
and prv.per_in_ler_id = p_per_in_ler_id
and (p_run_date between prv.rt_strt_dt and prv.rt_end_dt
or
nvl(p_run_date_end, prv.rt_strt_dt) between prv.rt_strt_dt and prv.rt_end_dt)--3608119
;
select per.full_name
from per_all_people_f per
where per.person_id = p_person_id
and p_run_date between nvl(per.effective_start_date,p_run_date)
and nvl(per.effective_end_date,p_run_date);
select formula_name
from ff_formulas_f ff
where ff.formula_id(+) = p_per_sel_rule_id
and p_run_date between nvl(ff.effective_start_date,p_run_date)
and nvl(ff.effective_end_date,p_run_date);
select name
from ben_benfts_grp
where benfts_grp_id = p_benfts_grp_id;
select name
from ben_rptg_grp_v
where rptg_grp_id = p_rptg_grp_id;
select pay.payroll_name
from pay_all_payrolls_f pay
where pay.payroll_id = p_payroll_id
and p_run_date between pay.effective_start_date and pay.effective_end_date ;