What is ReSA?

Every day, across thousands of stores, millions of transactions occur on Point of Sale (POS) registers. Cash is handed over, credit cards are swiped, items are returned, and voids are performed. If all of this raw, unchecked data flowed directly into the corporate General Ledger (GL) or the RMS stock ledger, errors and fraud would corrupt the company's financials instantly.

Oracle Retail Sales Audit (ReSA) is the gatekeeper. It sits between the store-level POS systems (Xstore, third-party POS) and the corporate financial systems (RMS, GL). Its job is to:

Without ReSA, a retailer has no way to validate that the $2.3 billion in annual revenue recorded by its POS systems is actually accurate.

The Complete Data Flow

Understanding where ReSA fits in the enterprise data flow is critical:

┌──────────┐    ┌──────────┐    ┌──────────────┐    ┌──────────┐    ┌──────────┐
│  XSTORE  │───▶│ XCENTER  │───▶│    ReSA      │───▶│   RMS    │───▶│   GL     │
│  (POS)   │    │(Corporate│    │(Sales Audit) │    │(Inventory│    │(General  │
│          │    │  Server) │    │              │    │ & Finance│    │ Ledger)  │
│ TLOGs    │    │ Routing  │    │ Audit &      │    │ Stock    │    │ Financial│
│ Created  │    │ Engine   │    │ Validation   │    │ Ledger   │    │ Reporting│
└──────────┘    └──────────┘    └──────────────┘    └──────────┘    └──────────┘

Key principle: Data only flows from ReSA to RMS after ReSA has validated it. If a transaction fails an audit rule, it is held in the Error Queue and must be manually corrected by a headquarters auditor before it can be released. This prevents bad data from ever touching the stock ledger.

TLOG Processing Pipeline

POS systems (like Oracle Xstore) generate Transaction Logs (TLOGs). A TLOG is a highly detailed, often cryptic flat file or XML payload documenting every single action on the register.

The Processing Steps

1. Trickle Polling: Modern stores send TLOGs to ReSA multiple times a day (trickle polling), rather than waiting for an end-of-day batch. This gives headquarters near-real-time visibility into store operations. Typical polling intervals are every 5–15 minutes.

2. Parsing & Normalization: ReSA parses the raw TLOG (which may be in proprietary POS formats like RTLog, POSLog XML, or custom flat files) into a normalized relational structure. The parsing engine handles:

• Multiple POS vendor formats (Oracle Xstore, NCR, Toshiba, etc.)
• Character encoding differences
• Missing or malformed fields
• Time zone conversions

3. Transaction Type Classification: Each TLOG record is classified into transaction types:

• SALE — Regular merchandise sale
• RETURN — Customer return with or without receipt
• VOID — Cancelled transaction (line void vs post void)
• NO_SALE — Cash drawer opened without a transaction (suspicious activity)
• PAID_OUT — Cash paid out of the drawer (e.g., petty cash)
• PAID_IN — Cash deposited into the drawer (e.g., starting float)

4. Balancing: ReSA compares the declared register totals (what the cashier said was in the drawer at end of day) against the calculated totals (what the TLOG transactions mathematically add up to) to find overages or shortages.

The ReSA Database Schema

ReSA stores parsed transaction data in a highly normalized schema. Understanding these tables is essential for any developer writing custom reports or integrations.

Querying ReSA Data

SELECT 
    sth.store,
    sth.day_dt,
    COUNT(DISTINCT sth.tran_seq_no)            AS total_transactions,
    SUM(CASE WHEN sth.trans_type = 'SALE' 
             THEN 1 ELSE 0 END)                AS sale_count,
    SUM(CASE WHEN sth.trans_type = 'RETURN' 
             THEN 1 ELSE 0 END)                AS return_count,
    SUM(sti.selling_retail * sti.quantity)      AS gross_sales,
    SUM(sti.discount_amt)                      AS total_discounts,
    SUM(stt.tender_amt)                        AS total_tendered
FROM 
    sa_tran_head sth
JOIN 
    sa_tran_item sti 
    ON  sth.store       = sti.store
    AND sth.day_dt      = sti.day_dt
    AND sth.tran_seq_no = sti.tran_seq_no
    AND sth.register_id = sti.register_id
LEFT JOIN 
    sa_tran_tender stt
    ON  sth.store       = stt.store
    AND sth.day_dt      = stt.day_dt
    AND sth.tran_seq_no = stt.tran_seq_no
    AND sth.register_id = stt.register_id
WHERE 
    sth.day_dt = TRUNC(SYSDATE - 1)
    AND sth.audit_status = 'POSTED'
GROUP BY 
    sth.store, sth.day_dt
ORDER BY 
    gross_sales DESC;

Audit Rules Engine

The core power of ReSA is its Rules Engine. Business analysts and loss prevention teams configure rules to catch anomalies. Rules are evaluated automatically during the audit process and fall into several categories:

Rule Categories

1. Cashier Fraud Detection:

• "Flag any transaction where a single cashier performs a void greater than $100 without a manager override."
• "Flag any cashier who performs more than 10 post-voids in a single shift."
• "Flag any cashier whose average discount percentage exceeds 15% of sales."

2. Data Integrity Rules:

• "Flag any transaction where a return is processed for an item that is not on file in RMS."
• "Flag any transaction where the tax amount is zero but the item is not tax-exempt."
• "Flag any transaction where the selling price is below the item's cost."

3. Suspicious Activity Patterns:

• "Flag any store where total returns exceed 20% of total sales for the day."
• "Flag any register with more than 5 no-sale drawer openings in an hour."
• "Flag any transaction processed after the store's official closing time."

4. Financial Threshold Rules:

• "Flag any single transaction exceeding $5,000."
• "Flag any single cash tender exceeding $2,000."
• "Flag any gift card reload exceeding $500."

Rule Configuration

Rules are configured in the SA_AUDIT_RULE table and associated parameter tables. Each rule has:

• A rule type (cashier, store, transaction, item)
• A severity level (warning, error, critical)
• Threshold parameters (dollar amounts, percentages, counts)
• An action (flag for review, auto-correct, reject)

Over/Short Balancing

One of ReSA's most important functions is Over/Short (OVR/SHORT) balancing. This compares what should be in the register's cash drawer with what the cashier actually counted.

The Calculation

Most retailers define tolerance thresholds for Over/Short:

• Within ±$2.00: Auto-accept (rounding errors)
• Between ±$2.01 and ±$25.00: Flag for review but auto-post
• Greater than ±$25.00: Hold for mandatory auditor review

Totals Posting to RMS & GL

Once a store's daily transactions have been audited and all errors resolved, the Totals Posting process releases the financial data to downstream systems:

Posting to RMS (Inventory Impact)

The saposupld batch program extracts validated sales data from ReSA and posts it to RMS. This is how RMS knows that items were sold:

1. Stock On Hand Reduction: For every item sold, RMS reduces ITEM_LOC_SOH.STOCK_ON_HAND at the selling store.
2. Stock Ledger Entry: RMS posts a debit entry to the STOCK_LEDGER reflecting the cost of goods sold (COGS).
3. Sales History: RMS updates ITEM_LOC_HIST with the sales quantities and amounts for future replenishment and planning calculations.

Posting to GL (Financial Impact)

ReSA generates General Ledger (GL) posting entries that follow standard double-entry accounting:

• Debit: Cash/Accounts Receivable (the money received)
• Credit: Revenue (the sales income)
• Debit: Cost of Goods Sold
• Credit: Inventory (reducing the inventory asset)

The Error Queue Workflow

When a transaction breaks an audit rule, it falls into the ReSA Error Queue. The error queue is the daily workload for headquarters auditors.

Error Resolution Process

1. Auditor opens the Error Queue in the ReSA web application
2. Reviews flagged transactions — each error shows the rule that was broken, the transaction details, and the severity
3. Takes action:
   • Override: The auditor determines the transaction is valid despite triggering the rule (e.g., a large legitimate purchase). They override the error with a reason code.
   • Correct: The auditor modifies the transaction data to fix the error (e.g., updating a missing item number, correcting a tax amount)
   • Delete: The auditor determines the transaction is invalid and removes it from the audit (rare — usually only for duplicate TLOGs)
4. Re-audit: After corrections, the auditor re-runs the audit for the store/day
5. Post: Once all errors are cleared, the auditor posts the store/day totals

ReSA Batch Architecture

ReSA operates on a daily batch cycle, typically running during the overnight window:

ReSA Reporting & Analytics

Beyond its core auditing function, ReSA is a goldmine for retail analytics:

• Shrinkage Analysis: By comparing expected inventory (based on receipts and sales) against actual stock counts, loss prevention teams can identify stores with abnormal shrinkage patterns.
• Cashier Performance: Track metrics like average transaction time, scan rate, void rate, and discount percentage per cashier.
• Tender Mix Analysis: Understand payment method trends (cash vs credit vs mobile wallets) by store, region, and time period.
• Returns Intelligence: Identify stores, items, or customers with abnormal return patterns that may indicate return fraud.

SELECT 
    sth.cashier_id,
    COUNT(DISTINCT sth.tran_seq_no)                    AS total_transactions,
    SUM(CASE WHEN sth.trans_type = 'VOID' 
             THEN 1 ELSE 0 END)                        AS void_count,
    ROUND(
        SUM(CASE WHEN sth.trans_type = 'VOID' THEN 1 ELSE 0 END) 
        / NULLIF(COUNT(DISTINCT sth.tran_seq_no), 0) 
        * 100, 2
    )                                                  AS void_rate_pct,
    SUM(CASE WHEN sth.trans_type = 'VOID' 
             THEN sti.selling_retail * sti.quantity 
             ELSE 0 END)                               AS void_amount
FROM 
    sa_tran_head sth
JOIN 
    sa_tran_item sti 
    ON  sth.store       = sti.store
    AND sth.day_dt      = sti.day_dt
    AND sth.tran_seq_no = sti.tran_seq_no
WHERE 
    sth.day_dt BETWEEN TRUNC(SYSDATE - 30) AND TRUNC(SYSDATE)
GROUP BY 
    sth.cashier_id
HAVING 
    SUM(CASE WHEN sth.trans_type = 'VOID' THEN 1 ELSE 0 END) > 5
ORDER BY 
    void_rate_pct DESC;