| 36314 |
amit |
1 |
-- Fix warehouse.invoice.receiveDate for entries where it is NULL
|
|
|
2 |
-- The receiveDate should reflect the actual GRN time
|
|
|
3 |
-- Uses warehouse.purchase.receivedOn which was set to LocalDateTime.now() during GRN processing
|
|
|
4 |
-- Falls back to MIN(inventoryItem.created) if purchase.receivedOn is also NULL
|
|
|
5 |
|
|
|
6 |
UPDATE warehouse.invoice wi
|
|
|
7 |
JOIN (
|
|
|
8 |
SELECT p.invoice_id, MIN(p.receivedOn) as grnTime
|
|
|
9 |
FROM warehouse.purchase p
|
|
|
10 |
WHERE p.receivedOn IS NOT NULL
|
|
|
11 |
GROUP BY p.invoice_id
|
|
|
12 |
) p ON wi.id = p.invoice_id
|
|
|
13 |
SET wi.receiveDate = p.grnTime
|
|
|
14 |
WHERE wi.receiveDate IS NULL
|
|
|
15 |
AND wi.status = 'received';
|