+++ /dev/null
--- not wide enough?
-
-
--- this was a mistake!!! - we should never had made gl 3 decimal places - it makes a nightmare everywhere..
-
-alter table glseries alter column glseries_amount TYPE numeric(12,2);
- --View: api.journalentry
-
-DROP VIEW api.journalentry;
- alter table gltrans alter column gltrans_amount TYPE numeric(12,2);
-
-CREATE OR REPLACE VIEW api.journalentry AS
- SELECT curr_symbol.curr_abbr AS currency, c.gltrans_amount AS amount, c.gltrans_date AS dist_date, c.gltrans_docnumber AS doc_number, formatglaccount(da.accnt_id) AS debit, formatglaccount(ca.accnt_id) AS credit, c.gltrans_notes AS notes
- FROM gltrans d, gltrans c, accnt da, accnt ca, curr_symbol
- WHERE d.gltrans_sequence = c.gltrans_sequence AND d.gltrans_accnt_id = da.accnt_id AND c.gltrans_accnt_id = ca.accnt_id AND d.gltrans_amount < 0::numeric AND c.gltrans_amount > 0::numeric AND d.gltrans_doctype = 'JE'::text AND c.gltrans_doctype = 'JE'::text AND curr_symbol.curr_id = basecurrid()
- ORDER BY d.gltrans_date DESC;
-
-ALTER TABLE api.journalentry
- OWNER TO admin;
-GRANT ALL ON TABLE api.journalentry TO admin;
-GRANT ALL ON TABLE api.journalentry TO xtrole;
-COMMENT ON VIEW api.journalentry
- IS 'Journal Entry';
-
-
--- Rule: "_DELETE" ON api.journalentry
-
--- DROP RULE "_DELETE" ON api.journalentry;
-
-CREATE OR REPLACE RULE "_DELETE" AS
- ON DELETE TO api.journalentry DO INSTEAD NOTHING;
-
--- Rule: "_INSERT" ON api.journalentry
-
--- DROP RULE "_INSERT" ON api.journalentry;
-
-CREATE OR REPLACE RULE "_INSERT" AS
- ON INSERT TO api.journalentry DO INSTEAD SELECT insertgltransaction('G/L'::text, 'JE'::text, new.doc_number, new.notes, getglaccntid(new.credit), getglaccntid(new.debit), (-1), currtobase(getcurrid(new.currency::text), new.amount, new.dist_date), new.dist_date) AS insertgltransaction;
-
--- Rule: "_UPDATE" ON api.journalentry
-
--- DROP RULE "_UPDATE" ON api.journalentry;
-
-CREATE OR REPLACE RULE "_UPDATE" AS
- ON UPDATE TO api.journalentry DO INSTEAD NOTHING;
---
+++ /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;
---
---
-