on 05-14-2014 4:49 PM
I would like to update the Item Cost in the Item Master(Inventory Data Tab) with Last Purchase Price using a formatted search for items that end in 'NI'. What is the actual query for this? Also would like it to "refresh regularly"
My query(not working):
SELECT distinct T0.[ItemCode]+'NI', T0.[AvgPrice], T0.[LastPurPrc] FROM OITM WHERE T0.[ItemCode]+'NI' = $[$38.1.0]
Please advise.
Thank you
Hi Daniel,
If you want to update the field only if itemcode ends with 'NI' :
SELECT
Case (Right(T0.ItemCode,2))
When 'NI' then T0.[LastPurPrc]
Else T0.[AvgPrice]
End
FROM OITM T0 WHERE T0.[ItemCode]= $[$38.1.0]
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Daniel,
I've made a small mistake... $[$38.1.0] is for itemcode in Document Matrix (Item 38, column 1)
On Item Master Data, you can get the itemcode by $[$5.0.0] our $[OITM.ItemCode]
Also check if you manage itemCost by WareHouse... Could explain why you get 0.00 Value.
The Query i Made bring back LastPurPrc if ItemCode ends with 'NI' or OITM.ItemCost if not.
As Rahul Said, you should consider using the Material Revaluation function, especially if you are using perpetual inventory (Company Settings)
If you want to do massUpdate of the whole Item Database, ypou should try using a query to fill an Excel File and import it by DTW as Stock Revaluation
Regards;
Emmanuel
Hi,
Your requirement is not clear. Do you want update OITW average price into OITM average price?
OITW average price is automatically update by system.
Thanks & Regards,
Nagarajan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Daniel,
Item Cost gets updated automatically as soon as Inventory transactions happen.
And if you want to update it really then there is a process to update. You can use Material Revaluation. So it will take the effect into future transactions.
What is the need for updating Cost?
Regards,
Rahul
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.