While Kevin is most certainly correct, it has been my experience that most end users, even those who are admins of Agile, do not have direct access to run UPDATE scripts against the database if you are not a DBA.  Sure, you can create scripts and, with appropriate approval, get your DBA to run them, but that’s not convenient especially if you need to do this repeatedly.

Another way to do this is via SOAP request in SOAPUI by Smartbear.  (http://www.soapui.org | http://www.smartbear.com)

You can use the updateObject request under the BusinessObject_Binding wsdl.  Here’s a sample.  In this sample, assume you have two users with logins of user.one and user.two

<soapenv:Envelope xmlns:soapenv=”http://schemas.xmlsoap.org/soap/envelope/”>

<soapenv:Body xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>
<updateObject xmlns=”http://xmlns.oracle.com/AgileObjects/Core/Business/V1″>
<request xmlns=””>
<requests><classIdentifier>User</classIdentifier><objectNumber>user.one</objectNumber><data><AGILEUSER.ENABLED xsi:type=”AgileListEntryType” attributeId=”12643″ xmlns:xsi=”http://xmlns.oracle.com/AgileObjects/Core/Common/V1″><selection><value>Inactive</value></selection></AGILEUSER.ENABLED></data></requests>
<requests><classIdentifier>User</classIdentifier><objectNumber>user.two</objectNumber><data><AGILEUSER.ENABLED xsi:type=”AgileListEntryType” attributeId=”12643″ xmlns:xsi=”http://xmlns.oracle.com/AgileObjects/Core/Common/V1″><selection><value>Active</value></selection></AGILEUSER.ENABLED></data></requests>
</request>
</updateObject>
</soapenv:Body>
</soapenv:Envelope>

Good point Raj… I will include it.

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′);

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

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

Thank you both Keith and “D”.  I’ve tried your suggestions, but here’s what I’m running into if I use PRE… When going from a Pending state to either Submitted or Review, Agile’s default behavior is to pop-up the “Change Status” dialog box to request users to select approvers, etc. No PX will begin until the Finish button is clicked. During the time that the PX runs, the dialog remains open while things process.  One of the critical functions of my PX is to populate both a page 3 attribute and the history with information. Once this happens, an “Object has been modified” error is shown within the dialog. I believe this happens whether the PX passes of fails. The question now is… is there any known workaround? Can the dialog be suppressed? I don’t think it can be done by the PX because, like I said, the dialog come up prior to the PX being triggered.

Thanks again,
Steve

Hi Raju,

Here is an alternative to what Arif offered. This one returns only one record for each path that the item takes to a different upper level assembly, as opposed to multiple records for each level in the same assembly. I hope its useful, although you did say that you may want to choose the level, so Arif’s solution may work better for you. Note that mine includes Change orders numbers, release dates, etc., but you can do away with that if not needed. See BELOW

Steve

Select Distinct
regexp_substr(Sys_Connect_By_Path(Bom.Item_Number, ‘|’),'[^|]+’,1,1) ITEM_NUMBER
,Sys_Connect_By_Path(Bom.Item_Number, ‘|’) Item_Path,
I.Item_Number TOP_ITEM,
trunc(I.CREATED) AGILE_CREATE_DATE,
I.DESCRIPTION TOP_ITEM_DESCRIPTION,
Bom.Item ,
C.Change_Number,
Trunc(C.Release_Date) Release_Date, –PROVIDES A CO RELEASE DATE WITHOUT THE TIMESTAMP
I.Created Create_Date,
BOM.ITEM_NUMBER,
CHANGE_IN ,
CHANGE_OUT ,
COMPONENT ,
LEVEL ,
BOM.SITE ,
BOM.FIND_NUMBER,
Bom.Quantity
From Agile.Bom
Inner Join Agile.Item I On I.Id = Bom.Item
Left Join Agile.Change C On Bom.Change_In = C.Id
Where Change_Out = 0 –THIS GETS MOST RECENT BOM
And Bom.Flags Like ’00_11%’ –THIS FLAG SETTING IS A WhereUsed REQUIREMENT

–NOTE THAT THE FOLLOWING LINE REFERENCES AN SITE ID THAT IS SPECIFIC TO  MY PLACE OF BUSINESS
–IN OUR CASE, ZZZ IS OUR MASTER ORG. YOU MAY UNCOMMENT THE FOLLOWING LINE AND REPLACE WITH YOUR OWN
–And Bom.Site = 250419868 –LOOK FOR BOM IN ZZZ ONLY

–THE FOLLOWING OPTIONAL LINE INSURES THE UPPER LEVEL ASSEMBLY’S SUBCLASS IS ETO ITEM. 
–REPLACE ‘ETO Item’ WITH THE NAME OF THE SUBCLASS THAT YOU CONSIDER THE TOP LEVEL
–And (Select N.Description From Agile.Nodetable N Where N.Id = I.Subclass) = ‘ETO Item‘

AND ( NOT EXISTS
(Select 1
FROM agile.BOM A
WHERE BOM.ITEM = A.COMPONENT
AND A.CHANGE_OUT = 0
AND A.FLAGS LIKE ’00_11%’
) )
CONNECT BY NOCYCLE PRIOR ITEM = COMPONENT
AND CHANGE_OUT = 0
AND BOM.FLAGS LIKE ’00_11%’
START WITH BOM.ID IN (
( Select Bom.Id
From Agile.Bom
Where Bom.Component In
(SELECT I.ID FROM AGILE.ITEM I
WHERE I.ENCODE_NAME = :ITEMNUMBER
)
AND Bom.Change_Out =0
And Bom.Flags Like ’00_11%’)
);

Hi again Keith.
I’ve given your pre-event suggestion some thought and feel like this is worth looking into. It seems like one of our developers tried pre-events before but found some issue with using them. At this point, I can’t recall what that issue was, so I’d like to know more about it. I’m assuming that the event object must have a method to tell it there is an error and prevent it from going forward. Either that, or I’m guessing you must force a particular exception to occur.  Do you know what syntax I might use to cause the pre-event to not trigger the event?

Many thanks,
Steve

Keith,  Thanks for the prompt feedback. As it turns out, some of our developers have already encountered this issue. The workaround for us will more likely be to create a state-based triggered PX configuration.

Steve

Michael, I did figure out that I must specify the revision when attaching to an ITEM.  So if I change this…

ITEM,2187010100U004,,C:/CADTEMP/2194610100000.pdf,FILE,,

to this…

ITEM,2187010100U004,02,C:/CADTEMP/2194610100000.pdf,FILE,,

This is a little strange, because the instructions seem to indicate that the Rev is optional, which would imply that it might attach to the latest rev by default.  At least a fould a method that works though.