Oracle RMS Item Creation Workflow

Overview — The Item Creation Pipeline#

In Oracle RMS, a "created item" is not simply a row in a table. It is a chain of related records across multiple tables, each unlocking a capability the item needs to participate in retail operations — to be described, to be ordered, to hold stock, and finally to be sold at a location.

The full creation pipeline has seven distinct steps. Some steps are always mandatory. Some are conditional. One step — ranging — is the final gate that makes an item commercially operational at a specific store or warehouse.

Prerequisites — What Must Exist Before You Start#

Before a single item can be created, the following foundation data must already exist and be in Active status. Attempting to create an item against missing foundation data will either fail immediately with an integrity constraint error or silently produce an incomplete record that fails downstream.

-- Validate that a dept/class/subclass combination exists before item creation
SELECT s.dept, s.class, s.subclass,
dp.dept_name, c.class_name, s.sub_name
FROM   subclass s
JOIN   class    c  ON c.dept  = s.dept AND c.class = s.class
JOIN   deps     dp ON dp.dept = s.dept
WHERE  s.dept     = :v_dept
AND  s.class    = :v_class
AND  s.subclass = :v_subclass;

Step 1 — Item Number Generation#

The item number is the primary key of ITEM_MASTER — declared as VARCHAR2(25). Every downstream table that references an item uses this number. Getting it right at the start matters because it cannot be changed once created.

Oracle RMS supports two item numbering approaches:

Auto-generated numbers — Most implementations use a database sequence (ITEM_NO_SEQ) to generate item numbers automatically. The RMS front-end calls ITEM_NO_SEQ.NEXTVAL when a new item is initiated. Numbers are typically numeric strings, often zero-padded to a fixed width (e.g. '0000100050').

Manual / externally assigned numbers — Some retailers bring item numbers from an upstream system (ERP, PLM). In this case the item number is passed in directly. The RMS import layer inserts this value as-is into ITEM_MASTER.ITEM.

Step 2 — Creating the ITEM_MASTER Record#

The ITEM_MASTER row is the root record. Everything else in the creation workflow is a child or satellite record of this row. When creating via integration, this is the first INSERT of the pipeline.

For child items in a style hierarchy (Level 2 or Level 3), two additional columns must be set at creation:

Step 3 — Item Descriptions (ITEM_DESC)#

The ITEM_DESC table stores the translatable, multilingual description for every item. At minimum, one row must exist in the base language of the RMS instance. The base language is found in COMPHEAD.LANG.

-- Insert ITEM_DESC in the base language — the minimal required description row
INSERT INTO item_desc
(item, lang, item_desc, short_desc)
SELECT :v_item,
ch.lang,
:v_item_desc,
:v_short_desc
FROM   comphead ch;

Step 4 — Item-Supplier Setup#

An item without a supplier cannot be ordered from anyone. Step 4 creates the commercial link between the item and its source of supply. This step involves two tables: ITEM_SUPPLIER (the trading agreement) and ITEM_SUPP_COUNTRY (the country of origin and FOB cost).

Step 5 — UDA Assignment (Optional)#

User Defined Attributes (UDAs) allow retailers to capture item attributes that are not covered by the standard RMS data model — things like season, brand tier, material composition, or fit type. UDAs are department-specific: a department may have mandatory UDAs that must be set before an item in that department can be activated.

There are three types of UDAs, each stored in its own table:

Step 6 — Status Activation (Candidate → Active)#

Until an item's status is 'A' (Active), it cannot appear on purchase orders, transfers, or any stock movement document. The status promotion from 'C' (Candidate) to 'A' is the approval gate that confirms the item's core setup is complete.

The activation validates — at minimum — that:

• The item has at least one ITEM_SUPPLIER row with PRIMARY_SUPP_IND = 'Y'
• At least one ITEM_DESC row exists in the base language
• All mandatory UDAs for the department are set
• For child items (Level 2/3): the parent item is also Active

The promotion itself is a simple UPDATE:

-- Promote item from Candidate to Active
-- Always validate prerequisites before running this
UPDATE item_master
SET    status = 'A'
WHERE  item   = :v_item
AND  status = 'C';

Step 7 — Ranging to Locations (ITEM_LOC)#

