# Infor VISUAL Open Customer Order Backlog Query Generator

> Source: https://netray.co/tools/infor-visual-open-customer-order-backlog-query-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Builds read-only SQL for open customer order lines in Infor VISUAL Manufacturing, a backlog summary grouped your way, and a delivery schedule view.

## Category

other-erp

## Erp

Infor VISUAL

## Audience

- VISUAL administrator
- Report writer
- Sales operations
- Controller

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Customer ID filter (LIKE)

**Key:** customer

**Type:** text

**Default:** %

#### Ship date cutoff (days from today)

**Key:** ship_days

**Type:** number

**Default:** 90

**Help:** Lines with a desired ship date up to this many days ahead. Use 0 for past due only.

#### Partially shipped lines

**Key:** partial

**Type:** select

**Options:**

- col.TOTAL_SHIPPED_QTY >= 0
- col.TOTAL_SHIPPED_QTY = 0

**Default:** col.TOTAL_SHIPPED_QTY >= 0

**Help:** The first option includes partially shipped lines, the second only untouched lines.

#### Order header statuses

**Key:** statuses

**Type:** text

**Default:** 'F','R'

**Help:** Quoted list. Confirm the open-type values used in your database.

#### Line statuses

**Key:** line_statuses

**Type:** text

**Default:** 'A'

#### Summary group by

**Key:** group_expr

**Type:** select

**Options:**

- co.CUSTOMER_ID
- col.PART_ID
- CONVERT(char(7), ISNULL(col.DESIRED_SHIP_DATE, co.DESIRED_SHIP_DATE), 120)

**Default:** CONVERT(char(7), ISNULL(col.DESIRED_SHIP_DATE, co.DESIRED_SHIP_DATE), 120)

**Help:** The third option groups by ship month (yyyy-mm).

**Blocks:**

#### 1. Open backlog detail

**Body:** -- Infor VISUAL open customer order lines (read-only login, verify column names on your version)
SELECT
    co.ID AS ORDER_ID,
    co.CUSTOMER_ID,
    co.STATUS,
    col.LINE_NO,
    col.PART_ID,
    col.LINE_STATUS,
    col.ORDER_QTY,
    col.TOTAL_SHIPPED_QTY,
    col.ORDER_QTY - col.TOTAL_SHIPPED_QTY AS OPEN_QTY,
    col.UNIT_PRICE,
    (col.ORDER_QTY - col.TOTAL_SHIPPED_QTY) * col.UNIT_PRICE AS OPEN_VALUE,
    ISNULL(col.DESIRED_SHIP_DATE, co.DESIRED_SHIP_DATE) AS SHIP_DATE
FROM CUSTOMER_ORDER co
JOIN CUST_ORDER_LINE col
    ON col.CUST_ORDER_ID = co.ID
WHERE co.STATUS IN ({{statuses}})
  AND col.LINE_STATUS IN ({{line_statuses}})
  AND co.CUSTOMER_ID LIKE '{{customer}}'
  AND col.ORDER_QTY > col.TOTAL_SHIPPED_QTY
  AND {{partial}}
  AND ISNULL(col.DESIRED_SHIP_DATE, co.DESIRED_SHIP_DATE) <= DATEADD(day, {{ship_days}}, GETDATE())
ORDER BY SHIP_DATE, co.ID, col.LINE_NO;

#### 2. Backlog summary

**Body:** SELECT
    {{group_expr}} AS GROUP_KEY,
    COUNT(*) AS OPEN_LINES,
    SUM(col.ORDER_QTY - col.TOTAL_SHIPPED_QTY) AS OPEN_QTY,
    SUM((col.ORDER_QTY - col.TOTAL_SHIPPED_QTY) * col.UNIT_PRICE) AS OPEN_VALUE
FROM CUSTOMER_ORDER co
JOIN CUST_ORDER_LINE col
    ON col.CUST_ORDER_ID = co.ID
WHERE co.STATUS IN ({{statuses}})
  AND col.LINE_STATUS IN ({{line_statuses}})
  AND co.CUSTOMER_ID LIKE '{{customer}}'
  AND col.ORDER_QTY > col.TOTAL_SHIPPED_QTY
  AND {{partial}}
  AND ISNULL(col.DESIRED_SHIP_DATE, co.DESIRED_SHIP_DATE) <= DATEADD(day, {{ship_days}}, GETDATE())
GROUP BY {{group_expr}}
ORDER BY GROUP_KEY;

#### 3. Delivery schedule lines for the same orders (optional)

**Body:** -- For lines with scheduled deliveries; inspect d.* to pick the date and quantity columns on your version
SELECT
    d.*,
    col.PART_ID,
    col.UNIT_PRICE
FROM CUST_LINE_DEL d
JOIN CUST_ORDER_LINE col
    ON col.CUST_ORDER_ID = d.CUST_ORDER_ID
   AND col.LINE_NO = d.CUST_ORDER_LINE_NO
JOIN CUSTOMER_ORDER co
    ON co.ID = col.CUST_ORDER_ID
