[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;
cursor csr_delete
is
select assignment_extra_info_id,object_version_number
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_SP_TAX_EXEM_INFO3' /* Bug 9393732 */
,'KR_YEA_DONATION_TOTALS' /* Bug 9393732 */
,'KR_YEA_DONATION_TOTALS1' /* Bug 12820464 */
,'KR_YEA_HOU_RENT_DETAILS' /* Bug 9393732 */
,'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'
,'KR_YEA_EMP_EXPENSE_DETAILS'
,'KR_YEA_TAX_GROUP_INFO'
,'KR_YEA_NON_TAXABLE_EARN_DETAIL'
,'KR_YEA_PREV_ER_INFO2' /* Bug 9737699 */
,'KR_YEA_TAX_EXEM_INFO2' /* Bug 9737699 */
,'KR_YEA_SEP_PEN_DETAILS' /* Bug 9737699 */
,'KR_YEA_PEN_SAVING_DETAILS' /* Bug 9737699 */
,'KR_YEA_HOU_SAVING_DETAILS' /* Bug 9737699 */
,'KR_YEA_LT_STOCK_SAVING_DETAILS');/* Bug 9737699 */
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_SP_TAX_EXEM_INFO3' /* Bug 9393732 */
,'KR_YEA_DONATION_TOTALS' /* Bug 9393732 */
,'KR_YEA_DONATION_TOTALS1' /* Bug 12820464 */
,'KR_YEA_HOU_RENT_DETAILS' /* Bug 9393732 */
,'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' -- Bug 3506170
,'KR_YEA_EMP_EXPENSE_DETAILS' -- Bug 5372366
,'KR_YEA_TAX_GROUP_INFO' -- Bug 7361372
,'KR_YEA_NON_TAXABLE_EARN_DETAIL' -- Bug 8880376
,'KR_YEA_PREV_ER_INFO2' /* Bug 9737699 */
,'KR_YEA_TAX_EXEM_INFO2' /* Bug 9737699 */
,'KR_YEA_SEP_PEN_DETAILS' /* Bug 9737699 */
,'KR_YEA_PEN_SAVING_DETAILS' /* Bug 9737699 */
,'KR_YEA_HOU_SAVING_DETAILS' /* Bug 9737699 */
,'KR_YEA_LT_STOCK_SAVING_DETAILS');/* Bug 9737699 */
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_SP_TAX_EXEM_INFO3' /* Bug 9393732 */
,'KR_YEA_DONATION_TOTALS' /* Bug 9393732 */
,'KR_YEA_DONATION_TOTALS1' /* Bug 12820464 */
,'KR_YEA_HOU_RENT_DETAILS' /* Bug 9393732 */
,'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
,'KR_YEA_DETAIL_DONATION_INFO'
,'KR_YEA_HOU_EXP_TAX_EXEM_INFO'
,'KR_YEA_DPNT_EDUC_TAX_EXEM_INFO'
,'KR_YEA_TAX_BREAK_INFO'
,'KR_YEA_OVS_TAX_BREAK_INFO'
,'KR_YEA_EMP_EXPENSE_DETAILS' -- Bug 5372366
,'KR_YEA_TAX_GROUP_INFO' -- Bug 7361372
,'KR_YEA_SEP_PEN_DETAILS' /* Bug 9737699 */
,'KR_YEA_PEN_SAVING_DETAILS' /* Bug 9737699 */
,'KR_YEA_HOU_SAVING_DETAILS' /* Bug 9737699 */
,'KR_YEA_LT_STOCK_SAVING_DETAILS') /* Bug 9737699 */
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 info.assignment_yea_info_id,
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 = 'KR_YEA_NON_TAXABLE_EARN_DETAIL';
select info.assignment_yea_info_id,
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,
info.ayi_information31,
info.ayi_information32,
info.ayi_information33
from per_kr_assignment_yea_info info
where assignment_id = p_assignment_id
and target_year = p_target_year
and information_type = 'KR_YEA_PREV_ER_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,
info.ayi_information31
from per_kr_assignment_yea_info info
where assignment_id = p_assignment_id
and target_year = p_target_year
and information_type = 'KR_YEA_TAX_EXEM_INFO';
Select assignment_extra_info_id,
aei_information2,
object_version_number
From per_assignment_extra_info
Where assignment_id = p_assignment_id
And to_char(fnd_date.canonical_to_date(aei_information1), 'yyyy') = p_target_year
And information_type = 'KR_YEA_TAX_EXEM_INFO2';
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
from per_kr_assignment_yea_info info
where assignment_id = p_assignment_id
and target_year = p_target_year
and information_type = 'KR_YEA_TAX_EXEM_INFO2';
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);
for i in csr_delete loop
--
hr_assignment_extra_info_api.delete_assignment_extra_info(
p_validate => false,
p_assignment_extra_info_id => i.assignment_extra_info_id,
p_object_version_number => i.object_version_number);
delete from per_assignment_extra_info
where assignment_id = p_assignment_id
And information_type = 'KR_YEA_FW_TAX_BREAK_INFO';
hr_assignment_extra_info_api.update_assignment_extra_info(
p_validate => false,
p_assignment_extra_info_id => l_assign_extra_info_id,
p_object_version_number => l_obj_ver_number,
p_aei_information_category => 'KR_YEA_TAX_EXEM_INFO2',
p_aei_information1 => rec6.ayi_information1,
p_aei_information2 => l_aei_information2,
p_aei_information3 => rec6.ayi_information3,
p_aei_information4 => rec6.ayi_information4,
p_aei_information5 => rec6.ayi_information5,
p_aei_information6 => rec6.ayi_information6,
p_aei_information7 => rec6.ayi_information7,
p_aei_information8 => rec6.ayi_information8,
p_aei_information9 => rec6.ayi_information9,
p_aei_information10 => rec6.ayi_information10,
p_aei_information11 => rec6.ayi_information11,
p_aei_information12 => rec6.ayi_information12,
p_aei_information13 => null,
p_aei_information14 => null,
p_aei_information15 => null,
p_aei_information16 => null,
p_aei_information17 => null,
p_aei_information18 => null,
p_aei_information19 => null,
p_aei_information20 => null,
p_aei_information21 => null,
p_aei_information22 => null,
p_aei_information23 => null,
p_aei_information24 => null,
p_aei_information25 => null,
p_aei_information26 => null,
p_aei_information27 => null,
p_aei_information28 => null,
p_aei_information29 => null,
p_aei_information30 => null);
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
--
-- Bug 9326153
--
cursor csr_delete
is
select assignment_extra_info_id,object_version_number
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_SP_TAX_EXEM_INFO3' /* Bug 9393732 */
,'KR_YEA_DONATION_TOTALS' /* Bug 9393732 */
,'KR_YEA_DONATION_TOTALS1' /* Bug 12820464 */
,'KR_YEA_HOU_RENT_DETAILS' /* Bug 9393732 */
,'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'
,'KR_YEA_EMP_EXPENSE_DETAILS'
,'KR_YEA_TAX_GROUP_INFO'
,'KR_YEA_NON_TAXABLE_EARN_DETAIL'
,'KR_YEA_PREV_ER_INFO2' /* Bug 9737699 */
,'KR_YEA_TAX_EXEM_INFO2' /* Bug 9737699 */
,'KR_YEA_SEP_PEN_DETAILS' /* Bug 9737699 */
,'KR_YEA_PEN_SAVING_DETAILS' /* Bug 9737699 */
,'KR_YEA_HOU_SAVING_DETAILS' /* Bug 9737699 */
,'KR_YEA_LT_STOCK_SAVING_DETAILS');/* Bug 9737699 */
for i in csr_delete loop
--
hr_assignment_extra_info_api.delete_assignment_extra_info(
p_validate => false,
p_assignment_extra_info_id => i.assignment_extra_info_id,
p_object_version_number => i.object_version_number);
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';
select nvl(ppos.final_process_date,p_eff_date)
from per_assignments_f asg,
per_periods_of_service ppos
where asg.assignment_id = p_assgn_id
and ppos.period_of_service_id = asg.period_of_service_id
and nvl(ppos.final_process_date,p_eff_date) between asg.effective_start_date and asg.effective_end_date;
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);
select count(*)
from PER_KR_ASSIGNMENT_YEA_INFO
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and target_year = p_target_year
and Ayi_information6 = p_ayi_information6;
select count(ayi_information2)
from per_kr_assignment_yea_info
where assignment_id = p_assignment_id
and assignment_yea_info_id <> p_assignment_yea_info_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and ayi_information2 = p_ayi_information2
and ayi_information6 = p_ayi_information6 -- Bug 9213683
and target_year = p_target_year;
select pay_kr_ff_functions_pkg.aged_flag(p_national_identifier,fnd_date.canonical_to_date(p_effective_date))
from dual;
procedure insert_fnd_sessions( p_effective_date in varchar2
)
is
cursor csr_get_session_id is
select session_id
from fnd_sessions
where session_id = userenv('sessionid');
-- Insert a row into fnd_sessions if reqd
open csr_get_session_id;
insert into fnd_sessions(session_id,effective_date)
values (userenv('sessionid'),fnd_date.canonical_to_date(p_effective_date));
end insert_fnd_sessions;
select MEANING
from HR_LOOKUPS
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code
and enabled_flag = 'Y';
procedure insert_don_type_data(p_assignment_id in number,
p_target_year in varchar2)
--
is
--
l_asg_extra_info_id number;
select aei_information1,
aei_information2,
aei_information3,
aei_information4,
aei_information5,
aei_information6,
aei_information7,
aei_information8,
aei_information9,
aei_information10,
aei_information11
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and to_number(to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY')) = to_number(p_target_year);
select assignment_yea_info_id,
object_version_number
from per_kr_assignment_yea_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and ayi_information2 = l_donation_year
and ayi_information8 = l_donation_code
and target_year = p_target_year;
insert_fnd_sessions( p_target_year||'/12/31 00:00:00');
insert into per_kr_assignment_yea_info
(assignment_yea_info_id,
assignment_id,
information_type,
target_year,
ayi_information_category,
ayi_information1,
ayi_information2,
ayi_information3,
ayi_information4,
ayi_information5,
ayi_information6,
ayi_information7,
ayi_information8,
ayi_information9,
ayi_information10,
ayi_information11,
object_version_number)
values
(PER_KR_ASSIGNMENT_YEA_INFO_S.nextval,
p_assignment_id,
'KR_YEA_DONATION_TYPE_DETAIL',
p_target_year,
'KR_YEA_DONATION_TYPE_DETAIL',
i.aei_information1,
i.aei_information2,
i.aei_information3,
i.aei_information4,
i.aei_information5,
i.aei_information6,
i.aei_information7,
i.aei_information8,
i.aei_information9,
i.aei_information10,
i.aei_information11,
1);
update per_kr_assignment_yea_info
set ayi_information4 = i.aei_information4,
ayi_information11 = i.aei_information11,
ayi_information5 = i.aei_information5
where assignment_yea_info_id = l_asg_extra_info_id;
update per_kr_assignment_yea_info
set ayi_information3 = i.aei_information3,
ayi_information4 = i.aei_information4,
ayi_information5 = i.aei_information5,
ayi_information9 = i.aei_information9,
ayi_information11 = i.aei_information11,
ayi_information10 = i.aei_information10
where assignment_yea_info_id = l_asg_extra_info_id;
select assignment_yea_info_id
from per_kr_assignment_yea_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and target_year = p_target_year
and ayi_information2 = p_target_year
and ayi_information8 = l_donation_code;
update per_kr_assignment_yea_info
set ayi_information9 = l_amt,
ayi_information10 = l_amt
where assignment_yea_info_id = l_asgInfoId
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and assignment_id = p_assignment_id;
delete
from per_kr_assignment_yea_info
where assignment_yea_info_id = l_asgInfoId
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and assignment_id = p_assignment_id;
insert into per_kr_assignment_yea_info
(assignment_yea_info_id,
assignment_id,
information_type,
target_year,
ayi_information_category,
ayi_information1,
ayi_information2,
ayi_information3,
ayi_information4,
ayi_information5,
ayi_information6,
ayi_information7,
ayi_information8,
ayi_information9,
ayi_information10,
ayi_information11,
object_version_number)
values
(PER_KR_ASSIGNMENT_YEA_INFO_S.nextval,
p_assignment_id,
'KR_YEA_DONATION_TYPE_DETAIL',
p_target_year,
'KR_YEA_DONATION_TYPE_DETAIL',
fnd_date.date_to_canonical(to_date('31-12-'||p_target_year,'DD-MM-YYYY')),
p_target_year,
'0',
'0',
'0',
null,
null,
l_donation_code,
l_amt,
l_amt,
'0',
1);
procedure update_don_tot_data(
p_assignment_id in number,
p_target_year in number,
p_stat_self in number,
p_stat_spouse in number,
p_stat_child in number,
p_pol_self in number,
p_tax_self in number,
p_tax_spouse in number,
p_tax_child in number,
p_pledt_self in number,
p_pledt_spouse in number,
p_pledt_child in number,
p_esoa_self in number,
p_spec_self in number,
p_spec_spouse in number,
p_spec_child in number,
p_rel_self in number,
p_rel_spouse in number,
p_rel_child in number,
p_oth_self in number,
p_oth_spouse in number,
p_oth_child in number,
p_stat_parent in number,
p_stat_bro_sis in number,
p_tax_parent in number,
p_tax_bro_sis in number,
p_pledt_parent in number,
p_pledt_bro_sis in number,
p_spec_parent in number,
p_spec_bro_sis in number,
p_rel_parent in number,
p_rel_bro_sis in number,
p_oth_parent in number,
p_oth_bro_sis in number)
--
is
--
cursor csr_total_exists is
select assignment_yea_info_id
from per_kr_assignment_yea_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TOTALS'
and target_year = p_target_year;
select assignment_yea_info_id
from per_kr_assignment_yea_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TOTALS1'
and target_year = p_target_year;
update per_kr_assignment_yea_info
set ayi_information2 = p_stat_self,
ayi_information4 = p_stat_spouse,
ayi_information3 = p_stat_child,
ayi_information5 = p_pol_self,
ayi_information8 = p_tax_self,
ayi_information10 = p_tax_spouse,
ayi_information9 = p_tax_child,
ayi_information11 = p_pledt_self,
ayi_information13 = p_pledt_spouse,
ayi_information12 = p_pledt_child,
ayi_information14 = p_spec_self,
ayi_information16 = p_spec_spouse,
ayi_information15 = p_spec_child,
ayi_information17 = p_rel_self,
ayi_information19 = p_rel_spouse,
ayi_information18 = p_rel_child,
ayi_information20 = p_esoa_self,
ayi_information23 = p_oth_self,
ayi_information25 = p_oth_spouse,
ayi_information24 = p_oth_child
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TOTALS'
and target_year = p_target_year;
insert into per_kr_assignment_yea_info
(assignment_yea_info_id,
assignment_id,
information_type,
target_year,
ayi_information_category,
ayi_information1,
ayi_information2,
ayi_information6,
ayi_information8,
ayi_information10,
ayi_information12,
ayi_information16,
ayi_information3,
ayi_information7,
ayi_information9,
ayi_information11,
ayi_information13,
ayi_information17,
object_version_number)
values
(PER_KR_ASSIGNMENT_YEA_INFO_S.nextval,
p_assignment_id,
'KR_YEA_DONATION_TOTALS1',
p_target_year,
'KR_YEA_DONATION_TOTALS1',
fnd_date.date_to_canonical(to_date('31-12-'||p_target_year,'DD-MM-YYYY')),
p_stat_parent,
p_tax_parent,
p_pledt_parent,
p_spec_parent,
p_rel_parent,
p_oth_parent,
p_stat_bro_sis,
p_tax_bro_sis,
p_pledt_bro_sis,
p_spec_bro_sis,
p_rel_bro_sis,
p_oth_bro_sis,
1);
update per_kr_assignment_yea_info
set ayi_information2 = p_stat_parent,
ayi_information6 = p_tax_parent,
ayi_information8 = p_pledt_parent,
ayi_information10 = p_spec_parent,
ayi_information12 = p_rel_parent,
ayi_information16 = p_oth_parent,
ayi_information3 = p_stat_bro_sis,
ayi_information7 = p_tax_bro_sis,
ayi_information9 = p_pledt_bro_sis,
ayi_information11 = p_spec_bro_sis,
ayi_information13 = p_rel_bro_sis,
ayi_information17 = p_oth_bro_sis
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TOTALS1'
and target_year = p_target_year;
delete
from per_kr_assignment_yea_info
where assignment_id = p_assignment_id
and information_type in ('KR_YEA_DONATION_TOTALS','KR_YEA_DONATION_TOTALS1')
and target_year = p_target_year;
insert into per_kr_assignment_yea_info
(assignment_yea_info_id,
assignment_id,
information_type,
target_year,
ayi_information_category,
ayi_information1,
ayi_information2,
ayi_information4,
ayi_information3,
ayi_information5,
ayi_information8,
ayi_information10,
ayi_information9,
ayi_information11,
ayi_information13,
ayi_information12,
ayi_information14,
ayi_information16,
ayi_information15,
ayi_information17,
ayi_information19,
ayi_information18,
ayi_information20,
ayi_information23,
ayi_information25,
ayi_information24,
object_version_number)
values
(PER_KR_ASSIGNMENT_YEA_INFO_S.nextval,
p_assignment_id,
'KR_YEA_DONATION_TOTALS',
p_target_year,
'KR_YEA_DONATION_TOTALS',
fnd_date.date_to_canonical(to_date('31-12-'||p_target_year,'DD-MM-YYYY')),
p_stat_self,
p_stat_spouse,
p_stat_child,
p_pol_self ,
p_tax_self ,
p_tax_spouse,
p_tax_child ,
p_pledt_self,
p_pledt_spouse,
p_pledt_child,
p_spec_self ,
p_spec_spouse,
p_spec_child,
p_rel_self,
p_rel_spouse,
p_rel_child ,
p_esoa_self ,
p_oth_self ,
p_oth_spouse,
p_oth_child,
1);
insert into per_kr_assignment_yea_info
(assignment_yea_info_id,
assignment_id,
information_type,
target_year,
ayi_information_category,
ayi_information1,
ayi_information2,
ayi_information6,
ayi_information8,
ayi_information10,
ayi_information12,
ayi_information16,
ayi_information3,
ayi_information7,
ayi_information9,
ayi_information11,
ayi_information13,
ayi_information17,
object_version_number)
values
(PER_KR_ASSIGNMENT_YEA_INFO_S.nextval,
p_assignment_id,
'KR_YEA_DONATION_TOTALS1',
p_target_year,
'KR_YEA_DONATION_TOTALS1',
fnd_date.date_to_canonical(to_date('31-12-'||p_target_year,'DD-MM-YYYY')),
p_stat_parent,
p_tax_parent,
p_pledt_parent,
p_spec_parent,
p_rel_parent,
p_oth_parent,
p_stat_bro_sis,
p_tax_bro_sis,
p_pledt_bro_sis,
p_spec_bro_sis,
p_rel_bro_sis,
p_oth_bro_sis,
1);