Item Images and Extended Attributes — Overview#

The core ITEM_MASTER table and its satellite tables — ITEM_DESC, ITEM_SUPPLIER, ITEM_LOC — capture what an item is, where it comes from, and where it goes. But a complete item record in a modern retail environment also needs images for e-commerce and digital channels, physical dimension data for warehouse management and logistics, printing attributes for shelf labels and price tickets, and whatever additional retailer-specific attributes the business requires.

Oracle RMS provides dedicated tables for images and dimensions, a ticketing attribute structure for label printing, and a formally documented extension model for anything beyond that. Understanding all four layers is essential for developers building integrations that need a complete item data picture — particularly those feeding Product Information Management (PIM) systems, e-commerce platforms, or warehouse management systems.

The ITEM_IMAGE Table#

ITEM_IMAGE stores image references for items. The composite primary key is ITEM + IMAGE_TYPE, allowing multiple images per item — each serving a different display purpose.

IMAGE_TYPE — Multiple Images Per Item#

The IMAGE_TYPE column controls what kind of image a given row represents. Values are retailer-configured but follow common conventions across Oracle Retail implementations. Each type corresponds to a different use case in the downstream systems that consume item images.

Image Storage Patterns — URLs, Paths and DAM Integration#

Because ITEM_IMAGE.IMAGE_ADDR is a plain VARCHAR2(400), it can hold any string — a relative file path, an absolute URL, a CDN endpoint, or a DAM system identifier. The pattern used has major implications for integration consumers.

ITEM_SUPP_COUNTRY_DIM — Physical Dimensions#

The ITEM_SUPP_COUNTRY_DIM table stores the physical measurement attributes of an item as specified by a particular supplier from a particular country of origin. Physical dimensions in Oracle RMS are modelled at the item-supplier-country level because the same item sourced from factories in different countries may have slightly different packaging dimensions or weights.

This table is consumed by warehouse management systems (WMS) for bin slotting, carton sizing, and pallet build calculations, and by logistics systems for freight cost estimation.

Ticketing Attributes — ITEM_TICKET#

Oracle RMS has a dedicated ticketing module that drives shelf label and price ticket printing. The ITEM_TICKET table holds the ticket printing configuration for each item — how many tickets to print, when to print them, and in what format.

The Oracle Retail Extension Model#

Retailers often need to capture attributes that are not in any Oracle RMS standard table — a proprietary sustainability score, a loyalty exclusion flag, an internal audit field, or a B2B-specific pricing tier. The temptation is to add custom columns directly to ITEM_MASTER or create a completely independent table with no relation to the RMS framework.

Oracle Retail formally documents an extension model — a specific pattern for adding custom attributes that does not break future upgrades and is supported by Oracle's patching and migration tools.

Adding Custom Columns — The Safe Approach#

Oracle Retail ships a set of pre-defined extension tables alongside the core schema. These tables are empty by design — they exist purely to receive custom columns added by the retailer or implementation partner. The most relevant for item data is ITEM_MASTER_EXT.

Custom Extension Tables — The Preferred Approach#

For larger sets of custom attributes, or for custom attributes that have their own relationships (e.g. an item can have multiple custom records, or the custom data joins to other reference tables), the better approach is a completely new custom table that references ITEM_MASTER via a foreign key.

CREATE TABLE xxrc_item_sustainability (
  item                VARCHAR2(25)  NOT NULL,
  carbon_score        NUMBER(5,2),
  recycled_pct        NUMBER(5,2),
  certifications      VARCHAR2(500),
  last_assessed_date  DATE,
  assessed_by         VARCHAR2(120),
  CONSTRAINT xxrc_item_sust_pk
    PRIMARY KEY (item),
  CONSTRAINT xxrc_item_sust_fk
    FOREIGN KEY (item) REFERENCES item_master(item)
);

Key Tables — Quick Reference#

Practical SQL Examples#

1. All images for an item with type descriptions

SELECT ii.item,
       ii.image_type,
       cd.code_desc                    image_type_desc,
       ii.image_addr,
       ii.image_desc,
       ii.image_indate
