Investigate invoices matching zero or multiple contracts

Joining an AP export to a contract register can increase reported spend without adding a purchase. One invoice matches two agreements, both rows expand into the result, and its amount is counted twice. A zero-match invoice can create the opposite error if an inner join removes it.

This method owns those join exceptions. The SUM guide retains the chosen procurement metric, and the unknown-status guide handles incomplete evidence. A contract match is an input to the selected policy, not a universal definition of managed spend.

Establish the invoice and contract grains

Keep a unique source-company/document/line key for each AP line. Contract rows should identify agreement/version, supplier, covered category or items, covered units/sites, effective interval and relevant channel restrictions. Separate contract headers from scope rows so a contract covering several categories is not mistaken for several independent agreements.

Normalize supplier keys through a reviewed crosswalk before matching; see the supplier normalization guide. A name resemblance is not enough to select an agreement.

Record which invoice date the policy uses for applicability. Invoice date, posting date and purchase date can fall on different sides of an agreement's expiry. The contract owner should supply the governing rule instead of the analyst choosing whichever date produces a match.

Count candidates before assigning a result

Generate candidates using reviewed supplier identity, then test category/item coverage, unit/site coverage, applicable dates and any selected policy conditions. Retain the count and failure reasons at each stage.

One valid candidate can support a classification. Zero candidates require investigation. Multiple valid candidates require an approved selection rule or an explicit ambiguous result. Never resolve overlap by keeping the first spreadsheet row or picking the newest agreement without evidence.

Microsoft documents multi-column merges and expansion of matches. Check match cardinality: a successful operation does not establish a one-to-one business relationship. Merge queries overview.

A synthetic four-line investigation

This example's selected policy requires an applicable current contract. It is an illustrative policy choice, not a replacement definition for all procurement teams.

A naive left join that expands both I-02 candidates produces five rows and $14,000. The extra $3,000 is duplicated evidence, not spend. Dropping I-03 because it has no match would conceal $2,000 more.

The reviewed output keeps four invoice lines and $11,000. I-01 is confirmed under the example policy. I-02 and I-03 remain unknown. I-04 is confirmed unmanaged only if the reviewed register and other required evidence are sufficiently complete to establish that no applicable agreement exists. Otherwise it also stays unknown.

Resolve overlap from scope and precedence

Two agreements may represent a framework and a specific order, different units, different categories or an amendment that supersedes an earlier version. Check the actual scope and source documents. Store the reviewed selection, reason, effective period and approver.

If both contracts legitimately apply, represent their attributes without duplicating the invoice's purchasing amount. A many-to-many evidence relationship can exist while the spend table remains one row per source line. If an amount must be allocated, document the allocation basis and verify that allocated amounts sum to the original line.

An agreement whose effective interval overlaps its predecessor may simply have a missing end date. Repair the register with authority and preserve the old version; do not silently manufacture an end date during the join.

Make zero matches useful

Separate unresolved supplier, missing category, uncovered unit, out-of-period contract and absent archive. Each needs a different action. Ask for the exact dated scope row rather than sending a general request to clean the contract file.

Keep no-match amounts in the source control total. Use an Unknown category or exception status until evidence is reviewed. A failed join by itself does not prove off-contract misconduct, and an expired agreement in one category does not prove the supplier has no other applicable agreement.

Verify before issuing the report

Reconcile source count and amount before and after matching, require at most one selected classification per line, inspect boundary-date examples and test an intentional overlap. Report candidate counts and unresolved amount beside the managed-spend view.

The procurement pilot specification includes these checks. Bring a small authorized invoice/contract sample to discuss an analysis scope. The method does not promise automatic legal interpretation, complete contract discovery or an already-built connection to your contract system.