Oracle RMS UDAs — User Defined Attributes

What Are UDAs and Why Do They Exist?#

The Oracle RMS core data model covers the attributes that every retailer needs on every item — description, department classification, unit of measure, pack indicators, supplier cost, and so on. But every retailer also needs attributes that are specific to their business, their product categories, and their reporting requirements.

A fashion retailer needs to capture season, trend direction, fabric composition, and fit type on clothing items. A grocery retailer needs shelf-life category, temperature handling class, and nutritional claims. A bookstore needs genre, reading age, and binding type. None of these are standard columns on ITEM_MASTER.

User Defined Attributes (UDAs) are Oracle RMS's answer to this. They are retailer-configured additional fields that can be attached to items, scoped by department, and made mandatory before an item can be activated. Once populated, UDA values are available for use in replenishment rules, promotion targeting, reporting, and external integrations.

The Three UDA Types#

Oracle RMS supports exactly three UDA types. Each type stores its item-level values in a separate table, has different validation rules, and serves a different kind of attribute.

The UDA Master Table#

Every UDA — regardless of type — starts with a row in the UDA table. This is the definition record for the attribute: its ID, its type, its description, and its default behaviour.

UDA_VALUES — List of Values Options#

For every UDA_TYPE = 'LV' UDA, the set of valid pick-list options is stored in UDA_VALUES. This table is the master reference for all LOV choices — both for the RMS UI drop-downs and for any integration that must validate UDA values before insertion.

UDA_DEPT — Linking UDAs to Departments#

A UDA defined in the UDA table is not automatically available on all items. UDAs are scoped to departments through the UDA_DEPT table. An item in Department 10 only has access to the UDAs that are assigned to Department 10 in UDA_DEPT. This scoping allows different product categories to have entirely different attribute sets — fashion departments capture season and trend direction; homeware departments capture material and country of manufacture.

The REQUIRED_IND on UDA_DEPT is the departmental override for the same column on UDA. A UDA can be optional globally but made mandatory for specific departments — or vice versa. When checking whether a UDA is required for an item, always check UDA_DEPT.REQUIRED_IND for the item's specific department, not UDA.REQUIRED_IND alone.

REQUIRED_IND — The Activation Gate#

The single most important UDA concept for developers working on item integrations is the activation gate. When an item's status is being promoted from Candidate ('C') to Active ('A'), Oracle RMS checks whether all mandatory UDAs for that item's department have been populated. If any required UDA is missing a value, the activation is blocked.

This check is performed at the application layer. The logic is:

1. Find all UDAs assigned to the item's department in UDA_DEPT where REQUIRED_IND = 'Y'
2. For each required UDA, check that a corresponding row exists in the appropriate item-level table (UDA_ITEM_LOV, UDA_ITEM_DATE, or UDA_ITEM_FF)
3. If any required UDA has no item-level row, activation fails with a validation message

Item-Level UDA Tables#

Each of the three UDA types stores its item-level values in a dedicated table. All three follow the same structural pattern: composite primary key of ITEM + UDA_ID, and a single column carrying the actual value.

Querying UDAs Across All Three Types#

Because UDA values are split across three tables depending on type, querying all UDAs for an item requires combining the three item-level tables. The standard pattern is a single query using LEFT JOIN to all three tables, with a CASE expression to surface the actual value regardless of which table it came from.

-- All UDA assignments for a single item — across all three types
-- Returns one row per UDA assigned to the item's department
SELECT u.uda_id,
u.uda_desc,
u.uda_type,
ud.required_ind,
-- Surface the value from whichever table is relevant
CASE u.uda_type
WHEN 'LV' THEN uil.uda_value
WHEN 'FF' THEN uif.uda_text
WHEN 'DA' THEN TO_CHAR(uid.uda_date, 'YYYY-MM-DD')
END                            uda_value,
-- For LOV types, also show the human-readable description
CASE u.uda_type
WHEN 'LV' THEN uv.uda_value_desc
ELSE NULL
END                            uda_value_desc,
CASE
WHEN ud.required_ind = 'Y'
AND CASE u.uda_type
WHEN 'LV' THEN uil.uda_value
WHEN 'FF' THEN uif.uda_text
WHEN 'DA' THEN TO_CHAR(uid.uda_date, 'X')
END IS NULL
THEN 'MISSING — BLOCKS ACTIVATION'
ELSE 'OK'
END                            activation_status
FROM   item_master   im
JOIN   deps          dp  ON  dp.dept    = im.dept
JOIN   uda_dept      ud  ON  ud.dept    = im.dept
JOIN   uda           u   ON  u.uda_id   = ud.uda_id
LEFT JOIN uda_item_lov  uil ON  uil.item   = im.item
AND uil.uda_id = u.uda_id
LEFT JOIN uda_item_date uid ON  uid.item   = im.item
AND uid.uda_id = u.uda_id
LEFT JOIN uda_item_ff   uif ON  uif.item   = im.item
AND uif.uda_id = u.uda_id
LEFT JOIN uda_values    uv  ON  uv.uda_id  = u.uda_id
AND uv.uda_value = uil.uda_value
WHERE  im.item = :v_item
ORDER BY ud.display_order, u.uda_desc;

