Adjust cost for back dated entries

vicky_dada

Hi,

I am using NAV2009, with average costing method. My ACIE is running fine for items with least number of transactions but it is taking time for items with large number of transactions.

My observations on this are when we ran for the first time it took 30 mins to run ACIE but for the next day it took 5 hours to complete and now it is taking even morethan 8 hours and we are not able to run.

We use manufacturing. Please confirm if my assumptions are correct, the user is making back dated entries (Prod. , sales etc), when we click the lookup in the unit cost field in the Item card, I could see Cost is adjusted field to false when the user post back dated entries. So if we restrict users to minimise back dated entries the time may decrease.

If the above assumption is correct, our db is as new as 1 month with only 10000 entries and how to handle if the size of DB grows. Please advice if I am working on wrong areas ( We use Average Costing method).

I tried running the batch job Item Category wise, the batch will run for all items then again we see the item repeted for Cost FW level 2.

jolly_man

vicky dada wrote:

Hi,

I am using NAV2009, with average costing method. My ACIE is running fine for items with least number of transactions but it is taking time for items with large number of transactions.

My observations on this are when we ran for the first time it took 30 mins to run ACIE but for the next day it took 5 hours to complete and now it is taking even morethan 8 hours and we are not able to run.

We use manufacturing. Please confirm if my assumptions are correct, the user is making back dated entries (Prod. , sales etc), when we click the lookup in the unit cost field in the Item card, I could see Cost is adjusted field to false when the user post back dated entries. So if we restrict users to minimise back dated entries the time may decrease.

If the above assumption is correct, our db is as new as 1 month with only 10000 entries and how to handle if the size of DB grows. Please advice if I am working on wrong areas ( We use Average Costing method).

I tried running the batch job Item Category wise, the batch will run for all items then again we see the item repeted for Cost FW level 2.

Personally, i had a lot of bad experience with this adjustment procedure. It is one of the fundamental's NAVISION procedures, because it has to calculate a lot of things.
In most cases, i recommand to my customers to run this procedure, not daily, but in weekends. Then, i have a lot of time to run the procedure which sometimes takes about 48 hours.
Now if you have doubts about it, before you begin the procedure you can look in this table "5804 - Avg. Cost Adjmt. Entry Point". Here are all the products which will be adjusted by the procedure and their begining date for the adjustment. You can check here if there are articles that are adjusting the cost with a long back date. (more than 6 months).
Even if you discover such articles, sometimes is normal, because there may be very old lots in the wearhouse and that have been sold recently. If you don't discover such cases of old lots, then repost here to tell you more about how to solve this thing.

vicky_dada

Hi Jolly man,

Thanks for the reply, I was searching the posts related to Adjust cost back dated entries, I found that this problem generally arise when there are very long back dated entries but I have entries posted only 30 days back.

I tried using the COMMIT function in CU 5895 in MakeSingleLevelAdjmt (after UpdateItemUnitCost;) and MakeWIPAdjmt (after CalcMfgCostAndUpdOutputEntries(ProdOrderLine);) it worked for Native DB but for SQL DB the batch job did not complete. There were other sugesstion like closing the inventory periods, in ILE making Applied entry to Adjust false etc, since the DB is as young as 30 days I feel making these changes might not help me in future.

Awaiting for your replies

jolly_man

vicky dada wrote:

Hi Jolly man,

Thanks for the reply, I was searching the posts related to Adjust cost back dated entries, I found that this problem generally arise when there are very long back dated entries but I have entries posted only 30 days back.

I tried using the COMMIT function in CU 5895 in MakeSingleLevelAdjmt (after UpdateItemUnitCost;) and MakeWIPAdjmt (after CalcMfgCostAndUpdOutputEntries(ProdOrderLine);) it worked for Native DB but for SQL DB the batch job did not complete. There were other sugesstion like closing the inventory periods, in ILE making Applied entry to Adjust false etc, since the DB is as young as 30 days I feel making these changes might not help me in future.

