Which Table contains Affected Items in Oracle database?
Building custom query to find changes and their affected items. Which table in the Oracle database contains the affected items. Thank you.
The REV table holds affected items. REV.ITEM links to ITEM.ID for the item number, and REV.CHANGE links to CHANGE.ID for the change number. Note that REV.RELEASE_TYPE is the life cycle, and links to NODETABLE.ID to get the text value. If you are using Sites, REV.SITE links to SITES.ID, otherwise it is always zero. The latest revision has LATEST_FLAG = 1, and released ones have RELEASED = 1.
Hi Gomez,
Just adding to what Kevin said, you can use the below query to load a Change with affected Items and expand it if you need to add additional details.
/* ***** Change Affected Item Query *********/
SELECT DISTINCT
change.change_number,
item.item_number,
item.description AS “Description”,
rev.old_revnumber AS “Old_Rev”,
rev.rev_number AS “New_Rev”,
oldlifecycle.description AS “Old_Lifecycle”,
newlifecycle.description AS “New_Lifecycle_Phase”,
NEW_TIME (rev.obsolete_date, ‘gmt’, ‘PDT’) AS “Obsolete_Date”,
NEW_TIME (rev.effective_date, ‘gmt’, ‘PDT’) AS “Effective_Date”
FROM agile.rev rev,
agile.item item,
agile.change change,
agile.nodetable oldlifecycle,
agile.nodetable newlifecycle
WHERE rev.item = item.id
AND rev.change = change.id
AND rev.old_release_type = oldlifecycle.id(+)
AND rev.release_type = newlifecycle.id(+)
AND change.change_number = ‘ENTERYOURCHANGENUMBERHERE’
ORDER BY item.item_number
Regards,
Arif