eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PAY_IE_TAX_VAL SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 40


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;

Line: 54


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;

Line: 87


   Delete from pay_ie_tax_error;

Line: 97


 UPDATE PAY_IE_TAX_BODY_INTERFACE
  SET EMPLOYER_NUMBER =
  		(SELECT EMPLOYER_NUMBER
  		FROM PAY_IE_TAX_HEADER_INTERFACE);

Line: 136


  	update pay_ie_tax_body_interface
  	set process_flag = 'Y'
  	where EMPLOYER_NUMBER = p_employer_number;

Line: 225


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);

Line: 251


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);

Line: 286


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;

Line: 312


    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;

Line: 324


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);

Line: 390


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);

Line: 462


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); */

Line: 494


select distinct pps_number pps_number,works_number,last_name,first_name
from pay_ie_tax_body_interface;

Line: 499


select count(*) from pay_ie_paye_details_f where
assignment_id = p_assignment_id;

Line: 505


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;

Line: 517


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);

Line: 536


   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;

Line: 548


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;

Line: 877


		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);

Line: 929


/*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;

Line: 1143


		FND_FILE.PUT_LINE(fnd_file.log, 'This record has been updated to a future date');

Line: 1154


		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);

Line: 1196


		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);

Line: 1239


		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);

Line: 1283


		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);

Line: 1326


		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);

Line: 1369


		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);

Line: 1412


		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);

Line: 1437


		/*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;

Line: 1467


		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);

Line: 1539


/* 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;

Line: 1555


	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
	);

Line: 1602


	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);

Line: 1692


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;

Line: 1704


SELECT meaning
FROM hr_lookups
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code;