APPS.PAY_NL_ATS_REPORT SQL Statements

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

Line: 28

	select  /*+ ORDERED */ pap.full_name Employee_name,
		hou.name Employer_name,
	 	hoi.org_information4 Tax_registration_number,
	 	pap.person_id Person_Id,
		paa.assignment_id Assignment_Id,
	 	pap.employee_number,
	 	paa.assignment_number,
	 	to_char(pap.Date_Of_Birth,'DD/MM/YYYY') Date_Of_Birth,
		ppos.date_start Date_Start,
		nvl(ppos.actual_termination_date,lp_tax_year_end_date) Date_End,
		to_char(greatest(ppos.date_start,lp_tax_year_start_date),'DD/MM/YYYY')||' - '||to_char(least(nvl(ppos.actual_termination_date,lp_tax_year_end_date),lp_tax_year_end_date),'DD/MM/YYYY') Period_Of_Service,
	 	pap.national_identifier SOFI_number,
	 	DECODE(SUBSTR(pai.action_information10,2,1),1,hr_general.decode_lookup('NL_TAX_TABLE','1'),2,hr_general.decode_lookup('NL_TAX_TABLE','2'),'') Wage_Tax_Table,
	 	decode(substr(pai.action_information9,1,1),'1',lp_yes,lp_no) Wage_Tax_Discount1,
	 	'(' || substr(pai.action_information9,2,2)||'/'||substr(pai.action_information9,4,2)||'/'||to_char(lp_tax_year_end_date,'YYYY') || ')' Date1,
	 	decode(substr(pai.action_information9,6,1),'1',lp_yes,lp_no) Wage_Tax_Discount2,
	 	'(' || substr(pai.action_information9,7,2)||'/'||substr(pai.action_information9,9,2)||'/'||to_char(lp_tax_year_end_date,'YYYY') || ')' Date2,
	 	decode(substr(pai.action_information9,11,1),'1',lp_yes,lp_no) Wage_Tax_Discount3,
	 	'(' || substr(pai.action_information9,12,2)||'/'||substr(pai.action_information9,14,2)||'/'||to_char(lp_tax_year_end_date,'YYYY') || ')' Date3,
  --11886537 begins
		--NVL(pai.action_information18,pai.action_information4) Taxable_Income,
	 	--pai.action_Information5 Deducted_Wage_Tax,
	 	--pai.action_information8 Labour_Tax_Reduction,
		NVL(fnd_number.canonical_to_number(pai.action_information18),fnd_number.canonical_to_number(pai.action_information4)) Taxable_Income,
	 	fnd_number.canonical_to_number(pai.action_Information5) Deducted_Wage_Tax,
	 	fnd_number.canonical_to_number(pai.action_information8) Labour_Tax_Reduction,
  --11886537 ends
	 	decode(substr(pai.action_information14,1,1),'1',lp_yes,2,lp_yes,3,lp_yes,lp_no) Insured_For_WAO,
	 	--decode(substr(pai.action_information14,2,1),'1',lp_yes,2,lp_yes,3,lp_yes,lp_no) Insured_For_ZFW,
		--pai.action_information15  ZVW_Cont,			--11886537
	 	fnd_number.canonical_to_number(pai.action_information15)  ZVW_Cont,           --11886537
		substr(pai.action_information12,13,1) Company_Car,
  --11886537 begins
	 	--pai.action_information17  Private_Use_Car,
	 	--pai.action_information16  Net_Expense_Allowance,
		--pai.action_information19  ZVW_Basis,
		--pai.action_information20  Value_Private_Use_Car,
		--pai.action_information21  Saved_Amount_LSS,
		--pai.action_information22  Employer_Child_Care,
		--pai.action_information23  Allowance_on_Disability,
		--pai.action_information24  Applied_LCLD,
		fnd_number.canonical_to_number(pai.action_information17)  Private_Use_Car,
	 	fnd_number.canonical_to_number(pai.action_information16)  Net_Expense_Allowance,
		fnd_number.canonical_to_number(pai.action_information19)  ZVW_Basis,
		fnd_number.canonical_to_number(pai.action_information20)  Value_Private_Use_Car,
		fnd_number.canonical_to_number(pai.action_information21)  Saved_Amount_LSS,
		fnd_number.canonical_to_number(pai.action_information22)  Employer_Child_Care,
		fnd_number.canonical_to_number(pai.action_information23)  Allowance_on_Disability,
		fnd_number.canonical_to_number(pai.action_information24)  Applied_LCLD,
  --11886537 ends
		pai.action_information25  User_Bal_String
	 from
                pay_assignment_actions assact,
                pay_action_information pai,
                per_all_assignments_f paa,
                per_all_people_f pap,
                per_periods_of_service ppos,
                hr_organization_units hou,
                hr_organization_information hoi
	 where
 	 	pai.action_context_type = 'AAP'
		and assact.payroll_action_id = lp_archive_action
		and pai.action_context_id = assact.assignment_action_id
 	 	and pai.action_information_category = 'NL ATS EMPLOYEE DETAILS'
	        and hoi.org_information_context = 'NL_ORG_INFORMATION'
	 	and pap.person_id = nvl(p_person_id,pap.person_id)
		and ppos.person_id = pap.person_id
		and ppos.date_start <= lp_Tax_Year_End_Date
		--and nvl(ppos.actual_termination_date, lp_Tax_Year_End_Date) >= lp_Tax_Year_Start_Date
	 	and pai.action_information1 = to_char(p_employer_id)
	 	and decode(pai.action_information_category,'NL ATS EMPLOYEE DETAILS',fnd_number.canonical_to_number(pai.action_information2),null) = nvl(p_person_id,pap.person_id)
	 	and decode(pai.action_information_category,'NL ATS EMPLOYEE DETAILS',fnd_number.canonical_to_number(pai.action_information3),null) = paa.assignment_id
	 	and pai.effective_date = lp_Tax_Year_End_Date
	and 	paa.effective_start_date =
		(
		SELECT MIN(asg.effective_start_date)
		FROM	per_all_assignments_f asg
		WHERE asg.assignment_id = paa.assignment_id
		and   asg.payroll_id is not NULL
		and   asg.effective_start_date <= least(nvl(ppos.actual_termination_date, lp_Tax_Year_End_Date), lp_Tax_Year_End_Date)
		and   nvl(asg.effective_end_date, lp_Tax_Year_End_Date) >= ppos.date_start

		)
	 	and lp_Tax_Year_End_Date between pap.effective_start_date and pap.effective_end_date
	 	and lp_Tax_Year_End_Date between hou.date_from and nvl(hou.date_to,hr_general.end_of_time)
	 	and paa.person_id = pap.person_id
	 	and pap.business_group_id = p_bg_id
	 	and paa.business_group_id = p_bg_id
	        and hou.organization_id = p_employer_id
	        and hoi.organization_id = p_employer_id
 	 	order by get_Address_Style(pap.person_id,lp_tax_year_end_date) desc, get_Post_Code(pap.person_id,lp_tax_year_end_date) asc, pap.person_id asc, paa.assignment_id asc;

