Why Staging Tables?

In a production RMS database, the core tables (ITEM_MASTER, ORDHEAD, ITEM_LOC_SOH) have complex constraints — foreign keys, check constraints, triggers, and business rules. If an external system tried to INSERT directly into these tables with invalid data, it would cause constraint violations, trigger failures, and potentially corrupt transactional integrity.

Staging tables solve this by providing a buffer zone:

┌──────────────┐     ┌──────────────┐     ┌──────────────┐     ┌──────────────┐
│  EXTERNAL    │────▶│  STAGING     │────▶│  VALIDATION  │────▶│ PRODUCTION   │
│  SYSTEM      │     │  TABLE       │     │  BATCH       │     │  TABLE       │
│              │     │              │     │              │     │              │
│ (POS, WMS,   │     │ (IF_TRAN_    │     │ Checks FK,   │     │ (TRAN_DATA,  │
│  EDI, ERP)   │     │  DATA, etc.) │     │ validates,   │     │  ITEM_MASTER │
│              │     │              │     │ transforms)  │     │  etc.)       │
│  Raw data    │     │ Unchecked    │     │              │     │              │
│  from source │     │ data buffer  │     │ Rejects bad  │     │ Clean,       │
│              │     │              │     │ rows         │     │ validated    │
└──────────────┘     └──────────────┘     └──────────────┘     └──────────────┘

The Staging-to-Production Pattern

Every staging table integration follows the same lifecycle:

1. Load: External system loads raw data into the staging table (via SQL*Loader, direct INSERT, flat file, or REST API)
2. Validate: A batch program reads each staging row and validates it against RMS business rules
3. Promote: Valid rows are inserted/updated into the corresponding production table
4. Reject: Invalid rows are moved to a rejection table with an error code and message
5. Purge: After successful processing, staging rows are deleted (or archived)

Key RMS Staging Tables

Loading Data into Staging Tables

Loading Methods

1. SQL*Loader (On-Premise): The most common method for bulk loads. SQL*Loader reads a flat file (CSV, fixed-width) and performs direct-path or conventional-path INSERT into the staging table.

2. Direct INSERT (PL/SQL or Pro*C): External integration programs connect to the RMS database and INSERT rows directly using SQL.

3. REST API (Cloud): In MFCS cloud deployments, external systems use ORDS REST API endpoints to load data into staging tables.

4. RIB Message Processing: For real-time integration, RIB adapters receive messages and load them into staging tables for batch promotion.

-- sqlldr_if_tran_data.ctl
LOAD DATA
INFILE 'pos_transactions.dat'
INTO TABLE if_tran_data
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
    tran_data_id    SEQUENCE(MAX,1),
    store           CHAR,
    register_id     CHAR,
    tran_date       DATE "YYYY-MM-DD",
    item            CHAR,
    quantity         DECIMAL EXTERNAL,
    selling_uom     CHAR,
    unit_retail      DECIMAL EXTERNAL,
    total_retail     DECIMAL EXTERNAL,
    status          CONSTANT 'N',
    create_datetime SYSDATE
)

The Promotion Process

The promotion batch program is the critical validation layer. For IF_TRAN_DATA (SAPOSUPLD), the promotion process:

1. Reads each staging row sequentially or in bulk
2. Validates item existence: Does the item exist in ITEM_MASTER?
3. Validates location: Is the store active in the STORE table?
4. Validates item-location: Is the item ranged to this store (exists in ITEM_LOC)?
5. Validates currency: Is the selling price in the store's local currency?
6. Cross-validates totals: Does quantity × unit_retail = total_retail?
7. Promotes valid rows: INSERT INTO tran_data SELECT ... FROM if_tran_data WHERE status = 'V'
8. Updates SOH: Decrements ITEM_LOC_SOH.STOCK_ON_HAND for each sold item
9. Rejects invalid rows: UPDATE if_tran_data SET status = 'R', error_msg = '...' WHERE ...

Error Handling & Rejection

When a staging row fails validation, it is not deleted. Instead, the row's status is updated to 'R' (Rejected) with an error message:

Common Rejection Reasons

-- Find all rejected staging rows from today's batch
SELECT 
    'IF_TRAN_DATA' AS staging_table,
    status,
    error_msg,
    COUNT(*)       AS rejected_count,
    MIN(store)     AS sample_store,
    MIN(item)      AS sample_item
FROM 
    if_tran_data
WHERE 
    status = 'R'
    AND create_datetime > TRUNC(SYSDATE)
GROUP BY 
    status, error_msg
ORDER BY 
    rejected_count DESC;

Monitoring Staging Table Health

Staging tables should be empty (or nearly empty) after the nightly batch completes. If rows accumulate, it indicates a problem:

• Growing IF_TRAN_DATA: POS transactions are not being processed — sales data is not reaching RMS
• Growing IF_ITEM: Item creation uploads are failing — new items can't be ranged or ordered
• Growing rejection counts: Data quality issues between the source system and RMS

Best Practices