Infor VISUAL Open Customer Order Backlog Query Generator

-- 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 ('F','R')
  AND col.LINE_STATUS IN ('A')
  AND co.CUSTOMER_ID LIKE '%'
  AND col.ORDER_QTY > col.TOTAL_SHIPPED_QTY
  AND col.TOTAL_SHIPPED_QTY >= 0
  AND ISNULL(col.DESIRED_SHIP_DATE, co.DESIRED_SHIP_DATE) <= DATEADD(day, 90, GETDATE())
ORDER BY SHIP_DATE, co.ID, col.LINE_NO;

SELECT
    CONVERT(char(7), ISNULL(col.DESIRED_SHIP_DATE, co.DESIRED_SHIP_DATE), 120) 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 ('F','R')
  AND col.LINE_STATUS IN ('A')
  AND co.CUSTOMER_ID LIKE '%'
  AND col.ORDER_QTY > col.TOTAL_SHIPPED_QTY
  AND col.TOTAL_SHIPPED_QTY >= 0
  AND ISNULL(col.DESIRED_SHIP_DATE, co.DESIRED_SHIP_DATE) <= DATEADD(day, 90, GETDATE())
GROUP BY CONVERT(char(7), ISNULL(col.DESIRED_SHIP_DATE, co.DESIRED_SHIP_DATE), 120)
ORDER BY GROUP_KEY;

-- 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 ('F','R')
  AND col.LINE_STATUS IN ('A')
  AND co.CUSTOMER_ID LIKE '%'
ORDER BY d.CUST_ORDER_ID, d.CUST_ORDER_LINE_NO;