Awaiting for your replies

vicky,
I'm not sure what impact could have this COMMIT, because sometimes errors happen and it should be able to rollback.
Regardin the speed performance you can do some tricks regarding SQL performance, to optimize SQL with Navision (i used a very good little book that explained me some settings to optimize navision). The main ideea i think is to convince your client to run this procedure in weekend.

vicky_dada

No Jolly,

My client is very much aware of the ACIE and he wnats to run daily and even I beleive that when it is not completing in one day, waiting for a week and running may even take more time.

My DB is very young only 12000 entries in ILE :x I am not getting where am I missing.

I need to check the thing why SQL DB was slower than Native but it only happened after writing commit function

vicky_dada

Hi guys,

After all my trials, I came to a conclusion that ACIE is running fine on Native but we have problem is SQL. It is taking atleast 5 times to complete in SQL. For temporary solution we are running the ACIE on Native and restoring back on SQL.

I came to a number of posts regarding the SQL tuning, I have tunned the SQL DB and incorporated recommendations made in SQL Tuning Advisor. I have done all the steps mentioned in the DB resource kit (Index tool,Key Info tool, etc) The ACIE performace has not improved.

After the query DBCC indexdefra blah blah..I could see the Logical Scan Fragmentation, Extent scan fragmentation has not improved. I have checked the Cliemt Monitor file and found that Key "Item No.,Valuation Date,Location Code,Variant Code,Entry No." of Value entry table is taking more time (this is an example there are other keys which take time). I tried disabling the Maintain SIFT index for hot tables, Maintain SQL index and rearranged the key with date and boolean fields at the end of index.

Guys, I am not understanding where to focus . We are using SQL 2008, 64 bit, RAID 1+0. 320 GB HD and NAV 2009.

Waiting for you sugesstions

Thanks

ara3n

try and load sp1 objects.

What costing method do you have?

vicky_dada

Hi ara3n,

I am working on loading the SP1 objects.

I am using Average costing method. I could see for indexes for value entry table haing Logical scan fragmentation around 77%, Extent scan fragementation as 39%. I tried DBCC indexdefra but these percentages doesnot decrease. Please suggest decreasing these percentages.

I have tried disabling the auto growth of database and also made auto update stats, create stats as false to view the effect.

vicky_dada

Hi ara3n

I was going through the code and found that "CheckCyclicFwdToAppliedEntries" function in T339. If I write the code Exit(False) (skip the check) in the function I could see the ACIE iw working fine and the unit cost in the Native and SQl are the same.

can you please give me suggestion on creating indexes for this function etc or will there be any problem if I incorporate this change.

Awaiting for your replies

ara3n

There is another thread on this function.
I've asked MS the same question, that it should only fire for production lines, once I hear, I will let you know.

vicky_dada

hi,

I got some info from MS abt the function as follows:

"Before version 5.0, in certain situations negative inventory, fixed applied credit memos and transfers could cause loops while running the adjust cost – item entries batch due to circular item application entry relations. To address this issue, one has created the couple of CheckCyclic… functions which you can find in table 339 from version 5.0 onwards.

As much as I know, the endless loops I have described above were only being caused for items which had open negative Item Ledger Entries. I saw that there are just a few of them in your database. Is it truly an absolute exception that you have negative inventory on hand for an item and / or per item & location combinations?

If this is the case, you might risk using your code for those item / locations where the inventory on hand is > 0 while posting as the risk of causing inconsistent item application entries might be rather small."

With the above comments can we goahead by executing Exit(false), onlywhile running adjust cost :-k something like this:
Table 339 / CheckCyclicFwdToAppliedEntries :
IF EntryIsVisited(FromEntryNo,ItemApplnEntry) THEN
EXIT(FALSE);
Exit(false);

Awaiting for your comments

These comments were received for one of my Ex.colleague, where he faced a similar problem but in his case the client was not much intrested in costing figures

