# Infor VISUAL Sales and Purchasing Columns Reference for Report Writers > Source: https://netray.co/tools/infor-visual-sales-and-purchasing-columns-reference > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Free Tools ## Short answer Searchable column list for VISUAL customer orders, shippers, purchase orders and receivers, with the joins and unit of measure rules Crystal and SSRS reports need. ## Category other-erp ## Erp Infor VISUAL ## Audience - Report writer - VISUAL administrator - ERP analyst ## Tool **Kind:** worksheet **Mode:** reference **Columns:** #### Table **Key:** table **Type:** text #### Column **Key:** column **Type:** text #### Meaning **Key:** meaning **Type:** text #### Join to **Key:** join **Type:** text #### Report tip **Key:** tip **Type:** text **Rows:** **Table:** CUSTOMER_ORDER **Column:** ID **Meaning:** Customer order number, header key **Join:** CUST_ORDER_LINE.CUST_ORDER_ID **Tip:** Header key is ID; lines use CUST_ORDER_ID **Table:** CUSTOMER_ORDER **Column:** CUSTOMER_ID **Meaning:** Customer the order is for **Join:** CUSTOMER.ID **Tip:** Join CUSTOMER for name and terms **Table:** CUSTOMER_ORDER **Column:** ORDER_DATE **Meaning:** Date the order was entered **Join:** - **Tip:** Bookings by month **Table:** CUSTOMER_ORDER **Column:** DESIRED_SHIP_DATE **Meaning:** Header desired ship date **Join:** - **Tip:** Lines can have their own date; report the line date **Table:** CUSTOMER_ORDER **Column:** STATUS **Meaning:** Header status code **Join:** - **Tip:** Decode on your database with SELECT STATUS, COUNT(*) FROM CUSTOMER_ORDER GROUP BY STATUS **Table:** CUSTOMER_ORDER **Column:** CUSTOMER_PO_REF **Meaning:** Customer's PO reference **Join:** - **Tip:** Show on acknowledgements and shipping reports **Table:** CUST_ORDER_LINE **Column:** CUST_ORDER_ID **Meaning:** Order the line belongs to **Join:** CUSTOMER_ORDER.ID **Tip:** Line key: CUST_ORDER_ID + LINE_NO **Table:** CUST_ORDER_LINE **Column:** LINE_NO **Meaning:** Line number **Join:** SHIPPER_LINE.CUST_ORDER_LINE_NO **Tip:** Shipper lines link with CUST_ORDER_LINE_NO, not LINE_NO **Table:** CUST_ORDER_LINE **Column:** PART_ID **Meaning:** Part ordered **Join:** PART.ID **Tip:** Can be blank for non-part lines; LEFT JOIN PART **Table:** CUST_ORDER_LINE **Column:** ORDER_QTY **Meaning:** Ordered quantity in stock U/M **Join:** - **Tip:** Open stock qty = ORDER_QTY - TOTAL_SHIPPED_QTY **Table:** CUST_ORDER_LINE **Column:** USER_ORDER_QTY **Meaning:** Ordered quantity in selling U/M **Join:** - **Tip:** Use with UNIT_PRICE for line value **Table:** CUST_ORDER_LINE **Column:** SELLING_UM **Meaning:** Selling unit of measure **Join:** - **Tip:** UNIT_PRICE is per this U/M **Table:** CUST_ORDER_LINE **Column:** TOTAL_SHIPPED_QTY **Meaning:** Quantity shipped to date, stock U/M **Join:** - **Tip:** Compare with ORDER_QTY, not USER_ORDER_QTY **Table:** CUST_ORDER_LINE **Column:** UNIT_PRICE **Meaning:** Price per selling unit **Join:** - **Tip:** Line value = USER_ORDER_QTY * UNIT_PRICE before discounts **Table:** CUST_ORDER_LINE **Column:** LINE_STATUS **Meaning:** Line status code **Join:** - **Tip:** Filter open lines on your database's open code **Table:** CUST_ORDER_LINE **Column:** DESIRED_SHIP_DATE **Meaning:** Line desired ship date **Join:** - **Tip:** Late = DESIRED_SHIP_DATE < GETDATE() and open qty > 0 **Table:** CUST_LINE_DEL **Column:** CUST_ORDER_ID **Meaning:** Order of the delivery schedule row **Join:** CUST_ORDER_LINE.CUST_ORDER_ID **Tip:** Only lines with delivery schedules have rows **Table:** CUST_LINE_DEL **Column:** CUST_ORDER_LINE_NO **Meaning:** Order line of the schedule row **Join:** CUST_ORDER_LINE.LINE_NO **Tip:** Join on CUST_ORDER_ID + CUST_ORDER_LINE_NO **Table:** CUST_LINE_DEL **Column:** DEL_SCHED_LINE_NO **Meaning:** Delivery schedule line number **Join:** - **Tip:** Key with order and line; one line can have many releases **Table:** CUST_LINE_DEL **Column:** DESIRED_SHIP_DATE **Meaning:** Ship date of the scheduled release **Join:** - **Tip:** For scheduled lines, lateness is per release, not per line **Table:** SHIPPER **Column:** PACKLIST_ID **Meaning:** Packlist (shipper) number, header key **Join:** SHIPPER_LINE.PACKLIST_ID **Tip:** One order can ship on many packlists **Table:** SHIPPER **Column:** CUST_ORDER_ID **Meaning:** Order shipped **Join:** CUSTOMER_ORDER.ID **Tip:** Header link to the order **Table:** SHIPPER **Column:** SHIPPED_DATE **Meaning:** Date shipped **Join:** - **Tip:** Use for shipments by day and on-time delivery **Table:** SHIPPER_LINE **Column:** PACKLIST_ID **Meaning:** Packlist the line belongs to **Join:** SHIPPER.PACKLIST_ID **Tip:** Line key: PACKLIST_ID + LINE_NO **Table:** SHIPPER_LINE **Column:** LINE_NO **Meaning:** Packlist line number **Join:** - **Tip:** Not the order line number **Table:** SHIPPER_LINE **Column:** CUST_ORDER_LINE_NO **Meaning:** Order line shipped **Join:** CUST_ORDER_LINE.LINE_NO **Tip:** Join with SHIPPER.CUST_ORDER_ID for the full order line key **Table:** SHIPPER_LINE **Column:** SHIPPED_QTY **Meaning:** Quantity shipped on this line, stock U/M **Join:** - **Tip:** On-time qty: SHIPPED_QTY where SHIPPED_DATE <= line desired date **Table:** PURCHASE_ORDER **Column:** ID **Meaning:** Purchase order number, header key **Join:** PURC_ORDER_LINE.PURC_ORDER_ID **Tip:** Header key is ID; lines use PURC_ORDER_ID **Table:** PURCHASE_ORDER **Column:** VENDOR_ID **Meaning:** Vendor **Join:** VENDOR.ID **Tip:** Join VENDOR for name **Table:** PURCHASE_ORDER **Column:** ORDER_DATE **Meaning:** Order date **Join:** - **Tip:** PO spend by month **Table:** PURCHASE_ORDER **Column:** STATUS **Meaning:** Header status code **Join:** - **Tip:** Decode with SELECT STATUS, COUNT(*) FROM PURCHASE_ORDER GROUP BY STATUS **Table:** PURC_ORDER_LINE **Column:** PURC_ORDER_ID **Meaning:** PO the line belongs to **Join:** PURCHASE_ORDER.ID **Tip:** Line key: PURC_ORDER_ID + LINE_NO **Table:** PURC_ORDER_LINE **Column:** LINE_NO **Meaning:** Line number **Join:** RECEIVER_LINE.PURC_ORDER_LINE_NO **Tip:** Receiver lines link with PURC_ORDER_LINE_NO **Table:** PURC_ORDER_LINE **Column:** PART_ID **Meaning:** Part purchased **Join:** PART.ID **Tip:** Blank for service or non-part lines; LEFT JOIN PART **Table:** PURC_ORDER_LINE **Column:** ORDER_QTY **Meaning:** Ordered quantity in stock U/M **Join:** - **Tip:** Open stock qty = ORDER_QTY - TOTAL_RECEIVED_QTY **Table:** PURC_ORDER_LINE **Column:** USER_ORDER_QTY **Meaning:** Ordered quantity in purchase U/M **Join:** - **Tip:** Use with UNIT_PRICE for line value **Table:** PURC_ORDER_LINE **Column:** PURCHASE_UM **Meaning:** Purchase unit of measure **Join:** - **Tip:** UNIT_PRICE is per this U/M **Table:** PURC_ORDER_LINE **Column:** TOTAL_RECEIVED_QTY **Meaning:** Quantity received to date, stock U/M **Join:** - **Tip:** Compare with ORDER_QTY **Table:** PURC_ORDER_LINE **Column:** UNIT_PRICE **Meaning:** Price per purchase unit **Join:** - **Tip:** Line value = USER_ORDER_QTY * UNIT_PRICE **Table:** PURC_ORDER_LINE **Column:** DESIRED_RECV_DATE **Meaning:** Line desired receive date **Join:** - **Tip:** Late = DESIRED_RECV_DATE < GETDATE() and open qty > 0 **Table:** PURC_ORDER_LINE **Column:** LINE_STATUS **Meaning:** Line status code **Join:** - **Tip:** Filter open lines on your database's open code **Table:** PURC_LINE_DEL **Column:** PURC_ORDER_ID **Meaning:** PO of the delivery schedule row **Join:** PURC_ORDER_LINE.PURC_ORDER_ID **Tip:** Only scheduled lines have rows **Table:** PURC_LINE_DEL **Column:** PURC_ORDER_LINE_NO **Meaning:** PO line of the schedule row **Join:** PURC_ORDER_LINE.LINE_NO **Tip:** Join on PURC_ORDER_ID + PURC_ORDER_LINE_NO **Table:** PURC_LINE_DEL **Column:** DEL_SCHED_LINE_NO **Meaning:** Delivery schedule line number **Join:** - **Tip:** Key with PO and line **Table:** RECEIVER **Column:** ID **Meaning:** Receiver number, header key **Join:** RECEIVER_LINE.RECEIVER_ID **Tip:** One PO can have many receivers **Table:** RECEIVER **Column:** PURC_ORDER_ID **Meaning:** PO received against **Join:** PURCHASE_ORDER.ID **Tip:** Header link to the PO **Table:** RECEIVER **Column:** RECEIVED_DATE **Meaning:** Date received **Join:** - **Tip:** Use for vendor on-time delivery **Table:** RECEIVER_LINE **Column:** RECEIVER_ID **Meaning:** Receiver the line belongs to **Join:** RECEIVER.ID **Tip:** Line key: RECEIVER_ID + LINE_NO **Table:** RECEIVER_LINE **Column:** PURC_ORDER_LINE_NO **Meaning:** PO line received **Join:** PURC_ORDER_LINE.LINE_NO **Tip:** Join with RECEIVER.PURC_ORDER_ID for the full PO line key **Table:** RECEIVER_LINE **Column:** RECEIVED_QTY **Meaning:** Quantity received, stock U/M **Join:** - **Tip:** Sum per PO line to reconcile TOTAL_RECEIVED_QTY **Notes:** For VISUAL Manufacturing on SQL Server; table keys match the VISUAL database table reference. Columns differ by version and customisation and the schema is not published openly, so confirm each column before you use it: SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN ('CUSTOMER_ORDER','CUST_ORDER_LINE','SHIPPER','SHIPPER_LINE','PURCHASE_ORDER','PURC_ORDER_LINE','RECEIVER','RECEIVER_LINE') ORDER BY TABLE_NAME, ORDINAL_POSITION; Status letters are site data; decode them with the GROUP BY queries shown in the tips. ## Content **Intro:** Most VISUAL reports join the same ten sales and purchasing tables, and most broken reports get one key or one unit of measure wrong. This reference lists the columns those reports use, with the join and a tip for each, for Crystal, SSRS and ad hoc SQL against VISUAL on SQL Server. **Sections:** #### How the tables chain together **Body:** On the sales side, CUSTOMER_ORDER is keyed by ID and CUST_ORDER_LINE by CUST_ORDER_ID and LINE_NO. Delivery schedules in CUST_LINE_DEL add DEL_SCHED_LINE_NO. Shipments are SHIPPER, keyed by PACKLIST_ID with CUST_ORDER_ID on the header, and SHIPPER_LINE, which points to the order line through CUST_ORDER_LINE_NO. Purchasing mirrors this: PURCHASE_ORDER by ID, PURC_ORDER_LINE by PURC_ORDER_ID and LINE_NO, PURC_LINE_DEL for schedules, and RECEIVER with RECEIVER_LINE pointing back through PURC_ORDER_LINE_NO. The packlist and receiver line numbers are their own sequences, not order line numbers, which is the join people get wrong most. **Bullets:** - Order line: CUST_ORDER_ID + LINE_NO - Shipment to order line: SHIPPER.CUST_ORDER_ID + SHIPPER_LINE.CUST_ORDER_LINE_NO - PO line: PURC_ORDER_ID + LINE_NO - Receipt to PO line: RECEIVER.PURC_ORDER_ID + RECEIVER_LINE.PURC_ORDER_LINE_NO #### Stock versus user units of measure **Body:** VISUAL stores two quantities on order lines. ORDER_QTY is in the part's stock unit of measure, and USER_ORDER_QTY is in the selling or purchase unit shown on the order, SELLING_UM or PURCHASE_UM. UNIT_PRICE is per the user unit, so line value is USER_ORDER_QTY times UNIT_PRICE. Shipped and received totals, TOTAL_SHIPPED_QTY and TOTAL_RECEIVED_QTY, are in stock units, so open quantity is ORDER_QTY minus the total. Mixing the two is safe only for parts where both units are the same, which is why a report can look right for months and then break on the first part bought by the box. #### Worked example: late customer order lines **Body:** Select from CUST_ORDER_LINE l joined to CUSTOMER_ORDER h on h.ID = l.CUST_ORDER_ID, then CUSTOMER c on c.ID = h.CUSTOMER_ID. Return l.CUST_ORDER_ID, l.LINE_NO, l.PART_ID, l.ORDER_QTY - l.TOTAL_SHIPPED_QTY AS OPEN_QTY and l.DESIRED_SHIP_DATE. Filter OPEN_QTY above zero, the open line status your database uses and l.DESIRED_SHIP_DATE before GETDATE(). For lines with delivery schedules, swap the line date for CUST_LINE_DEL dates so each release is judged separately and partial releases are not hidden. The same pattern on PURC_ORDER_LINE with DESIRED_RECV_DATE gives the late PO list. #### Gotchas **Body:** Status codes are single letters whose use varies by site, so decode them on your own database rather than copying a filter from another company. Lines without a part, such as freight or service, drop out of an inner join to PART. Summing SHIPPER_LINE quantities by order without the order key on SHIPPER mixes orders that share line numbers. Report Builder and Crystal joins created by dragging tables often link on the wrong line column, so check every join against this table. Run heavy reports on a reporting copy. **Cta:** If your team wants to ask VISUAL about late orders and open POs in plain English on your own servers, we can set up a read-only AI layer over these tables. **Faq:** #### What is Infor VISUAL? **Answer:** Infor VISUAL, often called VISUAL Manufacturing, is an ERP for make-to-order, engineer-to-order and job shop manufacturers. It covers quoting, customer orders, work orders and scheduling, purchasing, inventory and accounting, usually on SQL Server on-prem, which is why many reports are written in SQL against its tables. #### Does Infor VISUAL have a report builder? **Answer:** VISUAL includes its own reporting options and many sites also use Crystal Reports or SSRS against the database. Whatever the tool, the report is only as right as its joins, so build the query once against the keys in this table, test it on a copy and reuse it as a view or command. #### Why does my VISUAL open order quantity look wrong? **Answer:** Usually USER_ORDER_QTY, in the selling unit, is compared with TOTAL_SHIPPED_QTY, in the stock unit. Compare ORDER_QTY with TOTAL_SHIPPED_QTY instead. Also check delivery schedule lines and whether partial shipments spread over several packlists are all included in the shipped total. #### How do I link a VISUAL receiver to a PO line? **Answer:** Join RECEIVER_LINE to RECEIVER on RECEIVER_ID, then to PURC_ORDER_LINE on RECEIVER.PURC_ORDER_ID = PURC_ORDER_ID and RECEIVER_LINE.PURC_ORDER_LINE_NO = LINE_NO. RECEIVER_LINE.LINE_NO is the receiver's own sequence and does not match the PO line, so joining on it links receipts to the wrong lines. ## Search terms infor visual report builder, infor visual erp system, what is infor visual, infor visual manufacturing software, visual cust_order_line columns, visual purc_order_line table, infor visual shipper_line, visual crystal reports sql --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json