iVend Version 6.6
We have identified the issue, sometimes customers are facing iVend and SAP B1 stock mismatch issues.
Stock Mismatch between iVend and SAP B1
We have below queries for iVend and SAP B1 so that we can compare the stock easily. IVend Query :
select convert(varchar,a.Created,112) Created,
case when a.SourceType = -1 then 'External' when a.SourceType = 0 then 'Unknown' when a.SourceType = 1 then 'GoodsReceipt' when a.SourceType = 2 then 'GoodsReceiptPO'
when a.SourceType = 3 then 'Stock Transfer Receipt' when a.SourceType = 4 then 'StockTransfer' when a.SourceType = 5 then 'KitBuildBreak'
when a.SourceType = 6 and a.InQty > 0 then 'Goods Receipt InventoryCounting' when a.SourceType = 6 and a.OutQty > 0 then 'Goods Issue InventoryCounting'
when a.SourceType = 7 then 'SaleTransaction' when a.SourceType = 8 then 'LayawayTransaction'
when a.SourceType = 10 then 'GiftCertificateReceipt' when a.SourceType = 11 then 'GiftCertificateStockTransfer'
when a.SourceType = 12 then 'GoodsReturn' when a.SourceType = 13 then 'InventoryReconciliation'
when a.SourceType = 14 then 'StockTransferCancellation' else cast(a.SourceType as varchar) end as [iVend DocName],a.SourceKey [iVendKey], a.InQty, a.OutQty, x.TransType [SAP DocType],
x.BASE_REF as [SAP DocNum],
x.CreatedBy as [SAP DocEntry],
x.TransNum [SAP TransNum],
b.Id as ItemCode, c.Id as WhsCode,
a.SourceType [iVend SourceType]
from InvInventoryItemLog a
inner join InvProduct b on a.ProductKey = b.ProductKey
inner join InvWarehouse c on a.WarehouseKey = c.WarehouseKey
left join SBOInventoryUpdates x on a.SourceKey = x.RecordId and a.SourceType = -1
WHERE B.Id='Please Put Product Code' AND C.ID='Please Put Warehouse Code' and (a.InQty != 0 OR a.OutQty != 0)
order by a.Created;
SAP B1 Query :
select a."CreateDate",
b."USER_CODE" as "User",
a."TransType" "SAP DocType",
case
when a."TransType" = 13 then 'Sale Invoice'
when a."TransType" = 14 then 'Sale Credit Note'
when a."TransType" = 15 then 'Delivery'
when a."TransType" = 16 then 'Sales Return'
when a."TransType" = 18 then 'Purchase Invoice'
when a."TransType" = 19 then 'Purchase Credit Note'
when a."TransType" = 20 then 'GRPO'
when a."TransType" = 21 then 'Purchase Return'
when a."TransType" = 59 then 'Goods Receipt'
when a."TransType" = 60 then 'Goods Issue'
when a."TransType" = 67 and exists (select 1 from WTR1 x inner join OWHS y on x."FromWhsCod" = y."WhsCode" where x."DocEntry" = a."CreatedBy" and x."LineNum" = 0 and "Quantity" > 0 and y."U_CXS_INST" != 'Y')then 'Stock Transfer'
when a."TransType" = 67 and exists (select 1 from WTR1 x inner join OWHS y on x."FromWhsCod" = y."WhsCode" where x."DocEntry" = a."CreatedBy" and x."LineNum" = 0 and "Quantity" < 0 and y."U_CXS_INST" != 'Y')then 'Stock Transfer Cancellation'
when a."TransType" = 67 and exists (select 1 from WTR1 x inner join OWHS y on x."FromWhsCod" = y."WhsCode" where x."DocEntry" = a."CreatedBy" and x."LineNum" = 0 and "Quantity" > 0 and y."U_CXS_INST" = 'Y')then 'Stock Transfer Receipt'
when a."TransType" = 67 and exists (select 1 from WTR1 x inner join OWHS y on x."FromWhsCod" = y."WhsCode" where x."DocEntry" = a."CreatedBy" and x."LineNum" = 0 and "Quantity" < 0 and y."U_CXS_INST" = 'Y')then 'Stock Transfer Receipt Cancellation'
else 'Unknown' end as "SAP DocName",
a."CreatedBy" "SAP DocEntry",
a."BASE_REF" "SAP DocNumber",
case a."TransType"
when 13 then (select "U_CXS_TRKY" from OINV where "DocEntry" = a."CreatedBy")--Sale Invoice
when 14 then (select "U_CXS_TRKY" from ORIN where "DocEntry" = a."CreatedBy")--Sale Credit Note
when 15 then (select "U_CXS_TRKY" from ODLN where "DocEntry" = a."CreatedBy")--Delivery
when 16 then (select "U_CXS_TRKY" from ORDN where "DocEntry" = a."CreatedBy")--Sales Return
when 18 then (select "U_CXS_TRKY" from OPCH where "DocEntry" = a."CreatedBy")--Purchase Invoice
when 19 then (select "U_CXS_TRKY" from ORPC where "DocEntry" = a."CreatedBy")--Purchase Credit Note
when 20 then (select "U_CXS_TRKY" from OPDN where "DocEntry" = a."CreatedBy")--GRPO
when 21 then (select "U_CXS_TRKY" from ORPD where "DocEntry" = a."CreatedBy")--Purchase Return
when 59 then (select "U_CXS_TRKY" from OIGN where "DocEntry" = a."CreatedBy")--Goods Receipt
when 60 then (select "U_CXS_TRKY" from OIGE where "DocEntry" = a."CreatedBy")--Goods Issue
when 67 then (select "U_CXS_TRKY" from OWTR where "DocEntry" = a."CreatedBy")--Stock Transfer
else 'Unknown' end as "iVendKey",
a."InQty",
a."OutQty",
a."ItemCode",
a."LocCode" "WhsCode",
A."TransSeq" AS "SAP TransNum",
a."U_CXS_IINV"
FROM OIVL a
inner join OUSR b on a."UserSign" = b."USERID"
where (a."InQty" != 0 OR a."OutQty" != 0)
and a."ItemCode" in('Please Put Product Code') and a."LocCode" = 'Please Put Warehouse Code'
order by a."CreateDate";
N/A