Excess & Obsolete Inventory report

Has anyone out there (successfully) written an E & O report? I'm looking for something that will give me last issue date and the last receipt date in addition to the qty on hand, demand qty and qty on order. I can easily get the last 3 pieces of information, but am having more difficulty with the last issue & last receipt dates. Is there a table/fields where this is stored? I'm using Access queries to pull this data.

Thanks,

Jill

Find more posts tagged with

VISUAL - Enterprise General Discussions

Comments

unknown

Now, I don't use Access, I use SQL. I also cane barely SPEAK "E&O", much less write one.

The INVENTORY_BALANCE table seems to hold all inventory transactions. If you wrote something like the following, it would tell you the last time something was issued or received.

SELECT MAX(POSTING_DATE) AS DATE
,PART_ID
FROM [VELIVE].[dbo].[INVENTORY_BALANCE]
GROUP BY PART_ID

unknown

Use the INVENTORY_TRANS table, not INVENTORY_BALANCE. INVENTORY_BALANCE is used for the Inventory Balance report and only shows posted inventory values using month-end dates. INVENTORY_TRANS is the actual subledger.

Here's the access query:
SELECT dbo_INVENTORY_TRANS.PART_ID, Min(dbo_INVENTORY_TRANS.TRANSACTION_DATE) AS FIRST_TRANSACTION, Max(dbo_INVENTORY_TRANS.TRANSACTION_DATE) AS LAST_TRANSACTION
FROM dbo_INVENTORY_TRANS
GROUP BY dbo_INVENTORY_TRANS.PART_ID;

That will pull the first and last transaction for each part. If you're looking for only work order issues/receipts, then you'll need to add filters - perhaps even split it into two sub-queries.

udithap

Hi Jeremy, it worked perfectly, even for an accountant who doesn't really "do" .sql! Thank you very much...

Jill

Copyright © 2025 Infor. All rights reserved.