SC ReconciliationReconciliation Automation
  1. Home
  2. Matching
  3. Multi-SKU Grouping Logic

Multi-SKU Grouping Logic

Line-by-line reconciliation fractures the moment a procurement feed delivers consolidated freight, split invoices, or partial fulfillments that span multiple stock-keeping units. A single delivery against a blanket release can arrive as three goods receipts and two invoices, none of which align one-to-one with the original purchase order lines. Comparing those records individually produces a cascade of false exceptions: an invoice that is correct in aggregate fails because no single line matches, and a genuine short-ship hides inside an otherwise-balanced shipment.

Multi-SKU grouping resolves this structural mismatch by elevating reconciliation from scalar line-item comparisons to composite order sets. It is the mandatory preprocessing stage that runs before any record-level evaluation in modern Matching & Reconciliation Algorithms: it partitions raw documents into the order sets they logically belong to, computes aggregate metrics over each set, and only then hands clean, comparable units to the matching engine. Get the grouping grain right and downstream tolerance checks become trivial; get it wrong and every later stage inherits split-line noise it cannot recover from.

Core Concept & Decision Criteria

A group is the smallest set of records that must reconcile together because the trading relationship treats them as a unit — typically all receipts and invoice lines tied to one purchase order, ship-to location, and delivery window. The grouping grain is a deliberate engineering choice, not a property of the data. Choosing it too coarse (e.g. grouping by vendor alone) masks genuine variance across unrelated orders; choosing it too fine (raw PO line) reintroduces the split-line problem grouping exists to solve.

The decision signal for switching from flat line matching to grouped reconciliation is concrete and measurable: when a meaningful fraction of records arrive in a many-to-many relationship between PO lines, receipts, and invoice lines. If every invoice line maps cleanly to exactly one PO line, deterministic Exact vs Fuzzy Matching Strategies on the line key are sufficient and grouping only adds overhead. Once consolidation, backorders, or freight bundling appear, grouping becomes the only way to evaluate completeness without false exceptions.

Property Line-level reconciliation Group-level reconciliation
Comparison unit Single PO line ↔ single invoice line Composite order set (all lines sharing a key)
Handles split invoices No — each split fails individually Yes — splits sum to the group total
Handles partial / over receipts Poorly — every partial is an exception Yes — tracked as a completion ratio
False-exception rate on bundled freight High Low
Variance masking risk None Real — cross-line offsets can net to zero
Best fit Mature ERP, 1:1 master-data discipline 3PL/consolidated freight, blanket releases, OCR invoices

The trade-off to manage is variance masking: because group metrics aggregate, a positive deviation on one SKU can silently cancel a negative deviation on another. Grouping logic must therefore preserve per-SKU detail alongside the rollup so that a balanced total never hides an unbalanced composition. The SKU_VARIANCE_COUNT metric introduced below exists specifically to surface that case.

Implementation

Production-grade grouping requires a stable composite key that survives ERP transformations, EDI format migrations, and manual data-entry overrides. The key concatenates transactional anchors that remain invariant across document types — purchase order, advance shipping notice (ASN), and invoice — so the same logical order lands in the same partition regardless of which system emitted the record:

  • PO_HEADER_ID — normalized to uppercase, stripped of vendor-specific prefixes
  • VENDOR_CODE — canonicalized against supplier master data
  • SHIP_TO_LOCATION — standardized to a GLN or internal facility code
  • EXPECTED_DELIVERY_WINDOW — bucketed to ±24h or aligned to ASN DTM02 segments
  • ORDER_TYPE — e.g. STANDARD, BLANKET_RELEASE, CONSIGNMENT

SKU formats vary widely across trading partners (SKU-12345, 12345, 12345-001, or GS1-compliant GTINs), so canonicalization must run upstream via regex extraction or deterministic mapping tables — the same normalization discipline applied when Parsing CSV and Excel Feeds with Pandas or validating contracts with Schema Validation Using Pydantic. Grouping operates orthogonally to identifier matching: it aggregates records first, then applies matching rules to the unified set.

