How to detect the lifecycle phase of an item from db?
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’;
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
