Oracle RMS Transformable Items

What Are Transformable Items?#

In Oracle RMS, most items are static — they are received in the same unit they are stocked and sold. A shirt arrives as a shirt, is stocked as a shirt, and is sold as a shirt. The item number never changes from receiving dock to point of sale.

Some items do not work this way. In food retail, a whole salmon is purchased from a supplier but never sold as a whole fish — it is processed into salmon fillets, salmon steaks, and salmon skin-on portions, each with its own item number, its own price, and its own stock position. In textiles, a fabric roll is ordered by the metre and transformed into cut pattern pieces for garment assembly. In confectionery, a bulk sack of mixed sweets is received and repacked into individual 100g bags of sorted varieties.

Oracle RMS handles all of these scenarios through item transformations. A transformation is a formally defined rule that says: consuming a certain quantity of this input item produces certain quantities of these output items, with the input item's cost distributed across the outputs in a specified proportion.

ITEM_XFORM_IND — The Transformation Flag#

The entry point into the transformation system is a single column on ITEM_MASTER: ITEM_XFORM_IND. Setting this to 'Y' on an item marks it as a participant in at least one transformation rule — either as an input item, an output item, or both.

Input Items vs Output Items#

Every transformation rule has exactly one input item and one or more output items. These roles are defined in the ITEM_TRANSFORM table, not on ITEM_MASTER — a single item can be an input in one transformation and an output in another.

The ITEM_TRANSFORM Table Structure#

Transformation rules are stored across two related tables in Oracle RMS: ITEM_TRANSFORM_HEAD (the header record for the rule) and ITEM_TRANSFORM_DETAIL (one row per output item in the rule). Some RMS versions also use the simpler ITEM_TRANSFORM table structure — check your instance version, as the schema can vary between RMS 13.x, 14.x, and 16.x.

Transformation Ratios and Yield#

The ratio between FROM_QTY and TO_QTY captures the yield of the transformation — how much output you get from a given input quantity. In ideal transformations the input quantity equals the sum of output quantities. In reality, most physical transformations have waste or yield loss, so the sum of all TO_QTY values will be less than FROM_QTY.

Cost Allocation Across Output Items#

When a transformation is processed, the cost of the input item consumed is distributed to the output items according to the COST_ALLOC_PCT values. This is how Oracle RMS maintains correct cost-of-goods accounting when one purchase becomes multiple sellable products.

The calculation is straightforward:

Cost allocated to output item X = (Input quantity consumed × Input item AV_COST) × (COST_ALLOC_PCT for output X ÷ 100)

This allocated cost becomes the new average cost for each output item at that location, blended into the existing ITEM_LOC_SOH.AV_COST using the standard weighted average cost formula.

The Transformation Transaction Workflow#

A transformation transaction is the operational event that triggers the actual SOH and cost changes. It can be initiated from the RMS back-office or from a store/warehouse system. The workflow is the same regardless of where it originates.

Stock Ledger Impact#

Every transformation transaction writes to the Oracle RMS stock ledger (STKLEDGR), creating a full audit trail of what was consumed and what was produced. This is essential for inventory accounting, shrinkage analysis, and reconciliation.

Transformation vs Pack Breakdown — Key Differences#

Both transformations and pack breakdowns convert one item's stock into another item's stock. Developers new to Oracle RMS often ask when to use each. The distinction is architectural and commercial:

Key Tables — Quick Reference#

Practical SQL Examples#

1. All transformation rules for an input item

-- All transformation rules where a given item is the input
-- Shows what outputs are produced and in what ratio
SELECT h.xform_id,
h.from_item,
im_from.item_desc               input_desc,
h.from_qty,
h.from_uom,
h.loc,
h.loc_type,
h.effective_date,
h.end_date,
d.to_item,
im_to.item_desc                 output_desc,
d.to_qty,
d.to_uom,
d.cost_alloc_pct,
-- Implied yield ratio for this output
ROUND(d.to_qty / h.from_qty, 4) yield_ratio
FROM   item_transform_head   h
JOIN   item_transform_detail d   ON d.xform_id = h.xform_id
JOIN   item_master           im_from ON im_from.item = h.from_item
JOIN   item_master           im_to   ON im_to.item   = d.to_item
WHERE  h.from_item = :v_input_item
AND  (h.end_date IS NULL OR h.end_date > SYSDATE)
ORDER BY h.xform_id, d.to_item;

