[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select substr
(
legislative_parameters,
instr
(
legislative_parameters,
p_token
) + (length(p_token) + 1),
instr
(
legislative_parameters,
' ',
instr
(
legislative_parameters,
p_token
)
)
-
(
instr
(
legislative_parameters,
p_token
) + length(p_token)
)
),
business_group_id
from pay_payroll_actions
where payroll_action_id = p_pact_id;
select org.org_information1,
org.org_information2,
org.org_information3,
org.org_information4,
org.org_information5,
org.org_information6
from hr_organization_information org
where org.org_information_context = p_context
and org.organization_id = p_bg_id;
select pbt.balance_name,
pbd.database_item_suffix,
pbt.legislation_code,
pdb.defined_balance_id
from pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
where pbt.balance_type_id = p_balance_type_id
and pbd.balance_dimension_id = p_balance_dimension_id
and pdb.balance_type_id = pbt.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id;
select pet.formula_id
from pay_element_types_f pet,
ff_formulas_f fff
where pet.element_type_id = p_element_type_id
and pet.formula_id = fff.formula_id
and fff.formula_name = 'ONCE_EACH_PERIOD'
and p_effective_date between fff.effective_start_date and fff.effective_end_date
and p_effective_date between pet.effective_start_date and pet.effective_end_date;
select once_each_period_flag
from pay_element_types_f pet
where pet.element_type_id = p_element_type_id
and p_effective_date between pet.effective_start_date and pet.effective_end_date;
select piv.uom
from pay_input_values_f piv
where piv.input_value_id = p_input_value_id
and p_effective_date between piv.effective_start_date and piv.effective_end_date;
select distinct pet.element_type_id element_type_id,
piv.input_value_id,
nvl(pet.reporting_name, pet.element_name) element_name,
pec.classification_name,
piv.uom
from pay_element_types_f pet,
pay_input_values_f piv,
pay_run_results prr,
pay_element_classifications pec,
pay_assignment_actions paa, -- Assignment Action of Prepayments
pay_assignment_actions rpaa, -- Assignment Action of Run
pay_action_interlocks pai,
pay_payroll_actions ppa -- Payroll Action of Prepayments
where pet.element_type_id = prr.element_type_id
and piv.element_type_id = pet.element_type_id
and piv.name = 'Pay Value'
and pet.classification_id = pec.classification_id
and pec.classification_name in
(
'Statutory Information',
'Normal Income',
'Statutory Deductions',
'Lump Sum Amounts',
'Allowances',
'Deductions',
'Information',
'Involuntary Deductions',
'Employer Contributions',
'Voluntary Deductions',
'Direct Payments',
'Fringe Benefits'
)
and pet.element_name not in ('ZA_Tax_Output', 'ZA_Tax_Output_2', 'ZA_Tax', 'ZA_Tax_2',
'ZA_Tax_3', 'ZA_Tax_4', 'ZA_Tax_5', 'ZA_Tax_D1', 'ZA_Tax_D2',
'ZA_Tax_D3', 'ZA_Tax_M', 'ZA_Tax_6')
and pec.legislation_code = 'ZA'
and prr.assignment_action_id = rpaa.assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and pai.locking_action_id = paa.assignment_action_id
and rpaa.assignment_action_id = pai.locked_action_id
and ppa.effective_date between pet.effective_start_date and pet.effective_end_date
and ppa.effective_date between piv.effective_start_date and piv.effective_end_date
and ppa.payroll_action_id = p_payroll_action_id;
select pdb.defined_balance_id
from pay_balance_types pbt,
pay_defined_balances pdb
where pdb.balance_type_id = pbt.balance_type_id
and pbt.balance_name = p_balance
and pdb.balance_dimension_id = (select balance_dimension_id
from pay_balance_dimensions
where dimension_name = p_dimension);
select distinct paei.aei_information7 legal_entity_id
from per_all_assignments_f paa,
per_assignment_extra_info paei
where paa.payroll_id = p_payroll_id
and p_effective_date between paa.effective_start_date
and paa.effective_end_date
and paa.assignment_id = paei.assignment_id
and paei.information_type = 'ZA_SPECIFIC_INFO'
and not exists (select 1
from per_all_assignments_f paa1
where paa1.payroll_id = p_payroll_id
and paa1.organization_id = paei.aei_information7
And p_effective_date between paa1.effective_start_date
and paa1.effective_end_date);
select hou.name legal_entity_name,
hl.address_line_1,
hl.address_line_2,
hl.address_line_3,
hl.town_or_city,
hl.region_1,
hl.region_2,
hl.region_3,
hl.postal_code,
hl.country,
hl.telephone_number_1
from hr_locations hl,
hr_organization_units hou
where hou.organization_id = p_legal_entity_id
and hou.location_id = hl.location_id;
procedure update_employee_information(
p_action_context_id in number
,p_assignment_id in number) is
cursor csr_get_archive_info(p_action_context_id number
,p_assignment_id number) is
select action_information_id,
effective_date,
object_version_number
from pay_action_information
where action_context_id = p_action_context_id
and action_context_type = 'AAP'
and assignment_id = p_assignment_id
and action_information_category = 'EMPLOYEE DETAILS';
select action_information_id,
action_information10, --region2 i.e. Postal same as residential address indicator
effective_date,
object_version_number
from pay_action_information
where action_context_id = p_action_context_id
and action_context_type = 'AAP'
and assignment_id = p_assignment_id
and action_information_category = 'ADDRESS DETAILS'
and action_information14 = 'Employee Address';
select address_line1 ee_unit_num
, address_line2 ee_complex
, address_line3 ee_street_num
, region_1 ee_street_name
, region_2 ee_suburb_district
, town_or_city ee_town_city
, postal_code ee_postal_code
from per_addresses ad,
per_all_assignments_f paf
where paf.assignment_id = p_assignment_id
and paf.person_id = ad.person_id
and g_archive_effective_date between date_from and nvl(date_to,to_date('31-12-4712','DD-MM-YYYY'))
and g_archive_effective_date between paf.effective_start_date and paf.effective_end_date
and ad.style = 'ZA_SARS'
and ad.address_type = 'ZA_RES';
select paei.aei_information7 legal_entity_id,
hou.name legal_entity_name,
hl.telephone_number_1
from per_assignment_extra_info paei,
hr_organization_units hou,
hr_locations hl
where paei.assignment_id = p_assignment_id
and paei.information_type = 'ZA_SPECIFIC_INFO'
and paei.aei_information7 = hou.organization_id
and hou.location_id = hl.location_id;
l_proc := g_package || 'update_employee_information';
pay_action_information_api.update_action_information(
p_action_information_id => l_action_information_id,
p_object_version_number => l_ovn,
p_action_information18 => l_legal_entity_name,
p_action_information25 => NULL
);
update pay_action_information
set tax_unit_id = l_legal_entity_id
where action_information_id = l_action_information_id;
pay_action_information_api.update_action_information(
p_action_information_id => rec_employee_address.action_information_id,
p_object_version_number => rec_employee_address.object_version_number,
p_action_information5 => rec_res_address.ee_unit_num,
p_action_information6 => rec_res_address.ee_complex,
p_action_information7 => rec_res_address.ee_street_num,
p_action_information8 => rec_res_address.ee_street_name,
p_action_information9 => rec_res_address.ee_suburb_district,
p_action_information10 => rec_res_address.ee_town_city,
p_action_information12 => rec_res_address.ee_postal_code
);
pay_action_information_api.update_action_information(
p_action_information_id => rec_employee_address.action_information_id,
p_object_version_number => rec_employee_address.object_version_number,
p_action_information10 => NULL
);
end update_employee_information;
select effective_date
from pay_payroll_actions
where payroll_action_id = pactid;
select piv.input_value_id
from pay_input_values_f piv,
pay_element_types_f pet
where piv.element_type_id = pet.element_type_id
and pet.legislation_code = 'ZA'
and pet.element_name = p_element_name
and piv.name = p_value_name;
select pact.payroll_action_id payroll_action_id,
pact.effective_date effective_date
from pay_payrolls_f ppf,
pay_payroll_actions pact -- Payroll Action of Prepayments
where pact.payroll_id = ppf.payroll_id
and pact.effective_date between ppf.effective_start_date and ppf.effective_end_date
and pact.payroll_id = nvl(p_payroll_id, pact.payroll_id)
and pact.consolidation_set_id = p_consolidation_id
and pact.effective_date between p_start_date and p_end_date
and
(
pact.action_type = 'P'
or
pact.action_type = 'U'
)
and pact.action_status = 'C';
update pay_payroll_actions
set payroll_id = l_payroll_id, consolidation_set_id = l_consolidation_set
where payroll_action_id=p_payroll_action_id;
hr_utility.set_location('Calling update_employee_information', 40);
update_employee_information
(
p_action_context_id => p_assactid,
p_assignment_id => p_assignment_id
);
hr_utility.set_location('Returned from update_employee_information', 50);
select max(person_id)
into l_person_id
from per_all_assignments_f
where assignment_id = p_assignment_id;
select max(per_information1)
into l_tax_ref_number
from per_all_people_f papf
where papf.person_id = l_person_id
and papf.current_employee_flag = 'Y'
and per_information_category = 'ZA'
and g_archive_effective_date between effective_start_date and effective_end_date; -- Bug 4204930
select peevf.screen_entry_value
into l_tax_status
from pay_element_entries_f peef,
pay_element_entry_values_f peevf
where peef.assignment_id = p_assignment_id
and peevf.input_value_id = g_tax_element_id
and peef.element_entry_id = peevf.element_entry_id
and peef.effective_start_date <= g_archive_effective_date -- Bug 3513520
and peef.effective_end_date >= g_archive_effective_date -- Bug 3513520
and peevf.effective_start_date = peef.effective_start_date; -- Bug 3513520
select meaning
into g_tax_status_meaning
from hr_lookups
where lookup_type = 'ZA_TAX_STATUS'
and application_id = 800
and lookup_code = l_tax_status;
select period_num, cut_off_date
into l_tax_period, l_pay_date
from per_time_periods
where time_period_id = p_time_period_id;
select decode(to_char(max(papf.effective_end_date), 'dd/mm/yyyy'), '31/12/4712', null, max(papf.effective_end_date))
into l_termination_date
from per_all_people_f papf
where papf.person_id = l_person_id
and papf.current_employee_flag = 'Y';
select prv.result_value result_value /* Modified for Bug#10242073 */
from pay_run_result_values prv,
pay_run_results prr
where prr.status in ('P', 'PA')
and prv.run_result_id = prr.run_result_id
and prr.assignment_action_id = p_assignment_action_id
and prr.element_type_id = p_element_type_id
and prv.input_value_id = p_input_value_id
and prv.result_value is not null;
select nvl(sum(fnd_number.canonical_to_number(prv.result_value)),0) result_value
from pay_run_result_values prv,
pay_run_results prr
where prr.status in ('P', 'PA')
and prv.run_result_id = prr.run_result_id
and prr.assignment_action_id in (select paa.assignment_action_id
from pay_assignment_actions paa,
pay_assignment_actions paa1
where paa.source_action_id =paa1.source_action_id
and paa1.assignment_action_id = p_assignment_action_id
)
and prr.element_type_id = p_element_type_id
and prv.input_value_id = p_input_value_id;
select element_type_id
from pay_element_types_f
where element_name = 'ZA_Tax_PAYE_Employer_Contribution_NTG'
and legislation_code='ZA'
and p_effective_date between effective_start_date and effective_end_date;
SELECT pet.element_type_id,
piv.input_value_id
FROM pay_input_values_f piv,
pay_element_types_f pet
WHERE piv.element_type_id = pet.element_type_id
AND pet.legislation_code = 'ZA'
AND pet.element_name = p_element_name
AND piv.name = p_value_name;
SELECT ppf.payroll_id
FROM pay_all_payrolls_f ppf
WHERE ppf.consolidation_set_id = p_consolidation_set_id
AND ppf.payroll_id = NVL(p_payroll_id,ppf.payroll_id)
AND p_effective_date BETWEEN
ppf.effective_start_date AND ppf.effective_end_date;
select pact.payroll_action_id payroll_action_id, -- Payroll Action of Prepayments
pact.effective_date effective_date, -- Effective Date of Prepayments
pact.date_earned date_earned,
pact.payroll_id,
ppf.payroll_name payroll_name,
ppf.period_type period_type,
pact.pay_advice_message payroll_message
from pay_payrolls_f ppf,
pay_payroll_actions pact -- Payroll Action of Prepayments
where pact.payroll_id = ppf.payroll_id
and pact.effective_date between ppf.effective_start_date and ppf.effective_end_date
and pact.payroll_id = nvl(p_payroll_id, pact.payroll_id)
and pact.consolidation_set_id = p_consolidation_id
and pact.effective_date between p_start_date and p_end_date
and (pact.action_type = 'P' or pact.action_type = 'U')
and pact.action_status = 'C'
and not exists
(
select null
from pay_action_information pai
where pai.action_context_id = pact.payroll_action_id -- Payroll Action of Prepayments
and pai.action_context_type = 'PA'
and pai.action_information_category = 'EMEA PAYROLL INFO'
);
select pact.payroll_action_id payroll_action_id,
pact.effective_date effective_date,
pact.date_earned date_earned,
pact.pay_advice_message payroll_message
from pay_payrolls_f ppf,
pay_payroll_actions pact -- Payroll Action of Run
where pact.payroll_id = ppf.payroll_id
and pact.effective_date between ppf.effective_start_date and ppf.effective_end_date
and pact.payroll_id = p_payroll_id
and pact.effective_date between p_start_date and p_end_date
and (pact.action_type = 'R' or pact.action_type = 'Q')
and pact.action_status = 'C'
and not exists
(
select null
from pay_action_information pai
where pai.action_context_id = pact.payroll_action_id -- FIX can't user payroll action id of Run
and pai.action_context_type = 'PA' -- should be PA of archiver
and pai.action_information_category = 'EMPLOYEE OTHER INFORMATION'
);
sqlstr := 'select distinct person_id
from per_people_f ppf,
pay_payroll_actions ppa
where ppa.payroll_action_id = :payroll_action_id
and ppf.business_group_id = ppa.business_group_id
order by ppf.person_id';
select paa_run.assignment_id assignment_id,
paa_run.assignment_action_id run_action_id,
paa_pre.assignment_action_id prepaid_action_id
from pay_payroll_actions ppa_pre, -- Payroll Action of Prepayment
pay_assignment_actions paa_pre, -- Assignment Action of Prepayment
pay_action_interlocks pai,
per_all_assignments_f paaf,
pay_assignment_actions paa_run, -- Assignment Action of Run
pay_payroll_actions ppa_run, -- Payroll Action of Run
pay_payroll_actions ppa_arch -- Payroll Action of Archiver
where ppa_arch.payroll_action_id = p_pact_id
and ppa_run.action_type in ('R', 'Q') -- Payroll Run or Quickpay Run
and (ppa_run.payroll_id = p_payroll_id or p_payroll_id is null)
and ppa_run.effective_date between ppa_arch.start_date and ppa_arch.effective_date
and ppa_run.business_group_id = ppa_arch.business_group_id
and paa_run.payroll_action_id = ppa_run.payroll_action_id
and paa_run.source_action_id is null
and paa_run.action_status IN ('C','S') --10376999
and paaf.assignment_id = paa_run.assignment_id
and ppa_arch.effective_date between paaf.effective_start_date and paaf.effective_end_date
and paaf.person_id between stperson and endperson
and (paaf.payroll_id = p_payroll_id or p_payroll_id is null)
and pai.locked_action_id = paa_run.assignment_action_id
and paa_pre.assignment_action_id = pai.locking_action_id
and paa_pre.action_status IN ('C','S') --10376999
and ppa_pre.payroll_action_id = paa_pre.payroll_action_id
and ppa_pre.action_type in ('P', 'U') -- Prepayments or Quickpay Prepayments
and ppa_pre.consolidation_set_id = p_consolidation_id
and not exists -- You can comment this to make the Archive rerunable
(
select /*+ ORDERED */ NULL
from pay_action_interlocks pai2,
pay_assignment_actions paa_arch2, -- Assignment Action of Archiver
pay_payroll_actions ppa_arch2 -- Payroll Action of Archiver
where pai2.locked_action_id = paa_run.assignment_action_id
and paa_arch2.assignment_action_id = pai2.locking_action_id
and paa_arch2.payroll_action_id = ppa_arch2.payroll_action_id
and ppa_arch2.action_type = 'X'
and ppa_arch2.report_type = 'ZA_SOE'
)
order by paa_run.assignment_id
for update of paaf.assignment_id;
select paei.aei_information7
from per_assignment_extra_info paei
where paei.assignment_id = p_assignment_id
and paei.information_type = 'ZA_SPECIFIC_INFO';
select pay_assignment_actions_s.nextval
into l_actid
from dual;
select pre.locked_action_id pre_assignment_action_id, -- Assignment Action of Prepayments
pay.locked_action_id master_assignment_action_id, -- Assignment Action of Run
assact.assignment_id assignment_id,
assact.payroll_action_id pay_payroll_action_id, -- Payroll Action of Run
paa.effective_date effective_date, -- Effective Date of Run
ppaa.effective_date pre_effective_date, -- Effective Date of Archive
paa.date_earned date_earned, -- Date Earned of Run
paa.time_period_id time_period_id -- Time Period Id of Run
from pay_action_interlocks pre, -- Lock of Archiver on Prepayment
pay_action_interlocks pay, -- Lock of Prepayment on Run
pay_payroll_actions paa, -- Payroll Action of Run
pay_payroll_actions ppaa, -- Payroll Action of Archiver
pay_assignment_actions assact, -- Assignment Action of Run
pay_assignment_actions passact -- Assignment Action of Archiver
where pre.locked_action_id = pay.locking_action_id
and pre.locking_action_id = p_locking_action_id -- Assignment Action of Archiver
and pre.locked_action_id = passact.assignment_action_id
and passact.payroll_action_id = ppaa.payroll_action_id
and ppaa.action_type in ('P', 'U')
and pay.locked_action_id = assact.assignment_action_id
and assact.payroll_action_id = paa.payroll_action_id
and assact.source_action_id is NULL;
select paa.assignment_action_id child_assignment_action_id,
'S' run_type -- Separate Payment Run
from pay_assignment_actions paa, -- Assignment Action of Child Run
pay_run_types_f prt
where paa.source_action_id = p_master_assignment_action -- Assignment Action of Master Run
and paa.payroll_action_id = p_payroll_action_id -- Payroll Action of Master Run
and paa.assignment_id = p_assignment_id
and paa.run_type_id = prt.run_type_id
and prt.run_method = 'S'
and p_effective_date between prt.effective_start_date and prt.effective_end_date
union
select paa.assignment_action_id child_assignment_action_id,
'NP' run_type -- Standard Run, Process Separate Run
from pay_assignment_actions paa
where paa.payroll_action_id = p_payroll_action_id
and paa.assignment_id = p_assignment_id
and paa.action_sequence =
(
select max(paa1.action_sequence)
from pay_assignment_actions paa1,
pay_run_types_f prt1
where prt1.run_type_id = paa1.run_type_id
and prt1.run_method in ('N', 'P')
and paa1.payroll_action_id = p_payroll_action_id -- Payroll Action of Master Run
and paa1.assignment_id = p_assignment_id
and paa1.source_action_id = p_master_assignment_action -- Assignment Action of Master Run
and p_effective_date between prt1.effective_start_date and prt1.effective_end_date
);
select paa.assignment_action_id np_assignment_action_id,
prt.run_method
from pay_assignment_actions paa,
pay_run_types_f prt
where paa.source_action_id = p_assignment_action_id
and paa.payroll_action_id = p_payroll_action_id
and paa.assignment_id = p_assignment_id
and paa.run_type_id = prt.run_type_id
and prt.run_method in ('N','P')
and p_effective_date between prt.effective_start_date and prt.effective_end_date;
select paa.chunk_number
into l_chunk_number
from pay_assignment_actions paa
where paa.assignment_action_id = p_assactid;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
select pact.payroll_action_id payroll_action_id, -- Payroll Action of Prepayments
pact.effective_date effective_date, -- Effective Date of Prepayments
pact.date_earned date_earned,
pact.payroll_id,
ppf.payroll_name payroll_name,
ppf.period_type period_type,
pact.pay_advice_message payroll_message
from pay_payrolls_f ppf,
pay_payroll_actions pact -- Payroll Action of Prepayments
where pact.payroll_id = ppf.payroll_id
and pact.effective_date between ppf.effective_start_date and ppf.effective_end_date
and pact.payroll_id = nvl(p_payroll_id, pact.payroll_id)
and pact.consolidation_set_id = p_consolidation_id
and pact.effective_date between p_start_date and p_end_date
and (pact.action_type = 'P' or pact.action_type = 'U')
and pact.action_status = 'C'
and not exists
(
select null
from pay_action_information pai
where pai.action_context_id = pact.payroll_action_id -- Payroll Action of Prepayments
and pai.action_context_type = 'PA'
and pai.action_information_category = 'EMEA PAYROLL INFO'
);
select effective_date
from pay_payroll_actions
where payroll_action_id = cp_payroll_action_id;