Phase 3 · Item Management · Oracle RMS Series

Oracle RMS UDAs — User Defined Attributes

The standard Oracle RMS data model cannot anticipate every attribute a retailer needs to capture on an item. UDAs are the extension mechanism — retailer-defined fields that attach to items by department, drive activation gating, feed integrations, and power merchandising reports that the core schema alone cannot support.

⏱13 min read📅April 23, 2026✍️Priyanshu Pandey📚Oracle RMS Series

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.

📋

UDAs vs Differentiators — an important distinction

UDAs and differentiators (DIFF_1 through DIFF_4 on ITEM_MASTER) serve different purposes. Differentiators describe variant dimensions that distinguish child SKUs from each other within a style hierarchy — colour, size, fit. UDAs describe attributes of an item independent of its variant structure. A UDA for Season applies to the whole item, not to the size-blue variant specifically. Use differentiators for SKU-level variation; use UDAs for item-level classification and merchandising metadata.

Three UDA Types

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.

DADate UDA

UDA_ITEM_DATE

A Date UDA stores a single Oracle DATE value for an item. Common uses include launch date, end-of-life date, next-delivery date, and campaign start date. Date UDAs have no pre-defined valid values — any date can be entered. They are stored in UDA_ITEM_DATE with the column UDA_DATE.

Real-world examples: Launch Date, Season End Date, Promotional Start Date, Discontinuation Date.

LVList of Values (LOV) UDA

UDA_ITEM_LOV

A List of Values UDA stores one value from a pre-defined pick-list. The valid options are defined in UDA_VALUES and linked to the UDA. The item-level value is stored in UDA_ITEM_LOV with the column UDA_VALUE, which is a foreign key back to UDA_VALUES. This is the most common UDA type — it enforces data consistency by constraining input to known values.

Real-world examples: Season (SS26, AW26), Brand Tier (PREMIUM, VALUE, CORE), Product Range (BASIC, FASHION, LIFESTYLE), Hazardous Class (NONE, FLAMMABLE, FRAGILE).

FFFree Form (FF) UDA

UDA_ITEM_FF

A Free Form UDA stores an unvalidated text string. It is the most flexible type and the one that should be used sparingly — because it has no validation, data quality tends to degrade over time as users enter inconsistent values. Free Form UDAs are appropriate for attributes where the value space is too wide or unpredictable to define as a pick-list.

Real-world examples: Marketing Tagline, Special Handling Instructions, Internal Reference Number, Buyer Notes.

UDA Master

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.

01UDA

UDA Master Definition

Column	Type	Description
UDA_IDPK	NUMBER(5)	System-generated primary key. Referenced as a foreign key on all UDA value and assignment tables.
UDA_DESC	VARCHAR2(120)	Display name of the UDA as it appears in the RMS UI and reports (e.g. 'Season', 'Brand Tier').
UDA_TYPE	VARCHAR2(2)	DA = Date · LV = List of Values · FF = Free Form. Determines which item-level table holds values.
DISPLAY_TYPE	VARCHAR2(6)	How the UDA is displayed in the RMS Forms UI — TEXT, LOV, DATE picker, etc.
SINGLE_VALUE_IND	VARCHAR2(1)	Y = item can hold only one value for this UDA. N = multiple values allowed (rarely used in practice).
REQUIRED_IND	VARCHAR2(1)	Y = this UDA must be populated before an item can be activated. Checked at department level via UDA_DEPT.

💡

UDA_ID is the key that connects everything

UDA_ID is the foreign key used on every other UDA-related table — UDA_VALUES, UDA_DEPT, UDA_ITEM_LOV, UDA_ITEM_DATE, and UDA_ITEM_FF. When writing any query that spans UDA tables, always join on UDA_ID and always include the UDA.UDA_TYPE in your SELECT to know which storage table the value came from.

UDA Values

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.

02UDA_VALUES

LOV pick-list options

Column	Type	Description
UDA_IDPKFK	NUMBER(5)	FK → UDA.UDA_ID. Identifies which List of Values UDA this value belongs to.
UDA_VALUEPK	VARCHAR2(25)	The stored code value (e.g. 'SS26', 'PREMIUM', 'BASIC'). This is what is stored in UDA_ITEM_LOV.
UDA_VALUE_DESC	VARCHAR2(120)	Human-readable label for the value (e.g. 'Spring/Summer 2026'). Displayed in the RMS UI drop-down.
DISPLAY_ORDER	NUMBER(4)	Controls the order in which values appear in the pick-list. Lower numbers appear first.

⚠️

Inserting UDA_ITEM_LOV without validating against UDA_VALUES

