I have a client who sells commodities. They need their Pricelists to be updated with each Purchase, because of fluctuating Prices.
If I can get the following Formatted Search to work, I believe that Iu2019ll have a solution:
I created a UDF on the Item Master Inventory tab rows, which I called OITW.U_Test
I need to populate this UDF with the following calculation: (OITW.OnHand * OITW.AvgPrice) + (T1.OnOrder * (Sum of all the quantities on open POu2019s)) / (OITW.OnHand + T1.OnOrder)
Here is the logic behind it: This UDF must show a modified Moving Average. Because POu2019s does not update the AvgPrice (only the Goods Receipt PO) this is cause for concern for this client who might have Sales Orders for Items that is not in stock yet, and posted as a lower AvgPrice.
Here is what I have done so far:
Select T0.ItemCode, T1.OnHand, T1.AvgPrice, T1.OnOrder, T1.OnHand*T1.AvgPrice as 'InStock Value', T1.OnOrder* ?????? as 'PO Value' From OITM T0 INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode INNER JOIN POR1 T2 ON T0.ItemCode = T2.ItemCode
Any help will be appreciated.