2. Transformation rules where an item appears as an output

-- Find which transformation rules produce a given output item
-- Critical check before discontinuing — must handle upstream input items too
SELECT h.xform_id,
h.from_item,
im_from.item_desc               input_desc,
im_from.status                  input_status,
h.from_qty,
d.to_qty,
d.cost_alloc_pct,
h.effective_date,
h.end_date
FROM   item_transform_detail d
JOIN   item_transform_head   h      ON h.xform_id   = d.xform_id
JOIN   item_master           im_from ON im_from.item = h.from_item
WHERE  d.to_item = :v_output_item
AND  (h.end_date IS NULL OR h.end_date > SYSDATE)
ORDER BY h.from_item;

3. Validate cost allocation percentages sum to 100 for all active rules

-- Data integrity check — transformation rules where COST_ALLOC_PCT != 100
-- A rule that does not sum to 100 will misallocate cost on every transaction
SELECT h.xform_id,
h.from_item,
im.item_desc                    input_desc,
SUM(d.cost_alloc_pct)           total_alloc_pct,
COUNT(d.to_item)                output_count,
CASE
WHEN ABS(SUM(d.cost_alloc_pct) - 100) < 0.01 THEN 'OK'
ELSE 'INVALID — does not sum to 100'
END                             alloc_status
FROM   item_transform_head   h
JOIN   item_transform_detail d   ON d.xform_id = h.xform_id
JOIN   item_master           im  ON im.item     = h.from_item
WHERE  (h.end_date IS NULL OR h.end_date > SYSDATE)
GROUP BY h.xform_id, h.from_item, im.item_desc
HAVING ABS(SUM(d.cost_alloc_pct) - 100) >= 0.01
ORDER BY h.from_item;

4. SOH and cost position across input and output items at a location

-- Current SOH and average cost for all items in a transformation rule at a location
-- Use to monitor transformation pipeline inventory at a specific store or warehouse
SELECT 'INPUT'                              item_role,
h.from_item                          item,
im_f.item_desc,
soh_f.stock_on_hand,
soh_f.av_cost,
ROUND(soh_f.stock_on_hand

soh_f.av_cost, 2)           total_cost_value
FROM   item_transform_head  h
JOIN   item_master          im_f  ON im_f.item     = h.from_item
JOIN   item_loc_soh         soh_f ON  soh_f.item     = h.from_item
AND soh_f.loc      = :v_loc
AND soh_f.loc_type = :v_loc_type
WHERE  h.xform_id = :v_xform_id
UNION ALL
SELECT 'OUTPUT'                             item_role,
d.to_item                            item,
im_t.item_desc,
soh_t.stock_on_hand,
soh_t.av_cost,
ROUND(soh_t.stock_on_hand

soh_t.av_cost, 2)           total_cost_value
FROM   item_transform_detail d
JOIN   item_master           im_t  ON im_t.item     = d.to_item
LEFT JOIN item_loc_soh       soh_t ON  soh_t.item     = d.to_item
AND soh_t.loc      = :v_loc
AND soh_t.loc_type = :v_loc_type
WHERE  d.xform_id = :v_xform_id
ORDER BY item_role DESC, item;

5. PL/SQL — validate a transformation rule before processing a transaction

