+++ /dev/null
--- invadj routines ... -- split into seperate files...
-
-
-
-
--- 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;
---
---
-
+++ /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;
---
---
-
projects / Pman.Xtuple / commitdiff