DBA Data[Home] [Help]

APPS.PAY_NO_TAX_LEVY SQL Statements

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

Line: 68

	SELECT 	 PAY_NO_TAX_LEVY.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER_ID')
		,PAY_NO_TAX_LEVY.GET_PARAMETER(legislative_parameters,'BIMONTH_TERM')
		,PAY_NO_TAX_LEVY.GET_PARAMETER(legislative_parameters,'ARCHIVE')
		,PAY_NO_TAX_LEVY.GET_PARAMETER(legislative_parameters,'THIRD_PARTY_PAYEE')
		,effective_date
		,business_group_id
	FROM  pay_payroll_actions
	WHERE payroll_action_id = p_payroll_action_id;
Line: 107

select hou.name
from hr_organization_units          hou
where hou.organization_id = l_leg_emp_id;
Line: 113

select hoi.org_information1
from hr_organization_information    hoi
where  hoi.organization_id = l_leg_emp_id
and   hoi.org_information_context = to_char('NO_LEGAL_EMPLOYER_DETAILS');
Line: 121

select distinct haou.name name from hr_all_organization_units haou
where haou.ORGANIZATION_ID=l_payee_org;
Line: 126

select count(*)
from   pay_action_information
where  action_information_category = 'EMEA REPORT DETAILS'
and    action_information1         = 'PYNOTAXLEVYA'
and    action_context_id           = pactid;
Line: 134

select to_char(l_effective_date,'RRRR') from dual;
Line: 142

	 sqlstr := 'SELECT DISTINCT person_id
		FROM  per_people_f ppf
		     ,pay_payroll_actions ppa
		WHERE ppa.payroll_action_id = :payroll_action_id
		AND   ppa.business_group_id = ppf.business_group_id
		ORDER BY ppf.person_id';
Line: 259

	SELECT
	assact.ASSIGNMENT_ID		asg_id
	,assact.assignment_action_id	asg_act_id
	,assact.TAX_UNIT_ID		tax_unit_id
	,prr.LOCAL_UNIT_ID		local_unit_id
	--,prr.JURISDICTION_CODE	tax_mun_id
	,(select act_con.CONTEXT_VALUE  from pay_action_contexts act_con,ff_contexts con
	where con.CONTEXT_NAME = 'JURISDICTION_CODE'
	AND act_con.CONTEXT_ID = con.CONTEXT_ID
	AND act_con.ASSIGNMENT_ACTION_ID = assact.ASSIGNMENT_ACTION_ID
	AND act_con.ASSIGNMENT_ID = assact.ASSIGNMENT_ID) tax_mun_id

	FROM
	pay_assignment_actions	assact
	,pay_assignment_actions	assact1
	,pay_payroll_actions	ppa
	,pay_payroll_actions	ppa2
	,pay_payroll_actions	ppa3
	,per_all_assignments_f	asg
	,pay_element_types_f    ele
	,pay_run_results	prr
	,pay_run_result_values	prrv
	,pay_input_values_f	inpv
	,pay_action_interlocks  pai


	WHERE -- initial conditions

	ppa.payroll_action_id = p_payroll_action_id

	-- for 2nd pay payroll act table
	AND ppa2.date_earned between l_start_date and l_end_date
	AND ppa2.action_type IN ('R','Q')  -- Payroll Run or Quickpay Run

	-- for asg act table
	AND assact.PAYROLL_ACTION_ID = ppa2.PAYROLL_ACTION_ID
	AND assact.TAX_UNIT_ID = p_leg_emp_id
	AND assact.action_status = 'C'  -- Completed
	AND assact.source_action_id  IS NOT NULL -- Not Master Action


	-- for asg table
	AND assact.ASSIGNMENT_ID = asg.ASSIGNMENT_ID
	--AND ppa.date_earned between asg.EFFECTIVE_START_DATE and asg.EFFECTIVE_END_DATE
	--AND ppa.effective_date between asg.EFFECTIVE_START_DATE and asg.EFFECTIVE_END_DATE
	AND ppa2.effective_date between asg.EFFECTIVE_START_DATE and asg.EFFECTIVE_END_DATE -- Bug 5116907 fix
	AND asg.person_id   BETWEEN p_start_person AND p_end_person

	-- for run results
	AND assact.ASSIGNMENT_ACTION_ID = prr.ASSIGNMENT_ACTION_ID

	--for prepayments
	AND    assact1.action_status           = 'C' -- Completed
	AND    assact1.assignment_action_id    = pai.locking_action_id
	AND    assact1.payroll_action_id       = ppa3.payroll_action_id
	AND    ppa3.action_type            IN ('P','U')
	AND    ppa3.date_earned between l_start_date and l_end_date

	AND assact.ASSIGNMENT_ACTION_ID = pai.locked_action_id

	-- for element 'Wage Attachment Tax Levy'
	AND prr.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
	AND ele.ELEMENT_NAME = 'Wage Attachment Tax Levy'
	AND ele.LEGISLATION_CODE = 'NO'
	--AND ppa.date_earned between ele.EFFECTIVE_START_DATE and ele.EFFECTIVE_END_DATE
	AND ppa.effective_date between ele.EFFECTIVE_START_DATE and ele.EFFECTIVE_END_DATE

	AND prr.RUN_RESULT_ID = prrv.RUN_RESULT_ID
	AND inpv.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
	AND inpv.NAME = 'Third Party Payee'
	AND inpv.LEGISLATION_CODE = 'NO'
	--AND ppa.date_earned between inpv.EFFECTIVE_START_DATE and inpv.EFFECTIVE_END_DATE
	AND prrv.INPUT_VALUE_ID = inpv.INPUT_VALUE_ID
	--and haou.organization_id=prrv5.result_value
	AND prrv.result_value like nvl(to_char(p_payee_org),prrv.result_value)


	ORDER BY assact.assignment_id ;
