[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_person_selection_rule_id in number default null,
p_comp_selection_rule_id in number default null,
p_pgm_id in number default null,
p_pl_typ_id in number default null,
p_organization_id in number default null,
p_legal_entity_id in number default null,
p_business_group_id in number,
p_mo_num in number,
p_yr_num in number,
p_first_day_of_month in date,
p_effective_date in date) is
--
l_package varchar2(80) := g_package||'.main';
select distinct pen.prtt_enrt_rslt_id, pen.enrt_cvg_thru_dt, pen.pgm_id,
pen.pl_id, pen.oipl_id, pen.person_id, pen.ler_id, pen.pl_typ_id,
pen.prtt_enrt_rslt_stat_cd, pen.sspndd_flag, pil.per_in_ler_stat_cd,
pen.effective_start_date, pen.effective_end_date,
pen.enrt_cvg_strt_dt
from ben_prtt_enrt_rslt_f pen, ben_prtt_prem_f ppe,
ben_per_in_ler pil
,ben_prtt_prem_by_mo_f prm, ben_actl_prem_f apr
where
( (pen.sspndd_flag = 'N'
-- Credits for 'normal' stuff and
-- Task 417: Credits for:
-- c. voided results (RETRO and PRO).
and (pen.prtt_enrt_rslt_stat_cd is null
or pen.prtt_enrt_rslt_stat_cd = 'VOIDD')
-- result effective START date is this month
-- and pen.effective_start_date between
-- p_first_day_of_month and p_effective_date
-- cvg ended prior to the end of this month - needed for prospective
-- for retro we really care about cvg ending last month.
and pen.enrt_cvg_thru_dt between add_months(p_effective_date, - (apr.cr_lkbk_val))
and p_effective_date
-- a premium was paid for the month in which coverage ended
and ((prm.mo_num = to_char(pen.enrt_cvg_thru_dt,'mm')
and prm.yr_num = to_char(pen.enrt_cvg_thru_dt,'yyyy'))
-- or a premium was paid for the month after cvg ended
or (prm.mo_num = to_char(add_months(pen.enrt_cvg_thru_dt,1),'mm')
and prm.yr_num = to_char(add_months(pen.enrt_cvg_thru_dt,1),'yyyy')))
and pil.per_in_ler_stat_cd not in ('BCKDT')
and p_effective_date between
ppe.effective_start_date and ppe.effective_end_date
and p_effective_date between
prm.effective_start_date and prm.effective_end_date)
-- Task 415: Credits for:
-- b. suspended results (PRO).
or (pen.sspndd_flag = 'Y'
and pen.prtt_enrt_rslt_stat_cd is null
-- result effective START date is this month
and pen.effective_start_date between
p_first_day_of_month and p_effective_date
-- this will only happen for prospective, this is redundant
and apr.prsptv_r_rtsptv_cd = 'PRO'
-- a premium was paid for the month in which coverage was suspended
and ((prm.mo_num = to_char(p_effective_date,'mm')
and prm.yr_num = to_char(p_effective_date,'yyyy')))
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and p_effective_date between
ppe.effective_start_date and ppe.effective_end_date
and p_effective_date between
prm.effective_start_date and prm.effective_end_date)
-- Task 415: create prem credits for:
-- INTERIM dt-ended before cvg started criteria (PRO):
or (pen.prtt_enrt_rslt_stat_cd is null
-- rows where result was ended, not just date-track updated.
and pen.object_version_number = (select max(object_version_number)
from ben_prtt_enrt_rslt_f p where p.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id)
-- cvg started sometime this month - a pro prem could have been written
and pen.enrt_cvg_strt_dt between p_first_day_of_month and p_effective_date
-- result effective END date is this month
and pen.effective_end_date between
p_first_day_of_month and p_effective_date
-- date track ended before cvg started.
and pen.effective_end_date < pen.enrt_cvg_strt_dt
-- a premium was paid for this month
and (prm.mo_num = to_char(p_first_day_of_month,'mm')
and prm.yr_num = to_char(p_first_day_of_month,'yyyy'))
-- this will only happen for prospective, this is redundant
and apr.prsptv_r_rtsptv_cd = 'PRO'
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT'))
-- Task 417: create prem credits for:
-- BACKED OUT per in ler criteria (RETRO AND PRO):
-- when the premium processfor backedout le
-- get all the prem_by_mo for the period between the process and lkbk prd
-- then find ler_id for the premium is backed out and make sure credit entries are not alread ycreated for the
-- premium bug 3692290
or (pil.per_in_ler_stat_cd in ( 'BCKDT' ,'VOIDD')
--- and pil.bckt_dt between
--- p_first_day_of_month and p_effective_date
--- and p_effective_date between
--- ppe.effective_start_date and ppe.effective_end_date
--- and p_effective_date between
--- prm.effective_start_date and prm.effective_end_date
and pil.per_in_ler_id = ppe.per_in_ler_id
and ppe.prtt_prem_id = prm.prtt_prem_id
and prm.cr_val is null
and prm.effective_start_date between add_months(p_effective_date, - (apr.cr_lkbk_val))
and p_effective_date
and p_effective_date between
ppe.effective_start_date and ppe.effective_end_date
and p_effective_date between
prm.effective_start_date and prm.effective_end_date
and not exists
( select pmo.prtt_prem_by_mo_id from ben_prtt_prem_by_mo_f pmo
where pmo.prtt_prem_by_mo_id = prm.prtt_prem_by_mo_id
and pmo.cr_val is not null
)
)
)
and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP') -- not a dummy plan
and (pen.pl_id = p_pl_id or p_pl_id is null)
and (pen.pl_typ_id = p_pl_typ_id or p_pl_typ_id is null)
and (pen.pgm_id = p_pgm_id or p_pgm_id is null)
and (pen.person_id = p_person_id or p_person_id is null)
-- premium was not already credited
and nvl(prm.cr_val,0) = 0
-- credit look backs defined
and apr.cr_lkbk_val is not null
and apr.cr_lkbk_val <>0 -- bug 1213601
and pen.business_group_id = p_business_group_id
and pen.prtt_enrt_rslt_id = ppe.prtt_enrt_rslt_id
and ppe.prtt_prem_id = prm.prtt_prem_id
and ppe.actl_prem_id = apr.actl_prem_id
and apr.prem_asnmt_cd = 'ENRT'
and p_effective_date between
apr.effective_start_date and apr.effective_end_date
-- Do not use effective date against ppe nor prm because we want to pick
-- up interim rows that were end dated.
--and p_effective_date between
-- ppe.effective_start_date and ppe.effective_end_date
--and p_effective_date between
-- prm.effective_start_date and prm.effective_end_date
and pil.per_in_ler_id=ppe.per_in_ler_id
and pil.business_group_id=ppe.business_group_id;
select apr.actl_prem_id, apr.prtl_mo_det_mthd_cd, apr.prtl_mo_det_mthd_rl,
apr.cr_lkbk_crnt_py_only_flag, apr.cr_lkbk_uom,
apr.cr_lkbk_val, ppe.prtt_prem_id, apr.wsh_rl_dy_mo_num,
apr.rndg_cd, apr.rndg_rl, ppe.std_prem_val, apr.prsptv_r_rtsptv_cd,
apr.lwr_lmt_calc_rl, apr.lwr_lmt_val,
apr.upr_lmt_calc_rl, apr.upr_lmt_val
from ben_actl_prem_f apr, ben_prtt_prem_f ppe
where apr.prem_asnmt_cd = 'ENRT'
and apr.cr_lkbk_val is not null -- bug 1213601
and apr.cr_lkbk_val <>0
and ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and ppe.actl_prem_id = apr.actl_prem_id
and apr.business_group_id+0 = p_business_group_id
and (p_interim = 'Y'
or p_effective_date between
ppe.effective_start_date and ppe.effective_end_date)
and p_effective_date between
apr.effective_start_date and apr.effective_end_date;
select prm.val, prm.prtt_prem_by_mo_id, prm.mo_num, prm.yr_num,
prm.object_version_number, prm.effective_start_date
from ben_prtt_prem_by_mo_f prm
where prm.prtt_prem_id = p_prtt_prem_id
and prm.mo_num = p_process_mo_num
and prm.yr_num = p_process_yr_num
and prm.business_group_id+0 = p_business_group_id
and (p_interim = 'Y'
or p_effective_date between
prm.effective_start_date and prm.effective_end_date);
select yrp.start_date
from ben_yr_perd yrp, ben_popl_yr_perd cpy
where yrp.business_group_id+0 = p_business_group_id
and p_effective_date between
yrp.start_date and yrp.end_date
and yrp.yr_perd_id = cpy.yr_perd_id
and ((cpy.pgm_id = p_pgm_id) or
(p_pgm_id is null and cpy.pl_id = p_pl_id));
select opt_id from ben_oipl_f oipl
where oipl.oipl_id = l_oipl_id
and p_effective_date between
oipl.effective_start_date and oipl.effective_end_date;
if p_person_selection_rule_id is not null then
hr_utility.set_location('found a person rule',14);
l_rule_ret := ben_batch_utils.person_selection_rule
(p_person_id => l_results.person_id
,p_business_group_id => p_business_group_id
,p_person_selection_rule_id=> p_person_selection_rule_id
,p_effective_date => p_effective_date
);
if l_rule_ret = 'Y' and p_comp_selection_rule_id is not null then
hr_utility.set_location('found a comp object rule',16);
l_rule_ret:=ben_maintain_designee_elig.comp_selection_rule(
p_person_id => l_results.person_id
,p_business_group_id => p_business_group_id
,p_pgm_id => l_results.pgm_id
,p_pl_id => l_results.pl_id
,p_pl_typ_id => l_results.pl_typ_id
,p_opt_id => l_opt.opt_id
,p_oipl_id => l_results.oipl_id
,p_ler_id => null -- do not call with ler.
,p_comp_selection_rule_id => p_comp_selection_rule_id
,p_effective_date => p_effective_date
);
l_datetrack_mode := hr_api.g_update;
if l_datetrack_mode = hr_api.g_update then
l_prem_val := null;
ben_prtt_prem_by_mo_api.update_prtt_prem_by_mo
(p_prtt_prem_by_mo_id => l_prem_by_mo.prtt_prem_by_mo_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_val => l_prem_val
,p_cr_val => l_val
,p_object_version_number => l_prem_by_mo.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
,p_datetrack_mode => l_datetrack_mode);