RCVRCERC: Poor Performance In Querying RMA With RMA Number (Doc ID 468100.1)

Last updated on AUGUST 21, 2016

Applies to:

Oracle Inventory Management - Version 11.5.10 to 12.1.3 [Release 11.5 to 12.1]
Information in this document applies to any platform.
FORM:RCVRCERC.FMB - Enter Receipts GUI


Symptoms

When querying a RMA in the Receipts form with RMA number it takes a long time before the RMA is returned.
The trace file indicates that the following query consumes the most time:

SELECT distinct a.order_number, a.header_id, b.order_category_code,
oec.customer_id, oec.name customer_name, oec.customer_number
FROM
oe_order_headers_all a, oe_order_lines_all c, oe_transaction_types_all b,
oe_transaction_types_tl t, oe_sold_to_orgs_v oec WHERE (ORDER_NUMBER LIKE
:1) AND ( a.sold_to_org_id = oec.organization_id and a.header_id =
c.header_id and c.LINE_CATEGORY_CODE='RETURN' and ((a.ship_from_org_id is
not null and a.ship_from_org_id = :2) or exists (select 1 from
oe_order_lines_all oel where oel.header_id = a.header_id and
oel.ship_from_org_id = :3)) and a.open_flag = 'Y' and a.booked_flag = 'Y'
and c.line_type_id = b.transaction_type_id and b.order_category_code IN
('MIXED','RETURN') and b.transaction_type_id = t.transaction_type_id and
t.language = userenv('LANG') ) ORDER BY a.order_number

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