e-conomic VAT by Code

• How much sales and input VAT was posted each month?
• Which VAT codes carry reverse charge?

The view is named after the company, such as "Acme ApS VAT by Code", and reads the company's e-conomic data.

Gatlas adds it for a company when the company connects e-conomic, and it fills in after the first sync. For a company that connected earlier, ask an AI assistant on a Read/write MCP connection:

Add the e-conomic VAT by Code view for Acme ApS.

The assistant creates it with the template id economic/vat-by-code, after checking that the company's data has every column the template reads.

• connection_label: The e-conomic agreement the row comes from.
• month_start: First day of the month.
• month_end: Last day of the month.
• vat_code: e-conomic VAT code.
• vat_name: Name of the VAT code.
• vat_type: VAT type name, e.g. sales or purchase VAT.
• rate_percentage: The code's VAT rate in percent.
• reverse_charge: True for reverse charge codes.
• vat_account_number: Account the code posts its VAT to.
• contra_vat_account_number: Account the offsetting VAT posts to on a reverse charge code.
• entry_count: Posted entries carrying the code in the month.
• base_amount: Base-currency amount posted on non-VAT accounts with this code: the VAT base. Positive is debit.
• vat_amount: Base-currency amount posted on the code's VAT account. Positive is debit.
• contra_vat_amount: Base-currency amount posted on the code's contra VAT account (reverse charge).
• data_as_of: Most recent sync of the entries summed.

The company's VAT per e-conomic VAT code and month, from POSTED entries only (drafts are in the Ledger view). All amounts in base currency, positive is debit.

In e-conomic a posted line with a VAT code books its base amount on the line's account and the VAT on the code's VAT account (vat_account_number); both rows carry the code. base_amount sums the former, vat_amount the latter. For reverse charge codes the offsetting VAT sits on contra_vat_account_number (contra_vat_amount).

Sales VAT is credit (negative); input VAT is debit (positive). rate_percentage is the code's rate; vat_amount / base_amount should be close to it.

A code with no match in the VAT accounts table has null rate and type, and its VAT, if any, is counted in base_amount.

__SCHEMA__ stands for the company's schema. To adapt the view, copy the SQL, put the schema in its place and change what your company needs.

WITH coded AS (
  SELECT
    e."_gatlas_connection_label" AS connection_label,
    date_trunc('month', e."date"::date)::date AS month_start,
    btrim(e."vatCode") AS vat_code,
    e."accountNumber" AS account_number,
    e."amountInBaseCurrency" AS amount_base,
    e."_gatlas_synced_at" AS synced_at
  FROM __SCHEMA__.economic_accounting_year_entries e
  WHERE NULLIF(btrim(e."vatCode"), '') IS NOT NULL
)
SELECT
  c.connection_label,
  c.month_start,
  (c.month_start + INTERVAL '1 month - 1 day')::date AS month_end,
  c.vat_code,
  MAX(va."name") AS vat_name,
  MAX(va."vatTypeName") AS vat_type,
  MAX(va."ratePercentage") AS rate_percentage,
  COALESCE(BOOL_OR(va."reverseCharge"), false) AS reverse_charge,
  MAX(va."accountNumber") AS vat_account_number,
  MAX(va."contraAccountNumber") AS contra_vat_account_number,
  COUNT(*) AS entry_count,
  ROUND(SUM(c.amount_base) FILTER (
    WHERE c.account_number IS DISTINCT FROM va."accountNumber"
      AND c.account_number IS DISTINCT FROM va."contraAccountNumber")::numeric, 2) AS base_amount,
  ROUND(COALESCE(SUM(c.amount_base) FILTER (WHERE c.account_number = va."accountNumber"), 0)::numeric, 2) AS vat_amount,
  ROUND(COALESCE(SUM(c.amount_base) FILTER (WHERE c.account_number = va."contraAccountNumber"), 0)::numeric, 2) AS contra_vat_amount,
  MAX(c.synced_at) AS data_as_of
FROM coded c
LEFT JOIN __SCHEMA__.economic_vat_accounts va
  ON btrim(va."vatCode") = c.vat_code
 AND COALESCE(va."_gatlas_connection_label", '') = COALESCE(c.connection_label, '')
GROUP BY c.connection_label, c.month_start, c.vat_code
ORDER BY 1, 2, 4