Priya,

You might also try this… It seems to run fairly quickly.

SELECT * FROM
(SELECT I.ITEM_NUMBER, COUNT(B.COMPONENT) CNT FROM AGILE.ITEM I
LEFT JOIN AGILE.BOM B ON I.ID = B.COMPONENT
WHERE I.CLASS = 10000
GROUP BY I.ITEM_NUMBER)
WHERE CNT = 0;

It is a bit over simplified though. This would return only items that have NEVER been on a BOM. As you are probably already aware, it is possibly for an item to be a component in the BOM table, but not be on the latest BOM because the item had been redline removed on a change order.  This query does account for that use case, but with a little more work, it could.

Steve

Wow, that is a really good question. Clearly there is not any one way to answer this or even a right or wrong answer.  I think I read once that any documentation is better than no documentation. For us, I don’t think we’ve yet found a consistent and formal way of doing this, but I can tell you the direction we are moving on this.

First, let’s clarify the type of environment changes that you may be talking about. I’m guessing that you’re referring to configuration changes. This could be as simple as adding a new Page Three attribute to a subclass to creating a brand new subclass.  The company I work for has many process extensions (PX) within Agile.  In many cases, the way those PXs behave are based on Agile Document items that we use as configuration objects.  Without them, the PX does not function, so it is indeed important that we document how to recreate these objects and what purpose they serve.  Personally, one of my favorite tools to document most anything was a free app called ClarifyIt. I still have it and use it, but unfortunately it is longer available. It is just a convenient tool to do screen captures and add headings and text to those screen captures. But honestly, you can do the same thing with Microsoft Word.

We now use Azure DevOps for source control and to keep up with Features and tasks assigned to Analysts and developers. We have started to create UI configuration objects within DevOps to also document these Agile Configuration objects and changes to them. Within those, it’s descriptive text and images.  That’s really all it boils down to… any documentation tool where you can describe what you’ve done and add screen shots to provide clarification. How formal the documentation is a decision only you and your company decide on.

It is possible that I’ve misunderstood what you’re asking for. It may be that you’re wanting some tool that extracts data from the system both before and after config changes.  I can’t make any recommendations on that, because I’m not aware of any such tool. That said, this has given me an idea. I have a database view that I created that shows subclass properties.  I could see running this each I make changes and then saving the output of this as part part of my documentation.  I may consider sharing this view at some time in the future.

Steve LaLonde

Is the user logging in manually or through a Single Sign On  service?  Also, check to see if the user has made their own shortcut to launch the app.  Depending on how they went about doing that, it can save previous session data in the URL.  This can cause all kinds of weird behavior.

Steve

Nagma,

I hope I have an acceptable solution for you.  You didn’t answer my last question so I made some assumptions.
The ‘DIFF’ column gives you the accumulated  time from the first step in the workflow.  I believe you can do more with the formatting using the EXTRACT function, but for now, here’s an example of DIFF format: “+00 02:27:52.000000”.  I assume if it went into days, then those first 2 zeros would reflect that.  You notice that I removed your questionable sub-query altogether. Instead, I did a LEFT JOIN of WORKFLOW_PROCESS.  There is some explanation in the comments.

Steve LaLonde

Try running the following query:

