# SyteLine Open Purchase Order Report Query Generator

> Source: https://netray.co/tools/syteline-open-po-report-query-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Builds a read-only SQL query for open and late PO lines in SyteLine 9/10, with a second query for vendor on-time delivery from PO receipts.

## Category

developer

## Erp

Infor SyteLine

## Audience

- ERP administrator
- SyteLine developer
- Report writer
- Purchasing manager

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Site (site_ref)

**Key:** site

**Type:** text

**Default:** MAIN

**Help:** The site_ref value of the site you are reporting on.

#### Vendor filter (LIKE pattern)

**Key:** vendor

**Type:** text

**Default:** %

**Help:** Use % for all vendors. Vendor numbers can be stored right-justified with leading spaces, so prefer a leading % (for example %V1001).

#### Buyer filter (LIKE pattern)

**Key:** buyer

**Type:** text

**Default:** %

#### Warehouse filter (LIKE pattern)

**Key:** whse

**Type:** text

**Default:** %

#### Minimum days late

**Key:** days_late

**Type:** number

**Default:** 0

**Help:** 0 returns lines due today or earlier. Use -9999 to return every open line, including future due dates.

#### PO line statuses to include

**Key:** statuses

**Type:** text

**Default:** 'O'

**Help:** Quoted, comma separated stat values. O = Ordered. Add 'P' to include planned lines.

#### Order by

**Key:** order_by

**Type:** select

**Options:**

- pi.due_date, p.vend_num
- p.vend_num, pi.due_date
- open_value DESC

**Default:** pi.due_date, p.vend_num

#### On-time lookback (days)

**Key:** lookback

**Type:** number

**Default:** 90

**Blocks:**

#### 1. Open and late PO lines

**Body:** -- Open and late purchase order lines, SyteLine 9/10 (run on a read-only login)
DECLARE @Site nvarchar(20) = N'{{site}}';
DECLARE @DaysLate int = {{days_late}};

SELECT
    p.po_num,
    pi.po_line,
    pi.po_release,
    p.vend_num,
    va.name AS vendor_name,
    p.buyer,
    pi.whse,
    pi.item,
    pi.qty_ordered,
    pi.qty_received,
    pi.qty_ordered - pi.qty_received AS qty_open,
    pi.item_cost,
    (pi.qty_ordered - pi.qty_received) * pi.item_cost AS open_value,
    pi.due_date,
    DATEDIFF(day, pi.due_date, GETDATE()) AS days_late
FROM po_mst p
JOIN poitem_mst pi
    ON pi.site_ref = p.site_ref
   AND pi.po_num = p.po_num
JOIN vendaddr_mst va
    ON va.site_ref = p.site_ref
   AND va.vend_num = p.vend_num
WHERE p.site_ref = @Site
  AND pi.stat IN ({{statuses}})
  AND pi.qty_ordered > pi.qty_received
  AND p.vend_num LIKE N'{{vendor}}'
  AND ISNULL(p.buyer, N'') LIKE N'{{buyer}}'
  AND pi.whse LIKE N'{{whse}}'
  AND DATEDIFF(day, pi.due_date, GETDATE()) >= @DaysLate
ORDER BY {{order_by}};

#### 2. Vendor on-time percentage from PO receipts

**Body:** -- Receipt transactions (ref_type P, trans_type R) compared with the PO line due date
DECLARE @Site nvarchar(20) = N'{{site}}';
DECLARE @Lookback int = {{lookback}};

SELECT
    p.vend_num,
    va.name AS vendor_name,
    COUNT(*) AS receipts,
    SUM(CASE WHEN CAST(m.trans_date AS date) <= CAST(pi.due_date AS date) THEN 1 ELSE 0 END) AS on_time_receipts,
    CAST(100.0 * SUM(CASE WHEN CAST(m.trans_date AS date) <= CAST(pi.due_date AS date) THEN 1 ELSE 0 END) / COUNT(*) AS decimal(5,1)) AS on_time_pct
FROM matltran_mst m
JOIN poitem_mst pi
    ON pi.site_ref = m.site_ref
   AND pi.po_num = m.ref_num
   AND pi.po_line = m.ref_line_suf
   AND pi.po_release = m.ref_release
JOIN po_mst p
    ON p.site_ref = pi.site_ref
   AND p.po_num = pi.po_num
JOIN vendaddr_mst va
    ON va.site_ref = p.site_ref
   AND va.vend_num = p.vend_num
WHERE m.site_ref = @Site
  AND m.ref_type = 'P'
  AND m.trans_type = 'R'
  AND m.qty > 0
  AND m.trans_date >= DATEADD(day, -@Lookback, GETDATE())
  AND p.vend_num LIKE N'{{vendor}}'
GROUP BY p.vend_num, va.name
ORDER BY on_time_pct, receipts DESC;

