[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PAY_NO_UNION_DUES.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER_ID')
,PAY_NO_UNION_DUES.GET_PARAMETER(legislative_parameters,'ARCHIVE')
,PAY_NO_UNION_DUES.GET_PARAMETER(legislative_parameters,'THIRD_PARTY_PAYEE')
,fnd_date.canonical_to_date(PAY_NO_UNION_DUES.GET_PARAMETER(legislative_parameters,'FROM_DATE'))
,fnd_date.canonical_to_date(PAY_NO_UNION_DUES.GET_PARAMETER(legislative_parameters,'TO_DATE'))
,effective_date
,business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
select hou.name ,hoi.org_information1
from hr_organization_units hou
,hr_organization_information hoi
where hou.organization_id = l_leg_emp_id
and hoi.organization_id = l_leg_emp_id
and hoi.org_information_context = to_char('NO_LEGAL_EMPLOYER_DETAILS');
select distinct haou.name name from hr_all_organization_units haou
where haou.ORGANIZATION_ID=l_payee_org;
select count(*)
from pay_action_information
where action_information_category = 'EMEA REPORT DETAILS'
and action_information1 = 'PYNOTAXLEVYA'
and action_context_id = pactid;
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';
select to_char(l_effective_date,'RRRR') into l_bimonth_year from dual;
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
,prrv.result_value payee_id -- bug 14483053
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_from_date and l_to_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
-- To pick the terminated assignments.
AND ppa2.effective_date between asg.EFFECTIVE_START_DATE and asg.EFFECTIVE_END_DATE
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 IN ('C','S') -- 10229512
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_from_date and l_to_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 = 'Union Dues'
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 ;
SELECT pay_assignment_actions_s.NEXTVAL INTO l_actid FROM dual;
select to_number(ACTION_INFORMATION_ID) , to_number(ACTION_INFORMATION4),to_number(ACTION_INFORMATION30)
from pay_action_information
where ACTION_INFORMATION_CATEGORY = 'EMEA REPORT INFORMATION'
and ACTION_INFORMATION1 = 'PYNOUNDUEA'
and ACTION_CONTEXT_TYPE = 'AAP'
and ACTION_CONTEXT_ID = p_assignment_action_id
and EFFECTIVE_DATE = p_effective_date ;
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 --Union Member Number
,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 = 'Union Dues'
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'
--Bug 4661167 fix - Taking the 'Union Due Amount' instead of pay value
AND inpv1.NAME = 'Union Due Amount'
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 = 'Union Member Number'
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 = '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 = '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
AND prrv5.result_value = p_payee_id; -- bug 14483053
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;
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';
SELECT payroll_action_id
INTO l_payroll_action_id
FROM pay_assignment_actions
WHERE assignment_action_id = p_assignment_action_id ;
select name into l_third_party_name from hr_organization_units where organization_id = l_third_party_id ;
select pay_balance_pkg.get_value(pay_no_emp_cont.get_defined_balance_id('Subject to Union Dues','_ASG_PTD'),
l_main_asg_act_id)
into l_tax_dedn_basis
from dual;
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 --Union Member Number (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)
select pai.action_information3 employer,pai.action_information4 orgnumber,
pai.action_information5 from_date,pai.action_information6 to_date1,
pai.effective_date || ' (' ||pai.action_context_id||')' archiver
from pay_action_information pai
where to_char(pai.action_context_id)=l_payroll_action_id;
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,
Unionmemberno,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 Unionmemberno
,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,
TaxDeductionBasis,Unionmemberno,percentage,amount
order by thirdparty_id,order_name;
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;
SELECT org_information5
INTO p_global_id
FROM hr_organization_information
WHERE org_information_context = 'NO_THIRD_PARTY_PAYMENT'
AND organization_id = p_organization_id;
SELECT fffunc.cn (decode (data_type, 'N'
, global_value, NULL))
INTO p_global_value
FROM ff_globals_f glb
, pay_payroll_actions ppa
WHERE glb.global_id = p_global_id
AND ppa.payroll_action_id = p_payroll_action_id
AND ppa.effective_date
BETWEEN glb.effective_start_date
AND glb.effective_end_date;