Creating a Purchase Order (PO) in Oracle Retail Merchandising System (RMS) sets the stage, but the true complexity of purchasing lies in Order Management. This covers the lifecycle changes a PO undergoes: moving from a draft state into an approved commitment, handling mid-flight buyer revisions, and cleanly cancelling orders when vendors fail to deliver.

This guide explores the database mechanics behind PO approvals, the critical role of order revisions (REV_NO), and how cancellations cascade through the inventory ledger to release STOCK_ON_ORDER.

1. The PO Approval Workflow#

When a PO is initially created (either manually via the UI or automatically via Replenishment batches), it typically starts in Worksheet (W) status. In this state, it is essentially a draft. It has no impact on inventory systems, does not trigger EDI transmissions, and represents zero financial liability.

The critical pivot point is the Approval process.

State Transition: W -> A

When a buyer clicks "Approve" (or an automated batch approves the order), RMS executes a massive cascading transaction. The order's status in ORDHEAD.STATUS flips from W (Worksheet) to A (Approved).

OTB (Open to Buy) Commitment

Approval also triggers a financial commitment against the Open to Buy (OTB) budget. The ORDHEAD.OTB_EDATE (Open to Buy End Date) determines which fiscal period the financial liability falls into. If the budget for that period is exhausted, RMS will block the approval (unless the buyer has explicit override privileges).

2. Revision Tracking Mechanics#

Retail is dynamic. After a PO is approved and transmitted to the supplier, circumstances change. The buyer might negotiate a lower unit cost, increase the ordered quantity, or change the delivery dates.

Once a PO is Approved, any structural change to the order must be tracked as a formal Revision.

The Revision Lifecycle

To change an Approved PO, the buyer must transition the order back to Worksheet status. However, it's not a simple rollback.

1. The PO is placed into Worksheet (W). The ORDHEAD.REV_NO (Revision Number) is incremented by 1.
2. RMS takes a snapshot of the current approved state and writes it to the Revision History tables (e.g., ORDHEAD_REV, ORDSKU_REV, ORDLOC_REV).
3. The buyer makes the modifications (e.g., increasing QTY_ORDERED in ORDLOC).
4. The buyer re-approves the PO. The status returns to Approved (A).

Inventory and Financial Adjustments

When the revised PO is re-approved, RMS calculates the delta. If QTY_ORDERED increased by 50 units, STOCK_ON_ORDER is increased by 50. If the unit cost decreased, the OTB commitment is reduced by the difference.

SELECT 
    o.order_no,
    o.item,
    o.qty_ordered AS current_qty,
    r.qty_ordered AS previous_qty,
    (o.qty_ordered - r.qty_ordered) AS revision_delta
FROM ordsku o
JOIN ordsku_rev r 
  ON o.order_no = r.order_no 
 AND o.item = r.item
 AND r.rev_no = (SELECT MAX(rev_no) FROM ordsku_rev WHERE order_no = o.order_no);

3. Cancellation and Closure#

Not all orders are fulfilled perfectly. Suppliers short-ship, items are discontinued, or shipments are lost. To maintain accurate inventory records, outstanding PO quantities must be managed through Cancellation or Closure.

Order Cancellation (Status 'X')

Cancellation explicitly voids the remaining unreceived quantity on an order.

• Line Level: A specific item/location (ORDLOC) can be cancelled by updating QTY_CANCELLED. The remaining STOCK_ON_ORDER for that specific line is subtracted from ITEM_LOC_SOH.
• Header Level: If the entire order is cancelled, ORDHEAD.STATUS becomes X (Cancelled). RMS iterates through all ORDLOC records, sets QTY_CANCELLED equal to the outstanding quantity, and releases all associated STOCK_ON_ORDER and OTB commitments.

Order Closure (Status 'C')

Closure indicates the order has naturally reached the end of its lifecycle. When a PO is fully received (QTY_ORDERED = QTY_RECEIVED), it automatically transitions to Closed (C). However, buyers can manually close a partially received PO (e.g., if the supplier shipped 98 out of 100 units and the buyer decides not to wait for the remaining 2). Manually closing a PO functions identically to cancelling the remaining balance: the unreceived 2 units are marked as cancelled, and STOCK_ON_ORDER is released.

4. Core Tables Reference#

Revision Table Columns (ORDLOC_REV)

5. SQL Deep Dives#

Identifying Zombie POs

This query identifies Approved POs that are significantly past their "Not After Date" but still holding STOCK_ON_ORDER, suppressing replenishment.

SELECT 
    oh.order_no,
    oh.supplier,
    oh.not_after_date,
    ol.item,
    ol.location,
    (ol.qty_ordered - ol.qty_received - ol.qty_cancelled) AS outstanding_qty,
    ol.unit_cost
FROM ordhead oh
JOIN ordloc ol ON oh.order_no = ol.order_no
WHERE oh.status = 'A'
  AND oh.not_after_date < SYSDATE - 30 -- More than 30 days overdue
  AND (ol.qty_ordered - ol.qty_received - ol.qty_cancelled) > 0
ORDER BY oh.not_after_date ASC;

Cancelling a PO Line via PL/SQL API

When building custom extensions, never update ORDLOC.QTY_CANCELLED directly via SQL. Always use the Oracle-provided API packages to ensure OTB, STOCK_ON_ORDER, and EDI triggers fire correctly.

DECLARE
    L_error_message VARCHAR2(255);
    L_return_status VARCHAR2(1);
BEGIN
    -- Cancel 50 units of item '10012345' on order 998877 at store 200
    ORDER_SQL.CANCEL_LINE (
        O_error_message => L_error_message,
        O_return_status => L_return_status,
        I_order_no      => 998877,
        I_item          => '10012345',
        I_location      => 200,
        I_loc_type      => 'S',
        I_cancel_qty    => 50
    );

    IF L_return_status != 'S' THEN
        DBMS_OUTPUT.PUT_LINE('Cancellation Failed: ' || L_error_message);
        ROLLBACK;
    ELSE
        DBMS_OUTPUT.PUT_LINE('Line cancelled successfully.');
        COMMIT;
    END IF;
END;
/

6. Common Gotchas#

7. Key Takeaways#

8. Official Oracle Resources#

For further reading, consult the official Oracle documentation:

• Oracle Retail Merchandising System Operations Guide (Volume 1 & 2)
• Oracle RMS Data Model: Purchase Order Tables (ORDHEAD, ORDLOC)