Oracle RMS Purchase Orders

What Is a Purchase Order in Oracle RMS?#

A Purchase Order in Oracle RMS is the formal record of a buying commitment between a retailer and a supplier. It specifies what items are being ordered, in what quantities, at what cost, and for delivery to which locations. Every unit of stock that arrives at a store or warehouse enters the system through a purchase order receipt — there is no other way to introduce new inventory into the RMS stock ledger.

Purchase orders in RMS are created in two fundamentally different ways. Manual POs are created by buyers working in the RMS buying screen — selecting a supplier, adding items, specifying quantities, and setting delivery dates. Automated POs are generated by the replenishment engine, which calculates required quantities based on sales patterns, min/max thresholds, and lead times, then creates POs without buyer intervention subject to the AUTO_APPR_IND configuration on ITEM_LOC.

Both types produce identical records in the same three tables: ORDHEAD, ORDSKU, and ORDLOC. The distinction between manual and automated is carried in the ORDER_TYPE column on ORDHEAD and in workflow attributes — it does not change the fundamental data structure.

The Three-Table PO Structure#

Every purchase order in Oracle RMS is represented by three related records that form a parent-child-grandchild hierarchy:

PO Types — Regular, Replenishment, Distribution#

The ORDER_TYPE column on ORDHEAD classifies the purpose of the purchase order. This drives workflow routing, approval rules, and how the order is handled in downstream systems.

The Status Lifecycle — Worksheet to Closed#

Every Oracle RMS purchase order progresses through a defined status lifecycle. Status transitions are governed by business rules that the application enforces — an order cannot jump from Worksheet to Closed, for example, because receiving cannot occur against an unapproved order.

ORDHEAD — PO Header Deep Dive#

ORDSKU — Item Line Details#

ORDLOC — Location-Level Quantities#

ORDLOC is the most operationally active of the three PO tables. It holds the quantity ordered and quantity received per item per delivery location, and is the table directly read by receiving processes, replenishment calculations, and stock-on-order reporting.

The Approval and Transmission Workflow#

The path from a Worksheet PO to a transmitted order involves distinct steps that Oracle RMS either automates or routes to a buyer queue depending on configuration.

Open to Buy — OTB_EDATE and Cost Impact#

Oracle RMS integrates with an Open to Buy (OTB) framework that tracks buying commitments against budget periods. The OTB_EDATE column on ORDHEAD identifies which OTB period a purchase order's cost commitment falls within.

When a PO is approved, its total cost (sum of ORDLOC.UNIT_COST × QTY_ORDERED across all lines) is committed against the OTB budget for the OTB_EDATE period in the item's department. This is how buyers track whether they have sufficient budget headroom before approving additional orders.

Partial Receipts and the In Progress State#

Real-world supplier deliveries are rarely exactly the ordered quantity. Suppliers often deliver in multiple shipments, deliver short due to production issues, or deliver in a different quantity than ordered. Oracle RMS handles this through the In Progress ('I') status and the QTY_RECEIVED column on ORDLOC.

Key Tables — Quick Reference#

Practical SQL Examples#

1. Full PO summary — header, items, quantities and delivery locations

-- Complete PO view from header through to location quantities
SELECT oh.order_no,
oh.status,
oh.order_type,
s.supp_name,
oh.currency_code,
oh.written_date,
oh.orig_approval_date,
oh.not_before_date,
oh.not_after_date,
os.item,
im.item_desc,
os.unit_cost,
ol.loc,
ol.loc_type,
CASE ol.loc_type
WHEN 'S' THEN st.store_name
WHEN 'W' THEN w.wh_name
END                                  loc_name,
ol.qty_ordered,
ol.qty_received,
ol.qty_cancelled,
ol.qty_ordered

ol.qty_received
NVL(ol.qty_cancelled, 0)         qty_outstanding,
ROUND(ol.qty_ordered * os.unit_cost, 2) line_cost
FROM   ordhead       oh
JOIN   sups          s   ON  s.supplier  = oh.supplier
JOIN   ordsku        os  ON  os.order_no = oh.order_no
JOIN   item_master   im  ON  im.item     = os.item
JOIN   ordloc        ol  ON  ol.order_no = os.order_no
AND ol.item     = os.item
LEFT JOIN store      st  ON  st.store    = ol.loc AND ol.loc_type = 'S'
LEFT JOIN wh         w   ON  w.wh        = ol.loc AND ol.loc_type = 'W'
WHERE  oh.order_no = :v_order_no
ORDER BY os.item, ol.loc;

