Home » SQL & PL/SQL » SQL & PL/SQL » Bill of Materials (11g R2)
Bill of Materials [message #585378] Fri, 24 May 2013 08:23 Go to next message
robert1981
Messages: 7
Registered: May 2013
Location: Newcastle, England
Junior Member
Hello, I am hoping someone can help me with a problem querying hierarchical data.
I have worked with Bill of Material type queries for some time now and have a good handle on using the Connect By Prior statement to produce an exploded Bill of Materials, however we have an issue with the MRP engine and project managers require an exploded BOM totalling part quantities to buy until the problem is fixed.
The problem I am faced with is that our BOM's have many levels and use common parts in multiple assemblies which are sub-assemblies of sub-assemblies, for example if I want to total how many M3 X 20 Bolts are used in a particular structure from the top level assembly, this is easy enough if all sub-assemblies were at a quantity of 1 so lets say the M3 bolt was used in 5 sub-assemblies with varing quantitys then its a case of summing the qty per assembly.
However, if the M3 Bolt's parent has a quantity of say 2 and the quantity per assembly is 1 then how do I search back up the tree to find all of the parent quantities so can multiply the correct value?

Here is my basic query:


SELECT PART_NO, PART_REV, SUB_PART_NO, SUB_PART_REV, QTY, LEVEL
FROM ENG_PART_STRUCTURE_TAB
CONNECT BY PRIOR SUB_PART_NO = PART_NO AND PRIOR SUB_PART_REV = PART_REV
START WITH PART_NO = 'TOP LEVEL PART NUMBER' AND PART_REV = 'REV 0'


Best Regards

Robert.
Re: Bill of Materials [message #585382 is a reply to message #585378] Fri, 24 May 2013 09:21 Go to previous messageGo to next message
Amine
Messages: 259
Registered: March 2010
Senior Member

You can explode the parents having QTY > 1, then the sum would be easy. This may be helpful for you.

Regards,
Amine
Re: Bill of Materials [message #585392 is a reply to message #585382] Fri, 24 May 2013 10:29 Go to previous messageGo to next message
robert1981
Messages: 7
Registered: May 2013
Location: Newcastle, England
Junior Member
Amine, thank you for your help, I will give it a try.

Regards

Robert.
Re: Bill of Materials [message #585396 is a reply to message #585392] Fri, 24 May 2013 10:39 Go to previous messageGo to next message
Michel Cadot
Messages: 58641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: Bill of Materials [message #585419 is a reply to message #585396] Sat, 25 May 2013 03:22 Go to previous messageGo to next message
robert1981
Messages: 7
Registered: May 2013
Location: Newcastle, England
Junior Member
Hi Michel, thank you for the guidance, please see below.

CREATE TABLE ENG_PART_STRUCTURE_TAB
(
  PART_NO VARCHAR2(35),
  PART_REV VARCHAR2(35),
  SUB_PART_NO VARCHAR2(35),
  SUB_PART_REV VARCHAR2(35),
  QTY NUMBER(6)
)


 --Top level assembly
 INSERT INTO ENG_PART_STRUCTURE_TAB
 VALUES('AA9999','0','AA0001','0', 1);

 INSERT INTO ENG_PART_STRUCTURE_TAB
 VALUES('AA9999','0','AA8888','1', 2);

 INSERT INTO ENG_PART_STRUCTURE_TAB
 VALUES('AA9999','0','AA0003','1', 4);
 
 INSERT INTO ENG_PART_STRUCTURE_TAB
 VALUES('AA8888','1','AA7777','2', 1);

 INSERT INTO ENG_PART_STRUCTURE_TAB
 VALUES('AA7777','2','AA0001','0', 2);


 INSERT INTO ENG_PART_STRUCTURE_TAB
 VALUES('AA7777','2','AA0002','1', 1);


 INSERT INTO ENG_PART_STRUCTURE_TAB
 VALUES('AA7777','2','AA0003','1', 1);

 INSERT INTO ENG_PART_STRUCTURE_TAB
 VALUES('AA7777','2','AA6666','1', 1);

 INSERT INTO ENG_PART_STRUCTURE_TAB
 VALUES('AA6666','1','AA5555','1', 1);

 INSERT INTO ENG_PART_STRUCTURE_TAB
 VALUES('AA5555','1','AA4444','3', 2);

 INSERT INTO ENG_PART_STRUCTURE_TAB
 VALUES('AA4444','3','AA0001','0', 1);


SELECT PART_NO, PART_REV, SUB_PART_NO, SUB_PART_REV, QTY, LEVEL
FROM ENG_PART_STRUCTURE_TAB
CONNECT BY PRIOR SUB_PART_NO = PART_NO AND PRIOR SUB_PART_REV = PART_REV
START WITH PART_NO = 'AA9999' AND PART_REV = '0'


The problem I have hit is how can I SUM the sub part quantities no matter what level they exists in the tree?
by using a sub-query to get the sum of the parent only works to the immediate parent, however the problem is getting to the N'th level for a part number and multiplying all its children below based on it's quantity as a child part,
any guidance would be greatly welcome.

Regards

Robert.
Re: Bill of Materials [message #585428 is a reply to message #585419] Sat, 25 May 2013 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 58641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post the result you want for the data you gave.

Regards
Michel
Re: Bill of Materials [message #585433 is a reply to message #585428] Sat, 25 May 2013 05:06 Go to previous messageGo to next message
robert1981
Messages: 7
Registered: May 2013
Location: Newcastle, England
Junior Member
PARENT_PART	PARENT_REV	CHILD_PART	CHILD_REV	QTY	LEVEL
AA9999	        0	        AA0001	               0	1	1
AA9999	        0	        AA0003	               1	4	1
AA9999	        0	        AA8888	               1	2	1
AA8888	        1	        AA7777	               2	1	2
AA7777	        2	        AA0001	               0	2	3
AA7777	        2	        AA0002	               1	1	3
AA7777	        2	        AA0003	               1	1	3
AA7777	        2	        AA6666	               1	1	3
AA6666	        1	        AA5555	               1	1	4
AA5555	        1	        AA4444	               3	2	5
AA4444	        3	        AA0001	               0	1	6


Above is the results from the initial query.

Example Child Part AA0001 is a Child of AA9999, AA7777 and AA4444. Part AA4444 is a child of AA5555 who is a child of AA6666 who is a child of AA7777 who is a child of AA8888 who has a QTY of 2, therefore the child part AA0001 appears at levels 1,3 and 6 and its parents have varing quantities so we need to multiply the parent quantities by the child AA0001 qty to get a total qty of AA0001 in the structure.
I hope this clarifies the problem.

Regards

Rob.
Re: Bill of Materials [message #585445 is a reply to message #585433] Sat, 25 May 2013 14:24 Go to previous messageGo to next message
Amine
Messages: 259
Registered: March 2010
Senior Member

This should meet your requirement

SQL> create or replace function sum_sub_parts(p_part_no in varchar2
  2  , p_part_rev in varchar2) return number is
  3  l_sum       number(10) := 0;
  4  l_qty       number(10) := 0;
  5  begin
  6
  7    begin
  8      select qty
  9      into l_qty
 10      from ENG_PART_STRUCTURE_TAB
 11      where PART_NO = p_part_no
 12      and PART_REV = p_part_rev
 13      ;
 14      exception
 15      when no_data_found then return 1;
 16      when too_many_rows then null;
 17    end;
 18
 19    for l in
 20    (
 21    select SUB_PART_NO, SUB_PART_REV, QTY
 22    from ENG_PART_STRUCTURE_TAB
 23    where PART_NO = p_part_no
 24    and PART_REV = p_part_rev
 25    )
 26    loop
 27      l_sum := l_sum
 28      + l.qty * sum_sub_parts(p_part_no => l.SUB_PART_NO
 29                            , p_part_rev => l.SUB_PART_REV);
 30    end loop;
 31
 32    return l_sum;
 33
 34  end;
 35  /

Fonction crÚÚe.

SQL> select sum_sub_parts('AA9999', '0') sum
  2  from dual
  3  /

       SUM
----------
        17

SQL>
SQL> select sum_sub_parts('AA4444', '3') sum
  2  from dual
  3  /

       SUM
----------
         1

SQL>
SQL> select sum_sub_parts('AA5555', '1') sum
  2  from dual
  3  /

       SUM
----------
         2

SQL>
Re: Bill of Materials [message #585449 is a reply to message #585433] Sat, 25 May 2013 16:11 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1966
Registered: January 2010
Senior Member
WITH T AS (
           SELECT  CONNECT_BY_ROOT SUB_PART_NO SUB_PART_NO,
                   CONNECT_BY_ROOT SUB_PART_REV SUB_PART_REV,
                   XMLCAST(XMLQUERY(1 || SYS_CONNECT_BY_PATH(QTY,'*') RETURNING CONTENT) AS NUMBER) PATH_QTY
             FROM  ENG_PART_STRUCTURE_TAB
             WHERE CONNECT_BY_ISLEAF = 1
             START WITH SUB_PART_NO = :SUB_PART_NO
                    AND SUB_PART_REV = :SUB_PART_REV
             CONNECT BY SUB_PART_NO = PRIOR PART_NO
                    AND SUB_PART_REV = PRIOR PART_REV
          )
SELECT  SUB_PART_NO,
        SUB_PART_REV,
        SUM(PATH_QTY) TOTAL_QTY
  FROM  T
  GROUP BY SUB_PART_NO,
           SUB_PART_REV
/



For example:

SQL> VARIABLE SUB_PART_NO VARCHAR2(10)
SQL> VARIABLE SUB_PART_REV VARCHAR2(10)
SQL> EXEC :SUB_PART_NO := 'AA0001'

PL/SQL procedure successfully completed.

SQL> EXEC :SUB_PART_REV := '0'

PL/SQL procedure successfully completed.

SQL> WITH T AS (
  2             SELECT  CONNECT_BY_ROOT SUB_PART_NO SUB_PART_NO,
  3                     CONNECT_BY_ROOT SUB_PART_REV SUB_PART_REV,
  4                     XMLCAST(
  5                             XMLQUERY(
  6                                      1 || SYS_CONNECT_BY_PATH(QTY,'*')
  7                                      RETURNING CONTENT
  8                                     )
  9                             AS NUMBER
 10                            ) PATH_QTY
 11               FROM  ENG_PART_STRUCTURE_TAB
 12               WHERE CONNECT_BY_ISLEAF = 1
 13               START WITH SUB_PART_NO = :SUB_PART_NO
 14                      AND SUB_PART_REV = :SUB_PART_REV
 15               CONNECT BY SUB_PART_NO = PRIOR PART_NO
 16                      AND SUB_PART_REV = PRIOR PART_REV
 17            )
 18  SELECT  SUB_PART_NO,
 19          SUB_PART_REV,
 20          SUM(PATH_QTY) TOTAL_QTY
 21    FROM  T
 22    GROUP BY SUB_PART_NO,
 23             SUB_PART_REV
 24  /

SUB_PART_NO SUB_PART_REV   TOTAL_QTY
----------- ------------ -----------
AA0001      0                      9

SQL> EXEC :SUB_PART_NO := 'AA0003'

PL/SQL procedure successfully completed.

SQL> EXEC :SUB_PART_REV := '1'

PL/SQL procedure successfully completed.

SQL> WITH T AS (
  2             SELECT  CONNECT_BY_ROOT SUB_PART_NO SUB_PART_NO,
  3                     CONNECT_BY_ROOT SUB_PART_REV SUB_PART_REV,
  4                     XMLCAST(
  5                             XMLQUERY(
  6                                      1 || SYS_CONNECT_BY_PATH(QTY,'*')
  7                                      RETURNING CONTENT
  8                                     )
  9                             AS NUMBER
 10                            ) PATH_QTY
 11               FROM  ENG_PART_STRUCTURE_TAB
 12               WHERE CONNECT_BY_ISLEAF = 1
 13               START WITH SUB_PART_NO = :SUB_PART_NO
 14                      AND SUB_PART_REV = :SUB_PART_REV
 15               CONNECT BY SUB_PART_NO = PRIOR PART_NO
 16                      AND SUB_PART_REV = PRIOR PART_REV
 17            )
 18  SELECT  SUB_PART_NO,
 19          SUB_PART_REV,
 20          SUM(PATH_QTY) TOTAL_QTY
 21    FROM  T
 22    GROUP BY SUB_PART_NO,
 23             SUB_PART_REV
 24  /

SUB_PART_NO SUB_PART_REV   TOTAL_QTY
----------- ------------ -----------
AA0003      1                      6

SQL>


SY.
Re: Bill of Materials [message #585521 is a reply to message #585449] Mon, 27 May 2013 04:16 Go to previous message
robert1981
Messages: 7
Registered: May 2013
Location: Newcastle, England
Junior Member
Thank you all for your help.
Solomon, I have used the bones of your solution and with a few tweaks it has helped me get my query returning the correct value for a given structure.
I cant thank you enough for your help Solomon and everyone else who has contributed to this thread as it has helped me think of other ways to construct the query.

If I could buy you all a beer to say thank you I would!

Regards

Robert.
Previous Topic: sql query performance
Next Topic: does cast guarantee order of collection/nested table
Goto Forum:
  


Current Time: Fri Aug 01 05:57:52 CDT 2014

Total time taken to generate the page: 0.08705 seconds