[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
JLin 12-JAN-2005 115.25 4108099 Changed the mode to 'CORRECTION' when it calls update_element_entry
if effective_start_date is same as the session date in maintain_PAYE_tax_info
abhkumar 30-MAR-2005 115.26 4244787 Changed the mode from UPDATE_CHANGE_INSERT to UPDATE if there dosen't exists any future rows
for Tax Information
abhkumar 08-SEP-2005 115.27 4598178 Changed related to tax variation on bonus enhancement.
sclarke 25-FEB-2006 115.28 4704141 Added new procedure to create workbench tax info.
sclarke 10-MAR-2006 115.30 element link for CRP tax information
abhargav 14-Mar-2007 115.31 Renamed parameter p_hecs_sfss_flag to p_help_sfss_flag in procedure maintain_PAYE_tax_info().
vaisriva 26-May-2008 115.32 7042960 2008 Statutory Updates - FTA Claim Changes
|NOTES
+==========================================================================================
*/
type paye_number_table is table of number not null index by binary_integer;
,p_update_warning OUT nocopy BOOLEAN
) IS
type number_table is table of number not null index by binary_integer;
SELECT pet.element_type_id
FROM pay_element_types_f pet
WHERE pet.element_name = 'Tax Information' -- Bug No: 3648796
AND l_session_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND legislation_code = 'AU';
SELECT piv.input_value_id
,piv.name
FROM pay_input_values_f piv
WHERE piv.element_type_id = p_element_type_id
AND l_session_date BETWEEN piv.effective_start_date AND piv.effective_end_date;
SELECT pee.element_entry_id,
object_version_number
FROM pay_element_entries_f pee,
pay_element_entry_values_f pev
WHERE pee.assignment_id = p_assignment_id
AND l_session_date BETWEEN pee.effective_start_date AND pee.effective_end_date
AND pee.element_link_id = p_element_link
AND pev.element_entry_id = pee.element_entry_id
AND l_session_date BETWEEN pev.effective_start_date AND pev.effective_end_date
AND pev.input_value_id = p_inp_val;
is UPDATE_CHANGE_INSERT*/
CURSOR csr_fut_ele_entry(c_element_entry_id NUMBER, c_effective_date DATE) is
SELECT pee.effective_start_date, pee.effective_end_date
FROM pay_element_entries_f pee
WHERE pee.element_entry_id = c_element_entry_id
AND pee.effective_start_date > c_effective_date
ORDER BY pee.effective_start_date;
select scl.segment2
from per_all_assignments_f asg
, hr_soft_coding_keyflex scl
where scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
and asg.effective_start_date <= p_effective_date
and asg.effective_end_date >= p_effective_date
and asg.assignment_id = p_assignment_id;
SELECT decode(eev0.SCREEN_ENTRY_VALUE,'YS','Y','YI','Y','YC','Y','NN','N','YN','Y','Y','Y','N','N',Null) ,
eev1.SCREEN_ENTRY_VALUE ,
DECODE(
eev2.SCREEN_ENTRY_VALUE,
'N', 'N',
'Y', 'Y',
'NF','N',
'NP','N',
'NC','N',
'YF','Y',
'YP','Y',
'YC','Y',
'N'
),
DECODE(
eev2.SCREEN_ENTRY_VALUE,
'Y', 'X',
'N', 'X',
'NF','F',
'NP','P',
'NC','C',
'YF','F',
'YP','P',
'YC','C',
'X'),
decode(eev4.SCREEN_ENTRY_VALUE,'Y','Y','N','N','YY','Y','NY','N',Null) ,
decode(eev4.SCREEN_ENTRY_VALUE,'YY','Y','NY','Y','N') ,
eev5.SCREEN_ENTRY_VALUE ,
decode(decode(eev6.SCREEN_ENTRY_VALUE,'Y','Y','N','N','YY','Y','NY','N','N'),'Y','Y',decode(eev3.SCREEN_ENTRY_VALUE,'Y','Y','N')) ,
eev7.SCREEN_ENTRY_VALUE ,
pee.entry_information1
FROM pay_element_entries_f pee ,
pay_element_entry_values_f eev0 ,
pay_element_entry_values_f eev1 ,
pay_element_entry_values_f eev2 ,
pay_element_entry_values_f eev3 ,
pay_element_entry_values_f eev4 ,
pay_element_entry_values_f eev5 ,
pay_element_entry_values_f eev6 ,
pay_element_entry_values_f eev7 ,
hr_lookups hrl0 ,
hr_lookups hrl1 ,
hr_lookups hrl2 ,
hr_lookups hrl3 ,
hr_lookups hrl4 ,
hr_lookups hrl5
WHERE pee.element_entry_id = p_element_entry_id
AND eev0.INPUT_VALUE_ID = p_au_res_input_value_id
AND eev0.ELEMENT_ENTRY_ID = pee.element_entry_id
AND hrl0.lookup_type (+) = 'AU_AUST_RES_SENR_AUS'
AND hrl0.lookup_code (+) = eev0.SCREEN_ENTRY_VALUE
AND hrl0.enabled_flag (+)= 'Y'
AND eev1.INPUT_VALUE_ID = p_tax_free_input_value_id
AND eev1.ELEMENT_ENTRY_ID = pee.element_entry_id
AND hrl1.lookup_type (+) = 'YES_NO'
AND hrl1.lookup_code (+) = eev1.SCREEN_ENTRY_VALUE
AND hrl1.enabled_flag (+)= 'Y'
AND eev2.INPUT_VALUE_ID = p_fta_input_value_id
AND eev2.ELEMENT_ENTRY_ID = pee.element_entry_id
AND hrl2.lookup_type (+) = 'HR_AU_FTA_PAYMENT_BASIS'
AND hrl2.lookup_code (+) = eev2.SCREEN_ENTRY_VALUE
AND hrl2.enabled_flag (+) = 'Y'
AND eev3.INPUT_VALUE_ID = p_savings_reb_input_value_id
AND eev3.ELEMENT_ENTRY_ID = pee.element_entry_id
AND hrl3.lookup_type(+) = 'YES_NO'
AND hrl3.lookup_code(+) = eev3.SCREEN_ENTRY_VALUE
AND hrl3.enabled_flag (+) = 'Y'
AND eev4.INPUT_VALUE_ID = p_hecs_sfss_input_value_id
AND eev4.ELEMENT_ENTRY_ID = pee.element_entry_id
AND hrl4.lookup_type(+) = 'AU_HECS_SFSS'
AND hrl4.lookup_code (+) = eev4.SCREEN_ENTRY_VALUE
AND hrl4.enabled_flag (+) = 'Y'
AND eev5.INPUT_VALUE_ID = p_dec_date_input_value_id
AND eev5.ELEMENT_ENTRY_ID = pee.element_entry_id
AND eev6.INPUT_VALUE_ID = p_spouse_input_value_id
AND eev6.ELEMENT_ENTRY_ID = pee.element_entry_id
AND hrl5.lookup_type (+) = 'AU_SPOUSE_MLS'
AND hrl5.lookup_code (+) = eev6.SCREEN_ENTRY_VALUE
AND hrl5.enabled_flag (+) = 'Y'
AND eev7.INPUT_VALUE_ID = p_tfn_input_value_id
AND eev7.ELEMENT_ENTRY_ID= pee.element_entry_id
AND p_effective_start_date between eev0.effective_start_date and eev0.effective_end_date
AND p_effective_start_date between eev1.effective_start_date and eev1.effective_end_date
AND p_effective_start_date between eev2.effective_start_date and eev2.effective_end_date
AND p_effective_start_date between eev3.effective_start_date and eev3.effective_end_date
AND p_effective_start_date between eev4.effective_start_date and eev4.effective_end_date
AND p_effective_start_date between eev5.effective_start_date and eev5.effective_end_date
AND p_effective_start_date between eev6.effective_start_date and eev6.effective_end_date
AND p_effective_start_date between eev7.effective_start_date and eev7.effective_end_date
AND p_effective_start_date between pee.effective_start_date and pee.effective_end_date;
/* Bug 7042960: 2008 Statutory Updates - FTA Claim Changes */
CURSOR get_fta_claim_flag(p_fta_claim_flag VARCHAR2) IS
SELECT DECODE(
p_fta_claim_flag,
'N', 'N',
'Y', 'N',
'NF','NF',
'NP','NP',
'NC','NC',
'YF','NF',
'YP','NP',
'YC','NC',
'N'
)
FROM DUAL
WHERE rownum=1;
/* Bug : 3318756 - Replaced to_date with fnd_date.chardate_to_date in the select
statement for p_declaration_signed_date parameter */
CURSOR get_passed_tax_field_values( p_australian_resident_flag VARCHAR2
,p_tax_free_threshold_flag VARCHAR2
,p_fta_claim_flag VARCHAR2
,p_savings_rebate_flag VARCHAR2
,p_hecs_sfss_flag VARCHAR2
,p_declaration_signed_date VARCHAR2
,p_spouse_mls_flag VARCHAR2
,p_tax_file_number VARCHAR2) IS
SELECT decode(p_australian_resident_flag,'YS','Y','YI','Y','YC','Y','NN','N','YN','Y','Y','Y','N','N',Null) ,
p_tax_free_threshold_flag,
DECODE(
p_fta_claim_flag,
'N', 'N',
'Y', 'Y',
'NF','N',
'NP','N',
'NC','N',
'YF','Y',
'YP','Y',
'YC','Y',
'N'
),
DECODE(
p_fta_claim_flag,
'Y', 'X',
'N', 'X',
'NF','F',
'NP','P',
'NC','C',
'YF','F',
'YP','P',
'YC','C',
'X'),
decode(p_hecs_sfss_flag,'Y','Y','N','N','YY','Y','NY','N',Null) ,
decode(p_hecs_sfss_flag,'YY','Y','NY','Y','N') ,
fnd_date.date_to_canonical(fnd_date.chardate_to_date(p_declaration_signed_date)) ,
decode(decode(p_spouse_mls_flag,'Y','Y','N','N','YY','Y','NY','N','N'),'Y','Y',decode(p_savings_rebate_flag,'Y','Y','N')) ,
p_tax_file_number
FROM DUAL
WHERE rownum=1;
and l_mode = 'UPDATE' then
l_mode := 'CORRECTION';
IF (l_mode IN ('CORRECTION','UPDATE','UPDATE_CHANGE_INSERT','UPDATE_OVERRIDE')) THEN
-----------------------------------------------------------------------------
-- Get the element entry of the tax element entry that is to be updated
------------------------------------------------------------------------------
hr_utility.set_location('hr_au_tax_api.maintain_tax_info' ,7);
/*Bug# 4244787 - This piece of code added to change mode from UPDATE_CHANGE_INSERT to UPDATE when there are no
future element entry for Tax Information*/
IF l_mode = 'UPDATE_CHANGE_INSERT' THEN
OPEN csr_fut_ele_entry(l_element_entry_id, l_session_date);
l_mode := 'UPDATE';
/* Bug 7042960: 2008 Statutory Updates - FTA Claim Changes */
IF (l_session_date >= to_date('01/07/2008','DD/MM/YYYY')) THEN
open get_fta_claim_flag(p_fta_claim_flag);
py_element_entry_api.update_element_entry
(p_validate => p_validate
,p_datetrack_update_mode=> l_mode
,p_effective_date => l_session_date
,p_business_group_id => p_business_group_id
,p_element_entry_id => l_element_entry_id
,p_object_version_number=> l_object_version_number
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,p_attribute16 => p_attribute16
,p_attribute17 => p_attribute17
,p_attribute18 => p_attribute18
,p_attribute19 => p_attribute19
,p_attribute20 => p_attribute20
,p_input_value_id1 => l_inp_value_id_table(1)
,p_input_value_id2 => l_inp_value_id_table(2)
,p_input_value_id3 => l_inp_value_id_table(3)
,p_input_value_id4 => l_inp_value_id_table(4)
,p_input_value_id5 => l_inp_value_id_table(5)
,p_input_value_id6 => l_inp_value_id_table(6)
,p_input_value_id7 => l_inp_value_id_table(7)
,p_input_value_id8 => l_inp_value_id_table(8)
,p_input_value_id9 => l_inp_value_id_table(9)
,p_input_value_id10 => l_inp_value_id_table(10)
,p_input_value_id11 => l_inp_value_id_table(11)
,p_input_value_id12 => l_inp_value_id_table(12)
,p_input_value_id13 => l_inp_value_id_table(13)
,p_input_value_id14 => l_inp_value_id_table(14)
,p_entry_value1 => p_australian_resident_flag
,p_entry_value2 => p_tax_free_threshold_flag
,p_entry_value3 => p_rebate_amount
,p_entry_value4 => l_fta_claim_flag /* Bug 7042960 */
,p_entry_value5 => p_savings_rebate_flag
,p_entry_value6 => p_help_sfss_flag /* Bug#5258625 */
,p_entry_value7 => p_declaration_signed_date
,p_entry_value8 => p_medicare_levy_variation_code
,p_entry_value9 => p_spouse_mls_flag
,p_entry_value10 => p_dependent_children
,p_entry_value11 => p_tax_variation_type
,p_entry_value12 => p_tax_variation_amount
,p_entry_value13 => l_upd_tax_scale /* Bug 2145933 */
,p_entry_value14 => p_tax_file_number
,p_entry_information_category => l_entry_information_category
,p_entry_information1 => l_entry_information1
,p_override_user_ent_chk => 'Y'
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_update_warning => p_update_warning);
,p_update_warning OUT nocopy BOOLEAN
) IS
type number_table is table of number not null index by binary_integer;
SELECT pet.element_type_id
FROM pay_element_types_f pet
WHERE pet.element_name = 'Superannuation Guarantee Information' -- Bug No: 3648796
AND l_session_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND legislation_code = 'AU';
SELECT piv.input_value_id
,piv.name
FROM pay_input_values_f piv
WHERE piv.element_type_id = p_element_type_id
AND l_session_date BETWEEN piv.effective_start_date AND piv.effective_end_date;
SELECT pee.element_entry_id
,object_version_number
FROM pay_element_entries_f pee,
pay_element_entry_values_f pev
WHERE pee.assignment_id = p_assignment_id
AND l_session_date BETWEEN pee.effective_start_date AND pee.effective_end_date
AND pee.element_link_id = p_element_link
AND pev.element_entry_id = pee.element_entry_id
AND l_session_date BETWEEN pev.effective_start_date AND pev.effective_end_date
AND pev.input_value_id = p_inp_val;
SELECT nvl(screen_entry_value,'N')
FROM pay_element_entry_values_f
WHERE element_entry_id = p_element_entry_id
AND input_value_id = p_spr_flag_input_value_id
AND p_effective_start_date between effective_start_date and effective_end_date;
for the current where Superannuation is updated */
CURSOR get_tax_info_to_update(p_effective_start_date DATE,
p_assignment_id NUMBER) IS
SELECT pee.element_entry_id,
pee.object_version_number,
pee.effective_start_date,
pee.effective_end_date
FROM pay_element_types_f pet,
pay_element_links_f pel,
pay_element_entries_f pee
WHERE pet.element_name = 'Tax Information'
AND pel.element_type_id = pet.element_type_id
AND pee.element_link_id = pel.element_link_id
AND pee.assignment_id = p_assignment_id
AND pel.effective_start_date between pet.effective_start_date and pet.effective_end_date
AND p_effective_start_date between pee.effective_start_date and pee.effective_end_date;
IF (p_mode IN ('CORRECTION','UPDATE','UPDATE_CHANGE_INSERT','UPDATE_OVERRIDE')) THEN
-----------------------------------------------------------------------------
-- Get the element entry of the tax element entry that is to be updated
------------------------------------------------------------------------------
hr_utility.set_location('hr_au_tax_api.maintain_tax_info' ,7);
OPEN get_tax_info_to_update(p_effective_start_date ,
p_assignment_id );
FETCH get_tax_info_to_update INTO l_paye_element_entry_id,
l_paye_object_version_number,
l_paye_effective_start_date,
l_paye_effective_end_date;
CLOSE get_tax_info_to_update;
py_element_entry_api.update_element_entry
(p_validate => p_validate
,p_datetrack_update_mode => 'CORRECTION'
,p_effective_date => l_tax_effective_date
,p_business_group_id => p_business_group_id
,p_element_entry_id => l_paye_element_entry_id
,p_object_version_number => l_paye_object_version_number
,p_entry_information_category => 'AU_TAX_DEDUCTIONS'
,p_entry_information1 => fnd_date.date_to_canonical(sysdate)
,p_override_user_ent_chk => 'Y'
,p_effective_start_date => l_paye_effective_start_date
,p_effective_end_date => l_paye_effective_end_date
,p_update_warning => p_update_warning);
py_element_entry_api.update_element_entry
(p_validate => p_validate
,p_datetrack_update_mode => p_mode
,p_effective_date => l_session_date
,p_business_group_id => p_business_group_id
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,p_attribute16 => p_attribute16
,p_attribute17 => p_attribute17
,p_attribute18 => p_attribute18
,p_attribute19 => p_attribute19
,p_attribute20 => p_attribute20
,p_input_value_id1 => l_inp_value_id_table(1)
,p_entry_value1 => p_tfn_for_super_flag
,p_override_user_ent_chk => 'Y'
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_update_warning => p_update_warning);
select piv.input_value_id
, piv.name
from pay_input_values_f piv
where piv.element_type_id = p_element_type_id
and p_effective_date between piv.effective_start_date and piv.effective_end_date
order by piv.display_sequence;
select pet.element_type_id
from pay_element_types_f pet
where pet.element_name = g_paye_element -- Bug No: 3648796
and p_effective_date between pet.effective_start_date and pet.effective_end_date
and legislation_code = g_legislation_code;
select legislation_code
from per_business_groups
where business_group_id = p_business_group_id;
l_update_warning boolean;
,p_update_warning => l_update_warning
);
procedure update_adi_tax_crp
(p_validate in boolean default false
,p_assignment_id in number
,p_hire_date in date
,p_business_group_id in number
,p_payroll_id in number
,p_legal_employer in varchar2
,p_tax_file_number in varchar2
,p_tax_free_threshold in varchar2
,p_australian_resident in varchar2
,p_hecs in varchar2
,p_sfss in varchar2
,p_leave_loading in varchar2
,p_basis_of_payment in varchar2
,p_declaration_signed_date in varchar2
,p_medicare_levy_surcharge in varchar2
,p_medicare_levy_exemption in varchar2
,p_medicare_levy_dep_children in varchar2 default null
,p_medicare_levy_spouse in varchar2
,p_tax_variation_type in varchar2
,p_tax_variation_amount in number default null
,p_tax_variation_bonus in varchar2
,p_rebate_amount in number default null
,p_savings_rebate in varchar2
,p_ftb_claim in varchar2
,p_senior_australian in varchar2
,p_effective_date in date default null
) IS
cursor csr_tax_element is
select pet.element_type_id
from pay_element_types_f pet
where pet.element_name = g_paye_element
and p_hire_date between pet.effective_start_date and pet.effective_end_date
and legislation_code = g_legislation_code;
select object_version_number
from per_assignments_f
where assignment_id = p_assignment_id
and business_group_id = p_business_group_id;
select element_entry_id
from pay_element_entries_f
where assignment_id = p_assignment_id
and element_link_id = p_element_link_id;
select element_link_id
, object_version_number
from pay_element_links_f
where element_type_id = p_element_type_id
and business_group_id = p_business_group_id
and (payroll_id = p_payroll_id or link_to_all_payrolls_flag is not null)
and p_hire_date between effective_start_date and effective_end_date;
l_procedure varchar2(100) := g_package||'update_adi_tax_crp';
l_update_warning boolean;
l_spp_delete_warning boolean;
hr_au_assignment_api.update_au_emp_asg
(p_validate => p_validate
,p_effective_date => p_hire_date
,p_datetrack_update_mode => 'CORRECTION' -- hard code this mode since we only use this API for RI
,p_assignment_id => p_assignment_id
,p_object_version_number => l_object_version_number -- out parameter
,p_legal_employer_id => p_legal_employer
,p_lev_lod_flg => p_leave_loading
,p_cagr_grade_def_id => l_cagr_grade_def_id -- out parameter
,p_cagr_concatenated_segments => l_cagr_concatenated_segments -- out parameter
,p_comment_id => l_comment_id -- out parameter
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id -- out parameter
,p_effective_start_date => l_effective_start_date -- out parameter
,p_effective_end_date => l_effective_end_date -- out parameter
,p_concatenated_segments => l_concatenated_segments -- out parameter
,p_no_managers_warning => l_no_managers_warning -- out parameter
,p_other_manager_warning => l_other_manager_warning -- out parameter
);
hr_assignment_api.update_emp_asg_criteria
(p_effective_date => p_hire_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => p_assignment_id
,p_validate => p_validate
,p_payroll_id => p_payroll_id
,p_object_version_number => l_object_version_number -- from when we updated the asg earlier
,p_special_ceiling_step_id => l_special_ceiling_step_id
,p_people_group_id => l_people_group_id
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_group_name => l_group_name
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entries_changed_warning
,p_tax_district_changed_warning => l_tax_district_changed_warning
,p_concatenated_segments => l_concatenated_segments
);
,p_update_warning => l_update_warning
);
end update_adi_tax_crp;