Retail supply chains are fluid. Goods rarely stay where they are initially received. A central Warehouse (Distribution Center) receives massive bulk shipments from suppliers, and then must push smaller quantities out to hundreds of stores (W2S Transfers). Sometimes, a store in Miami sells out of swimsuits while a store in Orlando has too many, requiring a Store-to-Store (S2S) transfer.

Oracle RMS tracks these movements using the Transfer module. This guide explores how transfers are modeled, how they impact the ITEM_LOC_SOH table at every stage, and the financial implications of losing goods in transit.

1. The Transfer Lifecycle#

A Transfer in RMS is fundamentally similar to a Purchase Order, but instead of the supplier being an external vendor, the "supplier" is another location within your own network.

The lifecycle generally follows this path:

1. Input (Worksheet): The transfer is created (manually or via Replenishment).
2. Approved: The transfer is committed. The sending location is told to prepare the goods.
3. Shipped: The sending location packs the goods onto a truck and sends an ASN (Advance Shipment Notice).
4. Received: The receiving location offloads the truck and confirms the quantities.
5. Closed: The transfer is finalized, and any discrepancies are reconciled.

2. Inventory Impact by Status#

Unlike a standard PO where inventory just "appears" upon receipt, a transfer moves inventory between two internal locations. The ITEM_LOC_SOH table must balance perfectly across both locations at every step.

Step 1: Approved

When the transfer is approved:

• Sending Location: TSF_RESERVED_QTY increases. (The goods are still physically there, but cannot be sold).
• Receiving Location: IN_TRANSIT_QTY increases. (The goods are expected to arrive).

Step 2: Shipped

When the goods are loaded onto the truck (Tran Code 30 is written):

• Sending Location: STOCK_ON_HAND decreases. TSF_RESERVED_QTY decreases. (The goods have left the building).
• Receiving Location: IN_TRANSIT_QTY remains the same. The goods are literally in transit.

Step 3: Received

When the truck arrives and goods are scanned in (Tran Code 32 is written):

• Receiving Location: IN_TRANSIT_QTY decreases. STOCK_ON_HAND increases.

3. Freight and Handling Costs#

When transferring goods from a central warehouse to a store, the warehouse incurs labor costs (picking/packing) and freight costs (trucking).

In advanced retail accounting setups, these internal costs can be added to the item's valuation at the destination store via Estimated Transfer Cost (ETC). This is very similar to how Estimated Landed Cost (ELC) works for Purchase Orders, but it applies to internal movements.

However, many retailers simply treat these as operational expenses (OPEX) and transfer the goods strictly at their Weighted Average Cost (WAC), writing a Tran Code 30 at the sending location's WAC and a Tran Code 32 at the receiving location's WAC.

4. Core Tables Reference#

Transfer Header (TSFHEAD)

5. SQL Deep Dives#

Finding Stale In-Transit Transfers

This query finds transfers that were shipped more than 14 days ago but have not yet been fully received at the destination store. These represent lost inventory.

SELECT 
    th.tsf_no,
    th.from_loc,
    th.to_loc,
    td.item,
    td.tsf_qty AS requested_qty,
    td.ship_qty AS shipped_qty,
    td.received_qty,
    (td.ship_qty - NVL(td.received_qty, 0)) AS missing_in_transit_qty
FROM tsfhead th
JOIN tsfdetail td ON th.tsf_no = td.tsf_no
WHERE th.status IN ('S', 'A') -- Shipped or partially received
  AND th.delivery_date < SYSDATE - 14 -- More than 2 weeks overdue
  AND (td.ship_qty - NVL(td.received_qty, 0)) > 0
ORDER BY th.delivery_date ASC;

Checking Transfer Reservations at the Origin

Before an origin location ships a transfer, the inventory is locked up in TSF_RESERVED_QTY. This query shows how much inventory a warehouse has locked up for outbound store transfers.

SELECT 
    ils.item,
    ils.loc AS origin_warehouse,
    ils.stock_on_hand,
    ils.tsf_reserved_qty,
    (ils.stock_on_hand - ils.tsf_reserved_qty - ils.non_sellable_qty) AS free_available_stock
FROM item_loc_soh ils
WHERE ils.loc = 100 -- Warehouse 100
  AND ils.tsf_reserved_qty > 0;

6. Common Gotchas#

7. Official Oracle Resources#

For further reading, consult the official Oracle documentation:

• Oracle Retail Merchandising System Operations Guide: Transfers
• Oracle RMS Data Model: Transfers (TSFHEAD, TSFDETAIL)