3 -- fix the unit cost difference...
4 -- eg: receive item is $1
6 -- add a transaction for the difference
8 CREATE OR REPLACE FUNCTION invfifo_pohead_fix_unitcost(i_pohead_number text)
12 v_purchprice_accnt_id INTEGER;
13 v_lb_accnt_id INTEGER;
26 costcat_purchprice_accnt_id, -- 148 Inventory Asset
27 costcat_liability_accnt_id -- 141 Inventory Received Not Billed
29 v_purchprice_accnt_id,
37 -- delete existing ???? == do this before we start calculating!?!?
39 deleteGlSeries( gltrans_sequence, 'Void Unit cost adjusted on ' || to_char(NOW(), 'Day Mon DD YYY') )
44 gltrans_docnumber = i_pohead_number
46 gltrans_accnt_id = v_purchprice_accnt_id
48 gltrans_doctype IN ('PO')
50 gltrans_date >= fetchMetricText('invfifo_start_date')::date
54 gltrans_notes LIKE 'Unit cost adjusted for P/O%';
58 -- how much... -- still not sure about this - it's only been tested on a totally void purchase order.
60 -- how much has been accounted for ?
63 COALESCE(SUM(gltrans_amount),0)
71 gltrans_misc_id = invhist_id
75 invhist_transtype = 'RP'
78 invhist_id IS NOT NULL
80 gltrans_accnt_id = (SELECT costcat_asset_accnt_id FROM costcat LIMIT 1)
82 (gltrans_docnumber LIKE i_pohead_number || '-%' OR gltrans_docnumber = i_pohead_number )
89 -- now show what should be created by just using the 'non-vo
91 COALESCE(SUM(gltrans_amount),0)
99 gltrans_misc_id = invhist_id
103 invhist_transtype = 'RP'
106 invhist_id IS NOT NULL
110 gltrans_accnt_id = (SELECT costcat_asset_accnt_id FROM costcat LIMIT 1)
112 (gltrans_docnumber LIKE i_pohead_number || '-%' OR gltrans_docnumber = i_pohead_number )
118 RAISE NOTICE 'Unit cost diff : %', v_recorded - v_correct;
128 gltrans_accnt_id = v_purchprice_accnt_id
130 gltrans_doctype IN ('PO')
132 gltrans_date >= fetchMetricText('invfifo_start_date')::date
136 gltrans_docnumber LIKE i_pohead_number || '%' ;
139 IF (ABS(v_recorded - v_correct) <> 0) THEN
140 SELECT insertGLTransaction( fetchJournalNumber('GL-MISC'),
141 'S/R', 'PO', i_pohead_number,
142 'Unit cost adjusted for P/O ' || i_pohead_number || ' on ' || v_date,
144 v_purchprice_accnt_id, -1,
145 v_recorded - v_correct,
146 v_date::DATE, true ) INTO v_result;
154 LANGUAGE plpgsql VOLATILE
157 ALTER FUNCTION invfifo_pohead_fix_unitcost(text)
164 --SELECT gltrans_docnumber, gltrans_notes, gltrans_amount, invhist_id, invfifo_void FROM gltrans
168 -- gltrans_misc_id = invhist_id
170 -- -- invfifo_void = 0
172 -- invhist_transtype = 'RP'
175 -- gltrans_accnt_id = (SELECT costcat_asset_accnt_id FROM costcat LIMIT 1)
177 -- gltrans_docnumber LIKE '20018' || '-%'
179 -- NOT gltrans_deleted