This is an archive of the Maximo Yahoo Community. The content of this pages may be a sometimes obsolete so please check post dates.
Thanks to the community owner Christopher Wanko for providing the content.
anyone able to help with this one? I'm not sure where/what (table/column) to validate if inventory has had activity against.
thanks in advance
Do something like select * from item where itemnum not in (select itemnum from matusetrans where issuedate > sysdate-365);
Not 100% sure about issuedate but check matusetrans table..
Shannon Sutton
Cell: (318) 334-8002
On May 2, 2013, at 4:05 PM, "oscjake" <oscjake@yahoo.com> wrote:
> anyone able to help with this one? I'm not sure where/what (table/column) to validate if inventory has had activity against.
>
> thanks in advance
>
>
matusetrans and matrectrans
example:
select count(*) , itemnum from matusetrans
where itemnum is not null
and transdate > sysdate - 365
group by itemnum
union
select count(*) , itemnum from matrectrans
where itemnum is not null
and transdate > sysdate - 365
group by itemnum
will show you all items that had activity in the past year (on Oracle)
If you only want to check for one item
select * from matusetrans
where itemnun = 'xxx'
select * from matusetrans
where itemnum = 'xxx'
If both of the above bring back zero records, there is not activity.
This should give you a start.
Pat Morrow
pmorrow8@yahoo.com
________________________________
From: oscjake <oscjake@yahoo.com>
To: MAXIMO@yahoogroups.com
Sent: Thursday, May 2, 2013 2:05 PM
Subject: [MAXIMO List] SQL Query for Inventory with no activity in last 365 days
anyone able to help with this one? I'm not sure where/what (table/column) to validate if inventory has had activity against.
thanks in advance
If you want to look at the data by streroom then this query may help
select itemnum,location as storeroom ,siteid,orgid,issue1yrago,issue2yrago,issue3yrago,issueytd,lastissuedate from inventory
you could add to limit the selection
sql server
where lastissuedate > GETDATE()-365
oracle
where lastissuedate > sysdate-365
Use of the issue quantity fields depends on someone having done the end of year housekeeping in the inventory app i.e.
Inventory adjustments --> Zero Year to Date Quantities
Rgds Ian
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of oscjake
Sent: 02 May 2013 21:05
To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] SQL Query for Inventory with no activity in last 365 days
anyone able to help with this one? I'm not sure where/what (table/column) to validate if inventory has had activity against.
thanks in advance
GDF SUEZ E&P UK Ltd (Company Number 3386464), registered in England and Wales with a registered office address at: 40 Holborn Viaduct, London, EC1N 2PB.
**************************************************************************************************************
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify the system manager.
**************************************************************************************************************
thanks to all the great ideas and examples.
I'm going to work on this today and update the results
thanks again
--- In MAXIMO@yahoogroups.com, "oscjake" <oscjake@...> wrote:
>
> anyone able to help with this one? I'm not sure where/what (table/column) to validate if inventory has had activity against.
>
> thanks in advance
>