Oracle RMS Item Master

What is the Item Master?#

The ITEM_MASTER table is the central product catalog of Oracle RMS. Every product that exists in the system — whether it is a single sellable unit, a parent style, a pack of multiple items, or an inner component — has exactly one row in ITEM_MASTER. This table is the anchor point that connects the product to the merchandise hierarchy, to its suppliers, to its locations, and ultimately to every transaction that involves it.

Unlike a flat product list, the Oracle RMS item model is structured. Items can exist at different levels within a parent-child hierarchy (a style and its SKUs, for example), they can be packs containing multiple units, and they carry rich attributes covering everything from VAT treatment to unit of measure to differentiator values like colour and size.

The primary key of ITEM_MASTER is the column ITEM — declared as VARCHAR2(25). This single fact causes more bugs in RMS integrations than almost anything else. Item numbers in Oracle RMS are always strings, never integers, and must always be treated as such in SQL, PL/SQL, and any external system.

Item Levels — Transaction, Style and SKU#

Oracle RMS supports items structured across up to three levels of a parent-child hierarchy. Two columns on ITEM_MASTER control this: ITEM_LEVEL (the position of this specific item in its hierarchy) and TRAN_LEVEL (the level at which transactions — sales, orders, transfers — actually occur).

Item Types — Regular, Simple Pack and Complex Pack#

Beyond the level structure, RMS items are classified by whether they are standalone items or pre-packed bundles. This is controlled by two indicator columns: PACK_IND and SIMPLE_PACK_IND.

ITEM_MASTER — Key Columns Deep Dive#

The ITEM_MASTER table has over 80 columns in a full Oracle RMS installation. These are the columns you will encounter most frequently as a developer.

Item Status Codes#

The STATUS column on ITEM_MASTER drives a large amount of RMS business logic. Many batch processes, validation rules, and ordering controls check item status before allowing an operation to proceed.

ITEM_DESC — Multilingual Description Storage#

Oracle RMS is built to support multiple languages. Item descriptions are stored in two places simultaneously: a short description in ITEM_MASTER.ITEM_DESC and the full, translatable description in the separate ITEM_DESC table.

The ITEM_DESC table has a composite primary key of ITEM + LANG. Every item will have at least one row in this table for the base language of the RMS instance. Multilingual deployments add additional rows for each supported language.

How Items Attach to the Merchandise Hierarchy#

Every item in ITEM_MASTER must belong to a valid DEPT + CLASS + SUBCLASS combination. These three columns form a composite foreign key reference to the bottom of the merchandise hierarchy. This attachment:

• Determines the item's VAT region defaults (from DEPS.VAT_REGION)
• Places the item under a Buyer and Merchandiser's responsibility
• Controls which pricing rules and markdown calendars apply
• Drives all reporting rollups from item level up through the hierarchy

Key Database Tables — Quick Reference#

Practical SQL Examples#

1. Full item profile with hierarchy path

-- Complete item record with full merchandise hierarchy rollup
SELECT im.item,
id.item_desc,
im.item_level,
im.tran_level,
im.status,
im.pack_ind,
im.unit_of_measure,
d.div_name,
g.group_name,
dp.dept_name,
c.class_name,
s.sub_name
FROM   item_master  im
JOIN   item_desc    id  ON  id.item = im.item
AND id.lang = (SELECT lang FROM comphead)
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. Find all SKUs under a style parent

-- All transaction-level child items beneath a style
-- Works for both 2-level (style → SKU) and 3-level hierarchies
SELECT im.item,
id.item_desc,
im.item_level,
im.diff_1,
im.diff_2,
im.diff_type_1,
im.diff_type_2,
im.status
FROM   item_master  im
JOIN   item_desc    id  ON  id.item = im.item
AND id.lang = (SELECT lang FROM comphead)
WHERE  im.parent_item = :v_parent_item
AND  im.status      = 'A'
ORDER BY im.diff_1, im.diff_2, im.item;

3. Item status audit — counts by department

