Query to get Inventory Item details in R12

SELECT MTL_SYSTEM_ITEMS_B.LAST_UPDATE_DATE

      ,MTL_SYSTEM_ITEMS_B.LAST_UPDATED_BY

  ,MTL_SYSTEM_ITEMS_B.CREATION_DATE

  ,MTL_SYSTEM_ITEMS_B.CREATED_BY

  ,MTL_SYSTEM_ITEMS_B.DESCRIPTION

  ,MTL_SYSTEM_ITEMS_B.SEGMENT1

  ,MTL_SYSTEM_ITEMS_B.SHELF_LIFE_CODE

  ,MTL_SYSTEM_ITEMS_B.UNIT_WEIGHT

  ,MTL_SYSTEM_ITEMS_B.WEIGHT_UOM_CODE

  ,MTL_SYSTEM_ITEMS_B.VOLUME_UOM_CODE

  ,MTL_SYSTEM_ITEMS_B.UNIT_VOLUME

  ,MTL_SYSTEM_ITEMS_B.PRIMARY_UOM_CODE

  ,MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID

  ,MTL_SYSTEM_ITEMS_B.ITEM_TYPE

  ,MTL_PARAMETERS.MASTER_ORGANIZATION_ID

  ,MTL_SYSTEM_ITEMS_B.HAZARD_CLASS_ID

  ,MTL_SYSTEM_ITEMS_B.SERIAL_NUMBER_CONTROL_CODE

  ,MTL_SYSTEM_ITEMS_B.PLANNING_MAKE_BUY_CODE

  ,MTL_SYSTEM_ITEMS_B.BASE_ITEM_ID

  ,MTL_SYSTEM_ITEMS_B.START_DATE_ACTIVE

  ,MTL_SYSTEM_ITEMS_B.RETURNABLE_FLAG

  ,MTL_SYSTEM_ITEMS_B.PURCHASING_ENABLED_FLAG

  ,MTL_SYSTEM_ITEMS_B.SERVICEABLE_PRODUCT_FLAG

  ,MTL_SYSTEM_ITEMS_B.END_DATE_ACTIVE

  ,MTL_SYSTEM_ITEMS_B.FIXED_ORDER_QUANTITY

  ,MTL_SYSTEM_ITEMS_B.ORDER_COST

  ,MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_FLAG

  ,MTL_SYSTEM_ITEMS_B.CUSTOMER_ORDER_ENABLED_FLAG 

  FROM MTL_SYSTEM_ITEMS_B

      ,MTL_PARAMETERS 

 WHERE MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID = MTL_PARAMETERS.ORGANIZATION_ID 

   AND MTL_PARAMETERS.MASTER_ORGANIZATION_ID = MTL_PARAMETERS.ORGANIZATION_ID                             

Query to get AP Invoice Distribution to Inventory Material Distribution Data

SELECT mmt.transaction_date

          ,(SELECT ml.meaning

              FROM mfg_lookups ml

             WHERE ml.lookup_Type = 'CST_ACCOUNTING_LINE_TYPE'

               AND ml.lookup_code = mta.accounting_line_type

                ) accounting_type

          ,gl_code.concatenated_segments account, mtt.transaction_type_name transaction_type

          ,(SELECT po_hdr.segment1 

              FROM po_headers_all po_hdr 

             WHERE po_hdr.po_header_id = po_dist.po_header_id) transaction_source

          ,mmt.transaction_uom uom

          ,mta.primary_quantity 

          ,mta.base_transaction_value

          ,mmt.transaction_id, mmt.transaction_type_id, items.segment1 item, mmt.inventory_item_id, mmt.organization_id

          ,gl_code.code_combination_id

      FROM ap_invoice_distributions_all inv_dist

          ,po_distributions_all po_dist

          ,rcv_transactions rcv_trx

          ,mtl_material_transactions mmt

          ,mtl_transaction_types mtt

          ,mtl_transaction_accounts mta

          ,gl_code_combinations_kfv gl_code

          ,mtl_system_items_b items

     WHERE 1=1

       --AND inv_dist.po_distribution_id = 10536072

       AND inv_dist.po_distribution_id = po_dist.po_distribution_id

       AND po_dist.po_header_id = rcv_trx.po_header_id

       AND po_dist.po_line_id = rcv_trx.po_line_id

       AND po_dist.line_location_id = rcv_trx.po_line_location_id

       AND po_dist.po_distribution_id = rcv_trx.po_distribution_id

       AND transaction_type = 'DELIVER'

       AND mmt.rcv_transaction_id = rcv_trx.transaction_id

       AND mmt.transaction_type_id = mtt.transaction_type_id

       AND mmt.transaction_id = mta.transaction_id

       AND mta.reference_account = gl_code.code_combination_id

       AND mmt.inventory_item_id = items.inventory_item_id

       AND mmt.organization_id = items.organization_id

Few R12 Inventory Interview Questions in Oracle Apps

1. What is Master Item? 
2. What is Onhand quantity and Available quantity?
3. What is Move Orders? 
4. What are the Inventory Organizations, Name few Sub-Inventories?
5. What is KFF? Name few KFF's.
6. Tell some of the base tables in Inventory Module? 
7. In which column item will be stored?
8. What is the Primary key in MTL_SYSTEM_ITEMS_B table?
9. In which table we an find out Master Organizations?
10. In which table we can find out Sub-inventories?
11. In which column we can find out Item category name?
12. What is ABC analysis and ATP date? 
13. What are the Item Transactions we have?
14. What are the reports you have developed or Customized in Inventory Module?
15. What is min-max planning?

Item Master Item Types in Oracle Inventory

Item Master Item Types are Lookup Type which is held in the table fnd_common_lookups. Use the below SQL Query to see the entries: 

SELECT lookup_type, lookup_code, meaning
FROM fnd_common_lookups 
WHERE lookup_type = 'ITEM_TYPE' 

order by lookup_type, lookup_code; 

Join Condition:- 
mtl_system_items_b.item_type = fnd_common_lookups.lookup_code