Other ERPsFree referencePDF, Excel, Word exportInfor VISUALAll Infor VISUAL tools

Infor VISUAL Sales and Purchasing Columns Reference for Report Writers

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.

Download:

Table	Column	Meaning	Join to	Report tip
CUSTOMER_ORDER	ID	Customer order number, header key	CUST_ORDER_LINE.CUST_ORDER_ID	Header key is ID; lines use CUST_ORDER_ID
CUSTOMER_ORDER	CUSTOMER_ID	Customer the order is for	CUSTOMER.ID	Join CUSTOMER for name and terms
CUSTOMER_ORDER	ORDER_DATE	Date the order was entered	-	Bookings by month
CUSTOMER_ORDER	DESIRED_SHIP_DATE	Header desired ship date	-	Lines can have their own date; report the line date
CUSTOMER_ORDER	STATUS	Header status code	-	Decode on your database with SELECT STATUS, COUNT(*) FROM CUSTOMER_ORDER GROUP BY STATUS
CUSTOMER_ORDER	CUSTOMER_PO_REF	Customer's PO reference	-	Show on acknowledgements and shipping reports
CUST_ORDER_LINE	CUST_ORDER_ID	Order the line belongs to	CUSTOMER_ORDER.ID	Line key: CUST_ORDER_ID + LINE_NO
CUST_ORDER_LINE	LINE_NO	Line number	SHIPPER_LINE.CUST_ORDER_LINE_NO	Shipper lines link with CUST_ORDER_LINE_NO, not LINE_NO
CUST_ORDER_LINE	PART_ID	Part ordered	PART.ID	Can be blank for non-part lines; LEFT JOIN PART
CUST_ORDER_LINE	ORDER_QTY	Ordered quantity in stock U/M	-	Open stock qty = ORDER_QTY - TOTAL_SHIPPED_QTY
CUST_ORDER_LINE	USER_ORDER_QTY	Ordered quantity in selling U/M	-	Use with UNIT_PRICE for line value
CUST_ORDER_LINE	SELLING_UM	Selling unit of measure	-	UNIT_PRICE is per this U/M
CUST_ORDER_LINE	TOTAL_SHIPPED_QTY	Quantity shipped to date, stock U/M	-	Compare with ORDER_QTY, not USER_ORDER_QTY
CUST_ORDER_LINE	UNIT_PRICE	Price per selling unit	-	Line value = USER_ORDER_QTY * UNIT_PRICE before discounts
CUST_ORDER_LINE	LINE_STATUS	Line status code	-	Filter open lines on your database's open code
CUST_ORDER_LINE	DESIRED_SHIP_DATE	Line desired ship date	-	Late = DESIRED_SHIP_DATE < GETDATE() and open qty > 0
CUST_LINE_DEL	CUST_ORDER_ID	Order of the delivery schedule row	CUST_ORDER_LINE.CUST_ORDER_ID	Only lines with delivery schedules have rows
CUST_LINE_DEL	CUST_ORDER_LINE_NO	Order line of the schedule row	CUST_ORDER_LINE.LINE_NO	Join on CUST_ORDER_ID + CUST_ORDER_LINE_NO
CUST_LINE_DEL	DEL_SCHED_LINE_NO	Delivery schedule line number	-	Key with order and line; one line can have many releases
CUST_LINE_DEL	DESIRED_SHIP_DATE	Ship date of the scheduled release	-	For scheduled lines, lateness is per release, not per line
SHIPPER	PACKLIST_ID	Packlist (shipper) number, header key	SHIPPER_LINE.PACKLIST_ID	One order can ship on many packlists
SHIPPER	CUST_ORDER_ID	Order shipped	CUSTOMER_ORDER.ID	Header link to the order
SHIPPER	SHIPPED_DATE	Date shipped	-	Use for shipments by day and on-time delivery
SHIPPER_LINE	PACKLIST_ID	Packlist the line belongs to	SHIPPER.PACKLIST_ID	Line key: PACKLIST_ID + LINE_NO
SHIPPER_LINE	LINE_NO	Packlist line number	-	Not the order line number
SHIPPER_LINE	CUST_ORDER_LINE_NO	Order line shipped	CUST_ORDER_LINE.LINE_NO	Join with SHIPPER.CUST_ORDER_ID for the full order line key
SHIPPER_LINE	SHIPPED_QTY	Quantity shipped on this line, stock U/M	-	On-time qty: SHIPPED_QTY where SHIPPED_DATE <= line desired date
PURCHASE_ORDER	ID	Purchase order number, header key	PURC_ORDER_LINE.PURC_ORDER_ID	Header key is ID; lines use PURC_ORDER_ID
PURCHASE_ORDER	VENDOR_ID	Vendor	VENDOR.ID	Join VENDOR for name
PURCHASE_ORDER	ORDER_DATE	Order date	-	PO spend by month
PURCHASE_ORDER	STATUS	Header status code	-	Decode with SELECT STATUS, COUNT(*) FROM PURCHASE_ORDER GROUP BY STATUS
PURC_ORDER_LINE	PURC_ORDER_ID	PO the line belongs to	PURCHASE_ORDER.ID	Line key: PURC_ORDER_ID + LINE_NO
PURC_ORDER_LINE	LINE_NO	Line number	RECEIVER_LINE.PURC_ORDER_LINE_NO	Receiver lines link with PURC_ORDER_LINE_NO
PURC_ORDER_LINE	PART_ID	Part purchased	PART.ID	Blank for service or non-part lines; LEFT JOIN PART
PURC_ORDER_LINE	ORDER_QTY	Ordered quantity in stock U/M	-	Open stock qty = ORDER_QTY - TOTAL_RECEIVED_QTY
PURC_ORDER_LINE	USER_ORDER_QTY	Ordered quantity in purchase U/M	-	Use with UNIT_PRICE for line value
PURC_ORDER_LINE	PURCHASE_UM	Purchase unit of measure	-	UNIT_PRICE is per this U/M
PURC_ORDER_LINE	TOTAL_RECEIVED_QTY	Quantity received to date, stock U/M	-	Compare with ORDER_QTY
PURC_ORDER_LINE	UNIT_PRICE	Price per purchase unit	-	Line value = USER_ORDER_QTY * UNIT_PRICE
PURC_ORDER_LINE	DESIRED_RECV_DATE	Line desired receive date	-	Late = DESIRED_RECV_DATE < GETDATE() and open qty > 0
PURC_ORDER_LINE	LINE_STATUS	Line status code	-	Filter open lines on your database's open code
PURC_LINE_DEL	PURC_ORDER_ID	PO of the delivery schedule row	PURC_ORDER_LINE.PURC_ORDER_ID	Only scheduled lines have rows
PURC_LINE_DEL	PURC_ORDER_LINE_NO	PO line of the schedule row	PURC_ORDER_LINE.LINE_NO	Join on PURC_ORDER_ID + PURC_ORDER_LINE_NO
PURC_LINE_DEL	DEL_SCHED_LINE_NO	Delivery schedule line number	-	Key with PO and line
RECEIVER	ID	Receiver number, header key	RECEIVER_LINE.RECEIVER_ID	One PO can have many receivers
RECEIVER	PURC_ORDER_ID	PO received against	PURCHASE_ORDER.ID	Header link to the PO
RECEIVER	RECEIVED_DATE	Date received	-	Use for vendor on-time delivery
RECEIVER_LINE	RECEIVER_ID	Receiver the line belongs to	RECEIVER.ID	Line key: RECEIVER_ID + LINE_NO
RECEIVER_LINE	PURC_ORDER_LINE_NO	PO line received	PURC_ORDER_LINE.LINE_NO	Join with RECEIVER.PURC_ORDER_ID for the full PO line key
RECEIVER_LINE	RECEIVED_QTY	Quantity received, stock U/M	-	Sum per PO line to reconcile TOTAL_RECEIVED_QTY

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.

Get your VISUAL report joins checked

Send us the SQL or Crystal command behind a sales or purchasing report and we will check the keys and units of measure against your VISUAL version.

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

Was this tool useful?

How the tables chain together

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.

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

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

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

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.

Frequently Asked Questions

What is Infor VISUAL?

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?

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?

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?

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.

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.

Talk to an Expert Explore More Free Tools