IMMEX Temporary Import Balance Aging SQL Generator

-- 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 stg_immex_returns
  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 '548 days' AS legal_deadline,
       (i.import_date + INTERVAL '548 days')::date - CURRENT_DATE AS days_left,
       CASE
         WHEN (i.import_date + INTERVAL '548 days')::date < CURRENT_DATE THEN 'OVERDUE'
         WHEN (i.import_date + INTERVAL '548 days')::date - CURRENT_DATE <= 60 THEN 'EXPIRING'
         ELSE 'OK'
       END AS status
FROM stg_immex_imports 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) > 0
ORDER BY legal_deadline ASC;

SELECT i.part_no,
       SUM(i.qty_imported - COALESCE(d.qty_discharged, 0)) AS qty_open,
       MIN(i.import_date + INTERVAL '548 days') AS earliest_deadline
FROM stg_immex_imports i
LEFT JOIN (
  SELECT import_pedimento_no, import_line_no, SUM(qty_discharged) AS qty_discharged
  FROM stg_immex_returns
  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)) > 0
ORDER BY earliest_deadline ASC;