Line: 121

	select
	pos.name
	from
	per_organization_structures pos,
	per_org_structure_versions posv
	where pos.organization_structure_id = posv.organization_structure_id
	and to_char(pos.organization_structure_id) IN (select org_information1
	from hr_organization_information hoi where hoi.org_information_context='NL_BG_INFO'
	and hoi.organization_id=lp_business_group_id)
	and lp_tax_year_end_date between posv.date_from and nvl(posv.date_to,hr_general.End_of_time);*/

Line: 133

	select name
	from hr_organization_units
	where organization_id = lp_org_id;

Line: 138

	select full_name from per_all_people_f
	where person_id = lp_person_id
	and lp_effective_date between effective_start_date and effective_end_date;

Line: 143

	select	hlc.loc_information14					house_number,
		hlc.loc_information15					house_no_add,
		hr_general.decode_lookup('NL_REGION',hlc.region_1)	street_name,
		hlc.address_line_1					address_line1,
		hlc.address_line_2					address_line2,
		hlc.address_line_3					address_line3,
		hlc.postal_code						postcode,
		hlc.town_or_city					city,
		pay_nl_general.get_country_name(hlc.country)		country,
		hlc.style						add_style
	from	hr_locations						hlc,
		hr_organization_units					hou
	where	hou.business_group_id = p_bg_id
	and	hou.organization_id = p_org_id
	and	hlc.location_id = hou.location_id;

