TransOrder Integration Query Found To Be Poorly Performing (Doc ID 883353.1)

Last updated on JULY 09, 2014

Applies to:

Oracle Transportation Management - Version: 5.5.05 to 6.1
This problem can occur on any platform.

Symptoms

-- Problem Statement:

The following TransOrder integration query found to be slow and poorly performing.

SELECT sul.weight,
sul.volume,
sul.item_package_count,
sul.weight_uom_code,
sul.volume_uom_code
FROM ship_unit_line sul
WHERE sul.ob_line_gid = 'ID'
AND sul.ORDER_RELEASE_LINE_GID NOT IN
(SELECT orl.ORDER_RELEASE_LINE_GID
FROM ORDER_RELEASE_LINE orl, ORDER_RELEASE ord
WHERE orl.ORDER_RELEASE_LINE_GID =
ord.ORDER_RELEASE_GID
AND (ord.BUFFER_TYPE IS NOT NULL
AND ord.BUFFER_TYPE IN ('BUFFER')))
AND sul.SHIP_UNIT_GID NOT IN
(SELECT su.SHIP_UNIT_GID
FROM ship_unit su, order_release ord
WHERE su.ORDER_RELEASE_GID = ord.ORDER_RELEASE_GID
AND (ord.BUFFER_TYPE IS NOT NULL
AND ord.BUFFER_TYPE IN ('BUFFER')))

-- Steps To Reproduce:
The issue can be reproduced at will with the following steps:

1- Send in TransOrder integration.

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