SQL Query Library

-- Input VAT Summary for Return Preparation
-- Use in: DB Query Reader node
-- Parameters: set :org_id, :period_start, :period_end, :tax_regime as flow variables

SELECT
    zx.TAX_RATE_CODE,
    zx.TAX_STATUS_CODE,
    zx.SELF_ASSESSED_FLAG,
    ROUND(SUM(zx.TAXABLE_AMT), 2)          AS TOTAL_TAXABLE,
    ROUND(SUM(zx.TAX_AMT), 2)              AS TOTAL_VAT,
    COUNT(DISTINCT ai.INVOICE_ID)           AS INVOICE_COUNT
FROM ZX_LINES zx
INNER JOIN AP_INVOICES_ALL ai
    ON  zx.TRX_ID        = ai.INVOICE_ID   -- ZX_LINES links via TRX_ID, not INVOICE_ID
    AND zx.APPLICATION_ID = 200            -- 200 = AP; 222 = AR
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' (underscore, not hyphen)
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) — ZX_LINES is the tax SSOT
-- Use in: DB Query Reader node

SELECT
    zx.TAX_RATE_CODE,
    zx.TAX_STATUS_CODE,
    CASE
        WHEN zx.TAX_STATUS_CODE IN ('ZERO RATED','ZERO_RATED') THEN 'ZERO_RATED'
        WHEN zx.TAX_STATUS_CODE IN ('EXEMPT','EXEMPTED')       THEN 'EXEMPT'
        WHEN zx.SELF_ASSESSED_FLAG = 'Y'                       THEN 'REVERSE_CHARGE'
        ELSE 'STANDARD'
    END                                        AS VAT_CATEGORY,
    ROUND(SUM(zx.TAXABLE_AMT), 2)             AS TOTAL_NET_REVENUE,
    ROUND(SUM(zx.TAX_AMT), 2)                 AS TOTAL_OUTPUT_VAT,
    COUNT(DISTINCT ct.CUSTOMER_TRX_ID)         AS TRANSACTION_COUNT
FROM RA_CUSTOMER_TRX_ALL ct
INNER JOIN ZX_LINES zx
    ON  zx.TRX_ID        = ct.CUSTOMER_TRX_ID -- ZX_LINES links via TRX_ID
    AND zx.APPLICATION_ID = 222               -- 222 = AR
WHERE ct.ORG_ID          = :org_id
    AND ct.TRX_DATE BETWEEN :period_start AND :period_end
    AND zx.TAX_REGIME_CODE = :tax_regime
GROUP BY
    zx.TAX_RATE_CODE,
    zx.TAX_STATUS_CODE,
    CASE
        WHEN zx.TAX_STATUS_CODE IN ('ZERO RATED','ZERO_RATED') THEN 'ZERO_RATED'
        WHEN zx.TAX_STATUS_CODE IN ('EXEMPT','EXEMPTED')       THEN 'EXEMPT'
        WHEN zx.SELF_ASSESSED_FLAG = 'Y'                       THEN 'REVERSE_CHARGE'
        ELSE 'STANDARD'
    END
ORDER BY zx.TAX_RATE_CODE;

-- Rate Mismatch Detection (Oracle SQL)
-- Flags AP invoices where the applied VAT rate is not a known valid rate
-- for that supplier's country. Filter logic lives in WHERE, not HAVING.

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_AMT                 AS TAX_AMOUNT,
    zx.TAXABLE_AMT             AS TAXABLE_AMOUNT,
    hou.NAME                   AS CANON_ENTITY,
    'SUSPECT'                  AS RATE_CHECK
FROM AP_INVOICES_ALL ai
INNER JOIN ZX_LINES zx
    ON  zx.TRX_ID        = ai.INVOICE_ID
    AND zx.APPLICATION_ID = 200
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 = 'FR' AND zx.TAX_RATE NOT IN (0, 5.5, 10, 20))
     OR (ss.COUNTRY = 'DE' AND zx.TAX_RATE NOT IN (0, 7, 19))
     OR (ss.COUNTRY = 'NL' AND zx.TAX_RATE NOT IN (0, 9, 21))
     OR (ss.COUNTRY = 'BE' AND zx.TAX_RATE NOT IN (0, 6, 12, 21))
     OR (ss.COUNTRY = 'PL' AND zx.TAX_RATE NOT IN (0, 5, 8, 23))
    )
