Latest BOM from Agile DB

Do you want ref-des as well??   The easy answer is to look for any record where CHANGE_OUT is zero, for a specific assembly :
select a.item_number,b.item_number, b.find_number,b.quantity,r.label from item a, bom b, refdesig r
 where b.item=(select id from item where item_number='<assembly number>’) and b.id=r.bom(+) and b.change_out=0;

 However, the above *will* include records for pending changes as well. So if you have a lot of pending changes, the above will not work (at least not well). It will also list multiple lines for BOM records that have multiple values in the ref-des, because those are stored separately instead of as a single string in the DB.
 To really do it correctly, you need to make sure that CHANGE_OUT is zero *and* that BOM.CHANGE_IN references the ID of a released change linked to the assembly. And in order to not have 1 ref-des per line instead of all together (where there are multiple values), you would have to do this as a PL*SQL procedure so that you could collect them together as a single string value. It can get quite interesting when you have 20-30 revisions for an assembly or several hundred ref-des values for a component.

Thanks, how can we filter out the redlined BOMs with pending changes in the above query? Currently it gives us all the BOMs that has been added or removed and does not filter out the redlined ones.

Try with the below query, it should fetch the latest released structure of the BOM

select a.item_number,b.item_number, b.find_number,b.quantity from item a, bom b
where b.item=a.id and a.item_number='<BOM  Number>’ and b.change_out=0 and b.flags not like ‘0000000%’;