I tried this out as SQL Server, and it worked.
Original Message:
Sent: 02-27-2025 17:19
From: Steve Bruce
Subject: Searching for inventory that has no usage but has been in the storeroom for more than 7 years.
Hi Teresa,
INVTRANS table records inventory adjustment transactions (balance, cost and so on), but not issues and returns to store. These are in MATUSETRANS. Also, INVENTORY.LASTISSUEDATE should tell you when an Item was last issued. I'd suggest something along these lines (remove comments for use in Where Clause):
siteid = (SELECT defsite FROM dbo.maxuser WHERE userid = :&USERNAME&)
and status = 'ACTIVE'
and location = 'MYSTOREROOM'
and lastissuedate < current_date - 7 YEARS -- (DB2)INVENTORY keeps Last Issued date here
-- and lastissuedate < DATEADD(year, -7, GETDATE()) -- for SQL Server
and exists ( -- Has a current balance
select 1 from dbo.invbalances
where itemnum = inventory.itemnum
and siteid = inventory.siteid
and location = inventory.location
and itemsetid = inventory.itemsetid
and curbal > 0
)
and not exists ( -- Not on any open PO (could extend Status selection for WAPPR, PNDREV, INPRG?)
select 1 from dbo.poline l
inner join dbo.po on
po.ponum = l.ponum
and po.revisionnum = l.revisionnum
and po.siteid = l.siteid
and po.status in (select value from synonymdomain where domainid = 'POSTATUS' and maxvalue in ('APPR','HOLD'))
where
l.itemnum = inventory.itemnum
and l.siteid = inventory.siteid
and l.storeloc = inventory.location
)
and not exists ( -- Not on any open PR
select 1 from dbo.prline l
inner join dbo.pr on
pr.prnum = l.prnum
and pr.siteid = l.siteid
and pr.status in (select value from synonymdomain where domainid = 'PRSTATUS' and maxvalue in ('APPR'))
where
l.itemnum = inventory.itemnum
and l.siteid = inventory.siteid
and l.storeloc = inventory.location
)
order by siteid, location, itemnum
Good luck
------------------------------
Steve Bruce
Original Message:
Sent: 02-26-2025 11:24
From: Teresa Allen
Subject: Searching for inventory that has no usage but has been in the storeroom for more than 7 years.
Thanks for this response, being new to this site I didn't realize anyone responded. I tried your query without luck. Is this SQL server?
------------------------------
Teresa Allen
GAF
Original Message:
Sent: 10-30-2024 08:28
From: Stevie Holloway
Subject: Searching for inventory that has no usage but has been in the storeroom for more than 7 years.
Hi Teresa,
Please try this.
status = 'ACTIVE'
AND siteid = (SELECT defsite FROM maxuser WHERE userid = :&USERNAME&)
AND NOT EXISTS (
SELECT 1
FROM poline pol
WHERE inventory.siteid = pol.tositeid
AND inventory.location = pol.storeloc
AND inventory.itemsetid = pol.itemsetid
AND inventory.itemnum = pol.itemnum
AND pol.receiptscomplete = 0
AND pol.linetype = 'ITEM'
AND EXISTS (
SELECT 1
FROM po
WHERE ponum = pol.ponum
AND revisionnum = pol.revisionnum
AND siteid = pol.siteid
AND status IN ('APPR', 'CONF')
AND revisionnum = (
SELECT MAX(revisionnum)
FROM po por
WHERE por.ponum = po.ponum
AND por.siteid = po.siteid
)
)
)
AND NOT EXISTS (
SELECT 1
FROM prline pr
WHERE inventory.itemnum = pr.itemnum
AND inventory.itemsetid = pr.itemsetid
AND inventory.location = pr.storeloc
AND pr.siteid = inventory.siteid
AND EXISTS (
SELECT 1
FROM pr
WHERE prnum = pr.prnum
AND pr.siteid = pr.siteid
AND status IN ('APPR', 'PEND')
)
)
AND NOT EXISTS (
SELECT 1
FROM dbo.invtrans
WHERE inventory.itemnum = invtrans.itemnum
AND inventory.location = invtrans.storeloc
AND inventory.siteid = invtrans.siteid
AND invtrans.transdate >= DATEADD(year, -7, GETDATE())
)
AND EXISTS (
SELECT 1
FROM dbo.invtrans
WHERE inventory.itemnum = invtrans.itemnum
AND inventory.location = invtrans.storeloc
AND inventory.siteid = invtrans.siteid
AND invtrans.transdate <= DATEADD(year, -7, GETDATE())
);
------------------------------
Stevie Holloway
Tufts University
Original Message:
Sent: 10-29-2024 15:49
From: Teresa Allen
Subject: Searching for inventory that has no usage but has been in the storeroom for more than 7 years.
I am trying to create a query within Maximo for a storeroom attendant to query items in their storeroom that has had no issues for the past 7 years, has been in the storeroom for over 7 years and has no open PO's or PR's. We will use this query to determine whether to obsolete the inventory record from the storeroom. I have a couple queries that I have tried to modify and/or combine but I can't make any of them work together. I would appreciate any help.
This query gives me all items without an open PO
status = 'ACTIVE' and not exists (select 1 from poline pol where inventory.siteid = pol.tositeid and inventory.location = pol.storeloc and inventory.itemsetid = pol.itemsetid and inventory.itemnum = pol.itemnum and pol.receiptscomplete = 0 and pol.linetype = 'ITEM' and exists (select 1 from po where ponum=pol.ponum and revisionnum=pol.revisionnum and siteid=pol.siteid and status in ('APPR', 'CONF') and revisionnum = (select max(revisionnum) from po por where por.ponum=po.ponum and por.siteid = po.siteid) ) )
This query gives me all items with any transactions in the INVTRANS table that are dated within the last month but we need inventory with INSERTTYPE = INSERTITEM and INVTRANS.TRANSDATE > 7 YEARS
siteid = (select defsite from maxuser where userid = :&USERNAME& ) and (exists (select 1 from dbo.invtrans where (datepart(m, transdate) = datepart(m, dateadd(m, -1, getdate())) and datepart(yy, transdate) = datepart(yy, dateadd(m, -1, getdate()))) and (itemnum = inventory.itemnum and storeloc = inventory.location and siteid=inventory.siteid)))
And I want to combine these 2 queries into one. Can anyone help me with this?
#Inventory
------------------------------
Teresa Allen
GAF
------------------------------