In a perfect world, inventory only decreases when a customer buys it. In reality, retail operations suffer from shrinkage (theft), damage, spoilage, and administrative errors. When a store manager realizes they have 8 units on the shelf but RMS says they have 10, they must bring the system back to reality.

Oracle RMS handles this through Inventory Adjustments. This guide covers how adjustments impact the ITEM_LOC_SOH table, why Reason Codes are the most critical component for finance, and how inventory statuses isolate unsellable stock.

1. The Adjustment Workflow#

An inventory adjustment is a transaction that directly manipulates STOCK_ON_HAND without an associated sale or receipt.

1. Trigger: A user (or integration via SIM/Store Inventory Management) submits an adjustment of -2 units for an item.
2. Physical Update: RMS updates ITEM_LOC_SOH.STOCK_ON_HAND by -2.
3. Financial Log: RMS writes a record to TRAN_DATA with TRAN_CODE = 22.
4. Valuation: The TOTAL_COST on the TRAN_DATA record is calculated using the location's current Weighted Average Cost (WAC) multiplied by the -2 units. This represents the total financial loss.

2. Reason Codes & GL Mapping#

From a physical perspective, an adjustment of -2 units is identical regardless of why it happened. From a financial perspective, the "why" is everything.

Did the goods spoil? Were they stolen? Were they damaged by a forklift in the warehouse?

When submitting an adjustment, the user must select an Inventory Adjustment Reason Code (e.g., Reason Code 15 = Spoilage). RMS uses the FIF_GL_CROSS_REF table to map this specific Reason Code to a specific General Ledger (GL) expense account.

• Shrinkage might hit the "Store Operations Expense" GL.
• Warehouse damage might hit the "Supply Chain Overhead" GL.

3. Inventory Status Changes#

Not all adjustments remove stock from the building. Sometimes, stock is physically present but cannot be sold—for example, if an item is recalled, expired, or pending repair.

RMS manages this through Inventory Statuses. Instead of adjusting STOCK_ON_HAND down, a user performs an Inventory Status Change.

1. The user moves 5 units from "Available" to "Trouble" status.
2. STOCK_ON_HAND remains the same.
3. INV_STATUS_QTY.QTY for the "Trouble" status code increases by 5.
4. RMS aggregates all non-available statuses and updates ITEM_LOC_SOH.NON_SELLABLE_QTY by +5.

This removes the 5 units from the calculable Inventory Position, triggering replenishment to replace them, while keeping the financial asset value on the books until they are officially written off or returned to the vendor.

4. Core Tables Reference#

GL Cross Reference (FIF_GL_CROSS_REF)

5. SQL Deep Dives#

Auditing High-Value Adjustments

This query isolates adjustments (Tran Code 22) that resulted in a financial loss greater than $1,000 in a single transaction, grouping them by reason code.

SELECT 
    td.item,
    td.location,
    td.tran_date,
    td.units,
    td.total_cost,
    iar.inv_adj_reason_desc
FROM tran_data td
JOIN inv_adj_reason iar ON td.reason_code = iar.inv_adj_reason_code
WHERE td.tran_code = 22
  AND td.units < 0 -- Negative adjustments (losses)
  AND td.total_cost < -1000 -- More than $1,000 lost
ORDER BY td.total_cost ASC;

6. Official Oracle Resources#

For further reading, consult the official Oracle documentation:

• Oracle Retail Merchandising System Operations Guide: Inventory Management
• Oracle RMS Data Model: Inventory Tables