Adding last cost to new stock location in IM/Loc file

[rvb001]

We are consolidating our material locations from 9 to 1. In preparation to do a last to standard cost roll, I need to copy the last cost from the old location/locations to the last cost in the new location. In the past the same part number may have been stocked in multiple locations, so I don't know how to easily find the true last cost. In other words, the last cost may be in one of 9 locations.
Is there a easy way to do this easily, (over 10,000 P.N.s), or is brute force required.

 

[crystalreporting]

Pervasive or MS/SQL?

Peter Shirley

http://www.srhconsulting.com

 

[rvb001]

MS/SQL

 

[crystalreporting]

Off the top of my head - you could create a Crystal Report on the IMINVTRX file; group by item number and sort within that by transaction date (trx_dt). Drag the item item_no and new_unit_cost fields into the group footer and suppress all other sections. Because the report is grouped by item and sorted by date, what should appear on the report is the item number and latest cost for each item (regardless of location).

If the numbers look OK, you could then create a formula (or simple text object) to create the update statement you need to update IMINVLOC e.g. "Update IMINVLOC_SQL set last_cost =" + {IMINVTRX_SQL.new_unit_cost} + "where item_no = " + {IMINVTRX_SQL.item_no}". You'd want to add a where clause to just update your new location. Place the formula or text object in the group footer. Run the report and export it to text or RTF. Copy and paste the results into Query Analyzer, run the query.

I'm sure there's a better way to do this with a SQL statement but it's been a long day....

Peter Shirley

http://www.srhconsulting.com