--- /dev/null
+
+-- kingdee code for the account ---
+ALTER TABLE accnt ADD COLUMN accnt_code_alt TEXT DEFAULT '';
+
+-- chinese name for the account ---
+ALTER TABLE accnt ADD COLUMN accnt_descrip_alt TEXT DEFAULT '';
+
+CREATE INDEX accnt_code_descrip_alt ON accnt USING btree (accnt_code_alt, accnt_descrip_alt);
--- /dev/null
+ALTER TABLE bankaccnt ADD CONSTRAINT bankaccnt_accnt_id_fk
+ FOREIGN KEY (bankaccnt_accnt_id) REFERENCES accnt (accnt_id);
\ No newline at end of file
--- /dev/null
+ALTER TABLE checkhead add column checkhead_voided DATE;
+
--- /dev/null
+ALTER TABLE cntct ADD COLUMN cntct_id_card TEXT NOT NULL DEFAULT '';
\ No newline at end of file
--- /dev/null
+-- Sequence: cobapply_id_seq
+
+-- DROP SEQUENCE cobapply_id_seq;
+
+CREATE SEQUENCE cobapply_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 2147483647
+ START 1
+ CACHE 1;
+ALTER TABLE cobapply_id_seq
+ OWNER TO admin;
+GRANT ALL ON TABLE cobapply_id_seq TO admin;
+GRANT ALL ON TABLE cobapply_id_seq TO xtrole;
+
+
+-- cob apply - planned credit memo applications for a bill ;
+
+
+CREATE TABLE cobapply
+(
+ cobapply_id integer NOT NULL DEFAULT nextval(('cobapply_id_seq'::text)::regclass),
+ cobapply_cobmisc_id integer,
+ cobapply_aropen_id integer,
+ cobapply_applied boolean,
+
+ CONSTRAINT cobapply_pkey PRIMARY KEY (cobapply_id ),
+ CONSTRAINT cobapply_cobmisc_id_fkey FOREIGN KEY (cobapply_cobmisc_id)
+ REFERENCES cobmisc (cobmisc_id)
+ ON UPDATE CASCADE ON DELETE NO ACTION,
+ CONSTRAINT cobapply_aropen_id_fkey FOREIGN KEY (cobapply_aropen_id)
+ REFERENCES aropen (aropen_id)
+ ON UPDATE CASCADE ON DELETE NO ACTION
+)
+WITH (
+ OIDS=FALSE
+);
+
+CREATE INDEX cobapply_cobmisc_id_ix ON cobapply USING btree (cobapply_cobmisc_id);
+CREATE INDEX cobapply_aropen_id_ix ON cobapply USING btree (cobapply_aropen_id);
+CREATE INDEX cobapply_applied_ix ON cobapply USING btree (cobapply_applied);
+
+ALTER TABLE cobapply
+ OWNER TO admin;
+GRANT ALL ON TABLE cobapply TO admin;
+GRANT ALL ON TABLE cobapply TO xtrole;
+COMMENT ON TABLE cobapply
+ IS 'Planned credit memo applies to bills';
+
+
\ No newline at end of file
--- /dev/null
+-- add a 'displayed' sales person.
+
+
+alter table cohead add column cohead_display_salesrep_id INTEGER;
+
+ALTER TABLE cohead add column cohead_pretax_discount numeric(16,4) NOT NULL DEFAULT 0;
+
+ALTER TABLE cohead add column cohead_posttax_discount numeric(16,4) NOT NULL DEFAULT 0;
+
+ALTER TABLE cohead ADD CONSTRAINT cohead_display_salesrep_fkey FOREIGN KEY (cohead_display_salesrep_id)
+ REFERENCES salesrep(salesrep_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE NO ACTION;
+
+
+
--- /dev/null
+ALTER TABLE custinfo ADD COLUMN cust_passwd CHARACTER VARYING(64) NOT NULL DEFAULT '';
+
+CREATE INDEX cust_passwd_ix ON custinfo USING btree (cust_passwd);
+
+ALTER TABLE custinfo ADD COLUMN cust_login_email CHARACTER VARYING(256) NOT NULL DEFAULT '';
+
+CREATE INDEX cust_login_email_ix ON custinfo USING btree (cust_login_email);
+
+ALTER TABLE custinfo ADD COLUMN cust_dob TEXT NOT NULL DEFAULT '';
+
+CREATE INDEX cust_dob_ix ON custinfo USING btree (cust_dob);
+
+ALTER TABLE custinfo ADD COLUMN cust_subscribed BOOLEAN NOT NULL DEFAULT TRUE;
+
+CREATE INDEX cust_subscribed_ix ON custinfo USING btree (cust_subscribed);
\ No newline at end of file
--- /dev/null
+--
+-- first go at dragon reports
+
+
+CREATE SEQUENCE dragon_report_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 2147483647
+ START 1
+ CACHE 1;
+ALTER TABLE recvgrp_id_seq
+ OWNER TO admin;
+GRANT ALL ON TABLE dragon_report_id_seq TO admin;
+GRANT ALL ON TABLE dragon_report_id_seq TO xtrole;
+
+CREATE TABLE dragon_report
+(
+
+ id integer NOT NULL DEFAULT nextval(('dragon_report_id_seq'::text)::regclass),
+ name text NOT NULL,
+ query text,
+
+
+ CONSTRAINT dragon_report_pkey PRIMARY KEY (id)
+)
+WITH (
+ OIDS=FALSE
+);
+
+
+ALTER TABLE dragon_report
+ OWNER TO admin;
+GRANT ALL ON TABLE dragon_report TO admin;
+GRANT ALL ON TABLE dragon_report TO xtrole;
+COMMENT ON TABLE dragon_report
+ IS 'first go at dragon reports';
+
+
\ No newline at end of file
--- /dev/null
+
+CREATE SEQUENCE expense_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 2147483647
+ START 1
+ CACHE 1;
+ALTER TABLE expense_id_seq
+ OWNER TO admin;
+GRANT ALL ON TABLE expense_id_seq TO admin;
+GRANT ALL ON TABLE expense_id_seq TO xtrole;
+
+
+CREATE SEQUENCE expitem_id_seq
+ INCREMENT 1
+ MINVALUE 1
+ MAXVALUE 2147483647
+ START 1
+ CACHE 1;
+ALTER TABLE expitem_id_seq
+ OWNER TO admin;
+GRANT ALL ON TABLE expitem_id_seq TO admin;
+GRANT ALL ON TABLE expitem_id_seq TO xtrole;
+
+
+
+CREATE TABLE expense
+(
+ expense_id INTEGER NOT NULL DEFAULT nextval(('expense_id_seq'::text)::regclass),
+
+
+ expense_accnt_id INTEGER DEFAULT NULL,
+ expense_emp_id INTEGER NOT NULL,
+
+ expense_number TEXT NOT NULL,
+
+ expense_trandate date DEFAULT NULL,
+ expense_created date DEFAULT NULL,
+ expense_modified date DEFAULT NULL,
+ expense_duedate date DEFAULT NULL,
+
+ expense_memo TEXT,
+ expense_comments TEXT,
+ expense_status TEXT,
+
+ expense_advance decimal(12,3) DEFAULT '0.000',
+ expense_amount decimal(12,3) DEFAULT '0.000',
+
+ expense_tax decimal(12,3) DEFAULT '0.000',
+ expense_total decimal(12,3) DEFAULT '0.000',
+ expense_posted boolean default false,
+
+ CONSTRAINT expense_accnt_id_fkey FOREIGN KEY (expense_accnt_id)
+ REFERENCES accnt (accnt_id) MATCH SIMPLE,
+ CONSTRAINT expense_emp_id_fkey FOREIGN KEY (expense_emp_id)
+ REFERENCES emp (emp_id),
+ CONSTRAINT expense_id_pkey PRIMARY KEY (expense_id)
+)
+WITH (
+ OIDS=FALSE
+);
+
+ALTER TABLE expense
+ OWNER TO admin;
+
+GRANT ALL ON TABLE expense TO admin;
+GRANT ALL ON TABLE expense TO xtrole;
+COMMENT ON TABLE expense
+ IS 'Expense Reports';
+
+ CREATE INDEX expense_number_ix ON expense USING btree (expense_number);
+ CREATE INDEX expense_date_ix ON expense USING btree (expense_trandate,expense_created, expense_modified, expense_duedate );
+
+
+
+
+CREATE TABLE expitem
+(
+ expitem_id INTEGER NOT NULL DEFAULT nextval(('expitem_id_seq'::text)::regclass),
+
+ expitem_expense_id INTEGER NOT NULL,
+ expitem_curr_id INTEGER NOT NULL,
+ expitem_expcat_id INTEGER NOT NULL,
+
+
+ expitem_row INTEGER NOT NULL,
+
+
+ expitem_amount decimal(12,3) DEFAULT '0.000',
+ expitem_amount_fc decimal(12,3) DEFAULT '0.000',
+ expitem_tax decimal(12,3) DEFAULT '0.000',
+ expitem_total decimal(12,3) DEFAULT '0.000',
+ --grossAmt decimal(12,3) DEFAULT '0.000',
+ -- expense cat?
+
+ expitem_date date DEFAULT NULL,
+ --exchangeRate decimal(12,5) DEFAULT NULL,
+
+
+ expitem_is_billable INTEGER DEFAULT 0,
+
+ expitem_memo text DEFAULT '',
+
+
+ -- tax code ???
+ --receipt int(4) DEFAULT '0',
+ CONSTRAINT expitem_expense_id_fkey FOREIGN KEY (expitem_expense_id)
+ REFERENCES expense (expense_id),
+
+ CONSTRAINT expitem_curr_id_fkey FOREIGN KEY (expitem_curr_id)
+ REFERENCES curr_symbol (curr_id),
+
+ CONSTRAINT expitem_expcat_id_fkey FOREIGN KEY (expitem_expcat_id)
+ REFERENCES expcat (expcat_id),
+
+ -- unique row / expid....
+
+ CONSTRAINT expitem_id_pkey PRIMARY KEY (expitem_id)
+)
+WITH (
+ OIDS=FALSE
+);
+
+ALTER TABLE expitem
+ OWNER TO admin;
+
+GRANT ALL ON TABLE expitem TO admin;
+GRANT ALL ON TABLE expitem TO xtrole;
+COMMENT ON TABLE expitem
+ IS 'Expense Report Items';
+
--- /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';
+
+
+
+
+
+
+
+-- 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
+ALTER TABLE invdetail ADD CONSTRAINT invdetail_invhist_fk
+ FOREIGN KEY (invdetail_invhist_id) REFERENCES invhist (invhist_id);
\ No newline at end of file
--- /dev/null
+
+-- change for delivery note
+ALTER TABLE invhist_transfer ADD COLUMN invhist_transfer_delivery_note TEXT;
+CREATE INDEX invhist_transfer_delivery_note_ix ON invhist_transfer USING btree (invhist_transfer_delivery_note );
--- /dev/null
+ALTER TABLE location ADD COLUMN location_is_default INT DEFAULT 0;
\ No newline at end of file
--- /dev/null
+-- change for delivery note
+ALTER TABLE recvgrp ADD COLUMN recvgrp_receipt_number TEXT;
+CREATE INDEX recvgrp_receipt_number_ix ON recvgrp USING btree (recvgrp_receipt_number);
+
--- /dev/null
+-- change for delivery note
+ALTER TABLE shiphead ADD COLUMN shiphead_delivery_note TEXT;
+CREATE INDEX shiphead_delivery_note_ix ON shiphead USING btree (shiphead_delivery_note);
+
--- /dev/null
+ALTER TABLE vendaddrinfo ADD CONSTRAINT vendaddr_vend_id_fk
+ FOREIGN KEY (vendaddr_vend_id) REFERENCES vendinfo (vend_id);
\ No newline at end of file
--- /dev/null
+ALTER TABLE vendinfo ADD CONSTRAINT vend_terms_fk
+ FOREIGN KEY (vend_terms_id) REFERENCES terms (terms_id);
\ No newline at end of file
projects / Pman.Xtuple / commitdiff