SQL :Good Receipt data fetch for Error Quantity falls into negative inventory on Integration Failure

SQL :Good Receipt data fetch for Error Quantity falls into negative inventory on Integration Failure

This KB is for Internal Purpose Only for CitiXsys

Environment :

iVend 6.6 With SAPB1

Problem Statement :

Stock transfer and Sale transaction  add stucked on Integration Failure Monitor  with error message “System.Exception:DI:Quantity falls into negative inventory [WTR1.ItemCode][line: 2]”

Symptoms:

NA

Resolution/Work Around:

Below are the query used to fetch data against stock transfer and Sale Transaction.

Stock transfer

select a.ProductKey,Quantity, b.id,d.Id as wid 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 '%negative%')
select SUM(quantity) as qty, id,wid into #Temp1 from #Temp
group by Id,wid
select qty,OnHand,itemcode,WhsCode,qty-OnHand as gr from #Temp1 a,OITW b where b.ItemCode=A.id and b.WhsCode=a.wid and qty <> OnHand and qty>OnHand

Sale

select a.ProductKey,Quantity, b.id,BookingWarehouseKey,c.Id as wid 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 '%negative%')
select SUM(quantity) as qty, id,wid into #Temp3 from #Temp2
group by Id,wid
select * from #13
select qty,OnHand,itemcode,WhsCode,qty-OnHand as gr from #Temp3 a,OITW b where b.ItemCode=A.id and b.WhsCode=a.wid and qty <> OnHand and qty>OnHand

Good Issue

select a.ProductKey,Quantity, b.id,d.Id as wid into #Temp4
from InvInventoryTransactionDetail a ,InvProduct b ,InvInventoryTransaction c,InvWarehouse d where a.productkey=b.productkey
and c.InventoryTransactionKey=a.InventoryTransactionKey and c.WarehouseKey=d.warehousekey
and a.InventoryTransactionKey in (
select SourceKey from RepIntegrationLog where Flag=0 and Status=1 and SourceType=39 and Errormessage like '%negative%' )
select SUM(quantity) as qty, id,wid into #Temp5 from #Temp4
group by Id,wid
select qty,OnHand,itemcode,WhsCode,qty-OnHand as gr from #Temp5 a,OITW b where b.ItemCode=A.id and b.WhsCode=a.wid and qty <> OnHand and qty>OnHand

Consolidation Sale

select * into #Temp6 from ConTrxTransactionMapping where ConsolidatedTransactionKey in (
SELECT SourceKey FROM RepIntegrationLog where flag='0' and status='1' and ErrorMessage like '%quantity%negative%' and sourcetype='200')
select a.ProductKey,Quantity, b.id,BookingWarehouseKey,c.Id as wid into #Temp7 from TrxTransactionSaleItem a ,
InvProduct b,InvWarehouse c where a.productkey=b.productkey
and a.BookingWarehouseKey=c.warehousekey and TransactionKey in (
select TransactionKey from #Temp6)
select SUM(quantity) as qty, id,wid into #Temp8 from #Temp7
group by Id,wid
select qty,OnHand,itemcode,WhsCode,qty-OnHand as gr from #Temp8 a,OITW 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 qty <> OnHand and qty>OnHand
order by itemcode,WhsCode

Recommendations /Suggestion:

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