Oracle RMS Item Supplier Setup

How Item-Supplier Works in RMS#

In Oracle RMS, the connection between an item and its supplier is not implicit — it is an explicit record in the ITEM_SUPPLIER table. This table is the commercial agreement between a specific item and a specific supplier. It answers the questions that drive all purchasing logic: how much does this item cost from this supplier, how long does it take to arrive, and in what pack structure does it come?

An item can have multiple suppliers. This models real-world scenarios — a retailer may dual-source a product for resilience, or may source the same item from different trading arms of the same manufacturer. Each item-supplier pair gets its own row in ITEM_SUPPLIER, and one of those rows must be designated as the primary — the default source used by automated replenishment and purchase order defaults.

The item-supplier relationship also extends to a second table, ITEM_SUPP_COUNTRY, which adds the dimension of country of origin. The same supplier may manufacture the same item in multiple countries at different FOB costs. This three-level structure — item, supplier, country — forms the base of the entire Oracle Retail landed cost calculation chain.

The ITEM_SUPPLIER Table — Deep Dive#

The ITEM_SUPPLIER table has a composite primary key of ITEM + SUPPLIER. It is one of the most operationally active tables in Oracle RMS — read by replenishment batches, purchase order creation, cost calculation, and receiving validation.

The Primary Supplier Pattern#

The primary supplier is the most important concept in item-supplier setup. It is the designated default source for an item — the supplier used by automated replenishment when raising stock orders, the supplier that defaults onto new manual purchase orders, and the supplier whose cost is used in item cost lookups when no specific supplier is specified.

The primary supplier is flagged by setting PRIMARY_SUPP_IND = 'Y' on the relevant ITEM_SUPPLIER row. The business rule is absolute: exactly one row must carry PRIMARY_SUPP_IND = 'Y' per item at all times. Zero primaries means replenishment cannot determine where to order from. Two primaries means any query selecting WHERE PRIMARY_SUPP_IND = 'Y' returns multiple rows, causing TOO_MANY_ROWS exceptions in batch processing.

Oracle RMS enforces this constraint at the application layer through the Forms UI. It does not always enforce it as a database-level unique constraint. This means direct SQL inserts and updates that bypass the application layer can create invalid states that appear fine until a batch job runs.

The primary supplier pattern also appears in two related contexts. At the country-of-origin level, ITEM_SUPP_COUNTRY has its own PRIMARY_SUPP_IND = 'Y' to indicate the default sourcing country for a given item-supplier pair. At the location level, ITEM_LOC.PRIMARY_SUPP holds the primary supplier for replenishment at that specific store or warehouse — which can differ from the global primary supplier on ITEM_SUPPLIER.

Unit Cost — What It Represents and Where It Flows#

ITEM_SUPPLIER.UNIT_COST is the agreed trading cost between the retailer and that supplier for one unit of the item. It is expressed in the supplier's currency (from SUPS.CURRENCY_CODE) and represents the price at which the item would appear on a purchase order raised to that supplier.

This is distinct from the FOB cost in ITEM_SUPP_COUNTRY.UNIT_COST, which is the cost at the point of origin before freight and duty are added. For domestic suppliers, these two costs are often the same. For international sourcing, they are different — the ITEM_SUPP_COUNTRY cost is the departure price; ITEM_SUPPLIER cost reflects the delivered-to-port or agreed trading price.

The unit cost flows into RMS operations in the following ways:

Lead Times — The Hierarchy of Defaults#

Lead time in Oracle RMS is the number of days between placing a purchase order and the stock arriving at the receiving location. It drives the replenishment engine's calculation of when to order — ordering too late relative to the lead time results in stockouts; ordering too early results in excess inventory.

Lead time is not a single value. RMS uses a three-level default hierarchy, reading the most specific value available:

Pack Sizes — Inner Pack, Outer Case, and Rounding Flags#

