Oracle RMS Merchandise Hierarchy

What is the Merchandise Hierarchy?

In Oracle Retail Merchandising System (RMS), the Merchandise Hierarchy is the foundational classification structure that organises every product in your retail business. Think of it as the taxonomy of your entire catalog — a pyramid with six levels, from the broadest classification at the top down to the most granular at the bottom.

The hierarchy exists at the heart of almost every functional area in RMS. Pricing rules are defined at Department level. Replenishment parameters default from the Subclass. Reporting aggregates roll up through the hierarchy. Buyer and Merchandiser responsibilities are assigned at the Department. The hierarchy is not just a way to group products — it is the governance structure for how products behave inside RMS.

The six levels of the Oracle RMS Merchandise Hierarchy are:

Company → Division → Group → Department → Class → Subclass

Every item in RMS must belong to exactly one Subclass. That single Subclass membership places the item into a Class, a Department, a Group, a Division, and ultimately under the umbrella of the Company — giving every product a precise position in the classification tree.

All Six Levels — In Depth

Now let's go through each level properly — what it represents in retail, how it behaves in Oracle RMS, and the database specifics you need as a developer.

How Items Attach to the Hierarchy

Every item in Oracle RMS is stored in the ITEM_MASTER table. Three columns on that table directly reference the bottom of the merchandise hierarchy:

-- Key hierarchy columns on ITEM_MASTER
SELECT im.item,
im.item_desc,
im.dept,          -- FK → DEPS.DEPT
im.class,         -- Part of FK → CLASS(DEPT, CLASS)
im.subclass,      -- Part of FK → SUBCLASS(DEPT, CLASS, SUBCLASS)
im.item_type,     -- ITEM, REF, PACK
im.item_level,    -- 1=Transaction Item, 2=Style, 3=SKU
im.status         -- A=Active, D=Discontinued, etc.
FROM   item_master im
WHERE  im.dept     = :v_dept
AND  im.class    = :v_class
AND  im.subclass = :v_subclass
AND  im.status   = 'A'
ORDER BY im.item;

This direct attachment means that if you know an item's DEPT, CLASS, and SUBCLASS values, you can immediately traverse the entire hierarchy upward — through Classes, Departments, Groups, Divisions — using a single join chain.

Key Database Tables — Quick Reference

Print this table out and pin it to your monitor when you start RMS development.

Practical SQL Examples

These are the queries you'll find yourself writing repeatedly as an RMS developer or integrations engineer.

1. Get full hierarchy path for a given item

-- Full hierarchy path for a single item
SELECT im.item,
im.item_desc,
d.div_name,
g.group_name,
dp.dept_name,
c.class_name,
s.sub_name
FROM   item_master  im
JOIN   subclass     s   ON  s.dept     = im.dept
AND s.class    = im.class
AND s.subclass = im.subclass
JOIN   class        c   ON  c.dept     = im.dept
AND c.class    = im.class
JOIN   deps         dp  ON  dp.dept    = im.dept
JOIN   groups       g   ON  g.group_no = dp.group_no
JOIN   division     d   ON  d.div_no   = g.div_no
WHERE  im.item = :v_item;

2. Count active items per Department/Class

-- Active item counts rolled up to Dept → Class
SELECT dp.dept,
dp.dept_name,
c.class,
c.class_name,
COUNT(im.item) AS item_count
FROM   deps         dp
JOIN   class        c   ON c.dept  = dp.dept
JOIN   item_master  im  ON im.dept  = c.dept
AND im.class = c.class
WHERE  im.status = 'A'
GROUP BY dp.dept, dp.dept_name,
c.class, c.class_name
ORDER BY dp.dept, c.class;

3. Validate that a Dept/Class/Subclass combination exists

This pattern is used heavily in integration staging validation — confirming that incoming data references a valid hierarchy combination before allowing it into RMS.

FUNCTION hier_exists (
p_dept     IN subclass.dept%TYPE,
p_class    IN subclass.class%TYPE,
p_subclass IN subclass.subclass%TYPE
) RETURN BOOLEAN IS
l_count  PLS_INTEGER := 0;
BEGIN
SELECT COUNT(1)
INTO   l_count
FROM   subclass
WHERE  dept     = p_dept
AND  class    = p_class
AND  subclass = p_subclass;
RETURN (l_count > 0);
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END hier_exists;

Common Gotchas for Developers

These are the mistakes every developer makes at least once when they first start working with the RMS hierarchy.

• !

  Querying FROM DEPARTMENT instead of DEPS. The table is DEPS. There is no DEPARTMENT table in Oracle RMS. You will get ORA-00942 immediately. Burn this into memory: DEPS.

• !

  Treating CLASS as a globally unique key. Class 5 in Dept 10 and Class 5 in Dept 20 are different classes. Always filter or join using both DEPT and CLASS together. Forgetting the DEPT when joining to the CLASS table will produce a cross-join and a massive result set.

• !

  Using GROUP as a table name in SQL. GROUP is an Oracle reserved keyword. The table is GROUPS. If you write FROM group, Oracle will try to parse it as part of a GROUP BY clause and throw a syntax error.

• !

  Assuming Buyer on DEPS always matches Buyer on DIVISION. Buyer and Merchandiser assignments can be overridden at each level. Do not assume the Division buyer is the Department buyer — always read from the correct level for your business logic.

• !

  Creating an item without a valid Subclass. RMS enforces referential integrity at the database level. An item's DEPT+CLASS+SUBCLASS must resolve to a real row in the SUBCLASS table. Integration scripts that skip this validation will fail at the database constraint level.

• !

  Forgetting that hierarchy changes are not retroactive to stock ledger history. If you move items to a new Department, historical stock ledger entries still reference the original Department. Reporting queries that don't account for this will show incorrect historical rollups.

Key Takeaways