If a store manager looks at a shelf and sees 10 laptops, they believe their inventory is 10. But if an online customer just purchased 3 of them for store pickup, and a truck is arriving tomorrow with 5 more, the physical count is dangerously misleading for supply chain planning.

Oracle Retail Merchandising System (RMS) solves this by maintaining a comprehensive Inventory Position. This calculated metric dictates everything from automated replenishment to ecommerce order promising. This guide breaks down the critical ITEM_LOC_SOH table and how RMS tracks stock moving in and out of the business.

1. The ITEM_LOC_SOH Table#

The heartbeat of RMS inventory is the ITEM_LOC_SOH (Item Location Stock on Hand) table. Unlike some ERPs that calculate inventory on the fly by summing millions of transaction logs, RMS maintains an active, rolling total of every inventory state in this table.

When an item is received, STOCK_ON_HAND increments. When it's sold, it decrements. When a PO is approved, STOCK_ON_ORDER increments. This allows systems to query the exact state of inventory in milliseconds without heavy aggregations.

2. Physical Inventory Buckets#

These columns represent goods that are physically inside the four walls of the location (store or warehouse).

• STOCK_ON_HAND: The master count. Everything physically in the building that is owned by the retailer.
• NON_SELLABLE_QTY: A subset of STOCK_ON_HAND that cannot be sold (e.g., damaged goods, display models, or expired products). These are excluded from replenishment calculations.

3. Inbound Inventory Buckets#

These columns represent inventory that is owned or committed to the location, but has not yet physically arrived.

• STOCK_ON_ORDER: Inventory tied to Approved Purchase Orders. The supplier has been instructed to ship it, but it has not left their facility (or they haven't sent an ASN).
• IN_TRANSIT_QTY: Inventory that is currently moving on a truck or a boat. This happens when a supplier sends an ASN, or when a warehouse ships a transfer to a store.

4. Outbound & Reserved Buckets#

These columns represent inventory that is physically in STOCK_ON_HAND, but you are no longer allowed to sell it because it is promised elsewhere.

• CUSTOMER_RESV: Units reserved for a specific customer (e.g., Buy Online, Pick Up In Store - BOPIS). The customer hasn't collected them yet, so they are physically in the store, but they cannot be sold to a walk-in customer.
• TSF_RESERVED_QTY: Units reserved to be transferred out. For example, Store A is told to transfer 5 units to Store B. Store A's TSF_RESERVED_QTY increases by 5 until the truck arrives to pick them up.
• RTV_QTY: Units reserved for a Return To Vendor. They are boxed up waiting for the supplier to collect them.

5. Calculating Net Inventory Position#

The Inventory Position is the metric used by the RMS Replenishment engine. It asks: "If I look at what I have, what I've promised away, and what is already on its way... do I need to order more?"

The standard formula for Inventory Position is:

Inventory Position = (Sellable Physical Stock) + (Inbound Stock) - (Outbound Reservations)

Expanded into ITEM_LOC_SOH columns, it looks like this:

Inventory Position = (STOCK_ON_HAND - NON_SELLABLE_QTY) + (IN_TRANSIT_QTY + STOCK_ON_ORDER) - (CUSTOMER_RESV + TSF_RESERVED_QTY + RTV_QTY)

If this calculated Inventory Position drops below the item's configured Minimum Stock Level, RMS will automatically generate a Purchase Order or Warehouse Transfer to fill the gap.

6. SQL Deep Dives#

Querying the True Inventory Position

This query replicates the internal RMS logic to calculate exactly how many "free and clear" units a location has, taking into account all inbound pipelines and outbound reservations.

SELECT 
    item,
    loc AS location,
    stock_on_hand,
    -- Step 1: Available Physical
    (stock_on_hand - non_sellable_qty) AS available_physical,
    -- Step 2: Total Inbound
    (in_transit_qty + stock_on_order) AS total_inbound,
    -- Step 3: Total Reserved
    (customer_resv + tsf_reserved_qty + rtv_qty) AS total_reserved,
    
    -- Final Calculation: The Inventory Position
    ((stock_on_hand - non_sellable_qty) 
     + (in_transit_qty + stock_on_order) 
     - (customer_resv + tsf_reserved_qty + rtv_qty)) AS inventory_position
FROM item_loc_soh
WHERE item = '10012345'
  AND loc = 200;

Finding Negative Inventory

A major issue in retail is negative inventory, which occurs when a store's POS registers a sale for an item that RMS believes is completely out of stock. This happens due to theft, mis-scans at the register, or unrecorded receipts.

SELECT 
    item,
    loc AS location,
    stock_on_hand,
    last_update_datetime
FROM item_loc_soh
WHERE stock_on_hand < 0
ORDER BY stock_on_hand ASC;

7. Common Gotchas#

8. Official Oracle Resources#

For further reading, consult the official Oracle documentation:

• Oracle Retail Merchandising System Operations Guide: Inventory Management
• Oracle RMS Data Model: Inventory (ITEM_LOC_SOH)