1 -- relay method, that calls various updates based on the type of line.
4 --- updates the invfifo values for shipped and unitcost
7 -- test: SELECT invfifo_update_from_invdetail(11598); SELECT * from invdetailview where invdetail_id = 11598;
11 CREATE OR REPLACE FUNCTION invfifo_update_from_invdetail(integer)
15 i_invdetail_id ALIAS FOR $1;
17 RAISE EXCEPTION 'replaced with option version';
20 LANGUAGE plpgsql VOLATILE
23 ALTER FUNCTION invfifo_update_from_invdetail(integer)
28 CREATE OR REPLACE FUNCTION invfifo_update_from_invdetail(i_invdetail_id integer, i_update_after boolean)
37 r_invdetail_rev RECORD;
39 v_ts_start timestamp with time zone;
42 -- RAISE NOTICE ' invfifo_update_from_invdetail %',i_invdetail_id;
45 v_ts_start = clock_timestamp();
54 invdetail_id = i_invdetail_id;
57 if (r_invdetail.invfifo_void > 0) THEN
58 -- RAISE NOTICE ' calling void on %,%',i_invdetail_id, r_invdetail.invfifo_void;
60 invfifo_update_from_void(i_invdetail_id, r_invdetail.invfifo_void, i_update_after)
70 invdetail_id = r_invdetail.invfifo_void;
72 IF r_invdetail_rev.invfifo_void < 1 THEN
73 RAISE NOTICE 'voiding reversal %', r_invdetail.invfifo_void;
75 invfifo_update_from_void(r_invdetail.invfifo_void,i_invdetail_id, i_update_after)
86 -- should we fix broken voids here?
87 -- if before/after same and not void... -then fill needs calling..
89 -- validate the quantity - as if this is wrong it causes all sorts of problems.
93 COALESCE(invfifo_qty_after, 0)
99 invdetail_location_id = r_invdetail.invdetail_location_id
103 invhist_transdate < r_invdetail.invhist_transdate
106 ( invhist_transdate = r_invdetail.invhist_transdate
108 invdetail_id < i_invdetail_id
113 invhist_itemsite_id = r_invdetail.invhist_itemsite_id
115 -- if r_invhist.invdetail_qty
117 (r_invdetail.invdetail_qty > 0 AND invdetail_qty > 0)
119 (r_invdetail.invdetail_qty < 0 AND invdetail_qty < 0)
122 invhist_transdate DESC,
126 IF v_qty_before != r_invdetail.invfifo_qty_before THEN
127 PERFORM invfifo_fill(i_invdetail_id, true);
130 --RAISE NOTICE '% invfifo_update_from_invdetail (% : %/%) %s',
131 -- clock_timestamp(), i_invdetail_id ,
132 -- r_invdetail.invhist_transtype, r_invdetail.invhist_ordtype, r_invdetail.invdetail_qty;
134 IF (r_invdetail.invdetail_qty > 0) THEN
135 -- calculate the cost before.. (incremental cost)
137 IF (r_invdetail.invhist_transtype = 'RP') THEN
138 SELECT invfifo_update_from_pohead(i_invdetail_id, i_update_after)
140 -- RAISE NOTICE 'TIME: % invfifo_update_from_invdetail (%)',
141 -- clock_timestamp() - v_ts_start, i_invdetail_id ;
147 -- if it's a transfer
148 -- then unit price is dependant on FIFO sale that the tx relates to.
150 IF (r_invdetail.invhist_transtype = 'RL') THEN
151 SELECT invfifo_update_from_transfer_in(i_invdetail_id, i_update_after)
153 -- RAISE NOTICE 'TIME: % invfifo_update_from_invdetail (%)',
154 -- clock_timestamp() - v_ts_start, i_invdetail_id ;
160 -- what if it's a debit memo???
162 -- if's it's a credit memo.
163 IF (r_invdetail.invhist_transtype = 'RS' AND r_invdetail.invhist_ordtype = 'CM' ) THEN
164 SELECT invfifo_update_from_credit_memo(i_invdetail_id, i_update_after)
166 --RAISE NOTICE 'TIME: % invfifo_update_from_invdetail (%)',
167 -- clock_timestamp() - v_ts_start, i_invdetail_id ;
172 IF (r_invdetail.invhist_transtype = 'RS' AND r_invdetail.invhist_ordtype = 'SO' ) THEN
173 -- This is a voided transaction..
175 -- if void is not +ve then we need to know what it returned from..
176 if (r_invdetail.invfifo_void > 0) THEN
178 SELECT invfifo_update_from_void(i_invdetail_id, r_invdetail.invfifo_void, i_update_after)
182 UPDATE cohead set cohead_fifo_has_error = true where cohead_number = split_part(r_invdetail.invhist_ordnumber, '-', 1);
184 --UPDATE invfifo SET invfifo_void = 0 WHERE
185 -- invfifo_invdetail_id IN
192 -- invhist_itemsite_id = r_invdetail.invhist_itemsite_id
194 -- invhist_ordnumber like split_part(r_invdetail.invhist_ordnumber, '-', 1) || '-%'
196 -- invhist_ordtype = 'SO'
201 --PERFORM invfifo_cohead_void_flag(cohead_id ,r_invdetail.invhist_itemsite_id) FROM
202 -- (SELECT cohead_id FROM cohead where cohead_number = split_part(r_invdetail.invhist_ordnumber, '-', 1) ) x;
211 -- invdetail_id = i_invdetail_id;
215 -- RAISE NOTICE 'TIME: % invfifo_update_from_invdetail (%)',
216 -- clock_timestamp() - v_ts_start, i_invdetail_id ;
222 IF (r_invdetail.invhist_transtype = 'SH' AND r_invdetail.invhist_ordtype = 'IN' ) THEN
223 -- CAN BE RETurn from shipping.
224 SELECT invfifo_update_from_return_invoice(i_invdetail_id, i_update_after)
226 -- RAISE NOTICE 'TIME: % invfifo_update_from_invdetail (%)',
227 -- clock_timestamp() - v_ts_start, i_invdetail_id ;
235 -- if it's an adjustment...
236 -- then the invhist is really the only tx record of this..
238 IF (r_invdetail.invhist_transtype = 'AD') THEN
239 SELECT invfifo_update_from_adjustment_in(i_invdetail_id, i_update_after)
241 --RAISE NOTICE 'TIME: % invfifo_update_from_invdetail (%)',
242 -- clock_timestamp() - v_ts_start, i_invdetail_id ;
248 RAISE EXCEPTION 'Unknown transtype (IN) %, % %', r_invdetail.invhist_transtype, r_invdetail.invhist_ordtype , i_invdetail_id;
254 -- are these all based on FIFO values???
258 -- if's it's a shipment - record customer id...
259 IF (r_invdetail.invhist_transtype = 'SH' AND r_invdetail.invhist_ordtype='SO') THEN
260 SELECT invfifo_update_from_shipment(i_invdetail_id, i_update_after)
262 --RAISE NOTICE 'TIME: % invfifo_update_from_invdetail (%)',
263 -- clock_timestamp() - v_ts_start, i_invdetail_id ;
268 IF (r_invdetail.invhist_transtype = 'SH' AND r_invdetail.invhist_ordtype='IN') THEN
269 SELECT invfifo_update_from_invoice(i_invdetail_id, i_update_after)
271 --RAISE NOTICE 'TIME: % invfifo_update_from_invdetail (%)',
272 -- clock_timestamp() - v_ts_start, i_invdetail_id ;
277 IF (r_invdetail.invhist_transtype = 'RL') THEN
278 -- this is handled by the transfer in
281 invfifo_update_from_transfer_in(invdetail_id,i_update_after)
287 invhist_id = r_invdetail.invhist_id
289 invdetail_id != i_invdetail_id;
291 -- PERFORM invfifo_update_from_transfer_out(i_invdetail_id);
294 -- return PO inventory.. -- SAME as a void transaction
295 IF (r_invdetail.invhist_transtype = 'RP') THEN
298 if (r_invdetail.invfifo_void < 1) THEN
299 -- RAISE NOTICE 'performing void flag on ordnumber % , itemsite %', r_invdetail.invhist_ordnumber, r_invdetail.invhist_itemsite_id;
300 PERFORM invfifo_pohead_void_flag_order(pohead_id) FROM pohead where pohead_number = split_part(r_invdetail.invhist_ordnumber, '-', 1);
304 --RAISE EXCEPTION 'need to add rp back';
306 SELECT invfifo_update_from_return_inventory(i_invdetail_id, i_update_after)
308 --RAISE NOTICE 'TIME: % invfifo_update_from_invdetail (%)',
309 -- clock_timestamp() - v_ts_start, i_invdetail_id ;
314 IF (r_invdetail.invhist_transtype = 'AD') THEN
315 SELECT invfifo_update_from_adjustment_out(i_invdetail_id, i_update_after)
317 --RAISE NOTICE 'TIME: % invfifo_update_from_invdetail (%)',
318 -- clock_timestamp() - v_ts_start, i_invdetail_id ;
322 -- return from shipping? alwasy
323 IF (r_invdetail.invhist_transtype = 'RS') THEN
326 -- ignore returned shipment with invalid values?!?!
327 -- these should be voided and not happend.
333 RAISE EXCEPTION 'Unknonw transtype (OUT) % , invdetail_id = %',r_invdetail.invhist_transtype, i_invdetail_id ;
339 LANGUAGE plpgsql VOLATILE
342 ALTER FUNCTION invfifo_update_from_invdetail(integer,boolean)
347 --SELECT invfifo_update_from_invdetail(invdetail_id,true) FROM invdetail order by invdetail_id DESC limit 10;