Oracle RMS Item Locations — Ranging, De-ranging & Ranging Rules

What Is Ranging?#

Ranging is the act of formally assigning an item to a location in Oracle RMS. Until a row exists in ITEM_LOC for a specific item-location combination, that item does not exist at that location from the system's perspective. It cannot be ordered to that location, cannot receive stock there, cannot be sold there, and cannot appear on a stock count.

Ranging is the bridge between the item catalog and retail operations. An item may be fully set up in ITEM_MASTER, have a supplier in ITEM_SUPPLIER, and be Active — but none of that makes it available at any particular store or warehouse. Only an ITEM_LOC row does that.

This design is intentional. Different stores carry different ranges. A fashion retailer's flagship store carries the full range including premium lines; a smaller format store carries only core basics. Warehouse ranging is also explicit — an item must be ranged to a warehouse before that warehouse can be used as a replenishment source for stores carrying the item.

The ITEM_LOC Table — Deep Dive#

ITEM_LOC is one of the most-referenced tables in Oracle RMS. Its composite primary key of ITEM + LOC + LOC_TYPE is the pairing that almost every inventory and purchasing query joins on. The table carries far more than just a linking record — it holds the operational configuration of the item at that specific location.

The Ranging Workflow — What Happens Step by Step#

ITEM_LOC Status Values#

The STATUS column on ITEM_LOC controls what operations are permitted for an item at that location. It is independent of ITEM_MASTER.STATUS — an Active item can have an Inactive ranging record at a specific location, and that is a perfectly valid configuration.

AUTO_APPR_IND — The Ordering Gate#

AUTO_APPR_IND on ITEM_LOC controls whether purchase orders for this item at this location are automatically approved or require manual buyer sign-off.

When AUTO_APPR_IND = 'Y', replenishment-generated purchase orders for this item-location combination are created in Approved status and flow directly through to the supplier. When AUTO_APPR_IND = 'N', the PO is created in Worksheet status and sits in a buyer's approval queue until manually approved.

Ranging Rules — Ranging Multiple Locations at Once#

Manually creating individual ITEM_LOC rows for each location is only practical for small ranges. When an item needs to go live across dozens or hundreds of stores simultaneously, Oracle RMS provides ranging rules — configurable criteria that define which locations an item should be ranged to, with what default attributes.

Ranging rules operate on the organisational hierarchy. A buyer defines a rule that says: range this item to all Active stores in District 5 with STORE_ORD_MULT = 6 and AUTO_APPR_IND = 'Y'. The RMS application — or a batch process — then executes the rule and creates the ITEM_LOC rows for every qualifying location.

De-ranging — Pre-conditions and Consequences#

De-ranging removes an item from a location — it deletes or inactivates the ITEM_LOC row and its companion ITEM_LOC_SOH. Once de-ranged, the item no longer exists at that location from RMS's perspective: no ordering, no receiving, no selling, no stock counts.

De-ranging cannot happen arbitrarily. Oracle RMS enforces pre-conditions that must all be satisfied before a location record can be removed:

The recommended sequence before de-ranging is always:

1. Set ITEM_LOC.STATUS = 'I' to stop new orders
2. Let existing stock sell through to zero
3. Cancel or receive any open purchase orders
4. Complete or cancel any pending transfers
5. Complete any open stock counts
6. Confirm ITEM_LOC_SOH.STOCK_ON_HAND = 0
7. Process the de-ranging

ITEM_LOC_SOH — The Companion Record#

ITEM_LOC_SOH is automatically created by Oracle RMS when an item is ranged to a location. It is the stock position record for that item at that location and is the primary source for all inventory queries.

Key Tables — Quick Reference#

Practical SQL Examples#

1. Full ranging status for an item across all locations

-- Complete ranging snapshot for one item — all stores and warehouses
SELECT il.loc,
il.loc_type,
CASE il.loc_type
WHEN 'S' THEN s.store_name
WHEN 'W' THEN w.wh_name
END                             loc_name,
il.status                       ranging_status,
il.unit_retail,
il.primary_supp,
il.store_ord_mult,
il.auto_appr_ind,
ils.stock_on_hand,
ils.stock_on_order,
ils.in_transit_qty,
ils.av_cost,
il.capture_date
FROM   item_loc      il
LEFT JOIN store      s   ON  s.store    = il.loc AND il.loc_type = 'S'
LEFT JOIN wh         w   ON  w.wh       = il.loc AND il.loc_type = 'W'
LEFT JOIN item_loc_soh ils ON  ils.item     = il.item
AND ils.loc      = il.loc
AND ils.loc_type = il.loc_type
WHERE  il.item = :v_item
ORDER BY il.loc_type, loc_name;

