14 Replies Latest reply: Apr 4, 2013 12:38 PM by 1001125 RSS

    BOM Query

    User910243567
      Need help in writing a query to fetch all the assembled items. I have requirement as part of which I will pass Item1, Item2, Item3.... need to get all the assembled items which are using all this items (not just one all the items).

      Below query is fetching assembly items which are using any one items.

      Select * from bom_bill_of_materials bom, bom_inventory_components bic, mtl_system_items_b msib
      where bic.component_item_id = msib2.inventory_item_id
      AND bom.bill_sequence_id = bic.bill_sequence_id
      AND msib.segment1 IN ('Item1','Item2','Item3')

      How can modify it to fetch only assembly items which are using all the 3 items.

      As the above query 'IN' is acts like OR (condition1 or condition2....)

      Thanks for your response
        • 1. Re: BOM Query
          Frank Kulash
          Hi,
          User910243567 wrote:
          Need help in writing a query to fetch all the assembled items. I have requirement as part of which I will pass Item1, Item2, Item3.... need to get all the assembled items which are using all this items (not just one all the items).
          That depends on your data.

          Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables.
          Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
          Always say which version of Oracle you're using.
          Below query is fetching assembly items which are using any one items.
          Is it really doing that?
          Usually, "bill of materials" means a recursive, multi-level parent-child relationship. For example, a wagon may consist of a body, a handle, and 2 axle assembiles. An axle assembly may consist of an axle and 2 wheel assemblies, and a wheel assembly may consist of a wheel, a bracket, and 4 XYZ screws. If you're interested in which products require XYZ screws, would that mean just a wheel assembly, or would it include axle assembly and wagon as well as wheel assembly? The query you posted seems to only look at parents and their children; there's nothing recursive or multi-level about it.
          Select * from bom_bill_of_materials bom, bom_inventory_components bic, mtl_system_items_b msib
          where bic.component_item_id = msib2.inventory_item_id
          AND bom.bill_sequence_id = bic.bill_sequence_id
          AND msib.segment1 IN ('Item1','Item2','Item3')

          How can modify it to fetch only assembly items which are using all the 3 items.

          As the above query 'IN' is acts like OR (condition1 or condition2....)

          Thanks for your response
          If the query above really is finding all the objects that use any 1 of the 3 components, then the following will return objects that are using all 3 of them:
          WITH     got_cnt
          (
               SELECT  ...     -- list distinct columns needed for output
               ,     COUNT (DISTINCT msib.segment1) OVER (PARTITION BY  x)     AS cnt
               FROM      bom_bill_of_materials          bom
               ,      bom_inventory_components      bic
               ,      mtl_system_items_b          msib
               WHERE     bic.component_item_id     = msib2.inventory_item_id
               AND     bom.bill_sequence_id     = bic.bill_sequence_id 
               AND     msib.segment1           IN ('Item1', 'Item2', 'Item3')
          )
          SELECT     *     -- or list all columns except cnt
          FROM     got_cnt
          WHERE     cnt     = 3
          ;
          where x uniquely identifies the object that must include all 3. (X can be a single column, or some other expression, or a list of several columns and/or other expressions.)

          If one of the target items is "XYZ screw", then neither query above would include wagon or axle assembly. To get those results, you could do a CONNECT BY query, using CONNECT_BY_ROOT. In this case, it's especially important to say which version of Oracle you have, because every version since Oracle 7 has had singnificant improvements in this area.
          • 2. Re: BOM Query
            User910243567
            Thank you very much for your detailed reply and version of Oracle we are on; RDBMS : 10.2.0.4.0; Oracle Applications : 11.5.10.2.

            My requirement is exactly the example you quoted below of Wagon. Given XYZ screws and i need to list down the Multi Level parent items which are using it.

            In this case for given XYZ screw as input, I need to return output listed as Wheel assembly, axle assembly and wagon.

            Input: XYZ Screw

            Output: Wheel assembly, axle assembly, Wagon

            I was struck at this point on how i can make it as recursive to handle multi levels.

            Thanks!

            Edited by: User910243567 on Jan 4, 2012 12:18 AM
            • 3. Re: BOM Query
              908002
              Select * from bom_bill_of_materials bom, bom_inventory_components bic, mtl_system_items_b msib
              where bic.component_item_id = msib2.inventory_item_id
              AND bom.bill_sequence_id = bic.bill_sequence_id
              AND msib.segment1 = All ('Item1','Item2','Item3')
              • 4. Re: BOM Query
                User910243567
                Thanks for your response, your query will fetch assembly items which contains any of the one of the items (Item1 or Item2 or Item3).
                • 5. Re: BOM Query
                  User910243567
                  Hi Frank,

                  The Requirement is slightly modified now.

                  The below mentioned query might work for items entered from same level of BOM, the new requirement is that items can be entered from diff level. Any ideas on how this can be achieved

                  Assembly:

                  XYZ

                  Items
                  Level1 : X Y Z

                  Sub - Assembly: X
                  Items: a

                  Sub - Assembly: Y
                  Item: b

                  Sub - Assembly: Z
                  Item: c

                  Scenario- 1:

                  User enters: X, Y, Z (same level of BOM)

                  output: assembly: XYZ

                  Scenario-2: (different levels of BOM)

                  User enters: X, Y, c

                  output: assembly: XYZ

                  Scenario-3: X, b, c (different levels of BOM)

                  output: assembly: XYZ

                  Thanks for your time

                  Edited by: User910243567 on Apr 30, 2012 1:43 AM
                  • 6. Re: BOM Query
                    Etbin
                    Maybe expensive but constructing all paths including your Items and selecting those containing all Items should work
                    (I just cannot figure out your table and columns to accomplish that)
                    select substr(path,2,instr(path,'/',1,2) - 2 product, ...
                      from (select sys_connect_by_path(... ,'/') || '/' path, ...
                              from ...
                             start from ... in ('Item1','Item2','Item3')
                             connect by prior ... = ...
                           )
                     where instr(path,'/' || 'Item1' || '/') *
                           instr(path,'/' || 'Item2' || '/') * 
                           instr(path,'/' || 'Item3' || '/') > 0 
                    Regards

                    Etbin
                    • 7. Re: BOM Query
                      Frank Kulash
                      User910243567 wrote:
                      Hi Frank,

                      The Requirement is slightly modified now.
                      The way to post a question has not been modified. You still need to post CREATE TABLE and INSERT statements for some same data, and the results you want from that data.

                      In general, the way to find ancestors who have all of a given list of descendants is:
                      WITH     got_descendants     AS
                      (
                           SELECT     SYS_CONNECT_BY_ROOT  id          AS ancestor_id
                           ,     id                    AS descendant_id
                           FROM     table_x
                           WHERE     id     IN (target1, target2, ..., targetn)
                           CONNECT BY     parent_id     = PRIOR id
                      )
                      SELECT       ancestor_id
                      FROM       got_descendants
                      GROUP BY  ancestor_id
                      HAVING       COUNT (DISTINCT descendant_id)     = n
                      ORDER BY  ancestor_id
                      ;
                      If you put all the target ids in a separate table (perhaps a global temporary table), then you can generalize the query above to work with any number of target items, without having to change the query at all.
                      • 8. Re: BOM Query
                        User910243567
                        Thanks for your response.
                        • 9. Re: BOM Query
                          User910243567
                          I will make sure follow proper guidelines next time, really appreciate your help in this regard.

                          ThankS!
                          • 10. Re: BOM Query
                            User910243567
                            I have built the query as per your suggestions above, but there seems to be issue when the item entered is found in more than 1 sublevel. When i checked the path using SYS_CONNECT_BY.

                            As my current logic is returning the below result based on the item count: (Items I entered: P-1, P-345, P-2)

                            As see below Item P-1 appears in more than one sublevel. My logic is based on the item count(in this case 3) and number of rows it returned.(3 below)

                            1. /P-1/P-33/P-900
                            2. /P-345/P-432/P-2345/P-900
                            3./P-1/P-34/P-900

                            Can someone please guide, I can mail more details if you share your email id.

                            Thanks for your help.
                            • 11. Re: BOM Query
                              Frank Kulash
                              Hi,

                              Sorry, I don;t understand the problem.
                              User910243567 wrote:
                              I have built the query as per your suggestions above, but there seems to be issue when the item entered is found in more than 1 sublevel. When i checked the path using SYS_CONNECT_BY.

                              As my current logic is returning the below result based on the item count: (Items I entered: P-1, P-345, P-2)

                              As see below Item P-1 appears in more than one sublevel.
                              What is a "sub-level"? How is it related to the level?
                              The output below seems to indicate that 'P-1' is always at the same level: the 1st level (counting in the direction of the CONNECT BY) or the 3rd level (going in the opposite direction).
                              My logic is based on the item count(in this case 3) and number of rows it returned.(3 below)

                              1. /P-1/P-33/P-900
                              2. /P-345/P-432/P-2345/P-900
                              3./P-1/P-34/P-900
                              No kidding, you need to post CREATE TABLE and INSERT statments for the sample data, and the results you want from that data. Explain how you get those results from that data. It can be very helpful to make up terms, like "sub-level", if you don't know a common name for something, but make sure you define them.
                              Is the solution above coming close to what you want? If so, point out where it is not producing what you want, and explain how you get the right results in those places. If you modicifed the solution above, post your revised version.
                              Can someone please guide, I can mail more details if you share your email id.
                              There's no need for that. Post your CREATE TABLE and INSERT statements, results and explanation here, where lots of different people can help you.
                              • 12. Re: BOM Query
                                User910243567
                                Thanks for your reply, because of the sensitive of the work i am doing, i cannot share the details on this forum. thats the reason i was requesting for email address.

                                Thanks!
                                • 13. Re: BOM Query
                                  Frank Kulash
                                  Hi,
                                  User910243567 wrote:
                                  Thanks for your reply, because of the sensitive of the work i am doing, i cannot share the details on this forum. thats the reason i was requesting for email address.
                                  If you had my email address, would you send me sensitive information, knowing that I might post it anywhere? Of course not! You would change the data, and probably the names of the columns and the tables as well, so that I couldn't do any harm.
                                  Given that you need to make a test case with fake data, and perhaps fake column- and table names, anyway, then you might as well just post it here.
                                  • 14. Re: BOM Query
                                    1001125
                                    How to get Assemble Single Level BOM information? Please share sql