| 36446 |
amit |
1 |
-- DN lifecycle: add status column to debit_note, add retailer_acknowledge_timestamp to purchase_return_order
|
|
|
2 |
|
|
|
3 |
ALTER TABLE fofo.debit_note
|
|
|
4 |
ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'CREATED';
|
|
|
5 |
|
|
|
6 |
ALTER TABLE fofo.purchase_return_order
|
|
|
7 |
ADD COLUMN retailer_acknowledge_timestamp DATETIME NULL;
|
|
|
8 |
|
|
|
9 |
-- Backfill debit_note.status from purchase_return_order timestamps (DNs with PROs)
|
|
|
10 |
UPDATE fofo.debit_note dn SET status = 'APPROVED'
|
|
|
11 |
WHERE EXISTS (SELECT 1 FROM fofo.purchase_return_order pro WHERE pro.debit_note_id = dn.id AND pro.refund_timestamp IS NOT NULL);
|
|
|
12 |
|
|
|
13 |
UPDATE fofo.debit_note dn SET status = 'REJECTED'
|
|
|
14 |
WHERE status = 'CREATED'
|
|
|
15 |
AND EXISTS (SELECT 1 FROM fofo.purchase_return_order pro WHERE pro.debit_note_id = dn.id AND pro.reject_timestamp IS NOT NULL);
|
|
|
16 |
|
|
|
17 |
UPDATE fofo.debit_note dn SET status = 'RECEIVED'
|
|
|
18 |
WHERE status = 'CREATED'
|
|
|
19 |
AND EXISTS (SELECT 1 FROM fofo.purchase_return_order pro WHERE pro.debit_note_id = dn.id AND pro.receive_timestamp IS NOT NULL);
|
|
|
20 |
|
|
|
21 |
-- Backfill debit_note.status from purchase_return_item for early DNs without PROs
|
|
|
22 |
UPDATE fofo.debit_note dn SET status = 'APPROVED'
|
|
|
23 |
WHERE status = 'CREATED'
|
|
|
24 |
AND NOT EXISTS (SELECT 1 FROM fofo.purchase_return_order pro WHERE pro.debit_note_id = dn.id)
|
|
|
25 |
AND NOT EXISTS (SELECT 1 FROM fofo.purchase_return_item pri WHERE pri.debit_note_id = dn.id AND pri.status NOT IN ('RETURNED'));
|
|
|
26 |
|
|
|
27 |
UPDATE fofo.debit_note dn SET status = 'CANCELLED'
|
|
|
28 |
WHERE status = 'CREATED'
|
|
|
29 |
AND NOT EXISTS (SELECT 1 FROM fofo.purchase_return_order pro WHERE pro.debit_note_id = dn.id)
|
|
|
30 |
AND NOT EXISTS (SELECT 1 FROM fofo.purchase_return_item pri WHERE pri.debit_note_id = dn.id AND pri.status NOT IN ('REJECTED', 'DEBIT_NOTE_CANCELLED'));
|
|
|
31 |
|
|
|
32 |
-- Index for filtering by status
|
|
|
33 |
CREATE INDEX idx_debit_note_status ON fofo.debit_note (status);
|