Item ledger entries - ACIE

violeta

How to prevent certain item ledger entries from being ajdjusted by ACIE? I trided with Applied Entry to Adjust Field only on entries I want to adjust but it adjusts all entries on certain item.

Or how to prevent certain items or group of items from being adjusted?

Costing method is Average, Navision 4.0 SP3.

Thanks

David_Singleton

Its not possible, and any code you write to do this will be a disaster.

Rethink what you are doing, since it sounds like the company should be using STANDARD costing.

violeta

David,

Thanks for your answer. Company is using average costing method since 01.01.08. and it worked fine (automatic cost adjust, and manual from time to time just to be sure). But now I have some special situation (import from another system) and I have wrong COGS for about 300 items from another system (sales invoices imported and already posted in navision) and request to keep it like that I know what will ACIE do to them so I have to find the way to skip these entries..

bstoyano

hi,

A workaround may be to ask your developer to add a filter for items for the ACIE. Like in NAV 5.

What's wrong with the COGS of these 300 items?

violeta

Thank you Boris,

Filter on CU5895? OK that will help for 1 item. But what if I want to skip only one item ledger entry on that item?

Another problem is that I have multiple companies in one DB so if I choose not to adjust some ledger entry directly in code the same will apply to ledger entry with same Entry No. in another company.

Wrong amounts of COGS weren't created in NAV, they were imported from another specialized system. Simple mistake in caculation of unit cost and line amount but discovered too late

bstoyano

Can you specify what exactly you imported - item ledger entries for sales, or GL entries, or something else?
If I were to import such data in NAV I would create documents, e.g. sales orders, and post them in NAV.

as for ACIE skipping single entries - it is not adviseable to manually change data in the ledger. Although I have done it once, but there was no other way actually.

violeta

Saled header & sales lines

bstoyano

So you imported sales documents and posted them in NAV, and when you saw the COGS you were not happy

violeta

bstoyano wrote:

Although I have done it once, but there was no other way actually.

Can you explain... pls

David_Singleton

violeta wrote:

bstoyano wrote:

Although I have done it once, but there was no other way actually.

Can you explain... pls

And at this point it all goes down hill

Violeta, please contact me in about six months tie when you realize you have damaged your inventory valuation beyond repair and need help to fix it. ](*,) ](*,) ](*,) ](*,) ](*,) ](*,)

violeta

Hehehe, I forgot to say that I'm your big fan David. This is the first time I post to this forum but i use it for a long tome and your answers to other users helped me a lot.

I know that you always suggest logical and good answers but sometimes in real life there are request that are not logical and we need to do all those spooky things

bstoyano

Well, actually it couldn't have gone worse
And I spent a whole week in calculations to check if this could harm anything else. So far (1 year) everything is fine. I corrected wrong value entries, that were created by ACIE and there was a bug in ACIE - something used to go wrong with invoiced quantities. So actually there was no other way to fix old entries, at least Microsoft did not provide automatic bug fix for old entries.

I will look through my papers to see what I've done. But I think in your case it is not absolutely necessary to carry out "surgery" on the ledgers.

Check where did the costing go wrong? Can you do any revaluation through Revaluation Journal to inbound Item Ledger entries prior to the sales?

David_Singleton

violeta wrote:

Hehehe, I forgot to say that I'm your big fan David. This is the first time I post to this forum but i use it for a long tome and your answers to other users helped me a lot.

I know that you always suggest logical and good answers but sometimes in real life there are request that are not logical and we need to do all those spooky things

Thanks, I am really glad to hear that.

Seriously though don't modify code for specific entries, it will cause problems for ever that you will see for the next five years.

Work out the business need and get the data in there the way it is needed. Use revaluation journals if necessary.

violeta

Boris,

I think you didn't understand my problem

Here is an example

Purchase: quantity=4; unit cost=10; value= 40
Sale: quantity= 4; unit cost= 2,5; value=10

I want it to stay forever like that only for that one sale. If I run ACIE it will change unit cost and value on sale and I don't want that

bstoyano

Yes, I do not understand WHY you want to spoil the costing.

Anyway, as I promised you, I checked my notes. Unfortunately, my case was quite a different one. I had problems with Invoiced quantity on output entries and I had to make ACIE go through the output ILEntry once again after I had manually corrected the quantity of four value entries. I also watched very carefully what was happening with value entries and GL entries so as to avoid a mess there.

