Retailers buy the same items from the same suppliers repeatedly. Over time, the cost of manufacturing, raw materials, and shipping fluctuates. If you have a bin of 100 identical widgets, but you bought 50 of them for $1.00 last month and 50 of them for $1.50 this week, how do you value the asset?

Oracle RMS solves this using Weighted Average Cost (WAC). This guide breaks down the math, the database triggers, and how the system prevents financial collapse when negative inventory occurs.

1. The WAC Formula#

WAC is tracked at the Item/Location level. The WAC for a t-shirt in the New York store might be $10, while the WAC for the identical t-shirt in the London store is $12 (due to higher landed costs).

The value is stored in ITEM_LOC_SOH.UNIT_COST.

When new inventory is received, RMS blends the incoming cost with the existing cost using this formula:

New WAC = [ (Current SOH * Current WAC) + (Incoming Qty * Incoming Cost) ] / (Current SOH + Incoming Qty)

Example Scenario

• Current State: You have 10 units in stock. ITEM_LOC_SOH.UNIT_COST is $5.00. (Total Inventory Value = $50.00).
• The Receipt: You receive 5 new units. The Purchase Order ORDLOC.UNIT_COST is $8.00. (Total Incoming Value = $40.00).

The Calculation: [ (10 * $5.00) + (5 * $8.00) ] / (10 + 5) [ 50 + 40 ] / 15 90 / 15 = $6.00

The new ITEM_LOC_SOH.UNIT_COST becomes $6.00.

2. The Recalculation Trigger#

WAC is not calculated in a nightly batch. It is calculated synchronously, in real-time, the exact millisecond the receiving transaction is processed (Tran Code 20).

When a warehouse receives an ASN or a store confirms a delivery, the PL/SQL receiving packages immediately lock the ITEM_LOC_SOH row, perform the WAC math, and update the UNIT_COST.

Any sales (Tran Code 1) processed immediately after that receipt will book their Cost of Goods Sold (COGS) at the newly calculated $6.00 rate.

3. Negative Inventory & WAC Corruption#

The WAC formula works perfectly—until STOCK_ON_HAND drops below zero.

If a location has -5 units at $10 WAC, and they receive 5 units at $15... the math breaks. [ (-5 * 10) + (5 * 15) ] / 0 = Divide by Zero Error.

To prevent the database from crashing, RMS implements a strict failsafe: If a receipt causes STOCK_ON_HAND to equal zero or go negative, WAC is NOT recalculated. It simply inherits the incoming PO cost, or remains frozen at the previous value, depending on system options. Negative inventory is the primary cause of WAC corruption in RMS.

4. Cost Variances (Tran Code 70)#

What happens if you receive goods at an Estimated Landed Cost of $10, RMS updates the WAC to $10, but three weeks later the actual supplier invoice arrives for $12?

RMS handles this via Actual Landed Cost (ALC) true-ups, writing a TRAN_DATA record with Tran Code 70 (Cost Variance).

1. RMS calculates that it undervalued the receipt by $2 per unit.
2. It looks at the current STOCK_ON_HAND.
3. If the units are still in the building, RMS updates the WAC to reflect the true cost.
4. If the units have already been sold, RMS writes a financial adjustment (Tran Code 70) directly to the General Ledger to adjust the Cost of Goods Sold (COGS) for the previous fiscal period.

5. Core Tables Reference#

6. SQL Deep Dives#

Monitoring WAC Volatility

If a buyer makes a typo on a PO (entering $1000 instead of $10), the WAC will skyrocket upon receipt. This query finds items where the WAC jumped by more than 50% in a single day.

SELECT 
    td.item,
    td.location,
    td.tran_date,
    td.tran_code,
    ils.unit_cost AS current_wac,
    (td.total_cost / td.units) AS receipt_cost,
    ROUND((((td.total_cost / td.units) - ils.unit_cost) / ils.unit_cost) * 100, 2) AS variance_pct
FROM tran_data td
JOIN item_loc_soh ils ON td.item = ils.item AND td.location = ils.loc
WHERE td.tran_code = 20 -- Receipts
  AND td.tran_date = TRUNC(SYSDATE)
  AND td.units > 0
  AND ROUND((((td.total_cost / td.units) - ils.unit_cost) / ils.unit_cost) * 100, 2) > 50;

7. Official Oracle Resources#

For further reading, consult the official Oracle documentation:

• Oracle Retail Merchandising System Operations Guide: Cost Accounting
• Oracle RMS Data Model: Cost & Inventory (ITEM_LOC_SOH)