Last active
October 28, 2021 16:20
-
-
Save thomaspaulb/d171c295f789291982311d3d5f419bac to your computer and use it in GitHub Desktop.
Test trigger for Odoo stock issues
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE FUNCTION check_quants_equal_moves() | |
RETURNS TRIGGER AS | |
$BODY$ | |
DECLARE | |
quants_amount int; | |
moves_from int; | |
moves_to int; | |
BEGIN | |
quants_amount := ( | |
select sum(quantity) qty | |
from stock_quant q | |
where location_id = OLD.location_id | |
and product_id = OLD.product_id | |
); | |
moves_from := ( | |
select coalesce(sum(qty_done), 0) qty | |
from stock_move_line l | |
where l.state = 'done' | |
and location_id = OLD.location_id | |
and product_id = OLD.product_id | |
); | |
moves_to := ( | |
select coalesce(sum(qty_done), 0) qty | |
from stock_move_line l | |
where l.state = 'done' | |
and location_dest_id = OLD.location_id | |
and product_id = OLD.product_id | |
); | |
IF quants_amount != moves_to - moves_from THEN | |
raise exception '% is not % - %', quants_amount, moves_to, moves_from; | |
END IF; | |
RETURN NEW; | |
END; | |
$BODY$ | |
LANGUAGE plpgsql; | |
CREATE CONSTRAINT TRIGGER quants_equal_moves | |
AFTER INSERT OR UPDATE ON stock_quant | |
DEFERRABLE | |
INITIALLY DEFERRED | |
FOR EACH ROW EXECUTE PROCEDURE check_quants_equal_moves(); | |
-- testing | |
begin; | |
update stock_move_line set qty_done = 5 where id = 1; | |
update stock_quant set quantity = 5 where id = 1; | |
update stock_quant set quantity = -5 where id = 2; | |
commit; | |
select id, location_id, location_dest_id, qty_done, state from stock_move_line where product_id = 1; | |
select id, location_id, quantity from stock_quant where product_id = 1; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment