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.

Add Comment
3 Answer(s)

Hello

 REV table has it. Join on rev.change to change.id

Agile Angel Answered on March 22, 2019.
Add Comment

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.

Agile Angel Answered on March 23, 2019.
Add Comment

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

Agile Angel Answered on March 25, 2019.
Add Comment

Your Answer

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