Line: 160

	select	pad.add_information13					house_number,
		pad.add_information14					house_no_add,
		hr_general.decode_lookup('NL_REGION',pad.region_1)	street_name,
		pad.address_line1					address_line1,
		pad.address_line2					address_line2,
		pad.address_line3					address_line3,
		pad.postal_code						postcode,
		pad.town_or_city					city,
		pay_nl_general.get_country_name(pad.country)		country,
		pad.style						add_style
	from	per_addresses						pad
	where	pad.person_id = p_person_id
	and	p_effective_date between pad.date_from and nvl(pad.date_to,hr_general.end_of_time)
	and	pad.primary_flag = 'Y';

Line: 176

	select	hou.organization_id leg_emp_id,
		hoi.org_information1 leg_tax_ref
	from	hr_organization_units hou,
		hr_organization_information hoi,
		hr_organization_information hoi1,
		per_all_assignments_f paa
	where	paa.assignment_id = p_assignment_id
	and	hou.organization_id = nvl(paa.establishment_id,-1)
	and	hoi.organization_id = hou.organization_id
	and	hoi1.organization_id = hou.organization_id
	and	hoi1.org_information_context = 'CLASS'
	and	hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
	and	hoi1.org_information2 = 'Y'
	and	hoi.org_information_context = 'NL_LE_TAX_DETAILS'
	and	hoi.org_information1 IS NOT NULL
	and	hoi.org_information2 IS NOT NULL
	and 	paa.effective_start_date =
		(
		SELECT MIN(asg.effective_start_date)
		FROM per_all_assignments_f asg
		WHERE asg.assignment_id = paa.assignment_id
		and   asg.effective_start_date <= p_tax_year_end_date
		and   nvl(asg.effective_end_date, p_tax_year_end_date) >= p_tax_year_start_date

		);

Line: 291

	vUserBalVal.DELETE;

Line: 311

		select	max(ppa.payroll_action_id)
		into	l_archive_action
		from	pay_payroll_actions ppa
		where	ppa.report_qualifier='NL'
		and	ppa.business_group_id=p_bg_id
		and	ppa.report_type='NL_TAXOFFICE_ARCHIVE'
		and	ppa.report_category='ARCHIVE'
		and	pay_nl_taxoffice_archive.get_parameter(ppa.legislative_parameters,'EMPLOYER_ID')=to_char(p_employer_id)
		and	effective_date between l_tax_year_start_date and l_effective_date;

Line: 415

 	PAY_NL_XDO_REPORT.vXMLTable.DELETE;

Line: 714

					vUserBalVal.DELETE;

Line: 1512

			vUserBalVal.DELETE;

Line: 1626

		vUserBalVal.DELETE;

