My Oracle Support Banner

Generate Run Balance Performance Issue After R12.1 RUP11 / R12.2 RUP 12 (Doc ID 2435385.1)

Last updated on FEBRUARY 22, 2019

Applies to:

Oracle Payroll - Version 12.1 HRMS RUP11 to 12.2 HRMS RUP12 [Release 12.1 to 12.2]
Oracle HRMS (US) - Version 12.2 and later
Information in this document applies to any platform.
This note provides a brief overview of .

Symptoms

Problem Statement:

Generate Run Balance is taking time to complete. If the process does not complete within the PROCESS_TIMEOUT, the following error occurs.

HR_6881_HRPROC_ORA_ERR
SQLERRMC ORA-20001: Rerun the process. The previous attempt did not run to completion and was timed out.


Most expensive query:

SELECT /*+ ORDERED INDEX(ASSACT PAY_ASSIGNMENT_ACTIONS_PK, PACT
PAY_PAYROLL_ACTIONS_PK, RR PAY_RUN_RESULTS_N50, ET PAY_ELEMENT_TYPES_F_PK, TR
PAY_TAXABILITY_RULES_FK1, TARGET PAY_RUN_RESULT_VALUES_N50, FEED
PAY_BALANCE_FEEDS_F_N2, BT PAY_BALANCE_TYPES_PK) USE_NL(PACT ASSACT RR ET TR
TARGET FEED BT) */
fnd_number.number_to_canonical(nvl(sum(fnd_number.canonical_to_number(TARGET.r
esult_value) * FEED.scale),0)), FEED.balance_type_id FROM /* Assignment
within Government Reporting Entity For Run */
pay_assignment_actions ASSACT
,pay_payroll_actions PACT
,pay_run_results RR
,pay_element_types_f ET
,pay_taxability_rules TR
,pay_run_result_values TARGET
,pay_balance_feeds_f FEED
,pay_balance_types BT
where ASSACT.assignment_action_id = :ASSIGNMENT_ACTION_ID
and ASSACT.payroll_action_id = PACT.payroll_action_id
and nvl(TARGET.result_value,'0') != '0'
and FEED.input_value_id = TARGET.input_value_id
and TARGET.run_result_id = RR.run_result_id
and RR.assignment_action_id = ASSACT.assignment_action_id
and PACT.effective_date between
FEED.effective_start_date and FEED.effective_end_date
and RR.status in ('P','PA')
and ASSACT.tax_unit_id = :TAX_UNIT_ID
/* Subject to Tax in JD */
and ET.element_type_id = RR.element_type_id
and PACT.effective_date BETWEEN
ET.effective_start_date and ET.effective_end_date
and bt.jurisdiction_level IN ( 2,6,8,11 )
and BT.balance_type_id = FEED.balance_type_id
and nvl(TR.status,'VALID') <> 'D'
and ( TR.jurisdiction_code, TR.classification_id,
TR.tax_category ) =
(
/*
1. State level subject balance is required: jd level = 2 and
taxability rule will always exist for the state.
2. Local level subject balance is required: use the state level
subject balance if no taxability rules exist.
*/
SELECT decode(count(*), 0,
SUBSTR(:JURISDICTION_CODE,1,2) || '-000-0000' ,
DECODE(BT.JURISDICTION_LEVEL,6, SUBSTR(:JURISDICTION_CODE,1,6) || '-0000' ,
8,
SUBSTR(:JURISDICTION_CODE,1,8),

11,SUBSTR(:JURISDICTION_CODE,1,2) || '-000-' ||
SUBSTR(:JURISDICTION_CODE,8,4) )),
ET.CLASSIFICATION_ID,
ET.ELEMENT_INFORMATION1
FROM PAY_TAXABILITY_RULES
WHERE JURISDICTION_CODE =
DECODE(BT.JURISDICTION_LEVEL,6,SUBSTR(:JURISDICTION_CODE,1,6)
|| '-0000' ,
8,SUBSTR(:JURISDICTION_CODE,1,8),
11,SUBSTR(:JURISDICTION_CODE,1,2)
|| '-000-' || SUBSTR(:JURISDICTION_CODE,8,4) )
AND nvl(status,'VALID') <> 'D'
AND classification_id = ET.classification_id
and rownum < 2 )
and TR.tax_type =
(SELECT
CASE
WHEN pec.classification_name = 'Pre-Tax Deductions' AND
pec.legislation_code = 'US'
THEN
(SELECT decode(count(*), 0,
DECODE(BT.TAX_TYPE, 'CITY','DEFCITY', 'COUNTY','DEFCOUNTY',
'SCHOOL','DEFCITY',
'NW_CITY','NW_SIT', 'NW_COUNTY','NW_SIT',
'NW_SCHOOL','NW_SIT', BT.TAX_TYPE) , BT.TAX_TYPE)
FROM PAY_TAXABILITY_RULES
WHERE JURISDICTION_CODE =
DECODE(BT.JURISDICTION_LEVEL,6,SUBSTR(:JURISDICTION_CODE,1,6)
|| '-0000' ,
8,SUBSTR(:JURISDICTION_CODE,1,8),
11,SUBSTR(:JURISDICTION_CODE,1,2)
|| '-000-' || SUBSTR(:JURISDICTION_CODE,8,4) )
AND nvl(status,'VALID') <> 'D'
AND classification_id = ET.classification_id
and rownum < 2 )
ELSE
(SELECT decode(count(*), 0,
DECODE(BT.TAX_TYPE, 'CITY','SIT', 'COUNTY','SIT',
'SCHOOL','SIT',
'NW_CITY','NW_SIT', 'NW_COUNTY','NW_SIT',
'NW_SCHOOL','NW_SIT', BT.TAX_TYPE) , BT.TAX_TYPE)
FROM PAY_TAXABILITY_RULES
WHERE JURISDICTION_CODE =
DECODE(BT.JURISDICTION_LEVEL,6,SUBSTR(:JURISDICTION_CODE,1,6)
|| '-0000' ,
8,SUBSTR(:JURISDICTION_CODE,1,8),
11,SUBSTR(:JURISDICTION_CODE,1,2)
|| '-000-' || SUBSTR(:JURISDICTION_CODE,8,4) )
AND nvl(status,'VALID') <> 'D'
AND classification_id = ET.classification_id
and rownum < 2 )
END
FROM pay_element_classifications pec
WHERE pec.classification_id = ET.classification_id) and
FEED.balance_type_id in (4267, 4269, 4270, 4546, 135460 ) group by
FEED.balance_type_id

 

 

Steps to Reproduce:

The issue can be reproduced at will with the following steps:

  1.  Apply HR_PF.B RUP11
  2.  Run "Generate Run Balance"
  3.  Observed the performance issue.



Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.