R12: AP: Invoice Validation - Performance Issue (Doc ID 3029716.1)

Last updated on JUNE 24, 2024

Applies to:

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

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