cancel
Showing results for 
Search instead for 
Did you mean: 

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

Former Member
0 Kudos

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. 

View Entire Topic
kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

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

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

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
Active Contributor
0 Kudos

Hi,

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

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

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
0 Kudos

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
Active Contributor
0 Kudos

Hi,

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

Regards,

Former Member
0 Kudos

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.