SAP B1/iVend- Query Inventory In SAP

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],

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='TS5008' AND C.ID='W72' and (a.InQty != 0 OR a.OutQty != 0)

order by a.Created;