How to Import Standard Cost
john.ellis@tribridge.com asked 9 years ago
A SmartConnect 2013 user imported GP Inventory Transactions (Increase Adjustments) through an ODBC-based map. None of the fields in the source or in the line item mapping reflect cost. The items in GP are all Periodic (i.e. standard cost) items. But, the user found a case today where an item's transaction was imported with Current Cost and not Standard Cost. It would be great to know why this happened. But, instead of "going down that road", is there a way of creating a Calculated Field for the "Add line item" mapping to always import an item's standard cost as the Unit Cost? That is the expected behavior in GP, anyway, even if the inventory transaction were manually entered. It's just that, for this one item's transaction today, that was not the case. Thanks! Much appreciated! John
John,
You could write a Calculated Field using .NET script to pull the Item's Standard cost from IV00101 or you could create a SQL Lookup column to get the value from IV00101 so you aren't having to write .NET code.
Lorren
Hi Lorren: I didn't think about using the MSSQL Lookup column. That's a good idea. A few moments ago, before I got this message with your brilliant idea, I used another approach that tested successfully. But, I want to get your opinion, just the same. I created a SmartConnect Task for "after the document succeeds" that uses the T-SQL script below. Does my reasoning seem sound? In other words, did I do the right thing? 🙂
update IV10001 set IV10001.UNITCOST = IV00101.STNDCOST from IV10001
inner join IV00101 on IV10001.ITEMNMBR = IV00101.ITEMNMBR
John,
Your thinking is correct but are you sure you want to update every single inventory transaction that way? If not, you may want to restrict it to just the IV document being imported and have your task run on Document Success.
Lorren
Hi Lorren: Yes, this task is only going to be in this one map that the user has a concern about. For transactions imported, through this map, the user always wants the standard cost for each line item. And, the task runs my T-SQL script only if the Document Succeeds. Does my logic seem sound? John
John,
I would say that your logic will cause a problem because if there are other IV transactions, they will get updated with Standard Cost as well because you aren't restricting it to a specific IV Adjustment Transaction. Either restrict it to the specific transaction you just created or use the SQL Lookup in the map itself.
Lorren
I may not be explaining this well. The client wants to pull the Standard Cost from Item Maintenance no matter what the item is, in this one Inventory Transactions map. As long as the map does that, that will work for the client. What am I missing?
John,
You are explaining it just fine.
What you are missing is that your SQL Statement will update EVERY inventory transaction that is not posted but is sitting in the inventory transaction work table.
If I create an inventory adjustment and save it to a batch and then you run this map, your SQL statement will change the Unit Cost of my inventory transaction.
You need to restrict which inventory transaction is being updated by your SQL statement.
I get what you're saying, now, Lorren. OK, then, I could update the script to say "where BACHNUMB = 'whatever'". Correct?
John,
That would work perfectly.
Lorren