Here is what I have done:

---

Table 5802 - The Value Entry type 'Rounding Entry' is the VE we need to focus on.
1. Change the Entry Type of the 'Rounding Entry' to 'Direct Cost' [Make this field editable]
2. Take the amount from 'Cost Amount (Actual)' and transfer that amount to the 'Cost Amount (EXPECTED)' field with the opposite (-) sign. For example, if you have $100 in Actual, $0 that out and put in -$100 in Expected.
3. Populate the Invoiced Quantity field with the quantity of the output.
4. $0 out the 'Cost Posted to G/L' field [This is so the WIP account does not debit by double the amount it would if you do not do this step, when running Adjust Cost]

Table 32 - Also, on the Item Ledger Entry,
1. On the Output ILE entry, populate the 'Last Invoiced Date' field as the posting date.
2. Enter the 'Invoiced Quantity'
3. Mark 'Applied Entry to Adjust' flag on the Output Entry

Item Card - Unmark 'Cost is adjusted' flag (will need to make the field editable on the table)

>Then run Adjust Cost
>Then run the Post Inv. Cost to GL (PER ENTRY).

---

However in your case you want ACIE to miss some entries. I think that your approach is OK. I mean for a certain item you have to make sure that no ILE has Applied Entry to Adjust BEFORE the date of the entries you want to miss. If you have such entries NAV will open all consequent entries and adjust the cost. Your chance is if all previous entries have already been adjusted. If not, you can still unmark them and make ACIE think everything is OK with them. This will work if your cost is stable over time. HOWEVER, BEAR IN MIND THAT THIS CAN BE A DISASTER FOR YOUR DATABASE. Check it very-very carefully in a test database. And also remember that this will affect the costing.

That's all I can do so far. Be very careful.

David_Singleton

violeta wrote:

Boris,

I think you didn't understand my problem

Here is an example

Purchase: quantity=4; unit cost=10; value= 40
Sale: quantity= 4; unit cost= 2,5; value=10

I want it to stay forever like that only for that one sale. If I run ACIE it will change unit cost and value on sale and I don't want that

And what are you going to do with the $30?

bstoyano

Just checked in Cronus - if you unmark the Valued by Average Cost field in Value Entry table for the sale entry, ACIE will not change the cost.

