V175 Warehouse Transfers Links to IN_SOH

[2can2]

Hi, I am trying to get the correct query to check Wh transfers where Qty Transferred < Qty Received in IN_SOH. Seems fine for InTransit records if I link WH_TransferLineDetails.InTransit_In_SohID but can't work out the other links in WH_TransferLineDetails to IN_SOH.
Linking WH_TransferLines.WH_TransferLineID = IN_SOH.SourceID seems to work in some instances. Can you explain how the links from the WH_Transfer files to IN_SOH should be linked. Help still details V6. Thanks.
See attachment plus Select query below :

SELECT WH_Transfer.TransferNo, WH_Transfer.TransferDate, WH_TransferLines.LineNum, WH_TransferLines.PartNo, WH_TransferLines.QtyWanted, WH_TransferLines.QtyTrans,
WH_TransferLineDetails.Quantity AS DetailQty, IN_SOH.QuantityIn, WH_TransferLineDetails.Cost, (IN_SOH.QuantityIn - WH_TransferLines.QtyTrans) * WH_TransferLineDetails.Cost AS Difference,
IN_SOH.QuantityLeft, IN_SOH.DateIn, IN_SOH.ExpiryDate, WH_Transfer.Type
FROM WH_Transfer INNER JOIN
WH_TransferLines ON WH_Transfer.WH_TransferID = WH_TransferLines.WH_TransferID INNER JOIN
WH_TransferLineDetails ON WH_TransferLines.WH_TransferLineID = WH_TransferLineDetails.WH_TransferLineID INNER JOIN
IN_SOH ON WH_TransferLines.WH_TransferLineID = IN_SOH.SourceID
WHERE (WH_Transfer.TransferNo = '011724') AND (WH_Transfer.TransferDate > CONVERT(DATETIME, '2017-10-01 00:00:00', 102))
ORDER BY WH_Transfer.TransferNo, WH_TransferLines.LineNum

[Mike.Sheen]

Hi Doug,

If you take a look at the stored procedure usp_JIWA_QuantityAsAt (used by the Inventory Quantity As-At report as well as the Transaction tab of Inventory Maintenance) - you'll see we break down the collection of transactions for Warehouse Transfers into 3 separate queries:

• Warehouse Transfers In NOT Using InTransit
• Warehouse Transfers In Using InTransit
• Transfers into the InTransit warehouse

I think if you looked at the queries this stored proc uses, this will give you a good idea as to how the tables are linked together for the Warehouse Transfer Ins and Outs.

Mike

[2can2]

Thanks Mike.