SQL Query Library

-- Input VAT Summary for Return Preparation
-- Use in: DB Query Reader node
-- Parameters: Replace :org_id and :period with flow variables

SELECT 
    zx.TAX_RATE_CODE,
    zx.TAX_STATUS_CODE,
    zx.SELF_ASSESSED_FLAG,
    ROUND(SUM(zx.TAXABLE_AMOUNT), 2) AS TOTAL_TAXABLE,
    ROUND(SUM(zx.TAX_AMOUNT), 2) AS TOTAL_VAT,
    COUNT(DISTINCT zx.INVOICE_ID) AS INVOICE_COUNT
FROM ZX_LINES zx
INNER JOIN AP_INVOICES_ALL ai 
    ON zx.INVOICE_ID = ai.INVOICE_ID
WHERE ai.ORG_ID = :org_id
    AND ai.INVOICE_DATE BETWEEN :period_start AND :period_end
    AND ai.CANCELLED_DATE IS NULL
    AND zx.TAX_REGIME_CODE = :tax_regime  -- e.g., 'NL-VAT'
GROUP BY 
    zx.TAX_RATE_CODE,
    zx.TAX_STATUS_CODE,
    zx.SELF_ASSESSED_FLAG
ORDER BY zx.TAX_RATE_CODE;

-- Output VAT from AR (Sales Invoices)
SELECT 
    ctl.TAX_RATE,
    CASE 
        WHEN ctl.TAX_RATE = 0 THEN 'ZERO_RATED'
        WHEN ctl.TAX_RATE IS NULL THEN 'EXEMPT'
        ELSE 'STANDARD'
    END AS VAT_CATEGORY,
    ROUND(SUM(ctl.REVENUE_AMOUNT), 2) AS TOTAL_NET_REVENUE,
    ROUND(SUM(ctl.REVENUE_AMOUNT * ctl.TAX_RATE / 100), 2) AS TOTAL_OUTPUT_VAT,
    COUNT(DISTINCT ct.CUSTOMER_TRX_ID) AS TRANSACTION_COUNT
FROM RA_CUSTOMER_TRX_ALL ct
INNER JOIN RA_CUSTOMER_TRX_LINES_ALL ctl 
    ON ct.CUSTOMER_TRX_ID = ctl.CUSTOMER_TRX_ID
WHERE ct.ORG_ID = :org_id
    AND ct.TRX_DATE BETWEEN :period_start AND :period_end
    AND ctl.LINE_TYPE = 'LINE'  -- Exclude tax lines
GROUP BY 
    ctl.TAX_RATE,
    CASE 
        WHEN ctl.TAX_RATE = 0 THEN 'ZERO_RATED'
        WHEN ctl.TAX_RATE IS NULL THEN 'EXEMPT'
        ELSE 'STANDARD'
    END
ORDER BY ctl.TAX_RATE;

-- Rate Mismatch Detection
-- Flags invoices where applied rate is unusual for the supplier country
SELECT 
    ai.INVOICE_NUM,
    ai.INVOICE_DATE,
    s.VENDOR_NAME,
    ss.COUNTRY AS SUPPLIER_COUNTRY,
    zx.TAX_RATE_CODE,
    zx.TAX_RATE AS APPLIED_RATE,
    zx.TAX_AMOUNT,
    zx.TAXABLE_AMOUNT,
    hou.NAME AS CANON_ENTITY,
    CASE 
        WHEN ss.COUNTRY = 'FR' AND zx.TAX_RATE NOT IN (0, 5.5, 10, 20) THEN 'SUSPECT'
        WHEN ss.COUNTRY = 'DE' AND zx.TAX_RATE NOT IN (0, 7, 19) THEN 'SUSPECT'
        WHEN ss.COUNTRY = 'NL' AND zx.TAX_RATE NOT IN (0, 9, 21) THEN 'SUSPECT'
        WHEN ss.COUNTRY = 'BE' AND zx.TAX_RATE NOT IN (0, 6, 12, 21) THEN 'SUSPECT'
        WHEN ss.COUNTRY = 'PL' AND zx.TAX_RATE NOT IN (0, 5, 8, 23) THEN 'SUSPECT'
        ELSE 'OK'
    END AS RATE_CHECK
