My Oracle Support Banner

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


Symptoms

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 :

SELECT NVL(MAX(INVENTORY_ITEM_ID), -9999)
FROM MTL_SYSTEM_ITEMS
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

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


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