How to Detect Duplicate Payments Across Subsidiaries in NetSuite

Every AP system has a duplicate check, and every AP system still lets duplicates through. That isn't a criticism of the check — it's a structural consequence of how it works. A check that fires on exact matches removes exact matches from the population. What's left over, permanently, is everything that isn't an exact match.

This is the sequel to running an AP recovery audit in NetSuite, which covers the simple same-amount case. This one deals with the harder population: multi-subsidiary, multi-currency, and re-keyed references.

Why the survivors all look the same

Across a lot of AP data, duplicates that make it to payment fall into four recognisable groups.

The first three are findable with query logic. The fourth is genuinely hard and I'd suggest leaving it alone until the others are handled — the effort-to-yield ratio is poor and the false-positive rate is unpleasant.

Step 1 — normalise the reference

Before matching anything, strip references down to their comparable core. The goal is that INV-4471, inv 4471, INV4471 and 0004471 all reduce to the same string.

-- Reference normalisation: strip non-alphanumerics, upper-case,
-- remove common prefixes, drop leading zeros.
SELECT
  t.id,
  t.tranid                                        AS raw_ref,
  LTRIM(
    REGEXP_REPLACE(
      UPPER(REGEXP_REPLACE(t.tranid, '[^A-Za-z0-9]', '')),
      '^(INV|INVOICE|BILL|DOC|NO)', ''),
    '0')                                            AS norm_ref
FROM transaction t
WHERE t.type = 'VendBill' AND t.voided = 'F'

Run this on its own first and eyeball 50 rows. Reference conventions vary enormously between accounts, and you may need to add or remove prefixes from the regex. Getting this step right is most of the work.

One warning: normalising too aggressively creates false positives. If you strip letters entirely, a supplier using sequential numbering will match against itself constantly. Strip formatting and known prefixes; keep the alphanumeric core intact.

Step 2 — the cross-subsidiary query

Now match on the normalised reference or on amount-and-proximity, across subsidiaries. Two independent signals, because a duplicate usually trips one or the other rather than both.

-- Cross-subsidiary duplicate candidates in a OneWorld account.
WITH bills AS (
  SELECT
    t.id                                    AS bill_id,
    t.tranid                                AS bill_ref,
    LTRIM(REGEXP_REPLACE(UPPER(REGEXP_REPLACE(t.tranid, '[^A-Za-z0-9]', '')),
          '^(INV|INVOICE|BILL|DOC|NO)', ''), '0') AS norm_ref,
    t.trandate                              AS bill_date,
    t.entity                                AS vendor_id,
    BUILTIN.DF(t.entity)                   AS vendor_name,
    t.subsidiary                            AS sub_id,
    BUILTIN.DF(t.subsidiary)               AS subsidiary,
    t.currency                              AS currency_id,
    ABS(t.foreigntotal)                    AS amount
  FROM transaction t
  WHERE t.type = 'VendBill'
    AND t.voided = 'F'
    AND t.trandate >= ADD_MONTHS(CURRENT_DATE, -24)
)
SELECT
  a.vendor_name,
  a.bill_ref AS ref_a,   b.bill_ref AS ref_b,
  a.subsidiary AS sub_a, b.subsidiary AS sub_b,
  a.bill_date AS date_a, b.bill_date AS date_b,
  a.amount,
  ABS(a.bill_date - b.bill_date)          AS days_apart,
  CASE WHEN a.norm_ref = b.norm_ref AND a.amount = b.amount THEN 'ref+amount'
       WHEN a.norm_ref = b.norm_ref                       THEN 'ref only'
       ELSE                                                  'amount only'
  END                                     AS match_type
FROM bills a
JOIN bills b
  ON  a.vendor_id = b.vendor_id
  AND a.bill_id   < b.bill_id
  AND a.currency_id = b.currency_id
  AND (
        (a.norm_ref = b.norm_ref AND LENGTH(a.norm_ref) >= 4)
        OR
        (a.amount = b.amount AND ABS(a.bill_date - b.bill_date) <= 120)
      )