Integration Pattern — Inserting UDA Values Programmatically#

When items are created through an integration pipeline, UDA values must be inserted programmatically after the ITEM_MASTER row exists but before the item is activated. The pattern is the same regardless of UDA type: look up the UDA_ID from the UDA table, validate the value if it is LOV type, then insert into the appropriate item-level table.

-- Insert a LOV UDA value for an item — with validation
-- Use this pattern as the base for all three UDA types
PROCEDURE assign_lov_uda (
p_item      IN uda_item_lov.item%TYPE,
p_uda_id    IN uda_item_lov.uda_id%TYPE,
p_uda_value IN uda_item_lov.uda_value%TYPE
) IS
l_type_check  uda.uda_type%TYPE;
l_value_check PLS_INTEGER;
BEGIN
-- 1. Confirm this UDA is indeed a List of Values type
SELECT uda_type INTO l_type_check
FROM   uda
WHERE  uda_id = p_uda_id;
IF l_type_check != 'LV' THEN
RAISE_APPLICATION_ERROR(-20001,
'UDA ' || p_uda_id || ' is not a List of Values type');
END IF;
-- 2. Validate value exists in UDA_VALUES
SELECT COUNT(1) INTO l_value_check
FROM   uda_values
WHERE  uda_id    = p_uda_id
AND  uda_value = p_uda_value;
IF l_value_check = 0 THEN
RAISE_APPLICATION_ERROR(-20002,
'Value ''' || p_uda_value ||
''' is not valid for UDA ' || p_uda_id);
END IF;
-- 3. UPSERT — update if exists, insert if not
BEGIN
INSERT INTO uda_item_lov
(item, uda_id, uda_value)
VALUES
(p_item, p_uda_id, p_uda_value);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
UPDATE uda_item_lov
SET    uda_value = p_uda_value
WHERE  item      = p_item
AND  uda_id    = p_uda_id;
END;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20003,
'UDA_ID ' || p_uda_id || ' does not exist in UDA table');
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END assign_lov_uda;

Key Tables — Quick Reference#

Practical SQL Examples#

1. List all UDAs configured for a department with required status

-- All UDAs for a department — type, required flag, LOV options count
SELECT u.uda_id,
u.uda_desc,
u.uda_type,
ud.required_ind,
ud.default_value,
ud.display_order,
COUNT(uv.uda_value)   lov_options_count
FROM   uda_dept   ud
JOIN   uda        u   ON u.uda_id = ud.uda_id
LEFT JOIN uda_values uv ON  uv.uda_id = u.uda_id
WHERE  ud.dept = :v_dept
GROUP BY u.uda_id, u.uda_desc, u.uda_type,
ud.required_ind, ud.default_value, ud.display_order
ORDER BY ud.display_order, u.uda_desc;

2. Candidate items in a department missing required UDA values

-- Items stuck in Candidate status due to missing required UDA values
-- Run this before bulk activation to identify blockers
SELECT im.item,
im.item_desc,
u.uda_id,
u.uda_desc,
u.uda_type,
ud.required_ind
FROM   item_master  im
JOIN   uda_dept     ud  ON  ud.dept     = im.dept
AND ud.required_ind = 'Y'
JOIN   uda          u   ON  u.uda_id    = ud.uda_id
WHERE  im.status = 'C'
AND  im.dept   = :v_dept
-- No LOV value
AND  NOT EXISTS (
SELECT 1 FROM uda_item_lov uil
WHERE uil.item = im.item AND uil.uda_id = u.uda_id
)
-- No Date value
AND  NOT EXISTS (
SELECT 1 FROM uda_item_date uid
WHERE uid.item = im.item AND uid.uda_id = u.uda_id
)
-- No Free Form value
AND  NOT EXISTS (
SELECT 1 FROM uda_item_ff uif
WHERE uif.item = im.item AND uif.uda_id = u.uda_id
)
ORDER BY im.item, u.uda_id;

3. All items assigned a specific LOV UDA value — season report

-- All Active items assigned to a specific season UDA value
-- Replace :v_uda_id with the Season UDA_ID and :v_season with e.g. 'SS26'
SELECT im.item,
im.item_desc,
dp.dept_name,
uv.uda_value_desc   season_label,
uil.uda_value       season_code
FROM   uda_item_lov  uil
JOIN   item_master   im  ON  im.item    = uil.item
JOIN   deps          dp  ON  dp.dept    = im.dept
JOIN   uda_values    uv  ON  uv.uda_id  = uil.uda_id
AND uv.uda_value = uil.uda_value
WHERE  uil.uda_id    = :v_uda_id
AND  uil.uda_value = :v_season
AND  im.status     = 'A'
ORDER BY dp.dept_name, im.item_desc;

4. Pivot all LOV UDA values for items in a department — one row per item

-- Pivoted LOV UDA values for items — up to 4 UDAs shown as columns
-- Adjust the UDA_IDs in the CASE statements to match your instance config
SELECT im.item,
im.item_desc,
MAX(CASE WHEN uil.uda_id = :v_season_uda    THEN uil.uda_value END) season,
MAX(CASE WHEN uil.uda_id = :v_brand_uda     THEN uil.uda_value END) brand_tier,
MAX(CASE WHEN uil.uda_id = :v_range_uda     THEN uil.uda_value END) product_range,
MAX(CASE WHEN uil.uda_id = :v_handling_uda  THEN uil.uda_value END) handling_class
FROM   item_master   im
LEFT JOIN uda_item_lov uil ON uil.item = im.item
WHERE  im.dept   = :v_dept
AND  im.status = 'A'
GROUP BY im.item, im.item_desc
ORDER BY im.item_desc;

5. PL/SQL — validate all required UDAs for an item before activation

-- Returns TRUE if all required UDAs for the item's department are populated
-- Call this in your activation pipeline before updating STATUS to 'A'
FUNCTION uda_prereqs_met (
p_item IN item_master.item%TYPE
) RETURN BOOLEAN IS
l_missing PLS_INTEGER;
BEGIN
SELECT COUNT(1)
INTO   l_missing
FROM   item_master  im
JOIN   uda_dept     ud  ON  ud.dept        = im.dept
AND ud.required_ind = 'Y'
JOIN   uda          u   ON  u.uda_id        = ud.uda_id
WHERE  im.item = p_item
AND  NOT EXISTS (
SELECT 1 FROM uda_item_lov uil
WHERE uil.item = im.item AND uil.uda_id = u.uda_id
)
AND  NOT EXISTS (
SELECT 1 FROM uda_item_date uid
WHERE uid.item = im.item AND uid.uda_id = u.uda_id
)
AND  NOT EXISTS (
SELECT 1 FROM uda_item_ff uif
WHERE uif.item = im.item AND uif.uda_id = u.uda_id
);
RETURN (l_missing = 0);
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END uda_prereqs_met;

Common Gotchas for Developers#

• !

  Checking UDA.REQUIRED_IND instead of UDA_DEPT.REQUIRED_IND. The REQUIRED_IND column exists on both UDA and UDA_DEPT. The departmental override on UDA_DEPT takes precedence for a specific item's department. A UDA marked optional globally may be mandatory in Menswear but not in Accessories. Always query UDA_DEPT.REQUIRED_IND filtered by the item's department, not the global UDA.REQUIRED_IND.

• !

  Inserting to UDA_ITEM_LOV without validating against UDA_VALUES. The UDA_VALUE column on UDA_ITEM_LOV is a foreign key to UDA_VALUES. Inserting a value that does not exist in UDA_VALUES for that UDA_ID will either fail at the constraint level or — if the FK is disabled — produce a record that renders as blank in the RMS UI because the join to UDA_VALUES for the description returns no rows.

• !

  Using the wrong item-level table for a UDA type. Trying to insert a Date UDA value into UDA_ITEM_LOV, or a LOV value into UDA_ITEM_FF, will either fail at the constraint level or create data that no standard RMS query will find. Always read UDA.UDA_TYPE before deciding which of the three item-level tables to write to.

• !

  Not handling SINGLE_VALUE_IND = 'N' UDAs in queries. Most UDAs have SINGLE_VALUE_IND = 'Y' — one value per item. For the rare UDA where multiple values are allowed, UDA_ITEM_LOV can have multiple rows with the same ITEM + UDA_ID — only differing in UDA_VALUE. Queries that assume a single row per item-UDA pair and select into a scalar variable will throw TOO_MANY_ROWS for these multi-value UDAs.

• !

  Pivoting UDA values with hardcoded UDA_IDs across environments. UDA_ID values are system-generated and will differ between your DEV, UAT, and PROD environments unless you explicitly control the sequence or use named lookups. Any query or report that hardcodes a UDA_ID (e.g. WHERE uda_id = 42) will be wrong in a different environment. Always look up UDA_IDs by UDA_DESC at the top of any script that uses them, or parameterise them.

• !

  Forgetting to insert UDA values for new departments added after go-live. When a new department is set up in DEPS and UDAs are assigned via UDA_DEPT, existing items already in Active status in that department will have no UDA values. This is not a blocker for those items since they are already Active — but any report or integration that assumes all Active items in the department have UDA values will produce NULL-filled rows for legacy items.

• !

  Free Form UDA values never being cleaned up after LOV migration. Retailers occasionally migrate a Free Form UDA to a List of Values UDA as data governance improves. The old UDA_ITEM_FF rows are not automatically deleted when the UDA type changes. Without a cleanup, queries that read UDA values via a type-aware CASE expression will find rows in UDA_ITEM_FF for a UDA that is now configured as LV — producing confusing duplicated or contradictory results.

Key Takeaways#