Poor Performance Generating Pick Slip Report (Doc ID 969599.1)

Last updated on JUNE 28, 2016

Applies to:

Oracle Shipping Execution - Version 12.0.0 to 12.1 [Release 12.0 to 12.1]
Information in this document applies to any platform.
Report:WSHRDPIK.RDF - Pick Slip Report


Symptoms

Release 12.1.1

Find that after upgrading to 12.1.1 when printing the Pick Slip Report it is taking about 5 minutes to complete for a order with a single line.

The TKPROF shows this is the poorly performing SQL:

UNION ALL
SELECT DISTINCT
wpsv.pick_slip_number,
DECODE (msik.reservable_type,
---------------------------------------------
FROM (SELECT mmt.subinventory_code subinventory_code,
mmt.locator_id from_locator_id,
mmt.transfer_subinventory to_subinventory,
mmt.transfer_locator_id to_locator_id,
ABS (mmt.primary_quantity) primary_qty,
mmt.pick_slip_number,
mmt.transaction_date detailing_date,
mmt.move_order_line_id,
'PICKED' line_status,
mmt.transaction_id,
mmt.transaction_uom,
mmt.secondary_transaction_quantity,
mmt.secondary_uom_code,
mtrl.header_id,
mtrl.line_id,
mtrl.line_number
FROM mtl_material_transactions mmt, mtl_txn_request_lines mtrl
WHERE mmt.pick_slip_number IS NOT NULL
AND NVL (mmt.transaction_quantity, 0) < 0
AND mmt.move_order_line_id = mtrl.line_id
AND mtrl.organization_id = :p_organization_id











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