Re: getting list of complete BOM

On Apr 4, 3:47 am, "Ronald." <rmolev..._at_gmail.com> wrote:
> sorry guys for the 'misinformation' :-)
>
> It's indeed Oracle Applications, and to be specific the bill of
> material module.
> RDBMS is 8.1.7.4.0 and applications is 11.5.3
>
> simple example of current situation:
>
> product A has 4 components: B1, B2, B3, and B4
>
> product B1 has component C1
> product B2 and B4 has no component (it's a buy product)
> product B3 has component C2, C3 and C4
>
> product C2 has no component
> product C3 has component D1 and D2
> product C4 has component E1 and E2
> ......
>
> As I said before, I know the products are stored (the id's) in
> bom_bill_of_materials and the components in bom_inventory_components
> connected with the operation sequence number.
>
> I can make a select to go one level deep but since the structure can
> become quite big and some levels of components are several deep (7 or
> 8 are present) I don't manage to get this right.
>
> Hopefully is this information clear enough, if not I'm happy to
> clarify it further.
>
> best regards,
>
> Ronald.

Bills of material give me a headache. On of the tables in the BOM system should be a Part Master which has one row for every distinct part which can be a Model, Group, Assembly, or Part. There should be an indicator on the row for which type or row it is.

Your other main table is the BOM structure for a product. On these rows I would expect to find a parent_component column. One row of component X will appear for each higher level component that uses a component X in its construction (with the parent_component value set to its specific parent).

Not knowing Oracle Applications BOM structure I do not know if this is the basic model Oracle used or not or if the two tables in your post are these two tables. If not, then referencing information in the third table may be what you need to do to properly explode your structure. Being 8.1.7 I do no think that you can join in the same statement that uses the connect by clause but will have to use code that performs actions in a loop.

I am pretty sure that is how our MRP application works though it has been years since I have looked at any of the BOM code with a developer.

Look in the Application source to see what Oracle does. You should be able to basically copy the SQL. There are two main operations in BOM: explode the BOM to show all parts used in a Model and to show all uses of a component. Learn how to do this with the BOM structure you have and any question's answer just becomes doing part of one of the other operation. This includes maintenance operations like superceeding one part with another.

HTH -- Mark D Powell -- Received on Fri Apr 06 2007 - 08:35:11 CDT