The UDA_VALUE stored in UDA_ITEM_LOV is a foreign key to UDA_VALUES. Direct SQL inserts that do not validate the value against UDA_VALUES first will either fail with an integrity constraint violation or — if the FK is not enforced at the database level — silently create an invalid UDA assignment that the RMS UI cannot display correctly. Always look up valid values from UDA_VALUES before inserting.

UDA Dept Assignment

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.

03UDA_DEPT

UDA to Department assignment

Column	Type	Description
UDA_IDPKFK	NUMBER(5)	FK → UDA.UDA_ID. The UDA being assigned to this department.
DEPTPKFK	NUMBER(4)	FK → DEPS.DEPT. The department this UDA applies to.
REQUIRED_IND	VARCHAR2(1)	Y = items in this dept must have this UDA populated before activation. Overrides UDA.REQUIRED_IND at the dept level.
DEFAULT_VALUE	VARCHAR2(25)	For LOV UDAs, the default value that is pre-populated when a new item is created in this dept. NULL if no default.
DISPLAY_ORDER	NUMBER(4)	Order in which this UDA appears in the dept's UDA list within the RMS item setup form.

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.

Activation Gate

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:

Find all UDAs assigned to the item's department in UDA_DEPT where REQUIRED_IND = 'Y'
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)
If any required UDA has no item-level row, activation fails with a validation message

⚠️

Custom activation scripts must replicate this check

Any PL/SQL procedure or integration script that promotes items from Candidate to Active by directly updating ITEM_MASTER.STATUS bypasses the application-layer UDA check. Items that are activated this way may be missing mandatory UDA values — they will look Active and can appear on purchase orders, but reporting and replenishment logic that relies on those UDA values will produce incorrect results. Always run the prerequisite UDA validation query (see Practical SQL) before any programmatic activation.

Item UDA Tables

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.

04UDA_ITEM_LOV

List of Values — item assignments

Stores one row per item per LOV UDA. The UDA_VALUE column is a foreign key to UDA_VALUES and must be one of the pre-defined valid values.

Column	Type	Description
ITEMPKFK	VARCHAR2(25)	FK → ITEM_MASTER.ITEM. Always VARCHAR2 — never cast to NUMBER.
UDA_IDPKFK	NUMBER(5)	FK → UDA.UDA_ID. Must be a UDA with UDA_TYPE = 'LV'.
UDA_VALUEFK	VARCHAR2(25)	FK → UDA_VALUES(UDA_ID, UDA_VALUE). The selected pick-list code.

05UDA_ITEM_DATE

Date — item assignments

Stores one row per item per Date UDA. No validation against a pre-defined value set — any valid Oracle DATE is accepted.

Column	Type	Description
ITEMPKFK	VARCHAR2(25)	FK → ITEM_MASTER.ITEM.
UDA_IDPKFK	NUMBER(5)	FK → UDA.UDA_ID. Must be a UDA with UDA_TYPE = 'DA'.
UDA_DATE	DATE	The date value assigned to this item for this UDA.

06UDA_ITEM_FF

Free Form — item assignments

Stores one row per item per Free Form UDA. No validation — any text string up to 250 characters is accepted.

Column	Type	Description
ITEMPKFK	VARCHAR2(25)	FK → ITEM_MASTER.ITEM.
UDA_IDPKFK	NUMBER(5)	FK → UDA.UDA_ID. Must be a UDA with UDA_TYPE = 'FF'.
UDA_TEXT	VARCHAR2(250)	The free-form text value assigned to this item for this UDA.

Querying UDAs

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

SQL

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;

✅

This pattern works for any item in any department

The query above is the universal UDA audit pattern. Run it against any item to see every UDA the item's department requires, what value (if any) has been set, and whether any required UDAs are missing. Use the activation_status column output directly in pre-activation validation scripts.

Integration Pattern

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.

07Integration Insert Pattern

Applicable to all three types

The recommended sequence for inserting UDA values in an integration:

Identify all UDAs required for the item's department from UDA_DEPT WHERE REQUIRED_IND = 'Y'
For each LOV UDA, validate the incoming value against UDA_VALUES
Insert into UDA_ITEM_LOV, UDA_ITEM_DATE, or UDA_ITEM_FF based on the UDA type
Run the pre-activation UDA validation query to confirm no required UDAs remain missing
Proceed with item activation

Insert a LOV UDA value for an item — with validation — Use this pattern as the base for all three UDA types

PL/SQL

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;

RC:Storefront · Self-Hosted Headless E-Commerce

Syncing rich product attributes to your own storefront?

RC:Storefront is built to consume structured item data — categories, attributes, and metadata — from RC:OMS in real time. The same disciplined attribute thinking that UDAs bring to Oracle RMS is what powers clean, SEO-ready product pages on RC:Storefront. Self-hosted, zero transaction fees.