ORDER BY ai.INVOICE_DATE DESC;

-- Missing Reverse Charge Detection (Oracle SQL)
-- Finds EU-supplier AP invoices with no self-assessed ZX_LINES row.
-- Cross-border B2B services from EU suppliers typically require reverse charge.

SELECT
    ai.INVOICE_ID,
    ai.INVOICE_NUM,
    ai.INVOICE_DATE,
    ai.INVOICE_AMOUNT,
    ai.INVOICE_CURRENCY_CODE,
    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  -- only cross-border
    AND ss.COUNTRY IN (               -- EU-27 supplier countries
        '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 NOT EXISTS (
        SELECT 1
        FROM ZX_LINES zx
        WHERE zx.TRX_ID        = ai.INVOICE_ID  -- correct join column
          AND zx.APPLICATION_ID = 200            -- AP events only
          AND zx.SELF_ASSESSED_FLAG = 'Y'
    )
ORDER BY ai.INVOICE_AMOUNT DESC;

-- E-Invoice Submission Gap Analysis (Oracle SQL)
-- Finds AP invoices in mandate-active countries with no IDF_EINVOICE_LOG record.

SELECT
    ai.INVOICE_ID,
    ai.INVOICE_NUM,
    ai.INVOICE_DATE,
    ai.INVOICE_AMOUNT,
    ai.INVOICE_CURRENCY_CODE,
    s.VENDOR_NAME,
    ss.COUNTRY                 AS SUPPLIER_COUNTRY,
    hou.NAME                   AS ENTITY,
    icc.MANDATE_STATUS,
    icc.GO_LIVE_DATE,
    'NOT SUBMITTED'            AS IDF_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
    AND icc.MANDATE_STATUS = 'MANDATORY'
WHERE ai.INVOICE_DATE   >= icc.GO_LIVE_DATE   -- GO_LIVE_DATE, not EFFECTIVE_DATE
    AND ai.CANCELLED_DATE IS NULL
    AND NOT EXISTS (
        SELECT 1
        FROM IDF_EINVOICE_LOG iel
        WHERE iel.SOURCE_TRX_ID  = ai.INVOICE_ID  -- SOURCE_TRX_ID, not INVOICE_ID
          AND iel.SOURCE_SYSTEM   = 'AP'           -- AP vs AR distinction
    )
ORDER BY ai.INVOICE_DATE;

-- E-Invoice Rejection Error Pattern Analysis (Oracle SQL)
-- Ranks error codes by frequency to prioritise data quality fixes.

SELECT
    iel.COUNTRY_CODE,
    ierr.ERROR_CODE,
    ierr.ERROR_DETAIL,                              -- was ERROR_DESCRIPTION (does not exist)
    ierr.FIELD_NAME,
    COUNT(*)                                        AS ERROR_COUNT,
    COUNT(DISTINCT iel.SOURCE_TRX_ID)               AS AFFECTED_INVOICES,
    ROUND(COUNT(*) * 100.0 /
          SUM(COUNT(*)) OVER (PARTITION BY iel.COUNTRY_CODE), 1)
                                                    AS PCT_OF_COUNTRY_ERRORS,
    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.EINVOICE_STATUS  = 'REJECTED'             -- EINVOICE_STATUS, not STATUS
    AND iel.SUBMISSION_DATE >= :analysis_start
GROUP BY
    iel.COUNTRY_CODE,
    ierr.ERROR_CODE,
    ierr.ERROR_DETAIL,
    ierr.FIELD_NAME
ORDER BY iel.COUNTRY_CODE, ERROR_COUNT DESC;

-- Intercompany Transaction Summary (Oracle SQL)
-- Extracts GL movements where SEGMENT4 (intercompany partner) is populated.
-- Adjust segment positions to match your specific Chart of Accounts.

