Subversion Repositories SmartDukaan

-- Remove vendor catalog pricing held against INTERNAL suppliers (2026-09-12)

--

-- WHY

--   Internal suppliers are our own warehouses. They never negotiated a price with anyone - every row they

--   hold was copied there from some other vendor, which is exactly what made internal movement prices drift

--   further from reality on each hop. Since r37603-05 nothing reads them: an internal movement is priced

--   from the stock being moved, resolved back to the ORIGINAL EXTERNAL vendor, and receiving no longer

--   validates price for those movements at all. The rows are now worse than useless - a stale one would

--   silently win over the derived price if any lookup were ever reintroduced.

--

-- ORDERING - deploy the fofo WAR carrying the warehouse-grn-request-items.vm fix FIRST.

--   That template rendered $vendorCatalogPricingLogMap.get($catalogId).getTransferPrice() with no null

--   guard, so with these rows gone every internal-supplier GRN line would print the raw reference text in

--   the System Price column. Velocity fails quietly, so it would not error - it would just look broken.

--   The fix renders "-" instead, which is honest: there is no independent system price for an internal

--   movement. V2FofoGrnController returns the same map as JSON and is unaffected by an empty map.

--

-- REVERSIBLE: every deleted row is copied to a _bak_ table first. To restore, INSERT ... SELECT back.

--   Membership is frozen into the backups at run time, so a supplier whose internal flag changes later

--   cannot silently widen or narrow what was removed.

-- Multi-table DELETE (the "DELETE alias FROM" form) needs a default schema even when every table is

-- fully qualified, so select one up front rather than relying on how the client was invoked.

USE inventory;

CREATE TABLE inventory._bak_vcp_internal_20260912 AS

SELECT p.* FROM inventory.vendor_catalog_pricing p

  JOIN warehouse.supplier s ON s.id = p.vendor_id AND s.internal = 1;

CREATE TABLE inventory._bak_vcpl_internal_20260912 AS

SELECT l.* FROM inventory.vendor_catalog_pricing_log l

  JOIN warehouse.supplier s ON s.id = l.vendor_id AND s.internal = 1;

CREATE TABLE inventory._bak_vip_internal_20260912 AS

SELECT v.* FROM inventory.vendoritempricing v

  JOIN warehouse.supplier s ON s.id = v.vendor_id AND s.internal = 1;

-- Expected as of 2026-09-12: 12,372 / 17,241 / 33,252 rows across 16 internal suppliers.

SELECT (SELECT COUNT(*) FROM inventory._bak_vcp_internal_20260912)  AS pricing_backed_up,

       (SELECT COUNT(*) FROM inventory._bak_vcpl_internal_20260912) AS log_backed_up,

       (SELECT COUNT(*) FROM inventory._bak_vip_internal_20260912)  AS itempricing_backed_up;

-- Delete against the frozen backups rather than re-reading supplier.internal, so the rows removed are

-- exactly the rows preserved.

DELETE p FROM inventory.vendor_catalog_pricing p

  JOIN inventory._bak_vcp_internal_20260912 b

    ON b.vendor_id = p.vendor_id AND b.catalog_id = p.catalog_id;

DELETE l FROM inventory.vendor_catalog_pricing_log l

  JOIN inventory._bak_vcpl_internal_20260912 b ON b.id = l.id;

DELETE v FROM inventory.vendoritempricing v

  JOIN inventory._bak_vip_internal_20260912 b

    ON b.vendor_id = v.vendor_id AND b.item_id = v.item_id;

-- Verify: all three must be 0.

SELECT (SELECT COUNT(*) FROM inventory.vendor_catalog_pricing p

          JOIN warehouse.supplier s ON s.id = p.vendor_id AND s.internal = 1) AS pricing_left,

       (SELECT COUNT(*) FROM inventory.vendor_catalog_pricing_log l

          JOIN warehouse.supplier s ON s.id = l.vendor_id AND s.internal = 1) AS log_left,

       (SELECT COUNT(*) FROM inventory.vendoritempricing v

          JOIN warehouse.supplier s ON s.id = v.vendor_id AND s.internal = 1) AS itempricing_left;

-- Rollback:

-- INSERT INTO inventory.vendor_catalog_pricing     SELECT * FROM inventory._bak_vcp_internal_20260912;

-- INSERT INTO inventory.vendor_catalog_pricing_log SELECT * FROM inventory._bak_vcpl_internal_20260912;

-- INSERT INTO inventory.vendoritempricing          SELECT * FROM inventory._bak_vip_internal_20260912;