See RC:Storefront →

Quick Reference

Key Tables — Quick Reference

UDA Tables — Oracle RMS

UDA

UDA definition master

UDA_VALUES

LOV valid options

UDA_DEPT

UDA to department scope

UDA_ITEM_LOV

LOV values per item

UDA_ITEM_DATE

Date values per item

UDA_ITEM_FF

Free form values per item

Practical SQL

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

SQL

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

SQL

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'

SQL

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

SQL

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'

PL/SQL

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;

Gotchas

Common Gotchas for Developers

Important Gotchas

!
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

UDAs are retailer-defined item attributes that extend the standard ITEM_MASTER schema. They are scoped to departments via UDA_DEPT and come in three types: Date (DA), List of Values (LV), and Free Form (FF).
Each type stores item-level values in its own table: UDA_ITEM_LOV for LOV types, UDA_ITEM_DATE for dates, UDA_ITEM_FF for free text. Always check UDA.UDA_TYPE before deciding which table to read from or write to.
UDA_VALUES holds the valid pick-list options for LOV UDAs. Always validate against UDA_VALUES before inserting to UDA_ITEM_LOV — invalid values either fail on the FK constraint or render as blank in the RMS UI.
REQUIRED_IND on UDA_DEPT (not UDA) is the activation gate. Items in Candidate status with missing required UDA values cannot be activated. Always check UDA_DEPT.REQUIRED_IND for the item's specific department.
UDA_ID values are instance-specific and differ between DEV, UAT, and PROD. Never hardcode UDA_IDs in scripts — always look them up by UDA_DESC or parameterise them.
The universal UDA audit query joins UDA_DEPT → UDA → all three item-level tables with LEFT JOINs, using a CASE on UDA_TYPE to surface the correct value. This pattern works for any item in any department.

← Previous

Item Supplier Setup — Primary Supplier, Cost, Lead Times & Pack Sizes

Next in series →

Pack Items — Simple Packs, Complex Packs and Buyer Packs

Database

Applications

Business Intelligence

Oracle RMS UDAs

Oracle RMS UDAs — User Defined Attributes

What Are UDAs and Why Do They Exist?

UDAs vs Differentiators — an important distinction

The Three UDA Types

The UDA Master Table

UDA_ID is the key that connects everything

UDA_VALUES — List of Values Options

Inserting UDA_ITEM_LOV without validating against UDA_VALUES

UDA_DEPT — Linking UDAs to Departments

REQUIRED_IND — The Activation Gate

Custom activation scripts must replicate this check

Item-Level UDA Tables

Querying UDAs Across All Three Types

This pattern works for any item in any department

Integration Pattern — Inserting UDA Values Programmatically

Key Tables — Quick Reference

Practical SQL Examples

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

2. Candidate items in a department missing required UDA values

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

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

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

Common Gotchas for Developers

Important Gotchas

Key Takeaways

Key Takeaways

Related Articles

Oracle RMS Transformable Items & Rules

Oracle RMS Pack Items

RMS Item Locations — De-ranging and Ranging

RC:OMS

RC:Storefront

RC:Pulse

Oracle RMS UDAs — User Defined Attributes

What Are UDAs and Why Do They Exist?#

UDAs vs Differentiators — an important distinction

The Three UDA Types#

The UDA Master Table#

UDA_ID is the key that connects everything

UDA_VALUES — List of Values Options#

Inserting UDA_ITEM_LOV without validating against UDA_VALUES

UDA_DEPT — Linking UDAs to Departments#

REQUIRED_IND — The Activation Gate#

Custom activation scripts must replicate this check

Item-Level UDA Tables#

Querying UDAs Across All Three Types#

This pattern works for any item in any department

Integration Pattern — Inserting UDA Values Programmatically#

Key Tables — Quick Reference#

Practical SQL Examples#

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

2. Candidate items in a department missing required UDA values

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

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

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

Common Gotchas for Developers#

Important Gotchas

Key Takeaways#

Key Takeaways

Related Articles

Oracle RMS Transformable Items & Rules

Oracle RMS Pack Items

RMS Item Locations — De-ranging and Ranging

RC:OMS

RC:Storefront

RC:Pulse

What Are UDAs and Why Do They Exist?

The Three UDA Types

The UDA Master Table

UDA_VALUES — List of Values Options

UDA_DEPT — Linking UDAs to Departments

REQUIRED_IND — The Activation Gate

Item-Level UDA Tables

Querying UDAs Across All Three Types

Integration Pattern — Inserting UDA Values Programmatically

Key Tables — Quick Reference

Practical SQL Examples

Common Gotchas for Developers

Key Takeaways