Pack sizes are among the most practically important fields on ITEM_SUPPLIER. They define how a supplier ships an item — in what unit quantities — and they directly control how Oracle RMS rounds purchase order quantities. Wrong pack sizes mean POs are raised in quantities the supplier cannot fulfil, leading to short deliveries, receiving discrepancies, and replenishment inaccuracies.

The two rounding flags control how the replenishment engine snaps calculated order quantities to pack boundaries:

ITEM_SUPP_COUNTRY — FOB Costing by Country of Origin#

ITEM_SUPP_COUNTRY extends the item-supplier relationship with a third dimension — the country from which the item originates. The composite primary key is ITEM + SUPPLIER + ORIGIN_COUNTRY_ID.

This table matters most for retailers who import goods internationally, because cost and duty rates vary by origin country. The same item from the same supplier might cost less when manufactured in Bangladesh than in India, but attract a higher import duty from the former. The UNIT_COST on this table is the FOB cost — the price at the port of origin, before freight and insurance. This is the starting point for Oracle Retail Trade Management's landed cost calculation chain.

SUPP_DISCONTINUE_DATE — End-Dating a Supplier Relationship#

The SUPP_DISCONTINUE_DATE column on ITEM_SUPPLIER marks the date after which a supplier no longer supplies a specific item. It is a soft end-date — it does not delete the record, but it signals to RMS batch processes that new purchase orders should not be raised for this item-supplier combination beyond this date.

Setting this date correctly is critical for two scenarios:

Range-end management — When an item is being discontinued from a supplier (perhaps due to a supplier change or end of season), setting SUPP_DISCONTINUE_DATE to the appropriate date ensures that automated replenishment stops raising new POs to that supplier after that date, even if stock on hand drops below the reorder point.

Supplier replacement — When switching from Supplier A to Supplier B for an item, the workflow is: (1) set SUPP_DISCONTINUE_DATE on the old supplier's row, (2) create the new ITEM_SUPPLIER row for Supplier B, (3) transfer primary supplier flag to Supplier B. Outstanding POs to Supplier A are honoured until received; new replenishment orders go to Supplier B.

Key Tables — Quick Reference#

Practical SQL Examples#

1. All suppliers for an item — cost, lead time and pack structure

-- Full item-supplier setup audit for a single item
SELECT im.item,
im.item_desc,
s.supplier,
s.supp_name,
s.currency_code,
is2.unit_cost,
is2.lead_time,
NVL(is2.lead_time, s.lead_time)   effective_lead_time,
is2.supp_pack_size,
is2.inner_pack_size,
is2.primary_supp_ind,
is2.primary_country_id,
is2.round_to_case,
is2.round_to_inner_pkg,
is2.supp_discontinue_date
FROM   item_master   im
JOIN   item_supplier is2 ON is2.item     = im.item
JOIN   sups          s   ON s.supplier   = is2.supplier
WHERE  im.item     = :v_item
ORDER BY is2.primary_supp_ind DESC, s.supp_name;

2. Items with missing or zero unit cost on the primary supplier

-- Data quality check — Active items where primary supplier cost is 0 or NULL
-- A zero cost will result in zero-cost POs and incorrect stock valuation
SELECT im.item,
im.item_desc,
im.dept,
dp.dept_name,
s.supplier,
s.supp_name,
is2.unit_cost,
s.currency_code
FROM   item_master   im
JOIN   item_supplier is2 ON  is2.item             = im.item
AND is2.primary_supp_ind  = 'Y'
JOIN   sups          s   ON  s.supplier            = is2.supplier
JOIN   deps          dp  ON  dp.dept               = im.dept
WHERE  im.status = 'A'
AND  (is2.unit_cost IS NULL OR is2.unit_cost = 0)
ORDER BY dp.dept_name, im.item;

3. FOB cost vs trading cost comparison — spot sourcing savings

