Oracle RMS Suppliers & Partners

Suppliers vs Partners — What's the Difference?

Oracle RMS draws a clear distinction between two related but separate concepts: Suppliers and Partners.

A Supplier (stored in the SUPS table) is an entity from whom the retailer purchases merchandise. This is the company that raises invoices, receives purchase orders, and ships goods. Every item in RMS that is bought from an external source must have at least one supplier attached to it.

A Partner (stored in the PARTNER table) is a broader category. All suppliers are partners, but not all partners are suppliers. A Partner can be an agent, a broker, a consolidator, a factory, or a freight forwarder — any entity that plays a role in the supply chain without necessarily being the direct seller of goods. The PARTNER_TYPE column on the PARTNER table distinguishes which kind of relationship each partner record represents.

From a developer's perspective, the tables you will interact with most frequently are SUPS, ADDR (for supplier sites), ITEM_SUPPLIER (the item-supplier link table), and ITEM_SUPP_COUNTRY (sourcing country and origin cost). Understanding how these four tables connect is the foundation of all purchasing logic in RMS.

The SUPS Table — Supplier Master Deep Dive

The SUPS table is the root of all supplier data in Oracle RMS. Every supplier that can appear on a purchase order, every supplier that can be linked to an item, every cost that is tracked back to a source — all of it starts with a row in SUPS.

Supplier Sites & Addresses (ADDR)

One of the most common sources of confusion for developers new to Oracle RMS is that the SUPS table contains no address columns. All address information — billing address, order address, remittance address — is stored in the shared ADDR table, linked to the supplier via a module-key pattern.

Partner Types — All Codes Explained

The PARTNER table stores all supply chain entities beyond the direct supplier relationship. Every partner has a PARTNER_TYPE code that defines their role. These codes appear frequently in purchasing logic, deal structures, and cost component configurations.

ITEM_SUPPLIER — Linking Items to Suppliers

The ITEM_SUPPLIER table is the bridge between the product catalog and the supplier master. It stores one row per item-supplier combination, recording the commercial terms agreed with that specific supplier for that specific item: the cost price, the lead time, and the pack structure.

An item in RMS can have multiple suppliers. This models real-world scenarios where a retailer dual-sources a product for supply chain resilience, or where multiple trading arms of the same manufacturer have separate supplier records. One of those suppliers must be flagged as the primary supplier — the default source for replenishment and cost calculation.

ITEM_SUPP_COUNTRY — Sourcing by Country of Origin

The ITEM_SUPP_COUNTRY table extends the item-supplier relationship one level deeper by adding the country of origin dimension. The same supplier may source the same item from factories in multiple countries — India, Bangladesh, China — each at a different FOB cost and with potentially different pack sizes.

This table is particularly significant for retailers doing cross-border sourcing, import cost tracking, or any work with Oracle Retail's Trade Management module. The landed cost calculation chain starts here — UNIT_COST in ITEM_SUPP_COUNTRY is the FOB cost at origin, before freight, duty, and other cost components are added.

The Primary Supplier Pattern

Throughout Oracle RMS, whenever an item needs to be linked to a single definitive supplier — for replenishment, for cost lookup, for purchase order defaulting — the system uses the primary supplier pattern. This pattern appears in three related tables, each with a PRIMARY_SUPP_IND column.

Key Tables — Quick Reference

Practical SQL Examples

1. Full supplier profile — master, address, and item count

-- Supplier profile with main address and active item count
SELECT s.supplier,
s.supp_name,
s.sup_status,
s.currency_code,
s.payment_terms,
s.lead_time             default_lead_days,
a.add_1                 address,
a.city,
a.country_id,
COUNT(DISTINCT is2.item) active_item_count
FROM   sups          s
LEFT JOIN addr       a   ON  a.module    = 'SUPP'
AND a.key_value = s.supplier
AND a.addr_type = 'S0'
LEFT JOIN item_supplier is2 ON is2.supplier = s.supplier
WHERE  s.sup_status = 'A'
GROUP BY s.supplier, s.supp_name, s.sup_status,
s.currency_code, s.payment_terms, s.lead_time,
a.add_1, a.city, a.country_id
ORDER BY s.supp_name;

2. All suppliers for an item with cost comparison