PYTHON
import hashlib
import logging
from typing import Dict, List, Tuple

import pandas as pd

logger = logging.getLogger("reconciliation.grouping")

KEY_FIELDS = ("po_header_id", "vendor_code", "ship_to_loc", "order_type")


def build_group_key(row: pd.Series, window_days: int = 1) -> str:
    """Generate a deterministic composite key for multi-SKU grouping.

    The key is stable across PO/ASN/invoice document types: identical
    logical orders always hash to the same partition, which is what makes
    the grouping stage idempotent under pipeline replays.
    """
    components = [str(row.get(field, "")).strip().upper() for field in KEY_FIELDS]

    # Bucket the delivery date so freight that lands a day early/late still
    # groups with its purchase order instead of spawning a phantom set.
    delivery = pd.to_datetime(row.get("expected_delivery_date"), errors="coerce")
    if pd.isna(delivery):
        bucket = "NO_DATE"
    else:
        bucket = (delivery.normalize() - pd.Timedelta(days=delivery.dayofyear % window_days)).strftime("%Y-%m-%d")
    components.append(bucket)

    raw_key = "|".join(components)
    # Truncated SHA-256 gives a fixed-length, collision-resistant id.
    return hashlib.sha256(raw_key.encode()).hexdigest()[:16]


def normalize_and_group(df: pd.DataFrame, window_days: int = 1) -> Tuple[pd.DataFrame, Dict[str, List[int]]]:
    """Attach a group key to every record and return the partition index."""
    df = df.copy()
    df["group_key"] = df.apply(build_group_key, axis=1, window_days=window_days)
    grouped = df.groupby("group_key", sort=False, observed=True)
    group_index = {key: list(idx.index) for key, idx in grouped}
    logger.info("Partitioned %d records into %d groups", len(df), len(group_index))
    return df, group_index

Once records are partitioned, reconciliation transitions from scalar equality to vector validation. Each composite set carries aggregated metrics that describe the collective state of the fulfillment rather than any single line. The weighted average unit price normalizes mixed-quantity lines so that a high-volume SKU is not outweighed by a one-unit add-on, and the line completion ratio expresses how much of the order has physically arrived:

pˉg=∑i∈gqi⋅pi∑i∈gqirg=∑i∈gqirecv∑i∈gqiord\bar{p}_{g} = \frac{\sum_{i \in g} q_i \cdot p_i}{\sum_{i \in g} q_i} \qquad r_{g} = \frac{\sum_{i \in g} q^{\text{recv}}_i}{\sum_{i \in g} q^{\text{ord}}_i}
PYTHON
def aggregate_group_metrics(df: pd.DataFrame) -> pd.DataFrame:
    """Roll each group up to the vector metrics the tolerance engine consumes."""

    def _reduce(g: pd.DataFrame) -> pd.Series:
        qty_ordered = g["qty_ordered"].sum()
        qty_received = g["qty_received"].sum()
        weighted_price = (g["qty_ordered"] * g["unit_price"]).sum() / qty_ordered if qty_ordered else float("nan")
        # Per-SKU variance preserved so a balanced TOTAL cannot hide an
        # unbalanced COMPOSITION (the variance-masking failure mode).
        sku_variance = int((g["qty_received"] != g["qty_ordered"]).sum())
        return pd.Series({
            "total_qty_ordered": qty_ordered,
            "total_qty_received": qty_received,
            "weighted_avg_unit_price": round(weighted_price, 6),
            "sku_variance_count": sku_variance,
            "line_completion_ratio": round(qty_received / qty_ordered, 6) if qty_ordered else float("nan"),
            "distinct_sku_count": g["sku"].nunique(),
        })

    rollup = df.groupby("group_key", sort=False, observed=True).apply(_reduce)
    logger.info("Computed metrics for %d groups", len(rollup))
    return rollup.reset_index()

The state of each group is then driven through a small finite state machine — PENDING → PARTIAL_MATCH → TOLERANCE_ACCEPTED → EXCEPTION → CLOSED — based on the aggregated variance, so partial receipts and over-shipments are tracked explicitly rather than prematurely closing the reconciliation cycle.