Line: 1656

	select  pap.full_name Employee_name,
		hou.name Employer_name,
	 	hoi.org_information4 Tax_registration_number,
	 	pap.person_id Person_Id,
		paa.assignment_id Assignment_Id,
	 	pap.employee_number,
	 	paa.assignment_number,
	 	to_char(pap.Date_Of_Birth,'DD/MM/YYYY') Date_Of_Birth,
		ppos.date_start Date_Start,
		nvl(ppos.actual_termination_date,lp_tax_year_end_date) Date_End,
		to_char(greatest(ppos.date_start,lp_tax_year_start_date),'DD/MM/YYYY')||' - '||to_char(least(nvl(ppos.actual_termination_date,lp_tax_year_end_date),lp_tax_year_end_date),'DD/MM/YYYY') Period_Of_Service,
	 	pap.national_identifier SOFI_number,
	 	DECODE(SUBSTR(pai.action_information10,2,1),1,hr_general.decode_lookup('NL_TAX_TABLE','1'),2,hr_general.decode_lookup('NL_TAX_TABLE','2'),'') Wage_Tax_Table,
	 	decode(substr(pai.action_information9,1,1),'1',lp_yes,lp_no) Wage_Tax_Discount1,
	 	'(' || substr(pai.action_information9,2,2)||'/'||substr(pai.action_information9,4,2)||'/'||to_char(lp_tax_year_end_date,'YYYY') || ')' Date1,
	 	decode(substr(pai.action_information9,6,1),'1',lp_yes,lp_no) Wage_Tax_Discount2,
	 	'(' || substr(pai.action_information9,7,2)||'/'||substr(pai.action_information9,9,2)||'/'||to_char(lp_tax_year_end_date,'YYYY') || ')' Date2,
	 	decode(substr(pai.action_information9,11,1),'1',lp_yes,lp_no) Wage_Tax_Discount3,
	 	'(' || substr(pai.action_information9,12,2)||'/'||substr(pai.action_information9,14,2)||'/'||to_char(lp_tax_year_end_date,'YYYY') || ')' Date3,
	--11886537 begins
		--NVL(pai.action_information18,pai.action_information4) Taxable_Income,
	 	--pai.action_Information5 Deducted_Wage_Tax,
	 	--pai.action_information8 Labour_Tax_Reduction,
		NVL(fnd_number.canonical_to_number(pai.action_information18),fnd_number.canonical_to_number(pai.action_information4)) Taxable_Income,
	 	fnd_number.canonical_to_number(pai.action_Information5) Deducted_Wage_Tax,
	 	fnd_number.canonical_to_number(pai.action_information8) Labour_Tax_Reduction,
	--11886537 ends
	 	decode(substr(pai.action_information14,1,1),'1',lp_yes,2,lp_yes,3,lp_yes,lp_no) Insured_For_WAO,
	 	--decode(substr(pai.action_information14,2,1),'1',lp_yes,2,lp_yes,3,lp_yes,lp_no) Insured_For_ZFW,
        --pai.action_information15  ZVW_Cont,								 --11886537
	 	fnd_number.canonical_to_number(pai.action_information15)  ZVW_Cont,	 --11886537
		substr(pai.action_information12,13,1) Company_Car,
	--11886537 begins
		--pai.action_information17  Private_Use_Car,
	 	--pai.action_information16  Net_Expense_Allowance,
		--pai.action_information19  ZVW_Basis,
		--pai.action_information20  Value_Private_Use_Car,
		--pai.action_information21  Saved_Amount_LSS,
		--pai.action_information22  Employer_Child_Care,
		--pai.action_information23  Allowance_on_Disability,
		--pai.action_information24  Applied_LCLD,
	 	fnd_number.canonical_to_number(pai.action_information17)  Private_Use_Car,
	 	fnd_number.canonical_to_number(pai.action_information16)  Net_Expense_Allowance,
		fnd_number.canonical_to_number(pai.action_information19)  ZVW_Basis,
		fnd_number.canonical_to_number(pai.action_information20)  Value_Private_Use_Car,
		fnd_number.canonical_to_number(pai.action_information21)  Saved_Amount_LSS,
		fnd_number.canonical_to_number(pai.action_information22)  Employer_Child_Care,
		fnd_number.canonical_to_number(pai.action_information23)  Allowance_on_Disability,
		fnd_number.canonical_to_number(pai.action_information24)  Applied_LCLD,
	--11886537 ends
		pai.action_information25  User_Bal_String
	 from
                pay_assignment_actions assact,
                pay_action_information pai,
                per_all_assignments_f paa,
                per_all_people_f pap,
                per_periods_of_service ppos,
                hr_organization_units hou,
                hr_organization_information hoi
	 where
 	 	pai.action_context_type = 'AAP'
		and assact.payroll_action_id = lp_archive_action
		and pai.action_context_id = assact.assignment_action_id
 	 	and pai.action_information_category = 'NL ATS EMPLOYEE DETAILS'
	        and hoi.org_information_context = 'NL_ORG_INFORMATION'
	 	and pap.person_id = nvl(p_person_id,pap.person_id)
		and ppos.person_id = pap.person_id
		and ppos.date_start <= lp_Tax_Year_End_Date
		--and nvl(ppos.actual_termination_date, lp_Tax_Year_End_Date) >= lp_Tax_Year_Start_Date
	 	and pai.action_information1 = to_char(p_employer_id)
	 	and decode(pai.action_information_category,'NL ATS EMPLOYEE DETAILS',fnd_number.canonical_to_number(pai.action_information2),null) = nvl(p_person_id,pap.person_id)
	 	and decode(pai.action_information_category,'NL ATS EMPLOYEE DETAILS',fnd_number.canonical_to_number(pai.action_information3),null) = paa.assignment_id
	 	and pai.effective_date = lp_Tax_Year_End_Date
	and 	paa.effective_start_date =
		(
		SELECT MIN(asg.effective_start_date)
		FROM	per_all_assignments_f asg
		WHERE asg.assignment_id = paa.assignment_id
		and   asg.payroll_id is not NULL
		and   asg.effective_start_date <= least(nvl(ppos.actual_termination_date, lp_Tax_Year_End_Date), lp_Tax_Year_End_Date)
		and   nvl(asg.effective_end_date, lp_Tax_Year_End_Date) >= ppos.date_start

		)
	 	and lp_Tax_Year_End_Date between pap.effective_start_date and pap.effective_end_date
	 	and lp_Tax_Year_End_Date between hou.date_from and nvl(hou.date_to,hr_general.end_of_time)
	 	and paa.person_id = pap.person_id
	 	and pap.business_group_id = p_bg_id
	 	and paa.business_group_id = p_bg_id
	        and hou.organization_id = p_employer_id
	        and hoi.organization_id = p_employer_id
 	 	order by get_Address_Style(pap.person_id,lp_tax_year_end_date) desc, get_Post_Code(pap.person_id,lp_tax_year_end_date) asc, pap.person_id asc, paa.assignment_id asc;

