Items used on all revisions of an assembly
Hello –
I need to create a list of all items found on all revisions of an assembly. Is there a report or advanced search in Agile 9.3 that can do this?
I realize that I can run a BOM explosion report on each assembly selecting one revision at a time, but I need to do this for 110 different assemblies each assembly having up to 20 revisions. That’s a lot of clicking!
Thanks!
Doug
So far as I know, there is no report or search that can do this inside of Agile.
From the database, however, it can certainly be done. The script below should work (and it should be better indented, but the copy/paste didn’t bring tabs over).
set serveroutput on
declare
assmID number;
assmNM varchar2(100);
compNM varchar2(100);
frelr number;
cursor csrLBA is select distinct item from bom;
cursor csrFRR is select change from rev where item = assmID and change > 0 order by release_date asc;
cursor csrCAR is select distinct item_number from bom where item = assmID
and change_out=0 and (change_in = 0 or change_in = frelr);
begin
— get distinct list of all assemblies
open csrLBA;
loop
fetch csrLBA into assmID;
exit when csrLBA%NOTFOUND;
— get the item number for each assembly
select item_number into assmNM from item where id = assmID;
— get the ID of the first released change for the assembly
open csrFRR;
fetch csrFRR into frelr;
close csrFRR;
— find any items which are current (CO=0) and were added by either Intro or first rev (CI=0 or CI=frelr)
open csrCAR;
loop
fetch csrCAR into compNM;
exit when csrCAR%NOTFOUND;
dbms_output.put_line(assmNM || ‘ – ‘ || compNM);
end loop;
close csrCAR;
end loop;
close csrLBA;
end;
/
show err
