That’s a good one Matt.

However i tried a similar process to find out the Nth Level Item. I have created a procedure which takes your Item Number as input and then gives you BOM Explosion report as the output.  If your requirement is similar let me know will share the complete procedure. The procedure is applicable for one as well as multiple input.

Below is my cursor or to say the select statement for generating BOM Explosion. You can give your item number in place of ‘your input item number’ in below SQL and use it.

SELECT   ‘your input item Number’ AS PARENT_ITEM,
 a.item_number,
 LEVEL
      FROM   AGILE.BOM A
START WITH   (A.ITEM = (SELECT   DISTINCT i.id
                          FROM   agile.item i, agile.bom b
                         WHERE   i.id = b.item AND i.item_number = ‘your input item Number’)
              AND NVL (A.change_out, 0) = 0
              AND (SUBSTR (A.flags, 5, 1) = 1)
              AND A.id NOT IN (SELECT   C.prior_bom
                                 FROM   AGILE.bom C
                                WHERE   C.item = A.item))
CONNECT BY       PRIOR A.COMPONENT = A.ITEM
             AND NVL (A.change_out, 0) = 0
             AND (SUBSTR (A.flags, 5, 1) = 1)
             AND A.id NOT IN (SELECT   B.prior_bom
                                FROM   AGILE.bom B
                               WHERE   B.item = A.item);

Regards,
Arif

Hi Raju,

You can connect Item and BOM table for Level-1 bom output and then use hierarchical query concept to extend it to all levels of BOM. I was able to achieve an output similar to BOM explosion report using the same concept. 

Regards,
Arif

Try this.  I can’t take credit for this since our DBA found it after picking up this SQL query being passed from the Agile application to Oracle database (as we were diagnosing an earlier issue).  I later figured out that this is a BOM explosion report.  Use the current change ID or most recently released Change ID for the ? variable.  

SELECT DISTINCT a.id, 
                item_number, 
                find_number, 
                change_in, 
                change_out 
FROM   bom a 
START WITH ( a.item IN (SELECT DISTINCT item 
                        FROM   rev 
                        WHERE  CHANGE = ? 
                               AND item > 0) 
             AND Nvl (a.change_out, 0) = 0 
             AND ( Substr (a.flags, 5, 1) = 1 
                    OR a.change_in = ? ) 
             AND a.id NOT IN (SELECT c.prior_bom 
                              FROM   bom c 
                              WHERE  c.change_in = ?) ) 
CONNECT BY PRIOR a.component = a.item 
                 AND Nvl (a.change_out, 0) = 0 
                 AND ( Substr (a.flags, 5, 1) = 1 
                        OR a.change_in = ? ) 
ORDER  BY To_number(find_number)

Raju,

I’ve expanded on Arif’s answer just a little.  I would add the DISTINCT keyword (as Matt did).  This will prevent duplicate records, which may happen especially if your company makes use of sites. If your BOMs can vary between one site and the next, then you may even want to specify which site in the WHERE clause. 
If you also want your BOMs to be ordered in a way that is similar to how Agile might show an exploded BOM, then you may want to add a “path” that properly connects your find numbers.  Note that Matt used an ORDER BY statement which orders the exploded BOM numerically.  That’s fine as long as there is no duplication of numbers in your sub-assemblies. If there are, all of the 1’s will come first, followed by the 2’s, etc.  At our company we use find numbers up to 4 digits long.  In order to create a proper sort path, I must pad the find numbers with zeros.  See my query below.  Hopefully it will make sense.

Regards,
Steve

SELECT  distinct :assembly AS PARENT_ITEM,
 a.item_number,
 LEVEL, A.FIND_NUMBER
 ,SYS_CONNECT_BY_PATH(LPAD(A.FIND_NUMBER,4,’0′), ‘|’) ITEM_PATH
      FROM   AGILE.BOM A
START WITH   (A.ITEM = (SELECT   DISTINCT i.id
                          FROM   agile.item i, agile.bom b
                         WHERE   i.id = b.item AND i.item_number = :assembly)
              AND NVL (A.change_out, 0) = 0
              AND (SUBSTR (A.flags, 5, 1) = 1)
              AND A.id NOT IN (SELECT   C.prior_bom
                                 FROM   AGILE.bom C
                                WHERE   C.item = A.item))
CONNECT BY       PRIOR A.COMPONENT = A.ITEM
             AND NVL (A.change_out, 0) = 0
             AND (SUBSTR (A.flags, 5, 1) = 1)
             AND A.id NOT IN (SELECT   B.prior_bom
                                FROM   AGILE.bom B
                               WHERE   B.item = A.item)
ORDER BY ITEM_PATH;