Performance Issue When Processing Sales Orders for Assemble To Order (ATO) Items (Doc ID 1528793.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Bills of Material - Version 11.5.10.2 and later
Information in this document applies to any platform.

Symptoms

On : 11.5.10.2 version

ACTUAL BEHAVIOR
---------------
When processing sales orders for assemble to order (ato) items, there is a performance issue caused by the following sql statement.

********************************************************************************

SELECT /*+ LEADING (BE) */ :B7 TOP_BILL_SEQUENCE_ID, BOM.BILL_SEQUENCE_ID,
 BOM.ORGANIZATION_ID, :B5 EXPLOSION_TYPE, BIC.COMPONENT_SEQUENCE_ID,
 BIC.COMPONENT_ITEM_ID, BE.PLAN_LEVEL + 1 PLAN_LEVEL, BE.EXTENDED_QUANTITY *
 BIC.COMPONENT_QUANTITY EXTENDED_QUANTITY, BE.SORT_ORDER, SYSDATE
 CREATION_DATE, :B8 CREATED_BY, SYSDATE LAST_UPDATE_DATE, :B8
 LAST_UPDATED_BY, BE.TOP_ITEM_ID, BIC.ATTRIBUTE1, BIC.ATTRIBUTE2,
 BIC.ATTRIBUTE3, BIC.ATTRIBUTE4, BIC.ATTRIBUTE5, BIC.ATTRIBUTE6,
 BIC.ATTRIBUTE7, BIC.ATTRIBUTE8, BIC.ATTRIBUTE9, BIC.ATTRIBUTE10,
 BIC.ATTRIBUTE11, BIC.ATTRIBUTE12, BIC.ATTRIBUTE13, BIC.ATTRIBUTE14,
 BIC.ATTRIBUTE15, BIC.COMPONENT_QUANTITY, BIC.SO_BASIS, BIC.OPTIONAL,
 BIC.MUTUALLY_EXCLUSIVE_OPTIONS, BIC.CHECK_ATP, BIC.SHIPPING_ALLOWED,
 BIC.REQUIRED_TO_SHIP, BIC.REQUIRED_FOR_REVENUE, BIC.INCLUDE_ON_SHIP_DOCS,
 BIC.INCLUDE_ON_BILL_DOCS, BIC.LOW_QUANTITY, BIC.HIGH_QUANTITY,
 BIC.PICK_COMPONENTS, MSI.PRIMARY_UOM_CODE, MSI.PRIMARY_UNIT_OF_MEASURE,
 MSI.BASE_ITEM_ID, MSI.ATP_COMPONENTS_FLAG, MSI.ATP_FLAG, MSI.BOM_ITEM_TYPE,
 MSI.PICK_COMPONENTS_FLAG, MSI.REPLENISH_TO_ORDER_FLAG,
 MSI.SHIPPABLE_ITEM_FLAG, MSI.CUSTOMER_ORDER_FLAG, MSI.INTERNAL_ORDER_FLAG,
 MSI.CUSTOMER_ORDER_ENABLED_FLAG, MSI.INTERNAL_ORDER_ENABLED_FLAG,
 MSI.SO_TRANSACTIONS_FLAG, MSI.DESCRIPTION, BOM.ASSEMBLY_ITEM_ID,
 BE.COMPONENT_CODE, BE.LOOP_FLAG, BE.BOM_ITEM_TYPE PARENT_BOM_ITEM_TYPE,
 BIC.OPERATION_SEQ_NUM, BIC.ITEM_NUM, GREATEST(BE.EFFECTIVITY_DATE,
 BIC.EFFECTIVITY_DATE) EFFECTIVITY_DATE, LEAST(BE.DISABLE_DATE,
 NVL(BIC.DISABLE_DATE,BE.DISABLE_DATE)) DISABLE_DATE,
 BIC.IMPLEMENTATION_DATE, 1 REXPLODE_FLAG, BOM.COMMON_BILL_SEQUENCE_ID,
 BBOM_C.BILL_SEQUENCE_ID COMP_BILL_SEQ_ID, BBOM_C.COMMON_BILL_SEQUENCE_ID
 COMP_COMMON_BILL_SEQ_ID, BIC.AUTO_REQUEST_MATERIAL
FROM
BOM_BILL_OF_MATERIALS BBOM_C, MTL_SYSTEM_ITEMS MSI, BOM_INVENTORY_COMPONENTS
 BIC, BOM_BILL_OF_MATERIALS BOM, BOM_EXPLOSIONS BE WHERE
 BE.TOP_BILL_SEQUENCE_ID = :B7 AND BE.EXPLOSION_TYPE = :B5 AND
 BE.REXPLODE_FLAG = 1 AND BE.PLAN_LEVEL = :B6 AND BOM.ORGANIZATION_ID =
 BE.ORGANIZATION_ID AND BOM.ASSEMBLY_ITEM_ID = BE.COMPONENT_ITEM_ID AND ( (
 :B2 IS NULL AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL ) OR (:B2 IS NOT NULL
 AND BOM.ALTERNATE_BOM_DESIGNATOR IS NOT NULL AND
 BOM.ALTERNATE_BOM_DESIGNATOR=:B2 ) OR ( :B2 IS NOT NULL AND
 BOM.ALTERNATE_BOM_DESIGNATOR IS NULL AND NOT EXISTS (SELECT 'X' FROM
 BOM_BILL_OF_MATERIALS BOM2 WHERE BOM2.ORGANIZATION_ID = :B3 AND
 BOM2.ASSEMBLY_ITEM_ID = BE.COMPONENT_ITEM_ID AND
 BOM2.ALTERNATE_BOM_DESIGNATOR = :B2 AND BOM2.ASSEMBLY_TYPE = 1 ) ) ) AND
 BIC.BILL_SEQUENCE_ID = BOM.COMMON_BILL_SEQUENCE_ID AND
 NVL(BIC.ECO_FOR_PRODUCTION,2) = 2 AND BIC.IMPLEMENTATION_DATE IS NOT NULL
 AND BIC.COMPONENT_QUANTITY >= 0 AND MSI.ORGANIZATION_ID =
 BOM.ORGANIZATION_ID AND MSI.INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID AND (
 (:B5 = 'OPTIONAL' AND BE.BOM_ITEM_TYPE IN (1,2) AND (BIC.BOM_ITEM_TYPE IN
 (1,2) OR (BIC.BOM_ITEM_TYPE = 4 AND BIC.OPTIONAL = 1))) OR (:B5 =
 'INCLUDED' AND BE.PICK_COMPONENTS_FLAG = 'Y' AND BIC.BOM_ITEM_TYPE = 4 AND
 BIC.OPTIONAL = 2) OR (:B5 NOT IN ('OPTIONAL', 'INCLUDED')) ) AND (
 (BE.BASE_ITEM_ID IS NOT NULL AND BIC.BOM_ITEM_TYPE NOT IN (1,2) ) OR
 BE.BASE_ITEM_ID IS NULL ) AND BOM.ASSEMBLY_TYPE = 1 AND
 LEAST(BE.DISABLE_DATE, NVL(BIC.DISABLE_DATE,BE.DISABLE_DATE)) >=
 GREATEST(BE.EFFECTIVITY_DATE, BIC.EFFECTIVITY_DATE) AND BE.LOOP_FLAG = :B4
 AND BBOM_C.ORGANIZATION_ID(+) = :B3 AND BBOM_C.ASSEMBLY_ITEM_ID (+) =
 BIC.COMPONENT_ITEM_ID AND ( ( :B2 IS NULL AND
 BBOM_C.ALTERNATE_BOM_DESIGNATOR IS NULL ) OR (:B2 IS NOT NULL AND
 BBOM_C.ALTERNATE_BOM_DESIGNATOR IS NOT NULL AND
 BBOM_C.ALTERNATE_BOM_DESIGNATOR=:B2 ) OR ( :B2 IS NOT NULL AND
 BBOM_C.ALTERNATE_BOM_DESIGNATOR IS NULL AND NOT EXISTS (SELECT 'X' FROM
 BOM_BILL_OF_MATERIALS BOM2 WHERE BOM2.ORGANIZATION_ID = :B3 AND
 BOM2.ASSEMBLY_ITEM_ID = BIC.COMPONENT_ITEM_ID AND
 BOM2.ALTERNATE_BOM_DESIGNATOR = :B2 AND BOM2.ASSEMBLY_TYPE = 1 ) ) ) ORDER
 BY BE.SORT_ORDER, DECODE(:B1 ,1,BIC.OPERATION_SEQ_NUM, BIC.ITEM_NUM),
 DECODE(:B1 ,1,BIC.ITEM_NUM, BIC.OPERATION_SEQ_NUM)


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