Inventory data naturally degrades over time. Items are stolen, cashiers scan the wrong barcode, and warehouse workers misplace pallets. To run a profitable retail business, you must periodically count the physical goods and force the system to match reality.

In Oracle RMS, this process is known as a Stock Count (or Stocktake). This guide breaks down the database architecture behind scheduling counts, taking the snapshot, calculating the variance, and posting the financial adjustments.

1. Unit vs. Unit & Dollar Counts#

RMS supports two vastly different types of stock counts, dictated by the STOCKTAKE_TYPE in STAKE_HEAD.

Unit Counts (Cycle Counting)

Unit counts are operational. A store manager might decide to count all laptops today because they are high-theft items.

• They update STOCK_ON_HAND.
• Any variance creates a standard Tran Code 22 adjustment.
• Stores remain open during the count.

Unit & Dollar Counts (Wall-to-Wall)

Unit & Dollar counts are financial audits. Typically performed once a year, an external auditing firm counts every single item in the store.

• The store is completely locked down (no sales, no receipts, no transfers).
• RMS takes a massive financial snapshot of the expected inventory value.
• The variance permanently calculates the store's annual "Shrinkage" for the corporate balance sheet.

2. The Stock Count Lifecycle#

1. Scheduling: The count is created in STAKE_HEAD with a scheduled date.
2. Snapshot: On the morning of the count, a batch program (stksched) takes a snapshot of ITEM_LOC_SOH for all items involved and writes it to STAKE_SKU_LOC. This freezes the "Expected Quantity".
3. Counting: Store associates use handheld RF scanners (integrated via SIM) to count physical items.
4. Import: The physical count quantities are uploaded to RMS (STAKE_QTY).
5. Reconciliation: RMS compares the Snapshot (Expected) against the Physical (Actual). If the variance is within tolerance, it is accepted.
6. Update: The stockupd batch runs, forcing ITEM_LOC_SOH to match the physical count and writing Tran Code 22 adjustments for the variance.

3. The Snapshot and Variance#

The most critical part of a stock count is the Snapshot.

If RMS expects you to have 100 units, it records SNAPSHOT_QTY = 100. If your store associates count 95 units, the variance is -5.

4. Core Tables Reference#

Stocktake Snapshot (STAKE_SKU_LOC)

5. SQL Deep Dives#

Auditing Stocktake Shrinkage

After a Unit & Dollar count is complete, finance wants to know the total financial shrinkage (loss) for a store.

SELECT 
    ssl.stocktake_id,
    ssl.location,
    SUM(ssl.snapshot_qty) AS expected_units,
    SUM(ssl.count_qty) AS actual_units,
    SUM(ssl.adjustment_qty) AS variance_units,
    SUM(ssl.adjustment_qty * ils.unit_cost) AS total_financial_shrink
FROM stake_sku_loc ssl
JOIN item_loc_soh ils 
  ON ssl.item = ils.item AND ssl.location = ils.loc
WHERE ssl.stocktake_id = 998877
  AND ssl.adjustment_qty < 0 -- Only look at losses
GROUP BY ssl.stocktake_id, ssl.location;

6. Official Oracle Resources#

For further reading, consult the official Oracle documentation:

• Oracle Retail Merchandising System Operations Guide: Stock Counts
• Oracle RMS Data Model: Stocktakes (STAKE_HEAD)