Adjust Cost - Item Entries batch job spins...

Fisherman

All -

We are trying to close our month on NAV5, and our accountant ran the Adjust Cost - Item Entries batch job the other day so that he can close out the inventory period. When he did it (no filters), it locked out every user in the system from posting receipts, shipments, production, etc...

I went in to look in SQL Server EM at locks and locked processes, and his spid had a database exclusive lock applied, blocking all other processes.

I've been going through the (extraordinarily convoluted) code, and am not coming up with many ways to really improve the process. I added a filter on Global Dimension 1 Code from the Item card, and when we specify a value, it does run almost instantaneously, BUT, after running through all possible Global Dimension 1 Codes, I tried to rerun using no filters, and it went back into it's never-ending run.

I have so far tried to run for as long as 8 hours, after hours, an have not had it complete yet.

We have been running this without an issue until now.

Does anyone have any suggestions on improving this process? I noticed that it issues table locks almost immediately. Can this locking be postponed until immediately before updates? Any advice would be appreciated.

Thanks.

David_Singleton

The solution is to split Navision costing into three completely separate modules (tables), so we would have the "Value entry" table for FIFO and other forms of specific costing, LIFO Specific etc. "Average Cost" table where we would calculate the average cost of an item. and "Standard Cost" table where the standard cost would be worked out. Then there would be three completely separate costing routines for each of the costing methods. (thought the routines would all be recursively called from one master routine).

Currently the cost update does just tooooo much and all in one table.

Currently they keep looking at how to clean up the current costing, and whilst Olga did a brilliant job to get us this far, the next step is a complete rewrite.

The important thing is to look at how the data is used, and normalize it. Currently using one table to do three different jobs just does not work.

Oh and after all that is calculated it then needs another table that is updated each time with the current costing and that can be used for reporting inventory valuation with out the song and dance we have right now.

Fisherman

David -

So, you're saying that there is no fix for this in the current version? Am I stuck just praying that this stupid thing runs in a weekend?

David_Singleton

Fisherman wrote:

David -

So, you're saying that there is no fix for this in the current version? Am I stuck just praying that this stupid thing runs in a weekend?

Sorry, I must not have been reading properly. I thought you were asking if there were plans long term to change the whole process. I see you just need to get this to run.

First thing is that you shoudl be running this routine every day, not once a month. To catch up, I would suggest that you filter by groups of items selecting a couple of the heavier used items first and see how that goes.

Fisherman

I've found something on this, but I don't know how to fix it.

Update - I ran this over the Labor Day holiday weekend. It ran for 49 hours straight, and never completed. I finally had to kill it this morning so that production could start.

What I've found looks like a user error that has greatly fouled-up this job. I actually have an Item record that has no Item No. The "No." field is blank.

In Looking in the change log, I have found that the item was created, that the item number apparently was mis-keyed, and that the user somehow deleted the key value, and all of the default values that were inserted, but did not actually delete the record itself. I have no idea how this happened.

When I look in code, though, I notice this in the "InvtToAdjustExist" trigger in CodeUnit 5895 (Inventory Adjustment)

  RESET;
  COPYFILTERS(FilterItem);
  IF GETFILTER("No.") = '' THEN
    SETCURRENTKEY("Cost is Adjusted","Allow Online Adjustment");
  SETRANGE("Cost is Adjusted",FALSE);
  IF IsOnlineAdjmt THEN
    SETRANGE("Allow Online Adjustment",TRUE);

I've traced through this. Without applying any filters to the Adjust Cost - Item Entries job, it continually comes to this line of code, finds my record with a blank item number, and processes it over, and over, and over.... ad infinitum. I know that this is supposed to apply a blank filter to the item table, so that it will search for all items that need cost adjustment, but in my case, it's getting the same record again and again and again.

My first thought was to just run the report with an Item Filter of <>''. This doesn't work, though, because when my controller goes to close out the inventory period, it still sees that Item No. '' needs adjusting, and won't let him.

My second thought was to try to delete the bogus record. No dice here, either. It's somehow been tied to entries (I have no idea how this happened, either).

Then, I thought about renaming it to exclude it from this filter. Nothing here, either. Renaming it gives me the following error message:

---------------------------
Microsoft Dynamics NAV
---------------------------
You cannot rename a record because the old value of the field below is '',

Field: No.
Table: Item

---------------------------
OK   
---------------------------

Any ideas on how to solve this issue? Any ideas on how/why NAV would have allowed a primary key value of '' to begin with??

Fisherman

Got it!

The record was somehow tied to several Item Journal Lines, Requistion Lines, and a Purchase Line. Deleted all of those, and I was able to delete the record with no issue. When we went to run the job this time, it finished inside of 2 seconds, unfiltered.

It sounds stupid - but given that there are several other posts on the boards about this thing running long, I might suggest looking for this condition. The code isn't well-prepared for this mistake.