PauleHerrmann's Profile
Agile User
7
Points

Questions
1

Answers
1

  • RE: Joins to BOM of each Revision
    Agile User Asked on August 21, 2019 in Agile PLM (v9).

    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

     

    • 1126 views
    • 2 answers
    • 0 votes