Line: 340

 SELECT pay_assignment_actions_s.NEXTVAL  FROM   dual;
Line: 378

	select to_date(decode(l_bimonth_term,'01','JAN','02','MAR','03','MAY','04','JUL','05','SEP','06','NOV','00','JAN')
				||to_char(l_effective_date,'YY'),'MONYY') into l_start_date from dual;
Line: 383

	select last_day(to_date(decode(l_bimonth_term,'01','FEB','02','APR','03','JUN','04','AUG','05','OCT','06','DEC','DEC')
				||to_char(l_effective_date,'YY'),'MONYY')) into l_end_date from dual;
Line: 447

select to_number(ACTION_INFORMATION_ID) , to_number(ACTION_INFORMATION4)
,to_number(ACTION_INFORMATION3)
from pay_action_information
where ACTION_INFORMATION_CATEGORY = 'EMEA REPORT INFORMATION'
and   ACTION_INFORMATION1 = 'PYNOTAXLEVYA'
and   ACTION_CONTEXT_TYPE = 'AAP'
and   ACTION_CONTEXT_ID = p_assignment_action_id
and   EFFECTIVE_DATE = p_effective_date ;
Line: 464

SELECT
 per.last_name last_name
,per.first_name first_name
,per.order_name order_Name
,per.title per_title
,per.date_of_birth dob
,per.original_date_of_hire doh
,per.PERSON_ID			per_id
,per.NATIONAL_IDENTIFIER	per_ni
,per.EMPLOYEE_NUMBER		emp_no
,per.business_group_id		bg_id
,prrv1.result_value		res_val_1 -- Pay Value
,prrv2.result_value		res_val_2 -- Income Year
,prrv3.result_value		res_val_3 -- Deduction Percentage
,prrv4.result_value		res_val_4 -- Fixed Deduction Amount
,prrv5.result_value		res_val_5 -- Third Party Payee

FROM
pay_assignment_actions	assact
,pay_payroll_actions	ppa
,per_all_assignments_f	asg
,per_all_people_f	per
,pay_element_types_f    ele
,pay_input_values_f	inpv1
,pay_input_values_f	inpv2
,pay_input_values_f	inpv3
,pay_input_values_f	inpv4
,pay_input_values_f	inpv5
,pay_run_results	prr
,pay_run_result_values	prrv1
,pay_run_result_values	prrv2
,pay_run_result_values	prrv3
,pay_run_result_values	prrv4
,pay_run_result_values	prrv5


WHERE assact.ASSIGNMENT_ACTION_ID = p_assignment_action_id
AND assact.ASSIGNMENT_ID = asg.ASSIGNMENT_ID
AND ppa.date_earned between asg.EFFECTIVE_START_DATE and asg.EFFECTIVE_END_DATE
AND asg.PERSON_ID = per.PERSON_ID
AND ppa.date_earned between per.EFFECTIVE_START_DATE and per.EFFECTIVE_END_DATE
AND assact.PAYROLL_ACTION_ID = ppa.PAYROLL_ACTION_ID
AND assact.ASSIGNMENT_ACTION_ID = prr.ASSIGNMENT_ACTION_ID

