--過濾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                                                ) LSTSTART 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;