Start a project

Goods receipts not yet invoiced (GRPO to A/P)

Open goods receipt PO rows that no A/P invoice has closed yet, with open quantity and open value per vendor.

By Zain Abbas, Founder & Lead SAP Business One / AI Engineer · Last updated

Tables: OPDN, PDN1Use: Month-end accruals, vendor invoice chasing and GRPO clean-up.

Tool

SELECT T0.DocNum, T0.DocDate, T0.CardCode, T0.CardName,
  T1.ItemCode, T1.Dscription AS ItemName, T1.Quantity, T1.OpenQty,
  CASE WHEN T1.Quantity = 0 THEN 0 ELSE T1.LineTotal * T1.OpenQty / T1.Quantity END AS OpenValue,
  DATEDIFF(day, T0.DocDate, GETDATE()) AS DaysOpen
FROM OPDN T0
INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.CANCELED = 'N' AND T1.LineStatus = 'O'
ORDER BY T0.CardCode, T0.DocDate
Need your UDFs in this report, or a dashboard? We extend it for your company.Customize it →

What this query answers

At month end the accountant needs every goods receipt that no vendor invoice has closed yet, to accrue the cost and chase missing invoices. This returns each open goods receipt PO row with the vendor, item, received and still-open quantity, the open value and how many days the receipt has been waiting for an A/P invoice.

Sample output

Illustrative demo values. Your columns are the same; the numbers come from your company.

DocNumDocDateCardCodeCardNameItemCodeItemNameQuantityOpenQtyOpenValueDaysOpen
6122026-09-18V10000Global Parts Co.A00004Paper A4 Box200200180053
6202026-10-02V20000Metro PackagingA00007Carton 40x3050015037539
6312026-10-21V23000Prime LogisticsA00001Laser Printer 14201010210020
6402026-11-03V10000Global Parts Co.A00003Toner Cartridge Color606012607

SQL Server vs SAP HANA

The CASE guard and the DATEDIFF change as on other pages: DATEDIFF(day, T0.DocDate, GETDATE()) becomes DAYS_BETWEEN(T0."DocDate", CURRENT_TIMESTAMP). Quote "OpenQty" and "LineStatus" exactly.

Common errors and fixes

  • A receipt still shows after the vendor invoice was posted: The A/P invoice must be copied from the goods receipt (Copy From), not keyed by hand. A manual invoice does not close the GRPO row, so close it or reconcile the two.
  • Returned goods are still listed: A goods return copied from the receipt lowers OpenQty. If the return was created without a base document, the GRPO row stays open.
  • Open value is in the wrong currency: LineTotal is in local currency. Use TotalFrgn instead for the document currency.

Need this report built into SAP Business One?

A saved query answers the question once. We do SAP Business One development: add-ons, integrations and reports, from the database to the screen. Or hire a remote SAP Business One developer for the whole job, fixed price per phase.

Purchasing

Related queries

Open purchase orders by vendorOpen purchase order lines with quantity still to receive, grouped by vendor.Vendor aging report (AP aging)Open A/P invoice balance per vendor in due-date buckets.Last purchase price per itemThe most recent A/P invoice price, vendor and date for every item.Outgoing payments by dateVendor payments made in a period, split into cash, transfer, check and card.

Table reference: OPDN table (Goods Receipt PO (header)) · PDN1 table (Goods Receipt PO (rows))

How it works

Three steps, no signup

1

Copy the right version

Use the SQL Server tab for MS SQL databases and the HANA tab for SAP B1 on HANA.

2

Open Query Generator

In SAP Business One go to Tools → Queries → Query Generator, paste and click Execute.

3

Save and share

Click Save, pick a category and give the query a name. Your team can run it from Query Manager.

FAQ

Questions about this tool

How do I run the goods receipts not yet invoiced (grpo to a/p) query in SAP Business One?

Copy the version that matches your database, open Tools → Queries → Query Generator, paste it in the SQL box and click Execute. Save it to reuse it later.

Which tables does this query use?

It reads OPDN, PDN1 from the standard SAP Business One schema. No user-defined fields are needed.

Can this be turned into a dashboard or alert?

Yes. We turn queries into Crystal reports, Excel reports that refresh themselves, SAP B1 alerts or WhatsApp messages for managers.

More free tools

Built for SAP Business One teams

ERP selector: which ERP suits you?Answer 15 questions and compare ERPs on processes, size, budget, hosting and your country’s rules, with every claim sourced.SAP B1 dashboard builderDrag and drop SAP B1 dashboards for Sales, Purchasing, Inventory, Receivables and Finance with click-to-filter. Works offline, no developer needed.SQL to HANA query converterPaste a SQL Server query and get the SAP HANA version, with every change explained.SAP B1 table finderSearch any table: what it stores, its line tables and its key fields.

Need this inside SAP Business One?

We build add-ons, reports and integrations for SAP B1 on SQL Server and HANA. Tell us what you need and get a fixed price, usually within 24 hours.

Start a project Chat on WhatsApp