PYUGEN: Performance Issues With Payroll
(Doc ID 341686.1)
Last updated on JULY 30, 2020
Oracle Payroll - Version 126.96.36.199 and later Information in this document applies to any platform.
The PAYROLL PROCESS has slowed down -
Most expensive query appears to be the following:
SELECT /*+ ORDERED */ fnd_number.number_to_canonical(nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale),0)) FROM /* Person within Government Reporting Entity */ pay_assignment_actions BAL_ASSACT , pay_payroll_actions BACT , per_all_assignments_f ASS , pay_assignment_actions ASSACT , pay_payroll_actions PACT , pay_run_results RR , pay_run_result_values TARGET , pay_balance_feeds_f FEED where BAL_ASSACT.assignment_action_id = :ASSIGNMENT_ACTION_ID and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id and FEED.balance_type_id = :U1 + decode(TARGET.INPUT_VALUE_ID, null, 0, 0) and FEED.input_value_id = TARGET.input_value_id and nvl(TARGET.result_value,'0') != '0' and TARGET.run_result_id = RR.run_result_id and RR.assignment_action_id = ASSACT.assignment_action_id and ASSACT.payroll_action_id = PACT.payroll_action_id and PACT.effective_date between FEED.effective_start_date and FEED.effective_end_date and RR.status in ('P','PA') and ASSACT.action_sequence <= BAL_ASSACT.action_sequence and ASSACT.assignment_id = ASS.assignment_id /* This may break under the cost-based optimizer, since the result depends on the correct plan! */ and ASS.person_id = (select person_id from per_all_assignments_f START_ASS where START_ASS.assignment_id = BAL_ASSACT.assignment_id and rownum = 1) and PACT.effective_date between ASS.effective_start_date and ASS.effective_end_date and ASSACT.tax_unit_id = :TAX_UNIT_ID /* Year To Date */ and PACT.effective_date >=trunc(BACT.effective_date,'Y')
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!