R12 AP: Payables Open Interface Import program never finishes despite of the low volume of transactions (Doc ID 1419355.1)

Last updated on MARCH 16, 2016

Applies to:

Oracle Payables - Version 12.0.1 and later
Information in this document applies to any platform.

Symptoms

OA release 12

Performance issue with the Payables Open Interface Import process.
This concurrent request imports about 3000 invoices. It has been running more than 16 hours.

The following SQL statements are taking most of the time:

DELETE FROM ZX_VALIDATION_ERRORS_GT A
WHERE
A.ROWID < ( SELECT MAX(B.ROWID) FROM ZX_VALIDATION_ERRORS_GT B WHERE
A.APPLICATION_ID = B.APPLICATION_ID AND A.ENTITY_CODE = B.ENTITY_CODE AND
A.EVENT_CLASS_CODE = B.EVENT_CLASS_CODE AND A.TRX_ID = B.TRX_ID AND
A.TRX_LINE_ID = B.TRX_LINE_ID AND A.TRX_LEVEL_TYPE = B.TRX_LEVEL_TYPE AND
A.SUMMARY_TAX_LINE_NUMBER = B.SUMMARY_TAX_LINE_NUMBER AND A.MESSAGE_NAME =
B.MESSAGE_NAME )

SELECT APPLICATION_ID, ENTITY_CODE, EVENT_CLASS_CODE, TRX_ID, TRX_LINE_ID,
MESSAGE_NAME, MESSAGE_TEXT, TRX_LEVEL_TYPE, OTHER_DOC_APPLICATION_ID,
OTHER_DOC_ENTITY_CODE, OTHER_DOC_EVENT_CLASS_CODE, OTHER_DOC_TRX_ID,
INTERFACE_LINE_ENTITY_CODE, INTERFACE_LINE_ID
FROM
ZX_VALIDATION_ERRORS_GT

INSERT INTO ZX_VALIDATION_ERRORS_GT( APPLICATION_ID, ENTITY_CODE,
EVENT_CLASS_CODE, TRX_ID, TRX_LINE_ID, SUMMARY_TAX_LINE_NUMBER,
MESSAGE_NAME, MESSAGE_TEXT, TRX_LEVEL_TYPE, INTERFACE_TAX_ENTITY_CODE,
INTERFACE_TAX_LINE_ID ) SELECT APPLICATION_ID, ENTITY_CODE,
EVENT_CLASS_CODE, TRX_ID, TRX_LINE_ID, SUMMARY_TAX_LINE_NUMBER,
'ZX_TAX_RATE_ID_CODE_MISSING', :B1 , NULL, INTERFACE_ENTITY_CODE,
INTERFACE_TAX_LINE_ID FROM ZX_IMPORT_TAX_LINES_GT WHERE TAX_RATE_CODE IS
NULL AND TAX_RATE_ID IS NULL

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms