Search Results mtl_material_transactions




Here's a detailed description of the MTL_MATERIAL_TRANSACTIONS table in Oracle E-Business Suite:

Table Name: MTL_MATERIAL_TRANSACTIONS
Module: Inventory Management (INV)

Purpose:
- Tracks all material movements and inventory transactions
- Records detailed information about inventory item movements across subinventories
- Serves as a primary transaction history log for inventory operations

Key Columns:
1. TRANSACTION_ID (Primary Key)
- Unique identifier for each transaction
- Sequentially generated
- Used for referencing specific inventory movements

2. INVENTORY_ITEM_ID
- Links to MTL_SYSTEM_ITEMS table
- Identifies the specific inventory item being transacted

3. ORGANIZATION_ID
- Identifies the inventory organization
- References HR_ORGANIZATION_UNITS

4. TRANSACTION_TYPE_ID
- Indicates the type of transaction (e.g., receipt, issue, transfer)
- Links to MTL_TRANSACTION_TYPES

5. TRANSACTION_SOURCE_TYPE_ID
- Specifies the source of the transaction (e.g., PO, Work Order)

6. TRANSACTION_SOURCE_ID
- Reference to source document (Purchase Order, Work Order)

7. TRANSACTION_DATE
- Date and timestamp of the transaction
- Used for tracking and reporting purposes

8. PRIMARY_QUANTITY
- Quantity of items transacted
- Recorded in primary inventory unit of measure

9. TRANSACTION_QUANTITY
- Actual quantity moved
- Can differ from primary quantity based on UOM conversions

10. SUBINVENTORY_CODE
- Source and destination subinventory locations

11. LOCATOR_ID
- Specific storage location within subinventory

Common Transaction Types:
- Receipts
- Issues
- Transfers
- Adjustments
- Cost updates

Important Considerations:
- Closely integrated with Oracle Inventory, Purchasing, and Manufacturing modules
- Critical for inventory tracking and costing
- Used extensively in inventory reconciliation and auditing

Reporting and Analysis:
- Provides comprehensive transaction history
- Supports inventory valuation
- Enables detailed traceability of item movements

Performance Tips:
- Indexed on TRANSACTION_ID, INVENTORY_ITEM_ID
- Large volume table, consider partitioning for large inventories

Sample SQL Query:
```sql
SELECT transaction_id,
inventory_item_id,
transaction_date,
transaction_type_id,
primary_quantity
FROM mtl_material_transactions
WHERE organization_id = :p_org_id
AND transaction_date BETWEEN :start_date AND :end_date;
```

This comprehensive overview covers the essential aspects of the MTL_MATERIAL_TRANSACTIONS table in Oracle E-Business Suite's Inventory Management module.