Subversion Repositories SmartDukaan

-- 2026-09-24 - Mark debit notes settled by the pre-workflow returns flow as APPROVED.

--

-- APPLIED on hadb1 2026-09-24. Recorded here for the audit trail; do not re-run blindly.

--

-- Why: returns settled before the receive workflow (first purchase_return_order 2026-03-16 12:52) wrote the

-- warehouse SALE_RET scan, a returnorderinfo row and a wallet refund, but never touched the debit note. So

-- 5,557 fully settled notes still read status='CREATED' with no purchase_return_order, and every receive

-- screen decides on exactly those two things - the note looked like it was still awaiting receipt, Receive

-- button included. Re-receiving one (DN UPBLY975/4, IMEI 864973083197734) handed a partner back a phone that

-- had already been returned, refunded and resold, and a duplicate debit note followed.

--

-- Evidence rule (mirrors PurchaseReturnServiceImpl.applyReceipt, which refuses to return a unit twice):

-- a note qualifies only when EVERY unit on it is

--   (a) returned  - a SALE_RET / DOA_IN / SALE_RET_UNUSABLE scan for that unit against the order its

--                   invoice was billed on, and

--   (b) refunded  - the order's refunded returnorderinfo quantity covers its returned units.

-- Serialized units match by IMEI through line_item_imei (holds even where the SALE scan was deleted);

-- non-serialized ones qualify only when the invoice has no returnable quantity left for that item.

-- End state written is the one the new flow writes at refund: debit_note APPROVED, items RETURNED with

-- return_timestamp = the return scan and refund_timestamp = the refund. No purchase_return_order is

-- invented - these were never received through the workflow, and a PRO would falsify who received what.

--

-- Result: 4,154 debit notes and 6,639 return items. Open notes 5,557 -> 1,404.

-- Backups: fofo._bak_dn_status_20260924, fofo._bak_dn_pri_status_20260924.

-- Per-unit evidence and bucket per unit: fofo._dn_backfill_20260922 (+ _dn_backfill_dn_20260922 per note).

--

-- NOT backfilled, left at CREATED for review (see the bucket column):

--   C_REFUNDED_NOT_RETURNED      1,011 notes - refunded with no return scan (refund-leak / re-bill pattern)

--   E_NOT_RETURNED_NOT_REFUNDED    293 notes - raised and abandoned, oldest 2018

--   B_RETURNED_NOT_REFUNDED         82 notes - goods back, no refund recorded: partners may be owed money

--   D_NO_SALE_SCAN                  24 notes - sale scan deleted or IMEI mismatch

--   X_ORDER_NOT_FOUND                2 notes

-- Only 5 of the remainder belong to internal stores (fofo_store.internal = 1).

-- Guards matter: the tables below are a snapshot, and the live flow keeps settling notes. Both statements

-- re-check status and the absence of a purchase_return_order at execution time (DN 7228 was received

-- normally between the snapshot and the run, and correctly dropped out).

UPDATE fofo.debit_note dn

  JOIN fofo._dn_backfill_dn_20260922 b ON b.dn = dn.id AND b.units = b.ok_units

  SET dn.status = 'APPROVED'

  WHERE dn.status = 'CREATED'

    AND NOT EXISTS (SELECT 1 FROM fofo.purchase_return_order pro WHERE pro.debit_note_id = dn.id);

UPDATE fofo.purchase_return_item pri

  JOIN fofo._dn_backfill_20260922 u ON u.pri = pri.id

  JOIN fofo.debit_note dn ON dn.id = pri.debit_note_id AND dn.status = 'APPROVED'

  JOIN fofo._dn_backfill_dn_20260922 b ON b.dn = dn.id AND b.units = b.ok_units

  SET pri.status = 'RETURNED', pri.return_timestamp = u.returned_at, pri.refund_timestamp = u.refunded_at

  WHERE pri.status = 'DEBIT_NOTE_CREATED' AND u.bucket = 'A_RETURNED_REFUNDED'

    AND NOT EXISTS (SELECT 1 FROM fofo.purchase_return_order pro WHERE pro.debit_note_id = dn.id);

-- Verify

-- SELECT status, COUNT(*) FROM fofo.debit_note GROUP BY status;  -- APPROVED 4539, CANCELLED 6, CREATED 1404

-- SELECT COUNT(*) FROM fofo.debit_note dn WHERE dn.status='CREATED'

--   AND NOT EXISTS (SELECT 1 FROM fofo.purchase_return_order pro WHERE pro.debit_note_id=dn.id);  -- 1404