Find Unincorporated Released Items
There is a flag in the REV table named INCORPORATED that is set when a revision has been incorporated.
A query to select revisions that are released but not incorporated would be something like :
select i.item_number, r.rev_number, c.change_number
from item i, rev r, change c
where i.id = r.item and (r.incorporated is null or r.incorporated = 0)
and r.change = c.id and c.statustype in (3,4);
INCORPORATED can be null, 0 or 1, with 1 indicating that the revision has been incorporated. STATUSTYPE in the CHANGE table indicates the type of status for the change – 0=Pending, 1=Submitted, 2=Review, 3=Released, 4=Implemented, 5=Cancel, 6=Hold. So the query looks for a revision where INCORPORATED is null or zero, and where the related change has STATUSTYPE equals 3 or 4.