Financial Compliance

VAT validation, multi-currency operations, and regulatory compliance.

---

VAT (Value Added Tax) Overview

VAT is a consumption tax applied by most countries. Every business handling B2B sales internationally must validate VAT IDs.

Key Points:

• VAT ID format varies by country (GB = 12 digits, DE = 11, FR = 13, etc.)
• EU businesses can verify VAT via VIES (VAT Information Exchange System)
• Invalid VAT = cannot invoice B2B customers legally
• Wrong VAT = tax compliance failure, penalties

---

VAT Validation Basics

Step 1: Syntax Check

-- Check if VAT ID format is valid for its country
SELECT
    vat_id,
    anofox_vat_is_valid(vat_id) as is_valid_format,
    anofox_vat_get_country(vat_id) as country
FROM vendors
LIMIT 10;

Output:

vat_id        | is_valid_format | country
DE123456789   | true            | DE
FR12345678    | true            | FR
INVALID       | false           | null
GB123456789   | true            | GB

Step 2: EU Verification (VIES Check)

For EU businesses, verify the VAT ID is actually registered:

SELECT
    vat_id,
    anofox_vat_verify_eu(vat_id) as is_eu_registered,
    anofox_vat_is_valid(vat_id) as syntax_valid
FROM vendors
WHERE anofox_vat_get_country(vat_id) IN ('DE', 'FR', 'IT', 'ES', 'GB', 'NL', 'BE', 'AT');

Output:

vat_id        | is_eu_registered | syntax_valid
DE123456789   | true             | true
FR99999999    | false            | true  -- Syntax valid but not registered
ES99999999    | false            | true

---

Complete VAT Validation Workflow

-- Create a comprehensive VAT validation table
CREATE TABLE vendors_vat_validated AS
SELECT
    vendor_id,
    vendor_name,
    vat_id,
    -- Step 1: Syntax validation
    anofox_vat_is_valid(vat_id) as syntax_valid,
    -- Step 2: Extract country
    anofox_vat_get_country(vat_id) as country,
    -- Step 3: EU verification (if applicable)
    CASE
        WHEN anofox_vat_get_country(vat_id) IN ('DE', 'FR', 'IT', 'ES', 'GB', 'NL', 'BE', 'AT', 'SE')
        THEN anofox_vat_verify_eu(vat_id)
        ELSE NULL  -- Not EU
    END as eu_verified,
    -- Step 4: Final status
    CASE
        WHEN NOT anofox_vat_is_valid(vat_id) THEN 'INVALID_SYNTAX'
        WHEN anofox_vat_get_country(vat_id) IN ('DE', 'FR', 'IT', 'ES', 'GB', 'NL', 'BE', 'AT', 'SE')
         AND NOT anofox_vat_verify_eu(vat_id) THEN 'EU_UNREGISTERED'
        WHEN anofox_vat_is_valid(vat_id) THEN 'VALID'
        ELSE 'UNKNOWN'
    END as validation_status
FROM vendors;

-- Approved vendors (can invoice)
SELECT * FROM vendors_vat_validated WHERE validation_status = 'VALID';

-- Flagged vendors (manual review needed)
SELECT * FROM vendors_vat_validated WHERE validation_status != 'VALID';

---

Multi-Currency Operations

Currency Validation

Ensure amounts are in correct currency and format:

SELECT
    transaction_id,
    amount,
    currency,
    anofox_currency_is_valid(currency) as currency_valid,
    CASE
        WHEN anofox_currency_is_valid(currency) THEN amount
        ELSE NULL
    END as safe_amount
FROM transactions;

Currency Conversion

Convert amounts between currencies for consolidated reporting:

SELECT
    transaction_id,
    amount,
    currency,
    anofox_currency_convert(amount, currency, 'EUR') as amount_eur,
    anofox_currency_convert(amount, currency, 'USD') as amount_usd
FROM transactions
WHERE anofox_currency_is_valid(currency) IS TRUE;

Multi-Currency Accounting

-- Consolidate revenue across currencies
CREATE TABLE revenue_consolidated AS
SELECT
    region,
    DATE_TRUNC('month', transaction_date) as month,
    SUM(anofox_currency_convert(amount, currency, 'EUR')) as revenue_eur,
    COUNT(*) as transaction_count
FROM transactions
WHERE anofox_currency_is_valid(currency)
  AND amount > 0
GROUP BY 1, 2
ORDER BY 2 DESC;

---

Invoice Compliance Checklist

Before sending an invoice, validate all required fields:

CREATE TABLE invoices_ready_to_send AS
SELECT
    invoice_id,
    customer_id,
    -- Customer VAT validation
    CASE WHEN anofox_vat_is_valid(customer_vat) THEN TRUE ELSE FALSE END as customer_vat_valid,
    -- Amount validation
    CASE WHEN anofox_money_is_positive(invoice_amount) THEN TRUE ELSE FALSE END as amount_valid,
    -- Currency validation
    CASE WHEN anofox_currency_is_valid(currency) THEN TRUE ELSE FALSE END as currency_valid,
    -- Customer contact validation
    CASE WHEN anofox_email_validate(customer_email, 'dns') THEN TRUE ELSE FALSE END as email_valid,
    -- Comprehensive check
    CASE
        WHEN anofox_vat_is_valid(customer_vat)
         AND anofox_money_is_positive(invoice_amount)
         AND anofox_currency_is_valid(currency)
         AND anofox_email_validate(customer_email, 'dns')
        THEN 'READY'
        ELSE 'BLOCKED'
    END as invoice_status
