Identify Parts not used/Updated in Past 5 Yrs
We want to identify list of Items which have not been used in Past 5 yrs. Please let me know if there is a way to identify such data.
Condition to check if Item is not used
1.No Update on item in Past 5 Yrs
2.Item not used in any Bom/no component added to Bom
3.No Change created for Item
3.No Relationships added
4.No Attachment add/download
Thanks
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
Hi Steve,
Thanks for the reply . I am trying to identify the list of Items which has revision release date, Attachment last view date (for items with attachments) less than 5 years from now first using simple search from webclient.
Considering this as Input File, plan is to check the 1. item_history table for the last_upd date.
2.bom table with this item as either parent or component and get the last_upd date.
3. File Download/Add (already captured in Item_Histry)
4. Relationship Add /Remove (already Captured in Item_History)
5. Update of Change Controlled Fields( Handled initially in the search).
6.update of non change Controlled Fields (Covered in item_history table)
Identify the latest of the last_upd dates above and compare with sysdate to get the final result.
Is the above approach sounds logical or am I missing something? Pls correct me if I am Wrong.
Thanks,
Raju
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′);
Hi Steve,
Thanks for your help. I have a few questions.
1. If there is any update on p2,p3 attributes, does the last_upd value of item table gets updated?
2. Are the actions like add attachment/download attachment,View Attachment , Incorporate/unIncorporate tracked under last_upd value of item table?
Thanks,
Raju
To your first point, no. But the LAST_UPD value in PAGE_TWO/PAGE_THREE would get modified.
To your second point, as above, no. LAST_UPD is only used to track updates to the specific table it is an attribute of, like ITEM. So you would have to track attachment stuff through ITEM_HISTORY, using the values in ACTION. What those IDs mean can be found in LISTENTRY where parentid = 4456.