Introduction to Location Master

In Oracle Retail Merchandising System (RMS), every item must exist somewhere. The Location Master represents all the physical and logical nodes where inventory can be stored, transacted, or processed. Unlike the Organizational Hierarchy (which groups locations for financial and reporting purposes into Chains, Areas, Regions, and Districts), the Location Master deals with the actual attributes of the locations themselves.

The core location types in RMS are:

1. Stores (S): Brick-and-mortar retail locations or eCommerce web stores.
2. Warehouses (W): Physical distribution centers holding stock.
3. Virtual Warehouses (V): Logical partitions of a physical warehouse used for multi-channel inventory segregation.
4. Finishers (F): External third-party locations where items are sent for processing (e.g., ticketing, hanger insertion).

Stores (STORE)

A Store in RMS is the primary selling location. A store can be a traditional physical store, a pop-up shop, a kiosk, or an eCommerce fulfillment center (often flagged as a Web Store).

When a store is created in RMS, it must be assigned to an Organizational Hierarchy (it must belong to a District, which rolls up to a Region, Area, and Chain). It is also assigned a default currency, a VAT region (if operating in a tax-inclusive environment), and a primary warehouse for replenishment.

Warehouses (WH)

A Warehouse represents a physical Distribution Center (DC). Warehouses are responsible for receiving bulk shipments from suppliers, storing inventory, and fulfilling outbound transfers to stores or other warehouses.

Unlike stores, warehouses do not belong to the standard Organizational Hierarchy (Chain/Area/Region/District). Instead, they are considered independent nodes that service the organizational hierarchy.

Virtual Warehouses (VWH)

Modern retailers operate across multiple channels (Brick & Mortar, eCommerce, Wholesale). To manage this, a single physical Distribution Center might hold stock for all channels.

Oracle RMS uses Virtual Warehouses to logically partition the inventory within a single physical warehouse.

For example, Physical Warehouse 9000 might have:

• Virtual Warehouse 9001 (Retail Store Fulfillment)
• Virtual Warehouse 9002 (eCommerce Fulfillment)
• Virtual Warehouse 9003 (Wholesale Fulfillment)

How Virtual Warehouses Work

1. Inventory Segregation: Stock on Hand (SOH) is tracked at the Virtual Warehouse level in ITEM_LOC_SOH.
2. Costing: WAC (Weighted Average Cost) is typically maintained at the Physical Warehouse level, but inventory quantities are split across the virtual nodes.
3. Fulfillment: eCommerce orders can be hard-allocated exclusively against the eCommerce virtual warehouse (9002), ensuring retail replenishment doesn't steal eCommerce stock.

External Finishers

Finishers are third-party locations where raw or unfinished goods are sent to be finalized before hitting the sales floor. Common finishing activities include:

• Adding security tags
• Inserting hangers
• Price ticketing
• Garment pressing

In RMS, an external finisher is technically set up as a partner (PARTNER table) with a specific partner type (e.g., 'E' for External Finisher). However, they are treated as locations when it comes to inventory transfers. You can transfer stock from a Warehouse to a Finisher, and then from the Finisher to the Stores.

Database Schema Deep Dive

Let's look at the core tables that make up the Location Master in Oracle RMS.

Querying Locations and Addresses

Because addresses are stored centrally in the ADDR table, you must join based on the MODULE code. Here is a production-ready query to extract all active stores with their physical addresses and primary servicing warehouse:

SELECT 
    s.store,
    s.store_name,
    s.store_open_date,
    s.default_wh AS primary_warehouse,
    a.add_1 AS address_line_1,
    a.city,
    a.state,
    a.post
FROM 
    store s
LEFT JOIN 
    addr a ON s.store = a.key_value 
          AND a.module = 'ST' 
          AND a.primary_addr_ind = 'Y'
WHERE 
    s.store_close_date IS NULL 
    OR s.store_close_date > SYSDATE
ORDER BY 
    s.store;

Mapping Virtual to Physical Warehouses

When generating custom extracts for a WMS (Warehouse Management System), you often need to map the logical Virtual Warehouse back to the Physical Warehouse, as the WMS only cares about the physical building.

SELECT 
    v.vwh AS virtual_warehouse,
    v.vwh_desc,
    w.wh AS physical_warehouse,
    w.wh_name,
    a.city,
    a.state
FROM 
    vwh v
JOIN 
    wh w ON v.wh = w.wh
LEFT JOIN 
    addr a ON w.wh = a.key_value 
          AND a.module = 'WH'
          AND a.primary_addr_ind = 'Y'
ORDER BY 
    w.wh, v.vwh;