EDPMS / IDPMS reconciliation, automated in Business Central

Every export shipment a pharma company makes leaves behind a compliance tail: a shipping bill filed at customs, a remittance that arrives weeks or months later through the AD bank, and a closing event — the eBRC — that tells the RBI the money actually came in. Between the shipping bill and the eBRC lies a 9-month window. Miss it, and the exporter lands on the bank's caution list. Lose track of enough of them, and export benefits dry up and future remittances get blocked. Yet in most mid-market companies, the entire chain is tracked in a spreadsheet — if it's tracked at all.

This article is about how I closed that gap by designing the full EDPMS/IDPMS reconciliation workflow directly into Microsoft Dynamics 365 Business Central, with a lightweight Python service that auto-fetches shipping bills from the finance mailbox and pushes them into BC as they arrive. The result: zero manual data entry for shipping bill tagging, real-time visibility into open realisations, and one-click eBRC status for the AD bank.

What EDPMS and IDPMS actually are

The Export Data Processing and Monitoring System (EDPMS) is an RBI-mandated platform that receives every shipping bill filed at Indian customs. When your freight forwarder files a shipping bill through ICEGATE, the data flows automatically into EDPMS. From that point, the clock starts: the AD bank (your authorised dealer bank) is required to ensure that the corresponding export realisation — the inward foreign remittance — arrives and is matched against that shipping bill within the prescribed period. The bank then issues an eBRC (electronic Bank Realisation Certificate), which formally closes the entry in EDPMS.

The Import Data Processing and Monitoring System (IDPMS) is the mirror image for imports — every bill of entry must be matched to an outward remittance, and the AD bank closes it once payment is confirmed. The compliance burden is identical; the direction of money is reversed.

Why does it matter so acutely? If a shipping bill remains open in EDPMS past the realisation window without a valid extension or write-off application, the exporter is flagged. Banks are required to place such exporters on a caution list, which effectively blocks further export credit, FEMA benefits, and in some cases remittances until the backlog is cleared. For a pharmaceutical company with continuous exports and multiple buyer credits running simultaneously, the cost of getting on that list is disproportionate to the effort required to avoid it.

The manual pain — why spreadsheets fail here

The typical manual process looks like this: the logistics team files the shipping bill and emails a PDF to finance. Someone — usually the same person closing the books — manually enters the shipping bill number, date, and FOB value into a tracker. When the bank's inward remittance advice arrives (sometimes weeks later, sometimes with a partial amount), someone else manually tries to match it to the right invoice, which may cover multiple shipments. The FIRC (Foreign Inward Remittance Certificate) arrives separately. Then someone has to chase the AD bank to confirm that the eBRC has been generated and the EDPMS entry is closed.

Each step in that chain has a different owner, a different document format, and a different timeline. The failure modes are predictable:

• Shipping bills not tagged to invoices — the logistics team uses shipment references, finance uses invoice numbers, and the two never meet in the same row of the spreadsheet.
• Partial realisations not tracked — a buyer remits 90% against a disputed quantity; the shipping bill stays open; no one notices for four months.
• The 9-month window sneaks up — without ageing buckets against the shipping bill date (not the invoice date), the deadline is invisible until it's already past.
• eBRC follow-up is manual and ad hoc — there's no system prompt; it depends on someone remembering to chase the bank.

Designing the data model in Business Central

The starting point was mapping the full compliance chain as a data flow and then deciding where each field would live in BC. The chain is:

Sales Order → Posted Sales Invoice → Shipping Bill → Inward Remittance → FIRC → eBRC → EDPMS closed.

BC's standard Sales module handles the first two legs well. The gap starts at the shipping bill. I extended the data model using a combination of custom fields on the Posted Sales Invoice header and a dedicated Export Compliance table (Export Compliance Entry) that carries the full tail of the chain.

The custom fields added to the Posted Sales Invoice header are minimal but critical:

