Hi PDM Gurus,
I need to make an enhanced BOM Query in Windchill 10.1 M040. If the data comes out in a reasonable way a query using the QueryBuilder tool (under Org or Site/Utilities/Report Management) would be enough; I'd rather not try to dive into Cognos if I don't have to, but I don't quite know what links to put where, what objects to put in the From tab, what Joins etc...need some help on this.
REQUIRED INPUT: A parent part number and revision selector (with the default option selecting Latest)
NICE TO HAVE INPUT: The ability to put in multiple part numbers and it would do all of them at once
OPTIONAL INPUT: Toggle selectors on several of the part Attributes to turn on or off if they show in the data, like Name, Description, Context, some custom Global ones we've added, etc...
A full multi-level BOM printout of the parent(s) selected showing their layers. The requested parent is level 0, a first-level child would be level 1, a child of that would be level 2 and it would break out that object's BOM in full before moving on to the next level 1 child, etc...and the data columns displayed for each entry would be the part number, quantity and the list of toggle-selected attributes.
Does anyone have any idea on how I can do this? I've tried making one with Joins between Part, Parts List, etc...it doesn't come out properly.
To get the multi level bom report with SQL query is very difficult(impossible ). However without using congos you can create the reports. I have created some BOM report without using windchill API and JSP, to list MBOM, some attribute and current open process for all parts
attached sample java class contain useful API's
Let me know if you need more information
I had a very similar request from a customer of mine, though it was for the opposite direction in the BOM tree - a multi-level "where-used" report that gets run from the "Quick Links" drop-down in WC.. The report query was done entirely in Oracle SQL, using a specific means my company developed to actually run the report and print to a custom-formatted PDF, which opens in the browser and could then be saved to disk.
I am sure the query can essentially be "reversed" to put out a BOM instead of a where-used, and it also does show indented BOM level as well. Let me know if you're interested.
Okay, perhaps you can help me with this part. Despite Cognos not being very good at accurately copying reports I was able to make a full functioning copy of the Multi-level BOM report normally useable from the Structure tab of a WT part BOM. However like that original one it also insists that the report can only be run from within that tab of an assembly, and looking into the imported files/resource bundles that the report seems to call at the beginning I can't decipher which of those has the popup forcing the issue of running from a BOM; I want to get rid of that block and instead add a Prompt Page to the report allowing a user to add a parent part number at will. Most of the original BOM report data is fairly good and I want to re-use that if possible, probably adding half a dozen additional fields in the results table. Any ideas on how to do this?
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 = 'A10000007'
start with M2.WTPARTNUMBER = 'A10000007'
connect by M2.WTPARTNUMBER=prior M1.WTPARTNUMBER
GROUP BY M2.WTPARTNUMBER, M1.WTPARTNUMBER
order by M2.WTPARTNUMBER, M1.WTPARTNUMBER;