Good Receipt data fetch for Error Insufficient quantity for item with batch on Integration Monitor

Good Receipt data fetch for Error Insufficient quantity for item with batch on Integration Monitor

**********Internal Purpose to CXS Support**************

Environment : 

iVend 6.6 With SAPB1

Problem Statement : 

Stock transfer and Sale transaction  events stucked on Integration Failure Monitor  with error message “Insufficient quantity for item with batch  in warehouse”

Symptoms:

NA

Resolution/Work Around: 

Below are the query used to fetch data against stock transfer and Sale Transaction for  Insufficient quantity for batch in SAP

Query for fetch  stock transfer data for error insufficient batch

Select a.ProductKey,Quantity, b.id,d.Id as wid,StockTransferDetailKey,d.warehousekey into #Temp from InvStockTransferDetail a ,InvProduct b ,InvStockTransfer c,InvWarehouse d where a.productkey=b.productkey
and c.StockTransferKey=a.StockTransferKey and c.FromWarehouseKey=d.warehousekey
and a.StockTransferKey in (
select SourceKey from RepIntegrationLog where Flag=0 and Status=1 and SourceType=53 and Errormessage like '%Insufficient%batch%')

alter table #temp add tempbatch varchar (200)

update #temp
set tempbatch=batchkey
from InvStockTransferBatch a,#temp b where a.StockTransferDetailKey=b.StockTransferDetailKey

alter table #temp add batchdetailorg varchar (200)

update #temp
set batchdetailorg=batchnumber
from invbatch a,#temp b where a.batchkey=b.tempbatch and a.productkey=b.productkey

select SUM(quantity) as qty, id,wid,batchdetailorg into #Temp2 from #Temp
group by Id,wid,batchdetailorg

select a.DistNumber,a.ItemCode, b.WhsCode, b.Quantity BatchQty into #batchdetail
from [SAPdatabasename]..OBTN a
inner join [Brand_Folio]..OBTQ b on a.ItemCode = b.ItemCode and a.SysNumber = b.SysNumber

select qty,batchqty as "OnHand",itemcode,WhsCode,qty-batchqty as gr,batchdetailorg from #Temp2 a,#batchdetail b where b.ItemCode=A.id collate SQL_Latin1_General_CP1_CI_AS
and b.WhsCode=a.wid collate SQL_Latin1_General_CP1_CI_AS
and a.batchdetailorg=b.DistNumber collate SQL_Latin1_General_CP1_CI_AS
and qty <> batchqty and qty>batchqty

Query for fetch  Sale Transaction data for error insufficient batch

select a.DistNumber,a.ItemCode, b.WhsCode, b.Quantity BatchQty into #batchdetailsap
from [SAPdatabasename]..OBTN a
inner join [Brand_Folio]..OBTQ b on a.ItemCode = b.ItemCode and a.SysNumber = b.SysNumber

select a.ProductKey,Quantity, b.id,BookingWarehouseKey,c.Id as wid,a.ProductDetailKey,c.WarehouseKey into #Temp2 from TrxTransactionSaleItem a ,InvProduct b,InvWarehouse c where a.productkey=b.productkey
and a.BookingWarehouseKey=c.warehousekey and TransactionKey in (
select SourceKey from RepIntegrationLog where Flag=0 and Status=1 and SourceType=143 and Errormessage like '%Insufficient%batch%')

alter table #temp2 add batchdetailorg varchar (200)

update #temp2
set batchdetailorg=batchnumber
from invbatch a,#temp2 b where a.batchkey=b.ProductDetailKey and a.productkey=b.productkey

select SUM(quantity) as qty, id,wid,batchdetailorg into #Temp3 from #Temp2
group by Id,wid,batchdetailorg

select qty,batchqty as "OnHand",itemcode,WhsCode,qty-batchqty as gr,batchdetailorg from #Temp3 a,#batchdetailsap b where b.ItemCode=A.id collate SQL_Latin1_General_CP1_CI_AS
and b.WhsCode=a.wid collate SQL_Latin1_General_CP1_CI_AS
and a.batchdetailorg=b.DistNumber collate SQL_Latin1_General_CP1_CI_AS
and qty <> batchqty and qty>batchqty

Recommendations /Suggestion: 

None
This article is created only for Support team and such activities should be done along  with  product expert