AU: Payroll Reconciliation Summary XML - Poor Performance Post AU.17
(Doc ID 1484530.1)
Last updated on FEBRUARY 05, 2024
Applies to:
Oracle HRMS (Australia) - Version 11.5.10.2 and laterInformation in this document applies to any platform.
This note provides a brief overview of
Symptoms
Problem Statement:
Payroll Reconciliation Summary XML took 42 hours to finish processing when run as part of End of Year Processing for financial year 2011/2012
Payroll Reconciliation Summary XML is taking time, the spawned program 'Payroll Reconciliation Summary' finished in a few minutes, l
Problem sql id from trace:
SELECT PAA.ASSIGNMENT_ACTION_ID, PAA.ACTION_SEQUENCE, PAAF.ASSIGNMENT_ID,
PAA.TAX_UNIT_ID, PAA.SOURCE_ACTION_ID MASTER_ACTION_ID, PAA2.TAX_UNIT_ID
MASTER_TAX_UNIT_ID
FROM
PER_PEOPLE_F PAP, PER_ASSIGNMENTS_F PAAF, PAY_PAYROLL_ACTIONS PPA,
PAY_PAYROLL_ACTIONS PPA1, PAY_ASSIGNMENT_ACTIONS PAA,
PAY_ASSIGNMENT_ACTIONS PAA2, PER_PERIODS_OF_SERVICE PPS,
PAY_POPULATION_RANGES PPR WHERE PPA.PAYROLL_ACTION_ID = :B7 AND
PPR.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID AND PPR.CHUNK_NUMBER = :B6
AND PAA.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID AND PAA2.ASSIGNMENT_ID =
PAA.ASSIGNMENT_ID AND PAP.PERSON_ID = PPR.PERSON_ID AND PAP.PERSON_ID =
PAAF.PERSON_ID AND PAP.PERSON_ID = PPS.PERSON_ID AND
PPS.PERIOD_OF_SERVICE_ID = PAAF.PERIOD_OF_SERVICE_ID AND PPA1.DATE_EARNED
BETWEEN PAP.EFFECTIVE_START_DATE AND PAP.EFFECTIVE_END_DATE AND
PPA1.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID AND PPA1.PAYROLL_ACTION_ID
=
PAA2.PAYROLL_ACTION_ID AND PAA2.ACTION_STATUS ='C' AND PAA.ACTION_STATUS =
'C' AND PAA2.ASSIGNMENT_ACTION_ID = NVL(PAA.SOURCE_ACTION_ID,
PAA.ASSIGNMENT_ACTION_ID) AND PPA1.BUSINESS_GROUP_ID =
PPA.BUSINESS_GROUP_ID AND PPA.BUSINESS_GROUP_ID = :B5 AND PPA1.ACTION_TYPE
IN ('R','Q','I','B','V') AND PAA.TAX_UNIT_ID = :B4 AND PPA1.EFFECTIVE_DATE
BETWEEN :B3 AND :B2 AND DECODE(PPS.ACTUAL_TERMINATION_DATE,NULL,'Y',
DECODE(SIGN(PPS.ACTUAL_TERMINATION_DATE - (:B2 )),1,'Y','N')) LIKE :B1 AND
PAAF.EFFECTIVE_END_DATE = (SELECT MAX(EFFECTIVE_END_DATE) FROM
PER_ASSIGNMENTS_F IIPAF WHERE IIPAF.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID AND
IIPAF.EFFECTIVE_END_DATE >= :B3 AND IIPAF.EFFECTIVE_START_DATE ORDER BY PAAF.ASSIGNMENT_ID, PAA.ASSIGNMENT_ACTION_ID, PAA.TAX_UNIT_ID,
PAA.SOURCE_ACTION_ID, PAA2.TAX_UNIT_ID
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 432 0.38 0.41 0 0 0 0
Fetch 534 14448.56 14535.77 11049 421831059 0 16903
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 967 14448.95 14536.19 11049 421831059 0 16903
Steps to Reproduce:
Navigation: Submit Request > Payroll Reconciliation Summary XML
Parameters :
- Legal Employer: 201.XXXX
- Start Date : 01-Jul-2011
- End Date: 30-Jun-2012
- Employee Type : Both Terminated and Current Employees
- Year to Date Summary Totals : Yes
- Suppress Current Zero Records : No
- Suppress Current Negative Records : No
- Sort Order 1 : Employee
AU Legislative Patch AU.17 and AU.18 has been applied. The issue was first reported post AU.17, retested after AU.18, but still the same result.
Changes
Applied: AU Legislative Patch AU.17
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 |
| Changes |
| Cause |
| Solution |
| References |