Get Basic Detail of Items

Basic Detail

• Resource Type : SQL
• Purpose : Get basic detail of items.
• Difficulty Rating : Beginner

Apps Detail

• Version : R12 ( 12.1.3 )
• Responsibility : Inventory
• Related Function : Items : Master Items

Related Tables

• INV.MTL_SYSTEM_ITEMS_B
• INV.MTL_SYSTEM_ITEMS_TL

Other Detail

• Long Description of the items is stored in "INV.MTL_SYSTEM_ITEMS_TL" table.
• Item's Code / Name is stored in "SEGMENT1" field of "INV.MTL_SYSTEM_ITEMS_B" table.

Considerations

• None

Code

/**************************************************
PURPOSE : GET BASIC DETAIL OF THE ITEMS
**************************************************/

SELECT ITM.ORGANIZATION_ID AS ORG_ID, ITM.INVENTORY_ITEM_ID AS ITEM_ID, 
       ITM.SEGMENT1 AS ITEM_CODE, ITM.DESCRIPTION AS ITEM_NAME,
       ITM.START_DATE_ACTIVE AS ACTIVE_FROM, ITM.END_DATE_ACTIVE AS ACTIVE_TO,
       ITM.ENABLED_FLAG, ITM.INVENTORY_ITEM_STATUS_CODE AS ITEM_STATUS
FROM   INV.MTL_SYSTEM_ITEMS_B ITM;

Additional fields can be selected to get additional information. For example,