e-conomic Customers

• Who are the customers, and when was each last invoiced?
• Which customers have an open balance?

The view is named after the company, such as "Acme ApS Customers", 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 Customers view for Acme ApS.

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

• customer_number: e-conomic customer number.
• name: Customer name.
• email: Email.
• website: Website.
• address: Street address.
• zip: Postal code.
• city: City.
• country: Country.
• currency: The customer's default currency.
• customer_group_number: e-conomic customer group number.
• payment_terms_number: e-conomic payment terms number.
• vat_zone_number: e-conomic VAT zone number (1 is domestic in a Danish agreement).
• vat_number: VAT number.
• corporate_id: Company registration number (CVR in Denmark).
• balance: Open balance as e-conomic reports it.
• credit_limit: Credit limit.
• lifetime_invoice_count: Number of booked invoices to the customer.
• last_invoice_date: Date of the latest booked invoice.
• lifetime_gross_by_currency: Gross invoiced per invoice currency, as a JSON object of currency code to amount.
• connection_label: The e-conomic agreement the row comes from.
• data_as_of: When this row was last synced.

The company's customers from e-conomic, one row per customer and agreement (connection_label).

balance is the customer's open balance as e-conomic reports it. Lifetime activity counts booked invoices only: lifetime_invoice_count, last_invoice_date, and lifetime_gross_by_currency, a JSON object of gross invoiced per invoice currency (for example {"DKK": 125000.00, "EUR": 4000.00}); amounts in different currencies are never added together.

Invoice-level detail and open amounts are in the Invoices view.

__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 inv AS (
  SELECT
    COALESCE(i."_gatlas_connection_label", '') AS label_key,
    (NULLIF(i."customer"::text, '')::jsonb ->> 'customerNumber')::bigint AS customer_number,
    COALESCE(i."currency", 'unknown') AS currency,
    COUNT(*) AS invoice_count,
    SUM(i."grossAmount") AS gross,
    MAX(i."date"::date) AS last_invoice_date
  FROM __SCHEMA__.economic_invoices_booked i
  GROUP BY 1, 2, 3
),
per_customer AS (
  SELECT
    v.label_key,
    v.customer_number,
    SUM(v.invoice_count)::bigint AS invoice_count,
    MAX(v.last_invoice_date) AS last_invoice_date,
    jsonb_object_agg(v.currency, ROUND(v.gross::numeric, 2)) AS gross_by_currency
  FROM inv v
  WHERE v.customer_number IS NOT NULL
  GROUP BY 1, 2
)
SELECT
  c."customerNumber" AS customer_number,
  c."name" AS name,
  c."email" AS email,
  c."website" AS website,
  c."address" AS address,
  c."zip" AS zip,
  c."city" AS city,
  c."country" AS country,
  c."currency" AS currency,
  (NULLIF(c."customerGroup"::text, '')::jsonb ->> 'customerGroupNumber')::bigint AS customer_group_number,
  (NULLIF(c."paymentTerms"::text, '')::jsonb ->> 'paymentTermsNumber')::bigint AS payment_terms_number,
  (NULLIF(c."vatZone"::text, '')::jsonb ->> 'vatZoneNumber')::bigint AS vat_zone_number,
  c."vatNumber" AS vat_number,
  c."corporateIdentificationNumber" AS corporate_id,
  c."balance" AS balance,
  c."creditLimit" AS credit_limit,
  COALESCE(pc.invoice_count, 0) AS lifetime_invoice_count,
  pc.last_invoice_date,
  COALESCE(pc.gross_by_currency, '{}'::jsonb) AS lifetime_gross_by_currency,
  c."_gatlas_connection_label" AS connection_label,
  c."_gatlas_synced_at" AS data_as_of
FROM __SCHEMA__.economic_customers c
LEFT JOIN per_customer pc
  ON pc.label_key = COALESCE(c."_gatlas_connection_label", '')
 AND pc.customer_number = c."customerNumber"
ORDER BY c."customerNumber"