[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
** 04-jul-2003 asengar 3030621 Added two procedures insert_element_entry
** and update_element_entry.
** 30-JUL-2003 asengar 3030616 Added four functions get_weekly_tax_credit
** get_weekly_std_rate_cut_off,get_monthly_tax_credit
** get_monthly_std_rate_cut_off to be called by
** view pay_ie_paye_details_v.
** 09-FEB-2005 aashokan 4080773 Added a new procedure to create record in new tax record
** if pay frequency is changed.
** 10-Feb-2005 vikgupta 4080773 Modified the proc update_paye_change_freq (included
** P_DATETRACK_UPDATE_MODE)
** 11-Feb-2005 vikgupta 4080773 Modified the proc update_paye_change_freq
** 22-Feb-2005 skhandwa 4080773 Modified the proc update_paye_change_freq
** included check if no current or future record
** exists
** 22-Feb-2005 skhandwa 4080773 Modified the proc update_paye_change_freq
** For Cumulative, set nonapplicable
** credit and cut-off values to null
** 22-Feb-2005 skhandwa 4080773 Modified the proc update_paye_change_freq
** Added global variable for old payroll
** 22-Feb-2005 skhandwa 4080773 Modified the proc update_paye_change_freq
** Changed p_effective_date for Correction cases.
** 23-Feb-2005 skhandwa 4080773 Modified the proc update_paye_change_freq
** Added assignment start date check for Correction cases .
** 20-Apr-2005 alikhar 3227184 Changed cursor c_paye_dtl to use the payroll effective
** date to fetch paye values from pay_ie_paye_details_f.
** 26-Sep-2005 rrajaman 4619038 Added checks for new Tax Basis IE_EXEMPTION.
** 04-Oct-2005 rrajaman 4561012 Added checks for IE_WEEK1_MONTH1.
** 15-Dec-2005 vikgupta 4878630 Modified the update_paye_change_freq proc
** for tax credit upload process.
** 04-Jan-2006 vikgupta 4926302 added info source as IE_ELECTRONCI and asess
** basis 'IE_SEP_TREAT' for Tax Credit upload
** in update_paye_change_freq proc.
** 01-Mar-2006 rbhardwa 5070091 Made changes to accomodate offset payrolls.
** 19-Sep-2006 MGettins 5472781 Added a check to see if legislation
** has been installed, as part
** of the fix for GSI bug 5472781.
** 19-Feb-2007 vikgupta SR 17140460.6, change the parameter passed to
** update_paye_change_freq
** 09-Apr-2007 rbhardwa 5867343 Modified code to include new functions get_paye_tax_basis,
** get_diff_tax_basis and get_ie_exclude_tax_basis.
** 05-May-2008 knadhan 6929566 Replaced p_effective_date with new parameter p_cert_date,
** 05-Dec-2008 rrajaman 7622221 Ireland budget 2009 new formula function
** 11-Dec-2008 rrajaman 7622221 get_age_payroll_period modified to check age as of 31-Dec
** 23-Dec-2008 rrajaman 7665572 Levy dates advanced for Offset Payroll
** 03-Dec-2009 rrajaman 9177545 added get_periods_between function.
** 06-Sep-2010 vijranga 10078301 added get_ie_employer_info function.
** 23-Sep-2010 vijranga 10078301 Removed gscc errors
** 08-Nov-2010 vijranga 10254081 Added get_last_period function
** 22-JUL-2011 rsahai 12779712 Removed vague FND_LOG messages.
** 08-NOV-2011 rsahai 13359423 P2C changes 2012, proc update_ie_paye_details modified
** 23-Nov-2011 smeduri 13359545 added func get_usc_details
-------------------------------------------------------------------------------
*/
g_package varchar2(33) := 'pay_ie_paye.';
g_old_payroll_id per_all_assignments_f.payroll_id%TYPE; --added for update_paye_change_freq
SELECT pev.SCREEN_ENTRY_VALUE
FROM pay_element_entries_f pee, pay_element_links_f pel, pay_element_types_f pet,pay_element_entry_values_f pev,
pay_input_values_f piv
WHERE pee.element_link_id = pel.element_link_id
AND pet.element_type_id = pel.element_type_id
AND pet.element_name = 'IE Social Benefit Option 2'
AND pee.element_entry_id = c_element_entry_id
AND pet.element_type_id = piv.element_type_id
AND piv.legislation_code='IE'
AND piv.name in ('Absence Start Date','Absence End Date')
AND piv.element_type_id = pel.element_type_id
AND piv.input_value_id=pev.input_value_id
AND pev.element_entry_id = c_element_entry_id
ORDER by piv.name desc;
cursor c_paye_tax_basis is select tax_basis
from pay_ie_paye_details_f pipd
,pay_payroll_actions ppa
,per_time_periods ptp
where pipd.assignment_id = p_assignment_id
and ppa.payroll_action_id = p_payroll_action_id
and ppa.effective_date between pipd.effective_start_date and --Bug Fix 3227184
nvl(pipd.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
and pipd.info_source in ('IE_P45','IE_ELECTRONIC','IE_CERT_TAX_CREDITS','IE_NONE_PROVIDED');
SELECT 1
FROM per_all_assignments_f paaf
,per_time_periods ptp
,pay_ie_paye_details_f pipd
WHERE paaf.person_id = ( SELECT distinct person_id FROM per_all_assignments_f WHERE assignment_id = p_assignment_id )
AND paaf.assignment_id <> p_assignment_id
AND pipd.assignment_id(+) = paaf.assignment_id
AND nvl(pipd.tax_basis,'X') <> 'IE_EXCLUDE'
AND p_date_earned BETWEEN ptp.start_date and ptp.end_date
AND ptp.payroll_id = p_payroll_id
AND paaf.effective_start_date <= ptp.end_date
AND paaf.effective_end_date >= ptp.start_date;
SELECT 1
FROM per_all_assignments_f paaf
,per_time_periods ptp
,pay_ie_paye_details_f pipd
WHERE paaf.person_id = ( SELECT distinct person_id FROM per_all_assignments_f WHERE assignment_id = p_assignment_id )
AND paaf.assignment_id <> p_assignment_id
AND pipd.assignment_id(+) = paaf.assignment_id
AND nvl(pipd.tax_basis,'X') = 'IE_EXCLUDE'
AND p_date_earned BETWEEN ptp.start_date and ptp.end_date
AND ptp.payroll_id = p_payroll_id
AND paaf.effective_start_date <= ptp.end_date
AND paaf.effective_end_date >= ptp.start_date;
cursor c_paye_dtl is select ppa.payroll_id
,ppa.date_earned
,info_source
,tax_basis
,nvl(weekly_tax_credit,0)
,nvl(monthly_tax_credit,0)
,nvl(weekly_std_rate_cut_off,0)
,nvl(monthly_std_rate_cut_off,0)
,effective_start_date
,nvl(pipd.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
/*changes for SOE form requirements*/
,pipd.tax_assess_basis
,nvl(pipd.certificate_issue_date,to_date('01-01-0001','DD-MM-YYYY'))
,ptp.period_type
from pay_ie_paye_details_f pipd
,pay_payroll_actions ppa
,per_time_periods ptp
where pipd.assignment_id = p_assignment_id
and ppa.payroll_action_id = p_payroll_action_id
-- and ppa.date_earned between pipd.effective_start_date and
and ppa.effective_date between pipd.effective_start_date and --Bug Fix 3227184
nvl(pipd.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
and pipd.info_source in ('IE_P45','IE_ELECTRONIC','IE_CERT_TAX_CREDITS','IE_NONE_PROVIDED')
and ptp.payroll_id = ppa.payroll_id
and ppa.date_earned between ptp.start_date and ptp.end_date;
/* cursor c_soc_ben(c_payroll_id number,c_date_earned date) is select calculation_option
,nvl(reduced_tax_credit,0)
,nvl(reduced_standard_cutoff,0)
,nvl(benefit_amount,0)
from pay_ie_social_benefits_f psb,
per_time_periods ptp
where psb.absence_start_date between ptp.start_date and ptp.end_date
and ptp.payroll_id = c_payroll_id
and psb.assignment_id = p_assignment_id
and calculation_option not in('IE_OPTION0','IE_OPTION1')
and c_date_earned between ptp.start_date and ptp.end_date
order by psb.effective_start_date desc; */
select NVL(SUM(TO_NUMBER(SCREEN_ENTRY_VALUE)),0)
from
PAY_INPUT_VALUES_F INPVAL,
PAY_ELEMENT_TYPES_F TYPE,
PAY_ELEMENT_LINKS_F LINK,
PAY_ELEMENT_ENTRY_VALUES_F VALUE,
PAY_ELEMENT_ENTRIES_F ENTRY,
PER_TIME_PERIODS PTP,
PAY_PAYROLL_ACTIONS PACT
-- ,FND_SESSIONS SESH
WHERE
PACT.PAYROLL_ACTION_ID = P_PAYROLL_ACTION_ID AND
--PTP.TIME_PERIOD_ID = PACT.TIME_PERIOD_ID AND -- Bug 5070091 Offset payroll change
PACT.PAYROLL_ID = PTP.PAYROLL_ID AND
PACT.DATE_EARNED BETWEEN PTP.START_DATE AND PTP.END_DATE AND
--SESH.SESSION_ID = USERENV ('sessionid') AND
TYPE.ELEMENT_NAME = 'IE Social Benefit Option 2' AND
-- SESH.EFFECTIVE_DATE BETWEEN TYPE.EFFECTIVE_START_DATE AND TYPE.EFFECTIVE_END_DATE AND
PACT.EFFECTIVE_DATE BETWEEN TYPE.EFFECTIVE_START_DATE AND TYPE.EFFECTIVE_END_DATE AND
TYPE.ELEMENT_TYPE_ID = LINK.ELEMENT_TYPE_ID AND
-- SESH.EFFECTIVE_DATE BETWEEN LINK.EFFECTIVE_START_DATE AND LINK.EFFECTIVE_END_DATE AND
PACT.EFFECTIVE_DATE BETWEEN LINK.EFFECTIVE_START_DATE AND LINK.EFFECTIVE_END_DATE AND
ENTRY.ELEMENT_LINK_ID = LINK.ELEMENT_LINK_ID AND
ENTRY.ASSIGNMENT_ID = P_ASSIGNMENT_ID AND
ENTRY.EFFECTIVE_START_DATE <= PTP.END_DATE AND
ENTRY.EFFECTIVE_END_DATE >= PTP.START_DATE AND
VALUE.ELEMENT_ENTRY_ID = ENTRY.ELEMENT_ENTRY_ID AND
VALUE.EFFECTIVE_START_DATE = ENTRY.EFFECTIVE_START_DATE AND
VALUE.EFFECTIVE_END_DATE = ENTRY.EFFECTIVE_END_DATE AND
INPVAL.INPUT_VALUE_ID = VALUE.INPUT_VALUE_ID AND
INPVAL.NAME = 'Taxable Benefit Amount' AND
-- SESH.EFFECTIVE_DATE BETWEEN INPVAL.EFFECTIVE_START_DATE AND INPVAL.EFFECTIVE_END_DATE;
cursor c_usc_dtl is select tax_basis
,nvl(usc_tax_basis,'0')
,nvl(usc_rate_1,0)
,nvl(usc_yrly_cutoff_1 ,0)
,nvl(usc_mthly_cutoff_1 ,0)
,nvl(usc_wkly_cutoff_1 ,0)
,nvl(usc_rate_2,0)
,nvl(usc_yrly_cutoff_2 ,0)
,nvl(usc_mthly_cutoff_2 ,0)
,nvl(usc_wkly_cutoff_2,0)
,nvl(usc_rate_3,0)
,nvl(usc_yrly_cutoff_3 ,0)
,nvl(usc_mthly_cutoff_3 ,0)
,nvl(usc_wkly_cutoff_3 ,0)
,nvl(usc_rate_4,0)
,nvl(usc_yrly_cutoff_4 ,0)
,nvl(usc_mthly_cutoff_4 ,0)
,nvl(usc_wkly_cutoff_4 ,0)
,nvl(usc_rate_5 ,0)
from pay_ie_paye_details_f pipd
,pay_payroll_actions ppa
where pipd.assignment_id = p_assignment_id
and ppa.payroll_action_id = p_payroll_action_id
and ppa.effective_date between pipd.effective_start_date and
nvl(pipd.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
and pipd.info_source in ('IE_P45','IE_ELECTRONIC','IE_CERT_TAX_CREDITS','IE_NONE_PROVIDED');
cursor c_payroll_details is select ptp.period_num
,ptp.period_type
from per_time_periods ptp,
pay_all_payrolls pap,
pay_payroll_actions ppa
where pap.payroll_id = ptp.payroll_id
and pap.payroll_id=p_payroll_id
and ppa.payroll_id=pap.payroll_id
and ppa.payroll_action_id=p_payroll_action_id
and ppa.date_earned between ptp.start_date and ptp.end_date;
SELECT number_per_fiscal_year
FROM per_time_period_types
WHERE period_type =p_period_type;
SELECT nvl(pap.national_identifier, 'X')
FROM per_all_people_f pap
,per_all_assignments_f paa
,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = p_payroll_action_id
and paa.assignment_id = p_assignment_id
and ppa.effective_date between paa.effective_start_date and paa.effective_end_date
and paa.person_id = pap.person_id
and ppa.effective_date between pap.effective_start_date and pap.effective_end_date;
SELECT nvl(pipd.tax_basis, 'X')
from pay_ie_paye_details_f pipd,
pay_payroll_actions ppa
WHERE ppa.payroll_action_id = p_payroll_action_id
and pipd.assignment_id = p_assignment_id
and ppa.effective_date between pipd.effective_start_date and pipd.effective_end_date;
select 'TRUE'
from per_all_assignments_f asg,
per_work_incidents pwi,
hr_lookups hl
where p_date_earned between asg.effective_start_date
and asg.effective_end_date and
p_assignment_id = asg.assignment_id
and pwi.PERSON_ID = asg.PERSON_ID
and hl.lookup_type = 'INCIDENT_TYPE'
and pwi.INCIDENT_TYPE = hl.lookup_code
and hl.meaning = p_reference ;
procedure insert_element_entry
(p_element_entry_id in number
)is
l_procedure_name varchar2(61) := 'hr_ie_element_entry_hook.insert_element_name' ;
end insert_element_entry ;
procedure update_element_entry
( p_element_entry_id in number
) is
l_procedure_name varchar2(61) := 'hr_ie_element_entry_hook.update_element_name' ;
end update_element_entry ;
SELECT global_value
FROM ff_globals_f,fnd_sessions ses
WHERE global_name = l_name
AND ses.session_id = userenv('SESSIONID')
AND ses.effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT pp.period_type
FROM pay_all_payrolls_f pp, per_all_assignments_f pa,fnd_sessions ses
WHERE pa.assignment_id = p_assignment_id
AND ses.session_id = userenv('SESSIONID')
AND ses.effective_date BETWEEN pa.effective_start_date AND pa.effective_end_date
AND pp.payroll_id = pa.payroll_id
AND ses.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
SELECT nvl(pp.monthly_std_rate_cut_off,0)
FROM pay_ie_paye_details_f pp,fnd_sessions ses
WHERE pp.assignment_id=p_assignment_id
AND ses.session_id = userenv('SESSIONID')
AND ses.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
SELECT global_value
FROM ff_globals_f,fnd_sessions ses
WHERE global_name = l_name
AND ses.session_id = userenv('SESSIONID')
AND ses.effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT pp.period_type
FROM pay_all_payrolls_f pp, per_all_assignments_f pa,fnd_sessions ses
WHERE pa.assignment_id = p_assignment_id
AND ses.session_id = userenv('SESSIONID')
AND ses.effective_date BETWEEN pa.effective_start_date AND pa.effective_end_date
AND pp.payroll_id = pa.payroll_id
AND ses.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
SELECT nvl(pp.monthly_tax_credit,0)
FROM pay_ie_paye_details_f pp,fnd_sessions ses
WHERE pp.assignment_id=p_assignment_id
AND ses.session_id = userenv('SESSIONID')
AND ses.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
SELECT global_value
FROM ff_globals_f,fnd_sessions ses
WHERE global_name = l_name
AND ses.session_id = userenv('SESSIONID')
AND ses.effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT pp.period_type
FROM pay_all_payrolls_f pp, per_all_assignments_f pa,fnd_sessions ses
WHERE pa.assignment_id = p_assignment_id
AND ses.session_id = userenv('SESSIONID')
AND ses.effective_date BETWEEN pa.effective_start_date AND pa.effective_end_date
AND pp.payroll_id = pa.payroll_id
AND ses.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
SELECT nvl(pp.weekly_std_rate_cut_off,0)
FROM pay_ie_paye_details_f pp,fnd_sessions ses
WHERE pp.assignment_id=p_assignment_id
AND ses.session_id = userenv('SESSIONID')
AND ses.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
SELECT global_value
FROM ff_globals_f,fnd_sessions ses
WHERE global_name = l_name
AND ses.session_id = userenv('SESSIONID')
AND ses.effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT pp.period_type
FROM pay_all_payrolls_f pp, per_all_assignments_f pa,fnd_sessions ses
WHERE pa.assignment_id = p_assignment_id
AND ses.session_id = userenv('SESSIONID')
AND ses.effective_date BETWEEN pa.effective_start_date AND pa.effective_end_date
AND pp.payroll_id = pa.payroll_id
AND ses.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
SELECT nvl(pp.weekly_tax_credit,0)
FROM pay_ie_paye_details_f pp,fnd_sessions ses
WHERE pp.assignment_id=p_assignment_id
AND ses.session_id = userenv('SESSIONID')
AND ses.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
PROCEDURE update_paye_change_freq(p_assignment_id number
,p_effective_date date
,p_payroll_id number
,P_DATETRACK_UPDATE_MODE VARCHAR2
,p_tax_upload_flag varchar2 default 'X'
,p_tax_basis varchar2 default null
,p_cert_start_date date default null -- 17140460.6
,p_cert_end_date date default null
,p_weekly_tax_credit number default null
,p_monthly_tax_credit number default null
,p_weekly_std_rate_cut_off number default null
,p_monthly_std_rate_cut_off number default null
,p_tax_deducted_to_date number default null
,p_pay_to_date number default null
,p_cert_date date
--13359423
,p_yrly_tax_cred in number default null
,p_yrly_tax_rate_1 in number default null
,p_yrly_tax_rate_2 in number default null
,p_mthly_tax_rate_2 in number default null
,p_wkly_tax_rate_2 in number default null
,p_tax_rate_3 in number default null
,p_yrly_tax_rate_3 in number default null
,p_mthly_tax_rate_3 in number default null
,p_wkly_tax_rate_3 in number default null
,p_tax_rate_4 in number default null
,p_yrly_tax_rate_4 in number default null
,p_mthly_tax_rate_4 in number default null
,p_wkly_tax_rate_4 in number default null
,p_tax_rate_5 in number default null
,p_in_exempt_usc in varchar2 default null
,p_total_usc_pay_todate in number default null
,p_total_usc_tax_todate in number default null
,p_usc_rate_1 in number default null
,p_usc_yrly_cutoff_1 in number default null
,p_usc_mthly_cutoff_1 in number default null
,p_usc_wkly_cutoff_1 in number default null
,p_usc_rate_2 in number default null
,p_usc_yrly_cutoff_2 in number default null
,p_usc_mthly_cutoff_2 in number default null
,p_usc_wkly_cutoff_2 in number default null
,p_usc_rate_3 in number default null
,p_usc_yrly_cutoff_3 in number default null
,p_usc_mthly_cutoff_3 in number default null
,p_usc_wkly_cutoff_3 in number default null
,p_usc_rate_4 in number default null
,p_usc_yrly_cutoff_4 in number default null
,p_usc_mthly_cutoff_4 in number default null
,p_usc_wkly_cutoff_4 in number default null
,p_usc_rate_5 in number default null
,p_usc_tax_basis in varchar2 default null
,p_usc_info_source in varchar2 default null
--13359423
) is --4878630
Cursor c_effective_paye is select *
from pay_ie_paye_details_f
where p_effective_date between effective_start_date and effective_end_date
and assignment_id = p_assignment_id
order by effective_start_date asc;
cursor c_future_paye(p_paye_details_id number) is select *
from pay_ie_paye_details_f
where p_effective_date < effective_start_date
and assignment_id = p_assignment_id
and ((paye_details_id <> p_paye_details_id and p_paye_details_id is not null) or p_paye_details_id is null )
order by effective_start_date asc;
select *
from pay_ie_paye_details_f
where ((p_date < effective_start_date and trunc(p_date,'Y') = trunc(effective_start_date,'Y') and p_paye_id is null)
or (paye_details_id <> p_paye_id and p_paye_id is not null and p_date < effective_start_date))
and assignment_id = p_assignment_id
order by effective_start_date desc;
SELECT payroll_id ,effective_start_date
FROM per_all_assignments_f paa
WHERE paa.assignment_id=p_assignment_id
AND p_effective_date between paa.effective_start_date
and paa.effective_end_date;
SELECT global_value
FROM ff_globals_f
WHERE global_name = l_name
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT 1
FROM pay_all_payrolls_f pp
WHERE pp.payroll_id = p_payroll_id
AND p_effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date
AND period_type IN ('Calendar Month', 'Quarter', 'Bi-Month', 'Semi-Month', 'Semi-Year', 'Year');
p_update_mode varchar2(20);
hr_utility.set_location('In update_paye_change_freq',840);
-- delete all future records ie diff paye_details_id
open c_future_paye(c_effective_paye_fetch.paye_details_id);
pay_ie_paye_api.delete_ie_paye_details
(p_validate => FALSE
,p_effective_date => c_future_paye_fetch.effective_start_date
,p_datetrack_delete_mode => 'ZAP'
,p_paye_details_id => c_future_paye_fetch.paye_details_id
,p_object_version_number => c_future_paye_fetch.object_version_number
,p_effective_start_date => l_start_date
,p_effective_end_date => l_end_date
);
pay_ie_paye_api.delete_ie_paye_details
(p_validate => FALSE
,p_effective_date => c_effective_paye_fetch.effective_start_date
,p_datetrack_delete_mode => 'FUTURE_CHANGE'
,p_paye_details_id => c_effective_paye_fetch.paye_details_id
,p_object_version_number => l_api_ovn
,p_effective_start_date => l_start_date
,p_effective_end_date => l_end_date
);
--if P_DATETRACK_UPDATE_MODE = 'CORRECTION' then
pay_ie_paye_api.update_ie_paye_details
(p_validate => FALSE
,p_effective_date => p_effective_date
,p_datetrack_update_mode => 'CORRECTION'
,p_paye_details_id => c_effective_paye_fetch.paye_details_id
,p_info_source => decode_value_char(p_tax_upload_flag ='X',c_effective_paye_fetch.info_source,'IE_ELECTRONIC')
,p_tax_basis => decode_value_char(p_tax_upload_flag ='X',c_effective_paye_fetch.tax_basis,p_tax_basis) -- tax credit upload changes
,p_certificate_start_date => decode_value_date(p_tax_upload_flag='X',c_effective_paye_fetch.certificate_start_date,p_cert_start_date) -- tax credit upload changes 17140460.6
,p_tax_assess_basis => decode_value_char(p_tax_upload_flag ='X',c_effective_paye_fetch.tax_assess_basis,'IE_SEP_TREAT')
,p_certificate_issue_date => decode_value_date(p_tax_upload_flag='X',c_effective_paye_fetch.certificate_issue_date,p_cert_date) -- Bug 6929566 p_effective_date) -- tax credit upload changes
,p_certificate_end_date => decode_value_date(p_tax_upload_flag='X',c_effective_paye_fetch.certificate_end_date,p_cert_end_date) -- tax credit upload changes
,p_weekly_tax_credit => l_weekly_tax_credit
,p_weekly_std_rate_cut_off => l_weekly_std_rate_cut_off
,p_monthly_tax_credit => l_monthly_tax_credit
,p_monthly_std_rate_cut_off => l_monthly_std_rate_cut_off
,p_tax_deducted_to_date => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,p_tax_deducted_to_date) -- tax credit upload change
,p_pay_to_date => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,p_pay_to_date) -- tax credit upload change
,p_disability_benefit => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,null) -- tax credit upload change
,p_lump_sum_payment => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,null) -- tax credit upload change
,p_object_version_number => l_api_ovn
,p_effective_start_date => l_start_date
,p_effective_end_date => l_end_date
--13359423
,p_yrly_tax_cred => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.yrly_tax_cred,p_yrly_tax_cred)
,p_yrly_tax_rate_1 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.yrly_tax_rate_1,p_yrly_tax_rate_1)
,p_yrly_tax_rate_2 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.yrly_tax_rate_2,p_yrly_tax_rate_2)
,p_mthly_tax_rate_2 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.mthly_tax_rate_2,p_mthly_tax_rate_2)
,p_wkly_tax_rate_2 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.wkly_tax_rate_2,p_wkly_tax_rate_2)
,p_tax_rate_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.tax_rate_3,p_tax_rate_3)
,p_yrly_tax_rate_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.yrly_tax_rate_3,p_yrly_tax_rate_3)
,p_mthly_tax_rate_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.mthly_tax_rate_3,p_mthly_tax_rate_3)
,p_wkly_tax_rate_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.wkly_tax_rate_3,p_wkly_tax_rate_3)
,p_tax_rate_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.tax_rate_4,p_tax_rate_4)
,p_yrly_tax_rate_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.yrly_tax_rate_4,p_yrly_tax_rate_4)
,p_mthly_tax_rate_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.mthly_tax_rate_4,p_mthly_tax_rate_4)
,p_wkly_tax_rate_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.wkly_tax_rate_4,p_wkly_tax_rate_4)
,p_tax_rate_5 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.tax_rate_5,p_tax_rate_5)
,p_in_exempt_usc => decode_value_char(p_tax_upload_flag='X',c_effective_paye_fetch.in_exempt_usc,p_in_exempt_usc)
,p_total_usc_pay_todate => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,p_total_usc_pay_todate)
,p_total_usc_tax_todate => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,p_total_usc_tax_todate)
,p_usc_rate_1 => decode_value_char(p_tax_upload_flag='X',c_effective_paye_fetch.usc_rate_1,p_usc_rate_1)
,p_usc_yrly_cutoff_1 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_yrly_cutoff_1,p_usc_yrly_cutoff_1)
,p_usc_mthly_cutoff_1 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_mthly_cutoff_1,p_usc_mthly_cutoff_1)
,p_usc_wkly_cutoff_1 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_wkly_cutoff_1,p_usc_wkly_cutoff_1)
,p_usc_rate_2 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_rate_2,p_usc_rate_2)
,p_usc_yrly_cutoff_2 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_yrly_cutoff_2,p_usc_yrly_cutoff_2)
,p_usc_mthly_cutoff_2 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_mthly_cutoff_2,p_usc_mthly_cutoff_2)
,p_usc_wkly_cutoff_2 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_wkly_cutoff_2,p_usc_wkly_cutoff_2)
,p_usc_rate_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_rate_3,p_usc_rate_3)
,p_usc_yrly_cutoff_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_yrly_cutoff_3,p_usc_yrly_cutoff_3)
,p_usc_mthly_cutoff_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_mthly_cutoff_3,p_usc_mthly_cutoff_3)
,p_usc_wkly_cutoff_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_wkly_cutoff_3,p_usc_wkly_cutoff_3)
,p_usc_rate_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_rate_4,p_usc_rate_4)
,p_usc_yrly_cutoff_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_yrly_cutoff_4,p_usc_yrly_cutoff_4)
,p_usc_mthly_cutoff_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_mthly_cutoff_4,p_usc_mthly_cutoff_4)
,p_usc_wkly_cutoff_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_wkly_cutoff_4,p_usc_wkly_cutoff_4)
,p_usc_rate_5 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_rate_5,p_usc_rate_5)
,p_usc_tax_basis => decode_value_char(p_tax_upload_flag='X',c_effective_paye_fetch.usc_tax_basis,p_usc_tax_basis)
,p_usc_info_source => decode_value_char(p_tax_upload_flag='X',c_effective_paye_fetch.usc_info_source,p_usc_info_source)
--13359423
);
-- should always be update with new credits and cutoffs
if P_DATETRACK_UPDATE_MODE = 'CORRECTION' then
-- check with asg start date
hr_utility.set_location('Correction and <> TU',865);
pay_ie_paye_api.update_ie_paye_details
(p_validate => FALSE
,p_effective_date => p_effective_date
,p_datetrack_update_mode => 'CORRECTION'
,p_paye_details_id => c_effective_paye_fetch.PAYE_DETAILs_ID
,p_info_source => c_effective_paye_fetch.info_source
,p_tax_basis => c_effective_paye_fetch.tax_basis
,p_certificate_start_date => c_effective_paye_fetch.certificate_start_date
,p_tax_assess_basis => c_effective_paye_fetch.tax_assess_basis
,p_certificate_issue_date => c_effective_paye_fetch.certificate_issue_date
,p_certificate_end_date => c_effective_paye_fetch.certificate_end_date
,p_weekly_tax_credit => l_weekly_tax_credit
,p_weekly_std_rate_cut_off => l_weekly_std_rate_cut_off
,p_monthly_tax_credit => l_monthly_tax_credit
,p_monthly_std_rate_cut_off => l_monthly_std_rate_cut_off
,p_tax_deducted_to_date => hr_api.g_number
,p_pay_to_date => hr_api.g_number
,p_disability_benefit => hr_api.g_number
,p_lump_sum_payment => hr_api.g_number
,p_object_version_number => l_api_ovn
,p_effective_start_date => l_start_date
,p_effective_end_date => l_end_date
--13359423
,p_yrly_tax_cred => c_effective_paye_fetch.yrly_tax_cred
,p_yrly_tax_rate_1 => c_effective_paye_fetch.yrly_tax_rate_1
,p_yrly_tax_rate_2 => c_effective_paye_fetch.yrly_tax_rate_2
,p_mthly_tax_rate_2 => c_effective_paye_fetch.mthly_tax_rate_2
,p_wkly_tax_rate_2 => c_effective_paye_fetch.wkly_tax_rate_2
,p_tax_rate_3 => c_effective_paye_fetch.tax_rate_3
,p_yrly_tax_rate_3 => c_effective_paye_fetch.yrly_tax_rate_3
,p_mthly_tax_rate_3 => c_effective_paye_fetch.mthly_tax_rate_3
,p_wkly_tax_rate_3 => c_effective_paye_fetch.wkly_tax_rate_3
,p_tax_rate_4 => c_effective_paye_fetch.tax_rate_4
,p_yrly_tax_rate_4 => c_effective_paye_fetch.yrly_tax_rate_4
,p_mthly_tax_rate_4 => c_effective_paye_fetch.mthly_tax_rate_4
,p_wkly_tax_rate_4 => c_effective_paye_fetch.wkly_tax_rate_4
,p_tax_rate_5 => c_effective_paye_fetch.tax_rate_5
,p_in_exempt_usc => c_effective_paye_fetch.in_exempt_usc
,p_total_usc_pay_todate => hr_api.g_number
,p_total_usc_tax_todate => hr_api.g_number
,p_usc_rate_1 => c_effective_paye_fetch.usc_rate_1
,p_usc_yrly_cutoff_1 => c_effective_paye_fetch.usc_yrly_cutoff_1
,p_usc_mthly_cutoff_1 => c_effective_paye_fetch.usc_mthly_cutoff_1
,p_usc_wkly_cutoff_1 => c_effective_paye_fetch.usc_wkly_cutoff_1
,p_usc_rate_2 => c_effective_paye_fetch.usc_rate_2
,p_usc_yrly_cutoff_2 => c_effective_paye_fetch.usc_yrly_cutoff_2
,p_usc_mthly_cutoff_2 => c_effective_paye_fetch.usc_mthly_cutoff_2
,p_usc_wkly_cutoff_2 => c_effective_paye_fetch.usc_wkly_cutoff_2
,p_usc_rate_3 => c_effective_paye_fetch.usc_rate_3
,p_usc_yrly_cutoff_3 => c_effective_paye_fetch.usc_yrly_cutoff_3
,p_usc_mthly_cutoff_3 => c_effective_paye_fetch.usc_mthly_cutoff_3
,p_usc_wkly_cutoff_3 => c_effective_paye_fetch.usc_wkly_cutoff_3
,p_usc_rate_4 => c_effective_paye_fetch.usc_rate_4
,p_usc_yrly_cutoff_4 => c_effective_paye_fetch.usc_yrly_cutoff_4
,p_usc_mthly_cutoff_4 => c_effective_paye_fetch.usc_mthly_cutoff_4
,p_usc_wkly_cutoff_4 => c_effective_paye_fetch.usc_wkly_cutoff_4
,p_usc_rate_5 => c_effective_paye_fetch.usc_rate_5
,p_usc_tax_basis => c_effective_paye_fetch.usc_tax_basis
,p_usc_info_source => c_effective_paye_fetch.usc_info_source
--13359423
);
else -- asg start date > paye start date then update using asg start date
hr_utility.set_location('Correction and <> TU',865);
pay_ie_paye_api.update_ie_paye_details
(p_validate => FALSE
,p_effective_date => l_asg_effective_start_date
,p_datetrack_update_mode => 'UPDATE'
,p_paye_details_id => c_effective_paye_fetch.PAYE_DETAILs_ID
,p_info_source => c_effective_paye_fetch.info_source
,p_tax_basis => c_effective_paye_fetch.tax_basis
,p_certificate_start_date => c_effective_paye_fetch.certificate_start_date
,p_tax_assess_basis => c_effective_paye_fetch.tax_assess_basis
,p_certificate_issue_date => c_effective_paye_fetch.certificate_issue_date
,p_certificate_end_date => c_effective_paye_fetch.certificate_end_date
,p_weekly_tax_credit => l_weekly_tax_credit
,p_weekly_std_rate_cut_off => l_weekly_std_rate_cut_off
,p_monthly_tax_credit => l_monthly_tax_credit
,p_monthly_std_rate_cut_off => l_monthly_std_rate_cut_off
,p_tax_deducted_to_date => hr_api.g_number
,p_pay_to_date => hr_api.g_number
,p_disability_benefit => hr_api.g_number
,p_lump_sum_payment => hr_api.g_number
,p_object_version_number => l_api_ovn
,p_effective_start_date => l_start_date
,p_effective_end_date => l_end_date
--13359423
,p_yrly_tax_cred => c_effective_paye_fetch.yrly_tax_cred
,p_yrly_tax_rate_1 => c_effective_paye_fetch.yrly_tax_rate_1
,p_yrly_tax_rate_2 => c_effective_paye_fetch.yrly_tax_rate_2
,p_mthly_tax_rate_2 => c_effective_paye_fetch.mthly_tax_rate_2
,p_wkly_tax_rate_2 => c_effective_paye_fetch.wkly_tax_rate_2
,p_tax_rate_3 => c_effective_paye_fetch.tax_rate_3
,p_yrly_tax_rate_3 => c_effective_paye_fetch.yrly_tax_rate_3
,p_mthly_tax_rate_3 => c_effective_paye_fetch.mthly_tax_rate_3
,p_wkly_tax_rate_3 => c_effective_paye_fetch.wkly_tax_rate_3
,p_tax_rate_4 => c_effective_paye_fetch.tax_rate_4
,p_yrly_tax_rate_4 => c_effective_paye_fetch.yrly_tax_rate_4
,p_mthly_tax_rate_4 => c_effective_paye_fetch.mthly_tax_rate_4
,p_wkly_tax_rate_4 => c_effective_paye_fetch.wkly_tax_rate_4
,p_tax_rate_5 => c_effective_paye_fetch.tax_rate_5
,p_in_exempt_usc => c_effective_paye_fetch.in_exempt_usc
,p_total_usc_pay_todate => hr_api.g_number
,p_total_usc_tax_todate => hr_api.g_number
,p_usc_rate_1 => c_effective_paye_fetch.usc_rate_1
,p_usc_yrly_cutoff_1 => c_effective_paye_fetch.usc_yrly_cutoff_1
,p_usc_mthly_cutoff_1 => c_effective_paye_fetch.usc_mthly_cutoff_1
,p_usc_wkly_cutoff_1 => c_effective_paye_fetch.usc_wkly_cutoff_1
,p_usc_rate_2 => c_effective_paye_fetch.usc_rate_2
,p_usc_yrly_cutoff_2 => c_effective_paye_fetch.usc_yrly_cutoff_2
,p_usc_mthly_cutoff_2 => c_effective_paye_fetch.usc_mthly_cutoff_2
,p_usc_wkly_cutoff_2 => c_effective_paye_fetch.usc_wkly_cutoff_2
,p_usc_rate_3 => c_effective_paye_fetch.usc_rate_3
,p_usc_yrly_cutoff_3 => c_effective_paye_fetch.usc_yrly_cutoff_3
,p_usc_mthly_cutoff_3 => c_effective_paye_fetch.usc_mthly_cutoff_3
,p_usc_wkly_cutoff_3 => c_effective_paye_fetch.usc_wkly_cutoff_3
,p_usc_rate_4 => c_effective_paye_fetch.usc_rate_4
,p_usc_yrly_cutoff_4 => c_effective_paye_fetch.usc_yrly_cutoff_4
,p_usc_mthly_cutoff_4 => c_effective_paye_fetch.usc_mthly_cutoff_4
,p_usc_wkly_cutoff_4 => c_effective_paye_fetch.usc_wkly_cutoff_4
,p_usc_rate_5 => c_effective_paye_fetch.usc_rate_5
,p_usc_tax_basis => c_effective_paye_fetch.usc_tax_basis
,p_usc_info_source => c_effective_paye_fetch.usc_info_source
--13359423
);
else -- P_DATETRACK_UPDATE_MODE <> 'CORRECTION'
hr_utility.set_location('UPDATE and = TU',866);
pay_ie_paye_api.update_ie_paye_details
(p_validate => FALSE
,p_effective_date => p_effective_date
,p_datetrack_update_mode => 'UPDATE'
,p_paye_details_id => c_effective_paye_fetch.PAYE_DETAILs_ID
,p_info_source => decode_value_char(p_tax_upload_flag ='X',c_effective_paye_fetch.info_source,'IE_ELECTRONIC')
,p_tax_basis => decode_value_char(p_tax_upload_flag='X',c_effective_paye_fetch.tax_basis,p_tax_basis) -- tax credit upload changes
,p_certificate_start_date => decode_value_date(p_tax_upload_flag='X',c_effective_paye_fetch.certificate_start_date,p_cert_start_date) -- tax credit upload changes, 17140460.6
,p_tax_assess_basis => decode_value_char(p_tax_upload_flag ='X',c_effective_paye_fetch.tax_assess_basis,'IE_SEP_TREAT')
,p_certificate_issue_date => decode_value_date(p_tax_upload_flag='X',c_effective_paye_fetch.certificate_issue_date,p_cert_date) -- Bug 6929566 p_effective_date) -- tax credit upload changes
,p_certificate_end_date => decode_value_date(p_tax_upload_flag='X',c_effective_paye_fetch.certificate_end_date,p_cert_end_date) -- tax credit upload changes
,p_weekly_tax_credit => l_weekly_tax_credit
,p_weekly_std_rate_cut_off => l_weekly_std_rate_cut_off
,p_monthly_tax_credit => l_monthly_tax_credit
,p_monthly_std_rate_cut_off => l_monthly_std_rate_cut_off
,p_tax_deducted_to_date => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,p_tax_deducted_to_date) -- tax credit upload change
,p_pay_to_date => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,p_pay_to_date) -- tax credit upload change
,p_disability_benefit => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,null) -- tax credit upload change
,p_lump_sum_payment => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,null) -- tax credit upload change
,p_object_version_number => l_api_ovn
,p_effective_start_date => l_start_date
,p_effective_end_date => l_end_date
--13359423
,p_yrly_tax_cred => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.yrly_tax_cred,p_yrly_tax_cred)
,p_yrly_tax_rate_1 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.yrly_tax_rate_1,p_yrly_tax_rate_1)
,p_yrly_tax_rate_2 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.yrly_tax_rate_2,p_yrly_tax_rate_2)
,p_mthly_tax_rate_2 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.mthly_tax_rate_2,p_mthly_tax_rate_2)
,p_wkly_tax_rate_2 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.wkly_tax_rate_2,p_wkly_tax_rate_2)
,p_tax_rate_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.tax_rate_3,p_tax_rate_3)
,p_yrly_tax_rate_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.yrly_tax_rate_3,p_yrly_tax_rate_3)
,p_mthly_tax_rate_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.mthly_tax_rate_3,p_mthly_tax_rate_3)
,p_wkly_tax_rate_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.wkly_tax_rate_3,p_wkly_tax_rate_3)
,p_tax_rate_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.tax_rate_4,p_tax_rate_4)
,p_yrly_tax_rate_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.yrly_tax_rate_4,p_yrly_tax_rate_4)
,p_mthly_tax_rate_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.mthly_tax_rate_4,p_mthly_tax_rate_4)
,p_wkly_tax_rate_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.wkly_tax_rate_4,p_wkly_tax_rate_4)
,p_tax_rate_5 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.tax_rate_5,p_tax_rate_5)
,p_in_exempt_usc => decode_value_char(p_tax_upload_flag='X',c_effective_paye_fetch.in_exempt_usc,p_in_exempt_usc)
,p_total_usc_pay_todate => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,p_total_usc_pay_todate)
,p_total_usc_tax_todate => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,p_total_usc_tax_todate)
,p_usc_rate_1 => decode_value_char(p_tax_upload_flag='X',c_effective_paye_fetch.usc_rate_1,p_usc_rate_1)
,p_usc_yrly_cutoff_1 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_yrly_cutoff_1,p_usc_yrly_cutoff_1)
,p_usc_mthly_cutoff_1 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_mthly_cutoff_1,p_usc_mthly_cutoff_1)
,p_usc_wkly_cutoff_1 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_wkly_cutoff_1,p_usc_wkly_cutoff_1)
,p_usc_rate_2 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_rate_2,p_usc_rate_2)
,p_usc_yrly_cutoff_2 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_yrly_cutoff_2,p_usc_yrly_cutoff_2)
,p_usc_mthly_cutoff_2 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_mthly_cutoff_2,p_usc_mthly_cutoff_2)
,p_usc_wkly_cutoff_2 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_wkly_cutoff_2,p_usc_wkly_cutoff_2)
,p_usc_rate_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_rate_3,p_usc_rate_3)
,p_usc_yrly_cutoff_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_yrly_cutoff_3,p_usc_yrly_cutoff_3)
,p_usc_mthly_cutoff_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_mthly_cutoff_3,p_usc_mthly_cutoff_3)
,p_usc_wkly_cutoff_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_wkly_cutoff_3,p_usc_wkly_cutoff_3)
,p_usc_rate_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_rate_4,p_usc_rate_4)
,p_usc_yrly_cutoff_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_yrly_cutoff_4,p_usc_yrly_cutoff_4)
,p_usc_mthly_cutoff_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_mthly_cutoff_4,p_usc_mthly_cutoff_4)
,p_usc_wkly_cutoff_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_wkly_cutoff_4,p_usc_wkly_cutoff_4)
,p_usc_rate_5 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_rate_5,p_usc_rate_5)
,p_usc_tax_basis => decode_value_char(p_tax_upload_flag='X',c_effective_paye_fetch.usc_tax_basis,p_usc_tax_basis)
,p_usc_info_source => decode_value_char(p_tax_upload_flag='X',c_effective_paye_fetch.usc_info_source,p_usc_info_source)
--13359423
);
end if; --P_DATETRACK_UPDATE_MODE = 'CORRECTION'
--delete any other future records ie different paye_details_id
open c_future_paye(l_min_paye_id);
pay_ie_paye_api.delete_ie_paye_details
(p_validate => FALSE
,p_effective_date => c_future_paye_fetch.effective_start_date
,p_datetrack_delete_mode => 'ZAP'
,p_paye_details_id => c_future_paye_fetch.paye_details_id
,p_object_version_number => c_future_paye_fetch.object_version_number
,p_effective_start_date => l_start_date
,p_effective_end_date => l_end_date
);
pay_ie_paye_api.delete_ie_paye_details
(p_validate => FALSE
,p_effective_date => l_min_effective_date
,p_datetrack_delete_mode => 'FUTURE_CHANGE'
,p_paye_details_id => l_min_paye_id
,p_object_version_number => l_api_ovn
,p_effective_start_date => l_start_date
,p_effective_end_date => l_end_date
);
pay_ie_paye_api.update_ie_paye_details
(p_validate => FALSE
,p_effective_date => l_min_effective_date
,p_datetrack_update_mode => 'CORRECTION'
,p_paye_details_id => l_min_paye_id
,p_info_source => l_info_source
,p_tax_basis => l_tax_basis
,p_certificate_start_date => l_certificate_start_date
,p_tax_assess_basis => l_tax_assess_basis
,p_certificate_issue_date => l_certificate_issue_date
,p_certificate_end_date => l_certificate_end_date
,p_weekly_tax_credit => l_weekly_tax_credit
,p_weekly_std_rate_cut_off => l_weekly_std_rate_cut_off
,p_monthly_tax_credit => l_monthly_tax_credit
,p_monthly_std_rate_cut_off => l_monthly_std_rate_cut_off
,p_tax_deducted_to_date => hr_api.g_number
,p_pay_to_date => hr_api.g_number
,p_disability_benefit => hr_api.g_number
,p_lump_sum_payment => hr_api.g_number
,p_object_version_number => l_api_ovn
,p_effective_start_date => l_start_date
,p_effective_end_date => l_end_date
--13359423
,p_yrly_tax_cred => l_yrly_tax_cred
,p_yrly_tax_rate_1 => l_yrly_tax_rate_1
,p_yrly_tax_rate_2 => l_yrly_tax_rate_2
,p_mthly_tax_rate_2 => l_mthly_tax_rate_2
,p_wkly_tax_rate_2 => l_wkly_tax_rate_2
,p_tax_rate_3 => l_tax_rate_3
,p_yrly_tax_rate_3 => l_yrly_tax_rate_3
,p_mthly_tax_rate_3 => l_mthly_tax_rate_3
,p_wkly_tax_rate_3 => l_wkly_tax_rate_3
,p_tax_rate_4 => l_tax_rate_4
,p_yrly_tax_rate_4 => l_yrly_tax_rate_4
,p_mthly_tax_rate_4 => l_mthly_tax_rate_4
,p_wkly_tax_rate_4 => l_wkly_tax_rate_4
,p_tax_rate_5 => l_tax_rate_5
,p_in_exempt_usc => l_in_exempt_usc
,p_total_usc_pay_todate => hr_api.g_number
,p_total_usc_tax_todate => hr_api.g_number
,p_usc_rate_1 => l_usc_rate_1
,p_usc_yrly_cutoff_1 => l_usc_yrly_cutoff_1
,p_usc_mthly_cutoff_1 => l_usc_mthly_cutoff_1
,p_usc_wkly_cutoff_1 => l_usc_wkly_cutoff_1
,p_usc_rate_2 => l_usc_rate_2
,p_usc_yrly_cutoff_2 => l_usc_yrly_cutoff_2
,p_usc_mthly_cutoff_2 => l_usc_mthly_cutoff_2
,p_usc_wkly_cutoff_2 => l_usc_wkly_cutoff_2
,p_usc_rate_3 => l_usc_rate_3
,p_usc_yrly_cutoff_3 => l_usc_yrly_cutoff_3
,p_usc_mthly_cutoff_3 => l_usc_mthly_cutoff_3
,p_usc_wkly_cutoff_3 => l_usc_wkly_cutoff_3
,p_usc_rate_4 => l_usc_rate_4
,p_usc_yrly_cutoff_4 => l_usc_yrly_cutoff_4
,p_usc_mthly_cutoff_4 => l_usc_mthly_cutoff_4
,p_usc_wkly_cutoff_4 => l_usc_wkly_cutoff_4
,p_usc_rate_5 => l_usc_rate_5
,p_usc_tax_basis => l_usc_tax_basis
,p_usc_info_source => l_usc_info_source
--13359423
);
--delete any other future records ie different paye_details_id
hr_utility.set_location('else of future paye.'|| l_info_source,876);
pay_ie_paye_api.delete_ie_paye_details
(p_validate => FALSE
,p_effective_date => c_tax_upload_paye.effective_start_date
,p_datetrack_delete_mode => 'ZAP'
,p_paye_details_id => c_tax_upload_paye.paye_details_id
,p_object_version_number => c_tax_upload_paye.object_version_number
,p_effective_start_date => l_start_date
,p_effective_end_date => l_end_date
);
pay_ie_paye_api.delete_ie_paye_details
(p_validate => FALSE
,p_effective_date => l_max_effective_start_date
,p_datetrack_delete_mode => 'FUTURE_CHANGE'
,p_paye_details_id => l_max_paye_id
,p_object_version_number => l_api_ovn
,p_effective_start_date => l_start_date
,p_effective_end_date => l_end_date
);
pay_ie_paye_api.update_ie_paye_details
(p_validate => FALSE
,p_effective_date => l_max_effective_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_paye_details_id => l_max_paye_id
,p_info_source => 'IE_ELECTRONIC'
,p_tax_basis => p_tax_basis
,p_certificate_start_date => p_cert_start_date
,p_tax_assess_basis => 'IE_SEP_TREAT'
,p_certificate_issue_date => p_effective_date
,p_certificate_end_date => p_cert_end_date
,p_weekly_tax_credit => p_weekly_tax_credit
,p_weekly_std_rate_cut_off => p_weekly_std_rate_cut_off
,p_monthly_tax_credit => p_monthly_tax_credit
,p_monthly_std_rate_cut_off => p_monthly_std_rate_cut_off
,p_tax_deducted_to_date => p_tax_deducted_to_date
,p_pay_to_date => p_pay_to_date
,p_disability_benefit => null
,p_lump_sum_payment => null
,p_object_version_number => l_api_ovn
,p_effective_start_date => l_start_date
,p_effective_end_date => l_end_date
--13359423
,p_yrly_tax_cred => p_yrly_tax_cred
,p_yrly_tax_rate_1 => p_yrly_tax_rate_1
,p_yrly_tax_rate_2 => p_yrly_tax_rate_2
,p_mthly_tax_rate_2 => p_mthly_tax_rate_2
,p_wkly_tax_rate_2 => p_wkly_tax_rate_2
,p_tax_rate_3 => p_tax_rate_3
,p_yrly_tax_rate_3 => p_yrly_tax_rate_3
,p_mthly_tax_rate_3 => p_mthly_tax_rate_3
,p_wkly_tax_rate_3 => p_wkly_tax_rate_3
,p_tax_rate_4 => p_tax_rate_4
,p_yrly_tax_rate_4 => p_yrly_tax_rate_4
,p_mthly_tax_rate_4 => p_mthly_tax_rate_4
,p_wkly_tax_rate_4 => p_wkly_tax_rate_4
,p_tax_rate_5 => p_tax_rate_5
,p_in_exempt_usc => p_in_exempt_usc
,p_total_usc_pay_todate => p_total_usc_pay_todate
,p_total_usc_tax_todate => p_total_usc_tax_todate
,p_usc_rate_1 => p_usc_rate_1
,p_usc_yrly_cutoff_1 => p_usc_yrly_cutoff_1
,p_usc_mthly_cutoff_1 => p_usc_mthly_cutoff_1
,p_usc_wkly_cutoff_1 => p_usc_wkly_cutoff_1
,p_usc_rate_2 => p_usc_rate_2
,p_usc_yrly_cutoff_2 => p_usc_yrly_cutoff_2
,p_usc_mthly_cutoff_2 => p_usc_mthly_cutoff_2
,p_usc_wkly_cutoff_2 => p_usc_wkly_cutoff_2
,p_usc_rate_3 => p_usc_rate_3
,p_usc_yrly_cutoff_3 => p_usc_yrly_cutoff_3
,p_usc_mthly_cutoff_3 => p_usc_mthly_cutoff_3
,p_usc_wkly_cutoff_3 => p_usc_wkly_cutoff_3
,p_usc_rate_4 => p_usc_rate_4
,p_usc_yrly_cutoff_4 => p_usc_yrly_cutoff_4
,p_usc_mthly_cutoff_4 => p_usc_mthly_cutoff_4
,p_usc_wkly_cutoff_4 => p_usc_wkly_cutoff_4
,p_usc_rate_5 => p_usc_rate_5
,p_usc_tax_basis => p_usc_tax_basis
,p_usc_info_source => p_usc_info_source
--13359423
);
END update_paye_change_freq;
SELECT ptp.start_date start_date
,ptp.end_date end_date
FROM per_time_periods ptp
WHERE ptp.payroll_id=p_payroll_id
AND p_date_earned BETWEEN ptp.start_date AND ptp.end_date;
SELECT date_of_birth,per.last_name,paf.assignment_number
FROM per_all_people_f per
,per_all_assignments_f paf
WHERE per.person_id = paf.person_id
AND paf.assignment_id = p_assignment_id
AND p_date_earned BETWEEN per.effective_start_date AND per.effective_end_date
AND p_date_earned BETWEEN paf.effective_start_date AND paf.effective_end_date;
SELECT effective_date
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT date_of_birth,per.last_name,paf.assignment_number
FROM per_all_people_f per
,per_all_assignments_f paf
WHERE per.person_id = paf.person_id
AND paf.assignment_id = p_assignment_id
AND l_date_paid BETWEEN per.effective_start_date AND per.effective_end_date
AND l_date_paid BETWEEN paf.effective_start_date AND paf.effective_end_date;
SELECT COUNT (*)
FROM per_time_periods
WHERE payroll_id = p_payroll_id
AND regular_payment_date BETWEEN p_start_date AND p_end_date;
SELECT org_information1, org_information2, org_information3, org_information4, org_information5
FROM hr_organization_information
WHERE organization_id = p_tax_unit_id
and org_information_context = 'IE_EMPLOYER_INFO';
cursor c_last_pay_period is select max(ptp.period_num)
from per_time_periods ptp,
pay_all_payrolls pap,
pay_payroll_actions ppa
where pap.payroll_id = ptp.payroll_id
and pap.payroll_id=p_payroll_id
and ppa.payroll_id=pap.payroll_id
and ppa.payroll_action_id=p_payroll_action_id
and ptp.PERIOD_NAME like '%'||to_char(ppa.date_earned, 'YYYY')||'%';