FROM AP_INVOICES_ALL ai
INNER JOIN ZX_LINES zx ON ai.INVOICE_ID = zx.INVOICE_ID
INNER JOIN AP_SUPPLIERS s ON ai.VENDOR_ID = s.VENDOR_ID
INNER JOIN AP_SUPPLIER_SITES_ALL ss ON ai.VENDOR_SITE_ID = ss.VENDOR_SITE_ID
INNER JOIN HR_OPERATING_UNITS hou ON ai.ORG_ID = hou.ORGANIZATION_ID
WHERE ai.INVOICE_DATE BETWEEN :period_start AND :period_end
    AND ai.CANCELLED_DATE IS NULL
HAVING CASE 
    WHEN ss.COUNTRY = 'FR' AND zx.TAX_RATE NOT IN (0, 5.5, 10, 20) THEN 'SUSPECT'
    WHEN ss.COUNTRY = 'DE' AND zx.TAX_RATE NOT IN (0, 7, 19) THEN 'SUSPECT'
    WHEN ss.COUNTRY = 'NL' AND zx.TAX_RATE NOT IN (0, 9, 21) THEN 'SUSPECT'
    WHEN ss.COUNTRY = 'BE' AND zx.TAX_RATE NOT IN (0, 6, 12, 21) THEN 'SUSPECT'
    WHEN ss.COUNTRY = 'PL' AND zx.TAX_RATE NOT IN (0, 5, 8, 23) THEN 'SUSPECT'
    ELSE 'OK'
END = 'SUSPECT'
ORDER BY ai.INVOICE_DATE DESC;

-- Missing Reverse Charge Detection
-- Cross-border invoices that should have reverse charge but don't
SELECT 
    ai.INVOICE_NUM,
    ai.INVOICE_DATE,
    ai.INVOICE_AMOUNT,
    s.VENDOR_NAME,
    ss.COUNTRY AS SUPPLIER_COUNTRY,
    hou.NAME AS BUYER_ENTITY,
    'MISSING REVERSE CHARGE' AS ISSUE
FROM AP_INVOICES_ALL ai
INNER JOIN AP_SUPPLIERS s ON ai.VENDOR_ID = s.VENDOR_ID
INNER JOIN AP_SUPPLIER_SITES_ALL ss ON ai.VENDOR_SITE_ID = ss.VENDOR_SITE_ID
INNER JOIN HR_OPERATING_UNITS hou ON ai.ORG_ID = hou.ORGANIZATION_ID
WHERE ai.INVOICE_DATE BETWEEN :period_start AND :period_end
    AND ai.CANCELLED_DATE IS NULL
    AND ss.COUNTRY <> :buyer_country  -- Cross-border
    AND ss.COUNTRY IN ('AT','BE','BG','HR','CY','CZ','DK','EE','FI','FR','DE','GR','HU','IE','IT','LV','LT','LU','MT','NL','PL','PT','RO','SK','SI','ES','SE')  -- EU suppliers
    AND NOT EXISTS (
        SELECT 1 FROM ZX_LINES zx 
        WHERE zx.INVOICE_ID = ai.INVOICE_ID 
        AND zx.SELF_ASSESSED_FLAG = 'Y'
    )
ORDER BY ai.INVOICE_AMOUNT DESC;

-- Invoices Missing from E-Invoice Submission
SELECT 
    ai.INVOICE_ID,
    ai.INVOICE_NUM,
    ai.INVOICE_DATE,
    ai.INVOICE_AMOUNT,
    s.VENDOR_NAME,
    ss.COUNTRY AS SUPPLIER_COUNTRY,
    hou.NAME AS ENTITY,
    'NOT SUBMITTED' AS STATUS
FROM AP_INVOICES_ALL ai
INNER JOIN AP_SUPPLIERS s ON ai.VENDOR_ID = s.VENDOR_ID
INNER JOIN AP_SUPPLIER_SITES_ALL ss ON ai.VENDOR_SITE_ID = ss.VENDOR_SITE_ID
INNER JOIN HR_OPERATING_UNITS hou ON ai.ORG_ID = hou.ORGANIZATION_ID
INNER JOIN IDF_COUNTRY_CONFIG icc ON ss.COUNTRY = icc.COUNTRY_CODE
WHERE ai.INVOICE_DATE >= icc.EFFECTIVE_DATE  -- Only after mandate start
    AND ai.CANCELLED_DATE IS NULL
    AND NOT EXISTS (
        SELECT 1 FROM IDF_EINVOICE_LOG iel 
        WHERE iel.INVOICE_ID = ai.INVOICE_ID
    )
