Raju, I would agree with Raj… the easiest way to do this is via SQL query against the Agile database.  What constitutes an item update for you? Some attributes are change controlled, while others are not.  Is it enough to know whether the item was an affected item on a change order or do you need more?  It is possible to change the Description of an item, for example, without putting it on a change order.  Perhaps the simplest way to see if ANY change was done for an item is to look at the LAST_UPD_DATES for ITEM, PAGE_TWO, & PAGE_THREE tables.

Another question… I can see where adding an attachment constitutes “use”, but does downloading one?  Is possible that someone was simply referring to the document without actually using the item. Just a thought 🙂

You also may want to be careful of saying that the creation of a change order for an item means that it is used.  I don’t know about your place of business, but where I work, engineers have a bad habit of creating a change, but never releasing it… not often, but it happens. With that said, you’ll probably want only changes that have actually been released.  Or maybe not.  It’s possible that someone is “about to use it”, but just haven’t released the change yet.

I can work on a query for you, but be advised that the performance of such a query may not be that good. It’s much faster to find where something does exist than where something does not exist.

-Steve

Hello Raju

  You could get this info via SQL queries. I don’t believe there is anything out of the box that would help do this

Thanks

Raj

Raju,

I wouldn’t say that your approach is necessarily wrong, but let me say this… if you’re going to be doing direct queries against the database, then there’s not much point in attempting to get some initial list from the web client. I don’t know about where you work, but our company makes lots, and lots or parts. If you run the web client search and come up with more than 5000, which at least in our case, is the limit you can view in the search results, then you’re left with an inaccurate input file. Additionally, there’s probably a way to avoid using the ITEM_HISTORY table.  While I do query that table when needed, but it is a little more cumbersome to search for specific text in the history details.  I can work on a query for you as time permits.

Regards,
Steve

Raju,

As a show of good faith, I just wanted to show you some progress.  So far this query only involves the ITEM, PAGE_TWO, and PAGE_THREE tables, but I should be able to union addition sub-queries to include all other areas of interest. This query results include the name of the table that the latest update took place in.

Steve

SELECT * FROM
  (SELECT ID, ITEM_NUMBER, LAST_UPD, TBL_NAME 
    , RANK() OVER (PARTITION BY ID ORDER BY LAST_UPD DESC) IS_LATEST
  FROM
    (SELECT I.ID, I.ITEM_NUMBER, I.LAST_UPD, ‘ITEM’ TBL_NAME
    FROM AGILE.ITEM I
    UNION ALL
    SELECT P2.ID, I.ITEM_NUMBER, P2.LAST_UPD, ‘PAGE_TWO’ TBL_NAME
    FROM AGILE.PAGE_TWO P2
    INNER JOIN AGILE.ITEM I ON P2.ID = I.ID AND P2.CLASS = I.CLASS
    UNION ALL
    SELECT P2.ID, I.ITEM_NUMBER, P2.LAST_UPD, ‘PAGE_THREE’ TBL_NAME
    FROM AGILE.PAGE_THREE P2
    INNER JOIN AGILE.ITEM I ON P2.ID = I.ID AND P2.CLASS = I.CLASS
    ))
WHERE 1=1
AND IS_LATEST = 1
AND LAST_UPD < TO_DATE(’01-JAN-2015′);

Good point Raj… I will include it.

Steve