-- Compare FOB origin cost vs agreed trading cost across all sourcing countries
-- Useful for landed cost analysis and supplier negotiation reviews
SELECT is2.item,
im.item_desc,
s.supp_name,
is2.unit_cost                       trading_cost,
s.currency_code,
isc.origin_country_id,
isc.unit_cost                       fob_cost,
isc.duty_pct,
ROUND(
isc.unit_cost * (1 + NVL(isc.duty_pct, 0) / 100),
4
)                                   fob_plus_duty,
isc.primary_supp_ind               primary_origin
FROM   item_supplier      is2
JOIN   item_master        im  ON im.item     = is2.item
JOIN   sups               s   ON s.supplier  = is2.supplier
JOIN   item_supp_country  isc ON  isc.item     = is2.item
AND isc.supplier = is2.supplier
WHERE  is2.item = :v_item
ORDER BY isc.primary_supp_ind DESC, isc.origin_country_id;

4. Items where pack size creates ordering inefficiencies

-- Items where STORE_ORD_MULT on ITEM_LOC is not aligned to INNER_PACK_SIZE
-- Misaligned store order multiples cause broken-case orders suppliers cannot fill
SELECT il.item,
im.item_desc,
il.loc,
il.loc_type,
il.store_ord_mult,
is2.inner_pack_size,
is2.supp_pack_size,
CASE
WHEN MOD(il.store_ord_mult, is2.inner_pack_size) != 0
THEN 'MISALIGNED — not a multiple of inner pack'
WHEN il.store_ord_mult = 0 OR il.store_ord_mult IS NULL
THEN 'ZERO / NULL — replenishment will skip'
ELSE 'OK'
END                        alignment_status
FROM   item_loc       il
JOIN   item_master    im  ON im.item             = il.item
JOIN   item_supplier  is2 ON  is2.item             = il.item
AND is2.primary_supp_ind = 'Y'
WHERE  il.loc_type = 'S'
AND  il.status   = 'A'
AND  (
MOD(il.store_ord_mult, NULLIF(is2.inner_pack_size, 0)) != 0
OR il.store_ord_mult = 0
OR il.store_ord_mult IS NULL
)
ORDER BY il.item, il.loc;

5. PL/SQL — Safe primary supplier change procedure

-- Safely transfers primary supplier flag from one supplier to another
-- Wraps both updates in a single transaction to prevent intermediate states
-- where an item has either zero or two primary suppliers
PROCEDURE change_primary_supplier (
p_item         IN item_supplier.item%TYPE,
p_new_supplier IN item_supplier.supplier%TYPE
) IS
l_count PLS_INTEGER;
BEGIN
-- Validate: new supplier must have a row in ITEM_SUPPLIER
SELECT COUNT(1) INTO l_count
FROM   item_supplier
WHERE  item     = p_item
AND  supplier = p_new_supplier;
IF l_count = 0 THEN
RAISE_APPLICATION_ERROR(-20001,
'Supplier ' || p_new_supplier ||
' has no ITEM_SUPPLIER row for item ' || p_item);
END IF;
-- Step 1: Clear existing primary flag(s)
UPDATE item_supplier
SET    primary_supp_ind = 'N'
WHERE  item             = p_item
AND  primary_supp_ind = 'Y';
-- Step 2: Set new primary
UPDATE item_supplier
SET    primary_supp_ind = 'Y'
WHERE  item             = p_item
AND  supplier         = p_new_supplier;
-- Step 3: Update ITEM_LOC rows that still reference the old primary
-- Optional — remove if location-level supplier overrides are intentional
UPDATE item_loc
SET    primary_supp = p_new_supplier
WHERE  item         = p_item
AND  primary_supp != p_new_supplier;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END change_primary_supplier;

6. Discontinued supplier-item pairs still active on ITEM_LOC