-- Compare all supplier costs for a given item
SELECT im.item,
im.item_desc,
s.supplier,
s.supp_name,
is2.unit_cost,
s.currency_code,
is2.lead_time,
is2.supp_pack_size,
is2.primary_supp_ind,
is2.primary_country_id
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
AND  s.sup_status  = 'A'
ORDER BY is2.primary_supp_ind DESC,
is2.unit_cost ASC;

3. Items sourced from a specific country via a specific supplier

-- All items sourced from India via a given supplier
SELECT im.item,
im.item_desc,
im.dept,
dp.dept_name,
isc.origin_country_id,
isc.unit_cost,
isc.duty_pct,
isc.supp_pack_size
FROM   item_supp_country  isc
JOIN   item_master        im  ON im.item     = isc.item
JOIN   deps               dp  ON dp.dept     = im.dept
WHERE  isc.supplier          = :v_supplier
AND  isc.origin_country_id = 'IND'
ORDER BY dp.dept_name, im.item_desc;

4. PL/SQL — Check and set primary supplier safely

-- Safely update primary supplier for an item.
-- Clears existing primary before setting new one.
PROCEDURE set_primary_supplier (
p_item     IN item_supplier.item%TYPE,
p_supplier IN item_supplier.supplier%TYPE
) IS
BEGIN
-- Step 1: Clear any existing primary flag for this item
UPDATE item_supplier
SET    primary_supp_ind = 'N'
WHERE  item             = p_item
AND  primary_supp_ind = 'Y';
-- Step 2: Set the new primary supplier
UPDATE item_supplier
SET    primary_supp_ind = 'Y'
WHERE  item             = p_item
AND  supplier         = p_supplier;
IF SQL%ROWCOUNT = 0 THEN
RAISE_APPLICATION_ERROR(-20001,
'Supplier ' || p_supplier ||
' not linked to item ' || p_item);
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END set_primary_supplier;

5. Active suppliers with zero active items (cleanup candidates)

-- Active suppliers with zero active item-supplier records
-- Useful for data quality audits before go-live
SELECT s.supplier,
s.supp_name,
s.currency_code,
s.lead_time
FROM   sups s
WHERE  s.sup_status = 'A'
AND  NOT EXISTS (
SELECT 1
FROM   item_supplier  is2
JOIN   item_master    im ON im.item = is2.item
WHERE  is2.supplier = s.supplier
AND  im.status    = 'A'
)
ORDER BY s.supp_name;

Common Gotchas for Developers

• !

  Querying supplier address from SUPS directly. There are no address columns on the SUPS table itself. Address data is in the ADDR table with MODULE = 'SUPP'. Developers new to RMS regularly write joins expecting columns like SUPS.ADDRESS — they don't exist.

• !

  Inserting an item-supplier row without verifying the primary supplier constraint. Oracle RMS enforces "exactly one primary supplier per item" primarily at the application layer, not always at the database constraint level. Raw SQL inserts can create two PRIMARY_SUPP_IND = 'Y' rows for the same item, which will silently corrupt replenishment and cost logic.

• !

  Confusing ITEM_SUPPLIER.UNIT_COST with ITEM_SUPP_COUNTRY.UNIT_COST. Both tables have a UNIT_COST column. The one in ITEM_SUPPLIER is the agreed cost with that supplier. The one in ITEM_SUPP_COUNTRY is the FOB cost from that specific country of origin. For import calculations, always use ITEM_SUPP_COUNTRY.

• !

  Forgetting that ITEM_SUPPLIER.ITEM is VARCHAR2, not NUMBER. The item number in Oracle RMS is stored as VARCHAR2(25) in ITEM_MASTER and all related tables. Passing a numeric value without quotes in a bind variable can cause implicit conversion and index suppression — the query will full-scan instead of using the index.

• !

  Treating PARTNER and SUPS as completely separate number spaces. For supplier-type partners, the PARTNER number and the SUPPLIER number are the same value. Integrations that generate separate sequences for PARTNER and SUPS IDs will break this assumption.

• !

  Not checking SUPP_DISCONTINUE_DATE in replenishment logic. If a supplier has a SUPP_DISCONTINUE_DATE in the past on an ITEM_SUPPLIER row, that supplier-item combination is no longer valid for new orders. Custom batch scripts that ignore this column will attempt to raise POs against discontinued supplier-item pairs.

Key Takeaways