Maximo Open Forum

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
------------------------------

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?

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

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

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?

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