Trying to create a FMS query that shows available stock quantity on each Sales Order line

[Former Member]

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. 

[kothandaraman_nagarajan · Accepted solution]

Hi,

This is limitation of FMS. Without adding document, the value will not be updated.

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Edit2: Okay it so it works fine for any items that have a history, but it seems that if the item hasn't ever been on a Sales Order (i.e. it's not found in the RDR1 table) I get "no data found".  How can I make it simply return OnHand - IsCommited if it doesn't exist in the RDR1 table, but do the below query if it does?

Actually that's not true.  I've got it working now.  I changed the query a bit and the update trigger, and it feels like it's working exactly like I wanted.  Query below if you're interested.

/*Available FMS Sales Orders*/

SELECT (T0.OnHand-T0.IsCommited+(SELECT SUM(ISNULL(T3.U_BO, 0)) FROM RDR1 T3 WHERE T3.ItemCode = $[RDR1.ItemCode]))

FROM OITW T0

INNER JOIN RDR1 T1 ON T0.ItemCode = T1.ItemCode

WHERE T1.ItemCode = $[RDR1.ItemCode] AND T1.WhsCode = 'ATL'  AND T0.WhsCode = 'ATL'

I have it on Auto Refresh -  When Field Changes "Document Total" - Refresh Regularly.

Edit1: fixed grammatical error.

[kothandaraman_nagarajan · Accepted solution]

Hi,

Thanks for feedback. If you got correct FMS query, please close this thread.

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Hi,

Can you please check with the below mentioned query:

SELECT (T0.OnHand-T0.IsCommited+(SELECT SUM(ISNULL(T3.U_BO, 0)) FROM RDR1 T3 WHERE T3.ItemCode = $[RDR1.ItemCode] and T3.WhsCode = $[RDR1.WhsCode]))

FROM OITW T0

INNER JOIN RDR1 T1 ON T0.ItemCode = T1.ItemCode

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

Thanks,

Joseph

[Former Member · Accepted solution]

This is good, but the main problem I'm having is that if I have an item code that has a quantity in the OITW OnHand field, but does not have any entries in the RDR1 table the query doesn't show me anything.

If there are not any entries in the RDR1 table for a item, I want it to show the OITW.OnHand for that item.  If an itemcode does exist in the RDR1 table, then I want it to do the query as stated in your reply.  I tried using CASE WHEN, but I guess I wasn't writing it correctly.  Still working on it.

[former_member186712 · Accepted solution]

Hi,

Instead of a "inner join" change to a "left outer join"

Regards,

[Former Member · Accepted solution]

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.