In retail, profitability isn't just about selling high; it's about knowing exactly what your inventory costs. If you buy a t-shirt from a supplier in Vietnam for $2.00, your true cost is not $2.00. Once you factor in ocean freight, customs duties, port handling fees, and domestic transport, that shirt might actually cost $3.15 by the time it hits your store shelf.

Oracle RMS manages this complexity using Cost Components and Landed Cost. This guide breaks down the architecture of expenses and assessments, and how they flow into inventory valuation (UNIT_COST on ITEM_LOC_SOH).

1. Understanding Cost Components#

In RMS, any cost associated with an item is broken down into specific Cost Components (stored in COST_COMP). These components fall into three primary categories:

1. Base Cost: The actual price paid to the primary supplier for the merchandise.
2. Expenses: Additional costs incurred during transportation and handling (e.g., Freight, Insurance, Palletizing).
3. Assessments: Taxes, duties, or fees levied by government bodies (e.g., Import Duty, Harbor Maintenance Fee).

The CVB (Cost Value Basis)

Every expense and assessment component is calculated using a Cost Value Basis (CVB). This tells RMS how to calculate the component.

• Percentage (P): 5% of the Base Cost.
• Per Unit (U): $0.50 per unit.
• Value (V): A flat $1000 applied across the entire shipment.

By chaining these components together on an item or a PO, RMS builds a complete profile of the item's true cost.

2. Estimated Landed Cost (ELC)#

When a Purchase Order is created, the final invoices for freight and customs haven't arrived yet. Therefore, RMS uses Estimated Landed Cost (ELC).

ELC is calculated when the PO is approved. RMS looks at the item, the supplier, the origin country, and the routing path to attach default cost components (e.g., automatically applying a 6% import duty if the origin is China).

The ORDLOC_EXP Table

The estimated expenses for a specific purchase order line are stored in ORDLOC_EXP. This table links the order (ORDER_NO), the item (ITEM), and the location (LOCATION) to specific cost components (COMP_ID) and their estimated values.

3. Actual Landed Cost (ALC)#

Weeks after the merchandise is received, the actual invoices for freight, insurance, and customs arrive. Often, these invoices are processed in Oracle Retail Invoice Matching (ReIM) or a third-party financials system.

When the actual invoices are paid, the true cost is known. This is the Actual Landed Cost (ALC).

ALC Finalization (The True-Up)

If the ALC differs from the ELC (e.g., the estimated freight was $0.50 per unit, but the actual freight invoice came to $0.65 per unit), RMS must perform an ALC True-Up.

1. RMS receives the ALC payload (usually via the RIB ALCDesc message).
2. RMS compares the ALC against the ELC that was used during receiving.
3. RMS calculates the variance (in this case, +$0.15 per unit).
4. RMS writes a TRAN_DATA adjustment record (Tran Code 70 - Cost Variance) to true-up the Stock Ledger.
5. If the inventory is still in stock, RMS updates the WAC. If it has already been sold, the variance hits Cost of Goods Sold (COGS).

4. Core Tables Reference#

Cost Component Configuration (COST_COMP)

5. SQL Deep Dives#

Calculating Total ELC for a PO Line

This query calculates the total Estimated Landed Cost for a specific order line by summing the base cost and all associated estimated expenses.

SELECT 
    ol.order_no,
    ol.item,
    ol.location,
    ol.unit_cost AS base_cost,
    NVL(SUM(exp.estimated_value), 0) AS total_expenses,
    (ol.unit_cost + NVL(SUM(exp.estimated_value), 0)) AS estimated_landed_cost
FROM ordloc ol
LEFT JOIN ordloc_exp exp 
  ON ol.order_no = exp.order_no 
 AND ol.item = exp.item 
 AND ol.location = exp.location
WHERE ol.order_no = 998877
GROUP BY 
    ol.order_no, 
    ol.item, 
    ol.location, 
    ol.unit_cost;

Finding Significant ALC Variances

When ALC significantly exceeds ELC, it destroys gross margin. This query identifies receipts where the actual cost was more than 10% higher than the estimate.

SELECT 
    ah.order_no,
    ac.item,
    ac.comp_id,
    ac.estimated_value AS elc_amount,
    ac.actual_value AS alc_amount,
    (ac.actual_value - ac.estimated_value) AS variance_amount,
    ROUND(((ac.actual_value - ac.estimated_value) / ac.estimated_value) * 100, 2) AS variance_pct
FROM alc_head ah
JOIN alc_comp ac ON ah.alc_no = ac.alc_no
WHERE ac.estimated_value > 0
  AND ((ac.actual_value - ac.estimated_value) / ac.estimated_value) > 0.10
ORDER BY variance_amount DESC;

6. Common Gotchas#

7. Key Takeaways#

8. Official Oracle Resources#

For further reading, consult the official Oracle documentation:

• Oracle Retail Merchandising System Operations Guide
• Oracle RMS Data Model: Cost Components (COST_COMP)