DEV Community

fulfillmentyx
fulfillmentyx

Posted on Edited on

Keeping supplier references separate from warehouse item keys

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)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)

Collapse
 
hiredov profile image
Dov at HireDov •

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?