ORDER BY ai.INVOICE_DATE;

-- E-Invoice Error Frequency Analysis
SELECT 
    iel.COUNTRY_CODE,
    ierr.ERROR_CODE,
    ierr.ERROR_DESCRIPTION,
    COUNT(*) AS ERROR_COUNT,
    COUNT(DISTINCT iel.INVOICE_ID) AS AFFECTED_INVOICES,
    MIN(iel.SUBMISSION_DATE) AS FIRST_OCCURRENCE,
    MAX(iel.SUBMISSION_DATE) AS LAST_OCCURRENCE
FROM IDF_EINVOICE_LOG iel
INNER JOIN IDF_ERROR_LOG ierr ON iel.EINVOICE_ID = ierr.EINVOICE_ID
WHERE iel.STATUS = 'REJECTED'
    AND iel.SUBMISSION_DATE >= :analysis_start
GROUP BY 
    iel.COUNTRY_CODE,
    ierr.ERROR_CODE,
    ierr.ERROR_DESCRIPTION
ORDER BY ERROR_COUNT DESC;

-- Intercompany Transactions for CIT/TP
SELECT 
    gcc.SEGMENT1 AS ENTITY_CODE,
    gcc.SEGMENT4 AS IC_PARTNER_CODE,
    gcc.SEGMENT3 AS ACCOUNT_CODE,
    jh.PERIOD_NAME,
    ROUND(SUM(jl.ENTERED_DR), 2) AS TOTAL_DEBIT,
    ROUND(SUM(jl.ENTERED_CR), 2) AS TOTAL_CREDIT,
    ROUND(SUM(jl.ENTERED_DR) - SUM(jl.ENTERED_CR), 2) AS NET_AMOUNT,
    jh.CURRENCY_CODE,
    COUNT(*) AS LINE_COUNT
FROM GL_JE_LINES jl
INNER JOIN GL_JE_HEADERS jh ON jl.JE_HEADER_ID = jh.JE_HEADER_ID
INNER JOIN GL_CODE_COMBINATIONS gcc ON jl.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID
WHERE gcc.SEGMENT4 IS NOT NULL 
    AND gcc.SEGMENT4 <> '000'
    AND gcc.SEGMENT4 <> ''
    AND jh.STATUS = 'P'  -- Posted journals only
    AND jh.PERIOD_NAME LIKE :fiscal_year || '%'  -- e.g., '2026%'
GROUP BY 
    gcc.SEGMENT1, gcc.SEGMENT4, gcc.SEGMENT3, 
    jh.PERIOD_NAME, jh.CURRENCY_CODE
ORDER BY gcc.SEGMENT1, gcc.SEGMENT4, jh.PERIOD_NAME;

-- CIT-Relevant Account Balances
SELECT 
    gcc.SEGMENT1 AS ENTITY,
    gcc.SEGMENT3 AS ACCOUNT,
    CASE 
        WHEN gcc.SEGMENT3 BETWEEN '5000' AND '5099' THEN 'Tax Provisions'
        WHEN gcc.SEGMENT3 BETWEEN '6100' AND '6199' THEN 'R&D Expense'
        WHEN gcc.SEGMENT3 BETWEEN '4500' AND '4599' THEN 'Depreciation'
        WHEN gcc.SEGMENT3 BETWEEN '7000' AND '7099' THEN 'Intercompany Revenue'
        WHEN gcc.SEGMENT3 BETWEEN '8000' AND '8099' THEN 'Intercompany Cost'
        ELSE 'Other'
    END AS CIT_CATEGORY,
    gb.PERIOD_NAME,
    ROUND(gb.BEGIN_BALANCE_DR - gb.BEGIN_BALANCE_CR, 2) AS OPENING_BALANCE,
    ROUND(gb.PERIOD_NET_DR - gb.PERIOD_NET_CR, 2) AS PERIOD_MOVEMENT,
    ROUND((gb.BEGIN_BALANCE_DR + gb.PERIOD_NET_DR) - 
          (gb.BEGIN_BALANCE_CR + gb.PERIOD_NET_CR), 2) AS CLOSING_BALANCE