Ranging is the act of making an item available at a specific location. Until an ITEM_LOC row exists for an item at a store or warehouse, that item does not exist at that location from RMS's perspective — it cannot be ordered to that location, cannot receive stock there, and cannot be sold there.

Each ITEM_LOC row represents one item at one location. Creating this row also triggers the creation of a corresponding ITEM_LOC_SOH row with zero stock on hand.

When a ranging record is created, RMS also creates the companion ITEM_LOC_SOH row initialised with zero stock:

-- After ITEM_LOC insert, verify ITEM_LOC_SOH was created with zero SOH
SELECT ils.item,
ils.loc,
ils.loc_type,
ils.stock_on_hand,
ils.av_cost,
ils.unit_retail
FROM   item_loc_soh ils
WHERE  ils.item     = :v_item
AND  ils.loc      = :v_loc
AND  ils.loc_type = :v_loc_type;

Integration-Based Item Creation#

When creating items in bulk through an external integration — from an ERP, a PLM system, or a migration — the direct table-insert approach is replaced by a staging-table pipeline. This is the standard Oracle Retail integration architecture: data lands in staging, is validated, and is then promoted into the live tables by a batch process.

The relevant staging tables for item creation are:

Key Tables — Quick Reference#

Practical SQL Examples#

1. Pre-activation validation — check all prerequisites for a Candidate item

-- Full prerequisite check before activating a Candidate item
-- Returns one row per check with PASS/FAIL status
SELECT 'Hierarchy exists'                         check_name,
CASE WHEN s.dept IS NOT NULL THEN 'PASS' ELSE 'FAIL' END AS result
FROM   item_master im
LEFT JOIN subclass s ON  s.dept     = im.dept
AND s.class    = im.class
AND s.subclass = im.subclass
WHERE  im.item = :v_item
UNION ALL
SELECT 'Primary supplier exists'                  check_name,
CASE WHEN is2.supplier IS NOT NULL THEN 'PASS' ELSE 'FAIL' END
FROM   item_master im
LEFT JOIN item_supplier is2 ON  is2.item             = im.item
AND is2.primary_supp_ind = 'Y'
WHERE  im.item = :v_item
UNION ALL
SELECT 'Base language description exists'         check_name,
CASE WHEN id.item IS NOT NULL THEN 'PASS' ELSE 'FAIL' END
FROM   item_master im
LEFT JOIN item_desc id ON  id.item = im.item
AND id.lang = (SELECT lang FROM comphead)
WHERE  im.item = :v_item
UNION ALL
SELECT 'Item is in Candidate status'              check_name,
CASE WHEN im.status = 'C' THEN 'PASS'
WHEN im.status = 'A' THEN 'ALREADY ACTIVE'
ELSE 'FAIL' END
FROM   item_master im
WHERE  im.item = :v_item;

2. Check ranging completeness — which locations has an item been ranged to

-- Ranging status across all stores and warehouses for an item
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,
ils.stock_on_hand
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;

3. Find Candidate items with missing supplier (stuck in pipeline)

-- Candidate items that cannot be activated — missing primary supplier
SELECT im.item,
id.item_desc,
im.dept,
dp.dept_name,
im.status,
im.create_datetime
FROM   item_master  im
LEFT JOIN item_desc id  ON  id.item = im.item
AND id.lang = (SELECT lang FROM comphead)
LEFT JOIN deps      dp  ON  dp.dept = im.dept
WHERE  im.status = 'C'
AND  NOT EXISTS (
SELECT 1
FROM   item_supplier is2
WHERE  is2.item             = im.item
AND  is2.primary_supp_ind = 'Y'
)
ORDER BY im.create_datetime DESC;

4. Items Active but not ranged to any location

-- Active items with zero ranging records
-- Common data quality issue after bulk activation without ranging
SELECT im.item,
id.item_desc,
im.dept,
dp.dept_name
FROM   item_master  im
LEFT JOIN item_desc id  ON  id.item = im.item
AND id.lang = (SELECT lang FROM comphead)
LEFT 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 im.dept, im.item;

5. PL/SQL — Full item creation validation procedure

