By YX Fulfillment
A receiving file can list supplier SKU X17 from two different suppliers. Those labels can legitimately refer to different warehouse items. Give the warehouse item its own identifier and resolve a supplier reference using both the supplier and that supplier's SKU. If a site or catalogue adds another namespace, include it explicitly in the mapping.
Run a packing-list import against the same two inbound rows before and after introducing supplier B's mapping. The fixture keeps unmatched receiving records in the result for review.
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE supplier_map (
supplier TEXT NOT NULL, supplier_sku TEXT NOT NULL,
item_key TEXT NOT NULL, PRIMARY KEY (supplier, supplier_sku)
);
CREATE TABLE incoming (
line_id TEXT PRIMARY KEY,
supplier TEXT NOT NULL, supplier_sku TEXT NOT NULL
);
INSERT INTO supplier_map VALUES ('A','X17','WH-441');
INSERT INTO incoming VALUES ('R1','A','X17'), ('R2','B','X17');
""")
def resolve():
return db.execute("""
SELECT incoming.line_id, supplier_map.item_key
FROM incoming
LEFT JOIN supplier_map
ON incoming.supplier = supplier_map.supplier
AND incoming.supplier_sku = supplier_map.supplier_sku
ORDER BY incoming.line_id
""").fetchall()
before = resolve()
db.execute("INSERT INTO supplier_map VALUES ('B','X17','WH-612')")
after = resolve()
wrong_rows = db.execute("""
SELECT COUNT(*) FROM incoming
JOIN supplier_map USING (supplier_sku)
""").fetchone()[0]
assert before == [("R1", "WH-441"), ("R2", None)]
assert after == [("R1", "WH-441"), ("R2", "WH-612")]
assert wrong_rows == 4
print("Before supplier B mapping:", before)
print("After supplier B mapping:", after)
print("Rows from SKU-only join:", wrong_rows)
Output from a local run on Python 3.9.6
Before supplier B mapping: [('R1', 'WH-441'), ('R2', None)]
After supplier B mapping: [('R1', 'WH-441'), ('R2', 'WH-612')]
Rows from SKU-only join: 4
A row without a mapping remains visible for resolution. Adding the mapping for B then affects only R2. Joining on supplier SKU alone would match every inbound row against both supplier records. A report can detect that symptom by checking the promised number of results per inbound line.
Some suppliers may intentionally share a warehouse item when their products have been approved as interchangeable. Keep two supplier keys that map to the same warehouse key in that case. Where mappings change over time, save the mapping decision against a receipt or use dated mappings that prevent a later change from rewriting the earlier stock history.
Top comments (1)
Good point on the composite key. One extra safeguard: enforce uniqueness on (supplier, supplier_sku, valid_from) and give each mapping a status (proposed, approved, retired). Suppliers often reuse or renumber SKUs after a spec change, so an old mapping silently pointing at a new physical item is a nastier failure than a missing one. Sending unmatched rows to a review queue instead of auto-matching also keeps a human in the loop for new suppliers. Do you handle pack-size differences (a supplier's case of 12 vs each) in the same mapping table, or in a separate unit-conversion layer?