BOM for a past release
Based on the posts by Keving Cummings on this topic and trial-and-error, I have created the below query for fetching the BOM components for a past revision of an assembly item. This returns the correct results in many cases but is not consistent and there are exceptions resulting in additional records. Am I missing something?
SELECT A.ITEM bomitem, a.item_number bomitemno, a.quantity bqty, level FROM AGILE.BOM A START WITH (A.CHANGE_IN = 0 OR A.CHANGE_IN IN (SELECT r.CHANGE FROM agile.rev r, agile.item i WHERE i.id = r.item AND i.item_number = <ItemNo> AND r.RELEASE_DATE <= (SELECT DISTINCT r1.RELEASE_DATE FROM agile.rev r1, agile.change c1, agile.item i1 WHERE r1.change = c1.id AND i1.id = r1.item AND i1.item_number = <ItemNo> AND c1.change_number = <ChangeNo>)) ) AND (A.CHANGE_OUT = 0 OR A.CHANGE_OUT NOT IN (SELECT r.CHANGE FROM agile.rev r, agile.item i WHERE i.id = r.item AND i.item_number = <ItemNo> AND r.RELEASE_DATE <= (SELECT DISTINCT r1.RELEASE_DATE FROM agile.rev r1, agile.change c1, agile.item i1 WHERE r1.change = c1.id AND i1.id = r1.item AND i1.item_number = <ItemNo> AND c1.change_number = <ChangeNo>)) ) AND (A.ITEM = (SELECT DISTINCT i.id FROM agile.item i, agile.rev r, agile.change c WHERE r.change = c.id AND i.id = r.item AND i.item_number = <ItemNo> AND c.change_number = <ChangeNo> ) AND SUBSTR (A.flags, 4, 1) = 1 ) CONNECT BY PRIOR A.COMPONENT = A.ITEM AND SUBSTR (A.flags, 4, 1) = 1 ;
