DB Query to show level1 BOM of a Item

Hi ,

Could you please provide the DB query to display Level 1 BOM of a item,please help.

Thanks,
Surya

No Files Were Attached
Surya Agile User Asked on January 8, 2019 in Product Collaboration.
Add Comment
5 Answer(s)

select b.item_number, b.find_number, b.quantity
 from bom b
 where b.change_out = 0 and b.item = (select id from item where item_number='<assembly number>’);

 2 things to note about the above query :
1) It will select the latest released BOM, unless you have a pending change, in which case it gets latest released BOM and also the pending updates.
2) You can add other BOM attributes to the query easily, but adding the component description and other attributes would get more complicated.

 So the above query is VERY simple, but should get you the basic BOM data for an assembly.
 You could modify it to get the BOM from a specific revision of the assembly that is not latest, but it gets rather complicated at that point (you would have to understand how CHANGE_IN/CHANGE_OUT works).
 Adding component attributes isn’t very hard (add “, page_two p” to FROM clause,  “and b.component=p.id” to the WHERE clause and then add the attributes you want to the SELECT clause as “p.date01“, for exeample). So long as they are date/numeric/text values, you are fine, but when you get into list/multilist/multitext attributes, it gets even more complicated because you then have to link to LISTENTRY to get the text values (to the point you would probably do it as a PL/SQL procedure).

Kevin Cummings Agile Angel Answered on January 8, 2019.
Add Comment

Hi Kevin,

When I ran this query, it is giving me resulted BOM ,which Item has on Pending change.

If I want to get the latest BOM for item, with out connected to change,please help me to understand.

Thanks,
Surya

Surya Agile User Answered on January 9, 2019.
Add Comment

Okay, to eliminate any pending BOM records from the latest BOM, the query would be as follows :
select b.item_number, b.find_number, b.quantity
 from bom b
 where b.change_out = 0 and b.item = (select id from item where item_number='<assembly number>’)
   and exists (select null from change where id = b.change_in and statustype in (3,4) );

 The “STATUSTYPE in (3,4)” says to not include anything where the change is not yet released (3) or implemented (4). The “CHANGE_OUT=0” part of the query is simply telling the database that you want any record that has not been modified by a revision change (when this happens, CHANGE_OUT is set to be the rev change ID). The issue with pending changes is that CHANGE_OUT is not set to that ID until the change is released. So to eliminate pending BOM records you need to verify that what is in CHANGE_IN (the rev change that added the component) is released.

 CHANGE_IN and CHANGE_OUT are used to eliminate un-needed BOM records. If you do not modify a component going from rev A to rev B, why do you need a record for it?. CHANGE_IN is the ID of the revision change that added the BOM component record and CHANGE_OUT is the ID of the revision change that modified or removed the component part (a special case is when CHANGE_IN = CHANGE_OUT, which is used to indicate the component has been removed from the BOM). As such, every ID value in CHANGE_IN or CHANGE_OUT in the BOM for an assembly *MUST* be in the CHANGE attribute of the REV table for that assembly. Yes, it is complicated, but the storage savings were once computed at around 70% when an assembly had on average 10 revisions and 25 components, and only 10% of the components in the assembly BOM were modified in some way when a new revision was released.

 To get the BOM records for a specific revision (that is not latest), you would get the list of change IDs for each assembly revision up to and including the one being extracted (in release date order) and specify that CHANGE_IN is “0” or in that set of values. Then you specify that CHANGE_OUT is equal to zero or NOT in the set of revision change IDs that you just put together. That will get you all BOM records for a specific revision of an assembly. Note that for CHANGE_IN = 0, it is just an indicator that the BOM records were added to the Introductory revision of the assembly. Where CHANGE_OUT = 0 it simply means that the BOM record is active or pending.
 A query to get the revision BOM for an assembly would look like :
select b.item_number, b.find_number, b.quantity
 from bom b
 where b.item = (select id from item where item_number='<assembly number>’)
   and CHANGE_IN in (0,<set of rev change IDs>) and (CHANGE_OUT = 0 or CHANGE_OUT not in (<set of rev change IDs);

 The “<set of rev change IDs>” would be comma-delimited, looking something like “7634589,8834590,10023573”. You do not need to worry about released vs. pending when getting the revision BOM of an assembly, as you are directly specifying which change IDs to look for in the table.

Kevin Cummings Agile Angel Answered on January 9, 2019.
Add Comment

Try this db query to display the current BOM.

mrmathison Agile Talent Answered on January 11, 2019.
Add Comment

I am having trouble pasting code or attaching text file with code.  You can email me at mathison at hotmail dot com.

mrmathison Agile Talent Answered on January 11, 2019.
Add Comment

Your Answer

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