-- Item count by status within a department
-- Useful for range reviews and go-live readiness checks
SELECT dp.dept,
dp.dept_name,
im.status,
COUNT(im.item)         item_count,
COUNT(CASE WHEN im.tran_level = im.item_level THEN 1 END)
tran_level_items
FROM   deps        dp
JOIN   item_master im  ON im.dept = dp.dept
WHERE  dp.dept = :v_dept
GROUP BY dp.dept, dp.dept_name, im.status
ORDER BY im.status;

4. Pack component breakdown for a complex pack

-- Components inside a complex pack item with individual item details
SELECT ps.pack_no,
ps.item                component_item,
id.item_desc           component_desc,
ps.quantity            qty_in_pack,
cim.status             component_status,
cim.unit_of_measure
FROM   packsku      ps
JOIN   item_master  cim ON  cim.item = ps.item
JOIN   item_desc    id  ON  id.item  = ps.item
AND id.lang  = (SELECT lang FROM comphead)
WHERE  ps.pack_no = :v_pack_item
ORDER BY ps.item;

5. PL/SQL — Validate item exists, is active, and is at transaction level

-- Item validation function used in integration staging pipelines
-- Returns TRUE only if the item is active and can receive transactions
FUNCTION item_is_valid_tran (
p_item IN item_master.item%TYPE
) RETURN BOOLEAN IS
l_item_level   item_master.item_level%TYPE;
l_tran_level   item_master.tran_level%TYPE;
l_status       item_master.status%TYPE;
BEGIN
SELECT item_level, tran_level, status
INTO   l_item_level, l_tran_level, l_status
FROM   item_master
WHERE  item = p_item;
-- Item must be active AND at transaction level
RETURN (l_status = 'A' AND l_item_level = l_tran_level);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
WHEN OTHERS THEN
RETURN FALSE;
END item_is_valid_tran;

Common Gotchas for Developers#

• !

  Treating ITEM as a number in SQL or PL/SQL. The ITEM column is VARCHAR2(25). Writing WHERE item = 100050 without quotes triggers an implicit conversion that suppresses the index on the ITEM column and causes a full table scan on a table that may have millions of rows. Always quote item numbers: WHERE item = '100050'.

• !

  Ordering or transferring a style-level item directly.

  Items where ITEM_LEVEL TRAN_LEVEL are parent/style items and cannot appear on transaction documents — purchase orders, transfers, sales orders. Only items where ITEM_LEVEL = TRAN_LEVEL can be transacted. Integration scripts that do not check this will receive a validation error from the RMS API layer.

• !

  Querying ITEM_MASTER.ITEM_DESC without joining ITEM_DESC. The ITEM_DESC column on ITEM_MASTER is a convenience field and may not always be kept in sync in every RMS implementation. The authoritative, multilingual description is in the ITEM_DESC table. Always join ITEM_DESC when displaying item names in reports or integrations.

• !

  Checking STATUS = 'A' only and missing Inactive items with stock.

  Inactive items (STATUS = 'I') can still have positive STOCK_ON_HAND in ITEM_LOC_SOH. Any inventory reconciliation, stock audit, or on-hand reporting query that hard-filters for only Active status will produce incorrect totals if Inactive items hold stock.

• !

  Confusing ITEM_MASTER.ITEM_DESC with the item being ranged.

  An item existing in ITEM_MASTER with STATUS = 'A' does not mean it is available at any location. An item must be ranged to a location — a row must exist in ITEM_LOC — before it can be ordered, received, or sold at that location.

• !

  Pack SOH is not the sum of component SOH. Pack items have their own independent stock on hand in ITEM_LOC_SOH. If you receive a pack item, the pack SOH increases — not the component item SOH. The component SOH increases only when the pack is broken down. Queries that sum all SOH without accounting for pack relationships will double-count inventory.

• !

  Using PARENT_ITEM to traverse beyond two levels without recursion.

  PARENT_ITEM points to an item's immediate parent only. In a three-level hierarchy, a Level 3 item's PARENT_ITEM is a Level 2 item, whose own PARENT_ITEM is the Level 1 style. A single-hop join on PARENT_ITEM will not reach the Level 1 style from a Level 3 SKU. Use a hierarchical query (CONNECT BY PRIOR) or two explicit joins when you need to reach the top of a multi-level item tree.

Key Takeaways#