Inventory Value Report

We're trying to put a report together to show historical inventory value on a weekly basis. Because we are in an actual costing environment, pulling from the standard costing is not an option. Our inventory team is currently running the inventory value report every Friday, exporting to excel and then totaling. This is something we would like to automate.

I've been able to replicate current costing from the INVENTORY_TRANS table by adjusting in/out QTY - COSTED_QTY. Then getting a weighted average of each cost category to determine the total.

Unfortunately this method only works for current costing and need to look back at historical cost for a given product in time.

In the future, we can simply take a snapshot of the current costing stored in a separate table for this purpose but I'm having a hard time looking back in time.

Find more posts tagged with

VISUAL - Enterprise General Discussions

Accepted answers

All comments

unknown

Have you used the Inventory Balance report. That will give the costed values of your parts as of the end of the month. The nice thing is there is a history of the costs of previous months. The report can be found on the ledger tab under reports Inventory Balance. I hope this helps.

unknown

Debbie, thanks for the quick response! This is very helpful. Do you know what each of the flags are in this table? (A,B,N)

unknown

Active, New, Balance

unknown

We use the Inventory Balance report for our accountants at the end of the financial year. Unfortunately, it never matches the inventory balance totals in the nominal accounts. This has been the subject of endless support incidents for us since 2006. I had hoped that version 9.8 would have helped but we are thousands of pounds out of kilter again. So, not very accurate I'm afraid. We were about £30,000 out at one point.

Elaine

unknown

I went through the inventory valuation report being out of balance with the inventory balance report! I'm afraid to tell you that my resolution was a long and painful process.

First, you need a fully costed database. I run this on a Virtual machine where no one is transacting anything.

select distinct * from
(Select Sum(qty)as PLQty, part_id from part_location group by part_id) as a
inner join
(Select sum(qty) as INVBALQty, part_id from inventory_balance where posting_date='1/31/2021' group by part_id) as b
on a.part_id=b.part_id where a.plqty<>b.INVBALQty

This will tell you which parts are off. (you need to change the posting date to the most current posting date)

Then it is basically looking at the costed layer detail reports vs the inventory balance reports and finding if any transactions are not there and fixing them one by one. It was an arduous year long process for me. BUT, the two reports are finally staying in sync so far!

Copyright © 2025 Infor. All rights reserved.