Subversion Repositories SmartDukaan

-- Record where stock came from and what it cost, on the stock itself (2026-09-17)

--

-- WHY

--   Stock carried no record of its source, so every internal movement re-derived the origin by tracing serials

--   (impossible for non-serialised stock that had already moved) and then priced the move from that vendor's CURRENT

--   catalog TP. Standard practice is a receipt layer: each row of stock knows which outside vendor it came from and

--   what it cost when it arrived, and an internal movement moves it at that cost.

--

--   inventoryItem.origin_vendor_id   - outside vendor the unit came from. NULL = genuinely unknown; never a guess

--                                      and never an internal supplier.

--   inventoryItem.receipt_unit_cost  - unit price the stock was received at (warehouse.lineitem.unitPrice of the PO

--                                      line it arrived on). What an internal movement moves it at.

--   lineitem.origin_vendor_id        - origin the PO line was priced from, so a receiving GRN can copy it onward.

--

-- ORDERING: run before deploying the code that writes these columns. Both ALTERs are ADD COLUMN, which MySQL 5.7 does

-- INPLACE and allows reads/writes throughout; inventoryItem is ~1.5M rows so expect a couple of minutes.

ALTER TABLE warehouse.inventoryItem

  ADD COLUMN origin_vendor_id INT NULL

    COMMENT 'outside vendor this stock came from; NULL = unknown, never an internal supplier',

  ADD COLUMN receipt_unit_cost FLOAT NULL

    COMMENT 'unit price this stock was received at; what an internal movement moves it at';

ALTER TABLE warehouse.lineitem

  ADD COLUMN origin_vendor_id INT NULL

    COMMENT 'origin vendor this PO line was priced from; copied onto stock at GRN';

-- Backfill, facts only --------------------------------------------------------------------------------------------

-- 1. Stock received directly on an outside vendor's PO: that vendor, and the price on its PO line.

UPDATE warehouse.inventoryItem ii

  JOIN warehouse.purchase p        ON p.id = ii.purchaseId

  JOIN warehouse.purchaseorder po  ON po.id = p.purchaseOrder_id

  JOIN warehouse.supplier s        ON s.id = po.supplierId AND s.internal = 0

  LEFT JOIN warehouse.lineitem li  ON li.purchaseOrder_id = po.id AND li.itemId = ii.itemId

   SET ii.origin_vendor_id = po.supplierId,

       ii.receipt_unit_cost = li.unitPrice

 WHERE ii.origin_vendor_id IS NULL;

-- 2. Serialised stock that moved internally: the vendor of the most recent external purchase of that serial, and the

--    cost recorded against that purchase. Same rule billing uses to price a sale.

UPDATE warehouse.inventoryItem ii

  JOIN (SELECT o.id,

               (SELECT po2.supplierId FROM warehouse.inventoryItem o2

                  JOIN warehouse.purchase p2 ON p2.id = o2.purchaseId

                  JOIN warehouse.purchaseorder po2 ON po2.id = p2.purchaseOrder_id

                  JOIN warehouse.supplier s2 ON s2.id = po2.supplierId AND s2.internal = 0

                 WHERE o2.serialNumber = o.serialNumber AND o2.itemId = o.itemId

                 ORDER BY o2.created DESC, o2.id DESC LIMIT 1) AS origin_vendor_id,

               (SELECT o3.receipt_unit_cost FROM warehouse.inventoryItem o3

                  JOIN warehouse.purchase p3 ON p3.id = o3.purchaseId

                  JOIN warehouse.purchaseorder po3 ON po3.id = p3.purchaseOrder_id

                  JOIN warehouse.supplier s3 ON s3.id = po3.supplierId AND s3.internal = 0

                 WHERE o3.serialNumber = o.serialNumber AND o3.itemId = o.itemId

                 ORDER BY o3.created DESC, o3.id DESC LIMIT 1) AS receipt_unit_cost

          FROM warehouse.inventoryItem o

         WHERE o.origin_vendor_id IS NULL AND o.serialNumber IS NOT NULL AND LENGTH(o.serialNumber) > 0

           AND o.currentQuantity > 0) traced ON traced.id = ii.id

   SET ii.origin_vendor_id  = traced.origin_vendor_id,

       ii.receipt_unit_cost = traced.receipt_unit_cost

 WHERE traced.origin_vendor_id IS NOT NULL;

-- 3. Everything else keeps NULL: non-serialised stock that has already moved internally has no recoverable origin,

--    and a guess must never be written as a fact. Those units are priced from the latest externally approved catalog

--    price at movement time, as before.

-- What the backfill reached, for stock currently held.

SELECT COUNT(*) AS in_stock_rows,

       SUM(origin_vendor_id IS NOT NULL) AS with_origin,

       SUM(receipt_unit_cost IS NOT NULL) AS with_cost,

       SUM(origin_vendor_id IS NULL) AS unknown_origin

  FROM warehouse.inventoryItem WHERE currentQuantity > 0;