[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select business_group_id,
effective_date,
legislative_parameters
into g_business_group_id,
g_effective_date,
l_legislative_parameters
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
'SELECT DISTINCT per.person_id
FROM pay_payroll_actions ppa,
per_all_people_f per
WHERE ppa.payroll_action_id = :payroll_action_id
AND per.business_group_id + 0 = ppa.business_group_id
ORDER BY per.person_id';
select distinct
asg.assignment_id
from per_all_assignments_f asg
where asg.person_id
between p_start_person_id and p_end_person_id
and asg.business_group_id + 0 = g_business_group_id
and asg.assignment_type = 'E'
and asg.effective_start_date <= g_effective_date
and asg.effective_end_date >= g_soy
and (
/*
--
-- The reason why this SQL uses exists statement, not using UNION ALL,
-- is the latter "exists" statement performance is worse than former
-- "exists" statement. If former "exists" statement returns "true",
-- then latter "exists" is not checked.
--
exists(
select null
from pay_jp_itax_person_v2 person,
pay_jp_itax_arch_v2 arch
where person.assignment_id = asg.assignment_id
and person.effective_date
between g_soy and g_effective_date
and ((g_itax_organization_id is null) or (person.itax_organization_id = g_itax_organization_id))
and ( ( g_include_terminated_flag = 'Y'
and ( (g_termination_date_from is null and g_termination_date_to is null)
or person.actual_termination_date
between nvl(g_termination_date_from, person.actual_termination_date)
and nvl(g_termination_date_to, person.actual_termination_date)
)
)
or (g_include_terminated_flag = 'N' and person.actual_termination_date is null)
)
and arch.action_context_id = person.action_context_id
and arch.effective_date = person.effective_date
and ((g_payroll_id is null) or (arch.payroll_id = g_payroll_id))
)
or
*/
exists(
select null
from pay_jp_wic_assacts_v wic
where wic.assignment_id = asg.assignment_id
and wic.effective_date
between g_soy and g_effective_date
and ((g_itax_organization_id is null) or (wic.itax_organization_id = g_itax_organization_id))
and ((g_payroll_id is null) or (wic.payroll_id = g_payroll_id))
and ( ( g_include_terminated_flag = 'Y'
and ( (g_termination_date_from is null and g_termination_date_to is null)
or wic.actual_termination_date
between nvl(g_termination_date_from, wic.actual_termination_date)
and nvl(g_termination_date_to, wic.actual_termination_date)
)
)
or (g_include_terminated_flag = 'N' and wic.actual_termination_date is null)
)
/* Following check is removed for inappropriate archive data deletion in ARCHIVE_CODE
and (
g_rearchive_flag = 'Y'
or not exists(
select null
from pay_jp_itax_arch_v2 v
where v.assignment_id = wic.assignment_id
-- effective_date validation is for performance reason.
and v.effective_date = wic.effective_date
and v.assignment_action_id = wic.assignment_action_id)
)
*/
)
);
SELECT pay_assignment_actions_s.nextval
INTO l_locking_action_id
FROM dual;
select wic.assignment_action_id,
wic.action_sequence,
wic.payroll_id,
nvl(pay.prl_information1, g_bg_dpnt_ref_type) dpnt_ref_type,
nvl(fnd_date.canonical_to_date(pay_core_utils.get_parameter('ITAX_DPNT_EFFECTIVE_DATE', wic.legislative_parameters)),
wic.effective_date) dpnt_effective_date,
wic.effective_date,
wic.date_earned,
wic.itax_organization_id,
wic.itax_category,
wic.itax_yea_category,
wic.person_id,
wic.date_start,
wic.leaving_reason,
wic.actual_termination_date,
wic.employment_category
from pay_jp_wic_assacts_v wic,
pay_all_payrolls_f pay
where wic.assignment_id = cp_assignment_id
and wic.effective_date
between g_soy and g_effective_date
and ((g_itax_organization_id is null) or (wic.itax_organization_id = g_itax_organization_id))
and ((g_payroll_id is null) or (wic.payroll_id = g_payroll_id))
--
-- Do not check termination_date in ARCHIVE_CODE here
-- which has already been validated in ASSIGNMENT_ACTION_CODE.
--
/*
and ( ( g_include_terminated_flag = 'Y'
and ( (g_termination_date_from is null and g_termination_date_to is null)
or wic.actual_termination_date
between nvl(g_termination_date_from, wic.actual_termination_date)
and nvl(g_termination_date_to, wic.actual_termination_date)
)
)
or (g_include_terminated_flag = 'N' and wic.actual_termination_date is null)
)
*/
and pay.payroll_id = wic.payroll_id
and wic.effective_date
between pay.effective_start_date and pay.effective_end_date
order by wic.effective_date;
select person.action_information_id
from pay_jp_itax_arch_v2 arch,
pay_jp_itax_person_v2 person
where arch.assignment_id = cp_assignment_id
-- effective_date validation is for performance reason.
and arch.effective_date = cp_effective_date
and arch.assignment_action_id = cp_assignment_action_id
and person.action_context_id = arch.action_context_id
and person.effective_date = arch.effective_date;
select person.action_information_id,
person.action_context_id,
person.effective_date,
other2.ITW_USER_DESC_KANJI,
other2.WTM_USER_DESC_KANJI,
other2.WTM_USER_DESC_KANA
from pay_jp_itax_person_v2 person,
pay_jp_itax_arch_v2 arch,
pay_jp_itax_other2_v2 other2
where person.assignment_id = cp_assignment_id
and person.effective_date
between g_soy and g_eoy
and person.itax_organization_id = cp_itax_organization_id
and arch.action_context_id = person.action_context_id
and arch.effective_date = person.effective_date
and arch.itax_category = cp_itax_category
and other2.action_context_id = person.action_context_id
and other2.effective_date = person.effective_date
for update of person.action_information_id nowait
order by person.effective_date;
-- following cursor will be deleted.
-- When g_rearchive_flag = 'N' then all records except for records
-- derived by csr_prev_archive or created by current assact
-- stored in "cp_action_information_ids" will be deleted.
--
cursor csr_delete_archives(
cp_assignment_id number,
cp_action_information_ids fnd_table_of_number) is
select person.action_information_id,
person.action_context_id,
person.effective_date
from pay_jp_itax_person_v2 person,
pay_jp_itax_arch_v2 arch
where person.assignment_id = cp_assignment_id
and person.effective_date
between g_soy and g_effective_date
-- bug.5657929
and person.action_information_category = 'JP_ITAX_PERSON'
and person.action_information_id not in (
select *
from table(cp_action_information_ids))
and ((g_itax_organization_id is null) or (person.itax_organization_id = g_itax_organization_id))
and arch.action_context_id = person.action_context_id
and arch.effective_date = person.effective_date
and ((g_payroll_id is null) or (arch.payroll_id = g_payroll_id))
for update of person.action_information_id nowait;
select per.employee_number,
per.last_name last_name_kana,
per.first_name first_name_kana,
per.per_information18 last_name_kanji,
per.per_information19 first_name_kanji,
per.sex,
per.date_of_birth,
nvl(adrr.address_id, adrc.address_id) address_id,
rtrim(substrb(decode(adrr.address_id, null,
adrc.region_1 || adrc.region_2 || adrc.region_3,
adrr.region_1 || adrr.region_2 || adrr.region_3), 1, 240)) address_kana,
rtrim(substrb(decode(adrr.address_id, null,
adrc.address_line1 || adrc.address_line2 || adrc.address_line3,
adrr.address_line1 || adrr.address_line2 || adrr.address_line3), 1, 240)) address_kanji,
decode(adrr.address_id, null, adrc.country, adrr.country) country,
decode(adrr.address_id, null, adrc.town_or_city, adrr.town_or_city) district_code,
asg.organization_id
from per_all_assignments_f asg,
per_all_people_f per,
per_addresses adrr,
per_addresses adrc
where asg.assignment_id = cp_assignment_id
and cp_effective_date
between asg.effective_start_date and asg.effective_end_date
and per.person_id = asg.person_id
and cp_effective_date
between per.effective_start_date and per.effective_end_date
and adrr.person_id(+) = per.person_id
and adrr.address_type(+) = 'JP_R'
and cp_adr_effective_date
between adrr.date_from(+) and nvl(adrr.date_to(+), cp_adr_effective_date)
and adrc.person_id(+) = per.person_id
and adrc.address_type(+) = 'JP_C'
and cp_adr_effective_date
between adrc.date_from(+) and nvl(adrc.date_to(+), cp_adr_effective_date);
select hoi2.org_information3 reference_number,
lpad(hoi2.org_information4, 10, '0') reference_number1,
lpad(hoi2.org_information5, 12, '0') reference_number2,
rtrim(substrb(hoi1.org_information6 || hoi1.org_information7 || hoi1.org_information8, 1, 240))
salary_payer_address_kanji,
hoi1.org_information1 salary_payer_name_kanji,
hoi1.org_information12 salary_payer_telephone_number,
hoi2.org_information2 tax_office_number
from hr_all_organization_units hou,
hr_organization_information hoi1,
hr_organization_information hoi2
where hou.organization_id = cp_itax_organization_id
and hoi1.organization_id(+) = hou.organization_id
and hoi1.org_information_context(+) = 'JP_TAX_SWOT_INFO'
and hoi2.organization_id(+) = hou.organization_id
and hoi2.org_information_context(+) = 'JP_ITAX_WITHHELD_INFO';
procedure delete_archive(
p_action_information_id in number,
p_action_context_id in number,
p_effective_date in date)
is
l_count number;
select count(*)
into l_count
from pay_jp_itax_person_v2
where action_context_id = p_action_context_id
and action_information_id <> p_action_information_id;
-- When multiple archives exist, delete the correponding action info only.
-- If not, rollback the assignment action itself.
--
if l_count > 0 then
delete
from pay_action_information
where action_context_type = 'AAP'
and action_context_id = p_action_context_id
and effective_date = p_effective_date;
fnd_message.set_name('PAY', 'PAY_JP_ITW_ARCHIVE_DELETED');
end delete_archive;
select asg.assignment_id
into l_assignment_id
from pay_assignment_actions paa,
per_all_assignments_f asg
where paa.assignment_action_id = p_assignment_action_id
and asg.assignment_id = paa.assignment_id
and asg.effective_start_date <= g_effective_date
and asg.effective_end_date >= g_soy
and not exists(
select null
from per_all_assignments_f asg2
where asg2.assignment_id = asg.assignment_id
and asg2.effective_start_date < asg.effective_start_date
and asg2.effective_end_date >= g_soy)
for update of asg.assignment_id nowait;
-- then delete all archive data.
-- This specification seems to be confusing for user, there's possibility to
-- comment out the following code.
-- Fixed to replace not only ITW but also WTM description.
-- Note old override flag is not checked.
--
for l_archive_rec in csr_replace_archive(l_assignment_id,
l_wic_rec.itax_organization_id,
l_wic_rec.itax_category) loop
hr_utility.set_location(c_proc, 61);
delete_archive(
l_archive_rec.action_information_id,
l_archive_rec.action_context_id,
l_archive_rec.effective_date);
-- Delete old archive data
--
for l_archive_rec in csr_delete_archives(l_assignment_id, l_action_information_ids) loop
delete_archive(
l_archive_rec.action_information_id,
l_archive_rec.action_context_id,
l_archive_rec.effective_date);
select 'Y'
from dual
where exists(
select null
from pay_action_information
where action_context_id = p_payroll_action_id
and action_context_type = 'PA');
select paa.assignment_action_id
from pay_assignment_actions paa
where paa.payroll_action_id = p_payroll_action_id
and paa.action_status = 'C'
and not exists(
select null
from pay_action_information pai
where pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP');
-- Delete completed assignment actions without PAY_ACTION_INFORMATION.
--
for l_rec in csr_assacts loop
py_rollback_pkg.rollback_ass_action(l_rec.assignment_action_id);