The following lines contain the word 'select', 'insert', 'update' or 'delete':
select per.person_id person_id
,per.NATIONAL_IDENTIFIER pps_number
,count(distinct(asg.ASSIGNMENT_NUMBER)) no_of_assg
from per_all_assignments_f asg,
per_all_people_f per,
pay_all_payrolls_f pay,
pay_ie_tax_body_interface tbi,
per_periods_of_service pps
where per.national_identifier = tbi.pps_number
--AND pay.payroll_id = nvl(p_payroll_id,pay.payroll_id)
--AND asg.payroll_id = pay.payroll_id
AND per.person_id = asg.person_id
AND tbi.process_flag = 'Y'
AND asg.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
AND asg.effective_end_date >= trunc(tbi.cert_start_date)
AND per.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
AND per.effective_end_date >= trunc(tbi.cert_start_date)
and asg.period_of_service_id=pps.period_of_service_id
and pps.person_id=per.person_id
--and pps.date_start <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
and pps.period_of_service_id in (select max(pps1.period_of_service_id) from per_periods_of_service pps1 where pps1.person_id=pps.person_id and pps1.date_start <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy'))
--AND pay.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
--AND pay.effective_end_date >= trunc(tbi.cert_start_date)
group by per.person_id,per.NATIONAL_IDENTIFIER;
Select 1 from per_all_people_f per
,pay_ie_tax_body_interface tbi
Where per.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
AND per.effective_end_date >= trunc(tbi.cert_start_date)
and per.national_identifier=p_pps_no
AND tbi.process_flag = 'Y'
and tbi.pps_number=per.national_identifier;
SELECT distinct per.employee_number employee_no_hr
, per.national_identifier pps_number_hr
, per.last_name last_name_hr
, per.first_name first_name_hr
, asg.assignment_id assignment_id
--, asg.effective_start_date effective_start_date
, hoi.org_information1 tax_district
, pay.payroll_name payroll_name_hr
, pay.payroll_id payroll_id -- 4878630
--, ppd.paye_details_id paye_details_id --4878630
--, ppd.object_version_number object_version_no --4878630
--, ppd.effective_start_date ppd_effective_start_date
, tbi.pps_number pps_number_int
, asg.assignment_number employee_no_int --5724436
, tbi.first_name first_name_int
, tbi.last_name last_name_int
, tbi.cert_start_date cert_start_date
, tbi.cert_end_date cert_end_date
, tbi.cert_date cert_date
, tbi.wk_tax_credit wk_tax_credit
, tbi.mth_tax_credit mth_tax_credit
, tbi.wk_rate_cutoff wk_rate_cutoff
, tbi.mth_rate_cutoff mth_rate_cutoff
-- Bug Fix 3500192
, tbi.wk_mth_indicator wk_mth_indicator
-- Bug Fix 4618981
, tbi.exemption_indicator exemption_indicator
, tbi.tot_tax_to_date tot_tax_to_date
, tbi.tot_pay_to_date tot_pay_to_date
, tbi.std_rate_of_tax std_rate_of_tax
, tbi.higher_rate_of_tax higher_rate_of_tax
--13359423
,tbi.cd_yrly_tax_cred
,tbi.cd_yrly_tax_rate_1
,tbi.cd_yrly_tax_rate_2
,tbi.cd_mthly_tax_rate_2
,tbi.cd_wkly_tax_rate_2
,tbi.cd_tax_rate_3
,tbi.cd_yrly_tax_rate_3
,tbi.cd_mthly_tax_rate_3
,tbi.cd_wkly_tax_rate_3
,tbi.cd_tax_rate_4
,tbi.cd_yrly_tax_rate_4
,tbi.cd_mthly_tax_rate_4
,tbi.cd_wkly_tax_rate_4
,tbi.cd_tax_rate_5
,tbi.in_exempt_usc
,tbi.cd_total_usc_pay_todate
,tbi.cd_total_usc_tax_todate
,tbi.cd_usc_rate_1
,tbi.am_usc_yrly_cutoff_1
,tbi.am_usc_mthly_cutoff_1
,tbi.am_usc_wkly_cutoff_1
,tbi.cd_usc_rate_2
,tbi.am_usc_yrly_cutoff_2
,tbi.am_usc_mthly_cutoff_2
,tbi.am_usc_wkly_cutoff_2
,tbi.cd_usc_rate_3
,tbi.am_usc_yrly_cutoff_3
,tbi.am_usc_mthly_cutoff_3
,tbi.am_usc_wkly_cutoff_3
,tbi.cd_usc_rate_4
,tbi.am_usc_yrly_cutoff_4
,tbi.am_usc_mthly_cutoff_4
,tbi.am_usc_wkly_cutoff_4
,tbi.cd_usc_rate_5
--13359423
FROM hr_organization_information hoi
, hr_organization_units hou
, per_all_assignments_f asg
, per_all_people_f per
, pay_all_payrolls_f pay
--, pay_ie_paye_details_f ppd
, pay_ie_tax_body_interface tbi
, pay_ie_tax_header_interface thi
WHERE per.person_id = asg.person_id
AND per.national_identifier = tbi.pps_number
AND asg.business_group_id = hou.business_group_id
AND hou.organization_id = hoi.organization_id
AND hoi.org_information_context = 'IE_EMPLOYER_INFO' -- For Employer changes 4369280
AND hoi.org_information2 = p_employer_number
AND pay.payroll_id = nvl(p_payroll_id,pay.payroll_id)
AND asg.payroll_id = pay.payroll_id
--AND asg.assignment_id = ppd.assignment_id
AND per.national_identifier = p_pps_number
-- Bug Fix 3500192
-- added for multiple assignment issue 5894942
AND asg.assignment_number = tbi.works_number
AND thi.employer_number = p_employer_number
AND thi.tax_year = p_tax_year
AND tbi.employer_number = thi.employer_number
AND tbi.process_flag = 'Y'
AND asg.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
AND asg.effective_end_date >= trunc(tbi.cert_start_date)
AND per.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
AND per.effective_end_date >= trunc(tbi.cert_start_date)
AND pay.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
AND pay.effective_end_date >= trunc(tbi.cert_start_date);
SELECT distinct per.employee_number employee_no_hr
, per.national_identifier pps_number_hr
, per.last_name last_name_hr
, per.first_name first_name_hr
, asg.assignment_id assignment_id
--, asg.effective_start_date effective_start_date
, hoi.org_information1 tax_district
, pay.payroll_name payroll_name_hr
, pay.payroll_id payroll_id -- 4878630
--, ppd.paye_details_id paye_details_id --4878630
--, ppd.object_version_number object_version_no --4878630
--, ppd.effective_start_date ppd_effective_start_date
, tbi.pps_number pps_number_int
, asg.assignment_number employee_no_int --5724436
, tbi.first_name first_name_int
, tbi.last_name last_name_int
, tbi.cert_start_date cert_start_date
, tbi.cert_end_date cert_end_date
, tbi.cert_date cert_date
, tbi.wk_tax_credit wk_tax_credit
, tbi.mth_tax_credit mth_tax_credit
, tbi.wk_rate_cutoff wk_rate_cutoff
, tbi.mth_rate_cutoff mth_rate_cutoff
-- Bug Fix 3500192
, tbi.wk_mth_indicator wk_mth_indicator
-- Bug Fix 4618981
, tbi.exemption_indicator exemption_indicator
, tbi.tot_tax_to_date tot_tax_to_date
, tbi.tot_pay_to_date tot_pay_to_date
, tbi.std_rate_of_tax std_rate_of_tax
, tbi.higher_rate_of_tax higher_rate_of_tax
--13359423
,tbi.cd_yrly_tax_cred
,tbi.cd_yrly_tax_rate_1
,tbi.cd_yrly_tax_rate_2
,tbi.cd_mthly_tax_rate_2
,tbi.cd_wkly_tax_rate_2
,tbi.cd_tax_rate_3
,tbi.cd_yrly_tax_rate_3
,tbi.cd_mthly_tax_rate_3
,tbi.cd_wkly_tax_rate_3
,tbi.cd_tax_rate_4
,tbi.cd_yrly_tax_rate_4
,tbi.cd_mthly_tax_rate_4
,tbi.cd_wkly_tax_rate_4
,tbi.cd_tax_rate_5
,tbi.in_exempt_usc
,tbi.cd_total_usc_pay_todate
,tbi.cd_total_usc_tax_todate
,tbi.cd_usc_rate_1
,tbi.am_usc_yrly_cutoff_1
,tbi.am_usc_mthly_cutoff_1
,tbi.am_usc_wkly_cutoff_1
,tbi.cd_usc_rate_2
,tbi.am_usc_yrly_cutoff_2
,tbi.am_usc_mthly_cutoff_2
,tbi.am_usc_wkly_cutoff_2
,tbi.cd_usc_rate_3
,tbi.am_usc_yrly_cutoff_3
,tbi.am_usc_mthly_cutoff_3
,tbi.am_usc_wkly_cutoff_3
,tbi.cd_usc_rate_4
,tbi.am_usc_yrly_cutoff_4
,tbi.am_usc_mthly_cutoff_4
,tbi.am_usc_wkly_cutoff_4
,tbi.cd_usc_rate_5
--13359423
FROM hr_organization_information hoi
, hr_organization_units hou
, per_all_assignments_f asg
, per_all_people_f per
, pay_all_payrolls_f pay
--, pay_ie_paye_details_f ppd
, pay_ie_tax_body_interface tbi
, pay_ie_tax_header_interface thi,
per_periods_of_service pps
WHERE per.person_id = asg.person_id
AND per.national_identifier = tbi.pps_number
AND asg.business_group_id = hou.business_group_id
AND hou.organization_id = hoi.organization_id
AND hoi.org_information_context = 'IE_EMPLOYER_INFO' -- For Employer changes 4369280
AND hoi.org_information2 = p_employer_number
AND pay.payroll_id = nvl(p_payroll_id,pay.payroll_id)
AND asg.payroll_id = pay.payroll_id
--AND asg.assignment_id = ppd.assignment_id
AND per.national_identifier = p_pps_number
-- Bug Fix 3500192
-- added for multiple assignment issue 5894942
--AND asg.assignment_number = tbi.works_number
AND thi.employer_number = p_employer_number
AND thi.tax_year = p_tax_year
AND tbi.employer_number = thi.employer_number
AND tbi.process_flag = 'Y'
and asg.period_of_service_id=pps.period_of_service_id
and pps.person_id=per.person_id
--and pps.date_start <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
and pps.period_of_service_id in (select max(pps1.period_of_service_id) from per_periods_of_service pps1 where pps1.person_id=pps.person_id and pps1.date_start <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy'))
AND asg.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
AND asg.effective_end_date >= trunc(tbi.cert_start_date)
AND per.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
AND per.effective_end_date >= trunc(tbi.cert_start_date)
AND pay.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
AND pay.effective_end_date >= trunc(tbi.cert_start_date);
select pps_number, works_number from pay_ie_tax_body_interface
minus
SELECT distinct per.national_identifier pps_number, asg.assignment_number works_number
FROM hr_organization_information hoi
, hr_organization_units hou
, per_all_assignments_f asg
, per_all_people_f per
, pay_all_payrolls_f pay
--, pay_ie_paye_details_f ppd
, pay_ie_tax_body_interface tbi
, pay_ie_tax_header_interface thi
WHERE per.person_id = asg.person_id
AND asg.business_group_id = hou.business_group_id
AND hou.organization_id = hoi.organization_id
AND hoi.org_information_context = 'IE_EMPLOYER_INFO'
AND hoi.org_information2 = p_employer_number
AND pay.payroll_id = nvl(p_payroll_id,pay.payroll_id)
AND asg.payroll_id = pay.payroll_id
AND per.national_identifier = tbi.pps_number
AND asg.assignment_number = tbi.works_number
AND thi.employer_number = p_employer_number
AND thi.tax_year = p_tax_year
AND tbi.employer_number = thi.employer_number
AND tbi.process_flag = 'Y'
AND asg.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
AND asg.effective_end_date >= trunc(tbi.cert_start_date)
AND per.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
AND per.effective_end_date >= trunc(tbi.cert_start_date)
AND pay.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
AND pay.effective_end_date >= trunc(tbi.cert_start_date); */
select distinct pps_number pps_number,works_number,last_name,first_name
from pay_ie_tax_body_interface;
select count(*) from pay_ie_paye_details_f where
assignment_id = p_assignment_id;
select fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) aa
from pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = p_assignment_id
and to_number(to_char(ppa.effective_date,'YYYY')) = p_tax_year;
select distinct ppd.*
from per_all_people_f papf,
per_all_assignments_f paaf,
pay_ie_paye_details_f ppd
where papf.national_identifier = p_ppsn
and papf.person_id = paaf.person_id
and paaf.assignment_id = p_assignment_id
and paaf.assignment_id = ppd.assignment_id
and paaf.assignment_number = p_assignment_number
AND paaf.effective_start_date <= to_date('31/12/'||to_char(p_cert_start_date,'YYYY'),'dd/mm/yyyy')
AND paaf.effective_end_date >= trunc(p_cert_start_date)
AND papf.effective_start_date <= to_date('31/12/'||to_char(p_cert_start_date,'YYYY'),'dd/mm/yyyy')
AND papf.effective_end_date >= trunc(p_cert_start_date)
AND ppd.effective_start_date <= to_date('31/12/'||to_char(p_cert_start_date,'YYYY'),'dd/mm/yyyy')
AND ppd.effective_end_date >= trunc(p_cert_start_date);
SELECT pp.period_type
FROM pay_payrolls_f pp, per_assignments_f pa
WHERE pa.assignment_id = p_assignment_id
AND p_effective_date BETWEEN pa.effective_start_date AND pa.effective_end_date
AND pp.payroll_id = pa.payroll_id
AND p_effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
select global_value
from ff_globals_f
where global_name = p_global_name
and p_cert_date between effective_start_date and effective_end_date;
pay_ie_paye_pkg.update_paye_change_freq --4878630
(p_assignment_id => r_pay.assignment_id
,p_effective_date => l_effective_date -- Bug 6929566 -- r_pay.cert_date -- 5724436
,p_payroll_id => r_pay.payroll_id
,P_DATETRACK_UPDATE_MODE => 'UPDATE'
,p_tax_upload_flag => 'TU'
,p_tax_basis => l_tax_basis
,p_cert_start_date => r_pay.cert_start_date -- 17140460.6
,p_cert_end_date => r_pay.cert_end_date
,p_weekly_tax_credit => r_pay.wk_tax_credit
,p_monthly_tax_credit => r_pay.mth_tax_credit
,p_weekly_std_rate_cut_off => r_pay.wk_rate_cutoff
,p_monthly_std_rate_cut_off => r_pay.mth_rate_cutoff
,p_tax_deducted_to_date => r_pay.tot_tax_to_date
,p_pay_to_date => r_pay.tot_pay_to_date
,p_cert_date =>r_pay.cert_date
--13359423
,p_yrly_tax_cred => r_pay.cd_yrly_tax_cred
,p_yrly_tax_rate_1 => r_pay.cd_yrly_tax_rate_1
,p_yrly_tax_rate_2 => r_pay.cd_yrly_tax_rate_2
,p_mthly_tax_rate_2 => r_pay.cd_mthly_tax_rate_2
,p_wkly_tax_rate_2 => r_pay.cd_wkly_tax_rate_2
,p_tax_rate_3 => r_pay.cd_tax_rate_3
,p_yrly_tax_rate_3 => r_pay.cd_yrly_tax_rate_3
,p_mthly_tax_rate_3 => r_pay.cd_mthly_tax_rate_3
,p_wkly_tax_rate_3 => r_pay.cd_wkly_tax_rate_3
,p_tax_rate_4 => r_pay.cd_tax_rate_4
,p_yrly_tax_rate_4 => r_pay.cd_yrly_tax_rate_4
,p_mthly_tax_rate_4 => r_pay.cd_mthly_tax_rate_4
,p_wkly_tax_rate_4 => r_pay.cd_wkly_tax_rate_4
,p_tax_rate_5 => r_pay.cd_tax_rate_5
,p_in_exempt_usc => r_pay.in_exempt_usc
,p_total_usc_pay_todate => r_pay.cd_total_usc_pay_todate
,p_total_usc_tax_todate => r_pay.cd_total_usc_tax_todate
,p_usc_rate_1 => r_pay.cd_usc_rate_1
,p_usc_yrly_cutoff_1 => r_pay.am_usc_yrly_cutoff_1
,p_usc_mthly_cutoff_1 => r_pay.am_usc_mthly_cutoff_1
,p_usc_wkly_cutoff_1 => r_pay.am_usc_wkly_cutoff_1
,p_usc_rate_2 => r_pay.cd_usc_rate_2
,p_usc_yrly_cutoff_2 => r_pay.am_usc_yrly_cutoff_2
,p_usc_mthly_cutoff_2 => r_pay.am_usc_mthly_cutoff_2
,p_usc_wkly_cutoff_2 => r_pay.am_usc_wkly_cutoff_2
,p_usc_rate_3 => r_pay.cd_usc_rate_3
,p_usc_yrly_cutoff_3 => r_pay.am_usc_yrly_cutoff_3
,p_usc_mthly_cutoff_3 => r_pay.am_usc_mthly_cutoff_3
,p_usc_wkly_cutoff_3 => r_pay.am_usc_wkly_cutoff_3
,p_usc_rate_4 => r_pay.cd_usc_rate_4
,p_usc_yrly_cutoff_4 => r_pay.am_usc_yrly_cutoff_4
,p_usc_mthly_cutoff_4 => r_pay.am_usc_mthly_cutoff_4
,p_usc_wkly_cutoff_4 => r_pay.am_usc_wkly_cutoff_4
,p_usc_rate_5 => r_pay.cd_usc_rate_5
,p_usc_tax_basis => l_usc_tax_basis
,p_usc_info_source => 'IE_ELECTRONIC'
--13359423
);
/*Update interface table and set processed flag to 'Yes' to record that record has been updated or
inserted into payroll tables successfully using the row handler APIs.*/
IF (p_validate_mode = 'IE_VALIDATE_COMMIT') THEN
UPDATE pay_ie_tax_body_interface
SET processed_flag = 'Y'
WHERE pps_number = r_pay.pps_number_int;
FND_FILE.PUT_LINE(fnd_file.log, 'This record has been updated to a future date');
INSERT INTO pay_ie_tax_error ( pps_number
, employee_number
, full_name
, payroll_name
, tax_district
, error_stack_message
, error_message
, request_id
, error_date )
VALUES (r_pay.pps_number_hr
, r_pay.employee_no_hr
, r_pay.last_name_hr ||' '|| r_pay.first_name_hr
, r_pay.payroll_name_hr
, r_pay.tax_district
, l_error_stack
, l_error
, l_request_id
, sysdate);
INSERT INTO pay_ie_tax_error ( pps_number
, employee_number
, full_name
, payroll_name
, tax_district
, error_stack_message
, error_message
, request_id
, error_date )
VALUES (r_pay.pps_number_hr
, substr(r_pay.employee_no_int,1,12)
, r_pay.last_name_hr ||' '|| r_pay.first_name_hr
, r_pay.payroll_name_hr
, r_pay.tax_district
, l_error_stack
, l_error
, l_request_id
, sysdate);
INSERT INTO pay_ie_tax_error ( pps_number
, employee_number
, full_name
, payroll_name
, tax_district
, error_stack_message
, error_message
, request_id
, error_date )
VALUES (r_pay.pps_number_hr
, substr(r_pay.employee_no_int,1,12)
, r_pay.last_name_hr ||' '|| r_pay.first_name_hr
, r_pay.payroll_name_hr
, r_pay.tax_district
, l_error_stack
, l_error
, l_request_id
, sysdate);
INSERT INTO pay_ie_tax_error ( pps_number
, employee_number
, full_name
, payroll_name
, tax_district
, error_stack_message
, error_message
, request_id
, error_date )
VALUES (r_pay.pps_number_hr
, substr(r_pay.employee_no_int,1,12)
, r_pay.last_name_hr ||' '|| r_pay.first_name_hr
, r_pay.payroll_name_hr
, r_pay.tax_district
, l_error_stack
, l_error
, l_request_id
, sysdate);
INSERT INTO pay_ie_tax_error ( pps_number
, employee_number
, full_name
, payroll_name
, tax_district
, error_stack_message
, error_message
, request_id
, error_date )
VALUES (r_pay.pps_number_hr
, substr(r_pay.employee_no_int,1,12)
, r_pay.last_name_hr ||' '|| r_pay.first_name_hr
, r_pay.payroll_name_hr
, r_pay.tax_district
, l_error_stack
, l_error
, l_request_id
, sysdate);
INSERT INTO pay_ie_tax_error ( pps_number
, employee_number
, full_name
, payroll_name
, tax_district
, error_stack_message
, error_message
, request_id
, error_date )
VALUES (r_pay.pps_number_hr
, substr(r_pay.employee_no_int,1,12)
, r_pay.last_name_hr ||' '|| r_pay.first_name_hr
, r_pay.payroll_name_hr
, r_pay.tax_district
, l_error_stack
, l_error
, l_request_id
, sysdate);
INSERT INTO pay_ie_tax_error ( pps_number
, employee_number
, full_name
, payroll_name
, tax_district
, error_stack_message
, error_message
, request_id
, error_date )
VALUES (r_pay.pps_number_hr
, substr(r_pay.employee_no_int,1,12)
, r_pay.last_name_hr ||' '|| r_pay.first_name_hr
, r_pay.payroll_name_hr
, r_pay.tax_district
, l_error_stack
, l_error
, l_request_id
, sysdate);
/*Update interface table and set processed flag to 'No' to record that record has not been updated
or inserted into payroll tables*/
IF p_validate_mode = 'IE_VALIDATE_COMMIT' THEN
UPDATE pay_ie_tax_body_interface
SET processed_flag = 'N'
WHERE pps_number = r_pay.pps_number_int;
INSERT INTO pay_ie_tax_error ( pps_number
, employee_number
, full_name
, payroll_name
, tax_district
, error_stack_message
, error_message
, request_id
, error_date )
VALUES (r_pay.pps_number_hr
, substr(r_pay.employee_no_int,1,12)
, r_pay.last_name_hr ||' '|| r_pay.first_name_hr
, r_pay.payroll_name_hr
, r_pay.tax_district
, l_error_stack
, l_error
, l_request_id
, sysdate);
/* If user selects the mode to be 'Validate and Commit' then
p_validate_mode = 'IE_VALIDATE_COMMIT' then records are committed else records are rolled back */
IF (p_validate_mode = 'IE_VALIDATE_COMMIT') THEN
COMMIT;
INSERT INTO pay_ie_tax_error ( pps_number
, employee_number
, full_name
, payroll_name
, tax_district
, error_stack_message
, error_message
, request_id
, error_date)
VALUES (l_pps_number_hr
, substr(l_employee_number_hr,1,12)
, l_last_name_hr ||' '|| l_first_name_hr
, l_payroll_name_hr
, l_tax_district
, l_error_stack
, l_error
, l_request_id
, sysdate
);
INSERT INTO pay_ie_tax_error ( pps_number
, employee_number
, full_name
, payroll_name
, tax_district
, error_stack_message
, error_message
, request_id
, error_date)
VALUES (l_pps_number_hr
, substr(l_employee_number_hr,1,12)
, l_last_name_hr ||' '||l_first_name_hr
, l_payroll_name_hr
, l_tax_district
, l_error_stack
, l_error
, l_request_id
, sysdate);
SELECT COUNT(PBS.MTH_TAX_CREDIT) count_mth_taxcredit
, SUM (NVL(PBS.MTH_RATE_CUTOFF,0)) sum_mth_rate_cutoff
, SUM (NVL(PBS.WK_RATE_CUTOFF,0)) sum_wk_rate_cutoff
, SUM(NVL(PBS.MTH_TAX_CREDIT,0)) sum_mth_tax_credit
, SUM(NVL(PBS.WK_TAX_CREDIT,0)) sum_wk_tax_credit
--
,SUM(NVL(PBS.CD_YRLY_TAX_CRED,0)) sum_YRLY_TAX_CRED
,SUM(NVL(PBS.CD_YRLY_TAX_RATE_1,0)) sum_YRLY_TAX_RATE_1
,SUM(NVL(PBS.CD_YRLY_TAX_RATE_2,0)) sum_YRLY_TAX_RATE_2
,SUM(NVL(PBS.CD_MTHLY_TAX_RATE_2,0)) sum_MTHLY_TAX_RATE_2
,SUM(NVL(PBS.CD_WKLY_TAX_RATE_2,0)) sum_WKLY_TAX_RATE_2
,SUM(NVL(PBS.CD_YRLY_TAX_RATE_3,0)) sum_YRLY_TAX_RATE_3
,SUM(NVL(PBS.CD_MTHLY_TAX_RATE_3,0)) sum_MTHLY_TAX_RATE_3
,SUM(NVL(PBS.CD_WKLY_TAX_RATE_3,0)) sum_WKLY_TAX_RATE_3
,SUM(NVL(PBS.CD_YRLY_TAX_RATE_4,0)) sum_YRLY_TAX_RATE_4
,SUM(NVL(PBS.CD_MTHLY_TAX_RATE_4,0)) sum_MTHLY_TAX_RATE_4
,SUM(NVL(PBS.CD_WKLY_TAX_RATE_4,0)) sum_WKLY_TAX_RATE_4
,SUM(NVL(PBS.AM_USC_YRLY_CUTOFF_1,0)) sum_USC_YRLY_CUTOFF_1
,SUM(NVL(PBS.AM_USC_YRLY_CUTOFF_2,0)) sum_USC_YRLY_CUTOFF_2
,SUM(NVL(PBS.AM_USC_YRLY_CUTOFF_3,0)) sum_USC_YRLY_CUTOFF_3
,SUM(NVL(PBS.AM_USC_YRLY_CUTOFF_4,0)) sum_USC_YRLY_CUTOFF_4
--
FROM PAY_IE_TAX_HEADER_INTERFACE phs
, PAY_IE_TAX_BODY_INTERFACE pbs
WHERE PHS.EMPLOYER_NUMBER = PBS.EMPLOYER_NUMBER
AND PHS.TAX_YEAR = p_tax_year
AND PBS.EMPLOYER_NUMBER = p_employer_number;
SELECT PTS.RECORD_NO count_emp_recno
, NVL(PTS.TOTAL_MTH_RATE_CUTOFF,0) total_mth_cutoff
, NVL(PTS.TOTAL_WK_RATE_CUTOFF,0) total_wk_cutoff
, NVL(PTS.TOTAL_MTH_TAX_CREDIT,0) total_mth_credit
, NVL(PTS.TOTAL_WK_TAX_CREDIT,0) total_wk_credit
--
,NVL(PTS.TC_TOT_YEARLY_TAX_CRED,0) total_YEARLY_TAX_CRED
,NVL(PTS.TOT_YEARLY_TAX_RATE_1,0) total_YEARLY_TAX_RATE_1
,NVL(PTS.TOT_YEARLY_TAX_RATE_2,0) total_YEARLY_TAX_RATE_2
,NVL(PTS.TOT_MONTHLY_TAX_RATE_2,0) total_MONTHLY_TAX_RATE_2
,NVL(PTS.TOT_WEEKLY_TAX_RATE_2,0) total_WEEKLY_TAX_RATE_2
,NVL(PTS.TOT_YEARLY_TAX_RATE_3,0) total_YEARLY_TAX_RATE_3
,NVL(PTS.TOT_MONTHLY_TAX_RATE_3,0) total_MONTHLY_TAX_RATE_3
,NVL(PTS.TOT_WEEKLY_TAX_RATE_3,0) total_WEEKLY_TAX_RATE_3
,NVL(PTS.TOT_YEARLY_TAX_RATE_4,0) total_YEARLY_TAX_RATE_4
,NVL(PTS.TOT_MONTHLY_TAX_RATE_4,0) total_MONTHLY_TAX_RATE_4
,NVL(PTS.TOT_WEEKLY_TAX_RATE_4,0) total_WEEKLY_TAX_RATE_4
,NVL(PTS.TOT_YEARLY_USC_RATE_1,0) total_YEARLY_USC_RATE_1
,NVL(PTS.TOT_YEARLY_USC_RATE_2,0) total_YEARLY_USC_RATE_2
,NVL(PTS.TOT_YEARLY_USC_RATE_3,0) total_YEARLY_USC_RATE_3
,NVL(PTS.TOT_YEARLY_USC_RATE_4,0) total_YEARLY_USC_RATE_4
--
FROM PAY_IE_TAX_HEADER_INTERFACE phs
, PAY_IE_TAX_TRAILER_INTERFACE pts
WHERE PTS.EMPLOYER_NUMBER = p_employer_number
AND PHS.TAX_YEAR = p_tax_year;
Delete from pay_ie_tax_error;
update pay_ie_tax_body_interface
set process_flag = 'Y'
where EMPLOYER_NUMBER = p_employer_number;
INSERT INTO pay_ie_tax_error ( pps_number
, employee_number
, full_name
, payroll_name
, error_stack_message
, error_message
, request_id
, error_date)
VALUES (0
, NULL
, NULL
, NULL
, l_error_stack
, l_error
, l_request_id
, sysdate);
INSERT INTO pay_ie_tax_error ( pps_number
, employee_number
, full_name
, payroll_name
, error_stack_message
, error_message
, request_id
, error_date)
VALUES (0
, NULL
, NULL
, NULL
, l_error_stack
, l_error
, l_request_id
, sysdate);
Delete from PAY_IE_TAX_HEADER_INTERFACE;
Delete from PAY_IE_TAX_BODY_INTERFACE;
Delete from PAY_IE_TAX_TRAILER_INTERFACE;
--Inserting into the header table
l_record_type := 'HC';
INSERT INTO PAY_IE_TAX_HEADER_INTERFACE
(
RECORD_TYPE
,EMPLOYER_NUMBER
,EMPLOYER_NAME
,EMPLOYER_UNIT_NO
,TAX_YEAR
,HC_DATE
,HC_LABEL_YEAR
)
VALUES
(
'HC'
,l_employer_number
,l_employer_name
,l_employer_unit_no
,l_tax_year
,l_hc_date
,l_hc_label_year
);
--Inserting into the Body table
l_pps_number := get_token(l_present_line1,1,'",');
INSERT INTO PAY_IE_TAX_BODY_INTERFACE
(
RECORD_TYPE
,PROCESS_FLAG
,PPS_NUMBER
,EMPLOYER_NUMBER
,WK_RATE_CUTOFF
,WORKS_NUMBER
,MTH_TAX_CREDIT
,MTH_RATE_CUTOFF
,WK_TAX_CREDIT
,CERT_START_DATE
,CERT_END_DATE
,WK_MTH_INDICATOR
,CERT_DATE
,LAST_NAME
,FIRST_NAME
,ADDRESS_LINE1
,ADDRESS_LINE2
,ADDRESS_LINE3
,PROCESSED_FLAG
,PROCESSED_DATE
,TOT_PAY_TO_DATE
,TOT_TAX_TO_DATE
,EXEMPTION_INDICATOR
,STD_RATE_OF_TAX
,HIGHER_RATE_OF_TAX
--
,CD_YRLY_TAX_CRED
,CD_YRLY_TAX_RATE_1
,CD_YRLY_TAX_RATE_2
,CD_MTHLY_TAX_RATE_2
,CD_WKLY_TAX_RATE_2
,CD_TAX_RATE_3
,CD_YRLY_TAX_RATE_3
,CD_MTHLY_TAX_RATE_3
,CD_WKLY_TAX_RATE_3
,CD_TAX_RATE_4
,CD_YRLY_TAX_RATE_4
,CD_MTHLY_TAX_RATE_4
,CD_WKLY_TAX_RATE_4
,CD_TAX_RATE_5
,IN_EXEMPT_USC
,CD_TOTAL_USC_PAY_TODATE
,CD_TOTAL_USC_TAX_TODATE
,CD_USC_RATE_1
,AM_USC_YRLY_CUTOFF_1
,AM_USC_MTHLY_CUTOFF_1
,AM_USC_WKLY_CUTOFF_1
,CD_USC_RATE_2
,AM_USC_YRLY_CUTOFF_2
,AM_USC_MTHLY_CUTOFF_2
,AM_USC_WKLY_CUTOFF_2
,CD_USC_RATE_3
,AM_USC_YRLY_CUTOFF_3
,AM_USC_MTHLY_CUTOFF_3
,AM_USC_WKLY_CUTOFF_3
,CD_USC_RATE_4
,AM_USC_YRLY_CUTOFF_4
,AM_USC_MTHLY_CUTOFF_4
,AM_USC_WKLY_CUTOFF_4
,CD_USC_RATE_5
--
)
VALUES
(
'PC'
,NULL
,l_pps_number
,l_employer_number
,l_wk_rate_cutoff
,l_works_number
,l_mth_tax_credit
,l_mth_rate_cutoff
,l_wk_tax_credit
,l_cert_start_date
,l_cert_end_date
,l_wk_mth_indicator
,l_cert_date
,l_last_name
,l_first_name
,l_address_line1
,l_address_line2
,l_address_line3
,l_processed_flag
,l_processed_date
,l_tot_pay_to_date
,l_tot_tax_to_date
,l_exemption_indicator
,l_std_rate_of_tax
,l_higher_rate_of_tax
--
,l_CD_YRLY_TAX_CRED
,l_CD_YRLY_TAX_RATE_1
,l_CD_YRLY_TAX_RATE_2
,l_CD_MTHLY_TAX_RATE_2
,l_CD_WKLY_TAX_RATE_2
,l_CD_TAX_RATE_3
,l_CD_YRLY_TAX_RATE_3
,l_CD_MTHLY_TAX_RATE_3
,l_CD_WKLY_TAX_RATE_3
,l_CD_TAX_RATE_4
,l_CD_YRLY_TAX_RATE_4
,l_CD_MTHLY_TAX_RATE_4
,l_CD_WKLY_TAX_RATE_4
,l_CD_TAX_RATE_5
,l_IN_EXEMPT_USC
,l_CD_TOTAL_USC_PAY_TODATE
,l_CD_TOTAL_USC_TAX_TODATE
,l_CD_USC_RATE_1
,l_AM_USC_YRLY_CUTOFF_1
,l_AM_USC_MTHLY_CUTOFF_1
,l_AM_USC_WKLY_CUTOFF_1
,l_CD_USC_RATE_2
,l_AM_USC_YRLY_CUTOFF_2
,l_AM_USC_MTHLY_CUTOFF_2
,l_AM_USC_WKLY_CUTOFF_2
,l_CD_USC_RATE_3
,l_AM_USC_YRLY_CUTOFF_3
,l_AM_USC_MTHLY_CUTOFF_3
,l_AM_USC_WKLY_CUTOFF_3
,l_CD_USC_RATE_4
,l_AM_USC_YRLY_CUTOFF_4
,l_AM_USC_MTHLY_CUTOFF_4
,l_AM_USC_WKLY_CUTOFF_4
,l_CD_USC_RATE_5
--
);
INSERT INTO PAY_IE_TAX_TRAILER_INTERFACE
(
RECORD_TYPE
,EMPLOYER_NUMBER
,RECORD_NO
,TOTAL_MTH_RATE_CUTOFF
,TOTAL_WK_RATE_CUTOFF
,TOTAL_MTH_TAX_CREDIT
,TOTAL_WK_TAX_CREDIT
--
,TC_TOT_YEARLY_TAX_CRED
,TOT_YEARLY_TAX_RATE_1
,TOT_YEARLY_TAX_RATE_2
,TOT_MONTHLY_TAX_RATE_2
,TOT_WEEKLY_TAX_RATE_2
,TOT_YEARLY_TAX_RATE_3
,TOT_MONTHLY_TAX_RATE_3
,TOT_WEEKLY_TAX_RATE_3
,TOT_YEARLY_TAX_RATE_4
,TOT_MONTHLY_TAX_RATE_4
,TOT_WEEKLY_TAX_RATE_4
,TOT_YEARLY_USC_RATE_1
,TOT_YEARLY_USC_RATE_2
,TOT_YEARLY_USC_RATE_3
,TOT_YEARLY_USC_RATE_4
--
)
VALUES
(
'TC'
,l_employer_number
,l_record_no
,l_total_mth_rate_cutoff
,l_total_wk_rate_cutoff
,l_total_mth_tax_credit
,l_total_wk_tax_credit
--
,l_TC_TOT_YEARLY_TAX_CRED
,l_TOT_YEARLY_TAX_RATE_1
,l_TOT_YEARLY_TAX_RATE_2
,l_TOT_MONTHLY_TAX_RATE_2
,l_TOT_WEEKLY_TAX_RATE_2
,l_TOT_YEARLY_TAX_RATE_3
,l_TOT_MONTHLY_TAX_RATE_3
,l_TOT_WEEKLY_TAX_RATE_3
,l_TOT_YEARLY_TAX_RATE_4
,l_TOT_MONTHLY_TAX_RATE_4
,l_TOT_WEEKLY_TAX_RATE_4
,l_TOT_YEARLY_USC_RATE_1
,l_TOT_YEARLY_USC_RATE_2
,l_TOT_YEARLY_USC_RATE_3
,l_TOT_YEARLY_USC_RATE_4
--
);
SELECT COUNT(PBS.MTH_TAX_CREDIT) count_mth_taxcredit
, SUM (NVL(PBS.MTH_RATE_CUTOFF,0)) sum_mth_rate_cutoff
, SUM (NVL(PBS.WK_RATE_CUTOFF,0)) sum_wk_rate_cutoff
, SUM(NVL(PBS.MTH_TAX_CREDIT,0)) sum_mth_tax_credit
, SUM(NVL(PBS.WK_TAX_CREDIT,0)) sum_wk_tax_credit
FROM PAY_IE_TAX_HEADER_INTERFACE phs
, PAY_IE_TAX_BODY_INTERFACE pbs
WHERE PHS.EMPLOYER_NUMBER = PBS.EMPLOYER_NUMBER
AND PHS.TAX_YEAR = p_tax_year
AND PBS.EMPLOYER_NUMBER = p_employer_number;
SELECT PTS.RECORD_NO count_emp_recno
, NVL(PTS.TOTAL_MTH_RATE_CUTOFF,0) total_mth_cutoff
, NVL(PTS.TOTAL_WK_RATE_CUTOFF,0) total_wk_cutoff
, NVL(PTS.TOTAL_MTH_TAX_CREDIT,0) total_mth_credit
, NVL(PTS.TOTAL_WK_TAX_CREDIT,0) total_wk_credit
FROM PAY_IE_TAX_HEADER_INTERFACE phs
, PAY_IE_TAX_TRAILER_INTERFACE pts
WHERE PTS.EMPLOYER_NUMBER = p_employer_number
AND PHS.TAX_YEAR = p_tax_year;
Delete from pay_ie_tax_error;
UPDATE PAY_IE_TAX_BODY_INTERFACE
SET EMPLOYER_NUMBER =
(SELECT EMPLOYER_NUMBER
FROM PAY_IE_TAX_HEADER_INTERFACE);
update pay_ie_tax_body_interface
set process_flag = 'Y'
where EMPLOYER_NUMBER = p_employer_number;
INSERT INTO pay_ie_tax_error ( pps_number
, employee_number
, full_name
, payroll_name
, error_stack_message
, error_message
, request_id
, error_date)
VALUES (0
, NULL
, NULL
, NULL
, l_error_stack
, l_error
, l_request_id
, sysdate);
INSERT INTO pay_ie_tax_error ( pps_number
, employee_number
, full_name
, payroll_name
, error_stack_message
, error_message
, request_id
, error_date)
VALUES (0
, NULL
, NULL
, NULL
, l_error_stack
, l_error
, l_request_id
, sysdate);
select per.person_id person_id
,per.NATIONAL_IDENTIFIER pps_number
,count(distinct(asg.ASSIGNMENT_NUMBER)) no_of_assg
from per_all_assignments_f asg,
per_all_people_f per,
pay_all_payrolls_f pay,
pay_ie_tax_body_interface tbi,
per_periods_of_service pps
where per.national_identifier = tbi.pps_number
--AND pay.payroll_id = nvl(p_payroll_id,pay.payroll_id)
--AND asg.payroll_id = pay.payroll_id
AND per.person_id = asg.person_id
AND tbi.process_flag = 'Y'
AND asg.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
AND asg.effective_end_date >= trunc(tbi.cert_start_date)
AND per.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
AND per.effective_end_date >= trunc(tbi.cert_start_date)
and asg.period_of_service_id=pps.period_of_service_id
and pps.person_id=per.person_id
--and pps.date_start <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
and pps.period_of_service_id in (select max(pps1.period_of_service_id) from per_periods_of_service pps1 where pps1.person_id=pps.person_id and pps1.date_start <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy'))
--AND pay.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
--AND pay.effective_end_date >= trunc(tbi.cert_start_date)
group by per.person_id,per.NATIONAL_IDENTIFIER;
Select 1 from per_all_people_f per
,pay_ie_tax_body_interface tbi
Where per.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
AND per.effective_end_date >= trunc(tbi.cert_start_date)
and per.national_identifier=p_pps_no
AND tbi.process_flag = 'Y'
and tbi.pps_number=per.national_identifier;
SELECT distinct per.employee_number employee_no_hr
, per.national_identifier pps_number_hr
, per.last_name last_name_hr
, per.first_name first_name_hr
, asg.assignment_id assignment_id
--, asg.effective_start_date effective_start_date
, hoi.org_information1 tax_district
, pay.payroll_name payroll_name_hr
, pay.payroll_id payroll_id -- 4878630
--, ppd.paye_details_id paye_details_id --4878630
--, ppd.object_version_number object_version_no --4878630
--, ppd.effective_start_date ppd_effective_start_date
, tbi.pps_number pps_number_int
, asg.assignment_number employee_no_int --5724436
, tbi.first_name first_name_int
, tbi.last_name last_name_int
, tbi.cert_start_date cert_start_date
, tbi.cert_end_date cert_end_date
, tbi.cert_date cert_date
, tbi.wk_tax_credit/100 wk_tax_credit
, tbi.mth_tax_credit/100 mth_tax_credit
, tbi.wk_rate_cutoff/100 wk_rate_cutoff
, tbi.mth_rate_cutoff/100 mth_rate_cutoff
-- Bug Fix 3500192
, tbi.wk_mth_indicator wk_mth_indicator
-- Bug Fix 4618981
, tbi.exemption_indicator exemption_indicator
, tbi.tot_tax_to_date/100 tot_tax_to_date
, tbi.tot_pay_to_date/100 tot_pay_to_date
, tbi.std_rate_of_tax std_rate_of_tax
, tbi.higher_rate_of_tax higher_rate_of_tax
FROM hr_organization_information hoi
, hr_organization_units hou
, per_all_assignments_f asg
, per_all_people_f per
, pay_all_payrolls_f pay
--, pay_ie_paye_details_f ppd
, pay_ie_tax_body_interface tbi
, pay_ie_tax_header_interface thi
WHERE per.person_id = asg.person_id
AND per.national_identifier = tbi.pps_number
AND asg.business_group_id = hou.business_group_id
AND hou.organization_id = hoi.organization_id
AND hoi.org_information_context = 'IE_EMPLOYER_INFO' -- For Employer changes 4369280
AND hoi.org_information2 = p_employer_number
AND pay.payroll_id = nvl(p_payroll_id,pay.payroll_id)
AND asg.payroll_id = pay.payroll_id
--AND asg.assignment_id = ppd.assignment_id
AND per.national_identifier = p_pps_number
-- Bug Fix 3500192
-- added for multiple assignment issue 5894942
AND asg.assignment_number = tbi.works_number
AND thi.employer_number = p_employer_number
AND thi.tax_year = p_tax_year
AND tbi.employer_number = thi.employer_number
AND tbi.process_flag = 'Y'
AND asg.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
AND asg.effective_end_date >= trunc(tbi.cert_start_date)
AND per.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
AND per.effective_end_date >= trunc(tbi.cert_start_date)
AND pay.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
AND pay.effective_end_date >= trunc(tbi.cert_start_date);
SELECT distinct per.employee_number employee_no_hr
, per.national_identifier pps_number_hr
, per.last_name last_name_hr
, per.first_name first_name_hr
, asg.assignment_id assignment_id
--, asg.effective_start_date effective_start_date
, hoi.org_information1 tax_district
, pay.payroll_name payroll_name_hr
, pay.payroll_id payroll_id -- 4878630
--, ppd.paye_details_id paye_details_id --4878630
--, ppd.object_version_number object_version_no --4878630
--, ppd.effective_start_date ppd_effective_start_date
, tbi.pps_number pps_number_int
, asg.assignment_number employee_no_int --5724436
, tbi.first_name first_name_int
, tbi.last_name last_name_int
, tbi.cert_start_date cert_start_date
, tbi.cert_end_date cert_end_date
, tbi.cert_date cert_date
, tbi.wk_tax_credit/100 wk_tax_credit
, tbi.mth_tax_credit/100 mth_tax_credit
, tbi.wk_rate_cutoff/100 wk_rate_cutoff
, tbi.mth_rate_cutoff/100 mth_rate_cutoff
-- Bug Fix 3500192
, tbi.wk_mth_indicator wk_mth_indicator
-- Bug Fix 4618981
, tbi.exemption_indicator exemption_indicator
, tbi.tot_tax_to_date/100 tot_tax_to_date
, tbi.tot_pay_to_date/100 tot_pay_to_date
, tbi.std_rate_of_tax std_rate_of_tax
, tbi.higher_rate_of_tax higher_rate_of_tax
FROM hr_organization_information hoi
, hr_organization_units hou
, per_all_assignments_f asg
, per_all_people_f per
, pay_all_payrolls_f pay
--, pay_ie_paye_details_f ppd
, pay_ie_tax_body_interface tbi
, pay_ie_tax_header_interface thi,
per_periods_of_service pps
WHERE per.person_id = asg.person_id
AND per.national_identifier = tbi.pps_number
AND asg.business_group_id = hou.business_group_id
AND hou.organization_id = hoi.organization_id
AND hoi.org_information_context = 'IE_EMPLOYER_INFO' -- For Employer changes 4369280
AND hoi.org_information2 = p_employer_number
AND pay.payroll_id = nvl(p_payroll_id,pay.payroll_id)
AND asg.payroll_id = pay.payroll_id
--AND asg.assignment_id = ppd.assignment_id
AND per.national_identifier = p_pps_number
-- Bug Fix 3500192
-- added for multiple assignment issue 5894942
--AND asg.assignment_number = tbi.works_number
AND thi.employer_number = p_employer_number
AND thi.tax_year = p_tax_year
AND tbi.employer_number = thi.employer_number
AND tbi.process_flag = 'Y'
and asg.period_of_service_id=pps.period_of_service_id
and pps.person_id=per.person_id
--and pps.date_start <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
and pps.period_of_service_id in (select max(pps1.period_of_service_id) from per_periods_of_service pps1 where pps1.person_id=pps.person_id and pps1.date_start <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy'))
AND asg.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
AND asg.effective_end_date >= trunc(tbi.cert_start_date)
AND per.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
AND per.effective_end_date >= trunc(tbi.cert_start_date)
AND pay.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
AND pay.effective_end_date >= trunc(tbi.cert_start_date);
select pps_number, works_number from pay_ie_tax_body_interface
minus
SELECT distinct per.national_identifier pps_number, asg.assignment_number works_number
FROM hr_organization_information hoi
, hr_organization_units hou
, per_all_assignments_f asg
, per_all_people_f per
, pay_all_payrolls_f pay
--, pay_ie_paye_details_f ppd
, pay_ie_tax_body_interface tbi
, pay_ie_tax_header_interface thi
WHERE per.person_id = asg.person_id
AND asg.business_group_id = hou.business_group_id
AND hou.organization_id = hoi.organization_id
AND hoi.org_information_context = 'IE_EMPLOYER_INFO'
AND hoi.org_information2 = p_employer_number
AND pay.payroll_id = nvl(p_payroll_id,pay.payroll_id)
AND asg.payroll_id = pay.payroll_id
AND per.national_identifier = tbi.pps_number
AND asg.assignment_number = tbi.works_number
AND thi.employer_number = p_employer_number
AND thi.tax_year = p_tax_year
AND tbi.employer_number = thi.employer_number
AND tbi.process_flag = 'Y'
AND asg.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
AND asg.effective_end_date >= trunc(tbi.cert_start_date)
AND per.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
AND per.effective_end_date >= trunc(tbi.cert_start_date)
AND pay.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
AND pay.effective_end_date >= trunc(tbi.cert_start_date); */
select distinct pps_number pps_number,works_number,last_name,first_name
from pay_ie_tax_body_interface;
select count(*) from pay_ie_paye_details_f where
assignment_id = p_assignment_id;
select fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) aa
from pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = p_assignment_id
and to_number(to_char(ppa.effective_date,'YYYY')) = p_tax_year;
select distinct ppd.*
from per_all_people_f papf,
per_all_assignments_f paaf,
pay_ie_paye_details_f ppd
where papf.national_identifier = p_ppsn
and papf.person_id = paaf.person_id
and paaf.assignment_id = p_assignment_id
and paaf.assignment_id = ppd.assignment_id
and paaf.assignment_number = p_assignment_number
AND paaf.effective_start_date <= to_date('31/12/'||to_char(p_cert_start_date,'YYYY'),'dd/mm/yyyy')
AND paaf.effective_end_date >= trunc(p_cert_start_date)
AND papf.effective_start_date <= to_date('31/12/'||to_char(p_cert_start_date,'YYYY'),'dd/mm/yyyy')
AND papf.effective_end_date >= trunc(p_cert_start_date)
AND ppd.effective_start_date <= to_date('31/12/'||to_char(p_cert_start_date,'YYYY'),'dd/mm/yyyy')
AND ppd.effective_end_date >= trunc(p_cert_start_date);
SELECT pp.period_type
FROM pay_payrolls_f pp, per_assignments_f pa
WHERE pa.assignment_id = p_assignment_id
AND p_effective_date BETWEEN pa.effective_start_date AND pa.effective_end_date
AND pp.payroll_id = pa.payroll_id
AND p_effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
select global_value
from ff_globals_f
where global_name = p_global_name
and p_cert_date between effective_start_date and effective_end_date;
pay_ie_paye_pkg.update_paye_change_freq --4878630
(p_assignment_id => r_pay.assignment_id
,p_effective_date => l_effective_date -- Bug 6929566 -- r_pay.cert_date -- 5724436
,p_payroll_id => r_pay.payroll_id
,P_DATETRACK_UPDATE_MODE => 'UPDATE'
,p_tax_upload_flag => 'TU'
,p_tax_basis => l_tax_basis
,p_cert_start_date => r_pay.cert_start_date -- 17140460.6
,p_cert_end_date => r_pay.cert_end_date
,p_weekly_tax_credit => r_pay.wk_tax_credit
,p_monthly_tax_credit => r_pay.mth_tax_credit
,p_weekly_std_rate_cut_off => r_pay.wk_rate_cutoff
,p_monthly_std_rate_cut_off => r_pay.mth_rate_cutoff
,p_tax_deducted_to_date => r_pay.tot_tax_to_date
,p_pay_to_date => r_pay.tot_pay_to_date
,p_cert_date =>r_pay.cert_date);
/*Update interface table and set processed flag to 'Yes' to record that record has been updated or
inserted into payroll tables successfully using the row handler APIs.*/
IF (p_validate_mode = 'IE_VALIDATE_COMMIT') THEN
UPDATE pay_ie_tax_body_interface
SET processed_flag = 'Y'
WHERE pps_number = r_pay.pps_number_int;
FND_FILE.PUT_LINE(fnd_file.log, 'This record has been updated to a future date');
INSERT INTO pay_ie_tax_error ( pps_number
, employee_number
, full_name
, payroll_name
, tax_district
, error_stack_message
, error_message
, request_id
, error_date )
VALUES (r_pay.pps_number_hr
, r_pay.employee_no_hr
, r_pay.last_name_hr ||' '|| r_pay.first_name_hr
, r_pay.payroll_name_hr
, r_pay.tax_district
, l_error_stack
, l_error
, l_request_id
, sysdate);
INSERT INTO pay_ie_tax_error ( pps_number
, employee_number
, full_name
, payroll_name
, tax_district
, error_stack_message
, error_message
, request_id
, error_date )
VALUES (r_pay.pps_number_hr
, substr(r_pay.employee_no_int,1,12)
, r_pay.last_name_hr ||' '|| r_pay.first_name_hr
, r_pay.payroll_name_hr
, r_pay.tax_district
, l_error_stack
, l_error
, l_request_id
, sysdate);
INSERT INTO pay_ie_tax_error ( pps_number
, employee_number
, full_name
, payroll_name
, tax_district
, error_stack_message
, error_message
, request_id
, error_date )
VALUES (r_pay.pps_number_hr
, substr(r_pay.employee_no_int,1,12)
, r_pay.last_name_hr ||' '|| r_pay.first_name_hr
, r_pay.payroll_name_hr
, r_pay.tax_district
, l_error_stack
, l_error
, l_request_id
, sysdate);
INSERT INTO pay_ie_tax_error ( pps_number
, employee_number
, full_name
, payroll_name
, tax_district
, error_stack_message
, error_message
, request_id
, error_date )
VALUES (r_pay.pps_number_hr
, substr(r_pay.employee_no_int,1,12)
, r_pay.last_name_hr ||' '|| r_pay.first_name_hr
, r_pay.payroll_name_hr
, r_pay.tax_district
, l_error_stack
, l_error
, l_request_id
, sysdate);
INSERT INTO pay_ie_tax_error ( pps_number
, employee_number
, full_name
, payroll_name
, tax_district
, error_stack_message
, error_message
, request_id
, error_date )
VALUES (r_pay.pps_number_hr
, substr(r_pay.employee_no_int,1,12)
, r_pay.last_name_hr ||' '|| r_pay.first_name_hr
, r_pay.payroll_name_hr
, r_pay.tax_district
, l_error_stack
, l_error
, l_request_id
, sysdate);
INSERT INTO pay_ie_tax_error ( pps_number
, employee_number
, full_name
, payroll_name
, tax_district
, error_stack_message
, error_message
, request_id
, error_date )
VALUES (r_pay.pps_number_hr
, substr(r_pay.employee_no_int,1,12)
, r_pay.last_name_hr ||' '|| r_pay.first_name_hr
, r_pay.payroll_name_hr
, r_pay.tax_district
, l_error_stack
, l_error
, l_request_id
, sysdate);
INSERT INTO pay_ie_tax_error ( pps_number
, employee_number
, full_name
, payroll_name
, tax_district
, error_stack_message
, error_message
, request_id
, error_date )
VALUES (r_pay.pps_number_hr
, substr(r_pay.employee_no_int,1,12)
, r_pay.last_name_hr ||' '|| r_pay.first_name_hr
, r_pay.payroll_name_hr
, r_pay.tax_district
, l_error_stack
, l_error
, l_request_id
, sysdate);
/*Update interface table and set processed flag to 'No' to record that record has not been updated
or inserted into payroll tables*/
IF p_validate_mode = 'IE_VALIDATE_COMMIT' THEN
UPDATE pay_ie_tax_body_interface
SET processed_flag = 'N'
WHERE pps_number = r_pay.pps_number_int;
INSERT INTO pay_ie_tax_error ( pps_number
, employee_number
, full_name
, payroll_name
, tax_district
, error_stack_message
, error_message
, request_id
, error_date )
VALUES (r_pay.pps_number_hr
, substr(r_pay.employee_no_int,1,12)
, r_pay.last_name_hr ||' '|| r_pay.first_name_hr
, r_pay.payroll_name_hr
, r_pay.tax_district
, l_error_stack
, l_error
, l_request_id
, sysdate);
/* If user selects the mode to be 'Validate and Commit' then
p_validate_mode = 'IE_VALIDATE_COMMIT' then records are committed else records are rolled back */
IF (p_validate_mode = 'IE_VALIDATE_COMMIT') THEN
COMMIT;
INSERT INTO pay_ie_tax_error ( pps_number
, employee_number
, full_name
, payroll_name
, tax_district
, error_stack_message
, error_message
, request_id
, error_date)
VALUES (l_pps_number_hr
, substr(l_employee_number_hr,1,12)
, l_last_name_hr ||' '|| l_first_name_hr
, l_payroll_name_hr
, l_tax_district
, l_error_stack
, l_error
, l_request_id
, sysdate
);
INSERT INTO pay_ie_tax_error ( pps_number
, employee_number
, full_name
, payroll_name
, tax_district
, error_stack_message
, error_message
, request_id
, error_date)
VALUES (l_pps_number_hr
, substr(l_employee_number_hr,1,12)
, l_last_name_hr ||' '||l_first_name_hr
, l_payroll_name_hr
, l_tax_district
, l_error_stack
, l_error
, l_request_id
, sysdate);
SELECT assignment_id
, tax_basis
, info_source
, weekly_tax_credit
, weekly_std_rate_cut_off
, monthly_tax_credit
, monthly_std_rate_cut_off
FROM pay_ie_paye_details_f
WHERE paye_details_id = p_paye_details_id;
SELECT meaning
FROM hr_lookups
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code;