1 --- updates the invfifo values for shipped and unitcost
3 -- purchase order return from shipping.. (note we just pop it of the stack as usual,
4 --even though it affects landed cost, as our landed cost
5 -- is affected by this.
7 -- note, we actually need this where single items are returned from shipments..
10 CREATE OR REPLACE FUNCTION invfifo_update_from_return_inventory(integer)
14 i_invdetail_id ALIAS FOR $1;
16 RAISE EXCEPTION 'replaced with option version';
19 LANGUAGE plpgsql VOLATILE
22 ----------------------------
24 CREATE OR REPLACE FUNCTION invfifo_update_from_return_inventory(integer, boolean)
29 i_invdetail_id ALIAS FOR $1;
30 i_update_after ALIAS FOR $2;
33 r_invdetail_out RECORD;
38 v_est_unitcost NUMERIC;
40 v_invfifo_cost_before NUMERIC;
41 v_invfifo_cost_after NUMERIC;
57 invdetail_id = i_invdetail_id;
61 RAISE EXCEPTION ' invfifo_update_from_return_inventory -- need to add invfifo gen code';
64 IF (r_invdetail.invhist_transtype != 'RP') THEN
65 RAISE EXCEPTION 'invfifo_update_from_return_inventory called on non-inventory return';
71 -- fetch the actual purchase price
72 -- it should be the reverse of this.
74 SELECT invfifo_cost_at_qty(r_invdetail.invhist_itemsite_id, r_invdetail.invdetail_location_id, r_invdetail.invfifo_qty_before) INTO
75 v_invfifo_cost_before;
77 SELECT invfifo_cost_at_qty(r_invdetail.invhist_itemsite_id, r_invdetail.invdetail_location_id, r_invdetail.invfifo_qty_after) INTO
81 --RAISE NOTICE 'cost before is %, cost after is %',v_invfifo_cost_before, v_invfifo_cost_after;
83 IF v_invfifo_cost_before < 0 THEN
85 SELECT invfifo_cost_before_out(r_invdetail.invfifo_qty_before, r_invdetail.invdetail_location_id ,r_invdetail.invhist_itemsite_id) INTO
86 v_invfifo_cost_before;
88 v_invfifo_cost_after = v_invfifo_cost_before +
89 ABS(r_invdetail.invdetail_qty) * r_invdetail.invhist_unitcost;
95 IF v_invfifo_cost_after <= 0.0 THEN
96 -- we are selling more than we have..
97 -- we should find out what was the last purchase price and use that as the unitcost..
98 v_invfifo_cost_after = v_invfifo_cost_before +
99 ABS(r_invdetail.invdetail_qty) * r_invdetail.invhist_unitcost;
104 --RAISE NOTICE 'cost before is %, cost after is %',v_invfifo_cost_before, v_invfifo_cost_after;
105 v_unitcost = ABS((v_invfifo_cost_after - v_invfifo_cost_before) / r_invdetail.invdetail_qty);
106 v_landedcost = ABS((v_invfifo_cost_after - v_invfifo_cost_before) / r_invdetail.invdetail_qty);
109 r_invdetail.invfifo_unitcost = v_unitcost
111 r_invdetail.invfifo_landedunitcost = v_landedcost
113 r_invdetail.invfifo_cost_after = v_invfifo_cost_after
115 r_invdetail.invfifo_cost_before = v_invfifo_cost_before
116 --- we might need these???
118 --r_invdetail.invfifo_cust_id = v_cust_id
120 --r_invdetail.invfifo_cohead_id = v_cohead_id
130 invfifo_unitcost = v_unitcost,
131 invfifo_landedunitcost = v_landedcost,
132 invfifo_cost_after = v_invfifo_cost_after,
133 invfifo_cost_before = v_invfifo_cost_before,
134 invfifo_recalc_queued = false
136 invfifo_invdetail_id = r_invdetail.invdetail_id;
138 v_diff = ROUND(v_invfifo_cost_after - r_invdetail.invfifo_cost_after,2);
140 IF (v_diff = 0.0) THEN
144 IF NOT i_update_after THEN
148 -- update the values after this one..
149 -- FLAG The sales after this date as dirty.?? just in case?
153 invfifo_cost_after = invfifo_cost_after + v_diff,
154 invfifo_cost_before = invfifo_cost_before + v_diff
156 -- invfifo_recalc_queued = 1
160 -- unit costs and landed does not change on after..
162 invfifo_invdetail_id = invdetail_id
164 invhist_id = invdetail_invhist_id
166 invdetail_location_id = r_invdetail.invdetail_location_id
168 invhist_itemsite_id = r_invdetail.invhist_itemsite_id
170 invfifo_qty_after > r_invdetail.invfifo_qty_after
181 LANGUAGE plpgsql VOLATILE
184 ALTER FUNCTION invfifo_update_from_return_inventory(integer,boolean)