ORDER BY a.amount DESC

LENGTH(a.norm_ref) >= 4 stops very short references matching by chance. Note this query does not require the subsidiaries to differ — same-subsidiary re-keys are just as real, and the sub_a / sub_b columns let you separate them afterwards.

A match_type of ref+amount is close to conclusive. ref only usually means a part-payment or a corrected invoice and deserves a look. amount only is the noisiest bucket and where your recurring-charge false positives live.

Step 3 — the cross-currency case

When a supplier invoices in EUR and one entity books it in GBP, the amounts won't match in either currency at face value. What does match is the base-currency amount, approximately — the two entries were converted at different rates on different dates, so allow a tolerance.

-- Cross-currency duplicates: match on base amount within 2%,
-- for bills in different currencies.
WITH bills AS (
  SELECT
    t.id, t.tranid, t.trandate, t.entity,
    BUILTIN.DF(t.entity)      AS vendor_name,
    BUILTIN.DF(t.currency)    AS currency,
    t.currency                 AS currency_id,
    ABS(t.foreigntotal)       AS foreign_amount,
    ABS(t.total)              AS base_amount
  FROM transaction t
  WHERE t.type = 'VendBill' AND t.voided = 'F'
    AND t.trandate >= ADD_MONTHS(CURRENT_DATE, -24)
)
SELECT
  a.vendor_name,
  a.tranid AS ref_a, a.currency AS ccy_a, a.foreign_amount AS amt_a,
  b.tranid AS ref_b, b.currency AS ccy_b, b.foreign_amount AS amt_b,
  ROUND(a.base_amount, 2)                 AS base_a,
  ROUND(b.base_amount, 2)                 AS base_b,
  ABS(a.trandate - b.trandate)            AS days_apart
FROM bills a
JOIN bills b
  ON  a.entity = b.entity
  AND a.id     < b.id
  AND a.currency_id <> b.currency_id
  AND ABS(a.base_amount - b.base_amount)
      / NULLIF(a.base_amount, 0) <= 0.02      -- 2% FX tolerance
  AND ABS(a.trandate - b.trandate) <= 120
ORDER BY a.base_amount DESC

Widen the 2% tolerance if your booking dates are far apart or the currency pair is volatile. Note this query relies on t.total holding the base-currency amount, which is the standard behaviour but worth confirming in your account.

This produces a short list in most businesses — but the individual values tend to be large, because cross-currency suppliers are usually the significant international ones.

Ranking candidates by confidence

Running all three queries gives you more candidates than anyone will review. The way to make it tractable is a crude confidence score. Not machine learning — just additive signals, which in practice sorts nearly as well and can be explained to a CFO in one sentence.

Award points and sort descending:

Anything above 70 warrants immediate review. Between 40 and 70 is worth a look when you have time. Below 40, don't bother unless the value is exceptional. On the accounts I've seen this applied to, the top 5% of scored rows contains the large majority of genuine finds.

Preventing the next one

Recovery is the smaller half of this. Every duplicate you find is a duplicate that got through a process, and the process is still running.

Three changes remove most of the population:

1. Normalise references at entry, not just at audit. If AP enters references in a consistent format — or a script normalises them on save — the native duplicate check starts catching the re-keyed cases it currently misses.
2. Check across subsidiaries before payment, not after. A scheduled query against the last 30 days, run before each payment run, catches the cross-entity cases while the money is still yours.
3. Change how statements get reconciled. Before entering an apparently missing item from a supplier statement, search on amount and date rather than on reference. This is a one-line change to a work instruction and it addresses the single largest cause.

The third costs nothing and is the one most worth doing. It's also the one that usually gets skipped, because it's a habit rather than a system change — which is exactly why the duplicates keep coming.