POR1 table in SAP Business One (Purchase Order (rows))

What is the POR1 table in SAP B1?

POR1 is the SAP Business One table for purchase order rows. Each row is one item or service ordered from a vendor, with quantity, open quantity, price, warehouse and delivery date, and joins to the OPOR header on DocEntry. Goods receipt PO rows in PDN1 point back to it with BaseType 22, BaseEntry and BaseLine.

Where is the POR1 table in SAP B1?

In SAP Business One 10.0, POR1 is the "Purchase Order - Rows" table with 343 columns. You see its data in Purchasing - A/P → Purchase Order (header table OPOR).

POR1 key fields

Types as listed in the SAP Business One 10.0 SDK database tables reference: nVarChar(n) is text of up to n characters, Num(19,6) a decimal with 6 places. Column names are the same on SQL Server and SAP HANA.

How POR1 relates to other tables

POR1 is the row table of OPOR. When a goods receipt PO is copied from the order, its rows in PDN1 store BaseType 22, BaseEntry = POR1.DocEntry and BaseLine = POR1.LineNum, and the order row keeps the target in TargetType and TrgetEntry. An A/P invoice copied straight from the order does the same in PCH1.

Common joins

• POR1 T1 INNER JOIN OPOR T0 ON T0.DocEntry = T1.DocEntry (header)
• POR1 T1 LEFT JOIN OITM T2 ON T2.ItemCode = T1.ItemCode (item master)
• POR1 T1 LEFT JOIN PDN1 T3 ON T3.BaseType = 22 AND T3.BaseEntry = T1.DocEntry AND T3.BaseLine = T1.LineNum (receipts from the row)
• POR1 T1 LEFT JOIN PCH1 T4 ON T4.BaseType = 22 AND T4.BaseEntry = T1.DocEntry AND T4.BaseLine = T1.LineNum (invoices from the row)

When you use the POR1 table

Use POR1 for open purchase order lists by vendor, item or delivery date, for expected receipts per warehouse, and to compare ordered with received quantities.

Example query: open purchase order rows by delivery date

SQL Server

SELECT T0.DocNum, T0.CardCode, T0.CardName, T1.ItemCode, T1.Dscription, T1.Quantity,
  T1.OpenQty, T1.ShipDate, T1.WhsCode
FROM OPOR T0
INNER JOIN POR1 T1 ON T1.DocEntry = T0.DocEntry
WHERE T0.CANCELED = 'N' AND T1.LineStatus = 'O' AND T1.OpenQty > 0
ORDER BY T1.ShipDate, T0.DocNum, T1.LineNum

SAP HANA (column names in double quotes)

SELECT T0."DocNum", T0."CardCode", T0."CardName", T1."ItemCode", T1."Dscription", T1."Quantity",
  T1."OpenQty", T1."ShipDate", T1."WhsCode"
FROM OPOR T0
INNER JOIN POR1 T1 ON T1."DocEntry" = T0."DocEntry"
WHERE T0."CANCELED" = 'N' AND T1."LineStatus" = 'O' AND T1."OpenQty" > 0
ORDER BY T1."ShipDate", T0."DocNum", T1."LineNum"

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

Common pitfall: Quantity is not what is still to come

Quantity is the ordered quantity and does not go down when part of the row is received. For what the vendor still owes use OpenQty, and always combine it with LineStatus = 'O' so rows that were closed are left out. The header alone is not enough either: an open order in OPOR can contain rows that are already closed. See Open purchase orders query.

Ready-made queries that use POR1

• 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.

Building something on POR1?

Reading POR1 (Purchase Order (rows)) in a query is the easy part. We build SAP Business One vendor portals: vendors confirm purchase orders and delivery dates, upload invoices as A/P drafts and check payment status. Or hire a remote SAP Business One developer for the whole job, fixed price per phase.

Questions about the POR1 table

What is the POR1 table in SAP B1?

POR1 is the SAP Business One table for purchase order rows. Each row is one item or service ordered from a vendor, with quantity, open quantity, price, warehouse and delivery date, and joins to the OPOR header on DocEntry. Goods receipt PO rows in PDN1 point back to it with BaseType 22, BaseEntry and BaseLine.

How do I join POR1 to OPOR in SAP B1?

Join on POR1.DocEntry = OPOR.DocEntry. Vendor, dates and cancel status are on OPOR; items, quantities and prices are on POR1.

How do I find the goods receipt PO for a purchase order row?

Look in PDN1 for BaseType = 22, BaseEntry = POR1.DocEntry and BaseLine = POR1.LineNum, then join OPDN on PDN1.DocEntry for the receipt number and date.

What does OpenQty mean on POR1?

The ordered quantity that is not yet received or invoiced. It is the number to use for expected receipts, together with LineStatus = O.

What is the difference between OPOR and POR1?

OPOR is the purchase order header, with one row per order. POR1 holds one row per item or service on the order. They join on DocEntry.

Related tables

OPOR (Purchase Order (header)) · PDN1 (Goods Receipt PO (rows)) · PCH1 (A/P Invoice (rows)) · OITM (Items (item master data)) · search all SAP Business One tables

Sources: SAP Business One documentation and SDK (DI API database tables reference), SAP Help Portal; SDK 10.0 table page for POR1.