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

Last updated on DECEMBER 15, 2021

Applies to:

Symptoms

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

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