1410811 - Case Sensitive Problems with Item Codes and Warehouses

Symptom

The case (upper/lower) used in Item No. in documents should exactly match the case used for Item No. in Inventory → Item Master Data.
This rule also applies to warehouse codes. The case used in Whse in documents should exactly match the case used for Warehouse Code in Administration → Setup → Inventory →  Warehouses → Warehouses Setup.

If these conditions are not met (also when importing data to the database), the following issues can occur while you are working with SAP Business One:

1. Inventory Audit Report cannot group properly due to different cases of item codes.
2. The following error message appears when running the MRP Wizard:
   "Violation of primary key constraint" in the MSN2_primary.
3. The following error messages appear when trying to update the batch selection for a specific item:
   "Item set for row 1 does not match item in selected serial no. or batches"
   "Cannot add row 1 without complete selection of batch/serial numbers".
4. The following error message appears when using batches or serial numbers (receiving, creating, updating, allocating or issuing):
   "Internal error (-5002) occurred [Message 131-183]"
5. The following error message appears when trying to update Item Master Data:
   Another user or another operation modified data; to continue, open the window again 'Items - Warehouse' (OITW) (ODBC -2039) [Message 131-183]
6. When you run the Inventory Posting List report, certain items on the UI display results, but the printout does not display results for those items.
7. The Inventory Valuation Report generated with the option Row per Item displays an incorrect / negative quantity and value.
8. In the SAP HANA database, a serial number cannot be issued from a warehouse if the number was created by a document with the wrong warehouse code.
9. In the SAP HANA database, Inventory Audit Report does not display documents created with a wrong warehouse code.
10. MS SQL Server database (DB) fails to migrate to a HANA DB and the migration tool ends with the error 'migration checks failed'.
11. Under Production → Update Parent Item Prices Globally, it is not possible to update the parent item's price. One of the following two scenarios will occur:
    1. An error appears: "Component item tree not found [Bill of Materials - Components - Component Code]".
    2. No error appears and the update stops.
12. The Data Archiving Wizard in a Non-Perpetual Inventory system fails and the following errors are recorded in the log files:
    Internal Error (-5040) [Message 131-183]
    Error when deleting data; database possibly damaged
13. When product has a wrong letter case in a Production Order header:
        a. In MS SQL Server DB and SAP Business One version 9.3 and lower, backflush components are not issued when receiving the product.
        b. In MS SQL Server DB and SAP Business One version 10.0, Receipt from Production cannot be saved due to system error: Item is not a product in the referenced work order [Goods Receipt - Object Type] [Message 3560-2]
        c. In the SAP HANA database, Receipt from Production cannot be saved due to the system error: Invalid item number [Message 3702-1]. The same error appears when displaying the Production Order.

Note: This pre-check has been implemented in the following utilities:

• Migration Wizard
• Remote Support Platform, tasks 1520423, 1520813

Cause

Specific Solution

Solution

In order to confirm whether the database is affected by the case-sensitive problem with Item No. or Whse, please run the queries from the detection_query.zip attachment.

If the query retrieves any results, please follow these instructions:

1. Check Item No. (or Whse) in the affected documents.
2. If the case (upper/lower) of Item No. (or Whse) in these documents is different from the case used in Item No. in Item Master Data (or Warehouses Code in Warehouses - Setup) please update manually the documents. Both marketing documents and Bill of Material can be updated when they are still open or before importing the data.

If manually changing all differences is time consuming, please contact your Support Center.

To avoid this issue in the future:

• Always match the case of codes in SAP Business One when data are imported in the application.
• Check that the collation setting in the MS SQL Server matches the collation suggested in SAP Note 987436.

The solution to this SAP Note can be delivered via Remote Support Platform (RSP) for SAP Business One. Please refer to RSP tasks No. 1520423, No. 1520813 to solve the above-mentioned issue. Alternatively, please create an incident using Support Launchpad for SAP Business One with the component 'SBO-MM-MDD'. Mention this SAP Note, the results of the queries below and any information you might find relevant.

Note: You cannot run the RSP tasks No. 1520813 and 1520423 if you have duplicate master data records in OITM or OWHS with codes differing only in the letter case.

For example, 2 warehouses exist with codes "Whs1" and "WHS1", or 2 items exist with codes "ITEM1" and "item1". If you detect this issue with the below queries, please contact your Support Center:

select * from OWHS where upper("WhsCode") in
(select upper(i."WhsCode") from OWHS i group by upper(i."WhsCode") having count(*)>1);

select * from OITM where upper("ItemCode") in
(select upper(i."ItemCode") from OITM i group by upper(i."ItemCode") having count(*)>1);