Good luck and [-o<

violeta

Thanks a lot for for your answers Boris. I will think a little bit more, and I will let you know what I decided to do

Have a nice wekeend

violeta

bstoyano wrote:

Just checked in Cronus - if you unmark the Valued by Average Cost field in Value Entry table for the sale entry, ACIE will not change the cost.

Good luck and [-o<

Boris,

I didn't sow this post earlier. This is the kind of solution I was looking for. Thank you, thank you, thank you
Feel free to contact me anytime you need help

bstoyano

Don't mention it. We have all come here to help each other. But be cautious, because this will affect your average costing, in your case it will make it higher than the real cost. If the values/costs are very much different that may cause substantial diversion/deviation and in severe cases it may even cause negative inventory value at some moment (and the accountants will lynch you for this !!!). Not to mention that most probably such costing is somewhat against the accounting principles and as such is illegal. Use it very carefully.

David_Singleton

Boris,

in my opinion, advising a Navision user to go in and directly modify a Ledger Entry on a posted document. Is about the worst possible advise you can give. You think you are helping because in the short term the user has a solution, but you give no consideration to the long term damage. Not only that but in the future, other users will search this forum and find this advise (AND NO THEY WILL NOT EVEN SEE LET ALONE READ OR FOLLOW THE WARNINGS YOU POSTED), they will get into SQL and directly modify entries on the basis "It was advised to do this on mibuso".

Between this and the "Please teach me Navision because I am too lazy to learn" posts and this "Here is how to set your head on fire with a bucket of petrol" posts, it really makes me want to completely give up on the whole Navision community.

bstoyano

David,

I know that to some extent you are right. :? And there might be users that will rush through the lines. However, if someone has such type of a problem and is facing a wall, aren't we supposed to help? I guess that is the idea of this community.

What should we do when there is no other way out? I myself was forced once to do such an interference. I consulted with people here, some of which have more than 15 years of experience. I consulted Microsoft support engineers. Since we couldn't find any other solution, moreover we were advised by Microsoft engineers to fix the specific problem by manually changing the wrong entries, we did interfere the ledgers.
What I mean is that sometimes there is no other way out.

I would like to know what you think of it And the other experienced users too? What do the forum users do in such cases? Should anyone try to help in such tricky cases? If most of you think it is not right, I will change my posts and hide some of the info, or will ask the moderators to delete my posts, and thus prevent the spillover effect (:) don't know if it is the right word).

David_Singleton

There is an old joke:

A man goes into a Rolls Royce dealership and sees the latest, brand new, top of the line Rolls Royce fitted with every possible option and feature, he asks the sales person "how much is this car" to which the sales person replies "If you have to ask that question, then you can't afford it".

I think it is very valid in this case.

violeta

David,

Im sorry we made you this angry At some point you are very right, but on the other hand Boris saved me a lot of time with his solution. As you can see from my posts I would do this anyway, the question was only how much time do I need to find how.

Think like this: What is the worse thing that could happen if I put checkmark on Valued by Average Cost field? I could mess average pricing on those items, and at some point if I don't like it I can uncheck it again and run ACIE that will recalculate it to the right value. Do you agree? It's not like im changing posted amounts, dates, posting groups etc... I don't want to change posted entries, I just want to change the way they will behave in the future.

As I said, I really appreciate your work on this forum but this time I think that we are not that much guilty as you think

David_Singleton

Yes you are right. I need to stop caring, it is not my system and why should I care.

I am not going to get involved in discussions like this again, it just is not worth it.

bstoyano

David, your joke has an unfriendly connotation. Are you willing to insult me?

David_Singleton

a/ the joke was NOT aimed at you, and b/ as I said I really am losing interest.

Sunset

The problem with giving the advice of manually changing the entries is that your average user will have no idea what has gone wrong when the next upgrade says that his inventory has a faulty value.

The story will go something like this:

John Smith has a similar problem at the company he works. He sees this solution, and implements it. 1 year later John Smith changes jobs and will happily forget that he has made these changes. 2 years later John Smiths old company makes an upgrade to NAV 12, but now the inventory has a difference of 5.000.000. The difference appears because NAV has changed the way the inventory is handled, and obviously does not handle that manual changes have been made to the ledger entries. Now what should John Smith's old company do? Where does the 5.000.000 come from, and can they trust the old values? Can they trust NAV, and if not will they then change to a different system? If they change systems, was it good NAV advice that John Smith implemented?

The problem with giving out such advice on open forums is that people tend to implement them without fully realizing the consequenses it may have later.
If you have faulty data, you have a greater risk of making faulty decisions. Too many faulty decisions and you run the business into the ground!

David_Singleton

Sunset wrote:

The problem with giving the advice of manually changing the entries is that your average user will have no idea what has gone wrong when the next upgrade says that his inventory has a faulty value.

The story will go something like this:

John Smith has a similar problem at the company he works. He sees this solution, and implements it. 1 year later John Smith changes jobs and will happily forget that he has made these changes. 2 years later John Smiths old company makes an upgrade to NAV 12, but now the inventory has a difference of 5.000.000. The difference appears because NAV has changed the way the inventory is handled, and obviously does not handle that manual changes have been made to the ledger entries. Now what should John Smith's old company do? Where does the 5.000.000 come from, and can they trust the old values? Can they trust NAV, and if not will they then change to a different system? If they change systems, was it good NAV advice that John Smith implemented?

The problem with giving out such advice on open forums is that people tend to implement them without fully realizing the consequenses it may have later.
If you have faulty data, you have a greater risk of making faulty decisions. Too many faulty decisions and you run the business into the ground!

=D> =D> =D> =D> =D> =D> =D>

Thank you sunset, you have put in words my exact thoughts. :thumbsup:

Sunset

David Singleton wrote:

=D> =D> =D> =D> =D> =D> =D>

Thank you sunset, you have put in words my exact thoughts. :thumbsup:

You're welcome David
Although I do think that when we are thinking about what consequences it may have several versions down the line; we have been in the game too long :whistle:

bstoyano

I think it's clear enough that such measures are extreme and are definitely a sort of patch. As for the consequences in the future - I definitely have learned from your discussion.