[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pay_action_information_s.nextval
into l_action_information_id
from dual;
insert into pay_action_information(
action_information_id,
object_version_number,
action_context_id,
action_context_type,
effective_date,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10,
action_information11,
action_information12,
action_information13,
action_information14,
action_information15,
action_information16,
action_information17,
action_information18,
action_information19,
action_information20,
action_information21,
action_information22,
action_information23,
action_information24,
action_information25,
action_information26,
action_information27,
action_information28,
action_information29,
action_information30)
select p_action_information_id,
p_object_version_number,
p_action_context_id,
action_context_type,
p_effective_date,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10,
action_information11,
action_information12,
action_information13,
action_information14,
action_information15,
action_information16,
action_information17,
action_information18,
action_information19,
action_information20,
action_information21,
action_information22,
action_information23,
action_information24,
action_information25,
action_information26,
action_information27,
action_information28,
action_information29,
action_information30
from pay_action_information
where action_context_id = p_action_context_id_o
and action_context_type = p_action_context_type
and action_information_category = p_action_information_category;
insert into pay_action_information(
action_information_id,
object_version_number,
action_context_id,
action_context_type,
effective_date,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10,
action_information11,
action_information12,
action_information13,
action_information14,
action_information15,
action_information16,
action_information17,
action_information18,
action_information19,
action_information20,
action_information21,
action_information22,
action_information23,
action_information24,
action_information25,
action_information26,
action_information27,
action_information28,
action_information29,
action_information30)
select p_action_information_id,
p_object_version_number,
p_action_context_id,
action_context_type,
p_effective_date,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10,
action_information11,
action_information12,
action_information13,
action_information14,
action_information15,
action_information16,
action_information17,
action_information18,
action_information19,
action_information20,
action_information21,
action_information22,
action_information23,
action_information24,
action_information25,
action_information26,
action_information27,
action_information28,
action_information29,
action_information30
from pay_action_information
where action_context_id = p_action_context_id_o
and action_context_type = p_action_context_type
and action_information_category = p_action_information_category
and action_information1 = p_type;
insert into pay_action_information(
action_information_id,
object_version_number,
action_context_id,
action_context_type,
effective_date,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10,
action_information11,
action_information12,
action_information13,
action_information14,
action_information15,
action_information16,
action_information17,
action_information18,
action_information19,
action_information20,
action_information21,
action_information22,
action_information23,
action_information24,
action_information25,
action_information26,
action_information27,
action_information28,
action_information29,
action_information30)
select p_action_information_id,
p_object_version_number,
p_action_context_id,
action_context_type,
p_effective_date,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10,
action_information11,
action_information12,
action_information13,
action_information14,
action_information15,
action_information16,
action_information17,
action_information18,
action_information19,
action_information20,
action_information21,
action_information22,
action_information23,
action_information24,
action_information25,
action_information26,
action_information27,
action_information28,
action_information29,
action_information30
from pay_action_information
where action_context_id = p_action_context_id_o
and action_context_type = p_action_context_type
and action_information_category = p_action_information_category
and action_information1 = fnd_number.number_to_canonical(p_item_pay_id)
and action_information3 = p_item_type
and action_information4 = fnd_number.number_to_canonical(p_item_id);
select fnd_number.canonical_to_number(action_information1) item_pay_id,
action_information3 item_pay_type,
fnd_number.canonical_to_number(action_information4) item_id
from pay_action_information
where action_context_id = p_action_context_id_o
and action_information_category = p_action_information_category
and action_context_type = 'AAP'
order by
fnd_number.canonical_to_number(action_information2) desc,
fnd_number.canonical_to_number(action_information5) desc;
insert into pay_action_information(
action_information_id,
object_version_number,
action_context_id,
action_context_type,
effective_date,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4)
values(
p_action_information_id,
p_object_version_number,
p_payroll_action_id,
p_action_context_type,
p_effective_date,
p_action_information_category,
p_status,
fnd_number.number_to_canonical(p_itax_org_id),
fnd_number.number_to_canonical(p_archive_sequence),
fnd_number.number_to_canonical(p_payroll_action_id_o));
insert into pay_action_information(
action_information_id,
object_version_number,
action_context_id,
action_context_type,
effective_date,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10)
values(
p_action_information_id,
p_object_version_number,
p_payroll_action_id,
p_action_context_type,
p_effective_date,
p_action_information_category,
fnd_number.number_to_canonical(p_itax_org_id),
p_name,
p_name_kana,
p_address1,
p_address2,
p_address_kana1,
p_address_kana2,
p_phone,
p_serial1,
p_serial2);
insert into pay_action_information(
action_information_id,
object_version_number,
action_context_id,
action_context_type,
effective_date,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9)
values(
p_action_information_id,
p_object_version_number,
p_assignment_action_id,
p_action_context_type,
p_effective_date,
p_assignment_id,
p_action_information_category,
p_status,
fnd_number.number_to_canonical(p_proc_assignment_action_id),
fnd_number.number_to_canonical(p_organization_id),
fnd_number.number_to_canonical(p_job_id),
fnd_number.number_to_canonical(p_payroll_id),
fnd_number.number_to_canonical(p_location_id),
fnd_date.date_to_canonical(p_termination_date),
p_leaving_reason,
fnd_number.number_to_canonical(p_arch_assignment_action_id_o));
procedure update_assact(
p_assignment_action_id in number,
p_effective_date in date,
p_arch_assignment_action_id_o in number,
p_proc_assignment_action_id in number,
p_proc_assact_date_o in date,
p_status in varchar2,
p_organization_id in number,
p_job_id in number,
p_payroll_id in number,
p_location_id in number,
p_termination_date in date,
p_leaving_reason in varchar2,
p_action_information_id out nocopy number,
p_object_version_number out nocopy number)
is
--
l_proc varchar2(80) := c_package||'update_assact';
update pay_action_information
set object_version_number = p_object_version_number,
action_information1 = p_status,
action_information9 = fnd_number.number_to_canonical(p_arch_assignment_action_id_o)
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_ASSACT';
update pay_action_information
set object_version_number = p_object_version_number,
action_information2 = decode(action_information2,
fnd_number.number_to_canonical(p_proc_assignment_action_id),
action_information2,
fnd_number.number_to_canonical(p_proc_assignment_action_id)),
action_information3 = decode(nvl(action_information3,'X'),
nvl(fnd_number.number_to_canonical(p_organization_id),'X'),
action_information3,
fnd_number.number_to_canonical(p_organization_id)),
action_information4 = decode(nvl(action_information4,'X'),
nvl(fnd_number.number_to_canonical(p_job_id),'X'),
action_information4,
fnd_number.number_to_canonical(p_job_id)),
action_information5 = decode(nvl(action_information5,'X'),
nvl(fnd_number.number_to_canonical(p_payroll_id),'X'),
action_information5,
fnd_number.number_to_canonical(p_payroll_id)),
action_information6 = decode(nvl(action_information6,'X'),
nvl(fnd_number.number_to_canonical(p_location_id),'X'),
action_information6,
fnd_number.number_to_canonical(p_location_id)),
action_information7 = decode(nvl(action_information7,'X'),
nvl(fnd_date.date_to_canonical(p_termination_date),'X'),
action_information7,
fnd_date.date_to_canonical(p_termination_date)),
action_information8 = decode(nvl(action_information8,'X'),
nvl(p_leaving_reason,'X'),
action_information8,
p_leaving_reason)
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_ASSACT'
and (action_information2
<> fnd_number.number_to_canonical(p_proc_assignment_action_id)
or nvl(action_information3,'X')
<> nvl(fnd_number.number_to_canonical(p_organization_id),'X')
or nvl(action_information4,'X')
<> nvl(fnd_number.number_to_canonical(p_job_id),'X')
or nvl(action_information5,'X')
<> nvl(fnd_number.number_to_canonical(p_payroll_id),'X')
or nvl(action_information6,'X')
<> nvl(fnd_number.number_to_canonical(p_location_id),'X')
or nvl(action_information7,'X')
<> nvl(fnd_date.date_to_canonical(p_termination_date),'X')
or nvl(action_information8,'X')
<> nvl(p_leaving_reason,'X'));
end update_assact;
update pay_action_information
set object_version_number = p_object_version_number,
action_information1 = p_status,
action_information2 = fnd_number.number_to_canonical(p_proc_assignment_action_id),
action_information9 = fnd_number.number_to_canonical(p_arch_assignment_action_id_o)
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_ASSACT';
insert into pay_action_information(
action_information_id,
object_version_number,
action_context_id,
action_context_type,
effective_date,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8)
values(
p_action_information_id,
p_object_version_number,
p_assignment_action_id,
p_action_context_type,
p_effective_date,
p_assignment_id,
p_action_information_category,
fnd_number.number_to_canonical(p_person_id),
p_employee_number,
p_last_name,
p_first_name,
p_last_name_kana,
p_first_name_kana,
p_sex,
fnd_date.date_to_canonical(p_birth_date));
procedure update_emp(
p_assignment_action_id in number,
p_effective_date in date,
p_arch_assignment_action_id_o in number,
p_proc_assignment_action_id in number,
p_proc_assact_date_o in date,
p_person_id in number,
p_employee_number in varchar2,
p_last_name in varchar2,
p_first_name in varchar2,
p_last_name_kana in varchar2,
p_first_name_kana in varchar2,
p_sex in varchar2,
p_birth_date in date,
p_action_information_id out nocopy number,
p_object_version_number out nocopy number)
is
--
l_proc varchar2(80) := c_package||'update_emp';
update pay_action_information
set object_version_number = p_object_version_number,
action_information1 = decode(nvl(action_information1,'X'),
nvl(fnd_number.number_to_canonical(p_person_id),'X'),
action_information1,
fnd_number.number_to_canonical(p_person_id)),
action_information2 = decode(nvl(action_information2,'X'),
nvl(p_employee_number,'X'),
action_information2,
p_employee_number),
action_information3 = decode(nvl(action_information3,'X'),
nvl(p_last_name,'X'),
action_information3,
p_last_name),
action_information4 = decode(nvl(action_information4,'X'),
nvl(p_first_name,'X'),
action_information4,
p_first_name),
action_information5 = decode(nvl(action_information5,'X'),
nvl(p_last_name_kana,'X'),
action_information5,
p_last_name_kana),
action_information6 = decode(nvl(action_information6,'X'),
nvl(p_first_name_kana,'X'),
action_information6,
p_first_name_kana),
action_information7 = decode(nvl(action_information7,'X'),
nvl(p_sex,'X'),
action_information7,
p_sex),
action_information8 = decode(nvl(action_information8,'X'),
nvl(fnd_date.date_to_canonical(p_birth_date),'X'),
action_information8,
fnd_date.date_to_canonical(p_birth_date))
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_EMP'
and (nvl(action_information1,'X')
<> nvl(fnd_number.number_to_canonical(p_person_id),'X')
or nvl(action_information2,'X')
<> nvl(p_employee_number,'X')
or nvl(action_information3,'X')
<> nvl(p_last_name,'X')
or nvl(action_information4,'X')
<> nvl(p_first_name,'X')
or nvl(action_information5,'X')
<> nvl(p_last_name_kana,'X')
or nvl(action_information6,'X')
<> nvl(p_first_name_kana,'X')
or nvl(action_information7,'X')
<> nvl(p_sex,'X')
or nvl(action_information8,'X')
<> nvl(fnd_date.date_to_canonical(p_birth_date),'X'));
end update_emp;
insert into pay_action_information(
action_information_id,
object_version_number,
action_context_id,
action_context_type,
effective_date,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10)
values(
p_action_information_id,
p_object_version_number,
p_assignment_action_id,
p_action_context_type,
p_effective_date,
p_assignment_id,
p_action_information_category,
fnd_number.number_to_canonical(p_address_id),
p_address_jp,
p_postal_code,
p_district_code,
p_address1,
p_address2,
p_address_kana1,
p_address_kana2,
p_phone1,
p_phone2);
procedure update_emp_add(
p_assignment_action_id in number,
p_effective_date in date,
p_arch_assignment_action_id_o in number,
p_proc_assignment_action_id in number,
p_proc_assact_date_o in date,
p_address_id in number,
p_address_jp in varchar2,
p_postal_code in varchar2,
p_district_code in varchar2,
p_address1 in varchar2,
p_address2 in varchar2,
p_address_kana1 in varchar2,
p_address_kana2 in varchar2,
p_phone1 in varchar2,
p_phone2 in varchar2,
p_action_information_id out nocopy number,
p_object_version_number out nocopy number)
is
--
l_proc varchar2(80) := c_package||'update_emp_add';
update pay_action_information
set object_version_number = p_object_version_number,
action_information1 = decode(nvl(action_information1,'X'),
nvl(fnd_number.number_to_canonical(p_address_id),'X'),
action_information1,
fnd_number.number_to_canonical(p_address_id)),
action_information2 = decode(nvl(action_information2,'X'),
nvl(p_address_jp,'X'),
action_information2,
p_address_jp),
action_information3 = decode(nvl(action_information3,'X'),
nvl(p_postal_code,'X'),
action_information3,
p_postal_code),
action_information4 = decode(nvl(action_information4,'X'),
nvl(p_district_code,'X'),
action_information4,
p_district_code),
action_information5 = decode(nvl(action_information5,'X'),
nvl(p_address1,'X'),
action_information5,
p_address1),
action_information6 = decode(nvl(action_information6,'X'),
nvl(p_address2,'X'),
action_information6,
p_address2),
action_information7 = decode(nvl(action_information7,'X'),
nvl(p_address_kana1,'X'),
action_information7,
p_address_kana1),
action_information8 = decode(nvl(action_information8,'X'),
nvl(p_address_kana2,'X'),
action_information8,
p_address_kana2),
action_information9 = decode(nvl(action_information9,'X'),
nvl(p_phone1,'X'),
action_information9,
p_phone1),
action_information10 = decode(nvl(action_information10,'X'),
nvl(p_phone2,'X'),
action_information10,
p_phone2)
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_EMP_ADD'
and (nvl(action_information1,'X')
<> nvl(fnd_number.number_to_canonical(p_address_id),'X')
or nvl(action_information2,'X')
<> nvl(p_address_jp,'X')
or nvl(action_information3,'X')
<> nvl(p_postal_code,'X')
or nvl(action_information4,'X')
<> nvl(p_district_code,'X')
or nvl(action_information5,'X')
<> nvl(p_address1,'X')
or nvl(action_information6,'X')
<> nvl(p_address2,'X')
or nvl(action_information7,'X')
<> nvl(p_address_kana1,'X')
or nvl(action_information8,'X')
<> nvl(p_address_kana2,'X')
or nvl(action_information9,'X')
<> nvl(p_phone1,'X')
or nvl(action_information10,'X')
<> nvl(p_phone2,'X'));
end update_emp_add;
insert into pay_action_information(
action_information_id,
object_version_number,
action_context_id,
action_context_type,
effective_date,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5)
values(
p_action_information_id,
p_object_version_number,
p_assignment_action_id,
p_action_context_type,
p_effective_date,
p_assignment_id,
p_action_information_category,
fnd_date.date_to_canonical(p_hire_date),
p_organization_name,
p_job_name,
p_payroll_name,
p_location_name);
procedure update_emp_ass(
p_assignment_action_id in number,
p_effective_date in date,
p_arch_assignment_action_id_o in number,
p_proc_assignment_action_id in number,
p_proc_assact_date_o in date,
p_hire_date in date,
p_organization_name in varchar2,
p_job_name in varchar2,
p_payroll_name in varchar2,
p_location_name in varchar2,
p_action_information_id out nocopy number,
p_object_version_number out nocopy number)
is
--
l_proc varchar2(80) := c_package||'update_emp_ass';
update pay_action_information
set object_version_number = p_object_version_number,
action_information1 = decode(nvl(action_information1,'X'),
nvl(fnd_date.date_to_canonical(p_hire_date),'X'),
action_information1,
fnd_date.date_to_canonical(p_hire_date)),
action_information2 = decode(nvl(action_information2,'X'),
nvl(p_organization_name,'X'),
action_information2,
p_organization_name),
action_information3 = decode(nvl(action_information3,'X'),
nvl(p_job_name,'X'),
action_information3,
p_job_name),
action_information4 = decode(nvl(action_information4,'X'),
nvl(p_payroll_name,'X'),
action_information4,
p_payroll_name),
action_information5 = decode(nvl(action_information5,'X'),
nvl(p_location_name,'X'),
action_information5,
p_location_name)
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_EMP_ASS'
and (nvl(action_information1,'X')
<> nvl(fnd_date.date_to_canonical(p_hire_date),'X')
or nvl(action_information2,'X')
<> nvl(p_organization_name,'X')
or nvl(action_information3,'X')
<> nvl(p_job_name,'X')
or nvl(action_information4,'X')
<> nvl(p_payroll_name,'X')
or nvl(action_information5,'X')
<> nvl(p_location_name,'X'));
end update_emp_ass;
insert into pay_action_information(
action_information_id,
object_version_number,
action_context_id,
action_context_type,
effective_date,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10,
action_information11,
action_information12,
action_information13)
values(
p_action_information_id,
p_object_version_number,
p_assignment_action_id,
p_action_context_type,
p_effective_date,
p_assignment_id,
p_action_information_category,
fnd_number.number_to_canonical(p_itax_org_id),
p_itax_type,
fnd_number.number_to_canonical(p_hi_org_id),
p_hi_number,
fnd_number.number_to_canonical(p_wp_org_id),
p_wp_number,
fnd_number.number_to_canonical(p_wpf_org_id),
p_wpf_number,
fnd_number.number_to_canonical(p_ei_org_id),
p_ei_number,
p_nres_flag,
fnd_date.date_to_canonical(p_nres_start_date),
fnd_date.date_to_canonical(p_projected_res_date));
procedure update_emp_si(
p_assignment_action_id in number,
p_effective_date in date,
p_arch_assignment_action_id_o in number,
p_proc_assignment_action_id in number,
p_proc_assact_date_o in date,
p_itax_org_id in number,
p_itax_type in varchar2,
p_hi_org_id in number,
p_hi_number in varchar2,
p_wp_org_id in number,
p_wp_number in varchar2,
p_wpf_org_id in number,
p_wpf_number in varchar2,
p_ei_org_id in number,
p_ei_number in varchar2,
p_nres_flag in varchar2,
p_nres_start_date in date,
p_projected_res_date in date,
p_action_information_id out nocopy number,
p_object_version_number out nocopy number)
is
--
l_proc varchar2(80) := c_package||'update_emp_si';
update pay_action_information
set object_version_number = p_object_version_number,
action_information1 = decode(nvl(action_information1,'X'),
nvl(fnd_number.number_to_canonical(p_itax_org_id),'X'),
action_information1,
fnd_number.number_to_canonical(p_itax_org_id)),
action_information2 = decode(nvl(action_information2,'X'),
nvl(p_itax_type,'X'),
action_information2,
p_itax_type),
action_information3 = decode(nvl(action_information3,'X'),
nvl(fnd_number.number_to_canonical(p_hi_org_id),'X'),
action_information3,
fnd_number.number_to_canonical(p_hi_org_id)),
action_information4 = decode(nvl(action_information4,'X'),
nvl(p_hi_number,'X'),
action_information4,
p_hi_number),
action_information5 = decode(nvl(action_information5,'X'),
nvl(fnd_number.number_to_canonical(p_wp_org_id),'X'),
action_information5,
fnd_number.number_to_canonical(p_wp_org_id)),
action_information6 = decode(nvl(action_information6,'X'),
nvl(p_wp_number,'X'),
action_information6,
p_wp_number),
action_information7 = decode(nvl(action_information7,'X'),
nvl(fnd_number.number_to_canonical(p_wpf_org_id),'X'),
action_information7,
fnd_number.number_to_canonical(p_wpf_org_id)),
action_information8 = decode(nvl(action_information8,'X'),
nvl(p_wpf_number,'X'),
action_information8,
p_wpf_number),
action_information9 = decode(nvl(action_information9,'X'),
nvl(fnd_number.number_to_canonical(p_ei_org_id),'X'),
action_information9,
fnd_number.number_to_canonical(p_ei_org_id)),
action_information10 = decode(nvl(action_information10,'X'),
nvl(p_ei_number,'X'),
action_information10,
p_ei_number),
action_information11 = decode(nvl(action_information11,'X'),
nvl(p_nres_flag,'X'),
action_information11,
p_nres_flag),
action_information12 = decode(nvl(action_information12,'X'),
nvl(fnd_date.date_to_canonical(p_nres_start_date),'X'),
action_information12,
fnd_date.date_to_canonical(p_nres_start_date)),
action_information13 = decode(nvl(action_information13,'X'),
nvl(fnd_date.date_to_canonical(p_projected_res_date),'X'),
action_information13,
fnd_date.date_to_canonical(p_projected_res_date))
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_EMP_SI'
and (nvl(action_information1,'X')
<> nvl(fnd_number.number_to_canonical(p_itax_org_id),'X')
or nvl(action_information2,'X')
<> nvl(p_itax_type,'X')
or nvl(action_information3,'X')
<> nvl(fnd_number.number_to_canonical(p_hi_org_id),'X')
or nvl(action_information4,'X')
<> nvl(p_hi_number,'X')
or nvl(action_information5,'X')
<> nvl(fnd_number.number_to_canonical(p_wp_org_id),'X')
or nvl(action_information6,'X')
<> nvl(p_wp_number,'X')
or nvl(action_information7,'X')
<> nvl(fnd_number.number_to_canonical(p_wpf_org_id),'X')
or nvl(action_information8,'X')
<> nvl(p_wpf_number,'X')
or nvl(action_information9,'X')
<> nvl(fnd_number.number_to_canonical(p_ei_org_id),'X')
or nvl(action_information10,'X')
<> nvl(p_ei_number,'X')
or nvl(action_information11,'X')
<> nvl(p_nres_flag,'X')
or nvl(action_information12,'X')
<> nvl(fnd_date.date_to_canonical(p_nres_start_date),'X')
or nvl(action_information13,'X')
<> nvl(fnd_date.date_to_canonical(p_projected_res_date),'X'));
end update_emp_si;
insert into pay_action_information(
action_information_id,
object_version_number,
action_context_id,
action_context_type,
effective_date,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10,
action_information11,
action_information12,
action_information13)
values(
p_action_information_id,
p_object_version_number,
p_assignment_action_id,
p_action_context_type,
p_effective_date,
p_assignment_id,
p_action_information_category,
p_category,
p_col_mth1,
p_col_mth2,
p_col_mth3,
p_col_mth4,
p_col_mth5,
p_col_mth6,
p_col_mth7,
p_col_mth8,
p_col_mth9,
p_col_mth10,
p_col_mth11,
p_col_mth12);
procedure update_sal_act(
p_assignment_action_id in number,
p_effective_date in date,
p_arch_assignment_action_id_o in number,
p_category in varchar2,
p_col_mth1 in varchar2,
p_col_mth2 in varchar2,
p_col_mth3 in varchar2,
p_col_mth4 in varchar2,
p_col_mth5 in varchar2,
p_col_mth6 in varchar2,
p_col_mth7 in varchar2,
p_col_mth8 in varchar2,
p_col_mth9 in varchar2,
p_col_mth10 in varchar2,
p_col_mth11 in varchar2,
p_col_mth12 in varchar2,
p_action_information_id out nocopy number,
p_object_version_number out nocopy number)
is
--
l_proc varchar2(80) := c_package||'update_sal_act';
update pay_action_information
set object_version_number = p_object_version_number,
action_information2 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'01',p_col_mth1,
decode(nvl(p_col_mth1,'X'),
'X',action_information2,
decode(nvl(action_information2,'X'),
nvl(p_col_mth1,'X'),action_information2,
p_col_mth1))),
action_information3 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'02',p_col_mth2,
decode(nvl(p_col_mth2,'X'),
'X',action_information3,
decode(nvl(action_information3,'X'),
nvl(p_col_mth2,'X'),action_information3,
p_col_mth2))),
action_information4 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'03',p_col_mth3,
decode(nvl(p_col_mth3,'X'),
'X',action_information4,
decode(nvl(action_information4,'X'),
nvl(p_col_mth3,'X'),action_information4,
p_col_mth3))),
action_information5 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'04',p_col_mth4,
decode(nvl(p_col_mth4,'X'),
'X',action_information5,
decode(nvl(action_information5,'X'),
nvl(p_col_mth4,'X'),action_information5,
p_col_mth4))),
action_information6 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'05',p_col_mth5,
decode(nvl(p_col_mth5,'X'),
'X',action_information6,
decode(nvl(action_information6,'X'),
nvl(p_col_mth5,'X'),action_information6,
p_col_mth5))),
action_information7 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'06',p_col_mth6,
decode(nvl(p_col_mth6,'X'),
'X',action_information7,
decode(nvl(action_information7,'X'),
nvl(p_col_mth6,'X'),action_information7,
p_col_mth6))),
action_information8 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'07',p_col_mth7,
decode(nvl(p_col_mth7,'X'),
'X',action_information8,
decode(nvl(action_information8,'X'),
nvl(p_col_mth7,'X'),action_information8,
p_col_mth7))),
action_information9 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'08',p_col_mth8,
decode(nvl(p_col_mth8,'X'),
'X',action_information9,
decode(nvl(action_information9,'X'),
nvl(p_col_mth8,'X'),action_information9,
p_col_mth8))),
action_information10 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'09',p_col_mth9,
decode(nvl(p_col_mth9,'X'),
'X',action_information10,
decode(nvl(action_information10,'X'),
nvl(p_col_mth9,'X'),action_information10,
p_col_mth9))),
action_information11 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'10',p_col_mth10,
decode(nvl(p_col_mth10,'X'),
'X',action_information11,
decode(nvl(action_information11,'X'),
nvl(p_col_mth10,'X'),action_information11,
p_col_mth10))),
action_information12 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'11',p_col_mth11,
decode(nvl(p_col_mth11,'X'),
'X',action_information12,
decode(nvl(action_information12,'X'),
nvl(p_col_mth11,'X'),action_information12,
p_col_mth11))),
action_information13 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'12',p_col_mth12,
decode(nvl(p_col_mth12,'X'),
'X',action_information13,
decode(nvl(action_information13,'X'),
nvl(p_col_mth12,'X'),action_information13,
p_col_mth12)))
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_SAL_ACT'
and action_information1 = p_category
and (((p_col_mth1 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '01')
and (nvl(action_information2,'X') <> nvl(p_col_mth1,'X')))
or ((p_col_mth2 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '02')
and (nvl(action_information3,'X') <> nvl(p_col_mth2,'X')))
or ((p_col_mth3 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '03')
and (nvl(action_information4,'X') <> nvl(p_col_mth3,'X')))
or ((p_col_mth4 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '04')
and (nvl(action_information5,'X') <> nvl(p_col_mth4,'X')))
or ((p_col_mth5 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '05')
and (nvl(action_information6,'X') <> nvl(p_col_mth5,'X')))
or ((p_col_mth6 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '06')
and (nvl(action_information7,'X') <> nvl(p_col_mth6,'X')))
or ((p_col_mth7 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '07')
and (nvl(action_information8,'X') <> nvl(p_col_mth7,'X')))
or ((p_col_mth8 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '08')
and (nvl(action_information9,'X') <> nvl(p_col_mth8,'X')))
or ((p_col_mth9 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '09')
and (nvl(action_information10,'X') <> nvl(p_col_mth9,'X')))
or ((p_col_mth10 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '10')
and (nvl(action_information11,'X') <> nvl(p_col_mth10,'X')))
or ((p_col_mth11 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '11')
and (nvl(action_information12,'X') <> nvl(p_col_mth11,'X')))
or ((p_col_mth12 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '12')
and (nvl(action_information13,'X') <> nvl(p_col_mth12,'X'))));
end update_sal_act;
update pay_action_information
set object_version_number = p_object_version_number,
action_information2 = decode(nvl(p_col_mth1,'X'),
'X',action_information2,
p_col_mth1),
action_information3 = decode(nvl(p_col_mth2,'X'),
'X',action_information3,
p_col_mth2),
action_information4 = decode(nvl(p_col_mth3,'X'),
'X',action_information4,
p_col_mth3),
action_information5 = decode(nvl(p_col_mth4,'X'),
'X',action_information5,
p_col_mth4),
action_information6 = decode(nvl(p_col_mth5,'X'),
'X',action_information6,
p_col_mth5),
action_information7 = decode(nvl(p_col_mth6,'X'),
'X',action_information7,
p_col_mth6),
action_information8 = decode(nvl(p_col_mth7,'X'),
'X',action_information8,
p_col_mth7),
action_information9 = decode(nvl(p_col_mth8,'X'),
'X',action_information9,
p_col_mth8),
action_information10 = decode(nvl(p_col_mth9,'X'),
'X',action_information10,
p_col_mth9),
action_information11 = decode(nvl(p_col_mth10,'X'),
'X',action_information11,
p_col_mth10),
action_information12 = decode(nvl(p_col_mth11,'X'),
'X',action_information12,
p_col_mth11),
action_information13 = decode(nvl(p_col_mth12,'X'),
'X',action_information13,
p_col_mth12)
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_SAL_ACT'
and action_information1 = p_category;
insert into pay_action_information(
action_information_id,
object_version_number,
action_context_id,
action_context_type,
effective_date,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10,
action_information11,
action_information12,
action_information13,
action_information14)
values(
p_action_information_id,
p_object_version_number,
p_assignment_action_id,
p_action_context_type,
p_effective_date,
p_assignment_id,
p_action_information_category,
p_category,
fnd_number.number_to_canonical(p_col_mth1),
fnd_number.number_to_canonical(p_col_mth2),
fnd_number.number_to_canonical(p_col_mth3),
fnd_number.number_to_canonical(p_col_mth4),
fnd_number.number_to_canonical(p_col_mth5),
fnd_number.number_to_canonical(p_col_mth6),
fnd_number.number_to_canonical(p_col_mth7),
fnd_number.number_to_canonical(p_col_mth8),
fnd_number.number_to_canonical(p_col_mth9),
fnd_number.number_to_canonical(p_col_mth10),
fnd_number.number_to_canonical(p_col_mth11),
fnd_number.number_to_canonical(p_col_mth12),
fnd_number.number_to_canonical(p_col_tot));
procedure update_sal_pay(
p_assignment_action_id in number,
p_effective_date in date,
p_arch_assignment_action_id_o in number,
p_category in varchar2,
p_col_mth1 in number,
p_col_mth2 in number,
p_col_mth3 in number,
p_col_mth4 in number,
p_col_mth5 in number,
p_col_mth6 in number,
p_col_mth7 in number,
p_col_mth8 in number,
p_col_mth9 in number,
p_col_mth10 in number,
p_col_mth11 in number,
p_col_mth12 in number,
p_col_tot in number,
p_action_information_id out nocopy number,
p_object_version_number out nocopy number)
is
--
l_proc varchar2(80) := c_package||'update_sal_pay';
update pay_action_information
set object_version_number = p_object_version_number,
action_information2 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'01',fnd_number.number_to_canonical(p_col_mth1),
decode(nvl(fnd_number.number_to_canonical(p_col_mth1),'X'),
'X',action_information2,
decode(nvl(action_information2,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth1),'X'),action_information2,
fnd_number.number_to_canonical(p_col_mth1)))),
action_information3 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'02',fnd_number.number_to_canonical(p_col_mth2),
decode(nvl(fnd_number.number_to_canonical(p_col_mth2),'X'),
'X',action_information3,
decode(nvl(action_information3,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth2),'X'),action_information3,
fnd_number.number_to_canonical(p_col_mth2)))),
action_information4 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'03',fnd_number.number_to_canonical(p_col_mth3),
decode(nvl(fnd_number.number_to_canonical(p_col_mth3),'X'),
'X',action_information4,
decode(nvl(action_information4,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth3),'X'),action_information4,
fnd_number.number_to_canonical(p_col_mth3)))),
action_information5 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'04',fnd_number.number_to_canonical(p_col_mth4),
decode(nvl(fnd_number.number_to_canonical(p_col_mth4),'X'),
'X',action_information5,
decode(nvl(action_information5,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth4),'X'),action_information5,
fnd_number.number_to_canonical(p_col_mth4)))),
action_information6 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'05',fnd_number.number_to_canonical(p_col_mth5),
decode(nvl(fnd_number.number_to_canonical(p_col_mth5),'X'),
'X',action_information6,
decode(nvl(action_information6,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth5),'X'),action_information6,
fnd_number.number_to_canonical(p_col_mth5)))),
action_information7 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'06',fnd_number.number_to_canonical(p_col_mth6),
decode(nvl(fnd_number.number_to_canonical(p_col_mth6),'X'),
'X',action_information7,
decode(nvl(action_information7,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth6),'X'),action_information7,
fnd_number.number_to_canonical(p_col_mth6)))),
action_information8 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'07',fnd_number.number_to_canonical(p_col_mth7),
decode(nvl(fnd_number.number_to_canonical(p_col_mth7),'X'),
'X',action_information8,
decode(nvl(action_information8,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth7),'X'),action_information8,
fnd_number.number_to_canonical(p_col_mth7)))),
action_information9 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'08',fnd_number.number_to_canonical(p_col_mth8),
decode(nvl(fnd_number.number_to_canonical(p_col_mth8),'X'),
'X',action_information9,
decode(nvl(action_information9,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth8),'X'),action_information9,
fnd_number.number_to_canonical(p_col_mth8)))),
action_information10 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'09',fnd_number.number_to_canonical(p_col_mth9),
decode(nvl(fnd_number.number_to_canonical(p_col_mth9),'X'),
'X',action_information10,
decode(nvl(action_information10,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth9),'X'),action_information10,
fnd_number.number_to_canonical(p_col_mth9)))),
action_information11 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'10',fnd_number.number_to_canonical(p_col_mth10),
decode(nvl(fnd_number.number_to_canonical(p_col_mth10),'X'),
'X',action_information11,
decode(nvl(action_information11,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth10),'X'),action_information11,
fnd_number.number_to_canonical(p_col_mth10)))),
action_information12 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'11',fnd_number.number_to_canonical(p_col_mth11),
decode(nvl(fnd_number.number_to_canonical(p_col_mth11),'X'),
'X',action_information12,
decode(nvl(action_information12,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth11),'X'),action_information12,
fnd_number.number_to_canonical(p_col_mth11)))),
action_information13 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'12',fnd_number.number_to_canonical(p_col_mth12),
decode(nvl(fnd_number.number_to_canonical(p_col_mth12),'X'),
'X',action_information13,
decode(nvl(action_information13,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth12),'X'),action_information13,
fnd_number.number_to_canonical(p_col_mth12)))),
action_information14 = decode(nvl(fnd_number.number_to_canonical(p_col_tot),'X'),
'X',action_information14,
decode(nvl(action_information14,'X'),
nvl(fnd_number.number_to_canonical(p_col_tot),'X'),action_information14,
fnd_number.number_to_canonical(p_col_tot)))
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_SAL_PAY'
and action_information1 = p_category
and (((p_col_mth1 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '01')
and (nvl(action_information2,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth1),'X')))
or ((p_col_mth2 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '02')
and (nvl(action_information3,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth2),'X')))
or ((p_col_mth3 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '03')
and (nvl(action_information4,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth3),'X')))
or ((p_col_mth4 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '04')
and (nvl(action_information5,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth4),'X')))
or ((p_col_mth5 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '05')
and (nvl(action_information6,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth5),'X')))
or ((p_col_mth6 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '06')
and (nvl(action_information7,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth6),'X')))
or ((p_col_mth7 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '07')
and (nvl(action_information8,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth7),'X')))
or ((p_col_mth8 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '08')
and (nvl(action_information9,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth8),'X')))
or ((p_col_mth9 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '09')
and (nvl(action_information10,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth9),'X')))
or ((p_col_mth10 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '10')
and (nvl(action_information11,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth10),'X')))
or ((p_col_mth11 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '11')
and (nvl(action_information12,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth11),'X')))
or ((p_col_mth12 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '12')
and (nvl(action_information13,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth12),'X')))
or (p_col_tot is not null
and (nvl(action_information14,'X') <> nvl(fnd_number.number_to_canonical(p_col_tot),'X'))));
end update_sal_pay;
update pay_action_information
set object_version_number = p_object_version_number,
action_information2 = decode(nvl(fnd_number.number_to_canonical(p_col_mth1),'X'),
'X',action_information2,
fnd_number.number_to_canonical(p_col_mth1)),
action_information3 = decode(nvl(fnd_number.number_to_canonical(p_col_mth2),'X'),
'X',action_information3,
fnd_number.number_to_canonical(p_col_mth2)),
action_information4 = decode(nvl(fnd_number.number_to_canonical(p_col_mth3),'X'),
'X',action_information4,
fnd_number.number_to_canonical(p_col_mth3)),
action_information5 = decode(nvl(fnd_number.number_to_canonical(p_col_mth4),'X'),
'X',action_information5,
fnd_number.number_to_canonical(p_col_mth4)),
action_information6 = decode(nvl(fnd_number.number_to_canonical(p_col_mth5),'X'),
'X',action_information6,
fnd_number.number_to_canonical(p_col_mth5)),
action_information7 = decode(nvl(fnd_number.number_to_canonical(p_col_mth6),'X'),
'X',action_information7,
fnd_number.number_to_canonical(p_col_mth6)),
action_information8 = decode(nvl(fnd_number.number_to_canonical(p_col_mth7),'X'),
'X',action_information8,
fnd_number.number_to_canonical(p_col_mth7)),
action_information9 = decode(nvl(fnd_number.number_to_canonical(p_col_mth8),'X'),
'X',action_information9,
fnd_number.number_to_canonical(p_col_mth8)),
action_information10 = decode(nvl(fnd_number.number_to_canonical(p_col_mth9),'X'),
'X',action_information10,
fnd_number.number_to_canonical(p_col_mth9)),
action_information11 = decode(nvl(fnd_number.number_to_canonical(p_col_mth10),'X'),
'X',action_information11,
fnd_number.number_to_canonical(p_col_mth10)),
action_information12 = decode(nvl(fnd_number.number_to_canonical(p_col_mth11),'X'),
'X',action_information12,
fnd_number.number_to_canonical(p_col_mth11)),
action_information13 = decode(nvl(fnd_number.number_to_canonical(p_col_mth12),'X'),
'X',action_information13,
fnd_number.number_to_canonical(p_col_mth12)),
action_information14 = decode(nvl(fnd_number.number_to_canonical(p_col_tot),'X'),
'X',action_information14,
fnd_number.number_to_canonical(p_col_tot))
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_SAL_PAY'
and action_information1 = p_category;
insert into pay_action_information(
action_information_id,
object_version_number,
action_context_id,
action_context_type,
effective_date,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10,
action_information11,
action_information12,
action_information13,
action_information14,
action_information15,
action_information16)
values(
p_action_information_id,
p_object_version_number,
p_assignment_action_id,
p_action_context_type,
p_effective_date,
p_assignment_id,
p_action_information_category,
p_category,
p_col_cnt1,
p_col_cnt2,
p_col_cnt3,
p_col_cnt4,
p_col_cnt5,
p_col_cnt6,
p_col_cnt7,
p_col_cnt8,
p_col_cnt9,
p_col_cnt10,
p_col_cnt11,
p_col_cnt12,
p_col_cnt13,
p_col_cnt14,
p_col_cnt15);
procedure update_bon_act(
p_assignment_action_id in number,
p_effective_date in date,
p_arch_assignment_action_id_o in number,
p_category in varchar2,
p_col_cnt1 in varchar2,
p_col_cnt2 in varchar2,
p_col_cnt3 in varchar2,
p_col_cnt4 in varchar2,
p_col_cnt5 in varchar2,
p_col_cnt6 in varchar2,
p_col_cnt7 in varchar2,
p_col_cnt8 in varchar2,
p_col_cnt9 in varchar2,
p_col_cnt10 in varchar2,
p_col_cnt11 in varchar2,
p_col_cnt12 in varchar2,
p_col_cnt13 in varchar2,
p_col_cnt14 in varchar2,
p_col_cnt15 in varchar2,
p_action_information_id out nocopy number,
p_object_version_number out nocopy number)
is
--
l_proc varchar2(80) := c_package||'update_bon_act';
select decode(nvl(to_char(col_cnt1,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt1,
decode(nvl(to_char(col_cnt2,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt2,
decode(nvl(to_char(col_cnt3,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt3,
decode(nvl(to_char(col_cnt4,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt4,
decode(nvl(to_char(col_cnt5,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt5,
decode(nvl(to_char(col_cnt6,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt6,
decode(nvl(to_char(col_cnt7,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt7,
decode(nvl(to_char(col_cnt8,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt8,
decode(nvl(to_char(col_cnt9,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt9,
decode(nvl(to_char(col_cnt10,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt10,
decode(nvl(to_char(col_cnt11,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt11,
decode(nvl(to_char(col_cnt12,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt12,
decode(nvl(to_char(col_cnt13,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt13,
decode(nvl(to_char(col_cnt14,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt14,
decode(nvl(to_char(col_cnt15,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt15
from pay_jp_itwb_bon_act_efd_v
where assignment_action_id = p_arch_assignment_action_id_o;
update pay_action_information
set object_version_number = p_object_version_number,
action_information2 = decode(nvl(l_csr_act_efd.col_cnt1,-1),
0,p_col_cnt1,
decode(nvl(p_col_cnt1,'X'),
'X',action_information2,
decode(nvl(action_information2,'X'),
nvl(p_col_cnt1,'X'),action_information2,
p_col_cnt1))),
action_information3 = decode(nvl(l_csr_act_efd.col_cnt2,-1),
0,p_col_cnt2,
decode(nvl(p_col_cnt2,'X'),
'X',action_information3,
decode(nvl(action_information3,'X'),
nvl(p_col_cnt2,'X'),action_information3,
p_col_cnt2))),
action_information4 = decode(nvl(l_csr_act_efd.col_cnt3,-1),
0,p_col_cnt3,
decode(nvl(p_col_cnt3,'X'),
'X',action_information4,
decode(nvl(action_information4,'X'),
nvl(p_col_cnt3,'X'),action_information4,
p_col_cnt3))),
action_information5 = decode(nvl(l_csr_act_efd.col_cnt4,-1),
0,p_col_cnt4,
decode(nvl(p_col_cnt4,'X'),
'X',action_information5,
decode(nvl(action_information5,'X'),
nvl(p_col_cnt4,'X'),action_information5,
p_col_cnt4))),
action_information6 = decode(nvl(l_csr_act_efd.col_cnt5,-1),
0,p_col_cnt5,
decode(nvl(p_col_cnt5,'X'),
'X',action_information6,
decode(nvl(action_information6,'X'),
nvl(p_col_cnt5,'X'),action_information6,
p_col_cnt5))),
action_information7 = decode(nvl(l_csr_act_efd.col_cnt6,-1),
0,p_col_cnt6,
decode(nvl(p_col_cnt6,'X'),
'X',action_information7,
decode(nvl(action_information7,'X'),
nvl(p_col_cnt6,'X'),action_information7,
p_col_cnt6))),
action_information8 = decode(nvl(l_csr_act_efd.col_cnt7,-1),
0,p_col_cnt7,
decode(nvl(p_col_cnt7,'X'),
'X',action_information8,
decode(nvl(action_information8,'X'),
nvl(p_col_cnt7,'X'),action_information8,
p_col_cnt7))),
action_information9 = decode(nvl(l_csr_act_efd.col_cnt8,-1),
0,p_col_cnt8,
decode(nvl(p_col_cnt8,'X'),
'X',action_information9,
decode(nvl(action_information9,'X'),
nvl(p_col_cnt8,'X'),action_information9,
p_col_cnt8))),
action_information10 = decode(nvl(l_csr_act_efd.col_cnt9,-1),
0,p_col_cnt9,
decode(nvl(p_col_cnt9,'X'),
'X',action_information10,
decode(nvl(action_information10,'X'),
nvl(p_col_cnt9,'X'),action_information10,
p_col_cnt9))),
action_information11 = decode(nvl(l_csr_act_efd.col_cnt10,-1),
0,p_col_cnt10,
decode(nvl(p_col_cnt10,'X'),
'X',action_information11,
decode(nvl(action_information11,'X'),
nvl(p_col_cnt10,'X'),action_information11,
p_col_cnt10))),
action_information12 = decode(nvl(l_csr_act_efd.col_cnt11,-1),
0,p_col_cnt11,
decode(nvl(p_col_cnt11,'X'),
'X',action_information12,
decode(nvl(action_information12,'X'),
nvl(p_col_cnt11,'X'),action_information12,
p_col_cnt11))),
action_information13 = decode(nvl(l_csr_act_efd.col_cnt12,-1),
0,p_col_cnt12,
decode(nvl(p_col_cnt12,'X'),
'X',action_information13,
decode(nvl(action_information13,'X'),
nvl(p_col_cnt12,'X'),action_information13,
p_col_cnt12))),
action_information14 = decode(nvl(l_csr_act_efd.col_cnt13,-1),
0,p_col_cnt13,
decode(nvl(p_col_cnt13,'X'),
'X',action_information14,
decode(nvl(action_information14,'X'),
nvl(p_col_cnt13,'X'),action_information14,
p_col_cnt13))),
action_information15 = decode(nvl(l_csr_act_efd.col_cnt14,-1),
0,p_col_cnt14,
decode(nvl(p_col_cnt14,'X'),
'X',action_information15,
decode(nvl(action_information15,'X'),
nvl(p_col_cnt14,'X'),action_information15,
p_col_cnt14))),
action_information16 = decode(nvl(l_csr_act_efd.col_cnt15,-1),
0,p_col_cnt15,
decode(nvl(p_col_cnt15,'X'),
'X',action_information16,
decode(nvl(action_information16,'X'),
nvl(p_col_cnt15,'X'),action_information16,
p_col_cnt15)))
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_BON_ACT'
and action_information1 = p_category
and (((p_col_cnt1 is not null
or nvl(l_csr_act_efd.col_cnt1,-1) = 0)
and (nvl(action_information2,'X') <> nvl(p_col_cnt1,'X')))
or ((p_col_cnt2 is not null
or nvl(l_csr_act_efd.col_cnt2,-1) = 0)
and (nvl(action_information3,'X') <> nvl(p_col_cnt2,'X')))
or ((p_col_cnt3 is not null
or nvl(l_csr_act_efd.col_cnt3,-1) = 0)
and (nvl(action_information4,'X') <> nvl(p_col_cnt3,'X')))
or ((p_col_cnt4 is not null
or nvl(l_csr_act_efd.col_cnt4,-1) = 0)
and (nvl(action_information5,'X') <> nvl(p_col_cnt4,'X')))
or ((p_col_cnt5 is not null
or nvl(l_csr_act_efd.col_cnt5,-1) = 0)
and (nvl(action_information6,'X') <> nvl(p_col_cnt5,'X')))
or ((p_col_cnt6 is not null
or nvl(l_csr_act_efd.col_cnt6,-1) = 0)
and (nvl(action_information7,'X') <> nvl(p_col_cnt6,'X')))
or ((p_col_cnt7 is not null
or nvl(l_csr_act_efd.col_cnt7,-1) = 0)
and (nvl(action_information8,'X') <> nvl(p_col_cnt7,'X')))
or ((p_col_cnt8 is not null
or nvl(l_csr_act_efd.col_cnt8,-1) = 0)
and (nvl(action_information9,'X') <> nvl(p_col_cnt8,'X')))
or ((p_col_cnt9 is not null
or nvl(l_csr_act_efd.col_cnt9,-1) = 0)
and (nvl(action_information10,'X') <> nvl(p_col_cnt9,'X')))
or ((p_col_cnt10 is not null
or nvl(l_csr_act_efd.col_cnt10,-1) = 0)
and (nvl(action_information11,'X') <> nvl(p_col_cnt10,'X')))
or ((p_col_cnt11 is not null
or nvl(l_csr_act_efd.col_cnt11,-1) = 0)
and (nvl(action_information12,'X') <> nvl(p_col_cnt11,'X')))
or ((p_col_cnt12 is not null
or nvl(l_csr_act_efd.col_cnt12,-1) = 0)
and (nvl(action_information13,'X') <> nvl(p_col_cnt12,'X')))
or ((p_col_cnt13 is not null
or nvl(l_csr_act_efd.col_cnt13,-1) = 0)
and (nvl(action_information14,'X') <> nvl(p_col_cnt13,'X')))
or ((p_col_cnt14 is not null
or nvl(l_csr_act_efd.col_cnt14,-1) = 0)
and (nvl(action_information15,'X') <> nvl(p_col_cnt14,'X')))
or ((p_col_cnt15 is not null
or nvl(l_csr_act_efd.col_cnt15,-1) = 0)
and (nvl(action_information16,'X') <> nvl(p_col_cnt15,'X'))));
end update_bon_act;
update pay_action_information
set object_version_number = p_object_version_number,
action_information2 = decode(nvl(p_col_cnt1,'X'),
'X',action_information2,
p_col_cnt1),
action_information3 = decode(nvl(p_col_cnt2,'X'),
'X',action_information3,
p_col_cnt2),
action_information4 = decode(nvl(p_col_cnt3,'X'),
'X',action_information4,
p_col_cnt3),
action_information5 = decode(nvl(p_col_cnt4,'X'),
'X',action_information5,
p_col_cnt4),
action_information6 = decode(nvl(p_col_cnt5,'X'),
'X',action_information6,
p_col_cnt5),
action_information7 = decode(nvl(p_col_cnt6,'X'),
'X',action_information7,
p_col_cnt6),
action_information8 = decode(nvl(p_col_cnt7,'X'),
'X',action_information8,
p_col_cnt7),
action_information9 = decode(nvl(p_col_cnt8,'X'),
'X',action_information9,
p_col_cnt8),
action_information10 = decode(nvl(p_col_cnt9,'X'),
'X',action_information10,
p_col_cnt9),
action_information11 = decode(nvl(p_col_cnt10,'X'),
'X',action_information11,
p_col_cnt10),
action_information12 = decode(nvl(p_col_cnt11,'X'),
'X',action_information12,
p_col_cnt11),
action_information13 = decode(nvl(p_col_cnt12,'X'),
'X',action_information13,
p_col_cnt12),
action_information14 = decode(nvl(p_col_cnt13,'X'),
'X',action_information14,
p_col_cnt13),
action_information15 = decode(nvl(p_col_cnt14,'X'),
'X',action_information15,
p_col_cnt14),
action_information16 = decode(nvl(p_col_cnt15,'X'),
'X',action_information16,
p_col_cnt15)
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_BON_ACT'
and action_information1 = p_category;
insert into pay_action_information(
action_information_id,
object_version_number,
action_context_id,
action_context_type,
effective_date,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10,
action_information11,
action_information12,
action_information13,
action_information14,
action_information15,
action_information16,
action_information17)
values(
p_action_information_id,
p_object_version_number,
p_assignment_action_id,
p_action_context_type,
p_effective_date,
p_assignment_id,
p_action_information_category,
p_category,
p_col_cnt1,
p_col_cnt2,
p_col_cnt3,
p_col_cnt4,
p_col_cnt5,
p_col_cnt6,
p_col_cnt7,
p_col_cnt8,
p_col_cnt9,
p_col_cnt10,
p_col_cnt11,
p_col_cnt12,
p_col_cnt13,
p_col_cnt14,
p_col_cnt15,
p_col_tot);
procedure update_bon_pay(
p_assignment_action_id in number,
p_effective_date in date,
p_arch_assignment_action_id_o in number,
p_category in varchar2,
p_col_cnt1 in varchar2,
p_col_cnt2 in varchar2,
p_col_cnt3 in varchar2,
p_col_cnt4 in varchar2,
p_col_cnt5 in varchar2,
p_col_cnt6 in varchar2,
p_col_cnt7 in varchar2,
p_col_cnt8 in varchar2,
p_col_cnt9 in varchar2,
p_col_cnt10 in varchar2,
p_col_cnt11 in varchar2,
p_col_cnt12 in varchar2,
p_col_cnt13 in varchar2,
p_col_cnt14 in varchar2,
p_col_cnt15 in varchar2,
p_col_tot in varchar2,
p_action_information_id out nocopy number,
p_object_version_number out nocopy number)
is
--
l_proc varchar2(80) := c_package||'update_bon_pay';
select decode(nvl(to_char(col_cnt1,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt1,
decode(nvl(to_char(col_cnt2,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt2,
decode(nvl(to_char(col_cnt3,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt3,
decode(nvl(to_char(col_cnt4,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt4,
decode(nvl(to_char(col_cnt5,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt5,
decode(nvl(to_char(col_cnt6,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt6,
decode(nvl(to_char(col_cnt7,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt7,
decode(nvl(to_char(col_cnt8,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt8,
decode(nvl(to_char(col_cnt9,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt9,
decode(nvl(to_char(col_cnt10,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt10,
decode(nvl(to_char(col_cnt11,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt11,
decode(nvl(to_char(col_cnt12,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt12,
decode(nvl(to_char(col_cnt13,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt13,
decode(nvl(to_char(col_cnt14,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt14,
decode(nvl(to_char(col_cnt15,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt15
from pay_jp_itwb_bon_act_efd_v
where assignment_action_id = p_arch_assignment_action_id_o;
update pay_action_information
set object_version_number = p_object_version_number,
action_information2 = decode(nvl(l_csr_act_efd.col_cnt1,-1),
0,p_col_cnt1,
decode(nvl(p_col_cnt1,'X'),
'X',action_information2,
decode(nvl(action_information2,'X'),
nvl(p_col_cnt1,'X'),action_information2,
p_col_cnt1))),
action_information3 = decode(nvl(l_csr_act_efd.col_cnt2,-1),
0,p_col_cnt2,
decode(nvl(p_col_cnt2,'X'),
'X',action_information3,
decode(nvl(action_information3,'X'),
nvl(p_col_cnt2,'X'),action_information3,
p_col_cnt2))),
action_information4 = decode(nvl(l_csr_act_efd.col_cnt3,-1),
0,p_col_cnt3,
decode(nvl(p_col_cnt3,'X'),
'X',action_information4,
decode(nvl(action_information4,'X'),
nvl(p_col_cnt3,'X'),action_information4,
p_col_cnt3))),
action_information5 = decode(nvl(l_csr_act_efd.col_cnt4,-1),
0,p_col_cnt4,
decode(nvl(p_col_cnt4,'X'),
'X',action_information5,
decode(nvl(action_information5,'X'),
nvl(p_col_cnt4,'X'),action_information5,
p_col_cnt4))),
action_information6 = decode(nvl(l_csr_act_efd.col_cnt5,-1),
0,p_col_cnt5,
decode(nvl(p_col_cnt5,'X'),
'X',action_information6,
decode(nvl(action_information6,'X'),
nvl(p_col_cnt5,'X'),action_information6,
p_col_cnt5))),
action_information7 = decode(nvl(l_csr_act_efd.col_cnt6,-1),
0,p_col_cnt6,
decode(nvl(p_col_cnt6,'X'),
'X',action_information7,
decode(nvl(action_information7,'X'),
nvl(p_col_cnt6,'X'),action_information7,
p_col_cnt6))),
action_information8 = decode(nvl(l_csr_act_efd.col_cnt7,-1),
0,p_col_cnt7,
decode(nvl(p_col_cnt7,'X'),
'X',action_information8,
decode(nvl(action_information8,'X'),
nvl(p_col_cnt7,'X'),action_information8,
p_col_cnt7))),
action_information9 = decode(nvl(l_csr_act_efd.col_cnt8,-1),
0,p_col_cnt8,
decode(nvl(p_col_cnt8,'X'),
'X',action_information9,
decode(nvl(action_information9,'X'),
nvl(p_col_cnt8,'X'),action_information9,
p_col_cnt8))),
action_information10 = decode(nvl(l_csr_act_efd.col_cnt9,-1),
0,p_col_cnt9,
decode(nvl(p_col_cnt9,'X'),
'X',action_information10,
decode(nvl(action_information10,'X'),
nvl(p_col_cnt9,'X'),action_information10,
p_col_cnt9))),
action_information11 = decode(nvl(l_csr_act_efd.col_cnt10,-1),
0,p_col_cnt10,
decode(nvl(p_col_cnt10,'X'),
'X',action_information11,
decode(nvl(action_information11,'X'),
nvl(p_col_cnt10,'X'),action_information11,
p_col_cnt10))),
action_information12 = decode(nvl(l_csr_act_efd.col_cnt11,-1),
0,p_col_cnt11,
decode(nvl(p_col_cnt11,'X'),
'X',action_information12,
decode(nvl(action_information12,'X'),
nvl(p_col_cnt11,'X'),action_information12,
p_col_cnt11))),
action_information13 = decode(nvl(l_csr_act_efd.col_cnt12,-1),
0,p_col_cnt12,
decode(nvl(p_col_cnt12,'X'),
'X',action_information13,
decode(nvl(action_information13,'X'),
nvl(p_col_cnt12,'X'),action_information13,
p_col_cnt12))),
action_information14 = decode(nvl(l_csr_act_efd.col_cnt13,-1),
0,p_col_cnt13,
decode(nvl(p_col_cnt13,'X'),
'X',action_information14,
decode(nvl(action_information14,'X'),
nvl(p_col_cnt13,'X'),action_information14,
p_col_cnt13))),
action_information15 = decode(nvl(l_csr_act_efd.col_cnt14,-1),
0,p_col_cnt14,
decode(nvl(p_col_cnt14,'X'),
'X',action_information15,
decode(nvl(action_information15,'X'),
nvl(p_col_cnt14,'X'),action_information15,
p_col_cnt14))),
action_information16 = decode(nvl(l_csr_act_efd.col_cnt15,-1),
0,p_col_cnt15,
decode(nvl(p_col_cnt15,'X'),
'X',action_information16,
decode(nvl(action_information16,'X'),
nvl(p_col_cnt15,'X'),action_information16,
p_col_cnt15))),
action_information17 = decode(nvl(p_col_tot,'X'),
'X',action_information17,
decode(nvl(action_information17,'X'),
nvl(p_col_tot,'X'),action_information17,
p_col_tot))
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_BON_PAY'
and action_information1 = p_category
and (((p_col_cnt1 is not null
or nvl(l_csr_act_efd.col_cnt1,-1) = 0)
and (nvl(action_information2,'X') <> nvl(p_col_cnt1,'X')))
or ((p_col_cnt2 is not null
or nvl(l_csr_act_efd.col_cnt2,-1) = 0)
and (nvl(action_information3,'X') <> nvl(p_col_cnt2,'X')))
or ((p_col_cnt3 is not null
or nvl(l_csr_act_efd.col_cnt3,-1) = 0)
and (nvl(action_information4,'X') <> nvl(p_col_cnt3,'X')))
or ((p_col_cnt4 is not null
or nvl(l_csr_act_efd.col_cnt4,-1) = 0)
and (nvl(action_information5,'X') <> nvl(p_col_cnt4,'X')))
or ((p_col_cnt5 is not null
or nvl(l_csr_act_efd.col_cnt5,-1) = 0)
and (nvl(action_information6,'X') <> nvl(p_col_cnt5,'X')))
or ((p_col_cnt6 is not null
or nvl(l_csr_act_efd.col_cnt6,-1) = 0)
and (nvl(action_information7,'X') <> nvl(p_col_cnt6,'X')))
or ((p_col_cnt7 is not null
or nvl(l_csr_act_efd.col_cnt7,-1) = 0)
and (nvl(action_information8,'X') <> nvl(p_col_cnt7,'X')))
or ((p_col_cnt8 is not null
or nvl(l_csr_act_efd.col_cnt8,-1) = 0)
and (nvl(action_information9,'X') <> nvl(p_col_cnt8,'X')))
or ((p_col_cnt9 is not null
or nvl(l_csr_act_efd.col_cnt9,-1) = 0)
and (nvl(action_information10,'X') <> nvl(p_col_cnt9,'X')))
or ((p_col_cnt10 is not null
or nvl(l_csr_act_efd.col_cnt10,-1) = 0)
and (nvl(action_information11,'X') <> nvl(p_col_cnt10,'X')))
or ((p_col_cnt11 is not null
or nvl(l_csr_act_efd.col_cnt11,-1) = 0)
and (nvl(action_information12,'X') <> nvl(p_col_cnt11,'X')))
or ((p_col_cnt12 is not null
or nvl(l_csr_act_efd.col_cnt12,-1) = 0)
and (nvl(action_information13,'X') <> nvl(p_col_cnt12,'X')))
or ((p_col_cnt13 is not null
or nvl(l_csr_act_efd.col_cnt13,-1) = 0)
and (nvl(action_information14,'X') <> nvl(p_col_cnt13,'X')))
or ((p_col_cnt14 is not null
or nvl(l_csr_act_efd.col_cnt14,-1) = 0)
and (nvl(action_information15,'X') <> nvl(p_col_cnt14,'X')))
or ((p_col_cnt15 is not null
or nvl(l_csr_act_efd.col_cnt15,-1) = 0)
and (nvl(action_information16,'X') <> nvl(p_col_cnt15,'X')))
or (p_col_tot is not null
and (nvl(action_information17,'X') <> nvl(p_col_tot,'X'))));
end update_bon_pay;
procedure update_bon_pay(
p_assignment_action_id in number,
p_effective_date in date,
p_arch_assignment_action_id_o in number,
p_category in varchar2,
p_col_cnt1 in number,
p_col_cnt2 in number,
p_col_cnt3 in number,
p_col_cnt4 in number,
p_col_cnt5 in number,
p_col_cnt6 in number,
p_col_cnt7 in number,
p_col_cnt8 in number,
p_col_cnt9 in number,
p_col_cnt10 in number,
p_col_cnt11 in number,
p_col_cnt12 in number,
p_col_cnt13 in number,
p_col_cnt14 in number,
p_col_cnt15 in number,
p_col_tot in number,
p_action_information_id out nocopy number,
p_object_version_number out nocopy number)
is
--
l_proc varchar2(80) := c_package||'update_bon_pay';
update_bon_pay(
p_assignment_action_id => p_assignment_action_id,
p_effective_date => p_effective_date,
p_arch_assignment_action_id_o => p_arch_assignment_action_id_o,
p_category => p_category,
p_col_cnt1 => fnd_number.number_to_canonical(p_col_cnt1),
p_col_cnt2 => fnd_number.number_to_canonical(p_col_cnt2),
p_col_cnt3 => fnd_number.number_to_canonical(p_col_cnt3),
p_col_cnt4 => fnd_number.number_to_canonical(p_col_cnt4),
p_col_cnt5 => fnd_number.number_to_canonical(p_col_cnt5),
p_col_cnt6 => fnd_number.number_to_canonical(p_col_cnt6),
p_col_cnt7 => fnd_number.number_to_canonical(p_col_cnt7),
p_col_cnt8 => fnd_number.number_to_canonical(p_col_cnt8),
p_col_cnt9 => fnd_number.number_to_canonical(p_col_cnt9),
p_col_cnt10 => fnd_number.number_to_canonical(p_col_cnt10),
p_col_cnt11 => fnd_number.number_to_canonical(p_col_cnt11),
p_col_cnt12 => fnd_number.number_to_canonical(p_col_cnt12),
p_col_cnt13 => fnd_number.number_to_canonical(p_col_cnt13),
p_col_cnt14 => fnd_number.number_to_canonical(p_col_cnt14),
p_col_cnt15 => fnd_number.number_to_canonical(p_col_cnt15),
p_col_tot => fnd_number.number_to_canonical(p_col_tot),
p_action_information_id => p_action_information_id,
p_object_version_number => p_object_version_number);
end update_bon_pay;
update pay_action_information
set object_version_number = p_object_version_number,
action_information2 = decode(nvl(p_col_cnt1,'X'),
'X',action_information2,
p_col_cnt1),
action_information3 = decode(nvl(p_col_cnt2,'X'),
'X',action_information3,
p_col_cnt2),
action_information4 = decode(nvl(p_col_cnt3,'X'),
'X',action_information4,
p_col_cnt3),
action_information5 = decode(nvl(p_col_cnt4,'X'),
'X',action_information5,
p_col_cnt4),
action_information6 = decode(nvl(p_col_cnt5,'X'),
'X',action_information6,
p_col_cnt5),
action_information7 = decode(nvl(p_col_cnt6,'X'),
'X',action_information7,
p_col_cnt6),
action_information8 = decode(nvl(p_col_cnt7,'X'),
'X',action_information8,
p_col_cnt7),
action_information9 = decode(nvl(p_col_cnt8,'X'),
'X',action_information9,
p_col_cnt8),
action_information10 = decode(nvl(p_col_cnt9,'X'),
'X',action_information10,
p_col_cnt9),
action_information11 = decode(nvl(p_col_cnt10,'X'),
'X',action_information11,
p_col_cnt10),
action_information12 = decode(nvl(p_col_cnt11,'X'),
'X',action_information12,
p_col_cnt11),
action_information13 = decode(nvl(p_col_cnt12,'X'),
'X',action_information13,
p_col_cnt12),
action_information14 = decode(nvl(p_col_cnt13,'X'),
'X',action_information14,
p_col_cnt13),
action_information15 = decode(nvl(p_col_cnt14,'X'),
'X',action_information15,
p_col_cnt14),
action_information16 = decode(nvl(p_col_cnt15,'X'),
'X',action_information16,
p_col_cnt15),
action_information17 = decode(nvl(p_col_tot,'X'),
'X',action_information17,
p_col_tot)
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_BON_PAY'
and action_information1 = p_category;
insert into pay_action_information(
action_information_id,
object_version_number,
action_context_id,
action_context_type,
effective_date,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10,
action_information11,
action_information12,
action_information13,
action_information14,
action_information15,
action_information16,
action_information17,
action_information18,
action_information19)
values(
p_action_information_id,
p_object_version_number,
p_assignment_action_id,
p_action_context_type,
p_effective_date,
p_assignment_id,
p_action_information_category,
fnd_number.number_to_canonical(p_item_pay_id),
fnd_number.number_to_canonical(p_item_pay_ord),
p_item_type,
fnd_number.number_to_canonical(p_item_id),
fnd_number.number_to_canonical(p_item_rep_seq),
p_item_rep_name,
fnd_number.number_to_canonical(p_col_mth1),
fnd_number.number_to_canonical(p_col_mth2),
fnd_number.number_to_canonical(p_col_mth3),
fnd_number.number_to_canonical(p_col_mth4),
fnd_number.number_to_canonical(p_col_mth5),
fnd_number.number_to_canonical(p_col_mth6),
fnd_number.number_to_canonical(p_col_mth7),
fnd_number.number_to_canonical(p_col_mth8),
fnd_number.number_to_canonical(p_col_mth9),
fnd_number.number_to_canonical(p_col_mth10),
fnd_number.number_to_canonical(p_col_mth11),
fnd_number.number_to_canonical(p_col_mth12),
fnd_number.number_to_canonical(p_col_tot));
procedure update_sal_earn(
p_assignment_action_id in number,
p_assignment_id in number,
p_effective_date in date,
p_arch_assignment_action_id_o in number,
p_item_pay_id in number,
p_item_pay_ord in number,
p_item_type in varchar2,
p_item_id in number,
p_item_rep_seq in number,
p_item_rep_name in varchar2,
p_col_mth1 in number,
p_col_mth2 in number,
p_col_mth3 in number,
p_col_mth4 in number,
p_col_mth5 in number,
p_col_mth6 in number,
p_col_mth7 in number,
p_col_mth8 in number,
p_col_mth9 in number,
p_col_mth10 in number,
p_col_mth11 in number,
p_col_mth12 in number,
p_col_tot in number,
p_action_information_id out nocopy number,
p_object_version_number out nocopy number)
is
--
l_proc varchar2(80) := c_package||'update_sal_earn';
select action_information_id,
object_version_number
from pay_jp_itwb_sal_earn_v
where assignment_action_id = p_assignment_action_id
and item_pay_id = p_item_pay_id
and item_type = p_item_type
and item_id = p_item_id;
update pay_action_information
set object_version_number = p_object_version_number,
action_information2 = decode(nvl(action_information2,'X'),
nvl(fnd_number.number_to_canonical(p_item_pay_ord),'X'),action_information2,
fnd_number.number_to_canonical(p_item_pay_ord)),
action_information5 = decode(nvl(action_information5,'X'),
nvl(fnd_number.number_to_canonical(p_item_rep_seq),'X'),action_information5,
fnd_number.number_to_canonical(p_item_rep_seq)),
action_information6 = decode(nvl(action_information6,'X'),
nvl(p_item_rep_name,'X'),action_information6,
p_item_rep_name),
action_information7 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'01',fnd_number.number_to_canonical(p_col_mth1),
decode(nvl(fnd_number.number_to_canonical(p_col_mth1),'X'),
'X',action_information7,
decode(nvl(action_information7,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth1),'X'),action_information7,
fnd_number.number_to_canonical(p_col_mth1)))),
action_information8 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'02',fnd_number.number_to_canonical(p_col_mth2),
decode(nvl(fnd_number.number_to_canonical(p_col_mth2),'X'),
'X',action_information8,
decode(nvl(action_information8,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth2),'X'),action_information8,
fnd_number.number_to_canonical(p_col_mth2)))),
action_information9 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'03',fnd_number.number_to_canonical(p_col_mth3),
decode(nvl(fnd_number.number_to_canonical(p_col_mth3),'X'),
'X',action_information9,
decode(nvl(action_information9,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth3),'X'),action_information9,
fnd_number.number_to_canonical(p_col_mth3)))),
action_information10 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'04',fnd_number.number_to_canonical(p_col_mth4),
decode(nvl(fnd_number.number_to_canonical(p_col_mth4),'X'),
'X',action_information10,
decode(nvl(action_information10,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth4),'X'),action_information10,
fnd_number.number_to_canonical(p_col_mth4)))),
action_information11 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'05',fnd_number.number_to_canonical(p_col_mth5),
decode(nvl(fnd_number.number_to_canonical(p_col_mth5),'X'),
'X',action_information11,
decode(nvl(action_information11,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth5),'X'),action_information11,
fnd_number.number_to_canonical(p_col_mth5)))),
action_information12 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'06',fnd_number.number_to_canonical(p_col_mth6),
decode(nvl(fnd_number.number_to_canonical(p_col_mth6),'X'),
'X',action_information12,
decode(nvl(action_information12,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth6),'X'),action_information12,
fnd_number.number_to_canonical(p_col_mth6)))),
action_information13 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'07',fnd_number.number_to_canonical(p_col_mth7),
decode(nvl(fnd_number.number_to_canonical(p_col_mth7),'X'),
'X',action_information13,
decode(nvl(action_information13,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth7),'X'),action_information13,
fnd_number.number_to_canonical(p_col_mth7)))),
action_information14 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'08',fnd_number.number_to_canonical(p_col_mth8),
decode(nvl(fnd_number.number_to_canonical(p_col_mth8),'X'),
'X',action_information14,
decode(nvl(action_information14,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth8),'X'),action_information14,
fnd_number.number_to_canonical(p_col_mth8)))),
action_information15 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'09',fnd_number.number_to_canonical(p_col_mth9),
decode(nvl(fnd_number.number_to_canonical(p_col_mth9),'X'),
'X',action_information15,
decode(nvl(action_information15,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth9),'X'),action_information15,
fnd_number.number_to_canonical(p_col_mth9)))),
action_information16 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'10',fnd_number.number_to_canonical(p_col_mth10),
decode(nvl(fnd_number.number_to_canonical(p_col_mth10),'X'),
'X',action_information16,
decode(nvl(action_information16,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth10),'X'),action_information16,
fnd_number.number_to_canonical(p_col_mth10)))),
action_information17 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'11',fnd_number.number_to_canonical(p_col_mth11),
decode(nvl(fnd_number.number_to_canonical(p_col_mth11),'X'),
'X',action_information17,
decode(nvl(action_information17,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth11),'X'),action_information17,
fnd_number.number_to_canonical(p_col_mth11)))),
action_information18 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'12',fnd_number.number_to_canonical(p_col_mth12),
decode(nvl(fnd_number.number_to_canonical(p_col_mth12),'X'),
'X',action_information18,
decode(nvl(action_information18,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth12),'X'),action_information18,
fnd_number.number_to_canonical(p_col_mth12)))),
action_information19 = decode(nvl(fnd_number.number_to_canonical(p_col_tot),'X'),
'X',action_information19,
decode(nvl(action_information19,'X'),
nvl(fnd_number.number_to_canonical(p_col_tot),'X'),action_information19,
fnd_number.number_to_canonical(p_col_tot)))
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_SAL_EARN'
and action_information1 = fnd_number.number_to_canonical(p_item_pay_id)
and action_information3 = p_item_type
and action_information4 = fnd_number.number_to_canonical(p_item_id)
and (nvl(action_information2,'X') <> nvl(fnd_number.number_to_canonical(p_item_pay_ord),'X')
or nvl(action_information5,'X') <> nvl(fnd_number.number_to_canonical(p_item_rep_seq),'X')
or nvl(action_information6,'X') <> nvl(p_item_rep_name,'X')
or ((p_col_mth1 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '01')
and (nvl(action_information7,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth1),'X')))
or ((p_col_mth2 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '02')
and (nvl(action_information8,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth2),'X')))
or ((p_col_mth3 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '03')
and (nvl(action_information9,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth3),'X')))
or ((p_col_mth4 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '04')
and (nvl(action_information10,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth4),'X')))
or ((p_col_mth5 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '05')
and (nvl(action_information11,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth5),'X')))
or ((p_col_mth6 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '06')
and (nvl(action_information12,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth6),'X')))
or ((p_col_mth7 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '07')
and (nvl(action_information13,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth7),'X')))
or ((p_col_mth8 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '08')
and (nvl(action_information14,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth8),'X')))
or ((p_col_mth9 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '09')
and (nvl(action_information15,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth9),'X')))
or ((p_col_mth10 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '10')
and (nvl(action_information16,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth10),'X')))
or ((p_col_mth11 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '11')
and (nvl(action_information17,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth11),'X')))
or ((p_col_mth12 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '12')
and (nvl(action_information18,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth12),'X')))
or (p_col_tot is not null
and (nvl(action_information19,'X') <> nvl(fnd_number.number_to_canonical(p_col_tot),'X'))));
end update_sal_earn;
select action_information_id,
object_version_number
from pay_jp_itwb_sal_earn_v
where assignment_action_id = p_assignment_action_id
and item_pay_id = p_item_pay_id
and item_type = p_item_type
and item_id = p_item_id;
update pay_action_information
set object_version_number = p_object_version_number,
action_information7 = decode(nvl(fnd_number.number_to_canonical(p_col_mth1),'X'),
'X',action_information7,
fnd_number.number_to_canonical(p_col_mth1)),
action_information8 = decode(nvl(fnd_number.number_to_canonical(p_col_mth2),'X'),
'X',action_information8,
fnd_number.number_to_canonical(p_col_mth2)),
action_information9 = decode(nvl(fnd_number.number_to_canonical(p_col_mth3),'X'),
'X',action_information9,
fnd_number.number_to_canonical(p_col_mth3)),
action_information10 = decode(nvl(fnd_number.number_to_canonical(p_col_mth4),'X'),
'X',action_information10,
fnd_number.number_to_canonical(p_col_mth4)),
action_information11 = decode(nvl(fnd_number.number_to_canonical(p_col_mth5),'X'),
'X',action_information11,
fnd_number.number_to_canonical(p_col_mth5)),
action_information12 = decode(nvl(fnd_number.number_to_canonical(p_col_mth6),'X'),
'X',action_information12,
fnd_number.number_to_canonical(p_col_mth6)),
action_information13 = decode(nvl(fnd_number.number_to_canonical(p_col_mth7),'X'),
'X',action_information13,
fnd_number.number_to_canonical(p_col_mth7)),
action_information14 = decode(nvl(fnd_number.number_to_canonical(p_col_mth8),'X'),
'X',action_information14,
fnd_number.number_to_canonical(p_col_mth8)),
action_information15 = decode(nvl(fnd_number.number_to_canonical(p_col_mth9),'X'),
'X',action_information15,
fnd_number.number_to_canonical(p_col_mth9)),
action_information16 = decode(nvl(fnd_number.number_to_canonical(p_col_mth10),'X'),
'X',action_information16,
fnd_number.number_to_canonical(p_col_mth10)),
action_information17 = decode(nvl(fnd_number.number_to_canonical(p_col_mth11),'X'),
'X',action_information17,
fnd_number.number_to_canonical(p_col_mth11)),
action_information18 = decode(nvl(fnd_number.number_to_canonical(p_col_mth12),'X'),
'X',action_information18,
fnd_number.number_to_canonical(p_col_mth12)),
action_information19 = decode(nvl(fnd_number.number_to_canonical(p_col_tot),'X'),
'X',action_information19,
fnd_number.number_to_canonical(p_col_tot))
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_SAL_EARN'
and action_information1 = fnd_number.number_to_canonical(p_item_pay_id)
and action_information3 = p_item_type
and action_information4 = fnd_number.number_to_canonical(p_item_id);
insert into pay_action_information(
action_information_id,
object_version_number,
action_context_id,
action_context_type,
effective_date,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10,
action_information11,
action_information12,
action_information13,
action_information14,
action_information15,
action_information16,
action_information17,
action_information18,
action_information19)
values(
p_action_information_id,
p_object_version_number,
p_assignment_action_id,
p_action_context_type,
p_effective_date,
p_assignment_id,
p_action_information_category,
fnd_number.number_to_canonical(p_item_pay_id),
fnd_number.number_to_canonical(p_item_pay_ord),
p_item_type,
fnd_number.number_to_canonical(p_item_id),
fnd_number.number_to_canonical(p_item_rep_seq),
p_item_rep_name,
fnd_number.number_to_canonical(p_col_mth1),
fnd_number.number_to_canonical(p_col_mth2),
fnd_number.number_to_canonical(p_col_mth3),
fnd_number.number_to_canonical(p_col_mth4),
fnd_number.number_to_canonical(p_col_mth5),
fnd_number.number_to_canonical(p_col_mth6),
fnd_number.number_to_canonical(p_col_mth7),
fnd_number.number_to_canonical(p_col_mth8),
fnd_number.number_to_canonical(p_col_mth9),
fnd_number.number_to_canonical(p_col_mth10),
fnd_number.number_to_canonical(p_col_mth11),
fnd_number.number_to_canonical(p_col_mth12),
fnd_number.number_to_canonical(p_col_tot));
procedure update_sal_dct(
p_assignment_action_id in number,
p_assignment_id in number,
p_effective_date in date,
p_arch_assignment_action_id_o in number,
p_item_pay_id in number,
p_item_pay_ord in number,
p_item_type in varchar2,
p_item_id in number,
p_item_rep_seq in number,
p_item_rep_name in varchar2,
p_col_mth1 in number,
p_col_mth2 in number,
p_col_mth3 in number,
p_col_mth4 in number,
p_col_mth5 in number,
p_col_mth6 in number,
p_col_mth7 in number,
p_col_mth8 in number,
p_col_mth9 in number,
p_col_mth10 in number,
p_col_mth11 in number,
p_col_mth12 in number,
p_col_tot in number,
p_action_information_id out nocopy number,
p_object_version_number out nocopy number)
is
--
l_proc varchar2(80) := c_package||'update_sal_dct';
select action_information_id,
object_version_number
from pay_jp_itwb_sal_dct_v
where assignment_action_id = p_assignment_action_id
and item_pay_id = p_item_pay_id
and item_type = p_item_type
and item_id = p_item_id;
update pay_action_information
set object_version_number = p_object_version_number,
action_information2 = decode(nvl(action_information2,'X'),
nvl(fnd_number.number_to_canonical(p_item_pay_ord),'X'),action_information2,
fnd_number.number_to_canonical(p_item_pay_ord)),
action_information5 = decode(nvl(action_information5,'X'),
nvl(fnd_number.number_to_canonical(p_item_rep_seq),'X'),action_information5,
fnd_number.number_to_canonical(p_item_rep_seq)),
action_information6 = decode(nvl(action_information6,'X'),
nvl(p_item_rep_name,'X'),action_information6,
p_item_rep_name),
action_information7 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'01',fnd_number.number_to_canonical(p_col_mth1),
decode(nvl(fnd_number.number_to_canonical(p_col_mth1),'X'),
'X',action_information7,
decode(nvl(action_information7,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth1),'X'),action_information7,
fnd_number.number_to_canonical(p_col_mth1)))),
action_information8 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'02',fnd_number.number_to_canonical(p_col_mth2),
decode(nvl(fnd_number.number_to_canonical(p_col_mth2),'X'),
'X',action_information8,
decode(nvl(action_information8,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth2),'X'),action_information8,
fnd_number.number_to_canonical(p_col_mth2)))),
action_information9 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'03',fnd_number.number_to_canonical(p_col_mth3),
decode(nvl(fnd_number.number_to_canonical(p_col_mth3),'X'),
'X',action_information9,
decode(nvl(action_information9,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth3),'X'),action_information9,
fnd_number.number_to_canonical(p_col_mth3)))),
action_information10 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'04',fnd_number.number_to_canonical(p_col_mth4),
decode(nvl(fnd_number.number_to_canonical(p_col_mth4),'X'),
'X',action_information10,
decode(nvl(action_information10,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth4),'X'),action_information10,
fnd_number.number_to_canonical(p_col_mth4)))),
action_information11 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'05',fnd_number.number_to_canonical(p_col_mth5),
decode(nvl(fnd_number.number_to_canonical(p_col_mth5),'X'),
'X',action_information11,
decode(nvl(action_information11,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth5),'X'),action_information11,
fnd_number.number_to_canonical(p_col_mth5)))),
action_information12 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'06',fnd_number.number_to_canonical(p_col_mth6),
decode(nvl(fnd_number.number_to_canonical(p_col_mth6),'X'),
'X',action_information12,
decode(nvl(action_information12,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth6),'X'),action_information12,
fnd_number.number_to_canonical(p_col_mth6)))),
action_information13 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'07',fnd_number.number_to_canonical(p_col_mth7),
decode(nvl(fnd_number.number_to_canonical(p_col_mth7),'X'),
'X',action_information13,
decode(nvl(action_information13,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth7),'X'),action_information13,
fnd_number.number_to_canonical(p_col_mth7)))),
action_information14 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'08',fnd_number.number_to_canonical(p_col_mth8),
decode(nvl(fnd_number.number_to_canonical(p_col_mth8),'X'),
'X',action_information14,
decode(nvl(action_information14,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth8),'X'),action_information14,
fnd_number.number_to_canonical(p_col_mth8)))),
action_information15 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'09',fnd_number.number_to_canonical(p_col_mth9),
decode(nvl(fnd_number.number_to_canonical(p_col_mth9),'X'),
'X',action_information15,
decode(nvl(action_information15,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth9),'X'),action_information15,
fnd_number.number_to_canonical(p_col_mth9)))),
action_information16 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'10',fnd_number.number_to_canonical(p_col_mth10),
decode(nvl(fnd_number.number_to_canonical(p_col_mth10),'X'),
'X',action_information16,
decode(nvl(action_information16,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth10),'X'),action_information16,
fnd_number.number_to_canonical(p_col_mth10)))),
action_information17 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'11',fnd_number.number_to_canonical(p_col_mth11),
decode(nvl(fnd_number.number_to_canonical(p_col_mth11),'X'),
'X',action_information17,
decode(nvl(action_information17,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth11),'X'),action_information17,
fnd_number.number_to_canonical(p_col_mth11)))),
action_information18 = decode(nvl(to_char(p_effective_date,'MM'),'X'),
'12',fnd_number.number_to_canonical(p_col_mth12),
decode(nvl(fnd_number.number_to_canonical(p_col_mth12),'X'),
'X',action_information18,
decode(nvl(action_information18,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth12),'X'),action_information18,
fnd_number.number_to_canonical(p_col_mth12)))),
action_information19 = decode(nvl(fnd_number.number_to_canonical(p_col_tot),'X'),
'X',action_information19,
decode(nvl(action_information19,'X'),
nvl(fnd_number.number_to_canonical(p_col_tot),'X'),action_information19,
fnd_number.number_to_canonical(p_col_tot)))
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_SAL_DCT'
and action_information1 = fnd_number.number_to_canonical(p_item_pay_id)
and action_information3 = p_item_type
and action_information4 = fnd_number.number_to_canonical(p_item_id)
and (nvl(action_information2,'X') <> nvl(fnd_number.number_to_canonical(p_item_pay_ord),'X')
or nvl(action_information5,'X') <> nvl(fnd_number.number_to_canonical(p_item_rep_seq),'X')
or nvl(action_information6,'X') <> nvl(p_item_rep_name,'X')
or ((p_col_mth1 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '01')
and (nvl(action_information7,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth1),'X')))
or ((p_col_mth2 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '02')
and (nvl(action_information8,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth2),'X')))
or ((p_col_mth3 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '03')
and (nvl(action_information9,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth3),'X')))
or ((p_col_mth4 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '04')
and (nvl(action_information10,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth4),'X')))
or ((p_col_mth5 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '05')
and (nvl(action_information11,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth5),'X')))
or ((p_col_mth6 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '06')
and (nvl(action_information12,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth6),'X')))
or ((p_col_mth7 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '07')
and (nvl(action_information13,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth7),'X')))
or ((p_col_mth8 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '08')
and (nvl(action_information14,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth8),'X')))
or ((p_col_mth9 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '09')
and (nvl(action_information15,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth9),'X')))
or ((p_col_mth10 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '10')
and (nvl(action_information16,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth10),'X')))
or ((p_col_mth11 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '11')
and (nvl(action_information17,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth11),'X')))
or ((p_col_mth12 is not null
or nvl(to_char(p_effective_date,'MM'),'X') = '12')
and (nvl(action_information18,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth12),'X')))
or (p_col_tot is not null
and (nvl(action_information19,'X') <> nvl(fnd_number.number_to_canonical(p_col_tot),'X'))));
end update_sal_dct;
select action_information_id,
object_version_number
from pay_jp_itwb_sal_dct_v
where assignment_action_id = p_assignment_action_id
and item_pay_id = p_item_pay_id
and item_type = p_item_type
and item_id = p_item_id;
update pay_action_information
set object_version_number = p_object_version_number,
action_information7 = decode(nvl(fnd_number.number_to_canonical(p_col_mth1),'X'),
'X',action_information7,
fnd_number.number_to_canonical(p_col_mth1)),
action_information8 = decode(nvl(fnd_number.number_to_canonical(p_col_mth2),'X'),
'X',action_information8,
fnd_number.number_to_canonical(p_col_mth2)),
action_information9 = decode(nvl(fnd_number.number_to_canonical(p_col_mth3),'X'),
'X',action_information9,
fnd_number.number_to_canonical(p_col_mth3)),
action_information10 = decode(nvl(fnd_number.number_to_canonical(p_col_mth4),'X'),
'X',action_information10,
fnd_number.number_to_canonical(p_col_mth4)),
action_information11 = decode(nvl(fnd_number.number_to_canonical(p_col_mth5),'X'),
'X',action_information11,
fnd_number.number_to_canonical(p_col_mth5)),
action_information12 = decode(nvl(fnd_number.number_to_canonical(p_col_mth6),'X'),
'X',action_information12,
fnd_number.number_to_canonical(p_col_mth6)),
action_information13 = decode(nvl(fnd_number.number_to_canonical(p_col_mth7),'X'),
'X',action_information13,
fnd_number.number_to_canonical(p_col_mth7)),
action_information14 = decode(nvl(fnd_number.number_to_canonical(p_col_mth8),'X'),
'X',action_information14,
fnd_number.number_to_canonical(p_col_mth8)),
action_information15 = decode(nvl(fnd_number.number_to_canonical(p_col_mth9),'X'),
'X',action_information15,
fnd_number.number_to_canonical(p_col_mth9)),
action_information16 = decode(nvl(fnd_number.number_to_canonical(p_col_mth10),'X'),
'X',action_information16,
fnd_number.number_to_canonical(p_col_mth10)),
action_information17 = decode(nvl(fnd_number.number_to_canonical(p_col_mth11),'X'),
'X',action_information17,
fnd_number.number_to_canonical(p_col_mth11)),
action_information18 = decode(nvl(fnd_number.number_to_canonical(p_col_mth12),'X'),
'X',action_information18,
fnd_number.number_to_canonical(p_col_mth12)),
action_information19 = decode(nvl(fnd_number.number_to_canonical(p_col_tot),'X'),
'X',action_information19,
fnd_number.number_to_canonical(p_col_tot))
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_SAL_DCT'
and action_information1 = fnd_number.number_to_canonical(p_item_pay_id)
and action_information3 = p_item_type
and action_information4 = fnd_number.number_to_canonical(p_item_id);
insert into pay_action_information(
action_information_id,
object_version_number,
action_context_id,
action_context_type,
effective_date,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10,
action_information11,
action_information12,
action_information13,
action_information14,
action_information15,
action_information16,
action_information17,
action_information18,
action_information19,
action_information20,
action_information21,
action_information22)
values(
p_action_information_id,
p_object_version_number,
p_assignment_action_id,
p_action_context_type,
p_effective_date,
p_assignment_id,
p_action_information_category,
fnd_number.number_to_canonical(p_item_pay_id),
fnd_number.number_to_canonical(p_item_pay_ord),
p_item_type,
fnd_number.number_to_canonical(p_item_id),
fnd_number.number_to_canonical(p_item_rep_seq),
p_item_rep_name,
fnd_number.number_to_canonical(p_col_cnt1),
fnd_number.number_to_canonical(p_col_cnt2),
fnd_number.number_to_canonical(p_col_cnt3),
fnd_number.number_to_canonical(p_col_cnt4),
fnd_number.number_to_canonical(p_col_cnt5),
fnd_number.number_to_canonical(p_col_cnt6),
fnd_number.number_to_canonical(p_col_cnt7),
fnd_number.number_to_canonical(p_col_cnt8),
fnd_number.number_to_canonical(p_col_cnt9),
fnd_number.number_to_canonical(p_col_cnt10),
fnd_number.number_to_canonical(p_col_cnt11),
fnd_number.number_to_canonical(p_col_cnt12),
fnd_number.number_to_canonical(p_col_cnt13),
fnd_number.number_to_canonical(p_col_cnt14),
fnd_number.number_to_canonical(p_col_cnt15),
fnd_number.number_to_canonical(p_col_tot));
procedure update_bon_earn(
p_assignment_action_id in number,
p_assignment_id in number,
p_effective_date in date,
p_arch_assignment_action_id_o in number,
p_item_pay_id in number,
p_item_pay_ord in number,
p_item_type in varchar2,
p_item_id in number,
p_item_rep_seq in number,
p_item_rep_name in varchar2,
p_col_cnt1 in number,
p_col_cnt2 in number,
p_col_cnt3 in number,
p_col_cnt4 in number,
p_col_cnt5 in number,
p_col_cnt6 in number,
p_col_cnt7 in number,
p_col_cnt8 in number,
p_col_cnt9 in number,
p_col_cnt10 in number,
p_col_cnt11 in number,
p_col_cnt12 in number,
p_col_cnt13 in number,
p_col_cnt14 in number,
p_col_cnt15 in number,
p_col_tot in number,
p_action_information_id out nocopy number,
p_object_version_number out nocopy number)
is
--
l_proc varchar2(80) := c_package||'update_bon_earn';
select action_information_id,
object_version_number
from pay_jp_itwb_bon_earn_v
where assignment_action_id = p_assignment_action_id
and item_pay_id = p_item_pay_id
and item_type = p_item_type
and item_id = p_item_id;
select decode(nvl(to_char(col_cnt1,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt1,
decode(nvl(to_char(col_cnt2,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt2,
decode(nvl(to_char(col_cnt3,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt3,
decode(nvl(to_char(col_cnt4,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt4,
decode(nvl(to_char(col_cnt5,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt5,
decode(nvl(to_char(col_cnt6,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt6,
decode(nvl(to_char(col_cnt7,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt7,
decode(nvl(to_char(col_cnt8,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt8,
decode(nvl(to_char(col_cnt9,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt9,
decode(nvl(to_char(col_cnt10,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt10,
decode(nvl(to_char(col_cnt11,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt11,
decode(nvl(to_char(col_cnt12,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt12,
decode(nvl(to_char(col_cnt13,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt13,
decode(nvl(to_char(col_cnt14,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt14,
decode(nvl(to_char(col_cnt15,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt15
from pay_jp_itwb_bon_act_efd_v
where assignment_action_id = p_arch_assignment_action_id_o;
update pay_action_information
set object_version_number = p_object_version_number,
action_information2 = decode(nvl(action_information2,'X'),
nvl(fnd_number.number_to_canonical(p_item_pay_ord),'X'),action_information2,
fnd_number.number_to_canonical(p_item_pay_ord)),
action_information5 = decode(nvl(action_information5,'X'),
nvl(fnd_number.number_to_canonical(p_item_rep_seq),'X'),action_information5,
fnd_number.number_to_canonical(p_item_rep_seq)),
action_information6 = decode(nvl(action_information6,'X'),
nvl(p_item_rep_name,'X'),action_information6,
p_item_rep_name),
action_information7 = decode(nvl(l_csr_act_efd.col_cnt1,-1),
0,fnd_number.number_to_canonical(p_col_cnt1),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt1),'X'),
'X',action_information7,
decode(nvl(action_information7,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt1),'X'),action_information7,
fnd_number.number_to_canonical(p_col_cnt1)))),
action_information8 = decode(nvl(l_csr_act_efd.col_cnt2,-1),
0,fnd_number.number_to_canonical(p_col_cnt2),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt2),'X'),
'X',action_information8,
decode(nvl(action_information8,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt2),'X'),action_information8,
fnd_number.number_to_canonical(p_col_cnt2)))),
action_information9 = decode(nvl(l_csr_act_efd.col_cnt3,-1),
0,fnd_number.number_to_canonical(p_col_cnt3),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt3),'X'),
'X',action_information9,
decode(nvl(action_information9,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt3),'X'),action_information9,
fnd_number.number_to_canonical(p_col_cnt3)))),
action_information10 = decode(nvl(l_csr_act_efd.col_cnt4,-1),
0,fnd_number.number_to_canonical(p_col_cnt4),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt4),'X'),
'X',action_information10,
decode(nvl(action_information10,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt4),'X'),action_information10,
fnd_number.number_to_canonical(p_col_cnt4)))),
action_information11 = decode(nvl(l_csr_act_efd.col_cnt5,-1),
0,fnd_number.number_to_canonical(p_col_cnt5),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt5),'X'),
'X',action_information11,
decode(nvl(action_information11,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt5),'X'),action_information11,
fnd_number.number_to_canonical(p_col_cnt5)))),
action_information12 = decode(nvl(l_csr_act_efd.col_cnt6,-1),
0,fnd_number.number_to_canonical(p_col_cnt6),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt6),'X'),
'X',action_information12,
decode(nvl(action_information12,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt6),'X'),action_information12,
fnd_number.number_to_canonical(p_col_cnt6)))),
action_information13 = decode(nvl(l_csr_act_efd.col_cnt7,-1),
0,fnd_number.number_to_canonical(p_col_cnt7),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt7),'X'),
'X',action_information13,
decode(nvl(action_information13,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt7),'X'),action_information13,
fnd_number.number_to_canonical(p_col_cnt7)))),
action_information14 = decode(nvl(l_csr_act_efd.col_cnt8,-1),
0,fnd_number.number_to_canonical(p_col_cnt8),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt8),'X'),
'X',action_information14,
decode(nvl(action_information14,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt8),'X'),action_information14,
fnd_number.number_to_canonical(p_col_cnt8)))),
action_information15 = decode(nvl(l_csr_act_efd.col_cnt9,-1),
0,fnd_number.number_to_canonical(p_col_cnt9),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt9),'X'),
'X',action_information15,
decode(nvl(action_information15,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt9),'X'),action_information15,
fnd_number.number_to_canonical(p_col_cnt9)))),
action_information16 = decode(nvl(l_csr_act_efd.col_cnt10,-1),
0,fnd_number.number_to_canonical(p_col_cnt10),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt10),'X'),
'X',action_information16,
decode(nvl(action_information16,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt10),'X'),action_information16,
fnd_number.number_to_canonical(p_col_cnt10)))),
action_information17 = decode(nvl(l_csr_act_efd.col_cnt11,-1),
0,fnd_number.number_to_canonical(p_col_cnt11),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt11),'X'),
'X',action_information17,
decode(nvl(action_information17,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt11),'X'),action_information17,
fnd_number.number_to_canonical(p_col_cnt11)))),
action_information18 = decode(nvl(l_csr_act_efd.col_cnt12,-1),
0,fnd_number.number_to_canonical(p_col_cnt12),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt12),'X'),
'X',action_information18,
decode(nvl(action_information18,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt12),'X'),action_information18,
fnd_number.number_to_canonical(p_col_cnt12)))),
action_information19 = decode(nvl(l_csr_act_efd.col_cnt13,-1),
0,fnd_number.number_to_canonical(p_col_cnt13),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt13),'X'),
'X',action_information19,
decode(nvl(action_information19,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt13),'X'),action_information19,
fnd_number.number_to_canonical(p_col_cnt13)))),
action_information20 = decode(nvl(l_csr_act_efd.col_cnt14,-1),
0,fnd_number.number_to_canonical(p_col_cnt14),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt14),'X'),
'X',action_information20,
decode(nvl(action_information20,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt14),'X'),action_information20,
fnd_number.number_to_canonical(p_col_cnt14)))),
action_information21 = decode(nvl(l_csr_act_efd.col_cnt15,-1),
0,fnd_number.number_to_canonical(p_col_cnt15),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt15),'X'),
'X',action_information21,
decode(nvl(action_information21,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt15),'X'),action_information21,
fnd_number.number_to_canonical(p_col_cnt15)))),
action_information22 = decode(nvl(fnd_number.number_to_canonical(p_col_tot),'X'),
'X',action_information22,
decode(nvl(action_information22,'X'),
nvl(fnd_number.number_to_canonical(p_col_tot),'X'),action_information22,
fnd_number.number_to_canonical(p_col_tot)))
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_BON_EARN'
and action_information1 = fnd_number.number_to_canonical(p_item_pay_id)
and action_information3 = p_item_type
and action_information4 = fnd_number.number_to_canonical(p_item_id)
and (nvl(action_information2,'X') <> nvl(fnd_number.number_to_canonical(p_item_pay_ord),'X')
or nvl(action_information5,'X') <> nvl(fnd_number.number_to_canonical(p_item_rep_seq),'X')
or nvl(action_information6,'X') <> nvl(p_item_rep_name,'X')
or ((p_col_cnt1 is not null
or nvl(l_csr_act_efd.col_cnt1,-1) = 0)
and (nvl(action_information7,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt1),'X')))
or ((p_col_cnt2 is not null
or nvl(l_csr_act_efd.col_cnt2,-1) = 0)
and (nvl(action_information8,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt2),'X')))
or ((p_col_cnt3 is not null
or nvl(l_csr_act_efd.col_cnt3,-1) = 0)
and (nvl(action_information9,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt3),'X')))
or ((p_col_cnt4 is not null
or nvl(l_csr_act_efd.col_cnt4,-1) = 0)
and (nvl(action_information10,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt4),'X')))
or ((p_col_cnt5 is not null
or nvl(l_csr_act_efd.col_cnt5,-1) = 0)
and (nvl(action_information11,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt5),'X')))
or ((p_col_cnt6 is not null
or nvl(l_csr_act_efd.col_cnt6,-1) = 0)
and (nvl(action_information12,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt6),'X')))
or ((p_col_cnt7 is not null
or nvl(l_csr_act_efd.col_cnt7,-1) = 0)
and (nvl(action_information13,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt7),'X')))
or ((p_col_cnt8 is not null
or nvl(l_csr_act_efd.col_cnt8,-1) = 0)
and (nvl(action_information14,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt8),'X')))
or ((p_col_cnt9 is not null
or nvl(l_csr_act_efd.col_cnt9,-1) = 0)
and (nvl(action_information15,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt9),'X')))
or ((p_col_cnt10 is not null
or nvl(l_csr_act_efd.col_cnt10,-1) = 0)
and (nvl(action_information16,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt10),'X')))
or ((p_col_cnt11 is not null
or nvl(l_csr_act_efd.col_cnt11,-1) = 0)
and (nvl(action_information17,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt11),'X')))
or ((p_col_cnt12 is not null
or nvl(l_csr_act_efd.col_cnt12,-1) = 0)
and (nvl(action_information18,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt12),'X')))
or ((p_col_cnt13 is not null
or nvl(l_csr_act_efd.col_cnt13,-1) = 0)
and (nvl(action_information19,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt13),'X')))
or ((p_col_cnt14 is not null
or nvl(l_csr_act_efd.col_cnt14,-1) = 0)
and (nvl(action_information20,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt14),'X')))
or ((p_col_cnt15 is not null
or nvl(l_csr_act_efd.col_cnt15,-1) = 0)
and (nvl(action_information21,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt15),'X')))
or (p_col_tot is not null
and (nvl(action_information22,'X') <> nvl(fnd_number.number_to_canonical(p_col_tot),'X'))));
end update_bon_earn;
select action_information_id,
object_version_number
from pay_jp_itwb_bon_earn_v
where assignment_action_id = p_assignment_action_id
and item_pay_id = p_item_pay_id
and item_type = p_item_type
and item_id = p_item_id;
update pay_action_information
set object_version_number = p_object_version_number,
action_information7 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt1),'X'),
'X',action_information7,
fnd_number.number_to_canonical(p_col_cnt1)),
action_information8 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt2),'X'),
'X',action_information8,
fnd_number.number_to_canonical(p_col_cnt2)),
action_information9 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt3),'X'),
'X',action_information9,
fnd_number.number_to_canonical(p_col_cnt3)),
action_information10 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt4),'X'),
'X',action_information10,
fnd_number.number_to_canonical(p_col_cnt4)),
action_information11 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt5),'X'),
'X',action_information11,
fnd_number.number_to_canonical(p_col_cnt5)),
action_information12 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt6),'X'),
'X',action_information12,
fnd_number.number_to_canonical(p_col_cnt6)),
action_information13 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt7),'X'),
'X',action_information13,
fnd_number.number_to_canonical(p_col_cnt7)),
action_information14 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt8),'X'),
'X',action_information14,
fnd_number.number_to_canonical(p_col_cnt8)),
action_information15 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt9),'X'),
'X',action_information15,
fnd_number.number_to_canonical(p_col_cnt9)),
action_information16 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt10),'X'),
'X',action_information16,
fnd_number.number_to_canonical(p_col_cnt10)),
action_information17 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt11),'X'),
'X',action_information17,
fnd_number.number_to_canonical(p_col_cnt11)),
action_information18 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt12),'X'),
'X',action_information18,
fnd_number.number_to_canonical(p_col_cnt12)),
action_information19 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt13),'X'),
'X',action_information19,
fnd_number.number_to_canonical(p_col_cnt13)),
action_information20 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt14),'X'),
'X',action_information20,
fnd_number.number_to_canonical(p_col_cnt14)),
action_information21 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt15),'X'),
'X',action_information21,
fnd_number.number_to_canonical(p_col_cnt15)),
action_information22 = decode(nvl(fnd_number.number_to_canonical(p_col_tot),'X'),
'X',action_information22,
fnd_number.number_to_canonical(p_col_tot))
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_BON_EARN'
and action_information1 = fnd_number.number_to_canonical(p_item_pay_id)
and action_information3 = p_item_type
and action_information4 = fnd_number.number_to_canonical(p_item_id);
insert into pay_action_information(
action_information_id,
object_version_number,
action_context_id,
action_context_type,
effective_date,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10,
action_information11,
action_information12,
action_information13,
action_information14,
action_information15,
action_information16,
action_information17,
action_information18,
action_information19,
action_information20,
action_information21,
action_information22)
values(
p_action_information_id,
p_object_version_number,
p_assignment_action_id,
p_action_context_type,
p_effective_date,
p_assignment_id,
p_action_information_category,
fnd_number.number_to_canonical(p_item_pay_id),
fnd_number.number_to_canonical(p_item_pay_ord),
p_item_type,
fnd_number.number_to_canonical(p_item_id),
fnd_number.number_to_canonical(p_item_rep_seq),
p_item_rep_name,
fnd_number.number_to_canonical(p_col_cnt1),
fnd_number.number_to_canonical(p_col_cnt2),
fnd_number.number_to_canonical(p_col_cnt3),
fnd_number.number_to_canonical(p_col_cnt4),
fnd_number.number_to_canonical(p_col_cnt5),
fnd_number.number_to_canonical(p_col_cnt6),
fnd_number.number_to_canonical(p_col_cnt7),
fnd_number.number_to_canonical(p_col_cnt8),
fnd_number.number_to_canonical(p_col_cnt9),
fnd_number.number_to_canonical(p_col_cnt10),
fnd_number.number_to_canonical(p_col_cnt11),
fnd_number.number_to_canonical(p_col_cnt12),
fnd_number.number_to_canonical(p_col_cnt13),
fnd_number.number_to_canonical(p_col_cnt14),
fnd_number.number_to_canonical(p_col_cnt15),
fnd_number.number_to_canonical(p_col_tot));
procedure update_bon_dct(
p_assignment_action_id in number,
p_assignment_id in number,
p_effective_date in date,
p_arch_assignment_action_id_o in number,
p_item_pay_id in number,
p_item_pay_ord in number,
p_item_type in varchar2,
p_item_id in number,
p_item_rep_seq in number,
p_item_rep_name in varchar2,
p_col_cnt1 in number,
p_col_cnt2 in number,
p_col_cnt3 in number,
p_col_cnt4 in number,
p_col_cnt5 in number,
p_col_cnt6 in number,
p_col_cnt7 in number,
p_col_cnt8 in number,
p_col_cnt9 in number,
p_col_cnt10 in number,
p_col_cnt11 in number,
p_col_cnt12 in number,
p_col_cnt13 in number,
p_col_cnt14 in number,
p_col_cnt15 in number,
p_col_tot in number,
p_action_information_id out nocopy number,
p_object_version_number out nocopy number)
is
--
l_proc varchar2(80) := c_package||'update_bon_dct';
select action_information_id,
object_version_number
from pay_jp_itwb_bon_dct_v
where assignment_action_id = p_assignment_action_id
and item_pay_id = p_item_pay_id
and item_type = p_item_type
and item_id = p_item_id;
select decode(nvl(to_char(col_cnt1,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt1,
decode(nvl(to_char(col_cnt2,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt2,
decode(nvl(to_char(col_cnt3,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt3,
decode(nvl(to_char(col_cnt4,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt4,
decode(nvl(to_char(col_cnt5,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt5,
decode(nvl(to_char(col_cnt6,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt6,
decode(nvl(to_char(col_cnt7,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt7,
decode(nvl(to_char(col_cnt8,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt8,
decode(nvl(to_char(col_cnt9,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt9,
decode(nvl(to_char(col_cnt10,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt10,
decode(nvl(to_char(col_cnt11,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt11,
decode(nvl(to_char(col_cnt12,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt12,
decode(nvl(to_char(col_cnt13,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt13,
decode(nvl(to_char(col_cnt14,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt14,
decode(nvl(to_char(col_cnt15,'MM'),'X'),to_char(p_effective_date,'MM'),0,null) col_cnt15
from pay_jp_itwb_bon_act_efd_v
where assignment_action_id = p_arch_assignment_action_id_o;
update pay_action_information
set object_version_number = p_object_version_number,
action_information2 = decode(nvl(action_information2,'X'),
nvl(fnd_number.number_to_canonical(p_item_pay_ord),'X'),action_information2,
fnd_number.number_to_canonical(p_item_pay_ord)),
action_information5 = decode(nvl(action_information5,'X'),
nvl(fnd_number.number_to_canonical(p_item_rep_seq),'X'),action_information5,
fnd_number.number_to_canonical(p_item_rep_seq)),
action_information6 = decode(nvl(action_information6,'X'),
nvl(p_item_rep_name,'X'),action_information6,
p_item_rep_name),
action_information7 = decode(nvl(l_csr_act_efd.col_cnt1,-1),
0,fnd_number.number_to_canonical(p_col_cnt1),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt1),'X'),
'X',action_information7,
decode(nvl(action_information7,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt1),'X'),action_information7,
fnd_number.number_to_canonical(p_col_cnt1)))),
action_information8 = decode(nvl(l_csr_act_efd.col_cnt2,-1),
0,fnd_number.number_to_canonical(p_col_cnt2),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt2),'X'),
'X',action_information8,
decode(nvl(action_information8,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt2),'X'),action_information8,
fnd_number.number_to_canonical(p_col_cnt2)))),
action_information9 = decode(nvl(l_csr_act_efd.col_cnt3,-1),
0,fnd_number.number_to_canonical(p_col_cnt3),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt3),'X'),
'X',action_information9,
decode(nvl(action_information9,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt3),'X'),action_information9,
fnd_number.number_to_canonical(p_col_cnt3)))),
action_information10 = decode(nvl(l_csr_act_efd.col_cnt4,-1),
0,fnd_number.number_to_canonical(p_col_cnt4),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt4),'X'),
'X',action_information10,
decode(nvl(action_information10,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt4),'X'),action_information10,
fnd_number.number_to_canonical(p_col_cnt4)))),
action_information11 = decode(nvl(l_csr_act_efd.col_cnt5,-1),
0,fnd_number.number_to_canonical(p_col_cnt5),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt5),'X'),
'X',action_information11,
decode(nvl(action_information11,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt5),'X'),action_information11,
fnd_number.number_to_canonical(p_col_cnt5)))),
action_information12 = decode(nvl(l_csr_act_efd.col_cnt6,-1),
0,fnd_number.number_to_canonical(p_col_cnt6),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt6),'X'),
'X',action_information12,
decode(nvl(action_information12,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt6),'X'),action_information12,
fnd_number.number_to_canonical(p_col_cnt6)))),
action_information13 = decode(nvl(l_csr_act_efd.col_cnt7,-1),
0,fnd_number.number_to_canonical(p_col_cnt7),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt7),'X'),
'X',action_information13,
decode(nvl(action_information13,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt7),'X'),action_information13,
fnd_number.number_to_canonical(p_col_cnt7)))),
action_information14 = decode(nvl(l_csr_act_efd.col_cnt8,-1),
0,fnd_number.number_to_canonical(p_col_cnt8),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt8),'X'),
'X',action_information14,
decode(nvl(action_information14,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt8),'X'),action_information14,
fnd_number.number_to_canonical(p_col_cnt8)))),
action_information15 = decode(nvl(l_csr_act_efd.col_cnt9,-1),
0,fnd_number.number_to_canonical(p_col_cnt9),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt9),'X'),
'X',action_information15,
decode(nvl(action_information15,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt9),'X'),action_information15,
fnd_number.number_to_canonical(p_col_cnt9)))),
action_information16 = decode(nvl(l_csr_act_efd.col_cnt10,-1),
0,fnd_number.number_to_canonical(p_col_cnt10),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt10),'X'),
'X',action_information16,
decode(nvl(action_information16,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt10),'X'),action_information16,
fnd_number.number_to_canonical(p_col_cnt10)))),
action_information17 = decode(nvl(l_csr_act_efd.col_cnt11,-1),
0,fnd_number.number_to_canonical(p_col_cnt11),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt11),'X'),
'X',action_information17,
decode(nvl(action_information17,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt11),'X'),action_information17,
fnd_number.number_to_canonical(p_col_cnt11)))),
action_information18 = decode(nvl(l_csr_act_efd.col_cnt12,-1),
0,fnd_number.number_to_canonical(p_col_cnt12),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt12),'X'),
'X',action_information18,
decode(nvl(action_information18,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt12),'X'),action_information18,
fnd_number.number_to_canonical(p_col_cnt12)))),
action_information19 = decode(nvl(l_csr_act_efd.col_cnt13,-1),
0,fnd_number.number_to_canonical(p_col_cnt13),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt13),'X'),
'X',action_information19,
decode(nvl(action_information19,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt13),'X'),action_information19,
fnd_number.number_to_canonical(p_col_cnt13)))),
action_information20 = decode(nvl(l_csr_act_efd.col_cnt14,-1),
0,fnd_number.number_to_canonical(p_col_cnt14),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt14),'X'),
'X',action_information20,
decode(nvl(action_information20,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt14),'X'),action_information20,
fnd_number.number_to_canonical(p_col_cnt14)))),
action_information21 = decode(nvl(l_csr_act_efd.col_cnt15,-1),
0,fnd_number.number_to_canonical(p_col_cnt15),
decode(nvl(fnd_number.number_to_canonical(p_col_cnt15),'X'),
'X',action_information21,
decode(nvl(action_information21,'X'),
nvl(fnd_number.number_to_canonical(p_col_cnt15),'X'),action_information21,
fnd_number.number_to_canonical(p_col_cnt15)))),
action_information22 = decode(nvl(fnd_number.number_to_canonical(p_col_tot),'X'),
'X',action_information22,
decode(nvl(action_information22,'X'),
nvl(fnd_number.number_to_canonical(p_col_tot),'X'),action_information22,
fnd_number.number_to_canonical(p_col_tot)))
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_BON_DCT'
and action_information1 = fnd_number.number_to_canonical(p_item_pay_id)
and action_information3 = p_item_type
and action_information4 = fnd_number.number_to_canonical(p_item_id)
and (nvl(action_information2,'X') <> nvl(fnd_number.number_to_canonical(p_item_pay_ord),'X')
or nvl(action_information5,'X') <> nvl(fnd_number.number_to_canonical(p_item_rep_seq),'X')
or nvl(action_information6,'X') <> nvl(p_item_rep_name,'X')
or ((p_col_cnt1 is not null
or nvl(l_csr_act_efd.col_cnt1,-1) = 0)
and (nvl(action_information7,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt1),'X')))
or ((p_col_cnt2 is not null
or nvl(l_csr_act_efd.col_cnt2,-1) = 0)
and (nvl(action_information8,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt2),'X')))
or ((p_col_cnt3 is not null
or nvl(l_csr_act_efd.col_cnt3,-1) = 0)
and (nvl(action_information9,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt3),'X')))
or ((p_col_cnt4 is not null
or nvl(l_csr_act_efd.col_cnt4,-1) = 0)
and (nvl(action_information10,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt4),'X')))
or ((p_col_cnt5 is not null
or nvl(l_csr_act_efd.col_cnt5,-1) = 0)
and (nvl(action_information11,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt5),'X')))
or ((p_col_cnt6 is not null
or nvl(l_csr_act_efd.col_cnt6,-1) = 0)
and (nvl(action_information12,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt6),'X')))
or ((p_col_cnt7 is not null
or nvl(l_csr_act_efd.col_cnt7,-1) = 0)
and (nvl(action_information13,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt7),'X')))
or ((p_col_cnt8 is not null
or nvl(l_csr_act_efd.col_cnt8,-1) = 0)
and (nvl(action_information14,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt8),'X')))
or ((p_col_cnt9 is not null
or nvl(l_csr_act_efd.col_cnt9,-1) = 0)
and (nvl(action_information15,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt9),'X')))
or ((p_col_cnt10 is not null
or nvl(l_csr_act_efd.col_cnt10,-1) = 0)
and (nvl(action_information16,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt10),'X')))
or ((p_col_cnt11 is not null
or nvl(l_csr_act_efd.col_cnt11,-1) = 0)
and (nvl(action_information17,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt11),'X')))
or ((p_col_cnt12 is not null
or nvl(l_csr_act_efd.col_cnt12,-1) = 0)
and (nvl(action_information18,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt12),'X')))
or ((p_col_cnt13 is not null
or nvl(l_csr_act_efd.col_cnt13,-1) = 0)
and (nvl(action_information19,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt13),'X')))
or ((p_col_cnt14 is not null
or nvl(l_csr_act_efd.col_cnt14,-1) = 0)
and (nvl(action_information20,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt14),'X')))
or ((p_col_cnt15 is not null
or nvl(l_csr_act_efd.col_cnt15,-1) = 0)
and (nvl(action_information21,'X') <> nvl(fnd_number.number_to_canonical(p_col_cnt15),'X')))
or (p_col_tot is not null
and (nvl(action_information22,'X') <> nvl(fnd_number.number_to_canonical(p_col_tot),'X'))));
end update_bon_dct;
select action_information_id,
object_version_number
from pay_jp_itwb_bon_dct_v
where assignment_action_id = p_assignment_action_id
and item_pay_id = p_item_pay_id
and item_type = p_item_type
and item_id = p_item_id;
update pay_action_information
set object_version_number = p_object_version_number,
action_information7 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt1),'X'),
'X',action_information7,
fnd_number.number_to_canonical(p_col_cnt1)),
action_information8 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt2),'X'),
'X',action_information8,
fnd_number.number_to_canonical(p_col_cnt2)),
action_information9 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt3),'X'),
'X',action_information9,
fnd_number.number_to_canonical(p_col_cnt3)),
action_information10 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt4),'X'),
'X',action_information10,
fnd_number.number_to_canonical(p_col_cnt4)),
action_information11 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt5),'X'),
'X',action_information11,
fnd_number.number_to_canonical(p_col_cnt5)),
action_information12 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt6),'X'),
'X',action_information12,
fnd_number.number_to_canonical(p_col_cnt6)),
action_information13 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt7),'X'),
'X',action_information13,
fnd_number.number_to_canonical(p_col_cnt7)),
action_information14 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt8),'X'),
'X',action_information14,
fnd_number.number_to_canonical(p_col_cnt8)),
action_information15 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt9),'X'),
'X',action_information15,
fnd_number.number_to_canonical(p_col_cnt9)),
action_information16 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt10),'X'),
'X',action_information16,
fnd_number.number_to_canonical(p_col_cnt10)),
action_information17 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt11),'X'),
'X',action_information17,
fnd_number.number_to_canonical(p_col_cnt11)),
action_information18 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt12),'X'),
'X',action_information18,
fnd_number.number_to_canonical(p_col_cnt12)),
action_information19 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt13),'X'),
'X',action_information19,
fnd_number.number_to_canonical(p_col_cnt13)),
action_information20 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt14),'X'),
'X',action_information20,
fnd_number.number_to_canonical(p_col_cnt14)),
action_information21 = decode(nvl(fnd_number.number_to_canonical(p_col_cnt15),'X'),
'X',action_information21,
fnd_number.number_to_canonical(p_col_cnt15)),
action_information22 = decode(nvl(fnd_number.number_to_canonical(p_col_tot),'X'),
'X',action_information22,
fnd_number.number_to_canonical(p_col_tot))
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_BON_DCT'
and action_information1 = fnd_number.number_to_canonical(p_item_pay_id)
and action_information3 = p_item_type
and action_information4 = fnd_number.number_to_canonical(p_item_id);
insert into pay_action_information(
action_information_id,
object_version_number,
action_context_id,
action_context_type,
effective_date,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10,
action_information11,
action_information12,
action_information13,
action_information14,
action_information15,
action_information16,
action_information17,
action_information18,
action_information19)
values(
p_action_information_id,
p_object_version_number,
p_assignment_action_id,
p_action_context_type,
p_effective_date,
p_assignment_id,
p_action_information_category,
fnd_number.number_to_canonical(p_item_pay_id),
fnd_number.number_to_canonical(p_item_pay_ord),
p_item_type,
fnd_number.number_to_canonical(p_item_id),
fnd_number.number_to_canonical(p_item_rep_seq),
p_item_rep_name,
fnd_number.number_to_canonical(p_col_mth1),
fnd_number.number_to_canonical(p_col_mth2),
fnd_number.number_to_canonical(p_col_mth3),
fnd_number.number_to_canonical(p_col_mth4),
fnd_number.number_to_canonical(p_col_mth5),
fnd_number.number_to_canonical(p_col_mth6),
fnd_number.number_to_canonical(p_col_mth7),
fnd_number.number_to_canonical(p_col_mth8),
fnd_number.number_to_canonical(p_col_mth9),
fnd_number.number_to_canonical(p_col_mth10),
fnd_number.number_to_canonical(p_col_mth11),
fnd_number.number_to_canonical(p_col_mth12),
fnd_number.number_to_canonical(p_col_tot));
procedure update_wrk(
p_assignment_action_id in number,
p_assignment_id in number,
p_effective_date in date,
p_arch_assignment_action_id_o in number,
p_item_pay_id in number,
p_item_pay_ord in number,
p_item_type in varchar2,
p_item_id in number,
p_item_rep_seq in number,
p_item_rep_name in varchar2,
p_col_mth1 in number,
p_col_mth2 in number,
p_col_mth3 in number,
p_col_mth4 in number,
p_col_mth5 in number,
p_col_mth6 in number,
p_col_mth7 in number,
p_col_mth8 in number,
p_col_mth9 in number,
p_col_mth10 in number,
p_col_mth11 in number,
p_col_mth12 in number,
p_col_tot in number,
p_action_information_id out nocopy number,
p_object_version_number out nocopy number)
is
--
l_proc varchar2(80) := c_package||'update_wrk';
select action_information_id,
object_version_number
from pay_jp_itwb_wrk_v
where assignment_action_id = p_assignment_action_id
and item_pay_id = p_item_pay_id
and item_type = p_item_type
and item_id = p_item_id;
update pay_action_information
set object_version_number = p_object_version_number,
action_information2 = decode(nvl(action_information2,'X'),
nvl(fnd_number.number_to_canonical(p_item_pay_ord),'X'),action_information2,
fnd_number.number_to_canonical(p_item_pay_ord)),
action_information5 = decode(nvl(action_information5,'X'),
nvl(fnd_number.number_to_canonical(p_item_rep_seq),'X'),action_information5,
fnd_number.number_to_canonical(p_item_rep_seq)),
action_information6 = decode(nvl(action_information6,'X'),
nvl(p_item_rep_name,'X'),action_information6,
p_item_rep_name),
action_information7 = decode(nvl(fnd_number.number_to_canonical(p_col_mth1),'X'),
'X',action_information7,
decode(nvl(action_information7,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth1),'X'),action_information7,
fnd_number.number_to_canonical(p_col_mth1))),
action_information8 = decode(nvl(fnd_number.number_to_canonical(p_col_mth2),'X'),
'X',action_information8,
decode(nvl(action_information8,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth2),'X'),action_information8,
fnd_number.number_to_canonical(p_col_mth2))),
action_information9 = decode(nvl(fnd_number.number_to_canonical(p_col_mth3),'X'),
'X',action_information9,
decode(nvl(action_information9,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth3),'X'),action_information9,
fnd_number.number_to_canonical(p_col_mth3))),
action_information10 = decode(nvl(fnd_number.number_to_canonical(p_col_mth4),'X'),
'X',action_information10,
decode(nvl(action_information10,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth4),'X'),action_information10,
fnd_number.number_to_canonical(p_col_mth4))),
action_information11 = decode(nvl(fnd_number.number_to_canonical(p_col_mth5),'X'),
'X',action_information11,
decode(nvl(action_information11,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth5),'X'),action_information11,
fnd_number.number_to_canonical(p_col_mth5))),
action_information12 = decode(nvl(fnd_number.number_to_canonical(p_col_mth6),'X'),
'X',action_information12,
decode(nvl(action_information12,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth6),'X'),action_information12,
fnd_number.number_to_canonical(p_col_mth6))),
action_information13 = decode(nvl(fnd_number.number_to_canonical(p_col_mth7),'X'),
'X',action_information13,
decode(nvl(action_information13,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth7),'X'),action_information13,
fnd_number.number_to_canonical(p_col_mth7))),
action_information14 = decode(nvl(fnd_number.number_to_canonical(p_col_mth8),'X'),
'X',action_information14,
decode(nvl(action_information14,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth8),'X'),action_information14,
fnd_number.number_to_canonical(p_col_mth8))),
action_information15 = decode(nvl(fnd_number.number_to_canonical(p_col_mth9),'X'),
'X',action_information15,
decode(nvl(action_information15,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth9),'X'),action_information15,
fnd_number.number_to_canonical(p_col_mth9))),
action_information16 = decode(nvl(fnd_number.number_to_canonical(p_col_mth10),'X'),
'X',action_information16,
decode(nvl(action_information16,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth10),'X'),action_information16,
fnd_number.number_to_canonical(p_col_mth10))),
action_information17 = decode(nvl(fnd_number.number_to_canonical(p_col_mth11),'X'),
'X',action_information17,
decode(nvl(action_information17,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth11),'X'),action_information17,
fnd_number.number_to_canonical(p_col_mth11))),
action_information18 = decode(nvl(fnd_number.number_to_canonical(p_col_mth12),'X'),
'X',action_information18,
decode(nvl(action_information18,'X'),
nvl(fnd_number.number_to_canonical(p_col_mth12),'X'),action_information18,
fnd_number.number_to_canonical(p_col_mth12))),
action_information19 = decode(nvl(fnd_number.number_to_canonical(p_col_tot),'X'),
'X',action_information19,
decode(nvl(action_information19,'X'),
nvl(fnd_number.number_to_canonical(p_col_tot),'X'),action_information19,
fnd_number.number_to_canonical(p_col_tot)))
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_WRK'
and action_information1 = fnd_number.number_to_canonical(p_item_pay_id)
and action_information3 = p_item_type
and action_information4 = fnd_number.number_to_canonical(p_item_id)
and (nvl(action_information2,'X') <> nvl(fnd_number.number_to_canonical(p_item_pay_ord),'X')
or nvl(action_information5,'X') <> nvl(fnd_number.number_to_canonical(p_item_rep_seq),'X')
or nvl(action_information6,'X') <> nvl(p_item_rep_name,'X')
or (p_col_mth1 is not null
and (nvl(action_information7,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth1),'X')))
or (p_col_mth2 is not null
and (nvl(action_information8,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth2),'X')))
or (p_col_mth3 is not null
and (nvl(action_information9,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth3),'X')))
or (p_col_mth4 is not null
and (nvl(action_information10,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth4),'X')))
or (p_col_mth5 is not null
and (nvl(action_information11,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth5),'X')))
or (p_col_mth6 is not null
and (nvl(action_information12,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth6),'X')))
or (p_col_mth7 is not null
and (nvl(action_information13,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth7),'X')))
or (p_col_mth8 is not null
and (nvl(action_information14,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth8),'X')))
or (p_col_mth9 is not null
and (nvl(action_information15,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth9),'X')))
or (p_col_mth10 is not null
and (nvl(action_information16,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth10),'X')))
or (p_col_mth11 is not null
and (nvl(action_information17,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth11),'X')))
or (p_col_mth12 is not null
and (nvl(action_information18,'X') <> nvl(fnd_number.number_to_canonical(p_col_mth12),'X')))
or (p_col_tot is not null
and (nvl(action_information19,'X') <> nvl(fnd_number.number_to_canonical(p_col_tot),'X'))));
end update_wrk;
select action_information_id,
object_version_number
from pay_jp_itwb_wrk_v
where assignment_action_id = p_assignment_action_id
and item_pay_id = p_item_pay_id
and item_type = p_item_type
and item_id = p_item_id;
update pay_action_information
set object_version_number = p_object_version_number,
action_information7 = decode(nvl(fnd_number.number_to_canonical(p_col_mth1),'X'),
'X',action_information7,
fnd_number.number_to_canonical(p_col_mth1)),
action_information8 = decode(nvl(fnd_number.number_to_canonical(p_col_mth2),'X'),
'X',action_information8,
fnd_number.number_to_canonical(p_col_mth2)),
action_information9 = decode(nvl(fnd_number.number_to_canonical(p_col_mth3),'X'),
'X',action_information9,
fnd_number.number_to_canonical(p_col_mth3)),
action_information10 = decode(nvl(fnd_number.number_to_canonical(p_col_mth4),'X'),
'X',action_information10,
fnd_number.number_to_canonical(p_col_mth4)),
action_information11 = decode(nvl(fnd_number.number_to_canonical(p_col_mth5),'X'),
'X',action_information11,
fnd_number.number_to_canonical(p_col_mth5)),
action_information12 = decode(nvl(fnd_number.number_to_canonical(p_col_mth6),'X'),
'X',action_information12,
fnd_number.number_to_canonical(p_col_mth6)),
action_information13 = decode(nvl(fnd_number.number_to_canonical(p_col_mth7),'X'),
'X',action_information13,
fnd_number.number_to_canonical(p_col_mth7)),
action_information14 = decode(nvl(fnd_number.number_to_canonical(p_col_mth8),'X'),
'X',action_information14,
fnd_number.number_to_canonical(p_col_mth8)),
action_information15 = decode(nvl(fnd_number.number_to_canonical(p_col_mth9),'X'),
'X',action_information15,
fnd_number.number_to_canonical(p_col_mth9)),
action_information16 = decode(nvl(fnd_number.number_to_canonical(p_col_mth10),'X'),
'X',action_information16,
fnd_number.number_to_canonical(p_col_mth10)),
action_information17 = decode(nvl(fnd_number.number_to_canonical(p_col_mth11),'X'),
'X',action_information17,
fnd_number.number_to_canonical(p_col_mth11)),
action_information18 = decode(nvl(fnd_number.number_to_canonical(p_col_mth12),'X'),
'X',action_information18,
fnd_number.number_to_canonical(p_col_mth12)),
action_information19 = decode(nvl(fnd_number.number_to_canonical(p_col_tot),'X'),
'X',action_information19,
fnd_number.number_to_canonical(p_col_tot))
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_WRK'
and action_information1 = fnd_number.number_to_canonical(p_item_pay_id)
and action_information3 = p_item_type
and action_information4 = fnd_number.number_to_canonical(p_item_id);
insert into pay_action_information(
action_information_id,
object_version_number,
action_context_id,
action_context_type,
effective_date,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9)
values(
p_action_information_id,
p_object_version_number,
p_assignment_action_id,
p_action_context_type,
p_effective_date,
p_assignment_id,
p_action_information_category,
fnd_date.date_to_canonical(p_term_date),
p_employer_name,
p_employer_name_kana,
p_employer_address,
p_employer_address_kana,
fnd_number.number_to_canonical(p_txbl_inc),
fnd_number.number_to_canonical(p_si_prem),
fnd_number.number_to_canonical(p_mutual_aid_prem),
fnd_number.number_to_canonical(p_itax));
procedure update_pjob(
p_assignment_action_id in number,
p_assignment_id in number,
p_effective_date in date,
p_arch_assignment_action_id_o in number,
p_proc_assignment_action_id in number,
p_proc_assact_date_o in date,
p_term_date in date,
p_employer_name in varchar2,
p_employer_name_kana in varchar2,
p_employer_address in varchar2,
p_employer_address_kana in varchar2,
p_txbl_inc in number,
p_si_prem in number,
p_mutual_aid_prem in number,
p_itax in number,
p_action_information_id out nocopy number,
p_object_version_number out nocopy number)
is
--
l_proc varchar2(80) := c_package||'update_emp';
end update_pjob;
select assignment_id,
term_date,
employer_name,
employer_name_kana,
employer_address,
employer_address_kana,
txbl_inc,
si_prem,
mutual_aid_prem,
itax
from pay_jp_itwb_pjob_v
where assignment_action_id = p_arch_assignment_action_id_o;
insert into pay_action_information(
action_information_id,
object_version_number,
action_context_id,
action_context_type,
effective_date,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10,
action_information11,
action_information12,
action_information13,
action_information14,
action_information15,
action_information16,
action_information17,
action_information18)
values(
p_action_information_id,
p_object_version_number,
p_assignment_action_id,
p_action_context_type,
p_effective_date,
p_assignment_id,
p_action_information_category,
p_dep_itax_type,
p_declare_flag,
p_spouse_type,
p_spouse_flag,
p_dep_spouse,
p_aged_dep_spouse,
fnd_number.number_to_canonical(p_ord_dep_num),
fnd_number.number_to_canonical(p_sp_dep_num),
fnd_number.number_to_canonical(p_aged_parent_dep_lt_num),
fnd_number.number_to_canonical(p_aged_dep_num),
fnd_number.number_to_canonical(p_ord_disable_num),
fnd_number.number_to_canonical(p_sp_disable_num),
fnd_number.number_to_canonical(p_sp_disable_lt_num),
p_disable_type,
p_widow_type,
p_working_student_type,
fnd_number.number_to_canonical(p_otsu_spouse_dep_num),
p_non_res_flag);
procedure update_dep(
p_assignment_action_id in number,
p_effective_date in date,
p_arch_assignment_action_id_o in number,
p_proc_assignment_action_id in number,
p_dep_itax_type in varchar2,
p_declare_flag in varchar2,
p_spouse_type in varchar2,
p_spouse_flag in varchar2,
p_dep_spouse in varchar2,
p_aged_dep_spouse in varchar2,
p_ord_dep_num in number,
p_sp_dep_num in number,
p_aged_parent_dep_lt_num in number,
p_aged_dep_num in number,
p_ord_disable_num in number,
p_sp_disable_num in number,
p_sp_disable_lt_num in number,
p_disable_type in varchar2,
p_widow_type in varchar2,
p_working_student_type in varchar2,
p_otsu_spouse_dep_num in number,
p_non_res_flag in varchar2,
p_action_information_id out nocopy number,
p_object_version_number out nocopy number)
is
--
l_proc varchar2(80) := c_package||'update_dep';
update pay_action_information
set object_version_number = p_object_version_number,
action_information1 = decode(nvl(action_information1,'X'),
nvl(p_dep_itax_type,'X'),
action_information1,
p_dep_itax_type),
action_information2 = decode(nvl(action_information2,'X'),
nvl(p_declare_flag,'X'),
action_information2,
p_declare_flag),
action_information3 = decode(nvl(action_information3,'X'),
nvl(p_spouse_type,'X'),
action_information3,
p_spouse_type),
action_information4 = decode(nvl(action_information4,'X'),
nvl(p_spouse_flag,'X'),
action_information4,
p_spouse_flag),
action_information5 = decode(nvl(action_information5,'X'),
nvl(p_dep_spouse,'X'),
action_information5,
p_dep_spouse),
action_information6 = decode(nvl(action_information6,'X'),
nvl(p_aged_dep_spouse,'X'),
action_information6,
p_aged_dep_spouse),
action_information7 = decode(nvl(action_information7,'X'),
nvl(fnd_number.number_to_canonical(p_ord_dep_num),'X'),
action_information7,
fnd_number.number_to_canonical(p_ord_dep_num)),
action_information8 = decode(nvl(action_information8,'X'),
nvl(fnd_number.number_to_canonical(p_sp_dep_num),'X'),
action_information8,
fnd_number.number_to_canonical(p_sp_dep_num)),
action_information9 = decode(nvl(action_information9,'X'),
nvl(fnd_number.number_to_canonical(p_aged_parent_dep_lt_num),'X'),
action_information9,
fnd_number.number_to_canonical(p_aged_parent_dep_lt_num)),
action_information10 = decode(nvl(action_information10,'X'),
nvl(fnd_number.number_to_canonical(p_aged_dep_num),'X'),
action_information10,
fnd_number.number_to_canonical(p_aged_dep_num)),
action_information11 = decode(nvl(action_information11,'X'),
nvl(fnd_number.number_to_canonical(p_ord_disable_num),'X'),
action_information11,
fnd_number.number_to_canonical(p_ord_disable_num)),
action_information12 = decode(nvl(action_information12,'X'),
nvl(fnd_number.number_to_canonical(p_sp_disable_num),'X'),
action_information12,
fnd_number.number_to_canonical(p_sp_disable_num)),
action_information13 = decode(nvl(action_information13,'X'),
nvl(fnd_number.number_to_canonical(p_sp_disable_lt_num),'X'),
action_information13,
fnd_number.number_to_canonical(p_sp_disable_lt_num)),
action_information14 = decode(nvl(action_information14,'X'),
nvl(p_disable_type,'X'),
action_information14,
p_disable_type),
action_information15 = decode(nvl(action_information15,'X'),
nvl(p_widow_type,'X'),
action_information15,
p_widow_type),
action_information16 = decode(nvl(action_information16,'X'),
nvl(p_working_student_type,'X'),
action_information16,
p_working_student_type),
action_information17 = decode(nvl(action_information17,'X'),
nvl(fnd_number.number_to_canonical(p_otsu_spouse_dep_num),'X'),
action_information17,
fnd_number.number_to_canonical(p_otsu_spouse_dep_num)),
action_information18 = decode(nvl(action_information18,'X'),
nvl(p_non_res_flag,'X'),
action_information18,
p_non_res_flag)
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_DEP'
and (nvl(action_information1,'X')
<> nvl(p_dep_itax_type,'X')
or nvl(action_information2,'X')
<> nvl(p_declare_flag,'X')
or nvl(action_information3,'X')
<> nvl(p_spouse_type,'X')
or nvl(action_information4,'X')
<> nvl(p_spouse_flag,'X')
or nvl(action_information5,'X')
<> nvl(p_dep_spouse,'X')
or nvl(action_information6,'X')
<> nvl(p_aged_dep_spouse,'X')
or nvl(action_information7,'X')
<> nvl(fnd_number.number_to_canonical(p_ord_dep_num),'X')
or nvl(action_information8,'X')
<> nvl(fnd_number.number_to_canonical(p_sp_dep_num),'X')
or nvl(action_information9,'X')
<> nvl(fnd_number.number_to_canonical(p_aged_parent_dep_lt_num),'X')
or nvl(action_information10,'X')
<> nvl(fnd_number.number_to_canonical(p_aged_dep_num),'X')
or nvl(action_information11,'X')
<> nvl(fnd_number.number_to_canonical(p_ord_disable_num),'X')
or nvl(action_information12,'X')
<> nvl(fnd_number.number_to_canonical(p_sp_disable_num),'X')
or nvl(action_information13,'X')
<> nvl(fnd_number.number_to_canonical(p_sp_disable_lt_num),'X')
or nvl(action_information14,'X')
<> nvl(p_disable_type,'X')
or nvl(action_information15,'X')
<> nvl(p_widow_type,'X')
or nvl(action_information16,'X')
<> nvl(p_working_student_type,'X')
or nvl(action_information17,'X')
<> nvl(fnd_number.number_to_canonical(p_otsu_spouse_dep_num),'X')
or nvl(action_information18,'X')
<> nvl(p_non_res_flag,'X'));
end update_dep;
insert into pay_action_information(
action_information_id,
object_version_number,
action_context_id,
action_context_type,
effective_date,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6)
values(
p_action_information_id,
p_object_version_number,
p_assignment_action_id,
p_action_context_type,
p_effective_date,
p_assignment_id,
p_action_information_category,
fnd_number.number_to_canonical(p_act),
fnd_date.date_to_canonical(p_efd),
fnd_date.date_to_canonical(p_erd),
fnd_number.number_to_canonical(p_pay),
p_yea_type,
p_yea_flag);
procedure update_yea_act(
p_assignment_action_id in number,
p_effective_date in date,
p_arch_assignment_action_id_o in number,
p_proc_assignment_action_id in number,
p_proc_assact_date_o in date,
p_act in number,
p_efd in date,
p_erd in date,
p_pay in number,
p_yea_type in varchar2,
p_yea_flag in varchar2,
p_action_information_id out nocopy number,
p_object_version_number out nocopy number)
is
--
l_proc varchar2(80) := c_package||'update_yea_act';
update pay_action_information
set object_version_number = p_object_version_number,
action_information1 = decode(nvl(action_information1,'X'),
nvl(fnd_number.number_to_canonical(p_act),'X'),
action_information1,
fnd_number.number_to_canonical(p_act)),
action_information2 = decode(nvl(action_information2,'X'),
nvl(fnd_date.date_to_canonical(p_efd),'X'),
action_information2,
fnd_date.date_to_canonical(p_efd)),
action_information3 = decode(nvl(action_information3,'X'),
nvl(fnd_date.date_to_canonical(p_erd),'X'),
action_information3,
fnd_date.date_to_canonical(p_erd)),
action_information4 = decode(nvl(action_information4,'X'),
nvl(fnd_number.number_to_canonical(p_pay),'X'),
action_information4,
fnd_number.number_to_canonical(p_pay)),
action_information5 = decode(nvl(action_information5,'X'),
nvl(p_yea_type,'X'),
action_information5,
p_yea_type),
action_information6 = decode(nvl(action_information6,'X'),
nvl(p_yea_flag,'X'),
action_information6,
p_yea_flag)
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_YEA_ACT'
and (nvl(action_information1,'X')
<> nvl(fnd_number.number_to_canonical(p_act),'X')
or nvl(action_information2,'X')
<> nvl(fnd_date.date_to_canonical(p_efd),'X')
or nvl(action_information3,'X')
<> nvl(fnd_date.date_to_canonical(p_erd),'X')
or nvl(action_information4,'X')
<> nvl(fnd_number.number_to_canonical(p_pay),'X')
or nvl(action_information5,'X')
<> nvl(p_yea_type,'X')
or nvl(action_information6,'X')
<> nvl(p_yea_flag,'X'));
end update_yea_act;
update pay_action_information
set object_version_number = p_object_version_number,
action_information1 = decode(nvl(fnd_number.number_to_canonical(p_act),'X'),
'X',action_information1,
fnd_number.number_to_canonical(p_act)),
action_information2 = decode(nvl(fnd_date.date_to_canonical(p_efd),'X'),
'X',action_information2,
fnd_date.date_to_canonical(p_efd)),
action_information3 = decode(nvl(fnd_date.date_to_canonical(p_erd),'X'),
'X',action_information3,
fnd_date.date_to_canonical(p_erd)),
action_information4 = decode(nvl(fnd_number.number_to_canonical(p_pay),'X'),
'X',action_information4,
fnd_number.number_to_canonical(p_pay)),
action_information5 = decode(nvl(p_yea_type,'X'),
'X',action_information5,
p_yea_type),
action_information6 = decode(nvl(p_yea_flag,'X'),
'X',action_information6,
p_yea_flag)
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_YEA_ACT';
insert into pay_action_information(
action_information_id,
object_version_number,
action_context_id,
action_context_type,
effective_date,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10,
action_information11,
action_information12,
action_information13,
action_information14,
action_information15,
action_information16)
values(
p_action_information_id,
p_object_version_number,
p_assignment_action_id,
p_action_context_type,
p_effective_date,
p_assignment_id,
p_action_information_category,
fnd_number.number_to_canonical(p_sal_txbl_mon),
fnd_number.number_to_canonical(p_sal_txbl_kind),
fnd_number.number_to_canonical(p_sal_txbl),
fnd_number.number_to_canonical(p_bon_txbl_mon),
fnd_number.number_to_canonical(p_bon_txbl_kind),
fnd_number.number_to_canonical(p_bon_txbl),
fnd_number.number_to_canonical(p_spb_txbl_mon),
fnd_number.number_to_canonical(p_spb_txbl_kind),
fnd_number.number_to_canonical(p_spb_txbl),
fnd_number.number_to_canonical(p_bon_txbl_tot),
fnd_number.number_to_canonical(p_txbl),
fnd_number.number_to_canonical(p_sal_itx),
fnd_number.number_to_canonical(p_bon_itx),
fnd_number.number_to_canonical(p_spb_itx),
fnd_number.number_to_canonical(p_bon_itx_tot),
fnd_number.number_to_canonical(p_withhold_itx));
procedure update_yea_pay(
p_assignment_action_id in number,
p_effective_date in date,
p_arch_assignment_action_id_o in number,
p_proc_assignment_action_id in number,
p_proc_assact_date_o in date,
p_sal_txbl_mon in number,
p_sal_txbl_kind in number,
p_sal_txbl in number,
p_bon_txbl_mon in number,
p_bon_txbl_kind in number,
p_bon_txbl in number,
p_spb_txbl_mon in number,
p_spb_txbl_kind in number,
p_spb_txbl in number,
p_bon_txbl_tot in number,
p_txbl in number,
p_sal_itx in number,
p_bon_itx in number,
p_spb_itx in number,
p_bon_itx_tot in number,
p_withhold_itx in number,
p_action_information_id out nocopy number,
p_object_version_number out nocopy number)
is
--
l_proc varchar2(80) := c_package||'update_yea_pay';
update pay_action_information
set object_version_number = p_object_version_number,
action_information1 = decode(nvl(action_information1,'X'),
nvl(fnd_number.number_to_canonical(p_sal_txbl_mon),'X'),
action_information1,
fnd_number.number_to_canonical(p_sal_txbl_mon)),
action_information2 = decode(nvl(action_information2,'X'),
nvl(fnd_number.number_to_canonical(p_sal_txbl_kind),'X'),
action_information2,
fnd_number.number_to_canonical(p_sal_txbl_kind)),
action_information3 = decode(nvl(action_information3,'X'),
nvl(fnd_number.number_to_canonical(p_sal_txbl),'X'),
action_information3,
fnd_number.number_to_canonical(p_sal_txbl)),
action_information4 = decode(nvl(action_information4,'X'),
nvl(fnd_number.number_to_canonical(p_bon_txbl_mon),'X'),
action_information4,
fnd_number.number_to_canonical(p_bon_txbl_mon)),
action_information5 = decode(nvl(action_information5,'X'),
nvl(fnd_number.number_to_canonical(p_bon_txbl_kind),'X'),
action_information5,
fnd_number.number_to_canonical(p_bon_txbl_kind)),
action_information6 = decode(nvl(action_information6,'X'),
nvl(fnd_number.number_to_canonical(p_bon_txbl),'X'),
action_information6,
fnd_number.number_to_canonical(p_bon_txbl)),
action_information7 = decode(nvl(action_information7,'X'),
nvl(fnd_number.number_to_canonical(p_spb_txbl_mon),'X'),
action_information7,
fnd_number.number_to_canonical(p_spb_txbl_mon)),
action_information8 = decode(nvl(action_information8,'X'),
nvl(fnd_number.number_to_canonical(p_spb_txbl_kind),'X'),
action_information8,
fnd_number.number_to_canonical(p_spb_txbl_kind)),
action_information9 = decode(nvl(action_information9,'X'),
nvl(fnd_number.number_to_canonical(p_spb_txbl),'X'),
action_information9,
fnd_number.number_to_canonical(p_spb_txbl)),
action_information10 = decode(nvl(action_information10,'X'),
nvl(fnd_number.number_to_canonical(p_bon_txbl_tot),'X'),
action_information10,
fnd_number.number_to_canonical(p_bon_txbl_tot)),
action_information11 = decode(nvl(action_information11,'X'),
nvl(fnd_number.number_to_canonical(p_txbl),'X'),
action_information11,
fnd_number.number_to_canonical(p_txbl)),
action_information12 = decode(nvl(action_information12,'X'),
nvl(fnd_number.number_to_canonical(p_sal_itx),'X'),
action_information12,
fnd_number.number_to_canonical(p_sal_itx)),
action_information13 = decode(nvl(action_information13,'X'),
nvl(fnd_number.number_to_canonical(p_bon_itx),'X'),
action_information13,
fnd_number.number_to_canonical(p_bon_itx)),
action_information14 = decode(nvl(action_information14,'X'),
nvl(fnd_number.number_to_canonical(p_spb_itx),'X'),
action_information14,
fnd_number.number_to_canonical(p_spb_itx)),
action_information15 = decode(nvl(action_information15,'X'),
nvl(fnd_number.number_to_canonical(p_bon_itx_tot),'X'),
action_information15,
fnd_number.number_to_canonical(p_bon_itx_tot)),
action_information16 = decode(nvl(action_information16,'X'),
nvl(fnd_number.number_to_canonical(p_withhold_itx),'X'),
action_information16,
fnd_number.number_to_canonical(p_withhold_itx))
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_YEA_PAY'
and (nvl(action_information1,'X')
<> nvl(fnd_number.number_to_canonical(p_sal_txbl_mon),'X')
or nvl(action_information2,'X')
<> nvl(fnd_number.number_to_canonical(p_sal_txbl_kind),'X')
or nvl(action_information3,'X')
<> nvl(fnd_number.number_to_canonical(p_sal_txbl),'X')
or nvl(action_information4,'X')
<> nvl(fnd_number.number_to_canonical(p_bon_txbl_mon),'X')
or nvl(action_information5,'X')
<> nvl(fnd_number.number_to_canonical(p_bon_txbl_kind),'X')
or nvl(action_information6,'X')
<> nvl(fnd_number.number_to_canonical(p_bon_txbl),'X')
or nvl(action_information7,'X')
<> nvl(fnd_number.number_to_canonical(p_spb_txbl_mon),'X')
or nvl(action_information8,'X')
<> nvl(fnd_number.number_to_canonical(p_spb_txbl_kind),'X')
or nvl(action_information9,'X')
<> nvl(fnd_number.number_to_canonical(p_spb_txbl),'X')
or nvl(action_information10,'X')
<> nvl(fnd_number.number_to_canonical(p_bon_txbl_tot),'X')
or nvl(action_information11,'X')
<> nvl(fnd_number.number_to_canonical(p_txbl),'X')
or nvl(action_information12,'X')
<> nvl(fnd_number.number_to_canonical(p_sal_itx),'X')
or nvl(action_information13,'X')
<> nvl(fnd_number.number_to_canonical(p_bon_itx),'X')
or nvl(action_information14,'X')
<> nvl(fnd_number.number_to_canonical(p_spb_itx),'X')
or nvl(action_information15,'X')
<> nvl(fnd_number.number_to_canonical(p_bon_itx_tot),'X')
or nvl(action_information16,'X')
<> nvl(fnd_number.number_to_canonical(p_withhold_itx),'X'));
end update_yea_pay;
update pay_action_information
set object_version_number = p_object_version_number,
action_information1 = decode(nvl(fnd_number.number_to_canonical(p_sal_txbl_mon),'X'),
'X',action_information1,
fnd_number.number_to_canonical(p_sal_txbl_mon)),
action_information2 = decode(nvl(fnd_number.number_to_canonical(p_sal_txbl_kind),'X'),
'X',action_information2,
fnd_number.number_to_canonical(p_sal_txbl_kind)),
action_information3 = decode(nvl(fnd_number.number_to_canonical(p_sal_txbl),'X'),
'X',action_information3,
fnd_number.number_to_canonical(p_sal_txbl)),
action_information4 = decode(nvl(fnd_number.number_to_canonical(p_bon_txbl_mon),'X'),
'X',action_information4,
fnd_number.number_to_canonical(p_bon_txbl_mon)),
action_information5 = decode(nvl(fnd_number.number_to_canonical(p_bon_txbl_kind),'X'),
'X',action_information5,
fnd_number.number_to_canonical(p_bon_txbl_kind)),
action_information6 = decode(nvl(fnd_number.number_to_canonical(p_bon_txbl),'X'),
'X',action_information6,
fnd_number.number_to_canonical(p_bon_txbl)),
action_information7 = decode(nvl(fnd_number.number_to_canonical(p_spb_txbl_mon),'X'),
'X',action_information7,
fnd_number.number_to_canonical(p_spb_txbl_mon)),
action_information8 = decode(nvl(fnd_number.number_to_canonical(p_spb_txbl_kind),'X'),
'X',action_information8,
fnd_number.number_to_canonical(p_spb_txbl_kind)),
action_information9 = decode(nvl(fnd_number.number_to_canonical(p_spb_txbl),'X'),
'X',action_information9,
fnd_number.number_to_canonical(p_spb_txbl)),
action_information10 = decode(nvl(fnd_number.number_to_canonical(p_bon_txbl_tot),'X'),
'X',action_information10,
fnd_number.number_to_canonical(p_bon_txbl_tot)),
action_information11 = decode(nvl(fnd_number.number_to_canonical(p_txbl),'X'),
'X',action_information11,
fnd_number.number_to_canonical(p_txbl)),
action_information12 = decode(nvl(fnd_number.number_to_canonical(p_sal_itx),'X'),
'X',action_information12,
fnd_number.number_to_canonical(p_sal_itx)),
action_information13 = decode(nvl(fnd_number.number_to_canonical(p_bon_itx),'X'),
'X',action_information13,
fnd_number.number_to_canonical(p_bon_itx)),
action_information14 = decode(nvl(fnd_number.number_to_canonical(p_spb_itx),'X'),
'X',action_information14,
fnd_number.number_to_canonical(p_spb_itx)),
action_information15 = decode(nvl(fnd_number.number_to_canonical(p_bon_itx_tot),'X'),
'X',action_information15,
fnd_number.number_to_canonical(p_bon_itx_tot)),
action_information16 = decode(nvl(fnd_number.number_to_canonical(p_withhold_itx),'X'),
'X',action_information16,
fnd_number.number_to_canonical(p_withhold_itx))
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_YEA_PAY';
insert into pay_action_information(
action_information_id,
object_version_number,
action_context_id,
action_context_type,
effective_date,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10,
action_information11,
action_information12,
action_information13,
action_information14,
action_information15,
action_information16,
action_information17,
action_information18,
action_information19,
action_information20,
action_information21,
action_information22,
action_information23,
action_information24,
action_information25,
action_information26,
action_information27,
action_information28)
values(
p_action_information_id,
p_object_version_number,
p_assignment_action_id,
p_action_context_type,
p_effective_date,
p_assignment_id,
p_action_information_category,
fnd_number.number_to_canonical(p_sal_dct_si_prem),
fnd_number.number_to_canonical(p_dec_si_prem),
fnd_number.number_to_canonical(p_dec_small_comp_mut_aid_prem),
fnd_number.number_to_canonical(p_life_ins_prem_exm),
fnd_number.number_to_canonical(p_earthquake_ins_prem_exm),
fnd_number.number_to_canonical(p_spouse_sp_exm),
fnd_number.number_to_canonical(p_basic_exm),
fnd_number.number_to_canonical(p_gen_spouse_exm),
fnd_number.number_to_canonical(p_elder_spouse_exm),
fnd_number.number_to_canonical(p_gen_dep_exm),
fnd_number.number_to_canonical(p_specific_dep_exm),
fnd_number.number_to_canonical(p_junior_dep_exm),
fnd_number.number_to_canonical(p_elder_dep_exm),
fnd_number.number_to_canonical(p_elder_parent_lt_exm),
fnd_number.number_to_canonical(p_gen_disabled_exm),
fnd_number.number_to_canonical(p_sev_disabled_exm),
fnd_number.number_to_canonical(p_sev_disabled_lt_exm),
fnd_number.number_to_canonical(p_elder_exm),
fnd_number.number_to_canonical(p_widow_exm),
fnd_number.number_to_canonical(p_sp_widow_exm),
fnd_number.number_to_canonical(p_working_student_exm),
fnd_number.number_to_canonical(p_dep_exm),
fnd_number.number_to_canonical(p_spouse_income),
fnd_number.number_to_canonical(p_individual_pension_prem),
fnd_number.number_to_canonical(p_long_term_nonlife_ins_prem),
fnd_number.number_to_canonical(p_small_comp_mut_aid_prem),
fnd_number.number_to_canonical(p_national_pension_prem),
fnd_number.number_to_canonical(p_income_exm));
procedure update_yea_dct(
p_assignment_action_id in number,
p_effective_date in date,
p_arch_assignment_action_id_o in number,
p_proc_assignment_action_id in number,
p_proc_assact_date_o in date,
p_sal_dct_si_prem in number,
p_dec_si_prem in number,
p_dec_small_comp_mut_aid_prem in number,
p_life_ins_prem_exm in number,
p_earthquake_ins_prem_exm in number,
p_spouse_sp_exm in number,
p_basic_exm in number,
p_gen_spouse_exm in number,
p_elder_spouse_exm in number,
p_gen_dep_exm in number,
p_specific_dep_exm in number,
p_junior_dep_exm in number,
p_elder_dep_exm in number,
p_elder_parent_lt_exm in number,
p_gen_disabled_exm in number,
p_sev_disabled_exm in number,
p_sev_disabled_lt_exm in number,
p_elder_exm in number,
p_widow_exm in number,
p_sp_widow_exm in number,
p_working_student_exm in number,
p_dep_exm in number,
p_spouse_income in number,
p_individual_pension_prem in number,
p_long_term_nonlife_ins_prem in number,
p_small_comp_mut_aid_prem in number,
p_national_pension_prem in number,
p_income_exm in number,
p_action_information_id out nocopy number,
p_object_version_number out nocopy number)
is
--
l_proc varchar2(80) := c_package||'update_yea_dct';
update pay_action_information
set object_version_number = p_object_version_number,
action_information1 = decode(nvl(action_information1,'X'),
nvl(fnd_number.number_to_canonical(p_sal_dct_si_prem),'X'),
action_information1,
fnd_number.number_to_canonical(p_sal_dct_si_prem)),
action_information2 = decode(nvl(action_information2,'X'),
nvl(fnd_number.number_to_canonical(p_dec_si_prem),'X'),
action_information2,
fnd_number.number_to_canonical(p_dec_si_prem)),
action_information3 = decode(nvl(action_information3,'X'),
nvl(fnd_number.number_to_canonical(p_dec_small_comp_mut_aid_prem),'X'),
action_information3,
fnd_number.number_to_canonical(p_dec_small_comp_mut_aid_prem)),
action_information4 = decode(nvl(action_information4,'X'),
nvl(fnd_number.number_to_canonical(p_life_ins_prem_exm),'X'),
action_information4,
fnd_number.number_to_canonical(p_life_ins_prem_exm)),
action_information5 = decode(nvl(action_information5,'X'),
nvl(fnd_number.number_to_canonical(p_earthquake_ins_prem_exm),'X'),
action_information5,
fnd_number.number_to_canonical(p_earthquake_ins_prem_exm)),
action_information6 = decode(nvl(action_information6,'X'),
nvl(fnd_number.number_to_canonical(p_spouse_sp_exm),'X'),
action_information6,
fnd_number.number_to_canonical(p_spouse_sp_exm)),
action_information7 = decode(nvl(action_information7,'X'),
nvl(fnd_number.number_to_canonical(p_basic_exm),'X'),
action_information7,
fnd_number.number_to_canonical(p_basic_exm)),
action_information8 = decode(nvl(action_information8,'X'),
nvl(fnd_number.number_to_canonical(p_gen_spouse_exm),'X'),
action_information8,
fnd_number.number_to_canonical(p_gen_spouse_exm)),
action_information9 = decode(nvl(action_information9,'X'),
nvl(fnd_number.number_to_canonical(p_elder_spouse_exm),'X'),
action_information9,
fnd_number.number_to_canonical(p_elder_spouse_exm)),
action_information10 = decode(nvl(action_information10,'X'),
nvl(fnd_number.number_to_canonical(p_gen_dep_exm),'X'),
action_information10,
fnd_number.number_to_canonical(p_gen_dep_exm)),
action_information11 = decode(nvl(action_information11,'X'),
nvl(fnd_number.number_to_canonical(p_specific_dep_exm),'X'),
action_information11,
fnd_number.number_to_canonical(p_specific_dep_exm)),
action_information12 = decode(nvl(action_information12,'X'),
nvl(fnd_number.number_to_canonical(p_junior_dep_exm),'X'),
action_information12,
fnd_number.number_to_canonical(p_junior_dep_exm)),
action_information13 = decode(nvl(action_information13,'X'),
nvl(fnd_number.number_to_canonical(p_elder_dep_exm),'X'),
action_information13,
fnd_number.number_to_canonical(p_elder_dep_exm)),
action_information14 = decode(nvl(action_information14,'X'),
nvl(fnd_number.number_to_canonical(p_elder_parent_lt_exm),'X'),
action_information14,
fnd_number.number_to_canonical(p_elder_parent_lt_exm)),
action_information15 = decode(nvl(action_information15,'X'),
nvl(fnd_number.number_to_canonical(p_gen_disabled_exm),'X'),
action_information15,
fnd_number.number_to_canonical(p_gen_disabled_exm)),
action_information16 = decode(nvl(action_information16,'X'),
nvl(fnd_number.number_to_canonical(p_sev_disabled_exm),'X'),
action_information16,
fnd_number.number_to_canonical(p_sev_disabled_exm)),
action_information17 = decode(nvl(action_information17,'X'),
nvl(fnd_number.number_to_canonical(p_sev_disabled_lt_exm),'X'),
action_information17,
fnd_number.number_to_canonical(p_sev_disabled_lt_exm)),
action_information18 = decode(nvl(action_information18,'X'),
nvl(fnd_number.number_to_canonical(p_elder_exm),'X'),
action_information18,
fnd_number.number_to_canonical(p_elder_exm)),
action_information19 = decode(nvl(action_information19,'X'),
nvl(fnd_number.number_to_canonical(p_widow_exm),'X'),
action_information19,
fnd_number.number_to_canonical(p_widow_exm)),
action_information20 = decode(nvl(action_information20,'X'),
nvl(fnd_number.number_to_canonical(p_sp_widow_exm),'X'),
action_information20,
fnd_number.number_to_canonical(p_sp_widow_exm)),
action_information21 = decode(nvl(action_information21,'X'),
nvl(fnd_number.number_to_canonical(p_working_student_exm),'X'),
action_information21,
fnd_number.number_to_canonical(p_working_student_exm)),
action_information22 = decode(nvl(action_information22,'X'),
nvl(fnd_number.number_to_canonical(p_dep_exm),'X'),
action_information22,
fnd_number.number_to_canonical(p_dep_exm)),
action_information23 = decode(nvl(action_information23,'X'),
nvl(fnd_number.number_to_canonical(p_spouse_income),'X'),
action_information23,
fnd_number.number_to_canonical(p_spouse_income)),
action_information24 = decode(nvl(action_information24,'X'),
nvl(fnd_number.number_to_canonical(p_individual_pension_prem),'X'),
action_information24,
fnd_number.number_to_canonical(p_individual_pension_prem)),
action_information25 = decode(nvl(action_information25,'X'),
nvl(fnd_number.number_to_canonical(p_long_term_nonlife_ins_prem),'X'),
action_information25,
fnd_number.number_to_canonical(p_long_term_nonlife_ins_prem)),
action_information26 = decode(nvl(action_information26,'X'),
nvl(fnd_number.number_to_canonical(p_small_comp_mut_aid_prem),'X'),
action_information26,
fnd_number.number_to_canonical(p_small_comp_mut_aid_prem)),
action_information27 = decode(nvl(action_information27,'X'),
nvl(fnd_number.number_to_canonical(p_national_pension_prem),'X'),
action_information27,
fnd_number.number_to_canonical(p_national_pension_prem)),
action_information28 = decode(nvl(action_information28,'X'),
nvl(fnd_number.number_to_canonical(p_income_exm),'X'),
action_information28,
fnd_number.number_to_canonical(p_income_exm))
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_YEA_DCT'
and (nvl(action_information1,'X')
<> nvl(fnd_number.number_to_canonical(p_sal_dct_si_prem),'X')
or nvl(action_information2,'X')
<> nvl(fnd_number.number_to_canonical(p_dec_si_prem),'X')
or nvl(action_information3,'X')
<> nvl(fnd_number.number_to_canonical(p_dec_small_comp_mut_aid_prem),'X')
or nvl(action_information4,'X')
<> nvl(fnd_number.number_to_canonical(p_life_ins_prem_exm),'X')
or nvl(action_information5,'X')
<> nvl(fnd_number.number_to_canonical(p_earthquake_ins_prem_exm),'X')
or nvl(action_information6,'X')
<> nvl(fnd_number.number_to_canonical(p_spouse_sp_exm),'X')
or nvl(action_information7,'X')
<> nvl(fnd_number.number_to_canonical(p_basic_exm),'X')
or nvl(action_information8,'X')
<> nvl(fnd_number.number_to_canonical(p_gen_spouse_exm),'X')
or nvl(action_information9,'X')
<> nvl(fnd_number.number_to_canonical(p_elder_spouse_exm),'X')
or nvl(action_information10,'X')
<> nvl(fnd_number.number_to_canonical(p_gen_dep_exm),'X')
or nvl(action_information11,'X')
<> nvl(fnd_number.number_to_canonical(p_specific_dep_exm),'X')
or nvl(action_information12,'X')
<> nvl(fnd_number.number_to_canonical(p_junior_dep_exm),'X')
or nvl(action_information13,'X')
<> nvl(fnd_number.number_to_canonical(p_elder_dep_exm),'X')
or nvl(action_information14,'X')
<> nvl(fnd_number.number_to_canonical(p_elder_parent_lt_exm),'X')
or nvl(action_information15,'X')
<> nvl(fnd_number.number_to_canonical(p_gen_disabled_exm),'X')
or nvl(action_information16,'X')
<> nvl(fnd_number.number_to_canonical(p_sev_disabled_exm),'X')
or nvl(action_information17,'X')
<> nvl(fnd_number.number_to_canonical(p_sev_disabled_lt_exm),'X')
or nvl(action_information18,'X')
<> nvl(fnd_number.number_to_canonical(p_elder_exm),'X')
or nvl(action_information19,'X')
<> nvl(fnd_number.number_to_canonical(p_widow_exm),'X')
or nvl(action_information20,'X')
<> nvl(fnd_number.number_to_canonical(p_sp_widow_exm),'X')
or nvl(action_information21,'X')
<> nvl(fnd_number.number_to_canonical(p_working_student_exm),'X')
or nvl(action_information22,'X')
<> nvl(fnd_number.number_to_canonical(p_dep_exm),'X')
or nvl(action_information23,'X')
<> nvl(fnd_number.number_to_canonical(p_spouse_income),'X')
or nvl(action_information24,'X')
<> nvl(fnd_number.number_to_canonical(p_individual_pension_prem),'X')
or nvl(action_information25,'X')
<> nvl(fnd_number.number_to_canonical(p_long_term_nonlife_ins_prem),'X')
or nvl(action_information26,'X')
<> nvl(fnd_number.number_to_canonical(p_small_comp_mut_aid_prem),'X')
or nvl(action_information27,'X')
<> nvl(fnd_number.number_to_canonical(p_national_pension_prem),'X')
or nvl(action_information28,'X')
<> nvl(fnd_number.number_to_canonical(p_income_exm),'X'));
end update_yea_dct;
update pay_action_information
set object_version_number = p_object_version_number,
action_information1 = decode(nvl(fnd_number.number_to_canonical(p_sal_dct_si_prem),'X'),
'X',action_information1,
fnd_number.number_to_canonical(p_sal_dct_si_prem)),
action_information2 = decode(nvl(fnd_number.number_to_canonical(p_dec_si_prem),'X'),
'X',action_information2,
fnd_number.number_to_canonical(p_dec_si_prem)),
action_information3 = decode(nvl(fnd_number.number_to_canonical(p_dec_small_comp_mut_aid_prem),'X'),
'X',action_information3,
fnd_number.number_to_canonical(p_dec_small_comp_mut_aid_prem)),
action_information4 = decode(nvl(fnd_number.number_to_canonical(p_life_ins_prem_exm),'X'),
'X',action_information4,
fnd_number.number_to_canonical(p_life_ins_prem_exm)),
action_information5 = decode(nvl(fnd_number.number_to_canonical(p_earthquake_ins_prem_exm),'X'),
'X',action_information5,
fnd_number.number_to_canonical(p_earthquake_ins_prem_exm)),
action_information6 = decode(nvl(fnd_number.number_to_canonical(p_spouse_sp_exm),'X'),
'X',action_information6,
fnd_number.number_to_canonical(p_spouse_sp_exm)),
action_information7 = decode(nvl(fnd_number.number_to_canonical(p_basic_exm),'X'),
'X',action_information7,
fnd_number.number_to_canonical(p_basic_exm)),
action_information8 = decode(nvl(fnd_number.number_to_canonical(p_gen_spouse_exm),'X'),
'X',action_information8,
fnd_number.number_to_canonical(p_gen_spouse_exm)),
action_information9 = decode(nvl(fnd_number.number_to_canonical(p_elder_spouse_exm),'X'),
'X',action_information9,
fnd_number.number_to_canonical(p_elder_spouse_exm)),
action_information10 = decode(nvl(fnd_number.number_to_canonical(p_gen_dep_exm),'X'),
'X',action_information10,
fnd_number.number_to_canonical(p_gen_dep_exm)),
action_information11 = decode(nvl(fnd_number.number_to_canonical(p_specific_dep_exm),'X'),
'X',action_information11,
fnd_number.number_to_canonical(p_specific_dep_exm)),
action_information12 = decode(nvl(fnd_number.number_to_canonical(p_junior_dep_exm),'X'),
'X',action_information12,
fnd_number.number_to_canonical(p_junior_dep_exm)),
action_information13 = decode(nvl(fnd_number.number_to_canonical(p_elder_dep_exm),'X'),
'X',action_information13,
fnd_number.number_to_canonical(p_elder_dep_exm)),
action_information14 = decode(nvl(fnd_number.number_to_canonical(p_elder_parent_lt_exm),'X'),
'X',action_information14,
fnd_number.number_to_canonical(p_elder_parent_lt_exm)),
action_information15 = decode(nvl(fnd_number.number_to_canonical(p_gen_disabled_exm),'X'),
'X',action_information15,
fnd_number.number_to_canonical(p_gen_disabled_exm)),
action_information16 = decode(nvl(fnd_number.number_to_canonical(p_sev_disabled_exm),'X'),
'X',action_information16,
fnd_number.number_to_canonical(p_sev_disabled_exm)),
action_information17 = decode(nvl(fnd_number.number_to_canonical(p_sev_disabled_lt_exm),'X'),
'X',action_information17,
fnd_number.number_to_canonical(p_sev_disabled_lt_exm)),
action_information18 = decode(nvl(fnd_number.number_to_canonical(p_elder_exm),'X'),
'X',action_information18,
fnd_number.number_to_canonical(p_elder_exm)),
action_information19 = decode(nvl(fnd_number.number_to_canonical(p_widow_exm),'X'),
'X',action_information19,
fnd_number.number_to_canonical(p_widow_exm)),
action_information20 = decode(nvl(fnd_number.number_to_canonical(p_sp_widow_exm),'X'),
'X',action_information20,
fnd_number.number_to_canonical(p_sp_widow_exm)),
action_information21 = decode(nvl(fnd_number.number_to_canonical(p_working_student_exm),'X'),
'X',action_information21,
fnd_number.number_to_canonical(p_working_student_exm)),
action_information22 = decode(nvl(fnd_number.number_to_canonical(p_dep_exm),'X'),
'X',action_information22,
fnd_number.number_to_canonical(p_dep_exm)),
action_information23 = decode(nvl(fnd_number.number_to_canonical(p_spouse_income),'X'),
'X',action_information23,
fnd_number.number_to_canonical(p_spouse_income)),
action_information24 = decode(nvl(fnd_number.number_to_canonical(p_individual_pension_prem),'X'),
'X',action_information24,
fnd_number.number_to_canonical(p_individual_pension_prem)),
action_information25 = decode(nvl(fnd_number.number_to_canonical(p_long_term_nonlife_ins_prem),'X'),
'X',action_information25,
fnd_number.number_to_canonical(p_long_term_nonlife_ins_prem)),
action_information26 = decode(nvl(fnd_number.number_to_canonical(p_small_comp_mut_aid_prem),'X'),
'X',action_information26,
fnd_number.number_to_canonical(p_small_comp_mut_aid_prem)),
action_information27 = decode(nvl(fnd_number.number_to_canonical(p_national_pension_prem),'X'),
'X',action_information27,
fnd_number.number_to_canonical(p_national_pension_prem)),
action_information28 = decode(nvl(fnd_number.number_to_canonical(p_income_exm),'X'),
'X',action_information28,
fnd_number.number_to_canonical(p_income_exm))
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_YEA_DCT';
insert into pay_action_information(
action_information_id,
object_version_number,
action_context_id,
action_context_type,
effective_date,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7)
values(
p_action_information_id,
p_object_version_number,
p_assignment_action_id,
p_action_context_type,
p_effective_date,
p_assignment_id,
p_action_information_category,
fnd_number.number_to_canonical(p_amt_aft_emp_inc_dct),
fnd_number.number_to_canonical(p_net_txbl_income),
fnd_number.number_to_canonical(p_annual_tax),
fnd_number.number_to_canonical(p_act_housing_loan_tax_credit),
fnd_number.number_to_canonical(p_yea_annual_tax),
fnd_number.number_to_canonical(p_yea_tax_pay),
fnd_number.number_to_canonical(p_yea_income_tax));
procedure update_yea_tax(
p_assignment_action_id in number,
p_effective_date in date,
p_arch_assignment_action_id_o in number,
p_proc_assignment_action_id in number,
p_proc_assact_date_o in date,
p_amt_aft_emp_inc_dct in number,
p_net_txbl_income in number,
p_annual_tax in number,
p_act_housing_loan_tax_credit in number,
p_yea_annual_tax in number,
p_yea_income_tax in number,
p_yea_tax_pay in number,
p_action_information_id out nocopy number,
p_object_version_number out nocopy number)
is
--
l_proc varchar2(80) := c_package||'update_yea_tax';
update pay_action_information
set object_version_number = p_object_version_number,
action_information1 = decode(nvl(action_information1,'X'),
nvl(fnd_number.number_to_canonical(p_amt_aft_emp_inc_dct),'X'),
action_information1,
fnd_number.number_to_canonical(p_amt_aft_emp_inc_dct)),
action_information2 = decode(nvl(action_information2,'X'),
nvl(fnd_number.number_to_canonical(p_net_txbl_income),'X'),
action_information2,
fnd_number.number_to_canonical(p_net_txbl_income)),
action_information3 = decode(nvl(action_information3,'X'),
nvl(fnd_number.number_to_canonical(p_annual_tax),'X'),
action_information3,
fnd_number.number_to_canonical(p_annual_tax)),
action_information4 = decode(nvl(action_information4,'X'),
nvl(fnd_number.number_to_canonical(p_act_housing_loan_tax_credit),'X'),
action_information4,
fnd_number.number_to_canonical(p_act_housing_loan_tax_credit)),
action_information5 = decode(nvl(action_information5,'X'),
nvl(fnd_number.number_to_canonical(p_yea_annual_tax),'X'),
action_information5,
fnd_number.number_to_canonical(p_yea_annual_tax)),
action_information6 = decode(nvl(action_information6,'X'),
nvl(fnd_number.number_to_canonical(p_yea_tax_pay),'X'),
action_information6,
fnd_number.number_to_canonical(p_yea_tax_pay)),
action_information7 = decode(nvl(action_information7,'X'),
nvl(fnd_number.number_to_canonical(p_yea_income_tax),'X'),
action_information7,
fnd_number.number_to_canonical(p_yea_income_tax))
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_YEA_TAX'
and (nvl(action_information1,'X')
<> nvl(fnd_number.number_to_canonical(p_amt_aft_emp_inc_dct),'X')
or nvl(action_information2,'X')
<> nvl(fnd_number.number_to_canonical(p_net_txbl_income),'X')
or nvl(action_information3,'X')
<> nvl(fnd_number.number_to_canonical(p_annual_tax),'X')
or nvl(action_information4,'X')
<> nvl(fnd_number.number_to_canonical(p_act_housing_loan_tax_credit),'X')
or nvl(action_information5,'X')
<> nvl(fnd_number.number_to_canonical(p_yea_annual_tax),'X')
or nvl(action_information6,'X')
<> nvl(fnd_number.number_to_canonical(p_yea_tax_pay),'X')
or nvl(action_information7,'X')
<> nvl(fnd_number.number_to_canonical(p_yea_income_tax),'X'));
end update_yea_tax;
update pay_action_information
set object_version_number = p_object_version_number,
action_information1 = decode(nvl(fnd_number.number_to_canonical(p_amt_aft_emp_inc_dct),'X'),
'X',action_information1,
fnd_number.number_to_canonical(p_amt_aft_emp_inc_dct)),
action_information2 = decode(nvl(fnd_number.number_to_canonical(p_net_txbl_income),'X'),
'X',action_information2,
fnd_number.number_to_canonical(p_net_txbl_income)),
action_information3 = decode(nvl(fnd_number.number_to_canonical(p_annual_tax),'X'),
'X',action_information3,
fnd_number.number_to_canonical(p_annual_tax)),
action_information4 = decode(nvl(fnd_number.number_to_canonical(p_act_housing_loan_tax_credit),'X'),
'X',action_information4,
fnd_number.number_to_canonical(p_act_housing_loan_tax_credit)),
action_information5 = decode(nvl(fnd_number.number_to_canonical(p_yea_annual_tax),'X'),
'X',action_information5,
fnd_number.number_to_canonical(p_yea_annual_tax)),
action_information6 = decode(nvl(fnd_number.number_to_canonical(p_yea_tax_pay),'X'),
'X',action_information6,
fnd_number.number_to_canonical(p_yea_tax_pay)),
action_information7 = decode(nvl(fnd_number.number_to_canonical(p_yea_income_tax),'X'),
'X',action_information7,
fnd_number.number_to_canonical(p_yea_income_tax))
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_YEA_TAX';
insert into pay_action_information(
action_information_id,
object_version_number,
action_context_id,
action_context_type,
effective_date,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10,
action_information11,
action_information12,
action_information13,
action_information14,
action_information15,
action_information16,
action_information17,
action_information18,
action_information19,
action_information20,
action_information21,
action_information22,
action_information23,
action_information24,
action_information25,
action_information26)
values(
p_action_information_id,
p_object_version_number,
p_assignment_action_id,
p_action_context_type,
p_effective_date,
p_assignment_id,
p_action_information_category,
p_data_type,
p_information1,
p_information2,
p_information3,
p_information4,
p_information5,
p_information6,
p_information7,
p_information8,
p_information9,
p_information10,
p_information11,
p_information12,
p_information13,
p_information14,
p_information15,
p_information16,
p_information17,
p_information18,
p_information19,
p_information20,
p_information21,
p_information22,
p_information23,
p_information24,
p_information25);
procedure update_emp_cust(
p_assignment_action_id in number,
p_assignment_id in number,
p_effective_date in date,
p_arch_assignment_action_id_o in number,
p_data_type in varchar2,
p_information1 in varchar2,
p_information2 in varchar2,
p_information3 in varchar2,
p_information4 in varchar2,
p_information5 in varchar2,
p_information6 in varchar2,
p_information7 in varchar2,
p_information8 in varchar2,
p_information9 in varchar2,
p_information10 in varchar2,
p_information11 in varchar2,
p_information12 in varchar2,
p_information13 in varchar2,
p_information14 in varchar2,
p_information15 in varchar2,
p_information16 in varchar2,
p_information17 in varchar2,
p_information18 in varchar2,
p_information19 in varchar2,
p_information20 in varchar2,
p_information21 in varchar2,
p_information22 in varchar2,
p_information23 in varchar2,
p_information24 in varchar2,
p_information25 in varchar2,
p_action_information_id out nocopy number,
p_object_version_number out nocopy number)
is
--
l_proc varchar2(80) := c_package||'update_emp_cust';
update pay_action_information
set object_version_number = p_object_version_number,
action_information2 = decode(nvl(action_information2,'X'),
nvl(p_information1,'X'),action_information2,
p_information1),
action_information3 = decode(nvl(action_information3,'X'),
nvl(p_information2,'X'),action_information3,
p_information2),
action_information4 = decode(nvl(action_information4,'X'),
nvl(p_information3,'X'),action_information4,
p_information3),
action_information5 = decode(nvl(action_information5,'X'),
nvl(p_information4,'X'),action_information5,
p_information4),
action_information6 = decode(nvl(action_information6,'X'),
nvl(p_information5,'X'),action_information6,
p_information5),
action_information7 = decode(nvl(action_information7,'X'),
nvl(p_information6,'X'),action_information7,
p_information6),
action_information8 = decode(nvl(action_information8,'X'),
nvl(p_information7,'X'),action_information8,
p_information7),
action_information9 = decode(nvl(action_information9,'X'),
nvl(p_information8,'X'),action_information9,
p_information8),
action_information10 = decode(nvl(action_information10,'X'),
nvl(p_information9,'X'),action_information10,
p_information9),
action_information11 = decode(nvl(action_information11,'X'),
nvl(p_information10,'X'),action_information11,
p_information10),
action_information12 = decode(nvl(action_information12,'X'),
nvl(p_information11,'X'),action_information12,
p_information11),
action_information13 = decode(nvl(action_information13,'X'),
nvl(p_information12,'X'),action_information13,
p_information12),
action_information14 = decode(nvl(action_information14,'X'),
nvl(p_information13,'X'),action_information14,
p_information13),
action_information15 = decode(nvl(action_information15,'X'),
nvl(p_information14,'X'),action_information15,
p_information14),
action_information16 = decode(nvl(action_information16,'X'),
nvl(p_information15,'X'),action_information16,
p_information15),
action_information17 = decode(nvl(action_information17,'X'),
nvl(p_information16,'X'),action_information17,
p_information16),
action_information18 = decode(nvl(action_information18,'X'),
nvl(p_information17,'X'),action_information18,
p_information17),
action_information19 = decode(nvl(action_information19,'X'),
nvl(p_information18,'X'),action_information19,
p_information18),
action_information20 = decode(nvl(action_information20,'X'),
nvl(p_information19,'X'),action_information20,
p_information19),
action_information21 = decode(nvl(action_information21,'X'),
nvl(p_information20,'X'),action_information21,
p_information20),
action_information22 = decode(nvl(action_information22,'X'),
nvl(p_information21,'X'),action_information22,
p_information21),
action_information23 = decode(nvl(action_information23,'X'),
nvl(p_information22,'X'),action_information23,
p_information22),
action_information24 = decode(nvl(action_information24,'X'),
nvl(p_information23,'X'),action_information24,
p_information23),
action_information25 = decode(nvl(action_information25,'X'),
nvl(p_information24,'X'),action_information25,
p_information24),
action_information26 = decode(nvl(action_information26,'X'),
nvl(p_information25,'X'),action_information26,
p_information25)
where action_information_id = p_action_information_id
and object_version_number < p_object_version_number
and action_context_id = p_assignment_action_id
and action_context_type = 'AAP'
and action_information_category = 'JP_ITWB_EMP_CUST'
and action_information1 = p_data_type
and ((p_information1 is not null
and (nvl(action_information2,'X') <> nvl(p_information1,'X')))
or (p_information2 is not null
and (nvl(action_information3,'X') <> nvl(p_information2,'X')))
or (p_information3 is not null
and (nvl(action_information4,'X') <> nvl(p_information3,'X')))
or (p_information4 is not null
and (nvl(action_information5,'X') <> nvl(p_information4,'X')))
or (p_information5 is not null
and (nvl(action_information6,'X') <> nvl(p_information5,'X')))
or (p_information6 is not null
and (nvl(action_information7,'X') <> nvl(p_information6,'X')))
or (p_information7 is not null
and (nvl(action_information8,'X') <> nvl(p_information7,'X')))
or (p_information8 is not null
and (nvl(action_information9,'X') <> nvl(p_information8,'X')))
or (p_information9 is not null
and (nvl(action_information10,'X') <> nvl(p_information9,'X')))
or (p_information10 is not null
and (nvl(action_information11,'X') <> nvl(p_information10,'X')))
or (p_information11 is not null
and (nvl(action_information12,'X') <> nvl(p_information11,'X')))
or (p_information12 is not null
and (nvl(action_information13,'X') <> nvl(p_information12,'X')))
or (p_information13 is not null
and (nvl(action_information14,'X') <> nvl(p_information13,'X')))
or (p_information14 is not null
and (nvl(action_information15,'X') <> nvl(p_information14,'X')))
or (p_information15 is not null
and (nvl(action_information16,'X') <> nvl(p_information15,'X')))
or (p_information16 is not null
and (nvl(action_information17,'X') <> nvl(p_information16,'X')))
or (p_information17 is not null
and (nvl(action_information18,'X') <> nvl(p_information17,'X')))
or (p_information18 is not null
and (nvl(action_information19,'X') <> nvl(p_information18,'X')))
or (p_information19 is not null
and (nvl(action_information20,'X') <> nvl(p_information19,'X')))
or (p_information20 is not null
and (nvl(action_information21,'X') <> nvl(p_information20,'X')))
or (p_information21 is not null
and (nvl(action_information22,'X') <> nvl(p_information21,'X')))
or (p_information22 is not null
and (nvl(action_information23,'X') <> nvl(p_information22,'X')))
or (p_information23 is not null
and (nvl(action_information24,'X') <> nvl(p_information23,'X')))
or (p_information24 is not null
and (nvl(action_information25,'X') <> nvl(p_information24,'X')))
or (p_information25 is not null
and (nvl(action_information26,'X') <> nvl(p_information25,'X'))));
end update_emp_cust;
procedure update_emp_cust(
p_assignment_action_id in number,
p_assignment_id in number,
p_effective_date in date,
p_arch_assignment_action_id_o in number,
p_data_type in varchar2,
p_information1 in date,
p_information2 in date,
p_information3 in date,
p_information4 in date,
p_information5 in date,
p_information6 in date,
p_information7 in date,
p_information8 in date,
p_information9 in date,
p_information10 in date,
p_information11 in date,
p_information12 in date,
p_information13 in date,
p_information14 in date,
p_information15 in date,
p_information16 in date,
p_information17 in date,
p_information18 in date,
p_information19 in date,
p_information20 in date,
p_information21 in date,
p_information22 in date,
p_information23 in date,
p_information24 in date,
p_information25 in date,
p_action_information_id out nocopy number,
p_object_version_number out nocopy number)
is
--
l_proc varchar2(80) := c_package||'update_emp_cust';
update_emp_cust(
p_assignment_action_id => p_assignment_action_id,
p_assignment_id => p_assignment_id,
p_effective_date => p_effective_date,
p_arch_assignment_action_id_o => p_arch_assignment_action_id_o,
p_data_type => p_data_type,
p_information1 => fnd_date.date_to_canonical(p_information1),
p_information2 => fnd_date.date_to_canonical(p_information2),
p_information3 => fnd_date.date_to_canonical(p_information3),
p_information4 => fnd_date.date_to_canonical(p_information4),
p_information5 => fnd_date.date_to_canonical(p_information5),
p_information6 => fnd_date.date_to_canonical(p_information6),
p_information7 => fnd_date.date_to_canonical(p_information7),
p_information8 => fnd_date.date_to_canonical(p_information8),
p_information9 => fnd_date.date_to_canonical(p_information9),
p_information10 => fnd_date.date_to_canonical(p_information10),
p_information11 => fnd_date.date_to_canonical(p_information11),
p_information12 => fnd_date.date_to_canonical(p_information12),
p_information13 => fnd_date.date_to_canonical(p_information13),
p_information14 => fnd_date.date_to_canonical(p_information14),
p_information15 => fnd_date.date_to_canonical(p_information15),
p_information16 => fnd_date.date_to_canonical(p_information16),
p_information17 => fnd_date.date_to_canonical(p_information17),
p_information18 => fnd_date.date_to_canonical(p_information18),
p_information19 => fnd_date.date_to_canonical(p_information19),
p_information20 => fnd_date.date_to_canonical(p_information20),
p_information21 => fnd_date.date_to_canonical(p_information21),
p_information22 => fnd_date.date_to_canonical(p_information22),
p_information23 => fnd_date.date_to_canonical(p_information23),
p_information24 => fnd_date.date_to_canonical(p_information24),
p_information25 => fnd_date.date_to_canonical(p_information25),
p_action_information_id => p_action_information_id,
p_object_version_number => p_object_version_number);
end update_emp_cust;
procedure update_emp_cust(
p_assignment_action_id in number,
p_assignment_id in number,
p_effective_date in date,
p_arch_assignment_action_id_o in number,
p_data_type in varchar2,
p_information1 in number,
p_information2 in number,
p_information3 in number,
p_information4 in number,
p_information5 in number,
p_information6 in number,
p_information7 in number,
p_information8 in number,
p_information9 in number,
p_information10 in number,
p_information11 in number,
p_information12 in number,
p_information13 in number,
p_information14 in number,
p_information15 in number,
p_information16 in number,
p_information17 in number,
p_information18 in number,
p_information19 in number,
p_information20 in number,
p_information21 in number,
p_information22 in number,
p_information23 in number,
p_information24 in number,
p_information25 in number,
p_action_information_id out nocopy number,
p_object_version_number out nocopy number)
is
--
l_proc varchar2(80) := c_package||'update_emp_cust';
update_emp_cust(
p_assignment_action_id => p_assignment_action_id,
p_assignment_id => p_assignment_id,
p_effective_date => p_effective_date,
p_arch_assignment_action_id_o => p_arch_assignment_action_id_o,
p_data_type => p_data_type,
p_information1 => fnd_number.number_to_canonical(p_information1),
p_information2 => fnd_number.number_to_canonical(p_information2),
p_information3 => fnd_number.number_to_canonical(p_information3),
p_information4 => fnd_number.number_to_canonical(p_information4),
p_information5 => fnd_number.number_to_canonical(p_information5),
p_information6 => fnd_number.number_to_canonical(p_information6),
p_information7 => fnd_number.number_to_canonical(p_information7),
p_information8 => fnd_number.number_to_canonical(p_information8),
p_information9 => fnd_number.number_to_canonical(p_information9),
p_information10 => fnd_number.number_to_canonical(p_information10),
p_information11 => fnd_number.number_to_canonical(p_information11),
p_information12 => fnd_number.number_to_canonical(p_information12),
p_information13 => fnd_number.number_to_canonical(p_information13),
p_information14 => fnd_number.number_to_canonical(p_information14),
p_information15 => fnd_number.number_to_canonical(p_information15),
p_information16 => fnd_number.number_to_canonical(p_information16),
p_information17 => fnd_number.number_to_canonical(p_information17),
p_information18 => fnd_number.number_to_canonical(p_information18),
p_information19 => fnd_number.number_to_canonical(p_information19),
p_information20 => fnd_number.number_to_canonical(p_information20),
p_information21 => fnd_number.number_to_canonical(p_information21),
p_information22 => fnd_number.number_to_canonical(p_information22),
p_information23 => fnd_number.number_to_canonical(p_information23),
p_information24 => fnd_number.number_to_canonical(p_information24),
p_information25 => fnd_number.number_to_canonical(p_information25),
p_action_information_id => p_action_information_id,
p_object_version_number => p_object_version_number);
end update_emp_cust;
procedure select_item_info(
p_itax_organization_id in number,
p_effective_date in date)
is
--
l_proc varchar2(80) := c_package||'select_item_info';
select pap.payroll_id,
pap.payroll_name,
hoi.org_information2 category,
hoi.org_information3 type,
fnd_number.canonical_to_number(hoi.org_information4) elm_id,
fnd_number.canonical_to_number(hoi.org_information5) iv_id,
fnd_number.canonical_to_number(hoi.org_information6) bal_id,
fnd_number.canonical_to_number(hoi.org_information7) dbal_id,
hoi.org_information8 rep_name,
fnd_number.canonical_to_number(hoi.org_information9) rep_seq,
hoi.org_information10 null_valid
from hr_organization_information hoi,
pay_all_payrolls_f pap
where hoi.organization_id = p_itax_organization_id
and hoi.org_information_context = c_org_information_context
and pap.payroll_id = fnd_number.canonical_to_number(hoi.org_information1)
and p_effective_date
between pap.effective_start_date and pap.effective_end_date
order by
pap.payroll_name,
hoi.org_information2,
fnd_number.canonical_to_number(hoi.org_information9),
hoi.org_information8;
select pet.element_name,
piv.name
from pay_input_values_f_vl piv,
pay_element_types_f_vl pet
where piv.input_value_id = p_input_value_id
and p_effective_date
between piv.effective_start_date and piv.effective_end_date
and pet.element_type_id = piv.element_type_id
and pet.element_type_id = p_element_type_id
and p_effective_date
between pet.effective_start_date and pet.effective_end_date;
select pbt.balance_name,
pbd.database_item_suffix
from pay_defined_balances pdb,
pay_balance_types_vl pbt,
pay_balance_dimensions_vl pbd
where pdb.defined_balance_id = p_defined_balance_id
and pbt.balance_type_id = pdb.balance_type_id
and pbt.balance_type_id = p_balance_type_id
and pbd.balance_dimension_id = pdb.balance_dimension_id;
end select_item_info;
select hoi.organization_id
from hr_organization_information hoi,
hr_all_organization_units hou
where hoi.organization_id = p_itax_organization_id
and hoi.org_information_context = 'JP_TAX_SWOT_INFO'
and hou.organization_id = hoi.organization_id;
select payroll_id
from pay_all_payrolls_f
where payroll_id = p_payroll_id
and p_effective_date
between effective_start_date and effective_end_date;
select piv.input_value_id
from pay_input_values_f piv,
pay_element_types_f pet
where piv.input_value_id = p_input_value_id
and p_effective_date
between piv.effective_start_date and piv.effective_end_date
and nvl(piv.business_group_id,p_business_group_id) = p_business_group_id
and nvl(piv.legislation_code,c_legislation_code) = c_legislation_code
and pet.element_type_id = piv.element_type_id
and pet.element_type_id = p_element_type_id
and p_effective_date
between pet.effective_start_date and pet.effective_end_date
and nvl(pet.business_group_id,p_business_group_id) = p_business_group_id
and nvl(pet.legislation_code,c_legislation_code) = c_legislation_code;
select pdb.defined_balance_id
from pay_defined_balances pdb
where pdb.defined_balance_id = p_defined_balance_id
and nvl(pdb.business_group_id,p_business_group_id) = p_business_group_id
and nvl(pdb.legislation_code,c_legislation_code) = c_legislation_code
and pdb.balance_type_id = p_balance_type_id;
select hoi.org_information_id
from hr_organization_information hoi
where hoi.organization_id = p_itax_organization_id
and hoi.org_information_context = c_org_information_context
and fnd_number.canonical_to_number(hoi.org_information1) = p_payroll_id
and hoi.org_information2 = p_category
and hoi.org_information3 = p_type
and fnd_number.canonical_to_number(decode(p_type,'IV',hoi.org_information5,'DBAL',hoi.org_information7,null))
= to_number(decode(p_type,'IV',to_char(p_input_value_id),'DBAL',to_char(p_defined_balance_id),null));
procedure insert_item_info(
p_business_group_id in number,
p_itax_organization_id in number,
p_effective_date in date,
p_payroll_id in number,
p_category in varchar2,
p_type in varchar2,
p_element_type_id in number,
p_input_value_id in number,
p_balance_type_id in number,
p_defined_balance_id in number,
p_report_name in varchar2,
p_report_sequence in number,
p_null_valid in varchar2,
p_validate in boolean default true)
is
--
l_proc varchar2(80) := c_package||'insert_item_info';
hr_org_information_pkg.insert_row(
x_rowid => l_rowid,
x_org_information_id => l_org_information_id,
x_org_information_context => c_org_information_context,
x_organization_id => p_itax_organization_id,
x_org_information1 => fnd_number.number_to_canonical(p_payroll_id),
x_org_information2 => p_category,
x_org_information3 => p_type,
x_org_information4 => fnd_number.number_to_canonical(l_element_type_id),
x_org_information5 => fnd_number.number_to_canonical(l_input_value_id),
x_org_information6 => fnd_number.number_to_canonical(l_balance_type_id),
x_org_information7 => fnd_number.number_to_canonical(l_defined_balance_id),
x_org_information8 => p_report_name,
x_org_information9 => p_report_sequence,
x_org_information10 => p_null_valid,
x_org_information11 => null,
x_org_information12 => null,
x_org_information13 => null,
x_org_information14 => null,
x_org_information15 => null,
x_org_information16 => null,
x_org_information17 => null,
x_org_information18 => null,
x_org_information19 => null,
x_org_information20 => fnd_number.number_to_canonical(l_item_id),
x_attribute_category => null,
x_attribute1 => null,
x_attribute2 => null,
x_attribute3 => null,
x_attribute4 => null,
x_attribute5 => null,
x_attribute6 => null,
x_attribute7 => null,
x_attribute8 => null,
x_attribute9 => null,
x_attribute10 => null,
x_attribute11 => null,
x_attribute12 => null,
x_attribute13 => null,
x_attribute14 => null,
x_attribute15 => null,
x_attribute16 => null,
x_attribute17 => null,
x_attribute18 => null,
x_attribute19 => null,
x_attribute20 => null);
end insert_item_info;
procedure insert_item_info(
p_business_group_id in number,
p_itax_organization_id in number,
p_effective_date in date,
p_payroll_id in number,
p_category in varchar2,
p_type in varchar2,
p_item_business_group_id in number,
p_item_legislation_code in varchar2,
p_element_name in varchar2,
p_input_value_name in varchar2,
p_balance_name in varchar2,
p_dimension_name in varchar2,
p_report_name in varchar2,
p_report_sequence in number,
p_null_valid in varchar2,
p_validate in boolean default true)
is
--
l_proc varchar2(80) := c_package||'insert_item_info';
insert_item_info(
p_business_group_id => p_business_group_id,
p_itax_organization_id => p_itax_organization_id,
p_effective_date => p_effective_date,
p_payroll_id => p_payroll_id,
p_category => p_category,
p_type => p_type,
p_element_type_id => l_element_type_id,
p_input_value_id => l_input_value_id,
p_balance_type_id => l_balance_type_id,
p_defined_balance_id => l_defined_balance_id,
p_report_name => p_report_name,
p_report_sequence => p_report_sequence,
p_null_valid => p_null_valid,
p_validate => p_validate);
end insert_item_info;
procedure delete_item_info(
p_itax_organization_id in number,
p_effective_date in date,
p_payroll_id in number,
p_category in varchar2,
p_type in varchar2,
p_item_id in number)
is
--
l_proc varchar2(80) := c_package||'delete_item_info';
select hoi.rowid row_id,
hoi.org_information_id
from hr_organization_information hoi
where hoi.organization_id = p_itax_organization_id
and hoi.org_information_context = c_org_information_context
and fnd_number.canonical_to_number(hoi.org_information1) = p_payroll_id
and hoi.org_information2 = p_category
and hoi.org_information3 = p_type
and fnd_number.canonical_to_number(decode(p_type,'IV',hoi.org_information5,'DBAL',hoi.org_information7,null)) = p_item_id;
hr_org_information_pkg.delete_row(
x_rowid => l_rowid);
hr_utility.trace('delete l_org_information_id : '||to_char(l_org_information_id));
end delete_item_info;