Job Lot Composition Report Taking Too Much Time To Be Completed (Doc ID 2184987.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Work in Process - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

Actual Behavior
Job Lot Composition Report performance is very slow after upgrading the database from 11.2.0.3.5 to 12.1.0.2

According to tkprof trace file, below SQL takes too much time:

SQL ID: at2swrn1rkd4x Plan Hash: 3577687823

select MSI.SEGMENT1 C_ASSY_FLEX , MSI . description , WE . wip_entity_name
Job , DJ . lot_number Lot , round ( DJ . start_quantity , : P_Qty_Precision
) Qty_Planned , DJ . date_released Release_Date , ML . meaning Status ,
round ( DJ . quantity_completed , : P_Qty_Precision ) Qty_Completed , DJ .
date_completed Complete_Date , MSI . primary_uom_code UOM , DJ .
bom_revision Revision , DJ . wip_entity_id , WE . primary_item_id
from
wip_entities WE , mfg_lookups ML , mtl_system_items MSI , wip_discrete_jobs
DJ where exists ( select 'Y' from mtl_transaction_lots_view MTV where MTV .
disposition = DJ . wip_entity_id and MTV . organization_id = :
P_Organization_Id and mtv . disposition_type = 5 ) and WE . organization_id
= : P_Organization_Id and DJ . organization_id = : P_Organization_Id and
MSI . organization_id (+) = : P_Organization_Id and DJ . status_type = ML
. lookup_code and ML . lookup_type = 'WIP_JOB_STATUS' and DJ .
wip_entity_id = WE . wip_entity_id and DJ . primary_item_id = MSI .
inventory_item_id (+) AND WE.wip_entity_name BETWEEN 'xxx' AND 'xxx'
ORDER BY WE.wip_entity_name, DJ.lot_number

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.07 0.07 0 5 0 0
Fetch 1 11.66 251.49 684779 686396 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 11.73 251.56 684779 686401 0 1

 
Expected Behavior
Job Lot Composition Report should not take lot of time to complete.

Steps to Reproduce

  1. Login with Work in process responsibility
  2. Navigate to WIP > Reports > Run > Job Lot Composition Report

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