-- Find locations still replenishing from a discontinued item-supplier pair
-- These will generate errors when the next replenishment batch runs
SELECT il.item,
im.item_desc,
il.loc,
il.loc_type,
il.primary_supp,
is2.supp_discontinue_date,
s.supp_name
FROM   item_loc       il
JOIN   item_master    im  ON im.item     = il.item
JOIN   item_supplier  is2 ON  is2.item     = il.item
AND is2.supplier = il.primary_supp
JOIN   sups           s   ON  s.supplier   = il.primary_supp
WHERE  il.status                = 'A'
AND  im.status                = 'A'
AND  is2.supp_discontinue_date IS NOT NULL
AND  is2.supp_discontinue_date < SYSDATE
ORDER BY is2.supp_discontinue_date, il.item;

Common Gotchas for Developers#

• !

  Two rows with PRIMARY_SUPP_IND = 'Y' for the same item. This is the single most destructive data quality issue in item-supplier setup. Any PL/SQL that does SELECT supplier INTO l_sup FROM item_supplier WHERE item = :x AND primary_supp_ind = 'Y' will throw TOO_MANY_ROWS and abort. This condition is invisible to normal browsing — items look fine in the UI until a batch job hits them.

• !

  Confusing ITEM_SUPPLIER.UNIT_COST with ITEM_SUPP_COUNTRY.UNIT_COST.

  Both tables have a UNIT_COST column. ITEM_SUPPLIER.UNIT_COST is the agreed trading cost used on purchase orders. ITEM_SUPP_COUNTRY.UNIT_COST is the FOB price at origin used in landed cost calculations. For domestic sourcing they are often the same value. For international sourcing they are always different. Using the wrong one in a costing query produces incorrect margin calculations.

• !

  Not filtering on SUPP_DISCONTINUE_DATE in ordering queries. Any custom batch or integration that selects from ITEM_SUPPLIER to generate purchase orders must include AND (supp_discontinue_date IS NULL OR supp_discontinue_date SYSDATE). Omitting this check raises POs to suppliers who have been end-dated on the item, which the RMS receiving validation will then reject.

• !

  ITEM_SUPPLIER.LEAD_TIME being NULL and falling through to the wrong default.

  If ITEM_SUPPLIER.LEAD_TIME is NULL, RMS falls back to SUPS.LEAD_TIME. If that is also NULL, it falls back to the system default — which may be a generic placeholder value from implementation that does not reflect any real supplier. Always set a realistic lead time at either the item-supplier or supplier level. Use the effective lead time query (SQL example 1 above) to find which value the system is actually using.

• !

  Setting ROUND_TO_CASE = 'Y' without also setting a non-zero SUPP_PACK_SIZE.

  If ROUND_TO_CASE = 'Y' and SUPP_PACK_SIZE is zero or NULL, the rounding calculation divides by zero. Some RMS batch versions handle this gracefully by skipping the item; others raise an arithmetic error. Always set SUPP_PACK_SIZE to at least 1 when enabling case rounding.

• !

  Pack sizes on ITEM_SUPP_COUNTRY overriding ITEM_SUPPLIER silently. ITEM_SUPP_COUNTRY has its own SUPP_PACK_SIZE and INNER_PACK_SIZE columns. In some RMS versions and configurations, these country-level pack sizes take precedence over the supplier-level pack sizes when the item is being imported from that specific country. If pack rounding behaviour appears to be ignoring the values on ITEM_SUPPLIER, check whether the country-level pack sizes on ITEM_SUPP_COUNTRY are set to different values.

• !

  Treating DEFAULT_UOM on ITEM_SUPPLIER as informational only.

  If DEFAULT_UOM differs from ITEM_MASTER.UNIT_OF_MEASURE and CONVERT_UOM_IND = 'Y', RMS will apply a UOM conversion factor on receipt. If the conversion factor is not configured in the UOM conversion table, receiving will fail with a conversion error. Always validate UOM consistency when setting up items purchased in a different unit than they are sold.

Key Takeaways#