You may observe duplicate Inventory Items and their relevant details on various Inventory related report such as the Inventory Valuation report and others, as can be seen below.
Solution / Details
Please first note:
It’s recommended that this issue should preferably be handled and fixed by a professional Evolution support consultant.
This is due to the solution requiring advanced, database analysis skills and an understanding on how to compile SQL queries, to correct the above problem.
Therefore, please contact either your Evolution business partner (BP) or Evolution Support to get this issue resolved.
Apply the following steps to investigate and resolve the above query:
a) Cause of duplications:
This matter may be caused by having duplicated Price Lists per Inventory Item / Warehouse combination.
Apply the following steps to confirm such occurrences:
- On the relevant Inventory related report, identify a specific Inventory item to be used an example. In this case, we’ll be focusing on item AM007.
- Go to Inventory | Maintenance | Inventory Items.
- Find and open item AM007
- Open the Pricing tab and then click the Item Pricing button.
- On the top of the Inventory Price Maintenance screen, click the Save and Close button.
- You may notice at the bottom of the screen these errors:
In this case it refers to lines 19 and 8.
- Upon closer inspection, notice the duplications as marked below: The same pricelist record for the same Item/Warehouse combination is displayed in both lines which is illogical and invalid.
- Also note that Evolution should always prevent you from manually entering such duplicated combinations. This means these duplications may have been imported this way by mistake.
b) How to identify all duplicated records
- Using MS SQL Management Studio, run the diagnostic script below on the company database:
Diagnose for Duplicate Price List Prices - PER WAREHOUSE
Select S.Code + '_' + WH.Code + '_' + PN.cName as [Item and Warehouse and
COUNT(S.Code + '_' + WH.Code + '_' + PN.cName) as [PriceListCount]
from _etblPriceListPrices PP
inner join _etblPriceListName PN
on PP.iPriceListNameID = PN.IDPriceListName
inner join StkItem S
on PP.iStockID = S.StockLink
inner join WhseMst WH
on PP.iWarehouseID = WH.WhseLink
group by S.Code + '_' + WH.Code + '_' + PN.cName
having COUNT(S.Code + '_' + WH.Code + '_' + PN.cName) > 1
- Notice the results in which the various item code/warehouse combinations as well as the number of price list name duplication occurrences, are identified.
For instance, there are two occurrences of the Item AM007 /FG Warehouse combination and in both instances the Stark Ayres 2022/23 pricelist is used.
c) Resolving the above identified duplications
You should now compile and run updating SQL scripts to get rid of the identified duplicated records.
However, please also note:
Having duplicated Item/Warehouse combinations for the same Price List may not necessarily be the only reason for having duplicated Items on Inventory related report/s.
If needed, also run diagnostic scripts to identify duplicated Inventory Codes/ID’s, Pack Codes, Bin locations, Warehouse Codes/ID’s and other Inventory related auxiliary records, and then fix them accordingly.