-- Validate all item creation prerequisites and return an error list
-- Designed for integration pipelines — call before submitting to staging batch
PROCEDURE validate_item_creation (
p_item     IN  item_master.item%TYPE,
p_dept     IN  item_master.dept%TYPE,
p_class    IN  item_master.class%TYPE,
p_subclass IN  item_master.subclass%TYPE,
p_supplier IN  item_supplier.supplier%TYPE,
p_errors   OUT VARCHAR2
) IS
l_count    PLS_INTEGER;
l_errors   VARCHAR2(4000) := NULL;
BEGIN
-- 1. Item number must not already exist
SELECT COUNT(1) INTO l_count
FROM   item_master WHERE item = p_item;
IF l_count > 0 THEN
l_errors := l_errors || 'ITEM_EXISTS; ';
END IF;
-- 2. Dept/Class/Subclass combination must be valid
SELECT COUNT(1) INTO l_count
FROM   subclass
WHERE  dept     = p_dept
AND  class    = p_class
AND  subclass = p_subclass;
IF l_count = 0 THEN
l_errors := l_errors || 'INVALID_HIERARCHY; ';
END IF;
-- 3. Supplier must exist and be active
SELECT COUNT(1) INTO l_count
FROM   sups
WHERE  supplier   = p_supplier
AND  sup_status = 'A';
IF l_count = 0 THEN
l_errors := l_errors || 'INVALID_SUPPLIER; ';
END IF;
p_errors := NVL(RTRIM(l_errors, '; '), 'OK');
END validate_item_creation;

Common Gotchas for Developers#

• !

  Ranging an item before activating it. An item with STATUS = 'C' (Candidate) can technically receive an ITEM_LOC row — the database will not always prevent it. But RMS batches that process ranged items (replenishment, price change, transfer) check item status before processing. A Candidate item at a live location will cause silent skips or errors across multiple batch jobs. Always activate before ranging.

• !

  Missing ITEM_DESC base language row. The RMS UI looks up item names by joining ITEM_DESC on the base language code from COMPHEAD. If the ITEM_DESC row is missing or has the wrong LANG value, the item appears in the system with a blank description. It looks like a data error, but the item itself is fine — the description simply wasn't written to the right table with the right language key.

• !

  Ranging to a Virtual Warehouse instead of a Physical one. Virtual warehouses (WH_TYPE = 'VA') are logical partitions within a Physical warehouse. In most RMS configurations, replenishment orders are raised to Physical warehouses, not Virtual ones. Ranging an item to a Virtual WH and then trying to raise a PO to that WH will fail at the PO validation stage with a warehouse type mismatch error.

• !

  Duplicate PRIMARY_SUPP_IND = 'Y' rows in ITEM_SUPPLIER. If an integration INSERT creates two rows with PRIMARY_SUPP_IND = 'Y' for the same item, replenishment batches that select the primary supplier will return two rows and fail with a TOO_MANY_ROWS exception. RMS enforces this constraint at the application layer in the UI, but not always at the database level via a constraint — raw SQL inserts bypass the check silently.

• !

  Setting TRAN_LEVEL incorrectly for a style parent. For a Level 1 style item whose children are the transaction units, the correct values are ITEM_LEVEL = 1 and TRAN_LEVEL = 2. Setting TRAN_LEVEL = 1 on a style tells RMS this item is itself the transaction unit — you can then technically place it on a PO. This creates order lines against an item that has no stock and no SKU children, producing receiving and inventory calculation failures.

• !

  Not setting STORE_ORD_MULT correctly on ITEM_LOC. The STORE_ORD_MULT column on ITEM_LOC defines the minimum order multiple for store replenishment at that location. If it is set to zero or left NULL, automated replenishment batches will calculate a zero-quantity order and skip the item entirely. Set it to ITEM_SUPPLIER.INNER_PACK_SIZE at minimum.

• !

  ITEM_LOC_SOH not being queried after ranging, only ITEM_LOC.

  When ranging occurs, RMS creates both the ITEM_LOC row and the companion ITEM_LOC_SOH row. Integration scripts that check ranging status by querying only ITEM_LOC are looking at the ranging configuration. To confirm the item is fully operational and carries a stock position (even a zero one), always verify the ITEM_LOC_SOH row also exists.

Key Takeaways#