Skip to main content
S
srohde1-Visitor
1-Visitor
July 23, 2015
Question

SQL Query to find out BOM

  • July 23, 2015
  • 2 replies
  • 9020 views

Dear community,

I need to find out what database tables store the information about the bill of material of the products. Need to find out the top level nodes and all linked epm documents or parts.

What we try to do is get the files of all products so we can Export all products from windchill.

Any help is appreciated.

Thanks.

2 replies

B
bsindelar1-Visitor
1-Visitor
July 23, 2015

Stefan,

WTPart-to-WTPart Parent-Child links that build a WTPart BOM are stored in the WTPARTUSAGELINK table.

WTPart-to-EPMDocument owner links are stored in the EPMBUILDRULE table.

There are more tables needed to query if you're looking to pull EVERYTHING related to a specific WTPart or EPMDocument.  Let me know if there's any way I can help you with an extraction.

S
srohde1-VisitorAuthor
1-Visitor
September 9, 2015

In which columns are those 2 Parts stored? I can only see the IDA3A5 column which holds 1 Part Info. where is the Parent and the Child Part stored?

S
syadala12-Amethyst
12-Amethyst
September 9, 2015

Hi Stefan,

IDA3A5 id the Object ID of parent IDA3B5 is object ID of child (PartMaster). If you want number you have lo include Part master in you query. Let me know if you need any info.

regards

Sudhakar

A
adev-21-Visitor
1-Visitor
July 27, 2016

Hi Stefan,

Try this query to get the BOM structure

SELECT    M1.WTPARTNUMBER AS COMPONENT, M2.WTPARTNUMBER AS ASSEMBLY

FROM      WTPART, WTPARTMASTER M2, WTPARTUSAGELINK, WTPARTMASTER M1

WHERE     WTPART.IDA3MASTERREFERENCE = M2.IDA2A2

                   AND WTPART.IDA2A2 = WTPARTUSAGELINK.IDA3A5

                   AND WTPARTUSAGELINK.IDA3B5 = M1.IDA2A2

                   and M2.WTPARTNUMBER = '<Part_number>'

GROUP BY  M2.WTPARTNUMBER, M1.WTPARTNUMBER

order by  M2.WTPARTNUMBER, M1.WTPARTNUMBER;

    T
    TV_97805011-Visitor
    1-Visitor
    February 17, 2021

    How do we find the BOM structure for a specific Revision (in SQL), not the latest structure but at the time when a parent revision is released. Thanks

    W
    Wncplmmis23-Newcomer
    3-Newcomer
    November 3, 2021

    try this by modify WTPART.versionida2versioninfo  = what version you want to query

     

    -----------------------------------------------------

    SELECT *
    FROM
    (select
    (SELECT wtpartnumber
    FROM wtpartmaster
    WHERE ida2a2=(SELECT ida3masterreference FROM wtpart WHERE ida2a2=wtpartusagelink.ida3a5)
    ) AS parentpart ,
    (SELECT wtpartnumber FROM wtpartmaster WHERE ida2a2=wtpartusagelink.ida3b5
    ) AS CHILDPART ,
    (SELECT listagg(wtpartnumber,',') within GROUP(
    ORDER BY wtpartnumber)
    FROM WTPARTSUBSTITUTELINK a ,
    wtpartmaster b
    WHERE a.ida3b5 = b.ida2a2
    AND a.ida3a5 = wtpartusagelink.ida2a2
    ) AS Subpart ,
    AMOUNTA7 AS QTY,
    (select listagg(name,',') within GROUP(
    ORDER BY name) from PartUsesOccurrence where IDA3LINKREFERENCE = wtpartusagelink.ida2a2 ) location
    FROM wtpartusagelink
    WHERE markfordeletea2 =0
    AND wtpartusagelink.ida3a5 IN
    (SELECT WTPART_ID
    FROM
    (SELECT WTPARTMASTER.Ida2a2 AS WTPARTMASTER_ID,
    WTPART.Ida2a2 AS WTPART_ID,
    WTPARTMASTER.WTPARTNUMBER,
    WTPART.statecheckoutinfo,
    WTPART.versionida2versioninfo AS version,
    WTPART.iterationida2iterationinfo AS iteration,
    statestate,
    WTPART.versionsortida2versioninfo AS versionorder,
    wtpart.CREATESTAMPA2 AS CreationDate,
    wtpart.UPDATESTAMPA2 AS LastModifyDate,
    WTPARTMASTER.name AS PART_DESC,
    wtpart.IDA3D2ITERATIONINFO,
    RANK () OVER (PARTITION BY WTPARTNUMBER ORDER BY WTPART.versionsortida2versioninfo DESC) AS RANK,
    wtpart.parttype
    FROM WTPART,
    WTPARTMASTER
    WHERE WTPARTMASTER.IDA2A2 = WTPART.IDA3MASTERREFERENCE
    AND WTPART.latestiterationinfo = 1
    AND WTPART.statecheckoutinfo <> 'wrk'
    ORDER BY WTPARTNUMBER,
    WTPART.versionsortida2versioninfo
    ) ALLPART
    WHERE RANK = 1
    )
    START WITH (SELECT WTPARTNUMBER
    FROM WTPARTMASTER
    WHERE ida2a2=(SELECT ida3masterreference FROM wtpart WHERE ida2a2=ida3a5)) = :your Part_number
    CONNECT BY (SELECT WTPARTNUMBER
    FROM WTPARTMASTER
    WHERE ida2a2=(SELECT ida3masterreference FROM wtpart WHERE ida2a2=ida3a5)) =PRIOR (SELECT wtpartnumber FROM wtpartmaster WHERE ida2a2=ida3b5)
    )

    Terms & ConditionsCookie settingsAccessibility statement