Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: getting list of complete BOM

Re: getting list of complete BOM

From: Steven Kondolf <skondolf_at_nospam.rochester.rr.com>
Date: Sat, 07 Apr 2007 12:45:01 -0400
Message-ID: <9nhf13tq7trvg33tresnshfbv3d23v3sh6@4ax.com>


On 6 Apr 2007 06:35:11 -0700, "Mark D Powell" <Mark.Powell_at_eds.com> wrote:

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

From what I remember about Oracle Apps and boms, Oracle uses temp tables to handle the multiple levels - pull the components for the top level sku that has a bom and then loop thru and pull each sub-component's bill if one exists. Store in a temp table and display.

Go into bom, perform a query on a sku and then do help - about this record to see where the data resides. Received on Sat Apr 07 2007 - 11:45:01 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US