How to detect the lifecycle phase of an item from db?

I want to select the items based on their lifecycle phase with a sql query. How can I do that?

Agile Expert Asked on September 13, 2018 in Agile PLM (v9),   Product Collaboration.
Add Comment
2 Answer(s)
Best answer

Look at RELEASE_TYPE in the REV table where ITEM is the ID of the item that you want the lifecycle for. If you want this for the revision that a user will see when pulling the item up in Agile, specify where LATEST_FLAG=1, otherwise you will need to specify which revision (and change, really). And of course, the actual text value is in NODETABLE, what is in REV is just the ID of the lifecycle value.

 The query for latest revision would be something like :
select r.release_type, n.description from rev r, nodetable n
 where r.item = (select id from item where item_number = ‘XYZ’) and r.release_type = n.id and latest_flag=1;

 To get the lifecycle for a specific revision, the query would be like :
select r.release_type, n.description from rev r, nodetable n
 where r.item = (select id from item where item_number = ‘XYZ’) and r.release_type = n.id
    and r.change = (select id from change where change_number = ‘ABC’) and rev_number = ‘A’; 

Agile Angel Answered on September 13, 2018.

Thanks a lot Kevin!

on September 13, 2018.
Add Comment

Hi Swagoto,

Just to add to Kevin, Preliminary lifecycle is generally stored as Null value since it doesn’t map to any revision. So you can use NVL(n.description,’Preliminary’) AS LIFECYCLE for more accurate results.  Sample query that i used to give the latest revision of an item.

SELECT   ITEM_NUMBER,  NVL (NODE.DESCRIPTION, ‘Preliminary’) AS LIFECYCLE
 FROM   AGILE.ITEM I, AGILE.NODETABLE NODE, AGILE.REV R
 WHERE       I.ID = R.ITEM(+)
         AND I.DEFAULT_CHANGE = R.CHANGE(+)
         AND R.RELEASE_TYPE = NODE.ID
         AND I.ITEM_NUMBER=’XXXXXXX

Regards,
Arif

Agile Angel Answered on September 14, 2018.

Thanks a lot Arif for the additional input!

on September 14, 2018.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.