ROI - For Lot Controlled Items Poor Performance When Processing More Than 1000 Lines in RCV_TRANSACTIONS_INTERFACE Table
(Doc ID 1304668.1)
Last updated on JANUARY 07, 2020
Applies to:Oracle Inventory Management - Version 12.0.6 and later
Information in this document applies to any platform.
Executable:RVCTP - Receiving Transaction Processor
Performance issue when receiving or direct delivering lot controlled items via Receiving Open Interface (ROI) for more than 1000 records in RCV_TRANSACTIONS_INTERFACE table. It takes around one hour to process 1000 records (in RCV_TRANSACTIONS_INTERFACE table).
Acceptable performance would be to process 1000 records in less than 15 minutes. When there is less than 1000 records in RCV_TRANSACTIONS_INTERFACE table, there is no performance issue.
The expensive SQL statement is :
WHERE SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE - 1) AND NVL(END_DATE_ACTIVE, SYSDATE + 1) AND
INVENTORY_ITEM_ID = :B1
The issue can be reproduced at will with the following steps:
1. Populate the receiving interface tables : RCV_HEADERS_INTERFACE, RCV_TRANSACTIONS_INTERFACE and MTL_TRANSACTION_LOTS_INTERFACE tables
2. Run the Receiving Transaction Processor concurrent program
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