how to get Item ledger entried data from GL entries ?

Ho expert,

Accounting department want me to create a report to get inventory value that match GL entries “net change”,

So I created a SQL query from Item Ledger Entries + Value Entries like this:

Select T0.[Item No_], T0.[Lot No_],T0.[Posting Date], T0.[Document No_], T0.[Item Category Code], T0.[Remaining Quantity], T1.[Cost per Unit],

(T0.[Remaining Quantity] * T1.[Cost per Unit]) as InvValue, T0.[Entry No_], T1.[Entry No_] as VENo

from dbo.[Valley Fine Foods$Item Ledger Entry] T0 INNER JOIN

dbo.[Valley Fine Foods$Value Entry] T1 ON T1.[Item Ledger Entry No_] = T0.[Entry No_] and T1.[Item Ledger Entry Quantity] > 0

where T0.[Remaining Quantity] > 0 and T0.[Item Category Code] = ‘RW’

order by T0.[Item No_], T0.[Lot No_].

But unfortunately, The amount is different with our AcctNo 13000 (Inv of Raw Ingredient). if I remove “Item Category Code”, total amount only has 400,000.0 different.

Does anybody knows how to get correct data from Item Ledger Entry that match with Acct No amount ?

or how to link GL Entries with Value Entries or Item Ledger Entries .


Well first of all, then I would never do that from SQL. No matter how bad the report designer is in NAV 2013, then there are much better ways to do this. But that’s just my personal opinion.
I have very little experience doing this from SQL, as when it comes to NAV, then I have very little usage for it.
In NAV I could do this little trick very easily.

As to your actual code, then not sure why all the extra join filters on T1. Entry No should be enough, or?