What "correctly coded" means in an order to invoice integration

In an order to invoice integration, the value of the transaction is usually the easiest part to get right. The storefront or marketplace has already calculated a total, the customer has already paid it, and any discrepancy between the order total and the invoice total will be spotted within days by whoever reconciles the bank. What takes months to surface, and far longer to correct, is an invoice that carries the right number into the wrong place in the ledger. That is what finance means when they say a feed is wrong.

Correct coding is the act of resolving each order into a set of ledger coordinates. Every posted line needs all of them, and each one has to be derived from something observable in the source order rather than assumed.

A useful test is reproducibility. Given the same order payload and the same mapping tables, the integration should produce byte-identical postings today, next quarter and after a re-run following an outage. If the outcome depends on which operator pressed which button, on the time of day the job ran, or on a default that fires when a lookup misses, the coding is not correct. It is merely plausible, which is a different thing and much harder to audit.

  • Customer or debtor account — Which sales ledger account the invoice sits against, including the distinction between a named trade account and a pooled retail or marketplace control account.
  • Nominal or general ledger account — Which revenue, shipping, discount or adjustment account each line credits, and which control account carries the debit.
  • Tax code — Not a percentage. The code that determines how the line appears on the VAT return and in any EC or postponed accounting reporting.
  • Analysis dimensions — Cost centre and department in Sage 200, tracking categories in Xero, subsidiary, department, class and location in NetSuite. Whatever your management reporting slices by.
  • Date and period — The document date that decides which accounting period, and therefore which VAT return and which month-end, the invoice belongs to.

The derivation chain: from order attributes to ledger coordinates

Each coordinate should be produced by a written precedence rule, expressed as an ordered list of lookups with an explicit behaviour when every lookup misses. A typical revenue nominal derivation for a Shopify store posting into Sage 200 might read: match on SKU in the mapping table; failing that, match on the product type or a dedicated metafield; failing that, match on the collection; failing that, quarantine the order. Notice that the final step is not "use the default sales nominal". A default at the end of a chain is a mechanism for hiding mapping gaps in the accounts, and it will hide them successfully for about two quarters.

Product lines are the part everyone remembers. The lines that cause trouble are the others. Shipping revenue usually belongs on its own nominal and frequently carries a different tax treatment from the goods it accompanies. Order-level discounts have to be allocated across lines or posted to a discounts-allowed nominal, and the choice affects gross margin reporting by product group. Gift wrap, surcharges, handling fees, insurance, rounding differences and loyalty redemptions each need their own coded destination. A mapping document that covers SKUs and stops there is half a specification.

Dimensions are where the derivation chain most often diverges from the product hierarchy. Sales channel, warehouse of despatch and destination region are common analysis axes, and none of them are properties of the SKU. An Amazon Seller order and a direct web order for the same item may share a revenue nominal but must carry different channel dimensions, otherwise channel profitability reporting becomes a manual spreadsheet exercise and the integration has solved nothing. Establish during discovery which dimensions are mandatory in the target system, because a posting that omits a mandatory dimension will be rejected at the API boundary rather than silently mis-filed, and that failure needs handling.

Finally, agree which timestamp becomes the document date. Order placement, payment capture, despatch and settlement can fall in different months, and for marketplace trade they frequently do. The rule should be stated once, applied everywhere, and checked against the target system's period locks. If Sage 200 or NetSuite has closed the period by the time a delayed order arrives, the integration needs a defined behaviour: post to the current open period with a reference to the original date, or hold for finance to decide. Choosing at runtime is not a design.

Tax codes are chosen, not calculated

The single most common coding defect is treating the tax amount on the order as sufficient information. A storefront gives you a computed tax total and often a rate. A ledger needs a code, and the code encodes intent: standard rated UK supply, zero rated export, reverse charge, outside the scope, marketplace deemed supply where the platform has accounted for the VAT. Several of those produce a zero tax amount on the order. If the integration derives the code from the amount, all of them collapse into one code, and the VAT return boxes will be wrong even though every invoice total is right.

The correct inputs are the ones that determine the treatment: the ship-to country, the seller's registration in that country, whether the customer supplied a validated VAT number, the goods or services classification of the line, and, for marketplaces, whether the platform is the deemed supplier for that transaction. That set of inputs resolves to a tax code through a decision table. Write the table out, have it confirmed by whoever signs the VAT return, and store it as a versioned artefact rather than as conditional logic buried in transformation code.

Then add a consistency check. Once the integration has chosen a code, it knows the rate that code implies in Xero or Sage 200. Compare that to the rate implied by the order's own tax lines. If a line arrives with twenty per cent tax and the derivation chain has selected a zero rated export code, something upstream is misconfigured and the invoice should not post. This check costs very little and catches storefront tax configuration errors, incorrectly flagged customer accounts and changes to marketplace behaviour long before they accumulate into a disclosure.