2. Open POs by supplier — outstanding stock on order

-- All open Approved and In Progress POs for a supplier
-- Shows total outstanding value and quantity per item
SELECT oh.order_no,
oh.status,
oh.currency_code,
oh.not_before_date,
os.item,
im.item_desc,
SUM(ol.qty_ordered)                             total_ordered,
SUM(ol.qty_received)                            total_received,
SUM(ol.qty_ordered

ol.qty_received
NVL(ol.qty_cancelled, 0))                 total_outstanding,
SUM((ol.qty_ordered
ol.qty_received
NVL(ol.qty_cancelled, 0))

os.unit_cost)                             outstanding_cost
FROM   ordhead     oh
JOIN   ordsku      os  ON os.order_no = oh.order_no
JOIN   item_master im  ON im.item     = os.item
JOIN   ordloc      ol  ON  ol.order_no = os.order_no
AND ol.item     = os.item
WHERE  oh.supplier = :v_supplier
AND  oh.status  IN ('A', 'I')
GROUP BY oh.order_no, oh.status, oh.currency_code,
oh.not_before_date, os.item, im.item_desc
HAVING SUM(ol.qty_ordered

ol.qty_received
NVL(ol.qty_cancelled, 0)) > 0
ORDER BY oh.not_before_date, os.item;

3. Approved POs older than lead time — overdue delivery check

-- Approved POs where NOT_AFTER_DATE has passed and no receipt has occurred
-- These represent overdue deliveries that are still holding STOCK_ON_ORDER
SELECT oh.order_no,
oh.written_date,
oh.not_after_date,
SYSDATE - oh.not_after_date              days_overdue,
s.supp_name,
s.contact_name,
s.contact_phone,
COUNT(DISTINCT os.item)                  item_count,
SUM(ol.qty_ordered - NVL(ol.qty_received, 0)

NVL(ol.qty_cancelled, 0))          total_outstanding_units,
SUM((ol.qty_ordered - NVL(ol.qty_received, 0)
NVL(ol.qty_cancelled, 0))

os.unit_cost)                      outstanding_cost
FROM   ordhead   oh
JOIN   sups      s   ON s.supplier  = oh.supplier
JOIN   ordsku    os  ON os.order_no = oh.order_no
JOIN   ordloc    ol  ON  ol.order_no = os.order_no
AND ol.item     = os.item
WHERE  oh.status        IN ('A', 'I')
AND  oh.not_after_date < SYSDATE
AND  ol.qty_received   = 0
GROUP BY oh.order_no, oh.written_date, oh.not_after_date,
s.supp_name, s.contact_name, s.contact_phone
ORDER BY days_overdue DESC;

4. Worksheet POs that AUTO_APPR_IND should have auto-approved — replenishment queue check

-- Replenishment POs sitting in Worksheet status when they should be auto-approved
-- Indicates AUTO_APPR_IND configuration issue or a batch failure
SELECT oh.order_no,
oh.written_date,
oh.supplier,
s.supp_name,
COUNT(DISTINCT os.item)                    item_count,
MIN(oh.written_date)                       oldest_po_date
FROM   ordhead   oh
JOIN   sups      s   ON s.supplier  = oh.supplier
JOIN   ordsku    os  ON os.order_no = oh.order_no
WHERE  oh.status     = 'W'
AND  oh.order_type = 'B'               -- replenishment-generated
AND  oh.written_date < SYSDATE - 1    -- older than 1 day
GROUP BY oh.order_no, oh.written_date, oh.supplier, s.supp_name
ORDER BY oh.written_date;

5. PL/SQL — check whether a PO can be cancelled

