In large-scale retail, the base cost of an item is rarely the final word. Buyers constantly negotiate temporary price reductions to support marketing campaigns, volume discounts to lower inventory costs, or rebates based on sales performance.

Oracle Retail Merchandising System (RMS) manages these negotiations through the Deal Management module. This guide explores the two primary deal architectures—Off-Invoice and Bill-Back—and how the DEAL_HEAD tables interact with Purchase Orders to manipulate the true cost of goods.

1. Off-Invoice Deals#

An Off-Invoice Deal is the simplest and most common type of supplier discount. It is a reduction in the unit cost that is applied before the supplier sends the invoice.

If the base cost of an item is $10.00, and there is an active $1.00 Off-Invoice deal, the Purchase Order will be generated with a unit cost of $9.00.

How RMS Applies Off-Invoice Deals

1. A buyer creates a Deal in RMS (type Off-Invoice), specifying the item, supplier, location, and the active date range.
2. The Deal is approved, moving DEAL_HEAD.STATUS to A.
3. When a PO is created for that item/supplier during the active dates, RMS automatically detects the deal.
4. RMS calculates the discount and applies it to ORDLOC.UNIT_COST.
5. The PO is transmitted to the supplier with the discounted cost, and the supplier's invoice is expected to match it.

2. Bill-Back (Rebate) Deals#

A Bill-Back Deal (or Rebate) is more complex. The retailer pays the full base cost upfront on the PO, but the supplier agrees to refund a portion of the cost later, based on specific performance metrics.

Bill-Backs are typically tied to one of two triggers:

• Receipts: The supplier rebates $1.00 for every unit received into the warehouse during the deal period.
• Sales (Scan-Based): The supplier rebates $1.00 for every unit actually sold to a customer at the cash register during a specific promotional week.

The Bill-Back Lifecycle

1. The PO is created at the full Base Cost (e.g., $10.00). The supplier invoices $10.00, and the retailer pays $10.00.
2. The goods are received (or sold). RMS tracks these events.
3. A nightly batch process (dealcalc.pc / dealupld.pc) scans the TRAN_DATA table for receipts or sales that match the active Bill-Back deal parameters.
4. RMS calculates the accrued rebate (e.g., 1,000 units sold * $1.00 = $1,000 rebate).
5. RMS generates a financial claim against the supplier. This claim is sent to the financials system (like ReIM or Oracle Financials) to deduct $1,000 from the supplier's next payment.

3. Deal Thresholds and Tiers#

Supplier negotiations often involve volume tiers. RMS supports this through Complex Deals with thresholds.

For example:

• Buy 0 - 5,000 units: 0% discount.
• Buy 5,001 - 10,000 units: 5% off-invoice discount.
• Buy 10,001+ units: 10% off-invoice discount.

Target vs. Linear Deals

• Target (Retroactive): If you hit the 10,001 tier, you get the 10% discount on all 10,001 units. (Often handled as a Bill-Back at the end of the quarter).
• Linear (Stepped): You pay full price for the first 5,000, get 5% off the next 5,000, and 10% off the 1st unit over 10,000.

The complexity of tracking cumulative order volumes across multiple POs to determine tier eligibility is handled by the RMS deal calculation batches.

4. Core Tables Reference#

Deal Header Table (DEAL_HEAD)

5. SQL Deep Dives#

Finding Active Off-Invoice Deals for an Item

Before a PO is generated, it's highly useful to know if an item has an active deal that will lower its cost.

SELECT 
    dh.deal_id,
    dh.supplier,
    dh.billing_type,
    dh.active_date,
    dh.close_date,
    dd.item,
    dcp.deal_comp_type, -- '%' (Percent) or 'A' (Amount)
    dcp.deal_comp_val   -- The discount value (e.g., 5 for 5%, or 1.50 for $1.50)
FROM deal_head dh
JOIN deal_detail dd ON dh.deal_id = dd.deal_id
JOIN deal_comp_prom dcp ON dd.deal_detail_id = dcp.deal_detail_id
WHERE dh.status = 'A'
  AND dh.billing_type = 'OI'
  AND dh.active_date <= SYSDATE
  AND dh.close_date >= SYSDATE
  AND dd.item = '10012345'
  AND dh.supplier = 889900;

Tracking Accrued Bill-Back Income

To see how much rebate income a specific Bill-Back deal has generated so far:

SELECT 
    dh.deal_id,
    dh.supplier,
    SUM(dai.income_amount) AS total_accrued_rebate,
    SUM(dai.billed_amount) AS total_billed_to_supplier,
    (SUM(dai.income_amount) - SUM(dai.billed_amount)) AS pending_billing
FROM deal_head dh
JOIN deal_actuals_item dai ON dh.deal_id = dai.deal_id
WHERE dh.billing_type = 'BB'
  AND dh.deal_id = 45678
GROUP BY dh.deal_id, dh.supplier;

6. Common Gotchas#

7. Key Takeaways#

8. Official Oracle Resources#

For further reading, consult the official Oracle documentation:

• Oracle Retail Merchandising System Operations Guide
• Oracle RMS Data Model: Deal Management (DEAL_HEAD)