NetSuiteFree generatorPDF, Excel, Word exportNetSuite

SuiteQL Query for Open Purchase Order Lines

Open purchase order lines are one of the most requested NetSuite reports, and the SuiteQL for it follows a repeatable pattern: join transaction to transactionline, filter the header type, exclude the main and tax lines and show display values. This template fills that pattern for you.

Fill in

Generated code
Download:

SuiteQL query

SELECT
  t.id AS po_internal_id,
  t.tranid AS po_number,
  t.trandate AS order_date,
  BUILTIN.DF(t.entity) AS vendor,
  BUILTIN.DF(t.status) AS po_status,
  tl.id AS line_id,
  BUILTIN.DF(tl.item) AS item,
  tl.quantity AS quantity_ordered,
  tl.quantityshiprecv AS quantity_received,
  tl.netamount AS net_amount
FROM
  transaction t
  INNER JOIN transactionline tl ON tl.transaction = t.id
WHERE
  t.type = 'PurchOrd'
  AND tl.mainline = 'F'
  AND tl.taxline = 'F'
  AND t.status IN ('B', 'E')
  AND t.trandate >= TO_DATE('2026-01-01', 'YYYY-MM-DD')
  AND t.trandate <= TO_DATE('2026-12-31', 'YYYY-MM-DD')
  AND BUILTIN.DF(t.entity) LIKE '%Acme%'
ORDER BY
  t.trandate DESC, t.tranid, tl.id
FETCH FIRST 500 ROWS ONLY

Run it from SuiteScript

const results = query.runSuiteQL({ query: sql }).asMappedResults();

// For larger sets use runSuiteQLPaged with a pageSize and loop over the pages.

Verify column names in the Records Catalog for your account and release. In many accounts purchase order status codes are single letters such as B (Pending Receipt) and E (Partially Received), but check the values in your data. Some amount fields carry a negative sign on purchase transactions, so reconcile one known order. Remove the vendor filter to list all vendors.

Procurement questions without SQL

Optional: see how ERPray handles purchase order questions over read-only NetSuite data.

Optional. The tool and every export work without this. No spam.

How to use the template

Enter the date range, a vendor name fragment, the status codes you consider open and a row limit. Copy the query into the SuiteQL tool in your account, or into N/query.runSuiteQL in a script. Compare the first rows against a purchase order you know, then widen the filters. Export the text to share it with a colleague or add it to a query library.

  • Quote each status code, for example 'B', 'E'.
  • Keep the row limit low while testing.
  • Reconcile one known order before trusting totals.

How the query works

The transaction table holds the header and the transactionline table holds the lines. Joining on transactionline.transaction returns one row per line. The mainline flag marks the header row and taxline marks tax rows, so excluding both leaves item lines. BUILTIN.DF returns the display text for list fields such as vendor, item and status, which saves extra joins. The FETCH FIRST clause caps results.

Common mistakes

A common error is forgetting to filter out mainline and tax rows, which inflates counts and totals. Another is comparing dates as plain strings, so use TO_DATE with an explicit format. Developers also assume quantity sign and amount sign are the same across transaction types, which they are not. Finally, a LIKE on BUILTIN.DF can be slow on large vendor sets, so prefer filtering on the vendor internal id when you have it.

Frequently Asked Questions

What does BUILTIN.DF do?

BUILTIN.DF returns the display value of a field in SuiteQL, such as the vendor name for an entity id or the status label for a status code. It avoids joining lookup tables for list fields.

How do I find the right status codes?

Run a SELECT DISTINCT on t.status with BUILTIN.DF(t.status) for purchase orders in your account. That shows the real codes and labels used in your data, which is safer than relying on memory.

Can I use this query in a Map/Reduce script?

Yes. Run it in getInputData with runSuiteQLPaged or runSuiteQL and return the rows for the map stage. Keep an eye on governance and the size of each page.

Why do my totals not match the PO form?

The form may include tax, shipping and discount lines that this query excludes, and amount signs can differ. Compare line by line against one order and adjust the filters until they reconcile.

If procurement asks for new PO views every week, ERPray can answer those questions over read-only NetSuite data without a developer writing each query, and it shows the SQL used.

Talk to an ExpertExplore More Free Tools

Related Tools

NetSuite

NetSuite Transaction Type Codes Reference

Look up the short type code stored in transaction.type for common NetSuite transactions, with the matching record id.

NetSuite

SuiteScript search.create Snippet Generator

Build a ready-to-paste N/search snippet with a filter, three columns and a paged result loop that respects governance.

NetSuite

SuiteScript 2.x Modules Reference

A searchable table of the SuiteScript 2.x N/ modules, what each one is for and the methods you reach for most.

NetSuite

NetSuite Script and REST Error Codes Reference

Common NetSuite error names and HTTP status codes with the usual cause and the first thing to check.

More NetSuite tools

See all
Back to All Tools