-- for element 'Wage Attachment Tax Levy'
AND prr.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
AND ele.ELEMENT_NAME = 'Wage Attachment Tax Levy'
AND ele.LEGISLATION_CODE = 'NO'
AND ppa.date_earned between ele.EFFECTIVE_START_DATE and ele.EFFECTIVE_END_DATE

-- for input value 'Pay Value'
AND prr.RUN_RESULT_ID = prrv1.RUN_RESULT_ID
AND inpv1.NAME = 'Pay Value'
AND inpv1.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
AND inpv1.LEGISLATION_CODE = 'NO'
AND ppa.date_earned between inpv1.EFFECTIVE_START_DATE and inpv1.EFFECTIVE_END_DATE
AND prrv1.INPUT_VALUE_ID = inpv1.INPUT_VALUE_ID

-- for input value 'Income Year'
AND prr.RUN_RESULT_ID = prrv2.RUN_RESULT_ID
AND inpv2.NAME = 'Income Year'
AND inpv2.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
AND inpv2.LEGISLATION_CODE = 'NO'
AND ppa.date_earned between inpv2.EFFECTIVE_START_DATE and inpv2.EFFECTIVE_END_DATE
AND prrv2.INPUT_VALUE_ID = inpv2.INPUT_VALUE_ID

-- for input value 'Deduction Percentage'
AND prr.RUN_RESULT_ID = prrv3.RUN_RESULT_ID
AND inpv3.NAME = 'Deduction Percentage'
AND inpv3.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
AND inpv3.LEGISLATION_CODE = 'NO'
AND ppa.date_earned between inpv3.EFFECTIVE_START_DATE and inpv3.EFFECTIVE_END_DATE
AND prrv3.INPUT_VALUE_ID = inpv3.INPUT_VALUE_ID

-- for input value 'Fixed Deduction Amount'
AND prr.RUN_RESULT_ID = prrv4.RUN_RESULT_ID
AND inpv4.NAME = 'Fixed Deduction Amount'
AND inpv4.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
AND inpv4.LEGISLATION_CODE = 'NO'
AND ppa.date_earned between inpv4.EFFECTIVE_START_DATE and inpv4.EFFECTIVE_END_DATE
AND prrv4.INPUT_VALUE_ID = inpv4.INPUT_VALUE_ID

-- for input value 'Third Party Payee'
AND prr.RUN_RESULT_ID = prrv5.RUN_RESULT_ID
AND inpv5.NAME = 'Third Party Payee'
AND inpv5.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
AND inpv5.LEGISLATION_CODE = 'NO'
AND ppa.date_earned between inpv5.EFFECTIVE_START_DATE and inpv5.EFFECTIVE_END_DATE
AND prrv5.INPUT_VALUE_ID = inpv5.INPUT_VALUE_ID;
Line: 559

select  segment6
from pay_external_accounts	acc
,pay_org_payment_methods_f	pay_org
,hr_organization_information	hoi
where hoi.organization_id = p_organization_id
and hoi.org_information_context = 'NO_THIRD_PARTY_PAYMENT'
and pay_org.org_payment_method_id = hoi.org_information2
and pay_org.pmeth_information1 = 'DESTINATION'
and acc.external_account_id = pay_org.external_account_id;
Line: 573

select loc.address_line_1	line_1
,loc.address_line_2		line_2
,loc.address_line_3		line_3
,hr_general.decode_fnd_comm_lookup('NO_POSTAL_CODE',loc.postal_code) post_code
from hr_locations_all		loc
,hr_all_organization_units	hou
where hou.organization_id = p_organization_id
and loc.location_id = hou.location_id
and loc.style = 'NO';
Line: 585

SELECT payroll_action_id
FROM pay_assignment_actions
WHERE assignment_action_id = p_assignment_action_id ;
Line: 591

select name from hr_organization_units
where organization_id = l_third_party_id ;
Line: 596

select
pay_balance_pkg.get_value(pay_no_emp_cont.get_defined_balance_id('Tax Deduction Basis','_ASG_PTD'),
                          l_main_asg_act_id)