Where the mapping lives, and who owns it

Mapping tables have three plausible homes and the choice has operational consequences. Holding codes in the source platform, for instance as Shopify metafields on each product, keeps the data next to the person creating the product but scatters accounting configuration across a catalogue that merchandising controls. Holding it in the accounting system, on the item or product group record in NetSuite or Sage 200, keeps finance in control but requires every new SKU to be created twice. Holding it in the integration layer centralises it and makes it easy to version, at the cost of a small amount of tooling so that non-developers can maintain it.

There is no universally right answer, but there is a wrong one, which is holding the mapping in more than one place without a stated precedence. We have seen integrations where a metafield overrode a product group mapping on some code paths and not others, and the resulting inconsistency took longer to diagnose than the original build took to write. Decide, document it, and make the integration log which rule fired for each posted line so that the derivation can be replayed from the audit record.

Ownership matters as much as location. New products, new channels, new territories and new promotional mechanics all arrive without warning from the commercial side of the business. Someone in finance needs to own the mapping table, and the process for adding a SKU has to include the coding decision before the item goes on sale. Where that process does not exist, the integration will do the only responsible thing available to it, which is to stop, and the stoppage will be blamed on the integration rather than on the gap in the process.

Proving the coding: acceptance testing an order to invoice integration

Coding correctness is provable, and it should be proved in writing before go-live rather than discovered at the first month-end. The method we use is a golden sample. Take a set of real historical orders, ideally drawn from a full trading month so that seasonal and promotional patterns are represented. For each one, finance states the expected posting: debtor account, nominal per line, tax code per line, dimensions, document date. That expectation is agreed and frozen. The integration then processes the same orders into a sandbox company and the output is compared line by line.

Sample selection is where this exercise succeeds or fails. Ten happy-path domestic orders prove very little. Build the sample as a coverage matrix across the dimensions that actually drive the derivation chain: destination country, customer type, product group, fulfilment channel, payment method, discount mechanism and refund status. Twenty orders chosen to cover the matrix are worth two hundred chosen at random. Include the awkward ones deliberately: a part-refunded order, a mixed basket of standard and zero rated goods, an order shipped from a second warehouse, a marketplace order where the platform accounted for the tax.

Reconcile in the aggregate as well as the detail. Total the sample by nominal account and by tax code and compare against what the current manual process produced for the same orders. If the integration and the incumbent spreadsheet disagree, one of them is wrong, and establishing which before go-live is considerably cheaper than establishing it afterwards. On more than one occasion the exercise has shown the manual process to be the source of a long-standing misclassification, which is an uncomfortable but useful finding.

Keep the sample as a regression pack. Any change to the mapping tables, any upgrade to the target system, any new sales channel should be validated by re-running it. The pack, the expected results and the comparison tooling form part of the documentation handed over at completion, so the client can run it without us.

What to do when a code cannot be derived

Every derivation chain will eventually miss. A SKU is created outside the normal process, a marketplace introduces a new fee type, a customer ships to a country nobody anticipated. The design question is not how to prevent the miss, it is what the integration does in the five minutes afterwards. Guessing is the worst option, because a guess that posts successfully generates no signal and no urgency. The postings look complete, the reports look plausible, and the error is found in an audit.

The pattern that holds up is quarantine. The order is held, unposted, in an exception queue with the reason recorded in plain language: "no nominal mapping for SKU ABC-123". A named person receives an alert, corrects the mapping table, and releases the order. Because the posting is idempotent and keyed on the source order reference, releasing it produces exactly one invoice regardless of how many times release is attempted. The queue should be visible to finance, not only to IT, and it should have an agreed response time, because held orders are unbilled revenue.

A suspense nominal has a narrow legitimate use, which is to absorb small, bounded, well-understood residuals such as rounding differences, where the alternative is holding an entire order over a penny. It should never be the fallback for an unmapped product or an undetermined tax treatment. If the suspense account balance grows month on month, it has stopped being a control and become a hiding place.

Correct coding, then, is not a feature of the integration so much as a shared agreement between finance and the build, written down before any code exists. That agreement is exactly what the discovery and data mapping stage is for: the decision tables, the exception behaviours, the ownership of the mapping. It is why we fix scope in writing afterwards rather than before, and why the mapping document is handed over alongside the code. The integration will run for years. The people who maintain it need to know why every line went where it did.

Key points

  • Correct coding means every posted line resolves deterministically to a customer account, nominal, tax code, analysis dimensions and accounting period, with no silent defaults at the end of the derivation chain.
  • Tax codes must be chosen from the facts of the supply rather than inferred from the tax amount, and the chosen code's implied rate should be cross-checked against the order before posting.
  • Prove the coding before go-live with a coverage-based golden sample signed off by finance, keep it as a regression pack, and quarantine anything the mapping cannot resolve rather than guessing.