cancel
Showing results for 
Search instead for 
Did you mean: 

Update Item Cost w/Last Purchase Price FMS

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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]

Former Member
0 Kudos

Emmanuel,

Thank you.

However this query is always entering a "0.00" in item cost when the formatted search runs even though the there is a Last Purchase Price.  It is set to Auto Refresh When Field Changes: Item No. and "Refresh Regularly"

What am I missing?

Former Member
0 Kudos

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

Former Member
0 Kudos

Thank you Emmanuel that worked!

Former Member
0 Kudos

Please close your thread by marking helpful answer.

Thanks,

Gordon

Former Member
0 Kudos

Hi Gordon,

Great Query book by the way!

Where do I mark "helpful" answer?  Sorry don't see it?

Answers (2)

Answers (2)

kothandaraman_nagarajan
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

Hi

Thanks for your response.  I need to update Item Cost in Item Master with Last Purchase Price.  I want to do this via Formatted Search "Refresh Regularly".

Thank you

Former Member
0 Kudos

Check: