Query to get item attributes in Oracle apps

Query to get item attributes in Oracle apps

In this post , We will be discuss about the Query to get item attributes in Oracle apps. This query will help to extract the item detail with complete attributes values in oracle apps. We can get the complete item attributes informations being set for the given inventory item in oracle apps using this sql query. Please find below the detail SQL Query to get item attributes in Oracle apps.

3 Most Important Tables used by Query to get item attributes in Oracle apps

1.fnd_lookup_values

2.mtl_system_items

3.mtl_item_attributes_v

Detail SQL Query to get item attributes in Oracle apps

SELECT   segment1 item,
           msi.description,
           ml.meaning item_type,
           (SELECT      ia.user_attribute_name_gui
                     || '.'
                     || msi.inventory_item_status_code
              FROM   apps.mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.inventory_item_status_code')
              ITEM_STATUS,
           (SELECT      ia.user_attribute_name_gui
                     || '.'
                     || msi.purchasing_item_flag
              FROM   apps.mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.purchasing_item_flag') PURCHASING_ITEM_FLAG,
           (SELECT      ia.user_attribute_name_gui
                     || '.'
                     || msi.internal_order_enabled_flag
              FROM   apps.mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.internal_order_enabled_flag') internal_order_enabled_flag,
           (SELECT      ia.user_attribute_name_gui
                     || '.'
                     || msi.customer_order_enabled_flag
              FROM   apps.mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.customer_order_enabled_flag') customer_order_enabled_flag,
                         (SELECT      ia.user_attribute_name_gui
                     || '.'
                     || msi.shippable_item_flag
              FROM   apps.mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.shippable_item_flag') Shippable_item_flag,
           (SELECT      ia.user_attribute_name_gui
                     || '.'
                     || msi.mtl_transactions_enabled_flag
              FROM   apps.mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.mtl_transactions_enabled_flag') transactions_enabled_flag,
           (SELECT      ia.user_attribute_name_gui
                     || '.'
                     || msi.purchasing_enabled_flag
              FROM   apps.mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.purchasing_enabled_flag') purchasing_enabled_flag,
           (SELECT      ia.user_attribute_name_gui
                     || '.'
                     || msi.inventory_asset_flag
              FROM   apps.mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.inventory_asset_flag') inventory_asset_flag,
           (SELECT   ia.user_attribute_name_gui || '.' || msi.bom_enabled_flag
              FROM   apps.mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.bom_enabled_flag') bom_enabled_flag,
           (SELECT      ia.user_attribute_name_gui
                     || '.'
                     || msi.stock_enabled_flag
              FROM   apps.mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.stock_enabled_flag') stock_enabled_flag,
                                (SELECT      ia.user_attribute_name_gui
                     || '.'
                     || msi.inventory_item_flag
              FROM   apps.mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.inventory_item_flag') inventory_item_flag,
           (SELECT      ia.user_attribute_name_gui
                     || '.'
                     || msi.internal_order_flag
              FROM   apps.mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.internal_order_flag') internal_order_flag,
           (SELECT   ia.user_attribute_name_gui || '.' || msi.
      build_in_wip_flag
              FROM   apps.mtl_item_attributes_v ia
             WHERE   LOWER (ia.attribute_name) =
                        'mtl_system_items.build_in_wip_flag') build_in_wip_flag
    FROM   apps.fnd_lookup_values ml, apps.mtl_system_items msi
   WHERE      1=1-- msi.segment1 = '010-0041'
           AND msi.organization_id = :P_ORG_ID
           AND msi.item_type = ml.lookup_code(+)
           AND ml.lookup_type(+) = 'ITEM_TYPE'
ORDER BY   1, 2