WHERE co.STATUS IN ({{statuses}})
  AND col.LINE_STATUS IN ({{line_statuses}})
  AND co.CUSTOMER_ID LIKE '{{customer}}'
ORDER BY d.CUST_ORDER_ID, d.CUST_ORDER_LINE_NO;

**Notes:** The VISUAL schema is not publicly documented, so confirm on your database (sp_help 'CUST_ORDER_LINE'): ORDER_QTY, TOTAL_SHIPPED_QTY, UNIT_PRICE and DESIRED_SHIP_DATE on header and line, the STATUS and LINE_STATUS values your site uses, and the CUST_LINE_DEL columns. UNIT_PRICE is normally per selling unit of measure, so if lines use a selling UM different from stock, value with USER_ORDER_QTY and its shipped equivalent instead. Line discounts are not applied here. Use a read-only login.

## Content

**Intro:** This generator writes SQL Server queries for the open customer order backlog in Infor VISUAL Manufacturing 8 to 10: a line-level detail list, a summary grouped by customer, part or ship month, and an optional delivery schedule view. Paste them into SSMS, an Excel data connection or your report tool.

**Sections:**

#### How to use it

**Body:** Set a customer filter (% for all), how many days ahead of today to include by desired ship date, and whether partially shipped lines count. The status fields hold quoted value lists; the defaults treat firmed and released orders with active lines as open. Choose how the summary should group: customer for sales reviews, part for the master scheduler, or ship month for the finance backlog. Blocks 1 and 2 share the same filter, so the detail always reconciles to the summary. Block 3 is for customers on delivery schedules, where one order line has several dated releases.

**Bullets:**

- Block 1: one row per open line with open quantity, open value and ship date
- Block 2: open lines, quantity and value per group
- Block 3: delivery schedule rows for the same open orders

#### Where the data lives in VISUAL

**Body:** The order header is CUSTOMER_ORDER, keyed by ID, with CUSTOMER_ID, STATUS and a header DESIRED_SHIP_DATE. Lines are in CUST_ORDER_LINE, keyed by CUST_ORDER_ID and LINE_NO, with PART_ID, ORDER_QTY, TOTAL_SHIPPED_QTY, UNIT_PRICE, LINE_STATUS and an optional line-level DESIRED_SHIP_DATE. When a line has no date of its own the header date applies, which is why the query uses ISNULL to fall back. Scheduled deliveries live in CUST_LINE_DEL, keyed back to the line. The Customer Order Entry window shows the same values, so spot-check a few orders there before you circulate a backlog number.

**Bullets:**

#### Worked example

**Body:** With ship days at 30 and grouping by month, a line for 500 pieces with 200 shipped at a unit price of 12.50 gives OPEN_QTY 300 and OPEN_VALUE 3,750.00 and falls into the month of its desired ship date. Lines already past due are included because their date is earlier than the cutoff, so the first month in the summary usually carries the late backlog. If you want past due separately, run the detail with ship days set to 0 and compare. Changing the partial option to untouched lines only removes the 200-shipped line entirely.

**Bullets:**

#### Gotchas

**Body:** Units of measure are the most common source of a wrong backlog value: when a line is sold in a different unit than stock, ORDER_QTY is in stock units while the price is per selling unit, so value with the user-unit quantities instead. Discounts and order-level charges are not included, so this is gross line backlog. Header and line statuses work together; a closed header with an active line should not count, which the query handles by filtering both. Customizations and user-defined fields vary per site, so treat every column name as something to confirm.

**Bullets:**

**Cta:** If you want sales and planning to ask VISUAL for the backlog in plain English on your own servers, we can set up a private AI layer that runs queries like these read-only.

**Faq:**

#### Can I build this report in Infor VISUAL Report Builder instead?

**Answer:** Yes, if your users prefer to stay inside VISUAL. The same joins and filters apply: header to line on CUST_ORDER_ID, open quantity as ordered minus shipped, and both status filters. Validate the SQL first in SSMS, where it is quicker to compare against known orders, then rebuild it in the report tool.

#### What tables hold customer orders in Infor VISUAL Manufacturing?

**Answer:** CUSTOMER_ORDER holds headers, CUST_ORDER_LINE holds lines and CUST_LINE_DEL holds delivery schedule releases. Customer master data is in CUSTOMER. Shipments are recorded separately, but the line keeps a running total shipped quantity that this query uses.

#### Why does my backlog differ from the VISUAL screen total?

**Answer:** Usually units of measure, discounts, or status choices. Check whether the screen values use the selling unit and net price, whether held or firmed orders are included, and whether lines on closed headers are excluded. Run block 1 for one customer and compare line by line.

#### Is it safe to query the VISUAL database directly?

**Answer:** Read-only SELECT statements on a dedicated read-only login are standard practice, ideally on a reporting copy for large databases. Never update VISUAL tables directly; the application maintains related totals and audit records that SQL updates would skip.

## Search terms

infor visual report builder, infor visual erp, visual manufacturing erp system, infor visual manufacturing, infor visual backlog report, infor visual customer order sql, cust_order_line table

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