Multi-SKU grouping pipeline and group-state decision flow Raw PO, ASN and invoice records flow left to right through four pipeline stages — canonicalize SKU and keys, build the composite group key, partition into order sets, and aggregate vector metrics. Each new group enters the PENDING state, then drops into a decision cascade. First gate: is the SKU variance count greater than zero? If yes, the group's per-SKU composition is re-checked before the tolerance gate; if no it goes straight there. Second gate: are the group totals within tolerance? If yes the group reaches TOLERANCE_ACCEPTED and CLOSED. If no, a third gate asks whether the completion ratio is below 1.0: if yes the group becomes PARTIAL_MATCH and is held for later receipts, if no it routes to the EXCEPTION dead-letter queue. A lifecycle strip lists the five states PENDING, PARTIAL_MATCH, TOLERANCE_ACCEPTED, EXCEPTION and CLOSED. Raw PO / ASN / invoice records Canonicalize SKU + keys Build composite group key Partition into order sets Aggregate vector metrics new group enters as PENDING SKU variance count > 0? Re-check per-SKU composition Group totals within tolerance? TOLERANCE_ACCEPTED → CLOSED Completion ratio < 1.0? PARTIAL_MATCH hold for later receipts EXCEPTION queue dead-letter by group_key yes no yes no yes no Group lifecycle PENDING PARTIAL_MATCH TOLERANCE_ACCEPTED EXCEPTION CLOSED

Configuration & Threshold Calibration

The grouping key and its tolerances are version-controlled configuration, not embedded constants — they change as trading partners and freight patterns change. The two parameters that most affect grouping accuracy are the delivery-window bucket and the per-tier variance budget.

Parameter Typical range Rationale
delivery_window_days 1–3 days Wide enough to absorb early/late freight, narrow enough to keep distinct release cycles in separate groups.
qty_completion_floor 0.95–1.00 Below this ratio a group stays PARTIAL_MATCH and waits for further receipts instead of erroring.
sku_variance_ceiling 0–2 lines Maximum number of off-quantity SKUs tolerated before composition review is forced.
group_size_alert 250 lines Oversized groups usually signal a key that is too coarse (e.g. missing ship_to_loc).
weighted_price_band_pct ±2–5% Applied to the group weighted-average price, tier-dependent.

Tolerance tiers belong with the trading partner, not the algorithm. A strategic vendor on EDI with strong master-data discipline can run tight windows; a low-volume spot supplier delivering OCR-scanned invoices needs slack. Rather than re-implementing numeric boundaries here, the aggregated weighted_avg_unit_price and line_completion_ratio feed directly into Setting Quantity and Price Tolerance Windows, which evaluates the group totals against the per-tier bands. Where those bands need to flex at runtime by commodity or supplier, Configuring Dynamic Price Tolerance Thresholds supplies the injection pattern. The grouping stage simply guarantees that the numbers handed to those checks describe a coherent order set.

Orchestration & Integration

Grouping sits squarely in the middle of the pipeline: it consumes canonicalized records and produces order sets for the matching tiers. Its upstream contract is clean, typed data — SKUs already normalized, dates already ISO-8601, document types tagged — which is the output of the ingestion and mapping layers such as EDI 810 vs 850 Schema Mapping. If the grouping stage ever has to guess at a SKU format or a delivery date, that work has leaked from where it belongs and the partition becomes non-deterministic.

Downstream, grouped sets route into the bulk matching engine, which applies hierarchical rules: header-level validation first, then line-level SKU reconciliation within the established group boundary. This ordering matters for performance as much as correctness — by collapsing an N×N line comparison into grouped set operations, grouping is the structural precondition for the indexed, partitioned execution described in Algorithm Performance Optimization. Records that survive grouping but fail deterministic linkage fall through to the probabilistic pass; deciding when that fallback is warranted is covered in When to Use Fuzzy Matching Over Exact PO Matching.

