PLSQL QUERY TO VIEW ALL INVENTORY ITEMS FOR BOM IN ORACLE APPS

This below view will give you all the child items using in oracle bill of material (BOM) again finished good and sub-assembly.


CREATE OR REPLACE FORCE VIEW apps.xx_bom_tree (lev,
                                                     component_quantity,
                                                     inventory_item_id,
                                                     use_item,
                                                     primary_uom_code,
                                                     comp_item
                                                    )
AS
   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 = 'YUU
               AND bic.effectivity_date < SYSDATE
               AND bom.alternate_bom_designator IS NULL
        START WITH    msib.segment1
                   || msib.segment2
                   || msib.segment3
                   || msib.segment4
                   || msib.segment5 = 'ERRRRRR'
        /* top parent item here */
   CONNECT BY NOCYCLE PRIOR bic.component_item_id = msib.inventory_item_id
          ORDER BY LEVEL;