iVend Stock transfer duplicate data in InvBatch
******Internal to Citixsys******
******Internal to Citixsys******Environment:
6.5 Update 6 Patch 2
Problem statement:
At the time of creating stock transfer below error occurred.
Load failed Criteria -Nhibernate.Criterion.Queryover'2[CXS.SubSystem.Inventoty.Batch,CXS.SubSystsem.Inventory.Batch]
Proposed solution:
This issue can be resolved by sql query. we applied SQL queries to correct the information on store and suggested to plan the inventory match.
Issue is only reported on dated version and is not noticed on the latest branch of iVend release available for the production use
Below are the steps we require to follow-up to resolve the issue.
Step 1: Query used for checking duplicate data on Invbatch table.
Select IB.* from InvBatch IB JOIN (
select productkey,warehousekey,batchnumber from InvBatch
group by productkey,warehousekey, batchnumber
having count(1) > 1) GB
ON IB.productkey = GB.productkey AND IB.WarehouseKey = GB.WarehouseKey AND IB.BatchNumber = GB.BatchNumber
Step 2: Backup of Invbatach Table.
Step 3: Query used for delete duplicate data on Invbatch table.
With CTE as
(
Select IB.Batchkey,IB.productkey,IB.warehousekey,IB.batchnumber,row_number() over (partition by IB.productkey,IB.warehousekey,IB.batchnumber order by IB.Batchkey) as BatchRowNumber from InvBatch IB JOIN (
select productkey,warehousekey,batchnumber from InvBatch
group by productkey,warehousekey, batchnumber
having count(1) > 1) GB
ON IB.productkey = GB.productkey AND IB.WarehouseKey = GB.WarehouseKey AND IB.BatchNumber = GB.BatchNumber
)
Delete from invbatch where batchkey in (Select batchkey from CTE where BatchRowNumber > 1 )
This article is created only for Support team and will be done along with product expert.