1 -- invfifo is a shadow table to invdetail which contains the fifo data.
8 invfifo_invdetail_id integer NOT NULL UNIQUE,
10 -- unit is excluding shipping (it's mostly ignored now)
11 invfifo_unitcost numeric(18, 6) NOT NULL,
12 invfifo_landedunitcost NUMERIC(18,6) DEFAULT 0.0 NOT NULL,
15 invfifo_totalcost numeric(18, 6) NOT NULL,
17 -- key columns which the calculation is done on .
18 invfifo_qty_before numeric(18, 6) NOT NULL,
19 invfifo_qty_after numeric(18, 6) NOT NULL,
20 invfifo_cost_before numeric(18, 6) NOT NULL,
21 invfifo_cost_after numeric(18, 6) NOT NULL,
23 -- flags .. not used at present.. may not be in futer
24 invfifo_is_estimate boolean NOT NULL,
25 invfifo_recalc_queued boolean NOT NULL,
27 -- this is used to determine if update_from_invdetail has been run.
30 -- just stores cust/vend to save looking it up later.
31 invfifo_vend_id INT DEFAULT 0,
32 invfifo_cust_id INT DEFAULT 0,
33 invfifo_cohead_id INT NOT NULL DEFAULT 0,
34 -- FIXME - cohead_id might be a good idea here...
37 -- is the transaction a reversal of another one - so has $0 effect on FIFO.
38 -- should point to the invdetail_id of what it reverses, or -1 for random flagging...
39 invfifo_void INTEGER NOT NULL DEFAULT 0,
41 CONSTRAINT invfifo_invdetail_fk FOREIGN KEY ( invfifo_invdetail_id) REFERENCES invdetail (invdetail_id)
44 alter table invfifo add column invfifo_landedunitcost NUMERIC(18,6) DEFAULT 0.0 NOT NULL;
45 -- void of which record.
46 alter table invfifo add column invfifo_void INTEGER NOT NULL DEFAULT 0;
47 alter table invfifo add column invfifo_cohead_id INT NOT NULL DEFAULT 0;
49 GRANT ALL ON TABLE invfifo TO admin;
50 GRANT ALL ON TABLE invfifo TO xtrole;
53 CREATE INDEX invfifo_qty_before_indx
58 CREATE INDEX invfifo_qty_after_indx
63 CREATE INDEX invfifo_is_estimate_indx
66 (invfifo_is_estimate);
68 CREATE INDEX invfifo_recalc_queued_indx
71 (invfifo_recalc_queued);
73 CREATE INDEX invfifo_cust_id_indx
78 CREATE INDEX invfifo_vend_id_indx
83 CREATE INDEX invfifo_qty_before_indx
88 CREATE INDEX invfifo_void_indx
93 CREATE INDEX invfifo_cohead_id_indx
98 alter table invfifo drop column invfifo_coitem_id;
104 -- update /inserts tirgger invfifo_fill()
106 CREATE OR REPLACE FUNCTION invdetailtriggerfifo() RETURNS trigger
112 PERFORM invfifo_fill(NEW.invdetail_id);
118 LANGUAGE plpgsql VOLATILE
121 ALTER FUNCTION invdetailtriggerfifo()
127 CREATE TRIGGER _invdetailtriggerfifo
128 AFTER INSERT ON invdetail
129 FOR EACH ROW EXECUTE PROCEDURE invdetailtriggerfifo();
132 CREATE OR REPLACE FUNCTION invfifotriggerfifo() RETURNS trigger
138 IF (NEW.invfifo_void IS NOT NULL AND NEW.invfifo_void < 0 ) THEN
139 RAISE EXCEPTION 'Voiding a transaction needs to know what was voided invdetail_id= %', NEW.invfifo_invdetail_id ;
145 LANGUAGE plpgsql VOLATILE
148 ALTER FUNCTION invdetailtriggerfifo()
153 CREATE TRIGGER _invfifotriggerfifo
154 AFTER INSERT OR UPDATE ON invfifo
155 FOR EACH ROW EXECUTE PROCEDURE invfifotriggerfifo();