[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
function ee_datetrack_update_mode(
p_element_entry_id in number,
p_effective_start_date in date,
p_effective_end_date in date,
p_effective_date in date) return varchar2
is
l_datetrack_mode varchar2(30);
select 'Y'
from dual
where exists(
select null
from pay_element_entries_f
where element_entry_id = p_element_entry_id
and effective_start_date = p_effective_end_date + 1);
l_datetrack_mode := 'UPDATE';
l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
end ee_datetrack_update_mode;
function cei_datetrack_update_mode(
p_contact_extra_info_id in number,
p_effective_start_date in date,
p_effective_end_date in date,
p_effective_date in date) return varchar2
is
l_datetrack_mode varchar2(30);
select 'Y'
from dual
where exists(
select null
from per_contact_extra_info_f
where contact_extra_info_id = p_contact_extra_info_id
and effective_start_date = p_effective_end_date + 1);
l_datetrack_mode := 'UPDATE';
l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
end cei_datetrack_update_mode;
function cei_datetrack_delete_mode(
p_contact_extra_info_id in number,
p_effective_start_date in date,
p_effective_end_date in date,
p_effective_date in date) return varchar2
is
l_datetrack_mode varchar2(30);
select 'Y'
from dual
where exists(
select null
from per_contact_extra_info_f
where contact_extra_info_id = p_contact_extra_info_id
and effective_end_date = p_effective_start_date - 1);
l_datetrack_mode := 'DELETE';
l_datetrack_mode := 'DELETE';
end cei_datetrack_delete_mode;
select trim(per_information18 || ' ' || per_information19)
from per_all_people_f
where person_id = p_person_id
and p_effective_date
between effective_start_date and effective_end_date;
select trim(per_information18 || ' ' || per_information19)
from per_all_people_f
where person_id = p_person_id
and start_date = effective_start_date;
procedure insert_session(p_effective_date in date)
is
l_rowid rowid;
select rowid
from fnd_sessions
where session_id = userenv('sessionid')
for update nowait;
insert into fnd_sessions(
session_id,
effective_date)
values( userenv('sessionid'),
p_effective_date);
update fnd_sessions
set effective_date = p_effective_date
where rowid = l_rowid;
end insert_session;
procedure delete_session
is
begin
delete
from fnd_sessions
where session_id = userenv('sessionid');
end delete_session;
select submission_period_status,
submission_start_date,
submission_end_date
from pay_jp_def_pact_v pact,
pay_assignment_actions paa,
pay_jp_def_assact_v assact
where assact.action_information_id = p_action_information_id
and paa.assignment_action_id = assact.assignment_action_id
and pact.payroll_action_id = paa.payroll_action_id;
pay_jp_def_api.update_entry(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => p_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
P_ELEMENT_ENTRY_ID => p_rec.element_entry_id,
P_EE_OBJECT_VERSION_NUMBER => p_rec.ee_object_version_number);
pay_element_entry_api.update_element_entry(
p_validate => false,
p_effective_date => p_rec.effective_date,
p_business_group_id => p_business_group_id,
p_datetrack_update_mode => p_rec.datetrack_update_mode,
p_element_entry_id => p_rec.element_entry_id,
p_object_version_number => p_rec.ee_object_version_number,
p_input_value_id1 => c_disability_type_iv,
p_input_value_id2 => c_aged_type_iv,
p_input_value_id3 => c_widow_type_iv,
p_input_value_id4 => c_working_student_type_iv,
p_input_value_id5 => c_spouse_dep_type_iv,
p_input_value_id6 => c_spouse_disability_type_iv,
p_input_value_id7 => c_num_deps_iv,
p_input_value_id8 => c_num_ageds_iv,
p_input_value_id9 => c_num_aged_parents_iv,
p_input_value_id10 => c_num_specifieds_iv,
p_input_value_id11 => c_num_junior_deps_iv,
p_input_value_id12 => c_num_disableds_iv,
p_input_value_id13 => c_num_svr_disableds_iv,
p_input_value_id14 => c_num_svr_disableds_lt_iv,
p_entry_value1 => p_rec.disability_type,
p_entry_value2 => p_rec.aged_type,
p_entry_value3 => p_rec.widow_type,
p_entry_value4 => p_rec.working_student_type,
p_entry_value5 => p_rec.spouse_dep_type,
p_entry_value6 => p_rec.spouse_disability_type,
p_entry_value7 => fnd_number.number_to_canonical(p_rec.num_deps),
p_entry_value8 => fnd_number.number_to_canonical(p_rec.num_ageds),
p_entry_value9 => fnd_number.number_to_canonical(p_rec.num_aged_parents_lt),
p_entry_value10 => fnd_number.number_to_canonical(p_rec.num_specifieds),
p_entry_value11 => fnd_number.number_to_canonical(p_dep_rec.num_junior_deps),
p_entry_value12 => fnd_number.number_to_canonical(p_rec.num_disableds),
p_entry_value13 => fnd_number.number_to_canonical(p_rec.num_svr_disableds),
p_entry_value14 => fnd_number.number_to_canonical(p_rec.num_svr_disableds_lt),
-- Aged Type can be defaulted to '0' after 2005/01/01
-- even user enterable is "No".
p_override_user_ent_chk => 'Y',
p_effective_start_date => l_esd,
p_effective_end_date => l_eed,
p_update_warning => l_warning);
pay_jp_def_api.update_entry(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => p_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
P_STATUS => p_rec.status,
P_EE_OBJECT_VERSION_NUMBER => p_rec.ee_object_version_number);
pay_element_entry_api.delete_element_entry(
p_validate => false,
p_effective_date => p_rec.effective_date,
p_datetrack_delete_mode => 'ZAP',
p_element_entry_id => p_rec.element_entry_id,
p_object_version_number => p_rec.ee_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed,
p_delete_warning => l_warning);
pay_jp_def_api.update_entry(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => p_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
P_ELEMENT_ENTRY_ID => p_rec.element_entry_id,
P_EE_OBJECT_VERSION_NUMBER => p_rec.ee_object_version_number);
if p_rec.datetrack_update_mode = 'CORRECTION' then
pay_element_entry_api.update_element_entry(
p_validate => false,
p_effective_date => p_rec.effective_date,
p_business_group_id => p_business_group_id,
p_datetrack_update_mode => p_rec.datetrack_update_mode,
p_element_entry_id => p_rec.element_entry_id,
p_object_version_number => p_rec.ee_object_version_number,
p_input_value_id1 => c_disability_type_iv,
p_input_value_id2 => c_aged_type_iv,
p_input_value_id3 => c_widow_type_iv,
p_input_value_id4 => c_working_student_type_iv,
p_input_value_id5 => c_spouse_dep_type_iv,
p_input_value_id6 => c_spouse_disability_type_iv,
p_input_value_id7 => c_num_deps_iv,
p_input_value_id8 => c_num_ageds_iv,
p_input_value_id9 => c_num_aged_parents_iv,
p_input_value_id10 => c_num_specifieds_iv,
p_input_value_id11 => c_num_junior_deps_iv,
p_input_value_id12 => c_num_disableds_iv,
p_input_value_id13 => c_num_svr_disableds_iv,
p_input_value_id14 => c_num_svr_disableds_lt_iv,
p_entry_value1 => p_rec.disability_type_o,
p_entry_value2 => p_rec.aged_type_o,
p_entry_value3 => p_rec.widow_type_o,
p_entry_value4 => p_rec.working_student_type_o,
p_entry_value5 => p_rec.spouse_dep_type_o,
p_entry_value6 => p_rec.spouse_disability_type_o,
p_entry_value7 => fnd_number.number_to_canonical(p_rec.num_deps_o),
p_entry_value8 => fnd_number.number_to_canonical(p_rec.num_ageds_o),
p_entry_value9 => fnd_number.number_to_canonical(p_rec.num_aged_parents_lt_o),
p_entry_value10 => fnd_number.number_to_canonical(p_rec.num_specifieds_o),
p_entry_value11 => fnd_number.number_to_canonical(p_dep_rec.num_junior_deps_o),
p_entry_value12 => fnd_number.number_to_canonical(p_rec.num_disableds_o),
p_entry_value13 => fnd_number.number_to_canonical(p_rec.num_svr_disableds_o),
p_entry_value14 => fnd_number.number_to_canonical(p_rec.num_svr_disableds_lt_o),
-- Aged Type could be defaulted to '0' after 2005/01/01
-- even user enterable is "No".
p_override_user_ent_chk => 'Y',
p_effective_start_date => l_esd,
p_effective_end_date => l_eed,
p_update_warning => l_warning);
-- For UPDATE or UPDATE_CHANGE_INSERT, OVN for previous record
-- should be current OVN - 1. See API for more details.
-- !!!!!
-- This does not work after expired, because OVN of latest record is updated.
-- At first, lock the current record. If locked successfully,
-- derive OVN of previous record.
--
pay_ele_shd.lck(
p_effective_date => p_rec.effective_date,
p_datetrack_mode => 'CORRECTION',
p_element_entry_id => p_rec.element_entry_id,
p_object_version_number => p_rec.ee_object_version_number,
p_validation_start_date => l_vsd,
p_validation_end_date => l_ved);
select object_version_number
into p_rec.ee_object_version_number
from pay_element_entries_f
where element_entry_id = p_rec.element_entry_id
and effective_end_date = p_rec.effective_date - 1;
pay_element_entry_api.delete_element_entry(
p_validate => false,
p_effective_date => p_rec.effective_date - 1,
p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
p_element_entry_id => p_rec.element_entry_id,
p_object_version_number => p_rec.ee_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed,
p_delete_warning => l_warning);
pay_jp_def_api.update_entry(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => p_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
P_STATUS => p_rec.status,
P_EE_OBJECT_VERSION_NUMBER => p_rec.ee_object_version_number);
pay_jp_def_api.update_dep(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => p_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
P_CONTACT_EXTRA_INFO_ID => p_rec.contact_extra_info_id,
P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
hr_contact_extra_info_api.update_contact_extra_info(
p_validate => false,
p_effective_date => p_rec.effective_date,
p_datetrack_update_mode => p_rec.datetrack_update_mode,
p_contact_extra_info_id => p_rec.contact_extra_info_id,
p_object_version_number => p_rec.cei_object_version_number,
p_cei_information4 => fnd_date.date_to_canonical(p_rec.change_date),
p_cei_information5 => p_rec.change_reason,
p_cei_information8 => p_rec.dep_type,
p_cei_information2 => p_rec.occupation,
p_cei_information3 => fnd_number.number_to_canonical(p_rec.estimated_annual_income),
p_cei_information6 => p_rec.disability_type,
p_cei_information7 => p_rec.disability_details,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
pay_jp_def_api.update_dep(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => p_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
P_STATUS => p_rec.status,
P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
if p_rec.datetrack_delete_mode = 'ZAP' then
l_effective_date := p_rec.effective_date;
hr_contact_extra_info_api.delete_contact_extra_info(
p_validate => false,
p_effective_date => l_effective_date,
p_datetrack_delete_mode => p_rec.datetrack_delete_mode,
p_contact_extra_info_id => p_rec.contact_extra_info_id,
p_object_version_number => p_rec.cei_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
if p_rec.datetrack_delete_mode <> 'ZAP' then
pay_jp_def_api.update_dep(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => p_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
hr_contact_extra_info_api.delete_contact_extra_info(
p_validate => false,
p_effective_date => p_rec.effective_date,
p_datetrack_delete_mode => 'ZAP',
p_contact_extra_info_id => p_rec.contact_extra_info_id,
p_object_version_number => p_rec.cei_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
pay_jp_def_api.update_dep(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => p_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
P_CONTACT_EXTRA_INFO_ID => p_rec.contact_extra_info_id,
P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
if p_rec.datetrack_update_mode = 'CORRECTION' then
hr_contact_extra_info_api.update_contact_extra_info(
p_validate => false,
p_effective_date => p_rec.effective_date,
p_datetrack_update_mode => p_rec.datetrack_update_mode,
p_contact_extra_info_id => p_rec.contact_extra_info_id,
p_object_version_number => p_rec.cei_object_version_number,
-- Rollback change_date/change_reason is not supported for CORRECTION mode.
p_cei_information8 => p_rec.dep_type_o,
p_cei_information2 => p_rec.occupation_o,
p_cei_information3 => fnd_number.number_to_canonical(p_rec.estimated_annual_income_o),
p_cei_information6 => p_rec.disability_type_o,
p_cei_information7 => p_rec.disability_details_o,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
-- For UPDATE or UPDATE_CHANGE_INSERT, OVN for previous record
-- should be current OVN - 1. See API for more details.
-- !!!!!
-- This does not work after expired, because OVN of latest record is updated.
-- At first, lock the current record. If locked successfully,
-- derive OVN of previous record.
--
per_rei_shd.lck(
p_effective_date => p_rec.effective_date,
p_datetrack_mode => 'CORRECTION',
p_contact_extra_info_id => p_rec.contact_extra_info_id,
p_object_version_number => p_rec.cei_object_version_number,
p_validation_start_date => l_vsd,
p_validation_end_date => l_ved);
select object_version_number
into p_rec.cei_object_version_number
from per_contact_extra_info_f
where contact_extra_info_id = p_rec.contact_extra_info_id
and effective_end_date = p_rec.effective_date - 1;
hr_contact_extra_info_api.delete_contact_extra_info(
p_validate => false,
p_effective_date => p_rec.effective_date - 1,
p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
p_contact_extra_info_id => p_rec.contact_extra_info_id,
p_object_version_number => p_rec.cei_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
pay_jp_def_api.update_dep(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => p_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
P_STATUS => p_rec.status,
P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
if p_rec.datetrack_delete_mode = 'ZAP' then
--
-- Note EFFECTIVE_END_DATE/change_date/change_reason cannot be rollbacked
-- for ZAP case.
--
hr_contact_extra_info_api.create_contact_extra_info(
p_validate => false,
p_effective_date => p_rec.effective_date,
p_contact_relationship_id => p_rec.contact_relationship_id,
p_information_type => 'JP_ITAX_DEPENDENT',
p_cei_information_category => 'JP_ITAX_DEPENDENT',
p_cei_information2 => p_rec.occupation_o,
p_cei_information3 => fnd_number.number_to_canonical(p_rec.estimated_annual_income_o),
p_cei_information6 => p_rec.disability_type_o,
p_cei_information7 => p_rec.disability_details_o,
p_cei_information8 => p_rec.dep_type_o,
p_contact_extra_info_id => p_rec.contact_extra_info_id,
p_object_version_number => p_rec.cei_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
hr_contact_extra_info_api.delete_contact_extra_info(
p_validate => false,
p_effective_date => p_rec.effective_date - 1,
p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
p_contact_extra_info_id => p_rec.contact_extra_info_id,
p_object_version_number => p_rec.cei_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
pay_jp_def_api.update_dep(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => p_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
P_CONTACT_EXTRA_INFO_ID => p_rec.contact_extra_info_id,
P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
pay_jp_def_api.update_dep_oe(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => p_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
P_CONTACT_EXTRA_INFO_ID => p_rec.contact_extra_info_id,
P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
hr_contact_extra_info_api.update_contact_extra_info(
p_validate => false,
p_effective_date => p_rec.effective_date,
p_datetrack_update_mode => p_rec.datetrack_update_mode,
p_contact_extra_info_id => p_rec.contact_extra_info_id,
p_object_version_number => p_rec.cei_object_version_number,
p_cei_information2 => fnd_date.date_to_canonical(p_rec.change_date),
p_cei_information3 => p_rec.change_reason,
p_cei_information1 => p_rec.occupation,
p_cei_information5 => fnd_number.number_to_canonical(p_rec.oe_contact_relationship_id),
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
pay_jp_def_api.update_dep_oe(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => p_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
P_STATUS => p_rec.status,
P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
if p_rec.datetrack_delete_mode = 'ZAP' then
l_effective_date := p_rec.effective_date;
hr_contact_extra_info_api.delete_contact_extra_info(
p_validate => false,
p_effective_date => l_effective_date,
p_datetrack_delete_mode => p_rec.datetrack_delete_mode,
p_contact_extra_info_id => p_rec.contact_extra_info_id,
p_object_version_number => p_rec.cei_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
if p_rec.datetrack_delete_mode <> 'ZAP' then
pay_jp_def_api.update_dep(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => p_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
hr_contact_extra_info_api.delete_contact_extra_info(
p_validate => false,
p_effective_date => p_rec.effective_date,
p_datetrack_delete_mode => 'ZAP',
p_contact_extra_info_id => p_rec.contact_extra_info_id,
p_object_version_number => p_rec.cei_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
pay_jp_def_api.update_dep_oe(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => p_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
P_CONTACT_EXTRA_INFO_ID => p_rec.contact_extra_info_id,
P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
if p_rec.datetrack_update_mode = 'CORRECTION' then
hr_contact_extra_info_api.update_contact_extra_info(
p_validate => false,
p_effective_date => p_rec.effective_date,
p_datetrack_update_mode => p_rec.datetrack_update_mode,
p_contact_extra_info_id => p_rec.contact_extra_info_id,
p_object_version_number => p_rec.cei_object_version_number,
-- Rollback change_date/change_reason is not supported for CORRECTION mode.
p_cei_information1 => p_rec.occupation_o,
p_cei_information5 => fnd_number.number_to_canonical(p_rec.oe_contact_relationship_id_o),
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
-- For UPDATE or UPDATE_CHANGE_INSERT, OVN for previous record
-- should be current OVN - 1. See API for more details.
-- !!!!!
-- This does not work after expired, because OVN of latest record is updated.
-- At first, lock the current record. If locked successfully,
-- derive OVN of previous record.
--
per_rei_shd.lck(
p_effective_date => p_rec.effective_date,
p_datetrack_mode => 'CORRECTION',
p_contact_extra_info_id => p_rec.contact_extra_info_id,
p_object_version_number => p_rec.cei_object_version_number,
p_validation_start_date => l_vsd,
p_validation_end_date => l_ved);
select object_version_number
into p_rec.cei_object_version_number
from per_contact_extra_info_f
where contact_extra_info_id = p_rec.contact_extra_info_id
and effective_end_date = p_rec.effective_date - 1;
hr_contact_extra_info_api.delete_contact_extra_info(
p_validate => false,
p_effective_date => p_rec.effective_date - 1,
p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
p_contact_extra_info_id => p_rec.contact_extra_info_id,
p_object_version_number => p_rec.cei_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
pay_jp_def_api.update_dep_oe(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => p_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
P_STATUS => p_rec.status,
P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
if p_rec.datetrack_delete_mode = 'ZAP' then
--
-- Note EFFECTIVE_END_DATE/change_date/change_reason cannot be rollbacked
-- for ZAP case.
--
hr_contact_extra_info_api.create_contact_extra_info(
p_validate => false,
p_effective_date => p_rec.effective_date,
p_contact_relationship_id => p_rec.contact_relationship_id,
p_information_type => 'JP_ITAX_DEPENDENT_ON_OTHER_EMP',
p_cei_information_category => 'JP_ITAX_DEPENDENT_ON_OTHER_EMP',
p_cei_information1 => p_rec.occupation_o,
p_cei_information5 => fnd_number.number_to_canonical(p_rec.oe_contact_relationship_id_o),
p_contact_extra_info_id => p_rec.contact_extra_info_id,
p_object_version_number => p_rec.cei_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
hr_contact_extra_info_api.delete_contact_extra_info(
p_validate => false,
p_effective_date => p_rec.effective_date - 1,
p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
p_contact_extra_info_id => p_rec.contact_extra_info_id,
p_object_version_number => p_rec.cei_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
pay_jp_def_api.update_dep_oe(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => p_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
P_CONTACT_EXTRA_INFO_ID => p_rec.contact_extra_info_id,
P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
pay_jp_def_api.update_dep_os(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => p_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
P_CONTACT_EXTRA_INFO_ID => p_rec.contact_extra_info_id,
P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
hr_contact_extra_info_api.update_contact_extra_info(
p_validate => false,
p_effective_date => p_rec.effective_date,
p_datetrack_update_mode => p_rec.datetrack_update_mode,
p_contact_extra_info_id => p_rec.contact_extra_info_id,
p_object_version_number => p_rec.cei_object_version_number,
p_cei_information1 => p_rec.occupation,
p_cei_information2 => p_rec.os_salary_payer_name,
p_cei_information3 => p_rec.os_salary_payer_address,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
pay_jp_def_api.update_dep_os(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => p_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
P_STATUS => p_rec.status,
P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
if p_rec.datetrack_delete_mode = 'ZAP' then
l_effective_date := p_rec.effective_date;
hr_contact_extra_info_api.delete_contact_extra_info(
p_validate => false,
p_effective_date => l_effective_date,
p_datetrack_delete_mode => p_rec.datetrack_delete_mode,
p_contact_extra_info_id => p_rec.contact_extra_info_id,
p_object_version_number => p_rec.cei_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
if p_rec.datetrack_delete_mode <> 'ZAP' then
pay_jp_def_api.update_dep(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => p_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
hr_contact_extra_info_api.delete_contact_extra_info(
p_validate => false,
p_effective_date => p_rec.effective_date,
p_datetrack_delete_mode => 'ZAP',
p_contact_extra_info_id => p_rec.contact_extra_info_id,
p_object_version_number => p_rec.cei_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
pay_jp_def_api.update_dep_os(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => p_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
P_CONTACT_EXTRA_INFO_ID => p_rec.contact_extra_info_id,
P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
if p_rec.datetrack_update_mode = 'CORRECTION' then
hr_contact_extra_info_api.update_contact_extra_info(
p_validate => false,
p_effective_date => p_rec.effective_date,
p_datetrack_update_mode => p_rec.datetrack_update_mode,
p_contact_extra_info_id => p_rec.contact_extra_info_id,
p_object_version_number => p_rec.cei_object_version_number,
p_cei_information1 => p_rec.occupation_o,
p_cei_information2 => p_rec.os_salary_payer_name_o,
p_cei_information3 => p_rec.os_salary_payer_address_o,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
-- For UPDATE or UPDATE_CHANGE_INSERT, OVN for previous record
-- should be current OVN - 1. See API for more details.
-- !!!!!
-- This does not work after expired, because OVN of latest record is updated.
-- At first, lock the current record. If locked successfully,
-- derive OVN of previous record.
--
per_rei_shd.lck(
p_effective_date => p_rec.effective_date,
p_datetrack_mode => 'CORRECTION',
p_contact_extra_info_id => p_rec.contact_extra_info_id,
p_object_version_number => p_rec.cei_object_version_number,
p_validation_start_date => l_vsd,
p_validation_end_date => l_ved);
select object_version_number
into p_rec.cei_object_version_number
from per_contact_extra_info_f
where contact_extra_info_id = p_rec.contact_extra_info_id
and effective_end_date = p_rec.effective_date - 1;
hr_contact_extra_info_api.delete_contact_extra_info(
p_validate => false,
p_effective_date => p_rec.effective_date - 1,
p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
p_contact_extra_info_id => p_rec.contact_extra_info_id,
p_object_version_number => p_rec.cei_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
pay_jp_def_api.update_dep_os(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => p_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
P_STATUS => p_rec.status,
P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
if p_rec.datetrack_delete_mode = 'ZAP' then
--
-- Note EFFECTIVE_END_DATE/change_date/change_reason cannot be rollbacked
-- for ZAP case.
--
hr_contact_extra_info_api.create_contact_extra_info(
p_validate => false,
p_effective_date => p_rec.effective_date,
p_contact_relationship_id => p_rec.contact_relationship_id,
p_information_type => 'JP_ITAX_DEPENDENT_ON_OTHER_PAY',
p_cei_information_category => 'JP_ITAX_DEPENDENT_ON_OTHER_PAY',
p_cei_information1 => p_rec.occupation_o,
p_cei_information2 => p_rec.os_salary_payer_name_o,
p_cei_information3 => p_rec.os_salary_payer_address_o,
p_contact_extra_info_id => p_rec.contact_extra_info_id,
p_object_version_number => p_rec.cei_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
hr_contact_extra_info_api.delete_contact_extra_info(
p_validate => false,
p_effective_date => p_rec.effective_date - 1,
p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
p_contact_extra_info_id => p_rec.contact_extra_info_id,
p_object_version_number => p_rec.cei_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
pay_jp_def_api.update_dep_os(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => p_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
P_CONTACT_EXTRA_INFO_ID => p_rec.contact_extra_info_id,
P_CEI_OBJECT_VERSION_NUMBER => p_rec.cei_object_version_number);
pay_jp_def_api.update_entry_os(
p_validate => false,
p_action_information_id => p_rec.action_information_id,
p_object_version_number => p_rec.object_version_number,
p_element_entry_id => p_rec.element_entry_id,
p_ee_object_version_number => p_rec.ee_object_version_number);
pay_element_entry_api.update_element_entry(
p_validate => false,
p_effective_date => p_rec.effective_date,
p_business_group_id => p_business_group_id,
p_datetrack_update_mode => p_rec.datetrack_update_mode,
p_element_entry_id => p_rec.element_entry_id,
p_object_version_number => p_rec.ee_object_version_number,
p_input_value_id1 => c_dep_sec_sal_flag_iv,
p_entry_value1 => p_rec.sec_sal_submit_flag,
p_override_user_ent_chk => 'Y',
p_effective_start_date => l_esd,
p_effective_end_date => l_eed,
p_update_warning => l_warning);
pay_jp_def_api.update_entry_os(
p_validate => false,
p_action_information_id => p_rec.action_information_id,
p_object_version_number => p_rec.object_version_number,
p_status => p_rec.status,
p_ee_object_version_number => p_rec.ee_object_version_number);
pay_element_entry_api.delete_element_entry(
p_validate => false,
p_effective_date => p_rec.effective_date,
p_datetrack_delete_mode => 'ZAP',
p_element_entry_id => p_rec.element_entry_id,
p_object_version_number => p_rec.ee_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed,
p_delete_warning => l_warning);
pay_jp_def_api.update_entry_os(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => p_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
P_ELEMENT_ENTRY_ID => p_rec.element_entry_id,
P_EE_OBJECT_VERSION_NUMBER => p_rec.ee_object_version_number);
if p_rec.datetrack_update_mode = 'CORRECTION' then
--
pay_element_entry_api.update_element_entry(
p_validate => false,
p_effective_date => p_rec.effective_date,
p_business_group_id => p_business_group_id,
p_datetrack_update_mode => p_rec.datetrack_update_mode,
p_element_entry_id => p_rec.element_entry_id,
p_object_version_number => p_rec.ee_object_version_number,
p_input_value_id1 => c_dep_sec_sal_flag_iv,
p_entry_value1 => p_rec.sec_sal_submit_flag_o,
p_override_user_ent_chk => 'Y',
p_effective_start_date => l_esd,
p_effective_end_date => l_eed,
p_update_warning => l_warning);
select object_version_number
into p_rec.ee_object_version_number
from pay_element_entries_f
where element_entry_id = p_rec.element_entry_id
and effective_end_date = p_rec.effective_date - 1;
pay_element_entry_api.delete_element_entry(
p_validate => false,
p_effective_date => p_rec.effective_date - 1,
p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
p_element_entry_id => p_rec.element_entry_id,
p_object_version_number => p_rec.ee_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed,
p_delete_warning => l_warning);
pay_jp_def_api.update_entry(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => p_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => p_rec.object_version_number,
P_STATUS => p_rec.status,
P_EE_OBJECT_VERSION_NUMBER => p_rec.ee_object_version_number);
select pa.business_group_id
from per_all_assignments_f pa
where pa.assignment_id = p_rec.assignment_id
and p_rec.effective_date
between pa.effective_start_date and pa.effective_end_date;
select *
into p_rec
from pay_jp_def_assact_v
where action_information_id = p_action_information_id
for update nowait;
fnd_message.set_name('FND', 'FND_RECORD_DELETED_ERROR');
select /*+ ORDERED */
pee.element_entry_id,
pee.effective_start_date,
pee.effective_end_date,
pee.object_version_number,
peev.input_value_id,
peev.screen_entry_value
from pay_element_links_f pel,
pay_element_entries_f pee,
pay_element_entry_values_f peev
where pel.element_type_id = c_def_elm
and pel.business_group_id + 0 = g_business_group_id
and p_effective_date
between pel.effective_start_date and pel.effective_end_date
and pee.assignment_id = p_assignment_id
and pee.element_link_id = pel.element_link_id
and p_effective_date
between pee.effective_start_date and pee.effective_end_date
and pee.entry_type = 'E'
and peev.element_entry_id = pee.element_entry_id
and peev.effective_start_date = pee.effective_start_date
and peev.effective_end_date = pee.effective_end_date;
select /*+ ORDERED */
pee.element_entry_id,
pee.effective_start_date,
pee.effective_end_date,
pee.object_version_number,
peev.screen_entry_value
from pay_link_input_values_f pliv,
pay_element_entries_f pee,
pay_element_entry_values_f peev
where pliv.input_value_id = c_dep_sec_sal_flag_iv
and p_effective_date
between pliv.effective_start_date and pliv.effective_end_date
and pee.assignment_id = p_assignment_id
and pee.element_link_id = pliv.element_link_id
and p_effective_date
between pee.effective_start_date and pee.effective_end_date
and pee.entry_type = 'E'
and peev.element_entry_id = pee.element_entry_id
and peev.input_value_id = pliv.input_value_id
and peev.effective_start_date = pee.effective_start_date
and peev.effective_end_date = pee.effective_end_date;
select /*+ ORDERED */
per.person_id,
per.business_group_id,
per.employee_number employee_number,
per.last_name last_name_kana,
per.first_name first_name_kana,
per.per_information18 last_name,
per.per_information19 first_name,
per.date_of_birth,
per.date_of_death,
per.sex,
decode(adrr.address_id, null, adrc.postal_code, adrr.postal_code) postal_code,
trim(substrb(decode(adrr.address_id, null,
adrc.address_line1 || adrc.address_line2 || adrc.address_line3,
adrr.address_line1 || adrr.address_line2 || adrr.address_line3), 1, 240)) address,
decode(adrr.address_id,null,adrc.town_or_city,adrr.town_or_city) district_code
from per_all_assignments_f asg,
per_all_people_f per,
per_addresses adrr,
per_addresses adrc
where asg.assignment_id = p_assignment_id
and p_effective_date
between asg.effective_start_date and asg.effective_end_date
and per.person_id = asg.person_id
and p_effective_date
between per.effective_start_date and per.effective_end_date
and adrr.person_id(+) = per.person_id
and adrr.address_type(+) = 'JP_R'
and p_effective_date
between adrr.date_from(+) and nvl(adrr.date_to(+), p_effective_date)
and adrc.person_id(+) = per.person_id
and adrc.address_type(+) = 'JP_C'
and p_effective_date
between adrc.date_from(+) and nvl(adrc.date_to(+), p_effective_date);
select ctr.contact_relationship_id,
ctr.contact_person_id,
ctr.contact_type
from per_contact_relationships ctr
where ctr.person_id = p_person_id
and ctr.cont_information3 = 'Y'
and p_effective_date
between nvl(ctr.date_start, p_effective_date) and nvl(ctr.date_end, p_effective_date);
select 'Y' married_flag
from dual
where exists(
select null
from per_contact_relationships ctr
where ctr.person_id = p_person_id
and ctr.contact_type = 'S'
and p_effective_date
between nvl(ctr.date_start, p_effective_date) and nvl(ctr.date_end, p_effective_date));
select /*+ ORDERED */
cei.contact_extra_info_id,
cei.effective_start_date,
cei.effective_end_date,
cei.object_version_number,
ctr.contact_relationship_id,
per.last_name LAST_NAME_KANA,
per.first_name FIRST_NAME_KANA,
per.per_information18 LAST_NAME,
per.per_information19 FIRST_NAME,
ctr.contact_type,
per.date_of_birth,
per.date_of_death,
trim(substrb(decode(adrr.address_id, null,
adrc.address_line1 || adrc.address_line2 || adrc.address_line3,
adrr.address_line1 || adrr.address_line2 || adrr.address_line3), 1, 240)) ADDRESS,
cei.cei_information8 DEP_TYPE,
cei.cei_information2 OCCUPATION,
fnd_number.canonical_to_number(cei.cei_information3) ESTIMATED_ANNUAL_INCOME,
cei.cei_information6 DISABILITY_TYPE,
cei.cei_information7 DISABILITY_DETAILS
from per_contact_relationships ctr,
per_contact_extra_info_f cei,
per_addresses adrr,
per_addresses adrc,
per_all_people_f per
where ctr.person_id = p_person_id
and cei.contact_relationship_id = ctr.contact_relationship_id
and cei.cei_information_category = 'JP_ITAX_DEPENDENT'
and p_effective_date
between cei.effective_start_date and cei.effective_end_date
and adrr.person_id(+) = ctr.contact_person_id
and adrr.address_type(+) = 'JP_R'
and p_effective_date
between adrr.date_from(+) and nvl(adrr.date_to(+), p_effective_date)
and adrc.person_id(+) = ctr.contact_person_id
and adrc.address_type(+) = 'JP_C'
and p_effective_date
between adrc.date_from(+) and nvl(adrc.date_to(+), p_effective_date)
and per.person_id = ctr.contact_person_id
/* CEI guarantees that person record exists as of effective_date */
and p_effective_date
between per.effective_start_date and per.effective_end_date
order by
decode(ctr.contact_type, 'S', 1, 2),
per.date_of_birth,
per.full_name;
select /*+ ORDERED */
cei.contact_extra_info_id,
cei.effective_start_date,
cei.effective_end_date,
cei.object_version_number,
ctr.contact_relationship_id,
per.last_name LAST_NAME_KANA,
per.first_name FIRST_NAME_KANA,
per.per_information18 LAST_NAME,
per.per_information19 FIRST_NAME,
ctr.contact_type,
per.date_of_birth,
per.date_of_death,
trim(substrb(decode(adrr.address_id, null,
adrc.address_line1 || adrc.address_line2 || adrc.address_line3,
adrr.address_line1 || adrr.address_line2 || adrr.address_line3), 1, 240)) ADDRESS,
cei.cei_information1 OCCUPATION,
-- Do not return contact_relationship_id
fnd_number.canonical_to_number(cei.cei_information5) OE_CONTACT_RELATIONSHIP_ID_O,
ctr2.contact_relationship_id OE_CONTACT_RELATIONSHIP_ID,
ctr2.contact_person_id OE_CONTACT_PERSON_ID,
ctr2.contact_type OE_CONTACT_TYPE,
trim(substrb(decode(adrr2.address_id, null,
adrc2.address_line1 || adrc2.address_line2 || adrc2.address_line3,
adrr2.address_line1 || adrr2.address_line2 || adrr2.address_line3), 1, 240)) OE_ADDRESS
from per_contact_relationships ctr,
per_contact_extra_info_f cei,
per_addresses adrr,
per_addresses adrc,
per_all_people_f per,
per_contact_relationships ctr2,
per_addresses adrr2,
per_addresses adrc2
where ctr.person_id = p_person_id
and cei.contact_relationship_id = ctr.contact_relationship_id
and cei.cei_information_category = 'JP_ITAX_DEPENDENT_ON_OTHER_EMP'
and p_effective_date
between cei.effective_start_date and cei.effective_end_date
and adrr.person_id(+) = ctr.contact_person_id
and adrr.address_type(+) = 'JP_R'
and p_effective_date
between adrr.date_from(+) and nvl(adrr.date_to(+), p_effective_date)
and adrc.person_id(+) = ctr.contact_person_id
and adrc.address_type(+) = 'JP_C'
and p_effective_date
between adrc.date_from(+) and nvl(adrc.date_to(+), p_effective_date)
and per.person_id = ctr.contact_person_id
/* CEI guarantees that person record exists as of effective_date */
and p_effective_date
between per.effective_start_date and per.effective_end_date
/* No need to check date range of CTR */
and ctr2.contact_relationship_id(+) = fnd_number.canonical_to_number(cei.cei_information5)
and adrr2.person_id(+) = ctr2.contact_person_id
and adrr2.address_type(+) = 'JP_R'
and p_effective_date
between adrr2.date_from(+) and nvl(adrr2.date_to(+), p_effective_date)
and adrc2.person_id(+) = ctr2.contact_person_id
and adrc2.address_type(+) = 'JP_C'
and p_effective_date
between adrc2.date_from(+) and nvl(adrc2.date_to(+), p_effective_date)
order by
decode(ctr.contact_type, 'S', 1, 2),
per.date_of_birth,
per.full_name;
select cei.contact_extra_info_id,
cei.effective_start_date,
cei.effective_end_date,
cei.object_version_number,
ctr.contact_relationship_id,
per.last_name LAST_NAME_KANA,
per.first_name FIRST_NAME_KANA,
per.per_information18 LAST_NAME,
per.per_information19 FIRST_NAME,
ctr.contact_type,
per.date_of_birth,
per.date_of_death,
cei.cei_information1 OCCUPATION,
cei.cei_information2 OS_SALARY_PAYER_NAME,
cei.cei_information3 OS_SALARY_PAYER_ADDRESS
from per_all_people_f per,
per_contact_extra_info_f cei,
per_contact_relationships ctr
where ctr.person_id = p_person_id
and cei.contact_relationship_id = ctr.contact_relationship_id
and cei.cei_information_category = 'JP_ITAX_DEPENDENT_ON_OTHER_PAY'
and p_effective_date
between cei.effective_start_date and cei.effective_end_date
and per.person_id = ctr.contact_person_id
/* CEI guarantees that person record exists as of effective_date */
and p_effective_date
between per.effective_start_date and per.effective_end_date
order by
decode(ctr.contact_type, 'S', 1, 2),
per.date_of_birth,
per.full_name;
select legislation_code
from per_business_groups_perf
where business_group_id = g_business_group_id;
select decode(pjsn_v.rep_district_code,null,pjal_act.address_line_1,pjal_rep.address_line_1) district_name
from per_jp_address_lookups pjal_act,
(select pjsn_act.organization_id,
pjsn_act.district_code act_district_code,
substrb(nvl(pjsn_act.report_district_code,pjsn_act.district_code),1,5) rep_district_code
from pay_jp_swot_numbers pjsn_rep,
pay_jp_swot_numbers pjsn_act
where pjsn_rep.organization_id = pjsn_act.organization_id
and pjsn_rep.district_code = nvl(pjsn_act.report_district_code,pjsn_act.district_code)) pjsn_v,
per_jp_address_lookups pjal_rep
where substrb(pjal_act.district_code,1,5) = p_district_code
and pjsn_v.organization_id (+) = nvl(l_itax_organization_id,-1)
and substrb(pjsn_v.act_district_code(+),1,5) = substrb(pjal_act.district_code,1,5)
and substrb(pjal_rep.district_code(+),1,5) = pjsn_v.rep_district_code;
l_entry_rec.datetrack_update_mode := ee_datetrack_update_mode(
l_rec.element_entry_id,
l_rec.effective_start_date,
l_rec.effective_end_date,
p_effective_date);
P_DATETRACK_UPDATE_MODE => l_entry_rec.datetrack_update_mode,
P_ELEMENT_ENTRY_ID => l_entry_rec.element_entry_id,
P_EE_OBJECT_VERSION_NUMBER => l_entry_rec.ee_object_version_number,
P_DISABILITY_TYPE => '0',
P_DISABILITY_TYPE_O => l_entry_rec.disability_type_o,
P_AGED_TYPE => '0',
P_AGED_TYPE_O => l_entry_rec.aged_type_o,
P_WIDOW_TYPE => '0',
P_WIDOW_TYPE_O => l_entry_rec.widow_type_o,
P_WORKING_STUDENT_TYPE => '0',
P_WORKING_STUDENT_TYPE_O => l_entry_rec.working_student_type_o,
P_SPOUSE_DEP_TYPE => '0',
P_SPOUSE_DEP_TYPE_O => l_entry_rec.spouse_dep_type_o,
P_SPOUSE_DISABILITY_TYPE => '0',
P_SPOUSE_DISABILITY_TYPE_O => l_entry_rec.spouse_disability_type_o,
P_NUM_DEPS => 0,
P_NUM_DEPS_O => l_entry_rec.num_deps_o,
P_NUM_AGEDS => 0,
P_NUM_AGEDS_O => l_entry_rec.num_ageds_o,
P_NUM_AGED_PARENTS_LT => 0,
P_NUM_AGED_PARENTS_LT_O => l_entry_rec.num_aged_parents_lt_o,
P_NUM_SPECIFIEDS => 0,
P_NUM_SPECIFIEDS_O => l_entry_rec.num_specifieds_o,
P_NUM_DISABLEDS => 0,
P_NUM_DISABLEDS_O => l_entry_rec.num_disableds_o,
P_NUM_SVR_DISABLEDS => 0,
P_NUM_SVR_DISABLEDS_O => l_entry_rec.num_svr_disableds_o,
P_NUM_SVR_DISABLEDS_LT => 0,
P_NUM_SVR_DISABLEDS_LT_O => l_entry_rec.num_svr_disableds_lt_o,
P_ACTION_INFORMATION_ID => l_action_information_id,
P_OBJECT_VERSION_NUMBER => l_object_version_number);
l_entry_os_rec.datetrack_update_mode := ee_datetrack_update_mode(
l_rec.element_entry_id,
l_rec.effective_start_date,
l_rec.effective_end_date,
p_effective_date);
p_datetrack_update_mode => l_entry_os_rec.datetrack_update_mode,
p_element_entry_id => l_entry_os_rec.element_entry_id,
p_ee_object_version_number => l_entry_os_rec.ee_object_version_number,
p_sec_sal_submit_flag => 'N',
p_sec_sal_submit_flag_o => l_entry_os_rec.sec_sal_submit_flag_o,
p_action_information_id => l_action_information_id,
p_object_version_number => l_object_version_number);
p_datetrack_update_mode => cei_datetrack_update_mode(
l_rec.contact_extra_info_id,
l_rec.effective_start_date,
l_rec.effective_end_date,
p_effective_date),
p_datetrack_delete_mode => cei_datetrack_delete_mode(
l_rec.contact_extra_info_id,
l_rec.effective_start_date,
l_rec.effective_end_date,
p_effective_date),
p_contact_extra_info_id => l_rec.contact_extra_info_id,
p_cei_object_version_number => l_rec.object_version_number,
p_contact_relationship_id => l_rec.contact_relationship_id,
p_last_name_kana => l_rec.last_name_kana,
p_first_name_kana => l_rec.first_name_kana,
p_last_name => l_rec.last_name,
p_first_name => l_rec.first_name,
p_contact_type => l_rec.contact_type,
p_date_of_birth => l_rec.date_of_birth,
p_date_of_death => l_rec.date_of_death,
p_address => l_rec.address,
p_change_date => null,
p_change_reason => null,
p_dep_type => nvl(l_rec.dep_type, '0'),
p_dep_type_o => l_rec.dep_type,
p_occupation => l_rec.occupation,
p_occupation_o => l_rec.occupation,
p_estimated_annual_income => l_rec.estimated_annual_income,
p_estimated_annual_income_o => l_rec.estimated_annual_income,
p_disability_type => nvl(l_rec.disability_type, '0'),
p_disability_type_o => l_rec.disability_type,
p_disability_details => l_rec.disability_details,
p_disability_details_o => l_rec.disability_details,
P_ACTION_INFORMATION_ID => l_action_information_id,
P_OBJECT_VERSION_NUMBER => l_object_version_number);
p_datetrack_update_mode => cei_datetrack_update_mode(
l_rec.contact_extra_info_id,
l_rec.effective_start_date,
l_rec.effective_end_date,
p_effective_date),
p_datetrack_delete_mode => cei_datetrack_delete_mode(
l_rec.contact_extra_info_id,
l_rec.effective_start_date,
l_rec.effective_end_date,
p_effective_date),
p_contact_extra_info_id => l_rec.contact_extra_info_id,
p_cei_object_version_number => l_rec.object_version_number,
p_contact_relationship_id => l_rec.contact_relationship_id,
p_last_name_kana => l_rec.last_name_kana,
p_first_name_kana => l_rec.first_name_kana,
p_last_name => l_rec.last_name,
p_first_name => l_rec.first_name,
p_contact_type => l_rec.contact_type,
p_date_of_birth => l_rec.date_of_birth,
p_date_of_death => l_rec.date_of_death,
p_address => l_rec.address,
p_change_date => null,
p_change_reason => null,
p_occupation => l_rec.occupation,
p_occupation_o => l_rec.occupation,
p_oe_contact_relationship_id => l_rec.oe_contact_relationship_id,
p_oe_full_name => full_name(
l_rec.oe_contact_person_id,
p_effective_date),
p_oe_contact_type => l_rec.oe_contact_type,
p_oe_address => l_rec.oe_address,
p_oe_contact_relationship_id_o => l_rec.oe_contact_relationship_id_o,
P_ACTION_INFORMATION_ID => l_action_information_id,
p_object_version_number => l_object_version_number);
p_datetrack_update_mode => cei_datetrack_update_mode(
l_rec.contact_extra_info_id,
l_rec.effective_start_date,
l_rec.effective_end_date,
p_effective_date),
p_datetrack_delete_mode => cei_datetrack_delete_mode(
l_rec.contact_extra_info_id,
l_rec.effective_start_date,
l_rec.effective_end_date,
p_effective_date),
p_contact_extra_info_id => l_rec.contact_extra_info_id,
p_cei_object_version_number => l_rec.object_version_number,
p_contact_relationship_id => l_rec.contact_relationship_id,
p_last_name_kana => l_rec.last_name_kana,
p_first_name_kana => l_rec.first_name_kana,
p_last_name => l_rec.last_name,
p_first_name => l_rec.first_name,
p_contact_type => l_rec.contact_type,
p_date_of_birth => l_rec.date_of_birth,
p_date_of_death => l_rec.date_of_death,
p_occupation => l_rec.occupation,
p_occupation_o => l_rec.occupation,
p_os_salary_payer_name => l_rec.os_salary_payer_name,
p_os_salary_payer_name_o => l_rec.os_salary_payer_name,
p_os_salary_payer_address => l_rec.os_salary_payer_address,
p_os_salary_payer_address_o => l_rec.os_salary_payer_address,
P_ACTION_INFORMATION_ID => l_action_information_id,
p_object_version_number => l_object_version_number);
delete
from pay_action_information
where action_context_id = l_assact_rec.assignment_action_id
and action_context_type = 'AAP'
and action_information_category <> 'JP_DEF_ASSACT';
pay_jp_def_api.update_assact(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => l_assact_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => l_assact_rec.object_version_number,
P_TRANSACTION_STATUS => l_assact_rec.transaction_status);
select *
from pay_jp_def_entry_v
where assignment_action_id = p_assignment_action_id
for update nowait;
select *
from pay_jp_def_entry_dep_v
where assignment_action_id = p_assignment_action_id
for update nowait;
select *
from pay_jp_def_entry_os_v
where assignment_action_id = p_assignment_action_id
for update nowait;
select *
from pay_jp_def_emp_v
where assignment_action_id = p_assignment_action_id;
select *
from pay_jp_def_dep_v
where assignment_action_id = p_assignment_action_id
and status <> 'D'
order by decode(contact_type, 'S', 1, 2), date_of_birth, last_name_kana, first_name_kana;
pay_jp_def_api.update_entry(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => l_entry_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => l_entry_rec.object_version_number,
P_DISABILITY_TYPE => l_entry_rec.disability_type,
P_AGED_TYPE => l_entry_rec.aged_type,
P_WIDOW_TYPE => l_entry_rec.widow_type,
P_WORKING_STUDENT_TYPE => l_entry_rec.working_student_type,
P_SPOUSE_DEP_TYPE => l_entry_rec.spouse_dep_type,
P_SPOUSE_DISABILITY_TYPE => l_entry_rec.spouse_disability_type,
P_NUM_DEPS => l_entry_rec.num_deps,
P_NUM_AGEDS => l_entry_rec.num_ageds,
P_NUM_AGED_PARENTS_LT => l_entry_rec.num_aged_parents_lt,
P_NUM_SPECIFIEDS => l_entry_rec.num_specifieds,
P_NUM_DISABLEDS => l_entry_rec.num_disableds,
P_NUM_SVR_DISABLEDS => l_entry_rec.num_svr_disableds,
P_NUM_SVR_DISABLEDS_LT => l_entry_rec.num_svr_disableds_lt);
pay_jp_def_api.update_entry_dep(
p_validate => false,
p_action_information_id => l_entry_dep_rec.action_information_id,
p_object_version_number => l_entry_dep_rec.object_version_number,
p_num_junior_deps => l_entry_dep_rec.num_junior_deps);
pay_jp_def_api.update_entry_os(
p_validate => false,
p_action_information_id => l_entry_os_rec.action_information_id,
p_object_version_number => l_entry_os_rec.object_version_number,
p_sec_sal_submit_flag => l_entry_os_rec.sec_sal_submit_flag);
pay_jp_def_api.update_assact(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => l_assact_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => l_assact_rec.object_version_number,
P_TRANSACTION_STATUS => l_assact_rec.transaction_status,
P_FINALIZED_DATE => l_assact_rec.finalized_date,
P_FINALIZED_BY => l_assact_rec.finalized_by,
P_USER_COMMENTS => p_user_comments);
delete
from pay_action_information
where action_context_id = l_assact_rec.assignment_action_id
and action_context_type = 'AAP'
and action_information_category <> 'JP_DEF_ASSACT';
pay_jp_def_api.update_assact(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => l_assact_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => l_assact_rec.object_version_number,
P_TRANSACTION_STATUS => l_assact_rec.transaction_status,
P_FINALIZED_DATE => l_assact_rec.finalized_date,
P_FINALIZED_BY => l_assact_rec.finalized_by,
P_ADMIN_COMMENTS => p_admin_comments);
pay_jp_def_api.update_assact(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => l_assact_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => l_assact_rec.object_version_number,
P_TRANSACTION_STATUS => l_assact_rec.transaction_status,
P_FINALIZED_DATE => l_assact_rec.finalized_date,
P_FINALIZED_BY => l_assact_rec.finalized_by,
P_ADMIN_COMMENTS => p_admin_comments);
pay_jp_def_api.update_assact(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => l_assact_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => l_assact_rec.object_version_number,
P_TRANSACTION_STATUS => l_assact_rec.transaction_status);
select aif.action_information_id,
aif.object_version_number
from pay_jp_def_assact_v aif,
pay_assignment_actions paa
where paa.payroll_action_id = l_payroll_action_id
and paa.action_status = 'C'
and aif.assignment_action_id = paa.assignment_action_id
and transaction_status = 'F';
select *
from pay_jp_def_entry_v
where assignment_action_id = p_assignment_action_id
for update nowait;
select *
from pay_jp_def_entry_dep_v
where assignment_action_id = p_assignment_action_id
for update nowait;
select *
from pay_jp_def_entry_os_v
where assignment_action_id = p_assignment_action_id
for update nowait;
select *
from pay_jp_def_dep_v
where assignment_action_id = p_assignment_action_id
and status = 'D'
for update nowait;
select *
from pay_jp_def_dep_v
where assignment_action_id = p_assignment_action_id
and status = 'Q'
for update nowait;
select *
from pay_jp_def_dep_v
where assignment_action_id = p_assignment_action_id
and status = 'I'
for update nowait;
select *
from pay_jp_def_dep_oe_v
where assignment_action_id = p_assignment_action_id
and status = 'D'
for update nowait;
select *
from pay_jp_def_dep_oe_v
where assignment_action_id = p_assignment_action_id
and status = 'Q'
for update nowait;
select *
from pay_jp_def_dep_oe_v
where assignment_action_id = p_assignment_action_id
and status = 'I'
for update nowait;
select *
from pay_jp_def_dep_os_v
where assignment_action_id = p_assignment_action_id
and status = 'D'
for update nowait;
select *
from pay_jp_def_dep_os_v
where assignment_action_id = p_assignment_action_id
and status = 'Q'
for update nowait;
select *
from pay_jp_def_dep_os_v
where assignment_action_id = p_assignment_action_id
and status = 'I'
for update nowait;
insert_session(l_assact_rec.effective_date);
delete_session;
pay_jp_def_api.update_assact(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => l_assact_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => l_assact_rec.object_version_number,
P_TRANSFER_STATUS => l_assact_rec.transfer_status);
select assact.action_information_id,
assact.object_version_number,
per.full_name,
asg.assignment_number
from per_all_people_f per,
per_all_assignments_f asg,
pay_jp_def_assact_v assact,
pay_assignment_actions paa
where paa.payroll_action_id = l_payroll_action_id
and paa.action_status = 'C'
and assact.assignment_action_id = paa.assignment_action_id
and assact.transaction_status = 'A'
and assact.transfer_status = 'U'
and asg.assignment_id = assact.assignment_id
and assact.effective_date
between asg.effective_start_date and asg.effective_end_date
and per.person_id = asg.person_id
and assact.effective_date
between per.effective_start_date and per.effective_end_date;
select effective_date
into l_effective_date
from pay_jp_def_pact_v
where payroll_action_id = l_payroll_action_id;
insert_session(l_effective_date);
delete_session;
select *
from pay_jp_def_entry_v
where assignment_action_id = p_assignment_action_id
for update nowait;
select *
from pay_jp_def_entry_dep_v
where assignment_action_id = p_assignment_action_id
for update nowait;
select *
from pay_jp_def_entry_os_v
where assignment_action_id = p_assignment_action_id
for update nowait;
select *
from pay_jp_def_dep_v
where assignment_action_id = p_assignment_action_id
and status = 'I'
for update nowait;
select *
from pay_jp_def_dep_v
where assignment_action_id = p_assignment_action_id
and status = 'U'
for update nowait;
select *
from pay_jp_def_dep_v
where assignment_action_id = p_assignment_action_id
and status = 'D'
for update nowait;
select *
from pay_jp_def_dep_oe_v
where assignment_action_id = p_assignment_action_id
and status = 'I'
for update nowait;
select *
from pay_jp_def_dep_oe_v
where assignment_action_id = p_assignment_action_id
and status = 'U'
for update nowait;
select *
from pay_jp_def_dep_oe_v
where assignment_action_id = p_assignment_action_id
and status = 'D'
for update nowait;
select *
from pay_jp_def_dep_os_v
where assignment_action_id = p_assignment_action_id
and status = 'I'
for update nowait;
select *
from pay_jp_def_dep_os_v
where assignment_action_id = p_assignment_action_id
and status = 'U'
for update nowait;
select *
from pay_jp_def_dep_os_v
where assignment_action_id = p_assignment_action_id
and status = 'D'
for update nowait;
insert_session(l_assact_rec.effective_date);
delete_session;
pay_jp_def_api.update_assact(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => l_assact_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => l_assact_rec.object_version_number,
P_TRANSFER_STATUS => l_assact_rec.transfer_status);
select v.*
from pay_jp_def_entry_v v
where v.assignment_action_id = p_assignment_action_id
and exists(
select null
from pay_element_entries_f pee
where pee.element_entry_id = v.element_entry_id
and p_expiry_date + 1
between pee.effective_start_date and pee.effective_end_date)
for update nowait;
select v.*
from pay_jp_def_entry_os_v v
where v.assignment_action_id = p_assignment_action_id
and exists(
select null
from pay_element_entries_f pee
where pee.element_entry_id = v.element_entry_id
and p_expiry_date + 1
between pee.effective_start_date and pee.effective_end_date)
for update nowait;
select v.*
from pay_jp_def_dep_v v
where v.assignment_action_id = p_assignment_action_id
and status <> 'D'
and exists(
select null
from per_contact_extra_info_f cei
where cei.contact_extra_info_id = v.contact_extra_info_id
and p_expiry_date + 1
between cei.effective_start_date and cei.effective_end_date)
for update nowait;
select v.*
from pay_jp_def_dep_oe_v v
where v.assignment_action_id = p_assignment_action_id
and status <> 'D'
and exists(
select null
from per_contact_extra_info_f cei
where cei.contact_extra_info_id = v.contact_extra_info_id
and p_expiry_date + 1
between cei.effective_start_date and cei.effective_end_date)
for update nowait;
select v.*
from pay_jp_def_dep_os_v v
where v.assignment_action_id = p_assignment_action_id
and status <> 'D'
and exists(
select null
from per_contact_extra_info_f cei
where cei.contact_extra_info_id = v.contact_extra_info_id
and p_expiry_date + 1
between cei.effective_start_date and cei.effective_end_date)
for update nowait;
insert_session(p_expiry_date);
pay_element_entry_api.delete_element_entry(
p_validate => false,
p_effective_date => p_expiry_date,
p_datetrack_delete_mode => 'DELETE',
p_element_entry_id => l_rec.element_entry_id,
p_object_version_number => l_rec.ee_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed,
p_delete_warning => l_warning);
pay_jp_def_api.update_entry(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => l_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => l_rec.object_version_number,
P_EE_OBJECT_VERSION_NUMBER => l_rec.ee_object_version_number);
pay_element_entry_api.delete_element_entry(
p_validate => false,
p_effective_date => p_expiry_date,
p_datetrack_delete_mode => 'DELETE',
p_element_entry_id => l_rec.element_entry_id,
p_object_version_number => l_rec.ee_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed,
p_delete_warning => l_warning);
pay_jp_def_api.update_entry_os(
p_validate => false,
p_action_information_id => l_rec.action_information_id,
p_object_version_number => l_rec.object_version_number,
p_ee_object_version_number => l_rec.ee_object_version_number);
hr_contact_extra_info_api.delete_contact_extra_info(
p_validate => false,
p_effective_date => p_expiry_date,
p_datetrack_delete_mode => 'DELETE',
p_contact_extra_info_id => l_rec.contact_extra_info_id,
p_object_version_number => l_rec.cei_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
pay_jp_def_api.update_dep(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => l_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => l_rec.object_version_number,
P_CEI_OBJECT_VERSION_NUMBER => l_rec.cei_object_version_number);
hr_contact_extra_info_api.delete_contact_extra_info(
p_validate => false,
p_effective_date => p_expiry_date,
p_datetrack_delete_mode => 'DELETE',
p_contact_extra_info_id => l_rec.contact_extra_info_id,
p_object_version_number => l_rec.cei_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
pay_jp_def_api.update_dep_oe(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => l_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => l_rec.object_version_number,
P_CEI_OBJECT_VERSION_NUMBER => l_rec.cei_object_version_number);
hr_contact_extra_info_api.delete_contact_extra_info(
p_validate => false,
p_effective_date => p_expiry_date,
p_datetrack_delete_mode => 'DELETE',
p_contact_extra_info_id => l_rec.contact_extra_info_id,
p_object_version_number => l_rec.cei_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
pay_jp_def_api.update_dep_os(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => l_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => l_rec.object_version_number,
P_CEI_OBJECT_VERSION_NUMBER => l_rec.cei_object_version_number);
delete_session;
pay_jp_def_api.update_assact(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => l_assact_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => l_assact_rec.object_version_number,
P_TRANSFER_STATUS => l_assact_rec.transfer_status,
P_EXPIRY_DATE => l_assact_rec.expiry_date);
select v.*
from pay_jp_def_entry_v v
where v.assignment_action_id = p_assignment_action_id
and exists(
select null
from pay_element_entries_f pee
where pee.element_entry_id = v.element_entry_id
and p_expiry_date
between pee.effective_start_date and pee.effective_end_date)
for update nowait;
select v.*
from pay_jp_def_entry_os_v v
where v.assignment_action_id = p_assignment_action_id
and exists(
select null
from pay_element_entries_f pee
where pee.element_entry_id = v.element_entry_id
and p_expiry_date
between pee.effective_start_date and pee.effective_end_date)
for update nowait;
select v.*
from pay_jp_def_dep_v v
where v.assignment_action_id = p_assignment_action_id
and status <> 'D'
and exists(
select null
from per_contact_extra_info_f cei
where cei.contact_extra_info_id = v.contact_extra_info_id
and p_expiry_date
between cei.effective_start_date and cei.effective_end_date)
for update nowait;
select v.*
from pay_jp_def_dep_oe_v v
where v.assignment_action_id = p_assignment_action_id
and status <> 'D'
and exists(
select null
from per_contact_extra_info_f cei
where cei.contact_extra_info_id = v.contact_extra_info_id
and p_expiry_date
between cei.effective_start_date and cei.effective_end_date)
for update nowait;
select v.*
from pay_jp_def_dep_os_v v
where v.assignment_action_id = p_assignment_action_id
and status <> 'D'
and exists(
select null
from per_contact_extra_info_f cei
where cei.contact_extra_info_id = v.contact_extra_info_id
and p_expiry_date
between cei.effective_start_date and cei.effective_end_date)
for update nowait;
insert_session(l_assact_rec.expiry_date);
pay_element_entry_api.delete_element_entry(
p_validate => false,
p_effective_date => l_assact_rec.expiry_date,
p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
p_element_entry_id => l_rec.element_entry_id,
p_object_version_number => l_rec.ee_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed,
p_delete_warning => l_warning);
pay_jp_def_api.update_entry(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => l_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => l_rec.object_version_number,
P_EE_OBJECT_VERSION_NUMBER => l_rec.ee_object_version_number);
pay_element_entry_api.delete_element_entry(
p_validate => false,
p_effective_date => l_assact_rec.expiry_date,
p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
p_element_entry_id => l_rec.element_entry_id,
p_object_version_number => l_rec.ee_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed,
p_delete_warning => l_warning);
pay_jp_def_api.update_entry_os(
p_validate => false,
p_action_information_id => l_rec.action_information_id,
p_object_version_number => l_rec.object_version_number,
p_ee_object_version_number => l_rec.ee_object_version_number);
hr_contact_extra_info_api.delete_contact_extra_info(
p_validate => false,
p_effective_date => l_assact_rec.expiry_date,
p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
p_contact_extra_info_id => l_rec.contact_extra_info_id,
p_object_version_number => l_rec.cei_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
pay_jp_def_api.update_dep(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => l_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => l_rec.object_version_number,
P_CEI_OBJECT_VERSION_NUMBER => l_rec.cei_object_version_number);
hr_contact_extra_info_api.delete_contact_extra_info(
p_validate => false,
p_effective_date => l_assact_rec.expiry_date,
p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
p_contact_extra_info_id => l_rec.contact_extra_info_id,
p_object_version_number => l_rec.cei_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
pay_jp_def_api.update_dep_oe(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => l_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => l_rec.object_version_number,
P_CEI_OBJECT_VERSION_NUMBER => l_rec.cei_object_version_number);
hr_contact_extra_info_api.delete_contact_extra_info(
p_validate => false,
p_effective_date => l_assact_rec.expiry_date,
p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE',
p_contact_extra_info_id => l_rec.contact_extra_info_id,
p_object_version_number => l_rec.cei_object_version_number,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
pay_jp_def_api.update_dep_os(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => l_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => l_rec.object_version_number,
P_CEI_OBJECT_VERSION_NUMBER => l_rec.cei_object_version_number);
delete_session;
pay_jp_def_api.update_assact(
P_VALIDATE => false,
P_ACTION_INFORMATION_ID => l_assact_rec.action_information_id,
P_OBJECT_VERSION_NUMBER => l_assact_rec.object_version_number,
P_TRANSFER_STATUS => l_assact_rec.transfer_status,
P_EXPIRY_DATE => l_assact_rec.expiry_date);
procedure delete_unfinalized(
errbuf out nocopy varchar2,
retcode out nocopy varchar2,
p_payroll_action_id in varchar2)
is
l_payroll_action_id number := fnd_number.canonical_to_number(p_payroll_action_id);
select paa.assignment_action_id
from pay_jp_def_assact_v aif,
pay_assignment_actions paa
where paa.payroll_action_id = l_payroll_action_id
and aif.assignment_action_id(+) = paa.assignment_action_id
and nvl(aif.transaction_status, 'U') not in ('F', 'A');
end delete_unfinalized;