FROM invoices
WHERE invoice_status = 'READY';

---

Tax Reporting

Missing VAT IDs for EU Customers

-- Identify EU customers without VAT IDs (compliance issue)
SELECT
    customer_id,
    customer_name,
    country,
    COUNT(*) as invoice_count,
    SUM(invoice_amount) as total_amount
FROM invoices
WHERE country IN ('DE', 'FR', 'IT', 'ES', 'GB', 'NL', 'BE', 'AT')
  AND (vat_id IS NULL OR vat_id = '')
GROUP BY 1, 2, 3
HAVING COUNT(*) > 0
ORDER BY total_amount DESC;

Invalid VAT IDs Used

-- Track which invoices used invalid VAT IDs
CREATE TABLE audit_invalid_vat_invoices AS
SELECT
    invoice_id,
    customer_id,
    vat_id,
    anofox_vat_is_valid(vat_id) as vat_valid,
    invoice_amount,
    invoice_date
FROM invoices
WHERE NOT anofox_vat_is_valid(vat_id);

-- Monthly compliance report
SELECT
    DATE_TRUNC('month', invoice_date) as month,
    COUNT(*) as invalid_vat_count,
    SUM(invoice_amount) as amount_with_invalid_vat
FROM audit_invalid_vat_invoices
GROUP BY 1
ORDER BY 1 DESC;

---

Common International Scenarios

Scenario 1: UK VAT Post-Brexit

-- UK VAT (GB prefix) requires special handling post-Brexit
SELECT
    customer_id,
    vat_id,
    anofox_vat_get_country(vat_id) as country,
    CASE
        WHEN anofox_vat_get_country(vat_id) = 'GB'
        THEN 'BREXIT_SPECIAL_HANDLING'  -- Additional rules apply
        ELSE 'STANDARD_EU_VAT'
    END as vat_treatment
FROM customers;

Scenario 2: US Sales Tax (No VAT)

-- US uses sales tax, not VAT (different validation)
SELECT
    customer_id,
    customer_state,
    anofox_address_get_state(address) as state_from_address,
    CASE
        WHEN customer_state != anofox_address_get_state(address) THEN 'ADDRESS_MISMATCH'
        ELSE 'OK'
    END as address_validation
FROM customers
WHERE country = 'US';

Scenario 3: Canada GST/HST

-- Canada GST/HST (different by province)
SELECT
    customer_id,
    customer_province,
    tax_id,  -- Canadian Business Number
    CASE
        WHEN customer_province IN ('ON', 'NS', 'NB', 'NL', 'PE')
        THEN 'HST'  -- Harmonized Sales Tax
        ELSE 'GST'  -- Goods and Services Tax
    END as applicable_tax
FROM customers
WHERE country = 'CA';

---

Reporting & Compliance

Monthly Compliance Report

CREATE VIEW compliance_monthly_report AS
SELECT
    DATE_TRUNC('month', invoice_date) as period,
    COUNT(DISTINCT customer_id) as unique_customers,
    COUNT(*) as total_invoices,
    SUM(invoice_amount) as total_revenue,
    COUNT(*) FILTER (WHERE NOT anofox_vat_is_valid(vat_id)) as invalid_vat_invoices,
    COUNT(*) FILTER (WHERE vat_id IS NULL) as missing_vat_invoices,
    ROUND(100.0 * COUNT(*) FILTER (WHERE anofox_vat_is_valid(vat_id)) / COUNT(*), 1) as vat_valid_rate_pct
FROM invoices
GROUP BY 1
ORDER BY 1 DESC;

Audit Trail

-- Keep an audit trail of VAT validation for compliance
CREATE TABLE audit_vat_validation AS
SELECT
    audit_id,
    invoice_id,
    vat_id,
    anofox_vat_is_valid(vat_id) as was_valid,
    anofox_vat_get_country(vat_id) as country,
    anofox_vat_verify_eu(vat_id) as eu_registered,
    CURRENT_TIMESTAMP as validated_at
FROM invoices;

---

Best Practices

✅ Validate at source: Check VAT when customer is added, not at invoice time
✅ Progressive validation: Syntax first, then VIES (if slow), then manual review if needed
✅ Audit trail: Keep records of what was validated and when
✅ Periodic re-validation: VAT IDs can become inactive; re-check quarterly
✅ Geographic rules: Different countries have different tax rules (VAT, GST, Sales Tax, etc.)
✅ Separate by region: Keep EU, US, and other tax jurisdictions separate for reporting

---

Next Steps

• Validation Strategies — Understanding validation trade-offs
• Email Validation — Ensure customer contact info is valid
• Production Deployment — Integrate compliance checks into production pipelines