Pan-EU FBA puts your stock in up to seven EU fulfilment centres and generates VAT events under two regimes at the same time: local VAT in every country where stock is held, and the Union One Stop Shop quarterly return for cross-border B2C sales. One Amazon dataset drives both returns. The Amazon VAT Transactions Report (AVTR) is the source-of-truth file for the workflow, and its ShipFromCountry, ShipToCountry, and B2BFlag columns are the three columns that decide where each row goes: a local VAT return, the OSS return, or a zero-rated intra-EU B2B supply on the dispatch country's local return.
Sitting beside the AVTR is the VAT Calculation Report (VCR), produced per marketplace and aggregated monthly. The VCR is the marketplace-level summary view each country's local return reconciles back to. AVTR for the granular reconstruction, VCR for the marketplace totals — that's the practical division of labour for any Pan-EU FBA workflow taking Amazon VAT invoices to Excel.
The complication on top of that is language. The Tax Document Library issues per-order PDF VAT invoices in the marketplace language. Amazon.de invoices are in German, Amazon.fr in French, Amazon.it in Italian, Amazon.es in Spanish, Amazon.nl (and the Dutch-language Amazon.com.be) in Dutch, Amazon.pl in Polish, and Amazon.se in Swedish, with Amazon.co.uk and Amazon.ie in English. Any consolidated workflow has to unify those localised labels — Umsatzsteuer, TVA, IVA, BTW, VAT, Moms — into one column set the spreadsheet can pivot.
Three Amazon-specific changes have already moved the 2026 extraction logic. On 1 January 2026, Pan-EU FBA eligibility began requiring VAT registrations in at least five EU countries (Germany, France, Poland, Italy, Spain). From 1 April 2026, the VCR for Polish sales added a KSeF Number column carrying the identifier returned by Poland's National e-Invoice System. From 1 June 2026, VCS calculates tax at the shipment level by applicable tax rate rather than per individual unit, which increases row counts and forces a join on OrderID plus ShipmentID rather than OrderID alone. Each one changes a specific step in the extraction pipeline, and the article walks the deltas in order.
What follows is the document-to-spreadsheet route end to end: the AVTR columns that matter, the three-bucket split rule as concrete logic, the multi-language field mapping, the 2026 timeline, three honest options for how to actually run the workflow, the consolidated schema and its two pivot views, currency handling, and the edge cases that fall outside the main path. The return arithmetic, the registration-strategy decisions, and the Pan-EU-versus-EFN exit call still need the operator's or their advisor's judgement — the workflow below takes you to the return-ready data, not past it.
Inside the AVTR — columns, transaction types, and where the VCR fits
The AVTR is per-shipment, per-VAT-event data covering every Amazon-fulfilled movement of stock and every revenue event tied to a shipment. The VCR is a per-marketplace monthly aggregate of VAT-relevant amounts. The first is the source of truth for reconstruction; the second is the marketplace-level summary view each country's local return reconciles back to. Pan-EU operators use both, for different reasons.
The AVTR columns that drive the workflow
The columns below are the working subset. The full AVTR carries more fields than this, but the consolidation workflow turns on these.
- TransactionType — what the row is. Sale, Refund, Return, Inbound_Movement, FC_Transfer, Liquidation.
- ShipFromCountry — the country of the fulfilment centre dispatching the shipment.
- ShipToCountry — the customer-delivery country.
- ShipFromVATNumber — the seller's VAT number in the ship-from country (which is typically a different registration per ship-from country).
- ShipToVATNumber — the buyer's VAT number, populated only for B2B transactions where the buyer supplied a valid number.
- B2BFlag — Y or N. Y where the buyer supplied a valid VAT number and the order is a business-to-business sale; N for B2C.
- NetAmount, VATRate, VATAmount, GrossAmount — the four amount columns. Net is pre-VAT, gross is inclusive.
- CurrencyCode — denomination of the row's amounts. Pan-EU FBA produces EUR, GBP, PLN, SEK, CZK, and DKK rows depending on the marketplace.
- MarketplaceID — the Amazon marketplace identifier (A1F83G8C2ARO7P for the UK, A1PA6795UKMFR9 for Germany, and so on).
- OrderID and ShipmentID — the two join keys. From 1 June 2026 you need both together, not just OrderID, because each shipment becomes its own row.
Transaction types and what they mean for VAT
Three transaction types produce VAT events the returns care about: Sale, Refund, and Return. Sales generate output VAT in the bucket the split rule assigns the row to. Refunds and returns offset against the original sale's bucket, matched by OrderID and ShipmentID — they should never be re-bucketed independently of the row they reverse.
Three transaction types are stock-reconciliation entries that carry no VAT but matter for completeness: Inbound_Movement (stock arriving at an FC), FC_Transfer (stock moving between FCs, typically across borders under Pan-EU FBA), and Liquidation (stock disposed of). They don't feed either return, but a sensible workflow keeps them in the extracted schema for stock-on-hand reconciliation against the VCR aggregates and for audit trail purposes.
What the VCR adds beside the AVTR
The VCR is produced per marketplace and aggregated monthly. It carries the marketplace-level totals each country's tax authority expects to see on the local return: output VAT collected for the month, taxable amount broken down by VAT rate, and the split between marketplace-facilitated sales (where Amazon is liable for collection under the relevant Member State's marketplace facilitator rules) and seller-of-record sales (where you are).
From 1 April 2026, the VCR for Poland adds a new column to support Polish e-invoicing: the KSeF Number field, which carries the invoice number returned by Poland's National e-Invoice System (KSeF) for successfully submitted electronic invoices. The Polish local VAT return references this identifier rather than the Amazon invoice number, so the consolidation pipeline has to carry it through every step of the workflow from April onwards.
So which report do you use for OSS — and which for local?
For the OSS quarterly return, the AVTR is the only document that exposes shipment-level cross-border movements at the row grain OSS reporting needs. The VCR aggregates by marketplace, not by ship-to country, so it can't drive the per-Member-State breakdown OSS requires.
For each country's local VAT return, the AVTR supplies the rows assigned to that country's Local bucket, plus any zero-rated intra-EU B2B rows reported on the dispatch country's return where applicable. Cross-border B2C rows are excluded from the local-return pivot and reported through OSS. The VCR supplies the marketplace-totals check — the per-marketplace monthly figures the local return reconciles back to. The two work as a pair: the AVTR carries the granular row data, the VCR carries the marketplace summary used to file each local return and to validate that nothing got dropped between source rows and aggregated totals.
The three-bucket split rule — helper-column logic from AVTR columns
Every revenue-generating AVTR row falls into one of three VAT-treatment buckets, and the bucket is determined entirely by three columns: ShipFromCountry, ShipToCountry, and B2BFlag. Build the helper column once and the spreadsheet does the rest.
The three rules
Local VAT bucket — ShipFromCountry = ShipToCountry AND B2BFlag = N. The row reports on the local VAT return of the country in question. German stock to a German B2C customer reports German output VAT on the German return; French stock to a French B2C customer reports French TVA on the French return; and so on for each Pan-EU country where you hold stock and have a local VAT registration.
OSS bucket — ShipFromCountry ≠ ShipToCountry (both EU) AND B2BFlag = N. The row reports on the Union One Stop Shop quarterly return filed in your Member State of Identification. This is the cross-border B2C path: German stock dispatched to a French B2C customer is a French intra-Community distance sale, reported via OSS at the French VAT rate, paid in EUR through the MSI.
Zero-rated intra-EU B2B supply bucket — ShipFromCountry ≠ ShipToCountry AND B2BFlag = Y with a valid buyer VAT number populated in ShipToVATNumber. The row reports on the local VAT return of the dispatch country as a zero-rated intra-Community supply, with the buyer's VAT number flowing through to the EC Sales List or its national equivalent — the Zusammenfassende Meldung in Germany, the Déclaration européenne de services and the DEB in France, the Elenchi Intrastat in Italy.
In a spreadsheet, the helper column should classify the row before any pivot table runs. Expressed as pseudocode, the logic is:
IF TransactionType IN (Inbound_Movement, FC_Transfer, Liquidation) THEN NoVAT
ELSE IF ShipFromCountry is outside the EU THEN IOSS or out-of-scope import treatment
ELSE IF ShipFromCountry = ShipToCountry AND B2BFlag = N THEN Local
ELSE IF ShipFromCountry is different from ShipToCountry AND B2BFlag = N THEN OSS
ELSE IF ShipFromCountry is different from ShipToCountry AND B2BFlag = Y AND BuyerVATNumber is valid THEN IntraEU-B2B-ZeroRated
ELSE Local
The final fallback matters. A row that looks like B2B but lacks a valid buyer VAT number should not be zero-rated simply because the buyer claimed to be a business.
Why OSS sits where it does in this rule
The OSS bucket exists because cross-border B2C inside the EU no longer goes to the destination country's local return for most sellers. The European Commission's One Stop Shop scheme overview is the primary regulatory reference: the EU One Stop Shop comprises three optional schemes — Union, non-Union, and Import — and from 1 July 2021 a single EUR 10,000 annual turnover threshold covers intra-Community distance sales of goods alongside cross-border B2C services, above which the place of supply moves to the customer's Member State. Pan-EU FBA sellers cross that threshold on day one; cross-border B2C rows go to OSS, not to one local return per destination country. The local return of the dispatch country still gets the local-VAT-bucket rows and the zero-rated intra-Community-supply rows — never the OSS rows, which sit on the OSS return only.
Edge cases the rule alone doesn't cover
The three-bucket logic handles most of the AVTR, but four cases need their own treatment.
Refunds and returns offset against the bucket of the original Sale row. Match the Refund or Return row to its parent Sale on OrderID and ShipmentID, then apply the same bucket. A refund of a German-to-French B2C sale is an OSS-bucket negative; a refund of a German-to-German B2C sale is a local-VAT-bucket negative. Never re-bucket a refund based on its own columns — partial refunds and shipping refunds carry inherited treatment, not their own.
Inter-FC stock movements with TransactionType Inbound_Movement or FC_Transfer carry no VAT and don't feed either return, even when they cross borders. Keep them in the extracted schema for stock-on-hand reconciliation and audit, but exclude them from both pivot views by filtering on VATTreatmentBucket not equal to NoVAT.
Import OSS (IOSS) is a separate scheme covering low-value imports (≤€150) from non-EU dispatch points into the EU. IOSS rows sit outside the three-bucket Union OSS split entirely. If you are also dispatching from a non-EU warehouse — for example, FBA Export from a UK fulfilment centre to EU consumers post-Brexit — those rows feed IOSS, not Union OSS, and have to be separated upstream of the three-bucket logic.
B2B supplies with no valid VAT number — B2BFlag = Y but ShipToVATNumber missing, malformed, or unverified — cannot be zero-rated. They fall back to local VAT treatment in the ship-from country. A German FC dispatching to a self-declared business buyer who didn't supply a verified USt-IdNr is a domestic German B2C-equivalent sale for VAT purposes, German VAT charged, German return.
The zero-rated intra-EU B2B bucket also interacts with each dispatch country's domestic e-invoicing regime — sellers with German fulfilment-centre stock dispatching to other-EU B2B buyers should read up on Germany's B2B e-invoicing mandate covering XRechnung and ZUGFeRD for the broader compliance picture beyond the Amazon flow.
Unifying multi-language Tax Document Library field mapping
The AVTR is mostly language-neutral — country codes, numeric amounts, ISO date stamps. The Tax Document Library is the opposite. Amazon issues per-order PDF VAT invoices in the marketplace language, and every Pan-EU operator with stock in four-to-seven countries is reading the same invoice facts under different labels every day. Amazon.co.uk and Amazon.ie issue English-language invoices; Amazon.de in German; Amazon.fr in French; Amazon.it in Italian; Amazon.es in Spanish; Amazon.nl and the Dutch-language Amazon.com.be in Dutch; Amazon.pl in Polish; Amazon.se in Swedish.
The consolidation step has one job: map every per-language source label to the unified schema column. The table below shows the mapping for the seven fields that drive the workflow — invoice identifier, date, net, VAT rate, VAT amount, gross, and the seller's VAT registration number.
| Locale | Invoice Number | Invoice Date | Net | VAT Rate | VAT Amount | Gross | VAT Number |
|---|---|---|---|---|---|---|---|
| EN (co.uk, ie) | Invoice Number | Issue Date | Net amount | VAT Rate | VAT Amount | Total incl. VAT | VAT Number |
| DE (.de) | Rechnungsnummer | Rechnungsdatum | Nettobetrag | USt-Satz | USt-Betrag | Brutto | USt-IdNr |
| FR (.fr) | Numéro de facture | Date de facture | HT | Taux de TVA | Montant TVA | TTC | Numéro de TVA intracommunautaire |
| IT (.it) | Numero fattura | Data fattura | Imponibile | Aliquota IVA | Importo IVA | Totale | Partita IVA |
| ES (.es) | Número de factura | Fecha de factura | Base imponible | Tipo de IVA | Importe del IVA | Total | NIF / CIF |
| NL (.nl, .com.be) | Factuurnummer | Factuurdatum | Netto | BTW-tarief | BTW-bedrag | Totaal incl. BTW | BTW-nummer |
| PL (.pl) | Numer faktury | Data wystawienia | Netto | Stawka VAT | Kwota VAT | Razem brutto | NIP (+ KSeF ID from 1 April 2026) |
| SE (.se) | Fakturanummer | Fakturadatum | Netto | Momssats | Momsbelopp | Totalt inkl. moms | Momsregistreringsnummer |
A few practical points the table alone doesn't deliver.
Invoice numbering schemes differ. Italian fatture from Amazon.it carry sequential numbers issued per VAT-registered selling entity rather than per marketplace, so the same Amazon order can produce a Numero fattura that doesn't look like a typical Amazon-format identifier. Polish faktury from 1 April 2026 will carry the KSeF identifier as a separate field — your schema needs a dedicated KSeFID column, not a single Invoice Number column that overloads the source.
Date formats vary by locale. DD/MM/YYYY across most of continental Europe (Germany, France, Italy, Spain, Netherlands, Poland), DD-MM-YYYY in some Dutch invoices, YYYY-MM-DD in Swedish fakturor. The consolidation step should normalise to one ISO format (YYYY-MM-DD) in the output schema regardless of source, both so the per-country pivots sort correctly and so the audit-trail dates match across the seven marketplaces.
Decimal separators flip too. German, French, Italian, Spanish, Dutch, Polish, and Swedish invoices use the comma as decimal separator (1.234,56) and the full stop as thousands separator; English UK and Irish invoices use the full stop as decimal. Pivot tables and downstream accounting imports want native numeric values — the extraction step has to parse both conventions and emit one numeric type.
Hand-coding seven label sets is feasible for a single marketplace operator but brittle at scale. The labels also drift: the Polish PDF before April 2026 has one shape, after April it has the KSeF ID; German PDFs vary between Brutto and Gesamtbetrag inkl. USt. depending on the issuing entity (Amazon EU S.à r.l. on some marketplaces, the local Amazon entity on others); Spanish invoices alternate between NIF and CIF for the buyer identifier based on whether the buyer is an individual or a company.
A brief but important sidebar on B2B variants. Italian fatture destined for Italian B2B buyers carry the codice destinatario field for routing through the Sistema di Interscambio (SDI). German Rechnungen dispatched from a German FC to other-EU B2B buyers omit the VAT line and carry the buyer's USt-IdNr instead, marked as a zero-rated intra-Community supply. The multi-language mapping has to accommodate both the B2C variant (with VAT shown) and the B2B variant (with VAT zero-rated and a buyer VAT number captured) per locale.
The 2026 regulatory pipeline — three changes that move the extraction logic
Three Amazon-specific changes landed within a five-month window in early-to-mid 2026, and each one forced a concrete update to the extraction pipeline. The table below names the date, the change, and the specific delta the workflow has to absorb.
| Date | Change | Extraction-logic delta |
|---|---|---|
| 1 January 2026 | Pan-EU FBA programme eligibility now requires active VAT registrations in at least five EU countries: Germany, France, Poland, Italy, Spain. Sellers below that threshold are migrated to EFN (European Fulfilment Network — single-country stock, cross-border shipping at higher per-unit fulfilment cost). | Sellers exiting Pan-EU see the AVTR collapse to a single ShipFromCountry value: all rows are domestic to one country or cross-border to another. The three-bucket split simplifies to Local plus OSS; the local-VAT pivot needs only one country's data; the intra-EU B2B bucket all but disappears for sellers without an EU-VAT-registered presence in multiple countries. Sellers staying in must verify that five live registrations are reflected correctly in SellerVATNumberUsed for each row's ship-from country, with no missing entries that would cause a row to fall back to the wrong treatment. |
| 1 April 2026 | A new column is added to the VAT Calculation Report to support Polish e-invoicing compliance requirements. The new KSeF Number field will display the invoice number provided by Poland's National e-Invoice System (KSeF) for successfully submitted electronic invoices. | The consolidated schema gains a KSeFID column, populated only for Polish-marketplace rows from April onwards. The Polish local VAT return references the KSeF identifier rather than the Amazon invoice number. Any reconciliation against the KSeF system requires the new column to be carried through every downstream step — pivot view, return draft, archival record. The mechanics of how KSeF assigns identifiers and what the FA(3) XML payload contains is its own substantial topic; Poland's KSeF e-invoicing system and the FA(3) XML schema covers it in detail for sellers who need to integrate beyond simply capturing the column. |
| 1 June 2026 | VCS will calculate tax at the shipment level by applicable tax rate instead of by individual unit. | Partial shipments, multi-rate orders, and refunds now produce more rows in the VCR and AVTR — each shipment generates separate VAT lines per applicable tax rate rather than collapsing into a single per-order calculation. Reconciliation logic that joined on OrderID alone needs to join on OrderID plus ShipmentID; refund-matching that assumed one Sale row per OrderID needs to match Refund rows against the specific ShipmentID. Row counts increase materially for sellers with frequent partial shipments — for high-velocity Pan-EU accounts, a doubling of monthly AVTR rows is realistic — and the per-month VCR aggregate moves accordingly. |
OSS quarterly cadence, for context
OSS quarterly returns are due 30 April 2026, 31 July 2026, 31 October 2026, and 31 January 2027 — each filing covering the prior calendar quarter, filed in your Member State of Identification, denominated in EUR. The April KSeF column and the June shipment-level VCS change both land before the 31 July 2026 filing, so the Q2 2026 OSS return is the first one practitioners file under the new shape. The Q1 2026 return filed on 30 April catches only the January five-country change; everything else is downstream of that.
What the next sections already assume
The consolidated schema in the next section already includes the KSeFID column, the OrderID-plus-ShipmentID join key, and the increased row-count assumption. The three extraction routes section accounts for the row-count uplift in the manual-versus-batch trade-off. You do not need to re-architect anything specifically for these 2026 changes — they're already baked into the workflow that follows. What you do need to know is that they arrive in your pipeline by mid-2026, and that the Q2 2026 OSS filing is the first one where all three deltas are live simultaneously.
Three routes to the consolidated spreadsheet — services firm, manual, batch tool
The split rule, the multi-language mapping, and the 2026 deltas are the same whoever does the work. What differs is who runs the extraction step and how much of it sits under your own control. There are three routes practitioners actually choose between, driven by document volume, the number of marketplaces you sell on, and whether you want the document-to-spreadsheet step transparent enough to audit.
Route 1: VAT-services firm
Avalara, SimplyVAT, Hellotax, amavat, Staxxer, Vatglobal, and a handful of others run Pan-EU FBA compliance as a productised service. You hand over Amazon Seller Central credentials, the firm pulls the AVTR and the per-marketplace VCRs into their own platform, runs their own extraction and reconciliation, and returns draft local returns plus the OSS quarterly for you to sign off. Fees are typically £400 to £3,000 per month, scaling with country count and transaction volume.
The fit is sellers who have decided compliance is not what they want to spend operator hours on. The services work, the deliverables are filed returns rather than spreadsheets, and the pricing is roughly predictable. The honest qualification: it is still worth understanding what the service is extracting on your behalf. If a return is queried by a national tax authority three years later, you will want to know which AVTR rows fed which bucket and how the FX rate was applied — that knowledge is the audit trail behind the deliverable, not a substitute for the deliverable.
Route 2: Manual AVTR-plus-pivot
Download the AVTR monthly from Seller Central. Download the per-marketplace VCR exports monthly. Download Tax Document Library PDFs as needed for audit and retention. Build pivot tables in Excel or Google Sheets that apply the three-bucket split rule, group the local-VAT rows by ship-from country, and group the OSS rows by ship-to country. File the returns yourself or hand the spreadsheets to your accountant.
This route fits small Pan-EU sellers with two or three marketplaces beyond their home country, low PDF volume, and an operator who is comfortable in Excel. The honest pain points are real and they scale fast. The AVTR alone does not carry the PDF document identifiers in a retrievable form — six-year retention under HMRC and ten-year retention in some EU member states still requires the PDFs archived separately and indexed against the AVTR rows by OrderID. Multi-language PDF interpretation by hand stops scaling past a few hundred orders per month, and even at that volume the labels drift enough (the Brutto / Gesamtbetrag inkl. USt. variation in German invoices is the common case) to produce small mistakes that compound across a quarter. The 1 June 2026 shipment-level VCS change roughly doubles the row count for sellers with frequent partial shipments — a manual pivot workflow that was tractable in early 2026 will not necessarily stay tractable through Q3.
Route 3: Batch extraction tool
A folder of multi-language Tax Document Library PDFs plus the per-marketplace AVTR plus the per-marketplace VCR exports are processed together into one consolidated spreadsheet, joined on OrderID plus ShipmentID, classified by the three-bucket rule, with the KSeF ID column carried through for Polish rows from April 2026 onwards. The output is the schema specified in the next section, ready to feed the two pivot views — per-country local returns and the quarterly OSS return.
This is the natural product fit for AI-powered Amazon VAT invoice extraction when the document volume and the language coverage are what makes the manual route unsustainable. The extraction engine handles invoices to spreadsheets as a single job: upload the AVTR, the per-marketplace VCRs, and the Tax Document Library PDF batch into one extraction task; prompt the AI with the consolidation goal — "I need this for our Pan-EU FBA Q2 OSS return — produce one row per AVTR row joined to its PDF invoice and VCR aggregate, classify each row as Local / OSS / IntraEU-B2B-ZeroRated / IOSS / NoVAT, normalise dates to YYYY-MM-DD, and capture the KSeF Number for Polish rows" — and download the structured Excel or CSV. Single batches handle up to 6,000 files and individual PDFs up to 5,000 pages; larger quarterly archives can be split into repeat batches using the same saved prompt. The natural-language prompt is also what makes the workflow replicable across clients for the multi-client Pan-EU FBA bookkeeper — save the prompt once, apply it to each client's quarterly batch with no template configuration.
The fit is high-volume Pan-EU operators and ecommerce bookkeepers carrying multiple Pan-EU FBA clients. Scope honestly: the tool handles the document-to-spreadsheet step — the extraction and the unified schema. The VAT-return arithmetic, the registration-strategy questions (do you add a sixth country, do you drop to four and exit Pan-EU), and the choice of OSS Member State of Identification still need your call or your tax advisor's. The route gives you the data; what you do with it stays where it belongs.
A Pan-EU FBA operator or bookkeeper running this workflow is also typically reconciling adjacent Amazon flows — Amazon Sponsored Ads invoice reconciliation across marketplaces is the most common neighbour for any seller running paid placements on the same accounts.
The consolidated schema, the two pivots, and currency handling
One consolidated spreadsheet can drive both the local VAT returns and the OSS quarterly, but only if the row grain and the column set are right. For Pan-EU FBA, the row grain should be the shipment-level VAT event: one row per AVTR event, joined to the matching Tax Document Library PDF and reconciled back to the relevant marketplace VCR.
The working schema should carry four groups of fields.
| Group | Columns | What they do |
|---|---|---|
| Identity | OrderID, ShipmentID, MarketplaceID, MarketplaceCountry, AmazonVATInvoiceNumber, AmazonDocumentPDFFilename, KSeFID | Tie the row back to the Amazon order, shipment, marketplace, source PDF, and, for Polish rows from 1 April 2026, the KSeF identifier. |
| Movement and parties | ShipmentDate, ShipFromCountry, ShipToCountry, B2BFlag, BuyerVATNumber, SellerVATNumberUsed | Drive the local-versus-OSS split and prove which seller VAT registration was used for the dispatch country. |
| Amounts | NetAmount, VATRate, VATAmount, GrossAmount, CurrencyCode, FXRateToReportingCurrency, NetAmountReportingCurrency, VATAmountReportingCurrency, GrossAmountReportingCurrency | Carry both the source-currency amounts and the converted reporting-currency amounts needed for returns and reconciliation. |
| Classification | TransactionType, VATTreatmentBucket | Separate Sale, Refund, Return, Inbound_Movement, FC_Transfer, and Liquidation rows, then classify each row as Local, OSS, IntraEU-B2B-ZeroRated, IOSS, or NoVAT. |
Two pivot views then fall out of the same source table.
Per-country local-return pivot: filter VATTreatmentBucket to Local. Group by ShipFromCountry, which is the country of the return, then aggregate NetAmount, VATAmount, and GrossAmount by VATRate and TransactionType. That produces the return dataset for Germany, France, Italy, Spain, the Netherlands, Poland, Sweden, or whichever Pan-EU countries hold stock for that seller. The totals should reconcile back to that country's VCR for the month, allowing for timing differences and any Amazon marketplace-facilitated rows that are not seller-of-record output tax.
OSS quarterly pivot: filter VATTreatmentBucket to OSS. Group by ShipToCountry, because OSS reports the consumer's Member State, not the dispatch country. Aggregate NetAmount and VATAmount by VATRate, using the reporting-currency amount columns so the return is denominated in EUR. This is the spreadsheet view behind an OSS return for an Amazon seller: destination country, taxable amount, VAT rate, VAT due, with refunds netted against the same destination-country bucket as the original sale.
Currency handling is where otherwise sound spreadsheets start to drift. Pan-EU FBA produces rows in EUR, GBP, PLN, SEK, CZK, and DKK. Local VAT returns are filed in the local currency of the return country; the OSS return is filed in EUR. The schema needs an explicit FXRateToReportingCurrency column, and the workflow notes need to state the rate source used for each return.
There are three common sources. The ECB reference rate for the date of supply is the standard reference point for OSS and is the cleanest choice for the quarterly OSS pivot. The Amazon marketplace conversion rate carried in the AVTR is convenient for reconciliation to Amazon reports, but it is not always the rate a tax authority expects. The seller's bank settlement rate is auditable for cash reconciliation, but it usually answers a different question from the VAT return. Pick one source per return, document it, and apply it consistently across the quarter. Switching rate sources mid-quarter is how small rounding differences become a reconciliation problem.
With the schema fixed, the quarterly cycle becomes repetitive rather than inventive: pull the AVTR for the quarter, pull the per-marketplace VCRs, pull the Tax Document Library PDFs, run the consolidation, apply the FX rates, refresh the local-return and OSS pivots, then hand the outputs to the local tax preparers and the person filing OSS.
Edge cases, non-VCS marketplaces, and the Pan-EU-versus-EFN decision
The main workflow assumes Amazon's VAT Calculation Service is producing the VCR for the relevant marketplaces. VCS is available in the UK, Germany, France, Italy, Spain, the Netherlands, Belgium, and Ireland; Poland and Sweden were added by 2024; the Czech Republic has been announced as a phased rollout. Where VCS is not available, or where a seller has stock in a market that is not yet fully covered, the invoice workflow changes shape.
For non-VCS-supported stock locations, Amazon does not generate the same complete seller VAT invoice flow. Sellers may need to upload manual invoices themselves to satisfy Amazon Business buyer invoice requests, particularly on B2B orders. The extraction direction reverses: the consolidated schema becomes source data for generating seller-uploaded invoices rather than only the destination for parsing Amazon-issued PDFs. Operators with non-VCS stock usually maintain a parallel manual-invoice workflow keyed off the same AVTR rows, with OrderID, ShipmentID, buyer VAT number, seller VAT number, VAT rate, and dispatch country carried through from the consolidated table.
That edge case sits beside the bigger 2026 decision: stay in Pan-EU FBA with at least five local VAT registrations, or move to EFN. The 1 January 2026 five-country minimum pushed many sellers into a margin calculation they may have avoided when four countries were enough. Five local registrations cost roughly £400 to £600 per year each in compliance fees, before advisor time for each return cycle. EFN raises fulfilment cost per unit because stock ships cross-border from one home-country fulfilment centre, but it reduces the VAT footprint to one local registration plus the OSS quarterly for cross-border B2C.
The extraction difference is stark. Under Pan-EU FBA, the spreadsheet has multiple ShipFromCountry values, multiple SellerVATNumberUsed values, local-return pivots by country, OSS rows by destination country, and intra-EU B2B zero-rated rows by dispatch country. Under EFN, the AVTR collapses to one ShipFromCountry; local VAT is domestic to that country; cross-border B2C goes to OSS; the multi-country local-return layer disappears. Sellers with thin margins, low velocity in some Pan-EU countries, or high advisor cost are running that calculation now. Sellers with strong velocity in Germany, France, Italy, Spain, and Poland usually have a different answer. The right call depends on the operator's margin model and country-by-country sales mix, not a generic compliance article.
Hybrid operations add one more layer. A seller may keep Amazon Pan-EU FBA for fast-moving SKUs, use EFN for slower lines, and hold stock with third-party logistics providers in countries where Amazon coverage is not economical. Those teams often need consolidating multi-3PL fulfilment invoices across Pan-EU stock locations alongside the Amazon AVTR workflow, because the VAT return does not care whether the fulfilment invoice came from Amazon, a 3PL, or a mixed stock arrangement.
Extract invoice data to Excel with natural language prompts
Upload your invoices, describe what you need in plain language, and download clean, structured spreadsheets. No templates, no complex configuration.
Related Articles
Explore adjacent guides and reference articles on this topic.
Prepare the Irish Intrastat RPF CSV from Invoices
Build the Irish Intrastat working paper from invoices: fields lifted from invoices, enriched from product and shipment data, ready for RPF CSV upload.
Cyprus VAT Return From Supplier Invoices & Credit Notes
Prepare a Cyprus VAT return from supplier invoices and credit notes: purchase-register fields, 1-11B box mapping, and credit-note workflow before TFA filing.
Prepare the Irish VAT3 Return from Supplier Invoices
Prepare Ireland's bi-monthly VAT3 from supplier invoices: rate-split workbook for T2, reverse-charge E2/ES2/PA1/RCT cases, Sage codes, and the RTD payoff.