from dual;
Line: 681

	pay_action_information_api.update_action_information (
	 p_action_information_id        => l_action_info_id		-- in parameter
	,p_object_version_number        => l_ovn			-- in out parameter
	,p_action_information5          => l_third_party_id		--Third Party ID (Tax Collector ID)
	,p_action_information6          => l_third_party_name		--Third Party Name (Tax Collector's Name)
	,p_action_information7          => rec_loc_detail.line_1	--Third Party Address Line 1
	,p_action_information8          => rec_loc_detail.line_2	--Third Party Address Line 2
	,p_action_information9          => rec_loc_detail.line_3	--Third Party Address Line 3
	,p_action_information10         => rec_loc_detail.post_code	--Third Party Postal Code + City
	,p_action_information11         => l_third_party_dest_acc	--Third Party Destination Bank Account Number (Formatted)
	,p_action_information12         => rec_asg_detail.bg_id		--Business Group ID
	,p_action_information13         => rec_asg_detail.per_id	--PERSON_ID
	,p_action_information14         => rec_asg_detail.per_ni	--Person NATIONAL_IDENTIFIER
	,p_action_information15         => rec_asg_detail.last_name	--Person Lastname
	,p_action_information16         => rec_asg_detail.first_name	--Person Firstname
	,p_action_information17         => rec_asg_detail.order_name	--Person Ordername
	,p_action_information18         => rec_asg_detail.emp_no	--Person Employee number
	,p_action_information19         => rec_asg_detail.dob		--Person date of birth
	,p_action_information20         => rec_asg_detail.doh		--Person date of hiring
	,p_action_information21         => rec_asg_detail.per_title	--Person title
	,p_action_information22         => rec_asg_detail.res_val_2 	--Income Year (Input Value)
	,p_action_information23         => rec_asg_detail.res_val_3 	--Percentage (Input Value)
	,p_action_information24         => rec_asg_detail.res_val_4 	--Amount (Input Value)
	,p_action_information25         => rec_asg_detail.res_val_1 	--Deducted This Period (Input Value)
	,p_action_information26         => l_tax_dedn_basis		--Tax Deduction Basis (Balance Value)
	,p_action_information27         => NULL );
Line: 734

select  pai.action_information3 employer,pai.action_information4 orgnumber,
pai.action_information5 term,pai.action_information6 year,
pai.effective_date || ' (' ||pai.action_context_id||')' archiver
from pay_action_information pai
where to_char(pai.action_context_id)=l_payroll_action_id;
Line: 742

select thirdparty_id,thirdparty_name,thirdparty_address,
bankaccountno,ni,last_name,first_name,order_name,
emp_no,emp_dob,emp_doh,emp_title,
sum(TaxDeductionBasis) TaxDeductionBasis,
incomeyear,percentage,amount,
sum(deductedthisperiod) deductedthisperiod from
(select pai.action_information5 thirdparty_id
,pai.action_information6 thirdparty_name,
decode(pai.action_information7,null,'',',')
||pai.action_information7||
decode(pai.action_information8,null,'',',')
||pai.action_information8||
decode(pai.action_information9,null,'',',')
||pai.action_information9||
decode(pai.action_information10,null,'',',')
||pai.action_information10 thirdparty_address
,pai.action_information11 bankaccountno
,pai.action_information14 ni
,action_information15 last_name
,action_information16 first_name
,action_information17 order_name
,action_information18 emp_no
,action_information19 emp_dob
,action_information20 emp_doh
,action_information21 emp_title
,pai.action_information26 TaxDeductionBasis
,pai.action_information22 incomeyear
,pai.action_information23 percentage
,pai.action_information24 amount
,pai.action_information25 deductedthisperiod
from pay_action_information pai
where pai.action_information3=l_payroll_action_id)
group by
thirdparty_id,thirdparty_name,thirdparty_address,
bankaccountno,ni,last_name,first_name,order_name,
emp_no,emp_dob,emp_doh,emp_title,
incomeyear,percentage,amount
order by thirdparty_id,order_name;
Line: 798

	SELECT payroll_action_id
	INTO  l_payroll_action_id
	FROM pay_payroll_actions ppa,
	fnd_conc_req_summary_v fcrs,
	fnd_conc_req_summary_v fcrs1
	WHERE  fcrs.request_id = FND_GLOBAL.CONC_REQUEST_ID
	AND fcrs.priority_request_id = fcrs1.priority_request_id
	AND ppa.request_id between fcrs1.request_id  and fcrs.request_id
	AND ppa.request_id = fcrs1.request_id;