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

> Source: https://netray.co/tools/controlled-item-foreign-shipment-sql-query-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Fill in your table and column names to get three audit queries that catch unclassified items, controlled items on foreign orders and expired licenses.

## Category

developer

## Industry

Aerospace & Defense

## Audience

- ERP administrator
- Export compliance officer
- Database analyst

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Item master table

**Key:** item_table

**Type:** text

**Default:** item_master

**Help:** Your item or part table name

#### Item number column

**Key:** item_no

**Type:** text

**Default:** item_no

#### Jurisdiction column

**Key:** juris

**Type:** text

**Default:** export_jurisdiction

**Help:** Values such as ITAR, EAR, or blank

#### USML or ECCN column

**Key:** class_col

**Type:** text

**Default:** export_class

#### Sales order line table

**Key:** line_table

**Type:** text

**Default:** sales_order_line

#### Order number column

**Key:** order_no

**Type:** text

**Default:** order_no

#### Ship-to country column

**Key:** ship_country

**Type:** text

**Default:** ship_to_country

#### Your country code

**Key:** home_country

**Type:** text

**Default:** US

#### Order status column

**Key:** status_col

**Type:** text

**Default:** line_status

#### License table

**Key:** lic_table

**Type:** text

**Default:** export_license

#### License expiry column

**Key:** lic_expiry

**Type:** text

**Default:** expiry_date

#### License number column

**Key:** lic_no

**Type:** text

**Default:** license_no

#### High risk country codes

**Key:** watch

**Type:** text

**Default:** 'CN','RU','IR','KP','SY','CU'

**Help:** Illustrative only. Replace with your screened list

**Blocks:**

#### 1. Active items with no export classification

**Body:** SELECT i.{{item_no}}, i.{{juris}}, i.{{class_col}}
FROM {{item_table}} i
WHERE i.{{juris}} IS NULL
   OR i.{{juris}} = ''
   OR (i.{{juris}} <> 'EAR99' AND (i.{{class_col}} IS NULL OR i.{{class_col}} = ''))
ORDER BY i.{{item_no}};

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

**Body:** SELECT l.{{order_no}}, l.{{item_no}}, i.{{juris}}, i.{{class_col}}, l.{{ship_country}}
FROM {{line_table}} l
JOIN {{item_table}} i ON i.{{item_no}} = l.{{item_no}}
WHERE l.{{ship_country}} <> '{{home_country}}'
  AND i.{{juris}} IN ('ITAR', 'EAR')
  AND i.{{class_col}} <> 'EAR99'
  AND l.{{status_col}} NOT IN ('SHIPPED', 'CLOSED', 'CANCELLED')
ORDER BY l.{{ship_country}}, l.{{order_no}};

#### 3. Any open order to a high risk country

**Body:** SELECT l.{{order_no}}, l.{{item_no}}, l.{{ship_country}}
FROM {{line_table}} l
WHERE l.{{ship_country}} IN ({{watch}})
  AND l.{{status_col}} NOT IN ('SHIPPED', 'CLOSED', 'CANCELLED');

#### 4. Licenses expiring in the next 60 days

**Body:** SELECT lic.{{lic_no}}, lic.{{lic_expiry}}
FROM {{lic_table}} lic
WHERE lic.{{lic_expiry}} <= CURRENT_DATE + INTERVAL '60 days'
  AND lic.{{lic_expiry}} >= CURRENT_DATE
ORDER BY lic.{{lic_expiry}};

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use the generator

**Body:** 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

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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.

**Cta:** 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.

**Faq:**

#### Will these queries work on my ERP?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

erp export control sql query, find itar parts in erp, missing eccn query, foreign ship to controlled item report, export compliance sql report, license expiry query erp, export control audit sql, itar part flag report

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json