SELECT
    gcc.SEGMENT1               AS ENTITY_CODE,
    gcc.SEGMENT4               AS IC_PARTNER_CODE,
    gcc.SEGMENT3               AS ACCOUNT_CODE,
    jh.PERIOD_NAME,
    jh.CURRENCY_CODE,
    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,
    ROUND(SUM(jl.ACCOUNTED_DR) - SUM(jl.ACCOUNTED_CR), 2) AS NET_FUNCTIONAL,
    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 jh.STATUS       = 'P'                           -- posted journals only
    AND gcc.SEGMENT4  IS NOT NULL                     -- IC partner present
    AND gcc.SEGMENT4  <> '000'                        -- exclude clearing/dummy
    AND SUBSTR(jh.PERIOD_NAME, -2) IN                 -- fiscal year filter
        ('01','02','03','04','05','06',
         '07','08','09','10','11','12')
    AND jh.PERIOD_NAME LIKE :fiscal_year || '%'       -- e.g. flow var '2026'
GROUP BY
    gcc.SEGMENT1, gcc.SEGMENT4, gcc.SEGMENT3,
    jh.PERIOD_NAME, jh.CURRENCY_CODE
HAVING SUM(jl.ENTERED_DR) - SUM(jl.ENTERED_CR) <> 0  -- suppress zero-balance rows
ORDER BY gcc.SEGMENT1, gcc.SEGMENT4, jh.PERIOD_NAME;

-- CIT-Relevant Account Balances (Oracle SQL)
-- Adjust BETWEEN ranges to match your Chart of Accounts segment values.

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 Tax-Sensitive'
    END                                                         AS CIT_CATEGORY,
    gb.PERIOD_NAME,
    gb.CURRENCY_CODE,
    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 (not budget/encumbrance)
    AND gb.CURRENCY_CODE = 'STAT' -- remove this line to include all currencies
    AND gb.PERIOD_NAME   = :period -- e.g. 'JUN-26'
    AND (
           gcc.SEGMENT3 BETWEEN '5000' AND '5099'  -- tax provisions
        OR gcc.SEGMENT3 BETWEEN '6100' AND '6199'  -- R&D
        OR gcc.SEGMENT3 BETWEEN '4500' AND '4599'  -- depreciation
        OR gcc.SEGMENT3 BETWEEN '7000' AND '7099'  -- IC revenue
        OR gcc.SEGMENT3 BETWEEN '8000' AND '8099'  -- IC cost
    )
ORDER BY gcc.SEGMENT1, gcc.SEGMENT3;

-- Supplier VAT Master Data Quality Check (Oracle SQL)
-- Finds active EU suppliers with missing or malformed VAT numbers.
-- Ranked by invoice volume so the AP team tackles the most impactful records first.

SELECT
    dq.VENDOR_ID,
    dq.VENDOR_NAME,
    dq.VENDOR_SITE_ID,
    dq.COUNTRY,
    dq.VAT_REGISTRATION_NUM,
    dq.QUALITY_CHECK,
    dq.INVOICES_LAST_12M
FROM (
    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 LENGTH(ss.VAT_REGISTRATION_NUM) < 8         THEN 'TOO_SHORT'  -- LENGTH not LEN
            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   >= ADD_MONTHS(SYSDATE, -12)  -- Oracle: not DATEADD/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'
) dq
WHERE dq.QUALITY_CHECK <> 'OK'   -- filter on computed column via outer query
ORDER BY dq.INVOICES_LAST_12M DESC;

-- ZX_LINES vs GL VAT Reconciliation (Oracle SQL)
-- Compares tax SSOT (ZX_LINES) against subledger postings (XLA_AE_LINES)
-- for the same period and entity.

