Performance Issue in Pick Release Form When Entering Order Number (Doc ID 1367203.1)

Last updated on APRIL 09, 2017

Applies to:

Oracle Shipping Execution - Version 12.0.4 to 12.1.3 [Release 12.0 to 12.1]
Information in this document applies to any platform.

Symptoms

ACTUAL BEHAVIOR
---------------
Performance Issue in the Release Sales Order for Picking

Performance issue occurs in the Release Sales Order for Picking form.

When providing the order number in the order number field, the order gets recognized by the system, It then provides the Order Type and the Customer... normally this process takes two or three seconds , but lately it is taking more than an hour.


Problem SELECT identified as below..

select distinct /*+ index(wdd WSH_DELIVERY_DETAILS_N12) */
wdd.source_header_number , wtt.name "name" , party.party_name
"customer_name", wdd.source_header_id, wdd.source_header_type_id,
wdd.customer_id
from
wsh_delivery_details wdd, wsh_transaction_types_vl wtt, hz_parties party,
hz_cust_accounts cust_acct where (SOURCE_HEADER_NUMBER LIKE :1) AND (
wdd.source_header_type_id = wtt.transaction_type_id and party.party_id =
cust_acct.party_id and cust_acct.status = 'A' and wdd.customer_id =
cust_acct.cust_account_id and wdd.released_status in ('R','B','X') and
nvl(wdd.replenishment_status,'C') = 'C' and :2 = 1 and wdd.source_code =
'OE' ) order by 2,1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.01 0 1 0 0
Fetch 1 103.16 4261.36 735909 766145 1 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 103.17 4261.37 735909 766146 1 1


EXPECTED BEHAVIOR
-----------------------
Expect the form to not freeze and submit pick release

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