The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT master_organization_id
INTO g_Master_Org_ID
FROM mtl_parameters
WHERE organization_id = p_Org_ID;
SELECT SUBSTR(attribute_name, 18) Attribute_Code
FROM mtl_item_attributes
WHERE control_level = 1
AND attribute_group_id_gui IN
(20, 25, 30, 31, 35, 40, 41, 51,
60, 62, 65, 70, 80, 90, 100, 120 /* Start Bug 3713912 */,130/* End Bug 3713912 */);
/*----------------------------- Update_Org_Items -----------------------------*/
PROCEDURE Update_Org_Items
(
p_init_msg_list IN VARCHAR2 := FND_API.g_FALSE
, p_commit IN VARCHAR2 := FND_API.g_FALSE
, p_lock_rows IN VARCHAR2 := FND_API.g_TRUE
, p_validation_level IN NUMBER := FND_API.g_VALID_LEVEL_FULL
, p_Item_rec IN INV_ITEM_API.Item_rec_type
, p_update_changes_only IN VARCHAR2 := FND_API.g_FALSE
, p_validate_Master IN VARCHAR2 := FND_API.g_TRUE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Org_Items';
l_update_Item_TL BOOLEAN;
SELECT process_enabled_flag, wms_enabled_flag,
eam_enabled_flag, primary_cost_method,
trading_partner_org_flag
FROM mtl_parameters
WHERE organization_id = c_organization_id;
SELECT attribute_name, status_control_code,control_level /*Bug#6911195 Added control_level*/
FROM mtl_item_attributes
WHERE status_control_code IS NOT NULL;
SELECT attribute_name, attribute_value
FROM mtl_status_attribute_values
WHERE inventory_item_status_code = p_item_status_code;
SELECT status.attribute_name, attribute_value, status_control_code
FROM mtl_status_attribute_values status, mtl_item_attributes control
WHERE inventory_item_status_code =p_item_status_code
AND status.attribute_name = control.attribute_name
AND control_level = 2;
l_update_child_rec Boolean := FALSE; --Bug: 5220205
SAVEPOINT Update_Org_Items_PVT;
SELECT INSTALLED_FLAG INTO l_Lang_Flag
FROM FND_LANGUAGES
WHERE LANGUAGE_CODE = userenv('LANG');
fnd_message.SET_NAME( 'INV', 'INV_Update_Org_Items_notfound' );
IF ( FND_API.to_Boolean (p_update_changes_only) )
THEN
-- Update changed attributes only - not implemented yet
/*
INV_ITEM_Lib.Copy_Changed_Attributes
(
p_Item_rec => p_Item_rec
, x_Item_rec => l_Item_rec
);
INV_ITEM_API.Update_Item_Row
(
p_Item_rec => l_Item_rec
, p_update_Item_TL => TRUE
, p_Lang_Flag => l_Lang_Flag
, x_return_status => l_return_status
);
SELECT control_level
INTO l_status_code_control_level
FROM mtl_item_attributes
WHERE attribute_name = 'MTL_SYSTEM_ITEMS.INVENTORY_ITEM_STATUS_CODE';
l_update_Item_TL := FALSE;
l_update_child_rec := FALSE; --Bug: 5220205
SELECT description , long_description
INTO l_item_rec.description , l_item_rec.long_description
FROM mtl_system_items_tl
WHERE inventory_item_id = l_item_rec.inventory_item_id
AND organization_id = l_item_rec.organization_id
AND language = userenv('LANG');
l_update_Item_TL := TRUE;
l_update_child_rec := TRUE;
l_update_Item_TL := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE; -- Bug 6450473
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
ELSIF ( l_Attribute_Code = 'ALLOW_ITEM_DESC_UPDATE_FLAG') THEN
IF (NVL(l_Item_rec.ALLOW_ITEM_DESC_UPDATE_FLAG,'!') <> NVL(m_Item_rec.ALLOW_ITEM_DESC_UPDATE_FLAG,'!')) THEN
l_Item_rec.ALLOW_ITEM_DESC_UPDATE_FLAG := m_Item_rec.ALLOW_ITEM_DESC_UPDATE_FLAG;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
l_update_child_rec := TRUE;
IF l_update_child_rec = TRUE THEN
l_Item_rec.LAST_UPDATE_DATE := m_Item_rec.LAST_UPDATE_DATE;
l_Item_rec.LAST_UPDATED_BY := m_Item_rec.LAST_UPDATED_BY;
l_Item_rec.LAST_UPDATE_LOGIN := m_Item_rec.LAST_UPDATE_LOGIN;
SELECT 1 INTO l_exists
FROM ZX_OUTPUT_CLASSIFICATIONS_V
WHERE lookup_code = l_item_rec.TAX_CODE
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE) and NVL(end_date_active, SYSDATE)
AND org_id IN (-99, (SELECT org_information3 FROM hr_organization_information
WHERE ( ORG_INFORMATION_CONTEXT || '') ='Accounting Information'
AND ORGANIZATION_ID=l_Item_rec.organization_id))
AND rownum = 1;
SELECT 1 INTO l_exists
FROM ZX_INPUT_CLASSIFICATIONS_V
WHERE nvl(tax_type,'X') not in ('AWT','OFFSET') --Modified to fix bug 7588091
AND enabled_flag = 'Y'
AND sysdate between start_date_active and nvl(end_date_active,sysdate)
AND lookup_code = l_Item_rec.PURCHASING_TAX_CODE
AND org_id IN (-99, (SELECT org_information3 FROM hr_organization_information
WHERE ( ORG_INFORMATION_CONTEXT || '') ='Accounting Information'
AND ORGANIZATION_ID=l_Item_rec.organization_id))
AND rownum = 1;
IF l_update_child_rec = TRUE THEN
Set_Is_Master_Attr_Modified('Y'); /*Bug 6407303, Set the parameter */
INV_ITEM_API.Update_Item_Row
(
p_Item_rec => l_Item_rec
, p_update_Item_TL => l_update_Item_TL
, p_Lang_Flag => l_Lang_Flag
, x_return_status => l_return_status
);
ROLLBACK TO Update_Org_Items_PVT;
ROLLBACK TO Update_Org_Items_PVT;
ROLLBACK TO Update_Org_Items_PVT;
END Update_Org_Items;
l_sql_stmt := 'SELECT INVENTORY_ITEM_ID,
ITEM_CATALOG_GROUP_ID,
PRIMARY_UNIT_OF_MEASURE,
DESCRIPTION
FROM MTL_SYSTEM_ITEMS_B_KFV MSI,
MTL_PARAMETERS P
WHERE MSI.ORGANIZATION_ID = P.ORGANIZATION_ID ';
PROCEDURE Insert_Row(P_Item_Rec IN INV_ITEM_API.Item_rec_type
,x_row_Id OUT NOCOPY ROWID)
IS
CURSOR c_ego_exists IS
SELECT 'Y'
FROM FND_OBJECTS
WHERE OBJ_NAME ='EGO_ITEM';
SELECT unit_of_measure --* Bug 5192495 reverted Bug 4465182 Select translated unit of measure value
INTO l_Primary_Unit_of_Measure
FROM mtl_units_of_measure_vl
WHERE uom_code = P_Item_Rec.PRIMARY_UOM_CODE;
INSERT INTO MTL_SYSTEM_ITEMS_B(
DESCRIPTION,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SUMMARY_FLAG,
ENABLED_FLAG,
-- START_DATE_ACTIVE, Commented for Bug: 4457440
-- END_DATE_ACTIVE, Commented for Bug: 4457440
PRIMARY_UOM_CODE,
PRIMARY_UNIT_OF_MEASURE,
ALLOWED_UNITS_LOOKUP_CODE,
OVERCOMPLETION_TOLERANCE_TYPE,
OVERCOMPLETION_TOLERANCE_VALUE,
EFFECTIVITY_CONTROL,
CHECK_SHORTAGES_FLAG,
FULL_LEAD_TIME,
ORDER_COST,
MRP_SAFETY_STOCK_PERCENT,
MRP_SAFETY_STOCK_CODE,
MIN_MINMAX_QUANTITY,
MAX_MINMAX_QUANTITY,
MINIMUM_ORDER_QUANTITY,
FIXED_ORDER_QUANTITY,
FIXED_DAYS_SUPPLY,
MAXIMUM_ORDER_QUANTITY,
ATP_RULE_ID,
PICKING_RULE_ID,
RESERVABLE_TYPE,
POSITIVE_MEASUREMENT_ERROR,
NEGATIVE_MEASUREMENT_ERROR,
ENGINEERING_ECN_CODE,
ENGINEERING_ITEM_ID,
ENGINEERING_DATE,
SERVICE_STARTING_DELAY,
SERVICEABLE_COMPONENT_FLAG,
SERVICEABLE_PRODUCT_FLAG,
PAYMENT_TERMS_ID,
PREVENTIVE_MAINTENANCE_FLAG,
MATERIAL_BILLABLE_FLAG,
PRORATE_SERVICE_FLAG,
COVERAGE_SCHEDULE_ID,
SERVICE_DURATION_PERIOD_CODE,
SERVICE_DURATION,
INVOICEABLE_ITEM_FLAG,
TAX_CODE,
INVOICE_ENABLED_FLAG,
MUST_USE_APPROVED_VENDOR_FLAG,
OUTSIDE_OPERATION_FLAG,
OUTSIDE_OPERATION_UOM_TYPE,
SAFETY_STOCK_BUCKET_DAYS,
AUTO_REDUCE_MPS,
COSTING_ENABLED_FLAG,
AUTO_CREATED_CONFIG_FLAG,
CYCLE_COUNT_ENABLED_FLAG,
ITEM_TYPE,
MODEL_CONFIG_CLAUSE_NAME,
SHIP_MODEL_COMPLETE_FLAG,
MRP_PLANNING_CODE,
RETURN_INSPECTION_REQUIREMENT,
ATO_FORECAST_CONTROL,
RELEASE_TIME_FENCE_CODE,
RELEASE_TIME_FENCE_DAYS,
CONTAINER_ITEM_FLAG,
VEHICLE_ITEM_FLAG,
MAXIMUM_LOAD_WEIGHT,
MINIMUM_FILL_PERCENT,
CONTAINER_TYPE_CODE,
INTERNAL_VOLUME,
-- PRODUCT_FAMILY_ITEM_ID, - Bug 4408694
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20,
PURCHASING_TAX_CODE,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PURCHASING_ITEM_FLAG,
SHIPPABLE_ITEM_FLAG,
CUSTOMER_ORDER_FLAG,
INTERNAL_ORDER_FLAG,
INVENTORY_ITEM_FLAG,
ENG_ITEM_FLAG,
INVENTORY_ASSET_FLAG,
PURCHASING_ENABLED_FLAG,
CUSTOMER_ORDER_ENABLED_FLAG,
INTERNAL_ORDER_ENABLED_FLAG,
SO_TRANSACTIONS_FLAG,
MTL_TRANSACTIONS_ENABLED_FLAG,
STOCK_ENABLED_FLAG,
BOM_ENABLED_FLAG,
BUILD_IN_WIP_FLAG,
REVISION_QTY_CONTROL_CODE,
ITEM_CATALOG_GROUP_ID,
CATALOG_STATUS_FLAG,
RETURNABLE_FLAG,
DEFAULT_SHIPPING_ORG,
COLLATERAL_FLAG,
TAXABLE_FLAG,
QTY_RCV_EXCEPTION_CODE,
ALLOW_ITEM_DESC_UPDATE_FLAG,
INSPECTION_REQUIRED_FLAG,
RECEIPT_REQUIRED_FLAG,
MARKET_PRICE,
HAZARD_CLASS_ID,
RFQ_REQUIRED_FLAG,
QTY_RCV_TOLERANCE,
LIST_PRICE_PER_UNIT,
UN_NUMBER_ID,
PRICE_TOLERANCE_PERCENT,
ASSET_CATEGORY_ID,
ROUNDING_FACTOR,
UNIT_OF_ISSUE,
ENFORCE_SHIP_TO_LOCATION_CODE,
ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
ALLOW_UNORDERED_RECEIPTS_FLAG,
ALLOW_EXPRESS_DELIVERY_FLAG,
DAYS_EARLY_RECEIPT_ALLOWED,
DAYS_LATE_RECEIPT_ALLOWED,
RECEIPT_DAYS_EXCEPTION_CODE,
RECEIVING_ROUTING_ID,
INVOICE_CLOSE_TOLERANCE,
RECEIVE_CLOSE_TOLERANCE,
AUTO_LOT_ALPHA_PREFIX,
START_AUTO_LOT_NUMBER,
LOT_CONTROL_CODE,
SHELF_LIFE_CODE,
SHELF_LIFE_DAYS,
SERIAL_NUMBER_CONTROL_CODE,
START_AUTO_SERIAL_NUMBER,
AUTO_SERIAL_ALPHA_PREFIX,
SOURCE_TYPE,
SOURCE_ORGANIZATION_ID,
SOURCE_SUBINVENTORY,
EXPENSE_ACCOUNT,
ENCUMBRANCE_ACCOUNT,
RESTRICT_SUBINVENTORIES_CODE,
UNIT_WEIGHT,
WEIGHT_UOM_CODE,
VOLUME_UOM_CODE,
UNIT_VOLUME,
RESTRICT_LOCATORS_CODE,
LOCATION_CONTROL_CODE,
SHRINKAGE_RATE,
ACCEPTABLE_EARLY_DAYS,
PLANNING_TIME_FENCE_CODE,
DEMAND_TIME_FENCE_CODE,
LEAD_TIME_LOT_SIZE,
STD_LOT_SIZE,
CUM_MANUFACTURING_LEAD_TIME,
OVERRUN_PERCENTAGE,
MRP_CALCULATE_ATP_FLAG,
ACCEPTABLE_RATE_INCREASE,
ACCEPTABLE_RATE_DECREASE,
CUMULATIVE_TOTAL_LEAD_TIME,
PLANNING_TIME_FENCE_DAYS,
DEMAND_TIME_FENCE_DAYS,
END_ASSEMBLY_PEGGING_FLAG,
REPETITIVE_PLANNING_FLAG,
PLANNING_EXCEPTION_SET,
BOM_ITEM_TYPE,
PICK_COMPONENTS_FLAG,
REPLENISH_TO_ORDER_FLAG,
BASE_ITEM_ID,
ATP_COMPONENTS_FLAG,
ATP_FLAG,
FIXED_LEAD_TIME,
VARIABLE_LEAD_TIME,
WIP_SUPPLY_LOCATOR_ID,
WIP_SUPPLY_TYPE,
WIP_SUPPLY_SUBINVENTORY,
COST_OF_SALES_ACCOUNT,
SALES_ACCOUNT,
DEFAULT_INCLUDE_IN_ROLLUP_FLAG,
INVENTORY_ITEM_STATUS_CODE,
INVENTORY_PLANNING_CODE,
PLANNER_CODE,
PLANNING_MAKE_BUY_CODE,
FIXED_LOT_MULTIPLIER,
ROUNDING_CONTROL_TYPE,
CARRYING_COST,
POSTPROCESSING_LEAD_TIME,
PREPROCESSING_LEAD_TIME,
BUYER_ID,
ACCOUNTING_RULE_ID,
INVOICING_RULE_ID,
OVER_SHIPMENT_TOLERANCE,
UNDER_SHIPMENT_TOLERANCE,
OVER_RETURN_TOLERANCE,
UNDER_RETURN_TOLERANCE,
EQUIPMENT_TYPE,
RECOVERED_PART_DISP_CODE,
DEFECT_TRACKING_ON_FLAG,
EVENT_FLAG,
ELECTRONIC_FLAG,
DOWNLOADABLE_FLAG,
VOL_DISCOUNT_EXEMPT_FLAG,
COUPON_EXEMPT_FLAG,
COMMS_NL_TRACKABLE_FLAG,
ASSET_CREATION_CODE,
COMMS_ACTIVATION_REQD_FLAG,
ORDERABLE_ON_WEB_FLAG,
BACK_ORDERABLE_FLAG,
WEB_STATUS,
INDIVISIBLE_FLAG,
DIMENSION_UOM_CODE,
UNIT_LENGTH,
UNIT_WIDTH,
UNIT_HEIGHT,
BULK_PICKED_FLAG,
LOT_STATUS_ENABLED,
DEFAULT_LOT_STATUS_ID,
SERIAL_STATUS_ENABLED,
DEFAULT_SERIAL_STATUS_ID,
LOT_SPLIT_ENABLED,
LOT_MERGE_ENABLED,
INVENTORY_CARRY_PENALTY,
OPERATION_SLACK_PENALTY,
FINANCING_ALLOWED_FLAG,
EAM_ITEM_TYPE,
EAM_ACTIVITY_TYPE_CODE,
EAM_ACTIVITY_CAUSE_CODE,
EAM_ACT_NOTIFICATION_FLAG,
EAM_ACT_SHUTDOWN_STATUS,
DUAL_UOM_CONTROL,
SECONDARY_UOM_CODE,
DUAL_UOM_DEVIATION_HIGH,
DUAL_UOM_DEVIATION_LOW,
SERVICE_ITEM_FLAG,
VENDOR_WARRANTY_FLAG,
USAGE_ITEM_FLAG,
CONTRACT_ITEM_TYPE_CODE,
SUBSCRIPTION_DEPEND_FLAG,
SERV_REQ_ENABLED_CODE,
SERV_BILLING_ENABLED_FLAG,
SERV_IMPORTANCE_LEVEL,
PLANNED_INV_POINT_FLAG,
LOT_TRANSLATE_ENABLED,
DEFAULT_SO_SOURCE_TYPE,
CREATE_SUPPLY_FLAG,
SUBSTITUTION_WINDOW_CODE,
SUBSTITUTION_WINDOW_DAYS,
IB_ITEM_INSTANCE_CLASS,
CONFIG_MODEL_TYPE,
--Added as part of 11.5.9 ENH
LOT_SUBSTITUTION_ENABLED,
MINIMUM_LICENSE_QUANTITY,
EAM_ACTIVITY_SOURCE_CODE,
--Added as part of 11.5.10 ENH
TRACKING_QUANTITY_IND ,
ONT_PRICING_QTY_SOURCE,
SECONDARY_DEFAULT_IND ,
OPTION_SPECIFIC_SOURCED,
CONFIG_ORGS,
CONFIG_MATCH,
SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5,
SEGMENT6,
SEGMENT7,
SEGMENT8,
SEGMENT9,
SEGMENT10,
SEGMENT11,
SEGMENT12,
SEGMENT13,
SEGMENT14,
SEGMENT15,
SEGMENT16,
SEGMENT17,
SEGMENT18,
SEGMENT19,
SEGMENT20,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
LIFECYCLE_ID,
CURRENT_PHASE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN ,
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 ,
EXCLUDE_FROM_BUDGET_FLAG ,
DAYS_TGT_INV_SUPPLY ,
DAYS_TGT_INV_WINDOW ,
DAYS_MAX_INV_SUPPLY ,
DAYS_MAX_INV_WINDOW ,
DRP_PLANNED_FLAG ,
CRITICAL_COMPONENT_FLAG ,
CONTINOUS_TRANSFER ,
CONVERGENCE ,
DIVERGENCE
/* Start Bug 3713912 */--Added For R12 ENH
,LOT_DIVISIBLE_FLAG,
GRADE_CONTROL_FLAG,
DEFAULT_GRADE,
CHILD_LOT_FLAG,
PARENT_CHILD_GENERATION_FLAG,
CHILD_LOT_PREFIX,
CHILD_LOT_STARTING_NUMBER,
CHILD_LOT_VALIDATION_FLAG,
COPY_LOT_ATTRIBUTE_FLAG,
RECIPE_ENABLED_FLAG,
PROCESS_QUALITY_ENABLED_FLAG,
PROCESS_EXECUTION_ENABLED_FLAG,
PROCESS_COSTING_ENABLED_FLAG,
PROCESS_SUPPLY_SUBINVENTORY,
PROCESS_SUPPLY_LOCATOR_ID,
PROCESS_YIELD_SUBINVENTORY,
PROCESS_YIELD_LOCATOR_ID,
HAZARDOUS_MATERIAL_FLAG,
CAS_NUMBER,
RETEST_INTERVAL,
EXPIRATION_ACTION_INTERVAL,
EXPIRATION_ACTION_CODE,
MATURITY_DAYS,
HOLD_DAYS,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
ATTRIBUTE21,
ATTRIBUTE22,
ATTRIBUTE23,
ATTRIBUTE24,
ATTRIBUTE25,
ATTRIBUTE26,
ATTRIBUTE27,
ATTRIBUTE28,
ATTRIBUTE29,
ATTRIBUTE30,
/* End Bug 3713912 */
CHARGE_PERIODICITY_CODE,
REPAIR_LEADTIME,
REPAIR_YIELD ,
PREPOSITION_POINT,
REPAIR_PROGRAM,
SUBCONTRACTING_COMPONENT,
OUTSOURCED_ASSEMBLY,
/* Bug 4224512 Updating the object version number - Anmurali */
OBJECT_VERSION_NUMBER,
-- Fix for Bug#6644711
DEFAULT_MATERIAL_STATUS_ID,
-- Serial_Tagging Enh -- bug 9913552
SERIAL_TAGGING_FLAG
)
VALUES (
ltrim(rtrim(P_Item_Rec.DESCRIPTION)),
P_Item_Rec.INVENTORY_ITEM_ID,
P_Item_Rec.ORGANIZATION_ID,
P_Item_Rec.SUMMARY_FLAG,
P_Item_Rec.ENABLED_FLAG,
-- P_Item_Rec.START_DATE_ACTIVE, Commented for Bug: 4457440
-- P_Item_Rec.END_DATE_ACTIVE, Commented for Bug: 4457440
P_Item_Rec.PRIMARY_UOM_CODE,
l_Primary_Unit_of_Measure,
P_Item_Rec.ALLOWED_UNITS_LOOKUP_CODE,
P_Item_Rec.OVERCOMPLETION_TOLERANCE_TYPE,
P_Item_Rec.OVERCOMPLETION_TOLERANCE_VALUE,
P_Item_Rec.EFFECTIVITY_CONTROL,
P_Item_Rec.CHECK_SHORTAGES_FLAG,
P_Item_Rec.FULL_LEAD_TIME,
P_Item_Rec.ORDER_COST,
P_Item_Rec.MRP_SAFETY_STOCK_PERCENT,
P_Item_Rec.MRP_SAFETY_STOCK_CODE,
P_Item_Rec.MIN_MINMAX_QUANTITY,
P_Item_Rec.MAX_MINMAX_QUANTITY,
P_Item_Rec.MINIMUM_ORDER_QUANTITY,
P_Item_Rec.FIXED_ORDER_QUANTITY,
P_Item_Rec.FIXED_DAYS_SUPPLY,
P_Item_Rec.MAXIMUM_ORDER_QUANTITY,
P_Item_Rec.ATP_RULE_ID,
P_Item_Rec.PICKING_RULE_ID,
P_Item_Rec.RESERVABLE_TYPE,
P_Item_Rec.POSITIVE_MEASUREMENT_ERROR,
P_Item_Rec.NEGATIVE_MEASUREMENT_ERROR,
P_Item_Rec.ENGINEERING_ECN_CODE,
P_Item_Rec.ENGINEERING_ITEM_ID,
P_Item_Rec.ENGINEERING_DATE,
P_Item_Rec.SERVICE_STARTING_DELAY,
P_Item_Rec.SERVICEABLE_COMPONENT_FLAG,
P_Item_Rec.SERVICEABLE_PRODUCT_FLAG,
P_Item_Rec.PAYMENT_TERMS_ID,
P_Item_Rec.PREVENTIVE_MAINTENANCE_FLAG,
P_Item_Rec.MATERIAL_BILLABLE_FLAG,
P_Item_Rec.PRORATE_SERVICE_FLAG,
P_Item_Rec.COVERAGE_SCHEDULE_ID,
P_Item_Rec.SERVICE_DURATION_PERIOD_CODE,
P_Item_Rec.SERVICE_DURATION,
P_Item_Rec.INVOICEABLE_ITEM_FLAG,
P_Item_Rec.TAX_CODE,
P_Item_Rec.INVOICE_ENABLED_FLAG,
P_Item_Rec.MUST_USE_APPROVED_VENDOR_FLAG,
P_Item_Rec.OUTSIDE_OPERATION_FLAG,
P_Item_Rec.OUTSIDE_OPERATION_UOM_TYPE,
P_Item_Rec.SAFETY_STOCK_BUCKET_DAYS,
P_Item_Rec.AUTO_REDUCE_MPS,
P_Item_Rec.COSTING_ENABLED_FLAG,
P_Item_Rec.AUTO_CREATED_CONFIG_FLAG,
P_Item_Rec.CYCLE_COUNT_ENABLED_FLAG,
P_Item_Rec.ITEM_TYPE,
P_Item_Rec.MODEL_CONFIG_CLAUSE_NAME,
P_Item_Rec.SHIP_MODEL_COMPLETE_FLAG,
P_Item_Rec.MRP_PLANNING_CODE,
P_Item_Rec.RETURN_INSPECTION_REQUIREMENT,
P_Item_Rec.ATO_FORECAST_CONTROL,
P_Item_Rec.RELEASE_TIME_FENCE_CODE,
P_Item_Rec.RELEASE_TIME_FENCE_DAYS,
P_Item_Rec.CONTAINER_ITEM_FLAG,
P_Item_Rec.VEHICLE_ITEM_FLAG,
P_Item_Rec.MAXIMUM_LOAD_WEIGHT,
P_Item_Rec.MINIMUM_FILL_PERCENT,
P_Item_Rec.CONTAINER_TYPE_CODE,
P_Item_Rec.INTERNAL_VOLUME,
-- P_Item_Rec.PRODUCT_FAMILY_ITEM_ID, - Bug 4408694
P_Item_Rec.GLOBAL_ATTRIBUTE_CATEGORY,
P_Item_Rec.GLOBAL_ATTRIBUTE1,
P_Item_Rec.GLOBAL_ATTRIBUTE2,
P_Item_Rec.GLOBAL_ATTRIBUTE3,
P_Item_Rec.GLOBAL_ATTRIBUTE4,
P_Item_Rec.GLOBAL_ATTRIBUTE5,
P_Item_Rec.GLOBAL_ATTRIBUTE6,
P_Item_Rec.GLOBAL_ATTRIBUTE7,
P_Item_Rec.GLOBAL_ATTRIBUTE8,
P_Item_Rec.GLOBAL_ATTRIBUTE9,
P_Item_Rec.GLOBAL_ATTRIBUTE10,
P_Item_Rec.GLOBAL_ATTRIBUTE11,
P_Item_Rec.GLOBAL_ATTRIBUTE12,
P_Item_Rec.GLOBAL_ATTRIBUTE13,
P_Item_Rec.GLOBAL_ATTRIBUTE14,
P_Item_Rec.GLOBAL_ATTRIBUTE15,
P_Item_Rec.GLOBAL_ATTRIBUTE16,
P_Item_Rec.GLOBAL_ATTRIBUTE17,
P_Item_Rec.GLOBAL_ATTRIBUTE18,
P_Item_Rec.GLOBAL_ATTRIBUTE19,
P_Item_Rec.GLOBAL_ATTRIBUTE20,
P_Item_Rec.PURCHASING_TAX_CODE,
P_Item_Rec.ATTRIBUTE6,
P_Item_Rec.ATTRIBUTE7,
P_Item_Rec.ATTRIBUTE8,
P_Item_Rec.ATTRIBUTE9,
P_Item_Rec.ATTRIBUTE10,
P_Item_Rec.ATTRIBUTE11,
P_Item_Rec.ATTRIBUTE12,
P_Item_Rec.ATTRIBUTE13,
P_Item_Rec.ATTRIBUTE14,
P_Item_Rec.ATTRIBUTE15,
P_Item_Rec.PURCHASING_ITEM_FLAG,
P_Item_Rec.SHIPPABLE_ITEM_FLAG,
P_Item_Rec.CUSTOMER_ORDER_FLAG,
P_Item_Rec.INTERNAL_ORDER_FLAG,
P_Item_Rec.INVENTORY_ITEM_FLAG,
P_Item_Rec.ENG_ITEM_FLAG,
P_Item_Rec.INVENTORY_ASSET_FLAG,
P_Item_Rec.PURCHASING_ENABLED_FLAG,
P_Item_Rec.CUSTOMER_ORDER_ENABLED_FLAG,
P_Item_Rec.INTERNAL_ORDER_ENABLED_FLAG,
P_Item_Rec.SO_TRANSACTIONS_FLAG,
P_Item_Rec.MTL_TRANSACTIONS_ENABLED_FLAG,
P_Item_Rec.STOCK_ENABLED_FLAG,
P_Item_Rec.BOM_ENABLED_FLAG,
P_Item_Rec.BUILD_IN_WIP_FLAG,
P_Item_Rec.REVISION_QTY_CONTROL_CODE,
P_Item_Rec.ITEM_CATALOG_GROUP_ID, --Bug: 2805253 NVL(P_Item_Rec.ITEM_CATALOG_GROUP_ID,1),
P_Item_Rec.CATALOG_STATUS_FLAG,
P_Item_Rec.RETURNABLE_FLAG,
P_Item_Rec.DEFAULT_SHIPPING_ORG,
P_Item_Rec.COLLATERAL_FLAG,
P_Item_Rec.TAXABLE_FLAG,
P_Item_Rec.QTY_RCV_EXCEPTION_CODE,
P_Item_Rec.ALLOW_ITEM_DESC_UPDATE_FLAG,
P_Item_Rec.INSPECTION_REQUIRED_FLAG,
P_Item_Rec.RECEIPT_REQUIRED_FLAG,
P_Item_Rec.MARKET_PRICE,
P_Item_Rec.HAZARD_CLASS_ID,
P_Item_Rec.RFQ_REQUIRED_FLAG,
P_Item_Rec.QTY_RCV_TOLERANCE,
P_Item_Rec.LIST_PRICE_PER_UNIT,
P_Item_Rec.UN_NUMBER_ID,
P_Item_Rec.PRICE_TOLERANCE_PERCENT,
P_Item_Rec.ASSET_CATEGORY_ID,
P_Item_Rec.ROUNDING_FACTOR,
P_Item_Rec.UNIT_OF_ISSUE,
P_Item_Rec.ENFORCE_SHIP_TO_LOCATION_CODE,
P_Item_Rec.ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
P_Item_Rec.ALLOW_UNORDERED_RECEIPTS_FLAG,
P_Item_Rec.ALLOW_EXPRESS_DELIVERY_FLAG,
P_Item_Rec.DAYS_EARLY_RECEIPT_ALLOWED,
P_Item_Rec.DAYS_LATE_RECEIPT_ALLOWED,
P_Item_Rec.RECEIPT_DAYS_EXCEPTION_CODE,
P_Item_Rec.RECEIVING_ROUTING_ID,
P_Item_Rec.INVOICE_CLOSE_TOLERANCE,
P_Item_Rec.RECEIVE_CLOSE_TOLERANCE,
P_Item_Rec.AUTO_LOT_ALPHA_PREFIX,
P_Item_Rec.START_AUTO_LOT_NUMBER,
P_Item_Rec.LOT_CONTROL_CODE,
P_Item_Rec.SHELF_LIFE_CODE,
P_Item_Rec.SHELF_LIFE_DAYS,
P_Item_Rec.SERIAL_NUMBER_CONTROL_CODE,
P_Item_Rec.START_AUTO_SERIAL_NUMBER,
P_Item_Rec.AUTO_SERIAL_ALPHA_PREFIX,
P_Item_Rec.SOURCE_TYPE,
P_Item_Rec.SOURCE_ORGANIZATION_ID,
P_Item_Rec.SOURCE_SUBINVENTORY,
P_Item_Rec.EXPENSE_ACCOUNT,
P_Item_Rec.ENCUMBRANCE_ACCOUNT,
P_Item_Rec.RESTRICT_SUBINVENTORIES_CODE,
P_Item_Rec.UNIT_WEIGHT,
P_Item_Rec.WEIGHT_UOM_CODE,
P_Item_Rec.VOLUME_UOM_CODE,
P_Item_Rec.UNIT_VOLUME,
P_Item_Rec.RESTRICT_LOCATORS_CODE,
P_Item_Rec.LOCATION_CONTROL_CODE,
P_Item_Rec.SHRINKAGE_RATE,
P_Item_Rec.ACCEPTABLE_EARLY_DAYS,
P_Item_Rec.PLANNING_TIME_FENCE_CODE,
P_Item_Rec.DEMAND_TIME_FENCE_CODE,
P_Item_Rec.LEAD_TIME_LOT_SIZE,
P_Item_Rec.STD_LOT_SIZE,
P_Item_Rec.CUM_MANUFACTURING_LEAD_TIME,
P_Item_Rec.OVERRUN_PERCENTAGE,
P_Item_Rec.MRP_CALCULATE_ATP_FLAG,
P_Item_Rec.ACCEPTABLE_RATE_INCREASE,
P_Item_Rec.ACCEPTABLE_RATE_DECREASE,
P_Item_Rec.CUMULATIVE_TOTAL_LEAD_TIME,
P_Item_Rec.PLANNING_TIME_FENCE_DAYS,
P_Item_Rec.DEMAND_TIME_FENCE_DAYS,
P_Item_Rec.END_ASSEMBLY_PEGGING_FLAG,
P_Item_Rec.REPETITIVE_PLANNING_FLAG,
P_Item_Rec.PLANNING_EXCEPTION_SET,
P_Item_Rec.BOM_ITEM_TYPE,
P_Item_Rec.PICK_COMPONENTS_FLAG,
P_Item_Rec.REPLENISH_TO_ORDER_FLAG,
P_Item_Rec.BASE_ITEM_ID,
P_Item_Rec.ATP_COMPONENTS_FLAG,
P_Item_Rec.ATP_FLAG,
P_Item_Rec.FIXED_LEAD_TIME,
P_Item_Rec.VARIABLE_LEAD_TIME,
P_Item_Rec.WIP_SUPPLY_LOCATOR_ID,
P_Item_Rec.WIP_SUPPLY_TYPE,
P_Item_Rec.WIP_SUPPLY_SUBINVENTORY,
P_Item_Rec.COST_OF_SALES_ACCOUNT,
P_Item_Rec.SALES_ACCOUNT,
P_Item_Rec.DEFAULT_INCLUDE_IN_ROLLUP_FLAG,
P_Item_Rec.INVENTORY_ITEM_STATUS_CODE,
P_Item_Rec.INVENTORY_PLANNING_CODE,
P_Item_Rec.PLANNER_CODE,
P_Item_Rec.PLANNING_MAKE_BUY_CODE,
P_Item_Rec.FIXED_LOT_MULTIPLIER,
P_Item_Rec.ROUNDING_CONTROL_TYPE,
P_Item_Rec.CARRYING_COST,
P_Item_Rec.POSTPROCESSING_LEAD_TIME,
P_Item_Rec.PREPROCESSING_LEAD_TIME,
P_Item_Rec.BUYER_ID,
P_Item_Rec.ACCOUNTING_RULE_ID,
P_Item_Rec.INVOICING_RULE_ID,
P_Item_Rec.OVER_SHIPMENT_TOLERANCE,
P_Item_Rec.UNDER_SHIPMENT_TOLERANCE,
P_Item_Rec.OVER_RETURN_TOLERANCE,
P_Item_Rec.UNDER_RETURN_TOLERANCE,
P_Item_Rec.EQUIPMENT_TYPE,
P_Item_Rec.RECOVERED_PART_DISP_CODE,
P_Item_Rec.DEFECT_TRACKING_ON_FLAG,
P_Item_Rec.EVENT_FLAG,
P_Item_Rec.ELECTRONIC_FLAG,
P_Item_Rec.DOWNLOADABLE_FLAG,
P_Item_Rec.VOL_DISCOUNT_EXEMPT_FLAG,
P_Item_Rec.COUPON_EXEMPT_FLAG,
P_Item_Rec.COMMS_NL_TRACKABLE_FLAG,
P_Item_Rec.ASSET_CREATION_CODE,
P_Item_Rec.COMMS_ACTIVATION_REQD_FLAG,
P_Item_Rec.ORDERABLE_ON_WEB_FLAG,
P_Item_Rec.BACK_ORDERABLE_FLAG,
P_Item_Rec.WEB_STATUS,
P_Item_Rec.INDIVISIBLE_FLAG,
P_Item_Rec.DIMENSION_UOM_CODE,
P_Item_Rec.UNIT_LENGTH,
P_Item_Rec.UNIT_WIDTH,
P_Item_Rec.UNIT_HEIGHT,
P_Item_Rec.BULK_PICKED_FLAG,
P_Item_Rec.LOT_STATUS_ENABLED,
P_Item_Rec.DEFAULT_LOT_STATUS_ID,
P_Item_Rec.SERIAL_STATUS_ENABLED,
P_Item_Rec.DEFAULT_SERIAL_STATUS_ID,
P_Item_Rec.LOT_SPLIT_ENABLED,
P_Item_Rec.LOT_MERGE_ENABLED,
P_Item_Rec.INVENTORY_CARRY_PENALTY,
P_Item_Rec.OPERATION_SLACK_PENALTY,
P_Item_Rec.FINANCING_ALLOWED_FLAG,
P_Item_Rec.EAM_ITEM_TYPE,
P_Item_Rec.EAM_ACTIVITY_TYPE_CODE,
P_Item_Rec.EAM_ACTIVITY_CAUSE_CODE,
P_Item_Rec.EAM_ACT_NOTIFICATION_FLAG,
P_Item_Rec.EAM_ACT_SHUTDOWN_STATUS,
P_Item_Rec.DUAL_UOM_CONTROL,
P_Item_Rec.SECONDARY_UOM_CODE,
P_Item_Rec.DUAL_UOM_DEVIATION_HIGH,
P_Item_Rec.DUAL_UOM_DEVIATION_LOW,
l_SERVICE_ITEM_FLAG,
l_VENDOR_WARRANTY_FLAG,
l_USAGE_ITEM_FLAG,
P_Item_Rec.CONTRACT_ITEM_TYPE_CODE,
P_Item_Rec.SUBSCRIPTION_DEPEND_FLAG,
P_Item_Rec.SERV_REQ_ENABLED_CODE,
P_Item_Rec.SERV_BILLING_ENABLED_FLAG,
P_Item_Rec.SERV_IMPORTANCE_LEVEL,
P_Item_Rec.PLANNED_INV_POINT_FLAG,
P_Item_Rec.LOT_TRANSLATE_ENABLED,
P_Item_Rec.DEFAULT_SO_SOURCE_TYPE,
P_Item_Rec.CREATE_SUPPLY_FLAG,
P_Item_Rec.SUBSTITUTION_WINDOW_CODE,
P_Item_Rec.SUBSTITUTION_WINDOW_DAYS,
P_Item_Rec.IB_ITEM_INSTANCE_CLASS,
P_Item_Rec.CONFIG_MODEL_TYPE,
P_Item_Rec.LOT_SUBSTITUTION_ENABLED,
P_Item_Rec.MINIMUM_LICENSE_QUANTITY,
P_Item_Rec.EAM_ACTIVITY_SOURCE_CODE,
-- Added for 11.5.10
P_Item_Rec.TRACKING_QUANTITY_IND ,
P_Item_Rec.ONT_PRICING_QTY_SOURCE,
P_Item_Rec.SECONDARY_DEFAULT_IND ,
P_Item_Rec.OPTION_SPECIFIC_SOURCED,
p_Item_rec.CONFIG_ORGS,
p_Item_rec.CONFIG_MATCH,
P_Item_Rec.SEGMENT1,
P_Item_Rec.SEGMENT2,
P_Item_Rec.SEGMENT3,
P_Item_Rec.SEGMENT4,
P_Item_Rec.SEGMENT5,
P_Item_Rec.SEGMENT6,
P_Item_Rec.SEGMENT7,
P_Item_Rec.SEGMENT8,
P_Item_Rec.SEGMENT9,
P_Item_Rec.SEGMENT10,
P_Item_Rec.SEGMENT11,
P_Item_Rec.SEGMENT12,
P_Item_Rec.SEGMENT13,
P_Item_Rec.SEGMENT14,
P_Item_Rec.SEGMENT15,
P_Item_Rec.SEGMENT16,
P_Item_Rec.SEGMENT17,
P_Item_Rec.SEGMENT18,
P_Item_Rec.SEGMENT19,
P_Item_Rec.SEGMENT20,
P_Item_Rec.ATTRIBUTE_CATEGORY,
P_Item_Rec.ATTRIBUTE1,
P_Item_Rec.ATTRIBUTE2,
P_Item_Rec.ATTRIBUTE3,
P_Item_Rec.ATTRIBUTE4,
P_Item_Rec.ATTRIBUTE5,
P_Item_Rec.LIFECYCLE_ID,
P_Item_Rec.CURRENT_PHASE_ID,
P_Item_Rec.CREATION_DATE,
P_Item_Rec.CREATED_BY,
P_Item_Rec.LAST_UPDATE_DATE,
P_Item_Rec.LAST_UPDATED_BY,
P_Item_Rec.LAST_UPDATE_LOGIN,
P_Item_Rec.VMI_MINIMUM_UNITS,
P_Item_Rec.VMI_MINIMUM_DAYS,
P_Item_Rec.VMI_MAXIMUM_UNITS,
P_Item_Rec.VMI_MAXIMUM_DAYS ,
P_Item_Rec.VMI_FIXED_ORDER_QUANTITY ,
P_Item_Rec.SO_AUTHORIZATION_FLAG ,
P_Item_Rec.CONSIGNED_FLAG ,
P_Item_Rec.ASN_AUTOEXPIRE_FLAG ,
P_Item_Rec.VMI_FORECAST_TYPE ,
P_Item_Rec.FORECAST_HORIZON ,
P_Item_Rec.EXCLUDE_FROM_BUDGET_FLAG ,
P_Item_Rec.DAYS_TGT_INV_SUPPLY ,
P_Item_Rec.DAYS_TGT_INV_WINDOW ,
P_Item_Rec.DAYS_MAX_INV_SUPPLY ,
P_Item_Rec.DAYS_MAX_INV_WINDOW ,
P_Item_Rec.DRP_PLANNED_FLAG ,
P_Item_Rec.CRITICAL_COMPONENT_FLAG ,
P_Item_Rec.CONTINOUS_TRANSFER ,
P_Item_Rec.CONVERGENCE ,
P_Item_Rec.DIVERGENCE,
/* Start Bug 3713912 */--Added for R12
P_Item_rec.LOT_DIVISIBLE_FLAG,
P_Item_Rec.GRADE_CONTROL_FLAG,
P_Item_Rec.DEFAULT_GRADE,
P_Item_Rec.CHILD_LOT_FLAG,
P_Item_Rec.PARENT_CHILD_GENERATION_FLAG,
P_Item_Rec.CHILD_LOT_PREFIX,
P_Item_Rec.CHILD_LOT_STARTING_NUMBER,
P_Item_Rec.CHILD_LOT_VALIDATION_FLAG,
P_Item_Rec.COPY_LOT_ATTRIBUTE_FLAG,
P_Item_Rec.RECIPE_ENABLED_FLAG,
P_Item_Rec.PROCESS_QUALITY_ENABLED_FLAG,
P_Item_Rec.PROCESS_EXECUTION_ENABLED_FLAG,
P_Item_Rec.PROCESS_COSTING_ENABLED_FLAG,
P_Item_Rec.PROCESS_SUPPLY_SUBINVENTORY,
P_Item_Rec.PROCESS_SUPPLY_LOCATOR_ID,
P_Item_Rec.PROCESS_YIELD_SUBINVENTORY,
P_Item_Rec.PROCESS_YIELD_LOCATOR_ID,
P_Item_Rec.HAZARDOUS_MATERIAL_FLAG,
P_Item_Rec.CAS_NUMBER,
P_Item_Rec.RETEST_INTERVAL,
P_Item_Rec.EXPIRATION_ACTION_INTERVAL,
P_Item_Rec.EXPIRATION_ACTION_CODE,
P_Item_Rec.MATURITY_DAYS,
P_Item_Rec.HOLD_DAYS,
P_Item_Rec.ATTRIBUTE16,
P_Item_Rec.ATTRIBUTE17,
P_Item_Rec.ATTRIBUTE18,
P_Item_Rec.ATTRIBUTE19,
P_Item_Rec.ATTRIBUTE20,
P_Item_Rec.ATTRIBUTE21,
P_Item_Rec.ATTRIBUTE22,
P_Item_Rec.ATTRIBUTE23,
P_Item_Rec.ATTRIBUTE24,
P_Item_Rec.ATTRIBUTE25,
P_Item_Rec.ATTRIBUTE26,
P_Item_Rec.ATTRIBUTE27,
P_Item_Rec.ATTRIBUTE28,
P_Item_Rec.ATTRIBUTE29,
P_Item_Rec.ATTRIBUTE30,
/* End Bug 3713912 */
p_Item_rec.CHARGE_PERIODICITY_CODE,
p_Item_rec.REPAIR_LEADTIME,
p_Item_rec.REPAIR_YIELD,
p_Item_rec.PREPOSITION_POINT,
p_Item_rec.REPAIR_PROGRAM,
p_Item_rec.SUBCONTRACTING_COMPONENT,
p_Item_rec.OUTSOURCED_ASSEMBLY,
/* Bug 4224512 Updating the object version number - Anmurali */
1,
-- Fix for Bug#6644711
p_Item_rec.DEFAULT_MATERIAL_STATUS_ID,
--Serial_Tagging Enh -- bug 9913552
p_Item_rec.SERIAL_TAGGING_FLAG
)
RETURNING ROWID INTO x_row_Id;
INSERT INTO MTL_SYSTEM_ITEMS_TL(
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
LANGUAGE,
SOURCE_LANG,
DESCRIPTION,
LONG_DESCRIPTION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN )
SELECT
P_Item_Rec.INVENTORY_ITEM_ID,
P_Item_Rec.ORGANIZATION_ID,
L.LANGUAGE_CODE,
USERENV('LANG'),
ltrim(rtrim(P_Item_Rec.DESCRIPTION)),
ltrim(rtrim(P_Item_Rec.LONG_DESCRIPTION)),
P_Item_Rec.LAST_UPDATE_DATE,
P_Item_Rec.LAST_UPDATED_BY,
P_Item_Rec.CREATION_DATE,
P_Item_Rec.CREATED_BY,
P_Item_Rec.LAST_UPDATE_LOGIN
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B')
AND NOT EXISTS
( SELECT NULL
FROM MTL_SYSTEM_ITEMS_TL T
WHERE T.INVENTORY_ITEM_ID = P_Item_Rec.INVENTORY_ITEM_ID
AND T.ORGANIZATION_ID = P_Item_Rec.ORGANIZATION_ID
AND T.LANGUAGE = L.LANGUAGE_CODE);
INSERT INTO MTL_SYSTEM_ITEMS_TL (
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
LANGUAGE,
SOURCE_LANG,
DESCRIPTION,
LONG_DESCRIPTION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
SELECT
P_Item_Rec.INVENTORY_ITEM_ID,
P_Item_Rec.ORGANIZATION_ID,
MSI.LANGUAGE,
MSI.SOURCE_LANG,
ltrim(rtrim(MSI.DESCRIPTION)),
ltrim(rtrim(MSI.LONG_DESCRIPTION)),
P_Item_Rec.LAST_UPDATE_DATE,
P_Item_Rec.LAST_UPDATED_BY,
P_Item_Rec.CREATION_DATE,
P_Item_Rec.CREATED_BY,
P_Item_Rec.LAST_UPDATE_LOGIN
FROM
MTL_SYSTEM_ITEMS_TL MSI,
MTL_PARAMETERS MP
WHERE
MSI.INVENTORY_ITEM_ID = P_Item_Rec.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
AND MP.ORGANIZATION_ID = P_Item_Rec.ORGANIZATION_ID;
' ENI_ITEMS_STAR_PKG.Insert_Items_In_Star( '||
' p_api_version => 1.0 '||
' , p_init_msg_list => FND_API.g_TRUE '||
' , p_inventory_item_id => :l_Inventory_Item_ID '||
' , p_organization_id => :l_Organization_ID '||
' , x_return_status => :l_return_status '||
' , x_msg_count => :l_msg_count '||
' , x_msg_data => :l_msg_data ); '||
SELECT party_id INTO l_party_id
FROM EGO_USER_V
WHERE USER_ID = FND_GLOBAL.User_ID;
END Insert_Row;
PROCEDURE Update_Row(P_Item_Rec IN INV_ITEM_API.Item_rec_type)
IS
l_return_status VARCHAR2(1);
INV_ITEM_PVT.Update_Org_Items(
p_init_msg_list => fnd_api.g_TRUE
,p_commit => fnd_api.g_FALSE
,p_lock_rows => fnd_api.g_FALSE
,p_validation_level => fnd_api.g_VALID_LEVEL_FULL
,p_Item_rec => P_Item_Rec
,p_validate_Master => fnd_api.g_FALSE
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
END Update_Row;
SELECT muomv.UOM_CLASS
FROM MTL_UNITS_OF_MEASURE_VL muomv, mtl_system_items_b msib
WHERE muomv.uom_code = msib.secondary_uom_code
AND msib.inventory_item_id = Vinventory_item_id
AND msib.secondary_uom_code IS NOT NULL
AND ROWNUM = 1;
,P_Event IN VARCHAR2 DEFAULT 'INSERT'
,x_row_Id OUT NOCOPY ROWID
,P_Default_Move_Order_Sub_Inv IN VARCHAR2 -- Item Transaction Defaults for 11.5.9
,P_Default_Receiving_Sub_Inv IN VARCHAR2
,P_Default_Shipping_Sub_Inv IN VARCHAR2)
IS
Cursor c_get_uom_class(cp_uom mtl_units_of_measure_vl.unit_of_measure%TYPE
/* Bug 3713912 */ ,cp_uom_code mtl_units_of_measure_vl.uom_code%TYPE) IS
SELECT UOM_CLASS
FROM MTL_UNITS_OF_MEASURE_VL
WHERE UNIT_OF_MEASURE = cp_uom
OR uom_code = cp_uom_code;
SELECT COUNT(1)
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID = cp_Item_Id
AND ORGANIZATION_ID = cp_org_id;
INSERT_ROW(P_Item_Rec => P_Item_Rec,
x_row_id => x_row_Id);
INVIDIT2.Table_Inserts (
X_EVENT => P_Event,
X_ITEM_ID => P_Item_Rec.inventory_item_id,
X_ORG_ID => P_Item_rec.organization_id,
X_MASTER_ORG_ID => P_Master_Org_Id,
X_STATUS_CODE => P_Item_rec.inventory_item_status_code,
X_INVENTORY_ITEM_FLAG => P_Item_rec.inventory_item_flag,
X_PURCHASING_ITEM_FLAG => P_Item_rec.purchasing_item_flag,
X_INTERNAL_ORDER_FLAG => P_Item_rec.internal_order_flag,
X_MRP_PLANNING_CODE => P_Item_rec.mrp_planning_code,
X_SERVICEABLE_PRODUCT_FLAG => P_Item_rec.serviceable_product_flag,
X_COSTING_ENABLED_FLAG => P_Item_rec.costing_enabled_flag,
X_ENG_ITEM_FLAG => P_Item_rec.eng_item_flag,
X_CUSTOMER_ORDER_FLAG => P_Item_rec.customer_order_flag,
X_EAM_ITEM_TYPE => P_Item_rec.eam_item_type,
X_CONTRACT_ITEM_TYPE_CODE => P_Item_rec.contract_item_type_code,
P_FOLDER_CATEGORY_SET_ID => P_Category_set_id,
P_FOLDER_ITEM_CATEGORY_ID => l_folder_item_cat_id,
X_ALLOWED_UNIT_CODE => P_Item_rec.allowed_units_lookup_code,
X_PRIMARY_UOM => l_primary_uom,
X_PRIMARY_UOM_CODE => l_primary_uom_code,
X_PRIMARY_UOM_CLASS => l_rec_uom_class,
X_INV_INSTALL => P_inv_install,
X_LAST_UPDATED_BY => P_Item_rec.last_updated_by,
X_LAST_UPDATE_LOGIN => P_Item_rec.last_update_login,
X_ITEM_CATALOG_GROUP_ID => -1
,P_Default_Move_Order_Sub_Inv => P_Default_Move_Order_Sub_Inv
,P_Default_Receiving_Sub_Inv => P_Default_Receiving_Sub_Inv
,P_Default_Shipping_Sub_Inv => P_Default_Shipping_Sub_Inv
,P_Lifecycle_Id => P_Item_rec.Lifecycle_Id
,P_Current_Phase_Id => P_Item_rec.Current_Phase_id);
PROCEDURE Delete_Cost_Details(
P_Item_Id IN MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE
,P_Org_Id IN MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE
,P_Asset_Flag IN MTL_SYSTEM_ITEMS_B.INVENTORY_ASSET_FLAG%TYPE
,P_Cost_Txn IN NUMBER
,P_Last_Updated_By IN MTL_SYSTEM_ITEMS_B.LAST_UPDATED_BY%TYPE
,P_Last_Updated_Login IN MTL_SYSTEM_ITEMS_B.LAST_UPDATE_LOGIN%TYPE)
IS
l_prim_cost_method MTL_PARAMETERS.PRIMARY_COST_METHOD%TYPE;
SELECT primary_cost_method
INTO l_prim_cost_method
FROM mtl_parameters
WHERE ORGANIZATION_ID = p_org_id;
UPDATE cst_item_costs
SET inventory_asset_flag = decode(P_Asset_Flag,'Y', 1, 2)
,LAST_UPDATED_BY = P_Last_Updated_By
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATE_LOGIN = P_Last_Updated_Login
,PL_MATERIAL = 0
,PL_MATERIAL_OVERHEAD = 0
,PL_RESOURCE = 0
,PL_OUTSIDE_PROCESSING = 0
,PL_OVERHEAD = 0
,TL_MATERIAL = 0
,TL_MATERIAL_OVERHEAD = 0
,TL_RESOURCE = 0
,TL_OUTSIDE_PROCESSING = 0
,TL_OVERHEAD = 0
,MATERIAL_COST = 0
,MATERIAL_OVERHEAD_COST = 0
,RESOURCE_COST = 0
,OUTSIDE_PROCESSING_COST = 0
,OVERHEAD_COST = 0
,PL_ITEM_COST = 0
,TL_ITEM_COST = 0
,ITEM_COST = 0
,UNBURDENED_COST = 0
,BURDEN_COST = 0
WHERE organization_id = P_Org_Id
AND inventory_item_id = p_Item_Id
AND cost_type_id = l_prim_cost_method;
UPDATE cst_quantity_layers
SET LAST_UPDATED_BY = P_Last_Updated_By
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATE_LOGIN = P_Last_Updated_Login
,PL_MATERIAL = 0
,PL_MATERIAL_OVERHEAD = 0
,PL_RESOURCE = 0
,PL_OUTSIDE_PROCESSING = 0
,PL_OVERHEAD = 0
,TL_MATERIAL = 0
,TL_MATERIAL_OVERHEAD = 0
,TL_RESOURCE = 0
,TL_OUTSIDE_PROCESSING = 0
,TL_OVERHEAD = 0
,MATERIAL_COST = 0
,MATERIAL_OVERHEAD_COST = 0
,RESOURCE_COST = 0
,OUTSIDE_PROCESSING_COST = 0
,OVERHEAD_COST = 0
,PL_ITEM_COST = 0
,TL_ITEM_COST = 0
,ITEM_COST = 0
,UNBURDENED_COST = 0
,BURDEN_COST = 0
WHERE organization_id = P_Org_Id
AND inventory_item_id = P_Item_id;
DELETE cst_item_cost_details
WHERE inventory_item_id = P_Item_id
AND organization_id = P_Org_Id
AND cost_type_id = l_prim_cost_method;
DELETE cst_layer_cost_details
WHERE layer_id IN ( SELECT layer_id
FROM cst_quantity_layers
WHERE inventory_item_id = P_Item_Id
AND organization_id = P_Org_Id );
CSTPLENG.update_inv_layer_cost(
I_ORG_ID => P_Org_Id
,I_ITEM_ID => P_Item_id
,I_USERID => P_Last_Updated_By
,I_LOGIN_ID => P_Last_Updated_Login);
END Delete_Cost_Details;
PROCEDURE Update_Item(
P_Item_Rec IN INV_ITEM_API.Item_rec_type
,P_Item_Category_Struct_Id IN NUMBER
,P_Inv_Install IN NUMBER
,P_Master_Org_Id IN MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
,P_Category_Set_Id IN NUMBER
,P_Item_Category_Id IN NUMBER
,P_Mode IN VARCHAR2
,P_Updateble_Item IN VARCHAR2
,P_Cost_Txn IN VARCHAR2
,P_Item_Cost_Details IN VARCHAR2
,P_Inv_Item_status_old IN MTL_SYSTEM_ITEMS_FVL.INVENTORY_ITEM_STATUS_CODE%TYPE
,P_Default_Move_Order_Sub_Inv IN VARCHAR2 -- Item Transaction Defaults for 11.5.9
,P_Default_Receiving_Sub_Inv IN VARCHAR2
,P_Default_Shipping_Sub_Inv IN VARCHAR2
) IS
Cursor c_get_uom_class(cp_uom mtl_units_of_measure_vl.unit_of_measure%TYPE) IS
SELECT UOM_CLASS
FROM MTL_UNITS_OF_MEASURE_VL
WHERE UNIT_OF_MEASURE = cp_uom;
l_last_updated_by NUMBER;
l_last_update_date DATE;
l_last_update_login NUMBER;
SELECT ORGANIZATION_ID
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = org_id
OR ORGANIZATION_ID IN
(SELECT ORGANIZATION_ID FROM MTL_PARAMETERS
WHERE MASTER_ORGANIZATION_ID = org_id
AND PRIMARY_COST_METHOD in (2,5,6)
AND (1=(select control_level from mtl_item_attributes
where attribute_name= 'MTL_SYSTEM_ITEMS.INVENTORY_ASSET_FLAG')
)
);
select control_level INTO l_control_level
from mtl_item_attributes
where attribute_name= 'MTL_SYSTEM_ITEMS.PLANNING_MAKE_BUY_CODE';
Delete_Cost_Details(
P_Item_Id => P_Item_Rec.inventory_item_id
,P_Org_Id => l_org_id -- Bug 8512945 with base bug 8417326
,P_Asset_Flag => P_Item_rec.inventory_asset_flag
,P_Cost_Txn => P_Cost_Txn
,P_Last_Updated_By => P_Item_Rec.last_updated_by
,P_Last_Updated_Login => P_Item_Rec.last_update_login);
Delete_Cost_Details(
P_Item_Id => P_Item_Rec.inventory_item_id
,P_Org_Id => l_org_id -- Bug 8512945 with base bug 8417326
,P_Asset_Flag => P_Item_rec.inventory_asset_flag
,P_Cost_Txn => P_Cost_Txn
,P_Last_Updated_By => P_Item_Rec.last_updated_by
,P_Last_Updated_Login => P_Item_Rec.last_update_login);
SELECT 'Y' INTO l_exists
FROM mtl_system_items_b
WHERE inventory_item_id = P_Item_Rec.inventory_item_id
AND organization_id = l_org_id;
SELECT planning_make_buy_code INTO l_cst_item_type
FROM mtl_system_items_b
WHERE inventory_item_id = P_Item_Rec.inventory_item_id
AND organization_id = l_org_id;
INVIDIT2.Insert_Cost_Details (
x_item_id => P_Item_rec.inventory_item_id
,x_org_id => l_org_id -- Bug 8512945 with base bug 8417326
,x_inv_install => P_Inv_Install
,x_last_updated_by => P_Item_rec.last_updated_by
,x_cst_item_type => l_cst_item_type );
INVIDIT2.Insert_Costing_Category(
x_item_id => P_Item_rec.inventory_item_id
,x_org_id => P_Item_rec.organization_id);
Update_Row(P_Item_Rec => P_Item_Rec);
SELECT last_updated_by,last_update_date,LAST_UPDATE_LOGIN
INTO l_last_updated_by ,l_last_update_date,l_last_update_login
FROM
mtl_system_items_b WHERE inventory_item_id=P_Item_rec.inventory_item_id
AND organization_id= P_Master_Org_Id;
IF P_Updateble_Item = 'ALL' THEN
UPDATE mtl_system_items_b
SET segment1 = P_Item_rec.segment1
,segment2 = P_Item_rec.segment2
,segment3 = P_Item_rec.segment3
,segment4 = P_Item_rec.segment4
,segment5 = P_Item_rec.segment5
,segment6 = P_Item_rec.segment6
,segment7 = P_Item_rec.segment7
,segment8 = P_Item_rec.segment8
,segment9 = P_Item_rec.segment9
,segment10 = P_Item_rec.segment10
,segment11 = P_Item_rec.segment11
,segment12 = P_Item_rec.segment12
,segment13 = P_Item_rec.segment13
,segment14 = P_Item_rec.segment14
,segment15 = P_Item_rec.segment15
,segment16 = P_Item_rec.segment16
,segment17 = P_Item_rec.segment17
,segment18 = P_Item_rec.segment18
,segment19 = P_Item_rec.segment19
,segment20 = P_Item_rec.segment20
/* commenting as this is causing regression of bug 5220205
added code for this issue (bug 11710464)
,last_updated_by = l_last_updated_by
,last_update_date = l_last_update_date
,LAST_UPDATE_LOGIN = l_last_update_login*/
WHERE inventory_item_id = P_Item_rec.inventory_item_id;
l_event := 'UPDATE';
ELSIF (P_mode = 'UPDATE' ) THEN
l_event := 'ITEM_ORG';
INVIDIT2.Table_Inserts (
X_EVENT => l_event,
X_ITEM_ID => P_Item_Rec.inventory_item_id,
X_ORG_ID => P_Item_rec.organization_id,
X_MASTER_ORG_ID => P_Master_Org_Id,
X_STATUS_CODE => l_Inventory_Item_status,
X_INVENTORY_ITEM_FLAG => P_Item_rec.inventory_item_flag,
X_PURCHASING_ITEM_FLAG => P_Item_rec.purchasing_item_flag,
X_INTERNAL_ORDER_FLAG => P_Item_rec.internal_order_flag,
X_MRP_PLANNING_CODE => P_Item_rec.mrp_planning_code,
X_SERVICEABLE_PRODUCT_FLAG => P_Item_rec.serviceable_product_flag,
X_COSTING_ENABLED_FLAG => P_Item_rec.costing_enabled_flag,
X_ENG_ITEM_FLAG => P_Item_rec.eng_item_flag,
X_CUSTOMER_ORDER_FLAG => P_Item_rec.customer_order_flag,
X_EAM_ITEM_TYPE => P_Item_rec.eam_item_type,
X_CONTRACT_ITEM_TYPE_CODE => P_Item_rec.contract_item_type_code,
P_FOLDER_CATEGORY_SET_ID => P_Category_set_id,
P_FOLDER_ITEM_CATEGORY_ID => l_folder_item_cat_id,
X_ALLOWED_UNIT_CODE => P_Item_rec.allowed_units_lookup_code,
X_PRIMARY_UOM => P_Item_rec.primary_unit_of_measure,
X_PRIMARY_UOM_CODE => P_Item_rec.primary_uom_code,
X_PRIMARY_UOM_CLASS => l_rec_uom_class,
X_INV_INSTALL => P_inv_install,
X_LAST_UPDATED_BY => P_Item_rec.last_updated_by,
X_LAST_UPDATE_LOGIN => P_Item_rec.last_update_login,
X_ITEM_CATALOG_GROUP_ID => P_Item_rec.item_catalog_group_id
, P_Default_Move_Order_Sub_Inv => P_Default_Move_Order_Sub_Inv
, P_Default_Receiving_Sub_Inv => P_Default_Receiving_Sub_Inv
, P_Default_Shipping_Sub_Inv => P_Default_Shipping_Sub_Inv
, P_Lifecycle_Id => P_Item_rec.Lifecycle_Id
, P_Current_Phase_Id => P_Item_rec.Current_Phase_id);
' ENI_ITEMS_STAR_PKG.Update_Items_In_Star( '||
' p_api_version => 1.0 '||
' , p_init_msg_list => FND_API.g_TRUE '||
' , p_inventory_item_id => :P_Item_Rec.INVENTORY_ITEM_ID '||
' , p_organization_id => :P_Item_Rec.ORGANIZATION_ID '||
' , x_return_status => :l_return_status '||
' , x_msg_count => :l_msg_count '||
' , x_msg_data => :l_msg_data ); '||
Raise Event if Item got Updated successfully */
BEGIN
INV_ITEM_EVENTS_PVT.Raise_Events(
p_event_name => 'EGO_WF_WRAPPER_PVT.G_ITEM_UPDATE_EVENT'
,p_dml_type => 'UPDATE'
,p_inventory_item_id => p_Item_rec.INVENTORY_ITEM_ID
,p_organization_id => p_Item_rec.ORGANIZATION_ID
,p_item_description => p_Item_rec.DESCRIPTION
);
,p_dml_type => 'UPDATE'
,p_inventory_item_id => P_Item_Rec.INVENTORY_ITEM_ID
,p_item_description => P_Item_Rec.DESCRIPTION
,p_organization_id => P_Item_Rec.ORGANIZATION_ID
,p_master_org_flag => l_master_org );
END Update_Item;
SELECT
PRIMARY_UOM_CODE,
ALLOWED_UNITS_LOOKUP_CODE,
OVERCOMPLETION_TOLERANCE_TYPE,
OVERCOMPLETION_TOLERANCE_VALUE,
EFFECTIVITY_CONTROL,
CHECK_SHORTAGES_FLAG,
FULL_LEAD_TIME,
ORDER_COST,
MRP_SAFETY_STOCK_PERCENT,
MRP_SAFETY_STOCK_CODE,
MIN_MINMAX_QUANTITY,
MAX_MINMAX_QUANTITY,
MINIMUM_ORDER_QUANTITY,
FIXED_ORDER_QUANTITY,
FIXED_DAYS_SUPPLY,
MAXIMUM_ORDER_QUANTITY,
ATP_RULE_ID,
PICKING_RULE_ID,
RESERVABLE_TYPE,
POSITIVE_MEASUREMENT_ERROR,
NEGATIVE_MEASUREMENT_ERROR,
ENGINEERING_ECN_CODE,
ENGINEERING_ITEM_ID,
ENGINEERING_DATE,
SERVICE_STARTING_DELAY,
SERVICEABLE_COMPONENT_FLAG,
SERVICEABLE_PRODUCT_FLAG,
PAYMENT_TERMS_ID,
PREVENTIVE_MAINTENANCE_FLAG,
MATERIAL_BILLABLE_FLAG,
PRORATE_SERVICE_FLAG,
COVERAGE_SCHEDULE_ID,
SERVICE_DURATION_PERIOD_CODE,
SERVICE_DURATION,
INVOICEABLE_ITEM_FLAG,
TAX_CODE,
INVOICE_ENABLED_FLAG,
MUST_USE_APPROVED_VENDOR_FLAG,
OUTSIDE_OPERATION_FLAG,
OUTSIDE_OPERATION_UOM_TYPE,
SAFETY_STOCK_BUCKET_DAYS,
AUTO_REDUCE_MPS,
COSTING_ENABLED_FLAG,
AUTO_CREATED_CONFIG_FLAG,
CYCLE_COUNT_ENABLED_FLAG,
ITEM_TYPE,
MODEL_CONFIG_CLAUSE_NAME,
SHIP_MODEL_COMPLETE_FLAG,
MRP_PLANNING_CODE,
RETURN_INSPECTION_REQUIREMENT,
ATO_FORECAST_CONTROL,
RELEASE_TIME_FENCE_CODE,
RELEASE_TIME_FENCE_DAYS,
CONTAINER_ITEM_FLAG,
VEHICLE_ITEM_FLAG,
MAXIMUM_LOAD_WEIGHT,
MINIMUM_FILL_PERCENT,
CONTAINER_TYPE_CODE,
INTERNAL_VOLUME,
PRODUCT_FAMILY_ITEM_ID,
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20,
PURCHASING_TAX_CODE,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PURCHASING_ITEM_FLAG,
SHIPPABLE_ITEM_FLAG,
CUSTOMER_ORDER_FLAG,
INTERNAL_ORDER_FLAG,
INVENTORY_ITEM_FLAG,
ENG_ITEM_FLAG,
INVENTORY_ASSET_FLAG,
PURCHASING_ENABLED_FLAG,
CUSTOMER_ORDER_ENABLED_FLAG,
INTERNAL_ORDER_ENABLED_FLAG,
SO_TRANSACTIONS_FLAG,
MTL_TRANSACTIONS_ENABLED_FLAG,
STOCK_ENABLED_FLAG,
BOM_ENABLED_FLAG,
BUILD_IN_WIP_FLAG,
REVISION_QTY_CONTROL_CODE,
ITEM_CATALOG_GROUP_ID,
CATALOG_STATUS_FLAG,
RETURNABLE_FLAG,
DEFAULT_SHIPPING_ORG,
COLLATERAL_FLAG,
TAXABLE_FLAG,
QTY_RCV_EXCEPTION_CODE,
ALLOW_ITEM_DESC_UPDATE_FLAG,
INSPECTION_REQUIRED_FLAG,
RECEIPT_REQUIRED_FLAG,
MARKET_PRICE,
HAZARD_CLASS_ID,
RFQ_REQUIRED_FLAG,
QTY_RCV_TOLERANCE,
LIST_PRICE_PER_UNIT,
UN_NUMBER_ID,
PRICE_TOLERANCE_PERCENT,
ASSET_CATEGORY_ID,
ROUNDING_FACTOR,
UNIT_OF_ISSUE,
ENFORCE_SHIP_TO_LOCATION_CODE,
ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
ALLOW_UNORDERED_RECEIPTS_FLAG,
ALLOW_EXPRESS_DELIVERY_FLAG,
DAYS_EARLY_RECEIPT_ALLOWED,
DAYS_LATE_RECEIPT_ALLOWED,
RECEIPT_DAYS_EXCEPTION_CODE,
RECEIVING_ROUTING_ID,
INVOICE_CLOSE_TOLERANCE,
RECEIVE_CLOSE_TOLERANCE,
AUTO_LOT_ALPHA_PREFIX,
START_AUTO_LOT_NUMBER,
LOT_CONTROL_CODE,
SHELF_LIFE_CODE,
SHELF_LIFE_DAYS,
SERIAL_NUMBER_CONTROL_CODE,
START_AUTO_SERIAL_NUMBER,
AUTO_SERIAL_ALPHA_PREFIX,
SOURCE_TYPE,
SOURCE_ORGANIZATION_ID,
SOURCE_SUBINVENTORY,
EXPENSE_ACCOUNT,
ENCUMBRANCE_ACCOUNT,
RESTRICT_SUBINVENTORIES_CODE,
UNIT_WEIGHT,
WEIGHT_UOM_CODE,
VOLUME_UOM_CODE,
UNIT_VOLUME,
RESTRICT_LOCATORS_CODE,
LOCATION_CONTROL_CODE,
SHRINKAGE_RATE,
ACCEPTABLE_EARLY_DAYS,
PLANNING_TIME_FENCE_CODE,
DEMAND_TIME_FENCE_CODE,
LEAD_TIME_LOT_SIZE,
STD_LOT_SIZE,
CUM_MANUFACTURING_LEAD_TIME,
OVERRUN_PERCENTAGE,
MRP_CALCULATE_ATP_FLAG,
ACCEPTABLE_RATE_INCREASE,
ACCEPTABLE_RATE_DECREASE,
CUMULATIVE_TOTAL_LEAD_TIME,
PLANNING_TIME_FENCE_DAYS,
DEMAND_TIME_FENCE_DAYS,
END_ASSEMBLY_PEGGING_FLAG,
REPETITIVE_PLANNING_FLAG,
PLANNING_EXCEPTION_SET,
BOM_ITEM_TYPE,
PICK_COMPONENTS_FLAG,
REPLENISH_TO_ORDER_FLAG,
BASE_ITEM_ID,
ATP_COMPONENTS_FLAG,
ATP_FLAG,
FIXED_LEAD_TIME,
VARIABLE_LEAD_TIME,
WIP_SUPPLY_LOCATOR_ID,
WIP_SUPPLY_TYPE,
WIP_SUPPLY_SUBINVENTORY,
COST_OF_SALES_ACCOUNT,
SALES_ACCOUNT,
DEFAULT_INCLUDE_IN_ROLLUP_FLAG,
INVENTORY_ITEM_STATUS_CODE,
INVENTORY_PLANNING_CODE,
PLANNER_CODE,
PLANNING_MAKE_BUY_CODE,
FIXED_LOT_MULTIPLIER,
ROUNDING_CONTROL_TYPE,
CARRYING_COST,
POSTPROCESSING_LEAD_TIME,
PREPROCESSING_LEAD_TIME,
SUMMARY_FLAG,
ENABLED_FLAG,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
BUYER_ID,
ACCOUNTING_RULE_ID,
INVOICING_RULE_ID,
OVER_SHIPMENT_TOLERANCE,
UNDER_SHIPMENT_TOLERANCE,
OVER_RETURN_TOLERANCE,
UNDER_RETURN_TOLERANCE,
EQUIPMENT_TYPE,
RECOVERED_PART_DISP_CODE,
DEFECT_TRACKING_ON_FLAG,
EVENT_FLAG,
ELECTRONIC_FLAG,
DOWNLOADABLE_FLAG,
VOL_DISCOUNT_EXEMPT_FLAG,
COUPON_EXEMPT_FLAG,
COMMS_NL_TRACKABLE_FLAG,
ASSET_CREATION_CODE,
COMMS_ACTIVATION_REQD_FLAG,
ORDERABLE_ON_WEB_FLAG,
BACK_ORDERABLE_FLAG,
WEB_STATUS,
INDIVISIBLE_FLAG
, DIMENSION_UOM_CODE
, UNIT_LENGTH
, UNIT_WIDTH
, UNIT_HEIGHT
, BULK_PICKED_FLAG
, LOT_STATUS_ENABLED
, DEFAULT_LOT_STATUS_ID
, SERIAL_STATUS_ENABLED
, DEFAULT_SERIAL_STATUS_ID
, LOT_SPLIT_ENABLED
, LOT_MERGE_ENABLED
, INVENTORY_CARRY_PENALTY
, OPERATION_SLACK_PENALTY
, FINANCING_ALLOWED_FLAG
, EAM_ITEM_TYPE
, EAM_ACTIVITY_TYPE_CODE
, EAM_ACTIVITY_CAUSE_CODE
, EAM_ACT_NOTIFICATION_FLAG
, EAM_ACT_SHUTDOWN_STATUS
, DUAL_UOM_CONTROL
, SECONDARY_UOM_CODE
, DUAL_UOM_DEVIATION_HIGH
, DUAL_UOM_DEVIATION_LOW
, CONTRACT_ITEM_TYPE_CODE
-- , SUBSCRIPTION_DEPEND_FLAG
, SERV_REQ_ENABLED_CODE
, SERV_BILLING_ENABLED_FLAG
-- , SERV_IMPORTANCE_LEVEL
, PLANNED_INV_POINT_FLAG
, LOT_TRANSLATE_ENABLED
, DEFAULT_SO_SOURCE_TYPE
, CREATE_SUPPLY_FLAG
, SUBSTITUTION_WINDOW_CODE
, SUBSTITUTION_WINDOW_DAYS
, IB_ITEM_INSTANCE_CLASS
, CONFIG_MODEL_TYPE
--Added as part of 11.5.9 ENH
, LOT_SUBSTITUTION_ENABLED
, MINIMUM_LICENSE_QUANTITY
, EAM_ACTIVITY_SOURCE_CODE
--Added as part of 11.5.10 ENH
, TRACKING_QUANTITY_IND
, ONT_PRICING_QTY_SOURCE
, SECONDARY_DEFAULT_IND
, CONFIG_ORGS
, CONFIG_MATCH
, SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5,
SEGMENT6,
SEGMENT7,
SEGMENT8,
SEGMENT9,
SEGMENT10,
SEGMENT11,
SEGMENT12,
SEGMENT13,
SEGMENT14,
SEGMENT15,
SEGMENT16,
SEGMENT17,
SEGMENT18,
SEGMENT19,
SEGMENT20,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5 ,
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 ,
EXCLUDE_FROM_BUDGET_FLAG ,
DAYS_TGT_INV_SUPPLY ,
DAYS_TGT_INV_WINDOW ,
DAYS_MAX_INV_SUPPLY ,
DAYS_MAX_INV_WINDOW ,
DRP_PLANNED_FLAG ,
CRITICAL_COMPONENT_FLAG ,
CONTINOUS_TRANSFER ,
CONVERGENCE ,
DIVERGENCE
/* Start Bug 3713912 */-- Added for R12
,LOT_DIVISIBLE_FLAG,
GRADE_CONTROL_FLAG,
DEFAULT_GRADE,
CHILD_LOT_FLAG,
PARENT_CHILD_GENERATION_FLAG,
CHILD_LOT_PREFIX,
CHILD_LOT_STARTING_NUMBER,
CHILD_LOT_VALIDATION_FLAG,
COPY_LOT_ATTRIBUTE_FLAG,
RECIPE_ENABLED_FLAG,
PROCESS_QUALITY_ENABLED_FLAG,
PROCESS_EXECUTION_ENABLED_FLAG,
PROCESS_COSTING_ENABLED_FLAG,
PROCESS_SUPPLY_SUBINVENTORY,
PROCESS_SUPPLY_LOCATOR_ID,
PROCESS_YIELD_SUBINVENTORY,
PROCESS_YIELD_LOCATOR_ID,
HAZARDOUS_MATERIAL_FLAG,
CAS_NUMBER,
RETEST_INTERVAL,
EXPIRATION_ACTION_INTERVAL,
EXPIRATION_ACTION_CODE,
MATURITY_DAYS,
HOLD_DAYS,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
ATTRIBUTE21,
ATTRIBUTE22,
ATTRIBUTE23,
ATTRIBUTE24,
ATTRIBUTE25,
ATTRIBUTE26,
ATTRIBUTE27,
ATTRIBUTE28,
ATTRIBUTE29,
ATTRIBUTE30
/* End Bug 3713912 */
, CHARGE_PERIODICITY_CODE
, REPAIR_LEADTIME
, REPAIR_YIELD
, PREPOSITION_POINT
, REPAIR_PROGRAM
, SUBCONTRACTING_COMPONENT
, OUTSOURCED_ASSEMBLY
-- Fix for Bug#6644711
, DEFAULT_MATERIAL_STATUS_ID
-- Serial_Tagging Enh -- bug 9913552
, SERIAL_TAGGING_FLAG
from MTL_SYSTEM_ITEMS_B
where INVENTORY_ITEM_ID = P_Item_Rec.inventory_item_id
and ORGANIZATION_ID = P_Item_rec.organization_id
for update of INVENTORY_ITEM_ID nowait;
SELECT
DESCRIPTION,
LONG_DESCRIPTION,
decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
FROM MTL_SYSTEM_ITEMS_TL
WHERE INVENTORY_ITEM_ID = P_Item_Rec.inventory_item_id
AND ORGANIZATION_ID = P_Item_rec.organization_id
for update of INVENTORY_ITEM_ID nowait;
FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
AND ((recinfo.ALLOW_ITEM_DESC_UPDATE_FLAG = P_Item_Rec.ALLOW_ITEM_DESC_UPDATE_FLAG)
OR ((recinfo.ALLOW_ITEM_DESC_UPDATE_FLAG is null) AND (P_Item_Rec.ALLOW_ITEM_DESC_UPDATE_FLAG is null)))
AND ((recinfo.INSPECTION_REQUIRED_FLAG = P_Item_Rec.INSPECTION_REQUIRED_FLAG)
OR ((recinfo.INSPECTION_REQUIRED_FLAG is null) AND (P_Item_Rec.INSPECTION_REQUIRED_FLAG is null)))
AND ((recinfo.RECEIPT_REQUIRED_FLAG = P_Item_Rec.RECEIPT_REQUIRED_FLAG)
OR ((recinfo.RECEIPT_REQUIRED_FLAG is null) AND (P_Item_Rec.RECEIPT_REQUIRED_FLAG is null)))
AND ((recinfo.MARKET_PRICE = P_Item_Rec.MARKET_PRICE)
OR ((recinfo.MARKET_PRICE is null) AND (P_Item_Rec.MARKET_PRICE is null)))
AND ((recinfo.HAZARD_CLASS_ID = P_Item_Rec.HAZARD_CLASS_ID)
OR ((recinfo.HAZARD_CLASS_ID is null) AND (P_Item_Rec.HAZARD_CLASS_ID is null)))
AND ((recinfo.RFQ_REQUIRED_FLAG = P_Item_Rec.RFQ_REQUIRED_FLAG)
OR ((recinfo.RFQ_REQUIRED_FLAG is null) AND (P_Item_Rec.RFQ_REQUIRED_FLAG is null)))
AND ((recinfo.QTY_RCV_TOLERANCE = P_Item_Rec.QTY_RCV_TOLERANCE)
OR ((recinfo.QTY_RCV_TOLERANCE is null) AND (P_Item_Rec.QTY_RCV_TOLERANCE is null)))
AND ((recinfo.LIST_PRICE_PER_UNIT = P_Item_Rec.LIST_PRICE_PER_UNIT)
OR ((recinfo.LIST_PRICE_PER_UNIT is null) AND (P_Item_Rec.LIST_PRICE_PER_UNIT is null)))
AND ((recinfo.UN_NUMBER_ID = P_Item_Rec.UN_NUMBER_ID)
OR ((recinfo.UN_NUMBER_ID is null) AND (P_Item_Rec.UN_NUMBER_ID is null)))
AND ((recinfo.PRICE_TOLERANCE_PERCENT = P_Item_Rec.PRICE_TOLERANCE_PERCENT)
OR ((recinfo.PRICE_TOLERANCE_PERCENT is null) AND (P_Item_Rec.PRICE_TOLERANCE_PERCENT is null)))
AND ((recinfo.ASSET_CATEGORY_ID = P_Item_Rec.ASSET_CATEGORY_ID)
OR ((recinfo.ASSET_CATEGORY_ID is null) AND (P_Item_Rec.ASSET_CATEGORY_ID is null)))
AND ((recinfo.ROUNDING_FACTOR = P_Item_Rec.ROUNDING_FACTOR)
OR ((recinfo.ROUNDING_FACTOR is null) AND (P_Item_Rec.ROUNDING_FACTOR is null)))
AND ((recinfo.UNIT_OF_ISSUE = P_Item_Rec.UNIT_OF_ISSUE)
OR ((recinfo.UNIT_OF_ISSUE is null) AND (P_Item_Rec.UNIT_OF_ISSUE is null)))
AND ((recinfo.ENFORCE_SHIP_TO_LOCATION_CODE = P_Item_Rec.ENFORCE_SHIP_TO_LOCATION_CODE)
OR ((recinfo.ENFORCE_SHIP_TO_LOCATION_CODE is null) AND (P_Item_Rec.ENFORCE_SHIP_TO_LOCATION_CODE is null)))
AND ((recinfo.ALLOW_SUBSTITUTE_RECEIPTS_FLAG = P_Item_Rec.ALLOW_SUBSTITUTE_RECEIPTS_FLAG)
OR ((recinfo.ALLOW_SUBSTITUTE_RECEIPTS_FLAG is null) AND (P_Item_Rec.ALLOW_SUBSTITUTE_RECEIPTS_FLAG is null)))
AND ((recinfo.ALLOW_UNORDERED_RECEIPTS_FLAG = P_Item_Rec.ALLOW_UNORDERED_RECEIPTS_FLAG)
OR ((recinfo.ALLOW_UNORDERED_RECEIPTS_FLAG is null) AND (P_Item_Rec.ALLOW_UNORDERED_RECEIPTS_FLAG is null)))
AND ((recinfo.ALLOW_EXPRESS_DELIVERY_FLAG = P_Item_Rec.ALLOW_EXPRESS_DELIVERY_FLAG)
OR ((recinfo.ALLOW_EXPRESS_DELIVERY_FLAG is null) AND (P_Item_Rec.ALLOW_EXPRESS_DELIVERY_FLAG is null)))
AND ((recinfo.DAYS_EARLY_RECEIPT_ALLOWED = P_Item_Rec.DAYS_EARLY_RECEIPT_ALLOWED)
OR ((recinfo.DAYS_EARLY_RECEIPT_ALLOWED is null) AND (P_Item_Rec.DAYS_EARLY_RECEIPT_ALLOWED is null)))
AND ((recinfo.DAYS_LATE_RECEIPT_ALLOWED = P_Item_Rec.DAYS_LATE_RECEIPT_ALLOWED)
OR ((recinfo.DAYS_LATE_RECEIPT_ALLOWED is null) AND (P_Item_Rec.DAYS_LATE_RECEIPT_ALLOWED is null)))
AND ((recinfo.RECEIPT_DAYS_EXCEPTION_CODE = P_Item_Rec.RECEIPT_DAYS_EXCEPTION_CODE)
OR ((recinfo.RECEIPT_DAYS_EXCEPTION_CODE is null) AND (P_Item_Rec.RECEIPT_DAYS_EXCEPTION_CODE is null)))
AND ((recinfo.RECEIVING_ROUTING_ID = P_Item_Rec.RECEIVING_ROUTING_ID)
OR ((recinfo.RECEIVING_ROUTING_ID is null) AND (P_Item_Rec.RECEIVING_ROUTING_ID is null)))
AND ((recinfo.INVOICE_CLOSE_TOLERANCE = P_Item_Rec.INVOICE_CLOSE_TOLERANCE)
OR ((recinfo.INVOICE_CLOSE_TOLERANCE is null) AND (P_Item_Rec.INVOICE_CLOSE_TOLERANCE is null)))
AND ((recinfo.RECEIVE_CLOSE_TOLERANCE = P_Item_Rec.RECEIVE_CLOSE_TOLERANCE)
OR ((recinfo.RECEIVE_CLOSE_TOLERANCE is null) AND (P_Item_Rec.RECEIVE_CLOSE_TOLERANCE is null)))
AND ((recinfo.AUTO_LOT_ALPHA_PREFIX = P_Item_Rec.AUTO_LOT_ALPHA_PREFIX)
OR ((recinfo.AUTO_LOT_ALPHA_PREFIX is null) AND (P_Item_Rec.AUTO_LOT_ALPHA_PREFIX is null)))
AND ((recinfo.START_AUTO_LOT_NUMBER = P_Item_Rec.START_AUTO_LOT_NUMBER)
OR ((recinfo.START_AUTO_LOT_NUMBER is null) AND (P_Item_Rec.START_AUTO_LOT_NUMBER is null)))
AND ((recinfo.LOT_CONTROL_CODE = P_Item_Rec.LOT_CONTROL_CODE)
OR ((recinfo.LOT_CONTROL_CODE is null) AND (P_Item_Rec.LOT_CONTROL_CODE is null)))
AND ((recinfo.SHELF_LIFE_CODE = P_Item_Rec.SHELF_LIFE_CODE)
OR ((recinfo.SHELF_LIFE_CODE is null) AND (P_Item_Rec.SHELF_LIFE_CODE is null)))
AND ((recinfo.SHELF_LIFE_DAYS = P_Item_Rec.SHELF_LIFE_DAYS)
OR ((recinfo.SHELF_LIFE_DAYS is null) AND (P_Item_Rec.SHELF_LIFE_DAYS is null)))
AND ((recinfo.SERIAL_NUMBER_CONTROL_CODE = P_Item_Rec.SERIAL_NUMBER_CONTROL_CODE)
OR ((recinfo.SERIAL_NUMBER_CONTROL_CODE is null) AND (P_Item_Rec.SERIAL_NUMBER_CONTROL_CODE is null)))
AND ((recinfo.START_AUTO_SERIAL_NUMBER = P_Item_Rec.START_AUTO_SERIAL_NUMBER)
OR ((recinfo.START_AUTO_SERIAL_NUMBER is null) AND (P_Item_Rec.START_AUTO_SERIAL_NUMBER is null)))
AND ((recinfo.AUTO_SERIAL_ALPHA_PREFIX = P_Item_Rec.AUTO_SERIAL_ALPHA_PREFIX)
OR ((recinfo.AUTO_SERIAL_ALPHA_PREFIX is null) AND (P_Item_Rec.AUTO_SERIAL_ALPHA_PREFIX is null)))
AND ((recinfo.SOURCE_TYPE = P_Item_Rec.SOURCE_TYPE)
OR ((recinfo.SOURCE_TYPE is null) AND (P_Item_Rec.SOURCE_TYPE is null)))
AND ((recinfo.SOURCE_ORGANIZATION_ID = P_Item_Rec.SOURCE_ORGANIZATION_ID)
OR ((recinfo.SOURCE_ORGANIZATION_ID is null) AND (P_Item_Rec.SOURCE_ORGANIZATION_ID is null)))
AND ((recinfo.SOURCE_SUBINVENTORY = P_Item_Rec.SOURCE_SUBINVENTORY)
OR ((recinfo.SOURCE_SUBINVENTORY is null) AND (P_Item_Rec.SOURCE_SUBINVENTORY is null)))
AND ((recinfo.EXPENSE_ACCOUNT = P_Item_Rec.EXPENSE_ACCOUNT)
OR ((recinfo.EXPENSE_ACCOUNT is null) AND (P_Item_Rec.EXPENSE_ACCOUNT is null)))
AND ((recinfo.ENCUMBRANCE_ACCOUNT = P_Item_Rec.ENCUMBRANCE_ACCOUNT)
OR ((recinfo.ENCUMBRANCE_ACCOUNT is null) AND (P_Item_Rec.ENCUMBRANCE_ACCOUNT is null)))
AND ((recinfo.RESTRICT_SUBINVENTORIES_CODE = P_Item_Rec.RESTRICT_SUBINVENTORIES_CODE)
OR ((recinfo.RESTRICT_SUBINVENTORIES_CODE is null) AND (P_Item_Rec.RESTRICT_SUBINVENTORIES_CODE is null)))
AND ((recinfo.UNIT_WEIGHT = P_Item_Rec.UNIT_WEIGHT)
OR ((recinfo.UNIT_WEIGHT is null) AND (P_Item_Rec.UNIT_WEIGHT is null)))
AND ((recinfo.WEIGHT_UOM_CODE = P_Item_Rec.WEIGHT_UOM_CODE)
OR ((recinfo.WEIGHT_UOM_CODE is null) AND (P_Item_Rec.WEIGHT_UOM_CODE is null)))
AND ((recinfo.VOLUME_UOM_CODE = P_Item_Rec.VOLUME_UOM_CODE)
OR ((recinfo.VOLUME_UOM_CODE is null) AND (P_Item_Rec.VOLUME_UOM_CODE is null)))
AND ((recinfo.UNIT_VOLUME = P_Item_Rec.UNIT_VOLUME)
OR ((recinfo.UNIT_VOLUME is null) AND (P_Item_Rec.UNIT_VOLUME is null)))
AND ((recinfo.RESTRICT_LOCATORS_CODE = P_Item_Rec.RESTRICT_LOCATORS_CODE)
OR ((recinfo.RESTRICT_LOCATORS_CODE is null) AND (P_Item_Rec.RESTRICT_LOCATORS_CODE is null)))
AND ((recinfo.LOCATION_CONTROL_CODE = P_Item_Rec.LOCATION_CONTROL_CODE)
OR ((recinfo.LOCATION_CONTROL_CODE is null) AND (P_Item_Rec.LOCATION_CONTROL_CODE is null)))
AND ((recinfo.SHRINKAGE_RATE = P_Item_Rec.SHRINKAGE_RATE)
OR ((recinfo.SHRINKAGE_RATE is null) AND (P_Item_Rec.SHRINKAGE_RATE is null)))
AND ((recinfo.ACCEPTABLE_EARLY_DAYS = P_Item_Rec.ACCEPTABLE_EARLY_DAYS)
OR ((recinfo.ACCEPTABLE_EARLY_DAYS is null) AND (P_Item_Rec.ACCEPTABLE_EARLY_DAYS is null)))
AND ((recinfo.PLANNING_TIME_FENCE_CODE = P_Item_Rec.PLANNING_TIME_FENCE_CODE)
OR ((recinfo.PLANNING_TIME_FENCE_CODE is null) AND (P_Item_Rec.PLANNING_TIME_FENCE_CODE is null)))
AND ((recinfo.DEMAND_TIME_FENCE_CODE = P_Item_Rec.DEMAND_TIME_FENCE_CODE)
OR ((recinfo.DEMAND_TIME_FENCE_CODE is null) AND (P_Item_Rec.DEMAND_TIME_FENCE_CODE is null)))
AND ((recinfo.LEAD_TIME_LOT_SIZE = P_Item_Rec.LEAD_TIME_LOT_SIZE)
OR ((recinfo.LEAD_TIME_LOT_SIZE is null) AND (P_Item_Rec.LEAD_TIME_LOT_SIZE is null)))
AND ((recinfo.STD_LOT_SIZE = P_Item_Rec.STD_LOT_SIZE)
OR ((recinfo.STD_LOT_SIZE is null) AND (P_Item_Rec.STD_LOT_SIZE is null)))
AND ((recinfo.CUM_MANUFACTURING_LEAD_TIME = P_Item_Rec.CUM_MANUFACTURING_LEAD_TIME)
OR ((recinfo.CUM_MANUFACTURING_LEAD_TIME is null) AND (P_Item_Rec.CUM_MANUFACTURING_LEAD_TIME is null)))
AND ((recinfo.OVERRUN_PERCENTAGE = P_Item_Rec.OVERRUN_PERCENTAGE)
OR ((recinfo.OVERRUN_PERCENTAGE is null) AND (P_Item_Rec.OVERRUN_PERCENTAGE is null)))
AND ((recinfo.MRP_CALCULATE_ATP_FLAG = P_Item_Rec.MRP_CALCULATE_ATP_FLAG)
OR ((recinfo.MRP_CALCULATE_ATP_FLAG is null) AND (P_Item_Rec.MRP_CALCULATE_ATP_FLAG is null)))
AND ((recinfo.ACCEPTABLE_RATE_INCREASE = P_Item_Rec.ACCEPTABLE_RATE_INCREASE)
OR ((recinfo.ACCEPTABLE_RATE_INCREASE is null) AND (P_Item_Rec.ACCEPTABLE_RATE_INCREASE is null)))
AND ((recinfo.ACCEPTABLE_RATE_DECREASE = P_Item_Rec.ACCEPTABLE_RATE_DECREASE)
OR ((recinfo.ACCEPTABLE_RATE_DECREASE is null) AND (P_Item_Rec.ACCEPTABLE_RATE_DECREASE is null)))
AND ((recinfo.CUMULATIVE_TOTAL_LEAD_TIME = P_Item_Rec.CUMULATIVE_TOTAL_LEAD_TIME)
OR ((recinfo.CUMULATIVE_TOTAL_LEAD_TIME is null) AND (P_Item_Rec.CUMULATIVE_TOTAL_LEAD_TIME is null)))
AND ((recinfo.PLANNING_TIME_FENCE_DAYS = P_Item_Rec.PLANNING_TIME_FENCE_DAYS)
OR ((recinfo.PLANNING_TIME_FENCE_DAYS is null) AND (P_Item_Rec.PLANNING_TIME_FENCE_DAYS is null)))
AND ((recinfo.DEMAND_TIME_FENCE_DAYS = P_Item_Rec.DEMAND_TIME_FENCE_DAYS)
OR ((recinfo.DEMAND_TIME_FENCE_DAYS is null) AND (P_Item_Rec.DEMAND_TIME_FENCE_DAYS is null)))
AND ((recinfo.END_ASSEMBLY_PEGGING_FLAG = P_Item_Rec.END_ASSEMBLY_PEGGING_FLAG)
OR ((recinfo.END_ASSEMBLY_PEGGING_FLAG is null) AND (P_Item_Rec.END_ASSEMBLY_PEGGING_FLAG is null)))
AND ((recinfo.REPETITIVE_PLANNING_FLAG = P_Item_Rec.REPETITIVE_PLANNING_FLAG)
OR ((recinfo.REPETITIVE_PLANNING_FLAG is null) AND (P_Item_Rec.REPETITIVE_PLANNING_FLAG is null)))
AND ((recinfo.PLANNING_EXCEPTION_SET = P_Item_Rec.PLANNING_EXCEPTION_SET)
OR ((recinfo.PLANNING_EXCEPTION_SET is null) AND (P_Item_Rec.PLANNING_EXCEPTION_SET is null)))
AND (recinfo.BOM_ITEM_TYPE = P_Item_Rec.BOM_ITEM_TYPE)
AND (recinfo.PICK_COMPONENTS_FLAG = P_Item_Rec.PICK_COMPONENTS_FLAG)
AND (recinfo.REPLENISH_TO_ORDER_FLAG = P_Item_Rec.REPLENISH_TO_ORDER_FLAG)
AND ((recinfo.BASE_ITEM_ID = P_Item_Rec.BASE_ITEM_ID)
OR ((recinfo.BASE_ITEM_ID is null) AND (P_Item_Rec.BASE_ITEM_ID is null)))
AND (recinfo.ATP_COMPONENTS_FLAG = P_Item_Rec.ATP_COMPONENTS_FLAG)
AND (recinfo.ATP_FLAG = P_Item_Rec.ATP_FLAG)
AND ((recinfo.FIXED_LEAD_TIME = P_Item_Rec.FIXED_LEAD_TIME)
OR ((recinfo.FIXED_LEAD_TIME is null) AND (P_Item_Rec.FIXED_LEAD_TIME is null)))
AND ((recinfo.VARIABLE_LEAD_TIME = P_Item_Rec.VARIABLE_LEAD_TIME)
OR ((recinfo.VARIABLE_LEAD_TIME is null) AND (P_Item_Rec.VARIABLE_LEAD_TIME is null)))
AND ((recinfo.WIP_SUPPLY_LOCATOR_ID = P_Item_Rec.WIP_SUPPLY_LOCATOR_ID)
OR ((recinfo.WIP_SUPPLY_LOCATOR_ID is null) AND (P_Item_Rec.WIP_SUPPLY_LOCATOR_ID is null)))
AND ((recinfo.WIP_SUPPLY_TYPE = P_Item_Rec.WIP_SUPPLY_TYPE)
OR ((recinfo.WIP_SUPPLY_TYPE is null) AND (P_Item_Rec.WIP_SUPPLY_TYPE is null)))
AND ((recinfo.WIP_SUPPLY_SUBINVENTORY = P_Item_Rec.WIP_SUPPLY_SUBINVENTORY)
OR ((recinfo.WIP_SUPPLY_SUBINVENTORY is null) AND (P_Item_Rec.WIP_SUPPLY_SUBINVENTORY is null)))
AND ((recinfo.COST_OF_SALES_ACCOUNT = P_Item_Rec.COST_OF_SALES_ACCOUNT)
OR ((recinfo.COST_OF_SALES_ACCOUNT is null) AND (P_Item_Rec.COST_OF_SALES_ACCOUNT is null)))
AND ((recinfo.SALES_ACCOUNT = P_Item_Rec.SALES_ACCOUNT)
OR ((recinfo.SALES_ACCOUNT is null) AND (P_Item_Rec.SALES_ACCOUNT is null)))
AND ((recinfo.DEFAULT_INCLUDE_IN_ROLLUP_FLAG = P_Item_Rec.DEFAULT_INCLUDE_IN_ROLLUP_FLAG)
OR ((recinfo.DEFAULT_INCLUDE_IN_ROLLUP_FLAG is null) AND (P_Item_Rec.DEFAULT_INCLUDE_IN_ROLLUP_FLAG is null)))
AND ((recinfo.INVENTORY_ITEM_STATUS_CODE = P_Item_Rec.INVENTORY_ITEM_STATUS_CODE)
OR ((recinfo.INVENTORY_ITEM_STATUS_CODE is null) AND (P_Item_Rec.INVENTORY_ITEM_STATUS_CODE is null)))
AND ((recinfo.INVENTORY_PLANNING_CODE = P_Item_Rec.INVENTORY_PLANNING_CODE)
OR ((recinfo.INVENTORY_PLANNING_CODE is null) AND (P_Item_Rec.INVENTORY_PLANNING_CODE is null)))
AND ((recinfo.PLANNER_CODE = P_Item_Rec.PLANNER_CODE)
OR ((recinfo.PLANNER_CODE is null) AND (P_Item_Rec.PLANNER_CODE is null)))
AND ((recinfo.PLANNING_MAKE_BUY_CODE = P_Item_Rec.PLANNING_MAKE_BUY_CODE)
OR ((recinfo.PLANNING_MAKE_BUY_CODE is null) AND (P_Item_Rec.PLANNING_MAKE_BUY_CODE is null)))
AND ((recinfo.FIXED_LOT_MULTIPLIER = P_Item_Rec.FIXED_LOT_MULTIPLIER)
OR ((recinfo.FIXED_LOT_MULTIPLIER is null) AND (P_Item_Rec.FIXED_LOT_MULTIPLIER is null)))
AND ((recinfo.ROUNDING_CONTROL_TYPE = P_Item_Rec.ROUNDING_CONTROL_TYPE)
OR ((recinfo.ROUNDING_CONTROL_TYPE is null) AND (P_Item_Rec.ROUNDING_CONTROL_TYPE is null)))
AND ((recinfo.CARRYING_COST = P_Item_Rec.CARRYING_COST)
OR ((recinfo.CARRYING_COST is null) AND (P_Item_Rec.CARRYING_COST is null)))
AND ((recinfo.POSTPROCESSING_LEAD_TIME = P_Item_Rec.POSTPROCESSING_LEAD_TIME)
OR ((recinfo.POSTPROCESSING_LEAD_TIME is null) AND (P_Item_Rec.POSTPROCESSING_LEAD_TIME is null)))
AND ((recinfo.PREPROCESSING_LEAD_TIME = P_Item_Rec.PREPROCESSING_LEAD_TIME)
OR ((recinfo.PREPROCESSING_LEAD_TIME is null) AND (P_Item_Rec.PREPROCESSING_LEAD_TIME is null)))
AND (recinfo.SUMMARY_FLAG = P_Item_Rec.SUMMARY_FLAG)
AND (recinfo.ENABLED_FLAG = P_Item_Rec.ENABLED_FLAG)
AND ((recinfo.START_DATE_ACTIVE = P_Item_Rec.START_DATE_ACTIVE)
OR ((recinfo.START_DATE_ACTIVE is null) AND (P_Item_Rec.START_DATE_ACTIVE is null)))
AND ((recinfo.END_DATE_ACTIVE = P_Item_Rec.END_DATE_ACTIVE)
OR ((recinfo.END_DATE_ACTIVE is null) AND (P_Item_Rec.END_DATE_ACTIVE is null)))
AND ((recinfo.BUYER_ID = P_Item_Rec.BUYER_ID)
OR ((recinfo.BUYER_ID is null) AND (P_Item_Rec.BUYER_ID is null)))
AND ((recinfo.ACCOUNTING_RULE_ID = P_Item_Rec.ACCOUNTING_RULE_ID)
OR ((recinfo.ACCOUNTING_RULE_ID is null) AND (P_Item_Rec.ACCOUNTING_RULE_ID is null)))
AND ((recinfo.INVOICING_RULE_ID = P_Item_Rec.INVOICING_RULE_ID)
OR ((recinfo.INVOICING_RULE_ID is null) AND (P_Item_Rec.INVOICING_RULE_ID is null)))
AND ( (recinfo.OVER_SHIPMENT_TOLERANCE = P_Item_Rec.OVER_SHIPMENT_TOLERANCE)
OR ((recinfo.OVER_SHIPMENT_TOLERANCE is null) AND (P_Item_Rec.OVER_SHIPMENT_TOLERANCE is null)) )
AND ( (recinfo.UNDER_SHIPMENT_TOLERANCE = P_Item_Rec.UNDER_SHIPMENT_TOLERANCE)
OR ((recinfo.UNDER_SHIPMENT_TOLERANCE is null) AND (P_Item_Rec.UNDER_SHIPMENT_TOLERANCE is null)) )
AND ( (recinfo.OVER_RETURN_TOLERANCE = P_Item_Rec.OVER_RETURN_TOLERANCE)
OR ((recinfo.OVER_RETURN_TOLERANCE is null) AND (P_Item_Rec.OVER_RETURN_TOLERANCE is null)) )
AND ( (recinfo.UNDER_RETURN_TOLERANCE = P_Item_Rec.UNDER_RETURN_TOLERANCE)
OR ((recinfo.UNDER_RETURN_TOLERANCE is null) AND (P_Item_Rec.UNDER_RETURN_TOLERANCE is null)) )
AND ( (recinfo.EQUIPMENT_TYPE = P_Item_Rec.EQUIPMENT_TYPE)
OR ((recinfo.EQUIPMENT_TYPE is null) AND (P_Item_Rec.EQUIPMENT_TYPE is null)) )
AND ( (recinfo.RECOVERED_PART_DISP_CODE = P_Item_Rec.RECOVERED_PART_DISP_CODE)
OR ((recinfo.RECOVERED_PART_DISP_CODE is null) AND (P_Item_Rec.RECOVERED_PART_DISP_CODE is null)) )
AND ( (recinfo.DEFECT_TRACKING_ON_FLAG = P_Item_Rec.DEFECT_TRACKING_ON_FLAG)
OR ((recinfo.DEFECT_TRACKING_ON_FLAG is null) AND (P_Item_Rec.DEFECT_TRACKING_ON_FLAG is null)) )
AND ( (recinfo.EVENT_FLAG = P_Item_Rec.EVENT_FLAG)
OR ((recinfo.EVENT_FLAG is null) AND (P_Item_Rec.EVENT_FLAG is null)) )
AND ( (recinfo.ELECTRONIC_FLAG = P_Item_Rec.ELECTRONIC_FLAG)
OR ((recinfo.ELECTRONIC_FLAG is null) AND (P_Item_Rec.ELECTRONIC_FLAG is null)) )
AND ( (recinfo.DOWNLOADABLE_FLAG = P_Item_Rec.DOWNLOADABLE_FLAG)
OR ((recinfo.DOWNLOADABLE_FLAG is null) AND (P_Item_Rec.DOWNLOADABLE_FLAG is null)) )
AND ( (recinfo.VOL_DISCOUNT_EXEMPT_FLAG = P_Item_Rec.VOL_DISCOUNT_EXEMPT_FLAG)
OR ((recinfo.VOL_DISCOUNT_EXEMPT_FLAG is null) AND (P_Item_Rec.VOL_DISCOUNT_EXEMPT_FLAG is null)) )
AND ( (recinfo.COUPON_EXEMPT_FLAG = P_Item_Rec.COUPON_EXEMPT_FLAG)
OR ((recinfo.COUPON_EXEMPT_FLAG is null) AND (P_Item_Rec.COUPON_EXEMPT_FLAG is null)) )
AND ( (recinfo.COMMS_NL_TRACKABLE_FLAG = P_Item_Rec.COMMS_NL_TRACKABLE_FLAG)
OR ((recinfo.COMMS_NL_TRACKABLE_FLAG is null) AND (P_Item_Rec.COMMS_NL_TRACKABLE_FLAG is null)) )
AND ( (recinfo.ASSET_CREATION_CODE = P_Item_Rec.ASSET_CREATION_CODE)
OR ((recinfo.ASSET_CREATION_CODE is null) AND (P_Item_Rec.ASSET_CREATION_CODE is null)) )
AND ( (recinfo.COMMS_ACTIVATION_REQD_FLAG = P_Item_Rec.COMMS_ACTIVATION_REQD_FLAG)
OR ((recinfo.COMMS_ACTIVATION_REQD_FLAG is null) AND (P_Item_Rec.COMMS_ACTIVATION_REQD_FLAG is null)) )
AND ( (recinfo.ORDERABLE_ON_WEB_FLAG = P_Item_Rec.ORDERABLE_ON_WEB_FLAG)
OR ((recinfo.ORDERABLE_ON_WEB_FLAG is null) AND (P_Item_Rec.ORDERABLE_ON_WEB_FLAG is null)) )
AND ( (recinfo.BACK_ORDERABLE_FLAG = P_Item_Rec.BACK_ORDERABLE_FLAG)
OR ((recinfo.BACK_ORDERABLE_FLAG is null) AND (P_Item_Rec.BACK_ORDERABLE_FLAG is null)) )
AND ( (recinfo.WEB_STATUS = P_Item_Rec.WEB_STATUS)
OR ((recinfo.WEB_STATUS is null) AND (P_Item_Rec.WEB_STATUS is null)) )
AND ( (recinfo.INDIVISIBLE_FLAG = P_Item_Rec.INDIVISIBLE_FLAG)
OR ((recinfo.INDIVISIBLE_FLAG is null) AND (P_Item_Rec.INDIVISIBLE_FLAG is null)) )
AND ( (recinfo.DIMENSION_UOM_CODE = P_Item_Rec.DIMENSION_UOM_CODE)
OR ((recinfo.DIMENSION_UOM_CODE is null) AND (P_Item_Rec.DIMENSION_UOM_CODE is null)) )
AND ( (recinfo.UNIT_LENGTH = P_Item_Rec.UNIT_LENGTH)
OR ((recinfo.UNIT_LENGTH is null) AND (P_Item_Rec.UNIT_LENGTH is null)) )
AND ( (recinfo.UNIT_WIDTH = P_Item_Rec.UNIT_WIDTH)
OR ((recinfo.UNIT_WIDTH is null) AND (P_Item_Rec.UNIT_WIDTH is null)) )
AND ( (recinfo.UNIT_HEIGHT = P_Item_Rec.UNIT_HEIGHT)
OR ((recinfo.UNIT_HEIGHT is null) AND (P_Item_Rec.UNIT_HEIGHT is null)) )
AND ( (recinfo.BULK_PICKED_FLAG = P_Item_Rec.BULK_PICKED_FLAG)
OR ((recinfo.BULK_PICKED_FLAG is null) AND (P_Item_Rec.BULK_PICKED_FLAG is null)) )
AND ( (recinfo.LOT_STATUS_ENABLED = P_Item_Rec.LOT_STATUS_ENABLED)
OR ((recinfo.LOT_STATUS_ENABLED is null) AND (P_Item_Rec.LOT_STATUS_ENABLED is null)) )
AND ( (recinfo.DEFAULT_LOT_STATUS_ID = P_Item_Rec.DEFAULT_LOT_STATUS_ID)
OR ((recinfo.DEFAULT_LOT_STATUS_ID is null) AND (P_Item_Rec.DEFAULT_LOT_STATUS_ID is null)) )
AND ( (recinfo.SERIAL_STATUS_ENABLED = P_Item_Rec.SERIAL_STATUS_ENABLED)
OR ((recinfo.SERIAL_STATUS_ENABLED is null) AND (P_Item_Rec.SERIAL_STATUS_ENABLED is null)) )
AND ( (recinfo.DEFAULT_SERIAL_STATUS_ID = P_Item_Rec.DEFAULT_SERIAL_STATUS_ID)
OR ((recinfo.DEFAULT_SERIAL_STATUS_ID is null) AND (P_Item_Rec.DEFAULT_SERIAL_STATUS_ID is null)) )
AND ( (recinfo.LOT_SPLIT_ENABLED = P_Item_Rec.LOT_SPLIT_ENABLED)
OR ((recinfo.LOT_SPLIT_ENABLED is null) AND (P_Item_Rec.LOT_SPLIT_ENABLED is null)) )
AND ( (recinfo.LOT_MERGE_ENABLED = P_Item_Rec.LOT_MERGE_ENABLED)
OR ((recinfo.LOT_MERGE_ENABLED is null) AND (P_Item_Rec.LOT_MERGE_ENABLED is null)) )
AND ( (recinfo.INVENTORY_CARRY_PENALTY = P_Item_Rec.INVENTORY_CARRY_PENALTY)
OR ((recinfo.INVENTORY_CARRY_PENALTY is null) AND (P_Item_Rec.INVENTORY_CARRY_PENALTY is null)) )
AND ( (recinfo.OPERATION_SLACK_PENALTY = P_Item_Rec.OPERATION_SLACK_PENALTY)
OR ((recinfo.OPERATION_SLACK_PENALTY is null) AND (P_Item_Rec.OPERATION_SLACK_PENALTY is null)) )
AND ( (recinfo.FINANCING_ALLOWED_FLAG = P_Item_Rec.FINANCING_ALLOWED_FLAG)
OR ((recinfo.FINANCING_ALLOWED_FLAG is null) AND (P_Item_Rec.FINANCING_ALLOWED_FLAG is null)) )
AND ( (recinfo.EAM_ITEM_TYPE = P_Item_Rec.EAM_ITEM_TYPE)
OR ((recinfo.EAM_ITEM_TYPE is null) AND (P_Item_Rec.EAM_ITEM_TYPE is null)) )
AND ( (recinfo.EAM_ACTIVITY_TYPE_CODE = P_Item_Rec.EAM_ACTIVITY_TYPE_CODE)
OR ((recinfo.EAM_ACTIVITY_TYPE_CODE is null) AND (P_Item_Rec.EAM_ACTIVITY_TYPE_CODE is null)) )
AND ( (recinfo.EAM_ACTIVITY_CAUSE_CODE = P_Item_Rec.EAM_ACTIVITY_CAUSE_CODE)
OR ((recinfo.EAM_ACTIVITY_CAUSE_CODE is null) AND (P_Item_Rec.EAM_ACTIVITY_CAUSE_CODE is null)) )
AND ( (recinfo.EAM_ACT_NOTIFICATION_FLAG = P_Item_Rec.EAM_ACT_NOTIFICATION_FLAG)
OR ((recinfo.EAM_ACT_NOTIFICATION_FLAG is null) AND (P_Item_Rec.EAM_ACT_NOTIFICATION_FLAG is null)) )
AND ( (recinfo.EAM_ACT_SHUTDOWN_STATUS = P_Item_Rec.EAM_ACT_SHUTDOWN_STATUS)
OR ((recinfo.EAM_ACT_SHUTDOWN_STATUS is null) AND (P_Item_Rec.EAM_ACT_SHUTDOWN_STATUS is null)) )
AND ( (recinfo.DUAL_UOM_CONTROL = P_Item_Rec.DUAL_UOM_CONTROL)
OR ((recinfo.DUAL_UOM_CONTROL is null) AND (P_Item_Rec.DUAL_UOM_CONTROL is null)) )
AND ( (recinfo.SECONDARY_UOM_CODE = P_Item_Rec.SECONDARY_UOM_CODE)
OR ((recinfo.SECONDARY_UOM_CODE is null) AND (P_Item_Rec.SECONDARY_UOM_CODE is null)) )
AND ( (recinfo.DUAL_UOM_DEVIATION_HIGH = P_Item_Rec.DUAL_UOM_DEVIATION_HIGH)
OR ((recinfo.DUAL_UOM_DEVIATION_HIGH is null) AND (P_Item_Rec.DUAL_UOM_DEVIATION_HIGH is null)) )
AND ( (recinfo.DUAL_UOM_DEVIATION_LOW = P_Item_Rec.DUAL_UOM_DEVIATION_LOW)
OR ((recinfo.DUAL_UOM_DEVIATION_LOW is null) AND (P_Item_Rec.DUAL_UOM_DEVIATION_LOW is null)) )
AND ( (recinfo.CONTRACT_ITEM_TYPE_CODE = P_Item_Rec.CONTRACT_ITEM_TYPE_CODE)
OR ((recinfo.CONTRACT_ITEM_TYPE_CODE is null) AND (P_Item_Rec.CONTRACT_ITEM_TYPE_CODE is null)) )
/* AND ( (recinfo.SUBSCRIPTION_DEPEND_FLAG = P_Item_Rec.SUBSCRIPTION_DEPEND_FLAG)
OR ((recinfo.SUBSCRIPTION_DEPEND_FLAG is null) AND (P_Item_Rec.SUBSCRIPTION_DEPEND_FLAG is null)) )
*/ AND ( (recinfo.SERV_REQ_ENABLED_CODE = P_Item_Rec.SERV_REQ_ENABLED_CODE)
OR ( (recinfo.SERV_REQ_ENABLED_CODE is null) AND (P_Item_Rec.SERV_REQ_ENABLED_CODE is null) ) )
AND ( (recinfo.SERV_BILLING_ENABLED_FLAG = P_Item_Rec.SERV_BILLING_ENABLED_FLAG)
OR ( (recinfo.SERV_BILLING_ENABLED_FLAG is null) AND (P_Item_Rec.SERV_BILLING_ENABLED_FLAG is null) ) )
/* AND ( (recinfo.SERV_IMPORTANCE_LEVEL = P_Item_Rec.SERV_IMPORTANCE_LEVEL)
OR ( (recinfo.SERV_IMPORTANCE_LEVEL is null) AND (P_Item_Rec.SERV_IMPORTANCE_LEVEL is null) ) )
*/ AND ( (recinfo.PLANNED_INV_POINT_FLAG = P_Item_Rec.PLANNED_INV_POINT_FLAG)
OR ( (recinfo.PLANNED_INV_POINT_FLAG is null) AND (P_Item_Rec.PLANNED_INV_POINT_FLAG is null) ) )
AND ( (recinfo.LOT_TRANSLATE_ENABLED = P_Item_Rec.LOT_TRANSLATE_ENABLED)
OR ( (recinfo.LOT_TRANSLATE_ENABLED IS NULL) AND (P_Item_Rec.LOT_TRANSLATE_ENABLED IS NULL) ) )
AND ( recinfo.DEFAULT_SO_SOURCE_TYPE = P_Item_Rec.DEFAULT_SO_SOURCE_TYPE )
AND ( recinfo.CREATE_SUPPLY_FLAG = P_Item_Rec.CREATE_SUPPLY_FLAG )
AND ( (recinfo.SUBSTITUTION_WINDOW_CODE = P_Item_Rec.SUBSTITUTION_WINDOW_CODE)
OR ( (recinfo.SUBSTITUTION_WINDOW_CODE IS NULL) AND (P_Item_Rec.SUBSTITUTION_WINDOW_CODE IS NULL) ) )
AND ( (recinfo.SUBSTITUTION_WINDOW_DAYS = P_Item_Rec.SUBSTITUTION_WINDOW_DAYS)
OR ( (recinfo.SUBSTITUTION_WINDOW_DAYS IS NULL) AND (P_Item_Rec.SUBSTITUTION_WINDOW_DAYS IS NULL) ) )
AND ( (recinfo.IB_ITEM_INSTANCE_CLASS = P_Item_Rec.IB_ITEM_INSTANCE_CLASS)
OR ( (recinfo.IB_ITEM_INSTANCE_CLASS IS NULL) AND (P_Item_Rec.IB_ITEM_INSTANCE_CLASS IS NULL) ) )
AND ( (recinfo.CONFIG_MODEL_TYPE = P_Item_Rec.CONFIG_MODEL_TYPE)
OR ( (recinfo.CONFIG_MODEL_TYPE IS NULL) AND (P_Item_Rec.CONFIG_MODEL_TYPE IS NULL) ) )
--Added as part of 11.5.9 Enh
AND ( (recinfo.LOT_SUBSTITUTION_ENABLED = P_Item_Rec.LOT_SUBSTITUTION_ENABLED)
OR ( (recinfo.LOT_SUBSTITUTION_ENABLED IS NULL) AND (P_Item_Rec.LOT_SUBSTITUTION_ENABLED IS NULL) ) )
AND ( (recinfo.MINIMUM_LICENSE_QUANTITY = P_Item_Rec.MINIMUM_LICENSE_QUANTITY)
OR ( (recinfo.MINIMUM_LICENSE_QUANTITY IS NULL) AND (P_Item_Rec.MINIMUM_LICENSE_QUANTITY IS NULL) ) )
AND ( (recinfo.EAM_ACTIVITY_SOURCE_CODE = P_Item_Rec.EAM_ACTIVITY_SOURCE_CODE)
OR ( (recinfo.EAM_ACTIVITY_SOURCE_CODE IS NULL) AND (P_Item_Rec.EAM_ACTIVITY_SOURCE_CODE IS NULL) ) )
--Added as part of 11.5.10 Enh
AND ( (recinfo.TRACKING_QUANTITY_IND = P_Item_Rec.TRACKING_QUANTITY_IND)
OR ( (recinfo.TRACKING_QUANTITY_IND IS NULL) AND (P_Item_Rec.TRACKING_QUANTITY_IND IS NULL) ) )
AND ( (recinfo.ONT_PRICING_QTY_SOURCE = P_Item_Rec.ONT_PRICING_QTY_SOURCE)
OR ( (recinfo.ONT_PRICING_QTY_SOURCE IS NULL) AND (P_Item_Rec.ONT_PRICING_QTY_SOURCE IS NULL) ) )
AND ( (recinfo.SECONDARY_DEFAULT_IND = P_Item_Rec.SECONDARY_DEFAULT_IND)
OR ( (recinfo.SECONDARY_DEFAULT_IND IS NULL) AND (P_Item_Rec.SECONDARY_DEFAULT_IND IS NULL) ) )
AND ( (recinfo.CONFIG_ORGS = P_Item_Rec.CONFIG_ORGS)
OR ( (recinfo.CONFIG_ORGS IS NULL) AND (P_Item_Rec.CONFIG_ORGS IS NULL) ) )
AND ( (recinfo.CONFIG_MATCH = P_Item_Rec.CONFIG_MATCH)
OR ( (recinfo.CONFIG_MATCH IS NULL) AND (P_Item_Rec.CONFIG_MATCH IS NULL) ) )
AND ((recinfo.SEGMENT1 = P_Item_Rec.SEGMENT1)
OR ((recinfo.SEGMENT1 is null) AND (P_Item_Rec.SEGMENT1 is null)))
AND ((recinfo.SEGMENT2 = P_Item_Rec.SEGMENT2)
OR ((recinfo.SEGMENT2 is null) AND (P_Item_Rec.SEGMENT2 is null)))
AND ((recinfo.SEGMENT3 = P_Item_Rec.SEGMENT3)
OR ((recinfo.SEGMENT3 is null) AND (P_Item_Rec.SEGMENT3 is null)))
AND ((recinfo.SEGMENT4 = P_Item_Rec.SEGMENT4)
OR ((recinfo.SEGMENT4 is null) AND (P_Item_Rec.SEGMENT4 is null)))
AND ((recinfo.SEGMENT5 = P_Item_Rec.SEGMENT5)
OR ((recinfo.SEGMENT5 is null) AND (P_Item_Rec.SEGMENT5 is null)))
AND ((recinfo.SEGMENT6 = P_Item_Rec.SEGMENT6)
OR ((recinfo.SEGMENT6 is null) AND (P_Item_Rec.SEGMENT6 is null)))
AND ((recinfo.SEGMENT7 = P_Item_Rec.SEGMENT7)
OR ((recinfo.SEGMENT7 is null) AND (P_Item_Rec.SEGMENT7 is null)))
AND ((recinfo.SEGMENT8 = P_Item_Rec.SEGMENT8)
OR ((recinfo.SEGMENT8 is null) AND (P_Item_Rec.SEGMENT8 is null)))
AND ((recinfo.SEGMENT9 = P_Item_Rec.SEGMENT9)
OR ((recinfo.SEGMENT9 is null) AND (P_Item_Rec.SEGMENT9 is null)))
AND ((recinfo.SEGMENT10 = P_Item_Rec.SEGMENT10)
OR ((recinfo.SEGMENT10 is null) AND (P_Item_Rec.SEGMENT10 is null)))
AND ((recinfo.SEGMENT11 = P_Item_Rec.SEGMENT11)
OR ((recinfo.SEGMENT11 is null) AND (P_Item_Rec.SEGMENT11 is null)))
AND ((recinfo.SEGMENT12 = P_Item_Rec.SEGMENT12)
OR ((recinfo.SEGMENT12 is null) AND (P_Item_Rec.SEGMENT12 is null)))
AND ((recinfo.SEGMENT13 = P_Item_Rec.SEGMENT13)
OR ((recinfo.SEGMENT13 is null) AND (P_Item_Rec.SEGMENT13 is null)))
AND ((recinfo.SEGMENT14 = P_Item_Rec.SEGMENT14)
OR ((recinfo.SEGMENT14 is null) AND (P_Item_Rec.SEGMENT14 is null)))
AND ((recinfo.SEGMENT15 = P_Item_Rec.SEGMENT15)
OR ((recinfo.SEGMENT15 is null) AND (P_Item_Rec.SEGMENT15 is null)))
AND ((recinfo.SEGMENT16 = P_Item_Rec.SEGMENT16)
OR ((recinfo.SEGMENT16 is null) AND (P_Item_Rec.SEGMENT16 is null)))
AND ((recinfo.SEGMENT17 = P_Item_Rec.SEGMENT17)
OR ((recinfo.SEGMENT17 is null) AND (P_Item_Rec.SEGMENT17 is null)))
AND ((recinfo.SEGMENT18 = P_Item_Rec.SEGMENT18)
OR ((recinfo.SEGMENT18 is null) AND (P_Item_Rec.SEGMENT18 is null)))
AND ((recinfo.SEGMENT19 = P_Item_Rec.SEGMENT19)
OR ((recinfo.SEGMENT19 is null) AND (P_Item_Rec.SEGMENT19 is null)))
AND ((recinfo.SEGMENT20 = P_Item_Rec.SEGMENT20)
OR ((recinfo.SEGMENT20 is null) AND (P_Item_Rec.SEGMENT20 is null)))
AND ((recinfo.ATTRIBUTE_CATEGORY = P_Item_Rec.ATTRIBUTE_CATEGORY)
OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (P_Item_Rec.ATTRIBUTE_CATEGORY is null)))
AND ((recinfo.ATTRIBUTE1 = P_Item_Rec.ATTRIBUTE1)
OR ((recinfo.ATTRIBUTE1 is null) AND (P_Item_Rec.ATTRIBUTE1 is null)))
AND ((recinfo.ATTRIBUTE2 = P_Item_Rec.ATTRIBUTE2)
OR ((recinfo.ATTRIBUTE2 is null) AND (P_Item_Rec.ATTRIBUTE2 is null)))
AND ((recinfo.ATTRIBUTE3 = P_Item_Rec.ATTRIBUTE3)
OR ((recinfo.ATTRIBUTE3 is null) AND (P_Item_Rec.ATTRIBUTE3 is null)))
AND ((recinfo.ATTRIBUTE4 = P_Item_Rec.ATTRIBUTE4)
OR ((recinfo.ATTRIBUTE4 is null) AND (P_Item_Rec.ATTRIBUTE4 is null)))
AND ((recinfo.ATTRIBUTE5 = P_Item_Rec.ATTRIBUTE5)
OR ((recinfo.ATTRIBUTE5 is null) AND (P_Item_Rec.ATTRIBUTE5 is null))))
/* Start Bug 3713912 */ --Added for R12
AND ((recinfo.LOT_DIVISIBLE_FLAG = P_Item_Rec.LOT_DIVISIBLE_FLAG )
OR ((recinfo.LOT_DIVISIBLE_FLAG is null) AND (P_Item_Rec.LOT_DIVISIBLE_FLAG is null)))
AND ((recinfo.GRADE_CONTROL_FLAG = P_Item_Rec.GRADE_CONTROL_FLAG )
OR ((recinfo.GRADE_CONTROL_FLAG is null) AND (P_Item_Rec.GRADE_CONTROL_FLAG is null)))
AND ((recinfo.DEFAULT_GRADE = P_Item_Rec.DEFAULT_GRADE )
OR ((recinfo.DEFAULT_GRADE is null) AND (P_Item_Rec.DEFAULT_GRADE is null)))
AND ((recinfo.CHILD_LOT_FLAG = P_Item_Rec.CHILD_LOT_FLAG )
OR ((recinfo.CHILD_LOT_FLAG is null) AND (P_Item_Rec.CHILD_LOT_FLAG is null)))
AND ((recinfo.PARENT_CHILD_GENERATION_FLAG = P_Item_Rec.PARENT_CHILD_GENERATION_FLAG )
OR ((recinfo.PARENT_CHILD_GENERATION_FLAG is null) AND (P_Item_Rec.PARENT_CHILD_GENERATION_FLAG is null)))
AND ((recinfo.CHILD_LOT_PREFIX = P_Item_Rec.CHILD_LOT_PREFIX )
OR ((recinfo.CHILD_LOT_PREFIX is null) AND (P_Item_Rec.CHILD_LOT_PREFIX is null)))
AND ((recinfo.CHILD_LOT_STARTING_NUMBER = P_Item_Rec.CHILD_LOT_STARTING_NUMBER )
OR ((recinfo.CHILD_LOT_STARTING_NUMBER is null) AND (P_Item_Rec.CHILD_LOT_STARTING_NUMBER is null)))
AND ((recinfo.CHILD_LOT_VALIDATION_FLAG = P_Item_Rec.CHILD_LOT_VALIDATION_FLAG )
OR ((recinfo.CHILD_LOT_VALIDATION_FLAG is null) AND (P_Item_Rec.CHILD_LOT_VALIDATION_FLAG is null)))
AND ((recinfo.COPY_LOT_ATTRIBUTE_FLAG = P_Item_Rec.COPY_LOT_ATTRIBUTE_FLAG )
OR ((recinfo.COPY_LOT_ATTRIBUTE_FLAG is null) AND (P_Item_Rec.COPY_LOT_ATTRIBUTE_FLAG is null)))
AND ((recinfo.RECIPE_ENABLED_FLAG = P_Item_Rec.RECIPE_ENABLED_FLAG )
OR ((recinfo.RECIPE_ENABLED_FLAG is null) AND (P_Item_Rec.RECIPE_ENABLED_FLAG is null)))
AND ((recinfo.PROCESS_QUALITY_ENABLED_FLAG = P_Item_Rec.PROCESS_QUALITY_ENABLED_FLAG )
OR ((recinfo.PROCESS_QUALITY_ENABLED_FLAG is null) AND (P_Item_Rec.PROCESS_QUALITY_ENABLED_FLAG is null)))
AND ((recinfo.PROCESS_EXECUTION_ENABLED_FLAG = P_Item_Rec.PROCESS_EXECUTION_ENABLED_FLAG )
OR ((recinfo.PROCESS_EXECUTION_ENABLED_FLAG is null) AND (P_Item_Rec.PROCESS_EXECUTION_ENABLED_FLAG is null)))
AND ((recinfo.PROCESS_COSTING_ENABLED_FLAG = P_Item_Rec.PROCESS_COSTING_ENABLED_FLAG )
OR ((recinfo.PROCESS_COSTING_ENABLED_FLAG is null) AND (P_Item_Rec.PROCESS_COSTING_ENABLED_FLAG is null)))
AND ((recinfo.PROCESS_SUPPLY_SUBINVENTORY = P_Item_Rec.PROCESS_SUPPLY_SUBINVENTORY )
OR ((recinfo.PROCESS_SUPPLY_SUBINVENTORY is null) AND (P_Item_Rec.PROCESS_SUPPLY_SUBINVENTORY is null)))
AND ((recinfo.PROCESS_SUPPLY_LOCATOR_ID = P_Item_Rec.PROCESS_SUPPLY_LOCATOR_ID )
OR ((recinfo.PROCESS_SUPPLY_LOCATOR_ID is null) AND (P_Item_Rec.PROCESS_SUPPLY_LOCATOR_ID is null)))
AND ((recinfo.PROCESS_YIELD_SUBINVENTORY = P_Item_Rec.PROCESS_YIELD_SUBINVENTORY )
OR ((recinfo.PROCESS_YIELD_SUBINVENTORY is null) AND (P_Item_Rec.PROCESS_YIELD_SUBINVENTORY is null)))
AND ((recinfo.PROCESS_YIELD_LOCATOR_ID = P_Item_Rec.PROCESS_YIELD_LOCATOR_ID )
OR ((recinfo.PROCESS_YIELD_LOCATOR_ID is null) AND (P_Item_Rec.PROCESS_YIELD_LOCATOR_ID is null)))
AND ((recinfo.HAZARDOUS_MATERIAL_FLAG = P_Item_Rec.HAZARDOUS_MATERIAL_FLAG )
OR ((recinfo.HAZARDOUS_MATERIAL_FLAG is null) AND (P_Item_Rec.HAZARDOUS_MATERIAL_FLAG is null)))
AND ((recinfo.CAS_NUMBER = P_Item_Rec.CAS_NUMBER )
OR ((recinfo.CAS_NUMBER is null) AND (P_Item_Rec.CAS_NUMBER is null)))
AND ((recinfo.RETEST_INTERVAL = P_Item_Rec.RETEST_INTERVAL )
OR ((recinfo.RETEST_INTERVAL is null) AND (P_Item_Rec.RETEST_INTERVAL is null)))
AND ((recinfo.EXPIRATION_ACTION_INTERVAL = P_Item_Rec.EXPIRATION_ACTION_INTERVAL )
OR ((recinfo.EXPIRATION_ACTION_INTERVAL is null) AND (P_Item_Rec.EXPIRATION_ACTION_INTERVAL is null)))
AND ((recinfo.EXPIRATION_ACTION_CODE = P_Item_Rec.EXPIRATION_ACTION_CODE )
OR ((recinfo.EXPIRATION_ACTION_CODE is null) AND (P_Item_Rec.EXPIRATION_ACTION_CODE is null)))
AND ((recinfo.MATURITY_DAYS = P_Item_Rec.MATURITY_DAYS )
OR ((recinfo.MATURITY_DAYS is null) AND (P_Item_Rec.MATURITY_DAYS is null)))
AND ((recinfo.HOLD_DAYS = P_Item_Rec.HOLD_DAYS )
OR ((recinfo.HOLD_DAYS is null) AND (P_Item_Rec.HOLD_DAYS is null)))
AND ((recinfo.ATTRIBUTE16 = P_Item_Rec.ATTRIBUTE16 )
OR ((recinfo.ATTRIBUTE16 is null) AND (P_Item_Rec.ATTRIBUTE16 is null)))
AND ((recinfo.ATTRIBUTE17 = P_Item_Rec.ATTRIBUTE17 )
OR ((recinfo.ATTRIBUTE17 is null) AND (P_Item_Rec.ATTRIBUTE17 is null)))
AND ((recinfo.ATTRIBUTE18 = P_Item_Rec.ATTRIBUTE18 )
OR ((recinfo.ATTRIBUTE18 is null) AND (P_Item_Rec.ATTRIBUTE18 is null)))
AND ((recinfo.ATTRIBUTE19 = P_Item_Rec.ATTRIBUTE19 )
OR ((recinfo.ATTRIBUTE19 is null) AND (P_Item_Rec.ATTRIBUTE19 is null)))
AND ((recinfo.ATTRIBUTE20 = P_Item_Rec.ATTRIBUTE20 )
OR ((recinfo.ATTRIBUTE20 is null) AND (P_Item_Rec.ATTRIBUTE20 is null)))
AND ((recinfo.ATTRIBUTE21 = P_Item_Rec.ATTRIBUTE21 )
OR ((recinfo.ATTRIBUTE21 is null) AND (P_Item_Rec.ATTRIBUTE21 is null)))
AND ((recinfo.ATTRIBUTE22 = P_Item_Rec.ATTRIBUTE22 )
OR ((recinfo.ATTRIBUTE22 is null) AND (P_Item_Rec.ATTRIBUTE22 is null)))
AND ((recinfo.ATTRIBUTE23 = P_Item_Rec.ATTRIBUTE23 )
OR ((recinfo.ATTRIBUTE23 is null) AND (P_Item_Rec.ATTRIBUTE23 is null)))
AND ((recinfo.ATTRIBUTE24 = P_Item_Rec.ATTRIBUTE24 )
OR ((recinfo.ATTRIBUTE24 is null) AND (P_Item_Rec.ATTRIBUTE24 is null)))
AND ((recinfo.ATTRIBUTE25 = P_Item_Rec.ATTRIBUTE25 )
OR ((recinfo.ATTRIBUTE25 is null) AND (P_Item_Rec.ATTRIBUTE25 is null)))
AND ((recinfo.ATTRIBUTE26 = P_Item_Rec.ATTRIBUTE26 )
OR ((recinfo.ATTRIBUTE26 is null) AND (P_Item_Rec.ATTRIBUTE26 is null)))
AND ((recinfo.ATTRIBUTE27 = P_Item_Rec.ATTRIBUTE27 )
OR ((recinfo.ATTRIBUTE27 is null) AND (P_Item_Rec.ATTRIBUTE27 is null)))
AND ((recinfo.ATTRIBUTE28 = P_Item_Rec.ATTRIBUTE28 )
OR ((recinfo.ATTRIBUTE28 is null) AND (P_Item_Rec.ATTRIBUTE28 is null)))
AND ((recinfo.ATTRIBUTE29 = P_Item_Rec.ATTRIBUTE29 )
OR ((recinfo.ATTRIBUTE29 is null) AND (P_Item_Rec.ATTRIBUTE29 is null)))
AND ((recinfo.ATTRIBUTE30 = P_Item_Rec.ATTRIBUTE30 )
OR ((recinfo.ATTRIBUTE30 is null) AND (P_Item_Rec.ATTRIBUTE30 is null)))
/* End Bug 3713912 */
AND ((recinfo.CHARGE_PERIODICITY_CODE = p_Item_Rec.CHARGE_PERIODICITY_CODE)
OR (recinfo.CHARGE_PERIODICITY_CODE IS NULL AND p_Item_Rec.CHARGE_PERIODICITY_CODE IS NULL))
AND ((recinfo.REPAIR_LEADTIME = p_Item_Rec.REPAIR_LEADTIME)
OR(recinfo.REPAIR_LEADTIME IS NULL AND p_Item_Rec.REPAIR_LEADTIME IS NULL))
AND ((recinfo.REPAIR_YIELD = p_Item_Rec.REPAIR_YIELD)
OR(recinfo.REPAIR_YIELD IS NULL AND p_Item_Rec.REPAIR_YIELD IS NULL))
AND ((recinfo.PREPOSITION_POINT = p_Item_Rec.PREPOSITION_POINT)
OR(recinfo.PREPOSITION_POINT IS NULL AND p_Item_Rec.PREPOSITION_POINT IS NULL))
AND ((recinfo.REPAIR_PROGRAM = p_Item_Rec.REPAIR_PROGRAM)
OR(recinfo.REPAIR_PROGRAM IS NULL AND p_Item_Rec.REPAIR_PROGRAM IS NULL))
AND ((recinfo.SUBCONTRACTING_COMPONENT = p_Item_Rec.SUBCONTRACTING_COMPONENT)
OR(recinfo.SUBCONTRACTING_COMPONENT IS NULL AND p_Item_Rec.SUBCONTRACTING_COMPONENT IS NULL))
AND ((recinfo.OUTSOURCED_ASSEMBLY = p_Item_Rec.OUTSOURCED_ASSEMBLY)
OR(recinfo.OUTSOURCED_ASSEMBLY IS NULL AND p_Item_Rec.OUTSOURCED_ASSEMBLY IS NULL))
-- Fix for Bug#6644711
AND ( (recinfo.DEFAULT_MATERIAL_STATUS_ID = P_Item_Rec.DEFAULT_MATERIAL_STATUS_ID)
OR ((recinfo.DEFAULT_MATERIAL_STATUS_ID is null) AND (P_Item_Rec.DEFAULT_MATERIAL_STATUS_ID is null)))
-- Serial_Tagging Enh -- bug 9913552
AND ( (recinfo.SERIAL_TAGGING_FLAG = P_Item_Rec.SERIAL_TAGGING_FLAG)
OR ((recinfo.SERIAL_TAGGING_FLAG is null) AND (P_Item_Rec.SERIAL_TAGGING_FLAG is null)) )
THEN
NULL;
PROCEDURE DELETE_ROW IS
BEGIN
-- DELETE_ROW cannot be used to delete Item records.
RAISE_APPLICATION_ERROR (-20000, 'Cannot delete Item using MTL_SYSTEM_ITEMS_PKG.DELETE_ROW');
END DELETE_ROW;
/* DELETE FROM MTL_SYSTEM_ITEMS_TL T
WHERE NOT EXISTS ( SELECT NULL
FROM MTL_SYSTEM_ITEMS_B B
WHERE B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID
AND B.ORGANIZATION_ID = T.ORGANIZATION_ID);
UPDATE MTL_SYSTEM_ITEMS_TL T
SET( DESCRIPTION
, LONG_DESCRIPTION) = (SELECT ltrim(rtrim(B.DESCRIPTION))
, ltrim(rtrim(B.LONG_DESCRIPTION))
FROM MTL_SYSTEM_ITEMS_TL B
WHERE B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID
AND B.ORGANIZATION_ID = T.ORGANIZATION_ID
AND B.LANGUAGE = T.SOURCE_LANG)
WHERE(T.INVENTORY_ITEM_ID
, T.ORGANIZATION_ID
, T.LANGUAGE) IN (SELECT SUBT.INVENTORY_ITEM_ID,
SUBT.ORGANIZATION_ID,
SUBT.LANGUAGE
FROM MTL_SYSTEM_ITEMS_TL SUBB,
MTL_SYSTEM_ITEMS_TL SUBT
WHERE SUBB.INVENTORY_ITEM_ID = SUBT.INVENTORY_ITEM_ID
AND SUBB.ORGANIZATION_ID = SUBT.ORGANIZATION_ID
AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
AND (( SUBB.DESCRIPTION <> SUBT.DESCRIPTION
or ( SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null )
or ( SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null ) )
OR ( SUBB.LONG_DESCRIPTION <> SUBT.LONG_DESCRIPTION
or ( SUBB.LONG_DESCRIPTION is null and SUBT.LONG_DESCRIPTION is not null )
or ( SUBB.LONG_DESCRIPTION is not null and SUBT.LONG_DESCRIPTION is null ))));
This Trigger causes a insert into EGO_ITEM_TEXT_TL for each row inserted into
MTL_SYSTEM_ITEMS_TL, which causes performance issues. As part of bug 5701472,
we will drop the intermedia index, disable trigger EGO_MTL_SYSTEM_ITEMS_TL_TR1
batch insert into MTL_SYSTEM_ITEMS_TL and EGO_ITEM_TEXT_TL, enable the trigger
and ask customer to launch the concurrent request to build the intermedia index
(Item Catalog Text Index Build with CREATE option).*/
/* Drop index EGO_ITEM_TEXT_TL_CTX1. For that, we need to get the EGO schema name first. */
l_installed := FND_INSTALLATION.Get_App_Info ('EGO', l_inst_status, l_industry, l_Prod_Schema);
SELECT index_name into l_index_name
FROM all_indexes
WHERE table_name = 'EGO_ITEM_TEXT_TL'
AND index_name = 'EGO_ITEM_TEXT_TL_CTX1'
AND owner = l_prod_schema;
/*Disable Trigger EGO_MTL_SYSTEM_ITEMS_TL_TR1, and we will bulk insert into EGO_ITEM_TEXT_TL */
EXECUTE IMMEDIATE
'ALTER TRIGGER EGO_MTL_SYSTEM_ITEMS_TL_TR1 DISABLE';
in bug 5725517. Users need to run iStore Search concurrent programs for inserting
into IBE_CT_IMEDIA_SEARCH
*/
EXECUTE IMMEDIATE
'ALTER TRIGGER MTL_SYSTEM_ITEMS_TL_TA_IBE DISABLE';
/*First insert missing language records into MTL_SYSTEM_ITEMS_TL*/
INSERT
/*+ APPEND PARALLEL(tt) */
INTO MTL_SYSTEM_ITEMS_TL tt
(
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,DESCRIPTION
,LONG_DESCRIPTION
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,LANGUAGE
,SOURCE_LANG
)
select
/*+ parallel(B) parallel(L) */
B.INVENTORY_ITEM_ID
,B.ORGANIZATION_ID
,ltrim(rtrim(B.DESCRIPTION))
,ltrim(rtrim(B.LONG_DESCRIPTION))
,B.LAST_UPDATE_DATE
,B.LAST_UPDATED_BY
,B.CREATION_DATE
,B.CREATED_BY
,B.LAST_UPDATE_LOGIN
,L.LANGUAGE_CODE
,B.SOURCE_LANG
FROM MTL_SYSTEM_ITEMS_TL B ,
FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ( 'I','B' )
AND B.LANGUAGE = USERENV ( 'LANG' )
AND NOT EXISTS( SELECT /*+ parallel(t) */ NULL
FROM MTL_SYSTEM_ITEMS_TL T
WHERE T.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
AND T.ORGANIZATION_ID = B.ORGANIZATION_ID
AND T.LANGUAGE = L.LANGUAGE_CODE);
/* Next insert missing language records into EGO_ITEM_TEXT_TL
Fix for bug 12922572 : FP of Bug 5701472
Query suggested by performance team to use PARALLEL dml
*/
INSERT
/*+ APPEND PARALLEL (TL) */
INTO EGO_ITEM_TEXT_TL TL
(
ID_TYPE ,
ITEM_ID ,
ITEM_CODE ,
ORG_ID ,
LANGUAGE ,
SOURCE_LANG ,
ITEM_CATALOG_GROUP_ID ,
INVENTORY_ITEM_ID ,
TEXT ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
SELECT
/*+ PARALLEL (MSIK) */
'$$INTERNAL$$' ,
MSIK.INVENTORY_ITEM_ID ,
MSIK.CONCATENATED_SEGMENTS ,
MSIK.ORGANIZATION_ID ,
L.LANGUAGE_CODE ,
USERENV('LANG') ,
MSIK.ITEM_CATALOG_GROUP_ID ,
MSIK.INVENTORY_ITEM_ID ,
'1' ,
SYSDATE ,
MSIK.CREATED_BY ,
SYSDATE ,
MSIK.LAST_UPDATED_BY,
MSIK.LAST_UPDATE_LOGIN
FROM MTL_SYSTEM_ITEMS_B_KFV MSIK,
FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B')
AND MSIK.CONCATENATED_SEGMENTS IS NOT NULL
AND MSIK.INVENTORY_ITEM_ID IS NOT NULL
AND MSIK.ORGANIZATION_ID IS NOT NULL
AND (MSIK.INVENTORY_ITEM_ID,
MSIK.ORGANIZATION_ID)
NOT IN
(SELECT
/*+ UNNEST FULL (EITL1) PARALLEL (EITL1) */
EITL1.ITEM_ID,
EITL1.ORG_ID
FROM EGO_ITEM_TEXT_TL EITL1
WHERE EITL1.ID_TYPE = '$$INTERNAL$$'
AND EITL1.LANGUAGE = L.LANGUAGE_CODE
);
PROCEDURE UPDATE_NLS_TO_ORG(
X_INVENTORY_ITEM_ID IN VARCHAR2,
X_ORGANIZATION_ID IN VARCHAR2,
X_LANGUAGE IN VARCHAR2,
X_DESCRIPTION IN VARCHAR2,
X_LONG_DESCRIPTION IN VARCHAR2) IS
CURSOR Item_csr IS
SELECT INVENTORY_ITEM_ID
,ORGANIZATION_ID
,LANGUAGE
,SOURCE_LANG
,DESCRIPTION
,LONG_DESCRIPTION
FROM MTL_SYSTEM_ITEMS_TL
WHERE INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID
AND ORGANIZATION_ID IN ( SELECT ORGANIZATION_ID
FROM MTL_PARAMETERS
WHERE MASTER_ORGANIZATION_ID = X_ORGANIZATION_ID
AND ORGANIZATION_ID <> X_ORGANIZATION_ID)
AND LANGUAGE = X_LANGUAGE
FOR UPDATE OF INVENTORY_ITEM_ID;
select control_level
into l_desc_control
from mtl_item_attributes
where attribute_name = 'MTL_SYSTEM_ITEMS.DESCRIPTION';
select control_level
into l_longdesc_control
from mtl_item_attributes
where attribute_name = 'MTL_SYSTEM_ITEMS.LONG_DESCRIPTION';
update mtl_system_items_tl
set description = ltrim(rtrim(x_description)),
long_description = ltrim(rtrim(x_long_description)),
source_lang = x_language,
last_updated_by = fnd_profile.value('USER_ID'),
last_update_login = fnd_profile.value('LOGIN_ID')
WHERE INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID
AND ORGANIZATION_ID = X_ORGANIZATION_ID;
update mtl_system_items_tl
set description = ltrim(rtrim(x_description)),
source_lang = x_language,
last_update_date = sysdate,
last_updated_by = fnd_profile.value('USER_ID'),
last_update_login = fnd_profile.value('LOGIN_ID')
where CURRENT OF Item_csr;
update mtl_system_items_tl
set long_description = ltrim(rtrim(x_long_description)),
source_lang = x_language,
last_update_date = sysdate,
last_updated_by = fnd_profile.value('USER_ID'),
last_update_login = fnd_profile.value('LOGIN_ID')
where CURRENT OF Item_csr;
END UPDATE_NLS_TO_ORG;
SELECT 'Y'
FROM FND_OBJECTS
WHERE OBJ_NAME ='EGO_ITEM';
SELECT
SEGMENT1
from MTL_SYSTEM_ITEMS_B
where INVENTORY_ITEM_ID = P_Item_Rec.inventory_item_id
and ORGANIZATION_ID = P_Item_rec.organization_id
for update of INVENTORY_ITEM_ID nowait;
FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');