Skip to Content
author's profile photo Former Member
Former Member

Update Item Cost w/Last Purchase Price FMS

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on May 14, 2014 at 04:10 PM

    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]

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 14, 2014 at 04:44 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 15, 2014 at 12:37 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.