[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pos.actual_termination_date
into l_actual_termination_date
from per_periods_of_service pos,
per_assignments_f pa
where pos.person_id = pa.person_id
and pa.assignment_id = p_assignment_id
and p_session_date
between pa.effective_start_date
and pa.effective_end_date
and pos.actual_termination_date is not null;
select asg.payroll_id,
asg.effective_start_date,
asg.effective_end_date
into v_payroll_id,
v_asg_effective_start_date,
v_asg_effective_end_date
from per_assignments_f asg
where asg.assignment_id = p_assignment_id
and asg.payroll_id is not null
and p_session_date between asg.effective_start_date
and asg.effective_end_date;
select tim.start_date,
tim.end_date
into v_time_period_start_date,
v_time_period_end_date
from per_time_periods tim
where tim.payroll_id = v_payroll_id
and p_session_date between tim.start_date
and tim.end_date;
select asg.effective_start_date
into v_start_date
from per_assignments_f asg
where asg.assignment_id = p_assignment_id
and asg.effective_end_date = v_asg_effective_start_date - 1
and asg.assignment_type = 'E' ;
select asg.effective_end_date
into v_end_date
from per_assignments_f asg
where asg.assignment_id = p_assignment_id
and asg.effective_start_date - 1 = v_asg_effective_end_date
and asg.assignment_type = 'E' ;
select 'Y'
into v_overlap_occurred
from sys.dual
where exists
(select /*+ INDEX(ee, pay_element_entries_f_n51) */ null
from pay_element_entries_f ee
where ee.entry_type = 'E'
and ee.element_entry_id <> nvl(p_element_entry_id,0)
and ee.assignment_id = p_assignment_id
and ee.element_link_id = p_element_link_id
and ((p_processing_type = 'R' and
ee.effective_start_date <= p_validation_end_date and
ee.effective_end_date >= p_validation_start_date)
or (p_processing_type = 'N' and
ee.effective_start_date >= p_period_start_date and
ee.effective_end_date <= p_period_end_date)));
select decode(l_qualifying_units,
'H', p.date_start + trunc(l_qualifying_los/24),
'D', p.date_start + l_qualifying_los,
'Y', add_months(p.date_start,(12 * l_qualifying_los)),
'W', p.date_start + (l_qualifying_los * 7),
add_months(p.date_start,l_qualifying_los))
from per_periods_of_service p,
per_all_assignments_f a
where a.assignment_id = p_assignment_id
and p.period_of_service_id = a.period_of_service_id;
select add_months(p.date_of_birth, (l_qualifying_age * 12))
from per_all_people_f p,
per_assignments_f asg
where p.person_id = asg.person_id
and p.date_of_birth is not null
and asg.assignment_id = p_assignment_id
-- session_date comparison has been removed, so ensure that
-- looking at the recent data.
and p.effective_start_date = (select max(papf.effective_start_date)
from per_all_people_f papf
where papf.person_id = asg.person_id);
select pel.qualifying_age,
pel.qualifying_length_of_service,
pel.qualifying_units
into l_qualifying_age,
l_qualifying_los,
l_qualifying_units
from pay_element_links_f pel
where pel.element_link_id = p_element_link_id
and p_session_date
between pel.effective_start_date
and pel.effective_end_date;
SELECT PAY_ELEMENT_ENTRIES_S.NEXTVAL
INTO v_element_entry_id
FROM SYS.DUAL;
SELECT PAY_RUN_RESULTS_S.NEXTVAL
INTO v_run_result_id
FROM SYS.DUAL;
cursor c1 is select pet.process_in_run_flag
from pay_element_types_f pet
where pet.element_type_id = p_element_type_id;
select 'Y' ROW_RETURNED
from per_assignments_f ASG,
pay_element_links_f PEL
where lpi_effective_date between pel.effective_start_date
and pel.effective_end_date
and lpi_effective_date between asg.effective_start_date
and asg.effective_end_date
and pel.element_link_id = P_ELEMENT_LINK_ID
and asg.assignment_id = P_ASSIGNMENT_ID
and ((pel.payroll_id is not null
and asg.payroll_id = pel.payroll_id)
or (pel.link_to_all_payrolls_flag = 'Y'
and asg.payroll_id is not null)
or (pel.payroll_id is null
and pel.link_to_all_payrolls_flag = 'N'))
and (pel.organization_id = asg.organization_id
or pel.organization_id is null)
and (pel.position_id = asg.position_id
or pel.position_id is null)
and (pel.job_id = asg.job_id
or pel.job_id is null)
and (pel.grade_id = asg.grade_id
or pel.grade_id is null)
and (pel.location_id = asg.location_id
or pel.location_id is null)
-- start of change 115.20 --
and (
pel.pay_basis_id = asg.pay_basis_id
or
--
-- if EL is associated with a pay basis then this clause fails
--
pel.pay_basis_id is null and
NOT EXISTS
(SELECT pb.pay_basis_id
FROM PER_PAY_BASES pb,
PAY_INPUT_VALUES_F iv
WHERE iv.element_type_id = pel.element_type_id
and p_effective_date between
iv.effective_start_date and iv.effective_end_date
and pb.input_value_id =
iv.input_value_id
and pb.business_group_id = asg.business_group_id
)
or
--
-- if EL is associated with a pay basis then the associated
-- PB_ID must match the PB_ID on ASG
--
pel.pay_basis_id is null and
EXISTS
(SELECT pb.pay_basis_id
FROM PER_PAY_BASES pb,
PAY_INPUT_VALUES_F iv
WHERE iv.element_type_id = pel.element_type_id
and p_effective_date between
iv.effective_start_date and iv.effective_end_date
and pb.input_value_id =
iv.input_value_id
and pb.pay_basis_id = asg.pay_basis_id
)
-- change 115.23
or
pel.pay_basis_id is null and
asg.pay_basis_id is null and
EXISTS
(SELECT pb.pay_basis_id
FROM PER_PAY_BASES pb,
PAY_INPUT_VALUES_F iv
WHERE iv.element_type_id = pel.element_type_id
and p_effective_date between
iv.effective_start_date and iv.effective_end_date
and pb.input_value_id =
iv.input_value_id
and pb.business_group_id = asg.business_group_id
)
-- bug 7434613
OR
pel.pay_basis_id is null and
p_creator_type IN ('RR','EE')
)
-- end of change 115.20 --
and (pel.employment_category = asg.employment_category
or pel.employment_category is null)
and (pel.people_group_id is null
or exists
(select 1
from pay_assignment_link_usages_f palu
where palu.assignment_id = P_ASSIGNMENT_ID
and palu.element_link_id = P_ELEMENT_LINK_ID
and lpi_effective_date between palu.effective_start_date
and palu.effective_end_date))
;
select 1
from per_assignments_f
where assignment_id = p_asg_id
and p_session_date between effective_start_date and effective_end_date;
p_session_date in date, -- Context date for datetrack selection.
--
-- Bugfix 5135065
-- Added parameters p_time_period_start_date and p_time_period_end_date.
p_creator_type IN varchar2, -- Bug 7434613. Creator type used in assignment_eligible for link
-- to skip pay_basis criteria validation for retro entry creation
p_time_period_start_date in date, -- Beginning of the time period under
-- consideration, should contain the
-- payroll period start date if a non-
-- recurring entry is being created
--
p_time_period_end_date in date, -- End of the time period under
-- consideration, should contain the
-- payroll period end date if a non-
-- recurring entry is being created
--
p_min_eligibility_date in out nocopy date, -- The earliest date that the assignment is eligible
-- for the element, in an unbroken period encompassing
-- the session date (See explanation below).
--
p_max_eligibility_date in out nocopy date -- The latest date that the assignment is eligible
-- for the element, in an unbroken period encompassing
-- the session date (See explanation below).
--
) is
--
cursor csr_link_bounds is
--
-- Get the outer boundaries of the link date effectivity
-- NB The aggregate functions mean that this cursor will
-- ALWAYS return a row.
--
select min (effective_start_date) LINK_START,
max (effective_end_date) LINK_END
from pay_element_links_f
where element_link_id = P_ELEMENT_LINK_ID;
select min (paf.effective_start_date) ASGT_START,
max (paf.effective_end_date) ASGT_END
from per_assignments_f paf
where paf.assignment_id = P_ASSIGNMENT_ID
and paf.assignment_type in ('E','B','C') ; -- Added assignment_type 'C' in the check for bug 8792107
select asg1.effective_end_date
from per_assignments_f asg1
where asg1.assignment_id = p_assignment_id
-- Removed the following predicate as it is redundant
-- i.e. If assignment end date is less than session date then it
-- follows that the assignment start date must be less than
-- session date.
--and asg1.effective_start_date <= lpi_session_date
and asg1.effective_end_date <= lpi_session_date
-- Bugfix 5135065
-- Exclude any pieces of the assignment that end before the time period
-- start date
and asg1.effective_end_date >= p_time_period_start_date
order by asg1.effective_end_date desc;
select asg1.effective_start_date
from per_assignments_f asg1
where asg1.assignment_id = p_assignment_id
and asg1.effective_end_date >= lpi_session_date
-- Bugfix 5135065
-- Exclude any pieces of the assignment that start after the time
-- period end date
and asg1.effective_start_date <= p_time_period_end_date
order by asg1.effective_start_date;
select ser.*
into l_service_rec
from per_periods_of_service ser
where ser.person_id = (select distinct person_id
from per_all_assignments_f
where assignment_id = p_assignment_id)
and (c1rec.effective_start_date - 1) between ser.date_start
and nvl(ser.actual_termination_date, hr_general.end_of_time);
select pet.post_termination_rule
into l_post_termination_rule
from pay_element_types_f pet
,pay_element_links_f pel
where pel.element_link_id = p_element_link_id
and l_link.link_start between pel.effective_start_date
and pel.effective_end_date
and pel.element_type_id = pet.element_type_id
and l_link.link_start between pet.effective_start_date
and pet.effective_end_date;
select decode (l_post_termination_rule, 'A', l_service_rec.actual_termination_date
, 'L', l_service_rec.last_standard_process_date
, 'F', l_service_rec.final_process_date, null)
into l_eff_term_date
from dual;
select elt_tl.element_name
from pay_element_types_f_tl ELT_TL,
pay_element_types_f ELT,
pay_element_links_f LINK
where elt.element_type_id = link.element_type_id
and elt_tl.element_type_id = elt.element_type_id
and P_ELEMENT_LINK_ID = link.element_link_id
and userenv('LANG') = elt_tl.language
and lpi_session_date between link.effective_start_date
and link.effective_end_date
and lpi_session_date between elt.effective_start_date
and elt.effective_end_date;
select assignment_number
from per_assignments_f
where assignment_id = P_ASSIGNMENT_ID
and lpi_session_date between effective_start_date
and effective_end_date;
select asg.period_of_service_id,
asg.primary_flag,
-- Bugfix 5616075
pos.actual_termination_date,
pos.last_standard_process_date,
pos.final_process_date
into v_period_of_service_id,
v_primary_flag,
v_actual_termination_date,
v_last_standard_process_date,
v_final_process_date
from per_assignments_f asg,
per_periods_of_service pos
where asg.assignment_id = p_assignment_id
and asg.period_of_service_id = pos.period_of_service_id (+)
and p_session_date between asg.effective_start_date
and asg.effective_end_date;
select pet.post_termination_rule,
pet.processing_type
into v_post_termination_rule,
v_processing_type
from pay_element_types_f pet,
pay_element_links_f pel
where p_session_date between pel.effective_start_date
and pel.effective_end_date
and pel.element_link_id = p_element_link_id
and pet.element_type_id = pel.element_type_id
and p_session_date between pet.effective_start_date
and pet.effective_end_date;
select ptp.end_date
into v_element_term_rule_date
from per_all_assignments_f asg,
per_time_periods ptp
where asg.assignment_id = p_assignment_id
and p_session_date between asg.effective_start_date and asg.effective_end_date
and asg.payroll_id = ptp.payroll_id
and v_element_term_rule_date between ptp.start_date and ptp.end_date;
select min(asg.effective_start_date)
into v_asg_term_date
from per_assignments_f asg,
per_assignment_status_types ast
where asg.assignment_id = p_assignment_id
and asg.effective_end_date >= p_session_date
and asg.assignment_status_type_id = ast.assignment_status_type_id
and ast.per_system_status = 'TERM_ASSIGN';
select asg.effective_start_date
into v_asg_term_date
from per_assignments_f asg,
per_assignment_status_types ast
where asg.assignment_id = p_assignment_id
and asg.effective_end_date >= p_session_date
and asg.assignment_status_type_id = ast.assignment_status_type_id
and ast.per_system_status = 'END';
select ptp.end_date
into v_element_term_rule_date
from per_time_periods ptp,
per_assignments_f asg
where asg.assignment_id = p_assignment_id
and v_asg_term_date between asg.effective_start_date and asg.effective_end_date
and asg.payroll_id is not null
and ptp.payroll_id = asg.payroll_id
and v_asg_term_date between ptp.start_date and ptp.end_date;
select max(asg.effective_end_date)
into v_element_term_rule_date
from per_assignments_f asg
where asg.assignment_id = p_assignment_id
and asg.effective_start_date >= v_asg_term_date;
select ptp.end_date
into v_element_term_rule_date
from per_time_periods ptp,
per_assignments_f asg
where asg.assignment_id = p_assignment_id
and v_asg_term_date
between asg.effective_start_date
and asg.effective_end_date
and asg.payroll_id is not null
and ptp.payroll_id = asg.payroll_id
and v_asg_term_date
between ptp.start_date
and ptp.end_date;
select max(asg.effective_end_date)
into v_element_term_rule_date
from per_assignments_f asg
where asg.assignment_id = p_assignment_id
and asg.effective_start_date >= v_asg_term_date;
select min(pel.effective_start_date),
max(pel.effective_end_date)
into v_element_link_start_date,
v_element_link_end_date
from pay_element_links_f pel
where pel.element_link_id = p_element_link_id;
select min(pay.effective_start_date),
max(pay.effective_end_date)
into v_payroll_start_date,
v_payroll_end_date
from pay_all_payrolls_f pay,
per_all_assignments_f asg
where p_session_date
between asg.effective_start_date
and asg.effective_end_date
and asg.assignment_id = p_assignment_id
and asg.payroll_id is not null
and pay.payroll_id = asg.payroll_id;
select e.effective_end_date
into v_current_effective_end_date
from pay_element_entries_f e
where e.element_entry_id = p_element_entry_id
and p_session_date
between e.effective_start_date
and e.effective_end_date;
select /*+ INDEX(pee, pay_element_entries_f_n51) */
nvl(min(pee.effective_start_date) - 1, hr_general.end_of_time)
into v_future_recurring_end_date
from pay_element_entries_f pee
where pee.entry_type = 'E'
and pee.assignment_id = p_assignment_id
and pee.element_link_id = p_element_link_id
and pee.element_entry_id <> nvl(p_element_entry_id,0)
and ((p_mult_entries_allowed_flag = 'Y' and
nvl(pee.original_entry_id,pee.element_entry_id) =
nvl(p_original_entry_id,p_element_entry_id))
or (p_mult_entries_allowed_flag = 'N'))
and pee.effective_start_date > p_session_date;
if p_usage = 'INSERT' then
--
-- Bugfix 4114282
-- We now allow for a nonrecurring entry to be created for part of a
-- payroll period, even when the link only exists for part of that
-- period, as long as there is eligibility for the element.
-- Therefore, we no longer need this check...
/*
--
-- Ensure that the link exists for the duration of the entry
-- when inserting an nonrecurring entry.
--
if (greatest(p_validation_start_date,v_min_eligibility_date) < v_element_link_start_date) or
(least(p_validation_end_date,v_max_eligibility_date) > v_element_link_end_date) then
hr_utility.set_message(801, 'HR_6132_ELE_ENTRY_LINK_MISSING');
if(p_usage <> 'INSERT') then
if (v_max_eligibility_date < p_validation_end_date) then
hr_utility.set_message(801, 'HR_6284_ELE_ENTRY_DT_ASG_DEL');
select et_tl.element_name,
et.closed_for_entry_flag,
et.legislation_code
from pay_element_types_f_tl et_tl,
pay_element_types_f et
where et.element_type_id = et_tl.element_type_id
and et.element_type_id = p_element_type_id
and userenv('LANG') = et_tl.language
and et.effective_start_date <= p_validation_end_date
and et.effective_end_date >= p_validation_start_date;
select tp.status
from per_time_periods tp,
per_assignments_f asg
where asg.assignment_id = p_assignment_id
and asg.payroll_id is not null
and asg.effective_start_date <= p_validation_end_date
and asg.effective_end_date >= p_validation_start_date
and tp.payroll_id = asg.payroll_id
and tp.end_date >= p_validation_start_date
and tp.start_date <= p_validation_end_date
and tp.end_date >= asg.effective_start_date
and tp.start_date <= asg.effective_end_date
and tp.status='C';
select iv.uom,
iv.hot_default_flag,
iv.lookup_type,
iv.value_set_id
into v_uom,
v_hot_default_flag,
v_lookup_type,
v_value_set_id
from pay_input_values_f iv
where iv.input_value_id = p_input_value_id
and p_session_date
between iv.effective_start_date and iv.effective_end_date;
select /*+ INDEX(l, pay_link_input_values_f_n2) */
l.default_value,
l.min_value,
l.max_value
into v_default_value,
v_minimum_value,
v_maximum_value
from pay_link_input_values_f l
where l.input_value_id = p_input_value_id
and l.element_link_id = p_element_link_id
and p_session_date
between l.effective_start_date and l.effective_end_date;
select decode(l.default_value,
'',i.default_value,
l.default_value)
into v_default_value
from pay_link_input_values_f l,
pay_input_values_f i
where i.input_value_id = p_input_value_id
and l.input_value_id = i.input_value_id
and l.element_link_id = p_element_link_id
and p_session_date
between i.effective_start_date and i.effective_end_date
and p_session_date
between l.effective_start_date and l.effective_end_date;
select i.default_value,
i.min_value,
i.max_value
into v_default_value,
v_minimum_value,
v_maximum_value
from pay_input_values_f i
where i.input_value_id = p_input_value_id
and p_session_date
between i.effective_start_date and i.effective_end_date;
select h.meaning
into v_value_format_out
from hr_lookups h
where h.lookup_type = v_lookup_type
and h.lookup_code = v_value_format_in;
select i.hot_default_flag,
i.mandatory_flag,
i_tl.name
into v_hot_default_flag,
v_mandatory_flag,
v_name
from pay_input_values_f_tl i_tl,
pay_input_values_f i
where i.input_value_id = i_tl.input_value_id
and i.input_value_id = p_input_value_id
and userenv('LANG') = i_tl.language
and p_session_date
between i.effective_start_date
and i.effective_end_date;
select nvl(l.default_value,i.default_value)
into v_default_value
from pay_link_input_values_f l,
pay_input_values_f i
where i.input_value_id = p_input_value_id
and l.input_value_id = i.input_value_id
and l.element_link_id = p_element_link_id
and p_session_date
between i.effective_start_date and i.effective_end_date
and p_session_date
between l.effective_start_date and l.effective_end_date;
p_dt_update_mode in varchar2,
p_dt_delete_mode in varchar2,
p_usage in varchar2,
p_target_entry_id in number
) is
begin
g_debug := hr_utility.debug_enabled;
hr_utility.trace(' p_dt_update_mode : '|| p_dt_update_mode);
hr_utility.trace(' p_dt_delete_mode : '|| p_dt_delete_mode);
p_dt_update_mode,
p_dt_delete_mode,
p_usage,
p_target_entry_id,
null
);
p_dt_update_mode in varchar2,
p_dt_delete_mode in varchar2,
p_usage in varchar2,
p_target_entry_id in number,
p_creator_type in varchar2
) is
--
-- Local Variables
--
v_error_flag varchar2(1) := 'N';
select effective_start_date
from pay_element_entries_f
where element_entry_id = p_target_entry_id;
select 'Y'
from pay_element_links_f pel
,pay_element_types_f pet
,pay_accrual_plans pap
where pel.element_link_id = p_element_link_id
and p_session_date between pel.effective_start_date
and pel.effective_end_date
and pel.element_type_id = pet.element_type_id
and p_session_date between pet.effective_start_date
and pet.effective_end_date
and pet.element_type_id = pap.accrual_plan_element_type_id
union all
-- Should not allow these absence elements for CWKs
select 'Y'
from pay_element_links_f pel
,pay_element_types_f pet
,pay_input_values_f piv
,per_absence_attendance_types abt
where pel.element_link_id = p_element_link_id
and p_session_date between pel.effective_start_date
and pel.effective_end_date
and pel.element_type_id = pet.element_type_id
and p_session_date between pet.effective_start_date
and pet.effective_end_date
and pet.element_type_id = piv.element_type_id
and p_session_date between piv.effective_start_date
and piv.effective_end_date
and piv.input_value_id = abt.input_value_id
and abt.input_value_id is not null;
select et.element_type_id,
et.processing_type,
et.multiple_entries_allowed_flag,
et.third_party_pay_only_flag,
-- Bugfix 2866619
-- Need element classification for comparison purposes
et.element_name,
ec.classification_name
into v_element_type_id,
v_processing_type,
v_mult_entries_allowed_flag,
v_third_party_pay_only_flag,
v_element_name,
v_classification_name
from pay_element_links_f el,
pay_element_types_f et,
pay_element_classifications ec
where el.element_link_id = p_element_link_id
and et.element_type_id = el.element_type_id
and et.classification_id = ec.classification_id
and p_session_date between el.effective_start_date
and el.effective_end_date
and p_session_date between et.effective_start_date
and et.effective_end_date;
select asg.assignment_type, asg.assignment_number
into v_assignment_type, v_assignment_number
from per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and p_session_date between asg.effective_start_date
and asg.effective_end_date;
if ((p_usage = 'INSERT' or
p_dt_delete_mode = 'FUTURE_CHANGE') or
(p_dt_delete_mode = 'DELETE_NEXT_CHANGE' and
p_validation_end_date = hr_general.end_of_time)) then
--
if g_debug then
hr_utility.set_location('hr_entry.chk_element_entry_main', 10);
if p_usage = 'INSERT' then
v_validation_start_date := p_session_date;
If nvl(p_dt_delete_mode,'NULL') <> 'ZAP' then
hr_entry.chk_element_entry_eligibility (p_assignment_id =>p_assignment_id,
p_element_link_id =>p_element_link_id,
p_session_date =>p_session_date,
p_usage =>p_usage,
p_creator_type => p_creator_type,
p_validation_start_date =>v_validation_start_date,
p_validation_end_date =>v_validation_end_date,
p_time_period_start_date =>v_period_start_date,
p_time_period_end_date =>v_period_end_date,
p_min_eligibility_date =>v_min_eligible_date,
p_max_eligibility_date =>v_max_eligible_date
);
select distinct pet.POST_TERMINATION_RULE
into v_post_termination_rule
from pay_element_links_f pel,
pay_element_types_f pet
where pel.element_type_id=pet.element_type_id
and pel.element_link_id=p_element_link_id
and p_session_date between pet.effective_start_date and pet.effective_end_date
and p_session_date between pel.effective_start_date and pel.effective_end_date;
select min(pos.actual_termination_date)
into v_actual_termination_date
from per_periods_of_service pos,
per_assignments_f pa
where pos.person_id = pa.person_id
and pa.assignment_id = p_assignment_id
and p_session_date
between pa.effective_start_date
and pa.effective_end_date
and pos.actual_termination_date is not null
and pos.actual_termination_date >= p_session_date;
end if; /*Bug 8816456 Extended end if for p_dt_delete_mode <> 'ZAP' as in ZAP mode we dont need non_recurring_dates call*/
if ((p_usage = 'INSERT' or
p_dt_delete_mode = 'FUTURE_CHANGE') or
(p_dt_delete_mode = 'DELETE_NEXT_CHANGE' and
p_validation_end_date = hr_general.end_of_time)) then
--
if g_debug then
hr_utility.set_location('hr_entry.chk_element_entry_main', 20);
if p_usage = 'INSERT' then
dt_api.validate_dt_mode(
p_effective_date => p_session_date --*
,p_datetrack_mode => 'INSERT' --*
,p_base_table_name => 'pay_element_entries_f'
,p_base_key_column => 'element_entry_id'
,p_base_key_value => p_element_entry_id --*
,p_parent_table_name1 => 'per_all_assignments_f'
,p_parent_key_column1 => 'assignment_id'
,p_parent_key_value1 => p_assignment_id
,p_enforce_foreign_locking => true
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date);
if (p_usage = 'INSERT' and
((v_processing_type = 'R' and
p_entry_type <> 'E') or
v_processing_type = 'N')) then
--
if g_debug then
hr_utility.set_location('hr_entry.chk_element_entry_main', 30);
p_usage = 'INSERT') then
--
if g_debug then
hr_utility.set_location('hr_entry.chk_element_entry_main', 35);
select 'Y'
into v_error_flag
from sys.dual
where exists
(select /*+ FIRST_ROWS(1)
INDEX(pee, pay_element_entries_f_n51 */ 1
from pay_element_entries_f pee
where pee.entry_type = p_entry_type
and pee.assignment_id = p_assignment_id
and pee.element_link_id = p_element_link_id
and pee.effective_start_date >= v_period_start_date
and pee.effective_end_date <= v_period_end_date);
(p_dt_delete_mode = 'ZAP' or
p_dt_delete_mode = 'DELETE')) then
--
if g_debug then
hr_utility.set_location('hr_entry.chk_element_entry_main', 40);
select 'Y'
into v_error_flag
from sys.dual
where exists
(select /*+ FIRST_ROWS(1)
INDEX(pee, pay_element_entries_f_n51 */ 1
from pay_element_entries_f pee
where pee.target_entry_id = p_element_entry_id
and pee.element_link_id = p_element_link_id
and pee.assignment_id = p_assignment_id
and pee.effective_start_date <= v_validation_end_date
and pee.effective_end_date >= v_validation_start_date);
p_usage = 'INSERT') then
--
if g_debug then
hr_utility.set_location('hr_entry.chk_element_entry_main', 45);
select 'Y'
into v_error_flag
from sys.dual
where exists
(select /*+ FIRST_ROWS(1)
INDEX(pee, pay_element_entries_f_n51 */ 1
from pay_element_entries_f pee
where pee.entry_type = 'S'
and pee.assignment_id = p_assignment_id
and pee.element_link_id = p_element_link_id
and pee.effective_start_date >= v_period_start_date
and pee.effective_end_date <= v_period_end_date);
select 'Y'
into v_error_flag
from sys.dual
where exists
(select /*+ FIRST_ROWS(1)
INDEX(pee, pay_element_entries_f_n51 */ 1
from pay_element_entries_f pee
where pee.entry_type in ('R','A')
and pee.assignment_id = p_assignment_id
and pee.element_link_id = p_element_link_id
and pee.effective_start_date >= v_period_start_date
and pee.effective_end_date <= v_period_end_date);
p_usage = 'INSERT') then
--
if g_debug then
hr_utility.set_location('hr_entry.chk_element_entry_main', 55);
select 'Y'
into v_error_flag
from sys.dual
where not exists
(select /*+ FIRST_ROWS(1) */ 1
from pay_element_entries_f pee
where pee.assignment_id = p_assignment_id
and pee.element_entry_id = p_target_entry_id
having min(pee.effective_start_date) <=
v_validation_start_date
and max(pee.effective_end_date) >=
v_validation_end_date);
select 'Y'
into v_error_flag
from sys.dual
where exists
(select /*+ FIRST_ROWS(1)
INDEX(pee, pay_element_entries_f_n51 */ 1
from pay_element_entries_f pee
where pee.entry_type = 'S'
and pee.assignment_id = p_assignment_id
and pee.element_link_id = p_element_link_id
and pee.effective_start_date >= v_period_start_date
and pee.effective_end_date <= v_period_end_date);
select 'Y'
into v_error_flag
from sys.dual
where exists
(select /*+ FIRST_ROWS(1) */ 1
from pay_element_entries_f pee
where pee.entry_type in ('R','A')
and pee.assignment_id = p_assignment_id
and pee.target_entry_id = p_target_entry_id
and pee.effective_start_date >= v_period_start_date
and pee.effective_end_date <= v_period_end_date);
((p_usage = 'INSERT' or
p_dt_delete_mode = 'FUTURE_CHANGE') or
(p_dt_delete_mode = 'DELETE_NEXT_CHANGE' and
p_validation_end_date = hr_general.end_of_time)) then
p_effective_end_date := v_validation_end_date;
select peev.input_value_id,
piv.uom,
peev.screen_entry_value value
from pay_input_values_f piv,
pay_element_entry_values_f peev
where peev.element_entry_id = p_element_entry_id
and piv.input_value_id = peev.input_value_id
and p_session_date between peev.effective_start_date
and peev.effective_end_date
and p_session_date between piv.effective_start_date
and piv.effective_end_date;
select ee.element_entry_id,
ee.assignment_id,
ee.entry_type,
ee.creator_type,
ee.creator_id,
ee.effective_start_date,
ee.effective_end_date,
ee.cost_allocation_keyflex_id,
et.element_type_id,
et.processing_type,
et.input_currency_code,
et.output_currency_code
into v_element_entry_id,
v_assignment_id,
v_entry_type,
v_creator_type,
v_creator_id,
v_effective_start_date,
v_effective_end_date,
v_cost_allocation_keyflex_id,
v_element_type_id,
v_processing_type,
v_input_currency_code,
v_output_currency_code
from pay_element_entries_f ee,
pay_element_links_f el,
pay_element_types_f et
where ee.element_entry_id = p_element_entry_id
and el.element_link_id = ee.element_link_id
and et.element_type_id = el.element_type_id
and p_session_date between ee.effective_start_date
and ee.effective_end_date
and p_session_date between el.effective_start_date
and el.effective_end_date
and p_session_date between et.effective_start_date
and et.effective_end_date;
select count(*)
into v_entry_count
from pay_element_entries_f ee
where ee.element_entry_id = p_element_entry_id;
insert into pay_element_entry_values_f
(element_entry_value_id,
effective_start_date,
effective_end_date,
input_value_id,
element_entry_id,
screen_entry_value)
values
(pay_element_entry_values_s.nextval,
v_effective_start_date,
v_effective_end_date,
p_input_value_id_tbl(v_loop),
p_element_entry_id,
p_entry_value_tbl(v_loop));
insert into pay_element_entry_values_f
(element_entry_value_id,
effective_start_date,
effective_end_date,
input_value_id,
element_entry_id,
screen_entry_value)
select
eev.element_entry_value_id,
v_effective_start_date,
v_effective_end_date,
eev.input_value_id,
p_element_entry_id,
p_entry_value_tbl(v_loop)
from pay_element_entry_values_f eev
where eev.element_entry_id = p_element_entry_id
and eev.input_value_id + 0 = p_input_value_id_tbl(v_loop)
and p_session_date - 1 between eev.effective_start_date
and eev.effective_end_date;
select business_group_id
into v_bg_id
from per_assignments_f pas
where pas.assignment_id = v_assignment_id
and p_session_date between pas.effective_start_date
and pas.effective_end_date
and rownum=1;
select eev.screen_entry_value
into v_jurisdiction
from pay_element_entry_values_f eev,
pay_input_values_f piv,
pay_element_entries_f pee
where pee.element_entry_id = v_element_entry_id
and eev.element_entry_id = pee.element_entry_id
and eev.input_value_id = piv.input_value_id
and piv.name = 'Jurisdiction'
and p_session_date between pee.effective_start_date
and pee.effective_end_date
and p_session_date between eev.effective_start_date
and eev.effective_end_date
and p_session_date between piv.effective_start_date
and piv.effective_end_date;
insert into pay_run_results
(run_result_id,
element_type_id,
assignment_action_id,
entry_type,
source_id,
source_type,
status,
jurisdiction_code)
values
(v_run_result_id,
v_element_type_id,
null,
v_entry_type,
v_element_entry_id,
'E',
v_status,
v_jurisdiction);
insert into pay_run_result_values
(input_value_id,
run_result_id,
result_value)
values
(peev.input_value_id,
v_run_result_id,
v_amount);
insert into pay_run_result_values
(input_value_id,
run_result_id,
result_value)
select
peev.input_value_id,
v_run_result_id,
peev.screen_entry_value
from pay_element_entry_values_f peev
where peev.element_entry_id = v_element_entry_id
and p_session_date between peev.effective_start_date
and peev.effective_end_date;
procedure delete_covered_dependants
(p_validation_start_date date,
p_element_entry_id number,
p_start_date date DEFAULT NULL,
p_end_date date DEFAULT NULL) is
--
-- Set of covered dependants which are children of the deleted element entry
-- and which overlap or are later than the deletion date
-- (in the case of zap this will be all children because
-- p_validation_start_date will be the beginning of time).
-- If p_end_date is NULL change the start date of the covered dependent
cursor csr_covered_dependents is
select rowid,
dep.*
from ben_covered_dependents_f DEP
where dep.effective_end_date >= p_validation_start_date
and dep.element_entry_id = p_element_entry_id;
ben_covered_dependents_pkg.delete_row (dependant.rowid);
ben_covered_dependents_pkg.update_row (
--
p_covered_dependent_id => dependant.covered_dependent_id,
p_rowid => dependant.rowid,
p_contact_relationship_id => dependant.contact_relationship_id,
p_element_entry_id => dependant.element_entry_id,
p_effective_start_date => v_start_date,
p_effective_end_date => v_end_date);
end delete_covered_dependants;
procedure delete_beneficiaries
(p_validation_start_date date,
p_element_entry_id number,
p_start_date date DEFAULT NULL,
p_end_date date DEFAULT NULL)
is
--
-- Set of beneficiaries which are children of the deleted element entry
-- and which overlap or are later than the deletion date
-- (in the case of zap this will be all children because
-- p_validation_start_date will be the beginning of time).
-- If p_end_date is null change the start date of the covered beneficiary
cursor csr_beneficiaries is
select rowid,
ben.*
from ben_beneficiaries_f BEN
where ben.effective_end_date >= p_validation_start_date
and ben.element_entry_id = p_element_entry_id;
ben_beneficiaries_pkg.delete_row (entry_beneficiary.rowid);
ben_beneficiaries_pkg.update_row (
--
p_rowid => entry_beneficiary.rowid,
p_source_type => entry_beneficiary.source_type,
p_source_id => entry_beneficiary.source_id,
p_element_entry_id => entry_beneficiary.element_entry_id,
p_benefit_level => entry_beneficiary.benefit_level,
p_proportion => entry_beneficiary.proportion,
p_beneficiary_id => entry_beneficiary.beneficiary_id,
p_effective_start_date => v_start_date,
p_effective_end_date => v_end_date);
end delete_beneficiaries;
p_dt_delete_mode in varchar2,
p_session_date in date,
p_validation_start_date in date,
p_validation_end_date in date
) is
--------------------------------------
--
-- Local Variables
--
v_dt_delete_mode varchar2(30);
select rowid,
ben.*
from ben_beneficiaries_f BEN
where ben.element_entry_id = p_element_entry_id
and ben.effective_end_date = p_validation_start_date -1;
ben_beneficiaries_pkg.update_row (
--
p_rowid => entry_beneficiary.rowid,
p_source_type => entry_beneficiary.source_type,
p_source_id => entry_beneficiary.source_id,
p_element_entry_id => entry_beneficiary.element_entry_id,
p_benefit_level => entry_beneficiary.benefit_level,
p_proportion => entry_beneficiary.proportion,
p_beneficiary_id => entry_beneficiary.beneficiary_id,
p_effective_start_date =>entry_beneficiary.effective_start_date,
p_effective_end_date => p_validation_end_date);
select rowid,
dep.*
from ben_covered_dependents_f DEP
where dep.element_entry_id = p_element_entry_id
and dep.effective_end_date = p_validation_start_date -1;
ben_covered_dependents_pkg.update_row (
--
p_covered_dependent_id => dependant.covered_dependent_id,
p_rowid => dependant.rowid,
p_contact_relationship_id=> dependant.contact_relationship_id,
p_element_entry_id => dependant.element_entry_id,
p_effective_start_date => dependant.effective_start_date,
p_effective_end_date => p_validation_end_date);
v_dt_delete_mode := p_dt_delete_mode;
if (v_dt_delete_mode = 'ZAP'
or v_dt_delete_mode = 'DELETE') then
--
-- Delete rows in child tables which would be orphaned by the entry deletion
--
-- Bug fix 519738 - call procedure with new list of parameters, p_start_date defaults to null
hr_entry.delete_beneficiaries(
p_element_entry_id => p_element_entry_id,
p_end_date => p_session_date,
p_validation_start_date => p_validation_start_date);
hr_entry.delete_covered_dependants(
p_element_entry_id => p_element_entry_id,
p_end_date => p_session_date,
p_validation_start_date => p_validation_start_date);
if v_dt_delete_mode = 'ZAP' then
--
-- Enhancement 3205906
-- No longer need to delete run results for nonrecurring entries and
-- balance adjustments. These are no longer automatically created.
/*
if (p_processing_type = 'N' or
p_entry_type = 'B') then
--
-- Check to see if the entry is a balance adjustment. If, yes then
-- set the status to 'P' for processed.
--
if p_entry_type = 'B' then
v_status := 'P';
delete from pay_run_result_values rrv
where rrv.run_result_id =
(select rr.run_result_id
from pay_run_results rr
where rr.element_type_id + 0 = p_element_type_id
and rr.entry_type = p_entry_type
and rr.source_id = p_element_entry_id
and rr.source_type = 'E'
and rr.status = v_status);
delete from pay_run_results rr
where rr.element_type_id + 0 = p_element_type_id
and rr.entry_type = p_entry_type
and rr.source_id = p_element_entry_id
and rr.source_type = 'E'
and rr.status = v_status;
delete from pay_element_entry_values_f eev
where eev.element_entry_id = p_element_entry_id;
delete from pay_element_entry_values_f eev
where eev.element_entry_id in
(select ee.element_entry_id
from pay_element_entries_f ee
where ee.creator_type = p_creator_type
and ee.creator_id = p_creator_id
and ee.entry_type = p_entry_type
and ee.element_link_id = p_element_link_id
and ee.assignment_id = p_assignment_id);
delete from pay_element_entries_f ee
where ee.element_entry_id = p_element_entry_id;
delete from pay_element_entries_f ee
where ee.creator_type = p_creator_type
and ee.creator_id = p_creator_id
and ee.entry_type = p_entry_type
and ee.element_link_id = p_element_link_id
and ee.assignment_id = p_assignment_id;
elsif v_dt_delete_mode = 'DELETE' then
--
-- set the effective end date on element entry values.
-- DT_DELETE_MODE: DELETE
-- Step 8:
--
if g_debug then
hr_utility.set_location('hr_entry.del_3p_entry_values', 8);
update pay_element_entry_values_f eev
set eev.effective_end_date = p_session_date
where eev.element_entry_id = p_element_entry_id
and p_session_date between eev.effective_start_date
and eev.effective_end_date;
delete from pay_element_entry_values_f eev
where eev.element_entry_id = p_element_entry_id
and eev.effective_start_date >= p_validation_start_date;
elsif (v_dt_delete_mode = 'DELETE_NEXT_CHANGE'
or v_dt_delete_mode = 'FUTURE_CHANGE') then
--
-- delete element entry values between the validation start/end dates.
-- DT_DELETE_MODE: DELETE_NEXT_CHANGE/FUTURE_CHANGE
-- Step 9:
--
if g_debug then
hr_utility.set_location('hr_entry.del_3p_entry_values', 9);
delete from pay_element_entry_values_f eev
where eev.element_entry_id = p_element_entry_id
and (
(eev.effective_end_date between p_validation_start_date
and p_validation_end_date)
or (eev.effective_start_date between p_validation_start_date
and p_validation_end_date));
update pay_element_entry_values_f eev
-- bug 384948. Changed set clouse to supply effective_end_date of the
-- element entry as opposite to p_validation_end_date.
set eev.effective_end_date = (select effective_end_date
from pay_element_entries_f
where element_entry_id = p_element_entry_id
and effective_start_date = eev.effective_start_date)
where eev.element_entry_id = p_element_entry_id
and p_session_date between eev.effective_start_date
and eev.effective_end_date;
p_dt_update_mode varchar2,
p_num_entry_values number,
p_input_value_id_tbl hr_entry.number_table,
p_entry_value_tbl hr_entry.varchar2_table
) is
--
-- Local Variables
v_return_entry_id number;
and p_dt_update_mode is not null
and p_element_entry_id is not null
and p_element_type_id is not null
and p_element_link_id is not null
and p_entry_type is not null
and p_assignment_id is not null
and p_validation_start_date is not null
and p_validation_end_date is not null
and p_session_date is not null
and p_validation_start_date = trunc
(p_validation_start_date)
and p_validation_end_date = trunc
(p_validation_end_date)
and p_session_date = trunc (p_session_date));
if (p_dt_update_mode = 'CORRECTION' or -- DT Correction
p_processing_type = 'N' or -- Nonrecurring Entry
p_entry_type = 'D' or -- Additional
p_entry_type = 'S' or -- Override
p_entry_type = 'R' or -- Replacement Adjustment
p_entry_type = 'A') then -- Additive Adjustment
--
if p_num_entry_values > 0 then
--
-- NB. mandatory checks are not applied to adjustments.
if not p_entry_type in ('A','R') then
--
hr_entry.chk_mandatory_entry_values
(p_element_link_id,
p_session_date,
p_num_entry_values,
p_input_value_id_tbl,
p_entry_value_tbl);
update pay_element_entry_values_f eev
set eev.screen_entry_value = p_entry_value_tbl(v_loop)
where eev.element_entry_id = p_element_entry_id
and eev.input_value_id + 0 = p_input_value_id_tbl(v_loop)
and p_validation_start_date between eev.effective_start_date
and eev.effective_end_date;
UPDATE PAY_RUN_RESULT_VALUES PRRV1
SET PRRV1.RESULT_VALUE =
(SELECT PEEV1.SCREEN_ENTRY_VALUE
FROM PAY_ELEMENT_ENTRY_VALUES_F PEEV1
WHERE p_session_date
BETWEEN PEEV1.EFFECTIVE_START_DATE
AND PEEV1.EFFECTIVE_END_DATE
AND PEEV1.ELEMENT_ENTRY_ID = p_element_entry_id
AND PEEV1.INPUT_VALUE_ID + 0 = PRRV1.INPUT_VALUE_ID)
WHERE PRRV1.RUN_RESULT_ID =
(SELECT PRR1.RUN_RESULT_ID
FROM PAY_RUN_RESULTS PRR1
WHERE PRR1.SOURCE_ID = p_element_entry_id
AND PRR1.SOURCE_TYPE = 'E'
AND PRR1.STATUS = 'U'
AND PRR1.ELEMENT_TYPE_ID +0 = p_element_type_id);
if ((p_dt_update_mode = 'UPDATE' or
p_dt_update_mode = 'UPDATE_CHANGE_INSERT' or
p_dt_update_mode = 'UPDATE_OVERRIDE') and
p_processing_type = 'R') then
if g_debug then
hr_utility.set_location('hr_entry.upd_3p_entry_values', 4);
UPDATE PAY_ELEMENT_ENTRY_VALUES_F PEEV1
SET PEEV1.EFFECTIVE_END_DATE = p_validation_start_date - 1
WHERE PEEV1.ELEMENT_ENTRY_ID = p_element_entry_id
AND p_session_date
BETWEEN PEEV1.EFFECTIVE_START_DATE AND PEEV1.EFFECTIVE_END_DATE;
if p_dt_update_mode = 'UPDATE_OVERRIDE' then
if g_debug then
hr_utility.set_location('hr_entry.upd_3p_entry_values', 5);
DELETE FROM PAY_ELEMENT_ENTRY_VALUES_F PEEV1
WHERE PEEV1.ELEMENT_ENTRY_ID = p_element_entry_id
AND PEEV1.EFFECTIVE_START_DATE >= p_validation_start_date;
p_dt_update_mode varchar2,
p_num_entry_values number,
p_input_value_id_tbl hr_entry.number_table,
p_entry_value_tbl hr_entry.varchar2_table
) is
--
-- Local Variables
v_return_entry_id number;
p_dt_update_mode,
v_num_entry_values,
v_input_value_id_tbl,
v_entry_value_tbl);
p_dt_update_mode varchar2,
p_input_value_id1 number,
p_input_value_id2 number,
p_input_value_id3 number,
p_input_value_id4 number,
p_input_value_id5 number,
p_input_value_id6 number,
p_input_value_id7 number,
p_input_value_id8 number,
p_input_value_id9 number,
p_input_value_id10 number,
p_input_value_id11 number,
p_input_value_id12 number,
p_input_value_id13 number,
p_input_value_id14 number,
p_input_value_id15 number,
p_entry_value1 varchar2,
p_entry_value2 varchar2,
p_entry_value3 varchar2,
p_entry_value4 varchar2,
p_entry_value5 varchar2,
p_entry_value6 varchar2,
p_entry_value7 varchar2,
p_entry_value8 varchar2,
p_entry_value9 varchar2,
p_entry_value10 varchar2,
p_entry_value11 varchar2,
p_entry_value12 varchar2,
p_entry_value13 varchar2,
p_entry_value14 varchar2,
p_entry_value15 varchar2
) is
--
-- Local variables
v_num_entry_values number;
p_dt_update_mode,
v_num_entry_values,
v_input_value_id_tbl,
v_entry_value_tbl);
update pay_assignment_actions paa
set paa.action_status = 'M'
where paa.assignment_action_id =
(select aa.assignment_action_id
from pay_assignment_actions aa,
pay_payroll_actions pa,
per_assignments_f asg,
per_time_periods tim,
pay_payrolls_f pp
where pa.effective_date =
(select max(pa2.effective_date)
from pay_payroll_actions pa2,
pay_assignment_actions aa2
where aa2.assignment_id = p_assignment_id
and pa2.payroll_action_id = aa2.payroll_action_id)
and pa.action_sequence =
(select max(pa3.action_sequence)
from pay_payroll_actions pa3,
pay_assignment_actions aa3
where aa3.assignment_id = p_assignment_id
and pa3.payroll_action_id = aa3.payroll_action_id
and pa3.effective_date = pa.effective_date)
and not exists
(select null
from pay_payroll_actions pa4,
pay_assignment_actions aa4
where aa4.assignment_id = p_assignment_id
and aa4.payroll_action_id = pa4.payroll_action_id
and pa4.action_type = 'R'
and pa4.action_sequence < pa.action_sequence
and nvl(pa4.date_earned,pa4.effective_date) between
greatest(tim.start_date,p_effective_start_date) and
pa.effective_date)
and pa.action_type = 'R'
and aa.payroll_action_id = pa.payroll_action_id
and aa.action_status = 'C'
and aa.assignment_id = p_assignment_id
and asg.assignment_id = aa.assignment_id
and pp.payroll_id = asg.payroll_id
and tim.payroll_id = pp.payroll_id
and nvl(pa.date_earned,pa.effective_date)
between asg.effective_start_date
and asg.effective_end_date
and nvl(pa.date_earned,pa.effective_date)
between pp.effective_start_date
and pp.effective_end_date
and nvl(pa.date_earned,pa.effective_date)
between tim.start_date
and tim.end_date
and nvl(pa.date_earned,pa.effective_date)
between p_effective_start_date
and p_effective_end_date
and p_effective_start_date <= tim.end_date
and p_effective_end_date >= tim.start_date
and ((p_mode = 'ELEMENT_ENTRY'
and (pp.workload_shifting_level = 'E' or
pp.workload_shifting_level = 'A'))
or (p_mode = 'ASSIGNMENT'
and pp.workload_shifting_level = 'A')));
select /*+ INDEX(liv, pay_link_input_values_f_n2) */
iv.uom,
iv.hot_default_flag,
et.input_currency_code,
decode(iv.hot_default_flag,
'Y',nvl(liv.min_value,
iv.min_value)
,liv.min_value),
decode(iv.hot_default_flag,
'Y',nvl(liv.max_value,
iv.max_value)
,liv.max_value),
decode(iv.hot_default_flag,
'Y',nvl(liv.warning_or_error,
iv.warning_or_error)
,liv.warning_or_error)
into v_uom,
v_hot_default_flag,
v_input_currency_code,
v_minimum_value,
v_maximum_value,
v_warning_or_error
from pay_link_input_values_f liv,
pay_input_values_f iv,
pay_element_types_f et
where liv.element_link_id = p_element_link_id
and liv.input_value_id = p_input_value_id
and iv.input_value_id = liv.input_value_id
and et.element_type_id = iv.element_type_id
and p_session_date between liv.effective_start_date
and liv.effective_end_date
and p_session_date between iv.effective_start_date
and iv.effective_end_date
and p_session_date between et.effective_start_date
and et.effective_end_date;
select meaning
into v_message_text
from hr_lookups
where lookup_type = 'UNITS'
and lookup_code = v_uom;
select pca.cost_allocation_keyflex_id
from pay_cost_allocation_keyflex pca
where pca.cost_allocation_keyflex_id = p_cost_allocation_keyflex_id;
select substr(application_column_name,8,2)
from fnd_id_flex_segments_vl
where id_flex_code = 'COST'
and id_flex_num = p_cost_keyflex_structure
and application_id = 801
and enabled_flag = 'Y'
and display_flag = 'Y'
order by segment_num;
l_are_dynamic_inserts_allowed varchar2(2) := null;
select cost_allocation_keyflex_id
from pay_cost_allocation_keyflex c
where c.id_flex_num = :p_cost_keyflex_structure
and c.enabled_flag = ''Y''';
select 'Y' into l_are_dynamic_inserts_allowed
from fnd_id_flex_structures_vl
where id_flex_code = 'COST'
and id_flex_num = p_cost_keyflex_structure
and application_id = 801
and enabled_flag = 'Y'
and dynamic_inserts_allowed_flag = 'Y';
select pay_cost_allocation_keyflex_s.nextval
into l_cost_allocation_keyflex_id
from sys.dual;
select parameter_value
into v_cal_cost_segs
from pay_action_parameters
where parameter_name = 'COST_VAL_SEGS';
insert into pay_cost_allocation_keyflex
(cost_allocation_keyflex_id
,concatenated_segments
,id_flex_num
,last_update_date
,last_updated_by
,summary_flag
,enabled_flag
,start_date_active
,end_date_active
,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)
values
(l_cost_allocation_keyflex_id
,l_concatenated_segments
,p_cost_keyflex_structure
,null
,null
,p_summary_flag
,'Y'
,p_start_date_active
,p_end_date_active
,p_segment1
,p_segment2
,p_segment3
,p_segment4
,p_segment5
,p_segment6
,p_segment7
,p_segment8
,p_segment9
,p_segment10
,p_segment11
,p_segment12
,p_segment13
,p_segment14
,p_segment15
,p_segment16
,p_segment17
,p_segment18
,p_segment19
,p_segment20
,p_segment21
,p_segment22
,p_segment23
,p_segment24
,p_segment25
,p_segment26
,p_segment27
,p_segment28
,p_segment29
,p_segment30);
select a.payroll_id,
t.start_date,
t.end_date
into l_payroll_id,
l_start_date,
l_end_date
from per_time_periods t,
per_assignments_f a
where a.assignment_id = p_assignment_id
and p_session_date
between a.effective_start_date
and a.effective_end_date
and a.payroll_id is not null
and t.payroll_id = a.payroll_id
and p_session_date
between t.start_date
and t.end_date;
select max(ppr.run_result_id)
into l_run_result_id
from pay_run_results ppr,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_action_classifications pac
where ppr.source_id = p_element_entry_id
and ppr.source_type = 'E'
and ppr.entry_type = p_entry_type
and ppr.status <> 'U'
and ppr.element_type_id = p_element_type_id
and ppr.assignment_action_id = paa.assignment_action_id
and paa.assignment_id = p_assignment_id
and paa.action_status = 'C'
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.payroll_id = l_payroll_id
and pac.classification_name = 'QP_PAYRUN'
and ppa.action_type = pac.action_type
and ppa.effective_date
between l_start_date
and l_end_date;
select prr.status
into l_run_result_status
from pay_run_results prr
where prr.run_result_id = l_run_result_id;
select 'Y'
into l_overridden
from pay_element_entries_f pee
where p_session_date
between pee.effective_start_date
and pee.effective_end_date
and pee.entry_type = 'S'
and pee.assignment_id = p_assignment_id
and pee.element_link_id = p_element_link_id;
select 'Y'
into p_adjustment
from pay_element_entries_f pee
where p_session_date
between pee.effective_start_date
and pee.effective_end_date
and (pee.entry_type = 'R'
or pee.entry_type = 'A')
and pee.assignment_id = p_assignment_id
and pee.element_link_id = p_element_link_id
and pee.target_entry_id = p_element_entry_id;
p_dt_update_mode in varchar2,
p_dt_delete_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) is
-- --
--l_creator_meaning varchar2(80);
l_dt_update_mode varchar2(30) := nvl(p_dt_update_mode, 'CORRECTION');
l_dt_delete_mode varchar2(30) := nvl(p_dt_delete_mode, 'ZAP');
((p_dml_operation = 'DELETE' and
l_dt_delete_mode = 'ZAP') or
(p_dml_operation = 'UPDATE' and
l_dt_update_mode = 'CORRECTION')))) then
--
-- We must error because we cannot Update or Delete an entry which is for:
-- A: Absence
-- M: SMP
-- S: SSP
-- Q: QuickPay
-- UT: Us Tax
-- B: Balance Adjustment
-- SP: Salary Admin
--
if g_debug then
hr_utility.set_location('hr_entry.chk_creator_type', 5);
select h.meaning
into l_creator_meaning
from hr_lookups h
where h.lookup_type = 'CREATOR_TYPE'
and h.lookup_code = p_creator_type;
if (p_dml_operation = 'UPDATE') then
hr_utility.set_message(801, 'HR_7014_ELE_ENTRY_CREATOR_UPD');
(p_dml_operation = 'DELETE' or
(p_dml_operation = 'UPDATE' and
l_dt_update_mode = 'UPDATE_OVERRIDE'))) or
(p_creator_type = 'SP' and
((p_dml_operation = 'DELETE' and
l_dt_delete_mode <> 'ZAP') or
(p_dml_operation = 'UPDATE' and
l_dt_update_mode = 'UPDATE_OVERRIDE')))) then
--
if g_debug then
hr_utility.set_location('hr_entry.chk_creator_type', 10);
select 'Y'
into l_error_flag
from sys.dual
where exists
(select 1
from pay_element_entries_f pee
where pee.element_entry_id = p_element_entry_id
and pee.creator_type = 'SP'
and pee.effective_start_date >= p_validation_start_date);
select rowid,
dep.*
from ben_covered_dependents_f DEP
where p_element_entry_EED between dep.effective_start_date and dep.effective_end_date
and dep.element_entry_id = p_element_entry_id
and not exists ( select null from ben_covered_dependents_f DEP2
where dep2.element_entry_id = p_new_element_entry_id
and dep2.contact_relationship_id = dep.contact_relationship_id
and dep2.effective_start_date between
p_new_element_entry_ESD and p_new_element_entry_EED);
update ben_covered_dependents_f set effective_end_date = p_element_entry_EED
where element_entry_id = p_element_entry_id;
update ben_covered_dependents_f set element_entry_id = p_new_element_entry_id
where element_entry_id = p_element_entry_id;
ben_covered_dependents_pkg.update_row (
--
p_covered_dependent_id => dependant.covered_dependent_id,
p_rowid => dependant.rowid,
p_contact_relationship_id => dependant.contact_relationship_id,
p_element_entry_id => dependant.element_entry_id,
p_effective_start_date => v_start_date,
p_effective_end_date => v_end_date);
ben_covered_dependents_pkg.insert_row (
--
p_covered_dependent_id => l_covered_dependent_id,
p_rowid => l_rowid,
p_contact_relationship_id => dependant.contact_relationship_id,
p_element_entry_id => p_new_element_entry_id,
p_effective_start_date => p_new_element_entry_ESD,
p_effective_end_date => p_new_element_entry_EED);
select rowid,
ben.*
from ben_beneficiaries_f BEN
where p_element_entry_EED between ben.effective_start_date and ben.effective_end_date
and ben.element_entry_id = p_element_entry_id
and not exists ( select null from ben_beneficiaries_f BEN2
where ben2.element_entry_id = p_new_element_entry_id
and ben2.source_id = ben.source_id
and ben2.effective_start_date between
p_new_element_entry_ESD and p_new_element_entry_EED);
update ben_beneficiaries_f set effective_end_date = p_element_entry_EED
where element_entry_id = p_element_entry_id;
update ben_beneficiaries_f set element_entry_id = p_new_element_entry_id
where element_entry_id = p_element_entry_id;
ben_beneficiaries_pkg.update_row (
--
p_rowid => beneficiary.rowid,
p_source_type => beneficiary.source_type,
p_source_id => beneficiary.source_id,
p_element_entry_id => beneficiary.element_entry_id,
p_benefit_level => beneficiary.benefit_level,
p_proportion => beneficiary.proportion,
p_beneficiary_id => beneficiary.beneficiary_id,
p_effective_start_date => v_start_date,
p_effective_end_date => v_end_date);
ben_beneficiaries_pkg.insert_row (
--
p_rowid => l_rowid,
p_beneficiary_id => l_beneficiary_id,
p_source_type => beneficiary.source_type,
p_source_id => beneficiary.source_id,
p_element_entry_id => p_new_element_entry_id,
p_benefit_level => beneficiary.benefit_level,
p_proportion => beneficiary.proportion,
p_effective_start_date => p_new_element_entry_ESD,
p_effective_end_date => p_new_element_entry_EED);