While store managers care about how many units are on a shelf, the CFO only cares about what those units are worth. Oracle RMS bridges this gap using the Stock Ledger.

The Stock Ledger is a sub-ledger system within RMS that records the financial value of all inventory movements. Every time physical stock is altered, an immutable financial record is written. At the end of the day, week, and month, these records are rolled up and fed into the corporate financial system (like Oracle Financials or SAP).

1. The Immutable Journal: TRAN_DATA#

The most important table in all of Oracle RMS is TRAN_DATA.

Whenever an action occurs that impacts inventory—a receipt against a PO, a customer sale at the POS, an inventory adjustment due to damage, or a price change that affects the retail value of the stock—RMS writes a record to TRAN_DATA.

The Structure of a Tran Data Record

Every record in TRAN_DATA contains:

• The ITEM and LOCATION.
• The TRAN_CODE (what happened).
• The UNITS (how many).
• The TOTAL_COST (the financial impact to the balance sheet).
• The TOTAL_RETAIL (the impact to the potential revenue).

2. Key Transaction Codes#

TRAN_DATA categorizes every event using a two-digit TRAN_CODE. There are dozens of codes, but these are the most critical:

• Tran Code 20 (Receipt): Written when goods arrive from a supplier. Increases inventory valuation.
• Tran Code 1 (Sale): Written when the POS integration reports a customer purchase. Decreases inventory valuation and books revenue/COGS.
• Tran Code 4 (Customer Return): Written when a customer returns a product. Reverses Tran Code 1.
• Tran Code 22 (Inventory Adjustment): Written when a user manually adjusts SOH up or down (e.g., due to shrinkage, damage, or stock counts).
• Tran Code 30/32 (Transfers): Written when goods move between locations. Tran 30 represents the shipment out, Tran 32 represents the receipt in.
• Tran Code 11 (Markup) / Tran Code 15 (Markdown): Written when the retail price of an item changes. This doesn't change the physical units, but it changes the TOTAL_RETAIL value of the inventory sitting on the shelf.

3. Cost vs. Retail Accounting#

RMS supports two primary methods of inventory valuation. The method you choose dictates how the Stock Ledger behaves.

Cost Accounting

In cost accounting, the value of inventory is strictly what you paid for it.

• Every receipt is valued at the PO cost (or Actual Landed Cost).
• The UNIT_COST on ITEM_LOC_SOH is maintained via Weighted Average Cost (WAC) or Standard Cost.
• When an item is sold, the Cost of Goods Sold (COGS) is exactly the WAC of the item.

Retail Accounting

In retail accounting, inventory is managed at its Retail Selling Price.

• The ledger tracks the total retail value of all stock in a department.
• To determine the actual cost valuation at month-end, the ledger applies a "Cost-to-Retail Ratio" (the historical markup percentage for that department) to the total retail value.
• Because it's managed at Retail, every price change (Markdowns/Markups) directly impacts the stock ledger valuation and requires a TRAN_DATA record (Tran Codes 11/15) to account for the lost/gained margin.

4. The Rollup Process#

Generating financial reports by summing hundreds of millions of TRAN_DATA rows would crash the database. Instead, RMS uses batch programs to roll up transactions into summary tables.

1. Daily Rollup (saldly): At the end of the day, RMS sums all transactions by department/class/subclass and location, writing the totals to MONTH_DATA.
2. Monthly Rollup (salmth): At the end of the fiscal month (following the 4-5-4 calendar), RMS finalizes the MONTH_DATA records, closes the fiscal period, and passes the summarized financial data to the corporate General Ledger (GL) via cross-reference mapping (FIF_GL_CROSS_REF).

5. Core Tables Reference#

Transaction Journal (TRAN_DATA)

6. SQL Deep Dives#

Auditing Daily Sales vs. Receipts

This query summarizes the total units received (Tran Code 20) versus total units sold (Tran Code 1) for a specific store on a specific day.

SELECT 
    location,
    SUM(CASE WHEN tran_code = 20 THEN units ELSE 0 END) AS total_units_received,
    SUM(CASE WHEN tran_code = 20 THEN total_cost ELSE 0 END) AS total_cost_received,
    SUM(CASE WHEN tran_code = 1 THEN units ELSE 0 END) AS total_units_sold,
    SUM(CASE WHEN tran_code = 1 THEN total_retail ELSE 0 END) AS total_revenue_generated
FROM tran_data
WHERE location = 200
  AND tran_date = TRUNC(SYSDATE - 1)
GROUP BY location;

Reconciling Stock on Hand (SOH) with TRAN_DATA

Because TRAN_DATA is the complete historical journal, the sum of all transactions for an item/location should theoretically equal its current STOCK_ON_HAND. (Assuming an opening balance of 0).

SELECT 
    item,
    location,
    SUM(
        CASE 
            WHEN tran_code IN (20, 4, 32) THEN units -- Inbound (Receipts, Returns, Transfer In)
            WHEN tran_code IN (1, 30) THEN (units * -1) -- Outbound (Sales, Transfer Out)
            WHEN tran_code = 22 THEN units -- Adjustments (Positive or Negative)
            ELSE 0 
        END
    ) AS calculated_soh
FROM tran_data
WHERE item = '10012345' 
  AND location = 200
GROUP BY item, location;

7. Common Gotchas#

8. Official Oracle Resources#

For further reading, consult the official Oracle documentation:

• Oracle Retail Merchandising System Operations Guide: Stock Ledger
• Oracle RMS Data Model: Stock Ledger (TRAN_DATA)