FROM   item_image   ii
LEFT JOIN code_detail cd ON  cd.code_type = 'IMTP'
                         AND cd.code      = ii.image_type
WHERE  ii.item = :v_item
ORDER BY cd.display_seq, ii.image_type;

2. Items missing a primary image — e-commerce readiness check

SELECT im.item,
       im.item_desc,
       im.dept,
       dp.dept_name,
       im.status
FROM   item_master  im
JOIN   deps         dp ON dp.dept = im.dept
WHERE  im.status     = 'A'
  AND  im.item_level = im.tran_level
  AND  NOT EXISTS (
         SELECT 1
         FROM   item_image ii
         WHERE  ii.item       = im.item
           AND  ii.image_type = 'A'         -- primary image type
           AND  ii.image_addr IS NOT NULL
           AND  TRIM(ii.image_addr) != ' '
       )
ORDER BY dp.dept_name, im.item_desc;

3. Physical dimensions for an item across all sourcing countries

SELECT d.item,
       s.supp_name,
       d.origin_country_id,
       d.dim_object,
       d.weight,
       d.weight_uom,
       d.length,
       d.width,
       d.height,
       d.lwhdim_uom,
       -- Calculated volume in cubic units
       ROUND(d.length * d.width * d.height, 4) cubic_volume
FROM   item_supp_country_dim  d
JOIN   sups                   s ON s.supplier = d.supplier
WHERE  d.item = :v_item
ORDER BY d.origin_country_id, d.supplier,
         DECODE(d.dim_object, 'EA', 1, 'IN', 2, 'CA', 3);

4. Items missing case dimensions — WMS integration readiness check

SELECT im.item,
       im.item_desc,
       is2.supplier,
       s.supp_name,
       is2.primary_country_id
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
WHERE  im.status     = 'A'
  AND  im.item_level = im.tran_level
  AND  NOT EXISTS (
         SELECT 1
         FROM   item_supp_country_dim d
         WHERE  d.item              = im.item
           AND  d.supplier          = is2.supplier
           AND  d.origin_country_id = is2.primary_country_id
           AND  d.dim_object        = 'CA'
           AND  d.weight            > 0
       )
ORDER BY im.dept, im.item_desc;

5. Full item data export — master, images, dimensions and custom attributes

-- ITEM_MASTER_EXT (custom), and ITEM_DESC — one row per item
SELECT im.item,
       id.item_desc,
       id.short_desc,
       im.dept,
       dp.dept_name,
       im.status,
       im.unit_of_measure,
       -- Primary image URL
       img.image_addr                  primary_image_url,
       -- Case weight for logistics
       dim.weight                      case_weight_kg,
       dim.length                      case_length_cm,
       dim.width                       case_width_cm,
       dim.height                      case_height_cm,
       -- Custom extension attributes (add columns as configured)
       ext.sustainability_score,
       ext.b2b_excl_ind
FROM   item_master          im
JOIN   item_desc            id   ON  id.item = im.item
                                 AND id.lang = (SELECT lang FROM comphead)
JOIN   deps                 dp   ON  dp.dept = im.dept
-- Primary image — LEFT JOIN: items without images still appear
LEFT JOIN item_image        img  ON  img.item       = im.item
                                 AND img.image_type  = 'A'
-- Case dimensions from primary supplier-country — LEFT JOIN
LEFT JOIN item_supplier     is2  ON  is2.item             = im.item
                                 AND is2.primary_supp_ind  = 'Y'
LEFT JOIN item_supp_country_dim dim ON  dim.item              = im.item
                                    AND dim.supplier          = is2.supplier
                                    AND dim.origin_country_id = is2.primary_country_id
                                    AND dim.dim_object        = 'CA'
-- Custom extension table — LEFT JOIN
LEFT JOIN item_master_ext   ext  ON ext.item = im.item
WHERE  im.status     = 'A'
  AND  im.item_level = im.tran_level
ORDER BY dp.dept_name, im.item_desc;

Common Gotchas for Developers#

Key Takeaways#