[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
rrsel varchar(1000); -- select list for range row select.
retasactsel varchar(1000); -- select list for Retropay assignment action insert.
retpgasactsel varchar(1000); -- select list for Retropay assignment action insert.
ordrrsel varchar(1000); -- select list for range row select with ORDERED hint.
prrsel varchar(100); -- select list for Purge range row select.
brrsel varchar(1000); -- select list for range row select for BEE.
asactsel varchar(1000); -- select list for assignment action insert.
orgsel varchar(1000); -- select list for range rows for Organisations.
runasactsel varchar(1000); -- select list for Run assignment action insert.
runasactsel_rev varchar(1000); -- select list for Run assignment action insert for Batch Reversal.Bug 8725368.
puractsel varchar(1000); -- select list for Purge assignment action insert.
beeactsel varchar(1000); -- select list for assignment action insert.
revallasg varchar(3000); -- select list for reversal asg action insert.
fupdate varchar(1000); -- for update clause.
update_recurring_ee
NOTES
This function performs the actual database work of updating
a REE's input value as a result of an Update Formula Result Rule.
*/
procedure update_recurring_ee
(
p_element_entry_id in out nocopy number,
p_error_code in out nocopy number,
p_assignment_action_id in number,
p_assignment_id in number,
p_effective_date in date,
p_element_type_id in number,
p_input_value_id in number,
p_updated_value in varchar2
) is
-- Setup entry values cursor.
cursor get_entry_values (p_update_ee_id in number,
p_date in date) is
select input_value_id, screen_entry_value
from pay_element_entry_values_f eev
where eev.element_entry_id = p_update_ee_id
and p_date between eev.effective_start_date
and eev.effective_end_date;
cursor upd_entry_values (p_update_ee_id in number,
p_date in date) is
select eev.element_entry_value_id,
eev.input_value_id,
eev.element_entry_id,
eev.screen_entry_value
from pay_element_entry_values_f eev
where eev.element_entry_id = p_update_ee_id
and (p_date - 1) between
eev.effective_start_date and eev.effective_end_date;
cursor entry_record_exists(p_update_ee_id in number,
p_effective_end_date in date) is
select effective_end_date
from pay_element_entries_f
where element_entry_id = p_update_ee_id
and effective_start_date = p_effective_date
and effective_end_date = p_effective_end_date;
cursor entry_value_exists(p_update_ee_id in number,
p_input_value_id in number,
p_effective_end_date in date) is
select effective_end_date,screen_entry_value
from pay_element_entry_values_f
where element_entry_id = p_update_ee_id
and input_value_id = p_input_value_id
and effective_start_date = p_effective_date
and effective_end_date = p_effective_end_date;
c_indent constant varchar2(30) := 'pydynsql.update_recurring_ee';
update_ee_id number;
select pee.element_entry_id,
pee.updating_action_id,
pee.effective_start_date,
asg.assignment_number,
pel.element_link_id,
piv.lookup_type,
piv.uom,
pet.input_currency_code
into update_ee_id,
upd_act_id,
ee_effstart,
asgno,
link_id,
lookup_type,
uom,
input_curr
from pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet,
pay_input_values_f piv,
per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and p_effective_date between
asg.effective_start_date and asg.effective_end_date
and pel.element_type_id = p_element_type_id
and (pel.payroll_id = asg.payroll_id
or pel.payroll_id is null)
and p_effective_date between
pel.effective_start_date and pel.effective_end_date
and pee.element_link_id = pel.element_link_id
and pee.assignment_id = asg.assignment_id
and pee.entry_type = 'E'
and p_effective_date between
pee.effective_start_date and pee.effective_end_date
and (pee.element_entry_id = p_element_entry_id
or p_element_entry_id is null)
and pet.element_type_id = pel.element_type_id
and p_effective_date between
pet.effective_start_date and pet.effective_end_date
and piv.input_value_id = p_input_value_id
and p_effective_date between
piv.effective_start_date and piv.effective_end_date;
-- In this case no need of UPDATE for this element entry. we can skip UPDATE operation.
--p_error_code := 7328;
screen_value := hr_chkfmt.changeformat(p_updated_value, uom, input_curr);
select eev.screen_entry_value
into old_value
from pay_element_entry_values eev
where eev.element_entry_id = update_ee_id
and eev.input_value_id = p_input_value_id
and p_effective_date between
eev.effective_start_date and eev.effective_end_date;
select max(pee.effective_end_date)
into max_effend
from pay_element_entries_f pee
where pee.element_entry_id = update_ee_id;
-- to update. The new record being created should have the same
-- end date.
SELECT effective_end_date
INTO l_effective_end_date
FROM pay_element_entries_f
WHERE element_entry_id = update_ee_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
delete from pay_element_entries_f pee
where pee.element_entry_id = update_ee_id
and pee.effective_start_date > val_date;
open get_entry_values(update_ee_id, p_effective_date);
OPEN entry_record_exists(update_ee_id,l_effective_end_date);
-- Now, update the effective_end_date of existing entry.
-- Note : using val_date.
hr_utility.set_location(c_indent,40);
update pay_element_entries_f pee
set pee.effective_end_date = (p_effective_date - 1)
where pee.element_entry_id = update_ee_id
and val_date between
pee.effective_start_date and pee.effective_end_date;
update_ee_id
);
insert into pay_element_entries_f (
element_entry_id,
effective_start_date,
effective_end_date,
cost_allocation_keyflex_id,
assignment_id,
updating_action_id,
updating_action_type,
element_link_id,
element_type_id,
original_entry_id,
creator_type,
entry_type,
comment_id,
creator_id,
reason,
target_entry_id,
subpriority,
personal_payment_method_id,
all_entry_values_null,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
entry_information_category,
entry_information1,
entry_information2,
entry_information3,
entry_information4,
entry_information5,
entry_information6,
entry_information7,
entry_information8,
entry_information9,
entry_information10,
entry_information11,
entry_information12,
entry_information13,
entry_information14,
entry_information15,
entry_information16,
entry_information17,
entry_information18,
entry_information19,
entry_information20,
entry_information21,
entry_information22,
entry_information23,
entry_information24,
entry_information25,
entry_information26,
entry_information27,
entry_information28,
entry_information29,
entry_information30,
object_version_number,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
select pee.element_entry_id,
p_effective_date,
l_effective_end_date,
pee.cost_allocation_keyflex_id,
pee.assignment_id,
p_assignment_action_id,
'U',
pee.element_link_id,
pee.element_type_id,
pee.original_entry_id,
pee.creator_type,
pee.entry_type,
pee.comment_id,
pee.creator_id,
pee.reason,
pee.target_entry_id,
pee.subpriority,
pee.personal_payment_method_id,
l_all_entry_values_null,
pee.attribute_category,
pee.attribute1,
pee.attribute2,
pee.attribute3,
pee.attribute4,
pee.attribute5,
pee.attribute6,
pee.attribute7,
pee.attribute8,
pee.attribute9,
pee.attribute10,
pee.attribute11,
pee.attribute12,
pee.attribute13,
pee.attribute14,
pee.attribute15,
pee.attribute16,
pee.attribute17,
pee.attribute18,
pee.attribute19,
pee.attribute20,
entry_information_category,
entry_information1,
entry_information2,
entry_information3,
entry_information4,
entry_information5,
entry_information6,
entry_information7,
entry_information8,
entry_information9,
entry_information10,
entry_information11,
entry_information12,
entry_information13,
entry_information14,
entry_information15,
entry_information16,
entry_information17,
entry_information18,
entry_information19,
entry_information20,
entry_information21,
entry_information22,
entry_information23,
entry_information24,
entry_information25,
entry_information26,
entry_information27,
entry_information28,
entry_information29,
entry_information30,
ovn,
trunc(sysdate),
0,
0,
pee.created_by,
pee.creation_date
from pay_element_entries_f pee
where pee.element_entry_id = update_ee_id
and (p_effective_date - 1) between
pee.effective_start_date and pee.effective_end_date;
for entry_value in get_entry_values(update_ee_id, p_effective_date) loop
entry_val_list(entry_value.input_value_id) :=
entry_value.screen_entry_value;
OPEN entry_value_exists(update_ee_id,p_input_value_id,l_effective_end_date);
delete from pay_element_entry_values_f eev
where eev.element_entry_id = update_ee_id
and eev.effective_start_date > val_date;
update pay_element_entry_values_f eev
set eev.effective_end_date = (p_effective_date - 1)
where eev.element_entry_id = update_ee_id
and val_date between
eev.effective_start_date and eev.effective_end_date;
for update_values in upd_entry_values(update_ee_id, p_effective_date) loop
-- Enhancement 3478848
-- Removed this, this check is now performed when the entry
-- values are initially fetched, above.
/*
if update_values.input_value_id = p_input_value_id then
scr_upd_value := db_value;
scr_upd_value := entry_val_list(update_values.input_value_id);
insert into pay_element_entry_values (
element_entry_value_id,
effective_start_date,
effective_end_date,
input_value_id,
element_entry_id,
screen_entry_value)
values (update_values.element_entry_value_id,
p_effective_date,
l_effective_end_date,
update_values.input_value_id,
update_values.element_entry_id,
-- Enhancement 3478848
-- entry_val_list now contains the correct entry values
decode(trim(entry_val_list(update_values.input_value_id)), NULL, NULL, entry_val_list(update_values.input_value_id))); -- bug 8482621
update pay_element_entry_values_f eev
set screen_entry_value = decode(trim(entry_val_list(p_input_value_id)), NULL, NULL, entry_val_list(p_input_value_id)) -- bug 7340357
where eev.element_entry_id = update_ee_id
and input_value_id = p_input_value_id
and p_effective_date between eev.effective_start_date and eev.effective_end_date;
insert into pay_element_entry_values (
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,
p_effective_date,
max_effend,
eev.input_value_id,
eev.element_entry_id,
decode(eev.input_value_id, p_input_value_id,
db_value, eev.screen_entry_value)
from pay_element_entry_values_f eev
where eev.element_entry_id = update_ee_id
and (p_effective_date - 1) between
eev.effective_start_date and eev.effective_end_date;
p_element_entry_id := update_ee_id;
end update_recurring_ee;
select pee.element_entry_id,
pel.element_link_id,
pee.effective_start_date
into stop_ee_id, link_id, stop_ee_start_date
from pay_element_entries_f pee,
pay_element_links_f pel,
per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and p_date_earned between
asg.effective_start_date and asg.effective_end_date
and pel.element_type_id = p_element_type_id
and (pel.payroll_id = asg.payroll_id
or pel.payroll_id is null)
and p_date_earned between
pel.effective_start_date and pel.effective_end_date
and pee.element_link_id = pel.element_link_id
and pee.assignment_id = asg.assignment_id
and pee.entry_type = 'E'
and (pee.element_entry_id = p_element_entry_id
or p_element_entry_id is null)
and p_date_earned between
pee.effective_start_date and pee.effective_end_date;
select 'Y'
into v_error_flag
from sys.dual
where exists (
select null
from pay_element_entries_f pee
where pee.assignment_id = p_assignment_id
and pee.element_link_id = link_id
and pee.target_entry_id = stop_ee_id
and pee.effective_start_date <= c_eot
and pee.effective_end_date >= (p_date_earned + 1));
delete from pay_element_entries_f pee
where pee.element_entry_id = stop_ee_id
and pee.effective_start_date > p_date_earned;
update pay_element_entries_f pee
set pee.effective_end_date = p_date_earned,
pee.prev_upd_action_id = DECODE(pee.updating_action_type, 'U', pee.updating_action_id),
pee.updating_action_id = p_assignment_action_id,
pee.updating_action_type = 'S'
where pee.element_entry_id = stop_ee_id
and p_date_earned between
pee.effective_start_date and pee.effective_end_date;
delete from pay_element_entry_values_f eev
where eev.element_entry_id = stop_ee_id
and eev.effective_start_date > p_date_earned;
update pay_element_entry_values_f eev
set eev.effective_end_date = p_date_earned
where eev.element_entry_id = stop_ee_id
and p_date_earned between
eev.effective_start_date and eev.effective_end_date;
dummy number; -- dummy cos selects need something to select into.
select has.payroll_id,
nvl(has.formula_id,0)
into payid,
formula
from hr_assignment_sets has
where has.assignment_set_id = asetid;
select null
into dummy
from sys.dual
where exists (
select null
from hr_assignment_set_amendments amd
where amd.assignment_set_id = asetid
and amd.include_or_exclude = 'I');
select null
into dummy
from sys.dual
where exists (
select null
from hr_assignment_set_amendments amd
where amd.assignment_set_id = asetid
and amd.include_or_exclude = 'E');
select distinct paf.assignment_id
from per_all_assignments_f paf
where paf.period_of_service_id = p_per_of_serv;
select 1 res
from sys.dual
where exists (
select /*+ index(AC2 PAY_ASSIGNMENT_ACTIONS_N51) */ null
from pay_action_classifications acl,
pay_assignment_actions ac2,
pay_payroll_actions pa2
where ac2.assignment_id = p_asg_id
and pa2.payroll_action_id = ac2.payroll_action_id
and acl.classification_name = 'SEQUENCED'
and pa2.action_type = acl.action_type
and (pa2.effective_date > p_eff_date
or (ac2.action_status not in ('C', 'S')
and pa2.effective_date <= p_eff_date)));
select distinct paf.assignment_id
from per_all_assignments_f paf
where paf.period_of_service_id = p_per_of_serv;
select 1 res
from sys.dual
where exists (
select null
from pay_action_classifications acl,
pay_assignment_actions ac2,
pay_payroll_actions pa2
where ac2.assignment_id = p_asg_id
and pa2.payroll_action_id = ac2.payroll_action_id
and acl.classification_name = 'SEQUENCED'
and pa2.action_type = acl.action_type
and ac2.action_status not in ('C', 'S'));
select distinct paf.assignment_id
from per_all_assignments_f paf
where paf.period_of_service_id = p_per_of_serv;
select 1 res
from sys.dual
where exists (
select null
from pay_action_classifications acl,
pay_assignment_actions ac2,
pay_payroll_actions pa2
where ac2.assignment_id = p_asg_id
and pa2.payroll_action_id = ac2.payroll_action_id
and acl.classification_name = 'SEQUENCED'
and pa2.action_type = acl.action_type
and ((pa2.effective_date > p_eff_date
and ac2.action_status in ('C', 'S'))
or (ac2.action_status not in ('C', 'S')
and pa2.effective_date <= p_eff_date)));
select distinct pog_grp.source_id
from pay_object_groups pog_act,
pay_object_groups pog_grp
where pog_act.source_id = p_asg_id
and pog_act.source_type = 'PAF'
and pog_act.parent_object_group_id = pog_grp.parent_object_group_id -- the personlevel group
and pog_grp.source_type = 'PAF';
select 1 res
from sys.dual
where exists (
select null
from pay_action_classifications acl,
pay_assignment_actions ac2,
pay_payroll_actions pa2
where ac2.assignment_id = p_asg_id
and pa2.payroll_action_id = ac2.payroll_action_id
and acl.classification_name = 'SEQUENCED'
and pa2.action_type = acl.action_type
and (pa2.effective_date > p_eff_date
or (ac2.action_status not in ('C', 'S')
and pa2.effective_date <= p_eff_date)));
select 1 res
from sys.dual
where exists (
select null
from pay_action_classifications acl,
pay_assignment_actions ac2,
pay_payroll_actions pa2
where ac2.assignment_id = p_asg_id
and pa2.payroll_action_id = ac2.payroll_action_id
and acl.classification_name = 'SEQUENCED'
and pa2.action_type = acl.action_type
and ac2.action_status not in ('C', 'S'));
select 1 res
from sys.dual
where exists (
select null
from pay_action_classifications acl,
pay_assignment_actions ac2,
pay_payroll_actions pa2
where ac2.assignment_id = p_asg_id
and pa2.payroll_action_id = ac2.payroll_action_id
and acl.classification_name = 'SEQUENCED'
and pa2.action_type = acl.action_type
and ((pa2.effective_date > p_eff_date
and ac2.action_status in ('C', 'S'))
or (ac2.action_status not in ('C', 'S')
and pa2.effective_date <= p_eff_date)));
select '|| case chkno when null then '' else l_hints end ||' act.assignment_id,
act.assignment_action_id
from hr_assignment_sets has,
pay_population_ranges pop,
per_all_assignments_f pay_asg,
pay_payroll_actions pac,
pay_assignment_actions act
where pac.payroll_action_id = :pactid
and act.payroll_action_id = pac.payroll_action_id
and act.source_action_id is null
and pay_asg.assignment_id = act.assignment_id
and ((pac.action_type = ''BEE''
and pay_asg.effective_start_date = (select max(asg2.effective_start_date)
from per_all_assignments_f asg2
where asg2.assignment_id =
pay_asg.assignment_id))
or
(pac.action_type <> ''BEE''
and pac.effective_date between
pay_asg.effective_start_date and pay_asg.effective_end_date))
and pop.rowid = :chunk_rowid
and has.assignment_set_id = :asetid';
select '|| case chkno when null then '' else l_hints end ||' act.assignment_id,
act.assignment_action_id
from pay_payroll_actions pac,
pay_population_ranges pop,
hr_assignment_set_amendments amd,
per_all_assignments_f pay_asg,
pay_assignment_actions act
where pac.payroll_action_id = :pactid
and act.payroll_action_id = pac.payroll_action_id
and act.source_action_id is null
and pay_asg.assignment_id = act.assignment_id
and ((pac.action_type = ''BEE''
and pay_asg.effective_start_date = (select max(asg2.effective_start_date)
from per_all_assignments_f asg2
where asg2.assignment_id =
pay_asg.assignment_id))
or
(pac.action_type <> ''BEE''
and pac.effective_date between
pay_asg.effective_start_date and pay_asg.effective_end_date))
and pop.rowid = :chunk_rowid
and amd.assignment_set_id = :asetid
and amd.include_or_exclude = ''I''
and pay_asg.assignment_id = amd.assignment_id';
select null
from hr_assignment_set_amendments exc
where exc.assignment_set_id = has.assignment_set_id
and exc.include_or_exclude = ''E''
and act.assignment_id = exc.assignment_id)';
select pay_asg.assignment_id
from per_all_assignments_f pay_asg,
hr_assignment_sets has
where has.assignment_set_id = :v_asg_set
and pay_asg.payroll_id = has.payroll_id
and fnd_date.canonical_to_date(:v_effective_date) between
pay_asg.effective_start_date and pay_asg.effective_end_date';
select pay_asg.assignment_id
from per_all_assignments_f pay_asg,
hr_assignment_sets has,
hr_assignment_set_amendments amd
where has.assignment_set_id = :asetid
and amd.assignment_set_id = has.assignment_set_id
and pay_asg.payroll_id + 0 = has.payroll_id
and pay_asg.assignment_id = amd.assignment_id
and amd.include_or_exclude = ''I''
and fnd_date.canonical_to_date(:v_effective_date) between
pay_asg.effective_start_date and pay_asg.effective_end_date';
select pay_asg.assignment_id
from per_all_assignments_f pay_asg,
hr_assignment_sets has
where has.assignment_set_id = :asetid
and pay_asg.payroll_id = has.payroll_id
and fnd_date.canonical_to_date(:v_effective_date) between
pay_asg.effective_start_date and pay_asg.effective_end_date
and not exists (
select null
from hr_assignment_set_amendments amd
where amd.assignment_set_id = has.assignment_set_id
and pay_asg.assignment_id = amd.assignment_id
and amd.include_or_exclude = ''E'')';
select pay_asg.assignment_id, pay_asg.assignment_number,
pay_asg.payroll_id, pesm.element_type_id, petf.element_name
from per_all_assignments_f pay_asg,
hr_assignment_sets has,
PAY_ELEMENT_SET_MEMBERS pesm,
pay_element_types_f petf
where pay_asg.business_group_id = :p_bgid
and has.assignment_set_id = :pasetid
and pay_asg.assignment_type = ''E''
and fnd_date.canonical_to_date(:p_effective_date)
between pay_asg.effective_start_date
and pay_asg.effective_end_date
and pesm.element_set_id = :p_elesetid
and petf.element_type_id = pesm.element_type_id
and fnd_date.canonical_to_date(:p_effective_date) between
petf.effective_start_date and petf.effective_end_date
and ((petf.business_group_id is null and petf.legislation_code is null) or
(petf.business_group_id is null and petf.legislation_code = :p_legcode) or
(petf.business_group_id = :p_bgid))
and (exists
(select null
from pay_restriction_values psv
where psv.restriction_code = ''ELEMENT_TYPE''
and psv.customized_restriction_id = :p_restrictid
and (psv.value = ''BOTH'' or psv.value = petf.processing_type))
or not exists
(select null
from pay_restriction_values psv
where psv.restriction_code = ''ELEMENT_TYPE''
and psv.customized_restriction_id = :p_restrictid))';
select pay_asg.assignment_id, pay_asg.assignment_number,
pay_asg.payroll_id,petf.element_type_id, petf.element_name
from per_all_assignments_f pay_asg,
hr_assignment_sets has,
pay_element_types_f petf
where pay_asg.business_group_id = :p_bgid
and has.assignment_set_id = :pasetid
and pay_asg.assignment_type = ''E''
and fnd_date.canonical_to_date(:p_effective_date)
between petf.effective_start_date
and petf.effective_end_date
and petf.element_type_id = :p_element_id
and fnd_date.canonical_to_date(:p_effective_date)
between pay_asg.effective_start_date
and pay_asg.effective_end_date';
select pay_asg.assignment_id, pay_asg.assignment_number,
pay_asg.payroll_id, pesm.element_type_id, petf.element_name
from per_all_assignments_f pay_asg,
hr_assignment_sets has,
hr_assignment_set_amendments amd,
PAY_ELEMENT_SET_MEMBERS pesm,
pay_element_types_f petf
where pay_asg.business_group_id = :p_bgid
and has.assignment_set_id = :pasetid
and amd.assignment_set_id = has.assignment_set_id
and pay_asg.assignment_id = amd.assignment_id
and pay_asg.assignment_type = ''E''
and amd.include_or_exclude = ''I''
and fnd_date.canonical_to_date(:p_effective_date) between
pay_asg.effective_start_date and pay_asg.effective_end_date
and pesm.element_set_id = :p_elesetid
and petf.element_type_id = pesm.element_type_id
and fnd_date.canonical_to_date(:p_effective_date) between
petf.effective_start_date and petf.effective_end_date
and ((petf.business_group_id is null and petf.legislation_code is null) or
(petf.business_group_id is null and petf.legislation_code = :p_legcode) or
(petf.business_group_id = :p_bgid))
and (exists
(select null
from pay_restriction_values psv
where psv.restriction_code = ''ELEMENT_TYPE''
and psv.customized_restriction_id = :p_restrictid
and (psv.value = ''BOTH'' or psv.value = petf.processing_type))
or not exists
(select null
from pay_restriction_values psv
where psv.restriction_code = ''ELEMENT_TYPE''
and psv.customized_restriction_id = :p_restrictid))';
select pay_asg.assignment_id, pay_asg.assignment_number,
pay_asg.payroll_id,petf.element_type_id, petf.element_name
from per_all_assignments_f pay_asg,
hr_assignment_sets has,
hr_assignment_set_amendments amd,
pay_element_types_f petf
where pay_asg.business_group_id = :p_bgid
and has.assignment_set_id = :pasetid
and amd.assignment_set_id = has.assignment_set_id
and pay_asg.assignment_id = amd.assignment_id
and pay_asg.assignment_type = ''E''
and amd.include_or_exclude = ''I''
and fnd_date.canonical_to_date(:p_effective_date)
between petf.effective_start_date
and petf.effective_end_date
and petf.element_type_id = :p_element_id
and fnd_date.canonical_to_date(:p_effective_date) between
pay_asg.effective_start_date and pay_asg.effective_end_date';
select pay_asg.assignment_id, pay_asg.assignment_number,
pay_asg.payroll_id, pesm.element_type_id, petf.element_name
from per_all_assignments_f pay_asg,
hr_assignment_sets has,
PAY_ELEMENT_SET_MEMBERS pesm,
pay_element_types_f petf
where pay_asg.business_group_id = :p_bgid
and has.assignment_set_id = :pasetid
and pay_asg.assignment_type = ''E''
and fnd_date.canonical_to_date(:p_effective_date) between
pay_asg.effective_start_date and pay_asg.effective_end_date
and not exists (
select null
from hr_assignment_set_amendments amd
where amd.assignment_set_id = has.assignment_set_id
and pay_asg.assignment_id = amd.assignment_id
and amd.include_or_exclude = ''E'')
and pesm.element_set_id = :p_elesetid
and petf.element_type_id = pesm.element_type_id
and fnd_date.canonical_to_date(:p_effective_date) between
petf.effective_start_date and petf.effective_end_date
and ((petf.business_group_id is null and petf.legislation_code is null) or
(petf.business_group_id is null and petf.legislation_code = :p_legcode) or
(petf.business_group_id = :p_bgid))
and (exists
(select null
from pay_restriction_values psv
where psv.restriction_code = ''ELEMENT_TYPE''
and psv.customized_restriction_id = :p_restrictid
and (psv.value = ''BOTH'' or psv.value = petf.processing_type))
or not exists
(select null
from pay_restriction_values psv
where psv.restriction_code = ''ELEMENT_TYPE''
and psv.customized_restriction_id = :p_restrictid))';
select pay_asg.assignment_id, pay_asg.assignment_number,
pay_asg.payroll_id,petf.element_type_id, petf.element_name
from per_all_assignments_f pay_asg,
hr_assignment_sets has,
pay_element_types_f petf
where pay_asg.business_group_id = :p_bgid
and has.assignment_set_id = :pasetid
and pay_asg.assignment_type = ''E''
and fnd_date.canonical_to_date(:p_effective_date)
between petf.effective_start_date
and petf.effective_end_date
and petf.element_type_id = :p_element_id
and fnd_date.canonical_to_date(:p_effective_date) between
pay_asg.effective_start_date and pay_asg.effective_end_date
and not exists (
select null
from hr_assignment_set_amendments amd
where amd.assignment_set_id = has.assignment_set_id
and pay_asg.assignment_id = amd.assignment_id
and amd.include_or_exclude = ''E'')';
select pay_asg.assignment_id, pay_asg.assignment_number,
pay_asg.payroll_id, pesm.element_type_id, petf.element_name
from per_all_assignments_f pay_asg,
PAY_ELEMENT_SET_MEMBERS pesm,
pay_element_types_f petf
where pay_asg.business_group_id = :p_bgid
and pay_asg.assignment_type = ''E''
and fnd_date.canonical_to_date(:p_effective_date) between
pay_asg.effective_start_date and pay_asg.effective_end_date
and pesm.element_set_id = :p_elesetid
and petf.element_type_id = pesm.element_type_id
and fnd_date.canonical_to_date(:p_effective_date) between
petf.effective_start_date and petf.effective_end_date
and ((petf.business_group_id is null and petf.legislation_code is null) or
(petf.business_group_id is null and petf.legislation_code = :p_legcode) or
(petf.business_group_id = :p_bgid))
and (exists
(select null
from pay_restriction_values psv
where psv.restriction_code = ''ELEMENT_TYPE''
and psv.customized_restriction_id = :p_restrictid
and (psv.value = ''BOTH'' or psv.value = petf.processing_type))
or not exists
(select null
from pay_restriction_values psv
where psv.restriction_code = ''ELEMENT_TYPE''
and psv.customized_restriction_id = :p_restrictid))';
select pay_asg.assignment_id, pay_asg.assignment_number,
pay_asg.payroll_id,petf.element_type_id, petf.element_name
from per_all_assignments_f pay_asg,
pay_element_types_f petf
where pay_asg.business_group_id = :p_bgid
and pay_asg.assignment_type = ''E''
and fnd_date.canonical_to_date(:p_effective_date)
between petf.effective_start_date
and petf.effective_end_date
and petf.element_type_id = :p_element_id
and fnd_date.canonical_to_date(:p_effective_date) between
pay_asg.effective_start_date and pay_asg.effective_end_date';
select pay_asg.assignment_id,
pay_asg.payroll_id
from per_all_assignments_f pay_asg,
hr_assignment_sets has,
per_quickpaint_invocations inv
where inv.qp_invocation_id = :qp_invocation_id
and has.assignment_set_id = inv.invocation_context
and pay_asg.business_group_id = has.business_group_id
and inv.effective_date between
pay_asg.effective_start_date and pay_asg.effective_end_date';
select pay_asg.assignment_id,
pay_asg.payroll_id
from per_all_assignments_f pay_asg,
hr_assignment_sets has,
hr_assignment_set_amendments amd,
per_quickpaint_invocations inv
where inv.qp_invocation_id = :qp_invocation_id
and has.assignment_set_id = inv.invocation_context
and amd.assignment_set_id = has.assignment_set_id
and amd.include_or_exclude = ''I''
and pay_asg.assignment_id = amd.assignment_id
and pay_asg.business_group_id + 0 = has.business_group_id + 0
and inv.effective_date between
pay_asg.effective_start_date and pay_asg.effective_end_date';
select null
from hr_assignment_set_amendments amd
where amd.assignment_set_id = has.assignment_set_id
and amd.include_or_exclude = ''E''
and pay_asg.assignment_id = amd.assignment_id)';
appropreate code that defines the select statement for the
population ranges.
NOTES
*/
procedure archive_range(pactid in number,
sqlstr in out nocopy varchar2
)
is
sql_cur number;
select range_code
into range_proc
from pay_report_format_mappings_f prfm,
pay_payroll_actions ppa
where ppa.payroll_action_id = pactid
and ppa.report_type = prfm.report_type
and ppa.report_qualifier = prfm.report_qualifier
and ppa.report_category = prfm.report_category
and ppa.effective_date between prfm.effective_start_date
and prfm.effective_end_date;
select /*+ INDEX(paf PER_ASSIGNMENTS_F_PK)*/
pbg.legislation_code
into l_legislation
from per_all_assignments_f paf,
per_business_groups_perf pbg
where paf.assignment_id = p_assignment_id
and p_effective_date between paf.effective_start_date
and paf.effective_end_date
and paf.business_group_id = pbg.business_group_id;
select /*+ INDEX(paf PER_ASSIGNMENTS_F_PK)*/
pbg.legislation_code,
pbg.business_group_id
into l_legislation,
l_business_group_id
from per_all_assignments_f paf,
per_business_groups_perf pbg
where paf.assignment_id = p_assignment_id
and p_effective_date between paf.effective_start_date
and paf.effective_end_date
and paf.business_group_id = pbg.business_group_id;
select 1
into l_dummy
from dual
where exists (
select ''
from hr_organization_units hou,
hr_organization_information houi
where hou.organization_id = houi.organization_id
and hou.organization_id = l_local_unit_id
and houi.org_information_context = 'CLASS'
and houi.org_information1 =
upper(l_legislation||'_LOCAL_UNIT')
and hou.business_group_id = l_business_group_id
);
select to_number(SCL.segment1) tax_unit_id
from per_all_assignments_f ASG
,hr_soft_coding_keyflex SCL
where ASG.assignment_id = p_assignment_id
and SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
and p_effective_date between ASG.effective_start_date
and ASG.effective_end_date;
select establishment_id
from per_all_assignments_f ASG
where ASG.assignment_id = p_assignment_id
and p_effective_date between ASG.effective_start_date
and ASG.effective_end_date;
select /*+ INDEX(paf PER_ASSIGNMENTS_F_PK)*/
pbg.legislation_code
into l_legislation
from per_all_assignments_f paf,
per_business_groups_perf pbg
where paf.assignment_id = p_assignment_id
and p_effective_date between paf.effective_start_date
and paf.effective_end_date
and paf.business_group_id = pbg.business_group_id;
select /*+ INDEX(paf PER_ASSIGNMENTS_F_PK)*/
pbg.legislation_code
into l_legislation
from per_all_assignments_f paf,
per_business_groups_perf pbg
where paf.assignment_id = p_assignment_id
and p_effective_date between paf.effective_start_date
and paf.effective_end_date
and paf.business_group_id = pbg.business_group_id;
sqlstr := sqlstr || fupdate;
sqlstr := sqlstr || fupdate;
sqlstr := sqlstr || fupdate;
sqlstr := sqlstr || fupdate;
sqlstr := sqlstr || fupdate;
sqlstr := sqlstr || fupdate;
sqlstr := sqlstr || fupdate;
sqlstr := sqlstr || fupdate;
sqlstr := sqlstr || actorderby || fupdate;
sqlstr := sqlstr || actorderby || fupdate;
sqlstr := sqlstr || actorderby || fupdate;
sqlstr := sqlstr || actorderby || fupdate;
sqlstr := sqlstr || actorderby || fupdate;
sqlstr := sqlstr || actorderby || fupdate;
sqlstr := sqlstr || actorderby || fupdate;
sqlstr := sqlstr || actorderby || fupdate;
sqlstr := sqlstr || actorderby || fupdate;
select pac.assignment_set_id
into l_asg_set_id
from pay_payroll_actions pac,
hr_assignment_sets has
where pac.payroll_action_id = pactid
and has.assignment_set_id = pac.assignment_set_id
and has.formula_id is null;
select include_or_exclude
into l_inc_or_excl
from hr_assignment_set_amendments
where assignment_set_id = l_asg_set_id
and rownum = 1;
sqlstr := sqlstr || fupdate;
sqlstr := sqlstr || fupdate;
sqlstr := sqlstr || fupdate;
select 'Y'
from dual
where (NOT EXISTS
(select null
from pay_element_entry_values_f ev3,
pay_input_values_f iv3
where TRANSLATE(UPPER(iv3.name), ' ', '_') =
(select TRANSLATE(UPPER(hrl1.meaning), ' ', '_')
from hr_lookups hrl1
WHERE hrl1.lookup_type = 'NAME_TRANSLATIONS'
AND hrl1.lookup_code = 'ADV_OVERRIDE')
and l_eeid = ev3.element_entry_id
and ev3.input_value_id = iv3.input_value_id
and ((ev3.effective_start_date between l_start_date and l_end_date )
or (ev3.effective_start_date < l_start_date
and ev3.effective_end_date > l_start_date ))
and ((iv3.effective_start_date between l_start_date and l_end_date )
or (iv3.effective_start_date < l_start_date
and iv3.effective_end_date > l_start_date )))
OR EXISTS
(select null
from pay_element_entry_values_f ev4,
pay_input_values_f iv4
where TRANSLATE(UPPER(iv4.name), ' ', '_') =
(select TRANSLATE(UPPER(hrl2.meaning), ' ', '_')
from hr_lookups hrl2
WHERE hrl2.lookup_type = 'NAME_TRANSLATIONS'
AND hrl2.lookup_code = 'ADV_OVERRIDE')
and l_eeid = ev4.element_entry_id
and ev4.input_value_id = iv4.input_value_id
and ev4.screen_entry_value <> 'Y'
and ((ev4.effective_start_date between l_start_date and l_end_date )
or (ev4.effective_start_date < l_start_date
and ev4.effective_end_date > l_start_date ))
and ((iv4.effective_start_date between l_start_date and l_end_date )
or (iv4.effective_start_date < l_start_date
and iv4.effective_end_date >l_start_date ))));
rrsel := 'select distinct pay_pos.person_id, null, null';
ordrrsel := 'select /*+ ORDERED USE_NL(pay_asg) */ distinct pay_pos.person_id, null, null';
ordrrsel_1 := 'select /*+ ORDERED */ distinct pay_pos.person_id, null, null'; /*added this for bug 13780337*/
ordrrsel_nohint := 'select distinct pay_pos.person_id, null, null'; /*added this for bug 14184691*/
prrsel := 'select distinct pay_pos.person_id, null, null'; -- For purge.
brrsel := 'select distinct pay_asg.person_id, null, null'; -- For BEE.
orgsel := 'select distinct null, hou.organization_id, ''HOU''';
select
pay_assignment_actions_s.nextval,
null,
pay_pac.payroll_action_id,
''U'',
:chunk_number,
pay_assignment_actions_s.nextval,
1,
null,
''U'',
pay_pos.object_group_id';
select /*+ INDEX(pay_pos PER_PERIODS_OF_SERVICE_PK)*/
pay_assignment_actions_s.nextval,
pay_asg.assignment_id,
pay_pac.payroll_action_id,
''U'',
:chunk_number,
pay_assignment_actions_s.nextval,
1,
hr_dynsql.get_tax_unit(pay_asg.assignment_id,
pay_pac.effective_date),
''U'',
pay_asg.assignment_id';
select /*+ INDEX(pay_pos PER_PERIODS_OF_SERVICE_PK)
INDEX(pay_asg PER_ASSIGNMENTS_F_N12) */
pay_assignment_actions_s.nextval,
pay_asg.assignment_id,
pay_pac.payroll_action_id,
''U'',
:chunk_number,
pay_assignment_actions_s.nextval,
1,
hr_dynsql.get_tax_unit(pay_asg.assignment_id,
pay_pac.effective_date),
''U'',
null';
select /*+ INDEX(pay_pos PER_PERIODS_OF_SERVICE_PK)
INDEX(pay_asg PER_ASSIGNMENTS_F_N12) */
1,
pay_asg.assignment_id,
pay_pac.payroll_action_id,
''U'',
:chunk_number,
1,
1,
hr_dynsql.get_tax_unit(pay_asg.assignment_id,
pay_pac.effective_date),
''U'',
null';
select /*+ INDEX(pay_pos PER_PERIODS_OF_SERVICE_PK)
INDEX(pay_asg PER_ASSIGNMENTS_F_N12) */
pay_paa2.assignment_action_id,
pay_asg.assignment_id,
pay_pac.payroll_action_id,
''U'',
:chunk_number,
1,
1,
hr_dynsql.get_tax_unit(pay_asg.assignment_id,
pay_pac.effective_date),
''U'',
null';
select /*+ INDEX(pay_pos PER_PERIODS_OF_SERVICE_PK)*/
distinct 1,
pay_asg.assignment_id,
pay_pac.payroll_action_id,
''U'',
:chunk_number,
1,
1,
null,
''U'',
null';
select distinct 1,
pay_btl.assignment_id,
pay_pac.payroll_action_id,
''U'',
:chunk_number,
1,
1,
null,
''U'',
null';
and exists (select null
from pay_action_classifications pay_pcl,
pay_assignment_actions pay_act,
per_all_assignments_f pay_asg2,
pay_payroll_actions pay_pac2
where pay_pac2.consolidation_set_id +0 = pay_pac.consolidation_set_id
and pay_pac2.effective_date between
pay_pac.start_date and pay_pac.effective_date
and pay_act.payroll_action_id = pay_pac2.payroll_action_id
and pay_act.action_status = ''C''
and pay_pcl.classification_name = ''COSTED''
and pay_pac2.action_type = pay_pcl.action_type
and pay_asg.assignment_id = pay_act.assignment_id
and pay_asg2.assignment_id = pay_act.assignment_id
and pay_pac2.effective_date between
pay_asg2.effective_start_date and pay_asg2.effective_end_date
and pay_asg2.payroll_id + 0 = pay_asg.payroll_id + 0
and not exists (
select null
from pay_assignment_actions pay_ac2
where pay_ac2.assignment_id = pay_asg.assignment_id
and pay_pac.payroll_action_id = pay_ac2.payroll_action_id))';
and exists (select null from
pay_element_entries_f p_pee,
pay_element_entry_values_f p_pev,
pay_element_entry_values_f p_pev2,
pay_input_values_f p_piv,
pay_input_values_f p_piv2
where p_pee.assignment_id = pay_asg.assignment_id
and pay_pac.effective_date between p_pee.effective_start_date
and p_pee.effective_end_date
and p_pee.element_type_id =
(select to_number(p_plr.rule_mode)
from pay_legislation_rules p_plr,
per_business_groups_perf p_pbg
where p_pbg.business_group_id = pay_pac.business_group_id
and p_pbg.legislation_code = p_plr.legislation_code
and TRANSLATE(upper(p_plr.rule_type),''-'',''_'' )=
''PAY_ADVANCE_INDICATOR'')
and p_pee.element_entry_id = p_pev.element_entry_id
and p_pee.element_entry_id = p_pev2.element_entry_id
and p_pev.input_value_id = p_piv.input_value_id
and p_piv2.input_value_id = p_pev2.input_value_id
and p_piv.input_value_id = (select to_number(p_plr.rule_mode)
from pay_legislation_rules p_plr, per_business_groups_perf p_pbg
where p_pbg.business_group_id = pay_pac.business_group_id
and p_pbg.legislation_code = p_plr.legislation_code
and TRANSLATE(upper(p_plr.rule_type),''-'',''_'') = ''PAI_START_DATE'')
and p_piv2.input_value_id = (select to_number(p_plr.rule_mode)
from pay_legislation_rules p_plr, per_business_groups_perf p_pbg
where p_pbg.business_group_id = pay_pac.business_group_id
and p_pbg.legislation_code = p_plr.legislation_code
and TRANSLATE(upper(p_plr.rule_type), ''-'',''_'') = ''PAI_END_DATE'')
and not exists (select null
from pay_element_entries_f p_pe2
where p_pe2.assignment_id = pay_asg.assignment_id
and p_pe2.element_type_id =
(select to_number(p_plr2.rule_mode)
from pay_legislation_rules p_plr2, per_business_groups_perf p_pbg2
where p_pbg2.business_group_id = pay_pac.business_group_id
and p_pbg2.legislation_code = p_plr2.legislation_code
and TRANSLATE(upper(p_plr2.rule_type), ''-'', ''_'') = ''ADV_DEDUCTION'')
and p_pe2.effective_start_date between
fnd_date.canonical_to_date(p_pev.screen_entry_value)
and fnd_date.canonical_to_date(p_pev2.screen_entry_value)))';
(select null
from pay_element_entries_f pay_pee,
pay_element_types_f pay_pet,
pay_element_entry_values_f pay_pev,
pay_element_entry_values_f pay_pev2,
pay_input_values_f pay_piv,
pay_input_values_f pay_piv2
where pay_pee.assignment_id = pay_asg.assignment_id
and pay_pee.element_type_id = pay_pet.element_type_id
and pay_pet.advance_indicator = ''Y''
and pay_pee.element_entry_id = pay_pev.element_entry_id
and pay_pee.element_entry_id = pay_pev2.element_entry_id
and pay_pev.input_value_id = pay_piv.input_value_id
and pay_piv2.input_value_id = pay_pev2.input_value_id
and hr_dynsql.adv_override_check(pay_pee.element_entry_id,pay_pac.effective_date,pay_pac.end_date) = ''Y''
and TRANSLATE(UPPER(pay_piv.name), '' '', ''_'') =
(select TRANSLATE(UPPER(pay_hrl3.meaning), '' '', ''_'')
from hr_lookups pay_hrl3
WHERE pay_hrl3.lookup_type = ''NAME_TRANSLATIONS''
AND pay_hrl3.lookup_code = ''START_DATE'')
and TRANSLATE(UPPER(pay_piv2.name), '' '', ''_'') =
(select TRANSLATE(UPPER(pay_hrl4.meaning), '' '', ''_'')
from hr_lookups pay_hrl4
WHERE pay_hrl4.lookup_type = ''NAME_TRANSLATIONS''
AND pay_hrl4.lookup_code = ''END_DATE'')
and (pay_pev.screen_entry_value between
fnd_date.date_to_canonical(pay_pac.effective_date) and
fnd_date.date_to_canonical(pay_pac.end_date)
OR (pay_pev.screen_entry_value < fnd_date.date_to_canonical(pay_pac.effective_date) and
pay_pev2.screen_entry_value > fnd_date.date_to_canonical(pay_pac.effective_date)))
)' ;
(select null
from pay_element_entries_f pay_pee,
pay_element_types_f pay_pet,
pay_element_entry_values_f pay_pev,
pay_element_entry_values_f pay_pev2,
pay_input_values_f pay_piv,
pay_input_values_f pay_piv2
where pay_pee.assignment_id = pay_asg.assignment_id
and pay_pee.element_type_id = pay_pet.element_type_id
and pay_pet.advance_indicator = ''Y''
and pay_pee.element_entry_id = pay_pev.element_entry_id
and pay_pee.element_entry_id = pay_pev2.element_entry_id
and pay_pev.input_value_id = pay_piv.input_value_id
and pay_piv2.input_value_id = pay_pev2.input_value_id
and hr_dynsql.adv_override_check(pay_pee.element_entry_id,pay_pac.effective_date,pay_pac.end_date) = ''Y''
and TRANSLATE(UPPER(pay_piv.name), '' '', ''_'') =
(select TRANSLATE(UPPER(pay_hrl3.meaning), '' '', ''_'')
from hr_lookups pay_hrl3
WHERE pay_hrl3.lookup_type = ''NAME_TRANSLATIONS''
AND pay_hrl3.lookup_code = ''START_DATE'')
and TRANSLATE(UPPER(pay_piv2.name), '' '', ''_'') =
(select TRANSLATE(UPPER(pay_hrl4.meaning), '' '', ''_'')
from hr_lookups pay_hrl4
WHERE pay_hrl4.lookup_type = ''NAME_TRANSLATIONS''
AND pay_hrl4.lookup_code = ''END_DATE'')
and (pay_pev.screen_entry_value between
fnd_date.date_to_canonical(pay_pac.effective_date) and
fnd_date.date_to_canonical(pay_pac.end_date)
OR (pay_pev.screen_entry_value < fnd_date.date_to_canonical(pay_pac.effective_date) and
pay_pev2.screen_entry_value > fnd_date.date_to_canonical(pay_pac.effective_date)))
)' ;
and exists (select null
from pay_action_classifications pay_pcl,
pay_assignment_actions pay_act,
per_all_assignments_f pay_asg2,
pay_payroll_actions pay_pac2
where pay_pac2.consolidation_set_id +0 = pay_pac.consolidation_set_id
and pay_pac2.effective_date between
pay_pac.start_date and pay_pac.effective_date
and pay_act.payroll_action_id = pay_pac2.payroll_action_id
and pay_act.action_status = ''C''
and pay_pcl.classification_name = ''COSTED''
and pay_pac2.action_type = pay_pcl.action_type
and pay_asg.assignment_id = pay_act.assignment_id
and pay_asg2.assignment_id = pay_act.assignment_id
and pay_pac2.effective_date between
pay_asg2.effective_start_date and pay_asg2.effective_end_date
and pay_asg2.payroll_id + 0 = pay_asg.payroll_id + 0
and not exists (
select null
from pay_assignment_actions pay_ac2
where pay_ac2.assignment_id = pay_asg.assignment_id
and pay_pac.payroll_action_id = pay_ac2.payroll_action_id))';
select pay_pop.person_id
from pay_population_ranges pay_pop
where pay_pop.payroll_action_id = pay_pac.payroll_action_id
and pay_pop.chunk_number = :chunk)';
select pay_pop.person_id
from pay_population_ranges pay_pop
where pay_pop.payroll_action_id = pay_pac.payroll_action_id
and pay_pop.chunk_number = :chunk)';
select null
from hr_assignment_set_amendments pay_exc
where pay_exc.assignment_set_id = pay_pac.assignment_set_id
and pay_exc.assignment_id = pay_asg.assignment_id
and pay_exc.include_or_exclude = ''E'')';
select /*+ INDEX (pay_pa2 pay_payroll_actions_pk) */ null
from pay_action_classifications pay_acl,
pay_payroll_actions pay_pa2,
pay_assignment_actions pay_ac2
where pay_ac2.assignment_id = pay_asg.assignment_id
and pay_pa2.payroll_action_id = pay_ac2.payroll_action_id
and pay_acl.classification_name = ''SEQUENCED''
and pay_pa2.action_type = pay_acl.action_type
and (pay_pa2.effective_date > pay_pac.effective_date
or (pay_ac2.action_status not in (''C'', ''S'')
and pay_pa2.effective_date <= pay_pac.effective_date)))';
select /*+ INDEX (pay_pa2 pay_payroll_actions_pk) */ null
from pay_action_classifications pay_acl,
pay_payroll_actions pay_pa2,
pay_assignment_actions pay_ac2
where pay_ac2.assignment_id = pay_asg.assignment_id
and pay_pa2.payroll_action_id = pay_ac2.payroll_action_id
and pay_acl.classification_name = ''SEQUENCED''
and pay_pa2.action_type = pay_acl.action_type
and pay_ac2.action_status not in (''C'', ''S''))';
and not exists (select ''''
from pay_object_groups pay_pog_asg2
where pay_pog_asg2.parent_object_group_id = pay_pos.object_group_id
and pay_pog_asg2.source_type = ''PAF''
and hr_dynsql.process_group_seq_locked(pay_pog_asg2.source_id,
pay_pac.effective_date,
''Y'') <> ''N''
)';
select null
from pay_assignment_actions pay_ac2,
pay_payroll_actions pay_pa2
where pay_ac2.assignment_id = pay_asg.assignment_id +decode(pay_pos.period_of_service_id,0,0,0)
and pay_pa2.payroll_action_id = pay_ac2.payroll_action_id
and pay_pa2.action_type = ''Z''
and (pay_ac2.secondary_status <> ''C''
or (pay_pa2.effective_date >= pay_pac.effective_date)))
and exists (
select null
from pay_assignment_actions pay_ac4,
pay_payroll_actions pay_pa4
where pay_ac4.assignment_id = pay_asg.assignment_id +decode(pay_pos.period_of_service_id,0,0,0)
and pay_pa4.payroll_action_id = pay_ac4.payroll_action_id
and pay_pa4.effective_date <= pay_pac.effective_date
and pay_pa4.action_type <> ''Z''
and pay_pa4.effective_date >=
(select nvl(max(pay_pa42.effective_date)
,hr_general.start_of_time)
from pay_assignment_actions pay_ac42,
pay_payroll_actions pay_pa42
where pay_ac42.assignment_id = pay_asg.assignment_id +decode(pay_pos.period_of_service_id,0,0,0)
and pay_pa42.payroll_action_id = pay_ac42.payroll_action_id
and pay_pa42.action_type = ''Z''
and pay_ac42.secondary_status = ''C''
))
and (not exists
(select null from pay_action_parameters
where parameter_name = ''PURGE_SKIP_TERM_ASG''
and parameter_value = ''Y'')
or (pay_pac.effective_date between
pay_asg.effective_start_date and pay_asg.effective_end_date
and exists
(select null
from per_time_periods pay_tp5
where pay_tp5.payroll_id = pay_asg.payroll_id
and pay_pac.effective_date between
pay_tp5.start_date and pay_tp5.end_date)))';
select /*+ INDEX (pay_pa2 pay_payroll_actions_pk) */
null
from pay_action_classifications pay_acl,
pay_payroll_actions pay_pa2,
pay_assignment_actions pay_ac2
where pay_ac2.assignment_id = pay_asg.assignment_id
and pay_pa2.payroll_action_id = pay_ac2.payroll_action_id
and pay_acl.classification_name = ''SEQUENCED''
and pay_pa2.action_type = pay_acl.action_type
and ((pay_pa2.effective_date > pay_pac.effective_date
and pay_ac2.action_status in (''C'', ''S''))
or (pay_ac2.action_status not in (''C'', ''S'')
and pay_pa2.effective_date <= pay_pac.effective_date)))';
and exists (select ''''
from pay_retro_assignments pay_ret_asg,
per_all_assignments_f pay_asg2,
pay_object_groups pay_pog_asg2
where pay_pog_asg2.parent_object_group_id = pay_pos.object_group_id
and pay_pog_asg2.source_type = ''PAF''
and pay_asg2.assignment_id = pay_pog_asg2.source_id
and pay_asg2.payroll_id + 0 = pay_pac.payroll_id
and pay_ret_asg.assignment_id = pay_asg2.assignment_id
and pay_ret_asg.retro_assignment_action_id IS NULL
and pay_ret_asg.superseding_retro_asg_id IS NULL -- 7364151
and pay_ret_asg.approval_status <> ''D''
and pay_pac.effective_date between pay_asg2.effective_start_date
and pay_asg2.effective_end_date
)';
and exists (select ''''
from pay_retro_assignments pay_ret_asg,
per_all_assignments_f pay_asg2,
pay_object_groups pay_pog_asg2
where pay_pog_asg2.parent_object_group_id = pay_pos.object_group_id
and pay_pog_asg2.source_type = ''PAF''
and pay_asg2.assignment_id = pay_pog_asg2.source_id
and pay_asg2.payroll_id + 0 = pay_pac.payroll_id
and pay_ret_asg.assignment_id = pay_asg2.assignment_id
and pay_asg2.assignment_id = :assignment_id
and pay_ret_asg.retro_assignment_action_id IS NULL
and pay_ret_asg.superseding_retro_asg_id IS NULL -- 7364151
and pay_ret_asg.approval_status <> ''D''
and pay_pac.effective_date between pay_asg2.effective_start_date
and pay_asg2.effective_end_date
)';
(select 1
from pay_assignment_actions aa9
where aa9.source_action_id = pay_paa2.assignment_action_id)
/* check havent done reversal before */
and not exists
(select 1
from pay_action_interlocks int,
pay_assignment_actions aa9,
pay_payroll_actions pay_ppa2
where int.locked_action_id = pay_paa2.assignment_action_id
and aa9.assignment_action_id = int.locking_action_id
and pay_ppa2.payroll_action_id = aa9.payroll_action_id
and pay_ppa2.action_type = ''V'')';
fupdate := '
for update of pay_asg.assignment_id, pay_pos.period_of_service_id';