infor.com
concierge
infor u
developer portal
documentation central
customer center
Posts
Categories
Groups
ERPs
CSD
FSM/HRT
LN
M3
Syteline/ CSI
Hubs
Developer
Healthcare
Hospitality
Public Sector
Onboarding
Release Center
CloudSuites
Aerospace & Defense (LN)
Automotive (LN)
Chemicals (M3)
Corporate (FSM/HRT)
Distribution (Sx.e/CSD)
Distribution Enterprise (M3)
Engineering & Construction (LN)
Fashion (M3)
Food & Beverage (M3)
Healthcare (FSM/HRT)
Industrial (Syteline/CSI)
Industrial Enterprise (LN)
Manufacturing (M3)
Public Sector (FSM/HRT)
Solutions
Supply Chain Management (SCM)
Human Capital Management (HCM)
Events
Groups
User Groups
Your Groups
About
Email Community Support
Community News
Infor News
Migrated Forums
Home
Groups
VISUAL - Enterprise Customer Community
Excess & Obsolete Inventory report
udithap
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
Comments
Legacy Contributor
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
Legacy Contributor
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
Child Item
