Delivery Note Jiwa Custom St Proc

[2can2]

Hi Scott, I wonder if you can help on this. I needed to add a bin location to your custom report for Tidal delivery note - I added this code to the end of your custom stored proc (attached) BUT I get records being duplicated by the number of lines that exist on the SO!
I have done a similar thing in usp_Jiwa_Invoices_Invoice several times with no issues?
I added the following at the end of the code :
-- SELECT * FROM #TempTable2 ORDER BY id
SELECT #TempTable2.*, SO_LineDetails.BinLocation
FROM #TempTable2
INNER JOIN SO_Main ON #TempTable2.InvoiceNo = SO_Main.InvoiceNo
INNER JOIN SO_History ON SO_Main.InvoiceID = SO_History.InvoiceID
JOIN SO_Lines ON SO_History.InvoiceHistoryID = SO_Lines.InvoiceHistoryID
-- INNER JOIN SO_Lines ON #TempTable2.PartNo = SO_Lines.PartNo
INNER JOIN SO_LineDetails ON SO_Lines.InvoiceLineID = SO_LineDetails.InvoiceLineID
-- extract BinLocation! should always be 1 !
ORDER BY id

I must be missing something simple?? Cheers

[Scott.Pearce]

I suspect this is because a sales order line may have many sales order detail lines.

For example, a sales order line may be added and a quantity of 2 entered. Jiwa goes and looks at IN_SOH (stock) and finds a stock line with quantity 1, and another stock line with quantity 1. Jiwa uses these 2 separate stock lines to fulfill the sales order line we added with quantity 2. It records the fact that our single sales order line with a quantity of 2 used 2 separate stock lines by creating 2 *sales order line details* and linking them to the sales order line:

Sales Order Line 'A', Qty = 2
-->Line Detail 'Y', Qty = 1, BIN I
-->Line Detail 'Z', Qty = 1, BIN J

Therefore, when you do the INNER JOIN from the SO_Lines table to the SO_LineDetails table, the result set is going to consist of 2 rows. This is how SQL JOINS work.

Now, stock lives in bins. To that end, we record bin information IN THE LINE DETAIL. It makes no sense to say a "Sales Order Line" got it's stock from 1 particular bin - it may have got it's stock from all over the place!

I note that your SQL has the comment "-- extract BinLocation! should always be 1 !". Does this imply that in the case of Tidal Fluid, each part exists in 1 and only 1 bin?

[2can2]

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 ????

[Scott.Pearce]

I'd group by SO_Lines.InvoiceLineID, and get the MAX of everything in the SELECT list.

[2can2]

Can't use GroupBy on temptable. Anywaymodified the code within the stored proc and got that working.
Normally try and just add fields at the end of the stored proc but couldn't get this one to work.