Check inventory audit in SAP
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 SAPDocName,
a.CreatedBy SAPDocEntry,
a.BASE_REF SAPDocNumber,
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 SAPTransNum,
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('TS5008') and a.LocCode = 'W72'
order by a.CreateDate
Check Audit in iVend:
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 and a.InQty > 0 then 'Refund'
when a.SourceType = 7 and a.OutQty > 0 then 'Sale'
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 DocEntry], x.TransNum [SAP TransNum],
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
order by a.Created;