[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PAY_NO_SUPPORT_ORDER.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER_ID')
,PAY_NO_SUPPORT_ORDER.GET_PARAMETER(legislative_parameters,'ELEMENT_TYPE_ID')
,fnd_date.canonical_to_date(PAY_NO_SUPPORT_ORDER.GET_PARAMETER(legislative_parameters,'FROM_DATE'))
,fnd_date.canonical_to_date(PAY_NO_SUPPORT_ORDER.GET_PARAMETER(legislative_parameters,'TO_DATE'))
,PAY_NO_SUPPORT_ORDER.GET_PARAMETER(legislative_parameters,'THIRD_PARTY_PAYEE')
,PAY_NO_SUPPORT_ORDER.GET_PARAMETER(legislative_parameters,'ARCHIVE')
,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 element_name
FROM pay_element_types_f
WHERE ELEMENT_TYPE_ID = l_ele_type_id ;
SELECT nvl(REPORTING_NAME , ELEMENT_NAME)
FROM pay_element_types_f
WHERE ELEMENT_TYPE_ID = l_ele_type_id ;
select EEI_INFORMATION2 , EEI_INFORMATION3 , EEI_INFORMATION4
from pay_element_type_extra_info
where ELEMENT_TYPE_ID = l_ele_type_id ;
select EEI_INFORMATION2 , EEI_INFORMATION3 , EEI_INFORMATION4
from pay_element_type_extra_info
where ELEMENT_TYPE_ID = l_ele_type_id
and INFORMATION_TYPE = 'NO_EMPLOYEE_DEDUCTION_REPORT';
select INPUT_VALUE_ID
from pay_input_values_f
where ELEMENT_TYPE_ID = l_ele_type_id
AND NAME = l_iv_name ;
select
ipv1.INPUT_VALUE_ID ipv1_id -- Third Party Payee
,ipv2.INPUT_VALUE_ID ipv2_id -- Pay Value
,info.ELEMENT_TYPE_EXTRA_INFO_ID info_id
,info.EEI_INFORMATION5 def_bal_id
from pay_element_types_f ele
,pay_element_type_extra_info info
,pay_input_values_f ipv1
,pay_input_values_f ipv2
where ele.ELEMENT_TYPE_ID = p_ele_type_id
-- for pay_element_type_extra_info
AND info.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
AND info.INFORMATION_TYPE = 'NO_EMPLOYEE_DEDUCTION_REPORT'
-- for input value Third Party Payee
AND ipv1.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
AND ipv1.NAME = 'Third Party Payee'
-- for input value Pay Value
AND ipv2.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
AND ipv2.NAME = 'Pay Value' ;
select nvl((select eei_information1 from pay_element_type_extra_info petei
where petei.information_type='NO_ELEMENT_CODES'
and element_type_id = p_ele_type_id
and petei.eei_information2 = p_leg_emp_id
and rownum=1),
(select eei_information1 from pay_element_type_extra_info petei
where petei.information_type='NO_ELEMENT_CODES'
and element_type_id = p_ele_type_id
and eei_information2 is null
and rownum=1)) from dual;
select ipv1.INPUT_VALUE_ID ipv1_id -- Third Party Payee
,ipv2.INPUT_VALUE_ID ipv2_id -- Fixed Deduction Amount
,ipv3.INPUT_VALUE_ID ipv3_id -- Deduction Percentage
,ipv4.INPUT_VALUE_ID ipv4_id -- Reference Number
,ipv5.INPUT_VALUE_ID ipv5_id -- Pay Value
from pay_input_values_f ipv1
,pay_input_values_f ipv2
,pay_input_values_f ipv3
,pay_input_values_f ipv4
,pay_input_values_f ipv5
WHERE
-- for input value Third Party Payee
ipv1.ELEMENT_TYPE_ID = p_ele_type_id
AND ipv1.NAME = 'Third Party Payee'
-- for input value AMOUNT
AND ipv2.ELEMENT_TYPE_ID = p_ele_type_id
AND ipv2.NAME = 'Fixed Deduction Amount'
-- for input value PERCENTAGE
AND ipv3.ELEMENT_TYPE_ID = p_ele_type_id
AND ipv3.NAME = 'Deduction Percentage'
-- for input value REFERENCE NUMBER
AND ipv4.ELEMENT_TYPE_ID = p_ele_type_id
AND ipv4.NAME = 'Reference Number'
-- for input value PAY VALUE
AND ipv5.ELEMENT_TYPE_ID = p_ele_type_id
AND ipv5.NAME = 'Pay Value' ;
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 count(*) INTO l_count
FROM pay_action_information
WHERE action_information_category = 'EMEA REPORT DETAILS'
AND action_information1 = 'PYNOSUPORDA'
AND action_context_id = pactid;
select name into l_third_party_name from hr_organization_units where organization_id = l_third_party_id ;
/* SELECT pay_no_emp_cont.get_defined_balance_id('Total Pay','_ASG_PTD') INTO l_def_bal_id FROM dual ; */
SELECT pay_no_emp_cont.get_defined_balance_id('Wage Attachment Support Order Base','_ASG_PTD') INTO l_def_bal_id 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
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_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
-- 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 = '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 USER DEFINED DEDUCTION ELEMENTS
AND prr.ELEMENT_TYPE_ID = p_element_type_id
AND prr.RUN_RESULT_ID = prrv.RUN_RESULT_ID
AND prrv.INPUT_VALUE_ID = inpv.INPUT_VALUE_ID
AND inpv.ELEMENT_TYPE_ID = p_element_type_id
AND inpv.NAME = 'Third Party Payee'
AND prrv.result_value = nvl(p_third_party_id,prrv.result_value)
ORDER BY assact.assignment_id ;
SELECT act_con.CONTEXT_VALUE tax_mun_id
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 = p_assignment_action_id
AND act_con.ASSIGNMENT_ID = p_assignment_id ;
SELECT pay_assignment_actions_s.NEXTVAL INTO l_actid FROM dual;
select to_number(ACTION_INFORMATION_ID) action_info_id
,to_number(ACTION_INFORMATION4) main_asg_act_id
,to_number(ACTION_INFORMATION2) local_unit_id
,jurisdiction_code
from pay_action_information
where ACTION_INFORMATION_CATEGORY = 'EMEA REPORT INFORMATION'
and ACTION_INFORMATION1 = 'PYNOSUPORDA'
and ACTION_CONTEXT_TYPE = 'AAP'
and ACTION_CONTEXT_ID = p_assignment_action_id
and EFFECTIVE_DATE = p_effective_date
and TAX_UNIT_ID = p_leg_emp_id ;
select to_number(ACTION_INFORMATION10) ele_type_id -- Elemeny Type ID
,to_number(ACTION_INFORMATION13) ipv_third_party -- Input Value ID 1 = Third Party Payee
,to_number(ACTION_INFORMATION14) ipv_amount -- Input Value ID 2 = Amount
,to_number(ACTION_INFORMATION15) ipv_percent -- Input Value ID 3 = Percentage
,to_number(ACTION_INFORMATION16) ipv_ref_num -- Input Value ID 4 = Reference Number
,to_number(ACTION_INFORMATION17) ipv_pay_value -- Input Value ID 5 = Pay Value
,to_number(ACTION_INFORMATION18) def_bal_id -- Deduction Basis Balance : Defined Balance ID
from pay_action_information
where ACTION_INFORMATION_CATEGORY = 'EMEA REPORT DETAILS'
and ACTION_INFORMATION1 = 'PYNOSUPORDA'
and ACTION_CONTEXT_TYPE = 'PA'
and ACTION_CONTEXT_ID = p_payroll_action_id
and EFFECTIVE_DATE = p_effective_date
and TAX_UNIT_ID = p_leg_emp_id ;
SELECT
per.last_name per_last_name
,per.first_name per_first_name
,per.ORDER_NAME per_order_name
,per.PERSON_ID per_id
,per.NATIONAL_IDENTIFIER per_ni
,per.EMPLOYEE_NUMBER emp_no
,per.DATE_OF_BIRTH per_dob
,per.ORIGINAL_DATE_OF_HIRE per_doh
,per.TITLE per_title
,per.business_group_id bg_id
,prrv1.result_value res_val_1 -- Third Party Payee
,prrv2.result_value res_val_2 -- Pay Value
,prr.RUN_RESULT_ID run_res_id
,ppa.payroll_id payroll_id --payroll_id to set the context
,ppa.date_earned date_earned --date_earned to set the context
,prr.source_id original_entry_id --source_id to set the context
FROM
pay_assignment_actions assact
,pay_payroll_actions ppa
,per_all_assignments_f asg
,per_all_people_f per
,pay_run_results prr
,pay_run_result_values prrv1
,pay_run_result_values prrv2
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
AND prr.ELEMENT_TYPE_ID = p_ele_typ_id
-- for input value 'Third Party Payee'
AND prr.RUN_RESULT_ID = prrv1.RUN_RESULT_ID
AND prrv1.INPUT_VALUE_ID = p_ipvid_1
-- for input value 'Pay Value'
AND prr.RUN_RESULT_ID = prrv2.RUN_RESULT_ID
AND prrv2.INPUT_VALUE_ID = p_ipvid_2 ;
SELECT result_value
FROM pay_run_result_values
WHERE RUN_RESULT_ID = p_run_res_id
AND INPUT_VALUE_ID = p_inp_val_id ;
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(ele_info.def_bal_id , rec_info.main_asg_act_id)
into l_dedn_basis
from dual;
pay_action_information_api.update_action_information (
p_action_information_id => rec_info.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.per_last_name --Person Lastname
,p_action_information16 => rec_asg_detail.per_first_name --Person FirstName
,p_action_information17 => rec_asg_detail.per_order_name --Person Order Name
,p_action_information18 => rec_asg_detail.emp_no --Person Employee Number
,p_action_information19 => rec_asg_detail.per_dob --Person Date of Birth - DOB
,p_action_information20 => rec_asg_detail.per_doh --Person Date of Hire - DOH
,p_action_information21 => rec_asg_detail.per_title --Person Title
,p_action_information22 => l_ref_num --Reference Number
,p_action_information23 => l_percent --Percentage (Input Value)
,p_action_information24 => l_amt --Amount (Input Value)
,p_action_information25 => rec_asg_detail.res_val_2 --Deducted This Period (Input Value)
,p_action_information26 => l_dedn_basis --Deduction Basis (Balance Value)
);
select action_information3 employer
,action_information4 orgnumber
,action_information5 from_date
,action_information6 to_date
,action_information11 ele_name
,action_information12 ele_code
,action_information20 archived_on
from pay_action_information
where action_context_id = p_payroll_action_id ;
select action_information6 thirdparty_name
,action_information5 thirdparty_id
,', '||action_information7||', '
||action_information8||', '
||action_information9||', '
||action_information10 thirdparty_address
,action_information11 bankaccountno
,action_information14 emp_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
,action_information22 refno
,action_information23 percentage
,action_information24 amount
,action_information25 deductedthisperiod
,action_information26 deductionBasis
from pay_action_information pai
where action_information3 = to_char(p_payroll_action_id)
order by thirdparty_name , 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;