Expected Cost Not Posted Out

matttrax

Using Navision 3.7. All of the inventory is serialized and Specific costing is used. Expected Cost Posting to G/L is not turned on.

I know that many bugs were fixed in later versions regarding Expected Cost, but I don't know that they caused our problem necessarily.

Basically there are several items where the PO has been received and posted. The SO has then been shipped and posted. But when you look at the item ledger entries for that item (which is easy to find since everything has a unique serial), the "Cost Amount (Expected)" field is not zero. It seems like it's usually when someone negative then positive adjusted the item (for whatever reason) at different costs, but then the original cost was used when it was actually sold.

This is throwing off things like Inventory to G/L Reconcile. Anyone know of the best way to fix this?

David_Singleton

I assume you ran the Item adjust cost routine?

matttrax

They do run it every month, but I will confirm.

Not to stray too far off topic, but I've always wondered if that was necessary when everything was setup as specific costing. Since the exact cost of the item being sold is always known, there's not really anything to adjust, is there?

David_Singleton

Specific costing is just FIFO but limited to just one Item Ledger entry. There are many cases where the costs can change and wont correctly update if you don't run the routine.

So yes you do need to regularly run this.
The better solution is to turn off auto cost posting, and then run Adjust and post routines manually this makes for a much cleaner database. Unfortunately most clients want to see numbers immediately, even if they are wrong.

matttrax

They actually run Adjust Cost several times a month.

I don't know much about the inner workings of adjust cost, but apparently the values that are throwing off the reports are in closed accounting periods. Would that prevent the expected cost from being adjusted out?

David_Singleton

Assuming this is the same database as the otther issue you are having, I would assume both to be realated.

Since you have modified fields direct in SQL, I would concentrate on this as being the cause.

Most likely you have two issues that need to be resolved.

First the SQL indexes are some how out of sync with the Navision keys. The simplest and most reliable fix for this is a Navision FBK backup and restore.

Second is that it looks like you have inconsistant data in the actual Navision tables, ILE and VLE. There are many ways to fix these, but its a big job if you want to do it properly. It might be easiest just to flush all inventory to zero, delete all item ledger entries and then bring in new values as opening inventory. It wont be possible to keep the old entries becasue that wont fix the inconsistency. Of course DO NOT DO THIS FROM SQL, it must be done inside Navision, and make sure the person doing this knows what they are doing.

matttrax

Yeah, the developers / consultants who worked here before me were big on getting around the business logic. It's made things so much easier for me

Oddly enough I believe they are completely unrelated issues as some of these ledger entries are from 2004. They just never used the reports because they could tell the data was so bad. Entries were made to the G/L to correct values instead of actually correcting inventory. Now I'm trying get them back on track and fix things...maybe even use a Navision report or two.

There's no way they'll let me delete all old item ledger entries. More fun for me...was just hoping there might be an easier way.

I really appreciate all your help. I'll make another post with details of my fix later for people who may encounter the same problem.

Karenh

Instead of deleting ILE records, you could zero out the inventory, create new items numbers, and then bring in the inventory with new item numbers. You could add a field for the old number, with an ILE lookup to make it easy to review the history.

The new numbers could b a prefix plus the old numbers if you are happy with the old item number scheme.

Alex_Chow

David Singleton wrote:

There are many ways to fix these, but its a big job if you want to do it properly. It might be easiest just to flush all inventory to zero, delete all item ledger entries and then bring in new values as opening inventory. It wont be possible to keep the old entries becasue that wont fix the inconsistency. Of course DO NOT DO THIS FROM SQL, it must be done inside Navision, and make sure the person doing this knows what they are doing.

Please follow David's suggestion to heart. Don't try to do anymore funny things, you'll shoot yourself in the foot. I can almost guarantee it.

matttrax

Alex Chow wrote:

Please follow David's suggestion to heart. Don't try to do anymore funny things, you'll shoot yourself in the foot. I can almost guarantee it.

Haha, I would love it if I could this place to the point where funny things didn't have to be done. I can't even save a copy of the Item Ledger Entry table in Object Designer without an error.

But yes, I will be following David's suggestions. That's why I ask my questions here While I know a lot, I also know that I don't know the half of it. That level of knowledge can only be gained through years of experience and tons of implementations. Thanks for all of your help.