同时展多个物料BOM List

--過濾ITEM類型為'Phantom Item', 'Reference item'
--并且為激活狀態 'Active'
--instead of the  value key

    SELECT LEVEL,
           LST.ASSEMBLY_ITEM_ID,
           LST.COMPONENT_ITEM_ID,
           LST.ORGANIZATION_ID,
           CONNECT_BY_ROOT (LST.PARENT_ITEM) ROOT_ITEM,
           LST.PARENT_ITEM,
           LST.CHILDREN_ITEM,
           LST.DESCRIPTION,
           LST.PRIMARY_UOM_CODE,
           LST.COMPONENT_QUANTITY,
           LST.YIELD
      FROM (SELECT BOM.ASSEMBLY_ITEM_ID,
                   BIC.COMPONENT_ITEM_ID,
                   BOM.ORGANIZATION_ID,
                   BIC.COMPONENT_QUANTITY,
                   BIC.COMPONENT_YIELD_FACTOR YIELD,
                   (SELECT MSI.SEGMENT1
                      FROM INV.MTL_SYSTEM_ITEMS_B MSI
                     WHERE     MSI.ORGANIZATION_ID = 190          -- value key
                           AND MSI.INVENTORY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID)
                      PARENT_ITEM,
                   (SELECT MSI.SEGMENT1
                      FROM INV.MTL_SYSTEM_ITEMS_B MSI
                     WHERE     MSI.ORGANIZATION_ID = 190          -- value key
                           AND MSI.INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID)
                      CHILDREN_ITEM,
                   (SELECT MSI.DESCRIPTION
                      FROM INV.MTL_SYSTEM_ITEMS_B MSI
                     WHERE     MSI.ORGANIZATION_ID = 190          -- value key
                           AND MSI.INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID)
                      DESCRIPTION,
                   (SELECT MSI.PRIMARY_UOM_CODE
                      FROM INV.MTL_SYSTEM_ITEMS_B MSI
                     WHERE     MSI.ORGANIZATION_ID = 190          -- value key
                           AND MSI.INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID)
                      PRIMARY_UOM_CODE
              FROM BOM_BILL_OF_MATERIALS BOM, BOM_INVENTORY_COMPONENTS BIC
             WHERE     BOM.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
                   AND BIC.DISABLE_DATE IS NULL -- 物料组件是否失效：NULL有效 / NOT NULL失效
                   AND NOT EXISTS
                              (SELECT 'x'              --FCL.MEANING ITEM_TYPE
                                 FROM MTL_SYSTEM_ITEMS_B MSI,
                                      FND_COMMON_LOOKUPS FCL
                                WHERE     1 = 1
                                      AND FCL.LOOKUP_TYPE = 'ITEM_TYPE'
                                      AND FCL.LOOKUP_CODE = MSI.ITEM_TYPE
                                      --AND FCL.APPLICATION_ID(+) = 401
                                      --and msi.segment1 = '22321500'
                                      AND MSI.ORGANIZATION_ID = 190 -- value key
                                      AND MSI.INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID
                                      AND (   FCL.MEANING IN ('Phantom Item', 'Reference item')
                                           OR MSI.INVENTORY_ITEM_STATUS_CODE <> 'Active'))
                   AND BOM.ORGANIZATION_ID = 190  -- value key
                                                ) LST
START WITH (LST.ASSEMBLY_ITEM_ID IN
               (SELECT INVENTORY_ITEM_ID
                  FROM MTL_SYSTEM_ITEMS_B MSI
                 WHERE     1 = 1
                       AND MSI.ORGANIZATION_ID = 190  -- value key
                       AND MSI.INVENTORY_ITEM_STATUS_CODE = 'Active'
                       --and msi.segment1 in('44600006', '44600007')
                       AND MSI.ITEM_TYPE <> 'PH'
                       AND MSI.ITEM_TYPE <> 'REF'))
CONNECT BY LST.ASSEMBLY_ITEM_ID = PRIOR LST.COMPONENT_ITEM_ID;