RMS Database Schema: Tables & Naming Conventions
Oracle RMS contains thousands of tables. Memorizing them all is impossible, but understanding the underlying naming conventions unlocks the entire data model. Learn the prefixes, suffixes, and core foundational tables every developer must know.
When you first connect an SQL client to an Oracle RMS database, the sheer scale of the schema can be overwhelming. There are literally thousands of tables, views, and PL/SQL packages.
However, the architects at Oracle (and Retek, originally) followed strict naming conventions. Once you learn these conventions, navigating the RMS data model becomes highly intuitive. You won't need to memorize table names; you will be able to guess them accurately.
1. Decoding Table Prefixes
RMS tables generally start with a prefix indicating the functional area or module they belong to.
Common RMS Table Prefixes
| Column | Type | Description |
|---|---|---|
ORD | Purchase Orders | ORDHEAD, ORDLOC |
TSF | Transfers | TSFHEAD, TSFDETAIL |
INV | Inventory | INV_STATUS_CODES, INV_ADJ |
COMP | Competitor Pricing | COMP_SHOP_LIST |
DEAL | Supplier Deals | DEAL_HEAD, DEAL_DETAIL |
VAT | Value Added Tax | VAT_CODES, VAT_ITEM |
SUP | Suppliers | SUPS, SUP_TRAITS |
WH | Warehouses | WH, WH_ADD |
2. Decoding Table Suffixes
While prefixes tell you the business area, suffixes tell you the nature of the data stored in the table.
Common RMS Table Suffixes
| Column | Type | Description |
|---|---|---|
_TEMP | Temporary working tables used by overnight batch jobs. Data is usually truncated daily. | STKLEDGR_TEMP |
_HIST | Historical records or audit trails. | ITEM_LOC_SOH_HIST |
_REV | Revision tracking. Holds the state of an entity before it was modified. | ORDHEAD_REV |
_TL | Translation Label tables used for Multi-Language Support (MLS). | ITEM_MASTER_TL |
_EXT | Extension tables added in later versions to hold new columns without altering the base table. | ITEM_MASTER_EXT |
The Translation Tables (_TL)
If you query ITEM_MASTER.ITEM_DESC and it's null or in the wrong language, you should join to ITEM_MASTER_TL using the LANG column to get the localized description for the user's current locale.
3. The Header-Detail Pattern
Virtually all transactional entities in RMS follow a strict hierarchical pattern. If you understand this pattern for Purchase Orders, you automatically understand it for Transfers, RTVs, and Allocations.
Level 1: The Header
Contains data applicable to the entire transaction (Supplier, Dates, Status).
- PO:
ORDHEAD - Transfer:
TSFHEAD - Return to Vendor:
RTV_HEAD
Level 2: The Detail (or SKU)
Contains data specific to the items on the transaction, aggregated across all locations.
- PO:
ORDSKU - Transfer:
TSFDETAIL - Return to Vendor:
RTV_DETAIL
Level 3: The Location
Contains data specific to the item at a specific location (Store or Warehouse). This is where physical quantities are tracked.
- PO:
ORDLOC(containsQTY_ORDERED,QTY_RECEIVED) - Transfer:
TSFDETAIL(often combined with Level 2 for simple transactions, but location mapping tables exist).
4. The Big Five Foundational Tables
If you want to be productive in RMS on day one, memorize these five tables. Everything else connects back to them.
ITEM_MASTER: The core of the universe. Every product, style, and pack exists here. Primary key:ITEM.SUPS: The supplier master. Primary key:SUPPLIER.STORE: The store master table. Primary key:STORE.DEPS: The department table. Represents the first meaningful level of the merchandise hierarchy below group. Primary key:DEPT.ITEM_LOC_SOH: The Stock on Hand table. This tracks the physical inventory quantities for every item at every location. It is the most heavily updated table in the system.
5. Key Takeaways
Key Takeaways
- Prefixes (like ORD, TSF, INV) group tables by business function, making them easy to locate in the schema.
- Suffixes (like _TEMP, _TL, _HIST) identify the technical purpose of the table. Never write business logic that permanently relies on _TEMP tables.
- RMS transactions universally follow a Header -> Detail -> Location relational pattern.
- The
ITEM_MASTERandITEM_LOC_SOHtables are the most critical tables in the system. They are the hub that connects pricing, purchasing, and inventory.
