OPOR table in SAP Business One (Purchase Order (header))

OPOR key fields

How OPOR relates to other tables

OPOR is the header and POR1 the lines, joined on DocEntry and identified by LineNum. When a goods receipt PO or an A/P invoice is created from the order, each target line stores BaseType = 22, BaseEntry = the POR1.DocEntry and BaseLine = the POR1.LineNum. In the other direction, POR1.TargetType and TrgetEntry show the document the line was copied to (20 = goods receipt PO, 18 = A/P invoice).

Common joins

• OPOR T0 INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry (lines)
• PDN1 T2 ON T2.BaseType = 22 AND T2.BaseEntry = T1.DocEntry AND T2.BaseLine = T1.LineNum (goods receipts from the order)
• PCH1 T3 ON T3.BaseType = 22 AND T3.BaseEntry = T1.DocEntry AND T3.BaseLine = T1.LineNum (A/P invoices copied straight from the order)
• OPOR T0 INNER JOIN OCRD T4 ON T0.CardCode = T4.CardCode (vendor)

When you use the OPOR table

Use OPOR and POR1 for supplier follow-up, expected receipts per warehouse, the value of open commitments and purchase price checks. Work on the lines for anything about quantities, because one purchase order is often received in several deliveries.

Example query: open purchase order lines with value still to receive

SQL Server

SELECT T0.DocNum, T0.DocDueDate, T0.CardCode, T0.CardName,
  T1.ItemCode, T1.Dscription, T1.Quantity, T1.OpenQty, T1.WhsCode,
  T1.OpenQty * T1.Price AS OpenValue
FROM OPOR T0
INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.DocStatus = 'O' AND T0.CANCELED = 'N' AND T1.LineStatus = 'O'
ORDER BY T0.DocDueDate, T0.DocNum

SAP HANA (column names in double quotes)

SELECT T0."DocNum", T0."DocDueDate", T0."CardCode", T0."CardName",
  T1."ItemCode", T1."Dscription", T1."Quantity", T1."OpenQty", T1."WhsCode",
  T1."OpenQty" * T1."Price" AS "OpenValue"
FROM OPOR T0
INNER JOIN POR1 T1 ON T0."DocEntry" = T1."DocEntry"
WHERE T0."DocStatus" = 'O' AND T0."CANCELED" = 'N' AND T1."LineStatus" = 'O'
ORDER BY T0."DocDueDate", T0."DocNum"

Paste either version in Tools → Queries → Query Generator. Need the other dialect for your own query? Use the SQL to HANA converter.

Common pitfall: The header stays open while one line is open

OPOR.DocStatus stays 'O' until every line is closed, so a header-only report shows a 90 % received order as fully pending. Use POR1.OpenQty and LineStatus. And when you trace receipts, match on all three base fields (BaseType, BaseEntry, BaseLine): BaseEntry alone also matches other document types that use the same number. See Open purchase orders query.

Ready-made queries that use OPOR

• Open purchase orders by vendor: Open purchase order lines with quantity still to receive, grouped by vendor.
• Last purchase price per item: The most recent A/P invoice price, vendor and date for every item.

Related tables

OPCH (A/P Invoice (header)) · OITM (Items (item master data)) · OITW (Item stock per warehouse) · search all SAP Business One tables

Sources: SAP Business One documentation and SDK (DI API database tables reference), SAP Help Portal.