| 37822 |
amit |
1 |
-- Vendor warehouses so each Gurugram physical warehouse can receive internal movements from its Delhi counterpart only:
|
|
|
2 |
-- supplier 452 (7720 DL-HR) -> 13368 HR-NSSPL/GGN
|
|
|
3 |
-- supplier 325 (7573 Delhi) -> 13370 HR-NSSPL/DL
|
|
|
4 |
-- supplier 457 (9514 DL-Noida) -> 13372 HR-NSSPL/UPW
|
|
|
5 |
-- GRN books units into selectGoodSupplierWarehouse(supplier, physical) - exactly one OURS/GOOD row per pair.
|
|
|
6 |
-- Rows mirror WarehouseServiceImpl.createVendorWarehouse (edit-supplier screen), as for supplier 457 -> 9470/10521 on
|
|
|
7 |
-- 2026-09-15: OURS/GOOD, OURS/BAD, THIRD_PARTY/GOOD; displayName = destination seller label; location/state/pin from
|
|
|
8 |
-- the destination address master (#33); logisticsLocation from the physical row; gstin NULL.
|
|
|
9 |
|
|
|
10 |
START TRANSACTION;
|
|
|
11 |
|
|
|
12 |
INSERT INTO inventory.warehouse (displayName, location, status, addedOn, lastCheckedOn, tinNumber, gstin, pincode,
|
|
|
13 |
logisticsLocation, vendor_id, billingType, inventoryType, warehouseType, shippingWarehouseId, billingWarehouseId,
|
|
|
14 |
transferDelayInHours, isAvailabilityMonitored, state_id, source)
|
|
|
15 |
SELECT se.label, a.address, 3, NOW(), NOW(), NULL, NULL, a.pin, pw.logisticsLocation, p.vendor, 0, t.inv, t.wtype,
|
|
|
16 |
pw.id, pw.id, 0, 0, a.state_id, 0
|
|
|
17 |
FROM (SELECT 452 vendor, 13368 wh UNION ALL SELECT 325, 13370 UNION ALL SELECT 457, 13372) p
|
|
|
18 |
JOIN (SELECT 1 ord, 'GOOD' inv, 'OURS' wtype UNION ALL SELECT 2, 'BAD', 'OURS' UNION ALL SELECT 3, 'GOOD', 'THIRD_PARTY') t
|
|
|
19 |
JOIN inventory.warehouse pw ON pw.id = p.wh
|
|
|
20 |
JOIN transaction.sellerwarehouse sw ON sw.warehouse_id = p.wh AND sw.orderType = 0
|
|
|
21 |
JOIN transaction.seller se ON se.id = sw.seller_id
|
|
|
22 |
JOIN transaction.warehouseaddressmapping m ON m.warehouse_id = p.wh
|
|
|
23 |
JOIN transaction.warehouseaddressmaster a ON a.id = m.address_id
|
|
|
24 |
WHERE NOT EXISTS (SELECT 1 FROM inventory.warehouse x WHERE x.vendor_id = p.vendor AND x.billingWarehouseId = p.wh
|
|
|
25 |
AND x.inventoryType = 'GOOD' AND x.warehouseType = 'OURS')
|
|
|
26 |
ORDER BY p.wh, t.ord;
|
|
|
27 |
|
|
|
28 |
COMMIT;
|
|
|
29 |
|
|
|
30 |
-- verify: exactly one row of each type per pair
|
|
|
31 |
SELECT w.vendor_id, w.billingWarehouseId, w.id, w.warehouseType, w.inventoryType, w.displayName, w.state_id,
|
|
|
32 |
w.pincode, w.logisticsLocation, LEFT(w.location, 40) location
|
|
|
33 |
FROM inventory.warehouse w
|
|
|
34 |
WHERE (w.vendor_id, w.billingWarehouseId) IN ((452, 13368), (325, 13370), (457, 13372))
|
|
|
35 |
ORDER BY w.billingWarehouseId, w.id;
|