Joins to BOM of each Revision
I want to select the BOMs (including the revision and lifecycle of the BOM-Items) for each revision of an item. I believe it has something to do with the three columns “CHANGE_IN”, “CHANGE_OUT” and “PRIOR_BOM” in the BOM-table. Maybe the latest revision is the one with “0” in column “CHANGE_OUT”. But it is still hard to find the join to the other revisions. I saw all the questions regarding similar topics. But I was not able to answer my question. Can someone give me a hint what the correct join is?
I looked a little further. I think the revisions of the items in the BOM can be found based on the release date of the parent item.
Also the last BOM seems to be no problem (Change_Out=0).
What remains is the question, how to select the Items of older BOMs. Unfortunately this is my main topic.
Any help is welcome.
After I tested a long time, I think the following is close to the required result.
Only the highlighted part does not always bring the correct data. Can anyone help me with the correct join to the revision of the BOM items?
FROM AGILE.REV REV_PARENT
JOIN AGILE.ITEM ITEM ON ITEM.ID=REV_PARENT .ITEM
JOIN AGILE.BOM BOM ON BOM.ITEM=REV_PARENT.ITEM
AND ((BOM.CHANGE_OUT=0) OR (BOM.CHANGE_OUT>REV_PARENT.CHANGE))
JOIN AGILE.REV REV_CHILD ON REV_CHILD.ITEM = BOM.COMPONENT
AND REV_CHILD.LAST_UPD <= REV_PARENT.LAST_UPD
JOIN AGILE.ITEM ITEM_CHILD ON ITEM_CHILD.ID = BOM.COMPONENT
ORDER BY REV_PARENT.CHANGE, ITEM.ITEM_NUMBER