• Shipping Bill No. — alphanumeric, populated by the Python service on arrival
• Shipping Bill Date — date field, parsed from the ICEGATE email
• Port Code — maps to the customs location code
• AD Code — the bank's AD code against which the remittance will be matched
• Realisation Due Date — calculated as Shipping Bill Date + 270 days (9 months), surfaced as a warning field
• Realisation Status — an option field: Open / Partial / Realised / eBRC Issued / Write-Off

The Export Compliance Entry table carries the full lifecycle — one row per shipping bill — with foreign keys to both the Sales Invoice and (once matched) the Bank Ledger Entry for the inward remittance. The FOB value in the invoice currency, the realised amount, and the eBRC number all live here. This separation matters: a single remittance can cover multiple shipping bills, and a single shipping bill can be partially realised across multiple remittances. The entry table handles both many-to-one and one-to-many cleanly.

BC Dimensions carry the port code and AD code so they flow through to the GL and can be sliced in financial reporting without cluttering the chart of accounts. A Compliance Status FactBox on the Posted Sales Invoice page surfaces the realisation status inline — no need to navigate to a separate table to see whether a given invoice's shipping bill is open or closed.

The Python automation: email-to-BC pipeline

The biggest friction point in the manual process was the gap between the shipping bill being filed and the data reaching finance. ICEGATE sends a confirmation email to the exporter (and often to the freight forwarder) when a shipping bill is filed and when it clears examination. Those emails contain everything you need: the SB number, the date, the FOB value, the invoice reference, the port, and the IEC code.

The Python service connects to the finance mailbox via IMAP, runs every 30 minutes, and processes any new emails from ICEGATE or the freight forwarder that match a subject-line pattern. The core logic:

