--- /dev/null
+-- Sequence: accnt_accnt_id_seq
+
+--DROP table invadj;
+
+
+CREATE SEQUENCE invadj_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 2147483647
+ START 1
+ CACHE 1;
+ALTER TABLE invadj_id_seq
+ OWNER TO admin;
+GRANT ALL ON TABLE invadj_id_seq TO admin;
+GRANT ALL ON TABLE invadj_id_seq TO xtrole;
+
+
+CREATE TABLE invadj
+(
+ invadj_id integer NOT NULL DEFAULT nextval(('invadj_id_seq'::text)::regclass),
+ invadj_transdate date,
+ invadj_location_id integer,
+ invadj_itemsite_id integer,
+ invadj_qty_by integer,
+ invadj_posted boolean,
+
+ CONSTRAINT invadj_pkey PRIMARY KEY (invadj_id ),
+ CONSTRAINT invadj_location_fkey FOREIGN KEY (invadj_location_id)
+ REFERENCES location (location_id)
+ ON UPDATE CASCADE ON DELETE NO ACTION,
+
+ CONSTRAINT invadj_itemsite_fkey FOREIGN KEY (invadj_itemsite_id)
+ REFERENCES itemsite (itemsite_id)
+ ON UPDATE CASCADE ON DELETE NO ACTION
+)
+WITH (
+ OIDS=FALSE
+);
+
+ALTER TABLE invadj ADD COLUMN invadj_comments text;
+ALTER TABLE invadj ADD COLUMN invadj_voids_id INTEGER NOT NULL DEFAULT 0;
+ALTER TABLE invadj ADD COLUMN invadj_invdetail_id INTEGER;
+ALTER TABLE invadj ADD COLUMN invadj_voided_by_id INTEGER NOT NULL DEFAULT 0;
+ALTER TABLE invadj ADD COLUMN invadj_invadjgrp_id INTEGER DEFAULT NULL;
+
+
+CREATE INDEX invadj_location_id_ix ON invadj USING btree (invadj_location_id);
+CREATE INDEX invadj_transdate_ix ON invadj USING btree (invadj_transdate);
+CREATE INDEX invadj_itemsite_ix ON invadj USING btree (invadj_itemsite_id);
+CREATE INDEX invadj_posted_ix ON invadj USING btree (invadj_posted);
+
+CREATE INDEX invadj_voided_by_id_ix ON invadj USING btree (invadj_voided_by_id);
+CREATE INDEX invadj_invdetail_id_ix ON invadj USING btree (invadj_invdetail_id);
+CREATE INDEX invadj_voids_id_ix ON invadj USING btree (invadj_voids_id);
+
+CREATE INDEX invadj_invadjgrp_id_ix ON invadj USING btree (invadj_invadjgrp_id);
+
+
+
+ALTER TABLE invadj
+ OWNER TO admin;
+GRANT ALL ON TABLE invadj TO admin;
+GRANT ALL ON TABLE invadj TO xtrole;
+COMMENT ON TABLE invadj
+ IS 'Inventory Adjustment Draft';
+
+
+
+
+
+
+-- create dummy itemlocs
+
+CREATE OR REPLACE FUNCTION invadj_sync_invdetail(int)
+ RETURNS int AS
+$BODY$
+DECLARE
+
+ i_invdetail_id ALIAS FOR $1;
+
+ r_invfifo RECORD;
+ v_id INTEGER;
+
+
+BEGIN
+
+ SELECT
+ *
+ INTO
+ r_invfifo
+ FROM
+ invdetailview
+ WHERE
+ invdetail_id = i_invdetail_id;
+
+ IF NOT FOUND THEN
+ RAISE EXCEPTION 'could not find invdetail record';
+ END IF;
+
+ IF r_invfifo.invhist_transtype != 'AD' THEN
+ RAISE EXCEPTION 'record is not an adjustment';
+ END IF;
+
+ SELECT
+ invadj_id
+ INTO
+ v_id
+ FROM
+ invadj
+ WHERE
+ invadj_invdetail_id = r_invfifo.invdetail_id;
+
+ IF FOUND THEN
+ RETURN v_id;
+ END IF;
+
+ INSERT INTO
+ invadj
+ (
+
+ invadj_transdate , invadj_location_id ,
+ invadj_itemsite_id , invadj_qty_by ,
+
+ invadj_posted , invadj_comments,
+ invadj_voids_id, invadj_invdetail_id,
+ invadj_voided_by_id
+ )
+ VALUES
+ (
+ r_invfifo.invhist_transdate::date, r_invfifo.invdetail_location_id,
+ r_invfifo.invhist_itemsite_id, r_invfifo.invdetail_qty,
+
+ true, r_invfifo.invhist_ordnumber || '/' || r_invfifo.invhist_ordnumber || ' : '|| r_invfifo.invhist_comments,
+ 0, r_invfifo.invdetail_id,
+ 0
+
+ );
+
+ SELECT
+ invadj_id
+ INTO
+ v_id
+ FROM
+ invadj
+ WHERE
+ invadj_invdetail_id = r_invfifo.invdetail_id;
+
+
+ RETURN v_id;
+END;
+$BODY$
+ LANGUAGE plpgsql VOLATILE
+ COST 100;
+ALTER FUNCTION invadj_sync_invdetail(int)
+ OWNER TO admin;
+
+
+
+-- create dummy itemlocs
+
+CREATE OR REPLACE FUNCTION invadj_post(int)
+ RETURNS INTEGER AS
+$BODY$
+DECLARE
+
+ i_invadj_id ALIAS FOR $1;
+
+ r_invadj RECORD;
+ r_dist RECORD;
+
+ v_invdetail_id INTEGER;
+
+ v_series INTEGER;
+ v_result INTEGER;
+ v_result_bool BOOLEAN;
+BEGIN
+
+
+ SELECT
+ *
+ INTO
+ r_invadj
+ FROM
+ invadj
+ WHERE
+ invadj_id = i_invadj_id;
+
+ IF NOT FOUND THEN
+ RAISE EXCEPTION 'could not find invadj record';
+ END IF;
+
+ IF r_invadj.invadj_posted THEN
+ RAISE EXCEPTION 'invadj already posted';
+ END IF;
+
+
+ IF r_invadj.invadj_voids_id > 0 THEN
+
+ SELECT
+ invadj_voided_by_id
+ INTO
+ v_result
+ FROM
+ invadj
+ WHERE
+ invadj_id = r_invadj.invadj_voids_id;
+
+
+
+ IF NOT FOUND THEN
+ RAISE EXCEPTION 'voids id is invalid';
+ END IF;
+ IF v_result > 0 THEN
+ RAISE EXCEPTION 'voids id points to an already voided record';
+ END IF;
+
+ END IF;
+
+
+ SELECT invAdjustment(
+ r_invadj.invadj_itemsite_id,
+ r_invadj.invadj_qty_by,
+ 'INVADJ-' || r_invadj.invadj_id,
+ r_invadj.invadj_comments,
+ r_invadj.invadj_transdate
+ -- uses std cost?
+ ) INTO v_series;
+
+ IF v_series < 1 THEN
+ RAISE EXCEPTION 'invAdjustment post failed with %', v_series;
+ END IF;
+
+ SELECT
+ itemlocdist_id, itemlocdist_reqlotserial,
+ itemlocdist_distlotserial, itemlocdist_qty,
+
+ itemsite_loccntrl, itemsite_controlmethod,
+ itemsite_perishable, itemsite_warrpurc,
+
+ COALESCE(itemsite_lsseq_id,-1) AS itemsite_lsseq_id,
+ COALESCE(itemlocdist_source_id,-1) AS itemlocdist_source_id
+ INTO
+ r_dist
+ FROM
+ itemlocdist, itemsite
+ WHERE
+ (
+ (itemlocdist_itemsite_id=itemsite_id) AND (itemlocdist_series=v_series )
+ )
+ ORDER BY itemlocdist_id;
+
+
+ IF NOT FOUND THEN
+ RAISE EXCEPTION 'invAdjustment failed to create itemlocdist';
+ END IF;
+
+
+
+ INSERT INTO itemlocdist (
+ itemlocdist_itemlocdist_id, itemlocdist_source_type,
+ itemlocdist_source_id, itemlocdist_qty,
+ itemlocdist_ls_id, itemlocdist_expiration
+ )
+ SELECT
+ itemlocdist_id, 'L',
+ r_invadj.invadj_location_id, r_invadj.invadj_qty_by,
+ itemlocdist_ls_id, endOfTime()
+ FROM itemlocdist WHERE (itemlocdist_id=r_dist.itemlocdist_id);
+
+
+ SELECT distributeToLocations(r_dist.itemlocdist_id) INTO v_result;
+
+ IF NOT FOUND OR v_result < 0 THEN
+ RAISE EXCEPTION 'distributeToLocations failed ';
+ END IF;
+
+
+ SELECT postItemlocseries(v_series) INTO v_result_bool ;
+
+ IF NOT v_result_bool THEN
+ RAISE EXCEPTION 'postItemlocseries failed ';
+ END IF;
+
+ -- finally - if the record was a void - make refernce in the voided data..
+ IF r_invadj.invadj_voids_id > 0 THEN
+
+ UPDATE invadj SET
+ invadj_voided_by_id = r_invadj.invadj_id
+ WHERE
+ invadj_id = r_invadj.invadj_voids_id ;
+
+
+ END IF;
+
+ SELECT
+ invdetail_id
+ INTO
+ v_invdetail_id
+ FROM
+ invdetailview
+ WHERE
+ invhist_ordnumber = 'INVADJ-' || r_invadj.invadj_id
+ LIMIT 1;
+
+
+ UPDATE invadj SET
+ invadj_posted = true,
+ invadj_invdetail_id = v_invdetail_id
+ WHERE
+
+ invadj_id = r_invadj.invadj_id;
+
+
+
+
+ -- we need to update the invhist id..
+
+ IF r_invadj.invadj_voids_id > 0 OR r_invadj.invadj_voided_by_id > 0 THEN
+ -- try running the void flag code.
+ PERFORM invfifo_invadj_void_flag(v_invdetail_id);
+ END IF;
+
+
+
+ RETURN v_result;
+END;
+$BODY$
+ LANGUAGE plpgsql VOLATILE
+ COST 100;
+ALTER FUNCTION invadj_post(int)
+ OWNER TO admin;
+
+
+
+-- sync:
+--DELETE FROM invadj;
+--SELECT invadj_sync_invdetail(invdetail_id) FROM invdetailview where invhist_transtype = 'AD' ORDER BY invhist_transdate ASC;
+
+
+--- FIX the fact i forgot to update invdetail id..
+
+--UPDATE invadj
+-- SET invadj_invdetail_id = (SELECT invdetail_id FROM invdetailview WHERE invhist_ordnumber = 'INVADJ-' || invadj_id LIMIT 1)
+-- WHERE invadj_posted and invadj_invdetail_id IS NULL;
+--
+--
+