What if costing method was different ?

kapil4dynamics

I want to know that what would have been Cost of Goods Sold and average cost of item per month if costing method would have been average , at present it is FIFO. The Db is running from last 3-4 years. Version 4.0 Sp3. Native db.

What i am thinking is , if i change the costing method with force and run the adjust cost, i shud be able to achieve this.

Will this be correct and any suggestions, or any other better way ?

(Please Don't mention do it in other database not in production , Just Joking )

kapil4dynamics

Any ideas guys i did this, nothing happened. I turned Applied entry to adjust as true as well.

ara3n

you also need to set the flag on item card called cost is adjusted to false.

Also average costing in 4.0 needs to populate the avg. cost entry point table with the dates.
The dates are based on average method.
you need to write a routine, that checks and sees if there is an entry in ILE for a given date and insert a record into this table.

Check Cronus for examples.

After adjust cost is run, these records are deleted, so that next time there will be less entry to fix.

In 5.0, this is changed. The entries remain in this table and a flag. Cost is adjusted is ticked.

kapil4dynamics

Rashed, Thanx a lot for the reply.

I apologize, actually i forgot to mention that i made cost is adjusted to false through a processing report. Yeah but i did not check the avg. cost point entry table. :oops:

I will check this and will update the result on forum when i am through.

kapil4dynamics

Just a confirmation of steps as i am restoring the backup again.

1. I will forcefully change the costing method
2. Will Set Cost Adjusted to as False
3. Applied entry to adjust as true

and last

for every +ve entry in ILE and check there corresponding entry in VLE and pull them from VLE in avg. cost entry point table. Am i missing anything now.

ara3n

if you are using manufacturing, in production component line there is a field called cost is adjust that is also need to be unchecked.


And you'll need to run adjust cost afterward.

Also set the posting dates allow from and to to correct dates.

kapil4dynamics

No i am not using manufacturing. Just trade.

Will this simple report work

"Avg. Cost Adjmt. Entry Point"."Item No.":=VLE."Item No.";
"Avg. Cost Adjmt. Entry Point"."Location Code":=VLE."Location Code";
"Avg. Cost Adjmt. Entry Point"."Valuation Date":=VLE."Valuation Date";
modify;

kapil4dynamics

ara3n wrote:

The dates are based on average method.
you need to write a routine, that checks and sees if there is an entry in ILE for a given date and insert a record into this table.

Check Cronus for examples.

After adjust cost is run, these records are deleted, so that next time there will be less entry to fix.

This means only first entry for each item which is +ve is to be pulled in. I saw this table created some new records as well but once adjust cost is run it never gets the data for same item again irrespective of number of entries u post for that item.

kriki

[Topic moved from 'NAV 2009' forum to 'NAV/Navision' forum]

kapil4dynamics

kapil4dynamics wrote:

Just a confirmation of steps as i am restoring the backup again.

1. I will forcefully change the costing method
2. Will Set Cost Adjusted to as False
3. Applied entry to adjust as true

and last

for every +ve entry in ILE and check there corresponding entry in VLE and pull them from VLE in avg. cost entry point table. Am i missing anything now.

After doing this i wrote this on a report


IF EntryPoint.GET("Item No.","Variant Code","Location Code") THEN
CurrReport.SKIP;


EntryPoint.INIT;
EntryPoint."Item No.":="Item No.";
EntryPoint."Variant Code":="Variant Code";
EntryPoint."Location Code":="Location Code";
EntryPoint."Valuation Date":="Posting Date";
EntryPoint.INSERT;

It did punch in the records but after running adjust cost entries nothing happened. Looks like missing something now as well. Any ideas to add.

ara3n

what is Average Cost Calc. Type and Average Cost Period in Inventory Setup?

kapil4dynamics

It is Item&Location.... and other one is never.

First one i think is i will leave as it is (in my next run) but with this process what would be impact if i change the second one to quarter ?

U know i think i was checking few days back why v shudn't have -ve quantity if costing method is average this whole R&D process gave me the answer (i thmy adjust think so)st is running at this time.

I had to go to CU 22 and write if Applies to entry=0 then skip where it checks this and takes the data on Item Journal line to post.
Does that sound wrong reason for doing this was they did some sales when they had no quantity on hand. Yeah i ran the adjust cost before even touching the db.

But now this goes in Infinite loop. Still waiting to finish.

kapil4dynamics

It did finish but with a message that
There is an integer that is too large
106,014,486,508,173,294,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000.(means i had to cancel it) ](*,)

When digged in the reason was it had some purchase and sales entry with -ve quantity posted from order and invoices.

Now, i tried to avoid during calculation that it should not pick these entries in consideration (which i know is not the correct way) but after doing this, it went into sleep. Means ran the adjust cost ran very fast for few items but for went in loop for this item and after digging with this entry same issue came means -ve quantity posted from orders and invoices. And it will be issue for so many items which haven't come up in batch job but are visible in tables with crappy entries.

I don't know how any consultant can suggest -ve quantity through orders (i would assume if used with apply to item entry would be correct, but not at least with average costing method). I wonder why Document Credit Memo and Return Orders are there for.Pls correct if am wrong.

I noticed for some items they have started with sale with no quantity on hand. ](*,)

But yeah i am not in mood to leave this, if i bring the data in Entry point table per quarter and run adjust cost with keeping allow posting from and allow posting to per quarter since start of the first posting date in ILE will it work and keep the posting date as the last date of quarter. As of now i kept it yearly. I know i may not make any difference.

The best part is i am doing this for a smaller company present in the db means more big garbage is still to be handled and have only this weekend left. But good thing is, the items for which it ran it did the correct cost adjustment for a year and i matched with what was expected.

Any ideas are welcome.

ara3n

the error with long number is a bug and not related to your upgrade, you have to search for a hotfix for it.

You can also try and do these steps in 5.0 sp1 or in 2009.

Going negative, is a problem and adjust cost will not fix them for average cost. You can do a positive and negative adjustment for that day and it will readjust the sales entry with current avg cost.

kapil4dynamics

Finally did it

What is to be done

1. Forcefully change the costing method
2. Set Cost Adjusted to as False
3. Applied entry to adjust as true

AND

4. Set Allow Online Adjustment on item card as True

Run the adjust cost now ur test db will be ready in Average Cost.

Thanx a lot to all for all the tips.