# IMMEX Temporary Import Balance Aging SQL Generator

> Source: https://netray.co/tools/immex-temporary-import-balance-aging-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Build a SQL query that shows open temporary-import quantities by pedimento line, days to deadline and overdue status, from your own staging tables.

## Category

developer

## Erp

Any ERP

## Industry

Manufacturing

## Region

Mexico

## Audience

- Customs compliance
- ERP developer
- Trade compliance manager

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Imports table

**Key:** imports_table

**Type:** text

**Default:** stg_immex_imports

**Help:** Your own staging table of temporary-import lines

#### Returns table

**Key:** returns_table

**Type:** text

**Default:** stg_immex_returns

**Help:** Your own staging table of exports, returns or transfers that discharge imports

#### Permitted days on temporary import

**Key:** term_days

**Type:** number

**Default:** 548

**Help:** 548 is about 18 months; confirm the term that applies to each item category

#### Warning window

**Key:** warn_days

**Type:** number

**Default:** 60

**Help:** Flag lines expiring within this many days

#### Minimum open quantity to list

**Key:** min_open

**Type:** number

**Default:** 0

**Blocks:**

#### Aging query (PostgreSQL syntax)

**Body:** -- Assumes generic staging tables. Adjust column names to your ERP extract.
-- imports: pedimento_no, line_no, part_no, import_date, qty_imported
-- returns: import_pedimento_no, import_line_no, qty_discharged
WITH discharged AS (
  SELECT import_pedimento_no, import_line_no, SUM(qty_discharged) AS qty_discharged
  FROM {{returns_table}}
  GROUP BY import_pedimento_no, import_line_no
)
SELECT i.pedimento_no,
       i.line_no,
       i.part_no,
       i.import_date,
       i.qty_imported,
       COALESCE(d.qty_discharged, 0) AS qty_discharged,
       i.qty_imported - COALESCE(d.qty_discharged, 0) AS qty_open,
       i.import_date + INTERVAL '{{term_days}} days' AS legal_deadline,
       (i.import_date + INTERVAL '{{term_days}} days')::date - CURRENT_DATE AS days_left,
       CASE
         WHEN (i.import_date + INTERVAL '{{term_days}} days')::date < CURRENT_DATE THEN 'OVERDUE'
         WHEN (i.import_date + INTERVAL '{{term_days}} days')::date - CURRENT_DATE <= {{warn_days}} THEN 'EXPIRING'
         ELSE 'OK'
       END AS status
FROM {{imports_table}} i
LEFT JOIN discharged d
  ON d.import_pedimento_no = i.pedimento_no
 AND d.import_line_no = i.line_no
WHERE i.qty_imported - COALESCE(d.qty_discharged, 0) > {{min_open}}
ORDER BY legal_deadline ASC;

#### Variant: open balance by part number

**Body:** SELECT i.part_no,
       SUM(i.qty_imported - COALESCE(d.qty_discharged, 0)) AS qty_open,
       MIN(i.import_date + INTERVAL '{{term_days}} days') AS earliest_deadline
FROM {{imports_table}} i
LEFT JOIN (
  SELECT import_pedimento_no, import_line_no, SUM(qty_discharged) AS qty_discharged
  FROM {{returns_table}}
  GROUP BY import_pedimento_no, import_line_no
) d ON d.import_pedimento_no = i.pedimento_no AND d.import_line_no = i.line_no
GROUP BY i.part_no
HAVING SUM(i.qty_imported - COALESCE(d.qty_discharged, 0)) > {{min_open}}
ORDER BY earliest_deadline ASC;

**Notes:** Table and column names are generic placeholders, not a specific ERP schema. The permitted period differs by goods category under the IMMEX rules, and discharge is not always one-to-one with a pedimento line (scrap, waste, transfers and bill of materials yield matter). Use this as a planning report and confirm control rules with your customs broker.

## Content

**Intro:** Under an IMMEX program, temporarily imported inputs must be exported, returned or otherwise discharged within the permitted period. This generator gives trade compliance and ERP teams a starting query to age the open balance per pedimento line from their own data.

**Sections:**

#### How to use the query

**Body:** Load imports and discharges into two staging tables, or point the placeholders at views over your ERP's customs module. Set the term in days for your item category, choose a warning window and run the first query to see open quantity, the deadline and a status. The second query rolls the open balance up to part number so planners can see which materials need attention first. Schedule it weekly and send the OVERDUE and EXPIRING rows to the customs team.

**Bullets:**

- Keep one row per pedimento line, not per pedimento
- Match discharges to import lines with a stable key
- Include scrap and waste discharges in the returns table
- Review the term per goods category with your broker

#### Why the balance rarely ties out at first

**Body:** Raw quantities seldom match because finished goods consume inputs through the bill of materials, with yield loss, scrap and substitutions. The ERP knows the production quantity, but the customs record needs the equivalent input quantity discharged. Many first-time reports show large open balances that are really unrecorded discharges. Treat the first run as a data-quality exercise and fix the mapping from production orders and shipments to import lines.

#### Common mistakes

**Body:** Frequent errors include using calendar months as 30-day blocks, ignoring tolerance for transfers between IMMEX companies, and counting a line as discharged once the export document is created rather than when the export is validated. Another mistake is building the report on inventory balances instead of on customs documents, which hides lines that were consumed but never reported. Keep the report anchored on pedimento lines.

**Cta:** If your open IMMEX balances do not reconcile with production, we can help map consumption from your ERP to import lines in a read-only report your customs team can review.

**Faq:**

#### What is the permitted period for temporary imports?

**Answer:** It depends on the goods. Raw materials and components under IMMEX have historically had around 18 months, while machinery and other categories follow different rules. Confirm the term for each category with your customs broker and the current rules, then set the days field accordingly.

#### Can I run this on SQL Server or Oracle?

**Answer:** The syntax is PostgreSQL. For SQL Server replace the date arithmetic with DATEADD and DATEDIFF, and for Oracle use date plus a number of days. The CTE and join logic stays the same. Test the result on a small sample first.

#### Does this replace the IMMEX automated inventory control system?

**Answer:** No. Companies must keep the control records required by the customs authority, and the regulations specify the format and content. This query is an internal monitoring report built on your data, not a compliance filing or a replacement for those records.

#### What data do I need from the ERP?

**Answer:** Pedimento number and line, part number, import date and quantity for imports, plus export or return documents that reference the import line and the discharged quantity. If your ERP lacks that link, creating it is the main project, not the query.

## Search terms

immex temporary import balance sql, immex inventory control sql, immex 18 month deadline report, pedimento temporary import aging, immex annex 24 query, immex open balance report, mexico customs inventory aging, immex retorno reconciliation sql

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