[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_name like 'Overseas Earnings'
and pbt.legislation_code = 'KR'
and pbd.legislation_code = 'KR'
and pdb.legislation_code = 'KR'
and pbt.balance_type_id = pdb.balance_type_id
and pbd.balance_dimension_id = pdb.balance_dimension_id
and pbd.database_item_suffix = '_ASG_YTD';
select to_number(glb.global_value,'99999999999999999999.99999') -- Bug 5726158
from ff_globals_f glb
where glb.global_name = p_glbvar
and p_process_date between glb.effective_start_date and glb.effective_end_date;
procedure insert_donation_details(p_assignment_id in number,
p_effective_date in date,
p_asg_info_id in number,
p_exem_for_target_year in number,
p_carryover_amount in number
)
----------------------------------------------------------------------------
is
--
l_ovn number := null;
select fnd_date.canonical_to_date(aei_information1) Effective_date,
aei_information8 Donation_Code,
to_number(aei_information2) Donated_Year,
to_number(aei_information9) Donated_Amount,
to_number(aei_information3) Exempted_Balance,
to_number(aei_information10) Amt_sub_to_exem,
aei_information12 prev_bus_reg_num,
aei_information13 user_entered_flag,
object_version_number ovn
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and assignment_extra_info_id = p_asg_info_id
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
select assignment_extra_info_id, object_version_number
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and aei_information2 = p_donated_year
and aei_information8 = p_donation_code
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(add_months(p_effective_date,12), 'YYYY');
hr_utility.trace('*** Insert Donation Details ***');
hr_assignment_extra_info_api.update_assignment_extra_info(
p_validate => false,
p_assignment_extra_info_id => p_asg_info_id,
p_object_version_number => l_ovn,
p_aei_information_category => 'KR_YEA_DONATION_TYPE_DETAIL',
p_aei_information1 => fnd_date.date_to_canonical(l_eff_date),
p_aei_information2 => l_donation_year,
p_aei_information3 => l_exem_balance,
p_aei_information4 => p_exem_for_target_year,
p_aei_information5 => l_carryoveramt,
p_aei_information6 => null,
p_aei_information7 => null,
p_aei_information8 => l_donation_code,
p_aei_information9 => l_donated_amt,
p_aei_information10 => l_amt_sub_exem,
p_aei_information11 => l_obsolete_amt,
p_aei_information12 => l_prev_bus_reg_num,
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
);
hr_utility.trace('Update record if already present for this donation type');
hr_assignment_extra_info_api.update_assignment_extra_info(
p_validate => false,
p_assignment_extra_info_id => l_asgInfoId,
p_object_version_number => l_ovn1,
p_aei_information_category => 'KR_YEA_DONATION_TYPE_DETAIL',
p_aei_information1 => fnd_date.date_to_canonical(add_months(l_eff_date,12)),
p_aei_information2 => l_donation_year,
p_aei_information3 => (l_exem_balance + p_exem_for_target_year),
p_aei_information4 => '0',
p_aei_information5 => '0',
p_aei_information6 => null,
p_aei_information7 => null,
p_aei_information8 => l_donation_code,
p_aei_information9 => l_donated_amt,
p_aei_information10 => (l_donated_amt - (l_exem_balance + p_exem_for_target_year)),
p_aei_information11 => '0',
p_aei_information12 => null,
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
);
procedure update_donation_details(p_assignment_id in number,
p_effective_date in date,
p_donation_code in varchar2,
p_exem_for_target_year in number,
p_obsolete_amount in number
)
----------------------------------------------------------------------------
is
--
l_ovn number := null;
select assignment_extra_info_id asg_info_id,
fnd_date.canonical_to_date(aei_information1) effective_date,
to_number(aei_information2) donated_year,
to_number(aei_information9) donated_amount,
to_number(aei_information3) exempted_balance,
to_number(aei_information10) amt_sub_to_exem,
object_version_number ovn
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and aei_information8 = p_donation_code
and aei_information2 = to_char(p_effective_date, 'YYYY')
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
hr_utility.trace('*** Update Donation Details ***');
hr_assignment_extra_info_api.update_assignment_extra_info(
p_validate => false,
p_assignment_extra_info_id => l_asg_info_id,
p_object_version_number => l_ovn,
p_aei_information_category => 'KR_YEA_DONATION_TYPE_DETAIL',
p_aei_information1 => fnd_date.date_to_canonical(l_eff_date),
p_aei_information2 => l_donation_year,
p_aei_information3 => l_exem_balance,
p_aei_information4 => p_exem_for_target_year,
p_aei_information5 => '0',
p_aei_information6 => null,
p_aei_information7 => null,
p_aei_information8 => p_donation_code,
p_aei_information9 => l_donated_amt,
p_aei_information10 => l_amt_sub_exem,
p_aei_information11 => p_obsolete_amount,
p_aei_information12 => null,
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
);
procedure delete_future_details(p_assignment_id in number,
p_effective_date in date)
----------------------------------------------------------------------------
is
--
cursor del_future_details is
select assignment_extra_info_id
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(add_months(p_effective_date,12), 'YYYY')
and to_number(aei_information2) < to_number(to_char(add_months(p_effective_date,12),'YYYY'));
delete
from per_assignment_extra_info
where assignment_extra_info_id = i.assignment_extra_info_id;
select assignment_extra_info_id asg_info_id,
to_number(aei_information2) donated_year,
aei_information8 donation_code,
to_number(aei_information9) donated_amount,
to_number(aei_information4) exempted_amt,
to_number(aei_information5) carry_over_amt,
to_number(aei_information10) amt_sub_to_exem,
to_number(aei_information11) obsolete_amt
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
update_donation_details(p_assignment_id,
p_effective_date,
i.donation_code,
i.exempted_amt,
i.amt_sub_to_exem);
insert_donation_details(p_assignment_id,
p_effective_date,
i.asg_info_id,
i.exempted_amt,
i.amt_sub_to_exem);
-- Also, p_yea_info.educ_exp_tax_exem may still change. Hence, updated value of educ_exp_tax_exem
-- would be added to l_cuml_special_exem later.
--
p_yea_info.educ_exp_tax_exem := p_yea_info.educ_exp_tax_exem + p_yea_info.ee_occupation_educ_exp2005 ;
update_donation_details(p_assignment_id,
p_effective_date,
'20',
l_political2_exem,
p_yea_info.political_donation2 - l_political2_exem);
insert_donation_details(p_assignment_id,
p_effective_date,
p_yea_info.stat_asg_info_id_tbl(i),
l_stat_tax_exem,
p_yea_info.stat_amt_sub_to_exem_tbl(i) - l_stat_tax_exem);
-- Bug 5682450: Updated Political Donation Tax Break
p_yea_info.don_tax_break2004 := (greatest(0, p_yea_info.political_donation1) - greatest(0, p_yea_info.political_donation1 - l_political_tax_brk_lim2004 )) / l_pol_don_exem_rate_factor ;
insert_donation_details(p_assignment_id,
p_effective_date,
p_yea_info.tlaw_asg_info_id_tbl(i),
l_tlaw_tax_exem,
p_yea_info.tlaw_amt_sub_to_exem_tbl(i) - l_tlaw_tax_exem);
insert_donation_details(p_assignment_id,
p_effective_date,
p_yea_info.pledt_asg_info_id_tbl(i),
l_pledt_tax_exem,
p_yea_info.pledt_amt_sub_to_exem_tbl(i) - l_pledt_tax_exem);
update_donation_details(p_assignment_id,
p_effective_date,
'42',
p_yea_info.esoa_don_tax_exem2004,
p_yea_info.esoa_don2004 - p_yea_info.esoa_don_tax_exem2004);
insert_donation_details(p_assignment_id,
p_effective_date,
p_yea_info.spec_rel_asg_info_id_tbl(i),
l_spec_rel_tax_exem,
p_yea_info.spec_rel_amt_sub_to_exem_tbl(i) - l_spec_rel_tax_exem);
insert_donation_details(p_assignment_id,
p_effective_date,
p_yea_info.spec_rel_asg_info_id_tbl(i),
l_spec_rel_tax_exem,
p_yea_info.spec_rel_amt_sub_to_exem_tbl(i) - l_spec_rel_tax_exem);
insert_donation_details(p_assignment_id,
p_effective_date,
p_yea_info.rel_asg_info_id_tbl(i),
l_rel_tax_exem,
p_yea_info.rel_amt_sub_to_exem_tbl(i) - l_rel_tax_exem);
insert_donation_details(p_assignment_id,
p_effective_date,
p_yea_info.spec_asg_info_id_tbl(i),
l_spec_tax_exem,
p_yea_info.spec_amt_sub_to_exem_tbl(i) - l_spec_tax_exem);
insert_donation_details(p_assignment_id,
p_effective_date,
p_yea_info.spec_asg_info_id_tbl(i),
l_spec_tax_exem,
p_yea_info.spec_amt_sub_to_exem_tbl(i) - l_spec_tax_exem);
insert_donation_details(p_assignment_id,
p_effective_date,
p_yea_info.rel_asg_info_id_tbl(i),
l_rel_tax_exem,
p_yea_info.rel_amt_sub_to_exem_tbl(i) - l_rel_tax_exem);
insert_donation_details(p_assignment_id,
p_effective_date,
p_yea_info.spec_rel_asg_info_id_tbl(i),
l_spec_rel_tax_exem,
p_yea_info.spec_rel_amt_sub_to_exem_tbl(i) - l_spec_rel_tax_exem);
insert_donation_details(p_assignment_id,
p_effective_date,
p_yea_info.spec_rel_asg_info_id_tbl(i),
l_spec_rel_tax_exem,
p_yea_info.spec_rel_amt_sub_to_exem_tbl(i) - l_spec_rel_tax_exem);
-- Bug 7615517: Updated Total Credit Card amount for Company Related Expenses
p_yea_info.total_credit_card_exp :=p_yea_info.emp_cre_card_direct_exp2004
+ p_yea_info.dpnt_cre_card_direct_exp2004
+ p_yea_info.giro_tuition_paid_exp2004
+ p_yea_info.cash_receipt_exp2005
- p_yea_info.company_related_exp ;