[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PAY_FI_ARCHIVE_DPSA.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER_ID')
,PAY_FI_ARCHIVE_DPSA.GET_PARAMETER(legislative_parameters,'LOCAL_UNIT_ID')
,PAY_FI_ARCHIVE_DPSA.GET_PARAMETER(legislative_parameters,'YEAR_RPT')
,PAY_FI_ARCHIVE_DPSA.GET_PARAMETER(legislative_parameters,'TRANSACTION_TYPE')
,PAY_FI_ARCHIVE_DPSA.GET_PARAMETER(legislative_parameters,'DEDUCTIONS_SS') DEDUCTIONS_SS
,PAY_FI_ARCHIVE_DPSA.GET_PARAMETER(legislative_parameters,'ARCHIVE')
,effective_date
,business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT o1.name , hoi2.ORG_INFORMATION1
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =l_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_local_unit_id
AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='FI_LOCAL_UNIT_DETAILS';
SELECT o1.name ,hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION13
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =l_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_legal_emp_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='FI_LEGAL_EMPLOYER_DETAILS' ;
SELECT hoi4.ORG_INFORMATION2 contact_person , hoi3.ORG_INFORMATION2 phone
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi3
, hr_organization_information hoi4
WHERE o1.business_group_id =l_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_legal_emp_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND hoi3.organization_id (+)= o1.organization_id
AND hoi3.ORG_INFORMATION_CONTEXT (+)='ORG_CONTACT_DETAILS'
AND hoi3.org_information1 (+)= 'PHONE'
AND hoi4.organization_id (+)= o1.organization_id
AND hoi4.ORG_INFORMATION_CONTEXT (+)='ORG_CONTACT_DETAILS'
AND hoi4.org_information1 (+)= 'PERSON' ;
SELECT hoi4.ORG_INFORMATION2 contact_person , hoi3.ORG_INFORMATION2 phone
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi3
, hr_organization_information hoi4
WHERE o1.business_group_id =l_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_local_unit_id
AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND hoi3.organization_id (+)= o1.organization_id
AND hoi3.ORG_INFORMATION_CONTEXT (+)='ORG_CONTACT_DETAILS'
AND hoi3.org_information1 (+)= 'PHONE'
AND hoi4.organization_id (+)= o1.organization_id
AND hoi4.ORG_INFORMATION_CONTEXT (+)='ORG_CONTACT_DETAILS'
AND hoi4.org_information1 (+)= 'PERSON' ;
p_sql := '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 = 'PYFIDPSA'
AND action_context_id = p_payroll_action_id;
SELECT as1.person_id person_id,
act.assignment_id assignment_id,
act.assignment_action_id run_action_id,
act1.assignment_action_id prepaid_action_id
FROM pay_payroll_actions ppa
,pay_payroll_actions appa
,pay_payroll_actions appa2
,pay_assignment_actions act
,pay_assignment_actions act1
,pay_action_interlocks pai
,per_all_assignments_f as1
,hr_soft_coding_keyflex hsck
,pay_run_result_values TARGET
,pay_run_results RR
WHERE ppa.payroll_action_id = p_payroll_action_id
AND appa.effective_date BETWEEN l_canonical_start_date
AND l_canonical_end_date
AND as1.person_id BETWEEN p_start_person
AND p_end_person
AND appa.action_type IN ('R','Q')
-- Payroll Run or Quickpay Run
AND act.payroll_action_id = appa.payroll_action_id
AND act.source_action_id IS NULL -- Master Action
AND as1.assignment_id = act.assignment_id
-- Commenting Code to Include Terminated Assignments
-- AND ppa.effective_date BETWEEN as1.effective_start_date
-- AND as1.effective_end_date
AND act.action_status = 'C' -- Completed
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status = 'C' -- Completed
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U')
AND appa2.effective_date BETWEEN l_canonical_start_date
AND l_canonical_end_date
-- Prepayments or Quickpay Prepayments
AND hsck.SOFT_CODING_KEYFLEX_ID=as1.SOFT_CODING_KEYFLEX_ID
AND hsck.segment2 = to_char(p_local_unit_id)
AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
AND act.TAX_UNIT_ID = p_legal_employer_id
and TARGET.run_result_id = RR.run_result_id
AND (( RR.assignment_action_id
in ( Select act2.assignment_action_id
from pay_assignment_actions act2
Where act2.source_action_id=act.assignment_action_id
AND act2.action_status = 'C' -- Completed
AND act2.payroll_action_id = act.payroll_action_id))
or
(RR.assignment_action_id=act.assignment_action_id))
and RR.status in ('P','PA')
ORDER BY as1.person_id , act.assignment_id ;
SELECT as1.person_id person_id,
act.assignment_id assignment_id,
act.assignment_action_id run_action_id,
act1.assignment_action_id prepaid_action_id
FROM pay_payroll_actions ppa
,pay_payroll_actions appa
,pay_payroll_actions appa2
,pay_assignment_actions act
,pay_assignment_actions act1
,pay_action_interlocks pai
,per_all_assignments_f as1
,pay_run_result_values TARGET
,pay_run_results RR
WHERE ppa.payroll_action_id = p_payroll_action_id
AND appa.effective_date BETWEEN l_canonical_start_date
AND l_canonical_end_date
AND as1.person_id BETWEEN p_start_person
AND p_end_person
AND appa.action_type IN ('R','Q')
-- Payroll Run or Quickpay Run
AND act.payroll_action_id = appa.payroll_action_id
AND act.source_action_id IS NULL -- Master Action
AND as1.assignment_id = act.assignment_id
-- Commenting Code to Include Terminated Assignments
-- AND ppa.effective_date BETWEEN as1.effective_start_date
-- AND as1.effective_end_date
AND act.action_status = 'C' -- Completed
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status = 'C' -- Completed
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U')
AND appa2.effective_date BETWEEN l_canonical_start_date
AND l_canonical_end_date
-- Prepayments or Quickpay Prepayments
AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
AND act.TAX_UNIT_ID = p_legal_employer_id
and TARGET.run_result_id = RR.run_result_id
AND (( RR.assignment_action_id
in ( Select act2.assignment_action_id
from pay_assignment_actions act2
Where act2.source_action_id=act.assignment_action_id
AND act2.action_status = 'C' -- Completed
AND act2.payroll_action_id = act.payroll_action_id))
or
(RR.assignment_action_id=act.assignment_action_id))
and RR.status in ('P','PA')
ORDER BY as1.person_id , act.assignment_id;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
SELECT MAX( EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
FROM per_all_assignments paa
,pay_assignment_actions pac
WHERE pac.assignment_action_id = p_asg_act_id
AND paa.assignment_id = pac.assignment_id
AND paa.EFFECTIVE_START_DATE <= p_end_date
AND paa.EFFECTIVE_END_DATE > = p_start_date
AND assignment_status_type_id IN
(select assignment_status_type_id
from per_assignment_status_types
where per_system_status = 'ACTIVE_ASSIGN'
and active_flag = 'Y'
and (( legislation_code is null
and business_group_id is null)
OR (BUSINESS_GROUP_ID = p_business_group_id)));
SELECT pap.first_name first_name , pap.last_name last_name , pap. national_identifier , pap. person_id , pac.assignment_id,
pap.per_information1 place_residence , pap.business_group_id , pap.per_information23 fpin
FROM
pay_assignment_actions pac,
per_all_assignments_f assign,
per_all_people_f pap
WHERE pac.assignment_action_id = p_asg_act_id
AND assign.assignment_id = pac.assignment_id
AND assign.person_id = pap.person_id
AND pap.per_information_category = 'FI'
AND p_asg_effective_date BETWEEN assign.effective_start_date
AND assign.effective_end_date
AND p_asg_effective_date BETWEEN pap.effective_start_date
AND pap.effective_end_date;
SELECT ue.creator_id
FROM ff_user_entities ue,
ff_database_items di
WHERE di.user_name = csr_v_Balance_Name
AND ue.user_entity_id = di.user_entity_id
AND ue.legislation_code = 'FI'
AND ue.business_group_id is NULL
AND ue.creator_type = 'B';
SELECT ue.creator_id
FROM ff_user_entities ue,
ff_database_items di
WHERE di.user_name = csr_v_Balance_Name
AND ue.user_entity_id = di.user_entity_id
AND ue.legislation_code is NULL
AND ue.business_group_id = p_business_group_id
AND ue.creator_type = 'B';
SELECT REPLACE(UPPER(pbt.balance_name),' ' ,'_') balance_name
FROM pay_balance_types pbt , pay_balance_categories_f pbc
WHERE pbc.legislation_code='FI'
AND pbt.business_group_id =p_business_group_id
AND pbt.balance_category_id = pbc.balance_category_id
AND pbc.category_name = p_balance_category_name ;
SELECT action_context_id payroll_action_id ,action_information2 emp_type ,action_information3 emp_id
,action_information4 business_id ,action_information5 org_type ,action_information6 contact_person
,action_information7 phone ,action_information8 year ,action_information9 transact_type
FROM pay_action_information pai , pay_assignment_actions paa
WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information1 = 'PYFIDPSA'
AND pai.action_context_id = paa.payroll_action_id
AND paa.assignment_action_id = p_asg_act_id;
SELECT address_line1||' '||address_line2 address , postal_code , d_country
FROM per_addresses_v
WHERE ADDRESS_TYPE='FI_PR'
AND BUSINESS_GROUP_ID = p_business_group_id
AND PERSON_ID = p_person_id;
SELECT eev1.screen_entry_value screen_entry_value
FROM per_all_assignments_f asg1
,per_all_assignments_f asg2
,per_all_people_f per
,pay_element_links_f el
,pay_element_types_f et
,pay_input_values_f iv1
,pay_element_entries_f ee
,pay_element_entry_values_f eev1
WHERE asg1.assignment_id = p_assignment_id
AND per.person_id = asg1.person_id
AND asg2.person_id = per.person_id
AND asg2.primary_flag = 'Y'
AND et.element_name = 'Tax Card'
AND et.legislation_code = 'FI'
AND iv1.element_type_id = et.element_type_id
AND iv1.name = 'Tax Card Type'
AND el.business_group_id = per.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = asg2.assignment_id
AND ee.element_link_id = el.element_link_id
AND eev1.element_entry_id = ee.element_entry_id
AND eev1.input_value_id = iv1.input_value_id
AND asg1.effective_end_date > p_start_date
AND asg1.effective_start_date < p_end_date
AND per.effective_end_date > p_start_date
AND per.effective_start_date < p_end_date
AND asg2.effective_end_date > p_start_date
AND asg2.effective_start_date < p_end_date
AND ee.effective_end_date > p_start_date
AND ee.effective_start_date < p_end_date
AND ((eev1.effective_start_date < p_start_date
AND eev1.effective_end_date > p_start_date )
OR (eev1.effective_start_date BETWEEN p_start_date AND p_end_date
AND eev1.effective_end_date > p_end_date ));
SELECT COUNT(*)
FROM pay_action_information
WHERE action_information_category = 'EMEA REPORT INFORMATION'
AND action_context_type = 'AAP'
AND action_context_id= p_assignment_action_id
AND action_information1 =p_record_id
AND action_information2 = p_payment_type ;
SELECT territory_short_name territory_name , TERRITORY_CODE||' - '||territory_short_name territory_short_name
FROM fnd_territories_VL
WHERE TERRITORY_CODE=p_country_code;
SELECT LAST_DAY(TO_DATE('01'||LPAD(i,2,'0')||g_year,'DDMMYYYY')) , LPAD(i,2,'0')
INTO l_bal_date, l_month
FROM DUAL;
SELECT LAST_DAY(TO_DATE('01'||LPAD(i,2,'0')||g_year,'DDMMYYYY')),LPAD(i,2,'0')
INTO l_bal_date,l_month
FROM DUAL;
SELECT action_information2 emp_type ,action_information3 emp_id
,action_information4 business_id ,action_information5 org_type ,action_information6 contact_person
,action_information7 phone ,action_information8 year ,action_information9 transact_type
FROM pay_action_information pai
WHERE action_information_category = 'EMEA REPORT INFORMATION'
AND action_information1 = 'PYFIDPSA'
AND action_context_id = p_payroll_action_id;
SELECT substr(action_information4,1,1) payment_type , SUM(nvl(action_information9,0)) payment , SUM(nvl(action_information10,0)) tax , COUNT(*) num
,SUM(nvl(action_information12,0)) pretax_ded, SUM(nvl(action_information11,0)) pen_unemp_ins , SUM(nvl(action_information25,0)) bik
, SUM(nvl(action_information27,0) + nvl(action_information23,0)) CODE_670 ,SUM(nvl(action_information28,0)) CODE_631
FROM pay_action_information pai , pay_assignment_actions paa
WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_context_type= 'AAP'
AND pai.action_context_id= paa.assignment_action_id
AND paa.payroll_action_id = p_payroll_action_id
AND action_information3 ='VSPSERIE'
AND action_information4 <>'P2'
AND action_information29 IN ('1','3')
GROUP BY substr(action_information4 ,1,1);
SELECT substr(action_information4,1,1) payment_type , SUM(nvl(action_information9,0)) payment , SUM(nvl(action_information10,0)) tax , COUNT(*) num
,SUM(nvl(action_information12,0)) pretax_ded, SUM(nvl(action_information11,0)) pen_unemp_ins , SUM(nvl(action_information25,0)) bik
, SUM(nvl(action_information27,0) + nvl(action_information23,0)) CODE_670 ,SUM(nvl(action_information28,0)) CODE_631
FROM pay_action_information pai , pay_assignment_actions paa
WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_context_type= 'AAP'
AND pai.action_context_id= paa.assignment_action_id
AND paa.payroll_action_id = p_payroll_action_id
AND action_information3 ='VSPSERIE'
AND action_information4 ='P2'
AND action_information29 ='1'
GROUP BY substr(action_information4 ,1,1);
SELECT SUM(nvl(action_information15,0)) payment, SUM(nvl(action_information16,0)) tax , COUNT(*) num,
SUM(nvl(action_information19,0)) pretax_ded, SUM(nvl(action_information18,0)) bik
FROM pay_action_information pai , pay_assignment_actions paa
WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_context_type= 'AAP'
AND pai.action_context_id= paa.assignment_action_id
AND paa.payroll_action_id = p_payroll_action_id
AND action_information3 ='VSRAERIE'
AND NVL(action_information23,0) < 1
GROUP BY action_information3;
SELECT SUM(nvl(action_information15,0)) payment, SUM(nvl(action_information16,0)) tax , COUNT(*) num,
SUM(nvl(action_information19,0)) pretax_ded, SUM(nvl(action_information18,0)) bik
FROM pay_action_information pai , pay_assignment_actions paa
WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_context_type= 'AAP'
AND pai.action_context_id= paa.assignment_action_id
AND paa.payroll_action_id = p_payroll_action_id
AND action_information3 ='VSRAERIE'
AND NVL(action_information23,0) > 0
GROUP BY action_information3;
SELECT pai.action_information30
FROM pay_action_information pai , pay_assignment_actions paa
WHERE paa.payroll_action_id = p_payroll_action_id
AND pai.action_context_id= paa.assignment_action_id
AND pai.action_context_type= 'AAP'
AND pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information1 = 'PYFIDPSA'
AND pai.action_information3 ='VSPSERIE'
AND action_information29 IN ('1','3')
ORDER BY pai.action_information2 , action_information29 , action_information15
FOR UPDATE OF pai.action_information30;
SELECT pai.action_information30
FROM pay_action_information pai , pay_assignment_actions paa
WHERE paa.payroll_action_id = p_payroll_action_id
AND pai.action_context_id= paa.assignment_action_id
AND pai.action_context_type= 'AAP'
AND pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information1 = 'PYFIDPSA'
AND pai.action_information3 ='VSRAERIE'
ORDER BY pai.action_information2
FOR UPDATE OF pai.action_information30;
SELECT *
FROM pay_action_information pai
WHERE pai.action_context_id= p_payroll_action_id
AND pai.action_context_type= 'PA'
AND pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information1 = 'PYFIDPSA'
AND pai.action_information2 = 'VSPSVYSL'
ORDER BY pai.action_information4
FOR UPDATE OF pai.action_information30;
SELECT *
FROM pay_action_information pai
WHERE pai.action_context_id= p_payroll_action_id
AND pai.action_context_type= 'PA'
AND pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information1 = 'PYFIDPSA'
AND pai.action_information2 = 'VSPSVYHT'
FOR UPDATE OF pai.action_information30;
SELECT pai.action_information30
FROM pay_action_information pai , pay_assignment_actions paa
WHERE paa.payroll_action_id = p_payroll_action_id
AND pai.action_context_id= paa.assignment_action_id
AND pai.action_context_type= 'AAP'
AND pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information1 = 'PYFIDPSA'
AND pai.action_information3 = 'VSPSTUKI'
ORDER BY pai.action_information2
FOR UPDATE OF pai.action_information30;
UPDATE pay_action_information pai
SET pai.action_information30 =l_end_code
WHERE CURRENT OF csr_VSPSERIE;
UPDATE pay_action_information pai
SET pai.action_information30 =l_end_code
WHERE CURRENT OF csr_VSRAERIE;
UPDATE pay_action_information
SET action_information30 =l_end_code
WHERE CURRENT OF csr_VSPSTUKI;
UPDATE pay_action_information
SET action_information30 =l_end_code
WHERE CURRENT OF csr_VSPSVYSL;
UPDATE pay_action_information
SET action_information30 =l_end_code
WHERE CURRENT OF csr_VSPSVYHT;