Maximo Open Forum

Hey all, 

So, I pieced together this query a while back with the help of some co-workers and Maximo forums, but I knew it wasn't going to stand the test of time and was going to omit some records that we ultimately want to capture. For the time it was written, it did what we needed it to do.

Fast-forward to today, I'm trying to revisit this and capture a list of specific items from Inventory that have not been:

"Issued to an Asset, Transferred from another Store, or Received into inventory in over 365 days. Balance is not zero, cost is not zero.

My current query below is admittedly very "clunky", but it was the best I could do with my knowledge of SQL.

Here are the conditions I currently have (and want to add). I'm sure there is a more efficient way to write this as I just cobbled it together as I learned.

1. Record is not Obsolete (Not sure this is even necessary as Obsolete records don't have a positive quantity. Redundant?)
2. Last issue date > 365 (or lastIssuedate is null) - Some records have no issue history so the field is blank, which is why I had to include the MATRECTRANS table in mine. 
3. Receipt Date > 365 (or no receipt history) & (I'd like to include all types of receipt transactions including RECEIPT & SHIPRECEIPT but not INVOICE)
4. Current Balance !=0
5. Avgcost !=0

(status in (
    select value from synonymdomain
    where domainid ='ITEMSTATUS' 
    and maxvalue not in ('OBSOLETE')
)
and exists (
    select 1 from invbalances
    where invbalances.itemnum = inventory.itemnum
    and invbalances.location = inventory.location
    and invbalances.siteid = inventory.siteid 
    and invbalances.itemsetid = inventory.itemsetid 
    and invbalances.curbal != 0
    and lastissuedate < current date - 12 month)

and exists (
    select 1 from invcost
    where itemnum = inventory.itemnum
    and location = inventory.location
    and avgcost !=0)
)

and not exists (
    select 1 from matrectrans
    where itemnum = inventory.itemnum
    and tostoreloc = inventory.location
    and fromsiteid = inventory.siteid 
    and itemsetid = inventory.itemsetid 
    and issuetype in ( 
        select value from synonymdomain
        where domainid = 'ISSUETYPE'
        and maxvalue = 'SHIPRECEIPT')
)
and not exists(
                select 1 from matrectrans
        where itemnum = inventory.itemnum
        and tostoreloc = inventory.location
        and fromsiteid = inventory.siteid
        and itemsetid = inventory.itemsetid
        and transdate > current date - 12 month
)
and not exists (
    select 1 from matusetrans
    where itemnum = inventory.itemnum
    and storeloc = inventory.location
    and siteid = inventory.siteid  
    and issuetype in ( 
        select value from synonymdomain
        where domainid = 'ISSUETYPE'
        and maxvalue = 'ISSUE')
)
and not exists(
                select 1 from matusetrans
        where itemnum = inventory.itemnum
        and storeloc = inventory.location
        and siteid = inventory.siteid
        and transdate > current date - 12 month

)