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 .
Thanks