1 -- Sequence: accnt_accnt_id_seq
6 CREATE SEQUENCE invadj_id_seq
12 ALTER TABLE invadj_id_seq
14 GRANT ALL ON TABLE invadj_id_seq TO admin;
15 GRANT ALL ON TABLE invadj_id_seq TO xtrole;
20 invadj_id integer NOT NULL DEFAULT nextval(('invadj_id_seq'::text)::regclass),
21 invadj_transdate date,
22 invadj_location_id integer,
23 invadj_itemsite_id integer,
24 invadj_qty_by integer,
25 invadj_posted boolean,
27 CONSTRAINT invadj_pkey PRIMARY KEY (invadj_id ),
28 CONSTRAINT invadj_location_fkey FOREIGN KEY (invadj_location_id)
29 REFERENCES location (location_id)
30 ON UPDATE CASCADE ON DELETE NO ACTION,
32 CONSTRAINT invadj_itemsite_fkey FOREIGN KEY (invadj_itemsite_id)
33 REFERENCES itemsite (itemsite_id)
34 ON UPDATE CASCADE ON DELETE NO ACTION
40 ALTER TABLE invadj ADD COLUMN invadj_comments text;
41 ALTER TABLE invadj ADD COLUMN invadj_voids_id INTEGER NOT NULL DEFAULT 0;
42 ALTER TABLE invadj ADD COLUMN invadj_invdetail_id INTEGER;
43 ALTER TABLE invadj ADD COLUMN invadj_voided_by_id INTEGER NOT NULL DEFAULT 0;
44 ALTER TABLE invadj ADD COLUMN invadj_invadjgrp_id INTEGER DEFAULT NULL;
47 CREATE INDEX invadj_location_id_ix ON invadj USING btree (invadj_location_id);
48 CREATE INDEX invadj_transdate_ix ON invadj USING btree (invadj_transdate);
49 CREATE INDEX invadj_itemsite_ix ON invadj USING btree (invadj_itemsite_id);
50 CREATE INDEX invadj_posted_ix ON invadj USING btree (invadj_posted);
52 CREATE INDEX invadj_voided_by_id_ix ON invadj USING btree (invadj_voided_by_id);
53 CREATE INDEX invadj_invdetail_id_ix ON invadj USING btree (invadj_invdetail_id);
54 CREATE INDEX invadj_voids_id_ix ON invadj USING btree (invadj_voids_id);
56 CREATE INDEX invadj_invadjgrp_id_ix ON invadj USING btree (invadj_invadjgrp_id);
62 GRANT ALL ON TABLE invadj TO admin;
63 GRANT ALL ON TABLE invadj TO xtrole;
64 COMMENT ON TABLE invadj
65 IS 'Inventory Adjustment Draft';
72 -- create dummy itemlocs
74 CREATE OR REPLACE FUNCTION invadj_sync_invdetail(int)
79 i_invdetail_id ALIAS FOR $1;
94 invdetail_id = i_invdetail_id;
97 RAISE EXCEPTION 'could not find invdetail record';
100 IF r_invfifo.invhist_transtype != 'AD' THEN
101 RAISE EXCEPTION 'record is not an adjustment';
111 invadj_invdetail_id = r_invfifo.invdetail_id;
121 invadj_transdate , invadj_location_id ,
122 invadj_itemsite_id , invadj_qty_by ,
124 invadj_posted , invadj_comments,
125 invadj_voids_id, invadj_invdetail_id,
130 r_invfifo.invhist_transdate::date, r_invfifo.invdetail_location_id,
131 r_invfifo.invhist_itemsite_id, r_invfifo.invdetail_qty,
133 true, r_invfifo.invhist_ordnumber || '/' || r_invfifo.invhist_ordnumber || ' : '|| r_invfifo.invhist_comments,
134 0, r_invfifo.invdetail_id,
146 invadj_invdetail_id = r_invfifo.invdetail_id;
152 LANGUAGE plpgsql VOLATILE
154 ALTER FUNCTION invadj_sync_invdetail(int)
159 -- create dummy itemlocs
161 CREATE OR REPLACE FUNCTION invadj_post(int)
166 i_invadj_id ALIAS FOR $1;
171 v_invdetail_id INTEGER;
175 v_result_bool BOOLEAN;
186 invadj_id = i_invadj_id;
189 RAISE EXCEPTION 'could not find invadj record';
192 IF r_invadj.invadj_posted THEN
193 RAISE EXCEPTION 'invadj already posted';
197 IF r_invadj.invadj_voids_id > 0 THEN
206 invadj_id = r_invadj.invadj_voids_id;
211 RAISE EXCEPTION 'voids id is invalid';
214 RAISE EXCEPTION 'voids id points to an already voided record';
220 SELECT invAdjustment(
221 r_invadj.invadj_itemsite_id,
222 r_invadj.invadj_qty_by,
223 'INVADJ-' || r_invadj.invadj_id,
224 r_invadj.invadj_comments,
225 r_invadj.invadj_transdate
230 RAISE EXCEPTION 'invAdjustment post failed with %', v_series;
234 itemlocdist_id, itemlocdist_reqlotserial,
235 itemlocdist_distlotserial, itemlocdist_qty,
237 itemsite_loccntrl, itemsite_controlmethod,
238 itemsite_perishable, itemsite_warrpurc,
240 COALESCE(itemsite_lsseq_id,-1) AS itemsite_lsseq_id,
241 COALESCE(itemlocdist_source_id,-1) AS itemlocdist_source_id
245 itemlocdist, itemsite
248 (itemlocdist_itemsite_id=itemsite_id) AND (itemlocdist_series=v_series )
250 ORDER BY itemlocdist_id;
254 RAISE EXCEPTION 'invAdjustment failed to create itemlocdist';
259 INSERT INTO itemlocdist (
260 itemlocdist_itemlocdist_id, itemlocdist_source_type,
261 itemlocdist_source_id, itemlocdist_qty,
262 itemlocdist_ls_id, itemlocdist_expiration
266 r_invadj.invadj_location_id, r_invadj.invadj_qty_by,
267 itemlocdist_ls_id, endOfTime()
268 FROM itemlocdist WHERE (itemlocdist_id=r_dist.itemlocdist_id);
271 SELECT distributeToLocations(r_dist.itemlocdist_id) INTO v_result;
273 IF NOT FOUND OR v_result < 0 THEN
274 RAISE EXCEPTION 'distributeToLocations failed ';
278 SELECT postItemlocseries(v_series) INTO v_result_bool ;
280 IF NOT v_result_bool THEN
281 RAISE EXCEPTION 'postItemlocseries failed ';
284 -- finally - if the record was a void - make refernce in the voided data..
285 IF r_invadj.invadj_voids_id > 0 THEN
288 invadj_voided_by_id = r_invadj.invadj_id
290 invadj_id = r_invadj.invadj_voids_id ;
302 invhist_ordnumber = 'INVADJ-' || r_invadj.invadj_id
307 invadj_posted = true,
308 invadj_invdetail_id = v_invdetail_id
311 invadj_id = r_invadj.invadj_id;
316 -- we need to update the invhist id..
318 IF r_invadj.invadj_voids_id > 0 OR r_invadj.invadj_voided_by_id > 0 THEN
319 -- try running the void flag code.
320 PERFORM invfifo_invadj_void_flag(v_invdetail_id);
328 LANGUAGE plpgsql VOLATILE
330 ALTER FUNCTION invadj_post(int)
336 --DELETE FROM invadj;
337 --SELECT invadj_sync_invdetail(invdetail_id) FROM invdetailview where invhist_transtype = 'AD' ORDER BY invhist_transdate ASC;
340 --- FIX the fact i forgot to update invdetail id..
343 -- SET invadj_invdetail_id = (SELECT invdetail_id FROM invdetailview WHERE invhist_ordnumber = 'INVADJ-' || invadj_id LIMIT 1)
344 -- WHERE invadj_posted and invadj_invdetail_id IS NULL;