David_Singleton

vicky dada wrote:

No Jolly,

My client is very much aware of the ACIE and he wnats to run daily and even I beleive that when it is not completing in one day, waiting for a week and running may even take more time.

My DB is very young only 12000 entries in ILE :x I am not getting where am I missing.

I need to check the thing why SQL DB was slower than Native but it only happened after writing commit function

12,000 entries should take a few minutes to adjust. If its taking this long then you have a serious issue. What are your server specs, and what is the configuration.

vicky_dada

We are using SQL 2008, 64 bit, RAID 1+0. 320 GB HD and NAV 2009. Data and Log files are located at different drives.

vicky_dada

Hi ara3n,

any news from MS ?

Markandey_Pandey

Hi all,

I am facing the same problem, its taking too much time when i am poting the sales invoices for certain items. I am using NAV 2009 SP1 and SQL 2005.

Can anybody suggest me the solution.

ara3n

They didn't give me an answer but said that they will look into it.

sendoh

Hi Guys,

any updates on this, hmmm seems this issues arise in APAC Localization only?

regards,

Sendoh

sendoh

Hi Vicky dada,

do you implement this?

Table 339 / CheckCyclicFwdToAppliedEntries :
IF EntryIsVisited(FromEntryNo,ItemApplnEntry) THEN
EXIT(FALSE);
Exit(false);

Please share your experience in this issue. Thanks in advanse

bghubr

Make sure you defragment indexes for the indexed views for the Value Entry and Item Ledger Entry tables
In Nav 2009 flow fields are now index views. Make sure Auto Update Statistics on database is turned on (SQL needs them
to optimize)

Inventory Adjustment codeunit
In this function add the commit noted below in this Procedure

MakeSingleLevelAdjmt(VAR TheItem : Record Item)
LevelNo[1] := LevelNo[1] + 1;

UpDateWindow(LevelNo[1],WindowItem,WindowAdjust,WindowFWLevel,WindowEntry);

TheItem.SETCURRENTKEY("Low-Level Code");
IF TheItem.FINDLAST THEN
TheItem.SETRANGE("Low-Level Code",TheItem."Low-Level Code");

WITH Item DO
IF TheItem.FINDSET THEN BEGIN
REPEAT
Item := TheItem;
GetItem("No.");
UpDateWindow(WindowAdjmtLevel,"No.",WindowAdjust,WindowFWLevel,WindowEntry);

REPEAT
LevelExceeded := FALSE;
AdjustItemAppliedCost;
UNTIL NOT LevelExceeded;

AdjustItemAvgCost;
PostAdjmtBuf;
UpdateItemUnitCost;
COMMIT; //Add a commit here
UNTIL (TheItem.NEXT = 0) OR LevelExceeded;
END;

sendoh

Thanks bghubr, Yes, I already did the SQL optimization(all recommended by Microsoft)regarding adding COMMIT, have you tried this in the production environment?

bghubr

sendoh wrote:

Thanks bghubr, Yes, I already did the SQL optimization(all recommended by Microsoft)regarding adding COMMIT, have you tried this in the production environment?

Yes we have this in our system. And our ledger table contains 60 million rows and value entry 120 million rows.
We process approxiamately 40000 different items per day. And within this we also have freight applied to entries that maybe up to 6 months old thru item charge assignments.

In your sql optimization did you rebuild the indexes on the VSIFT for Value Entry and Item Ledger Entries?
Here is a sql statement to check the sifts and reorganize the indexes on them.

SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'YourcompanyNamehere$Item Ledger Entry$VSIFT$5'),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

ALTER INDEX [VSIFTIDX] on [yourcompanynamehere$Item Ledger Entry$VSIFT$5] Reorganize

You may need to change the $5 to another view depending on how your indexes are setup in your database.

The commit will help since it commits after each item so the server does not have to cache all transactions,
especially when running log running adjust costs.