Open Stock Transfers- SQL Queries

Open Stock Transfers- SQL Queries

Environment: 

iVend 6.6 with SAPB1 10.

Problem Statement :  

There is need to have SQL Queried to find Open Stock Transfers in iVend Enterprise database

SQL Queries : 

Below Queries are used :
a)
select a.StockTransferKey,a.FromWarehouseKey,a.ToWarehouseKey,a.BusinessDate,b.ProductKey,b.Quantity,b.QuantityOpen,b.QuantityReceived,b.QuantityShipped,c.id as Warehouseid,c.InTransitWarehouseKey,
e.id as Itemcode,e.ProductKey, 
d.InStockQuantity,
d.InReturnQuantity,
d.OnRentQuantity,
d.LostQuantity,
d.OnLayawayQuantity,
d.OnOrderQuantity,
d.OnFulFillmentQuantity,
d.AvailableQuantity ,
d.InTransitQuantity ,
d.ReservedQuantity 
 
from InvStockTransfer  (nolock) a,
InvStockTransferdetail  (nolock) b,
InvWarehouse (nolock) c,
InvInventoryItem (nolock) d,
invproduct (nolock) e 
where a.StockTransferKey=b.StockTransferKey
and a.Status in (0,2) 
and a.ToWarehouseKey=c.WarehouseKey
and b.ProductKey=d.ProductKey
and d.ProductKey=e.ProductKey
and c.InTransitWarehouseKey=d.WarehouseKey

b)
Select IP.id,
x.*, III.AvailableQuantity as 'INTRANSITQTY' from
(
Select ISTD.ProductKey, sum(istd.QuantityOpen) as TOTALOPENQTY from InvStockTransfer IST join InvStockTransferDetail ISTD on IST.StockTransferKey = ISTD.StockTransferKey where IST.Status in (0,2)
group by ISTD.ProductKey) as x join invinventoryitem III on x.ProductKey = III.ProductKey and III.WarehouseKey = 'PASS INTRASIT WAREHOUSE KEY' join InvProduct IP on IP.productkey = x.ProductKey
where x.TOTALOPENQTY > III.AvailableQuantity
** Need to pass the parameter of INTRASIT WAREHOUSE KEY

    • Related Articles

    • SQL Queries to find the database size

      Environment: Not Environment Specific Problem Statement: SQL Queries to find the database size Solution: There are below SQL Queries , used to analyze the iVend database : SQL Query for database size SELECT DB_NAME(database_id) AS DBName,Name AS ...

    • Prevent stock transfers if the available quantity is 0

      Environment: 6.6 Problem statement: Is there a way for the system to allow transfers of stock only when they have stock of it? So if they are transferring an item with a stock quantity of 0 showing on their system the transfer will be rejected. ...

    • Stock Transfer status is open however same has been already received at store

      ******Internal to CitiXsys****** Environment: 6.5 Update 6 Problem Statement: Stock Transfer status is open however same has been already received at the corresponding store. Resolution: This is a dated version issue. Run the queries and update the ...

    • How to delete open Stock Transfer Request.

      Query : How can we delete open Stock Transfer Request in iVend. Answer: Please follow below steps: - 1. Open Store Management Console from which the stocks has been requested (i.e. Management console of TO WAREHOUSE) 2. choose Operations > Business ...

    • While Cancelling the Stock Transfers error message showing 'Id already exists'

      Product Version: iVend 6.6 Problem Statement: While Cancelling the Stock Transfers error message showing '"Id already exists"' Proposed Solution: User trying to receive an inter-store transfer from iVend, but gets an error indicating that the "Id ...