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

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

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.

Try this db query to display the current BOM.

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

Hi Kevin,

I have got the BOM items, thanks.

But I want to understand how did you get the idea of using “statustype in (3,4)”, as a SDK developer that I know thesestatuses would refer to “Release” and “Implemented” statuses respectively, but as a beginner in DB programming how could one get the idea of this.

Also, while I try to display the page two and page three attributes of bom items, bom table do not have those fields, how to feed in BOM item as Item and get the data.

Basically, I am trying to display page two fields of child items(BOM items), could you please give a query of one or two fields, that would help me to understand better, many thanks!!

Thanks,
Surya

LOL  Many years of looking at what is displayed in the GUI, and looking at what is in the database, and figuring it out.

 Given that the ID in CHANGE.STATUS can be many different values, the schema designers added an attribute that very simply provided the level of the status – Pending = 0, Submit = 1, Review = 2, Released = 3, Implemented = 4, Canceled = 5, Hold = 6. This value is also associated with the status ID itself in PROPERTYTABLE, but it is far simpler/cheaper to save it in the CHANGE table instead of having to look it up using the status ID value. Smart design!

 To get P2/P3 attributes for the child item number, use BOM.COMPONENT to link to PAGE_TWO.ID and/or PAGE_THREE.ID, and then get the attributes you want. Modifying the query I provided above (for the latest released BOM) to include TEXT01 and DATE03 from page two, it would look like :

select b.item_number, b.find_number, b.quantity, p2.text01, p2.date03
 from bom b, page_two p2
 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) )
   and b.component = p2.id;

List values would be a bit harder, as you would need to get the actual text value from LISTENTRY and also account for where the list ID was null (using an outer join). If you only need 1 list attribute, not so hard. But for 7 of them, not so easy as each attribute must separately join to LISTENTRY. Almost easier to just have the query insert data into a table (putting the list ID values in there as-is), and then update each list attribute with a query against LISTENTRY, and then retrieve the data you need from the table.