Subversion Repositories SmartDukaan

-- Item master fixes found while e-invoicing the Delhi -> HR movement (APPLIED on hadb1 2026-10-02).
-- NIC rejected IRNs for invalid HSNs; billing failed "Category not found" (CTRGY_1000) for category-0 items; the mug still
-- carried the pre-22-Sep-2025 rate (GST 2.0 moved ceramic tableware 6911/6912 from 12% to 5%). HSNs set to 6-digit
-- subheadings, which NIC accepts. Backups: catalog._bak_item_hsn_20261002, catalog._bak_stategstrate_20261002,
-- transaction._bak_lineitem_hsn_20261002, transaction._bak_order_irn_20261002.
-- Each statement matches only rows still holding the old value, so a re-run is a no-op.

START TRANSACTION;

-- HSN: item master (the e-invoice reads HSN from catalog.item, not the order line)
UPDATE catalog.item SET hsnCode = '852352' WHERE id = 29566 AND hsnCode = 'NOGST';     -- Reliance JIO SIM (smart cards)
UPDATE catalog.item SET hsnCode = '691200' WHERE id = 33538 AND hsnCode = '9093119';   -- Smartdukaan Mug (ceramic tableware)
UPDATE catalog.item SET hsnCode = '210690' WHERE id = 33855 AND hsnCode = '9093119';   -- Smartdukaan Gift Sweet (sweetmeats)
UPDATE catalog.item SET hsnCode = '902300' WHERE id = 34682 AND hsnCode = '90230090';  -- Itel S23 Dummy (demonstration models)
UPDATE catalog.item SET hsnCode = '392690'                                             -- Xeuce plastic back covers (39263099 is not a valid subheading)
 WHERE id IN (29349, 29363, 29411, 29414, 29417, 29373, 29407, 29408, 29409, 29410, 29412, 29413, 29415, 29418, 29419)
   AND hsnCode = '39263099';

-- Category: category 0 does not exist, so billing could not resolve it
UPDATE catalog.item SET category = 17001 WHERE id = 34682 AND category = 0;  -- Dummy Mobiles
UPDATE catalog.item SET category = 10030 WHERE id = 35658 AND category = 0;  -- Cables (Ziox Knot 602)
UPDATE catalog.item SET category = 10018 WHERE id = 35882 AND category = 0;  -- Carrying Case (FOC S24 cover)

-- GST rates
UPDATE catalog.stategstrate SET igst_rate = 5 WHERE item_id = 33538 AND state_id = -1 AND igst_rate = 12;
UPDATE catalog.stategstrate SET cgst_rate = 2.5, sgst_rate = 2.5 WHERE item_id = 33538 AND state_id = 0 AND cgst_rate = 6;
INSERT INTO catalog.stategstrate (item_id, state_id, igst_rate, cgst_rate, sgst_rate)
  SELECT 34682, -1, 18, 0, 0 FROM DUAL
   WHERE NOT EXISTS (SELECT 1 FROM catalog.stategstrate WHERE item_id = 34682 AND state_id = -1);
INSERT INTO catalog.stategstrate (item_id, state_id, igst_rate, cgst_rate, sgst_rate)
  SELECT 34682, 0, 0, 9, 9 FROM DUAL
   WHERE NOT EXISTS (SELECT 1 FROM catalog.stategstrate WHERE item_id = 34682 AND state_id = 0);

-- Billed lines on the invoices NIC had rejected (no IRN was ever issued, so they were corrected before first filing)
UPDATE transaction.lineitem l JOIN transaction.`order` o ON o.id = l.order_id
   SET l.hsnCode = '852352' WHERE o.invoice_number = 'NSDL73573' AND l.item_id = 29566 AND l.hsnCode = 'NOGST';
UPDATE transaction.lineitem l JOIN transaction.`order` o ON o.id = l.order_id
   SET l.hsnCode = '392690' WHERE o.invoice_number IN ('NSDL73573', 'NSDL73605') AND l.hsnCode = '39263099';
UPDATE transaction.lineitem l JOIN transaction.`order` o ON o.id = l.order_id
   SET l.hsnCode = '691200', l.igstRate = 5
 WHERE o.invoice_number IN ('NSDL73575', 'NSDL73581') AND l.item_id = 33538 AND l.igstRate = 12;

COMMIT;

-- The four corrected invoices were then re-filed with the cron command --retryIrn (r37833); all got IRN + e-way bill.