Data Collection Performs Slowly Selecting from MRP_AP_SYSTEM_ITEMS_V

(Doc ID 728475.1)

Last updated on JUNE 28, 2017

Applies to:

Oracle Advanced Supply Chain Planning - Version 12.0.4 and later
Information in this document applies to any platform.
EXECUTABLE:MSCPDPW - Planning Data Pull Worker


Symptoms

PROBLEM
During Data Collection the Planning Data Pull Worker (MSCPDPW) performs slowly.

insert into MSC_ST_SYSTEM_ITEMS ( ORGANIZATION_ID, SR_INVENTORY_ITEM_ID,
LOTS_EXPIRATION, LOT_CONTROL_CODE, SHRINKAGE_RATE, FIXED_DAYS_SUPPLY,
FIXED_ORDER_QUANTITY, FIXED_LOT_MULTIPLIER, MINIMUM_ORDER_QUANTITY,
MAXIMUM_ORDER_QUANTITY, ROUNDING_CONTROL_TYPE,
PLANNING_TIME_FENCE_DAYS,
DEMAND_TIME_FENCE_DAYS, ABC_CLASS_ID, ABC_CLASS_NAME,
SR_CATEGORY_ID, CATEGORY_NAME, MRP_PLANNING_CODE, FIXED_LEAD_TIME,
VARIABLE_LEAD_TIME, PREPROCESSING_LEAD_TIME, POSTPROCESSING_LEAD_TIME,
FULL_LEAD_TIME, CUMULATIVE_TOTAL_LEAD_TIME,
CUM_MANUFACTURING_LEAD_TIME, UOM_CODE, BUILT_IN_WIP_FLAG,
PURCHASING_ENABLED_FLAG, PLANNING_MAKE_BUY_CODE, STANDARD_COST,
CARRYING_COST, MRP_CALCULATE_ATP_FLAG, END_ASSEMBLY_PEGGING_FLAG,
ENGINEERING_ITEM_FLAG, INVENTORY_ITEM_FLAG, WIP_SUPPLY_TYPE,
MRP_SAFETY_STOCK_CODE, MRP_SAFETY_STOCK_PERCENT,
SAFETY_STOCK_BUCKET_DAYS, ACCEPTABLE_EARLY_DELIVERY, BUYER_NAME,
PLANNER_CODE, PLANNING_EXCEPTION_SET, EXCESS_QUANTITY,
EXCEPTION_SHORTAGE_DAYS, EXCEPTION_EXCESS_DAYS,
EXCEPTION_OVERPROMISED_DAYS, REPETITIVE_VARIANCE_DAYS, BASE_ITEM_ID,
BOM_ITEM_TYPE, ATO_FORECAST_CONTROL, EFFECTIVITY_CONTROL,
INVENTORY_PLANNING_CODE, UNIT_WEIGHT, UNIT_VOLUME, WEIGHT_UOM,
VOLUME_UOM, PRODUCT_FAMILY_ID, RELEASE_TIME_FENCE_CODE,
RELEASE_TIME_FENCE_DAYS, ATP_RULE_ID, ORDER_COST,
ATP_COMPONENTS_FLAG,
REPETITIVE_TYPE, ORGANIZATION_CODE, INVENTORY_TYPE,
IN_SOURCE_PLAN,
ATP_FLAG, REVISION_QTY_CONTROL_CODE, EXPENSE_ACCOUNT,
INVENTORY_ASSET_FLAG, ACCEPTABLE_RATE_DECREASE,
ACCEPTABLE_RATE_INCREASE, BUYER_ID, SOURCE_ORG_ID, DMD_LATENESS_COST,
SUPPLIER_CAP_OVERUTIL_COST, MATERIAL_COST, RESOURCE_COST,
AVERAGE_DISCOUNT, DELETED_FLAG, PIP_FLAG, REPLENISH_TO_ORDER_FLAG,
PICK_COMPONENTS_FLAG, YIELD_CONV_FACTOR, CREATE_SUPPLY_FLAG,
SUBSTITUTION_WINDOW, DESCRIPTION, LIST_PRICE, ITEM_NAME,
REDUCE_MPS,
CRITICAL_COMPONENT_FLAG, VMI_MINIMUM_UNITS, VMI_MINIMUM_DAYS,
VMI_MAXIMUM_UNITS, VMI_MAXIMUM_DAYS, VMI_FIXED_ORDER_QUANTITY,
SO_AUTHORIZATION_FLAG, CONSIGNED_FLAG, ASN_AUTOEXPIRE_FLAG,
VMI_FORECAST_TYPE, FORECAST_HORIZON, BUDGET_CONSTRAINED,
DAYS_TGT_INV_SUPPLY, DAYS_TGT_INV_WINDOW, DAYS_MAX_INV_SUPPLY,
DAYS_MAX_INV_WINDOW, DRP_PLANNED, CONTINOUS_TRANSFER, CONVERGENCE,
DIVERGENCE, EAM_ITEM_TYPE, ITEM_CREATION_DATE, SHORTAGE_TYPE,
EXCESS_TYPE, PLANNING_TIME_FENCE_CODE, SOURCE_TYPE, REFRESH_ID,
SR_INSTANCE_ID) select x.ORGANIZATION_ID, x.INVENTORY_ITEM_ID,
x.LOTS_EXPIRATION, x.LOT_CONTROL_CODE, x.SHRINKAGE_RATE,
x.FIXED_DAYS_SUPPLY, x.FIXED_ORDER_QUANTITY, x.FIXED_LOT_MULTIPLIER,
x.MINIMUM_ORDER_QUANTITY, x.MAXIMUM_ORDER_QUANTITY,
x.ROUNDING_CONTROL_TYPE, x.PLANNING_TIME_FENCE_DAYS,
x.DEMAND_TIME_FENCE_DAYS, x.ABC_CLASS_ID, x.ABC_CLASS_NAME,
x.CATEGORY_ID, x.CATEGORY_NAME, x.MRP_PLANNING_CODE,
x.FIXED_LEAD_TIME, x.VARIABLE_LEAD_TIME, x.PREPROCESSING_LEAD_TIME,
x.POSTPROCESSING_LEAD_TIME, x.FULL_LEAD_TIME,
x.CUMULATIVE_TOTAL_LEAD_TIME, x.CUM_MANUFACTURING_LEAD_TIME,
x.UOM_CODE,
x.BUILT_IN_WIP_FLAG, x.PURCHASING_ENABLED_FLAG,
x.PLANNING_MAKE_BUY_CODE, x.ITEM_COST, x.CARRYING_COST,
x.MRP_CALCULATE_ATP_FLAG, x.END_ASSEMBLY_PEGGING_FLAG, x.ENG_ITEM_FLAG, x.INVENTORY_ITEM_FLAG, x.WIP_SUPPLY_TYPE, x.MRP_SAFETY_STOCK_CODE,
x.MRP_SAFETY_STOCK_PERCENT, x.SAFETY_STOCK_BUCKET_DAYS,
x.ACCEPTABLE_EARLY_DELIVERY, x.BUYER_NAME, x.PLANNER_CODE,
x.PLANNING_EXCEPTION_SET, x.EXCESS_QUANTITY, x.EXCEPTION_SHORTAGE_DAYS,
x.EXCEPTION_EXCESS_DAYS, x.EXCEPTION_OVERPROMISED_DAYS,
x.REPETITIVE_VARIANCE_DAYS, x.BASE_ITEM_ID, x.BOM_ITEM_TYPE,
x.ATO_FORECAST_CONTROL, x.EFFECTIVITY_CONTROL,
x.INVENTORY_PLANNING_CODE, x.UNIT_WEIGHT, x.UNIT_VOLUME,
x.WEIGHT_UOM,
x.VOLUME_UOM, x.PRODUCT_FAMILY_ID, x.RELEASE_TIME_FENCE_CODE,
x.RELEASE_TIME_FENCE_DAYS, x.ATP_RULE_ID, x.ORDER_COST,
x.ATP_COMPONENTS_FLAG, x.REPETITIVE_TYPE, 1,
x.INVENTORY_TYPE, 2, x.ATP_FLAG, x.REVISION_QTY_CONTROL_CODE,
x.EXPENSE_ACCOUNT, x.INVENTORY_ASSET_FLAG, x.ACCEPTABLE_RATE_DECREASE,
x.ACCEPTABLE_RATE_INCREASE, x.BUYER_ID, x.SOURCE_ORG_ID,
TO_NUMBER(DECODE( 1, 1, x.Attribute1,
2, x.Attribute2, 3, x.Attribute3, 4,
x.Attribute4, 5, x.Attribute5, 6, x.Attribute6,
7, x.Attribute7, 8, x.Attribute8, 9,
x.Attribute9, 10, x.Attribute10, 11, x.Attribute11,
12, x.Attribute12, 13, x.Attribute13, 14,
x.Attribute14, 15, x.Attribute15)), TO_NUMBER(DECODE(
1, 1, x.Attribute1, 2,
x.Attribute2, 3, x.Attribute3, 4, x.Attribute4,
5, x.Attribute5, 6, x.Attribute6, 7,
x.Attribute7, 8, x.Attribute8, 9, x.Attribute9,
10, x.Attribute10, 11, x.Attribute11, 12,
x.Attribute12, 13, x.Attribute13, 14, x.Attribute14,
15, x.Attribute15)), x.MATERIAL_COST, x.RESOURCE_COST,
1, 2, DECODE(x.PLANNED_INVENTORY_POINT,1,1,2) ,
x.REPLENISH_TO_ORDER_FLAG, x.PICK_COMPONENTS_FLAG , -99999,
x.CREATE_SUPPLY_FLAG,x.SUBSTITUTION_WINDOW, x.DESCRIPTION,
x.MRP_BIS_LIST_PRICE, x.SEGMENT1, x.REDUCE_MPS,
x.CRITICAL_COMPONENT_FLAG,x.VMI_MINIMUM_UNITS,x.VMI_MINIMUM_DAYS,
x.VMI_MAXIMUM_UNITS, x.VMI_MAXIMUM_DAYS,x.VMI_FIXED_ORDER_QUANTITY,
x.SO_AUTHORIZATION_FLAG,x.CONSIGNED_FLAG,x.ASN_AUTOEXPIRE_FLAG,
x.VMI_FORECAST_TYPE,x.FORECAST_HORIZON,x.EXCLUDE_FROM_BUDGET_FLAG,
x.DAYS_TGT_INV_SUPPLY,x.DAYS_TGT_INV_WINDOW, x.DAYS_MAX_INV_SUPPLY,
x.DAYS_MAX_INV_WINDOW,x.DRP_PLANNED_FLAG,x.CONTINOUS_TRANSFER,x.CONVERGENCE, x.DIVERGENCE, x.EAM_ITEM_TYPE, x.MSIB_CREATION_DATE, x.SHORTAGE_TYPE ,
x.EXCESS_TYPE ,x.PLANNING_TIME_FENCE_CODE, x.SOURCE_TYPE, 1,
1 from MRP_AP_NOCOST_SYSTEM_ITEMS_V x WHERE
x.ORGANIZATION_ID IN (52,55,58,61,64,67,154,195);

.

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