Report on Stock Valuation at a certain date

Toddy_Boy
Toddy_Boy Member Posts: 232
edited 2013-05-29 in NAV/Navision Classic Client
One of the reports run for the auditors is a bespoke general stock summary report (stock valuation I guess) by location based on the location and item ledger entry tables, the ILE table is being filtered by Open item ledger entries with the report calculating remaining quantity * unit cost

With the report filtering only Open item ledger entries is not great as we cannot get a reflection of the stock value at a point in time unless that point in time is right now and no-one touches the database until the report is run.

Where can I start to build an equivalent report or is there a standard one available? I think the Item Register is a good place to start, somehow link to the ILE table, is there something on the ILE table that can be used to determine the date the item ledger entry was closed, this would help determine if the ledger item was closed ?

We are on objects GB2.01.B SQL Server 2009 classic client

Regards

Steve
Life is for enjoying ... if you find yourself frowning you're doing something wrong

Comments

  • Toddy_Boy
    Toddy_Boy Member Posts: 232
    Anyone?

    If I join the Item Register table (filtered by a date range) to the Item Ledger Entry table and total the unit cost * quantity will that give me the stock valuation or is that too simplistic? [-o<

    Regards

    Steve
    Life is for enjoying ... if you find yourself frowning you're doing something wrong
  • Kishorm
    Kishorm Member Posts: 921
    If by "at a certain date" you are referring to the "Posting Date" of the transactions then you can use the standard "Inventory Valuation" report (1001) - apply a filter on the "Location Filter" field on the the Item dataitem and set an End Date on the options tab.
  • jglathe
    jglathe Member Posts: 639
    Hi,

    2.01B on 2009 SQL... cool :mrgreen: I'm afraid this isn't possible with this business logic. The problem is:

    - you could calculate the quantity at date by summing up the item ledger entries.quantity until date (by location),
    - you can't delimit the unit cost at date, since you have no value entries or anything else where the valuation can be retraced. The valuation is always the actual, depending on item applications and Adjust Cost for tem Ledger Entries. You could try to reconstruct the valuation by finding out which g/l entries are posted for the item ledger entries, but depending on your application this may be a futile effort.

    In the NA version, there is a report 10138 "Inventory to G/L Reconcile". This is also available in 2.01B (or 2.60, the version is US2.00). Alex Chow has written a blog post about it: https://community.dynamics.com/nav/b/na ... aZKd5z4KXk
    This report does a calculation as good as possible, IMO, walking through the applications. But however, even this version doesn't do an "at date" calculation (newer versions do). But as far as I understand it, it could be made into a report which can do an "at date" calculation:

    - Calculate the "open" flag at date. This can be found in T32 "Item ledger Entry" in NAVW16.00.01.01, function "CalculateRemQuantity". This should be adaptable to 2.01:
    CalculateRemQuantity(ItemLedgEntryNo : Integer;PostingDate : Date) : Decimal
ItemApplnEntry.SETCURRENTKEY("Inbound Item Entry No.");
ItemApplnEntry.SETRANGE("Inbound Item Entry No.",ItemLedgEntryNo);
RemQty := 0;
IF ItemApplnEntry.FINDSET THEN
  REPEAT
    IF ItemApplnEntry."Posting Date" <= PostingDate THEN
      RemQty += ItemApplnEntry.Quantity;
  UNTIL ItemApplnEntry.NEXT = 0;
EXIT(RemQty);

    - filter the item ledger entries until date, also for the applications.

    Maybe it's worth a try. Unfortunately I have a similar problem, with an actual bastardized 5.0/2009R2 database, and revaluation postings. And I have a hard time to explain some obvious bugs to the auditor...

    with best regards

    Jens

Categories