Developer ToolsFree generatorPDF, Excel, Word exportInfor VISUALDiscrete Manufacturing

Infor VISUAL SQL Query Generator

Most VISUAL report requests start the same way: show me the open work orders that are late. This generator writes that SQL Server query for you from a few fields, so you can paste it into SSMS or a Crystal Reports command.

Fill in

Work order status

U unreleased, F firmed, R released, C closed, X cancelled.

Want dates up to (days from today)

0 means want date up to today, which finds late orders.

Part ID starts with

Maximum rows

Generated code

Download:

Late and open work orders

-- Work orders by status with a want date on or before a cutoff
-- Verify table and column names against your VISUAL version before use
SELECT TOP 200
    wo.BASE_ID,
    wo.LOT_ID,
    wo.SPLIT_ID,
    wo.SUB_ID,
    wo.PART_ID,
    wo.STATUS,
    wo.DESIRED_QTY,
    wo.RECEIVED_QTY,
    wo.DESIRED_QTY - wo.RECEIVED_QTY AS QTY_REMAINING,
    wo.DESIRED_WANT_DATE
FROM WORK_ORDER wo
WHERE wo.TYPE = 'W'
  AND wo.STATUS = 'R'
  AND wo.PART_ID LIKE '[Part ID starts with]%'
  AND wo.DESIRED_WANT_DATE <= DATEADD(day, 0, GETDATE())
ORDER BY wo.DESIRED_WANT_DATE, wo.BASE_ID, wo.LOT_ID, wo.SPLIT_ID, wo.SUB_ID;

Table and column names shown (WORK_ORDER, BASE_ID, LOT_ID, SPLIT_ID, SUB_ID, PART_ID, STATUS, DESIRED_QTY, RECEIVED_QTY, DESIRED_WANT_DATE, TYPE) are common in VISUAL databases but can differ by version and customization. Run on a test or read only copy first, and never run ad hoc queries during peak transaction times.

Plain English questions over VISUAL

Optional: see how a read-only assistant can write and show queries like this one on your VISUAL data.

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

How to use the generator

Pick the status you want, set how many days from today the want date should be included, and optionally limit by the start of a part ID. The query appears on the right and updates as you type. Copy it into a query tool connected to a read only copy or reporting database. With a days value of 0 you get orders whose want date is today or earlier, which are your late orders. A positive value looks ahead, which is useful for a weekly shortage meeting.

How the query is built

VISUAL identifies a work order by Base ID, Lot ID, Split ID and Sub ID, so the query returns and sorts on all four. The type filter keeps only work orders, since the same table can hold other records. Remaining quantity is desired minus received. The want date comparison uses DATEADD against the current date, which keeps the query reusable without editing dates by hand. The TOP clause protects you from accidentally returning an entire table.

Join and sort on all four ID parts.
Filter on type and status explicitly.
Use DATEADD so dates are relative.
Cap rows while testing.

Extending the query

To see customer demand behind an order, join to the relevant customer order tables, or add operation and requirement tables to find the blocking material. Add one join at a time and re check row counts, because joins on one to many tables multiply rows and inflate totals. If you need cost, be careful to pick the right cost fields for your costing method, as covered in the VISUAL costing method reference.

Common mistakes

The classic error is joining only on Base ID and merging lots and sub orders. Another is running heavy queries against the live database during production hours. A third is trusting column names from memory. Always check the schema of your own database, because customizations and versions change it. Treat the generated SQL as a starting draft that you review.

Frequently Asked Questions

Will this work on my VISUAL version?

The table and column names are common across many installations, but versions and customizations differ. Check your schema in SQL Server Management Studio before using the query, and test on a non production copy first.

Is any of my data sent to Netray?

No. The generator runs in your browser and only substitutes your entries into text. Nothing is executed against a database and nothing is stored on a server by this tool.

Why does the query sort by four ID columns?

A VISUAL work order is identified by Base ID, Lot ID, Split ID and Sub ID together. Sorting on all four keeps lots, splits and sub assemblies in a stable order and avoids confusing mixed results.

Can I use this query in Crystal Reports?

Yes, as a command or a view definition, though you may prefer to convert the literals into parameters. Keep the same ID columns, and test the report with a small row cap before publishing it.

If you would rather ask questions like which work orders are late and why in plain English, with the SQL shown for review, we can set up a read-only assistant over your VISUAL database.

Talk to an Expert Explore More Free Tools

Related Tools

Other ERPs

More Developer Tools tools

See all

Back to All Tools