-- Validate whether a PO is eligible for cancellation
-- Returns 'OK' or a description of the blocking condition
FUNCTION can_cancel_po (
p_order_no IN ordhead.order_no%TYPE
) RETURN VARCHAR2 IS
l_status      ordhead.status%TYPE;
l_recv_count  PLS_INTEGER;
BEGIN
SELECT status INTO l_status
FROM   ordhead
WHERE  order_no = p_order_no;
-- Cannot cancel a Closed PO
IF l_status = 'C' THEN
RETURN 'BLOCKED: PO is already Closed';
END IF;
-- Cannot cancel an already-Cancelled PO
IF l_status = 'X' THEN
RETURN 'BLOCKED: PO is already Cancelled';
END IF;
-- If In Progress, check for any received quantities
IF l_status = 'I' THEN
SELECT COUNT(1) INTO l_recv_count
FROM   ordloc
WHERE  order_no    = p_order_no
AND  qty_received > 0;
IF l_recv_count > 0 THEN
RETURN 'PARTIAL_CANCEL_ONLY: PO has ' || l_recv_count
|| ' line(s) with receipts — only outstanding lines can be cancelled';
END IF;
END IF;
RETURN 'OK';
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'NOT_FOUND: ORDER_NO ' || p_order_no || ' does not exist';
WHEN OTHERS THEN
RETURN 'ERROR: ' || SQLERRM;
END can_cancel_po;

Common Gotchas for Developers#

• !

  Querying ORDLOC without LOC_TYPE and getting duplicate location results. Store 200 and Warehouse 200 both appear as LOC = 200 in ORDLOC. The LOC_TYPE column is the only discriminator. Filtering by LOC alone in PO queries will return rows for both location types when the numbers collide — silently doubling quantities in aggregated reports.

• !

  Leaving Approved POs open after the supplier confirms non-delivery.

  Approved POs with no receipts continue to hold STOCK_ON_ORDER in ITEM_LOC_SOH indefinitely. The replenishment engine subtracts this stock-on-order from calculated need, so it will not raise replacement orders for items it believes are already on the way. When a supplier confirms they cannot deliver, POs must be explicitly cancelled or closed to release the stock-on-order commitment.

• !

  Treating ORDSKU.UNIT_COST as the definitive cost for all locations.

  ORDLOC also has a UNIT_COST column that can differ from ORDSKU.UNIT_COST when location-specific pricing is configured. The receiving process uses ORDLOC.UNIT_COST to calculate the WAC update for ITEM_LOC_SOH.AV_COST. Reporting queries that calculate total order value using only ORDSKU.UNIT_COST × ORDLOC.QTY_ORDERED may produce incorrect figures if the two cost columns differ.

• !

  Creating POs for items not ranged at the delivery location.

  Oracle RMS validates on PO approval that the delivery location in ORDLOC has a corresponding ITEM_LOC record for the item. If the item is not ranged at that location, the approval will fail. Direct SQL inserts to ORDLOC that bypass this check will create PO lines that cannot be received — the receiving process will fail when it tries to update ITEM_LOC_SOH and finds no SOH record.

• !

  Using ORDSKU for total order quantities without summing ORDLOC.

  The canonical source for ordered and received quantities is ORDLOC, not ORDSKU. Some implementations store a rolled-up total quantity on ORDSKU as a convenience column, but it may not be kept perfectly in sync with ORDLOC in all RMS versions. Always sum ORDLOC.QTY_ORDERED aggregated by ORDER_NO + ITEM when you need the accurate total ordered quantity per item per PO.

• !

  Not accounting for QTY_CANCELLED when calculating outstanding quantity.

  The formula for outstanding quantity on an ORDLOC line is QTY_ORDERED - QTY_RECEIVED - QTY_CANCELLED. Queries that calculate outstanding as simply QTY_ORDERED - QTY_RECEIVED will overstate outstanding for partially cancelled lines, producing incorrect stock-on-order totals and misleading replenishment calculations.

• !

  Raising a PO to a supplier with SUP_STATUS = 'I' (Inactive). Oracle RMS blocks PO creation against an inactive supplier through the UI. Integration scripts that create ORDHEAD records via direct SQL bypass this check. An inactive supplier's PO will pass insertion but fail when the EDI transmission batch tries to resolve the supplier's communication details — causing a silent transmission failure that leaves the supplier unaware of the order.

Key Takeaways#