[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select oipl.opt_id, oipl.pl_id, pl.pl_typ_id
from ben_oipl_f oipl, ben_pl_f pl
where oipl.oipl_id = p_oipl_id
and pl.pl_id = oipl.pl_id
and p_effective_date
between pl.effective_start_date
and pl.effective_end_date
and p_effective_date
between oipl.effective_start_date
and oipl.effective_end_date;
select pl.pl_id, pl.pl_typ_id
from ben_pl_f pl
where pl.pl_id = p_pl_id
and p_effective_date
between pl.effective_start_date
and pl.effective_end_date;
select plip.pgm_id
from ben_plip_f plip
where plip.pgm_id = p_pgm_id
and plip.pl_id = p_pl_id
and p_effective_date
between plip.effective_start_date
and plip.effective_end_date;
select distinct pen.person_id , pen.pgm_id, nvl(pen.bnft_amt,0) bnft_amt
from ben_prtt_enrt_rslt_f pen,
per_all_people_f per -- Bug 1750817 : Filter out enrollments of deleted person.
where per.person_id = pen.person_id
and per.business_group_id = pen.business_group_id
and p_effective_date between per.effective_start_date
and per.effective_end_date
and pen.prtt_enrt_rslt_stat_cd is null
and pen.sspndd_flag = 'N'
and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP') -- not a dummy plan
-- cvg is active entire month
and ((pen.enrt_cvg_strt_dt <= p_last_day_of_month
and pen.enrt_cvg_thru_dt >= p_first_day_of_month)
or
-- is no washrule and cvg was for at least part of the month
(pen.enrt_cvg_strt_dt <= p_last_day_of_month
and pen.enrt_cvg_thru_dt >= p_first_day_of_month
and p_wsh_rl_dy_mo_num is null)
or
-- if washrule there, and cvg strts this month it starts before wash day.
(p_wsh_rl_dy_mo_num is not null
and pen.enrt_cvg_strt_dt between
p_first_day_of_month and p_last_day_of_month
and to_char(pen.enrt_cvg_strt_dt,'dd') < p_wsh_rl_dy_mo_num )
or
-- if washrule there, and cvg end this month it ends after wash day.
(p_wsh_rl_dy_mo_num is not null
and pen.enrt_cvg_thru_dt between
p_first_day_of_month and p_last_day_of_month
and to_char(pen.enrt_cvg_thru_dt,'dd') > p_wsh_rl_dy_mo_num ))
and ((pen.pl_id = p_pl_id and pen.oipl_id is null) or p_pl_id is null)
and (pen.oipl_id = p_oipl_id or p_oipl_id is null)
and pen.business_group_id = p_business_group_id
/* Bug#2903964 - it is better to get the results based on effective end date rather
filtering on effective_date
and p_effective_date between
pen.effective_start_date and pen.effective_end_date */
and pen.effective_end_date = hr_api.g_eot;
select nvl(vpf.val,0) val, vpf.upr_lmt_val, vpf.upr_lmt_calc_rl
,vpf.lwr_lmt_val, vpf.lwr_lmt_calc_rl, vpf.rndg_cd,
vpf.rndg_rl, vpf.bnft_rt_typ_cd, vpf.mlt_cd
from ben_vrbl_rt_prfl_f vpf
where vpf.vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
and vpf.business_group_id = p_business_group_id
and p_effective_date between
vpf.effective_start_date and vpf.effective_end_date;
select 'Y'
from ben_vrbl_rt_prfl_f vpf, ben_actl_prem_vrbl_rt_f apv
where apv.actl_prem_id = p_actl_prem_id
and apv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
and ((vpf.alwys_cnt_all_prtts_flag = 'N' and exists
(select 'x' from ben_ttl_prtt_rt_f ttp
where ttp.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id))
or (vpf.alwys_sum_all_cvg_flag = 'N'and exists
(select 'x' from ben_ttl_cvg_vol_rt_f tcv
where tcv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id)))
and vpf.business_group_id = p_business_group_id
and p_effective_date between
vpf.effective_start_date and vpf.effective_end_date;
l_insert_record varchar2(1);
select apr.wsh_rl_dy_mo_num, apr.actl_prem_id, apr.prem_asnmt_lvl_cd,
apr.val, apr.uom, apr.pl_id, apr.oipl_id, apr.bnft_rt_typ_cd,
apr.rndg_cd, apr.rndg_rl, apr.upr_lmt_calc_rl, apr.upr_lmt_val,
apr.lwr_lmt_calc_rl, apr.lwr_lmt_val, apr.prsptv_r_rtsptv_cd,
apr.mlt_cd, apr.cost_allocation_keyflex_id, apr.vrbl_rt_add_on_calc_rl
from ben_actl_prem_f apr
where apr.actl_prem_id = p_actl_prem_id
and p_effective_date between
apr.effective_start_date and apr.effective_end_date;
select count('s') num_of_prtts, sum(nvl(pen.bnft_amt,0)) total_cvg
from ben_prtt_enrt_rslt_f pen,
per_all_people_f per -- Bug 1750817 : Filter out enrollments of deleted person.
where per.person_id = pen.person_id
and per.business_group_id = pen.business_group_id
and p_effective_date between per.effective_start_date
and per.effective_end_date
and pen.prtt_enrt_rslt_stat_cd is null
and pen.sspndd_flag = 'N'
and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP') -- not a dummy plan
-- cvg is active entire month
and ((pen.enrt_cvg_strt_dt <= p_last_day_of_month
and pen.enrt_cvg_thru_dt >= p_first_day_of_month)
or
-- is no washrule and cvg was for at least part of the month
(pen.enrt_cvg_strt_dt <= p_last_day_of_month
and pen.enrt_cvg_thru_dt >= p_first_day_of_month
and p_wsh_rl_dy_mo_num is null)
or
-- if washrule there, and cvg strts this month it starts before wash day.
(p_wsh_rl_dy_mo_num is not null
and pen.enrt_cvg_strt_dt between
p_first_day_of_month and p_last_day_of_month
and to_char(pen.enrt_cvg_strt_dt,'dd') < p_wsh_rl_dy_mo_num )
or
-- if washrule there, and cvg end this month it ends after wash day.
(p_wsh_rl_dy_mo_num is not null
and pen.enrt_cvg_thru_dt between
p_first_day_of_month and p_last_day_of_month
and to_char(pen.enrt_cvg_thru_dt,'dd') > p_wsh_rl_dy_mo_num ))
and ((pen.pl_id = p_pl_id and pen.oipl_id is null) or p_pl_id is null)
and (pen.oipl_id = p_oipl_id or p_oipl_id is null)
and pen.business_group_id = p_business_group_id
/* Bug#2903964 - it is better to get the results based on effective end date rather
filtering on effective_date
and p_effective_date between
pen.effective_start_date and pen.effective_end_date */
and pen.effective_end_date = hr_api.g_eot;
select pen.prtt_enrt_rslt_id, pen.person_id, pen.pl_id, pen.oipl_id,
pen.pgm_id, pen.pl_typ_id,
/* Start of Code Change for WWBUG: 1646442: added following table */
pen.enrt_cvg_strt_dt
/* End of Code Change for WWBUG: 1646442 */
from ben_prtt_enrt_rslt_f pen,
per_all_people_f per -- Bug 1750817 : Filter out enrollments of deleted person.
where per.person_id = pen.person_id
and per.business_group_id = pen.business_group_id
and p_effective_date between per.effective_start_date
and per.effective_end_date
and pen.prtt_enrt_rslt_stat_cd is null
and pen.sspndd_flag = 'N'
and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP') -- not a dummy plan
-- cvg is active entire month
and ((pen.enrt_cvg_strt_dt <= p_last_day_of_month
and pen.enrt_cvg_thru_dt >= p_first_day_of_month)
or
-- is no washrule and cvg was for at least part of the month
(pen.enrt_cvg_strt_dt <= p_last_day_of_month
and pen.enrt_cvg_thru_dt >= p_first_day_of_month
and p_wsh_rl_dy_mo_num is null)
or
-- if washrule there, and cvg strts this month it starts before wash day.
(p_wsh_rl_dy_mo_num is not null
and pen.enrt_cvg_strt_dt between
p_first_day_of_month and p_last_day_of_month
and to_char(pen.enrt_cvg_strt_dt,'dd') < p_wsh_rl_dy_mo_num )
or
-- if washrule there, and cvg end this month it ends after wash day.
(p_wsh_rl_dy_mo_num is not null
and pen.enrt_cvg_thru_dt between
p_first_day_of_month and p_last_day_of_month
and to_char(pen.enrt_cvg_thru_dt,'dd') > p_wsh_rl_dy_mo_num ))
and ((pen.pl_id = p_pl_id and pen.oipl_id is null) or p_pl_id is null)
and (pen.oipl_id = p_oipl_id or p_oipl_id is null)
and pen.business_group_id = p_business_group_id
/* Bug#2903964 - it is better to get the results based on effective end date rather
filtering on effective_date
and p_effective_date between
pen.effective_start_date and pen.effective_end_date*/
and pen.effective_end_date = hr_api.g_eot;
select ppe.std_prem_uom, ppe.prtt_prem_id
from ben_prtt_prem_f ppe,
ben_per_in_ler pil
where ppe.actl_prem_id = p_actl_prem_id
and ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and ppe.business_group_id = p_business_group_id
and p_effective_date between
ppe.effective_start_date and ppe.effective_end_date
and pil.per_in_ler_id(+)=ppe.per_in_ler_id
and pil.business_group_id(+)=ppe.business_group_id
and (pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') -- found row condition
or pil.per_in_ler_stat_cd is null -- outer join condition
);
select pbm.pl_r_oipl_prem_by_mo_id, pbm.object_version_number
from ben_pl_r_oipl_prem_by_mo_f pbm
where pbm.mo_num = p_mo_num
and pbm.yr_num = p_yr_num
and pbm.actl_prem_id = p_actl_prem_id ;
select prm.prtt_prem_by_mo_id,prm.val,prm.cr_val,mnl_adj_flag, prm.object_version_number
from ben_prtt_prem_by_mo_f prm
where prm.mo_num = p_mo_num
and prm.yr_num = p_yr_num
and prm.prtt_prem_id = p_prtt_prem_id
order by prm.effective_start_date ;
select prm.prtt_prem_by_mo_id,prm.val,prm.cr_val,mnl_adj_flag, prm.object_version_number
from ben_prtt_prem_by_mo_f prm
where prm.mo_num = p_mo_num
and prm.yr_num = p_yr_num
and prm.prtt_prem_id = p_prtt_prem_id
and p_effective_dt between prm.effective_start_date and prm.effective_end_date;
,p_program_update_date => sysdate
/* CODE PRIOR TO WWBUG: 1646442
,p_effective_date => p_effective_date);
,p_program_update_date => sysdate
,p_effective_date => l_effective_date_mo); --p_effective_date);
hr_utility.set_location ('update the premium:'|| l_prm.prtt_prem_by_mo_id, 10) ;
ben_prtt_prem_by_mo_api.update_prtt_prem_by_mo
(p_prtt_prem_by_mo_id => l_prm.prtt_prem_by_mo_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_mnl_adj_flag => 'N'
,p_val => l_prtt_val
,p_cr_val => null
,p_alctd_val_flag => 'Y'
,p_uom => l_ppe.std_prem_uom -- uom from prtt_prem if exists
,p_cost_allocation_keyflex_id => l_prems.cost_allocation_keyflex_id
,p_object_version_number => l_prm.object_version_number
,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_effective_date => l_last_effective_dt
,p_datetrack_mode => hr_api.g_correction);
ben_prtt_prem_by_mo_api.update_prtt_prem_by_mo
(p_prtt_prem_by_mo_id => l_prm.prtt_prem_by_mo_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_mnl_adj_flag => 'N'
,p_val => l_prtt_val
,p_alctd_val_flag => 'Y'
,p_uom => l_ppe.std_prem_uom -- uom from prtt_prem if exists
,p_cost_allocation_keyflex_id => l_prems.cost_allocation_keyflex_id
,p_object_version_number => l_prm.object_version_number
,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_effective_date => l_last_effective_dt
,p_datetrack_mode => hr_api.g_correction);
,p_program_update_date => sysdate
,p_effective_date => l_effective_date_mo ); -- p_effective_date);
ben_pl_r_oipl_prem_by_mo_api.update_pl_r_oipl_prem_by_mo
(p_pl_r_oipl_prem_by_mo_id => l_pbm.pl_r_oipl_prem_by_mo_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_mnl_adj_flag => 'N'
,p_val => l_val
,p_uom => l_prems.uom -- uom from actl_prem
,p_prtts_num => l_results.num_of_prtts
,p_cost_allocation_keyflex_id => l_cak
,p_object_version_number => l_pbm.object_version_number
,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_effective_date => l_effective_date_mo -- p_effective_date
,p_datetrack_mode => hr_api.g_correction);