Query to find an Item attribute info :

SELECT   segment1 item,
           msi.description,
           inventory_item_id,
           ml.meaning item_type,
           (SELECT      ia.user_attribute_name_gui
                     || '.'
                     || msi.inventory_item_status_code
              FROM   mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.inventory_item_status_code')
              attribute,
           (SELECT      ia.user_attribute_name_gui
                     || '.'
                     || msi.purchasing_item_flag
              FROM   mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.purchasing_item_flag')
              attribute,
           (SELECT      ia.user_attribute_name_gui
                     || '.'
                     || msi.shippable_item_flag
              FROM   mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.shippable_item_flag')
              attribute,
           (SELECT      ia.user_attribute_name_gui
                     || '.'
                     || msi.mtl_transactions_enabled_flag
              FROM   mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.mtl_transactions_enabled_flag')
              attribute,
           (SELECT      ia.user_attribute_name_gui
                     || '.'
                     || msi.so_transactions_flag
              FROM   mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.so_transactions_flag')
              attribute,
           (SELECT      ia.user_attribute_name_gui
                     || '.'
                     || msi.internal_order_enabled_flag
              FROM   mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.internal_order_enabled_flag')
              attribute,
           (SELECT      ia.user_attribute_name_gui
                     || '.'
                     || msi.customer_order_enabled_flag
              FROM   mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.customer_order_enabled_flag')
              attribute,
           (SELECT      ia.user_attribute_name_gui
                     || '.'
                     || msi.purchasing_enabled_flag
              FROM   mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.purchasing_enabled_flag')
              attribute,
           (SELECT      ia.user_attribute_name_gui
                     || '.'
                     || msi.inventory_asset_flag
              FROM   mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.inventory_asset_flag')
              attribute,
           (SELECT   ia.user_attribute_name_gui || '.' || msi.eng_item_flag
              FROM   mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.eng_item_flag')
              attribute,
           (SELECT      ia.user_attribute_name_gui
                     || '.'
                     || msi.inventory_item_flag
              FROM   mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.inventory_item_flag')
              attribute,
           (SELECT   ia.user_attribute_name || '.' || msi.service_item_flag
              FROM   mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.service_item_flag')
              attribute,
           (SELECT      ia.user_attribute_name_gui
                     || '.'
                     || msi.internal_order_flag
              FROM   mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.internal_order_flag')
              attribute,
           (SELECT   ia.user_attribute_name_gui || '.' || msi.
      build_in_wip_flag
              FROM   mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.build_in_wip_flag')
              attribute,
           (SELECT   ia.user_attribute_name_gui || '.' || msi.bom_enabled_flag
              FROM   mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.bom_enabled_flag')
              attribute,
           (SELECT      ia.user_attribute_name_gui
                     || '.'
                     || msi.stock_enabled_flag
              FROM   mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.stock_enabled_flag')
              attribute
    FROM   fnd_lookup_values ml, mtl_system_items msi
   WHERE       msi.segment1 LIKE 'AS18947%'
           AND msi.organization_id = 204
           AND msi.item_type = ml.lookup_code(+)
           AND ml.lookup_type(+) = 'ITEM_TYPE'
ORDER BY   1, 2