-- Validate all pre-conditions for processing a transformation at a location
-- Call before any programmatic transformation transaction
FUNCTION validate_transformation (
p_xform_id    IN item_transform_head.xform_id%TYPE,
p_loc         IN item_transform_head.loc%TYPE,
p_loc_type    IN item_transform_head.loc_type%TYPE,
p_xform_qty   IN NUMBER  -- multiples of FROM_QTY to transform
) RETURN VARCHAR2 IS
l_from_item   item_transform_head.from_item%TYPE;
l_from_qty    item_transform_head.from_qty%TYPE;
l_soh         item_loc_soh.stock_on_hand%TYPE;
l_alloc_sum   NUMBER;
l_output_cnt  PLS_INTEGER;
l_end_date    item_transform_head.end_date%TYPE;
BEGIN
-- 1. Rule must exist and be currently effective
SELECT from_item, from_qty, end_date
INTO   l_from_item, l_from_qty, l_end_date
FROM   item_transform_head
WHERE  xform_id = p_xform_id
AND  SYSDATE >= effective_date;
IF l_end_date IS NOT NULL AND l_end_date < SYSDATE THEN
RETURN 'RULE_EXPIRED: end_date ' || TO_CHAR(l_end_date, 'YYYY-MM-DD');
END IF;
-- 2. Input item must have sufficient SOH at this location
SELECT NVL(stock_on_hand, 0) INTO l_soh
FROM   item_loc_soh
WHERE  item     = l_from_item
AND  loc      = p_loc
AND  loc_type = p_loc_type;
IF l_soh < (l_from_qty * p_xform_qty) THEN
RETURN 'INSUFFICIENT_SOH: available=' || l_soh
|| ' required=' || (l_from_qty * p_xform_qty);
END IF;
-- 3. Cost allocation must sum to 100
SELECT SUM(cost_alloc_pct), COUNT(to_item)
INTO   l_alloc_sum, l_output_cnt
FROM   item_transform_detail
WHERE  xform_id = p_xform_id;
IF l_output_cnt = 0 THEN
RETURN 'NO_OUTPUTS: transformation rule has no output items';
END IF;
IF ABS(l_alloc_sum - 100) >= 0.01 THEN
RETURN 'ALLOC_INVALID: cost_alloc_pct sums to ' || l_alloc_sum;
END IF;
RETURN 'OK';
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'RULE_NOT_FOUND: xform_id=' || p_xform_id
|| ' not effective or does not exist';
WHEN OTHERS THEN
RETURN 'ERROR: ' || SQLERRM;
END validate_transformation;

Common Gotchas for Developers#

• !

  ITEM_XFORM_IND = 'N' on either the input or an output item. The flag must be 'Y' on every item that participates in a transformation — both the item being consumed and every item being produced. A transformation rule that references an item with ITEM_XFORM_IND = 'N' cannot be saved via the RMS UI, and a programmatic insert to ITEM_TRANSFORM_DETAIL for such an item will fail at transaction processing time even if the insert itself succeeds.

• !

  COST_ALLOC_PCT not summing to exactly 100 due to rounding. When entering percentages like 33.33%, 33.33%, 33.34% for three outputs, integration scripts that calculate these values programmatically often introduce floating-point rounding errors that leave the sum at 99.99 or 100.01. Oracle RMS validates this at save time in the UI. Direct inserts to ITEM_TRANSFORM_DETAIL may not be validated until the first transaction is processed — at which point cost allocation produces incorrect results. Always verify the sum before inserting.

• !

  Setting output items as ORDERABLE_IND = 'Y' when they should never be purchased.

  Output items — salmon fillets, cut panels, repacked bags — are produced through transformation, not purchased from suppliers. Setting ORDERABLE_IND = 'Y' on them allows replenishment to raise purchase orders for items that no supplier actually sells. Always set ORDERABLE_IND = 'N' on output items unless there is a deliberate dual-source strategy where the item can be both produced and purchased.

• !

  Processing a transformation at a location where output items are not ranged.

  When a transformation transaction increases the SOH of an output item, the system needs an ITEM_LOC_SOH record to credit the inventory to. If the output item is not ranged at the location (ITEM_LOC row missing), the transformation will either fail entirely or post the inventory to a location-level holding account, depending on RMS version configuration. Always ensure all output items are ranged at every location where transformations will be processed.

• !

  Using the same item as both input and output in the same transformation rule.

  A transformation where the same item appears in both FROM_ITEM and TO_ITEM creates a circular reference — consuming an item to produce itself. This is not a meaningful operation and will produce incorrect SOH and cost results. Oracle RMS may or may not prevent this at the UI level depending on version. Always validate that no item appears on both sides of a transformation rule in integration scripts.

• !

  Not end-dating expired transformation rules. Active transformation rules with no END_DATE remain permanently available for selection. When product specifications change — a different portioning method, new output items, revised yield percentages — the old rule must be explicitly end-dated before the new rule is created. Without this, both the old and new rules are presented as valid options, and operators may accidentally process transformations against the outdated ratios and cost allocation percentages.

• !

  Querying only ITEM_TRANSFORM (the older table name) in mixed-version RMS environments.

  Older RMS versions (13.x and earlier) stored transformation rules in a single ITEM_TRANSFORM table rather than the head-detail split of newer versions. If your queries use ITEM_TRANSFORM and get an ORA-00942, the instance uses the newer schema. If you query ITEM_TRANSFORM_HEAD and get ORA-00942, you are on an older version. Always verify the actual table names present in your specific RMS instance before building any transformation-related queries.

Key Takeaways#