Line: 1749

	select
	pos.name
	from
	per_organization_structures pos,
	per_org_structure_versions posv
	where pos.organization_structure_id = posv.organization_structure_id
	and to_char(pos.organization_structure_id) IN (select org_information1
	from hr_organization_information hoi where hoi.org_information_context='NL_BG_INFO'
	and hoi.organization_id=lp_business_group_id)
	and lp_tax_year_end_date between posv.date_from and nvl(posv.date_to,hr_general.End_of_time);*/

Line: 1761

	select name
	from hr_organization_units
	where organization_id = lp_org_id;

Line: 1766

	select full_name from per_all_people_f
	where person_id = lp_person_id
	and lp_effective_date between effective_start_date and effective_end_date;

Line: 1771

	select	hlc.loc_information14					house_number,
		hlc.loc_information15					house_no_add,
		hr_general.decode_lookup('NL_REGION',hlc.region_1)	street_name,
		hlc.address_line_1					address_line1,
		hlc.address_line_2					address_line2,
		hlc.address_line_3					address_line3,
		hlc.postal_code						postcode,
		hlc.town_or_city					city,
		pay_nl_general.get_country_name(hlc.country)		country,
		hlc.style						add_style
	from	hr_locations						hlc,
		hr_organization_units					hou
	where	hou.business_group_id = p_bg_id
	and	hou.organization_id = p_org_id
	and	hlc.location_id = hou.location_id;