WITH zx_summary AS (
    SELECT
        zx.INTERNAL_ORGANIZATION_ID    AS ORG_ID,
        zx.TAX_DETERMINE_DATE,
        TRUNC(zx.TAX_DETERMINE_DATE, 'MM') AS PERIOD_MONTH,
        zx.APPLICATION_ID,
        ROUND(SUM(zx.TAX_AMT), 2)     AS ZX_TAX_TOTAL
    FROM ZX_LINES zx
    WHERE zx.INTERNAL_ORGANIZATION_ID = :org_id
      AND zx.TAX_DETERMINE_DATE BETWEEN :period_start AND :period_end
    GROUP BY
        zx.INTERNAL_ORGANIZATION_ID,
        zx.TAX_DETERMINE_DATE,
        TRUNC(zx.TAX_DETERMINE_DATE, 'MM'),
        zx.APPLICATION_ID
),
gl_summary AS (
    SELECT
        xh.LEDGER_ID,
        TRUNC(xh.ACCOUNTING_DATE, 'MM')  AS PERIOD_MONTH,
        ROUND(SUM(xl.ACCOUNTED_DR) - SUM(xl.ACCOUNTED_CR), 2) AS GL_TAX_NET
    FROM XLA_AE_LINES xl
    INNER JOIN XLA_AE_HEADERS xh
        ON  xl.AE_HEADER_ID = xh.AE_HEADER_ID
    WHERE xl.ACCOUNTING_CLASS_CODE = 'TAX'
      AND xh.ACCOUNTING_DATE BETWEEN :period_start AND :period_end
    GROUP BY
        xh.LEDGER_ID,
        TRUNC(xh.ACCOUNTING_DATE, 'MM')
)
SELECT
    zx.PERIOD_MONTH,
    zx.APPLICATION_ID,
    zx.ZX_TAX_TOTAL,
    gl.GL_TAX_NET,
    ROUND(zx.ZX_TAX_TOTAL - gl.GL_TAX_NET, 2)  AS VARIANCE,
    CASE
        WHEN ABS(zx.ZX_TAX_TOTAL - gl.GL_TAX_NET) < 1  THEN 'RECONCILED'
        WHEN ABS(zx.ZX_TAX_TOTAL - gl.GL_TAX_NET) < 100 THEN 'MINOR VARIANCE'
        ELSE 'INVESTIGATE'
    END                                          AS RECON_STATUS
FROM zx_summary zx
LEFT JOIN gl_summary gl
    ON  TRUNC(zx.TAX_DETERMINE_DATE, 'MM') = gl.PERIOD_MONTH
ORDER BY zx.PERIOD_MONTH;

-- Open AP Invoice Holds by Type and Age (Oracle SQL)
-- Shows holds that have not been released (RELEASE_LOOKUP_CODE IS NULL).

SELECT
    h.HOLD_LOOKUP_CODE                          AS HOLD_TYPE,
    hou.NAME                                    AS ENTITY,
    COUNT(DISTINCT h.INVOICE_ID)                AS HELD_INVOICES,
    ROUND(SUM(ai.INVOICE_AMOUNT), 2)            AS TOTAL_HELD_AMOUNT,
    ai.INVOICE_CURRENCY_CODE,
    SUM(CASE WHEN SYSDATE - ai.INVOICE_DATE <= 30  THEN 1 ELSE 0 END) AS AGED_0_30,
    SUM(CASE WHEN SYSDATE - ai.INVOICE_DATE BETWEEN 31 AND 60 THEN 1 ELSE 0 END) AS AGED_31_60,
    SUM(CASE WHEN SYSDATE - ai.INVOICE_DATE BETWEEN 61 AND 90 THEN 1 ELSE 0 END) AS AGED_61_90,
    SUM(CASE WHEN SYSDATE - ai.INVOICE_DATE > 90  THEN 1 ELSE 0 END) AS AGED_OVER_90
FROM AP_HOLDS_ALL h
INNER JOIN AP_INVOICES_ALL ai
    ON  h.INVOICE_ID = ai.INVOICE_ID
INNER JOIN HR_OPERATING_UNITS hou
    ON  ai.ORG_ID = hou.ORGANIZATION_ID
WHERE h.RELEASE_LOOKUP_CODE IS NULL      -- only active (unreleased) holds
    AND ai.CANCELLED_DATE IS NULL
    AND ai.ORG_ID = :org_id
GROUP BY
    h.HOLD_LOOKUP_CODE,
    hou.NAME,
    ai.INVOICE_CURRENCY_CODE
ORDER BY TOTAL_HELD_AMOUNT DESC;