R12: AP: Invoice Validation - Performance Issue
(Doc ID 3029716.1)
Last updated on JUNE 24, 2024
Applies to:
Oracle Payables - Version 12.1.3 and laterInformation in this document applies to any platform.
Symptoms
AP Invoice Validation / Invoice Validation Child Worker Process - Running Long
SQL ID: 02jzzc5fpjvjx Plan Hash: 4215267216
SELECT 'PER:' || pera.PERSON_ID
FROM
PER_ASSIGNMENTS_F PERA WHERE EXISTS (SELECT '1' FROM
PER_PEOPLE_F PERF, PER_ASSIGNMENTS_F PERA1,
PER_PERSON_TYPES PPT WHERE TRUNC(SYSDATE) BETWEEN
PERF.EFFECTIVE_START_DATE AND PERF.EFFECTIVE_END_DATE AND
PERF.PERSON_ID = PERA.SUPERVISOR_ID AND PERA1.PERSON_ID =
PERF.PERSON_ID AND TRUNC(SYSDATE) BETWEEN PERA1.EFFECTIVE_START_DATE
AND PERA1.EFFECTIVE_END_DATE AND PERA1.PRIMARY_FLAG = 'Y'
AND PERA1.ASSIGNMENT_TYPE = 'E' AND PPT.SYSTEM_PERSON_TYPE IN
('EMP','EMP_APL') AND PPT.PERSON_TYPE_ID = PERF.PERSON_TYPE_ID
) START WITH PERA.PERSON_ID = ( select po.agent_id
FROM ap_holds_all aph, po_distributions_all pd,
PO_RELEASES_ALL po, po_headers_all ph WHERE
pd.line_location_id = aph.line_location_id AND po.po_release_id
= pd.po_release_id and po.po_header_id = ph.po_header_id
AND aph.hold_id = :transactionId AND rownum = 1 )
AND TRUNC(SYSDATE) BETWEEN PERA.EFFECTIVE_START_DATE AND
PERA.EFFECTIVE_END_DATE AND PERA.PRIMARY_FLAG = 'Y'
AND PERA.ASSIGNMENT_TYPE = 'E' CONNECT BY NOCYCLE PRIOR
PERA.SUPERVISOR_ID = PERA.PERSON_ID AND TRUNC(SYSDATE) BETWEEN
PERA.EFFECTIVE_START_DATE AND PERA.EFFECTIVE_END_DATE AND
PERA.PRIMARY_FLAG = 'Y' AND PERA.ASSIGNMENT_TYPE = 'E'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 157 0.69 0.75 0 0 0 0
Execute 157 0.78 0.87 0 0 0 0
Fetch 157 3577.51 3832.59 2853641 329606035 0 1126
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 471 3578.99 3834.22 2853641 329606035 0 1126
SQL ID: 3m6s7kp8x1c82 Plan Hash: 3334698072
UPDATE AP_HOLDS_ALL SET WF_STATUS = 'STARTED'
WHERE
HOLD_ID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 160 223.98 242.71 131910 21274528 323 160
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 161 223.98 242.71 131910 21274528 323 160
STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Submit Invoice Validation
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 |