Trying to create a FMS query that shows available stock quantity on each Sales Order line
Using SAP B1 v 8.82
I realize that I may be going about this in completely the wrong way. Here's my code:
SELECT (T0.OnHand - T0.IsCommited + (SELECT SUM(ISNULL(T3.U_BO, 0)) FROM RDR1 T3 INNER JOIN OITM T4 ON T4.ItemCode = T3.ItemCode WHERE T3.U_BO > 0 AND T3.ItemCode = $[RDR1.ItemCode])) [available]
FROM OITW T0
INNER JOIN RDR1 T1 ON T1.ItemCode = T0.ItemCode
INNER JOIN ORDR T2 ON T2.DocEntry = T1.DocEntry
WHERE T0.WhsCode = 'ATL' AND T1.WhsCode = 'ATL' AND T2.DocNum = $[ORDR.DocNum] AND T1.LineNum = $[RDR1.LineNum]
GROUP BY T0.OnHand, T0.IsCommited
There is a UDF that I call upon in the RDR1 table called BO. I created this field so that if a person wants to wait for a new batch they can "backorder" it. In another UDF called "Available" I set up a user-defined value based on the above query. The end result should be that whenever someone enters an item, the query finds the level of stock in the warehouse, subtracts the amount already committed, and adds back in any backorder quantities on other orders for the same item. For example, say there's 1000 units of ITEM1 in stock, 2000 are ordered on SO # 1 and 500 are ordered on SO # 2. The salesperson who created SO # 1 wants to wait for a new batch and types "2000" in the BO field. I try to enter a new SO # 3 for 300 pieces. When I key in ITEM1 on the first line, the field "Available" should update to say "500" (1000 - 2500 + 2000 = 500).
It seems to be working, but only after the document is added. In the example above I enter SO # 3 with a qty of 300 and when I go back to that SO I see that "Available" shows 200. Ideally I'd like to see it say 500 while I'm working in the SO before I add it.
Hm...I haven't used joins other than inner join very much, but I think I finally got it to work. This code might be an inelegant way to do it, but it gets the job done. Shows OnHand if there's no history on the item, and otherwise shows the OnHand - IsCommited + BO.
SELECT CASE WHEN
(SELECT ISNULL(COUNT(T0.ItemCode), 0) FROM RDR1 T0 WHERE T0.ItemCode = $[RDR1.ItemCode] AND T0.WhsCode = $[RDR1.WhsCode]) = 0
ELSE (SELECT (T4.OnHand-T4.IsCommited+(SELECT SUM(ISNULL(T3.U_BO, 0)) FROM RDR1 T3 WHERE T3.ItemCode = $[RDR1.ItemCode] and T3.WhsCode = $[RDR1.WhsCode]))
FROM OITW T4
WHERE T4.ItemCode = $[RDR1.ItemCode] AND T4.[WhsCode]= $[RDR1.WhsCode])
FROM OITW T1
WHERE T1.ItemCode = $[RDR1.ItemCode] AND T1.WhsCode = $[RDR1.WhsCode]
Thank you as well as everyone else who assisted.