Environment

iVend 6.6 integrated with SAPB1

Problem Statement

Query used to compare Missing Inventory document between iVend and SAP HANA

Symptoms

NA

Resolution/Work Around

We can use below query to compare data iVend and SAP HANA

Note: This query needs to be run on the SAP Database

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('Put Item Code') and a."LocCode" = 'Put warehouse code' 

order by a."CreateDate";

Run on Enterprise Database

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='Put Item Code' AND C.ID='Put warehouse code' and (a.InQty != 0 OR a.OutQty != 0)

order by a.Created;

Recommendations /Suggestion

 NA