Line: 1788

	select	pad.add_information13					house_number,
		pad.add_information14					house_no_add,
		hr_general.decode_lookup('NL_REGION',pad.region_1)	street_name,
		pad.address_line1					address_line1,
		pad.address_line2					address_line2,
		pad.address_line3					address_line3,
		pad.postal_code						postcode,
		pad.town_or_city					city,
		pay_nl_general.get_country_name(pad.country)		country,
		pad.style						add_style
	from	per_addresses						pad
	where	pad.person_id = p_person_id
	and	p_effective_date between pad.date_from and nvl(pad.date_to,hr_general.end_of_time)
	and	pad.primary_flag = 'Y';

Line: 1804

	select	hou.organization_id leg_emp_id,
		hoi.org_information1 leg_tax_ref
	from	hr_organization_units hou,
		hr_organization_information hoi,
		hr_organization_information hoi1,
		per_all_assignments_f paa
	where	paa.assignment_id = p_assignment_id
	and	hou.organization_id = nvl(paa.establishment_id,-1)
	and	hoi.organization_id = hou.organization_id
	and	hoi1.organization_id = hou.organization_id
	and	hoi1.org_information_context = 'CLASS'
	and	hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
	and	hoi1.org_information2 = 'Y'
	and	hoi.org_information_context = 'NL_LE_TAX_DETAILS'
	and	hoi.org_information1 IS NOT NULL
	and	hoi.org_information2 IS NOT NULL
	and 	paa.effective_start_date =
		(
		SELECT MIN(asg.effective_start_date)
		FROM per_all_assignments_f asg
		WHERE asg.assignment_id = paa.assignment_id
		and   asg.effective_start_date <= p_tax_year_end_date
		and   nvl(asg.effective_end_date, p_tax_year_end_date) >= p_tax_year_start_date

		);

Line: 1919

	vUserBalVal.DELETE;

Line: 1939

		select	max(ppa.payroll_action_id)
		into	l_archive_action
		from	pay_payroll_actions ppa
		where	ppa.report_qualifier='NL'
		and	ppa.business_group_id=p_bg_id
		and	ppa.report_type='NL_TAXOFFICE_ARCHIVE'
		and	ppa.report_category='ARCHIVE'
		and	pay_nl_taxoffice_archive.get_parameter(ppa.legislative_parameters,'EMPLOYER_ID')=to_char(p_employer_id)
		and	effective_date between l_tax_year_start_date and l_effective_date;

Line: 2043

 	PAY_NL_XDO_REPORT.vXMLTable.DELETE;

Line: 2342

					vUserBalVal.DELETE;

Line: 3140

			vUserBalVal.DELETE;

Line: 3254

		vUserBalVal.DELETE;

Line: 3277

	SELECT program_name
	INTO l_file_name
	FROM fnd_lobs
	WHERE file_id = p_file_id;

Line: 3282

	-- the delete will ensure that the patch is rerunnable
	DELETE FROM per_gb_xdo_templates
	WHERE file_name = l_file_name AND
	effective_start_date = l_start_date AND
	effective_end_date = l_end_date;

Line: 3288

	INSERT INTO per_gb_xdo_templates
	(file_id,
	file_name,
	file_description,
	effective_start_date,
	effective_end_date)
	SELECT p_file_id, l_file_name, 'Template for year 0001-4712',
	l_start_date, l_end_date
	FROM fnd_lobs
	WHERE file_id = p_file_id;

Line: 3376

		SELECT	pad.style INTO l_address_style
		FROM	per_addresses pad
		WHERE	pad.person_id = p_person_id
		AND	pad.primary_flag = 'Y'
		AND	p_effective_date between pad.date_from and nvl(pad.date_to,hr_general.end_of_time);

Line: 3411

		SELECT	pad.postal_code INTO l_post_code
		FROM	per_addresses pad
		WHERE	pad.person_id = p_person_id
		AND	pad.primary_flag = 'Y'
		AND	p_effective_date between pad.date_from and nvl(pad.date_to,hr_general.end_of_time);