Automating marketplace order processing, fulfillment tracking, and financial reconciliation with Microsoft Navision.
The Challenge: Bringing Amazon Sales Into the ERP Workflow
As the client’s ecommerce operation expanded, Amazon Marketplace became an increasingly important sales channel. While the business already had an automated integration connecting its primary ecommerce storefront to Microsoft Navision ERP, Amazon orders presented an entirely different set of challenges.
Amazon Seller Central provided daily sales reporting, but there was no automated connection between marketplace transactions and the client’s internal accounting and fulfillment systems.
Initially, office staff handled the process manually. Each day, they would export sales reports from Seller Central and upload them through the existing order import system. While this was an improvement over manually entering individual orders, it still required staff intervention and introduced delays between the time an order was placed and when it became available for processing in Navision.
More importantly, the standard sales reports didn’t provide all the financial information needed to accurately account for each transaction.
Amazon charges sellers a variety of marketplace fees, including referral commissions and fulfillment charges. These costs can vary depending on the product, its category, dimensions, fulfillment method, and other factors. Simply importing the gross sales amount wasn’t enough to produce an accurate representation of the transaction within the client’s accounting system.
The result was a secondary manual process involving spreadsheets, fee lookups, and ongoing reconciliation work.
What initially appeared to be a relatively straightforward order import problem had become a more complicated integration challenge.
The Hidden Complexity: Amazon Marketplace Fees
The biggest friction point wasn’t actually retrieving orders. It was accounting for the financial details associated with each sale.
Unlike a traditional ecommerce storefront, where the merchant generally controls its payment processing and transaction costs, Amazon operates as a marketplace with its own fee structures.
Two particularly important expenses were:
- Referral Commissions: Amazon’s commission on each sale, generally calculated as a percentage of the transaction and varying by product category.
- Fulfillment Fees: Charges associated with Amazon’s Fulfillment by Amazon (FBA) program, including pick, pack, and shipping services.
These fees weren’t consistently available in the standard sales reports the client was exporting.
To compensate, staff maintained a separate cross-reference spreadsheet containing product information and the applicable Amazon fees. As Amazon adjusted its fulfillment and commission rates, those values had to be reviewed and manually updated to keep the imported sales records accurate.
This introduced another layer of ongoing maintenance and another opportunity for discrepancies.
The existing manual process was accomplishing its primary objective of getting orders into Navision, but it wasn’t providing a complete or particularly efficient financial picture of Amazon sales.
We needed a way to retrieve both the order information and the actual financial details directly from Amazon.
Extending the Existing Integration: A Custom Amazon Connector
Rather than introducing another standalone application or purchasing a third-party integration platform, we extended the custom middleware already developed for the client’s ecommerce-to-ERP integration.
This was an important architectural advantage. We had already established the underlying infrastructure for communicating with Navision, transforming incoming sales data, and creating ERP sales orders.
The next step was building a new connector capable of communicating directly with Amazon Seller Central.
Connecting to Amazon Seller Central
We utilized Amazon’s Selling Partner API (SP-API) to establish a direct connection with the client’s Seller Central account.
This provided a programmatic means of retrieving marketplace order information and financial transactions without requiring staff to export and upload daily sales reports.
The integration utilized two primary API services:
- Orders API: Retrieves marketplace order information, including order status, line items, quantities, and fulfillment details.
- Finances API: Retrieves the actual financial transactions associated with fulfilled orders, including marketplace commissions and fulfillment charges.
We developed a custom PHP client to handle API authentication, requests, pagination, and rate limiting. This allowed the middleware to communicate directly with Amazon without relying on an additional integration platform or third-party service.
Incoming Amazon data was then passed into the existing middleware environment for processing.
While the underlying ERP import functionality was already in place, Amazon’s data structures differed from those of the client’s primary ecommerce platform. We developed the necessary collection and transformation methods to translate Amazon order records into the internal format expected by the existing import engine.
This allowed us to reuse much of the original integration logic rather than developing an entirely separate order processing system.
A Two-Stage Order Processing Workflow
One of the more important architectural decisions involved recognizing that an Amazon order and an Amazon financial transaction don’t necessarily happen at the same time.
An order becomes available shortly after purchase, but the associated fulfillment charges and marketplace fees aren’t finalized until the order ships and Amazon processes the payment.
Simply retrieving a new order and immediately submitting it to Navision would leave the financial information incomplete.
We addressed this by separating initial order capture from final ERP processing.
Stage 1: Initial Order Capture
The middleware runs as a scheduled background process, periodically polling Amazon for new and updated orders.
When a new order is identified, its basic information is retrieved and recorded in a local transaction ledger. This includes the Amazon order identifier, creation date, product SKUs, quantities, and available pricing information.
At this point, the order is stored in a pending state. It has been captured by our system but isn’t yet eligible for import into Navision.
Stage 2: Financial Enrichment and Fulfillment Verification
During subsequent synchronization cycles, the middleware retrieves Amazon’s financial transactions and associates them with previously captured orders.
This is where the Finances API becomes particularly important. Once Amazon processes a shipment, the associated fulfillment and referral fees become available as financial transactions.
Amazon can initially post these transactions in a deferred state before subsequently releasing them. Our integration accounts for both transaction states and ensures that the same financial charges aren’t counted twice.
The middleware collects the applicable fees, associates them with the original order, and updates its internal records.
Only after all items have shipped and the associated financial information is available does the order become eligible for submission to Navision.
This approach ensures that the ERP receives a complete transaction rather than an incomplete order requiring additional reconciliation.
Accounting for Multiple Shipments
Another complication involved Amazon’s ability to fulfill a single customer order through multiple shipments.
Depending on inventory availability and warehouse locations, items within the same order could be dispatched separately. For example, an order containing three products might be fulfilled through two different Amazon warehouses, resulting in multiple shipments and separate fulfillment transactions.
From an accounting perspective, importing each shipment as an independent sales order would create problems. The client’s ERP needed to reflect the original customer transaction as a complete order, not several unrelated partial transactions.
We built additional fulfillment tracking into the middleware to account for this behavior.
Rather than submitting an order as soon as its first shipment was processed, the system continued monitoring its fulfillment status until all associated items had shipped.
The middleware tracks ordered and shipped quantities at the individual line-item level. An order with outstanding items remains in a pending state, allowing subsequent shipment information and fees to be incorporated into the original record.
Once the shipped quantities match the original ordered quantities and the associated financial information is available, the middleware marks the order as ready for ERP processing.
This ensures that Navision receives a consolidated order containing the complete set of line items, fulfillment information, and applicable marketplace fees.
Building a Reliable Integration Layer
One of the advantages of the original ecommerce integration was its modular design.
The middleware had already separated the responsibilities of retrieving sales data, transforming incoming records, and submitting completed orders to Navision.
Adding Amazon meant developing another collection of data retrieval and transformation methods while retaining the existing ERP communication layer.
We also introduced a local transaction ledger to manage the additional complexity associated with Amazon’s order and financial processing lifecycle.
Order Tracking and Transaction Management
Because Amazon orders can move through several stages before they’re ready for ERP processing, we needed a reliable way to maintain their state between synchronization cycles.
We implemented a lightweight SQLite database to track orders, individual line items, financial transactions, processing attempts, and eventual Navision order references.
This provided several important capabilities:
- Order state tracking: Each order maintains a processing status, allowing the middleware to distinguish between pending, imported, and problematic transactions.
- Shipment reconciliation: Individual shipment transactions are associated with their original orders and aggregated as additional fulfillment information becomes available.
- Financial tracking: Actual fulfillment and commission fees are recorded against the appropriate orders.
- Duplicate prevention: Successfully imported orders are recorded with their corresponding Navision order numbers, preventing subsequent synchronization cycles from creating duplicate transactions.
- Error management: Orders with unknown product SKUs, incomplete financial information, or ERP submission errors are identified and retained for investigation.
The ledger also provides a historical record of the integration’s activity, making it possible to trace an order from its initial appearance in Seller Central through its eventual creation in Navision.
Mapping Amazon Products to Navision
Another important component was reconciling Amazon’s product identifiers with the client’s existing ERP inventory.
Amazon identifies products using marketplace-specific identifiers and seller SKUs, while Navision relies on its own internal item numbers.
We maintained a product cross-reference file mapping Amazon seller SKUs to their corresponding Navision inventory items and pricing.
This allowed the integration to translate marketplace order line items into the correct ERP products without requiring changes to the client’s existing inventory structure.
Unknown SKUs were flagged for review rather than allowing an incomplete or incorrectly mapped order to proceed.
Submitting Completed Orders to Navision
Once an order passed fulfillment verification and financial reconciliation, the middleware handed it off to the existing Navision integration.
This was another benefit of the modular architecture. We didn’t need to recreate the ERP communication layer or develop a separate process for Amazon orders.
The integration used the established SOAP/XML web services connection to create the sales order in Navision, including the appropriate customer, location, payment terms, product line items, and marketplace fee adjustments.
The actual Amazon fulfillment and referral fees were represented as separate negative resource lines within the Navision order, allowing those costs to be reflected alongside the original sale.
Importantly, the middleware only submits a completed order once. Its local ledger maintains the resulting Navision order number and import status, providing an additional safeguard against duplicate transactions.
Results & Business Impact
The Amazon integration eliminated the daily manual export and upload process that had previously been required to get marketplace orders into Navision.
More importantly, it addressed the financial reconciliation problem that had made the original manual workflow particularly cumbersome.
The client no longer needed to maintain a separate spreadsheet of Amazon fulfillment and commission fees or continually research rate changes to keep imported sales records accurate.
Key improvements included:
- Automated Order Retrieval: Amazon sales are retrieved directly through the Selling Partner API rather than relying on manually exported reports.
- Reduced Administrative Work: Staff no longer need to perform daily report exports, uploads, and associated order processing.
- Improved Financial Accuracy: Actual marketplace fees are retrieved from Amazon’s financial transaction data rather than relying on manually maintained estimates.
- Complete Order Processing: Multi-shipment orders are consolidated before submission to Navision, ensuring that the ERP receives the complete transaction.
- Duplicate Prevention: Transaction tracking and import safeguards prevent orders from being submitted multiple times.
- Improved Operational Visibility: The local transaction ledger provides a record of processing activity, pending orders, and exceptions requiring attention.
- Extensible Architecture: Additional sales channels can be incorporated into the middleware without rebuilding the underlying ERP connection.
One Integration Platform, Multiple Sales Channels
This project demonstrates another important advantage of custom middleware: the ability to extend an existing solution as a business evolves.
The original ecommerce integration solved the problem of connecting a modern storefront to a legacy ERP system. When the client expanded its marketplace presence, we were able to extend that same architecture to accommodate Amazon’s unique requirements.
Rather than creating another isolated integration or introducing additional manual processes, we built on the infrastructure already serving the business.
The result was a more unified order processing environment that could accommodate sales from multiple channels while maintaining a consistent connection to the company’s core business systems.
For organizations selling across multiple marketplaces and ecommerce platforms, this kind of architecture provides a practical path toward consolidating order processing, improving financial visibility, and reducing the administrative overhead associated with managing disconnected sales channels.