Idempotency is the property that ties the stage into a replayable pipeline. Because the composite key is a pure function of canonical fields, re-running a batch reproduces identical partitions, identical aggregates, and identical state transitions — so a replayed or late-arriving record lands in its existing group and updates the completion ratio rather than spawning a duplicate set. Persisting group state incrementally (keyed by group_key) lets late receipts re-open a PARTIAL_MATCH group and re-evaluate it without reprocessing the whole feed.

Debugging & Pipeline Recovery

When a group misbehaves, the triage path runs through the metrics it already carries.

  • DLQ triage. Route EXCEPTION-state groups to a dead-letter queue keyed by group_key, carrying the full member record list so an analyst can see every PO line, receipt, and invoice line that landed in the set. Never dead-letter individual lines from a group — the unit of recovery is the whole order set.
  • Failure-reason taxonomy. Tag each dead-lettered group with one of SHORT_RECEIPT (completion ratio below floor with no pending receipts), OVER_SHIP (ratio > 1.0 beyond tolerance), PRICE_BAND_BREACH (weighted price outside the tier band), COMPOSITION_IMBALANCE (sku_variance_count high while totals net to zero — the variance-masking case), ORPHAN_INVOICE (invoice lines with no PO members), or KEY_OVERSIZED (group exceeds group_size_alert). This single field turns a flat queue into a triage dashboard.
  • Monitoring signals & alert thresholds. Track group count, average group size, and DLQ volume by failure reason per vendor. A sudden spike in KEY_OVERSIZED or a collapse in group count usually means a key component went null upstream (a missing ship_to_loc merges unrelated orders). A sustained climb in ORPHAN_INVOICE points at a vendor SKU-aliasing change that canonicalization no longer covers.
  • Audit log fields. Emit group_key, member_record_ids, total_qty_ordered, total_qty_received, weighted_avg_unit_price, sku_variance_count, group_state, and evaluated_at for every group — resolved or not — to append-only storage so SOX and internal audit reviews can replay any grouping decision. The classification and isolation patterns for that audit boundary are covered in Data Security Boundaries for Procurement Systems.

Adhering to GS1 identification standards for SKU normalization and leaning on the official pandas groupby documentation for memory-efficient partitioning keeps the stage deterministic at high throughput. Columnar storage (Parquet) for the persisted group state and strict typing on the key fields close the remaining sources of partition drift.

FAQ

When should I group records instead of matching PO lines one-to-one?

Group as soon as a meaningful share of your feed arrives many-to-many — consolidated freight, split invoices, blanket-release draws, or backordered partials. If every invoice line maps cleanly to one PO line under master-data governance, flat line matching is faster and grouping only adds overhead. The trigger is the shape of the data, not the volume.

How wide should the delivery-window bucket be?

Start at ±1 day and widen only if you see legitimately related receipts splitting into separate groups. Too wide and distinct release cycles for the same PO collapse together, masking variance; too narrow and a truck that arrives a day late spawns a phantom group. Tune it per lane using the observed early/late spread, not a global default.

A group’s totals balance but I know a SKU was short-shipped — why didn’t it flag?

That is variance masking: a positive deviation on one SKU cancels a negative one on another, so the aggregate nets to zero. This is exactly why the rollup keeps sku_variance_count and per-SKU detail alongside the totals. Force a composition re-check whenever the variance count is non-zero, regardless of whether the group total lands inside tolerance.

Why hash the composite key instead of grouping on the raw concatenated string?

A truncated SHA-256 gives a fixed-length, index-friendly identifier that does not balloon with long PO numbers or facility codes, and it is stable to log, persist, and join on across systems. The hash is a pure function of the canonical fields, so grouping stays idempotent — the same logical order always produces the same key on every replay.

How do late-arriving receipts re-open a closed group?

Persist group state incrementally keyed by group_key. A late receipt rebuilds the same key, lands in the existing partition, and re-evaluates the completion ratio — moving a PARTIAL_MATCH group toward CLOSED without reprocessing the entire feed. Only groups in a terminal CLOSED/EXCEPTION state with an audit reason should resist reopening, and then only through an explicit adjustment path.