Subversion Repositories SmartDukaan

-- Backdated PO auto-approval: backfill of the POs still stuck awaiting an HOD click.

--

-- Backdated POs used to be parked in status 0 (INIT) until someone clicked a mailed approval link. INIT keeps a

-- PO out of the PO list, out of GRN/invoice matching and out of the auto-close sweep, so an unclicked PO is

-- simply frozen. 440 POs are sitting that way, going back to 2024.

--

-- Code now auto-approves at creation, so only POs that are still operationally live are worth moving. POScheduler

-- auto-closes READY POs older than 6 days (4 days for internal suppliers), so anything older than that window

-- would go READY -> CLOSED on the next cron tick and never be receivable. That leaves three:

--

--   53974  2026-09-15  BSB Marketing (external)          -- stays open

--   53901  2026-09-14  BSB Marketing (external)          -- stays open

--   53855  2026-09-12  NSSPL UP-DLHR (internal)          -- past its 4-day window, will auto-close

--

-- The creator of a historical PO was never recorded anywhere, so these are stamped SYSTEM (backfill) rather than

-- guessed at. New POs carry their real creator.

--

-- approvedOn is a varchar(128) holding 'YYYY-MM-DD HH:MM:SS.SSS' - that is what Hibernate writes and reads back.

-- Backup before touching anything.

CREATE TABLE IF NOT EXISTS warehouse.poapproval_backup_20260917 AS

SELECT * FROM warehouse.poapproval WHERE poId IN (53974, 53901, 53855);

CREATE TABLE IF NOT EXISTS warehouse.purchaseorder_backup_20260917 AS

SELECT id, status, updatedAt FROM warehouse.purchaseorder WHERE id IN (53974, 53901, 53855);

-- Stamp the approval rows, only where still genuinely unapproved.

UPDATE warehouse.poapproval

SET approvedOn = LEFT(DATE_FORMAT(NOW(3), '%Y-%m-%d %H:%i:%s.%f'), 23),

    approvedBy = 'SYSTEM (backfill)'

WHERE poId IN (53974, 53901, 53855)

  AND approvedOn IS NULL;

-- Release the POs, only where still INIT.

UPDATE warehouse.purchaseorder

SET status = 1,

    updatedAt = NOW()

WHERE id IN (53974, 53901, 53855)

  AND status = 0;

-- Verify: all three should read status 1 with an approvedBy set.

SELECT p.id, p.poNumber, p.status, DATE(p.createdAt) poDate, a.approvedOn, a.approvedBy

FROM warehouse.purchaseorder p

JOIN warehouse.poapproval a ON a.poId = p.id

WHERE p.id IN (53974, 53901, 53855);