1 CREATE OR REPLACE FUNCTION invsell_apply_all() RETURNS integer
8 -- find all the matches that we can update, and make the changes.
9 SELECT COUNT(invsell_invhist_id) into v_cnt FROM
13 ROUND(invsell_current_totalcost,2) != ROUND(invsell_calc_totalcost,2)
15 invsell_is_estimate = false ;
23 SELECT invsell_invhist_id INTO v_cnt
28 ROUND(invsell_current_totalcost,2) != ROUND(invsell_calc_totalcost,2)
30 invsell_is_estimate = false
32 invsell_invhist_id ASC
35 RAISE NOTICE 'APPLY INVSELL TO %', v_cnt;
36 -- updates invhist and gltrans
38 invsell_apply_invsell(
48 LANGUAGE plpgsql VOLATILE
51 ALTER FUNCTION invsell_apply_all()
66 CREATE OR REPLACE FUNCTION invsell_apply_invsell(integer) RETURNS integer
69 i_invsell_invhist_id ALIAS FOR $1;
70 v_mininvsell_transdate timestamp with time zone;
74 --RAISE NOTICE 'invsell_apply_invsell:i_invsell_invhist_id =%', i_invsell_invhist_id;
75 -- find all the matches that we can update, and make the changes.
77 --RAISE NOTICE 'invsell_apply';
78 -- updates invhist and gltrans
87 invdepend ON invhist_id = invdepend_invhist_id
89 invsell ON invdepend_parent_id = invsell_invhist_id
92 invsell_invhist_id = i_invsell_invhist_id;
95 -- updates invhist and itemsite.
96 --RAISE NOTICE 'invsell_apply_order';
105 invsell_invhist_id = i_invsell_invhist_id;
107 --RAISE NOTICE 'calc min';
108 SELECT min(invsell_transdate)
109 INTO v_mininvsell_transdate
112 invsell_itemsite_id = (
113 SELECT invsell_itemsite_id FROM invsell WHERE invsell_invhist_id = i_invsell_invhist_id LIMIT 1
118 --RAISE NOTICE 'asset trialbal';
120 invsell_apply_trialbal (
121 costcat_asset_accnt_id,
122 v_mininvsell_transdate
126 LEFT JOIN itemsite ON itemsite_id = invsell_itemsite_id
127 LEFT JOIN costcat ON costcat_id = itemsite_costcat_id
129 invsell_invhist_id = i_invsell_invhist_id;
132 --RAISE NOTICE 'shipasset trialbal';
134 invsell_apply_trialbal (
135 costcat_shipasset_accnt_id,
136 v_mininvsell_transdate
140 LEFT JOIN itemsite ON itemsite_id = invsell_itemsite_id
141 LEFT JOIN costcat ON costcat_id = itemsite_costcat_id
143 invsell_invhist_id = i_invsell_invhist_id;
146 --RAISE NOTICE 'Cogs trialbal';
148 invsell_apply_trialbal (
149 resolvecosaccount(invsell_itemsite_id, cohead_cust_id),
150 v_mininvsell_transdate
156 cohead_number = CASE strpos(invsell_ordnumber, '-')
157 WHEN 0 THEN invsell_ordnumber
158 ELSE substr(invsell_ordnumber, 1, strpos(invsell_ordnumber, '-') - 1)
161 invsell_invhist_id = i_invsell_invhist_id;
166 LANGUAGE plpgsql VOLATILE
169 ALTER FUNCTION invsell_apply_invsell(integer)