SC ReconciliationReconciliation Automation
  1. Home
  2. Matching
  3. Multi-SKU Grouping Logic
  4. Reconciling Kitted and Bundled Shipments to PO Lines

Reconciling Kitted and Bundled Shipments to PO Lines

↑ Part of Multi-SKU Grouping Logic.

A purchase order line for KIT-4410 x 50 should not reconcile against a single receipt line, because the warehouse rarely ships a kit as one physical unit. Distribution centers pick and ship components — the enclosure, the cable set, the mounting bracket — separately, sometimes across multiple advance shipping notices (ASNs), and the invoice may bill either the kit SKU or every component individually depending on which system generated it. The reverse also happens: a vendor ships one bundled carton SKU against a PO that was cut at the component level. Comparing a kit-level PO line to a component-level receipt fails on quantity, on SKU identity, and on unit price simultaneously, even when the shipment is exactly correct. The fix is not a looser tolerance — it is exploding the kit’s bill of materials (BOM) so both sides of the comparison share a common reconciliation grain before either side ever reaches the general Matching & Reconciliation Algorithms engine.

Operational Trigger Signals

Route a PO line through kit/bundle reconciliation instead of standard Multi-SKU Grouping Logic grouping when the pipeline’s own telemetry shows:

  1. Kit-to-component fan-out on receipt — a single ordered SKU (kit_sku) resolves to two or more distinct SKUs on the goods receipt or ASN, and those SKUs are not substitutes or partial shipments of the same item.
  2. Component-to-kit fan-in on receipt — the inverse: several PO lines for individually ordered components are satisfied by one received carton SKU that the vendor packs and ships as a pre-built bundle.
  3. BOM-master coverage gap — the kit-master (BOM) table used to explode kit_sku into its components is missing an active revision for the SKU on the PO, so the reconciliation engine cannot determine whether an unmatched component belongs to the kit or is a genuine exception.
  4. Component quantity ratio drift — a kit is defined as, say, 1 enclosure + 2 cables + 4 screws per unit, but the received component ratios do not divide evenly into whole kit units, which signals either a short-ship of one component or a stale BOM revision.
  5. Price allocation ambiguity — the PO carries one blended kit price but the invoice bills components at their individual list prices (or vice versa), so a naive price-tolerance check compares numbers that were never meant to be equal.

When any of these persist across a supplier for more than a few receiving cycles, treat kit explosion as a first-class stage that runs before tolerance evaluation, not as a special case bolted onto Setting Quantity and Price Tolerance Windows.

Step-by-Step Implementation

The core engineering decision is the reconciliation grain: do you roll every component receipt up to the kit level and compare one number to the PO line, or do you explode the PO line down to components and compare each one individually? Both are valid, and the right choice depends on whether the business cares about kit completeness as a whole or about the availability of each individual part — the same grain trade-off that motivates choosing exact identifiers over similarity scoring in Exact vs Fuzzy Matching Strategies: pick the comparison unit deliberately, don’t let mismatched grains masquerade as match failures. The procedure below supports either grain from the same exploded structure:

  1. Load the kit-master (BOM) table — for every kit_sku, retrieve its active component list with the expected quantity of each component per one kit unit, keyed by an effective-date range so historical POs explode against the BOM revision that was active when they were cut.
  2. Explode the PO line — for each PO line whose SKU is a kit, multiply the ordered kit quantity by each component’s per-kit quantity to produce the expected component quantities. A PO line for a non-kit SKU explodes to itself with a multiplier of 1.
  3. Group receipt lines to the parent key — tag every receipt/invoice line with the kit_sku it belongs to (via the BOM reverse lookup) and group by that parent key, summing received quantity per component.
  4. Roll up and compare at the chosen grain — for kit-level comparison, convert grouped component receipts back into “kits completed” using the limiting (minimum) component ratio; for component-level comparison, compare each exploded expected quantity directly against its grouped receipt quantity.
  5. Allocate price proportionally — when the PO carries a blended kit price, allocate it across components by each component’s standard-cost weight so a component-level price tolerance check has a defensible per-component target instead of comparing against zero.
  6. Log and route the residual — any component with no BOM match, a kit with a broken ratio, or a shortfall below the required completion threshold routes to the exception queue with the specific component causing the break, not just the parent kit ID.
PYTHON
import logging
from decimal import Decimal
from typing import TypedDict

import pandas as pd

logger = logging.getLogger(__name__)


