The Oracle RMS Organizational Hierarchy

What is the Organizational Hierarchy?#

In Oracle RMS, every product that exists in the system must be tied to a location before it can be bought, sold, transferred, or counted. The Organizational Hierarchy is the structure that defines all those locations — from the broadest grouping (Chain) down to the individual Store, plus the separate concept of Warehouses.

Unlike the Merchandise Hierarchy — which classifies products — the Organizational Hierarchy classifies where business happens. The two hierarchies intersect at the item-location level: when an item is ranged to a store or warehouse, a record is created linking the item's merchandise hierarchy position to a location in the organizational hierarchy.

The Oracle RMS Organizational Hierarchy has two distinct tracks:

The Store Track follows a five-level chain: Chain → Area → Region → District → Store. This represents your physical retail footprint — every store belongs to a District, which rolls up through management levels to the top.

The Warehouse Track sits alongside the store hierarchy. Warehouses are not part of the Chain-to-Store chain — they have their own table (WH) and their own type system (Physical, Virtual, Finisher). But in day-to-day RMS operations, stores and warehouses are treated identically as locations using the same LOC + LOC_TYPE pattern throughout the schema.

The Store Hierarchy — Chain to Store#

The store side of the Organizational Hierarchy is a strict five-level parent-child chain. Every store must belong to a District. Every District belongs to a Region. Every Region to an Area. Every Area to a Chain. Skipping a level is not possible — the database enforces this via foreign key constraints.

Warehouses — Physical, Virtual and Finisher#

Warehouses in Oracle RMS are all stored in a single table — WH. The WH_TYPE column discriminates between the three fundamental warehouse types. Understanding the difference between a Physical and a Virtual warehouse is essential for any developer working on inventory, ordering, or replenishment logic.

The LOC + LOC_TYPE Pattern — Most Important Concept#

This is the single most important architectural pattern in Oracle RMS for developers to understand. Throughout the entire RMS schema, whenever a table needs to store a reference to a location — whether a store or a warehouse — it does so using two columns together: LOC (the location number) and LOC_TYPE (a code indicating what kind of location it is).

This pattern appears on dozens of tables — ITEM_LOC, ITEM_LOC_SOH, ORDLOC, TSFHEAD, STKLEDGR, and many more. Every time you query these tables, you must filter by both LOC and LOC_TYPE together, or you risk pulling records from the wrong location type.

The valid LOC_TYPE values you will encounter are:

Stores vs Warehouses — Key Differences in RMS#

From a database perspective, stores and warehouses are both just "locations" in RMS. But they behave very differently in business logic.

Key Database Tables — Quick Reference#

Practical SQL Examples#

1. Full store hierarchy path — store to chain

-- Full store hierarchy rollup for a given store
SELECT s.store,
s.store_name,
s.store_type,
s.status,
d.district_name,
r.region_name,
a.area_name,
c.chain_name,
s.default_wh,
w.wh_name         default_wh_name
FROM   store    s
JOIN   district d  ON d.district_no = s.district_no
JOIN   region   r  ON r.region_no   = d.region_no
JOIN   area     a  ON a.area_no     = r.area_no
JOIN   chain    c  ON c.chain_no    = a.chain_no
LEFT JOIN wh    w  ON w.wh          = s.default_wh
WHERE  s.store = :v_store;

2. Stock on hand for an item — across all stores and warehouses

-- Item SOH across all locations (stores + warehouses combined)
-- Uses UNION to join both location types with their names
SELECT 'STORE'          loc_type_desc,
s.store          loc_no,
s.store_name     loc_name,
ils.stock_on_hand,
ils.av_cost,
ils.unit_retail
FROM   item_loc_soh  ils
JOIN   store         s   ON s.store = ils.loc
WHERE  ils.item     = :v_item
AND  ils.loc_type = 'S'
AND  ils.stock_on_hand > 0
UNION ALL
SELECT 'WAREHOUSE'      loc_type_desc,
w.wh             loc_no,
w.wh_name        loc_name,
ils.stock_on_hand,
ils.av_cost,
ils.unit_retail
FROM   item_loc_soh  ils
JOIN   wh            w   ON w.wh = ils.loc
WHERE  ils.item     = :v_item
AND  ils.loc_type = 'W'
AND  ils.stock_on_hand > 0
ORDER BY loc_type_desc, loc_no;

3. All active stores with their default warehouses in a region

-- Operational report: active stores in a region with warehouse info
SELECT s.store,
s.store_name,
s.store_type,
d.district_name,
w.wh            default_wh_no,
w.wh_name       default_wh_name,
w.wh_type
FROM   store    s
JOIN   district d  ON d.district_no = s.district_no
JOIN   region   r  ON r.region_no   = d.region_no
LEFT JOIN wh    w  ON w.wh          = s.default_wh
WHERE  r.region_no = :v_region
AND  s.status    = 'A'
ORDER BY d.district_name, s.store_name;

4. PL/SQL — Validate a location exists (any type)

-- Function to validate a LOC + LOC_TYPE combination
-- Used in integration staging validation before inserting to RMS
FUNCTION loc_exists (
p_loc      IN item_loc.loc%TYPE,
p_loc_type IN item_loc.loc_type%TYPE
) RETURN BOOLEAN IS
l_count   PLS_INTEGER := 0;
BEGIN
IF p_loc_type = 'S' THEN
SELECT COUNT(1) INTO l_count
FROM   store
WHERE  store  = p_loc
AND  status = 'A';
ELSIF p_loc_type = 'W' THEN
SELECT COUNT(1) INTO l_count
FROM   wh
WHERE  wh = p_loc;
END IF;
RETURN (l_count > 0);
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END loc_exists;

Common Gotchas for Developers#

• !

  Querying ITEM_LOC without a LOC_TYPE filter. Store 101 and Warehouse 101 are completely different locations with the same LOC value. Filtering by only LOC = 101 will return rows for both. Always include AND LOC_TYPE = 'S' or 'W' explicitly.

• !

  Treating Virtual Warehouses like Physical Warehouses in PO logic.

  Purchase Orders are received into Physical Warehouses. A Virtual Warehouse cannot directly receive a PO — stock is received at the Physical WH level and then logically allocated to Virtual WHs. Trying to create a PO to a Virtual WH will fail validation.

• !

  Forgetting that STORE and WH number ranges can overlap. There is no technical separation in RMS preventing Store 5000 and Warehouse 5000 from coexisting. The LOC_TYPE is the only discriminator. Integration scripts that strip LOC_TYPE and try to look up just by number will fail silently or return wrong data.

• !

  Assuming DEFAULT_WH on STORE is always set. In some RMS configurations, stores may not have a DEFAULT_WH assigned — especially during initial data migration or for online-only stores. Always LEFT JOIN on WH when reading DEFAULT_WH, or validate for NULL before using it in batch logic.

• !

  Missing the difference between Store STATUS values. A store with STATUS = 'I' (Inactive) still exists in the hierarchy and may still have stock on hand. STATUS = 'C' (Closed) means the store has completed closure. Logic that only checks for STATUS = 'A' will miss Inactive stores that still need inventory management.

• !

  Using LOC_TYPE = 'A' (Area) in item_loc context. LOC_TYPE values like 'A', 'R', 'D' (Area, Region, District) are used in some RMS contexts for aggregated pricing and promotion assignment — but they are not valid in ITEM_LOC or ITEM_LOC_SOH. Those tables only accept 'S', 'W', or 'E'.

Key Takeaways#