2. Items ranged to a store but with zero SOH and no open orders — de-ranging candidates

-- Items safe to de-range from a specific location
-- Zero SOH, no open PO lines, no pending transfers
SELECT il.item,
im.item_desc,
il.status,
ils.stock_on_hand,
ils.stock_on_order,
ils.in_transit_qty,
il.capture_date
FROM   item_loc     il
JOIN   item_master  im  ON im.item     = il.item
JOIN   item_loc_soh ils ON  ils.item     = il.item
AND ils.loc      = il.loc
AND ils.loc_type = il.loc_type
WHERE  il.loc      = :v_loc
AND  il.loc_type = :v_loc_type
AND  il.status   = 'I'               -- already set to Inactive
AND  ils.stock_on_hand   = 0
AND  ils.stock_on_order  = 0
AND  ils.in_transit_qty  = 0
AND  NOT EXISTS (                    -- no open transfer lines
SELECT 1 FROM tsfdetail td
JOIN   tsfhead th ON th.tsf_no = td.tsf_no
WHERE  (td.from_loc = il.loc OR td.to_loc = il.loc)
AND   td.item     = il.item
AND   th.status NOT IN ('C', 'R')  -- not Closed or Received
)
ORDER BY im.item_desc;

3. Unranged Active items — items with no location records at all

-- Active transaction-level items that have never been ranged anywhere
-- Common data quality issue after bulk item creation without a ranging step
SELECT im.item,
im.item_desc,
im.dept,
dp.dept_name,
im.status,
im.item_level,
im.tran_level
FROM   item_master  im
JOIN   deps         dp ON dp.dept = im.dept
WHERE  im.status     = 'A'
AND  im.item_level = im.tran_level   -- transaction items only
AND  NOT EXISTS (
SELECT 1 FROM item_loc il
WHERE  il.item = im.item
)
ORDER BY dp.dept_name, im.item_desc;

4. Active items ranged to stores but missing from the supplying warehouse

-- Items ranged to stores but not ranged to their store's DEFAULT_WH
-- These items cannot be replenished because the warehouse has no ranging record
SELECT il.item,
im.item_desc,
il.loc                          store_no,
s.store_name,
s.default_wh,
w.wh_name                       default_wh_name
FROM   item_loc     il
JOIN   item_master  im  ON im.item   = il.item
JOIN   store        s   ON s.store   = il.loc
JOIN   wh           w   ON w.wh      = s.default_wh
WHERE  il.loc_type  = 'S'
AND  il.status    = 'A'
AND  im.status    = 'A'
AND  s.default_wh IS NOT NULL
AND  NOT EXISTS (
SELECT 1 FROM item_loc il_wh
WHERE  il_wh.item     = il.item
AND  il_wh.loc      = s.default_wh
AND  il_wh.loc_type = 'W'
)
ORDER BY il.item, s.store_name;

5. PL/SQL — validate de-ranging pre-conditions for an item at a location

-- Returns 'OK' or a description of the blocking condition
-- Run before any programmatic de-ranging to avoid partial failures
FUNCTION can_derange (
p_item     IN item_loc.item%TYPE,
p_loc      IN item_loc.loc%TYPE,
p_loc_type IN item_loc.loc_type%TYPE
) RETURN VARCHAR2 IS
l_soh          NUMBER;
l_on_order     NUMBER;
l_in_transit   NUMBER;
l_open_tsf     PLS_INTEGER;
l_open_count   PLS_INTEGER;
BEGIN
-- 1. Check SOH, on-order, and in-transit quantities
SELECT NVL(stock_on_hand,  0),
NVL(stock_on_order, 0),
NVL(in_transit_qty, 0)
INTO   l_soh, l_on_order, l_in_transit
FROM   item_loc_soh
WHERE  item     = p_item
AND  loc      = p_loc
AND  loc_type = p_loc_type;
IF l_soh > 0 THEN
RETURN 'BLOCKED: STOCK_ON_HAND = ' || l_soh || ' — must be zero';
END IF;
IF l_on_order > 0 THEN
RETURN 'BLOCKED: STOCK_ON_ORDER = ' || l_on_order
|| ' — close or receive open POs first';
END IF;
IF l_in_transit > 0 THEN
RETURN 'BLOCKED: IN_TRANSIT_QTY = ' || l_in_transit
|| ' — receive pending transfers first';
END IF;
-- 2. No open transfer lines
SELECT COUNT(1) INTO l_open_tsf
FROM   tsfdetail td
JOIN   tsfhead   th ON th.tsf_no = td.tsf_no
WHERE  td.item      = p_item
AND  (td.from_loc = p_loc OR td.to_loc = p_loc)
AND  th.status NOT IN ('C', 'R');
IF l_open_tsf > 0 THEN
RETURN 'BLOCKED: ' || l_open_tsf
|| ' open transfer line(s) — complete or cancel first';
END IF;
-- 3. No active stock count
SELECT COUNT(1) INTO l_open_count
FROM   scount_detail sc
JOIN   scount_head   sh ON sh.count_id = sc.count_id
WHERE  sc.item     = p_item
AND  sc.loc      = p_loc
AND  sc.loc_type = p_loc_type
AND  sh.status NOT IN ('C');
IF l_open_count > 0 THEN
RETURN 'BLOCKED: open stock count — complete or cancel first';
END IF;
RETURN 'OK';
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'NOT_RANGED: no ITEM_LOC_SOH record found for this location';
WHEN OTHERS THEN
RETURN 'ERROR: ' || SQLERRM;
END can_derange;

