by 2can2 » Tue Jan 12, 2016 7:03 pm
Hi Scott, That's correct they only have one Bin per item. I tried using the Default Bin Location but that has the added complication of several warehouses and so I opted for SO_LineDetails. I do realise that there could be more than 1 SO_LineDetails rec for 1 SO_Lines as they are picked on FIFO from GRN's but thought I could probably use something Like Top 1 OR Distinct But I haven't got that far?
An sql query that I used to check only shows one Detail line for each line but my stored proc still doesn't work correctly!
A better option I thought would be to use BinLocationLookup but I now get duplicate records for each line. The sample SO has a physical item, a non physical item and a Comment Line - added
SELECT #TempTable2.*, IN_BinLocationLookup.Description as BinLoc
FROM #TempTable2
INNER JOIN SO_Main ON #TempTable2.InvoiceNo = SO_Main.InvoiceNo
INNER JOIN SO_History ON SO_Main.InvoiceID = SO_History.InvoiceID
INNER JOIN SO_Lines ON SO_History.InvoiceHistoryID = SO_Lines.InvoiceHistoryID
LEFT OUTER JOIN IN_Main ON SO_Lines.InventoryID = IN_Main.InventoryID
INNER JOIN IN_BinLocation ON IN_Main.InventoryID = IN_BinLocation.IN_MainID
INNER JOIN IN_BinLocationLookup ON IN_BinLocation.IN_BinLocationLookup_INBinLookupID = IN_BinLocationLookup.INBinLookupID
WHERE (IN_BinLocation.IN_LogicalID = SO_Main.IN_LogicalID OR IN_BinLocation.IN_LogicalID IS NULL)
ORDER BY id
Result 6 lines.
SQL Query =
SELECT SO_Main.InvoiceNo, SO_History.HistoryNo, SO_Main.CurrentHistoryNo, SO_Main.Status, SO_Lines.LineNum, SO_Lines.PartNo, SO_Lines.CurrentLineTotal,
SO_Lines.QuantityThisDel, SO_History.InvoiceHistoryID, IN_BinLocationLookup.Description, IN_BinLocation.IN_LogicalID
FROM SO_Main INNER JOIN
SO_History ON SO_Main.InvoiceID = SO_History.InvoiceID INNER JOIN
SO_Lines ON SO_History.InvoiceHistoryID = SO_Lines.InvoiceHistoryID LEFT OUTER JOIN
IN_BinLocationLookup INNER JOIN
IN_BinLocation ON IN_BinLocationLookup.INBinLookupID = IN_BinLocation.IN_BinLocationLookup_INBinLookupID AND
IN_BinLocationLookup.INBinLookupID = IN_BinLocation.IN_BinLocationLookup_INBinLookupID INNER JOIN
IN_Main ON IN_BinLocation.IN_MainID = IN_Main.InventoryID ON SO_Lines.InventoryID = IN_Main.InventoryID
WHERE (SO_Main.InvoiceNo = '36143') AND (IN_BinLocation.IN_LogicalID = 'ZZZZZZZZZZ0000000000' OR
IN_BinLocation.IN_LogicalID IS NULL)
ORDER BY SO_Main.InvoiceNo, SO_Lines.LineNum
Result = 3 lines - correct ????