The BOM table includes all information related to the BOM, including redlining. The change_in and change_out columns link to the change IDs that caused the BOM record to be added, removed, or another field to be redlined (change_in = change_out.)

Hi Kalyani,

You can check -> https://myagileplm.com/question/how-to-fectch-redline-bom-table-of-a-affected-item-in-a-change-using-db-query/

Somehow i could see my old answer not coming up there so i added it back. Please check and see if it helps.

Regards,

Arif

I once tried to just show the BOM for the latest revision of an assembly in a single query and I got it to work. But it was so messy and kludgy, I threw it away – it was not worth keeping. It is the list of change IDs that is required to be used  for each revision that is a pain. It is far easier to create a table for your data, write a PL/SQL block to process things, and be able to see what is going on for each attribute, and each step of the processing.

To get complete data for each revision of an assembly, you need to get the list of the database IDs for ALL changes that affect the assembly. Then loop through them and set up the criteria for your main query against the BOM table as follows :

1. For the introductory revision (if it has BOM data), specify “where change_in = 0 and change_out is not null”
2. For each released revision (ordered by release date), specify “where change_in in (0,<all change IDs processed so far including the current revision>) and (change_out=0 OR change_out NOT IN (<all change IDs processed so far including current revision>) “;

So you will execute your main query getting the various fields you wish (find_number, quantity, etc.) from the BOM table N+1 times, where N is the number of revisions the assembly has released. Processing un-released revisions is not advised, as the data might change. Otherwise, just process them like a released revision but make sure to show the revision as enclosed in parentheses, like “(F)”, so that users know it is un-released.

If you need to include change-controlled attributes (other than the assembly revision), then you need to include subqueries against REDLINE_ATTRIBUTE and/or REDLINE_MSATTRIBUTE for each change-controlled attribute. Something on the order of “(select new_text from REDLINE_ATTRIBUTE where object_id= BOM.item and change_id = <ID of the change number linked to the current assembly revision> and attid = <ID for the attribute needed>)”. The sub-query shown would be for a text or multi-list attribute (NEW_DATE would be for a date value, NEW_NUMBER1 would for a numeric or list attribute). Note that if the value had not changed for the given revision, there will be no record in the table. Hopefully you do not use change-controlled attributes.

I would very Very VERY strongly advise you to not try to do this in a single query. Is it doable? For a very simple BOM configuration and for a single revision of the assembly, maybe. For all revisions and if there are change-controlled attributes involved, no. There are too many things going on. I can guarantee that the easiest way to do it is to build a table with the all the attributes you want in it, and fill it in for each assembly revision (using the where clauses I provided above), and THEN handling list attributes as well as change-controlled attributes.