[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
31-aug-01 tilak 115.16 1970990, update_prtt_prem_by_mo is
called only when there is a changes
in uom or val
04-aug-01 tilak 115.17 cost_allocation_keyflex_id added in
the condition to call update_prtt_prem_by_mo
13-mar-02 ikasire 115.18 UTF8 changes
14-mar-02 ikasire 115.19 GSCC errors
08-Jun-02 pabodla 115.20 Do not select the contingent worker
assignment when assignment data is
fetched.
30-Dec-02 mmudigon 115.21 NOCOPY
21-feb-03 vsethi 115.22 Bug 2784213. Premium records should be
created with effective date of end of
every month and not process date
30-Jan-04 ikasire 115.23 Bug3379060 Proration doesnot work if
the coverage starts on first on a
Month
12-Jul-04 tjesumic 115.24 NONE code calcualtion is changed
if the start and end mont is not partial , partiam_mo is not
called. bug 3742713
07-Sep-04 tjesumic 115.25 charges created when credit and debit exisit for a month
and credit is no more valid# 3879156
07-Sep-04 tjesumic 115.26 # 3879156
08-Sep-04 tjesumic 115.27 # 3666347 where to end the calucaltion logic changed
14-Sep-04 tjesumic 115.28 # 3666347 the lookback period added to end the calcualtion
14-Sep-04 tjesumic 115.29 # 3666347 where to end the calucaltion validated the premium start date
instead of effective end date. OSB may not have date tracked result but prem
22-Mar-05 tjesumic 115.30 # 4222031 Whne a plan start and end on the same month and wash rule is
defined , the end date is used for premium computation
21-jun-2005 tjesumic 115.31 round of the date to chnged to trunc to find the first date of the month
20-Dec-05 abparekh 115.32 Bug 4892354 : In procedure compute_prem get valid update modes before
updating PRM record
22-Feb-08 rtagarra 115.33 Bug 6840074
20-Oct-08 sallumwa 115.34 Bug 7414822 : Do not write into ben_reporting table when the coverage for
the same is end-dated.
13-Jan-09 pvelvano 115.35 Bug 7676969 : Premium Calculation Summary Report is summing the previous
enrollments amounts for COBRA Participant.
01-Dec-10 velvanop 115.36 Bug 10298963: Premium Calculation Summary Report is summing the previous
enrollments amounts for enrollments ending in the future(ex FONM)
21-Jun-12 velvanop 115.37 Bug 14143354: Commented the fixes of 7676969,10298963. If Coverage is end dated and the Premium Process is run
on or before the Coverage end date, system should pick up the end dated coverage also.
*/
--------------------------------------------------------------------------------
g_package varchar2(80) := 'ben_prem_prtt_monthly';
select asg.assignment_id, asg.organization_id, loc.region_2, asg.location_id
from hr_locations_all loc, per_assignments_f asg
where asg.person_id = p_person_id
and asg.primary_flag = 'Y'
and asg.assignment_type <> 'C'
and loc.location_id(+) = asg.location_id
and asg.business_group_id+0 = p_business_group_id
and p_effective_date between
asg.effective_start_date and asg.effective_end_date
order by 1;
select pbg.cost_allocation_structure
from per_business_groups pbg
where pbg.business_group_id+0 = p_business_group_id;
select segment1, segment2, segment3, segment4, segment5, segment6,
segment7, segment8, segment9, segment10, segment11, segment12,
segment13, segment14, segment15, segment16, segment17, segment18,
segment19, segment20, segment21, segment22, segment23, segment24,
segment25, segment26, segment27, segment28, segment29, segment30
from pay_cost_allocation_keyflex cak, ben_actl_prem_f apr
where apr.actl_prem_id = p_actl_prem_id
and apr.cost_allocation_keyflex_id = cak.cost_allocation_keyflex_id
and apr.business_group_id+0 = p_business_group_id
and p_effective_date between
nvl(cak.start_date_active, p_effective_date)
and nvl(cak.end_date_active, p_effective_date)
and cak.enabled_flag = 'Y'
and p_effective_date between
apr.effective_start_date and apr.effective_end_date;
select cbs.sgmt_num, cbs.sgmt_cstg_mthd_cd, cbs.sgmt_cstg_mthd_rl
from ben_prem_cstg_by_sgmt_f cbs
where cbs.actl_prem_id = p_actl_prem_id
and cbs.business_group_id+0 = p_business_group_id
and p_effective_date between
cbs.effective_start_date and cbs.effective_end_date
order by 1;
select asg.assignment_id, asg.organization_id, loc.region_2, asg.location_id
from hr_locations_all loc, per_assignments_f asg
where asg.person_id = p_person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
and loc.location_id(+) = asg.location_id
and asg.business_group_id+0 = p_business_group_id
and p_effective_date between
asg.effective_start_date and asg.effective_end_date
order by 1;
select full_name from per_people_f
where person_id = p_person_id
and p_effective_date between effective_start_date
and effective_end_date;
select distinct 'Y'
from ben_prtt_prem_by_mo_f prm, ben_prtt_prem_f ppe
where ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and ppe.prtt_prem_id = prm.prtt_prem_id
-- any premiums between esd of result and date we are voided it
and to_date(to_char(prm.mo_num)||'-'||to_char(prm.yr_num), 'mm-yyyy')
between p_effective_start_date 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;
select prm.prtt_prem_by_mo_id, prm.object_version_number,
prm.mnl_adj_flag,prm.uom,prm.val,prm.cr_val,prm.cost_allocation_keyflex_id
, effective_start_date
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 make sure all the time cursor hit the first row
order by prm.effective_start_date ;
select prm.prtt_prem_by_mo_id, prm.object_version_number,
prm.mnl_adj_flag,prm.uom,prm.val,prm.cr_val,prm.cost_allocation_keyflex_id
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;
l_prm_update_mode varchar2(60);
l_update_mode boolean;
l_update_override_mode boolean;
l_update_change_insert_mode boolean;
,p_program_update_date => sysdate
,p_effective_date => l_effective_date_mo);
hr_utility.set_location ('update the premium:'|| l_prm.prtt_prem_by_mo_id, 10) ;
p_update => l_update_mode,
p_update_override => l_update_override_mode,
p_update_change_insert => l_update_change_insert_mode);
if l_update_change_insert_mode
then
l_prm_update_mode := hr_api.g_update_change_insert;
elsif l_update_override_mode
then
l_prm_update_mode := hr_api.g_update_override;
elsif l_update_mode
then
l_prm_update_mode := hr_api.g_update;
l_prm_update_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_val
,p_cr_val => null
,p_alctd_val_flag => 'N'
,p_uom => p_uom
,p_prtt_prem_id => p_prtt_prem_id
,p_cost_allocation_keyflex_id => l_cak
,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_prm.effective_start_date
,p_datetrack_mode => l_prm_update_mode);
p_update => l_update_mode,
p_update_override => l_update_override_mode,
p_update_change_insert => l_update_change_insert_mode);
if l_update_change_insert_mode
then
l_prm_update_mode := hr_api.g_update_change_insert;
elsif l_update_override_mode
then
l_prm_update_mode := hr_api.g_update_override;
l_prm_update_mode := hr_api.g_correction;
l_prm_update_mode := hr_api.g_update;
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_val
,p_alctd_val_flag => 'N'
,p_uom => p_uom
,p_prtt_prem_id => p_prtt_prem_id
,p_cost_allocation_keyflex_id => l_cak
,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_prm.effective_start_date
,p_datetrack_mode => l_prm_update_mode);
,p_comp_selection_rl in number default null
,p_pgm_id in number default null
,p_pl_typ_id in number default null
,p_pl_id in number default null
,p_object_version_number in out nocopy number
,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 pen.person_id, pen.pl_id, pen.oipl_id, pen.effective_start_date,
pen.effective_end_date, pen.enrt_cvg_strt_dt, pen.enrt_cvg_thru_dt,
pen.pgm_id, pen.pl_typ_id, pen.ler_id, pen.prtt_enrt_rslt_id
from ben_prtt_enrt_rslt_f pen
where 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 starts sometime before end of next month
and pen.enrt_cvg_strt_dt <= add_months(p_effective_date,1)
and pen.person_id = p_person_id
-- check criteria user entered on the submit form:
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.business_group_id+0 = p_business_group_id
and p_effective_date between
pen.effective_start_date and pen.effective_end_date
/* Bug 14143354: Commented the fixes of 7676969,10298963. If Coverage is end dated and the Premium Process is run
on or before the Coverage end date, system should pick up the end dated coverage also. Added the below condition*/
and p_effective_date between
pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt ;
select ppe.std_prem_val, ppe.std_prem_uom, apr.prtl_mo_det_mthd_cd,
apr.prtl_mo_det_mthd_rl, apr.wsh_rl_dy_mo_num, apr.actl_prem_id,
ppe.prtt_prem_id, apr.rndg_cd, apr.rndg_rl, apr.prsptv_r_rtsptv_cd,
apr.lwr_lmt_calc_rl, apr.lwr_lmt_val,
apr.upr_lmt_calc_rl, apr.upr_lmt_val,
apr.cr_lkbk_val,apr.cr_lkbk_crnt_py_only_flag,
ppe.effective_start_date
from ben_actl_prem_f apr,
ben_per_in_ler pil,
ben_prtt_prem_f ppe
where apr.prem_asnmt_cd = 'ENRT' -- PROC are dealt with in benprplo.pkb
and apr.business_group_id+0 = p_business_group_id
and ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and p_effective_date between
apr.effective_start_date and apr.effective_end_date
and ppe.actl_prem_id = apr.actl_prem_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+0=ppe.business_group_id+0
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
;
select pen.effective_start_date,
pen.effective_end_date, pen.prtt_enrt_rslt_id
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.effective_start_date < p_effective_start_date;
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;
SELECT typ_cd
FROM ben_ler_f
WHERE ler_id = p_ler_id
AND business_group_id = p_business_group_id;
SELECT 'Y'
FROM ben_prtt_enrt_rslt_f pen
WHERE pen.prtt_enrt_rslt_id = l_pen_id
AND pen.business_group_id = p_business_group_id
AND ((p_ler_typ_cd <> 'SCHEDDO'
and p_effective_date BETWEEN pen.effective_start_date
AND Decode(p_var,'RETRO',pen.effective_end_date,Add_Months(last_day(pen.effective_end_date),1))
AND pen.effective_start_date between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt)
or (p_ler_typ_cd = 'SCHEDDO'
and p_effective_date BETWEEN pen.enrt_cvg_strt_dt
AND Decode(p_var,'RETRO',pen.enrt_cvg_thru_dt,Add_Months(last_day(pen.enrt_cvg_thru_dt),1))
and pen.enrt_cvg_thru_dt >= pen.effective_start_date))
and pen.ler_id = p_ler_id;
if p_comp_selection_rl is not null then
hr_utility.set_location('found a rule',12);
l_rule_ret:=ben_maintain_designee_elig.comp_selection_rule(
p_person_id => p_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 => l_results.ler_id
,p_comp_selection_rule_id => p_comp_selection_rl
,p_effective_date => p_effective_date
);
l_actn := 'Calling ben_person_actions_api.update_person_actions...';
ben_person_actions_api.update_person_actions
(p_person_action_id => p_person_action_id
,p_action_status_cd => 'P'
,p_object_version_number => p_object_version_number
,p_effective_date => p_effective_date
);
ben_person_actions_api.update_person_actions
(p_person_action_id => p_person_action_id
,p_action_status_cd => 'E'
,p_object_version_number => p_object_version_number
,p_effective_date => p_effective_date
);