Customs & Internal Order Automation
A data pipeline that digitized cross-border customs handling between headquarters and foreign subsidiary — from ERP through automated customs declarations to FedEx shipment, with mobile data capture and built-in import/export compliance logic.
Standalone project — not part of the FastAPI portal. Built on SQL Server Agent, ERP configuration, and FedEx API integration.
The Problem
Every shipment to the foreign subsidiary required manual customs clearance. Staff had to create a consolidation delivery note in the ERP, key package dimensions and weights, export a commercial invoice as PDF, upload it into the FedEx shipping software, and re-enter shipment details. On the foreign side, staff had to actively look up each individual shipment in the ERP to track it. The process touched three separate systems with no automated handoff between them, and any mistake in document status, weight, or customs classification meant a delayed or rejected shipment.
Find out where your manual processes are costing you the most — and what to automate first.
Take the 2-minute process auditThe Approach
Core pipeline
- ERP-to-FedEx data pipeline
- A SQL Server Agent job pulls new delivery notes from the ERP into a customs staging table. A database view aggregates the data into the format the FedEx Stratus agent expects. FedEx Stratus picks up ready records automatically — no manual export or file transfer.
- Commercial invoice generation
- The ERP's print engine generates the commercial invoice from a custom template that auto-detects whether the shipment is a standard delivery or an RMA return and adjusts fields accordingly — one template handles both flows. The FedEx agent picks up the PDF directly from a network share, so no manual upload into the shipping software is needed.
- Customs regulation logic
- The pipeline encodes item-specific customs rules into the data transformation layer. Classification, valuation, and export declarations follow regulatory requirements built into the system logic rather than relying on staff to remember them.
- RMA handling
- To avoid duplication of customs logic, return shipments follow the same pipeline with adjusted parameters: shipment purpose is set to repair-and-return, the commercial invoice switches to a pro-forma format, and FedEx Ship Manager flags are configured automatically — no separate workflow needed.
Mobile data capture
- Mobile scanner integration
- Warehouse staff scan the delivery note barcode and the FedEx package barcode with a handheld scanner, enter weight and dimensions, and confirm. The data writes directly into the ERP and flows through the pipeline to FedEx — no manual entry to the ERP or re-entry into the shipping software.
Automation and notifications
- Automated status management
- After FedEx confirms shipment, the pipeline sets the dispatch status in the customs table automatically. The ERP's EDI status field is updated to prevent double-processing — a flag that previously had to be set by hand on every document.
- Daily summary email
- A scheduled SQL Agent job fires daily, compiles all shipped end-customer deliveries from that day's consolidation notes, and emails a tracking summary to the foreign warehouse and the central office. Both the consolidation and end-customer delivery notes are flagged as sent to prevent duplicate notifications.
Process design
- BPMN process documentation
- The entire customs flow — from delivery note creation through FedEx dispatch to email notification — is documented as a formal BPMN diagram covering three swim lanes: logistics staff, FedEx Ship Manager, and the automated SQL Server Agent layer. The diagram doubles as specification and operations reference.
- Troubleshooting runbook
- A troubleshooting guide covers the two most common failure modes: documents not appearing in FedEx and notification emails not arriving. Each scenario follows a decision tree from most likely cause to escalation, so staff can resolve issues without contacting the process owner.
Architecture
The Result
Lead time reduction
67% shorter customs processing time
Error reduction
86% fewer customs and shipping errors
Automation
Zero manual data transfer between ERP, customs database, and FedEx
Adoption
Production since November 2022, processing all export shipments