Other ERPsFree referencePDF, Excel, Word exportInfor VISUALDiscrete Manufacturing

Infor VISUAL Database Table Reference

Anyone writing a Crystal report or SQL query against VISUAL needs to know which table holds what. This reference lists the tables most reports touch, grouped by area, with the usual keys. Search by table name or by topic and export the list.

Download:

Table	Area	What it holds	Typical key or join
PART	Inventory	Part master: description, planning and costing defaults, buy or make flags	ID
PART_SITE	Inventory	Site level part settings in multi site installations	PART_ID, SITE_ID
PART_LOCATION	Inventory	On hand quantity by warehouse and location	PART_ID, WAREHOUSE_ID, LOCATION_ID
WAREHOUSE	Inventory	Warehouse definitions	ID
LOCATION	Inventory	Bin or location definitions inside a warehouse	WAREHOUSE_ID, ID
INVENTORY_TRANS	Inventory	Inventory transaction history for issues, receipts and adjustments	PART_ID, WORKORDER_BASE_ID
WORK_ORDER	Shop floor	Work order header, status, quantities and dates	TYPE, BASE_ID, LOT_ID, SPLIT_ID, SUB_ID
OPERATION	Shop floor	Routing operations on a work order	WORKORDER_BASE_ID and sequence number
REQUIREMENT	Shop floor	Material requirements on a work order operation	WORKORDER_BASE_ID, OPERATION_SEQ_NO, PIECE_NO
LABOR_TICKET	Shop floor	Labor reporting against work orders	EMPLOYEE_ID, WORKORDER_BASE_ID
SHOP_RESOURCE	Shop floor	Machines and work centers used in routings	ID
CUSTOMER	Sales	Customer master	ID
CUSTOMER_ORDER	Sales	Customer order header	ID
CUST_ORDER_LINE	Sales	Customer order lines	CUST_ORDER_ID, LINE_NO
CUST_LINE_DEL	Sales	Scheduled delivery releases for a customer order line	CUST_ORDER_ID, CUST_ORDER_LINE_NO
SHIPPER	Sales	Shipment header	PACKLIST_ID
SHIPPER_LINE	Sales	Shipment lines linked to order lines	PACKLIST_ID, LINE_NO
VENDOR	Purchasing	Vendor master	ID
PURCHASE_ORDER	Purchasing	Purchase order header	ID
PURC_ORDER_LINE	Purchasing	Purchase order lines	PURC_ORDER_ID, LINE_NO
RECEIVER	Purchasing	Receiving document header	ID
DEMAND_SUPPLY_LINK	Planning	Links supply such as work orders and purchase orders to the demand they cover	supply and demand base ids
RECEIVABLE	Finance	Accounts receivable invoices	INVOICE_ID
PAYABLE	Finance	Accounts payable invoices	VOUCHER_ID

Table names are shown as commonly seen in VISUAL Manufacturing databases. Column sets differ by version and customisation, so confirm names against your own database with a read only login before putting them in a production report.

Want these reports without writing SQL?

An optional walkthrough of asking questions of a VISUAL database in plain language, read only.

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

How to use the table list

Type a word such as order, labor or vendor in the search box to narrow the list. The Typical key column shows what you normally join on. Start from the header table for the document you care about, then step down to its lines or operations. Export to Excel if you want to annotate the list with your own custom tables and user defined fields for onboarding a new report writer.

Search by area to see all shop floor tables together
Check keys before joining to avoid duplicate rows
Add your own notes in the Excel export

How the shop floor tables fit together

A work order is identified by a composite of type, base id, lot, split and sub id, which is why joins between WORK_ORDER, OPERATION and REQUIREMENT need several columns. Operations hang off the work order, and material requirements hang off an operation. Labor tickets and inventory transactions point back to the work order base id. Missing a key column in the join is the most common reason a report shows multiplied quantities.

Common reporting mistakes

Reports go wrong in a few predictable ways. Joining on the base id alone ignores lot and split, so quantities repeat. Reading a header status without filtering for closed or cancelled documents inflates open counts. Querying live tables with heavy joins during shift hours slows the shop floor, so run large extracts on a replica or off hours.

Use all key columns for work order joins
Filter by status explicitly
Prefer a read only login
Document user defined fields separately

Frequently Asked Questions

Are these the exact names in every VISUAL version?

They are the names commonly seen, but versions and customisations differ. Treat the list as a starting map and confirm against your own database with a schema query before relying on a column name in production.

Where do user defined fields live?

VISUAL stores user defined fields in a separate user data structure keyed to the parent record rather than as columns on the table. Check your version documentation for the exact table and how it relates to each parent record.

Can I write to these tables directly?

Avoid it. Direct writes bypass the application business logic, costing and audit trail. Use the application, its supported import tools or the vendor API, and keep reporting logins read only.

Does this work for Crystal Reports?

Yes. Crystal reports connect to the same SQL Server database, so the table names and join keys apply. Use a command object or a view if you want to share a tested join across several reports.

If your team keeps rebuilding the same VISUAL reports, we can set up a read only natural language layer over the database so analysts can ask for them directly.

Talk to an Expert Explore More Free Tools

Related Tools

Developer Tools

More Other ERPs tools

See all

Back to All Tools