[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pdb.defined_balance_id
from pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where pbt.balance_type_id = pdb.balance_type_id
and pbt.balance_name = p_bal_name
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pbd.dimension_name in ('_ASG_YTD_MTH','_ASG_YTD_BON')
and pbt.legislation_code = 'KR'
and pbd.legislation_code = 'KR';
select pdb.defined_balance_id
from pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where pbt.balance_type_id = pdb.balance_type_id
and pbt.balance_name = p_bal_name
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pbd.dimension_name = '_ASG_YTD'
and pbt.legislation_code = 'KR'
and pbd.legislation_code = 'KR';
select ayi_information3
from per_kr_assignment_yea_info
where information_type = 'KR_YEA_ENTRY_STATUS'
and assignment_id = p_assignment_id
and target_year = p_target_year;
Select 'Y'
From per_assignment_extra_info
Where assignment_id = p_assignment_id
And to_number(to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY')) = p_target_year
And information_type in ('KR_YEA_SP_TAX_EXEM_INFO'
,'KR_YEA_SP_TAX_EXEM_INFO2'
,'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
,'KR_YEA_HOU_EXP_TAX_EXEM_INFO'
,'KR_YEA_DPNT_EDUC_TAX_EXEM_INFO'
,'KR_YEA_PREV_ER_INFO'
,'KR_YEA_TAX_BREAK_INFO'
,'KR_YEA_TAX_EXEM_INFO'
,'KR_YEA_OVS_TAX_BREAK_INFO'
,'KR_YEA_DETAIL_DONATION_INFO' --3506170
,'KR_YEA_EMP_EXPENSE_DETAILS') ; -- Bug 5372366
Select 'Y'
From per_assignment_extra_info
Where assignment_id = p_assignment_id
And information_type ='KR_YEA_FW_TAX_BREAK_INFO';
select info.assignment_id,
info.information_type,
info.ayi_information1,
info.ayi_information2,
info.ayi_information3,
info.ayi_information4,
info.ayi_information5,
info.ayi_information6,
info.ayi_information7,
info.ayi_information8,
info.ayi_information9,
info.ayi_information10,
info.ayi_information11,
info.ayi_information12,
info.ayi_information13,
info.ayi_information14,
info.ayi_information15,
info.ayi_information16,
info.ayi_information17,
info.ayi_information18,
info.ayi_information19,
info.ayi_information20,
info.ayi_information21,
info.ayi_information22,
info.ayi_information23,
info.ayi_information24,
info.ayi_information25,
info.ayi_information26,
info.ayi_information27,
info.ayi_information28,
info.ayi_information29,
info.ayi_information30
from per_kr_assignment_yea_info info
where assignment_id = p_assignment_id
and target_year = p_target_year
and information_type in ('KR_YEA_SP_TAX_EXEM_INFO'
,'KR_YEA_SP_TAX_EXEM_INFO2'
,'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
,'KR_YEA_HOU_EXP_TAX_EXEM_INFO'
,'KR_YEA_DPNT_EDUC_TAX_EXEM_INFO'
,'KR_YEA_PREV_ER_INFO'
,'KR_YEA_TAX_BREAK_INFO'
,'KR_YEA_TAX_EXEM_INFO'
,'KR_YEA_OVS_TAX_BREAK_INFO'
,'KR_YEA_DETAIL_DONATION_INFO' --3506170
,'KR_YEA_EMP_EXPENSE_DETAILS') -- Bug 5372366
Union All
select info.assignment_id,
info.information_type,
info.ayi_information1,
info.ayi_information2,
info.ayi_information3,
info.ayi_information4,
info.ayi_information5,
info.ayi_information6,
info.ayi_information7,
info.ayi_information8,
info.ayi_information9,
info.ayi_information10,
info.ayi_information11,
info.ayi_information12,
info.ayi_information13,
info.ayi_information14,
info.ayi_information15,
info.ayi_information16,
info.ayi_information17,
info.ayi_information18,
info.ayi_information19,
info.ayi_information20,
info.ayi_information21,
info.ayi_information22,
info.ayi_information23,
info.ayi_information24,
info.ayi_information25,
info.ayi_information26,
info.ayi_information27,
info.ayi_information28,
info.ayi_information29,
info.ayi_information30
from per_kr_assignment_yea_info info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_FW_TAX_BREAK_INFO';
select person_id
from per_assignments_f
where assignment_id = p_assignment_id
and p_eff_date between effective_start_date and effective_end_date ;
select session_id
from fnd_sessions
where session_id = userenv('sessionid');
insert into fnd_sessions(session_id,effective_date)
values (userenv('sessionid'),l_effective_date);
delete from per_assignment_extra_info
where assignment_id = p_assignment_id
and to_number(to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY')) = p_target_year
And information_type in ('KR_YEA_SP_TAX_EXEM_INFO'
,'KR_YEA_SP_TAX_EXEM_INFO2'
,'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
,'KR_YEA_HOU_EXP_TAX_EXEM_INFO'
,'KR_YEA_DPNT_EDUC_TAX_EXEM_INFO'
,'KR_YEA_PREV_ER_INFO'
,'KR_YEA_TAX_BREAK_INFO'
,'KR_YEA_TAX_EXEM_INFO'
,'KR_YEA_OVS_TAX_BREAK_INFO'
,'KR_YEA_DETAIL_DONATION_INFO' --3506170
,'KR_YEA_EMP_EXPENSE_DETAILS') ; -- Bug 5372366
delete from per_assignment_extra_info
where assignment_id = p_assignment_id
And information_type = 'KR_YEA_FW_TAX_BREAK_INFO';
function update_allowed(p_business_group_id in number,
p_assignment_id in number,
p_target_year in number,
p_effective_date in date)
return varchar2
is
-- cursor to get the Update Allowed Flag of the assignment
cursor csr_update_allowed_flag is
select nvl(aei_information2, 'Y')
from per_assignment_extra_info
where information_type = 'KR_YEA_ENTRY_UPDATE'
and aei_information1 = p_target_year
and assignment_id = p_assignment_id;
select 'Y'
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_id = p_assignment_id
and paa.source_action_id is null
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type = 'B'
and ppa.action_status = 'C'
and trunc(ppa.effective_date, 'YYYY') = trunc(p_effective_date, 'YYYY')
and pay_kr_ff_functions_pkg.get_legislative_parameter(ppa.payroll_action_id, 'REPORT_TYPE', null) = 'YEA'
and pay_kr_ff_functions_pkg.get_legislative_parameter(ppa.payroll_action_id, 'REPORT_QUALIFIER', null) = 'KR'
and pay_kr_ff_functions_pkg.get_legislative_parameter(ppa.payroll_action_id, 'REPORT_CATEGORY', null) IN ('N','I');
select posv.org_structure_version_id
from per_organization_structures pos,
per_org_structure_versions posv
where pos.primary_structure_flag = 'Y'
and pos.business_group_id = p_business_group_id
and pos.organization_structure_id = posv.organization_structure_id
and p_effective_date between posv.date_from and nvl(posv.date_to,p_effective_date);
select min(fnd_date.canonical_to_date(hoi.org_information2)),
max(fnd_date.canonical_to_date(hoi.org_information3)),
max(fnd_date.canonical_to_date(hoi.org_information4))
from (select pose.organization_id_parent,
level hierarchy_level,
pose.organization_id_child
from per_org_structure_elements pose
start with pose.organization_id_child = p_emp_org_id
and pose.org_structure_version_id = p_version
connect by prior pose.organization_id_parent = organization_id_child
) org,
hr_organization_information hoi
where hoi.organization_id = org.organization_id_parent
and hoi.org_information1 = p_target_year
and hoi.org_information_context = 'KR_YEA_ENTRY_PERIOD_ORG'
group by organization_id_child, org.hierarchy_level
order by org.hierarchy_level asc;
select min(fnd_date.canonical_to_date(hoi.org_information2)),
max(fnd_date.canonical_to_date(hoi.org_information3)),
max(fnd_date.canonical_to_date(hoi.org_information4))
from hr_organization_information hoi
where hoi.organization_id = p_business_group_id
and hoi.org_information1 = p_target_year
and hoi.org_information_context ='KR_YEA_ENTRY_PERIOD_BG';
select min(fnd_date.canonical_to_date(hoi.org_information2)),
max(fnd_date.canonical_to_date(hoi.org_information3)),
max(fnd_date.canonical_to_date(hoi.org_information4))
from hr_organization_information hoi
where hoi.organization_id = p_emp_org
and hoi.org_information1 = p_target_year
and hoi.org_information_context ='KR_YEA_ENTRY_PERIOD_ORG';
select organization_id
from per_assignments_f paf
where assignment_id = p_assignment_id
and p_effective_date between paf.effective_start_date and paf.effective_end_date;
l_update_allowed varchar2(1);
l_asg_update_allowed varchar2(1);
l_update_cut_off_date date;
hr_utility.set_location('pay_kr_yea_sshr_utils_pkg.update_allowed', 10);
l_update_allowed := 'N';
l_update_cut_off_date;
l_update_cut_off_date;
l_update_cut_off_date;
l_update_allowed := 'N';
hr_utility.trace('l_update_cut_off_date = '|| l_update_cut_off_date);
l_update_allowed := 'N';
if l_update_cut_off_date is null then
l_update_cut_off_date := to_date('31.12.4712','DD.MM.YYYY');
l_update_allowed := 'Y';
elsif p_effective_date between l_entry_end_date and l_update_cut_off_date then
-- check the assignment level update allowed flag.
l_asg_update_allowed := null;
open csr_update_allowed_flag;
fetch csr_update_allowed_flag into l_asg_update_allowed;
close csr_update_allowed_flag;
if l_asg_update_allowed = 'Y' then
l_update_allowed := 'Y';
l_update_allowed := 'N';
end if; -- assignment level update allowed
hr_utility.trace('Assgn Level Update Allowed = '||l_update_allowed);
l_update_allowed := 'N';
return l_update_allowed;
end update_allowed;
select formula_id
from ff_formulas_f
where formula_name = upper(p_formula_name)
and business_group_id = p_business_group_id
and p_effective_date between effective_start_date and effective_end_date;
select session_id from fnd_sessions
where session_id = userenv('sessionid');
select person_id
from per_assignments_f
where assignment_id = p_assignment_id;
insert into fnd_sessions(session_id,effective_date)
values (userenv('sessionid'),l_effective_date);
select nvl(aei_information2,'Y') update_allowed,
assignment_extra_info_id info_id
from per_assignment_extra_info
where assignment_id = P_ASSIGNMENT_ID
and information_type = 'KR_YEA_ENTRY_UPDATE'
and aei_information1 = P_TARGET_YEAR
for update nowait;
insert into per_assignment_extra_info(
assignment_extra_info_id,
assignment_id,
aei_information1,
aei_information2,
information_type,
aei_information_category)
values(
per_assignment_extra_info_s.nextval,
P_ASSIGNMENT_ID,
P_TARGET_YEAR,
'Y',
'KR_YEA_ENTRY_UPDATE',
'KR_YEA_ENTRY_UPDATE');
else -- update the record
if l_current_access = 'N' then
l_future_access := 'Y';
update per_assignment_extra_info
set aei_information2 = l_future_access
where assignment_extra_info_id = l_info_id;
procedure delete_all_records(
P_ASSIGNMENT_ID in varchar2,
P_TARGET_YEAR in varchar2)
is
begin
delete from per_assignment_extra_info
where assignment_id = p_assignment_id
and to_number(to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY')) = p_target_year
And information_type in ('KR_YEA_SP_TAX_EXEM_INFO'
,'KR_YEA_SP_TAX_EXEM_INFO2'
,'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
,'KR_YEA_HOU_EXP_TAX_EXEM_INFO'
,'KR_YEA_DPNT_EDUC_TAX_EXEM_INFO'
,'KR_YEA_PREV_ER_INFO'
,'KR_YEA_TAX_BREAK_INFO'
,'KR_YEA_TAX_EXEM_INFO'
,'KR_YEA_OVS_TAX_BREAK_INFO'
,'KR_YEA_DETAIL_DONATION_INFO' --3506170
,'KR_YEA_EMP_EXPENSE_DETAILS'); -- Bug 5372366
select peev.screen_entry_value
from pay_element_entries_f pee,
pay_element_entry_values_f peev,
pay_input_values_f piv,
pay_element_types_f pet
where pet.element_name = 'TAX'
and pet.legislation_code = 'KR'
and pee.entry_type = 'E'
and pet.element_type_id = piv.element_type_id
and piv.name = 'NON_RESIDENT_FLAG'
and pee.element_entry_id = peev.element_entry_id
and piv.input_value_id = peev.input_value_id
and pee.assignment_id = p_assignment_id
and p_effective_date between pee.effective_start_date and pee.effective_end_date
and p_effective_date between peev.effective_start_date and peev.effective_end_date
and p_effective_date between piv.effective_start_date and piv.effective_end_date;
select nvl(pei.PEI_INFORMATION3,'N')
from PER_PEOPLE_EXTRA_INFO pei,
per_assignments_f paf
where paf.assignment_id = p_assgn_id
and p_eff_date between paf.effective_start_date and paf.effective_end_DATE
and pei.person_id = paf.person_id
and information_type = 'PER_KR_RELATED_YEA_INFORMATION';
procedure update_house_holder(p_person_id in varchar2,
p_house_holder_code in varchar2)
is
cursor csr_record_exist(p_per_id in varchar2) is
select person_extra_info_id, object_version_number
from per_people_extra_info
where person_id = p_per_id
and information_type = 'PER_KR_RELATED_YEA_INFORMATION';
hr_person_extra_info_api.update_person_extra_info(
p_validate => false,
p_person_extra_info_id => l_pei_id,
p_object_version_number => l_ovn,
p_pei_information3 => p_house_holder_code);