Developer ToolsFree generatorPDF, Excel, Word exportAerospace & Defense

SQL Query Generator: Controlled Items, Missing Classifications and Foreign Shipments

Most export compliance gaps in an ERP are data gaps: items without a classification, controlled parts on a foreign order, or a license that quietly expired. This generator writes four audit queries you can run against your reporting database.

Fill in

Item master table

Your item or part table name

Item number column

Jurisdiction column

Values such as ITAR, EAR, or blank

USML or ECCN column

Sales order line table

Order number column

Ship-to country column

Your country code

Order status column

License table

License expiry column

License number column

High risk country codes

Illustrative only. Replace with your screened list

Generated code

Free, optional. Reopen it on any device.

Download:

1. Active items with no export classification

SELECT i.item_no, i.export_jurisdiction, i.export_class
FROM item_master i
WHERE i.export_jurisdiction IS NULL
   OR i.export_jurisdiction = ''
   OR (i.export_jurisdiction <> 'EAR99' AND (i.export_class IS NULL OR i.export_class = ''))
ORDER BY i.item_no;

2. Open order lines for controlled items shipping outside the home country

SELECT l.order_no, l.item_no, i.export_jurisdiction, i.export_class, l.ship_to_country
FROM sales_order_line l
JOIN item_master i ON i.item_no = l.item_no
WHERE l.ship_to_country <> 'US'
  AND i.export_jurisdiction IN ('ITAR', 'EAR')
  AND i.export_class <> 'EAR99'
  AND l.line_status NOT IN ('SHIPPED', 'CLOSED', 'CANCELLED')
ORDER BY l.ship_to_country, l.order_no;

3. Any open order to a high risk country

SELECT l.order_no, l.item_no, l.ship_to_country
FROM sales_order_line l
WHERE l.ship_to_country IN ('CN','RU','IR','KP','SY','CU')
  AND l.line_status NOT IN ('SHIPPED', 'CLOSED', 'CANCELLED');

4. Licenses expiring in the next 60 days

SELECT lic.license_no, lic.expiry_date
FROM export_license lic
WHERE lic.expiry_date <= CURRENT_DATE + INTERVAL '60 days'
  AND lic.expiry_date >= CURRENT_DATE
ORDER BY lic.expiry_date;

These queries use generic table and column names. Map them to your ERP schema, and adjust date syntax: the interval expression is PostgreSQL style, while SQL Server uses DATEADD and Oracle uses SYSDATE + 60. Statuses and codes differ by system. Run read-only in a reporting copy first. The country list is a placeholder, so use your own screening sources.

Turn these queries into live ERP holds

Optional: we can review how classification data is stored in your ERP and propose a read-only exception report.

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

Was this tool useful?

How to use the generator

Enter the table and column names from your ERP schema. For SyteLine, Epicor, SAP or any other system, find where item master data, order lines and any license records live, and type those names in. The output updates as you edit. Copy it to your SQL client and run it against a read-only reporting copy. Export the results to Excel for the compliance owner, who decides what needs correction or a hold.

What each query catches

The first query lists items that have no jurisdiction or classification recorded, which is the root cause of most missed holds. The second joins open order lines to the item master and lists controlled items going outside your country, which are the lines that need a license check or an exemption review. The third shows any open order to the countries on your watch list. The fourth lists licenses about to expire so you can renew or stop shipping.

Missing classification: fix the item master first
Controlled item foreign shipments: confirm a license or exemption
Watch list countries: confirm screening and policy
Expiring licenses: renew before the next shipment

Limits of a query

A query can flag candidates but cannot decide. A foreign ship-to is not the whole story, because an export can occur through a freight forwarder, a foreign person employee or an emailed drawing. Also check bill-to, end user and intermediate consignee, not only ship-to. Use the output as an input to a documented review, and keep the review record alongside the query result.

Common mistakes

Running ad hoc queries on a production database at peak hours, trusting a blank classification column as meaning not controlled, and hard-coding a country list without a review date. Put the queries in a scheduled report, version them, and assign an owner. Where the ERP supports a hold flag on the item or order, use that for enforcement and keep the query as a safety net.

Frequently Asked Questions

Will these queries work on my ERP?

They work on any SQL database once the table and column names match your schema. Date syntax differs between PostgreSQL, SQL Server and Oracle, so adjust the expiry query. Test in a reporting copy first and confirm the status values your system uses.

Why check items with a blank classification?

A blank means the item was never classified, not that it is uncontrolled. Without a recorded jurisdiction and classification, no hold or license check can fire. Classifying the item master is usually the highest value control you can add.

Does a domestic ship-to mean there is no export?

No. A domestic delivery to a freight forwarder or a foreign person employee can still be an export or deemed export. Look at the end user and intermediate consignees as well as the ship-to address, and consider technical data transfers.

Is the high risk country list authoritative?

No. It is an illustrative default. Build your list from current official sources such as ITAR 126.1, the EAR country groups and OFAC sanctions programs, and put a review date on it.

If you want these checks as a scheduled, read-only report or a live hold inside your ERP, we can build it on your own infrastructure. The generator is free and complete as it stands.

Talk to an Expert Explore More Free Tools