Skip to content
logo Knowledgebase

Inventory: Duplicated Inventory Items on Inventory related report such as Valuation report

Created on  | Last modified on  Highlight Matches

Summary

Learn how to fix having duplicated Inventory Items on Inventory related report such as Valuation report in Sage 200 Evolution.

Description

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.

 Image

 

 

Solution / Details

Resolution

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:

  1. 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.
  2. Go to Inventory | Maintenance | Inventory Items.
  3. Find and open item AM007
  4. Open the Pricing tab and then click the Item Pricing button.

        Image

  1. On the top of the Inventory Price Maintenance screen, click the Save and Close button.

Image

 

  1. You may notice at the bottom of the screen these errors:

         In this case it refers to lines 19 and 8.

        Image

  1.  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.

Image

  1. 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

 

  1. 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

PricelistName],

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

 

  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.

        Image

 

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.