1. Fetch and parse — imaplib to connect, email stdlib to parse the MIME structure, then regex patterns trained on the ICEGATE email template to extract the SB number, date, FOB, and invoice reference. For PDFs attached by the freight forwarder, pdfplumber extracts the same fields from the shipping bill document layout.
2. Match to BC invoice — the extracted invoice reference (which may be a purchase order number, proforma number, or our own sales invoice number depending on the buyer) is matched against BC via the OData web service on the Sales Invoice page. The service queries /api/v2.0/companies(…)/salesInvoices?$filter=externalDocumentNo eq '…' and returns the BC document number.
3. Push the shipping bill fields — a PATCH request to the same OData endpoint writes the Shipping Bill No., Shipping Bill Date, Port Code, and sets Realisation Status to Open. Simultaneously, a POST to a custom BC API page creates the corresponding row in Export Compliance Entry.
4. Log and alert — unmatched emails (where the invoice reference doesn't resolve in BC) are written to an exceptions log and trigger a Slack message to the finance team for manual intervention. This keeps the automation honest — it doesn't silently swallow failures.

The service runs as a systemd unit on a small Linux VM. Total Python code: under 400 lines including the IMAP handling, the regex patterns, and the BC API calls. There is no third-party middleware, no iPaaS subscription, no additional licensing cost. The BC OData endpoint is already included in the Business Central subscription.

Compliance is a workflow, not a month-end scramble. If you're reconstructing the shipping bill register at the end of the month, you've already lost the visibility that makes the register useful.

The reconciliation view: open vs realised

With shipping bills landing in BC automatically, the reconciliation view becomes trivial to build. I created a BC report page (accessible from the Finance role centre) that queries Export Compliance Entry and surfaces:

• Open entries — shipping bills with Realisation Status = Open, sorted by Realisation Due Date ascending so the most urgent entries are always at the top.
• Ageing buckets — 0–90 days, 91–180 days, 181–270 days, and Over-270 (already past the window). The over-270 bucket is highlighted in red; anything in the 181–270 bucket triggers a system notification to the finance manager.
• Partial realisations — entries where the realised amount (from the matched Bank Ledger Entry) is less than the FOB value, showing the shortfall and whether a write-off application has been filed.
• eBRC status — a one-field indicator: Pending / Issued / number populated. When the AD bank confirms the eBRC number, the finance team updates the eBRC No. field, which sets Realisation Status = eBRC Issued and effectively closes the entry.

Matching the inward remittance to the shipping bill is done in BC's Bank Reconciliation workflow, extended with a custom "Apply to Export Entry" action. When the treasury team applies an inward remittance line to a sales invoice, the action also finds the corresponding Export Compliance Entry rows and updates the realised amount. If the remittance covers multiple invoices — common when a buyer consolidates payments — the action applies pro-rata across the matched entries and flags any resulting partial realisations automatically.

Handling the hard cases

The straightforward case — one shipment, one invoice, one remittance, one eBRC — is maybe 70% of the volume. The remaining 30% is where manual systems collapse.

Partial realisations: RBI permits write-offs of unrealised export proceeds up to prescribed limits (currently 10% of the invoice value for exports to non-ACU countries, with additional provisions for specific circumstances). When a partial realisation is confirmed as final — either because the buyer disputed quantity or because the write-off limit covers the shortfall — the Export Compliance Entry is updated with a Write-Off Amount and a Write-Off Reason field. The entry can then be marked Realised even though the bank receipt doesn't equal the FOB value, and the write-off is flagged in the exceptions report sent to the AD bank.

Multiple shipping bills per remittance: A buyer might consolidate three months of shipments into one wire transfer. The bank reconciliation action handles this by allowing one Bank Ledger Entry to be linked to multiple Export Compliance Entry rows, applying the remittance amount sequentially (oldest shipping bill first by default, configurable) until it's exhausted. Any residual goes to the most recent open entry as a partial realisation.

Currency rounding: FOB values are in the invoice currency (USD, EUR, GBP), but the realisation is in INR at the bank's buying rate on the date of credit. The Export Compliance Entry stores both the foreign-currency FOB amount and the INR equivalent at the rate BC already captured on the sales invoice. Rounding differences of a few rupees (which the bank and the RBI both accept) are absorbed into a Forex Rounding variance field rather than showing as a partial realisation. This matters because even a ₹2 shortfall would otherwise trigger an open status and an exception flag.

IDPMS (import side): The same data model and reconciliation logic apply in reverse. Every bill of entry is tracked in an Import Compliance Entry table, linked to the Posted Purchase Invoice and the outward remittance Bank Ledger Entry. The Python service watches for the ICEGATE bill-of-entry acknowledgement emails (or the bank's outward remittance confirmation) and pushes data into BC on the same pattern.

Build vs buy

There are bolt-on products that promise to solve the EDPMS/IDPMS reconciliation problem. Some of them are genuinely capable. The reasons I chose a thin Python-plus-BC integration instead:

• The data is already in BC. Every sales invoice, every bank ledger entry, every customer is already there. A bolt-on means extracting that data, reconciling it in a second system, and then either duplicating it back or running two sources of truth.
• The automation is narrow. The actual automation — parsing ICEGATE emails and writing three fields to a BC record — is maybe 200 lines of Python. It doesn't justify an annual SaaS subscription.
• Auditability. Every data change is a BC ledger entry with a timestamp, a user ID, and a document reference. The statutory auditors and the AD bank can pull a complete trail from a single system without needing access to a third-party platform.
• Maintenance. The ICEGATE email template changes rarely. When it does, updating a regex pattern in one Python file takes fifteen minutes.

The tradeoff is that someone on the team needs to understand how the BC API and the Python service interact. That's a real cost. For a company already running BC and with at least one technically comfortable person in finance or IT, it's comfortably worth it.

The principle

Export compliance fails when it's treated as a reporting task — something you reconstruct from shipping documents and bank statements at the end of the month. By that point the realisation clock has been running for weeks without anyone tracking it, the ageing buckets are already compressed, and the exception management is reactive rather than preventive.

The shift this automation enables is structural: every export transaction is born compliance-aware. The shipping bill arrives in the same system as the invoice, with its due date already set. The open entries are visible in real time. The AD bank exceptions report is a scheduled extract, not a manual compilation. The eBRC closure is one field update, not a multi-email chase.

Compliance should be designed into the transaction flow, not reconstructed afterward. If your ERP already holds the invoice and the remittance, the only missing piece is the shipping bill — and a 200-line Python script can close that gap.