Common Gotchas for Developers#

• !

  Ranging an item to a store but not to its DEFAULT_WH. When a store has a DEFAULT_WH configured on the STORE table, replenishment orders for items at that store are raised to that warehouse. If the item is not also ranged to the warehouse (no ITEM_LOC row with LOC_TYPE = 'W'), the replenishment batch cannot create an ORDLOC line for the warehouse leg. The store will never receive replenishment stock even if its SOH drops below the reorder point. Always range to both store and supplying warehouse simultaneously.

• !

  Setting STORE_ORD_MULT to zero or NULL. The STORE_ORD_MULT column defines the minimum order quantity for replenishment. A zero or NULL value causes the replenishment batch to either skip the item entirely or generate a zero-quantity order — both of which look like silent success but result in no stock being ordered. Default it to at least ITEM_SUPPLIER.INNER_PACK_SIZE for every ranged location.

• !

  Querying ITEM_LOC without LOC_TYPE and getting double results.

  Store number 1000 and Warehouse number 1000 are different locations that share the same LOC value. Querying WHERE item = :x AND loc = 1000 without AND loc_type = 'S' returns rows for both. This is the most common silent data bug in RMS reporting queries — it inflates item counts, SOH totals, and order quantities by appearing to add a duplicate location.

• !

  Assuming ITEM_LOC_SOH always exists when ITEM_LOC does. In a standard RMS instance, creating an ITEM_LOC row triggers the automatic creation of the companion ITEM_LOC_SOH row. However, in some direct SQL migration scenarios or older implementations, the SOH row may be missing. Any query that does an INNER JOIN between ITEM_LOC and ITEM_LOC_SOH will silently drop those locations from results. Use LEFT JOIN and check for NULL SOH values in reporting logic.

• !

  De-ranging without first setting STATUS = 'I' to drain stock. Attempting to de-range an item that still has open orders or positive SOH will fail. The correct sequence is always: Inactive → run down stock → clear orders → de-range. Skipping directly to de-range on an Active item with stock produces a hard error in the UI and, in direct SQL scenarios, a constraint violation or partial delete that leaves orphaned ITEM_LOC_SOH records without their parent ITEM_LOC row.

• !

  Ranging to a Virtual Warehouse and then wondering why replenishment fails.

  Virtual Warehouses (WH_TYPE = 'VA') are logical subdivisions of a Physical Warehouse. In most RMS configurations, purchase orders are raised against Physical Warehouses. Ranging an item exclusively to a Virtual WH and setting the store's DEFAULT_WH to that Virtual WH means replenishment order generation will fail — or succeed but create PO lines that the supplier's EDI system cannot process against a logical warehouse that does not physically exist.

• !

  Using LOCAL_ITEM_DESC for all locations instead of ITEM_DESC.

  ITEM_LOC.LOCAL_ITEM_DESC is intended for location-specific description overrides — a different product name used by a particular store. Some integrations incorrectly populate this field for every location as a shortcut to avoid the ITEM_DESC table join. This creates maintenance overhead: whenever the master description changes in ITEM_DESC, the LOCAL_ITEM_DESC overrides across hundreds of location records must also be updated manually.

Key Takeaways#