FROM GL_BALANCES gb
INNER JOIN GL_CODE_COMBINATIONS gcc ON gb.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID
WHERE gb.ACTUAL_FLAG = 'A'  -- Actuals only
    AND gb.PERIOD_NAME = :period
    AND gcc.SEGMENT3 IN (
        -- Tax provisions
        SELECT SEGMENT3 FROM GL_CODE_COMBINATIONS WHERE SEGMENT3 BETWEEN '5000' AND '5099'
        UNION
        -- R&D
        SELECT SEGMENT3 FROM GL_CODE_COMBINATIONS WHERE SEGMENT3 BETWEEN '6100' AND '6199'
        UNION
        -- Depreciation
        SELECT SEGMENT3 FROM GL_CODE_COMBINATIONS WHERE SEGMENT3 BETWEEN '4500' AND '4599'
    )
ORDER BY gcc.SEGMENT1, gcc.SEGMENT3;

-- Supplier VAT Data Quality
SELECT 
    s.VENDOR_ID,
    s.VENDOR_NAME,
    ss.VENDOR_SITE_ID,
    ss.COUNTRY,
    ss.VAT_REGISTRATION_NUM,
    CASE 
        WHEN ss.VAT_REGISTRATION_NUM IS NULL THEN 'MISSING'
        WHEN LEN(ss.VAT_REGISTRATION_NUM) < 8 THEN 'TOO_SHORT'
        WHEN ss.COUNTRY = 'NL' AND ss.VAT_REGISTRATION_NUM NOT LIKE 'NL%' THEN 'WRONG_PREFIX'
        WHEN ss.COUNTRY = 'DE' AND ss.VAT_REGISTRATION_NUM NOT LIKE 'DE%' THEN 'WRONG_PREFIX'
        WHEN ss.COUNTRY = 'FR' AND ss.VAT_REGISTRATION_NUM NOT LIKE 'FR%' THEN 'WRONG_PREFIX'
        WHEN ss.COUNTRY = 'BE' AND ss.VAT_REGISTRATION_NUM NOT LIKE 'BE%' THEN 'WRONG_PREFIX'
        ELSE 'OK'
    END AS QUALITY_CHECK,
    (SELECT COUNT(*) FROM AP_INVOICES_ALL ai 
     WHERE ai.VENDOR_SITE_ID = ss.VENDOR_SITE_ID 
     AND ai.INVOICE_DATE >= DATEADD(MONTH, -12, GETDATE())) AS INVOICES_LAST_12M
FROM AP_SUPPLIERS s
INNER JOIN AP_SUPPLIER_SITES_ALL ss ON s.VENDOR_ID = ss.VENDOR_ID
WHERE ss.COUNTRY IN ('AT','BE','BG','HR','CY','CZ','DK','EE','FI','FR','DE','GR','HU','IE','IT','LV','LT','LU','MT','NL','PL','PT','RO','SK','SI','ES','SE')
    AND s.ENABLED_FLAG = 'Y'
HAVING CASE 
    WHEN ss.VAT_REGISTRATION_NUM IS NULL THEN 'MISSING'
    WHEN LEN(ss.VAT_REGISTRATION_NUM) < 8 THEN 'TOO_SHORT'
    WHEN ss.COUNTRY = 'NL' AND ss.VAT_REGISTRATION_NUM NOT LIKE 'NL%' THEN 'WRONG_PREFIX'
    WHEN ss.COUNTRY = 'DE' AND ss.VAT_REGISTRATION_NUM NOT LIKE 'DE%' THEN 'WRONG_PREFIX'
    WHEN ss.COUNTRY = 'FR' AND ss.VAT_REGISTRATION_NUM NOT LIKE 'FR%' THEN 'WRONG_PREFIX'
    WHEN ss.COUNTRY = 'BE' AND ss.VAT_REGISTRATION_NUM NOT LIKE 'BE%' THEN 'WRONG_PREFIX'
    ELSE 'OK'
END <> 'OK'
ORDER BY INVOICES_LAST_12M DESC;