**Notes:** Verify on your SyteLine 9/10 database before relying on it: the stat values in use on poitem_mst (check against the PO line status list in your forms), and whether item_cost is in vendor currency for foreign vendors before summing open_value across vendors. Blanket POs carry due dates at release level, so read them by po_release. In a multi-site database every join must include site_ref. Run it on a reporting copy or a read-only login.

## Content

**Intro:** This generator writes a read-only SQL Server query for open and late purchase order lines in Infor SyteLine 9 and 10 (CloudSuite Industrial on-prem), plus a vendor on-time summary built from PO receipt transactions. Fill the fields, copy the SQL into SSMS or your report tool, and export the result to Excel.

**Sections:**

#### How to use it

**Body:** Enter the site_ref of the site you report on, then narrow by vendor, buyer and warehouse with LIKE patterns (% means all). Minimum days late controls the main filter: 0 returns lines due today or earlier, 7 returns lines at least a week late, and -9999 returns the whole open book including future due dates. Statuses default to O (Ordered); add P if planned lines matter to your buyers. Pick a sort order and copy block 1. Block 2 uses the same site and vendor filter and a lookback window to compute on-time delivery per vendor. Paste either block into SQL Server Management Studio connected to a reporting copy, or use it as the dataset of an SSRS report or an Excel data connection.

**Bullets:**

- Block 1: one row per open PO line or release, with open quantity, open value and days late
- Block 2: receipts in the lookback window per vendor, on-time count and percentage
- Both blocks join on site_ref, which is required in multi-site databases

#### Where the data lives in SyteLine

**Body:** The PO header is po_mst (po_num, vend_num, buyer, stat) and its lines and releases are poitem_mst (po_line, po_release, item, whse, qty_ordered, qty_received, item_cost, due_date, stat). The vendor name comes from vendaddr_mst. Receipts post to the material transaction table matltran_mst with ref_type P and trans_type R, where ref_num, ref_line_suf and ref_release point back to the PO line and release. The Purchase Orders and Purchase Order Lines forms read the same rows, so you can spot-check any line by opening it in the form. Open quantity is simply ordered minus received; returns and rejected quantities are not netted out in this version, which keeps the logic easy to audit.

**Bullets:**

- po_mst: header, vendor, buyer, status
- poitem_mst: line and release, quantities, cost, due date, status
- vendaddr_mst: vendor name and address
- matltran_mst: posted receipts used for on-time measurement

#### Worked example

**Body:** A buyer wants every line at least five days late in warehouse MAIN. Set days late to 5, warehouse to MAIN and keep status O. A line ordered for 400 pieces with 150 received at an item_cost of 3.20 returns qty_open 250 and open_value 800.00. If its due_date was ten days ago, days_late is 10. For the on-time block with a 90 day lookback, a vendor with 40 receipts of which 34 arrived on or before the PO line due date shows 85.0 percent. A receipt dated the same day as the due date counts as on time because the comparison casts both to date.

**Bullets:**

#### Gotchas and version notes

**Body:** Order and vendor numbers in SyteLine are often stored right-justified with leading spaces, so an exact match such as vend_num = 'V1001' can return nothing; use LIKE with a leading % or LTRIM. Blanket POs keep the agreement on the blanket line and the dated quantities on releases, so always read due_date at po_release level. On-time is measured against due_date; if your buyers maintain a promise date and want supplier performance against it, swap the column after confirming it in your schema. GETDATE returns server time; sites in other time zones may want the site-local date. Partial receipts create several receipt rows for one line, and each is scored separately.

**Bullets:**

**Cta:** If you want buyers to ask SyteLine which POs are late and why in plain English on your own servers, we can set up a private AI layer that runs queries like this read-only.

**Faq:**

#### How do I build a custom purchase order report in SyteLine?

**Answer:** Start from a tested SQL dataset like block 1, then publish it as an SSRS report or an Excel connection on a reporting copy. For a report inside SyteLine itself, wrap the logic in a stored procedure and register it as a report through the normal custom report process. Keep site_ref in every join and filter.

#### Can I export SyteLine PO reports to Excel?

**Answer:** Yes. Most SyteLine forms can export grid rows to Excel, which is fine for a quick list. For a repeatable report, connect Excel to SQL Server with a read-only login and paste the generated query as the data source, so the sheet refreshes with one click instead of manual exports.

#### Which table holds SyteLine PO line due dates?

**Answer:** poitem_mst holds due_date per line and release, together with qty_ordered, qty_received, item_cost and stat. The header po_mst holds vendor, buyer and header status. Join them on site_ref and po_num, and on po_line and po_release when matching to receipt transactions in matltran_mst.

#### Why does my vendor filter return no rows?

**Answer:** Vendor and order numbers are usually padded with leading spaces to a fixed width. An equality or a LIKE pattern without a leading % will miss them. Use LIKE N'%V1001' or compare LTRIM(p.vend_num) to the value you typed.

## Search terms

syteline purchase order report, syteline custom reports, export syteline reports to excel, syteline reporting, syteline open po query, syteline poitem_mst, syteline vendor on time delivery sql

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json