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?

Edit:

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.

Add Comment
1 Answer(s)

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?

SELECT
ITEM.ITEM_NUMBER ITEM_PARENT
,REV_PARENT.CHANGE CHANGE_PARENT
,REV_PARENT.REV_NUMBER REV_PARENT
,BOM.ITEM_NUMBER ITEM_CHILD
,ITEM_CHILD.DESCRIPTION DESCRIPTION_CHILD
,BOM.QUANTITY QUANTITY_CHILD
,MAX(REV_CHILD.REV_NUMBER) REV_CHILD

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_IN<=REV_PARENT.CHANGE
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

GROUP BY
ITEM.ITEM_NUMBER
,REV_PARENT.CHANGE
,REV_PARENT.REV_NUMBER
,BOM.ITEM_NUMBER
,ITEM_CHILD.DESCRIPTION
,BOM.QUANTITY

ORDER BY REV_PARENT.CHANGE, ITEM.ITEM_NUMBER

 

Agile User Answered 21 hours ago.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.