Oracle SQL query multi-level BOM

Oracle SQL query multi-level BOM

In this post , We will be discuss about the SQL query which help to extract the multi level bom in oracle apps. This is one of the very useful SQL to find out all the components details in the BOM. In some scenario BOM components itself working as a BOM Item so these kind of scenarios , we do call the multi level BOM in oracle apps and this query will help to extract the these kind of Multi level bom details.

Important Table using in the Multi level BOM sql query

1.bom_components_b

2.bom_structures_b

3.mtl_system_items_b

Detail Oracle SQL query multi-level BOM

SELECT DISTINCT LEVEL "LEV", component_quantity, msib.inventory_item_id,

                   msib2.inventory_item_id use_item, msib2.primary_uom_code,

                      msib2.segment1

                   || '.'

                   || msib2.segment2

                   || '.'

                   || msib2.segment3

                   || '.'

                   || msib2.segment4

                   || '.'

                   || msib2.segment5 AS "COMP_ITEM"

              /*bic.component_item_id,*/

              /*msib.inventory_item_id,*/

              /*msib2.inventory_item_id*/

   FROM            bom.bom_components_b bic,

                   bom.bom_structures_b bom,

                   inv.mtl_system_items_b msib,

                   inv.mtl_system_items_b msib2

             WHERE 1 = 1

               AND bic.bill_sequence_id = bom.bill_sequence_id

               AND bic.disable_date IS NULL

               AND bom.assembly_item_id = msib.inventory_item_id

               AND bom.organization_id = msib.organization_id

               AND bic.component_item_id = msib2.inventory_item_id

               AND bom.organization_id = msib2.organization_id

               AND bom.organization_id = 85         /* organization id here */

               AND msib2.segment5 = 'F00'

               AND bic.effectivity_date < SYSDATE

               AND bom.alternate_bom_designator IS NULL

        START WITH    msib.segment1

                   || msib.segment2

                   || msib.segment3

                   || msib.segment4

                   || msib.segment5 = 'ITEM CODE'

        /* top parent item here */

   CONNECT BY NOCYCLE PRIOR bic.component_item_id = msib.inventory_item_id

          ORDER BY LEVEL;

Oracle SQL query multi-level BOM