Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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. 

Former Member
Former Member replied

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

THEN T1.OnHand

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])

END

FROM OITW T1

WHERE T1.ItemCode = $[RDR1.ItemCode] AND T1.WhsCode = $[RDR1.WhsCode]

Thank you as well as everyone else who assisted.

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question