Re: Need a custom BOM Query

doehr · ‎May 30, 2016

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...

OUTPUT:

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.

Please advise,

Daryl

satre-2 · ‎May 31, 2016

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

Thanks

Shreyas

+91-9405954712

doehr · ‎May 31, 2016

Looks promising, I'll have to try it out.

bsindelar · ‎May 31, 2016

Daryl,

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.

The only thing in the "required" items my report does NOT have is a revision option, but that would be easy to do.
In your "nice to have" section, this report could do the reports via multiple part input or use of wildcard searching, but this was not a requirement for what I built so I am not sure if the format would look "nice", though I think it would be ok.
For your "optional" section, the ask from my customer was to simply always include all IBAs on the objects, valued or not. We also had fixed attribute display for the obvious things (name, number, version, etc), so every report had the same number of columns for simplicity sake.

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.

doehr · ‎May 31, 2016

Just in case it ends up being an easier option, does anyone know a Query that could be built in Cognos to do this?

satre-2 · ‎May 31, 2016

There is already existing MBOM Report in cognos if you want to add IBA you can refer below article

https://support.ptc.com/appserver/cs/view/solution.jsp?n=CS20865‌

Thanks

Shreyas

satre-2 · ‎May 31, 2016

There is already existing MBOM Report in cognos if you want to add IBA you can refer below article

https://support.ptc.com/appserver/cs/view/solution.jsp?n=CS20865‌

Thanks

Shreyas

doehr · ‎Jun 01, 2016

Works to a point, but still having some issues with it. I'm now trying to modify that with some of the extra stuff I need.

doehr · ‎Jun 15, 2016

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?

Daryl

kjoe · ‎Aug 24, 2016

--BOM Query

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;