From 05255c8e235bd7a71e53c9d37e1aee28e5367757 Mon Sep 17 00:00:00 2001 From: Alan Knowles Date: Wed, 25 Feb 2015 23:20:13 +0800 Subject: [PATCH] sync --- pgsql/invadj_post.sql | 193 ++++++++++++++++++++++++++++++++ pgsql/invadj_sync_invdetail.sql | 84 ++++++++++++++ 2 files changed, 277 insertions(+) create mode 100644 pgsql/invadj_post.sql create mode 100644 pgsql/invadj_sync_invdetail.sql diff --git a/pgsql/invadj_post.sql b/pgsql/invadj_post.sql new file mode 100644 index 00000000..bd54d99b --- /dev/null +++ b/pgsql/invadj_post.sql @@ -0,0 +1,193 @@ + +-- create dummy itemlocs + + +-- 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; +-- +-- + diff --git a/pgsql/invadj_sync_invdetail.sql b/pgsql/invadj_sync_invdetail.sql new file mode 100644 index 00000000..8c2a58d2 --- /dev/null +++ b/pgsql/invadj_sync_invdetail.sql @@ -0,0 +1,84 @@ + +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; + -- 2.39.2