| 36512 |
amit |
1 |
-- Reconcile inventory.currentinventorysnapshot with scan truth
|
|
|
2 |
-- Only for active tag_listing items, skips zero rows
|
|
|
3 |
|
|
|
4 |
-- Step 1: Detect drift (use this output for email alert)
|
|
|
5 |
-- Returns items where snapshot != scan truth (both under-counted and phantom)
|
|
|
6 |
SELECT scan_truth.itemId item_id, scan_truth.currentWarehouseId warehouse_id,
|
|
|
7 |
scan_truth.avail AS scan_avail, IFNULL(cis.availability, 0) AS snap_avail,
|
|
|
8 |
(scan_truth.avail - IFNULL(cis.availability, 0)) AS delta
|
|
|
9 |
FROM (
|
|
|
10 |
SELECT ii.itemId, ii.currentWarehouseId,
|
|
|
11 |
SUM(CASE WHEN s.type IN ('PURCHASE','SALE_RET','MARKED_GOOD') THEN s.quantity
|
|
|
12 |
WHEN s.type IN ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD') THEN -s.quantity
|
|
|
13 |
ELSE 0 END) avail
|
|
|
14 |
FROM warehouse.inventoryItem ii
|
|
|
15 |
JOIN warehouse.scanNew s ON s.inventoryItemId = ii.id
|
|
|
16 |
JOIN catalog.tag_listing tl ON tl.item_id = ii.itemId AND tl.active = 1
|
|
|
17 |
WHERE ii.currentWarehouseId > 0
|
|
|
18 |
GROUP BY ii.itemId, ii.currentWarehouseId
|
|
|
19 |
HAVING avail > 0
|
|
|
20 |
) scan_truth
|
|
|
21 |
LEFT JOIN inventory.currentinventorysnapshot cis
|
|
|
22 |
ON cis.item_id = scan_truth.itemId AND cis.warehouse_id = scan_truth.currentWarehouseId
|
|
|
23 |
WHERE IFNULL(cis.availability, -1) != scan_truth.avail;
|
|
|
24 |
|
|
|
25 |
-- Step 2: Sync — upsert scan truth for active tag_listing items with positive stock
|
|
|
26 |
INSERT INTO inventory.currentinventorysnapshot (item_id, warehouse_id, availability)
|
|
|
27 |
SELECT scan_truth.itemId, scan_truth.currentWarehouseId, scan_truth.avail
|
|
|
28 |
FROM (
|
|
|
29 |
SELECT ii.itemId, ii.currentWarehouseId,
|
|
|
30 |
SUM(CASE WHEN s.type IN ('PURCHASE','SALE_RET','MARKED_GOOD') THEN s.quantity
|
|
|
31 |
WHEN s.type IN ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD') THEN -s.quantity
|
|
|
32 |
ELSE 0 END) avail
|
|
|
33 |
FROM warehouse.inventoryItem ii
|
|
|
34 |
JOIN warehouse.scanNew s ON s.inventoryItemId = ii.id
|
|
|
35 |
JOIN catalog.tag_listing tl ON tl.item_id = ii.itemId AND tl.active = 1
|
|
|
36 |
WHERE ii.currentWarehouseId > 0
|
|
|
37 |
GROUP BY ii.itemId, ii.currentWarehouseId
|
|
|
38 |
HAVING avail > 0
|
|
|
39 |
) scan_truth
|
|
|
40 |
LEFT JOIN inventory.currentinventorysnapshot cis
|
|
|
41 |
ON cis.item_id = scan_truth.itemId AND cis.warehouse_id = scan_truth.currentWarehouseId
|
|
|
42 |
WHERE IFNULL(cis.availability, -1) != scan_truth.avail
|
|
|
43 |
ON DUPLICATE KEY UPDATE availability = VALUES(availability);
|
|
|
44 |
|
|
|
45 |
-- Step 3: Zero out phantom stock for tag_listing items (snapshot > 0 but scans say 0 or item not in tag_listing)
|
|
|
46 |
UPDATE inventory.currentinventorysnapshot cis
|
|
|
47 |
SET cis.availability = 0
|
|
|
48 |
WHERE cis.availability > 0
|
|
|
49 |
AND (
|
|
|
50 |
cis.item_id NOT IN (SELECT DISTINCT item_id FROM catalog.tag_listing WHERE active = 1)
|
|
|
51 |
OR NOT EXISTS (
|
|
|
52 |
SELECT 1 FROM warehouse.inventoryItem ii
|
|
|
53 |
JOIN warehouse.scanNew s ON s.inventoryItemId = ii.id
|
|
|
54 |
WHERE ii.itemId = cis.item_id AND ii.currentWarehouseId = cis.warehouse_id
|
|
|
55 |
GROUP BY ii.itemId, ii.currentWarehouseId
|
|
|
56 |
HAVING SUM(CASE WHEN s.type IN ('PURCHASE','SALE_RET','MARKED_GOOD') THEN s.quantity
|
|
|
57 |
WHEN s.type IN ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD') THEN -s.quantity
|
|
|
58 |
ELSE 0 END) > 0
|
|
|
59 |
)
|
|
|
60 |
);
|