class KitReconciliationResult(TypedDict):
    """One row of the kit-level reconciliation output."""
    kit_sku: str
    po_line_id: str
    ordered_kits: float
    kits_completed: float
    completion_ratio: float
    limiting_component: str
    status: str


def explode_bom(
    po_lines: pd.DataFrame,
    bom_master: pd.DataFrame,
) -> pd.DataFrame:
    """Explode kit PO lines into expected component quantities.

    po_lines expects: ['po_line_id', 'sku', 'ordered_qty'].
    bom_master expects: ['kit_sku', 'component_sku', 'qty_per_kit', 'cost_weight'].
    Non-kit SKUs (no BOM rows) pass through as their own single component.
    """
    kit_skus = set(bom_master["kit_sku"])
    is_kit = po_lines["sku"].isin(kit_skus)

    exploded = po_lines[is_kit].merge(bom_master, left_on="sku", right_on="kit_sku", how="left")
    exploded["expected_component_qty"] = exploded["ordered_qty"] * exploded["qty_per_kit"]
    logger.info("exploded %d kit PO lines into %d component rows", is_kit.sum(), len(exploded))

    # Non-kit lines: the SKU is its own single "component" with a 1:1 ratio.
    flat = po_lines[~is_kit].copy()
    flat["kit_sku"] = flat["sku"]
    flat["component_sku"] = flat["sku"]
    flat["qty_per_kit"] = 1.0
    flat["cost_weight"] = 1.0
    flat["expected_component_qty"] = flat["ordered_qty"]

    cols = ["po_line_id", "kit_sku", "component_sku", "ordered_qty",
            "qty_per_kit", "cost_weight", "expected_component_qty"]
    return pd.concat([exploded[cols], flat[cols]], ignore_index=True)


def reconcile_kit_receipts(
    exploded_po: pd.DataFrame,
    receipts: pd.DataFrame,
    min_completion_ratio: float = 0.98,
) -> list[KitReconciliationResult]:
    """Group component receipts back to the parent kit and roll up completion.

    receipts expects: ['po_line_id', 'component_sku', 'received_qty'].
    Returns one result per (po_line_id, kit_sku) using the limiting component.
    """
    grouped_receipts = (
        receipts.groupby(["po_line_id", "component_sku"], as_index=False)["received_qty"].sum()
    )
    merged = exploded_po.merge(
        grouped_receipts, on=["po_line_id", "component_sku"], how="left"
    )
    merged["received_qty"] = merged["received_qty"].fillna(0.0)

    # A component can only complete as many kits as its own received ratio allows.
    merged["component_ratio"] = merged["received_qty"] / merged["expected_component_qty"].replace(0, pd.NA)

    results: list[KitReconciliationResult] = []
    for (po_line_id, kit_sku), group in merged.groupby(["po_line_id", "kit_sku"]):
        missing = group[group["received_qty"] == 0]
        if not missing.empty and (group["expected_component_qty"] > 0).all():
            limiting = missing.iloc[0]
        else:
            limiting = group.loc[group["component_ratio"].idxmin()]

        ordered_kits = group["ordered_qty"].iloc[0]
        completion_ratio = round(float(limiting["component_ratio"] or 0.0), 4)
        kits_completed = round(completion_ratio * ordered_kits, 2)
        status = "COMPLETE" if completion_ratio >= min_completion_ratio else "PARTIAL_KIT"

        if status == "PARTIAL_KIT":
            logger.warning(
                "kit %s on PO line %s short: %s at %.2f%% (limiting component)",
                kit_sku, po_line_id, limiting["component_sku"], completion_ratio * 100,
            )

        results.append(KitReconciliationResult(
            kit_sku=kit_sku,
            po_line_id=po_line_id,
            ordered_kits=float(ordered_kits),
            kits_completed=kits_completed,
            completion_ratio=completion_ratio,
            limiting_component=str(limiting["component_sku"]),
            status=status,
        ))

    logger.info("reconciled %d kit/bundle PO lines, %d partial",
                len(results), sum(1 for r in results if r["status"] == "PARTIAL_KIT"))
    return results


def allocate_kit_price(kit_price: Decimal, cost_weights: dict[str, float]) -> dict[str, Decimal]:
    """Allocate a blended kit PO price across components by cost weight."""
    total_weight = sum(cost_weights.values())
    return {
        sku: (kit_price * Decimal(str(weight / total_weight))).quantize(Decimal("0.01"))
        for sku, weight in cost_weights.items()
    }

The limiting-component logic mirrors a bill-of-materials shortage calculation: the number of kits you can consider “complete” is bounded by the scarcest component, expressed as