select c.CHANGE_NUMBER
,(select le.ENTRYVALUE from listentry le where le.ENTRYID = c.CATEGORY)as CATEGORY
,(SELECT last_name || ‘,’ || first_name || ‘(‘ || loginid || ‘)’
FROM agileuser
WHERE id = c.ORIGINATOR) initiator
,(SELECT last_name || ‘,’ || first_name || ‘(‘ || loginid || ‘)’
FROM agileuser
WHERE id = c.owner) coordinator
, w1.state
,(SELECT description
FROM nodetable
WHERE id = w1.state) workflow_status
, w1.ORDER_BY
, NVL(TO_CHAR(w1.LOCAL_DATE,’DD-MON-YY HH24:MI:SS’),0)local_date
, NVL(TO_CHAR(w2.LOCAL_DATE,’DD-MON-YY HH24:MI:SS’),0)INITIAL_LOCAL_DATE
,(CAST(w1.LOCAL_DATE as timestamp) – CAST(w2.LOCAL_DATE as timestamp)) diff
from WORKFLOW_PROCESS w1
INNER JOIN CHANGE C ON W1.CHANGE_ID = C.ID
–BY DOING THE FOLLOWING JOIN WHERE ORDER_BY = 0, WE GET THE SAME DATE/TIME REPEATEDLY FOR THE FIRST STEP IN THE WORKFLOW (INITIAL_LOCAL_DATE).
–BY DOING THIS, WE CAN ACCUMMULATE THE TIME RATHER THAN TRY TO GET THE TIME FROM ONE STEP TO THE NEXT.
LEFT JOIN WORKFLOW_PROCESS W2 ON W2.CHANGE_ID = C.ID AND W2.ORDER_BY = 0
WHERE c.CHANGE_NUMBER=’NPI001084′
–THIS NEXT LINE MAY NOT BE REQUIRED IN YOUR CASE. IN MY TEST CASE, WHEN THE CHANGE WENT TO A STATUS OF Implemented,
–THE LOCAL DATE WAS NULL. I AM NOT SURE IF THAT HAPPENS IN ALL CASES
AND W1.LOCAL_DATE IS NOT NULL
order by w1.ORDER_BY,local_date ASC;

Nagma,

Are you trying to get durations of each step, or just beginning to end?

Steve LaLonde

Just to make sure I understand, are you simply looking for the list values for a particular attribute on a particular object?  Based on your response to Steve Jones above, I can’t tell if you’re saying you’re looking at a TABLE named “PROJECT_GENERAL_INFO_MV”, or if that is the name of your project object.  Regardless, all objects have ties to Page Two and Page Three data via the object ID. So, as long as you can find the correct table your object is in, then you can get the associated P2 and P3 data for it.  I believe you’ll find Project objects in the ACTIVITY table.

As for the list and multitext values, you must know the name of the column you’re trying to retrieve.  Here’s an example:

Let’s say you have a project object named “My Project 0001” and the list and multitext attributes you’re trying to get are stored in LIST01 (PAGE_TWO) and MULTITEXT50 (PAGE_THREE).  I recommend you just do sub selects for these attributes.

SELECT A.ID PROJID, A.NAME PROJNAME
,(SELECT ENTRYVALUE FROM AGILE.LISTENTRY WHERE ENTRYID = P2.LIST01) MY_LIST_VAL
–For Multitext you must know the attributes base ID
,(SELECT TEXT FROM AGILE.AGILE_FLEX WHERE ID = A.ID AND ATTID = 1567) MY_MTEXT_VAL
FROM AGILE.ACTIVITY A
INNER JOIN AGILE.PAGE_TWO P2 ON A.ID = P2.ID
WHERE A.NAME = ‘My Project 0001’;

I hope this helps,

Steve LaLonde

Any chance this occurred in a test environment after a clone from production?  In that instance, it is possible for XREF tables to get out of sync.  By the way, our company tolerated the classic PIP solution for several but recently replaced it with DocState by ZeroWaitState.  Highly recommend it.

Dinesh,

I took the code I use for this in SOAP UI, but replaced with your parameters. NOTE: I use Agile 9.3.2, but I’m hoping this works for you all the same.  Please try the following:

<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>Part</classIdentifier><objectNumber>P00001</objectNumber><data><DOCSIZE xsi:type=”AgileListEntryType” attributeId=”1068″ xmlns:xsi=”http://xmlns.oracle.com/AgileObjects/Core/Common/V1″><selection><value>Buy</value></selection></DOCSIZE></data></requests>
</request>
</updateObject>
</soapenv:Body>
</soapenv:Envelope>

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