[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select distinct
yp.start_date,yp.end_date
from ben_prtt_enrt_rslt_f pen,
ben_popl_yr_perd pyp,
ben_yr_perd yp
where pen.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null
and c_effective_date <= pen.effective_end_date
and pyp.pl_id=pen.pl_id
and yp.yr_perd_id=pyp.yr_perd_id
and c_rate_start_or_end_dt
between yp.start_date and yp.end_date;
select distinct
yp.start_date,yp.end_date
from ben_popl_yr_perd pyp,
ben_yr_perd yp
where pyp.pl_id=c_pl_id
and yp.yr_perd_id=pyp.yr_perd_id
and c_rate_start_or_end_dt
between yp.start_date and yp.end_date;
select pen.prtt_enrt_rslt_id,
pen.pl_id,
opt.opt_id,
pen.pgm_id,
pen.ler_id,
pen.pl_typ_id,
pen.person_id,
pen.effective_start_date,
pen.effective_end_date,
pen.uom
from ben_prtt_enrt_rslt_f pen,
ben_oipl_f opt
where pen.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null
and opt.oipl_id(+)=pen.oipl_id
and pen.enrt_cvg_strt_dt between opt.effective_start_date(+)
and opt.effective_end_date(+)
order by pen.effective_start_date desc;
select prv.prtt_rt_val_id,
prv.acty_base_rt_id,
prv.prtt_enrt_rslt_id,
prv.rt_strt_dt,
prv.rt_end_dt,
prv.business_group_id,
prv.mlt_cd,
prv.acty_ref_perd_cd,
prv.rt_val,
prv.cmcd_rt_val,
prv.ann_rt_val,
prv.per_in_ler_id,
prv.element_entry_value_id,
prv.prtt_reimbmt_rqst_id,
prv.object_version_number
from ben_prtt_rt_val prv
where prtt_rt_val_id = c_prtt_rt_val_id;
select 'Y'
from pay_assignment_actions d,
pay_payroll_actions e
where d.assignment_id = c_assignment_id
and d.payroll_action_id = e.payroll_action_id
and e.action_type <> 'X';
select d.assignment_action_id,
g.end_date
from pay_assignment_actions d,
pay_payroll_actions e,
per_time_periods g
where d.assignment_id = c_assignment_id
and g.payroll_id = c_payroll_id
and g.payroll_id = e.payroll_id
and d.payroll_action_id = e.payroll_action_id
and e.action_type in ('R','Q')
and e.time_period_id = g.time_period_id
and e.date_earned between g.start_date and g.end_date
order by g.end_date desc;
select 'x'
from pay_run_results h
where h.element_type_id = c_element_type_id
and h.assignment_action_id = c_assignment_action_id
and h.status in ('P', 'PA')
and h.source_type = 'E';
select 'Y'
from pay_run_results prr,
pay_element_entries_f pee
where pee.element_entry_id = p_element_entry_id
and p_effective_date between pee.effective_start_date
and pee.effective_end_date
and prr.source_id = decode(pee.entry_type,
'A', decode (adjust_ee_source,
'T', pee.target_entry_id,
pee.element_entry_id),
'R', decode (adjust_ee_source,
'T', pee.target_entry_id,
pee.element_entry_id),
pee.element_entry_id)
and prr.entry_type = pee.entry_type
and prr.source_type = 'E'
and prr.status <> 'U'
and not exists
(select 1
from pay_run_results sub_rr
where sub_rr.source_id = prr.run_result_id
and sub_rr.source_type in ('R', 'V')) ;
select 'Y'
from pay_run_results result,
pay_assignment_actions asgt_action,
pay_payroll_actions pay_action,
per_time_periods period
where result.source_id = nvl (p_original_entry_id, p_element_entry_id)
and result.status <> 'U'
and result.source_type = 'E'
and result.assignment_action_id = asgt_action.assignment_action_id
and asgt_action.payroll_action_id = pay_action.payroll_action_id
and pay_action.payroll_id = period.payroll_id
and pay_action.date_earned between period.start_date
and period.end_date
and not exists
(select 1
from pay_run_results rev_result
where rev_result.source_id = result.run_result_id
and rev_result.source_type in ('R', 'V'));
select plr.rule_mode
into l_adjust_ee_source
from pay_legislation_rules plr
, per_business_groups_perf pbgf
where plr.rule_type = 'ADJUSTMENT_EE_SOURCE'
and pbgf.legislation_code = plr.legislation_code
and pbgf.business_group_id = p_business_group_id;
g_ext_inpval_tab.delete;
select eiv.extra_input_value_id,
eiv.input_value_id,
eiv.acty_base_rt_id,
eiv.input_text,
eiv.return_var_name,
eiv.upd_when_ele_ended_cd
from ben_extra_input_values eiv,
pay_input_values_f piv
where eiv.acty_base_rt_id = p_acty_base_rt_id
and eiv.business_group_id = p_business_group_id
and piv.input_value_id = eiv.input_value_id
and p_effective_date between piv.effective_start_date
and piv.effective_end_date
order by piv.display_sequence;
select
aba.*, pil.per_in_ler_id
from per_absence_attendances aba,
ben_per_in_ler pil,
ben_ler_f ler
where aba.person_id = p_person_id
and pil.person_id = p_person_id
and aba.absence_attendance_id = pil.trgr_table_pk_id
and pil.ler_id = ler.ler_id
and ler.typ_cd = 'ABS'
and pil.per_in_ler_stat_cd = 'STRTD'
and p_effective_date between
ler.effective_start_date and ler.effective_end_date;
select upper(replace(name,' ','_'))
from pay_input_values_f
where input_value_id = p_input_value_id1
and p_effective_date between
effective_start_date and effective_end_date;
g_outputs.delete;
select bg.legislation_code
from per_business_groups bg
where bg.business_group_id = p_business_group_id;
select input_value_id,
screen_entry_value
from pay_element_entry_values_f
where element_entry_id = p_element_entry_id
and p_effective_date between effective_start_date
and effective_end_date;
select peev.input_value_id,
peev.screen_entry_value,
piv.UOM
from pay_element_entry_values_f peev, pay_input_values_f piv
where peev.element_entry_id = p_element_entry_id
and p_effective_date between peev.effective_start_date and peev.effective_end_date
and piv.input_value_id = peev.input_value_id
and p_effective_date between piv.effective_start_date and piv.effective_end_date;
select pgm_uom
from ben_pgm_f
where pgm_id = c_pgm_id
and c_effective_date between effective_start_date
and effective_end_date
and business_group_id = c_business_group_id;
select nip_pl_uom
from ben_pl_f
where pl_id = c_pl_id
and c_effective_date between effective_start_date
and effective_end_date
and business_group_id = c_business_group_id;
SELECT LTRIM
( TO_CHAR
( DECODE --round to min acct limits if available.
( fc.minimum_accountable_unit,
NULL, ROUND( nvalue, fc.precision ),
ROUND( nvalue / fc.minimum_accountable_unit ) * fc.minimum_accountable_unit
)
, CONCAT --construct NLS format mask.
( '99999999999999999990', --currencies formatted without NLS 'G'.
DECODE( fc.precision, 0, '', RPAD( 'D', fc.precision+1, '9' ) )
)
), ' ' --left trim white space.
)
INTO changed_val
FROM fnd_currencies fc
WHERE fc.currency_code = p_curr_code;
l_update boolean;
l_update_override boolean;
l_update_change_insert boolean;
p_update => l_update,
p_update_override => l_update_override,
p_update_change_insert => l_update_change_insert);
if l_update then
l_upd_mode := hr_api.g_update;
elsif l_update_override then
l_upd_mode := hr_api.g_update_override;
select qi.assignment_action_id,
qi.element_entry_id,
tp.end_date
from pay_assignment_actions aa,
pay_payroll_actions pa,
pay_quickpay_inclusions qi,
pay_element_links_f el,
pay_element_entries_f ee,
per_time_periods tp
where aa.assignment_id = p_assignment_id
and pa.payroll_action_id = aa.payroll_action_id
and pa.action_type = 'Q'
and pa.time_period_id = tp.time_period_id
and p_effective_date between tp.start_date and tp.end_date
and aa.assignment_action_id = qi.assignment_action_id
and ee.element_entry_id = qi.element_entry_id
and aa.assignment_id = ee.assignment_id
and el.element_link_id = ee.element_link_id
and el.element_type_id = p_element_type_id
and pa.effective_date between el.effective_start_date and
el.effective_end_date
and pa.effective_date between ee.effective_start_date and
ee.effective_end_date;
select end_date
from per_time_periods ptp
where ptp.payroll_id = v_payroll_id
and ptp.end_date between
v_start_date and v_end_date;
g_cache_quick_payrun_object.delete;
hr_utility.set_location('Delete : Cache quick pay',113);
procedure insert_into_quick_pay
(p_person_id number,
p_element_type_id number,
p_assignment_id number,
p_element_entry_id number,
p_effective_date date,
p_start_date date,
p_end_date date,
p_payroll_id number) is
--
cursor c_periods(v_payroll_id in number,
v_start_date in date,
v_end_date in date) is
select end_date
from per_time_periods ptp
where ptp.payroll_id = v_payroll_id
and ptp.end_date between
v_start_date and v_end_date;
hr_utility.set_location('Enter : insert into quick pay',111);
hr_utility.set_location('Insert quick pay'||p_element_entry_id,11);
insert into pay_quickpay_inclusions (element_entry_id, assignment_action_id)
values (p_element_entry_id,g_cache_quick_payrun_object(j).assignment_action_id);
select abr.asmt_to_use_cd
from ben_acty_base_rt_f abr
where abr.acty_base_rt_id = c_acty_base_rt_id
and c_effective_date between
abr.effective_start_date and abr.effective_end_date;
SELECT asg.assignment_id, asg.payroll_id, asg.organization_id,
asg.effective_start_date, asg.effective_end_date
FROM per_all_assignments_f asg, per_assignment_status_types ast
WHERE asg.person_id = c_person_id
AND asg.assignment_type <> 'C'
AND asg.primary_flag = 'Y'
AND asg.assignment_status_type_id = ast.assignment_status_type_id
-- AND ast.per_system_status <> 'TERM_ASSIGN' /* Bug 5655933 */
AND asg.assignment_type =
NVL (c_assignment_type, asg.assignment_type)
AND c_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
ORDER BY asg.assignment_type DESC ; -- Bug 4463077 : Look for employee assignment and then for benefits asg
select payroll_id
from pay_payrolls_f
where business_group_id = c_business_group_id
and period_type = 'Calendar Month'
and c_effective_date
between effective_start_date and effective_end_date;
select nvl(abr.ele_entry_val_cd,'PP') ele_entry_val_cd,
abr.name
from ben_acty_base_rt_f abr
where abr.acty_base_rt_id = p_acty_base_rt_id
and p_rate_date between abr.effective_start_date
and abr.effective_end_date;
select *
from BEN_PRTL_MO_RT_PRTN_VAL_F
where (acty_base_rt_id = p_acty_base_rt_id or
actl_prem_id = p_actl_prem_id or
cvg_amt_calc_mthd_id = p_cvg_amt_calc_mthd_id
)
and c_effective_date
between effective_start_date and effective_end_date
and to_number(to_char(c_start_or_stop_date,'DD'))
between from_dy_mo_num and to_dy_mo_num
and (num_days_month = to_number(to_char(last_day(c_start_or_stop_date),'DD')) or
num_days_month is null)
and c_start_or_stop_cd=
decode(nvl(strt_r_stp_cvg_cd,'ETHR'),'ETHR',c_start_or_stop_cd,strt_r_stp_cvg_cd);
select 'Y'
from BEN_PRTL_MO_RT_PRTN_VAL_F
where (acty_base_rt_id = p_acty_base_rt_id or
actl_prem_id = p_actl_prem_id or
cvg_amt_calc_mthd_id = p_cvg_amt_calc_mthd_id
)
and prorate_by_day_to_mon_flag = 'Y'
and c_effective_date
between effective_start_date and effective_end_date;
select epe.elig_per_elctbl_chc_id
from ben_elig_per_elctbl_chc epe
,ben_oipl_f oipl
,ben_per_in_ler pil
where epe.per_in_ler_id = pil.per_in_ler_id
and nvl(epe.pgm_id, -1) = nvl(p_pgm_id, -1)
and epe.pl_id = p_pl_id
and epe.pl_id = oipl.pl_id(+)
and nvl(epe.oipl_id, -1) = oipl.oipl_id(+)
and nvl(oipl.opt_id, -1) = nvl(p_opt_id, -1)
and pil.person_id = p_person_id
and pil.ler_id = p_ler_id
and pil.per_in_ler_stat_cd = 'STRTD'
and epe.business_group_id = p_business_group_id
and pil.business_group_id = epe.business_group_id
and epe.business_group_id = oipl.business_group_id(+)
and p_effective_date
between oipl.effective_start_date(+) and oipl.effective_end_date(+);
select asg.business_group_id,
asg.payroll_id,
asg.job_id,
asg.grade_id,
asg.position_id,
asg.organization_id,
asg.location_id,
asg.pay_basis_id,
asg.employment_category
from per_all_assignments_f asg
where asg.assignment_id = c_asg_id
and c_eff_date
between asg.effective_start_date and asg.effective_end_date;
select elk.element_link_id,
elk.business_group_id,
elk.element_type_id,
elk.payroll_id,
elk.link_to_all_payrolls_flag,
elk.job_id,
elk.grade_id,
elk.position_id,
elk.organization_id,
elk.location_id,
elk.pay_basis_id,
elk.employment_category,
elk.people_group_id
from pay_element_links_f elk
where elk.element_type_id = c_elt_id
and c_eff_date
between elk.effective_start_date and elk.effective_end_date;
select null
from pay_assignment_link_usages_f alu
where alu.assignment_id = c_asg_id
and alu.element_link_id = c_elk_id
and c_eff_date
between alu.effective_start_date and alu.effective_end_date;
select el.element_link_id
from per_all_assignments_f asg,
pay_element_links_f el
where asg.assignment_id = c_asg_id
and el.business_group_id = asg.business_group_id
and el.element_type_id = c_elt_id
and c_eff_date
between asg.effective_start_date and asg.effective_end_date
and c_eff_date
between el.effective_start_date and el.effective_end_date
and
(
(el.payroll_id is not null and
el.payroll_id = asg.payroll_id)
or (el.link_to_all_payrolls_flag = 'Y' and
asg.payroll_id is not null)
or (el.payroll_id is null and
el.link_to_all_payrolls_flag = 'N')
)
and (el.job_id is null or
el.job_id = asg.job_id)
and (el.grade_id is null or
el.grade_id = asg.grade_id)
and (el.position_id is null or
el.position_id = asg.position_id)
and (el.organization_id is null or
el.organization_id = asg.organization_id)
and (el.location_id is null or
el.location_id = asg.location_id)
and (el.pay_basis_id is null or
el.pay_basis_id = asg.pay_basis_id)
and (el.employment_category is null or
el.employment_category = asg.employment_category)
and (el.people_group_id is null or exists
(select null
from pay_assignment_link_usages_f alu
where alu.assignment_id = asg.assignment_id
and alu.element_link_id = el.element_link_id
and c_eff_date between alu.effective_start_date
and alu.effective_end_date)
)
;
select distinct
elt.element_name,
ele.element_entry_id,
ele.effective_start_date,
ele.effective_end_date,
ele.object_version_number,
ele.creator_id,
ele.creator_type
from pay_element_entries_f ele,
pay_element_links_f elk,
pay_element_types_f elt
where ele.effective_start_date = (select min(ele2.effective_start_date)
from pay_element_entries_f ele2
where ele2.element_entry_id
= ele.element_entry_id)
and ele.effective_start_date > p_effective_date
and ele.assignment_id = p_assignment_id
and nvl(ele.creator_id,-1) <> p_enrt_rslt_id
and ele.entry_type = 'E'
and ele.element_link_id = elk.element_link_id
and ele.effective_start_date between elk.effective_start_date
and elk.effective_end_date
and elk.element_type_id = p_element_type_id
and elt.element_type_id = elk.element_type_id
and elk.effective_start_date between elt.effective_start_date
and elt.effective_end_date
order by ele.effective_start_date desc;
select abr.name rt_name,
prv.rt_strt_dt
from ben_prtt_enrt_rslt_f pen,
ben_prtt_rt_val prv,
ben_acty_base_rt_f abr
where pen.person_id = p_person_id
and pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
and prv.prtt_rt_val_stat_cd is null
and pen.prtt_enrt_rslt_stat_cd is null
and abr.acty_base_rt_id = prv.acty_base_rt_id
and prv.rt_strt_dt between abr.effective_start_date
and abr.effective_end_date
and prv.element_entry_value_id in
(select elv.element_entry_value_id
from pay_element_entry_values_f elv
where elv.element_entry_id = p_element_entry_id);
l_delete_warning boolean;
py_element_entry_api.delete_element_entry
(p_validate =>p_validate
,p_datetrack_delete_mode =>hr_api.g_zap
,p_effective_date =>l_future_ee_rec.effective_end_date
,p_element_entry_id =>l_future_ee_rec.element_entry_id
,p_object_version_number =>l_future_ee_rec.object_version_number
,p_effective_start_date =>l_effective_start_date
,p_effective_end_date =>l_effective_end_date
,p_delete_warning =>l_delete_warning);
SELECT per_in_ler_id
FROM ben_prtt_rt_val
WHERE prtt_rt_val_id = p_prtt_rt_val_id;
SELECT 'X'
FROM ben_prtt_rt_val
WHERE prtt_enrt_rslt_id = p_enrt_rslt_id
AND ended_per_in_ler_id = p_per_in_ler_id
AND ann_rt_val = p_annual_amt;
SELECT ptp.start_date
FROM per_time_periods ptp
WHERE ptp.payroll_id = p_payroll_id
AND ptp.start_date <= p_last_pp_strt_dt
ORDER BY ptp.start_date DESC;
SELECT ptp.end_date
FROM per_time_periods ptp
WHERE ptp.payroll_id = p_payroll_id
AND ptp.end_date <= p_last_pp_end_dt
ORDER BY ptp.start_date DESC;
SELECT 'X'
FROM pay_element_entry_values_f
WHERE element_entry_value_id IN
(
SELECT element_entry_value_id
FROM ben_prtt_rt_val
WHERE prtt_enrt_rslt_id = p_enrt_rslt_id
AND prtt_rt_val_stat_cd IS NULL
)
AND effective_start_date >= l_pp_start_dt
AND effective_end_date <= l_pp_end_dt
AND screen_entry_value <> p_per_pay_amt;
select abr.prtl_mo_det_mthd_cd,
abr.prtl_mo_det_mthd_rl,
abr.wsh_rl_dy_mo_num,
abr.prtl_mo_eff_dt_det_cd,
abr.prtl_mo_eff_dt_det_rl,
abr.rndg_cd,
abr.rndg_rl,
abr.ele_rqd_flag,
abr.one_ann_pymt_cd,
abr.entr_ann_val_flag,
abr.use_calc_acty_bs_rt_flag,
abr.acty_typ_cd,
abr.input_va_calc_rl,
abr.rt_typ_cd,
abr.element_type_id,
abr.input_value_id,
abr.ele_entry_val_cd,
abr.rt_mlt_cd,
abr.parnt_chld_cd,
abr.rcrrg_cd,
abr.name
-- bug 6441505
,abr.element_det_rl
from ben_acty_base_rt_f abr
where abr.acty_base_rt_id=c_acty_base_rt_id
and c_effective_date between abr.effective_start_date
and abr.effective_end_date;
select ele.element_entry_id,
ele.effective_start_date,
ele.effective_end_date,
ele.object_version_number,
elv.element_entry_value_id
from pay_element_entries_f ele,
pay_element_entry_values_f elv
where ele.element_link_id = p_element_link_id
and ele.assignment_id = p_assignment_id
and (p_enrt_rslt_id is null or
p_enrt_rslt_id = ele.creator_id)
and (p_element_entry_id is null or
p_element_entry_id = ele.element_entry_id)
and p_effective_date between ele.effective_start_date
and ele.effective_end_date
and ele.entry_type = 'E'
and elv.element_entry_id = ele.element_entry_id
and elv.effective_start_date between ele.effective_start_date
and ele.effective_end_date
and elv.input_value_id = p_input_value_id;
select ele.element_entry_id,
ele.effective_start_date,
ele.effective_end_date,
ele.object_version_number,
elv.element_entry_value_id
from pay_element_entries_f ele,
pay_element_entry_values_f elv
where p_effective_date between ele.effective_start_date
and ele.effective_end_date
and elv.element_entry_id = ele.element_entry_id
and elv.element_entry_value_id = p_element_entry_value_id
and elv.effective_start_date between ele.effective_start_date
and ele.effective_end_date;
select max(ele.effective_end_date) max_ee_end_date
from pay_element_entries_f ele
where ele.element_entry_id = p_element_entry_id;
select eev.screen_entry_value,
ee.object_version_number,
ee.creator_id,
ee.creator_type,
ee.effective_start_date,
ee.effective_end_date
from pay_element_entry_values_f eev,
pay_element_entries_f ee
where eev.element_entry_id = p_element_entry_id
and eev.input_value_id = p_input_value_id
and p_effective_date between eev.effective_start_date
and eev.effective_end_date
and ee.element_entry_id=eev.element_entry_id
and p_effective_date between ee.effective_start_date
and ee.effective_end_date;
select pet.element_name,
pet.multiple_entries_allowed_flag,
pet.processing_type
from pay_element_types_f pet
where pet.element_type_id = p_element_type_id
and p_effective_date between pet.effective_start_date
and pet.effective_end_date;
select abr.name
from ben_prtt_enrt_rslt_f pen,
ben_acty_base_rt_f abr,
ben_prtt_rt_val prv
where prv.acty_base_rt_id <> p_acty_base_rt_id
and prv.element_entry_value_id = p_element_entry_value_id
and prv.prtt_rt_val_stat_cd is null
and abr.acty_base_rt_id = prv.acty_base_rt_id
and prv.rt_strt_dt between abr.effective_start_date
and abr.effective_end_date
--bug# 3307450 added rt end dt condition to take care of element fix script
and prv.rt_end_dt > p_rt_strt_dt
and pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.person_id = p_person_id;
select abr1.name
from ben_prtt_enrt_rslt_f pen1,
ben_prtt_enrt_rslt_f pen2,
ben_acty_base_rt_f abr1,
ben_prtt_rt_val prv1
where pen2.prtt_enrt_rslt_id = p_enrt_rslt_id
and pen1.prtt_enrt_rslt_id <> pen2.prtt_enrt_rslt_id
and pen1.person_id = pen2.person_id
and pen1.prtt_enrt_rslt_stat_cd is null
and (nvl(pen1.pgm_id,-1) <> nvl(pen2.pgm_id,-1) or
nvl(pen1.pl_id,-1) <> nvl(pen2.pl_id,-1) or
nvl(pen1.oipl_id,-1) <> nvl(pen2.oipl_id,-1))
and pen1.prtt_enrt_rslt_id = prv1.prtt_enrt_rslt_id
and abr1.acty_base_rt_id = prv1.acty_base_rt_id
and prv1.rt_strt_dt between abr1.effective_start_date
and abr1.effective_end_date
and p_rt_strt_dt between prv1.rt_strt_dt
and prv1.rt_end_dt
and prv1.prtt_rt_val_stat_cd is null
and prv1.element_entry_value_id in
(select pev.element_entry_value_id
from pay_element_entry_values_f pev
where pev.element_entry_id = p_element_entry_id);
select ele.element_entry_id,
ele.element_link_id,
ele.effective_start_date,
ele.effective_end_date,
ele.object_version_number,
elv.screen_entry_value
from pay_element_entries_f ele,
pay_element_entry_values_f elv,
pay_element_links_f elk
where ele.creator_id = p_enrt_rslt_id
and ele.creator_type = 'F'
and ele.entry_type = 'E'
and ele.effective_start_date > p_effective_date
and ele.element_entry_id <> p_element_entry_id
and elv.element_entry_id = ele.element_entry_id
and elv.input_value_id = p_input_value_id
and elv.effective_start_date between ele.effective_start_date
and ele.effective_end_date
and ele.assignment_id = p_assignment_id
and ele.element_link_id = elk.element_link_id
and ele.effective_start_date between elk.effective_start_date
and elk.effective_end_date
and elk.element_type_id = p_element_type_id
order by ele.effective_start_date asc;
select start_date,end_date
from per_time_periods
where payroll_id = p_payroll_id
and decode(p_prtl_mo_eff_dt_det_cd,'DTPD',regular_payment_date,
'PPED',end_date,
'DTERND',regular_payment_date,
end_date)
<= p_end_date
and decode(p_prtl_mo_eff_dt_det_cd,'DTPD',regular_payment_date,
'PPED',end_date,
'DTERND',regular_payment_date,
end_date)
>= p_start_date
order by start_date desc;
select start_date,end_date
from per_time_periods
where payroll_id = p_payroll_id
and decode(p_prtl_mo_eff_dt_det_cd,'DTPD',regular_payment_date,
'DTERND',regular_payment_date,
'PPED',end_date,
end_date)
> p_end_date
order by start_date;
select ptp.start_date,ptp.end_date
from per_time_periods ptp
where ptp.payroll_id = p_payroll_id
and ptp.end_date<=add_months(trunc(p_rt_start_date,'YYYY'),
12)-1
order by ptp.end_date desc;
select ptp.start_date,
ptp.end_date
from per_time_periods ptp
where ptp.payroll_id = p_payroll_id
and ptp.start_date > p_rt_start_date
order by ptp.end_date asc;
select pil.per_in_ler_id,
pil.lf_evt_ocrd_dt,
pil.trgr_table_pk_id,
ler.ler_id,
ler.typ_cd
from ben_per_in_ler pil,
ben_ler_f ler
where pil.per_in_ler_id = c_per_in_ler_id
and ler.ler_id = pil.ler_id
and c_effective_date between
ler.effective_start_date and ler.effective_end_date;
select abr2.rt_mlt_cd,
abr2.entr_ann_val_flag
from ben_acty_base_rt_f abr,
ben_acty_base_rt_f abr2
where abr.acty_base_rt_id = c_acty_base_rt_id
and abr2.acty_base_rt_id = abr.parnt_acty_base_rt_id
and abr2.parnt_chld_cd = 'PARNT'
and c_effective_date
between abr.effective_start_date
and abr.effective_end_date
and c_effective_date
between abr2.effective_start_date
and abr2.effective_end_date;
select ptp.end_date
from per_time_periods ptp
where ptp.payroll_id = p_payroll_id
and p_rt_start_date between
ptp.start_date and ptp.end_date ;
select nvl(ptp.regular_payment_date,ptp.end_date) end_date
,ptp.end_date ptp_end_date -- Bug 8985608
from per_time_periods ptp
where ptp.payroll_id = p_payroll_id
and p_rt_start_date between
ptp.start_date and ptp.end_date ;
select ler.name name, ler.ler_id ler_id
from ben_prtt_rt_val prv,
ben_per_in_ler pil,
ben_ler_f ler
where prv.per_in_ler_id = pil.per_in_ler_id
and pil.ler_id = ler.ler_id
and prv.prtt_rt_val_id = p_prtt_rt_val_id
and prv.rt_strt_dt = p_rt_start_date;
SELECT ecr.enrt_rt_id
FROM ben_enrt_rt ecr,
ben_prtt_rt_val prv,
ben_acty_base_rt_f abr
WHERE prv.prtt_rt_val_id = ecr.prtt_rt_val_id
AND prv.prtt_rt_val_stat_cd is NULL
AND abr.acty_base_rt_id = prv.acty_base_rt_id
AND ecr.acty_base_rt_id = prv.acty_base_rt_id
AND prv.rt_strt_dt between abr.effective_start_date
AND abr.effective_end_date
AND prv.rt_end_dt > prv.rt_strt_dt
AND ecr.business_group_id = p_business_group_id
AND ecr.prtt_rt_val_id = p_prtt_rt_val_id
AND ecr.acty_base_rt_id = p_acty_base_rt_id;
SELECT *
FROM per_time_periods ptp
WHERE payroll_id = p_payroll_id
AND p_date BETWEEN ptp.start_date AND ptp.end_date ;
SELECT /*+ LEADING(ee)
INDEX(ee pay_element_entries_f_n51) */
ee.*
FROM pay_element_entries_f ee,
pay_element_types_f et,
pay_element_links_f el,
pay_element_entry_values_f ev
WHERE el.element_link_id = ee.element_link_id
AND el.element_link_id = l_elem_link_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = l_ass_id
and ev.ELEMENT_ENTRY_ID = ee.ELEMENT_ENTRY_ID
and ev.INPUT_VALUE_ID = l_input_value_id
AND l_normal_pp_dt BETWEEN ee.effective_start_date
AND ee.effective_end_date
AND l_normal_pp_dt BETWEEN el.effective_start_date
AND el.effective_end_date
AND l_normal_pp_dt BETWEEN et.effective_start_date
AND et.effective_end_date
AND l_normal_pp_dt BETWEEN ev.effective_start_date
AND ev.effective_end_date
AND et.multiple_entries_allowed_flag = 'N'
AND ee.entry_type = 'E'
and et.PROCESSING_TYPE = 'N';
select prv.* from ben_prtt_rt_val prv,
pay_element_entries_f et,
pay_element_entry_values_f ev
where et.element_type_id = l_element_type_id
and ev.input_value_id = l_input_value_id
and et.ELEMENT_ENTRY_ID = l_ELEMENT_ENTRY_ID
and et.ELEMENT_ENTRY_ID = ev.ELEMENT_ENTRY_ID
and ev.ELEMENT_ENTRY_VALUE_ID = prv.ELEMENT_ENTRY_VALUE_ID
AND l_normal_pp_dt BETWEEN et.effective_start_date
AND et.effective_end_date
AND l_normal_pp_dt BETWEEN ev.effective_start_date
AND ev.effective_end_date
and prv.business_group_id = p_business_group_id;
l_delete_warning BOOLEAN;
l_update_warning BOOLEAN;
l_update_ee boolean;
select pgm_id,pl_id,oipl_id--,pl_typ_id,ler_id -- 6716202
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 rownum = 1 ;
select distinct yp.start_date
from ben_popl_yr_perd pyp, ben_yr_perd yp
where pyp.pl_id = cv_pl_id
and pyp.yr_perd_id = yp.yr_perd_id
and pyp.business_group_id = p_business_group_id
and cv_effective_date BETWEEN yp.start_date AND yp.end_date
and yp.business_group_id = p_business_group_id ;
select
max(prv.rt_strt_dt) max_strt_dt
from ben_prtt_enrt_rslt_f pen,
ben_prtt_rt_val prv
where pen.person_id = p_person_id
and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
and prv.acty_base_rt_id = p_acty_base_rt_id
and prv.prtt_rt_val_id <> p_prtt_rt_val_id
and (p_mlt_cd <> 'SAREC' OR (p_mlt_cd = 'SAREC' and prv.rt_strt_dt >= l_yp_start_date))
and prv.rt_end_dt < p_rt_start_date
and prv.prtt_rt_val_stat_cd is null ;
select count(prv.prtt_enrt_rslt_id)
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and prv.acty_base_rt_id <> p_acty_base_rt_id
and prv.rt_end_dt > c_effective_date
and prv.prtt_rt_val_stat_cd is null
and prv.element_entry_value_id in
(select pev.element_entry_value_id
from pay_element_entry_values_f pev
where pev.element_entry_id = c_element_entry_id);
SELECT ptp.start_date
FROM per_time_periods ptp
,per_all_assignments_f asg
WHERE ptp.regular_payment_date >= p_effective_start_date
and ptp.payroll_id = asg.payroll_id
AND ptp.start_date <= p_effective_start_date
AND p_business_group_id = asg.business_group_id
AND asg.primary_flag = 'Y'
AND asg.person_id = p_person_id
AND p_effective_start_date
between asg.effective_start_date
and asg.effective_end_date
order by ptp.regular_payment_date;
select pev.element_entry_value_id
from ben_prtt_enrt_rslt_f pen,
ben_prtt_rt_val prv,
pay_element_entry_values_f pev
where pen.person_id = p_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and prv.mlt_cd = 'SAREC'
and prv.acty_base_rt_id = p_acty_base_rt_id
and prv.prtt_rt_val_id <> p_prtt_rt_val_id
and prv.rt_strt_dt = l_max_strt_dt
and prv.rt_end_dt < p_rt_start_date
and prv.prtt_rt_val_stat_cd is null
and prv.ann_rt_val = p_ann_rt_val
and pev.element_entry_value_id = prv.element_entry_value_id
and p_rt_start_date between pev.effective_start_date
and pev.effective_end_date ;
select pev.element_entry_value_id
from ben_prtt_enrt_rslt_f pen,
ben_prtt_rt_val prv,
pay_element_entry_values_f pev
where pen.person_id = p_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
and prv.acty_base_rt_id = p_acty_base_rt_id
and prv.prtt_rt_val_id <> p_prtt_rt_val_id
and prv.rt_strt_dt = l_max_strt_dt
and prv.rt_end_dt < p_rt_start_date
and prv.prtt_rt_val_stat_cd is null
and prv.ann_rt_val = p_ann_rt_val
and prv.rt_val = p_rt_val
and prv.cmcd_rt_val = p_cmcd_rt_val
and pev.element_entry_value_id = prv.element_entry_value_id
and p_rt_start_date between pev.effective_start_date
and pev.effective_end_date ;
select pee.element_entry_id,
pee.effective_end_date,
pee.object_version_number,
pee.assignment_id,
pee.element_type_id,
pev.input_value_id,
abr.ele_entry_val_cd -- Bug 5575402
from pay_element_entries_f pee,
pay_element_entry_values_f pev,
ben_acty_base_rt_f abr
where pev.element_entry_value_id = l_ee_id
and pee.element_entry_id = pev.element_entry_id
and pev.effective_start_date between pee.effective_start_date
and pee.effective_end_date
and abr.acty_base_rt_id = p_acty_base_rt_id
and pev.effective_start_date between abr.effective_start_date
and abr.effective_end_date
and abr.element_type_id = pee.element_type_id
and abr.input_value_id = pev.input_value_id
order by pee.effective_end_date desc;
select pay.period_type
from per_all_assignments_f asg,
pay_payrolls_f pay,
per_all_assignments_f asg2,
pay_payrolls_f pay2
where asg.person_id = cp_person_id
and asg.assignment_type <> 'C'
and asg.business_group_id = cp_business_group_id
and asg.primary_flag = 'Y'
and cp_effective_date between
asg.effective_start_date and asg.effective_end_date
and pay.payroll_id=asg.payroll_id
and pay.business_group_id = asg.business_group_id
and cp_effective_date between
pay.effective_start_date and pay.effective_end_date
and asg2.person_id = cp_person_id
and asg2.assignment_type <> 'C'
and asg2.business_group_id = cp_business_group_id
and asg2.primary_flag = 'Y'
and cp_orig_effective_date between
asg2.effective_start_date and asg2.effective_end_date
and pay2.payroll_id=asg2.payroll_id
and pay2.business_group_id = asg2.business_group_id
and cp_orig_effective_date between
pay2.effective_start_date and pay2.effective_end_date
and pay2.period_type<>pay.period_type
and asg.assignment_type = asg2.assignment_type ;
select screen_entry_value,element_entry_value_id
from pay_element_entry_values_f pev
where pev.input_value_id = p_input_value_id
and pev.element_entry_value_id = p_element_entry_value_id
and p_rt_start_date between pev.effective_start_date
and pev.effective_end_date ;
select rt_val
from ben_prtt_rt_val prv
where prv.prtt_rt_val_id = p_prtt_rt_val_id
and p_rt_start_date between prv.rt_strt_dt
and prv.rt_end_dt;
select element_entry_id
from pay_element_entry_values_f pev
where pev.input_value_id = p_input_value_id
and l_max_strt_dt between pev.effective_start_date
and pev.effective_end_date ;
ben_prtt_rt_val_api.update_prtt_rt_val
(p_validate => p_validate
,p_person_id => p_person_id
,p_prtt_rt_val_id => p_prtt_rt_val_id
,p_business_group_id => p_business_group_id
,p_element_entry_value_id => l_ee_id
,p_input_value_id => p_input_value_id
,p_object_version_number => p_prv_object_version_number
,p_effective_date => p_rt_start_date
);
py_element_entry_api.delete_element_entry
(p_validate =>p_validate
,p_datetrack_delete_mode =>'FUTURE_CHANGE'
,p_effective_date =>l_fsa_ee_effective_end_date
,p_element_entry_id =>l_fsa_element_entry_id
,p_object_version_number =>l_fsa_ovn
,p_effective_start_date =>l_effective_start_date
,p_effective_end_date =>l_effective_end_date
,p_delete_warning =>l_delete_warning);
ben_prtt_rt_val_api.update_prtt_rt_val
(p_validate => p_validate
,p_person_id => p_person_id
,p_prtt_rt_val_id => p_prtt_rt_val_id
,p_business_group_id => p_business_group_id
,p_element_entry_value_id => l_ee_id
,p_input_value_id => p_input_value_id
,p_object_version_number => p_prv_object_version_number
,p_effective_date => p_rt_start_date
);
py_element_entry_api.delete_element_entry
(p_validate =>p_validate
,p_datetrack_delete_mode =>'FUTURE_CHANGE'
,p_effective_date =>l_fsa_ee_effective_end_date
,p_element_entry_id =>l_fsa_element_entry_id
,p_object_version_number =>l_fsa_ovn
,p_effective_start_date =>l_effective_start_date
,p_effective_end_date =>l_effective_end_date
,p_delete_warning =>l_delete_warning);
py_element_entry_api.delete_element_entry
(p_validate =>p_validate
,p_datetrack_delete_mode =>'FUTURE_CHANGE'
,p_effective_date =>l_ee_effective_end_date
,p_element_entry_id =>l_element_entry_id
,p_object_version_number =>l_object_version_number
,p_effective_start_date =>l_effective_start_date
,p_effective_end_date =>l_effective_end_date
,p_delete_warning =>l_delete_warning);
l_update_ee:=true;
hr_utility.set_location('inserting mode ',90);
l_update_ee:=false;
if l_update_ee then
open get_current_value(l_element_entry_id,l_input_value_id,
l_zero_pp_date);
py_element_entry_api.update_element_entry
(p_validate =>p_validate
,p_override_user_ent_chk =>'Y'
,p_datetrack_update_mode =>l_upd_mode
,p_effective_date =>l_zero_pp_date
,p_business_group_id =>p_business_group_id
,p_element_entry_id =>l_element_entry_id
,p_object_version_number =>l_object_version_number
,p_creator_type =>'F'
,p_creator_id =>p_enrt_rslt_id
,p_input_value_id1 =>g_ext_inpval_tab(1).input_value_id
,p_entry_value1 =>g_ext_inpval_tab(1).return_value
,p_input_value_id2 =>g_ext_inpval_tab(2).input_value_id
,p_entry_value2 =>g_ext_inpval_tab(2).return_value
,p_input_value_id3 =>g_ext_inpval_tab(3).input_value_id
,p_entry_value3 =>g_ext_inpval_tab(3).return_value
,p_input_value_id4 =>g_ext_inpval_tab(4).input_value_id
,p_entry_value4 =>g_ext_inpval_tab(4).return_value
,p_input_value_id5 =>g_ext_inpval_tab(5).input_value_id
,p_entry_value5 =>g_ext_inpval_tab(5).return_value
,p_input_value_id6 =>g_ext_inpval_tab(6).input_value_id
,p_entry_value6 =>g_ext_inpval_tab(6).return_value
,p_input_value_id7 =>g_ext_inpval_tab(7).input_value_id
,p_entry_value7 =>g_ext_inpval_tab(7).return_value
,p_input_value_id8 =>g_ext_inpval_tab(8).input_value_id
,p_entry_value8 =>g_ext_inpval_tab(8).return_value
,p_input_value_id9 =>g_ext_inpval_tab(9).input_value_id
,p_entry_value9 =>g_ext_inpval_tab(9).return_value
,p_input_value_id10 =>g_ext_inpval_tab(10).input_value_id
,p_entry_value10 =>g_ext_inpval_tab(10).return_value
,p_input_value_id11 =>g_ext_inpval_tab(11).input_value_id
,p_entry_value11 =>g_ext_inpval_tab(11).return_value
,p_input_value_id12 =>g_ext_inpval_tab(12).input_value_id
,p_entry_value12 =>g_ext_inpval_tab(12).return_value
,p_input_value_id13 =>g_ext_inpval_tab(13).input_value_id
,p_entry_value13 =>g_ext_inpval_tab(13).return_value
,p_input_value_id14 =>g_ext_inpval_tab(14).input_value_id
,p_entry_value14 =>g_ext_inpval_tab(14).return_value
,p_input_value_id15 =>l_input_value_id
,p_entry_value15 =>0
,p_effective_start_date =>l_effective_start_date
,p_effective_end_date =>l_effective_end_date
,p_update_warning =>l_update_warning
);
py_element_entry_api.update_element_entry
(p_validate =>p_validate
,p_datetrack_update_mode =>l_upd_mode
,p_effective_date =>l_zero_pp_date
,p_business_group_id =>p_business_group_id
,p_element_entry_id =>l_element_entry_id
,p_object_version_number =>l_object_version_number
,p_override_user_ent_chk => 'Y'
,p_creator_type =>'F'
,p_creator_id =>p_enrt_rslt_id
,p_input_value_id1 =>g_ext_inpval_tab(1).input_value_id
,p_entry_value1 =>g_ext_inpval_tab(1).return_value
,p_input_value_id2 =>g_ext_inpval_tab(2).input_value_id
,p_entry_value2 =>g_ext_inpval_tab(2).return_value
,p_input_value_id3 =>g_ext_inpval_tab(3).input_value_id
,p_entry_value3 =>g_ext_inpval_tab(3).return_value
,p_input_value_id4 =>g_ext_inpval_tab(4).input_value_id
,p_entry_value4 =>g_ext_inpval_tab(4).return_value
,p_input_value_id5 =>g_ext_inpval_tab(5).input_value_id
,p_entry_value5 =>g_ext_inpval_tab(5).return_value
,p_input_value_id6 =>g_ext_inpval_tab(6).input_value_id
,p_entry_value6 =>g_ext_inpval_tab(6).return_value
,p_input_value_id7 =>g_ext_inpval_tab(7).input_value_id
,p_entry_value7 =>g_ext_inpval_tab(7).return_value
,p_input_value_id8 =>g_ext_inpval_tab(8).input_value_id
,p_entry_value8 =>g_ext_inpval_tab(8).return_value
,p_input_value_id9 =>g_ext_inpval_tab(9).input_value_id
,p_entry_value9 =>g_ext_inpval_tab(9).return_value
,p_input_value_id10 =>g_ext_inpval_tab(10).input_value_id
,p_entry_value10 =>g_ext_inpval_tab(10).return_value
,p_input_value_id11 =>g_ext_inpval_tab(11).input_value_id
,p_entry_value11 =>g_ext_inpval_tab(11).return_value
,p_input_value_id12 =>g_ext_inpval_tab(12).input_value_id
,p_entry_value12 =>g_ext_inpval_tab(12).return_value
,p_input_value_id13 =>g_ext_inpval_tab(13).input_value_id
,p_entry_value13 =>g_ext_inpval_tab(13).return_value
,p_input_value_id14 =>g_ext_inpval_tab(14).input_value_id
,p_entry_value14 =>g_ext_inpval_tab(14).return_value
,p_effective_start_date =>l_effective_start_date
,p_effective_end_date =>l_effective_end_date
,p_update_warning =>l_update_warning
);
l_update_ee:=true;
py_element_entry_api.update_element_entry
(p_validate => p_validate
,p_datetrack_update_mode => hr_api.g_correction
,p_effective_date => l_zero_pp_date
,p_business_group_id => p_business_group_id
,p_element_entry_id => l_element_entry_id
,p_override_user_ent_chk =>'Y'
,p_object_version_number => l_object_version_number
,p_creator_type => 'F'
,p_creator_id => p_enrt_rslt_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_update_warning => l_update_warning
);
if l_update_ee then
--
open get_current_value(l_element_entry_id,l_input_value_id,
l_special_pp_date);
py_element_entry_api.update_element_entry
(p_validate =>p_validate
,p_datetrack_update_mode =>l_upd_mode
,p_effective_date =>l_special_pp_date
,p_business_group_id =>p_business_group_id
,p_element_entry_id =>l_element_entry_id
,p_override_user_ent_chk =>'Y'
,p_object_version_number =>l_object_version_number
,p_creator_type =>'F'
,p_creator_id =>p_enrt_rslt_id
,p_input_value_id1 =>g_ext_inpval_tab(1).input_value_id
,p_entry_value1 =>g_ext_inpval_tab(1).return_value
,p_input_value_id2 =>g_ext_inpval_tab(2).input_value_id
,p_entry_value2 =>g_ext_inpval_tab(2).return_value
,p_input_value_id3 =>g_ext_inpval_tab(3).input_value_id
,p_entry_value3 =>g_ext_inpval_tab(3).return_value
,p_input_value_id4 =>g_ext_inpval_tab(4).input_value_id
,p_entry_value4 =>g_ext_inpval_tab(4).return_value
,p_input_value_id5 =>g_ext_inpval_tab(5).input_value_id
,p_entry_value5 =>g_ext_inpval_tab(5).return_value
,p_input_value_id6 =>g_ext_inpval_tab(6).input_value_id
,p_entry_value6 =>g_ext_inpval_tab(6).return_value
,p_input_value_id7 =>g_ext_inpval_tab(7).input_value_id
,p_entry_value7 =>g_ext_inpval_tab(7).return_value
,p_input_value_id8 =>g_ext_inpval_tab(8).input_value_id
,p_entry_value8 =>g_ext_inpval_tab(8).return_value
,p_input_value_id9 =>g_ext_inpval_tab(9).input_value_id
,p_entry_value9 =>g_ext_inpval_tab(9).return_value
,p_input_value_id10 =>g_ext_inpval_tab(10).input_value_id
,p_entry_value10 =>g_ext_inpval_tab(10).return_value
,p_input_value_id11 =>g_ext_inpval_tab(11).input_value_id
,p_entry_value11 =>g_ext_inpval_tab(11).return_value
,p_input_value_id12 =>g_ext_inpval_tab(12).input_value_id
,p_entry_value12 =>g_ext_inpval_tab(12).return_value
,p_input_value_id13 =>g_ext_inpval_tab(13).input_value_id
,p_entry_value13 =>g_ext_inpval_tab(13).return_value
,p_input_value_id14 =>g_ext_inpval_tab(14).input_value_id
,p_entry_value14 =>g_ext_inpval_tab(14).return_value
,p_input_value_id15 =>l_input_value_id
,p_entry_value15 =>l_special_amt
,p_effective_start_date =>l_effective_start_date
,p_effective_end_date =>l_effective_end_date
,p_update_warning =>l_update_warning
);
py_element_entry_api.update_element_entry
(p_validate =>p_validate
,p_datetrack_update_mode =>l_upd_mode
,p_effective_date =>l_special_pp_date
,p_business_group_id =>p_business_group_id
,p_element_entry_id =>l_element_entry_id
,p_override_user_ent_chk =>'Y'
,p_object_version_number =>l_object_version_number
,p_creator_type =>'F'
,p_creator_id =>p_enrt_rslt_id
,p_input_value_id1 =>g_ext_inpval_tab(1).input_value_id
,p_entry_value1 =>g_ext_inpval_tab(1).return_value
,p_input_value_id2 =>g_ext_inpval_tab(2).input_value_id
,p_entry_value2 =>g_ext_inpval_tab(2).return_value
,p_input_value_id3 =>g_ext_inpval_tab(3).input_value_id
,p_entry_value3 =>g_ext_inpval_tab(3).return_value
,p_input_value_id4 =>g_ext_inpval_tab(4).input_value_id
,p_entry_value4 =>g_ext_inpval_tab(4).return_value
,p_input_value_id5 =>g_ext_inpval_tab(5).input_value_id
,p_entry_value5 =>g_ext_inpval_tab(5).return_value
,p_input_value_id6 =>g_ext_inpval_tab(6).input_value_id
,p_entry_value6 =>g_ext_inpval_tab(6).return_value
,p_input_value_id7 =>g_ext_inpval_tab(7).input_value_id
,p_entry_value7 =>g_ext_inpval_tab(7).return_value
,p_input_value_id8 =>g_ext_inpval_tab(8).input_value_id
,p_entry_value8 =>g_ext_inpval_tab(8).return_value
,p_input_value_id9 =>g_ext_inpval_tab(9).input_value_id
,p_entry_value9 =>g_ext_inpval_tab(9).return_value
,p_input_value_id10 =>g_ext_inpval_tab(10).input_value_id
,p_entry_value10 =>g_ext_inpval_tab(10).return_value
,p_input_value_id11 =>g_ext_inpval_tab(11).input_value_id
,p_entry_value11 =>g_ext_inpval_tab(11).return_value
,p_input_value_id12 =>g_ext_inpval_tab(12).input_value_id
,p_entry_value12 =>g_ext_inpval_tab(12).return_value
,p_input_value_id13 =>g_ext_inpval_tab(13).input_value_id
,p_entry_value13 =>g_ext_inpval_tab(13).return_value
,p_input_value_id14 =>g_ext_inpval_tab(14).input_value_id
,p_entry_value14 =>g_ext_inpval_tab(14).return_value
,p_effective_start_date =>l_effective_start_date
,p_effective_end_date =>l_effective_end_date
,p_update_warning =>l_update_warning
);
l_update_ee:=true;
py_element_entry_api.update_element_entry
(p_validate => p_validate
,p_datetrack_update_mode => hr_api.g_correction
,p_effective_date => l_special_pp_date
,p_business_group_id => p_business_group_id
,p_element_entry_id => l_element_entry_id
,p_override_user_ent_chk =>'Y'
,p_object_version_number => l_object_version_number
,p_creator_type => 'F'
,p_creator_id => p_enrt_rslt_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_update_warning => l_update_warning
);
if l_update_ee then
l_skip := true; -- Bug 13526198
py_element_entry_api.update_element_entry
(p_validate =>p_validate
,p_datetrack_update_mode =>l_upd_mode
,p_effective_date =>l_normal_pp_date
,p_business_group_id =>p_business_group_id
,p_element_entry_id =>l_element_entry_id
,p_override_user_ent_chk =>'Y'
,p_object_version_number =>l_object_version_number
,p_creator_type =>'F'
,p_creator_id =>p_enrt_rslt_id
,p_input_value_id1 =>g_ext_inpval_tab(1).input_value_id
,p_entry_value1 =>g_ext_inpval_tab(1).return_value
,p_input_value_id2 =>g_ext_inpval_tab(2).input_value_id
,p_entry_value2 =>g_ext_inpval_tab(2).return_value
,p_input_value_id3 =>g_ext_inpval_tab(3).input_value_id
,p_entry_value3 =>g_ext_inpval_tab(3).return_value
,p_input_value_id4 =>g_ext_inpval_tab(4).input_value_id
,p_entry_value4 =>g_ext_inpval_tab(4).return_value
,p_input_value_id5 =>g_ext_inpval_tab(5).input_value_id
,p_entry_value5 =>g_ext_inpval_tab(5).return_value
,p_input_value_id6 =>g_ext_inpval_tab(6).input_value_id
,p_entry_value6 =>g_ext_inpval_tab(6).return_value
,p_input_value_id7 =>g_ext_inpval_tab(7).input_value_id
,p_entry_value7 =>g_ext_inpval_tab(7).return_value
,p_input_value_id8 =>g_ext_inpval_tab(8).input_value_id
,p_entry_value8 =>g_ext_inpval_tab(8).return_value
,p_input_value_id9 =>g_ext_inpval_tab(9).input_value_id
,p_entry_value9 =>g_ext_inpval_tab(9).return_value
,p_input_value_id10 =>g_ext_inpval_tab(10).input_value_id
,p_entry_value10 =>g_ext_inpval_tab(10).return_value
,p_input_value_id11 =>g_ext_inpval_tab(11).input_value_id
,p_entry_value11 =>g_ext_inpval_tab(11).return_value
,p_input_value_id12 =>g_ext_inpval_tab(12).input_value_id
,p_entry_value12 =>g_ext_inpval_tab(12).return_value
,p_input_value_id13 =>g_ext_inpval_tab(13).input_value_id
,p_entry_value13 =>g_ext_inpval_tab(13).return_value
,p_input_value_id14 =>g_ext_inpval_tab(14).input_value_id
,p_entry_value14 =>g_ext_inpval_tab(14).return_value
,p_input_value_id15 =>l_input_value_id
,p_entry_value15 =>l_per_pay_amt
,p_effective_start_date =>l_effective_start_date
,p_effective_end_date =>l_effective_end_date
,p_update_warning =>l_update_warning
);
py_element_entry_api.update_element_entry
(p_validate =>p_validate
,p_datetrack_update_mode =>l_upd_mode
,p_effective_date =>l_normal_pp_date
,p_business_group_id =>p_business_group_id
,p_element_entry_id =>l_element_entry_id
,p_override_user_ent_chk =>'Y'
,p_object_version_number =>l_object_version_number
,p_creator_type =>'F'
,p_creator_id =>p_enrt_rslt_id
,p_input_value_id1 =>g_ext_inpval_tab(1).input_value_id
,p_entry_value1 =>g_ext_inpval_tab(1).return_value
,p_input_value_id2 =>g_ext_inpval_tab(2).input_value_id
,p_entry_value2 =>g_ext_inpval_tab(2).return_value
,p_input_value_id3 =>g_ext_inpval_tab(3).input_value_id
,p_entry_value3 =>g_ext_inpval_tab(3).return_value
,p_input_value_id4 =>g_ext_inpval_tab(4).input_value_id
,p_entry_value4 =>g_ext_inpval_tab(4).return_value
,p_input_value_id5 =>g_ext_inpval_tab(5).input_value_id
,p_entry_value5 =>g_ext_inpval_tab(5).return_value
,p_input_value_id6 =>g_ext_inpval_tab(6).input_value_id
,p_entry_value6 =>g_ext_inpval_tab(6).return_value
,p_input_value_id7 =>g_ext_inpval_tab(7).input_value_id
,p_entry_value7 =>g_ext_inpval_tab(7).return_value
,p_input_value_id8 =>g_ext_inpval_tab(8).input_value_id
,p_entry_value8 =>g_ext_inpval_tab(8).return_value
,p_input_value_id9 =>g_ext_inpval_tab(9).input_value_id
,p_entry_value9 =>g_ext_inpval_tab(9).return_value
,p_input_value_id10 =>g_ext_inpval_tab(10).input_value_id
,p_entry_value10 =>g_ext_inpval_tab(10).return_value
,p_input_value_id11 =>g_ext_inpval_tab(11).input_value_id
,p_entry_value11 =>g_ext_inpval_tab(11).return_value
,p_input_value_id12 =>g_ext_inpval_tab(12).input_value_id
,p_entry_value12 =>g_ext_inpval_tab(12).return_value
,p_input_value_id13 =>g_ext_inpval_tab(13).input_value_id
,p_entry_value13 =>g_ext_inpval_tab(13).return_value
,p_input_value_id14 =>g_ext_inpval_tab(14).input_value_id
,p_entry_value14 =>g_ext_inpval_tab(14).return_value
,p_effective_start_date =>l_effective_start_date
,p_effective_end_date =>l_effective_end_date
,p_update_warning =>l_update_warning
);
delete the old element entry, set the element_entry_value_id on rate record to NULL and create a
new element entry for the payroll period. Added code in the else part c_entry_exists%FOUND */
open c_entry_exists(l_normal_pp_date, l_element_link_id, l_assignment_id,p_input_value_id);
-- Tell next steps to update instead of create
--
l_update_ee:=true;
py_element_entry_api.update_element_entry
(p_validate =>p_validate
,p_datetrack_update_mode =>hr_api.g_correction
,p_effective_date =>l_normal_pp_date
,p_business_group_id =>p_business_group_id
,p_element_entry_id =>l_element_entry_id
,p_override_user_ent_chk =>'Y'
,p_object_version_number =>l_object_version_number
,p_creator_type =>'F'
,p_creator_id => p_enrt_rslt_id
,p_effective_start_date =>l_effective_start_date
,p_effective_end_date =>l_effective_end_date
,p_update_warning =>l_update_warning
);
ben_prtt_rt_val_api.update_prtt_rt_val(
p_validate => p_validate
,p_prtt_rt_val_id => j.prtt_rt_val_id
,p_element_entry_value_id => NULL
,p_object_version_number => j.object_version_number
,p_effective_date=> j.RT_STRT_DT
,p_business_group_id => p_business_group_id);
py_element_entry_api.delete_element_entry
(p_validate => p_validate
,p_datetrack_delete_mode => hr_api.g_zap
,p_effective_date => l_entry_exists.effective_start_date
,p_element_entry_id => l_entry_exists.element_entry_id
,p_object_version_number => l_entry_exists.object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_delete_warning => l_delete_warning
);
(p_action => hr_api.g_delete
,p_old_amt => l_per_pay_amt
,p_input_value_id => l_input_value_id
,p_element_entry_id => l_element_entry_id
,p_person_id => p_person_id
,p_business_group_id => p_business_group_id
,p_effective_date => l_normal_pp_end_date
);*/
py_element_entry_api.update_element_entry
(p_validate =>p_validate
,p_datetrack_update_mode =>hr_api.g_correction
,p_effective_date =>l_normal_pp_date
,p_business_group_id =>p_business_group_id
,p_element_entry_id =>l_element_entry_id
,p_override_user_ent_chk =>'Y'
,p_object_version_number =>l_object_version_number
,p_creator_type =>'F'
,p_creator_id => p_enrt_rslt_id
,p_effective_start_date =>l_effective_start_date
,p_effective_end_date =>l_effective_end_date
,p_update_warning =>l_update_warning
);
py_element_entry_api.update_element_entry
(p_validate =>p_validate
,p_datetrack_update_mode =>hr_api.g_correction
,p_effective_date =>l_future_ee_rec.effective_start_date
,p_business_group_id =>p_business_group_id
,p_element_entry_id =>l_future_ee_rec.element_entry_id
,p_override_user_ent_chk =>'Y'
,p_object_version_number =>l_future_ee_rec.object_version_number
,p_input_value_id1 =>l_input_value_id
,p_entry_value1 =>l_per_pay_amt
,p_effective_start_date =>l_effective_start_date
,p_effective_end_date =>l_effective_end_date
,p_update_warning =>l_update_warning
);
py_element_entry_api.update_element_entry
(p_validate =>p_validate
,p_datetrack_update_mode =>hr_api.g_correction
,p_effective_date =>l_effective_date
,p_business_group_id =>p_business_group_id
,p_element_entry_id =>l_element_entry_id
,p_override_user_ent_chk =>'Y'
,p_object_version_number =>l_object_version_number
,p_creator_type =>'F'
,p_creator_id =>p_enrt_rslt_id
,p_effective_start_date =>l_effective_start_date
,p_effective_end_date =>l_effective_end_date
,p_update_warning =>l_update_warning
);
py_element_entry_api.delete_element_entry
(p_validate => p_validate
,p_datetrack_delete_mode => hr_api.g_delete
,p_effective_date => l_normal_pp_end_date
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_delete_warning => l_delete_warning
);
(p_action => hr_api.g_delete
,p_old_amt => l_per_pay_amt
,p_input_value_id => l_input_value_id
,p_element_entry_id => l_element_entry_id
,p_person_id => p_person_id
,p_business_group_id => p_business_group_id
,p_effective_date => l_normal_pp_end_date
);
insert_into_quick_pay
(p_person_id => p_person_id,
p_element_type_id => l_element_type_id,
p_assignment_id => l_assignment_id,
p_element_entry_id => l_element_entry_id,
p_effective_date => l_rt_strt_dt,
p_start_date => l_rt_strt_dt,
p_end_date => g_max_end_date,
p_payroll_id => l_payroll_id);
hr_utility.set_location('update prtt_rt_val',180);
ben_prtt_rt_val_api.update_prtt_rt_val
(p_validate => p_validate
,p_business_group_id => l_prv_rec.business_group_id
,p_prtt_rt_val_id => l_prv_rec.prtt_rt_val_id
,p_element_entry_value_id => l_entry_value_id
,p_object_version_number => p_prv_object_version_number
,p_effective_date => l_rt_strt_dt
);
select abr.ele_rqd_flag,
abr.input_va_calc_rl,
abr.element_type_id,
abr.input_value_id,
abr.prtl_mo_det_mthd_cd
from ben_acty_base_rt_f abr
where abr.acty_base_rt_id=p_acty_base_rt_id
and p_effective_date between
abr.effective_start_date and abr.effective_end_date;
select pee.element_entry_id,
asg.assignment_id,
asg.payroll_id,
pet.element_name,
pet.multiple_entries_allowed_flag,
pel.element_type_id,
pev.input_value_id
from per_all_assignments_f asg,
pay_element_links_f pel,
pay_element_types_f pet,
pay_element_entries_f pee,
pay_element_entry_values_f pev
where asg.person_id = p_person_id
and asg.assignment_id = pee.assignment_id
and pee.effective_start_date between asg.effective_start_date
and asg.effective_end_date
and pev.element_entry_value_id = p_element_entry_value_id
and pee.element_entry_id = pev.element_entry_id
and pee.creator_id = p_prtt_enrt_rslt_id
and pee.creator_type = 'F'
and pee.entry_type = 'E'
and pev.effective_start_date between pee.effective_start_date
and pee.effective_end_date
and pel.element_link_id = pee.element_link_id
and pee.effective_start_date between pel.effective_start_date
and pel.effective_end_date
and pet.element_type_id = pel.element_type_id
and pel.effective_start_date between pet.effective_start_date
and pet.effective_end_date;
select ele.element_entry_id,
ele.element_link_id,
ele.effective_start_date,
ele.effective_end_date,
pev.screen_entry_value,
ele.object_version_number
from pay_element_entries_f ele,
pay_element_entry_values_f pev,
pay_element_links_f elk
where ele.creator_id = p_prtt_enrt_rslt_id
and ele.creator_type = 'F'
and ele.entry_type = 'E'
and ele.assignment_id = p_assignment_id
and ele.element_link_id = elk.element_link_id
and ele.effective_start_date between elk.effective_start_date
and elk.effective_end_date
and elk.element_type_id = p_element_type_id
and pev.element_entry_id = ele.element_entry_id
and pev.input_value_id = p_input_value_id
and pev.effective_start_date between ele.effective_start_date
and ele.effective_end_date
order by ele.effective_start_date desc;
select ele.element_entry_id,
ele.effective_start_date,
ele.effective_end_date,
ele.object_version_number
from pay_element_entries_f ele,
pay_element_links_f elk
where p_effective_date between ele.effective_start_date
and ele.effective_end_date
and ele.assignment_id = p_assignment_id
and ele.element_link_id = elk.element_link_id
and ele.effective_start_date between elk.effective_start_date
and elk.effective_end_date
and elk.element_type_id = p_element_type_id
and ele.creator_type ='F'
and ele.entry_type = 'E'
and nvl(ele.creator_id,-1) <> p_prtt_enrt_rslt_id
order by ele.effective_start_date desc;
select min(effective_start_date),
max(effective_end_date)
from pay_element_entries_f
where element_entry_id = p_element_entry_id;
select pil.per_in_ler_id,
pil.trgr_table_pk_id,
ler.ler_id
from ben_per_in_ler pil,
ben_ler_f ler
where pil.per_in_ler_id = c_per_in_ler_id
and ler.ler_id = pil.ler_id
and ler.typ_cd ='ABS'
and c_effective_date between
ler.effective_start_date and ler.effective_end_date;
select leclr.*
from ben_le_clsn_n_rstr leclr
,ben_prtt_rt_val prv
,ben_acty_base_rt_f abr
where bkup_tbl_typ_cd = 'BEN_PRTT_RT_VAL_ADJ'
and bkup_tbl_id = prv.prtt_rt_val_id
and prv.acty_base_rt_id = abr.acty_base_rt_id
and abr.element_type_id = p_element_type_id;
l_delete_warning BOOLEAN;
l_update_warning boolean;
l_dt_del_mode := hr_api.g_delete;
py_element_entry_api.delete_element_entry
(p_validate =>p_validate
,p_datetrack_delete_mode =>l_dt_del_mode
,p_effective_date =>l_effective_date
,p_element_entry_id =>l_current_ee_rec.element_entry_id
,p_object_version_number =>l_current_ee_rec.object_version_number
,p_effective_start_date =>l_dummy_date
,p_effective_end_date =>l_dummy_date
,p_delete_warning =>l_delete_warning);
p_action => hr_api.g_delete,
p_old_amt => null,
p_input_value_id => l_abr_info.input_value_id,
p_element_entry_id => l_current_ee_rec.element_entry_id,
p_person_id => p_person_id,
p_business_group_id => p_business_group_id,
p_effective_date => l_prv_rec.rt_strt_dt -1 );
insert_into_quick_pay
(p_person_id => p_person_id,
p_element_type_id => l_element_type_id,
p_assignment_id => l_assignment_id,
p_element_entry_id => l_element_entry_id,
p_effective_date => l_effective_date,
p_start_date => l_effective_date,
p_end_date => g_max_end_date,
p_payroll_id => l_payroll_id);
hr_utility.set_location('before pay.delete l_effective_date '||l_effective_date,44333);
py_element_entry_api.delete_element_entry
(p_validate =>p_validate
,p_datetrack_delete_mode =>'FUTURE_CHANGE'
,p_effective_date =>l_effective_date
,p_element_entry_id =>l_element_entry_id
,p_object_version_number =>l_object_version_number
,p_effective_start_date =>l_effective_start_date
,p_effective_end_date =>l_effective_end_date
,p_delete_warning =>l_delete_warning);
hr_utility.set_location('after pay.delete l_effective_date '||l_effective_date,44333);
p_action => hr_api.g_delete,
p_old_amt => null,
p_input_value_id => l_input_value_id,
p_element_entry_id => l_element_entry_id,
p_person_id => p_person_id,
p_business_group_id => p_business_group_id,
p_effective_date => l_effective_date);
py_element_entry_api.update_element_entry
(p_validate =>p_validate
,p_datetrack_update_mode =>hr_api.g_correction
,p_effective_date =>l_effective_date
,p_business_group_id =>p_business_group_id
,p_element_entry_id =>l_element_entry_id
,p_override_user_ent_chk =>'Y'
,p_object_version_number =>l_object_version_number
,p_creator_type =>'F'
,p_creator_id =>l_prv_rec.prtt_enrt_rslt_id
,p_effective_start_date =>l_effective_start_date
,p_effective_end_date =>l_effective_end_date
,p_update_warning =>l_update_warning
);
py_element_entry_api.update_element_entry
(p_validate =>p_validate
,p_datetrack_update_mode =>hr_api.g_correction
,p_effective_date =>l_effective_date
,p_business_group_id =>p_business_group_id
,p_element_entry_id =>l_element_entry_id
,p_object_version_number =>l_object_version_number
,p_creator_type =>'F'
,p_creator_id =>l_prv_rec.prtt_enrt_rslt_id
,p_subpriority =>l_subpriority
,p_override_user_ent_chk =>'Y'
,p_input_value_id1 =>g_ext_inpval_tab(1).input_value_id
,p_entry_value1 =>g_ext_inpval_tab(1).return_value
,p_input_value_id2 =>g_ext_inpval_tab(2).input_value_id
,p_entry_value2 =>g_ext_inpval_tab(2).return_value
,p_input_value_id3 =>g_ext_inpval_tab(3).input_value_id
,p_entry_value3 =>g_ext_inpval_tab(3).return_value
,p_input_value_id4 =>g_ext_inpval_tab(4).input_value_id
,p_entry_value4 =>g_ext_inpval_tab(4).return_value
,p_input_value_id5 =>g_ext_inpval_tab(5).input_value_id
,p_entry_value5 =>g_ext_inpval_tab(5).return_value
,p_input_value_id6 =>g_ext_inpval_tab(6).input_value_id
,p_entry_value6 =>g_ext_inpval_tab(6).return_value
,p_input_value_id7 =>g_ext_inpval_tab(7).input_value_id
,p_entry_value7 =>g_ext_inpval_tab(7).return_value
,p_input_value_id8 =>g_ext_inpval_tab(8).input_value_id
,p_entry_value8 =>g_ext_inpval_tab(8).return_value
,p_input_value_id9 =>g_ext_inpval_tab(9).input_value_id
,p_entry_value9 =>g_ext_inpval_tab(9).return_value
,p_input_value_id10 =>g_ext_inpval_tab(10).input_value_id
,p_entry_value10 =>g_ext_inpval_tab(10).return_value
,p_input_value_id11 =>g_ext_inpval_tab(11).input_value_id
,p_entry_value11 =>g_ext_inpval_tab(11).return_value
,p_input_value_id12 =>g_ext_inpval_tab(12).input_value_id
,p_entry_value12 =>g_ext_inpval_tab(12).return_value
,p_input_value_id13 =>g_ext_inpval_tab(13).input_value_id
,p_entry_value13 =>g_ext_inpval_tab(13).return_value
,p_input_value_id14 =>g_ext_inpval_tab(14).input_value_id
,p_entry_value14 =>g_ext_inpval_tab(14).return_value
,p_effective_start_date =>l_effective_start_date
,p_effective_end_date =>l_effective_end_date
,p_update_warning =>l_update_warning
);
pqp_absence_plan_process.update_absence_plan_details
(p_person_id => p_person_id
,p_assignment_id => l_assignment_id
,p_business_group_id => p_business_group_id
,p_legislation_code => get_legislation_code(p_business_group_id)
,p_effective_date => l_effective_date
,p_element_type_id => l_element_type_id
,p_pl_id => g_result_rec.pl_id
,p_pl_typ_id => g_result_rec.pl_typ_id
,p_ler_id => l_ler_id
,p_per_in_ler_id => l_per_in_ler_id
,p_absence_attendance_id => l_absence_attendance_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_formula_outputs => g_outputs
,p_error_code => l_err_code
,p_error_message => l_err_mesg
);
select abr.prtl_mo_det_mthd_cd,
abr.prtl_mo_det_mthd_rl,
abr.wsh_rl_dy_mo_num,
abr.prtl_mo_eff_dt_det_cd,
abr.prtl_mo_eff_dt_det_rl,
abr.acty_typ_cd,
abr.element_type_id,
abr.input_value_id,
abr.input_va_calc_rl,
abr.rndg_cd,
abr.rndg_rl,
abr.ele_rqd_flag,
abr.one_ann_pymt_cd,
abr.entr_ann_val_flag,
abr.ele_entry_val_cd
from ben_acty_base_rt_f abr
where abr.acty_base_rt_id=p_acty_base_rt_id
and abr.business_group_id=p_business_group_id
and p_effective_date between
abr.effective_start_date and abr.effective_end_date;
select element_entry_value_id
from pay_element_entry_values_f
where element_entry_id = p_element_entry_id
and input_value_id = p_input_value_id
and p_effective_date between effective_start_date
and effective_end_date;
SELECT object_version_number
FROM pay_element_entries_f
WHERE element_entry_id = v_element_entry_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
l_delete_warning boolean;
l_update_ee boolean;
l_update_warning boolean;
l_update boolean;
l_update_override boolean;
l_update_change_insert boolean;
py_element_entry_api.delete_element_entry
(p_validate => p_validate
,p_datetrack_delete_mode => hr_api.g_zap
,p_effective_date => p_rt_strt_date
,p_element_entry_id => p_element_entry_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date -- out parm
,p_effective_end_date => l_effective_end_date -- out parm
,p_delete_warning => l_delete_warning
);
py_element_entry_api.update_element_entry
(p_validate =>p_validate
,p_datetrack_update_mode =>hr_api.g_correction
,p_effective_date =>p_rt_strt_date
,p_business_group_id =>p_business_group_id
,p_element_entry_id =>l_element_entry_id
,p_object_version_number =>l_object_version_number
,p_override_user_ent_chk =>'Y'
,p_creator_type =>'F'
,p_creator_id =>p_enrt_rslt_id
,p_effective_start_date =>l_effective_start_date
,p_effective_end_date =>l_effective_end_date
,p_update_warning =>l_update_warning
);
ben_prtt_rt_val_api.update_prtt_rt_val
(p_validate => p_validate
,p_person_id => p_person_id
,p_prtt_rt_val_id => l_prv_rec.prtt_rt_val_id
,p_business_group_id => l_prv_rec.business_group_id
,p_element_entry_value_id => l_entry_value_id
,p_input_value_id => p_input_value_id
,p_object_version_number => l_prv_object_version_number
,p_effective_date => p_rt_strt_date
);
py_element_entry_api.delete_element_entry
(p_validate => p_validate
,p_datetrack_delete_mode => hr_api.g_delete
,p_effective_date => p_rt_end_date
,p_element_entry_id => l_element_entry_id
-- ,p_object_version_number => l_object_version_number
,p_object_version_number => l_ovn
,p_effective_start_date => l_effective_start_date -- out parm
,p_effective_end_date => l_effective_end_date -- out parm
,p_delete_warning => l_delete_warning
);
p_action => hr_api.g_delete,
p_old_amt => p_amt,
p_input_value_id => p_input_value_id,
p_element_entry_id => p_element_entry_id,
p_person_id => p_person_id,
p_business_group_id => p_business_group_id,
p_effective_date => p_effective_date);
,p_dt_delete_mode IN VARCHAR2
,p_amt in number) is
--
-- Bug 2386380 fix - added default value to decode function
--
cursor c_pps_prev_month(p_end_date in date,
p_prtl_mo_eff_dt_det_cd in varchar2,
p_payroll_id in number) is
select start_date,end_date
from per_time_periods
where payroll_id = p_payroll_id
and decode(p_prtl_mo_eff_dt_det_cd,'DTPD',regular_payment_date,
'PPED',end_date,
'DTERND',regular_payment_date,
end_date)
< p_end_date
order by start_date desc;
select start_date,end_date
from per_time_periods
where payroll_id = p_payroll_id
and p_end_date between
start_date
and decode(p_prtl_mo_eff_dt_det_cd,'DTPD',regular_payment_date
,'PPED',end_date
,'DTERND',regular_payment_date
,end_date);
select start_date,end_date
from per_time_periods
where payroll_id = p_payroll_id
and decode(p_prtl_mo_eff_dt_det_cd,'DTPD',regular_payment_date,
'PPED',end_date,
'DTERND',regular_payment_date,
end_date)
<= p_end_date
and decode(p_prtl_mo_eff_dt_det_cd,'DTPD',regular_payment_date,
'PPED',end_date,
'DTERND',regular_payment_date,
end_date)
>= p_start_date
order by start_date asc;
select abr.prtl_mo_det_mthd_cd,
abr.prtl_mo_det_mthd_rl,
abr.wsh_rl_dy_mo_num,
abr.prtl_mo_eff_dt_det_cd,
abr.prtl_mo_eff_dt_det_rl,
abr.input_va_calc_rl,
abr.rndg_cd,
abr.rndg_rl,
abr.rcrrg_cd,
abr.ele_rqd_flag,
abr.one_ann_pymt_cd,
abr.entr_ann_val_flag,
abr.ele_entry_val_cd,
abr.name
from ben_acty_base_rt_f abr
where abr.acty_base_rt_id=p_acty_base_rt_id
and abr.business_group_id=p_business_group_id
and p_effective_date between
abr.effective_start_date and abr.effective_end_date;
select pel.element_link_id,
pel.element_type_id,
pev.input_value_id,
pet.element_name,
pet.processing_type
from pay_element_types_f pet,
pay_element_links_f pel,
pay_element_entries_f pee,
pay_element_entry_values_f pev
where pev.element_entry_value_id = p_element_entry_value_id
and pee.element_entry_id = pev.element_entry_id
and pev.effective_start_date between pee.effective_start_date
and pee.effective_end_date
and pel.element_link_id = pee.element_link_id
and pee.effective_start_date between pel.effective_start_date
and pel.effective_end_date
and pet.element_type_id = pel.element_type_id
and pel.effective_start_date between pet.effective_start_date
and pet.effective_end_date;
select asg.assignment_id,
asg.payroll_id,
pee.element_entry_id,
pee.effective_start_date,
pee.effective_end_date,
pee.object_version_number,
pee.original_entry_id,
pee.entry_type,
pee.element_link_id,
pev.screen_entry_value
from per_all_assignments_f asg,
pay_element_links_f pel,
pay_element_entries_f pee,
pay_element_entry_values_f pev
where asg.person_id = p_person_id
and pee.assignment_id = asg.assignment_id
and p_effective_date between asg.effective_start_date
and asg.effective_end_date
and pee.creator_id = p_enrt_rslt_id
and pee.creator_type = 'F'
and pee.entry_type = 'E'
and p_effective_date <= pee.effective_end_date
and pel.element_link_id = pee.element_link_id
and pee.effective_start_date between pel.effective_start_date
and pel.effective_end_date
and pel.element_type_id = p_element_type_id
and pev.element_entry_id = pee.element_entry_id
and pev.input_value_id = p_input_value_id
and (p_element_entry_value_id is null or
pev.element_entry_value_id = p_element_entry_value_id)
and pev.effective_start_date between pee.effective_start_date
and pee.effective_end_date
order by pee.effective_start_date ;
select pee.element_entry_id,
pee.effective_start_date,
pee.effective_end_date,
pee.object_version_number
from pay_element_entries_f pee,
pay_element_links_f pel
where pee.assignment_id = p_assignment_id
and pee.creator_id = p_enrt_rslt_id
and pee.creator_type = 'F'
and pee.entry_type = 'E'
and pee.effective_start_date > p_effective_date
and pel.element_link_id = pee.element_link_id
and pee.effective_start_date between pel.effective_start_date
and pel.effective_end_date
and pel.element_type_id = p_element_type_id
and pee.effective_start_date
= (select min(pee2.effective_start_date)
from pay_element_entries_f pee2
where pee2.element_entry_id = pee.element_entry_id);
select 'x'
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = p_enrt_rslt_id
and prv.acty_base_rt_id <> p_acty_base_rt_id
and prv.rt_end_dt > c_effective_date
-- and prv.rt_strt_dt <=c_effective_date
and prv.prtt_rt_val_stat_cd is null
and prv.element_entry_value_id in
(select pev.element_entry_value_id
from pay_element_entry_values_f pev
where pev.element_entry_id = c_element_entry_id);
select 'x'
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = p_enrt_rslt_id
and prv.acty_base_rt_id <> p_acty_base_rt_id
and prv.rt_end_dt > c_effective_date
-- and prv.rt_strt_dt <=c_effective_date
and prv.prtt_rt_val_stat_cd is null
and prv.acty_base_rt_id in
(select abr.acty_base_rt_id
from ben_acty_base_rt_f abr
where abr.acty_base_rt_id = prv.acty_base_rt_id
and abr.element_type_id = c_element_type_id
and c_effective_date between abr.effective_start_date
and abr.effective_end_date);
select paa.primary_flag
from per_all_assignments_f paa
where paa.assignment_id = c_assignment_id
and c_effective_date between paa.effective_start_date and paa.effective_end_date;
select pil.lf_evt_ocrd_dt,
pil.trgr_table_pk_id,
pil.per_in_ler_id,
ler.ler_id
from ben_prtt_rt_val prt,
ben_per_in_ler pil,
ben_ler_f ler
where prt.prtt_rt_val_id = c_prtt_rt_val_id
-- ended_per_in_ler_id may be null in case of back out
and pil.per_in_ler_id = nvl(prt.ended_per_in_ler_id,prt.per_in_ler_id)
and ler.ler_id = pil.ler_id
and ler.typ_cd ='ABS'
and c_effective_date between
ler.effective_start_date and ler.effective_end_date;
select min(effective_start_date),
max(effective_end_date)
from pay_element_entries_f
where element_entry_id = p_element_entry_id;
select object_version_number
from pay_element_entries_f pee
where pee.element_entry_id = p_element_entry_id
and p_effective_date between pee.effective_start_date
and pee.effective_end_date;
select ler.name name, ler.ler_id ler_id
from ben_prtt_rt_val prv,
ben_per_in_ler pil,
ben_ler_f ler
where prv.per_in_ler_id = pil.per_in_ler_id
and pil.ler_id = ler.ler_id
and prv.prtt_rt_val_id = p_prtt_rt_val_id
and prv.rt_end_dt = p_rt_end_date;
select pay2.payroll_id old_payroll_id,pay.payroll_id new_payroll_id
from per_all_assignments_f asg,
pay_payrolls_f pay,
per_all_assignments_f asg2,
pay_payrolls_f pay2
where asg.person_id = p_person_id
and asg.business_group_id = p_business_group_id
and asg.primary_flag = 'Y'
and p_rt_end_date between
asg.effective_start_date and asg.effective_end_date
and pay.payroll_id=asg.payroll_id
and pay.business_group_id = asg.business_group_id
and p_rt_end_date between
pay.effective_start_date and pay.effective_end_date
AND asg.assignment_id <> asg2.assignment_id
and asg2.person_id = p_person_id
and asg2.business_group_id = p_business_group_id
and asg2.primary_flag = 'Y'
and p_rt_end_date between
asg2.effective_start_date and asg2.effective_end_date
and pay2.payroll_id=asg2.payroll_id
and pay2.business_group_id = asg2.business_group_id
and p_rt_end_date between
pay2.effective_start_date and pay2.effective_end_date
and pay2.payroll_id<>pay.payroll_id
AND pay2.payroll_id = p_payroll_id ;
SELECT ((end_date - START_DATE) + 1)
FROM per_time_periods
WHERE payroll_id = p_payroll_id
AND p_rt_end_date BETWEEN start_date AND end_date;
SELECT pev.*
FROM pay_element_entry_values_f pev,
ben_prtt_rt_val prv
WHERE prv.prtt_rt_val_id = p_prtt_rt_val_id
AND prv.rt_end_dt = p_rt_end_date
AND prv.element_entry_value_id = pev.element_entry_value_id
and p_rt_end_date between pev.effective_start_date and pev.effective_end_date;
select pil.*
from ben_peR_in_ler pil,
ben_ler_f ler
where pil.procd_dt is null
and pil.business_group_id = p_business_group_id
and pil.person_id = p_person_id
and pil.per_in_ler_stat_cd = 'STRTD'
and pil.ler_id = ler.ler_id
and ler.typ_cd not in ('IREC','GSP','COMP','ABS','SCHEDDU')
and ler.business_group_id = p_business_group_id
order by pil.lf_evt_ocrd_dt desc;
select *
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_enrt_rslt_id
and pen.business_group_id = p_business_group_id
and pen.person_id = p_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.per_in_ler_id = p_pil_id
and pen.effective_end_Date = hr_api.g_eot
and pen.enrt_cvg_thru_dt = hr_api.g_eot;
l_delete_warning BOOLEAN;
l_update_ee boolean;
l_update_warning BOOLEAN;
l_dt_delete_mode varchar2(80);
l_update boolean;
l_update_override boolean;
l_update_change_insert boolean;
l_dt_delete_mode := hr_api.g_zap;
l_dt_delete_mode := hr_api.g_delete;
l_dt_delete_mode := hr_api.g_zap;
hr_utility.set_location('l_dt_delete_mode ' || l_dt_delete_mode,1200);
the below update_element_entry errors out. So, restricting this call
to only when atleast one extra input_value_id NOT NULL */
( g_ext_inpval_tab(1).input_value_id IS NOT NULL
OR g_ext_inpval_tab(2).input_value_id IS NOT NULL
OR g_ext_inpval_tab(3).input_value_id IS NOT NULL
OR g_ext_inpval_tab(4).input_value_id IS NOT NULL
OR g_ext_inpval_tab(5).input_value_id IS NOT NULL
OR g_ext_inpval_tab(6).input_value_id IS NOT NULL
OR g_ext_inpval_tab(7).input_value_id IS NOT NULL
OR g_ext_inpval_tab(8).input_value_id IS NOT NULL
OR g_ext_inpval_tab(9).input_value_id IS NOT NULL
OR g_ext_inpval_tab(10).input_value_id IS NOT NULL
OR g_ext_inpval_tab(11).input_value_id IS NOT NULL
OR g_ext_inpval_tab(12).input_value_id IS NOT NULL
OR g_ext_inpval_tab(13).input_value_id IS NOT NULL
OR g_ext_inpval_tab(14).input_value_id IS NOT NULL)
then
pay_element_entry_api.update_element_entry
(p_validate =>p_validate
,p_datetrack_update_mode =>hr_api.g_correction
,p_effective_date =>l_element_entry_start_date
,p_business_group_id =>p_business_group_id
,p_element_entry_id =>l_element_entry_id
,p_object_version_number =>l_object_version_number
,p_subpriority =>l_subpriority
,p_override_user_ent_chk =>'Y'
,p_input_value_id1 =>g_ext_inpval_tab(1).input_value_id
,p_entry_value1 =>g_ext_inpval_tab(1).return_value
,p_input_value_id2 =>g_ext_inpval_tab(2).input_value_id
,p_entry_value2 =>g_ext_inpval_tab(2).return_value
,p_input_value_id3 =>g_ext_inpval_tab(3).input_value_id
,p_entry_value3 =>g_ext_inpval_tab(3).return_value
,p_input_value_id4 =>g_ext_inpval_tab(4).input_value_id
,p_entry_value4 =>g_ext_inpval_tab(4).return_value
,p_input_value_id5 =>g_ext_inpval_tab(5).input_value_id
,p_entry_value5 =>g_ext_inpval_tab(5).return_value
,p_input_value_id6 =>g_ext_inpval_tab(6).input_value_id
,p_entry_value6 =>g_ext_inpval_tab(6).return_value
,p_input_value_id7 =>g_ext_inpval_tab(7).input_value_id
,p_entry_value7 =>g_ext_inpval_tab(7).return_value
,p_input_value_id8 =>g_ext_inpval_tab(8).input_value_id
,p_entry_value8 =>g_ext_inpval_tab(8).return_value
,p_input_value_id9 =>g_ext_inpval_tab(9).input_value_id
,p_entry_value9 =>g_ext_inpval_tab(9).return_value
,p_input_value_id10 =>g_ext_inpval_tab(10).input_value_id
,p_entry_value10 =>g_ext_inpval_tab(10).return_value
,p_input_value_id11 =>g_ext_inpval_tab(11).input_value_id
,p_entry_value11 =>g_ext_inpval_tab(11).return_value
,p_input_value_id12 =>g_ext_inpval_tab(12).input_value_id
,p_entry_value12 =>g_ext_inpval_tab(12).return_value
,p_input_value_id13 =>g_ext_inpval_tab(13).input_value_id
,p_entry_value13 =>g_ext_inpval_tab(13).return_value
,p_input_value_id14 =>g_ext_inpval_tab(14).input_value_id
,p_entry_value14 =>g_ext_inpval_tab(14).return_value
,p_effective_start_date =>l_effective_start_date
,p_effective_end_date =>l_effective_end_date
,p_update_warning =>l_update_warning
);
pqp_absence_plan_process.update_absence_plan_details
(p_person_id => p_person_id
,p_assignment_id => l_assignment_id
,p_business_group_id => p_business_group_id
,p_legislation_code => get_legislation_code(p_business_group_id)
,p_effective_date => p_effective_date
,p_element_type_id => l_element_type_id
,p_pl_id => g_result_rec.pl_id
,p_pl_typ_id => g_result_rec.pl_typ_id
,p_ler_id => l_ler_id
,p_per_in_ler_id => l_per_in_ler_id
,p_absence_attendance_id => l_absence_attendance_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_formula_outputs => g_outputs
,p_error_code => l_err_code
,p_error_message => l_err_mesg
);
l_dt_delete_mode = hr_api.g_delete then
--
-- ABSENCES: ZAP and recreate the element entry if the absence
-- was processed in a payroll and is currently end dated in a
-- period for which payroll has already been run.
--
-- This needs to be eventually merged with c_get_end_dt cursor
--
l_ele_processed := chk_ele_processed
(p_element_entry_id => l_element_entry_id
,p_original_entry_id=> l_original_entry_id
,p_processing_type => l_processing_type
,p_entry_type => l_entry_type
,p_business_group_id => p_business_group_id
,p_effective_date => p_effective_date);
pqp_absence_plan_process.update_absence_plan_details
(p_person_id => p_person_id
,p_assignment_id => l_assignment_id
,p_business_group_id => p_business_group_id
,p_legislation_code => get_legislation_code(p_business_group_id)
,p_effective_date => p_effective_date
,p_element_type_id => l_element_type_id
,p_pl_id => g_result_rec.pl_id
,p_pl_typ_id => g_result_rec.pl_typ_id
,p_ler_id => l_ler_id
,p_per_in_ler_id => l_per_in_ler_id
,p_absence_attendance_id => l_absence_attendance_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_rt_end_dt
,p_formula_outputs => g_outputs
,p_error_code => l_err_code
,p_error_message => l_err_mesg
);
elsif l_dt_delete_mode = hr_api.g_zap then
if g_debug then
hr_utility.set_location('ZAP mode',11);
py_element_entry_api.delete_element_entry
(p_validate => p_validate
,p_datetrack_delete_mode => hr_api.g_zap
,p_effective_date => l_element_entry_start_date
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_delete_warning => l_delete_warning
);
p_action => 'DELETE',
p_old_amt => fnd_number.canonical_to_number(l_curr_val_char),
p_input_value_id => l_input_value_id,
p_element_entry_id => l_element_entry_id,
p_person_id => p_person_id,
p_business_group_id => p_business_group_id,
p_effective_date => p_effective_date);
pqp_absence_plan_process.delete_absence_plan_details
(p_assignment_id => l_assignment_id
,p_business_group_id => p_business_group_id
,p_legislation_code => get_legislation_code(p_business_group_id)
,p_effective_date => p_effective_date
,p_pl_id => g_result_rec.pl_id
,p_pl_typ_id => g_result_rec.pl_typ_id
,p_ler_id => l_ler_id
,p_per_in_ler_id => l_per_in_ler_id
,p_absence_attendance_id => l_absence_attendance_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_formula_outputs => g_outputs
,p_error_code => l_err_code
,p_error_message => l_err_mesg
);
elsif l_dt_delete_mode = hr_api.g_delete then
if g_debug then
hr_utility.set_location('DELETE mode',11);
py_element_entry_api.update_element_entry
(p_validate =>p_validate
,p_datetrack_update_mode =>l_dt_upd_mode
,p_effective_date =>l_special_pp_date
,p_business_group_id =>p_business_group_id
,p_element_entry_id =>l_element_entry_id
,p_object_version_number =>l_object_version_number
,p_creator_type =>'F'
,p_creator_id =>p_enrt_rslt_id
,p_override_user_ent_chk =>'Y'
,p_input_value_id1 =>l_input_value_id
,p_entry_value1 =>l_special_amt
,p_effective_start_date =>l_new_date
,p_effective_end_date =>l_end_of_time
,p_update_warning =>l_update_warning
);
py_element_entry_api.update_element_entry
(p_validate =>p_validate
,p_datetrack_update_mode =>l_dt_upd_mode
,p_effective_date =>l_dt_to_use
,p_business_group_id =>p_business_group_id
,p_element_entry_id =>l_element_entry_id
,p_object_version_number =>l_object_version_number
,p_override_user_ent_chk =>'Y'
,p_creator_type =>'F'
,p_creator_id =>p_enrt_rslt_id
,p_input_value_id1 =>l_input_value_id
,p_entry_value1 =>0
,p_effective_start_date =>l_new_date
,p_effective_end_date =>l_end_of_time
,p_update_warning =>l_update_warning
);
(p_action => hr_api.g_update
,p_amt => 0
,p_old_amt => l_curr_val
,p_input_value_id => l_input_value_id
,p_element_entry_id => l_element_entry_id
,p_person_id => p_person_id
,p_business_group_id => p_business_group_id
,p_effective_date => l_dt_to_use
);
hr_utility.set_location('DT Delete mode '||l_dt_delete_mode,30);
py_element_entry_api.update_element_entry
(p_validate =>p_validate
,p_datetrack_update_mode =>l_dt_upd_mode
,p_effective_date =>l_rt_end_dt + 1
,p_business_group_id =>p_business_group_id
,p_element_entry_id =>l_element_entry_id
,p_override_user_ent_chk =>'Y'
,p_object_version_number =>l_object_version_number
,p_input_value_id1 =>l_input_value_id
,p_entry_value1 =>0
,p_effective_start_date =>l_effective_start_date
,p_effective_end_date =>l_effective_end_date
,p_update_warning =>l_update_warning
);
(p_action => 'UPDATE'
,p_amt => 0
,p_old_amt => fnd_number.canonical_to_number(l_curr_val_char)
,p_input_value_id => l_input_value_id
,p_element_entry_id => l_element_entry_id
,p_person_id => p_person_id
,p_business_group_id=> p_business_group_id
,p_effective_date => l_rt_end_dt + 1
);
py_element_entry_api.delete_element_entry
(p_validate => p_validate
,p_datetrack_delete_mode => l_dt_delete_mode
,p_effective_date => l_rt_end_dt
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_delete_warning => l_delete_warning);
p_action => hr_api.g_delete,
p_old_amt => fnd_number.canonical_to_number(l_curr_val_char),
p_input_value_id => l_input_value_id,
p_element_entry_id => l_element_entry_id,
p_person_id => p_person_id,
p_business_group_id => p_business_group_id,
p_effective_date => l_rt_end_dt);
pqp_absence_plan_process.update_absence_plan_details
(p_person_id => p_person_id
,p_assignment_id => l_assignment_id
,p_business_group_id => p_business_group_id
,p_legislation_code => get_legislation_code(p_business_group_id)
,p_effective_date => p_effective_date
,p_element_type_id => l_element_type_id
,p_pl_id => g_result_rec.pl_id
,p_pl_typ_id => g_result_rec.pl_typ_id
,p_ler_id => l_ler_id
,p_per_in_ler_id => l_per_in_ler_id
,p_absence_attendance_id => l_absence_attendance_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_formula_outputs => g_outputs
,p_error_code => l_err_code
,p_error_message => l_err_mesg
);
py_element_entry_api.update_element_entry
(p_validate =>p_validate
,p_datetrack_update_mode =>l_dt_upd_mode
,p_effective_date =>l_future_ee_rec.effective_end_date
,p_business_group_id =>p_business_group_id
,p_element_entry_id =>l_future_ee_rec.element_entry_id
,p_override_user_ent_chk =>'Y'
,p_object_version_number =>l_future_ee_rec.object_version_number
,p_input_value_id1 =>l_input_value_id
,p_entry_value1 =>0
,p_effective_start_date =>l_effective_start_date
,p_effective_end_date =>l_effective_end_date
,p_update_warning =>l_update_warning
);
py_element_entry_api.delete_element_entry
(p_validate => p_validate
,p_datetrack_delete_mode => hr_api.g_zap
,p_effective_date => l_future_ee_rec.effective_start_date
,p_element_entry_id => l_future_ee_rec.element_entry_id
,p_object_version_number => l_future_ee_rec.object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_delete_warning => l_delete_warning
);
p_action => 'DELETE',
p_old_amt => fnd_number.canonical_to_number(l_curr_val_char),
p_input_value_id => l_input_value_id,
p_element_entry_id => l_future_ee_rec.element_entry_id,
p_person_id => p_person_id,
p_business_group_id => p_business_group_id,
p_effective_date => p_effective_date);
g_get_link_cache.delete;
g_get_link_cache.delete;
select element_entry_value_id
from pay_element_entry_values_f
where ELEMENT_ENTRY_ID = p_element_entry_id
and input_value_id = p_input_value_id;
select processing_type
from pay_element_types_f
where element_type_id = p_element_type_id
and p_effective_date between effective_start_date
and effective_end_date ;
l_update_warning BOOLEAN;
l_delete_warning boolean;
py_element_entry_api.update_element_entry
(p_validate =>p_validate
,p_datetrack_update_mode =>'CORRECTION'
,p_effective_date =>p_effective_date
,p_business_group_id =>p_business_group_id
,p_element_entry_id =>l_element_entry_id
,p_object_version_number =>l_object_version_number
,p_creator_type =>'F'
,p_creator_id =>p_prtt_reimbmt_rqst_id
,p_input_value_id1 =>p_input_value_id
,p_entry_value1 =>p_amt
,p_effective_start_date =>l_effective_start_date
,p_effective_end_date =>l_effective_end_Date
,p_update_warning =>l_update_warning
);
py_element_entry_api.delete_element_entry
(p_validate => p_validate
,p_datetrack_delete_mode => hr_api.g_delete
,p_effective_date => p_effective_date
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_delete_warning => l_delete_warning
);
ben_prtt_rmt_aprvd_pymt_api.update_prtt_rmt_aprvd_pymt
(p_prtt_rmt_aprvd_fr_pymt_id => p_prtt_rmt_aprvd_fr_pymt_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_element_entry_value_id => l_element_entry_value_id,
p_object_version_number => p_object_version_number,
p_effective_date => p_effective_date,
p_aprvd_fr_pymt_amt => p_amt,
p_datetrack_mode => 'CORRECTION');
,p_dt_delete_mode IN VARCHAR2
,p_element_entry_value_id in number) is
--
cursor c_ele_info (p_element_entry_value_id number) is
select ele.element_entry_id,
ele.entry_type,
ele.original_entry_id,
elt.processing_type,
elk.element_type_id,
elk.effective_end_date
from pay_element_entry_values_f elv,
pay_element_entries_f ele,
pay_element_links_f elk,
pay_element_types_f elt
where elv.element_entry_value_id = p_element_entry_value_id
and elv.element_entry_id = ele.element_entry_id
and elv.effective_start_date between ele.effective_start_date
and ele.effective_end_date
and ele.element_link_id = elk.element_link_id
and ele.effective_start_date between elk.effective_start_date
and elk.effective_end_date
and elk.element_type_id = elt.element_type_id
and elk.effective_start_date between elt.effective_start_date
and elt.effective_end_date ;
select object_version_number
from pay_element_entries_f
where element_entry_id = p_element_entry_id;
l_delete_warning boolean;
py_element_entry_api.delete_element_entry
(p_validate => p_validate
,p_datetrack_delete_mode => hr_api.g_zap
,p_effective_date => p_effective_date
,p_element_entry_id => l_ele_rec.element_entry_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_delete_warning => l_delete_warning
);