kits_completed=ordered_kits×min⁡c ∈ components(receivedcqty_per_kitc)\text{kits\_completed} = \text{ordered\_kits} \times \min_{c \,\in\, \text{components}} \left( \frac{\text{received}_c}{\text{qty\_per\_kit}_c} \right)

Reporting only the aggregate kits_completed number hides which component caused the shortfall, which is why the function above carries limiting_component through to the output row instead of discarding it after the minimum is taken.

Kit PO line exploded to components and rolled back up to completion A single parent PO line for kit SKU KIT-4410 explodes through the BOM master into three expected components: an enclosure, a cable set, and a mounting bracket, each with its own expected quantity. Receipt lines from one or more ASNs are grouped by component SKU and matched against those expected quantities. The rollup stage recombines the three component match ratios using the limiting component to produce a single kit completion percentage reported back against the original PO line. PO line: KIT-4410 x 50 BOM explode qty_per_kit lookup Enclosure expect 50 Cable set expect 100 Bracket expect 200 Grouped ASN received 50 Grouped ASN received 96 Grouped ASN received 200 Rollup: limiting = Cable set 96 / 100 = 96% 48 / 50 kits complete component match rollup

Configuration Reference

Parameter Accepted values Default Notes
bom_effective_date date range per revision required Explode against the BOM revision active on the PO date, never the current one.
min_completion_ratio 0.90 – 1.00 0.98 Below this, a kit reports PARTIAL_KIT even if most components arrived.
reconciliation_grain kit_level, component_level kit_level Kit-level for completeness reporting; component-level when parts have independent inventory value.
price_allocation_method cost_weight, equal_split, list_price cost_weight How a blended kit price is spread across components for tolerance checks.
substitute_component_map SKU → SKU list {} Approved substitutions (e.g. revision B bracket) that should not count as a shortfall.
unmapped_component_action exception, hold, auto_flat_match exception Behavior when a received SKU has no BOM entry for the ordered kit.

Debugging & Recovery

Kit reconciliation fails in ways that flat SKU matching does not, so triage needs kit-specific diagnostics:

  • Stale BOM revision — a PO cut before a BOM engineering change explodes against the wrong component list, producing phantom shortfalls on components that were actually superseded. Always join on bom_effective_date, and alert when a PO date falls outside every known revision’s coverage window.
  • Orphan component receipts — a received SKU with no matching kit_sku in the BOM master (a new component, a data-entry typo, or a substitute part) cannot be grouped and lands in the exception queue tagged UNMAPPED_COMPONENT; resolve by extending the BOM master, not by silently matching on partial SKU strings.
  • Ratio drift false positives — when component_ratio is consistently just under 1.0 across every kit for one supplier, suspect a BOM qty_per_kit that is wrong rather than dozens of independent short-ships; a systematic 2–3% shortfall across all kits is a master-data bug, not a shipping pattern.
  • Fan-in double counting — when several component-level PO lines are satisfied by one bundled receipt SKU, make sure the grouping key ties every original PO line to that one receipt so the same physical units are not counted as fulfilling more than one PO line.
  • Price allocation reconciliation — allocated component prices must sum back to the original kit price within a cent after rounding; log and alert on any allocation set that does not, since silent rounding drift compounds across high-volume kit SKUs.

FAQ

Should I reconcile at the kit level or the component level?

Default to kit-level completion when the business cares whether the customer or downstream line can use the kit at all — a 96% complete kit is still not shippable if the missing 4% is the enclosure. Switch to component-level when components carry independent inventory value or are individually billable, since kit-level rollup can mask a genuine shortfall in one part behind a healthy aggregate. Many pipelines run both: component-level for inventory accuracy, kit-level for order-fulfillment status, from the same exploded table.

What happens when a receipt contains a component that is not in the kit’s BOM?

It routes to the exception queue tagged UNMAPPED_COMPONENT rather than being silently dropped or force-matched. This is usually one of three things: a genuinely extra item that should not have shipped, a substitute part that needs to be added to substitute_component_map, or a BOM master that has not been updated for a new component revision. Never auto-match on partial SKU similarity here — that reintroduces the exact false-positive risk that When to Use Fuzzy Matching Over Exact PO Matching warns against for structurally different keys.

How do I handle a PO written at the component level when the vendor ships a pre-built bundle?

Treat it as the mirror case of kit explosion: define a reverse BOM entry that maps the vendor’s bundle SKU back to the set of component PO lines it satisfies, then group those PO lines under that bundle key before running the same rollup logic. The completion math is identical — only the direction of the explosion changes.